CST Inventory Subledger

SELECT
                1                       AC_SOURCE_TYPE
            ,           CQL.COST_GROUP_ID               AC_CQL_CG_ID
            /*,   &P_ITEM_SEG AC_ITEM*/
            ,           MSI.DESCRIPTION                                             AC_ITEM_DESC
            ,           MSI.PRIMARY_UOM_CODE                                    AC_UOM
            ,   DECODE(:P_ITEM_REVISION, 1, MOH.REVISION, NULL)     AC_REVISION
            /*,   &P_CAT_SEG AC_CATEGORY*/
            ,   LU.MEANING                  AC_INV_ASSET
            ,           MOH.SUBINVENTORY_CODE                                   AC_SUBINV
            ,           SEC.DESCRIPTION                                             AC_SUBINV_DESC
            /*,   &P_LOC_SEG AC_LOC_SEG*/
            ,   MIL.PHYSICAL_LOCATION_CODE          AC_LOC
            ,   MIL.DESCRIPTION                 AC_LOC_DESC
            ,           MOH.TRANSACTION_QUANTITY                                AC_QUANTITY
            ,           ROUND(MOH.TRANSACTION_QUANTITY *
                                NVL(CQL.MATERIAL_COST,0) *
                                DECODE(SEC.ASSET_INVENTORY, 1, 1, 0) *
                                :P_EXCHANGE_RATE / :ROUND_UNIT) * :ROUND_UNIT       AC_MATL_COST
            ,           ROUND(MOH.TRANSACTION_QUANTITY *
                                NVL(CQL.MATERIAL_OVERHEAD_COST,0) *
                                DECODE(SEC.ASSET_INVENTORY, 1, 1, 0) *
                                :P_EXCHANGE_RATE / :ROUND_UNIT) * :ROUND_UNIT       AC_MOVH_COST
            ,           ROUND(MOH.TRANSACTION_QUANTITY *
                                NVL(CQL.RESOURCE_COST,0) *
                                DECODE(SEC.ASSET_INVENTORY, 1, 1, 0) *
                                :P_EXCHANGE_RATE / :ROUND_UNIT) * :ROUND_UNIT       AC_RES_COST
            ,           ROUND(MOH.TRANSACTION_QUANTITY *
                                NVL(CQL.OUTSIDE_PROCESSING_COST,0) *
                                DECODE(SEC.ASSET_INVENTORY, 1, 1, 0) *
                                :P_EXCHANGE_RATE / :ROUND_UNIT) * :ROUND_UNIT       AC_OSP_COST
            ,           ROUND(MOH.TRANSACTION_QUANTITY *
                                NVL(CQL.OVERHEAD_COST,0) *
                                DECODE(SEC.ASSET_INVENTORY, 1, 1, 0) *
                                :P_EXCHANGE_RATE / :ROUND_UNIT) * :ROUND_UNIT       AC_OVHD_COST
            ,           ROUND(MOH.TRANSACTION_QUANTITY *
                                NVL(CQL.ITEM_COST,0) *
                                DECODE(SEC.ASSET_INVENTORY, 1, 1, 0) *
                                :P_EXCHANGE_RATE / :ROUND_UNIT) * :ROUND_UNIT       AC_TOTAL_COST
                ,BOM_CSTRISLR_XMLP_PKG.ac_i_rowcountformula(:AC_I_QUANTITY) AC_I_ROWCOUNT,
                fnd_flex_xml_publisher_apis.process_kff_combination_1('ac_loc_pseg', 'INV', 'MTLL', 101, MIL.ORGANIZATION_ID, MIL.INVENTORY_LOCATION_ID, 'ALL', 'Y', 'PADDED_VALUE') AC_LOC_PSEG,
                fnd_flex_xml_publisher_apis.process_kff_combination_1('ac_loc_segment', 'INV', 'MTLL', 101, MIL.ORGANIZATION_ID, MIL.INVENTORY_LOCATION_ID, 'ALL', 'Y', 'VALUE') AC_LOC_SEGMENT,
                fnd_flex_xml_publisher_apis.process_kff_combination_1('ac_item_pseg', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'PADDED_VALUE') AC_ITEM_PSEG,
                fnd_flex_xml_publisher_apis.process_kff_combination_1('ac_category_segment', 'INV', 'MCAT', MC.STRUCTURE_ID, NULL, MC.CATEGORY_ID, 'ALL', 'Y', 'VALUE') AC_CATEGORY_SEGMENT,
                fnd_flex_xml_publisher_apis.process_kff_combination_1('ac_cat_pseg', 'INV', 'MCAT', MC.STRUCTURE_ID, NULL, MC.CATEGORY_ID, 'ALL', 'Y', 'PADDED_VALUE') AC_CAT_PSEG,
                fnd_flex_xml_publisher_apis.process_kff_combination_1('ac_item_segment', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') AC_ITEM_SEGMENT
            FROM    CST_QUANTITY_LAYERS         CQL
            ,           MTL_CATEGORIES                      MC
            ,           MTL_ITEM_CATEGORIES                 MIC
            ,           MTL_SYSTEM_ITEMS_VL                 MSI
            ,                  MTL_PARAMETERS                                        MP
            ,           MTL_SECONDARY_INVENTORIES       SEC
            ,           MTL_ONHAND_QUANTITIES           MOH
            ,   MTL_ITEM_LOCATIONS      MIL
            ,   MRP_PROJECT_PARAMETERS  MPP
            ,   MFG_LOOKUPS         LU
            WHERE       MOH.ORGANIZATION_ID                 =   :P_ORG_ID
            AND MOH.SUBINVENTORY_CODE BETWEEN
                    NVL(:P_SUBINV_FROM,  MOH.SUBINVENTORY_CODE)
            AND     NVL(:P_SUBINV_TO,  MOH.SUBINVENTORY_CODE)
            AND         SEC.ORGANIZATION_ID                 =   MOH.ORGANIZATION_ID
            AND         SEC.SECONDARY_INVENTORY_NAME    =   MOH.SUBINVENTORY_CODE
            AND         SEC.ASSET_INVENTORY                 =   1
            AND         MSI.ORGANIZATION_ID                 =   MOH.ORGANIZATION_ID
            AND         MSI.INVENTORY_ITEM_ID               =   MOH.INVENTORY_ITEM_ID
            AND MSI.INVENTORY_ASSET_FLAG    =   DECODE(:P_EXP_ITEM, 1, MSI.INVENTORY_ASSET_FLAG
                                        ,  'Y')
            AND         MIC.ORGANIZATION_ID                 =   MSI.ORGANIZATION_ID
            AND         MIC.INVENTORY_ITEM_ID               =   MSI.INVENTORY_ITEM_ID
            AND         MIC.CATEGORY_SET_ID                 =   :P_CATEGORY_SET
            AND         MC.CATEGORY_ID                      =   MIC.CATEGORY_ID
            AND         MIL.ORGANIZATION_ID             (+) =   MOH.ORGANIZATION_ID
            AND         MIL.INVENTORY_LOCATION_ID          (+) =    MOH.LOCATOR_ID
            AND         MPP.ORGANIZATION_ID             (+) =   MIL.ORGANIZATION_ID
            AND         MPP.PROJECT_ID                  (+) =   MIL.PROJECT_ID
            AND           MP.ORGANIZATION_ID                                  =                 MOH.ORGANIZATION_ID
            AND         CQL.ORGANIZATION_ID         =   MOH.ORGANIZATION_ID
            AND         CQL.INVENTORY_ITEM_ID           =   MOH.INVENTORY_ITEM_ID
            AND            CQL.COST_GROUP_ID                                    =  NVL(MPP.COSTING_GROUP_ID, MP.DEFAULT_COST_GROUP_ID)
            AND LU.LOOKUP_TYPE          =   'SYS_YES_NO'
            AND LU.LOOKUP_CODE          =   DECODE(MSI.INVENTORY_ASSET_FLAG,'Y',1,2)
            AND         &P_ITEM_WHERE
            AND         &P_CAT_WHERE
            and CQL.COST_GROUP_ID = :AC_CG_ID
            UNION ALL
            SELECT
                    2
            ,       CQL.COST_GROUP_ID
            /*,     null*/
            ,       MSI.DESCRIPTION
            ,       MSI.PRIMARY_UOM_CODE
            ,       DECODE(:P_ITEM_REVISION, 1, MMT.REVISION, NULL)
            /*,     null*/
            ,       LU.MEANING
            ,       SEC.SECONDARY_INVENTORY_NAME
            ,       SEC.DESCRIPTION
            /*,     null AC_LOC_SEG*/
            ,       MIL.PHYSICAL_LOCATION_CODE
            ,       MIL.DESCRIPTION
            ,       (-1)*MMT.PRIMARY_QUANTITY
            ,       ROUND(MMT.PRIMARY_QUANTITY *
                          NVL(CQL.MATERIAL_COST,0) *
                          DECODE(SEC.ASSET_INVENTORY, 1, -1, 0) *
                          :P_EXCHANGE_RATE / :ROUND_UNIT) * :ROUND_UNIT
            ,       ROUND(MMT.PRIMARY_QUANTITY *
                          NVL(CQL.MATERIAL_OVERHEAD_COST,0) *
                          DECODE(SEC.ASSET_INVENTORY, 1, -1, 0) *
                          :P_EXCHANGE_RATE / :ROUND_UNIT) * :ROUND_UNIT
            ,       ROUND(MMT.PRIMARY_QUANTITY *
                          NVL(CQL.RESOURCE_COST,0) *
                          DECODE(SEC.ASSET_INVENTORY, 1, -1, 0) *
                          :P_EXCHANGE_RATE / :ROUND_UNIT) * :ROUND_UNIT
            ,       ROUND(MMT.PRIMARY_QUANTITY *
                          NVL(CQL.OUTSIDE_PROCESSING_COST,0) *
                          DECODE(SEC.ASSET_INVENTORY, 1, -1, 0) *
                          :P_EXCHANGE_RATE / :ROUND_UNIT) * :ROUND_UNIT
            ,       ROUND(MMT.PRIMARY_QUANTITY *
                          NVL(CQL.OVERHEAD_COST,0) *
                          DECODE(SEC.ASSET_INVENTORY, 1, -1, 0) *
                          :P_EXCHANGE_RATE / :ROUND_UNIT) * :ROUND_UNIT
            ,       ROUND(MMT.PRIMARY_QUANTITY *
                          NVL(CQL.ITEM_COST,0) *
                          DECODE(SEC.ASSET_INVENTORY, 1, -1, 0) *
                          :P_EXCHANGE_RATE / :ROUND_UNIT) * :ROUND_UNIT,
                BOM_CSTRISLR_XMLP_PKG.ac_i_rowcountformula(:AC_I_QUANTITY) AC_I_ROWCOUNT,
                fnd_flex_xml_publisher_apis.process_kff_combination_1('ac_loc_pseg', 'INV', 'MTLL', 101, MIL.ORGANIZATION_ID, MIL.INVENTORY_LOCATION_ID, 'ALL', 'Y', 'PADDED_VALUE') AC_LOC_PSEG,
                fnd_flex_xml_publisher_apis.process_kff_combination_1('ac_loc_segment', 'INV', 'MTLL', 101, MIL.ORGANIZATION_ID, MIL.INVENTORY_LOCATION_ID, 'ALL', 'Y', 'VALUE') AC_LOC_SEGMENT,
                fnd_flex_xml_publisher_apis.process_kff_combination_1('ac_item_pseg', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'PADDED_VALUE') AC_ITEM_PSEG,
                fnd_flex_xml_publisher_apis.process_kff_combination_1('ac_category_segment', 'INV', 'MCAT', MC.STRUCTURE_ID, NULL, MC.CATEGORY_ID, 'ALL', 'Y', 'VALUE') AC_CATEGORY_SEGMENT,
                fnd_flex_xml_publisher_apis.process_kff_combination_1('ac_cat_pseg', 'INV', 'MCAT', MC.STRUCTURE_ID, NULL, MC.CATEGORY_ID, 'ALL', 'Y', 'PADDED_VALUE') AC_CAT_PSEG,
                fnd_flex_xml_publisher_apis.process_kff_combination_1('ac_item_segment', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') AC_ITEM_SEGMENT
            FROM        CST_QUANTITY_LAYERS             CQL
            ,           MTL_CATEGORIES                      MC
            ,           MTL_ITEM_CATEGORIES                 MIC
            ,           MTL_SYSTEM_ITEMS_VL                 MSI
            ,                  MTL_PARAMETERS                                        MP
            ,           MTL_SECONDARY_INVENTORIES       SEC
            ,           MTL_ITEM_LOCATIONS                  MIL
            ,           MRP_PROJECT_PARAMETERS          MPP
            ,           MTL_MATERIAL_TRANSACTIONS       MMT
            ,   MFG_LOOKUPS         LU
            WHERE       MMT.COSTED_FLAG                     >= 'A'
            AND         MMT.COSTED_FLAG                     <= 'Z'
            AND         MMT.ORGANIZATION_ID + 0         = :P_ORG_ID
            AND           MMT.ORGANIZATION_ID             = NVL(MMT.OWNING_ORGANIZATION_ID,MMT.ORGANIZATION_ID)
            AND           NVL(MMT.OWNING_TP_TYPE,2)=2
            AND           NVL(MMT.LOGICAL_TRANSACTION, 2) <> 1
            AND         SEC.ORGANIZATION_ID                 = MMT.ORGANIZATION_ID + 0
            AND         SEC.SECONDARY_INVENTORY_NAME    = NVL(MMT.SUBINVENTORY_CODE, NULL)
            AND         SEC.ASSET_INVENTORY                 = 1
            AND         MSI.ORGANIZATION_ID                 = MMT.ORGANIZATION_ID + 0
            AND         MSI.INVENTORY_ITEM_ID               = MMT.INVENTORY_ITEM_ID + 0
            AND MSI.INVENTORY_ASSET_FLAG    = DECODE(:P_EXP_ITEM, 1, MSI.INVENTORY_ASSET_FLAG, 1)
            AND         MIC.ORGANIZATION_ID                 = MSI.ORGANIZATION_ID
            AND         MIC.INVENTORY_ITEM_ID               = MSI.INVENTORY_ITEM_ID
            AND         MIC.CATEGORY_SET_ID                 = :P_CATEGORY_SET
            AND         MC.CATEGORY_ID                      = MIC.CATEGORY_ID
            AND         MIL.ORGANIZATION_ID                         (+) = MMT.ORGANIZATION_ID
            AND         MIL.INVENTORY_LOCATION_ID          (+) = MMT.LOCATOR_ID
            AND         MPP.ORGANIZATION_ID                         (+) = MIL.ORGANIZATION_ID
            AND         MPP.PROJECT_ID                              (+) = MIL.PROJECT_ID
            AND           MP.ORGANIZATION_ID    =  MMT.ORGANIZATION_ID
            AND         CQL.ORGANIZATION_ID                 = MMT.ORGANIZATION_ID
            AND         CQL.INVENTORY_ITEM_ID           = MMT.INVENTORY_ITEM_ID
            AND         CQL.COST_GROUP_ID                   = NVL(MPP.COSTING_GROUP_ID, MP.DEFAULT_COST_GROUP_ID)
            AND LU.LOOKUP_TYPE          = 'SYS_YES_NO'
            AND LU.LOOKUP_CODE          = DECODE(MSI.INVENTORY_ASSET_FLAG,'Y',1,2)
            AND         &P_ITEM_WHERE
            AND         &P_CAT_WHERE
            and CQL.COST_GROUP_ID = :AC_CG_ID