Introduction
This Query will help to Fetch the IPV Report Data in Oracle Apps.
SQL Query:
BEGIN
fnd_client_info.set_org_context (:p_org);
END;
SELECT glps.set_of_books_id, msi.organization_id, pov.vendor_id,
glps.period_name, api.invoice_num invoice_num,
aid.accounting_date invoice_date,
DECODE (DECODE (aid.quantity_invoiced,
0, 0,
NULL, 0,
NVL (aid.quantity_invoiced, 1)
/ ABS (NVL (aid.quantity_invoiced, 1))
),
0, plc1.displayed_field,
1, plc2.displayed_field,
-1, plc3.displayed_field
) entry_type,
ROUND
(( aid.quantity_invoiced
/ (DECODE (pll.match_option,
‘R’, po_uom_s.po_uom_convert_p
(pol.unit_meas_lookup_code,
rct.unit_of_measure,
pol.item_id
),
1
)
)
),
20
) qty_invoiced,
NVL (aid.exchange_rate, 1) invoice_rate, aid.amount invoice_amount,
aid.unit_price invoice_price, NVL (pod.rate,
NVL (poh.rate, 1)) po_rate,
pll.price_override po_price, rct.unit_of_measure receipt_unit,
pol.item_id item_id, msi.segment1 itemnumber,
REPLACE (msi.description, CHR (44), CHR (32)) description,
REPLACE (pov.vendor_name, CHR (44), CHR (32)) vendor,
DECODE (poh.type_lookup_code,
‘BLANKET’, poh.segment1 || ‘ – ‘ || por.release_num,
‘PLANNED’, poh.segment1 || ‘ – ‘ || por.release_num,
poh.segment1
) po_number_release,
poh.currency_code currency, api.invoice_currency_code invoice_currency,
pol.line_num line_num, pol.unit_meas_lookup_code unit,
lot.location_code LOCATION,
ROUND
( DECODE (aid.quantity_invoiced,
0, DECODE (aid.invoice_price_variance,
NULL, DECODE (aid.exchange_rate,
NULL, aid.amount,
aid.base_amount
),
aid.invoice_price_variance
* NVL (aid.exchange_rate, 1)
),
NULL, DECODE (aid.invoice_price_variance,
NULL, DECODE (aid.exchange_rate,
NULL, aid.amount,
aid.base_amount
),
aid.invoice_price_variance
* NVL (aid.exchange_rate, 1)
),
aid.unit_price * NVL (aid.exchange_rate, 1)
)
* (DECODE (pll.match_option,
‘R’, po_uom_s.po_uom_convert_p
(pol.unit_meas_lookup_code,
rct.unit_of_measure,
pol.item_id
),
1
)
),
2
) invoice_base_price,
ROUND
( pll.price_override
* DECODE (pll.match_option,
‘R’, DECODE (rct.transaction_id,
NULL, NVL (poh.rate, 1),
NVL (rct.currency_conversion_rate, 1)
),
NVL (poh.rate, 1)
),
2
) po_base_price —:c_exp_precision
,
aid.exchange_rate_variance ex_rate_vari,
aid.base_invoice_price_variance base_inv_price_var
FROM gl_code_combinations gcc1,
gl_code_combinations gcc,
gl_period_statuses glps,
po_distributions pod,
po_line_locations pll,
po_lines pol,
po_releases por,
po_headers poh,
po_vendors pov,
ap_invoice_distributions aid,
ap_invoices api,
mtl_system_items msi,
mtl_categories mca,
po_lookup_codes plc1,
po_lookup_codes plc2,
po_lookup_codes plc3,
hr_locations_all_tl lot,
rcv_transactions rct
WHERE 1 = 1
AND gcc.code_combination_id = pod.code_combination_id
AND gcc1.code_combination_id = pod.variance_account_id
AND pod.line_location_id = pll.line_location_id
AND pll.po_line_id = pol.po_line_id
AND aid.invoice_id = api.invoice_id
AND aid.posted_flag || ” = ‘Y’
AND aid.po_distribution_id = pod.po_distribution_id
AND pol.po_header_id = poh.po_header_id
AND pll.po_release_id = por.po_release_id(+)
AND poh.vendor_id = pov.vendor_id(+)
AND pol.item_id = msi.inventory_item_id(+)
AND msi.organization_id = :c_organization_id
AND pol.category_id = mca.category_id
AND lot.location_id(+) = pll.ship_to_location_id
AND pll.ship_to_location_id IS NOT NULL
AND aid.rcv_transaction_id = rct.transaction_id(+)
AND lot.LANGUAGE(+) = USERENV (‘LANG’)
AND pod.destination_type_code IN (‘INVENTORY’, ‘SHOP FLOOR’)
AND plc1.lookup_type = ‘POXRCIPV’
AND plc1.lookup_code = ‘ADJUSTMENT’
AND plc2.lookup_type = ‘POXRCIPV’
AND plc2.lookup_code = ‘ENTRY’
AND plc3.lookup_type = ‘POXRCIPV’
AND plc3.lookup_code = ‘REVERSAL’
AND glps.application_id = 201
AND aid.accounting_date BETWEEN glps.start_date AND glps.end_date
AND poh.type_lookup_code IN (‘STANDARD’, ‘BLANKET’, ‘PLANNED’)
AND pll.shipment_type IN (‘STANDARD’, ‘BLANKET’, ‘SCHEDULED’)
AND glps.period_name = p_period_name
AND glps.set_of_books_id = set_of_books;
Got any queries?
Do drop a note by writing us at Venkatesh.b@staging.doyensys.com or use the comment section below to ask your questions
