Monday, March 24, 2014

P2P query in oracle apps R12

Give 'PO Number' as an input to the following script. 
It will provide all required PO details associated Receipt details, Invoice details, Payment details.
Purchase Order -> Receipts -> Invoices -> Payments 





SELECT pha.segment1 po_number,
       pla.line_num,
       plla.shipment_num,
       msi.segment1 ordered_item,
       msi.description ordered_item_description,
       pla.unit_price po_unit_price,
       rt.transaction_type,
       rt.transaction_date,
       rt.subinventory,
       plla.quantity quantity_ordered,
       plla.quantity_received,
       pda.quantity_delivered,
       plla.quantity_Billed,
       rsh.receipt_num,
       aia.invoice_num,
       ail.line_number inv_line_number,
       aid.distribution_line_number inv_dist_number,
       ail.line_type_lookup_code Line_type,
       aid.line_type_lookup_code Dist_line_type,
       aid.amount,
       aid.quantity_invoiced,
       ac.check_number,
       aip.payment_num
  FROM apps.rcv_transactions rt,
       apps.po_headers_all pha,
       apps.po_line_locations_all plla,
       apps.po_distributions_all pda,
       apps.po_lines_all pla,
       apps.mtl_system_items msi,
       apps.rcv_shipment_headers rsh,
       apps.ap_invoices_all aia,
       apps.ap_invoice_lines_all ail,
       apps.ap_invoice_distributions_all aid,
       apps.ap_invoice_payments_all aip,
       apps.ap_checks_all ac
WHERE     rt.po_header_id = pha.po_header_id
       AND pha.po_header_id = pla.po_header_id
       AND pla.po_line_id = plla.po_line_id
       AND plla.line_location_id = pda.line_location_id
       AND rt.po_line_location_id = plla.line_location_id
       AND pla.item_id = msi.inventory_item_id
       AND rt.po_line_id = pla.po_line_id
       AND rt.organization_id = msi.organization_id
       AND rsh.shipment_header_id = rt.shipment_header_id
       AND aip.check_id = ac.check_id
       AND aia.invoice_id = aip.invoice_id
       AND aia.invoice_id = ail.invoice_id
       AND aia.invoice_id = aid.invoice_id
       AND pda.po_distribution_id(+) = aid.po_distribution_id
       AND rt.transaction_type = 'DELIVER'
       AND ail.line_type_lookup_code = 'ITEM'
       AND aid.line_type_lookup_code = 'ACCRUAL'
       AND pha.segment1 = '&PO_Number'

No comments:

Post a Comment

How to improve blog performance

Improving the performance of a blog can involve a variety of strategies, including optimizing the website's technical infrastructure, im...