CST Inventory Subledger

Description
Categories: BI Publisher
Imported from BI Publisher
Description: Inventory Subledger Report
Application: Bills of Material
Source: Inventory Subledger Report (XML)
Short Name: CSTRISLR_XML
DB package: BOM_CSTRISLR_XMLP_PKG
Run CST Inventory Subledger and other Oracle EBS reports with Blitz Report™ on our demo environment
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
Parameter Name SQL text Validation
Item From
 
Char
Item To
 
Char
Category Set
 
LOV Oracle
Category From
 
Char
Category To
 
Char
Subinventory From
 
LOV Oracle
Subinventory To
 
LOV Oracle
Currency
 
LOV Oracle
Exchange Rate
 
LOV Oracle
Quantities by Revision
 
LOV Oracle
Negative Quantities Only
 
LOV Oracle
Include Expense Items
 
LOV Oracle
Include Zero Quantities
 
LOV Oracle