Oracle Applications Inventory Aging Query

For Implementing Inventory Aging Report Via XML Publisher
Features of this report are

1. Backdated Values of Cost (Average Cost or Standard Cost based on Organisational setup in MTL_PARAMETERS Table )
2. Aging in 5 User Configurable Buckets
3. One report for both Standard Costing and Average Costing

Following are the steps

1. Register the Concurrent program (report) with executable XDODTEXE and Method Java Concurrent Program
2. Register Data Definition (XML Publisher Administrator Responsibility --> Data Definitions)
3. Register Template (XML Publisher Administrator Responsibility --> Templates)

following is the link  Click to Download  for

• Views for receipt and issue of inventory
• Database package to be registered in the instance
• the Data Definition
• RTF Template

objects have to be registered in the same sequence starting from views and ending with the template.

(Note: for 11.5.9 instance you would need to make sure that xml publisher is implemented on our site and this report works good on R12 )

if you have downloaded, that is all you need to close this object.


and if you are just looking for query, here is the one used for Inventory Aging,


/* Final Query to be changed as per implementation of item code */

SELECT ev1.INVENTORY_ITEM_ID,


ev1.ITEM_CODE ITEM,

XXRG_HR_PKG.get_org_name(:ORG_ID) ORGANIZATION,

ev1.DESCRIPTION,

SUM(Buk11) buk11,

SUM(Buk21) buk21 ,

SUM(Buk31) buk31 ,

SUM(Buk41) buk41,

SUM(Buk51) buk51

FROM

(SELECT ev1.INVENTORY_ITEM_ID,

ITEM_CODE,

DESCRIPTION,

(

CASE

WHEN age BETWEEN :BUK1_DAYS_FROM AND :BUK1_DAYS_TO

THEN SUM(aqty)

ELSE NULL

END) Buk11,

(

CASE

WHEN age BETWEEN :BUK2_DAYS_FROM AND :BUK2_DAYS_TO

THEN SUM(aqty)

ELSE NULL

END) Buk21,

(

CASE

WHEN age BETWEEN :BUK3_DAYS_FROM AND :BUK3_DAYS_TO

THEN SUM(aqty)

ELSE NULL

END) Buk31,

(

CASE

WHEN age BETWEEN :BUK4_DAYS_FROM AND :BUK4_DAYS_TO

THEN SUM(aqty)

ELSE NULL

END) Buk41,

(

CASE

WHEN age >= :BUK5_DAYS_FROM

THEN SUM(aqty)

ELSE NULL

END) Buk51

FROM

(SELECT

ITEM_CODE,

DESCRIPTION,

TRANSACTION_DATE,

TRANSACTION_QUANTITY,

SUM(TRANSACTION_QUANTITY) OVER(PARTITION BY INVENTORY_ITEM_ID ORDER BY TRANSACTION_ID,TRANSACTION_DATE)+ NVL(NQTY,0) BFF ,

(

CASE

WHEN TRANSACTION_QUANTITY > SUM(TRANSACTION_QUANTITY) OVER(PARTITION BY INVENTORY_ITEM_ID ORDER BY TRANSACTION_ID,TRANSACTION_DATE)+ NVL(NQTY,0)

THEN SUM(TRANSACTION_QUANTITY) OVER(PARTITION BY INVENTORY_ITEM_ID ORDER BY TRANSACTION_ID,TRANSACTION_DATE) +NVL(NQTY,0)

ELSE TRANSACTION_QUANTITY

END) AQTY

--,TCOST

,

NVL(fnd_conc_date.string_to_date(:TILL_DATE),SYSDATE)-fnd_conc_date.string_to_date(TRANSACTION_DATE) AGE,

inventory_item_id

FROM

(SELECT V1.TRANSACTION_ID,

V1.ITEM_CODE,

V1.DESCRIPTION,

TRUNC(

CASE

WHEN V1.TRANSACTION_TYPE_ID = 4

THEN V1.TRANSACTION_DATE - 700

ELSE TRANSACTION_DATE

END) TRANSACTION_DATE,

V1.TRANSACTION_QUANTITY,

V1.INVENTORY_ITEM_ID ,

(SELECT SUM(V2.TRANSACTION_QUANTITY)

FROM XX_RGSTOCKAGEAGE_ISS_V V2

WHERE V1.INVENTORY_ITEM_ID =V2.INVENTORY_ITEM_ID

AND TRUNC(V2.TRANSACTION_DATE)<= NVL(:TILL_DATE,SYSDATE)

AND V2.organization_id = :ORG_ID

) NQTY

FROM XX_RGSTOCKAGE_REC_V V1

WHERE V1.organization_id =:ORG_ID

AND TRUNC(V1.TRANSACTION_DATE)<= NVL(:TILL_DATE,SYSDATE)

GROUP BY V1.TRANSACTION_ID,

V1.TRANSACTION_TYPE_ID,

V1.ITEM_CODE,

V1.DESCRIPTION,

V1.TRANSACTION_DATE,

V1.TRANSACTION_QUANTITY,

V1.INVENTORY_ITEM_ID--,V1.TRANSACTION_COST,V1.ACTUAL_COST,V1.PRIOR_COST

)

)ev1

WHERE ev1.aqty>0

GROUP BY ev1.inventory_item_id,

ev1.ITEM_CODE,

ev1.description,

ev1.age



UNION ALL



SELECT INVENTORY_ITEM_ID,



ITEM_CODE,

DESCRIPTION,

QTY BUK11,

NULL BUK21,

NULL BUK31,

NULL BUK41,

NULL BUK51

FROM

(SELECT

si.SEGMENT1 ITEM_CODE ,

si.description,

mmt.inventory_item_id,

SUM (mmt.transaction_quantity) qty

FROM mtl_material_transactions mmt,

mtl_system_items si

WHERE mmt.inventory_item_id = si.inventory_item_id

AND mmt.organization_id = si.organization_id

AND si.organization_id = :ORG_ID

AND TRUNC(TRANSACTION_DATE) <= NVL(:TILL_DATE,SYSDATE)

and si.inventory_item_id = mmt.inventory_item_id

GROUP BY mmt.inventory_item_id,

si.SEGMENT1,

si.description

HAVING SUM (mmt.transaction_quantity) < 0

)

) ev1

