SELECT PO_Line_Number ,
unit_price purchase_price ,
Item_Dupl_Number Item_Number ,
Item_Dupl_Descrption Item_Descrption,
NOT_MATCHED_INVOICE ,
Supplier_Dupl_Name Supplier_Name ,
Supplier_Dupl_Number Supplier_Number,
orgnization_name inv_org ,
ON_HAND_QUANTITY ,
buyer_name ,
item_cost std_frozen_cost ,
orgnization_name ship_to_org_name ,
Receipt_Number ,
Receipt_Date ,
Receipt_shipped_quantity ,
Receipt_received_quantity ,
invoice_number ,
invoice_date ,
invoice_quantity ,
invoice_amount ,
invoice_paid
FROM
(SELECT DISTINCT x.inventory_item_id ,
x.org_name ,
x.org_id ,
x.orgnization_name ,
x.Organization_ID ,
x.Supplier_Name ,
DECODE(z.MATCH_STATUS_FLAG,'A',NULL,z.invoice_number) NOT_MATCHED_INVOICE ,
DECODE(x.supplier_name,(lag (x.supplier_name) over (ORDER BY x.segment1,x.line_num)),NULL,x.supplier_name) Supplier_Dupl_Name,
--decode((lag (x.segment1) over (ORDER BY x.segment1,x.line_num)),x.segment1,decode(x.supplier_name,(lag (x.supplier_name) over (ORDER BY x.segment1,x.line_num)),NULL,x.supplier_name),NULL),
x.Supplier_Number ,
DECODE(x.Supplier_Number ,(lag (x.Supplier_Number ) over (ORDER BY x.segment1,x.line_num)),NULL,x.Supplier_Number) Supplier_Dupl_Number ,
x.item_number ,
DECODE(x.item_number,(lag (x.item_number) over (ORDER BY x.segment1,x.line_num)),NULL,x.item_number) Item_Dupl_Number ,
x.item_description ,
DECODE(x.item_description,(lag (x.item_description ) over (ORDER BY x.segment1,x.line_num)),NULL,x.item_description ) Item_Dupl_Descrption,
x.quantity ,
x.PO_Line_Number ,
x.segment1 ,
x.line_num ,
x.buyer_name ,
x.unit_price ,
y.Receipt_Number ,
y.Receipt_Date ,
y.Receipt_shipped_quantity ,
y.Receipt_received_quantity ,
--x.ON_HAND "ON_HAND_QUANTITY" ,
--decode(p.is_consigned,1,'Y',2,'N', null) consigned_flag,
p.ON_HAND_QUANTITY,
x.item_cost ,
z.invoice_number ,
z.invoice_date ,
z.invoice_quantity,
z.invoice_paid ,
z.invoice_amount ,
z.invoice_amount_paid
FROM
(SELECT DISTINCT cst.ITEM_COST ,
pha.po_header_id ,
pla.po_line_id ,
pv.vendor_name Supplier_Name ,
pv.segment1 Supplier_Number ,
msib.segment1 item_number ,
msib.inventory_item_id ,
-- sum(nvl(moq.transaction_quantity,0)) ON_HAND ,
pla.item_description ,
plla.org_id ,
plla.ship_to_organization_id Organization_ID,
plla.quantity ,
pha.segment1
|| '-'
|| pla.line_num PO_Line_Number ,
pha.segment1 ,
pla.line_num ,
papf.full_name buyer_name ,
pla.unit_price ,
hou.name org_name ,
ood.organization_name orgnization_name
/*rsh.receipt_num Receipt_Number ,
rsh.creation_date Receipt_date ,
rsl.quantity_shipped Receipt_shipped_quantity ,
rsl.quantity_received Receipt_received_quantity,
aia.invoice_num ,
aia.invoice_date ,
aia.invoice_amount ,
aia.payment_status_flag,
aia.amount_paid ,
aia.invoice_id,
msib.inventory_item_id*/
FROM apps.po_headers_all pha ,
apps.po_lines_all pla ,
apps.po_line_locations_all plla ,
apps.po_vendors pv ,
--apps.po_distributions_all pda ,
apps.mtl_system_items_b msib ,
apps.per_all_people_f papf ,
apps.hr_organization_units hou ,
apps.ORG_ORGANIZATION_DEFINITIONS OOD,
apps.cst_item_costs CST
-- apps.MTL_ONHAND_QUANTITIES_DETAIL MOQ
/*apps.rcv_shipment_lines rsl ,
apps.rcv_shipment_headers rsh
apps.ap_invoice_distributions_all aida,
apps.ap_invoices_all aia*/
WHERE pha.po_header_id = pla.po_header_id
AND pha.org_id = pla.org_id
AND pha.po_header_id = plla.po_header_id
AND pla.po_line_id = plla.po_line_id
/*AND pla.po_line_id =pda.po_line_id
AND pha.po_header_id = pda.po_header_id
AND plla.line_location_id = pda.line_location_id*/
AND pha.vendor_id = pv.vendor_id
AND pla.item_id = msib.inventory_item_id
AND NVL(msib.organization_id,plla.ship_to_organization_id) =plla.ship_to_organization_id
AND pha.agent_id = papf.person_id
AND plla.ship_to_organization_id = ood.organization_id
AND pha.org_id =hou.ORGANIZATION_ID
AND CST.inventory_item_id =pla.item_id
AND CST.organization_id =plla.ship_to_organization_id
--AND MOQ.organization_id =plla.ship_to_organization_id
--AND pla.item_id =MOQ.inventory_item_id (+)
--and CST.inventory_item_id = MOQ.inventory_item_id
AND CST.COST_TYPE_ID =1
--AND sysdate BETWEEN papf.effective_start_date AND papf.effective_end_date
/*AND pla.po_line_id = rsl.po_line_id
AND pha.po_header_id = rsl.po_header_id
AND rsl.shipment_header_id = rsh.shipment_header_id
AND aida.invoice_id = aia.invoice_id
AND pda.po_distribution_id = aida.po_distribution_id*/
-- and pla.po_line_id =50487
-- AND pha.segment1 IN ('442305782','442305186')
--and pla.po_line_id =50487
GROUP BY pha.po_header_id ,
pla.po_line_id ,
msib.inventory_item_id ,
plla.org_id ,
plla.ship_to_organization_id ,
pv.vendor_name ,
pv.segment1 ,
msib.segment1 ,
pla.item_description ,
plla.quantity ,
pha.segment1 ,
pla.line_num ,
papf.full_name ,
pla.unit_price ,
hou.name ,
ood.organization_name ,
cst.ITEM_COST
ORDER BY pha.segment1,
pla.line_num --('442303903','442305186'); --,'442305782'
) x ,
(SELECT rsl.po_header_id ,
rsl.po_line_id ,
rsl.to_organization_id ,
listagg(rsh.receipt_num,',') within GROUP (
ORDER BY rsl.po_header_id,rsl.po_line_id) Receipt_Number,
listagg(rsh.creation_date,',') within GROUP (
ORDER BY rsl.po_header_id,rsl.po_line_id) Receipt_date ,
SUM(rsl.quantity_shipped) Receipt_shipped_quantity ,
SUM(rsl.quantity_received) Receipt_received_quantity
FROM apps.rcv_shipment_lines rsl ,
apps.rcv_shipment_headers rsh
WHERE rsl.shipment_header_id = rsh.shipment_header_id
-- AND rsl.po_header_id =24282
--and rsl.po_line_id =50487
GROUP BY rsl.po_header_id,
rsl.po_line_id ,
rsl.to_organization_id
) y ,
(SELECT pda.po_header_id ,
pda.po_line_id ,
--aia.invoice_id,
pda.po_distribution_id ,
aida.org_id ,
aida.MATCH_STATUS_FLAG MATCH_STATUS_FLAG,
listagg(aia.invoice_num,',') within GROUP (
ORDER BY pda.po_header_id,pda.po_line_id) invoice_number,
listagg(aia.invoice_date,',') within GROUP (
ORDER BY pda.po_header_id,pda.po_line_id) invoice_date,
listagg(aia.payment_status_flag,',') within GROUP (
ORDER BY aia.invoice_num) invoice_paid ,
SUM(aida.quantity_invoiced) invoice_quantity,
SUM(aia.invoice_amount) invoice_amount ,
SUM(aia.amount_paid) invoice_amount_paid
FROM apps.po_distributions_all pda ,
apps.ap_invoice_distributions_all aida,
apps.ap_invoices_all aia
WHERE aida.invoice_id = aia.invoice_id
AND pda.org_id = aida.org_id
AND aia.org_id =aida.org_id
AND pda.po_distribution_id = aida.po_distribution_id
--AND pda.po_header_id =24282
--AND pda.po_line_id =52668
GROUP BY pda.po_header_id ,
pda.po_line_id ,
-- aia.invoice_id,
pda.po_distribution_id,
aida.org_id ,
aida.MATCH_STATUS_FLAG
) z ,
(SELECT inventory_item_id,
organization_id ,
NVL(SUM(transaction_quantity),0) ON_HAND_QUANTITY
FROM apps.MTL_ONHAND_QUANTITIES_detail MOQ
GROUP BY inventory_item_id,
organization_id
) p
WHERE x.po_header_id =y.po_header_id
AND x.po_line_id = y.po_line_id
AND x.Organization_ID = y.to_organization_id
AND x.po_header_id = z.po_header_id
AND x.po_line_id = z.po_line_id
AND x.Org_ID = z.org_id
AND x.inventory_item_id =p.inventory_item_id
AND x.organization_id =p.organization_id
AND x.org_id =103
ORDER BY x.segment1,
x.line_num
)
No comments:
Post a Comment