1 PACKAGE BODY wip_flow_completion_filter AS
2 /* $Header: wipfwocb.pls 120.0 2005/05/25 07:46:47 appldev noship $ */
3
4 PROCEDURE retrieve_schedules (i_where IN VARCHAR2, i_default_sub IN VARCHAR2,
5 i_default_loc IN NUMBER,
6 i_org_locator_control IN NUMBER,
7 i_qty_retrieve IN NUMBER,
8 i_num_records IN NUMBER,
9 o_result OUT NOCOPY ret_sch_t,
10 o_restrict_error OUT NOCOPY BOOLEAN,
11 o_lot_serial_error OUT NOCOPY BOOLEAN,
12 o_error_num OUT NOCOPY NUMBER,
13 o_error_msg OUT NOCOPY VARCHAR2) IS
14 l_cursor_id NUMBER;
15 l_stmt VARCHAR2(10000);
16 l_dummy INTEGER;
17 l_wip_entity_id NUMBER;
18 l_primary_item_id NUMBER;
19 l_completion_subinventory VARCHAR2(10);
20 l_completion_locator NUMBER;
21 l_alt_routing_designator VARCHAR2(10);
22 l_org_id NUMBER;
23 l_project_id NUMBER;
24 l_task_id NUMBER;
25 l_ret NUMBER;
26 i NUMBER;
27 l_item_locator_control NUMBER;
28 l_restrict_sub NUMBER;
29 l_restrict_loc NUMBER;
30 l_serial_control NUMBER;
31 l_lot_control NUMBER;
32 l_sub_locator_control NUMBER;
33 l_locator_control NUMBER;
34 l_cnt NUMBER;
35 l_cur_restrict_error BOOLEAN;
36 l_cur_lot_serial_error BOOLEAN;
37 l_tot_qty NUMBER;
38 l_cur_qty NUMBER;
39
40 BEGIN
41
42 l_stmt := 'SELECT primary_item_id,
43 wip_entity_id,
44 organization_id,
45 completion_subinventory,
46 completion_locator_id,
47 alternate_routing_designator,
48 project_id,
49 task_id,
50 planned_quantity-NVL(quantity_completed,0)-NVL(quantity_scrapped,0)
51 FROM wip_flow_schedules wfs
52 WHERE (planned_quantity - NVL(quantity_completed,0) -
53 NVL(quantity_scrapped,0)) > 0
54 AND status = 1
55 and ( wfs.demand_source_header_id is null or
56 exists ( select 1 from wip_open_demands_v wodv
57 where to_char(wodv.demand_source_line_id) = wfs.demand_source_line
58 and wodv.demand_source_header_id = wfs.demand_source_header_id)
59 ) ';
60 /* bug 3899971, not fetching the schedules linked to orders lines not booked */
61 l_stmt := l_stmt || ' AND ' || i_where ;
62 l_stmt := l_stmt || ' order by build_sequence, schedule_number ';
63
64
65 l_cursor_id := dbms_sql.open_cursor;
66 dbms_sql.parse(l_cursor_id, l_stmt, dbms_sql.v7);
67 dbms_sql.define_column(l_cursor_id, 1, l_primary_item_id);
68 dbms_sql.define_column(l_cursor_id, 2, l_wip_entity_id);
69 dbms_sql.define_column(l_cursor_id, 3, l_org_id);
70 dbms_sql.define_column(l_cursor_id, 4, l_completion_subinventory, 10);
71 dbms_sql.define_column(l_cursor_id, 5, l_completion_locator);
72 dbms_sql.define_column(l_cursor_id, 6, l_alt_routing_designator, 10);
73 dbms_sql.define_column(l_cursor_id, 7, l_project_id);
74 dbms_sql.define_column(l_cursor_id, 8, l_task_id);
75 dbms_sql.define_column(l_cursor_id, 9, l_cur_qty);
76 l_dummy := dbms_sql.execute(l_cursor_id);
77
78 -- This will be set to true if the item has resricted subinventory/locator,
79 -- and the subinventory/locator given from the default or routing
80 -- doesn't match. That record will not be returned in the result table.
81 o_restrict_error := false;
82
83 -- This will be set to true if the item has lot/serial controlled,
84 -- That record will not be returned in the result table.
85 o_lot_serial_error := false;
86
87 -- Index of result table
88 i := 1;
89
90 -- Total quantity eligible to be completed.
91 l_tot_qty := 0;
92
93 WHILE dbms_sql.fetch_rows(l_cursor_id) > 0 LOOP
94 -- This is to indicate resricted subinventory/locator error for
95 -- the current record.
96 l_cur_restrict_error := false;
97
98 -- This is to indicate lot/serial control error for the current record.
99 l_cur_lot_serial_error := false;
100
101 dbms_sql.column_value(l_cursor_id,1, l_primary_item_id);
102 dbms_sql.column_value(l_cursor_id,2, l_wip_entity_id);
103 dbms_sql.column_value(l_cursor_id,3, l_org_id);
104 dbms_sql.column_value(l_cursor_id,4, l_completion_subinventory);
105 dbms_sql.column_value(l_cursor_id,5, l_completion_locator);
106 dbms_sql.column_value(l_cursor_id,6, l_alt_routing_designator);
107 dbms_sql.column_value(l_cursor_id,7, l_project_id);
108 dbms_sql.column_value(l_cursor_id,8, l_task_id);
109 dbms_sql.column_value(l_cursor_id,9, l_cur_qty);
110
111
112 -- Derive the sub/loc from the routing if none is specified in
113 -- the flow schedule.
114 if (l_completion_subinventory is NULL) then
115 l_ret := wip_flow_derive.routing_completion_sub_loc(
116 l_completion_subinventory,
117 l_completion_locator,
118 l_primary_item_id,
119 l_org_id,
120 l_alt_routing_designator);
121
122 l_ret := wip_flow_derive.completion_loc(
123 l_completion_locator,
124 l_primary_item_id,
125 l_org_id,
126 l_alt_routing_designator,
127 l_project_id,
128 l_task_id,
129 l_completion_subinventory,
130 NULL);
131 end if;
132
133 -- Error out if no subinventory in the routing, and no default is given.
134 -- Otherwise set it to default sub
135 if (l_completion_subinventory IS NULL) then
136 if (i_default_sub IS NOT NULL) then
137 l_completion_subinventory := i_default_sub;
138 else
139 o_error_num := -1;
140 return;
141 end if;
142 end if;
143
144 select location_control_code,
145 restrict_locators_code, restrict_subinventories_code,
146 serial_number_control_code, lot_control_code
147 into l_item_locator_control,
148 l_restrict_loc, l_restrict_sub,
149 l_serial_control, l_lot_control
150 from mtl_system_items
151 where inventory_item_id = l_primary_item_id
152 and organization_id = l_org_id;
153
154 -- Check if the item is under lot/serial control
155 -- If it is, set the lot_serial_error flag.
156 -- We will ignore this record not to be included in the
157 -- result, but will continue to process the rest
158 if (l_serial_control not in (1,6) or l_lot_control <> 1) then
159 o_lot_serial_error := true;
160 l_cur_lot_serial_error := true;
161 end if;
162
163 -- Make sure that the sub is valid if the item uses restricted
164 -- subinventory. If it's invalid, set the restricted error flag.
165 -- In this case, we keep continue processing for other records,
166 -- while the current record with restriction error will not
167 -- be returned.
168 if (l_restrict_sub = 1) then
169 select count(*)
170 into l_cnt
171 from mtl_item_sub_ast_trk_val_v
172 where organization_id = l_org_id
173 and inventory_item_id = l_primary_item_id
174 and secondary_inventory_name = l_completion_subinventory;
175 if (l_cnt = 0) then
176 o_restrict_error := true;
177 l_cur_restrict_error := true;
178 end if;
179 end if;
180
181 select locator_type
182 into l_sub_locator_control
183 from mtl_secondary_inventories
184 where organization_id = l_org_id
185 and secondary_inventory_name = l_completion_subinventory;
186
187 l_locator_control := loc_control(i_org_locator_control,
188 l_sub_locator_control,
189 l_item_locator_control,
190 l_restrict_loc);
191
192 -- If it's locator controled, error out if no locator specified and
193 -- no default is given
194 -- Otherwise set it to default locator
195 if (l_locator_control <> 1 AND l_completion_locator is NULL) then
196 if (i_default_loc is NULL) then
197 o_error_num := -1;
198 return;
199 else
200 l_completion_locator := i_default_loc;
201 end if;
202 end if;
203
204 -- As in subinventory, we also want to make sure that if the item
205 -- has restricted locator, the locator is a valid one.
206 -- If it's not, set the restricted error flag.
207 if (l_locator_control = 2 AND l_restrict_loc = 1) then
208 select count(*)
209 into l_cnt
210 from mtl_secondary_locators
211 where inventory_item_id = l_primary_item_id
212 and organization_id = l_org_id
213 and subinventory_code = l_completion_subinventory
214 and secondary_locator = l_completion_locator;
215 if (l_cnt = 0) then
216 o_restrict_error := true;
217 l_cur_restrict_error := true;
218 end if;
219 end if;
220
221 -- If the restricted error flag and lot/serial error for the current record
222 -- is not set, put this record in the result table.
223 if (l_cur_restrict_error = false and l_cur_lot_serial_error = false) then
224 o_result(i).wip_entity_id := l_wip_entity_id;
225 o_result(i).completion_subinventory := l_completion_subinventory;
226 o_result(i).completion_locator_id := l_completion_locator;
227
228 -- Only retrieve up to i_qty_retrieve if i_qty_retrieve is not null
229 if (i_qty_retrieve IS NOT NULL and i_qty_retrieve <= (l_tot_qty+l_cur_qty)) then
230 o_result(i).quantity := i_qty_retrieve - l_tot_qty;
231 exit;
232 else
233 o_result(i).quantity := l_cur_qty;
234 end if;
235
236 l_tot_qty := l_tot_qty + l_cur_qty;
237 i := i + 1;
238
239 if (i_num_records IS NOT NULL and i > i_num_records) then
240 exit;
241 end if;
242
243 end if;
244
245
246 END LOOP;
247 o_error_num := 0;
248
249 END retrieve_schedules;
250
251 -- This function return the locator control.
252 function loc_control(org_control IN number,
253 sub_control IN number,
254 item_control IN number default NULL,
255 restrict_flag IN Number default NULL)
256 return number is
257 locator_control number;
258 begin
259
260 if (org_control = 1) then
261 locator_control := 1;
262 elsif (org_control = 2) then
263 locator_control := 2;
264 elsif (org_control = 3) then
265 locator_control := 3;
266 if (restrict_flag = 1) then
267 locator_control := 2;
268 end if;
269 elsif (org_control = 4) then
270 if (sub_control = 1) then
271 locator_control := 1;
272 elsif (sub_control = 2) then
273 locator_control := 2;
274 elsif (sub_control = 3) then
275 locator_control := 3;
276 if (restrict_flag = 1) then
277 locator_control := 2;
278 end if;
279 elsif (sub_control = 5) then
280 if (item_control = 1) then
281 locator_control := 1;
282 elsif (item_control = 2) then
283 locator_control := 2;
284 elsif (item_control = 3) then
285 locator_control := 3;
286 if (restrict_flag = 1) then
287 locator_control := 2;
288 end if;
289 elsif (item_control IS NULL) then
290 locator_control := sub_control;
291 else
292 return -1;
293 end if;
294 else
295 return -1;
296 end if;
297 else
298 return -1;
299 end if;
300 return locator_control;
301 end loc_control;
302
303 END wip_flow_completion_filter;
[Home] [Help]