Oracle Procure To Pay (P2P) Queries

REQUISITION

SELECT prh.segment1 Req_num,

       pdt.document_type_code,

       prh.TYPE_LOOKUP_CODE TYPE,

       prh.AUTHORIZATION_STATUS status,

       ppf.FULL_NAME Preparer,

       PRL.LINE_NUM,

       msi.SEGMENT1 item_num,

       PLT.LINE_TYPE,

       msi.DESCRIPTION Item_des,

       ppf.FULL_NAME Requester

  FROM po_requisition_headers prh,

       per_people_f ppf,

       po_requisition_lines prl,

       PO_LINE_TYPES PLT,

       po_document_types pdt,

       Mtl_system_items msi

 WHERE     prh.REQUISITION_HEADER_ID = prl.REQUISITION_HEADER_ID

       AND prh.preparer_id = ppf.person_id

       AND plt.line_type_id = prl.line_type_id

       AND msi.inventory_item_id = prl.item_id

       -- and msi.ORGANIZATION_ID=prl.ORG_ID

       AND pdt.document_subtype = prh.type_lookup_code

       AND pdt.document_type_code = 'REQUISITION'

       AND pdt.org_id = prh.org_id

       AND prh.segment1 = '5633'

PURCHASE ORDER QUERY

SELECT pv.VENDOR_NAME,

       pv.VENDOR_ID,

       pv.SEGMENT1 vend_num,

       pp.FULL_NAME buyer_name,

       pp.LAST_NAME,

       pdt.type_name po_type,

       ph.STATUS_LOOKUP_CODE STATUS,

       msi.SEGMENT1 item_num,

       msi.DESCRIPTION Item_des,

       pl.QUANTITY,

       pl.UNIT_PRICE

  FROM po_vendors pv,

       po_headers ph,

       po_lines pl,

       po_document_types pdt,

       per_people_f pp,

       mtl_system_items msi

 WHERE     ph.po_header_id = pl.PO_HEADER_ID

       AND pv.VENDOR_ID = ph.VENDOR_ID

       AND msi.ORGANIZATION_ID = ph.ORG_ID

       AND ( (    pdt.document_type_code IN ('PO', 'PA') AND pdt.document_subtype = ph.type_lookup_code))

       AND msi.inventory_item_id = pl.item_id

       AND ph.agent_id = pp.person_id

       AND ph.SEGMENT1 = 4419

P2P Cycle

SELECT prh.segment1 reqno,

       ph.SEGMENT1 po_num,

       prh.TYPE_LOOKUP_CODE po_Type,

       prh.TYPE_LOOKUP_CODE req_type,

       prl.LINE_NUM,

       ph.ORG_ID,

       pd.PO_DISTRIBUTION_ID,

       ai.INVOICE_TYPE_LOOKUP_CODE,

       ai.INVOICE_AMOUNT,

       ai.AMOUNT_PAID,

       ai.INVOICE_DATE

  FROM po_requisition_headers_all prh,

       po_requisition_lines_all prl,

       po_req_distributions_all prd,

       po_headers ph,

       po_lines pl,

       ap_invoices_all ai,

       po_distributions_all pd,

       RCV_SHIPMENT_HEADERS RSH,

       RCV_SHIPMENT_LINES RSL,

       ap_invoice_distributions_all aid

 WHERE     prh.REQUISITION_HEADER_ID = prl.REQUISITION_HEADER_ID

       AND prd.REQUISITION_LINE_ID = prl.REQUISITION_LINE_ID

       AND pd.REQ_DISTRIBUTION_ID = prd.DISTRIBUTION_ID

       AND RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID

       AND ph.PO_HEADER_ID = rsl.PO_HEADER_ID

       AND pl.PO_LINE_ID = pd.PO_LINE_ID

       AND pl.PO_HEADER_ID = ph.PO_HEADER_ID

       AND aid.INVOICE_ID = ai.INVOICE_ID

       AND pd.PO_DISTRIBUTION_ID = aid.PO_DISTRIBUTION_ID

       AND prh.segment1 = '5659'

REQUSITION & PO QUERY FOR ID’S

