Saturday, October 28, 2017

Oracle Purchase Order (PO) Related Scripts

 ALL OPEN PO'S
SELECT h.segment1 "PO NUM",
       h.authorization_status "STATUS",
       l.line_num "SEQ NUM",
       ll.line_location_id,
       d.po_distribution_id,
       h.type_lookup_code "TYPE"
  FROM po.po_headers_all h,
       po.po_lines_all l,
       po.po_line_locations_all ll,
       po.po_distributions_all d
 WHERE     h.po_header_id = l.po_header_id
       AND ll.po_line_id = l.po_Line_id
       AND ll.line_location_id = d.line_location_id
       AND h.closed_date IS NULL
       AND h.type_lookup_code NOT IN ('QUOTATION');

 PO WITHOUT PURCHASE REQUISITION
  SELECT prh.segment1 "PR NUM",
         TRUNC (prh.creation_date) "CREATED ON",
         TRUNC (prl.creation_date) "Line Creation Date",
         prl.line_num "Seq #",
         msi.segment1 "Item Num",
         prl.item_description "Description",
         prl.quantity "Qty",
         TRUNC (prl.need_by_date) "Required By",
         ppf1.full_name "REQUESTOR",
         ppf2.agent_name "BUYER"
    FROM po.po_requisition_headers_all prh,
         po.po_requisition_lines_all prl,
         apps.per_people_f ppf1,
         (SELECT DISTINCT agent_id, agent_name FROM apps.po_agents_v) ppf2,
         po.po_req_distributions_all prd,
         inv.mtl_system_items_b msi,
         po.po_line_locations_all pll,
         po.po_lines_all pl,
         po.po_headers_all ph
   WHERE     prh.requisition_header_id = prl.requisition_header_id
         AND prl.requisition_line_id = prd.requisition_line_id
         AND ppf1.person_id = prh.preparer_id
         AND prh.creation_date BETWEEN ppf1.effective_start_date
                                   AND ppf1.effective_end_date
         AND ppf2.agent_id(+) = msi.buyer_id
         AND msi.inventory_item_id = prl.item_id
         AND msi.organization_id = prl.destination_organization_id
         AND pll.line_location_id(+) = prl.line_location_id
         AND pll.po_header_id = ph.po_header_id(+)
         AND pll.po_line_id = pl.po_line_id(+)
         AND prh.authorization_status = 'APPROVED'
         AND pll.line_location_id IS NULL
         AND prl.closed_code IS NULL
         AND NVL (prl.cancel_flag, 'N') <> 'Y'
ORDER BY 1, 2, 4;

 PURCHASE REQUISITION WITHOUT PO
  SELECT prh.segment1 "PR NUM",
         TRUNC (prh.creation_date) "CREATED ON",
         TRUNC (prl.creation_date) "Line Creation Date",
         prl.line_num "Seq #",
         msi.segment1 "Item Num",
         prl.item_description "Description",
         prl.quantity "Qty",
         TRUNC (prl.need_by_date) "Required By",
         ppf1.full_name "REQUESTOR",
         ppf2.agent_name "BUYER"
    FROM po.po_requisition_headers_all prh,
         po.po_requisition_lines_all prl,
         apps.per_people_f ppf1,
         (SELECT DISTINCT agent_id, agent_name FROM apps.po_agents_v) ppf2,
         po.po_req_distributions_all prd,
         inv.mtl_system_items_b msi,
         po.po_line_locations_all pll,
         po.po_lines_all pl,
         po.po_headers_all ph
   WHERE     prh.requisition_header_id = prl.requisition_header_id
         AND prl.requisition_line_id = prd.requisition_line_id
         AND ppf1.person_id = prh.preparer_id
         AND prh.creation_date BETWEEN ppf1.effective_start_date
                                   AND ppf1.effective_end_date
         AND ppf2.agent_id(+) = msi.buyer_id
         AND msi.inventory_item_id = prl.item_id
         AND msi.organization_id = prl.destination_organization_id
         AND pll.line_location_id(+) = prl.line_location_id
         AND pll.po_header_id = ph.po_header_id(+)
         AND pll.po_line_id = pl.po_line_id(+)
         AND prh.authorization_status = 'APPROVED'
         AND pll.line_location_id IS NULL
         AND prl.closed_code IS NULL
         AND NVL (prl.cancel_flag, 'N') <> 'Y'
