1 PACKAGE BODY gme_complete_batch_step_pvt AS
2 /* $Header: GMEVCMSB.pls 120.21.12020000.2 2012/07/26 15:32:30 gmurator ship $ */
3
4 G_DEBUG VARCHAR2(5) := FND_PROFILE.VALUE('AFLOG_LEVEL');
5 g_pkg_name CONSTANT VARCHAR2 (30) := 'gme_complete_batch_step_pvt';
6
7 /*===========================================================================================
8 Procedure
9 complete_step
10 Description
11 This particular procedure call completes the batch steps.
12 Parameters
13 p_batch_step_rec The batch step row to complete
14 p_batch_header_rec The batch he
15 x_return_status outcome of the API call
16 S - Success
17 E - Error
18 U - Unexpected error
19 X - Unallocated Items Found
20
21 History
22 =======
23 G. Muratore 14-MAY-09 Bug 8472384
24 Make a call to purge_batch_exceptions to remove any pending reservations
25 when the batch is being completed.
26
27 G. Muratore 09-Nov-11 Bug 12742260
28 Purge the data only if there are no exceptions.
29 =============================================================================================*/
30
31 PROCEDURE complete_step
32 (p_batch_step_rec IN GME_BATCH_STEPS%ROWTYPE
33 ,p_batch_header_rec IN gme_batch_header%ROWTYPE
34 ,x_batch_step_rec OUT NOCOPY GME_BATCH_STEPS%ROWTYPE
35 ,x_exception_material_tbl IN OUT NOCOPY gme_common_pvt.exceptions_tab
36 ,x_return_status OUT NOCOPY VARCHAR2) IS
37
38 CURSOR Cur_step_count(v_batch_id NUMBER) IS
39 SELECT count(1)
40 FROM GME_BATCH_STEPS
41 WHERE batch_id = v_batch_id
42 AND step_status < gme_common_pvt.g_step_completed
43 AND rownum = 1;
44
45 l_api_name CONSTANT VARCHAR2 (30) := 'complete_step';
46
47 l_batch_header_rec gme_batch_header%ROWTYPE;
48 l_in_batch_header_rec gme_batch_header%ROWTYPE;
49 l_batch_step_rec gme_batch_steps%ROWTYPE;
50 l_return_status VARCHAR2(1);
51 l_step_count NUMBER;
52
53 error_release_batch EXCEPTION;
54 error_complete_step_rec EXCEPTION;
55 error_complete_batch EXCEPTION;
56 error_validation EXCEPTION;
57 error_fetch EXCEPTION;
58
59 -- Bug 8472384 - Add new exception
60 purge_exception_err EXCEPTION;
61
62 BEGIN
63
64 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
65 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
66 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' Complete step batchstep_id='||p_batch_step_rec.batchstep_id);
67 END IF;
68
69 x_return_status := FND_API.G_RET_STS_SUCCESS;
70
71 l_batch_step_rec := p_batch_step_rec;
72 l_batch_header_rec := p_batch_header_rec;
73
74 -- if the step status is pending, call release step first... don't need to worry
75 -- about calling release batch if the batch is pending because release step will take
76 -- care of this; check for step control batch and not a phantom done in pub
77 IF p_batch_step_rec.step_status = gme_common_pvt.g_step_pending THEN
78 -- actual start date already populated in p_batch_step_rec
79 gme_release_batch_step_pvt.release_step
80 (p_batch_step_rec => p_batch_step_rec
81 ,p_batch_header_rec => p_batch_header_rec
82 ,x_batch_step_rec => l_batch_step_rec
83 ,x_exception_material_tbl => x_exception_material_tbl
84 ,x_return_status => l_return_status);
85
86 IF l_return_status NOT IN (FND_API.G_RET_STS_SUCCESS, gme_common_pvt.g_exceptions_err) THEN
87 x_return_status := l_return_status;
88 RAISE error_release_batch;
89 END IF;
90
91 IF l_return_status = gme_common_pvt.g_exceptions_err THEN
92 x_return_status := gme_common_pvt.g_exceptions_err;
93 END IF;
94
95 -- re-retrieve the batch header if the batch status was pending
96 IF (p_batch_header_rec.batch_status = gme_common_pvt.g_batch_pending) THEN
97 IF NOT gme_batch_header_dbl.fetch_row(l_batch_header_rec, l_batch_header_rec) THEN
98 RAISE error_fetch;
99 END IF;
100 END IF;
101
102 END IF; /* IF p_batch_step_rec.step_status = gme_common_pvt.g_step_pending */
103
104 -- Bug 8252288 - Added last parameter to reinstate behavior before 6348353 was implemented.
105 complete_step_recursive
106 (p_batch_step_rec => l_batch_step_rec
107 ,p_batch_header_rec => l_batch_header_rec
108 ,x_batch_step_rec => x_batch_step_rec
109 ,x_exception_material_tbl => x_exception_material_tbl
110 ,x_return_status => l_return_status
111 ,p_quality_override => TRUE);
112
113 IF l_return_status NOT IN (FND_API.G_RET_STS_SUCCESS, gme_common_pvt.g_exceptions_err) THEN
114 x_return_status := l_return_status;
115 RAISE error_complete_step_rec;
116 END IF;
117
118 IF l_return_status = gme_common_pvt.g_exceptions_err THEN
119 x_return_status := gme_common_pvt.g_exceptions_err;
120 END IF;
121
122 /* If the step controls batch status profile is set then to complete the batch */
123 /* if all the steps are complete and this is not a phantom batch */
124
125 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
126 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' step controls batch= '||gme_common_pvt.g_step_controls_batch_sts_ind);
127 END IF;
128
129 IF (gme_common_pvt.g_step_controls_batch_sts_ind = 1) AND (l_batch_header_rec.parentline_id IS NULL) AND
130 (l_batch_header_rec.batch_status <> gme_common_pvt.g_batch_completed) THEN
131 /* Get the count of the number of steps less than complete for this batch */
132
133 OPEN Cur_step_count(l_batch_header_rec.batch_id);
134 FETCH Cur_step_count INTO l_step_count;
135 CLOSE Cur_step_count;
136
137 /* If all the steps are complete or closed in the batch then call complete batch */
138 IF (l_step_count = 0) THEN
139 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
140 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' calling complete batch because step controls batch and all steps complete');
141 END IF;
142
143 l_in_batch_header_rec := l_batch_header_rec;
144 l_in_batch_header_rec.actual_cmplt_date := x_batch_step_rec.actual_cmplt_date;
145
146 -- call complete batch validation to make sure batch is in position to be completed
147 -- can't do this in pub because there's no way to know if other steps may be
148 -- completed in the process of this being completed
149 gme_complete_batch_pvt.validate_batch_for_complete
150 (p_batch_header_rec => l_in_batch_header_rec
151 ,x_batch_header_rec => l_batch_header_rec
152 ,x_return_status => x_return_status);
153
154 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
155 RAISE error_validation;
156 END IF;
157
158 l_in_batch_header_rec := l_batch_header_rec;
159 gme_complete_batch_pvt.complete_batch
160 (p_batch_header_rec => l_in_batch_header_rec
161 ,x_exception_material_tbl => x_exception_material_tbl
162 ,x_batch_header_rec => l_batch_header_rec
163 ,x_return_status => l_return_status);
164
165 IF l_return_status NOT IN (FND_API.G_RET_STS_SUCCESS, gme_common_pvt.g_exceptions_err) THEN
166 x_return_status := l_return_status;
167 RAISE error_complete_batch;
168 END IF;
169
170 IF l_return_status = gme_common_pvt.g_exceptions_err THEN
171 x_return_status := gme_common_pvt.g_exceptions_err;
172 ELSE
173 -- Bug 12742260 - Purge the data only if there are no exceptions.
174 -- Bug 8472384 - Delete all remaining reservations including for phantom batches.
175 gme_cancel_batch_pvt.purge_batch_exceptions (p_batch_header_rec => l_batch_header_rec
176 ,p_delete_reservations => 'T'
177 ,p_recursive => 'R'
178 ,x_return_status => l_return_status);
179
180 IF l_return_status <> fnd_api.g_ret_sts_success THEN
181 IF (g_debug <= gme_debug.g_log_procedure) THEN
182 gme_debug.put_line ( g_pkg_name
183 || '.'
184 || l_api_name
185 || ':'
186 || 'purge_exception_err');
187 END IF;
188 RAISE purge_exception_err;
189 END IF;
190 END IF;
191 END IF; /* IF l_step_count = 0 */
192 END IF; /* IF (gme_common_pvt.g_step_controls_batch_sts_ind = 'Y') AND */
193
194 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
195 gme_debug.put_line('Exiting api '||g_pkg_name||'.'||l_api_name);
196 END IF;
197
198 EXCEPTION
199 WHEN error_release_batch OR error_complete_batch OR
200 error_complete_step_rec OR error_validation OR purge_exception_err THEN
201 NULL;
202 WHEN error_fetch THEN
203 gme_common_pvt.log_message ('GME_UNEXPECTED_ERROR', 'ERROR'
204 ,SQLERRM);
205 x_return_status := fnd_api.g_ret_sts_unexp_error;
206 WHEN OTHERS THEN
207 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
208 IF g_debug <= gme_debug.g_log_procedure THEN
209 gme_debug.put_line ('Unexpected error: '||g_pkg_name||'.'||l_api_name||': '||SQLERRM);
210 END IF;
211 x_return_status := FND_API.g_ret_sts_unexp_error;
212 END complete_step;
213
214 PROCEDURE complete_step_recursive
215 (p_batch_step_rec IN gme_batch_steps%ROWTYPE
216 ,p_batch_header_rec IN gme_batch_header%ROWTYPE
217 ,x_batch_step_rec OUT NOCOPY gme_batch_steps%ROWTYPE
218 ,x_exception_material_tbl IN OUT NOCOPY gme_common_pvt.exceptions_tab
219 ,x_return_status OUT NOCOPY VARCHAR2
220 ,p_quality_override IN BOOLEAN := FALSE) IS --Bug#6348353
221
222 l_api_name CONSTANT VARCHAR2 (30) := 'complete_step_recursive';
223
224 l_in_batch_step_rec gme_batch_steps%ROWTYPE;
225 l_return_status VARCHAR2 (1);
226 l_msg_count NUMBER;
227 l_msg_stack VARCHAR2 (2000);
228 l_lock_status VARCHAR2(1);
229 l_locked_by_status VARCHAR2(1);
230 l_lock_allowed VARCHAR2(1);
231
232 step_cmpl_closed EXCEPTION;
233 cmpl_step_line_error EXCEPTION;
234 cmpl_step_prod_error EXCEPTION;
235 update_step_qty_error EXCEPTION;
236 error_process_dep_steps EXCEPTION;
237 gmo_lock_error EXCEPTION;
238 error_quality_status EXCEPTION; --Bug#6348353
239
240 BEGIN
241 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
242 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
243 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' complete step recursive batch_step_id='||p_batch_step_rec.batchstep_id);
244 END IF;
245
246 /* Set the return status to success initially */
247 x_return_status := FND_API.G_RET_STS_SUCCESS;
248
249 x_batch_step_rec := p_batch_step_rec;
250
251 /* Exit the recursive loop if the step is already released, completed or closed */
252 IF x_batch_step_rec.step_status IN (gme_common_pvt.g_step_completed
253 ,gme_common_pvt.g_step_closed) THEN
254 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
255 gme_debug.put_line (g_pkg_name||'.'||l_api_name||'step is completed or closed; returning');
256 END IF;
257 RAISE step_cmpl_closed;
258 END IF;
259
260 -- Bug 9478698 - Call GMO only if enhanced_pi_ind is activated.
261 IF p_batch_header_rec.enhanced_pi_ind = 'Y' THEN
262 -- Pawan Kumar added for bug 5034336
263 -- check for batch step lock status from gmo
264 gmo_vbatch_grp.GET_ENTITY_LOCK_STATUS (
265 P_API_VERSION => 1.0,
266 P_INIT_MSG_LIST => FND_API.G_FALSE,
267 P_COMMIT => FND_API.G_FALSE,
268 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
269 X_RETURN_STATUS => x_return_status ,
270 X_MSG_COUNT => l_msg_count,
271 X_MSG_DATA => l_msg_stack,
272 P_ENTITY_NAME => 'OPERATION',
273 P_ENTITY_KEY => x_batch_step_rec.batchstep_id,
274 P_REQUESTER => gme_common_pvt.g_user_ident,
275 X_LOCK_STATUS => l_lock_status,
276 X_LOCKED_BY_STATUS => l_locked_by_status,
277 X_LOCK_ALLOWED => l_lock_allowed);
278
279 IF x_return_status <> fnd_api.g_ret_sts_success THEN
280 RAISE gmo_lock_error;
281 END IF;
282
283 IF (g_debug <= gme_debug.g_log_procedure) THEN
284 gme_debug.put_line ( g_pkg_name
285 || '.'
286 || l_api_name
287 || ':'
288 || 'from gmo the lock_status='
289 || l_lock_status);
290 END IF;
291
292 IF l_lock_status = 'Y' THEN
293 gme_common_pvt.log_message ('GME_STEP_LOCK_ERROR');
294 RAISE gmo_lock_error;
295 END IF;
296 END IF;
297
298
299 /* If this procedure is invoked while completing the entire batch */
300 /* then there is no need to go through the recursive procedure as */
301 /* the complete batch call completes all the steps */
302 IF p_batch_header_rec.batch_status <> gme_common_pvt.g_batch_completed THEN
303 gme_release_batch_step_pvt.process_dependent_steps
304 (p_batch_step_rec => p_batch_step_rec
305 ,p_batch_header_rec => p_batch_header_rec
306 ,x_exception_material_tbl => x_exception_material_tbl
307 ,x_return_status => l_return_status);
308
309 IF l_return_status NOT IN (FND_API.G_RET_STS_SUCCESS, gme_common_pvt.g_exceptions_err) THEN
310 x_return_status := l_return_status;
311 RAISE error_process_dep_steps;
312 END IF;
313
314 IF l_return_status = gme_common_pvt.g_exceptions_err THEN
315 x_return_status := gme_common_pvt.g_exceptions_err;
316 END IF;
317 END IF; -- IF p_batch_header_rec.batch_status <> gme_common_pvt.g_batch_completed THEN
318
319 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
320 gme_debug.put_line (g_pkg_name||'.'||l_api_name||'Calling complete step line to create product transactions...');
321 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' for step = '|| x_batch_step_rec.batchstep_id);
322 END IF;
323
324 --Bug#6348353 Adding the below validation
325 -- if quality is not complete cannot complete step
326 IF (p_batch_step_rec.quality_status NOT IN (1,4,6) AND
327 p_quality_override = FALSE) THEN
328 RAISE error_quality_status;
329 END IF;
330
331 -- Bug 7475553 - Initialize step dates properly.
332 -- This code taken from 11i File GMEVCTSB.pls procedure certify_step_line.
333 -- If the batch status is certified, then this indicates that the user
334 -- is performing a full batch certification. That is, the call to this
335 -- function is via a batch certification and not the certification of the step.
336 l_in_batch_step_rec := x_batch_step_rec;
337 IF p_batch_header_rec.batch_status = gme_common_pvt.g_batch_completed THEN
338 IF l_in_batch_step_rec.actual_cmplt_date IS NULL THEN
339 l_in_batch_step_rec.actual_cmplt_date := p_batch_header_rec.actual_cmplt_date;
340 END IF;
341 /* If the Batch Step has not yet been Released then the Actual */
342 /* Start Date must also be assigned here. It will be set to the */
343 /* actual completion date of the batch. */
344 IF p_batch_step_rec.step_status < gme_common_pvt.g_step_wip THEN
345 l_in_batch_step_rec.actual_start_date := p_batch_header_rec.actual_cmplt_date;
346 END IF;
347 END IF;
348
349 -- Bug 7475553 - Use l_in_batch_step_rec to make sure dates are passed in properly.
350 complete_step_line
351 (p_batch_step_rec => l_in_batch_step_rec
352 ,x_batch_step_rec => x_batch_step_rec
353 ,x_exception_material_tbl => x_exception_material_tbl
354 ,x_return_status => l_return_status);
355
356 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
357 x_return_status := l_return_status;
358 RAISE cmpl_step_line_error;
359 END IF;
360
361 complete_step_material
362 (p_batch_step_rec => x_batch_step_rec
363 ,p_update_inv_ind => p_batch_header_rec.update_inventory_ind
364 ,x_exception_material_tbl => x_exception_material_tbl
365 ,x_return_status => l_return_status);
366
367 IF l_return_status NOT IN (FND_API.G_RET_STS_SUCCESS, gme_common_pvt.g_exceptions_err) THEN
368 x_return_status := l_return_status;
369 RAISE cmpl_step_prod_error;
370 END IF;
371
372 IF l_return_status = gme_common_pvt.g_exceptions_err THEN
373 x_return_status := gme_common_pvt.g_exceptions_err;
374 END IF;
375
376 /* Invoke the update step qty API to update the step quantities and the */
377 /* quantities of the succeeding steps */
378 l_in_batch_step_rec := x_batch_step_rec;
379 gme_update_step_qty_pvt.update_step_qty
380 (p_batch_step_rec => l_in_batch_step_rec
381 ,x_message_count => l_msg_count
382 ,x_message_list => l_msg_stack
383 ,x_return_status => l_return_status
384 ,x_batch_step_rec => x_batch_step_rec);
385
386 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
387 RAISE update_step_qty_error;
388 END IF;
389
390 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
391 gme_debug.put_line('Exiting api '||g_pkg_name||'.'||l_api_name);
392 END IF;
393
394 EXCEPTION
395 WHEN update_step_qty_error THEN
396 x_return_status := l_return_status;
397 WHEN step_cmpl_closed OR cmpl_step_line_error OR cmpl_step_prod_error OR error_process_dep_steps THEN
398 NULL;
399 WHEN gmo_lock_error THEN
400 IF (g_debug <= gme_debug.g_log_procedure) THEN
401 gme_debug.put_line ( g_pkg_name
402 || '.'
403 || l_api_name
404 || ':'
405 || 'GMO_LOCK_ERROR.');
406
407 END IF;
408 x_return_status := fnd_api.g_ret_sts_error;
409 WHEN error_quality_status THEN --Bug#6348353
410 gme_common_pvt.log_message('GME_QUALITY_NOT_COMPLETE'
411 ,'STEP_NO', x_batch_step_rec.batchstep_no
412 ,'BATCH_NO', p_batch_header_rec.batch_no);
413 x_return_status := fnd_api.g_ret_sts_error;
414 WHEN OTHERS THEN
415 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
416 IF g_debug <= gme_debug.g_log_procedure THEN
417 gme_debug.put_line ('Unexpected error: '||g_pkg_name||'.'||l_api_name||': '||SQLERRM);
418 END IF;
419 x_return_status := FND_API.g_ret_sts_unexp_error;
420 END complete_step_recursive;
421
422 /*===========================================================================================
423 Procedure
424 complete_step_line
425 Description
426 This particular procedure is used to complete the step and updates actual dates for activity and resource.
427 Parameters
428 p_batch_step_rec Batch Step Line
429 x_batch_step_rec Batch Step Line
430 x_return_status outcome of the API call
431 S - Success
432 E - Error
433 U - Unexpected error
434 History
435
436 =============================================================================================*/
437
438 PROCEDURE complete_step_line
439 (p_batch_step_rec IN gme_batch_steps%ROWTYPE
440 ,x_batch_step_rec OUT NOCOPY gme_batch_steps%ROWTYPE
441 ,x_exception_material_tbl IN OUT NOCOPY gme_common_pvt.exceptions_tab
442 ,x_return_status OUT NOCOPY VARCHAR2) IS
443
444 l_api_name CONSTANT VARCHAR2 (30) := 'complete_step_line';
445
446 batch_step_upd_err EXCEPTION;
447 BEGIN
448
449 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
450 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
451 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' Complete step line batchstep_id='||p_batch_step_rec.batchstep_id);
452 END IF;
453
454 x_return_status := FND_API.G_RET_STS_SUCCESS;
455
456 -- Each time this is called, p_batch_step_rec has already been retrieved from DB... has all
457 -- latest data and in addition has the actual completion date calculated and set
458 x_batch_step_rec := p_batch_step_rec;
459
460 /* Update the Batch Step Status to WIP */
461 x_batch_step_rec.step_status := gme_common_pvt.g_step_completed;
462
463 -- Update the batch step
464 IF NOT (gme_batch_steps_dbl.update_row (x_batch_step_rec)) THEN
465 RAISE batch_step_upd_err;
466 END IF;
467
468 -- Update WHO columns for output structure
469 x_batch_step_rec.last_updated_by := gme_common_pvt.g_user_ident;
470 x_batch_step_rec.last_update_date := gme_common_pvt.g_timestamp;
471 x_batch_step_rec.last_update_login := gme_common_pvt.g_login_id;
472
473 /* Update the actual completion dates of the activities */
474 UPDATE gme_batch_step_activities
475 SET actual_cmplt_date = x_batch_step_rec.actual_cmplt_date
476 WHERE batchstep_id = x_batch_step_rec.batchstep_id
477 AND batch_id = x_batch_step_rec.batch_id
478 AND actual_cmplt_date IS NULL;
479
480 /* Update the actual start dates of the activities */
481 UPDATE gme_batch_step_activities
482 SET actual_start_date = x_batch_step_rec.actual_start_date
483 WHERE batchstep_id = x_batch_step_rec.batchstep_id
484 AND batch_id = x_batch_step_rec.batch_id
485 AND actual_start_date IS NULL;
486
487
488 /* Update the actual completion dates of the resources */
489 UPDATE gme_batch_step_resources
490 SET actual_cmplt_date = x_batch_step_rec.actual_cmplt_date
491 WHERE batchstep_id = x_batch_step_rec.batchstep_id
492 AND batch_id = x_batch_step_rec.batch_id
493 AND actual_cmplt_date IS NULL;
494
495 /* Update the actual start dates of the resources */
496 UPDATE gme_batch_step_resources
497 SET actual_start_date = x_batch_step_rec.actual_start_date
498 WHERE batchstep_id = x_batch_step_rec.batchstep_id
499 AND batch_id = x_batch_step_rec.batch_id
500 AND actual_start_date IS NULL;
501
502 /* We need to remove the resource information for the gme_batch_step_rsrc_summary */
503 /* table, as this table should only hold data of the resources which are in PENDING or WIP */
504 DELETE FROM gme_batch_step_rsrc_summary
505 WHERE batchstep_id = x_batch_step_rec.batchstep_id
506 AND batch_id = x_batch_step_rec.batch_id;
507
508 EXCEPTION
509 WHEN batch_step_upd_err THEN
510 gme_common_pvt.log_message ('GME_UNEXPECTED_ERROR', 'ERROR', SQLERRM);
511 x_return_status := FND_API.g_ret_sts_unexp_error;
512 WHEN OTHERS THEN
513 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
514 IF g_debug <= gme_debug.g_log_procedure THEN
515 gme_debug.put_line ('Unexpected error: '||g_pkg_name||'.'||l_api_name||': '||SQLERRM);
516 END IF;
517 x_return_status := FND_API.g_ret_sts_unexp_error;
518 END complete_step_line;
519
520 PROCEDURE complete_step_material
521 (p_batch_step_rec IN gme_batch_steps%ROWTYPE
522 ,p_update_inv_ind IN VARCHAR2
523 ,x_exception_material_tbl IN OUT NOCOPY gme_common_pvt.exceptions_tab
524 ,x_return_status OUT NOCOPY VARCHAR2) IS
525
526
527 CURSOR Cur_step_prod_byprod(v_batchstep_id NUMBER) IS
528 SELECT matl.*
529 FROM gme_material_details matl, gme_batch_step_items item
530 WHERE item.batchstep_id = v_batchstep_id
531 AND item.material_detail_id = matl.material_detail_id
532 -- 12896375 - Look at all line types.
533 -- AND (matl.line_type IN (gme_common_pvt.g_line_type_prod, gme_common_pvt.g_line_type_byprod) OR
534 -- (matl.line_type = gme_common_pvt.g_line_type_ing AND matl.phantom_id IS NOT NULL))
535 AND matl.release_type = gme_common_pvt.g_mtl_autobystep_release;
536
537 l_api_name CONSTANT VARCHAR2 (30) := 'complete_step_material';
538
539 l_return_status VARCHAR2(1);
540 l_matl_dtl_rec gme_material_details%ROWTYPE;
541 l_matl_dtl_tab gme_common_pvt.material_details_tab;
542 l_yield BOOLEAN;
543
544 error_process_prod EXCEPTION;
545
546 BEGIN
547 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
548 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
549 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' Completing products/byproducts for step_id='||p_batch_step_rec.batchstep_id);
550 END IF;
551
552 /* Set the return status to success initially */
553 x_return_status := FND_API.G_RET_STS_SUCCESS;
554
555 -- retrieve all autobystep products and phantom ingredients associated to the step...
556 OPEN Cur_step_prod_byprod(p_batch_step_rec.batchstep_id);
557 FETCH Cur_step_prod_byprod BULK COLLECT INTO l_matl_dtl_tab;
558 CLOSE Cur_step_prod_byprod;
559
560 FOR i IN 1..l_matl_dtl_tab.COUNT LOOP
561 l_matl_dtl_rec := l_matl_dtl_tab(i);
562
563 l_yield := TRUE;
564 gme_complete_batch_pvt.process_material
565 (p_material_detail_rec => l_matl_dtl_rec
566 ,p_yield => l_yield
567 ,p_trans_date => p_batch_step_rec.actual_cmplt_date
568 ,p_update_inv_ind => p_update_inv_ind
569 ,x_exception_material_tbl => x_exception_material_tbl
570 ,x_return_status => l_return_status);
571
572 IF l_return_status NOT IN (FND_API.G_RET_STS_SUCCESS, gme_common_pvt.g_exceptions_err) THEN
573 x_return_status := l_return_status;
574 RAISE error_process_prod;
575 END IF;
576
577 IF l_return_status = gme_common_pvt.g_exceptions_err THEN
578 x_return_status := gme_common_pvt.g_exceptions_err;
579 END IF;
580 END LOOP;
581
582 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
583 gme_debug.put_line('Exiting api '||g_pkg_name||'.'||l_api_name);
584 END IF;
585
586 EXCEPTION
587 WHEN error_process_prod THEN
588 NULL;
589 WHEN OTHERS THEN
590 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
591 IF g_debug <= gme_debug.g_log_procedure THEN
592 gme_debug.put_line ('Unexpected error: '||g_pkg_name||'.'||l_api_name||': '||SQLERRM);
593 END IF;
594 x_return_status := FND_API.g_ret_sts_unexp_error;
595 END complete_step_material;
596
597 PROCEDURE validate_dependent_steps (p_batch_id IN NUMBER
598 ,p_step_id IN NUMBER
599 ,p_step_actual_start_date IN DATE
600 ,x_return_status OUT NOCOPY VARCHAR2) IS
601
602 l_api_name CONSTANT VARCHAR2 (30) := 'validate_dependent_steps';
603
604 CURSOR Cur_get_dep_steps(v_batch_id NUMBER, v_step_id NUMBER) IS
605 SELECT d.dep_step_id, d.dep_type, d.standard_delay, s.steprelease_type,
606 s.step_status,s.actual_cmplt_date,s.actual_start_date
607 FROM gme_batch_step_dependencies d, gme_batch_steps s
608 WHERE d.batchstep_id = v_step_id
609 AND s.batchstep_id = d.dep_step_id
610 AND s.batch_id = v_batch_id
611 AND s.batch_id = d.batch_id;
612
613 l_dep_step_rec Cur_get_dep_steps%ROWTYPE;
614
615 GME_STEP_DEP_COMPLETE EXCEPTION;
616 GME_STEP_DEP_WIP EXCEPTION;
617 INVALID_START_DATE EXCEPTION;
618 BEGIN
619 IF NVL (g_debug, gme_debug.g_log_procedure + 1) <=
620 gme_debug.g_log_procedure THEN
621 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
622 || l_api_name);
623 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' batchstep_id = '||p_step_id);
624 END IF;
625
626 x_return_status := FND_API.g_ret_sts_success;
627
628 FOR l_dep_step_rec IN Cur_get_dep_steps(p_batch_id, p_step_id) LOOP
629 /*
630 If the dependency is Finish To Start then the prior step should be
631 completed or closed; if the dependecy is start to start then prior
632 step should be WIP, completed or closed */
633
634 IF (l_dep_step_rec.dep_type = gme_common_pvt.g_dep_type_finish_start) THEN
635 IF l_dep_step_rec.step_status NOT IN (gme_common_pvt.g_step_completed
636 ,gme_common_pvt.g_step_closed) THEN
637 RAISE GME_STEP_DEP_COMPLETE;
638 END IF;
639 IF p_step_actual_start_date < l_dep_step_rec.actual_cmplt_date
640 + (l_dep_step_rec.standard_delay/24) THEN
641 RAISE INVALID_START_DATE;
642 END IF;
643 ELSE -- start to start
644 IF l_dep_step_rec.step_status NOT IN (gme_common_pvt.g_step_wip
645 ,gme_common_pvt.g_step_completed
646 ,gme_common_pvt.g_step_closed) THEN
647 RAISE GME_STEP_DEP_WIP;
648 END IF;
649 IF p_step_actual_start_date < l_dep_step_rec.actual_start_date
650 + (l_dep_step_rec.standard_delay/24) THEN
651 RAISE INVALID_START_DATE;
652 END IF;
653 END IF;
654 END LOOP; -- FOR l_dep_step_rec IN Cur_get_dep_steps
655
656 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
657 gme_debug.put_line('Exiting api '||g_pkg_name||'.'||l_api_name);
658 END IF;
659
660 EXCEPTION
661 WHEN INVALID_START_DATE THEN
662 gme_common_pvt.log_message('GME_INVALID_START_DATE');
663 x_return_status := FND_API.G_RET_STS_ERROR;
664 WHEN GME_STEP_DEP_WIP THEN
665 gme_common_pvt.log_message('GME_STEP_DEP_WIP');
666 x_return_status := FND_API.G_RET_STS_ERROR;
667 WHEN GME_STEP_DEP_COMPLETE THEN
668 gme_common_pvt.log_message('GME_STEP_DEP_COMPLETE');
669 x_return_status := FND_API.G_RET_STS_ERROR ;
670 WHEN OTHERS THEN
671 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
672 IF g_debug <= gme_debug.g_log_procedure THEN
673 gme_debug.put_line ('Unexpected error: '||g_pkg_name||'.'||l_api_name||': '||SQLERRM);
674 END IF;
675 x_return_status := FND_API.g_ret_sts_unexp_error;
676 END validate_dependent_steps;
677
678 PROCEDURE validate_step_for_complete (p_batch_header_rec IN gme_batch_header%ROWTYPE
679 ,p_batch_step_rec IN gme_batch_steps%ROWTYPE
680 ,p_override_quality IN VARCHAR2
681 ,x_batch_step_rec OUT NOCOPY gme_batch_steps%ROWTYPE
682 ,x_return_status OUT NOCOPY VARCHAR2) IS
683
684 l_api_name CONSTANT VARCHAR2 (30) := 'validate_step_for_complete';
685 CURSOR cur_validity_rule(v_recipe_validity_rule_id NUMBER)
686 IS
687 SELECT *
688 FROM gmd_recipe_validity_rules
689 WHERE recipe_validity_rule_id = v_recipe_validity_rule_id;
690
691 CURSOR cur_validity_status_type(v_validity_rule_status VARCHAR2)
692 IS
693 SELECT status_type
694 FROM gmd_status
695 WHERE status_code=v_validity_rule_status;
696
697 l_validity_rule gmd_recipe_validity_rules%ROWTYPE;
698 l_status_type GMD_STATUS.status_type%TYPE;
699 error_vr_not_found EXCEPTION;
700 error_validity_status EXCEPTION;
701 error_cmplt_date EXCEPTION;
702 error_no_direct_compl EXCEPTION;
703 error_quality_status EXCEPTION;
704 error_validation EXCEPTION;
705 error_future_date EXCEPTION;
706 --Bug#5109119
707 error_close_period EXCEPTION;
708 error_vr_dates EXCEPTION;
709
710 -- Bug 9277115
711 error_actual_start_date EXCEPTION;
712 BEGIN
713 IF NVL (g_debug, gme_debug.g_log_procedure + 1) <=
714 gme_debug.g_log_procedure THEN
715 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
716 || l_api_name);
717 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' batchstep_id = '||p_batch_step_rec.batchstep_id);
718 END IF;
719
720 x_return_status := fnd_api.g_ret_sts_success;
721
722 -- set output structure
723 x_batch_step_rec := p_batch_step_rec;
724
725 --Sunith ch.5404329 check validity rule if it's not NULL; it would be NULL in case of LCF
726 IF p_batch_header_rec.recipe_validity_rule_id IS NOT NULL THEN
727 -- Bug 13004429 - This check should only be done for a pending batch.
728 IF p_batch_header_rec.batch_status = 1 THEN
729 OPEN cur_validity_rule(p_batch_header_rec.recipe_validity_rule_id);
730 FETCH cur_validity_rule INTO l_validity_rule;
731 CLOSE cur_validity_rule;
732
733 IF l_validity_rule.recipe_validity_rule_id IS NULL THEN -- not found
734 RAISE error_vr_not_found;
735 ELSE
736 -- following prevents user from releasing a pending batch
737 -- if validity rule is ON_HOLD or OBSOLETE.
738 OPEN cur_validity_status_type(l_validity_rule.validity_rule_status);
739 FETCH cur_validity_status_type INTO l_status_type;
740 CLOSE cur_validity_status_type;
741
742 IF l_status_type IN ('1000' ,'800') THEN
743 RAISE error_validity_status;
744 END IF;
745 END IF; -- IF l_validity_rule.recipe_validity_rule_id IS NULL
746
747 /* IF l_validity_rule.start_date > x_batch_header_rec.actual_start_date OR
748 (l_validity_rule.end_date IS NOT NULL AND
749 l_validity_rule.end_date < x_batch_header_rec.actual_start_date) THEN
750 RAISE error_vr_dates;
751 END IF;*/
752 -- sunitha ch. Bug 5336007 aded call to check_validity_rule_dates and passed p_validate_plan_dates_ind=1
753 -- to validate planned start date against validate rule dates
754 IF NOT gme_common_pvt.check_validity_rule_dates (
755 p_validity_rule_id => p_batch_header_rec.recipe_validity_rule_id
756 ,p_start_date => p_batch_header_rec.actual_start_date
757 ,p_cmplt_date => p_batch_header_rec.actual_cmplt_date
758 ,p_batch_header_rec => p_batch_header_rec
759 ,p_validate_plan_dates_ind => 1) THEN
760 x_return_status := fnd_api.g_ret_sts_error;
761 RAISE error_vr_dates;
762 END IF;
763 -- End Bug 5336007
764 END IF; -- p_batch_header_rec.batch_status = 1
765 END IF; -- IF p_batch_header_rec.recipe_validity_rule_id IS NOT NULL
766
767 -- Bug 9277115 - Let's also check the step start date if the step is not already released.
768 IF p_batch_step_rec.step_status = gme_common_pvt.g_step_pending THEN
769 IF p_batch_step_rec.actual_start_date < p_batch_header_rec.actual_start_date THEN
770 RAISE error_actual_start_date;
771 END IF;
772 END IF;
773
774 -- set actual complete date if it's not passed
775 IF p_batch_step_rec.actual_cmplt_date IS NULL THEN
776 IF p_batch_step_rec.step_status = gme_common_pvt.g_step_pending THEN
777 --Sunitha ch. Bug#5327152 set actual complete start if it's not passed
778 IF x_batch_step_rec.actual_start_date IS NULL THEN
779 x_batch_step_rec.actual_start_date:=SYSDATE;
780 END IF;
781
782 x_batch_step_rec.actual_cmplt_date := x_batch_step_rec.actual_start_date;
783 ELSE
784 x_batch_step_rec.actual_cmplt_date := SYSDATE;
785 END IF;
786 ELSE -- user passed in an actual cmplt date; validate it against start date
787 IF x_batch_step_rec.actual_cmplt_date < x_batch_step_rec.actual_start_date THEN
788 RAISE error_cmplt_date;
789 ELSIF (x_batch_step_rec.actual_cmplt_date > SYSDATE) THEN
790 RAISE error_future_date;
791 END IF;
792 x_batch_step_rec.actual_cmplt_date := p_batch_step_rec.actual_cmplt_date;
793 END IF;
794
795 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
796 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'));
797 END IF;
798
799 --Bug#5109119 check for close period
800 IF NOT gme_common_pvt.check_close_period(p_org_id => p_batch_header_rec.organization_id
801 ,p_trans_date => x_batch_step_rec.actual_cmplt_date) THEN
802 RAISE error_close_period;
803 END IF;
804
805
806 -- Enforce Step Dependency Checks
807 IF p_batch_header_rec.enforce_step_dependency = 1 THEN
808 IF p_batch_step_rec.step_status <> gme_common_pvt.g_step_wip THEN
809 RAISE error_no_direct_compl;
810 END IF;
811
812 -- validate dependent step status and dates
813 validate_dependent_steps (p_batch_id => x_batch_step_rec.batch_id
814 ,p_step_id => x_batch_step_rec.batchstep_id
815 ,p_step_actual_start_date => x_batch_step_rec.actual_start_date
816 ,x_return_status => x_return_status);
817
818 IF x_return_status <> fnd_api.g_ret_sts_success THEN
819 RAISE error_validation;
820 END IF;
821 END IF;
822
823 -- if quality is not complete cannot complete step
824 IF (x_batch_step_rec.quality_status NOT IN (1,4,6) AND
825 p_override_quality = fnd_api.g_false) THEN
826 RAISE error_quality_status;
827 END IF;
828
829 IF NVL (g_debug, gme_debug.g_log_procedure + 1) <=
830 gme_debug.g_log_procedure THEN
831 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
832 END IF;
833
834 EXCEPTION
835 --Bug#5109119 Begin
836 WHEN error_close_period THEN
837 x_return_status := FND_API.G_RET_STS_ERROR;
838 WHEN error_validation THEN
839 NULL;
840 -- 9277115 - add following condition.
841 WHEN error_actual_start_date THEN
842 gme_common_pvt.log_message ('GME_STEP_START_BATCH_START_ERR');
843 x_return_status := FND_API.G_RET_STS_ERROR;
844 WHEN error_no_direct_compl THEN
845 gme_common_pvt.log_message('NO_DIRECT_CERT_ALLOWED');
846 x_return_status := FND_API.G_RET_STS_ERROR ;
847 WHEN error_future_date THEN
848 fnd_message.set_name ('GMA', 'SY_NOFUTUREDATE');
849 fnd_msg_pub.ADD;
850 x_return_status := FND_API.G_RET_STS_ERROR;
851 WHEN error_cmplt_date THEN
852 gme_common_pvt.log_message('GME_INVALID_DATE_RANGE'
853 ,'DATE1','Completion date'
854 ,'DATE2','Start date');
855 x_return_status := fnd_api.g_ret_sts_error;
856 WHEN error_quality_status THEN
857 gme_common_pvt.log_message('GME_QUALITY_NOT_COMPLETE'
858 ,'STEP_NO', x_batch_step_rec.batchstep_no
859 ,'BATCH_NO', p_batch_header_rec.batch_no);
860 x_return_status := fnd_api.g_ret_sts_error;
861 WHEN error_vr_dates THEN
862 x_return_status := FND_API.G_RET_STS_ERROR;
863 WHEN OTHERS THEN
864 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
865
866 IF g_debug <= gme_debug.g_log_procedure THEN
867 gme_debug.put_line ( 'Unexpected error: '
868 || g_pkg_name
869 || '.'
870 || l_api_name
871 || ': '
872 || SQLERRM);
873 END IF;
874 x_return_status := fnd_api.g_ret_sts_unexp_error;
875 END validate_step_for_complete;
876
877 /*===========================================================================================
878 Procedure
879 validate_step_cmplt_date
880 Description
881 This procedure is used to ensure that the step actual start dates are
882 not earlier than the batch actual start dates.
883
884 ** no changes for convergence
885
886 Parameters
887 p_batch_step_rec Batch step recrod
888 p_batch_header_rec Batch Header record
889 x_batch_start_date Calculated batch start date
890 x_return_status outcome of the API call
891 S - Success
892 E - Error
893 U - Unexpected error
894 History
895
896 G. Muratore 27-MAR-2012 Bug 13706812
897 Improved performance of dependent step cursor. This issue was found by migration testing.
898 =============================================================================================*/
899
900 PROCEDURE validate_step_cmplt_date
901 (p_batch_step_rec IN GME_BATCH_STEPS%ROWTYPE
902 ,p_batch_header_rec IN GME_BATCH_HEADER%ROWTYPE
903 ,x_batch_start_date OUT NOCOPY DATE
904 ,x_return_status OUT NOCOPY VARCHAR2) IS
905
906 l_api_name CONSTANT VARCHAR2 (30) := 'validate_step_cmplt_date';
907
908 -- Bug 13706812 - Use New select to help performance. Renamed original cursor.
909 CURSOR Cur_get_dep_steps_old IS
910 SELECT dep_step_id, dep_type, standard_delay
911 FROM gme_batch_step_dependencies
912 START WITH batchstep_id = p_batch_step_rec.batchstep_id
913 CONNECT BY batchstep_id = PRIOR dep_step_id;
914
915 CURSOR Cur_get_dep_steps IS
916 SELECT dep_step_id, dep_type, standard_delay
917 FROM (SELECT * FROM gme_batch_step_dependencies WHERE batch_id = p_batch_header_rec.batch_id) d,
918 (SELECT * FROM gme_batch_steps WHERE batch_id = p_batch_header_rec.batch_id) s,
919 (SELECT * FROM gme_batch_steps WHERE batch_id = p_batch_header_rec.batch_id) p
920 WHERE s.batchstep_id = d.batchstep_id
921 AND p.batchstep_id(+) = d.dep_step_id
922 START WITH d.batchstep_id = p_batch_step_rec.batchstep_id
923 CONNECT BY d.batchstep_id = PRIOR d.dep_step_id;
924
925
926 BATCH_STEP_FETCH_ERROR EXCEPTION;
927 INVALID_DATE_ERR EXCEPTION;
928 X_prev_start_date DATE;
929 X_prev_step NUMBER;
930 l_batch_step GME_BATCH_STEPS%ROWTYPE;
931
932 BEGIN
933 IF NVL (g_debug, gme_debug.g_log_procedure + 1) <=
934 gme_debug.g_log_procedure THEN
935 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
936 || l_api_name);
937 END IF;
938
939 -- Set the return status to success initially *
940 x_return_status := FND_API.G_RET_STS_SUCCESS;
941
942 -- Check the date of the step being certified with the batch actual start date
943 IF (p_batch_header_rec.actual_start_date IS NOT NULL) THEN
944 IF (p_batch_step_rec.actual_start_date < p_batch_header_rec.actual_start_date) THEN
945 gme_common_pvt.log_message('GME_BATCH_START_STEP_START_ERR',
946 'STEP_DATE', TO_CHAR(p_batch_step_rec.actual_start_date, 'DD-MON-YYYY HH24:MI:SS'),
947 'STEP_NO', TO_CHAR(p_batch_step_rec.batchstep_no),
948 'BATCH_DATE', TO_CHAR(p_batch_header_rec.actual_start_date, 'DD-MON-YYYY HH24:MI:SS'));
949 RAISE INVALID_DATE_ERR;
950 END IF;
951 END IF;
952
953 -- Here prev refers to the next step since we are moving backwards in the tree
954 X_prev_start_date := p_batch_step_rec.actual_start_date;
955 X_prev_step := p_batch_step_rec.batchstep_no;
956
957 -- Move through the dependency tree and see if any dates are invalid
958 FOR get_rec IN Cur_get_dep_steps LOOP
959 l_batch_step.batchstep_id := get_rec.dep_step_id;
960
961 -- Initialize batch step row
962 IF NOT (GME_BATCH_STEPS_DBL.fetch_row(l_batch_step, l_batch_step)) THEN
963 RAISE BATCH_STEP_FETCH_ERROR;
964 END IF;
965
966 -- Check if the step has a complete date if so validate otherwise assign one to it
967 IF (l_batch_step.actual_cmplt_date IS NOT NULL) THEN
968 IF get_rec.dep_type = gme_common_pvt.g_dep_type_finish_start THEN
969 IF (p_batch_header_rec.enforce_step_dependency = 1) AND (l_batch_step.actual_cmplt_date + (get_rec.standard_delay/24) > X_prev_start_date) THEN
970 gme_common_pvt.log_message('GME_STEP_START_PREV_STEP_CMPLT',
971 'PREV_DATE',
972 TO_CHAR(X_prev_start_date, 'DD-MON-YYYY HH24:MI:SS'),
973 'PREV_STEP', TO_CHAR(X_prev_step),
974 'CUR_DATE',
975 TO_CHAR(l_batch_step.actual_cmplt_date +
976 (get_rec.standard_delay/24), 'DD-MON-YYYY HH24:MI:SS'));
977 RAISE INVALID_DATE_ERR;
978 END IF;
979 ELSIF get_rec.dep_type = gme_common_pvt.g_dep_type_start_start THEN
980 IF (p_batch_header_rec.enforce_step_dependency = 1) AND (l_batch_step.actual_start_date + (get_rec.standard_delay/24) > X_prev_start_date) THEN
981 gme_common_pvt.log_message('GME_STEP_START_PREV_STEP_START',
982 'PREV_DATE',
983 TO_CHAR(X_prev_start_date, 'DD-MON-YYYY HH24:MI:SS'),
984 'PREV_STEP', TO_CHAR(X_prev_step),
985 'CUR_DATE',
986 TO_CHAR(l_batch_step.actual_start_date +
987 (get_rec.standard_delay/24), 'DD-MON-YYYY HH24:MI:SS'));
988 RAISE INVALID_DATE_ERR;
989 END IF;
990 EXIT;
991 END IF;
992 ELSIF (get_rec.dep_type = gme_common_pvt.g_dep_type_finish_start) AND
993 (get_rec.standard_delay >= 0) THEN
994 l_batch_step.actual_cmplt_date := X_prev_start_date - (get_rec.standard_delay/24);
995 ELSE
996 l_batch_step.actual_cmplt_date := X_prev_start_date;
997 END IF;
998
999 -- Check if the step has a start date if so validate otherwise assign one to it
1000 IF (l_batch_step.actual_start_date IS NULL) THEN
1001 l_batch_step.actual_start_date := l_batch_step.actual_cmplt_date;
1002 END IF;
1003
1004 -- Validate step actual start date against batch actual start date
1005 IF (p_batch_header_rec.actual_start_date IS NOT NULL) THEN
1006 IF (l_batch_step.actual_start_date < p_batch_header_rec.actual_start_date) THEN
1007 gme_common_pvt.log_message('GME_BATCH_START_STEP_START_ERR',
1008 'STEP_DATE',
1009 TO_CHAR(l_batch_step.actual_start_date, 'DD-MON-YYYY HH24:MI:SS'),
1010 'STEP_NO', TO_CHAR(l_batch_step.batchstep_no),
1011 'BATCH_DATE',
1012 TO_CHAR(p_batch_header_rec.actual_start_date, 'DD-MON-YYYY HH24:MI:SS'));
1013 RAISE INVALID_DATE_ERR;
1014 END IF;
1015 END IF;
1016 X_prev_start_date := l_batch_step.actual_start_date;
1017 X_prev_step := l_batch_step.batchstep_no;
1018 END LOOP;
1019
1020 x_batch_start_date := X_prev_start_date;
1021
1022 IF NVL (g_debug, gme_debug.g_log_procedure + 1) <=
1023 gme_debug.g_log_procedure THEN
1024 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
1025 END IF;
1026 EXCEPTION
1027 WHEN BATCH_STEP_FETCH_ERROR THEN
1028 gme_common_pvt.log_message ('GME_UNEXPECTED_ERROR', 'ERROR'
1029 ,SQLERRM);
1030 x_return_status := fnd_api.g_ret_sts_unexp_error;
1031 WHEN INVALID_DATE_ERR THEN
1032 x_return_status := FND_API.G_RET_STS_ERROR;
1033 WHEN OTHERS THEN
1034 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1035
1036 IF g_debug <= gme_debug.g_log_procedure THEN
1037 gme_debug.put_line ( 'Unexpected error: '
1038 || g_pkg_name
1039 || '.'
1040 || l_api_name
1041 || ': '
1042 || SQLERRM);
1043 END IF;
1044 x_return_status := fnd_api.g_ret_sts_unexp_error;
1045 END validate_step_cmplt_date;
1046
1047 END gme_complete_batch_step_pvt;
[Home] [Help]