INV Inventory Aging

➜ Back to Library
Description
Categories: BI Publisher, Logistics
Application: Inventory
Source: Inventory Aging Report(XML)
Short Name: INVAGERP_XML
DB package: INV_AGERPXML_PKG 
SELECT 
       bucket_seq,
       organization_id,
       decode(:P_AGE_LEVEL,1,null,subinventory_code) subinventory_code,
       decode(:P_AGE_LEVEL,3,locator_code,null) locator_code,
       inventory_item_id,
       item_code,
       item_category,
       item_desc,
       uom_code,
       owing_party,
       buckets_days_heading,
       to_char(Max(last_trx_date),'DD-MON-YYYY') last_trx_date,
       Trunc(SYSDATE)-Min(first_trx_date) n_age,
       Sum(on_hand) on_hand,
       Max(sum_on_hand) t_onhand,
       Sum(Round(n_Value,2)) n_value
from
(
SELECT organization_id,
       subinventory_code,
       locator_code,
       inventory_item_id,
       item_code,
       item_category,
       item_desc,
       uom_code,
       owing_party,
       buckets_days_heading,
       INV_AGERPXML_PKG.f_bucket_days_seq(:P_BUCKETS_ID,buckets_days_heading) bucket_seq,
       Trunc(Max(last_trx_date)) last_trx_date,
       Trunc(Min(first_trx_date)) first_trx_date,
       Sum(on_hand) on_hand,
       Max(sum_on_hand) sum_on_hand,
       Sum(n_Value) n_Value
FROM (
SELECT 
       --msib.concatenated_segments  item_code ,
       fnd_flex_xml_publisher_apis.process_kff_combination_1('cf_item', 'INV', 'MSTK', 101, MSIB.ORGANIZATION_ID, MSIB.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') item_code,
       moqd.organization_id,               
       moqd.subinventory_code,
       inv_utilities.get_conc_segments(moqd.organization_id,moqd.locator_id) locator_code,
       moqd.inventory_item_id,  
       --MIC.CATEGORY_CONCAT_SEGS item_category,
       fnd_flex_xml_publisher_apis.process_kff_combination_1('cf_cat_field', 'INV', 'MCAT', MIC.STRUCTURE_ID, NULL, MIC.CATEGORY_ID, 'ALL', 'Y', 'VALUE') item_category,
       msib.description item_desc,
       Decode(is_consigned, 1, Decode (moqd.owning_tp_type, 1,
       (SELECT vendor_name || '-' || pvsa.vendor_site_code 
                                                          FROM  po_vendors pv,
                                                                po_vendor_sites_all pvsa
                                                          WHERE pvsa.vendor_id = pv.vendor_id
                                                          AND   pvsa.vendor_site_id = moqd.owning_organization_id),
                                                      2,  (SELECT(substr(hout.NAME,1,60)||'-'||substr(mp.ORGANIZATION_CODE,1,3)) Party 
                                                          FROM
                                                                hr_organization_information hoi
                                                               ,hr_all_organization_units_tl hout
                                                               ,mtl_parameters mp
                                                          WHERE    
                                                                hoi.organization_id = hout.organization_id
                                                            AND hout.organization_id =mp.organization_id
                                                            AND  hout.language = userenv ('LANG')
                                                            AND  hoi.org_information1='OPERATING_UNIT'
                                                            AND  hoi.org_information2='Y'
                                                            AND  hoi.org_information_context='CLASS'
                                                            AND  hoi.organization_id= MOQD.owning_organization_id
                                                            AND  moqd.organization_id <> moqd.owning_organization_id)
                                      )
                           ,NULL) owing_party,
       --moqd.last_update_date last_trx_date,
       CASE   :P_AGE_LEVEL
        when  '1' then    max(moqd.last_update_date) over(partition by moqd.organization_id,moqd.inventory_item_id,moqd.is_consigned,moqd.owning_tp_type,Decode(moqd.is_consigned,1,moqd.owning_organization_id,null))  
        when  '2' THEN    max(moqd.last_update_date) over(partition by moqd.organization_id,moqd.inventory_item_id,moqd.subinventory_code,moqd.is_consigned,moqd.owning_tp_type,Decode(moqd.is_consigned,1,moqd.owning_organization_id,null))  
        when  '3' THEN    max(moqd.last_update_date) over(partition by moqd.organization_id,moqd.inventory_item_id,moqd.subinventory_code,moqd.locator_id,moqd.is_consigned,moqd.owning_tp_type,Decode(moqd.is_consigned,1,moqd.owning_organization_id,null))
       END last_trx_date,
       CASE   :P_AGE_LEVEL
        when  '1' then    min(moqd.last_update_date) over(partition by moqd.organization_id,moqd.inventory_item_id,moqd.is_consigned,moqd.owning_tp_type,Decode(moqd.is_consigned,1,moqd.owning_organization_id,null))  
        when  '2' THEN    min(moqd.last_update_date) over(partition by moqd.organization_id,moqd.inventory_item_id,moqd.subinventory_code,moqd.is_consigned,moqd.owning_tp_type,Decode(moqd.is_consigned,1,moqd.owning_organization_id,null))  
        when  '3' THEN    min(moqd.last_update_date) over(partition by moqd.organization_id,moqd.inventory_item_id,moqd.subinventory_code,moqd.locator_id,moqd.is_consigned,moqd.owning_tp_type,Decode(moqd.is_consigned,1,moqd.owning_organization_id,null))
       END first_trx_date,
       moqd.primary_transaction_quantity on_hand,
       CASE   :P_AGE_LEVEL
        when  '1' then    sum(moqd.primary_transaction_quantity) over(partition by moqd.organization_id,moqd.inventory_item_id,moqd.is_consigned,moqd.owning_tp_type,Decode(moqd.is_consigned,1,moqd.owning_organization_id,null))  
        when  '2' THEN    sum(moqd.primary_transaction_quantity) over(partition by moqd.organization_id,moqd.inventory_item_id,moqd.subinventory_code,moqd.is_consigned,moqd.owning_tp_type,Decode(moqd.is_consigned,1,moqd.owning_organization_id,null))  
        when  '3' THEN    sum(moqd.primary_transaction_quantity) over(partition by moqd.organization_id,moqd.inventory_item_id,moqd.subinventory_code,moqd.locator_id,moqd.is_consigned,moqd.owning_tp_type,Decode(moqd.is_consigned,1,moqd.owning_organization_id,null))
       END sum_on_hand,
       moqd.primary_transaction_quantity * INV_AGERPXML_PKG.f_unit_cost(moqd.organization_id, moqd.inventory_item_id,moqd.locator_id,moqd.cost_group_id,Nvl(moqd.orig_date_received,moqd.creation_date)) n_Value,
       msib.PRIMARY_UOM_CODE uom_code,
       INV_AGERPXML_PKG.f_bucket_days_heading(:P_BUCKETS_ID,Nvl(moqd.orig_date_received,moqd.creation_date)) buckets_days_heading  --p_buckets_day  
       --moqd.orig_date_received      
FROM  mtl_onhand_quantities_detail moqd,
      MTL_SYSTEM_ITEMS_vl msib,
      MTL_SECONDARY_INVENTORIES_FK_V msiv,
      MTL_ITEM_CATEGORIES_V MIC,
      mtl_item_locations_kfv mil,
      CST_COST_GROUPS CCG
WHERE moqd.organization_id = msib.organization_id
AND   moqd.inventory_item_id = msib.inventory_item_id
AND   msib.organization_id = :P_ORG_ID
AND   msib.INVENTORY_ASSET_FLAG = Decode(:P_EXP_ITEM,'2','Y', msib.INVENTORY_ASSET_FLAG)
AND   msiv.organization_id = moqd.organization_id
AND   msiv.secondary_inventory_name = moqd.subinventory_code
AND   msiv.asset_inventory = Decode(:P_EXP_SUB,'2',1,msiv.asset_inventory)
AND   MOQD.COST_GROUP_ID = CCG.COST_GROUP_ID
AND   CCG.COST_GROUP BETWEEN NVL(:P_CG_FROM, CCG.COST_GROUP) AND NVL(:P_CG_TO, CCG.COST_GROUP)
AND   msib.organization_id = mic.organization_id
AND   msib.inventory_item_id = mic.inventory_item_id
AND   moqd.organization_id = mil.organization_id (+)
AND   moqd.locator_id = mil.inventory_location_id(+)
AND    mic.category_set_id = :P_CATEGORY_SET
AND   &P_ITEM_WHERE
AND   &P_CAT_WHERE
)
GROUP BY 
       organization_id,
       subinventory_code ,
       locator_code,
       inventory_item_id,
       item_code,
       item_category,
       item_desc,
       uom_code,
       owing_party,
       INV_AGERPXML_PKG.f_bucket_days_seq(:P_BUCKETS_ID,buckets_days_heading),
       buckets_days_heading
UNION ALL
--below is for heading all onhand and value is 0
SELECT 
       organization_id,
       subinventory_code,
       locator_code,
       inventory_item_id,
       item_code,
       item_category,
       item_desc,
       uom_code,
       owing_party,
       aablt.report_heading1 || ' ' || aablt.report_heading2 buckets_days_heading,
       aabl.bucket_sequence_num bucket_seq,
       last_trx_date,
       first_trx_date,
       0 on_hand,
       sum_on_hand,
       0 n_Value
 FROM
(SELECT organization_id,
       subinventory_code,
       locator_code,
       inventory_item_id,
       item_code,
       item_category,
       item_desc,
       uom_code,
       owing_party,
       buckets_days_heading,
       INV_AGERPXML_PKG.f_bucket_days_seq(:P_BUCKETS_ID,buckets_days_heading) bucket_seq,
       Trunc(Max(last_trx_date)) last_trx_date,
       Trunc(Min(first_trx_date)) first_trx_date,
       Sum(on_hand) on_hand,
       Max(sum_on_hand) sum_on_hand,
       Sum(n_Value) n_Value
FROM (
SELECT 
       --msib.concatenated_segments   item_code ,
       fnd_flex_xml_publisher_apis.process_kff_combination_1('cf_item', 'INV', 'MSTK', 101, MSIB.ORGANIZATION_ID, MSIB.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') item_code,
       moqd.organization_id,               
       moqd.subinventory_code,
       inv_utilities.get_conc_segments(moqd.organization_id,moqd.locator_id) locator_code,
       moqd.inventory_item_id,  
       --MIC.CATEGORY_CONCAT_SEGS item_category,
       fnd_flex_xml_publisher_apis.process_kff_combination_1('cf_cat_field', 'INV', 'MCAT', MIC.STRUCTURE_ID, NULL, MIC.CATEGORY_ID, 'ALL', 'Y', 'VALUE') item_category,
       msib.description item_desc,
       Decode(is_consigned, 1, Decode (moqd.owning_tp_type, 1,
       (SELECT vendor_name || '-' || pvsa.vendor_site_code 
                                                          FROM  po_vendors pv,
                                                                po_vendor_sites_all pvsa
                                                          WHERE pvsa.vendor_id = pv.vendor_id
                                                          AND   pvsa.vendor_site_id = moqd.owning_organization_id),
                                                      2,  (SELECT(substr(hout.NAME,1,60)||'-'||substr(mp.ORGANIZATION_CODE,1,3)) Party 
                                                          FROM
                                                                hr_organization_information hoi
                                                               ,hr_all_organization_units_tl hout
                                                               ,mtl_parameters mp
                                                          WHERE    
                                                                hoi.organization_id = hout.organization_id
                                                            AND hout.organization_id =mp.organization_id
                                                            AND  hout.language = userenv ('LANG')
                                                            AND  hoi.org_information1='OPERATING_UNIT'
                                                            AND  hoi.org_information2='Y'
                                                            AND  hoi.org_information_context='CLASS'
                                                            AND  hoi.organization_id= MOQD.owning_organization_id
                                                            AND  moqd.organization_id <> moqd.owning_organization_id)
                                      )
                           ,NULL) owing_party,
       --moqd.last_update_date last_trx_date,
       CASE   :P_AGE_LEVEL
        when  '1' then    max(moqd.last_update_date) over(partition by moqd.organization_id,moqd.inventory_item_id,moqd.is_consigned,moqd.owning_tp_type,Decode(moqd.is_consigned,1,moqd.owning_organization_id,null))  
        when  '2' THEN    max(moqd.last_update_date) over(partition by moqd.organization_id,moqd.inventory_item_id,moqd.subinventory_code,moqd.is_consigned,moqd.owning_tp_type,Decode(moqd.is_consigned,1,moqd.owning_organization_id,null))  
        when  '3' THEN    max(moqd.last_update_date) over(partition by moqd.organization_id,moqd.inventory_item_id,moqd.subinventory_code,moqd.locator_id,moqd.is_consigned,moqd.owning_tp_type,Decode(moqd.is_consigned,1,moqd.owning_organization_id,null))
       END last_trx_date,
       CASE   :P_AGE_LEVEL
        when  '1' then    min(moqd.last_update_date) over(partition by moqd.organization_id,moqd.inventory_item_id,moqd.is_consigned,moqd.owning_tp_type,Decode(moqd.is_consigned,1,moqd.owning_organization_id,null))  
        when  '2' THEN    min(moqd.last_update_date) over(partition by moqd.organization_id,moqd.inventory_item_id,moqd.subinventory_code,moqd.is_consigned,moqd.owning_tp_type,Decode(moqd.is_consigned,1,moqd.owning_organization_id,null))  
        when  '3' THEN    min(moqd.last_update_date) over(partition by moqd.organization_id,moqd.inventory_item_id,moqd.subinventory_code,moqd.locator_id,moqd.is_consigned,moqd.owning_tp_type,Decode(moqd.is_consigned,1,moqd.owning_organization_id,null))
       END first_trx_date,
       moqd.primary_transaction_quantity on_hand,
       CASE   :P_AGE_LEVEL
        when  '1' then    sum(moqd.primary_transaction_quantity) over(partition by moqd.organization_id,moqd.inventory_item_id,moqd.is_consigned,moqd.owning_tp_type,Decode(moqd.is_consigned,1,moqd.owning_organization_id,null))  
        when  '2' THEN    sum(moqd.primary_transaction_quantity) over(partition by moqd.organization_id,moqd.inventory_item_id,moqd.subinventory_code,moqd.is_consigned,moqd.owning_tp_type,Decode(moqd.is_consigned,1,moqd.owning_organization_id,null))  
        when  '3' THEN    sum(moqd.primary_transaction_quantity) over(partition by moqd.organization_id,moqd.inventory_item_id,moqd.subinventory_code,moqd.locator_id,moqd.is_consigned,moqd.owning_tp_type,Decode(moqd.is_consigned,1,moqd.owning_organization_id,null))
       END sum_on_hand,
              moqd.primary_transaction_quantity * INV_AGERPXML_PKG.f_unit_cost(moqd.organization_id, moqd.inventory_item_id,moqd.locator_id,moqd.cost_group_id,Nvl(moqd.orig_date_received,moqd.creation_date)) n_Value,
       msib.PRIMARY_UOM_CODE uom_code,
       INV_AGERPXML_PKG.f_bucket_days_heading(:P_BUCKETS_ID,Nvl(moqd.orig_date_received,moqd.creation_date)) buckets_days_heading  --p_buckets_day  
       --moqd.orig_date_received      
FROM  mtl_onhand_quantities_detail moqd,
      MTL_SYSTEM_ITEMS_vl msib,
      MTL_ITEM_CATEGORIES_V MIC,
      MTL_SECONDARY_INVENTORIES_FK_V msiv,
      mtl_item_locations_kfv mil,
      CST_COST_GROUPS CCG
WHERE moqd.organization_id = msib.organization_id
AND   moqd.inventory_item_id = msib.inventory_item_id
AND   moqd.organization_id = :P_ORG_ID
AND   msib.INVENTORY_ASSET_FLAG = Decode(:P_EXP_ITEM,'2','Y', msib.INVENTORY_ASSET_FLAG)
AND   msiv.organization_id = moqd.organization_id
AND   msiv.secondary_inventory_name = moqd.subinventory_code
AND msiv.asset_inventory = Decode(:P_EXP_SUB,'2',1,msiv.asset_inventory)
AND   MOQD.COST_GROUP_ID = CCG.COST_GROUP_ID
AND   CCG.COST_GROUP BETWEEN NVL(:P_CG_FROM, CCG.COST_GROUP) AND NVL(:P_CG_TO, CCG.COST_GROUP)
AND   msib.organization_id = mic.organization_id
AND   msib.inventory_item_id = mic.inventory_item_id
AND   moqd.organization_id = mil.organization_id (+)
AND   moqd.locator_id = mil.inventory_location_id(+)
AND    mic.category_set_id = :P_CATEGORY_SET
AND   &P_ITEM_WHERE
AND   &P_CAT_WHERE
)
GROUP BY 
       organization_id,
       subinventory_code ,
       locator_code,
       inventory_item_id,
       item_code,
       item_category,
       item_desc,
       uom_code,
       owing_party,
       INV_AGERPXML_PKG.f_bucket_days_seq(:P_BUCKETS_ID,buckets_days_heading),
       buckets_days_heading
 ) detail,
  AR_AGING_BUCKETS aab,
  AR_AGING_BUCKET_LINES_B AABL,   --AR_AGING_BUCKET_LINES_vl for language
  AR_AGING_BUCKET_LINES_tl aablt
WHERE  aab.aging_type = 'INVENTORYAGE'
AND     aabl.aging_bucket_id = aab.aging_bucket_id
AND     aab.status = 'A'
AND     aablt.aging_bucket_line_id = aabl.aging_bucket_line_id
AND     aablt.LANGUAGE = userenv ('LANG')
AND     aab.aging_bucket_id = :P_BUCKETS_ID
AND   aabl.type ='INVENTORYAGE'
)
WHERE bucket_seq not in  (-99999,-99998)
GROUP BY 
       organization_id,
       decode(:P_AGE_LEVEL,1,null,subinventory_code) ,
       decode(:P_AGE_LEVEL,3,locator_code,null) ,
       inventory_item_id,
       item_code,
       item_category,
       item_desc,
       uom_code,
       owing_party,
       buckets_days_heading,
       bucket_seq
ORDER BY 
       organization_id,
       subinventory_code,
       locator_code,
       bucket_seq,
       inventory_item_id,
       item_code,
       item_category,
       item_desc,
       uom_code,
       owing_party,
       buckets_days_heading
Parameter Name SQL text Validation
Category Structure 
 
 Number
Organization_Id 
 
 Number
Include Expense Subinventories 
 
 LOV Oracle
Include Expense Items 
 
 LOV Oracle
Buckets Days 
 
 LOV Oracle
Order By 
 
 LOV Oracle
Cost Group To 
 
 LOV Oracle
Cost Group From 
 
 LOV Oracle
Level 
 
 LOV Oracle
Item To 
 
Item From 
 
Category To 
 
Category From 
 
Category Set 
 
 LOV Oracle
Title 
 
Download
 
Related Blitz Reports
JA India - RG23C Part I Report - draft - draft
JA India - OSP Pending Receipt - draft
PO Receiving Exceptions
PO Receipt Adjustments
PO Overshipments
PO Substitute Receipts
JA India - RG 23 D - draft
PO Purchase Order and Releases Detail
JA India - Outside Processing Challan - draft