ORDER BY 1, 2, 4;

 REQUISITION AND PO
SELECT r.segment1 "Req Num", ph.segment1 "PO Num"
  FROM po_headers_all ph,
       po_distributions_all d,
       po_req_distributions_all rd,
       po_requisition_lines_all rl,
       po_requisition_headers_all r
 WHERE     ph.po_header_id = d.po_header_id
       AND d.req_distribution_id = rd.distribution_id
       AND rd.requisition_line_id = rl.requisition_line_id
       AND rl.requisition_header_id = r.requisition_header_id;

 CANCELLED REQUISITIONS
SELECT prh.REQUISITION_HEADER_ID,
       prh.PREPARER_ID,
       prh.SEGMENT1 "REQ NUM",
       TRUNC (prh.CREATION_DATE),
       prh.DESCRIPTION,
       prh.NOTE_TO_AUTHORIZER
  FROM apps.Po_Requisition_headers_all prh, apps.po_action_history pah
 WHERE     action_code = 'CANCEL'
       AND pah.object_type_code = 'REQUISITION'
       AND pah.object_id = prh.REQUISITION_HEADER_ID;

 INTERNAL REQUISITION WITHOUT INTERNAL SALES ORDER
  SELECT RQH.SEGMENT1,
         RQL.LINE_NUM,
         RQL.REQUISITION_HEADER_ID,
         RQL.REQUISITION_LINE_ID,
         RQL.ITEM_ID,
         RQL.UNIT_MEAS_LOOKUP_CODE,
         RQL.UNIT_PRICE,
         RQL.QUANTITY,
         RQL.QUANTITY_CANCELLED,
         RQL.QUANTITY_DELIVERED,
         RQL.CANCEL_FLAG,
         RQL.SOURCE_TYPE_CODE,
         RQL.SOURCE_ORGANIZATION_ID,
         RQL.DESTINATION_ORGANIZATION_ID,
         RQH.TRANSFERRED_TO_OE_FLAG
    FROM PO_REQUISITION_LINES_ALL RQL, PO_REQUISITION_HEADERS_ALL RQH
   WHERE     RQL.REQUISITION_HEADER_ID = RQH.REQUISITION_HEADER_ID
         AND RQL.SOURCE_TYPE_CODE = 'INVENTORY'
         AND RQL.SOURCE_ORGANIZATION_ID IS NOT NULL
         AND NOT EXISTS
                    (SELECT 'existing internal order'
                       FROM OE_ORDER_LINES_ALL LIN
                      WHERE LIN.SOURCE_DOCUMENT_LINE_ID =
                               RQL.REQUISITION_LINE_ID
                            AND LIN.SOURCE_DOCUMENT_TYPE_ID = 10)
ORDER BY RQH.REQUISITION_HEADER_ID, RQL.LINE_NUM;

