Search This Blog

Saturday, July 13, 2013

Oracle Purchasing Report Queries

Oracle Purchasing related queries/scripts

---Used 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;


-----Relation with Requistion 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;


-----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 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;


-----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;


----- 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;


-----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;


----- List and PO With there approval , invoice and payment details
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';


----- 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');