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');
No comments:
Post a Comment