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