Open Purchase Requisition in PO
This can be used to identify requisitions which need to be converted to Purchase Orders
  SELECT prha.segment1 req_num,
         TRIM (TO_CHAR ( (prla.quantity * prla.unit_price), '999,999,999.99'))
            line_value,
         prla.line_num,
         prha.creation_date,
         prla.creation_date line_creation_date,
         prla.vendor_id,
         prla.vendor_site_id,
         NVL2 (
            (SELECT DISTINCT pha2.vendor_id
               FROM po.po_headers_all pha2
              WHERE     pha2.type_lookup_code = 'CONTRACT'
                    AND pha2.authorization_status = 'APPROVED'
                    AND pha2.creation_date >= '01-APR-2009'
                    AND pha2.vendor_id = prla.vendor_id
                    AND pha2.vendor_site_id = prla.vendor_site_id
                    AND pha.org_id = prha.org_id),
            'YES',
            '')
            cpa_exists,
         (SELECT MIN (pah.creation_date)
            FROM po.po_action_history pah
           WHERE     object_id = prha.requisition_header_id
                 AND pah.object_type_code = 'REQUISITION'
                 AND pah.action_code = 'SUBMIT')
            submitted_for_approval,
         (SELECT MAX (pah.creation_date)
            FROM po.po_action_history pah
           WHERE     object_id = prha.requisition_header_id
                 AND pah.object_type_code = 'REQUISITION'
                 AND pah.action_code = 'APPROVE')
            last_approved,
         mcb.segment1 || '.' || mcb.segment2 purchase_category,
         pct.NAME commodity,
         CASE
            WHEN pct.NAME IS NOT NULL
            THEN
               (SELECT papf2.full_name
                  FROM po.po_commodities_tl pct,
                       po.po_commodity_categories pcc,
                       po.po_commodity_grants pcg,
                       hr.per_all_people_f papf2
                 WHERE     pct.commodity_id = pcg.commodity_id
                       AND pcc.category_id(+) = mcb.category_id
                       AND pct.commodity_id(+) = pcc.commodity_id
                       AND papf2.person_id = pcg.person_id
                       AND SYSDATE BETWEEN papf2.effective_start_date
                                       AND papf2.effective_end_date)
         END
            commodity_buyer,
         CASE
            - PUNCHOUT
            WHEN     prla.catalog_type = 'EXTERNAL'
                 AND prla.catalog_source = 'EXTERNAL'
                 AND prla.source_type_code = 'VENDOR'
            THEN
               'PUNCHOUT'
            -INTERNAL CATALOGUE
            WHEN     prla.catalog_type = 'CATALOG'
                 AND prla.catalog_source = 'INTERNAL'
                 AND prla.source_type_code = 'VENDOR'
                 AND prla.item_id IS NULL
            THEN
               'LOCAL_CATALOGUE'
            - NON CATALOGUE:
            WHEN     prla.catalog_type = 'NONCATALOG'
                 AND prla.catalog_source = 'INTERNAL'
                 AND prla.source_type_code = 'VENDOR'
            THEN
               'NONCAT'
            ELSE
               'Other'
         END
            order_type,
         prla.suggested_vendor_product_code catalogue_code,
         prha.emergency_po_num,
         TO_CHAR (prla.need_by_date, 'DD-MON-RRRR') need_by_date,
         prla.item_description,
         hlat.description deliver_to,
         prla.quantity,
         prla.unit_price,
         TRIM (TO_CHAR ( (prla.quantity * prla.unit_price), '999,999,999.99'))
            line_value,
         prla.unit_meas_lookup_code uom,
         prla.note_to_agent note_to_buyer,
         prla.note_to_vendor note_to_supplier,
         papf.full_name req_prepaper,
         papf.email_address req_email,
         SUBSTR (haout.NAME, 0, 2) service,
         haout.NAME hr_org,
         prla.suggested_vendor_name supplier,
         prla.suggested_vendor_location site,
         prha.description req_description
    FROM po.po_requisition_headers_all prha,
         po.po_requisition_lines_all prla,
         po.po_line_locations_all plla,
         po.po_lines_all pla,
         po.po_headers_all pha,
         hr.per_all_people_f papf,
         hr.per_all_assignments_f paaf,
         hr.hr_all_organization_units_tl haout,
         hr.hr_locations_all_tl hlat,
         inv.mtl_categories_b mcb,
         po.po_commodities_tl pct,
         po.po_commodity_categories pcc
   WHERE     prha.requisition_header_id = prla.requisition_header_id
         AND plla.line_location_id(+) = prla.line_location_id
         AND pla.po_line_id(+) = plla.po_line_id
         AND prha.preparer_id = papf.person_id
         AND pha.po_header_id(+) = plla.po_header_id
         AND prla.deliver_to_location_id = hlat.location_id
         AND haout.organization_id = paaf.organization_id
         AND paaf.person_id = papf.person_id
         AND mcb.category_id = prla.category_id
         AND pcc.category_id(+) = mcb.category_id
         AND pct.commodity_id(+) = pcc.commodity_id
         AND SYSDATE BETWEEN papf.effective_start_date
                         AND papf.effective_end_date
         AND SYSDATE BETWEEN paaf.effective_start_date
                         AND paaf.effective_end_date
         AND (prla.cancel_flag IS NULL OR prla.cancel_flag = 'N')
         AND prha.authorization_status = 'APPROVED'
         AND prha.creation_date >= '01-JAN-2012'
         AND paaf.assignment_type = 'E'
         AND paaf.primary_flag = 'Y'
         AND prla.closed_date IS NULL
         AND pha.segment1 IS NULL
ORDER BY prha.segment1 DESC, prla.line_num;


