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