INV Subinventory listing

Description
Categories: BI Publisher, Logistics
Application: Inventory
Source: Subinventory listing (XML)
Short Name: INVSRSUB_XML
DB package: INV_INVSRSUB_XMLP_PKG
select
       a.secondary_inventory_name  subinventory,
       a.description,
       a.disable_date,
         mfg1.meaning atp_code,
         mfg2.meaning avail_code,
         mfg3.meaning reservable_code,
         mfg4.meaning locator_type,
         mfg6.meaning  quantity_tracked,
         a.picking_order,
         a.material_account           material_account,
         null  c_material_account,
         a.material_overhead_account  material_burden,
         null   c_material_burden_flexdat,
         a.resource_account           resource_account,
         null          c_resource_flexdat,
         a.overhead_account           overhead_account,
         null      c_resource_ovh_flexdat,
         a.outside_processing_account value_added,
         null       c_value_added_flexdat,
         mfg5.meaning                 inventory_type,
         a.encumbrance_account        encumbrance_account,
         &P_encumbrance_FLEXSQL       c_encumbrance_flexdat,
         a.expense_account            expense_account,
         &P_expense_FLEXSQL           c_expense_flexdat,
         a.source_type                source_type_id,
         mfg7.meaning                 source_type_name,
         a.source_organization_id     source_org_id,
         o.organization_code          source_org_code,
         o.organization_name          source_org_name,
         a.source_subinventory        source_subinv,
         a.requisition_approval_type  req_apprv_id,
         mfg8.meaning                 req_apprv_name,
         mfg9.meaning        default_count_type,
         mfg10.meaning      planning_level,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('f_encumbrance_flex', 'SQLGL', 'GL#', GLC_6.CHART_OF_ACCOUNTS_ID, NULL, GLC_6.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') F_ENCUMBRANCE_FLEX,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('f_expense_flex', 'SQLGL', 'GL#', GLC_7.CHART_OF_ACCOUNTS_ID, NULL, GLC_7.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') F_EXPENSE_FLEX,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('f_resource_flex', 'SQLGL', 'GL#', GLC_3.CHART_OF_ACCOUNTS_ID, NULL, GLC_3.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') F_RESOURCE_FLEX,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('f_resource_ovh_flex', 'SQLGL', 'GL#', GLC_4.CHART_OF_ACCOUNTS_ID, NULL, GLC_4.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') F_RESOURCE_OVH_FLEX,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('f_value_added_flex', 'SQLGL', 'GL#', GLC_5.CHART_OF_ACCOUNTS_ID, NULL, GLC_5.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') F_VALUE_ADDED_FLEX,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('f_material_account', 'SQLGL', 'GL#', GLC_1.CHART_OF_ACCOUNTS_ID, NULL, GLC_1.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') F_MATERIAL_ACCOUNT,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('f_material_burden_flex', 'SQLGL', 'GL#', GLC_2.CHART_OF_ACCOUNTS_ID, NULL, GLC_2.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') F_MATERIAL_BURDEN_FLEX
from
       mtl_secondary_inventories a,
       mfg_lookups mfg1,
       mfg_lookups mfg2,
       mfg_lookups mfg3,
       mfg_lookups mfg4,
       mfg_lookups mfg5,
       mfg_lookups mfg6,
       mfg_lookups mfg7,
       mfg_lookups mfg8,
       mfg_lookups mfg9,
       mfg_lookups mfg10,
       gl_code_combinations glc_1,
       gl_code_combinations glc_2,
       gl_code_combinations glc_3,
       gl_code_combinations glc_4,
       gl_code_combinations glc_5,
       gl_code_combinations glc_6,
       gl_code_combinations glc_7,
       org_organization_definitions o
where
          a.default_count_type_code = mfg9.lookup_code(+)
   and 'MTL_COUNT_TYPES' = mfg9.lookup_type (+)
   and a.planning_level = mfg10.lookup_code(+)
   and 'SYS_YES_NO' = mfg10.lookup_type(+)
   and 'MTL_SOURCE_TYPES' = mfg7.lookup_type(+)
   and a.source_type    = mfg7.lookup_code(+)
   and 'MTL_REQUISITION_APPROVAL' =  mfg8.lookup_type (+)
   and a.requisition_approval_type = mfg8.lookup_code(+)
   and 'SYS_YES_NO' = mfg6.lookup_type
   and a.quantity_tracked = mfg6.lookup_code
   and 'MTL_ATP_CODE' = mfg1.lookup_type
   and a.inventory_atp_code = mfg1.lookup_code
   and 'MTL_AVAILABILITY' = mfg2.lookup_type(+)
   and a.availability_type = mfg2.lookup_code(+)
   and 'MTL_RESERVABLE_SUBINVENTORY' = mfg3.lookup_type
   and a.reservable_type = mfg3.lookup_code
   and 'MTL_LOCATION_CONTROL' = mfg4.lookup_type
   and a.locator_type        = mfg4.lookup_code
   and 'SYS_YES_NO'     = mfg5.lookup_type
   and a.asset_inventory = mfg5.lookup_code
   and a.material_account = glc_1.code_combination_id (+)
   and a.material_overhead_account = glc_2.code_combination_id (+)
   and a.resource_account = glc_3.code_combination_id (+)
   and a.overhead_account = glc_4.code_combination_id (+)
   and a.outside_processing_account = glc_5.code_combination_id(+)
   and a.encumbrance_account = glc_6.code_combination_id(+)
   and a.expense_account = glc_7.code_combination_id(+)
   and a.source_organization_id = o.organization_id(+)
   and a.organization_id = :P_ORG_ID
   order by secondary_inventory_name
Parameter Name SQL text Validation
Account Flex Structure
 
Number
Organization
 
Number
Ask a question