Reports2017-11-18T12:27:27+00:00

WIP Required Components

Description
Categories: Enginatics, Manufacturing
Discrete jobs and required components.
Parameter 'Show Shortage List' can be used to show a shortage list of existing discrete jobs (similar to Oracle's 'Discrete Job Shortage Report')

select
mso.segment1 sales_order,
ppa.segment1 project,
we.wip_entity_name job,
xxen_util.meaning(wdj.status_type,'WIP_JOB_STATUS',700) wip_status,
wl.line_code,
msiv0.concatenated_segments 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,
moqd.on_hand,
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,
ood.organization_name organization,
ood.organization_code,
we.wip_entity_id,
wro.inventory_item_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,
(
select
sum(moqd.primary_transaction_quantity) on_hand,
moqd.organization_id,
moqd.inventory_item_id
from
mtl_onhand_quantities_detail moqd,
mtl_secondary_inventories msi
where
moqd.subinventory_code=msi.secondary_inventory_name and
moqd.organization_id=msi.organization_id and
msi.availability_type=1
group by
moqd.organization_id,
moqd.inventory_item_id
) moqd
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=moqd.inventory_item_id(+) and
wro.organization_id=moqd.organization_id(+)
order by
ood.organization_code,
we.wip_entity_name,
wro.operation_seq_num,
msiv.concatenated_segments

Parameter Name SQL text Validation
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
Project
ppa.segment1=:project_number
LOV
Component
msiv.concatenated_segments like :component
LOV
Assembly
msiv0.concatenated_segments like :assembly
LOV
Job
we.wip_entity_name=:job_name
LOV
Organization Code
we.organization_id=:organization_id
LOV Oracle
MRP Net
wro.mrp_net_flag=:net_flag
LOV Oracle
Show Phantom Components
wro.wip_supply_type<>6
LOV Oracle
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
Date Required to
wro.date_required<:date_required_to+1
Date

By continuing to use the site, you agree to the use of cookies. Accept