INV Inventory Aging

âžœ Back to Library
Description
Categories: BI Publisher
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