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
Description: Inventory Subledger Report
Application: Bills of Material
Source: Inventory Subledger Report (XML)
Short Name: CSTRISLR_XML
DB package: BOM_CSTRISLR_XMLP_PKG
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 |