ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 8F71FACA8BC6E1EEE053B46B6358A1C6Y 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, 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, 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 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 1 = 1 and 1 = 1 ) 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, 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 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 1 = 1 and 1 = 1 ) 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 INV INVAGERP_XML 82288223F0503869E053B46B63588994 US RD INV Inventory Aging Application: Inventory Source: Inventory Aging Report(XML) Short Name: INVAGERP_XML DB package: INV_AGERPXML_PKG BI Publisher :l_profile :p_age_level :p_buckets_id :p_cat_from :p_cat_num :p_cat_set_where :p_cat_to :p_cat_where :p_category_set :p_cg_from :p_cg_to :p_conc_request_id :p_exp_item :p_exp_sub :p_item_from :p_item_to :p_item_where :p_locator_level :p_order_by :p_org_id :p_struct_num :p_sub_level :p_title 1 -20 :p_cat_num Char AR هيكل الفئة D Kategoriestruktur F Structure de catégorie US Category Structure ZHS 类别结构 2 -10 :p_org_id Char US Organization_Id 3 10 :p_title Char AR العنوان D Titel F Titre US Title ZHS 标题 4 20 :p_category_set LOV Oracle INV_SRS_CATEGORY_SET Y select category_set_id id, category_set_name value, description description from mtl_category_sets order by value,description SELECT CATEGORY_SET_NAME FROM MTL_CATEGORY_SETS A, MTL_DEFAULT_CATEGORY_SETS B WHERE B.FUNCTIONAL_AREA_ID = 5 AND A.CATEGORY_SET_ID = B.CATEGORY_SET_ID Y AR مجموعة الفئات D KategorieSet F Jeu de catégories US Category Set ZHS 类别集 5 30 :p_cat_from Char AR الفئة من D Von Kategorie F Catégorie - De US Category From ZHS 类别自 6 40 :p_cat_to Char AR الفئة إلى D Bis Kategorie F Catégorie - A US Category To ZHS 类别至 7 50 :p_item_from Char AR الصنف من D Von Artikel F Article - De US Item From ZHS 物料自 8 60 :p_item_to Char AR الصنف إلى D Bis Artikel F Article - A US Item To ZHS 物料至 9 70 :p_age_level LOV Oracle INV_AGE_LEVEL Y select lookup_code id, meaning value, meaning description from mfg_lookups where lookup_type='INVAGELEVEL' order by lookup_code SELECT MEANING FROM MFG_LOOKUPS WHERE LOOKUP_TYPE = 'INVAGELEVEL' AND LOOKUP_CODE = 1 Y AR المستوى D Ebene F Niveau US Level ZHS 层 10 80 :p_cg_from LOV Oracle CST_SRS_COST_GROUP_NAMES Y select ccg.cost_group id, ccg.cost_group value, ccg.description description from cst_cost_groups ccg,((select cost_group_id,organization_id from cst_cost_group_accounts) union (select default_cost_group_id cost_group_id,organization_id from mtl_parameters)) ccga where ccg.cost_group_id=ccga.cost_group_id and ccga.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') and nvl(ccg.disable_date,sysdate+1)>sysdate order by cost_group AR مجموعة التكلفة من D Von Kostengruppe F Groupe de coûts - De US Cost Group From ZHS 成本组自 11 90 :p_cg_to LOV Oracle CST_SRS_COST_GROUP_NAMES Y select ccg.cost_group id, ccg.cost_group value, ccg.description description from cst_cost_groups ccg,((select cost_group_id,organization_id from cst_cost_group_accounts) union (select default_cost_group_id cost_group_id,organization_id from mtl_parameters)) ccga where ccg.cost_group_id=ccga.cost_group_id and ccga.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') and nvl(ccg.disable_date,sysdate+1)>sysdate order by cost_group AR مجموعة التكلفة إلى D Bis Kostengruppe F Groupe de coûts - A US Cost Group To ZHS 成本组至 12 100 :p_order_by LOV Oracle INV_AGERP_ORDER Y select lookup_code id, meaning value, meaning description from mfg_lookups where lookup_type='INVAGESORT' order by lookup_code Y AR ترتيب حسب D Sortieren nach F Tri par US Order By ZHS 排序依据 13 110 :p_buckets_id LOV Oracle INV_AGE_BUCKETS_DAY Y select aging_bucket_id id, bucket_name value, bucket_name description from ar_aging_buckets where aging_type='INVENTORYAGE' and status='A' order by value,description Y AR أيام الرزم D Zeitraumtage F Jours de regroupement US Buckets Days ZHS 时段天数 14 120 :p_exp_item LOV Oracle INV_SRS_YES_NO Y select lookup_code id, meaning value, null description from mfg_lookups where lookup_type='SYS_YES_NO' order by lookup_code SELECT MEANING FROM MFG_LOOKUPS WHERE LOOKUP_TYPE='SYS_YES_NO' AND LOOKUP_CODE=2 Y AR تضمين بنود المصروفات D Mit Aufwandsartikeln F Inclure les articles passés en charges US Include Expense Items ZHS 包括低值易耗品 15 130 :p_exp_sub LOV Oracle INV_SRS_YES_NO Y select lookup_code id, meaning value, null description from mfg_lookups where lookup_type='SYS_YES_NO' order by lookup_code SELECT MEANING FROM MFG_LOOKUPS WHERE LOOKUP_TYPE = 'SYS_YES_NO' AND LOOKUP_CODE = 2 Y AR تضمين مخازن المصروفات D Mit Aufwandslägern F Inclure les magasins passés en charges US Include Expense Subinventories ZHS 包括费用子库存