GROUP BY

ev1.ITEM_CODE,

ev1.INVENTORY_ITEM_ID,

ev1.DESCRIPTION,

XXRG_HR_PKG.get_org_name(:ORG_ID)

ORDER BY

ev1.ITEM_CODE

--***************Receipt View*******************--

CREATE OR REPLACE VIEW XX_RGSTOCKAGE_REC_V

(SEGMENT2, SEGMENT1, DESCRIPTION, INVENTORY_ITEM_ID, TRANSACTION_ID,

TRANSACTION_DATE, TRANSACTION_TYPE_ID, TRANSACTION_QUANTITY, ACTUAL_COST, PRIOR_COST,

NEW_COST, TRANSACTION_COST, TRANSACTION_TYPE_NAME, ORGANIZATION_ID, FROM_ORG_ID)

AS

select ms.segment2, ms.segment1,ms. description,ms.inventory_item_id,mt .transaction_id,

mt.transaction_date,mt. transaction_type_id,

transaction_quantity,actual_cost,prior_cost, new_cost,mt.TRANSACTION_COST,mtt .TRANSACTION_TYPE_NAME,MT.ORGANIZATION_ID ,nvl(MT.TRANSFER_ORGANIZATION_ID, 0)

from mtl_material_transactions mt,mtl_transaction_types mtt,mtl_system_items ms

where mt.transaction_type_id = mtt .transaction_type_id

and mt.organization_id=ms. organization_id

and mt.inventory_item_id=ms. inventory_item_id

--and mt.transaction_type_id in (15,123,44,42,101,121,43,18,4,3)

--and mt.organization_id in (344,392,348,302,209,208,207,206,205,204,203,202,104,216)

AND MT.TRANSACTION_QUANTITY > 0

--***************End of Receipt View*******************--

--***************issues View*******************--

CREATE OR REPLACE VIEW XX_RGSTOCKAGEAGE_ISS_V

(SEGMENT2, SEGMENT1, DESCRIPTION, INVENTORY_ITEM_ID, TRANSACTION_ID,

TRANSACTION_DATE, TRANSACTION_TYPE_ID, TRANSACTION_QUANTITY, ACTUAL_COST, PRIOR_COST,

NEW_COST, TRANSACTION_COST, TRANSACTION_TYPE_NAME, ORGANIZATION_ID, FROM_ORG_ID)

AS

select

ms.segment2, ms.segment1,ms. description,MS.inventory_item_id,transaction_id ,trunc(transaction_date) transaction_date ,mt.transaction_type_id,

transaction_quantity ,actual_cost,prior_cost, new_cost,mt.TRANSACTION_COST,mtt .TRANSACTION_TYPE_NAME,MT.ORGANIZATION_ID ,nvl(MT.TRANSFER_ORGANIZATION_ID, 0)

from mtl_material_transactions mt,mtl_transaction_types mtt,mtl_system_items ms

where mt.transaction_type_id = mtt .transaction_type_id

and mt.organization_id=ms. organization_id

and mt.inventory_item_id=ms. inventory_item_id

--and mt.transaction_type_id in (100,35,32,33,120,17,4,3,36,122)

--and mt.organization_id in (344,392,348,302,209,208,207,206,205,204,203,202,104,216)

AND MT.TRANSACTION_QUANTITY <0>

--***************issues View*******************--

--***************For Average Cost*******************--

select new_cost from mtl_material_transactions

where inventory_item_id = :item_id

and organization_id = :org_id

and trunc(transaction_date) <= :till_date

and transaction_id in (select max(transaction_id) from mtl_material_transactions

where inventory_item_id = :item_id

and organization_id = :org_id

and trunc(transaction_date) <= :till_date)

--***************For Average Cost*******************--

--***************For Standard Cost*******************--

select standard_cost from CST_COST_HISTORY_V

where inventory_item_id = :item_id

and organization_id = :org_id

and trunc(update_date) <= :till_date

and cost_update_id in (select max(cost_update_id) from CST_COST_HISTORY_V

where inventory_item_id = :item_id

and organization_id = :org_id

and trunc(update_date) <= :till_date)

--***************For Standard Cost*******************--

Disclaimer : Royal Group and Oracle are trademarks of respective companies