1 PACKAGE BODY gme_release_batch_step_pvt AS
2 /* $Header: GMEVRLSB.pls 120.15.12020000.2 2012/07/26 15:44:53 gmurator ship $ */
3 g_debug VARCHAR2 (5) := fnd_profile.VALUE ('AFLOG_LEVEL');
4 g_pkg_name CONSTANT VARCHAR2 (30) := 'gme_release_batch_step_pvt';
5 l_first_step_start_date DATE;
6
7 /*===========================================================================================
8 Procedure
9 release_step
10 Description
11 This procedure call releases a batch step and dependent steps
12 if the step is automatic.
13
14 Parameters
15 p_batch_step_rec The batch step record to release.
16 p_batch_header_rec The batch header of the batch that the step belongs to.
17 x_batch_step_rec Output updated batch step record.
18 x_exception_material_tbl Table of records of exceptions found while releasing the step, any dependent steps
19 and possibly the batch (if it was released).
20 x_return_status outcome of the API call
21 S - Success
22 E - Error
23 U - Unexpected error
24 X - Exceptions found
25 =============================================================================================*/
26 PROCEDURE release_step (
27 p_batch_step_rec IN gme_batch_steps%ROWTYPE
28 ,p_batch_header_rec IN gme_batch_header%ROWTYPE
29 ,x_batch_step_rec OUT NOCOPY gme_batch_steps%ROWTYPE
30 ,x_exception_material_tbl IN OUT NOCOPY gme_common_pvt.exceptions_tab
31 ,x_return_status OUT NOCOPY VARCHAR2)
32 IS
33 l_api_name CONSTANT VARCHAR2 (30) := 'RELEASE_STEP';
34 /* Exception definitions */
35 error_release_batch EXCEPTION;
36 error_release_step_rec EXCEPTION;
37 /* Local variables */
38 l_return_status VARCHAR2 (1);
39 l_batch_header_rec gme_batch_header%ROWTYPE;
40 l_batch_step_rec gme_batch_steps%ROWTYPE;
41 --Bug#5109119
42 l_in_batch_header_rec gme_batch_header%ROWTYPE;
43 BEGIN
44 IF NVL (g_debug, gme_debug.g_log_procedure + 1) <=
45 gme_debug.g_log_procedure THEN
46 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
47 || l_api_name);
48 gme_debug.put_line ( g_pkg_name
49 || '.'
50 || l_api_name
51 || ' Releasing batch_step_id='
52 || p_batch_step_rec.batchstep_id);
53 END IF;
54
55 /* Set the return status to success initially */
56 x_return_status := fnd_api.g_ret_sts_success;
57 l_batch_step_rec := p_batch_step_rec;
58 l_batch_header_rec := p_batch_header_rec;
59
60 /* Bug#5109119 initialize with NULL and it should be NULL as we are testing
61 for NULL value later in our code.
62 */
63 l_first_step_start_date := NULL;
64
65 release_step_recursive
66 (p_batch_step_rec => l_batch_step_rec
67 ,p_batch_header_rec => l_batch_header_rec
68 ,x_batch_step_rec => x_batch_step_rec
69 ,x_exception_material_tbl => x_exception_material_tbl
70 ,x_return_status => l_return_status);
71
72 IF l_return_status NOT IN
73 (fnd_api.g_ret_sts_success, gme_common_pvt.g_exceptions_err) THEN
74 x_return_status := l_return_status;
75 RAISE error_release_step_rec;
76 END IF;
77
78 IF l_return_status = gme_common_pvt.g_exceptions_err THEN
79 x_return_status := gme_common_pvt.g_exceptions_err;
80 END IF;
81
82 /*Bug#5109119 moved the release batch call (which is there before call to release_step_recursive)
83 moved here because first step actual start date will be available if and only if the above procedure returns
84 */
85 /* If the batch status is Pending, then release the batch... */
86 /* check that this is valid already done in Pub */
87 IF (p_batch_header_rec.batch_status = gme_common_pvt.g_batch_pending) THEN
88 /* Bug#5109119 intialize Batch actual start date with the passed one or with the calculated
89 first step actual start date no need to check for close period here as it's one of the step actual start
90 dates and already validated in process_Dependent_steps procedure*/
91 l_in_batch_header_rec := p_batch_header_rec;
92 l_in_batch_header_rec.actual_start_date := NVL(l_first_step_start_date,p_batch_step_rec.actual_start_date);
93
94 gme_release_batch_pvt.release_batch
95 (p_batch_header_rec => l_in_batch_header_rec
96 ,p_phantom_product_id => NULL
97 ,x_batch_header_rec => l_batch_header_rec
98 ,x_return_status => l_return_status
99 ,x_exception_material_tbl => x_exception_material_tbl);
100
101 IF l_return_status NOT IN
102 (fnd_api.g_ret_sts_success, gme_common_pvt.g_exceptions_err) THEN
103 x_return_status := l_return_status;
104 RAISE error_release_batch;
105 END IF;
106
107 IF l_return_status = gme_common_pvt.g_exceptions_err THEN
108 x_return_status := gme_common_pvt.g_exceptions_err;
109 END IF;
110 END IF;
111
112
113 IF NVL (g_debug, gme_debug.g_log_procedure + 1) <=
114 gme_debug.g_log_procedure THEN
115 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
116 END IF;
117 EXCEPTION
118 WHEN error_release_batch OR error_release_step_rec THEN
119 NULL;
120 WHEN OTHERS THEN
121 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
122
123 IF g_debug <= gme_debug.g_log_procedure THEN
124 gme_debug.put_line ( 'Unexpected error: '
125 || g_pkg_name
126 || '.'
127 || l_api_name
128 || ': '
129 || SQLERRM);
130 END IF;
131
132 x_return_status := fnd_api.g_ret_sts_unexp_error;
133 END release_step;
134
135 /*===========================================================================================
136 Procedure
137 release_step_recursive
138 Description
139 Recursively call this procedure for dependent steps based on the dependency.
140 Once recursion stops, release the step. Releasing must be an end of recursion process
141 Parameters
142 p_batch_step_rec The batch step record to release.
143 p_batch_header_rec The batch header of the batch that the step belongs to.
144 x_batch_step_rec Output updated batch step record.
145 x_exception_material_tbl Table of records of exceptions found while releasing the step, any dependent steps
146 x_return_status outcome of the API call
147 S - Success
148 E - Error
149 U - Unexpected error
150 X - Exception found
151 =============================================================================================*/
152 PROCEDURE release_step_recursive (
153 p_batch_step_rec IN gme_batch_steps%ROWTYPE
154 ,p_batch_header_rec IN gme_batch_header%ROWTYPE
155 ,x_batch_step_rec OUT NOCOPY gme_batch_steps%ROWTYPE
156 ,x_exception_material_tbl IN OUT NOCOPY gme_common_pvt.exceptions_tab
157 ,x_return_status OUT NOCOPY VARCHAR2)
158 IS
159 l_api_name CONSTANT VARCHAR2 (30) := 'release_step_recursive';
160 step_rel_cmpl_closed EXCEPTION;
161 rel_step_line_error EXCEPTION;
162 rel_step_ing_error EXCEPTION;
163 update_step_qty_error EXCEPTION;
164 error_process_dep_steps EXCEPTION;
165 error_validation EXCEPTION;
166 /* Local variables */
167 l_return_status VARCHAR2 (1);
168 l_msg_count NUMBER;
169 l_msg_stack VARCHAR2 (2000);
170 l_in_batch_step_rec gme_batch_steps%ROWTYPE;
171 l_batch_step_rec gme_batch_steps%ROWTYPE;
172 BEGIN
173 IF NVL (g_debug, gme_debug.g_log_procedure + 1) <=
174 gme_debug.g_log_procedure THEN
175 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
176 || l_api_name);
177 gme_debug.put_line (g_pkg_name
178 || '.'
179 || l_api_name
180 || ' Release step recursive batch_step_id='
181 || p_batch_step_rec.batchstep_id);
182 gme_debug.put_line(g_pkg_name
183 || '.'
184 || l_api_name
185 || ' step actual start date='
186 || to_char(p_batch_step_rec.actual_start_date,'YYYY-MON-DD HH24:MI:SS'));
187 END IF;
188
189 /* Set the return status to success initially */
190 x_return_status := fnd_api.g_ret_sts_success;
191
192 /* Exit the recursive loop if the step is already released, completed or closed */
193 IF p_batch_step_rec.step_status IN
194 (gme_common_pvt.g_step_wip
195 ,gme_common_pvt.g_step_completed
196 ,gme_common_pvt.g_step_closed) THEN
197 RAISE step_rel_cmpl_closed;
198 END IF;
199
200 gme_validate_flex_fld_pvt.validate_flex_batch_step
201 (p_batch_step => p_batch_step_rec
202 ,x_batch_step => l_batch_step_rec
203 ,x_return_status => x_return_status);
204
205 IF x_return_status <> fnd_api.g_ret_sts_success THEN
206 RAISE error_validation;
207 END IF;
208
209 process_dependent_steps
210 (p_batch_step_rec => p_batch_step_rec
211 ,p_batch_header_rec => p_batch_header_rec
212 ,x_exception_material_tbl => x_exception_material_tbl
213 ,x_return_status => l_return_status);
214
215 IF l_return_status NOT IN
216 (fnd_api.g_ret_sts_success, gme_common_pvt.g_exceptions_err) THEN
217 x_return_status := l_return_status;
218 RAISE error_process_dep_steps;
219 END IF;
220
221 IF l_return_status = gme_common_pvt.g_exceptions_err THEN
222 x_return_status := gme_common_pvt.g_exceptions_err;
223 END IF;
224
225 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
226 gme_debug.put_line
227 ('Calling release step line to complete ingredient transactions...');
228 gme_debug.put_line ('for step = ' || x_batch_step_rec.batchstep_id);
229 END IF;
230
231 release_step_line (p_batch_step_rec => p_batch_step_rec
232 ,x_batch_step_rec => x_batch_step_rec
233 ,x_exception_material_tbl => x_exception_material_tbl
234 ,x_return_status => l_return_status);
235
236 IF l_return_status <> fnd_api.g_ret_sts_success THEN
237 x_return_status := l_return_status;
238 RAISE rel_step_line_error;
239 END IF;
240
241 release_step_ingredients
242 (p_batch_step_rec => x_batch_step_rec
243 ,p_update_inv_ind => p_batch_header_rec.update_inventory_ind
244 ,x_exception_material_tbl => x_exception_material_tbl
245 ,x_return_status => l_return_status);
246
247 IF l_return_status NOT IN
248 (fnd_api.g_ret_sts_success, gme_common_pvt.g_exceptions_err) THEN
249 x_return_status := l_return_status;
250 RAISE rel_step_ing_error;
251 END IF;
252
253 IF l_return_status = gme_common_pvt.g_exceptions_err THEN
254 x_return_status := gme_common_pvt.g_exceptions_err;
255 END IF;
256
257 /* Invoke the update step qty API to update the step quantities and the */
258 /* quantities of the succeeding steps */
259 l_in_batch_step_rec := x_batch_step_rec;
260 gme_update_step_qty_pvt.update_step_qty
261 (p_batch_step_rec => l_in_batch_step_rec
262 ,x_message_count => l_msg_count
263 ,x_message_list => l_msg_stack
264 ,x_return_status => l_return_status
265 ,x_batch_step_rec => x_batch_step_rec);
266
267 IF l_return_status <> fnd_api.g_ret_sts_success THEN
268 RAISE update_step_qty_error;
269 END IF;
270
271 /* Needs to be done for each step released */
272 IF (x_batch_step_rec.quality_status = 2) THEN
273 wf_event.RAISE
274 (p_event_name => gme_common_pvt.G_BSTEP_REL_WF
275 ,p_event_key => TO_CHAR
276 (x_batch_step_rec.batchstep_id) );
277 END IF;
278
279 IF NVL (g_debug, gme_debug.g_log_procedure + 1) <=
280 gme_debug.g_log_procedure THEN
281 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
282 END IF;
283 EXCEPTION
284 WHEN error_validation THEN
285 NULL;
286 WHEN update_step_qty_error THEN
287 x_return_status := l_return_status;
288 WHEN step_rel_cmpl_closed OR rel_step_line_error OR rel_step_ing_error OR error_process_dep_steps THEN
289 NULL;
290 WHEN OTHERS THEN
291 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
292
293 IF g_debug <= gme_debug.g_log_procedure THEN
294 gme_debug.put_line ( 'Unexpected error: '
295 || g_pkg_name
296 || '.'
297 || l_api_name
298 || ': '
299 || SQLERRM);
300 END IF;
301
302 x_return_status := fnd_api.g_ret_sts_unexp_error;
303 END release_step_recursive;
304
305 PROCEDURE process_dependent_steps (
306 p_batch_step_rec IN gme_batch_steps%ROWTYPE
307 ,p_batch_header_rec IN gme_batch_header%ROWTYPE
308 ,x_exception_material_tbl IN OUT NOCOPY gme_common_pvt.exceptions_tab
309 ,x_return_status OUT NOCOPY VARCHAR2)
310 IS
311 l_api_name CONSTANT VARCHAR2 (30) := 'process_dependent_steps';
312
313 CURSOR cur_get_dep_steps (v_batchstep_id NUMBER, v_batch_id NUMBER)
314 IS
315 SELECT d.dep_step_id, d.dep_type, d.standard_delay
316 ,s.steprelease_type, s.step_status
317 FROM gme_batch_step_dependencies d, gme_batch_steps s
318 WHERE d.batchstep_id = v_batchstep_id
319 AND s.batchstep_id = d.dep_step_id
320 AND s.batch_id = v_batch_id
321 AND d.batch_id = s.batch_id;
322
323 l_dep_step_rec cur_get_dep_steps%ROWTYPE;
324 l_return_status VARCHAR2 (1);
325 l_batch_step_rec gme_batch_steps%ROWTYPE;
326 l_in_batch_step_rec gme_batch_steps%ROWTYPE;
327 l_complete_dep_step BOOLEAN;
328
329 batch_step_fetch_error EXCEPTION;
330 dep_step_rel_error EXCEPTION;
331 dep_step_cmpl_error EXCEPTION;
332 --Bug#5109119
333 error_close_period EXCEPTION;
334 error_future_date EXCEPTION;
335 BEGIN
336 IF NVL (g_debug, gme_debug.g_log_procedure + 1) <=
337 gme_debug.g_log_procedure THEN
338 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
339 || l_api_name);
340 gme_debug.put_line ( g_pkg_name
341 || '.'
342 || l_api_name
343 || ' Processing batch_step_id='
344 || p_batch_step_rec.batchstep_id);
345 END IF;
346
347 /* Set the return status to success initially */
348 x_return_status := fnd_api.g_ret_sts_success;
349
350 /* Bug#5109119 Begin check the close period for current step. this check will be done for current step and dependent steps
351 also as process_dependent_steps will be called for each step*/
352
353 -- Bug 8595231 - We do not need to validate the start date if the step is already released.
354 -- As this procedure gets called by complete_step_recursive also.
355 IF p_batch_step_rec.step_status = 1 THEN
356 IF NOT gme_common_pvt.check_close_period(p_org_id => p_batch_header_rec.organization_id
357 ,p_trans_date => p_batch_step_rec.actual_start_date) THEN
358 RAISE error_close_period;
359 END IF;
360 END IF;
361
362 /* because of step depedency types(-ve offset) some time the dependent step might get the date that can greater
363 than sysdate. so following check is introduced */
364 IF (p_batch_step_rec.actual_start_date > SYSDATE) THEN
365 RAISE error_future_date;
366 END IF;
367 /* Bug#5109119 End */
368
369 /* Get the immediate dependent steps for the current step */
370 FOR l_dep_step_rec IN cur_get_dep_steps (p_batch_step_rec.batchstep_id
371 ,p_batch_header_rec.batch_id) LOOP
372 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
373 gme_debug.put_line ( g_pkg_name
374 || '.'
375 || l_api_name
376 || 'fetched dep step '
377 || l_dep_step_rec.dep_step_id);
378 gme_debug.put_line ( g_pkg_name
379 || '.'
380 || l_api_name
381 || 'steprelease_type = '
382 || l_dep_step_rec.steprelease_type);
383 gme_debug.put_line ( g_pkg_name
384 || '.'
385 || l_api_name
386 || 'dep_type = '
387 || l_dep_step_rec.dep_type);
388 gme_debug.put_line ( g_pkg_name
389 || '.'
390 || l_api_name
391 || 'standard_delay = '
392 || l_dep_step_rec.standard_delay);
393 gme_debug.put_line ( g_pkg_name
394 || '.'
395 || l_api_name
396 || 'step_status = '
397 || l_dep_step_rec.step_status);
398 END IF;
399
400 /* If the dependent step is set to automatic release */
401 IF l_dep_step_rec.steprelease_type =
402 gme_common_pvt.g_auto_step_release THEN
403 l_batch_step_rec.batchstep_id := l_dep_step_rec.dep_step_id;
404
405 IF NOT (gme_batch_steps_dbl.fetch_row (l_batch_step_rec
406 ,l_batch_step_rec) ) THEN
407 RAISE batch_step_fetch_error;
408 END IF;
409
410 /* If the dependency is Finish To Start and their is a positive delay */
411 /* then complete the dependent step otherwise call the release API if the step */
412 /* status is pending. */
413 IF (l_dep_step_rec.dep_type =
414 gme_common_pvt.g_dep_type_finish_start)
415 AND (l_dep_step_rec.standard_delay >= 0) THEN
416 l_complete_dep_step := TRUE;
417 ELSE
418 l_complete_dep_step := FALSE;
419 END IF; /* IF (l_dep_step_rec.dep_type = gme_common_pvt.g_dep_type_finish_start) AND */
420
421 /* If the step status is pending */
422 IF (l_dep_step_rec.step_status = gme_common_pvt.g_step_pending) THEN
423 /*Bug#5183521 when step dependency is start to start, the dependent should start before the current step
424 considering the standard delay defined in dependencies.commented the ELSE and added OR condition */
425 IF (l_dep_step_rec.dep_type = gme_common_pvt.g_dep_type_finish_start AND
426 l_dep_step_rec.standard_delay < 0) OR
427 (l_dep_step_rec.dep_type = gme_common_pvt.g_dep_type_start_start) THEN
428 l_batch_step_rec.actual_start_date :=
429 p_batch_step_rec.actual_start_date
430 - (l_dep_step_rec.standard_delay / 24);
431 /*ELSIF (l_dep_step_rec.dep_type =
432 gme_common_pvt.g_dep_type_start_start) THEN
433 l_batch_step_rec.actual_start_date :=
434 p_batch_step_rec.actual_start_date
435 + (l_dep_step_rec.standard_delay / 24); */
436 END IF;
437 /* dep_type = gme_common_pvt.g_dep_type_finish_start */
438
439 l_in_batch_step_rec := l_batch_step_rec;
440 /* Bug#5109119 when l_complete_dep_step is TRUE call complete_step directly, no need to call release_step */
441 IF NOT l_complete_dep_step THEN
442 release_step_recursive
443 (p_batch_step_rec => l_in_batch_step_rec
444 ,p_batch_header_rec => p_batch_header_rec
445 ,x_batch_step_rec => l_batch_step_rec
446 ,x_exception_material_tbl => x_exception_material_tbl
447 ,x_return_status => l_return_status);
448
449 IF l_return_status NOT IN
450 (fnd_api.g_ret_sts_success, gme_common_pvt.g_exceptions_err) THEN
451 x_return_status := l_return_status;
452 RAISE dep_step_rel_error;
453 END IF;
454
455 IF l_return_status = gme_common_pvt.g_exceptions_err THEN
456 x_return_status := gme_common_pvt.g_exceptions_err;
457 END IF;
458 END IF; /* IF NOT l_complete_dep_step THEN */
459 END IF; /* IF (l_dep_step_rec.step_status = gme_common_pvt.g_step_pending) */
460
461 IF l_complete_dep_step THEN
462 l_batch_step_rec.actual_cmplt_date :=
463 p_batch_step_rec.actual_start_date
464 - (l_dep_step_rec.standard_delay / 24);
465
466 --Sunitha Ch. bug#5488991 assigning the completion date to the start only when it is null
467 IF l_batch_step_rec.actual_start_date IS NULL THEN
468 l_batch_step_rec.actual_start_date :=
469 l_batch_step_rec.actual_cmplt_date;
470 ELSE
471 IF l_batch_step_rec.actual_cmplt_date < l_batch_step_rec.actual_start_date THEN
472 l_batch_step_rec.actual_cmplt_date :=
473 l_batch_step_rec.actual_start_date;
474 END IF;
475 END IF;
476 l_in_batch_step_rec := l_batch_step_rec;
477 gme_complete_batch_step_pvt.complete_step_recursive
478 (p_batch_step_rec => l_in_batch_step_rec
479 ,p_batch_header_rec => p_batch_header_rec
480 ,x_return_status => l_return_status
481 ,x_batch_step_rec => l_batch_step_rec
482 ,x_exception_material_tbl => x_exception_material_tbl);
483
484 IF l_return_status NOT IN
485 (fnd_api.g_ret_sts_success
486 ,gme_common_pvt.g_exceptions_err) THEN
487 x_return_status := l_return_status;
488 RAISE dep_step_cmpl_error;
489 END IF;
490
491 IF l_return_status = gme_common_pvt.g_exceptions_err THEN
492 x_return_status := gme_common_pvt.g_exceptions_err;
493 END IF;
494 END IF; -- IF l_complete_dep_step THEN
495 END IF; -- IF l_dep_step_rec.steprelease_type = gme_common_pvt.g_auto_step_release
496 END LOOP; /* FOR l_dep_step_rec IN Cur_get_dep_steps */
497
498
499 /* Bug#5109119 l_first_step_start_date will preserve the min(step dates) between the recursive calls.
500 we compare this date with the actual start date of the current step and if this is less than then
501 change
502 */
503 IF l_first_step_start_date IS NULL OR
504 l_first_step_start_date > p_batch_step_rec.actual_start_date THEN
505 l_first_step_start_date := p_batch_step_rec.actual_start_date;
506 END IF;
507
508 IF NVL (g_debug, gme_debug.g_log_procedure + 1) <=
509 gme_debug.g_log_procedure THEN
510 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
511 END IF;
512 EXCEPTION
513 --Bug#5109119 Begin
514 WHEN error_close_period THEN
515 x_return_status := FND_API.G_RET_STS_ERROR;
516 WHEN error_future_date THEN
517 fnd_message.set_name ('GMA', 'SY_NOFUTUREDATE');
518 fnd_msg_pub.ADD;
519 x_return_status := FND_API.G_RET_STS_ERROR;
520 --Bug#5109119 End
521 WHEN batch_step_fetch_error THEN
522 gme_common_pvt.log_message ('GME_UNEXPECTED_ERROR', 'ERROR'
523 ,SQLERRM);
524 x_return_status := fnd_api.g_ret_sts_unexp_error;
525 WHEN dep_step_rel_error OR dep_step_cmpl_error THEN
526 NULL;
527 WHEN OTHERS THEN
528 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
529
530 IF g_debug <= gme_debug.g_log_procedure THEN
531 gme_debug.put_line ( 'Unexpected error: '
532 || g_pkg_name
533 || '.'
534 || l_api_name
535 || ': '
536 || SQLERRM);
537 END IF;
538
539 x_return_status := fnd_api.g_ret_sts_unexp_error;
540 END process_dependent_steps;
541
542 /*===========================================================================================
543 Procedure
544 release_step_line
545 Description
546 This procedure releases the step and updates actual dates for activity and resource.
547 Parameters
548 p_batch_step_rec Input Batch Step Line
549 p_batch_header_rec Batch Header that step belongs to
550 x_batch_step_rec Output Batch Step Line
551 x_return_status outcome of the API call
552 S - Success
553 E - Error
554 U - Unexpected error
555 History
556 G. Muratore 27-MAR-2012 Bug 13706812
557 Improved performance of activities cursor. This issue was found by migration testing.
558 =============================================================================================*/
559 PROCEDURE release_step_line (
560 p_batch_step_rec IN gme_batch_steps%ROWTYPE
561 ,x_batch_step_rec OUT NOCOPY gme_batch_steps%ROWTYPE
562 ,x_exception_material_tbl IN OUT NOCOPY gme_common_pvt.exceptions_tab
563 ,x_return_status OUT NOCOPY VARCHAR2)
564 IS
565 l_api_name CONSTANT VARCHAR2 (30) := 'release_step_line';
566 l_return_status VARCHAR2 (1);
567 l_batch_step_rec gme_batch_steps%ROWTYPE;
568 l_in_batch_step_rec gme_batch_steps%ROWTYPE;
569 batch_step_upd_err EXCEPTION;
570 BEGIN
571 IF NVL (g_debug, gme_debug.g_log_procedure + 1) <=
572 gme_debug.g_log_procedure THEN
573 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
574 || l_api_name);
575 gme_debug.put_line ( g_pkg_name
576 || '.'
577 || l_api_name
578 || ' Release step line batchstep_id='
579 || p_batch_step_rec.batchstep_id);
580 END IF;
581
582 x_return_status := fnd_api.g_ret_sts_success;
583 -- Each time this is called, p_batch_step_rec has already been retrieved from DB... has all
584 -- latest data and in addition has the actual start date calculated and set
585 x_batch_step_rec := p_batch_step_rec;
586 /* Update the Batch Step Status to WIP */
587 x_batch_step_rec.step_status := gme_common_pvt.g_step_wip;
588
589 -- Update the batch step
590 IF NOT (gme_batch_steps_dbl.update_row (x_batch_step_rec) ) THEN
591 RAISE batch_step_upd_err;
592 END IF;
593
594 -- Update WHO columns for output structure
595 x_batch_step_rec.last_updated_by := gme_common_pvt.g_user_ident;
596 x_batch_step_rec.last_update_date := gme_common_pvt.g_timestamp;
597 x_batch_step_rec.last_update_login := gme_common_pvt.g_login_id;
598
599 -- Bug 13706812 - Use batch id in whse clause to help performance.
600 -- Update activity start date
601 -- Does not factor in offset
602 UPDATE gme_batch_step_activities
603 SET actual_start_date = x_batch_step_rec.actual_start_date
604 ,last_updated_by = gme_common_pvt.g_user_ident
605 ,last_update_date = gme_common_pvt.g_timestamp
606 ,last_update_login = gme_common_pvt.g_login_id
607 WHERE batchstep_id = x_batch_step_rec.batchstep_id
608 AND batch_id = x_batch_step_rec.batch_id;
609
610 -- Update resource start date
611 -- Does not factor in offset
612 UPDATE gme_batch_step_resources
613 SET actual_start_date = x_batch_step_rec.actual_start_date
614 ,last_updated_by = gme_common_pvt.g_user_ident
615 ,last_update_date = gme_common_pvt.g_timestamp
616 ,last_update_login = gme_common_pvt.g_login_id
617 WHERE batchstep_id = x_batch_step_rec.batchstep_id;
618
619 IF NVL (g_debug, gme_debug.g_log_procedure + 1) <=
620 gme_debug.g_log_procedure THEN
621 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
622 END IF;
623 EXCEPTION
624 WHEN batch_step_upd_err THEN
625 gme_common_pvt.log_message ('GME_UNEXPECTED_ERROR', 'ERROR'
626 ,SQLERRM);
627 x_return_status := fnd_api.g_ret_sts_unexp_error;
628 WHEN OTHERS THEN
629 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
630
631 IF g_debug <= gme_debug.g_log_procedure THEN
632 gme_debug.put_line ( 'Unexpected error: '
633 || g_pkg_name
634 || '.'
635 || l_api_name
636 || ': '
637 || SQLERRM);
638 END IF;
639
640 x_return_status := fnd_api.g_ret_sts_unexp_error;
641 END release_step_line;
642
643 PROCEDURE release_step_ingredients (
644 p_batch_step_rec IN gme_batch_steps%ROWTYPE
645 ,p_update_inv_ind IN VARCHAR2
646 ,x_exception_material_tbl IN OUT NOCOPY gme_common_pvt.exceptions_tab
647 ,x_return_status OUT NOCOPY VARCHAR2)
648 IS
649 CURSOR cur_step_ingredients (v_batchstep_id NUMBER)
650 IS
651 SELECT matl.*
652 FROM gme_material_details matl, gme_batch_step_items item
653 WHERE item.batchstep_id = v_batchstep_id
654 AND item.material_detail_id = matl.material_detail_id
655 AND matl.line_type = gme_common_pvt.g_line_type_ing
656 AND matl.release_type = gme_common_pvt.g_mtl_autobystep_release;
657
658 l_api_name CONSTANT VARCHAR2 (30) := 'release_step_ingredients';
659 l_return_status VARCHAR2 (1);
660 l_matl_dtl_rec gme_material_details%ROWTYPE;
661 l_matl_dtl_tab gme_common_pvt.material_details_tab;
662 l_consume BOOLEAN;
663
664 l_reserved_qty NUMBER; -- Bug 13795581
665
666 error_process_ing EXCEPTION;
667 BEGIN
668 IF NVL (g_debug, gme_debug.g_log_procedure + 1) <=
669 gme_debug.g_log_procedure THEN
670 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
671 || l_api_name);
672 gme_debug.put_line ( g_pkg_name
673 || '.'
674 || l_api_name
675 || ' Releasing ingredients for step_id='
676 || p_batch_step_rec.batchstep_id);
677 END IF;
678
679 /* Set the return status to success initially */
680 x_return_status := fnd_api.g_ret_sts_success;
681
682 -- retrieve all autobystep ingredients associated to the step...
683 OPEN cur_step_ingredients (p_batch_step_rec.batchstep_id);
684
685 FETCH cur_step_ingredients
686 BULK COLLECT INTO l_matl_dtl_tab;
687
688 CLOSE cur_step_ingredients;
689
690 FOR i IN 1 .. l_matl_dtl_tab.COUNT LOOP
691 l_matl_dtl_rec := l_matl_dtl_tab (i);
692
693 -- Bug 13795581 - Check reserved qty if wip plan is zero.
694 l_reserved_qty := 99;
695 IF nvl(l_matl_dtl_rec.wip_plan_qty, 0) = 0 THEN
696 gme_reservations_pvt.get_reserved_qty(p_mtl_dtl_rec => l_matl_dtl_rec,
697 p_supply_sub_only => 'F',
698 x_reserved_qty => l_reserved_qty,
699 x_return_status => l_return_status);
700 END IF;
701
702 -- Bug 13795581 - bypass records which have a zero wip plan and also no reservations.
703 IF l_reserved_qty > 0 THEN
704 l_consume := TRUE;
705
706 gme_release_batch_pvt.process_ingredient
707 (p_material_detail_rec => l_matl_dtl_rec
708 ,p_consume => l_consume
709 ,p_trans_date => p_batch_step_rec.actual_start_date
710 ,p_update_inv_ind => p_update_inv_ind
711 ,x_exception_material_tbl => x_exception_material_tbl
712 ,x_return_status => l_return_status);
713
714 IF l_return_status NOT IN
715 (fnd_api.g_ret_sts_success, gme_common_pvt.g_exceptions_err) THEN
716 x_return_status := l_return_status;
717 RAISE error_process_ing;
718 END IF;
719
720 IF l_return_status = gme_common_pvt.g_exceptions_err THEN
721 x_return_status := gme_common_pvt.g_exceptions_err;
722 END IF;
723 END IF;
724 END LOOP;
725
726 IF NVL (g_debug, gme_debug.g_log_procedure + 1) <=
727 gme_debug.g_log_procedure THEN
728 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
729 END IF;
730 EXCEPTION
731 WHEN error_process_ing THEN
732 NULL;
733 WHEN OTHERS THEN
734 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
735
736 IF g_debug <= gme_debug.g_log_procedure THEN
737 gme_debug.put_line ( 'Unexpected error: '
738 || g_pkg_name
739 || '.'
740 || l_api_name
741 || ': '
742 || SQLERRM);
743 END IF;
744
745 x_return_status := fnd_api.g_ret_sts_unexp_error;
746 END release_step_ingredients;
747
748 PROCEDURE validate_step_for_release (p_batch_header_rec IN gme_batch_header%ROWTYPE
749 ,p_batch_step_rec IN gme_batch_steps%ROWTYPE
750 ,x_batch_step_rec OUT NOCOPY gme_batch_steps%ROWTYPE
751 ,x_return_status OUT NOCOPY VARCHAR2) IS
752
753 l_api_name CONSTANT VARCHAR2 (30) := 'validate_step_for_release';
754 CURSOR cur_validity_rule(v_recipe_validity_rule_id NUMBER)
755 IS
756 SELECT *
757 FROM gmd_recipe_validity_rules
758 WHERE recipe_validity_rule_id = v_recipe_validity_rule_id;
759
760 CURSOR cur_validity_status_type(v_validity_rule_status VARCHAR2)
761 IS
762 SELECT status_type
763 FROM gmd_status
764 WHERE status_code=v_validity_rule_status;
765
766 l_validity_rule gmd_recipe_validity_rules%ROWTYPE;
767 l_status_type GMD_STATUS.status_type%TYPE;
768 error_vr_not_found EXCEPTION;
769 error_validity_status EXCEPTION;
770 error_future_date EXCEPTION;
771 error_validation EXCEPTION;
772 error_actual_start_date EXCEPTION;
773 --Bug#5109119
774 error_close_period EXCEPTION;
775 error_vr_dates EXCEPTION;
776 BEGIN
777 IF NVL (g_debug, gme_debug.g_log_procedure + 1) <=
778 gme_debug.g_log_procedure THEN
779 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
780 || l_api_name);
781 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' batchstep_id = '||p_batch_step_rec.batchstep_id);
782 END IF;
783
784 x_return_status := fnd_api.g_ret_sts_success;
785
786 -- set output structure
787 x_batch_step_rec := p_batch_step_rec;
788
789 -- actual start date is filled in for both p_batch_header_rec and p_batch_step_rec
790
791 -- set actual start date if it's not passed
792 IF p_batch_step_rec.actual_start_date IS NULL THEN
793 x_batch_step_rec.actual_start_date := SYSDATE;
794 ELSE -- user passed in an actual start date; ensure it's not in the future
795 IF (p_batch_step_rec.actual_start_date > SYSDATE) THEN
796 RAISE error_future_date;
797 ELSIF p_batch_header_rec.batch_status = gme_common_pvt.g_batch_wip THEN
798 IF p_batch_step_rec.actual_start_date < p_batch_header_rec.actual_start_date THEN
799 RAISE error_actual_start_date;
800 END IF;
801 END IF;
802 x_batch_step_rec.actual_start_date := p_batch_step_rec.actual_start_date;
803 END IF;
804
805 --Sunith ch.5404329 check validity rule if it's not NULL; it would be NULL in case of LCF
806 IF p_batch_header_rec.recipe_validity_rule_id IS NOT NULL THEN
807 -- Bug 13004429 - This check should only be done for a pending batch.
808 IF p_batch_header_rec.batch_status = 1 THEN
809 OPEN cur_validity_rule(p_batch_header_rec.recipe_validity_rule_id);
810 FETCH cur_validity_rule INTO l_validity_rule;
811 CLOSE cur_validity_rule;
812
813 IF l_validity_rule.recipe_validity_rule_id IS NULL THEN -- not found
814 RAISE error_vr_not_found;
815 ELSE
816 -- following prevents user from releasing a pending batch
817 -- if validity rule is ON_HOLD or OBSOLETE.
818 OPEN cur_validity_status_type(l_validity_rule.validity_rule_status);
819 FETCH cur_validity_status_type INTO l_status_type;
820 CLOSE cur_validity_status_type;
821
822 IF l_status_type IN ('1000' ,'800') THEN
823 RAISE error_validity_status;
824 END IF;
825 END IF; -- IF l_validity_rule.recipe_validity_rule_id IS NULL
826
827 /* IF l_validity_rule.start_date > x_batch_header_rec.actual_start_date OR
828 (l_validity_rule.end_date IS NOT NULL AND
829 l_validity_rule.end_date < x_batch_header_rec.actual_start_date) THEN
830 RAISE error_vr_dates;
831 END IF;*/
832 -- sunitha ch. Bug 5336007 aded call to check_validity_rule_dates and passed p_validate_plan_dates_ind=1
833 -- to validate planned start date against validate rule dates
834 IF NOT gme_common_pvt.check_validity_rule_dates (
835 p_validity_rule_id => p_batch_header_rec.recipe_validity_rule_id
836 ,p_start_date => p_batch_header_rec.actual_start_date
837 ,p_cmplt_date => p_batch_header_rec.actual_cmplt_date
838 ,p_batch_header_rec => p_batch_header_rec
839 ,p_validate_plan_dates_ind => 1) THEN
840 x_return_status := fnd_api.g_ret_sts_error;
841 RAISE error_vr_dates;
842 END IF;
843 -- End Bug 5336007
844 END IF; -- p_batch_header_rec.batch_status = 1
845 END IF; -- IF p_batch_header_rec.recipe_validity_rule_id IS NOT NULL
846
847
848 IF NOT gme_common_pvt.check_close_period(p_org_id => p_batch_header_rec.organization_id
849 ,p_trans_date => x_batch_step_rec.actual_start_date) THEN
850 RAISE error_close_period;
851 END IF;
852
853 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
854 gme_debug.put_line (g_pkg_name||'.'||l_api_name||'actual_cmplt_date='||to_char(x_batch_step_rec.actual_cmplt_date,'DD-MON-YYYY HH24:MI:SS'));
855 END IF;
856
857 -- Enforce Step Dependency Checks
858 IF p_batch_header_rec.enforce_step_dependency = 1 THEN
859 -- validate dependent step status and dates
860 gme_complete_batch_step_pvt.validate_dependent_steps
861 (p_batch_id => x_batch_step_rec.batch_id
862 ,p_step_id => x_batch_step_rec.batchstep_id
863 ,p_step_actual_start_date => x_batch_step_rec.actual_start_date
864 ,x_return_status => x_return_status);
865
866 IF x_return_status <> fnd_api.g_ret_sts_success THEN
867 RAISE error_validation;
868 END IF;
869 END IF;
870
871 -- will check any auto by step associated to the step; phantom batches - all release types checked
872 gme_release_batch_pvt.check_unexploded_phantom
873 (p_batch_id => x_batch_step_rec.batch_id
874 ,p_auto_by_step => 1 -- auto by step ingredients
875 ,p_batchstep_id => x_batch_step_rec.batchstep_id -- assoc to this step
876 ,x_return_status => x_return_status);
877
878 IF x_return_status <> fnd_api.g_ret_sts_success THEN
879 RAISE error_validation;
880 END IF;
881
882 IF NVL (g_debug, gme_debug.g_log_procedure + 1) <=
883 gme_debug.g_log_procedure THEN
884 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
885 END IF;
886
887 EXCEPTION
888 --Bug#5109119
889 WHEN error_close_period THEN
890 x_return_status := FND_API.G_RET_STS_ERROR;
891 -- 7564922 - Add missing exception clause for validity status check.
892 WHEN error_validity_status THEN
893 gme_common_pvt.log_message ('GME_VALIDITY_OBSO_OR_ONHOLD');
894 x_return_status := FND_API.G_RET_STS_ERROR;
895 WHEN error_future_date THEN
896 fnd_message.set_name ('GMA', 'SY_NOFUTUREDATE');
897 fnd_msg_pub.ADD;
898 x_return_status := FND_API.G_RET_STS_ERROR;
899 WHEN error_validation THEN
900 NULL;
901 WHEN error_actual_start_date THEN
902 gme_common_pvt.log_message ('GME_STEP_START_BATCH_START_ERR');
903 x_return_status := FND_API.G_RET_STS_ERROR;
904 WHEN error_vr_dates THEN
905 x_return_status := FND_API.G_RET_STS_ERROR;
906 WHEN OTHERS THEN
907 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
908
909 IF g_debug <= gme_debug.g_log_procedure THEN
910 gme_debug.put_line ( 'Unexpected error: '
911 || g_pkg_name
912 || '.'
913 || l_api_name
914 || ': '
915 || SQLERRM);
916 END IF;
917 x_return_status := fnd_api.g_ret_sts_unexp_error;
918 END validate_step_for_release;
919
920 END gme_release_batch_step_pvt;
[Home] [Help]