WIS_WD_OP_SUB_MATERIALS

Details

Schema: FUSION
Object owner: WIS
Object type: VIEW

Columns

Name
ROW_ID SUB_MATERIAL_ID WD_OPERATION_MATERIAL_ID SUB_COMPONENT_ID SUB_COMPONENT_SEQUENCE_ID SUBSTITUTE_ITEM_QUANTITY SUBSTITUTE_INVERSE_QUANTITY PRIORITY_RANKING ATTRIBUTE_CATEGORY ATTRIBUTE1 ATTRIBUTE2 ATTRIBUTE3 ATTRIBUTE4 ATTRIBUTE5 ATTRIBUTE6 ATTRIBUTE7 ATTRIBUTE8 ATTRIBUTE9 ATTRIBUTE10 ATTRIBUTE11 ATTRIBUTE12 ATTRIBUTE13 ATTRIBUTE14 ATTRIBUTE15 ATTRIBUTE16 ATTRIBUTE17 ATTRIBUTE18 ATTRIBUTE19 ATTRIBUTE20 ATTRIBUTE21 ATTRIBUTE22 ATTRIBUTE23 ATTRIBUTE24 ATTRIBUTE25 ATTRIBUTE26 ATTRIBUTE27 ATTRIBUTE28 ATTRIBUTE29 ATTRIBUTE30 ATTRIBUTE_NUMBER1 ATTRIBUTE_NUMBER2 ATTRIBUTE_NUMBER3 ATTRIBUTE_NUMBER4 ATTRIBUTE_NUMBER5 ATTRIBUTE_NUMBER6 ATTRIBUTE_NUMBER7 ATTRIBUTE_NUMBER8 ATTRIBUTE_NUMBER9 ATTRIBUTE_NUMBER10 ATTRIBUTE_DATE1 ATTRIBUTE_DATE2 ATTRIBUTE_DATE3 ATTRIBUTE_DATE4 ATTRIBUTE_DATE5 ATTRIBUTE_TIMESTAMP1 ATTRIBUTE_TIMESTAMP2 ATTRIBUTE_TIMESTAMP3 ATTRIBUTE_TIMESTAMP4 ATTRIBUTE_TIMESTAMP5 OPERATION_MATERIAL_ID MATERIAL_SEQ_NUMBER MATERIAL_TYPE ADHOC_FLAG OP_MTL_COMPONENT_SEQUENCE_ID COMPONENT_SEQUENCE_ID_PATH WD_OPERATION_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 FIND_NUMBER PRIMARY_UOM_CODE

Name

ROW_ID

SUB_MATERIAL_ID

WD_OPERATION_MATERIAL_ID

SUB_COMPONENT_ID

SUB_COMPONENT_SEQUENCE_ID

SUBSTITUTE_ITEM_QUANTITY

SUBSTITUTE_INVERSE_QUANTITY

PRIORITY_RANKING

ATTRIBUTE_CATEGORY

ATTRIBUTE1

ATTRIBUTE2

ATTRIBUTE3

ATTRIBUTE4

ATTRIBUTE5

ATTRIBUTE6

ATTRIBUTE7

ATTRIBUTE8

ATTRIBUTE9

ATTRIBUTE10

ATTRIBUTE11

ATTRIBUTE12

ATTRIBUTE13

ATTRIBUTE14

ATTRIBUTE15

ATTRIBUTE16

ATTRIBUTE17

ATTRIBUTE18

ATTRIBUTE19

ATTRIBUTE20

ATTRIBUTE21

ATTRIBUTE22

ATTRIBUTE23

ATTRIBUTE24

ATTRIBUTE25

ATTRIBUTE26

ATTRIBUTE27

ATTRIBUTE28

ATTRIBUTE29

ATTRIBUTE30

ATTRIBUTE_NUMBER1

ATTRIBUTE_NUMBER2

ATTRIBUTE_NUMBER3

ATTRIBUTE_NUMBER4

ATTRIBUTE_NUMBER5

ATTRIBUTE_NUMBER6

ATTRIBUTE_NUMBER7

ATTRIBUTE_NUMBER8

ATTRIBUTE_NUMBER9

ATTRIBUTE_NUMBER10

ATTRIBUTE_DATE1

ATTRIBUTE_DATE2

ATTRIBUTE_DATE3

ATTRIBUTE_DATE4

ATTRIBUTE_DATE5

ATTRIBUTE_TIMESTAMP1

ATTRIBUTE_TIMESTAMP2

ATTRIBUTE_TIMESTAMP3

ATTRIBUTE_TIMESTAMP4

ATTRIBUTE_TIMESTAMP5

OPERATION_MATERIAL_ID

MATERIAL_SEQ_NUMBER

MATERIAL_TYPE

ADHOC_FLAG

OP_MTL_COMPONENT_SEQUENCE_ID

