[Home] [Help]
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
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
|
|
|
|