Purchase Requisition Query
  SELECT prha.segment1 "REQ NO",
         prha.creation_date "REQ DATE",
         prla.quantity "REQ QTY",
         pha.segment1 "PO NO",
         pha.creation_date "PO DATE",
         SUM (pla.quantity) "PO QTY",
         pla.unit_price "PRICE",
         rsh.receipt_num,
         rt.transaction_date "RECEIPT DATE",
         rt.quantity "RECEIPT QTY",
         rt.po_unit_price "PRICE",
         aia.invoice_num,
         aia.invoice_date,
         apc.check_date,
         apc.check_number
    FROM po_requisition_headers_all prha,
         po_requisition_lines_all prla,
         po_req_distributions_all prda,
         po_headers_all pha,
         po_lines_all pla,
         po_distributions_all pda,
         rcv_transactions rt,
         rcv_shipment_lines rsl,
         rcv_shipment_headers rsh,
         ap_invoices_all aia,
         ap_invoice_lines_all aila,
         ap_invoice_distributions_all aid,
         ap_invoice_payments_all aip,
         ap_payment_schedules_all apsa,
         ap_checks_all apc
   WHERE     prha.requisition_header_id = prla.requisition_header_id
         AND prla.requisition_line_id = prda.requisition_line_id
         AND pda.req_distribution_id = prda.distribution_id
         AND pla.po_line_id = pda.po_line_id
         AND pha.po_header_id = pla.po_header_id
         AND pda.po_distribution_id = rt.po_distribution_id
         AND rsh.shipment_header_id = rt.shipment_header_id
         AND rt.transaction_id = aid.rcv_transaction_id
         AND aila.invoice_id = aid.invoice_id
         AND aia.invoice_id = aila.invoice_id
         AND aia.invoice_id = aip.invoice_id
         AND aia.invoice_id = apsa.invoice_id
         AND apc.check_id = aip.check_id
         AND prha.segment1 = '14663'
         AND pha.SEGMENT1 = '7162'
GROUP BY prha.segment1,
         prha.creation_date,
         prla.quantity,
         pha.segment1,
         pha.creation_date,
         pla.quantity,
         pla.unit_price,
         rsh.receipt_num,
         rt.transaction_date,
         rt.quantity,
         rt.po_unit_price,
         aia.invoice_num,
         aia.invoice_date,
         apc.check_date,
         apc.check_number

PO QUERIES
Technical Queries related to Oracle Purchasing
1] TO LIST OUT ALL CANCEL REQUISITIONS:->> list My cancel Requistion
SELECT prh.REQUISITION_HEADER_ID,
       prh.PREPARER_ID,
       prh.SEGMENT1 "REQ NUM",
       TRUNC (prh.CREATION_DATE),
       prh.DESCRIPTION,
       prh.NOTE_TO_AUTHORIZER
  FROM Po_Requisition_headers_all prh, apps.po_action_history pah
 WHERE     Action_code = 'CANCEL'
       AND pah.object_type_code = 'REQUISITION'
       AND pah.object_id = prh.REQUISITION_HEADER_ID

2] TO LIST ALL INTERNAL REQUISITIONS THAT DO NOT HAVE AN ASSOCIATED INTERNAL SALES ORDER
  SELECT RQH.SEGMENT1 REQ_NUM,
         RQL.LINE_NUM,
         RQL.REQUISITION_HEADER_ID,
         RQL.REQUISITION_LINE_ID,
         RQL.ITEM_ID,
         RQL.UNIT_MEAS_LOOKUP_CODE,
         RQL.UNIT_PRICE,
         RQL.QUANTITY,
         RQL.QUANTITY_CANCELLED,
         RQL.QUANTITY_DELIVERED,
         RQL.CANCEL_FLAG,
         RQL.SOURCE_TYPE_CODE,
         RQL.SOURCE_ORGANIZATION_ID,
         RQL.DESTINATION_ORGANIZATION_ID,
         RQH.TRANSFERRED_TO_OE_FLAG
    FROM PO_REQUISITION_LINES_ALL RQL, PO_REQUISITION_HEADERS_ALL RQH
   WHERE     RQL.REQUISITION_HEADER_ID = RQH.REQUISITION_HEADER_ID
         AND RQL.SOURCE_TYPE_CODE = 'INVENTORY'
         AND RQL.SOURCE_ORGANIZATION_ID IS NOT NULL
         AND NOT EXISTS
                    (SELECT 'existing internal order'
                       FROM OE_ORDER_LINES_ALL LIN
                      WHERE LIN.SOURCE_DOCUMENT_LINE_ID =
                               RQL.REQUISITION_LINE_ID
                            AND LIN.SOURCE_DOCUMENT_TYPE_ID = 10)
