[Home] [Help]
PACKAGE BODY: APPS.GMP_LEAD_TIME_CALCULATOR_PKG
Source
1 PACKAGE BODY gmp_lead_time_calculator_pkg AS
2 /* $Header: GMPLTCPB.pls 120.6.12010000.1 2008/07/30 06:15:42 appldev ship $ */
3
4 -- Package Global Variables
5 TYPE ref_cursor_typ IS REF CURSOR;
6
7 TYPE item_eff_typ IS RECORD
8 (
9 --item_id NUMBER, --Sowmya - INVCONV
10 inventory_item_id NUMBER,
11 --item_no VARCHAR2(32), --Sowmya - INVCONV
12 item_no VARCHAR2(40),
13 eff_qty NUMBER,
14 --prim_um VARCHAR2(4), --Sowmya - INVCONV
15 prim_um VARCHAR2(3),
16 --org_code VARCHAR2(4), --Sowmya - INVCONV
17 --mtl_org_id NUMBER, --Sowmya - INVCONV
18 org_id NUMBER,
19 daily_work_hours NUMBER,
20 fmeff_id NUMBER,
21 recipe_id NUMBER,
22 formula_id NUMBER,
23 routing_id NUMBER
24 ) ;
25 item_eff item_eff_typ ;
26
27 -- Rtg dtl
28 TYPE routing_details_typ IS RECORD
29 (
30 routing_id NUMBER,
31 routing_no VARCHAR2(32),
32 routing_qty NUMBER,
33 routing_um VARCHAR2(4),
34 routingstep_id NUMBER(16),
35 operation_no VARCHAR2(32),
36 step_qty NUMBER,
37 process_qty_um VARCHAR2(4),
38 activity VARCHAR2(16),
39 activity_factor NUMBER,
40 oprnline_id NUMBER,
41 Resources VARCHAR2(16),
42 process_qty NUMBER ,
43 process_uom VARCHAR2(4),
44 resource_cnt NUMBER,
45 Resource_usage NUMBER,
46 usage_um VARCHAR2(4),
47 scale_type NUMBER,
48 prim_rsrc_ind NUMBER,
49 material_ind NUMBER
50 );
51 TYPE routing_dtl_tbl_typ IS TABLE OF routing_details_typ
52 INDEX by BINARY_INTEGER;
53 routing_dtl_tbl routing_dtl_tbl_typ ;
54
55 TYPE routing_steps_typ IS RECORD
56 (
57 routing_id NUMBER ,
58 routingstep_id NUMBER,
59 f_cum_duration NUMBER,
60 v_cum_duration NUMBER,
61 cum_duration NUMBER,
62 start_offset NUMBER ,
63 end_offset NUMBER
64 );
65 TYPE rtg_steps_tbl_typ IS TABLE OF routing_steps_typ
66 INDEX BY BINARY_INTEGER;
67 rtg_steps_tbl rtg_steps_tbl_typ ;
68
69 UOM_CONVERSION_ERROR EXCEPTION ;
70 g_rtgdtl_sz NUMBER := 0 ;
71 g_stepassn_sz NUMBER := 0 ;
72 g_stepassn_locn NUMBER := 0 ;
73 g_rtgdtl_loc NUMBER := 1 ;
74 g_fmdtl_loc NUMBER := 1 ;
75 g_stepassn_loc NUMBER := 1 ;
76 g_prev_routing_id NUMBER := 0;
77 g_cum_f_duration NUMBER ;
78 g_cum_v_duration NUMBER ;
79 g_cum_duration NUMBER ;
80 g_item_cnt NUMBER := 0 ;
81 g_non_rtg_itm_cnt NUMBER := 0 ;
82 g_err_cnt NUMBER := 0 ;
83 g_user_id NUMBER := to_number(FND_PROFILE.VALUE('USER_ID')) ;
84 g_curr_time DATE := sysdate ;
85 g_uom_hr VARCHAR2(4) := fnd_profile.VALUE('BOM:HOUR_UOM_CODE'); /* B5885931 */
86
87 /*B5146342 - to handle mulitple same activities in an oprn and overrides - starts*/
88 TYPE recipe_orgn_override_typ IS RECORD
89 (
90 routing_id NUMBER,
91 org_id NUMBER,
92 routingstep_id NUMBER,
93 oprn_line_id NUMBER,
94 recipe_id NUMBER,
95 activity_factor NUMBER,
96 resources VARCHAR2(16),
97 resource_usage NUMBER,
98 process_qty NUMBER
99 );
100 TYPE recipe_orgn_override_tbl IS TABLE OF recipe_orgn_override_typ
101 INDEX by BINARY_INTEGER;
102 rcp_orgn_override recipe_orgn_override_tbl;
103
104 TYPE recipe_override_typ IS RECORD
105 (
106 routing_id NUMBER,
107 routingstep_id NUMBER,
108 recipe_id NUMBER,
109 step_qty NUMBER
110 );
111 TYPE recipe_override_tbl IS TABLE OF recipe_override_typ
112 INDEX by BINARY_INTEGER;
113 recipe_override recipe_override_tbl;
114
115 TYPE gmp_routing_step_offsets_typ IS RECORD
116 (
117 plant_code VARCHAR2(4),
118 fmeff_id NUMBER,
119 formula_id NUMBER,
120 routingstep_id NUMBER,
121 start_offset NUMBER,
122 end_offset NUMBER,
123 formulaline_id NUMBER
124 );
125 TYPE rtgstep_offsets_tbl IS TABLE OF gmp_routing_step_offsets_typ
126 INDEX BY BINARY_INTEGER ;
127 rstep_offsets rtgstep_offsets_tbl;
128
129 recipe_orgn_over_size INTEGER; /* No. of rows in recipe orgn override */
130 recipe_override_size INTEGER; /* Number of rows in recipe override */
131 /*B5146342 - to handle mulitple same activities in an oprn and overrides - ends*/
132
133 /*
134 REM+===========================================================================+
135 REM|PROCEDURE NAME |
136 REM| calculate_lead_times |
137 REM|PARAMETERS |
138 REM| |
139 REM|DESCRIPTION |
140 REM| |
141 REM|HISTORY |
142 REM| 06-22-2004 Nisheeth added condition to consider the status. |
143 REM| |
144 REM| B5885931 Rajesh Patangya Resource Usage should be in BOM:HOUR_UOM_CODE |
145 REM| |
146 REM| |
147 REM+===========================================================================+*/
148 PROCEDURE calculate_lead_times(
149 errbuf OUT NOCOPY VARCHAR2,
150 retcode OUT NOCOPY VARCHAR2,
151 p_from_orgn NUMBER,
152 p_to_orgn NUMBER,
153 p_from_item_id NUMBER,
154 p_to_item_id NUMBER)
155 IS
156
157 cur_item_eff ref_cursor_typ ;
158 cur_routing_dtls ref_cursor_typ;
159 c_recipe_override ref_cursor_typ;
160 c_recipe_orgn ref_cursor_typ;
161 cur_formula_dtls ref_cursor_typ;
162 cur_step_assn ref_cursor_typ;
163
164 rtgdtl_sz NUMBER ;
165 i NUMBER ;
166 non_rtg_item NUMBER ;
167 temp_var NUMBER ;
168 recipe_orgn_statement VARCHAR2(32700); /*B5146342 */
169 recipe_statement VARCHAR2(32700);
170 sql_stmt VARCHAR2(32700);
171 fmdtl_sz NUMBER ;
172 stepassn_sz NUMBER ;
173 matl_assn BOOLEAN ;
174
175 BEGIN
176 -- Initialize the global vars
177 g_rtgdtl_sz := 0 ;
178 g_rtgdtl_loc := 1 ;
179 recipe_orgn_over_size := 1; /*B5146342*/
180 recipe_override_size := 1; /*B5146342*/
181 non_rtg_item := 0 ;
182 temp_var := 0 ;
183
184 -- First Turn off the formula security
185 gmd_p_fs_context.set_additional_attr ;
186
187 -- Get the routing details information
188 -- If routing does not exist , just update attributes and offsets
189
190 OPEN cur_routing_dtls FOR
191 SELECT rdtl.routing_id ,
192 rhdr.routing_no ,
193 rhdr.routing_qty,
194 rhdr.routing_uom, /*Sowmya - Inventory convergence*/
195 rdtl.routingstep_id ,
196 opr.oprn_no ,
197 rdtl.step_qty ,
198 opr.process_qty_uom, /*Sowmya - Inventory convergence*/
199 act.activity ,
200 act.activity_factor,
201 act.oprn_line_id ,
202 ores.resources ,
203 ores.process_qty ,
204 ores.resource_process_uom, /*Sowmya - Inventory convergence*/
205 ores.resource_count ,
206 inv_convert.inv_um_convert
207 (-1, -- Item_id
208 38, -- Precision
209 ores.resource_usage, -- Quantity
210 ores.resource_usage_uom , -- from Unit
211 g_uom_hr , -- To Unit
212 NULL , -- From_name
213 NULL -- To_name
214 ) resource_usage, -- B5885931
215 ores.resource_usage_uom, /*Sowmya - Inventory convergence*/
216 ores.scale_type ,
217 ores.prim_rsrc_ind,
218 act.material_ind
219 FROM gmd_operations opr,
220 gmd_operation_activities act,
221 gmd_operation_resources ores,
222 fm_rout_dtl rdtl,
223 gmd_routings_b rhdr,
224 (SELECT distinct gr.routing_id
225 FROM gmd_recipes_b gr,
226 gmd_recipe_validity_rules grv,
227 gmd_status_b gs
228 WHERE gr.recipe_id = grv.recipe_id
229 AND grv.validity_rule_status = gs.status_code
230 AND gs.status_type IN ('700','900')
231 AND gs.delete_mark = 0
232 AND gr.delete_mark = 0
233 AND grv.delete_mark = 0
234 ) eff
235 WHERE eff.routing_id = rhdr.routing_id
236 AND rhdr.routing_id = rdtl.routing_id
237 AND rdtl.oprn_id = opr.oprn_id
238 AND opr.oprn_id = act.oprn_id
239 AND act.oprn_line_id = ores.oprn_line_id
240 AND ores.prim_rsrc_ind in (1,2)
241 AND opr.delete_mark = 0
242 AND ores.delete_mark = 0
243 AND rhdr.delete_mark = 0
244 ORDER BY
245 rdtl.routing_id, rdtl.routingstep_id, act.offset_interval,
246 act.oprn_line_id, ores.prim_rsrc_ind,ores.resources ;
247
248 rtgdtl_sz := 1 ;
249 LOOP
250 FETCH cur_routing_dtls INTO routing_dtl_tbl(rtgdtl_sz) ;
251 EXIT WHEN cur_routing_dtls%NOTFOUND ;
252 -- B5885931 Log message in concurrent log when uom_conversion is not proper.
253 BEGIN
254 IF NVL(routing_dtl_tbl(rtgdtl_sz).resource_usage, 0) < 0 THEN
255 log_message('UOM Conversion setup error for routing_id '|| routing_dtl_tbl(rtgdtl_sz).routing_id );
256 END IF;
257 EXCEPTION
258 WHEN OTHERS THEN
259 Null;
260 END;
261 rtgdtl_sz := rtgdtl_sz + 1;
262 END LOOP;
263 rtgdtl_sz := rtgdtl_sz - 1 ;
264 g_rtgdtl_sz := rtgdtl_sz ;
265
266 CLOSE cur_routing_dtls ;
267 log_message('Routing Details Size is '||rtgdtl_sz);
268
269 /*
270 log_message('routing_id rout_qty rout_um oprn_no
271 step_qty proc_qty_um oprn_lin_id act act_fact res proc_qty UM
272 Cnt Usage UM Scale_tp Prim ');
273 i:= 1 ;
274 FOR i in 1..rtgdtl_sz
275 LOOP
276
277 log_message(routing_dtl_tbl(i).routing_id ||'-'||
278 routing_dtl_tbl(i).routing_qty ||'-'||
279 routing_dtl_tbl(i).routing_um ||'-'||
280 routing_dtl_tbl(i).operation_no ||'-'||
281 routing_dtl_tbl(i).step_qty ||'-'||
282 routing_dtl_tbl(i).process_qty_um ||'-'||
283 routing_dtl_tbl(i).oprnline_id ||'-'||
284 routing_dtl_tbl(i).activity ||'-'||
285 routing_dtl_tbl(i).activity_factor ||'-'||
286 routing_dtl_tbl(i).Resources ||'-'||
287 routing_dtl_tbl(i).process_qty ||'-'||
288 routing_dtl_tbl(i).process_uom ||'-'||
289 routing_dtl_tbl(i).resource_cnt ||'-'||
290 routing_dtl_tbl(i).Resource_usage ||'-'||
291 routing_dtl_tbl(i).usage_um ||'-'||
292 routing_dtl_tbl(i).scale_type ||'-'||
293 routing_dtl_tbl(i).prim_rsrc_ind||'=='||
294 round(temp_var,5) );
295 END LOOP ;
296 */
297
298 /*B5146342 - to handle mulitple same activities in an oprn and overrides - starts*/
299 recipe_orgn_statement := ' SELECT '
300 ||' grb.routing_id, gc.organization_id, '
301 ||' gc.routingstep_id, gc.oprn_line_id, gc.recipe_id, '
302 ||' gc.activity_factor, '
303 ||' gc.resources, gc.resource_usage, gc.process_qty '
304 ||' FROM gmd_recipes grb, '
305 ||' gmd_status_b gs, '
306 ||' ( '
307 ||' SELECT '
308 ||' gor.recipe_id, '
309 ||' gor.organization_id, '
310 ||' gor.oprn_line_id, '
311 ||' gor.routingstep_id, '
312 ||' goa.activity_factor, '
313 ||' gor.resources, '
314 ||' inv_convert.inv_um_convert '
315 ||' (-1, ' -- Item_id
316 ||' 38,' -- Precision
317 ||' gor.resource_usage,' -- Quantity
318 ||' gor.usage_uom , ' -- from Unit
319 ||' :b_uom_hr , ' -- To Unit
320 ||' NULL , ' -- From_name
321 ||' NULL ' -- To_name
322 ||' ) resource_usage, ' -- B5885931
323 ||' gor.process_qty '
324 ||' FROM gmd_recipe_orgn_activities goa, '
325 ||' gmd_recipe_orgn_resources gor '
326 ||' WHERE gor.recipe_id = goa.recipe_id '
327 ||' AND gor.organization_id = goa.organization_id '
328 ||' AND gor.oprn_line_id = goa.oprn_line_id '
329 ||' AND gor.routingstep_id = goa.routingstep_id '
330 ||' UNION ALL '
331 ||' SELECT goa.recipe_id, '
332 ||' goa.organization_id, '
333 ||' goa.oprn_line_id, '
334 ||' goa.routingstep_id, '
335 ||' goa.activity_factor, '
336 ||' NULL resources, '
337 ||' -1 resource_usage, '
338 ||' -1 process_qty '
339 ||' FROM gmd_recipe_orgn_activities goa '
340 ||' WHERE NOT EXISTS( SELECT 1 '
341 ||' FROM gmd_recipe_orgn_resources gor '
342 ||' WHERE gor.recipe_id = goa.recipe_id '
343 ||' AND gor.organization_id = goa.organization_id '
344 ||' AND gor.oprn_line_id = goa.oprn_line_id '
345 ||' AND gor.routingstep_id = goa.routingstep_id ) '
346 ||' UNION ALL '
347 ||' SELECT gor.recipe_id, '
348 ||' gor.organization_id, '
349 ||' gor.oprn_line_id, '
350 ||' gor.routingstep_id, '
351 ||' -1 activity_factor, '
352 ||' gor.resources, '
353 ||' inv_convert.inv_um_convert '
354 ||' (-1, ' -- Item_id
355 ||' 38,' -- Precision
356 ||' gor.resource_usage,' -- Quantity
357 ||' gor.usage_uom , ' -- from Unit
358 ||' :b_uom_hr , ' -- To Unit
359 ||' NULL , ' -- From_name
360 ||' NULL ' -- To_name
361 ||' ) resource_usage, ' -- B5885931
362 ||' gor.process_qty '
363 ||' FROM gmd_recipe_orgn_resources gor '
364 ||' WHERE NOT EXISTS( SELECT 1 '
365 ||' FROM gmd_recipe_orgn_activities goa'
366 ||' WHERE goa.recipe_id = gor.recipe_id '
367 ||' AND goa.organization_id = gor.organization_id '
368 ||' AND goa.oprn_line_id = gor.oprn_line_id '
369 ||' AND goa.routingstep_id = gor.routingstep_id ) '
370 ||' ) gc '
371 ||' WHERE grb.recipe_id = gc.recipe_id '
372 ||' AND grb.delete_mark = 0 '
373 ||' AND grb.recipe_status = gs.status_code '
374 ||' AND gs.status_type IN (' ||'''700'''|| ',' ||'''900'''|| ') '
375 ||' AND gs.delete_mark = 0 '
376 ||' AND gc.organization_id >= NVL(:from_orgn,gc.organization_id) '
377 ||' AND gc.organization_id <= NVL(:to_orgn,gc.organization_id) '
378 ||' ORDER BY 1,2,3,4,5 ' ;
379
380 OPEN c_recipe_orgn FOR recipe_orgn_statement USING
381 g_uom_hr , g_uom_hr , p_from_orgn , p_to_orgn ;
382
383 LOOP
384 FETCH c_recipe_orgn INTO rcp_orgn_override(recipe_orgn_over_size);
385 EXIT WHEN c_recipe_orgn%NOTFOUND;
386 recipe_orgn_over_size := recipe_orgn_over_size + 1;
387 END LOOP;
388 CLOSE c_recipe_orgn;
389 recipe_orgn_over_size := recipe_orgn_over_size -1 ;
390 time_stamp ;
391 log_message('recipe_orgn_over_size is= '|| to_char(recipe_orgn_over_size));
392
393 recipe_statement :=
394 ' SELECT grb.routing_id, grs.routingstep_id, grs.recipe_id, '
395 ||' grs.step_qty '
396 ||' FROM gmd_recipes grb, '
397 ||' gmd_status_b gs, '
398 ||' gmd_recipe_routing_steps grs '
399 ||' WHERE grb.recipe_id = grs.recipe_id '
400 ||' AND grb.delete_mark = 0 '
401 ||' AND grb.recipe_status = gs.status_code '
402 ||' AND gs.status_type IN (' ||'''700'''|| ',' ||'''900'''|| ') '
403 ||' AND gs.delete_mark = 0 '
404 ||' ORDER BY 1,2,3 ' ;
405
406 OPEN c_recipe_override FOR recipe_statement ;
407 LOOP
408 FETCH c_recipe_override INTO recipe_override(recipe_override_size);
409 EXIT WHEN c_recipe_override%NOTFOUND;
410 recipe_override_size := recipe_override_size + 1;
411 END LOOP;
412 CLOSE c_recipe_override;
413 recipe_override_size := recipe_override_size -1 ;
414 time_stamp ;
415 log_message('recipe Override size is = '||to_char(recipe_override_size)) ;
416 /*B5146342 - to handle mulitple same activities in an oprn and overrides - ends*/
417
418 -- Abhay / Teresa 12/16/2003 B3322282
419 -- Rewrote SQL statement for 8i.
420
421 /*Sowmya - Inventory convergence - begin - Changed the cursor for inventory convergence.*/
422
423 -- Abhay / Teresa 12/16/2003 B3322282
424 -- Rewrote SQL statement for 8i.
425 sql_stmt := ' SELECT '
426 ||' msi.inventory_item_id, '
427 ||' msi.segment1, ' --Added as part of inventory convergence
428 ||' inv_convert.inv_um_convert '
429 ||' (eff.inventory_item_id, '
430 ||' NULL, '
431 ||' msi.organization_id, '
432 ||' NULL, '
433 ||' eff.std_qty, '
434 ||' msi.primary_uom_code , ' /* primary */
435 ||' eff.detail_uom , ' /* routing um */
436 ||' NULL , '
437 ||' NULL '
438 ||' ) eff_qty, '
439 ||' msi.primary_uom_code , '
440 ||' mp.organization_id , '
441 ||' gmp_lead_time_calculator_pkg.get_avg_working_hours(bc.calendar_code) , '
442 ||' eff.fmeff_id, '
443 ||' eff.recipe_id, '
444 ||' eff.formula_id, '
445 ||' eff.routing_id '
446 ||' FROM mtl_parameters mp, '
447 ||' mtl_system_items msi, '
448 ||' bom_calendars bc, '
449 ||' fm_form_eff eff, '
450 ||' (SELECT DISTINCT '
451 ||' NVL(eff.organization_id,mp.organization_id) organization_id, '
452 ||' eff.fmeff_id pref_eff, '
453 ||' DENSE_RANK () '
454 ||' OVER (PARTITION BY eff.inventory_item_id,NVL(eff.organization_id,mp.organization_id) '
455 ||' ORDER BY eff.preference,eff.last_update_date DESC) drank, '
456 ||' eff.inventory_item_id '
457 ||' FROM gmd_status_b gs,'
458 ||' mtl_parameters mp, '
459 ||' hr_organization_units hr, '
460 ||' fm_form_eff eff '
461 ||' WHERE NVL(eff.organization_id,mp.organization_id) = mp.organization_id '
462 --B3696730 niyadav 06/22/2004 Conditions added to include status check.
463 ||' AND eff.validity_rule_status = gs.status_code '
464 ||' AND gs.status_type IN (' ||'''700'''|| ',' ||''' 900'''|| ') '
465 --B3696730 niyadav 06/22/2004 code changes end.
466 ||' AND mp.organization_id = hr.organization_id '
467 ||' AND mp.process_enabled_flag = '||''''||'Y'||''''
468 ||' AND nvl(hr.date_to,sysdate) >= sysdate '
469 --Inventory convergence. Resource whse does not exist any longer
470 --'AND sy.resource_whse_code IS NOT NULL '
471 ||' ) prvr '
472 ||' WHERE eff.fmeff_id = prvr.pref_eff '
473 ||' AND prvr.organization_id = mp.organization_id '
474 ||' AND mp.calendar_code = bc.calendar_code '
475 ||' AND eff.inventory_item_id = msi.inventory_item_id '
476 ||' AND msi.organization_id = mp.organization_id '
477 ||' AND msi.inventory_item_id >= NVL(:from_item_id,msi.inventory_item_id ) '
478 ||' AND msi.inventory_item_id <= NVL(:to_item_id,msi.inventory_item_id ) '
479 ||' AND msi.organization_id between NVL(:from_org_id,msi.organization_id) '
480 ||' and NVL(:to_org_id,msi.organization_id) '
481 ||' AND drank = 1 '
482 ||' ORDER BY eff.routing_id ' ;
483
484 OPEN cur_item_eff FOR sql_stmt USING p_from_item_id , p_to_item_id, p_from_orgn , p_to_orgn ;
485 LOOP
486 FETCH cur_item_eff INTO item_eff ;
487 EXIT WHEN cur_item_eff%NOTFOUND ;
488 IF item_eff.routing_id IS NULL OR item_eff.eff_qty < 0 THEN
489 -- Just update the Inventory item Attribute to Zero
490 non_rtg_item := non_rtg_item + 1 ;
491 g_non_rtg_itm_cnt := g_non_rtg_itm_cnt + 1 ;
492 ELSE -- item_eff.routing_id is NOT NULL
493 calc_lead_time(item_eff.routing_id) ;
494 -- We may want to move the insert into routing_offsets and
495 -- updates to the mtl_system_items into this procedure
496 END IF ;
497 END LOOP ;
498 CLOSE cur_item_eff ;
499
500 time_stamp ;
501 retcode := '0' ;
502 log_message('Total number of items updated '||g_item_cnt );
503 log_message('Total number of items without Rtg '||g_non_rtg_itm_cnt );
504 log_message('Total number errors '||g_err_cnt );
505
506 EXCEPTION
507 WHEN OTHERS THEN
508 log_message('Error in procedure calculate_lead_times '||sqlcode||sqlerrm) ;
509 retcode := '-111' ;
510 END calculate_lead_times ;
511
512 /*
513 REM+=========================================================================+
514 REM| PROCEDURE NAME |
515 REM| calc_lead_time |
516 REM| DESCRIPTION |
517 REM| |
518 REM| HISTORY |
519 REM| 06-Oct-2003 Abhay Satpute Created |
520 REM| 23-APR-2004 Abhay B3580639 Changed g_rtgdtl_loc value to i-1 |
521 REM| also added commit and corrected where cond in main SQL|
522 REM| 24-APR-2004 Abhay B3580768 do not divide by l_lead_time_lot_size |
523 REM| for fixed lead time |
524 REM+=========================================================================+
525 */
526 PROCEDURE calc_lead_time (p_routing_id NUMBER)
527 IS
528 i NUMBER ;
529 j NUMBER ;
530 k NUMBER ;
531 z NUMBER ;
532 prev_step_id NUMBER := 0 ;
533 prev_activity VARCHAR2(64) := '__' ;
534 v_resource_usage NUMBER := 0 ;
535 prev_v_resource_usage NUMBER := 0 ;
536 f_resource_usage NUMBER := 0 ;
537 prev_f_resource_usage NUMBER := 0 ;
538 curr_loc NUMBER := 0 ;
539 step_stored NUMBER := 0 ;
540 step_loc NUMBER := 0 ;
541 next_step_loc NUMBER := 0 ;
542 temp_start_offset NUMBER := to_number(NULL);
543 temp_end_offset NUMBER := to_number(NULL);
544
545 -- Item update variables
546 l_item_ret NUMBER ;
547 l_inv_item_id NUMBER := 0 ;
548 l_inv_org_id NUMBER := 0 ;
549 l_lead_time_lot_size NUMBER := 0 ;
550 l_temp_v_lead_time NUMBER := 0 ;
551 l_temp_f_lead_time NUMBER := 0 ;
552
553 temp_ret_stat VARCHAR2(2000) ;
554 l_item_rec inv_item_grp.item_rec_type ;
555 o_item_rec inv_item_grp.item_rec_type ;
556 l_error_tbl inv_item_grp.error_tbl_type;
557 found_Step NUMBER; /*B5146342*/
558 orgn_Step NUMBER; /*B5146342*/
559
560 /*B5251675 - ASQC STEP QTY CALCULATION STARTS */
561 m NUMBER ;
562 curr_cnt NUMBER ;
563 calc_step_qty_flag NUMBER ;
564 l_step_tbl gmd_auto_step_calc.step_rec_tbl;
565 l_msg_count NUMBER ;
566 l_msg_data VARCHAR2(2000) ;
567 l_return_status VARCHAR2(30);
568 cur_calc_step_qty ref_cursor_typ;
569 asqc_found BOOLEAN ;
570
571 /*B5251675 - ASQC STEP QTY CALCULATION ENDS */
572
573 BEGIN
574
575 found_step := 1 ; /*B5146342*/
576 orgn_Step := 1; /*B5146342*/
577
578 -- Do not delete the existing pl/sql table for now as
579 -- the leadtimes etc will not change if routing remains same
580 -- Once we consider the overridesthis will have to be deleted
581
582 -- =============================================
583
584
585 -- Bug: 6441299 Kbanddyo commented the IF condition below and the routing_id assignment
586
587 --IF g_prev_routing_id <> p_routing_id THEN
588 --g_prev_routing_id := p_routing_id ;
589
590 rtg_steps_tbl.DELETE ;
591 g_cum_f_duration := 0 ;
592 g_cum_v_duration := 0 ;
593 g_cum_duration := 0 ;
594 asqc_found := TRUE;
595 j:= 0;
596
597
598 -- Bug: 6441299 Kbanddyo initialization of variables
599 g_rtgdtl_loc := 1;
600 found_step := 1;
601 orgn_step := 1;
602
603 OPEN cur_calc_step_qty FOR
604 SELECT calculate_step_quantity
605 FROM gmd_recipes
606 WHERE recipe_id = item_eff.recipe_id;
607
608 FETCH cur_calc_step_qty INTO calc_step_qty_flag;
609 CLOSE cur_calc_step_qty;
610
611 /*
612 Also notice that when we compute the step times those are for total output
613 and the product item is part of it so the lead time lot size needs to be
614 discounted for that OR increase the lot size by the ratio
615 of total output/prod qty
616 */
617
618 FOR i in g_rtgdtl_loc..g_rtgdtl_sz
619 LOOP
620
621 step_loc := i ;
622 IF i = g_rtgdtl_sz THEN
623 next_step_loc := i - 1 ;
624 ELSE
625 next_step_loc := i + 1;
626 END IF ;
627
628 IF routing_dtl_tbl(i).routing_id = p_routing_id THEN
629
630 --Kbanddyo Added this line as a part of BUG#6441299
631 --The logic used for looping through all the values from g_rtgdtl_loc..g_rtgdtl_sz fails for the last one
632 g_rtgdtl_loc := i ;
633
634 /* ---------- B5251675 ASQC STEP QTY CALCULATION STARTS-------------------*/
635 log_message('calc_step_qty_flag - '|| calc_step_qty_flag);
636
637 IF ( calc_step_qty_flag = 1 AND asqc_found ) THEN
638 gmd_auto_step_calc.calc_step_qty(p_parent_id => item_eff.recipe_id,
639 p_step_tbl => l_step_tbl,
640 p_msg_count => l_msg_count,
641 p_msg_stack => l_msg_data,
642 p_return_status => l_return_status,
643 p_called_from_batch => 0,
644 p_ignore_mass_conv => TRUE,
645 p_ignore_vol_conv => TRUE,
646 p_scale_factor => 1,
647 p_process_loss => 0,
648 p_organization_id => item_eff.org_id);
649
650 m:= 1;
651 LOOP
652 FOR curr_cnt in i..g_rtgdtl_sz
653 LOOP
654
655 IF routing_dtl_tbl(curr_cnt).routingstep_id = l_step_tbl(m).step_id THEN
656 routing_dtl_tbl(curr_cnt).step_qty := l_step_tbl(m).step_qty;
657 log_message('Step quantities updated..');
658 END IF;
659 EXIT WHEN routing_dtl_tbl(curr_cnt).routingstep_id > l_step_tbl(m).step_id OR
660 routing_dtl_tbl(curr_cnt).routing_id > p_routing_id ;
661 END LOOP;
662 m := m + 1;
663 EXIT WHEN m > l_step_tbl.COUNT ;
664 END LOOP;
665
666 asqc_found := FALSE;
667 END IF;
668
669 /* ---------- B5251675 ASQC STEP QTY CALCULATION ENDS-------------------*/
670
671 IF ( calc_step_qty_flag <> 1 ) THEN /*B5251675 - when ASQC is not turned on step qty override
672 to be considered*/
673 /* ---------- B5146342 STEP OVERRIDES STARTS -------------------*/
674 j:= 1 ;
675 FOR j IN found_step..recipe_override_size
676 LOOP
677 IF recipe_override(j).routing_id = routing_dtl_tbl(i).routing_id
678 AND recipe_override(j).routingstep_id = routing_dtl_tbl(i).routingstep_id
679 AND recipe_override(j).recipe_id = item_eff.recipe_id THEN
680
681 routing_dtl_tbl(i).step_qty := recipe_override(j).step_qty ;
682 found_step := j ;
683 EXIT ;
684 ELSIF recipe_override(j).routing_id > routing_dtl_tbl(i).routing_id THEN
685 EXIT ;
686 ELSE
687 /* Keep on looping */
688 NULL ;
689 END IF;
690 END LOOP ;
691 /* ---------- B5146342 STEP OVERRIDES ENDS -------------------*/
692 END IF; /*B5251675*/
693
694 /* ---------- B5146342 ORGN OVERRIDES STARTS -------------------*/
695 k:= 1 ;
696 FOR k IN orgn_step..recipe_orgn_over_size
697 LOOP
698 IF rcp_orgn_override(k).routing_id = routing_dtl_tbl(i).routing_id
699 AND rcp_orgn_override(k).routingstep_id =
700 routing_dtl_tbl(i).routingstep_id
701 AND rcp_orgn_override(k).recipe_id = item_eff.recipe_id
702 AND rcp_orgn_override(k).org_id = item_eff.org_id
703 AND rcp_orgn_override(k).oprn_line_id = routing_dtl_tbl(i).oprnline_id
704 THEN
705 orgn_step := k ;
706
707 -- Activity factor override
708 IF rcp_orgn_override(k).activity_factor >= 0 THEN
709 routing_dtl_tbl(i).activity_factor := rcp_orgn_override(k).activity_factor ;
710 END IF;
711
712 -- Resource Overrides
713 IF rcp_orgn_override(k).resources = routing_dtl_tbl(i).resources THEN
714 IF rcp_orgn_override(k).process_qty > 0 THEN
715 routing_dtl_tbl(i).process_qty := rcp_orgn_override(k).process_qty ;
716 END IF;
717 -- SPECIAL !!! process_qty ZERO than take final step_qty */
718 IF rcp_orgn_override(k).process_qty = 0 THEN
719 routing_dtl_tbl(i).process_qty := routing_dtl_tbl(i).step_qty ;
720 END IF ;
721
722 IF rcp_orgn_override(k).resource_usage >= 0 THEN
723 routing_dtl_tbl(i).resource_usage := rcp_orgn_override(k).resource_usage ;
724 END IF;
725 -- found the resource, now exit
726 orgn_step := k ;
727 EXIT;
728 ELSE
729 NULL ;
730 END IF;
731
732 ELSIF rcp_orgn_override(k).routing_id > routing_dtl_tbl(i).routing_id THEN
733 EXIT ;
734 ELSE
735 /* Keep on looping */
736 NULL ;
737 END IF;
738 END LOOP ;
739 /* ---------- B5146342 ORGN OVERRIDES ENDS -------------------*/
740
741 /* B5146342 - SPECIAL !!! process_qty ZERO than take step_qty */
742 IF routing_dtl_tbl(i).process_qty = 0 THEN
743 routing_dtl_tbl(i).process_qty := routing_dtl_tbl(i).step_qty ;
744 END IF ;
745
746 v_resource_usage := 0 ;
747 f_resource_usage := 0 ;
748
749 -- calculate rsrc_usage
750 IF routing_dtl_tbl(i).scale_type > 0 THEN
751
752 v_resource_usage := ROUND(((routing_dtl_tbl(i).step_qty/
753 routing_dtl_tbl(i).process_qty)*
754 routing_dtl_tbl(i).resource_usage *
755 routing_dtl_tbl(i).activity_factor ),5);
756 IF prev_v_resource_usage = 0 THEN
757 prev_v_resource_usage := v_resource_usage ;
758 END IF ;
759 ELSE
760 f_resource_usage := routing_dtl_tbl(i).resource_usage *
761 routing_dtl_tbl(i).activity_factor ;
762 IF prev_f_resource_usage = 0 THEN
763 prev_f_resource_usage := f_resource_usage ;
764 END IF ;
765 END IF ;
766
767 -- check if step and activity is same
768 IF (routing_dtl_tbl(step_loc).routingstep_id =
769 routing_dtl_tbl(next_step_loc).routingstep_id ) AND
770 (routing_dtl_tbl(step_loc).activity =
771 routing_dtl_tbl(next_step_loc).activity) THEN
772
773 --Find the longest resource in the activity
774 IF v_resource_usage > prev_v_resource_usage THEN
775 prev_v_resource_usage := v_resource_usage ;
776 END IF ;
777 IF f_resource_usage > prev_f_resource_usage THEN
778 prev_f_resource_usage := f_resource_usage ;
779 END IF ;
780
781 -- Last row of the whole program and multiple activities
782 IF (i = g_rtgdtl_sz ) THEN
783
784 IF prev_f_resource_usage <> 0 THEN
785 g_cum_f_duration := g_cum_f_duration + prev_f_resource_usage ;
786 ELSE
787 g_cum_f_duration := g_cum_f_duration + f_resource_usage ;
788 prev_f_resource_usage := f_resource_usage ;
789 END IF;
790
791 IF prev_v_resource_usage <> 0 THEN
792 g_cum_v_duration := g_cum_v_duration + prev_v_resource_usage ;
793 ELSE
794 g_cum_v_duration := g_cum_v_duration + v_resource_usage ;
795 prev_v_resource_usage := v_resource_usage ;
796 END IF;
797
798 IF prev_f_resource_usage > prev_v_resource_usage THEN
799 g_cum_duration := g_cum_duration + prev_f_resource_usage ;
800 ELSE
801 g_cum_duration := g_cum_duration + prev_v_resource_usage ;
802 END IF;
803
804 log_message(' Last Row Route=' || routing_dtl_tbl(step_loc).routing_id
805 || ' Step=' || routing_dtl_tbl(step_loc).routingstep_id || ' oper=' ||
806 routing_dtl_tbl(step_loc).oprnline_id ||' Scaling '||
807 routing_dtl_tbl(i).scale_type || ' fixed Usage=' ||
808 f_resource_usage || ' Prev fixed=' || prev_f_resource_usage
809 || ' Var Usage=' || v_resource_usage || ' Prev Var=' || prev_v_resource_usage );
810
811 prev_f_resource_usage := 0 ;
812 prev_v_resource_usage := 0 ;
813
814 END IF;
815
816 ELSE -- step OR activity changed
817 log_message(' CALCULATE STEP=' || routing_dtl_tbl(step_loc).routingstep_id || '-' ||
818 routing_dtl_tbl(next_step_loc).routingstep_id || ' oper=' ||
819 routing_dtl_tbl(step_loc).oprnline_id || '-' ||
820 routing_dtl_tbl(next_step_loc).oprnline_id ||' Scaling '||
821 routing_dtl_tbl(i).scale_type || ' fixed Usage=' ||
822 f_resource_usage || ' Prev fixed=' || prev_f_resource_usage
823 || ' Var Usage=' || v_resource_usage || ' Prev Var=' || prev_v_resource_usage );
824
825 IF prev_f_resource_usage <> 0 THEN
826 g_cum_f_duration := g_cum_f_duration + prev_f_resource_usage ;
827 ELSE
828 g_cum_f_duration := g_cum_f_duration + f_resource_usage ;
829 prev_f_resource_usage := f_resource_usage ;
830 END IF;
831
832 IF prev_v_resource_usage <> 0 THEN
833 g_cum_v_duration := g_cum_v_duration + prev_v_resource_usage ;
834 ELSE
835 g_cum_v_duration := g_cum_v_duration + v_resource_usage ;
836 prev_v_resource_usage := v_resource_usage ;
837 END IF;
838
839 IF prev_f_resource_usage > prev_v_resource_usage THEN
840 g_cum_duration := g_cum_duration + prev_f_resource_usage ;
841 ELSE
842 g_cum_duration := g_cum_duration + prev_v_resource_usage ;
843 END IF;
844
845 prev_f_resource_usage := 0 ;
846 prev_v_resource_usage := 0 ;
847
848 /* g_cum_f_duration := g_cum_f_duration + prev_f_resource_usage ;
849 g_cum_v_duration := g_cum_v_duration + prev_v_resource_usage ;
850
851 IF prev_f_resource_usage > prev_v_resource_usage THEN
852 g_cum_duration := g_cum_duration + prev_f_resource_usage ;
853 ELSE
854 g_cum_duration := g_cum_duration + prev_v_resource_usage ;
855 END IF;
856 prev_f_resource_usage := 0 ;
857 prev_v_resource_usage := 0 ;*/
858 END IF ; /* Step or activity change */
859
860 IF (routing_dtl_tbl(step_loc).routingstep_id <>
861 routing_dtl_tbl(next_step_loc).routingstep_id ) OR
862 (i = g_rtgdtl_sz )
863 THEN
864 curr_loc := curr_loc + 1 ;
865 rtg_steps_tbl(curr_loc).routing_id := routing_dtl_tbl(i).routing_id ;
866 rtg_steps_tbl(curr_loc).routingstep_id := routing_dtl_tbl(i).routingstep_id ;
867 rtg_steps_tbl(curr_loc).f_cum_duration := g_cum_f_duration ;
868 rtg_steps_tbl(curr_loc).v_cum_duration := g_cum_v_duration ;
869 rtg_steps_tbl(curr_loc).cum_duration := g_cum_duration ;
870 END IF ;
871
872 END IF; -- check for routing_id
873
874 --Commenting the line since the looping logic was not correct ..as a part of BUG#6441299
875 -- g_rtgdtl_loc := i -1 ;
876
877 EXIT WHEN routing_dtl_tbl(i).routing_id > p_routing_id ;
878
879 END LOOP ; -- loop of routing
880
881 -- Now calculate the offsets
882 FOR i in 1..rtg_steps_tbl.COUNT
883 LOOP
884 IF i = 1 THEN
885 rtg_steps_tbl(i).start_offset := 0 ;
886 ELSE
887 -- B5102961 Rajesh Patangya
888 IF g_cum_duration = 0 THEN
889 temp_start_offset := 0 ;
890 ELSE
891 temp_start_offset := 100*(rtg_steps_tbl(i-1).cum_duration/g_cum_duration);
892 END IF;
893 rtg_steps_tbl(i).start_offset := round(temp_start_offset,5);
894 temp_start_offset := to_number(NULL) ;
895 END IF ;
896
897 IF i < rtg_steps_tbl.COUNT THEN
898 IF g_cum_duration = 0 THEN
899 temp_end_offset := 0 ;
900 ELSE
901 temp_end_offset := 100*(rtg_steps_tbl(i).cum_duration/g_cum_duration) ;
902 END IF;
903 rtg_steps_tbl(i).end_offset := round(temp_end_offset,5);
904 temp_end_offset := to_number(NULL );
905 ELSE
906 rtg_steps_tbl(i).end_offset := 100 ;
907 END IF ;
908
909 log_message( 'Route Offset ' || rtg_steps_tbl(i).routing_id
910 ||' Step= ' || rtg_steps_tbl(i).routingstep_id
911 ||' Fixed=' || rtg_steps_tbl(i).f_cum_duration
912 ||' Variable='|| rtg_steps_tbl(i).v_cum_duration
913 ||' cum Duration=' || rtg_steps_tbl(i).cum_duration
914 ||' Start Off=' || rtg_steps_tbl(i).start_offset
915 ||' End Off=' || rtg_steps_tbl(i).end_offset ) ;
916
917 END LOOP;
918
919 --END IF ; /* check for routing id change */
920 -- Bug: 6441299 Kbanddyo commented the above condition
921
922 -- Calculate the lot size always
923 -- Convert the rtg qty to Item Primary so that it can be used as
924 -- lead time lot size.
925
926 /*
927 l_lead_time_lot_size := GMICUOM.uom_conversion(
928 item_eff.item_id,
929 0,
930 routing_dtl_tbl(g_rtgdtl_loc).routing_qty,
931 routing_dtl_tbl(g_rtgdtl_loc).routing_um,
932 item_eff.prim_um,
933 0 );
934 */
935
936 /*Sowmya - Inventory convergence - begin*/
937 l_lead_time_lot_size := inv_convert.inv_um_convert
938 (item_eff.inventory_item_id,
939 NULL,
940 item_eff.org_id,
941 NULL,
942 routing_dtl_tbl(g_rtgdtl_loc).routing_qty,
943 item_eff.prim_um , /* primary */
944 routing_dtl_tbl(g_rtgdtl_loc).routing_um, /* routing um */
945 NULL ,
946 NULL );
947 /*Sowmya - Inventory convergence - end*/
948
949 IF l_lead_time_lot_size <= 0 THEN
950 -- UOM conversion failed, stop here
951 RAISE UOM_CONVERSION_ERROR ;
952 END IF ;
953
954 -- This check is redundant as we stated in the design that Lead Time
955 -- will be calculated for only those organizations that have rsrc whse
956 IF item_eff.inventory_item_id > 0 AND item_eff.org_id IS NOT NULL THEN
957
958 /* Re-instate this code after Oracle Inventory Team
959 takes care of the Update_item API , currently the API
960 returns error for many seemingly correct data conditions
961
962 l_item_rec.organization_id := l_inv_org_id ;
963 l_item_rec.inventory_item_id :=l_inv_item_id ;
964 l_item_rec.fixed_lead_time := g_cum_f_duration ;
965 l_item_rec.variable_lead_time := g_cum_v_duration ;
966 l_item_rec.lead_time_lot_size := 1 ;
967 inv_item_grp.update_item (
968 fnd_api.g_TRUE,
969 fnd_api.g_TRUE,
970 1,
971 l_item_rec ,
972 o_item_rec ,
973 temp_ret_stat,
974 l_error_tbl
975 );
976 log_message('ret stat is :'||temp_ret_stat);
977
978 IF temp_ret_stat = 'E' THEN
979 for z in 1..l_error_tbl.COUNT
980 LOOP
981 log_message(l_error_tbl(z).MESSAGE_TEXT);
982 END LOOP;
983 END IF ;
984 */
985
986 BEGIN
987 l_temp_v_lead_time := (g_cum_v_duration/item_eff.daily_work_hours)
988 / l_lead_time_lot_size ;
989 l_temp_f_lead_time := (g_cum_f_duration/item_eff.daily_work_hours) ;
990
991 log_message( 'FINAL-> ' || routing_dtl_tbl(g_rtgdtl_loc).routing_no
992 || '('|| routing_dtl_tbl(g_rtgdtl_loc).routing_id || ')'
993 || 'Recipe=' || item_eff.recipe_id
994 ||' Item =' || item_eff.inventory_item_id
995 ||' Qty=' || routing_dtl_tbl(g_rtgdtl_loc).routing_qty
996 ||' Fixed=' || l_temp_f_lead_time
997 ||' Cum Fixed=' || g_cum_f_duration
998 ||' Variable='|| l_temp_v_lead_time
999 ||' Cum Variable='|| g_cum_v_duration );
1000
1001 UPDATE mtl_system_items
1002 SET
1003 fixed_lead_time = l_temp_f_lead_time,
1004 variable_lead_time = l_temp_v_lead_time ,
1005 lead_time_lot_size = 1,
1006 last_update_date = g_curr_time,
1007 last_updated_by = g_user_id
1008 --WHERE organization_id = item_eff.mtl_org_id
1009 WHERE organization_id = item_eff.org_id
1010 AND inventory_item_id = item_eff.inventory_item_id;
1011
1012 COMMIT;
1013
1014 g_item_cnt := g_item_cnt + 1 ;
1015 l_lead_time_lot_size := 0 ;
1016
1017 EXCEPTION
1018 WHEN OTHERS THEN
1019 log_message('Error occurred during item attribute update '|| sqlerrm) ;
1020
1021 END ; -- anonymous block for item update
1022
1023 END IF ; -- valid item id and org id
1024
1025 BEGIN
1026
1027 DELETE FROM gmp_routing_offsets
1028 WHERE fmeff_id = item_eff.fmeff_id
1029 AND organization_id = item_eff.org_id;
1030
1031 EXCEPTION
1032 WHEN OTHERS THEN
1033 log_message('Error while deleting !!');
1034 END ; -- anonymous block for rtg off delete
1035
1036 BEGIN
1037
1038 FOR i IN 1..rtg_steps_tbl.COUNT
1039 LOOP
1040
1041 INSERT INTO gmp_routing_offsets(
1042 /*Sowmya - Inventory convergence - commented plant code and included organization id*/
1043 --plant_code, fmeff_id, recipe_id,
1044 organization_id,
1045 fmeff_id,
1046 recipe_id,
1047 formula_id,
1048 routing_id,
1049 routingstep_id,
1050 start_offset,
1051 end_offset,
1052 creation_date,
1053 created_by,
1054 last_update_date,
1055 last_updated_by,
1056 last_update_login )
1057 VALUES (
1058 /*Sowmya - Inventory convergence - commented plant code and included organization id*/
1059 --item_eff.org_code, item_eff.fmeff_id,item_eff.recipe_id,
1060 item_eff.org_id,
1061 item_eff.fmeff_id,
1062 item_eff.recipe_id,
1063 item_eff.formula_id,
1064 rtg_steps_tbl(i).routing_id,
1065 rtg_steps_tbl(i).routingstep_id,
1066 rtg_steps_tbl(i).start_offset,
1067 rtg_steps_tbl(i).end_offset,
1068 g_curr_time,
1069 g_user_id,
1070 g_curr_time,
1071 g_user_id,
1072 g_user_id)
1073 ;
1074
1075 END LOOP;
1076
1077 EXCEPTION
1078 WHEN OTHERS THEN
1079 log_message('Error in insert into gmp_routing_offsets'||sqlerrm) ;
1080
1081 END ; -- end of anonymous block for insert
1082
1083
1084 EXCEPTION
1085 WHEN UOM_CONVERSION_ERROR THEN
1086 g_err_cnt := g_err_cnt + 1 ;
1087 log_message('UOM conversion error for item id '||item_eff.inventory_item_id ) ;
1088 WHEN OTHERS THEN
1089 log_message('Error in procedure calc_lead_time'||sqlcode||sqlerrm) ;
1090
1091 END calc_lead_time ;
1092
1093 /*
1094 REM+=========================================================================+
1095 REM| PROCEDURE NAME |
1096 REM| log_message |
1097 REM| DESCRIPTION |
1098 REM| |
1099 REM| HISTORY |
1100 REM| 06-Oct-2003 Abhay Satpute Created |
1101 REM+=========================================================================+
1102 */
1103 PROCEDURE log_message(
1104 pbuff VARCHAR2)
1105 IS
1106 BEGIN
1107 IF fnd_global.conc_request_id > 0 THEN
1108 FND_FILE.PUT_LINE( FND_FILE.LOG, pBUFF);
1109 ELSE
1110 null;
1111 END IF;
1112
1113 EXCEPTION
1114 WHEN OTHERS THEN
1115 fnd_file.put_line(fnd_file.log, 'Error in log_message '||sqlerrm);
1116 END log_message;
1117
1118 /*
1119 REM+=========================================================================+
1120 REM| PROCEDURE NAME |
1121 REM| time_stamp |
1122 REM| DESCRIPTION |
1123 REM| |
1124 REM| HISTORY |
1125 REM| 06-Oct-2003 Abhay Satpute Created |
1126 REM+=========================================================================+
1127 */
1128 PROCEDURE time_stamp IS
1129
1130 cur_time VARCHAR2(25) := NULL ;
1131
1132 BEGIN
1133 SELECT to_char(sysdate,'DD-MON-RRRR HH24:MI:SS')
1134 INTO cur_time FROM sys.dual ;
1135
1136 log_message(cur_time);
1137 EXCEPTION
1138 WHEN OTHERS THEN
1139 log_message('Failure occured in time_stamp');
1140 log_message(sqlerrm);
1141 RAISE;
1142
1143 END time_stamp ;
1144
1145 /*
1146 REM+=========================================================================+
1147 REM| PROCEDURE NAME |
1148 REM| get_avg_working_hours |
1149 REM| DESCRIPTION |
1150 REM| This procedure gets the average working hours for a calendar. |
1151 REM| This value is used in computation of lead time of the item |
1152 REM| |
1153 REM| HISTORY |
1154 REM| 01-July-2005 Abhay Satpute |
1155 REM+=========================================================================+
1156 */
1157 FUNCTION get_avg_working_hours(p_calendar_code VARCHAR2)
1158 RETURN NUMBER IS
1159
1160 TYPE ref_cursor_typ IS REF CURSOR;
1161 cur_cal_work_ratio ref_cursor_typ ;
1162 cur_avg_work_hours ref_cursor_typ ;
1163 cur_tot_shft_days ref_cursor_typ ;
1164
1165 l_work_hours NUMBER ;
1166 t_work_hours NUMBER ; /*B5146342*/
1167 total_work_hours NUMBER ;
1168 l_tot_days NUMBER ;
1169 total_tot_days NUMBER ;
1170 l_work_ratio NUMBER ;
1171 l_calendar_code VARCHAR2(10);
1172 l_shift_num NUMBER ;
1173 ret_avg_work_hours NUMBER ;
1174 INVALID_CALENDAR EXCEPTION ;
1175
1176 BEGIN
1177 l_work_hours := 0 ;
1178 t_work_hours := 0;
1179 total_work_hours := 0 ;
1180 l_tot_days := 0 ;
1181 total_tot_days := 0 ;
1182 l_work_ratio := 0 ;
1183 l_shift_num := 0 ;
1184 ret_avg_work_hours := 0 ;
1185
1186 /*Computes the work ratio*/
1187 OPEN cur_cal_work_ratio FOR
1188 SELECT (sum(days_on) + sum(days_off) )/ sum(days_on) work_ratio
1189 FROM bom_workday_patterns
1190 WHERE shift_num is NULL
1191 AND calendar_code = p_calendar_code ;
1192
1193 FETCH cur_cal_work_ratio INTO l_work_ratio ;
1194
1195 CLOSE cur_cal_work_ratio ;
1196
1197 /*Computes the total work hours for the working days for all the shifts*/
1198 OPEN cur_avg_work_hours FOR
1199 SELECT
1200 p.calendar_code,
1201 p.shift_num,
1202 sum(p.days_on *( (decode ((sign (st.to_time - st.from_time) ),-1,((86400-st.from_time)+st.to_time),(st.to_time-st.from_time)) ) / 3600 )) work_hrs
1203 FROM bom_workday_patterns p, bom_calendar_shifts s, bom_shift_times st
1204 WHERE p.calendar_code = s.calendar_code
1205 AND p.shift_num = s.shift_num
1206 AND s.calendar_code = st.calendar_code
1207 AND s.shift_num = st.shift_num
1208 AND p.shift_num is NOT NULL
1209 AND p.calendar_code = p_calendar_code
1210 GROUP BY p.calendar_code, p.shift_num;
1211
1212 LOOP
1213 FETCH cur_avg_work_hours INTO l_calendar_code, l_shift_num, l_work_hours;
1214 EXIT WHEN cur_avg_work_hours%NOTFOUND ;
1215
1216 /*Computes the total days on and days off for a specific shift in the calendar */
1217 /*B5146342 - moved this cursor from outside to calculate appropriate work hours*/
1218 OPEN cur_tot_shft_days FOR
1219 SELECT
1220 (sum(p.days_on) + sum(p.days_off)) tot_days
1221 FROM bom_workday_patterns p, bom_calendar_shifts s
1222 WHERE p.calendar_code = s.calendar_code
1223 AND p.shift_num = l_shift_num
1224 AND s.shift_num = p.shift_num
1225 AND p.shift_num is NOT NULL
1226 AND p.calendar_code = l_calendar_code
1227 GROUP BY p.calendar_code, p.shift_num;
1228
1229 FETCH cur_tot_shft_days INTO l_tot_days;
1230 EXIT WHEN cur_tot_shft_days%NOTFOUND ;
1231
1232 CLOSE cur_tot_shft_days;
1233
1234 t_work_hours := l_work_hours/l_tot_days; /*B5146342*/
1235 total_work_hours := total_work_hours + t_work_hours ;
1236
1237 END LOOP ;
1238
1239 CLOSE cur_avg_work_hours ;
1240
1241
1242 IF total_work_hours = 0 THEN
1243 ret_avg_work_hours := 0 ;
1244 RAISE INVALID_CALENDAR ;
1245 ELSE
1246 ret_avg_work_hours := total_work_hours * l_work_ratio ; /*B5146342*/
1247
1248 IF ret_avg_work_hours > 24 THEN
1249 ret_avg_work_hours := 24;
1250 END IF;
1251 END IF ;
1252
1253 RETURN ret_avg_work_hours ;
1254
1255 EXCEPTION
1256 WHEN INVALID_CALENDAR THEN RETURN 0 ;
1257 WHEN OTHERS THEN RETURN 0 ;
1258
1259 END get_avg_working_hours;
1260
1261 END gmp_lead_time_calculator_pkg ;