INV Organization parameters listing

Description
Categories: BI Publisher, Logistics
Application: Inventory
Source: Organization parameters listing (XML)
Short Name: INVSROPT_XML
DB package: INV_INVSROPT_XMLP_PKG
select 
          ood.organization_code     ORG_CODE,
         a.master_organization_id  MASTER_ORG_ID, 
         o1.organization_code       MASTER_ORG_CD, 
         o1.organization_name       MASTER_ORG_NM,
         a.cost_organization_id    COST_ORG_ID, 
         o2.organization_code      COST_ORG_CD, 
         o2.organization_name      COST_ORG_NM,
         a.source_organization_id  SOURCE_ORG_ID, 
         o3.organization_code      SOURCE_ORG_CD, 
         o3.organization_name      SOURCE_ORG_NM,
         mfg1.meaning   PRIMARY_COST_METHOD,
         br.resource_code DEFAULT_MATL_SUB_ELEM,
         mfg2.meaning   NEGATIVE_BALANCE,
         mfg11.meaning  GL_UPDATE_CODE,  
         a.calendar_code  CALENDAR_CODE, 
         bom1.exception_set_name CALENDAR_EXCEPTION_SET_NAME,
         a.default_demand_class  DEFAULT_DEMAND_CLASS, 
         mfg12.meaning ENCUMBRANCE_REVERSAL_FLAG, 
         mfg3.meaning   LOCATOR_CONTROL,
         mfg4.meaning   INTERORG_TRANSFER_CODE,
         DECODE(a.maintain_fifo_qty_stack_type, NULL,'',mfg6.meaning)
               MAINTAIN_FIFO_COST,
         mfg7.meaning   SERIAL_NUMBER_TYPE,
         mfg8.meaning   LOT_NUMBER_UNIQUENESS,
         mfg9.meaning   LOT_NUMBER_GENERATION,
         DECODE(a.LOT_NUMBER_ZERO_PADDING, NULL, '' , 
                        mfg10.meaning)
                        LOT_NUMBER_ZERO_PADDING,
         b.rule_name    ATP_RULE_NAME,
         c.picking_rule_name  PICKING_RULE_NAME,
         a.default_locator_order_value,
         a.default_subinv_order_value,
         a.interorg_trnsfr_charge_percent  intorg_charge_percent,
         a.auto_serial_alpha_prefix,
         a.start_auto_serial_number,
         a.auto_lot_alpha_prefix,
         a.lot_number_length,
         mfg13.meaning                      SERIAL_GENERATION,
         mfg14.meaning                      SOURCE_TYPE,
         a.source_subinventory              SOURCE_SUBINV,       
         a.purchase_price_var_account       PPV_flexid, 
         &P_ppv_account                     PPV_flexdat,
         a.invoice_price_var_account        IPV_flexid, 
         &P_ipv_account                     IPV_flexdat,
         a.encumbrance_account              ENCUMBRANCE_flexid,
         &P_encumbrance_acct                ENCUMBRANCE_flexdat,
         a.expense_account                  EXPENSE_flexid, 
         &P_expense_account                 EXPENSE_flexdat,
         a.sales_account                    SALES_flexid, 
         &P_sales_account                   SALES_flexdat,
         a.material_account                 MATERIAL_flexid,         
         null                material_flexdat,
         a.material_overhead_account  mat_burden_flexid,       
         null           mat_burden_flexdat,
         a.resource_account                resource_flexid,
         null              resource_flexdat,
         a.ap_accrual_account            ap_accrual_flexid,
        null            ap_accural_flexdat,
         a.overhead_account                resource_ovh_flexid,
       null          resource_ovh_flexdat,
       a.outside_processing_account      value_added_flexid, 
       null               value_added_flexdat,         
         a.intransit_inv_account           intransit_flexid,
       null                  intransit_flexdat,
         a.interorg_receivables_account    intorg_receivables_flexid,
        null       intorg_receivables_flexdat,
         a.interorg_price_var_account      intorg_ppv_flexid,
        null                 intorg_ppv_flexdat,
         a.interorg_payables_account       intorg_payables_flexid,
        null          intorg_payables_flexdat,
         a.cost_of_sales_account           sales_cost_flexid,
        null                sales_cost_flexdat,
        a.average_cost_var_account           AVG_CST_VAR_flexid, 
        &P_avg_cost_var_account              AVG_CST_VAR_flexdat,
         a.interorg_transfer_cr_account    intorg_transfer_flexid,
        null            intorg_transfer_flexdat ,
        a.starting_revision     STARTING_REVISION, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('f_intorg_transfer_flex', 'SQLGL', 'GL#', GLC_13.CHART_OF_ACCOUNTS_ID, NULL, GLC_13.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') F_INTORG_TRANSFER_FLEX, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('f_intorg_payables_flex', 'SQLGL', 'GL#', GLC_11.CHART_OF_ACCOUNTS_ID, NULL, GLC_11.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') F_INTORG_PAYABLES_FLEX, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('f_intransit_flex', 'SQLGL', 'GL#', GLC_8.CHART_OF_ACCOUNTS_ID, NULL, GLC_8.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') F_INTRANSIT_FLEX, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('f_intorg_receivables_flex', 'SQLGL', 'GL#', GLC_9.CHART_OF_ACCOUNTS_ID, NULL, GLC_9.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') F_INTORG_RECEIVABLES_FLEX, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('f_intorg_ppv_flex', 'SQLGL', 'GL#', GLC_10.CHART_OF_ACCOUNTS_ID, NULL, GLC_10.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') f_intorg_ppv_flex, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('f_material_flex', 'SQLGL', 'GL#', GLC_1.CHART_OF_ACCOUNTS_ID, NULL, GLC_1.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') F_MATERIAL_FLEX, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('f_sales_cost_flex', 'SQLGL', 'GL#', GLC_12.CHART_OF_ACCOUNTS_ID, NULL, GLC_12.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') F_SALES_COST_FLEX, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('f_ap_accrual_flex', 'SQLGL', 'GL#', GLC_5.CHART_OF_ACCOUNTS_ID, NULL, GLC_5.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') F_AP_ACCRUAL_FLEX, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('f_mat_burden_flex', 'SQLGL', 'GL#', GLC_2.CHART_OF_ACCOUNTS_ID, NULL, GLC_2.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') F_MAT_BURDEN_FLEX, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('f_resource_flex', 'SQLGL', 'GL#', GLC_4.CHART_OF_ACCOUNTS_ID, NULL, GLC_4.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_6.CHART_OF_ACCOUNTS_ID, NULL, GLC_6.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_7.CHART_OF_ACCOUNTS_ID, NULL, GLC_7.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') F_VALUE_ADDED_FLEX, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('f_ppv', 'SQLGL', 'GL#', GLC_14.CHART_OF_ACCOUNTS_ID, NULL, GLC_14.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') F_PPV, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('f_ipv', 'SQLGL', 'GL#', GLC_15.CHART_OF_ACCOUNTS_ID, NULL, GLC_15.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') F_IPV, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('f_encumbrance', 'SQLGL', 'GL#', GLC_16.CHART_OF_ACCOUNTS_ID, NULL, GLC_16.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') F_ENCUMBRANCE, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('f_expense', 'SQLGL', 'GL#', GLC_17.CHART_OF_ACCOUNTS_ID, NULL, GLC_17.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') F_EXPENSE, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('f_sales', 'SQLGL', 'GL#', GLC_18.CHART_OF_ACCOUNTS_ID, NULL, GLC_18.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') F_SALES, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('f_avg_cst_var', 'SQLGL', 'GL#', GLC_19.CHART_OF_ACCOUNTS_ID, NULL, GLC_19.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') F_AVG_CST_VAR
from
       mtl_parameters            a,
       mtl_atp_rules               b,
       mtl_picking_rules        c,
       org_organization_definitions ood,
       org_organization_definitions o1,
       org_organization_definitions o2,
       org_organization_definitions o3,
       bom_resources             br,
       bom_exception_sets     bom1,
       mfg_lookups               mfg1,
       mfg_lookups               mfg2,
       mfg_lookups               mfg3,
       mfg_lookups               mfg4,
       mfg_lookups               mfg6,
       mfg_lookups               mfg7,
       mfg_lookups               mfg8,
       mfg_lookups               mfg9,
       mfg_lookups               mfg10,
       mfg_lookups               mfg11,
       mfg_lookups               mfg12,
       mfg_lookups               mfg13,
       mfg_lookups               mfg14,
       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,
       gl_code_combinations      glc_8,
       gl_code_combinations      glc_9,
       gl_code_combinations      glc_10,
       gl_code_combinations      glc_11,
       gl_code_combinations      glc_12,
       gl_code_combinations      glc_13,
       gl_code_combinations      glc_14,
       gl_code_combinations      glc_15,
       gl_code_combinations      glc_16,
       gl_code_combinations      glc_17,
       gl_code_combinations      glc_18,
       gl_code_combinations      glc_19
where
        a.organization_id = br.organization_id (+)
   and  a.default_material_cost_id = br.resource_id (+)
   and  a.master_organization_id =  o1.organization_id (+)
   and  a.cost_organization_id = o2.organization_id (+)
   and  a.source_organization_id = o3.organization_id (+)
   and  a.organization_id = ood.organization_id (+) 
   and  a.organization_id = :P_org_id
   and  a.default_atp_rule_id = b.rule_id(+)
   and  a.default_picking_rule_id  = c.picking_rule_id(+)
   and  mfg1.lookup_type (+) = 'MTL_PRIMARY_COST'	
   and  a.primary_cost_method = mfg1.lookup_code(+)
   and  mfg2.lookup_type (+) = 'SYS_YES_NO'
   and  a.negative_inv_receipt_code = mfg2.lookup_code(+)
   and  mfg3.lookup_type (+) = 'MTL_LOCATION_CONTROL'
   and  a.stock_locator_control_code = mfg3.lookup_code(+)
   and  mfg4.lookup_type (+) = 'MTL_INTER_INV_TRANSFER'
   and  a.matl_interorg_transfer_code = mfg4.lookup_code(+)
   and  mfg6.lookup_type (+) = 'SYS_YES_NO'
   and  a.maintain_fifo_qty_stack_type = mfg6.lookup_code(+)
   and  mfg7.lookup_type (+) = 'MTL_SERIAL_NUMBER_TYPE'
   and  a.serial_number_type = mfg7.lookup_code(+)
   and  mfg8.lookup_type (+) = 'MTL_LOT_UNIQUENESS'
   and  a.lot_number_uniqueness = mfg8.lookup_code(+)
   and  mfg9.lookup_type (+) = 'MTL_LOT_GENERATION'
   and  a.lot_number_generation = mfg9.lookup_code(+)
   and  mfg10.lookup_type (+) = 'SYS_YES_NO'
   and a.lot_number_zero_padding = mfg10.lookup_code(+)
   and  mfg11.lookup_type (+) = 'SYS_YES_NO'
   and decode(a.general_ledger_update_code,3,2,1) = mfg11.lookup_code(+)
   and  mfg12.lookup_type (+) = 'SYS_YES_NO' 
   and a.encumbrance_reversal_flag = mfg12.lookup_code(+)
   and mfg13.lookup_type (+) = 'MTL_SERIAL_GENERATION' 
   and a.serial_number_generation = mfg13.lookup_code(+) 
   and mfg14.lookup_type (+) = 'MTL_SOURCE_TYPES' 
   and a.source_type = mfg14.lookup_code (+) 
   and a.material_account                  = glc_1.code_combination_id (+)
   and a.material_overhead_account         = glc_2.code_combination_id (+)
   and a.matl_ovhd_absorption_acct         = glc_3.code_combination_id (+)
   and a.resource_account                  = glc_4.code_combination_id (+)
   and a.ap_accrual_account                = glc_5.code_combination_id (+)
   and a.overhead_account                  = glc_6.code_combination_id (+)
   and a.outside_processing_account        = glc_7.code_combination_id (+)
   and a.intransit_inv_account             = glc_8.code_combination_id (+)
   and a.interorg_receivables_account      = glc_9.code_combination_id (+)
   and a.interorg_price_var_account        = glc_10.code_combination_id (+)
   and a.interorg_payables_account         = glc_11.code_combination_id (+)
   and a.cost_of_sales_account             = glc_12.code_combination_id (+)
   and a.interorg_transfer_cr_account      = glc_13.code_combination_id (+)
   and a.purchase_price_var_account        = glc_14.code_combination_id (+)
   and a.invoice_price_var_account         = glc_15.code_combination_id (+)
   and a.encumbrance_account               = glc_16.code_combination_id (+)
   and a.expense_account                   = glc_17.code_combination_id (+)
   and a.sales_account                     = glc_18.code_combination_id (+)
   and a.average_cost_var_account   = glc_19.code_combination_id (+) 
   and a.calendar_exception_set_id = bom1.exception_set_id (+)
Parameter Name SQL text Validation
GL Structure Number
 
Number
Organization
 
Number