SELECT wsg.schedule_group_name,
/*RTRIM (LTRIM (msi.attribute1, '0'), '0') LENGTH,
RTRIM (LTRIM (msi.attribute2, '0'), '0') width,
RTRIM (LTRIM (msi.attribute3, '0'), '0') thick,
*/ -- Commented as part of remediation on 4-Nov
rtrim(ltrim(msi.Unit_length,'0'),'0') length,
rtrim(ltrim(msi.Unit_Width,'0'),'0') width,
rtrim(ltrim(msi.Unit_Height,'0'),'0') thick,
RTRIM (LTRIM (msi.attribute4, '0'), '0') face, msi.attribute9 vdwg,
we.wip_entity_name job, msi.segment1 assembly,
SUBSTR (msi.description, 1, 15) description,
DECODE (msi.inventory_planning_code,
2, 'MIN',
6, '',
'?'
)
|| ' '
|| DECODE (msi.mrp_planning_code, 3, 'MRP', 6, '', '?') plan_code,
wdj.start_quantity quantity, wdj.scheduled_completion_date pack_date,
wdj.start_quantity
- (NVL (wdj.quantity_completed, 0) + NVL (wdj.quantity_scrapped, 0))
rem_qty,
wo.quantity_in_queue + wo.quantity_running + wo.quantity_waiting_to_move wo_qty,
wdj.wip_entity_id,
wdj.primary_item_id,
wdj.organization_id,
wo.description wo_description,
wo.operation_seq_num,
wo.quantity_running wo_r,
wo.quantity_in_queue wo_q,
wo.quantity_waiting_to_move wo_m,
'R' status_type,
(select max(wmt.transaction_date) from wip_move_transactions wmt where wmt.wip_entity_id=wdj.wip_entity_id and wmt.to_operation_seq_num=wo.operation_seq_num) last_transaction,
case when (case when wo.quantity_in_queue>0 then 'Q' end||case when wo.quantity_running>0 then 'R' end||case when wo.quantity_waiting_to_move>0 then 'M' end) is not null
then substr(wo.description,1,6)||'-'||(case when wo.quantity_in_queue>0 then 'Q' end||case when wo.quantity_running>0 then 'R' end||case when wo.quantity_waiting_to_move>0 then 'M' end) end step,
(select br.resource_code from bom_resources br where br.resource_id=:p_resource_id) resource_name,
substr(rtrim(ltrim(msi.unit_length,'0'),'0')||'x'||rtrim(ltrim(msi.unit_width,'0'),'0')||'x'||rtrim(ltrim(msi.unit_height,'0'),'0'),1,20) dimensions
FROM wip_schedule_groups wsg,
wip_entities we,
mtl_system_items_b msi,
wip_discrete_jobs wdj,
wip_operations wo,
WIP_OPERATION_RESOURCES orr
WHERE we.primary_item_id = msi.inventory_item_id
AND wdj.wip_entity_id = wo.wip_entity_id
AND wdj.organization_id = wo.organization_id
AND wo.wip_entity_id = orr.wip_entity_id (+)
AND wo.organization_id = orr.organization_id (+)
--AND wo.operation_seq_num = orr.operation_seq_num (+)
AND wo.quantity_in_queue + wo.quantity_running +wo.quantity_waiting_to_move > 0
AND wdj.status_type = 3
-- AND wo.operation_seq_num IN (SELECT MIN (operation_seq_num)
-- FROM wip_operations wo2
-- WHERE wo2.wip_entity_id = wo.wip_entity_id)
AND we.organization_id = msi.organization_id
AND we.wip_entity_id = wdj.wip_entity_id
AND we.organization_id = wdj.organization_id
AND wdj.schedule_group_id = wsg.schedule_group_id(+)
and 1=1
-- only look at jobs were quantity not completed
AND NVL (wdj.quantity_completed, 0) + NVL (wdj.quantity_scrapped, 0) <>
wdj.start_quantity
AND ( ( (msi.segment1 NOT LIKE '6%' AND :p_6million = 'Yes')
AND ( (msi.segment1 LIKE '5%' AND :p_5million = 'No')
OR msi.segment1 NOT LIKE '5%'
)
)
OR ( (msi.segment1 NOT LIKE '5%' AND :p_5million = 'Yes')
AND ( (msi.segment1 LIKE '6%' AND :p_6million = 'No')
OR msi.segment1 NOT LIKE '6%'
)
)
OR (msi.segment1 LIKE '6%' AND (:p_6milliononly = 'Yes'))
OR (msi.segment1 LIKE '5%' AND (:p_5milliononly = 'Yes'))
OR ( :p_6milliononly = 'No'
AND :p_6million = 'No'
AND :p_5milliononly = 'No'
AND :p_5million = 'No'
)
)
UNION
SELECT wsg.schedule_group_name,
/*RTRIM (LTRIM (msi.attribute1, '0'), '0') LENGTH,
RTRIM (LTRIM (msi.attribute2, '0'), '0') width,
RTRIM (LTRIM (msi.attribute3, '0'), '0') thick,
*/ -- Commented as part of remediation on 4-Nov-16
rtrim(ltrim(msi.Unit_length,'0'),'0') length,
rtrim(ltrim(msi.Unit_Width,'0'),'0') width,
rtrim(ltrim(msi.Unit_Height,'0'),'0') thick,
RTRIM (LTRIM (msi.attribute4, '0'), '0') face, msi.attribute9 vdwg,
we.wip_entity_name job, msi.segment1 assembly,
SUBSTR (msi.description, 1, 15) description,
DECODE (msi.inventory_planning_code,
2, 'MIN',
6, '',
'?'
)
|| ' '
|| DECODE (msi.mrp_planning_code, 3, 'MRP', 6, '', '?') plan_code,
wdj.start_quantity quantity,
wdj.scheduled_completion_date pack_date,
wdj.start_quantity - (NVL (wdj.quantity_completed, 0) + NVL (wdj.quantity_scrapped, 0)) rem_qty,
wo.quantity_in_queue + wo.quantity_running wo_qty, wdj.wip_entity_id,
wdj.primary_item_id, wdj.organization_id,
wo.description wo_description,
wo.operation_seq_num,
wo.quantity_running ,
wo.quantity_in_queue ,
wo.quantity_waiting_to_move ,
'U' status_type,
(select max(wmt.transaction_date) from wip_move_transactions wmt where wmt.wip_entity_id=wdj.wip_entity_id and wmt.to_operation_seq_num=wo.operation_seq_num) last_transaction,
case when (case when wo.quantity_in_queue>0 then 'Q' end||case when wo.quantity_running>0 then 'R' end||case when wo.quantity_waiting_to_move>0 then 'M' end) is not null
then substr(wo.description,1,6)||'-'||(case when wo.quantity_in_queue>0 then 'Q' end||case when wo.quantity_running>0 then 'R' end||case when wo.quantity_waiting_to_move>0 then 'M' end) end step,
(select br.resource_code from bom_resources br where br.resource_id=:p_resource_id) resource_name,
substr(rtrim(ltrim(msi.unit_length,'0'),'0')||'x'||rtrim(ltrim(msi.unit_width,'0'),'0')||'x'||rtrim(ltrim(msi.unit_height,'0'),'0'),1,20) dimensions
FROM wip_schedule_groups wsg,
wip_entities we,
mtl_system_items_b msi,
wip_discrete_jobs wdj,
wip_operations wo,
WIP_OPERATION_RESOURCES orr
WHERE we.primary_item_id = msi.inventory_item_id
AND wdj.wip_entity_id = wo.wip_entity_id
AND wdj.organization_id = wo.organization_id
AND wo.wip_entity_id = orr.wip_entity_id (+)
AND wo.organization_id = orr.organization_id (+)
--AND wo.operation_seq_num = orr.operation_seq_num (+)
AND wdj.status_type = 1
AND wo.operation_seq_num IN (SELECT MIN (operation_seq_num)
FROM wip_operations wo2
WHERE wo2.wip_entity_id = wo.wip_entity_id)
AND we.organization_id = msi.organization_id
AND we.wip_entity_id = wdj.wip_entity_id
AND we.organization_id = wdj.organization_id
AND wdj.schedule_group_id = wsg.schedule_group_id(+)
and 1=1
-- only look at jobs were quantity not completed
AND NVL (wdj.quantity_completed, 0) + NVL (wdj.quantity_scrapped, 0) <>
wdj.start_quantity
AND ( ( (msi.segment1 NOT LIKE '6%' AND :p_6million = 'Yes')
AND ( (msi.segment1 LIKE '5%' AND :p_5million = 'No')
OR msi.segment1 NOT LIKE '5%'
)
)
OR ( (msi.segment1 NOT LIKE '5%' AND :p_5million = 'Yes')
AND ( (msi.segment1 LIKE '6%' AND :p_6million = 'No')
OR msi.segment1 NOT LIKE '6%'
)
)
OR (msi.segment1 LIKE '6%' AND (:p_6milliononly = 'Yes'))
OR (msi.segment1 LIKE '5%' AND (:p_5milliononly = 'Yes'))
OR ( :p_6milliononly = 'No'
AND :p_6million = 'No'
AND :p_5milliononly = 'No'
AND :p_5million = 'No'
)
) |
