[Home] [Help]
PACKAGE BODY: APPS.GME_CREATE_STEP_PVT
Source
1 PACKAGE BODY gme_create_step_pvt AS
2 /* $Header: GMEVCRSB.pls 120.25.12020000.3 2012/11/07 14:19:48 gmurator ship $ */
3 g_debug VARCHAR2 (5) := fnd_profile.VALUE ('AFLOG_LEVEL');
4 g_pkg_name CONSTANT VARCHAR2 (30) := 'gme_create_step_pvt';
5
6 PROCEDURE create_batch_steps (
7 p_recipe_rout_step_tbl IN gmd_recipe_fetch_pub.recipe_step_tbl
8 ,p_recipe_rout_act_tbl IN gmd_recipe_fetch_pub.oprn_act_tbl
9 ,p_recipe_rout_resc_tbl IN gmd_recipe_fetch_pub.oprn_resc_tbl
10 ,p_resc_parameters_tbl IN gmd_recipe_fetch_pub.recp_resc_proc_param_tbl
11 ,p_recipe_rout_matl_tbl IN gmd_recipe_fetch_pub.recipe_rout_matl_tbl
12 ,p_routing_depd_tbl IN gmd_recipe_fetch_pub.routing_depd_tbl
13 ,p_gme_batch_header_rec IN gme_batch_header%ROWTYPE
14 ,p_use_workday_cal IN VARCHAR2
15 ,p_contiguity_override IN VARCHAR2
16 ,x_return_status OUT NOCOPY VARCHAR2
17 ,p_ignore_qty_below_cap IN VARCHAR2
18 DEFAULT fnd_api.g_true
19 ,p_step_start_date IN DATE := NULL
20 ,p_step_cmplt_date IN DATE := NULL
21 ,p_step_due_date IN DATE := NULL)
22 IS
23 -- Local variables
24 l_gme_batch_steps gme_create_step_pvt.gme_batch_steps_tab
25 := gme_create_step_pvt.gme_batch_steps_tab
26 ();
27 l_gme_batch_step_activities gme_create_step_pvt.gme_batch_step_activities_tab
28 := gme_create_step_pvt.gme_batch_step_activities_tab
29 ();
30 l_gme_batch_step_resources gme_create_step_pvt.gme_batch_step_resources_tab
31 := gme_create_step_pvt.gme_batch_step_resources_tab
32 ();
33 l_gme_rsrc_parameters gme_create_step_pvt.gme_process_parameters_tab
34 := gme_create_step_pvt.gme_process_parameters_tab
35 ();
36 l_gme_batch_step_items gme_create_step_pvt.gme_batch_step_items_tab
37 := gme_create_step_pvt.gme_batch_step_items_tab
38 ();
39 l_gme_batch_step_dep gme_create_step_pvt.gme_batch_step_dep_tab
40 := gme_create_step_pvt.gme_batch_step_dep_tab
41 ();
42 l_step_charge_rsrc_tab gme_create_step_pvt.step_charge_rsrc_tab;
43
44 TYPE l_batchstep_ids IS TABLE OF NUMBER
45 INDEX BY BINARY_INTEGER;
46
47 i PLS_INTEGER;
48 j PLS_INTEGER;
49 l_batch_id gme_batch_header.batch_id%TYPE;
50 l_step_tbl gmd_auto_step_calc.step_rec_tbl;
51 l_batchstep_id gme_batch_steps.batchstep_id%TYPE;
52 l_last_batchstep_id gme_batch_steps.batchstep_id%TYPE;
53 l_qty gme_batch_steps.plan_step_qty%TYPE;
54 l_mass_qty gme_batch_steps.plan_mass_qty%TYPE;
55 l_volume_qty gme_batch_steps.plan_volume_qty%TYPE;
56 l_plan_charges gme_batch_steps.plan_charges%TYPE;
57 l_charge gme_batch_steps.plan_charges%TYPE;
58 l_uom_class mtl_units_of_measure.uom_class%TYPE;
59 l_batchstep_activity_id gme_batch_step_activities.batchstep_activity_id%TYPE;
60 l_activity_factor gme_batch_step_activities.plan_activity_factor%TYPE;
61 l_resources gme_batch_step_resources.resources%TYPE;
62 l_batchstep_ids_tab l_batchstep_ids;
63 l_total_scale_by_charge PLS_INTEGER;
64 l_count_scale_by_charge PLS_INTEGER;
65 l_return_status VARCHAR2 (1);
66 l_doc_type VARCHAR2 (4);
67 l_qc_status VARCHAR2 (1);
68 l_msg_stack VARCHAR2 (100);
69 l_msg_count NUMBER;
70 l_gmd_text_code NUMBER;
71 l_text_string gme_text_table.text%TYPE;
72 l_api_name CONSTANT VARCHAR2 (30) := 'CREATE_BATCH_STEP';
73 l_gme_text_code NUMBER;
74 l_recipe_id NUMBER;
75 --Bug#5231180
76 l_std_factor NUMBER;
77
78 -- Bug 9694223
79 l_skip NUMBER;
80 l_count NUMBER;
81
82 error_insert_batch_step EXCEPTION;
83 error_insert_b_step_act EXCEPTION;
84 error_insert_b_step_res EXCEPTION;
85 error_insert_b_res_param EXCEPTION;
86 error_insert_res_txns EXCEPTION;
87 error_insert_b_step_items EXCEPTION;
88 error_insert_b_step_depend EXCEPTION;
89 error_calc_step_qty EXCEPTION;
90 error_calc_dates EXCEPTION;
91 error_calc_charges EXCEPTION;
92 error_create_text EXCEPTION;
93 process_qty_below_cap EXCEPTION;
94 invalid_resource_qty_usage EXCEPTION;
95 error_truncate_date EXCEPTION;
96 --FPBug#4395561
97 create_flex_failure EXCEPTION;
98
99 l_step_plan_start_date DATE;
100 l_step_plan_cmplt_date DATE;
101 l_rsrc_start_date DATE;
102 l_rsrc_cmplt_date DATE;
103 l_gme_batch_header gme_batch_header%ROWTYPE;
104
105 CURSOR cur_get_batch_steps (v_batch_id NUMBER, v_count NUMBER DEFAULT 0)
106 IS
107 SELECT batchstep_id, plan_step_qty, plan_charges
108 FROM gme_batch_steps
109 WHERE batch_id = v_batch_id
110 AND ( v_count <> 1
111 OR ( v_count = 1
112 AND batchstep_id = (SELECT MAX (batchstep_id)
113 FROM gme_batch_steps
114 WHERE batch_id = v_batch_id) ) );
115
116 CURSOR cur_get_activities (v_batch_id NUMBER)
117 IS
118 SELECT batchstep_activity_id, plan_activity_factor
119 FROM gme_batch_step_activities
120 WHERE batch_id = v_batch_id;
121
122 CURSOR cur_get_step_activities (
123 x_step_id gme_batch_step_activities.batchstep_id%TYPE
124 ,x_batch_id NUMBER)
125 IS
126 SELECT batchstep_activity_id, plan_activity_factor
127 FROM gme_batch_step_activities
128 WHERE batchstep_id = x_step_id AND batch_id = x_batch_id;
129
130 CURSOR cur_get_resources (v_batchstep_activity_id NUMBER)
131 IS
132 SELECT batchstep_resource_id, plan_rsrc_usage, plan_rsrc_count
133 FROM gme_batch_step_resources
134 WHERE batchstep_activity_id = v_batchstep_activity_id;
135
136 CURSOR cur_recipe_id (v_recipe_validity_rule_id NUMBER)
137 IS
138 SELECT recipe_id
139 FROM gmd_recipe_validity_rules
140 WHERE recipe_validity_rule_id = v_recipe_validity_rule_id;
141
142 CURSOR cur_step_dates (
143 v_batch_id gme_batch_header.batch_id%TYPE
144 ,v_batchstep_id gme_batch_steps.batchstep_id%TYPE)
145 IS
146 SELECT plan_start_date, plan_cmplt_date
147 FROM gme_batch_steps
148 WHERE batch_id = v_batch_id AND batchstep_id = v_batchstep_id;
149
150 CURSOR cur_step_plan_cmplt_date (
151 v_batch_id gme_batch_header.batch_id%TYPE
152 ,v_batchstep_id gme_batch_steps.batchstep_id%TYPE)
153 IS
154 SELECT plan_cmplt_date
155 FROM gme_batch_steps
156 WHERE batch_id = v_batch_id AND batchstep_id = v_batchstep_id;
157
158 CURSOR cur_is_charge_associated (v_batch_id NUMBER, v_batchstep_id NUMBER)
159 IS
160 SELECT resources
161 FROM gme_batch_step_charges
162 WHERE batch_id = v_batch_id
163 AND batchstep_id = v_batchstep_id
164 AND ROWNUM = 1;
165
166 CURSOR cur_get_resource_dates (
167 v_resources gme_batch_step_resources.resources%TYPE
168 ,v_batch_id NUMBER
169 ,v_batchstep_id NUMBER)
170 IS
171 SELECT plan_start_date, plan_cmplt_date
172 FROM gme_batch_step_resources
173 WHERE resources = v_resources
174 AND batch_id = v_batch_id
175 AND batchstep_id = v_batchstep_id;
176
177 CURSOR cur_get_batchstep_ids (v_batch_id NUMBER)
178 IS
179 SELECT batchstep_id
180 FROM gme_batch_steps
181 WHERE batch_id = v_batch_id;
182 BEGIN
183 IF g_debug <= gme_debug.g_log_procedure THEN
184 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
185 || l_api_name);
186 END IF;
187
188 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
189 gme_debug.put_line ('BEGIN Create_step');
190 END IF;
191
192 x_return_status := fnd_api.g_ret_sts_success;
193 l_batch_id := p_gme_batch_header_rec.batch_id;
194
195 IF (p_gme_batch_header_rec.batch_type = gme_common_pvt.g_doc_type_batch) THEN
196 l_doc_type := 'PROD';
197 ELSE
198 l_doc_type := 'FPO';
199 END IF;
200
201 OPEN cur_recipe_id (p_gme_batch_header_rec.recipe_validity_rule_id);
202
203 FETCH cur_recipe_id
204 INTO l_recipe_id;
205
206 CLOSE cur_recipe_id;
207
208 -- Note that by doing an extend to the PL/SQL table, a record is added to the collection
209 -- and all elements are initialized to NULL. Therefore, no need to initialize NULL elements
210 -- explicitly through an assignment statement as that is done in the EXTEND!
211 -- Create data for GME_BATCH_STEPS...
212 i := p_recipe_rout_step_tbl.FIRST;
213 j := 0;
214 l_step_charge_rsrc_tab.DELETE ();
215
216 --Bug#5112133
217 IF (NVL(g_debug, -1) = gme_debug.g_log_statement) THEN
218 gme_debug.put_line ('copy_routing_text_ind = '||gme_common_pvt.g_copy_routing_text_ind);
219 END IF;
220 WHILE i IS NOT NULL LOOP
221 l_gme_batch_steps.EXTEND;
222
223 IF (j = 0) THEN
224 j := l_gme_batch_steps.FIRST;
225 ELSE
226 j := l_gme_batch_steps.NEXT (j);
227 END IF;
228
229 l_gme_batch_steps (j).batch_id := l_batch_id;
230 l_gme_batch_steps (j).routingstep_id :=
231 p_recipe_rout_step_tbl (i).routingstep_id;
232 l_gme_batch_steps (j).batchstep_no :=
233 p_recipe_rout_step_tbl (i).routingstep_no;
234 l_gme_batch_steps (j).oprn_id := p_recipe_rout_step_tbl (i).oprn_id;
235 l_gme_batch_steps (j).step_status := gme_common_pvt.g_step_pending;
236 l_gme_batch_steps (j).quality_status := 1;
237 l_gme_batch_steps (j).delete_mark := 0;
238 l_gme_batch_steps (j).step_qty_um :=
239 p_recipe_rout_step_tbl (i).process_qty_uom;
240 l_gme_batch_steps (j).max_step_capacity :=
241 p_recipe_rout_step_tbl (i).max_capacity;
242 l_gme_batch_steps (j).max_step_capacity_um :=
243 p_recipe_rout_step_tbl (i).capacity_uom;
244 l_gme_batch_steps (j).minimum_transfer_qty :=
245 p_recipe_rout_step_tbl (i).minimum_transfer_qty;
246
247
248 l_gme_batch_steps (j).plan_step_qty :=
249 p_recipe_rout_step_tbl (i).step_qty;
250
251 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
252 gme_debug.put_line ('DURING INITIAL CREATION step number is '|| l_gme_batch_steps (j).batchstep_no);
253 gme_debug.put_line ('DURING INITIAL CREATION plan step qty from gmd is '|| l_gme_batch_steps (j).plan_step_qty);
254 END IF;
255
256 l_gme_batch_steps (j).plan_start_date := gme_common_pvt.g_timestamp;
257 l_gme_batch_steps (j).plan_cmplt_date := gme_common_pvt.g_timestamp;
258 l_gme_batch_steps (j).plan_charges := 0;
259
260 IF p_gme_batch_header_rec.enforce_step_dependency = 1 THEN
261 l_gme_batch_steps (j).steprelease_type := 1;
262 ELSE
263 l_gme_batch_steps (j).steprelease_type :=
264 p_recipe_rout_step_tbl (i).steprelease_type;
265 END IF; /*enforce_step_dependency = 1 */
266
267 IF p_step_due_date IS NULL THEN
268 l_gme_batch_steps (j).due_date :=
269 l_gme_batch_steps (j).plan_cmplt_date;
270 ELSE
271 l_gme_batch_steps (j).due_date := p_step_due_date;
272 END IF;
273
274 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
275 gme_debug.put_line ( ' minimum_transfer_qty '
276 || l_gme_batch_steps (j).minimum_transfer_qty);
277 gme_debug.put_line ( ' max step capacity '
278 || l_gme_batch_steps (j).max_step_capacity);
279 gme_debug.put_line ( ' max step capacity uom '
280 || l_gme_batch_steps (j).max_step_capacity_um);
281 END IF;
282
283 -- Insert text if copy text is on and there is text on the step passed in...
284 -- Note... this should also work for insert step because an operation can have text
285 -- and so as long as gmd passes it back in the fetch... this will work (and copy_routing_text = '1')
286 IF (p_recipe_rout_step_tbl (i).text_code IS NOT NULL)
287 AND (gme_common_pvt.g_copy_routing_text_ind = 1) THEN
288 l_gmd_text_code := p_recipe_rout_step_tbl (i).text_code;
289 l_text_string :=
290 'gme_batch_steps' || '|' || TO_CHAR (l_batch_id)
291 || '|';
292 l_text_string :=
293 l_text_string || l_gme_batch_steps (j).batchstep_no;
294 --Bug#5112133
295 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
296 gme_debug.put_line ('text code for step=' || l_gmd_text_code);
297 gme_debug.put_line ('key_field for step=' || l_text_string);
298 gme_debug.put_line ('calling copy_and_create_text for step line');
299 END IF;
300 copy_and_create_text (l_gmd_text_code
301 ,l_text_string
302 ,l_gme_text_code
303 ,l_return_status);
304
305 IF (l_return_status <> x_return_status) THEN
306 RAISE error_create_text;
307 ELSE
308 l_gme_batch_steps (j).text_code := l_gme_text_code;
309 END IF;
310 END IF;
311
312 gmd_gme_int.check_qc(p_routingid => p_gme_batch_header_rec.routing_id,
313 p_routingstepid => l_gme_batch_steps(j).routingstep_id,
314 p_recipeid => l_recipe_id,
315 p_organization_id => p_gme_batch_header_rec.organization_id,
316 p_resultout => l_qc_status);
317 IF (l_qc_status = 'S') THEN
318 l_gme_batch_steps(j).quality_status := 2;
319 END IF;
320
321
322 --FPBug#4395561 Start
323 /* call create flex procedure to insert the default values of the BATCH_STEPS_DTL_FLEX
324 DFF's segments if they are enabled */
325 l_return_status := NULL;
326 gme_validate_flex_fld_pvt.create_flex_batch_step (
327 l_gme_batch_steps (j),
328 l_gme_batch_steps (j),
329 l_return_status);
330 IF l_return_status <> FND_API.g_ret_sts_success THEN
331 RAISE create_flex_failure;
332 END IF;
333 --FPBug#4395561 End
334
335 IF (gme_batch_steps_dbl.insert_row
336 (p_batch_step => l_gme_batch_steps
337 (j)
338 ,x_batch_step => l_gme_batch_steps
339 (j) ) =
340 TRUE) THEN
341 -- Keep the last batchstep_id produced... this is required for single step insert... since we don't
342 -- have the routingstep_id, we need this when linking the activities and resources back to the step.
343 l_last_batchstep_id := l_gme_batch_steps (j).batchstep_id;
344 l_step_charge_rsrc_tab (l_last_batchstep_id).resources :=
345 p_recipe_rout_step_tbl (j).resources;
346
347 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
348 gme_debug.put_line ( g_pkg_name
349 || '.'
350 || l_api_name
351 || 'resources name obtained from GMD is'
352 || p_recipe_rout_step_tbl (j).resources);
353 END IF;
354 ELSE
355 RAISE error_insert_batch_step;
356 END IF;
357
358 i := p_recipe_rout_step_tbl.NEXT (i);
359 END LOOP; /* WHILE i IS NOT NULL; BATCH STEPS */
360
361 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
362 gme_debug.put_line ('Completed batch steps');
363 gme_debug.put_line ('GME_BATCH_STEP_ACTIVITIES');
364 END IF;
365
366 i := p_recipe_rout_act_tbl.FIRST;
367 j := 0;
368
369 WHILE i IS NOT NULL LOOP
370 l_gme_batch_step_activities.EXTEND;
371
372 IF (j = 0) THEN
373 j := l_gme_batch_step_activities.FIRST;
374 ELSE
375 j := l_gme_batch_step_activities.NEXT (j);
376 END IF;
377
378 l_gme_batch_step_activities (j).batch_id := l_batch_id;
379 l_gme_batch_step_activities (j).activity :=
380 p_recipe_rout_act_tbl (i).activity;
381 l_gme_batch_step_activities (j).oprn_line_id :=
382 p_recipe_rout_act_tbl (i).oprn_line_id;
383 l_gme_batch_step_activities (j).offset_interval :=
384 p_recipe_rout_act_tbl (i).offset_interval;
385 l_gme_batch_step_activities (j).plan_activity_factor :=
386 p_recipe_rout_act_tbl (i).activity_factor;
387 l_gme_batch_step_activities (j).sequence_dependent_ind :=
388 p_recipe_rout_act_tbl (i).sequence_dependent_ind;
389 l_gme_batch_step_activities (j).material_ind :=
390 p_recipe_rout_act_tbl (i).material_ind;
391 l_gme_batch_step_activities (j).delete_mark := 0;
392 l_gme_batch_step_activities (j).break_ind :=
393 p_recipe_rout_act_tbl (i).break_ind;
394 l_gme_batch_step_activities (j).max_break :=
395 p_recipe_rout_act_tbl (i).max_break;
396
397 -- Link this activity to the batch step it belongs to...
398 IF (p_recipe_rout_act_tbl (i).routingstep_id IS NOT NULL) THEN
399 SELECT batchstep_id
400 ,plan_start_date
401 ,plan_cmplt_date
402 INTO l_gme_batch_step_activities (j).batchstep_id
403 ,l_gme_batch_step_activities (j).plan_start_date
404 ,l_gme_batch_step_activities (j).plan_cmplt_date
405 FROM gme_batch_steps
406 WHERE batch_id = l_batch_id
407 AND routingstep_id = p_recipe_rout_act_tbl (i).routingstep_id;
408 ELSE
409 SELECT batchstep_id
410 ,plan_start_date
411 ,plan_cmplt_date
412 INTO l_gme_batch_step_activities (j).batchstep_id
413 ,l_gme_batch_step_activities (j).plan_start_date
414 ,l_gme_batch_step_activities (j).plan_cmplt_date
415 FROM gme_batch_steps
416 WHERE batch_id = l_batch_id
417 AND batchstep_id = l_last_batchstep_id;
418 END IF;
419
420 -- Insert text if copy text is on and there is text on the activity passed in...
421 -- Note... this should also work for insert step because an operation can have text
422 -- and so as long as gmd passes it back in the fetch... this will work (and copy_routing_text = '1')
423 IF (p_recipe_rout_act_tbl (i).text_code IS NOT NULL)
424 AND (gme_common_pvt.g_copy_routing_text_ind = 1) THEN
425 l_gmd_text_code := p_recipe_rout_act_tbl (i).text_code;
426 l_text_string :=
427 'gme_batch_step_activities' || '|' || TO_CHAR (l_batch_id)
428 || '|';
429 l_text_string :=
430 l_text_string
431 || l_gme_batch_step_activities (j).batchstep_id
432 || '|';
433 l_text_string :=
434 l_text_string || p_recipe_rout_act_tbl (i).activity;
435
436 --Bug#5112133
437 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
438 gme_debug.put_line ('text code for activity=' || l_gmd_text_code);
439 gme_debug.put_line ('key_field for activity=' || l_text_string);
440 gme_debug.put_line ('calling copy_and_create_text for activities');
441 END IF;
442
443 copy_and_create_text (l_gmd_text_code
444 ,l_text_string
445 ,l_gme_text_code
446 ,l_return_status);
447
448 IF (l_return_status <> x_return_status) THEN
449 RAISE error_create_text;
450 ELSE
451 l_gme_batch_step_activities (j).text_code := l_gme_text_code;
452 END IF;
453 END IF;
454
455 --FPBug#4395561 Start
456 /* call create flex procedure to insert the default values of the GME_BATCH_STEP_ACTIVITIES_FLEX
457 DFF's segments if they are enabled */
458 l_return_status:=NULL;
459 gme_validate_flex_fld_pvt.create_flex_step_activities (
460 l_gme_batch_step_activities (j),
461 l_gme_batch_step_activities (j),
462 l_return_status);
463 IF l_return_status <> FND_API.g_ret_sts_success THEN
464 RAISE create_flex_failure;
465 END IF;
466 --FPBug#4395561 End
467
468 IF (gme_batch_step_activities_dbl.insert_row
469 (p_batch_step_activities => l_gme_batch_step_activities
470 (j)
471 ,x_batch_step_activities => l_gme_batch_step_activities
472 (j) ) ) THEN
473 NULL;
474 ELSE
475 RAISE error_insert_b_step_act;
476 END IF;
477
478 i := p_recipe_rout_act_tbl.NEXT (i);
479 END LOOP; /* WHILE i IS NOT NULL; STEP ACTIVITIES */
480
481 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
482 gme_debug.put_line ('Completed GME_BATCH_STEP_ACTIVITIES');
483 -- Create data for GME_BATCH_STEP_RESOURCES and GME_RESOURCE_TXNS...
484 gme_debug.put_line ('GME_BATCH_STEP_RESOURCES');
485 END IF;
486
487 i := p_recipe_rout_resc_tbl.FIRST;
488 j := 0;
489
490 WHILE i IS NOT NULL LOOP
491 l_gme_batch_step_resources.EXTEND;
492
493 IF (j = 0) THEN
494 j := l_gme_batch_step_resources.FIRST;
495 ELSE
496 j := l_gme_batch_step_resources.NEXT (j);
497 END IF;
498
499 l_gme_batch_step_resources (j).batch_id := l_batch_id;
500 l_gme_batch_step_resources (j).organization_id :=
501 p_gme_batch_header_rec.organization_id;
502 l_gme_batch_step_resources (j).resources :=
503 p_recipe_rout_resc_tbl (i).resources;
504 l_gme_batch_step_resources (j).cost_analysis_code :=
505 p_recipe_rout_resc_tbl (i).cost_analysis_code;
506 l_gme_batch_step_resources (j).cost_cmpntcls_id :=
507 p_recipe_rout_resc_tbl (i).cost_cmpntcls_id;
508 l_gme_batch_step_resources (j).prim_rsrc_ind :=
509 p_recipe_rout_resc_tbl (i).prim_rsrc_ind;
510 l_gme_batch_step_resources (j).scale_type :=
511 p_recipe_rout_resc_tbl (i).scale_type;
512 l_gme_batch_step_resources (j).plan_rsrc_count :=
513 p_recipe_rout_resc_tbl (i).resource_count;
514 l_gme_batch_step_resources (j).plan_rsrc_qty :=
515 p_recipe_rout_resc_tbl (i).process_qty;
516 l_gme_batch_step_resources (j).original_rsrc_qty :=
517 p_recipe_rout_resc_tbl (i).process_qty;
518 l_gme_batch_step_resources (j).resource_qty_um :=
519 p_recipe_rout_resc_tbl (i).process_uom;
520 l_gme_batch_step_resources (j).plan_rsrc_usage :=
521 p_recipe_rout_resc_tbl (i).resource_usage
522 * p_recipe_rout_resc_tbl (i).resource_count;
523 l_gme_batch_step_resources (j).original_rsrc_usage :=
524 p_recipe_rout_resc_tbl (i).resource_usage
525 * p_recipe_rout_resc_tbl (i).resource_count;
526 l_gme_batch_step_resources (j).usage_um :=
527 p_recipe_rout_resc_tbl (i).usage_um;
528 l_gme_batch_step_resources (j).offset_interval :=
529 p_recipe_rout_resc_tbl (i).offset_interval;
530 l_gme_batch_step_resources (j).capacity_um :=
531 p_recipe_rout_resc_tbl (i).capacity_uom;
532 l_gme_batch_step_resources (j).min_capacity :=
533 p_recipe_rout_resc_tbl (i).min_capacity;
534 l_gme_batch_step_resources (j).max_capacity :=
535 p_recipe_rout_resc_tbl (i).max_capacity;
536 l_gme_batch_step_resources (j).capacity_tolerance :=
537 p_recipe_rout_resc_tbl (i).capacity_tolerance;
538 l_gme_batch_step_resources (j).calculate_charges :=
539 p_recipe_rout_resc_tbl (i).capacity_constraint;
540 l_gme_batch_step_resources (j).process_parameter_1 :=
541 p_recipe_rout_resc_tbl (i).process_parameter_1;
542 l_gme_batch_step_resources (j).process_parameter_2 :=
543 p_recipe_rout_resc_tbl (i).process_parameter_2;
544 l_gme_batch_step_resources (j).process_parameter_3 :=
545 p_recipe_rout_resc_tbl (i).process_parameter_3;
546 l_gme_batch_step_resources (j).process_parameter_4 :=
547 p_recipe_rout_resc_tbl (i).process_parameter_4;
548 l_gme_batch_step_resources (j).process_parameter_5 :=
549 p_recipe_rout_resc_tbl (i).process_parameter_5;
550
551 IF l_gme_batch_step_resources (j).scale_type <> 0 THEN
552 IF l_gme_batch_step_resources (j).plan_rsrc_qty = 0
553 AND l_gme_batch_step_resources (j).plan_rsrc_usage <> 0 THEN
554 gme_common_pvt.log_message
555 ('gme_rsrc_qty_usage_oprn'
556 ,'RESOURCE'
557 ,p_recipe_rout_resc_tbl (i).resources
558 ,'ACTIVITY'
559 ,p_recipe_rout_resc_tbl (i).activity
560 ,'STEP_OPRN_NO'
561 , p_recipe_rout_resc_tbl (i).routingstep_no
562 || ' - '
563 || p_recipe_rout_resc_tbl (i).oprn_no);
564 RAISE invalid_resource_qty_usage;
565 END IF;
566 END IF;
567
568 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
569 gme_debug.put_line ( ' plan_rsrc_qty '
570 || l_gme_batch_step_resources (j).plan_rsrc_qty);
571 END IF;
572
573 -- Link this resource to the batch step it belongs to...
574 IF (p_recipe_rout_resc_tbl (i).routingstep_id IS NOT NULL) THEN
575 SELECT batchstep_id
576 ,plan_start_date
577 ,plan_cmplt_date
578 INTO l_gme_batch_step_resources (j).batchstep_id
579 ,l_gme_batch_step_resources (j).plan_start_date
580 ,l_gme_batch_step_resources (j).plan_cmplt_date
581 FROM gme_batch_steps
582 WHERE batch_id = l_batch_id
583 AND routingstep_id = p_recipe_rout_resc_tbl (i).routingstep_id;
584 ELSE
585 SELECT batchstep_id
586 ,plan_start_date
587 ,plan_cmplt_date
588 INTO l_gme_batch_step_resources (j).batchstep_id
589 ,l_gme_batch_step_resources (j).plan_start_date
590 ,l_gme_batch_step_resources (j).plan_cmplt_date
591 FROM gme_batch_steps
592 WHERE batch_id = l_batch_id
593 AND batchstep_id = l_last_batchstep_id;
594 END IF;
595
596 -- Link this resource to the activity it belongs to...
597 -- Note: To go from resources upto activity, you key on batch_id, batchstep_id and
598 -- oprn_line_id -> because oprn can be repeated many times in different steps so, if you have
599 -- batchstep_id and then oprn_line_id, you will get a unique row from gme_batch_step_activities...
600 SELECT batchstep_activity_id
601 INTO l_gme_batch_step_resources (j).batchstep_activity_id
602 FROM gme_batch_step_activities
603 WHERE batch_id = l_batch_id
604 AND batchstep_id = l_gme_batch_step_resources (j).batchstep_id
605 AND oprn_line_id = p_recipe_rout_resc_tbl (i).oprn_line_id;
606
607 -- Insert text if copy text is on and there is text on the resource passed in...
608 -- Note... this should also work for insert step because an operation can have text
609 -- and so as long as gmd passes it back in the fetch... this will work (and copy_routing_text = '1')
610 IF (p_recipe_rout_resc_tbl (i).text_code IS NOT NULL)
611 AND (gme_common_pvt.g_copy_routing_text_ind = '1') THEN
612 l_gmd_text_code := p_recipe_rout_resc_tbl (i).text_code;
613 l_text_string :=
614 'gme_batch_step_resources' || '|' || TO_CHAR (l_batch_id)
615 || '|';
616 l_text_string :=
617 l_text_string
618 || TO_CHAR (l_gme_batch_step_resources (j).batchstep_activity_id)
619 || '|';
620 l_text_string :=
621 l_text_string || p_recipe_rout_resc_tbl (i).resources;
622
623 --Bug#5112133
624 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
625 gme_debug.put_line ('text code for resource=' || l_gmd_text_code);
626 gme_debug.put_line ('key_field for resource=' || l_text_string);
627 gme_debug.put_line ('calling copy_and_create_text for resources');
628 END IF;
629
630 copy_and_create_text (l_gmd_text_code
631 ,l_text_string
632 ,l_gme_text_code
633 ,l_return_status);
634
635 IF (l_return_status <> x_return_status) THEN
636 RAISE error_create_text;
637 ELSE
638 l_gme_batch_step_resources (j).text_code := l_gme_text_code;
639 END IF;
640 END IF;
641
642 --FPBug#4395561 Start
643 /* call create flex procedure to insert the default values of the GME_BATCH_STEP_RESOURCES_FLEX
644 DFF's segments if they are enabled */
645 l_return_status:=NULL;
646 gme_validate_flex_fld_pvt.create_flex_step_resources (
647 l_gme_batch_step_resources (j),
648 l_gme_batch_step_resources (j),
649 l_return_status);
650 IF l_return_status <> FND_API.g_ret_sts_success THEN
651 RAISE create_flex_failure;
652 END IF;
653 --FPBug#4395561 End
654
655 IF (gme_batch_step_resources_dbl.insert_row
656 (p_batch_step_resources => l_gme_batch_step_resources
657 (j)
658 ,x_batch_step_resources => l_gme_batch_step_resources
659 (j) ) ) THEN
660 NULL;
661 ELSE
662 RAISE error_insert_b_step_res;
663 END IF;
664
665 i := p_recipe_rout_resc_tbl.NEXT (i);
666 END LOOP; /* WHILE i IS NOT NULL; */
667
668 IF l_gme_batch_step_resources.COUNT > 0 THEN
669 gme_batch_step_chg_pvt.set_sequence_dependent_id (l_batch_id);
670 END IF;
671
672 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
673 gme_debug.put_line ('Completed GME_BATCH_STEP_RESOURCES');
674 gme_debug.put_line ('GME_step_process_parameters');
675 END IF;
676
677 FOR m IN 1 .. p_resc_parameters_tbl.COUNT LOOP
678 l_gme_rsrc_parameters.EXTEND;
679 l_gme_rsrc_parameters (m).batch_id := l_batch_id;
680 l_gme_rsrc_parameters (m).resources :=
681 p_resc_parameters_tbl (m).resources;
682 l_gme_rsrc_parameters (m).parameter_id :=
683 p_resc_parameters_tbl (m).parameter_id;
684 l_gme_rsrc_parameters (m).parameter_uom :=
685 p_resc_parameters_tbl (m).units;
686 l_gme_rsrc_parameters (m).target_value :=
687 p_resc_parameters_tbl (m).target_value;
688 l_gme_rsrc_parameters (m).minimum_value :=
689 p_resc_parameters_tbl (m).minimum_value;
690 l_gme_rsrc_parameters (m).maximum_value :=
691 p_resc_parameters_tbl (m).maximum_value;
692
693 -- Link this process parameters to the batch step it belongs to...
694 IF (p_resc_parameters_tbl (m).routingstep_id IS NOT NULL) THEN
695 SELECT batchstep_id
696 INTO l_gme_rsrc_parameters (m).batchstep_id
697 FROM gme_batch_steps
698 WHERE batch_id = l_batch_id
699 AND routingstep_id = p_resc_parameters_tbl (m).routingstep_id;
700 ELSE
701 SELECT batchstep_id
702 INTO l_gme_rsrc_parameters (m).batchstep_id
703 FROM gme_batch_steps
704 WHERE batch_id = l_batch_id
705 AND batchstep_id = l_last_batchstep_id;
706 END IF;
707
708 -- Link this process parameters to the activity it belongs to...
709 SELECT batchstep_activity_id
710 INTO l_gme_rsrc_parameters (m).batchstep_activity_id
711 FROM gme_batch_step_activities
712 WHERE batch_id = l_batch_id
713 AND batchstep_id = l_gme_rsrc_parameters (m).batchstep_id
714 AND oprn_line_id = p_resc_parameters_tbl (m).oprn_line_id;
715
716 -- Link this process parameters to the resources it belongs to..
717 SELECT batchstep_resource_id
718 INTO l_gme_rsrc_parameters (m).batchstep_resource_id
719 FROM gme_batch_step_resources
720 WHERE batch_id = l_batch_id
721 AND batchstep_id = l_gme_rsrc_parameters (m).batchstep_id
722 AND batchstep_activity_id =
723 l_gme_rsrc_parameters (m).batchstep_activity_id
724 AND resources = p_resc_parameters_tbl (m).resources;
725
726 --FPBug#4395561 Start
727 /* call create flex procedure to insert the default values of the GME_BATCH_PROC_PARAM_FLEX
728 DFF's segments if they are enabled */
729 l_return_status:=NULL;
730 gme_validate_flex_fld_pvt.create_flex_process_param (
731 l_gme_rsrc_parameters (m),
732 l_gme_rsrc_parameters (m),
733 l_return_status);
734 IF l_return_status <> FND_API.g_ret_sts_success THEN
735 RAISE create_flex_failure;
736 END IF;
737 --FPBug#4395561 End
738
739
740 IF NOT (gme_process_parameters_dbl.insert_row
741 (p_process_parameters => l_gme_rsrc_parameters
742 (m)
743 ,x_process_parameters => l_gme_rsrc_parameters
744 (m) ) ) THEN
745 RAISE error_insert_b_res_param;
746 END IF;
747 END LOOP;
748
749 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
750 gme_debug.put_line ('GME_BATCH_STEP_ITEMS');
751 END IF;
752
753 -- Create data for GME_BATCH_STEP_ITEMS --> item/step assocations...
754 i := p_recipe_rout_matl_tbl.FIRST;
755 j := 0;
756 l_skip := 0;
757
758 WHILE i IS NOT NULL LOOP
759 -- Bug 9694223 - The l_skip variable tells us if the j index was really used or not for the previous item step assoc.
760 IF l_skip = 0 THEN
761 l_gme_batch_step_items.EXTEND;
762
763 IF (j = 0) THEN
764 j := l_gme_batch_step_items.FIRST;
765 ELSE
766 j := l_gme_batch_step_items.NEXT (j);
767 END IF;
768 ELSE
769 l_skip := 0;
770 END IF;
771
772 l_gme_batch_step_items (j).batch_id := l_batch_id;
773
774 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
775 gme_debug.put_line ('batch_id is '||l_batch_id);
776 gme_debug.put_line ('formulaline_id is '||p_recipe_rout_matl_tbl (i).formulaline_id);
777 gme_debug.put_line ('routingstep_id is '||p_recipe_rout_matl_tbl (i).routingstep_id);
778 END IF;
779
780 SELECT count(*)
781 INTO l_count
782 FROM gme_material_details
783 WHERE batch_id = l_batch_id
784 AND formulaline_id = p_recipe_rout_matl_tbl (i).formulaline_id;
785
786 -- Bug 9694223 - if this record is missing it means that the user is doing a reroute
787 -- to a new routing, which has a step association, for an item which has since been deleted
788 -- from the batch after it was created.
789 IF l_count = 0 THEN
790 l_skip := 1;
791 ELSE
792 SELECT material_detail_id
793 INTO l_gme_batch_step_items (j).material_detail_id
794 FROM gme_material_details
795 WHERE batch_id = l_batch_id
796 AND formulaline_id = p_recipe_rout_matl_tbl (i).formulaline_id;
797
798 IF (p_recipe_rout_matl_tbl (i).routingstep_id IS NOT NULL) THEN
799 SELECT batchstep_id
800 INTO l_gme_batch_step_items (j).batchstep_id
801 FROM gme_batch_steps
802 WHERE batch_id = l_batch_id
803 AND routingstep_id = p_recipe_rout_matl_tbl (i).routingstep_id;
804 ELSE
805 l_gme_batch_step_items (j).batchstep_id := l_last_batchstep_id;
806 END IF;
807
808 l_gme_batch_step_items (j).minimum_transfer_qty :=
809 p_recipe_rout_matl_tbl (i).minimum_transfer_qty;
810 l_gme_batch_step_items (j).minimum_delay :=
811 p_recipe_rout_matl_tbl (i).minimum_delay;
812 l_gme_batch_step_items (j).maximum_delay :=
813 p_recipe_rout_matl_tbl (i).maximum_delay;
814
815 --Rajesh Patangya DFF Enhancement 03Jan2008 Bug# 6195829
816 l_gme_batch_step_items(j).ATTRIBUTE_CATEGORY:= p_recipe_rout_matl_tbl(i).ATTRIBUTE_CATEGORY;
817 l_gme_batch_step_items(j).ATTRIBUTE1 := p_recipe_rout_matl_tbl(i).ATTRIBUTE1 ;
818 l_gme_batch_step_items(j).ATTRIBUTE2 := p_recipe_rout_matl_tbl(i).ATTRIBUTE2 ;
819 l_gme_batch_step_items(j).ATTRIBUTE3 := p_recipe_rout_matl_tbl(i).ATTRIBUTE3 ;
820 l_gme_batch_step_items(j).ATTRIBUTE4 := p_recipe_rout_matl_tbl(i).ATTRIBUTE4 ;
821 l_gme_batch_step_items(j).ATTRIBUTE5 := p_recipe_rout_matl_tbl(i).ATTRIBUTE5 ;
822 l_gme_batch_step_items(j).ATTRIBUTE6 := p_recipe_rout_matl_tbl(i).ATTRIBUTE6 ;
823 l_gme_batch_step_items(j).ATTRIBUTE7 := p_recipe_rout_matl_tbl(i).ATTRIBUTE7 ;
824 l_gme_batch_step_items(j).ATTRIBUTE8 := p_recipe_rout_matl_tbl(i).ATTRIBUTE8 ;
825 l_gme_batch_step_items(j).ATTRIBUTE9 := p_recipe_rout_matl_tbl(i).ATTRIBUTE9 ;
826 l_gme_batch_step_items(j).ATTRIBUTE10 := p_recipe_rout_matl_tbl(i).ATTRIBUTE10 ;
827 l_gme_batch_step_items(j).ATTRIBUTE11 := p_recipe_rout_matl_tbl(i).ATTRIBUTE11 ;
828 l_gme_batch_step_items(j).ATTRIBUTE12 := p_recipe_rout_matl_tbl(i).ATTRIBUTE12 ;
829 l_gme_batch_step_items(j).ATTRIBUTE13 := p_recipe_rout_matl_tbl(i).ATTRIBUTE13 ;
830 l_gme_batch_step_items(j).ATTRIBUTE14 := p_recipe_rout_matl_tbl(i).ATTRIBUTE14 ;
831 l_gme_batch_step_items(j).ATTRIBUTE15 := p_recipe_rout_matl_tbl(i).ATTRIBUTE15 ;
832 l_gme_batch_step_items(j).ATTRIBUTE16 := p_recipe_rout_matl_tbl(i).ATTRIBUTE16 ;
833 l_gme_batch_step_items(j).ATTRIBUTE17 := p_recipe_rout_matl_tbl(i).ATTRIBUTE17 ;
834 l_gme_batch_step_items(j).ATTRIBUTE18 := p_recipe_rout_matl_tbl(i).ATTRIBUTE18 ;
835 l_gme_batch_step_items(j).ATTRIBUTE19 := p_recipe_rout_matl_tbl(i).ATTRIBUTE19 ;
836 l_gme_batch_step_items(j).ATTRIBUTE20 := p_recipe_rout_matl_tbl(i).ATTRIBUTE20 ;
837 l_gme_batch_step_items(j).ATTRIBUTE21 := p_recipe_rout_matl_tbl(i).ATTRIBUTE21 ;
838 l_gme_batch_step_items(j).ATTRIBUTE22 := p_recipe_rout_matl_tbl(i).ATTRIBUTE22 ;
839 l_gme_batch_step_items(j).ATTRIBUTE23 := p_recipe_rout_matl_tbl(i).ATTRIBUTE23 ;
840 l_gme_batch_step_items(j).ATTRIBUTE24 := p_recipe_rout_matl_tbl(i).ATTRIBUTE24 ;
841 l_gme_batch_step_items(j).ATTRIBUTE25 := p_recipe_rout_matl_tbl(i).ATTRIBUTE25 ;
842 l_gme_batch_step_items(j).ATTRIBUTE26 := p_recipe_rout_matl_tbl(i).ATTRIBUTE26 ;
843 l_gme_batch_step_items(j).ATTRIBUTE27 := p_recipe_rout_matl_tbl(i).ATTRIBUTE27 ;
844 l_gme_batch_step_items(j).ATTRIBUTE28 := p_recipe_rout_matl_tbl(i).ATTRIBUTE28 ;
845 l_gme_batch_step_items(j).ATTRIBUTE29 := p_recipe_rout_matl_tbl(i).ATTRIBUTE29 ;
846 l_gme_batch_step_items(j).ATTRIBUTE30 := p_recipe_rout_matl_tbl(i).ATTRIBUTE30 ;
847
848
849 -- Bug: 7715293 vpedarla
850 IF (p_recipe_rout_matl_tbl (i).text_code IS NOT NULL)
851 AND (gme_common_pvt.g_copy_routing_text_ind = '1') THEN
852
853 l_gmd_text_code := p_recipe_rout_matl_tbl (i).text_code;
854 l_text_string :=
855 'gme_batch_step_items' || '|' || TO_CHAR (l_batch_id)
856 || '|';
857 l_text_string :=
858 l_text_string
859 || TO_CHAR (p_recipe_rout_matl_tbl(j).RECIPE_ID)
860 || '|';
861 l_text_string :=
862 l_text_string
863 || TO_CHAR (p_recipe_rout_matl_tbl(j).FORMULALINE_ID)
864 || '|';
865 l_text_string :=
866 l_text_string||TO_CHAR (p_recipe_rout_matl_tbl(j).ROUTINGSTEP_ID);
867
868 -- Bug#5112133
869 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
870 gme_debug.put_line ('text code for step items=' || l_gmd_text_code);
871 gme_debug.put_line ('key_field for step items=' || l_text_string);
872 gme_debug.put_line ('calling copy_and_create_text for step items');
873 END IF;
874
875 copy_and_create_text (l_gmd_text_code
876 ,l_text_string
877 ,l_gme_text_code
878 ,l_return_status);
879
880 IF (l_return_status <> x_return_status) THEN
881 RAISE error_create_text;
882 ELSE
883 l_gme_batch_step_items(j).text_code := l_gme_text_code;
884 END IF;
885 END IF;
886
887 IF (gme_batch_step_items_dbl.insert_row
888 (p_batch_step_items => l_gme_batch_step_items
889 (j)
890 ,x_batch_step_items => l_gme_batch_step_items
891 (j) ) ) THEN
892 NULL;
893 ELSE
894 RAISE error_insert_b_step_items;
895 END IF;
896 END IF; -- l_count = 0
897 i := p_recipe_rout_matl_tbl.NEXT (i);
898 END LOOP; /* WHILE i IS NOT NULL ; STEP ITEM ASSOCIATIONS*/
899
900 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
901 gme_debug.put_line ('Completed GME_BATCH_STEP_ITEMS');
902 gme_debug.put_line ('GME_BATCH_STEP_DEPENDENCIES');
903 END IF;
904
905 -- Create data for GME_BATCH_STEP_DEPENDENCIES
906 i := p_routing_depd_tbl.FIRST;
907 j := 0;
908
909 WHILE i IS NOT NULL LOOP
910 l_gme_batch_step_dep.EXTEND;
911
912 IF (j = 0) THEN
913 j := l_gme_batch_step_dep.FIRST;
914 ELSE
915 j := l_gme_batch_step_dep.NEXT (j);
916 END IF;
917
918 l_gme_batch_step_dep (j).batch_id := l_batch_id;
919
920 SELECT batchstep_id
921 INTO l_gme_batch_step_dep (j).batchstep_id
922 FROM gme_batch_steps
923 WHERE batch_id = l_batch_id
924 AND batchstep_no = p_routing_depd_tbl (i).routingstep_no;
925
926 SELECT batchstep_id
927 INTO l_gme_batch_step_dep (j).dep_step_id
928 FROM gme_batch_steps
929 WHERE batch_id = l_batch_id
930 AND batchstep_no = p_routing_depd_tbl (i).dep_routingstep_no;
931
932 l_gme_batch_step_dep (j).dep_type := p_routing_depd_tbl (i).dep_type;
933 l_gme_batch_step_dep (j).rework_code :=
934 p_routing_depd_tbl (i).rework_code;
935 l_gme_batch_step_dep (j).standard_delay :=
936 p_routing_depd_tbl (i).standard_delay;
937 l_gme_batch_step_dep (j).min_delay :=
938 p_routing_depd_tbl (i).minimum_delay;
939 l_gme_batch_step_dep (j).max_delay :=
940 p_routing_depd_tbl (i).max_delay;
941 l_gme_batch_step_dep (j).transfer_qty :=
942 p_routing_depd_tbl (i).transfer_qty;
943 l_gme_batch_step_dep (j).transfer_um :=
944 p_routing_depd_tbl (i).routingstep_no_uom;
945 l_gme_batch_step_dep (j).transfer_percent :=
946 p_routing_depd_tbl (i).transfer_pct;
947 l_gme_batch_step_dep (j).chargeable_ind :=
948 p_routing_depd_tbl (i).chargeable_ind;
949
950 IF (gme_batch_step_depend_dbl.insert_row (l_gme_batch_step_dep (j)
951 ,l_gme_batch_step_dep (j) ) ) THEN
952 NULL;
953 ELSE
954 RAISE error_insert_b_step_depend;
955 END IF;
956
957 i := p_routing_depd_tbl.NEXT (i);
958 END LOOP; /* WHILE i IS NOT NULL ; STEP DEPENDENCIES*/
959
960 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
961 gme_debug.put_line ('Completed GME_BATCH_STEP_DEPENDENCIES');
962 END IF;
963
964 -- Now calculate the step quantities...
965 IF ( p_recipe_rout_step_tbl.COUNT = 1
966 AND p_recipe_rout_step_tbl (1).routingstep_id IS NULL) THEN
967 -- This is an insert... the plan_step_qty is filled in above... does actual_step_qty get filled in if this is WIP?
968 -- ALSO, don't need to call auto_step_calc, because there are no dependencies tied in with this step... and there
969 -- are no item_step_associations either... so do nothing...
970 NULL;
971 ELSE
972 IF (p_gme_batch_header_rec.automatic_step_calculation = 1) THEN
973 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
974 gme_debug.put_line ('auto step calc for batch id '||l_batch_id);
975 gme_debug.put_line ('CALLING gmd_auto_step_calc.calc_step_qty. Org is '||p_gme_batch_header_rec.organization_id);
976 END IF;
977
978 gmd_auto_step_calc.calc_step_qty
979 (p_parent_id => l_batch_id
980 ,p_step_tbl => l_step_tbl
981 ,p_msg_count => l_msg_count
982 ,p_msg_stack => l_msg_stack
983 ,p_return_status => l_return_status
984 ,p_called_from_batch => 1
985 ,p_organization_id => p_gme_batch_header_rec.organization_id);
986 ELSE
987 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
988 gme_debug.put_line ('NO auto step calc...');
989 gme_debug.put_line ('CALLING calc_step_qty ...');
990 END IF;
991
992 calc_step_qty (l_batch_id, l_step_tbl, l_return_status, 1);
993 END IF;
994
995 IF l_return_status <> x_return_status THEN
996 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
997 gme_debug.put_line
998 (fnd_msg_pub.get (p_encoded => fnd_api.g_false) );
999 END IF;
1000
1001 RAISE error_calc_step_qty;
1002 END IF;
1003
1004 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1005 gme_debug.put_line ('done step calc...');
1006 gme_debug.put_line ('GO INTO LOOP TO RESET PLAN STEP QTY... ');
1007 END IF;
1008
1009 -- Update the plan_step_qty in GME_BATCH_STEPS
1010 FOR i IN l_step_tbl.FIRST .. l_step_tbl.LAST LOOP
1011
1012 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1013 gme_debug.put_line ('step number is '|| l_step_tbl (i).step_no);
1014 gme_debug.put_line ('PLAN STEP QTY FROM GMD IS '|| l_step_tbl (i).step_qty);
1015 gme_debug.put_line ('PLAN STEP QTY RNDED FROM GMD IS '|| ROUND (l_step_tbl (i).step_qty, 32));
1016 END IF;
1017
1018 -- Round the step_qty, step_mass_qty, step_vol_qty to 32 decimal places.
1019 UPDATE gme_batch_steps
1020 SET plan_step_qty = ROUND (l_step_tbl (i).step_qty, 32)
1021 ,plan_mass_qty = ROUND (l_step_tbl (i).step_mass_qty, 32)
1022 ,mass_ref_um = l_step_tbl (i).step_mass_uom
1023 ,plan_volume_qty = ROUND (l_step_tbl (i).step_vol_qty, 32)
1024 ,volume_ref_um = l_step_tbl (i).step_vol_uom
1025 WHERE batch_id = l_batch_id
1026 AND batchstep_no = l_step_tbl (i).step_no;
1027 END LOOP; /* FOR i IN l_step_tbl.FIRST .. l_step_tbl.LAST */
1028 END IF; /* IF p_recipe_rout_step_tbl.COUNT = 1 */
1029
1030 IF ( p_recipe_rout_step_tbl.COUNT = 1
1031 AND p_recipe_rout_step_tbl (1).routingstep_id IS NULL) THEN
1032 -- This is an insert... calc charges for the newly inserted step
1033 /* Bug#5231180 Begin modified the following code to calculate mass qty and volume qty depends on the
1034 step qty uom used */
1035 --get the uom class and conversion rate in step qty uom
1036 SELECT uom_class, conversion_rate
1037 INTO l_uom_class, l_std_factor
1038 FROM mtl_uom_conversions
1039 WHERE uom_code = p_recipe_rout_step_tbl (1).process_qty_uom
1040 AND inventory_item_id = 0;
1041
1042 l_mass_qty := NULL;
1043 l_volume_qty := NULL;
1044
1045 --Bug#5231180 used gme_common_pvt variables rather GMD spec variables
1046 --IF l_uom_class = NVL(gmd_auto_step_calc.g_profile_mass_um_type,gme_common_pvt.g_mass_um_type) THEN
1047 IF l_uom_class = gme_common_pvt.g_mass_um_type THEN
1048 --multiply the step qty with standard factor to get mass qty
1049 l_mass_qty := p_recipe_rout_step_tbl (1).step_qty * l_std_factor;
1050 --ELSIF l_uom_class = NVL(gmd_auto_step_calc.g_profile_volume_um_type,gme_common_pvt.g_volume_um_type) THEN
1051 ELSIF l_uom_class = gme_common_pvt.g_volume_um_type THEN
1052 --multiply the step qty with standard factor to get vol qty
1053 l_volume_qty := p_recipe_rout_step_tbl (1).step_qty * l_std_factor;
1054 END IF;
1055
1056 IF NVL (g_debug, -1) = gme_debug.g_log_statement THEN
1057 gme_debug.put_line
1058 ( g_pkg_name
1059 || '.'
1060 || l_api_name
1061 || 'resources name passed in case of single step insertion is'
1062 || p_recipe_rout_step_tbl (1).resources);
1063 gme_debug.put_line
1064 ( g_pkg_name
1065 || '.'
1066 || l_api_name
1067 || ' Mass Qty, Volume Qty: '
1068 || l_mass_qty ||' '||l_volume_qty );
1069 END IF;
1070 --Bug#5231180 End
1071 gme_update_step_qty_pvt.calc_charge
1072 (p_step_id => l_last_batchstep_id
1073 ,p_resources => p_recipe_rout_step_tbl (1).resources
1074 ,p_mass_qty => l_mass_qty
1075 ,p_vol_qty => l_volume_qty
1076 ,x_charge => l_charge
1077 ,x_return_status => l_return_status);
1078
1079 IF l_return_status <> x_return_status THEN
1080 RAISE error_calc_charges;
1081 END IF;
1082
1083 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1084 gme_debug.put_line ( ' return status from calc charge '
1085 || l_return_status);
1086 gme_debug.put_line (' charges ' || TO_CHAR (l_charge) );
1087 END IF;
1088
1089 UPDATE gme_batch_steps
1090 SET plan_charges = l_charge
1091 WHERE batchstep_id = l_last_batchstep_id;
1092 ELSE
1093 update_charges (p_batch_id => l_batch_id
1094 ,p_step_charge_rsrc_tab => l_step_charge_rsrc_tab
1095 ,x_return_status => l_return_status);
1096
1097 IF l_return_status <> x_return_status THEN
1098 RAISE error_calc_charges;
1099 END IF;
1100 END IF;
1101
1102 OPEN cur_get_batch_steps (l_batch_id, p_recipe_rout_step_tbl.COUNT);
1103
1104 FETCH cur_get_batch_steps
1105 INTO l_batchstep_id, l_qty, l_plan_charges;
1106
1107 WHILE cur_get_batch_steps%FOUND LOOP
1108 UPDATE gme_batch_step_resources
1109 SET plan_rsrc_usage =
1110 ROUND ( (l_qty / plan_rsrc_qty * plan_rsrc_usage), 32)
1111 WHERE batchstep_id = l_batchstep_id
1112 AND scale_type = 1
1113 AND -- scale_type = 1 denotes linear scale...
1114 plan_rsrc_qty <> 0;
1115
1116 FETCH cur_get_batch_steps
1117 INTO l_batchstep_id, l_qty, l_plan_charges;
1118 END LOOP; /* WHILE cur_get_batch_steps%FOUND */
1119
1120 CLOSE cur_get_batch_steps;
1121
1122 -- The following variable will accumulate the # of resources that are scale by charge and associated to a step with
1123 -- a NULL charge. as long as this variable is > 0, a message will be put to the API stack saying that
1124 -- at least 1 resource was not scaled by charge because the charge is undefined.
1125 -- Look at calc_charge for ways of getting a NULL charge...
1126 l_total_scale_by_charge := 0;
1127
1128 IF ( p_recipe_rout_step_tbl.COUNT = 1
1129 AND p_recipe_rout_step_tbl (1).routingstep_id IS NULL) THEN
1130 -- This is an insert step... deal with only that new step...
1131 SELECT plan_step_qty, plan_charges
1132 INTO l_qty, l_plan_charges
1133 FROM gme_batch_steps
1134 WHERE batchstep_id = l_last_batchstep_id;
1135
1136 -- Round the plan_rsrc_qty to 32 decimal places.
1137 UPDATE gme_batch_step_resources
1138 SET plan_rsrc_qty = ROUND (l_qty, 32)
1139 WHERE batchstep_id = l_last_batchstep_id
1140 AND scale_type <>
1141 0
1142 -- if scale_type = 0, then let's leave the plan_rsrc_qty to that passed in by GMD... scale_type = 0 is fixed
1143 AND plan_rsrc_qty <> 0;
1144
1145 -- Update the resource quantities for all resources and usage if scale by charge...
1146 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1147 gme_debug.put_line (' plan charges ' || TO_CHAR (l_plan_charges) );
1148 END IF;
1149
1150 IF l_plan_charges IS NOT NULL THEN
1151 UPDATE gme_batch_step_resources
1152 SET plan_rsrc_usage =
1153 ROUND (plan_rsrc_usage * l_plan_charges, 32)
1154 WHERE batchstep_id = l_last_batchstep_id AND scale_type = 2;
1155 -- scale_type = 2 denotes scale by charge...
1156 ELSE
1157 -- if charges is NULL and there exists resources with are scale by charge, these resources are not touched, i.e.
1158 -- in effect, charge is defaulted to 1... however, let's give user a warning that this has occurred.
1159 -- Because of the limitation in forms taking only 1 message at a time (?), we will put one message if this occurs,
1160 -- NOT one message per occurrence.
1161 SELECT COUNT (1)
1162 INTO l_count_scale_by_charge
1163 FROM gme_batch_step_resources
1164 WHERE batchstep_id = l_last_batchstep_id AND scale_type = 2;
1165 -- scale_type = 2 denotes scale by charge...
1166 END IF;
1167
1168 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1169 gme_debug.put_line ( ' total scale by charge '
1170 || TO_CHAR (l_total_scale_by_charge) );
1171 END IF;
1172
1173 l_total_scale_by_charge := l_count_scale_by_charge;
1174
1175 -- Update the Usage on resources based on the activity factor for only that step...
1176 OPEN cur_get_step_activities (l_last_batchstep_id, l_batch_id);
1177
1178 FETCH cur_get_step_activities
1179 INTO l_batchstep_activity_id, l_activity_factor;
1180
1181 WHILE cur_get_step_activities%FOUND LOOP
1182 UPDATE gme_batch_step_resources
1183 SET plan_rsrc_usage =
1184 ROUND (plan_rsrc_usage * l_activity_factor, 32)
1185 ,plan_rsrc_qty =
1186 ROUND (plan_rsrc_qty * l_activity_factor, 32)
1187 WHERE batchstep_activity_id = l_batchstep_activity_id;
1188
1189 IF p_gme_batch_header_rec.update_inventory_ind = 'Y' THEN
1190 FOR l_rec IN cur_get_resources (l_batchstep_activity_id) LOOP
1191 UPDATE gme_resource_txns
1192 SET resource_usage =
1193 ROUND ( l_rec.plan_rsrc_usage
1194 / l_rec.plan_rsrc_count
1195 ,32)
1196 WHERE doc_id = l_batch_id
1197 AND doc_type = l_doc_type
1198 AND line_id = l_rec.batchstep_resource_id;
1199 END LOOP; /* FOR resources */
1200 END IF;
1201 /* IF p_gme_batch_header_rec.update_inventory_ind = 'Y' THEN */
1202
1203 FETCH cur_get_step_activities
1204 INTO l_batchstep_activity_id, l_activity_factor;
1205 END LOOP; /* WHILE cur_get_step_activities%FOUND */
1206
1207 CLOSE cur_get_step_activities;
1208 ELSE
1209 -- Update the resource quantities for all resources and usage if scale by charge...
1210 OPEN cur_get_batch_steps (l_batch_id);
1211
1212 FETCH cur_get_batch_steps
1213 INTO l_batchstep_id, l_qty, l_plan_charges;
1214
1215 WHILE cur_get_batch_steps%FOUND LOOP
1216 UPDATE gme_batch_step_resources
1217 SET plan_rsrc_qty = ROUND (l_qty, 32)
1218 WHERE batchstep_id = l_batchstep_id
1219 AND scale_type <> 0
1220 AND plan_rsrc_qty <> 0;
1221
1222 -- if scale_type = 0, then let's leave the plan_rsrc_qty to that passed in by GMD... scale_type = 0 is fixed
1223 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1224 gme_debug.put_line ( ' plan charges batch steps '
1225 || TO_CHAR (l_plan_charges) );
1226 END IF;
1227
1228 IF l_plan_charges IS NOT NULL THEN
1229 UPDATE gme_batch_step_resources
1230 SET plan_rsrc_usage =
1231 ROUND (plan_rsrc_usage * l_plan_charges, 32)
1232 WHERE batchstep_id = l_batchstep_id AND scale_type = 2;
1233 -- scale_type = 2 denotes scale by charge...
1234 ELSE
1235 -- if charges is NULL and there exists resources with are scale by charge, these resources are not touched, i.e.
1236 -- in effect, charge is defaulted to 1... however, let's give user a warning that this has occurred.
1237 -- Because of the limitation in forms taking only 1 message at a time (?), we will put one message if this occurs,
1238 -- NOT one message per occurrence.
1239 SELECT COUNT (1)
1240 INTO l_count_scale_by_charge
1241 FROM gme_batch_step_resources
1242 WHERE batchstep_id = l_batchstep_id AND scale_type = 2;
1243 -- scale_type = 2 denotes scale by charge...
1244 END IF;
1245
1246 l_total_scale_by_charge :=
1247 l_total_scale_by_charge + l_count_scale_by_charge;
1248
1249 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1250 gme_debug.put_line ( ' l_total scale by charge loop '
1251 || TO_CHAR (l_total_scale_by_charge) );
1252 END IF;
1253
1254 FETCH cur_get_batch_steps
1255 INTO l_batchstep_id, l_qty, l_plan_charges;
1256 END LOOP; /* WHILE cur_get_batch_steps%FOUND */
1257
1258 CLOSE cur_get_batch_steps;
1259
1260 -- Update the Usage on resources based on the activity factor...
1261 OPEN cur_get_activities (l_batch_id);
1262
1263 FETCH cur_get_activities
1264 INTO l_batchstep_activity_id, l_activity_factor;
1265
1266 WHILE cur_get_activities%FOUND LOOP
1267 UPDATE gme_batch_step_resources
1268 SET plan_rsrc_usage =
1269 ROUND (plan_rsrc_usage * l_activity_factor, 32)
1270 ,plan_rsrc_qty =
1271 ROUND (plan_rsrc_qty * l_activity_factor, 32)
1272 WHERE batchstep_activity_id = l_batchstep_activity_id;
1273
1274 IF p_gme_batch_header_rec.update_inventory_ind = 'Y' THEN
1275 FOR l_rec IN cur_get_resources (l_batchstep_activity_id) LOOP
1276 UPDATE gme_resource_txns
1277 SET resource_usage =
1278 ROUND ( l_rec.plan_rsrc_usage
1279 / l_rec.plan_rsrc_count
1280 ,32)
1281 WHERE doc_id = l_batch_id
1282 AND doc_type = l_doc_type
1283 AND line_id = l_rec.batchstep_resource_id;
1284 END LOOP; /* FOR resources */
1285 END IF;
1286 /* IF p_gme_batch_header_rec.update_inventory_ind = 'Y' THEN */
1287
1288 FETCH cur_get_activities
1289 INTO l_batchstep_activity_id, l_activity_factor;
1290 END LOOP; /* WHILE cur_get_activities%FOUND */
1291
1292 CLOSE cur_get_activities;
1293 END IF;
1294
1295 /* Lets check if any of the resource quantities are falling below the min capacity */
1296 IF (p_ignore_qty_below_cap = fnd_api.g_false) THEN
1297 IF gme_common_pvt.resource_qty_below_capacity
1298 (p_batch_id => l_batch_id) THEN
1299 RAISE process_qty_below_cap;
1300 END IF;
1301 END IF; /* IF NOT (p_ignore_qty_below_cap) */
1302
1303 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1304 gme_debug.put_line ( 'l_total_scale_by_charge='
1305 || l_total_scale_by_charge);
1306 END IF;
1307
1308 IF l_total_scale_by_charge > 0 THEN
1309 -- we are not raising an error, just warn the user.
1310 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1311 gme_debug.put_line ('undefined charge warning');
1312 END IF;
1313
1314 fnd_message.set_name ('GME', 'GME_UNDEF_CHRG_RSRC_SCALE');
1315 fnd_msg_pub.ADD;
1316 END IF;
1317
1318 IF ( p_recipe_rout_step_tbl.COUNT = 1
1319 AND p_recipe_rout_step_tbl (1).routingstep_id IS NULL) THEN
1320 gme_create_step_pvt.calc_dates
1321 (p_gme_batch_header_rec => p_gme_batch_header_rec
1322 ,p_use_workday_cal => p_use_workday_cal
1323 ,p_contiguity_override => p_contiguity_override
1324 ,p_return_status => l_return_status
1325 ,p_step_id => l_last_batchstep_id
1326 ,p_plan_start_date => p_step_start_date
1327 ,p_plan_cmplt_date => p_step_cmplt_date);
1328
1329 IF l_return_status <> x_return_status THEN
1330 RAISE error_calc_dates;
1331 END IF;
1332 ELSE
1333 gme_create_step_pvt.calc_dates
1334 (p_gme_batch_header_rec => p_gme_batch_header_rec
1335 ,p_use_workday_cal => p_use_workday_cal
1336 ,p_contiguity_override => p_contiguity_override
1337 ,p_return_status => l_return_status);
1338 END IF;
1339
1340 IF l_return_status <> x_return_status THEN
1341 RAISE error_calc_dates;
1342 END IF;
1343
1344 IF ( p_recipe_rout_step_tbl.COUNT = 1
1345 AND p_recipe_rout_step_tbl (1).routingstep_id IS NULL) THEN
1346 OPEN cur_step_dates (p_gme_batch_header_rec.batch_id
1347 ,l_last_batchstep_id);
1348
1349 FETCH cur_step_dates
1350 INTO l_step_plan_start_date, l_step_plan_cmplt_date;
1351
1352 CLOSE cur_step_dates;
1353
1354 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1355 gme_debug.put_line ( l_api_name
1356 || 'start,cmplt dates for step are '
1357 || TO_CHAR (l_step_plan_start_date
1358 ,'DD-MON-YYYY HH24:MI:SS')
1359 || ' '
1360 || TO_CHAR (l_step_plan_cmplt_date
1361 ,'DD-MON-YYYY HH24:MI:SS') );
1362 gme_debug.put_line
1363 ( 'user supplied start,cmplt dates for step are'
1364 || TO_CHAR (p_step_start_date
1365 ,'DD-MON-YYYY HH24:MI:SS')
1366 || ' '
1367 || TO_CHAR (p_step_cmplt_date
1368 ,'DD-MON-YYYY HH24:MI:SS') );
1369 gme_debug.put_line
1370 ( 'start,cmplt dates for batch are'
1371 || TO_CHAR (p_gme_batch_header_rec.plan_start_date
1372 ,'DD-MON-YYYY HH24:MI:SS')
1373 || TO_CHAR (p_gme_batch_header_rec.plan_cmplt_date
1374 ,'DD-MON-YYYY HH24:MI:SS') );
1375 END IF;
1376
1377 IF (p_step_cmplt_date IS NOT NULL)
1378 AND (p_step_cmplt_date <> l_step_plan_cmplt_date) THEN
1379 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1380 gme_debug.put_line
1381 ('p_step_cmplt_date diff from l_step_plan_cmplt_date');
1382 END IF;
1383
1384 IF (p_step_cmplt_date > l_step_plan_cmplt_date) THEN
1385 UPDATE gme_batch_steps
1386 SET plan_cmplt_date = p_step_cmplt_date
1387 ,last_updated_by = gme_common_pvt.g_user_ident
1388 ,last_update_date = gme_common_pvt.g_timestamp
1389 ,last_update_login = gme_common_pvt.g_login_id
1390 WHERE batch_id = p_gme_batch_header_rec.batch_id
1391 AND batchstep_id = l_last_batchstep_id;
1392 ELSIF (p_step_cmplt_date < l_step_plan_cmplt_date) THEN
1393 l_gme_batch_header.batch_id := p_gme_batch_header_rec.batch_id;
1394 l_gme_batch_header.batch_type :=
1395 p_gme_batch_header_rec.batch_type;
1396 l_gme_batch_header.plan_cmplt_date := p_step_cmplt_date;
1397 gme_reschedule_batch_pvt.truncate_date
1398 (p_batch_header_rec => l_gme_batch_header
1399 ,p_date => 1
1400 ,p_batchstep_id => l_last_batchstep_id
1401 ,x_return_status => l_return_status);
1402
1403 IF l_return_status <> x_return_status THEN
1404 RAISE error_truncate_date;
1405 END IF;
1406 END IF; /* IF (p_step_cmplt_date > l_step_plan_cmplt_date) */
1407
1408 l_step_plan_cmplt_date := p_step_cmplt_date;
1409 END IF; /* p_step_cmplt_date is not null */
1410
1411 --picking the step values again
1412 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1413 gme_debug.put_line
1414 ( 'step start,cmplt dates for step after comparision with user step dates are'
1415 || TO_CHAR (l_step_plan_start_date, 'DD-MON-YYYY HH24:MI:SS')
1416 || ' '
1417 || TO_CHAR (l_step_plan_cmplt_date, 'DD-MON-YYYY HH24:MI:SS') );
1418 END IF;
1419
1420 --checking against the batch dates
1421 IF (l_step_plan_start_date < p_gme_batch_header_rec.plan_start_date) THEN
1422 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1423 gme_debug.put_line
1424 ('l_step_plan_start_date diff from batch start date');
1425 END IF;
1426
1427 gme_reschedule_batch_pvt.truncate_date
1428 (p_batch_header_rec => p_gme_batch_header_rec
1429 ,p_date => 0
1430 ,p_batchstep_id => l_last_batchstep_id
1431 ,x_return_status => l_return_status);
1432
1433 IF l_return_status <> x_return_status THEN
1434 RAISE error_truncate_date;
1435 END IF;
1436 END IF;
1437 /* (l_step_plan_start_date < p_gme_batch_header_rec.plan_start_date) */
1438
1439 IF (l_step_plan_cmplt_date > p_gme_batch_header_rec.plan_cmplt_date) THEN
1440 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1441 gme_debug.put_line
1442 ('l_step_plan_cmplt_date diff from batch cmplt date');
1443 END IF;
1444
1445 gme_reschedule_batch_pvt.truncate_date
1446 (p_batch_header_rec => p_gme_batch_header_rec
1447 ,p_date => 1
1448 ,p_batchstep_id => l_last_batchstep_id
1449 ,x_return_status => l_return_status);
1450
1451 IF l_return_status <> x_return_status THEN
1452 RAISE error_truncate_date;
1453 END IF;
1454 END IF;
1455 /* (l_step_plan_cmplt_date > p_gme_batch_header_rec.plan_cmplt_date) */
1456 END IF; /* IF (p_recipe_rout_step_tbl.COUNT = 1 AND */
1457
1458 OPEN cur_get_batchstep_ids (p_gme_batch_header_rec.batch_id);
1459
1460 FETCH cur_get_batchstep_ids
1461 BULK COLLECT INTO l_batchstep_ids_tab;
1462
1463 CLOSE cur_get_batchstep_ids;
1464
1465 FOR i IN 1 .. l_batchstep_ids_tab.COUNT LOOP
1466 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1467 gme_debug.put_line ( 'batch,step ids are'
1468 || p_gme_batch_header_rec.batch_id
1469 || l_batchstep_ids_tab (i) );
1470 END IF;
1471
1472 OPEN cur_is_charge_associated (p_gme_batch_header_rec.batch_id
1473 ,l_batchstep_ids_tab (i) );
1474
1475 FETCH cur_is_charge_associated
1476 INTO l_resources;
1477
1478 IF cur_is_charge_associated%FOUND THEN
1479 CLOSE cur_is_charge_associated;
1480
1481 OPEN cur_get_resource_dates (l_resources
1482 ,p_gme_batch_header_rec.batch_id
1483 ,l_batchstep_ids_tab (i) );
1484
1485 FETCH cur_get_resource_dates
1486 INTO l_rsrc_start_date, l_rsrc_cmplt_date;
1487
1488 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1489 gme_debug.put_line ( g_pkg_name
1490 || '.'
1491 || l_api_name
1492 || 'rsrc start,cmplt dates are'
1493 || l_rsrc_start_date
1494 || l_rsrc_cmplt_date);
1495 END IF;
1496
1497 CLOSE cur_get_resource_dates;
1498
1499 UPDATE gme_batch_step_charges
1500 SET plan_start_date = l_rsrc_start_date
1501 ,plan_cmplt_date = l_rsrc_cmplt_date
1502 WHERE batch_id = p_gme_batch_header_rec.batch_id
1503 AND batchstep_id = l_batchstep_ids_tab (i);
1504 ELSE
1505 CLOSE cur_is_charge_associated;
1506 END IF;
1507 END LOOP;
1508
1509 IF ( p_recipe_rout_step_tbl.COUNT = 1
1510 AND p_recipe_rout_step_tbl (1).routingstep_id IS NULL) THEN
1511 wf_event.RAISE (p_event_name => gme_common_pvt.G_BATCHSTEP_CREATED
1512 ,p_event_key => l_gme_batch_steps (1).batchstep_id);
1513 END IF;
1514
1515 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1516 gme_debug.put_line
1517 ( 'Done with Create Batch steps with return code = '
1518 || x_return_status);
1519 END IF;
1520
1521 IF g_debug <= gme_debug.g_log_procedure THEN
1522 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
1523 END IF;
1524 EXCEPTION
1525 WHEN error_insert_batch_step THEN
1526 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1527 gme_debug.put_line ('insert batch step error');
1528 END IF;
1529
1530 x_return_status := fnd_api.g_ret_sts_error;
1531 WHEN error_insert_b_step_act THEN
1532 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1533 gme_debug.put_line ('insert batch step activity error');
1534 END IF;
1535
1536 x_return_status := fnd_api.g_ret_sts_error;
1537 WHEN error_insert_b_step_res THEN
1538 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1539 gme_debug.put_line ('insert batch step resource error');
1540 END IF;
1541
1542 x_return_status := fnd_api.g_ret_sts_error;
1543 WHEN error_insert_res_txns THEN
1544 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1545 gme_debug.put_line ('insert resource txns error');
1546 END IF;
1547
1548 x_return_status := fnd_api.g_ret_sts_error;
1549 WHEN error_insert_b_res_param THEN
1550 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1551 gme_debug.put_line ('insert resource param error');
1552 END IF;
1553
1554 x_return_status := fnd_api.g_ret_sts_error;
1555 WHEN error_insert_b_step_items THEN
1556 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1557 gme_debug.put_line ('insert batch step items error');
1558 END IF;
1559
1560 x_return_status := fnd_api.g_ret_sts_error;
1561 WHEN error_insert_b_step_depend THEN
1562 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1563 gme_debug.put_line ('insert batch step depend error');
1564 END IF;
1565
1566 x_return_status := fnd_api.g_ret_sts_error;
1567 WHEN error_calc_step_qty THEN
1568 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1569 gme_debug.put_line ('calc step qty error');
1570 END IF;
1571
1572 x_return_status := fnd_api.g_ret_sts_error;
1573 WHEN error_calc_dates THEN
1574 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1575 gme_debug.put_line ('calc dates error');
1576 END IF;
1577
1578 x_return_status := l_return_status;
1579 WHEN error_calc_charges THEN
1580 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1581 gme_debug.put_line ('calc charge error');
1582 END IF;
1583
1584 x_return_status := fnd_api.g_ret_sts_error;
1585 WHEN error_create_text THEN
1586 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1587 gme_debug.put_line ('create text error');
1588 END IF;
1589
1590 x_return_status := l_return_status;
1591 WHEN process_qty_below_cap THEN
1592 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1593 gme_debug.put_line ('process qty below capacity');
1594 END IF;
1595
1596 x_return_status := fnd_api.g_ret_sts_error;
1597 WHEN invalid_resource_qty_usage THEN
1598 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1599 gme_debug.put_line
1600 ('invalild resource process quantity and usage');
1601 END IF;
1602 x_return_status := fnd_api.g_ret_sts_error;
1603 WHEN error_truncate_date THEN
1604 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1605 gme_debug.put_line ('truncate date procedure error');
1606 END IF;
1607
1608 x_return_status := fnd_api.g_ret_sts_error;
1609 --FPBug#4395561
1610 WHEN create_flex_failure THEN
1611 x_return_status := l_return_status;
1612 IF (NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT) THEN
1613 gme_debug.put_line ('Creating the default values of the DFF failure');
1614 END IF;
1615 WHEN OTHERS THEN
1616 IF g_debug <= gme_debug.g_log_unexpected THEN
1617 gme_debug.put_line ( 'When others exception in '
1618 || g_pkg_name
1619 || '.'
1620 || l_api_name
1621 || ' Error is '
1622 || SQLERRM);
1623 END IF;
1624
1625 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1626 x_return_status := fnd_api.g_ret_sts_unexp_error;
1627 END create_batch_steps;
1628
1629 PROCEDURE update_charges (
1630 p_batch_id IN NUMBER
1631 ,p_step_charge_rsrc_tab IN gme_create_step_pvt.step_charge_rsrc_tab
1632 ,x_return_status OUT NOCOPY VARCHAR2)
1633 IS
1634 x_charge_tab charge_tab;
1635 x_num_steps PLS_INTEGER;
1636 l_return_status VARCHAR2 (1);
1637 x_charge gme_batch_steps.plan_charges%TYPE;
1638 l_api_name CONSTANT VARCHAR2 (30) := 'update charges';
1639
1640 -- Cursor Definitions
1641 CURSOR cur_get_steps (v_batch_id NUMBER)
1642 IS
1643 SELECT batchstep_id, plan_mass_qty, mass_ref_um, plan_volume_qty
1644 ,volume_ref_um
1645 FROM gme_batch_steps
1646 WHERE batch_id = v_batch_id;
1647
1648 x_cur_step_rec cur_get_steps%ROWTYPE;
1649 error_calc_charge EXCEPTION;
1650 BEGIN
1651 IF g_debug <= gme_debug.g_log_procedure THEN
1652 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
1653 || l_api_name);
1654 END IF;
1655
1656 x_return_status := fnd_api.g_ret_sts_success;
1657 x_num_steps := 0;
1658
1659 OPEN cur_get_steps (p_batch_id);
1660
1661 FETCH cur_get_steps
1662 INTO x_cur_step_rec;
1663
1664 WHILE cur_get_steps%FOUND LOOP
1665 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1666 gme_debug.put_line
1667 ( g_pkg_name
1668 || '.'
1669 || l_api_name
1670 || 'p_step_charge_rsrc_tab(batchstep_id).resources is '
1671 || p_step_charge_rsrc_tab (x_cur_step_rec.batchstep_id).resources);
1672 END IF;
1673
1674 gme_update_step_qty_pvt.calc_charge
1675 (p_step_id => x_cur_step_rec.batchstep_id
1676 ,p_resources => p_step_charge_rsrc_tab
1677 (x_cur_step_rec.batchstep_id).resources
1678 ,p_mass_qty => x_cur_step_rec.plan_mass_qty
1679 ,p_vol_qty => x_cur_step_rec.plan_volume_qty
1680 ,x_charge => x_charge
1681 ,x_return_status => l_return_status);
1682
1683 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1684 gme_debug.put_line
1685 ( ' update charges >calc charges > return charge '
1686 || TO_CHAR (x_charge) );
1687 gme_debug.put_line
1688 ( ' update charges >calc charges >batchstep_id '
1689 || TO_CHAR (x_cur_step_rec.batchstep_id) );
1690 END IF;
1691
1692 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1693 RAISE error_calc_charge;
1694 END IF;
1695
1696 x_num_steps := x_num_steps + 1;
1697 x_charge_tab (x_num_steps).step_id := x_cur_step_rec.batchstep_id;
1698 x_charge_tab (x_num_steps).charge := x_charge;
1699
1700 FETCH cur_get_steps
1701 INTO x_cur_step_rec;
1702 END LOOP;
1703
1704 FOR i IN 1 .. x_charge_tab.COUNT LOOP
1705 UPDATE gme_batch_steps
1706 SET plan_charges = x_charge_tab (i).charge
1707 WHERE batchstep_id = x_charge_tab (i).step_id;
1708 END LOOP;
1709
1710 IF g_debug <= gme_debug.g_log_procedure THEN
1711 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
1712 END IF;
1713 EXCEPTION
1714 WHEN error_calc_charge THEN
1715 x_return_status := l_return_status;
1716 WHEN OTHERS THEN
1717 IF g_debug <= gme_debug.g_log_unexpected THEN
1718 gme_debug.put_line ( 'When others exception in '
1719 || g_pkg_name
1720 || '.'
1721 || l_api_name
1722 || ' Error is '
1723 || SQLERRM);
1724 END IF;
1725
1726 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1727 x_return_status := fnd_api.g_ret_sts_unexp_error;
1728 END update_charges;
1729
1730 /*======================================================================
1731 -- FUNCTION :
1732 -- get_max_step_date
1733 --
1734 -- DESCRIPTION:
1735 -- This PL/SQL function is responsible for calculating max step
1736 -- dates based on shop calendar or otherwise
1737 --
1738 -- REQUIREMENTS
1739 -- p_gme_batch_header_rec non null value.
1740 -- SYNOPSIS:
1741 --
1742 --===================================================================== */
1743 FUNCTION get_max_step_date (
1744 p_use_workday_cal IN VARCHAR2
1745 ,p_calendar_code IN VARCHAR2
1746 ,p_batchstep_id IN NUMBER
1747 ,p_batch_id IN NUMBER
1748 ,p_batch_start_date IN DATE)
1749 RETURN DATE
1750 IS
1751 CURSOR cur_get_step_date_4_cal (v_step_id NUMBER, v_batch_id NUMBER)
1752 IS
1753 SELECT dep_type, r.plan_start_date, r.plan_cmplt_date
1754 ,standard_delay
1755 FROM gme_batch_step_dependencies d, gme_batch_steps r
1756 WHERE d.batch_id = r.batch_id
1757 AND d.batch_id = v_batch_id
1758 AND r.batchstep_id = d.dep_step_id
1759 AND d.batchstep_id = v_step_id;
1760
1761 CURSOR cur_get_step_date (v_step_id NUMBER, v_batch_id NUMBER)
1762 IS
1763 SELECT MAX (DECODE (dep_type
1764 ,1, r.plan_start_date + standard_delay / 24
1765 ,0, r.plan_cmplt_date + standard_delay / 24) )
1766 FROM gme_batch_step_dependencies d, gme_batch_steps r
1767 WHERE d.batch_id = r.batch_id
1768 AND d.batch_id = v_batch_id
1769 AND r.batchstep_id = d.dep_step_id
1770 AND d.batchstep_id = v_step_id;
1771
1772 l_date DATE;
1773 l_max_date DATE;
1774 l_plan_start_date DATE;
1775 l_api_name CONSTANT VARCHAR2 (30) := 'get_max_step_date';
1776 BEGIN
1777 IF g_debug <= gme_debug.g_log_procedure THEN
1778 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
1779 || l_api_name);
1780 END IF;
1781
1782 l_max_date := NULL;
1783
1784 IF p_use_workday_cal = fnd_api.g_true THEN
1785 FOR rec IN cur_get_step_date_4_cal (p_batchstep_id, p_batch_id) LOOP
1786 IF rec.dep_type = 1 THEN
1787 l_date := rec.plan_start_date;
1788 ELSE
1789 l_date := rec.plan_cmplt_date;
1790 END IF;
1791
1792 l_plan_start_date :=
1793 get_working_start_time (p_start_date => l_date
1794 ,p_offset => rec.standard_delay
1795 ,p_calendar_code => p_calendar_code);
1796
1797 IF l_plan_start_date IS NULL THEN
1798 RETURN NULL;
1799 END IF;
1800
1801 IF l_max_date IS NULL OR l_max_date < l_plan_start_date THEN
1802 l_max_date := l_plan_start_date;
1803 END IF;
1804 END LOOP;
1805 ELSE /* p_use_workday_cal = FND_API.G_TRUE */
1806 OPEN cur_get_step_date (p_batchstep_id, p_batch_id);
1807
1808 FETCH cur_get_step_date
1809 INTO l_date;
1810
1811 IF (cur_get_step_date%FOUND) AND (l_date IS NOT NULL) THEN
1812 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1813 gme_debug.put_line ( 'GET_MAX_STEP_DATE: from cursor'
1814 || TO_CHAR (l_date
1815 ,'DD-MON-YYYY HH24:MI:SS') );
1816 END IF;
1817
1818 l_max_date := l_date;
1819 ELSE
1820 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1821 gme_debug.put_line ('No prior step here');
1822 END IF;
1823 END IF;
1824
1825 CLOSE cur_get_step_date;
1826 END IF; /* p_use_workday_cal = FND_API.G_TRUE */
1827
1828 IF l_max_date IS NULL THEN
1829 l_max_date := p_batch_start_date;
1830 END IF;
1831
1832 IF g_debug <= gme_debug.g_log_procedure THEN
1833 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
1834 END IF;
1835
1836 RETURN l_max_date;
1837 EXCEPTION
1838 WHEN OTHERS THEN
1839 IF g_debug <= gme_debug.g_log_unexpected THEN
1840 gme_debug.put_line ( 'When others exception in '
1841 || g_pkg_name
1842 || '.'
1843 || l_api_name
1844 || ' Error is '
1845 || SQLERRM);
1846 END IF;
1847
1848 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1849 RETURN NULL;
1850 END get_max_step_date;
1851
1852 /*======================================================================
1853 -- FUNCTION :
1854 -- get_working_start_time
1855 --
1856 -- DESCRIPTION:
1857 -- This PL/SQL function gets the date and checks to see
1858 -- if the date + offset is a valid working datetime If
1859 -- it is not working datetime then function finds next
1860 -- working datetime and returns it.
1861 -- In case of some error it returns NULL.
1862 -- REQUIREMENTS
1863 -- p_start_date DATE
1864 -- p_offset NUMBER in hours
1865 -- p_calendar_code VARCHAR2 Calendar CODE
1866 -- SYNOPSIS:
1867 --
1868 --
1869 --
1870 --===================================================================== */
1871 FUNCTION get_working_start_time (
1872 p_start_date IN DATE
1873 ,p_offset IN NUMBER
1874 ,p_calendar_code IN VARCHAR2)
1875 RETURN DATE
1876 IS
1877 l_contig_period_tbl gmp_calendar_api.contig_period_tbl;
1878 l_diff NUMBER;
1879 l_start_date DATE;
1880 l_cal_count NUMBER;
1881 l_return_status VARCHAR2 (1);
1882 l_api_name CONSTANT VARCHAR2 (30) := 'get_working_time';
1883 BEGIN
1884 IF g_debug <= gme_debug.g_log_procedure THEN
1885 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
1886 || l_api_name);
1887 END IF;
1888
1889 IF p_start_date IS NULL THEN
1890 RETURN NULL;
1891 END IF;
1892
1893 IF p_offset >= 0 THEN
1894 IF NVL (g_debug, -1) = gme_debug.g_log_statement THEN
1895 gme_debug.put_line ('calling get_contiguous_periods from point 1 get_working_time'); -- Bug 13582990
1896 gme_debug.put_line ('start date passed in is '||TO_CHAR(p_start_date, 'DD-MON-YYYY HH24:MI:SS')); -- Bug 13582990
1897 gme_debug.put_line ('duration passed in is '||p_offset); -- Bug 13582990
1898 gme_debug.put_line ('p_calendar_code passed in is '||p_calendar_code); -- Bug 13582990
1899 END IF;
1900 gmp_calendar_api.get_contiguous_periods
1901 (p_api_version => 1
1902 ,p_init_msg_list => TRUE
1903 ,p_start_date => p_start_date
1904 ,p_end_date => NULL
1905 ,p_calendar_code => p_calendar_code
1906 ,p_duration => p_offset
1907 ,p_output_tbl => l_contig_period_tbl
1908 ,x_return_status => l_return_status);
1909
1910 IF (l_return_status <> 'S') THEN
1911 RETURN NULL;
1912 END IF;
1913
1914 l_cal_count := l_contig_period_tbl.COUNT;
1915 l_start_date := l_contig_period_tbl (l_cal_count).end_date;
1916
1917 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1918 gme_debug.put_line ('rows returned from get_contiguous_periods is '||l_cal_count); -- Bug 13582990
1919 END IF;
1920 ELSE /* p_offset >= 0 */
1921 IF NVL (g_debug, -1) = gme_debug.g_log_statement THEN
1922 gme_debug.put_line ('calling get_contiguous_periods from point 2 get_working_time'); -- Bug 13582990
1923 gme_debug.put_line ('END date passed in is '||TO_CHAR(p_start_date, 'DD-MON-YYYY HH24:MI:SS')); -- Bug 13582990
1924 gme_debug.put_line ('duration passed in is '||ABS(p_offset)); -- Bug 13582990
1925 gme_debug.put_line ('p_calendar_code passed in is '||p_calendar_code); -- Bug 13582990
1926 END IF;
1927 gmp_calendar_api.get_contiguous_periods
1928 (p_api_version => 1
1929 ,p_init_msg_list => TRUE
1930 ,p_start_date => NULL
1931 ,p_end_date => p_start_date
1932 ,p_calendar_code => p_calendar_code
1933 ,p_duration => ABS (p_offset)
1934 ,p_output_tbl => l_contig_period_tbl
1935 ,x_return_status => l_return_status);
1936
1937 IF (l_return_status <> 'S') THEN
1938 RETURN NULL;
1939 END IF;
1940
1941 l_cal_count := l_contig_period_tbl.COUNT;
1942 l_start_date := l_contig_period_tbl (l_cal_count).start_date;
1943
1944 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1945 gme_debug.put_line ('rows returned from get_contiguous_periods is '||l_cal_count); -- Bug 13582990
1946 END IF;
1947 END IF; /* p_offset >= 0 */
1948
1949 IF gmp_calendar_api.is_working_daytime
1950 (p_api_version => 1
1951 ,p_init_msg_list => TRUE
1952 ,p_calendar_code => p_calendar_code
1953 ,p_date => l_start_date
1954 ,p_ind => 0
1955 ,x_return_status => l_return_status) THEN
1956 RETURN l_start_date;
1957 ELSE /* gmp_calendar_api.is_working_daytime */
1958 l_diff := 1 / 3600;
1959
1960 IF NVL (g_debug, -1) = gme_debug.g_log_statement THEN
1961 gme_debug.put_line (l_api_name || ':l_diff ' || l_diff);
1962 END IF;
1963
1964 IF p_offset > 0 THEN
1965 /* If offset was 0 then the l_start_date is the working date time */
1966 IF NVL (g_debug, -1) = gme_debug.g_log_statement THEN
1967 gme_debug.put_line ('calling get_contiguous_periods from point 3 get_working_time'); -- Bug 13582990
1968 gme_debug.put_line ('start date passed in is '||TO_CHAR(l_start_date, 'DD-MON-YYYY HH24:MI:SS')); -- Bug 13582990
1969 gme_debug.put_line ('duration passed in is '||l_diff); -- Bug 13582990
1970 gme_debug.put_line ('p_calendar_code passed in is '||p_calendar_code); -- Bug 13582990
1971 END IF;
1972 gmp_calendar_api.get_contiguous_periods
1973 (p_api_version => 1
1974 ,p_init_msg_list => TRUE
1975 ,p_start_date => l_start_date
1976 ,p_end_date => NULL
1977 ,p_calendar_code => p_calendar_code
1978 ,p_duration => l_diff
1979 ,p_output_tbl => l_contig_period_tbl
1980 ,x_return_status => l_return_status);
1981
1982 IF (l_return_status <> 'S') THEN
1983 RETURN NULL;
1984 END IF;
1985
1986 l_cal_count := l_contig_period_tbl.COUNT;
1987 l_start_date :=
1988 (l_contig_period_tbl (l_cal_count).end_date - (l_diff / 24) );
1989
1990 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1991 gme_debug.put_line ('rows returned from get_contiguous_periods is '||l_cal_count); -- Bug 13582990
1992 END IF;
1993 ELSE /* p_offset > 0 */
1994 IF NVL (g_debug, -1) = gme_debug.g_log_statement THEN
1995 gme_debug.put_line ('calling get_contiguous_periods from point 4 get_working_time'); -- Bug 13582990
1996 gme_debug.put_line ('END date passed in is '||TO_CHAR(l_start_date, 'DD-MON-YYYY HH24:MI:SS')); -- Bug 13582990
1997 gme_debug.put_line ('duration passed in is '||l_diff); -- Bug 13582990
1998 gme_debug.put_line ('p_calendar_code passed in is '||p_calendar_code); -- Bug 13582990
1999 END IF;
2000 gmp_calendar_api.get_contiguous_periods
2001 (p_api_version => 1
2002 ,p_init_msg_list => TRUE
2003 ,p_start_date => NULL
2004 ,p_end_date => l_start_date
2005 ,p_calendar_code => p_calendar_code
2006 ,p_duration => l_diff
2007 ,p_output_tbl => l_contig_period_tbl
2008 ,x_return_status => l_return_status);
2009
2010 IF (l_return_status <> 'S') THEN
2011 RETURN NULL;
2012 END IF;
2013
2014 l_cal_count := l_contig_period_tbl.COUNT;
2015 l_start_date :=
2016 (l_contig_period_tbl (l_cal_count).start_date + (l_diff / 24) );
2017
2018 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2019 gme_debug.put_line ('rows returned from get_contiguous_periods is '||l_cal_count); -- Bug 13582990
2020 END IF;
2021 END IF; /* p_offset > 0 */
2022 END IF; /* gmp_calendar_api.is_working_daytime */
2023
2024 IF g_debug <= gme_debug.g_log_procedure THEN
2025 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
2026 END IF;
2027
2028 RETURN l_start_date;
2029 EXCEPTION
2030 WHEN OTHERS THEN
2031 IF g_debug <= gme_debug.g_log_unexpected THEN
2032 gme_debug.put_line ( 'When others exception in '
2033 || g_pkg_name
2034 || '.'
2035 || l_api_name
2036 || ' Error is '
2037 || SQLERRM);
2038 END IF;
2039
2040 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2041 RETURN NULL;
2042 END get_working_start_time;
2043
2044 /*======================================================================
2045 -- PROCEDURE :
2046 -- calc_dates
2047 --
2048 -- DESCRIPTION:
2049 -- This PL/SQL procedure is responsible for calculating dates for the batch
2050 --
2051 -- REQUIREMENTS
2052 -- p_gme_batch_header_rec non null value.
2053 -- SYNOPSIS:
2054 -- calc_dates (p_gme_batch_header_rec,p_use_workday_cal, X_return_status);
2055 -- -- Pawan Kumar bug 823188 added for shop calendar
2056 -- Added additional parameters for the shop calendar implementation.
2057 --===================================================================== */
2058 PROCEDURE calc_dates (
2059 p_gme_batch_header_rec IN gme_batch_header%ROWTYPE
2060 ,p_use_workday_cal IN VARCHAR2
2061 ,p_contiguity_override IN VARCHAR2
2062 ,p_return_status OUT NOCOPY VARCHAR2
2063 ,p_step_id IN gme_batch_steps.batchstep_id%TYPE
2064 DEFAULT NULL
2065 ,p_plan_start_date IN DATE DEFAULT NULL
2066 ,p_plan_cmplt_date IN DATE DEFAULT NULL)
2067 IS
2068 x_cur_row PLS_INTEGER;
2069 x_step_tbl step_tab;
2070 l_step_duration_tab step_duration_tab;
2071 x_act_tab activities_tab;
2072 x_act_row gme_batch_step_activities%ROWTYPE;
2073 x_rsrc_tab resources_tab;
2074 x_rsrc_row gme_batch_step_resources%ROWTYPE;
2075 x_rsrc_txns_tab rsrc_txns_tab;
2076 x_step_row gme_batch_steps%ROWTYPE;
2077 x_max_act_date DATE;
2078 x_max_rsrc_date DATE;
2079 l_return_status VARCHAR2 (1);
2080 x_routing_id gme_batch_header.routing_id%TYPE;
2081 x_gmd_step_tbl gmd_auto_step_calc.step_rec_tbl;
2082 l_batch_id gme_batch_header.batch_id%TYPE;
2083 x_batch_duration NUMBER;
2084 x_batch_start_date DATE;
2085 x_step_start_date DATE;
2086 x_step_no gme_batch_steps.batchstep_no%TYPE;
2087 max_cmplt_date DATE;
2088 min_start_date DATE;
2089 l_usage_hrs gme_batch_step_resources.plan_rsrc_usage%TYPE;
2090 l_batchstep_activity_id NUMBER;
2091 l_batchstep_id NUMBER;
2092 l_cal_count NUMBER;
2093 l_contig_period_tbl gmp_calendar_api.contig_period_tbl;
2094 l_duration NUMBER;
2095 l_activity VARCHAR2 (80);
2096 l_api_name CONSTANT VARCHAR2 (30) := 'Calc dates';
2097
2098 CURSOR cur_get_step_activities (v_step_id NUMBER, v_batch_id NUMBER)
2099 IS
2100 SELECT *
2101 FROM gme_batch_step_activities
2102 WHERE batch_id = v_batch_id AND batchstep_id = v_step_id;
2103
2104 CURSOR cur_get_act_rsrcs (v_batchstep_activity_id NUMBER)
2105 IS
2106 SELECT *
2107 FROM gme_batch_step_resources
2108 WHERE batchstep_activity_id = v_batchstep_activity_id;
2109
2110 CURSOR cur_get_step_id (v_step NUMBER, v_batch_id NUMBER)
2111 IS
2112 SELECT batchstep_id
2113 FROM gme_batch_steps
2114 WHERE batch_id = v_batch_id AND batchstep_no = v_step;
2115
2116 CURSOR cur_get_step (v_batchstep_id NUMBER)
2117 IS
2118 SELECT batchstep_no
2119 FROM gme_batch_steps
2120 WHERE batchstep_id = v_batchstep_id;
2121
2122 CURSOR cur_get_activity (v_batchstep_activity_id NUMBER)
2123 IS
2124 SELECT activity
2125 FROM gme_batch_step_activities
2126 WHERE batchstep_activity_id = v_batchstep_activity_id;
2127
2128 CURSOR cur_recipe_validity_rule (
2129 p_recipe_validity_rule_id gme_batch_header.recipe_validity_rule_id%TYPE)
2130 IS
2131 SELECT inventory_item_id
2132 FROM gmd_recipe_validity_rules
2133 WHERE recipe_validity_rule_id = p_recipe_validity_rule_id;
2134
2135 load_steps_failed EXCEPTION;
2136 no_activities EXCEPTION;
2137 no_resources EXCEPTION;
2138 error_insert_res_txns EXCEPTION;
2139 error_cont_period EXCEPTION;
2140 error_non_contiguious EXCEPTION;
2141 l_doc_type VARCHAR2 (4);
2142 l_calendar_code VARCHAR2 (10);
2143 l_cont_ind NUMBER;
2144 l_item_id NUMBER;
2145 l_use_workday_cal VARCHAR2 (5);
2146 BEGIN
2147 IF g_debug <= gme_debug.g_log_procedure THEN
2148 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
2149 || l_api_name);
2150 END IF;
2151
2152 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2153 gme_debug.put_line ('start calc_dates');
2154 gme_debug.put_line ('p_step_id is '||p_step_id); -- Bug 13582990
2155 gme_debug.put_line ('p_use_workday_cal is '||p_use_workday_cal); -- Bug 13582990
2156 gme_debug.put_line ('p_contiguity_override is '||p_contiguity_override); -- Bug 13582990
2157 END IF;
2158
2159 p_return_status := fnd_api.g_ret_sts_success;
2160 l_batch_id := p_gme_batch_header_rec.batch_id;
2161
2162 IF (p_gme_batch_header_rec.batch_type = 0) THEN
2163 l_doc_type := 'PROD';
2164 ELSE
2165 l_doc_type := 'FPO';
2166 END IF;
2167
2168 l_use_workday_cal := p_use_workday_cal;
2169
2170 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2171 gme_debug.put_line ('batch_id=' || l_batch_id);
2172 END IF;
2173
2174 l_calendar_code := gme_common_pvt.g_calendar_code;
2175
2176 IF l_calendar_code IS NULL THEN
2177 l_use_workday_cal := fnd_api.g_false;
2178 ELSE
2179 OPEN cur_recipe_validity_rule
2180 (p_gme_batch_header_rec.recipe_validity_rule_id);
2181
2182 FETCH cur_recipe_validity_rule
2183 INTO l_item_id;
2184
2185 CLOSE cur_recipe_validity_rule;
2186
2187 l_cont_ind := 1;
2188 --Assigning hard coded value now have to use GMD api
2189 END IF;
2190
2191 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2192 gme_debug.put_line ('cal code is '||l_calendar_code); -- Bug 13582990
2193 END IF;
2194
2195 /* If P_step_id is NULL, this means we want to process all steps in the batch. If */
2196 /* it is not NULL, then we only want to calc the dates for that step, i.e. insert_step... */
2197 IF (p_step_id IS NULL) THEN
2198 gmd_auto_step_calc.load_steps (l_batch_id
2199 ,1
2200 ,NULL
2201 ,x_gmd_step_tbl
2202 ,x_routing_id
2203 ,l_return_status);
2204
2205 IF l_return_status <> p_return_status THEN
2206 RAISE load_steps_failed;
2207 END IF;
2208
2209 /* populate X_step_tbl with the step_id that corresponds to that returned from the GMD load_steps */
2210 FOR i IN x_gmd_step_tbl.FIRST .. x_gmd_step_tbl.LAST LOOP
2211 OPEN cur_get_step_id (x_gmd_step_tbl (i).step_no, l_batch_id);
2212
2213 FETCH cur_get_step_id
2214 INTO x_step_tbl (i);
2215
2216 CLOSE cur_get_step_id;
2217 END LOOP;
2218 ELSE
2219 x_step_tbl (1) := p_step_id;
2220 x_gmd_step_tbl (1).step_id := p_step_id;
2221
2222 SELECT batchstep_no
2223 INTO x_gmd_step_tbl (1).step_no
2224 FROM gme_batch_steps
2225 WHERE batchstep_id = p_step_id;
2226 END IF;
2227
2228 -- Calculate the duration of each step...
2229 FOR i IN x_step_tbl.FIRST .. x_step_tbl.LAST LOOP
2230 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2231 gme_debug.put_line ('step_no ' || x_gmd_step_tbl (i).step_no);
2232 gme_debug.put_line ('step_id ' || x_gmd_step_tbl (i).step_id);
2233 END IF;
2234
2235 l_step_duration_tab (x_gmd_step_tbl (i).step_no) :=
2236 get_max_duration (x_step_tbl (i), l_batch_id);
2237
2238 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2239 gme_debug.put_line
2240 ( 'stepduration '
2241 || l_step_duration_tab
2242 (x_gmd_step_tbl (i).step_no) );
2243 END IF;
2244 END LOOP;
2245
2246 x_batch_start_date := p_gme_batch_header_rec.plan_start_date;
2247 x_step_start_date := p_plan_start_date;
2248
2249 IF (p_step_id IS NOT NULL) THEN
2250 -- This is a step insert... calc the start date with the cmplt date and duration...
2251 -- If the start date is NOT NULL, then we use that... don't care if planned completion date is filled in... we'll calc anyway
2252 -- If both dates are null, that's OK too, the code below will use the batch plan start date.
2253 IF (p_plan_start_date IS NULL) AND (p_plan_cmplt_date IS NOT NULL) THEN
2254 SELECT batchstep_no
2255 INTO x_step_no
2256 FROM gme_batch_steps
2257 WHERE batchstep_id = p_step_id;
2258
2259 IF l_use_workday_cal = fnd_api.g_true THEN
2260 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2261 gme_debug.put_line ('calling get_contiguous_periods from point 1 calc_dates'); -- Bug 13582990
2262 gme_debug.put_line ('plan_cmplt date is not null used for END DATE parm is '||
2263 TO_CHAR(p_plan_cmplt_date,'DD-MON-YYYY HH24:MI:SS'));
2264 gme_debug.put_line ('duration passed in is '||l_step_duration_tab(x_step_no)); -- Bug 13582990
2265 gme_debug.put_line ('p_calendar_code passed in is '||l_calendar_code); -- Bug 13582990
2266 END IF;
2267
2268 gmp_calendar_api.get_contiguous_periods
2269 (p_api_version => 1
2270 ,p_init_msg_list => TRUE
2271 ,p_start_date => NULL
2272 ,p_end_date => p_plan_cmplt_date
2273 ,p_calendar_code => l_calendar_code
2274 ,p_duration => l_step_duration_tab(x_step_no)
2275 ,p_output_tbl => l_contig_period_tbl
2276 ,x_return_status => l_return_status);
2277
2278 IF (l_return_status <> p_return_status) THEN
2279 RAISE error_cont_period;
2280 END IF;
2281
2282 l_cal_count := l_contig_period_tbl.COUNT;
2283
2284 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2285 gme_debug.put_line ('rows returned from get_contiguous_periods is '||l_cal_count); -- Bug 13582990
2286 END IF;
2287
2288 IF l_cont_ind = 1 AND p_contiguity_override = fnd_api.g_false THEN
2289 IF l_cal_count > 1 THEN
2290 RAISE error_non_contiguious;
2291 END IF;
2292 END IF;
2293
2294 x_step_start_date :=
2295 l_contig_period_tbl (l_cal_count).start_date;
2296 ELSE
2297 x_step_start_date :=
2298 p_plan_cmplt_date - l_step_duration_tab (x_step_no)
2299 / 24;
2300 END IF;
2301
2302 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2303 gme_debug.put_line ( 'calculated step start date is '
2304 || TO_CHAR (x_step_start_date
2305 ,'DD-MON-YYYY HH24:MI:SS') );
2306 END IF;
2307 END IF;
2308 ELSE
2309 IF (p_gme_batch_header_rec.plan_start_date IS NOT NULL) THEN
2310 calc_longest_time (l_batch_id
2311 ,l_step_duration_tab
2312 ,x_batch_duration
2313 ,l_return_status);
2314
2315 IF l_use_workday_cal = fnd_api.g_true THEN
2316 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2317 gme_debug.put_line ('calling get_contiguous_periods from point 2 calc_dates'); -- Bug 13582990
2318 gme_debug.put_line ('start date is not null used for START DATE parm is '||
2319 TO_CHAR(p_gme_batch_header_rec.plan_start_date,'DD-MON-YYYY HH24:MI:SS'));
2320 gme_debug.put_line ('duration passed in is '||x_batch_duration); -- Bug 13582990
2321 gme_debug.put_line ('p_calendar_code passed in is '||l_calendar_code); -- Bug 13582990
2322 END IF;
2323
2324 gmp_calendar_api.get_contiguous_periods
2325 (p_api_version => 1
2326 ,p_init_msg_list => TRUE
2327 ,p_start_date => p_gme_batch_header_rec.plan_start_date
2328 ,p_end_date => NULL
2329 ,p_calendar_code => l_calendar_code
2330 ,p_duration => x_batch_duration
2331 ,p_output_tbl => l_contig_period_tbl
2332 ,x_return_status => l_return_status);
2333
2334 IF (l_return_status <> p_return_status) THEN
2335 RAISE error_cont_period;
2336 END IF;
2337
2338 l_cal_count := l_contig_period_tbl.COUNT;
2339
2340 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2341 gme_debug.put_line ('rows returned from get_contiguous_periods is '||l_cal_count); -- Bug 13582990
2342 END IF;
2343
2344 IF l_cont_ind = 1 AND p_contiguity_override = fnd_api.g_false THEN
2345 IF l_cal_count > 1 THEN
2346 RAISE error_non_contiguious;
2347 END IF;
2348 END IF;
2349 END IF;
2350
2351 x_batch_start_date := p_gme_batch_header_rec.plan_start_date;
2352 ELSIF (p_gme_batch_header_rec.plan_start_date IS NULL)
2353 AND (p_gme_batch_header_rec.plan_cmplt_date IS NOT NULL) THEN
2354 -- calc longest time for the entire batch if we're only given cmplt date...
2355 calc_longest_time (l_batch_id
2356 ,l_step_duration_tab
2357 ,x_batch_duration
2358 ,l_return_status);
2359
2360 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2361 gme_debug.put_line ('duration of batch is ' || x_batch_duration);
2362 gme_debug.put_line
2363 ( 'batch completion date is '
2364 || TO_CHAR
2365 (p_gme_batch_header_rec.plan_cmplt_date
2366 ,'DD-MON-YYYY HH24:MI:SS') );
2367 END IF;
2368
2369 IF l_use_workday_cal = fnd_api.g_true THEN
2370 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2371 gme_debug.put_line ('calling get_contiguous_periods from point 3 calc_dates'); -- Bug 13582990
2372 -- gme_debug.put_line ('end date null ' || l_use_workday_cal);
2373 gme_debug.put_line ('plan_cmplt_date is not null used for END DATE parm is '||
2374 TO_CHAR(p_gme_batch_header_rec.plan_cmplt_date,'DD-MON-YYYY HH24:MI:SS'));
2375 gme_debug.put_line ('duration passed in is '||x_batch_duration); -- Bug 13582990
2376 gme_debug.put_line ('p_calendar_code passed in is '||l_calendar_code); -- Bug 13582990
2377 END IF;
2378
2379 gmp_calendar_api.get_contiguous_periods
2380 (p_api_version => 1
2381 ,p_init_msg_list => TRUE
2382 ,p_start_date => NULL
2383 ,p_end_date => p_gme_batch_header_rec.plan_cmplt_date
2384 ,p_calendar_code => l_calendar_code
2385 ,p_duration => x_batch_duration
2386 ,p_output_tbl => l_contig_period_tbl
2387 ,x_return_status => l_return_status);
2388
2389 IF (l_return_status <> p_return_status) THEN
2390 RAISE error_cont_period;
2391 END IF;
2392
2393 l_cal_count := l_contig_period_tbl.COUNT;
2394
2395 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2396 gme_debug.put_line ('rows returned from get_contiguous_periods is '||l_cal_count); -- Bug 13582990
2397 END IF;
2398
2399 IF l_cont_ind = 1 AND p_contiguity_override = fnd_api.g_false THEN
2400 IF l_cal_count > 1 THEN
2401 RAISE error_non_contiguious;
2402 END IF;
2403 END IF;
2404
2405 x_batch_start_date :=
2406 l_contig_period_tbl (l_cal_count).start_date;
2407 ELSE
2408 x_batch_start_date :=
2409 p_gme_batch_header_rec.plan_cmplt_date
2410 - x_batch_duration / 24;
2411 END IF;
2412
2413 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2414 gme_debug.put_line ( 'calculated batch start date is '
2415 || TO_CHAR (x_batch_start_date
2416 ,'DD-MON-YYYY HH24:MI:SS') );
2417 END IF;
2418 ELSIF (p_gme_batch_header_rec.plan_start_date IS NULL)
2419 AND (p_gme_batch_header_rec.plan_cmplt_date IS NULL) THEN
2420 calc_longest_time (l_batch_id
2421 ,l_step_duration_tab
2422 ,x_batch_duration
2423 ,l_return_status);
2424
2425 IF l_use_workday_cal = fnd_api.g_true THEN
2426 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2427 gme_debug.put_line ('calling get_contiguous_periods from point 4 calc_dates'); -- Bug 13582990
2428 gme_debug.put_line ('Both dates null. p_contiguity_override is '|| p_contiguity_override); -- Bug 13582990
2429 gme_debug.put_line ('batch completion is '||
2430 TO_CHAR(p_gme_batch_header_rec.plan_cmplt_date,'DD-MON-YYYY HH24:MI:SS')); -- Bug 13582990
2431 gme_debug.put_line ('start date passed in gme_common_pvt.g_timestamp is '||
2432 TO_CHAR(gme_common_pvt.g_timestamp,'DD-MON-YYYY HH24:MI:SS')); -- Bug 13582990
2433 gme_debug.put_line ('duration passed in is '||x_batch_duration); -- Bug 13582990
2434 gme_debug.put_line ('p_calendar_code passed in is '||l_calendar_code); -- Bug 13582990
2435 END IF;
2436
2437 gmp_calendar_api.get_contiguous_periods
2438 (p_api_version => 1
2439 ,p_init_msg_list => TRUE
2440 ,p_start_date => gme_common_pvt.g_timestamp
2441 ,p_end_date => NULL
2442 ,p_calendar_code => l_calendar_code
2443 ,p_duration => x_batch_duration
2444 ,p_output_tbl => l_contig_period_tbl
2445 ,x_return_status => l_return_status);
2446
2447 IF (l_return_status <> p_return_status) THEN
2448 RAISE error_cont_period;
2449 END IF;
2450
2451 l_cal_count := l_contig_period_tbl.COUNT;
2452
2453 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2454 gme_debug.put_line ('rows returned from get_contiguous_periods is '||l_cal_count); -- Bug 13582990
2455 END IF;
2456
2457 IF l_cont_ind = 1 AND p_contiguity_override = fnd_api.g_false THEN
2458 IF l_cal_count > 1 THEN
2459 RAISE error_non_contiguious;
2460 END IF;
2461 END IF;
2462 END IF;
2463
2464 x_batch_start_date := gme_common_pvt.g_timestamp;
2465 END IF;
2466 END IF;
2467
2468 /* Calculate the dates for all the rows in X_step_tbl */
2469 FOR i IN x_step_tbl.FIRST .. x_step_tbl.LAST LOOP
2470 SELECT *
2471 INTO x_step_row
2472 FROM gme_batch_steps
2473 WHERE batchstep_id = x_step_tbl(i);
2474
2475 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2476 gme_debug.put_line ('step_no=' || x_step_row.batchstep_no); -- Bug 13582990
2477 gme_debug.put_line ('step_id=' || x_step_tbl(i) );
2478 END IF;
2479
2480 -- For individual steps you can pass in a plan_start date or use the setup data.
2481 IF (x_step_start_date IS NULL) THEN
2482 x_step_row.plan_start_date :=
2483 get_max_step_date (p_use_workday_cal => p_use_workday_cal
2484 ,p_calendar_code => l_calendar_code
2485 ,p_batchstep_id => x_step_tbl (i)
2486 ,p_batch_id => l_batch_id
2487 ,p_batch_start_date => x_batch_start_date);
2488 ELSE
2489 x_step_row.plan_start_date := x_step_start_date;
2490 END IF;
2491
2492 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2493 gme_debug.put_line ( '1 '
2494 || TO_CHAR (x_step_row.plan_start_date
2495 ,'DD-MON-YYYY HH24:MI:SS') );
2496 END IF;
2497
2498 x_act_tab.DELETE;
2499 x_cur_row := 0;
2500
2501 OPEN cur_get_step_activities (x_step_tbl (i), l_batch_id);
2502
2503 FETCH cur_get_step_activities
2504 INTO x_act_row;
2505
2506 WHILE cur_get_step_activities%FOUND LOOP
2507 x_cur_row := x_cur_row + 1;
2508 x_act_tab (x_cur_row) := x_act_row;
2509
2510 FETCH cur_get_step_activities
2511 INTO x_act_row;
2512 END LOOP;
2513
2514 CLOSE cur_get_step_activities;
2515
2516 IF x_cur_row = 0 THEN
2517 l_batchstep_id := x_step_tbl (i);
2518 RAISE no_activities;
2519 END IF;
2520
2521 FOR j IN x_act_tab.FIRST .. x_act_tab.LAST LOOP
2522 IF l_use_workday_cal = fnd_api.g_true THEN
2523 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2524 gme_debug.put_line (' activity is '|| x_act_tab(j).activity);
2525 gme_debug.put_line (' step start date '
2526 || TO_CHAR (x_step_row.plan_start_date
2527 ,'DD-MON-YYYY HH24:MI:SS') );
2528 END IF;
2529
2530 x_act_tab (j).plan_start_date :=
2531 get_working_start_time
2532 (p_start_date => x_step_row.plan_start_date
2533 ,p_offset => x_act_tab (j).offset_interval
2534 ,p_calendar_code => l_calendar_code);
2535
2536 IF x_act_tab (j).plan_start_date IS NULL THEN
2537 RAISE error_cont_period;
2538 END IF;
2539 ELSE
2540 x_act_tab (j).plan_start_date :=
2541 x_step_row.plan_start_date
2542 + x_act_tab (j).offset_interval / 24;
2543 END IF;
2544
2545 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2546 gme_debug.put_line ('offset' || x_act_tab (j).offset_interval);
2547 gme_debug.put_line ( 'x_act_tab(j).plan_start_date '
2548 || TO_CHAR (x_act_tab (j).plan_start_date
2549 ,'DD-MON-YYYY HH24:MI:SS') );
2550 END IF;
2551
2552 -- Retrieve all resources for this activity
2553 x_rsrc_tab.DELETE;
2554 x_cur_row := 0;
2555
2556 OPEN cur_get_act_rsrcs (x_act_tab (j).batchstep_activity_id);
2557
2558 FETCH cur_get_act_rsrcs
2559 INTO x_rsrc_row;
2560
2561 WHILE cur_get_act_rsrcs%FOUND LOOP
2562 x_cur_row := x_cur_row + 1;
2563 x_rsrc_tab (x_cur_row) := x_rsrc_row;
2564
2565 FETCH cur_get_act_rsrcs
2566 INTO x_rsrc_row;
2567 END LOOP;
2568
2569 CLOSE cur_get_act_rsrcs;
2570
2571 IF x_cur_row = 0 THEN
2572 l_batchstep_activity_id := x_act_tab (j).batchstep_activity_id;
2573 RAISE no_resources;
2574 END IF;
2575
2576 IF p_gme_batch_header_rec.update_inventory_ind = 'Y' THEN
2577 x_rsrc_txns_tab.DELETE;
2578 x_cur_row := 0;
2579 END IF;
2580 /* IF p_gme_batch_header_rec.update_inventory_ind = 'Y' THEN */
2581
2582 FOR k IN x_rsrc_tab.FIRST .. x_rsrc_tab.LAST LOOP
2583 IF l_use_workday_cal = fnd_api.g_true THEN
2584 x_rsrc_tab (k).plan_start_date :=
2585 get_working_start_time
2586 (p_start_date => x_act_tab (j).plan_start_date
2587 ,p_offset => x_rsrc_tab (k).offset_interval
2588 ,p_calendar_code => l_calendar_code);
2589
2590 IF x_rsrc_tab (k).plan_start_date IS NULL THEN
2591 RAISE error_cont_period;
2592 END IF;
2593
2594 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2595 gme_debug.put_line
2596 ( 'rsrc_start_date '
2597 || TO_CHAR
2598 (x_rsrc_tab (k).plan_start_date
2599 ,'DD-MON-YYYY HH24:MI:SS') );
2600 END IF;
2601 ELSE
2602 x_rsrc_tab (k).plan_start_date :=
2603 x_act_tab (j).plan_start_date
2604 + x_rsrc_tab (k).offset_interval / 24;
2605 END IF;
2606
2607 get_usage_in_hours (x_rsrc_tab (k).plan_rsrc_usage
2608 ,x_rsrc_tab (k).usage_um
2609 ,l_usage_hrs
2610 ,l_return_status);
2611 l_duration := l_usage_hrs / x_rsrc_tab (k).plan_rsrc_count;
2612
2613 IF l_use_workday_cal = fnd_api.g_true
2614 AND l_calendar_code IS NOT NULL THEN
2615 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2616 gme_debug.put_line ('calling get_contiguous_periods from point 5 calc_dates'); -- Bug 13582990
2617 gme_debug.put_line ('start date passed in is '||
2618 TO_CHAR(x_rsrc_tab(k).plan_start_date, 'DD-MON-YYYY HH24:MI:SS')); -- Bug 13582990
2619 gme_debug.put_line ('duration passed in is '||l_duration); -- Bug 13582990
2620 gme_debug.put_line ('p_calendar_code passed in is '||l_calendar_code); -- Bug 13582990
2621 END IF;
2622
2623 gmp_calendar_api.get_contiguous_periods
2624 (p_api_version => 1
2625 ,p_init_msg_list => TRUE
2626 ,p_start_date => x_rsrc_tab (k).plan_start_date
2627 ,p_end_date => NULL
2628 ,p_calendar_code => l_calendar_code
2629 ,p_duration => l_duration
2630 ,p_output_tbl => l_contig_period_tbl
2631 ,x_return_status => l_return_status);
2632
2633 IF (l_return_status <> p_return_status) THEN
2634 RAISE error_cont_period;
2635 END IF;
2636 ELSE
2637 l_contig_period_tbl (1).start_date :=
2638 x_rsrc_tab (k).plan_start_date;
2639 l_contig_period_tbl (1).end_date :=
2640 x_rsrc_tab (k).plan_start_date + l_duration / 24;
2641 END IF;
2642
2643 l_cal_count := l_contig_period_tbl.COUNT;
2644
2645 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2646 gme_debug.put_line ('rows returned from get_contiguous_periods is '||l_cal_count); -- Bug 13582990
2647 END IF;
2648
2649 x_rsrc_tab (k).plan_cmplt_date :=
2650 l_contig_period_tbl (l_cal_count).end_date;
2651
2652 IF p_gme_batch_header_rec.update_inventory_ind = 'Y' THEN
2653 FOR m IN 1 .. l_cal_count LOOP
2654
2655 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2656 gme_debug.put_line ('rsrc loop iteration is '||m); -- Bug 13582990
2657 gme_debug.put_line ('l_contig_period_tbl(m).start_date is '||
2658 TO_CHAR(l_contig_period_tbl(m).start_date, 'DD-MON-YYYY HH24:MI:SS')); -- Bug 13582990
2659
2660 gme_debug.put_line ('l_contig_period_tbl(m).end_date is '||
2661 TO_CHAR(l_contig_period_tbl(m).end_date, 'DD-MON-YYYY HH24:MI:SS')); -- Bug 13582990
2662 END IF;
2663
2664 x_rsrc_tab (k).plan_start_date :=
2665 l_contig_period_tbl (m).start_date;
2666 x_rsrc_tab (k).plan_cmplt_date :=
2667 l_contig_period_tbl (m).end_date;
2668
2669 -- Bug 9212573 - Now pass parameter p_trans_count. This will tell
2670 -- underlying routine if there is only one transaction required and
2671 -- therefore it can derive the txn usage with a simpler algorithm.
2672 insert_resource_txns (p_gme_batch_header_rec => p_gme_batch_header_rec
2673 ,p_doc_type => l_doc_type
2674 ,p_batch_step_resources_rec => x_rsrc_tab (k)
2675 ,p_trans_count => l_cal_count
2676 ,x_return_status => l_return_status);
2677 /* insert_resource_txns (p_gme_batch_header_rec
2678 ,l_doc_type
2679 ,x_rsrc_tab (k)
2680 ,l_return_status); */
2681
2682 IF (l_return_status <> p_return_status) THEN
2683 RAISE error_insert_res_txns;
2684 END IF;
2685 END LOOP;
2686
2687 x_rsrc_tab (k).plan_start_date :=
2688 l_contig_period_tbl (1).start_date;
2689 END IF;
2690
2691 /* IF p_gme_batch_header_rec.update_inventory_ind = 'Y' THEN */
2692 IF k = x_rsrc_tab.FIRST
2693 OR x_rsrc_tab (k).plan_cmplt_date > x_max_rsrc_date THEN
2694 x_max_rsrc_date := x_rsrc_tab (k).plan_cmplt_date;
2695 END IF;
2696 END LOOP; /* FOR resources */
2697
2698 x_act_tab (j).plan_cmplt_date := x_max_rsrc_date;
2699
2700 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2701 gme_debug.put_line ( '2 '
2702 || TO_CHAR (x_act_tab (j).plan_cmplt_date
2703 ,'DD-MON-YYYY HH24:MI:SS') );
2704 END IF;
2705
2706 IF j = x_act_tab.FIRST
2707 OR x_act_tab (j).plan_cmplt_date > x_max_act_date THEN
2708 x_max_act_date := x_act_tab (j).plan_cmplt_date;
2709 END IF;
2710
2711 FOR k IN x_rsrc_tab.FIRST .. x_rsrc_tab.LAST LOOP
2712 UPDATE gme_batch_step_resources
2713 SET plan_start_date = x_rsrc_tab (k).plan_start_date
2714 ,plan_cmplt_date = x_rsrc_tab (k).plan_cmplt_date
2715 WHERE batchstep_resource_id =
2716 x_rsrc_tab (k).batchstep_resource_id;
2717 END LOOP;
2718 END LOOP; /* FOR j IN X_act_tab.FIRST..X_act_tab.LAST */
2719
2720 x_step_row.plan_cmplt_date := x_max_act_date;
2721 x_step_row.due_date := x_step_row.plan_cmplt_date;
2722
2723 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2724 gme_debug.put_line ( '1 '
2725 || TO_CHAR (x_step_row.plan_cmplt_date
2726 ,'DD-MON-YYYY HH24:MI:SS') );
2727 gme_debug.put_line ( 'due_date = '
2728 || TO_CHAR (x_step_row.due_date
2729 ,'DD-MON-YYYY HH24:MI:SS') );
2730 END IF;
2731
2732 FOR k IN x_act_tab.FIRST .. x_act_tab.LAST LOOP
2733 UPDATE gme_batch_step_activities
2734 SET plan_start_date = x_act_tab (k).plan_start_date
2735 ,plan_cmplt_date = x_act_tab (k).plan_cmplt_date
2736 WHERE batchstep_activity_id = x_act_tab (k).batchstep_activity_id;
2737 END LOOP;
2738
2739 UPDATE gme_batch_steps
2740 SET plan_start_date = x_step_row.plan_start_date
2741 ,plan_cmplt_date = x_step_row.plan_cmplt_date
2742 ,due_date = x_step_row.due_date
2743 WHERE batchstep_id = x_step_row.batchstep_id;
2744 END LOOP; /*FOR i IN X_step_tbl.FIRST..X_step_tbl.LAST */
2745
2746 -- Update the batch header dates
2747 IF (p_step_id IS NULL) THEN
2748 -- not a single step insert... however, that should also be considered for updating the header dates...
2749 SELECT MAX (plan_cmplt_date)
2750 INTO max_cmplt_date
2751 FROM gme_batch_steps
2752 WHERE batch_id = l_batch_id;
2753
2754 SELECT MIN (plan_start_date)
2755 INTO min_start_date
2756 FROM gme_batch_steps
2757 WHERE batch_id = l_batch_id;
2758
2759 IF p_gme_batch_header_rec.batch_status = 2 THEN
2760 -- Pawan Kumar made following changes for bug 5015873
2761 -- min_start_date := p_gme_batch_header_rec.plan_start_date;
2762 UPDATE gme_batch_header
2763 SET plan_cmplt_date = max_cmplt_date
2764 ,last_updated_by = gme_common_pvt.g_user_ident
2765 ,last_update_date = gme_common_pvt.g_timestamp
2766 ,last_update_login = gme_common_pvt.g_login_id
2767 WHERE batch_id = l_batch_id;
2768 ELSE
2769 UPDATE gme_batch_header
2770 SET plan_start_date = min_start_date
2771 ,plan_cmplt_date = max_cmplt_date
2772 ,last_updated_by = gme_common_pvt.g_user_ident
2773 ,last_update_date = gme_common_pvt.g_timestamp
2774 ,last_update_login = gme_common_pvt.g_login_id
2775 WHERE batch_id = l_batch_id;
2776 END IF;
2777
2778 /* UPDATE gme_batch_header
2779 SET plan_start_date = min_start_date
2780 ,plan_cmplt_date = max_cmplt_date
2781 ,last_updated_by = gme_common_pvt.g_user_ident
2782 ,last_update_date = gme_common_pvt.g_timestamp
2783 ,last_update_login = gme_common_pvt.g_login_id
2784 WHERE batch_id = l_batch_id;*/
2785
2786 UPDATE gme_batch_header
2787 SET due_date = max_cmplt_date
2788 ,last_updated_by = gme_common_pvt.g_user_ident
2789 ,last_update_date = gme_common_pvt.g_timestamp
2790 ,last_update_login = gme_common_pvt.g_login_id
2791 WHERE batch_id = l_batch_id
2792 AND due_date = gme_common_pvt.g_timestamp;
2793 END IF;
2794
2795 IF g_debug <= gme_debug.g_log_procedure THEN
2796 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
2797 END IF;
2798 EXCEPTION
2799 WHEN load_steps_failed THEN
2800 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2801 gme_debug.put_line ('calc_dates... load_steps_failed');
2802 END IF;
2803
2804 p_return_status := fnd_api.g_ret_sts_error;
2805 WHEN error_cont_period THEN
2806 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2807 gme_debug.put_line ('Contiguity period ... _failed');
2808 END IF;
2809
2810 p_return_status := l_return_status;
2811 WHEN error_non_contiguious THEN
2812 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2813 gme_debug.put_line ('Contiguity period ... not found');
2814 END IF;
2815
2816 gme_common_pvt.log_message ('GME_NON_CONTIGUOUS_TIME');
2817 p_return_status := 'C';
2818 WHEN no_activities THEN
2819 p_return_status := fnd_api.g_ret_sts_error;
2820
2821 OPEN cur_get_step (l_batchstep_id);
2822
2823 FETCH cur_get_step
2824 INTO x_step_no;
2825
2826 CLOSE cur_get_step;
2827
2828 gme_common_pvt.log_message ('GME_NO_ACTIVITIES', 'STEPNO'
2829 ,x_step_no);
2830 WHEN no_resources THEN
2831 p_return_status := fnd_api.g_ret_sts_error;
2832
2833 OPEN cur_get_activity (l_batchstep_activity_id);
2834
2835 FETCH cur_get_activity
2836 INTO l_activity;
2837
2838 CLOSE cur_get_activity;
2839
2840 gme_common_pvt.log_message ('GME_NO_RESOURCES'
2841 ,'ACTIVITY'
2842 ,l_activity);
2843 WHEN OTHERS THEN
2844 IF g_debug <= gme_debug.g_log_unexpected THEN
2845 gme_debug.put_line ( 'When others exception in '
2846 || g_pkg_name
2847 || '.'
2848 || l_api_name
2849 || ' Error is '
2850 || SQLERRM);
2851 END IF;
2852
2853 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2854 p_return_status := fnd_api.g_ret_sts_unexp_error;
2855 END calc_dates;
2856
2857 /*======================================================================
2858 -- PROCEDURE :
2859 -- calc_longest_time_orig
2860 --
2861 -- DESCRIPTION:
2862 -- This PL/SQL procedure is responsible for calculating the amount of time
2863 -- the batch will take based on the steps. You must calculate the longest
2864 -- path by taking every branch of the step dep tree and calculating how long
2865 -- each will take. The path with the largest time is the path that will determine
2866 -- the length of time that the batch will take.
2867 --
2868 -- REQUIREMENTS
2869 -- l_batch_id non null value.
2870 -- l_step_duration_tab non null value.
2871 -- HISTORY
2872 -- Chandrashekar Tiruvidula 20-Dec-2006 Rewrote procedure Bug 5716727
2873
2874 -- G. Muratore 24-Nov-2008 Bug 7341534
2875 -- Frontport of 6774660/5618732 Reinstate rewritten 11i function.
2876 -- Renamed this r 12 function with "_orig". Hierarchical query does not handle data
2877 -- where there are no step dependencies. If that is figured out someday, this code
2878 -- could be reinstated.
2879 --================================================================================= */
2880 PROCEDURE calc_longest_time_orig(l_batch_id IN gme_batch_header.batch_id%TYPE,
2881 l_step_duration_tab IN step_duration_tab,
2882 x_batch_duration OUT NOCOPY NUMBER,
2883 x_return_status OUT NOCOPY VARCHAR2) IS
2884
2885 CURSOR cur_get_leaf_nodes (v_batch_id NUMBER) IS
2886 SELECT batchstep_id, batchstep_no
2887 FROM gme_batch_steps
2888 WHERE batch_id = v_batch_id
2889 AND batchstep_id NOT IN (SELECT dep_step_id
2890 FROM gme_batch_step_dependencies
2891 WHERE batch_id = v_batch_id);
2892 TYPE leaf_node_fields IS RECORD(leaf_batchstep_id NUMBER,
2893 leaf_batchstep_no NUMBER);
2894 TYPE leaf_nodes_tab IS TABLE OF leaf_node_fields INDEX BY BINARY_INTEGER;
2895 l_leaf_nodes_tbl leaf_nodes_tab;
2896 /* Each record is built up of 14 character strings like this 00020000100001 by the query below
2897 Characters 1-5 is step_no 00020
2898 Characters 6-10 is dep_step_no 00010
2899 Character 11 is dep_type 0
2900 Characters 12-14 is standard delay 001
2901 If there is a dependency like 30 -> 20 -> 10 then the output would be 0003000020000100020000100001
2902 If there are multiple branches then we will get multiple records like the above */
2903 CURSOR Cur_get_branches(v_batch_id NUMBER, v_batchstep_id NUMBER) IS
2904 SELECT branch, LENGTH(branch) sz
2905 FROM (SELECT REPLACE(sys_connect_by_path(LPAD(s.batchstep_no, 5, 0)||LPAD(p.batchstep_no, 5, 0 )||dep_type||LPAD(standard_delay, 3,0),' '), ' ', NULL) branch
2906 FROM (SELECT * FROM gme_batch_step_dependencies WHERE batch_id = v_batch_id) d,
2907 (SELECT * FROM gme_batch_steps WHERE batch_id = v_batch_id) s,
2908 (SELECT * FROM gme_batch_steps WHERE batch_id = v_batch_id) p
2909 WHERE s.batchstep_id = d.batchstep_id
2910 AND p.batchstep_id(+) = d.dep_step_id
2911 AND connect_by_isleaf = 1
2912 START WITH d.batchstep_id = v_batchstep_id
2913 CONNECT BY d.batchstep_id = PRIOR d.dep_step_id) x;
2914 TYPE branches_tab IS TABLE OF Cur_get_branches%ROWTYPE INDEX BY BINARY_INTEGER;
2915 l_branches_tbl branches_tab;
2916 l_api_name CONSTANT VARCHAR2 (30) := 'calc_longest_time';
2917 m NUMBER;
2918 n NUMBER;
2919 l_duration NUMBER := 0;
2920 l_step_no NUMBER;
2921 l_dep_step_no NUMBER;
2922 l_dep_type NUMBER;
2923 l_standard_delay NUMBER;
2924 BEGIN
2925 IF g_debug <= gme_debug.g_log_procedure THEN
2926 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'|| l_api_name);
2927 END IF;
2928 x_return_status := fnd_api.g_ret_sts_success;
2929 x_batch_duration := 0;
2930 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2931 gme_debug.put_line (g_pkg_name || '.'|| l_api_name||' calculate duration');
2932 END IF;
2933 OPEN Cur_get_leaf_nodes (l_batch_id);
2934 FETCH Cur_get_leaf_nodes BULK COLLECT INTO l_leaf_nodes_tbl;
2935 CLOSE Cur_get_leaf_nodes;
2936 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2937 gme_debug.put_line (g_pkg_name || '.'|| l_api_name||' number of leaf nodes: ' || l_leaf_nodes_tbl.COUNT);
2938 END IF;
2939 FOR i IN 1..l_leaf_nodes_tbl.COUNT LOOP
2940 OPEN Cur_get_branches(l_batch_id, l_leaf_nodes_tbl(i).leaf_batchstep_id);
2941 FETCH Cur_get_branches BULK COLLECT INTO l_branches_tbl;
2942 CLOSE Cur_get_branches;
2943 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2944 gme_debug.put_line(g_pkg_name || '.'|| l_api_name||' number of branches: ' || l_branches_tbl.COUNT);
2945 END IF;
2946 FOR j IN 1..l_branches_tbl.COUNT LOOP
2947 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2948 gme_debug.put_line(g_pkg_name || '.'|| l_api_name||' current branch: ' || l_branches_tbl(j).branch);
2949 END IF;
2950 m := 1;
2951 n := 5;
2952 /* Divide by 14 because length of step_no = 5, dep_step_no = 5, dep_type = 1, standard_delay = 3 */
2953 FOR k IN 1..l_branches_tbl(j).sz/14 LOOP --Start parsing string now
2954 l_step_no := SUBSTR(l_branches_tbl(j).branch, m, 5);
2955 m := n + 1;
2956 n := m + 4;
2957 l_dep_step_no := SUBSTR(l_branches_tbl(j).branch, m, 5);
2958 m := n + 1;
2959 n := m + 0;
2960 l_dep_type := SUBSTR(l_branches_tbl(j).branch, m, 1);
2961 m := n + 1;
2962 n := m + 2;
2963 l_standard_delay := SUBSTR(l_branches_tbl(j).branch, m, 3);
2964 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2965 gme_debug.put_line(g_pkg_name || '.'|| l_api_name||' Step->Depstep->Deptype->Delay = '||l_step_no||'->'||l_dep_step_no||'->'||l_dep_type||'->'||l_standard_delay);
2966 END IF;
2967 IF (k = 1) THEN
2968 l_duration := l_step_duration_tab(l_step_no);
2969 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2970 gme_debug.put_line(g_pkg_name || '.'|| l_api_name||' step->duration: ' || l_step_no||'->'||l_duration);
2971 END IF;
2972 END IF;
2973 l_duration := l_duration + l_standard_delay;
2974 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2975 gme_debug.put_line(g_pkg_name || '.'|| l_api_name||' depstep->duration: ' || l_dep_step_no||'->'||l_duration);
2976 END IF;
2977 IF (l_dep_type = 1) THEN -- Start to Start
2978 IF l_step_duration_tab(l_dep_step_no) > l_duration THEN
2979 l_duration := l_step_duration_tab(l_dep_step_no);
2980 END IF;
2981 ELSE
2982 l_duration := l_duration + l_step_duration_tab(l_dep_step_no);
2983 END IF;
2984 m := n + 1;
2985 n := m + 4;
2986 END LOOP; -- parsing loop
2987 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2988 gme_debug.put_line(g_pkg_name || '.'|| l_api_name||' duration of branch: ' || l_duration);
2989 END IF;
2990 IF l_duration > x_batch_duration THEN
2991 x_batch_duration := l_duration;
2992 END IF;
2993 l_duration := 0;
2994 END LOOP; -- branches loop
2995 END LOOP; -- leaf nodes loop
2996 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2997 gme_debug.put_line(g_pkg_name || '.'|| l_api_name||' duration of batch: ' || x_batch_duration);
2998 END IF;
2999 EXCEPTION
3000 WHEN OTHERS THEN
3001 IF g_debug <= gme_debug.g_log_unexpected THEN
3002 gme_debug.put_line('When others exception in '|| g_pkg_name|| '.'|| l_api_name|| ' Error is '|| SQLERRM);
3003 END IF;
3004 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
3005 x_return_status := fnd_api.g_ret_sts_unexp_error;
3006 END calc_longest_time_orig;
3007
3008 /*======================================================================
3009 -- PROCEDURE :
3010 -- calc_longest_time
3011 --
3012 -- DESCRIPTION:
3013 -- This PL/SQL procedure is responsible for calculating the amount of time
3014 -- the batch will take based on the steps. You must calculate the longest
3015 -- path by taking every branch of the step dep tree and calculating how long
3016 -- each will take. The path with the largest time is the path that will determine
3017 -- the length of time that the batch will take.
3018 --
3019 -- REQUIREMENTS
3020 -- l_batch_id non null value.
3021 -- l_step_duration_tab non null value.
3022 -- HISTORY
3023 -- G. Muratore 24-Nov-2008 Bug 7341534
3024 -- Frontport of 6774660/5618732 Reinstate rewritten 11i function.
3025 -- Renamed this r 12 function with "_orig". Hierarchical query does not handle data
3026 -- where there are no step dependencies.
3027 --================================================================================= */
3028 PROCEDURE calc_longest_time (
3029 l_batch_id IN gme_batch_header.batch_id%TYPE,
3030 l_step_duration_tab IN step_duration_tab,
3031 x_batch_duration OUT NOCOPY NUMBER,
3032 x_return_status OUT NOCOPY VARCHAR2
3033 ) IS
3034 l_step_no gme_batch_steps.batchstep_no%TYPE;
3035 l_dep_step_no gme_batch_steps.batchstep_no%TYPE;
3036 l_leaf_nodes step_tab;
3037
3038 l_count NUMBER := 0;
3039 i PLS_INTEGER;
3040 j PLS_INTEGER;
3041 --Bug#5077094
3042 branch PLS_INTEGER := 1;
3043 Currec PLS_INTEGER;
3044 reccount PLS_INTEGER;
3045 CurrecInitVal PLS_INTEGER;
3046 l_branch_batchstep_id step_tab;
3047 l_branch_dep_step_id step_tab;
3048 l_branch_dep_type step_tab;
3049 l_branch_batchstep_no step_tab;
3050 l_branch_dep_step_no step_tab;
3051 l_branch_standard_delay step_tab;
3052 l_api_name CONSTANT VARCHAR2 (30) := 'Calc longest time';
3053
3054 CURSOR cur_get_leaf_nodes (v_batch_id NUMBER) IS
3055 SELECT batchstep_id
3056 FROM gme_batch_steps
3057 WHERE batch_id = v_batch_id AND
3058 batchstep_id NOT IN (SELECT dep_step_id
3059 FROM gme_batch_step_dependencies
3060 WHERE batch_id = v_batch_id);
3061 CURSOR cur_get_step_no (v_step_id gme_batch_steps.batchstep_id%TYPE) IS
3062 SELECT batchstep_no
3063 FROM gme_batch_steps
3064 WHERE batchstep_id = v_step_id;
3065
3066 l_brch_leaf_cnt NUMBER := 0;
3067 l_leaf_nodeid NUMBER := 0;
3068
3069 BEGIN
3070 x_return_status := FND_API.G_RET_STS_SUCCESS;
3071 x_batch_duration := 0;
3072 i := 1;
3073
3074 IF (NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT) THEN
3075 gme_debug.put_line ('calculate duration');
3076 END IF;
3077
3078 OPEN cur_get_leaf_nodes (l_batch_id);
3079 FETCH cur_get_leaf_nodes BULK COLLECT INTO l_leaf_nodes;
3080 CLOSE cur_get_leaf_nodes;
3081
3082 IF (NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT) THEN
3083 gme_debug.put_line ('number of leaf nodes: ' || l_leaf_nodes.COUNT);
3084 END IF;
3085
3086 FOR i IN l_leaf_nodes.FIRST .. l_leaf_nodes.LAST
3087 LOOP
3088 l_count := 0;
3089
3090 l_count := get_longest_in_branch(l_leaf_nodes (i),l_step_duration_tab);
3091 IF (l_count > x_batch_duration) THEN
3092 x_batch_duration := l_count;
3093 END IF;
3094 END LOOP; /* FOR leaf_nodes */
3095
3096 IF (NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT) THEN
3097 gme_debug.put_line ('X_batch_duration = ' || x_batch_duration);
3098 END IF;
3099 EXCEPTION
3100 WHEN OTHERS THEN
3101 -- fnd_msg_pub.add_exc_msg (l_package_name, l_api_name);
3102
3103 IF (NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT) THEN
3104 gme_debug.put_line ('ERROR - when others - sqlerrm = ' || SQLERRM);
3105 END IF;
3106
3107 x_return_status := FND_API.g_ret_sts_unexp_error;
3108 END calc_longest_time;
3109
3110 /*======================================================================
3111 -- FUNCTION :
3112 -- get_longest_in_branch
3113 --
3114 -- DESCRIPTION:
3115 -- This PL/SQL procedure is responsible for calculating the maximum amount
3116 -- of duration of a branch in the step dependency tree structure.
3117 -- The logic is to pass the leaf node and calculate the the maximum step
3118 -- duration out of it's subnodes and we continue to follow this recursively.
3119 -- for the last node having no subnodes( that is having no dependencies)
3120 -- we take the step duration of that node as such and this is the termination
3121 -- conidtion for the recursion.
3122 --
3123 -- REQUIREMENTS
3124 -- node non null value (batch step id).
3125 -- l_step_duration_tab non null value.
3126 -- HISTORY
3127 -- G. Muratore 24-Nov-2008 Bug 7341534
3128 -- Frontport of 6774660/5618732 Reinstate rewritten 11i function.
3129 -- Added this new procedure.
3130
3131 -- G. Muratore 09-Jul-2010 Bug 9862326
3132 -- Account for all dependent delays when deriving duration of
3133 -- a given step when using start to start dependency.
3134
3135 -- G. Muratore 13-Dec-2010 Bug 10226452
3136 -- Account for all dependent delays when deriving duration of
3137 -- a given step when using start to start dependency.
3138 -- The 9862326 fix was not complete and the additional changes are
3139 -- made here via the additional delay string loop.
3140
3141 -- G. Muratore 01-Mar-2011 Bug 11818852
3142 -- Increase variable size introduced in 10226452.
3143 --===================================================================== */
3144 FUNCTION get_longest_in_branch(
3145 node IN NUMBER
3146 ,l_step_duration_tab IN step_duration_tab
3147
3148 ) RETURN NUMBER IS
3149 -- l_step_duration_tab step_duration_tab;
3150 StepDuration NUMBER := 0;
3151 long_in_branch NUMBER := 0;
3152 longi NUMBER;
3153 x NUMBER;
3154 leaf_subnode_stepno NUMBER;
3155 subnodes_found NUMBER := 0;
3156
3157 -- Bug 9862326
3158 l_start_string NUMBER;
3159 l_end_string NUMBER;
3160 l_standard_delay NUMBER;
3161 l_standard_delay_string VARCHAR2(4000);
3162
3163 -- Bug 10226452
3164 l_standard_delay_hold NUMBER;
3165 l_dep_type NUMBER;
3166 l_batchstep_no NUMBER;
3167 l_dep_step_id NUMBER;
3168
3169 -- Bug 9862326 - This cursor will fetch all delays related to a given step including dependent steps.
3170 CURSOR cur_get_step_delay (v_batch_id NUMBER, v_batchstep_id NUMBER) IS
3171 SELECT REPLACE(sys_connect_by_path(dep_type||';'||d.dep_step_id||';'||standard_delay||';',' '), ' ', NULL) branch
3172 FROM (SELECT * FROM gme_batch_step_dependencies WHERE batch_id = v_batch_id) d,
3173 (SELECT * FROM gme_batch_steps WHERE batch_id = v_batch_id) s,
3174 (SELECT * FROM gme_batch_steps WHERE batch_id = v_batch_id) p
3175 WHERE s.batchstep_id = d.batchstep_id
3176 AND p.batchstep_id(+) = d.dep_step_id
3177 AND connect_by_isleaf = 1
3178 START WITH d.batchstep_id = v_batchstep_id
3179 CONNECT BY d.batchstep_id = PRIOR d.dep_step_id;
3180
3181 -- Bug 10226452
3182 CURSOR cur_get_step_no (v_batch_id NUMBER, v_batchstep_id NUMBER) IS
3183 SELECT batchstep_no
3184 FROM gme_batch_steps
3185 WHERE batchstep_id = v_batchstep_id;
3186 BEGIN
3187
3188 IF (NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT) THEN
3189 gme_debug.put_line ('entering get_longest_in_branch with batchstep_id '||node);
3190 END IF;
3191
3192 for curnode in (select d.dep_step_id, dep_type, standard_delay, s.batchstep_no, s.batch_id
3193 from gme_batch_step_dependencies d, gme_batch_steps s
3194 where s.batchstep_id = d.batchstep_id and d.batchstep_id = node)
3195 loop
3196 /* initialize step duration */
3197 subnodes_found := 1;
3198 IF (NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT) THEN
3199 gme_debug.put_line ('entering the loop');
3200 gme_debug.put_line ('batchstep_no is '||curnode.batchstep_no);
3201 gme_debug.put_line ('batch_id is '||curnode.batch_id);
3202 gme_debug.put_line ('duration is '||l_step_duration_tab (curnode.batchstep_no));
3203 gme_debug.put_line ('delay is '||curnode.standard_delay);
3204 gme_debug.put_line ('dep_type is '||curnode.dep_type);
3205 END IF;
3206
3207 -- This calculation account for duration and delay of the current step.
3208 StepDuration := l_step_duration_tab (curnode.batchstep_no) + curnode.standard_delay;
3209
3210 IF (curnode.dep_type = 1) THEN -- Start to Start
3211 IF (NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT) THEN
3212 gme_debug.put_line ('start to start...');
3213 gme_debug.put_line ('get all dependent delays in a string');
3214 END IF;
3215
3216 -- Bug 9862326 - Must account for delay of dependent steps due to inheritance.
3217 OPEN cur_get_step_delay(curnode.batch_id, curnode.dep_step_id);
3218 FETCH cur_get_step_delay INTO l_standard_delay_string;
3219
3220 -- Bug 10226452 - Restructure this code to loop thru all branches fetched.
3221 l_standard_delay_hold := 0;
3222
3223 WHILE cur_get_step_delay%FOUND LOOP
3224 IF (NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT) THEN
3225 gme_debug.put_line ('delay string is '||l_standard_delay_string);
3226 END IF;
3227
3228 -- Sum up all dependent delays for the current step by extrapolating values from string.
3229 l_standard_delay := 0;
3230 l_start_string := 1;
3231 WHILE l_start_string <= LENGTH(l_standard_delay_string) LOOP
3232 -- Get the dep type
3233 l_end_string := instr(l_standard_delay_string, ';', l_start_string);
3234 l_dep_type := SUBSTR(l_standard_delay_string, l_start_string, l_end_string - l_start_string);
3235 l_start_string := l_end_string + 1;
3236
3237 -- Get the batch step no
3238 l_end_string := instr(l_standard_delay_string, ';', l_start_string);
3239 l_dep_step_id := SUBSTR(l_standard_delay_string, l_start_string, l_end_string - l_start_string);
3240 l_start_string := l_end_string + 1;
3241
3242 -- Get the standard delay and add it in. This value is always added regardless of dep type.
3243 l_end_string := instr(l_standard_delay_string, ';', l_start_string);
3244 l_standard_delay := l_standard_delay + SUBSTR(l_standard_delay_string, l_start_string, l_end_string - l_start_string);
3245 l_start_string := l_end_string + 1;
3246
3247 -- If it's a finish to start we need to add in duration of the dependent step also.
3248 IF (l_dep_type = 0) THEN
3249 -- We need to get the batch step no because the table is indexed by step no.
3250 OPEN cur_get_step_no(curnode.batch_id, l_dep_step_id);
3251 FETCH cur_get_step_no INTO l_batchstep_no;
3252 CLOSE cur_get_step_no;
3253
3254 l_standard_delay := l_standard_delay + l_step_duration_tab (l_batchstep_no);
3255 END IF;
3256
3257 END LOOP; /* WHILE l_start_string IS NOT NULL */
3258
3259 IF (NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT) THEN
3260 gme_debug.put_line ('aggregate dependent delay is '||l_standard_delay);
3261 gme_debug.put_line ('HOLD delay is '||l_standard_delay_hold);
3262 END IF;
3263
3264 IF l_standard_delay > l_standard_delay_hold THEN
3265 l_standard_delay_hold := l_standard_delay;
3266 END IF;
3267
3268 FETCH cur_get_step_delay INTO l_standard_delay_string;
3269 END LOOP; /* WHILE l_start_string IS NOT NULL */
3270
3271 CLOSE cur_get_step_delay;
3272
3273 StepDuration := StepDuration + l_standard_delay_hold;
3274
3275 x := get_longest_in_branch(curnode.dep_step_id,l_step_duration_tab);
3276 IF (x > StepDuration) THEN
3277 longi := x;
3278 ELSE
3279 longi := StepDuration;
3280 END IF;
3281
3282 IF (NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT) THEN
3283 gme_debug.put_line (
3284 'step duration replaced with dep step duration... '
3285 || longi
3286 );
3287 END IF;
3288
3289 IF (NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT) THEN
3290 gme_debug.put_line ('done start to start...');
3291 END IF;
3292 ELSE -- dep_type = 0... thatn's an end to start, so add this in...
3293 IF (NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT) THEN
3294 gme_debug.put_line ('end to start...');
3295 END IF;
3296
3297 longi := StepDuration + get_longest_in_branch(curnode.dep_step_id,l_step_duration_tab);
3298 IF (NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT) THEN
3299 gme_debug.put_line (
3300 'added duration of dep step and the resulted is'||longi);
3301 END IF;
3302 END IF;
3303 if ( longi > long_in_branch) then
3304 long_in_branch := longi;
3305 end if;
3306 end loop;
3307 IF subnodes_found = 0 THEN
3308 select batchstep_no into leaf_subnode_stepno
3309 from gme_batch_steps
3310 where batchstep_id = node;
3311 long_in_branch := l_step_duration_tab(leaf_subnode_stepno);
3312 END IF;
3313 IF (NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT) THEN
3314 gme_debug.put_line ('Weight for node'||node||'-->'||long_in_branch);
3315 END IF;
3316 return long_in_branch;
3317 END get_longest_in_branch;
3318
3319 /*======================================================================
3320 -- PROCEDURE :
3321 -- calc_step_qty
3322 --
3323 -- DESCRIPTION:
3324 -- This PL/SQL procedure is responsible for scaling step
3325 -- quantities.
3326 -- This procedure is a modification of GMD_AUTO_STEP_CALC.calc_step_qty.
3327 -- The GMD_AUTO_STEP_CALC package will be used whereever possible for
3328 -- underlying routines so as not to duplicate code unnecessarily.
3329 -- This procedure will keep as close to the original as possible.
3330 -- This will always be called from a batch, but the parm to specify this
3331 -- will be kept just in case...
3332 --
3333 -- REQUIREMENTS
3334 -- p_parent_id non null value.
3335 -- p_step_tbl non null value.
3336 -- SYNOPSIS:
3337 -- calc_step_qty (426, X_step_tbl, X_msg_count, X_msg_stack, X_return_status, 0);
3338 --
3339 --
3340 --===================================================================== */
3341 PROCEDURE calc_step_qty (
3342 p_parent_id IN NUMBER
3343 ,p_step_tbl OUT NOCOPY gmd_auto_step_calc.step_rec_tbl
3344 ,p_return_status OUT NOCOPY VARCHAR2
3345 ,p_called_from_batch IN NUMBER DEFAULT 1)
3346 IS
3347 x_step_rows NUMBER;
3348 x_routing_id gme_batch_header.routing_id%TYPE;
3349 x_step_qty NUMBER;
3350 x_new_factor NUMBER;
3351 x_uom_class mtl_units_of_measure.uom_class%TYPE;
3352 x_scale_factor NUMBER;
3353 x_return_status VARCHAR2 (1);
3354 l_api_name CONSTANT VARCHAR2 (30) := 'Calc step qty';
3355
3356 CURSOR cur_get_std_factor (v_uom_code VARCHAR2)
3357 IS
3358 SELECT a.conversion_rate, b.uom_class
3359 FROM mtl_uom_conversions a, mtl_units_of_measure b
3360 WHERE a.uom_code = b.uom_code
3361 AND a.inventory_item_id = 0
3362 AND b.uom_code = v_uom_code;
3363
3364 CURSOR cur_get_std_um (v_uom_class VARCHAR2)
3365 IS
3366 SELECT uom_code
3367 FROM mtl_units_of_measure
3368 WHERE uom_class = v_uom_class AND base_uom_flag = 'Y';
3369
3370 load_steps_failed EXCEPTION;
3371 error_calculating_scale_factor EXCEPTION;
3372 BEGIN
3373 IF g_debug <= gme_debug.g_log_procedure THEN
3374 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
3375 || l_api_name);
3376 END IF;
3377
3378 p_return_status := fnd_api.g_ret_sts_success;
3379 /* Load the steps based into the PL/SQL table P_step_tbl based on the dependencies */
3380 gmd_auto_step_calc.load_steps (p_parent_id
3381 ,p_called_from_batch
3382 ,NULL
3383 ,p_step_tbl
3384 ,x_routing_id
3385 ,x_return_status);
3386
3387 IF x_return_status <> p_return_status THEN
3388 RAISE load_steps_failed;
3389 END IF;
3390
3391 IF (gme_common_pvt.g_mass_um_type IS NOT NULL) THEN
3392 OPEN cur_get_std_um (gme_common_pvt.g_mass_um_type);
3393
3394 FETCH cur_get_std_um
3395 INTO gmd_auto_step_calc.g_mass_std_um;
3396
3397 CLOSE cur_get_std_um;
3398 END IF;
3399
3400 IF (gme_common_pvt.g_volume_um_type IS NOT NULL) THEN
3401 OPEN cur_get_std_um (gme_common_pvt.g_volume_um_type);
3402
3403 FETCH cur_get_std_um
3404 INTO gmd_auto_step_calc.g_vol_std_um;
3405
3406 CLOSE cur_get_std_um;
3407 END IF;
3408
3409 x_scale_factor := gme_common_pvt.g_routing_scale_factor;
3410 x_step_rows := p_step_tbl.COUNT;
3411
3412 /* Calculate the step quantities for all the rows in X_step_tbl */
3413 FOR i IN 1 .. x_step_rows LOOP
3414 SELECT plan_step_qty
3415 INTO x_step_qty
3416 FROM gme_batch_steps
3417 WHERE batchstep_no = p_step_tbl (i).step_no
3418 AND batch_id = p_parent_id;
3419
3420 x_step_qty := x_step_qty * x_scale_factor;
3421 p_step_tbl (i).step_qty := x_step_qty;
3422
3423 /* Get the std factor and UOM type for the step_qty */
3424 OPEN cur_get_std_factor (p_step_tbl (i).step_qty_uom);
3425
3426 FETCH cur_get_std_factor
3427 INTO x_new_factor, x_uom_class;
3428
3429 CLOSE cur_get_std_factor;
3430
3431 IF x_uom_class = gme_common_pvt.g_mass_um_type THEN
3432 p_step_tbl (i).step_mass_qty := x_step_qty * x_new_factor;
3433 p_step_tbl (i).step_mass_uom := gmd_auto_step_calc.g_mass_std_um;
3434 ELSIF x_uom_class = gme_common_pvt.g_volume_um_type THEN
3435 p_step_tbl (i).step_vol_qty := x_step_qty * x_new_factor;
3436 p_step_tbl (i).step_vol_uom := gmd_auto_step_calc.g_vol_std_um;
3437 END IF;
3438 END LOOP; /*FOR i IN 1..X_step_rows*/
3439
3440 IF g_debug <= gme_debug.g_log_procedure THEN
3441 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
3442 END IF;
3443 EXCEPTION
3444 WHEN load_steps_failed THEN
3445 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
3446 gme_debug.put_line ('GME auto step calc -- load steps failed');
3447 END IF;
3448
3449 p_return_status := fnd_api.g_ret_sts_error;
3450 WHEN error_calculating_scale_factor THEN
3451 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
3452 gme_debug.put_line
3453 ('GME_auto step calc -- error calc scale factor');
3454 END IF;
3455
3456 p_return_status := fnd_api.g_ret_sts_error;
3457 WHEN OTHERS THEN
3458 IF g_debug <= gme_debug.g_log_unexpected THEN
3459 gme_debug.put_line ( 'When others exception in '
3460 || g_pkg_name
3461 || '.'
3462 || l_api_name
3463 || ' Error is '
3464 || SQLERRM);
3465 END IF;
3466
3467 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
3468 x_return_status := fnd_api.g_ret_sts_unexp_error;
3469 END calc_step_qty;
3470
3471 PROCEDURE copy_and_create_text (
3472 p_gmd_text_code IN NUMBER
3473 ,p_text_string IN gme_text_table.text%TYPE
3474 ,x_gme_text_code OUT NOCOPY NUMBER
3475 ,x_return_status OUT NOCOPY VARCHAR2)
3476 IS
3477 l_number_of_text_lines NUMBER;
3478 l_return BOOLEAN;
3479 l_text_header gme_text_header%ROWTYPE;
3480 l_text_table gme_common_pvt.text_tab;
3481 l_api_name CONSTANT VARCHAR2 (30) := 'copy and create text';
3482 unexpected_error EXCEPTION;
3483
3484 CURSOR fm_text_tbl_cursor (p_text_code fm_text_tbl.text_code%TYPE)
3485 IS
3486 SELECT *
3487 FROM fm_text_tbl
3488 WHERE text_code = p_text_code
3489 ORDER BY line_no;
3490 BEGIN
3491 IF g_debug <= gme_debug.g_log_procedure THEN
3492 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
3493 || l_api_name);
3494 END IF;
3495
3496 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
3497 gme_debug.put_line ('BEGIN copy and create text for '
3498 || p_text_string);
3499 END IF;
3500
3501 x_return_status := fnd_api.g_ret_sts_success;
3502 l_text_table.DELETE;
3503 l_number_of_text_lines := 0;
3504 l_return :=
3505 gme_text_dbl.insert_header_row (l_text_header, l_text_header);
3506
3507 IF (l_return) THEN
3508 x_gme_text_code := l_text_header.text_code;
3509
3510 -- This fetches using the fm text data based on the fm text code.
3511 FOR l_fm_text_tbl_row IN fm_text_tbl_cursor (p_gmd_text_code) LOOP
3512 l_number_of_text_lines := l_number_of_text_lines + 1;
3513 l_text_table (l_number_of_text_lines).text_code :=
3514 x_gme_text_code;
3515 l_text_table (l_number_of_text_lines).line_no :=
3516 l_fm_text_tbl_row.line_no;
3517 l_text_table (l_number_of_text_lines).lang_code :=
3518 l_fm_text_tbl_row.lang_code;
3519 l_text_table (l_number_of_text_lines).paragraph_code :=
3520 l_fm_text_tbl_row.paragraph_code;
3521 l_text_table (l_number_of_text_lines).sub_paracode :=
3522 l_fm_text_tbl_row.sub_paracode;
3523
3524 IF (l_fm_text_tbl_row.line_no = -1) THEN
3525 l_text_table (l_number_of_text_lines).text := p_text_string;
3526 ELSE
3527 l_text_table (l_number_of_text_lines).text :=
3528 l_fm_text_tbl_row.text;
3529
3530 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
3531 gme_debug.put_line
3532 ( 'text for line no '
3533 || l_fm_text_tbl_row.line_no
3534 || ' is '
3535 || l_text_table (l_number_of_text_lines).text);
3536 END IF;
3537 END IF;
3538 END LOOP;
3539 /* FOR l_fm_text_tbl_row in fm_text_tbl_cursor(l_text_code) LOOP */
3540 ELSE
3541 -- We could not insert the text header. Error message pushed on stack in dbl code.
3542 RAISE unexpected_error;
3543 END IF;
3544
3545 -- Insert Text
3546 FOR l_row_count IN 1 .. l_text_table.COUNT LOOP
3547 l_return :=
3548 gme_text_dbl.insert_text_row (l_text_table (l_row_count)
3549 ,l_text_table (l_row_count) );
3550
3551 IF NOT l_return THEN
3552 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
3553 gme_debug.put_line ( 'trouble inserting text for '
3554 || p_text_string);
3555 END IF;
3556
3557 -- We could not insert the text info. Error message pushed on stack in dbl code.
3558 RAISE unexpected_error;
3559 END IF;
3560
3561 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
3562 gme_debug.put_line ('inserted text for ' || p_text_string);
3563 END IF;
3564 END LOOP; /* FOR l_row_count IN 1 .. l_text_table.COUNT */
3565
3566 IF g_debug <= gme_debug.g_log_procedure THEN
3567 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
3568 END IF;
3569 EXCEPTION
3570 WHEN unexpected_error THEN
3571 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
3572 gme_debug.put_line
3573 ('ERROR - unexpected error in copy and create text while inserting header or dtl');
3574 END IF;
3575
3576 x_return_status := fnd_api.g_ret_sts_unexp_error;
3577 WHEN OTHERS THEN
3578 IF g_debug <= gme_debug.g_log_unexpected THEN
3579 gme_debug.put_line ( 'When others exception in '
3580 || g_pkg_name
3581 || '.'
3582 || l_api_name
3583 || ' Error is '
3584 || SQLERRM);
3585 END IF;
3586
3587 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
3588 x_return_status := fnd_api.g_ret_sts_unexp_error;
3589 END copy_and_create_text;
3590
3591 PROCEDURE get_usage_in_hours (
3592 p_plan_rsrc_usage IN gme_batch_step_resources.plan_rsrc_usage%TYPE
3593 ,p_usage_um IN gme_batch_step_resources.usage_um%TYPE
3594 ,x_usage_hrs OUT NOCOPY gme_batch_step_resources.plan_rsrc_usage%TYPE
3595 ,x_return_status OUT NOCOPY VARCHAR2)
3596 IS
3597 l_hour_um mtl_units_of_measure.uom_code%TYPE;
3598 l_hour_uom_class mtl_units_of_measure.uom_class%TYPE;
3599 l_hour_std_factor NUMBER;
3600 l_usage_uom_class mtl_units_of_measure.uom_class%TYPE;
3601 l_usage_std_factor NUMBER;
3602 missing_profile_option EXCEPTION;
3603 l_api_name CONSTANT VARCHAR2 (30) := 'GET_USAGE_IN_HOURS';
3604
3605 CURSOR get_um_type_and_factor (v_uom_code VARCHAR2)
3606 IS
3607 SELECT b.uom_class, a.conversion_rate
3608 FROM mtl_uom_conversions a, mtl_units_of_measure b
3609 WHERE a.uom_code = b.uom_code
3610 AND a.inventory_item_id = 0
3611 AND b.uom_code = v_uom_code;
3612 BEGIN
3613 IF g_debug <= gme_debug.g_log_procedure THEN
3614 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
3615 || l_api_name);
3616 END IF;
3617
3618 x_return_status := fnd_api.g_ret_sts_success;
3619
3620 -- Bug 14769220 - Initialize uom global for this code to work correctly.
3621 IF NOT gme_common_pvt.g_setup_done THEN
3622 gme_common_pvt.g_hour_uom_code := fnd_profile.VALUE ('BOM:HOUR_UOM_CODE');
3623 END IF;
3624
3625 x_usage_hrs := 0;
3626 l_hour_um := gme_common_pvt.g_hour_uom_code;
3627
3628 IF l_hour_um = p_usage_um THEN
3629 x_usage_hrs := p_plan_rsrc_usage;
3630 ELSE /* we have to calculate the usage in hours... */
3631 OPEN get_um_type_and_factor (l_hour_um);
3632
3633 FETCH get_um_type_and_factor
3634 INTO l_hour_uom_class, l_hour_std_factor;
3635
3636 IF get_um_type_and_factor%NOTFOUND THEN
3637 x_return_status := fnd_api.g_ret_sts_error;
3638 END IF;
3639
3640 CLOSE get_um_type_and_factor;
3641
3642 IF x_return_status = fnd_api.g_ret_sts_success THEN
3643 OPEN get_um_type_and_factor (p_usage_um);
3644
3645 FETCH get_um_type_and_factor
3646 INTO l_usage_uom_class, l_usage_std_factor;
3647
3648 IF get_um_type_and_factor%NOTFOUND THEN
3649 x_return_status := fnd_api.g_ret_sts_error;
3650 END IF;
3651
3652 CLOSE get_um_type_and_factor;
3653
3654 IF x_return_status = fnd_api.g_ret_sts_success THEN
3655 IF l_usage_uom_class <> l_hour_uom_class THEN
3656 x_return_status := fnd_api.g_ret_sts_error;
3657 END IF;
3658 END IF;
3659 END IF;
3660
3661 IF x_return_status = fnd_api.g_ret_sts_success THEN
3662 x_usage_hrs :=
3663 p_plan_rsrc_usage * l_usage_std_factor / l_hour_std_factor;
3664 END IF;
3665 END IF;
3666
3667 IF g_debug <= gme_debug.g_log_procedure THEN
3668 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
3669 END IF;
3670 EXCEPTION
3671 WHEN OTHERS THEN
3672 IF g_debug <= gme_debug.g_log_unexpected THEN
3673 gme_debug.put_line ( 'When others exception in '
3674 || g_pkg_name
3675 || '.'
3676 || l_api_name
3677 || ' Error is '
3678 || SQLERRM);
3679 END IF;
3680
3681 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
3682 x_return_status := fnd_api.g_ret_sts_unexp_error;
3683 END get_usage_in_hours;
3684
3685 FUNCTION get_max_duration (v_step_id IN NUMBER, v_batch_id IN NUMBER)
3686 RETURN NUMBER
3687 IS
3688 CURSOR cur_get_activity (v_step_id NUMBER, v_batch_id NUMBER)
3689 IS
3690 SELECT batchstep_activity_id, offset_interval, batchstep_id
3691 ,batch_id
3692 FROM gme_batch_step_activities
3693 WHERE batchstep_id = v_step_id AND batch_id = v_batch_id;
3694
3695 CURSOR cur_get_resource (
3696 v_step_id NUMBER
3697 ,v_batch_id NUMBER
3698 ,v_activity_id NUMBER)
3699 IS
3700 SELECT batchstep_resource_id, offset_interval, plan_rsrc_usage
3701 ,plan_rsrc_count, usage_um
3702 FROM gme_batch_step_resources
3703 WHERE batchstep_id = v_step_id
3704 AND batch_id = v_batch_id
3705 AND batchstep_activity_id = v_activity_id;
3706
3707 l_hour_um mtl_units_of_measure.uom_code%TYPE;
3708 l_rsrc_duration NUMBER;
3709 l_act_duration NUMBER;
3710 l_max_rsrc_duration NUMBER;
3711 l_max_act_duration NUMBER;
3712 x_usage_hour gme_batch_step_resources.plan_rsrc_usage%TYPE;
3713 l_usage_hrs gme_batch_step_resources.plan_rsrc_usage%TYPE;
3714 l_return_status VARCHAR2 (1);
3715 missing_profile_option EXCEPTION;
3716 l_api_name CONSTANT VARCHAR2 (30) := 'get_max_duration';
3717 BEGIN
3718 IF g_debug <= gme_debug.g_log_procedure THEN
3719 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
3720 || l_api_name);
3721 END IF;
3722
3723 l_hour_um := gme_common_pvt.g_hour_uom_code;
3724
3725 FOR step_activity IN cur_get_activity (v_step_id, v_batch_id) LOOP
3726 l_max_rsrc_duration := 0;
3727
3728 FOR step_resource IN
3729 cur_get_resource (step_activity.batchstep_id
3730 ,step_activity.batch_id
3731 ,step_activity.batchstep_activity_id) LOOP
3732 IF step_resource.usage_um = l_hour_um THEN
3733 x_usage_hour := step_resource.plan_rsrc_usage;
3734 ELSE
3735 get_usage_in_hours (step_resource.plan_rsrc_usage
3736 ,step_resource.usage_um
3737 ,l_usage_hrs
3738 ,l_return_status);
3739
3740 IF l_return_status = fnd_api.g_ret_sts_success THEN
3741 x_usage_hour := l_usage_hrs;
3742 ELSE
3743 x_usage_hour := 0;
3744 END IF;
3745 END IF;
3746
3747 l_rsrc_duration :=
3748 step_resource.offset_interval
3749 + (x_usage_hour / step_resource.plan_rsrc_count);
3750
3751 IF l_rsrc_duration > NVL (l_max_rsrc_duration, -1) THEN
3752 l_max_rsrc_duration := l_rsrc_duration;
3753 END IF;
3754 END LOOP;
3755
3756 l_act_duration := step_activity.offset_interval + l_max_rsrc_duration;
3757
3758 IF l_act_duration > NVL (l_max_act_duration, -1) THEN
3759 l_max_act_duration := l_act_duration;
3760 END IF;
3761 END LOOP;
3762
3763 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
3764 gme_debug.put_line ('l_max_act_duration ' || l_max_act_duration);
3765 END IF;
3766
3767 IF g_debug <= gme_debug.g_log_procedure THEN
3768 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
3769 END IF;
3770
3771 RETURN l_max_act_duration;
3772 EXCEPTION
3773 WHEN missing_profile_option THEN
3774 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
3775 gme_debug.put_line (l_api_name
3776 || ' ERROR - missing_profile_option');
3777 END IF;
3778
3779 l_max_act_duration := 0;
3780 RETURN 0;
3781 WHEN OTHERS THEN
3782 IF g_debug <= gme_debug.g_log_unexpected THEN
3783 gme_debug.put_line ( 'When others exception in '
3784 || g_pkg_name
3785 || '.'
3786 || l_api_name
3787 || ' Error is '
3788 || SQLERRM);
3789 END IF;
3790
3791 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
3792 RETURN 0;
3793 END get_max_duration;
3794
3795 PROCEDURE insert_resource_txns (
3796 p_gme_batch_header_rec IN gme_batch_header%ROWTYPE
3797 ,p_doc_type IN VARCHAR2
3798 ,p_batch_step_resources_rec IN gme_batch_step_resources%ROWTYPE
3799 ,p_trans_count IN NUMBER
3800 ,x_return_status OUT NOCOPY VARCHAR2)
3801 IS
3802 l_gme_resource_txns gme_resource_txns%ROWTYPE;
3803 l_txn_usage NUMBER;
3804 l_usage_time NUMBER;
3805 l_hour_um mtl_units_of_measure.uom_code%TYPE;
3806 l_api_name CONSTANT VARCHAR2 (30) := 'insert_resource_txns';
3807 error_insert_res_txns EXCEPTION;
3808 missing_profile_option EXCEPTION;
3809 --FPBug#4395561
3810 create_flex_failure EXCEPTION;
3811 l_return_status VARCHAR2(1);
3812 BEGIN
3813 IF g_debug <= gme_debug.g_log_procedure THEN
3814 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
3815 || l_api_name);
3816 END IF;
3817
3818 l_usage_time :=
3819 ( p_batch_step_resources_rec.plan_cmplt_date
3820 - p_batch_step_resources_rec.plan_start_date)
3821 * 24;
3822 l_hour_um := gme_common_pvt.g_hour_uom_code;
3823 l_txn_usage :=
3824 inv_convert.inv_um_convert
3825 (item_id => 0
3826 ,PRECISION => 5
3827 ,from_quantity => l_usage_time
3828 ,from_unit => l_hour_um
3829 ,to_unit => p_batch_step_resources_rec.usage_um
3830 ,from_name => NULL
3831 ,to_name => NULL);
3832
3833 -- Bug 9212573 - Add '=' to usage condition and new p_trans_count condition from 11.5.10.
3834 IF (l_txn_usage <= 0 OR NVL(p_trans_count, 1) = 1) THEN
3835 l_txn_usage :=
3836 p_batch_step_resources_rec.plan_rsrc_usage
3837 / p_batch_step_resources_rec.plan_rsrc_count;
3838 END IF;
3839
3840 IF p_gme_batch_header_rec.update_inventory_ind = 'Y' THEN
3841 l_gme_resource_txns.doc_id := p_batch_step_resources_rec.batch_id;
3842 l_gme_resource_txns.doc_type := p_doc_type;
3843 l_gme_resource_txns.organization_id :=
3844 p_gme_batch_header_rec.organization_id;
3845 l_gme_resource_txns.line_type := 0;
3846 l_gme_resource_txns.line_id :=
3847 p_batch_step_resources_rec.batchstep_resource_id;
3848 l_gme_resource_txns.resources :=
3849 p_batch_step_resources_rec.resources;
3850 l_gme_resource_txns.resource_usage := ROUND (l_txn_usage, 32);
3851 l_gme_resource_txns.trans_qty_um :=
3852 p_batch_step_resources_rec.usage_um;
3853 l_gme_resource_txns.trans_date :=
3854 p_batch_step_resources_rec.plan_start_date;
3855 l_gme_resource_txns.completed_ind := 0;
3856 l_gme_resource_txns.posted_ind := 0;
3857 l_gme_resource_txns.overrided_protected_ind := 'N';
3858 l_gme_resource_txns.start_date :=
3859 p_batch_step_resources_rec.plan_start_date;
3860 l_gme_resource_txns.end_date :=
3861 p_batch_step_resources_rec.plan_cmplt_date;
3862 l_gme_resource_txns.delete_mark := 0;
3863
3864 FOR i IN 1 .. p_batch_step_resources_rec.plan_rsrc_count LOOP
3865 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
3866 gme_debug.put_line ( g_pkg_name
3867 || '.'
3868 || l_api_name
3869 || ' '
3870 || 'resource transaction # '
3871 || i
3872 || ' end date '
3873 || TO_CHAR (l_gme_resource_txns.end_date
3874 ,'DD-MON-YYYY HH24:MI:SS') );
3875 END IF;
3876
3877 /* once api is known will add this GMF_PeriodClose_PUB.Verify_PeriodClose
3878 IF NOT gme_api_grp.close_period_check_flexible(p_tran_rec => p_tran_rec,
3879 x_tran_rec => l_tran_rec_out) THEN
3880 RAISE FND_API.g_exc_error;
3881 END IF;
3882
3883 l_gme_resource_txns.trans_date := l_tran_rec_out.trans_date;
3884 */
3885
3886 --FPBug#4395561 Start
3887 /*call create flex procedure to insert the default values of the GME_RSRC_TXN_FLEX
3888 DFF's segments if they are enabled */
3889 l_return_status:=NULL;
3890 gme_validate_flex_fld_pvt.create_flex_resource_txns (
3891 l_gme_resource_txns,
3892 l_gme_resource_txns,
3893 l_return_status);
3894 IF l_return_status <> FND_API.g_ret_sts_success THEN
3895 RAISE create_flex_failure;
3896 END IF;
3897 --FPBug#4395561 End
3898
3899 IF (gme_resource_txns_dbl.insert_row
3900 (p_resource_txns => l_gme_resource_txns
3901 ,x_resource_txns => l_gme_resource_txns) ) THEN
3902 NULL;
3903 ELSE
3904 RAISE error_insert_res_txns;
3905 END IF;
3906 END LOOP;
3907 END IF;
3908
3909 IF g_debug <= gme_debug.g_log_procedure THEN
3910 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
3911 END IF;
3912 EXCEPTION
3913 WHEN error_insert_res_txns THEN
3914 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
3915 gme_debug.put_line ('insert resource txns error');
3916 END IF;
3917
3918 x_return_status := fnd_api.g_ret_sts_error;
3919 WHEN missing_profile_option THEN
3920 x_return_status := fnd_api.g_ret_sts_error;
3921 WHEN fnd_api.g_exc_error THEN
3922 x_return_status := fnd_api.g_ret_sts_error;
3923 --FPBug#4395561
3924 WHEN create_flex_failure THEN
3925 x_return_status := l_return_status;
3926 IF (NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT) THEN
3927 gme_debug.put_line ('Creating the default values of the DFF failure');
3928 END IF;
3929 WHEN OTHERS THEN
3930 IF g_debug <= gme_debug.g_log_unexpected THEN
3931 gme_debug.put_line ( 'When others exception in '
3932 || g_pkg_name
3933 || '.'
3934 || l_api_name
3935 || ' Error is '
3936 || SQLERRM);
3937 END IF;
3938
3939 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
3940 END insert_resource_txns;
3941 END gme_create_step_pvt;