Reports 2017-11-18T12:27:27+00:00

WIP Entities

Description
Categories: Enginatics, Manufacturing
WIP Jobs

select
haou.name organization,
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,
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,
ppa.project_number,
pt.task_number,
wdj.source_code,
wdj.source_line_id,
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,
wdj.scheduled_start_date,
wdj.scheduled_completion_date,
wdj.start_quantity,
wdj.quantity_completed,
wdj.completion_subinventory,
we.wip_entity_id,
we.creation_date,
xxen_util.user_name(we.created_by) created_by
from
hr_all_organization_units haou,
mtl_parameters mp,
wip_entities we,
mtl_system_items_vl msiv,
mtl_planners mpl,
wip_discrete_jobs wdj,
wip_schedule_groups wsg,
(
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,
&xrrpv_table
pa_tasks pt
where
1=1 and
haou.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
wdj.project_id=ppa.project_id(+) and
wdj.task_id=pt.task_id(+)
order by
we.creation_date desc

Parameter Name SQL text Validation
organization_id|inventory_item_id
(
select distinct
dbms_lob.substr(xrrpv.value,instr(xrrpv.value,'|')-1) organization_id,
dbms_lob.substr(xrrpv.value,20,instr(xrrpv.value,'|')+1) inventory_item_id
from
xxen_report_run_param_values xrrpv
where
xrrpv.run_id=:run_id
) xrrpv,
Planner Code
msiv.planner_code=:planner_code
LOV
Schedule Group
wsg.schedule_group_name=:schedule_group
LOV
Item
msiv.concatenated_segments=:item
LOV
Job
we.wip_entity_name=:job_name
LOV
Job Status
wdj.status_type in (
select
to_number(flv.lookup_code) status
from
fnd_lookup_values flv
where
flv.meaning=:job_status and
flv.lookup_type='WIP_JOB_STATUS' and
flv.language=userenv('lang') and
flv.view_application_id=700 and
flv.security_group_id=0
)
LOV
Open only
wdj.status_type in (1,2,3,4,5)
LOV
Organization
we.organization_id=:organization_id
LOV Oracle
organization_id|inventory_item_id
wdj.status_type in (1,3,6,8,9,10,11,13,14,15,16,17) and
xrrpv.organization_id=msiv.organization_id and
xrrpv.inventory_item_id=msiv.inventory_item_id