CST Inventory Subledger

Description
Categories: Financials, Manufacturing, Oracle Standard
Application: Bills of Material
Source: Inventory Subledger Report (XML)
Short Name: CSTRISLR_XML
DB package: BOM_CSTRISLR_XMLP_PKG
SELECT
                CG.COST_GROUP               AC_CG_NAME
            ,   CG.DESCRIPTION              AC_CG_DESC
            ,           GCCV1.PADDED_CONCATENATED_SEGMENTS  AC_MATL_PSEG
            ,           GCCV2.PADDED_CONCATENATED_SEGMENTS  AC_MOVH_PSEG
            ,           GCCV3.PADDED_CONCATENATED_SEGMENTS  AC_RES_PSEG
            ,           GCCV4.PADDED_CONCATENATED_SEGMENTS  AC_OSP_PSEG
            ,           GCCV5.PADDED_CONCATENATED_SEGMENTS  AC_OVHD_PSEG
            ,           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_COST_GROUPS     CG
            ,   CST_AVG_DIST_ACCTS_V        CAV
            ,   GL_CODE_COMBINATIONS_KFV    GCCV1
            ,   GL_CODE_COMBINATIONS_KFV    GCCV2
            ,   GL_CODE_COMBINATIONS_KFV    GCCV3
            ,   GL_CODE_COMBINATIONS_KFV    GCCV4
            ,   GL_CODE_COMBINATIONS_KFV    GCCV5
            ,           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
                CAV.ORGANIZATION_ID             =   :P_ORG_ID
            AND CG.COST_GROUP_ID                =   CAV.COST_GROUP_ID
            AND GCCV1.CODE_COMBINATION_ID       =   CAV.MATERIAL_ACCOUNT
            AND GCCV2.CODE_COMBINATION_ID       =   CAV.MATERIAL_OVERHEAD_ACCOUNT
            AND GCCV3.CODE_COMBINATION_ID       =   CAV.RESOURCE_ACCOUNT
            AND GCCV4.CODE_COMBINATION_ID       =   CAV.OUTSIDE_PROCESSING_ACCOUNT
            AND GCCV5.CODE_COMBINATION_ID       =   CAV.OVERHEAD_ACCOUNT
            AND 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 = CG.COST_GROUP_ID
            UNION ALL
            SELECT
                CG.COST_GROUP               AC_CG_NAME
            ,   CG.DESCRIPTION              AC_CG_DESC
            ,           GCCV1.PADDED_CONCATENATED_SEGMENTS  AC_MATL_PSEG
            ,           GCCV2.PADDED_CONCATENATED_SEGMENTS  AC_MOVH_PSEG
            ,           GCCV3.PADDED_CONCATENATED_SEGMENTS  AC_RES_PSEG
            ,           GCCV4.PADDED_CONCATENATED_SEGMENTS  AC_OSP_PSEG
            ,           GCCV5.PADDED_CONCATENATED_SEGMENTS  AC_OVHD_PSEG
            ,       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_COST_GROUPS     CG
            ,   CST_AVG_DIST_ACCTS_V        CAV
            ,   GL_CODE_COMBINATIONS_KFV    GCCV1
            ,   GL_CODE_COMBINATIONS_KFV    GCCV2
            ,   GL_CODE_COMBINATIONS_KFV    GCCV3
            ,   GL_CODE_COMBINATIONS_KFV    GCCV4
            ,   GL_CODE_COMBINATIONS_KFV    GCCV5
            ,           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
                CAV.ORGANIZATION_ID             =   :P_ORG_ID
            AND CG.COST_GROUP_ID                =   CAV.COST_GROUP_ID
            AND GCCV1.CODE_COMBINATION_ID       =   CAV.MATERIAL_ACCOUNT
            AND GCCV2.CODE_COMBINATION_ID       =   CAV.MATERIAL_OVERHEAD_ACCOUNT
            AND GCCV3.CODE_COMBINATION_ID       =   CAV.RESOURCE_ACCOUNT
            AND GCCV4.CODE_COMBINATION_ID       =   CAV.OUTSIDE_PROCESSING_ACCOUNT
            AND GCCV5.CODE_COMBINATION_ID       =   CAV.OVERHEAD_ACCOUNT
            AND 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 = CG.COST_GROUP_ID
Parameter Name SQL text Validation
View Cost Privilege
 
Number
CST_SRS_RATE_TYPE
 
Number
Quantity Precision
 
Number
Organization Id
 
Number
CST_SRS_INVERSE_RATE
 
Category Structure
 
Number
Include Zero Quantities
 
LOV Oracle
Include Expense Items
 
LOV Oracle
Negative Quantities Only
 
LOV Oracle
Quantities by Revision
 
LOV Oracle
Exchange Rate
 
LOV Oracle
Currency
 
LOV Oracle
Subinventory To
 
LOV Oracle
Subinventory From
 
LOV Oracle
Category To
 
Category From
 
Category Set
 
LOV Oracle
Item To
 
Item From