[Home] [Help]
PACKAGE BODY: APPS.GME_RESOURCE_ENGINE_PVT
Source
1 PACKAGE BODY gme_resource_engine_pvt AS
2 /* $Header: GMEVRXNB.pls 120.16.12010000.1 2008/07/25 10:31:59 appldev ship $
3 *****************************************************************
4 * *
5 * Package GME_RESOURCE_ENGINE_PVT *
6 * *
7 * Contents CREATE_PENDING_TRANS *
8 * CREATE_COMPLETED_TRANS *
9 * UPDATE_PENDING_TRANS *
10 * DELETE_PENDING_TRANS *
11 * FETCH_ALL_RESOURCES *
12 * BUILD_RESOURCE_TRAN *
13 * FETCH_ACTIVE_RESOURCES *
14 * *
15 * Use This is the private layer of the GME Resource *
16 * Transaction Processor. *
17 * *
18 * History
19 * 17-JAN-2006 Susruth D. Bug#4917189 *
20 * Added Update statements instead of dbl for updating *
21 * the reverse_id in CONSOLIDATE_BATCH_RESOURCES. *
22 * 09-JUN-2006 SivakumarG Bug#5231180 *
23 * Code added to resource_dtl_process to recalculate charge*
24 * if there is any insertion/updation/deletion if resouces *
25 * 27-SEP-2007 Swapna K Bug#6154309
26 * Added the condition to check for ASQC batch before assigning the *
27 * overrided_protected_ind column of the resource transactions. *
28 * 07-NOV-2007 Swapna K Bug#6607524
29 * Changed the hardcoded organization id to that of the batch *
30 * header's organization id in resource_dtl_process procedure *
31 *****************************************************************
32 */
33 /* Global variables */
34 g_debug VARCHAR2 (5) := fnd_profile.VALUE ('AFLOG_LEVEL');
35 g_pkg_name CONSTANT VARCHAR2 (30) := 'GME_RESOURCE_ENGINE_PVT';
36
37 /*===========================================================================================
38 Procedure
39 fetch_all_resources
40 Description
41 This particular procedure is used to fetch all the resource transactions for a particular
42 trans id or a batch id
43 Parameters
44 p_resource_rec The resource transaction rec
45 x_resource_tbl All the resource transactions pertaining to the criteria.
46 x_return_status outcome of the API call
47 S - Success
48 E - Error
49 U - Unexpected error
50 =============================================================================================*/
51 PROCEDURE fetch_all_resources (
52 p_resource_rec IN gme_resource_txns_gtmp%ROWTYPE
53 ,x_resource_tbl OUT NOCOPY gme_common_pvt.resource_transactions_tab
54 ,x_return_status OUT NOCOPY VARCHAR2
55 ,p_active_trans IN NUMBER DEFAULT 0 -- B3425554
56 )
57 IS
58 TYPE query_ref IS REF CURSOR;
59
60 get_rsrc query_ref;
61 l_where VARCHAR2 (2000);
62 l_api_name CONSTANT VARCHAR2 (30) := 'FETCH_ALL_TRANS';
63 l_return_status VARCHAR2 (1) := fnd_api.g_ret_sts_success;
64 l_line_index BINARY_INTEGER := 1;
65 l_cursor BINARY_INTEGER;
66 l_debug VARCHAR2 (2000);
67 BEGIN
68 IF g_debug <= gme_debug.g_log_procedure THEN
69 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
70 || l_api_name);
71 END IF;
72
73 /* Initialize API return status to sucess */
74 x_return_status := fnd_api.g_ret_sts_success;
75
76 -- Determine if any of the key values are present
77 IF ( (p_resource_rec.poc_trans_id IS NOT NULL)
78 OR (p_resource_rec.poc_trans_id <> fnd_api.g_miss_num) ) THEN
79 l_debug := 'Build Where Clause Cursor Ref 1';
80 l_where := 'POC_TRANS_ID =:poc_trans_id ';
81 l_cursor := 1;
82 ELSIF ( (p_resource_rec.doc_id IS NOT NULL)
83 OR (p_resource_rec.doc_id <> fnd_api.g_miss_num) ) THEN
84 l_debug := 'Build Where Clause Cursor Ref 2';
85 l_where := ' DOC_ID =:doc_id';
86 l_cursor := 2;
87 ELSE
88 l_debug := 'Report An Error';
89 END IF;
90
91 -- B3425554 only fetch changed trxns performance enhancenment
92 IF p_active_trans = 1 THEN
93 l_where :=
94 l_where || ' AND action_code IN (''ADD'',''DEL'',''UPD'')';
95 END IF;
96
97 IF l_cursor = 1 THEN
98 OPEN get_rsrc
99 FOR ' SELECT * FROM GME_RESOURCE_TXNS_GTMP
100 WHERE '
101 || l_where USING p_resource_rec.poc_trans_id;
102 ELSIF l_cursor = 2 THEN
103 OPEN get_rsrc
104 FOR ' SELECT * FROM GME_RESOURCE_TXNS_GTMP
105 WHERE '
106 || l_where USING p_resource_rec.doc_id;
107 END IF;
108
109 LOOP
110 FETCH get_rsrc
111 INTO x_resource_tbl (l_line_index);
112
113 EXIT WHEN get_rsrc%NOTFOUND;
114 l_line_index := l_line_index + 1;
115 END LOOP;
116
117 CLOSE get_rsrc;
118
119 IF g_debug <= gme_debug.g_log_procedure THEN
120 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
121 END IF;
122 EXCEPTION
123 WHEN fnd_api.g_exc_error THEN
124 x_return_status := fnd_api.g_ret_sts_error;
125 WHEN fnd_api.g_exc_unexpected_error THEN
126 x_return_status := fnd_api.g_ret_sts_unexp_error;
127
128 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
129 gme_debug.put_line ('IN UN ' || SQLERRM);
130 END IF;
131 WHEN OTHERS THEN
132 IF g_debug <= gme_debug.g_log_unexpected THEN
133 gme_debug.put_line ( 'When others exception in '
134 || g_pkg_name
135 || '.'
136 || l_api_name
137 || ' Error is '
138 || SQLERRM);
139 END IF;
140
141 x_return_status := fnd_api.g_ret_sts_unexp_error;
142 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
143 END fetch_all_resources;
144
145 /*===========================================================================================
146 Procedure
147 create_resource_trans
148 Description
149 This particular procedure is used to create pending or completed resource transactions
150 Parameters
151 p_resource_rec The resource transaction rec
152 x_tran_rec The resource transaction rec with the updated poc trans id
153 x_return_status outcome of the API call
154 S - Success
155 E - Error
156 U - Unexpected error
157 =============================================================================================*/
158 PROCEDURE create_resource_trans (
159 p_tran_rec IN gme_resource_txns_gtmp%ROWTYPE
160 ,x_tran_rec OUT NOCOPY gme_resource_txns_gtmp%ROWTYPE
161 ,x_return_status OUT NOCOPY VARCHAR2)
162 IS
163 l_api_name CONSTANT VARCHAR2 (30) := 'CREATE_RESOURCE_TRANS';
164 l_return_status VARCHAR2 (1) := fnd_api.g_ret_sts_success;
165 l_gme_tran_rec gme_resource_txns_gtmp%ROWTYPE;
166 BEGIN
167 IF g_debug <= gme_debug.g_log_procedure THEN
168 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
169 || l_api_name);
170 END IF;
171
172 l_gme_tran_rec := p_tran_rec;
173
174 /* Now Call the INSERT rec DML Layer */
175 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
176 gme_debug.put_line ('Calling Insert Resource TXNS rec');
177 gme_debug.put_line ('Resources +> ' || p_tran_rec.resources);
178 END IF;
179
180 l_gme_tran_rec.action_code := 'ADD';
181
182 IF NOT gme_resource_txns_gtmp_dbl.insert_row
183 (p_resource_txns => l_gme_tran_rec
184 ,x_resource_txns => x_tran_rec) THEN
185 RAISE fnd_api.g_exc_error;
186 END IF;
187
188 x_return_status := l_return_status;
189
190 IF g_debug <= gme_debug.g_log_procedure THEN
191 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
192 END IF;
193 EXCEPTION
194 WHEN fnd_api.g_exc_error THEN
195 x_return_status := fnd_api.g_ret_sts_error;
196 WHEN fnd_api.g_exc_unexpected_error THEN
197 x_return_status := fnd_api.g_ret_sts_unexp_error;
198 WHEN OTHERS THEN
199 IF g_debug <= gme_debug.g_log_unexpected THEN
200 gme_debug.put_line ( 'When others exception in '
201 || g_pkg_name
202 || '.'
203 || l_api_name
204 || ' Error is '
205 || SQLERRM);
206 END IF;
207
208 x_return_status := fnd_api.g_ret_sts_unexp_error;
209 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
210 END create_resource_trans;
211
212 /*===========================================================================================
213 Procedure
214 delete_resource_trans
215 Description
216 This particular procedure is used to delete pending or completed resource transactions
217 Parameters
218 p_resource_rec The resource transaction rec
219 x_return_status outcome of the API call
220 S - Success
221 E - Error
222 U - Unexpected error
223 =============================================================================================*/
224 PROCEDURE delete_resource_trans (
225 p_tran_rec IN gme_resource_txns_gtmp%ROWTYPE
226 ,x_return_status OUT NOCOPY VARCHAR2)
227 IS
228 l_gme_tran_rec gme_resource_txns_gtmp%ROWTYPE;
229 l_api_name CONSTANT VARCHAR2 (30) := 'DELETE_RESOURCE_TRANS';
230 l_return_status VARCHAR2 (1) := fnd_api.g_ret_sts_success;
231 BEGIN
232 IF g_debug <= gme_debug.g_log_procedure THEN
233 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
234 || l_api_name);
235 END IF;
236
237 l_gme_tran_rec := p_tran_rec;
238
239 IF p_tran_rec.action_code = 'ADD' THEN
240 DELETE FROM gme_resource_txns_gtmp
241 WHERE poc_trans_id = p_tran_rec.poc_trans_id;
242 ELSE
243 l_gme_tran_rec.action_code := 'DEL';
244
245 IF NOT gme_resource_txns_gtmp_dbl.update_row
246 (p_resource_txns => l_gme_tran_rec) THEN
247 RAISE fnd_api.g_exc_error;
248 END IF;
249 END IF;
250
251 x_return_status := l_return_status;
252
253 IF g_debug <= gme_debug.g_log_procedure THEN
254 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
255 END IF;
256 EXCEPTION
257 WHEN fnd_api.g_exc_error THEN
258 x_return_status := fnd_api.g_ret_sts_error;
259 WHEN fnd_api.g_exc_unexpected_error THEN
260 x_return_status := fnd_api.g_ret_sts_unexp_error;
261 WHEN OTHERS THEN
262 IF g_debug <= gme_debug.g_log_unexpected THEN
263 gme_debug.put_line ( 'When others exception in '
264 || g_pkg_name
265 || '.'
266 || l_api_name
267 || ' Error is '
268 || SQLERRM);
269 END IF;
270
271 x_return_status := fnd_api.g_ret_sts_unexp_error;
272 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
273 END delete_resource_trans;
274
275 /*===========================================================================================
276 Procedure
277 update_resource_trans
278 Description
279 This particular procedure is used to update pending or completed resource transactions
280 Parameters
281 p_resource_rec The resource transaction rec
282 x_return_status outcome of the API call
283 S - Success
284 E - Error
285 U - Unexpected error
286 =============================================================================================*/
287 PROCEDURE update_resource_trans (
288 p_tran_rec IN gme_resource_txns_gtmp%ROWTYPE
289 ,x_return_status OUT NOCOPY VARCHAR2)
290 IS
291 l_api_name CONSTANT VARCHAR2 (30) := 'UPDATE_RESOURCE_TRANS';
292 l_return_status VARCHAR2 (1) := fnd_api.g_ret_sts_success;
293 l_gme_tran_rec gme_resource_txns_gtmp%ROWTYPE;
294 BEGIN
295 IF g_debug <= gme_debug.g_log_procedure THEN
296 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
297 || l_api_name);
298 END IF;
299
300 l_gme_tran_rec := p_tran_rec;
301
302 IF p_tran_rec.action_code = 'NONE' THEN
303 l_gme_tran_rec.action_code := 'UPD';
304 END IF;
305
306 /* Now Call the UPDATE rec DML Layer */
307 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
308 gme_debug.put_line ( 'Calling UPDATE rec trans id:'
309 || TO_CHAR (p_tran_rec.poc_trans_id) );
310 END IF;
311
312 IF NOT gme_resource_txns_gtmp_dbl.update_row
313 (p_resource_txns => l_gme_tran_rec) THEN
314 RAISE fnd_api.g_exc_error;
315 END IF;
316
317 x_return_status := l_return_status;
318
319 IF g_debug <= gme_debug.g_log_procedure THEN
320 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
321 END IF;
322 EXCEPTION
323 WHEN fnd_api.g_exc_error THEN
324 x_return_status := fnd_api.g_ret_sts_error;
325 WHEN fnd_api.g_exc_unexpected_error THEN
326 x_return_status := fnd_api.g_ret_sts_unexp_error;
327 WHEN OTHERS THEN
328 IF g_debug <= gme_debug.g_log_unexpected THEN
329 gme_debug.put_line ( 'When others exception in '
330 || g_pkg_name
331 || '.'
332 || l_api_name
333 || ' Error is '
334 || SQLERRM);
335 END IF;
336
337 x_return_status := fnd_api.g_ret_sts_unexp_error;
338 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
339 END update_resource_trans;
340
341 /*===========================================================================================
342 Procedure
343 consolidate_batch_resources
344 Description
345 This particular procedure is used to consolidate all the resource transactions from the
346 temporary tblle and post them into the main tblle.
347 Parameters
348 p_batch_id The batch_id for which the resources have to be consolidated
349 x_return_status outcome of the API call
350 S - Success
351 E - Error
352 U - Unexpected error
353
354 History
355 10MAR04 P.Raghu Bug#3479771
356 Modified consolidate_batch_resources procedure
357 such that reverse txns are created for completed txns.
358 for updation and deletion with posated_ind=0.
359 14-MAR-2004 Shrikant Nene 3570630
360 Modified CONSOLIDATE_BATCH_RESOURCES procedure keep the
361 attributes of the resource transactions. Currently if
362 these attributes are updated by users manually and then
363 if they change the transaction on the screen, attributes
364 are lost.
365 10-AUG-2004 Rishi Varma B3818266/3759970
366 Added code to populate the reverse_id.
367 02-SEP-2004 Rishi Varma B3856541
368 Made changes for the rsrc txns in closed period ME.
369 17-JAN-2006 Susruth D. Bug#4917189
370 Added Update statements instead of dbl for updating the reverse_id
371 in CONSOLIDATE_BATCH_RESOURCES.
372 =============================================================================================*/
373 PROCEDURE consolidate_batch_resources (
374 p_batch_id IN NUMBER
375 ,x_return_status OUT NOCOPY VARCHAR2)
376 IS
377 l_api_name CONSTANT VARCHAR2 (30) := 'CONSOLIDATE_BATCH_RESOURCES';
378 l_return_status VARCHAR2 (1);
379 l_msg_data VARCHAR2 (2000);
380 l_msg_count NUMBER;
381
382 CURSOR cur_get_prev_rec (v_poc_id NUMBER)
383 IS
384 SELECT *
385 FROM gme_resource_txns
386 WHERE poc_trans_id = v_poc_id;
387
388 l_debug VARCHAR2 (2000);
389 l_prev_rec cur_get_prev_rec%ROWTYPE;
390 l_resource_rec gme_resource_txns_gtmp%ROWTYPE;
391 l_tran_rec gme_resource_txns%ROWTYPE;
392 l_resource_tbl gme_common_pvt.resource_transactions_tab;
393 l_trans_date DATE;
394 l_org_id NUMBER;
395 l_period_id INTEGER;
396
397 l_in_tran_rec gme_resource_txns%ROWTYPE;
398
399 CURSOR cur_get_org_id (v_batch_id NUMBER)
400 IS
401 SELECT organization_id
402 FROM gme_batch_header
403 WHERE batch_id = v_batch_id;
404
405
406 insert_failure EXCEPTION;
407 update_failure EXCEPTION;
408
409 -- Bug 5903208
410 gmf_cost_failure EXCEPTION;
411 l_message_count NUMBER;
412 l_message_list VARCHAR2(2000);
413
414 BEGIN
415 IF g_debug <= gme_debug.g_log_procedure THEN
416 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
417 || l_api_name);
418 END IF;
419
420 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
421 gme_debug.display_resource_gtmp (NULL, NULL, p_batch_id);
422 END IF;
423
424 l_debug := 'Doc Id = > ' || p_batch_id;
425
426 l_resource_rec.doc_id := p_batch_id;
427 fetch_all_resources (p_resource_rec => l_resource_rec
428 ,x_resource_tbl => l_resource_tbl
429 ,x_return_status => l_return_status
430 ,p_active_trans => 1);
431
432 l_debug := 'Number Of recs => ' || TO_CHAR (l_resource_tbl.COUNT);
433
434 OPEN cur_get_org_id (p_batch_id);
435
436 FETCH cur_get_org_id
437 INTO l_org_id;
438
439 CLOSE cur_get_org_id;
440
441 IF (l_resource_tbl.COUNT < 1) THEN
442 l_debug := 'No recs found ';
443 END IF;
444
445 FOR i IN 1 .. l_resource_tbl.COUNT LOOP
446 l_resource_rec := l_resource_tbl (i);
447
448 IF (l_resource_rec.action_code <> 'NONE') THEN
449 build_resource_tran (l_resource_rec, l_tran_rec);
450 END IF;
451
452 IF (l_resource_rec.action_code = 'ADD') THEN
453 l_trans_date := l_tran_rec.trans_date;
454
455 IF NOT gme_common_pvt.close_period_check_flexible
456 (p_org_id => l_org_id
457 ,p_trans_date => l_trans_date
458 ,x_trans_date => l_tran_rec.trans_date
459 ,x_period_id => l_period_id) THEN
460 RAISE fnd_api.g_exc_error;
461 END IF;
462
463
464 --Rishi Varma B3856541 02-09-2004 end
465 IF NOT gme_resource_txns_dbl.insert_row (l_tran_rec, l_tran_rec) THEN
466 RAISE insert_failure;
467 END IF;
468
469 IF l_tran_rec.completed_ind = 1
470 THEN
471 --
472 -- Bug 5903208 - Make call to GMF for actual costing
473 --
474 GMF_LAYERS.Create_Resource_Layers
475 ( p_api_version => 1.0,
476 p_init_msg_list => FND_API.G_FALSE,
477 p_rsrc_rec => l_tran_rec,
478 p_doc_qty => l_tran_rec.resource_usage,
479 p_doc_um => l_tran_rec.trans_qty_um,
480 x_return_status => l_return_status,
481 x_msg_count => l_msg_count,
482 x_msg_data => l_msg_data);
483
484 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
485 THEN
486 RAISE gmf_cost_failure;
487 END IF;
488 -- End Bug 5903208
489 END IF;
490
491 ELSIF (l_resource_rec.action_code = 'UPD') THEN
492 OPEN cur_get_prev_rec (l_resource_rec.poc_trans_id);
493
494 FETCH cur_get_prev_rec
495 INTO l_prev_rec;
496
497 CLOSE cur_get_prev_rec;
498
499 l_in_tran_rec := l_prev_rec;
500
501 IF l_prev_rec.completed_ind = 1 THEN
502 /* Reverse the completed transaction */
503 l_prev_rec.resource_usage := (-1) * l_prev_rec.resource_usage;
504 l_prev_rec.posted_ind := 0;
505
506 /*Populating the reverse_id of the reversed transaction with the poc_trans_id
507 of the original transaction*/
508 l_prev_rec.reverse_id := l_in_tran_rec.poc_trans_id;
509
510 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
511 gme_debug.put_line
512 ( g_pkg_name
513 || '.'
514 || l_api_name
515 || ' UPD action code: poc_trans_id of the original transaction is '
516 || l_in_tran_rec.poc_trans_id);
517 END IF;
518
519 l_trans_date := l_prev_rec.trans_date;
520
521 IF NOT gme_common_pvt.close_period_check_flexible
522 (p_org_id => l_org_id
523 ,p_trans_date => l_trans_date
524 ,x_trans_date => l_prev_rec.trans_date
525 ,x_period_id => l_period_id) THEN
526 RAISE fnd_api.g_exc_error;
527 END IF;
528
529 IF NOT gme_resource_txns_dbl.insert_row (l_prev_rec
530 ,l_prev_rec) THEN
531 RAISE insert_failure;
532 ELSE
533 --Bug#4917189 Susruth D. Added below updated to make sure only we update the reverse_id but not any
534 -- other column for the original txn which is reversed.
535 -- l_in_tran_rec.reverse_id := l_prev_rec.poc_trans_id;
536
537 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
538 gme_debug.put_line
539 ( g_pkg_name
540 || '.'
541 || l_api_name
542 || ' UPD action code: poc_trans_id of the reversal txn is '
543 || l_prev_rec.poc_trans_id);
544 END IF;
545 /*
546 IF NOT gme_resource_txns_dbl.update_row (l_in_tran_rec) THEN
547 RAISE update_failure;
548 END IF;*/
549 UPDATE gme_resource_txns
550 SET reverse_id = l_prev_rec.poc_trans_id
551 WHERE poc_trans_id = l_in_tran_rec.poc_trans_id;
552
553
554 --
555 -- Bug 5903208 - Make call to GMF for actual costing
556 --
557 GMF_LAYERS.Create_Resource_Layers
558 ( p_api_version => 1.0,
559 p_init_msg_list => FND_API.G_FALSE,
560 p_rsrc_rec => l_prev_rec,
561 p_doc_qty => l_prev_rec.resource_usage,
562 p_doc_um => l_prev_rec.trans_qty_um,
563 x_return_status => l_return_status,
564 x_msg_count => l_msg_count,
565 x_msg_data => l_msg_data);
566
567 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
568 THEN
569 RAISE gmf_cost_failure;
570 END IF;
571 -- End Bug 5903208
572
573 END IF;
574
575 /* Post the actual transaction */
576 l_tran_rec.posted_ind := 0;
577 l_trans_date := l_tran_rec.trans_date;
578
579 IF NOT gme_common_pvt.close_period_check_flexible
580 (p_org_id => l_org_id
581 ,p_trans_date => l_trans_date
582 ,x_trans_date => l_tran_rec.trans_date
583 ,x_period_id => l_period_id) THEN
584 RAISE fnd_api.g_exc_error;
585 END IF;
586
587 IF NOT gme_resource_txns_dbl.insert_row (l_tran_rec
588 ,l_tran_rec) THEN
589 RAISE insert_failure;
590 END IF;
591
592
593 --
594 -- Bug 5903208 - Make call to GMF for actual costing
595 --
596 GMF_LAYERS.Create_Resource_Layers
597 ( p_api_version => 1.0,
598 p_init_msg_list => FND_API.G_FALSE,
599 p_rsrc_rec => l_tran_rec,
600 p_doc_qty => l_tran_rec.resource_usage,
601 p_doc_um => l_tran_rec.trans_qty_um,
602 x_return_status => l_return_status,
603 x_msg_count => l_msg_count,
604 x_msg_data => l_msg_data);
605
606 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
607 THEN
608 RAISE gmf_cost_failure;
609 END IF;
610 -- End Bug 5903208
611
612 ELSIF NOT gme_resource_txns_dbl.update_row (l_tran_rec) THEN
613 RAISE update_failure;
614 END IF;
615 ELSIF (l_resource_rec.action_code = 'DEL') THEN
616 IF l_resource_rec.completed_ind = 1 THEN
617 IF l_tran_rec.resource_usage = 0 THEN
618 l_tran_rec.delete_mark := 1;
619
620 IF NOT gme_resource_txns_dbl.delete_row (l_tran_rec) THEN
621 RAISE update_failure;
622 END IF;
623 ELSE
624 l_tran_rec.resource_usage :=
625 (-1) * l_tran_rec.resource_usage;
626 l_tran_rec.posted_ind := 0;
627
628 /*Populating the reverse_id of the reversal transaction with the poc_trans_id
629 of the original transaction*/
630 OPEN cur_get_prev_rec (l_resource_rec.poc_trans_id);
631
632 FETCH cur_get_prev_rec
633 INTO l_in_tran_rec;
634
635 CLOSE cur_get_prev_rec;
636
637 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
638 gme_debug.put_line
639 ( g_pkg_name
640 || '.'
641 || l_api_name
642 || ' DEL action code: poc_trans_id of the original transaction is '
643 || l_in_tran_rec.poc_trans_id);
644 END IF;
645
646 l_tran_rec.reverse_id := l_tran_rec.poc_trans_id;
647 l_trans_date := l_tran_rec.trans_date;
648
649 IF NOT gme_common_pvt.close_period_check_flexible
650 (p_org_id => l_org_id
651 ,p_trans_date => l_trans_date
652 ,x_trans_date => l_tran_rec.trans_date
653 ,x_period_id => l_period_id) THEN
654 RAISE fnd_api.g_exc_error;
655 END IF;
656
657 IF NOT gme_resource_txns_dbl.insert_row (l_tran_rec
658 ,l_tran_rec) THEN
659 RAISE insert_failure;
660 ELSE
661 --Bug#4917189 Susruth D. Added below updated to make sure only we update the reverse_id but not any
662 -- other column for the original txn which is reversed.
663 /*l_in_tran_rec.reverse_id := l_tran_rec.poc_trans_id;*/
664
665 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
666 gme_debug.put_line
667 ( g_pkg_name
668 || '.'
669 || l_api_name
670 || ' DEL action code: poc_trans_id of the reversal txn is '
671 || l_tran_rec.poc_trans_id);
672 END IF;
673
674 /*IF NOT gme_resource_txns_dbl.update_row (l_in_tran_rec) THEN
675 RAISE update_failure;
676 END IF;*/
677 UPDATE gme_resource_txns
678 SET reverse_id = l_tran_rec.poc_trans_id
679 WHERE poc_trans_id = l_in_tran_rec.poc_trans_id;
680
681 --
682 -- Bug 5903208 - Make call to GMF for actual costing
683 --
684 GMF_LAYERS.Create_Resource_Layers
685 ( p_api_version => 1.0,
686 p_init_msg_list => FND_API.G_FALSE,
687 p_rsrc_rec => l_tran_rec,
688 p_doc_qty => l_tran_rec.resource_usage,
689 p_doc_um => l_tran_rec.trans_qty_um,
690 x_return_status => l_return_status,
691 x_msg_count => l_msg_count,
692 x_msg_data => l_msg_data);
693
694 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
695 THEN
696 RAISE gmf_cost_failure;
697 END IF;
698 -- End Bug 5903208
699
700 END IF;
701 END IF;
702 ELSE
703 IF NOT gme_resource_txns_dbl.delete_row (l_tran_rec) THEN
704 RAISE update_failure;
705 END IF;
706 END IF;
707 END IF;
708 END LOOP;
709
710 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
711 gme_debug.display_resource_gtmp (NULL, NULL, p_batch_id);
712 END IF;
713
714 x_return_status := l_return_status;
715
716 IF g_debug <= gme_debug.g_log_procedure THEN
717 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
718 END IF;
719 EXCEPTION
720 WHEN fnd_api.g_exc_error THEN
721 x_return_status := fnd_api.g_ret_sts_error;
722 WHEN insert_failure THEN
723 x_return_status := fnd_api.g_ret_sts_error;
724 WHEN update_failure THEN
725 x_return_status := fnd_api.g_ret_sts_error;
726 WHEN OTHERS THEN
727 IF g_debug <= gme_debug.g_log_unexpected THEN
728 gme_debug.put_line ( 'When others exception in '
729 || g_pkg_name
730 || '.'
731 || l_api_name
732 || ' Error is '
733 || SQLERRM);
734 END IF;
735
736 x_return_status := fnd_api.g_ret_sts_unexp_error;
737 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
738 END consolidate_batch_resources;
739
740 /*===========================================================================================
741 Procedure
742 build_resource_tran
743 Description
744 This particular procedure is used to build a transaction rec from the temporary tblle rec.
745 Parameters
746 p_tmp_rec The resource transaction rec of the temporary tblle
747 p_resource_rec The resource transaction rec.
748 =============================================================================================*/
749 PROCEDURE build_resource_tran (
750 p_tmp_rec IN gme_resource_txns_gtmp%ROWTYPE
751 ,p_resource_rec OUT NOCOPY gme_resource_txns%ROWTYPE)
752 IS
753 l_api_name CONSTANT VARCHAR2 (30) := 'build_resource_tran';
754 BEGIN
755 IF g_debug <= gme_debug.g_log_procedure THEN
756 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
757 || l_api_name);
758 END IF;
759
760 p_resource_rec.organization_id := p_tmp_rec.organization_id;
761 p_resource_rec.poc_trans_id := p_tmp_rec.poc_trans_id;
762 p_resource_rec.orgn_code := p_tmp_rec.orgn_code;
763 p_resource_rec.doc_type := p_tmp_rec.doc_type;
764 p_resource_rec.doc_id := p_tmp_rec.doc_id;
765 p_resource_rec.line_type := p_tmp_rec.line_type;
766 p_resource_rec.line_id := p_tmp_rec.line_id;
767 p_resource_rec.resources := p_tmp_rec.resources;
768 p_resource_rec.resource_usage := p_tmp_rec.resource_usage;
769 p_resource_rec.trans_qty_um := p_tmp_rec.trans_um;
770 p_resource_rec.trans_date := p_tmp_rec.trans_date;
771 p_resource_rec.completed_ind := p_tmp_rec.completed_ind;
772 p_resource_rec.event_id := p_tmp_rec.event_id;
773 p_resource_rec.posted_ind := p_tmp_rec.posted_ind;
774 p_resource_rec.overrided_protected_ind
775 := p_tmp_rec.overrided_protected_ind;
776 p_resource_rec.reason_code := p_tmp_rec.reason_code;
777 p_resource_rec.reason_id := p_tmp_rec.reason_id;
778 p_resource_rec.start_date := p_tmp_rec.start_date;
779 p_resource_rec.end_date := p_tmp_rec.end_date;
780 p_resource_rec.creation_date := gme_common_pvt.g_timestamp;
781 p_resource_rec.last_update_date := gme_common_pvt.g_timestamp;
782 p_resource_rec.created_by := gme_common_pvt.g_user_ident;
783 p_resource_rec.last_updated_by := gme_common_pvt.g_user_ident;
784 p_resource_rec.last_update_login := gme_common_pvt.g_login_id;
785 p_resource_rec.delete_mark := p_tmp_rec.delete_mark;
786 p_resource_rec.text_code := p_tmp_rec.text_code;
787 p_resource_rec.sequence_dependent_ind
788 := p_tmp_rec.sequence_dependent_ind;
789 p_resource_rec.instance_id := p_tmp_rec.instance_id;
790 p_resource_rec.instance_id := p_tmp_rec.instance_id;
791 -- Pawan Added attribute for flex field implemenation
792 p_resource_rec.attribute_category := p_tmp_rec.attribute_category;
793 p_resource_rec.attribute1 := p_tmp_rec.attribute1;
794 p_resource_rec.attribute2 := p_tmp_rec.attribute2;
795 p_resource_rec.attribute3 := p_tmp_rec.attribute3;
796 p_resource_rec.attribute4 := p_tmp_rec.attribute4;
797 p_resource_rec.attribute5 := p_tmp_rec.attribute5;
798 p_resource_rec.attribute6 := p_tmp_rec.attribute6;
799 p_resource_rec.attribute7 := p_tmp_rec.attribute7;
800 p_resource_rec.attribute8 := p_tmp_rec.attribute8;
801 p_resource_rec.attribute9 := p_tmp_rec.attribute9;
802 p_resource_rec.attribute10 := p_tmp_rec.attribute10;
803 p_resource_rec.attribute11 := p_tmp_rec.attribute11;
804 p_resource_rec.attribute12 := p_tmp_rec.attribute12;
805 p_resource_rec.attribute13 := p_tmp_rec.attribute13;
806 p_resource_rec.attribute14 := p_tmp_rec.attribute14;
807 p_resource_rec.attribute15 := p_tmp_rec.attribute15;
808 p_resource_rec.attribute16 := p_tmp_rec.attribute16;
809 p_resource_rec.attribute17 := p_tmp_rec.attribute17;
810 p_resource_rec.attribute18 := p_tmp_rec.attribute18;
811 p_resource_rec.attribute19 := p_tmp_rec.attribute19;
812 p_resource_rec.attribute20 := p_tmp_rec.attribute20;
813 p_resource_rec.attribute21 := p_tmp_rec.attribute21;
814 p_resource_rec.attribute22 := p_tmp_rec.attribute22;
815 p_resource_rec.attribute23 := p_tmp_rec.attribute23;
816 p_resource_rec.attribute24 := p_tmp_rec.attribute24;
817 p_resource_rec.attribute25 := p_tmp_rec.attribute25;
818 p_resource_rec.attribute26 := p_tmp_rec.attribute26;
819 p_resource_rec.attribute27 := p_tmp_rec.attribute27;
820 p_resource_rec.attribute28 := p_tmp_rec.attribute28;
821 p_resource_rec.attribute29 := p_tmp_rec.attribute29;
822 p_resource_rec.attribute30 := p_tmp_rec.attribute30;
823
824
825
826
827
828
829
830 IF g_debug <= gme_debug.g_log_procedure THEN
831 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
832 END IF;
833 --Bug2804440
834 EXCEPTION
835 WHEN OTHERS THEN
836 IF g_debug <= gme_debug.g_log_unexpected THEN
837 gme_debug.put_line ( 'When others exception in '
838 || g_pkg_name
839 || '.'
840 || l_api_name
841 || ' Error is '
842 || SQLERRM);
843 END IF;
844
845 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
846 --End Bug2804440
847 END build_resource_tran;
848
849 /*===========================================================================================
850 Procedure
851 fetch_active_resources
852 Description
853 This particular procedure is used to fetch the active resources for a particular line or doc
854 or trans id from the temporary tblle.
855 Parameters
856 p_resource_rec The resource transaction rec
857 x_resource_tbl All the resource transactions pertaining to the criteria.
858 x_return_status outcome of the API call
859 S - Success
860 E - Error
861 U - Unexpected error
862 History
863 Namit Singhi. Bug#5609683. Added parameter p_calling_mode.
864 =============================================================================================*/
865 PROCEDURE fetch_active_resources (
866 p_resource_rec IN gme_resource_txns_gtmp%ROWTYPE
867 ,p_calling_mode IN VARCHAR2 DEFAULT NULL --bug#5609683
868 ,x_resource_tbl OUT NOCOPY gme_common_pvt.resource_transactions_tab
869 ,x_return_status OUT NOCOPY VARCHAR2)
870 IS
871 TYPE query_ref IS REF CURSOR;
872
873 get_rsrc query_ref;
874 l_where VARCHAR2 (2000);
875 l_api_name CONSTANT VARCHAR2 (30) := 'FETCH_ACTIVE_RESOURCES';
876 l_return_status VARCHAR2 (1) := fnd_api.g_ret_sts_success;
877 l_line_index BINARY_INTEGER := 1;
878 l_cursor BINARY_INTEGER;
879 bad_keys EXCEPTION;
880 BEGIN
881 IF g_debug <= gme_debug.g_log_procedure THEN
882 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
883 || l_api_name);
884 END IF;
885
886 /* Initialize API return status to sucess */
887 x_return_status := fnd_api.g_ret_sts_success;
888
889 -- Determine if any of the key values are present
890 IF (p_resource_rec.poc_trans_id IS NOT NULL) THEN
891 l_where := 'POC_TRANS_ID =:poc_trans_id ';
892 l_cursor := 1;
893 ELSIF (p_resource_rec.line_id IS NOT NULL) THEN
894 l_where := ' LINE_ID =:line_id';
895 l_cursor := 2;
896 ELSIF (p_resource_rec.doc_id IS NOT NULL) THEN
897 l_where := ' DOC_ID =:doc_id';
898 l_cursor := 3;
899 ELSE
900 RAISE bad_keys;
901 END IF;
902
903 l_where :=
904 l_where
905 || ' AND action_code NOT IN ('
906 || ''''
907 || 'REVS'
908 || ''''
909 || ','
910 || ''''
911 || 'REVL'
912 || ''''
913 || ','
914 || ''''
915 || 'DEL'
916 || ''''
917 || ')'
918 --nsinghi bug#5609683
919 -- If called from gme_update_step_qty_pvt.reduce_pending_usage, then we need to order the resources by usage ASC
920 -- so that the remaining usage gets factored in for the remaining pending resource txn.
921 -- || ' AND delete_mark <> 1 ORDER BY completed_ind, poc_trans_id ';
922 || ' AND delete_mark <> 1 ';
923
924 IF p_calling_mode = 'REDUCE_USAGE' THEN
925 l_where := l_where || ' ORDER BY completed_ind, resource_usage,poc_trans_id ';
926 ELSE
927 l_where := l_where || ' ORDER BY completed_ind, poc_trans_id ';
928 END IF;
929
930 IF l_cursor = 1 THEN
931 OPEN get_rsrc
932 FOR ' SELECT * FROM GME_RESOURCE_TXNS_GTMP
933 WHERE '
934 || l_where USING p_resource_rec.poc_trans_id;
935 ELSIF l_cursor = 2 THEN
936 OPEN get_rsrc
937 FOR ' SELECT * FROM GME_RESOURCE_TXNS_GTMP
938 WHERE '
939 || l_where USING p_resource_rec.line_id;
940 ELSIF l_cursor = 3 THEN
941 OPEN get_rsrc
942 FOR ' SELECT * FROM GME_RESOURCE_TXNS_GTMP
943 WHERE '
944 || l_where USING p_resource_rec.doc_id;
945 END IF;
946
947 LOOP
948 FETCH get_rsrc
949 INTO x_resource_tbl (l_line_index);
950
951 EXIT WHEN get_rsrc%NOTFOUND;
952 l_line_index := l_line_index + 1;
953 END LOOP;
954
955 CLOSE get_rsrc;
956
957 IF g_debug <= gme_debug.g_log_procedure THEN
958 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
959 END IF;
960 EXCEPTION
961 WHEN fnd_api.g_exc_error THEN
962 x_return_status := fnd_api.g_ret_sts_error;
963 WHEN fnd_api.g_exc_unexpected_error THEN
964 x_return_status := fnd_api.g_ret_sts_unexp_error;
965 WHEN bad_keys THEN
966 x_return_status := fnd_api.g_ret_sts_error;
967 WHEN OTHERS THEN
968 IF g_debug <= gme_debug.g_log_unexpected THEN
969 gme_debug.put_line ( 'When others exception in '
970 || g_pkg_name
971 || '.'
972 || l_api_name
973 || ' Error is '
974 || SQLERRM);
975 END IF;
976
977 x_return_status := fnd_api.g_ret_sts_unexp_error;
978 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
979 END fetch_active_resources;
980
981 /*===========================================================================================
982 Procedure
983 resource_dtl_process
984 Description
985 This particular procedure is used to process the resource detail.
986 Parameters
987 p_step_resources_rec The batch step resource rec to identify the resource
988 p_action_code Action to peform the resource rec
989 x_step_resources_rec The batch step resources rec.
990 x_return_status outcome of the API call
991 S - Success
992 E - Error
993 U - Unexpected error
994 HISTORY
995 Bharati Satpute Bug2188136 21/03/2002 Added code for action_code 'INSERT'
996
997 =============================================================================================*/
998 PROCEDURE resource_dtl_process (
999 p_step_resources_rec IN gme_batch_step_resources%ROWTYPE
1000 ,p_action_code IN VARCHAR2
1001 ,p_check_prim_rsrc IN BOOLEAN := FALSE
1002 ,x_step_resources_rec OUT NOCOPY gme_batch_step_resources%ROWTYPE
1003 ,x_return_status OUT NOCOPY VARCHAR2)
1004 IS
1005 l_api_name CONSTANT VARCHAR2 (30) := 'resource_dtl_process';
1006 l_batch_header gme_batch_header%ROWTYPE;
1007 l_batch_step gme_batch_steps%ROWTYPE;
1008 l_step_activity gme_batch_step_activities%ROWTYPE;
1009 l_step_resources gme_batch_step_resources%ROWTYPE;
1010 l_resource_txns gme_resource_txns_gtmp%ROWTYPE;
1011 l_resource_tbl gme_common_pvt.resource_transactions_tab;
1012 l_return_status VARCHAR2 (1);
1013 l_rsrc_usage NUMBER;
1014 l_rsrc_count NUMBER;
1015 l_tot_usage NUMBER;
1016 l_completed NUMBER (5);
1017
1018 x_batch_step gme_batch_steps%ROWTYPE;
1019
1020 CURSOR cur_sum_usage (v_batchstep_resource_id NUMBER, v_completed NUMBER)
1021 IS
1022 SELECT NVL (SUM (resource_usage), 0)
1023 FROM gme_resource_txns_gtmp
1024 WHERE line_id = v_batchstep_resource_id
1025 AND completed_ind = v_completed
1026 AND (v_completed = 1 OR sequence_dependent_ind = 0);
1027
1028 --Bug#5231180 Begin added the following cursors
1029 CURSOR cur_get_rsrc (v_rsrc cr_rsrc_mst.resources%TYPE)
1030 IS
1031 SELECT max_capacity, capacity_um
1032 FROM cr_rsrc_mst
1033 WHERE resources = v_rsrc
1034 AND capacity_constraint = 1;
1035
1036 CURSOR cur_get_charge_rsrc(v_step_id gme_batch_steps.batchstep_id%TYPE, v_rsrc cr_rsrc_mst.resources%TYPE)
1037 IS
1038 SELECT 1
1039 FROM DUAL
1040 WHERE EXISTS (SELECT 1
1041 FROM gme_batch_step_charges
1042 WHERE batchstep_id = v_step_id
1043 AND resources = v_rsrc);
1044 --Bug#5231180 End
1045 l_rsrc_rec cur_get_rsrc%ROWTYPE;
1046 l_exists NUMBER;
1047 l_temp_qty NUMBER;
1048 error_in_conversion EXCEPTION;
1049 error_in_clear_charges EXCEPTION;
1050
1051 -- Bug 5903208
1052 gmf_cost_failure EXCEPTION;
1053 l_message_count NUMBER;
1054 l_message_list VARCHAR2(2000);
1055
1056 BEGIN
1057 IF g_debug <= gme_debug.g_log_procedure THEN
1058 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
1059 || l_api_name);
1060 END IF;
1061
1062 /* There is no public layer for this package so adding initialize right here *
1063 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
1064 gme_debug.log_initialize('RESOURCE_TXN');
1065 END IF;
1066
1067 /* Initialize return status to success */
1068 x_return_status := fnd_api.g_ret_sts_success;
1069 x_step_resources_rec := p_step_resources_rec;
1070 l_batch_step.batchstep_id := x_step_resources_rec.batchstep_id;
1071
1072 IF NOT gme_batch_steps_dbl.fetch_row (p_batch_step => l_batch_step
1073 ,x_batch_step => l_batch_step) THEN
1074 RAISE fnd_api.g_exc_error;
1075 END IF;
1076
1077 l_step_activity.batchstep_activity_id :=
1078 p_step_resources_rec.batchstep_activity_id;
1079
1080 IF NOT gme_batch_step_activities_dbl.fetch_row
1081 (p_batch_step_activities => l_step_activity
1082 ,x_batch_step_activities => l_step_activity) THEN
1083 RAISE fnd_api.g_exc_error;
1084 END IF;
1085
1086 l_batch_header.batch_id := x_step_resources_rec.batch_id;
1087
1088 IF NOT gme_batch_header_dbl.fetch_row (p_batch_header => l_batch_header
1089 ,x_batch_header => l_batch_header) THEN
1090 RAISE fnd_api.g_exc_error;
1091 END IF;
1092
1093 /* If automatic step qty calculation is set for the batch then */
1094 /* we have to compute the usage fields */
1095 IF (p_action_code = 'INSERT')
1096 AND (l_batch_header.automatic_step_calculation = 1) THEN
1097 /* If the step status is greater than pending then */
1098 /* we have to calculate the actual quantities */
1099 IF l_batch_step.step_status > 2 THEN
1100 x_step_resources_rec.actual_rsrc_qty :=
1101 l_batch_step.actual_step_qty;
1102 x_step_resources_rec.original_rsrc_qty :=
1103 l_batch_step.actual_step_qty;
1104 x_step_resources_rec.original_rsrc_usage :=
1105 x_step_resources_rec.actual_rsrc_usage;
1106
1107 /* If the resource scale type is calculate by charges then */
1108 IF x_step_resources_rec.scale_type = 2 THEN
1109 x_step_resources_rec.original_rsrc_usage :=
1110 ( x_step_resources_rec.actual_rsrc_usage
1111 / l_step_activity.actual_activity_factor)
1112 / l_batch_step.actual_charges;
1113 x_step_resources_rec.original_rsrc_qty :=
1114 ( (x_step_resources_rec.actual_rsrc_qty)
1115 / l_step_activity.actual_activity_factor);
1116 /* If the resource scale type is linear/fixed then */
1117 ELSE
1118 x_step_resources_rec.original_rsrc_usage :=
1119 (x_step_resources_rec.actual_rsrc_usage)
1120 / l_step_activity.actual_activity_factor;
1121 x_step_resources_rec.original_rsrc_qty :=
1122 (x_step_resources_rec.actual_rsrc_qty)
1123 / l_step_activity.actual_activity_factor;
1124 END IF; /*IF l_gme_batchstep_resources.scale_type = 1*/
1125 /* If the step status is pending then we have to update the plan quantities */
1126 ELSIF l_batch_step.step_status = 1 THEN
1127 x_step_resources_rec.plan_rsrc_qty := l_batch_step.plan_step_qty;
1128 x_step_resources_rec.original_rsrc_qty :=
1129 l_batch_step.plan_step_qty;
1130 x_step_resources_rec.original_rsrc_usage :=
1131 x_step_resources_rec.plan_rsrc_usage;
1132
1133 IF x_step_resources_rec.scale_type = 2 THEN
1134 x_step_resources_rec.original_rsrc_usage :=
1135 ( x_step_resources_rec.plan_rsrc_usage
1136 / l_step_activity.plan_activity_factor)
1137 / l_batch_step.plan_charges;
1138 x_step_resources_rec.original_rsrc_qty :=
1139 ( x_step_resources_rec.plan_rsrc_qty
1140 / l_step_activity.plan_activity_factor);
1141 /* If the resource scale type is linear/fix then */
1142 ELSE
1143 x_step_resources_rec.original_rsrc_usage :=
1144 ( (x_step_resources_rec.plan_rsrc_usage)
1145 / l_step_activity.plan_activity_factor);
1146 x_step_resources_rec.original_rsrc_qty :=
1147 (x_step_resources_rec.plan_rsrc_qty)
1148 / l_step_activity.plan_activity_factor;
1149 END IF; /*IF l_gme_batchstep_resources.scale_type = 1*/
1150 END IF; /*IF l_batch_step.step_status > 2*/
1151 END IF; /* IF p_action_code = 'INSERT' */
1152
1153 IF (l_batch_step.step_status = 1) THEN
1154 x_step_resources_rec.actual_rsrc_usage := NULL;
1155 x_step_resources_rec.actual_rsrc_qty := NULL;
1156 x_step_resources_rec.actual_rsrc_count := NULL;
1157 END IF; /* IF (l_batch_step.step_status = 1) */
1158
1159 --rishi 3446787 03/03/04 added the p_check_prim_rsrc parameter
1160 IF p_action_code IN ('INSERT', 'UPDATE') THEN
1161 validate_resource (p_batch_step_rec => l_batch_step
1162 ,p_step_activity_rec => l_step_activity
1163 ,p_step_resources_rec => x_step_resources_rec
1164 ,p_check_prim_rsrc => p_check_prim_rsrc
1165 ,x_return_status => l_return_status);
1166
1167 IF l_return_status <> x_return_status THEN
1168 RAISE fnd_api.g_exc_error;
1169 END IF;
1170 END IF;
1171
1172 /* Since this procedure is called from the forms
1173 and there is no public layer for this we can call
1174 set_timestamp here */
1175 gme_common_pvt.set_timestamp;
1176
1177 IF p_action_code = 'INSERT' THEN
1178 -- Shikha Nagar - added check to prevent second instance of primary rsrc from getting inserted
1179 -- for an activity
1180 --rishi 3446787 03/03/04
1181 -- commented the call to check_primary_resorce as it is already being called
1182 -- by validate_resource above.
1183 /*
1184 GME_RESOURCE_ENGINE_PVT.check_primary_resource (p_step_resources_rec => x_step_resources_rec
1185 ,x_return_status => l_return_status);
1186 IF l_return_status <> x_return_status THEN
1187 RAISE FND_API.g_exc_error;
1188 END IF;
1189 */
1190 IF NOT (gme_batch_step_resources_dbl.insert_row
1191 (p_batch_step_resources => x_step_resources_rec
1192 ,x_batch_step_resources => x_step_resources_rec) ) THEN
1193 RAISE fnd_api.g_exc_error;
1194 END IF;
1195 END IF;
1196
1197 IF p_action_code IN ('INSERT', 'UPDATE') THEN
1198 IF l_batch_header.update_inventory_ind = 'Y' THEN
1199 IF l_batch_step.step_status = 1 THEN
1200 gme_update_step_qty_pvt.adjust_pending_usage
1201 (p_batch_step_resources_rec => x_step_resources_rec
1202 ,x_return_status => l_return_status);
1203
1204 IF l_return_status <> x_return_status THEN
1205 RAISE fnd_api.g_exc_error;
1206 END IF;
1207 ELSIF l_batch_step.step_status > 1
1208 AND NVL (x_step_resources_rec.actual_rsrc_usage, -1) >= 0 THEN
1209 gme_update_step_qty_pvt.adjust_actual_usage
1210 (p_batch_step_resources_rec => x_step_resources_rec
1211 ,x_return_status => l_return_status);
1212
1213 IF l_return_status <> x_return_status THEN
1214 RAISE fnd_api.g_exc_error;
1215 END IF;
1216 END IF; /* IF l_batch_step.step_status = 1 */
1217 END IF; /* IF l_batch_header.update_inventory_ind = 'Y' */
1218
1219 --
1220 -- Bug 5903208 - Make call to GMF for actual costing data recording
1221 --
1222 IF p_action_code = 'INSERT' THEN
1223 GMF_VIB.Update_Batch_Requirements
1224 ( p_api_version => 1.0,
1225 p_init_msg_list => FND_API.G_FALSE,
1226 p_batch_id => l_batch_header.batch_id,
1227 x_return_status => l_return_status,
1228 x_msg_count => l_message_count,
1229 x_msg_data => l_message_list);
1230
1231 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
1232 THEN
1233 RAISE gmf_cost_failure;
1234 END IF;
1235 END IF;
1236 -- End Bug 5903208
1237
1238 ELSIF p_action_code = 'DELETE' THEN
1239 /* Remove this call as per Resource TD page 57 */
1240 /* Call to be made from GME_API_PUB.save_batch */
1241 /*
1242 GME_RESOURCE_ENGINE_PVT.check_primary_resource (p_step_resources_rec => x_step_resources_rec
1243 ,x_return_status => l_return_status);
1244 IF l_return_status <> x_return_status THEN
1245 RAISE FND_API.g_exc_error;
1246 END IF;
1247 */
1248 gme_delete_batch_step_pvt.delete_resource
1249 (p_batch_step_resources_rec => x_step_resources_rec
1250 ,x_return_status => l_return_status);
1251
1252 IF l_return_status <> x_return_status THEN
1253 RAISE fnd_api.g_exc_error;
1254 END IF;
1255
1256 -- Bug 5043868 - Make call to GMF for actual costing data
1257 -- recording when deleting a resource from the form or api.
1258 GMF_VIB.Update_Batch_Requirements
1259 ( p_api_version => 1.0,
1260 p_init_msg_list => FND_API.G_FALSE,
1261 p_batch_id => l_batch_header.batch_id,
1262 x_return_status => l_return_status,
1263 x_msg_count => l_message_count,
1264 x_msg_data => l_message_list);
1265
1266 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
1267 THEN
1268 RAISE gmf_cost_failure;
1269 END IF;
1270 -- End Bug 5903208
1271
1272 END IF; /* IF p_action_code IN ('INSERT', 'UPDATE') */
1273
1274 /* Update has to be done after updating the transactions as the adjust actual */
1275 /* usage logic depends on the previous actual usage to deduce the usage to be */
1276 /* deducted from the pending resource transactions */
1277 IF p_action_code = 'UPDATE' THEN
1278 IF NOT (gme_batch_step_resources_dbl.update_row
1279 (p_batch_step_resources => x_step_resources_rec) ) THEN
1280 RAISE fnd_api.g_exc_error;
1281 END IF;
1282
1283 x_step_resources_rec.last_updated_by := gme_common_pvt.g_user_ident;
1284 x_step_resources_rec.last_update_date := gme_common_pvt.g_timestamp;
1285 END IF;
1286
1287 /*Bug#5231180 resource insertion and deletion might result in the max step capacity
1288 if the resource is capacity constrained then we need to recalculate charges
1289 if the resource is not capacity constrained then there is no need to recalculate charges */
1290 /*Bug#6607524 Changed the hard coded 1381 to the batch header's organization id*/
1291 gme_common_pvt.g_setup_done :=
1292 gme_common_pvt.setup (p_org_id => l_batch_header.ORGANIZATION_ID --1381
1293 ,p_org_code => NULL);
1294
1295 IF NOT gme_common_pvt.g_setup_done THEN
1296 RAISE fnd_api.g_exc_error;
1297 END IF;
1298
1299 OPEN cur_get_rsrc(x_step_resources_rec.resources);
1300 FETCH cur_get_rsrc INTO l_rsrc_rec;
1301 IF cur_get_rsrc%FOUND THEN
1302 l_exists := 1;
1303 END IF;
1304 CLOSE cur_get_rsrc;
1305
1306 IF l_exists = 1 THEN /*capacity constrained resource */
1307 IF l_rsrc_rec.capacity_um <> l_batch_step.step_qty_um THEN
1308 l_temp_qty := inv_convert.inv_um_convert(item_id => 0
1309 ,PRECISION => gme_common_pvt.g_precision
1310 ,from_quantity => l_rsrc_rec.max_capacity
1311 ,from_unit => l_rsrc_rec.capacity_um
1312 ,to_unit => l_batch_step.step_qty_um
1313 ,from_name => NULL
1314 ,to_name => NULL);
1315 IF l_temp_qty < 0 THEN
1316 RAISE error_in_conversion;
1317 END IF;
1318 IF l_temp_qty < l_batch_step.max_step_capacity THEN
1319 l_batch_step.max_step_capacity := l_temp_qty;
1320 END IF;
1321 END IF;
1322
1323 IF p_action_code = 'UPDATE' THEN
1324 /* check whether resource is the one that determines charges */
1325 OPEN cur_get_charge_rsrc(l_batch_step.batchstep_id,x_step_resources_rec.resources);
1326 FETCH cur_get_charge_rsrc INTO l_exists;
1327 CLOSE cur_get_charge_rsrc;
1328 IF l_exists = 1 AND x_step_resources_rec.scale_type <> 2 THEN
1329 /* this rsrc is determining resource since scale type is not By Charge delete the charge details */
1330 gme_batch_step_chg_pvt.clear_charges(
1331 p_batch_id => l_batch_step.batch_id
1332 ,p_batchstep_id => l_batch_step.batchstep_id
1333 ,x_return_status => l_return_status);
1334
1335 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1336 RAISE error_in_clear_charges;
1337 END IF;
1338 END IF;
1339
1340 ELSIF p_action_code IN ('INSERT', 'DELETE') THEN
1341 --call recalculate charges procedure with R as p_cal_type
1342 gme_update_step_qty_pvt.recalculate_charges(
1343 p_batchstep_rec => l_batch_step
1344 ,p_cal_type => 'R'
1345 ,x_batchstep_rec => x_batch_step
1346 ,x_return_status => l_return_status );
1347
1348 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1349 RAISE fnd_api.g_exc_error;
1350 END IF;
1351 END IF; /*action code check */
1352 END IF; /*p_step_resources_rec.capacity_constraint = 1 */
1353 /*Bug#5231180 End */
1354
1355 IF g_debug <= gme_debug.g_log_procedure THEN
1356 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
1357 END IF;
1358 EXCEPTION
1359 WHEN gmf_cost_failure THEN
1360 -- Bug 5903208
1361 x_return_status := FND_API.G_RET_STS_ERROR;
1362
1363 WHEN fnd_api.g_exc_error THEN
1364 x_return_status := fnd_api.g_ret_sts_error;
1365 WHEN OTHERS THEN
1366 IF g_debug <= gme_debug.g_log_unexpected THEN
1367 gme_debug.put_line ( 'When others exception in '
1368 || g_pkg_name
1369 || '.'
1370 || l_api_name
1371 || ' Error is '
1372 || SQLERRM);
1373 END IF;
1374
1375 x_return_status := fnd_api.g_ret_sts_unexp_error;
1376 fnd_msg_pub.add_exc_msg ('GME_RESOURCE_ENGINE_PVT'
1377 ,'resource_dtl_process');
1378 END resource_dtl_process;
1379
1380 /*===========================================================================================
1381 Procedure
1382 validate_resource
1383 Description
1384 This particular procedure is used to check for the validity of the fields.
1385 Parameters
1386 p_step_resources_rec The batch step resource rec to identify the resource
1387 x_return_status outcome of the API call
1388 S - Success
1389 E - Error
1390 U - Unexpected error
1391 History
1392 Bharati Satpute Bug 2165993 1/09/2002 Incomprehensible error message when inserting a resource
1393 =============================================================================================*/
1394 PROCEDURE validate_resource (
1395 p_batch_step_rec IN gme_batch_steps%ROWTYPE
1396 ,p_step_activity_rec IN gme_batch_step_activities%ROWTYPE
1397 ,p_step_resources_rec IN gme_batch_step_resources%ROWTYPE
1398 ,p_check_prim_rsrc IN BOOLEAN := FALSE
1399 ,x_return_status OUT NOCOPY VARCHAR2)
1400 IS
1401 l_api_name CONSTANT VARCHAR2 (30) := 'validate_resource';
1402 BEGIN
1403 IF g_debug <= gme_debug.g_log_procedure THEN
1404 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
1405 || l_api_name);
1406 END IF;
1407
1408 /* Initialize return status to success */
1409 x_return_status := fnd_api.g_ret_sts_success;
1410
1411 IF ( p_batch_step_rec.step_status = 1
1412 AND p_step_resources_rec.plan_start_date IS NULL) THEN
1413 gme_common_pvt.log_message ('GME_START_DATE_REQD');
1414 RAISE fnd_api.g_exc_error;
1415 END IF;
1416
1417 IF p_batch_step_rec.step_status = 1
1418 AND p_step_resources_rec.plan_rsrc_usage IS NULL THEN
1419 gme_common_pvt.log_message ('GME_PLAN_RSRC_REQD');
1420 RAISE fnd_api.g_exc_error;
1421 END IF;
1422
1423 IF ( p_batch_step_rec.step_status = 1
1424 AND p_step_resources_rec.plan_rsrc_qty IS NULL) THEN
1425 gme_common_pvt.log_message ('GME_RSRC_QTY_REQD');
1426 RAISE fnd_api.g_exc_error;
1427 END IF;
1428
1429 IF ( p_batch_step_rec.step_status = 1
1430 AND p_step_resources_rec.plan_rsrc_count IS NULL) THEN
1431 gme_common_pvt.log_message ('GME_RSRC_COUNT_REQD');
1432 RAISE fnd_api.g_exc_error;
1433 END IF;
1434
1435 IF ( p_batch_step_rec.step_status > 2
1436 AND p_step_resources_rec.actual_rsrc_qty IS NULL) THEN
1437 gme_common_pvt.log_message ('GME_RSRC_QTY_REQD');
1438 RAISE fnd_api.g_exc_error;
1439 END IF;
1440
1441 IF ( p_batch_step_rec.step_status > 2
1442 AND p_step_resources_rec.actual_rsrc_count IS NULL) THEN
1443 gme_common_pvt.log_message ('GME_RSRC_COUNT_REQD');
1444 RAISE fnd_api.g_exc_error;
1445 END IF;
1446
1447 /* Let us check for the validitiy of the plan and actual dates */
1448 IF p_step_resources_rec.plan_start_date IS NOT NULL
1449 AND p_step_resources_rec.plan_cmplt_date IS NOT NULL THEN
1450 IF p_step_resources_rec.plan_start_date >
1451 p_step_resources_rec.plan_cmplt_date THEN
1452 gme_common_pvt.log_message ('PM_BADSTARTDATE');
1453 RAISE fnd_api.g_exc_error;
1454 END IF;
1455 END IF;
1456
1457 IF p_step_resources_rec.actual_start_date IS NOT NULL
1458 AND p_step_resources_rec.actual_cmplt_date IS NOT NULL THEN
1459 IF p_step_resources_rec.actual_start_date >
1460 p_step_resources_rec.actual_cmplt_date THEN
1461 gme_common_pvt.log_message ('PM_BADSTARTDATE');
1462 RAISE fnd_api.g_exc_error;
1463 END IF;
1464 END IF;
1465
1466 IF p_step_resources_rec.plan_start_date IS NOT NULL
1467 AND ( p_step_resources_rec.plan_start_date <
1468 p_step_activity_rec.plan_start_date
1469 OR p_step_resources_rec.plan_start_date >
1470 p_step_activity_rec.plan_cmplt_date) THEN
1471 gme_common_pvt.log_message
1472 ('GME_RSRC_PLAN_DATE'
1473 ,'START_DATE'
1474 ,fnd_date.date_to_displaydt (p_step_activity_rec.plan_start_date)
1475 ,'END_DATE'
1476 ,fnd_date.date_to_displaydt (p_step_activity_rec.plan_cmplt_date) );
1477 RAISE fnd_api.g_exc_error;
1478 END IF;
1479
1480 IF p_step_resources_rec.plan_cmplt_date IS NOT NULL
1481 AND p_step_resources_rec.plan_cmplt_date >
1482 p_step_activity_rec.plan_cmplt_date THEN
1483 gme_common_pvt.log_message
1484 ('GME_RSRC_PLAN_DATE'
1485 ,'START_DATE'
1486 ,fnd_date.date_to_displaydt (p_step_activity_rec.plan_start_date)
1487 ,'END_DATE'
1488 ,fnd_date.date_to_displaydt (p_step_activity_rec.plan_cmplt_date) );
1489 RAISE fnd_api.g_exc_error;
1490 END IF;
1491
1492 IF p_step_resources_rec.actual_start_date IS NOT NULL
1493 AND ( p_step_resources_rec.actual_start_date <
1494 p_step_activity_rec.actual_start_date
1495 OR p_step_resources_rec.actual_start_date >
1496 p_step_activity_rec.actual_cmplt_date) THEN
1497 gme_common_pvt.log_message
1498 ('GME_RSRC_ACTUAL_DATE'
1499 ,'START_DATE'
1500 ,fnd_date.date_to_displaydt (p_step_activity_rec.actual_start_date)
1501 ,'END_DATE'
1502 ,fnd_date.date_to_displaydt
1503 (NVL (p_step_activity_rec.actual_cmplt_date
1504 ,p_step_activity_rec.actual_start_date) ) );
1505 RAISE fnd_api.g_exc_error;
1506 END IF;
1507
1508 IF p_step_resources_rec.actual_cmplt_date IS NOT NULL
1509 AND p_step_resources_rec.actual_cmplt_date >
1510 p_step_activity_rec.actual_cmplt_date THEN
1511 gme_common_pvt.log_message
1512 ('GME_RSRC_ACTUAL_DATE'
1513 ,'START_DATE'
1514 ,fnd_date.date_to_displaydt (p_step_activity_rec.actual_start_date)
1515 ,'END_DATE'
1516 ,fnd_date.date_to_displaydt (p_step_activity_rec.actual_cmplt_date) );
1517 RAISE fnd_api.g_exc_error;
1518 END IF;
1519
1520 /* Only one primary resource is allowed per activity let us check for it now */
1521 /* Remove this call as per Resource TD page 57 */
1522 /* Call to be made from GME_API_PUB.save_batch */
1523 /*
1524 IF p_check_prim_rsrc THEN
1525 check_primary_resource (p_step_resources_rec => p_step_resources_rec
1526 ,x_return_status => x_return_status);
1527 END IF;
1528 */
1529 IF g_debug <= gme_debug.g_log_procedure THEN
1530 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
1531 END IF;
1532 EXCEPTION
1533 WHEN fnd_api.g_exc_error THEN
1534 x_return_status := fnd_api.g_ret_sts_error;
1535 WHEN OTHERS THEN
1536 IF g_debug <= gme_debug.g_log_unexpected THEN
1537 gme_debug.put_line ( 'When others exception in '
1538 || g_pkg_name
1539 || '.'
1540 || l_api_name
1541 || ' Error is '
1542 || SQLERRM);
1543 END IF;
1544
1545 x_return_status := fnd_api.g_ret_sts_unexp_error;
1546 fnd_msg_pub.add_exc_msg ('GME_RESOURCE_ENGINE_PVT'
1547 ,'VALIDATE_RESOURCE');
1548 END validate_resource;
1549
1550 /*===========================================================================================
1551 Procedure
1552 check_primary_resource
1553 Description
1554 This particular procedure is used to check for the existence of one and only one primary
1555 resource associated with the activity.
1556 Parameters
1557 p_step_resources_rec The batch step resource rec to identify the resource
1558 x_return_status outcome of the API call
1559 S - Success
1560 E - Error
1561 U - Unexpected error
1562 =============================================================================================*/
1563 PROCEDURE check_primary_resource (
1564 p_step_resources_rec IN gme_batch_step_resources%ROWTYPE
1565 ,x_return_status OUT NOCOPY VARCHAR2)
1566 IS
1567 l_api_name CONSTANT VARCHAR2 (30) := 'check_primary_resource';
1568
1569 CURSOR cur_get_prim_rsrc (
1570 v_batchstep_activity_id NUMBER
1571 ,v_batchstep_resource_id NUMBER)
1572 IS
1573 SELECT COUNT (1)
1574 FROM gme_batch_step_resources
1575 WHERE batchstep_resource_id <> NVL (v_batchstep_resource_id, -1)
1576 AND batchstep_activity_id = v_batchstep_activity_id
1577 AND prim_rsrc_ind = 1;
1578
1579 --Rishi 3446787/3020345 start
1580 CURSOR cur_get_activity (v_batchstep_activity_id NUMBER)
1581 IS
1582 SELECT activity
1583 FROM gme_batch_step_activities
1584 WHERE batchstep_activity_id = v_batchstep_activity_id;
1585
1586 CURSOR cur_get_step_id (v_batchstep_activity_id NUMBER)
1587 IS
1588 SELECT batchstep_id
1589 FROM gme_batch_step_activities
1590 WHERE batchstep_activity_id = v_batchstep_activity_id;
1591
1592 CURSOR cur_get_batchstep_no (v_batchstep_id NUMBER)
1593 IS
1594 SELECT batchstep_no
1595 FROM gme_batch_steps
1596 WHERE batchstep_id = v_batchstep_id;
1597
1598 l_activity gme_batch_step_activities.activity%TYPE;
1599 l_batchstep_no gme_batch_steps.batchstep_no%TYPE;
1600 l_step_id gme_batch_steps.batchstep_id%TYPE;
1601 --Rishi 3446787/3020345 end
1602 l_count NUMBER (5);
1603 BEGIN
1604 IF g_debug <= gme_debug.g_log_procedure THEN
1605 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
1606 || l_api_name);
1607 END IF;
1608
1609 /* Initialize return status to success */
1610 x_return_status := fnd_api.g_ret_sts_success;
1611
1612 OPEN cur_get_prim_rsrc (p_step_resources_rec.batchstep_activity_id
1613 ,p_step_resources_rec.batchstep_resource_id);
1614
1615 FETCH cur_get_prim_rsrc
1616 INTO l_count;
1617
1618 CLOSE cur_get_prim_rsrc;
1619
1620 --Rishi 3446787/3020345 start
1621 OPEN cur_get_activity (p_step_resources_rec.batchstep_activity_id);
1622
1623 FETCH cur_get_activity
1624 INTO l_activity;
1625
1626 CLOSE cur_get_activity;
1627
1628 OPEN cur_get_step_id (p_step_resources_rec.batchstep_activity_id);
1629
1630 FETCH cur_get_step_id
1631 INTO l_step_id;
1632
1633 CLOSE cur_get_step_id;
1634
1635 OPEN cur_get_batchstep_no (l_step_id);
1636
1637 FETCH cur_get_batchstep_no
1638 INTO l_batchstep_no;
1639
1640 CLOSE cur_get_batchstep_no;
1641
1642 --Rishi 3446787/3020345 end
1643 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1644 gme_debug.put_line ( ' Activity:'
1645 || p_step_resources_rec.batchstep_activity_id
1646 || ' Resource:'
1647 || p_step_resources_rec.batchstep_resource_id
1648 || ' Count:'
1649 || l_count);
1650 END IF;
1651
1652 IF (l_count > 0) AND (NVL (p_step_resources_rec.prim_rsrc_ind, 0) = 1) THEN
1653 fnd_message.set_name ('GME', 'GME_ONE_PRIM_RSRC');
1654 --Rishi 3446787/3020345
1655 fnd_message.set_token ('ACTIVITY', l_activity);
1656 fnd_message.set_token ('STEPNO', l_batchstep_no);
1657 fnd_msg_pub.ADD;
1658 RAISE fnd_api.g_exc_error;
1659 ELSIF (l_count = 0) AND NVL (p_step_resources_rec.prim_rsrc_ind, 0) = 0 THEN
1660 fnd_message.set_name ('GME', 'GME_MIN_ONE_PRIM_RSRC');
1661 fnd_message.set_token ('ACTIVITY', l_activity);
1662 fnd_message.set_token ('STEPNO', l_batchstep_no);
1663 --Rishi 3446787/3020345 end
1664 fnd_msg_pub.ADD;
1665 RAISE fnd_api.g_exc_error;
1666 END IF;
1667
1668 IF g_debug <= gme_debug.g_log_procedure THEN
1669 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
1670 END IF;
1671 EXCEPTION
1672 WHEN fnd_api.g_exc_error THEN
1673 x_return_status := fnd_api.g_ret_sts_error;
1674 WHEN OTHERS THEN
1675 IF g_debug <= gme_debug.g_log_unexpected THEN
1676 gme_debug.put_line ( 'When others exception in '
1677 || g_pkg_name
1678 || '.'
1679 || l_api_name
1680 || ' Error is '
1681 || SQLERRM);
1682 END IF;
1683
1684 x_return_status := fnd_api.g_ret_sts_unexp_error;
1685 fnd_msg_pub.add_exc_msg ('GME_RESOURCE_ENGINE_PVT'
1686 ,'CHECK_PRIMARY_RESOURCE');
1687 END check_primary_resource;
1688
1689 /*===========================================================================================
1690 Procedure
1691 check_primary_resource
1692 Description
1693 This procedure is used to check for the existence of one and only one primary resource.
1694 Parameters
1695 p_batch_id Batch row identifier
1696 p_batchstep_id Batchstep row identifier
1697 x_return_status outcome of the API call
1698 S - Success
1699 E - Error
1700 U - Unexpected error
1701 =============================================================================================*/
1702 PROCEDURE check_primary_resource (
1703 p_batch_id IN NUMBER
1704 ,p_batchstep_id IN NUMBER
1705 ,x_return_status OUT NOCOPY VARCHAR2)
1706 IS
1707 CURSOR cur_get_batch_steps
1708 IS
1709 SELECT batch_id, batchstep_no, batchstep_id
1710 FROM gme_batch_steps
1711 WHERE batch_id = p_batch_id
1712 AND batchstep_id = NVL (p_batchstep_id, batchstep_id);
1713
1714 CURSOR cur_get_batchstep_activities (
1715 v_batch_id NUMBER
1716 ,v_batchstep_id NUMBER)
1717 IS
1718 SELECT activity, batchstep_activity_id
1719 FROM gme_batch_step_activities
1720 WHERE batch_id = v_batch_id AND batchstep_id = v_batchstep_id;
1721
1722 CURSOR cur_get_prim_rsrc_count (v_batchstep_activity_id NUMBER)
1723 IS
1724 SELECT COUNT (1)
1725 FROM gme_batch_step_resources
1726 WHERE batchstep_activity_id = v_batchstep_activity_id
1727 AND prim_rsrc_ind = 1;
1728
1729 l_count NUMBER (5);
1730 BEGIN
1731 /* Initialize return status to success */
1732 x_return_status := fnd_api.g_ret_sts_success;
1733
1734 FOR batchsteps IN cur_get_batch_steps LOOP
1735 FOR activities IN
1736 cur_get_batchstep_activities (p_batch_id
1737 ,batchsteps.batchstep_id) LOOP
1738 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1739 gme_debug.put_line ( ' Activity:'
1740 || activities.batchstep_activity_id);
1741 END IF;
1742
1743 OPEN cur_get_prim_rsrc_count (activities.batchstep_activity_id);
1744
1745 FETCH cur_get_prim_rsrc_count
1746 INTO l_count;
1747
1748 CLOSE cur_get_prim_rsrc_count;
1749
1750 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1751 gme_debug.put_line (' Count:' || l_count);
1752 END IF;
1753
1754 IF (l_count > 1) THEN
1755 fnd_message.set_name ('GME', 'GME_ONE_PRIM_RSRC');
1756 fnd_message.set_token ('ACTIVITY', activities.activity);
1757 fnd_message.set_token ('STEPNO', batchsteps.batchstep_no);
1758 fnd_msg_pub.ADD;
1759 RAISE fnd_api.g_exc_error;
1760 ELSIF (l_count = 0) THEN
1761 fnd_message.set_name ('GME', 'GME_MIN_ONE_PRIM_RSRC');
1762 fnd_message.set_token ('ACTIVITY', activities.activity);
1763 fnd_message.set_token ('STEPNO', batchsteps.batchstep_no);
1764 fnd_msg_pub.ADD;
1765 RAISE fnd_api.g_exc_error;
1766 END IF;
1767 END LOOP;
1768 END LOOP;
1769 EXCEPTION
1770 WHEN fnd_api.g_exc_error THEN
1771 x_return_status := fnd_api.g_ret_sts_error;
1772 WHEN OTHERS THEN
1773 x_return_status := fnd_api.g_ret_sts_unexp_error;
1774 fnd_msg_pub.add_exc_msg ('GME_RESOURCE_ENGINE_PVT'
1775 ,'CHECK_PRIMARY_RESOURCE');
1776 END check_primary_resource;
1777
1778 /*===========================================================================================
1779 Procedure
1780 get_resource_usage
1781 Description
1782 This particular procedure is used to get the resource usage based on the transactions.
1783 Parameters
1784 p_step_resources_rec The batch step resource rec to identify the resource
1785 x_step_resources_rec The batch step resource rec to identify the resource
1786 x_return_status outcome of the API call
1787 S - Success
1788 E - Error
1789 U - Unexpected error
1790 Pawan kumar added code for bug 2294055
1791 =============================================================================================*/
1792 PROCEDURE get_resource_usage (
1793 p_step_resources_rec IN gme_batch_step_resources%ROWTYPE
1794 ,x_step_resources_rec OUT NOCOPY gme_batch_step_resources%ROWTYPE
1795 ,x_return_status OUT NOCOPY VARCHAR2)
1796 IS
1797 l_api_name CONSTANT VARCHAR2 (30) := 'get_resource_usage';
1798
1799 CURSOR cur_get_stat
1800 IS
1801 SELECT step_status
1802 FROM gme_batch_steps
1803 WHERE batchstep_id = p_step_resources_rec.batchstep_id;
1804
1805 /* Bug 2651477 added delete_mark condition */
1806 CURSOR cur_get_usage (v_completed_ind NUMBER)
1807 IS
1808 SELECT SUM (resource_usage)
1809 FROM gme_resource_txns_gtmp
1810 WHERE line_id = p_step_resources_rec.batchstep_resource_id
1811 AND completed_ind = v_completed_ind
1812 AND (v_completed_ind = 1 OR sequence_dependent_ind = 0)
1813 AND action_code <> 'DEL'
1814 AND NVL (delete_mark, 0) <> 1;
1815
1816 l_status NUMBER (5);
1817 l_tot_usage NUMBER;
1818 BEGIN
1819 IF g_debug <= gme_debug.g_log_procedure THEN
1820 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
1821 || l_api_name);
1822 END IF;
1823
1824 /* Initialize return status to success */
1825 x_return_status := fnd_api.g_ret_sts_success;
1826 x_step_resources_rec.batchstep_resource_id :=
1827 p_step_resources_rec.batchstep_resource_id;
1828
1829 IF NOT gme_batch_step_resources_dbl.fetch_row
1830 (p_batch_step_resources => x_step_resources_rec
1831 ,x_batch_step_resources => x_step_resources_rec) THEN
1832 RAISE fnd_api.g_exc_error;
1833 END IF;
1834
1835 OPEN cur_get_stat;
1836
1837 FETCH cur_get_stat
1838 INTO l_status;
1839
1840 CLOSE cur_get_stat;
1841
1842 IF l_status = 1 THEN
1843 OPEN cur_get_usage (0);
1844
1845 FETCH cur_get_usage
1846 INTO l_tot_usage;
1847
1848 CLOSE cur_get_usage;
1849
1850 x_step_resources_rec.plan_rsrc_usage := l_tot_usage;
1851 ELSE
1852 OPEN cur_get_usage (1);
1853
1854 FETCH cur_get_usage
1855 INTO l_tot_usage;
1856
1857 CLOSE cur_get_usage;
1858
1859 x_step_resources_rec.actual_rsrc_usage := l_tot_usage;
1860 END IF;
1861
1862 IF g_debug <= gme_debug.g_log_procedure THEN
1863 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
1864 END IF;
1865 EXCEPTION
1866 WHEN fnd_api.g_exc_error THEN
1867 x_return_status := fnd_api.g_ret_sts_error;
1868 WHEN OTHERS THEN
1869 IF g_debug <= gme_debug.g_log_unexpected THEN
1870 gme_debug.put_line ( 'When others exception in '
1871 || g_pkg_name
1872 || '.'
1873 || l_api_name
1874 || ' Error is '
1875 || SQLERRM);
1876 END IF;
1877
1878 x_return_status := fnd_api.g_ret_sts_unexp_error;
1879 fnd_msg_pub.add_exc_msg ('GME_RESOURCE_ENGINE_PVT'
1880 ,'GET_RESOURCE_USAGE');
1881 END get_resource_usage;
1882
1883 /*===========================================================================================
1884 Procedure
1885 validate_rsrc_txn_param
1886 Description
1887 This particular procedure is used to validate all parameters passed to rsrc txn APIs
1888 Parameters
1889 p_batchstep_rsrc_id OR
1890 (p_plant_code,p_batch_no,step_no,activity and resource) to uniquely identify a resource
1891 p_trans_date transaction date of resource txn
1892 p_start_date start date of resource txn
1893 p_end_date end date of resource txn
1894 p_usage resource usage of the txns and resource
1895 p_reason_code reason to insert a completed rsrc txn
1896 p_instance_id instance_id of the instance of rsrc txn(for WPS)
1897 p_instance_no instance_no of the instance of rsrc txn(for WPS)
1898 x_return_status reflects return status of the API
1899 HISTORY
1900 09JULY03 BUG#3041697 V. Ajay Kumar
1901 Port bug 2965879 to 11.5.10K.
1902 Modified code such that an error is not raised if the
1903 reason code is not entered.
1904 02-SEP-2004 Rishi Varma B3856541
1905 Made changes for the rsrc txns in closed period ME.Added an extra parameter x_trans_date
1906 and replaced the gmi call with call to the new grp layer procedure.
1907 =============================================================================================*/
1908 PROCEDURE validate_rsrc_txn_param (
1909 p_called_from IN NUMBER
1910 ,p_batchstep_rsrc_id IN NUMBER
1911 ,p_org_code IN VARCHAR2
1912 ,p_batch_no IN VARCHAR2 := NULL
1913 ,p_batchstep_no IN NUMBER := NULL
1914 ,p_activity IN VARCHAR2 := NULL
1915 ,p_resource IN VARCHAR2 := NULL
1916 ,p_trans_date IN DATE
1917 ,p_start_date IN DATE
1918 ,p_end_date IN DATE
1919 ,p_usage IN NUMBER
1920 ,p_reason_name IN VARCHAR2
1921 ,p_reason_id IN NUMBER
1922 ,p_instance_id IN NUMBER
1923 ,p_instance_no IN NUMBER
1924 ,x_line_id OUT NOCOPY NUMBER
1925 ,x_step_status OUT NOCOPY NUMBER
1926 ,x_batch_header_rec OUT NOCOPY gme_batch_header%ROWTYPE
1927 ,x_instance_id OUT NOCOPY NUMBER
1928 ,x_reason_id OUT NOCOPY NUMBER
1929 ,x_return_status OUT NOCOPY VARCHAR2
1930 ,
1931 --Rishi Varma B3856541 02-09-2004 start
1932 x_trans_date OUT NOCOPY DATE)
1933 IS
1934 CURSOR cur_get_batch_id (v_org_id VARCHAR2, v_batch_no VARCHAR2)
1935 IS
1936 SELECT batch_id
1937 FROM gme_batch_header
1938 WHERE organization_id = v_org_id
1939 AND batch_no = v_batch_no
1940 AND batch_type = 0;
1941
1942 CURSOR cur_get_batchstep_id (v_batch_id NUMBER, v_batchstep_no NUMBER)
1943 IS
1944 SELECT batchstep_id
1945 FROM gme_batch_steps
1946 WHERE batch_id = v_batch_id AND batchstep_no = v_batchstep_no;
1947
1948 CURSOR cur_get_batch_details (v_resource_id NUMBER)
1949 IS
1950 SELECT a.batch_id, a.resources
1951 FROM gme_batch_step_resources a
1952 WHERE a.batchstep_resource_id = v_resource_id;
1953
1954 /* Bug 2685645 added batch_id param and used in where clause */
1955 CURSOR cur_get_activity_id (
1956 v_step_id NUMBER
1957 ,v_activity VARCHAR2
1958 ,v_batch_id NUMBER)
1959 IS
1960 SELECT batchstep_activity_id
1961 FROM gme_batch_step_activities
1962 WHERE batchstep_id = v_step_id
1963 AND batch_id = v_batch_id
1964 AND activity = v_activity;
1965
1966 CURSOR cur_fetch_resource_dtl (v_activity_id NUMBER, v_resource VARCHAR2)
1967 IS
1968 SELECT batchstep_resource_id, resources
1969 FROM gme_batch_step_resources
1970 WHERE batchstep_activity_id = v_activity_id
1971 AND resources = v_resource;
1972
1973 CURSOR cur_get_step_status (v_line_id NUMBER)
1974 IS
1975 SELECT step_status
1976 FROM gme_batch_steps a, gme_batch_step_resources b
1977 WHERE a.batchstep_id = b.batchstep_id
1978 AND b.batchstep_resource_id = v_line_id;
1979
1980 CURSOR cur_get_instance_id (v_instance_no NUMBER, v_resource VARCHAR2)
1981 IS
1982 SELECT instance_id
1983 FROM gmp_resource_instances i, cr_rsrc_dtl r
1984 WHERE r.resource_id = i.resource_id
1985 AND r.resources = v_resource
1986 AND instance_number = v_instance_no;
1987
1988 CURSOR cur_validate_instance_id (
1989 v_instance_id NUMBER
1990 ,v_resource VARCHAR2)
1991 IS
1992 SELECT 1
1993 FROM gmp_resource_instances i, cr_rsrc_dtl r
1994 WHERE r.resource_id = i.resource_id
1995 AND r.resources = v_resource
1996 AND instance_id = v_instance_id;
1997
1998 CURSOR cur_get_rsrc_actual_dates (v_line_id NUMBER)
1999 IS
2000 SELECT actual_start_date, actual_cmplt_date
2001 FROM gme_batch_step_resources
2002 WHERE batchstep_resource_id = v_line_id;
2003
2004 CURSOR cur_validate_reason_id (v_reason_id NUMBER)
2005 IS
2006 SELECT reason_id
2007 FROM mtl_transaction_reasons
2008 WHERE NVL (disable_date, SYSDATE + 1) > SYSDATE
2009 AND reason_id = v_reason_id;
2010
2011 CURSOR cur_get_reason_id (v_reason_name VARCHAR2)
2012 IS
2013 SELECT reason_id
2014 FROM mtl_transaction_reasons
2015 WHERE NVL (disable_date, SYSDATE + 1) > SYSDATE
2016 AND reason_name = v_reason_name;
2017
2018 l_api_name CONSTANT VARCHAR2 (30) := 'validate_rsrc_txn_param';
2019 l_batch_header gme_batch_header%ROWTYPE;
2020 l_tran_rec gmi_trans_engine_pub.ictran_rec;
2021 l_step_status NUMBER;
2022 l_batch_id NUMBER;
2023 l_batchstep_id NUMBER;
2024 l_activity_id NUMBER;
2025 l_dummy NUMBER;
2026 l_instance_no NUMBER;
2027 l_instance_id NUMBER := NULL;
2028 l_rsrc_actual_start_date DATE;
2029 l_rsrc_actual_cmplt_date DATE;
2030 l_return_status VARCHAR2 (2);
2031 l_line_id NUMBER;
2032 l_overrided_protected_ind VARCHAR2 (1);
2033 l_resource VARCHAR2 (16);
2034 l_batch_step_rsrc_rec_in gme_batch_step_resources%ROWTYPE;
2035 l_batch_step_rsrc_rec gme_batch_step_resources%ROWTYPE;
2036 --Rishi Varma 02-09-2004 B3856541
2037 /* start , Punit Kumar */
2038 /*
2039 p_tran_rec gmi_trans_engine_pub.ictran_rec;
2040 l_tran_rec_out gmi_trans_engine_pub.ictran_rec;
2041 */
2042
2043 /*start , Punit Kumar */
2044 l_org_id NUMBER;
2045 l_period_id INTEGER;
2046 /* end */
2047 rtxn_for_fpo_not_allowed EXCEPTION;
2048 batch_hdr_fetch_err EXCEPTION;
2049 neg_usage_not_allowed EXCEPTION;
2050 invalid_step_status EXCEPTION;
2051 invalid_reason_code EXCEPTION;
2052 invalid_date EXCEPTION;
2053 invalid_instance_id EXCEPTION;
2054 invalid_instance_no EXCEPTION;
2055 batch_not_found EXCEPTION;
2056 rsrcid_not_found EXCEPTION;
2057 batchstep_not_found EXCEPTION;
2058 stepactivity_not_found EXCEPTION;
2059 resource_not_found EXCEPTION;
2060 close_period_err EXCEPTION;
2061 step_status_asqc_error EXCEPTION;
2062 asqc_update_rsrc_api_error EXCEPTION;
2063 asqc_ovrd_end_txn_api_error EXCEPTION;
2064 rtxn_for_updinv_not_allowed EXCEPTION;
2065 BEGIN
2066 IF g_debug <= gme_debug.g_log_procedure THEN
2067 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
2068 || l_api_name);
2069 END IF;
2070
2071 /* Initially let us assign the return status to success */
2072 x_return_status := fnd_api.g_ret_sts_success;
2073
2074 l_batch_step_rsrc_rec_in.batchstep_resource_id := p_batchstep_rsrc_id;
2075 IF NOT gme_common_pvt.get_batchstep_rsrc (
2076 p_batchstep_rsrc_rec => l_batch_step_rsrc_rec_in
2077 ,p_org_code => p_org_code
2078 ,p_batch_no => p_batch_no
2079 ,p_batchstep_no => p_batchstep_no
2080 ,p_activity => p_activity
2081 ,p_resource => p_resource
2082 ,x_batchstep_rsrc_rec => l_batch_step_rsrc_rec) THEN
2083 raise rsrcid_not_found;
2084 END IF;
2085 /*
2086 IF p_batchstep_rsrc_id IS NOT NULL THEN
2087 l_line_id := p_batchstep_rsrc_id;
2088
2089 OPEN cur_get_batch_details (l_line_id);
2090
2091 FETCH cur_get_batch_details
2092 INTO l_batch_id, l_resource;
2093
2094 IF cur_get_batch_details%NOTFOUND THEN
2095 gme_common_pvt.log_message ('GME_RSRCID_NOT_FOUND'
2096 ,'BATCHSTEP_RSRC_ID'
2097 ,l_line_id);
2098 RAISE rsrcid_not_found;
2099 END IF;
2100
2101 CLOSE cur_get_batch_details;
2102
2103 IF p_called_from = 5 THEN
2104 -- This portion of the code would be executed only when we call this validation
2105 -- procedure from end_cmplt_actual_rsrc_txn
2106 -- B2498487 value of overrided_protected_ind is passed in p_resoure param
2107 l_overrided_protected_ind := p_resource;
2108 END IF;
2109 ELSE
2110 IF ( p_org_id IS NULL
2111 OR p_batch_no IS NULL
2112 OR p_batchstep_no IS NULL
2113 OR p_activity IS NULL
2114 OR p_resource IS NULL) THEN
2115 gme_common_pvt.log_message ('INPUT_PARMS_MISS'
2116 ,'PROC'
2117 , l_api_name || '.' || g_pkg_name);
2118 RAISE fnd_api.g_exc_error;
2119 ELSE
2120 -- Validate input param one by one to see if it identifies a resource correctly
2121 OPEN cur_get_batch_id (p_org_id, p_batch_no);
2122
2123 FETCH cur_get_batch_id
2124 INTO l_batch_id;
2125
2126 IF cur_get_batch_id%NOTFOUND THEN
2127 gme_common_pvt.log_message ('GME_BATCH_NOT_FOUND');
2128 RAISE batch_not_found;
2129 END IF;
2130
2131 CLOSE cur_get_batch_id;
2132
2133 -- use batch_id to fetch batchstep_id
2134 OPEN cur_get_batchstep_id (l_batch_id, p_batchstep_no);
2135
2136 FETCH cur_get_batchstep_id
2137 INTO l_batchstep_id;
2138
2139 IF cur_get_batchstep_id%NOTFOUND THEN
2140 gme_common_pvt.log_message ('GME_BATCH_STEP_NOT_FOUND'
2141 ,'STEP_ID'
2142 ,p_batchstep_no);
2143 RAISE batchstep_not_found;
2144 END IF;
2145
2146 CLOSE cur_get_batchstep_id;
2147
2148 -- fetch activity_id
2149 OPEN cur_get_activity_id (l_batchstep_id, p_activity, l_batch_id);
2150
2151 FETCH cur_get_activity_id
2152 INTO l_activity_id;
2153
2154 IF cur_get_activity_id%NOTFOUND THEN
2155 gme_common_pvt.log_message ('GME_STEP_ACTIVITY_NOT_FOUND'
2156 ,'ACTIVITY'
2157 ,p_activity
2158 ,'STEP_NO'
2159 ,p_batchstep_no);
2160 RAISE stepactivity_not_found;
2161 END IF;
2162
2163 CLOSE cur_get_activity_id;
2164
2165 -- fetch resource id
2166 OPEN cur_fetch_resource_dtl (l_activity_id, p_resource);
2167
2168 FETCH cur_fetch_resource_dtl
2169 INTO l_line_id, l_resource;
2170
2171 IF cur_fetch_resource_dtl%NOTFOUND THEN
2172 gme_common_pvt.log_message ('GME_RSRC_NOT_FOUND'
2173 ,'RESOURCE'
2174 ,p_resource
2175 ,'ACTIVITY'
2176 ,p_activity);
2177 RAISE resource_not_found;
2178 END IF;
2179
2180 CLOSE cur_fetch_resource_dtl;
2181 END IF; -- if plant_code etc input param is NOT NULL
2182 END IF; -- if p_batchstep_resource_id is NOT NULL
2183 */
2184 x_line_id := l_batch_step_rsrc_rec.batchstep_resource_id;
2185
2186 /* V. Ajay Kumar BUG#3041697 Removed the check for reason code */
2187 IF ( (p_trans_date IS NULL AND p_called_from <> 5)
2188 OR ( (p_start_date IS NULL) AND (p_batchstep_rsrc_id <> -1) )
2189 OR ( (p_end_date IS NULL) AND (p_usage <> 0) ) ) THEN
2190 gme_common_pvt.log_message ('INPUT_PARMS_MISS'
2191 ,'PROC'
2192 , l_api_name || '.' || g_pkg_name);
2193 RAISE fnd_api.g_exc_error;
2194 END IF;
2195
2196 l_batch_header.batch_id := l_batch_step_rsrc_rec.batch_id;
2197
2198 IF NOT gme_batch_header_dbl.fetch_row (p_batch_header => l_batch_header
2199 ,x_batch_header => l_batch_header) THEN
2200 RAISE batch_hdr_fetch_err;
2201 END IF;
2202
2203 /* We cannot insert allocations for an FPO */
2204 IF l_batch_header.batch_type = 10 THEN
2205 gme_common_pvt.log_message ('GME_RTXN_FOR_FPO_NT_ALWD');
2206 RAISE rtxn_for_fpo_not_allowed;
2207 END IF;
2208
2209 /* We cannot insert txns if the batch does not support txns*/
2210 IF l_batch_header.update_inventory_ind = 'N' THEN
2211 gme_common_pvt.log_message ('GME_RTXN_FOR_UPDINV_NT_ALWD');
2212 RAISE rtxn_for_updinv_not_allowed;
2213 END IF;
2214
2215 IF p_usage IS NOT NULL THEN
2216 IF p_usage < 0 THEN
2217 gme_common_pvt.log_message ('GME_NEG_USAGE_NT_ALWD');
2218 RAISE neg_usage_not_allowed;
2219 END IF;
2220 END IF;
2221
2222 OPEN cur_get_step_status (l_batch_step_rsrc_rec.batchstep_resource_id);
2223
2224 FETCH cur_get_step_status
2225 INTO l_step_status;
2226
2227 CLOSE cur_get_step_status;
2228
2229 IF l_step_status NOT IN (2, 3) THEN
2230 gme_common_pvt.log_message ('PC_STEP_STATUS_ERR');
2231 RAISE invalid_step_status;
2232 END IF;
2233
2234 x_step_status := l_step_status;
2235
2236 IF (l_batch_header.automatic_step_calculation = 1) THEN
2237 IF p_called_from = 1 THEN
2238 gme_common_pvt.log_message ('GME_UPD_RSRC_NT_WRK_ASQCBTCH');
2239 RAISE asqc_update_rsrc_api_error;
2240 END IF;
2241
2242 IF (l_step_status = 2) THEN
2243 gme_common_pvt.log_message ('GME_INV_STEP_STATUS_ASQC');
2244 RAISE step_status_asqc_error;
2245 END IF;
2246
2247 -- Shikha Nagar B2498487 Added code to prevent ending txn
2248 -- for automatically generated ASQC txn
2249 IF ( p_called_from = 5
2250 AND l_step_status = 3
2251 AND l_overrided_protected_ind = 'N') THEN
2252 gme_common_pvt.log_message ('GME_NT_END_ASQC_OVRD_TXN');
2253 RAISE asqc_ovrd_end_txn_api_error;
2254 END IF;
2255 END IF; /* If ASQC is on */
2256 /* Lets validate the reason code passed in */
2257
2258 IF p_reason_id IS NOT NULL THEN
2259 OPEN cur_validate_reason_id(p_reason_id);
2260 FETCH cur_validate_reason_id
2261 INTO x_reason_id;
2262 IF cur_validate_reason_id%NOTFOUND THEN
2263 CLOSE cur_validate_reason_id;
2264 gme_common_pvt.log_message(p_product_code => 'INV'
2265 ,p_message_code => 'INV_LOTC_REASONID_INVALID');
2266 RAISE FND_API.g_exc_error;
2267 END IF;
2268 CLOSE cur_validate_reason_id;
2269 ELSIF p_reason_name IS NOT NULL THEN
2270 OPEN cur_get_reason_id(p_reason_name);
2271 FETCH cur_get_reason_id
2272 INTO x_reason_id;
2273 IF cur_get_reason_id%NOTFOUND THEN
2274 CLOSE cur_get_reason_id;
2275 gme_common_pvt.log_message('GME_INVALID_REASON_NAME');
2276 RAISE FND_API.g_exc_error;
2277 ELSE
2278 FETCH cur_get_reason_id
2279 INTO x_reason_id;
2280 IF cur_get_reason_id%NOTFOUND THEN
2281 CLOSE cur_get_reason_id;
2282 ELSE
2283 CLOSE cur_get_reason_id;
2284 gme_common_pvt.log_message('GME_REASON_NAME_NOT_UNIQUE');
2285 RAISE FND_API.g_exc_error;
2286 END IF;
2287 END IF;
2288 END IF; /* IF p_reason_name IS NOT NULL */
2289
2290 /* Lets validate the instance id/no passed in */
2291 IF p_instance_id IS NOT NULL THEN
2292 OPEN cur_validate_instance_id (p_instance_id, l_batch_step_rsrc_rec.resources);
2293
2294 FETCH cur_validate_instance_id
2295 INTO l_dummy;
2296
2297 IF cur_validate_instance_id%NOTFOUND THEN
2298 gme_common_pvt.log_message ('GME_INVALID_INSTANCE_ID'
2299 ,'INSTANCE_ID'
2300 ,p_instance_id);
2301
2302 CLOSE cur_validate_instance_id;
2303
2304 RAISE invalid_instance_id;
2305 END IF;
2306
2307 CLOSE cur_validate_instance_id;
2308
2309 x_instance_id := p_instance_id;
2310 ELSIF p_instance_no IS NOT NULL THEN
2311 OPEN cur_get_instance_id (p_instance_no, l_batch_step_rsrc_rec.resources);
2312
2313 FETCH cur_get_instance_id
2314 INTO l_instance_id;
2315
2316 IF cur_get_instance_id%NOTFOUND THEN
2317 gme_common_pvt.log_message ('GME_INVALID_INSTANCE_NO'
2318 ,'INSTANCE_NO'
2319 ,p_instance_no);
2320
2321 CLOSE cur_get_instance_id;
2322
2323 RAISE invalid_instance_no;
2324 END IF;
2325
2326 CLOSE cur_get_instance_id;
2327 END IF;
2328
2329 x_instance_id := l_instance_id;
2330
2331 OPEN cur_get_rsrc_actual_dates (l_batch_step_rsrc_rec.batchstep_resource_id);
2332
2333 FETCH cur_get_rsrc_actual_dates
2334 INTO l_rsrc_actual_start_date, l_rsrc_actual_cmplt_date;
2335
2336 CLOSE cur_get_rsrc_actual_dates;
2337
2338 -- Validate trans_date
2339 IF p_trans_date < l_rsrc_actual_start_date THEN
2340 gme_common_pvt.log_message ('GME_BAD_TRANS_DATE');
2341 RAISE invalid_date;
2342 ELSIF p_trans_date > gme_common_pvt.g_timestamp THEN
2343 gme_common_pvt.log_message ('GME_BAD_TRANS_SYS_DATE');
2344 RAISE invalid_date;
2345 ELSIF l_rsrc_actual_cmplt_date IS NOT NULL THEN
2346 IF p_trans_date > l_rsrc_actual_cmplt_date THEN
2347 gme_common_pvt.log_message ('GME_BAD_TRANS_CMPLT_DATE');
2348 RAISE invalid_date;
2349 END IF;
2350 ELSIF p_start_date IS NOT NULL THEN
2351 IF p_trans_date < p_start_date THEN
2352 gme_common_pvt.log_message ('GME_BAD_TRANS_STRT_DATE');
2353 RAISE invalid_date;
2354 END IF;
2355 ELSIF p_end_date IS NOT NULL THEN
2356 IF p_trans_date > p_end_date THEN
2357 gme_common_pvt.log_message ('GME_BAD_TRANS_END_DATE');
2358 RAISE invalid_date;
2359 END IF;
2360 END IF;
2361
2362 --Rishi Varma 02-09-2004 B3856541 start
2363 /*Commenting the gmi call and adding call to the check_period_check_flexible
2364 procedure for checking resource txns. in closed periods*/
2365 /*l_tran_rec.trans_date := p_trans_date;
2366 l_tran_rec.orgn_code := l_batch_header.plant_code;
2367 l_tran_rec.whse_code := l_batch_header.wip_whse_code;
2368 IF NOT GMI_TRANS_ENGINE_PVT.close_period_check
2369 ( p_tran_rec => l_tran_rec,
2370 p_retry_flag => 1,
2371 x_tran_rec => l_tran_rec)
2372 THEN
2373 --Bug3315440
2374 -- gme_common_pvt.log_message('GME_DATE_IN_CLSD_PRD','TRANS_DATE',p_trans_date);
2375 gme_common_pvt.log_message('GME_DATE_IN_CLSD_PRD','TRANS_DATE',fnd_date.date_to_displayDT(p_trans_date));
2376 RAISE close_period_err;
2377 END IF;
2378 */
2379
2380 ---- p_tran_rec.orgn_code := l_batch_header.plant_code;
2381 ----p_tran_rec.orgn_code := l_batch_header.ORGANIZATION_ID;
2382 ----p_tran_rec.whse_code := l_batch_header.wip_whse_code;
2383
2384 /* start , Punit Kumar */
2385 /*
2386 p_tran_rec.trans_date := p_trans_date;
2387 IF NOT gme_api_grp.close_period_check_flexible
2388 (p_tran_rec => p_tran_rec,
2389 x_tran_rec => l_tran_rec_out)
2390 THEN
2391 RAISE FND_API.g_exc_error;
2392 END IF;
2393 x_trans_date := l_tran_rec_out.trans_date;
2394 --Rishi Varma 02-09-2004 B3856541 end
2395 */
2396 IF NOT gme_common_pvt.close_period_check_flexible
2397 (p_org_id => l_batch_step_rsrc_rec.organization_id
2398 ,p_trans_date => p_trans_date
2399 ,x_trans_date => x_trans_date
2400 ,x_period_id => l_period_id) THEN
2401 RAISE fnd_api.g_exc_error;
2402 END IF;
2403
2404 /* end */
2405
2406 -- Validate start_date
2407 IF p_start_date < l_rsrc_actual_start_date THEN
2408 gme_common_pvt.log_message ('GME_BAD_START_DATE');
2409 RAISE invalid_date;
2410 ELSIF p_start_date > gme_common_pvt.g_timestamp THEN
2411 gme_common_pvt.log_message ('GME_BAD_START_END_DATE');
2412 RAISE invalid_date;
2413 ELSIF l_rsrc_actual_cmplt_date IS NOT NULL THEN
2414 IF p_start_date > l_rsrc_actual_cmplt_date THEN
2415 gme_common_pvt.log_message ('GME_BAD_START_CMPLT_DATE');
2416 RAISE invalid_date;
2417 END IF;
2418 END IF;
2419
2420 -- Validate END DATE
2421 IF p_end_date < p_start_date THEN
2422 gme_common_pvt.log_message ('PM_BADENDDATE');
2423 RAISE invalid_date;
2424 ELSIF p_end_date > gme_common_pvt.g_timestamp THEN
2425 gme_common_pvt.log_message ('GME_BAD_END_DATE');
2426 RAISE invalid_date;
2427 ELSIF l_rsrc_actual_cmplt_date IS NOT NULL THEN
2428 IF p_end_date > l_rsrc_actual_cmplt_date THEN
2429 gme_common_pvt.log_message ('GME_BAD_END_CMPLT_DATE');
2430 RAISE invalid_date;
2431 END IF;
2432 END IF;
2433
2434 x_batch_header_rec := l_batch_header;
2435
2436 IF g_debug <= gme_debug.g_log_procedure THEN
2437 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
2438 END IF;
2439 EXCEPTION
2440 WHEN invalid_date OR close_period_err OR step_status_asqc_error OR asqc_update_rsrc_api_error OR asqc_ovrd_end_txn_api_error OR fnd_api.g_exc_error THEN
2441 x_return_status := fnd_api.g_ret_sts_error;
2442 WHEN invalid_instance_id OR invalid_instance_no THEN
2443 x_return_status := fnd_api.g_ret_sts_error;
2444 WHEN neg_usage_not_allowed OR batch_hdr_fetch_err OR rsrcid_not_found THEN
2445 x_return_status := fnd_api.g_ret_sts_error;
2446 WHEN batch_not_found OR batchstep_not_found OR invalid_step_status THEN
2447 x_return_status := fnd_api.g_ret_sts_error;
2448 WHEN rtxn_for_fpo_not_allowed THEN
2449 x_return_status := fnd_api.g_ret_sts_error;
2450 WHEN stepactivity_not_found OR resource_not_found OR rtxn_for_updinv_not_allowed THEN
2451 x_return_status := fnd_api.g_ret_sts_error;
2452 WHEN invalid_reason_code THEN
2453 fnd_message.set_name ('GMA', 'SY_REASONCODE');
2454 fnd_msg_pub.ADD;
2455 x_return_status := fnd_api.g_ret_sts_error;
2456 WHEN OTHERS THEN
2457 IF g_debug <= gme_debug.g_log_unexpected THEN
2458 gme_debug.put_line ( 'When others exception in '
2459 || g_pkg_name
2460 || '.'
2461 || l_api_name
2462 || ' Error is '
2463 || SQLERRM);
2464 END IF;
2465
2466 x_return_status := fnd_api.g_ret_sts_unexp_error;
2467 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2468 END validate_rsrc_txn_param;
2469
2470 /*===========================================================================================
2471 Procedure
2472 update_actual_resource_usage
2473 Description
2474 This particular procedure is used to insert completed rsrc txn rec for a resource and deletes
2475 all other existing rsrc txns.
2476 Parameters
2477 (p_org_code,p_batch_no,step_no,activity and resource) to uniquely identify a resource
2478 p_trans_date transaction date of resource txn
2479 p_start_date start date of resource txn
2480 p_end_date end date of resource txn
2481 p_usage resource usage of the txns and resource
2482 p_reason_code reason to insert a completed rsrc txn
2483 p_instance_id instance_id of the instance of rsrc txn(for WPS)
2484 p_instance_no instance_no of the instance of rsrc txn(for WPS)
2485 x_return_status reflects return status of the API
2486 02-SEP-04 Rishi Varma B3856541
2487 Added the new parameter to the validate_rsrc_param procedure call.
2488 01-OCT-04 Rishi Varma 3896510/3865212
2489 Made changes for bug 3896510
2490
2491 16-March-2005 Punit Kumar
2492 Convergence changes
2493 =============================================================================================*/
2494 PROCEDURE update_actual_resource_usage (
2495 p_org_code IN VARCHAR2
2496 ,p_batch_no IN VARCHAR2 := NULL
2497 ,p_batchstep_no IN NUMBER := NULL
2498 ,p_activity IN VARCHAR2 := NULL
2499 ,p_resource IN VARCHAR2 := NULL
2500 ,p_reason_name IN VARCHAR2,
2501 p_instance_no IN NUMBER
2502 ,p_rsrc_txn_rec IN gme_resource_txns%ROWTYPE
2503 ,x_rsrc_txn_rec IN OUT NOCOPY gme_resource_txns%ROWTYPE
2504 ,x_return_status OUT NOCOPY VARCHAR2)
2505 IS
2506 l_api_name CONSTANT VARCHAR2 (30) := 'UPDATE_ACTUAL_RSRC_USAGE';
2507 l_resource_txns gme_resource_txns_gtmp%ROWTYPE;
2508 l_batch_header gme_batch_header%ROWTYPE;
2509 l_step_resources gme_batch_step_resources%ROWTYPE;
2510 l_resource_tbl gme_common_pvt.resource_transactions_tab;
2511 l_line_id NUMBER;
2512 l_instance_id NUMBER;
2513 l_reason_id NUMBER;
2514 l_inv_trans_count NUMBER;
2515 l_rsrc_trans_count NUMBER;
2516 l_step_status NUMBER;
2517 l_return_status VARCHAR2 (2);
2518 l_resources VARCHAR2 (16);
2519 l_usage_uom VARCHAR2 (4);
2520 --Rishi Varma 02-09-2004 B3856541
2521 l_trans_date DATE;
2522 l_rsrc_txn_rec gme_resource_txns_gtmp%ROWTYPE;
2523 l_tran_rec gme_resource_txns_gtmp%ROWTYPE;
2524
2525 CURSOR cur_fetch_resource_dtl (v_line_id NUMBER)
2526 IS
2527 SELECT resources, usage_um
2528 FROM gme_batch_step_resources
2529 WHERE batchstep_resource_id = v_line_id;
2530
2531 validation_failure EXCEPTION;
2532 rsrc_fetch_err EXCEPTION;
2533 rsrc_update_err EXCEPTION;
2534 rsrc_txn_insert_err EXCEPTION;
2535 error_load_trans EXCEPTION;
2536 update_rsrc_txn_err EXCEPTION;
2537 BEGIN
2538 IF g_debug <= gme_debug.g_log_procedure THEN
2539 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
2540 || l_api_name);
2541 END IF;
2542
2543 /* Initially let us assign the return status to success */
2544 x_return_status := fnd_api.g_ret_sts_success;
2545
2546 /*siva commented following IF condition to allow insertion of flexfileds
2547 without validation when p_validate_flexfields is FALSE */
2548 --IF gme_common_pvt.g_flex_validate_prof = 1 THEN
2549 /*Validate Flexfields using the new procedure gme_api_validate_flex_fld_pvt.validate_rsrc_txn_flex */
2550 gme_validate_flex_fld_pvt.validate_rsrc_txn_flex
2551 (p_resource_txn_rec => p_rsrc_txn_rec
2552 ,x_resource_txn_rec => x_rsrc_txn_rec
2553 ,x_return_status => l_return_status);
2554
2555 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2556 RAISE validation_failure;
2557 END IF;
2558 --END IF;
2559
2560 IF g_debug <= gme_debug.g_log_procedure THEN
2561 gme_debug.put_line ( 'before calling validate_rsrc_txn_param '
2562 || g_pkg_name
2563 || '.'
2564 || l_api_name);
2565 END IF;
2566
2567 validate_rsrc_txn_param (p_called_from => 1
2568 ,p_batchstep_rsrc_id => p_rsrc_txn_rec.line_id
2569 ,p_org_code => p_org_code
2570 ,p_batch_no => p_batch_no
2571 ,p_batchstep_no => p_batchstep_no
2572 ,p_activity => p_activity
2573 ,p_resource => p_resource
2574 ,p_trans_date => p_rsrc_txn_rec.trans_date
2575 ,p_start_date => p_rsrc_txn_rec.start_date
2576 ,p_end_date => p_rsrc_txn_rec.end_date
2577 ,p_usage => p_rsrc_txn_rec.resource_usage
2578 ,p_reason_name => p_reason_name
2579 ,p_reason_id => p_rsrc_txn_rec.reason_id
2580 ,p_instance_id => p_rsrc_txn_rec.instance_id
2581 ,p_instance_no => p_instance_no
2582 ,x_line_id => l_line_id
2583 ,x_step_status => l_step_status
2584 ,x_batch_header_rec => l_batch_header
2585 ,x_instance_id => l_instance_id
2586 ,x_reason_id => l_reason_id
2587 ,x_return_status => x_return_status
2588 ,x_trans_date => l_trans_date);
2589
2590 IF g_debug <= gme_debug.g_log_procedure THEN
2591 gme_debug.put_line ( 'after calling validate_rsrc_txn_param '
2592 || g_pkg_name
2593 || '.'
2594 || l_api_name);
2595 END IF;
2596
2597 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2598 RAISE validation_failure;
2599 END IF;
2600
2601 OPEN cur_fetch_resource_dtl (l_line_id);
2602
2603 FETCH cur_fetch_resource_dtl
2604 INTO l_resources, l_usage_uom;
2605
2606 CLOSE cur_fetch_resource_dtl;
2607
2608 /* Lets now load the transactions associated with the batch into the temporary tblle */
2609 gme_trans_engine_util.load_rsrc_trans
2610 (p_batch_row => l_batch_header
2611 ,x_rsc_row_count => l_rsrc_trans_count
2612 ,x_return_status => l_return_status);
2613
2614 IF l_return_status <> x_return_status THEN
2615 RAISE error_load_trans;
2616 END IF;
2617 IF g_debug <= gme_debug.g_log_statement THEN
2618 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' Transactions Loaded '||l_rsrc_trans_count);
2619 END IF;
2620
2621 l_rsrc_txn_rec.line_id := l_line_id;
2622 gme_resource_engine_pvt.fetch_active_resources
2623 (p_resource_rec => l_rsrc_txn_rec
2624 ,x_resource_tbl => l_resource_tbl
2625 ,x_return_status => l_return_status);
2626
2627 IF l_return_status <> x_return_status THEN
2628 RAISE fnd_api.g_exc_error;
2629 END IF;
2630
2631 -- delete all rec in l_resource_tbl
2632 FOR i IN 1 .. l_resource_tbl.COUNT LOOP
2633 delete_resource_trans (p_tran_rec => l_resource_tbl (i)
2634 ,x_return_status => l_return_status);
2635
2636 IF l_return_status <> 'S' THEN
2637 RAISE update_rsrc_txn_err;
2638 END IF;
2639 END LOOP;
2640
2641 -- construct record for insertion
2642 l_rsrc_txn_rec.doc_id := l_batch_header.batch_id;
2643 l_rsrc_txn_rec.doc_type := 'PROD';
2644 l_rsrc_txn_rec.line_type := 0;
2645 l_rsrc_txn_rec.organization_id := l_batch_header.organization_id;
2646 l_rsrc_txn_rec.orgn_code := NULL;
2647 l_rsrc_txn_rec.event_id := gme_common_pvt.g_transaction_header_id;
2648 l_rsrc_txn_rec.line_id := l_line_id;
2649 l_rsrc_txn_rec.resources := l_resources;
2650 l_rsrc_txn_rec.resource_usage := p_rsrc_txn_rec.resource_usage;
2651 l_rsrc_txn_rec.trans_um := l_usage_uom;
2652 l_rsrc_txn_rec.trans_date := p_rsrc_txn_rec.trans_date;
2653 l_rsrc_txn_rec.completed_ind := 1;
2654 l_rsrc_txn_rec.posted_ind := 0;
2655 --Bug#6154309 only for ASQC batches overrided_protected_ind should be yes.
2656 IF l_batch_header.automatic_step_calculation = 1 THEN
2657 l_rsrc_txn_rec.overrided_protected_ind := 'Y';
2658 ELSE
2659 l_rsrc_txn_rec.overrided_protected_ind := 'N';
2660 END IF;
2661 l_rsrc_txn_rec.reason_id := l_reason_id;
2662 l_rsrc_txn_rec.start_date := p_rsrc_txn_rec.start_date;
2663 l_rsrc_txn_rec.end_date := p_rsrc_txn_rec.end_date;
2664 l_rsrc_txn_rec.action_code := 'ADD';
2665 l_rsrc_txn_rec.delete_mark := 0;
2666 l_rsrc_txn_rec.text_code := 0;
2667
2668 IF p_rsrc_txn_rec.instance_id IS NOT NULL THEN
2669 l_rsrc_txn_rec.instance_id := p_rsrc_txn_rec.instance_id;
2670 ELSE
2671 l_rsrc_txn_rec.instance_id := l_instance_id;
2672 END IF;
2673
2674 l_rsrc_txn_rec.sequence_dependent_ind := 0;
2675 --siva copying flex-fields
2676 --IF gme_common_pvt.g_flex_validate_prof = 1 THEN
2677 l_rsrc_txn_rec.attribute_category := x_rsrc_txn_rec.attribute_category;
2678 l_rsrc_txn_rec.attribute1 := x_rsrc_txn_rec.attribute1;
2679 l_rsrc_txn_rec.attribute2 := x_rsrc_txn_rec.attribute2;
2680 l_rsrc_txn_rec.attribute3 := x_rsrc_txn_rec.attribute3;
2681 l_rsrc_txn_rec.attribute4 := x_rsrc_txn_rec.attribute4;
2682 l_rsrc_txn_rec.attribute5 := x_rsrc_txn_rec.attribute5;
2683 l_rsrc_txn_rec.attribute6 := x_rsrc_txn_rec.attribute6;
2684 l_rsrc_txn_rec.attribute7 := x_rsrc_txn_rec.attribute7;
2685 l_rsrc_txn_rec.attribute8 := x_rsrc_txn_rec.attribute8;
2686 l_rsrc_txn_rec.attribute9 := x_rsrc_txn_rec.attribute9;
2687 l_rsrc_txn_rec.attribute10 := x_rsrc_txn_rec.attribute10;
2688 l_rsrc_txn_rec.attribute11 := x_rsrc_txn_rec.attribute11;
2689 l_rsrc_txn_rec.attribute12 := x_rsrc_txn_rec.attribute12;
2690 l_rsrc_txn_rec.attribute13 := x_rsrc_txn_rec.attribute13;
2691 l_rsrc_txn_rec.attribute14 := x_rsrc_txn_rec.attribute14;
2692 l_rsrc_txn_rec.attribute15 := x_rsrc_txn_rec.attribute15;
2693 l_rsrc_txn_rec.attribute16 := x_rsrc_txn_rec.attribute16;
2694 l_rsrc_txn_rec.attribute17 := x_rsrc_txn_rec.attribute17;
2695 l_rsrc_txn_rec.attribute18 := x_rsrc_txn_rec.attribute18;
2696 l_rsrc_txn_rec.attribute19 := x_rsrc_txn_rec.attribute19;
2697 l_rsrc_txn_rec.attribute20 := x_rsrc_txn_rec.attribute20;
2698 l_rsrc_txn_rec.attribute21 := x_rsrc_txn_rec.attribute21;
2699 l_rsrc_txn_rec.attribute22 := x_rsrc_txn_rec.attribute22;
2700 l_rsrc_txn_rec.attribute23 := x_rsrc_txn_rec.attribute23;
2701 l_rsrc_txn_rec.attribute24 := x_rsrc_txn_rec.attribute24;
2702 l_rsrc_txn_rec.attribute25 := x_rsrc_txn_rec.attribute25;
2703 l_rsrc_txn_rec.attribute26 := x_rsrc_txn_rec.attribute26;
2704 l_rsrc_txn_rec.attribute27 := x_rsrc_txn_rec.attribute27;
2705 l_rsrc_txn_rec.attribute28 := x_rsrc_txn_rec.attribute28;
2706 l_rsrc_txn_rec.attribute29 := x_rsrc_txn_rec.attribute29;
2707 l_rsrc_txn_rec.attribute30 := x_rsrc_txn_rec.attribute30;
2708 --END IF;
2709 IF NOT (gme_resource_txns_gtmp_dbl.insert_row (l_rsrc_txn_rec
2710 ,l_rsrc_txn_rec) ) THEN
2711 RAISE rsrc_txn_insert_err;
2712 END IF;
2713
2714 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2715 --Rishi Varma 3896510/3865212
2716 /*Replaced hardcoded value '157' for batch_id with l_batch_header.batch_id*/
2717 gme_debug.display_resource_gtmp (NULL, NULL
2718 ,l_batch_header.batch_id);
2719 END IF;
2720
2721 l_step_resources.batchstep_resource_id := l_rsrc_txn_rec.line_id;
2722 IF NOT gme_batch_step_resources_dbl.fetch_row
2723 (p_batch_step_resources => l_step_resources
2724 ,x_batch_step_resources => l_step_resources) THEN
2725 RAISE rsrc_fetch_err;
2726 END IF;
2727
2728 l_step_resources.actual_rsrc_count := 1;
2729 l_step_resources.actual_rsrc_usage := l_rsrc_txn_rec.resource_usage;
2730
2731 IF NOT gme_batch_step_resources_dbl.update_row
2732 (p_batch_step_resources => l_step_resources) THEN
2733 RAISE rsrc_update_err;
2734 END IF;
2735
2736 --22APR02 Pawan Kumar bug 2912743 added line_id. Assigning the value of poc_trans_id
2737 x_rsrc_txn_rec.poc_trans_id := l_rsrc_txn_rec.poc_trans_id;
2738 x_rsrc_txn_rec.doc_id := l_rsrc_txn_rec.doc_id;
2739 x_rsrc_txn_rec.line_id := l_rsrc_txn_rec.line_id;
2740
2741 IF g_debug <= gme_debug.g_log_procedure THEN
2742 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
2743 END IF;
2744 EXCEPTION
2745 WHEN validation_failure OR error_load_trans THEN
2746 x_return_status := fnd_api.g_ret_sts_error;
2747 WHEN update_rsrc_txn_err OR rsrc_txn_insert_err THEN
2748 x_return_status := fnd_api.g_ret_sts_error;
2749 WHEN rsrc_fetch_err OR rsrc_update_err THEN
2750 x_return_status := fnd_api.g_ret_sts_error;
2751 WHEN OTHERS THEN
2752 IF g_debug <= gme_debug.g_log_unexpected THEN
2753 gme_debug.put_line ( 'When others exception in '
2754 || g_pkg_name
2755 || '.'
2756 || l_api_name
2757 || ' Error is '
2758 || SQLERRM);
2759 END IF;
2760
2761 x_return_status := fnd_api.g_ret_sts_unexp_error;
2762 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2763 END update_actual_resource_usage;
2764
2765 /*===========================================================================================
2766 Procedure
2767 insert_incr_actual_rsrc_txn
2768 Description
2769 This particular procedure is used to insert incrementally a completed rsrc txn rec
2770 Parameters
2771 (p_org_code,p_batch_no,step_no,activity and resource) to uniquely identify a resource
2772 p_trans_date transaction date of resource txn
2773 p_start_date start date of resource txn
2774 p_end_date end date of resource txn
2775 p_usage resource usage of the txns and resource
2776 p_reason_code reason to insert a completed rsrc txn
2777 p_instance_id instance_id of the instance of rsrc txn(for WPS)
2778 p_instance_no instance_no of the instance of rsrc txn(for WPS)
2779 x_return_status reflects return status of the API
2780 02-SEP-04 Rishi Varma B3856541
2781 Added the new parameter to the validate_rsrc_param procedure call.
2782
2783 16-March-2005 Punit Kumar
2784 Convergenc changes
2785 =============================================================================================*/
2786 PROCEDURE insert_incr_actual_rsrc_txn (
2787 p_org_code IN VARCHAR2
2788 /*inventory organization under which the batch was created.*/
2789 ,p_batch_no IN VARCHAR2 := NULL
2790 ,p_batchstep_no IN NUMBER := NULL
2791 ,p_activity IN VARCHAR2 := NULL
2792 ,p_resource IN VARCHAR2 := NULL
2793 ,p_reason_name IN VARCHAR2
2794 ,p_instance_no IN NUMBER
2795 ,p_rsrc_txn_rec IN gme_resource_txns%ROWTYPE
2796 ,x_rsrc_txn_rec IN OUT NOCOPY gme_resource_txns%ROWTYPE
2797 ,x_return_status OUT NOCOPY VARCHAR2)
2798 IS
2799 l_api_name CONSTANT VARCHAR2 (30)
2800 := 'insert_incr_actual_rsrc_txn';
2801 l_resource_txns gme_resource_txns%ROWTYPE;
2802 l_batch_header gme_batch_header%ROWTYPE;
2803 l_step_resources gme_batch_step_resources%ROWTYPE;
2804 l_resource_tbl gme_common_pvt.resource_transactions_tab;
2805 l_step_status NUMBER;
2806 l_activity_id NUMBER;
2807 l_inv_trans_count NUMBER;
2808 l_rsrc_trans_count NUMBER;
2809 l_dummy NUMBER;
2810 l_instance_no NUMBER;
2811 l_instance_id NUMBER;
2812 l_reason_id NUMBER;
2813 l_line_id NUMBER;
2814 l_rsrc_actual_start_date DATE;
2815 l_rsrc_actual_cmplt_date DATE;
2816 l_return_status VARCHAR2 (2);
2817 l_resources VARCHAR2 (16);
2818 l_usage_uom VARCHAR2 (4);
2819 --Rishi Varma B3856541 02-09-2004
2820 l_trans_date DATE;
2821 l_rsrc_txn_rec gme_resource_txns%ROWTYPE;
2822
2823 CURSOR cur_fetch_resource_dtl (v_line_id NUMBER)
2824 IS
2825 ----SELECT resources,usage_uom
2826 SELECT resources, usage_um
2827 FROM gme_batch_step_resources
2828 WHERE batchstep_resource_id = v_line_id;
2829
2830 validation_failure EXCEPTION;
2831 rsrc_fetch_err EXCEPTION;
2832 rsrc_update_err EXCEPTION;
2833 rsrc_txn_insert_err EXCEPTION;
2834 reduce_pend_usage_err EXCEPTION;
2835 error_load_trans EXCEPTION;
2836 BEGIN
2837 IF g_debug <= gme_debug.g_log_procedure THEN
2838 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
2839 || l_api_name);
2840 END IF;
2841
2842 /* Initially let us assign the return status to success */
2843 x_return_status := fnd_api.g_ret_sts_success;
2844
2845 /*siva commented following IF condition to allow insertion of flexfileds
2846 without validation when p_validate_flexfields is FALSE */
2847 --IF gme_common_pvt.g_flex_validate_prof = 1 THEN
2848 /*Validate Flexfields using the new procedure gme_api_validate_flex_fld_pvt.validate_rsrc_txn_flex */
2849 gme_validate_flex_fld_pvt.validate_rsrc_txn_flex
2850 (p_resource_txn_rec => p_rsrc_txn_rec
2851 ,x_resource_txn_rec => x_rsrc_txn_rec
2852 ,x_return_status => x_return_status);
2853
2854 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2855 RAISE validation_failure;
2856 END IF;
2857 --END IF;
2858
2859 l_rsrc_txn_rec := x_rsrc_txn_rec;
2860
2861 IF g_debug <= gme_debug.g_log_procedure THEN
2862 gme_debug.put_line ( 'before calling validate_rsrc_txn_param '
2863 || g_pkg_name
2864 || '.'
2865 || l_api_name);
2866 END IF;
2867
2868 validate_rsrc_txn_param (p_called_from => 2
2869 ,p_batchstep_rsrc_id => p_rsrc_txn_rec.line_id
2870 ,p_org_code => p_org_code
2871 ,p_batch_no => p_batch_no
2872 ,p_batchstep_no => p_batchstep_no
2873 ,p_activity => p_activity
2874 ,p_resource => p_resource
2875 ,p_trans_date => p_rsrc_txn_rec.trans_date
2876 ,p_start_date => p_rsrc_txn_rec.start_date
2877 ,p_end_date => p_rsrc_txn_rec.end_date
2878 ,p_usage => p_rsrc_txn_rec.resource_usage
2879 ,p_reason_id => p_rsrc_txn_rec.reason_id
2880 ,p_reason_name => p_reason_name
2881 ,p_instance_id => p_rsrc_txn_rec.instance_id
2882 ,p_instance_no => p_instance_no
2883 ,x_line_id => l_line_id
2884 ,x_step_status => l_step_status
2885 ,x_batch_header_rec => l_batch_header
2886 ,x_instance_id => l_instance_id
2887 ,x_reason_id => l_reason_id
2888 ,x_return_status => x_return_status
2889 ,x_trans_date => l_trans_date);
2890
2891 IF g_debug <= gme_debug.g_log_procedure THEN
2892 gme_debug.put_line ( 'after calling validate_rsrc_txn_param '
2893 || g_pkg_name
2894 || '.'
2895 || l_api_name);
2896 END IF;
2897
2898 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2899 RAISE validation_failure;
2900 END IF;
2901
2902 OPEN cur_fetch_resource_dtl (l_line_id);
2903
2904 FETCH cur_fetch_resource_dtl
2905 INTO l_resources, l_usage_uom;
2906
2907 CLOSE cur_fetch_resource_dtl;
2908
2909 -- construct record for insertion
2910 l_rsrc_txn_rec.doc_id := l_batch_header.batch_id;
2911 /* hard coding doc_type to PROD as rsrc txn can only exist for batchesi not FPO
2912 later if we decide to have this functionality for FPOs we would have to change code to
2913 get batch_type from l_batch_header variable */
2914 l_rsrc_txn_rec.doc_type := 'PROD';
2915 l_rsrc_txn_rec.line_type := 0;
2916 l_rsrc_txn_rec.organization_id := l_batch_header.organization_id;
2917 l_rsrc_txn_rec.line_id := l_line_id;
2918 l_rsrc_txn_rec.event_id := gme_common_pvt.g_transaction_header_id;
2919 l_rsrc_txn_rec.resources := l_resources;
2920 l_rsrc_txn_rec.resource_usage := p_rsrc_txn_rec.resource_usage;
2921 l_rsrc_txn_rec.trans_qty_um := l_usage_uom;
2922 l_rsrc_txn_rec.trans_date := l_trans_date;
2923 l_rsrc_txn_rec.completed_ind := 1;
2924 l_rsrc_txn_rec.posted_ind := 0;
2925 --Bug#6154309 only for ASQC batches overrided_protected_ind should be yes.
2926 IF l_batch_header.automatic_step_calculation = 1 THEN
2927 l_rsrc_txn_rec.overrided_protected_ind := 'Y';
2928 ELSE
2929 l_rsrc_txn_rec.overrided_protected_ind := 'N';
2930 END IF;
2931 l_rsrc_txn_rec.reason_id := l_reason_id;
2932 l_rsrc_txn_rec.start_date := p_rsrc_txn_rec.start_date;
2933 l_rsrc_txn_rec.end_date := p_rsrc_txn_rec.end_date;
2934 l_rsrc_txn_rec.delete_mark := 0;
2935 l_rsrc_txn_rec.text_code := 0;
2936
2937 IF p_rsrc_txn_rec.instance_id IS NOT NULL THEN
2938 l_rsrc_txn_rec.instance_id := p_rsrc_txn_rec.instance_id;
2939 ELSE
2940 l_rsrc_txn_rec.instance_id := l_instance_id;
2941 END IF;
2942
2943 l_rsrc_txn_rec.sequence_dependent_ind := 0;
2944
2945 IF NOT (gme_resource_txns_dbl.insert_row (l_rsrc_txn_rec
2946 ,x_rsrc_txn_rec) ) THEN
2947 RAISE rsrc_txn_insert_err;
2948 END IF;
2949
2950 l_rsrc_txn_rec := x_rsrc_txn_rec;
2951 -- update resource actual count and usage
2952 ---- l_step_resources.batchstep_resource_id := l_resource_txns.line_id;
2953 l_step_resources.batchstep_resource_id := l_rsrc_txn_rec.line_id;
2954
2955 IF NOT gme_batch_step_resources_dbl.fetch_row
2956 (p_batch_step_resources => l_step_resources
2957 ,x_batch_step_resources => l_step_resources) THEN
2958 RAISE rsrc_fetch_err;
2959 END IF;
2960
2961 l_step_resources.actual_rsrc_usage :=
2962 NVL (l_step_resources.actual_rsrc_usage, 0)
2963 + l_rsrc_txn_rec.resource_usage;
2964
2965 IF l_step_status = 2 THEN
2966 /* Lets now load the transactions associated with the batch into the temporary tblle */
2967 gme_trans_engine_util.load_rsrc_trans
2968 (p_batch_row => l_batch_header
2969 ,x_rsc_row_count => l_rsrc_trans_count
2970 ,x_return_status => l_return_status);
2971
2972 IF l_return_status <> x_return_status THEN
2973 RAISE error_load_trans;
2974 END IF;
2975
2976 gme_update_step_qty_pvt.reduce_pending_usage
2977 (p_batch_step_resources_rec => l_step_resources
2978 ,x_return_status => x_return_status);
2979
2980 IF x_return_status <> 'S' THEN
2981 RAISE reduce_pend_usage_err;
2982 END IF;
2983 END IF;
2984
2985 IF NOT gme_batch_step_resources_dbl.update_row
2986 (p_batch_step_resources => l_step_resources) THEN
2987 RAISE rsrc_update_err;
2988 END IF;
2989
2990 --22APR02 Pawan Kumar bug 2912743 added poc_trans_id
2991 -- Assigning the value of poc_trans_id
2992 ---x_poc_trans_id := l_resource_txns.poc_trans_id;
2993 IF g_debug <= gme_debug.g_log_procedure THEN
2994 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
2995 END IF;
2996 EXCEPTION
2997 WHEN validation_failure OR error_load_trans OR rsrc_txn_insert_err THEN
2998 x_return_status := fnd_api.g_ret_sts_error;
2999 WHEN rsrc_fetch_err OR rsrc_update_err OR reduce_pend_usage_err THEN
3000 x_return_status := fnd_api.g_ret_sts_error;
3001 WHEN OTHERS THEN
3002 IF g_debug <= gme_debug.g_log_unexpected THEN
3003 gme_debug.put_line ( 'When others exception in '
3004 || g_pkg_name
3005 || '.'
3006 || l_api_name
3007 || ' Error is '
3008 || SQLERRM);
3009 END IF;
3010
3011 x_return_status := fnd_api.g_ret_sts_unexp_error;
3012 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
3013 END insert_incr_actual_rsrc_txn;
3014
3015 /*==== =======================================================================================
3016 Procedure
3017 insert_timed_actual_rsrc_txn
3018 Description
3019 This particular procedure is used to insert completed rsrc txn rec and calculates the usage from provided txn dates
3020 Parameters
3021 (p_org_code,p_batch_no,step_no,activity and resource) to uniquely identify a resource
3022 p_trans_date transaction date of resource txn
3023 p_start_date start date of resource txn
3024 p_end_date end date of resource txn
3025 p_reason_code reason to insert a completed rsrc txn
3026 p_instance_id instance_id of the instance of rsrc txn(for WPS)
3027 p_instance_no instance_no of the instance of rsrc txn(for WPS)
3028 x_return_status reflects return status of the API
3029 History
3030 09JULY03 BUG#3041705 V. Ajay Kumar
3031 Port bug 2965882 to 11.5.10K.
3032 Modified code such that the difference in start date and end date
3033 is calculated in hours.
3034 02-SEP-04 Rishi Varma B3856541
3035 Added the new parameter to the validate_rsrc_param procedure call.
3036
3037 15-March-2005 Punit Kumar
3038 Convergence changes
3039 =============================================================================================*/
3040 PROCEDURE insert_timed_actual_rsrc_txn (
3041 p_org_code IN VARCHAR2
3042 ,p_batch_no IN VARCHAR2 := NULL
3043 ,p_batchstep_no IN NUMBER := NULL
3044 ,p_activity IN VARCHAR2 := NULL
3045 ,p_resource IN VARCHAR2 := NULL
3046 ,p_reason_name IN VARCHAR2,
3047 p_instance_no IN NUMBER
3048 ,p_rsrc_txn_rec IN gme_resource_txns%ROWTYPE
3049 ,x_rsrc_txn_rec IN OUT NOCOPY gme_resource_txns%ROWTYPE
3050 ,x_return_status OUT NOCOPY VARCHAR2)
3051 IS
3052 l_api_name CONSTANT VARCHAR2 (30)
3053 := 'insert_timed_actual_rsrc_txn';
3054 l_resource_txns gme_resource_txns%ROWTYPE;
3055 l_batch_header gme_batch_header%ROWTYPE;
3056 l_step_resources gme_batch_step_resources%ROWTYPE;
3057 l_resource_tbl gme_common_pvt.resource_transactions_tab;
3058 l_line_id NUMBER;
3059 l_inv_trans_count NUMBER;
3060 l_rsrc_trans_count NUMBER;
3061 l_step_status NUMBER;
3062 l_return_status VARCHAR2 (2);
3063 l_resources VARCHAR2 (16);
3064 l_usage_uom VARCHAR2 (4);
3065 l_txn_usage NUMBER;
3066 l_instance_id NUMBER;
3067 l_reason_id NUMBER;
3068 l_usage_time NUMBER;
3069 l_hour_um sy_uoms_mst.um_code%TYPE;
3070 l_rsrc_txn_rec gme_resource_txns%ROWTYPE;
3071
3072
3073 --Rishi Varma B3856541 02-09-2004
3074 l_trans_date DATE;
3075
3076 CURSOR cur_fetch_resource_dtl (v_line_id NUMBER)
3077 IS
3078 --- SELECT resources,usage_uom
3079 SELECT resources, usage_um
3080 FROM gme_batch_step_resources
3081 WHERE batchstep_resource_id = v_line_id;
3082
3083 validation_failure EXCEPTION;
3084 uom_conversion_err EXCEPTION;
3085 missing_profile_option EXCEPTION;
3086 rsrc_fetch_err EXCEPTION;
3087 rsrc_update_err EXCEPTION;
3088 rsrc_txn_insert_err EXCEPTION;
3089 reduce_pend_usage_err EXCEPTION;
3090 error_load_trans EXCEPTION;
3091 BEGIN
3092 IF g_debug <= gme_debug.g_log_procedure THEN
3093 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
3094 || l_api_name);
3095 END IF;
3096
3097 /* Initially let us assign the return status to success */
3098 x_return_status := fnd_api.g_ret_sts_success;
3099
3100 /*siva commented following IF condition to allow insertion of flexfileds
3101 without validation when p_validate_flexfields is FALSE */
3102 --IF gme_common_pvt.g_flex_validate_prof = 1 THEN
3103 /*Validate Flexfields using the new procedure gme_api_validate_flex_fld_pvt.validate_rsrc_txn_flex */
3104 gme_validate_flex_fld_pvt.validate_rsrc_txn_flex
3105 (p_resource_txn_rec => p_rsrc_txn_rec
3106 ,x_resource_txn_rec => x_rsrc_txn_rec
3107 ,x_return_status => l_return_status);
3108
3109 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3110 RAISE validation_failure;
3111 END IF;
3112 --END IF;
3113 l_rsrc_txn_rec := x_rsrc_txn_rec;
3114
3115 IF g_debug <= gme_debug.g_log_procedure THEN
3116 gme_debug.put_line ( 'before calling validate_rsrc_txn_param '
3117 || g_pkg_name
3118 || '.'
3119 || l_api_name);
3120 END IF;
3121
3122 validate_rsrc_txn_param (p_called_from => 3
3123 ,p_batchstep_rsrc_id => p_rsrc_txn_rec.line_id
3124 ,p_org_code => p_org_code
3125 ,p_batch_no => p_batch_no
3126 ,p_batchstep_no => p_batchstep_no
3127 ,p_activity => p_activity
3128 ,p_resource => p_resource
3129 ,p_trans_date => p_rsrc_txn_rec.trans_date
3130 ,p_start_date => p_rsrc_txn_rec.start_date
3131 ,p_end_date => p_rsrc_txn_rec.end_date
3132 ,p_usage => NULL
3133 ,p_reason_id => p_rsrc_txn_rec.reason_id
3134 ,p_reason_name => p_reason_name
3135 ,p_instance_id => p_rsrc_txn_rec.instance_id
3136 ,p_instance_no => p_instance_no
3137 ,x_line_id => l_line_id
3138 ,x_step_status => l_step_status
3139 ,x_batch_header_rec => l_batch_header
3140 ,x_instance_id => l_instance_id
3141 ,x_reason_id => l_reason_id
3142 ,x_return_status => x_return_status
3143 ,
3144 --Rishi Varma B3856541 02-09-2004
3145 x_trans_date => l_trans_date);
3146
3147 IF g_debug <= gme_debug.g_log_procedure THEN
3148 gme_debug.put_line ( 'after calling validate_rsrc_txn_param '
3149 || g_pkg_name
3150 || '.'
3151 || l_api_name);
3152 END IF;
3153
3154 IF x_return_status <> fnd_api.g_ret_sts_success THEN
3155 RAISE validation_failure;
3156 END IF;
3157
3158 /* V. Ajay Kumar Bug 3041705. Multiplied the difference of
3159 dates by 24 to convert it to hours */
3160 l_usage_time :=
3161 (p_rsrc_txn_rec.end_date - p_rsrc_txn_rec.start_date) * 24;
3162 l_hour_um :=
3163 fnd_profile.value_specific (NAME => 'BOM:HOUR_UOM_CODE'
3164 ,user_id => gme_common_pvt.g_user_ident);
3165
3166 IF (l_hour_um IS NULL) THEN
3167 gme_common_pvt.log_message ('GME_API_UNABLE_TO_GET_CONSTANT'
3168 ,'CONSTANT_NAME'
3169 ,'BOM:HOUR_UOM_CODE');
3170 RAISE missing_profile_option;
3171 END IF;
3172
3173 OPEN cur_fetch_resource_dtl (l_line_id);
3174
3175 FETCH cur_fetch_resource_dtl
3176 INTO l_resources, l_usage_uom;
3177
3178 CLOSE cur_fetch_resource_dtl;
3179
3180 /* siva from_name and to_name made null*/
3181 IF l_hour_um <> l_rsrc_txn_rec.trans_qty_um THEN
3182 l_txn_usage :=
3183 inv_convert.inv_um_convert (item_id => 0
3184 ,PRECISION => 5
3185 ,from_quantity => l_usage_time
3186 ,from_unit => l_hour_um
3187 ,to_unit => l_rsrc_txn_rec.trans_qty_um
3188 ,from_name => NULL
3189 ,to_name => NULL);
3190
3191 IF (l_txn_usage = -99999) THEN
3192 gme_common_pvt.log_message ('GME_RSRC_USG_NT_CNV_SYUOM'
3193 ,'SY_UOM'
3194 ,l_hour_um
3195 ,'RSRC_USG_UOM'
3196 ,l_usage_uom);
3197 RAISE uom_conversion_err;
3198 END IF;
3199 ELSE
3200 l_txn_usage := l_usage_time;
3201 END IF;
3202
3203 -- construct record for insertion
3204 ----l_resource_txns.doc_id := l_batch_header.batch_id;
3205 -- hard coding doc_type to PROD as rsrc txn can only exist for batchesi not FPO
3206 -- later if we decide to have this functionality for FPOs we would have to change code to
3207 -- get batch_type from l_batch_header variable
3208 l_rsrc_txn_rec.doc_id := l_batch_header.batch_id;
3209 l_rsrc_txn_rec.doc_type := 'PROD';
3210 l_rsrc_txn_rec.line_type := 0;
3211 l_rsrc_txn_rec.organization_id := l_batch_header.organization_id;
3212 l_rsrc_txn_rec.orgn_code := NULL;
3213 l_rsrc_txn_rec.line_id := l_line_id;
3214 l_rsrc_txn_rec.event_id := gme_common_pvt.g_transaction_header_id;
3215 l_rsrc_txn_rec.resources := l_resources;
3216 l_rsrc_txn_rec.resource_usage := l_txn_usage;
3217 l_rsrc_txn_rec.trans_qty_um := l_usage_uom;
3218 l_rsrc_txn_rec.trans_date := l_trans_date;
3219 l_rsrc_txn_rec.completed_ind := 1;
3220 l_rsrc_txn_rec.posted_ind := 0;
3221 --Bug#6154309 only for ASQC batches overrided_protected_ind should be yes.
3222 IF l_batch_header.automatic_step_calculation = 1 THEN
3223 l_rsrc_txn_rec.overrided_protected_ind := 'Y';
3224 ELSE
3225 l_rsrc_txn_rec.overrided_protected_ind := 'N';
3226 END IF;
3227 l_rsrc_txn_rec.reason_id := l_reason_id;
3228 l_rsrc_txn_rec.start_date := p_rsrc_txn_rec.start_date;
3229 l_rsrc_txn_rec.end_date := p_rsrc_txn_rec.end_date;
3230 l_rsrc_txn_rec.delete_mark := 0;
3231 l_rsrc_txn_rec.text_code := 0;
3232
3233 IF p_rsrc_txn_rec.instance_id IS NOT NULL THEN
3234 l_rsrc_txn_rec.instance_id := p_rsrc_txn_rec.instance_id;
3235 ELSE
3236 l_rsrc_txn_rec.instance_id := l_instance_id;
3237 END IF;
3238
3239 l_rsrc_txn_rec.sequence_dependent_ind := 0;
3240
3241 IF NOT (gme_resource_txns_dbl.insert_row (l_rsrc_txn_rec
3242 ,x_rsrc_txn_rec) ) THEN
3243 RAISE rsrc_txn_insert_err;
3244 END IF;
3245
3246 l_rsrc_txn_rec := x_rsrc_txn_rec;
3247 -- update resource actual count and usage
3248 --l_step_resources.batchstep_resource_id := l_resource_txns.line_id;
3249 l_step_resources.batchstep_resource_id := l_rsrc_txn_rec.line_id;
3250
3251 IF NOT gme_batch_step_resources_dbl.fetch_row
3252 (p_batch_step_resources => l_step_resources
3253 ,x_batch_step_resources => l_step_resources) THEN
3254 RAISE rsrc_fetch_err;
3255 END IF;
3256
3257 l_step_resources.actual_rsrc_usage :=
3258 NVL (l_step_resources.actual_rsrc_usage, 0)
3259 + l_txn_usage;
3260
3261 IF l_step_status = 2 THEN
3262 /* Lets now load the transactions associated with the batch into the temporary tblle */
3263 gme_trans_engine_util.load_rsrc_trans
3264 (p_batch_row => l_batch_header
3265 ,x_rsc_row_count => l_rsrc_trans_count
3266 ,x_return_status => l_return_status);
3267
3268 IF l_return_status <> x_return_status THEN
3269 RAISE error_load_trans;
3270 END IF;
3271
3272 gme_update_step_qty_pvt.reduce_pending_usage
3273 (p_batch_step_resources_rec => l_step_resources
3274 ,x_return_status => x_return_status);
3275
3276 IF x_return_status <> 'S' THEN
3277 RAISE reduce_pend_usage_err;
3278 END IF;
3279 END IF;
3280
3281 IF NOT gme_batch_step_resources_dbl.update_row
3282 (p_batch_step_resources => l_step_resources) THEN
3283 RAISE rsrc_update_err;
3284 END IF;
3285
3286 --22APR02 Pawan Kumar bug 2912743 added poc_trans_id
3287 -- Assigning the value of poc_trans_id
3288
3289 ---x_poc_trans_id := l_resource_txns.poc_trans_id;
3290 IF g_debug <= gme_debug.g_log_procedure THEN
3291 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
3292 END IF;
3293 EXCEPTION
3294 WHEN validation_failure OR missing_profile_option OR error_load_trans OR rsrc_txn_insert_err THEN
3295 x_return_status := fnd_api.g_ret_sts_error;
3296 WHEN rsrc_fetch_err OR rsrc_update_err OR reduce_pend_usage_err THEN
3297 x_return_status := fnd_api.g_ret_sts_error;
3298 WHEN uom_conversion_err THEN
3299 x_return_status := fnd_api.g_ret_sts_error;
3300 WHEN OTHERS THEN
3301 IF g_debug <= gme_debug.g_log_unexpected THEN
3302 gme_debug.put_line ( 'When others exception in '
3303 || g_pkg_name
3304 || '.'
3305 || l_api_name
3306 || ' Error is '
3307 || SQLERRM);
3308 END IF;
3309
3310 x_return_status := fnd_api.g_ret_sts_unexp_error;
3311 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
3312 END insert_timed_actual_rsrc_txn;
3313
3314 /*==== =======================================================================================
3315 Procedure
3316 start_cmplt_actual_rsrc_txn
3317 Description
3318 This particular procedure is used to insert a start completed rsrc txn rec
3319 Parameters
3320 (p_org_code,p_batch_no,step_no,activity and resource) to uniquely identify a resource
3321 p_trans_date transaction date of resource txn
3322 p_start_date start date of resource txn
3323 p_reason_code reason to insert a completed rsrc txn
3324 p_instance_id instance_id of the instance of rsrc txn(for WPS)
3325 p_instance_no instance_no of the instance of rsrc txn(for WPS)
3326 x_return_status reflects return status of the API
3327 02-SEP-04 Rishi Varma B3856541
3328 Added the new parameter to the validate_rsrc_param procedure call.
3329 =============================================================================================*/
3330 PROCEDURE start_cmplt_actual_rsrc_txn (
3331 /* inventory organization under which the batch was created */
3332 p_org_code IN VARCHAR2
3333 ,p_batch_no IN VARCHAR2 := NULL
3334 ,p_batchstep_no IN NUMBER := NULL
3335 ,p_activity IN VARCHAR2 := NULL
3336 ,p_resource IN VARCHAR2 := NULL
3337 ,p_reason_name IN VARCHAR2
3338 ,p_instance_no IN NUMBER
3339 ,p_rsrc_txn_rec IN gme_resource_txns%ROWTYPE
3340 ,x_rsrc_txn_rec IN OUT NOCOPY gme_resource_txns%ROWTYPE
3341 ,x_return_status OUT NOCOPY VARCHAR2)
3342 IS
3343 l_api_name CONSTANT VARCHAR2 (30) := 'start_cmplt_actual_rsrc_txn';
3344 l_resource_txns gme_resource_txns%ROWTYPE;
3345 l_batch_header gme_batch_header%ROWTYPE;
3346 l_line_id NUMBER;
3347 l_step_status NUMBER;
3348 l_return_status VARCHAR2 (2);
3349 l_resources VARCHAR2 (16);
3350 l_usage_uom VARCHAR2 (4);
3351 l_instance_id NUMBER;
3352 l_reason_id NUMBER;
3353
3354 l_rsrc_txn_rec gme_resource_txns%ROWTYPE;
3355
3356 CURSOR cur_fetch_resource_dtl (v_line_id NUMBER)
3357 IS
3358 ---SELECT resources,usage_uom
3359 SELECT resources, usage_um
3360 FROM gme_batch_step_resources
3361 WHERE batchstep_resource_id = v_line_id;
3362
3363 --Rishi Varma B3856541 02-09-2004 start
3364 l_trans_date DATE;
3365 validation_failure EXCEPTION;
3366 rsrc_txn_insert_err EXCEPTION;
3367 BEGIN
3368 IF g_debug <= gme_debug.g_log_procedure THEN
3369 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
3370 || l_api_name);
3371 END IF;
3372
3373 /* Initially let us assign the return status to success */
3374 x_return_status := fnd_api.g_ret_sts_success;
3375
3376 /*siva commented following IF condition to allow insertion of flexfileds
3377 without validation when p_validate_flexfields is FALSE */
3378 -- IF gme_common_pvt.g_flex_validate_prof = 1 THEN
3379 --Validate Flexfields using the new procedure
3380 gme_validate_flex_fld_pvt.validate_rsrc_txn_flex
3381 (p_resource_txn_rec => p_rsrc_txn_rec
3382 ,x_resource_txn_rec => x_rsrc_txn_rec
3383 ,x_return_status => x_return_status);
3384
3385 IF x_return_status <> fnd_api.g_ret_sts_success THEN
3386 RAISE validation_failure;
3387 END IF;
3388 --END IF;
3389 /* siva added the following line */
3390 l_rsrc_txn_rec := x_rsrc_txn_rec;
3391
3392
3393 /* Initially let us assign the return status to success */
3394 l_return_status := fnd_api.g_ret_sts_success;
3395
3396 IF g_debug <= gme_debug.g_log_procedure THEN
3397 gme_debug.put_line ( 'before calling validate_rsrc_txn_param '
3398 || g_pkg_name
3399 || '.'
3400 || l_api_name);
3401 END IF;
3402
3403 validate_rsrc_txn_param (p_called_from => 4
3404 ,p_batchstep_rsrc_id => p_rsrc_txn_rec.line_id
3405 ,p_org_code => p_org_code
3406 ,p_batch_no => p_batch_no
3407 ,p_batchstep_no => p_batchstep_no
3408 ,p_activity => p_activity
3409 ,p_resource => p_resource
3410 ,p_trans_date => p_rsrc_txn_rec.trans_date
3411 ,p_start_date => p_rsrc_txn_rec.start_date
3412 ,p_end_date => p_rsrc_txn_rec.end_date
3413 ,p_usage => 0
3414 ,p_reason_id => p_rsrc_txn_rec.reason_id
3415 ,p_reason_name => p_reason_name
3416 ,p_instance_id => p_rsrc_txn_rec.instance_id
3417 ,p_instance_no => p_instance_no
3418 ,x_line_id => l_line_id
3419 ,x_step_status => l_step_status
3420 ,x_batch_header_rec => l_batch_header
3421 ,x_instance_id => l_instance_id
3422 ,x_reason_id => l_reason_id
3423 ,x_return_status => l_return_status
3424 ,x_trans_date => l_trans_date);
3425
3426 IF g_debug <= gme_debug.g_log_procedure THEN
3427 gme_debug.put_line ( 'after calling validate_rsrc_txn_param '
3428 || g_pkg_name
3429 || '.'
3430 || l_api_name);
3431 END IF;
3432
3433 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3434 RAISE validation_failure;
3435 END IF;
3436
3437 OPEN cur_fetch_resource_dtl (l_line_id);
3438
3439 FETCH cur_fetch_resource_dtl
3440 INTO l_resources, l_usage_uom;
3441
3442 CLOSE cur_fetch_resource_dtl;
3443
3444
3445 -- construct record for insertion
3446 l_rsrc_txn_rec.doc_id := l_batch_header.batch_id;
3447 l_rsrc_txn_rec.doc_type := 'PROD';
3448 l_rsrc_txn_rec.line_type := 0;
3449 l_rsrc_txn_rec.organization_id := l_batch_header.organization_id;
3450 l_rsrc_txn_rec.orgn_code := NULL;
3451 l_rsrc_txn_rec.line_id := l_line_id;
3452 l_rsrc_txn_rec.event_id := gme_common_pvt.g_transaction_header_id;
3453 l_rsrc_txn_rec.resources := l_resources;
3454 l_rsrc_txn_rec.resource_usage := 0;
3455 l_rsrc_txn_rec.trans_qty_um := l_usage_uom;
3456
3457 IF g_debug <= gme_debug.g_log_procedure THEN
3458 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' l_trans_date ' || l_trans_date);
3459 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' p_rsrc_txn_rec.trans_date ' || p_rsrc_txn_rec.trans_date);
3460 END IF;
3461
3462 IF l_trans_date IS NOT NULL THEN
3463 l_rsrc_txn_rec.trans_date := l_trans_date;
3464 ELSE
3465 l_rsrc_txn_rec.trans_date := p_rsrc_txn_rec.trans_date;
3466 END IF;
3467
3468 l_rsrc_txn_rec.completed_ind := 1;
3469 l_rsrc_txn_rec.posted_ind := 0;
3470 --Bug#6154309 only for ASQC batches overrided_protected_ind should be yes.
3471 IF l_batch_header.automatic_step_calculation = 1 THEN
3472 l_rsrc_txn_rec.overrided_protected_ind := 'Y';
3473 ELSE
3474 l_rsrc_txn_rec.overrided_protected_ind := 'N';
3475 END IF;
3476 l_rsrc_txn_rec.reason_id := l_reason_id;
3477 l_rsrc_txn_rec.start_date := p_rsrc_txn_rec.start_date;
3478 l_rsrc_txn_rec.end_date := p_rsrc_txn_rec.end_date;
3479 l_rsrc_txn_rec.delete_mark := 0;
3480 l_rsrc_txn_rec.text_code := 0;
3481 IF p_rsrc_txn_rec.instance_id IS NOT NULL THEN
3482 l_rsrc_txn_rec.instance_id := p_rsrc_txn_rec.instance_id;
3483 ELSE
3484 l_rsrc_txn_rec.instance_id := l_instance_id;
3485 END IF;
3486
3487 l_rsrc_txn_rec.sequence_dependent_ind := 0;
3488
3489 IF NOT (gme_resource_txns_dbl.insert_row (l_rsrc_txn_rec
3490 ,x_rsrc_txn_rec) ) THEN
3491 RAISE rsrc_txn_insert_err;
3492 END IF;
3493
3494 IF g_debug <= gme_debug.g_log_procedure THEN
3495 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name|| ' with return status '||x_return_status);
3496 END IF;
3497 EXCEPTION
3498 WHEN validation_failure OR rsrc_txn_insert_err THEN
3499 x_return_status := fnd_api.g_ret_sts_error;
3500 WHEN OTHERS THEN
3501 IF g_debug <= gme_debug.g_log_unexpected THEN
3502 gme_debug.put_line ( 'When others exception in '
3503 || g_pkg_name
3504 || '.'
3505 || l_api_name
3506 || ' Error is '
3507 || SQLERRM);
3508 END IF;
3509
3510 x_return_status := fnd_api.g_ret_sts_unexp_error;
3511 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
3512 END start_cmplt_actual_rsrc_txn;
3513
3514 /*==== =======================================================================================
3515 Procedure
3516 end_cmplt_actual_rsrc_txn
3517 Description
3518 This particular procedure is used to end a started completed rsrc txn rec and calculates
3519 the usage from rsrc txn dates
3520 Parameters
3521 p_poc_trans_id id to uniquely identify a resource txn
3522 p_trans_date transaction date of resource txn
3523 p_end_date end date of resource txn
3524 p_reason_name reason to insert a completed rsrc txn
3525 p_instance_id instance_id of the instance of rsrc txn(for WPS)
3526 p_instance_no instance_no of the instance of rsrc txn(for WPS)
3527 x_return_status reflects return status of the API
3528 History
3529 09JULY03 BUG#3041705 V. Ajay Kumar
3530 Port bug 2965882 to 11.5.10K.
3531 Modified code such that the difference in start date and end date
3532 is calculated in hours.
3533 RajaSekhar Reddy 21-MAY-2004 BUG#3610141
3534 Added code to assign plan_rsrc_count to actual_rsrc_count if actual_rsrc_count is NULL.
3535 02-SEP-04 Rishi Varma B3856541
3536 Added the new parameter to the validate_rsrc_param procedure call.
3537
3538 14th March 2005 Punit kumar
3539 Convergence changes
3540 =============================================================================================*/
3541 PROCEDURE end_cmplt_actual_rsrc_txn (
3542 p_rsrc_txn_rec IN gme_resource_txns%ROWTYPE
3543 ,p_reason_name IN VARCHAR2
3544 ,p_instance_no IN NUMBER
3545 ,x_rsrc_txn_rec IN OUT NOCOPY gme_resource_txns%ROWTYPE
3546 ,x_return_status OUT NOCOPY VARCHAR2)
3547 IS
3548 l_api_name CONSTANT VARCHAR2 (30) := 'end_cmplt_actual_rsrc_txn';
3549 l_resource_txns gme_resource_txns%ROWTYPE;
3550 l_batch_header gme_batch_header%ROWTYPE;
3551 l_step_resources gme_batch_step_resources%ROWTYPE;
3552 l_resource_tbl gme_common_pvt.resource_transactions_tab;
3553 --Begin Bug# 3479669
3554 l_tran_rec gme_resource_txns%ROWTYPE;
3555 --End Bug# 3479669
3556 l_step_status NUMBER;
3557 l_return_status VARCHAR2 (2);
3558 l_txn_usage NUMBER;
3559 l_instance_id NUMBER;
3560 l_reason_id NUMBER;
3561 l_dummy NUMBER;
3562 l_usage_time NUMBER;
3563 l_inv_trans_count NUMBER;
3564 l_line_id NUMBER;
3565 l_rsrc_trans_count NUMBER;
3566 l_hour_um sy_uoms_mst.um_code%TYPE;
3567 l_trans_date DATE;
3568 l_rsrc_txn_rec gme_resource_txns%ROWTYPE;
3569
3570 --siva added following variables
3571 x_trans_date DATE;
3572 x_reason_id NUMBER;
3573 x_instance_id NUMBER;
3574
3575 CURSOR cur_validate_poc_trans_id (v_poc_trans_id NUMBER)
3576 IS
3577 SELECT 1
3578 FROM gme_resource_txns
3579 WHERE poc_trans_id = v_poc_trans_id;
3580
3581 invalid_poc_trans_id EXCEPTION;
3582 validation_failure EXCEPTION;
3583 uom_conversion_err EXCEPTION;
3584 missing_profile_option EXCEPTION;
3585 rsrc_txn_fetch_err EXCEPTION;
3586 rsrc_fetch_err EXCEPTION;
3587 rsrc_update_err EXCEPTION;
3588 rsrc_txn_upd_err EXCEPTION;
3589 reduce_pend_usage_err EXCEPTION;
3590 error_load_trans EXCEPTION;
3591 invalid_txn_for_end EXCEPTION;
3592 expected_error EXCEPTION;
3593 rsrc_txn_ins_err EXCEPTION;
3594 BEGIN
3595 IF g_debug <= gme_debug.g_log_procedure THEN
3596 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
3597 || l_api_name);
3598 END IF;
3599
3600 /* Initially let us assign the return status to success */
3601 x_return_status := fnd_api.g_ret_sts_success;
3602
3603 -- validate poc_trans_id right here as validate_rsrc_txn_param doesnt validate it
3604
3605 OPEN cur_validate_poc_trans_id (p_rsrc_txn_rec.poc_trans_id);
3606
3607 FETCH cur_validate_poc_trans_id
3608 INTO l_dummy;
3609
3610 IF cur_validate_poc_trans_id%NOTFOUND THEN
3611 gme_common_pvt.log_message ('GME_INV_POC_TRANS_ID'
3612 ,'POC_TRANS_ID'
3613 ,p_rsrc_txn_rec.poc_trans_id);
3614 RAISE invalid_poc_trans_id;
3615 END IF;
3616
3617 CLOSE cur_validate_poc_trans_id;
3618
3619 /*siva commented following IF condition to allow insertion of flexfileds
3620 without validation when p_validate_flexfields is FALSE */
3621 --IF gme_common_pvt.g_flex_validate_prof = 1 THEN
3622 /* Validate Flexfields using the new procedure */
3623 gme_validate_flex_fld_pvt.validate_rsrc_txn_flex
3624 (p_resource_txn_rec => p_rsrc_txn_rec
3625 ,x_resource_txn_rec => x_rsrc_txn_rec
3626 ,x_return_status => x_return_status);
3627
3628 IF x_return_status <> fnd_api.g_ret_sts_success THEN
3629 RAISE validation_failure;
3630 END IF;
3631 --END IF;
3632
3633 l_rsrc_txn_rec.poc_trans_id := p_rsrc_txn_rec.poc_trans_id;
3634
3635 IF NOT (gme_resource_txns_dbl.fetch_row (l_rsrc_txn_rec, l_rsrc_txn_rec) ) THEN
3636 RAISE rsrc_txn_fetch_err;
3637 END IF;
3638
3639 IF ( (l_rsrc_txn_rec.resource_usage <> 0)
3640 OR (l_rsrc_txn_rec.start_date <> l_rsrc_txn_rec.end_date)
3641 OR (l_rsrc_txn_rec.completed_ind <> 1)
3642 OR (l_rsrc_txn_rec.delete_mark <> 0) ) THEN
3643 gme_common_pvt.log_message ('GME_INVALID_TXN_FOR_END');
3644 RAISE invalid_txn_for_end;
3645 END IF;
3646
3647 /* V. Ajay Kumar Bug 3041705. Multiplied the difference of
3648 dates by 24 to convert it to hours */
3649 l_usage_time :=
3650 (p_rsrc_txn_rec.end_date - l_rsrc_txn_rec.start_date) * 24;
3651
3652 IF l_usage_time < 0 THEN
3653 gme_common_pvt.log_message ('PM_BADENDDATE');
3654 RAISE expected_error;
3655 END IF;
3656
3657 l_hour_um :=
3658 fnd_profile.value_specific (NAME => 'BOM:HOUR_UOM_CODE'
3659 ,user_id => gme_common_pvt.g_user_ident);
3660
3661 IF (l_hour_um IS NULL) THEN
3662 gme_common_pvt.log_message ('GME_API_UNABLE_TO_GET_CONSTANT'
3663 ,'CONSTANT_NAME'
3664 ,'BOM:HOUR_UOM_CODE');
3665 RAISE missing_profile_option;
3666 END IF;
3667
3668 /* siva from_name and to_name made NULL */
3669 IF l_hour_um <> l_rsrc_txn_rec.trans_qty_um THEN
3670 l_txn_usage :=
3671 inv_convert.inv_um_convert (item_id => 0
3672 ,PRECISION => 5
3673 ,from_quantity => l_usage_time
3674 ,from_unit => l_hour_um
3675 ,to_unit => l_rsrc_txn_rec.trans_qty_um
3676 ,from_name => NULL
3677 ,to_name => NULL);
3678
3679 IF (l_txn_usage = -99999) THEN
3680 gme_common_pvt.log_message ('GME_RSRC_USG_NT_CNV_SYUOM'
3681 ,'SY_UOM'
3682 ,l_hour_um
3683 ,'RSRC_USG_UOM'
3684 ,l_rsrc_txn_rec.trans_qty_um);
3685 RAISE uom_conversion_err;
3686 END IF;
3687 ELSE
3688 l_txn_usage := l_usage_time;
3689 END IF;
3690
3691 -- following procedure is being called to validate trans_date, end_date, reason_code
3692 -- instance_id and instance_no
3693 -- since we are not passing poc_trans_id to validate procedure so we cannot figure out
3694 -- batch_id there so using one of the params that is not used by current procedure to pass
3695 -- batch_id to validations procedure to fetch batch rec.
3696 -- using p_batchstep_rsrc_id to pass batch_id
3697 -- B2498487 passing value of overrided_protected_ind value to validations procedure
3698 -- so that we dont have to fetch it there and can directly work on it
3699 -- using p_resource to pass overrided_protected_ind value
3700
3701 IF p_rsrc_txn_rec.trans_date IS NULL THEN
3702 l_trans_date := l_rsrc_txn_rec.trans_date;
3703 ELSE
3704 l_trans_date := p_rsrc_txn_rec.trans_date;
3705 END IF;
3706
3707 /* siva added following code begin */
3708 IF p_rsrc_txn_rec.reason_id IS NULL THEN
3709 l_reason_id := l_rsrc_txn_rec.reason_id;
3710 ELSE
3711 l_reason_id := p_rsrc_txn_rec.reason_id;
3712 END IF;
3713
3714 IF p_rsrc_txn_rec.instance_id IS NULL THEN
3715 l_instance_id := l_rsrc_txn_rec.instance_id;
3716 ELSE
3717 l_instance_id := p_rsrc_txn_rec.instance_id;
3718 END IF;
3719 /* siva end */
3720
3721 IF g_debug <= gme_debug.g_log_procedure THEN
3722 gme_debug.put_line ( 'before calling validate_rsrc_txn_param '
3723 || g_pkg_name
3724 || '.'
3725 || l_api_name);
3726 END IF;
3727
3728 validate_rsrc_txn_param (p_called_from => 5
3729 ,p_batchstep_rsrc_id => l_rsrc_txn_rec.line_id
3730 ,p_org_code => NULL
3731 ,p_batch_no => NULL
3732 ,p_batchstep_no => NULL
3733 ,p_activity => NULL
3734 ,p_resource => l_rsrc_txn_rec.resources
3735 /* siva passing l_trans_date instead of l_rsrc_txn_rec.trans_date */
3736 ,p_trans_date => l_trans_date
3737 ,p_start_date => l_rsrc_txn_rec.start_date
3738 ,p_end_date => l_rsrc_txn_rec.end_date
3739 ,p_usage => l_txn_usage
3740 ,p_reason_id => l_reason_id
3741 ,p_reason_name => p_reason_name
3742 ,p_instance_id => l_instance_id
3743 ,p_instance_no => p_instance_no
3744 ,x_line_id => l_line_id
3745 ,x_step_status => l_step_status
3746 ,x_batch_header_rec => l_batch_header
3747 ,x_instance_id => x_instance_id
3748 ,x_reason_id => x_reason_id
3749 ,x_return_status => x_return_status
3750 ,x_trans_date => x_trans_date);
3751
3752 IF g_debug <= gme_debug.g_log_procedure THEN
3753 gme_debug.put_line ( 'after calling validate_rsrc_txn_param '
3754 || g_pkg_name
3755 || '.'
3756 || l_api_name);
3757 END IF;
3758
3759 IF x_return_status <> fnd_api.g_ret_sts_success THEN
3760 RAISE validation_failure;
3761 END IF;
3762
3763 -- construct record for updation
3764 l_rsrc_txn_rec.resource_usage := l_txn_usage;
3765 /* siva changed to x_trans_date, x_reason_id,x_instance_id */
3766 l_rsrc_txn_rec.trans_date := x_trans_date;
3767 l_rsrc_txn_rec.reason_id := x_reason_id;
3768 l_rsrc_txn_rec.end_date := p_rsrc_txn_rec.end_date;
3769 l_rsrc_txn_rec.instance_id := x_instance_id;
3770
3771 --siva copying flex field values
3772 --IF gme_common_pvt.g_flex_validate_prof = 1 THEN
3773 l_rsrc_txn_rec.attribute_category := x_rsrc_txn_rec.attribute_category;
3774 l_rsrc_txn_rec.attribute1 := x_rsrc_txn_rec.attribute1;
3775 l_rsrc_txn_rec.attribute2 := x_rsrc_txn_rec.attribute2;
3776 l_rsrc_txn_rec.attribute3 := x_rsrc_txn_rec.attribute3;
3777 l_rsrc_txn_rec.attribute4 := x_rsrc_txn_rec.attribute4;
3778 l_rsrc_txn_rec.attribute5 := x_rsrc_txn_rec.attribute5;
3779 l_rsrc_txn_rec.attribute6 := x_rsrc_txn_rec.attribute6;
3780 l_rsrc_txn_rec.attribute7 := x_rsrc_txn_rec.attribute7;
3781 l_rsrc_txn_rec.attribute8 := x_rsrc_txn_rec.attribute8;
3782 l_rsrc_txn_rec.attribute9 := x_rsrc_txn_rec.attribute9;
3783 l_rsrc_txn_rec.attribute10 := x_rsrc_txn_rec.attribute10;
3784 l_rsrc_txn_rec.attribute11 := x_rsrc_txn_rec.attribute11;
3785 l_rsrc_txn_rec.attribute12 := x_rsrc_txn_rec.attribute12;
3786 l_rsrc_txn_rec.attribute13 := x_rsrc_txn_rec.attribute13;
3787 l_rsrc_txn_rec.attribute14 := x_rsrc_txn_rec.attribute14;
3788 l_rsrc_txn_rec.attribute15 := x_rsrc_txn_rec.attribute15;
3789 l_rsrc_txn_rec.attribute16 := x_rsrc_txn_rec.attribute16;
3790 l_rsrc_txn_rec.attribute17 := x_rsrc_txn_rec.attribute17;
3791 l_rsrc_txn_rec.attribute18 := x_rsrc_txn_rec.attribute18;
3792 l_rsrc_txn_rec.attribute19 := x_rsrc_txn_rec.attribute19;
3793 l_rsrc_txn_rec.attribute20 := x_rsrc_txn_rec.attribute20;
3794 l_rsrc_txn_rec.attribute21 := x_rsrc_txn_rec.attribute21;
3795 l_rsrc_txn_rec.attribute22 := x_rsrc_txn_rec.attribute22;
3796 l_rsrc_txn_rec.attribute23 := x_rsrc_txn_rec.attribute23;
3797 l_rsrc_txn_rec.attribute24 := x_rsrc_txn_rec.attribute24;
3798 l_rsrc_txn_rec.attribute25 := x_rsrc_txn_rec.attribute25;
3799 l_rsrc_txn_rec.attribute26 := x_rsrc_txn_rec.attribute26;
3800 l_rsrc_txn_rec.attribute27 := x_rsrc_txn_rec.attribute27;
3801 l_rsrc_txn_rec.attribute28 := x_rsrc_txn_rec.attribute28;
3802 l_rsrc_txn_rec.attribute29 := x_rsrc_txn_rec.attribute29;
3803 l_rsrc_txn_rec.attribute30 := x_rsrc_txn_rec.attribute30;
3804 -- END IF;
3805
3806 --Begin Bug#3479669
3807 IF g_debug <= gme_debug.g_log_procedure THEN
3808 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
3809 || l_api_name);
3810 END IF;
3811
3812 IF l_rsrc_txn_rec.posted_ind = 1 THEN
3813 l_rsrc_txn_rec.posted_ind := 0;
3814
3815 IF NOT (gme_resource_txns_dbl.insert_row (l_rsrc_txn_rec, l_tran_rec) ) THEN
3816 RAISE rsrc_txn_ins_err;
3817 END IF;
3818 x_rsrc_txn_rec := l_tran_rec ;
3819 ELSE
3820 --End Bug#3479669
3821
3822 ---IF NOT (GME_RESOURCE_TXNS_DBL.update_row(l_resource_txns)) THEN
3823 IF NOT (gme_resource_txns_dbl.update_row (l_rsrc_txn_rec) ) THEN
3824 RAISE rsrc_txn_upd_err;
3825 END IF;
3826
3827 x_rsrc_txn_rec := l_rsrc_txn_rec ;
3828
3829 --Begin Bug#3479669
3830 IF g_debug <= gme_debug.g_log_procedure THEN
3831 gme_debug.put_line ( 'Entering api '
3832 || g_pkg_name
3833 || '.'
3834 || l_api_name);
3835 END IF;
3836 END IF;
3837
3838 ----x_poc_trans_id := l_tran_rec.poc_trans_id;
3839 --End Bug#3479669
3840
3841 -- update resource actual count and usage
3842 ---l_step_resources.batchstep_resource_id := l_resource_txns.line_id;
3843 l_step_resources.batchstep_resource_id := l_rsrc_txn_rec.line_id;
3844
3845 IF NOT gme_batch_step_resources_dbl.fetch_row
3846 (p_batch_step_resources => l_step_resources
3847 ,x_batch_step_resources => l_step_resources) THEN
3848 RAISE rsrc_fetch_err;
3849 END IF;
3850
3851 l_step_resources.actual_rsrc_usage :=
3852 NVL (l_step_resources.actual_rsrc_usage, 0)
3853 + l_txn_usage;
3854
3855 --BEGIN BUG#3610141 RajaSekhar
3856 IF l_step_resources.actual_rsrc_count IS NULL THEN
3857 l_step_resources.actual_rsrc_count :=
3858 l_step_resources.plan_rsrc_count;
3859 END IF;
3860
3861 --END BUG#3610141
3862 IF l_step_status = 2 THEN
3863 /* Lets now load the transactions associated with the batch into the temporary tblle */
3864 gme_trans_engine_util.load_rsrc_trans
3865 (p_batch_row => l_batch_header
3866 ,x_rsc_row_count => l_rsrc_trans_count
3867 ,x_return_status => l_return_status);
3868
3869 IF l_return_status <> x_return_status THEN
3870 RAISE error_load_trans;
3871 END IF;
3872
3873 gme_update_step_qty_pvt.reduce_pending_usage
3874 (p_batch_step_resources_rec => l_step_resources
3875 ,x_return_status => x_return_status);
3876
3877 IF x_return_status <> 'S' THEN
3878 RAISE reduce_pend_usage_err;
3879 END IF;
3880 END IF;
3881
3882 IF NOT gme_batch_step_resources_dbl.update_row
3883 (p_batch_step_resources => l_step_resources) THEN
3884 RAISE rsrc_update_err;
3885 END IF;
3886
3887 IF g_debug <= gme_debug.g_log_procedure THEN
3888 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
3889 END IF;
3890 EXCEPTION
3891 WHEN expected_error OR missing_profile_option THEN
3892 x_return_status := fnd_api.g_ret_sts_error;
3893 --Begin Bug#3479669
3894 --Added rsrc_txn_ins_err exception
3895 WHEN error_load_trans OR invalid_poc_trans_id OR rsrc_txn_upd_err OR rsrc_txn_ins_err THEN
3896 --End Bug#3479669
3897 x_return_status := fnd_api.g_ret_sts_error;
3898 WHEN validation_failure THEN
3899 x_return_status := fnd_api.g_ret_sts_error;
3900 WHEN uom_conversion_err OR invalid_txn_for_end THEN
3901 x_return_status := fnd_api.g_ret_sts_error;
3902 WHEN rsrc_fetch_err OR rsrc_txn_fetch_err OR rsrc_update_err OR reduce_pend_usage_err THEN
3903 x_return_status := fnd_api.g_ret_sts_error;
3904 WHEN OTHERS THEN
3905 IF g_debug <= gme_debug.g_log_unexpected THEN
3906 gme_debug.put_line ( 'When others exception in '
3907 || g_pkg_name
3908 || '.'
3909 || l_api_name
3910 || ' Error is '
3911 || SQLERRM);
3912 END IF;
3913
3914 x_return_status := fnd_api.g_ret_sts_unexp_error;
3915 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
3916 END end_cmplt_actual_rsrc_txn;
3917 END gme_resource_engine_pvt;