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