[Home] [Help]
PACKAGE BODY: APPS.IGS_AS_SUARC_LGCY_PUB
Source
1 PACKAGE BODY IGS_AS_SUARC_LGCY_PUB AS
2 /* $Header: IGSPAS2B.pls 120.2 2005/10/20 23:56:17 appldev noship $ */
3 g_pkg_name CONSTANT VARCHAR2(30) := 'IGS_AS_SUARC_LGCY_PUB';
4 g_suarc_status VARCHAR2(10) := 'VALID';
5 g_wlst_pri_pref_exists BOOLEAN :=FALSE;
6 PROCEDURE validate_parameters(p_suarc_dtls_rec IN sua_refcd_rec_type ) AS
7 /*===========================================================================+
8 | PROCEDURE |
9 | validate_parameters |
10 | |
11 | DESCRIPTION |
12 | This is a public procedure and is responsible for the |
13 | creation of a student Assessment unit outcome record. |
14 | SCOPE - PUBLIC |
15 | |
16 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
17 | |
18 | ARGUMENTS : IN: |
19 | p_suarc_dtls_rec |
20 | IN/ OUT: |
21 | |
22 | RETURNS : NONE |
23 | |
24 | NOTES |
25 | |
26 | CREATION HISTORY : |
27 | bradhakr 03-jul-2005 |
28 | MODIFICATION HISTORY |
29 +===========================================================================*/
30 -- Declare local variables and initialise parameters.
31 l_msg_count NUMBER ;
32 l_msg_data VARCHAR2(2000);
33 l_grade_msg FND_NEW_MESSAGES.MESSAGE_NAME%TYPE;
34 BEGIN
35 -- Person Number is Mandatory
36 IF p_suarc_dtls_rec.person_number IS NULL THEN
37 FND_MESSAGE.SET_NAME('IGS','IGS_EN_PER_NUM_NULL');
38 FND_MSG_PUB.Add;
39 g_suarc_status := 'INVALID';
40 END IF;
41 -- Program Code is Mandatory
42 IF p_suarc_dtls_rec.program_cd IS NULL THEN
43 FND_MESSAGE.SET_NAME('IGS','IGS_EN_PRGM_CD_NULL');
44 FND_MSG_PUB.Add;
45 g_suarc_status := 'INVALID';
46 END IF;
47 -- Unit Code is Mandatory
48 IF p_suarc_dtls_rec.unit_cd IS NULL THEN
49 FND_MESSAGE.SET_NAME('IGS','IGS_EN_UNITCD_SPECIFIED');
50 FND_MSG_PUB.Add;
51 g_suarc_status := 'INVALID';
52 END IF;
53 -- Calendar Alternate Code is Mandatory
54 IF p_suarc_dtls_rec.teach_cal_alt_code IS NULL THEN
55 FND_MESSAGE.SET_NAME('IGS','IGS_EN_CAL_TYP_NULL');
56 FND_MSG_PUB.Add;
57 g_suarc_status := 'INVALID';
58 END IF;
59 -- Location Code is Mandatory
60 IF p_suarc_dtls_rec.location_cd IS NULL THEN
61 FND_MESSAGE.SET_NAME('IGS','IGS_EN_LOC_CD_NULL');
62 FND_MSG_PUB.Add;
63 g_suarc_status := 'INVALID';
64 END IF;
65 -- Unit Class is Mandatory
66 IF p_suarc_dtls_rec.unit_class IS NULL THEN
67 FND_MESSAGE.SET_NAME('IGS','IGS_EN_UNT_CLS_NULL');
68 FND_MSG_PUB.Add;
69 g_suarc_status := 'INVALID';
70 END IF;
71 -- Reference Code Type is Mandatory
72 IF p_suarc_dtls_rec.reference_cd_type IS NULL THEN
73 FND_MESSAGE.SET_NAME('IGS','IGS_AS_REFERENCE_CD_NULL');
74 FND_MSG_PUB.Add;
75 g_suarc_status := 'INVALID';
76 END IF;
77 -- Reference Code is Mandatory
78 IF p_suarc_dtls_rec.reference_cd IS NULL THEN
79 FND_MESSAGE.SET_NAME('IGS','IGS_AS_REFERENCE_CD_NULL');
80 FND_MSG_PUB.Add;
81 g_suarc_status := 'INVALID';
82 END IF;
83 -- Applied Program Code is Mandatory
84 IF p_suarc_dtls_rec.applied_program_cd IS NULL THEN
85 FND_MESSAGE.SET_NAME('IGS','IGS_AS_APPLIED_PROGRAM_CD_NULL');
86 FND_MSG_PUB.Add;
87 g_suarc_status := 'INVALID';
88 END IF;
89 -- If Program Code is specified , it should be in Upper Case.
90 IF p_suarc_dtls_rec.program_cd IS NOT NULL THEN
91 BEGIN
92 igs_en_su_attempt_pkg.check_constraints(
93 column_name => 'COURSE_CD' ,
94 column_value => p_suarc_dtls_rec.program_cd );
95 EXCEPTION
96 WHEN OTHERS THEN
97 FND_MSG_PUB.COUNT_AND_GET ( p_count => l_msg_count ,
98 p_data => l_msg_data);
99 FND_MSG_PUB.DELETE_MSG(l_msg_count);
100 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_EN_PRGM_CD_UCASE');
101 FND_MSG_PUB.ADD;
102 g_suarc_status := 'INVALID';
103 END;
104 END IF;
105 -- If Applied Program Code is specified , it should be in Upper Case.
106 IF p_suarc_dtls_rec.program_cd IS NOT NULL THEN
107 BEGIN
108 igs_en_su_attempt_pkg.check_constraints(
109 column_name => 'COURSE_CD' ,
110 column_value => p_suarc_dtls_rec.applied_program_cd );
111 EXCEPTION
112 WHEN OTHERS THEN
113 FND_MSG_PUB.COUNT_AND_GET ( p_count => l_msg_count ,
114 p_data => l_msg_data);
115 FND_MSG_PUB.DELETE_MSG(l_msg_count);
116 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_EN_PRGM_CD_UCASE');
117 FND_MSG_PUB.ADD;
118 g_suarc_status := 'INVALID';
119 END;
120 END IF;
121 -- If Unit Code is specified , it should be in Upper Case.
122 IF p_suarc_dtls_rec.unit_cd IS NOT NULL THEN
123 BEGIN
124 igs_en_su_attempt_pkg.check_constraints(
125 column_name => 'UNIT_CD' ,
126 column_value => p_suarc_dtls_rec.unit_cd );
127 EXCEPTION
128 WHEN OTHERS THEN
129 FND_MSG_PUB.COUNT_AND_GET ( p_count => l_msg_count ,
130 p_data => l_msg_data);
131 FND_MSG_PUB.DELETE_MSG(l_msg_count);
132 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_EN_UNT_CD_UCASE');
133 FND_MSG_PUB.ADD;
134 g_suarc_status := 'INVALID';
135 END;
136 END IF;
137 -- If Location Code is specified , it should be in Upper Case.
138 IF p_suarc_dtls_rec.location_cd IS NOT NULL THEN
139 BEGIN
140 igs_en_su_attempt_pkg.check_constraints(
141 column_name => 'LOCATION_CD' ,
142 column_value => p_suarc_dtls_rec.location_cd );
143 EXCEPTION
144 WHEN OTHERS THEN
145 FND_MSG_PUB.COUNT_AND_GET ( p_count => l_msg_count ,
146 p_data => l_msg_data);
147 FND_MSG_PUB.DELETE_MSG(l_msg_count);
148 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_EN_LOC_CD_UCASE');
149 FND_MSG_PUB.ADD;
150 g_suarc_status := 'INVALID';
151 END;
152 END IF;
153 -- If Unit class is specified , it should be in Upper Case.
154 IF p_suarc_dtls_rec.unit_class IS NOT NULL THEN
155 BEGIN
156 igs_en_su_attempt_pkg.check_constraints(
157 column_name => 'UNIT_CLASS' ,
158 column_value => p_suarc_dtls_rec.unit_class );
159 EXCEPTION
160 WHEN OTHERS THEN
161 FND_MSG_PUB.COUNT_AND_GET ( p_count => l_msg_count ,
162 p_data => l_msg_data);
163 FND_MSG_PUB.DELETE_MSG(l_msg_count);
164 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_EN_UNT_CLS_UCASE');
165 FND_MSG_PUB.ADD;
166 g_suarc_status := 'INVALID';
167 END;
168 END IF;
169 END validate_parameters;
170
171 PROCEDURE validate_db_cons( p_person_id IN NUMBER,
172 p_unit_version_number IN NUMBER,
173 p_uoo_id IN NUMBER ,
174 p_suarc_dtls_rec IN sua_refcd_rec_type
175 ) AS
176 /*===========================================================================+
177 | PROCEDURE |
178 | validate_db_cons |
179 | |
180 | DESCRIPTION |
181 | This is a public procedure and is responsible for the |
182 | creation of a student Assessment unit outcome record. |
183 | SCOPE - PUBLIC |
184 | |
185 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
186 | |
187 | ARGUMENTS : IN: |
188 | p_person_id |
189 | p_unit_version_number |
190 | p_uoo_id |
191 | p_suarc_dtls_rec |
192 | IN/ OUT: |
193 | |
194 | RETURNS : NONE |
195 | |
196 | NOTES |
197 | |
198 | CREATION HISTORY : |
199 | bradhakr 03-jul-2005 |
200 | MODIFICATION HISTORY |
201 +===========================================================================*/
202 l_indicator BOOLEAN := false;
203 l_number NUMBER;
204 CURSOR get_ref_code_id
205 IS
206 SELECT refcd.reference_code_id
207 FROM igs_ge_ref_cd refcd, igs_ge_ref_cd_type refcdt
208 WHERE refcd.reference_cd_type = refcdt.reference_cd_type
209 AND refcd.reference_cd = p_suarc_dtls_rec.reference_cd
210 AND refcd.reference_cd_type = p_suarc_dtls_rec.reference_cd_type;
211
212 BEGIN
213 -- derive ref_code_id
214 OPEN get_ref_code_id;
215 FETCH get_ref_code_id INTO l_number;
216 CLOSE get_ref_code_id;
217
218 -- Check for UK validation.
219 IF igs_as_sua_ref_cds_pkg.get_uk_for_validation(
220 X_PERSON_ID => p_person_id,
221 X_COURSE_CD => p_suarc_dtls_rec.program_cd,
222 X_UOO_ID => p_uoo_id,
223 X_REFERENCE_CODE_ID => l_number,
224 X_APPLIED_COURSE_CD => p_suarc_dtls_rec.applied_program_cd,
225 X_DELETED_DATE => TO_DATE(null)
226 ) THEN
227 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_AS_UK_CHK_REF_CD' );
228 FND_MSG_PUB.ADD;
229 g_suarc_status := 'INVALID';
230 END IF;
231
232 -- Foreign Key Validation - Check if Location Code exists.
233 IF NOT igs_ad_location_pkg.get_pk_for_validation ( x_location_cd => p_suarc_dtls_rec.location_cd) THEN
234 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_EN_LOC_CD_INV' );
235 FND_MSG_PUB.ADD;
236 g_suarc_status := 'INVALID';
237 END IF;
238
239 -- Foreign Key Validation - Check if Student Unit Attempt exists.
240 IF NOT igs_en_su_attempt_pkg.get_pk_for_validation (
241 x_person_id => p_person_id,
242 x_course_cd => p_suarc_dtls_rec.program_cd,
243 x_uoo_id => p_uoo_id) THEN
244 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_FI_PRSNID_PRGCD_NOT_MATCH');
245 FND_MSG_PUB.ADD;
246 g_suarc_status := 'INVALID';
247 END IF ;
248
249 -- Foreign Key Validation - Check if Student Program Attempt exists.
250 IF NOT igs_en_stdnt_ps_att_pkg.get_pk_for_validation (
251 x_person_id => p_person_id,
252 x_course_cd => p_suarc_dtls_rec.program_cd) THEN
253 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_FI_PRSNID_PRGCD_NOT_MATCH');
254 FND_MSG_PUB.ADD;
255 g_suarc_status := 'INVALID';
256 END IF ;
257
258 -- Foreign Key Validation - Check if Student (Applied )Program Attempt exists.
259 IF NOT igs_en_stdnt_ps_att_pkg.get_pk_for_validation (
260 x_person_id => p_person_id,
261 x_course_cd => p_suarc_dtls_rec.applied_program_cd)THEN
262 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_AS_NO_PROGRAM_ATTEMPT');
263 FND_MSG_PUB.ADD;
264 g_suarc_status := 'INVALID';
265 END IF ;
266
267 -- Foreign Key Validation - Check if Unit Code exists.
268 IF NOT igs_ps_unit_pkg.get_pk_for_validation ( x_unit_cd => p_suarc_dtls_rec.unit_cd)THEN
269 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_IN_INVALID_UNIT_CODE');
270 FND_MSG_PUB.ADD;
271 g_suarc_status := 'INVALID';
272 END IF;
273
274 -- Foreign Key Validation - Check if Unit Code / Version exists.
275 IF NOT igs_ps_unit_ver_pkg.get_pk_for_validation (
276 x_unit_cd => p_suarc_dtls_rec.unit_cd,
277 x_version_number => NVL ( p_suarc_dtls_rec.version_number,p_unit_version_number)) THEN
278 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_PS_UNITCODE_UNITVER_NE');
279 FND_MSG_PUB.ADD;
280 g_suarc_status := 'INVALID';
281 END IF;
282 END validate_db_cons;
283
284 PROCEDURE create_suarc ( p_api_version IN NUMBER,
285 p_init_msg_list IN VARCHAR2 ,
286 p_commit IN VARCHAR2 ,
287 p_validation_level IN NUMBER ,
288 p_suarc_dtls_rec IN sua_refcd_rec_type ,
289 x_return_status OUT NOCOPY VARCHAR2,
290 x_msg_count OUT NOCOPY NUMBER,
291 x_msg_data OUT NOCOPY VARCHAR2) AS
292 l_api_name CONSTANT VARCHAR2(30) := 'create_suarc';
293 l_api_version CONSTANT NUMBER := 1.0;
294 l_insert_flag BOOLEAN := TRUE;
295 l_suar_id igs_as_sua_ref_cds.suar_id%TYPE;
296 l_person_id igs_pe_person.person_id%TYPE;
297 l_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE;
298 l_cal_type igs_en_su_attempt.cal_type%TYPE;
299 l_ci_sequence_number igs_en_su_attempt.ci_Sequence_number%TYPE;
300 l_ci_start_dt igs_ca_inst.start_dt%TYPE;
301 l_ci_end_dt igs_ca_inst.end_dt%TYPE;
302 l_version_number igs_en_su_attempt.version_number%TYPE;
303 l_reference_code_id igs_as_sua_ref_cds.reference_code_id%TYPE;
304 l_reference_cd_type igs_as_sua_ref_cds.reference_cd_type%TYPE;
305 l_reference_cd igs_as_sua_ref_cds.reference_cd%TYPE;
306 l_applied_program_cd igs_as_sua_ref_cds.applied_course_cd%TYPE;
307 l_return_status VARCHAR2(30);
308 l_msg_count NUMBER ;
309 l_msg_data VARCHAR2(2000);
310 l_org_unit_cd igs_or_unit.org_unit_cd%TYPE;
311 l_ref_cd_id NUMBER;
312 L_EXISTS VARCHAR2(30);
313 l_sua_status igs_en_su_attempt_all.unit_attempt_status%TYPE;
314
315 CURSOR cur_sua_status
316 IS
317 SELECT unit_attempt_status
318 FROM igs_en_su_attempt_all
319 where person_id = l_person_id
320 AND course_cd =p_suarc_dtls_rec.program_cd
321 AND uoo_id = l_uoo_id ;
322
323 CURSOR cur_check_ref_cd
324 IS
325 SELECT 'X'
326 FROM igs_ge_ref_cd refcd
327 WHERE refcd.reference_cd = p_suarc_dtls_rec.reference_cd
328 AND refcd.reference_cd_type = p_suarc_dtls_rec.reference_cd_type;
329
330 CURSOR cur_check_ref_cd_section_abv
331 IS
332 SELECT 'X'
333 FROM igs_ge_ref_cd refcd, igs_ge_ref_cd_type refcdt
334 WHERE refcd.reference_cd_type = refcdt.reference_cd_type
335 AND refcd.reference_cd = p_suarc_dtls_rec.reference_cd
336 AND refcd.reference_cd_type = p_suarc_dtls_rec.reference_cd_type
337 AND refcdt.UNIT_FLAG='Y'
338 AND refcdt.UNIT_SECTION_FLAG='Y'
339 AND refcdt.UNIT_SECTION_OCCURRENCE_FLAG='Y';
340
341 CURSOR get_ref_code_id
342 IS
343 SELECT refcd.reference_code_id
344 FROM igs_ge_ref_cd refcd
345 WHERE refcd.reference_cd = p_suarc_dtls_rec.reference_cd
346 AND refcd.reference_cd_type = p_suarc_dtls_rec.reference_cd_type;
347
348 BEGIN
349 -- Create a savepoint
350 SAVEPOINT CREATE_SUARC_PUB;
351 -- Check for the Compatible API call
352 IF NOT FND_API.COMPATIBLE_API_CALL( l_api_version,
353 p_api_version,
354 l_api_name,
355 g_pkg_name) THEN
356 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
357 END IF;
358 -- If the calling program has passed the parameter for initializing the message list
359 IF FND_API.TO_BOOLEAN(p_init_msg_list) THEN
360 FND_MSG_PUB.INITIALIZE;
361 END IF;
362 -- Set the return status to success
363 x_return_status := FND_API.G_RET_STS_SUCCESS;
364 g_suarc_status := 'VALID';
365 --
366 -- 1.Validate Parameters
367 validate_parameters(p_suarc_dtls_rec => p_suarc_dtls_rec);
368 -- 2.Derive all required parameters
369 IF g_suarc_status <> 'INVALID' THEN
370 -- Derive Person ID
371 l_person_id := igs_ge_gen_003.get_person_id ( p_person_number => p_suarc_dtls_rec.person_number) ;
372 IF l_person_id IS NULL THEN
373 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_GE_INVALID_PERSON_NUMBER');
374 FND_MSG_PUB.ADD;
375 g_suarc_status := 'INVALID';
376 END IF;
377 -- Derive Calendar Details.
378 igs_ge_gen_003.get_calendar_instance(
379 p_alternate_cd => p_suarc_dtls_rec.teach_cal_alt_code ,
380 p_s_cal_category => '''TEACHING''',
381 p_cal_type => l_cal_type ,
382 p_ci_sequence_number => l_ci_sequence_number ,
383 p_start_dt => l_ci_start_dt ,
384 p_end_dt => l_ci_end_dt ,
385 p_return_status => l_return_status );
386 IF l_return_status = 'INVALID' THEN
387 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_EN_ALT_CD_NO_CAL_FND');
388 FND_MSG_PUB.ADD;
389 g_suarc_status := 'INVALID';
390 ELSIF l_return_status = 'MULTIPLE' THEN
391 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_EN_MULTI_TCH_CAL_FND');
392 FND_MSG_PUB.ADD;
393 g_suarc_status := 'INVALID';
394 END IF ;
395 -- Derive Unit version if parameter is NULL.
396 IF p_suarc_dtls_rec.version_number IS NULL THEN
397 IF NOT igs_en_gen_legacy.get_unit_ver (
398 p_cal_type => l_cal_type ,
399 p_ci_sequence_number => l_ci_sequence_number ,
400 p_unit_cd => p_suarc_dtls_rec.unit_cd ,
401 p_location_cd => p_suarc_dtls_rec.location_cd ,
402 P_unit_class => p_suarc_dtls_rec.unit_class ,
403 p_version_number => l_version_number ) THEN
404 g_suarc_status := 'INVALID';
405 END IF;
406 END IF;
407 -- Derive uoo_id
408 IF NOT igs_en_gen_legacy.get_uoo_id (
409 p_cal_type => l_cal_type ,
410 p_ci_sequence_number => l_ci_sequence_number ,
411 p_unit_cd => p_suarc_dtls_rec.unit_cd ,
412 p_location_cd => p_suarc_dtls_rec.location_cd ,
413 P_unit_class => p_suarc_dtls_rec.unit_class ,
414 p_version_number => NVL ( p_suarc_dtls_rec.version_number,l_version_number) ,
415 p_uoo_id => l_uoo_id ,
416 p_owner_org_unit_cd => l_org_unit_cd ) THEN
417 g_suarc_status := 'INVALID';
418 END IF;
419
420 -- swaghmar 4327987 - To check for the status of the SUA
421 OPEN cur_sua_status;
422 FETCH cur_sua_status INTO l_sua_status;
423 IF l_sua_status IN ('WAITLISTED','UNCONFIRM') THEN
424 FND_MESSAGE.SET_NAME('IGS', 'IGS_AS_SUA_STS_NT_ALLOWED');
425 FND_MSG_PUB.ADD;
426 g_suarc_status := 'INVALID';
427 END IF;
428 CLOSE cur_sua_status;
429
430 -- derive reference code id
431 OPEN get_ref_code_id;
432 FETCH get_ref_code_id INTO l_ref_cd_id;
433 CLOSE get_ref_code_id;
434 --- check if reference code exists
435 OPEN cur_check_ref_cd;
436 FETCH cur_check_ref_cd INTO l_exists;
437 IF cur_check_ref_cd%NOTFOUND THEN
438 FND_MESSAGE.SET_NAME('IGS', 'IGS_AS_REFERENCE_CD_NT_EXIST');
439 FND_MSG_PUB.ADD;
440 g_suarc_status := 'INVALID';
441 END IF;
442 CLOSE cur_check_ref_cd;
443 -- check if reference code can be used for the unit set
444 OPEN cur_check_ref_cd_section_abv;
445 FETCH cur_check_ref_cd_section_abv INTO l_exists;
446 IF cur_check_ref_cd_section_abv%NOTFOUND THEN
447 FND_MESSAGE.SET_NAME('IGS', 'IGS_AS_REFERENCE_CODE_NOT_SET');
448 FND_MSG_PUB.ADD;
449 g_suarc_status := 'INVALID';
450 END IF;
451 CLOSE cur_check_ref_cd_section_abv;
452
453 -- 3. The DB constraints are validated by making a call to validate_db_cons.
454 validate_db_cons ( p_person_id => l_person_id ,
455 p_unit_version_number => l_version_number ,
456 p_uoo_id => l_uoo_id ,
457 p_suarc_dtls_rec => p_suarc_dtls_rec ) ;
458
459 END IF ;
460 --swaghmar Bug #4327987
461 SELECT IGS_AS_SUA_REF_CDS_S.nextval INTO l_suar_id from dual;
462
463 IF g_suarc_status = 'VALID' THEN
464 INSERT INTO IGS_AS_SUA_REF_CDS (
465 SUAR_ID,
466 person_id,
467 course_cd,
468 uoo_id,
469 REFERENCE_CODE_ID,
470 REFERENCE_CD_TYPE,
471 REFERENCE_CD,
472 APPLIED_COURSE_CD,
473 CREATED_BY,
474 CREATION_DATE,
475 last_update_date,
476 last_updated_by,
477 last_update_login
478 )
479 VALUES (
480 l_suar_id,
481 l_person_id,
482 p_suarc_dtls_rec.program_cd,
483 l_uoo_id,
484 l_ref_cd_id,
485 p_suarc_dtls_rec.reference_cd_type,
486 p_suarc_dtls_rec.reference_cd,
487 p_suarc_dtls_rec.applied_program_cd,
488 nvl(fnd_global.user_id,-1),
489 SYSDATE,
490 SYSDATE,
491 NVL(fnd_global.user_id,-1),
492 NVL(fnd_global.login_id,-1));
493 END IF;
494 --
495 -- If the calling program has passed the parameter for committing the data and there
496 -- have been no errors in calling the balances process, then commit the work
497 IF ( (FND_API.To_Boolean(p_commit)) AND (g_suarc_status = 'VALID') ) THEN
498 COMMIT WORK;
499 END IF;
500 FND_MSG_PUB.COUNT_AND_GET( p_count => x_msg_count,
501 p_data => x_msg_data);
502 --
503 -- Retutn Status to the calling program
504 IF g_suarc_status = 'INVALID' THEN
505 ROLLBACK TO CREATE_SUARC_PUB;
506 x_return_status := FND_API.G_RET_STS_ERROR;
507 ELSIF g_suarc_status = 'WARNING' THEN
508 ROLLBACK TO CREATE_SUARC_PUB;
509 x_return_status := 'W';
510 END IF ;
511 EXCEPTION
512 WHEN FND_API.G_EXC_ERROR THEN
513 ROLLBACK TO CREATE_SUARC_PUB;
514 x_return_status := FND_API.G_RET_STS_ERROR;
515 FND_MSG_PUB.COUNT_AND_GET( p_count => x_msg_count,
516 p_data => x_msg_data);
517 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
518 ROLLBACK TO CREATE_SUARC_PUB;
519 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
520 FND_MSG_PUB.COUNT_AND_GET( p_count => x_msg_count,
521 p_data => x_msg_data);
522 WHEN OTHERS THEN
523 ROLLBACK TO CREATE_SUARC_PUB;
524 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
525 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
526 FND_MSG_PUB.Add_Exc_Msg(g_pkg_name,
527 l_api_name);
528 END IF;
529 FND_MSG_PUB.COUNT_AND_GET( p_count => x_msg_count,
530 p_data => x_msg_data);
531 END create_suarc ;
532 END igs_as_suarc_lgcy_pub ;