Below is the query to fetch the details of unpaid and partially invoices
SELECT Ledger_name,
po_number,
ORDER_DATE,
TRANSACTION_DATE,
VENDOR_NAME,
CATEGORY_SEG,
ITEM_DESCRIPTION,
LINE_TYPE,
ASSET_DESC,
PROJ_NUM,
LINE_NUM,
SHIPMENT_NUMBER,
QUANTITY_RECEIVED,
QUANTITY_BILLED,
UNIT,
PO_UNIT_PRICE,
PO_CURRENCY_CODE,
FUNC_UNIT_PRICE,
ACCRUAL_AMOUNT,
DISTRIBUTION_NUM,
DIST_QTY_ORDERED,
SHIPMENT_PRICE,
CHARGE_ACCOUNT ,
ACCRUAL_ACCOUNT,
FUNC_ACCRUAL_AMOUNT,
ACCRUAL_CURRENCY_CODE,
ACCRUAL_AMT_USD,
Country_Name
FROM (SELECT sob.name Ledger_name,
NVL(poh.CLM_DOCUMENT_NUMBER,poh.SEGMENT1) po_number,–Changed as a part of CLM
porl.release_num po_release_number,
poh.po_header_id po_header_id,
pol.po_line_id po_line_id,
POH.CREATION_DATE ORDER_DATE,
cpea.shipment_id po_shipment_id,
cpea.distribution_id po_distribution_id,
plt.line_type line_type,
nvl(POL.LINE_NUM_DISPLAY, to_char(POL.LINE_NUM)) line_num,–Changed as a part of CLM
msi.concatenated_segments item_name,
mca.concatenated_segments category_seg,
replace(REGEXP_REPLACE(pol.item_description,'(^[[:space:]]*|[[:space:]]*|[[(\s)]]*$)|’),’,’,”) item_description,
replace(pov.vendor_name,’,’,”) vendor_name,
fnc2.currency_code accrual_currency_code,
poll.shipment_num shipment_number,
poll.unit_meas_lookup_code uom_code,
pod.distribution_num distribution_num,
cpea.quantity_received quantity_received,
cpea.quantity_billed quantity_billed,
cpea.accrual_quantity quantity_accrued,
pod.quantity_ordered DIST_QTY_ORDERED,
ROUND(cpea.unit_price,
NVL(fnc2.extended_precision, 2)) po_unit_price,
cpea.currency_code po_currency_code,
ROUND(DECODE(NVL(fnc1.minimum_accountable_unit, 0),
0, cpea.unit_price * cpea.currency_conversion_rate,
(cpea.unit_price / fnc1.minimum_accountable_unit)
* cpea.currency_conversion_rate
* fnc1.minimum_accountable_unit),
NVL(fnc1.extended_precision, 2))
func_unit_price,
gcc1.concatenated_segments charge_account,
gcc2.concatenated_segments accrual_account,
cpea.accrual_amount accrual_amount,
nvl(poll.price_override,0) shipment_price,
ROUND(DECODE(NVL(fnc1.minimum_accountable_unit, 0),
0, cpea.accrual_amount * cpea.currency_conversion_rate,
(cpea.accrual_amount / fnc1.minimum_accountable_unit)
* cpea.currency_conversion_rate
* fnc1.minimum_accountable_unit), NVL(fnc1.precision, 2))
func_accrual_amount,
nvl(fnc2.extended_precision,2) PO_PRECISION,
nvl(fnc1.extended_precision,2) PO_FUNC_PRECISION,
nvl(fnc1.precision,2) ACCR_PRECISION,
pod.attribute1 PROJ_NUM,
(select replace(REGEXP_REPLACE(ipr.asset_desc,'(^[[:space:]]*|[[:space:]]*|[[(\s)]]*$)|’),’,’,”) from CUST_PROJ_REFERENCE_DATA_ALL ipr
where ipr.project_number = pod.attribute1
and ipr.Asset_tag_id= pod.attribute2) Asset_Desc,
poll.unit_meas_lookup_code UNIT,
(select max(transaction_date)
from rcv_transactions
where po_header_id = poh.PO_HEADER_ID
and currency_code=sob.currency_code
and transaction_type like ‘REC%’) TRANSACTION_DATE,
Round(ROUND(DECODE(NVL(fnc1.minimum_accountable_unit, 0),
0, cpea.accrual_amount * cpea.currency_conversion_rate,
(cpea.accrual_amount / fnc1.minimum_accountable_unit)
* cpea.currency_conversion_rate
* fnc1.minimum_accountable_unit), NVL(fnc1.precision, 2)) *
(select Conversion_rate
from gl_daily_rates
where to_currency=’USD’
and from_currency=fnc2.currency_code
and trunc(conversion_date)=trunc(sysdate)
and conversion_type=’Corporate’),2) Accrual_Amt_USD,
(select fv.Territory_short_name
from fnd_territories_vl fv, fnd_currencies fc
where fv.territory_code=fc.issuing_territory_code
and fc.currency_code=fnc2.currency_code) Country_Name
FROM cst_per_end_accruals_temp cpea,
po_headers_all poh,
po_lines_all pol,
po_line_locations_all poll,
po_distributions_all pod,
po_vendors pov,
po_line_types plt,
po_releases_all porl,
mtl_system_items_kfv msi,
fnd_currencies fnc1,
fnd_currencies fnc2,
mtl_categories_kfv mca,
gl_code_combinations_kfv gcc1,
gl_code_combinations_kfv gcc2,
— gl_sets_of_books sob –For Global ERP Replaced gl_sets_of_books with gl_ledgers by PShruti on 30-NOV-2016.
gl_ledgers sob,
hr_operating_units hou
WHERE pod.po_distribution_id = cpea.distribution_id
AND poh.po_header_id = pol.po_header_id
AND pol.po_line_id = poll.po_line_id
AND poll.line_location_id = pod.line_location_id
AND pol.line_type_id = plt.line_type_id
AND porl.po_release_id (+) = poll.po_release_id
AND poh.vendor_id = pov.vendor_id
AND msi.inventory_item_id (+) = pol.item_id
AND (msi.organization_id IS NULL
OR
(msi.organization_id = poll.ship_to_organization_id AND msi.organization_id IS NOT NULL))
AND fnc1.currency_code = cpea.currency_code
AND fnc2.currency_code = sob.currency_code
AND cpea.category_id = mca.category_id
AND gcc1.code_combination_id = pod.code_combination_id
AND gcc2.code_combination_id = pod.accrual_account_id
— AND sob.set_of_books_id = :l_sob_id
AND sob.ledger_id = c_sob_id
AND sob.ledger_id = hou.set_of_books_id
AND poh.org_id=hou.organization_id
AND P_INCLUDE_INV = ‘N’
AND cpea.quantity_billed = 0
/* AND (trunc((select max(transaction_date)
from rcv_transactions
where po_header_id = poh.PO_HEADER_ID
and transaction_type like ‘REC%’)) between trunc(sysdate-20) and trunc(sysdate))*/
union
SELECT sob.name Ledger_name,
NVL(poh.CLM_DOCUMENT_NUMBER,poh.SEGMENT1) po_number,–Changed as a part of CLM
porl.release_num po_release_number,
poh.po_header_id po_header_id,
pol.po_line_id po_line_id,
POH.CREATION_DATE ORDER_DATE,
cpea.shipment_id po_shipment_id,
cpea.distribution_id po_distribution_id,
plt.line_type line_type,
nvl(POL.LINE_NUM_DISPLAY, to_char(POL.LINE_NUM)) line_num,–Changed as a part of CLM
msi.concatenated_segments item_name,
mca.concatenated_segments category_seg,
replace(REGEXP_REPLACE(pol.item_description,'(^[[:space:]]*|[[:space:]]*|[[(\s)]]*$)|’),’,’,”) item_description,
replace(pov.vendor_name,’,’,”) vendor_name,
fnc2.currency_code accrual_currency_code,
poll.shipment_num shipment_number,
poll.unit_meas_lookup_code uom_code,
pod.distribution_num distribution_num,
cpea.quantity_received quantity_received,
cpea.quantity_billed quantity_billed,
cpea.accrual_quantity quantity_accrued,
pod.quantity_ordered DIST_QTY_ORDERED,
ROUND(cpea.unit_price,
NVL(fnc2.extended_precision, 2)) po_unit_price,
cpea.currency_code po_currency_code,
ROUND(DECODE(NVL(fnc1.minimum_accountable_unit, 0),
0, cpea.unit_price * cpea.currency_conversion_rate,
(cpea.unit_price / fnc1.minimum_accountable_unit)
* cpea.currency_conversion_rate
* fnc1.minimum_accountable_unit),
NVL(fnc1.extended_precision, 2))
func_unit_price,
gcc1.concatenated_segments charge_account,
gcc2.concatenated_segments accrual_account,
cpea.accrual_amount accrual_amount,
nvl(poll.price_override,0) shipment_price,
ROUND(DECODE(NVL(fnc1.minimum_accountable_unit, 0),
0, cpea.accrual_amount * cpea.currency_conversion_rate,
(cpea.accrual_amount / fnc1.minimum_accountable_unit)
* cpea.currency_conversion_rate
* fnc1.minimum_accountable_unit), NVL(fnc1.precision, 2))
func_accrual_amount,
nvl(fnc2.extended_precision,2) PO_PRECISION,
nvl(fnc1.extended_precision,2) PO_FUNC_PRECISION,
nvl(fnc1.precision,2) ACCR_PRECISION,
pod.attribute1 PROJ_NUM,
(select replace(REGEXP_REPLACE(ipr.asset_desc,'(^[[:space:]]*|[[:space:]]*|[[(\s)]]*$)|’),’,’,”) from CUST_PROJ_REFERENCE_DATA_ALL ipr
where ipr.project_number = pod.attribute1
and ipr.Asset_tag_id= pod.attribute2) Asset_Desc,
poll.unit_meas_lookup_code UNIT,
(select max(transaction_date)
from rcv_transactions
where po_header_id = poh.PO_HEADER_ID
and currency_code=sob.currency_code
and transaction_type like ‘REC%’) TRANSACTION_DATE,
Round(ROUND(DECODE(NVL(fnc1.minimum_accountable_unit, 0),
0, cpea.accrual_amount * cpea.currency_conversion_rate,
(cpea.accrual_amount / fnc1.minimum_accountable_unit)
* cpea.currency_conversion_rate
* fnc1.minimum_accountable_unit), NVL(fnc1.precision, 2)) *
(select Conversion_rate
from gl_daily_rates
where to_currency=’USD’
and from_currency=fnc2.currency_code
and trunc(conversion_date)=trunc(sysdate)
and conversion_type=’Corporate’),2) Accrual_Amt_USD,
(select fv.Territory_short_name
from fnd_territories_vl fv, fnd_currencies fc
where fv.territory_code=fc.issuing_territory_code
and fc.currency_code=fnc2.currency_code) Country_Name
FROM cst_per_end_accruals_temp cpea,
po_headers_all poh,
po_lines_all pol,
po_line_locations_all poll,
po_distributions_all pod,
po_vendors pov,
po_line_types plt,
po_releases_all porl,
mtl_system_items_kfv msi,
fnd_currencies fnc1,
fnd_currencies fnc2,
mtl_categories_kfv mca,
gl_code_combinations_kfv gcc1,
gl_code_combinations_kfv gcc2,
— gl_sets_of_books sob –For Global ERP Replaced gl_sets_of_books with gl_ledgers by PShruti on 30-NOV-2016.
gl_ledgers sob,
hr_operating_units hou
WHERE pod.po_distribution_id = cpea.distribution_id
AND poh.po_header_id = pol.po_header_id
AND pol.po_line_id = poll.po_line_id
AND poll.line_location_id = pod.line_location_id
AND pol.line_type_id = plt.line_type_id
AND porl.po_release_id (+) = poll.po_release_id
AND poh.vendor_id = pov.vendor_id
AND msi.inventory_item_id (+) = pol.item_id
AND (msi.organization_id IS NULL
OR
(msi.organization_id = poll.ship_to_organization_id AND msi.organization_id IS NOT NULL))
AND fnc1.currency_code = cpea.currency_code
AND fnc2.currency_code = sob.currency_code
AND cpea.category_id = mca.category_id
AND gcc1.code_combination_id = pod.code_combination_id
AND gcc2.code_combination_id = pod.accrual_account_id
— AND sob.set_of_books_id = :l_sob_id
AND sob.ledger_id = c_sob_id
AND sob.ledger_id = hou.set_of_books_id
AND poh.org_id=hou.organization_id
–AND cpea.quantity_billed = 0
AND P_INCLUDE_INV = ‘Y’
/* AND (trunc((select max(transaction_date)
from rcv_transactions
where po_header_id = poh.PO_HEADER_ID
and transaction_type like ‘REC%’)) between trunc(sysdate-20) and trunc(sysdate))*/
) Q
Group by
Ledger_name,
po_number,
ORDER_DATE,
TRANSACTION_DATE,
VENDOR_NAME,
CATEGORY_SEG,
ITEM_DESCRIPTION,
LINE_TYPE,
ASSET_DESC,
PROJ_NUM,
LINE_NUM,
SHIPMENT_NUMBER,
QUANTITY_RECEIVED,
QUANTITY_BILLED,
UNIT,
PO_UNIT_PRICE,
PO_CURRENCY_CODE,
FUNC_UNIT_PRICE,
ACCRUAL_AMOUNT,
DISTRIBUTION_NUM,
DIST_QTY_ORDERED,
SHIPMENT_PRICE,
CHARGE_ACCOUNT ,
ACCRUAL_ACCOUNT,
FUNC_ACCRUAL_AMOUNT,
ACCRUAL_CURRENCY_CODE,
ACCRUAL_AMT_USD,
Country_Name;
For any queries, you can message us at contact@staging.doyensys.com
