WIS_WD_OPERATION_MATERIALS_V
Details
-
Schema: FUSION
-
Object owner: WIS
-
Object type: VIEW
Columns
Name |
---|
ROW_ID WD_OPERATION_MATERIAL_ID MATERIAL_SEQ_NUMBER MATERIAL_TYPE ADHOC_FLAG COMPONENT_SEQUENCE_ID COMPONENT_SEQUENCE_ID_PATH WD_OPERATION_ID WORK_DEFINITION_ID ORGANIZATION_ID INVENTORY_ITEM_ID YIELD_FACTOR BASIS_TYPE UOM_CODE SUPPLY_TYPE MATERIAL_QUANTITY INVERSE_MATERIAL_QUANTITY SUPPLY_SUBINVENTORY_NAME SUPPLY_LOCATOR_ID OPTIONAL PLANNING_FACTOR ENFORCE_INT_REQUIREMENTS CHECK_ATP EFFECTIVITY_DATE DISABLE_DATE COMPONENT_QUANTITY ITEM_NUM QUANTITY_RELATED SO_BASIS MUTUALLY_EXCLUSIVE_OPTIONS LOW_QUANTITY HIGH_QUANTITY BOM_ITEM_TYPE OPTIONAL_ON_MODEL ACD_TYPE FIND_NUMBER PRIMARY_UOM_CODE |
Query
SQL_Statement |
---|
SELECT MTL.ROWID ROW_ID, MTL.WD_OPERATION_MATERIAL_ID, MTL.MATERIAL_SEQ_NUMBER, MTL.MATERIAL_TYPE, MTL.ADHOC_FLAG, MTL.COMPONENT_SEQUENCE_ID, MTL.COMPONENT_SEQUENCE_ID_PATH, MTL.WD_OPERATION_ID, WD.WORK_DEFINITION_ID, MTL.ORGANIZATION_ID, MTL.INVENTORY_ITEM_ID, NVL(MTL.YIELD_FACTOR, COMP.COMPONENT_YIELD_FACTOR) YIELD_FACTOR, TO_CHAR(COMP.BASIS_TYPE) as BASIS_TYPE, NVL(MTL.UOM_CODE, COMP.PRIMARY_UOM_CODE) UOM_CODE, MTL.SUPPLY_TYPE, CASE WHEN MTL.COMPONENT_SEQUENCE_ID_PATH IS NULL THEN MTL.MATERIAL_QUANTITY ELSE COMP.COMPONENT_QUANTITY END MATERIAL_QUANTITY, CASE WHEN MTL.COMPONENT_SEQUENCE_ID_PATH IS NULL THEN MTL.INVERSE_MATERIAL_QUANTITY ELSE decode(COMP.COMPONENT_QUANTITY,0,0,wie_int_utils_pvt.round_precision(1/COMP.COMPONENT_QUANTITY)) END INVERSE_MATERIAL_QUANTITY, MTL.SUPPLY_SUBINVENTORY_NAME, MTL.SUPPLY_LOCATOR_ID, COMP.OPTIONAL, COMP.PLANNING_FACTOR, COMP.ENFORCE_INT_REQUIREMENTS, COMP.CHECK_ATP, COMP.EFFECTIVITY_DATE, COMP.DISABLE_DATE, COMP.COMPONENT_QUANTITY, COMP.ITEM_NUM, COMP.QUANTITY_RELATED, COMP.SO_BASIS, COMP.MUTUALLY_EXCLUSIVE_OPTIONS, COMP.LOW_QUANTITY, COMP.HIGH_QUANTITY, COMP.BOM_ITEM_TYPE, COMP.OPTIONAL_ON_MODEL, COMP.ACD_TYPE, COMP.OPERATION_SEQ_NUM AS FIND_NUMBER, ITEM.PRIMARY_UOM_CODE FROM WIS_WORK_DEFINITIONS WD, WIS_WD_OPERATIONS_B WDO, WIS_WD_OPERATION_MATERIALS MTL, EGP_SYSTEM_ITEMS_VL ITEM, EGP_COMPONENTS_B COMP WHERE NVL(WD.SYSTEM_GENERATED_FLAG,'N')='N' AND MTL.ADHOC_FLAG = 'N' AND WD.WORK_DEFINITION_ID = WDO.WORK_DEFINITION_ID AND WDO.WD_OPERATION_ID = MTL.WD_OPERATION_ID AND WD.ORGANIZATION_ID = MTL.ORGANIZATION_ID AND MTL.COMPONENT_SEQUENCE_ID = COMP.COMPONENT_SEQUENCE_ID AND MTL.INVENTORY_ITEM_ID = COMP.PK1_VALUE AND COMP.PK1_VALUE = ITEM.INVENTORY_ITEM_ID AND COMP.PK2_VALUE = ITEM.ORGANIZATION_ID AND COMP.IMPLEMENTATION_DATE IS NOT NULL UNION ALL SELECT MTL.ROWID ROW_ID, MTL.WD_OPERATION_MATERIAL_ID, MTL.MATERIAL_SEQ_NUMBER, MTL.MATERIAL_TYPE, MTL.ADHOC_FLAG, MTL.COMPONENT_SEQUENCE_ID, MTL.COMPONENT_SEQUENCE_ID_PATH, MTL.WD_OPERATION_ID, WD.WORK_DEFINITION_ID, MTL.ORGANIZATION_ID, MTL.INVENTORY_ITEM_ID, MTL.YIELD_FACTOR AS YIELD_FACTOR, MTL.BASIS_TYPE AS BASIS_TYPE, MTL.UOM_CODE AS UOM_CODE, MTL.SUPPLY_TYPE, MTL.MATERIAL_QUANTITY, MTL.INVERSE_MATERIAL_QUANTITY, MTL.SUPPLY_SUBINVENTORY_NAME, MTL.SUPPLY_LOCATOR_ID, NULL AS OPTIONAL, NULL AS PLANNING_FACTOR, NULL AS ENFORCE_INT_REQUIREMENTS, NULL AS CHECK_ATP, NULL AS EFFECTIVITY_DATE, NULL AS DISABLE_DATE, NULL AS COMPONENT_QUANTITY, NULL AS ITEM_NUM, NULL AS QUANTITY_RELATED, NULL AS SO_BASIS, NULL AS MUTUALLY_EXCLUSIVE_OPTIONS, NULL AS LOW_QUANTITY, NULL AS HIGH_QUANTITY, NULL AS BOM_ITEM_TYPE, NULL AS OPTIONAL_ON_MODEL, NULL AS ACD_TYPE, NULL AS FIND_NUMBER, ITEM.PRIMARY_UOM_CODE FROM WIS_WORK_DEFINITIONS WD, WIS_WD_OPERATIONS_B WDO, WIS_WD_OPERATION_MATERIALS MTL, EGP_SYSTEM_ITEMS_VL ITEM WHERE NVL(WD.SYSTEM_GENERATED_FLAG,'N')='N' AND MTL.ADHOC_FLAG = 'Y' AND WD.WORK_DEFINITION_ID = WDO.WORK_DEFINITION_ID AND WDO.WD_OPERATION_ID = MTL.WD_OPERATION_ID AND WD.ORGANIZATION_ID = MTL.ORGANIZATION_ID AND MTL.COMPONENT_SEQUENCE_ID IS NULL AND MTL.INVENTORY_ITEM_ID = ITEM.INVENTORY_ITEM_ID AND MTL.ORGANIZATION_ID = ITEM.ORGANIZATION_ID UNION ALL SELECT NULL ROW_ID, NULL AS WD_OPERATION_MATERIAL_ID, COMP.ITEM_NUM AS MATERIAL_SEQ_NUMBER, MATERIAL_TYPE_LOOKUP.LOOKUP_CODE AS MATERIAL_TYPE, 'N' AS ADHOC_FLAG, COMP.COMPONENT_SEQUENCE_ID, TO_CLOB(COMP.BILL_SEQUENCE_ID||'-'||COMP.COMPONENT_SEQUENCE_ID) AS COMPONENT_SEQUENCE_ID_PATH, WDO.WD_OPERATION_ID, WD.WORK_DEFINITION_ID, WD.organization_id, ITEM.inventory_item_id, COMP.COMPONENT_YIELD_FACTOR, TO_CHAR(COMP.BASIS_TYPE), COMP.PRIMARY_UOM_CODE, TO_CHAR(COALESCE(COMP.WIP_SUPPLY_TYPE,ITEM.WIP_SUPPLY_TYPE,1)), COMP.COMPONENT_QUANTITY, TO_NUMBER(DECODE(COMP.COMPONENT_QUANTITY,0,0,(DECODE(INSTR(TO_CHAR(ROUND(1/COMP.COMPONENT_QUANTITY,PRECISION_VAL.PROFILE_OPTION_VALUE)), '.'),1,'0' ||TO_CHAR(ROUND(1/COMP.COMPONENT_QUANTITY,PRECISION_VAL.PROFILE_OPTION_VALUE)),TO_CHAR(ROUND(1/COMP.COMPONENT_QUANTITY,PRECISION_VAL.PROFILE_OPTION_VALUE)))))) AS INVERSE_MATERIAL_QUANTITY, COMP.SUPPLY_SUBINVENTORY AS SUPPLY_SUBINVENTORY_NAME, COMP.SUPPLY_LOCATOR_ID, COMP.OPTIONAL, COMP.PLANNING_FACTOR, COMP.ENFORCE_INT_REQUIREMENTS, COMP.CHECK_ATP, COMP.EFFECTIVITY_DATE, COMP.DISABLE_DATE, COMP.COMPONENT_QUANTITY, COMP.ITEM_NUM, COMP.QUANTITY_RELATED, COMP.SO_BASIS, COMP.MUTUALLY_EXCLUSIVE_OPTIONS, COMP.LOW_QUANTITY, COMP.HIGH_QUANTITY, COMP.BOM_ITEM_TYPE, COMP.OPTIONAL_ON_MODEL, COMP.ACD_TYPE, COMP.OPERATION_SEQ_NUM AS FIND_NUMBER, ITEM.PRIMARY_UOM_CODE FROM WIS_WORK_DEFINITIONS WD, WIS_WD_OPERATIONS_B WDO, EGP_SYSTEM_ITEMS_VL ITEM, EGP_COMPONENTS_B COMP, EGP_STRUCTURES_B STRT, (SELECT NVL(PROFILE_OPTION_VALUE,5) PROFILE_OPTION_VALUE FROM FND_PROFILE_OPTION_VALUES VAL, FND_PROFILE_OPTIONS_VL PF WHERE PF.PROFILE_OPTION_ID = VAL.PROFILE_OPTION_ID AND PF.PROFILE_OPTION_NAME = 'INV_QUANTITY_DECIMAL_PRECISION' AND PF.APPLICATION_ID = VAL.APPLICATION_ID AND VAL.LEVEL_NAME = 'SITE' AND VAL.LEVEL_VALUE = 'SITE') PRECISION_VAL, (SELECT LOOKUP_CODE FROM FND_LOOKUPS WHERE LOOKUP_TYPE= 'ORA_WIS_WD_INPUT_MATERIAL') MATERIAL_TYPE_LOOKUP WHERE NVL(WD.SYSTEM_GENERATED_FLAG,'N')='Y' AND WD.WORK_DEFINITION_ID = WDO.WORK_DEFINITION_ID AND STRT.COMMON_BILL_SEQUENCE_ID = COMP.BILL_SEQUENCE_ID AND WD.BILL_SEQUENCE_ID = STRT.BILL_SEQUENCE_ID AND COMP.PK1_VALUE = ITEM.INVENTORY_ITEM_ID AND COMP.PK2_VALUE = ITEM.ORGANIZATION_ID AND COMP.IMPLEMENTATION_DATE IS NOT NULL |