SELECT prh.SEGMENT1 reqno,

       ph.SEGMENT1 ponum,

       prh.REQUISITION_HEADER_ID,

       ppf.FULL_NAME preparer,

       ppf.FULL_NAME requester,

       podt.DOCUMENT_TYPE_CODE,

       plt.LINE_TYPE,

       ms.SEGMENT1 Item,

       mo.ORGANIZATION_NAME,

       pv.VENDOR_NAME supplier,

       hl.LOCATION_CODE "ship to location",

       mo.CITY "bill to location",

       ppf.FULL_NAME buyer

  FROM po_requisition_headers_all prh,

       po_requisition_lines_all prl,

       po_document_types podt,

       po_headers ph,

       po_lines pl,

       po_vendors pv,

       po_line_types plt,

       po_req_distributions_all prd,

       po_distributions_all pd,

       per_people_f ppf,

       mtl_system_items ms,

       mtl_organizations mo,

       hr_locations_all_tl hl

 WHERE     prh.REQUISITION_HEADER_ID = prl.REQUISITION_HEADER_ID

       AND ph.PO_HEADER_ID = pl.PO_HEADER_ID

       AND ph.VENDOR_ID = pv.VENDOR_ID

       AND prl.REQUISITION_LINE_ID = prd.REQUISITION_LINE_ID

       AND prd.DISTRIBUTION_ID = pd.REQ_DISTRIBUTION_ID

       AND pl.PO_LINE_ID = pd.PO_LINE_ID

       AND prh.TYPE_LOOKUP_CODE = podt.DOCUMENT_SUBTYPE

       AND prl.LINE_TYPE_ID = plt.LINE_TYPE_ID

       AND prh.PREPARER_ID = ppf.PERSON_ID

       AND prl.ITEM_ID = ms.INVENTORY_ITEM_ID

       AND prh.ORG_ID = mo.ORGANIZATION_ID

       AND prh.ORG_ID = ms.ORGANIZATION_ID

       AND ph.SHIP_TO_LOCATION_ID = hl.LOCATION_ID

       AND prh.SEGMENT1 = '5710'

ABOVE QUERY TO GET THE DETAILS FOR REQUSITION WITHOUT PO

SELECT prh.SEGMENT1 reqno,

       ph.SEGMENT1 ponum,

       prh.REQUISITION_HEADER_ID,

       ppf.FULL_NAME preparer,

       ppf.FULL_NAME requester,

       podt.DOCUMENT_TYPE_CODE,

       plt.LINE_TYPE,

       ms.SEGMENT1 Item,

       mo.ORGANIZATION_NAME,

       pv.VENDOR_NAME supplier,

       hl.LOCATION_CODE "ship to location",

       mo.CITY "bill to location",

       ppf.FULL_NAME buyer

  FROM po_requisition_headers_all prh,

       po_requisition_lines_all prl,

       po_document_types podt,

       po_headers ph,

       po_lines pl,

       po_vendors pv,

       po_line_types plt,

       po_req_distributions_all prd,

       po_distributions_all pd,

       per_people_f ppf,

       mtl_system_items ms,

       mtl_organizations mo,

       hr_locations_all_tl hl

 WHERE     prh.REQUISITION_HEADER_ID = prl.REQUISITION_HEADER_ID

       AND ph.PO_HEADER_ID(+) = pl.PO_HEADER_ID

       AND ph.VENDOR_ID = pv.VENDOR_ID(+)

       AND prl.REQUISITION_LINE_ID = prd.REQUISITION_LINE_ID

       AND prd.DISTRIBUTION_ID = pd.REQ_DISTRIBUTION_ID(+)

       AND pl.PO_LINE_ID(+) = pd.PO_LINE_ID

       AND prh.TYPE_LOOKUP_CODE = podt.DOCUMENT_SUBTYPE

       AND prl.LINE_TYPE_ID = plt.LINE_TYPE_ID

       AND prh.PREPARER_ID = ppf.PERSON_ID

       AND prl.ITEM_ID = ms.INVENTORY_ITEM_ID

       AND prh.ORG_ID = mo.ORGANIZATION_ID

       AND prh.ORG_ID = ms.ORGANIZATION_ID

       AND ph.SHIP_TO_LOCATION_ID = hl.LOCATION_ID(+)

       AND prh.SEGMENT1 = '5711'

INVOICE PO

SELECT rcvt.transaction_id,

       rcvt.transaction_type,

       rcvt.po_distribution_id,

       rcvt.po_header_id,

       rcvt.po_line_id,

       rcvt.po_line_location_id,

       rcvt.po_release_id,

       rcvt.transaction_date,

       rcvl.quantity_shipped,

       rcvl.quantity_received,

       rcvl.item_description,

       rcvl.item_id,

       rcvl.shipment_line_status_code,

       rcvl.destination_type_code,

       rcvl.to_subinventory,

       rcvh.shipment_header_id,

       rcvh.receipt_num,

       rcvh.receipt_source_code,

       rcvh.vendor_id,

       rcvh.vendor_site_id

  FROM rcv_shipment_headers rcvh,

       rcv_shipment_lines rcvl,

       rcv_transactions rcvt

 WHERE     1 = 1

       AND rcvh.shipment_header_id = rcvl.shipment_header_id

       AND rcvl.shipment_line_id = rcvt.shipment_line_id

       AND rcvh.shipment_header_id = rcvt.shipment_header_id

       AND rcvt.transaction_type = 'RECEIVE'

