VIEW: APPS.WIP_SUB_OPERATION_RESOURCES_V

Source

View Text - Preformatted

SELECT WOR.ROWID ROW_ID , WOR.WIP_ENTITY_ID , WOR.OPERATION_SEQ_NUM , WOR.RESOURCE_SEQ_NUM , BR.RESOURCE_CODE , BR.PURCHASE_ITEM_ID , WOR.ORGANIZATION_ID , WOR.REPETITIVE_SCHEDULE_ID , WOR.LAST_UPDATE_DATE , WOR.LAST_UPDATED_BY , WOR.CREATION_DATE , WOR.CREATED_BY , WOR.LAST_UPDATE_LOGIN , WOR.REQUEST_ID , WOR.PROGRAM_APPLICATION_ID , WOR.PROGRAM_ID , WOR.PROGRAM_UPDATE_DATE , WOR.RESOURCE_ID , WOR.UOM_CODE , MUC.UOM_CLASS , WOR.BASIS_TYPE , ROUND(WOR.USAGE_RATE_OR_AMOUNT, 6) , DECODE(WOR.USAGE_RATE_OR_AMOUNT, 0, 0, ROUND(1/WOR.USAGE_RATE_OR_AMOUNT,6)) "INVERSE" , WOR.ACTIVITY_ID , CA.ACTIVITY , WOR.SCHEDULED_FLAG , WOR.ASSIGNED_UNITS , WOR.MAXIMUM_ASSIGNED_UNITS , DECODE(ROUND(WOR.USAGE_RATE_OR_AMOUNT * DECODE(WOR.BASIS_TYPE,1, DECODE(WOR.REPETITIVE_SCHEDULE_ID, NULL, WDJ.START_QUANTITY, WRS.DAILY_PRODUCTION_RATE * WRS.PROCESSING_WORK_DAYS), 1) ,6), 0, NULL, ROUND(WOR.USAGE_RATE_OR_AMOUNT * DECODE(WOR.BASIS_TYPE,1, DECODE(WOR.REPETITIVE_SCHEDULE_ID, NULL, WDJ.START_QUANTITY, WRS.DAILY_PRODUCTION_RATE * WRS.PROCESSING_WORK_DAYS), 1),6)) "SCHEDULED_UNITS" , WOR.AUTOCHARGE_TYPE , MFL.MEANING "AUTOCHARGE_CODE" , WOR.STANDARD_RATE_FLAG , DECODE(WOR.APPLIED_RESOURCE_UNITS,0,NULL,WOR.APPLIED_RESOURCE_UNITS) , DECODE(ROUND(WOR.USAGE_RATE_OR_AMOUNT * DECODE(WOR.BASIS_TYPE,1, DECODE(WOR.REPETITIVE_SCHEDULE_ID, NULL, WDJ.START_QUANTITY, WRS.DAILY_PRODUCTION_RATE * WRS.PROCESSING_WORK_DAYS), 1) - WOR.APPLIED_RESOURCE_UNITS, 6), 0, NULL, ROUND(WOR.USAGE_RATE_OR_AMOUNT * DECODE(WOR.BASIS_TYPE,1, DECODE(WOR.REPETITIVE_SCHEDULE_ID, NULL, WDJ.START_QUANTITY, WRS.DAILY_PRODUCTION_RATE * WRS.PROCESSING_WORK_DAYS), 1) - WOR.APPLIED_RESOURCE_UNITS,6)) "QUANTITY_OPEN" , WOR.APPLIED_RESOURCE_VALUE , WOR.START_DATE , WOR.COMPLETION_DATE , WOR.ATTRIBUTE_CATEGORY , WOR.ATTRIBUTE1 , WOR.ATTRIBUTE2 , WOR.ATTRIBUTE3 , WOR.ATTRIBUTE4 , WOR.ATTRIBUTE5 , WOR.ATTRIBUTE6 , WOR.ATTRIBUTE7 , WOR.ATTRIBUTE8 , WOR.ATTRIBUTE9 , WOR.ATTRIBUTE10 , WOR.ATTRIBUTE11 , WOR.ATTRIBUTE12 , WOR.ATTRIBUTE13 , WOR.ATTRIBUTE14 , WOR.ATTRIBUTE15 , WOR.DEPARTMENT_ID , WOR.PHANTOM_FLAG , BD.DEPARTMENT_CODE , WOR.PHANTOM_OP_SEQ_NUM , WOR.PHANTOM_ITEM_ID , BR.DESCRIPTION , BR.RESOURCE_TYPE ,WOR.SCHEDULE_SEQ_NUM ,WOR.SUBSTITUTE_GROUP_NUM ,WOR.REPLACEMENT_GROUP_NUM ,WOR.PRINCIPLE_FLAG ,WOR.SETUP_ID FROM MFG_LOOKUPS MFL , MTL_UOM_CONVERSIONS MUC , CST_ACTIVITIES CA , BOM_RESOURCES BR , WIP_DISCRETE_JOBS WDJ , WIP_REPETITIVE_SCHEDULES WRS , WIP_SUB_OPERATION_RESOURCES WOR , BOM_DEPARTMENTS BD WHERE MFL.LOOKUP_TYPE = 'BOM_AUTOCHARGE_TYPE' AND MFL.LOOKUP_CODE = WOR.AUTOCHARGE_TYPE AND CA.ACTIVITY_ID(+) = WOR.ACTIVITY_ID AND BR.RESOURCE_ID = WOR.RESOURCE_ID AND MUC.UOM_CODE = WOR.UOM_CODE AND MUC.INVENTORY_ITEM_ID = 0 AND WDJ.ORGANIZATION_ID(+) = WOR.ORGANIZATION_ID AND WDJ.WIP_ENTITY_ID(+) = WOR.WIP_ENTITY_ID AND WRS.ORGANIZATION_ID(+) = WOR.ORGANIZATION_ID AND WRS.WIP_ENTITY_ID(+) = WOR.WIP_ENTITY_ID AND WRS.REPETITIVE_SCHEDULE_ID(+) = WOR.REPETITIVE_SCHEDULE_ID AND BD.DEPARTMENT_ID(+) = WOR.DEPARTMENT_ID

