eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.WIP_OPERATION_RESOURCES_INQ_V

Source

View Text - Preformatted


SELECT 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(WE.ENTITY_TYPE, 5, DECODE(ROUND(WOR.USAGE_RATE_OR_AMOUNT * DECODE(WOR.BASIS_TYPE,1, WDJ.START_QUANTITY - NVL(WO.CUMULATIVE_SCRAP_QUANTITY,0), DECODE(SIGN(WDJ.START_QUANTITY - NVL(WO.CUMULATIVE_SCRAP_QUANTITY,0)),1,1,0) ) ,6), 0, NULL, ROUND(WOR.USAGE_RATE_OR_AMOUNT * DECODE(WOR.BASIS_TYPE,1, WDJ.START_QUANTITY - NVL(WO.CUMULATIVE_SCRAP_QUANTITY,0), DECODE(SIGN(WDJ.START_QUANTITY - NVL(WO.CUMULATIVE_SCRAP_QUANTITY, 0)),1,1,0)),6)), 8, DECODE(ROUND(WOR.USAGE_RATE_OR_AMOUNT * DECODE(WOR.BASIS_TYPE,1, WDJ.START_QUANTITY - NVL(WO.CUMULATIVE_SCRAP_QUANTITY,0), DECODE( SIGN(WDJ.START_QUANTITY - NVL(WO.CUMULATIVE_SCRAP_QUANTITY,0)),1,1,0) ) ,6) , 0, NULL, ROUND(WOR.USAGE_RATE_OR_AMOUNT * DECODE(WOR.BASIS_TYPE,1, WDJ.START_QUANTITY - NVL(WO.CUMULATIVE_SCRAP_QUANTITY,0), DECODE(SIGN(WDJ.START_QUANTITY - NVL(WO.CUMULATIVE_SCRAP_QUANTITY,0)),1,1,0)),6)), 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" , DECODE(WE.ENTITY_TYPE, 5, WOR.USAGE_RATE_OR_AMOUNT * DECODE(WOR.BASIS_TYPE,1, WDJ.START_QUANTITY - NVL(WO.CUMULATIVE_SCRAP_QUANTITY,0), DECODE(SIGN(WDJ.START_QUANTITY - NVL(WO.CUMULATIVE_SCRAP_QUANTITY,0)),1,1,0) ), 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(WE.ENTITY_TYPE, 5, DECODE(ROUND(WOR.USAGE_RATE_OR_AMOUNT * DECODE(WOR.BASIS_TYPE,1, WDJ.START_QUANTITY - NVL(WO.CUMULATIVE_SCRAP_QUANTITY,0), DECODE(SIGN(WDJ.START_QUANTITY - NVL(WO.CUMULATIVE_SCRAP_QUANTITY,0)),1,1,0) )- WOR.APPLIED_RESOURCE_UNITS ,6), 0, NULL, ROUND(WOR.USAGE_RATE_OR_AMOUNT * DECODE(WOR.BASIS_TYPE,1, WDJ.START_QUANTITY - NVL(WO.CUMULATIVE_SCRAP_QUANTITY,0), DECODE(sign(WDJ.START_QUANTITY - NVL(WO.CUMULATIVE_SCRAP_QUANTITY,0)),1,1,0))- WOR.APPLIED_RESOURCE_UNITS ,6)), 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)

View Text - HTML Formatted

SELECT 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(WE.ENTITY_TYPE
, 5
, DECODE(ROUND(WOR.USAGE_RATE_OR_AMOUNT * DECODE(WOR.BASIS_TYPE
, 1
, WDJ.START_QUANTITY - NVL(WO.CUMULATIVE_SCRAP_QUANTITY
, 0)
, DECODE(SIGN(WDJ.START_QUANTITY - NVL(WO.CUMULATIVE_SCRAP_QUANTITY
, 0))
, 1
, 1
, 0) )
, 6)
, 0
, NULL
, ROUND(WOR.USAGE_RATE_OR_AMOUNT * DECODE(WOR.BASIS_TYPE
, 1
, WDJ.START_QUANTITY - NVL(WO.CUMULATIVE_SCRAP_QUANTITY
, 0)
, DECODE(SIGN(WDJ.START_QUANTITY - NVL(WO.CUMULATIVE_SCRAP_QUANTITY
, 0))
, 1
, 1
, 0))
, 6))
, 8
, DECODE(ROUND(WOR.USAGE_RATE_OR_AMOUNT * DECODE(WOR.BASIS_TYPE
, 1
, WDJ.START_QUANTITY - NVL(WO.CUMULATIVE_SCRAP_QUANTITY
, 0)
, DECODE( SIGN(WDJ.START_QUANTITY - NVL(WO.CUMULATIVE_SCRAP_QUANTITY
, 0))
, 1
, 1
, 0) )
, 6)
, 0
, NULL
, ROUND(WOR.USAGE_RATE_OR_AMOUNT * DECODE(WOR.BASIS_TYPE
, 1
, WDJ.START_QUANTITY - NVL(WO.CUMULATIVE_SCRAP_QUANTITY
, 0)
, DECODE(SIGN(WDJ.START_QUANTITY - NVL(WO.CUMULATIVE_SCRAP_QUANTITY
, 0))
, 1
, 1
, 0))
, 6))
, 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"
, DECODE(WE.ENTITY_TYPE
, 5
, WOR.USAGE_RATE_OR_AMOUNT * DECODE(WOR.BASIS_TYPE
, 1
, WDJ.START_QUANTITY - NVL(WO.CUMULATIVE_SCRAP_QUANTITY
, 0)
, DECODE(SIGN(WDJ.START_QUANTITY - NVL(WO.CUMULATIVE_SCRAP_QUANTITY
, 0))
, 1
, 1
, 0) )
, 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(WE.ENTITY_TYPE
, 5
, DECODE(ROUND(WOR.USAGE_RATE_OR_AMOUNT * DECODE(WOR.BASIS_TYPE
, 1
, WDJ.START_QUANTITY - NVL(WO.CUMULATIVE_SCRAP_QUANTITY
, 0)
, DECODE(SIGN(WDJ.START_QUANTITY - NVL(WO.CUMULATIVE_SCRAP_QUANTITY
, 0))
, 1
, 1
, 0) )- WOR.APPLIED_RESOURCE_UNITS
, 6)
, 0
, NULL
, ROUND(WOR.USAGE_RATE_OR_AMOUNT * DECODE(WOR.BASIS_TYPE
, 1
, WDJ.START_QUANTITY - NVL(WO.CUMULATIVE_SCRAP_QUANTITY
, 0)
, DECODE(SIGN(WDJ.START_QUANTITY - NVL(WO.CUMULATIVE_SCRAP_QUANTITY
, 0))
, 1
, 1
, 0))- WOR.APPLIED_RESOURCE_UNITS
, 6))
, 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)