select distinct
We.Wip_Entity_Name JOB,
msib.segment1 ASSEMBLY,
msib.description "DESCRIPTION",
(SELECT Meaning
FROM apps.fnd_lookup_values
WHERE lookup_type LIKE 'WIP_ENTITY' and lookup_code = we.entity_type and language = 'US') TYPE,
Wdj.Class_Code CLASS,
wdj.net_quantity QUANTITY,
(SELECT NVL(msib1.segment1, ' ')
from apps.mtl_system_items_b msib1
where msib1.inventory_item_id = wdj.bom_reference_id
and msib1.ORGANIZATION_ID = wdj.ORGANIZATION_ID) BOM_REFERENCE,
NVL(Wdj.Alternate_BOM_Designator, ' ') ALTERNATE_BILL,
(SELECT NVL(msib2.segment1, ' ')
from apps.mtl_system_items_b msib2
where msib2.inventory_item_id = wdj.routing_reference_id
and msib2.ORGANIZATION_ID = wdj.ORGANIZATION_ID) ROUTING_REFERENCE,
NVL(Wdj.Alternate_Routing_Designator, ' ') ALTERNATE_ROUTING,
(SELECT Meaning
FROM apps.fnd_lookup_values
WHERE lookup_type LIKE 'WIP_CLASS_TYPE' and lookup_code = wdj.job_type and language = 'US') "WO TYPE",
(SELECT Meaning
FROM apps.fnd_lookup_values
WHERE lookup_type LIKE 'WIP_JOB_STATUS' and lookup_code = wdj.status_type and language = 'US') STATUS,
NVL(We.Description, ' ') "WO DESCRIPTION",
Wdj.date_released WO_RELEASE_DATE,
Wdj.date_completed WO_COMPLETION_DATE,
wdj.date_closed WO_CLOSED_DATE,
wo.Operation_Seq_Num OPERATION_SEQ_NUM,
to_char(Wo.First_Unit_Start_Date,'dd-mon-yyyy hh24:mi:ss') OPN_START_DATE,
to_char(Wo.Last_Unit_Completion_Date,'dd-mon-yyyy hh24:mi:ss') OPN_COMPLETION_DATE,
NVL(Bso.Operation_Code, ' ') OPERATION_CODE,
(Select Round((wo.Last_Unit_Completion_Date - wo.First_Unit_Start_Date)* 24*60,0)"Round" from DUAL) RUN_TIME_MINS
from
apps.wip_operations wo
,apps.wip_entities we
,apps.wip_discrete_jobs wdj
,apps.mtl_system_items_b msib
,apps.bom_operational_routings bor
,apps.bom_operation_sequences bos
,apps.bom_standard_operations bso
,apps.mfg_lookups ml
where 1=1
and wo.wip_entity_id = we.wip_entity_id
and wo.operation_sequence_id = bos.operation_sequence_id
and wdj.wip_entity_id = we.wip_entity_id
and msib.inventory_item_id = wdj.primary_item_id
and msib.ORGANIZATION_ID = wdj.ORGANIZATION_ID
and msib.inventory_item_id = bor.assembly_item_id
and bos.routing_sequence_id = bor.routing_sequence_id
and bso.standard_operation_id(+) = bos.standard_operation_id
and ml.lookup_code (+) = to_number(bos.shutdown_type)
and ml.lookup_type (+) = 'BOM_EAM_SHUTDOWN_TYPE'
and we.entity_type not in (6,7)
and Wdj.Class_Code not in ('BLDGMAINT', 'CALIB', 'ENGMAINT', 'MAINT')
Order by to_char(Wdj.date_released,'dd-mon-yyyy hh24:mi:ss'), We.Wip_Entity_Name, Wo.Operation_Seq_Num |
