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

WIP Required Components

Description
Categories: Enginatics, Manufacturing
Discrete jobs and required components

select
ood.organization_name organization,
ood.organization_code,
we.wip_entity_name job,
xxen_util.meaning(wdj.status_type,'WIP_JOB_STATUS',700) wip_status,
wl.line_code,
msiv0.segment1 assembly,
msiv0.description assembly_description,
bd.department_code department,
bd.description department_desc,
wdj.scheduled_start_date,
wdj.scheduled_completion_date,
wdj.start_quantity job_start_qty,
wsg.schedule_group_name,
wdj.build_sequence,
bcb.item_num item_sequence,
wro.operation_seq_num,
msiv.concatenated_segments component,
msiv.description component_description,
muot.unit_of_measure_tl primary_uom,
wro.quantity_per_assembly,
wro.required_quantity,
wro.quantity_issued,
greatest(wro.required_quantity-wro.quantity_issued,0) quantity_open,
nvl(miqv.quantity,0) quantity_on_hand,
mso.segment1 sales_order,
ppa.segment1 project,
wro.date_required,
xxen_util.meaning(wro.wip_supply_type,'WIP_SUPPLY',700) supply_type,
xxen_util.meaning(msiv.atp_flag,'ATP_FLAG',3) atp_flag,
xxen_util.meaning(wro.mrp_net_flag,'SYS_YES_NO',700) mrp_net_flag,
wro.supply_subinventory supply_subinv,
milk.concatenated_segments supply_locator,
xxen_util.meaning(msiv.planning_make_buy_code,'MTL_PLANNING_MAKE_BUY',700) make_buy,
msiv.planner_code planner_code,
mp.description planner,
ppx.full_name buyer,
decode(miqv.subinventory_code,nvl(wro.supply_subinventory,'-1'),decode(nvl(miqv.locator_id,-1),nvl(wro.supply_locator_id,-1),1,0),0) supply_flag,
decode (msi.availability_type,1,1,0) availability_type,
we.wip_entity_id
from
org_organization_definitions ood,
wip_entities we,
mtl_reservations mr,
mtl_sales_orders mso,
wip_discrete_jobs wdj,
wip_lines wl,
mtl_system_items_vl msiv0,
bom_departments bd,
wip_schedule_groups wsg,
pa_projects_all ppa,
wip_requirement_operations wro,
bom_components_b bcb,
mtl_system_items_vl msiv,
mtl_units_of_measure_tl muot,
mtl_item_locations_kfv milk,
mtl_planners mp,
per_people_x ppx,
mtl_item_quantities_view miqv,
mtl_secondary_inventories msi
where
1=1 and
ood.organization_id=we.organization_id and
we.wip_entity_id=mr.supply_source_header_id(+) and
mr.demand_source_header_id=mso.sales_order_id(+) and
we.wip_entity_id=wdj.wip_entity_id and
wdj.line_id=wl.line_id(+) and
wdj.primary_item_id=msiv0.inventory_item_id(+) and
wdj.organization_id=msiv0.organization_id(+) and
wro.department_id=bd.department_id(+) and
wdj.schedule_group_id=wsg.schedule_group_id(+) and
wdj.project_id=ppa.project_id(+) and
wdj.wip_entity_id=wro.wip_entity_id and
wdj.organization_id=wro.organization_id and
wro.component_sequence_id=bcb.component_sequence_id(+) and
wro.inventory_item_id=msiv.inventory_item_id and
wro.organization_id=msiv.organization_id and
msiv.primary_uom_code=muot.uom_code and
muot.language=userenv('lang') and
wro.supply_locator_id=milk.inventory_location_id(+) and
wro.organization_id=milk.organization_id(+) and
msiv.planner_code=mp.planner_code(+) and
msiv.organization_id=mp.organization_id(+) and
msiv.buyer_id=ppx.person_id(+) and
wro.inventory_item_id=miqv.inventory_item_id(+) and
wro.organization_id=miqv.organization_id(+) and
miqv.subinventory_code=msi.secondary_inventory_name(+) and
miqv.organization_id=msi.organization_id(+)
order by
ood.organization_code,
we.wip_entity_name,
wro.operation_seq_num,
msiv.concatenated_segments

Parameter Name SQL text Validation
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
Date Required to
wro.date_required<:date_required_to+1
Date
Show Shortage List
we.entity_type in (1,5) and
wdj.status_type in (1,3,4,6) and
wro.wip_supply_type<>6 and
wro.required_quantity>0 and
wro.required_quantity>wro.quantity_issued
LOV Oracle
Show Phantom Components
wro.wip_supply_type<>6
LOV Oracle
Operating Unit
ood.operating_unit in (select haou.organization_id from hr_all_organization_units haou where haou.name=:operating_unit)
LOV
Organization
ood.organization_code=:organization_code
LOV