View Text - HTML Formatted

SELECT WOR.ROWID ROW_ID
, WOR.WIP_ENTITY_ID
, WOR.OPERATION_SEQ_NUM
, WOR.RESOURCE_SEQ_NUM
, BR.RESOURCE_CODE
, BR.PURCHASE_ITEM_ID
, WOR.ORGANIZATION_ID
, WOR.REPETITIVE_SCHEDULE_ID
, WOR.LAST_UPDATE_DATE
, WOR.LAST_UPDATED_BY
, WOR.CREATION_DATE
, WOR.CREATED_BY
, WOR.LAST_UPDATE_LOGIN
, WOR.REQUEST_ID
, WOR.PROGRAM_APPLICATION_ID
, WOR.PROGRAM_ID
, WOR.PROGRAM_UPDATE_DATE
, WOR.RESOURCE_ID
, WOR.UOM_CODE
, MUC.UOM_CLASS
, WOR.BASIS_TYPE
, ROUND(WOR.USAGE_RATE_OR_AMOUNT
, 6)
, DECODE(WOR.USAGE_RATE_OR_AMOUNT
, 0
, 0
, ROUND(1/WOR.USAGE_RATE_OR_AMOUNT
, 6)) "INVERSE"
, WOR.ACTIVITY_ID
, CA.ACTIVITY
, WOR.SCHEDULED_FLAG
, WOR.ASSIGNED_UNITS
, WOR.MAXIMUM_ASSIGNED_UNITS
, DECODE(ROUND(WOR.USAGE_RATE_OR_AMOUNT * DECODE(WOR.BASIS_TYPE
, 1
, DECODE(WOR.REPETITIVE_SCHEDULE_ID
, NULL
, WDJ.START_QUANTITY
, WRS.DAILY_PRODUCTION_RATE * WRS.PROCESSING_WORK_DAYS)
, 1)
, 6)
, 0
, NULL
, ROUND(WOR.USAGE_RATE_OR_AMOUNT * DECODE(WOR.BASIS_TYPE
, 1
, DECODE(WOR.REPETITIVE_SCHEDULE_ID
, NULL
, WDJ.START_QUANTITY
, WRS.DAILY_PRODUCTION_RATE * WRS.PROCESSING_WORK_DAYS)
, 1)
, 6)) "SCHEDULED_UNITS"
, WOR.AUTOCHARGE_TYPE
, MFL.MEANING "AUTOCHARGE_CODE"
, WOR.STANDARD_RATE_FLAG
, DECODE(WOR.APPLIED_RESOURCE_UNITS
, 0
, NULL
, WOR.APPLIED_RESOURCE_UNITS)
, DECODE(ROUND(WOR.USAGE_RATE_OR_AMOUNT * DECODE(WOR.BASIS_TYPE
, 1
, DECODE(WOR.REPETITIVE_SCHEDULE_ID
, NULL
, WDJ.START_QUANTITY
, WRS.DAILY_PRODUCTION_RATE * WRS.PROCESSING_WORK_DAYS)
, 1) - WOR.APPLIED_RESOURCE_UNITS
, 6)
, 0
, NULL
, ROUND(WOR.USAGE_RATE_OR_AMOUNT * DECODE(WOR.BASIS_TYPE
, 1
, DECODE(WOR.REPETITIVE_SCHEDULE_ID
, NULL
, WDJ.START_QUANTITY
, WRS.DAILY_PRODUCTION_RATE * WRS.PROCESSING_WORK_DAYS)
, 1) - WOR.APPLIED_RESOURCE_UNITS
, 6)) "QUANTITY_OPEN"
, WOR.APPLIED_RESOURCE_VALUE
, WOR.START_DATE
, WOR.COMPLETION_DATE
, WOR.ATTRIBUTE_CATEGORY
, WOR.ATTRIBUTE1
, WOR.ATTRIBUTE2
, WOR.ATTRIBUTE3
, WOR.ATTRIBUTE4
, WOR.ATTRIBUTE5
, WOR.ATTRIBUTE6
, WOR.ATTRIBUTE7
, WOR.ATTRIBUTE8
, WOR.ATTRIBUTE9
, WOR.ATTRIBUTE10
, WOR.ATTRIBUTE11
, WOR.ATTRIBUTE12
, WOR.ATTRIBUTE13
, WOR.ATTRIBUTE14
, WOR.ATTRIBUTE15
, WOR.DEPARTMENT_ID
, WOR.PHANTOM_FLAG
, BD.DEPARTMENT_CODE
, WOR.PHANTOM_OP_SEQ_NUM
, WOR.PHANTOM_ITEM_ID
, BR.DESCRIPTION
, BR.RESOURCE_TYPE
, WOR.SCHEDULE_SEQ_NUM
, WOR.SUBSTITUTE_GROUP_NUM
, WOR.REPLACEMENT_GROUP_NUM
, WOR.PRINCIPLE_FLAG
, WOR.SETUP_ID
FROM MFG_LOOKUPS MFL
, MTL_UOM_CONVERSIONS MUC
, CST_ACTIVITIES CA
, BOM_RESOURCES BR
, WIP_DISCRETE_JOBS WDJ
, WIP_REPETITIVE_SCHEDULES WRS
, WIP_SUB_OPERATION_RESOURCES WOR
, BOM_DEPARTMENTS BD
WHERE MFL.LOOKUP_TYPE = 'BOM_AUTOCHARGE_TYPE'
AND MFL.LOOKUP_CODE = WOR.AUTOCHARGE_TYPE
AND CA.ACTIVITY_ID(+) = WOR.ACTIVITY_ID
AND BR.RESOURCE_ID = WOR.RESOURCE_ID
AND MUC.UOM_CODE = WOR.UOM_CODE
AND MUC.INVENTORY_ITEM_ID = 0
AND WDJ.ORGANIZATION_ID(+) = WOR.ORGANIZATION_ID
AND WDJ.WIP_ENTITY_ID(+) = WOR.WIP_ENTITY_ID
AND WRS.ORGANIZATION_ID(+) = WOR.ORGANIZATION_ID
AND WRS.WIP_ENTITY_ID(+) = WOR.WIP_ENTITY_ID
AND WRS.REPETITIVE_SCHEDULE_ID(+) = WOR.REPETITIVE_SCHEDULE_ID
AND BD.DEPARTMENT_ID(+) = WOR.DEPARTMENT_ID