1 PACKAGE BODY IGC_CBC_VALIDATIONS_PKG AS
2 /*$Header: IGCBVALB.pls 120.10.12000000.3 2007/10/08 04:09:00 mbremkum ship $*/
3
4 -- -----------------------------------------------------------------------
5 -- Declare global variables.
6 -- -----------------------------------------------------------------------
7 G_PKG_NAME CONSTANT VARCHAR2(30):= 'IGC_CBC_VALIDATIONS_PKG';
8
9 -- Variables for Central Logging
10 --l_debug_mode VARCHAR2(1) := NVL(FND_PROFILE.VALUE('IGC_DEBUG_ENABLED'),'N');
11 g_debug_mode VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
12
13 --bug 3199488
14 g_debug_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
15 g_state_level NUMBER := FND_LOG.LEVEL_STATEMENT;
16 g_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
17 g_event_level NUMBER := FND_LOG.LEVEL_EVENT;
18 g_excep_level NUMBER := FND_LOG.LEVEL_EXCEPTION;
19 g_error_level NUMBER := FND_LOG.LEVEL_ERROR;
20 g_unexp_level NUMBER := FND_LOG.LEVEL_UNEXPECTED;
21 g_path VARCHAR2(255) := 'IGC.PLSQL.IGCBVALB.IGC_CBC_VALIDATIONS_PKG.';
22 --bug 3199488
23
24 -- -----------------------------------------------------------------------
25 -- Private Functions for Procedure
26 -- -----------------------------------------------------------------------
27 PROCEDURE message_token(
28 tokname IN VARCHAR2,
29 tokval IN VARCHAR2
30 );
31
32 PROCEDURE add_message(
33 appname IN VARCHAR2,
34 msgname IN VARCHAR2
35 );
36
37 PROCEDURE Put_Debug_Msg (
38 P_path IN VARCHAR2,
39 p_debug_msg IN VARCHAR2
40 );
41
42
43 /*=======================================================================+
44 | PROCEDURE message_token |
45 | |
46 | Note : This is a private function to add tokens and values onto the |
47 | error stack as any error could happen during the public |
48 | procedures that are called. |
49 | |
50 | Parameters : |
51 | |
52 | Parameters for Procedure to process properly. |
53 | |
54 | tokname Token name for message that has been defined |
55 | tokval Token value to be displayed in the error message |
56 | |
57 +=======================================================================*/
58 PROCEDURE message_token(
59 tokname IN VARCHAR2,
60 tokval IN VARCHAR2
61 ) IS
62
63 BEGIN
64
65 IGC_MSGS_PKG.message_token (p_tokname => tokname,
66 p_tokval => tokval);
67
68 END message_token;
69
70
71 /*=======================================================================+
72 | PROCEDURE add_message |
73 | |
74 | Note : This is a private function to add messages onto the error stack|
75 | as any error could happen during the public procedures that are|
76 | called. |
77 | |
78 | Parameters : |
79 | |
80 | Parameters for Procedure to process properly. |
81 | |
82 | appname Application name that message is for |
83 | msgname Message name that has been seeded in database |
84 | |
85 +=======================================================================*/
86 PROCEDURE add_message(
87 appname IN VARCHAR2,
88 msgname IN VARCHAR2
89 ) IS
90
91 BEGIN
92
93 IGC_MSGS_PKG.add_message (p_appname => appname,
94 p_msgname => msgname);
95
96 END add_message;
97
98
99 /*=======================================================================+
100 | PROCEDURE Put_Debug_Msg |
101 | |
102 | Note : This is a private function to output any debug information if |
103 | debug is enabled for the system to determine any issue that |
104 | may be happening at customer site. |
105 | |
106 | Parameters : |
107 | |
108 | Parameters for Procedure to process properly. |
109 | |
110 | p_debug_msg This is the message that is to be output to log for |
111 | debugging purposes. |
112 | |
113 +=======================================================================*/
114 PROCEDURE Put_Debug_Msg (
115 p_path IN VARCHAR2,
116 p_debug_msg IN VARCHAR2
117 ) IS
118
119 -- Constants :
120
121 /*l_Return_Status VARCHAR2(1);
122 l_prod VARCHAR2(3) := 'IGC';
123 l_sub_comp VARCHAR2(3) := 'CBC';
124 l_profile_name VARCHAR2(255) := 'IGC_DEBUG_LOG_DIRECTORY';*/
125 l_api_name CONSTANT VARCHAR2(30) := 'Put_Debug_Msg';
126
127 BEGIN
128
129 IF(g_state_level >= g_debug_level) THEN
130 FND_LOG.STRING(g_state_level, p_path, p_debug_msg);
131 END IF;
132
133 /*IGC_MSGS_PKG.Put_Debug_Msg (l_full_path, p_debug_message => p_debug_msg,
134 p_profile_log_name => l_profile_name,
135 p_prod => l_prod,
136 p_sub_comp => l_sub_comp,
137 p_filename_val => NULL,
138 x_Return_Status => l_Return_Status
139 );
140 IF (l_Return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
141 raise FND_API.G_EXC_ERROR;
142 END IF;*/
143
144 RETURN;
145
146 -- --------------------------------------------------------------------
147 -- Exception handler section for the Put_Debug_Msg procedure.
148 -- --------------------------------------------------------------------
149 EXCEPTION
150
151 /*WHEN FND_API.G_EXC_ERROR THEN
152 RETURN;*/
153
154 WHEN OTHERS THEN
155 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
156 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
157 END IF;
158 NULL;
159 RETURN;
160
161 END Put_Debug_Msg;
162
163
164 /*=======================================================================+
165 | PROCEDURE Validate_CCID |
166 | |
167 | Note : This procedure is designed to validate the CCID that is given |
168 | based upon the rules defined for the CCID to be entered into |
169 | the CBC Funds Checker process and inserted into the table |
170 | IGC_CBC_JE_LINES. |
171 | |
172 | If there is to be any changes inside of this procedure then |
173 | there needs to be analysis performed on the effect it will have|
174 | on the Funds Checker process. |
175 | |
176 | Parameters : |
177 | |
178 | Standard header params for Public Procedures. |
179 | |
180 | p_api_version Version number for API to run |
181 | p_init_msg_list Message stack to be initialized flag |
182 | p_commit Is work to be commited here flag |
183 | p_validation_level Validation Level to be performed |
184 | p_return_status Status returned from Procedure |
185 | p_msg_count Number of messages on stack returned |
186 | p_msg_data Message text information returned |
187 | |
188 | Parameters for Procedure to process properly. |
189 | |
190 | p_validation_type Type of Validation FC (Funds), LC (Legacy) |
191 | p_ccid Code Combination ID From GL tables |
192 | p_transaction_date Date transaction to compare period start / end |
193 | p_det_sum_value Detail (D) or Summary (S) transaction |
194 | p_set_of_books_id Set Of Books being processed |
195 | p_actual_flag Actual Flag for Encumbrance or Budget. |
196 | p_result_code Result Code mapping for status update to user |
197 | |
198 +=======================================================================*/
199 PROCEDURE Validate_CCID
200 (
201 p_api_version IN NUMBER,
202 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
203 p_commit IN VARCHAR2 := FND_API.G_FALSE,
204 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
205 p_return_status OUT NOCOPY VARCHAR2,
206 p_msg_count OUT NOCOPY NUMBER,
207 p_msg_data OUT NOCOPY VARCHAR2,
208
209 p_validation_type IN VARCHAR2,
210 p_ccid IN igc_cbc_je_lines.code_combination_id%TYPE, -- Contract ID
211 p_effective_date IN igc_cbc_je_lines.effective_date%TYPE, -- Transaction Date
212 p_det_sum_value IN igc_cbc_je_lines.detail_summary_code%TYPE,
213 p_set_of_books_id IN gl_sets_of_books.set_of_books_id%TYPE,
214 p_actual_flag IN VARCHAR2,
215 p_result_code OUT NOCOPY VARCHAR2
216 ) IS
217
218 -- --------------------------------------------------------------------
219 -- Declare the cursors to be used during this function run.
220 -- --------------------------------------------------------------------
221 CURSOR c_validate_ccid_values IS
222 SELECT GCC.detail_budgeting_allowed_flag,
223 GCC.detail_posting_allowed_flag,
224 GCC.enabled_flag,
225 GCC.start_date_active,
226 GCC.end_date_active
227 FROM gl_code_combinations GCC,
228 gl_sets_of_books GSB
229 WHERE GCC.code_combination_id = p_ccid
230 AND GSB.set_of_books_id = p_set_of_books_id
231 AND GCC.chart_of_accounts_id = GSB.chart_of_accounts_id;
232
233 -- -------------------------------------------------------------------------
234 -- Declare local variables used within fuction
235 -- -------------------------------------------------------------------------
236 l_budget_flag gl_code_combinations.detail_budgeting_allowed_flag%TYPE;
237 l_posting_flag gl_code_combinations.detail_posting_allowed_flag%TYPE;
238 l_enabled_flag gl_code_combinations.enabled_flag%TYPE;
239 l_start_date gl_code_combinations.start_date_active%TYPE;
240 l_end_date gl_code_combinations.end_date_active%TYPE;
241 l_closing_status gl_period_statuses.closing_status%TYPE;
242 l_period_name igc_cbc_je_lines.period_name%TYPE;
243 l_period_set_name igc_cbc_je_lines.period_set_name%TYPE;
244 l_quarter_num igc_cbc_je_lines.quarter_num%TYPE;
245 l_period_num igc_cbc_je_lines.period_num%TYPE;
246 l_period_year igc_cbc_je_lines.period_year%TYPE;
247 l_msg_count NUMBER;
248 l_msg_data VARCHAR2(2000);
249 l_Return_Status VARCHAR2(1);
250 l_validation_error BOOLEAN := FALSE;
251 l_api_name CONSTANT VARCHAR2(30) := 'Validate_CCID';
252 l_api_version CONSTANT NUMBER := 1.0;
253 l_full_path VARCHAR2(255);
254
255 BEGIN
256
257 l_full_path := g_path || 'Validate_CCID';
258
259 SAVEPOINT Validate_CCID_Pub;
260
261 IF NOT FND_API.Compatible_API_Call ( l_api_version,
262 p_api_version,
263 l_api_name,
264 G_PKG_NAME )
265 THEN
266 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
267 END IF;
268
269 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
270 FND_MSG_PUB.initialize ;
271 END IF;
272
273 -- --------------------------------------------------------------------
274 -- Initialize Return status
275 -- --------------------------------------------------------------------
276 p_return_status := FND_API.G_RET_STS_SUCCESS;
277 p_result_code := NULL;
278
279 -- --------------------------------------------------------------------
280 -- Retrieve data to check that the CCID is a valid to check flags that
281 -- indicate if the CCID will be valid for Funds Check and Reservation.
282 -- --------------------------------------------------------------------
283 OPEN c_validate_ccid_values;
284
285 FETCH c_validate_ccid_values
286 INTO l_budget_flag,
287 l_posting_flag,
288 l_enabled_flag,
289 l_start_date,
290 l_end_date;
291
292 -- --------------------------------------------------------------------
293 -- Ensure that the record can be found based upon the CCID given.
294 -- --------------------------------------------------------------------
295 IF (c_validate_ccid_values%NOTFOUND) THEN
296 p_result_code := 'F20';
297
298 ELSIF (l_enabled_flag = 'N') THEN
299
300 -- --------------------------------------------------------------------
301 -- Check to see if the CCID is not enabled. If not enabled then setup
302 -- the appropriate global variable indicating that there was a validation
303 -- error and update the status and result status for the record in the
304 -- IGC_CC_INTERFACE table and set the global variable which indicates an
305 -- error has happened during the validation. This will prevent the funds
306 -- check from being performed.
307 -- --------------------------------------------------------------------
308
309 p_result_code := 'F21';
310
311 ELSIF (
312 (sign(p_effective_date - nvl(l_start_date, p_effective_date)) < 0)
313 OR (sign(nvl(l_end_date, p_effective_date) - p_effective_date) < 0)
314 ) THEN
315
316 -- --------------------------------------------------------------------
317 -- Make sure that the CCID start and end dates are in the active
318 -- range. If they are not then set the status of the line to 'F21 as
319 -- is the case in the Standard funds check.
320 -- --------------------------------------------------------------------
321
322 p_result_code := 'F21';
323
324 ELSIF p_validation_type ='FC' THEN
325
326 -- --------------------------------------------------------------------
327 -- Check to see if the CCID does not have POSTING enabled. If not then
328 -- setup the appropriate global variable indicating that there was a
329 -- validation error and update the status and result status for the
330 -- record in the IGC_CC_INTERFACE table and set the global variable
331 -- which indicates an error has happened during the validation. This
332 -- will prevent the funds check from being performed.
333 -- --------------------------------------------------------------------
334 IF (l_posting_flag = 'N') AND (p_det_sum_value ='D') THEN
335
336 p_result_code := 'F22';
337
338 ELSIF (l_budget_flag = 'N') AND (p_actual_flag = 'B') AND (p_det_sum_value ='D') THEN
339
340 -- --------------------------------------------------------------------
341 -- Check to see if the CCID does not have budgeting enabled. If not
342 -- then setup the appropriate global variable indicating that there was
343 -- a validation error and update the status and result status for the
344 -- record in the IGC_CC_INTERFACE table and set the global variable
345 -- which indicates an error has happened during the validation. This
346 -- will prevent the funds check from being performed.
347 -- ---------------------------------------------------------------------
348 p_result_code := 'F23';
349
350 ELSIF l_budget_flag = 'N' AND p_det_sum_value = 'D' AND p_actual_flag = 'B' THEN
351
352 p_result_code := 'F23';
353
354 END IF;
355
356 END IF;
357
358 -- --------------------------------------------------------------------
359 -- Close all cursors used by this function here.
360 -- --------------------------------------------------------------------
361 IF (c_validate_ccid_values%ISOPEN) THEN
362 CLOSE c_validate_ccid_values;
363 END IF;
364
365 IF FND_API.To_Boolean ( p_commit ) THEN
366 COMMIT WORK;
367 END iF;
368
369 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
370 p_data => p_msg_data );
371
372 RETURN;
373
374 -- -------------------------------------------------------------------------
375 -- Exception handler section for the Validate_CCID procedure.
376 -- -------------------------------------------------------------------------
377 EXCEPTION
378
379 WHEN FND_API.G_EXC_ERROR THEN
380
381 ROLLBACK TO Validate_CCID_Pub;
382 p_return_status := FND_API.G_RET_STS_ERROR;
383 p_result_code := NULL;
384 IF (c_validate_ccid_values%ISOPEN) THEN
385 CLOSE c_validate_ccid_values;
386 END IF;
387 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
388 p_data => p_msg_data );
389 IF (g_excep_level >= g_debug_level ) THEN
390 FND_LOG.STRING (g_excep_level,l_full_path,'FND_API.G_EXC_ERROR Exception Raised');
391 END IF;
392 RETURN;
393
394 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
395
396 ROLLBACK TO Validate_CCID_Pub;
397 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
398 p_result_code := NULL;
399 IF (c_validate_ccid_values%ISOPEN) THEN
400 CLOSE c_validate_ccid_values;
401 END IF;
402 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
403 p_data => p_msg_data );
404 IF (g_excep_level >= g_debug_level ) THEN
405 FND_LOG.STRING (g_excep_level,l_full_path,'FND_API.G_EXC_UNEXPECTED_ERROR Exception Raised');
406 END IF;
407 RETURN;
408
409 WHEN OTHERS THEN
410
411 ROLLBACK TO Validate_CCID_Pub;
412 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
413 p_result_code := NULL;
414 IF (c_validate_ccid_values%ISOPEN) THEN
415 CLOSE c_validate_ccid_values;
416 END IF;
417 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
418 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
419 l_api_name);
420 END IF;
421
422 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
423 p_data => p_msg_data );
424 IF ( g_unexp_level >= g_debug_level ) THEN
425 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
426 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
427 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
428 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
429 END IF;
430 RETURN;
431
432 END Validate_CCID;
433
434
435 /*=======================================================================+
436 | PROCEDURE Validate_Get_CCID_Budget_Info |
437 | |
438 | Note : This procedure is designed to validate the CCID Budget Version |
439 | information based upon the rules defined for the CCID to be |
440 | entered into the CBC Funds Checker process and inserted into |
441 | the table IGC_CBC_JE_LINES. |
442 | |
443 | If there is to be any changes inside of this procedure then |
444 | there needs to be analysis performed on the effect it will have|
445 | on the Funds Checker process. |
446 | |
447 | Parameters : |
448 | |
449 | Standard header params for Public Procedures. |
450 | |
451 | p_api_version Version number for API to run |
452 | p_init_msg_list Message stack to be initialized flag |
453 | p_commit Is work to be commited here flag |
454 | p_validation_level Validation Level to be performed |
455 | p_return_status Status returned from Procedure |
456 | p_msg_count Number of messages on stack returned |
457 | p_msg_data Message text information returned |
458 | |
459 | Parameters for Procedure to process properly. |
460 | |
461 | p_efc_enabled Enhanced Funds Checker enabled flag. |
462 | p_set_of_books_id GL Set Of books ID being processed |
463 | p_actual_flag Actual Flag for Encumbrance or Budget. |
464 | p_ccid GL Code Combination ID |
465 | p_det_sum_value Detail (D) or Summary (S) transaction |
466 | p_currency_code Currency Code that transaction is for |
467 | p_effective_date Transaction date for period range |
468 | p_budget_ver_id Funding Budget Version ID if Budget CCID |
469 | p_out_budget_ver_id Funding Budget Version ID if available for CCID|
470 | p_amount_type Amount type in GL for CCID |
471 | p_funds_level_code What level of Funds Check required |
472 | |
473 +=======================================================================*/
474 PROCEDURE Validate_Get_CCID_Budget_Info
475 (
476 p_api_version IN NUMBER,
477 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
478 p_commit IN VARCHAR2 := FND_API.G_FALSE,
479 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
480 p_return_status OUT NOCOPY VARCHAR2,
481 p_msg_count OUT NOCOPY NUMBER,
482 p_msg_data OUT NOCOPY VARCHAR2,
483
484 p_efc_enabled IN VARCHAR2,
485 p_set_of_books_id IN gl_sets_of_books.set_of_books_id%TYPE,
486 p_actual_flag IN VARCHAR2,
487 p_ccid IN igc_cbc_je_lines.code_combination_id%TYPE,
488 p_det_sum_value IN igc_cbc_je_lines.detail_summary_code%TYPE,
489 p_currency_code IN igc_cbc_je_lines.currency_code%TYPE,
490 p_effective_date IN igc_cbc_je_lines.effective_date%TYPE, -- Transaction Date
491 p_budget_ver_id IN igc_cbc_je_lines.budget_version_id%TYPE,
492 p_out_budget_ver_id OUT NOCOPY igc_cbc_je_lines.budget_version_id%TYPE,
493 p_amount_type OUT NOCOPY igc_cbc_je_lines.amount_type%TYPE,
494 p_funds_level_code OUT NOCOPY igc_cbc_je_lines.funds_check_level_code%TYPE
495 ) IS
496
497 -- -------------------------------------------------------------------------
498 -- Declare local variables used within fuction
499 -- -------------------------------------------------------------------------
500 l_budget_ver_id igc_cbc_je_lines.budget_version_id%TYPE;
501 l_amount_type igc_cbc_je_lines.amount_type%TYPE;
502 l_funds_level_code igc_cbc_je_lines.funds_check_level_code%TYPE;
503 l_cbc_override igc_cbc_je_lines.funds_check_level_code%TYPE;
504
505 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Get_CCID_Budget_Info';
506 l_api_version CONSTANT NUMBER := 1.0;
507 l_gl_application_id fnd_application.application_id%TYPE;
508
509 /*Commented for compilation - igc_cbc_summary_templates_v View is dummy Bug No 6341012*/
510
511 /*
512 l_efc_budget_str VARCHAR2(2000) := '
513 SELECT GST.amount_type,
514 GST.funds_check_level_code,
515 GST.cbc_override,
516 ST.funding_budget_version_id
517 FROM igc_cbc_summary_templates_v GST,
518 psa_efc_summary_budgets ST,
519 gl_budget_versions BVR,
520 gl_budgets BUD,
521 gl_period_statuses FPER,
522 gl_period_statuses LPER
523 WHERE GST.template_id IN
524 ( SELECT template_id
525 FROM gl_account_hierarchies
526 WHERE set_of_books_id = :1
527 AND summary_code_combination_id = :2
528 )
529 AND GST.template_id = ST.template_id
530 AND GST.set_of_books_id = FPER.set_of_books_id
531 AND ST.funding_budget_version_id = BVR.budget_version_id
532 AND BVR.budget_name = BUD.budget_name
533 AND FPER.set_of_books_id = :3
534 AND LPER.set_of_books_id = :4
535 AND BUD.first_valid_period_name = FPER.period_name
536 AND BUD.last_valid_period_name = LPER.period_name
537 AND FPER.application_id = :5
538 AND LPER.application_id = :6
539 AND :7 BETWEEN FPER.start_date AND LPER.end_date
540 ';
541 */
542
543 -- --------------------------------------------------------------------
544 -- Declare the cursors to be used during this function run.
545 -- --------------------------------------------------------------------
546 --
547 -- Bug 2885953 - amended cursor below for performance enhancements
548 -- CURSOR c_igc_je_detail_info IS
549 -- SELECT GBA.amount_type,
550 -- GBA.funds_check_level_code,
551 -- BAR.cbc_override,
552 -- GBA.funding_budget_version_id
553 -- FROM gl_budget_assignments GBA,
554 -- igc_cbc_ba_ranges_v BAR
555 -- WHERE GBA.set_of_books_id = p_set_of_books_id
556 -- AND BAR.set_of_books_id = GBA.set_of_books_id
557 -- AND GBA.code_combination_id = p_ccid
558 -- AND GBA.currency_code = p_currency_code
559 -- AND GBA.range_id = BAR.range_id
560 -- AND GBA.range_id IN
561 -- ( SELECT asg.range_id
562 -- FROM gl_budget_assignment_ranges asg,
563 -- gl_budget_versions bvr,
564 -- gl_budgets bud,
565 -- gl_period_statuses fper,
566 -- gl_period_statuses lper
567 -- WHERE asg.funding_budget_version_id IS NOT NULL
568 -- AND asg.funding_budget_version_id=bvr.budget_version_id
569 -- AND bvr.budget_name=bud.budget_name
570 -- AND fper.set_of_books_id = p_set_of_books_id
571 -- AND lper.set_of_books_id = p_set_of_books_id
572 -- AND bud.first_valid_period_name = fper.period_name
573 -- AND bud.last_valid_period_name = lper.period_name
574 -- AND fper.application_id = l_gl_application_id
575 -- AND lper.application_id = l_gl_application_id
576 -- AND p_effective_date BETWEEN fper.start_date AND lper.end_date
577 -- );
578 CURSOR c_igc_je_detail_info IS
579 SELECT GBA.amount_type,
580 GBA.funds_check_level_code,
581 BAR.cbc_override,
582 GBA.funding_budget_version_id
583 FROM gl_budget_assignments GBA,
584 igc_cbc_ba_ranges BAR,
585 gl_budget_assignment_ranges asg,
586 gl_budget_versions bvr,
587 gl_budgets bud,
588 gl_period_statuses fper,
589 gl_period_statuses lper
590 /*R12 Uptake - Commented for compilation Bug No 6341012*/
591 -- WHERE GBA.set_of_books_id = p_set_of_books_id
592 WHERE GBA.ledger_id = p_set_of_books_id
593 /*R12 Uptake - Commented for compilation Bug No 6341012*/
594 -- AND BAR.set_of_books_id(+) = GBA.set_of_books_id
595 AND BAR.set_of_books_id(+) = GBA.ledger_id
596 AND GBA.code_combination_id = p_ccid
597 AND GBA.currency_code = p_currency_code
598 AND GBA.range_id = BAR.cbc_range_id(+)
599 AND GBA.range_id = asg.range_id
600 AND asg.funding_budget_version_id=bvr.budget_version_id
601 AND bvr.budget_name=bud.budget_name
602 AND fper.set_of_books_id = p_set_of_books_id
603 AND lper.set_of_books_id = p_set_of_books_id
604 AND bud.first_valid_period_name = fper.period_name
605 AND bud.last_valid_period_name = lper.period_name
606 AND fper.application_id = l_gl_application_id
607 AND lper.application_id = l_gl_application_id
608 AND p_effective_date BETWEEN fper.start_date AND lper.end_date
609 ;
610 /*
611
612 --Commented for compilation - igc_cbc_summary_templates_v View does not exist Bug No 6341012
613
614 CURSOR c_igc_je_summary_info IS
615 SELECT GST.amount_type,
616 GST.funds_check_level_code,
617 GST.cbc_override,
618 GST.funding_budget_version_id
619 FROM igc_cbc_summary_templates_v GST,
620 gl_account_hierarchies GAH
621 --R12 Uptake - Commented for compilation
622 -- WHERE GAH.set_of_books_id = p_set_of_books_id
623 WHERE GAH.ledger_id = p_set_of_books_id
624 AND GAH.summary_code_combination_id = p_ccid
625 AND GST.template_id = GAH.template_id
626 --R12 Uptake - Commented for compilation
627 -- AND GST.set_of_books_id = GAH.set_of_books_id;
628 AND GST.set_of_books_id = GAH.ledger_id;
629
630 */
631
632 l_full_path VARCHAR2(255);
633
634 BEGIN
635
636 l_full_path := g_path || 'Validate_Get_CCID_Budget_Info';
637
638 SAVEPOINT Valid_Get_CCID_Bdg_Info_Pub;
639
640 IF NOT FND_API.Compatible_API_Call ( l_api_version,
641 p_api_version,
642 l_api_name,
643 G_PKG_NAME )
644 THEN
645 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
646 END IF;
647
648 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
649 FND_MSG_PUB.initialize ;
650 END IF;
651
652 -- -------------------------------------------------------------------
653 -- Initialize return status to Success.
654 -- -------------------------------------------------------------------
655 p_return_status := FND_API.G_RET_STS_SUCCESS ;
656
657 -- --------------------------------------------------------------------
658 -- Obtain the application ID that will be used throughout this process.
659 -- --------------------------------------------------------------------
660 SELECT application_id
661 INTO l_gl_application_id
662 FROM fnd_application
663 WHERE application_short_name = 'SQLGL';
664
665 -- ------------------------------------------------------------------------
666 -- If this is a summary_record being inserted then get the DR_CR_CODE from
667 -- the corresponding summary ID.
668 -- ------------------------------------------------------------------------
669 IF (p_det_sum_value = 'S') THEN
670
671 -- ------------------------------------------------------------------------
672 -- Obtain the funds_check_level_code and amount_type for the CCID that is
673 -- being inserted.
674 -- ------------------------------------------------------------------------
675 OPEN c_igc_je_detail_info;
676
677 FETCH c_igc_je_detail_info
678 INTO l_amount_type,
679 l_funds_level_code,
680 l_cbc_override,
681 l_budget_ver_id;
682
683 IF (c_igc_je_detail_info%NOTFOUND) THEN
684
685 l_funds_level_code := 'N';
686
687 IF FND_API.TO_BOOLEAN(p_efc_enabled) THEN
688
689 BEGIN
690
691 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
692 IF g_debug_mode = 'Y' THEN
693 Put_Debug_Msg (l_full_path, 'Obtaining budget version from EFC tables' );
694 END IF;
695 /*
696 --Commented for compilation - igc_cbc_summary_templates_v View does not exist Bug No 6341012
697
698 EXECUTE IMMEDIATE l_efc_budget_str
699 INTO l_amount_type,
700 l_funds_level_code,
701 l_cbc_override,
702 l_budget_ver_id
703 USING p_set_of_books_id,
704 p_ccid,
705 p_set_of_books_id,
706 p_set_of_books_id,
707 l_gl_application_id,
708 l_gl_application_id,
709 p_effective_date;
710 */
711 EXCEPTION
712 WHEN NO_DATA_FOUND THEN
713 NULL; --means no funding budget found for this period
714 WHEN OTHERS THEN
715 NULL;
716 END;
717
718 ELSE
719
720 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
721 IF g_debug_mode = 'Y' THEN
722 Put_Debug_Msg (l_full_path, 'Obtaining budget version from summary template table');
723 END IF;
724 /*
725 -- Commented for compilation - cursor c_igc_je_summary_info is based on a dummy view Bug No 6341012
726 OPEN c_igc_je_summary_info;
727 FETCH c_igc_je_summary_info
728 INTO l_amount_type,
729 l_funds_level_code,
730 l_cbc_override,
731 l_budget_ver_id;
732 */
733 END IF;
734
735 IF (l_funds_level_code IS NULL) THEN
736
737 -- Assign default level
738
739 l_funds_level_code := 'N';
740
741 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
742 IF g_debug_mode = 'Y' THEN
743 Put_Debug_Msg (l_full_path, ' Detail Funds Level not found, assign N');
744 END IF;
745
746 ELSE
747
748 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
749 IF g_debug_mode = 'Y' THEN
750 Put_Debug_Msg (l_full_path, ' Summary Funds Level Received From Summary Templates.');
751 END IF;
752
753 END IF;
754
755 ELSE
756
757 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
758 IF g_debug_mode = 'Y' THEN
759 Put_Debug_Msg (l_full_path, ' Summary Funds Level Received From Budget Assignments.');
760 END IF;
761
762 END IF;
763
764 ELSE
765
766 -- ------------------------------------------------------------------------
767 -- Obtain the funds_check_level_code and amount_type for the CCID that is
768 -- being inserted.
769 -- ------------------------------------------------------------------------
770 OPEN c_igc_je_detail_info;
771
772 FETCH c_igc_je_detail_info
773 INTO l_amount_type,
774 l_funds_level_code,
775 l_cbc_override,
776 l_budget_ver_id;
777
778 IF (c_igc_je_detail_info%NOTFOUND) THEN
779
780 -- Assign default level
781
782 l_funds_level_code := 'N';
783 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
784 IF g_debug_mode = 'Y' THEN
785 Put_Debug_Msg (l_full_path, ' Detail Funds Level not found, assign N');
786 END IF;
787
788 ELSE
789
790 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
791 IF g_debug_mode = 'Y' THEN
792 Put_Debug_Msg (l_full_path, ' Detail Funds Level Received From Budget Assignments');
793 END IF;
794
795 END IF;
796
797 END IF;
798
799 IF (NOT FND_API.TO_BOOLEAN(p_efc_enabled)) THEN
800
801 l_funds_level_code := 'N';
802
803 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
804 IF g_debug_mode = 'Y' THEN
805 Put_Debug_Msg (l_full_path, ' BC is disabled, assigning severity level None ');
806 END IF;
807
808 END IF;
809
810 -- -------------------------------------------------------------------
811 -- Make sure that the Budget Version id is not NULL, amount type is
812 -- not NULL, and that the funds check level is anything but N. If
813 -- this case is TRUE then the CCID can not be checked.
814 -- -------------------------------------------------------------------
815 l_funds_level_code := NVL(l_funds_level_code, 'N');
816
817 l_budget_ver_id := NVL(l_budget_ver_id,p_budget_ver_id);
818
819 IF ((p_actual_flag = 'B') AND
820 (p_budget_ver_id IS NOT NULL)) THEN
821
822 l_budget_ver_id := p_budget_ver_id;
823
824 END IF;
825
826 IF ((p_actual_flag = 'B') AND
827 (l_budget_ver_id IS NULL)) THEN
828
829 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
830 IF g_debug_mode = 'Y' THEN
831 Put_Debug_Msg (l_full_path, ' Actual Flag is B and Budget Version ID is NULL error.');
832 END IF;
833 message_token ('CCID', to_char(p_ccid));
834 message_token ('SOB_ID', to_char(p_set_of_books_id));
835 add_message ('IGC', 'IGC_BUDGET_NOT_FOUND');
836 RAISE FND_API.G_EXC_ERROR;
837
838 END IF;
839
840 IF ((l_funds_level_code <> 'N') AND
841 (l_amount_type IS NULL) AND
842 (l_budget_ver_id IS NULL)) THEN
843
844 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
845 IF g_debug_mode = 'Y' THEN
846 Put_Debug_Msg (l_full_path, ' Funds Check Level is : ' || l_funds_level_code );
847 Put_Debug_Msg (l_full_path, ' Amount Type is NULL and Budget Version ID is NULL error.');
848 END IF;
849 message_token ('CCID', to_char(p_ccid));
850 message_token ('SOB_ID', to_char(p_set_of_books_id));
851 add_message ('IGC', 'IGC_INVALID_BUDGET_STATE');
852 RAISE FND_API.G_EXC_ERROR;
853
854 END IF;
855
856 -- -------------------------------------------------------------------------
857 -- If the CBC-specific funds check level (cbc_override) has a value, and
858 -- the standard funds check level is NOT set to 'None', use the cbc_override
859 -- -------------------------------------------------------------------------
860 IF ((l_cbc_override IS NOT NULL) AND
861 (l_funds_level_code <> 'N')) THEN
862 p_funds_level_code := l_cbc_override;
863 ELSE
864 p_funds_level_code := l_funds_level_code;
865 END IF;
866
867 p_out_budget_ver_id := l_budget_ver_id;
868 p_amount_type := l_amount_type;
869
870 -- -------------------------------------------------------------------------
871 -- Close all cursors that have been opened in this procedure.
872 -- -------------------------------------------------------------------------
873 IF (c_igc_je_detail_info%ISOPEN) THEN
874 CLOSE c_igc_je_detail_info;
875 END IF;
876 /*
877 -- Commented the cursor for compilation Bug No 6341012
878 IF (c_igc_je_summary_info%ISOPEN) THEN
879 CLOSE c_igc_je_summary_info;
880 END IF;
881 */
882
883 IF FND_API.To_Boolean ( p_commit ) THEN
884 COMMIT WORK;
885 END iF;
886
887 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
888 p_data => p_msg_data );
889
890 RETURN;
891
892 -- -------------------------------------------------------------------------
893 -- Exception handler section for the Get_CCID_Budget_Info procedure.
894 -- -------------------------------------------------------------------------
895 EXCEPTION
896
897 WHEN FND_API.G_EXC_ERROR THEN
898
899 ROLLBACK TO Valid_Get_CCID_Bdg_Info_Pub;
900 p_return_status := FND_API.G_RET_STS_ERROR;
901 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
902 p_data => p_msg_data );
903 IF (g_excep_level >= g_debug_level ) THEN
904 FND_LOG.STRING (g_excep_level,l_full_path,'FND_API.G_EXC_ERROR Exception Raised');
905 END IF;
906
907 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
908
909 ROLLBACK TO Valid_Get_CCID_Bdg_Info_Pub;
910 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
911 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
912 p_data => p_msg_data );
913 IF (g_excep_level >= g_debug_level ) THEN
914 FND_LOG.STRING (g_excep_level,l_full_path,'FND_API.G_EXC_UNEXPECTED_ERROR Exception Raised');
915 END IF;
916
917 WHEN OTHERS THEN
918
919 ROLLBACK TO Valid_Get_CCID_Bdg_Info_Pub;
920 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
921
922 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
923 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
924 l_api_name);
925 END IF;
926
927 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
928 p_data => p_msg_data );
929 IF ( g_unexp_level >= g_debug_level ) THEN
930 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
931 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
932 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
933 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
934 END IF;
935
936 END Validate_Get_CCID_Budget_Info;
937
938
939 /*=======================================================================+
940 | PROCEDURE Validate_Get_CCID_Period_Name |
941 | |
942 | Note : This procedure is designed to validate the CCID Period Name |
943 | information based upon the rules defined for the CCID to be |
944 | entered into the CBC Funds Checker process and inserted into |
945 | the table IGC_CBC_JE_LINES. |
946 | |
947 | If there is to be any changes inside of this procedure then |
948 | there needs to be analysis performed on the effect it will have|
949 | on the Funds Checker process. |
950 | |
951 | Parameters : |
952 | |
953 | Standard header params for Public Procedures. |
954 | |
955 | p_api_version Version number for API to run |
956 | p_init_msg_list Message stack to be initialized flag |
957 | p_commit Is work to be commited here flag |
958 | p_validation_level Validation Level to be performed |
959 | p_return_status Status returned from Procedure |
960 | p_msg_count Number of messages on stack returned |
961 | p_msg_data Message text information returned |
962 | |
963 | Parameters for Procedure to process properly. |
964 | |
965 | p_sob_id GL Set of Books ID to be processed |
966 | p_effect_date Transaction Date |
967 | p_check_type Type of check Funds (FC) or Legacy (LC) |
968 | p_period_name Period name for CCID if found for Check type |
969 | p_period_set_name Period Set Name for CCID if found |
970 | p_quarter_num Quarter number for CCID if found |
971 | p_period_num Period Number for CCID if found |
972 | p_period_year Period Year for CCID if found |
973 | p_result_status Result Code for updating line status |
974 | |
975 +=======================================================================*/
976 PROCEDURE Validate_Get_CCID_Period_Name
977 (
978 p_api_version IN NUMBER,
979 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
980 p_commit IN VARCHAR2 := FND_API.G_FALSE,
981 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
982 p_return_status OUT NOCOPY VARCHAR2,
983 p_msg_count OUT NOCOPY NUMBER,
984 p_msg_data OUT NOCOPY VARCHAR2,
985
986 p_sob_id IN gl_sets_of_books.set_of_books_id%TYPE,
987 p_effect_date IN igc_cbc_je_lines.effective_date%TYPE,
988 p_check_type IN VARCHAR2,
989 p_period_name OUT NOCOPY igc_cbc_je_lines.period_name%TYPE,
990 p_period_set_name OUT NOCOPY igc_cbc_je_lines.period_set_name%TYPE,
991 p_quarter_num OUT NOCOPY igc_cbc_je_lines.quarter_num%TYPE,
992 p_period_num OUT NOCOPY igc_cbc_je_lines.period_num%TYPE,
993 p_period_year OUT NOCOPY igc_cbc_je_lines.period_year%TYPE,
994 p_result_status OUT NOCOPY VARCHAR2
995 ) IS
996
997 -- -------------------------------------------------------------------------
998 -- Declare local variables used within fuction
999 -- -------------------------------------------------------------------------
1000 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Get_CCID_Period_Name';
1001 l_api_version CONSTANT NUMBER := 1.0;
1002 l_gl_application_id fnd_application.application_id%TYPE;
1003
1004 -- --------------------------------------------------------------------------
1005 -- Obtain the period information on the set of books ID and the effective
1006 -- date range. This is the period information to be added into the records
1007 -- for the summary and detail account records.
1008 -- --------------------------------------------------------------------------
1009 CURSOR c_igc_fc_period_info IS
1010 SELECT GPS.period_name,
1011 GP.period_set_name,
1012 GPS.period_num,
1013 GPS.period_year,
1014 GPS.quarter_num
1015 FROM gl_period_statuses GPS,
1016 gl_sets_of_books GP
1017 WHERE GPS.set_of_books_id = p_sob_id
1018 AND GPS.application_id = l_gl_application_id
1019 AND GPS.adjustment_period_flag = 'N'
1020 AND GP.set_of_books_id = GPS.set_of_books_id
1021 -- AND to_date (p_effect_date)
1022 AND p_effect_date BETWEEN GPS.start_date AND GPS.end_date
1023 AND GPS.closing_status
1024 IN ('O','F');
1025
1026 -- --------------------------------------------------------------------------
1027 -- Obtain the period information on the set of books ID and the effective
1028 -- date range. This is the period information to be added into the records
1029 -- for the summary and detail account records.
1030 -- --------------------------------------------------------------------------
1031 CURSOR c_igc_legacy_period_info IS
1032 SELECT GPS.period_name,
1033 GP.period_set_name,
1034 GPS.period_num,
1035 GPS.period_year,
1036 GPS.quarter_num
1037 FROM gl_period_statuses GPS,
1038 gl_sets_of_books GP
1039 WHERE GPS.set_of_books_id = p_sob_id
1040 AND GPS.application_id = l_gl_application_id
1041 AND GPS.adjustment_period_flag = 'N'
1042 AND GP.set_of_books_id = GPS.set_of_books_id
1043 AND GPS.closing_status = 'O'
1044 -- AND to_date (p_effect_date)
1045 AND p_effect_date BETWEEN GPS.start_date AND GPS.end_date;
1046
1047 l_full_path VARCHAR2(255);
1048
1049 BEGIN
1050
1051 l_full_path := g_path || 'Validate_Get_CCID_Period_Name';
1052
1053 SAVEPOINT Valid_Get_CCID_Per_Name_Pub;
1054
1055 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1056 p_api_version,
1057 l_api_name,
1058 G_PKG_NAME )
1059 THEN
1060 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1061 END IF;
1062
1063 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
1064 FND_MSG_PUB.initialize ;
1065 END IF;
1066
1067 -- -------------------------------------------------------------------------
1068 -- Initialize variables here.
1069 -- -------------------------------------------------------------------------
1070 p_return_status := FND_API.G_RET_STS_SUCCESS ;
1071 p_period_name := NULL;
1072 p_period_set_name := NULL;
1073 p_quarter_num := 0;
1074 p_period_num := 0;
1075 p_period_year := 0;
1076 p_result_status := NULL;
1077
1078 -- --------------------------------------------------------------------
1079 -- Obtain the application ID that will be used throughout this process.
1080 -- --------------------------------------------------------------------
1081 SELECT application_id
1082 INTO l_gl_application_id
1083 FROM fnd_application
1084 WHERE application_short_name = 'SQLGL';
1085
1086 -- -------------------------------------------------------------------------
1087 -- Based upon the type of check being performed open and fetch the period
1088 -- information that is required using the appropriate Cursor.
1089 -- -------------------------------------------------------------------------
1090 IF (p_check_type = 'FC') THEN
1091
1092 OPEN c_igc_fc_period_info;
1093 FETCH c_igc_fc_period_info
1094 INTO p_period_name,
1095 p_period_set_name,
1096 p_period_num,
1097 p_period_year,
1098 p_quarter_num;
1099
1100 IF (c_igc_fc_period_info%NOTFOUND) THEN
1101 p_result_status := 'F24';
1102 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
1103 IF g_debug_mode = 'Y' THEN
1104 Put_Debug_Msg (l_full_path, ' Period Info not Found for Funds Check Open / Future Period.');
1105 END IF;
1106 END IF;
1107
1108 ELSIF (p_check_type = 'LC') THEN
1109
1110 OPEN c_igc_legacy_period_info;
1111 FETCH c_igc_legacy_period_info
1112 INTO p_period_name,
1113 p_period_set_name,
1114 p_period_num,
1115 p_period_year,
1116 p_quarter_num;
1117
1118 IF (c_igc_legacy_period_info%NOTFOUND) THEN
1119 p_result_status := 'F24';
1120 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
1121 IF g_debug_mode = 'Y' THEN
1122 Put_Debug_Msg (l_full_path, ' Period Info not Found for Legacy Data Open Period.');
1123 END IF;
1124 END IF;
1125
1126 ELSE
1127
1128 p_result_status := 'F24';
1129 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
1130 IF g_debug_mode = 'Y' THEN
1131 Put_Debug_Msg (l_full_path, ' Period Info not Found Invalid Check Type Passed in....');
1132 END IF;
1133
1134 END IF;
1135
1136 -- -------------------------------------------------------------------------
1137 -- Make sure that all cursors have been closed before leaving this procedure
1138 -- -------------------------------------------------------------------------
1139 IF (c_igc_fc_period_info%ISOPEN) THEN
1140 CLOSE c_igc_fc_period_info;
1141 END IF;
1142
1143 IF (c_igc_legacy_period_info%ISOPEN) THEN
1144 CLOSE c_igc_legacy_period_info;
1145 END IF;
1146
1147 IF FND_API.To_Boolean ( p_commit ) THEN
1148 COMMIT WORK;
1149 END IF;
1150
1151 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
1152 p_data => p_msg_data );
1153
1154 RETURN;
1155
1156 -- -------------------------------------------------------------------------
1157 -- Exception handler section for the Validate_Get_CCID_Period_Name procedure.
1158 -- -------------------------------------------------------------------------
1159 EXCEPTION
1160
1161 WHEN FND_API.G_EXC_ERROR THEN
1162
1163 ROLLBACK TO Valid_Get_CCID_Per_Name_Pub;
1164 p_return_status := FND_API.G_RET_STS_ERROR;
1165 IF (c_igc_fc_period_info%ISOPEN) THEN
1166 CLOSE c_igc_fc_period_info;
1167 END IF;
1168 IF (c_igc_legacy_period_info%ISOPEN) THEN
1169 CLOSE c_igc_legacy_period_info;
1170 END IF;
1171
1172 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
1173 p_data => p_msg_data );
1174 IF (g_excep_level >= g_debug_level ) THEN
1175 FND_LOG.STRING (g_excep_level,l_full_path,'FND_API.G_EXC_ERROR Exception Raised');
1176 END IF;
1177 RETURN;
1178
1179 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1180
1181 ROLLBACK TO Valid_Get_CCID_Per_Name_Pub;
1182 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1183 IF (c_igc_fc_period_info%ISOPEN) THEN
1184 CLOSE c_igc_fc_period_info;
1185 END IF;
1186 IF (c_igc_legacy_period_info%ISOPEN) THEN
1187 CLOSE c_igc_legacy_period_info;
1188 END IF;
1189
1190 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
1191 p_data => p_msg_data );
1192 IF (g_excep_level >= g_debug_level ) THEN
1193 FND_LOG.STRING (g_excep_level,l_full_path,'FND_API.G_EXC_UNEXPECTED_ERROR Exception Raised');
1194 END IF;
1195 RETURN;
1196
1197 WHEN OTHERS THEN
1198
1199 ROLLBACK TO Valid_Get_CCID_Per_Name_Pub;
1200 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1201 IF (c_igc_fc_period_info%ISOPEN) THEN
1202 CLOSE c_igc_fc_period_info;
1203 END IF;
1204 IF (c_igc_legacy_period_info%ISOPEN) THEN
1205 CLOSE c_igc_legacy_period_info;
1206 END IF;
1207
1208 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1209 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
1210 l_api_name);
1211 END IF;
1212
1213 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
1214 p_data => p_msg_data );
1215
1216 IF ( g_unexp_level >= g_debug_level ) THEN
1217 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
1218 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
1219 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
1220 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
1221 END IF;
1222
1223 RETURN;
1224
1225 END Validate_Get_CCID_Period_Name;
1226
1227
1228 /*=======================================================================+
1229 | PROCEDURE Validate_Check_EFC_Enabled |
1230 | |
1231 | Note : This procedure is designed to validate the CCID Period Name |
1232 | information based upon the rules defined for the CCID to be |
1233 | entered into the CBC Funds Checker process and inserted into |
1234 | the table IGC_CBC_JE_LINES. |
1235 | |
1236 | If there is to be any changes inside of this procedure then |
1237 | there needs to be analysis performed on the effect it will have|
1238 | on the Funds Checker process. |
1239 | |
1240 | Parameters : |
1241 | |
1242 | Standard header params for Public Procedures. |
1243 | |
1244 | p_api_version Version number for API to run |
1245 | p_init_msg_list Message stack to be initialized flag |
1246 | p_commit Is work to be commited here flag |
1247 | p_validation_level Validation Level to be performed |
1248 | p_return_status Status returned from Procedure |
1249 | p_msg_count Number of messages on stack returned |
1250 | p_msg_data Message text information returned |
1251 | |
1252 | Parameters for Procedure to process properly. |
1253 | |
1254 | p_sob_id GL Set Of Books being processed |
1255 | p_efc_enabled Enhanced Funds Checker enabled Flag |
1256 | |
1257 +=======================================================================*/
1258 PROCEDURE Validate_Check_EFC_Enabled
1259 (
1260 p_api_version IN NUMBER,
1261 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1262 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1263 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1264 p_return_status OUT NOCOPY VARCHAR2,
1265 p_msg_count OUT NOCOPY NUMBER,
1266 p_msg_data OUT NOCOPY VARCHAR2,
1267
1268 p_sob_id IN gl_sets_of_books.set_of_books_id%TYPE,
1269 p_efc_enabled OUT NOCOPY VARCHAR2
1270 ) IS
1271
1272 -- --------------------------------------------------------------------
1273 -- Declare the cursors to be used during this function run.
1274 -- --------------------------------------------------------------------
1275 CURSOR c_efc_table (p_schema VARCHAR2) IS
1276 SELECT '1'
1277 FROM all_tables
1278 WHERE table_name = 'PSA_EFC_OPTIONS'
1279 AND owner = p_schema;
1280
1281 -- --------------------------------------------------------------------
1282 -- Declare local variables used within fuction
1283 -- --------------------------------------------------------------------
1284 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Check_EFC_Enabled';
1285 l_api_version CONSTANT NUMBER := 1.0;
1286 l_enable VARCHAR2(25);
1287
1288 l_full_path VARCHAR2(255);
1289
1290 -- Added for Bug 3432148
1291 l_schema fnd_oracle_userid.oracle_username%TYPE;
1292 l_prod_status fnd_product_installations.status%TYPE;
1293 l_industry fnd_product_installations.industry%TYPE;
1294
1295 BEGIN
1296
1297 l_full_path := g_path || 'Validate_Check_EFC_Enabled';
1298
1299 SAVEPOINT Validate_Check_EFC_Enabled_Pub;
1300
1301 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1302 p_api_version,
1303 l_api_name,
1304 G_PKG_NAME )
1305 THEN
1306 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1307 END IF;
1308
1309 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
1310 FND_MSG_PUB.initialize ;
1311 END IF;
1312
1313 -- --------------------------------------------------------------------
1314 -- Initialize variables here.
1315 -- --------------------------------------------------------------------
1316 p_return_status := FND_API.G_RET_STS_SUCCESS;
1317 p_efc_enabled := FND_API.G_FALSE;
1318
1319 -- Bug 3432148, added schema name in the query
1320 IF NOT fnd_installation.get_app_info (application_short_name => 'PSA',
1321 status => l_prod_status,
1322 industry => l_industry,
1323 oracle_schema => l_schema)
1324 THEN
1325 IF g_debug_mode = 'Y' THEN
1326 Put_Debug_Msg (l_full_path, 'fnd_installation.get_app_info returned FALSE ');
1327 END IF;
1328
1329 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1330 END IF;
1331
1332 OPEN c_efc_table(l_schema);
1333 FETCH c_efc_table INTO l_enable;
1334
1335 IF (l_enable IS NOT NULL) THEN
1336 BEGIN
1337 EXECUTE IMMEDIATE
1338 'SELECT mult_funding_budgets_flag FROM psa_efc_options WHERE set_of_books_id = :1'
1339 INTO l_enable
1340 USING p_sob_id;
1341
1342 EXCEPTION
1343 WHEN NO_DATA_FOUND THEN
1344 NULL; -- No record for this SOB: EFC is not enabled
1345 END;
1346 END IF;
1347
1348 CLOSE c_efc_table;
1349
1350 IF (l_enable = 'Y') THEN
1351 p_efc_enabled := FND_API.G_TRUE;
1352 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
1353 IF g_debug_mode = 'Y' THEN
1354 Put_Debug_Msg (l_full_path, 'EFC is enabled ');
1355 END IF;
1356 ELSE
1357 p_efc_enabled := FND_API.G_FALSE;
1358 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
1359 IF g_debug_mode = 'Y' THEN
1360 Put_Debug_Msg (l_full_path, 'EFC is NOT enabled ');
1361 END IF;
1362 END IF;
1363
1364 IF FND_API.To_Boolean ( p_commit ) THEN
1365 COMMIT WORK;
1366 END IF;
1367
1368 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
1369 p_data => p_msg_data );
1370
1371 RETURN;
1372
1373 -- --------------------------------------------------------------------
1374 -- Exception handler section for the Validate_Get_CCID_Period_Name procedure.
1375 -- --------------------------------------------------------------------
1376 EXCEPTION
1377
1378 WHEN FND_API.G_EXC_ERROR THEN
1379
1380 ROLLBACK TO Validate_Check_EFC_Enabled_Pub;
1381 p_return_status := FND_API.G_RET_STS_ERROR;
1382 p_efc_enabled := FND_API.G_FALSE;
1383 IF (c_efc_table%ISOPEN) THEN
1384 CLOSE c_efc_table;
1385 END IF;
1386
1387 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
1388 p_data => p_msg_data );
1389 IF (g_excep_level >= g_debug_level ) THEN
1390 FND_LOG.STRING (g_excep_level,l_full_path,'FND_API.G_EXC_ERROR Exception Raised');
1391 END IF;
1392 RETURN;
1393
1394 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1395
1396 ROLLBACK TO Validate_Check_EFC_Enabled_Pub;
1397 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1398 p_efc_enabled := FND_API.G_FALSE;
1399 IF (c_efc_table%ISOPEN) THEN
1400 CLOSE c_efc_table;
1401 END IF;
1402
1403 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
1404 p_data => p_msg_data );
1405 IF (g_excep_level >= g_debug_level ) THEN
1406 FND_LOG.STRING (g_excep_level,l_full_path,'FND_API.G_EXC_UNEXPECTED_ERROR Exception Raised');
1407 END IF;
1408 RETURN;
1409
1410 WHEN OTHERS THEN
1411
1412 ROLLBACK TO Validate_Check_EFC_Enabled_Pub;
1413 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1414 p_efc_enabled := FND_API.G_FALSE;
1415 IF (c_efc_table%ISOPEN) THEN
1416 CLOSE c_efc_table;
1417 END IF;
1418
1419 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1420 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
1421 l_api_name);
1422 END IF;
1423
1424 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
1425 p_data => p_msg_data );
1426 IF ( g_unexp_level >= g_debug_level ) THEN
1427 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
1428 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
1429 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
1430 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
1431 END IF;
1432
1433 RETURN;
1434
1435 END Validate_Check_EFC_Enabled;
1436
1437
1438 /*=======================================================================+
1439 | PROCEDURE Validate_CC_Interface |
1440 | |
1441 | Note : This procedure is designed to validate the CC Interface table |
1442 | information based upon the rules defined for the CCID to be |
1443 | entered into the CBC Funds Checker process and inserted into |
1444 | the table IGC_CBC_JE_LINES. |
1445 | |
1446 | If there is to be any changes inside of this procedure then |
1447 | there needs to be analysis performed on the effect it will have|
1448 | on the Funds Checker process. |
1449 | |
1450 | Parameters : |
1451 | |
1452 | Standard header params for Public Procedures. |
1453 | |
1454 | p_api_version Version number for API to run |
1455 | p_init_msg_list Message stack to be initialized flag |
1456 | p_commit Is work to be commited here flag |
1457 | p_validation_level Validation Level to be performed |
1458 | p_return_status Status returned from Procedure |
1459 | p_msg_count Number of messages on stack returned |
1460 | p_msg_data Message text information returned |
1461 | |
1462 | Parameters for Procedure to process properly. |
1463 | |
1464 | p_sob_id GL Set Or Books ID being processed |
1465 | p_cbc_enabled Commitment Budgetary Control enabled flag |
1466 | p_cc_head_id Contract Commitment Header ID |
1467 | p_actl_flag Actual Flag for GL processing |
1468 | p_documt_type Contract Commitment Document Type |
1469 | p_sum_line_num Summary Template Line Number |
1470 | p_cbc_flag Is there CBC Lines present in table |
1471 | p_sbc_flag Is there SBC Lines present in table |
1472 | p_packet_id packet_id, if originated in Purchasing |
1473 | |
1474 +=======================================================================*/
1475 PROCEDURE Validate_CC_Interface
1476 (
1477 p_api_version IN NUMBER,
1478 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1479 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1480 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1481 p_return_status OUT NOCOPY VARCHAR2,
1482 p_msg_count OUT NOCOPY NUMBER,
1483 p_msg_data OUT NOCOPY VARCHAR2,
1484
1485 p_sob_id IN gl_sets_of_books.set_of_books_id%TYPE,
1486 p_cbc_enabled IN VARCHAR2,
1487 p_cc_head_id IN igc_cbc_je_batches.cc_header_id%TYPE,
1488 p_actl_flag IN VARCHAR2,
1489 p_documt_type IN igc_cc_interface.document_type%TYPE,
1490 -- p_sum_line_num OUT NOCOPY igc_cbc_je_lines.cbc_je_line_num%TYPE,
1491 p_cbc_flag OUT NOCOPY VARCHAR2,
1492 p_sbc_flag OUT NOCOPY VARCHAR2
1493 -- p_packet_id IN NUMBER
1494 ) IS
1495
1496 -- --------------------------------------------------------------------
1497 -- Declare the cursors to be used during this function run.
1498 -- --------------------------------------------------------------------
1499 CURSOR c_cbc_count IS --Check if CBC records in the interface table
1500 SELECT count(*)
1501 /*
1502 ,max(batch_line_num)
1503 */
1504 FROM igc_cc_interface_v
1505 WHERE budget_dest_flag ='C'
1506 AND cc_header_id = p_cc_head_id
1507 AND actual_flag = p_actl_flag
1508 AND document_type = p_documt_type;
1509
1510 CURSOR c_sbc_count IS --Check if SBC records in the interface table
1511 SELECT count(*)
1512 FROM igc_cc_interface_v
1513 WHERE budget_dest_flag = 'S'
1514 AND cc_header_id = p_cc_head_id
1515 AND actual_flag = p_actl_flag
1516 AND document_type = p_documt_type;
1517
1518 CURSOR c_sob_count IS -- Check sob in the table, must be 1 or 0
1519 SELECT count(DISTINCT set_of_books_id)
1520 FROM igc_cc_interface_v
1521 WHERE cc_header_id = p_cc_head_id
1522 AND actual_flag = p_actl_flag
1523 AND document_type = p_documt_type;
1524
1525 /* THIS CHECK IS NOT REQIRED ANYMORE BECAUSE OF PA INTEGRATION
1526 CURSOR c_result_count IS --Check result code in the table, must be 0
1527 SELECT count(*)
1528 FROM igc_cc_interface_v
1529 WHERE cc_header_id = p_cc_head_id
1530 AND actual_flag = p_actl_flag
1531 AND document_type = p_documt_type
1532 AND ( cbc_result_code IS NOT NULL
1533 OR status_code IS NOT NULL ); */
1534 /*
1535 --R12 uptake. Encumbrance Details are seeded in R12. Bug No 6341012
1536 CURSOR c_enc_count IS --Check encumbrance_type_id Must be 0
1537 SELECT count(*)
1538 FROM igc_cc_interface_v
1539 WHERE cc_header_id = p_cc_head_id
1540 AND actual_flag = 'E'
1541 AND document_type = p_documt_type
1542 AND encumbrance_type_id IS NULL;
1543 */
1544 -- ssmales 29/01/02 bug 2201905 - added three new cursors below
1545 -- bug 2201905 start block
1546 /*
1547 --Packet ID does not exist in R12. Hence Commented. Bug No 6341012
1548 CURSOR c_cbc_count_packet IS --Check if CBC records in the interface table
1549 SELECT count(*),
1550 max(batch_line_num)
1551 FROM igc_cc_interface_v
1552 WHERE budget_dest_flag ='C'
1553 AND reference_6 = p_packet_id
1554 AND actual_flag = p_actl_flag ;
1555
1556 CURSOR c_sbc_count_packet IS --Check if SBC records in the interface table
1557 SELECT count(*)
1558 FROM igc_cc_interface_v
1559 WHERE budget_dest_flag = 'S'
1560 AND reference_6 = p_packet_id
1561 AND actual_flag = p_actl_flag ;
1562
1563 CURSOR c_enc_count_packet IS --Check encumbrance_type_id Must be 0
1564 SELECT count(*)
1565 FROM igc_cc_interface_v
1566 WHERE reference_6 = p_packet_id
1567 AND actual_flag = 'E'
1568 AND encumbrance_type_id IS NULL;
1569 */
1570 -- bug 2201905 end block
1571
1572 -- -------------------------------------------------------------------------
1573 -- Declare local variables used within fuction
1574 -- -------------------------------------------------------------------------
1575 l_cbc_count NUMBER := 0;
1576 l_sbc_count NUMBER := 0;
1577 l_sob_count NUMBER := 0;
1578 l_result_count NUMBER := 0;
1579 l_enc_count NUMBER := 0;
1580 l_api_name CONSTANT VARCHAR2(30) := 'Validate_CC_Interface';
1581 l_api_version CONSTANT NUMBER := 1.0;
1582
1583 l_full_path VARCHAR2(255);
1584
1585 BEGIN
1586
1587 l_full_path := g_path || 'Validate_CC_Interface';
1588
1589 SAVEPOINT Validate_CC_Interface_Pub;
1590
1591 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1592 p_api_version,
1593 l_api_name,
1594 G_PKG_NAME )
1595 THEN
1596 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1597 END IF;
1598
1599 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
1600 FND_MSG_PUB.initialize ;
1601 END IF;
1602
1603 -- -------------------------------------------------------------------------
1604 -- Initialize variables here.
1605 -- -------------------------------------------------------------------------
1606 p_return_status := FND_API.G_RET_STS_SUCCESS ;
1607 p_cbc_flag := FND_API.G_TRUE;
1608 p_sbc_flag := FND_API.G_TRUE;
1609
1610
1611 -- ssmales 29/01/02 bug 2201905 - added if block below
1612 -- bug 2201905 start block
1613 /*
1614 R12 Uptake. Packet ID is obsolete. Bug No 6341012
1615 Cursor no longer required
1616 IF (p_packet_id is not null) THEN
1617
1618 OPEN c_cbc_count_packet;
1619 FETCH c_cbc_count_packet
1620 INTO l_cbc_count
1621 ,p_sum_line_num;
1622
1623
1624 CLOSE c_cbc_count_packet;
1625
1626 ELSE
1627 */
1628 -- bug 2201905 end block
1629
1630 OPEN c_cbc_count;
1631 FETCH c_cbc_count
1632 INTO l_cbc_count;
1633 -- ,p_sum_line_num;
1634
1635 CLOSE c_cbc_count;
1636
1637 -- ssmales 29/01/02 bug 2201905 - added end if statement below
1638 -- END IF ;
1639
1640 IF (l_cbc_count = 0) THEN
1641 p_cbc_flag := FND_API.G_FALSE;
1642 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
1643 IF g_debug_mode = 'Y' THEN
1644 Put_Debug_Msg (l_full_path, ' No CBC Records in CC Interface ');
1645 END IF;
1646 END IF;
1647
1648 Put_Debug_Msg (l_full_path, 'CBC flag: ' || p_cbc_flag || ' CBC Enabled: ' || p_cbc_enabled);
1649
1650 IF FND_API.TO_BOOLEAN(p_cbc_flag) AND (NOT FND_API.TO_BOOLEAN(p_cbc_enabled)) THEN
1651 message_token ('SOB_ID', p_sob_id);
1652 add_message ('IGC', 'IGC_BC_NOT_ENABLED'); -- BC is disabled, no encumbrances allowed
1653 raise FND_API.G_EXC_ERROR;
1654 END IF;
1655
1656 -- ssmales 29/01/02 bug 2201905 - added if block below
1657 -- bug 2201905 start block
1658 /*
1659 R12 Uptake. Packet ID is obsolete. Bug No 6341012
1660 Cursor no longer required
1661
1662 IF (p_packet_id is not null) THEN
1663
1664 OPEN c_sbc_count_packet;
1665 FETCH c_sbc_count_packet
1666 INTO l_sbc_count;
1667
1668 CLOSE c_sbc_count_packet;
1669
1670 ELSE
1671 */
1672 -- bug 2201905 end block
1673
1674 OPEN c_sbc_count;
1675 FETCH c_sbc_count
1676 INTO l_sbc_count;
1677
1678 CLOSE c_sbc_count;
1679
1680 -- ssmales 29/01/02 bug 2201905 - added end if statement below
1681 -- END IF ;
1682
1683 IF (l_sbc_count = 0) THEN
1684 p_sbc_flag := FND_API.G_FALSE;
1685 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
1686 IF g_debug_mode = 'Y' THEN
1687 Put_Debug_Msg (l_full_path, ' No SBC Records in CC Interface ');
1688 END IF;
1689 END IF;
1690
1691
1692 /* Changed per change request. No rows - no erorr thrown
1693
1694 IF ((NOT FND_API.TO_BOOLEAN(p_sbc_flag)) AND
1695 (NOT FND_API.TO_BOOLEAN(p_cbc_flag))) THEN
1696 message_token ('CC_HEADER_ID', to_char(p_cc_head_id));
1697 message_token ('ACTUAL_FLAG', p_actl_flag);
1698 add_message ('IGC', 'IGC_VALIDATE_NO_ROWS'); --No rows in the interface table to check
1699 raise FND_API.G_EXC_ERROR;
1700 END IF;
1701 */
1702
1703 /*
1704 -- Bidisha S, 2093525. Not quite sure whether this is required anymore
1705 -- as we could have multiple set of books with MRC enabled.
1706
1707 OPEN c_sob_count;
1708 FETCH c_sob_count
1709 INTO l_sob_count;
1710
1711 CLOSE c_sob_count;
1712
1713 IF (NVL(l_sob_count,0) > 1) THEN
1714 message_token ('CC_HEADER_ID', to_char(p_cc_head_id));
1715 message_token ('ACTUAL_FLAG', p_actl_flag);
1716 add_message ('IGC', 'IGC_VALIDATE_SOB'); --Not one set of books in the batch
1717 raise FND_API.G_EXC_ERROR;
1718 END IF;
1719 */
1720
1721
1722 -- ssmales 29/01/02 bug 2201905 - added if block below
1723 -- bug 2201905 start block
1724 /*
1725 R12 Uptake. Packet ID is obsolete. Bug No 6341012
1726 Cursor no longer valid
1727 IF (p_packet_id is not null) THEN
1728
1729 OPEN c_enc_count_packet;
1730 FETCH c_enc_count_packet
1731 INTO l_enc_count;
1732
1733 CLOSE c_enc_count_packet;
1734
1735 ELSE
1736
1737 -- bug 2201905 end block
1738
1739 OPEN c_enc_count;
1740 FETCH c_enc_count
1741 INTO l_enc_count;
1742
1743 CLOSE c_enc_count;
1744
1745 -- ssmales 29/01/02 bug 2201905 - added end if statement below
1746 END IF ;
1747
1748 IF (l_enc_count <> 0) THEN
1749 message_token ('CC_HEADER_ID', to_char(p_cc_head_id));
1750 message_token ('ACTUAL_FLAG', 'E');
1751 add_message ('IGC', 'IGC_VALIDATE_ENC_CODE'); -- Records without encumbrance type id
1752 raise FND_API.G_EXC_ERROR;
1753 END IF;
1754 */
1755 IF FND_API.To_Boolean ( p_commit ) THEN
1756 COMMIT WORK;
1757 END IF;
1758
1759 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
1760 p_data => p_msg_data );
1761
1762 RETURN;
1763
1764 -- -------------------------------------------------------------------------
1765 -- Exception handler section for the Validate_Get_CCID_Period_Name procedure.
1766 -- -------------------------------------------------------------------------
1767 EXCEPTION
1768
1769 WHEN FND_API.G_EXC_ERROR THEN
1770
1771 ROLLBACK TO Validate_CC_Interface_Pub;
1772 p_return_status := FND_API.G_RET_STS_ERROR;
1773 IF (c_cbc_count%ISOPEN) THEN
1774 CLOSE c_cbc_count;
1775 END IF;
1776 IF (c_sbc_count%ISOPEN) THEN
1777 CLOSE c_sbc_count;
1778 END IF;
1779 IF (c_sob_count%ISOPEN) THEN
1780 CLOSE c_sob_count;
1781 END IF;
1782 /*
1783 IF (c_enc_count%ISOPEN) THEN
1784 CLOSE c_enc_count;
1785 END IF;
1786 */
1787 -- ssmales 29/01/02 bug 2201905 - added block below
1788 -- bug 2201905 start block
1789 /*
1790 IF (c_cbc_count_packet%ISOPEN) THEN
1791 CLOSE c_cbc_count_packet;
1792 END IF;
1793 IF (c_sbc_count_packet%ISOPEN) THEN
1794 CLOSE c_sbc_count_packet;
1795 END IF;
1796 IF (c_enc_count_packet%ISOPEN) THEN
1797 CLOSE c_enc_count_packet;
1798 END IF;
1799 */
1800 -- bug 2201905 end block
1801 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
1802 p_data => p_msg_data );
1803 IF (g_excep_level >= g_debug_level ) THEN
1804 FND_LOG.STRING (g_excep_level,l_full_path,'FND_API.G_EXC_ERROR Exception Raised');
1805 END IF;
1806
1807 RETURN;
1808
1809 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1810
1811 ROLLBACK TO Validate_CC_Interface_Pub;
1812 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1813 IF (c_cbc_count%ISOPEN) THEN
1814 CLOSE c_cbc_count;
1815 END IF;
1816 IF (c_sbc_count%ISOPEN) THEN
1817 CLOSE c_sbc_count;
1818 END IF;
1819 IF (c_sob_count%ISOPEN) THEN
1820 CLOSE c_sob_count;
1821 END IF;
1822 -- ssmales 29/01/02 bug 2201905 - added block below
1823 -- bug 2201905 start block
1824 /*
1825 IF (c_cbc_count_packet%ISOPEN) THEN
1826 CLOSE c_cbc_count_packet;
1827 END IF;
1828 IF (c_sbc_count_packet%ISOPEN) THEN
1829 CLOSE c_sbc_count_packet;
1830 END IF;
1831 IF (c_enc_count_packet%ISOPEN) THEN
1832 CLOSE c_enc_count_packet;
1833 END IF;
1834 */
1835 -- bug 2201905 end block
1836 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
1837 p_data => p_msg_data );
1838 IF (g_excep_level >= g_debug_level ) THEN
1839 FND_LOG.STRING (g_excep_level,l_full_path,'FND_API.G_EXC_UNEXPECTED_ERROR Exception Raised');
1840 END IF;
1841
1842 RETURN;
1843
1844 WHEN OTHERS THEN
1845
1846 ROLLBACK TO Validate_CC_Interface_Pub;
1847 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1848 IF (c_cbc_count%ISOPEN) THEN
1849 CLOSE c_cbc_count;
1850 END IF;
1851 IF (c_sbc_count%ISOPEN) THEN
1852 CLOSE c_sbc_count;
1853 END IF;
1854 IF (c_sob_count%ISOPEN) THEN
1855 CLOSE c_sob_count;
1856 END IF;
1857 -- ssmales 29/01/02 bug 2201905 - added block below
1858 -- bug 2201905 start block
1859 /*
1860 IF (c_cbc_count_packet%ISOPEN) THEN
1861 CLOSE c_cbc_count_packet;
1862 END IF;
1863 IF (c_sbc_count_packet%ISOPEN) THEN
1864 CLOSE c_sbc_count_packet;
1865 END IF;
1866 IF (c_enc_count_packet%ISOPEN) THEN
1867 CLOSE c_enc_count_packet;
1868 END IF;
1869 */
1870 -- bug 2201905 end block
1871 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1872 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
1873 l_api_name);
1874 END IF;
1875
1876 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
1877 p_data => p_msg_data );
1878 IF ( g_unexp_level >= g_debug_level ) THEN
1879 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
1880 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
1881 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
1882 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
1883 END IF;
1884
1885 RETURN;
1886
1887 END Validate_CC_Interface;
1888 END IGC_CBC_VALIDATIONS_PKG;
1889
[Home] [Help]