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