ORDER BY RQH.REQUISITION_HEADER_ID, RQL.LINE_NUM;


3] Display what requisition and PO are linked(Relation with Requisition and PO )>>
SELECT r.segment1 "Req Num", p.segment1 "PO Num"
  FROM po_headers_all p,
       po_distributions_all d,
       po_req_distributions_all rd,
       po_requisition_lines_all rl,
       po_requisition_headers_all r
 WHERE     p.po_header_id = d.po_header_id
       AND d.req_distribution_id = rd.distribution_id
       AND rd.requisition_line_id = rl.requisition_line_id
       AND rl.requisition_header_id = r.requisition_header_id

4] List all Purchase Requisition without a Purchase Order that means a PR has not been autocreated to PO. (Purchase Requisition without a Purchase Order)>>
  SELECT prh.segment1 "PR NUM",
         TRUNC (prh.creation_date) "CREATED ON",
         TRUNC (prl.creation_date) "Line Creation Date",
         prl.line_num "Seq #",
         msi.segment1 "Item Num",
         prl.item_description "Description",
         prl.quantity "Qty",
         TRUNC (prl.need_by_date) "Required By",
         ppf1.full_name "REQUESTOR",
         ppf2.agent_name "BUYER"
    FROM po.po_requisition_headers_all prh,
         po.po_requisition_lines_all prl,
         apps.per_people_f ppf1,
         (SELECT DISTINCT agent_id, agent_name FROM apps.po_agents_v) ppf2,
         po.po_req_distributions_all prd,
         inv.mtl_system_items_b msi,
         po.po_line_locations_all pll,
         po.po_lines_all pl,
         po.po_headers_all ph
   WHERE     prh.requisition_header_id = prl.requisition_header_id
         AND prl.requisition_line_id = prd.requisition_line_id
         AND ppf1.person_id = prh.preparer_id
         AND prh.creation_date BETWEEN ppf1.effective_start_date
                                   AND ppf1.effective_end_date
         AND ppf2.agent_id(+) = msi.buyer_id
         AND msi.inventory_item_id = prl.item_id
         AND msi.organization_id = prl.destination_organization_id
         AND pll.line_location_id(+) = prl.line_location_id
         AND pll.po_header_id = ph.po_header_id(+)
         AND PLL.PO_LINE_ID = PL.PO_LINE_ID(+)
         AND PRH.AUTHORIZATION_STATUS = 'APPROVED'
         AND PLL.LINE_LOCATION_ID IS NULL
         AND PRL.CLOSED_CODE IS NULL
         AND NVL (PRL.CANCEL_FLAG, 'N') <> 'Y'
ORDER BY 1, 2

5] list all information form PR to PO …as a requisition moved from different stages till converting into PR. This query capture all details related to that PR to PO.>> LIST AND ALL DATA ENTRY FROM PR TILL PO
  SELECT DISTINCT u.description "Requestor",
                  porh.segment1 AS "Req Number",
                  TRUNC (porh.Creation_Date) "Created On",
                  pord.LAST_UPDATED_BY,
                  porh.Authorization_Status "Status",
                  porh.Description "Description",
                  poh.segment1 "PO Number",
                  TRUNC (poh.Creation_date) "PO Creation Date",
                  poh.AUTHORIZATION_STATUS "PO Status",
                  TRUNC (poh.Approved_Date) "Approved Date"
    FROM apps.po_headers_all poh,
         apps.po_distributions_all pod,
         apps.po_req_distributions_all pord,
         apps.po_requisition_lines_all porl,
         apps.po_requisition_headers_all porh,
         apps.fnd_user u
   WHERE     porh.requisition_header_id = porl.requisition_header_id
         AND porl.requisition_line_id = pord.requisition_line_id
         AND pord.distribution_id = pod.req_distribution_id(+)
         AND pod.po_header_id = poh.po_header_id(+)
         AND porh.created_by = u.user_id
ORDER BY 2

