View: WIP_OPERATION_RESOURCES_INQ_V

Product:	WIP - Work in Process
Description:	Additional data for WIP_OPERATION_RESOURCES
Implementation/DBA Data:	APPS.WIP_OPERATION_RESOURCES_INQ_V

View Text

SELECT /*+ ORDERED */ WOR.ROWID ROW_ID
, WOR.ORGANIZATION_ID
, WOR.WIP_ENTITY_ID
, WOR.REPETITIVE_SCHEDULE_ID
, DECODE(WE.ENTITY_TYPE
, 2
, NULL
, WE.WIP_ENTITY_NAME) "JOB_NAME"
, DECODE(WE.ENTITY_TYPE
, 2
, NULL
, WE.DESCRIPTION) "JOB_DESCRIPTION"
, WE.PRIMARY_ITEM_ID
, NVL(WDJ.STATUS_TYPE
, WRS.STATUS_TYPE) "STATUS_TYPE"
, MF1.MEANING "STATUS_CODE"
, WRS.LINE_ID
, WL.LINE_CODE
, NVL(WRS.FIRST_UNIT_START_DATE
, WDJ.SCHEDULED_START_DATE) "ENTITY_START_DATE"
, WO.SCHEDULED_QUANTITY
, WOR.OPERATION_SEQ_NUM
, NVL(WOR.DEPARTMENT_ID
, WO.DEPARTMENT_ID)
, BD.DEPARTMENT_CODE
, WO.DESCRIPTION "OPERATION_DESCRIPTION"
, WOR.RESOURCE_SEQ_NUM
, BR.RESOURCE_CODE
, BR.RESOURCE_TYPE
, MF5.MEANING "RESOURCE_TYPE_NAME"
, BR.DESCRIPTION
, 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
, WOR.BASIS_TYPE
, MF3.MEANING "BASIS_CODE"
, ROUND(WOR.USAGE_RATE_OR_AMOUNT
, 6)
, DECODE(WOR.USAGE_RATE_OR_AMOUNT
, 0
, NULL
, ROUND(1 / WOR.USAGE_RATE_OR_AMOUNT
, 6)) "INVERSE"
, WOR.ACTIVITY_ID
, CA.ACTIVITY
, WOR.SCHEDULED_FLAG
, MF4.MEANING "SCHEDULED_CODE"
, WOR.ASSIGNED_UNITS
, DECODE(ROUND(WOR.USAGE_RATE_OR_AMOUNT * DECODE(WOR.BASIS_TYPE
, 1
, WO.SCHEDULED_QUANTITY
, 1)
, 6)
, 0
, NULL
, ROUND(WOR.USAGE_RATE_OR_AMOUNT * DECODE(WOR.BASIS_TYPE
, 1
, WO.SCHEDULED_QUANTITY
, 1)
, 6)) "SCHEDULED_UNITS"
, WOR.USAGE_RATE_OR_AMOUNT * DECODE(WOR.BASIS_TYPE
, 1
, WO.SCHEDULED_QUANTITY
, 1) - WOR.APPLIED_RESOURCE_UNITS "REMAINING_UNITS"
, WOR.AUTOCHARGE_TYPE
, MF2.MEANING "AUTOCHARGE_CODE"
, WOR.STANDARD_RATE_FLAG
, DECODE(WOR.APPLIED_RESOURCE_UNITS
, 0
, NULL
, WOR.APPLIED_RESOURCE_UNITS) "APPLIED_RESOURCE_UNITS"
, DECODE(ROUND(WOR.USAGE_RATE_OR_AMOUNT * DECODE(WOR.BASIS_TYPE
, 1
, WO.SCHEDULED_QUANTITY
, 1) - WOR.APPLIED_RESOURCE_UNITS
, 6)
, 0
, NULL
, ROUND(WOR.USAGE_RATE_OR_AMOUNT * DECODE(WOR.BASIS_TYPE
, 1
, WO.SCHEDULED_QUANTITY
, 1) - WOR.APPLIED_RESOURCE_UNITS
, 6)) "QUANTITY_OPEN"
, 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.SCHEDULE_SEQ_NUM
, WOR.SUBSTITUTE_GROUP_NUM
, WOR.REPLACEMENT_GROUP_NUM
, WOR.PRINCIPLE_FLAG
, WOR.SETUP_ID
, WOR.PARENT_RESOURCE_SEQ
, WOR.BATCH_ID
FROM WIP_OPERATION_RESOURCES WOR
, WIP_OPERATIONS WO
, WIP_ENTITIES WE
, BOM_DEPARTMENTS BD
, CST_ACTIVITIES CA
, WIP_LINES WL
, BOM_RESOURCES BR
, WIP_REPETITIVE_SCHEDULES WRS
, WIP_DISCRETE_JOBS WDJ
, MFG_LOOKUPS MF1
, MFG_LOOKUPS MF2
, MFG_LOOKUPS MF3
, MFG_LOOKUPS MF4
, MFG_LOOKUPS MF5
WHERE MF1.LOOKUP_TYPE = 'WIP_JOB_STATUS'
AND MF1.LOOKUP_CODE = NVL(WDJ.STATUS_TYPE
, WRS.STATUS_TYPE)
AND MF2.LOOKUP_TYPE = 'BOM_AUTOCHARGE_TYPE'
AND MF2.LOOKUP_CODE = WOR.AUTOCHARGE_TYPE
AND MF3.LOOKUP_TYPE = 'CST_BASIS_SHORT'
AND MF3.LOOKUP_CODE = WOR.BASIS_TYPE
AND MF4.LOOKUP_TYPE = 'BOM_RESOURCE_SCHEDULE_TYPE'
AND MF4.LOOKUP_CODE = WOR.SCHEDULED_FLAG
AND MF5.LOOKUP_TYPE (+) = 'BOM_RESOURCE_TYPE'
AND MF5.LOOKUP_CODE (+) = BR.RESOURCE_TYPE
AND WOR.BASIS_TYPE IN (1
, 2)
AND CA.ACTIVITY_ID(+) = WOR.ACTIVITY_ID
AND WL.LINE_ID(+) = WRS.LINE_ID
AND WL.ORGANIZATION_ID(+) = WRS.ORGANIZATION_ID
AND WRS.REPETITIVE_SCHEDULE_ID(+) = WOR.REPETITIVE_SCHEDULE_ID
AND WRS.ORGANIZATION_ID(+) = WOR.ORGANIZATION_ID
AND BD.DEPARTMENT_ID = NVL(WOR.DEPARTMENT_ID
, WO.DEPARTMENT_ID)
AND BD.ORGANIZATION_ID = WO.ORGANIZATION_ID
AND BR.RESOURCE_ID = WOR.RESOURCE_ID
AND WE.WIP_ENTITY_ID = WOR.WIP_ENTITY_ID
AND WE.ENTITY_TYPE NOT IN (6
, 7)
AND WDJ.WIP_ENTITY_ID(+) = WOR.WIP_ENTITY_ID
AND WDJ.ORGANIZATION_ID(+) = WOR.ORGANIZATION_ID
AND WOR.WIP_ENTITY_ID = WO.WIP_ENTITY_ID
AND WOR.OPERATION_SEQ_NUM = WO.OPERATION_SEQ_NUM
AND WOR.ORGANIZATION_ID = WO.ORGANIZATION_ID
AND NVL(WOR.REPETITIVE_SCHEDULE_ID
, -1)=NVL(WO.REPETITIVE_SCHEDULE_ID
, -1)

Columns