VIEW: APPS.WSM_JOB_SERIAL_NUMBERS_V

Source

View Text - Preformatted

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, msn.SERIAL_ATTRIBUTE_CATEGORY , msn.TERRITORY_CODE , msn.ORIGINATION_DATE , MSN.C_ATTRIBUTE1 , MSN.C_ATTRIBUTE2 , MSN.C_ATTRIBUTE3 , MSN.C_ATTRIBUTE4 , MSN.C_ATTRIBUTE5 , MSN.C_ATTRIBUTE6 , MSN.C_ATTRIBUTE7 , MSN.C_ATTRIBUTE8 , MSN.C_ATTRIBUTE9 , MSN.C_ATTRIBUTE10 , MSN.C_ATTRIBUTE11 , MSN.C_ATTRIBUTE12 , MSN.C_ATTRIBUTE13 , MSN.C_ATTRIBUTE14 , MSN.C_ATTRIBUTE15 , MSN.C_ATTRIBUTE16 , MSN.C_ATTRIBUTE17 , MSN.C_ATTRIBUTE18 , MSN.C_ATTRIBUTE19 , MSN.C_ATTRIBUTE20 , MSN.D_ATTRIBUTE1 , MSN.D_ATTRIBUTE2 , MSN.D_ATTRIBUTE3 , MSN.D_ATTRIBUTE4 , MSN.D_ATTRIBUTE5 , MSN.D_ATTRIBUTE6 , MSN.D_ATTRIBUTE7 , MSN.D_ATTRIBUTE8 , MSN.D_ATTRIBUTE9 , MSN.D_ATTRIBUTE10 , MSN.N_ATTRIBUTE1 , MSN.N_ATTRIBUTE2 , MSN.N_ATTRIBUTE3 , MSN.N_ATTRIBUTE4 , MSN.N_ATTRIBUTE5 , MSN.N_ATTRIBUTE6 , MSN.N_ATTRIBUTE7 , MSN.N_ATTRIBUTE8 , MSN.N_ATTRIBUTE9 , MSN.N_ATTRIBUTE10 , MSN.STATUS_ID , MSN.TIME_SINCE_NEW , MSN.CYCLES_SINCE_NEW , MSN.TIME_SINCE_OVERHAUL , MSN.CYCLES_SINCE_OVERHAUL , MSN.TIME_SINCE_REPAIR , MSN.CYCLES_SINCE_REPAIR , MSN.TIME_SINCE_VISIT , MSN.CYCLES_SINCE_VISIT , MSN.TIME_SINCE_MARK , MSN.CYCLES_SINCE_MARK , MSN.NUMBER_OF_REPAIRS , MSN.ATTRIBUTE_CATEGORY , MSN.ATTRIBUTE1 , MSN.ATTRIBUTE2 , MSN.ATTRIBUTE3 , MSN.ATTRIBUTE4 , MSN.ATTRIBUTE5 , MSN.ATTRIBUTE6 , MSN.ATTRIBUTE7 , MSN.ATTRIBUTE8 , MSN.ATTRIBUTE9 , MSN.ATTRIBUTE10 , MSN.ATTRIBUTE11 , MSN.ATTRIBUTE12 , MSN.ATTRIBUTE13 , MSN.ATTRIBUTE14 , MSN.ATTRIBUTE15 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), msn.SERIAL_ATTRIBUTE_CATEGORY , msn.TERRITORY_CODE , msn.ORIGINATION_DATE , MSN.C_ATTRIBUTE1 , MSN.C_ATTRIBUTE2 , MSN.C_ATTRIBUTE3 , MSN.C_ATTRIBUTE4 , MSN.C_ATTRIBUTE5 , MSN.C_ATTRIBUTE6 , MSN.C_ATTRIBUTE7 , MSN.C_ATTRIBUTE8 , MSN.C_ATTRIBUTE9 , MSN.C_ATTRIBUTE10 , MSN.C_ATTRIBUTE11 , MSN.C_ATTRIBUTE12 , MSN.C_ATTRIBUTE13 , MSN.C_ATTRIBUTE14 , MSN.C_ATTRIBUTE15 , MSN.C_ATTRIBUTE16 , MSN.C_ATTRIBUTE17 , MSN.C_ATTRIBUTE18 , MSN.C_ATTRIBUTE19 , MSN.C_ATTRIBUTE20 , MSN.D_ATTRIBUTE1 , MSN.D_ATTRIBUTE2 , MSN.D_ATTRIBUTE3 , MSN.D_ATTRIBUTE4 , MSN.D_ATTRIBUTE5 , MSN.D_ATTRIBUTE6 , MSN.D_ATTRIBUTE7 , MSN.D_ATTRIBUTE8 , MSN.D_ATTRIBUTE9 , MSN.D_ATTRIBUTE10 , MSN.N_ATTRIBUTE1 , MSN.N_ATTRIBUTE2 , MSN.N_ATTRIBUTE3 , MSN.N_ATTRIBUTE4 , MSN.N_ATTRIBUTE5 , MSN.N_ATTRIBUTE6 , MSN.N_ATTRIBUTE7 , MSN.N_ATTRIBUTE8 , MSN.N_ATTRIBUTE9 , MSN.N_ATTRIBUTE10 , MSN.STATUS_ID , MSN.TIME_SINCE_NEW , MSN.CYCLES_SINCE_NEW , MSN.TIME_SINCE_OVERHAUL , MSN.CYCLES_SINCE_OVERHAUL , MSN.TIME_SINCE_REPAIR , MSN.CYCLES_SINCE_REPAIR , MSN.TIME_SINCE_VISIT , MSN.CYCLES_SINCE_VISIT , MSN.TIME_SINCE_MARK , MSN.CYCLES_SINCE_MARK , MSN.NUMBER_OF_REPAIRS , MSN.ATTRIBUTE_CATEGORY , MSN.ATTRIBUTE1 , MSN.ATTRIBUTE2 , MSN.ATTRIBUTE3 , MSN.ATTRIBUTE4 , MSN.ATTRIBUTE5 , MSN.ATTRIBUTE6 , MSN.ATTRIBUTE7 , MSN.ATTRIBUTE8 , MSN.ATTRIBUTE9 , MSN.ATTRIBUTE10 , MSN.ATTRIBUTE11 , MSN.ATTRIBUTE12 , MSN.ATTRIBUTE13 , MSN.ATTRIBUTE14 , MSN.ATTRIBUTE15 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 msn.wip_entity_id IS NULL and we.gen_object_id = mog.object_id