6] Identifying all PO’s which does not have any PR’s>>LIST ALL PURCHASE REQUISITION WITHOUT A PURCHASE ORDER THAT MEANS A PR HAS NOT BEEN AUTOCREATED TO PO.
  SELECT prh.segment1 "PR NUM",
         TRUNC (prh.creation_date) "CREATED ON",
         TRUNC (prl.creation_date) "Line Creation Date",
         prl.line_num "Seq #",
         msi.segment1 "Item Num",
         prl.item_description "Description",
         prl.quantity "Qty",
         TRUNC (prl.need_by_date) "Required By",
         ppf1.full_name "REQUESTOR",
         ppf2.agent_name "BUYER"
    FROM po.po_requisition_headers_all prh,
         po.po_requisition_lines_all prl,
         apps.per_people_f ppf1,
         (SELECT DISTINCT agent_id, agent_name FROM apps.po_agents_v) ppf2,
         po.po_req_distributions_all prd,
         inv.mtl_system_items_b msi,
         po.po_line_locations_all pll,
         po.po_lines_all pl,
         po.po_headers_all ph
   WHERE     prh.requisition_header_id = prl.requisition_header_id
         AND prl.requisition_line_id = prd.requisition_line_id
         AND ppf1.person_id = prh.preparer_id
         AND prh.creation_date BETWEEN ppf1.effective_start_date
                                   AND ppf1.effective_end_date
         AND ppf2.agent_id(+) = msi.buyer_id
         AND msi.inventory_item_id = prl.item_id
         AND msi.organization_id = prl.destination_organization_id
         AND pll.line_location_id(+) = prl.line_location_id
         AND pll.po_header_id = ph.po_header_id(+)
         AND PLL.PO_LINE_ID = PL.PO_LINE_ID(+)
         AND PRH.AUTHORIZATION_STATUS = 'APPROVED'
         AND PLL.LINE_LOCATION_ID IS NULL
         AND PRL.CLOSED_CODE IS NULL
         AND NVL (PRL.CANCEL_FLAG, 'N') <> 'Y'
ORDER BY 1, 2

7] Relation between Requisition and PO tables>>Here is link:
PO_DISTRIBUTIONS_ALL =>PO_HEADER_ID
, REQ_DISTRIBUTION_IDPO_HEADERS_ALL=>PO_HEADER_ID
, SEGMENT1PO_REQ_DISTRIBUTIONS_ALL =>DISTRIBUTION_ID
, REQUISITION_LINE_IDPO_REQUISITION_LINES_ALL =>REQUISITION_LINE_ID
,PO_REQUISITION_HEADERS_ALL =>REQUISITION_HEADER_ID, REQUISITION_LINE_ID, SEGMENT1

What you have to make a join on PO_DISTRIBUTIONS_ALL (REQ_DISTRIBUTION_ID) and PO_REQ_DISTRIBUTIONS_ALL (DISTRIBUTION_ID) to see if there is a PO for the req.
You need to find table which hold PO Approval path
These two table keeps the data:
PO_APPROVAL_LIST_HEADERS
PO_APPROVAL_LIST_LINES

