BOM Resource

SELECT res.resource_id              resource_id,
                   res.resource_code            Name,
                   res.description              Description,
                   res.resource_type            resource_type,
                   lu1.meaning                  Type,
                   res.functional_currency_flag functional_currencty_flag,
                   lu2.meaning                  Functional_Currency,
                   res.unit_of_measure          UOM,
                   res.cost_element_id          cost_element_id,
                   ce.cost_element              Cost_Element,
                   res.autocharge_type          autocharge_type,
                   lu3.meaning                  Autocharge,
                   msi.description              pi_description,
                   gl1.description              aa_description,
                   gl2.description              rv_description,
                   res.allow_costs_flag         allow_costs_flag,
                   lu5.meaning                  Allow_Costs,
                   res.standard_rate_flag       standard_rate_flag,
                   lu6.meaning                  Std_Rate,
                   res.default_activity_id      default_activity_id,
                   act.activity                 Activity,
                   res.default_basis_type       default_basis_type,
                   lu7.meaning                  Basis,
                   res.disable_date             Disable_Date,
                   res.purchase_item_id         purchase_item_id,
                   res.absorption_account       absorption_account,
                   res.rate_variance_account    rate_variance_account,
                fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flexfield', 'INV', 'MSTK', 101, msi.ORGANIZATION_ID, msi.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') C_FLEXFIELD,
                fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flexfield2', 'SQLGL', 'GL#', gl1.CHART_OF_ACCOUNTS_ID, NULL, gl1.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') C_FLEXFIELD2,
                fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flexfield3', 'SQLGL', 'GL#', gl2.CHART_OF_ACCOUNTS_ID, NULL, gl2.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') C_FLEXFIELD3
            FROM   bom_resources res,
                   mfg_lookups  lu1, mfg_lookups lu2, mfg_lookups lu3,
                   mfg_lookups  lu5, mfg_lookups lu6, mfg_lookups lu7,
                   cst_cost_elements ce,
                   cst_activities act,
                   mtl_system_items msi,
                   gl_code_combinations gl1,
                   gl_code_combinations gl2
            WHERE  res.organization_id = :P_ORG_ID
              and  msi.inventory_item_id(+) = res.purchase_item_id
              and  (msi.organization_id = res.organization_id or
                    res.purchase_item_id is null)
              and  gl1.code_combination_id(+) = res.absorption_account
              and  gl2.code_combination_id(+) = res.rate_variance_account
              and  ((res.resource_code between :P_FROM and :P_TO
                     and
                     :P_FROM is not null and :P_TO is not null)
                    or
                    (res.resource_code <= :P_TO and :P_FROM is null and :P_TO is not null)
                    or
                    (res.resource_code >= :P_FROM and :P_FROM is not null and :P_TO is null)
                    or
                    (:P_FROM is null and :P_TO is null))
              and  res.cost_code_type in (3,4)
              and  res.cost_element_id = ce.cost_element_id
              and  res.default_activity_id = act.activity_id(+)
              and  lu1.lookup_type = 'BOM_RESOURCE_TYPE' and
                   lu1.lookup_code = res.resource_type
              and  lu2.lookup_type = 'SYS_YES_NO' and
                   lu2.lookup_code = res.functional_currency_flag
              and  lu3.lookup_type = 'BOM_AUTOCHARGE_TYPE' and
                   lu3.lookup_code = res.autocharge_type
              and  lu5.lookup_type = 'SYS_YES_NO' and
                   lu5.lookup_code = res.allow_costs_flag
              and  lu6.lookup_type = 'SYS_YES_NO' and
                   lu6.lookup_code = res.standard_rate_flag
              and  lu7.lookup_type(+) = 'CST_BASIS'
              and  lu7.lookup_code(+) = res.default_basis_type
            ORDER BY resource_code