1 PACKAGE BODY gme_release_batch_step_pvt AS
2 /* $Header: GMEVRLSB.pls 120.11.12000000.2 2007/03/06 21:35:32 adeshmuk 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 IF NOT gme_common_pvt.check_close_period(p_org_id => p_batch_header_rec.organization_id
353 ,p_trans_date => p_batch_step_rec.actual_start_date) THEN
354 RAISE error_close_period;
355 END IF;
356
357 /* because of step depedency types(-ve offset) some time the dependent step might get the date that can greater
358 than sysdate. so following check is introduced */
359 IF (p_batch_step_rec.actual_start_date > SYSDATE) THEN
360 RAISE error_future_date;
361 END IF;
362 /* Bug#5109119 End */
363
364 /* Get the immediate dependent steps for the current step */
365 FOR l_dep_step_rec IN cur_get_dep_steps (p_batch_step_rec.batchstep_id
366 ,p_batch_header_rec.batch_id) LOOP
367 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
368 gme_debug.put_line ( g_pkg_name
369 || '.'
370 || l_api_name
371 || 'fetched dep step '
372 || l_dep_step_rec.dep_step_id);
373 gme_debug.put_line ( g_pkg_name
374 || '.'
375 || l_api_name
376 || 'steprelease_type = '
377 || l_dep_step_rec.steprelease_type);
378 gme_debug.put_line ( g_pkg_name
379 || '.'
380 || l_api_name
381 || 'dep_type = '
382 || l_dep_step_rec.dep_type);
383 gme_debug.put_line ( g_pkg_name
384 || '.'
385 || l_api_name
386 || 'standard_delay = '
387 || l_dep_step_rec.standard_delay);
388 gme_debug.put_line ( g_pkg_name
389 || '.'
390 || l_api_name
391 || 'step_status = '
392 || l_dep_step_rec.step_status);
393 END IF;
394
395 /* If the dependent step is set to automatic release */
396 IF l_dep_step_rec.steprelease_type =
397 gme_common_pvt.g_auto_step_release THEN
398 l_batch_step_rec.batchstep_id := l_dep_step_rec.dep_step_id;
399
400 IF NOT (gme_batch_steps_dbl.fetch_row (l_batch_step_rec
401 ,l_batch_step_rec) ) THEN
402 RAISE batch_step_fetch_error;
403 END IF;
404
405 /* If the dependency is Finish To Start and their is a positive delay */
406 /* then complete the dependent step otherwise call the release API if the step */
407 /* status is pending. */
408 IF (l_dep_step_rec.dep_type =
409 gme_common_pvt.g_dep_type_finish_start)
410 AND (l_dep_step_rec.standard_delay >= 0) THEN
411 l_complete_dep_step := TRUE;
412 ELSE
413 l_complete_dep_step := FALSE;
414 END IF; /* IF (l_dep_step_rec.dep_type = gme_common_pvt.g_dep_type_finish_start) AND */
415
416 /* If the step status is pending */
417 IF (l_dep_step_rec.step_status = gme_common_pvt.g_step_pending) THEN
418 /*Bug#5183521 when step dependency is start to start, the dependent should start before the current step
419 considering the standard delay defined in dependencies.commented the ELSE and added OR condition */
420 IF (l_dep_step_rec.dep_type = gme_common_pvt.g_dep_type_finish_start AND
421 l_dep_step_rec.standard_delay < 0) OR
422 (l_dep_step_rec.dep_type = gme_common_pvt.g_dep_type_start_start) THEN
423 l_batch_step_rec.actual_start_date :=
424 p_batch_step_rec.actual_start_date
425 - (l_dep_step_rec.standard_delay / 24);
426 /*ELSIF (l_dep_step_rec.dep_type =
427 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 END IF;
432 /* dep_type = gme_common_pvt.g_dep_type_finish_start */
433
434 l_in_batch_step_rec := l_batch_step_rec;
435 /* Bug#5109119 when l_complete_dep_step is TRUE call complete_step directly, no need to call release_step */
436 IF NOT l_complete_dep_step THEN
437 release_step_recursive
438 (p_batch_step_rec => l_in_batch_step_rec
439 ,p_batch_header_rec => p_batch_header_rec
440 ,x_batch_step_rec => l_batch_step_rec
441 ,x_exception_material_tbl => x_exception_material_tbl
442 ,x_return_status => l_return_status);
443
444 IF l_return_status NOT IN
445 (fnd_api.g_ret_sts_success
446 ,gme_common_pvt.g_exceptions_err) THEN
447 x_return_status := l_return_status;
448 RAISE dep_step_rel_error;
449 END IF;
450
451 IF l_return_status = gme_common_pvt.g_exceptions_err THEN
452 x_return_status := gme_common_pvt.g_exceptions_err;
453 END IF;
454 END IF; /* IF NOT l_complete_dep_step THEN */
455 END IF; /* IF (l_dep_step_rec.step_status = gme_common_pvt.g_step_pending) */
456
457 IF l_complete_dep_step THEN
458 l_batch_step_rec.actual_cmplt_date :=
459 p_batch_step_rec.actual_start_date
460 - (l_dep_step_rec.standard_delay / 24);
461
462 --Sunitha Ch. bug#5488991 assigning the completion date to the start only when it is null
463 IF l_batch_step_rec.actual_start_date IS NULL THEN
464 l_batch_step_rec.actual_start_date :=
465 l_batch_step_rec.actual_cmplt_date;
466 ELSE
467 IF l_batch_step_rec.actual_cmplt_date < l_batch_step_rec.actual_start_date THEN
468 l_batch_step_rec.actual_cmplt_date :=
469 l_batch_step_rec.actual_start_date;
470 END IF;
471 END IF;
472 l_in_batch_step_rec := l_batch_step_rec;
473 gme_complete_batch_step_pvt.complete_step_recursive
474 (p_batch_step_rec => l_in_batch_step_rec
475 ,p_batch_header_rec => p_batch_header_rec
476 ,x_return_status => l_return_status
477 ,x_batch_step_rec => l_batch_step_rec
478 ,x_exception_material_tbl => x_exception_material_tbl);
479
480 IF l_return_status NOT IN
481 (fnd_api.g_ret_sts_success
482 ,gme_common_pvt.g_exceptions_err) THEN
483 x_return_status := l_return_status;
484 RAISE dep_step_cmpl_error;
485 END IF;
486
487 IF l_return_status = gme_common_pvt.g_exceptions_err THEN
488 x_return_status := gme_common_pvt.g_exceptions_err;
489 END IF;
490 END IF; -- IF l_complete_dep_step THEN
491 END IF; -- IF l_dep_step_rec.steprelease_type = gme_common_pvt.g_auto_step_release
492 END LOOP; /* FOR l_dep_step_rec IN Cur_get_dep_steps */
493
494
495 /* Bug#5109119 l_first_step_start_date will preserve the min(step dates) between the recursive calls.
496 we compare this date with the actual start date of the current step and if this is less than then
497 change
498 */
499 IF l_first_step_start_date IS NULL OR
500 l_first_step_start_date > p_batch_step_rec.actual_start_date THEN
501 l_first_step_start_date := p_batch_step_rec.actual_start_date;
502 END IF;
503
504 IF NVL (g_debug, gme_debug.g_log_procedure + 1) <=
505 gme_debug.g_log_procedure THEN
506 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
507 END IF;
508 EXCEPTION
509 --Bug#5109119 Begin
510 WHEN error_close_period THEN
511 x_return_status := FND_API.G_RET_STS_ERROR;
512 WHEN error_future_date THEN
513 fnd_message.set_name ('GMA', 'SY_NOFUTUREDATE');
514 fnd_msg_pub.ADD;
515 x_return_status := FND_API.G_RET_STS_ERROR;
516 --Bug#5109119 End
517 WHEN batch_step_fetch_error THEN
518 gme_common_pvt.log_message ('GME_UNEXPECTED_ERROR', 'ERROR'
519 ,SQLERRM);
520 x_return_status := fnd_api.g_ret_sts_unexp_error;
521 WHEN dep_step_rel_error OR dep_step_cmpl_error THEN
522 NULL;
523 WHEN OTHERS THEN
524 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
525
526 IF g_debug <= gme_debug.g_log_procedure THEN
527 gme_debug.put_line ( 'Unexpected error: '
528 || g_pkg_name
529 || '.'
530 || l_api_name
531 || ': '
532 || SQLERRM);
533 END IF;
534
535 x_return_status := fnd_api.g_ret_sts_unexp_error;
536 END process_dependent_steps;
537
538 /*===========================================================================================
539 Procedure
540 release_step_line
541 Description
542 This procedure releases the step and updates actual dates for activity and resource.
543 Parameters
544 p_batch_step_rec Input Batch Step Line
545 p_batch_header_rec Batch Header that step belongs to
546 x_batch_step_rec Output Batch Step Line
547 x_return_status outcome of the API call
548 S - Success
549 E - Error
550 U - Unexpected error
551 History
552
553 =============================================================================================*/
554 PROCEDURE release_step_line (
555 p_batch_step_rec IN gme_batch_steps%ROWTYPE
556 ,x_batch_step_rec OUT NOCOPY gme_batch_steps%ROWTYPE
557 ,x_exception_material_tbl IN OUT NOCOPY gme_common_pvt.exceptions_tab
558 ,x_return_status OUT NOCOPY VARCHAR2)
559 IS
560 l_api_name CONSTANT VARCHAR2 (30) := 'release_step_line';
561 l_return_status VARCHAR2 (1);
562 l_batch_step_rec gme_batch_steps%ROWTYPE;
563 l_in_batch_step_rec gme_batch_steps%ROWTYPE;
564 batch_step_upd_err EXCEPTION;
565 BEGIN
566 IF NVL (g_debug, gme_debug.g_log_procedure + 1) <=
567 gme_debug.g_log_procedure THEN
568 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
569 || l_api_name);
570 gme_debug.put_line ( g_pkg_name
571 || '.'
572 || l_api_name
573 || ' Release step line batchstep_id='
574 || p_batch_step_rec.batchstep_id);
575 END IF;
576
577 x_return_status := fnd_api.g_ret_sts_success;
578 -- Each time this is called, p_batch_step_rec has already been retrieved from DB... has all
579 -- latest data and in addition has the actual start date calculated and set
580 x_batch_step_rec := p_batch_step_rec;
581 /* Update the Batch Step Status to WIP */
582 x_batch_step_rec.step_status := gme_common_pvt.g_step_wip;
583
584 -- Update the batch step
585 IF NOT (gme_batch_steps_dbl.update_row (x_batch_step_rec) ) THEN
586 RAISE batch_step_upd_err;
587 END IF;
588
589 -- Update WHO columns for output structure
590 x_batch_step_rec.last_updated_by := gme_common_pvt.g_user_ident;
591 x_batch_step_rec.last_update_date := gme_common_pvt.g_timestamp;
592 x_batch_step_rec.last_update_login := gme_common_pvt.g_login_id;
593
594 -- Update activity start date
595 -- Does not factor in offset
596 UPDATE gme_batch_step_activities
597 SET actual_start_date = x_batch_step_rec.actual_start_date
598 ,last_updated_by = gme_common_pvt.g_user_ident
599 ,last_update_date = gme_common_pvt.g_timestamp
600 ,last_update_login = gme_common_pvt.g_login_id
601 WHERE batchstep_id = x_batch_step_rec.batchstep_id;
602
603 -- Update resource start date
604 -- Does not factor in offset
605 UPDATE gme_batch_step_resources
606 SET actual_start_date = x_batch_step_rec.actual_start_date
607 ,last_updated_by = gme_common_pvt.g_user_ident
608 ,last_update_date = gme_common_pvt.g_timestamp
609 ,last_update_login = gme_common_pvt.g_login_id
610 WHERE batchstep_id = x_batch_step_rec.batchstep_id;
611
612 IF NVL (g_debug, gme_debug.g_log_procedure + 1) <=
613 gme_debug.g_log_procedure THEN
614 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
615 END IF;
616 EXCEPTION
617 WHEN batch_step_upd_err THEN
618 gme_common_pvt.log_message ('GME_UNEXPECTED_ERROR', 'ERROR'
619 ,SQLERRM);
620 x_return_status := fnd_api.g_ret_sts_unexp_error;
621 WHEN OTHERS THEN
622 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
623
624 IF g_debug <= gme_debug.g_log_procedure THEN
625 gme_debug.put_line ( 'Unexpected error: '
626 || g_pkg_name
627 || '.'
628 || l_api_name
629 || ': '
630 || SQLERRM);
631 END IF;
632
633 x_return_status := fnd_api.g_ret_sts_unexp_error;
634 END release_step_line;
635
636 PROCEDURE release_step_ingredients (
637 p_batch_step_rec IN gme_batch_steps%ROWTYPE
638 ,p_update_inv_ind IN VARCHAR2
639 ,x_exception_material_tbl IN OUT NOCOPY gme_common_pvt.exceptions_tab
640 ,x_return_status OUT NOCOPY VARCHAR2)
641 IS
642 CURSOR cur_step_ingredients (v_batchstep_id NUMBER)
643 IS
644 SELECT matl.*
645 FROM gme_material_details matl, gme_batch_step_items item
646 WHERE item.batchstep_id = v_batchstep_id
647 AND item.material_detail_id = matl.material_detail_id
648 AND matl.line_type = gme_common_pvt.g_line_type_ing
649 AND matl.release_type = gme_common_pvt.g_mtl_autobystep_release;
650
651 l_api_name CONSTANT VARCHAR2 (30) := 'release_step_ingredients';
652 l_return_status VARCHAR2 (1);
653 l_matl_dtl_rec gme_material_details%ROWTYPE;
654 l_matl_dtl_tab gme_common_pvt.material_details_tab;
655 l_consume BOOLEAN;
656 error_process_ing EXCEPTION;
657 BEGIN
658 IF NVL (g_debug, gme_debug.g_log_procedure + 1) <=
659 gme_debug.g_log_procedure THEN
660 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
661 || l_api_name);
662 gme_debug.put_line ( g_pkg_name
663 || '.'
664 || l_api_name
665 || ' Releasing ingredients for step_id='
666 || p_batch_step_rec.batchstep_id);
667 END IF;
668
669 /* Set the return status to success initially */
670 x_return_status := fnd_api.g_ret_sts_success;
671
672 -- retrieve all autobystep ingredients associated to the step...
673 OPEN cur_step_ingredients (p_batch_step_rec.batchstep_id);
674
675 FETCH cur_step_ingredients
676 BULK COLLECT INTO l_matl_dtl_tab;
677
678 CLOSE cur_step_ingredients;
679
680 FOR i IN 1 .. l_matl_dtl_tab.COUNT LOOP
681 l_matl_dtl_rec := l_matl_dtl_tab (i);
682 l_consume := TRUE;
683
684 gme_release_batch_pvt.process_ingredient
685 (p_material_detail_rec => l_matl_dtl_rec
686 ,p_consume => l_consume
687 ,p_trans_date => p_batch_step_rec.actual_start_date
688 ,p_update_inv_ind => p_update_inv_ind
689 ,x_exception_material_tbl => x_exception_material_tbl
690 ,x_return_status => l_return_status);
691
692 IF l_return_status NOT IN
693 (fnd_api.g_ret_sts_success, gme_common_pvt.g_exceptions_err) THEN
694 x_return_status := l_return_status;
695 RAISE error_process_ing;
696 END IF;
697
698 IF l_return_status = gme_common_pvt.g_exceptions_err THEN
699 x_return_status := gme_common_pvt.g_exceptions_err;
700 END IF;
701 END LOOP;
702
703 IF NVL (g_debug, gme_debug.g_log_procedure + 1) <=
704 gme_debug.g_log_procedure THEN
705 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
706 END IF;
707 EXCEPTION
708 WHEN error_process_ing THEN
709 NULL;
710 WHEN OTHERS THEN
711 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
712
713 IF g_debug <= gme_debug.g_log_procedure THEN
714 gme_debug.put_line ( 'Unexpected error: '
715 || g_pkg_name
716 || '.'
717 || l_api_name
718 || ': '
719 || SQLERRM);
720 END IF;
721
722 x_return_status := fnd_api.g_ret_sts_unexp_error;
723 END release_step_ingredients;
724
725 PROCEDURE validate_step_for_release (p_batch_header_rec IN gme_batch_header%ROWTYPE
726 ,p_batch_step_rec IN gme_batch_steps%ROWTYPE
727 ,x_batch_step_rec OUT NOCOPY gme_batch_steps%ROWTYPE
728 ,x_return_status OUT NOCOPY VARCHAR2) IS
729
730 l_api_name CONSTANT VARCHAR2 (30) := 'validate_step_for_release';
731 CURSOR cur_validity_rule(v_recipe_validity_rule_id NUMBER)
732 IS
733 SELECT *
734 FROM gmd_recipe_validity_rules
735 WHERE recipe_validity_rule_id = v_recipe_validity_rule_id;
736
737 CURSOR cur_validity_status_type(v_validity_rule_status VARCHAR2)
738 IS
739 SELECT status_type
740 FROM gmd_status
741 WHERE status_code=v_validity_rule_status;
742
743 l_validity_rule gmd_recipe_validity_rules%ROWTYPE;
744 l_status_type GMD_STATUS.status_type%TYPE;
745 error_vr_not_found EXCEPTION;
746 error_validity_status EXCEPTION;
747 error_future_date EXCEPTION;
748 error_validation EXCEPTION;
749 error_actual_start_date EXCEPTION;
750 --Bug#5109119
751 error_close_period EXCEPTION;
752 error_vr_dates EXCEPTION;
753 BEGIN
754 IF NVL (g_debug, gme_debug.g_log_procedure + 1) <=
755 gme_debug.g_log_procedure THEN
756 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
757 || l_api_name);
758 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' batchstep_id = '||p_batch_step_rec.batchstep_id);
759 END IF;
760
761 x_return_status := fnd_api.g_ret_sts_success;
762
763 -- set output structure
764 x_batch_step_rec := p_batch_step_rec;
765
766 -- actual start date is filled in for both p_batch_header_rec and p_batch_step_rec
767
768 -- set actual start date if it's not passed
769 IF p_batch_step_rec.actual_start_date IS NULL THEN
770 x_batch_step_rec.actual_start_date := SYSDATE;
771 ELSE -- user passed in an actual start date; ensure it's not in the future
772 IF (p_batch_step_rec.actual_start_date > SYSDATE) THEN
773 RAISE error_future_date;
774 ELSIF p_batch_header_rec.batch_status = gme_common_pvt.g_batch_wip THEN
775 IF p_batch_step_rec.actual_start_date < p_batch_header_rec.actual_start_date THEN
776 RAISE error_actual_start_date;
777 END IF;
778 END IF;
779 x_batch_step_rec.actual_start_date := p_batch_step_rec.actual_start_date;
780 END IF;
781 --Sunith ch.5404329 check validity rule if it's not NULL; it would be NULL in case of LCF
782 IF p_batch_header_rec.recipe_validity_rule_id IS NOT NULL THEN
783 OPEN cur_validity_rule(p_batch_header_rec.recipe_validity_rule_id);
784 FETCH cur_validity_rule INTO l_validity_rule;
785 CLOSE cur_validity_rule;
786
787 IF l_validity_rule.recipe_validity_rule_id IS NULL THEN -- not found
788 RAISE error_vr_not_found;
789 ELSE
790 -- following prevents user from releasing a pending batch
791 -- if validity rule is ON_HOLD or OBSOLETE.
792 OPEN cur_validity_status_type(l_validity_rule.validity_rule_status);
793 FETCH cur_validity_status_type INTO l_status_type;
794 CLOSE cur_validity_status_type;
795
796 IF l_status_type IN ('1000' ,'800') THEN
797 RAISE error_validity_status;
798 END IF;
799 END IF; -- IF l_validity_rule.recipe_validity_rule_id IS NULL
800
801 /* IF l_validity_rule.start_date > x_batch_header_rec.actual_start_date OR
802 (l_validity_rule.end_date IS NOT NULL AND
803 l_validity_rule.end_date < x_batch_header_rec.actual_start_date) THEN
804 RAISE error_vr_dates;
805 END IF;*/
806 --sunitha ch. Bug 5336007 aded call to check_validity_rule_dates and passed p_validate_plan_dates_ind=1
807 --to validate planned start date against validate rule dates
808 IF NOT gme_common_pvt.check_validity_rule_dates (
809 p_validity_rule_id => p_batch_header_rec.recipe_validity_rule_id
810 ,p_start_date => p_batch_header_rec.actual_start_date
811 ,p_cmplt_date => p_batch_header_rec.actual_cmplt_date
812 ,p_batch_header_rec => p_batch_header_rec
813 ,p_validate_plan_dates_ind => 1) THEN
814 x_return_status := fnd_api.g_ret_sts_error;
815 RAISE error_vr_dates;
816 END IF;
817 -- End Bug 5336007
818 END IF; -- IF p_batch_header_rec.recipe_validity_rule_id IS NOT NULL
819 IF NOT gme_common_pvt.check_close_period(p_org_id => p_batch_header_rec.organization_id
820 ,p_trans_date => x_batch_step_rec.actual_start_date) THEN
821 RAISE error_close_period;
822 END IF;
823
824 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
825 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'));
826 END IF;
827
828 -- Enforce Step Dependency Checks
829 IF p_batch_header_rec.enforce_step_dependency = 1 THEN
830 -- validate dependent step status and dates
831 gme_complete_batch_step_pvt.validate_dependent_steps
832 (p_batch_id => x_batch_step_rec.batch_id
833 ,p_step_id => x_batch_step_rec.batchstep_id
834 ,p_step_actual_start_date => x_batch_step_rec.actual_start_date
835 ,x_return_status => x_return_status);
836
837 IF x_return_status <> fnd_api.g_ret_sts_success THEN
838 RAISE error_validation;
839 END IF;
840 END IF;
841
842 -- will check any auto by step associated to the step; phantom batches - all release types checked
843 gme_release_batch_pvt.check_unexploded_phantom
844 (p_batch_id => x_batch_step_rec.batch_id
845 ,p_auto_by_step => 1 -- auto by step ingredients
846 ,p_batchstep_id => x_batch_step_rec.batchstep_id -- assoc to this step
847 ,x_return_status => x_return_status);
848
849 IF x_return_status <> fnd_api.g_ret_sts_success THEN
850 RAISE error_validation;
851 END IF;
852
853 IF NVL (g_debug, gme_debug.g_log_procedure + 1) <=
854 gme_debug.g_log_procedure THEN
855 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
856 END IF;
857
858 EXCEPTION
859 --Bug#5109119
860 WHEN error_close_period THEN
861 x_return_status := FND_API.G_RET_STS_ERROR;
862 WHEN error_future_date THEN
863 fnd_message.set_name ('GMA', 'SY_NOFUTUREDATE');
864 fnd_msg_pub.ADD;
865 x_return_status := FND_API.G_RET_STS_ERROR;
866 WHEN error_validation THEN
867 NULL;
868 WHEN error_actual_start_date THEN
869 gme_common_pvt.log_message ('GME_STEP_START_BATCH_START_ERR');
870 x_return_status := FND_API.G_RET_STS_ERROR;
871 WHEN error_vr_dates THEN
872 x_return_status := FND_API.G_RET_STS_ERROR;
873 WHEN OTHERS THEN
874 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
875
876 IF g_debug <= gme_debug.g_log_procedure THEN
877 gme_debug.put_line ( 'Unexpected error: '
878 || g_pkg_name
879 || '.'
880 || l_api_name
881 || ': '
882 || SQLERRM);
883 END IF;
884 x_return_status := fnd_api.g_ret_sts_unexp_error;
885 END validate_step_for_release;
886
887 END gme_release_batch_step_pvt;
[Home] [Help]