[Home] [Help]
PACKAGE BODY: APPS.WIP_WS_SHORTAGE
Source
1 PACKAGE BODY WIP_WS_SHORTAGE AS
2 /* $Header: wipwsshb.pls 120.18.12020000.3 2012/11/05 21:43:18 dalu ship $ */
3
4
5
6
7
8
9
10 /*
11 * UTIL PROCEDURE This procedure converts time provided in hour min and secs into secs
12 */
13 FUNCTION get_time_in_secs(hour NUMBER, minutes NUMBER, sec NUMBER) RETURN NUMBER IS
14 BEGIN
15 return(nvl(hour,0)*24*60 + nvl(minutes,0)*60 + nvl(sec,0));
16 END get_time_in_secs;
17
18
19 /*
20 * UTIL PROCEDURE This procedure converts time provided in date to secs
21 */
22 FUNCTION get_time_in_secs (p_date DATE) return NUMBER IS
23 BEGIN
24 --get the time part till mins only
25 return get_time_in_secs(to_number(to_char(p_date,'HH24')),
26 to_number(to_char(p_date, 'MI')), 0);
27 END get_time_in_secs;
28
29
30 /*
31 * This procedure get the component shortage preference values for a given org and stores in package variables
32 * Returns Y if preference exist for an org, otherwise N
33 */
34 PROCEDURE get_org_comp_calc_param(
35 p_org_id IN NUMBER,
36 x_pref_exists OUT NOCOPY VARCHAR2) IS
37
38 l_returnStatus varchar2(1);
39 l_params wip_logger.param_tbl_t;
40 l_row_seq_num NUMBER;
41 l_cutoff_hr NUMBER;
42 l_cutoff_min NUMBER;
43 l_dtl_row_seq_num NUMBER;
44 l_comp_calc_type NUMBER;
45
46 CURSOR cat_set_id_csr IS
47 select wpv.attribute_value_code
48 from wip_preference_values wpv
49 where wpv.preference_id = g_pref_id_comp_short
50 and wpv.level_id = g_pref_level_id_site
51 and wpv.attribute_name = g_pref_val_comp_type_cset_att;
52
53 BEGIN
54 x_pref_exists := 'Y';
55 if (g_logLevel <= wip_constants.trace_logging) then
56 l_params(1).paramName := 'p_org_id';
57 l_params(1).paramValue := p_org_id;
58 wip_logger.entryPoint(p_procName => 'WIP_WS_SHORTAGE.get_org_comp_calc_param',
59 p_params => l_params,
60 x_returnStatus => l_returnStatus);
61 if(l_returnStatus <> fnd_api.g_ret_sts_success) then
62 raise fnd_api.g_exc_unexpected_error;
63 end if;
64 end if;
65
66 for c_cat_set_id_csr in cat_set_id_csr loop
67 g_org_comp_calc_rec.category_set_id := c_cat_set_id_csr.attribute_value_code;
68 end loop;
69
70 l_row_seq_num := wip_ws_util.get_multival_pref_seq(
71 g_pref_id_comp_short, g_pref_level_id_site, g_pref_val_mast_org_att, to_char(p_org_id));
72
73 if(l_row_seq_num is null) then
74 x_pref_exists := 'N';
75 return;
76 end if;
77
78 g_org_comp_calc_rec.org_id := p_org_id;
79 g_org_comp_calc_rec.shortage_calc_level := wip_ws_util.get_multival_pref_val_code(
80 g_pref_id_comp_short, g_pref_level_id_site, l_row_seq_num, g_pref_val_calclevel_att);
81 g_org_comp_calc_rec.inc_expected_rcpts := wip_ws_util.get_multival_pref_val_code(
82 g_pref_id_comp_short, g_pref_level_id_site, l_row_seq_num, g_pref_val_inc_rcpts_att);
83 g_org_comp_calc_rec.inc_released_jobs := wip_ws_util.get_multival_pref_val_code(
84 g_pref_id_comp_short, g_pref_level_id_site, l_row_seq_num, g_pref_val_inc_release_att);
85 g_org_comp_calc_rec.inc_unreleased_jobs := wip_ws_util.get_multival_pref_val_code(
86 g_pref_id_comp_short, g_pref_level_id_site, l_row_seq_num, g_pref_val_inc_unreleased_att);
87 g_org_comp_calc_rec.inc_onhold_jobs := wip_ws_util.get_multival_pref_val_code(
88 g_pref_id_comp_short, g_pref_level_id_site, l_row_seq_num, g_pref_val_inc_onhold_att);
89
90 if(g_org_comp_calc_rec.inc_expected_rcpts = g_pref_val_calclevel_org) then
91 g_org_comp_calc_rec.supply_cutoff_hr := wip_ws_util.get_multival_pref_val_code(
92 g_pref_id_comp_short, g_pref_level_id_site, l_row_seq_num, g_pref_val_cutoff_hr_att );
93 g_org_comp_calc_rec.supply_cutoff_min := wip_ws_util.get_multival_pref_val_code(
94 g_pref_id_comp_short, g_pref_level_id_site, l_row_seq_num, g_pref_val_cutoff_min_att );
95 g_org_comp_calc_rec.supply_cutoff_time_in_sec := get_time_in_secs(g_org_comp_calc_rec.supply_cutoff_hr, g_org_comp_calc_rec.supply_cutoff_min, 0);
96 end if;
97
98 /* Finding the critical components has moved to separate procedures
99 --now find out the components to be included in calculations
100 --User can setup three types of preferences
101 --preference attribute "type", possible values are: 1(All), 2(Item), 3(category)
102 --first find the org seq number
103
104 l_dtl_row_seq_num := wip_ws_util.get_multival_pref_seq(
105 g_pref_id_comp_short, g_pref_level_id_site, g_pref_val_dtl_org_att, to_char(p_org_id));
106
107
108 l_comp_calc_type := wip_ws_util.get_multival_pref_val_code(
109 g_pref_id_comp_short, g_pref_level_id_site, l_dtl_row_seq_num, g_pref_val_inc_onhold_att);
110 */
111
112 /*
113 --test code, remove after actual implementation
114 g_org_comp_calc_rec.org_id := 207;
115 g_org_comp_calc_rec.shortage_calc_level := 1;
116 g_org_comp_calc_rec.inc_expected_rcpts := 2;
117 g_org_comp_calc_rec.supply_cutoff_hr := null;
118 g_org_comp_calc_rec.supply_cutoff_min := null;
119 g_org_comp_calc_rec.inc_released_jobs := 1;
120 g_org_comp_calc_rec.inc_unreleased_jobs := 1;
121 g_org_comp_calc_rec.inc_onhold_jobs := 2;
122 */
123 if (g_logLevel <= wip_constants.trace_logging) then
124 wip_logger.exitPoint(p_procName => 'WIP_WS_SHORTAGE.get_org_comp_calc_param',
125 p_procReturnStatus => l_returnStatus,
126 p_msg => 'Request processed successfully!',
127 x_returnStatus => l_returnStatus);
128 end if;
129
130 EXCEPTION
131 WHEN OTHERS THEN
132 if (g_logLevel <= wip_constants.trace_logging) then
133 wip_logger.exitPoint(p_procName => 'WIP_WS_SHORTAGE.get_org_comp_calc_param',
134 p_procReturnStatus => l_returnStatus,
135 p_msg => 'unexpected error: ' || SQLERRM,
136 x_returnStatus => l_returnStatus);
137 end if;
138
139 END get_org_comp_calc_param;
140
141
142 /*
143 * This procedure gets the end time for last shift
144 */
145 FUNCTION get_period_end_time(p_org_id NUMBER) RETURN DATE IS
146 l_first_work_day DATE;
147 l_last_shift_end_time DATE;
148 CURSOR shift_time_csr IS
149 select bsd.seq_num || '.' || bsd.shift_num shift_id,
150 wip_ws_util.get_appended_date( bsd.shift_date, t.from_time) from_date,
151 wip_ws_util.get_appended_date( bsd.shift_date, t.to_time) to_date,
152 t.shift_num,
153 bsd.seq_num,
154 wip_ws_util.get_shift_info_for_display(mp.organization_id, bsd.seq_num, t.shift_num) as display
155 from mtl_parameters mp, bom_shift_dates bsd,
156 (select bst.calendar_code,
157 bst.shift_num,
158 min(bst.from_time) from_time,
159 max(decode(sign(bst.to_time - bst.from_time), -1, 24*60*60, 0) + bst.to_time) to_time
160 from bom_shift_times bst
161 group by bst.calendar_code, bst.shift_num ) t
162 where mp.organization_id = p_org_id
163 and mp.calendar_code = bsd.calendar_code
164 and bsd.calendar_code = t.calendar_code
165 and bsd.shift_num = t.shift_num
166 and bsd.exception_set_id = -1
167 and bsd.shift_date + t.to_time / (24*60*60) > sysdate
168 and bsd.shift_date between l_first_work_day and wip_ws_util.get_next_work_date_by_org_id(p_org_id,
169 wip_ws_util.get_next_work_date_by_org_id(p_org_id,l_first_work_day)) --fix bug 9484419
170 and bsd.seq_num is not null
171 order by to_date; --fix bug 9484419
172
173 BEGIN
174 --:1 - Org_id
175 --:2 - null (code will assume sysdate)
176 l_first_work_day := wip_ws_util.get_first_workday(p_org_id, null, null);
177
178 for c_shift_time_csr in shift_time_csr loop
179 l_last_shift_end_time := c_shift_time_csr.to_date;
180 end loop;
181
182 return l_last_shift_end_time;
183
184 END get_period_end_time;
185
186
187 /*
188 * This procedure returns the string for applicable job statuses
189 */
190 FUNCTION get_pref_job_statuses RETURN VARCHAR2 IS
191 status_str VARCHAR2(240) := null;
192 BEGIN
193 if(g_org_comp_calc_rec.inc_released_jobs = 1) then
194 status_str := to_char(wip_constants.RELEASED);
195 end if;
196
197 if(g_org_comp_calc_rec.inc_unreleased_jobs = 1) then
198 if(status_str is not null) then
199 status_str := status_str|| ' , ';
200 end if;
201 status_str := status_str || to_char(wip_constants.UNRELEASED);
202 end if;
203
204 if(g_org_comp_calc_rec.inc_onhold_jobs = 1) then
205 if(status_str is not null) then
206 status_str := status_str|| ' , ';
207 end if;
208 status_str := status_str || to_char(wip_constants.HOLD);
209 end if;
210 return status_str;
211 END get_pref_job_statuses ;
212
213
214 /*
215 * This procedure returns the string for applicable job types - right now it includes only standard jobs
216 */
217 FUNCTION get_job_types RETURN VARCHAR2 IS
218 job_type_str VARCHAR2(240) := null;
219 BEGIN
220 job_type_str := to_char(wip_constants.STANDARD);
221 return job_type_str;
222 END get_job_types;
223
224
225 /*
226 * This procedure returns the string for all the component category selected in preferences for this org
227 */
228 FUNCTION get_pref_comp_cat(p_org_id NUMBER) return VARCHAR2 IS
229 cat_string VARCHAR2(1048);
230 CURSOR pref_cat_csr IS
231 select wpv.attribute_value_code
232 from wip_preference_values wpv
233 where wpv.preference_id = g_pref_id_comp_short
234 and wpv.level_id = g_pref_level_id_site
235 and wpv.attribute_name = g_pref_val_comp_type_cat_att
236 and wpv.sequence_number in (
237 select wpv1.sequence_number
238 from wip_preference_values wpv1
239 where wpv1.preference_id = g_pref_id_comp_short
240 and wpv1.level_id = g_pref_level_id_site
241 and wpv1.attribute_name = g_pref_val_comp_type_att
242 and wpv1.attribute_value_code = to_char(g_pref_val_comp_type_cat)
243 and wpv1.sequence_number in (
244 select wpv2.sequence_number
245 from wip_preference_values wpv2
246 where wpv2.preference_id = g_pref_id_comp_short
247 and wpv2.level_id = g_pref_level_id_site
248 and wpv2.attribute_name = g_pref_val_dtl_org_att
249 and wpv2.attribute_value_code = to_char(p_org_id)));
250 BEGIN
251 for c_pref_cat_csr in pref_cat_csr loop
252 if cat_string is not null then
253 cat_string := cat_string || ',';
254 end if;
255 cat_string := cat_string || c_pref_cat_csr.attribute_value_code;
256 end loop;
257 return cat_string;
258 END get_pref_comp_cat;
259
260
261 /*
262 * This procedure returns the string for all the components selected in preference for this org
263 */
264 FUNCTION get_pref_comp_id(p_org_id NUMBER) return VARCHAR2 IS
265 comp_string VARCHAR2(1048);
266 CURSOR pref_itm_csr IS
267 select wpv.attribute_value_code
268 from wip_preference_values wpv
269 where wpv.preference_id = g_pref_id_comp_short
270 and wpv.level_id = g_pref_level_id_site
271 and wpv.attribute_name = g_pref_val_comp_type_item_att
272 and wpv.sequence_number in (
273 select wpv1.sequence_number
274 from wip_preference_values wpv1
275 where wpv1.preference_id = g_pref_id_comp_short
276 and wpv1.level_id = g_pref_level_id_site
277 and wpv1.attribute_name = g_pref_val_comp_type_att
278 and wpv1.attribute_value_code = to_char(g_pref_val_comp_type_item)
279 and wpv1.sequence_number in (
280 select wpv2.sequence_number
281 from wip_preference_values wpv2
282 where wpv2.preference_id = g_pref_id_comp_short
283 and wpv2.level_id = g_pref_level_id_site
284 and wpv2.attribute_name = g_pref_val_dtl_org_att
285 and wpv2.attribute_value_code = to_char(p_org_id)));
286
287 BEGIN
288 wip_ws_util.trace_log('WIPWSSHB:get_pref_comp_id:Execution cursor to get item ids');
289 for c_pref_itm_csr in pref_itm_csr loop
290 if comp_string is not null then
291 comp_string := comp_string || ',';
292 end if;
293 comp_string := comp_string || 'to_number(c_pref_itm_csr.attribute_value_code)';
294 end loop;
295
296
297 /*
298 --TODO: test code, remove after testing
299 if(comp_string is null) then
300 comp_string := '249';
301 end if;
302 */
303 wip_ws_util.trace_log('WIPWSSHB:get_pref_comp_id: item id string='||comp_string);
304 return comp_string;
305
306 END get_pref_comp_id;
307
308
309 /*
310 * This procedure finds out the onhand quantity (available to transact) of an item in org or subinv based on parameter
311 */
312 FUNCTION get_subinv_component_onhand(
313 p_org_id NUMBER,
314 p_subinv_code VARCHAR2 ,
315 p_component_id NUMBER)RETURN NUMBER IS
316
317 l_is_revision_control boolean;
318 l_is_lot_control boolean;
319 l_is_serial_control boolean;
320 l_lot_control_code number;
321 l_revision_control_code number;
322 l_serial_control_code number;
323
324 x_qoh number;
325 x_rqoh number;
326 x_qr number;
327 x_qs number;
328 x_att number;
329 x_atr number;
330
331 x_return_status varchar2(2);
332 x_msg_count number;
333 x_msg_data varchar2(256);
334
335 CURSOR item_ctrl_csr IS
336 select msi.revision_qty_control_code,
337 msi.lot_control_code,
338 msi.serial_number_control_code
339 from mtl_system_items_b msi
340 where msi.organization_id = p_org_id
341 and msi.inventory_item_id = p_component_id;
342
343 BEGIN
344 wip_ws_util.trace_log('WIPWSSHB.get_subinv_component_onhand: Begin '||
345 '; p_org_id '||p_org_id||
346 '; p_subinv_code '||p_subinv_code||
347 '; p_component_id '||p_component_id);
348
349 for c_item_ctrl_csr in item_ctrl_csr loop
350 l_revision_control_code := c_item_ctrl_csr.revision_qty_control_code;
351 l_lot_control_code := c_item_ctrl_csr.lot_control_code;
352 l_serial_control_code := c_item_ctrl_csr.serial_number_control_code;
353 end loop;
354
355 --bug 7045337 since lot number is passed as null, l_is_lot_control should be passed as false
356 --based on Inv team's suggestion passing null for l_is_revision_control and l_is_serial_control
357 --also since we are not calculating att at revision/serial
358 /**************
359 if ( l_lot_control_code = WIP_CONSTANTS.LOT ) then
360 l_is_lot_control := true;
361 else
362 l_is_lot_control := false;
363 end if;
364
365 if( l_revision_control_code = WIP_CONSTANTS.REV ) then
366 l_is_revision_control := true;
367 else
368 l_is_revision_control := false;
369 end if;
370
371 if( l_serial_control_code in (WIP_CONSTANTS.FULL_SN, WIP_CONSTANTS.DYN_RCV_SN) ) then
372 l_is_serial_control := true;
373 else
374 l_is_serial_control := false;
375 end if;
376 **************/
377
378 l_is_lot_control := false;
379 l_is_revision_control := false;
380 l_is_serial_control := false;
381
382 fnd_msg_pub.Delete_Msg;
383 inv_quantity_tree_pub.query_quantities(
384 p_api_version_number => 1.0,
385 p_init_msg_lst => 'T',
386 x_return_status => x_return_status,
387 x_msg_count => x_msg_count,
388 x_msg_data => x_msg_data,
389 p_organization_id => p_org_id,
390 p_inventory_item_id => p_component_id,
391 p_tree_mode => 2,
392 p_is_revision_control => l_is_revision_control,
393 p_is_lot_control => l_is_lot_control,
394 p_is_serial_control => l_is_serial_control,
395 p_lot_expiration_date => sysdate,
396 p_revision => null,
397 p_lot_number => null,
398 p_subinventory_code => p_subinv_code,
399 p_locator_id => null,
400 p_onhand_source => 3,
401 x_qoh => x_qoh,
402 x_rqoh => x_rqoh,
403 x_qr => x_qr,
404 x_qs => x_qs,
405 x_att => x_att,
406 x_atr => x_atr
407 );
408
409 --call to clear the in memory cache
410 inv_quantity_tree_pub.clear_quantity_cache;
411
412 wip_ws_util.trace_log('WIPWSSHB.get_subinv_component_onhand: '||
413 '; x_msg_count '||x_msg_count||
414 '; x_msg_data '||x_msg_data||
415 '; x_qoh '||x_qoh||
416 '; x_rqoh '||x_rqoh||
417 '; x_qr '||x_qr||
418 '; x_qs '||x_qs||
419 '; x_att '||x_att||
420 '; x_atr '||x_atr);
421
422 return x_att;
423
424 END get_subinv_component_onhand;
425
426
427 /*
428 *This procedure finds out the onhand qty (available to transact) of a component in org
429 */
430 FUNCTION get_org_component_onhand(
431 p_org_id NUMBER,
432 p_component_id NUMBER) RETURN NUMBER IS
433 BEGIN
434 return (get_subinv_component_onhand(p_org_id, to_char(null), p_component_id));
435 END get_org_component_onhand;
436
437
438 /*
439 * This procedure inserts a component record in shortages temp table
440 */
441 PROCEDURE insert_critical_component(p_org_id NUMBER,
442 p_inv_item_id NUMBER,
443 p_subinv_code VARCHAR2 ,
444 p_locator_id NUMBER,
445 p_avail_qty NUMBER) IS
446 BEGIN
447 insert into wip_ws_critical_comp_temp
448 (organization_id,
449 inventory_item_id,
450 supply_subinventory,
451 supply_locator_id,
452 onhand_qty,
453 projected_avail_qty
454 )values
455 (p_org_id,
456 p_inv_item_id,
457 p_subinv_code,
458 p_locator_id,
459 p_avail_qty,
460 p_avail_qty
461 );
462 END insert_critical_component;
463
464 FUNCTION is_all_component_selected(p_org_id NUMBER) RETURN BOOLEAN IS
465 CURSOR all_item_pref_csr IS
466 select wpv.attribute_value_code
467 from wip_preference_values wpv
468 where wpv.preference_id = g_pref_id_comp_short
469 and wpv.level_id = g_pref_level_id_site
470 and wpv.attribute_name = g_pref_val_comp_type_att
471 and wpv.sequence_number in (
472 select wpv1.sequence_number
473 from wip_preference_values wpv1
474 where wpv1.preference_id = g_pref_id_comp_short
475 and wpv1.level_id = g_pref_level_id_site
476 and wpv1.attribute_name = g_pref_val_dtl_org_att
477 and wpv1.attribute_value_code = to_char(p_org_id));
478 l_found BOOLEAN := FALSE;
479 BEGIN
480 for c_all_item_pref_csr in all_item_pref_csr loop
481 if(c_all_item_pref_csr.attribute_value_code = g_pref_val_comp_type_all) then
482 l_found := TRUE;
483 exit;
484 end if;
485 end loop;
486 return l_found;
487
488 END is_all_component_selected;
489
490 /*
491 * This procedure finds out the critical components based on preferences and usage in jobs.
492 * Call the procedure to insert the critical component into temp table
493 * It inserts a record for org component, and if subinv calc is selected in preference, then
494 * another record is inserted for subinv
495 */
496 PROCEDURE get_pref_critical_components (p_org_id NUMBER, p_end_time DATE) IS
497 l_job_status_clause VARCHAR2(240);
498 l_job_statuses VARCHAR2(240);
499 l_job_type_clause VARCHAR2(240);
500 l_sql VARCHAR2(4000);
501 l_cursor integer;
502 l_dummy integer;
503 l_inv_item_id NUMBER;
504 l_subinv_code VARCHAR2(10);
505 l_old_inv_item_id NUMBER := -1;
506 l_comp_avail NUMBER;
507 l_item_ids VARCHAR2(1048);
508 l_cat_ids VARCHAR2(1048);
509 l_item_clause VARCHAR2(4000);
510 l_cat_clause VARCHAR2(4000); -- Bug 14169271: Extend length of the clause
511 l_temp_where VARCHAR2(4000);
512 l_all_clause VARCHAR2(240);
513
514 BEGIN
515 wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_pref_critical_components: Entered' );
516 l_job_status_clause := ' and wdj.status_type in ('|| get_pref_job_statuses() || ')';
517 wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_pref_critical_components: l_job_status_clause='||l_job_status_clause );
518 l_job_type_clause := ' and wdj.job_type in ('||get_job_types() || ')';
519 wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_pref_critical_components: l_job_type_clause='||l_job_type_clause );
520
521 l_sql := l_sql || 'select distinct wro.inventory_item_id, ';
522 l_sql := l_sql || ' decode(wro.supply_subinventory, null, ';
523 l_sql := l_sql || ' (decode(msi.wip_supply_subinventory, null, wp.default_pull_supply_subinv, msi.wip_supply_subinventory)),';
524 l_sql := l_sql || ' wro.supply_subinventory) supply_subinventory ';
525 l_sql := l_sql || ' from wip_discrete_jobs wdj, ';
526 l_sql := l_sql || ' wip_requirement_operations wro, ';
527 l_sql := l_sql || ' mtl_system_items msi, ';
528 l_sql := l_sql || ' wip_parameters wp ';
529 l_sql := l_sql || ' where wdj.organization_id = :org_id ';
530 l_sql := l_sql || ' and wdj.start_quantity - wdj.quantity_completed - wdj.quantity_scrapped > 0 ';
531 l_sql := l_sql || ' and wdj.scheduled_start_date < :shift_end_time ';
532 l_sql := l_sql || ' and wro.organization_id = wdj.organization_id ';
533 l_sql := l_sql || ' and wro.wip_entity_id = wdj.wip_entity_id ';
534 l_sql := l_sql || ' and wp.organization_id = wdj.organization_id ';
535 l_sql := l_sql || ' and msi.organization_id = wdj.organization_id ';
536 l_sql := l_sql || ' and msi.inventory_item_id = wro.inventory_item_id ';
537 l_sql := l_sql || l_job_status_clause;
538 l_sql := l_sql || l_job_type_clause;
539
540 l_cat_ids := get_pref_comp_cat(p_org_id);
541 l_item_ids := get_pref_comp_id(p_org_id);
542
543 if(is_all_component_selected(p_org_id)) then
544 l_all_clause := ' 1=1 ';
545 end if;
546
547 /*
548 if(l_cat_ids is not null) then
549 l_cat_clause := ' exists (select inventory_item_id ' ||
550 ' from mtl_item_categories ' ||
551 ' where inventory_item_id = wro.inventory_item_id '||
552 ' and organization_id = wdj.organization_id '||
553 ' and category_set_id = :cat_set_id '||
554 ' and category_id in (:cat_ids))';
555 --l_sql := l_sql || l_cat_clause;
556 end if;
557 */
558
559 if(l_cat_ids is not null) then
560 -- Bug 14169271: Rewrite the clause because cannot bind multiple values to one variable
561 -- Bind (NUMBER a, NUMBER b) to l_cat_ids will cause ORA-01722: invalid number
562 l_cat_clause := ' exists (select inventory_item_id ' ||
563 ' from mtl_item_categories ' ||
564 ' where inventory_item_id = wro.inventory_item_id '||
565 ' and organization_id = wdj.organization_id '||
566 ' and category_set_id = :cat_set_id '||
567 ' and category_id in (select wpv.attribute_value_code '||
568 ' from wip_preference_values wpv '||
569 ' where wpv.preference_id = :pref_id_comp_short '||
570 ' and wpv.level_id = :pref_level_id_site '||
571 ' and wpv.attribute_name = :pref_val_comp_type_cat_att '||
572 ' and wpv.sequence_number in ( '||
573 ' select wpv1.sequence_number '||
574 ' from wip_preference_values wpv1 '||
575 ' where wpv1.preference_id = :pref_id_comp_short '||
576 ' and wpv1.level_id = :pref_level_id_site '||
577 ' and wpv1.attribute_name = :pref_val_comp_type_att '||
578 ' and wpv1.attribute_value_code = to_char(:pref_val_comp_type_cat) '||
579 ' and wpv1.sequence_number in ( '||
580 ' select wpv2.sequence_number '||
581 ' from wip_preference_values wpv2 '||
582 ' where wpv2.preference_id = :pref_id_comp_short '||
583 ' and wpv2.level_id = :pref_level_id_site '||
584 ' and wpv2.attribute_name = :pref_val_dtl_org_att '||
585 ' and wpv2.attribute_value_code = to_char(wro.organization_id))) '||
586 ' ))';
587 --l_sql := l_sql || l_cat_clause;
588 end if;
589
590 if(l_item_ids is not null) then
591 --l_item_clause := ' msi.inventory_item_id in (:inv_item_ids)';
592 --l_sql := l_sql || l_item_clause;
593 l_item_clause := 'msi.inventory_item_id in ( '||
594 ' select wpv.attribute_value_code ' ||
595 ' from wip_preference_values wpv ' ||
596 ' where wpv.preference_id = :pref_id_comp_short1 ' ||
597 ' and wpv.level_id = :pref_level_id_site1 ' ||
598 ' and wpv.attribute_name = :pref_val_comp_type_item_att1 ' ||
599 ' and wpv.sequence_number in ( ' ||
600 ' select wpv1.sequence_number ' ||
601 ' from wip_preference_values wpv1 ' ||
602 ' where wpv1.preference_id = :pref_id_comp_short2 ' ||
603 ' and wpv1.level_id = :pref_level_id_site2 ' ||
604 ' and wpv1.attribute_name = :pref_val_comp_type_att2 ' ||
605 ' and wpv1.attribute_value_code = to_char(:pref_val_comp_type_item2) ' ||
606 ' and wpv1.sequence_number in ( ' ||
607 ' select wpv2.sequence_number ' ||
608 ' from wip_preference_values wpv2 ' ||
609 ' where wpv2.preference_id = :pref_id_comp_short3 ' ||
610 ' and wpv2.level_id = :pref_level_id_site3 ' ||
611 ' and wpv2.attribute_name = :pref_val_dtl_org_att3 ' ||
612 ' and wpv2.attribute_value_code = to_char(wro.organization_id))) )';
613
614 end if;
615
616 if(l_all_clause is not null OR l_cat_clause is not null OR l_item_clause is not null) then
617 l_temp_where := l_temp_where || ' and ( ';
618
619 if(l_all_clause is not null) then
620 l_temp_where := l_temp_where || '1 = 1';
621 end if;
622
623 if(l_cat_clause is not null) then
624 if(l_all_clause is not null) then
625 l_temp_where := l_temp_where || ' OR ';
626 end if;
627 l_temp_where := l_temp_where || l_cat_clause;
628 end if;
629
630 if(l_item_clause is not null) then
631 if(l_all_clause is not null OR l_cat_clause is not null) then
632 l_temp_where := l_temp_where || ' OR ';
633 end if;
634 l_temp_where := l_temp_where || l_item_clause;
635 end if;
636
637 l_temp_where := l_temp_where || ')';
638 else
639 l_temp_where := l_temp_where || 'and 1 = 2';
640 end if;
641
642 l_sql := l_sql || l_temp_where;
643
644 l_sql := l_sql || ' order by inventory_item_id ';
645
646 wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_pref_critical_components: l_sql='||l_sql );
647
648 l_cursor := dbms_sql.open_cursor;
649 dbms_sql.parse(l_cursor, l_sql, dbms_sql.native);
650 dbms_sql.define_column(l_cursor, 1, l_inv_item_id);
651 dbms_sql.define_column(l_cursor, 2, l_subinv_code,10);
652 dbms_sql.bind_variable(l_cursor, ':org_id', p_org_id);
653 dbms_sql.bind_variable(l_cursor, ':shift_end_time', p_end_time);
654 if(l_cat_ids is not null) then
655 dbms_sql.bind_variable(l_cursor, ':cat_set_id', g_org_comp_calc_rec.category_set_id);
656 --dbms_sql.bind_variable(l_cursor, ':cat_ids', l_cat_ids);
657 -- Bug 14169271: Bind variables in l_cat_clause
658 dbms_sql.bind_variable(l_cursor, ':pref_id_comp_short', g_pref_id_comp_short);
659 dbms_sql.bind_variable(l_cursor, ':pref_level_id_site', g_pref_level_id_site);
660 dbms_sql.bind_variable(l_cursor, ':pref_val_comp_type_cat_att', g_pref_val_comp_type_cat_att);
661 dbms_sql.bind_variable(l_cursor, ':pref_val_comp_type_att', g_pref_val_comp_type_att);
662 dbms_sql.bind_variable(l_cursor, ':pref_val_comp_type_cat', g_pref_val_comp_type_cat);
663 dbms_sql.bind_variable(l_cursor, ':pref_val_dtl_org_att', g_pref_val_dtl_org_att);
664 end if;
665
666 if(l_item_ids is not null) then
667 --dbms_sql.bind_variable(l_cursor, ':inv_item_ids', l_item_ids);
668 dbms_sql.bind_variable(l_cursor, ':pref_id_comp_short1', g_pref_id_comp_short );
669 dbms_sql.bind_variable(l_cursor, ':pref_level_id_site1', g_pref_level_id_site);
670 dbms_sql.bind_variable(l_cursor, ':pref_val_comp_type_item_att1', g_pref_val_comp_type_item_att);
671 dbms_sql.bind_variable(l_cursor, ':pref_id_comp_short2', g_pref_id_comp_short);
672 dbms_sql.bind_variable(l_cursor, ':pref_level_id_site2', g_pref_level_id_site);
673 dbms_sql.bind_variable(l_cursor, ':pref_val_comp_type_att2', g_pref_val_comp_type_att);
674 dbms_sql.bind_variable(l_cursor, ':pref_val_comp_type_item2', g_pref_val_comp_type_item);
675 dbms_sql.bind_variable(l_cursor, ':pref_id_comp_short3', g_pref_id_comp_short);
676 dbms_sql.bind_variable(l_cursor, ':pref_level_id_site3', g_pref_level_id_site);
677 dbms_sql.bind_variable(l_cursor, ':pref_val_dtl_org_att3', g_pref_val_dtl_org_att);
678
679 end if;
680
681 l_dummy := dbms_sql.execute(l_cursor);
682
683 LOOP
684 EXIT WHEN DBMS_SQL.FETCH_ROWS (l_cursor) = 0;
685 dbms_sql.column_value(l_cursor, 1, l_inv_item_id);
686 dbms_sql.column_value(l_cursor, 2, l_subinv_code);
687 wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_pref_critical_components: component='||l_inv_item_id||', subinv_code='||l_subinv_code );
688 if(l_inv_item_id <> l_old_inv_item_id) then
689 wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_pref_critical_components: calling orgonhand for component='||l_inv_item_id );
690 l_comp_avail := get_org_component_onhand(p_org_id, l_inv_item_id);
691 wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_pref_critical_components: back from orgcomponent onhand, component ='||l_inv_item_id||', onhand='||l_comp_avail );
692 insert_critical_component(p_org_id, l_inv_item_id, null, null, l_comp_avail);
693 wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_pref_critical_components: back from insert_critical_component, org='||p_org_id||', item ='||l_inv_item_id||', onhand='||l_comp_avail );
694 end if;
695
696 if(g_org_comp_calc_rec.shortage_calc_level = 2) then
697 wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_pref_critical_components: shortage calc=subinv, calling subinv onhand, org='||p_org_id||', item ='||l_inv_item_id||', subinv='||l_subinv_code);
698 l_comp_avail := get_subinv_component_onhand(p_org_id, l_subinv_code, l_inv_item_id);
699 wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_pref_critical_components: shortage calc=subinv, back from subinv onhand, org='||p_org_id||', item ='||l_inv_item_id||', subinv='||l_subinv_code||', subinv onhand='||l_comp_avail);
700 insert_critical_component(p_org_id, l_inv_item_id, l_subinv_code, null, l_comp_avail);
701 wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_pref_critical_components: shortage calc=subinv, back from insert_critical_component, org='||p_org_id||', item ='||l_inv_item_id);
702 end if;
703 l_old_inv_item_id := l_inv_item_id;
704 END LOOP;
705 dbms_sql.close_cursor(l_cursor);
706
707
708 EXCEPTION
709 WHEN OTHERS THEN
710 dbms_sql.close_cursor(l_cursor);
711
712 END get_pref_critical_components;
713
714
715 /**
716 * This procedure finds out the job ops to be considered based on timeline job statuses selected in preferences
717 * It stores the job ops in global pl/sql table for later use
718 */
719 PROCEDURE get_job_ops(p_org_id NUMBER, p_end_time DATE) IS
720 l_job_status_clause VARCHAR2(240);
721 l_job_type_clause VARCHAR2(240);
722 l_job_statuses VARCHAR2(240);
723 l_sql VARCHAR2(2048);
724 l_cursor integer;
725 l_dummy integer;
726 l_org_id NUMBER;
727 l_wip_ent_id NUMBER;
728 l_dept_id NUMBER;
729 l_op_seq_num NUMBER;
730 l_op_fusd DATE;
731 l_op_sch_qty NUMBER;
732 l_op_start_qty NUMBER;
733 l_op_open_qty NUMBER;
734 l_return_status VARCHAR2(1);
735 l_return_code NUMBER;
736 i NUMBER;
737 BEGIN
738 wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_job_ops:Entered');
739 l_job_status_clause := 'and wdj.status_type in ('|| get_pref_job_statuses() || ')';
740 l_job_type_clause := 'and wdj.job_type in ('||get_job_types() || ')';
741
742 l_sql := l_sql || 'SELECT wo.organization_id, ';
743 l_sql := l_sql || 'wo.wip_entity_id, ';
744 l_sql := l_sql || 'wo.department_id, ';
745 l_sql := l_sql || 'wo.operation_seq_num, ';
746 l_sql := l_sql || 'wo.first_unit_start_date, ';
747 l_sql := l_sql || 'wo.scheduled_quantity, ';
748 l_sql := l_sql || 'wo.scheduled_quantity-wo.cumulative_scrap_quantity as start_qty, ';
749 l_sql := l_sql || 'wo.scheduled_quantity-wo.cumulative_scrap_quantity as open_qty ';
750 l_sql := l_sql || 'FROM wip_discrete_jobs wdj, ';
751 l_sql := l_sql || 'wip_operations wo ';
752 l_sql := l_sql || 'WHERE wdj.organization_id = :org_id ';
753 l_sql := l_sql || 'AND wdj.scheduled_start_date < :shift_end_time ';
754 l_sql := l_sql || 'AND wo.organization_id = wdj.organization_id ';
755 l_sql := l_sql || 'AND wo.wip_entity_id = wdj.wip_entity_id ';
756 l_sql := l_sql || 'AND wo.first_unit_start_date < :shift_end_time2 ';
757 l_sql := l_sql || 'AND wo.scheduled_quantity -wo.quantity_completed -wo.cumulative_scrap_quantity > 0 ';
758 l_sql := l_sql || l_job_status_clause;
759 l_sql := l_sql || l_job_type_clause;
760 l_sql := l_sql || 'ORDER BY wo.first_unit_start_date, ';
761 l_sql := l_sql || ' wdj.scheduled_start_date, ';
762 l_sql := l_sql || ' wo.operation_seq_num ';
763
764 wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_job_ops:l_sql='||l_sql);
765
766 l_cursor := dbms_sql.open_cursor;
767 dbms_sql.parse(l_cursor, l_sql, dbms_sql.native);
768 dbms_sql.define_column(l_cursor, 1, l_org_id);
769 dbms_sql.define_column(l_cursor, 2, l_wip_ent_id);
770 dbms_sql.define_column(l_cursor, 3, l_dept_id);
771 dbms_sql.define_column(l_cursor, 4, l_op_seq_num);
772 dbms_sql.define_column(l_cursor, 5, l_op_fusd );
773 dbms_sql.define_column(l_cursor, 6, l_op_sch_qty );
774 dbms_sql.define_column(l_cursor, 7, l_op_start_qty );
775 dbms_sql.define_column(l_cursor, 8, l_op_open_qty );
776
777 dbms_sql.bind_variable(l_cursor, ':org_id', p_org_id);
778 dbms_sql.bind_variable(l_cursor, ':shift_end_time', p_end_time);
779 dbms_sql.bind_variable(l_cursor, ':shift_end_time2', p_end_time);
780
781 l_dummy := dbms_sql.execute(l_cursor);
782 i := 0;
783 LOOP
784 EXIT WHEN DBMS_SQL.FETCH_ROWS (l_cursor) = 0;
785 dbms_sql.column_value(l_cursor, 1, l_org_id);
786 dbms_sql.column_value(l_cursor, 2, l_wip_ent_id);
787 dbms_sql.column_value(l_cursor, 3, l_dept_id);
788 dbms_sql.column_value(l_cursor, 4, l_op_seq_num);
789 dbms_sql.column_value(l_cursor, 5, l_op_fusd);
790 dbms_sql.column_value(l_cursor, 6, l_op_sch_qty);
791 dbms_sql.column_value(l_cursor, 7, l_op_start_qty);
792 dbms_sql.column_value(l_cursor, 8, l_op_open_qty);
793
794 wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_job_ops:Adding job op, l_org_id='||l_org_id||',l_wip_ent_id='||l_wip_ent_id||
795 ',l_op_seq_num='||l_op_seq_num||',l_dept_id='||l_dept_id||',l_op_fusd='||l_op_fusd||',l_op_start_qty='||l_op_start_qty||
796 ',l_op_open_qty='||l_op_open_qty||',l_op_sch_qty='||l_op_sch_qty);
797
798 g_wip_job_op_tbl(i).ORGANIZATION_ID := l_org_id;
799 g_wip_job_op_tbl(i).WIP_ENTITY_ID := l_wip_ent_id;
800 g_wip_job_op_tbl(i).OPERATION_SEQ_NUM := l_op_seq_num;
801 g_wip_job_op_tbl(i).DEPARTMENT_ID := l_dept_id;
802 g_wip_job_op_tbl(i).FIRST_UNIT_START_DATE := l_op_fusd;
803 g_wip_job_op_tbl(i).START_QTY := l_op_start_qty;
804 g_wip_job_op_tbl(i).OPEN_QTY := l_op_open_qty;
805 g_wip_job_op_tbl(i).SCHEDULED_QTY := l_op_sch_qty;
806 i := i+1;
807 END LOOP;
808 dbms_sql.close_cursor(l_cursor);
809
810 --call custom hook procedure to reorder operations if necessary
811 begin
812 wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_job_ops:Calling custom procedure for reordering operations');
813 wip_ws_custom.reorder_ops_for_shortage(g_wip_job_op_tbl, l_return_status, l_return_code);
814 wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_job_ops:back from custom procedure for reordering operations with status='||l_return_status);
815
816 if(l_return_status <> 'S') then
817 raise fnd_api.g_exc_unexpected_error;
818 end if;
819 exception
820 when others then
821 raise;
822 end;
823
824 EXCEPTION
825 WHEN OTHERS THEN
826 dbms_sql.close_cursor(l_cursor);
827
828
829 END get_job_ops;
830
831
832
833
834 /**
835 * This procedure finds out the critical components used in job ops and stores them in global pl/sql table
836 * These components are ordered by requirement date (operation start date)
837 *
838 */
839 PROCEDURE get_job_critical_components(p_org_id NUMBER, p_end_time DATE) IS
840 l_job_status_clause VARCHAR2(240);
841 l_job_statuses VARCHAR2(240);
842 l_sql VARCHAR2(4000);
843 l_cursor integer;
844 l_dummy integer;
845 l_org_id NUMBER;
846 l_wip_ent_id NUMBER;
847 l_dept_id NUMBER;
848 l_op_seq_num NUMBER;
849 l_op_fusd DATE;
850 l_op_sch_qty NUMBER;
851 l_op_start_qty NUMBER;
852 l_op_open_qty NUMBER;
853 l_cat_ids VARCHAR2(1024);
854 l_item_ids VARCHAR2(2048);
855 l_subinv_code VARCHAR2(10);
856 l_uom_code VARCHAR2(3);
857 l_inv_item_id NUMBER;
858 l_req_qty NUMBER;
859 l_qty_issued NUMBER;
860 l_qpa NUMBER;
861 l_qty_allocated NUMBER;
862 l_wip_supply_type NUMBER;
863 l_basis_type NUMBER;
864 l_item_clause VARCHAR2(4000);
865 l_cat_clause VARCHAR2(4000); -- Bug 14169271: Extend length of the clause
866 l_all_clause VARCHAR2(240);
867 l_temp_where VARCHAR2(4000);
868 i NUMBER;
869 j NUMBER;
870 l_qty_open NUMBER;
871 l_yield NUMBER;
872 BEGIN
873
874 wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_job_critical_components:Entered');
875
876 l_sql := l_sql || 'select wro.inventory_item_id, ';
877 l_sql := l_sql || ' decode(wro.supply_subinventory, null, ';
878 l_sql := l_sql || ' (decode(msi.wip_supply_subinventory, null, wp.default_pull_supply_subinv, msi.wip_supply_subinventory)),';
879 l_sql := l_sql || ' wro.supply_subinventory) supply_subinventory, ';
880 l_sql := l_sql || ' nvl(wro.required_quantity,0), ';
881 l_sql := l_sql || ' nvl(wro.quantity_issued,0), ';
882 l_sql := l_sql || ' nvl(wro.quantity_per_assembly,0), ';
883 l_sql := l_sql || ' nvl(wro.quantity_allocated,0), ';
884 l_sql := l_sql || ' wro.basis_type, ';
885 l_sql := l_sql || ' wro.wip_supply_type, ';
886 l_sql := l_sql || ' msi.primary_uom_code, ';
887 l_sql := l_sql || ' decode(wp.include_component_yield, 1, nvl(wro.component_yield_factor, 1), 1) ';
888 l_sql := l_sql || ' from wip_requirement_operations wro, ';
889 l_sql := l_sql || ' mtl_system_items msi, ';
890 l_sql := l_sql || ' wip_parameters wp ';
891 l_sql := l_sql || ' where wro.organization_id = :l_org_id ';
892 l_sql := l_sql || ' and wro.wip_entity_id = :l_wip_ent_id ';
893 l_sql := l_sql || ' and wro.operation_seq_num = :l_operation_seq_num ';
894 --bug 6983119 - Added the condition wro.quantity_per_assembly > 0
895 l_sql := l_sql || ' and wro.quantity_per_assembly > 0 ';
896 l_sql := l_sql || ' and wp.organization_id = wro.organization_id ';
897 l_sql := l_sql || ' and msi.organization_id = wro.organization_id ';
898 l_sql := l_sql || ' and msi.inventory_item_id = wro.inventory_item_id ';
899
900 /*
901 l_cat_ids := get_pref_comp_cat(p_org_id);
902 wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_job_critical_components:l_cat_ids='||l_cat_ids);
903 l_item_ids := get_pref_comp_id(p_org_id);
904 wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_job_critical_components:l_item_ids='||l_item_ids);
905
906 if(l_cat_ids is not null) then
907 l_cat_clause := ' and exists (select inventory_item_id ' ||
908 ' from mtl_item_categories ' ||
909 ' where inventory_item_id = wro.inventory_item_id '||
910 ' and organization_id = wro.organization_id '||
911 ' and category_set_id = :cat_set_id '||
912 ' and category_id in (:cat_ids))';
913 l_sql := l_sql || l_cat_clause;
914 end if;
915
916 if(l_item_ids is not null) then
917 l_item_clause := ' and msi.inventory_item_id in (:inv_item_ids)';
918 l_sql := l_sql || l_item_clause;
919 end if;
920 */
921
922 l_cat_ids := get_pref_comp_cat(p_org_id);
923 wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_job_critical_components:l_cat_ids='||l_cat_ids);
924 l_item_ids := get_pref_comp_id(p_org_id);
925 wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_job_critical_components:l_item_ids='||l_item_ids);
926
927 if(is_all_component_selected(p_org_id)) then
928 l_all_clause := ' 1=1 ';
929 end if;
930
931 if(l_cat_ids is not null) then
932 -- Bug 14169271: Rewrite the clause because cannot bind multiple values to one variable
933 -- Bind (NUMBER a, NUMBER b) to l_cat_ids will cause ORA-01722: invalid number
934 l_cat_clause := ' exists (select inventory_item_id ' ||
935 ' from mtl_item_categories ' ||
936 ' where inventory_item_id = wro.inventory_item_id '||
937 ' and organization_id = wro.organization_id '||
938 ' and category_set_id = :cat_set_id '||
939 ' and category_id in ( select wpv.attribute_value_code '||
940 ' from wip_preference_values wpv '||
941 ' where wpv.preference_id = :pref_id_comp_short '||
942 ' and wpv.level_id = :pref_level_id_site '||
943 ' and wpv.attribute_name = :pref_val_comp_type_cat_att '||
944 ' and wpv.sequence_number in ( '||
945 ' select wpv1.sequence_number '||
946 ' from wip_preference_values wpv1 '||
947 ' where wpv1.preference_id = :pref_id_comp_short '||
948 ' and wpv1.level_id = :pref_level_id_site '||
949 ' and wpv1.attribute_name = :pref_val_comp_type_att '||
950 ' and wpv1.attribute_value_code = to_char(:pref_val_comp_type_cat) '||
951 ' and wpv1.sequence_number in ( '||
952 ' select wpv2.sequence_number '||
953 ' from wip_preference_values wpv2 '||
954 ' where wpv2.preference_id = :pref_id_comp_short '||
955 ' and wpv2.level_id = :pref_level_id_site '||
956 ' and wpv2.attribute_name = :pref_val_dtl_org_att '||
957 ' and wpv2.attribute_value_code = to_char(:l_org_id))) '||
958 ' ))';
959 --l_sql := l_sql || l_cat_clause;
960 end if;
961
962 if(l_item_ids is not null) then
963 --l_item_clause := ' msi.inventory_item_id in (:inv_item_ids)';
964 l_item_clause := 'msi.inventory_item_id in ( '||
965 ' select wpv.attribute_value_code ' ||
966 ' from wip_preference_values wpv ' ||
967 ' where wpv.preference_id = :pref_id_comp_short1 ' ||
968 ' and wpv.level_id = :pref_level_id_site1 ' ||
969 ' and wpv.attribute_name = :pref_val_comp_type_item_att1 ' ||
970 ' and wpv.sequence_number in ( ' ||
971 ' select wpv1.sequence_number ' ||
972 ' from wip_preference_values wpv1 ' ||
973 ' where wpv1.preference_id = :pref_id_comp_short2 ' ||
974 ' and wpv1.level_id = :pref_level_id_site2 ' ||
975 ' and wpv1.attribute_name = :pref_val_comp_type_att2 ' ||
976 ' and wpv1.attribute_value_code = to_char(:pref_val_comp_type_item2) ' ||
977 ' and wpv1.sequence_number in ( ' ||
978 ' select wpv2.sequence_number ' ||
979 ' from wip_preference_values wpv2 ' ||
980 ' where wpv2.preference_id = :pref_id_comp_short3 ' ||
981 ' and wpv2.level_id = :pref_level_id_site3 ' ||
982 ' and wpv2.attribute_name = :pref_val_dtl_org_att3 ' ||
983 ' and wpv2.attribute_value_code = to_char(wro.organization_id))) )';
984
985 --l_sql := l_sql || l_item_clause;
986 end if;
987
988 if(l_all_clause is not null OR l_cat_clause is not null OR l_item_clause is not null) then
989 l_temp_where := l_temp_where || ' and ( ';
990
991 if(l_all_clause is not null) then
992 l_temp_where := l_temp_where || '1 = 1';
993 end if;
994
995 if(l_cat_clause is not null) then
996 if(l_all_clause is not null) then
997 l_temp_where := l_temp_where || ' OR ';
998 end if;
999 l_temp_where := l_temp_where || l_cat_clause;
1000 end if;
1001
1002 if(l_item_clause is not null) then
1003 if(l_all_clause is not null OR l_cat_clause is not null) then
1004 l_temp_where := l_temp_where || ' OR ';
1005 end if;
1006 l_temp_where := l_temp_where || l_item_clause;
1007 end if;
1008
1009 l_temp_where := l_temp_where || ')';
1010 else
1011 l_temp_where := l_temp_where || 'and 1 = 2';
1012 end if;
1013
1014 l_sql := l_sql || l_temp_where;
1015
1016
1017
1018
1019 wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_job_critical_components:l_sql='||l_sql);
1020
1021 IF (g_wip_job_op_tbl.COUNT > 0) THEN
1022 wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_job_critical_components:g_wip_job_op_tbl.count>0');
1023 FOR i in g_wip_job_op_tbl.FIRST .. g_wip_job_op_tbl.LAST LOOP
1024 wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_job_critical_components:Entered in for loop for org_id='||g_wip_job_op_tbl(i).ORGANIZATION_ID||
1025 ',wip_ent_id='||g_wip_job_op_tbl(i).WIP_ENTITY_ID||',op_seq_num='||g_wip_job_op_tbl(i).OPERATION_SEQ_NUM);
1026
1027
1028 l_cursor := dbms_sql.open_cursor;
1029 dbms_sql.parse(l_cursor, l_sql, dbms_sql.native);
1030 dbms_sql.define_column(l_cursor, 1, l_inv_item_id);
1031 dbms_sql.define_column(l_cursor, 2, l_subinv_code, 10);
1032 dbms_sql.define_column(l_cursor, 3, l_req_qty);
1033 dbms_sql.define_column(l_cursor, 4, l_qty_issued);
1034 dbms_sql.define_column(l_cursor, 5, l_qpa);
1035 dbms_sql.define_column(l_cursor, 6, l_qty_allocated);
1036 dbms_sql.define_column(l_cursor, 7, l_basis_type);
1037 dbms_sql.define_column(l_cursor, 8, l_wip_supply_type);
1038 dbms_sql.define_column(l_cursor, 9, l_uom_code, 3);
1039 dbms_sql.define_column(l_cursor, 10, l_yield);
1040
1041 dbms_sql.bind_variable(l_cursor, ':l_org_id', g_wip_job_op_tbl(i).ORGANIZATION_ID);
1042 dbms_sql.bind_variable(l_cursor, ':l_wip_ent_id', g_wip_job_op_tbl(i).WIP_ENTITY_ID);
1043 dbms_sql.bind_variable(l_cursor, ':l_operation_seq_num', g_wip_job_op_tbl(i).OPERATION_SEQ_NUM);
1044
1045 if(l_cat_ids is not null) then
1046 dbms_sql.bind_variable(l_cursor, ':cat_set_id', g_org_comp_calc_rec.category_set_id);
1047 --dbms_sql.bind_variable(l_cursor, ':cat_ids', l_cat_ids);
1048 -- Bug 14169271: Bind variables in l_cat_clause
1049 dbms_sql.bind_variable(l_cursor, ':pref_id_comp_short', g_pref_id_comp_short);
1050 dbms_sql.bind_variable(l_cursor, ':pref_level_id_site', g_pref_level_id_site);
1051 dbms_sql.bind_variable(l_cursor, ':pref_val_comp_type_cat_att', g_pref_val_comp_type_cat_att);
1052 dbms_sql.bind_variable(l_cursor, ':pref_val_comp_type_att', g_pref_val_comp_type_att);
1053 dbms_sql.bind_variable(l_cursor, ':pref_val_comp_type_cat', g_pref_val_comp_type_cat);
1054 dbms_sql.bind_variable(l_cursor, ':pref_val_dtl_org_att', g_pref_val_dtl_org_att);
1055 end if;
1056 if(l_item_ids is not null) then
1057 --dbms_sql.bind_variable(l_cursor, ':inv_item_ids', l_item_ids);
1058 dbms_sql.bind_variable(l_cursor, ':pref_id_comp_short1', g_pref_id_comp_short );
1059 dbms_sql.bind_variable(l_cursor, ':pref_level_id_site1', g_pref_level_id_site);
1060 dbms_sql.bind_variable(l_cursor, ':pref_val_comp_type_item_att1', g_pref_val_comp_type_item_att);
1061 dbms_sql.bind_variable(l_cursor, ':pref_id_comp_short2', g_pref_id_comp_short);
1062 dbms_sql.bind_variable(l_cursor, ':pref_level_id_site2', g_pref_level_id_site);
1063 dbms_sql.bind_variable(l_cursor, ':pref_val_comp_type_att2', g_pref_val_comp_type_att);
1064 dbms_sql.bind_variable(l_cursor, ':pref_val_comp_type_item2', g_pref_val_comp_type_item);
1065 dbms_sql.bind_variable(l_cursor, ':pref_id_comp_short3', g_pref_id_comp_short);
1066 dbms_sql.bind_variable(l_cursor, ':pref_level_id_site3', g_pref_level_id_site);
1067 dbms_sql.bind_variable(l_cursor, ':pref_val_dtl_org_att3', g_pref_val_dtl_org_att);
1068
1069 end if;
1070
1071 wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_job_critical_components: point 10 - before dbms_sql.execute');
1072 l_dummy := dbms_sql.execute(l_cursor);
1073 wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_job_critical_components: point 20 - after dbms_sql.execute');
1074 LOOP
1075 EXIT WHEN DBMS_SQL.FETCH_ROWS (l_cursor) = 0;
1076 wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_job_critical_components: point 50');
1077 dbms_sql.column_value(l_cursor, 1, l_inv_item_id);
1078 dbms_sql.column_value(l_cursor, 2, l_subinv_code);
1079 dbms_sql.column_value(l_cursor, 3, l_req_qty);
1080 dbms_sql.column_value(l_cursor, 4, l_qty_issued);
1081 dbms_sql.column_value(l_cursor, 5, l_qpa);
1082 dbms_sql.column_value(l_cursor, 6, l_qty_allocated);
1083 dbms_sql.column_value(l_cursor, 7, l_basis_type);
1084 dbms_sql.column_value(l_cursor, 8, l_wip_supply_type);
1085 dbms_sql.column_value(l_cursor, 9, l_uom_code);
1086 dbms_sql.column_value(l_cursor, 10, l_yield);
1087
1088 wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_job_critical_components:add_critical_component=l_inv_item_id='||l_inv_item_id||
1089 ',l_qpa='||l_qpa||',l_req_qty='||l_req_qty||',l_qty_issued='||l_qty_issued||
1090 ',l_op_open_qty='||g_wip_job_op_tbl(i).OPEN_QTY||
1091 ',l_qty_allocated='||l_qty_allocated||',l_comp_open_qty='||to_char((g_wip_job_op_tbl(i).OPEN_QTY * l_qpa) - l_qty_issued - l_qty_allocated));
1092
1093
1094 --add record for org level component information
1095 j := g_wip_job_critical_comp_tbl.LAST;
1096 if (j is NULL) then j:= 0; end if; j := j+1;
1097 g_wip_job_critical_comp_tbl(j).ORGANIZATION_ID := p_org_id;
1098 g_wip_job_critical_comp_tbl(j).WIP_ENTITY_ID := g_wip_job_op_tbl(i).WIP_ENTITY_ID;
1099 g_wip_job_critical_comp_tbl(j).OPERATION_SEQ_NUM := g_wip_job_op_tbl(i).OPERATION_SEQ_NUM;
1100 g_wip_job_critical_comp_tbl(j).INVENTORY_ITEM_ID := l_inv_item_id;
1101 g_wip_job_critical_comp_tbl(j).DEPARTMENT_ID := g_wip_job_op_tbl(i).DEPARTMENT_ID;
1102 g_wip_job_critical_comp_tbl(j).DATE_REQUIRED := g_wip_job_op_tbl(i).FIRST_UNIT_START_DATE;
1103 g_wip_job_critical_comp_tbl(j).QTY_PER_ASSEMBLY := l_qpa;
1104 g_wip_job_critical_comp_tbl(j).REQUIRED_QTY := l_req_qty;
1105 g_wip_job_critical_comp_tbl(j).QUANTITY_ISSUED := l_qty_issued;
1106 --g_wip_job_critical_comp_tbl(j).QUANTITY_OPEN := (g_wip_job_op_tbl(i).OPEN_QTY * l_qpa) - l_qty_issued - l_qty_allocated;
1107 if(nvl(l_basis_type, 1) = 1) then --item basis type
1108 l_qty_open := (g_wip_job_op_tbl(i).OPEN_QTY * l_qpa)/l_yield - l_qty_issued - l_qty_allocated;
1109 else --basis type = lot
1110 l_qty_open := l_qpa/l_yield - l_qty_issued - l_qty_allocated;
1111 end if;
1112 if(l_qty_open < 0) then l_qty_open := 0; end if;
1113 g_wip_job_critical_comp_tbl(j).QUANTITY_OPEN := nvl(l_qty_open , 0);
1114 g_wip_job_critical_comp_tbl(j).WIP_SUPPLY_TYPE := l_wip_supply_type;
1115 g_wip_job_critical_comp_tbl(j).BASIS_TYPE := l_basis_type;
1116 g_wip_job_critical_comp_tbl(j).SUPPLY_SUBINVENOTRY := null; --for org record
1117 g_wip_job_critical_comp_tbl(j).PRIMARY_UOM_CODE := l_uom_code;
1118
1119 --add another record for subinv if preference is set
1120 if(g_org_comp_calc_rec.shortage_calc_level = 2) then
1121 wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_job_critical_components:add_critical_component=l_inv_item_id='||l_inv_item_id||', l_subinv='||l_subinv_code);
1122 j := g_wip_job_critical_comp_tbl.LAST;
1123 if (j is NULL) then j:= 0; end if; j := j+1;
1124 g_wip_job_critical_comp_tbl(j).ORGANIZATION_ID := p_org_id;
1125 g_wip_job_critical_comp_tbl(j).WIP_ENTITY_ID := g_wip_job_op_tbl(i).WIP_ENTITY_ID;
1126 g_wip_job_critical_comp_tbl(j).OPERATION_SEQ_NUM := g_wip_job_op_tbl(i).OPERATION_SEQ_NUM;
1127 g_wip_job_critical_comp_tbl(j).INVENTORY_ITEM_ID := l_inv_item_id;
1128 g_wip_job_critical_comp_tbl(j).DEPARTMENT_ID := g_wip_job_op_tbl(i).DEPARTMENT_ID;
1129 g_wip_job_critical_comp_tbl(j).DATE_REQUIRED := g_wip_job_op_tbl(i).FIRST_UNIT_START_DATE;
1130 g_wip_job_critical_comp_tbl(j).QTY_PER_ASSEMBLY := l_qpa;
1131 g_wip_job_critical_comp_tbl(j).REQUIRED_QTY := l_req_qty;
1132 g_wip_job_critical_comp_tbl(j).QUANTITY_ISSUED := l_qty_issued;
1133 --g_wip_job_critical_comp_tbl(j).QUANTITY_OPEN := (g_wip_job_op_tbl(i).OPEN_QTY * l_qpa) - (l_qty_issued - l_qty_allocated);
1134 if(nvl(l_basis_type, 1) = 1) then --item basis type
1135 l_qty_open := (g_wip_job_op_tbl(i).OPEN_QTY * l_qpa)/l_yield - l_qty_issued - l_qty_allocated;
1136 else --basis type = lot
1137 l_qty_open := l_qpa/l_yield - l_qty_issued - l_qty_allocated;
1138 end if;
1139 if(l_qty_open < 0) then l_qty_open := 0; end if;
1140 g_wip_job_critical_comp_tbl(j).QUANTITY_OPEN := nvl(l_qty_open , 0);
1141 g_wip_job_critical_comp_tbl(j).WIP_SUPPLY_TYPE := l_wip_supply_type;
1142 g_wip_job_critical_comp_tbl(j).BASIS_TYPE := l_basis_type;
1143 g_wip_job_critical_comp_tbl(j).SUPPLY_SUBINVENOTRY := l_subinv_code; --for subinv record
1144 g_wip_job_critical_comp_tbl(j).PRIMARY_UOM_CODE := l_uom_code;
1145 end if;
1146 END LOOP;
1147
1148
1149 dbms_sql.close_cursor(l_cursor);
1150
1151 END LOOP;
1152 END IF;
1153 --EXCEPTION
1154 -- WHEN OTHERS THEN
1155 -- dbms_sql.close_cursor(l_cursor);
1156
1157 END get_job_critical_components;
1158
1159
1160 /**
1161 * This procedure finds out the critical resources used in jobs based on preference and stores these
1162 * in global pl/sql table for later use. These job op resources are ordered by required date (operation start date)
1163 */
1164 PROCEDURE get_job_critical_resources(p_org_id NUMBER, p_end_time DATE) IS
1165 l_job_status_clause VARCHAR2(240);
1166 l_job_statuses VARCHAR2(240);
1167 l_sql VARCHAR2(2048);
1168 l_cursor integer;
1169 l_dummy integer;
1170 l_org_id NUMBER;
1171 l_wip_ent_id NUMBER;
1172 l_dept_id NUMBER;
1173 l_op_seq_num NUMBER;
1174 l_op_fusd NUMBER;
1175 l_op_sch_qty NUMBER;
1176 l_op_start_qty NUMBER;
1177 l_op_open_qty NUMBER;
1178 i NUMBER;
1179 j NUMBER;
1180 CURSOR res_req_csr(p_org_id NUMBER, p_wip_ent_id NUMBER, p_op_seq_num NUMBER) IS
1181 select distinct
1182 wor.wip_entity_id,
1183 wor.operation_seq_num,
1184 wor.resource_id,
1185 nvl(wip_ws_dl_util.get_col_res_usage_req(wor.wip_entity_id, wor.operation_seq_num,wo.department_id, wor.resource_id, null),0) open_quantity,
1186 wor.uom_code,
1187 decode( wip_ws_time_entry.is_time_uom(wor.uom_code), 'Y',
1188 inv_convert.inv_um_convert(-1,
1189 38,
1190 wor.usage_rate_or_amount,
1191 wor.uom_code,
1192 fnd_profile.value('BOM:HOUR_UOM_CODE'),
1193 NULL,
1194 NULL),
1195 null) usage,
1196 wor.applied_resource_units ,
1197 wor.basis_type,
1198 decode(wp.include_resource_efficiency, 1, nvl(bdr.efficiency, 1), 1) efficiency
1199 from wip_operation_resources wor,
1200 wip_operations wo,
1201 wip_parameters wp,
1202 bom_department_resources bdr
1203 where wor.organization_id = p_org_id
1204 and wor.wip_entity_id = p_wip_ent_id
1205 and wor.operation_seq_num = p_op_seq_num
1206 and wo.wip_entity_id = wor.wip_entity_id
1207 and wo.organization_id = wor.organization_id
1208 and wp.organization_id = wor.organization_id
1209 and wo.operation_seq_num = wor.operation_seq_num
1210 and bdr.resource_id = wor.resource_id
1211 and bdr.department_id = nvl(wor.department_id, wo.department_id)
1212 and wor.resource_id in (
1213 select distinct to_number(wpv.attribute_value_code) resource_id
1214 from wip_preference_values wpv
1215 where wpv.preference_id = g_pref_id_res_short
1216 and wpv.attribute_name = 'resource'
1217 and wpv.level_id = 1
1218 and wpv.sequence_number in (
1219 select wpv_org.sequence_number
1220 from wip_preference_values wpv_org
1221 where wpv_org.preference_id = g_pref_id_res_short
1222 and wpv_org.attribute_name = 'organization'
1223 and to_number(wpv_org.attribute_value_code) = p_org_id))
1224 order by resource_id;
1225
1226 l_shift_seq NUMBER;
1227 l_shift_num NUMBER;
1228 l_shift_start_date DATE;
1229 l_shift_end_date DATE;
1230 l_shift_string VARCHAR2(240);
1231 l_req_date DATE;
1232 l_res_req NUMBER;
1233 prev_res_id NUMBER;
1234
1235 cursor critical_res_csr IS
1236 select organization_id, resource_id, department_id from wip_ws_critical_res_temp;
1237
1238
1239 BEGIN
1240 wip_ws_util.trace_log('WIP_WS_SHORTAGE:get_job_critical_resources:Entered ');
1241
1242 IF (g_wip_job_op_tbl.COUNT > 0) THEN
1243 FOR i in g_wip_job_op_tbl.FIRST .. g_wip_job_op_tbl.LAST LOOP
1244 wip_ws_util.trace_log('WIP_WS_SHORTAGE:get_job_critical_resources: Check critical resources in operation ');
1245 wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_job_critical_resources:Entered in operation for loop for org_id='||g_wip_job_op_tbl(i).ORGANIZATION_ID||
1246 ',wip_ent_id='||g_wip_job_op_tbl(i).WIP_ENTITY_ID||',op_seq_num='||g_wip_job_op_tbl(i).OPERATION_SEQ_NUM);
1247 prev_res_id := null;
1248 FOR c_res_req_csr in res_req_csr(p_org_id,g_wip_job_op_tbl(i).WIP_ENTITY_ID, g_wip_job_op_tbl(i).OPERATION_SEQ_NUM) LOOP
1249 IF(c_res_req_csr.resource_id <> nvl(prev_res_id , -1)) THEN
1250 prev_res_id := c_res_req_csr.resource_id;
1251 wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_job_critical_resources:Entered in resource for loop for resource id='||c_res_req_csr.RESOURCE_ID||
1252 ',qty_open='||c_res_req_csr.open_quantity);
1253
1254 j := g_wip_job_critical_res_tbl.LAST;
1255 if (j is NULL) then j:=0; end if; j:=j+1;
1256 g_wip_job_critical_res_tbl(j).ORGANIZATION_ID := p_org_id;
1257 g_wip_job_critical_res_tbl(j).WIP_ENTITY_ID := g_wip_job_op_tbl(i).WIP_ENTITY_ID;
1258 g_wip_job_critical_res_tbl(j).OPERATION_SEQ_NUM := g_wip_job_op_tbl(i).OPERATION_SEQ_NUM;
1259 g_wip_job_critical_res_tbl(j).RESOURCE_ID := c_res_req_csr.RESOURCE_ID;
1260 g_wip_job_critical_res_tbl(j).DEPARTMENT_ID := g_wip_job_op_tbl(i).DEPARTMENT_ID;
1261 g_wip_job_critical_res_tbl(j).DATE_REQUIRED := g_wip_job_op_tbl(i).FIRST_UNIT_START_DATE;
1262 g_wip_job_critical_res_tbl(j).QUANTITY_OPEN := c_res_req_csr.open_quantity;
1263 g_wip_job_critical_res_tbl(j).PRIMARY_UOM_CODE := c_res_req_csr.uom_code;
1264 g_wip_job_critical_res_tbl(j).QUANTITY_ISSUED := c_res_req_csr.applied_resource_units;
1265 if(nvl(c_res_req_csr.basis_type, 1) = 1) then --item basis type
1266 l_res_req := c_res_req_csr.usage * g_wip_job_op_tbl(i).OPEN_QTY;
1267 else
1268 l_res_req := c_res_req_csr.usage;
1269 end if;
1270
1271 l_res_req := nvl(l_res_req, 0) / nvl(c_res_req_csr.efficiency, 1);
1272 g_wip_job_critical_res_tbl(j).REQUIRED_QTY := l_res_req;
1273 l_req_date := g_wip_job_critical_res_tbl(j).DATE_REQUIRED; -- bug 9484419
1274 if(g_wip_job_critical_res_tbl(j).DATE_REQUIRED < sysdate) then
1275 l_req_date := sysdate;
1276 end if;
1277 wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_job_critical_resources:calling wip_ws_util.retrive_first_shift');
1278 --get shift id for each resource
1279 wip_ws_util.retrieve_first_shift(
1280 p_org_id => g_wip_job_critical_res_tbl(j).ORGANIZATION_ID,
1281 p_dept_id => g_wip_job_critical_res_tbl(j).DEPARTMENT_ID,
1282 p_resource_id => g_wip_job_critical_res_tbl(j).RESOURCE_ID ,
1283 p_date => l_req_date,
1284 x_shift_seq => l_shift_seq,
1285 x_shift_num => l_shift_num,
1286 x_shift_start_date => l_shift_start_date,
1287 x_shift_end_date => l_shift_end_date,
1288 x_shift_string => l_shift_string
1289 );
1290
1291 wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_job_critical_resources:back from retrieve_first_shift with shift_num='||
1292 l_shift_num||',shift_seq='||l_shift_seq);
1293
1294 g_wip_job_critical_res_tbl(j).SHIFT_NUM := l_shift_num;
1295 g_wip_job_critical_res_tbl(j).SHIFT_SEQ := l_shift_seq;
1296 --insert a record for dept resource
1297 begin
1298 insert into wip_ws_critical_res_temp
1299 (organization_id,
1300 resource_id,
1301 department_id)
1302 values
1303 (
1304 g_wip_job_critical_res_tbl(j).ORGANIZATION_ID,
1305 g_wip_job_critical_res_tbl(j).RESOURCE_ID,
1306 g_wip_job_critical_res_tbl(j).DEPARTMENT_ID
1307 );
1308
1309 exception when others then --ignore duplicate exception
1310 null;
1311 end;
1312 END IF;
1313 END LOOP;
1314 END LOOP;
1315 END IF;
1316
1317 wip_ws_util.trace_log( 'Printing critical job op resources');
1318 IF (g_wip_job_critical_res_tbl.COUNT > 0) THEN
1319 FOR j in g_wip_job_critical_res_tbl.FIRST .. g_wip_job_critical_res_tbl.LAST LOOP
1320 wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_job_critical_resources:Critical Resource:'||
1321 'org_id='||g_wip_job_critical_res_tbl(j).ORGANIZATION_ID ||
1322 ',wip_ent_id='||g_wip_job_critical_res_tbl(j).WIP_ENTITY_ID ||
1323 ',op_seq_num='||g_wip_job_critical_res_tbl(j).OPERATION_SEQ_NUM ||
1324 'res_id,='||g_wip_job_critical_res_tbl(j).RESOURCE_ID ||
1325 ',dept_id='||g_wip_job_critical_res_tbl(j).DEPARTMENT_ID ||
1326 ',date_req='||g_wip_job_critical_res_tbl(j).DATE_REQUIRED ||
1327 ',qty_req='||g_wip_job_critical_res_tbl(j).REQUIRED_QTY ||
1328 ',qty_issued='||g_wip_job_critical_res_tbl(j).QUANTITY_ISSUED ||
1329 ',qty_open='||g_wip_job_critical_res_tbl(j).QUANTITY_OPEN ||
1330 ',uom='||g_wip_job_critical_res_tbl(j).PRIMARY_UOM_CODE||
1331 ',shift_num='||g_wip_job_critical_res_tbl(j).SHIFT_NUM||
1332 ',shift_seq='||g_wip_job_critical_res_tbl(j).SHIFT_SEQ);
1333 END LOOP;
1334 END IF;
1335
1336 wip_ws_util.trace_log( 'Printing critical resources');
1337 for c_critical_res_csr in critical_res_csr loop
1338 wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_job_critical_resources:Critical Resource in temp table:'||
1339 'org_id='||c_critical_res_csr.ORGANIZATION_ID ||
1340 ',res_id='||c_critical_res_csr.RESOURCE_ID ||
1341 ',dept_id='||c_critical_res_csr.DEPARTMENT_ID);
1342
1343 end loop;
1344
1345 END get_job_critical_resources;
1346
1347
1348 /*
1349 * This procedure finds out the supply from discrete jobs for a particular subassy on a given date
1350 * The returned qty does not include the qty that is already reserved
1351 */
1352 FUNCTION get_wip_supply(p_org_id NUMBER, p_inv_item_id NUMBER, p_rcpt_date DATE) RETURN NUMBER IS
1353 CURSOR job_csr (p_org_id NUMBER, p_inv_item_id NUMBER, p_rcpt_date DATE) IS
1354 select wdj.organization_id,
1355 wdj.primary_item_id inventory_item_id,
1356 wdj.scheduled_completion_date receipt_date,
1357 GREATEST(0, (wdj.start_quantity - wdj.quantity_completed
1358 - wdj.quantity_scrapped)) item_qty,
1359 (select sum(mr.reservation_quantity)
1360 from mtl_reservations mr
1361 where mr.supply_source_type_id = 5 --wip supply
1362 and mr.supply_source_header_id = wdj.wip_entity_id
1363 and mr.organization_id = wdj.organization_id) reservation_qty,
1364 wdj.wip_entity_id --added for bug 6886708 for logging
1365 from wip_discrete_jobs wdj
1366 where wdj.organization_id = p_org_id
1367 and wdj.primary_item_id = p_inv_item_id
1368 and trunc(wdj.scheduled_completion_date) = trunc(p_rcpt_date)
1369 and wdj.status_type IN (WIP_CONSTANTS.UNRELEASED, WIP_CONSTANTS.RELEASED,
1370 WIP_CONSTANTS.COMP_CHRG, WIP_CONSTANTS.HOLD)
1371 and (wdj.start_quantity - wdj.quantity_completed - wdj.quantity_scrapped) > 0
1372 and wdj.job_type in (WIP_CONSTANTS.STANDARD, WIP_CONSTANTS.NONSTANDARD);
1373 l_qty NUMBER := 0;
1374 BEGIN
1375 for c_job_csr in job_csr(p_org_id, p_inv_item_id, p_rcpt_date) loop
1376 l_qty := l_qty + (c_job_csr.item_qty - nvl(c_job_csr.reservation_qty,0));
1377 wip_ws_util.trace_log('WIP_WS_SHORTAGE:get_wip_supply: '||
1378 'c_job_csr.wip_entity_id = '||c_job_csr.wip_entity_id||
1379 'c_job_csr.item_qty = '||c_job_csr.item_qty||
1380 'c_job_csr.reservation_qty = '||c_job_csr.reservation_qty);
1381 end loop;
1382 if(l_qty < 0 or l_qty is null) then l_qty := 0; end if;
1383 return l_qty;
1384
1385 EXCEPTION when others then
1386 return 0;
1387 END get_wip_supply;
1388
1389 /*
1390 * This procedure finds out the supply from flow schedules for a particular subassy on a given date
1391 *
1392 */
1393 FUNCTION get_flow_supply(p_org_id NUMBER, p_inv_item_id NUMBER, p_rcpt_date DATE) RETURN NUMBER IS
1394 CURSOR flow_sched_csr(p_org_id NUMBER, p_inv_item_id NUMBER, p_rcpt_date DATE) IS
1395 select SUM(GREATEST( 0, (wfs.planned_quantity - wfs.quantity_completed
1396 - wfs.quantity_scrapped))) item_qty,
1397 wfs.wip_entity_id --added for bug 6886708 for logging
1398 from WIP_FLOW_SCHEDULES wfs
1399 where wfs.status = 1
1400 and wfs.SCHEDULED_FLAG = 1
1401 and wfs.organization_id = p_org_id
1402 and wfs.primary_item_id = p_inv_item_id
1403 and trunc(wfs.scheduled_completion_date) = trunc(p_rcpt_date)
1404 and (wfs.planned_quantity - wfs.quantity_completed - quantity_scrapped) > 0
1405 and wfs.demand_source_header_id is null
1406 and wfs.demand_source_line is null;
1407 l_qty NUMBER := 0;
1408 BEGIN
1409 for c_flow_sched_csr in flow_sched_csr(p_org_id, p_inv_item_id, p_rcpt_date) loop
1410 l_qty := c_flow_sched_csr.item_qty;
1411 wip_ws_util.trace_log('WIP_WS_SHORTAGE:get_flow_supply: '||
1412 'flow_sched_csr.wip_entity_id = '||c_flow_sched_csr.wip_entity_id||
1413 'flow_sched_csr.item_qty = '||c_flow_sched_csr.item_qty);
1414 end loop;
1415 if (l_qty < 0 or l_qty is null) then l_qty := 0; end if;
1416 return l_qty;
1417
1418 EXCEPTION when others then
1419 return 0;
1420
1421 END get_flow_supply;
1422
1423
1424 /*
1425 * This procedure finds out the supply from discrete jobs negative requirements
1426 * for a particular subassy on a given date
1427 */
1428 FUNCTION get_wip_negreq_supply(p_org_id NUMBER, p_inv_item_id NUMBER, p_rcpt_date DATE) RETURN NUMBER IS
1429 CURSOR wip_negreq_csr(p_org_id NUMBER, p_inv_item_id NUMBER, p_rcpt_date DATE) IS
1430 select SUM(-1*wro.required_quantity) item_qty
1431 from wip_requirement_operations wro,
1432 wip_discrete_jobs wdj
1433 where wro.organization_id = p_org_id
1434 and wro.inventory_item_id = p_inv_item_id
1435 and trunc(wro.date_required) = trunc(p_rcpt_date)
1436 and wro.organization_id = wdj.organization_id
1437 and wro.wip_entity_id = wdj.wip_entity_id
1438 and wro.wip_supply_type <> wip_constants.PHANTOM
1439 and wro.required_quantity < 0
1440 and wro.operation_seq_num > 0
1441 and wdj.job_type in (WIP_CONSTANTS.STANDARD, WIP_CONSTANTS.NONSTANDARD)
1442 and wdj.status_type IN (WIP_CONSTANTS.UNRELEASED, WIP_CONSTANTS.RELEASED,
1443 WIP_CONSTANTS.COMP_CHRG, WIP_CONSTANTS.HOLD);
1444 l_qty NUMBER := 0;
1445 BEGIN
1446 for c_wip_negreq_csr in wip_negreq_csr(p_org_id, p_inv_item_id, p_rcpt_date) loop
1447 l_qty := c_wip_negreq_csr.item_qty;
1448 wip_ws_util.trace_log('WIP_WS_SHORTAGE:get_wip_negreq_supply: '||
1449 'wip_negreq_csr.item_qty = '||c_wip_negreq_csr.item_qty);
1450 end loop;
1451 if (l_qty < 0 or l_qty is null) then l_qty := 0; end if;
1452 return l_qty;
1453
1454 EXCEPTION when others then
1455 return 0;
1456 END get_wip_negreq_supply;
1457
1458
1459 /*
1460 * This procedure finds out the supply from repetitive schedule for a particular subassy on a given date
1461 */
1462 FUNCTION get_rep_sch_supply(p_org_id NUMBER, p_inv_item_id NUMBER, p_rcpt_date DATE) RETURN NUMBER IS
1463 CURSOR rep_sched_csr(p_org_id NUMBER, p_inv_item_id NUMBER, p_rcpt_date DATE)IS
1464 SELECT
1465 SUM(MRP_HORIZONTAL_PLAN_SC.compute_daily_rate_t(dates.calendar_code, dates.exception_set_id,
1466 sched.daily_production_rate, sched.quantity_completed,
1467 sched.first_unit_completion_date, dates.calendar_date )) item_qty
1468 FROM bom_calendar_dates dates,
1469 mtl_parameters param,
1470 wip_repetitive_schedules sched,
1471 wip_repetitive_items rep_items
1472 WHERE rep_items.primary_item_id = p_inv_item_id
1473 and rep_items.organization_id = p_org_id
1474 and rep_items.wip_entity_id = sched.wip_entity_id
1475 and rep_items.line_id = sched.line_id
1476 and sched.organization_id = rep_items.organization_id
1477 and sched.status_type IN (WIP_CONSTANTS.UNRELEASED,
1478 WIP_CONSTANTS.RELEASED, WIP_CONSTANTS.COMP_CHRG, WIP_CONSTANTS.HOLD)
1479 and dates.seq_num is not null
1480 and TRUNC(dates.calendar_date) >= TRUNC(sched.first_unit_completion_date)
1481 and TRUNC(dates.calendar_date)
1482 <= (select trunc(cal.calendar_date - 1)
1483 from bom_calendar_dates cal
1484 where cal.exception_set_id = dates.exception_set_id
1485 and cal.calendar_code = dates.calendar_code
1486 and cal.seq_num = (select cal1.prior_seq_num + ceil(sched.processing_work_days)
1487 from bom_calendar_dates cal1
1488 where cal1.exception_set_id = dates.exception_set_id
1489 and cal1.calendar_code = dates.calendar_code
1490 and cal1.calendar_date = TRUNC(sched.first_unit_completion_date)) )
1491 and dates.calendar_date = trunc(p_rcpt_date)
1492 and dates.exception_set_id = param.calendar_exception_set_id
1493 and dates.calendar_code = param.calendar_code
1494 and param.organization_id = rep_items.organization_id;
1495 l_qty NUMBER := 0;
1496 BEGIN
1497 for c_rep_sched_csr in rep_sched_csr(p_org_id, p_inv_item_id, p_rcpt_date) loop
1498 l_qty := c_rep_sched_csr.item_qty;
1499 wip_ws_util.trace_log('WIP_WS_SHORTAGE:get_rep_sch_supply: '||
1500 'rep_sched_csr.item_qty = '||c_rep_sched_csr.item_qty);
1501 end loop;
1502 if (l_qty < 0 or l_qty is null) then l_qty := 0; end if;
1503 return l_qty;
1504
1505 EXCEPTION when others then
1506 return 0;
1507 END get_rep_sch_supply;
1508
1509
1510 /*
1511 * This procedure finds out the supply from purchase order for a particular item on a given date
1512 * The returned qty does not include the qty that is already reserved
1513 */
1514 FUNCTION get_po_supply(p_org_id NUMBER, p_inv_item_id NUMBER, p_rcpt_date DATE) RETURN NUMBER IS
1515 CURSOR po_csr(p_org_id NUMBER, p_inv_item_id NUMBER, p_rcpt_date DATE) IS
1516 SELECT
1517 ms.to_org_primary_quantity item_qty,
1518 (select sum(mr.reservation_quantity)
1519 from mtl_reservations mr
1520 where mr.supply_source_type_id = 1 --po supply
1521 and mr.supply_source_header_id = ms.po_header_id
1522 and mr.supply_source_line_id = ms.po_line_id ) reservation_qty,
1523 pd.PO_HEADER_ID --added for bug 6886708 for logging
1524 FROM po_distributions_all pd,
1525 mtl_supply ms
1526 WHERE ms.item_id = p_inv_item_id
1527 AND ms.to_organization_id = p_org_id
1528 AND ( ms.supply_type_code = 'PO' or
1529 ms.supply_type_code = 'ASN')
1530 AND ms.destination_type_code = 'INVENTORY'
1531 AND trunc(ms.expected_delivery_date) = trunc(p_rcpt_date)
1532 AND pd.po_distribution_id = ms.po_distribution_id
1533 AND ms.po_line_id is not null
1534 AND ms.item_id is not null
1535 AND ms.to_org_primary_quantity > 0
1536 AND NOT EXISTS (select 'y' FROM OE_DROP_SHIP_SOURCES ODSS
1537 WHERE ms.po_line_location_id = ODSS.line_location_id);
1538
1539 l_qty NUMBER := 0;
1540 BEGIN
1541 for c_po_csr in po_csr(p_org_id, p_inv_item_id, p_rcpt_date) loop
1542 l_qty := l_qty + (nvl(c_po_csr.item_qty,0) - nvl(c_po_csr.reservation_qty,0));
1543 wip_ws_util.trace_log('WIP_WS_SHORTAGE:get_po_supply: '||
1544 'po_csr.PO_HEADER_ID = '||c_po_csr.PO_HEADER_ID||
1545 'po_csr.item_qty = '||c_po_csr.item_qty||
1546 'po_csr.reservation_qty = '||c_po_csr.reservation_qty);
1547 end loop;
1548 if(l_qty < 0 or l_qty is null) then l_qty := 0; end if;
1549 return l_qty;
1550
1551 EXCEPTION when others then
1552 return 0;
1553 END get_po_supply;
1554
1555
1556 /*
1557 * This procedure finds out the supply from purchase req for a particular item on a given date
1558 * The returned qty does not include the qty that is already reserved
1559 */
1560 FUNCTION get_req_supply(p_org_id NUMBER, p_inv_item_id NUMBER, p_rcpt_date DATE) RETURN NUMBER IS
1561 CURSOR req_csr(p_org_id NUMBER, p_inv_item_id NUMBER, p_rcpt_date DATE) IS
1562 SELECT
1563 (nvl(ms.to_org_primary_quantity,0) *
1564 pd.req_line_quantity/prl.quantity) item_qty,
1565 (select sum(mr.reservation_quantity)
1566 from mtl_reservations mr
1567 where mr.supply_source_type_id = 18 --po req supply
1568 and mr.supply_source_header_id = ms.req_header_id
1569 and mr.supply_source_line_id = ms.req_line_id ) reservation_qty,
1570 pd.requisition_line_id, --added for bug 6886708 for logging
1571 prl.REQUISITION_HEADER_ID --added for bug 6886708 for logging
1572 FROM po_req_distributions_all pd,
1573 po_requisition_lines_all prl,
1574 mtl_supply ms
1575 WHERE ms.item_id = p_inv_item_id
1576 AND ms.to_organization_id = p_org_id
1577 AND ms.supply_type_code = 'REQ'
1578 AND ms.destination_type_code = 'INVENTORY'
1579 AND trunc(ms.expected_delivery_date) = trunc(p_rcpt_date)
1580 AND pd.requisition_line_id = prl.requisition_line_id
1581 AND prl.requisition_line_id = ms.req_line_id
1582 AND ms.to_org_primary_quantity > 0
1583 AND ms.req_line_id is not null
1584 AND ms.item_id is not null
1585 AND NOT EXISTS (select 'y' FROM OE_DROP_SHIP_SOURCES ODSS
1586 WHERE ms.req_line_id = ODSS.requisition_line_id);
1587 l_qty NUMBER := 0;
1588 BEGIN
1589 for c_req_csr in req_csr(p_org_id, p_inv_item_id, p_rcpt_date) loop
1590 l_qty := l_qty + (c_req_csr.item_qty - nvl(c_req_csr.reservation_qty,0));
1591 wip_ws_util.trace_log('WIP_WS_SHORTAGE:get_req_supply: '||
1592 'req_csr.requisition_line_id = '||c_req_csr.requisition_line_id||
1593 'req_csr.REQUISITION_HEADER_ID = '||c_req_csr.REQUISITION_HEADER_ID||
1594 'req_csr.item_qty = '||c_req_csr.item_qty||
1595 'req_csr.reservation_qty = '||c_req_csr.reservation_qty);
1596 end loop;
1597 if(l_qty < 0 or l_qty is null) then l_qty := 0; end if;
1598 return l_qty;
1599
1600 EXCEPTION when others then
1601 return 0;
1602 END get_req_supply;
1603
1604 /*
1605 * This procedure finds out the supply from instransit shipment for a particular item on a given date
1606 * The returned qty does not include the qty that is already reserved
1607 */
1608 FUNCTION get_intransit_ship_supply(p_org_id NUMBER, p_inv_item_id NUMBER, p_rcpt_date DATE) RETURN NUMBER IS
1609 CURSOR intransit_ship_csr(p_org_id NUMBER, p_inv_item_id NUMBER, p_rcpt_date DATE) IS
1610 SELECT
1611 SUM(nvl(ms.to_org_primary_quantity, 0) * pd.req_line_quantity/pl.quantity)
1612 item_qty,
1613 (select sum(mr.reservation_quantity)
1614 from mtl_reservations mr
1615 where mr.supply_source_type_id = 18 --todo, need to check source type id
1616 and mr.supply_source_header_id = ms.shipment_header_id
1617 and mr.supply_source_line_id = ms.shipment_line_id ) reservation_qty,
1618 pd.requisition_line_id, --added for bug 6886708 for logging
1619 pl.REQUISITION_HEADER_ID --added for bug 6886708 for logging
1620 FROM po_req_distributions_all pd,
1621 po_requisition_lines_all pl,
1622 mtl_supply ms
1623 WHERE ms.item_id = p_inv_item_id
1624 AND ms.to_organization_id = p_org_id
1625 AND ms.supply_type_code = 'SHIPMENT'
1626 AND ms.destination_type_code = 'INVENTORY'
1627 AND pd.requisition_line_id = pl.requisition_line_id
1628 AND pl.quantity > 0
1629 AND pl.requisition_line_id = ms.req_line_id
1630 AND trunc(ms.expected_delivery_date) = trunc(p_rcpt_date)
1631 --AND ms.req_line_id is not null
1632 AND ms.shipment_line_id is not null
1633 AND ms.item_id is not null
1634 AND ms.to_org_primary_quantity > 0;
1635 l_qty NUMBER := 0;
1636 BEGIN
1637 for c_intransit_ship_csr in intransit_ship_csr(p_org_id, p_inv_item_id, p_rcpt_date) loop
1638 l_qty := l_qty + (c_intransit_ship_csr.item_qty - nvl(c_intransit_ship_csr.reservation_qty,0));
1639 wip_ws_util.trace_log('WIP_WS_SHORTAGE:get_intransit_ship_supply: '||
1640 'intransit_ship_csr.requisition_line_id = '||c_intransit_ship_csr.requisition_line_id||
1641 'intransit_ship_csr.REQUISITION_HEADER_ID = '||c_intransit_ship_csr.REQUISITION_HEADER_ID||
1642 'intransit_ship_csr.item_qty = '||c_intransit_ship_csr.item_qty||
1643 'intransit_ship_csr.reservation_qty = '||c_intransit_ship_csr.reservation_qty);
1644 end loop;
1645 if(l_qty < 0 or l_qty is null) then l_qty := 0; end if;
1646 return l_qty;
1647
1648 EXCEPTION when others then
1649 return 0;
1650 END get_intransit_ship_supply;
1651
1652
1653 /*
1654 * This procedure finds out the supply from intransit receipt for a particular item on a given date
1655 * The returned qty does not include the qty that is already reserved
1656 */
1657 FUNCTION get_intransit_receipt_supply(p_org_id NUMBER, p_inv_item_id NUMBER, p_rcpt_date DATE) RETURN NUMBER IS
1658 CURSOR intransit_receipt_csr(p_org_id NUMBER, p_inv_item_id NUMBER, p_rcpt_date DATE) IS
1659
1660 SELECT
1661 nvl(ms.TO_ORG_PRIMARY_QUANTITY, 0) * pd.req_line_quantity /
1662 pl.quantity item_qty,
1663 (select sum(mr.reservation_quantity)
1664 from mtl_reservations mr
1665 where mr.supply_source_type_id = 18 --todo, need to check source type id
1666 and mr.supply_source_header_id = ms.shipment_header_id
1667 and mr.supply_source_line_id = ms.shipment_line_id ) reservation_qty
1668 FROM po_requisition_lines_all pl,
1669 po_req_distributions_all pd,
1670 mtl_supply ms
1671 WHERE ms.item_id = p_inv_item_id
1672 AND ms.to_organization_id = p_org_id
1673 AND ms.supply_type_code = 'RECEIVING'
1674 AND ms.destination_type_code = 'INVENTORY'
1675 AND pd.requisition_line_id = pl.requisition_line_id
1676 AND trunc(ms.expected_delivery_date) = trunc(p_rcpt_date)
1677 AND pl.quantity > 0
1678 AND ms.req_line_id = pl.requisition_line_id
1679 AND ms.po_distribution_id is null
1680 AND ms.item_id is not null
1681 AND ms.to_org_primary_quantity > 0
1682 AND NOT EXISTS (select 'y' FROM OE_DROP_SHIP_SOURCES ODSS
1683 WHERE ms.req_line_id = ODSS.requisition_line_id)
1684 UNION ALL
1685 SELECT
1686 SUM(ms.to_org_primary_quantity) item_qty,
1687 (select sum(mr.reservation_quantity)
1688 from mtl_reservations mr
1689 where mr.supply_source_type_id = 18 --todo, need to check source type id
1690 and mr.supply_source_header_id = ms.shipment_header_id
1691 and mr.supply_source_line_id = ms.shipment_line_id ) reservation_qty
1692 FROM mtl_secondary_inventories msub,
1693 mtl_supply ms
1694 WHERE ms.item_id = p_inv_item_id
1695 AND ms.to_organization_id = p_org_id
1696 AND ms.supply_type_code = 'RECEIVING'
1697 AND ms.destination_type_code = 'INVENTORY'
1698 AND ms.to_organization_id = msub.organization_id(+)
1699 AND ms.to_subinventory = msub.secondary_inventory_name(+)
1700 AND trunc(ms.expected_delivery_date) = trunc(p_rcpt_date)
1701 AND ms.req_line_id is null
1702 AND ms.po_distribution_id is null
1703 AND ms.item_id is not null
1704 AND ms.to_org_primary_quantity > 0;
1705 l_qty NUMBER := 0;
1706 BEGIN
1707 for c_intransit_receipt_csr in intransit_receipt_csr(p_org_id, p_inv_item_id, p_rcpt_date) loop
1708 l_qty := l_qty + (c_intransit_receipt_csr.item_qty - nvl(c_intransit_receipt_csr.reservation_qty,0));
1709 wip_ws_util.trace_log('WIP_WS_SHORTAGE:get_intransit_receipt_supply: '||
1710 'c_intransit_receipt_csr.item_qty = '||c_intransit_receipt_csr.item_qty||
1711 'c_intransit_receipt_csr.reservation_qty = '||c_intransit_receipt_csr.reservation_qty);
1712 end loop;
1713 if(l_qty < 0 or l_qty is null) then l_qty := 0; end if;
1714 return l_qty;
1715
1716 EXCEPTION when others then
1717 return 0;
1718 END get_intransit_receipt_supply;
1719
1720
1721 /*
1722 * This procedure finds out the supply from po in rcvng for a particular item on a given date
1723 * The returned qty does not include the qty that is already reserved
1724 */
1725 FUNCTION get_po_rcv_supply(p_org_id NUMBER, p_inv_item_id NUMBER, p_rcpt_date DATE) RETURN NUMBER IS
1726 CURSOR po_rcv_csr(p_org_id NUMBER, p_inv_item_id NUMBER, p_rcpt_date DATE) IS
1727 SELECT
1728 ms.to_org_primary_quantity item_qty,
1729 (select sum(mr.reservation_quantity)
1730 from mtl_reservations mr
1731 where mr.supply_source_type_id = 18 --todo, need to check source type id
1732 and mr.supply_source_header_id = ms.shipment_header_id
1733 and mr.supply_source_line_id = ms.shipment_line_id ) reservation_qty,
1734 pd.PO_HEADER_ID --added for bug 6886708 for logging
1735 FROM po_distributions_all pd,
1736 mtl_supply ms
1737 WHERE ms.item_id = p_inv_item_id
1738 AND ms.to_organization_id = p_org_id
1739 AND ms.supply_type_code = 'RECEIVING'
1740 AND ms.destination_type_code = 'INVENTORY'
1741 AND trunc(ms.expected_delivery_date) = trunc(p_rcpt_date)
1742 AND pd.po_distribution_id = ms.po_distribution_id
1743 and ms.item_id is not null
1744 AND ms.to_org_primary_quantity > 0
1745 AND NOT EXISTS (select 'y' FROM OE_DROP_SHIP_SOURCES ODSS
1746 WHERE ms.po_line_location_id = ODSS.line_location_id);
1747 l_qty NUMBER := 0;
1748 BEGIN
1749 for c_po_rcv_csr in po_rcv_csr(p_org_id, p_inv_item_id, p_rcpt_date) loop
1750 l_qty := l_qty + (c_po_rcv_csr.item_qty - nvl(c_po_rcv_csr.reservation_qty,0));
1751 wip_ws_util.trace_log('WIP_WS_SHORTAGE:get_po_rcv_supply: '||
1752 'c_po_rcv_csr.PO_HEADER_ID = '||c_po_rcv_csr.PO_HEADER_ID||
1753 'c_po_rcv_csr.item_qty = '||c_po_rcv_csr.item_qty||
1754 'c_po_rcv_csr.reservation_qty = '||c_po_rcv_csr.reservation_qty);
1755 end loop;
1756 if(l_qty < 0 or l_qty is null) then l_qty := 0; end if;
1757 return l_qty;
1758
1759 EXCEPTION when others then
1760 return 0;
1761 END get_po_rcv_supply;
1762
1763
1764 /*
1765 * This functions finds out the expected rcpt qty for an item on a given
1766 * date. The qty is in primary uom. This procedure will include only loose
1767 * qty as expected receipt. If supply is tied with some reservation,
1768 * then its not considered as supply
1769 */
1770 FUNCTION calc_expected_receipts(p_org_id NUMBER, p_inv_item_id NUMBER, p_rcpt_date DATE) RETURN NUMBER IS
1771 wip_supply NUMBER := 0;
1772 neg_wip_supply NUMBER := 0;
1773 flow_supply NUMBER := 0;
1774 rep_sch_supply NUMBER := 0;
1775 po_supply NUMBER := 0;
1776 intransit_ship_supply NUMBER := 0;
1777 req_supply NUMBER := 0;
1778 intransit_receipt_supply NUMBER := 0;
1779 po_rcv_supply NUMBER := 0;
1780 BEGIN
1781 wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_expected_receipts: '||
1782 'p_org_id = '||p_org_id||
1783 'p_inv_item_id = '||p_inv_item_id||
1784 'p_rcpt_date = '||p_rcpt_date);
1785
1786 wip_supply := get_wip_supply(p_org_id, p_inv_item_id, p_rcpt_date);
1787 wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_expected_receipts: wip_supply = '||wip_supply);
1788 neg_wip_supply := get_wip_negreq_supply(p_org_id, p_inv_item_id, p_rcpt_date);
1789 wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_expected_receipts: neg_wip_supply = '||neg_wip_supply);
1790 flow_supply := get_flow_supply(p_org_id, p_inv_item_id, p_rcpt_date);
1791 wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_expected_receipts: flow_supply = '||flow_supply);
1792 rep_sch_supply := get_rep_sch_supply(p_org_id, p_inv_item_id, p_rcpt_date);
1793 wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_expected_receipts: rep_sch_supply = '||rep_sch_supply);
1794 po_supply := get_po_supply(p_org_id, p_inv_item_id, p_rcpt_date);
1795 wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_expected_receipts: po_supply = '||po_supply);
1796 intransit_ship_supply := get_intransit_ship_supply(p_org_id, p_inv_item_id, p_rcpt_date);
1797 wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_expected_receipts: intransit_ship_supply = '||intransit_ship_supply);
1798 req_supply := get_req_supply(p_org_id, p_inv_item_id, p_rcpt_date);
1799 wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_expected_receipts: req_supply = '||req_supply);
1800 intransit_receipt_supply := get_intransit_receipt_supply(p_org_id, p_inv_item_id, p_rcpt_date);
1801 wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_expected_receipts: intransit_receipt_supply = '||intransit_receipt_supply);
1802 po_rcv_supply := get_po_rcv_supply(p_org_id, p_inv_item_id, p_rcpt_date);
1803 wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_expected_receipts: po_rcv_supply = '||po_rcv_supply);
1804
1805 return (wip_supply + neg_wip_supply + flow_supply + rep_sch_supply + po_supply +
1806 intransit_ship_supply + req_supply + intransit_receipt_supply + po_rcv_supply);
1807
1808 END calc_expected_receipts;
1809
1810
1811 /*
1812 * This procedure finds out the expected rcpt for each component in component temp table
1813 * for a given date and bumps up the projected available qty with rcpt qty
1814 */
1815 PROCEDURE calc_expected_receipts(p_org_id NUMBER, p_rcpt_date DATE) IS
1816 CURSOR comp IS
1817 select inventory_item_id
1818 from wip_ws_critical_comp_temp
1819 where organization_id = p_org_id
1820 and supply_subinventory is null;
1821 l_rcpt NUMBER;
1822
1823 BEGIN
1824 wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_expected_receipts:Entered');
1825 for c_comp in comp LOOP
1826 l_rcpt := calc_expected_receipts(p_org_id, c_comp.inventory_item_id, p_rcpt_date);
1827 if(nvl(l_rcpt, -1) < 0) then l_rcpt := 0; end if;
1828 update wip_ws_critical_comp_temp
1829 set PROJECTED_AVAIL_QTY = PROJECTED_AVAIL_QTY + l_rcpt
1830 where organization_id = p_org_id
1831 and inventory_item_id = c_comp.inventory_item_id
1832 and supply_subinventory is null;
1833 END LOOP;
1834 END calc_expected_receipts;
1835
1836
1837 /*
1838 * This procedure update the resource information in resource temp table
1839 */
1840 PROCEDURE update_res_shift_avail(p_org_id NUMBER, p_dept_id NUMBER, p_resource_id NUMBER,
1841 p_res_avail_date DATE, p_shift_num NUMBER,
1842 p_onhand_qty NUMBER, p_proj_onhand NUMBER) IS
1843 BEGIN
1844 update
1845 wip_ws_critical_res_temp
1846 set
1847 resource_avail_date = p_res_avail_date,
1848 resource_shift_num = p_shift_num,
1849 onhand_qty = p_onhand_qty,
1850 projected_avail_qty = p_proj_onhand
1851 where
1852 organization_id = p_org_id and
1853 department_id = p_dept_id and
1854 resource_id = p_resource_id;
1855
1856 END update_res_shift_avail;
1857
1858
1859 /*
1860 * Loops over the critical job op components pl/sql table and inserts each record into component
1861 * shortage table
1862 */
1863 PROCEDURE insert_components IS
1864 BEGIN
1865 wip_ws_util.log_time('insert_components: Inserting component shortage records');
1866 wip_ws_util.trace_log('WIP_WS_SHORTAGE:insert_components:Entered: Number of records to insert='||g_wip_job_critical_comp_tbl.COUNT);
1867 --FOR i in 1..g_wip_job_critical_comp_tbl.COUNT LOOP
1868 IF(g_wip_job_critical_comp_tbl.COUNT > 0) THEN
1869 FOR i in g_wip_job_critical_comp_tbl.FIRST..g_wip_job_critical_comp_tbl.LAST LOOP
1870 wip_ws_util.trace_log('WIP_WS_SHORTAGE:insert_components:inv_item='||g_wip_job_critical_comp_tbl(i).INVENTORY_ITEM_ID||
1871 ',org_id='||g_wip_job_critical_comp_tbl(i).ORGANIZATION_ID||
1872 ',wip_entity_id='||g_wip_job_critical_comp_tbl(i).WIP_ENTITY_ID||
1873 ',operation_seq_num='||g_wip_job_critical_comp_tbl(i).OPERATION_SEQ_NUM);
1874 insert into wip_ws_comp_shortage(
1875 ORGANIZATION_ID,
1876 WIP_ENTITY_ID,
1877 OPERATION_SEQ_NUM,
1878 INVENTORY_ITEM_ID,
1879 DEPARTMENT_ID,
1880 PRIMARY_UOM_CODE,
1881 DATE_REQUIRED,
1882 REQUIRED_QTY,
1883 QUANTITY_ISSUED,
1884 QUANTITY_OPEN,
1885 WIP_SUPPLY_TYPE,
1886 SUPPLY_SUBINVENOTRY,
1887 SUPPLY_LOCATOR_ID,
1888 ONHAND_QTY,
1889 PROJ_AVAIL_QTY,
1890 SHORTAGE_QTY,
1891 LAST_UPDATE_DATE,
1892 LAST_UPDATED_BY,
1893 CREATION_DATE,
1894 CREATED_BY,
1895 LAST_UPDATE_LOGIN,
1896 REQUEST_ID,
1897 PROGRAM_APPLICATION_ID,
1898 PROGRAM_ID,
1899 OBJECT_VERSION_NUMBER,
1900 PROGRAM_RUN_DATE
1901 )values(
1902 g_wip_job_critical_comp_tbl(i).ORGANIZATION_ID,
1903 g_wip_job_critical_comp_tbl(i).WIP_ENTITY_ID,
1904 g_wip_job_critical_comp_tbl(i).OPERATION_SEQ_NUM,
1905 g_wip_job_critical_comp_tbl(i).INVENTORY_ITEM_ID,
1906 g_wip_job_critical_comp_tbl(i).DEPARTMENT_ID,
1907 g_wip_job_critical_comp_tbl(i).PRIMARY_UOM_CODE,
1908 g_wip_job_critical_comp_tbl(i).DATE_REQUIRED,
1909 g_wip_job_critical_comp_tbl(i).REQUIRED_QTY,
1910 g_wip_job_critical_comp_tbl(i).QUANTITY_ISSUED,
1911 g_wip_job_critical_comp_tbl(i).QUANTITY_OPEN,
1912 g_wip_job_critical_comp_tbl(i).WIP_SUPPLY_TYPE,
1913 g_wip_job_critical_comp_tbl(i).SUPPLY_SUBINVENOTRY,
1914 g_wip_job_critical_comp_tbl(i).SUPPLY_LOCATOR_ID,
1915 g_wip_job_critical_comp_tbl(i).ONHAND_QTY,
1916 g_wip_job_critical_comp_tbl(i).PROJ_AVAIL_QTY,
1917 g_wip_job_critical_comp_tbl(i).SHORTAGE_QTY,
1918 sysdate,
1919 g_user_id,
1920 sysdate,
1921 g_user_id,
1922 g_login_id,
1923 g_request_id,
1924 g_prog_appid,
1925 g_prog_id,
1926 g_init_obj_ver,
1927 g_prog_run_date
1928 );
1929 END LOOP;
1930 END IF;
1931 wip_ws_util.log_time('insert_components: Done with inserting components');
1932
1933 END insert_components;
1934
1935
1936 /*
1937 * Loops over the critical job op resources pl/sql table and inserts each record into resource
1938 * shortage table
1939 */
1940 PROCEDURE insert_resources IS
1941 BEGIN
1942 wip_ws_util.trace_log('WIP_WS_SHORTAGE:insert_resources:Entered: Number of records to insert='||g_wip_job_critical_res_tbl.COUNT);
1943 wip_ws_util.log_time('insert_resources: Inserting resource shortage records');
1944 --FOR i in 1..g_wip_job_critical_res_tbl.COUNT LOOP
1945 IF(g_wip_job_critical_res_tbl.COUNT > 0) THEN
1946 FOR i in g_wip_job_critical_res_tbl.FIRST..g_wip_job_critical_res_tbl.LAST LOOP
1947 wip_ws_util.trace_log('WIP_WS_SHORTAGE:insert_resources:resource'||g_wip_job_critical_res_tbl(i).RESOURCE_ID||
1948 ',org_id='||g_wip_job_critical_res_tbl(i).ORGANIZATION_ID||
1949 ',wip_entity_id='||g_wip_job_critical_res_tbl(i).WIP_ENTITY_ID||
1950 ',operation_seq_num='||g_wip_job_critical_res_tbl(i).OPERATION_SEQ_NUM);
1951
1952 insert into wip_ws_res_shortage(
1953 ORGANIZATION_ID,
1954 WIP_ENTITY_ID,
1955 OPERATION_SEQ_NUM,
1956 RESOURCE_ID,
1957 DEPARTMENT_ID,
1958 DATE_REQUIRED,
1959 REQUIRED_QTY,
1960 QUANTITY_ISSUED,
1961 QUANTITY_OPEN,
1962 RESOURCE_AVAIL,
1963 RESOURCE_PROJ_AVAIL,
1964 RESOURCE_SHORTAGE,
1965 PRIMARY_UOM_CODE,
1966 LAST_UPDATE_DATE,
1967 LAST_UPDATED_BY,
1968 CREATION_DATE,
1969 CREATED_BY,
1970 LAST_UPDATE_LOGIN,
1971 REQUEST_ID,
1972 PROGRAM_APPLICATION_ID,
1973 PROGRAM_ID,
1974 OBJECT_VERSION_NUMBER,
1975 PROGRAM_RUN_DATE
1976 )values(
1977 g_wip_job_critical_res_tbl(i).ORGANIZATION_ID,
1978 g_wip_job_critical_res_tbl(i).WIP_ENTITY_ID,
1979 g_wip_job_critical_res_tbl(i).OPERATION_SEQ_NUM,
1980 g_wip_job_critical_res_tbl(i).RESOURCE_ID,
1981 g_wip_job_critical_res_tbl(i).DEPARTMENT_ID,
1982 g_wip_job_critical_res_tbl(i).DATE_REQUIRED,
1983 g_wip_job_critical_res_tbl(i).REQUIRED_QTY,
1984 g_wip_job_critical_res_tbl(i).QUANTITY_ISSUED,
1985 g_wip_job_critical_res_tbl(i).QUANTITY_OPEN,
1986 g_wip_job_critical_res_tbl(i).RESOURCE_AVAIL,
1987 g_wip_job_critical_res_tbl(i).RESOURCE_PROJ_AVAIL,
1988 g_wip_job_critical_res_tbl(i).RESOURCE_SHORTAGE,
1989 g_wip_job_critical_res_tbl(i).PRIMARY_UOM_CODE,
1990 sysdate,
1991 g_user_id,
1992 sysdate,
1993 g_user_id,
1994 g_login_id,
1995 g_request_id,
1996 g_prog_appid,
1997 g_prog_id,
1998 g_init_obj_ver,
1999 g_prog_run_date
2000 );
2001 END LOOP;
2002 END IF;
2003 wip_ws_util.log_time('insert_resources: Done with resource insertion');
2004 /*
2005 exception when others then
2006 null;
2007 */
2008 END insert_resources;
2009
2010
2011 /*
2012 * Delete all component records from comp shortage table for a given org
2013 */
2014 PROCEDURE delete_components (p_org_id NUMBER) IS
2015 BEGIN
2016 wip_ws_util.log_time('delete_components: Starting to delete org components');
2017 delete from wip_ws_comp_shortage
2018 where organization_id = p_org_id;
2019 wip_ws_util.log_time('delete_components: Done with deleting org components');
2020 END delete_components;
2021
2022
2023 /*
2024 * Delete all resource records from res shortage table for a given org
2025 */
2026 PROCEDURE delete_resources(p_org_id NUMBER) IS
2027 BEGIN
2028 wip_ws_util.log_time('delete_resources: Starting to delete org resources');
2029 delete from wip_ws_res_shortage
2030 where organization_id = p_org_id;
2031 wip_ws_util.log_time('delete_resources: Done with deleting org resources');
2032 END delete_resources;
2033
2034
2035 /*
2036 * This procedure is responsible for deleting the old records from comp and res shortage tables
2037 * and populate the newly calculted data present in pl/sql tables
2038 */
2039 PROCEDURE write_db(p_org_id NUMBER) IS
2040 BEGIN
2041 wip_ws_util.log_time('write_db: Entering write_db');
2042 wip_ws_util.trace_log('WIP_WS_SHORTAGE:write_db:Entered');
2043 delete_components(p_org_id);
2044 wip_ws_util.trace_log('WIP_WS_SHORTAGE:write_db:done with delete_components');
2045 insert_components;
2046 wip_ws_util.trace_log('WIP_WS_SHORTAGE:write_db:done with insert_components');
2047 delete_resources(p_org_id);
2048 wip_ws_util.trace_log('WIP_WS_SHORTAGE:write_db:done with delete_resources');
2049 insert_resources;
2050
2051 wip_ws_util.trace_log('WIP_WS_SHORTAGE:write_db:Finished');
2052 wip_ws_util.log_time('write_db: Done with write_db');
2053 END write_db;
2054
2055
2056 /*
2057 * This is the main procedure for calculating resource shortage. It first find out the
2058 * critical resources. Then it loop over the job ops and find out the critical job op
2059 * resources. Then it loops over the critical job op resources and calcultes the availability
2060 * and shortage numbers for each job op resource
2061 */
2062 PROCEDURE calc_res_shortage (p_org_id NUMBER) IS
2063 i NUMBER;
2064 l_item_project_avail_qty NUMBER;
2065 current_res_req_date DATE;
2066 current_res_shift_num NUMBER;
2067 l_res_remain_qty NUMBER;
2068 l_res_shortage NUMBER;
2069 l_res_onhand_qty NUMBER;
2070 l_res_avail_date DATE;
2071 l_res_avail_shift NUMBER;
2072 l_res_project_avail_qty NUMBER;
2073
2074 CURSOR critical_res_csr (p_org_id NUMBER, p_dept_id NUMBER, p_res_id NUMBER) IS
2075 select department_id,
2076 resource_id,
2077 onhand_qty,
2078 projected_avail_qty,
2079 resource_avail_date,
2080 resource_shift_num
2081 from wip_ws_critical_res_temp
2082 where organization_id = p_org_id
2083 and department_id = p_dept_id
2084 and resource_id = p_res_id;
2085
2086 BEGIN
2087 wip_ws_util.log_time('calc_res_shortage: Entering calc_res_shortage');
2088 wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_res_shortage:Entered');
2089 get_job_critical_resources(p_org_id, g_period_end_time);
2090 wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_res_shortage:Done with get_job_critical_resources ');
2091
2092 IF(g_wip_job_critical_res_tbl.COUNT > 0) THEN
2093 FOR i in g_wip_job_critical_res_tbl.FIRST .. g_wip_job_critical_res_tbl.LAST LOOP
2094 current_res_req_date := g_wip_job_critical_res_tbl(i).DATE_REQUIRED;
2095 current_res_shift_num := g_wip_job_critical_res_tbl(i).shift_num;
2096 --if this resource req is in past, make it work in current shift
2097 --shift num is already reflecting the current shift from get_job_critical_resources procedure
2098 if(current_res_req_date < sysdate) then
2099 current_res_req_date := sysdate;
2100 end if;
2101
2102 wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_res_shortage:Point 20: Enter loop for: '||
2103 'wip_ent_id='||g_wip_job_critical_res_tbl(i).WIP_ENTITY_ID||
2104 ',op_seq_num='||g_wip_job_critical_res_tbl(i).OPERATION_SEQ_NUM||
2105 ',dept_id='||g_wip_job_critical_res_tbl(i).DEPARTMENT_ID||
2106 ',res_id='||g_wip_job_critical_res_tbl(i).RESOURCE_ID
2107 );
2108 for c_critical_res_csr in critical_res_csr(p_org_id,
2109 g_wip_job_critical_res_tbl(i).DEPARTMENT_ID,
2110 g_wip_job_critical_res_tbl(i).RESOURCE_ID) loop
2111 l_res_avail_date := c_critical_res_csr.resource_avail_date;
2112 l_res_avail_shift := c_critical_res_csr.resource_shift_num;
2113 l_res_project_avail_qty := c_critical_res_csr.projected_avail_qty;
2114 l_res_onhand_qty := c_critical_res_csr.onhand_qty;
2115
2116 wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_res_shortage:Point 30: Found resource in temp table: '||
2117 'l_res_avail_date='||l_res_avail_date||
2118 ',l_res_avail_shift='||l_res_avail_shift||
2119 ',l_res_project_avail_qty='||l_res_project_avail_qty||
2120 ',l_res_onhand_qty='||l_res_onhand_qty
2121 );
2122
2123 end loop;
2124
2125 --first time this row is accessed
2126 if((l_res_avail_date is NULL) OR (l_res_avail_shift is null)) then
2127 l_res_onhand_qty := wip_ws_dl_util.get_shift_capacity(
2128 p_org_id,
2129 g_wip_job_critical_res_tbl(i).DEPARTMENT_ID,
2130 g_wip_job_critical_res_tbl(i).RESOURCE_ID,
2131 g_wip_job_critical_res_tbl(i).SHIFT_SEQ,
2132 g_wip_job_critical_res_tbl(i).SHIFT_NUM);
2133
2134 wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_res_shortage:Point 40: resource accessed first time:'||
2135 'l_res_onhand_qty='||l_res_onhand_qty
2136 );
2137 end if;
2138
2139 if(
2140 trunc(l_res_avail_date) = trunc(current_res_req_date) AND
2141 l_res_avail_shift = g_wip_job_critical_res_tbl(i).SHIFT_NUM) then
2142 --found a job resource working in the same day and shift as critical res record
2143 l_res_onhand_qty := l_res_project_avail_qty;
2144
2145 wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_res_shortage:Point 50: resource working in same day and shift as critical res found'||
2146 'l_res_onhand_qty='||l_res_onhand_qty
2147 );
2148
2149 else
2150 --found a job resource that is working on a different date or shift then critical record
2151 --in this case we need to refill the resource availability
2152 l_res_onhand_qty := wip_ws_dl_util.get_shift_capacity(
2153 p_org_id,
2154 g_wip_job_critical_res_tbl(i).DEPARTMENT_ID,
2155 g_wip_job_critical_res_tbl(i).RESOURCE_ID,
2156 g_wip_job_critical_res_tbl(i).SHIFT_SEQ,
2157 g_wip_job_critical_res_tbl(i).SHIFT_NUM);
2158
2159 wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_res_shortage:Point 60: resource working in difference day or shift as critical res found'||
2160 'l_res_onhand_qty='||l_res_onhand_qty
2161 );
2162
2163 end if;
2164
2165 if(l_res_onhand_qty >= g_wip_job_critical_res_tbl(i).QUANTITY_OPEN) then
2166 l_res_shortage := 0;
2167 else
2168 l_res_shortage := g_wip_job_critical_res_tbl(i).QUANTITY_OPEN - l_res_onhand_qty;
2169 end if;
2170 l_res_remain_qty := l_res_onhand_qty - g_wip_job_critical_res_tbl(i).QUANTITY_OPEN;
2171
2172 wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_res_shortage:Point 70:'||
2173 ',l_res_onhand_qty='||l_res_onhand_qty||
2174 ',l_res_shortage='||l_res_shortage||
2175 ',l_res_remain_qty='||l_res_remain_qty
2176 );
2177
2178 if(l_res_remain_qty < 0) then l_res_remain_qty := 0; end if;
2179
2180 --update job resource record with availability/shortage info
2181 g_wip_job_critical_res_tbl(i).RESOURCE_AVAIL := l_res_onhand_qty;
2182 --g_wip_job_critical_res_tbl(i).RESOURCE_PROJ_AVAIL := l_res_remain_qty;
2183 g_wip_job_critical_res_tbl(i).RESOURCE_PROJ_AVAIL := l_res_onhand_qty;
2184 g_wip_job_critical_res_tbl(i).RESOURCE_SHORTAGE := l_res_shortage;
2185
2186 wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_res_shortage:Point 80:Calling update in temp table');
2187
2188 --update critical resource record with availability info
2189 update_res_shift_avail(p_org_id, g_wip_job_critical_res_tbl(i).DEPARTMENT_ID,
2190 g_wip_job_critical_res_tbl(i).RESOURCE_ID, trunc(current_res_req_date),
2191 g_wip_job_critical_res_tbl(i).SHIFT_NUM, l_res_onhand_qty, l_res_remain_qty);
2192
2193 wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_res_shortage:Point 90:Done update in temp table');
2194 END LOOP;
2195 END IF;
2196 END calc_res_shortage;
2197
2198
2199 /*
2200 * This is the main procedure for calculating component shortage. It first find out the
2201 * critical components. Then it calls the procedure to find out job ops that are within this
2202 * date range and based on job status preferences. Then it loop over the job ops and find out
2203 * the critical job op components. Then it loops over the critical job op components and calculates
2204 * the availability and shortage numbers for each job op component
2205 */
2206 PROCEDURE calc_comp_shortage (p_org_id NUMBER) IS
2207 previous_jobop_comp_start_time DATE;
2208 current_jobop_comp_start_time DATE;
2209 l_inv_item_id NUMBER;
2210 l_supply_subinv VARCHAR2(10);
2211 l_item_onhand_qty NUMBER;
2212 l_item_project_avail_qty NUMBER;
2213 i NUMBER;
2214
2215
2216 CURSOR critical_comp_csr IS
2217 select rowid,
2218 organization_id,
2219 inventory_item_id,
2220 supply_subinventory,
2221 nvl(onhand_qty,0) onhand_qty,
2222 nvl(projected_avail_qty,0) projected_avail_qty
2223 from wip_ws_critical_comp_temp
2224 where organization_id = p_org_id
2225 and inventory_item_id = l_inv_item_id
2226 and nvl(supply_subinventory, 'NULL') = nvl(l_supply_subinv, 'NULL');
2227
2228 BEGIN
2229 --this is the main procedure responsible for calculating component shortages
2230 wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_comp_shortage:Entered');
2231
2232 get_pref_critical_components (p_org_id, g_period_end_time);
2233 wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_comp_shortage:Returned from get_pref_critical_components');
2234 get_job_ops(p_org_id, g_period_end_time);
2235 wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_comp_shortage:Returned from get_job_ops');
2236 get_job_critical_components(p_org_id, g_period_end_time);
2237 wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_comp_shortage:Returned from get_job_critical_components');
2238
2239 previous_jobop_comp_start_time := null;
2240 current_jobop_comp_start_time := null;
2241 IF(g_wip_job_critical_comp_tbl.COUNT > 0) THEN
2242 FOR i in g_wip_job_critical_comp_tbl.FIRST .. g_wip_job_critical_comp_tbl.LAST LOOP
2243 wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_comp_shortage:job_critical_comp_loop point1, inv_item_id='||
2244 g_wip_job_critical_comp_tbl(i).INVENTORY_ITEM_ID||
2245 ',date_req='||g_wip_job_critical_comp_tbl(i).DATE_REQUIRED||
2246 ',subinv='||g_wip_job_critical_comp_tbl(i).SUPPLY_SUBINVENOTRY||
2247 ',quantity_open='||g_wip_job_critical_comp_tbl(i).QUANTITY_OPEN);
2248
2249 current_jobop_comp_start_time := g_wip_job_critical_comp_tbl(i).DATE_REQUIRED;
2250 if(g_org_comp_calc_rec.inc_expected_rcpts = 1) then
2251 wip_ws_util.trace_log('WIP_WS_SHORTAGE:ks_debug, inc exp receipt=1');
2252 wip_ws_util.trace_log('WIP_WS_SHORTAGE:ks_debug, previous_jobop_comp_start_date='||to_char(previous_jobop_comp_start_time, 'DD-MON-YYYY HH24:MI:SS'));
2253 wip_ws_util.trace_log('WIP_WS_SHORTAGE:ks_debug, previous_jobop_comp_start_time='||get_time_in_secs(previous_jobop_comp_start_time));
2254 wip_ws_util.trace_log('WIP_WS_SHORTAGE:ks_debug, current_jobop_comp_start_date='||to_char(current_jobop_comp_start_time, 'DD-MON-YYYY HH24:MI:SS'));
2255 wip_ws_util.trace_log('WIP_WS_SHORTAGE:ks_debug, current_jobop_comp_start_time='||get_time_in_secs(current_jobop_comp_start_time));
2256 wip_ws_util.trace_log('WIP_WS_SHORTAGE:ks_debug, supply_cutoff_time='||g_org_comp_calc_rec.supply_cutoff_time_in_sec);
2257
2258 --previous job op and current job op are on same day
2259 if(trunc(previous_jobop_comp_start_time) = trunc(current_jobop_comp_start_time)) then
2260 if(get_time_in_secs(previous_jobop_comp_start_time) < g_org_comp_calc_rec.supply_cutoff_time_in_sec AND
2261 get_time_in_secs(current_jobop_comp_start_time) >= g_org_comp_calc_rec.supply_cutoff_time_in_sec) then
2262 wip_ws_util.trace_log('WIP_WS_SHORTAGE:ks_debug, include expected receipt code called for same day');
2263 calc_expected_receipts(p_org_id, trunc(current_jobop_comp_start_time));
2264 end if;
2265 end if;
2266
2267 --no previous job op and current job start time is past rcpt time, should happen for first jobop in list only
2268 if(previous_jobop_comp_start_time is null) then
2269 if (get_time_in_secs(current_jobop_comp_start_time) >= g_org_comp_calc_rec.supply_cutoff_time_in_sec) then
2270 wip_ws_util.trace_log('WIP_WS_SHORTAGE:ks_debug, include expected receipt code called for prev day = null');
2271 calc_expected_receipts(p_org_id, trunc(current_jobop_comp_start_time));
2272 end if;
2273 end if;
2274
2275 --previous job op was on previous day and current job op is current day, this would be executed for first job of day only
2276 if (trunc(previous_jobop_comp_start_time) < trunc(current_jobop_comp_start_time)) then
2277 if (get_time_in_secs(current_jobop_comp_start_time) >= g_org_comp_calc_rec.supply_cutoff_time_in_sec) then
2278 wip_ws_util.trace_log('WIP_WS_SHORTAGE:ks_debug, include expected receipt code called for first job of day');
2279 calc_expected_receipts(p_org_id, trunc(current_jobop_comp_start_time));
2280 end if;
2281 end if;
2282
2283 end if;
2284 l_inv_item_id := g_wip_job_critical_comp_tbl(i).INVENTORY_ITEM_ID;
2285 l_supply_subinv := g_wip_job_critical_comp_tbl(i).SUPPLY_SUBINVENOTRY;
2286
2287 wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_comp_shortage:opening critical comp avail csr');
2288 for c_critical_comp_csr in critical_comp_csr loop
2289 l_item_project_avail_qty := c_critical_comp_csr.projected_avail_qty;
2290 l_item_onhand_qty := c_critical_comp_csr.onhand_qty;
2291 wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_comp_shortage:projected_avail='||l_item_project_avail_qty||', onhand='||l_item_onhand_qty||', rowid='||c_critical_comp_csr.rowid);
2292 end loop;
2293
2294 if(l_item_project_avail_qty < 0) then l_item_project_avail_qty := 0; end if;
2295 g_wip_job_critical_comp_tbl(i).ONHAND_QTY := l_item_onhand_qty;
2296 g_wip_job_critical_comp_tbl(i).PROJ_AVAIL_QTY := l_item_project_avail_qty;
2297 if(g_wip_job_critical_comp_tbl(i).PROJ_AVAIL_QTY < 0 ) then
2298 g_wip_job_critical_comp_tbl(i).SHORTAGE_QTY := g_wip_job_critical_comp_tbl(i).QUANTITY_OPEN;
2299 elsif(g_wip_job_critical_comp_tbl(i).PROJ_AVAIL_QTY >= g_wip_job_critical_comp_tbl(i).QUANTITY_OPEN) then
2300 g_wip_job_critical_comp_tbl(i).SHORTAGE_QTY := 0;
2301 elsif(g_wip_job_critical_comp_tbl(i).PROJ_AVAIL_QTY < g_wip_job_critical_comp_tbl(i).QUANTITY_OPEN) then
2302 g_wip_job_critical_comp_tbl(i).SHORTAGE_QTY := g_wip_job_critical_comp_tbl(i).QUANTITY_OPEN - g_wip_job_critical_comp_tbl(i).PROJ_AVAIL_QTY;
2303 end if;
2304
2305 if(l_item_project_avail_qty < 0) then
2306 l_item_project_avail_qty := 0;
2307 else
2308 l_item_project_avail_qty := l_item_project_avail_qty - g_wip_job_critical_comp_tbl(i).QUANTITY_OPEN;
2309 if(l_item_project_avail_qty < 0 ) then
2310 l_item_project_avail_qty := 0;
2311 end if;
2312 end if;
2313 wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_comp_shortage: after consumption projected_avail='||l_item_project_avail_qty||', onhand='||l_item_onhand_qty);
2314
2315 update wip_ws_critical_comp_temp
2316 set projected_avail_qty = l_item_project_avail_qty
2317 where organization_id = p_org_id
2318 and inventory_item_id = l_inv_item_id
2319 and nvl(supply_subinventory, 'NULL') = nvl(l_supply_subinv, 'NULL');
2320
2321 previous_jobop_comp_start_time := current_jobop_comp_start_time;
2322 END LOOP;
2323 END IF;
2324 wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_comp_shortage:Point 100, total critical comp to be inserted='||g_wip_job_critical_comp_tbl.COUNT);
2325
2326 END calc_comp_shortage;
2327
2328
2329 /*
2330 * This is the main procedure that contains the concurrent program for component and resource
2331 * shortage. Calculation is done for a particular org.
2332 * calculation type is always 1, which mean both component and resource calculation
2333 */
2334 PROCEDURE calc_shortage (
2335 errbuf OUT NOCOPY VARCHAR2,
2336 retcode OUT NOCOPY NUMBER,
2337 p_org_id IN NUMBER,
2338 p_calc_type IN NUMBER DEFAULT 1) IS
2339
2340 l_return_status VARCHAR2(1);
2341 l_returnStatus VARCHAR2(1);
2342 l_params wip_logger.param_tbl_t;
2343 l_msg_data VARCHAR2(1000);
2344 l_msg_count NUMBER;
2345 l_lock_status NUMBER;
2346 x_return_status NUMBER;
2347 l_pref_exists varchar2(1);
2348
2349 l_concurrent_count NUMBER;
2350 l_conc_status boolean;
2351
2352 BEGIN
2353 retcode := 0;
2354
2355 wip_ws_util.trace_log('WIPWSSHB:calc_shortage: setting up savepoint WIP_SHORT_CALC_START');
2356 SAVEPOINT WIP_SHORT_CALC_START;
2357 wip_ws_util.trace_log('WIPWSSHB:calc_shortage: savepoint WIP_SHORT_CALC_START successful');
2358
2359 if (g_logLevel <= wip_constants.trace_logging) then
2360 l_params(1).paramName := 'p_org_id';
2361 l_params(1).paramValue := p_org_id;
2362 wip_logger.entryPoint(p_procName => 'WIP_WS_SHORTAGE.calc_shortage',
2363 p_params => l_params,
2364 x_returnStatus => l_returnStatus);
2365 if(l_returnStatus <> fnd_api.g_ret_sts_success) then
2366 raise fnd_api.g_exc_unexpected_error;
2367 end if;
2368 end if;
2369
2370 l_concurrent_count := wip_ws_util.get_no_of_running_concurrent(
2371 p_program_application_id => fnd_global.prog_appl_id,
2372 p_concurrent_program_id => fnd_global.conc_program_id,
2373 p_org_id => p_org_id);
2374
2375 if l_concurrent_count > 1 then
2376 wip_ws_util.log_for_duplicate_concurrent (
2377 p_org_id => p_org_id,
2378 p_program_name => 'Component Shortage');
2379 l_conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', 'Errors encountered in calculation program, please check the log file.');
2380 return;
2381 end if;
2382
2383 wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_shortage:calling get_org_comp_calc_param');
2384 get_org_comp_calc_param(p_org_id, l_pref_exists);
2385 wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_shortage: returned from get_org_comp_calc_param with '||l_pref_exists);
2386 if(l_pref_exists = 'N') then
2387 wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_shortage:No Preference exists for this organization');
2388 fnd_message.set_name('WIP','WIP_WS_SHORTAGE_NOPREF');
2389 raise FND_API.G_EXC_ERROR;
2390 end if;
2391
2392 wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:calc_shortage: calling get_period_end_time' );
2393 g_period_end_time := get_period_end_time(p_org_id);
2394 wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:calc_shortage:g_period_end_time='||to_char(g_period_end_time));
2395
2396
2397 wip_ws_util.log_time('WIPWSSHB:calc_shortage:Calling calc_comp_shortage');
2398 wip_ws_util.trace_log('WIPWSSHB:calc_shortage:Calling calc_comp_shortage');
2399 calc_comp_shortage (p_org_id);
2400 wip_ws_util.log_time('WIPWSSHB:calc_shortage:Done with calc_comp_shortage');
2401 wip_ws_util.trace_log('WWIPWSSHB:calc_shortage:Done with calc_comp_shortage');
2402
2403 wip_ws_util.log_time('WIPWSSHB:calc_shortage:Calling calc_res_shortage');
2404 wip_ws_util.trace_log('WIPWSSHB:calc_shortage:Calling calc_res_shortage');
2405 calc_res_shortage (p_org_id);
2406 wip_ws_util.log_time('WIPWSSHB:calc_shortage:Done with calc_res_shortage');
2407 wip_ws_util.trace_log('WIPWSSHB:calc_shortage:Done with calc_res_shortage');
2408
2409 wip_ws_util.log_time('WIPWSSHB:calc_shortage:Calling write_db');
2410 wip_ws_util.trace_log('WIPWSSHB:calc_shortage:Calling write_db');
2411 write_db (p_org_id);
2412 wip_ws_util.log_time('WIPWSSHB:calc_shortage:Done with write_db');
2413 wip_ws_util.trace_log('WIPWSSHB:calc_shortage:Done with write_db');
2414 commit;
2415 wip_ws_util.log_time('WIPWSSHB:calc_shortage:Done with db commit');
2416 wip_ws_util.trace_log('WIPWSSHB:calc_shortage:Done with db commit');
2417
2418 if (g_logLevel <= wip_constants.trace_logging) then
2419 wip_logger.exitPoint(p_procName => 'WIP_WS_SHORTAGE.calc_shortage',
2420 p_procReturnStatus => retcode,
2421 p_msg => 'Request processed successfully!',
2422 x_returnStatus => l_returnStatus);
2423 end if;
2424
2425 EXCEPTION
2426 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2427 wip_ws_util.trace_log('WIPWSSHB:calc_shortage: Exception: Unexpected error');
2428 ROLLBACK TO WIP_SHORT_CALC_START;
2429 retcode := 2; -- End with error
2430 fnd_message.set_name('WIP', 'WIP_UNEXPECTED_ERROR');
2431 fnd_message.set_token('ERROR_TEXT', 'wip_ws_shortage.calc_shortage: ' || SQLERRM);
2432 errbuf := fnd_message.get;
2433 if (g_logLevel <= wip_constants.trace_logging) then
2434 wip_logger.exitPoint(p_procName => 'WIP_WS_SHORTAGE.calc_shortage',
2435 p_procReturnStatus => retcode,
2436 p_msg => 'unexpected error: ' || SQLERRM,
2437 x_returnStatus => l_returnStatus);
2438 end if;
2439
2440 WHEN FND_API.G_EXC_ERROR THEN
2441 retcode := 1;
2442 wip_ws_util.trace_log('WIPWSSHB:calc_shortage: Exception: Expected Error');
2443 ROLLBACK TO WIP_SHORT_CALC_START;
2444 --bug 6756693 Get the message and write it
2445 errbuf := fnd_message.get;
2446 fnd_file.put_line(fnd_file.log, errbuf);
2447 --end bug 6756693
2448 if (g_logLevel <= wip_constants.trace_logging) then
2449 wip_logger.exitPoint(p_procName => 'WIP_WS_SHORTAGE.calc_shortage',
2450 p_procReturnStatus => retcode,
2451 p_msg => 'expected error: ' || errbuf,
2452 x_returnStatus => l_returnStatus);
2453 end if;
2454
2455 WHEN OTHERS THEN
2456 wip_ws_util.trace_log('WIPWSSHB:calc_shortage: Others Exception: '|| SQLERRM);
2457 ROLLBACK TO WIP_SHORT_CALC_START;
2458 retcode := 2; --End with error
2459 if (g_logLevel <= wip_constants.trace_logging) then
2460 wip_logger.exitPoint(p_procName => 'WIP_WS_SHORTAGE.calc_shortage',
2461 p_procReturnStatus => retcode,
2462 p_msg => 'error: ' || SQLERRM,
2463 x_returnStatus => l_returnStatus);
2464 end if;
2465 fnd_message.set_name('WIP', 'WIP_UNEXPECTED_ERROR');
2466 fnd_message.set_token('ERROR_TEXT', 'wip_ws_shortage.calc_shortage: ' || SQLERRM);
2467 errbuf := fnd_message.get;
2468
2469 END calc_shortage;
2470
2471
2472
2473 END WIP_WS_SHORTAGE;