select
mp.organization_code,
we.wip_entity_name job,
xxen_util.meaning(we.entity_type,'WIP_ENTITY',700) type,
xxen_util.meaning(wdj.status_type,'WIP_JOB_STATUS',700) status,
msiv.planner_code,
mpl.description planner,
wsg.schedule_group_name schedule_group,
msiv.concatenated_segments item,
msiv.description item_description,
&category_columns
xxen_util.meaning(msiv.item_type,'ITEM_TYPE',3) user_item_type,
xxen_util.meaning(msiv.mrp_planning_code,'MRP_PLANNING_CODE',700) planning_method,
xxen_util.meaning(msiv.end_assembly_pegging_flag,'ASSEMBLY_PEGGING_CODE',0) pegging,
we.description entity_description,
wdj.description job_description,
nvl(cii.instance_number,wdj.asset_number) asset_number,
ppa.project_number,
pt.task_number,
wdj.source_code,
wdj.source_line_id,
ooha.order_number sales_order_number,
rtrim(oola.line_number||'.'||oola.shipment_number||'.'||oola.option_number||'.'||oola.component_number||'.'||oola.service_number,'.') sales_order_line,
xxen_util.meaning(wdj.firm_planned_flag,'SYS_YES_NO',700) firm,
xxen_util.meaning(wdj.job_type,'WIP_DISCRETE_JOB',700) job_type,
xxen_util.meaning(wdj.wip_supply_type,'WIP_SUPPLY',700) wip_supply_type,
wdj.class_code,
xxen_util.client_time(wdj.scheduled_start_date) scheduled_start_date,
xxen_util.client_time(wdj.scheduled_completion_date) scheduled_completion_date,
xxen_util.client_time(wdj.date_released) released_date,
xxen_util.client_time(wdj.date_completed) completed_date,
xxen_util.client_time(wdj.date_closed) closed_date,
decode(row_number() over (partition by we.wip_entity_id order by wo.operation_seq_num, wor.resource_seq_num),1,wdj.start_quantity) start_quantity,
decode(row_number() over (partition by we.wip_entity_id order by wo.operation_seq_num, wor.resource_seq_num),1,nullif(wdj.quantity_scrapped,0)) quantity_scrapped,
decode(row_number() over (partition by we.wip_entity_id order by wo.operation_seq_num, wor.resource_seq_num),1,nullif(wdj.quantity_completed,0)) quantity_completed,
decode(row_number() over (partition by we.wip_entity_id order by wo.operation_seq_num, wor.resource_seq_num),1,nullif(wdj.start_quantity-wdj.quantity_completed-wdj.quantity_scrapped,0)) quantity_remaining,
wdj.completion_subinventory,
bd.department_code,
wo.operation_seq_num operation_sequence,
bso.operation_code,
wo.description operation_description,
br.resource_code,
br.description resource_description,
&resource_columns
xxen_util.client_time(wo.date_last_moved) date_last_moved,
case when nvl(wor.resource_rank,1)=1 then wo.in_queue end in_queue,
case when nvl(wor.resource_rank,1)=1 then wo.running end running,
case when nvl(wor.resource_rank,1)=1 then wo.to_move end to_move,
case when nvl(wor.resource_rank,1)=1 then wo.rejected end rejected,
case when nvl(wor.resource_rank,1)=1 then wo.scrapped end scrapped,
case when nvl(wor.resource_rank,1)=1 then wo.completed end completed,
xxen_util.client_time(wo.first_unit_start_date) first_unit_start_date,
xxen_util.client_time(wo.last_unit_completion_date) last_unit_completion_date,
xxen_util.user_name(wo.last_updated_by) operation_last_updated_by,
xxen_util.user_name(we.created_by) created_by,
xxen_util.client_time(we.creation_date) creation_date,
haouv.name organization,
we.wip_entity_id
from
hr_all_organization_units_vl haouv,
mtl_parameters mp,
wip_entities we,
mtl_system_items_vl msiv,
mtl_planners mpl,
wip_discrete_jobs wdj,
wip_schedule_groups wsg,
csi_item_instances cii,
(
select ppa.project_id, ppa.segment1 project_number from pa_projects_all ppa union
select psm.project_id, psm.project_number from pjm_seiban_numbers psm
) ppa,
pa_tasks pt,
(
select
wo.wip_entity_id,
wo.organization_id,
wo.repetitive_schedule_id,
wo.operation_seq_num,
wo.department_id,
wo.standard_operation_id,
wo.description,
wo.date_last_moved,
nullif(wo.quantity_in_queue,0) in_queue,
nullif(wo.quantity_running,0) running,
nullif(wo.quantity_waiting_to_move,0) to_move,
nullif(wo.quantity_rejected,0) rejected,
nullif(wo.quantity_scrapped,0) scrapped,
nullif(wo.quantity_completed,0) completed,
nvl(wo.cumulative_scrap_quantity,0) cumulative_scrap_quantity,
wo.first_unit_start_date,
wo.last_unit_completion_date,
wo.last_updated_by
from
wip_operations wo
where
2=2
) wo,
bom_departments bd,
(select bso.* from bom_standard_operations bso where nvl(bso.operation_type,1)=1 and bso.line_id is null) bso,
(
select
x.*
from
(select row_number() over (partition by wor.wip_entity_id, wor.organization_id, wor.operation_seq_num, wor.repetitive_schedule_id order by wor.resource_seq_num) resource_rank, wor.* from wip_operation_resources wor) x
where
(x.resource_rank=1 or :show_resources='Y')
) wor,
bom_resources br,
oe_order_headers_all ooha,
oe_order_lines_all oola
where
1=1 and
we.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='INV') and
haouv.organization_id=mp.organization_id and
mp.organization_id=we.organization_id and
we.primary_item_id=msiv.inventory_item_id(+) and
we.organization_id=msiv.organization_id(+) and
msiv.planner_code=mpl.planner_code(+) and
msiv.organization_id=mpl.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
decode(wdj.maintenance_object_type,3,wdj.maintenance_object_id)=cii.instance_id(+) and
wdj.project_id=ppa.project_id(+) and
wdj.task_id=pt.task_id(+) and
wdj.wip_entity_id=wo.wip_entity_id(+) and
wdj.organization_id=wo.organization_id(+) and
wo.department_id=bd.department_id(+) and
wo.standard_operation_id=bso.standard_operation_id(+) and
wo.wip_entity_id=wor.wip_entity_id(+) and
wo.organization_id=wor.organization_id(+) and
wo.operation_seq_num=wor.operation_seq_num(+) and
wor.resource_id=br.resource_id(+) and
wdj.source_line_id=oola.line_id(+) and
oola.header_id=ooha.header_id(+)
order by
mp.organization_code,
we.creation_date desc,
wo.operation_seq_num,
wor.resource_seq_num |