8] List all the PO’s with there approval ,invoice and Payment Details>>LIST AND PO WITH THERE APPROVAL , INVOICE AND PAYMENT DETAIL
SELECT a.org_id "ORG ID",
       E.SEGMENT1 "VENDOR NUM",
       e.vendor_name "SUPPLIER NAME",
       UPPER (e.vendor_type_lookup_code) "VENDOR TYPE",
       f.vendor_site_code "VENDOR SITE CODE",
       f.ADDRESS_LINE1 "ADDRESS",
       f.city "CITY",
       f.country "COUNTRY",
       TO_CHAR (TRUNC (d.CREATION_DATE)) "PO Date",
       d.segment1 "PO NUM",
       d.type_lookup_code "PO Type",
       c.quantity_ordered "QTY ORDERED",
       c.quantity_cancelled "QTY CANCELLED",
       g.item_id "ITEM ID",
       g.item_description "ITEM DESCRIPTION",
       g.unit_price "UNIT PRICE",
       (NVL (c.quantity_ordered, 0) - NVL (c.quantity_cancelled, 0))
       * NVL (g.unit_price, 0)
          "PO Line Amount",
       (SELECT DECODE (ph.approved_FLAG, 'Y', 'Approved')
          FROM po.po_headers_all ph
         WHERE ph.po_header_ID = d.po_header_id)
          "PO Approved?",
       a.invoice_type_lookup_code "INVOICE TYPE",
       a.invoice_amount "INVOICE AMOUNT",
       TO_CHAR (TRUNC (a.INVOICE_DATE)) "INVOICE DATE",
       a.invoice_num "INVOICE NUMBER",
       (SELECT DECODE (x.MATCH_STATUS_FLAG, 'A', 'Approved')
          FROM ap.ap_invoice_distributions_all x
         WHERE x.INVOICE_DISTRIBUTION_ID = b.invoice_distribution_id)
          "Invoice Approved?",
       a.amount_paid,
       h.amount,
       h.check_id,
       h.invoice_payment_id "Payment Id",
       i.check_number "Cheque Number",
       TO_CHAR (TRUNC (i.check_DATE)) "PAYMENT DATE"
  FROM AP.AP_INVOICES_ALL A,
       AP.AP_INVOICE_DISTRIBUTIONS_ALL B,
       PO.PO_DISTRIBUTIONS_ALL C,
       PO.PO_HEADERS_ALL D,
       PO.PO_VENDORS E,
       PO.PO_VENDOR_SITES_ALL F,
       PO.PO_LINES_ALL G,
       AP.AP_INVOICE_PAYMENTS_ALL H,
       AP.AP_CHECKS_ALL I
 WHERE     a.invoice_id = b.invoice_id
       AND b.po_distribution_id = c.po_distribution_id(+)
       AND c.po_header_id = d.po_header_id(+)
       AND e.vendor_id(+) = d.VENDOR_ID
       AND f.vendor_site_id(+) = d.vendor_site_id
       AND d.po_header_id = g.po_header_id
       AND c.po_line_id = g.po_line_id
       AND a.invoice_id = h.invoice_id
       AND h.check_id = i.check_id
       AND f.vendor_site_id = i.vendor_site_id
       AND c.PO_HEADER_ID IS NOT NULL
       AND a.payment_status_flag = 'Y'
       AND d.type_lookup_code != 'BLANKET'


10] To know the link to GL_JE_LINES table for purchasing accrual and budgetary control actions..The budgetary (encumbrance) and accrual actions in the purchasing module generate records that will be imported into GL for the corresponding accrual and budgetary journals.
The following reference fields are used to capture and keep PO information in the GL_JE_LINES table.
These reference fields are populated when the Journal source (JE_SOURCE in GL_JE_HEADERS) isPurchasing.
Budgetary Records from PO (These include reservations, reversals and cancellations):
REFERENCE_1- Source (PO or REQ)
REFERENCE_2- PO Header ID or Requisition Header ID (from po_headers_all.po_header_id orpo_requisition_headers_all.requisition_header_id)
REFERENCE_3- Distribution ID (from po_distributions_all.po_distribution_id orpo_req_distributions_all.distribution_id)
REFERENCE_4- Purchase Order or Requisition number (from po_headers_all.segment1 orpo_requisition_headers_all.segment1)
REFERENCE_5- (Autocreated Purchase Orders only) Backing requisition number (from po_requisition_headers_all.segment1)
Accrual Records from PO:
REFERENCE_1- Source (PO)
REFERENCE_2- PO Header ID (from po_headers_all.po_header_id)
REFERENCE_3- Distribution ID (from po_distributions_all.po_distribution_id
REFERENCE_4- Purchase Order number (from po_headers_all.segment1)
REFERENCE_5- (ON LINE ACCRUALS ONLY) Receiving Transaction ID (from rcv_receiving_sub_ledger.rcv_transaction_id)
Take a note for Period end accruals, the REFERENCE_5 column is not used.

11] List all open PO'S>>
SELECT h.segment1 "PO NUM",
       h.authorization_status "STATUS",
       l.line_num "SEQ NUM",
       ll.line_location_id,
       d.po_distribution_id,
       h.type_lookup_code "TYPE"
  FROM po.po_headers_all h,
       po.po_lines_all l,
       po.po_line_locations_all ll,
       po.po_distributions_all d
 WHERE     h.po_header_id = l.po_header_id
       AND ll.po_line_id = l.po_Line_id
       AND ll.line_location_id = d.line_location_id
       AND h.closed_date IS NULL
       AND h.type_lookup_code NOT IN ('QUOTATION')

