PO SQLS
-- -----------------------------------------------------------------------------------
-- Purchase/Internal Requisition details
-- -----------------------------------------------------------------------------------
SELECT prh.segment1 "Req #", prh.creation_date, prh.created_by,
poh.segment1 "PO #", ppx.full_name "Requestor Name",
prh.description "Req Description", prh.authorization_status,
prh.note_to_authorizer, prh.type_lookup_code, prl.line_num,
prl.line_type_id, prl.item_description, prl.unit_meas_lookup_code,
prl.unit_price, prl.quantity, prl.quantity_delivered, prl.need_by_date,
prl.note_to_agent, prl.currency_code, prl.rate_type, prl.rate_date,
prl.quantity_cancelled, prl.cancel_date, prl.cancel_reason
FROM po_requisition_headers_all prh,
po_requisition_lines_all prl,
po_req_distributions_all prd,
per_people_x ppx,
po_headers_all poh,
po_distributions_all pda
WHERE prh.requisition_header_id = prl.requisition_header_id
AND ppx.person_id = prh.preparer_id
AND prh.type_lookup_code = 'PURCHASE' --'INTERNAL'
AND prd.requisition_line_id = prl.requisition_line_id
AND pda.req_distribution_id = prd.distribution_id
AND pda.po_header_id = poh.po_header_id
-- -----------------------------------------------------------------------------------
-- SQL Query to extract Oracle Purchase Order Information
-- -----------------------------------------------------------------------------------
SELECT
poh.po_header_id,
poh.type_lookup_code PO_TYPE,
poh.authorization_status PO_STATUS,
poh.segment1 PO_NUMBER,
pov.vendor_name SUPPLIER_NAME,
povs.vendor_site_code Location,
hrls.location_code Ship_To,
hrlb.location_code Bill_to,
pol.line_num ,
msib.segment1 Item,
pol.unit_price,
pol.quantity,
pod.amount_billed Amount,
pod.destination_subinventory,
ppf.full_name Buyer_Name,
poh.closed_Code
FROM
PO_HEADERS_ALL poh,
PO_LINES_ALL pol,
mtl_system_items_b msib,
PO_LINE_LOCATIONS_ALL poll,
PO_DISTRIBUTIONS_ALL pod,
po_vendors pov,
po_vendor_sites_All povs,
hr_locations_all hrls,
hr_locations_all hrlb,
per_all_people_f ppf,
po_line_types polt
WHERE
1 =1
AND polt.line_type_id = pol.line_type_id
AND povs.vendor_site_id = poh.vendor_site_id
AND pov.vendor_id = poh.vendor_id
AND pol.item_id = msib.inventory_item_id
AND msib.organization_id = &application_id
AND poh.po_header_id = pol.po_header_id
AND pol.po_line_id = pod.po_line_id
AND poll.line_location_id = pod.line_location_id
AND poh.ship_to_location_id = hrls.location_id
AND poh.bill_to_location_id = hrlb.location_id
AND poh.agent_id = ppf.person_id
AND poh.segment1 = &LP1;
-- -----------------------------------------------------------------------------------
-- Receiving transactions Details for PO
-- -----------------------------------------------------------------------------------
SELECT ph.segment1 po_num, ood.organization_name, pol.po_line_id,
pll.quantity, rsh.receipt_source_code, rsh.vendor_id,
rsh.vendor_site_id, rsh.organization_id, rsh.shipment_num,
rsh.receipt_num, rsh.ship_to_location_id, rsh.bill_of_lading,
rsl.shipment_line_id, rsl.quantity_shipped, rsl.quantity_received,
rct.transaction_type, rct.transaction_id,
NVL (rct.source_doc_quantity, 0) transaction_qty
FROM rcv_transactions rct,
rcv_shipment_headers rsh,
rcv_shipment_lines rsl,
po_lines_all pol,
po_line_locations_all pll,
po_headers_all ph,
org_organization_definitions ood
WHERE 1 = 1
AND rct.po_header_id = ph.po_header_id
AND rct.po_line_location_id = pll.line_location_id
AND rct.po_line_id = pol.po_line_id
AND rct.shipment_line_id = rsl.shipment_line_id
AND rsl.shipment_header_id = rsh.shipment_header_id
AND rsh.ship_to_org_id = ood.organization_id
ORDER BY rct.transaction_id
-- -----------------------------------------------------------------------------------
-- Internal Requisitions not having 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
-- -----------------------------------------------------------------------------------
-- Purchase Requisition not having 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.pl_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
-- -----------------------------------------------------------------------------------
-- PO’s which does not have any 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
-- -----------------------------------------------------------------------------------
-- Purchase orders with Approval, Invoice & 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'
-- -----------------------------------------------------------------------------------
-- Purchase/Internal Requisition details
-- -----------------------------------------------------------------------------------
SELECT prh.segment1 "Req #", prh.creation_date, prh.created_by,
poh.segment1 "PO #", ppx.full_name "Requestor Name",
prh.description "Req Description", prh.authorization_status,
prh.note_to_authorizer, prh.type_lookup_code, prl.line_num,
prl.line_type_id, prl.item_description, prl.unit_meas_lookup_code,
prl.unit_price, prl.quantity, prl.quantity_delivered, prl.need_by_date,
prl.note_to_agent, prl.currency_code, prl.rate_type, prl.rate_date,
prl.quantity_cancelled, prl.cancel_date, prl.cancel_reason
FROM po_requisition_headers_all prh,
po_requisition_lines_all prl,
po_req_distributions_all prd,
per_people_x ppx,
po_headers_all poh,
po_distributions_all pda
WHERE prh.requisition_header_id = prl.requisition_header_id
AND ppx.person_id = prh.preparer_id
AND prh.type_lookup_code = 'PURCHASE' --'INTERNAL'
AND prd.requisition_line_id = prl.requisition_line_id
AND pda.req_distribution_id = prd.distribution_id
AND pda.po_header_id = poh.po_header_id
-- -----------------------------------------------------------------------------------
-- SQL Query to extract Oracle Purchase Order Information
-- -----------------------------------------------------------------------------------
SELECT
poh.po_header_id,
poh.type_lookup_code PO_TYPE,
poh.authorization_status PO_STATUS,
poh.segment1 PO_NUMBER,
pov.vendor_name SUPPLIER_NAME,
povs.vendor_site_code Location,
hrls.location_code Ship_To,
hrlb.location_code Bill_to,
pol.line_num ,
msib.segment1 Item,
pol.unit_price,
pol.quantity,
pod.amount_billed Amount,
pod.destination_subinventory,
ppf.full_name Buyer_Name,
poh.closed_Code
FROM
PO_HEADERS_ALL poh,
PO_LINES_ALL pol,
mtl_system_items_b msib,
PO_LINE_LOCATIONS_ALL poll,
PO_DISTRIBUTIONS_ALL pod,
po_vendors pov,
po_vendor_sites_All povs,
hr_locations_all hrls,
hr_locations_all hrlb,
per_all_people_f ppf,
po_line_types polt
WHERE
1 =1
AND polt.line_type_id = pol.line_type_id
AND povs.vendor_site_id = poh.vendor_site_id
AND pov.vendor_id = poh.vendor_id
AND pol.item_id = msib.inventory_item_id
AND msib.organization_id = &application_id
AND poh.po_header_id = pol.po_header_id
AND pol.po_line_id = pod.po_line_id
AND poll.line_location_id = pod.line_location_id
AND poh.ship_to_location_id = hrls.location_id
AND poh.bill_to_location_id = hrlb.location_id
AND poh.agent_id = ppf.person_id
AND poh.segment1 = &LP1;
-- -----------------------------------------------------------------------------------
-- Receiving transactions Details for PO
-- -----------------------------------------------------------------------------------
SELECT ph.segment1 po_num, ood.organization_name, pol.po_line_id,
pll.quantity, rsh.receipt_source_code, rsh.vendor_id,
rsh.vendor_site_id, rsh.organization_id, rsh.shipment_num,
rsh.receipt_num, rsh.ship_to_location_id, rsh.bill_of_lading,
rsl.shipment_line_id, rsl.quantity_shipped, rsl.quantity_received,
rct.transaction_type, rct.transaction_id,
NVL (rct.source_doc_quantity, 0) transaction_qty
FROM rcv_transactions rct,
rcv_shipment_headers rsh,
rcv_shipment_lines rsl,
po_lines_all pol,
po_line_locations_all pll,
po_headers_all ph,
org_organization_definitions ood
WHERE 1 = 1
AND rct.po_header_id = ph.po_header_id
AND rct.po_line_location_id = pll.line_location_id
AND rct.po_line_id = pol.po_line_id
AND rct.shipment_line_id = rsl.shipment_line_id
AND rsl.shipment_header_id = rsh.shipment_header_id
AND rsh.ship_to_org_id = ood.organization_id
ORDER BY rct.transaction_id
-- -----------------------------------------------------------------------------------
-- Internal Requisitions not having 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
-- -----------------------------------------------------------------------------------
-- Purchase Requisition not having 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.pl_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
-- -----------------------------------------------------------------------------------
-- PO’s which does not have any 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
-- -----------------------------------------------------------------------------------
-- Purchase orders with Approval, Invoice & 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'
0 comments:
Post a Comment