INV Item definition detail

select
      decode(:P_break_id, 1, &P_cat_flex, &P_Item_flex)                     break_col1,
      decode(:P_break_id, 1, &P_Item_flex, &P_cat_flex)                    break_col2,
      decode(:P_break_id, 1, &P_cat_flex, 'X')       	                          C_cat_flex,
      &P_item_flex	 C_item_flex,
      msi_tl.description,
      msi_tl.long_description,
      msi.base_item_id,
      msi.engineering_item_id
	                  &C_bom_sel
	                  &C_gen_info_sel
                                  &C_eam_sel
		&C_cst_sel
		&C_po_sel
		&C_rcv_sel
		&C_inv_sel
		&C_phys_sel
		&C_web_sel
		&C_gp_sel
		&C_mps_sel
		&C_lead_sel
		&C_wip_sel
		&C_oe_sel
                               &C_OPM_Sel
		&C_invc_sel
                                &C_service_info_sel
                   ,decode(:P_break_id, 2, &P_cat_flex, 'X')    C_cat_flex2
                   ,msi.wip_supply_locator_id wip_supply_locator_id
                   ,msi.vmi_minimum_units vmi_minimum_units
                  ,msi.vmi_minimum_days vmi_minimum_days
                  ,msi.vmi_maximum_units vmi_maximum_units
                  ,msi.vmi_maximum_days vmi_maximum_days
                  ,msi.vmi_fixed_order_quantity vmi_fixed_order_quantity
       ,INV_MEANING_SEL.c_fnd_lookup_vl(msi.so_authorization_flag,'MTL_MSI_GP_RELEASE_AUTH')  so_authorization_flag
                  ,decode(msi.consigned_flag,'1',:P_YES_VALUE,:P_NO_VALUE) consigned_flag
                  ,decode(msi.asn_autoexpire_flag,'1',:P_YES_VALUE,:P_NO_VALUE) asn_autoexpire_flag
       ,INV_MEANING_SEL.c_fnd_lookup_vl(msi.vmi_forecast_type,'MTL_MSI_GP_FORECAST_TYPE') vmi_forecast_type
                  ,msi.forecast_horizon forecast_horizon
             ,msi.days_tgt_inv_supply days_tgt_inv_supply
                 ,msi.days_tgt_inv_window days_tgt_inv_window
                ,msi.days_max_inv_supply days_max_inv_supply
                ,msi.days_max_inv_window days_max_inv_window
              ,decode(msi.drp_planned_flag,'1',:P_YES_VALUE,:P_NO_VALUE) drp_planned_flag
             ,decode(msi.outsourced_assembly,'1',:P_YES_VALUE,:P_NO_VALUE) outsourced_assembly
    ,INV_MEANING_SEL.c_fnd_lookup_vl(msi.continous_transfer,'MTL_MSI_MRP_INT_ORG') continous_transfer
    ,INV_MEANING_SEL.c_fnd_lookup_vl(msi.convergence,'MTL_MSI_MRP_CONV_SUPP') convergence
    ,INV_MEANING_SEL.c_fnd_lookup_vl(msi.divergence,'MTL_MSI_MRP_DIV_SUPP') divergence,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_cat_pad', 'INV', 'MCAT', MC.STRUCTURE_ID, NULL, MC.CATEGORY_ID, 'ALL', 'Y', 'PADDED_VALUE') C_cat_pad,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_cat_field', 'INV', 'MCAT', MC.STRUCTURE_ID, NULL, MC.CATEGORY_ID, 'ALL', 'Y', 'VALUE')  C_cat_field,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_cat_pad2', 'INV', 'MCAT', MC.STRUCTURE_ID, NULL, MC.CATEGORY_ID, 'ALL', 'Y', 'PADDED_VALUE') C_CAT_PAD2,
    fnd_flex_xml_publisher_apis.process_kff_combination_1('c_cat_field2', 'INV', 'MCAT', MC.STRUCTURE_ID, NULL, MC.CATEGORY_ID, 'ALL', 'Y', 'VALUE') C_cat_field2,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_item_pad', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'PADDED_VALUE') C_item_pad,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_item_field', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') C_item_field,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_bom_item_field', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') C_bom_item_field,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_eng_item_field', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') C_eng_item_field,
	/*fnd_flex_xml_publisher_apis.process_kff_combination_1('c_cst_acct_field', 'SQLGL', 'GL#', CST.CHART_OF_ACCOUNTS_ID, NULL, CST.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') C_cst_acct_field,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_acat_field', 'OFA', 'CAT#', 101, NULL, PACF.CATEGORY_ID, 'ALL', 'Y', 'VALUE') C_acat_field,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_exp_acct_field', 'SQLGL', 'GL#', PXAF.CHART_OF_ACCOUNTS_ID, NULL, PXAF.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') C_exp_acct_field,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_enc_acct_field', 'SQLGL', 'GL#', PEAF.CHART_OF_ACCOUNTS_ID, NULL, PEAF.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') C_enc_acct_field,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_wip_loc_field', 'INV', 'MTLL', 101, LOC.ORGANIZATION_ID, LOC.INVENTORY_LOCATION_ID, 'ALL', 'Y', 'VALUE') C_wip_loc_field,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_invc_acct_field', 'SQLGL', 'GL#', IAF.CHART_OF_ACCOUNTS_ID, NULL, IAF.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') C_invc_acct_field*/
    decode(msi.asset_category_id ,null,null,&C_acat_field1) C_acat_field_1,
	decode(msi.encumbrance_account,null,null,&C_exp_acct_field1) C_exp_acct_field_1,
	decode(msi.expense_account,null,null,&C_enc_acct_field1) C_enc_acct_field_1,
	decode(msi.wip_supply_locator_id,null,null,&C_wip_loc_field1) C_wip_loc_field_1,
	decode(msi.Sales_account,null,null,&C_invc_acct_field1) C_invc_acct_field_1,
	decode(msi.cost_of_sales_account,null,null,&C_cst_acct_field1) C_cst_acct_field_1,
	/*&C_acat_field1 C_acat_field_1,
    &C_exp_acct_field1 C_exp_acct_field_1,
    &C_enc_acct_field1 C_enc_acct_field_1,
    &C_wip_loc_field1 C_wip_loc_field_1,
    &C_invc_acct_field1 C_invc_acct_field_1,
    &C_cst_acct_field1 C_cst_acct_field_1,*/
	INV_INVIRITD_XMLP_PKG.resolve_vendor_warranty('x') C_resolve_vendor_warranty_flag,
	INV_INVIRITD_XMLP_PKG.resolve_material_billable('x') C_resolve_material_billable,
	INV_INVIRITD_XMLP_PKG.resolve_serviceable_product(msi.segment1) C_resolve_servcble_prod_flag,
	INV_INVIRITD_XMLP_PKG.resolve_service_item_flag('x') C_resolve_service_item_flag
from	&C_cat_from
&C_gen_info_from
&C_bom_from
&C_eam_from
&C_cst_from
&C_po_from
&C_rcv_from
&C_inv_from
&C_phys_from
&C_gp_from
&C_mps_from
&C_wip_from
&C_oe_from
&C_OPM_From
&C_invc_from
mtl_system_items_b		msi,
mtl_system_items_tl                    msi_tl
where	&P_item_where
and  	msi.organization_id = :P_org_id
and  	msi.inventory_item_id  = msi_tl.inventory_item_id
and 	msi.organization_id = msi_tl.organization_id
and	msi_tl.language 	  = userenv('LANG')
and  	&P_cat_where &C_cat_where
	&C_gen_info_where
	&C_bom_where
                &C_eam_where
	&C_cst_where
	&C_po_where
	&C_rcv_where
	&C_inv_where
                  &C_phys_where
	&C_gp_where
	&C_mps_where
	&C_wip_where
	&C_oe_where
               &C_OPM_Where
	&C_invc_where
order by 1 asc,  2 asc