INV Inventory Aging

➜ Back to Library
Description
Categories: BI Publisher
Imported from 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)
AND   :CF_PROFILE = 'Y'
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 NameSQL textValidation
Title
 
Char
Category Set
 
LOV Oracle
Category From
 
Char
Category To
 
Char
Item From
 
Char
Item To
 
Char
Level
 
LOV Oracle
Cost Group From
 
LOV Oracle
Cost Group To
 
LOV Oracle
Order By
 
LOV Oracle
Buckets Days
 
LOV Oracle
Include Expense Items
 
LOV Oracle
Include Expense Subinventories
 
LOV Oracle
Download
Blitz Report™