AP Remaining Amount  - To query remaining amounts
SELECT v.segment1 vendor_number,

       v.vendor_name VENDOR_NAME,

       i.invoice_num INVOICE_NUMBER,

       ps.due_date Due_Date,

       i.invoice_date Invoice_date,

       i.invoice_currency_code CURRENCY_CODE,

       i.exchange_rate EXCHANGE_RATE,

       ps.amount_remaining,

       i.payment_cross_rate

  FROM APPS.ap_payment_schedules_all ps,

       APPS.ap_invoices_all i,

       APPS.po_vendors v,

       APPS.po_vendor_sites_all vs

 WHERE     i.invoice_id = ps.invoice_id

       AND i.vendor_id = v.vendor_id

       AND i.vendor_site_id = vs.vendor_site_id

       AND i.cancelled_date IS NULL

       AND (NVL (ps.amount_remaining, 0) * NVL (i.exchange_rate, 1)) != 0

       AND i.payment_status_flag IN ('N', 'P')

       AND i.org_id = ps.org_id

       AND i.org_id = vs.org_id

       AND i.org_id = 204

AP Prepay Status Query

     SELECT aid.accounting_date prepay_accounting_date,

            NVL (aid1.accounting_date, aid.accounting_date)

               prepay_application_date,

            gsb.short_name sob_name,

            pv.vendor_name vendor_name,

            pvs.vendor_site_code vendor_site_code,

            aia.invoice_num invoice_num,

            aia.invoice_date invoice_date,

            aip.accounting_date payment_date,

            gcc.segment2 prepay_natural_account,

            aia.description prepay_description,

            aia.invoice_currency_code currency,

            aid.invoice_distribution_id prepay_distribution_id,

            aid.amount currency_amt,

            NVL (aid.base_amount, aid.amount) inr_amount,

            NVL (SUM (-1 * aid1.amount), 0) currency_amt_applied,

            NVL (SUM (-1 * NVL (aid1.base_amount, aid1.amount)), 0)

               inr_amount_applied,

            aid.amount + NVL (SUM (aid1.amount), 0) available_currency_amount,

              NVL (aid.base_amount, aid.amount)

            + NVL (SUM (NVL (aid1.base_amount, aid1.amount)), 0)

               available_inr_amount

       FROM ap_invoices_all aia,

            ap_invoice_distributions_all aid,

            ap_invoice_distributions_all aid1,

            po_vendors pv,

            po_vendor_sites_all pvs,

            gl_code_combinations gcc,

            ap_invoice_payments_all aip,

            gl_sets_of_books gsb

      WHERE     aia.invoice_id = aid.invoice_id

            AND aia.invoice_type_lookup_code = 'PREPAYMENT'

            AND aia.invoice_amount = aia.amount_paid

            AND aid.invoice_distribution_id = aid1.prepay_distribution_id(+)

            AND aia.vendor_id = pv.vendor_id

            AND aia.vendor_site_id = pvs.vendor_site_id

            AND pv.vendor_id = pvs.vendor_id

            AND aid.dist_code_combination_id = gcc.code_combination_id

            AND aip.invoice_id = aid.invoice_id

            AND gsb.set_of_books_id = aia.set_of_books_id

            AND aip.REVERSAL_FLAG = 'N'

   --AND DECODE(APPS.FND_GLOBAL.RESP_ID,21612,1,APPS.FND_GLOBAL.RESP_ID) = DECODE(APPS.FND_GLOBAL.RESP_ID,21612,1,DISC_SECURITY.RESP_ID)

   --AND DECODE(APPS.FND_GLOBAL.RESP_ID,21612,1,DISC_SECURITY.GL_SOB_ID) = DECODE(APPS.FND_GLOBAL.RESP_ID,21612,1,GSB.SET_OF_BOOKS_ID)

   GROUP BY gsb.short_name,

            aia.invoice_num,

            aia.invoice_date,

            aip.accounting_date,

            gcc.segment2,

            aia.description,

            aia.invoice_currency_code,

            aid.invoice_distribution_id,

            aid.amount,

            aid.base_amount,

            pv.vendor_name,

            pvs.vendor_site_code,

            aid.accounting_date,

            aid1.accounting_date