[Home] [Help]
PACKAGE BODY: APPS.IGC_CC_APPROVAL_PROCESS
Source
1 PACKAGE BODY IGC_CC_APPROVAL_PROCESS AS
2 /* $Header: IGCCAPPB.pls 120.8.12020000.1 2013/02/15 20:46:57 appldev ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IGC_CC_APPROVAL_PROCESS';
5 g_profile_name VARCHAR2(255) := 'IGC_DEBUG_LOG_DIRECTORY';
6
7 --l_debug_mode VARCHAR2(1) := NVL(FND_PROFILE.VALUE('IGC_DEBUG_ENABLED'),'N');
8 g_debug_mode VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
9
10 --Variables for ATG Central logging
11 g_debug_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
12 g_state_level NUMBER := FND_LOG.LEVEL_STATEMENT;
13 g_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
14 g_event_level NUMBER := FND_LOG.LEVEL_EVENT;
15 g_excep_level NUMBER := FND_LOG.LEVEL_EXCEPTION;
16 g_error_level NUMBER := FND_LOG.LEVEL_ERROR;
17 g_unexp_level NUMBER := FND_LOG.LEVEL_UNEXPECTED;
18 g_path VARCHAR2(255) := 'IGC.PLSQL.IGCCAPPB.IGC_CC_APPROVAL_PROCESS.';
19
20
21 PROCEDURE Put_Debug_Msg (
22 p_path IN VARCHAR2,
23 p_debug_msg IN VARCHAR2
24 );
25
26
27 PROCEDURE change_document_status
28 ( p_itemtype IN VARCHAR2
29 , p_itemkey IN VARCHAR2
30 , p_cc_header_id IN NUMBER
31 , p_cc_state IN VARCHAR2
32 , p_cc_type IN VARCHAR2
33 , p_cc_preparer_id IN NUMBER
34 , p_cc_owner_id IN NUMBER
35 , p_cc_current_owner IN NUMBER
36 , p_cc_apprvl_status IN VARCHAR2
37 , p_cc_encumb_status IN VARCHAR2
38 , p_cc_action_request IN VARCHAR2
39 , p_error_code OUT NOCOPY NUMBER
40 ) ;
41 PROCEDURE Create_Action_History
42 ( p_cc_header_id IN IGC_CC_HEADERS.cc_header_id%TYPE
43 , p_cc_version_num IN IGC_CC_HEADERS.cc_version_num%TYPE
44 , p_cc_state IN IGC_CC_HEADERS.cc_state%TYPE
45 , p_cc_old_cc_state IN IGC_CC_HEADERS.cc_state%TYPE
46 , p_cc_ctrl_status IN IGC_CC_HEADERS.cc_ctrl_status%TYPE
47 , p_cc_apprvl_status IN IGC_CC_HEADERS.cc_apprvl_status%TYPE
48 , p_cc_notes IN IGC_CC_ACTIONS.cc_action_notes%TYPE
49 , p_action_requested IN VARCHAR2
50 , p_error_code OUT NOCOPY NUMBER
51 ) ;
52
53 PROCEDURE message_token(
54 tokname IN VARCHAR2,
55 tokval IN VARCHAR2
56 ) IS
57
58 BEGIN
59
60 IGC_MSGS_PKG.message_token (p_tokname => tokname,
61 p_tokval => tokval);
62
63 END message_token;
64
65
66 /****************************************************************************/
67
68 -- Sets the Message Stack
69
70 PROCEDURE add_message(
71 appname IN VARCHAR2,
72 msgname IN VARCHAR2
73 ) IS
74
75 i BINARY_INTEGER;
76 l_full_path VARCHAR2(255);
77 BEGIN
78
79 l_full_path:= g_path || 'add_message';
80
81 IGC_MSGS_PKG.add_message (p_appname => appname,
82 p_msgname => msgname);
83 IF (g_debug_mode = 'Y') THEN
84 Put_Debug_Msg(l_full_path, 'Raising an execution exception: '||fnd_msg_pub.get(1,FND_API.G_FALSE));
85 END IF;
86
87 END add_message;
88
89 PROCEDURE Generate_Message
90 IS
91 l_cur NUMBER;
92 l_msg_count NUMBER ;
93 l_msg_data VARCHAR2(32000) ;
94 l_full_path VARCHAR2(255);
95
96 BEGIN
97
98 l_full_path:= g_path || 'Generate_Message';
99
100 IF (g_debug_mode = 'Y') THEN
101 Put_Debug_Msg(l_full_path, 'Error during the execution ');
102 END IF;
103
104 FND_MSG_PUB.Count_And_Get ( p_count => l_msg_count,
105 p_data => l_msg_data );
106
107 IF l_msg_count >0 THEN
108 l_msg_data :='';
109
110 FOR l_cur IN 1..l_msg_count LOOP
111 -- l_msg_data :=l_msg_data||' Mes No'||l_cur||' '||FND_MSG_PUB.GET(l_cur,FND_API.G_FALSE);
112 l_msg_data :=l_msg_data||' '||l_cur||' '||FND_MSG_PUB.GET(l_cur,FND_API.G_FALSE);
113 IF(g_error_level >= g_debug_level) THEN
114 FND_LOG.STRING(g_error_level, l_full_path, l_msg_data);
115 END IF;
116 END LOOP;
117 ELSE
118 IF(g_error_level >= g_debug_level) THEN
119 FND_LOG.STRING(g_error_level, l_full_path, l_msg_data);
120 END IF;
121 l_msg_data :='Error stack has no data';
122 END IF;
123
124 IF (g_debug_mode = 'Y') THEN
125 Put_Debug_Msg(l_full_path, 'Error text is '||l_msg_data);
126 END IF;
127
128 END Generate_Message;
129
130
131 PROCEDURE Preparer_Can_Approve
132 ( p_api_version IN NUMBER ,
133 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
134 p_commit IN VARCHAR2 := FND_API.G_FALSE,
135 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
136 x_return_status OUT NOCOPY VARCHAR2,
137 x_msg_count OUT NOCOPY NUMBER ,
138 x_msg_data OUT NOCOPY VARCHAR2,
139 p_org_id IN NUMBER ,
140 p_cc_state IN VARCHAR2,
141 p_cc_type IN VARCHAR2,
142 x_result OUT NOCOPY VARCHAR2
143 ) IS
144
145 l_api_name CONSTANT VARCHAR2(30) := 'Preparer_Can_Approve';
146 l_api_version CONSTANT NUMBER := 1.0;
147
148 l_can_approve VARCHAR2(2);
149
150 CURSOR c_setup_opt IS
151 SELECT UPPER(icrc.cc_can_prpr_apprv_flag)
152 FROM IGC_CC_ROUTING_CTRLS icrc
153 WHERE icrc.org_id = p_org_id
154 AND icrc.cc_state = p_cc_state
155 AND icrc.cc_type = p_cc_type;
156
157 CURSOR c_meaning_state IS
158 SELECT meaning
159 FROM fnd_lookups
160 WHERE lookup_code = p_cc_state
161 AND lookup_type = 'IGC_CC_STATE';
162
163 CURSOR c_meaning_type IS
164 SELECT meaning
165 FROM fnd_lookups
166 WHERE lookup_code = p_cc_type
167 AND lookup_type = 'IGC_CC_TYPE';
168
169 CURSOR c_org_name IS
170 SELECT name
171 FROM hr_organization_units
172 WHERE organization_id = p_org_id;
173
174 l_value VARCHAR2(255);
175 l_full_path VARCHAR2(255);
176
177 BEGIN
178
179 l_full_path:= g_path || 'Preparer_Can_Approve';
180
181 SAVEPOINT Preparer_Can_Approve;
182 -- Standard call to check for call compatibility.
183
184 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
185 p_api_version ,
186 l_api_name ,
187 G_PKG_NAME )
188 THEN
189 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
190 END IF;
191
192 -- Initialize message list if p_init_msg_list is set to TRUE.
193 IF FND_API.to_Boolean( p_init_msg_list ) THEN
194 FND_MSG_PUB.initialize;
195 END IF;
196
197 -- Initialize API return status to success
198 x_return_status := FND_API.G_RET_STS_SUCCESS;
199
200 -- API body
201
202 OPEN c_setup_opt;
203 FETCH c_setup_opt INTO l_can_approve;
204
205 IF c_setup_opt%NOTFOUND THEN
206
207 CLOSE c_setup_opt;
208
209 FND_MESSAGE.SET_NAME ('IGC', 'IGC_CC_OPTION_NOT_FOUND');
210
211 OPEN c_meaning_type ;
212 FETCH c_meaning_type INTO l_value;
213 CLOSE c_meaning_type;
214 FND_MESSAGE.SET_TOKEN ('CC_TYPE',l_value);
215
216 OPEN c_meaning_state ;
217 FETCH c_meaning_state INTO l_value;
218 CLOSE c_meaning_state;
219 FND_MESSAGE.SET_TOKEN ('CC_STATE',l_value);
220
221 OPEN c_org_name ;
222 FETCH c_org_name INTO l_value;
223 CLOSE c_org_name;
224
225 FND_MESSAGE.SET_TOKEN ('ORG_ID',l_value);
226
227 FND_MSG_PUB.ADD;
228 raise FND_API.G_EXC_ERROR;
229 END IF;
230
231 CLOSE c_setup_opt;
232
233 IF l_can_approve = 'Y' THEN
234 x_result := FND_API.G_TRUE;
235 ELSE
236 x_result := FND_API.G_FALSE;
237 END IF;
238
239 IF FND_API.To_Boolean( p_commit ) THEN
240 COMMIT WORK;
241 END IF;
242
243 -- Standard call to get message count and if count is 1, get message info.
244 FND_MSG_PUB.Count_And_Get
245 ( p_count => x_msg_count ,
246 p_data => x_msg_data
247 );
248 EXCEPTION
249 WHEN FND_API.G_EXC_ERROR THEN
250 ROLLBACK to Preparer_Can_Approve;
251
252 x_return_status := FND_API.G_RET_STS_ERROR;
253
254 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
255 p_data => x_msg_data);
256 IF (g_excep_level >= g_debug_level ) THEN
257 FND_LOG.STRING (g_excep_level,l_full_path,'FND_API.G_EXC_ERROR Exception Raised');
258 END IF;
259 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
260 ROLLBACK to Preparer_Can_Approve;
261
262 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
263
264 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
265 p_data => x_msg_data);
266 IF (g_excep_level >= g_debug_level ) THEN
267 FND_LOG.STRING (g_excep_level,l_full_path,'FND_API.G_EXC_UNEXPECTED_ERROR Exception Raised');
268 END IF;
269 WHEN OTHERS THEN
270 ROLLBACK to Preparer_Can_Approve;
271
272 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
273
274 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
275 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
276 l_api_name);
277 END IF;
278 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
279 p_data => x_msg_data);
280 IF ( g_unexp_level >= g_debug_level ) THEN
281 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
282 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
283 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
284 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
285 END IF;
286
287 END Preparer_Can_Approve;
288
289
290 PROCEDURE Approved_By_Preparer
291 ( p_api_version IN NUMBER
292 , p_init_msg_list IN VARCHAR2
293 , p_commit IN VARCHAR2
294 , p_validation_level IN NUMBER
295 , p_return_status OUT NOCOPY VARCHAR2
296 , p_msg_count OUT NOCOPY NUMBER
297 , p_msg_data OUT NOCOPY VARCHAR2
298 , p_cc_header_id IN NUMBER
299 , p_org_id IN NUMBER
300 , p_sob_id IN NUMBER
301 , p_cc_state IN VARCHAR2
302 , p_cc_type IN VARCHAR2
303 , p_cc_preparer_id IN NUMBER
304 , p_cc_owner_id IN NUMBER
305 , p_cc_current_owner IN NUMBER
306 , p_cc_apprvl_status IN VARCHAR2
307 , p_cc_encumb_status IN VARCHAR2
308 , p_cc_ctrl_status IN VARCHAR2
309 , p_cc_version_number IN NUMBER
310 , p_cc_notes IN VARCHAR2
311 , p_acct_date IN DATE
312 ) IS
313
314 l_cc_header_id NUMBER := p_cc_header_id;
315 l_cc_state VARCHAR2(2) := p_cc_state;
316 l_cc_old_cc_state VARCHAR2(2) := p_cc_state;
317 l_cc_type VARCHAR2(2) := p_cc_type;
318 l_cc_preparer_id NUMBER := p_cc_preparer_id;
319 l_cc_owner_id NUMBER := p_cc_owner_id;
320 l_cc_current_owner NUMBER := p_cc_current_owner;
321 l_cc_apprvl_status VARCHAR2(2) := p_cc_apprvl_status;
322 l_cc_encumb_status VARCHAR2(2) := p_cc_encumb_status;
323 l_cc_ctrl_status VARCHAR2(2) := p_cc_ctrl_status;
324 l_cc_version_number NUMBER := p_cc_version_number;
325 l_cc_notes VARCHAR2(240) := p_cc_notes;
326 l_cc_org_id NUMBER := p_org_id;
327 l_cc_sob_id NUMBER := p_sob_id;
328
329 l_cc_Action_requested VARCHAR2(240) := 'APPROVE';
330 l_encumbrance_on VARCHAR2(1);
331 l_error_status NUMBER := 0;
332 l_return_status VARCHAR2(1);
333 l_bc_status VARCHAR2(1);
334 l_error_code NUMBER := 0;
335 l_mode VARCHAR2(1);
336 l_status_flag VARCHAR2(1);
337 l_msg_data VARCHAR2(2000);
338 l_msg_count NUMBER := 0;
339 l_msg VARCHAR2(2000);
340 l_count NUMBER := 0;
341
342 l_api_name CONSTANT VARCHAR2(30) := 'Approved_By_Preparer';
343 l_api_version CONSTANT NUMBER := 1.0;
344 l_full_path VARCHAR2(255);
345
346 BEGIN
347
348 l_full_path:= g_path || 'Approved_By_Preparer';
349 -- check if the budgetary control is ON for a given cc state
350 SAVEPOINT Approved_By_Preparer;
351
352 -- Standard call to check for call compatibility.
353 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
354 p_api_version ,
355 l_api_name ,
356 G_PKG_NAME )
357 THEN
358 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
359 END IF;
360
361 -- Initialize message list if p_init_msg_list is set to TRUE.
362 IF FND_API.to_Boolean( p_init_msg_list ) THEN
363 FND_MSG_PUB.initialize;
364 END IF;
365
366 -- Initialize API return status to success
367 p_return_status := FND_API.G_RET_STS_SUCCESS;
368
369
370 -- IF (upper(fnd_profile.value('IGC_DEBUG_ENABLED')) ='Y') THEN
371 -- IGC_MSGS_PKG.g_debug_mode := TRUE;
372 -- ELSE
373 -- IGC_MSGS_PKG.g_debug_mode := FALSE;
374 -- END IF;
375
376 IF (g_debug_mode = 'Y') THEN
377 Put_Debug_Msg(l_full_path, '**************************************************************************');
378 Put_Debug_Msg(l_full_path, 'Procedure '||l_api_name||' called , Date '||to_char(sysdate,'DD-MON-YY MI:SS'));
379 Put_Debug_Msg(l_full_path, '**************************************************************************');
380
381 Put_Debug_Msg(l_full_path, ' CCHeaderID '||p_cc_header_id
382 ||' OrgID '|| p_org_id
383 ||' SOBID '|| p_sob_id
384 ||' State '|| p_cc_state
385 ||' Type '|| p_cc_type
386 ||' Preparer '|| p_cc_preparer_id
387 ||' Owner '|| p_cc_owner_id
388 ||' CurOwner '|| p_cc_current_owner
389 ||' ApprStatus '|| p_cc_apprvl_status
390 ||' EncStatus '|| p_cc_encumb_status
391 ||' CtrlStat '|| p_cc_ctrl_status
392 ||' Vers '|| p_cc_version_number
393 ||' Notes '|| p_cc_notes );
394
395
396 Put_Debug_Msg(l_full_path, 'Checking budgetary control');
397 END IF;
398
399 IGC_CC_BUDGETARY_CTRL_PKG.Check_Budgetary_Ctrl_On
400 ( 1.0
401 , FND_API.G_FALSE
402 , FND_API.G_VALID_LEVEL_NONE
403 , l_return_status
404 , l_msg_count
405 , l_msg_data
406 , l_cc_org_id
407 , l_cc_sob_id
408 , l_cc_state
409 , l_encumbrance_on
410 ) ;
411
412 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
413 raise FND_API.G_EXC_ERROR;
414 END IF;
415
416 -- if budgetary control is ON then
417 -- attempt to reserve the funds.
418
419 IF l_encumbrance_on = FND_API.G_TRUE THEN
420
421 global_budgetary_control_on := TRUE;
422
423 -- attempt to reserve funds for the CC. For successful execution of
424 -- this procedure, set the org id for the current session.
425
426
427 IF (g_debug_mode = 'Y') THEN
428 Put_Debug_Msg(l_full_path, 'Reserving funds');
429 END IF;
430
431 IGC_CC_BUDGETARY_CTRL_PKG.Execute_Budgetary_Ctrl
432 ( 1.0
433 , FND_API.G_FALSE
434 , FND_API.G_TRUE
435 , FND_API.G_VALID_LEVEL_FULL
436 , l_return_status
437 , l_bc_status
438 , l_msg_count
439 , l_msg_data
440 , l_cc_header_id
441 , p_acct_date
442 , 'R'
443 );
444
445 SAVEPOINT Approved_By_Preparer;
446
447 IF l_return_status <> FND_API.G_RET_STS_SUCCESS OR l_bc_status <> FND_API.G_TRUE THEN
448 raise FND_API.G_EXC_ERROR;
449 END IF;
450
451 -- read the new encumbrance status from the database.
452
453 SELECT cc_encmbrnc_status
454 INTO l_cc_encumb_status
455 FROM igc_cc_headers
456 WHERE cc_header_id = l_cc_header_id;
457
458 IF (g_debug_mode = 'Y') THEN
459 Put_Debug_Msg(l_full_path, 'New enc status is: '||l_cc_encumb_status);
460 END IF;
461
462 ELSE
463
464 global_budgetary_control_on := FALSE;
465 IF (g_debug_mode = 'Y') THEN
466 Put_Debug_Msg(l_full_path, ' No BC reqired');
467 END IF;
468 l_cc_encumb_status := p_cc_encumb_status;
469
470 END IF;
471
472 -- if funds reservation passes then
473 -- change the document status, create a PO entry and create an action history rec.
474
475 IF (g_debug_mode = 'Y') THEN
476 Put_Debug_Msg(l_full_path, 'Changing doc status');
477 END IF;
478
479 Change_Document_Status
480 ( p_itemtype => NULL
481 , p_itemkey => NULL
482 , p_cc_header_id => l_cc_header_id
483 , p_cc_state => l_cc_state
484 , p_cc_type => p_cc_type
485 , p_cc_preparer_id => l_cc_preparer_id
486 , p_cc_owner_id => l_cc_owner_id
487 , p_cc_current_owner => l_cc_current_owner
488 , p_cc_apprvl_status => 'AP'
489 , p_cc_encumb_status => l_cc_encumb_status
490 , p_cc_action_request => 'APPROVE'
491 , p_error_code => l_error_code
492 );
493
494 IF l_cc_type IN ('S', 'R') AND l_cc_state = 'CM' THEN
495
496 IF (g_debug_mode = 'Y') THEN
497 Put_Debug_Msg(l_full_path, 'Generating PO');
498 END IF;
499
500 -- generate interface entries.
501 IGC_CC_PO_INTERFACE_PKG.convert_cc_to_po
502 ( p_api_version => 1.0
503 , p_init_msg_list => FND_API.G_FALSE
504 , p_commit => FND_API.G_FALSE
505 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
506 , x_return_status => l_return_status
507 , x_msg_count => l_msg_count
508 , x_msg_data => l_msg_data
509 , p_cc_header_id => l_cc_header_id
510 );
511
512 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
513 raise FND_API.G_EXC_ERROR;
514 END IF;
515
516 END IF;
517
518 IF (g_debug_mode = 'Y') THEN
519 Put_Debug_Msg(l_full_path, 'Creating action history');
520 END IF;
521
522 Create_Action_History
523 ( p_cc_header_id => l_cc_header_id
524 , p_cc_version_num => l_cc_version_number
525 , p_cc_state => l_cc_state
526 , p_cc_old_cc_state => l_cc_old_cc_state
527 , p_cc_ctrl_status => l_cc_ctrl_status
528 , p_cc_apprvl_status => 'AP'
529 , p_cc_notes => l_cc_notes
530 , p_action_requested => 'APPROVE'
531 , p_error_code => l_error_code
532 ) ;
533
534 -- Standard check of p_commit.
535 IF FND_API.to_Boolean (p_commit) THEN
536 COMMIT WORK;
537 END IF;
538
539
540 FND_MSG_PUB.Count_And_Get(p_count => l_msg_count ,
541 p_data => p_msg_data );
542 p_msg_count := l_msg_count;
543
544 IF (g_debug_mode = 'Y') THEN
545 Put_Debug_Msg(l_full_path, 'Successfully completed, '||l_msg_count||' messages');
546 END IF;
547
548 EXCEPTION
549 WHEN FND_API.G_EXC_ERROR THEN
550 ROLLBACK to Approved_By_Preparer;
551 p_return_status := FND_API.G_RET_STS_ERROR;
552
553 Generate_Message;
554
555 FND_MSG_PUB.Count_And_Get(p_count => p_msg_count ,
556 p_data => p_msg_data );
557 IF (g_excep_level >= g_debug_level ) THEN
558 FND_LOG.STRING (g_excep_level,l_full_path,'FND_API.G_EXC_ERROR Exception Raised');
559 END IF;
560 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
561 ROLLBACK to Approved_By_Preparer;
562 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
563
564 Generate_Message;
565
566 FND_MSG_PUB.Count_And_Get(p_count => p_msg_count ,
567 p_data => p_msg_data );
568 IF (g_excep_level >= g_debug_level ) THEN
569 FND_LOG.STRING (g_excep_level,l_full_path,'FND_API.G_EXC_UNEXPECTED_ERROR Exception Raised');
570 END IF;
571 WHEN OTHERS THEN
572 ROLLBACK to Approved_By_Preparer;
573 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
574 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
575 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
576 l_api_name);
577 END IF;
578
579 Generate_Message;
580
581 FND_MSG_PUB.Count_And_Get(p_count => p_msg_count ,
582 p_data => p_msg_data );
583 IF ( g_unexp_level >= g_debug_level ) THEN
584 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
585 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
586 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
587 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
588 END IF;
589
590
591 END Approved_By_Preparer;
592
593
594 PROCEDURE Change_Document_Status
595 ( p_itemtype IN VARCHAR2
596 , p_itemkey IN VARCHAR2
597 , p_cc_header_id IN NUMBER
598 , p_cc_state IN VARCHAR2
599 , p_cc_type IN VARCHAR2
600 , p_cc_preparer_id IN NUMBER
601 , p_cc_owner_id IN NUMBER
602 , p_cc_current_owner IN NUMBER
603 , p_cc_apprvl_status IN VARCHAR2
604 , p_cc_encumb_status IN VARCHAR2
605 , p_cc_action_request IN VARCHAR2
606 , p_error_code OUT NOCOPY NUMBER
607 ) IS
608
609
610 l_cc_header_id IGC_CC_HEADERS.cc_header_id%TYPE := p_cc_header_id;
611 l_cc_state VARCHAR2(100) := p_cc_state;
612 l_cc_encumbrnc_status VARCHAR2(100) := p_cc_encumb_status;
613
614
615 l_cc_number VARCHAR2(255);
616 CURSOR c_ccnum IS
617 SELECT cc_num
618 FROM igc_cc_headers
619 WHERE cc_header_id = l_cc_header_id;
620
621 l_full_path VARCHAR2(255);
622
623 BEGIN
624
625 l_full_path:= g_path || 'Change_Document_Status';
626
627 -- check on cc_state for document status change.
628 IF (l_cc_state = 'PR' AND global_budgetary_control_on AND l_cc_encumbrnc_status = 'P' )
629 OR (l_cc_state = 'PR' AND NOT global_budgetary_control_on AND l_cc_encumbrnc_status = 'N' )
630 OR (l_cc_state = 'CM' AND global_budgetary_control_on AND l_cc_encumbrnc_status = 'C' )
631 OR (l_cc_state = 'CM' AND NOT global_budgetary_control_on AND l_cc_encumbrnc_status = 'N' )
632 OR (l_cc_state = 'CL' AND l_cc_encumbrnc_status = 'N' )
633 OR (l_cc_state = 'CT' AND l_cc_encumbrnc_status = 'N' )
634 OR (p_cc_type = 'R' )
635
636
637 THEN
638
639 IF (g_debug_mode = 'Y') THEN
640 Put_Debug_Msg(l_full_path, 'Updating headers to Approved status');
641 END IF;
642
643
644 UPDATE IGC_CC_HEADERS
645 SET cc_apprvl_status = 'AP'
646 WHERE cc_header_id = l_cc_header_id;
647
648 ELSE
649
650 OPEN c_ccnum;
651 FETCH c_ccnum INTO l_cc_number;
652 CLOSE c_ccnum;
653
654 IF (g_debug_mode = 'Y') THEN
655 Put_Debug_Msg(l_full_path, 'Combination of statuses is incorrect, raising an exception');
656 END IF;
657
658 message_token ('CC_NUM', l_cc_number);
659 message_token ('CC_STATE', l_cc_state);
660 message_token ('CC_ENC_STATUS', l_cc_encumbrnc_status);
661 message_token ('CC_APR_STATUS', 'AP');
662 add_message ('IGC', 'IGC_CC_STATE_ERROR');
663 RAISE FND_API.G_EXC_ERROR;
664
665 END IF;
666
667 -- update database table IGC_CC_HEADERS
668
669 END; -- END procedure change_document_status, overloaded.
670
671
672 PROCEDURE Create_Action_History
673 ( p_cc_header_id IN IGC_CC_HEADERS.cc_header_id%TYPE
674 , p_cc_version_num IN IGC_CC_HEADERS.cc_version_num%TYPE
675 , p_cc_state IN IGC_CC_HEADERS.cc_state%TYPE
676 , p_cc_old_cc_state IN IGC_CC_HEADERS.cc_state%TYPE
677 , p_cc_ctrl_status IN IGC_CC_HEADERS.cc_ctrl_status%TYPE
678 , p_cc_apprvl_status IN IGC_CC_HEADERS.cc_apprvl_status%TYPE
679 , p_cc_notes IN IGC_CC_ACTIONS.cc_action_notes%TYPE
680 , p_action_requested IN VARCHAR2
681 , p_error_code OUT NOCOPY NUMBER
682 ) IS
683
684 l_cc_state varchar2(2) := p_cc_state;
685 l_cc_action_type VARCHAR2(10);
686 l_return_status VARCHAR2(100);
687 l_msg_data VARCHAR2(2000);
688 l_msg_count NUMBER;
689 l_rowid VARCHAR2(30);
690 g_flag BOOLEAN := FALSE;
691 l_full_path VARCHAR2(255);
692
693 BEGIN
694
695 l_full_path:= g_path || 'Create_Action_History';
696
697
698 IF (g_debug_mode = 'Y') THEN
699 Put_Debug_Msg(l_full_path, 'Generating history record app status: '||p_cc_apprvl_status||' action type: '||'AP');
700 END IF;
701
702 IGC_CC_ACTIONS_PKG.Insert_Row
703 ( p_api_version => 1.0
704 , p_init_msg_list => FND_API.G_FALSE
705 , p_commit => FND_API.G_FALSE
706 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
707 , x_return_status => l_return_status
708 , x_msg_count => l_msg_count
709 , x_msg_data => l_msg_data
710 , P_Rowid => l_rowid
711 , P_CC_Header_Id => p_cc_header_id
712 , P_CC_Action_Version_Num => p_cc_version_num
713 , P_CC_Action_Type => 'AP'
714 , P_CC_Action_State => l_cc_state
715 , P_CC_Action_Ctrl_Status => p_cc_ctrl_status
716 , P_CC_Action_Apprvl_Status => p_cc_apprvl_status
717 , P_CC_Action_Notes => p_cc_notes
718 , P_Last_Update_Date => sysdate
719 , P_Last_Updated_By => fnd_global.user_id
720 , P_Last_Update_Login => fnd_global.login_id
721 , P_Creation_Date => sysdate
722 , P_Created_By => fnd_global.user_id
723 ) ;
724
725 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
726 raise FND_API.G_EXC_ERROR;
727 END IF;
728
729 END Create_Action_History;
730
731 PROCEDURE Put_Debug_Msg (
732 p_path IN VARCHAR2,
733 p_debug_msg IN VARCHAR2
734 ) IS
735
736 -- Constants :
737
738 /*l_return_status VARCHAR2(1);*/
739 l_api_name CONSTANT VARCHAR2(30) := 'Put_Debug_Msg';
740
741 BEGIN
742
743 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
744 /*IGC_MSGS_PKG.Put_Debug_Msg (l_full_path, p_debug_message => p_debug_msg,
745 p_profile_log_name => g_profile_name,
746 p_prod => 'IGC',
747 p_sub_comp => 'CC_APPR',
748 p_filename_val => NULL,
749 x_Return_Status => l_return_status
750 );
751 IF (l_Return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
752 raise FND_API.G_EXC_ERROR;
753 END IF;*/
754 -- END IF;
755
756 IF(g_state_level >= g_debug_level) THEN
757 FND_LOG.STRING(g_state_level, p_path, p_debug_msg);
758 END IF;
759 -- --------------------------------------------------------------------
760 -- Exception handler section for the Put_Debug_Msg procedure.
761 -- --------------------------------------------------------------------
762 EXCEPTION
763
764 /*WHEN FND_API.G_EXC_ERROR THEN
765 RETURN;*/
766
767 WHEN OTHERS THEN
768 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
769 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
770 END IF;
771 NULL;
772 RETURN;
773
774 END Put_Debug_Msg;
775
776
777 END IGC_CC_APPROVAL_PROCESS;