SELECT
aiv.vendor_name, aiv.vendor_number, aiv.invoice_num, aiv.gl_date, aiv.terms_date,
aiv.terms_name, ahv.hold_date, ahv.release_date
FROM apps.ap_invoices_v aiv,apps.ap_holds_v ahv
WHERE
1=1
and aiv.invoice_id=ahv.invoice_id
-- and aiv.invoice_id=apsa.invoice_id
and HOLD_LOOKUP_CODE like 'QTY ORD%' and RELEASE_REASON is null
order by hold_date desc;
Preparing a Nested Query to read XML FILE from Appl Server.
select Hdrxml.TRX_ID, Hdrxml.BILL_TO_NAME, Hdrxml.TRX_NUMBER,Hdrxml.G_LINE,linexml.*
from
(SELECT XMLTYPE(bfilename('XX_AR_INV_OUT', 'o87864385Copy.out'), nls_charset_id('UTF8')) xml_data
FROM dual) a, xmltable('/XX_AR_INV_XML_GEN/LIST_G_INV/G_INV' passing a.xml_data
columns TRX_ID NUMBER path 'TRX_ID',
BILL_TO_NAME VARCHAR2(1000) path 'BILL_TO_NAME',
TRX_NUMBER VARCHAR2(1000) path 'TRX_NUMBER' ,
G_LINE XMLTYPE PATH 'LIST_G_LINE/G_LINE'
) Hdrxml,
xmltable('/G_LINE' passing Hdrxml.G_LINE
columns LINE_NUMBER NUMBER path 'LINE_NUMBER',
ITEM_NUMBER VARCHAR2(1000) path 'ITEM_NUMBER',
QUANTITY_SHIPPED VARCHAR2(1000) path 'QUANTITY_SHIPPED',
UNIT_PRICE VARCHAR2(1000) path 'UNIT_PRICE',
UOM_CODE VARCHAR2(1000) path 'UOM_CODE'
) linexml
;
Oracle SQL Query useful Tutorial Video
https://www.youtube.com/watch?v=0OmNC-_Khrw
select distinct aia.invoice_num,LISTAGG( line_number,',') WITHIN GROUP( ORDER BY line_number ) AS line_num from ap_invoices_all aia, ap_invoice_lines_all aila
where aia.invoice_id=aila.invoice_id
and aia.org_id=aila.org_id
group by aia.invoice_num;
select 'A' BLOCK ,aia.invoice_num,LISTAGG( line_number,',') WITHIN GROUP( ORDER BY line_number ) AS line_num
from ap_invoices_all aia, ap_invoice_lines_all aila
where aia.invoice_id=aila.invoice_id
and aia.org_id=aila.org_id
group by aia.invoice_num
having count(aila.line_number) < 3
select 'A' BLOCK ,
aia.invoice_num--,LISTAGG( line_number,',') WITHIN GROUP( ORDER BY line_number ) AS line_num
from ap_invoices_all aia, ap_invoice_lines_all aila
where aia.invoice_id=aila.invoice_id
and aia.org_id=aila.org_id
and aia.invoice_num ='ERS-36577'
--and rownum <5
--group by aia.invoice_num;
--having count(aila.line_number) < 3
Union all
select 'B' BLOCK ,
aia.invoice_num--LISTAGG( line_number,',') WITHIN GROUP( ORDER BY line_number ) AS line_num
from ap_invoices_all aia, ap_invoice_lines_all aila
where aia.invoice_id=aila.invoice_id
and aia.org_id=aila.org_id
and aia.invoice_num ='ERS-36577'
--group by aia.invoice_num
--having count(aila.line_number) > 10;
select gcck.concatenated_segments,
--gcck.SEGMENT1||'-'||gcck.SEGMENT2||'-'||gcck.SEGMENT3||'-'||gcck.SEGMENT4|| '-'||gcck.SEGMENT5||'-'||gcck.SEGMENT6||'-'||gcck.SEGMENT7||'-'||gcck.SEGMENT8||'-'||gcck.SEGMENT9 "ACCOUNT",
gcck.SEGMENT3,
case when gcck.SEGMENT3 in ( 5250,1110,1590,4150)then
'ABC'
ELSE
NULL
end SOB_ITP,
decode(gcck.SEGMENT3,5250,'abc',1110,'xyz',null)
from gl_code_combinations_kfv gcck;
where gcck.SEGMENT3 != '5250';
select gcck.concatenated_segments, gcck.SEGMENT3,
case when gcck.SEGMENT2 in ( '520','170','510')then
case when gcck.SEGMENT3 in ( '5250','1590')then
'NESTED CASE'
else
'NESTED ELSE'
end
ELSE
NULL
end SOB_ITP
from gl_code_combinations_kfv gcck;
where gcck.SEGMENT3 != '5250';
1) write a query to display po_no,po_line_num,po_shipment_number, po_distribution num and charge account code
2) Write query to display code combination segment against charge_account ccid of po _distributions
