Introduction
Uninvoiced Receipts report is designed to provide a preview of all accrual entries that will be posted to your account in a specific month.
Script to Uninvoiced Receipts Report
–/*Query 1* Company and Ledger Details/*
SELECT gsb.NAME c_company, fsp.inventory_organization_id c_organization_id,
gsb.currency_code gl_currency, gsb.chart_of_accounts_id structure_acc,
mdv.structure_id structure_cat, mdv.category_set_id c_category_set_id,
flo1.meaning c_yes, flo2.meaning c_no, glps.end_date c_end_date
FROM –gl_sets_of_books gsb
gl_ledgers gsb,
financials_system_params_all fsp,
mtl_default_sets_view mdv,
fnd_lookups flo1,
fnd_lookups flo2,
gl_period_statuses glps
–WHERE gsb.set_of_books_id = fsp.set_of_books_id
WHERE gsb.ledger_id = fsp.set_of_books_id
AND mdv.functional_area_id = 2
AND flo1.lookup_type = ‘YES_NO’
AND flo1.lookup_code = ‘Y’
AND flo2.lookup_type = ‘YES_NO’
AND flo2.lookup_code = ‘N’
AND glps.application_id = 201
AND glps.set_of_books_id = fsp.set_of_books_id
AND ( ( :p_period_name IS NULL
AND TRUNC (SYSDATE) BETWEEN TRUNC (glps.start_date)
AND TRUNC (glps.end_date)
)
OR (:p_period_name IS NOT NULL AND :p_period_name = glps.period_name)
)
–/*Query 2* Header Details/
SELECT pov.vendor_name c_sort,
poh.segment1
|| DECODE (porl.release_num, NULL, ”, ‘ – ‘ || porl.release_num)
po_release,
plt.line_type line_type, pol.line_num line, pol.item_description,
pov.vendor_name vendor, poh.currency_code c_currency,
fnc.minimum_accountable_unit min_acc_unit,
pll.price_override unit_price, pll.shipment_num shipment,
pll.unit_meas_lookup_code unit, pod.distribution_num distribution,
pod.quantity_ordered dist_quantity, pll.quantity ship_quantity,
NVL (pll.price_override, 0) shipment_price,
pll.line_location_id p_po_line_location_id, apid.quantity_invoiced,
mca.segment1, mca.segment2, mca.segment3, mca.segment4, mca.segment5,
mca.segment6, mca.segment7, mca.segment8, mca.segment9, mca.segment10,
mca.segment11, mca.segment12, mca.segment13, mca.segment14,
mca.segment15, mca.segment16, mca.segment17, mca.segment18,
mca.segment19, mca.segment20, pod.quantity_ordered dist_qty_ordered,
pll.quantity ship_qty_ordered, fnc.PRECISION PRECISION,
po_tax_sv.get_tax (‘PO’, pod.po_distribution_id) tax,
NVL (NVL (pod.rate, poh.rate), 1) exchange_rate,
NVL (pll.match_option, ‘P’) match_option,
NVL (apid.rcv_transaction_id, -1) ap_rcv_txn_id,
apid.invoice_id apid_invoice_id, pod.attribute4 project_number,
poh.creation_date order_date, pvs.attribute1
FROM po_lines_all pol,
po_headers_all poh,
ap_suppliers pov,
po_line_types_vl plt,
po_line_locations_all pll,
po_releases_all porl,
mtl_system_items_b msi,
mtl_categories_vl mca,
fnd_currencies fnc,
po_distributions_all pod,
ap_invoice_distributions_all apid,
gl_code_combinations gcc1,
gl_code_combinations gcc2,
ap_supplier_sites_all pvs
WHERE poh.po_header_id = pol.po_header_id
AND pol.po_line_id = pll.po_line_id
AND pll.line_location_id = pod.line_location_id
AND pod.po_distribution_id = apid.po_distribution_id(+)
AND poh.type_lookup_code IN (‘BLANKET’, ‘STANDARD’, ‘PLANNED’)
AND pll.shipment_type IN (‘BLANKET’, ‘STANDARD’, ‘SCHEDULED’)
AND pol.line_type_id = plt.line_type_id
AND NVL (pol.order_type_lookup_code, ‘QUANTITY’) NOT IN
(‘RATE’, ‘FIXED PRICE’)
AND DECODE (NVL (:p_inc_online_accruals, ‘N’),
‘Y’, ‘N’,
NVL (pll.accrue_on_receipt_flag, ‘N’)
) = ‘N’
AND gcc1.code_combination_id = pod.code_combination_id
AND gcc2.code_combination_id = pod.accrual_account_id
AND (pll.quantity – NVL (pll.quantity_cancelled, 0) != 0)
AND ( ( NVL (pll.accrue_on_receipt_flag, ‘N’) = ‘N’
AND NVL (pod.accrue_on_receipt_flag, ‘N’) = ‘N’
)
OR ( NVL (:p_inc_online_accruals, ‘N’) = ‘Y’
AND NVL (pll.accrue_on_receipt_flag, ‘N’) = ‘Y’
AND NVL (pod.accrue_on_receipt_flag, ‘N’) = ‘Y’
)
)
AND msi.inventory_item_id(+) = pol.item_id
AND :organization_id = NVL (msi.organization_id, :organization_id)
AND mca.category_id = pol.category_id
AND porl.po_release_id(+) = pll.po_release_id
AND poh.vendor_id = pov.vendor_id
AND fnc.currency_code = :c_functional_currency
AND EXISTS (
SELECT ‘Got a receipt for this shipment’
FROM rcv_transactions rct
WHERE pll.line_location_id = rct.po_line_location_id
AND NVL (rct.consigned_flag, ‘N’) = ‘N’
AND rct.transaction_type IN (‘RECEIVE’, ‘MATCH’)
AND TRUNC (rct.transaction_date) <= :end_date)
AND (:p_vendor_from IS NULL OR pov.vendor_name >= :p_vendor_from)
AND (:p_vendor_to IS NULL OR pov.vendor_name <= :p_vendor_to)
AND ( NVL (pod.accrued_flag, ‘N’) =
DECODE (:p_yes_no,
NULL, NVL (pod.accrued_flag, ‘N’),
:p_yes_no
)
OR (NVL (:p_yes_no, ‘Y’) = ‘Y’ AND pod.accrue_on_receipt_flag = ‘Y’)
)
AND NVL (apid.line_type_lookup_code, ‘PO_NOT_INVOICED’) <> ‘PREPAY’
AND pov.vendor_id = pvs.vendor_id
AND poh.vendor_site_id = pvs.vendor_site_id
AND pvs.org_id = fnd_profile.VALUE (‘ORG_ID’)
AND NVL (pvs.attribute1, ‘%’) LIKE NVL (:ap_owner, ‘%’)
UNION ALL
SELECT pov.vendor_name c_sort,
poh.segment1
|| DECODE (porl.release_num, NULL, ”, ‘ – ‘ || porl.release_num)
po_release,
plt.line_type line_type, pol.line_num line, pol.item_description,
pov.vendor_name vendor, poh.currency_code c_currency,
fnc.minimum_accountable_unit min_acc_unit,
pll.price_override unit_price, pll.shipment_num shipment,
pll.unit_meas_lookup_code unit, pod.distribution_num distribution,
pod.quantity_ordered dist_quantity, pll.quantity ship_quantity,
1 shipment_price, pll.line_location_id p_po_line_location_id,
apid.quantity_invoiced, mca.segment1, mca.segment2, mca.segment3,
mca.segment4, mca.segment5, mca.segment6, mca.segment7, mca.segment8,
mca.segment9, mca.segment10, mca.segment11, mca.segment12,
mca.segment13, mca.segment14, mca.segment15, mca.segment16,
mca.segment17, mca.segment18, mca.segment19, mca.segment20,
pod.amount_ordered dist_qty_ordered, pll.amount ship_qty_ordered,
fnc.PRECISION PRECISION,
po_tax_sv.get_tax (‘PO’, pod.po_distribution_id) tax,
NVL (NVL (pod.rate, poh.rate), 1) exchange_rate,
NVL (pll.match_option, ‘P’) match_option,
NVL (apid.rcv_transaction_id, -1) ap_rcv_txn_id,
apid.invoice_id apid_invoice_id, pod.attribute4 project_number,
poh.creation_date order_date, pvs.attribute1
FROM po_lines_all pol,
po_headers_all poh,
ap_suppliers pov,
po_line_types_vl plt,
po_line_locations_all pll,
po_releases_all porl,
mtl_system_items_b msi,
mtl_categories_vl mca,
fnd_currencies fnc,
po_distributions_all pod,
ap_invoice_distributions_all apid,
gl_code_combinations gcc1,
gl_code_combinations gcc2,
ap_supplier_sites_all pvs
WHERE poh.po_header_id = pol.po_header_id
AND pol.po_line_id = pll.po_line_id
AND pll.line_location_id = pod.line_location_id
AND pod.po_distribution_id = apid.po_distribution_id(+)
AND poh.type_lookup_code IN (‘BLANKET’, ‘STANDARD’, ‘PLANNED’)
AND pll.shipment_type IN (‘BLANKET’, ‘STANDARD’, ‘SCHEDULED’)
AND pol.line_type_id = plt.line_type_id
AND NVL (pol.order_type_lookup_code, ‘QUANTITY’) IN
(‘RATE’, ‘FIXED PRICE’)
AND DECODE (NVL (:p_inc_online_accruals, ‘N’),
‘Y’, ‘N’,
NVL (pll.accrue_on_receipt_flag, ‘N’)
) = ‘N’
AND gcc1.code_combination_id = pod.code_combination_id
AND gcc2.code_combination_id = pod.accrual_account_id
AND (pll.amount – NVL (pll.amount_cancelled, 0) != 0)
AND ( ( NVL (pll.accrue_on_receipt_flag, ‘N’) = ‘N’
AND NVL (pod.accrue_on_receipt_flag, ‘N’) = ‘N’
)
OR ( NVL (:p_inc_online_accruals, ‘N’) = ‘Y’
AND NVL (pll.accrue_on_receipt_flag, ‘N’) = ‘Y’
AND NVL (pod.accrue_on_receipt_flag, ‘N’) = ‘Y’
)
)
AND msi.inventory_item_id(+) = pol.item_id
AND :organization_id = NVL (msi.organization_id, :organization_id)
AND mca.category_id = pol.category_id
AND porl.po_release_id(+) = pll.po_release_id
AND poh.vendor_id = pov.vendor_id
AND fnc.currency_code = :c_functional_currency
AND EXISTS (
SELECT ‘Got a receipt for this shipment’
FROM rcv_transactions rct
WHERE pll.line_location_id = rct.po_line_location_id
AND NVL (rct.consigned_flag, ‘N’) = ‘N’
AND rct.transaction_type IN (‘RECEIVE’, ‘MATCH’)
AND TRUNC (rct.transaction_date) <= :end_date)
AND (:p_vendor_from IS NULL OR pov.vendor_name >= :p_vendor_from)
AND (:p_vendor_to IS NULL OR pov.vendor_name <= :p_vendor_to)
AND ( NVL (pod.accrued_flag, ‘N’) =
DECODE (:p_yes_no,
NULL, NVL (pod.accrued_flag, ‘N’),
:p_yes_no
)
OR (NVL (:p_yes_no, ‘Y’) = ‘Y’ AND pod.accrue_on_receipt_flag = ‘Y’)
)
AND NVL (apid.line_type_lookup_code, ‘PO_NOT_INVOICED’) <> ‘PREPAY’
AND pov.vendor_id = pvs.vendor_id
AND poh.vendor_site_id = pvs.vendor_site_id
AND pvs.org_id = fnd_profile.VALUE (‘ORG_ID’)
AND NVL (pvs.attribute1, ‘%’) LIKE NVL (:ap_owner, ‘%’)
–/*Query 3* Received line location Details/
SELECT rct.po_line_location_id line_location_id,
SUM (ROUND ((NVL (rct.source_doc_quantity, 0)), :p_qty_precision)
) qty_received,
rct.transaction_date receipt_date
FROM rcv_transactions rct, po_lines pol
WHERE rct.transaction_type IN (‘RECEIVE’, ‘MATCH’)
AND TRUNC (rct.transaction_date) <= :end_date
AND rct.po_line_location_id = :p_po_line_location_id
AND rct.po_line_id = pol.po_line_id
AND NVL (pol.order_type_lookup_code, ‘QUANTITY’) NOT IN
(‘RATE’, ‘FIXED PRICE’)
GROUP BY rct.po_line_location_id, rct.transaction_date
UNION ALL
SELECT rct.po_line_location_id line_location_id,
SUM (ROUND ((NVL (rct.amount, 0)), :p_qty_precision)) qty_received,
rct.transaction_date receipt_date
FROM rcv_transactions rct, po_lines pol
WHERE rct.transaction_type IN (‘RECEIVE’, ‘MATCH’)
AND TRUNC (rct.transaction_date) <= :end_date
AND rct.po_line_location_id = :p_po_line_location_id
AND rct.po_line_id = pol.po_line_id
AND NVL (pol.order_type_lookup_code, ‘QUANTITY’) IN
(‘RATE’, ‘FIXED PRICE’)
GROUP BY rct.po_line_location_id, rct.transaction_date;
–/*Query 4* Corrected received line location Details /
SELECT rct.po_line_location_id line_location_id,
SUM (ROUND (NVL (rct1.source_doc_quantity, 0), :p_qty_precision)
) qty_corrected
FROM rcv_transactions rct, rcv_transactions rct1, po_lines pol
WHERE rct.transaction_type IN (‘RECEIVE’, ‘MATCH’)
AND rct.po_line_location_id = :p_po_line_location_id
AND rct1.transaction_type = ‘CORRECT’
AND rct1.parent_transaction_id = rct.transaction_id
AND TRUNC (rct1.transaction_date) <= :end_date
AND rct1.po_line_location_id = :p_po_line_location_id
AND rct.po_line_id = pol.po_line_id
AND NVL (pol.order_type_lookup_code, ‘QUANTITY’) NOT IN
(‘RATE’, ‘FIXED PRICE’)
GROUP BY rct.po_line_location_id
UNION ALL
SELECT rct.po_line_location_id line_location_id,
SUM (ROUND (NVL (rct1.amount, 0), :p_qty_precision)) qty_corrected
FROM rcv_transactions rct, rcv_transactions rct1, po_lines pol
WHERE rct.transaction_type IN (‘RECEIVE’, ‘MATCH’)
AND rct.po_line_location_id = :p_po_line_location_id
AND rct1.transaction_type = ‘CORRECT’
AND rct1.parent_transaction_id = rct.transaction_id
AND TRUNC (rct1.transaction_date) <= :end_date
AND rct1.po_line_location_id = :p_po_line_location_id
AND rct.po_line_id = pol.po_line_id
AND NVL (pol.order_type_lookup_code, ‘QUANTITY’) IN
(‘RATE’, ‘FIXED PRICE’)
GROUP BY rct.po_line_location_id;
–/*Query 5* Return location Details /
SELECT rct.po_line_location_id line_location_id,
SUM (ROUND ((NVL (rct.source_doc_quantity, 0)), :p_qty_precision)
) qty_returned
FROM rcv_transactions rct, po_lines pol
WHERE rct.transaction_type = ‘RETURN TO VENDOR’
AND TRUNC (rct.transaction_date) <= :end_date
AND rct.po_line_location_id = :p_po_line_location_id
AND rct.po_line_id = pol.po_line_id
AND NVL (pol.order_type_lookup_code, ‘QUANTITY’) NOT IN
(‘RATE’, ‘FIXED PRICE’)
GROUP BY rct.po_line_location_id
UNION ALL
SELECT rct.po_line_location_id line_location_id,
SUM (ROUND ((NVL (rct.amount, 0)), :p_qty_precision)) qty_returned
FROM rcv_transactions rct, po_lines pol
WHERE rct.transaction_type = ‘RETURN TO VENDOR’
AND TRUNC (rct.transaction_date) <= :end_date
AND rct.po_line_location_id = :p_po_line_location_id
AND rct.po_line_id = pol.po_line_id
AND NVL (pol.order_type_lookup_code, ‘QUANTITY’) IN
(‘RATE’, ‘FIXED PRICE’)
GROUP BY rct.po_line_location_id;
–/*Query 6* Corrected return location Details /
SELECT rct.po_line_location_id line_location_id,
SUM (ROUND (NVL (rct1.source_doc_quantity, 0), :p_qty_precision)
) qty_returned_corrected
FROM rcv_transactions rct, rcv_transactions rct1, po_lines pol
WHERE rct.transaction_type = ‘RETURN TO VENDOR’
AND rct.po_line_location_id = :p_po_line_location_id
AND rct1.transaction_type = ‘CORRECT’
AND rct1.parent_transaction_id = rct.transaction_id
AND TRUNC (rct1.transaction_date) <= :end_date
AND rct.po_line_location_id = :p_po_line_location_id
AND rct.po_line_id = pol.po_line_id
AND NVL (pol.order_type_lookup_code, ‘QUANTITY’) NOT IN
(‘RATE’, ‘FIXED PRICE’)
GROUP BY rct.po_line_location_id
UNION ALL
SELECT rct.po_line_location_id line_location_id,
SUM (ROUND (NVL (rct1.amount, 0), :p_qty_precision)
) qty_returned_corrected
FROM rcv_transactions rct, rcv_transactions rct1, po_lines pol
WHERE rct.transaction_type = ‘RETURN TO VENDOR’
AND rct.po_line_location_id = :p_po_line_location_id
AND rct1.transaction_type = ‘CORRECT’
AND rct1.parent_transaction_id = rct.transaction_id
AND TRUNC (rct1.transaction_date) <= :end_date
AND rct.po_line_location_id = :p_po_line_location_id
AND pol.po_line_id = rct.po_line_id
AND NVL (pol.order_type_lookup_code, ‘QUANTITY’) IN
(‘RATE’, ‘FIXED PRICE’)
GROUP BY rct.po_line_location_id;
What we expect in the script.
This script helps us to comprehend Uninvoiced Receipts Report. Couple of table which is being used are
po_lines_all pol,
po_headers_all poh,
ap_suppliers pov,
po_line_types_vl plt,
po_line_locations_all pll,
po_releases_all porl,
mtl_system_items_b msi etc.
Summary
This Post described the script Uninvoiced Receipts Report in Oracle EBS R12.
Got any queries?
Do drop a note by writing us at doyen.ebiz@gmail.com or use the comment section below to ask your questions.
