Procure to Pay (P2P) is a process cycle where organization purchase items from supplier and supplier send invoice for payment. Below query is developed to identify invoice created/paid against PO and payment pending/done against an invoice for that PO.
PO -> PO Receipt -> AP Invoice -> Payment
Detail P2P Query
SELECT po_prefix,
po_number,
po_status,
po_charge_account,
po_date,
po_currency,
po_exchange_rate,
vendor_code,
vendor_name,
capex_number,
capex_description,
item_code,
item_description,
quantity_ordered,
unit_cost,
po_value_foreign_currency,
po_value_local_currency,
receipt_date,
receipt_year,
receipt_period,
srv_number,
received_qty,
srv_value_local_currency,
total_lcm_charge,
total_value,
paid_amount_lc,
payment_date,
payment_year,
payment_period,
balance_qty,
po_tax,
balance_value,
act_balance_to_be_paid balance_to_be_paid,
inv_tax,
po_header_id,
po_line_id,
(CASE WHEN (NVL(act_balance_to_be_paid,0) = 0) --AND paid_amount_lc > 0
THEN 'Fully Paid'
WHEN NVL(act_balance_to_be_paid,0) <> 0
AND (NVL(act_balance_to_be_paid,0) < (DECODE(NVL(srv_value_local_currency,0),0,NVL(po_value_local_currency,0),NVL(srv_value_local_currency,0))))
--AND paid_amount_lc <> 0
THEN 'Partially Paid'
WHEN (NVL(act_balance_to_be_paid,0) = (DECODE(NVL(srv_value_local_currency,0),0,NVL(po_value_local_currency,0),NVL(srv_value_local_currency,0))))
THEN 'Unpaid'
ELSE NULL
END) payment_status ,
payment_amount_lc ,
balance_to_be_paid_n,
invoice_amount_lc,
act_balance_to_be_paid
FROM
(
SELECT po_type po_prefix,
po_number,
po_status,
po_charge_account,
po_date,
po_currency,
po_exchange_rate,
vendor_code,
vendor_name,
capex_number,
capex_description,
item_code,
item_description,
quantity_ordered,
unit_cost,
po_value_foreign_currency,
po_value_local_currency,
receipt_date,
receipt_year,
receipt_period,
srv_number,
received_qty,
srv_value_local_currency,
total_lcm_charge,
(srv_value_local_currency + total_lcm_charge) total_value,
paid_amount_lc,
payment_date,
payment_year,
payment_period,
balance_qty,
po_tax,
(po_value_local_currency - srv_value_local_currency) balance_value,
(NVL(po_value_local_currency,0) - NVL(paid_amount_lc,0)) balance_to_be_paid,
(DECODE(NVL(srv_value_local_currency,0),0,NVL(po_value_local_currency,0), NVL(srv_value_local_currency,0))- NVL(paid_amount_lc,0)) balance_to_be_paid_n,
(CASE WHEN NVL(invoice_amount_lc,0) - NVL(payment_amount_lc,0) <> 0
THEN (DECODE(NVL(srv_value_local_currency,0),0,NVL(po_value_local_currency,0), NVL(srv_value_local_currency,0))- NVL(paid_amount_lc,0))
ELSE (DECODE(NVL(srv_value_local_currency,0),0,NVL(po_value_local_currency,0), NVL(srv_value_local_currency,0))- NVL(paid_amount_lc,0))
END
) act_balance_to_be_paid,
inv_tax,
po_header_id,
po_line_id,
transaction_id,
payment_amount_lc,
invoice_amount_lc
FROM
(
SELECT pha.attribute1 po_type,
pha.segment1 po_number,
pha.document_status po_status,
gcc.segment1||'.'||
gcc.segment2||'.'||
gcc.segment3||'.'||
gcc.segment4||'.'||
gcc.segment5||'.'||
gcc.segment6 po_charge_account,
TO_CHAR(pha.creation_date,'DD-MON-YYYY') po_date,
pha.currency_code po_currency,
pha.rate po_exchange_rate,
psv.segment1 vendor_code,
psv.vendor_name,
esiv.item_number capex_number,
esiv.description capex_description,
esiv.item_number item_code,
esiv.description item_description,
pla.quantity quantity_ordered,
NVL(pla.unit_price,0) unit_cost,
(CASE WHEN pha.currency_code <> gll.currency_code
THEN (pla.quantity * NVL(pla.unit_price,0))
ELSE 0
END) po_value_foreign_currency,
(CASE WHEN pha.currency_code <> gll.currency_code
THEN ((pla.quantity * NVL(pla.unit_price,0))* NVL(pha.rate,1))
ELSE (pla.quantity * NVL(pla.unit_price,0))
END) po_value_local_currency,
srv.srv_number,
srv.receipt_date,
srv.receipt_year,
srv.receipt_period,
srv.received_qty,
srv.transaction_id,
(CASE WHEN pha.currency_code <> gll.currency_code
THEN ((srv.received_qty * NVL(pla.unit_price,0))* NVL(pha.rate,1))
ELSE (srv.received_qty * NVL(pla.unit_price,0))
END) srv_value_local_currency,
srv.total_lcm_charge,
(CASE WHEN (NVL(pay.payment_amount_lc,0) - NVL(pay.inv_tax,0)) <> (NVL(pay.invoice_amount_lc,0) - NVL(pay.inv_tax,0))
AND NVL(pay.payment_amount_lc,0) <> 0
AND (NVL(pay.payment_amount_lc,0) - NVL(pay.inv_tax,0)) < (NVL(pay.paid_amount_lc,0) - NVL(pay.inv_tax,0))
--
THEN (NVL(pay.payment_amount_lc,0) - NVL(pay.inv_tax,0))
WHEN (NVL(pay.payment_amount_lc,0) - NVL(pay.inv_tax,0)) <> (NVL(pay.invoice_amount_lc,0) - NVL(pay.inv_tax,0))
AND ((NVL(pay.payment_amount_lc,0) = 0) OR (pay.payment_amount_lc IS NULL))
AND (NVL(pay.payment_amount_lc,0) - NVL(pay.inv_tax,0)) < (NVL(pay.paid_amount_lc,0) - NVL(pay.inv_tax,0))
--
THEN (NVL(pay.payment_amount_lc,0)) --- NVL(pay.inv_tax,0))
WHEN (NVL(pay.payment_amount_lc,0) - NVL(pay.inv_tax,0)) = (NVL(pay.invoice_amount_lc,0) - NVL(pay.inv_tax,0))
THEN (NVL(pay.paid_amount_lc,0) )
WHEN (NVL(pay.payment_amount_lc,0) - NVL(pay.inv_tax,0)) > (NVL(pay.paid_amount_lc,0) - NVL(pay.inv_tax,0))
THEN (NVL(pay.paid_amount_lc,0)) --- NVL(pay.inv_tax,0))
--
ELSE (NVL(pay.paid_amount_lc,0) )
END) paid_amount_lc,
pay.payment_date,
pay.payment_year,
pay.payment_period,
(pla.quantity - srv.received_qty) balance_qty,
NVL(pda.recoverable_tax,0) po_tax,
pha.po_header_id,
pla.po_line_id,
pll.line_location_id,
pay.inv_tax,
srv.shipment_header_id,
pay.payment_amount_lc,
pay.invoice_amount_lc
FROM po_headers_all pha,
po_lines_all pla,
po_line_locations_all pll,
(SELECT pda.po_header_id,
pda.po_line_id,
pda.line_location_id,
pda.code_combination_id,
SUM(NVL(pda.recoverable_tax,0)) recoverable_tax
FROM po_distributions_all pda
GROUP BY pda.po_header_id,
pda.po_line_id,
pda.line_location_id,
pda.code_combination_id
) pda,
poz_suppliers_v psv,
egp_system_items_v esiv,
egp_item_categories eic,
egp_categories_b ecb,
egp_category_set_valid_cats ecsvc,
egp_category_sets_b ecsb,
egp_categories_tl ect,
egp_item_cat_assignments eica,
gl_code_combinations gcc,
xle_entity_profiles xep,
xle_registrations xr,
hr_operating_units hou,
hr_organization_units horg,
gl_ledger_norm_seg_vals glnsv,
gl_ledgers gll,
--PO Receipt
(SELECT srv_number,
receipt_date,
srv_date,
receipt_year,
receipt_period,
received_qty,
transaction_id,
po_header_id,
po_line_id,
po_line_location_id,
shipment_header_id,
shipment_line_id,
total_lcm_charge
FROM
(
SELECT rsh.receipt_num srv_number,
TO_CHAR(rsh.creation_date,'DD-MON-YYYY') receipt_date,
rsh.creation_date srv_date,
TO_CHAR(rsh.creation_date,'YYYY') receipt_year,
TO_CHAR(rsh.creation_date,'Mon-YY') receipt_period,
rt.quantity received_qty,
rt.transaction_id,
rt.po_header_id,
rt.po_line_id,
rt.po_line_location_id,
rt.shipment_header_id,
rt.shipment_line_id,
DECODE (rt.transaction_type, 'RECEIVE', 1, 'RETURN TO VENDOR', -1)
* NVL (
( NVL (rt.quantity, 0)
- NVL (
(SELECT ABS (SUM (rt1.quantity))
FROM rcv_transactions rt1
WHERE rt1.transaction_type IN
('RECEIVE', 'CORRECT') -- this condition require to remove if require recived not populate
AND rt1.po_line_id = rt.po_line_id
AND rt1.parent_transaction_id = rt.transaction_id),
0))
* ROUND (
NVL (
(SELECT SUM (clcc.est_unit_amt_in_charge_bu_curr)
FROM cmr_rcv_transactions crt,
fun_all_business_units_v fabuv,
cml_landed_cost_charges clcc
WHERE 1 = 1
AND rt.transaction_id =
crt.external_system_ref_id
AND crt.cmr_rcv_transaction_id =
clcc.cmr_rcv_transaction_id
AND fabuv.bu_id = clcc.procurement_bu_id),
0),
5),
0) total_lcm_charge
FROM rcv_shipment_headers rsh,
rcv_shipment_lines rsl,
rcv_transactions rt
WHERE rsh.shipment_header_id = rsl.shipment_header_id
AND rsh.shipment_header_id = rt.shipment_header_id
AND rsl.shipment_line_id = rt.shipment_line_id
AND rt.transaction_type = 'RECEIVE'
AND rt.transaction_id NOT IN (SELECT rt1.parent_transaction_id
FROM rcv_transactions rt1
WHERE rt1.transaction_type IN
('RECEIVE', 'CORRECT','RETURN TO VENDOR')
AND rt1.po_line_id = rt.po_line_id
)
)
) srv,
--
--Invoice Payment
(SELECT SUM(payment_amount_lc) payment_amount_lc,
SUM(paid_amount_lc) paid_amount_lc,
SUM(invoice_amount_lc) invoice_amount_lc,
SUM(inv_tax) inv_tax,
rtrim (xmlagg (xmlelement (e, payment_date || ',')).extract ('//text()'), ',') payment_date,
rtrim (xmlagg (xmlelement (e, payment_year || ',')).extract ('//text()'), ',') payment_year,
rtrim (xmlagg (xmlelement (e, payment_period || ',')).extract ('//text()'), ',') payment_period,
po_header_id,
po_line_id,
rcv_shipment_line_id
--invoice_id
FROM
(
SELECT NVL(SUM(aipa.amount * NVL(aia.exchange_rate,1)),0) payment_amount_lc,
NVL(SUM(ail.amount * NVL(aia.exchange_rate,1)),0) paid_amount_lc,
NVL(SUM(aia.invoice_amount * NVL(aia.exchange_rate,1)),0) invoice_amount_lc,
NVL((SELECT SUM(ail1.amount)
FROM ap_invoice_lines_all ail1
WHERE ail1.invoice_id = aia.invoice_id
AND ail1.line_type_lookup_code = 'TAX'
),0) inv_tax,
aipa.payment_date,
aipa.payment_year,
aipa.payment_period,
ail.po_header_id,
ail.po_line_id,
ail.rcv_shipment_line_id,
aia.invoice_id
FROM (SELECT SUM(aipa.amount) amount,
rtrim (xmlagg (xmlelement (e, payment_date || ',')).extract ('//text()'), ',') payment_date,
rtrim (xmlagg (xmlelement (e, payment_year || ',')).extract ('//text()'), ',') payment_year,
rtrim (xmlagg (xmlelement (e, payment_period || ',')).extract ('//text()'), ',') payment_period,
aipa.invoice_id
FROM
(SELECT SUM(aipa.amount) amount,
rtrim (xmlagg (xmlelement (e, TO_CHAR(aca.check_date,'DD-MON-YYYY') || ',')).extract ('//text()'), ',') payment_date,
rtrim (xmlagg (xmlelement (e, TO_CHAR(aca.check_date,'YYYY') || ',')).extract ('//text()'), ',') payment_year,
rtrim (xmlagg (xmlelement (e, TO_CHAR(aca.check_date,'Mon-YY') || ',')).extract ('//text()'), ',') payment_period,
aipa.invoice_id
FROM ap_checks_all aca,
ap_invoice_payments_all aipa
WHERE aca.check_id = aipa.check_id
AND aca.status_lookup_code NOT IN ('VOIDED')
GROUP BY aipa.invoice_id
UNION
SELECT ABS(SUM(apad.amount)) amount,
rtrim (xmlagg (xmlelement (e, payment_date || ',')).extract ('//text()'), ',') payment_date,
rtrim (xmlagg (xmlelement (e, payment_year || ',')).extract ('//text()'), ',') payment_year,
rtrim (xmlagg (xmlelement (e, payment_period || ',')).extract ('//text()'), ',') payment_period,
invoice_id
FROM
(SELECT SUM(apad.amount) amount,
TO_CHAR(apha.accounting_date,'DD-MON-YYYY') payment_date,
TO_CHAR(apha.accounting_date,'YYYY') payment_year,
TO_CHAR(apha.accounting_date,'Mon-YY') payment_period,
apha.invoice_id
FROM ap_prepay_history_all apha,
ap_prepay_app_dists apad,
ap_invoices_all pre_inv,
ap_invoices_all inv,
ap_invoice_lines_all pre_inv_line,
ap_invoice_distributions_all aida
WHERE 1=1 --apha1.invoice_id = aia.invoice_id
AND inv.invoice_id = apha.prepay_invoice_id
AND pre_inv.invoice_id = pre_inv_line.invoice_id
AND pre_inv.invoice_id = apha.invoice_id
AND pre_inv_line.discarded_flag <> 'Y'
AND pre_inv_line.line_type_lookup_code = 'PREPAY'
AND pre_inv_line.line_number = apha.invoice_line_number
AND apha.prepay_history_id = apad.prepay_history_id
AND apad.prepay_dist_lookup_code = 'PREPAY APPL'
AND aida.invoice_distribution_id = apad.invoice_distribution_id
GROUP BY TO_CHAR(apha.accounting_date,'DD-MON-YYYY') ,
TO_CHAR(apha.accounting_date,'YYYY') ,
TO_CHAR(apha.accounting_date,'Mon-YY') ,
apha.invoice_id
) apad
GROUP BY apad.invoice_id
) aipa
GROUP BY aipa.invoice_id
) aipa,
--
ap_invoices_all aia,
ap_invoice_lines_all ail
WHERE aia.invoice_id = aipa.invoice_id
AND aia.invoice_id = ail.invoice_id
AND ail.line_type_lookup_code = 'ITEM'
AND ((aia.cancelled_date IS NULL) OR (aia.cancelled_date > SYSDATE))
AND NVL(ail.discarded_flag,'N') <> 'Y'
AND ail.po_header_id IS NOT NULL
GROUP BY aipa.payment_date,
aipa.payment_year,
aipa.payment_period,
ail.po_header_id,
ail.po_line_id,
ail.rcv_shipment_line_id,
aia.invoice_id
) aipa
GROUP BY po_header_id,
po_line_id,
rcv_shipment_line_id
--invoice_id
) pay
WHERE pha.po_header_id = pla.po_header_id
AND pla.po_line_id = pll.po_line_id
AND pha.document_status NOT IN ('INCOMPLETE', 'PENDING APPROVAL', 'REJECTED')
AND pda.po_header_id = pha.po_header_id
AND pda.po_line_id = pll.po_line_id
AND pda.line_location_id = pll.line_location_id
AND pha.vendor_id = psv.vendor_id
AND pla.item_id = esiv.inventory_item_id
AND esiv.organization_id = pll.ship_to_organization_id
AND pda.code_combination_id = gcc.code_combination_id
AND eic.category_id = ecb.category_id
AND esiv.inventory_item_id = eic.inventory_item_id
AND esiv.organization_id = eic.organization_id
AND eic.category_id = ecb.category_id
AND ecb.category_id = ecsvc.category_id
AND ecsb.category_set_id = ecsvc.category_set_id
AND ecb.category_id = ect.category_id
AND ecsb.category_set_id = eica.category_set_id
AND eic.inventory_item_id = eica.inventory_item_id
AND ecb.category_id = eica.category_id
--AND ect.category_name = 'CIP Category' --for particular category
AND ect.language = USERENV ('LANG')
AND ect.source_lang = USERENV ('LANG')
AND xep.legal_entity_id = xr.source_id
AND xr.source_table = 'XLE_ENTITY_PROFILES'
AND xep.legal_entity_id = hou.default_legal_context_id(+)
AND horg.organization_id = hou.business_group_id
AND glnsv.legal_entity_id = xep.legal_entity_id
AND gll.ledger_id = glnsv.ledger_id
AND pha.prc_bu_id = HOU.organization_id
AND pla.po_header_id = srv.po_header_id(+)
AND pla.po_line_id = srv.po_line_id(+)
AND pla.po_header_id = pay.po_header_id(+)
AND pla.po_line_id = pay.po_line_id(+)
AND srv.shipment_line_id = pay.rcv_shipment_line_id(+)
AND UPPER(pla.line_status) NOT IN ('CANCELED')
AND hou.organization_id = :p_bu_id
AND TRUNC(pha.creation_date) >= :p_from_date
AND TRUNC(pha.creation_date) <= :p_to_date
AND ((pha.po_header_id IN (:p_po_id)) OR ('All' IN (:p_po_id||'All')))
)
)
ORDER BY po_header_id,po_line_id
po_number,
po_status,
po_charge_account,
po_date,
po_currency,
po_exchange_rate,
vendor_code,
vendor_name,
capex_number,
capex_description,
item_code,
item_description,
quantity_ordered,
unit_cost,
po_value_foreign_currency,
po_value_local_currency,
receipt_date,
receipt_year,
receipt_period,
srv_number,
received_qty,
srv_value_local_currency,
total_lcm_charge,
total_value,
paid_amount_lc,
payment_date,
payment_year,
payment_period,
balance_qty,
po_tax,
balance_value,
act_balance_to_be_paid balance_to_be_paid,
inv_tax,
po_header_id,
po_line_id,
(CASE WHEN (NVL(act_balance_to_be_paid,0) = 0) --AND paid_amount_lc > 0
THEN 'Fully Paid'
WHEN NVL(act_balance_to_be_paid,0) <> 0
AND (NVL(act_balance_to_be_paid,0) < (DECODE(NVL(srv_value_local_currency,0),0,NVL(po_value_local_currency,0),NVL(srv_value_local_currency,0))))
--AND paid_amount_lc <> 0
THEN 'Partially Paid'
WHEN (NVL(act_balance_to_be_paid,0) = (DECODE(NVL(srv_value_local_currency,0),0,NVL(po_value_local_currency,0),NVL(srv_value_local_currency,0))))
THEN 'Unpaid'
ELSE NULL
END) payment_status ,
payment_amount_lc ,
balance_to_be_paid_n,
invoice_amount_lc,
act_balance_to_be_paid
FROM
(
SELECT po_type po_prefix,
po_number,
po_status,
po_charge_account,
po_date,
po_currency,
po_exchange_rate,
vendor_code,
vendor_name,
capex_number,
capex_description,
item_code,
item_description,
quantity_ordered,
unit_cost,
po_value_foreign_currency,
po_value_local_currency,
receipt_date,
receipt_year,
receipt_period,
srv_number,
received_qty,
srv_value_local_currency,
total_lcm_charge,
(srv_value_local_currency + total_lcm_charge) total_value,
paid_amount_lc,
payment_date,
payment_year,
payment_period,
balance_qty,
po_tax,
(po_value_local_currency - srv_value_local_currency) balance_value,
(NVL(po_value_local_currency,0) - NVL(paid_amount_lc,0)) balance_to_be_paid,
(DECODE(NVL(srv_value_local_currency,0),0,NVL(po_value_local_currency,0), NVL(srv_value_local_currency,0))- NVL(paid_amount_lc,0)) balance_to_be_paid_n,
(CASE WHEN NVL(invoice_amount_lc,0) - NVL(payment_amount_lc,0) <> 0
THEN (DECODE(NVL(srv_value_local_currency,0),0,NVL(po_value_local_currency,0), NVL(srv_value_local_currency,0))- NVL(paid_amount_lc,0))
ELSE (DECODE(NVL(srv_value_local_currency,0),0,NVL(po_value_local_currency,0), NVL(srv_value_local_currency,0))- NVL(paid_amount_lc,0))
END
) act_balance_to_be_paid,
inv_tax,
po_header_id,
po_line_id,
transaction_id,
payment_amount_lc,
invoice_amount_lc
FROM
(
SELECT pha.attribute1 po_type,
pha.segment1 po_number,
pha.document_status po_status,
gcc.segment1||'.'||
gcc.segment2||'.'||
gcc.segment3||'.'||
gcc.segment4||'.'||
gcc.segment5||'.'||
gcc.segment6 po_charge_account,
TO_CHAR(pha.creation_date,'DD-MON-YYYY') po_date,
pha.currency_code po_currency,
pha.rate po_exchange_rate,
psv.segment1 vendor_code,
psv.vendor_name,
esiv.item_number capex_number,
esiv.description capex_description,
esiv.item_number item_code,
esiv.description item_description,
pla.quantity quantity_ordered,
NVL(pla.unit_price,0) unit_cost,
(CASE WHEN pha.currency_code <> gll.currency_code
THEN (pla.quantity * NVL(pla.unit_price,0))
ELSE 0
END) po_value_foreign_currency,
(CASE WHEN pha.currency_code <> gll.currency_code
THEN ((pla.quantity * NVL(pla.unit_price,0))* NVL(pha.rate,1))
ELSE (pla.quantity * NVL(pla.unit_price,0))
END) po_value_local_currency,
srv.srv_number,
srv.receipt_date,
srv.receipt_year,
srv.receipt_period,
srv.received_qty,
srv.transaction_id,
(CASE WHEN pha.currency_code <> gll.currency_code
THEN ((srv.received_qty * NVL(pla.unit_price,0))* NVL(pha.rate,1))
ELSE (srv.received_qty * NVL(pla.unit_price,0))
END) srv_value_local_currency,
srv.total_lcm_charge,
(CASE WHEN (NVL(pay.payment_amount_lc,0) - NVL(pay.inv_tax,0)) <> (NVL(pay.invoice_amount_lc,0) - NVL(pay.inv_tax,0))
AND NVL(pay.payment_amount_lc,0) <> 0
AND (NVL(pay.payment_amount_lc,0) - NVL(pay.inv_tax,0)) < (NVL(pay.paid_amount_lc,0) - NVL(pay.inv_tax,0))
--
THEN (NVL(pay.payment_amount_lc,0) - NVL(pay.inv_tax,0))
WHEN (NVL(pay.payment_amount_lc,0) - NVL(pay.inv_tax,0)) <> (NVL(pay.invoice_amount_lc,0) - NVL(pay.inv_tax,0))
AND ((NVL(pay.payment_amount_lc,0) = 0) OR (pay.payment_amount_lc IS NULL))
AND (NVL(pay.payment_amount_lc,0) - NVL(pay.inv_tax,0)) < (NVL(pay.paid_amount_lc,0) - NVL(pay.inv_tax,0))
--
THEN (NVL(pay.payment_amount_lc,0)) --- NVL(pay.inv_tax,0))
WHEN (NVL(pay.payment_amount_lc,0) - NVL(pay.inv_tax,0)) = (NVL(pay.invoice_amount_lc,0) - NVL(pay.inv_tax,0))
THEN (NVL(pay.paid_amount_lc,0) )
WHEN (NVL(pay.payment_amount_lc,0) - NVL(pay.inv_tax,0)) > (NVL(pay.paid_amount_lc,0) - NVL(pay.inv_tax,0))
THEN (NVL(pay.paid_amount_lc,0)) --- NVL(pay.inv_tax,0))
--
ELSE (NVL(pay.paid_amount_lc,0) )
END) paid_amount_lc,
pay.payment_date,
pay.payment_year,
pay.payment_period,
(pla.quantity - srv.received_qty) balance_qty,
NVL(pda.recoverable_tax,0) po_tax,
pha.po_header_id,
pla.po_line_id,
pll.line_location_id,
pay.inv_tax,
srv.shipment_header_id,
pay.payment_amount_lc,
pay.invoice_amount_lc
FROM po_headers_all pha,
po_lines_all pla,
po_line_locations_all pll,
(SELECT pda.po_header_id,
pda.po_line_id,
pda.line_location_id,
pda.code_combination_id,
SUM(NVL(pda.recoverable_tax,0)) recoverable_tax
FROM po_distributions_all pda
GROUP BY pda.po_header_id,
pda.po_line_id,
pda.line_location_id,
pda.code_combination_id
) pda,
poz_suppliers_v psv,
egp_system_items_v esiv,
egp_item_categories eic,
egp_categories_b ecb,
egp_category_set_valid_cats ecsvc,
egp_category_sets_b ecsb,
egp_categories_tl ect,
egp_item_cat_assignments eica,
gl_code_combinations gcc,
xle_entity_profiles xep,
xle_registrations xr,
hr_operating_units hou,
hr_organization_units horg,
gl_ledger_norm_seg_vals glnsv,
gl_ledgers gll,
--PO Receipt
(SELECT srv_number,
receipt_date,
srv_date,
receipt_year,
receipt_period,
received_qty,
transaction_id,
po_header_id,
po_line_id,
po_line_location_id,
shipment_header_id,
shipment_line_id,
total_lcm_charge
FROM
(
SELECT rsh.receipt_num srv_number,
TO_CHAR(rsh.creation_date,'DD-MON-YYYY') receipt_date,
rsh.creation_date srv_date,
TO_CHAR(rsh.creation_date,'YYYY') receipt_year,
TO_CHAR(rsh.creation_date,'Mon-YY') receipt_period,
rt.quantity received_qty,
rt.transaction_id,
rt.po_header_id,
rt.po_line_id,
rt.po_line_location_id,
rt.shipment_header_id,
rt.shipment_line_id,
DECODE (rt.transaction_type, 'RECEIVE', 1, 'RETURN TO VENDOR', -1)
* NVL (
( NVL (rt.quantity, 0)
- NVL (
(SELECT ABS (SUM (rt1.quantity))
FROM rcv_transactions rt1
WHERE rt1.transaction_type IN
('RECEIVE', 'CORRECT') -- this condition require to remove if require recived not populate
AND rt1.po_line_id = rt.po_line_id
AND rt1.parent_transaction_id = rt.transaction_id),
0))
* ROUND (
NVL (
(SELECT SUM (clcc.est_unit_amt_in_charge_bu_curr)
FROM cmr_rcv_transactions crt,
fun_all_business_units_v fabuv,
cml_landed_cost_charges clcc
WHERE 1 = 1
AND rt.transaction_id =
crt.external_system_ref_id
AND crt.cmr_rcv_transaction_id =
clcc.cmr_rcv_transaction_id
AND fabuv.bu_id = clcc.procurement_bu_id),
0),
5),
0) total_lcm_charge
FROM rcv_shipment_headers rsh,
rcv_shipment_lines rsl,
rcv_transactions rt
WHERE rsh.shipment_header_id = rsl.shipment_header_id
AND rsh.shipment_header_id = rt.shipment_header_id
AND rsl.shipment_line_id = rt.shipment_line_id
AND rt.transaction_type = 'RECEIVE'
AND rt.transaction_id NOT IN (SELECT rt1.parent_transaction_id
FROM rcv_transactions rt1
WHERE rt1.transaction_type IN
('RECEIVE', 'CORRECT','RETURN TO VENDOR')
AND rt1.po_line_id = rt.po_line_id
)
)
) srv,
--
--Invoice Payment
(SELECT SUM(payment_amount_lc) payment_amount_lc,
SUM(paid_amount_lc) paid_amount_lc,
SUM(invoice_amount_lc) invoice_amount_lc,
SUM(inv_tax) inv_tax,
rtrim (xmlagg (xmlelement (e, payment_date || ',')).extract ('//text()'), ',') payment_date,
rtrim (xmlagg (xmlelement (e, payment_year || ',')).extract ('//text()'), ',') payment_year,
rtrim (xmlagg (xmlelement (e, payment_period || ',')).extract ('//text()'), ',') payment_period,
po_header_id,
po_line_id,
rcv_shipment_line_id
--invoice_id
FROM
(
SELECT NVL(SUM(aipa.amount * NVL(aia.exchange_rate,1)),0) payment_amount_lc,
NVL(SUM(ail.amount * NVL(aia.exchange_rate,1)),0) paid_amount_lc,
NVL(SUM(aia.invoice_amount * NVL(aia.exchange_rate,1)),0) invoice_amount_lc,
NVL((SELECT SUM(ail1.amount)
FROM ap_invoice_lines_all ail1
WHERE ail1.invoice_id = aia.invoice_id
AND ail1.line_type_lookup_code = 'TAX'
),0) inv_tax,
aipa.payment_date,
aipa.payment_year,
aipa.payment_period,
ail.po_header_id,
ail.po_line_id,
ail.rcv_shipment_line_id,
aia.invoice_id
FROM (SELECT SUM(aipa.amount) amount,
rtrim (xmlagg (xmlelement (e, payment_date || ',')).extract ('//text()'), ',') payment_date,
rtrim (xmlagg (xmlelement (e, payment_year || ',')).extract ('//text()'), ',') payment_year,
rtrim (xmlagg (xmlelement (e, payment_period || ',')).extract ('//text()'), ',') payment_period,
aipa.invoice_id
FROM
(SELECT SUM(aipa.amount) amount,
rtrim (xmlagg (xmlelement (e, TO_CHAR(aca.check_date,'DD-MON-YYYY') || ',')).extract ('//text()'), ',') payment_date,
rtrim (xmlagg (xmlelement (e, TO_CHAR(aca.check_date,'YYYY') || ',')).extract ('//text()'), ',') payment_year,
rtrim (xmlagg (xmlelement (e, TO_CHAR(aca.check_date,'Mon-YY') || ',')).extract ('//text()'), ',') payment_period,
aipa.invoice_id
FROM ap_checks_all aca,
ap_invoice_payments_all aipa
WHERE aca.check_id = aipa.check_id
AND aca.status_lookup_code NOT IN ('VOIDED')
GROUP BY aipa.invoice_id
UNION
SELECT ABS(SUM(apad.amount)) amount,
rtrim (xmlagg (xmlelement (e, payment_date || ',')).extract ('//text()'), ',') payment_date,
rtrim (xmlagg (xmlelement (e, payment_year || ',')).extract ('//text()'), ',') payment_year,
rtrim (xmlagg (xmlelement (e, payment_period || ',')).extract ('//text()'), ',') payment_period,
invoice_id
FROM
(SELECT SUM(apad.amount) amount,
TO_CHAR(apha.accounting_date,'DD-MON-YYYY') payment_date,
TO_CHAR(apha.accounting_date,'YYYY') payment_year,
TO_CHAR(apha.accounting_date,'Mon-YY') payment_period,
apha.invoice_id
FROM ap_prepay_history_all apha,
ap_prepay_app_dists apad,
ap_invoices_all pre_inv,
ap_invoices_all inv,
ap_invoice_lines_all pre_inv_line,
ap_invoice_distributions_all aida
WHERE 1=1 --apha1.invoice_id = aia.invoice_id
AND inv.invoice_id = apha.prepay_invoice_id
AND pre_inv.invoice_id = pre_inv_line.invoice_id
AND pre_inv.invoice_id = apha.invoice_id
AND pre_inv_line.discarded_flag <> 'Y'
AND pre_inv_line.line_type_lookup_code = 'PREPAY'
AND pre_inv_line.line_number = apha.invoice_line_number
AND apha.prepay_history_id = apad.prepay_history_id
AND apad.prepay_dist_lookup_code = 'PREPAY APPL'
AND aida.invoice_distribution_id = apad.invoice_distribution_id
GROUP BY TO_CHAR(apha.accounting_date,'DD-MON-YYYY') ,
TO_CHAR(apha.accounting_date,'YYYY') ,
TO_CHAR(apha.accounting_date,'Mon-YY') ,
apha.invoice_id
) apad
GROUP BY apad.invoice_id
) aipa
GROUP BY aipa.invoice_id
) aipa,
--
ap_invoices_all aia,
ap_invoice_lines_all ail
WHERE aia.invoice_id = aipa.invoice_id
AND aia.invoice_id = ail.invoice_id
AND ail.line_type_lookup_code = 'ITEM'
AND ((aia.cancelled_date IS NULL) OR (aia.cancelled_date > SYSDATE))
AND NVL(ail.discarded_flag,'N') <> 'Y'
AND ail.po_header_id IS NOT NULL
GROUP BY aipa.payment_date,
aipa.payment_year,
aipa.payment_period,
ail.po_header_id,
ail.po_line_id,
ail.rcv_shipment_line_id,
aia.invoice_id
) aipa
GROUP BY po_header_id,
po_line_id,
rcv_shipment_line_id
--invoice_id
) pay
WHERE pha.po_header_id = pla.po_header_id
AND pla.po_line_id = pll.po_line_id
AND pha.document_status NOT IN ('INCOMPLETE', 'PENDING APPROVAL', 'REJECTED')
AND pda.po_header_id = pha.po_header_id
AND pda.po_line_id = pll.po_line_id
AND pda.line_location_id = pll.line_location_id
AND pha.vendor_id = psv.vendor_id
AND pla.item_id = esiv.inventory_item_id
AND esiv.organization_id = pll.ship_to_organization_id
AND pda.code_combination_id = gcc.code_combination_id
AND eic.category_id = ecb.category_id
AND esiv.inventory_item_id = eic.inventory_item_id
AND esiv.organization_id = eic.organization_id
AND eic.category_id = ecb.category_id
AND ecb.category_id = ecsvc.category_id
AND ecsb.category_set_id = ecsvc.category_set_id
AND ecb.category_id = ect.category_id
AND ecsb.category_set_id = eica.category_set_id
AND eic.inventory_item_id = eica.inventory_item_id
AND ecb.category_id = eica.category_id
--AND ect.category_name = 'CIP Category' --for particular category
AND ect.language = USERENV ('LANG')
AND ect.source_lang = USERENV ('LANG')
AND xep.legal_entity_id = xr.source_id
AND xr.source_table = 'XLE_ENTITY_PROFILES'
AND xep.legal_entity_id = hou.default_legal_context_id(+)
AND horg.organization_id = hou.business_group_id
AND glnsv.legal_entity_id = xep.legal_entity_id
AND gll.ledger_id = glnsv.ledger_id
AND pha.prc_bu_id = HOU.organization_id
AND pla.po_header_id = srv.po_header_id(+)
AND pla.po_line_id = srv.po_line_id(+)
AND pla.po_header_id = pay.po_header_id(+)
AND pla.po_line_id = pay.po_line_id(+)
AND srv.shipment_line_id = pay.rcv_shipment_line_id(+)
AND UPPER(pla.line_status) NOT IN ('CANCELED')
AND hou.organization_id = :p_bu_id
AND TRUNC(pha.creation_date) >= :p_from_date
AND TRUNC(pha.creation_date) <= :p_to_date
AND ((pha.po_header_id IN (:p_po_id)) OR ('All' IN (:p_po_id||'All')))
)
)
ORDER BY po_header_id,po_line_id