WIS_WD_OPERATION_MATERIALS

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

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

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