COMPONENT_SEQUENCE_ID_PATH

WD_OPERATION_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

FIND_NUMBER

PRIMARY_UOM_CODE

Query

SQL_Statement
SELECT SUBMTL.ROWID ROW_ID, SUBMTL.WD_OP_SUB_MATERIAL_ID AS SUB_MATERIAL_ID, SUBMTL.WD_OPERATION_MATERIAL_ID, SUBMTL.SUBSTITUTE_COMPONENT_ID AS SUB_COMPONENT_ID, SUBMTL.COMPONENT_SEQUENCE_ID AS SUB_COMPONENT_SEQUENCE_ID, SUBMTL.SUBSTITUTE_ITEM_QUANTITY, SUBMTL.SUBSTITUTE_INVERSE_QUANTITY, SUBCOMP.PLANNING_PRIORITY, SUBCOMP.ATTRIBUTE_CATEGORY, SUBCOMP.ATTRIBUTE1, SUBCOMP.ATTRIBUTE2, SUBCOMP.ATTRIBUTE3, SUBCOMP.ATTRIBUTE4, SUBCOMP.ATTRIBUTE5, SUBCOMP.ATTRIBUTE6, SUBCOMP.ATTRIBUTE7, SUBCOMP.ATTRIBUTE8, SUBCOMP.ATTRIBUTE9, SUBCOMP.ATTRIBUTE10, SUBCOMP.ATTRIBUTE11, SUBCOMP.ATTRIBUTE12, SUBCOMP.ATTRIBUTE13, SUBCOMP.ATTRIBUTE14, SUBCOMP.ATTRIBUTE15, SUBCOMP.ATTRIBUTE16, SUBCOMP.ATTRIBUTE17, SUBCOMP.ATTRIBUTE18, SUBCOMP.ATTRIBUTE19, SUBCOMP.ATTRIBUTE20, SUBCOMP.ATTRIBUTE21, SUBCOMP.ATTRIBUTE22, SUBCOMP.ATTRIBUTE23, SUBCOMP.ATTRIBUTE24, SUBCOMP.ATTRIBUTE25, SUBCOMP.ATTRIBUTE26, SUBCOMP.ATTRIBUTE27, SUBCOMP.ATTRIBUTE28, SUBCOMP.ATTRIBUTE29, SUBCOMP.ATTRIBUTE30, SUBCOMP.ATTRIBUTE_NUMBER1, SUBCOMP.ATTRIBUTE_NUMBER2, SUBCOMP.ATTRIBUTE_NUMBER3, SUBCOMP.ATTRIBUTE_NUMBER4, SUBCOMP.ATTRIBUTE_NUMBER5, SUBCOMP.ATTRIBUTE_NUMBER6, SUBCOMP.ATTRIBUTE_NUMBER7, SUBCOMP.ATTRIBUTE_NUMBER8, SUBCOMP.ATTRIBUTE_NUMBER9, SUBCOMP.ATTRIBUTE_NUMBER10, SUBCOMP.ATTRIBUTE_DATE1, SUBCOMP.ATTRIBUTE_DATE2, SUBCOMP.ATTRIBUTE_DATE3, SUBCOMP.ATTRIBUTE_DATE4, SUBCOMP.ATTRIBUTE_DATE5, SUBCOMP.ATTRIBUTE_TIMESTAMP1, SUBCOMP.ATTRIBUTE_TIMESTAMP2, SUBCOMP.ATTRIBUTE_TIMESTAMP3, SUBCOMP.ATTRIBUTE_TIMESTAMP4, SUBCOMP.ATTRIBUTE_TIMESTAMP5, MTL.WD_OPERATION_MATERIAL_ID AS OPERATION_MATERIAL_ID, MTL.MATERIAL_SEQ_NUMBER, MTL.MATERIAL_TYPE, MTL.ADHOC_FLAG, MTL.COMPONENT_SEQUENCE_ID AS OP_MTL_COMPONENT_SEQUENCE_ID, MTL.COMPONENT_SEQUENCE_ID_PATH, MTL.WD_OPERATION_ID, MTL.ORGANIZATION_ID, MTL.INVENTORY_ITEM_ID, COMP.COMPONENT_YIELD_FACTOR AS YIELD_FACTOR, TO_CHAR(COMP.BASIS_TYPE) AS BASIS_TYPE, COMP.PRIMARY_UOM_CODE AS UOM_CODE, MTL.SUPPLY_TYPE, COMP.COMPONENT_QUANTITY AS MATERIAL_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, MTL.SUPPLY_SUBINVENTORY_NAME, MTL.SUPPLY_LOCATOR_ID, COMP.OPTIONAL, COMP.PLANNING_FACTOR, SUBCOMP.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.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, EGP_SUBSTITUTE_COMPONENTS SUBCOMP, WIS_WD_OP_SUB_MATERIALS SUBMTL, (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 WHERE NVL(WD.SYSTEM_GENERATED_FLAG,'N')='N' AND WD.WORK_DEFINITION_ID = WDO.WORK_DEFINITION_ID AND WDO.WD_OPERATION_ID = MTL.WD_OPERATION_ID AND MTL.COMPONENT_SEQUENCE_ID_PATH IS NOT NULL AND (MTL.COMPONENT_SEQUENCE_ID = COMP.COMPONENT_SEQUENCE_ID AND COMP.PK1_VALUE = ITEM.INVENTORY_ITEM_ID AND COMP.PK2_VALUE = ITEM.ORGANIZATION_ID AND SUBMTL.WD_OPERATION_MATERIAL_ID = MTL.WD_OPERATION_MATERIAL_ID AND SUBMTL.COMPONENT_SEQUENCE_ID = SUBCOMP.COMPONENT_SEQUENCE_ID AND SUBCOMP.SUBSTITUTE_COMPONENT_ID = SUBMTL.SUBSTITUTE_COMPONENT_ID) AND SUBCOMP.ACD_TYPE != 3 UNION ALL SELECT SUBMTL.ROWID ROW_ID, SUBMTL.WD_OP_SUB_MATERIAL_ID AS SUB_MATERIAL_ID, SUBMTL.WD_OPERATION_MATERIAL_ID, SUBMTL.SUBSTITUTE_COMPONENT_ID AS SUB_COMPONENT_ID, SUBMTL.COMPONENT_SEQUENCE_ID AS SUB_COMPONENT_SEQUENCE_ID, SUBMTL.SUBSTITUTE_ITEM_QUANTITY, SUBMTL.SUBSTITUTE_INVERSE_QUANTITY, SUBCOMP.PLANNING_PRIORITY, SUBCOMP.ATTRIBUTE_CATEGORY, SUBCOMP.ATTRIBUTE1, SUBCOMP.ATTRIBUTE2, SUBCOMP.ATTRIBUTE3, SUBCOMP.ATTRIBUTE4, SUBCOMP.ATTRIBUTE5, SUBCOMP.ATTRIBUTE6, SUBCOMP.ATTRIBUTE7, SUBCOMP.ATTRIBUTE8, SUBCOMP.ATTRIBUTE9, SUBCOMP.ATTRIBUTE10, SUBCOMP.ATTRIBUTE11, SUBCOMP.ATTRIBUTE12, SUBCOMP.ATTRIBUTE13, SUBCOMP.ATTRIBUTE14, SUBCOMP.ATTRIBUTE15, SUBCOMP.ATTRIBUTE16, SUBCOMP.ATTRIBUTE17, SUBCOMP.ATTRIBUTE18, SUBCOMP.ATTRIBUTE19, SUBCOMP.ATTRIBUTE20, SUBCOMP.ATTRIBUTE21, SUBCOMP.ATTRIBUTE22, SUBCOMP.ATTRIBUTE23, SUBCOMP.ATTRIBUTE24, SUBCOMP.ATTRIBUTE25, SUBCOMP.ATTRIBUTE26, SUBCOMP.ATTRIBUTE27, SUBCOMP.ATTRIBUTE28, SUBCOMP.ATTRIBUTE29, SUBCOMP.ATTRIBUTE30, SUBCOMP.ATTRIBUTE_NUMBER1, SUBCOMP.ATTRIBUTE_NUMBER2, SUBCOMP.ATTRIBUTE_NUMBER3, SUBCOMP.ATTRIBUTE_NUMBER4, SUBCOMP.ATTRIBUTE_NUMBER5, SUBCOMP.ATTRIBUTE_NUMBER6, SUBCOMP.ATTRIBUTE_NUMBER7, SUBCOMP.ATTRIBUTE_NUMBER8, SUBCOMP.ATTRIBUTE_NUMBER9, SUBCOMP.ATTRIBUTE_NUMBER10, SUBCOMP.ATTRIBUTE_DATE1, SUBCOMP.ATTRIBUTE_DATE2, SUBCOMP.ATTRIBUTE_DATE3, SUBCOMP.ATTRIBUTE_DATE4, SUBCOMP.ATTRIBUTE_DATE5, SUBCOMP.ATTRIBUTE_TIMESTAMP1, SUBCOMP.ATTRIBUTE_TIMESTAMP2, SUBCOMP.ATTRIBUTE_TIMESTAMP3, SUBCOMP.ATTRIBUTE_TIMESTAMP4, SUBCOMP.ATTRIBUTE_TIMESTAMP5, MTL.WD_OPERATION_MATERIAL_ID AS OPERATION_MATERIAL_ID, MTL.MATERIAL_SEQ_NUMBER, MTL.MATERIAL_TYPE, MTL.ADHOC_FLAG, MTL.COMPONENT_SEQUENCE_ID AS OP_MTL_COMPONENT_SEQUENCE_ID, MTL.COMPONENT_SEQUENCE_ID_PATH, MTL.WD_OPERATION_ID, MTL.ORGANIZATION_ID, MTL.INVENTORY_ITEM_ID, NVL(MTL.YIELD_FACTOR, COMP.COMPONENT_YIELD_FACTOR) AS YIELD_FACTOR, TO_CHAR(COMP.BASIS_TYPE) AS BASIS_TYPE, NVL(MTL.UOM_CODE, COMP.PRIMARY_UOM_CODE) AS UOM_CODE, MTL.SUPPLY_TYPE, MTL.MATERIAL_QUANTITY, MTL.INVERSE_MATERIAL_QUANTITY, MTL.SUPPLY_SUBINVENTORY_NAME, MTL.SUPPLY_LOCATOR_ID, COMP.OPTIONAL, COMP.PLANNING_FACTOR, SUBCOMP.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.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, WIS_WD_OP_SUB_MATERIALS SUBMTL, EGP_SYSTEM_ITEMS_VL ITEM, EGP_COMPONENTS_B COMP, EGP_SUBSTITUTE_COMPONENTS SUBCOMP WHERE NVL(WD.SYSTEM_GENERATED_FLAG,'N')='N' AND WD.WORK_DEFINITION_ID = WDO.WORK_DEFINITION_ID AND WDO.WD_OPERATION_ID = MTL.WD_OPERATION_ID AND MTL.COMPONENT_SEQUENCE_ID_PATH IS NULL AND (MTL.COMPONENT_SEQUENCE_ID = COMP.COMPONENT_SEQUENCE_ID AND COMP.PK1_VALUE = ITEM.INVENTORY_ITEM_ID AND COMP.PK2_VALUE = ITEM.ORGANIZATION_ID AND COMP.COMPONENT_SEQUENCE_ID = SUBCOMP.COMPONENT_SEQUENCE_ID AND SUBMTL.WD_OPERATION_MATERIAL_ID = MTL.WD_OPERATION_MATERIAL_ID AND SUBMTL.COMPONENT_SEQUENCE_ID = SUBCOMP.COMPONENT_SEQUENCE_ID AND SUBCOMP.SUBSTITUTE_COMPONENT_ID = SUBMTL.SUBSTITUTE_COMPONENT_ID) UNION ALL SELECT NULL ROW_ID, NULL AS WD_OP_SUB_MATERIAL_ID, NULL AS WD_OPERATION_SUB_MATERIAL_ID, SUBCOMP.SUBSTITUTE_COMPONENT_ID, SUBCOMP.COMPONENT_SEQUENCE_ID, SUBCOMP.SUBSTITUTE_ITEM_QUANTITY, NULL AS SUBSTITUTE_INVERSE_QUANTITY, SUBCOMP.PLANNING_PRIORITY , SUBCOMP.ATTRIBUTE_CATEGORY, SUBCOMP.ATTRIBUTE1, SUBCOMP.ATTRIBUTE2, SUBCOMP.ATTRIBUTE3, SUBCOMP.ATTRIBUTE4, SUBCOMP.ATTRIBUTE5, SUBCOMP.ATTRIBUTE6, SUBCOMP.ATTRIBUTE7, SUBCOMP.ATTRIBUTE8, SUBCOMP.ATTRIBUTE9, SUBCOMP.ATTRIBUTE10, SUBCOMP.ATTRIBUTE11, SUBCOMP.ATTRIBUTE12, SUBCOMP.ATTRIBUTE13, SUBCOMP.ATTRIBUTE14, SUBCOMP.ATTRIBUTE15, SUBCOMP.ATTRIBUTE16, SUBCOMP.ATTRIBUTE17, SUBCOMP.ATTRIBUTE18, SUBCOMP.ATTRIBUTE19, SUBCOMP.ATTRIBUTE20, SUBCOMP.ATTRIBUTE21, SUBCOMP.ATTRIBUTE22, SUBCOMP.ATTRIBUTE23, SUBCOMP.ATTRIBUTE24, SUBCOMP.ATTRIBUTE25, SUBCOMP.ATTRIBUTE26, SUBCOMP.ATTRIBUTE27, SUBCOMP.ATTRIBUTE28, SUBCOMP.ATTRIBUTE29, SUBCOMP.ATTRIBUTE30, SUBCOMP.ATTRIBUTE_NUMBER1, SUBCOMP.ATTRIBUTE_NUMBER2, SUBCOMP.ATTRIBUTE_NUMBER3, SUBCOMP.ATTRIBUTE_NUMBER4, SUBCOMP.ATTRIBUTE_NUMBER5, SUBCOMP.ATTRIBUTE_NUMBER6, SUBCOMP.ATTRIBUTE_NUMBER7, SUBCOMP.ATTRIBUTE_NUMBER8, SUBCOMP.ATTRIBUTE_NUMBER9, SUBCOMP.ATTRIBUTE_NUMBER10, SUBCOMP.ATTRIBUTE_DATE1, SUBCOMP.ATTRIBUTE_DATE2, SUBCOMP.ATTRIBUTE_DATE3, SUBCOMP.ATTRIBUTE_DATE4, SUBCOMP.ATTRIBUTE_DATE5, SUBCOMP.ATTRIBUTE_TIMESTAMP1, SUBCOMP.ATTRIBUTE_TIMESTAMP2, SUBCOMP.ATTRIBUTE_TIMESTAMP3, SUBCOMP.ATTRIBUTE_TIMESTAMP4, SUBCOMP.ATTRIBUTE_TIMESTAMP5, 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(CONCAT(COMP.BILL_SEQUENCE_ID ,CONCAT('-' ,COMP.COMPONENT_SEQUENCE_ID))) AS COMPONENT_SEQUENCE_ID_PATH, WDO.WD_OPERATION_ID, TO_NUMBER(COMP.PK2_VALUE), TO_NUMBER(COMP.PK1_VALUE), COMP.COMPONENT_YIELD_FACTOR, TO_CHAR(COMP.BASIS_TYPE), COMP.PRIMARY_UOM_CODE, TO_CHAR(COMP.WIP_SUPPLY_TYPE), 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, SUBCOMP.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.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_STRUCTURES_B STRUC, EGP_COMPONENTS_B COMP, EGP_SUBSTITUTE_COMPONENTS SUBCOMP, (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 WDO.WORK_DEFINITION_ID = WD.WORK_DEFINITION_ID AND COMP.PK1_VALUE = ITEM.INVENTORY_ITEM_ID AND COMP.PK2_VALUE = ITEM.ORGANIZATION_ID AND WD.BILL_SEQUENCE_ID = STRUC.BILL_SEQUENCE_ID AND COMP.BILL_SEQUENCE_ID = STRUC.COMMON_BILL_SEQUENCE_ID AND COMP.COMPONENT_SEQUENCE_ID = SUBCOMP.COMPONENT_SEQUENCE_ID AND SUBCOMP.ACD_TYPE ! = 3

SQL_Statement

SELECT

SUBMTL.ROWID ROW_ID,

SUBMTL.WD_OP_SUB_MATERIAL_ID AS SUB_MATERIAL_ID,

SUBMTL.WD_OPERATION_MATERIAL_ID,

SUBMTL.SUBSTITUTE_COMPONENT_ID AS SUB_COMPONENT_ID,

SUBMTL.COMPONENT_SEQUENCE_ID AS SUB_COMPONENT_SEQUENCE_ID,

SUBMTL.SUBSTITUTE_ITEM_QUANTITY,

SUBMTL.SUBSTITUTE_INVERSE_QUANTITY,

SUBCOMP.PLANNING_PRIORITY,

SUBCOMP.ATTRIBUTE_CATEGORY,

SUBCOMP.ATTRIBUTE1,

SUBCOMP.ATTRIBUTE2,

SUBCOMP.ATTRIBUTE3,

SUBCOMP.ATTRIBUTE4,

SUBCOMP.ATTRIBUTE5,

SUBCOMP.ATTRIBUTE6,

SUBCOMP.ATTRIBUTE7,

SUBCOMP.ATTRIBUTE8,

SUBCOMP.ATTRIBUTE9,

SUBCOMP.ATTRIBUTE10,

SUBCOMP.ATTRIBUTE11,

SUBCOMP.ATTRIBUTE12,

SUBCOMP.ATTRIBUTE13,

SUBCOMP.ATTRIBUTE14,

SUBCOMP.ATTRIBUTE15,

SUBCOMP.ATTRIBUTE16,

SUBCOMP.ATTRIBUTE17,

SUBCOMP.ATTRIBUTE18,

SUBCOMP.ATTRIBUTE19,

SUBCOMP.ATTRIBUTE20,

SUBCOMP.ATTRIBUTE21,

SUBCOMP.ATTRIBUTE22,

SUBCOMP.ATTRIBUTE23,

SUBCOMP.ATTRIBUTE24,

SUBCOMP.ATTRIBUTE25,

SUBCOMP.ATTRIBUTE26,

SUBCOMP.ATTRIBUTE27,

SUBCOMP.ATTRIBUTE28,

SUBCOMP.ATTRIBUTE29,

SUBCOMP.ATTRIBUTE30,

SUBCOMP.ATTRIBUTE_NUMBER1,

SUBCOMP.ATTRIBUTE_NUMBER2,

SUBCOMP.ATTRIBUTE_NUMBER3,

SUBCOMP.ATTRIBUTE_NUMBER4,

SUBCOMP.ATTRIBUTE_NUMBER5,

SUBCOMP.ATTRIBUTE_NUMBER6,

SUBCOMP.ATTRIBUTE_NUMBER7,

SUBCOMP.ATTRIBUTE_NUMBER8,

SUBCOMP.ATTRIBUTE_NUMBER9,

SUBCOMP.ATTRIBUTE_NUMBER10,

SUBCOMP.ATTRIBUTE_DATE1,

SUBCOMP.ATTRIBUTE_DATE2,

SUBCOMP.ATTRIBUTE_DATE3,

SUBCOMP.ATTRIBUTE_DATE4,

SUBCOMP.ATTRIBUTE_DATE5,

SUBCOMP.ATTRIBUTE_TIMESTAMP1,

SUBCOMP.ATTRIBUTE_TIMESTAMP2,

SUBCOMP.ATTRIBUTE_TIMESTAMP3,

SUBCOMP.ATTRIBUTE_TIMESTAMP4,

SUBCOMP.ATTRIBUTE_TIMESTAMP5,

MTL.WD_OPERATION_MATERIAL_ID AS OPERATION_MATERIAL_ID,

MTL.MATERIAL_SEQ_NUMBER,

MTL.MATERIAL_TYPE,

MTL.ADHOC_FLAG,

MTL.COMPONENT_SEQUENCE_ID AS OP_MTL_COMPONENT_SEQUENCE_ID,

MTL.COMPONENT_SEQUENCE_ID_PATH,

MTL.WD_OPERATION_ID,

MTL.ORGANIZATION_ID,

MTL.INVENTORY_ITEM_ID,

COMP.COMPONENT_YIELD_FACTOR AS YIELD_FACTOR,

TO_CHAR(COMP.BASIS_TYPE) AS BASIS_TYPE,

COMP.PRIMARY_UOM_CODE AS UOM_CODE,

MTL.SUPPLY_TYPE,

COMP.COMPONENT_QUANTITY AS MATERIAL_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,

MTL.SUPPLY_SUBINVENTORY_NAME,

MTL.SUPPLY_LOCATOR_ID,

COMP.OPTIONAL,

COMP.PLANNING_FACTOR,

SUBCOMP.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.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,

EGP_SUBSTITUTE_COMPONENTS SUBCOMP,

WIS_WD_OP_SUB_MATERIALS SUBMTL,

(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

WHERE NVL(WD.SYSTEM_GENERATED_FLAG,'N')='N'

AND WD.WORK_DEFINITION_ID = WDO.WORK_DEFINITION_ID

AND WDO.WD_OPERATION_ID = MTL.WD_OPERATION_ID

AND MTL.COMPONENT_SEQUENCE_ID_PATH IS NOT NULL

AND (MTL.COMPONENT_SEQUENCE_ID = COMP.COMPONENT_SEQUENCE_ID

AND COMP.PK1_VALUE = ITEM.INVENTORY_ITEM_ID

AND COMP.PK2_VALUE = ITEM.ORGANIZATION_ID

AND SUBMTL.WD_OPERATION_MATERIAL_ID = MTL.WD_OPERATION_MATERIAL_ID

AND SUBMTL.COMPONENT_SEQUENCE_ID = SUBCOMP.COMPONENT_SEQUENCE_ID

AND SUBCOMP.SUBSTITUTE_COMPONENT_ID = SUBMTL.SUBSTITUTE_COMPONENT_ID)

AND SUBCOMP.ACD_TYPE != 3

UNION ALL

SELECT

SUBMTL.ROWID ROW_ID,

SUBMTL.WD_OP_SUB_MATERIAL_ID AS SUB_MATERIAL_ID,

SUBMTL.WD_OPERATION_MATERIAL_ID,

SUBMTL.SUBSTITUTE_COMPONENT_ID AS SUB_COMPONENT_ID,

SUBMTL.COMPONENT_SEQUENCE_ID AS SUB_COMPONENT_SEQUENCE_ID,

SUBMTL.SUBSTITUTE_ITEM_QUANTITY,

SUBMTL.SUBSTITUTE_INVERSE_QUANTITY,

SUBCOMP.PLANNING_PRIORITY,

SUBCOMP.ATTRIBUTE_CATEGORY,

SUBCOMP.ATTRIBUTE1,

SUBCOMP.ATTRIBUTE2,

SUBCOMP.ATTRIBUTE3,

SUBCOMP.ATTRIBUTE4,

SUBCOMP.ATTRIBUTE5,

SUBCOMP.ATTRIBUTE6,

SUBCOMP.ATTRIBUTE7,

SUBCOMP.ATTRIBUTE8,

SUBCOMP.ATTRIBUTE9,

SUBCOMP.ATTRIBUTE10,

SUBCOMP.ATTRIBUTE11,

SUBCOMP.ATTRIBUTE12,

SUBCOMP.ATTRIBUTE13,

SUBCOMP.ATTRIBUTE14,

SUBCOMP.ATTRIBUTE15,

SUBCOMP.ATTRIBUTE16,

SUBCOMP.ATTRIBUTE17,

SUBCOMP.ATTRIBUTE18,

SUBCOMP.ATTRIBUTE19,

SUBCOMP.ATTRIBUTE20,

SUBCOMP.ATTRIBUTE21,

SUBCOMP.ATTRIBUTE22,

SUBCOMP.ATTRIBUTE23,

SUBCOMP.ATTRIBUTE24,

SUBCOMP.ATTRIBUTE25,

SUBCOMP.ATTRIBUTE26,

SUBCOMP.ATTRIBUTE27,

SUBCOMP.ATTRIBUTE28,

SUBCOMP.ATTRIBUTE29,

SUBCOMP.ATTRIBUTE30,

SUBCOMP.ATTRIBUTE_NUMBER1,

SUBCOMP.ATTRIBUTE_NUMBER2,

SUBCOMP.ATTRIBUTE_NUMBER3,

SUBCOMP.ATTRIBUTE_NUMBER4,

SUBCOMP.ATTRIBUTE_NUMBER5,

SUBCOMP.ATTRIBUTE_NUMBER6,

SUBCOMP.ATTRIBUTE_NUMBER7,

SUBCOMP.ATTRIBUTE_NUMBER8,

SUBCOMP.ATTRIBUTE_NUMBER9,

SUBCOMP.ATTRIBUTE_NUMBER10,

SUBCOMP.ATTRIBUTE_DATE1,

SUBCOMP.ATTRIBUTE_DATE2,

SUBCOMP.ATTRIBUTE_DATE3,

SUBCOMP.ATTRIBUTE_DATE4,

SUBCOMP.ATTRIBUTE_DATE5,

SUBCOMP.ATTRIBUTE_TIMESTAMP1,

SUBCOMP.ATTRIBUTE_TIMESTAMP2,

SUBCOMP.ATTRIBUTE_TIMESTAMP3,

SUBCOMP.ATTRIBUTE_TIMESTAMP4,

SUBCOMP.ATTRIBUTE_TIMESTAMP5,

MTL.WD_OPERATION_MATERIAL_ID AS OPERATION_MATERIAL_ID,

MTL.MATERIAL_SEQ_NUMBER,

MTL.MATERIAL_TYPE,

MTL.ADHOC_FLAG,

MTL.COMPONENT_SEQUENCE_ID AS OP_MTL_COMPONENT_SEQUENCE_ID,

MTL.COMPONENT_SEQUENCE_ID_PATH,

MTL.WD_OPERATION_ID,

MTL.ORGANIZATION_ID,

MTL.INVENTORY_ITEM_ID,

NVL(MTL.YIELD_FACTOR, COMP.COMPONENT_YIELD_FACTOR) AS YIELD_FACTOR,

TO_CHAR(COMP.BASIS_TYPE) AS BASIS_TYPE,

NVL(MTL.UOM_CODE, COMP.PRIMARY_UOM_CODE) AS UOM_CODE,

MTL.SUPPLY_TYPE,

MTL.MATERIAL_QUANTITY,

MTL.INVERSE_MATERIAL_QUANTITY,

MTL.SUPPLY_SUBINVENTORY_NAME,

MTL.SUPPLY_LOCATOR_ID,

COMP.OPTIONAL,

COMP.PLANNING_FACTOR,

SUBCOMP.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.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,

WIS_WD_OP_SUB_MATERIALS SUBMTL,

EGP_SYSTEM_ITEMS_VL ITEM,

EGP_COMPONENTS_B COMP,

EGP_SUBSTITUTE_COMPONENTS SUBCOMP

WHERE NVL(WD.SYSTEM_GENERATED_FLAG,'N')='N'

AND WD.WORK_DEFINITION_ID = WDO.WORK_DEFINITION_ID

AND WDO.WD_OPERATION_ID = MTL.WD_OPERATION_ID

AND MTL.COMPONENT_SEQUENCE_ID_PATH IS NULL

AND (MTL.COMPONENT_SEQUENCE_ID = COMP.COMPONENT_SEQUENCE_ID

AND COMP.PK1_VALUE = ITEM.INVENTORY_ITEM_ID

AND COMP.PK2_VALUE = ITEM.ORGANIZATION_ID

AND COMP.COMPONENT_SEQUENCE_ID = SUBCOMP.COMPONENT_SEQUENCE_ID

AND SUBMTL.WD_OPERATION_MATERIAL_ID = MTL.WD_OPERATION_MATERIAL_ID

AND SUBMTL.COMPONENT_SEQUENCE_ID = SUBCOMP.COMPONENT_SEQUENCE_ID

AND SUBCOMP.SUBSTITUTE_COMPONENT_ID = SUBMTL.SUBSTITUTE_COMPONENT_ID)

UNION ALL

SELECT

NULL ROW_ID,

NULL AS WD_OP_SUB_MATERIAL_ID,

NULL AS WD_OPERATION_SUB_MATERIAL_ID,

SUBCOMP.SUBSTITUTE_COMPONENT_ID,

SUBCOMP.COMPONENT_SEQUENCE_ID,

SUBCOMP.SUBSTITUTE_ITEM_QUANTITY,

NULL AS SUBSTITUTE_INVERSE_QUANTITY,

SUBCOMP.PLANNING_PRIORITY ,

SUBCOMP.ATTRIBUTE_CATEGORY,

SUBCOMP.ATTRIBUTE1,

SUBCOMP.ATTRIBUTE2,

SUBCOMP.ATTRIBUTE3,

SUBCOMP.ATTRIBUTE4,

SUBCOMP.ATTRIBUTE5,

SUBCOMP.ATTRIBUTE6,

SUBCOMP.ATTRIBUTE7,

SUBCOMP.ATTRIBUTE8,

SUBCOMP.ATTRIBUTE9,

SUBCOMP.ATTRIBUTE10,

SUBCOMP.ATTRIBUTE11,

SUBCOMP.ATTRIBUTE12,

SUBCOMP.ATTRIBUTE13,

SUBCOMP.ATTRIBUTE14,

SUBCOMP.ATTRIBUTE15,

SUBCOMP.ATTRIBUTE16,

SUBCOMP.ATTRIBUTE17,

SUBCOMP.ATTRIBUTE18,

SUBCOMP.ATTRIBUTE19,

SUBCOMP.ATTRIBUTE20,

SUBCOMP.ATTRIBUTE21,

SUBCOMP.ATTRIBUTE22,

SUBCOMP.ATTRIBUTE23,

SUBCOMP.ATTRIBUTE24,

SUBCOMP.ATTRIBUTE25,

SUBCOMP.ATTRIBUTE26,

SUBCOMP.ATTRIBUTE27,

SUBCOMP.ATTRIBUTE28,

SUBCOMP.ATTRIBUTE29,

SUBCOMP.ATTRIBUTE30,

SUBCOMP.ATTRIBUTE_NUMBER1,

SUBCOMP.ATTRIBUTE_NUMBER2,

SUBCOMP.ATTRIBUTE_NUMBER3,

SUBCOMP.ATTRIBUTE_NUMBER4,

SUBCOMP.ATTRIBUTE_NUMBER5,

SUBCOMP.ATTRIBUTE_NUMBER6,

SUBCOMP.ATTRIBUTE_NUMBER7,

SUBCOMP.ATTRIBUTE_NUMBER8,

SUBCOMP.ATTRIBUTE_NUMBER9,

SUBCOMP.ATTRIBUTE_NUMBER10,

SUBCOMP.ATTRIBUTE_DATE1,

SUBCOMP.ATTRIBUTE_DATE2,

SUBCOMP.ATTRIBUTE_DATE3,

SUBCOMP.ATTRIBUTE_DATE4,

SUBCOMP.ATTRIBUTE_DATE5,

SUBCOMP.ATTRIBUTE_TIMESTAMP1,

SUBCOMP.ATTRIBUTE_TIMESTAMP2,

SUBCOMP.ATTRIBUTE_TIMESTAMP3,

SUBCOMP.ATTRIBUTE_TIMESTAMP4,

SUBCOMP.ATTRIBUTE_TIMESTAMP5,

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(CONCAT(COMP.BILL_SEQUENCE_ID ,CONCAT('-' ,COMP.COMPONENT_SEQUENCE_ID))) AS COMPONENT_SEQUENCE_ID_PATH,

WDO.WD_OPERATION_ID,

TO_NUMBER(COMP.PK2_VALUE),

TO_NUMBER(COMP.PK1_VALUE),

COMP.COMPONENT_YIELD_FACTOR,

TO_CHAR(COMP.BASIS_TYPE),

COMP.PRIMARY_UOM_CODE,

TO_CHAR(COMP.WIP_SUPPLY_TYPE),

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,

SUBCOMP.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.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_STRUCTURES_B STRUC,

EGP_COMPONENTS_B COMP,

EGP_SUBSTITUTE_COMPONENTS SUBCOMP,

(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 WDO.WORK_DEFINITION_ID = WD.WORK_DEFINITION_ID

AND COMP.PK1_VALUE = ITEM.INVENTORY_ITEM_ID

AND COMP.PK2_VALUE = ITEM.ORGANIZATION_ID

AND WD.BILL_SEQUENCE_ID = STRUC.BILL_SEQUENCE_ID

AND COMP.BILL_SEQUENCE_ID = STRUC.COMMON_BILL_SEQUENCE_ID

AND COMP.COMPONENT_SEQUENCE_ID = SUBCOMP.COMPONENT_SEQUENCE_ID

AND SUBCOMP.ACD_TYPE ! = 3