[Home] [Help]
PACKAGE BODY: APPS.IGC_CC_OPN_UPD_GET_LNK_PUB
Source
1 PACKAGE BODY IGC_CC_OPN_UPD_GET_LNK_PUB AS
2 /* $Header: IGCOUGLB.pls 120.10.12000000.5 2007/10/25 14:29:21 smannava ship $ */
3
4 -- --------------------------------------------------------------------
5 -- Define Global variables for package below
6 -- --------------------------------------------------------------------
7
8 G_PKG_NAME VARCHAR2(30) ;
9 g_debug_msg VARCHAR2(10000);
10
11 -- l_debug_mode VARCHAR2(1) := NVL(FND_PROFILE.VALUE('IGC_DEBUG_ENABLED'),'N');
12
13 -- Variables for logging levels
14 --bug 3199488
15 g_debug_level NUMBER;
16 g_state_level NUMBER;
17 g_proc_level NUMBER;
18 g_event_level NUMBER;
19 g_excep_level NUMBER;
20 g_error_level NUMBER;
21 g_unexp_level NUMBER;
22 g_path VARCHAR2(255);
23 g_debug_mode VARCHAR2(1);
24
25 --
26 -- Generic Procedure for putting out NOCOPY debug information
27 --
28 PROCEDURE Output_Debug (
29 p_path IN VARCHAR2,
30 p_debug_msg IN VARCHAR2
31 );
32
33 --
34 -- This procedure is called with cc_open_api_main procedure
35 -- This validates the entire record.
36 --
37 PROCEDURE CC_OPEN_API_VALIDATE (
38 p_cc_header_rec IN OUT NOCOPY CC_HEADER_REC_TYPE,
39 p_current_org_id IN igc_cc_headers.org_id%TYPE,
40 p_current_sob_id IN igc_cc_headers.set_of_books_id%TYPE,
41 p_func_currency_code IN igc_cc_headers.currency_code%TYPE,
42 x_valid_cc OUT NOCOPY VARCHAR2,
43 x_currency_code OUT NOCOPY igc_cc_headers.currency_code%TYPE,
44 x_conversion_type OUT NOCOPY igc_cc_headers.conversion_type%TYPE,
45 x_conversion_date OUT NOCOPY igc_cc_headers.conversion_date%TYPE,
46 x_conversion_rate OUT NOCOPY igc_cc_headers.conversion_rate%TYPE
47 );
48
49 --
50 -- This procedure is called with cc_open_api_main procedure
51 -- This procedure is used to derive the cc_header_id number.
52
53 PROCEDURE CC_OPEN_API_DERIVE (
54 x_header_id OUT NOCOPY NUMBER
55 );
56
57 -- Main program which selects all the records from Header PL-SQL table
58 -- and calls other programs for processing
59 PROCEDURE CC_Open_API_Main (
60 p_api_version IN NUMBER,
61 p_init_msg_list IN VARCHAR2 ,
62 p_commit IN VARCHAR2,
63 p_validation_level IN NUMBER,
64 p_cc_header_rec IN CC_HEADER_REC_TYPE,
65 x_return_status OUT NOCOPY VARCHAR2,
66 x_msg_count OUT NOCOPY NUMBER,
67 x_msg_data OUT NOCOPY VARCHAR2
68 ) IS
69
70 l_api_name VARCHAR2(30);
71 l_api_version NUMBER ;
72 l_debug VARCHAR2 (1);
73 l_valid_cc VARCHAR2(2000);
74 l_error_status VARCHAR2(1);
75 l_current_org_id NUMBER;
76 l_current_user_id NUMBER;
77 l_current_login_id NUMBER;
78 l_current_set_of_books_id NUMBER;
79 l_row_id VARCHAR2(18);
80 l_flag VARCHAR2(1);
81 l_header_id NUMBER;
82 l_parent_header_id NUMBER;
83 l_func_currency_code VARCHAR2(15);
84 l_return_status VARCHAR2(1);
85 l_msg_count NUMBER;
86 l_msg_data VARCHAR2(12000);
87 l_error_text VARCHAR2(12000);
88 l_msg_buf VARCHAR2(2000);
89 l_error_message VARCHAR2(240);
90 l_cc_header_rec CC_HEADER_REC_TYPE;
91 l_status VARCHAR2(240);
92 x_valid_cc VARCHAR2(2000);
93 x_currency_code igc_cc_headers.currency_code%TYPE;
94 x_conversion_type igc_cc_headers.conversion_type%TYPE;
95 x_conversion_date igc_cc_headers.conversion_date%TYPE;
96 x_conversion_rate igc_cc_headers.conversion_rate%TYPE;
97 l_full_path VARCHAR(500);
98
99
100 l_init_msg_list VARCHAR2(2000);
101 l_commit VARCHAR2(2000);
102 l_validation_level NUMBER;
103 BEGIN
104 --Added by svaithil for GSCC warnings
105
106 l_init_msg_list := nvl(p_init_msg_list,FND_API.G_FALSE);
107 l_commit := nvl(p_commit,FND_API.G_FALSE);
108 l_validation_level := nvl(p_validation_level,FND_API.G_VALID_LEVEL_FULL);
109 l_api_name := 'CC_Open_API_Main';
110 l_api_version := 1.0;
111 l_error_status := NVL(l_error_status,'N');
112
113 -- -------------------------------------------------------------------
114 -- Initialize the return values
115 -- -------------------------------------------------------------------
116 x_return_status := FND_API.G_RET_STS_SUCCESS;
117 x_msg_data := NULL;
118 x_msg_count := 0;
119 SAVEPOINT CC_Open_API_PT;
120 l_full_path := g_path||'cc_open_api_main';
121
122 -- -------------------------------------------------------------------
123 -- Setup Debug info for API usage if needed.
124 -- -------------------------------------------------------------------
125 -- l_debug := FND_PROFILE.VALUE('IGC_DEBUG_ENABLED');
126 IF (g_debug_mode = 'Y') THEN
127 l_debug := FND_API.G_TRUE;
128 ELSE
129 l_debug := FND_API.G_FALSE;
130 END IF;
131 IGC_MSGS_PKG.g_debug_mode := FND_API.TO_BOOLEAN(l_debug);
132 IF g_debug_mode = 'Y'
133 THEN
134 g_debug_msg := 'CC Open API Main debug mode enabled...';
135 Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
136 END IF;
137
138 -- -------------------------------------------------------------------
139 -- Make sure that the appropriate version is being used
140 -- -------------------------------------------------------------------
141 IF (NOT FND_API.Compatible_API_Call ( l_api_version,
142 p_api_version,
143 l_api_name,
144 G_PKG_NAME )) THEN
145 g_debug_msg := 'CC Open API Main Incorrect Version...';
146 IF(g_excep_level >= g_debug_level) THEN
147 FND_LOG.STRING(g_excep_level, l_full_path,g_debug_msg );
148 END IF;
149 -- Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
150 raise FND_API.G_EXC_UNEXPECTED_ERROR ;
151 END IF;
152
153 -- -------------------------------------------------------------------
154 -- Make sure that if the message stack is to be initialized it is.
155 -- -------------------------------------------------------------------
156 IF (FND_API.to_Boolean ( l_init_msg_list )) THEN
157 FND_MSG_PUB.initialize ;
158 END IF;
159
160 -- -------------------------------------------------------------------
161 -- Open API starts here.
162 -- -------------------------------------------------------------------
163 l_cc_header_rec := p_cc_header_rec;
164
165 -- -------------------------------------------------------------------
166 -- Get the profile values
167 -- -------------------------------------------------------------------
168 l_current_org_id := NVL(SUBSTRB(USERENV('CLIENT_INFO'),1,10),-99);
169 IF (l_current_org_id = -99) THEN
170 dbms_application_info.set_client_info(l_cc_header_rec.org_id);
171 l_current_org_id := l_cc_header_rec.org_id;
172 END IF;
173 l_current_set_of_books_id := l_cc_header_rec.set_of_books_id;
174 l_current_user_id := l_cc_header_rec.last_updated_by;
175 l_current_login_id := l_cc_header_rec.last_update_login;
176
177 -- -------------------------------------------------------------------
178 -- Get the Functional Currency Code
179 -- -------------------------------------------------------------------
180 BEGIN
181
182 l_full_path := g_path||'cc_open_api_main';
183
184 SELECT currency_code INTO l_func_currency_code
185 FROM gl_sets_of_books
186 WHERE set_of_books_id = l_current_set_of_books_id;
187
188 EXCEPTION
189
190 WHEN NO_DATA_FOUND THEN
191 g_debug_msg := 'CC Open API Main Unable to get functional currency...';
192 -- Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
193 IF(g_excep_level >= g_debug_level) THEN
194 FND_LOG.STRING(g_excep_level, l_full_path,g_debug_msg );
195 END IF;
196
197 -- Bug 3199488
198 IF ( g_excep_level >= g_debug_level ) THEN
199 FND_LOG.STRING (g_excep_level,l_full_path,'NO_DATA_FOUND Exception Raised');
200 END IF;
201 -- Bug 3199488
202 NULL;
203 END;
204
205 -- -------------------------------------------------------------------
206 -- Header record validation.
207 -- -------------------------------------------------------------------
208 IF g_debug_mode = 'Y'
209 THEN
210 g_debug_msg := 'CC Open API Main Header Record Validation Starts Here...';
211 Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
212 END IF;
213
214 l_valid_cc := FND_API.G_FALSE;
215
216 CC_OPEN_API_VALIDATE (l_cc_header_rec,
217 l_current_org_id,
218 l_current_set_of_books_id,
219 l_func_currency_code,
220 x_valid_cc,
221 x_currency_code,
222 x_conversion_type,
223 x_conversion_date,
224 x_conversion_rate
225 );
226
227 -- -------------------------------------------------------------------
228 -- If validation succeeds, get the derived values and insert header record.
229 -- -------------------------------------------------------------------
230 IF (x_valid_cc <> FND_API.G_TRUE) THEN
231
232 x_msg_data := FND_MESSAGE.GET;
233 x_return_status := FND_API.G_RET_STS_ERROR;
234 x_msg_count := 1;
235
236 IF g_debug_mode = 'Y'
237 THEN
238 g_debug_msg := 'CC Open API Main Header Validation Not Successful...';
239 IF(g_excep_level >= g_debug_level) THEN
240 FND_LOG.STRING(g_excep_level, l_full_path,g_debug_msg );
241 END IF;
242 --Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
243 END IF;
244
245 ELSE
246
247 IF g_debug_mode = 'Y'
248 THEN
249 g_debug_msg := 'CC Open API Main Header Id Derivation Starts Here...';
250 Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
251 END IF;
252
253 CC_OPEN_API_DERIVE ( l_header_id );
254
255 IF g_debug_mode = 'Y'
256 THEN
257 g_debug_msg := 'CC Open API Main Header Record Insert Row Starts Here...';
258 Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
259 END IF;
260
261 IF (l_header_id IS NULL) THEN
262
263 -- --------------------------------------------------------------------
264 -- Failure in retrieving the sequesnce number for the Header ID.
265 -- --------------------------------------------------------------------
266 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_NO_CC_HDR_SEQ');
267 FND_MSG_PUB.ADD;
268 RAISE FND_API.G_EXC_ERROR;
269
270 ELSE
271
272 IGC_CC_HEADERS_PKG.Insert_Row (1.0,
273 FND_API.G_FALSE,
274 FND_API.G_FALSE,
275 FND_API.G_VALID_LEVEL_FULL,
276 l_return_status,
277 l_msg_count,
278 l_msg_data,
279 l_row_id,
280 l_header_id,
281 l_cc_header_rec.org_id,
282 l_cc_header_rec.CC_Type,
283 l_cc_header_rec.CC_Num,
284 l_cc_header_rec.CC_Ref_Num,
285 0, -- CC version number
286 l_cc_header_rec.parent_header_id,
287 'PR', -- CC state
288 'E', -- CC Control Status
289 'N', -- CC Encumbrance Status
290 'IN', -- CC Approval Status
291 l_cc_header_rec.Vendor_Id,
292 l_cc_header_rec.Vendor_Site_Id,
293 l_cc_header_rec.Vendor_Contact_Id,
294 l_cc_header_rec.Term_Id,
295 l_cc_header_rec.Location_Id,
296 l_cc_header_rec.Set_Of_Books_Id,
297 NULL, -- CC_Acct_Date
298 l_cc_header_rec.CC_Desc,
299 l_cc_header_rec.CC_Start_Date,
300 l_cc_header_rec.CC_End_Date,
301 l_cc_header_rec.CC_Owner_User_Id,
302 l_cc_header_rec.CC_Preparer_User_Id,
303 x_currency_code,
304 x_conversion_type,
305 x_conversion_date,
306 x_conversion_rate,
307 SYSDATE,
308 l_current_user_id,
309 l_current_login_id,
310 NVL(l_cc_header_rec.Created_By, l_current_user_id),
311 NVL(l_cc_header_rec.Creation_Date, sysdate),
312 l_cc_header_rec.CC_Preparer_User_Id, -- CC_Current_User_Id,
313 NULL, -- Wf_Item_Type,
314 NULL, -- Wf_Item_Key,
315 l_cc_header_rec.Attribute1,
316 l_cc_header_rec.Attribute2,
317 l_cc_header_rec.Attribute3,
318 l_cc_header_rec.Attribute4,
319 l_cc_header_rec.Attribute5,
320 l_cc_header_rec.Attribute6,
321 l_cc_header_rec.Attribute7,
322 l_cc_header_rec.Attribute8,
323 l_cc_header_rec.Attribute9,
324 l_cc_header_rec.Attribute10,
325 l_cc_header_rec.Attribute11,
326 l_cc_header_rec.Attribute12,
327 l_cc_header_rec.Attribute13,
328 l_cc_header_rec.Attribute14,
329 l_cc_header_rec.Attribute15,
330 l_cc_header_rec.Context,
331 l_cc_header_rec.CC_Guarantee_Flag,
332 l_flag
333 );
334 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
335
336 ROLLBACK to CC_Open_API_PT;
337 x_msg_data := FND_MESSAGE.GET;
338 x_return_status := FND_API.G_RET_STS_ERROR;
339 x_msg_count := 1;
340 g_debug_msg := 'CC Open API Main Header Record Insert Row Not Successful...'||l_msg_data;
341 -- Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
342 IF(g_excep_level >= g_debug_level) THEN
343 FND_LOG.STRING(g_excep_level, l_full_path,g_debug_msg );
344 END IF;
345 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
346
347 ELSE
348
349 IF (FND_API.To_Boolean(l_commit)) THEN
350 IF g_debug_mode = 'Y'
351 THEN
352 g_debug_msg := 'CC Open API Main Commiting CC header Record...';
353 Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
354 END IF;
355 COMMIT WORK;
356 END IF;
357
358 END IF;
359
360 END IF;
361
362 END IF;
363
364 RETURN;
365
366 -- --------------------------------------------------------------------
367 -- Exception handler section for the CC_Open_API_Main Procedure.
368 -- --------------------------------------------------------------------
369 EXCEPTION
370
371 WHEN FND_API.G_EXC_ERROR THEN
372 ROLLBACK to CC_Open_API_PT;
373 x_msg_data := FND_MESSAGE.GET;
374 x_return_status := FND_API.G_RET_STS_ERROR;
375 x_msg_count := 1;
376
377 -- Bug 3199488
378 IF ( g_excep_level >= g_debug_level ) THEN
379 FND_LOG.STRING (g_excep_level,l_full_path,'FND_API.G_EXC_ERROR Exception Raised');
380 END IF;
381 -- Bug 3199488
382 RETURN;
383
384 WHEN OTHERS THEN
385 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
386 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
387 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
388 END IF;
389 -- Bug 3199488
390 IF ( g_unexp_level >= g_debug_level ) THEN
391 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
392 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
393 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
394 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
395 END IF;
396 -- Bug 3199488
397 RETURN;
398
399 END CC_Open_API_Main;
400
401
402 -- To perform validations on the CC Header record.
403 -- Validate the CC Header record and return the result
404
405 PROCEDURE CC_OPEN_API_VALIDATE (
406 p_cc_header_rec IN OUT NOCOPY CC_HEADER_REC_TYPE,
407 p_current_org_id IN igc_cc_headers.org_id%TYPE,
408 p_current_sob_id IN igc_cc_headers.set_of_books_id%TYPE,
409 p_func_currency_code IN igc_cc_headers.currency_code%TYPE,
410 x_valid_cc OUT NOCOPY VARCHAR2,
411 x_currency_code OUT NOCOPY igc_cc_headers.currency_code%TYPE,
412 x_conversion_type OUT NOCOPY igc_cc_headers.conversion_type%TYPE,
413 x_conversion_date OUT NOCOPY igc_cc_headers.conversion_date%TYPE,
414 x_conversion_rate OUT NOCOPY igc_cc_headers.conversion_rate%TYPE
415 ) IS
416
417 l_api_name VARCHAR2(30);
418 l_error_message VARCHAR2(240);
419 l_error_count NUMBER;
420 l_count NUMBER;
421 l_valid_cc VARCHAR2(2000);
422 l_return_status VARCHAR2(1);
423 l_msg_count NUMBER;
424 l_msg_data VARCHAR2(12000);
425 l_encumbrance_flag VARCHAR2(1);
426 l_relation_flag VARCHAR2(1);
427 l_parent_header_id NUMBER;
428 l_vendor_id NUMBER;
429 l_vendor_site_id NUMBER;
430 l_vendor_contact_id NUMBER;
431 l_term_id NUMBER;
432 l_location_id NUMBER;
433 l_populate_terms_id NUMBER;
434 l_billed_to_location_id NUMBER;
435 l_vendor_curr_code VARCHAR2(15);
436 l_vendor_site_curr_code VARCHAR2(15);
437 l_currency_code VARCHAR2(15);
438 l_cov_curr_code VARCHAR2(15);
439 l_cov_conversion_type VARCHAR2(30);
440 l_cov_conversion_rate NUMBER;
441 l_cov_conversion_date DATE;
442 l_conversion_type VARCHAR2(30);
443 l_conversion_rate NUMBER;
444 l_conversion_date DATE;
445 l_set_of_books_id NUMBER;
446 l_user_id NUMBER;
447 l_login_id NUMBER;
448 l_cc_num_method igc_cc_system_options_all.cc_num_method%TYPE;
449 l_cc_num_datatype igc_cc_system_options_all.cc_num_datatype%TYPE;
450 l_cc_num_created NUMBER;
451 l_org_name hr_organization_units.name%TYPE;
452 l_sob_id igc_cc_headers.set_of_books_id%TYPE;
453 l_cc_num igc_cc_headers.cc_num%TYPE;
454 l_name hr_all_organization_units.name%TYPE;
455 l_full_path VARCHAR(500);
456
457 CURSOR c_validate_sob_id IS
458 SELECT GL.set_of_books_id
459 FROM gl_sets_of_books GL
460 WHERE GL.set_of_books_id = p_cc_header_rec.set_of_books_id;
461
462 CURSOR c_validate_org_id IS
463 SELECT name
464 FROM hr_organization_units
465 WHERE organization_id = p_cc_header_rec.org_id;
466
467 CURSOR c_validate_sob_org_combo IS
468 SELECT HAOU.name
469 FROM hr_organization_information OOD,
470 hr_all_organization_units HAOU
471 WHERE OOD.organization_id = p_cc_header_rec.org_id
472 AND OOD.organization_id = HAOU.organization_id
473 AND OOD.org_information3 || '' = to_char(p_cc_header_rec.set_of_books_id)
474 AND HAOU.organization_id || '' = OOD.organization_id;
475
476 CURSOR c_val_cover_state_stat IS
477 SELECT cc_num
478 FROM igc_cc_headers
479 WHERE cc_header_id = p_cc_header_rec.parent_header_id
480 AND cc_state IN ('PR','CM')
481 AND cc_apprvl_status = 'AP';
482
483 BEGIN
484 --Added by svaithil for GSCC warnings
485 l_api_name := 'CC_Open_API_Validate';
486 -- -------------------------------------------------------------------
487 -- Initialize the return values
488 -- -------------------------------------------------------------------
489 x_valid_cc := FND_API.G_FALSE;
490 x_currency_code := NULL;
491 x_conversion_type := NULL;
492 x_conversion_date := NULL;
493 x_conversion_rate := NULL;
494 l_error_count := 0;
495 l_full_path := g_path||'cc_open_api_validate';
496
497 IF g_debug_mode = 'Y'
498 THEN
499 Output_Debug( l_full_path,'Starting Validation..... CC NUM : ' || p_cc_header_rec.cc_num);
500 Output_Debug( l_full_path,'Starting Validation..... CC REF NUM : ' || p_cc_header_rec.cc_ref_num);
501
502 END IF;
503
504 -- -------------------------------------------------------------------
505 -- Validate the Org Id.
506 -- -------------------------------------------------------------------
507 IF (p_cc_header_rec.org_id <> p_current_org_id) THEN
508 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_ORGID_NO_MATCH');
509 FND_MESSAGE.SET_TOKEN('ORGID', TO_CHAR(p_cc_header_rec.org_id), TRUE);
510 FND_MESSAGE.SET_TOKEN('CURR_ORGID', TO_CHAR(p_current_org_id), TRUE);
511 FND_MSG_PUB.ADD;
512 l_error_count := l_error_count + 1;
513
514 IF g_debug_mode = 'Y'
515 THEN
516 g_debug_msg := 'CC Open API Validate Org Id...';
517 Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
518 END IF;
519
520 IF (p_cc_header_rec.org_id IS NULL) THEN
521 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_NO_ORG_ID');
522 FND_MSG_PUB.ADD;
523 l_error_count := l_error_count + 1;
524
525 IF g_debug_mode = 'Y'
526 THEN
527 g_debug_msg := 'CC Open API Validate Org Id is NULL...';
528 Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
529 END IF;
530 ELSE
531
532 -- -------------------------------------------------------------------
533 -- Ensure that the Organization ID number actually exists in system
534 -- -------------------------------------------------------------------
535 OPEN c_validate_org_id;
536 FETCH c_validate_org_id
537 INTO l_org_name;
538
539 IF (c_validate_org_id%NOTFOUND) THEN
540
541 FND_MESSAGE.SET_NAME('IGC', 'IGC_ORG_NOT_FOUND');
542 FND_MESSAGE.SET_TOKEN('ORG_ID', TO_CHAR(p_cc_header_rec.org_id),TRUE);
543 FND_MSG_PUB.ADD;
544 l_error_count := l_error_count + 1;
545 IF g_debug_mode = 'Y'
546 THEN
547 g_debug_msg := 'CC Open API Validate Org Id is Not Found...';
548 IF(g_excep_level >= g_debug_level) THEN
549 FND_LOG.STRING(g_excep_level, l_full_path,g_debug_msg );
550 END IF;
551 --Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
552 END IF;
553
554 END IF;
555 CLOSE c_validate_org_id;
556
557 END IF;
558
559 -- -------------------------------------------------------------------
560 -- Validate Set of Books Id
561 -- -------------------------------------------------------------------
562 IF (p_cc_header_rec.set_of_books_id <> p_current_sob_id) THEN
563 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_SOB_NO_MATCH_USER_SOB');
564 FND_MESSAGE.SET_TOKEN('SOB_ID', TO_CHAR(p_cc_header_rec.set_of_books_id), TRUE);
565 FND_MESSAGE.SET_TOKEN('CURRENT_SOB_ID', TO_CHAR(p_current_sob_id), TRUE);
566 FND_MSG_PUB.ADD;
567 l_error_count := l_error_count + 1;
568 IF g_debug_mode = 'Y'
569 THEN
570 g_debug_msg := 'CC Open API Validate Set Of Books Id...';
571 Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
572 END IF;
573 END IF;
574
575 IF (p_cc_header_rec.set_of_books_id IS NULL) THEN
576 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_NO_SOB');
577 FND_MSG_PUB.ADD;
578 l_error_count := l_error_count + 1;
579
580 IF g_debug_mode = 'Y'
581 THEN
582 g_debug_msg := 'CC Open API Validate NULL Set of books ID...';
583 Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
584 END IF;
585 ELSE
586
587 -- -------------------------------------------------------------------
588 -- Ensure that the Set Of Books ID actually exists in system
589 -- -------------------------------------------------------------------
590 OPEN c_validate_sob_id;
591 FETCH c_validate_sob_id
592 INTO l_sob_id;
593
594 IF (c_validate_sob_id%NOTFOUND) THEN
595
596 FND_MESSAGE.SET_NAME('IGC', 'IGC_SOB_ID_NOT_FOUND');
597 FND_MESSAGE.SET_TOKEN('SOB_ID', TO_CHAR(p_cc_header_rec.set_of_books_id));
598 FND_MSG_PUB.ADD;
599 l_error_count := l_error_count + 1;
600 IF g_debug_mode = 'Y'
601 THEN
602 g_debug_msg := 'CC Open API Validate SOB Id is Not Found...';
603 IF(g_excep_level >= g_debug_level) THEN
604 FND_LOG.STRING(g_excep_level, l_full_path,g_debug_msg );
605 END IF;
606 --Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
607 END IF;
608 END IF;
609
610 END IF;
611 CLOSE c_validate_sob_id;
612
613 END IF;
614
615 -- -------------------------------------------------------------------
616 -- Validate Org ID and set of Books ID Combination.
617 -- -------------------------------------------------------------------
618 OPEN c_validate_sob_org_combo;
619 FETCH c_validate_sob_org_combo
620 INTO l_name;
621
622 IF (c_validate_sob_org_combo%NOTFOUND) THEN
623 FND_MESSAGE.SET_NAME('IGC', 'IGC_NO_SOB_ORG_COMBO');
624 FND_MESSAGE.SET_TOKEN('SOB_ID', TO_CHAR(p_cc_header_rec.set_of_books_id), TRUE);
625 FND_MESSAGE.SET_TOKEN('ORG_ID', TO_CHAR(p_cc_header_rec.org_id), TRUE);
626 FND_MSG_PUB.ADD;
627 l_error_count := l_error_count + 1;
628 IF g_debug_mode = 'Y'
629 THEN
630 g_debug_msg := 'CC Open API Validate Set of books ID and Org ID Combo Failed...';
631 IF(g_excep_level >= g_debug_level) THEN
632 FND_LOG.STRING(g_excep_level, l_full_path,g_debug_msg );
633 END IF;
634 --Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
635 END IF;
636 END IF;
637
638 CLOSE c_validate_sob_org_combo;
639
640 -- -------------------------------------------------------------------
641 -- Validate the CC type.
642 -- -------------------------------------------------------------------
643 IF UPPER(p_cc_header_rec.cc_type) NOT IN ('S', 'C', 'R') THEN
644 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_CCTYPE_INVALID');
645 FND_MESSAGE.SET_TOKEN('CC_TYPE', p_cc_header_rec.cc_type, TRUE);
646 FND_MSG_PUB.ADD;
647 l_error_count := l_error_count + 1;
648 IF g_debug_mode = 'Y'
649 THEN
650 g_debug_msg := 'CC Open API Validate CC Type...';
651 Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
652 END IF;
653 END IF;
654
655 -- -------------------------------------------------------------------
656 -- Get the numbering method begins here.
657 -- -------------------------------------------------------------------
658 BEGIN
659
660 SELECT CCNM.cc_num_method,
661 CCNM.cc_num_datatype
662 INTO l_cc_num_method,
663 l_cc_num_datatype
664 FROM igc_cc_system_options_all CCNM
665 WHERE CCNM.org_id = p_current_org_id;
666
667 EXCEPTION
668 WHEN OTHERS THEN
669 l_error_count := l_error_count + 1;
670 FND_MESSAGE.SET_NAME ('IGC', 'IGC_CC_NUM_METHOD_NOT_DEFINED');
671 FND_MSG_PUB.ADD;
672 IF g_debug_mode = 'Y'
673 THEN
674 g_debug_msg := 'CC Open API Validate Numbering method not found...';
675 IF(g_excep_level >= g_debug_level) THEN
676 FND_LOG.STRING(g_excep_level, l_full_path,g_debug_msg );
677 END IF;
678 --Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
679 END IF;
680 -- Bug 3199488
681 IF ( g_unexp_level >= g_debug_level ) THEN
682 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
683 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
684 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
685 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
686 END IF;
687 -- Bug 3199488
688 END;
689
690 -- -------------------------------------------------------------------
691 -- Check to ensure that the CC Reference Number given is NOT NULL.
692 -- -------------------------------------------------------------------
693 IF (p_cc_header_rec.cc_ref_num IS NULL) THEN
694 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_NO_REF_NUM');
695 FND_MSG_PUB.ADD;
696 l_error_count := l_error_count + 1;
697 IF g_debug_mode = 'Y'
698 THEN
699 g_debug_msg := 'CC Open API Validate CC Reference Number failure is NULL...';
700 IF(g_excep_level >= g_debug_level) THEN
701 FND_LOG.STRING(g_excep_level, l_full_path,g_debug_msg );
702 END IF;
703 --Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
704 END IF;
705 END IF;
706
707 -- -------------------------------------------------------------------
708 -- Check to ensure that if the method for automatic numbering being
709 -- on and the CC_NUM given is NOT NULL then raise error message.
710 -- -------------------------------------------------------------------
711 IF ((l_cc_num_method = 'A') AND
712 (p_cc_header_rec.cc_num IS NOT NULL)) THEN
713
714 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_AUTO_NUMBERING_ENABLED');
715 FND_MESSAGE.SET_TOKEN('CC_NUM', p_cc_header_rec.cc_num);
716 FND_MESSAGE.SET_TOKEN('ORG_ID', p_current_org_id);
717 FND_MSG_PUB.ADD;
718 l_error_count := l_error_count + 1;
719 IF g_debug_mode = 'Y'
720 THEN
721 g_debug_msg := 'CC Open API Validate CC Number failure with auto numbering on...';
722 IF(g_excep_level >= g_debug_level) THEN
723 FND_LOG.STRING(g_excep_level, l_full_path,g_debug_msg );
724 END IF;
725 --Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
726 END IF;
727
728 ELSIF ((l_cc_num_method = 'M') AND
729 (l_cc_num_datatype = 'N') AND
730 (p_cc_header_rec.cc_num IS NOT NULL)) THEN
731
732 IGC_CC_SYSTEM_OPTIONS_PKG.Validate_Numeric_CC_Num (p_api_version => 1.0,
733 p_init_msg_list => FND_API.G_FALSE,
734 p_commit => FND_API.G_FALSE,
735 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
736 x_return_status => l_return_status,
737 x_msg_count => l_msg_count,
738 x_msg_data => l_msg_data,
739 p_cc_num => p_cc_header_rec.cc_num
740 );
741
742 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
743
744 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
745 FND_MESSAGE.SET_NAME ('IGC', 'IGC_CC_NOT_NUMERIC_CC_NUM');
746 FND_MESSAGE.SET_TOKEN('CC_NUM', p_cc_header_rec.cc_num);
747 FND_MESSAGE.SET_TOKEN('ORG_ID', p_current_org_id);
748 FND_MSG_PUB.ADD;
749 END IF;
750
751 l_error_count := l_error_count + 1;
752 IF g_debug_mode = 'Y'
753 THEN
754 g_debug_msg := 'CC Open API Validate Numbering method not found...';
755 IF(g_excep_level >= g_debug_level) THEN
756 FND_LOG.STRING(g_excep_level, l_full_path,g_debug_msg );
757 END IF;
758 --Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
759 END IF;
760
761 END IF;
762
763 ELSIF ((l_cc_num_method = 'M') AND
764 (p_cc_header_rec.cc_num IS NULL)) THEN
765
766 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_NUM_NULL_FOR_MANUAL');
767 FND_MSG_PUB.ADD;
768 l_error_count := l_error_count + 1;
769 IF g_debug_mode = 'Y'
770 THEN
771 g_debug_msg := 'CC Open API Validate CC Number failure with Manual numbering on and CC num NULL...';
772 IF(g_excep_level >= g_debug_level) THEN
773 FND_LOG.STRING(g_excep_level, l_full_path,g_debug_msg );
774 END IF;
775 --Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
776 END IF;
777
778 END IF;
779
780 -- -------------------------------------------------------------------
781 -- If auto numbering is enabled and the CC number given is NULL
782 -- then build the CC Number for the record that is to be created.
783 -- -------------------------------------------------------------------
784 IF ((l_cc_num_method = 'A') AND
785 (p_cc_header_rec.cc_num IS NULL)) THEN
786
787 IGC_CC_SYSTEM_OPTIONS_PKG.Create_Auto_CC_Num (p_api_version => 1.0,
788 p_init_msg_list => FND_API.G_FALSE,
789 p_commit => FND_API.G_FALSE,
790 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
791 x_return_status => l_return_status,
792 x_msg_count => l_msg_count,
793 x_msg_data => l_msg_data,
794 p_org_id => p_current_org_id,
795 p_sob_id => p_cc_header_rec.set_of_books_id,
796 x_cc_num => l_cc_num_created
797 );
798 IF (l_cc_num_created >= 0) THEN
799
800 p_cc_header_rec.cc_num := to_char (l_cc_num_created);
801
802 ELSE
803
804 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_API_AUTO_CC_NUM_FAIL');
805 FND_MESSAGE.SET_TOKEN('ORG_ID', p_current_org_id);
806 FND_MSG_PUB.ADD;
807 l_error_count := l_error_count + 1;
808
809 IF g_debug_mode = 'Y'
810 THEN
811 g_debug_msg := 'CC Open API Validate CC Number failure with auto numbering on...';
812 IF(g_excep_level >= g_debug_level) THEN
813 FND_LOG.STRING(g_excep_level, l_full_path,g_debug_msg );
814 END IF;
815 --Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
816 END IF;
817
818 END IF;
819
820 END IF;
821
822 -- -------------------------------------------------------------------
823 -- Check whether the CC Number already exists in the database.
824 -- -------------------------------------------------------------------
825 BEGIN
826 l_count := 0;
827 SELECT COUNT(*) INTO l_count
828 FROM igc_cc_headers
829 WHERE /*org_id = p_cc_header_rec.org_id
830 AND --Commented during MOAC uptake */
831 cc_num = p_cc_header_rec.cc_num;
832
833 EXCEPTION
834 WHEN OTHERS THEN
835 l_error_count := l_error_count + 1;
836 g_debug_msg := 'CC Open API Validate CC Number Unable to Validate...';
837 --Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
838 IF(g_excep_level >= g_debug_level) THEN
839 FND_LOG.STRING(g_excep_level, l_full_path,g_debug_msg );
840 END IF;
841 -- Bug 3199488
842 IF ( g_unexp_level >= g_debug_level ) THEN
843 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
844 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
845 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
846 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
847 END IF;
848 -- Bug 3199488
849
850
851 END;
852
853 IF (l_count > 0) THEN
854 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_DUP_CC_NUMBER');
855 FND_MESSAGE.SET_TOKEN('CC_NUMBER', p_cc_header_rec.cc_num);
856 FND_MSG_PUB.ADD;
857 l_error_count := l_error_count + 1;
858 IF g_debug_mode = 'Y'
859 THEN
860 g_debug_msg := 'CC Open API Validate CC Number...';
861 Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
862 END IF;
863 END IF;
864
865 -- -------------------------------------------------------------------
866 -- Check if the CC Reference Number Already exists in the database.
867 -- -------------------------------------------------------------------
868 BEGIN
869 l_count := 0;
870 SELECT COUNT(*) INTO l_count
871 FROM igc_cc_headers
872 WHERE /*org_id = p_cc_header_rec.org_id
873 AND --Commented during MOAC uptake */
874 cc_ref_num = p_cc_header_rec.cc_ref_num;
875
876 EXCEPTION
877
878 WHEN OTHERS THEN
879 l_error_count := l_error_count + 1;
880 g_debug_msg := 'CC Open API Validate CC Reference Number Unable to Validate...';
881 IF g_debug_mode = 'Y' THEN
882 IF(g_excep_level >= g_debug_level) THEN
883 FND_LOG.STRING(g_excep_level, l_full_path,g_debug_msg );
884 END IF;
885 END IF;
886 --Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
887 -- Bug 3199488
888 IF ( g_unexp_level >= g_debug_level ) THEN
889 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
890 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
891 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
892 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
893 END IF;
894 -- Bug 3199488
895 END;
896
897 IF l_count > 0 THEN
898 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_DUP_CC_REF_NUM');
899 FND_MESSAGE.SET_TOKEN('CC_REF_NUM', p_cc_header_rec.cc_ref_num);
900 FND_MSG_PUB.ADD;
901 l_error_count := l_error_count + 1;
902 IF g_debug_mode = 'Y'
903 THEN
904 g_debug_msg := 'CC Open API Validate CC Reference Number...';
905 Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
906 END IF;
907 END IF;
908
909
910 -- -------------------------------------------------------------------
911 -- Parent_header_id should not be null and should be a valid value
912 -- for CC type 'R'
913 -- -------------------------------------------------------------------
914 IF p_cc_header_rec.cc_type = 'R' THEN
915
916 IF p_cc_header_rec.parent_header_Id IS NULL THEN
917
918 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_PARENT_HDR_ID_REQD');
919 FND_MSG_PUB.ADD;
920 l_error_count := l_error_count + 1;
921 IF g_debug_mode = 'Y'
922 THEN
923 g_debug_msg := 'CC Open API Validate Parent Header Id Null...';
924 IF(g_excep_level >= g_debug_level) THEN
925 FND_LOG.STRING(g_excep_level, l_full_path,g_debug_msg );
926 END IF;
927 --Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
928 END IF;
929
930 ELSE
931
932 OPEN c_val_cover_state_stat;
933 FETCH c_val_cover_state_stat
934 INTO l_cc_num;
935
936 IF (c_val_cover_state_stat%NOTFOUND) THEN
937 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_PARENT_NOT_VAL_STATE');
938 FND_MSG_PUB.ADD;
939 l_error_count := l_error_count + 1;
940 IF g_debug_mode = 'Y'
941 THEN
942 g_debug_msg := 'CC Open API Validate Parent Header State and Status invalid...';
943 IF(g_excep_level >= g_debug_level) THEN
944 FND_LOG.STRING(g_excep_level, l_full_path,g_debug_msg );
945 END IF;
946 --Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
947 END IF;
948 END IF;
949
950 BEGIN
951
952 SELECT cchd.currency_code,
953 cchd.conversion_type,
954 cchd.conversion_rate,
955 cchd.conversion_date
956 INTO l_cov_curr_code,
957 l_cov_conversion_type,
958 l_cov_conversion_rate,
959 l_cov_conversion_date
960 FROM igc_cc_headers cchd
961 WHERE cchd.cc_header_id = p_cc_header_rec.parent_header_id
962 AND cchd.cc_type = 'C';
963
964 EXCEPTION
965
966 WHEN NO_DATA_FOUND THEN
967 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_PARENT_NOT_VALID_OAPI');
968 FND_MESSAGE.SET_TOKEN('PARENT_HEADER_ID', TO_CHAR(p_cc_header_rec.parent_header_id), TRUE);
969 FND_MSG_PUB.ADD;
970 l_error_count := l_error_count + 1;
971 IF g_debug_mode = 'Y'
972 THEN
973 g_debug_msg := 'CC Open API Validate Invalid Cover Details...';
974 IF(g_excep_level >= g_debug_level) THEN
975 FND_LOG.STRING(g_excep_level, l_full_path,g_debug_msg );
976 END IF;
977 --Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
978 END IF;
979 END;
980
981 END IF;
982
983 ELSE
984
985 IF p_cc_header_rec.parent_header_id IS NOT NULL THEN
986 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_PARENT_ID_NULL_OAPI');
987 FND_MSG_PUB.ADD;
988 l_error_count := l_error_count + 1;
989 IF g_debug_mode = 'Y'
990 THEN
991 g_debug_msg := 'CC Open API Validate Parent Header Id Must be Null...';
992 IF(g_excep_level >= g_debug_level) THEN
993 FND_LOG.STRING(g_excep_level, l_full_path,g_debug_msg );
994 END IF;
995 --Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
996 END IF;
997 END IF;
998
999 END IF;
1000
1001 -- -------------------------------------------------------------------
1002 -- Check Budgetary Control is on
1003 -- -------------------------------------------------------------------
1004 IGC_CC_BUDGETARY_CTRL_PKG.CHECK_BUDGETARY_CTRL_ON (p_api_version => 1.0,
1005 p_init_msg_list => FND_API.G_FALSE,
1006 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1007 X_return_status => l_return_status,
1008 X_msg_count => l_msg_count,
1009 X_msg_data => l_msg_data,
1010 p_org_id => p_cc_header_rec.org_id,
1011 p_sob_id => p_cc_header_rec.set_of_books_id,
1012 p_cc_state => 'PR',
1013 X_encumbrance_on => l_encumbrance_flag
1014 );
1015
1016 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1017 l_error_count := l_error_count + NVL(l_msg_count,0);
1018 IF g_debug_mode = 'Y'
1019 THEN
1020 g_debug_msg := 'CC Open API Validate Check Budgetary Control Not Successful...';
1021 IF(g_excep_level >= g_debug_level) THEN
1022 FND_LOG.STRING(g_excep_level, l_full_path,g_debug_msg );
1023 END IF;
1024 --Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
1025 END IF;
1026 END IF;
1027
1028 -- -------------------------------------------------------------------
1029 -- Validate the Start_Date.
1030 -- -------------------------------------------------------------------
1031 IGC_CC_BUDGETARY_CTRL_PKG.Validate_CC (p_api_version => 1.0,
1032 p_init_msg_list => FND_API.G_FALSE,
1033 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1034 x_return_status => l_return_status,
1035 x_msg_count => l_msg_count,
1036 x_msg_data => l_msg_data,
1037 p_cc_header_id => NULL,
1038 X_valid_cc => l_valid_cc,
1039 p_mode => 'E',
1040 p_field_from => 'START_DATE',
1041 p_encumbrance_flag => l_encumbrance_flag,
1042 p_sob_id => p_cc_header_rec.set_of_books_id,
1043 p_org_id => p_cc_header_rec.org_id,
1044 p_start_date => TRUNC(p_cc_header_rec.cc_start_date),
1045 p_end_date => TRUNC(p_cc_header_rec.cc_end_date),
1046 p_cc_type_code => p_cc_header_rec.cc_type,
1047 p_parent_cc_header_id => p_cc_header_rec.parent_header_id,
1048 p_cc_det_pf_date => NULL,
1049 p_acct_date => NULL,
1050 p_prev_acct_date => NULL,
1051 p_cc_state => 'PR'
1052 );
1053
1054 IF ((l_return_status <> FND_API.G_RET_STS_SUCCESS) OR
1055 (l_valid_cc <> FND_API.G_TRUE)) THEN
1056 l_error_count := l_error_count + NVL(l_msg_count,0);
1057 IF g_debug_mode = 'Y'
1058 THEN
1059 g_debug_msg := 'CC Open API Validate CC for start date Not Successful...';
1060 IF(g_excep_level >= g_debug_level) THEN
1061 FND_LOG.STRING(g_excep_level, l_full_path,g_debug_msg );
1062 END IF;
1063 --Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
1064 END IF;
1065 END IF;
1066
1067 -- -------------------------------------------------------------------
1068 -- Validate the End Date.
1069 -- -------------------------------------------------------------------
1070 IGC_CC_BUDGETARY_CTRL_PKG.Validate_CC (p_api_version => 1.0,
1071 p_init_msg_list => FND_API.G_FALSE,
1072 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1073 x_return_status => l_return_status,
1074 x_msg_count => l_msg_count,
1075 x_msg_data => l_msg_data,
1076 p_cc_header_id => NULL,
1077 X_valid_cc => l_valid_cc,
1078 p_mode => 'E',
1079 p_field_from => 'END_DATE',
1080 p_encumbrance_flag => l_encumbrance_flag,
1081 p_sob_id => p_cc_header_rec.set_of_books_id,
1082 p_org_id => p_cc_header_rec.org_id,
1083 p_start_date => TRUNC(p_cc_header_rec.cc_start_date),
1084 p_end_date => TRUNC(p_cc_header_rec.cc_end_date),
1085 p_cc_type_code => p_cc_header_rec.cc_type,
1086 p_parent_cc_header_id => p_cc_header_rec.parent_header_id,
1087 p_cc_det_pf_date => NULL,
1088 p_acct_date => NULL,
1089 p_prev_acct_date => NULL,
1090 p_cc_state => 'PR'
1091 );
1092
1093 IF ((l_return_status <> FND_API.G_RET_STS_SUCCESS) OR
1094 (l_valid_cc <> FND_API.G_TRUE)) THEN
1095 l_error_count := l_error_count + NVL(l_msg_count,0);
1096 IF g_debug_mode = 'Y'
1097 THEN
1098 g_debug_msg := 'CC Open API Validate CC for end date Not Successful...';
1099 IF(g_excep_level >= g_debug_level) THEN
1100 FND_LOG.STRING(g_excep_level, l_full_path,g_debug_msg );
1101 END IF;
1102 --Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
1103 END IF;
1104 END IF;
1105
1106 -- -------------------------------------------------------------------
1107 -- Validate Vendor Id
1108 -- -------------------------------------------------------------------
1109 IF p_cc_header_rec.vendor_id IS NOT NULL THEN
1110
1111 BEGIN
1112 SELECT vendor_id,
1113 invoice_currency_code
1114 INTO l_vendor_id,
1115 l_vendor_curr_code
1116 FROM po_vendors
1117 WHERE vendor_id = p_cc_header_rec.vendor_id
1118 AND enabled_flag = 'Y'
1119 AND sysdate BETWEEN NVL(start_date_active, sysdate-1)
1120 AND NVL(end_date_active, sysdate+1);
1121
1122 EXCEPTION
1123
1124 WHEN NO_DATA_FOUND THEN
1125 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_INVALID_VENDOR_ID');
1126 FND_MESSAGE.SET_TOKEN('VENDOR_ID', TO_CHAR(p_cc_header_rec.vendor_id), TRUE);
1127 FND_MSG_PUB.ADD;
1128 l_error_count := l_error_count + 1;
1129 IF g_debug_mode = 'Y'
1130 THEN
1131 g_debug_msg := 'CC Open API Validate Vendor Info Not Successful...';
1132 IF(g_excep_level >= g_debug_level) THEN
1133 FND_LOG.STRING(g_excep_level, l_full_path,g_debug_msg );
1134 END IF;
1135 --Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
1136 END IF;
1137 END;
1138
1139 END IF;
1140
1141 -- -------------------------------------------------------------------
1142 -- Validate Vendor Site Id
1143 -- -------------------------------------------------------------------
1144 IF p_cc_header_rec.vendor_site_id IS NOT NULL THEN
1145
1146 BEGIN
1147 SELECT vendor_site_id,
1148 invoice_currency_code,
1149 terms_id,
1150 bill_to_location_id
1151 INTO l_vendor_site_id,
1152 l_vendor_site_curr_code,
1153 l_populate_terms_id,
1154 l_billed_to_location_id
1155 FROM po_vendor_sites_all
1156 WHERE org_id = p_cc_header_rec.org_id /* Addded this condition for MOAC uptake */
1157 AND vendor_site_id = p_cc_header_rec.vendor_site_id
1158 AND vendor_id = p_cc_header_rec.vendor_id
1159 AND purchasing_site_flag = 'Y'
1160 AND NVL(inactive_date, sysdate+1) > sysdate;
1161
1162 EXCEPTION
1163
1164 WHEN NO_DATA_FOUND THEN
1165 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_INVALID_VENDOR_SITE_ID');
1166 FND_MESSAGE.SET_TOKEN('VENDOR_SITE_ID', TO_CHAR(p_cc_header_rec.vendor_site_id), TRUE);
1167 FND_MSG_PUB.ADD;
1168 l_error_count := l_error_count + 1;
1169 IF g_debug_mode = 'Y'
1170 THEN
1171 g_debug_msg := 'CC Open API Validate Vendor Site Info Not Successful...';
1172 IF(g_excep_level >= g_debug_level) THEN
1173 FND_LOG.STRING(g_excep_level, l_full_path,g_debug_msg );
1174 END IF;
1175 --Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
1176 END IF;
1177 END;
1178
1179 END IF;
1180
1181 IF g_debug_mode = 'Y'
1182 THEN
1183 Output_Debug( l_full_path,'Contact ID : ' || p_cc_header_rec.vendor_contact_id);
1184 Output_Debug( l_full_path,'Site ID : ' || p_cc_header_rec.vendor_site_id);
1185 Output_Debug( l_full_path,'Vendor ID : ' || p_cc_header_rec.vendor_id);
1186 END IF;
1187
1188 -- -------------------------------------------------------------------
1189 -- Validate Vendor Contact Id
1190 -- -------------------------------------------------------------------
1191 IF (p_cc_header_rec.vendor_contact_id IS NOT NULL) THEN
1192
1193 IF ((p_cc_header_rec.vendor_site_id IS NULL) OR
1194 (p_cc_header_rec.vendor_id IS NULL)) THEN
1195
1196 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_VNDR_SITE_CONT_ID_NULL');
1197 FND_MSG_PUB.ADD;
1198 l_error_count := l_error_count + 1;
1199 IF g_debug_mode = 'Y'
1200 THEN
1201 g_debug_msg := 'CC Open API Validate Vendor Contact and Vendor Validation Not Successful...';
1202 IF(g_excep_level >= g_debug_level) THEN
1203 FND_LOG.STRING(g_excep_level, l_full_path,g_debug_msg );
1204 END IF;
1205 --Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
1206 END IF;
1207
1208 ELSE
1209
1210 BEGIN
1211 SELECT vendor_contact_id
1212 INTO l_vendor_contact_id
1213 FROM po_vendor_contacts
1214 WHERE vendor_site_id = p_cc_header_rec.vendor_site_id
1215 AND vendor_contact_id = p_cc_header_rec.vendor_contact_id
1216 AND NVL(inactive_date, sysdate+1) > sysdate;
1217
1218 EXCEPTION
1219
1220 WHEN NO_DATA_FOUND THEN
1221 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_INVALID_VEDR_CONTACT_ID');
1222 FND_MESSAGE.SET_TOKEN('VENDOR_CONTACT_ID', TO_CHAR(p_cc_header_rec.vendor_contact_id), TRUE);
1223 FND_MSG_PUB.ADD;
1224 l_error_count := l_error_count + 1;
1225 IF g_debug_mode = 'Y'
1226 THEN
1227 g_debug_msg := 'CC Open API Validate Vendor Contact Not Successful...';
1228 IF(g_excep_level >= g_debug_level) THEN
1229 FND_LOG.STRING(g_excep_level, l_full_path,g_debug_msg );
1230 END IF;
1231 --Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
1232 END IF;
1233 END;
1234
1235 END IF;
1236
1237 END IF;
1238
1239 -- -------------------------------------------------------------------
1240 -- Validate Term Id
1241 -- -------------------------------------------------------------------
1242 IF p_cc_header_rec.term_id IS NOT NULL THEN
1243
1244 BEGIN
1245 SELECT term_id
1246 INTO l_term_id
1247 FROM ap_terms_val_v
1248 WHERE term_id = p_cc_header_rec.term_id;
1249
1250 EXCEPTION
1251
1252 WHEN NO_DATA_FOUND THEN
1253 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_INVALID_TERM_ID');
1254 FND_MESSAGE.SET_TOKEN('TERM_ID', TO_CHAR(p_cc_header_rec.term_id), TRUE);
1255 FND_MSG_PUB.ADD;
1256 l_error_count := l_error_count + 1;
1257 IF g_debug_mode = 'Y'
1258 THEN
1259 g_debug_msg := 'CC Open API Validate Term Not Successful...';
1260 IF(g_excep_level >= g_debug_level) THEN
1261 FND_LOG.STRING(g_excep_level, l_full_path,g_debug_msg );
1262 END IF;
1263 --Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
1264 END IF;
1265 END;
1266
1267 ELSE
1268
1269 -- -----------------------------------------------------------------------------------
1270 -- If there is a site ID given and the term ID is NULL then assign the site ID
1271 -- to the term ID.
1272 -- -----------------------------------------------------------------------------------
1273 IF p_cc_header_rec.vendor_site_id IS NOT NULL THEN
1274 p_cc_header_rec.term_id := l_populate_terms_id;
1275 END IF;
1276
1277 END IF;
1278
1279 -- -------------------------------------------------------------------
1280 -- Validate Location Id
1281 -- -------------------------------------------------------------------
1282 IF p_cc_header_rec.location_id IS NOT NULL THEN
1283
1284 IF p_cc_header_rec.vendor_id IS NULL THEN
1285 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_LOCATION_ID_NULL');
1286 FND_MSG_PUB.ADD;
1287 l_error_count := l_error_count + 1;
1288 IF g_debug_mode = 'Y'
1289 THEN
1290 g_debug_msg := 'CC Open API Validate Vendor and Location Valdiation Not Successful...';
1291 IF(g_excep_level >= g_debug_level) THEN
1292 FND_LOG.STRING(g_excep_level, l_full_path,g_debug_msg );
1293 END IF;
1294 --Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
1295 END IF;
1296 ELSE
1297
1298 BEGIN
1299 SELECT location_id
1300 INTO l_location_id
1301 FROM hr_locations
1302 WHERE location_id = p_cc_header_rec.location_id
1303 AND bill_to_site_flag = 'Y'
1304 AND NVL(inactive_date, sysdate+1) > sysdate;
1305
1306 EXCEPTION
1307 WHEN NO_DATA_FOUND THEN
1308 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_INVALID_LOCATION_ID');
1309 FND_MESSAGE.SET_TOKEN('LOCATION_ID', TO_CHAR(p_cc_header_rec.location_id), TRUE);
1310 FND_MSG_PUB.ADD;
1311 l_error_count := l_error_count + 1;
1312 IF g_debug_mode = 'Y'
1313 THEN
1314 g_debug_msg := 'CC Open API Validate Location Not Successful...';
1315 IF(g_excep_level >= g_debug_level) THEN
1316 FND_LOG.STRING(g_excep_level, l_full_path,g_debug_msg );
1317 END IF;
1318 --Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
1319 END IF;
1320 END;
1321
1322 END IF;
1323
1324 ELSE
1325
1326 -- -----------------------------------------------------------------------------------
1327 -- If there is a site ID given and the location ID is NULL then assign the site ID
1328 -- to the location ID.
1329 -- -----------------------------------------------------------------------------------
1330 IF p_cc_header_rec.vendor_site_id IS NOT NULL THEN
1331 p_cc_header_rec.location_id := l_billed_to_location_id;
1332 END IF;
1333
1334 END IF;
1335
1336 -- -------------------------------------------------------------------
1337 -- Validate Cc Owner User Id
1338 -- -------------------------------------------------------------------
1339 BEGIN
1340 -- Performance Tuning, Replaced the following query with
1341 -- the one below.
1342 -- SELECT fu.user_id
1343 -- INTO l_user_id
1344 -- FROM fnd_user fu, hr_employees he
1345 -- WHERE fu.user_id = p_cc_header_rec.cc_owner_user_id
1346 -- AND sysdate BETWEEN NVL(fu.start_date, sysdate)
1347 -- AND NVL(fu.end_date, sysdate)
1348 -- AND fu.employee_id IS NOT NULL
1349 -- AND fu.employee_id = he.employee_id;
1350 SELECT fu.user_id
1351 INTO l_user_id
1352 FROM fnd_user fu,
1353 per_people_f p, /* per_all_people_f p, --Commented during MOAC uptake for bug#6341012*/
1354 per_all_assignments_f a,
1355 per_assignment_status_types past
1356 WHERE fu.user_id = p_cc_header_rec.cc_owner_user_id
1357 AND sysdate BETWEEN NVL(fu.start_date, sysdate)
1358 AND NVL(fu.end_date, sysdate)
1359 AND fu.employee_id IS NOT NULL
1360 AND fu.employee_id = p.person_id
1361 /* AND p.business_group_id = (select nvl(max(fsp.business_group_id),0) from financials_system_parameters fsp) --Commented during MOAC uptake for bug #6341012 */
1362 AND p.employee_number is not null
1363 AND trunc(sysdate) between p.effective_start_date and p.effective_end_date
1364 AND a.person_id = p.person_id
1365 AND a.primary_flag = 'Y'
1366 AND trunc(sysdate) between a.effective_start_date
1367 AND a.effective_end_date
1368 AND a.assignment_status_type_id = past.assignment_status_type_id
1369 AND past.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN')
1370 AND a.assignment_type = 'E';
1371
1372 EXCEPTION
1373 WHEN NO_DATA_FOUND THEN
1374 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_OWNER_UID_INVALID');
1375 FND_MESSAGE.SET_TOKEN('OWNER_UID', TO_CHAR(p_cc_header_rec.cc_owner_user_id), TRUE);
1376 FND_MSG_PUB.ADD;
1377 l_error_count := l_error_count + 1;
1378 IF g_debug_mode = 'Y'
1379 THEN
1380 g_debug_msg := 'CC Open API Validate Owner Not Successful...';
1381 IF(g_excep_level >= g_debug_level) THEN
1382 FND_LOG.STRING(g_excep_level, l_full_path,g_debug_msg );
1383 END IF;
1384 --Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
1385 END IF;
1386 END;
1387
1388 -- -------------------------------------------------------------------
1389 -- Validate Cc Preparer User Id
1390 -- -------------------------------------------------------------------
1391 BEGIN
1392 -- Performance tuning, Replaced the following sql
1393 -- with the one below.
1394 -- SELECT fu.user_id
1395 -- INTO l_user_id
1396 -- FROM fnd_user fu, hr_employees he
1397 -- WHERE fu.user_id = p_cc_header_rec.cc_preparer_user_id
1398 -- AND sysdate BETWEEN NVL(fu.start_date, sysdate)
1399 -- AND NVL(fu.end_date, sysdate)
1400 -- AND fu.employee_id IS NOT NULL
1401 -- AND fu.employee_id = he.employee_id;
1402
1403 SELECT fu.user_id
1404 INTO l_user_id
1405 FROM fnd_user fu,
1406 per_people_f p, /* per_all_people_f p, --Commented for Bug#6341012
1407 during MOAC uptake*/
1408 per_all_assignments_f a,
1409 per_assignment_status_types past
1410 WHERE fu.user_id = p_cc_header_rec.cc_preparer_user_id
1411 AND sysdate BETWEEN NVL(fu.start_date, sysdate)
1412 AND NVL(fu.end_date, sysdate)
1413 AND fu.employee_id IS NOT NULL
1414 AND fu.employee_id = p.person_id
1415 /*AND p.business_group_id = (SELECT NVL(MAX(fsp.business_group_id),0)
1416 FROM financials_system_parameters fsp)
1417 --Commented during MOAC uptake for bug#6341012 */
1418 AND p.employee_number is not null
1419 AND trunc(sysdate) between p.effective_start_date and p.effective_end_date
1420 AND a.person_id = p.person_id
1421 AND a.primary_flag = 'Y'
1422 AND trunc(sysdate) between a.effective_start_date
1423 AND a.effective_end_date
1424 AND a.assignment_status_type_id = past.assignment_status_type_id
1425 AND past.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN')
1426 AND a.assignment_type = 'E';
1427
1428 EXCEPTION
1429
1430 WHEN NO_DATA_FOUND THEN
1431 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_PREPARER_UID_INVALID');
1432 FND_MESSAGE.SET_TOKEN('PREPARER_UID', TO_CHAR(p_cc_header_rec.cc_preparer_user_id), TRUE);
1433 FND_MSG_PUB.ADD;
1434 l_error_count := l_error_count + 1;
1435 IF g_debug_mode = 'Y'
1436 THEN
1437 g_debug_msg := 'CC Open API Validate Preparer Not Successful...';
1438 IF(g_excep_level >= g_debug_level) THEN
1439 FND_LOG.STRING(g_excep_level, l_full_path,g_debug_msg );
1440 END IF;
1441 --Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
1442 END IF;
1443 END;
1444
1445 -- -------------------------------------------------------------------
1446 -- Validate Currency Code and the conversion columns
1447 -- -------------------------------------------------------------------
1448 x_currency_code := p_cc_header_rec.currency_code;
1449 x_conversion_type := p_cc_header_rec.conversion_type;
1450 x_conversion_date := p_cc_header_rec.conversion_date;
1451 x_conversion_rate := p_cc_header_rec.conversion_rate;
1452
1453 IF (x_currency_code IS NULL) THEN
1454
1455 IF l_vendor_site_curr_code IS NOT NULL THEN
1456 x_currency_code := l_vendor_site_curr_code;
1457 IF g_debug_mode = 'Y'
1458 THEN
1459 g_debug_msg := 'CC Open API Validate Vendor Site Invoice Currency...'||x_currency_code;
1460 Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
1461 END IF;
1462 ELSIF l_vendor_curr_code IS NOT NULL THEN
1463 x_currency_code := l_vendor_curr_code;
1464 IF g_debug_mode = 'Y'
1465 THEN
1466 g_debug_msg := 'CC Open API Validate Vendor Invoice Currency...'||x_currency_code;
1467 Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
1468 END IF;
1469 END IF;
1470
1471 END IF;
1472
1473 IF x_currency_code IS NULL THEN
1474
1475 IF p_func_currency_code IS NULL THEN
1476 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_CURR_CODE_REQD');
1477 FND_MSG_PUB.ADD;
1478 l_error_count := l_error_count + 1;
1479 IF g_debug_mode = 'Y'
1480 THEN
1481 g_debug_msg := 'CC Open API Validate Currency cannot be null...';
1482 --Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
1483 IF(g_excep_level >= g_debug_level) THEN
1484 FND_LOG.STRING(g_excep_level, l_full_path,g_debug_msg );
1485 END IF;
1486 END IF;
1487 ELSE
1488 x_currency_code := p_func_currency_code;
1489 END IF;
1490
1491 END IF;
1492
1493 l_currency_code := NULL;
1494
1495 -- -------------------------------------------------------------------
1496 -- Check if the non-functional currency is a valid currency.
1497 -- -------------------------------------------------------------------
1498 BEGIN
1499 SELECT currency_code
1500 INTO l_currency_code
1501 FROM fnd_currencies_vl
1502 WHERE enabled_flag = 'Y'
1503 AND currency_flag = 'Y'
1504 AND SYSDATE BETWEEN NVL(start_date_active, SYSDATE)
1505 AND NVL(end_date_active, SYSDATE)
1506 AND currency_code = x_currency_code;
1507
1508 EXCEPTION
1509
1510 WHEN NO_DATA_FOUND THEN
1511 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_CURR_CODE_INVALID');
1512 FND_MESSAGE.SET_TOKEN('CURR_CODE', x_currency_code, TRUE);
1513 FND_MSG_PUB.ADD;
1514 l_error_count := l_error_count + 1;
1515 IF g_debug_mode = 'Y'
1516 THEN
1517 g_debug_msg := 'CC Open API Validate Currency Invalid...';
1518 IF(g_excep_level >= g_debug_level) THEN
1519 FND_LOG.STRING(g_excep_level, l_full_path,g_debug_msg );
1520 END IF;
1521 --Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
1522 END IF;
1523 END;
1524
1525 IF (l_currency_code IS NOT NULL) THEN
1526
1527 IF ((x_currency_code <> p_func_currency_code) AND
1528 ((x_conversion_type IS NULL) OR
1529 (x_conversion_rate IS NULL) OR
1530 (x_conversion_date IS NULL))) THEN
1531
1532 BEGIN
1533
1534 l_relation_flag := gl_currency_api.is_fixed_rate (x_currency_code,
1535 p_func_currency_code,
1536 NVL(x_conversion_date, SYSDATE)
1537 );
1538 IF l_relation_flag = 'Y' THEN
1539 x_conversion_type := 'EMU FIXED';
1540 ELSE
1541
1542 IF (x_conversion_type IS NULL) THEN
1543
1544 BEGIN
1545 SELECT ccsp.default_rate_type
1546 INTO x_conversion_type
1547 FROM igc_cc_system_options_all ccsp
1548 WHERE ccsp.org_id = p_cc_header_rec.org_id;
1549
1550 EXCEPTION
1551
1552 WHEN OTHERS THEN
1553 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_CONV_TYPE_RATE_DT_REQD');
1554 FND_MSG_PUB.ADD;
1555 l_error_count := l_error_count + 1;
1556 IF g_debug_mode = 'Y'
1557 THEN
1558 g_debug_msg := 'CC Open API Validate No default rate type defined...';
1559 IF(g_excep_level >= g_debug_level) THEN
1560 FND_LOG.STRING(g_excep_level, l_full_path,g_debug_msg );
1561 END IF;
1562 --Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
1563 END IF;
1564 -- Bug 3199488
1565 IF ( g_unexp_level >= g_debug_level ) THEN
1566 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
1567 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
1568 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
1569 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
1570 END IF;
1571 -- Bug 3199488
1572
1573 END;
1574
1575 END IF;
1576
1577 END IF;
1578
1579 EXCEPTION
1580
1581 WHEN OTHERS THEN
1582 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_CURR_CODE_INVALID');
1583 FND_MESSAGE.SET_TOKEN('CURR_CODE', x_currency_code, TRUE);
1584 FND_MSG_PUB.ADD;
1585 l_error_count := l_error_count + 1;
1586 IF g_debug_mode = 'Y'
1587 THEN
1588 g_debug_msg := 'CC Open API Validate Invalid Currency Code defined...';
1589 IF(g_excep_level >= g_debug_level) THEN
1590 FND_LOG.STRING(g_excep_level, l_full_path,g_debug_msg );
1591 END IF;
1592 --Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
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;
1604
1605 END IF;
1606
1607 IF l_cov_curr_code <> p_func_currency_code AND p_cc_header_rec.cc_type = 'R' AND
1608 ( x_currency_code <> l_cov_curr_code OR
1609 x_conversion_type <> l_cov_conversion_type OR
1610 x_conversion_rate <> l_cov_conversion_rate OR
1611 x_conversion_date <> l_cov_conversion_date) THEN
1612
1613 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_CURR_CD_CT_CR_CD_SAME');
1614 FND_MSG_PUB.ADD;
1615 l_error_count := l_error_count + 1;
1616 IF g_debug_mode = 'Y'
1617 THEN
1618 g_debug_msg := 'CC Open API Validate Cover and Release currency does not match...';
1619 IF(g_excep_level >= g_debug_level) THEN
1620 FND_LOG.STRING(g_excep_level, l_full_path,g_debug_msg );
1621 END IF;
1622 --Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
1623 END IF;
1624 END IF;
1625
1626 -- -------------------------------------------------------------------
1627 -- Conversion Type Validation.
1628 -- -------------------------------------------------------------------
1629 IF x_currency_code <> p_func_currency_code AND
1630 x_conversion_type IS NOT NULL THEN
1631
1632 IF x_conversion_type <> 'EMU FIXED' AND
1633 x_conversion_type <> 'Period Average (Upgrade)' THEN
1634
1635 l_conversion_type := NULL;
1636
1637 BEGIN
1638 IF g_debug_mode = 'Y'
1639 THEN
1640 g_debug_msg := 'CC Open API Validate Other Conversion Type...';
1641 Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
1642 END IF;
1643
1644 SELECT conversion_type
1645 INTO l_conversion_type
1646 FROM gl_daily_conversion_types
1647 WHERE conversion_type <> 'Period Average (Upgrade)'
1648 AND conversion_type <> 'EMU FIXED'
1649 AND conversion_type = x_conversion_type;
1650
1651 EXCEPTION
1652
1653 WHEN NO_DATA_FOUND THEN
1654 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_CONV_TYPE_INVALID');
1655 FND_MESSAGE.SET_TOKEN('CONVTYPE', x_conversion_type, TRUE);
1656 FND_MSG_PUB.ADD;
1657 l_error_count := l_error_count + 1;
1658 IF g_debug_mode = 'Y'
1659 THEN
1660 g_debug_msg := 'CC Open API Validate Invalid Conversion Type...';
1661 Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
1662 END IF;
1663 END;
1664
1665 END IF;
1666
1667 IF UPPER(x_conversion_type) = 'USER' AND
1668 l_conversion_type IS NOT NULL AND
1669 (x_conversion_date IS NULL OR
1670 x_conversion_rate IS NULL) THEN
1671
1672 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_CONV_DATE_RATE_REQD');
1673 FND_MSG_PUB.ADD;
1674 l_error_count := l_error_count + 1;
1675 IF g_debug_mode = 'Y'
1676 THEN
1677 g_debug_msg := 'CC Open API Validate Rate and Date required...';
1678 IF(g_excep_level >= g_debug_level) THEN
1679 FND_LOG.STRING(g_excep_level, l_full_path,g_debug_msg );
1680 END IF;
1681 --Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
1682 END IF;
1683
1684 ELSIF (UPPER(x_conversion_type) <> 'USER' AND
1685 l_conversion_type IS NOT NULL AND
1686 UPPER(x_conversion_type) = 'EMU FIXED') OR
1687 (UPPER(x_conversion_type) NOT IN ('USER','EMU FIXED') AND
1688 (x_conversion_date IS NULL OR
1689 x_conversion_rate IS NULL)) THEN
1690 BEGIN
1691
1692 l_conversion_rate := gl_currency_api.get_rate (p_cc_header_rec.set_of_books_id,
1693 x_currency_code,
1694 NVL(x_conversion_date,SYSDATE),
1695 x_conversion_type
1696 );
1697
1698 x_conversion_rate := ROUND(l_conversion_rate,15);
1699 IF x_conversion_date IS NULL THEN
1700 x_conversion_date := SYSDATE;
1701 END IF;
1702
1703 EXCEPTION
1704
1705 WHEN OTHERS THEN
1706 FND_MESSAGE.SET_NAME('IGC', 'IGC_API_CONV_RATE_FAILURE');
1707 FND_MESSAGE.SET_TOKEN('CONV_TYPE', x_conversion_type, TRUE);
1708 FND_MESSAGE.SET_TOKEN('CONV_DATE', x_conversion_date, TRUE);
1709 FND_MESSAGE.SET_TOKEN('CURR_CODE', x_currency_code, TRUE);
1710 FND_MSG_PUB.ADD;
1711 l_error_count := l_error_count + 1;
1712 IF g_debug_mode = 'Y'
1713 THEN
1714 g_debug_msg := 'CC Open API Validate Get RATE is could not be obtained...';
1715 IF(g_excep_level >= g_debug_level) THEN
1716 FND_LOG.STRING(g_excep_level, l_full_path,g_debug_msg );
1717 END IF;
1718 --Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
1719 END IF;
1720 -- Bug 3199488
1721 IF ( g_unexp_level >= g_debug_level ) THEN
1722 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
1723 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
1724 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
1725 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
1726 END IF;
1727 -- Bug 3199488
1728
1729 END;
1730
1731 ELSIF (UPPER(x_conversion_type) <> 'USER' AND
1732 l_conversion_type IS NOT NULL AND
1733 UPPER(x_conversion_type) = 'EMU FIXED') OR
1734 (UPPER(x_conversion_type) NOT IN ('USER','EMU FIXED') AND
1735 (x_conversion_date IS NULL OR
1736 x_conversion_rate IS NOT NULL)) THEN
1737
1738 FND_MESSAGE.SET_NAME('IGC', 'IGC_API_CONV_RATE_NOT_ALLWD');
1739 FND_MESSAGE.SET_TOKEN('CONV_TYPE', x_conversion_type, TRUE);
1740 FND_MESSAGE.SET_TOKEN('CONV_DATE', x_conversion_date, TRUE);
1741 FND_MESSAGE.SET_TOKEN('CONV_RATE', x_conversion_rate, TRUE);
1742 FND_MSG_PUB.ADD;
1743 l_error_count := l_error_count + 1;
1744 IF g_debug_mode = 'Y'
1745 THEN
1746 g_debug_msg := 'CC Open API Validate Rate NOT required...';
1747 IF(g_excep_level >= g_debug_level) THEN
1748 FND_LOG.STRING(g_excep_level, l_full_path,g_debug_msg );
1749 END IF;
1750 --Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
1751 END IF;
1752
1753 END IF;
1754
1755 END IF;
1756
1757 IF x_currency_code = p_func_currency_code AND
1758 (x_conversion_type IS NOT NULL OR
1759 x_conversion_date IS NOT NULL OR
1760 x_conversion_rate IS NOT NULL ) THEN
1761 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_CONV_TYPE_RTDT_NULL');
1762 FND_MSG_PUB.ADD;
1763 x_conversion_type := NULL;
1764 x_conversion_date := NULL;
1765 x_conversion_rate := NULL;
1766 l_error_count := l_error_count + 1;
1767 IF g_debug_mode = 'Y'
1768 THEN
1769 g_debug_msg := 'CC Open API Validate Conversion type, rate, date not required...';
1770 IF(g_excep_level >= g_debug_level) THEN
1771 FND_LOG.STRING(g_excep_level, l_full_path,g_debug_msg );
1772 END IF;
1773 --Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
1774 END IF;
1775 END IF;
1776
1777 END IF;
1778
1779 -- -------------------------------------------------------------------
1780 -- Validate Created By
1781 -- -------------------------------------------------------------------
1782 IF p_cc_header_rec.created_by IS NOT NULL THEN
1783
1784 BEGIN
1785 SELECT user_id
1786 INTO l_user_id
1787 FROM fnd_user
1788 WHERE user_id = p_cc_header_rec.created_by;
1789
1790 EXCEPTION
1791
1792 WHEN NO_DATA_FOUND THEN
1793 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_INVALID_CREATED_BY');
1794 FND_MESSAGE.SET_TOKEN('CREATED_BY', TO_CHAR(p_cc_header_rec.created_by), TRUE);
1795 FND_MSG_PUB.ADD;
1796 l_error_count := l_error_count + 1;
1797 IF g_debug_mode = 'Y'
1798 THEN
1799 g_debug_msg := 'CC Open API Validate Invalid Created By...';
1800 IF(g_excep_level >= g_debug_level) THEN
1801 FND_LOG.STRING(g_excep_level, l_full_path,g_debug_msg );
1802 END IF;
1803 --Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
1804 END IF;
1805 END;
1806
1807 ELSE
1808
1809 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_INVALID_CREATED_BY');
1810 FND_MESSAGE.SET_TOKEN('CREATED_BY', TO_CHAR(p_cc_header_rec.created_by), TRUE);
1811 FND_MSG_PUB.ADD;
1812 l_error_count := l_error_count + 1;
1813 IF g_debug_mode = 'Y'
1814 THEN
1815 g_debug_msg := 'CC Open API Validate Invalid Created By is NULL...';
1816 IF(g_excep_level >= g_debug_level) THEN
1817 FND_LOG.STRING(g_excep_level, l_full_path,g_debug_msg );
1818 END IF;
1819 --Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
1820 END IF;
1821
1822 END IF;
1823
1824 -- -------------------------------------------------------------------
1825 -- Validate Last Updated By
1826 -- -------------------------------------------------------------------
1827 IF p_cc_header_rec.last_updated_by IS NOT NULL THEN
1828
1829 BEGIN
1830 SELECT user_id
1831 INTO l_user_id
1832 FROM fnd_user
1833 WHERE user_id = p_cc_header_rec.last_updated_by;
1834
1835 EXCEPTION
1836
1837 WHEN NO_DATA_FOUND THEN
1838 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_INVALID_LAST_UPDATED_BY');
1839 FND_MESSAGE.SET_TOKEN('LAST_UPDATED_BY', TO_CHAR(p_cc_header_rec.last_updated_by), TRUE);
1840 FND_MSG_PUB.ADD;
1841 l_error_count := l_error_count + 1;
1842 IF g_debug_mode = 'Y'
1843 THEN
1844 g_debug_msg := 'CC Open API Validate Invalid Last Updated By...';
1845 IF(g_excep_level >= g_debug_level) THEN
1846 FND_LOG.STRING(g_excep_level, l_full_path,g_debug_msg );
1847 END IF;
1848 --Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
1849 END IF;
1850 END;
1851 ELSE
1852
1853 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_INVALID_LAST_UPDATED_BY');
1854 FND_MESSAGE.SET_TOKEN('LAST_UPDATED_BY', TO_CHAR(p_cc_header_rec.last_updated_by), TRUE);
1855 FND_MSG_PUB.ADD;
1856 l_error_count := l_error_count + 1;
1857 IF g_debug_mode = 'Y'
1858 THEN
1859 g_debug_msg := 'CC Open API Validate Invalid Last Updated By is NULL...';
1860 IF(g_excep_level >= g_debug_level) THEN
1861 FND_LOG.STRING(g_excep_level, l_full_path,g_debug_msg );
1862 END IF;
1863 --Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
1864 END IF;
1865
1866 END IF;
1867
1868 -- -------------------------------------------------------------------
1869 -- Validate Last Update Login
1870 -- -------------------------------------------------------------------
1871 IF p_cc_header_rec.last_update_login IS NOT NULL THEN
1872
1873 BEGIN
1874 SELECT login_id
1875 INTO l_login_id
1876 FROM fnd_logins
1877 WHERE login_id = p_cc_header_rec.last_update_login;
1878
1879 EXCEPTION
1880
1881 WHEN NO_DATA_FOUND THEN
1882 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_INVALID_LAST_UPD_LOGIN');
1883 FND_MESSAGE.SET_TOKEN('LAST_UPDATE_LOGIN', TO_CHAR(p_cc_header_rec.last_update_login), TRUE);
1884 FND_MSG_PUB.ADD;
1885 l_error_count := l_error_count + 1;
1886 IF g_debug_mode = 'Y'
1887 THEN
1888 g_debug_msg := 'CC Open API Validate Invalid Last Update Login...';
1889 IF(g_excep_level >= g_debug_level) THEN
1890 FND_LOG.STRING(g_excep_level, l_full_path,g_debug_msg );
1891 END IF;
1892 -- Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
1893 END IF;
1894 END;
1895
1896 ELSE
1897
1898 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_INVALID_LAST_UPD_LOGIN');
1899 FND_MESSAGE.SET_TOKEN('LAST_UPDATE_LOGIN', TO_CHAR(p_cc_header_rec.last_update_login), TRUE);
1900 FND_MSG_PUB.ADD;
1901 l_error_count := l_error_count + 1;
1902 IF g_debug_mode = 'Y'
1903 THEN
1904 g_debug_msg := 'CC Open API Validate Invalid Last Update Login is NULL...';
1905 IF(g_excep_level >= g_debug_level) THEN
1906 FND_LOG.STRING(g_excep_level, l_full_path,g_debug_msg );
1907 END IF;
1908 --Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
1909 END IF;
1910
1911 END IF;
1912
1913 -- --------------------------------------------------------------------
1914 -- Ensure that all cursors are closed upon exit.
1915 -- --------------------------------------------------------------------
1916 IF (c_validate_sob_id%ISOPEN) THEN
1917 CLOSE c_validate_sob_id;
1918 END IF;
1919 IF (c_validate_org_id%ISOPEN) THEN
1920 CLOSE c_validate_org_id;
1921 END IF;
1922 IF (c_validate_sob_org_combo%ISOPEN) THEN
1923 CLOSE c_validate_sob_org_combo;
1924 END IF;
1925 IF (c_val_cover_state_stat%ISOPEN) THEN
1926 CLOSE c_val_cover_state_stat;
1927 END IF;
1928
1929 IF g_debug_mode = 'Y'
1930 THEN
1931 g_debug_msg := 'CC Open API Validate Error Count...' || to_char(l_error_count);
1932 Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
1933 END IF;
1934
1935 IF l_error_count > 0 THEN
1936 x_valid_cc := FND_API.G_FALSE;
1937 ELSE
1938 x_valid_cc := FND_API.G_TRUE;
1939 END IF;
1940
1941 RETURN;
1942
1943 -- --------------------------------------------------------------------
1944 -- Exception handler section for the CC_Open_API_Validate Procedure.
1945 -- --------------------------------------------------------------------
1946 EXCEPTION
1947
1948 WHEN OTHERS THEN
1949 x_valid_cc := FND_API.G_FALSE;
1950 x_currency_code := NULL;
1951 x_conversion_type := NULL;
1952 x_conversion_date := NULL;
1953 x_conversion_rate := NULL;
1954 IF g_debug_mode = 'Y'
1955 THEN
1956
1957 Output_Debug( l_full_path,'Exception encountered for this record.');
1958 END IF;
1959 IF (c_validate_sob_id%ISOPEN) THEN
1960 CLOSE c_validate_sob_id;
1961 END IF;
1962 IF (c_validate_org_id%ISOPEN) THEN
1963 CLOSE c_validate_org_id;
1964 END IF;
1965 IF (c_validate_sob_org_combo%ISOPEN) THEN
1966 CLOSE c_validate_sob_org_combo;
1967 END IF;
1968 IF (c_val_cover_state_stat%ISOPEN) THEN
1969 CLOSE c_val_cover_state_stat;
1970 END IF;
1971 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
1972 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
1973 END IF;
1974 -- Bug 3199488
1975 IF ( g_unexp_level >= g_debug_level ) THEN
1976 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
1977 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
1978 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
1979 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
1980 END IF;
1981 -- Bug 3199488
1982 RETURN;
1983
1984 END CC_OPEN_API_VALIDATE;
1985
1986
1987 PROCEDURE CC_OPEN_API_DERIVE (
1988 x_header_id OUT NOCOPY NUMBER
1989 ) IS
1990
1991 l_api_name VARCHAR2(30);
1992
1993 l_full_path VARCHAR(500);
1994 BEGIN
1995 --Added by svaithil for GSCC warnings
1996 l_api_name := 'CC_Open_API_Derive';
1997
1998 x_header_id := NULL;
1999 l_full_path := g_path||'cc_open_api_derive';
2000 SELECT igc_cc_headers_s.nextval
2001 INTO x_header_id
2002 FROM DUAL;
2003
2004 -- --------------------------------------------------------------------
2005 -- Exception handler section for the CC_Open_API_Derive Procedure.
2006 -- --------------------------------------------------------------------
2007 EXCEPTION
2008
2009 WHEN OTHERS THEN
2010 x_header_id := NULL;
2011 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
2012 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
2013 END IF;
2014 -- Bug 3199488
2015 IF ( g_unexp_level >= g_debug_level ) THEN
2016 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
2017 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
2018 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
2019 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
2020 END IF;
2021 -- Bug 3199488
2022 RETURN;
2023
2024 END CC_OPEN_API_DERIVE;
2025
2026
2027 -- To perform commitment action from an external system on a particular contract commitment.
2028
2029 PROCEDURE CC_Update_Control_Status_API (
2030 p_api_version IN NUMBER,
2031 p_init_msg_list IN VARCHAR2,
2032 p_commit IN VARCHAR2,
2033 p_validation_level IN NUMBER,
2034 p_cc_num IN igc_cc_headers.cc_num%TYPE,
2035 p_set_of_books_id IN igc_cc_headers.set_of_books_id%TYPE,
2036 p_org_id IN igc_cc_headers.org_id%TYPE,
2037 p_action_code IN fnd_lookups.lookup_code%TYPE,
2038 p_last_updated_by IN igc_cc_headers.last_updated_by%TYPE,
2039 p_last_update_login IN igc_cc_headers.last_update_login%TYPE,
2040 x_return_status OUT NOCOPY VARCHAR2,
2041 x_msg_count OUT NOCOPY NUMBER,
2042 x_msg_data OUT NOCOPY VARCHAR2
2043 ) IS
2044
2045 l_api_name VARCHAR2(30);
2046 l_api_version NUMBER;
2047 l_debug VARCHAR2 (1);
2048 l_cc_header_rec igc_cc_headers%ROWTYPE;
2049 l_cc_header_id igc_cc_headers.cc_header_id%TYPE;
2050 l_cc_ref_num igc_cc_headers.cc_ref_num%TYPE;
2051 l_cc_num igc_cc_headers.cc_num%TYPE;
2052 l_valid_cc VARCHAR2(2000);
2053 l_return_status VARCHAR2(1);
2054 l_result VARCHAR2(1);
2055 l_msg_count NUMBER;
2056 l_msg_data VARCHAR2(12000);
2057 l_encumbrance_flag VARCHAR2(1);
2058 l_new_ctrl_status igc_cc_headers.cc_ctrl_status%TYPE;
2059 l_new_apprvl_status igc_cc_headers.cc_apprvl_status%TYPE;
2060 l_prev_ctrl_status igc_cc_headers.cc_ctrl_status%TYPE;
2061 l_prev_apprvl_status igc_cc_headers.cc_apprvl_status%TYPE;
2062 l_action_type_code fnd_lookups.lookup_code%TYPE;
2063 l_action_meaning fnd_lookups.meaning%TYPE;
2064 l_control_meaning fnd_lookups.meaning%TYPE;
2065 l_seq VARCHAR2(40);
2066 l_itemkey igc_cc_headers.wf_item_key%TYPE;
2067 l_itemtype igc_cc_headers.wf_item_type%TYPE;
2068 l_row_id VARCHAR2(18);
2069 l_current_user_id NUMBER;
2070 l_current_login_id NUMBER;
2071 l_user_id NUMBER;
2072 l_login_id NUMBER;
2073 -- l_debug_mode VARCHAR2(1);
2074 l_name hr_all_organization_units.name%TYPE;
2075 l_current_org_id NUMBER;
2076 l_init_msg_list varchar2(2000);
2077 l_commit varchar2(2000);
2078 l_validation_level NUMBER;
2079
2080 CURSOR c_validate_sob_org_combo IS
2081 SELECT HAOU.name
2082 FROM hr_organization_information OOD,
2083 hr_all_organization_units HAOU
2084 WHERE OOD.organization_id = l_current_org_id
2085 AND OOD.organization_id = HAOU.organization_id
2086 AND OOD.org_information3 || '' = to_char(p_set_of_books_id)
2087 AND HAOU.organization_id || '' = OOD.organization_id;
2088
2089 CURSOR c_cc_header_exist IS
2090 SELECT cchd.cc_header_id
2091 FROM igc_cc_headers cchd
2092 WHERE cchd.cc_num = p_cc_num;
2093 /* AND cchd.set_of_books_id = p_set_of_books_id
2094 AND cchd.org_id = l_current_org_id; --Commented during MOAC up take*/
2095
2096 CURSOR c_val_ref_num IS
2097 SELECT cchd.cc_ref_num
2098 FROM igc_cc_headers cchd
2099 WHERE cchd.cc_header_id = l_cc_header_id
2100 AND cchd.cc_ref_num IS NOT NULL;
2101
2102 CURSOR c_cc_header_state IS
2103 SELECT *
2104 FROM igc_cc_headers cchd
2105 WHERE cchd.cc_header_id = l_cc_header_id
2106 AND cchd.cc_apprvl_status = 'AP'
2107 AND cchd.cc_state = 'CM';
2108
2109 e_invalid_action EXCEPTION;
2110 e_cc_not_found EXCEPTION;
2111
2112 l_full_path VARCHAR(500);
2113 BEGIN
2114
2115 --Added by svaithil for GSCC warnings
2116
2117 l_init_msg_list := nvl(p_init_msg_list,FND_API.G_FALSE);
2118 l_commit := nvl(p_commit,FND_API.G_FALSE);
2119 l_validation_level := nvl(p_validation_level,FND_API.G_VALID_LEVEL_FULL);
2120 l_api_name := 'CC_Update_Control_Status_API';
2121 l_api_version := 1.0;
2122 -- -------------------------------------------------------------------
2123 -- Initialize the return values
2124 -- -------------------------------------------------------------------
2125 x_return_status := FND_API.G_RET_STS_SUCCESS;
2126 x_msg_data := NULL;
2127 x_msg_count := 0;
2128 l_full_path := g_path||'cc_update_control_status_API';
2129 SAVEPOINT CC_Update_API_PT;
2130
2131 -- -------------------------------------------------------------------
2132 -- Setup Debug info for API usage if needed.
2133 -- -------------------------------------------------------------------
2134 -- l_debug := FND_PROFILE.VALUE('IGC_DEBUG_ENABLED');
2135 -- IF (l_debug = 'Y') THEN
2136 -- l_debug := FND_API.G_TRUE;
2137 -- ELSE
2138 -- l_debug := FND_API.G_FALSE;
2139 -- END IF;
2140 -- IGC_MSGS_PKG.g_debug_mode := FND_API.TO_BOOLEAN(l_debug);
2141 IF g_debug_mode = 'Y'
2142 THEN
2143 g_debug_msg := 'CC Update API Debug mode enabled...';
2144 Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
2145 END IF;
2146
2147 -- -------------------------------------------------------------------
2148 -- Make sure that the appropriate version is being used
2149 -- -------------------------------------------------------------------
2150 IF (NOT FND_API.Compatible_API_Call ( l_api_version,
2151 p_api_version,
2152 l_api_name,
2153 G_PKG_NAME )) THEN
2154 IF g_debug_mode = 'Y'
2155 THEN
2156 g_debug_msg := 'CC Update APi Incorrect version...';
2157 --Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
2158 IF(g_excep_level >= g_debug_level) THEN
2159 FND_LOG.STRING(g_excep_level, l_full_path,g_debug_msg );
2160 END IF;
2161 END IF;
2162 raise FND_API.G_EXC_UNEXPECTED_ERROR ;
2163 END IF;
2164
2165 -- -------------------------------------------------------------------
2166 -- Make sure that if the message stack is to be initialized it is.
2167 -- -------------------------------------------------------------------
2168 IF (FND_API.to_Boolean ( l_init_msg_list )) THEN
2169 FND_MSG_PUB.initialize ;
2170 END IF;
2171
2172 -- --------------------------------------------------------------------
2173 -- Update API logic.
2174 -- --------------------------------------------------------------------
2175 IF g_debug_mode = 'Y'
2176 THEN
2177 g_debug_msg := 'CC Update API Starts Here...';
2178 Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
2179 END IF;
2180
2181 -- --------------------------------------------------------------------
2182 -- Get the profile values
2183 -- --------------------------------------------------------------------
2184 l_current_user_id := p_last_updated_by;
2185 l_current_login_id := p_last_update_login;
2186 -- l_debug_mode := FND_PROFILE.VALUE('IGC_DEBUG_ENABLED');
2187 l_current_org_id := NVL(SUBSTRB(USERENV('CLIENT_INFO'),1,10),-99);
2188 IF (l_current_org_id = -99) THEN
2189 dbms_application_info.set_client_info(p_org_id);
2190 l_current_org_id := p_org_id;
2191 ELSE
2192 l_current_org_id := p_org_id;
2193 END IF;
2194
2195 -- -------------------------------------------------------------------
2196 -- Validate Org ID and set of Books ID Combination.
2197 -- -------------------------------------------------------------------
2198 OPEN c_validate_sob_org_combo;
2199 FETCH c_validate_sob_org_combo
2200 INTO l_name;
2201
2202 IF (c_validate_sob_org_combo%NOTFOUND) THEN
2203 FND_MESSAGE.SET_NAME('IGC', 'IGC_NO_SOB_ORG_COMBO');
2204 FND_MESSAGE.SET_TOKEN('SOB_ID', TO_CHAR(p_set_of_books_id), TRUE);
2205 FND_MESSAGE.SET_TOKEN('ORG_ID', TO_CHAR(p_org_id), TRUE);
2206 FND_MSG_PUB.ADD;
2207 x_msg_data := FND_MESSAGE.GET;
2208 x_return_status := FND_API.G_RET_STS_ERROR;
2209 x_msg_count := 1;
2210 IF g_debug_mode = 'Y'
2211 THEN
2212 g_debug_msg := 'CC Update API Validate Set of books ID and Org ID Combo Failed...';
2213 IF(g_excep_level >= g_debug_level) THEN
2214 FND_LOG.STRING(g_excep_level, l_full_path,g_debug_msg );
2215 END IF;
2216 --Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
2217 END IF;
2218 RAISE E_CC_NOT_FOUND;
2219 END IF;
2220
2221 -- --------------------------------------------------------------------
2222 -- Make sure that the CC can be found.
2223 -- --------------------------------------------------------------------
2224 OPEN c_cc_header_exist;
2225 FETCH c_cc_header_exist
2226 INTO l_cc_header_id;
2227
2228 IF (c_cc_header_exist%NOTFOUND) THEN
2229 FND_MESSAGE.SET_NAME ('IGC', 'IGC_CC_NOT_FOUND');
2230 FND_MESSAGE.SET_TOKEN('CC_NUM', p_cc_num, TRUE);
2231 FND_MSG_PUB.ADD;
2232 x_msg_data := FND_MESSAGE.GET;
2233 x_return_status := FND_API.G_RET_STS_ERROR;
2234 x_msg_count := 1;
2235 IF g_debug_mode = 'Y'
2236 THEN
2237 g_debug_msg := 'CC Update APi CC Found or not...'||x_msg_data;
2238 IF(g_excep_level >= g_debug_level) THEN
2239 FND_LOG.STRING(g_excep_level, l_full_path,g_debug_msg );
2240 END IF;
2241 --Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
2242 END IF;
2243 RAISE E_CC_NOT_FOUND;
2244 END IF;
2245
2246 -- --------------------------------------------------------------------
2247 -- Validate that the CC Reference number is NOT NULL
2248 -- --------------------------------------------------------------------
2249 OPEN c_val_ref_num;
2250 FETCH c_val_ref_num
2251 INTO l_cc_ref_num;
2252
2253 IF (c_val_ref_num%NOTFOUND) THEN
2254 FND_MESSAGE.SET_NAME ('IGC', 'IGC_CC_NO_UPD_NO_REF');
2255 FND_MESSAGE.SET_TOKEN('CC_NUM', p_cc_num,TRUE);
2256 FND_MSG_PUB.ADD;
2257 x_msg_data := FND_MESSAGE.GET;
2258 x_return_status := FND_API.G_RET_STS_ERROR;
2259 x_msg_count := 1;
2260 IF g_debug_mode = 'Y'
2261 THEN
2262 g_debug_msg := 'CC Update APi CC Found or not...'||x_msg_data;
2263 IF(g_excep_level >= g_debug_level) THEN
2264 FND_LOG.STRING(g_excep_level, l_full_path,g_debug_msg );
2265 END IF;
2266 --Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
2267 END IF;
2268 RAISE E_CC_NOT_FOUND;
2269 END IF;
2270
2271 OPEN c_cc_header_state;
2272 FETCH c_cc_header_state
2273 INTO l_cc_header_rec;
2274
2275 IF (c_cc_header_state%NOTFOUND) THEN
2276 FND_MESSAGE.SET_NAME ('IGC', 'IGC_CC_NOT_APPRVD_CONFIRMED');
2277 FND_MESSAGE.SET_TOKEN('CC_NUM', p_cc_num, TRUE);
2278 FND_MSG_PUB.ADD;
2279 x_msg_data := FND_MESSAGE.GET;
2280 x_return_status := FND_API.G_RET_STS_ERROR;
2281 x_msg_count := 1;
2282 IF g_debug_mode = 'Y'
2283 THEN
2284 g_debug_msg := 'CC Update APi CC Found or not approved / confirmed...'||x_msg_data;
2285 IF(g_excep_level >= g_debug_level) THEN
2286 FND_LOG.STRING(g_excep_level, l_full_path,g_debug_msg );
2287 END IF;
2288 --Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
2289 END IF;
2290 RAISE E_CC_NOT_FOUND;
2291 END IF;
2292
2293 IF l_cc_header_rec.cc_type = 'C' THEN
2294 FND_MESSAGE.SET_NAME ('IGC', 'IGC_CC_TYPE_NOT_ALWD');
2295 FND_MSG_PUB.ADD;
2296 x_msg_data := FND_MESSAGE.GET;
2297 x_return_status := FND_API.G_RET_STS_ERROR;
2298 x_msg_count := 1;
2299 IF g_debug_mode = 'Y'
2300 THEN
2301 g_debug_msg := 'CC Update APi Action Not Allowed...'||x_msg_data;
2302 IF(g_excep_level >= g_debug_level) THEN
2303 FND_LOG.STRING(g_excep_level, l_full_path,g_debug_msg );
2304 END IF;
2305 --Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
2306 END IF;
2307 RAISE E_INVALID_ACTION;
2308 END IF;
2309
2310 BEGIN
2311 SELECT lkup.lookup_code,
2312 lkup.meaning
2313 INTO l_action_type_code,
2314 l_action_meaning
2315 FROM fnd_lookups lkup
2316 WHERE lkup.lookup_type = 'IGC_CC_ACTION_TYPE'
2317 AND lkup.lookup_code = p_action_code
2318 AND lkup.lookup_code IN ('OP','CL','OH','RH');
2319
2320 EXCEPTION
2321 WHEN NO_DATA_FOUND THEN
2322 FND_MESSAGE.SET_NAME ('IGC', 'IGC_CC_INVALID_ACTION_CODE');
2323 FND_MESSAGE.SET_TOKEN('ACTION_CODE', p_action_code,TRUE);
2324 FND_MSG_PUB.ADD;
2325 x_msg_data := FND_MESSAGE.GET;
2326 x_return_status := FND_API.G_RET_STS_ERROR;
2327 x_msg_count := 1;
2328 IF g_debug_mode = 'Y'
2329 THEN
2330 g_debug_msg := 'CC Update APi Invalid Action Code...'||x_msg_data;
2331 IF(g_excep_level >= g_debug_level) THEN
2332 FND_LOG.STRING(g_excep_level, l_full_path,g_debug_msg );
2333 END IF;
2334 --Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
2335 END IF;
2336 RAISE E_INVALID_ACTION;
2337 END;
2338
2339 -- -------------------------------------------------------------------
2340 -- Validate Last Update Login
2341 -- -------------------------------------------------------------------
2342 IF l_current_login_id IS NOT NULL THEN
2343
2344 BEGIN
2345 SELECT login_id
2346 INTO l_login_id
2347 FROM fnd_logins
2348 WHERE login_id = l_current_login_id;
2349
2350 EXCEPTION
2351
2352 WHEN NO_DATA_FOUND THEN
2353 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_INVALID_LAST_UPD_LOGIN');
2354 FND_MESSAGE.SET_TOKEN('LAST_UPDATE_LOGIN', TO_CHAR(l_current_login_id), TRUE);
2355 FND_MSG_PUB.ADD;
2356 x_msg_data := FND_MESSAGE.GET;
2357 x_return_status := FND_API.G_RET_STS_ERROR;
2358 x_msg_count := 1;
2359 IF g_debug_mode = 'Y'
2360 THEN
2361 g_debug_msg := 'CC Update API Validate Invalid Last Update Login...';
2362 IF(g_excep_level >= g_debug_level) THEN
2363 FND_LOG.STRING(g_excep_level, l_full_path,g_debug_msg );
2364 END IF;
2365 --Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
2366 END IF;
2367 RAISE E_CC_NOT_FOUND;
2368 END;
2369
2370 ELSE
2371
2372 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_INVALID_LAST_UPD_LOGIN');
2373 FND_MESSAGE.SET_TOKEN('LAST_UPDATE_LOGIN', TO_CHAR(l_current_login_id), TRUE);
2374 FND_MSG_PUB.ADD;
2375 x_msg_data := FND_MESSAGE.GET;
2376 x_return_status := FND_API.G_RET_STS_ERROR;
2377 x_msg_count := 1;
2378 IF g_debug_mode = 'Y'
2379 THEN
2380 g_debug_msg := 'CC Update API Validate Invalid Last Update Login is NULL...';
2381 IF(g_excep_level >= g_debug_level) THEN
2382 FND_LOG.STRING(g_excep_level, l_full_path,g_debug_msg );
2383 END IF;
2384 --Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
2385 END IF;
2386 RAISE E_CC_NOT_FOUND;
2387
2388 END IF;
2389
2390 -- -------------------------------------------------------------------
2391 -- Validate Last Updated By
2392 -- -------------------------------------------------------------------
2393 IF l_current_user_id IS NOT NULL THEN
2394
2395 BEGIN
2396 SELECT user_id
2397 INTO l_user_id
2398 FROM fnd_user
2399 WHERE user_id = l_current_user_id;
2400
2401 EXCEPTION
2402
2403 WHEN NO_DATA_FOUND THEN
2404 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_INVALID_LAST_UPDATED_BY');
2405 FND_MESSAGE.SET_TOKEN('LAST_UPDATED_BY', TO_CHAR(l_current_user_id), TRUE);
2406 FND_MSG_PUB.ADD;
2407 x_msg_data := FND_MESSAGE.GET;
2408 x_return_status := FND_API.G_RET_STS_ERROR;
2409 x_msg_count := 1;
2410 IF g_debug_mode = 'Y'
2411 THEN
2412 g_debug_msg := 'CC Update API Validate Invalid Last Updated By...';
2413 IF(g_excep_level >= g_debug_level) THEN
2414 FND_LOG.STRING(g_excep_level, l_full_path,g_debug_msg );
2415 END IF;
2416 --Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
2417 END IF;
2418 RAISE E_CC_NOT_FOUND;
2419 END;
2420 ELSE
2421
2422 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_INVALID_LAST_UPDATED_BY');
2423 FND_MESSAGE.SET_TOKEN('LAST_UPDATED_BY', TO_CHAR(l_current_user_id), TRUE);
2424 FND_MSG_PUB.ADD;
2425 x_msg_data := FND_MESSAGE.GET;
2426 x_return_status := FND_API.G_RET_STS_ERROR;
2427 x_msg_count := 1;
2428 IF g_debug_mode = 'Y'
2429 THEN
2430 g_debug_msg := 'CC Update API Validate Invalid Last Updated By is NULL...';
2431 IF(g_excep_level >= g_debug_level) THEN
2432 FND_LOG.STRING(g_excep_level, l_full_path,g_debug_msg );
2433 END IF;
2434 --Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
2435 END IF;
2436 RAISE E_CC_NOT_FOUND;
2437
2438 END IF;
2439
2440 BEGIN
2441 SELECT lkup.lookup_code,
2442 lkup.meaning
2443 INTO l_action_type_code,
2444 l_control_meaning
2445 FROM fnd_lookups lkup
2446 WHERE lkup.lookup_type = 'IGC_CC_CONTROL_STATUS'
2447 AND lkup.lookup_code = l_cc_header_rec.cc_ctrl_status;
2448
2449 EXCEPTION
2450 WHEN NO_DATA_FOUND THEN
2451 FND_MESSAGE.SET_NAME ('IGC', 'IGC_CC_INVALID_CONTROL_STATUS');
2452 FND_MESSAGE.SET_TOKEN('CTRL_STATUS', l_cc_header_rec.cc_ctrl_status,TRUE);
2453 FND_MSG_PUB.ADD;
2454 x_msg_data := FND_MESSAGE.GET;
2455 x_return_status := FND_API.G_RET_STS_ERROR;
2456 x_msg_count := 1;
2457 IF g_debug_mode = 'Y'
2458 THEN
2459 g_debug_msg := 'CC Update APi Invalid Control Status ...'||x_msg_data;
2460 IF(g_excep_level >= g_debug_level) THEN
2461 FND_LOG.STRING(g_excep_level, l_full_path,g_debug_msg );
2462 END IF;
2463 --Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
2464 END IF;
2465 RAISE E_INVALID_ACTION;
2466 END;
2467
2468 -- --------------------------------------------------------------------
2469 -- Check if the commitment action is allowed or not.
2470 -- --------------------------------------------------------------------
2471 IF (l_cc_header_rec.cc_ctrl_status = 'E') THEN
2472
2473 IF p_action_code NOT IN ('OP','OH') THEN
2474 FND_MESSAGE.SET_NAME ('IGC', 'IGC_CC_ACTION_NOT_ALLOWED');
2475 FND_MESSAGE.SET_TOKEN('CODE_MEANING', l_action_meaning,TRUE);
2476 FND_MESSAGE.SET_TOKEN('CC_NUM', p_cc_num,TRUE);
2477 FND_MESSAGE.SET_TOKEN('CONT_STATUS', l_control_meaning,TRUE);
2478 FND_MSG_PUB.ADD;
2479 x_msg_data := FND_MESSAGE.GET;
2480 x_return_status := FND_API.G_RET_STS_ERROR;
2481 x_msg_count := 1;
2482 IF g_debug_mode = 'Y'
2483 THEN
2484 g_debug_msg := 'CC Update APi '||p_action_code||' Action not allowed ...'||x_msg_data;
2485 IF(g_excep_level >= g_debug_level) THEN
2486 FND_LOG.STRING(g_excep_level, l_full_path,g_debug_msg );
2487 END IF;
2488 --Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
2489 END IF;
2490 RAISE E_INVALID_ACTION;
2491 END IF;
2492
2493 ELSIF (l_cc_header_rec.cc_ctrl_status = 'O') THEN
2494
2495 IF p_action_code NOT IN ('CL','OH') THEN
2496 FND_MESSAGE.SET_NAME ('IGC', 'IGC_CC_ACTION_NOT_ALLOWED');
2497 FND_MESSAGE.SET_TOKEN('CODE_MEANING', l_action_meaning,TRUE);
2498 FND_MESSAGE.SET_TOKEN('CC_NUM', p_cc_num,TRUE);
2499 FND_MESSAGE.SET_TOKEN('CONT_STATUS', l_control_meaning,TRUE);
2500 FND_MSG_PUB.ADD;
2501 x_msg_data := FND_MESSAGE.GET;
2502 x_return_status := FND_API.G_RET_STS_ERROR;
2503 x_msg_count := 1;
2504 IF g_debug_mode = 'Y'
2505 THEN
2506 g_debug_msg := 'CC Update APi '||p_action_code||' Action not allowed ...'||x_msg_data;
2507 IF(g_excep_level >= g_debug_level) THEN
2508 FND_LOG.STRING(g_excep_level, l_full_path,g_debug_msg );
2509 END IF;
2510 --Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
2511 END IF;
2512 RAISE E_INVALID_ACTION;
2513 END IF;
2514
2515 ELSIF (l_cc_header_rec.cc_ctrl_status = 'C') THEN
2516
2517 IF p_action_code NOT IN ('OP','OH') THEN
2518 FND_MESSAGE.SET_NAME ('IGC', 'IGC_CC_ACTION_NOT_ALLOWED');
2519 FND_MESSAGE.SET_TOKEN('CODE_MEANING', l_action_meaning,TRUE);
2520 FND_MESSAGE.SET_TOKEN('CC_NUM', p_cc_num,TRUE);
2521 FND_MESSAGE.SET_TOKEN('CONT_STATUS', l_control_meaning,TRUE);
2522 FND_MSG_PUB.ADD;
2523 x_msg_data := FND_MESSAGE.GET;
2524 x_return_status := FND_API.G_RET_STS_ERROR;
2525 x_msg_count := 1;
2526 IF g_debug_mode = 'Y'
2527 THEN
2528 g_debug_msg := 'CC Update APi '||p_action_code||' Action not allowed ...'||x_msg_data;
2529 IF(g_excep_level >= g_debug_level) THEN
2530 FND_LOG.STRING(g_excep_level, l_full_path,g_debug_msg );
2531 END IF;
2532 --Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
2533 END IF;
2534 RAISE E_INVALID_ACTION;
2535 END IF;
2536
2537 ELSIF (l_cc_header_rec.cc_ctrl_status = 'H') THEN
2538
2539 IF p_action_code NOT IN ('RH') THEN
2540 FND_MESSAGE.SET_NAME ('IGC', 'IGC_CC_ACTION_NOT_ALLOWED');
2541 FND_MESSAGE.SET_TOKEN('CODE_MEANING', l_action_meaning,TRUE);
2542 FND_MESSAGE.SET_TOKEN('CC_NUM', p_cc_num,TRUE);
2543 FND_MESSAGE.SET_TOKEN('CONT_STATUS', l_control_meaning,TRUE);
2544 FND_MSG_PUB.ADD;
2545 x_msg_data := FND_MESSAGE.GET;
2546 x_return_status := FND_API.G_RET_STS_ERROR;
2547 x_msg_count := 1;
2548 IF g_debug_mode = 'Y'
2549 THEN
2550 g_debug_msg := 'CC Update APi '||p_action_code||' Action not allowed ...'||x_msg_data;
2551 IF(g_excep_level >= g_debug_level) THEN
2552 FND_LOG.STRING(g_excep_level, l_full_path,g_debug_msg );
2553 END IF;
2554 --Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
2555 END IF;
2556 RAISE E_INVALID_ACTION;
2557 END IF;
2558
2559 END IF;
2560
2561 -- --------------------------------------------------------------------
2562 -- Retaining the previous statuses of the contract
2563 -- --------------------------------------------------------------------
2564 l_prev_ctrl_status := l_cc_header_rec.cc_ctrl_status;
2565 l_prev_apprvl_status := l_cc_header_rec.cc_apprvl_status;
2566
2567 IF (p_action_code = 'OP') THEN
2568 l_new_ctrl_status := 'O';
2569 ELSIF (p_action_code = 'CL') THEN
2570 l_new_ctrl_status := 'C';
2571 ELSIF (p_action_code = 'OH') THEN
2572 l_new_ctrl_status := 'H';
2573 ELSIF (p_action_code = 'RH') THEN
2574 l_new_ctrl_status := 'O';
2575 l_new_apprvl_status := 'RR';
2576 ELSE
2577 FND_MESSAGE.SET_NAME ('IGC', 'IGC_CC_INVALID_ACTION_CODE');
2578 FND_MESSAGE.SET_TOKEN('ACTION_CODE', p_action_code,TRUE);
2579 FND_MSG_PUB.ADD;
2580 x_msg_data := FND_MESSAGE.GET;
2581 x_return_status := FND_API.G_RET_STS_ERROR;
2582 x_msg_count := 1;
2583 IF g_debug_mode = 'Y'
2584 THEN
2585 g_debug_msg := 'CC Update APi Invalid Action Code...'||x_msg_data;
2586 IF(g_excep_level >= g_debug_level) THEN
2587 FND_LOG.STRING(g_excep_level, l_full_path,g_debug_msg );
2588 END IF;
2589 --Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
2590 END IF;
2591 RAISE E_INVALID_ACTION;
2592 END IF;
2593
2594 IF p_action_code = 'RH' THEN
2595 IF g_debug_mode = 'Y'
2596 THEN
2597 g_debug_msg := 'CC Update APi Release On Hold begins here ...';
2598 Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
2599 END IF;
2600
2601 -- --------------------------------------------------------------------
2602 -- Check Budgetary Control is on
2603 -- --------------------------------------------------------------------
2604 IGC_CC_BUDGETARY_CTRL_PKG.CHECK_BUDGETARY_CTRL_ON (p_api_version => 1.0,
2605 p_init_msg_list => FND_API.G_FALSE,
2606 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2607 X_return_status => l_return_status,
2608 X_msg_count => l_msg_count,
2609 X_msg_data => l_msg_data,
2610 p_org_id => l_cc_header_rec.org_id,
2611 p_sob_id => l_cc_header_rec.set_of_books_id,
2612 p_cc_state => l_cc_header_rec.cc_state,
2613 X_encumbrance_on => l_encumbrance_flag
2614 );
2615
2616 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2617 x_msg_data := FND_MESSAGE.GET;
2618 x_return_status := FND_API.G_RET_STS_ERROR;
2619 x_msg_count := 1;
2620 IF g_debug_mode = 'Y'
2621 THEN
2622 g_debug_msg := 'CC Update APi Check Budgetary Control Not Successful...'||x_msg_data;
2623 IF(g_excep_level >= g_debug_level) THEN
2624 FND_LOG.STRING(g_excep_level, l_full_path,g_debug_msg );
2625 END IF;
2626 --Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
2627 END IF;
2628 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2629 ELSE
2630 IF g_debug_mode = 'Y'
2631 THEN
2632 g_debug_msg := 'CC Update APi Validate CC begins here ...';
2633 Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
2634 END IF;
2635
2636 -- --------------------------------------------------------------------
2637 -- Validate the Accounting Date.
2638 -- --------------------------------------------------------------------
2639 IGC_CC_BUDGETARY_CTRL_PKG.Validate_CC (p_api_version => 1.0,
2640 p_init_msg_list => FND_API.G_FALSE,
2641 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2642 x_return_status => l_return_status,
2643 x_msg_count => l_msg_count,
2644 x_msg_data => l_msg_data,
2645 p_cc_header_id => l_cc_header_rec.cc_header_id,
2646 X_valid_cc => l_valid_cc,
2647 p_mode => 'E',
2648 p_field_from => 'APPROVAL',
2649 p_encumbrance_flag => l_encumbrance_flag,
2650 p_sob_id => l_cc_header_rec.set_of_books_id,
2651 p_org_id => l_cc_header_rec.org_id,
2652 p_start_date => TRUNC(l_cc_header_rec.cc_start_date),
2653 p_end_date => TRUNC(l_cc_header_rec.cc_end_date),
2654 p_cc_type_code => l_cc_header_rec.cc_type,
2655 p_parent_cc_header_id => l_cc_header_rec.parent_header_id,
2656 p_cc_det_pf_date => NULL,
2657 p_acct_date => TRUNC(l_cc_header_rec.cc_acct_date),
2658 p_prev_acct_date => TRUNC(l_cc_header_rec.cc_acct_date),
2659 p_cc_state => l_cc_header_rec.cc_state
2660 );
2661
2662 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2663 x_msg_data := FND_MESSAGE.GET;
2664 x_return_status := FND_API.G_RET_STS_ERROR;
2665 x_msg_count := 1;
2666 IF g_debug_mode = 'Y'
2667 THEN
2668 g_debug_msg := 'CC Update APi Validate CC Not Successful...'||x_msg_data;
2669 IF(g_excep_level >= g_debug_level) THEN
2670 FND_LOG.STRING(g_excep_level, l_full_path,g_debug_msg );
2671 END IF;
2672 --Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
2673 END IF;
2674 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2675 ELSE
2676 IF g_debug_mode = 'Y'
2677 THEN
2678 g_debug_msg := 'CC Update APi Approval Process begins here ...';
2679 Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
2680 END IF;
2681
2682 SELECT to_char(IGC_CC_WF_ITEMKEY_S.NEXTVAL)
2683 INTO l_seq
2684 FROM sys.dual;
2685
2686 l_itemkey := TO_CHAR(l_cc_header_id) || '-' || l_seq;
2687 l_itemtype := 'CCAPPWF';
2688
2689 UPDATE igc_cc_headers_all ICH
2690 SET ICH.cc_ctrl_status = l_new_ctrl_status,
2691 ICH.cc_apprvl_status = l_new_apprvl_status,
2692 ICH.wf_item_type = 'CCAPPWF',
2693 ICH.wf_item_key = l_itemkey
2694 WHERE ICH.cc_header_id = l_cc_header_id
2695 AND ICH.cc_num = l_cc_header_rec.cc_num
2696 AND ICH.set_of_books_id = l_cc_header_rec.set_of_books_id
2697 AND ICH.org_id = l_cc_header_rec.org_id;
2698
2699 -- ---------------------------------------------------------------------
2700 -- If the number of rows updated is NOT 1 then an exception must be
2701 -- encountered.
2702 -- ---------------------------------------------------------------------
2703 IF ((SQL%ROWCOUNT <> 1) AND (SQL%ROWCOUNT <> 0) ) THEN
2704 IF g_debug_mode = 'Y'
2705 THEN
2706 g_debug_msg := 'CC Update APi Incorrect Update ...';
2707 IF(g_excep_level >= g_debug_level) THEN
2708 FND_LOG.STRING(g_excep_level, l_full_path,g_debug_msg );
2709 END IF;
2710 --Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
2711 END IF;
2712
2713 ROLLBACK to CC_Update_API_PT;
2714
2715 FND_MESSAGE.SET_NAME ('IGC', 'IGC_CC_NOT_FOUND');
2716 FND_MESSAGE.SET_TOKEN ('CC_NUM', to_char(l_cc_header_id));
2717 x_msg_data := FND_MESSAGE.GET;
2718 x_return_status := FND_API.G_RET_STS_ERROR;
2719 x_msg_count := 1;
2720 ELSE
2721
2722 IF g_debug_mode = 'Y'
2723 THEN
2724 g_debug_msg := 'CC Update APi Insert into Action History begins here ...';
2725 Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
2726 END IF;
2727
2728 -- ------------------------------------------------------------
2729 -- Insert record into table IGC_CC_ACTIONS for action history.
2730 -- ------------------------------------------------------------
2731 IGC_CC_ACTIONS_PKG.Insert_Row (1.0,
2732 FND_API.G_FALSE,
2733 FND_API.G_FALSE,
2734 FND_API.G_VALID_LEVEL_FULL,
2735 l_return_status,
2736 l_msg_count,
2737 l_msg_data,
2738 l_row_id,
2739 l_cc_header_id,
2740 NVL(l_cc_header_rec.cc_version_num, 0),
2741 SUBSTR(p_action_code,1,2),
2742 l_cc_header_rec.cc_state,
2743 l_new_ctrl_status,
2744 l_new_apprvl_status,
2745 'CC Update API',
2746 SYSDATE,
2747 l_current_user_id,
2748 l_current_login_id,
2749 SYSDATE,
2750 l_current_user_id
2751 );
2752
2753 IF l_return_status IN ('E','U') THEN
2754 ROLLBACK to CC_Update_API_PT;
2755 x_msg_data := FND_MESSAGE.GET;
2756 x_return_status := FND_API.G_RET_STS_ERROR;
2757 x_msg_count := 1;
2758 IF g_debug_mode = 'Y'
2759 THEN
2760 g_debug_msg := 'CC Update APi Action History Insertion Not Successful...'||l_msg_data;
2761 IF(g_excep_level >= g_debug_level) THEN
2762 FND_LOG.STRING(g_excep_level, l_full_path,g_debug_msg );
2763 END IF;
2764 --Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
2765 END IF;
2766 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2767 ELSE
2768
2769 -- ------------------------------------------------------------
2770 -- Commit Have to be done here.
2771 -- ------------------------------------------------------------
2772 IF g_debug_mode = 'Y'
2773 THEN
2774 g_debug_msg := 'CC Update API Commiting before Approval process...';
2775 Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
2776 END IF;
2777 COMMIT WORK;
2778
2779 END IF;
2780
2781 IF g_debug_mode = 'Y'
2782 THEN
2783 g_debug_msg := 'CC Update APi Preparer Can Approve Check begins here ...';
2784 Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
2785 END IF;
2786
2787 -- ------------------------------------------------------------
2788 -- Approval process.
2789 -- ------------------------------------------------------------
2790 IGC_CC_APPROVAL_PROCESS.preparer_can_approve (p_api_version => 1.0,
2791 p_init_msg_list => FND_API.G_FALSE,
2792 p_commit => FND_API.G_FALSE,
2793 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2794 x_return_status => l_return_status,
2795 x_msg_count => l_msg_count,
2796 x_msg_data => l_msg_data,
2797 p_org_id => l_cc_header_rec.org_id,
2798 p_cc_state => l_cc_header_rec.cc_state,
2799 p_cc_type => l_cc_header_rec.cc_type,
2800 x_result => l_result
2801 );
2802
2803 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2804 x_msg_data := FND_MESSAGE.GET;
2805 x_return_status := FND_API.G_RET_STS_ERROR;
2806 x_msg_count := 1;
2807 IF g_debug_mode = 'Y'
2808 THEN
2809 g_debug_msg := 'CC Update APi Preparer Can Approve Not Successful...'||x_msg_data;
2810 IF(g_excep_level >= g_debug_level) THEN
2811 FND_LOG.STRING(g_excep_level, l_full_path,g_debug_msg );
2812 END IF;
2813 --Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
2814 END IF;
2815 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2816 ELSE
2817 IF l_result <> FND_API.G_TRUE THEN
2818 IF g_debug_mode = 'Y'
2819 THEN
2820 g_debug_msg := 'CC Update APi Workflow Call begins here ...'||l_result;
2821 Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
2822 END IF;
2823
2824 -- ------------------------------------------------------------
2825 -- call to Approval Workflow Procedure
2826 -- ------------------------------------------------------------
2827 IGC_CC_APPROVAL_WF_PKG.Start_Process (p_api_version => 1.0,
2828 p_init_msg_list => FND_API.G_FALSE,
2829 p_commit => FND_API.G_FALSE,
2830 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2831 p_wf_version => 2,
2832 x_return_status => l_return_status,
2833 x_msg_count => l_msg_count,
2834 x_msg_data => l_msg_data,
2835 p_item_key => l_itemkey,
2836 p_cc_header_id => l_cc_header_rec.cc_header_id,
2837 p_acct_date => l_cc_header_rec.cc_acct_date,
2838 p_note => 'CC Update API',
2839 p_debug_mode => g_debug_mode
2840 );
2841 ELSE
2842
2843 IF g_debug_mode = 'Y'
2844 THEN
2845 g_debug_msg := 'CC Update APi Approved by preparer begins here ...';
2846 Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
2847 END IF;
2848
2849 IGC_CC_APPROVAL_PROCESS.approved_by_preparer (
2850 p_api_version => 1.0,
2851 p_init_msg_list => FND_API.G_FALSE,
2852 p_commit => FND_API.G_FALSE,
2853 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2854 p_return_status => l_return_status,
2855 p_msg_count => l_msg_count,
2856 p_msg_data => l_msg_data,
2857 p_cc_header_id => l_cc_header_rec.cc_header_id,
2858 p_org_id => l_cc_header_rec.org_id,
2859 p_sob_id => l_cc_header_rec.set_of_books_id,
2860 p_cc_state => l_cc_header_rec.cc_state,
2861 p_cc_type => l_cc_header_rec.cc_type,
2862 p_cc_preparer_id => l_cc_header_rec.cc_preparer_user_id,
2863 p_cc_owner_id => l_cc_header_rec.cc_owner_user_id,
2864 p_cc_current_owner => l_cc_header_rec.cc_current_user_id,
2865 p_cc_apprvl_status => l_cc_header_rec.cc_apprvl_status,
2866 p_cc_encumb_status => l_cc_header_rec.cc_encmbrnc_status,
2867 p_cc_ctrl_status => l_cc_header_rec.cc_ctrl_status,
2868 p_cc_version_number => l_cc_header_rec.cc_version_num,
2869 p_cc_notes => 'CC Update API',
2870 p_acct_date => SYSDATE
2871 );
2872
2873 END IF; -- l_result validation.
2874 END IF; -- Preparer Can approve call.
2875 END IF; -- Update of statuses.
2876 END IF; -- Validate CC Accounting date validation.
2877 END IF; -- Check Budgetary Control on or not.
2878
2879 ELSIF p_action_code IN ('OP','CL','OH') THEN
2880
2881 IF g_debug_mode = 'Y'
2882 THEN
2883 g_debug_msg := 'CC Update APi Open, Close, On Hold Actions begins here ...';
2884 Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
2885 END IF;
2886
2887 UPDATE igc_cc_headers_all ICH
2888 SET ICH.cc_ctrl_status = l_new_ctrl_status
2889 WHERE ICH.cc_header_id = l_cc_header_id
2890 AND ICH.cc_num = l_cc_header_rec.cc_num
2891 AND ICH.set_of_books_id = l_cc_header_rec.set_of_books_id
2892 AND ICH.org_id = l_cc_header_rec.org_id;
2893
2894 -- ---------------------------------------------------------------------
2895 -- If the number of rows updated is NOT 1 then an exception must be
2896 -- encountered.
2897 -- ---------------------------------------------------------------------
2898 IF ((SQL%ROWCOUNT <> 1) AND (SQL%ROWCOUNT <> 0) ) THEN
2899
2900 ROLLBACK to CC_Update_API_PT;
2901
2902 FND_MESSAGE.SET_NAME ('IGC', 'IGC_CC_NOT_FOUND');
2903 FND_MESSAGE.SET_TOKEN ('CC_NUM', to_char(l_cc_header_id));
2904 x_msg_data := FND_MESSAGE.GET;
2905 x_return_status := FND_API.G_RET_STS_ERROR;
2906 x_msg_count := 1;
2907 IF g_debug_mode = 'Y'
2908 THEN
2909 g_debug_msg := 'CC Update APi Incorrect Update...'||x_msg_data;
2910 IF(g_excep_level >= g_debug_level) THEN
2911 FND_LOG.STRING(g_excep_level, l_full_path,g_debug_msg );
2912 END IF;
2913 --Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
2914 END IF;
2915
2916 ELSE
2917
2918 IGC_CC_PO_INTERFACE_PKG.UPDATE_PO_APPROVED_FLAG (
2919 p_api_version => 1.0,
2920 p_init_msg_list => FND_API.G_FALSE,
2921 p_commit => FND_API.G_FALSE,
2922 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2923 X_return_status => l_return_status,
2924 X_msg_count => l_msg_count,
2925 X_msg_data => l_msg_data,
2926 p_cc_header_id => l_cc_header_rec.cc_header_id);
2927
2928 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2929
2930 ROLLBACK to CC_Update_API_PT;
2931 x_msg_data := FND_MESSAGE.GET;
2932 x_return_status := FND_API.G_RET_STS_ERROR;
2933 x_msg_count := 1;
2934 IF g_debug_mode = 'Y'
2935 THEN
2936 g_debug_msg := 'CC Update APi Update PO Approved Flag Not Successful...'||x_msg_data;
2937 IF(g_excep_level >= g_debug_level) THEN
2938 FND_LOG.STRING(g_excep_level, l_full_path,g_debug_msg );
2939 END IF;
2940 --Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
2941 END IF;
2942 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2943
2944 ELSE
2945
2946 IF g_debug_mode = 'Y'
2947 THEN
2948 g_debug_msg := 'CC Update APi Insert into Action History begins here ...';
2949 Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
2950 END IF;
2951
2952 -- ------------------------------------------------------------
2953 -- Insert record into table IGC_CC_ACTIONS for action history.
2954 -- ------------------------------------------------------------
2955 IGC_CC_ACTIONS_PKG.Insert_Row (1.0,
2956 FND_API.G_FALSE,
2957 FND_API.G_FALSE,
2958 FND_API.G_VALID_LEVEL_FULL,
2959 l_return_status,
2960 l_msg_count,
2961 l_msg_data,
2962 l_row_id,
2963 l_cc_header_id,
2964 NVL(l_cc_header_rec.cc_version_num, 0),
2965 SUBSTR(p_action_code,1,2),
2966 l_cc_header_rec.cc_state,
2967 l_new_ctrl_status,
2968 l_cc_header_rec.cc_apprvl_status,
2969 'CC Update API',
2970 sysdate,
2971 l_current_user_id,
2972 l_current_login_id,
2973 sysdate,
2974 l_current_user_id );
2975
2976 IF l_return_status IN ('E','U') THEN
2977 ROLLBACK to CC_Update_API_PT;
2978 x_msg_data := FND_MESSAGE.GET;
2979 x_return_status := FND_API.G_RET_STS_ERROR;
2980 x_msg_count := 1;
2981 IF g_debug_mode = 'Y'
2982 THEN
2983 g_debug_msg := 'CC Update APi Action History Insertion Not Successful...'||l_msg_data;
2984 IF(g_excep_level >= g_debug_level) THEN
2985 FND_LOG.STRING(g_excep_level, l_full_path,g_debug_msg );
2986 END IF;
2987 --Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
2988 END IF;
2989 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2990 ELSE
2991
2992 IF FND_API.To_Boolean(l_commit) THEN
2993 IF g_debug_mode = 'Y'
2994 THEN
2995 g_debug_msg := 'CC Update API Commiting After Successful Commitment Actions Open or Close or On Hold...';
2996 Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
2997 END IF;
2998 COMMIT WORK;
2999 END IF;
3000
3001 END IF;
3002 END IF; -- Update PO Approved Flag results.
3003 END IF; -- Update Control Statuses.
3004 END IF; -- Commitment Actions line Open , Close , On Hold.
3005
3006 -- --------------------------------------------------------------------
3007 -- Close Cursor
3008 -- --------------------------------------------------------------------
3009 IF (c_cc_header_state%ISOPEN) THEN
3010 CLOSE c_cc_header_state;
3011 END IF;
3012 IF (c_cc_header_exist%ISOPEN) THEN
3013 CLOSE c_cc_header_exist;
3014 END IF;
3015 IF (c_val_ref_num%ISOPEN) THEN
3016 CLOSE c_val_ref_num;
3017 END IF;
3018 IF (c_validate_sob_org_combo%ISOPEN) THEN
3019 CLOSE c_validate_sob_org_combo;
3020 END IF;
3021
3022 RETURN;
3023
3024 -- --------------------------------------------------------------------
3025 -- Exception handler section for the CC_Update_API procedure.
3026 -- --------------------------------------------------------------------
3027 EXCEPTION
3028
3029 WHEN FND_API.G_EXC_ERROR THEN
3030 x_msg_data := FND_MESSAGE.GET;
3031 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3032 x_msg_count := 1;
3033 ROLLBACK to CC_Update_API_PT;
3034 IF (c_cc_header_state%ISOPEN) THEN
3035 CLOSE c_cc_header_state;
3036 END IF;
3037 IF (c_cc_header_exist%ISOPEN) THEN
3038 CLOSE c_cc_header_exist;
3039 END IF;
3040 IF (c_val_ref_num%ISOPEN) THEN
3041 CLOSE c_val_ref_num;
3042 END IF;
3043 IF (c_validate_sob_org_combo%ISOPEN) THEN
3044 CLOSE c_validate_sob_org_combo;
3045 END IF;
3046 -- Bug 3199488
3047 IF ( g_excep_level >= g_debug_level ) THEN
3048 FND_LOG.STRING (g_excep_level,l_full_path,'FND_API.G_EXC_ERROR Exception Raised');
3049 END IF;
3050 -- Bug 3199488
3051 RETURN;
3052
3053 WHEN E_INVALID_ACTION THEN
3054 x_msg_data := FND_MESSAGE.GET;
3055 x_return_status := FND_API.G_RET_STS_ERROR;
3056 x_msg_count := 1;
3057 ROLLBACK to CC_Update_API_PT;
3058 IF (c_cc_header_state%ISOPEN) THEN
3059 CLOSE c_cc_header_state;
3060 END IF;
3061 IF (c_cc_header_exist%ISOPEN) THEN
3062 CLOSE c_cc_header_exist;
3063 END IF;
3064 IF (c_val_ref_num%ISOPEN) THEN
3065 CLOSE c_val_ref_num;
3066 END IF;
3067 IF (c_validate_sob_org_combo%ISOPEN) THEN
3068 CLOSE c_validate_sob_org_combo;
3069 END IF;
3070 -- Bug 3199488
3071 IF ( g_excep_level >= g_debug_level ) THEN
3072 FND_LOG.STRING (g_excep_level,l_full_path,'E_INVALID_ACTION Exception Raised');
3073 END IF;
3074 -- Bug 3199488
3075 RETURN;
3076
3077 WHEN E_CC_NOT_FOUND THEN
3078 x_msg_data := FND_MESSAGE.GET;
3079 x_return_status := FND_API.G_RET_STS_ERROR;
3080 x_msg_count := 1;
3081 ROLLBACK to CC_Update_API_PT;
3082 IF (c_cc_header_state%ISOPEN) THEN
3083 CLOSE c_cc_header_state;
3084 END IF;
3085 IF (c_cc_header_exist%ISOPEN) THEN
3086 CLOSE c_cc_header_exist;
3087 END IF;
3088 IF (c_val_ref_num%ISOPEN) THEN
3089 CLOSE c_val_ref_num;
3090 END IF;
3091 IF (c_validate_sob_org_combo%ISOPEN) THEN
3092 CLOSE c_validate_sob_org_combo;
3093 END IF;
3094 -- Bug 3199488
3095 IF ( g_excep_level >= g_debug_level ) THEN
3096 FND_LOG.STRING (g_excep_level,l_full_path,'E_CC_NOT_FOUND Exception Raised');
3097 END IF;
3098 -- Bug 3199488
3099 RETURN;
3100
3101 WHEN OTHERS THEN
3102 x_msg_data := FND_MESSAGE.GET;
3103 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3104 x_msg_count := 1;
3105 IF (c_cc_header_state%ISOPEN) THEN
3106 CLOSE c_cc_header_state;
3107 END IF;
3108 IF (c_cc_header_exist%ISOPEN) THEN
3109 CLOSE c_cc_header_exist;
3110 END IF;
3111 IF (c_val_ref_num%ISOPEN) THEN
3112 CLOSE c_val_ref_num;
3113 END IF;
3114 IF (c_validate_sob_org_combo%ISOPEN) THEN
3115 CLOSE c_validate_sob_org_combo;
3116 END IF;
3117 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
3118 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
3119 END IF;
3120 -- Bug 3199488
3121 IF ( g_unexp_level >= g_debug_level ) THEN
3122 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
3123 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
3124 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
3125 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
3126 END IF;
3127 -- Bug 3199488
3128 RETURN;
3129
3130 END CC_Update_Control_Status_API;
3131
3132
3133 --
3134 -- Output_Debug Procedure is the Generic procedure designed for outputting debug
3135 -- information that is required from this procedure.
3136 --
3137 -- Parameters :
3138 --
3139 -- p_debug_msg ==> Record to be output into the debug log file.
3140 --
3141 PROCEDURE Output_Debug (
3142 p_path IN VARCHAR2,
3143 p_debug_msg IN VARCHAR2
3144 ) IS
3145
3146 -- Constants :
3147
3148 /* l_prod VARCHAR2(3) := 'IGC';
3149 l_sub_comp VARCHAR2(6) := 'CC_API';
3150 l_profile_name VARCHAR2(255) := 'IGC_DEBUG_LOG_DIRECTORY';
3151 l_Return_Status VARCHAR2(1);
3152 l_api_name CONSTANT VARCHAR2(30) := 'Output_Debug';*/
3153
3154 BEGIN
3155
3156 /* IGC_MSGS_PKG.Put_Debug_Msg (p_debug_message => p_debug_msg,
3157 p_profile_log_name => l_profile_name,
3158 p_prod => l_prod,
3159 p_sub_comp => l_sub_comp,
3160 p_filename_val => NULL,
3161 x_Return_Status => l_Return_Status
3162 );
3163
3164 IF (l_Return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
3165 raise FND_API.G_EXC_ERROR;
3166 END IF;
3167 */
3168 IF(g_state_level >= g_debug_level) THEN
3169 FND_LOG.STRING(g_state_level,p_path, p_debug_msg);
3170 END IF;
3171 RETURN;
3172
3173 -- --------------------------------------------------------------------
3174 -- Exception handler section for the Output_Debug procedure.
3175 -- --------------------------------------------------------------------
3176 EXCEPTION
3177
3178 WHEN FND_API.G_EXC_ERROR THEN
3179 RETURN;
3180
3181 WHEN OTHERS THEN
3182 /*IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
3183 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
3184 END IF;*/
3185 RETURN;
3186
3187 END Output_Debug;
3188
3189 -- ---------------------------------------------------------------------------
3190 -- The CC_Get_API procedure is designed to be an API that can
3191 -- be used by external systems to obtain the existing reference number for
3192 -- a given Contract Commitment Number
3193 -- ---------------------------------------------------------------------------
3194 PROCEDURE CC_Get_API (
3195 p_api_version IN NUMBER,
3196 p_init_msg_list IN VARCHAR2,
3197 p_commit IN VARCHAR2,
3198 p_validation_level IN NUMBER,
3199 p_cc_num IN igc_cc_headers.cc_num%TYPE,
3200 p_org_id IN igc_cc_headers.org_id%TYPE,
3201 p_set_of_books_id IN igc_cc_headers.set_of_books_id%TYPE,
3202 x_cc_header_id OUT NOCOPY igc_cc_headers.cc_header_id%TYPE,
3203 x_cc_ref_num OUT NOCOPY igc_cc_headers.cc_ref_num%TYPE,
3204 x_return_status OUT NOCOPY VARCHAR2,
3205 x_msg_count OUT NOCOPY NUMBER,
3206 x_msg_data OUT NOCOPY VARCHAR2
3207 ) IS
3208
3209 CURSOR c_check_cc_num IS
3210 SELECT ICH.cc_header_id,
3211 ICH.cc_ref_num
3212 FROM igc_cc_headers ICH
3213 WHERE ICH.org_id = p_org_id
3214 AND ICH.set_of_books_id = p_set_of_books_id
3215 AND ICH.cc_num = p_cc_num;
3216
3217 CURSOR c_validate_sob_org_combo IS
3218 SELECT HAOU.name
3219 FROM hr_organization_information OOD,
3220 hr_all_organization_units HAOU
3221 WHERE OOD.organization_id = p_org_id
3222 AND OOD.organization_id = HAOU.organization_id
3223 AND OOD.org_information3 || '' = to_char(p_set_of_books_id)
3224 AND HAOU.organization_id || '' = OOD.organization_id;
3225
3226 l_api_name VARCHAR2(30);
3227 l_api_version NUMBER;
3228 l_debug VARCHAR2 (1);
3229 l_name hr_all_organization_units.name%TYPE;
3230
3231 l_full_path VARCHAR(500);
3232 l_init_msg_list varchar2(2000);
3233 l_commit varchar2(2000);
3234 l_validation_level NUMBER;
3235 BEGIN
3236 --Added by svaithil for GSCC warnings
3237
3238 l_init_msg_list := nvl(p_init_msg_list,FND_API.G_FALSE);
3239 l_commit := nvl(p_commit,FND_API.G_FALSE);
3240 l_validation_level := nvl(p_validation_level,FND_API.G_VALID_LEVEL_FULL);
3241 l_api_name := 'CC_Get_API';
3242 l_api_version := 1.0;
3243
3244 -- -------------------------------------------------------------------
3245 -- Initialize the return values
3246 -- -------------------------------------------------------------------
3247 x_cc_header_id := NULL;
3248 x_cc_ref_num := NULL;
3249 x_return_status := FND_API.G_RET_STS_SUCCESS;
3250 x_msg_data := NULL;
3251 x_msg_count := 0;
3252 l_full_path := g_path||'cc_get_API';
3253 -- -------------------------------------------------------------------
3254 -- Setup Debug info for API usage if needed.
3255 -- -------------------------------------------------------------------
3256 -- l_debug := FND_PROFILE.VALUE('IGC_DEBUG_ENABLED');
3257 -- IF (l_debug = 'Y') THEN
3258 -- l_debug := FND_API.G_TRUE;
3259 -- ELSE
3260 -- l_debug := FND_API.G_FALSE;
3261 -- END IF;
3262 -- IGC_MSGS_PKG.g_debug_mode := FND_API.TO_BOOLEAN(l_debug);
3263
3264 -- -------------------------------------------------------------------
3265 -- Make sure that the appropriate version is being used
3266 -- -------------------------------------------------------------------
3267 IF (NOT FND_API.Compatible_API_Call ( l_api_version,
3268 p_api_version,
3269 l_api_name,
3270 G_PKG_NAME )) THEN
3271 raise FND_API.G_EXC_UNEXPECTED_ERROR ;
3272 END IF;
3273
3274 -- -------------------------------------------------------------------
3275 -- Make sure that if the message stack is to be initialized it is.
3276 -- -------------------------------------------------------------------
3277 IF (FND_API.to_Boolean ( l_init_msg_list )) THEN
3278 FND_MSG_PUB.initialize ;
3279 END IF;
3280
3281 -- -------------------------------------------------------------------
3282 -- Validate Org ID and set of Books ID Combination.
3283 -- -------------------------------------------------------------------
3284 OPEN c_validate_sob_org_combo;
3285 FETCH c_validate_sob_org_combo
3286 INTO l_name;
3287
3288 IF (c_validate_sob_org_combo%NOTFOUND) THEN
3289
3290 FND_MESSAGE.SET_NAME('IGC', 'IGC_NO_SOB_ORG_COMBO');
3291 FND_MESSAGE.SET_TOKEN('SOB_ID', TO_CHAR(p_set_of_books_id), TRUE);
3292 FND_MESSAGE.SET_TOKEN('ORG_ID', TO_CHAR(p_org_id), TRUE);
3293 FND_MSG_PUB.ADD;
3294 x_msg_data := FND_MESSAGE.GET;
3295 x_return_status := FND_API.G_RET_STS_ERROR;
3296 x_msg_count := 1;
3297 x_cc_header_id := NULL;
3298 x_cc_ref_num := NULL;
3299
3300 ELSE
3301
3302 -- -------------------------------------------------------------------
3303 -- Open the Cursor that will determine if the CC Header ID can be
3304 -- found based upon the CC number, ORG ID, and SOB ID.
3305 -- -------------------------------------------------------------------
3306 OPEN c_check_cc_num;
3307 FETCH c_check_cc_num
3308 INTO x_cc_header_id,
3309 x_cc_ref_num;
3310
3311 IF (c_check_cc_num%NOTFOUND) THEN
3312
3313 FND_MESSAGE.SET_NAME ('IGC', 'IGC_CC_NOT_FOUND');
3314 FND_MESSAGE.SET_TOKEN ('CC_NUM', p_cc_num);
3315 FND_MSG_PUB.ADD;
3316 x_msg_data := FND_MESSAGE.GET;
3317 x_return_status := FND_API.G_RET_STS_ERROR;
3318 x_msg_count := 1;
3319 x_cc_header_id := NULL;
3320 x_cc_ref_num := NULL;
3321
3322 END IF;
3323
3324 END IF;
3325
3326 -- -------------------------------------------------------------------
3327 -- Close all cursors.
3328 -- -------------------------------------------------------------------
3329 IF (c_check_cc_num%ISOPEN) THEN
3330 CLOSE c_check_cc_num;
3331 END IF;
3332 IF (c_validate_sob_org_combo%ISOPEN) THEN
3333 CLOSE c_validate_sob_org_combo;
3334 END IF;
3335
3336 RETURN;
3337
3338 EXCEPTION
3339
3340 WHEN OTHERS THEN
3341 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3342 x_cc_header_id := NULL;
3343 x_cc_ref_num := NULL;
3344 IF (c_check_cc_num%ISOPEN) THEN
3345 CLOSE c_check_cc_num;
3346 END IF;
3347 IF (c_validate_sob_org_combo%ISOPEN) THEN
3348 CLOSE c_validate_sob_org_combo;
3349 END IF;
3350 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
3351 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
3352 l_api_name);
3353 END IF;
3354 -- Bug 3199488
3355 IF ( g_unexp_level >= g_debug_level ) THEN
3356 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
3357 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
3358 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
3359 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
3360 END IF;
3361 -- Bug 3199488
3362 RETURN;
3363
3364 END CC_Get_API;
3365
3366 -- ---------------------------------------------------------------------------
3367 -- The CC_Link_API procedure is designed to be an API that can
3368 -- be used by external systems to Link a Contract Commitment document to a
3369 -- document that was created via an External System
3370 -- ---------------------------------------------------------------------------
3371 PROCEDURE CC_Link_API (
3372 p_api_version IN NUMBER,
3373 p_init_msg_list IN VARCHAR2,
3374 p_commit IN VARCHAR2,
3375 p_validation_level IN NUMBER ,
3376 p_cc_ref_num IN igc_cc_headers.cc_ref_num%TYPE,
3377 p_org_id IN igc_cc_headers.org_id%TYPE,
3378 p_set_of_books_id IN igc_cc_headers.set_of_books_id%TYPE,
3379 p_cc_num IN igc_cc_headers.cc_num%TYPE,
3380 x_return_status OUT NOCOPY VARCHAR2,
3381 x_msg_count OUT NOCOPY NUMBER,
3382 x_msg_data OUT NOCOPY VARCHAR2
3383 ) IS
3384
3385 CURSOR c_check_cc_num IS
3386 SELECT ICH.cc_num,
3387 ICH.cc_header_id
3388 FROM igc_cc_headers ICH
3389 WHERE /*ICH.org_id = p_org_id
3390 AND ICH.set_of_books_id = p_set_of_books_id
3391 AND --Commented during r12 MOAC uptake */
3392 ICH.cc_num = p_cc_num;
3393
3394 CURSOR c_check_dup_ref_num IS
3395 SELECT ICH.cc_ref_num
3396 FROM igc_cc_headers ICH
3397 WHERE /*ICH.org_id = p_org_id
3398 AND ICH.set_of_books_id = p_set_of_books_id
3399 AND --Commented during MOAC uptake */
3400 ICH.cc_ref_num = p_cc_ref_num;
3401
3402 CURSOR c_validate_sob_org_combo IS
3403 SELECT HAOU.name
3404 FROM hr_organization_information OOD,
3405 hr_all_organization_units HAOU
3406 WHERE OOD.organization_id = p_org_id
3407 AND OOD.organization_id = HAOU.organization_id
3408 AND OOD.org_information3 || '' = to_char(p_set_of_books_id)
3409 AND HAOU.organization_id || '' = OOD.organization_id;
3410
3411 l_api_name VARCHAR2(30);
3412 l_api_version NUMBER ;
3413 l_debug VARCHAR2(1);
3414 l_cc_num igc_cc_headers.cc_num%TYPE;
3415 l_cc_ref_num igc_cc_headers.cc_ref_num%TYPE;
3416 l_cc_header_id igc_cc_headers.cc_header_id%TYPE;
3417 l_name hr_all_organization_units.name%TYPE;
3418
3419 l_full_path VARCHAR(500);
3420 l_init_msg_list varchar2(2000);
3421 l_commit varchar2(2000);
3422 l_validation_level NUMBER;
3423 BEGIN
3424
3425 --Added by svaithil for GSCC warnings
3426
3427 l_init_msg_list := nvl(p_init_msg_list,FND_API.G_FALSE);
3428 l_commit := nvl(p_commit,FND_API.G_FALSE);
3429 l_validation_level := nvl(p_validation_level,FND_API.G_VALID_LEVEL_FULL);
3430 l_api_name := 'CC_Link_API';
3431 l_api_version := 1.0;
3432 -- -------------------------------------------------------------------
3433 -- Initialize the return values
3434 -- -------------------------------------------------------------------
3435 x_return_status := FND_API.G_RET_STS_SUCCESS;
3436 x_msg_data := NULL;
3437 x_msg_count := 0;
3438
3439 SAVEPOINT CC_Link_API_PT;
3440 l_full_path := g_path||'cc_link_API';
3441
3442 -- -------------------------------------------------------------------
3443 -- Setup Debug info for API usage if needed.
3444 -- -------------------------------------------------------------------
3445 -- l_debug := FND_PROFILE.VALUE('IGC_DEBUG_ENABLED');
3446 -- IF (l_debug = 'Y') THEN
3447 -- l_debug := FND_API.G_TRUE;
3448 -- ELSE
3449 -- l_debug := FND_API.G_FALSE;
3450 -- END IF;
3451 -- IGC_MSGS_PKG.g_debug_mode := FND_API.TO_BOOLEAN(l_debug);
3452
3453 -- --------------------------------------------------------------------
3454 -- Make sure that the appropriate version is being used
3455 -- --------------------------------------------------------------------
3456 IF (NOT FND_API.Compatible_API_Call ( l_api_version,
3457 p_api_version,
3458 l_api_name,
3459 G_PKG_NAME )) THEN
3460 raise FND_API.G_EXC_UNEXPECTED_ERROR ;
3461 END IF;
3462
3463 -- --------------------------------------------------------------------
3464 -- Make sure that if the message stack is to be initialized it is.
3465 -- --------------------------------------------------------------------
3466 IF (FND_API.to_Boolean ( l_init_msg_list )) THEN
3467 FND_MSG_PUB.initialize ;
3468 END IF;
3469
3470 -- -------------------------------------------------------------------
3471 -- Validate Org ID and set of Books ID Combination.
3472 -- -------------------------------------------------------------------
3473 OPEN c_validate_sob_org_combo;
3474 FETCH c_validate_sob_org_combo
3475 INTO l_name;
3476
3477 IF (c_validate_sob_org_combo%NOTFOUND) THEN
3478
3479 FND_MESSAGE.SET_NAME('IGC', 'IGC_NO_SOB_ORG_COMBO');
3480 FND_MESSAGE.SET_TOKEN('SOB_ID', TO_CHAR(p_set_of_books_id), TRUE);
3481 FND_MESSAGE.SET_TOKEN('ORG_ID', TO_CHAR(p_org_id), TRUE);
3482 FND_MSG_PUB.ADD;
3483 x_msg_data := FND_MESSAGE.GET;
3484 x_return_status := FND_API.G_RET_STS_ERROR;
3485 x_msg_count := 1;
3486
3487 ELSE
3488
3489 -- --------------------------------------------------------------------
3490 -- Make sure that the CC can be found.
3491 -- --------------------------------------------------------------------
3492 OPEN c_check_cc_num;
3493 FETCH c_check_cc_num
3494 INTO l_cc_num,
3495 l_cc_header_id;
3496
3497 IF (c_check_cc_num%NOTFOUND) THEN
3498
3499 FND_MESSAGE.SET_NAME ('IGC', 'IGC_CC_NOT_FOUND');
3500 FND_MESSAGE.SET_TOKEN ('CC_NUM', p_cc_num);
3501 FND_MSG_PUB.ADD;
3502 x_msg_data := FND_MESSAGE.GET;
3503 x_return_status := FND_API.G_RET_STS_ERROR;
3504 x_msg_count := 1;
3505
3506 ELSE
3507
3508 OPEN c_check_dup_ref_num;
3509 FETCH c_check_dup_ref_num
3510 INTO l_cc_ref_num;
3511
3512 IF (c_check_dup_ref_num%NOTFOUND) THEN
3513
3514 UPDATE igc_cc_headers_all ICH
3515 SET ICH.cc_ref_num = p_cc_ref_num
3516 WHERE ICH.cc_header_id = l_cc_header_id
3517 AND ICH.cc_num = p_cc_num
3518 AND ICH.set_of_books_id = p_set_of_books_id
3519 AND ICH.org_id = p_org_id;
3520
3521 -- ---------------------------------------------------------------------
3522 -- If the number of rows updated is NOT 1 then an exception must be
3523 -- encountered.
3524 -- ---------------------------------------------------------------------
3525 IF ( (SQL%ROWCOUNT <> 1) AND (SQL%ROWCOUNT <> 0) ) THEN
3526
3527 ROLLBACK to CC_Link_API_PT;
3528
3529 FND_MESSAGE.SET_NAME ('IGC', 'IGC_CC_NOT_FOUND');
3530 FND_MESSAGE.SET_TOKEN ('CC_NUM', p_cc_num);
3531 FND_MSG_PUB.ADD;
3532 x_msg_data := FND_MESSAGE.GET;
3533 x_return_status := FND_API.G_RET_STS_ERROR;
3534 x_msg_count := 1;
3535
3536 END IF;
3537
3538 ELSE
3539
3540 -- --------------------------------------------------------------------
3541 -- Duplicate Reference numbers not allowed for the same org and SOB
3542 -- --------------------------------------------------------------------
3543 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_DUP_CC_REF_NUM');
3544 FND_MESSAGE.SET_TOKEN('CC_REF_NUM', p_cc_ref_num);
3545 FND_MSG_PUB.ADD;
3546 x_msg_data := FND_MESSAGE.GET;
3547 x_return_status := FND_API.G_RET_STS_ERROR;
3548 x_msg_count := 1;
3549
3550 END IF;
3551
3552 END IF;
3553
3554 END IF;
3555
3556 -- --------------------------------------------------------------------
3557 -- Committing the record based on the value passed as a parameter.
3558 -- --------------------------------------------------------------------
3559 IF FND_API.To_Boolean(l_commit) THEN
3560 IF g_debug_mode = 'Y'
3561 THEN
3562 g_debug_msg := 'CC Link API Commiting After Successful Link...';
3563 Output_Debug( l_full_path,p_debug_msg => g_debug_msg);
3564 END IF;
3565 COMMIT WORK;
3566 END IF;
3567
3568 -- --------------------------------------------------------------------
3569 -- Close Cursor
3570 -- --------------------------------------------------------------------
3571 IF (c_check_cc_num%ISOPEN) THEN
3572 CLOSE c_check_cc_num;
3573 END IF;
3574 IF (c_check_dup_ref_num%ISOPEN) THEN
3575 CLOSE c_check_dup_ref_num;
3576 END IF;
3577 IF (c_validate_sob_org_combo%ISOPEN) THEN
3578 CLOSE c_validate_sob_org_combo;
3579 END IF;
3580
3581 RETURN;
3582
3583 EXCEPTION
3584
3585 WHEN OTHERS THEN
3586 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3587 IF (c_check_cc_num%ISOPEN) THEN
3588 CLOSE c_check_cc_num;
3589 END IF;
3590 IF (c_check_dup_ref_num%ISOPEN) THEN
3591 CLOSE c_check_dup_ref_num;
3592 END IF;
3593 IF (c_validate_sob_org_combo%ISOPEN) THEN
3594 CLOSE c_validate_sob_org_combo;
3595 END IF;
3596
3597 ROLLBACK to CC_Link_API_PT;
3598
3599 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
3600 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
3601 l_api_name);
3602 END IF;
3603 -- Bug 3199488
3604 IF ( g_unexp_level >= g_debug_level ) THEN
3605 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
3606 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
3607 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
3608 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
3609 END IF;
3610 -- Bug 3199488
3611 RETURN;
3612
3613 END CC_Link_API;
3614
3615 /* Added below API during MOAC uptake for bug#6341012
3616 This procedure intilalizes global variables and MOAC initialization will be done
3617 It also validates the ORG_ID and sets ORG Context */
3618
3619 PROCEDURE Set_Global_Info
3620 (p_api_version_number IN NUMBER,
3621 p_responsibility_id IN NUMBER,
3622 p_user_id IN NUMBER,
3623 p_resp_appl_id IN NUMBER,
3624 p_operating_unit_id IN NUMBER,
3625 x_return_status OUT NOCOPY VARCHAR2,
3626 x_msg_count OUT NOCOPY NUMBER,
3627 x_msg_data OUT NOCOPY VARCHAR2
3628
3629 ) IS
3630 l_operating_unit_id NUMBER;
3631 l_api_version_number CONSTANT NUMBER := 1.0;
3632 l_api_name CONSTANT VARCHAR2(30):= 'Set_Global_Info';
3633 l_value_conversion_error BOOLEAN := FALSE;
3634 l_return_status VARCHAR2(1);
3635 l_dummy VARCHAR2(1);
3636 l_temp_num NUMBER ;
3637
3638 l_msg_data varchar2(2000);
3639 l_msg_count number;
3640 l_product_code varchar2(3) := 'IGI';
3641
3642
3643 /** cursor l_resp_csr to check the combination of
3644 resposibility and application id. **/
3645
3646 CURSOR l_resp_csr IS
3647 SELECT 'x'
3648 FROM fnd_responsibility
3649 WHERE responsibility_id = p_responsibility_id
3650 AND application_id = p_resp_appl_id;
3651
3652
3653
3654 CURSOR l_user_csr IS
3655 SELECT 'x'
3656 FROM fnd_user
3657 WHERE user_id = p_user_id;
3658 l_resp_csr_rec l_resp_csr%ROWTYPE;
3659
3660 BEGIN
3661 -- Standard Api compatibility call
3662 IF NOT FND_API.Compatible_API_Call ( l_api_version_number ,
3663 p_api_version_number ,
3664 l_api_name ,
3665 G_PKG_NAME )
3666 THEN
3667 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3668 END IF;
3669
3670 x_return_status := FND_API.G_RET_STS_SUCCESS;
3671
3672
3673 -- Ensure the responsibility id passed is valid
3674 IF p_responsibility_id IS NULL THEN
3675 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3676 THEN
3677 FND_MESSAGE.SET_NAME('IGC','IGC_RESP_ID_INVALID');
3678 FND_MSG_PUB.add;
3679 END IF;
3680
3681 RAISE FND_API.G_EXC_ERROR;
3682 END IF;
3683
3684 OPEN l_resp_csr;
3685 FETCH l_resp_csr INTO l_dummy;
3686 IF l_resp_csr%NOTFOUND THEN
3687 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3688 FND_MESSAGE.SET_NAME('IGC','IGC_RESP_ID_INVALID');
3689 FND_MSG_PUB.add;
3690 END IF;
3691 CLOSE l_resp_csr;
3692 RAISE FND_API.G_EXC_ERROR;
3693 ELSE
3694 CLOSE l_resp_csr;
3695 END IF;
3696
3697 -- Ensure the user id passed is valid
3698 IF p_user_id IS NULL THEN
3699 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3700 THEN
3701 FND_MESSAGE.SET_NAME('IGC','IGC_USER_ID_INVALID');
3702 FND_MSG_PUB.add;
3703 END IF;
3704
3705 RAISE FND_API.G_EXC_ERROR;
3706 END IF;
3707
3708
3709 OPEN l_user_csr ;
3710 FETCH l_user_csr INTO l_dummy;
3711 IF l_user_csr%NOTFOUND THEN
3712 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3713 FND_MESSAGE.SET_NAME('IGC','IGC_USER_ID_INVALID');
3714 FND_MSG_PUB.add;
3715 END IF;
3716 CLOSE l_user_csr;
3717 RAISE FND_API.G_EXC_ERROR;
3718 ELSE
3719 CLOSE l_user_csr; END IF;
3720
3721
3722 -- Based on the Responsibility, Intialize the Application
3723
3724 FND_GLOBAL.Apps_Initialize
3725 ( user_id => p_user_id
3726 , resp_id => p_responsibility_id
3727 , resp_appl_id => p_resp_appl_id
3728 );
3729
3730
3731
3732 If NVL(mo_global.get_ou_count, 0) = 0 then
3733 MO_GLOBAL.INIT(l_product_code);
3734 end if ;
3735
3736 l_operating_unit_id := GET_VALID_OU(p_operating_unit_id, l_product_code);
3737
3738 IF l_operating_unit_id IS NULL THEN
3739 FND_MSG_PUB.Initialize;
3740
3741 x_return_status := FND_API.G_RET_STS_ERROR;
3742
3743 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3744
3745 FND_MESSAGE.SET_NAME('IGC','IGC_MOAC_PASS_VALID_ORG');
3746 FND_MSG_PUB.add;
3747 FND_MSG_PUB.Count_And_Get
3748 ( p_count => x_msg_count ,
3749 p_data => x_msg_data );
3750 END IF;
3751 else
3752 MO_GLOBAL.SET_POLICY_CONTEXT('S',l_operating_unit_id);
3753 END IF;
3754
3755 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3756 RAISE FND_API.G_EXC_ERROR ;
3757 END IF ;
3758
3759 -- -----------------------------------------------------------------------------
3760
3761
3762 EXCEPTION
3763
3764 WHEN FND_API.G_EXC_ERROR
3765 THEN
3766
3767 x_return_status := FND_API.G_RET_STS_ERROR;
3768
3769 FND_MSG_PUB.Count_And_Get
3770 ( p_count => x_msg_count ,
3771 p_data => x_msg_data );
3772
3773 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
3774 THEN
3775 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3776
3777 FND_MSG_PUB.Count_And_Get
3778 ( p_count => x_msg_count ,
3779 p_data => x_msg_data );
3780
3781 WHEN OTHERS
3782 THEN
3783
3784 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3785
3786 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3787 THEN
3788 FND_MSG_PUB.add_exc_msg
3789 ( p_pkg_name => G_PKG_NAME
3790 , p_procedure_name => l_api_name );
3791
3792 END IF;
3793
3794 FND_MSG_PUB.Count_And_Get
3795 ( p_count => x_msg_count ,
3796 p_data => x_msg_data );
3797
3798 END Set_global_info;
3799
3800 FUNCTION GET_VALID_OU
3801 ( p_org_id hr_operating_units.organization_id%TYPE DEFAULT NULL , p_product_code VARCHAR2 )
3802 RETURN NUMBER
3803
3804 /*
3805 -- This function is used to determine and get valid operating unit where cc is enabled for this operating unit.
3806 -- Returns ORG_ID if valid and CC is enabled or retruns NULL if invalid or CC is not enabled.
3807
3808 -- This function uses MO_GLOBAL.validate_orgid_pub_api(...) to get valid org_id.
3809 -- MO_GLOBAL.validate_orgid_pub_api retruns p_org_id if Valid or returns NULL if invalid .
3810 -- If p_org_id does not exist in Global table, then it would throw up error.
3811 -- Before calling this function, global temp table should be populated using MO initialization routine. */
3812
3813 IS
3814 l_org_id NUMBER ;
3815 l_status VARCHAR2(1);
3816
3817 BEGIN
3818 l_org_id := p_org_id ;
3819
3820 -- VALIDATE_ORGID_PUB_API will retrun either
3821 -- Success ( 'S','O','C','D') or Failure ( 'F')
3822
3823 mo_global.validate_orgid_pub_api( l_org_id, 'Y',l_status );
3824 /* This function is used to determine and get valid operating unit where CC is enabled.
3825
3826 -- Checking if CC is enabled or not */
3827
3828 If l_org_id is not null and l_status IN ( 'S','O','C','D') then
3829 If p_product_code = 'CC' then
3830 If igi_gen.is_req_installed(p_product_code,l_org_id) = 'Y' then
3831 RETURN l_org_id ;
3832 else
3833 RETURN NULL ;
3834 end if ;
3835 End if ;
3836 else
3837 RETURN NULL;
3838 End if ;
3839
3840 END GET_VALID_OU;
3841
3842
3843 --Added by svaithil for GSCC warnings on 20/05/2004
3844 begin
3845 G_PKG_NAME := 'IGC_CC_OPN_UPD_GET_LNK_PUB';
3846 g_debug_msg := NULL;
3847 g_debug_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
3848 g_state_level := FND_LOG.LEVEL_STATEMENT;
3849 g_proc_level := FND_LOG.LEVEL_PROCEDURE;
3850 g_event_level := FND_LOG.LEVEL_EVENT;
3851 g_excep_level := FND_LOG.LEVEL_EXCEPTION;
3852 g_error_level := FND_LOG.LEVEL_ERROR;
3853 g_unexp_level := FND_LOG.LEVEL_UNEXPECTED;
3854 g_path := 'IGC.PLSQL.IGCOUGLB.IGC_CC_OPN_UPD_GET_LNK_PUB.';
3855 g_debug_mode := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
3856
3857
3858 END IGC_CC_OPN_UPD_GET_LNK_PUB;