[Home] [Help]
PACKAGE BODY: APPS.IGC_CC_APPROVAL_WF_PKG
Source
1 PACKAGE BODY IGC_CC_APPROVAL_WF_PKG AS
2 /* $Header: IGCVAWFB.pls 120.7.12000000.4 2007/10/15 11:26:39 smannava ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IGC_CC_APPROVAL_WF_PKG';
5 --l_debug_mode VARCHAR2(1) := NVL(FND_PROFILE.VALUE('IGC_DEBUG_ENABLED'),'N');
6 g_debug_mode VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
7
8 g_cc_state igc_cc_headers.cc_state%TYPE;
9 g_cc_enc_status igc_cc_headers.cc_encmbrnc_status%TYPE;
10 g_cc_appr_status igc_cc_headers.cc_apprvl_status%TYPE;
11 g_bc_reqired VARCHAR2(1);
12 g_cc_type igc_cc_headers.cc_type%TYPE;
13 g_note igc_cc_headers.cc_desc%TYPE;
14 g_reject_note VARCHAR2(32000);
15 g_bc_failure_message VARCHAR2(32000);
16 g_error_text VARCHAR2(32000);
17 g_cc_version_number igc_cc_headers.cc_version_num%TYPE;
18 g_bc_executed VARCHAR2(1);
19 g_cc_ctrl_status igc_cc_headers.cc_ctrl_status%TYPE;
20 g_org_id igc_cc_headers.org_id%TYPE;
21 g_sob_id igc_cc_headers.set_of_books_id%TYPE;
22 g_cc_header_id igc_cc_headers.cc_header_id%TYPE;
23 g_itemtype VARCHAR2(50);
24 g_itemkey VARCHAR2(50);
25 g_wf_version NUMBER;
26 g_cc_new_state igc_cc_headers.cc_state%TYPE;
27 g_cc_new_enc_status igc_cc_headers.cc_encmbrnc_status%TYPE;
28 g_cc_new_appr_status igc_cc_headers.cc_apprvl_status%TYPE;
29 g_owner_id igc_cc_headers.CC_OWNER_USER_ID%TYPE;
30 g_approver_id igc_cc_headers.CC_OWNER_USER_ID%TYPE;
31 g_acct_date igc_cc_headers.cc_acct_date%TYPE;
32 g_owner_name VARCHAR2(255);
33 g_preparer_id igc_cc_headers.cc_preparer_user_id%TYPE;
34 g_preparer_name VARCHAR2(255);
35 g_old_approver_name VARCHAR2(255);
36 g_approver_name VARCHAR2(255);
37 g_business_group_id NUMBER;
38 g_pos_structure_version_id NUMBER(15);
39 g_create_po_entries BOOLEAN;
40 g_restore_enc BOOLEAN; --If transfer from PN to CM failed, restoration of enc amounts required
41 g_action_notes VARCHAR2(2000); --Action notes for create_action_history
42 g_cc_action_type VARCHAR2(10); --Action type for create_action_history
43 g_profile_name VARCHAR2(255) := 'IGC_DEBUG_LOG_DIRECTORY';
44 g_debug_init VARCHAR2(1);
45 g_process_name VARCHAR2(255) :='IGC_APPROVAL_WORKFLOW_MAIN';
46 g_wf_name VARCHAR2(255) :='IGCAPRWF';
47 g_cc_number igc_cc_headers.cc_num%TYPE;
48 -- CBC CC Bug 2111529 07-Feb-2001 S Brewer Start(1)
49 g_use_approval_hier VARCHAR2(1);
50 -- CBC CC Bug 2111529 07-Feb-2001 S Brewer End(1)
51
52 -- Bug 3199488
53 g_debug_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
54 g_state_level NUMBER := FND_LOG.LEVEL_STATEMENT;
55 g_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
56 g_event_level NUMBER := FND_LOG.LEVEL_EVENT;
57 g_excep_level NUMBER := FND_LOG.LEVEL_EXCEPTION;
58 g_error_level NUMBER := FND_LOG.LEVEL_ERROR;
59 g_unexp_level NUMBER := FND_LOG.LEVEL_UNEXPECTED;
60 g_path VARCHAR2(255) := 'IGC.PLSQL.IGCVAWFB.IGC_CC_APPROVAL_WF_PKG.';
61 l_full_path VARCHAR2(255);
62
63 -- Bug 3199488
64
65
66
67 PROCEDURE Generate_Message;
68
69
70 PROCEDURE Put_Debug_Msg (
71 p_path IN VARCHAR2,
72 p_debug_msg IN VARCHAR2
73 );
74
75 PROCEDURE message_token(
76 tokname IN VARCHAR2,
77 tokval IN VARCHAR2
78 );
79
80 PROCEDURE add_message(
81 appname IN VARCHAR2,
82 msgname IN VARCHAR2
83 );
84
85
86 /* This procedure determines the new statuses, states
87 and generate the list of neccessary action */
88 PROCEDURE Generate_CC_Action(
89 p_action VARCHAR2,
90 x_return_status OUT NOCOPY VARCHAR2);
91
92 /* Checks the person authority */
93 PROCEDURE Check_Authority(
94 p_result OUT NOCOPY VARCHAR2,
95 x_return_status OUT NOCOPY VARCHAR2 );
96
97 /* Reads values of the WF parameters into global variables */
98 PROCEDURE Get_Parameters(
99 x_return_status OUT NOCOPY VARCHAR2);
100
101 /* Initialize Business group id */
102 PROCEDURE Get_Business_group(
103 x_return_status OUT NOCOPY VARCHAR2);
104
105 /* Read values from the original CC to global variables and init WF variables */
106 PROCEDURE Init_variables(
107 x_return_status OUT NOCOPY VARCHAR2);
108
109 /* Looks for the next available approver */
110 PROCEDURE Find_Next_Approver(
111 x_return_status OUT NOCOPY VARCHAR2);
112
113 /* Set meaning values for all parameters */
114 PROCEDURE Set_Parameters(
115 x_return_status OUT NOCOPY VARCHAR2);
116
117
118 /* Procedure Initializes g_bc_enabled parameter */
119 PROCEDURE Set_BC_Parameter(
120 x_return_status OUT NOCOPY VARCHAR2);
121
122 /* This procedure is call from the execute BC and reinit version number of the CC */
123
124 PROCEDURE Reinit_version(
125 x_return_status OUT NOCOPY VARCHAR2);
126
127 /* Procedure creates a recorn in the history table */
128 PROCEDURE Create_History_Record(
129 x_return_status OUT NOCOPY VARCHAR2);
130
131 /* Procedure calls PO generation for a CC */
132 PROCEDURE Generate_PO(
133 x_return_status OUT NOCOPY VARCHAR2);
134
135 /* Main procedure does all neccessary steps */
136 PROCEDURE Process_Request(
137 p_action IN VARCHAR2,
138 x_return_status OUT NOCOPY VARCHAR2);
139 /* Procedure Checks supplier */
140 PROCEDURE Check_Supplier(
141 x_return_status OUT NOCOPY VARCHAR2);
142
143
144 /* Procedure update CC with the new statuses */
145 PROCEDURE Update_CC(
146 x_return_status OUT NOCOPY VARCHAR2);
147
148 FUNCTION Check_Segment(
149 seg VARCHAR2,
150 seg_low VARCHAR2,
151 seg_high VARCHAR2)
152 RETURN BOOLEAN;
153
154 /* this procedure is used when we need to reject cancelled CC,
155 it returns approval status before cancellation*/
156 FUNCTION Get_Last_App_status
157 RETURN VARCHAR2;
158
159 /**************************************************************************/
160 /* This procedure is run when WF needs to get next approval person */
161 /**************************************************************************/
162
163 PROCEDURE Select_Approver
164 (
165 itemtype IN VARCHAR2,
166 itemkey IN VARCHAR2,
167 actid IN NUMBER,
168 funcmode IN VARCHAR2,
169 resultout OUT NOCOPY VARCHAR2
170 )
171 IS
172
173 l_api_name CONSTANT VARCHAR2(30) := 'Select_Approver' ;
174 l_return_status VARCHAR2(1);
175
176 l_full_path VARCHAR2(255) := g_path||'Select_Approver';
177 BEGIN
178
179 SAVEPOINT Select_Approver;
180 g_itemtype:=itemtype;
181 g_itemkey:=itemkey;
182 g_debug_init:=NULL;
183
184 IF g_debug_mode = 'Y'
185 THEN
186 Put_Debug_Msg( l_full_path,'**************************************************************************');
187 Put_Debug_Msg( l_full_path,'Procedure '||l_api_name||' called IN '||funcmode||' mode '||' Date '||to_char(sysdate,'DD-MON-YY MI:SS'));
188 Put_Debug_Msg( l_full_path,'**************************************************************************');
189 END IF;
190
191 IF ( funcmode = 'RUN' ) THEN
192
193 Get_Parameters(x_return_status =>l_return_status );
194
195 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
196 raise FND_API.G_EXC_ERROR;
197 END IF;
198
199 IF (g_wf_version = 1) THEN --First version - no approval hierarchy
200 g_approver_name := g_owner_name;
201 g_approver_id := g_owner_id;
202
203 wf_engine.SetItemAttrText(g_itemtype,g_itemkey,'APPROVER_NAME',g_approver_name);
204 wf_engine.SetItemAttrText(g_itemtype,g_itemkey,'APPROVER_ID',g_approver_id);
205
206 IF g_debug_mode = 'Y'
207 THEN
208 --Put_Debug_Msg( l_full_path,l_api_name||' successfully completed, WF version '||g_wf_version||' new approver name '||g_approver_name);
209 -- Bug 3199488
210 IF ( g_event_level >= g_debug_level ) THEN
211 FND_LOG.STRING (g_event_level,l_full_path,l_api_name||' successfully completed, WF version '|| g_wf_version||' new approver name '||g_approver_name);
212 END IF;
213 -- Bug 3199488
214 END IF;
215
216 resultout := 'COMPLETE:S' ;
217 return;
218 END IF;
219
220 --Version is not 1 - then find approver in the hierarchy.
221
222 IF g_approver_name IS NULL THEN
223 g_approver_name := g_owner_name;
224 g_approver_id := g_owner_id;
225 ELSE
226 g_old_approver_name:=g_approver_name;
227
228 Find_Next_Approver(x_return_status =>l_return_status );
229
230 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
231 raise FND_API.G_EXC_ERROR;
232 END IF;
233
234 END IF;
235
236 wf_engine.SetItemAttrText(g_itemtype,g_itemkey,'APPROVER_OLD_NAME',g_old_approver_name);
237 wf_engine.SetItemAttrText(g_itemtype,g_itemkey,'APPROVER_NAME',g_approver_name);
238 wf_engine.SetItemAttrText(g_itemtype,g_itemkey,'APPROVER_ID',g_approver_id);
239
240 IF g_debug_mode = 'Y'
241 THEN
242 --Put_Debug_Msg( l_full_path,l_api_name||' successfully completed, WF version '||g_wf_version||' new approver name '||g_approver_name);
243 -- Bug 3199488
244 IF ( g_event_level >= g_debug_level ) THEN
245 FND_LOG.STRING (g_event_level,l_full_path,l_api_name||' successfully completed, WF version '|| g_wf_version||' new approver name '||g_approver_name);
246 END IF;
247 -- Bug 3199488
248 END IF;
249
250 resultout := 'COMPLETE:S' ;
251 return;
252 END IF ;
253
254 IF ( funcmode = 'CANCEL' ) THEN
255 resultout := 'COMPLETE' ;
256 return;
257 END IF;
258
259 IF ( funcmode NOT IN ( 'RUN', 'CANCEL' ) ) THEN
260 resultout := '' ;
261 return;
262 END IF;
263
264 EXCEPTION
265 WHEN FND_API.G_EXC_ERROR THEN
266 --If execution error, rollback all database changes, generate message text
267 --and return failure status to the WF
268 ROLLBACK TO Select_Approver;
269 resultout := 'COMPLETE:E';
270 Generate_Message();
271 --Bug 3199488
272 IF ( g_excep_level >= g_debug_level ) THEN
273 FND_LOG.STRING (g_excep_level,l_full_path,'FND_API.G_EXC_ERROR Exception Raised');
274 END IF;
275 --Bug 3199488
276 return;
277
278 WHEN OTHERS THEN
279
280 IF g_debug_mode = 'Y'
281 THEN
282 Put_Debug_Msg( l_full_path,l_api_name||' raised unhandled exception');
283 END IF;
284
285 wf_core.context(G_PKG_NAME, l_api_name,
286 itemtype, itemkey, to_char(actid), funcmode);
287 -- Bug 3199488
288 IF ( g_unexp_level >= g_debug_level ) THEN
289 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
290 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
291 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
292 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
293 END IF;
294 -- Bug 3199488
295 RAISE ;
296
297 END Select_Approver ;
298
299
300 /**************************************************************************/
301 /* This procedure is run when WF needs to check authority of the approver */
302 /**************************************************************************/
303
304 PROCEDURE Check_Authority
305 (
306 itemtype IN VARCHAR2,
307 itemkey IN VARCHAR2,
308 actid IN NUMBER,
309 funcmode IN VARCHAR2,
310 resultout OUT NOCOPY VARCHAR2
311 )
312 IS
313
314 l_api_name CONSTANT VARCHAR2(30) := 'Check_Authority' ;
315 l_return_status VARCHAR2(1);
316 l_result VARCHAR2(1);
317 l_full_path VARCHAR2(255) := g_path||'Check_Authority';
318 BEGIN
319
320 SAVEPOINT Check_Authority;
321 g_itemtype:=itemtype;
322 g_itemkey:=itemkey;
323 g_debug_init:=NULL;
324
325 IF g_debug_mode = 'Y'
326 THEN
327 Put_Debug_Msg( l_full_path,'**************************************************************************');
328 Put_Debug_Msg( l_full_path,'Procedure '||l_api_name||' called IN '||funcmode||' mode'||' Date '||to_char(sysdate,'DD-MON-YY MI:SS'));
329 Put_Debug_Msg( l_full_path,'**************************************************************************');
330 END IF;
331
332 IF ( funcmode = 'RUN' ) THEN
333
334 Get_Parameters(x_return_status =>l_return_status );
335
336 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
337 raise FND_API.G_EXC_ERROR;
338 END IF;
339
340 --Check the supplier
341
342 Check_Supplier(x_return_status =>l_return_status );
343
344 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
345 raise FND_API.G_EXC_ERROR;
346 END IF;
347
348 IF (g_wf_version = 1) THEN --First version - no approval hierarchy
349
350 IF g_debug_mode = 'Y'
351 THEN
352 --Put_Debug_Msg( l_full_path,l_api_name||' successfully completed, WF version '||g_wf_version||' result Y');
353 -- Bug 3199488
354 IF ( g_event_level >= g_debug_level ) THEN
355 FND_LOG.STRING (g_event_level,l_full_path,l_api_name||' successfully completed, WF version '||g_wf_version||' result Y');
356 END IF;
357 -- Bug 3199488
358 END IF;
359
360 resultout := 'COMPLETE:Y' ;
361 return;
362 END IF;
363
364 Check_Authority(p_result=> l_result,
365 x_return_status =>l_return_status );
366
367 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
368 raise FND_API.G_EXC_ERROR;
369 END IF;
370
371 IF g_debug_mode = 'Y'
372 THEN
373 Put_Debug_Msg( l_full_path,l_api_name||' successfully completed, WF version '||g_wf_version||' result '||l_result);
374 -- Bug 3199488
375 IF ( g_event_level >= g_debug_level ) THEN
376 FND_LOG.STRING (g_event_level,l_full_path,l_api_name||' successfully completed, WF version '||g_wf_version||' result '||l_result);
377 END IF;
378 -- Bug 3199488
379 END IF;
380
381 resultout := 'COMPLETE:'||l_result ;
382 return;
383 END IF ;
384
385 IF ( funcmode = 'CANCEL' ) THEN
386 resultout := 'COMPLETE' ;
387 return;
388 END IF;
389
390 IF ( funcmode NOT IN ( 'RUN', 'CANCEL' ) ) THEN
391 resultout := '' ;
392 return;
393 END IF;
394
395 EXCEPTION
396 WHEN FND_API.G_EXC_ERROR THEN
397 --If execution error, rollback all database changes, generate message text
398 --and return failure status to the WF
399
400 ROLLBACK TO Check_Authority;
401 resultout := 'COMPLETE:E';
402 Generate_Message();
403 --Bug 3199488
404 IF ( g_excep_level >= g_debug_level ) THEN
405 FND_LOG.STRING (g_excep_level,l_full_path,'FND_API.G_EXC_ERROR Exception Raised');
406 END IF;
407 --Bug 3199488
408 return;
409
410 WHEN OTHERS THEN
411 Put_Debug_Msg( l_full_path,l_api_name||' raised unhandled exception');
412
413 wf_core.context(G_PKG_NAME, l_api_name,
414 itemtype, itemkey, to_char(actid), funcmode);
415 -- Bug 3199488
416 IF ( g_unexp_level >= g_debug_level ) THEN
417 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
418 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
419 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
420 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
421 END IF;
422 -- Bug 3199488
423 RAISE ;
424
425 END Check_Authority ;
426
427
428 /**************************************************************************/
429 /* Procedure returns if CC reqiures encumber */
430 /**************************************************************************/
431
432 PROCEDURE Funds_Required
433 (
434 itemtype IN VARCHAR2,
435 itemkey IN VARCHAR2,
436 actid IN NUMBER,
437 funcmode IN VARCHAR2,
438 resultout OUT NOCOPY VARCHAR2
439 )
440 IS
441
442 l_api_name CONSTANT VARCHAR2(30) := 'Funds_Required' ;
443 l_return_status VARCHAR2(1);
444 l_full_path VARCHAR2(255) := g_path||'Funds_Required';
445 BEGIN
446
447 SAVEPOINT Funds_Required;
448 g_itemtype:=itemtype;
449 g_itemkey:=itemkey;
450 g_debug_init:=NULL;
451
452 IF g_debug_mode = 'Y'
453 THEN
454 Put_Debug_Msg( l_full_path,'**************************************************************************');
455 Put_Debug_Msg( l_full_path,'Procedure '||l_api_name||' called IN '||funcmode||' mode'||' Date '||to_char(sysdate,'DD-MON-YY MI:SS'));
456 Put_Debug_Msg( l_full_path,'**************************************************************************');
457 END IF;
458
459 IF ( funcmode = 'RUN' ) THEN
460
461 g_bc_reqired:= wf_engine.GetItemAttrText (g_itemtype,g_itemkey,'BC_REQUIRED');
462
463 IF g_bc_reqired='N' THEN
464
465 IF g_debug_mode = 'Y'
466 THEN
467 --Put_Debug_Msg( l_full_path,l_api_name||' successfully completed, Return result: N');
468 -- Bug 3199488
469 IF ( g_event_level >= g_debug_level ) THEN
470 FND_LOG.STRING (g_event_level,l_full_path,l_api_name || ' successfully completed, Return result: N');
471 END IF;
472 -- Bug 3199488
473 END IF;
474
475 resultout := 'COMPLETE:N';
476 ELSE
477
478 IF g_debug_mode = 'Y'
479 THEN
480 --Put_Debug_Msg( l_full_path,l_api_name||' successfully completed, Return result: Y');
481 -- Bug 3199488
482 IF ( g_event_level >= g_debug_level ) THEN
483 FND_LOG.STRING (g_event_level,l_full_path,l_api_name || ' successfully completed, Return result: Y');
484 END IF;
485 -- Bug 3199488
486 END IF;
487
488 resultout := 'COMPLETE:Y' ;
489 END IF;
490 return;
491 END IF ;
492
493 IF ( funcmode = 'CANCEL' ) THEN
494 resultout := 'COMPLETE' ;
495 return;
496 END IF;
497
498 IF ( funcmode NOT IN ( 'RUN', 'CANCEL' ) ) THEN
499 resultout := '' ;
500 return;
501 END IF;
502
503 EXCEPTION
504
505 WHEN OTHERS THEN
506 Put_Debug_Msg( l_full_path,l_api_name||' raised unhandled exception');
507
508 wf_core.context(G_PKG_NAME, l_api_name,
509 itemtype, itemkey, to_char(actid), funcmode);
510 -- Bug 3199488
511 IF ( g_unexp_level >= g_debug_level ) THEN
512 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
513 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
514 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
515 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
516 END IF;
517 -- Bug 3199488
518 RAISE ;
519
520 END Funds_Required ;
521
522
523
524 /**************************************************************************/
525 /* Procedure for execution of Reject action */
526 /**************************************************************************/
527
528 PROCEDURE Reject_Contract
529 (
530 itemtype IN VARCHAR2,
531 itemkey IN VARCHAR2,
532 actid IN NUMBER,
533 funcmode IN VARCHAR2,
534 resultout OUT NOCOPY VARCHAR2
535 )
536 IS
537
538 l_api_name CONSTANT VARCHAR2(32766) := g_path||'Reject_Contract' ;
539 l_return_status VARCHAR2(1);
540 BEGIN
541
542 SAVEPOINT Reject_Contract;
543 g_itemtype:=itemtype;
544 g_itemkey:=itemkey;
545 g_debug_init:=NULL;
546
547 IF g_debug_mode = 'Y'
548 THEN
549 Put_Debug_Msg( l_full_path,'**************************************************************************');
550 Put_Debug_Msg( l_full_path,'Procedure '||l_api_name||' called IN '||funcmode||' mode'||' Date '||to_char(sysdate,'DD-MON-YY MI:SS'));
551 Put_Debug_Msg( l_full_path,'**************************************************************************');
552 END IF;
553
554 IF ( funcmode = 'RUN' ) THEN
555
556 Process_request(
557 p_action => 'R',
558 x_return_status => l_return_status );
559
560 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
561 raise FND_API.G_EXC_ERROR;
562 END IF;
563
564 IF g_debug_mode = 'Y'
565 THEN
566 Put_Debug_Msg( l_full_path,l_api_name||' successfully completed, Return result: S');
567 -- Bug 3199488
568 IF ( g_event_level >= g_debug_level ) THEN
569 FND_LOG.STRING (g_event_level,l_full_path,l_api_name||' successfully completed, Return result: S');
570 END IF;
571 -- Bug 3199488
572 END IF;
573
574 resultout := 'COMPLETE:S' ;
575 return;
576 END IF ;
577
578 IF ( funcmode = 'CANCEL' ) THEN
579 resultout := 'COMPLETE' ;
580 return;
581 END IF;
582
583 IF ( funcmode NOT IN ( 'RUN', 'CANCEL' ) ) THEN
584 resultout := '' ;
585 return;
586 END IF;
587
588 EXCEPTION
589 WHEN FND_API.G_EXC_ERROR THEN
590 --If execution error, rollback all database changes, generate message text
591 --and return failure status to the WF
592
593 ROLLBACK TO Reject_Contract;
594 resultout := 'COMPLETE:E';
595 Generate_Message();
596 --Bug 3199488
597 IF ( g_excep_level >= g_debug_level ) THEN
598 FND_LOG.STRING (g_excep_level,l_full_path,'FND_API.G_EXC_ERROR Exception Raised');
599 END IF;
600 -- Bug 3199488
601 return;
602
603 WHEN OTHERS THEN
604 Put_Debug_Msg( l_full_path,l_api_name||' raised unhandled exception');
605
606 wf_core.context(G_PKG_NAME, l_api_name,
607 itemtype, itemkey, to_char(actid), funcmode);
608 -- Bug 3199488
609 IF ( g_unexp_level >= g_debug_level ) THEN
610 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
611 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
612 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
613 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
614 END IF;
615 -- Bug 3199488
616 RAISE ;
617
618 END Reject_Contract ;
619
620
621
622 /**************************************************************************/
623 /* Procedure for execution of Approve action */
624 /**************************************************************************/
625
626 PROCEDURE Approve_Contract
627 (
628 itemtype IN VARCHAR2,
629 itemkey IN VARCHAR2,
630 actid IN NUMBER,
631 funcmode IN VARCHAR2,
632 resultout OUT NOCOPY VARCHAR2
633 )
634 IS
635
636 l_api_name CONSTANT VARCHAR2(30) := 'Approve_Contract' ;
637 l_return_status VARCHAR2(1);
638 l_full_path VARCHAR2(255) := g_path||'Approve_Contract';
639 BEGIN
640
641 SAVEPOINT Approve_Contract;
642 g_itemtype:=itemtype;
643 g_itemkey:=itemkey;
644 g_debug_init:=NULL;
645
646 IF g_debug_mode = 'Y'
647 THEN
648 Put_Debug_Msg( l_full_path,'**************************************************************************');
649 Put_Debug_Msg( l_full_path,'Procedure '||l_api_name||' called IN '||funcmode||' mode'||' Date '||to_char(sysdate,'DD-MON-YY MI:SS'));
650 Put_Debug_Msg( l_full_path,'**************************************************************************');
651 END IF;
652
653 IF ( funcmode = 'RUN' ) THEN
654 Process_request(
655 p_action => 'A',
656 x_return_status => l_return_status );
657
658 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
659 raise FND_API.G_EXC_ERROR;
660 END IF;
661
662 IF g_debug_mode = 'Y'
663 THEN
664 --Put_Debug_Msg( l_full_path,l_api_name||' successfully completed, Result: S');
665 -- Bug 3199488
666 IF ( g_event_level >= g_debug_level ) THEN
667 FND_LOG.STRING (g_event_level,l_full_path,l_api_name||' successfully completed, Result: S');
668 END IF;
669 -- Bug 3199488
670 END IF;
671
672 resultout := 'COMPLETE:S' ;
673 return;
674 END IF ;
675
676 IF ( funcmode = 'CANCEL' ) THEN
677 resultout := 'COMPLETE' ;
678 return;
679 END IF;
680
681 IF ( funcmode NOT IN ( 'RUN', 'CANCEL' ) ) THEN
682 resultout := '' ;
683 return;
684 END IF;
685
686 EXCEPTION
687 WHEN FND_API.G_EXC_ERROR THEN
688 --If execution error, rollback all database changes, generate message text
689 --and return failure status to the WF
690
691 ROLLBACK TO Approve_Contract;
692 resultout := 'COMPLETE:E';
693 Generate_Message();
694 -- Bug 3199488
695 IF ( g_excep_level >= g_debug_level ) THEN
696 FND_LOG.STRING (g_excep_level,l_full_path,'FND_API.G_EXC_ERROR Exception Raised');
697 END IF;
698 -- Bug 3199488
699 return;
700
701 WHEN OTHERS THEN
702 Put_Debug_Msg( l_full_path,l_api_name||' raised unhandled exception');
703
704 wf_core.context(G_PKG_NAME, l_api_name,
705 itemtype, itemkey, to_char(actid), funcmode);
706 -- Bug 3199488
707 IF ( g_unexp_level >= g_debug_level ) THEN
708 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
709 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
710 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
711 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
712 END IF;
713 -- Bug 3199488
714 RAISE ;
715
716 END Approve_Contract ;
717
718
719 /**************************************************************************/
720 /* Procedure for execution of Budgetary control */
721 /**************************************************************************/
722
723 PROCEDURE Execute_BC
724 (
725 itemtype IN VARCHAR2,
726 itemkey IN VARCHAR2,
727 actid IN NUMBER,
728 funcmode IN VARCHAR2,
729 resultout OUT NOCOPY VARCHAR2
730 )
731 IS
732
733 l_api_name CONSTANT VARCHAR2(30) := 'Execute_BC' ;
734 l_return_status VARCHAR2(1) ;
735 l_msg_count NUMBER ;
736 l_msg_data VARCHAR2(2000) ;
737 l_bc_status VARCHAR2(1) ;
738 l_full_path VARCHAR2(255) := g_path||'Execute_BC';
739 BEGIN
740
741 SAVEPOINT Execute_BC;
742 g_itemtype:=itemtype;
743 g_itemkey:=itemkey;
744 g_debug_init:=NULL;
745
746 IF g_debug_mode = 'Y'
747 THEN
748 Put_Debug_Msg( l_full_path,'**************************************************************************');
749 Put_Debug_Msg( l_full_path,'Procedure '||l_api_name||' called IN '||funcmode||' mode'||' Date '||to_char(sysdate,'DD-MON-YY MI:SS'));
750 Put_Debug_Msg( l_full_path,'**************************************************************************');
751 END IF;
752
753 IF ( funcmode = 'RUN' ) THEN
754
755 Get_Parameters(x_return_status =>l_return_status );
756
757 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
758 raise FND_API.G_EXC_ERROR;
759 END IF;
760
761 --Exec BC
762
763 IF g_debug_mode = 'Y'
764 THEN
765 Put_Debug_Msg( l_full_path,'Executing BC');
766 END IF;
767
768 IGC_CC_BUDGETARY_CTRL_PKG.Execute_Budgetary_Ctrl
769 ( p_api_version => 1.0,
770 x_return_status => l_return_status,
771 x_bc_status => l_bc_status,
772 x_msg_count => l_msg_count,
773 x_msg_data => l_msg_data,
774 p_cc_header_id => g_cc_header_id,
775 p_accounting_date => g_acct_date,
776 p_mode => 'R');
777
778
779 SAVEPOINT Execute_BC;
780
781 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
782 IF g_debug_mode = 'Y'
783 THEN
784 --Put_Debug_Msg( l_full_path,'IGC_CC_BUDGETARY_CTRL_PKG.Execute_Budgetary_Ctrl returned error ');
785 -- Bug 3199488
786 IF ( g_excep_level >= g_debug_level ) THEN
787 FND_LOG.STRING (g_excep_level,l_full_path,'IGC_CC_BUDGETARY_CTRL_PKG.Execute_Budgetary_Ctrl returned error ');
788 END IF;
789 -- Bug 3199488
790 END IF;
791 raise FND_API.G_EXC_ERROR;
792 END IF;
793
794 IF g_debug_mode = 'Y'
795 THEN
796 Put_Debug_Msg( l_full_path,'Successfully executed');
797 END IF;
798
799 IF l_bc_status = FND_API.G_TRUE THEN
800
801 --Setting bc executed status
802 Reinit_Version(x_return_status =>l_return_status );
803
804 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
805 raise FND_API.G_EXC_ERROR;
806 END IF;
807
808 g_bc_executed :='Y';
809
810 wf_engine.SetItemAttrText(g_itemtype,g_itemkey,'BC_EXECUTED',g_bc_executed);
811
812 IF g_debug_mode = 'Y'
813 THEN
814 --Put_Debug_Msg( l_full_path,l_api_name||' successfully completed, Return result: P');
815 -- Bug 3199488
816 IF ( g_excep_level >= g_debug_level ) THEN
817 FND_LOG.STRING (g_excep_level,l_full_path,l_api_name||' successfully completed, Return result: P');
818 END IF;
819 -- Bug 3199488
820 END IF;
821
822 resultout := 'COMPLETE:P' ;
823 ELSE
824
825 Generate_Message();
826
827 g_bc_failure_message:=g_error_text;
828
829 wf_engine.SetItemAttrText(g_itemtype,g_itemkey,'BC_FAILURE_MESSAGE',g_bc_failure_message);
830
831 IF g_debug_mode = 'Y'
832 THEN
833 Put_Debug_Msg( l_full_path,l_api_name||' successfully completed, Return result: F, BC failed ');
834 END IF;
835
836 resultout := 'COMPLETE:F' ;
837 return;
838 END IF;
839
840 END IF ;
841
842 IF ( funcmode = 'CANCEL' ) THEN
843 resultout := 'COMPLETE' ;
844 return;
845 END IF;
846
847 IF ( funcmode NOT IN ( 'RUN', 'CANCEL' ) ) THEN
848 resultout := '' ;
849 return;
850 END IF;
851
852 EXCEPTION
853 WHEN FND_API.G_EXC_ERROR THEN
854 --If execution error, rollback all database changes, generate message text
855 --and return failure status to the WF
856
857 ROLLBACK TO Execute_BC;
858
859 resultout := 'COMPLETE:E';
860 Generate_Message();
861 -- Bug 3199488
862 IF ( g_excep_level >= g_debug_level ) THEN
863 FND_LOG.STRING (g_excep_level,l_full_path,'FND_API.G_EXC_ERROR Exception Raised');
864 END IF;
865 -- Bug 3199488
866 return;
867
868 WHEN OTHERS THEN
869 Put_Debug_Msg( l_full_path,l_api_name||' raised unhandled exception');
870
871 wf_core.context(G_PKG_NAME, l_api_name,
872 itemtype, itemkey, to_char(actid), funcmode);
873 -- Bug 3199488
874 IF ( g_unexp_level >= g_debug_level ) THEN
875 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
876 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
877 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
878 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
879 END IF;
880 -- Bug 3199488
881 RAISE ;
882
883 END Execute_BC ;
884
885
886 /**************************************************************************/
887 /* Procedure for execution of Error rollback action */
888 /**************************************************************************/
889
890 PROCEDURE Failed_Process
891 (
892 itemtype IN VARCHAR2,
893 itemkey IN VARCHAR2,
894 actid IN NUMBER,
895 funcmode IN VARCHAR2,
896 resultout OUT NOCOPY VARCHAR2
897 )
898 IS
899
900 l_api_name CONSTANT VARCHAR2(30) := 'Failed_Process' ;
901 l_return_status VARCHAR2(1);
902 l_full_path VARCHAR2(255) := g_path||'Failed_Process';
903 BEGIN
904
905 SAVEPOINT Failed_Process;
906 g_itemtype:=itemtype;
907 g_itemkey:=itemkey;
908 g_debug_init:=NULL;
909
910 IF g_debug_mode = 'Y'
911 THEN
912 Put_Debug_Msg( l_full_path,'**************************************************************************');
913 Put_Debug_Msg( l_full_path,'Procedure '||l_api_name||' called IN '||funcmode||' mode'||' Date '||to_char(sysdate,'DD-MON-YY MI:SS'));
914 Put_Debug_Msg( l_full_path,'**************************************************************************');
915 END IF;
916
917 IF ( funcmode = 'RUN' ) THEN
918
919 Process_request(
920 p_action => 'E',
921 x_return_status => l_return_status );
922
923 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
924 raise FND_API.G_EXC_ERROR;
925 END IF;
926
927 IF g_debug_mode = 'Y'
928 THEN
929 --Put_Debug_Msg( l_full_path,l_api_name||' successfully completed, Return result: None');
930 -- Bug 31994888
931 IF ( g_event_level >= g_debug_level ) THEN
932 FND_LOG.STRING (g_event_level,l_full_path,l_api_name||' successfully completed, Return result: None');
933 END IF;
934 -- Bug 3199488
935 END IF;
936
937 resultout := 'COMPLETE' ;
938 return;
939 END IF ;
940
941 IF ( funcmode = 'CANCEL' ) THEN
942 resultout := 'COMPLETE' ;
943 return;
944 END IF;
945
946 IF ( funcmode NOT IN ( 'RUN', 'CANCEL' ) ) THEN
947 resultout := '' ;
948 return;
949 END IF;
950
951 EXCEPTION
952 WHEN FND_API.G_EXC_ERROR THEN
953 --If execution error, rollback all database changes, generate message text
954 --and return failure status to the WF
955
956 ROLLBACK TO Failed_Process;
957 Generate_Message();
958 --Bug 3199488
959 IF ( g_excep_level >= g_debug_level ) THEN
960 FND_LOG.STRING (g_excep_level,l_full_path,'FND_API.G_EXC_ERROR Exception Raised');
961 END IF;
962 -- Bug 3199488
963 RAISE;
964
965 WHEN OTHERS THEN
966 Put_Debug_Msg( l_full_path,l_api_name||' raised unhandled exception');
967
968 wf_core.context(G_PKG_NAME, l_api_name,
969 itemtype, itemkey, to_char(actid), funcmode);
970 -- Bug 3199488
971 IF ( g_unexp_level >= g_debug_level ) THEN
972 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
973 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
974 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
975 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
976 END IF;
977 -- Bug 3199488
978 RAISE ;
979
980 END Failed_Process ;
981
982
983
984 /**************************************************************************/
985 /* Procedure for execution of BC failed action */
986 /**************************************************************************/
987 PROCEDURE BC_Failed
988 (
989 itemtype IN VARCHAR2,
990 itemkey IN VARCHAR2,
991 actid IN NUMBER,
992 funcmode IN VARCHAR2,
993 resultout OUT NOCOPY VARCHAR2
994 )
995 IS
996
997 l_api_name CONSTANT VARCHAR2(30) := 'BC_Failed' ;
998 l_return_status VARCHAR2(1);
999 l_full_path VARCHAR2(255):= g_path||'BC_Failed';
1000 BEGIN
1001
1002 SAVEPOINT BC_Failed;
1003 g_itemtype:=itemtype;
1004 g_itemkey:=itemkey;
1005 g_debug_init:=NULL;
1006
1007 IF g_debug_mode = 'Y'
1008 THEN
1009 Put_Debug_Msg( l_full_path,'**************************************************************************');
1010 Put_Debug_Msg( l_full_path,'Procedure '||l_api_name||' called IN '||funcmode||' mode'||' Date '||to_char(sysdate,'DD-MON-YY MI:SS'));
1011 Put_Debug_Msg( l_full_path,'**************************************************************************');
1012 END IF;
1013
1014 IF ( funcmode = 'RUN' ) THEN
1015
1016 Process_request(
1017 p_action => 'F',
1018 x_return_status => l_return_status );
1019
1020 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1021 raise FND_API.G_EXC_ERROR;
1022 END IF;
1023
1024 IF g_debug_mode = 'Y'
1025 THEN
1026 Put_Debug_Msg( l_full_path,l_api_name||' successfully completed, Return result: S');
1027 -- Bug 31994888
1028 IF ( g_event_level >= g_debug_level ) THEN
1029 FND_LOG.STRING (g_event_level,l_full_path,l_api_name||' successfully completed, Return result: S');
1030 END IF;
1031 -- Bug 3199488
1032 END IF;
1033
1034 resultout := 'COMPLETE:S' ;
1035 return;
1036 END IF ;
1037
1038 IF ( funcmode = 'CANCEL' ) THEN
1039 resultout := 'COMPLETE' ;
1040 return;
1041 END IF;
1042
1043 IF ( funcmode NOT IN ( 'RUN', 'CANCEL' ) ) THEN
1044 resultout := '' ;
1045 return;
1046 END IF;
1047
1048 EXCEPTION
1049 WHEN FND_API.G_EXC_ERROR THEN
1050 --If execution error, rollback all database changes, generate message text
1051 --and return failure status to the WF
1052
1053 ROLLBACK TO BC_Failed;
1054 resultout := 'COMPLETE:E';
1055 Generate_Message();
1056 --Bug 3199488
1057 IF ( g_excep_level >= g_debug_level ) THEN
1058 FND_LOG.STRING (g_excep_level,l_full_path,'FND_API.G_EXC_ERROR Exception Raised');
1059 END IF;
1060 --Bug 3199488
1061 return;
1062
1063 WHEN OTHERS THEN
1064 Put_Debug_Msg( l_full_path,l_api_name||' raised unhandled exception');
1065
1066 wf_core.context(G_PKG_NAME, l_api_name,
1067 itemtype, itemkey, to_char(actid), funcmode);
1068 -- Bug 3199488
1069 IF ( g_unexp_level >= g_debug_level ) THEN
1070 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
1071 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
1072 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
1073 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
1074 END IF;
1075 -- Bug 3199488
1076 RAISE ;
1077
1078 END BC_Failed ;
1079
1080
1081
1082
1083 /**************************************************************************/
1084 /* Procedure WF submition */
1085 /**************************************************************************/
1086
1087 PROCEDURE Start_Process
1088 (
1089 p_api_version IN NUMBER ,
1090 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
1091 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
1092 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
1093 p_wf_version IN NUMBER := 2,
1094 x_return_status OUT NOCOPY VARCHAR2 ,
1095 x_msg_count OUT NOCOPY NUMBER ,
1096 x_msg_data OUT NOCOPY VARCHAR2 ,
1097 p_item_key IN VARCHAR2 ,
1098 p_cc_header_id IN NUMBER ,
1099 p_acct_date IN DATE ,
1100 p_note IN VARCHAR2 := '',
1101 p_debug_mode IN VARCHAR2 := FND_API.G_FALSE
1102 )
1103 IS
1104
1105 l_api_name CONSTANT VARCHAR2(30) := 'Start_Process' ;
1106 l_api_version CONSTANT NUMBER := 1.0 ;
1107 l_return_status VARCHAR2(1) ;
1108 l_msg_count NUMBER ;
1109 l_msg_data VARCHAR2(2000) ;
1110
1111 CURSOR c_update_cc IS
1112 SELECT cc_apprvl_status
1113 FROM igc_cc_headers
1114 WHERE cc_header_id = g_cc_header_id
1115 FOR UPDATE;
1116
1117 CURSOR c_wf_name IS
1118 SELECT wf_approval_itemtype,
1119 wf_approval_process
1120 FROM igc_cc_routing_ctrls
1121 WHERE (org_id,cc_type,cc_state)
1122 IN (
1123 SELECT org_id ,
1124 cc_type ,
1125 cc_state
1126 FROM igc_cc_headers
1127 WHERE cc_header_id = g_cc_header_id
1128 );
1129
1130 l_full_path VARCHAR2(255) := g_path||'Start_Prcocess';
1131 BEGIN
1132
1133 SAVEPOINT Start_Process;
1134 --
1135 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
1136 FND_MSG_PUB.initialize ;
1137 END IF;
1138 --
1139 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1140
1141
1142 g_cc_header_id := p_cc_header_id;
1143 g_itemkey := p_item_key;
1144 g_wf_version := p_wf_version;
1145 g_debug_init := 'Y';
1146 g_note := p_note;
1147 g_acct_date := p_acct_date;
1148
1149 OPEN c_wf_name;
1150 FETCH c_wf_name INTO g_wf_name,g_process_name;
1151
1152 IF c_wf_name%NOTFOUND OR g_wf_name IS NULL OR g_process_name IS NULL THEN
1153 g_wf_name:= 'IGCAPRWF';
1154 g_process_name:= 'IGC_APPROVAL_WORKFLOW_MAIN';
1155 END IF;
1156
1157 CLOSE c_wf_name;
1158
1159 g_itemtype := g_wf_name;
1160
1161
1162 /*IF p_debug_mode = FND_API.G_TRUE OR p_debug_mode = 'Y' OR (upper(fnd_profile.value('IGC_DEBUG_ENABLED')) ='Y') THEN
1163 IGC_MSGS_PKG.g_debug_mode := TRUE;
1164 l_debug_mode := 'Y';
1165 ELSE
1166 IGC_MSGS_PKG.g_debug_mode := FALSE;
1167 l_debug_mode := 'N';
1168 END IF;*/
1169
1170 IF g_debug_mode = 'Y'
1171 THEN
1172 Put_Debug_Msg( l_full_path,'**************************************************************************');
1173 Put_Debug_Msg( l_full_path,'WF process is run on '||to_char(sysdate,'DD-MON-YY MI:SS')
1174 ||' CC header: '||g_cc_header_id
1175 ||' WF version: '||p_wf_version);
1176 Put_Debug_Msg( l_full_path,'**************************************************************************');
1177
1178 -- Lock CC
1179 Put_Debug_Msg( l_full_path,'Locking CC ');
1180 END IF;
1181
1182 OPEN c_update_cc;
1183 FETCH c_update_cc INTO g_cc_state;
1184
1185 IF c_update_cc%NOTFOUND THEN
1186
1187 CLOSE c_update_cc;
1188 message_token ('CC_HEADER_ID', g_cc_header_id);
1189 add_message ('IGC', 'IGC_CC_NOT_FOUND');
1190 RAISE FND_API.G_EXC_ERROR;
1191
1192 END IF;
1193
1194 IF g_debug_mode = 'Y'
1195 THEN
1196 Put_Debug_Msg( l_full_path,'Success, create process');
1197 END IF;
1198
1199 wf_engine.CreateProcess ( ItemType => g_wf_name,
1200 ItemKey => p_item_key,
1201 Process => g_process_name );
1202
1203 WF_Engine.SetItemUserKey
1204 (
1205 ItemType => g_wf_name ,
1206 ItemKey => p_item_key ,
1207 UserKey => p_cc_header_id
1208 );
1209
1210
1211 --Read values from the original CC to global variables and init WF variables
1212 Init_variables(x_return_status =>l_return_status );
1213
1214 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1215 raise FND_API.G_EXC_ERROR;
1216 END IF;
1217
1218 --Set meaning values for all parameters
1219
1220 Set_Parameters(x_return_status =>l_return_status );
1221
1222 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1223 raise FND_API.G_EXC_ERROR;
1224 END IF;
1225
1226 --Initialize g_bc_enabled parameter
1227
1228 Set_BC_Parameter(x_return_status =>l_return_status );
1229
1230 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1231 raise FND_API.G_EXC_ERROR;
1232 END IF;
1233
1234 wf_engine.SetItemAttrText (g_itemtype,g_itemkey,'BC_REQUIRED',g_bc_reqired);
1235
1236 -- Check if the requested CC state and status are valid and can be handler by the process
1237
1238 Generate_CC_Action(
1239 p_action => 'A',
1240 x_return_status => l_return_status );
1241
1242 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1243 raise FND_API.G_EXC_ERROR;
1244 END IF;
1245
1246 --Insert history record
1247
1248 g_cc_new_state := g_cc_state;
1249 g_cc_new_appr_status := g_cc_appr_status;
1250 g_cc_action_type := 'SA';
1251 g_action_notes := p_note;
1252
1253 Create_History_Record(
1254 x_return_status => l_return_status );
1255
1256 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1257 raise FND_API.G_EXC_ERROR;
1258 END IF;
1259
1260 g_cc_new_state := NULL;
1261 g_cc_new_appr_status := NULL;
1262
1263
1264 IF g_debug_mode = 'Y'
1265 THEN
1266 Put_Debug_Msg( l_full_path,'Update CC header to IP');
1267 END IF;
1268
1269 UPDATE igc_cc_headers
1270 SET cc_apprvl_status = 'IP',
1271 last_update_date = sysdate,
1272 last_updated_by = fnd_global.user_id,
1273 last_update_login = fnd_global.login_id
1274 WHERE CURRENT OF c_update_cc;
1275
1276 CLOSE c_update_cc;
1277
1278 -- Start the process
1279
1280 IF IGC_MSGS_PKG.g_debug_mode = TRUE THEN
1281 wf_engine.SetItemAttrText(g_itemtype,g_itemkey,'DEBUG_MODE', FND_API.G_TRUE);
1282 ELSE
1283 wf_engine.SetItemAttrText(g_itemtype,g_itemkey,'DEBUG_MODE', FND_API.G_FALSE);
1284 END IF;
1285
1286 IF g_debug_mode = 'Y'
1287 THEN
1288 Put_Debug_Msg( l_full_path,'Starting process');
1289 END IF;
1290
1291 wf_engine.StartProcess ( ItemType => g_wf_name,
1292 ItemKey => p_item_key );
1293
1294
1295 COMMIT WORK;
1296
1297 IF g_debug_mode = 'Y'
1298 THEN
1299 Put_Debug_Msg( l_full_path,l_api_name||' Successfully completed');
1300 END IF;
1301
1302 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1303 p_data => x_msg_data );
1304
1305 EXCEPTION
1306 WHEN FND_API.G_EXC_ERROR THEN
1307 Rollback to Start_Process;
1308 x_return_status := FND_API.G_RET_STS_ERROR;
1309
1310 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1311 p_data => x_msg_data );
1312 --Bug 3199488
1313 IF ( g_excep_level >= g_debug_level ) THEN
1314 FND_LOG.STRING (g_excep_level,l_full_path,'FND_API.G_EXC_ERROR Exception Raised');
1315 END IF;
1316 --Bug 3199488
1317
1318 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1319 Rollback to Start_Process;
1320 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1321
1322 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1323 p_data => x_msg_data );
1324 --Bug 3199488
1325 IF ( g_excep_level >= g_debug_level ) THEN
1326 FND_LOG.STRING (g_excep_level,l_full_path,'FND_API.G_EXC_UNEXPECTED_ERROR Exception Raised');
1327 END IF;
1328 --Bug 3199488
1329
1330 WHEN OTHERS THEN
1331
1332 Put_Debug_Msg( l_full_path,l_api_name||' raised unhandled exception');
1333
1334 Rollback to Start_Process;
1335 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1336
1337 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
1338 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
1339 END IF;
1340
1341 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1342 p_data => x_msg_data );
1343 -- Bug 3199488
1344 IF ( g_unexp_level >= g_debug_level ) THEN
1345 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
1346 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
1347 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
1348 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
1349 END IF;
1350 -- Bug 3199488
1351
1352 END Start_Process ;
1353
1354 /* This procedure determines the new statuses, states
1355 and generate the list of neccessary action */
1356
1357 PROCEDURE Generate_CC_Action(
1358 p_action VARCHAR2,
1359 x_return_status OUT NOCOPY VARCHAR2)
1360 IS
1361 l_api_name CONSTANT VARCHAR2(30) := 'Generate_CC_Action' ;
1362
1363 l_total_state VARCHAR2(10);
1364 l_new_state VARCHAR2(10);
1365 l_old_appr_status VARCHAR2(2);
1366
1367 l_full_path VARCHAR2(255) := g_path||'Generate_CC_action';
1368 BEGIN
1369
1370 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1371
1372 IF g_debug_mode = 'Y'
1373 THEN
1374 Put_Debug_Msg( l_full_path,l_api_name||' started');
1375 END IF;
1376
1377 --Initilize total state
1378 l_total_state:=g_cc_state||g_cc_appr_status||g_cc_enc_status;
1379
1380 g_restore_enc :=FALSE;
1381
1382 l_old_appr_status :='IN';
1383
1384 IF l_total_state='PRINN' THEN
1385 SELECT decode(p_action,
1386 'A','PRAP'||decode(g_bc_reqired,'Y','P','N'),
1387 'R','PRRJN',
1388 'E','PRINN',
1389 'F','PRINN'
1390 )
1391 INTO l_new_state
1392 FROM dual;
1393 ELSIF l_total_state='PRINP' THEN
1394 SELECT decode(p_action,
1395 'A','PRAPP',
1396 'R','PRRJP',
1397 'E','PRINP'
1398 ) -- No Failed action
1399 INTO l_new_state
1400 FROM dual;
1401 ELSIF l_total_state='PRRRN' THEN
1402 SELECT decode(p_action,
1403 'A','PRAP'||decode(g_bc_reqired,'Y','P','N'),
1404 'R','PRRJN',
1405 'E','PRRRN',
1406 'F','PRRRN'
1407 )
1408 INTO l_new_state
1409 FROM dual;
1410 ELSIF l_total_state='PRRRP' THEN
1411 SELECT decode(p_action,
1412 'A','PRAPP',
1413 'R','PRRJP',
1414 'E','PRRRP'
1415 ) -- No Failed action
1416 INTO l_new_state
1417 FROM dual;
1418 ELSIF l_total_state='CMINN' THEN --5
1419 SELECT decode(p_action,
1420 'A','CMAP'||decode(g_bc_reqired,'Y','C','N'),
1421 'R','PRRRN',
1422 'E','CMINN',
1423 'F','CMRJN'
1424 )
1425 INTO l_new_state
1426 FROM dual;
1427 ELSIF l_total_state='CMINT' THEN --6
1428 SELECT decode(p_action,
1429 'A','CMAPC',
1430 'R','PRRRP',
1431 'E','CMINT',
1432 'F','CMRJT'
1433 )
1434 INTO l_new_state
1435 FROM dual;
1436
1437 IF p_action = 'R' THEN --Rollback to provisional enc needed
1438 g_restore_enc :=TRUE;
1439 END IF;
1440
1441 ELSIF l_total_state='CMINC' THEN --7
1442 SELECT decode(p_action,
1443 'A','CMAPC',
1444 'R','CMRJC',
1445 'E','CMINC'
1446 )
1447 INTO l_new_state
1448 FROM dual;
1449 ELSIF l_total_state='CMRRN' THEN --8
1450 SELECT decode(p_action,
1451 'A','CMAP'||decode(g_bc_reqired,'Y','C','N'),
1452 'R','CMRJN',
1453 'E','CMRRN',
1454 'F','CMRRN'
1455 )
1456 INTO l_new_state
1457 FROM dual;
1458 ELSIF l_total_state='CMRRC' THEN --9
1459 SELECT decode(p_action,
1460 'A','CMAPC',
1461 'R','CMRJC',
1462 'E','CMRRC'
1463 )
1464 INTO l_new_state
1465 FROM dual;
1466 ELSIF l_total_state='CLINN' THEN --10
1467 -- l_old_appr_status:= Get old status from history
1468 l_old_appr_status:= Get_Last_App_status;
1469 SELECT decode(p_action,
1470 'A','CLAPN',
1471 'R','PR'||l_old_appr_status||'N',
1472 'E','CLINN'
1473 )
1474 INTO l_new_state
1475 FROM dual;
1476 ELSIF l_total_state='CLINP' THEN --11
1477 -- Get old status from history
1478 l_old_appr_status:= Get_Last_App_status;
1479 SELECT decode(p_action,
1480 'A','CLAPN',
1481 'R','PR'||l_old_appr_status||'P',
1482 'E','CLINP'
1483 )
1484 INTO l_new_state
1485 FROM dual;
1486
1487 ELSIF l_total_state='CTINC' THEN --12
1488 SELECT decode(p_action,
1489 'A','CTAPN',
1490 'R','CMAPC',
1491 'E','CTINC',
1492 'F','CTINC'
1493 )
1494 INTO l_new_state
1495 FROM dual;
1496 ELSIF l_total_state='CTINNC' THEN --13
1497 SELECT decode(p_action,
1498 'A','CTAPN',
1499 'R','CMAPN',
1500 'E','CTINN'
1501 )
1502 INTO l_new_state
1503 FROM dual;
1504 ELSIF g_cc_appr_status ='RJ' AND g_cc_state IN ('PR','CM') THEN
1505 SELECT decode(p_action,
1506 'A',g_cc_state||'AP'||decode(g_bc_reqired,'N',g_cc_enc_status,decode(g_cc_state,'PR','P','C')),
1507 'R',g_cc_state||'RJ'||g_cc_enc_status,
1508 'E',g_cc_state||'RJ'||g_cc_enc_status,
1509 'F',g_cc_state||'RJ'||'N'
1510 )
1511 INTO l_new_state
1512 FROM dual;
1513
1514 ELSE
1515 --Generate error message, unhandled situation
1516
1517 message_token ('CC_NUM', g_cc_number);
1518 message_token ('CC_STATE', g_cc_state);
1519 message_token ('CC_ENC_STATUS', g_cc_enc_status);
1520 message_token ('CC_APR_STATUS', g_cc_appr_status);
1521 add_message ('IGC', 'IGC_CC_STATE_ERROR');
1522 RAISE FND_API.G_EXC_ERROR;
1523
1524 END IF;
1525
1526
1527 g_create_po_entries:=FALSE;
1528
1529 IF p_action = 'A' AND g_cc_type IN ('S', 'R') AND g_cc_state = 'CM' THEN
1530 g_create_po_entries :=TRUE; --Po entries create
1531 IF g_debug_mode = 'Y'
1532 THEN
1533 Put_Debug_Msg( l_full_path,'PO generation required');
1534 END IF;
1535 END IF;
1536
1537 --obtaining the note text
1538
1539 SELECT decode(p_action,
1540 'F',g_bc_failure_message,
1541 'E',substr(g_error_text,1,240),
1542 'A',substr(g_reject_note,1,240),
1543 'R',substr(g_reject_note,1,240))
1544 INTO g_action_notes
1545 FROM dual;
1546
1547 --Getting the new statuses and states
1548 g_cc_new_state :=substr(l_new_state,1,2);
1549 g_cc_new_appr_status:=substr(l_new_state,3,2);
1550 g_cc_new_enc_status :=substr(l_new_state,5,1);
1551
1552 g_cc_action_type :='RJ';
1553
1554 IF p_action = 'A' THEN
1555 g_cc_action_type :='AP';
1556 END IF;
1557
1558 IF g_debug_mode = 'Y'
1559 THEN
1560 Put_Debug_Msg( l_full_path,'New parameters: '||
1561 ' state: '||g_cc_new_state||
1562 ' appr status: '||g_cc_new_appr_status||
1563 ' enc status: '||g_cc_new_enc_status||
1564 ' act type: '||g_cc_action_type
1565 );
1566
1567 Put_Debug_Msg( l_full_path,l_api_name||' Successfully completed');
1568 END IF;
1569
1570 EXCEPTION
1571 WHEN FND_API.G_EXC_ERROR THEN
1572 x_return_status := FND_API.G_RET_STS_ERROR;
1573 --Bug 3199488
1574 IF ( g_excep_level >= g_debug_level ) THEN
1575 FND_LOG.STRING (g_excep_level,l_full_path,'FND_API.G_EXC_ERROR Exception Raised');
1576 END IF;
1577 --Bug 3199488
1578 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1579 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1580 --Bug 3199488
1581 IF ( g_excep_level >= g_debug_level ) THEN
1582 FND_LOG.STRING (g_excep_level,l_full_path,'FND_API.G_EXC_UNEXPECTED_ERROR Exception Raised');
1583 END IF;
1584 --Bug 3199488
1585
1586 WHEN OTHERS THEN
1587 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1588
1589 Put_Debug_Msg( l_full_path,l_api_name||' raised unhandled exception');
1590
1591 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
1592 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
1593 END IF;
1594 -- Bug 3199488
1595 IF ( g_unexp_level >= g_debug_level ) THEN
1596 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
1597 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
1598 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
1599 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
1600 END IF;
1601 -- Bug 3199488
1602
1603 END Generate_CC_Action;
1604
1605
1606 /* Reads values of the WF parameters into global vatiables */
1607
1608 PROCEDURE Get_Parameters(
1609 x_return_status OUT NOCOPY VARCHAR2)
1610 IS
1611
1612 l_api_name CONSTANT VARCHAR2(30) := 'Get_Parameters' ;
1613 l_full_path VARCHAR2(255) := g_path||'Get_Parameters';
1614
1615 BEGIN
1616
1617 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1618 IF g_debug_mode = 'Y'
1619 THEN
1620 Put_Debug_Msg( l_full_path,l_api_name||' started');
1621 END IF;
1622
1623 g_old_approver_name := wf_engine.GetItemAttrText(g_itemtype,g_itemkey,'APPROVER_OLD_NAME');
1624 g_approver_name := wf_engine.GetItemAttrText(g_itemtype,g_itemkey,'APPROVER_NAME');
1625 g_preparer_name := wf_engine.GetItemAttrText(g_itemtype,g_itemkey,'PREPARER_NAME');
1626 g_approver_id := wf_engine.GetItemAttrNumber(g_itemtype,g_itemkey,'APPROVER_ID');
1627 g_owner_name := wf_engine.GetItemAttrText(g_itemtype,g_itemkey,'OWNER_NAME');
1628 g_wf_version := wf_engine.GetItemAttrNumber(g_itemtype,g_itemkey,'WF_VERSION');
1629 g_cc_header_id := wf_engine.GetItemAttrNumber(g_itemtype,g_itemkey,'CC_HEADER_ID');
1630 g_cc_state := wf_engine.GetItemAttrText (g_itemtype,g_itemkey,'CC_STATE');
1631 g_cc_enc_status := wf_engine.GetItemAttrText (g_itemtype,g_itemkey,'CC_ENCUMBRANCE_STATUS');
1632 g_cc_appr_status := wf_engine.GetItemAttrText (g_itemtype,g_itemkey,'CC_APPRVL_STATUS');
1633 g_bc_reqired := wf_engine.GetItemAttrText (g_itemtype,g_itemkey,'BC_REQUIRED');
1634 g_cc_type := wf_engine.GetItemAttrText (g_itemtype,g_itemkey,'CC_TYPE');
1635 g_note := wf_engine.GetItemAttrText (g_itemtype,g_itemkey,'NOTE');
1636 g_reject_note := wf_engine.GetItemAttrText (g_itemtype,g_itemkey,'REJECT_NOTE');
1637 g_bc_failure_message := wf_engine.GetItemAttrText (g_itemtype,g_itemkey,'BC_FAILURE_MESSAGE');
1638 g_cc_version_number := wf_engine.GetItemAttrText (g_itemtype,g_itemkey,'CC_VERSION_NUM');
1639 g_bc_executed := wf_engine.GetItemAttrText (g_itemtype,g_itemkey,'BC_EXECUTED');
1640 g_cc_ctrl_status := wf_engine.GetItemAttrText (g_itemtype,g_itemkey,'CC_CTRL_STATUS');
1641 g_org_id := wf_engine.GetItemAttrNumber(g_itemtype,g_itemkey,'ORG_ID');
1642 g_sob_id := wf_engine.GetItemAttrNumber(g_itemtype,g_itemkey,'SOB_ID');
1643 g_cc_number := wf_engine.GetItemAttrText (g_itemtype,g_itemkey,'CC_NUMBER');
1644 g_acct_date := wf_engine.GetItemAttrDate (g_itemtype,g_itemkey,'CC_ACCT_DATE');
1645
1646 IF g_org_id IS NOT NULL THEN
1647
1648 /*Replaced below line with call to mo_global. for MOAC uptake for bug#6341012 */
1649 --fnd_client_info.set_org_context(to_char(g_org_id));
1650 mo_global.set_policy_context('S',g_org_id);
1651
1652 END IF;
1653
1654 IF g_debug_mode = 'Y'
1655 THEN
1656 Put_Debug_Msg( l_full_path,'Parameters values:'||
1657 g_old_approver_name || ' - APPROVER_OLD_NAME ' ||
1658 g_approver_name || ' - APPROVER_NAME ' ||
1659 g_preparer_name || ' - PREPARER_NAME ' ||
1660 g_owner_name || ' - OWNER_NAME ' ||
1661 g_wf_version || ' - WF_VERSION ' ||
1662 g_cc_header_id || ' - CC_HEADER_ID ' ||
1663 g_cc_state || ' - CC_STATE ' ||
1664 g_cc_enc_status || ' - CC_ENCUMBRANCE_STATUS' ||
1665 g_cc_appr_status || ' - CC_APPRVL_STATUS ' ||
1666 g_bc_reqired || ' - BC_REQUIRED ' ||
1667 g_cc_type || ' - CC_TYPE ' ||
1668 g_approver_id || ' - APPROVER_ID ' ||
1669 g_note || ' - NOTE ' ||
1670 g_reject_note || ' - REJECT_NOTE ' ||
1671 g_bc_failure_message || ' - BC_FAILURE_MESSAGE ' ||
1672 g_cc_version_number || ' - CC_VERSION_NUM ' ||
1673 g_bc_executed || ' - BC_EXECUTED ' ||
1674 g_cc_ctrl_status || ' - CC_CTRL_STATUS ' ||
1675 g_org_id || ' - ORG_ID ' ||
1676 g_sob_id || ' - SOB_ID ' );
1677
1678 Put_Debug_Msg( l_full_path,l_api_name||' Successfully completed');
1679 END IF;
1680
1681 EXCEPTION
1682 WHEN FND_API.G_EXC_ERROR THEN
1683 x_return_status := FND_API.G_RET_STS_ERROR;
1684 --Bug 3199488
1685 IF ( g_excep_level >= g_debug_level ) THEN
1686 FND_LOG.STRING (g_excep_level,l_full_path,'FND_API.G_EXC_ERROR Exception Raised');
1687 END IF;
1688 --Bug 3199488
1689
1690 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1691 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1692 --Bug 3199488
1693 IF ( g_excep_level >= g_debug_level ) THEN
1694 FND_LOG.STRING (g_excep_level,l_full_path,'FND_API.G_EXC_UNEXPECTED_ERROR Exception Raised');
1695 END IF;
1696 --Bug 3199488
1697
1698 WHEN OTHERS THEN
1699 Put_Debug_Msg( l_full_path,l_api_name||' raised unhandled exception');
1700 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1701
1702 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
1703 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
1704 END IF;
1705 -- Bug 3199488
1706 IF ( g_unexp_level >= g_debug_level ) THEN
1707 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
1708 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
1709 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
1710 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
1711 END IF;
1712 -- Bug 3199488
1713
1714 END Get_Parameters;
1715
1716 /****************************************************************************/
1717 PROCEDURE Set_Parameters(
1718 x_return_status OUT NOCOPY VARCHAR2)
1719 IS
1720
1721 l_api_name CONSTANT VARCHAR2(30) := 'Set_Parameters' ;
1722
1723 CURSOR c_meaning(l_type VARCHAR2,l_code VARCHAR2) IS
1724 SELECT meaning
1725 FROM fnd_lookups
1726 WHERE lookup_code = l_code
1727 AND lookup_type = l_type;
1728 l_value VARCHAR2(255);
1729
1730 l_full_path VARCHAR2(255) := g_path||'Set_parameters';
1731 BEGIN
1732
1733 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1734 IF g_debug_mode = 'Y'
1735 THEN
1736 Put_Debug_Msg( l_full_path,l_api_name||' started');
1737 END IF;
1738
1739 l_value:=null;
1740 OPEN c_meaning('IGC_CC_STATE',g_cc_state);
1741 FETCH c_meaning INTO l_value;
1742 CLOSE c_meaning;
1743 wf_engine.SetItemAttrText (g_itemtype,g_itemkey,'CC_STATE_MEANING',l_value);
1744
1745 l_value:=null;
1746 OPEN c_meaning('IGC_CC_STATE',g_cc_new_state);
1747 FETCH c_meaning INTO l_value;
1748 CLOSE c_meaning;
1749 wf_engine.SetItemAttrText (g_itemtype,g_itemkey,'CC_STATE_NEW_MEANING',l_value);
1750
1751 l_value:=null;
1752 OPEN c_meaning('IGC_CC_CONTROL_STATUS',g_cc_ctrl_status);
1753 FETCH c_meaning INTO l_value;
1754 CLOSE c_meaning;
1755 wf_engine.SetItemAttrText (g_itemtype,g_itemkey,'CC_CTRL_MEANING',l_value);
1756
1757 l_value:=null;
1758 OPEN c_meaning('IGC_CC_TYPE',g_cc_type);
1759 FETCH c_meaning INTO l_value;
1760 CLOSE c_meaning;
1761 wf_engine.SetItemAttrText (g_itemtype,g_itemkey,'CC_TYPE_MEANING',l_value);
1762
1763 l_value:=null;
1764 OPEN c_meaning('IGC_CC_APPROVAL_STATUS',g_cc_appr_status);
1765 FETCH c_meaning INTO l_value;
1766 CLOSE c_meaning;
1767 wf_engine.SetItemAttrText (g_itemtype,g_itemkey,'CC_APPRVL_MEANING',l_value);
1768
1769 l_value:=null;
1770 OPEN c_meaning('IGC_CC_APPROVAL_STATUS',g_cc_new_appr_status);
1771 FETCH c_meaning INTO l_value;
1772 CLOSE c_meaning;
1773 wf_engine.SetItemAttrText (g_itemtype,g_itemkey,'CC_APPRVL_NEW_MEANING',l_value);
1774
1775
1776 l_value:=null;
1777 OPEN c_meaning('IGC_CC_ENCUMBRANCE_STATUS',g_cc_enc_status);
1778 FETCH c_meaning INTO l_value;
1779 CLOSE c_meaning;
1780 wf_engine.SetItemAttrText (g_itemtype,g_itemkey,'CC_ENCU_MEANING',l_value);
1781
1782 l_value:=null;
1783 OPEN c_meaning('IGC_CC_ENCUMBRANCE_STATUS',g_cc_new_enc_status);
1784 FETCH c_meaning INTO l_value;
1785 CLOSE c_meaning;
1786 wf_engine.SetItemAttrText (g_itemtype,g_itemkey,'CC_ENCU_NEW_MEANING',l_value);
1787
1788 wf_engine.SetItemAttrText (g_itemtype,g_itemkey,'CC_NEW_STATE',g_cc_new_state);
1789 wf_engine.SetItemAttrText (g_itemtype,g_itemkey,'CC_ENCUMBRANCE_NEW_STATUS',g_cc_new_enc_status);
1790 wf_engine.SetItemAttrText (g_itemtype,g_itemkey,'CC_APPRVL_NEW_STATUS',g_cc_new_appr_status);
1791
1792 IF g_debug_mode = 'Y'
1793 THEN
1794 Put_Debug_Msg( l_full_path,l_api_name||' Successfully completed');
1795 END IF;
1796
1797 EXCEPTION
1798 WHEN FND_API.G_EXC_ERROR THEN
1799 x_return_status := FND_API.G_RET_STS_ERROR;
1800 --Bug 3199488
1801 IF ( g_excep_level >= g_debug_level ) THEN
1802 FND_LOG.STRING (g_excep_level,l_full_path,'FND_API.G_EXC_ERROR Exception Raised');
1803 END IF;
1804 --Bug 3199488
1805
1806 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1807 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1808 --Bug 3199488
1809 IF ( g_excep_level >= g_debug_level ) THEN
1810 FND_LOG.STRING (g_excep_level,l_full_path,'FND_API.G_EXC_UNEXPECTED_ERROR Exception Raised');
1811 END IF;
1812 --Bug 3199488
1813
1814 WHEN OTHERS THEN
1815 Put_Debug_Msg( l_full_path,l_api_name||' raised unhandled exception');
1816
1817 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1818
1819 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
1820 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
1821 END IF;
1822 -- Bug 3199488
1823 IF ( g_unexp_level >= g_debug_level ) THEN
1824 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
1825 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
1826 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
1827 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
1828 END IF;
1829 -- Bug 3199488
1830
1831 END Set_Parameters;
1832
1833
1834 PROCEDURE Init_variables(
1835 x_return_status OUT NOCOPY VARCHAR2)
1836 IS
1837 l_api_name CONSTANT VARCHAR2(30) := 'Init_variables' ;
1838
1839 l_cc_desc igc_cc_headers.cc_desc%TYPE;
1840 l_cc_start_date igc_cc_headers.cc_start_date%TYPE;
1841 l_cc_end_date igc_cc_headers.cc_end_date%TYPE;
1842 l_note VARCHAR2(255);
1843 l_org_name hr_organization_units.name%TYPE;
1844 l_account_date igc_cc_headers.cc_acct_date%TYPE;
1845 l_user_display_name VARCHAR2(255);
1846 l_owner_display_name VARCHAR2(255);
1847 l_use_pos VARCHAR2(1);
1848
1849 CURSOR c_org_name IS
1850 SELECT name
1851 FROM hr_organization_units
1852 WHERE organization_id = g_org_id;
1853
1854 CURSOR c_cc_data IS
1855 SELECT org_id ,
1856 cc_type ,
1857 cc_num ,
1858 cc_version_num ,
1859 cc_state ,
1860 cc_ctrl_status ,
1861 cc_encmbrnc_status ,
1862 cc_apprvl_status ,
1863 set_of_books_id ,
1864 cc_acct_date ,
1865 cc_desc ,
1866 cc_start_date ,
1867 cc_end_date ,
1868 f1.employee_id user_id ,
1869 f2.employee_id owner_id
1870 FROM igc_cc_headers,
1871 fnd_user f1,
1872 fnd_user f2
1873 WHERE cc_header_id = g_cc_header_id
1874 AND f1.user_id=cc_owner_user_id
1875 AND f2.user_id=cc_preparer_user_id;
1876
1877 CURSOR c_fin_par IS
1878 SELECT use_positions_flag
1879 FROM financials_system_parameters;
1880
1881
1882 l_full_path VARCHAR2(500) := g_path || 'Init_variables';
1883 BEGIN
1884
1885 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1886 IF g_debug_mode = 'Y'
1887 THEN
1888 Put_Debug_Msg( l_full_path,l_api_name||' started');
1889 END IF;
1890
1891 g_bc_executed :='N';
1892
1893 OPEN c_cc_data;
1894 FETCH c_cc_data
1895 INTO g_org_id,
1896 g_cc_type,
1897 g_cc_number ,
1898 g_cc_version_number,
1899 g_cc_state,
1900 g_cc_ctrl_status,
1901 g_cc_enc_status,
1902 g_cc_appr_status,
1903 g_sob_id,
1904 l_account_date,
1905 l_cc_desc,
1906 l_cc_start_date,
1907 l_cc_end_date,
1908 g_owner_id,
1909 g_preparer_id ;
1910
1911 IF c_cc_data%NOTFOUND THEN
1912
1913 CLOSE c_cc_data;
1914 message_token ('CC_HEADER_ID', g_cc_header_id);
1915 add_message ('IGC', 'IGC_CC_NOT_FOUND');
1916 RAISE FND_API.G_EXC_ERROR;
1917
1918 END IF;
1919
1920 CLOSE c_cc_data;
1921
1922 OPEN c_org_name;
1923 FETCH c_org_name INTO l_org_name;
1924 CLOSE c_org_name;
1925
1926 WF_DIRECTORY.GetUserName('PER',
1927 g_preparer_id,
1928 g_preparer_name,
1929 l_user_display_name);
1930
1931 WF_DIRECTORY.GetUserName('PER',
1932 g_owner_id,
1933 g_owner_name,
1934 l_owner_display_name);
1935
1936 /*Replaced below line with call to mo_global. for MOAC uptake for bug#6341012 */
1937 --fnd_client_info.set_org_context(to_char(g_org_id));
1938 mo_global.set_policy_context('S',g_org_id);
1939 IF g_wf_version = 2 THEN
1940 IF g_debug_mode = 'Y'
1941 THEN
1942 Put_Debug_Msg( l_full_path,'Current WF version is: '||g_wf_version||' checking use position hierarchy flag');
1943 END IF;
1944 OPEN c_fin_par;
1945 FETCH c_fin_par INTO l_use_pos;
1946
1947 IF c_fin_par%NOTFOUND THEN
1948 --Error - can't find record for the current org
1949
1950 CLOSE c_fin_par;
1951 message_token ('ORG_ID', g_org_id);
1952 add_message ('IGC', 'IGC_ORG_NOT_FOUND');
1953 RAISE FND_API.G_EXC_ERROR;
1954
1955 END IF;
1956
1957 CLOSE c_fin_par;
1958
1959 -- CBC CC Bug 2111529 07-Feb-2001 S Brewer Start(2)
1960 -- Set the g_use_approval_hier parameter
1961 g_use_approval_hier := nvl(l_use_pos,'N');
1962 IF g_debug_mode = 'Y'
1963 THEN
1964 Put_Debug_Msg( l_full_path,'g_use_approval_hier = '||g_use_approval_hier);
1965 END IF;
1966
1967 -- want to check the g_use_approval_hier value instead of l_use_pos
1968
1969 IF (g_use_approval_hier = 'N') THEN
1970 --IF l_use_pos ='N' THEN
1971 --Put_Debug_Msg( l_full_path,'Flag is None, using WF version 1');
1972 --g_wf_version :=1;
1973 -- Will use the supervisor hierarchy instead
1974 IF g_debug_mode = 'Y'
1975 THEN
1976 Put_Debug_Msg( l_full_path,'Use approval hierarchy is N - using supervisor hierarchy');
1977 END IF;
1978 -- CBC CC Bug 2111529 07-Feb-2001 S Brewer End(2)
1979 ELSE
1980 IF g_debug_mode = 'Y'
1981 THEN
1982 Put_Debug_Msg( l_full_path,'Flag is Y, using WF version 2');
1983 END IF;
1984 END IF;
1985
1986 END IF;
1987
1988 wf_engine.SetItemAttrText(g_itemtype,g_itemkey,'PREPARER_NAME',g_preparer_name);
1989 wf_engine.SetItemAttrText(g_itemtype,g_itemkey,'OWNER_NAME',g_owner_name);
1990 wf_engine.SetItemAttrNumber (g_itemtype,g_itemkey,'WF_VERSION',g_wf_version);
1991 wf_engine.SetItemAttrText (g_itemtype,g_itemkey,'CC_STATE',g_cc_state);
1992 wf_engine.SetItemAttrText (g_itemtype,g_itemkey,'CC_ENCUMBRANCE_STATUS',g_cc_enc_status);
1993 wf_engine.SetItemAttrText (g_itemtype,g_itemkey,'CC_APPRVL_STATUS',g_cc_appr_status);
1994 wf_engine.SetItemAttrText (g_itemtype,g_itemkey,'BC_REQUIRED',g_bc_reqired);
1995 wf_engine.SetItemAttrText (g_itemtype,g_itemkey,'CC_TYPE',g_cc_type);
1996 wf_engine.SetItemAttrText (g_itemtype,g_itemkey,'NOTE',g_note);
1997 wf_engine.SetItemAttrText (g_itemtype,g_itemkey,'CC_DESC',l_cc_desc);
1998 wf_engine.SetItemAttrText (g_itemtype,g_itemkey,'CC_VERSION_NUM',g_cc_version_number);
1999 wf_engine.SetItemAttrText (g_itemtype,g_itemkey,'BC_EXECUTED',g_bc_executed);
2000 wf_engine.SetItemAttrText (g_itemtype,g_itemkey,'CC_CTRL_STATUS',g_cc_ctrl_status);
2001 wf_engine.SetItemAttrNumber (g_itemtype,g_itemkey,'ORG_ID',g_org_id);
2002 wf_engine.SetItemAttrNumber (g_itemtype,g_itemkey,'SOB_ID',g_sob_id);
2003
2004 wf_engine.SetItemAttrNumber (g_itemtype,g_itemkey,'CC_HEADER_ID',g_cc_header_id);
2005 wf_engine.SetItemAttrText (g_itemtype,g_itemkey,'CC_NUMBER',g_cc_number);
2006 wf_engine.SetItemAttrNumber (g_itemtype,g_itemkey,'CC_OWNER_USER_ID',g_owner_id);
2007 wf_engine.SetItemAttrText (g_itemtype,g_itemkey,'OWNER_NAME',g_owner_name);
2008 wf_engine.SetItemAttrText (g_itemtype,g_itemkey,'OWNER_DISP_NAME',l_owner_display_name);
2009 wf_engine.SetItemAttrNumber (g_itemtype,g_itemkey,'CC_PREPARER_USER_ID',g_preparer_id);
2010 wf_engine.SetItemAttrText (g_itemtype,g_itemkey,'PREPARER_NAME',g_preparer_name);
2011 wf_engine.SetItemAttrText (g_itemtype,g_itemkey,'PREPARER_DISP_NAME',l_user_display_name);
2012 wf_engine.SetItemAttrText (g_itemtype,g_itemkey,'CC_DESC',l_cc_desc);
2013 wf_engine.SetItemAttrDate (g_itemtype,g_itemkey,'CC_START_DATE',l_cc_start_date);
2014 wf_engine.SetItemAttrDate (g_itemtype,g_itemkey,'CC_END_DATE',l_cc_end_date);
2015 wf_engine.SetItemAttrText (g_itemtype,g_itemkey,'ORGANIZATION_NAME',l_org_name);
2016 wf_engine.SetItemAttrDate (g_itemtype,g_itemkey,'CC_ACCT_DATE',g_acct_date);
2017
2018 IF g_debug_mode = 'Y'
2019 THEN
2020 Put_Debug_Msg( l_full_path,'Initialized vaules:'
2021 ||' PREPARER_NAME ' ||g_preparer_name
2022 ||' OWNER_NAME ' ||g_owner_name
2023 ||' WF_VERSION ' ||g_wf_version
2024 ||' CC_STATE ' ||g_cc_state
2025 ||' CC_ENCUMBRANCE_STATUS ' ||g_cc_enc_status
2026 ||' CC_APPRVL_STATUS ' ||g_cc_appr_status
2027 ||' BC_REQUIRED ' ||g_bc_reqired
2028 ||' CC_TYPE ' ||g_cc_type
2029 ||' NOTE ' ||g_note
2030 ||' CC_DESC ' ||l_cc_desc
2031 ||' CC_VERSION_NUM ' ||g_cc_version_number
2032 ||' BC_EXECUTED ' ||g_bc_executed
2033 ||' CC_CTRL_STATUS ' ||g_cc_ctrl_status
2034 ||' ORG_ID ' ||g_org_id
2035 ||' SOB_ID ' ||g_sob_id
2036 ||' CC_HEADER_ID ' ||g_cc_header_id
2037 ||' CC_NUMBER ' ||g_cc_number
2038 ||' CC_OWNER_USER_ID ' ||g_owner_id
2039 ||' OWNER_NAME ' ||g_owner_name
2040 ||' OWNER_DISP_NAME ' ||l_owner_display_name
2041 ||' CC_PREPARER_USER_ID ' ||g_preparer_id
2042 ||' PREPARER_NAME ' ||g_preparer_name
2043 ||' PREPARER_DISP_NAME ' ||l_user_display_name
2044 ||' CC_DESC ' ||l_cc_desc
2045 ||' CC_START_DATE ' ||l_cc_start_date
2046 ||' CC_END_DATE ' ||l_cc_end_date
2047 ||' ORGANIZATION_NAME ' ||l_org_name
2048 ||' CC_ACCT_DATE ' ||l_account_date);
2049
2050 Put_Debug_Msg( l_full_path,l_api_name||' Successfully completed');
2051 END IF;
2052
2053 EXCEPTION
2054 WHEN FND_API.G_EXC_ERROR THEN
2055 x_return_status := FND_API.G_RET_STS_ERROR;
2056 --Bug 3199488
2057 IF ( g_excep_level >= g_debug_level ) THEN
2058 FND_LOG.STRING (g_excep_level,l_full_path,'FND_API.G_EXC_ERROR Exception Raised');
2059 END IF;
2060 --Bug 3199488
2061
2062 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2063 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2064 --Bug 3199488
2065 IF ( g_excep_level >= g_debug_level ) THEN
2066 FND_LOG.STRING (g_excep_level,l_full_path,'FND_API.G_EXC_UNEXPECTED_ERROR Exception Raised');
2067 END IF;
2068 --Bug 3199488
2069
2070 WHEN OTHERS THEN
2071 Put_Debug_Msg( l_full_path,l_api_name||' raised unhandled exception');
2072
2073 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2074
2075 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
2076 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
2077 END IF;
2078 -- Bug 3199488
2079 IF ( g_unexp_level >= g_debug_level ) THEN
2080 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
2081 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
2082 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
2083 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
2084 END IF;
2085 -- Bug 3199488
2086
2087 END Init_variables;
2088
2089 /* This procedure is calld from the execute BC and reinit version number of the CC */
2090
2091 PROCEDURE Reinit_version(
2092 x_return_status OUT NOCOPY VARCHAR2)
2093 IS
2094 l_api_name CONSTANT VARCHAR2(30) := 'Reinit_version' ;
2095
2096 CURSOR c_cc_data IS
2097 SELECT cc_version_num ,
2098 cc_encmbrnc_status
2099 FROM igc_cc_headers
2100 WHERE cc_header_id = g_cc_header_id;
2101
2102 l_full_path VARCHAR2(255) := g_path||'Reinit_version';
2103
2104 BEGIN
2105
2106 x_return_status := FND_API.G_RET_STS_SUCCESS ;
2107 IF g_debug_mode = 'Y'
2108 THEN
2109 Put_Debug_Msg( l_full_path,l_api_name||' started');
2110 END IF;
2111
2112 OPEN c_cc_data;
2113 FETCH c_cc_data
2114 INTO g_cc_version_number,
2115 g_cc_enc_status;
2116
2117 IF c_cc_data%NOTFOUND THEN
2118
2119 CLOSE c_cc_data;
2120 message_token ('CC_HEADER_ID', g_cc_header_id);
2121 add_message ('IGC', 'IGC_CC_NOT_FOUND');
2122 RAISE FND_API.G_EXC_ERROR;
2123
2124 END IF;
2125
2126 CLOSE c_cc_data;
2127
2128 wf_engine.SetItemAttrText (g_itemtype,g_itemkey,'CC_VERSION_NUM',g_cc_version_number);
2129 wf_engine.SetItemAttrText (g_itemtype,g_itemkey,'CC_ENCUMBRANCE_NEW_STATUS',g_cc_new_enc_status);
2130
2131 IF g_debug_mode = 'Y'
2132 THEN
2133 Put_Debug_Msg( l_full_path,' New values: '||
2134 ' Version number: '||g_cc_version_number||
2135 ' NEw enc status: '||g_cc_new_enc_status);
2136
2137 Put_Debug_Msg( l_full_path,l_api_name||' Successfully completed');
2138 END IF;
2139
2140 EXCEPTION
2141 WHEN FND_API.G_EXC_ERROR THEN
2142 x_return_status := FND_API.G_RET_STS_ERROR;
2143 --Bug 3199488
2144 IF ( g_excep_level >= g_debug_level ) THEN
2145 FND_LOG.STRING (g_excep_level,l_full_path,'FND_API.G_EXC_ERROR Exception Raised');
2146 END IF;
2147 --Bug 3199488
2148
2149 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2150 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2151 --Bug 3199488
2152 IF ( g_excep_level >= g_debug_level ) THEN
2153 FND_LOG.STRING (g_excep_level,l_full_path,'FND_API.G_EXC_UNEXPECTED_ERROR Exception Raised');
2154 END IF;
2155 --Bug 3199488
2156
2157 WHEN OTHERS THEN
2158 Put_Debug_Msg( l_full_path,l_api_name||' raised unhandled exception');
2159
2160 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2161
2162 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
2163 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
2164 END IF;
2165 -- Bug 3199488
2166 IF ( g_unexp_level >= g_debug_level ) THEN
2167 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
2168 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
2169 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
2170 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
2171 END IF;
2172 -- Bug 3199488
2173
2174 END Reinit_version;
2175
2176
2177 /* Procedure calls PO generation for a CC */
2178 PROCEDURE Generate_PO(
2179 x_return_status OUT NOCOPY VARCHAR2)
2180 IS
2181
2182 l_api_name CONSTANT VARCHAR2(30) := 'Generate_PO' ;
2183 l_return_status VARCHAR2(1) ;
2184 l_msg_count NUMBER ;
2185 l_msg_data VARCHAR2(2000) ;
2186
2187 l_full_path VARCHAR2(500) := g_path || 'Generate_PO';
2188 BEGIN
2189
2190 x_return_status := FND_API.G_RET_STS_SUCCESS ;
2191 IF g_debug_mode = 'Y'
2192 THEN
2193 Put_Debug_Msg( l_full_path,l_api_name||' started');
2194 END IF;
2195
2196 IGC_CC_PO_INTERFACE_PKG.Convert_Cc_To_Po
2197 ( p_api_version => 1.0,
2198 x_return_status => l_return_status,
2199 x_msg_count => l_msg_count,
2200 x_msg_data => l_msg_data,
2201 p_cc_header_id => g_cc_header_id
2202 );
2203
2204 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2205 IF g_debug_mode = 'Y'
2206 THEN
2207 Put_Debug_Msg( l_full_path,'Error during PO generation');
2208 END IF;
2209 raise FND_API.G_EXC_ERROR;
2210 END IF;
2211
2212 IF g_debug_mode = 'Y'
2213 THEN
2214 Put_Debug_Msg( l_full_path,l_api_name||' Successfully completed');
2215 END IF;
2216
2217 EXCEPTION
2218 WHEN FND_API.G_EXC_ERROR THEN
2219 x_return_status := FND_API.G_RET_STS_ERROR;
2220 --Bug 3199488
2221 IF ( g_excep_level >= g_debug_level ) THEN
2222 FND_LOG.STRING (g_excep_level,l_full_path,'FND_API.G_EXC_ERROR Exception Raised');
2223 END IF;
2224 --Bug 3199488
2225
2226 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2227 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2228 --Bug 3199488
2229 IF ( g_excep_level >= g_debug_level ) THEN
2230 FND_LOG.STRING (g_excep_level,l_full_path,'FND_API.G_EXC_UNEXPECTED_ERROR Exception Raised');
2231 END IF;
2232 --Bug 3199488
2233
2234 WHEN OTHERS THEN
2235 Put_Debug_Msg( l_full_path,l_api_name||' raised unhandled exception');
2236
2237 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2238
2239 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
2240 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
2241 END IF;
2242 -- Bug 3199488
2243 IF ( g_unexp_level >= g_debug_level ) THEN
2244 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
2245 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
2246 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
2247 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
2248 END IF;
2249 -- Bug 3199488
2250
2251 END Generate_PO;
2252
2253
2254
2255 /* Main procedure */
2256 PROCEDURE Process_request(
2257 p_action IN VARCHAR2,
2258 x_return_status OUT NOCOPY VARCHAR2)
2259 IS
2260 l_api_name CONSTANT VARCHAR2(30) := 'Process_request' ;
2261 l_return_status VARCHAR2(1) ;
2262 l_full_path VARCHAR2(500) := g_path || 'Process_request';
2263
2264 BEGIN
2265
2266 x_return_status := FND_API.G_RET_STS_SUCCESS ;
2267 IF g_debug_mode = 'Y'
2268 THEN
2269 Put_Debug_Msg( l_full_path,l_api_name||' started');
2270 END IF;
2271
2272 --Get parameters from the WF
2273
2274 Get_Parameters(x_return_status =>l_return_status );
2275
2276 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2277 raise FND_API.G_EXC_ERROR;
2278 END IF;
2279
2280 --Generate appropriate statuses and states
2281
2282 Generate_CC_Action(
2283 p_action => p_action,
2284 x_return_status => l_return_status );
2285
2286 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2287 raise FND_API.G_EXC_ERROR;
2288 END IF;
2289
2290 --Apply changes to CC
2291
2292 Update_CC(x_return_status =>l_return_status );
2293
2294 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2295 raise FND_API.G_EXC_ERROR;
2296 END IF;
2297
2298 --Check if we need to generate PO
2299
2300 IF g_create_po_entries THEN
2301
2302 Generate_PO(x_return_status =>l_return_status );
2303
2304 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2305 raise FND_API.G_EXC_ERROR;
2306 END IF;
2307 END IF;
2308
2309 --Create history record
2310
2311 Create_History_Record(x_return_status =>l_return_status );
2312
2313 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2314 raise FND_API.G_EXC_ERROR;
2315 END IF;
2316
2317 --Applying new values to a WF process
2318
2319 Set_Parameters(x_return_status =>l_return_status );
2320
2321 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2322 raise FND_API.G_EXC_ERROR;
2323 END IF;
2324
2325 IF g_debug_mode = 'Y'
2326 THEN
2327 Put_Debug_Msg( l_full_path,l_api_name||' Successfully completed');
2328 END IF;
2329
2330 EXCEPTION
2331 WHEN FND_API.G_EXC_ERROR THEN
2332 x_return_status := FND_API.G_RET_STS_ERROR;
2333 --Bug 3199488
2334 IF ( g_excep_level >= g_debug_level ) THEN
2335 FND_LOG.STRING (g_excep_level,l_full_path,'FND_API.G_EXC_ERROR Exception Raised');
2336 END IF;
2337 --Bug 3199488
2338
2339 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2340 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2341 --Bug 3199488
2342 IF ( g_excep_level >= g_debug_level ) THEN
2343 FND_LOG.STRING (g_excep_level,l_full_path,'FND_API.G_EXC_UNEXPECTED_ERROR Exception Raised');
2344 END IF;
2345 --Bug 3199488
2346
2347 WHEN OTHERS THEN
2348 Put_Debug_Msg( l_full_path,l_api_name||' raised unhandled exception');
2349
2350 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2351
2352 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
2353 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
2354 END IF;
2355
2356 -- Bug 3199488
2357 IF ( g_unexp_level >= g_debug_level ) THEN
2358 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
2359 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
2360 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
2361 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
2362 END IF;
2363 -- Bug 3199488
2364
2365 END Process_request;
2366
2367
2368 /* This procedure determines if WF process has to run executete BC or not */
2369
2370 PROCEDURE Set_BC_Parameter(
2371 x_return_status OUT NOCOPY VARCHAR2)
2372 IS
2373 l_api_name CONSTANT VARCHAR2(30) := 'Set_BC_Parameter' ;
2374 l_return_status VARCHAR2(1) ;
2375 l_msg_count NUMBER ;
2376 l_msg_data VARCHAR2(2000) ;
2377 l_full_path VARCHAR2(500) := g_path || 'Set_BC_Parameter';
2378 BEGIN
2379
2380 x_return_status := FND_API.G_RET_STS_SUCCESS ;
2381 IF g_debug_mode = 'Y'
2382 THEN
2383 Put_Debug_Msg( l_full_path,l_api_name||' started');
2384 END IF;
2385
2386 IGC_CC_BUDGETARY_CTRL_PKG.Check_Budgetary_Ctrl_On(
2387 p_api_version => 1.0,
2388 x_return_status => l_return_status,
2389 x_msg_count => l_msg_count,
2390 x_msg_data => l_msg_data,
2391 p_org_id => g_org_id,
2392 p_sob_id => g_sob_id,
2393 p_cc_state => g_cc_state,
2394 x_encumbrance_on => g_bc_reqired);
2395
2396 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2397 raise FND_API.G_EXC_ERROR;
2398 END IF;
2399
2400 IF g_bc_reqired = FND_API.G_TRUE THEN
2401 g_bc_reqired := 'Y';
2402 ELSE
2403 g_bc_reqired := 'N';
2404 END IF;
2405
2406 IF g_debug_mode = 'Y'
2407 THEN
2408 Put_Debug_Msg( l_full_path,'BC value for the contract parameters: '||g_bc_reqired);
2409 END IF;
2410
2411 -- CBC CC Bug 2174147 17-Jan-2001 S Brewer start(1)
2412 -- The follwing 2 IF statements set the g_bc_required variable to the
2413 -- wrong value. The above call to the IGC_CC_BUDGETARY_CTRL_PKG is enough to
2414 -- correctly set the variable g_bc_required. That is the only call used
2415 -- when approving through the form IGCCENTR.fmb, so, to keep this consistent
2416 -- with manual approval, commenting out the following additional
2417 -- IF statements.
2418
2419 -- IF (g_cc_state = 'CL' AND g_cc_enc_status='P')
2420 -- OR (g_cc_state = 'CT' AND g_cc_enc_status='C' ) THEN
2421
2422 -- g_bc_reqired :='Y'; --Cancellation or Completion requred
2423
2424 -- END IF;
2425
2426 -- IF g_bc_reqired = 'Y' THEN
2427 --Can be reqired only if turned on
2428 /* Possible values of g_bc_reqired:
2429 'N' - not reqired,
2430 'Y' - reservation */
2431 -- IF ( g_cc_state IN ('PR','CM')) AND g_cc_enc_status= 'N' OR g_cc_enc_status='T' THEN
2432 -- g_bc_reqired :='Y'; --Reservation requred
2433 -- ELSE
2434 -- g_bc_reqired :='N';
2435 -- END IF;
2436 -- END IF;
2437
2438 -- CBC CC Bug 2174147 17-Jan-2001 S Brewer end(1)
2439
2440 IF g_debug_mode = 'Y'
2441 THEN
2442 Put_Debug_Msg( l_full_path,'Final BC value : '||g_bc_reqired||' CC state '||g_cc_state||' enc status '||g_cc_enc_status);
2443
2444 Put_Debug_Msg( l_full_path,l_api_name||' Successfully completed');
2445 END IF;
2446
2447 EXCEPTION
2448 WHEN FND_API.G_EXC_ERROR THEN
2449 x_return_status := FND_API.G_RET_STS_ERROR;
2450 --Bug 3199488
2451 IF ( g_excep_level >= g_debug_level ) THEN
2452 FND_LOG.STRING (g_excep_level,l_full_path,'FND_API.G_EXC_ERROR Exception Raised');
2453 END IF;
2454 --Bug 3199488
2455
2456 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2457 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2458 --Bug 3199488
2459 IF ( g_excep_level >= g_debug_level ) THEN
2460 FND_LOG.STRING (g_excep_level,l_full_path,'FND_API.G_EXC_UNEXPECTED_ERROR Exception Raised');
2461 END IF;
2462 --Bug 3199488
2463
2464 WHEN OTHERS THEN
2465 Put_Debug_Msg( l_full_path,l_api_name||' raised unhandled exception');
2466
2467 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2468
2469 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
2470 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
2471 END IF;
2472 -- Bug 3199488
2473 IF ( g_unexp_level >= g_debug_level ) THEN
2474 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
2475 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
2476 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
2477 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
2478 END IF;
2479 -- Bug 3199488
2480
2481 END Set_BC_Parameter;
2482
2483 /* Procedure creates a recorn in the history table */
2484 PROCEDURE Create_History_Record(
2485 x_return_status OUT NOCOPY VARCHAR2)
2486 IS
2487 l_api_name CONSTANT VARCHAR2(30) := 'Create_History_Record' ;
2488 l_return_status VARCHAR2(1) ;
2489 l_msg_count NUMBER ;
2490 l_msg_data VARCHAR2(2000) ;
2491 l_rowid VARCHAR2(30);
2492
2493 l_full_path VARCHAR2(500) := g_path || 'Create_History_Record';
2494 BEGIN
2495
2496 x_return_status := FND_API.G_RET_STS_SUCCESS ;
2497 IF g_debug_mode = 'Y'
2498 THEN
2499 Put_Debug_Msg( l_full_path,l_api_name||' started');
2500 END IF;
2501
2502 IGC_CC_ACTIONS_PKG.Insert_Row
2503 ( p_api_version => 1.0,
2504 x_return_status => l_return_status,
2505 x_msg_count => l_msg_count,
2506 x_msg_data => l_msg_data,
2507 p_rowid => l_rowid,
2508 p_cc_header_id => g_cc_header_id,
2509 p_cc_action_version_num => g_cc_version_number,
2510 p_cc_action_type => g_cc_action_type,
2511 p_cc_action_state => g_cc_new_state,
2512 p_cc_action_ctrl_status => g_cc_ctrl_status,
2513 p_cc_action_apprvl_status => g_cc_new_appr_status,
2514 p_cc_action_notes => g_action_notes,
2515 p_last_update_date => sysdate,
2516 p_last_updated_by => fnd_global.user_id,
2517 p_last_update_login => fnd_global.login_id,
2518 p_creation_date => sysdate,
2519 p_created_by => fnd_global.user_id
2520 );
2521
2522 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2523 IF g_debug_mode = 'Y'
2524 THEN
2525 Put_Debug_Msg( l_full_path,'Error during history record insertion');
2526 END IF;
2527 raise FND_API.G_EXC_ERROR;
2528 END IF;
2529
2530 IF g_debug_mode = 'Y'
2531 THEN
2532 Put_Debug_Msg( l_full_path,l_api_name||' Successfully completed');
2533 END IF;
2534
2535
2536 EXCEPTION
2537 WHEN FND_API.G_EXC_ERROR THEN
2538 x_return_status := FND_API.G_RET_STS_ERROR;
2539 --Bug 3199488
2540 IF ( g_excep_level >= g_debug_level ) THEN
2541 FND_LOG.STRING (g_excep_level,l_full_path,'FND_API.G_EXC_ERROR Exception Raised');
2542 END IF;
2543 --Bug 3199488
2544
2545 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2546 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2547 --Bug 3199488
2548 IF ( g_excep_level >= g_debug_level ) THEN
2549 FND_LOG.STRING (g_excep_level,l_full_path,'FND_API.G_EXC_UNEXPECTED_ERROR Exception Raised');
2550 END IF;
2551 --Bug 3199488
2552
2553 WHEN OTHERS THEN
2554 Put_Debug_Msg( l_full_path,l_api_name||' raised unhandled exception');
2555
2556 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2557
2558 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
2559 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
2560 END IF;
2561 -- Bug 3199488
2562 IF ( g_unexp_level >= g_debug_level ) THEN
2563 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
2564 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
2565 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
2566 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
2567 END IF;
2568 -- Bug 3199488
2569
2570 END Create_History_Record;
2571
2572
2573 /* Procedure update CC with the new statuses */
2574
2575 PROCEDURE Update_CC(
2576 x_return_status OUT NOCOPY VARCHAR2)
2577 IS
2578 l_api_name CONSTANT VARCHAR2(30) := 'Update_CC' ;
2579 CURSOR c_update_cc IS
2580 SELECT cc_state ,
2581 cc_encmbrnc_status ,
2582 cc_apprvl_status
2583 FROM igc_cc_headers
2584 WHERE cc_header_id = g_cc_header_id
2585 FOR UPDATE;
2586
2587 l_return_status VARCHAR2(1) ;
2588 l_msg_count NUMBER ;
2589 l_msg_data VARCHAR2(2000) ;
2590
2591 l_full_path VARCHAR2(500) := g_path || 'Update_CC';
2592
2593 BEGIN
2594
2595 x_return_status := FND_API.G_RET_STS_SUCCESS ;
2596 IF g_debug_mode = 'Y'
2597 THEN
2598 Put_Debug_Msg( l_full_path,l_api_name||' started');
2599 END IF;
2600
2601 FOR c_update_cc_rec IN c_update_cc LOOP
2602 UPDATE igc_cc_headers
2603 SET cc_state = g_cc_new_state,
2604 cc_encmbrnc_status = g_cc_new_enc_status,
2605 cc_apprvl_status = g_cc_new_appr_status,
2606 last_update_date = sysdate,
2607 last_updated_by = fnd_global.user_id,
2608 last_update_login = fnd_global.login_id
2609 WHERE CURRENT OF c_update_cc;
2610 IF SQL%ROWCOUNT <>1 THEN
2611 message_token ('CC_HEADER_ID', g_cc_header_id);
2612 add_message ('IGC', 'IGC_CC_NOT_FOUND');
2613 RAISE FND_API.G_EXC_ERROR;
2614 END IF;
2615 END LOOP;
2616
2617 IF g_restore_enc THEN
2618
2619 IF g_debug_mode = 'Y'
2620 THEN
2621 Put_Debug_Msg( l_full_path,'Restoring enc status');
2622 END IF;
2623
2624 IGC_CC_BUDGETARY_CTRL_PKG.Set_Encumbrance_Status
2625 ( p_api_version => 1.0,
2626 x_return_status => l_return_status,
2627 x_msg_count => l_msg_count,
2628 x_msg_data => l_msg_data,
2629 p_cc_header_id => g_cc_header_id,
2630 p_encumbrance_status_code => 'P');
2631
2632
2633 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2634 IF g_debug_mode = 'Y'
2635 THEN
2636 Put_Debug_Msg( l_full_path,'IGC_CC_BUDGETARY_CTRL_PKG.Set_Encumbrance_Status returned error ');
2637 END IF;
2638 raise FND_API.G_EXC_ERROR;
2639 END IF;
2640
2641 END IF;
2642
2643 IF g_debug_mode = 'Y'
2644 THEN
2645 Put_Debug_Msg( l_full_path,l_api_name||'Successfully completed');
2646 END IF;
2647
2648 EXCEPTION
2649 WHEN FND_API.G_EXC_ERROR THEN
2650 x_return_status := FND_API.G_RET_STS_ERROR;
2651 --Bug 3199488
2652 IF ( g_excep_level >= g_debug_level ) THEN
2653 FND_LOG.STRING (g_excep_level,l_full_path,'FND_API.G_EXC_ERROR Exception Raised');
2654 END IF;
2655 --Bug 3199488
2656
2657 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2658 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2659 --Bug 3199488
2660 IF ( g_excep_level >= g_debug_level ) THEN
2661 FND_LOG.STRING (g_excep_level,l_full_path,'FND_API.G_EXC_UNEXPECTED_ERROR Exception Raised');
2662 END IF;
2663 --Bug 3199488
2664
2665 WHEN OTHERS THEN
2666 Put_Debug_Msg( l_full_path,l_api_name||' raised unhandled exception');
2667
2668 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2669
2670 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
2671 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
2672 END IF;
2673 -- Bug 3199488
2674 IF ( g_unexp_level >= g_debug_level ) THEN
2675 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
2676 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
2677 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
2678 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
2679 END IF;
2680 -- Bug 3199488
2681
2682 END Update_CC;
2683
2684 /* Procedure gets business group id*/
2685
2686 PROCEDURE Get_Business_Group(
2687 x_return_status OUT NOCOPY VARCHAR2)
2688 IS
2689 l_api_name CONSTANT VARCHAR2(30) := 'Get_Business_Group' ;
2690 l_full_path VARCHAR2(500) := g_path || 'Get_Business_Group';
2691
2692 CURSOR c_bg IS
2693 SELECT business_group_id
2694 FROM financials_system_parameters;
2695
2696
2697 -- CBC CC Bug 2111529 07-Feb-2001 S Brewer Start(3)
2698 -- Cursor to find value in use approval hierarchies flag
2699 CURSOR c_use_positions IS
2700 SELECT use_positions_flag
2701 FROM financials_system_parameters;
2702
2703 l_use_positions VARCHAR2(1);
2704 -- CBC CC Bug 2111529 07-Feb-2001 S Brewer End(3)
2705
2706
2707 CURSOR c_hierarchy_id IS
2708 SELECT pos_structure_version_id
2709 FROM per_pos_structure_versions
2710 WHERE position_structure_id =
2711 ( SELECT default_approval_path_id
2712 FROM igc_cc_routing_ctrls
2713 WHERE org_id = g_org_id
2714 AND cc_type = g_cc_type
2715 AND cc_state = g_cc_state
2716 )
2717 AND sysdate
2718 BETWEEN NVL(date_from,sysdate) AND NVL(date_to,sysdate);
2719
2720 BEGIN
2721
2722 x_return_status := FND_API.G_RET_STS_SUCCESS ;
2723 IF g_debug_mode = 'Y'
2724 THEN
2725 Put_Debug_Msg( l_full_path,l_api_name||' started');
2726 END IF;
2727
2728 OPEN c_bg;
2729 FETCH c_bg INTO g_business_group_id;
2730 CLOSE c_bg;
2731
2732
2733 -- CBC CC Bug 2111529 07-Feb-2001 S Brewer Start(4)
2734 -- Only need to get the position structure if using approval hierarchies
2735 OPEN c_use_positions;
2736 FETCH c_use_positions INTO l_use_positions;
2737 CLOSE c_use_positions;
2738
2739 g_use_approval_hier := nvl(l_use_positions,'N');
2740 IF (g_use_approval_hier = 'Y') THEN
2741
2742
2743 OPEN c_hierarchy_id;
2744 FETCH c_hierarchy_id INTO g_pos_structure_version_id;
2745 CLOSE c_hierarchy_id;
2746
2747 IF (g_pos_structure_version_id IS NULL) THEN
2748
2749 -- The Use Approval hierarchies option has been chosen, but no position
2750 -- hierarchy has been assigned to this document type
2751 message_token('CC_TYPE',g_cc_type);
2752 message_token('CC_STATE',g_cc_state);
2753 add_message('IGC','IGC_NO_POS_HIER');
2754 RAISE FND_API.G_EXC_ERROR;
2755 END IF;
2756
2757 END IF;
2758 -- CBC CC Bug 2111529 07-Feb-2001 S Brewer End(4)
2759
2760
2761 IF g_debug_mode = 'Y'
2762 THEN
2763 Put_Debug_Msg( l_full_path,l_api_name||' Successfully completed');
2764 END IF;
2765
2766 EXCEPTION
2767 WHEN FND_API.G_EXC_ERROR THEN
2768 x_return_status := FND_API.G_RET_STS_ERROR;
2769 --Bug 3199488
2770 IF ( g_excep_level >= g_debug_level ) THEN
2771 FND_LOG.STRING (g_excep_level,l_full_path,'FND_API.G_EXC_ERROR Exception Raised');
2772 END IF;
2773 --Bug 3199488
2774
2775 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2776 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2777 --Bug 3199488
2778 IF ( g_excep_level >= g_debug_level ) THEN
2779 FND_LOG.STRING (g_excep_level,l_full_path,'FND_API.G_EXC_UNEXPECTED_ERROR Exception Raised');
2780 END IF;
2781 --Bug 3199488
2782
2783 WHEN OTHERS THEN
2784 Put_Debug_Msg( l_full_path,l_api_name||' raised unhandled exception');
2785
2786 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2787
2788 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
2789 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
2790 END IF;
2791 -- Bug 3199488
2792 IF ( g_unexp_level >= g_debug_level ) THEN
2793 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
2794 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
2795 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
2796 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
2797 END IF;
2798 -- Bug 3199488
2799
2800 END Get_Business_Group;
2801
2802
2803 /* Procedure gets business group id*/
2804 PROCEDURE Find_Next_Approver(
2805 x_return_status OUT NOCOPY VARCHAR2)
2806 IS
2807 l_api_name CONSTANT VARCHAR2(30) := 'Find_Next_Approver' ;
2808 l_return_status VARCHAR2(1) ;
2809
2810 l_approver_display_name VARCHAR2(255);
2811 l_cur_approver_id NUMBER(15);
2812 l_pos_id NUMBER(15);
2813 l_new_pos_id NUMBER(15);
2814 l_job_id NUMBER(15);
2815 l_new_job_id NUMBER(15);
2816 l_supervisor_id NUMBER(15);
2817 l_full_path VARCHAR2(500) := g_path || 'Find_Next_Approver';
2818
2819 /* Find job/position, using person id*/
2820 CURSOR c_user_pos_id IS
2821 SELECT position_id,
2822 job_id,
2823 supervisor_id
2824 FROM per_assignments_f
2825 WHERE person_id = g_approver_id
2826 AND business_group_id = g_business_group_id
2827 AND sysdate BETWEEN effective_start_date
2828 AND effective_end_date;
2829
2830 /* Find person, using position id*/
2831 CURSOR c_pos_user_id (cpos_id NUMBER) IS
2832 SELECT pep.person_id
2833 FROM per_assignments_f ass,
2834 per_all_people_f pep
2835 WHERE position_id = cpos_id
2836 AND ass.person_id = pep.person_id
2837 AND ass.business_group_id = g_business_group_id
2838 AND pep.business_group_id = g_business_group_id
2839 AND sysdate BETWEEN ass.effective_start_date
2840 AND ass.effective_end_date
2841 ORDER BY pep.full_name;
2842
2843 /* Find person, using job id*/
2844 CURSOR c_job_user_id (cjob_id NUMBER) IS
2845 SELECT pep.person_id
2846 FROM per_assignments_f ass,
2847 per_all_people_f pep
2848 WHERE job_id = cjob_id
2849 AND ass.person_id = pep.person_id
2850 AND ass.business_group_id = g_business_group_id
2851 AND pep.business_group_id = g_business_group_id
2852 AND sysdate BETWEEN ass.effective_start_date
2853 AND ass.effective_end_date
2854 ORDER BY pep.full_name;
2855
2856
2857 /* Find upper position id*/
2858 CURSOR c_sub_pos_id (subpos_id NUMBER) IS
2859 SELECT parent_position_id
2860 FROM per_pos_structure_elements
2861 WHERE subordinate_position_id = subpos_id
2862 AND business_group_id = g_business_group_id
2863 AND pos_structure_version_id =g_pos_structure_version_id;
2864
2865 BEGIN
2866
2867 x_return_status := FND_API.G_RET_STS_SUCCESS ;
2868 IF g_debug_mode = 'Y'
2869 THEN
2870 Put_Debug_Msg( l_full_path,l_api_name||' started');
2871 END IF;
2872
2873 Get_Business_group(x_return_status =>l_return_status );
2874
2875 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2876 raise FND_API.G_EXC_ERROR;
2877 END IF;
2878
2879 --Looking for positions for current approver
2880
2881 OPEN c_user_pos_id;
2882 FETCH c_user_pos_id INTO l_pos_id,l_job_id,l_supervisor_id;
2883
2884
2885 IF c_user_pos_id%NOTFOUND THEN
2886 CLOSE c_user_pos_id;
2887 message_token ('PERSON_ID', g_approver_id);
2888 message_token ('GROUP_ID', g_business_group_id);
2889 message_token ('DATE', sysdate);
2890 add_message ('IGC', 'IGC_ASSIGNMENT_NOT_FOUND');
2891 RAISE FND_API.G_EXC_ERROR;
2892 END IF;
2893
2894 IF g_debug_mode = 'Y'
2895 THEN
2896 Put_Debug_Msg( l_full_path,'Getting data for the current approver: '||
2897 ' Approver id '|| g_approver_id||
2898 ' Position id '|| l_pos_id||
2899 ' Job id '|| l_job_id||
2900 ' Supervisor id '|| l_supervisor_id
2901 );
2902 END IF;
2903
2904 CLOSE c_user_pos_id;
2905
2906
2907 -- CBC CC Bug 2111529 07-Feb-2001 S Brewer Start(5)
2908 -- Want to use the 'Use Approval Hierarchies' flag instead
2909
2910 IF (g_use_approval_hier = 'Y') THEN
2911 -- CBC CC Bug 2111529 07-Feb-2001 S Brewer End(5)
2912
2913
2914 IF l_pos_id IS NOT NULL AND g_pos_structure_version_id IS NOT NULL THEN
2915 --Use position hierarchy
2916 IF g_debug_mode = 'Y'
2917 THEN
2918 Put_Debug_Msg( l_full_path,'Using position hierarchy');
2919 END IF;
2920
2921 WHILE l_cur_approver_id IS NULL LOOP
2922
2923 OPEN c_sub_pos_id(l_pos_id);
2924 FETCH c_sub_pos_id INTO l_new_pos_id;
2925
2926 IF c_sub_pos_id%NOTFOUND THEN
2927 CLOSE c_sub_pos_id;
2928 message_token ('POSITION_ID', l_pos_id);
2929 message_token ('STRUCTURE_ID', g_pos_structure_version_id);
2930 message_token ('GROUP_ID', g_business_group_id);
2931 add_message ('IGC', 'IGC_PARENT_POS_NOT_FOUND');
2932 RAISE FND_API.G_EXC_ERROR;
2933 END IF;
2934
2935 CLOSE c_sub_pos_id;
2936
2937 IF g_debug_mode = 'Y'
2938 THEN
2939 Put_Debug_Msg( l_full_path,'Next position found: '||l_new_pos_id);
2940 END IF;
2941
2942 OPEN c_pos_user_id(l_new_pos_id);
2943 FETCH c_pos_user_id INTO l_cur_approver_id;
2944 CLOSE c_pos_user_id;
2945
2946 l_pos_id :=l_new_pos_id;
2947
2948 IF g_debug_mode = 'Y'
2949 THEN
2950 Put_Debug_Msg( l_full_path,'Person for the position: '||NVL(to_char(l_cur_approver_id),' Not assigned'));
2951 END IF;
2952
2953 END LOOP;
2954 ELSIF g_pos_structure_version_id IS NOT NULL THEN
2955 --Problem position hierarchy assigned for the CC, but no position for the person found
2956 message_token ('APPROVER_ID', g_approver_id);
2957 message_token ('STRUCTURE_ID', g_pos_structure_version_id);
2958 message_token ('GROUP_ID', g_business_group_id);
2959 add_message ('IGC', 'IGC_POS_STRUCTURE_NOT_FOUND');
2960 RAISE FND_API.G_EXC_ERROR;
2961 ELSE
2962
2963 -- CBC CC Bug 2111529 07-Feb-2001 S Brewer Start(6)
2964 -- The option 'Use Approval Hierarchies' has been chosen, but no
2965 -- position hierarchy has been assigned to this document type
2966 message_token('CC_TYPE',g_cc_type);
2967 message_token('CC_STATE',g_cc_state);
2968 add_message('IGC','IGC_NO_POS_HIER');
2969 RAISE FND_API.G_EXC_ERROR;
2970 END IF;
2971
2972 ELSIF (g_use_approval_hier = 'N') THEN
2973
2974 -- CBC CC Bug 2111529 07-Feb-2001 S Brewer End(6)
2975 IF g_debug_mode = 'Y'
2976 THEN
2977 Put_Debug_Msg( l_full_path,'Using job hierarchy');
2978 END IF;
2979
2980 IF l_supervisor_id IS NULL THEN --Problem - no position, no supervisor
2981 message_token ('APPROVER_ID', g_approver_id);
2982 message_token ('GROUP_ID', g_business_group_id);
2983 add_message ('IGC', 'IGC_SUPERVISOR_NOT_FOUND');
2984 RAISE FND_API.G_EXC_ERROR;
2985 END IF;
2986 l_cur_approver_id:= l_supervisor_id;
2987 IF g_debug_mode = 'Y'
2988 THEN
2989 Put_Debug_Msg( l_full_path,'Assigned approver: '||l_cur_approver_id);
2990 END IF;
2991 END IF;
2992
2993
2994 g_approver_id:= l_cur_approver_id;
2995
2996 WF_DIRECTORY.GetUserName('PER',
2997 g_approver_id,
2998 g_approver_name,
2999 l_approver_display_name);
3000
3001
3002 IF g_debug_mode = 'Y'
3003 THEN
3004 Put_Debug_Msg( l_full_path,l_api_name||' Successfully completed');
3005 END IF;
3006
3007 EXCEPTION
3008 WHEN FND_API.G_EXC_ERROR THEN
3009 x_return_status := FND_API.G_RET_STS_ERROR;
3010 --Bug 3199488
3011 IF ( g_excep_level >= g_debug_level ) THEN
3012 FND_LOG.STRING (g_excep_level,l_full_path,'FND_API.G_EXC_ERROR Exception Raised');
3013 END IF;
3014 --Bug 3199488
3015
3016 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3017 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3018 --Bug 3199488
3019 IF ( g_excep_level >= g_debug_level ) THEN
3020 FND_LOG.STRING (g_excep_level,l_full_path,'FND_API.G_EXC_UNEXPECTED_ERROR Exception Raised');
3021 END IF;
3022 --Bug 3199488
3023
3024 WHEN OTHERS THEN
3025 Put_Debug_Msg( l_full_path,l_api_name||' raised unhandled exception');
3026
3027 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3028
3029 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
3030 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
3031 END IF;
3032 -- Bug 3199488
3033 IF ( g_unexp_level >= g_debug_level ) THEN
3034 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
3035 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
3036 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
3037 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
3038 END IF;
3039 -- Bug 3199488
3040
3041 END Find_Next_Approver;
3042
3043
3044
3045 /* Procedure checks authority of the particular position or group */
3046
3047 PROCEDURE Check_Authority(
3048 p_result OUT NOCOPY VARCHAR2,
3049 x_return_status OUT NOCOPY VARCHAR2)
3050 IS
3051
3052 l_api_name CONSTANT VARCHAR2(30) := 'Check_Authority' ;
3053 l_return_status VARCHAR2(1) ;
3054
3055 l_pos_structure_version_id NUMBER(15) := 65;
3056 l_pos_id NUMBER(15);
3057 l_job_id NUMBER(15);
3058 l_control_group_id igc_cc_control_groups.control_group_id%TYPE;
3059 l_enabled_flag igc_cc_control_groups.enabled_flag%TYPE;
3060 l_active_date igc_cc_control_groups.active_date%TYPE;
3061 l_group_amount igc_cc_control_groups.amount%TYPE;
3062 l_total_amount igc_cc_control_groups.amount%TYPE;
3063 l_ccid_in_range boolean;
3064
3065 TYPE control_rules_tbl_type IS TABLE OF igc_cc_control_rules%ROWTYPE
3066 INDEX BY BINARY_INTEGER;
3067
3068 l_control_rules_tbl control_rules_tbl_type;
3069 l_total_accounts NUMBER;
3070 l_cur_account NUMBER;
3071 l_full_path VARCHAR2(500) := g_path || 'Check_Authority';
3072
3073 /* Find job/position, using person id*/
3074 CURSOR c_user_pos_id IS
3075 SELECT position_id,
3076 job_id
3077 FROM per_assignments_f
3078 WHERE person_id = g_approver_id
3079 AND business_group_id = g_business_group_id
3080 AND sysdate BETWEEN effective_start_date
3081 AND effective_end_date;
3082
3083 CURSOR c_control_func_info IS
3084 SELECT control_group_id
3085 FROM igc_cc_control_functions
3086 WHERE sysdate BETWEEN NVL(start_date,sysdate-1) AND NVL(end_date,sysdate+1)
3087 AND ( (l_pos_id IS NOT NULL AND position_id = l_pos_id)
3088 OR (l_job_id IS NOT NULL AND job_id = l_job_id)
3089 )
3090 AND cc_state = g_cc_state
3091 AND cc_type = g_cc_type
3092 AND org_id = g_org_id ;
3093
3094 -- CBC CC Bug 2111529 07-Feb-2001 S Brewer Start(7)
3095 -- Cursor to find control group for the position (if position hierarchies
3096 -- are being used)
3097 CURSOR c_pos_cont_func_info(p_position_id igc_cc_control_functions.position_id%TYPE) IS
3098 SELECT control_group_id
3099 FROM igc_cc_control_functions
3100 WHERE sysdate BETWEEN nvl(start_date,sysdate) AND nvl(end_date,sysdate)
3101 AND position_id = p_position_id
3102 AND cc_state = g_cc_state
3103 AND cc_type = g_cc_type
3104 AND org_id = g_org_id;
3105
3106 -- CBC CC Bug 2111529 07-Feb-2001 S Brewer End(7)
3107
3108
3109
3110 CURSOR c_control_rules_info IS
3111 SELECT control_rule_id ,
3112 rule_type_code ,
3113 amount_limit ,
3114 segment1_low ,
3115 segment2_low ,
3116 segment3_low ,
3117 segment4_low ,
3118 segment5_low ,
3119 segment6_low ,
3120 segment7_low ,
3121 segment8_low ,
3122 segment9_low ,
3123 segment10_low ,
3124 segment11_low ,
3125 segment12_low ,
3126 segment13_low ,
3127 segment14_low ,
3128 segment15_low ,
3129 segment16_low ,
3130 segment17_low ,
3131 segment18_low ,
3132 segment19_low ,
3133 segment20_low ,
3134 segment21_low ,
3135 segment22_low ,
3136 segment23_low ,
3137 segment24_low ,
3138 segment25_low ,
3139 segment26_low ,
3140 segment27_low ,
3141 segment28_low ,
3142 segment29_low ,
3143 segment30_low ,
3144 segment1_high ,
3145 segment2_high ,
3146 segment3_high ,
3147 segment4_high ,
3148 segment5_high ,
3149 segment6_high ,
3150 segment7_high ,
3151 segment8_high ,
3152 segment9_high ,
3153 segment10_high ,
3154 segment11_high ,
3155 segment12_high ,
3156 segment13_high ,
3157 segment14_high ,
3158 segment15_high ,
3159 segment16_high ,
3160 segment17_high ,
3161 segment18_high ,
3162 segment19_high ,
3163 segment20_high ,
3164 segment21_high ,
3165 segment22_high ,
3166 segment23_high ,
3167 segment24_high ,
3168 segment25_high ,
3169 segment26_high ,
3170 segment27_high ,
3171 segment28_high ,
3172 segment29_high ,
3173 segment30_high
3174 FROM igc_cc_control_rules
3175 WHERE org_id = g_org_id
3176 AND control_group_id = l_control_group_id;
3177
3178 CURSOR c_control_group_info IS
3179 SELECT enabled_flag ,
3180 active_date ,
3181 amount
3182 FROM igc_cc_control_groups
3183 WHERE org_id = g_org_id
3184 AND control_group_id = l_control_group_id;
3185
3186
3187 -- Performance Tuning, replaced view igc_cc_acct_lines_v
3188 -- with igc_cc_acct_lines
3189 -- Also replaced the following :-
3190 -- cc_acct_comp_func_amt amount
3191 CURSOR c_cc_account_lines IS
3192 SELECT ccal.cc_acct_line_id line_id,
3193 ccal.cc_charge_code_combination_id ccid,
3194 IGC_CC_COMP_AMT_PKG.COMPUTE_FUNCTIONAL_AMT(ccal.cc_header_id,
3195 NVL(ccal.cc_acct_entered_amt,0)) amount
3196 FROM igc_cc_acct_lines ccal
3197 WHERE ccal.cc_header_id = g_cc_header_id
3198 ORDER BY ccal.cc_acct_line_id;
3199
3200 -- Performance Tuning, replaced view igc_cc_acct_lines_v
3201 -- with igc_cc_acct_lines
3202 -- Also replaced the following :-
3203 -- SELECT SUM(cc_acct_comp_func_amt)
3204 CURSOR c_total_amount IS
3205 SELECT SUM(IGC_CC_COMP_AMT_PKG.COMPUTE_FUNCTIONAL_AMT(ccal.cc_header_id,
3206 NVL(ccal.cc_acct_entered_amt,0)))
3207 FROM igc_cc_acct_lines ccal
3208 WHERE ccal.cc_header_id = g_cc_header_id;
3209
3210
3211 CURSOR c_ccid_info (l_code_id NUMBER) IS
3212 SELECT segment1 ,
3213 segment2 ,
3214 segment3 ,
3215 segment4 ,
3216 segment5 ,
3217 segment6 ,
3218 segment7 ,
3219 segment8 ,
3220 segment9 ,
3221 segment10 ,
3222 segment11 ,
3223 segment12 ,
3224 segment13 ,
3225 segment14 ,
3226 segment15 ,
3227 segment16 ,
3228 segment17 ,
3229 segment18 ,
3230 segment19 ,
3231 segment20 ,
3232 segment21 ,
3233 segment22 ,
3234 segment23 ,
3235 segment24 ,
3236 segment25 ,
3237 segment26 ,
3238 segment27 ,
3239 segment28 ,
3240 segment29 ,
3241 segment30
3242 FROM gl_code_combinations
3243 WHERE code_combination_id = l_code_id;
3244
3245
3246 BEGIN
3247
3248 x_return_status := FND_API.G_RET_STS_SUCCESS ;
3249 IF g_debug_mode = 'Y'
3250 THEN
3251 Put_Debug_Msg( l_full_path,l_api_name||' started');
3252 END IF;
3253
3254 p_result:='N';
3255
3256 Get_Business_group(x_return_status =>l_return_status );
3257
3258 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3259 raise FND_API.G_EXC_ERROR;
3260 END IF;
3261
3262 --Looking for position/job for current approver
3263
3264 IF g_debug_mode = 'Y'
3265 THEN
3266 Put_Debug_Msg( l_full_path,'Searching job/position');
3267 END IF;
3268
3269 OPEN c_user_pos_id;
3270 FETCH c_user_pos_id INTO l_pos_id,l_job_id;
3271
3272 IF c_user_pos_id%NOTFOUND THEN
3273 CLOSE c_user_pos_id;
3274 message_token ('PERSON_ID', g_approver_id);
3275 message_token ('GROUP_ID', g_business_group_id);
3276 message_token ('ACTIVE_DATE', sysdate);
3277 add_message ('IGC', 'IGC_ASSIGNMENT_NOT_FOUND');
3278 RAISE FND_API.G_EXC_ERROR;
3279 END IF;
3280
3281 CLOSE c_user_pos_id;
3282
3283 IF g_debug_mode = 'Y'
3284 THEN
3285 Put_Debug_Msg( l_full_path,'Position: '||l_pos_id||' job: '||l_job_id);
3286 END IF;
3287
3288 -- Find the appropriate approval group for the job/position, status, state.
3289
3290 IF g_debug_mode = 'Y'
3291 THEN
3292 Put_Debug_Msg( l_full_path,'Searching control group');
3293 END IF;
3294
3295
3296 -- CBC CC Bug 2111529 07-Feb-2001 S Brewer Start(8)
3297 -- If position hierarchy is being used (determined by option
3298 -- 'Use approval hierarchies'), then we want to use a different
3299 -- cursor to find the control group for the position, not the job
3300 IF (g_use_approval_hier = 'N') THEN
3301 -- Use the original cursor
3302
3303 OPEN c_control_func_info;
3304 FETCH c_control_func_info INTO l_control_group_id;
3305
3306 IF (c_control_func_info%NOTFOUND) THEN
3307 --No group is assigned
3308 IF g_debug_mode = 'Y'
3309 THEN
3310 Put_Debug_Msg( l_full_path,'No control group assigned');
3311 END IF;
3312 CLOSE c_control_func_info;
3313 return;
3314 END IF;
3315
3316 CLOSE c_control_func_info;
3317
3318 ELSIF (g_use_approval_hier = 'Y') THEN
3319
3320 OPEN c_pos_cont_func_info(l_pos_id);
3321 FETCH c_pos_cont_func_info INTO l_control_group_id;
3322 CLOSE c_pos_cont_func_info;
3323
3324 IF g_debug_mode = 'Y'
3325 THEN
3326 Put_Debug_Msg( l_full_path,'g_use_approval_hier = '||g_use_approval_hier);
3327 Put_Debug_Msg( l_full_path,'Found control group '||l_control_group_id);
3328 END IF;
3329
3330 END IF;
3331 -- CBC CC Bug 2111529 07-Feb-2001 S Brewer End(8)
3332
3333
3334 OPEN c_control_group_info;
3335 FETCH c_control_group_info
3336 INTO l_enabled_flag,
3337 l_active_date ,
3338 l_group_amount;
3339
3340 IF g_debug_mode = 'Y'
3341 THEN
3342 Put_Debug_Msg( l_full_path,'Control group data: id '||l_control_group_id||' active date: '||l_active_date||' amount: '||l_group_amount);
3343 END IF;
3344
3345 IF c_control_group_info%NOTFOUND THEN
3346 CLOSE c_control_group_info;
3347 message_token ('CONTROL_GROUP_ID', l_control_group_id);
3348 add_message ('IGC', 'IGC_CONTROL_GROUP_NOT_FOUND');
3349 RAISE FND_API.G_EXC_ERROR;
3350 END IF;
3351
3352 CLOSE c_control_group_info;
3353
3354 -- Check the group enable flag and dates
3355
3356 IF l_enabled_flag<>'Y' OR NVL(l_active_date,sysdate+1)<sysdate THEN
3357 -- Assigned gruop is expired or not enabled - return 'N'
3358 IF g_debug_mode = 'Y'
3359 THEN
3360 Put_Debug_Msg( l_full_path,'Group is disabled or expired, return N');
3361 END IF;
3362 return;
3363 END IF;
3364
3365
3366 --c_control_group_info
3367 --c_control_func_info
3368 --c_control_rules_info
3369
3370 -- Initalize rules for the record group
3371
3372 l_total_accounts:=0;
3373
3374 IF g_debug_mode = 'Y'
3375 THEN
3376 Put_Debug_Msg( l_full_path,'Initializing rules record');
3377 END IF;
3378
3379 FOR c_control_rules_info_rec IN c_control_rules_info LOOP
3380
3381 --Store all ranges for the grup into the PL/SQL table
3382
3383 IF g_debug_mode = 'Y'
3384 THEN
3385 Put_Debug_Msg( l_full_path,'New rule found: '||c_control_rules_info_rec.control_rule_id);
3386 END IF;
3387
3388 l_control_rules_tbl(l_total_accounts).control_rule_id:=c_control_rules_info_rec.control_rule_id;
3389 l_control_rules_tbl(l_total_accounts).rule_type_code:=c_control_rules_info_rec.rule_type_code;
3390 l_control_rules_tbl(l_total_accounts).amount_limit:=c_control_rules_info_rec.amount_limit;
3391 l_control_rules_tbl(l_total_accounts).segment1_low:=c_control_rules_info_rec.segment1_low;
3392 l_control_rules_tbl(l_total_accounts).segment2_low:=c_control_rules_info_rec.segment2_low;
3393 l_control_rules_tbl(l_total_accounts).segment3_low:=c_control_rules_info_rec.segment3_low;
3394 l_control_rules_tbl(l_total_accounts).segment4_low:=c_control_rules_info_rec.segment4_low;
3395 l_control_rules_tbl(l_total_accounts).segment5_low:=c_control_rules_info_rec.segment5_low;
3396 l_control_rules_tbl(l_total_accounts).segment6_low:=c_control_rules_info_rec.segment6_low;
3397 l_control_rules_tbl(l_total_accounts).segment7_low:=c_control_rules_info_rec.segment7_low;
3398 l_control_rules_tbl(l_total_accounts).segment8_low:=c_control_rules_info_rec.segment8_low;
3399 l_control_rules_tbl(l_total_accounts).segment9_low:=c_control_rules_info_rec.segment9_low;
3400 l_control_rules_tbl(l_total_accounts).segment10_low:=c_control_rules_info_rec.segment10_low;
3401 l_control_rules_tbl(l_total_accounts).segment11_low:=c_control_rules_info_rec.segment11_low;
3402 l_control_rules_tbl(l_total_accounts).segment12_low:=c_control_rules_info_rec.segment12_low;
3403 l_control_rules_tbl(l_total_accounts).segment13_low:=c_control_rules_info_rec.segment13_low;
3404 l_control_rules_tbl(l_total_accounts).segment14_low:=c_control_rules_info_rec.segment14_low;
3405 l_control_rules_tbl(l_total_accounts).segment15_low:=c_control_rules_info_rec.segment15_low;
3406 l_control_rules_tbl(l_total_accounts).segment16_low:=c_control_rules_info_rec.segment16_low;
3407 l_control_rules_tbl(l_total_accounts).segment17_low:=c_control_rules_info_rec.segment17_low;
3408 l_control_rules_tbl(l_total_accounts).segment18_low:=c_control_rules_info_rec.segment18_low;
3409 l_control_rules_tbl(l_total_accounts).segment19_low:=c_control_rules_info_rec.segment19_low;
3410 l_control_rules_tbl(l_total_accounts).segment20_low:=c_control_rules_info_rec.segment20_low;
3411 l_control_rules_tbl(l_total_accounts).segment21_low:=c_control_rules_info_rec.segment21_low;
3412 l_control_rules_tbl(l_total_accounts).segment22_low:=c_control_rules_info_rec.segment22_low;
3413 l_control_rules_tbl(l_total_accounts).segment23_low:=c_control_rules_info_rec.segment23_low;
3414 l_control_rules_tbl(l_total_accounts).segment24_low:=c_control_rules_info_rec.segment24_low;
3415 l_control_rules_tbl(l_total_accounts).segment25_low:=c_control_rules_info_rec.segment25_low;
3416 l_control_rules_tbl(l_total_accounts).segment26_low:=c_control_rules_info_rec.segment26_low;
3417 l_control_rules_tbl(l_total_accounts).segment27_low:=c_control_rules_info_rec.segment27_low;
3418 l_control_rules_tbl(l_total_accounts).segment28_low:=c_control_rules_info_rec.segment28_low;
3419 l_control_rules_tbl(l_total_accounts).segment29_low:=c_control_rules_info_rec.segment29_low;
3420 l_control_rules_tbl(l_total_accounts).segment30_low:=c_control_rules_info_rec.segment30_low;
3421 l_control_rules_tbl(l_total_accounts).segment1_high:=c_control_rules_info_rec.segment1_high;
3422 l_control_rules_tbl(l_total_accounts).segment2_high:=c_control_rules_info_rec.segment2_high;
3423 l_control_rules_tbl(l_total_accounts).segment3_high:=c_control_rules_info_rec.segment3_high;
3424 l_control_rules_tbl(l_total_accounts).segment4_high:=c_control_rules_info_rec.segment4_high;
3425 l_control_rules_tbl(l_total_accounts).segment5_high:=c_control_rules_info_rec.segment5_high;
3426 l_control_rules_tbl(l_total_accounts).segment6_high:=c_control_rules_info_rec.segment6_high;
3427 l_control_rules_tbl(l_total_accounts).segment7_high:=c_control_rules_info_rec.segment7_high;
3428 l_control_rules_tbl(l_total_accounts).segment8_high:=c_control_rules_info_rec.segment8_high;
3429 l_control_rules_tbl(l_total_accounts).segment9_high:=c_control_rules_info_rec.segment9_high;
3430 l_control_rules_tbl(l_total_accounts).segment10_high:=c_control_rules_info_rec.segment10_high;
3431 l_control_rules_tbl(l_total_accounts).segment11_high:=c_control_rules_info_rec.segment11_high;
3432 l_control_rules_tbl(l_total_accounts).segment12_high:=c_control_rules_info_rec.segment12_high;
3433 l_control_rules_tbl(l_total_accounts).segment13_high:=c_control_rules_info_rec.segment13_high;
3434 l_control_rules_tbl(l_total_accounts).segment14_high:=c_control_rules_info_rec.segment14_high;
3435 l_control_rules_tbl(l_total_accounts).segment15_high:=c_control_rules_info_rec.segment15_high;
3436 l_control_rules_tbl(l_total_accounts).segment16_high:=c_control_rules_info_rec.segment16_high;
3437 l_control_rules_tbl(l_total_accounts).segment17_high:=c_control_rules_info_rec.segment17_high;
3438 l_control_rules_tbl(l_total_accounts).segment18_high:=c_control_rules_info_rec.segment18_high;
3439 l_control_rules_tbl(l_total_accounts).segment19_high:=c_control_rules_info_rec.segment19_high;
3440 l_control_rules_tbl(l_total_accounts).segment20_high:=c_control_rules_info_rec.segment20_high;
3441 l_control_rules_tbl(l_total_accounts).segment21_high:=c_control_rules_info_rec.segment21_high;
3442 l_control_rules_tbl(l_total_accounts).segment22_high:=c_control_rules_info_rec.segment22_high;
3443 l_control_rules_tbl(l_total_accounts).segment23_high:=c_control_rules_info_rec.segment23_high;
3444 l_control_rules_tbl(l_total_accounts).segment24_high:=c_control_rules_info_rec.segment24_high;
3445 l_control_rules_tbl(l_total_accounts).segment25_high:=c_control_rules_info_rec.segment25_high;
3446 l_control_rules_tbl(l_total_accounts).segment26_high:=c_control_rules_info_rec.segment26_high;
3447 l_control_rules_tbl(l_total_accounts).segment27_high:=c_control_rules_info_rec.segment27_high;
3448 l_control_rules_tbl(l_total_accounts).segment28_high:=c_control_rules_info_rec.segment28_high;
3449 l_control_rules_tbl(l_total_accounts).segment29_high:=c_control_rules_info_rec.segment29_high;
3450 l_control_rules_tbl(l_total_accounts).segment30_high:=c_control_rules_info_rec.segment30_high;
3451
3452 l_total_accounts :=l_total_accounts+1;
3453 END LOOP;
3454
3455 IF l_total_accounts=0 THEN
3456 -- No ranges found - return 'Y'
3457 IF g_debug_mode = 'Y'
3458 THEN
3459 Put_Debug_Msg( l_full_path,'No ranges found, calculationg total amount');
3460 END IF;
3461
3462 OPEN c_total_amount;
3463 FETCH c_total_amount INTO l_total_amount;
3464 CLOSE c_total_amount;
3465
3466 IF g_debug_mode = 'Y'
3467 THEN
3468 Put_Debug_Msg( l_full_path,'Total CC amount is: '||l_total_amount);
3469 END IF;
3470
3471 IF l_group_amount IS NOT NULL AND l_group_amount < NVL(l_total_amount,0) THEN
3472 IF g_debug_mode = 'Y'
3473 THEN
3474 Put_Debug_Msg( l_full_path,'Total amount exceeded : return N');
3475 END IF;
3476 p_result:='N';
3477 RETURN;
3478 END IF;
3479
3480 IF g_debug_mode = 'Y'
3481 THEN
3482 Put_Debug_Msg( l_full_path,'Total amount passed : return Y');
3483 END IF;
3484 p_result:='Y';
3485 RETURN;
3486 END IF;
3487
3488 -- Loop through all CC account lines
3489
3490 IF g_debug_mode = 'Y'
3491 THEN
3492 Put_Debug_Msg( l_full_path,'Loop through cc accounts ');
3493 END IF;
3494
3495 FOR c_cc_account_lines_rec IN c_cc_account_lines LOOP
3496
3497 l_ccid_in_range := FALSE; -- Set flag not in range
3498
3499 -- increase total amount
3500 l_total_amount:= NVL(l_total_amount,0) + NVL(c_cc_account_lines_rec.amount,0);
3501
3502 IF g_debug_mode = 'Y'
3503 THEN
3504 Put_Debug_Msg( l_full_path,' New line found: '||c_cc_account_lines_rec.line_id
3505 ||' ccid: '||c_cc_account_lines_rec.ccid
3506 ||' amount: '||c_cc_account_lines_rec.amount
3507 ||' new total: '||l_total_amount);
3508 END IF;
3509
3510 FOR c_ccid_info_rec IN c_ccid_info(c_cc_account_lines_rec.ccid) LOOP
3511
3512 IF g_debug_mode = 'Y'
3513 THEN
3514 Put_Debug_Msg( l_full_path,' Loop through ranges ');
3515 END IF;
3516
3517 -- Loop through ranges
3518 FOR l_cur_account IN 0..(l_total_accounts-1) LOOP
3519
3520 -- Check if CCID in range
3521
3522 IF
3523 Check_Segment( c_ccid_info_rec.segment1,
3524 l_control_rules_tbl(l_cur_account).segment1_low,
3525 l_control_rules_tbl(l_cur_account).segment1_high)
3526 AND
3527 Check_Segment( c_ccid_info_rec.segment2,
3528 l_control_rules_tbl(l_cur_account).segment2_low,
3529 l_control_rules_tbl(l_cur_account).segment2_high)
3530
3531 AND
3532 Check_Segment( c_ccid_info_rec.segment3,
3533 l_control_rules_tbl(l_cur_account).segment3_low,
3534 l_control_rules_tbl(l_cur_account).segment3_high)
3535 AND
3536 Check_Segment( c_ccid_info_rec.segment4,
3537 l_control_rules_tbl(l_cur_account).segment4_low,
3538 l_control_rules_tbl(l_cur_account).segment4_high)
3539 AND
3540 Check_Segment( c_ccid_info_rec.segment5,
3541 l_control_rules_tbl(l_cur_account).segment5_low,
3542 l_control_rules_tbl(l_cur_account).segment5_high)
3543 AND
3544 Check_Segment( c_ccid_info_rec.segment6,
3545 l_control_rules_tbl(l_cur_account).segment6_low,
3546 l_control_rules_tbl(l_cur_account).segment6_high)
3547 AND
3548 Check_Segment( c_ccid_info_rec.segment7,
3549 l_control_rules_tbl(l_cur_account).segment7_low,
3550 l_control_rules_tbl(l_cur_account).segment7_high)
3551 AND
3552 Check_Segment( c_ccid_info_rec.segment8,
3553 l_control_rules_tbl(l_cur_account).segment8_low,
3554 l_control_rules_tbl(l_cur_account).segment8_high)
3555 AND
3556 Check_Segment( c_ccid_info_rec.segment9,
3557 l_control_rules_tbl(l_cur_account).segment9_low,
3558 l_control_rules_tbl(l_cur_account).segment9_high)
3559 AND
3560 Check_Segment( c_ccid_info_rec.segment10,
3561 l_control_rules_tbl(l_cur_account).segment10_low,
3562 l_control_rules_tbl(l_cur_account).segment10_high)
3563 AND
3564 Check_Segment( c_ccid_info_rec.segment11,
3565 l_control_rules_tbl(l_cur_account).segment11_low,
3566 l_control_rules_tbl(l_cur_account).segment11_high)
3567 AND
3568 Check_Segment( c_ccid_info_rec.segment12,
3569 l_control_rules_tbl(l_cur_account).segment12_low,
3570 l_control_rules_tbl(l_cur_account).segment12_high)
3571 AND
3572 Check_Segment( c_ccid_info_rec.segment13,
3573 l_control_rules_tbl(l_cur_account).segment13_low,
3574 l_control_rules_tbl(l_cur_account).segment13_high)
3575 AND
3576 Check_Segment( c_ccid_info_rec.segment14,
3577 l_control_rules_tbl(l_cur_account).segment14_low,
3578 l_control_rules_tbl(l_cur_account).segment14_high)
3579 AND
3580 Check_Segment( c_ccid_info_rec.segment15,
3581 l_control_rules_tbl(l_cur_account).segment15_low,
3582 l_control_rules_tbl(l_cur_account).segment15_high)
3583 AND
3584 Check_Segment( c_ccid_info_rec.segment16,
3585 l_control_rules_tbl(l_cur_account).segment16_low,
3586 l_control_rules_tbl(l_cur_account).segment16_high)
3587 AND
3588 Check_Segment( c_ccid_info_rec.segment17,
3589 l_control_rules_tbl(l_cur_account).segment17_low,
3590 l_control_rules_tbl(l_cur_account).segment17_high)
3591 AND
3592 Check_Segment( c_ccid_info_rec.segment18,
3593 l_control_rules_tbl(l_cur_account).segment18_low,
3594 l_control_rules_tbl(l_cur_account).segment18_high)
3595 AND
3596 Check_Segment( c_ccid_info_rec.segment19,
3597 l_control_rules_tbl(l_cur_account).segment19_low,
3598 l_control_rules_tbl(l_cur_account).segment19_high)
3599 AND
3600 Check_Segment( c_ccid_info_rec.segment20,
3601 l_control_rules_tbl(l_cur_account).segment20_low,
3602 l_control_rules_tbl(l_cur_account).segment20_high)
3603 AND
3604 Check_Segment( c_ccid_info_rec.segment21,
3605 l_control_rules_tbl(l_cur_account).segment21_low,
3606 l_control_rules_tbl(l_cur_account).segment21_high)
3607 AND
3608 Check_Segment( c_ccid_info_rec.segment22,
3609 l_control_rules_tbl(l_cur_account).segment22_low,
3610 l_control_rules_tbl(l_cur_account).segment22_high)
3611 AND
3612 Check_Segment( c_ccid_info_rec.segment23,
3613 l_control_rules_tbl(l_cur_account).segment23_low,
3614 l_control_rules_tbl(l_cur_account).segment23_high)
3615 AND
3616 Check_Segment( c_ccid_info_rec.segment24,
3617 l_control_rules_tbl(l_cur_account).segment24_low,
3618 l_control_rules_tbl(l_cur_account).segment24_high)
3619 AND
3620 Check_Segment( c_ccid_info_rec.segment25,
3621 l_control_rules_tbl(l_cur_account).segment25_low,
3622 l_control_rules_tbl(l_cur_account).segment25_high)
3623 AND
3624 Check_Segment( c_ccid_info_rec.segment26,
3625 l_control_rules_tbl(l_cur_account).segment26_low,
3626 l_control_rules_tbl(l_cur_account).segment26_high)
3627 AND
3628 Check_Segment( c_ccid_info_rec.segment27,
3629 l_control_rules_tbl(l_cur_account).segment27_low,
3630 l_control_rules_tbl(l_cur_account).segment27_high)
3631 AND
3632 Check_Segment( c_ccid_info_rec.segment28,
3633 l_control_rules_tbl(l_cur_account).segment28_low,
3634 l_control_rules_tbl(l_cur_account).segment28_high)
3635 AND
3636 Check_Segment( c_ccid_info_rec.segment29,
3637 l_control_rules_tbl(l_cur_account).segment29_low,
3638 l_control_rules_tbl(l_cur_account).segment29_high)
3639 AND
3640 Check_Segment( c_ccid_info_rec.segment30,
3641 l_control_rules_tbl(l_cur_account).segment30_low,
3642 l_control_rules_tbl(l_cur_account).segment30_high)
3643 THEN
3644
3645 IF g_debug_mode = 'Y'
3646 THEN
3647 Put_Debug_Msg( l_full_path,' Line in the range id: '||l_control_rules_tbl(l_cur_account).control_rule_id);
3648 Put_Debug_Msg( l_full_path,' type:'||l_control_rules_tbl(l_cur_account).rule_type_code);
3649 Put_Debug_Msg( l_full_path,' limit before: '||l_control_rules_tbl(l_cur_account).amount_limit);
3650 END IF;
3651
3652 -- Check if range type in exclude or include
3653 IF l_control_rules_tbl(l_cur_account).rule_type_code = 'EXCLUDE' THEN
3654 -- Exclude: Return N
3655 IF g_debug_mode = 'Y'
3656 THEN
3657 Put_Debug_Msg( l_full_path,' Range type exclude, return N');
3658 END IF;
3659 return;
3660 END IF;
3661
3662 -- Include: Decrease the range amount
3663 l_control_rules_tbl(l_cur_account).amount_limit:=NVL(l_control_rules_tbl(l_cur_account).amount_limit,0) - NVL(c_cc_account_lines_rec.amount,0);
3664
3665 -- Check if amount is still not negative
3666 IF l_control_rules_tbl(l_cur_account).amount_limit <0 THEN
3667 IF g_debug_mode = 'Y'
3668 THEN
3669 Put_Debug_Msg( l_full_path,' Amount exceeded, return N');
3670 END IF;
3671 --Maxmum limit for line exeeded
3672 return;
3673 END IF;
3674
3675 -- Set flag in range
3676 l_ccid_in_range := TRUE;
3677
3678 END IF;
3679
3680 END LOOP; -- End range loop
3681
3682 END LOOP; --end CCID LOOP (only one value should be returne by the cursor)
3683
3684 -- Check if CCID was in the range
3685 IF NOT l_ccid_in_range THEN
3686 IF g_debug_mode = 'Y'
3687 THEN
3688 Put_Debug_Msg( l_full_path,' Line not in the range, return N');
3689 END IF;
3690 -- Not in the range - Return 'N'
3691 return;
3692 END IF;
3693
3694 END LOOP; --End account lines loop
3695
3696
3697 -- Check if group has total amount
3698 -- Check document total amount
3699
3700 IF g_debug_mode = 'Y'
3701 THEN
3702 Put_Debug_Msg( l_full_path,'Checking total amount');
3703 END IF;
3704 IF l_group_amount IS NOT NULL AND l_group_amount < l_total_amount THEN
3705 IF g_debug_mode = 'Y'
3706 THEN
3707 Put_Debug_Msg( l_full_path,'Total amount exceeded, return N');
3708 END IF;
3709 -- Amount exceeded - return 'N'
3710 return;
3711 END IF;
3712
3713 p_result:='Y'; --Has authority
3714
3715 IF g_debug_mode = 'Y'
3716 THEN
3717 Put_Debug_Msg( l_full_path,l_api_name||' Successfully completed, return Y');
3718 END IF;
3719
3720 EXCEPTION
3721 WHEN FND_API.G_EXC_ERROR THEN
3722 x_return_status := FND_API.G_RET_STS_ERROR;
3723 --Bug 3199488
3724 IF ( g_excep_level >= g_debug_level ) THEN
3725 FND_LOG.STRING (g_excep_level,l_full_path,'FND_API.G_EXC_ERROR Exception Raised');
3726 END IF;
3727 --Bug 3199488
3728
3729 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3730 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3731 --Bug 3199488
3732 IF ( g_excep_level >= g_debug_level ) THEN
3733 FND_LOG.STRING (g_excep_level,l_full_path,'FND_API.G_EXC_UNEXPECTED_ERROR Exception Raised');
3734 END IF;
3735 --Bug 3199488
3736
3737 WHEN OTHERS THEN
3738 Put_Debug_Msg( l_full_path,l_api_name||' raised unhandled exception');
3739
3740 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3741
3742 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
3743 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
3744 END IF;
3745 -- Bug 3199488
3746 IF ( g_unexp_level >= g_debug_level ) THEN
3747 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
3748 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
3749 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
3750 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
3751 END IF;
3752 -- Bug 3199488
3753
3754 END Check_Authority;
3755
3756 PROCEDURE message_token(
3757 tokname IN VARCHAR2,
3758 tokval IN VARCHAR2
3759 ) IS
3760
3761 BEGIN
3762
3763 IGC_MSGS_PKG.message_token (p_tokname => tokname,
3764 p_tokval => tokval);
3765
3766 END message_token;
3767
3768
3769 /****************************************************************************/
3770
3771 -- Sets the Message Stack
3772
3773 PROCEDURE add_message(
3774 appname IN VARCHAR2,
3775 msgname IN VARCHAR2
3776 ) IS
3777
3778 i BINARY_INTEGER;
3779
3780 l_full_path VARCHAR2(500) := g_path || 'add_message';
3781 BEGIN
3782 IGC_MSGS_PKG.add_message (p_appname => appname,
3783 p_msgname => msgname);
3784 IF g_debug_mode = 'Y'
3785 THEN
3786 Put_Debug_Msg( l_full_path,'Raising an execution exception: '||fnd_msg_pub.get(1,FND_API.G_FALSE));
3787 END IF;
3788
3789 END add_message;
3790
3791 PROCEDURE Put_Debug_Msg (
3792 p_path IN VARCHAR2,
3793 p_debug_msg IN VARCHAR2
3794 ) IS
3795
3796 -- Constants :
3797
3798 /*l_return_status VARCHAR2(1);
3799 l_api_name CONSTANT VARCHAR2(30) := 'Put_Debug_Msg';*/
3800
3801 BEGIN
3802
3803 -- IF g_debug_init IS NULL THEN --Need to init debug
3804 -- FND_API.G_TRUE
3805 -- IF wf_engine.GetItemAttrText(g_itemtype,g_itemkey,'DEBUG_MODE') = 'T'
3806 -- THEN
3807 -- IGC_MSGS_PKG.g_debug_mode := TRUE;
3808 -- ELSE
3809 -- IGC_MSGS_PKG.g_debug_mode := FALSE;
3810 -- END IF;
3811 -- g_debug_init :='Y';
3812 -- END IF;
3813
3814 /*IGC_MSGS_PKG.Put_Debug_Msg ( p_debug_message => p_debug_msg,
3815 p_profile_log_name => g_profile_name,
3816 p_prod => NULL,
3817 p_sub_comp => NULL,
3818 p_filename_val => g_itemtype||'_'||g_itemkey||'.dbg',
3819 x_Return_Status => l_return_status
3820 );
3821 IF (l_Return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
3822 raise FND_API.G_EXC_ERROR;
3823 END IF;*/
3824
3825 --Bug 3199488
3826 IF(g_state_level >= g_debug_level) THEN
3827 FND_LOG.STRING(g_state_level, p_path, p_debug_msg);
3828 END IF;
3829 -- Bug 3199488
3830
3831 RETURN;
3832 -- --------------------------------------------------------------------
3833 -- Exception handler section for the Put_Debug_Msg procedure.
3834 -- --------------------------------------------------------------------
3835 EXCEPTION
3836
3837 /*WHEN FND_API.G_EXC_ERROR THEN
3838 RETURN;*/
3839
3840 WHEN OTHERS THEN
3841 /*IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
3842 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
3843 END IF;*/
3844 NULL;
3845 RETURN;
3846
3847 END Put_Debug_Msg;
3848
3849
3850
3851 PROCEDURE Generate_Message
3852 IS
3853 l_cur NUMBER;
3854 l_msg_count NUMBER ;
3855 l_msg_data VARCHAR2(32000) ;
3856
3857 l_full_path VARCHAR2(500) := g_path || 'Generate_Message';
3858 BEGIN
3859 IF g_debug_mode = 'Y'
3860 THEN
3861 Put_Debug_Msg( l_full_path,'Error during the execution ');
3862 END IF;
3863
3864 FND_MSG_PUB.Count_And_Get ( p_count => l_msg_count,
3865 p_data => l_msg_data );
3866
3867 IF l_msg_count >0 THEN
3868 g_error_text :='';
3869
3870 FOR l_cur IN 1..l_msg_count LOOP
3871 g_error_text := g_error_text||' Mes No'||l_cur||' '||FND_MSG_PUB.GET(l_cur,FND_API.G_FALSE);
3872 --Bug 3199488
3873 IF(g_excep_level >= g_debug_level) THEN
3874 FND_LOG.STRING(g_excep_level, l_full_path,g_error_text);
3875 END IF;
3876 --Bug 3199488
3877 END LOOP;
3878 ELSE
3879 g_error_text :='Error stack has no data';
3880 END IF;
3881 wf_engine.SetItemAttrText (g_itemtype,g_itemkey,'ERROR_TEXT',g_error_text);
3882
3883 IF g_debug_mode = 'Y'
3884 THEN
3885 Put_Debug_Msg( l_full_path,'Error text is '||g_error_text);
3886 END IF;
3887
3888 END Generate_Message;
3889
3890 FUNCTION Check_Segment(
3891 seg VARCHAR2,
3892 seg_low VARCHAR2,
3893 seg_high VARCHAR2)
3894 RETURN BOOLEAN IS
3895
3896 BEGIN
3897 IF (seg IS NULL) OR (seg BETWEEN seg_low AND seg_high) THEN
3898 return TRUE;
3899 END IF;
3900 return FALSE;
3901 END;
3902
3903 /* this procedure is used when we need to reject cancelled CC,
3904 it returns approval status before cancellation*/
3905 FUNCTION Get_Last_App_status
3906 RETURN VARCHAR2 IS
3907 CURSOR c_app_status IS
3908 SELECT cc_action_apprvl_status
3909 FROM igc_cc_actions
3910 WHERE (cc_header_id, cc_action_num)
3911 IN ( SELECT cc_header_id,max(cc_action_num)
3912 FROM igc_cc_actions
3913 WHERE cc_action_state ='PR'
3914 AND cc_header_id = g_cc_header_id
3915 GROUP BY cc_header_id) ;
3916
3917 l_app_status igc_cc_actions.cc_action_apprvl_status%TYPE;
3918
3919 BEGIN
3920
3921 OPEN c_app_status;
3922 FETCH c_app_status INTO l_app_status;
3923
3924 IF c_app_status%NOTFOUND THEN
3925
3926 CLOSE c_app_status;
3927
3928 message_token ('CC_NUMBER', g_cc_number);
3929 add_message ('IGC', 'IGC_PREV_STATE_NOT_FOUND');
3930 RAISE FND_API.G_EXC_ERROR;
3931
3932 END IF;
3933
3934 CLOSE c_app_status;
3935
3936 RETURN l_app_status;
3937
3938 END Get_Last_App_status;
3939
3940 /* Procedure Checks supplier */
3941
3942 PROCEDURE Check_Supplier(
3943 x_return_status OUT NOCOPY VARCHAR2)
3944 IS
3945 l_api_name CONSTANT VARCHAR2(30) := 'Check_Supplier' ;
3946 l_vendor_flag VARCHAR2(1);
3947
3948 CURSOR c_sys_par IS
3949 SELECT enforce_vendor_hold_flag
3950 FROM igc_cc_system_options_all /*igc_cc_system_parameters*/
3951 WHERE org_id = g_org_id;
3952
3953
3954 CURSOR c_cc_par IS
3955 SELECT hold_flag
3956 FROM po_vendors
3957 WHERE vendor_id =
3958 (SELECT vendor_id
3959 FROM igc_cc_headers
3960 WHERE cc_header_id = g_cc_header_id);
3961
3962 l_full_path VARCHAR2(500):= g_path || 'Check_Supplier';
3963 BEGIN
3964
3965 x_return_status := FND_API.G_RET_STS_SUCCESS ;
3966 IF g_debug_mode = 'Y'
3967 THEN
3968 Put_Debug_Msg( l_full_path,l_api_name||' started');
3969 END IF;
3970
3971 OPEN c_sys_par;
3972 FETCH c_sys_par INTO l_vendor_flag;
3973 CLOSE c_sys_par;
3974
3975 IF l_vendor_flag ='Y' THEN
3976
3977 IF g_debug_mode = 'Y'
3978 THEN
3979 Put_Debug_Msg( l_full_path,'Setup option is Y, checking CC info..');
3980 END IF;
3981
3982 l_vendor_flag :=NULL;
3983
3984 OPEN c_cc_par;
3985 FETCH c_cc_par INTO l_vendor_flag;
3986 CLOSE c_cc_par;
3987
3988 IF NVL(l_vendor_flag,'N') ='Y' AND g_cc_state IN ('PR','CM') THEN
3989
3990 add_message ('IGC', 'IGC_CC_SUPPLIER_ON_HOLD_NO_APP');
3991 RAISE FND_API.G_EXC_ERROR;
3992
3993 END IF;
3994
3995 IF g_debug_mode = 'Y'
3996 THEN
3997 Put_Debug_Msg( l_full_path,'Done');
3998 END IF;
3999
4000 END IF;
4001
4002 IF g_debug_mode = 'Y'
4003 THEN
4004 Put_Debug_Msg( l_full_path,l_api_name||' Successfully completed');
4005 END IF;
4006
4007 EXCEPTION
4008 WHEN FND_API.G_EXC_ERROR THEN
4009 x_return_status := FND_API.G_RET_STS_ERROR;
4010 --Bug 3199488
4011 IF ( g_excep_level >= g_debug_level ) THEN
4012 FND_LOG.STRING (g_excep_level,l_full_path,'FND_API.G_EXC_ERROR Exception Raised');
4013 END IF;
4014 --Bug 3199488
4015
4016 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4017 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4018 --Bug 3199488
4019 IF ( g_excep_level >= g_debug_level ) THEN
4020 FND_LOG.STRING (g_excep_level,l_full_path,'FND_API.G_EXC_UNEXPECTED_ERROR Exception Raised');
4021 END IF;
4022 --Bug 3199488
4023
4024 WHEN OTHERS THEN
4025 Put_Debug_Msg( l_full_path,l_api_name||' raised unhandled exception');
4026
4027 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4028
4029 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
4030 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
4031 END IF;
4032 -- Bug 3199488
4033 IF ( g_unexp_level >= g_debug_level ) THEN
4034 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
4035 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
4036 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
4037 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
4038 END IF;
4039 -- Bug 3199488
4040
4041 END Check_Supplier;
4042
4043
4044
4045
4046 END IGC_CC_APPROVAL_WF_PKG;
4047
4048