View: WIP_JOB_SERIAL_NUMBERS_V

Product:	WIP - Work in Process
Description:
Implementation/DBA Data:	APPS.WIP_JOB_SERIAL_NUMBERS_V

View Text

SELECT MSN.SERIAL_NUMBER
, DECODE(MSN.OPERATION_SEQ_NUM
, NULL
, 1
, 2) WIP_SERIAL_STATUS
, ML1.MEANING WIP_SERIAL_STATUS_CODE
, MSN.CURRENT_STATUS
, MSN.ROWID MSN_ROWID
, MSN.WIP_ENTITY_ID
, MSN.CURRENT_ORGANIZATION_ID ORGANIZATION_ID
, MSN.INVENTORY_ITEM_ID PRIMARY_ITEM_ID
, BD.DEPARTMENT_CODE
, MSN.OPERATION_SEQ_NUM
, MSN.INTRAOPERATION_STEP_TYPE
, ML2.MEANING INTRAOPERATION_STEP_CODE
, BSO.OPERATION_CODE STANDARD_OPERATION_CODE
, MSN.GROUP_MARK_ID
, MSN.LINE_MARK_ID
FROM MTL_SERIAL_NUMBERS MSN
, BOM_DEPARTMENTS BD
, BOM_STANDARD_OPERATIONS BSO
, WIP_OPERATIONS WO
, WIP_ENTITIES WE
, MFG_LOOKUPS ML1
, MFG_LOOKUPS ML2
WHERE MSN.WIP_ENTITY_ID = WO.WIP_ENTITY_ID (+)
AND MSN.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
AND MSN.OPERATION_SEQ_NUM = WO.OPERATION_SEQ_NUM (+)
AND BSO.STANDARD_OPERATION_ID (+) = WO.STANDARD_OPERATION_ID
AND BD.DEPARTMENT_ID (+) = WO.DEPARTMENT_ID
AND ML1.LOOKUP_TYPE = 'WIP_SERIAL_STATUS'
AND ML1.LOOKUP_CODE = DECODE(MSN.OPERATION_SEQ_NUM
, NULL
, 1
, 2)
AND ML2.LOOKUP_TYPE (+) = 'WIP_INTRAOPERATION_STEP'
AND ML2.LOOKUP_CODE (+) = MSN.INTRAOPERATION_STEP_TYPE UNION ALL SELECT MSN.SERIAL_NUMBER
, 3
, ML1.MEANING
, MSN.CURRENT_STATUS
, MSN.ROWID MSN_ROWID
, WE.WIP_ENTITY_ID
, MSN.CURRENT_ORGANIZATION_ID
, MSN.INVENTORY_ITEM_ID
, TO_CHAR(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
FROM MTL_SERIAL_NUMBERS MSN
, MFG_LOOKUPS ML1
, MTL_OBJECT_GENEALOGY MOG
, WIP_ENTITIES WE
WHERE MSN.GEN_OBJECT_ID = MOG.PARENT_OBJECT_ID
AND MOG.OBJECT_TYPE = 5
AND MOG.PARENT_OBJECT_TYPE = 2
AND MOG.END_DATE_ACTIVE IS NULL
AND ML1.LOOKUP_TYPE = 'WIP_SERIAL_STATUS'
AND ML1.LOOKUP_CODE = 3
AND WE.GEN_OBJECT_ID = MOG.OBJECT_ID

Columns