12] There are different authorization_status can a requisition have.Approved
Cancelled
In Process
Incomplete
Pre-Approved
Rejected
and you should note: When we finally close the requisition from Requisition Summary form the authorization_status of the requisition does not change. Instead its closed_code becomesFINALLY CLOSED.

13] A standard Quotations one that you can tie back to a PO.Navigate to RFQ -> Auto create -> enter a PO and reference it back.14] To debug for a PO , where should I start.Thats is possible, your PO get stuck somewhere, so what you have to do is to analyze which stage it stucked.Get po_header_id first and run each query and then analyze the data.For better understanding this is splited into 5 major stages.

Stage 1: PO Creation :
PO_HEADERS_ALL
select po_header_id from po_headers_all where segment1 =;
select * from po_headers_all where po_header_id =;

po_lines_all
select * from po_lines_all where po_header_id =;

po_line_locations_all
select * from po_line_locations_all where po_header_id =;

po_distributions_all
select * from po_distributions_all where po_header_id =;

po_releases_all
SELECT * FROM po_releases_all WHERE po_header_id =;

Stage 2: Once PO is received data is moved to respective receving tables and inventory tables
RCV_SHIPMENT_HEADERS
select * from rcv_shipment_headers where shipment_header_id in(select shipment_header_id from rcv_shipment_lineswhere po_header_id =);

RCV_SHIPMENT_LINES
select * from rcv_shipment_lines where po_header_id =;

RCV_TRANSACTIONS
select * from rcv_transactions where po_header_id =;

RCV_ACCOUNTING_EVENTS
SELECT * FROM rcv_Accounting_Events WHERE rcv_transaction_id IN(select transaction_id from rcv_transactionswhere po_header_id =);

RCV_RECEIVING_SUB_LEDGER
select * from rcv_receiving_sub_ledger where rcv_transaction_id in (select transaction_id from rcv_transactions where po_header_id =);

RCV_SUB_LEDGER_DETAILS
select * from rcv_sub_ledger_detailswhere rcv_transaction_id in (select transaction_id from rcv_transactions where po_header_id =);

MTL_MATERIAL_TRANSACTIONS
select * from mtl_material_transactions where transaction_source_id =;

MTL_TRANSACTION_ACCOUNTS
select * from mtl_transaction_accounts where transaction_id in ( select transaction_id from mtl_material_transactions where transaction_source_id = =);

Stage 3: Invoicing details
AP_INVOICE_DISTRIBUTIONS_ALL
select * from ap_invoice_distributions_all where po_distribution_id in ( select po_distribution_id from po_distributions_all where po_header_id =);

AP_INVOICES_ALL
select * from ap_invoices_all where invoice_id in(select invoice_id from ap_invoice_distributions_all where po_distribution_id in( select po_distribution_id from po_distributions_all where po_header_id =));

Stage 4 : Many Time there is tie up with Project related PO
PA_EXPENDITURE_ITEMS_ALL
select * from pa_expenditure_items_all peia where peia.orig_transaction_reference in( select to_char(transaction_id) from mtl_material_transactionswhere transaction_source_id = );

Stage 5 : General Ledger
Prompt 17. GL_BC_PACKETS ..This is for encumbrances
SELECT * FROM gl_bc_packets WHERE reference2 IN (’‘);

GL_INTERFACE
SELECT *FROM GL_INTERFACE GLIWHERE user_je_source_name =PurchasingAND gl_sl_link_table =RSLAND reference21=POAND EXISTS( SELECT 1FROM rcv_receiving_sub_ledger RRSLWHERE GLI.reference22 =RRSL.reference2AND GLI.reference23 =RRSL.reference3AND GLI.reference24 =RRSL.reference4AND RRSL.rcv_transaction_id in(select transaction_id from rcv_transactionswhere po_header_id ));

GL_IMPORT_REFERENCES
SELECT *FROM gl_import_references GLIRWHERE reference_1=POAND gl_sl_link_table =RSLAND EXISTS( SELECT 1FROM rcv_receiving_sub_ledger RRSLWHERE GLIR.reference_2 =RRSL.reference2AND GLIR.reference_3 =RRSL.reference3AND GLIR.reference_4 =RRSL.reference4AND RRSL.rcv_transaction_id in(select transaction_id from rcv_transactions where po_header_id =))



No comments:

Post a Comment

Best Blogger TipsGet Flower Effect