Find number of PO's based on Organization Name.
SELECT
hou.name AS organization_name,
COUNT(pha.po_header_id) AS standard_po_count
FROM
po_headers_all pha,
hr_operating_units hou
WHERE
pha.prc_bu_id = hou.organization_id
AND pha.type_lookup_code = 'STANDARD'
GROUP BY
hou.name
--3198
-------------
Standard PO count query
SELECT
hou.name OrgName,
count(*)
FROM po_headers_all pha,
hr_operating_units hou
WHERE type_lookup_code = 'STANDARD' --'BLANKET' or 'CONTRACT' AND
pha.prc_bu_id = hou.organization_id
GROUP BY hou. NAME
--------------------------
--Write query to get PO details with no supplier.
SELECT
pha.segment1 AS po_number,
pha.document_status,
pha.creation_date,
ps.segment1 AS supplier_number,
hp.party_name AS supplier_name
FROM
po_headers_all pha,
poz_suppliers ps,
hz_parties hp
WHERE
pha.vendor_id = ps.vendor_id(+)
AND ps.party_id = hp.party_id(+)
AND pha.vendor_id IS NULL
ORDER BY
pha.creation_date DESC
--------------------
Write a query to get PO details with Supplier
SELECT
pha.segment1 AS po_number,
pha.document_status,
pha.creation_date,
ps.segment1 AS supplier_number,
hp.party_name AS supplier_name
FROM
po_headers_all pha,
poz_suppliers ps,
hz_parties hp
WHERE
pha.vendor_id = ps.vendor_id(+)
AND ps.party_id = hp.party_id(+)
AND pha.vendor_id IS NOT NULL
ORDER BY
pha.creation_date DESC
----------------------
Write a query to list every PO and the total number of lines associated with it.
SELECT
pha.segment1 AS po_number,
hp.party_name AS supplier_name,
pha.document_status,
COUNT(pla.po_line_id) AS total_lines
FROM
po_headers_all pha,
po_lines_all pla,
poz_suppliers ps,
hz_parties hp
WHERE
pha.po_header_id = pla.po_header_id(+)
AND pha.vendor_id = ps.vendor_id(+)
AND ps.party_id = hp.party_id(+)
GROUP BY
pha.segment1,
hp.party_name,
pha.document_status
ORDER BY
total_lines DESC
-------------------------
Write a query to get Line Amount for every PO
SELECT
pha.segment1 AS po_number,
hp.party_name AS supplier_name,
pha.currency_code,
pha.document_status,
SUM(NVL(plla.amount, NVL(plla.quantity, 0) * NVL(pla.unit_price, 0))) AS total_po_amount,
COUNT(DISTINCT pla.po_line_id) AS total_lines
FROM
po_headers_all pha,
po_lines_all pla,
po_line_locations_all plla,
poz_suppliers ps,
hz_parties hp
WHERE
pha.po_header_id = pla.po_header_id(+)
AND pla.po_line_id = plla.po_line_id(+)
AND pha.vendor_id = ps.vendor_id(+)
AND ps.party_id = hp.party_id(+)
GROUP BY
pha.segment1,
hp.party_name,
pha.currency_code,
pha.document_status
ORDER BY
pha.segment1 DESC
---------------
Write a query to get Line Amount for every PO along with BU name.
SELECT
hou.name AS organization_name,
pha.segment1 AS po_number,
hp.party_name AS supplier_name,
pha.currency_code,
pha.document_status,
SUM(NVL(plla.amount, NVL(plla.quantity, 0) * NVL(pla.unit_price, 0))) AS total_po_amount,
COUNT(DISTINCT pla.po_line_id) AS total_lines
FROM
po_headers_all pha,
po_lines_all pla,
po_line_locations_all plla,
poz_suppliers ps,
hz_parties hp,
hr_organization_units hou
WHERE
pha.po_header_id = pla.po_header_id(+)
AND pla.po_line_id = plla.po_line_id(+)
AND pha.vendor_id = ps.vendor_id(+)
AND ps.party_id = hp.party_id(+)
AND pha.prc_bu_id = hou.organization_id(+)
GROUP BY
hou.name,
pha.segment1,
hp.party_name,
pha.currency_code,
pha.document_status
ORDER BY
hou.name ASC,
pha.segment1 DESC
=====================================
Which PO has maximum amount among all the BU.
SELECT * FROM (
SELECT
hou.name AS organization_name,
pha.segment1 AS po_number,
hp.party_name AS supplier_name,
pha.currency_code,
pha.document_status,
SUM(NVL(plla.amount, NVL(plla.quantity, 0) * NVL(pla.unit_price, 0))) AS total_po_amount
FROM
po_headers_all pha,
po_lines_all pla,
po_line_locations_all plla,
poz_suppliers ps,
hz_parties hp,
hr_organization_units hou
WHERE
pha.po_header_id = pla.po_header_id(+)
AND pla.po_line_id = plla.po_line_id(+)
AND pha.vendor_id = ps.vendor_id(+)
AND ps.party_id = hp.party_id(+)
AND pha.prc_bu_id = hou.organization_id(+)
GROUP BY
hou.name,
pha.segment1,
hp.party_name,
pha.currency_code,
pha.document_status
ORDER BY
6 DESC -- Sorts by the 6th column (total_po_amount)
)
WHERE ROWNUM = 1
=================
Lowest PO Amount among all the BU's
SELECT * FROM (
SELECT
hou.name AS organization_name,
pha.segment1 AS po_number,
hp.party_name AS supplier_name,
pha.currency_code,
pha.document_status,
SUM(NVL(plla.amount, NVL(plla.quantity, 0) * NVL(pla.unit_price, 0))) AS total_po_amount
FROM
po_headers_all pha,
po_lines_all pla,
po_line_locations_all plla,
poz_suppliers ps,
hz_parties hp,
hr_organization_units hou
WHERE
pha.po_header_id = pla.po_header_id(+)
AND pla.po_line_id = plla.po_line_id(+)
AND pha.vendor_id = ps.vendor_id(+)
AND ps.party_id = hp.party_id(+)
AND pha.prc_bu_id = hou.organization_id(+)
GROUP BY
hou.name,
pha.segment1,
hp.party_name,
pha.currency_code,
pha.document_status
HAVING SUM(NVL(plla.amount, NVL(plla.quantity, 0) * NVL(pla.unit_price, 0))) > 0
ORDER BY
total_po_amount ASC
)
WHERE ROWNUM = 1
=========================================
Real-Time Reports related to P2P cycle
1.Purchase Order PDF Report (Standard/Custom)
-----------------------
SELECT
hou.name AS business_unit_name,
pha.segment1 AS po_number,
pha.document_status,
pha.currency_code,
pha.creation_date,
-- Supplier Details
hp.party_name AS supplier_name,
ps.segment1 AS supplier_number,
pasa.vendor_site_code AS supplier_site,
-- Line Details
pla.line_num,
pla.item_description,
pla.unit_price,
pla.quantity,
pla.uom_code,
-- Amount Calculation
NVL(plla.amount, NVL(plla.quantity, 0) * NVL(pla.unit_price, 0)) AS line_amount,
-- Ship-to/Bill-to (Using Base Table for IDs)
hla_ship.location_code AS ship_to_location,
hla_bill.location_code AS bill_to_location
FROM
po_headers_all pha,
po_lines_all pla,
po_line_locations_all plla,
poz_suppliers ps,
hz_parties hp,
poz_supplier_sites_all_m pasa,
hr_organization_units hou,
hr_locations_all hla_ship,
hr_locations_all hla_bill
WHERE 1=1
AND pha.po_header_id = pla.po_header_id
AND pla.po_line_id = plla.po_line_id
AND pha.vendor_id = ps.vendor_id(+)
AND ps.party_id = hp.party_id(+)
AND pha.vendor_site_id = pasa.vendor_site_id(+)
AND pha.prc_bu_id = hou.organization_id(+)
AND pha.ship_to_location_id = hla_ship.location_id(+)
AND pha.bill_to_location_id = hla_bill.location_id(+)
ORDER BY
pla.line_num ASC
---------------------
2.Purchase Order Change Order Report
------------------------
SELECT
hou.name AS business_unit,
pha.segment1 AS po_number,
pha.revision_num AS current_revision,
pha.document_status,
hp.party_name AS supplier_name,
-- Line Details
pla.line_num,
pla.item_description,
-- Changed Values
plla.quantity AS revised_quantity,
pla.unit_price AS revised_price,
NVL(plla.amount, NVL(plla.quantity, 0) * NVL(pla.unit_price, 0)) AS revised_line_amount,
-- Meta Data
pha.last_update_date AS change_date,
pu.username AS changed_by
FROM
po_headers_all pha,
po_lines_all pla,
po_line_locations_all plla,
poz_suppliers ps,
hz_parties hp,
hr_organization_units hou,
per_users pu
WHERE
pha.po_header_id = pla.po_header_id
AND pla.po_line_id = plla.po_line_id
AND pha.vendor_id = ps.vendor_id(+)
AND ps.party_id = hp.party_id(+)
AND pha.prc_bu_id = hou.organization_id(+)
AND pha.last_updated_by = pu.user_guid(+)
-- Filter for POs that have undergone a Change Order
AND pha.revision_num > 0
ORDER BY
pha.last_update_date DESC,
pla.line_num ASC
-------------------
Write a query to find Price Changes for PO lines.
--------------------
SELECT
pha.segment1 AS po_number,
pla.line_num,
pla.unit_price AS current_price,
pla_arch.unit_price AS previous_price,
(pla.unit_price - pla_arch.unit_price) AS price_diff
FROM
po_headers_all pha,
po_lines_all pla,
po_lines_archive_all pla_arch
WHERE 1=1
AND pha.po_header_id = pla.po_header_id
AND pla.po_line_id = pla_arch.po_line_id
AND pla.unit_price <> pla_arch.unit_price
AND pla_arch.object_version_number = (
SELECT MAX(pla_sub.object_version_number)
FROM po_lines_archive_all pla_sub
WHERE pla_sub.po_line_id = pla.po_line_id
AND pla_sub.object_version_number < pla.object_version_number
)
ORDER BY
pha.segment1,
pla.line_num
==============================
3.Goods Receipt Note (GRN) / Receiving Receipt Report
SELECT
rsh.receipt_num,
rsh.shipment_num,
rsh.waybill_airbill_num,
hp.party_name AS supplier_name,
pha.segment1 AS po_number,
pla.line_num,
rsl.item_description,
rt.transaction_type,
rt.transaction_date,
rt.quantity AS received_qty,
rt.uom_code,
flv.meaning AS routing_name
FROM
rcv_shipment_headers rsh,
rcv_shipment_lines rsl,
rcv_transactions rt,
po_headers_all pha,
po_lines_all pla,
hz_parties hp,
poz_suppliers_v psv,
fnd_lookup_values flv
WHERE 1=1
AND rsh.shipment_header_id = rsl.shipment_header_id
AND rsl.shipment_line_id = rt.shipment_line_id
AND rsl.po_header_id = pha.po_header_id
AND rsl.po_line_id = pla.po_line_id
AND pha.vendor_id = psv.vendor_id
AND psv.party_id = hp.party_id
AND TO_CHAR(rt.routing_header_id) = flv.lookup_code(+)
AND flv.lookup_type(+) = 'RCV_ROUTING_HEADERS'
AND flv.language(+) = USERENV('LANG')
AND rt.transaction_type = 'RECEIVE'
AND rsh.shipment_num IS NOT NULL
AND rsh.waybill_airbill_num IS NOT NULL
ORDER BY
rsh.receipt_num DESC
--------------------------
4.Accrual Reconciliation Report
--------------------------------
SELECT
hou.name AS business_unit,
pha.segment1 AS po_number,
pla.line_num,
pda.distribution_num,
hp.party_name AS supplier_name,
esib.item_number,
gcc.segment1 || '-' || gcc.segment2 || '-' || gcc.segment3 AS accrual_account_string,
pda.quantity_ordered,
pda.quantity_delivered,
pda.quantity_billed,
(pda.quantity_delivered - pda.quantity_billed) AS qty_to_be_invoiced,
pla.unit_price,
((pda.quantity_delivered - pda.quantity_billed) * pla.unit_price) AS accrual_balance
FROM
po_headers_all pha,
po_lines_all pla,
po_distributions_all pda,
hz_parties hp,
poz_suppliers psv,
hr_organization_units hou,
egp_system_items_b esib,
gl_code_combinations gcc
WHERE 1=1
AND pha.po_header_id = pla.po_header_id
AND pla.po_line_id = pda.po_line_id
AND pha.vendor_id = psv.vendor_id
AND psv.party_id = hp.party_id
AND pha.prc_bu_id = hou.organization_id
AND pla.item_id = esib.inventory_item_id(+)
AND pda.destination_organization_id = esib.organization_id(+)
AND pda.accrual_account_id = gcc.code_combination_id
AND pda.quantity_delivered <> pda.quantity_billed
AND pha.document_status NOT IN ('CLOSED', 'CANCELLED')
ORDER BY
pha.segment1,
pla.line_num
--------------------------------------
5.Payables Invoice Register
--------------------------------------
SELECT
hou.name AS business_unit,
aia.invoice_num,
aia.invoice_date,
aia.invoice_type_lookup_code AS type,
hp.party_name AS supplier_name,
psv.segment1 AS supplier_number,
aia.invoice_amount,
aia.invoice_currency_code AS currency,
aia.payment_status_flag AS paid_status,
att.name AS payment_terms,
ail.line_number,
ail.amount AS line_amount,
pha.segment1 AS po_number,
ail.description AS line_description
FROM
ap_invoices_all aia,
ap_invoice_lines_all ail,
hz_parties hp,
poz_suppliers psv,
hr_organization_units hou,
po_headers_all pha,
ap_terms_tl att
WHERE 1=1
AND aia.invoice_id = ail.invoice_id
AND aia.vendor_id = psv.vendor_id
AND psv.party_id = hp.party_id
AND aia.org_id = hou.organization_id
AND aia.terms_id = att.term_id(+)
AND att.language(+) = USERENV('LANG')
AND ail.po_header_id = pha.po_header_id(+)
AND aia.cancelled_date IS NULL
ORDER BY
aia.creation_date DESC
-----------------------
6.Payment Register (Payment File)
-----------------------
SELECT
hou.name AS business_unit,
aca.check_number AS payment_reference,
aca.check_date AS payment_date,
aca.amount AS payment_amount,
aca.currency_code AS currency,
aca.payment_method_code,
aca.status_lookup_code AS payment_status,
hp.party_name AS supplier_name,
psv.segment1 AS supplier_number,
cba.bank_account_name AS internal_bank_account,
aia.invoice_num,
aipa.amount AS amount_paid_on_invoice
FROM
ap_checks_all aca,
ap_invoice_payments_all aipa,
ap_invoices_all aia,
poz_suppliers psv,
hz_parties hp,
hr_organization_units hou,
iby_payments_all ipa,
ce_bank_accounts cba
WHERE 1=1
AND aca.check_id = aipa.check_id
AND aipa.invoice_id = aia.invoice_id
AND aca.vendor_id = psv.vendor_id
AND psv.party_id = hp.party_id
AND aca.org_id = hou.organization_id
AND aca.payment_id = ipa.payment_id
AND ipa.internal_bank_account_id = cba.bank_account_id
AND aca.status_lookup_code <> 'VOIDED'
ORDER BY
aca.check_date DESC
-----------------------
7.Supplier Open Items Report
-----------------------------
SELECT
hou.name AS business_unit,
hp.party_name AS supplier_name, -- Name is stored here
pv.segment1 AS supplier_num,
aia.invoice_num,
aia.invoice_date,
aia.invoice_amount,
aps.amount_remaining,
aps.due_date,
decode(aia.payment_status_flag, 'N', 'Unpaid', 'P', 'Partially Paid') AS payment_status
FROM
ap_invoices_all aia,
ap_payment_schedules_all aps,
poz_suppliers pv,
hz_parties hp,
hr_organization_units hou
WHERE 1=1
AND aia.invoice_id = aps.invoice_id
AND aia.vendor_id = pv.vendor_id
AND pv.party_id = hp.party_id
AND aia.org_id = hou.organization_id
AND aia.payment_status_flag IN ('N', 'P')
AND aps.amount_remaining <> 0
AND aia.cancelled_date IS NULL
ORDER BY
aps.due_date ASC
---------------------------
8.Tax Detail Report (P2P Specific)
----------------------------------
SELECT
hou.name AS business_unit,
hp.party_name AS supplier_name,
aia.invoice_num,
-- Tax Engine Details
zl.tax_regime_code,
zl.tax,
zl.tax_status_code,
zl.tax_rate_code,
-- Recovery Details from Distribution Table
zrd.recovery_rate_code,
zrd.recovery_type_code,
-- Amounts
zl.line_amt AS taxable_amount,
zrd.rec_nrec_tax_amt AS tax_distribution_amt,
decode(zrd.recoverable_flag, 'Y', 'Recoverable', 'N', 'Non-Recoverable') AS tax_type,
-- P2P Reference
pha.segment1 AS po_number
FROM
ap_invoices_all aia,
ap_invoice_lines_all aila,
zx_lines zl,
zx_rec_nrec_dist zrd, -- The table containing Recovery Rates
hz_parties hp,
poz_suppliers pv,
po_headers_all pha,
hr_organization_units hou
WHERE 1=1
-- Link Invoice to Lines
AND aia.invoice_id = aila.invoice_id
-- Link Invoice Lines to Tax Lines
AND aila.invoice_id = zl.trx_id
AND aila.line_number = zl.trx_line_number
-- Link Tax Lines to Tax Distributions (Recovery Info)
AND zl.tax_line_id = zrd.tax_line_id
-- Link to Supplier
AND aia.vendor_id = pv.vendor_id
AND pv.party_id = hp.party_id
-- Link to PO
AND aila.po_header_id = pha.po_header_id(+)
-- Link to Business Unit
AND aia.org_id = hou.organization_id
-- Application Filter for AP
AND zl.application_id = 200
ORDER BY
aia.invoice_num, zl.tax_line_number
------------------
9.Blanket Purchase Agreement (BPA) Status Report
SELECT
hou.name AS business_unit,
pha.segment1 AS bpa_number,
hp.party_name AS supplier_name,
pha.document_status AS bpa_status,
pha.blanket_total_amount AS agreed_amount,
pha.amount_released AS released_amount,
pla.line_num,
esi.item_number,
pla.item_description,
pla.unit_price,
per.display_name AS buyer_name
FROM
po_headers_all pha,
po_lines_all pla,
po_line_locations_all pll, -- Bridge to Inventory Org
hz_parties hp,
poz_suppliers psv,
egp_system_items_b esi,
hr_organization_units hou,
per_person_names_f per
WHERE 1=1
AND pha.po_header_id = pla.po_header_id
AND pla.po_line_id = pll.po_line_id
AND pha.vendor_id = psv.vendor_id
AND psv.party_id = hp.party_id
AND pla.item_id = esi.inventory_item_id(+)
AND pll.ship_to_organization_id = esi.organization_id(+)
AND pha.prc_bu_id = hou.organization_id
AND pha.agent_id = per.person_id
AND per.name_type = 'GLOBAL'
AND sysdate BETWEEN per.effective_start_date AND per.effective_end_date
AND pha.type_lookup_code = 'BLANKET'
ORDER BY
pha.segment1, pla.line_num
----------------------------
SELECT
prh.requisition_number AS req_num,
poh.segment1 AS po_number,
aia.invoice_num AS invoice_number,
aeh.event_type_code,
aeh.accounting_date,
ael.accounted_dr,
ael.accounted_cr,
-- GL Accounting
gj_head.name AS gl_journal_name,
gcc.segment1 || '-' || gcc.segment2 || '-' || gcc.segment3 AS account_string
FROM
por_requisition_headers_all prh,
por_requisition_lines_all prl,
por_req_distributions_all prd,
po_distributions_all pda,
po_headers_all poh,
ap_invoice_lines_all ail,
ap_invoices_all aia,
xla_transaction_entities xe,
xla_ae_headers aeh,
xla_ae_lines ael,
gl_import_references gir,
gl_je_lines gj_line,
gl_je_headers gj_head,
gl_code_combinations gcc
WHERE 1=1
AND prh.requisition_header_id = prl.requisition_header_id
AND prl.requisition_line_id = prd.requisition_line_id
AND prd.distribution_id = pda.req_distribution_id
AND pda.po_header_id = poh.po_header_id
AND pda.po_header_id = ail.po_header_id(+)
AND pda.line_location_id = ail.po_line_location_id(+)
AND ail.invoice_id = aia.invoice_id(+)
AND aia.invoice_id = xe.source_id_int_1(+)
AND xe.entity_code(+) = 'AP_INVOICES'
AND xe.application_id(+) = 200
AND xe.entity_id = aeh.entity_id(+)
AND aeh.ae_header_id = ael.ae_header_id(+)
AND ael.gl_sl_link_id = gir.gl_sl_link_id(+)
AND ael.gl_sl_link_table = gir.gl_sl_link_table(+)
AND gir.je_header_id = gj_line.je_header_id(+)
AND gir.je_line_num = gj_line.je_line_num(+)
AND gj_line.je_header_id = gj_head.je_header_id(+)
AND ael.code_combination_id = gcc.code_combination_id(+)
ORDER BY
prh.requisition_number DESC
No comments:
Post a Comment