-
Notifications
You must be signed in to change notification settings - Fork 62
Expand file tree
/
Copy pathWIP Required Components.sql
More file actions
149 lines (147 loc) · 6.18 KB
/
WIP Required Components.sql
File metadata and controls
149 lines (147 loc) · 6.18 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
/*************************************************************************/
/* */
/* (c) 2010-2024 Enginatics GmbH */
/* www.enginatics.com */
/* */
/*************************************************************************/
-- Report Name: WIP Required Components
-- Description: Detailed project WIP report that lists discrete jobs and required components. The parameter 'Show Shortage List' can be used to show a shortage list of existing discrete jobs (similar to Oracle's 'Discrete Job Shortage Report'.
-- Excel Examle Output: https://www.enginatics.com/example/wip-required-components/
-- Library Link: https://www.enginatics.com/reports/wip-required-components/
-- Run Report: https://demo.enginatics.com/
select
ood.organization_code,
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,
xxen_util.meaning(msiv0.item_type,'ITEM_TYPE',3) assembly_item_type,
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,
xxen_util.meaning(msiv.item_type,'ITEM_TYPE',3) component_item_type,
muot.unit_of_measure_tl primary_uom,
-- quantities as per view wip_requirement_operations_v
wro.quantity_per_assembly,
wro.required_quantity,
decode(wro.quantity_issued, 0,to_number(null), wro.quantity_issued) quantity_issued,
decode((wro.required_quantity-wro.quantity_issued),0,
null,
decode(sign(wro.required_quantity),-1*sign(wro.quantity_issued),
(wro.required_quantity - wro.quantity_issued),
decode(sign(abs(wro.required_quantity) - abs(wro.quantity_issued)),-1,
null,
(wro.required_quantity-wro.quantity_issued)
)
)
) quantity_open,
decode(wip_picking_pub.quantity_allocated(wro.wip_entity_id, wro.operation_seq_num,wro.organization_id, wro.inventory_item_id, wro.repetitive_schedule_id, wro.quantity_issued),0,
to_number(null),
wip_picking_pub.quantity_allocated(wro.wip_entity_id, wro.operation_seq_num,wro.organization_id, wro.inventory_item_id, wro.repetitive_schedule_id, wro.quantity_issued)
) quantity_allocated,
wro.released_quantity quantity_released,
decode(mr.primary_reservation_quantity, 0,null, mr.primary_reservation_quantity) quantity_reserved,
--
moqd.on_hand inv_quantity_onhand,
decode(xxen_inv_sd.get_inv_qty(wro.inventory_item_id,wro.organization_id,'AVAILABLE',wro.supply_subinventory)
,0,to_number(null),xxen_inv_sd.get_inv_qty(wro.inventory_item_id,wro.organization_id,'AVAILABLE',wro.supply_subinventory)
) subinv_quantity_available,
decode(xxen_inv_sd.get_inv_qty(wro.inventory_item_id,wro.organization_id,'ONHAND',wro.supply_subinventory)
,0,to_number(null),xxen_inv_sd.get_inv_qty(wro.inventory_item_id,wro.organization_id,'AVAILABLE',wro.supply_subinventory)
) subinv_quantity_onhand,
--
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,
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
we.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) and
ood.organization_id=we.organization_id and
wro.wip_entity_id=mr.demand_source_header_id(+) and
wro.organization_id=mr.organization_id(+) and
mr.demand_source_type_id(+)=5 and
wro.operation_seq_num=mr.demand_source_line_id(+) and
wro.inventory_item_id=mr.inventory_item_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