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