Saturday, June 6, 2015

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


No comments:

Post a Comment

Best Blogger TipsGet Flower Effect