View Text - HTML Formatted

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
, MSN.SERIAL_ATTRIBUTE_CATEGORY
, MSN.TERRITORY_CODE
, MSN.ORIGINATION_DATE
, MSN.C_ATTRIBUTE1
, MSN.C_ATTRIBUTE2
, MSN.C_ATTRIBUTE3
, MSN.C_ATTRIBUTE4
, MSN.C_ATTRIBUTE5
, MSN.C_ATTRIBUTE6
, MSN.C_ATTRIBUTE7
, MSN.C_ATTRIBUTE8
, MSN.C_ATTRIBUTE9
, MSN.C_ATTRIBUTE10
, MSN.C_ATTRIBUTE11
, MSN.C_ATTRIBUTE12
, MSN.C_ATTRIBUTE13
, MSN.C_ATTRIBUTE14
, MSN.C_ATTRIBUTE15
, MSN.C_ATTRIBUTE16
, MSN.C_ATTRIBUTE17
, MSN.C_ATTRIBUTE18
, MSN.C_ATTRIBUTE19
, MSN.C_ATTRIBUTE20
, MSN.D_ATTRIBUTE1
, MSN.D_ATTRIBUTE2
, MSN.D_ATTRIBUTE3
, MSN.D_ATTRIBUTE4
, MSN.D_ATTRIBUTE5
, MSN.D_ATTRIBUTE6
, MSN.D_ATTRIBUTE7
, MSN.D_ATTRIBUTE8
, MSN.D_ATTRIBUTE9
, MSN.D_ATTRIBUTE10
, MSN.N_ATTRIBUTE1
, MSN.N_ATTRIBUTE2
, MSN.N_ATTRIBUTE3
, MSN.N_ATTRIBUTE4
, MSN.N_ATTRIBUTE5
, MSN.N_ATTRIBUTE6
, MSN.N_ATTRIBUTE7
, MSN.N_ATTRIBUTE8
, MSN.N_ATTRIBUTE9
, MSN.N_ATTRIBUTE10
, MSN.STATUS_ID
, MSN.TIME_SINCE_NEW
, MSN.CYCLES_SINCE_NEW
, MSN.TIME_SINCE_OVERHAUL
, MSN.CYCLES_SINCE_OVERHAUL
, MSN.TIME_SINCE_REPAIR
, MSN.CYCLES_SINCE_REPAIR
, MSN.TIME_SINCE_VISIT
, MSN.CYCLES_SINCE_VISIT
, MSN.TIME_SINCE_MARK
, MSN.CYCLES_SINCE_MARK
, MSN.NUMBER_OF_REPAIRS
, MSN.ATTRIBUTE_CATEGORY
, MSN.ATTRIBUTE1
, MSN.ATTRIBUTE2
, MSN.ATTRIBUTE3
, MSN.ATTRIBUTE4
, MSN.ATTRIBUTE5
, MSN.ATTRIBUTE6
, MSN.ATTRIBUTE7
, MSN.ATTRIBUTE8
, MSN.ATTRIBUTE9
, MSN.ATTRIBUTE10
, MSN.ATTRIBUTE11
, MSN.ATTRIBUTE12
, MSN.ATTRIBUTE13
, MSN.ATTRIBUTE14
, MSN.ATTRIBUTE15
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)
, MSN.SERIAL_ATTRIBUTE_CATEGORY
, MSN.TERRITORY_CODE
, MSN.ORIGINATION_DATE
, MSN.C_ATTRIBUTE1
, MSN.C_ATTRIBUTE2
, MSN.C_ATTRIBUTE3
, MSN.C_ATTRIBUTE4
, MSN.C_ATTRIBUTE5
, MSN.C_ATTRIBUTE6
, MSN.C_ATTRIBUTE7
, MSN.C_ATTRIBUTE8
, MSN.C_ATTRIBUTE9
, MSN.C_ATTRIBUTE10
, MSN.C_ATTRIBUTE11
, MSN.C_ATTRIBUTE12
, MSN.C_ATTRIBUTE13
, MSN.C_ATTRIBUTE14
, MSN.C_ATTRIBUTE15
, MSN.C_ATTRIBUTE16
, MSN.C_ATTRIBUTE17
, MSN.C_ATTRIBUTE18
, MSN.C_ATTRIBUTE19
, MSN.C_ATTRIBUTE20
, MSN.D_ATTRIBUTE1
, MSN.D_ATTRIBUTE2
, MSN.D_ATTRIBUTE3
, MSN.D_ATTRIBUTE4
, MSN.D_ATTRIBUTE5
, MSN.D_ATTRIBUTE6
, MSN.D_ATTRIBUTE7
, MSN.D_ATTRIBUTE8
, MSN.D_ATTRIBUTE9
, MSN.D_ATTRIBUTE10
, MSN.N_ATTRIBUTE1
, MSN.N_ATTRIBUTE2
, MSN.N_ATTRIBUTE3
, MSN.N_ATTRIBUTE4
, MSN.N_ATTRIBUTE5
, MSN.N_ATTRIBUTE6
, MSN.N_ATTRIBUTE7
, MSN.N_ATTRIBUTE8
, MSN.N_ATTRIBUTE9
, MSN.N_ATTRIBUTE10
, MSN.STATUS_ID
, MSN.TIME_SINCE_NEW
, MSN.CYCLES_SINCE_NEW
, MSN.TIME_SINCE_OVERHAUL
, MSN.CYCLES_SINCE_OVERHAUL
, MSN.TIME_SINCE_REPAIR
, MSN.CYCLES_SINCE_REPAIR
, MSN.TIME_SINCE_VISIT
, MSN.CYCLES_SINCE_VISIT
, MSN.TIME_SINCE_MARK
, MSN.CYCLES_SINCE_MARK
, MSN.NUMBER_OF_REPAIRS
, MSN.ATTRIBUTE_CATEGORY
, MSN.ATTRIBUTE1
, MSN.ATTRIBUTE2
, MSN.ATTRIBUTE3
, MSN.ATTRIBUTE4
, MSN.ATTRIBUTE5
, MSN.ATTRIBUTE6
, MSN.ATTRIBUTE7
, MSN.ATTRIBUTE8
, MSN.ATTRIBUTE9
, MSN.ATTRIBUTE10
, MSN.ATTRIBUTE11
, MSN.ATTRIBUTE12
, MSN.ATTRIBUTE13
, MSN.ATTRIBUTE14
, MSN.ATTRIBUTE15
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 MSN.WIP_ENTITY_ID IS NULL
AND WE.GEN_OBJECT_ID = MOG.OBJECT_ID