ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 0F5063C8D7E14A2B9C0D1E2F3A4B5C6DY 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' ) ) v02 - bind anchors converted to 1=1 + SQL_TEXT for the independent optional filters (Org Id, Jobs/Assemblies/Pack Dates From-To); Drawer converted to matching-value (E exclude / O only; blank=all); Resource ID 1=1. The 4 six/five-million flags stay as bind anchors (one interdependent boolean - cannot be split). Added Request Group assignments (All Reports@WIP, All Inclusive GUI@INV) - confirm vs the program's actual request groups (the gather did not capture _assignments.xml). US OFS Brands Work Order Schedule Detail Custom Reports OFS 1=1 :p_5million :p_5milliononly :p_6million :p_6milliononly 1 -10 1=1 we.organization_id=:p_org_id Number fnd_profile.value('MFG_ORGANIZATION_ID') Y US Org Id 2 10 1=1 we.wip_entity_name>=:p_from_job LOV custom Y Y select we.wip_entity_name id, we.wip_entity_name value, msiv.concatenated_segments description from wip_entities we, wip_discrete_jobs wdj, mtl_system_items_vl msiv where we.wip_entity_id=wdj.wip_entity_id and we.organization_id=:$flex$.Org_Id and msiv.inventory_item_id(+)=we.primary_item_id and msiv.organization_id(+)=we.organization_id order by we.wip_entity_name US Jobs From 3 20 1=1 we.wip_entity_name<=:p_to_job LOV custom Y Y select we.wip_entity_name id, we.wip_entity_name value, msiv.concatenated_segments description from wip_entities we, wip_discrete_jobs wdj, mtl_system_items_vl msiv where we.wip_entity_id=wdj.wip_entity_id and we.organization_id=:$flex$.Org_Id and msiv.inventory_item_id(+)=we.primary_item_id and msiv.organization_id(+)=we.organization_id order by we.wip_entity_name US Jobs To 4 30 1=1 msi.segment1>=:p_from_assmb LOV custom Y Y select distinct msiv.segment1 id, msiv.segment1 value, msiv.description description from mtl_system_items_vl msiv, wip_discrete_jobs wdj, wip_entities we where msiv.organization_id=:$flex$.Org_Id and we.wip_entity_id=wdj.wip_entity_id and we.primary_item_id=msiv.inventory_item_id and we.organization_id=msiv.organization_id order by msiv.segment1 US Assemblies From 5 40 1=1 msi.segment1<=:p_to_assmb LOV custom Y Y select distinct msiv.segment1 id, msiv.segment1 value, msiv.description description from mtl_system_items_vl msiv, wip_discrete_jobs wdj, wip_entities we where msiv.organization_id=:$flex$.Org_Id and we.wip_entity_id=wdj.wip_entity_id and we.primary_item_id=msiv.inventory_item_id and we.organization_id=msiv.organization_id order by msiv.segment1 US Assemblies To 6 50 1=1 wdj.scheduled_completion_date>=:p_from_date Date US Pack Dates From 7 60 1=1 wdj.scheduled_completion_date<:p_to_date+1 Date US Pack Dates To 8 70 :p_6million LOV custom Y select 'Yes' id, 'Yes' value, null description from dual union all select 'No' id, 'No' value, null description from dual No Y US Exclude 6 Million Parts? 9 80 :p_6milliononly LOV custom Y select 'Yes' id, 'Yes' value, null description from dual union all select 'No' id, 'No' value, null description from dual No Y US Only 6 Million Parts 10 90 :p_5million LOV custom Y select 'Yes' id, 'Yes' value, null description from dual union all select 'No' id, 'No' value, null description from dual No Y US Exclude 5 Million Parts? 11 100 :p_5milliononly LOV custom Y select 'Yes' id, 'Yes' value, null description from dual union all select 'No' id, 'No' value, null description from dual No Y US Only 5 Million Parts 12 110 1=1 nvl(wsg.schedule_group_name,'x')<>'DRAWER' LOV custom Y select 'A' id, 'All jobs' value, null description from dual union all select 'E' id, 'Exclude drawers' value, null description from dual union all select 'O' id, 'Only drawers' value, null description from dual E All jobs US Drawer 13 1=1 nvl(wsg.schedule_group_name,'x')='DRAWER' LOV O US Drawer 14 120 1=1 orr.resource_id=:p_resource_id LOV custom Y select br.resource_id id, br.resource_code value, br.description from bom_resources br where br.resource_id is not null order by br.resource_code US Resource ID