1 PACKAGE BODY igf_sp_assign_pub AS
2 /* $Header: IGFSP05B.pls 120.1 2006/05/15 06:27:00 svuppala noship $ */
3
4 /****************************************************************************
5 Created By: Vinay Chappidi
6 Date Created By: 19-Feb-2003
7 Purpose: Public API for creating Sponsor-Student relation ship
8 Known limitations,enhancements,remarks:
9
10 Change History
11 Who When What
12 svuppala 12-May-2006 Bug 5217319 Added call to format amount by rounding off to currency precision
13 in the igf_sp_stdnt_rel_pkg.insert_row call in create_stdnt_spnsr_rel procedure.
14 vvutukur 20-Jul-2003 Enh#3038511.FICR106 Build. Modified procedure create_stdnt_spnsr_rel.
15 pathipat 24-Apr-2003 Enh 2831569 - Commercial Receivables build
16 Modified create_stdnt_spnsr_rel() - added call to chk_manage_account()
17 ******************************************************************************/
18
19
20 g_pkg_name CONSTANT VARCHAR2(30) := 'Igf_Sp_Assign_Pub';
21 g_c_temp VARCHAR2(1);
22
23 FUNCTION validate_prsn(p_n_person_id IN hz_parties.party_ID%TYPE) RETURN BOOLEAN
24 AS
25 /****************************************************************************
26 Created By: Vinay Chappidi
27 Date Created By: 19-Feb-2003
28 Purpose: Local Function to validate if the person exists in the system
29 Known limitations,enhancements,remarks:
30
31 Change History
32
33 Who When What
34 ******************************************************************************/
35 CURSOR c_check_valid_person (cp_n_person_id igs_pe_person_base_v.person_id%TYPE)
36 IS
37 SELECT 'x'
38 FROM igs_pe_person_base_v
39 WHERE person_id = cp_n_person_id;
40 BEGIN
41 OPEN c_check_valid_person(p_n_person_id);
42 FETCH c_check_valid_person INTO g_c_temp;
43 IF c_check_valid_person%FOUND THEN
44 CLOSE c_check_valid_person;
45 RETURN TRUE;
46 ELSE
47 CLOSE c_check_valid_person;
48 RETURN FALSE;
49 END IF;
50 END validate_prsn;
51
52 PROCEDURE validate_api_prsn_id(p_c_sys_prsn_id_typ IN igs_pe_person_id_typ.s_person_id_type%TYPE,
53 p_c_usr_alt_prsn_id_typ IN igs_pe_person_id_typ.person_id_type%TYPE,
54 p_c_api_prsn_id IN igs_pe_alt_pers_id.api_person_id%TYPE,
55 p_b_ret_status OUT NOCOPY BOOLEAN,
56 p_n_person_id OUT NOCOPY hz_parties.party_id%TYPE)
57 AS
58 /****************************************************************************
59 Created By: Vinay Chappidi
60 Date Created By: 19-Feb-2003
61 Purpose: Local procedure for identifying a unique person depending on the
62 the alternate person details provided to this API
63 Known limitations,enhancements,remarks:
64
65 Change History
66 Who When What
67 ******************************************************************************/
68
69 CURSOR cur_get_person_id(cp_c_sys_prsn_id_typ igs_pe_person_id_typ.s_person_id_type%TYPE,
70 cp_c_usr_alt_prs_id_type igs_pe_person_id_typ.person_id_type%TYPE,
71 cp_c_api_person_id igs_pe_alt_pers_id.api_person_id%TYPE)
72 IS
73 SELECT api.pe_person_id
74 FROM igs_pe_person_id_typ pit,
75 igs_pe_alt_pers_id api
76 WHERE api.person_id_type = pit.person_id_type AND
77 api.person_id_type = cp_c_usr_alt_prs_id_type AND
78 api.api_person_id = cp_c_api_person_id AND
79 pit.s_person_id_type = cp_c_sys_prsn_id_typ AND
80 SYSDATE BETWEEN api.start_dt AND NVL(api.end_dt, SYSDATE);
81
82 l_n_person_id hz_parties.party_id%TYPE;
83 BEGIN
84 -- check if for the combination of Alternate-Person Id Type, API Person ID if there exists a unique person
85 -- when no person is identified or when there are more than one person identified from above cursor then
86 -- set the return status OUT variable to FALSE and return.
87 OPEN cur_get_person_id (p_c_sys_prsn_id_typ,p_c_usr_alt_prsn_id_typ,p_c_api_prsn_id);
88 FETCH cur_get_person_id INTO l_n_person_id;
89 IF cur_get_person_id%NOTFOUND THEN
90 p_b_ret_status := FALSE;
91 p_n_person_id := NULL;
92 ELSE
93 -- Check if there are any duplicate persons with the same value
94 -- if the next fetch is successful then the Person Details are invalid.
95 FETCH cur_get_person_id INTO l_n_person_id;
96 IF cur_get_person_id%NOTFOUND THEN
97 p_b_ret_status := TRUE;
98 p_n_person_id := l_n_person_id;
99 ELSE
100 p_b_ret_status := FALSE;
101 p_n_person_id := NULL;
102 END IF;
103 END IF;
104 CLOSE cur_get_person_id;
105 END validate_api_prsn_id;
106
107
108 FUNCTION validate_spnsr_cd(p_c_sponsor_code IN igf_aw_fund_cat.fund_code%TYPE) RETURN BOOLEAN
109 AS
110 /****************************************************************************
111 Created By: Vinay Chappidi
112 Date Created By: 19-Feb-2003
113 Purpose: Local Function for validating if the Sponsor Code is active in the system
114 Known limitations,enhancements,remarks:
115
116 Change History
117 Who When What
118 ******************************************************************************/
119 CURSOR c_check_valid_spnsr_cd(cp_c_fund_code igf_aw_fund_cat.fund_code%TYPE,
120 cp_c_sys_fund_type igf_aw_fund_cat.sys_fund_type%TYPE,
121 cp_c_status igf_aw_fund_cat.active%TYPE)
122 IS
123 SELECT 'x'
124 FROM igf_aw_fund_cat c,
125 igf_aw_fund_mast m
126 WHERE c.fund_code = m.fund_code AND
127 c.fund_code = cp_c_fund_code AND
128 c.sys_fund_type = cp_c_sys_fund_type AND
129 c.active = cp_c_status;
130 BEGIN
131 -- validate the sponsor code parameter is a valid
132 OPEN c_check_valid_spnsr_cd(p_c_sponsor_code,'SPONSOR','Y');
133 FETCH c_check_valid_spnsr_cd INTO g_c_temp;
134 IF c_check_valid_spnsr_cd%NOTFOUND THEN
135 CLOSE c_check_valid_spnsr_cd;
136 RETURN FALSE;
137 ELSE
138 CLOSE c_check_valid_spnsr_cd;
139 RETURN TRUE;
140 END IF;
141 END validate_spnsr_cd;
142
143
144 FUNCTION validate_award_cal_inst(p_c_awd_ci_cal_type igs_ca_inst.cal_type%TYPE,
145 p_n_awd_ci_sequence_number igs_ca_inst.sequence_number%TYPE) RETURN BOOLEAN
146 AS
147 /****************************************************************************
148 Created By: Vinay Chappidi
149 Date Created By: 19-Feb-2003
150 Purpose: Local function for validating the Award Calendar Instance parameters
151 Known limitations,enhancements,remarks:
152
153 Change History
154 Who When What
155 ******************************************************************************/
156
157 CURSOR cur_check_ci_status(cp_c_cal_type igs_ca_inst.cal_type%TYPE,
158 cp_n_sequence_number igs_ca_inst.sequence_number%TYPE,
159 cp_c_cal_status igs_ca_stat.cal_status%TYPE)
160 IS
161 SELECT 'x'
162 FROM igs_ca_inst i,
163 igs_ca_stat s,
164 igf_ap_batch_aw_map b
165 WHERE i.cal_status = s.cal_status AND
166 i.cal_type = b.ci_cal_type AND
167 i.sequence_number = b.ci_sequence_number AND
168 i.cal_type = cp_c_cal_type AND
169 i.sequence_number = cp_n_sequence_number AND
170 s.s_cal_status = cp_c_cal_status;
171 BEGIN
172 OPEN cur_check_ci_status(p_c_awd_ci_cal_type,p_n_awd_ci_sequence_number,'ACTIVE');
173 FETCH cur_check_ci_status INTO g_c_temp;
174 IF cur_check_ci_status%NOTFOUND THEN
175 CLOSE cur_check_ci_status;
176 RETURN FALSE;
177 ELSE
178 CLOSE cur_check_ci_status;
179 RETURN TRUE;
180 END IF;
181 END validate_award_cal_inst;
182
183 FUNCTION validate_load_cal_inst(p_c_ld_ci_cal_type igs_ca_inst.cal_type%TYPE,
184 p_n_ld_ci_sequence_number igs_ca_inst.sequence_number%TYPE) RETURN BOOLEAN
185 AS
186 /****************************************************************************
187 Created By: Vinay Chappidi
188 Date Created By: 19-Feb-2003
189 Purpose: Local function for validating the Term Calendar Instance parameters
190 Known limitations,enhancements,remarks:
191
192 Change History
193 Who When What
194 ******************************************************************************/
195 CURSOR cur_check_ci_status (cp_c_cal_type igs_ca_inst.cal_type%TYPE,
196 cp_n_sequence_number igs_ca_inst.sequence_number%TYPE,
197 cp_c_cal_status igs_ca_stat.cal_status%TYPE,
198 cp_c_awd_cal_cat igs_ca_type.s_cal_cat%TYPE,
199 cp_c_ld_cal_cat igs_ca_type.s_cal_cat%TYPE)
200 IS
201 SELECT 'x'
202 FROM igs_ca_inst aw,
203 igs_ca_inst ld,
204 igs_ca_type ld_t,
205 igs_ca_type aw_t,
206 igs_ca_inst_rel rel,
207 igs_ca_stat status
208 WHERE rel.sup_cal_type=aw.cal_type AND
209 rel.sup_ci_sequence_number=aw.sequence_number AND
210 rel.sub_cal_type=ld.cal_type AND
211 rel.sub_ci_sequence_number=ld.sequence_number AND
212 aw.cal_type = aw_t.cal_type AND
213 aw_t.s_cal_cat= cp_c_awd_cal_cat AND
214 ld.cal_type = ld_t.cal_type AND
215 ld_t.s_cal_cat= cp_c_ld_cal_cat AND
216 ld.cal_status = status.cal_status AND
217 status.s_cal_status= cp_c_cal_status AND
218 ld.cal_type = cp_c_cal_type AND
219 ld.sequence_number = cp_n_sequence_number;
220
221 BEGIN
222 OPEN cur_check_ci_status(p_c_ld_ci_cal_type,p_n_ld_ci_sequence_number,'ACTIVE','AWARD','LOAD');
223 FETCH cur_check_ci_status INTO g_c_temp;
224 IF cur_check_ci_status%NOTFOUND THEN
225 CLOSE cur_check_ci_status;
226 RETURN FALSE;
227 ELSE
228 CLOSE cur_check_ci_status;
229 RETURN TRUE;
230 END IF;
231 END validate_load_cal_inst;
232
233 PROCEDURE check_spnsr_awd_rel(p_c_fund_code IN igf_aw_fund_mast.fund_code%TYPE,
234 p_c_aw_cal_type IN igs_ca_inst.cal_type%TYPE,
235 p_n_aw_sequence_number IN igs_ca_inst.sequence_number%TYPE,
236 p_c_ld_cal_type IN igs_ca_inst.cal_type%TYPE,
237 p_n_ld_sequence_number IN igs_ca_inst.sequence_number%TYPE,
238 p_b_return_status OUT NOCOPY BOOLEAN,
239 p_n_fund_id OUT NOCOPY igf_aw_fund_mast.fund_id%TYPE)
240 AS
241 /****************************************************************************
242 Created By: Vinay Chappidi
243 Date Created By: 19-Feb-2003
244 Purpose: Local procedure for checking if the Sponsor, Award Calendar and Load Calendar
245 combination exists in the system.
246 Known limitations,enhancements,remarks:
247
248 Change History
249 Who When What
250 ******************************************************************************/
251 CURSOR c_check_spn_awd(cp_c_fund_code igf_aw_fund_mast.fund_code%TYPE,
252 cp_c_fund_status igf_aw_fund_mast.discontinue_fund%TYPE,
253 cp_c_aw_cal_type igf_aw_fund_mast.ci_cal_type%TYPE,
254 cp_n_aw_sequence_number igf_aw_fund_mast.ci_sequence_number%TYPE,
255 cp_c_ld_cal_type igf_aw_fund_tp.tp_cal_type%TYPE,
256 cp_n_ld_sequence_number igf_aw_fund_tp.tp_sequence_number%TYPE)
257 IS
258 SELECT m.fund_id
259 FROM igf_aw_fund_mast m,
260 igf_aw_fund_tp t
261 WHERE m.fund_id = t.fund_id AND
262 m.fund_code = cp_c_fund_code AND
263 m.ci_cal_type = cp_c_aw_cal_type AND
264 m.ci_sequence_number = cp_n_aw_sequence_number AND
265 m.discontinue_fund = cp_c_fund_status AND
266 t.tp_cal_type = cp_c_ld_cal_type AND
267 t.tp_sequence_number = cp_n_ld_sequence_number;
268
269 l_n_fund_id igf_aw_fund_mast.fund_id%TYPE;
270 BEGIN
271 -- Check if the Award Calendar Insatnce, Load Calendar Instance and the Sponsor Details are setup in the system
272 -- Return False when there is no relation and TRUE when there exists a relation
273 OPEN c_check_spn_awd(p_c_fund_code,'N',p_c_aw_cal_type,p_n_aw_sequence_number,p_c_ld_cal_type,p_n_ld_sequence_number);
274 FETCH c_check_spn_awd INTO l_n_fund_id;
275 IF c_check_spn_awd%NOTFOUND THEN
276 p_b_return_status := FALSE;
277 p_n_fund_id := NULL;
278 ELSE
279 p_b_return_status := TRUE;
280 p_n_fund_id := l_n_fund_id;
281 END IF;
282 CLOSE c_check_spn_awd;
283 END check_spnsr_awd_rel;
284
285 PROCEDURE check_create_fa_rec(p_n_person_id hz_parties.party_id%TYPE,
286 p_c_awd_ci_cal_type igs_ca_inst.cal_type%TYPE,
287 p_n_awd_ci_sequence_number igs_ca_inst.sequence_number%TYPE,
288 p_b_ret_status OUT NOCOPY BOOLEAN,
289 p_n_base_id OUT NOCOPY NUMBER)
290 AS
291 /****************************************************************************
292 Created By: Vinay Chappidi
293 Date Created By: 19-Feb-2003
294 Purpose: Local procedure for checking if there exists FA Base record for the
295 Award Calendar Instance, Person combination. If a there is no record
296 then a new FA base record is created by invoking existing function
297 Known limitations,enhancements,remarks:
298
299 Change History
300 Who When What
301 ******************************************************************************/
302 l_b_ret_status BOOLEAN := FALSE;
303 l_n_base_id igf_ap_fa_base_rec_all.base_id%TYPE;
304 l_c_message_text fnd_new_messages.message_text%TYPE;
305
306 BEGIN
307 -- Check if FA base record is already existing, if yes then assign the Base ID to the OUT variable and return
308 -- from this procedure. when a FA base record is not found then create using the existing function
309 -- If the function returns FALSE without value for base_id then assign NULL to the base_id OUT variable,
310 -- if the function returns TRUE then assign this base id to the OUT variable and return
311
312
313 -- OUT variable message text will be passed only when the person is already having a FA Base Record
314 -- since we are upfront checking for FA base record existance, messges text OUT variable will be NULL
315 -- and hence no need to add to the message list.
316 l_b_ret_status := igf_sp_create_base_rec.create_fa_base_record(p_cal_type => p_c_awd_ci_cal_type,
317 p_sequence_number => p_n_awd_ci_sequence_number,
318 p_person_id => p_n_person_id,
319 p_base_id => l_n_base_id,
320 p_message => l_c_message_text);
321 IF (l_b_ret_status OR (l_b_ret_status=FALSE AND l_n_base_id IS NOT NULL))THEN
322 p_n_base_id := l_n_base_id;
323 p_b_ret_status := TRUE;
324 ELSE
325 p_n_base_id := NULL;
326 p_b_ret_status := FALSE;
327 END IF;
328 END check_create_fa_rec;
329
330 FUNCTION check_spnsr_stdnt_rel(p_c_fund_id igf_aw_fund_mast.fund_id%TYPE,
331 p_n_base_id igf_ap_fa_base_rec.base_id%TYPE,
332 p_c_ld_cal_type igs_ca_inst.cal_type%TYPE,
333 p_n_ld_ci_sequence_number igs_ca_inst.sequence_number%TYPE) RETURN BOOLEAN
334 AS
335 /****************************************************************************
336 Created By: Vinay Chappidi
337 Date Created By: 19-Feb-2003
338 Purpose: Local function for checking if there already exists a Sponsor-Student relationship.
339 Known limitations,enhancements,remarks:
340
341 Change History
342 Who When What
343 ******************************************************************************/
344 CURSOR c_check_stdnt_spnsr_rel(cp_n_fund_id igf_sp_stdnt_rel.fund_id%TYPE,
345 cp_n_base_id igf_sp_stdnt_rel.base_id%TYPE,
346 cp_c_ld_cal_type igf_sp_stdnt_rel.ld_cal_type%TYPE,
347 cp_n_ld_seq_number igf_sp_stdnt_rel.ld_sequence_number%TYPE)
348 IS
349 SELECT 'x'
350 FROM igf_sp_stdnt_rel
351 WHERE fund_id = cp_n_fund_id AND
352 base_id = cp_n_base_id AND
353 ld_cal_type = cp_c_ld_cal_type AND
354 ld_sequence_number = cp_n_ld_seq_number;
355 BEGIN
356 -- check if the Sponsor-Student relation is already exists.
357 -- if the relation is already existing then return TRUE else return FALSE
358 OPEN c_check_stdnt_spnsr_rel(p_c_fund_id,p_n_base_id,p_c_ld_cal_type,p_n_ld_ci_sequence_number);
359 FETCH c_check_stdnt_spnsr_rel INTO g_c_temp;
360 IF c_check_stdnt_spnsr_rel%NOTFOUND THEN
361 CLOSE c_check_stdnt_spnsr_rel;
362 RETURN FALSE;
363 ELSE
364 CLOSE c_check_stdnt_spnsr_rel;
365 RETURN TRUE;
366 END IF;
367 END check_spnsr_stdnt_rel;
368
369 PROCEDURE create_stdnt_spnsr_rel(p_api_version IN NUMBER,
370 p_init_msg_list IN VARCHAR2,
371 p_commit IN VARCHAR2,
372 x_return_status OUT NOCOPY VARCHAR2,
373 x_msg_count OUT NOCOPY NUMBER,
374 x_msg_data OUT NOCOPY VARCHAR2,
375 p_person_id IN NUMBER,
376 p_alt_person_id_type IN VARCHAR2,
377 p_api_person_id IN VARCHAR2,
378 p_sponsor_code IN VARCHAR2,
379 p_awd_ci_cal_type IN VARCHAR2,
380 p_awd_ci_sequence_number IN NUMBER,
381 p_ld_cal_type IN VARCHAR2,
382 p_ld_ci_sequence_number IN NUMBER,
383 p_amount IN NUMBER)
384 AS
385 /****************************************************************************
386 Created By: Vinay Chappidi
387 Date Created By: 19-Feb-2003
388 Purpose: This procedure is the main api for creating a Sponsor-Student relationship.
389 For validating all parameters, local functions/ procedures are invoked.
390 Known limitations,enhancements,remarks:
391
392 Change History
393
394 Who When What
395 svuppala 12-May-2006 Bug 5217319 Added call to format amount by rounding off to currency precision
396 in the igf_sp_stdnt_rel_pkg.insert_row call
397 vvutukur 20-Jul-2003 Enh#3038511.FICR106 Build. Added call to generic procedure
398 igs_fi_crdapi_util.get_award_year_status to validate Award Year Status.
399 pathipat 24-Apr-2003 Enh 2831569 - Commercial Receivables build
400 Added check for manage_accounts - call to chk_manage_account()
401 ******************************************************************************/
402 l_b_error BOOLEAN :=FALSE;
403 -- variable for capturing the return status from a procedure
404 l_b_ret_status BOOLEAN := FALSE;
405 l_rowid VARCHAR2(25);
406
407 l_api_name CONSTANT VARCHAR2(30) := 'Create_Stdnt_Spnsr_Rel';
408 l_api_version CONSTANT NUMBER := 1.0;
409
410 l_n_person_id hz_parties.party_id%TYPE;
411 l_n_fund_id igf_aw_fund_mast.fund_id%TYPE;
412 l_c_sys_alt_prs_id_typ igs_pe_person_id_typ.s_person_id_type%TYPE;
413 l_n_base_id igf_ap_fa_base_rec_all.base_id%TYPE;
414 l_n_spr_stdnt_id igf_sp_stdnt_rel.spnsr_stdnt_id%TYPE;
415
416 l_c_manage_acc igs_fi_control_all.manage_accounts%TYPE := NULL;
417 l_c_message_name fnd_new_messages.message_name%TYPE := NULL;
418
419 l_v_awd_yr_status_cd igf_ap_batch_aw_map.award_year_status_code%TYPE;
420
421 BEGIN
422
423 -- Create a savepoint
424 SAVEPOINT create_stdnt_spnsr_rel_pub;
425
426 -- Check for the Compatible API call if the versions of the API and the version passed are
427 -- different then raise the unexpected error message
428 IF NOT fnd_api.compatible_api_call(l_api_version,
429 p_api_version,
430 l_api_name,
431 g_pkg_name) THEN
432 RAISE fnd_api.g_exc_unexpected_error;
433 END IF;
434
435 -- If the calling program has passed the parameter for initializing the message list
436 -- then call the Initialize program of the FND_MSG_PUB package
437 IF fnd_api.to_boolean(p_init_msg_list) THEN
438 fnd_msg_pub.initialize;
439 END IF;
440
441 -- Set the return status to success
442 x_return_status := fnd_api.g_ret_sts_success;
443
444 -- Obtain the value of manage_accounts in the System Options form
445 -- If it is null, then raise error. For normal processing, the value should
446 -- be 'OTHER' or 'STUDENT_FINANCE'
447 igs_fi_com_rec_interface.chk_manage_account( p_v_manage_acc => l_c_manage_acc,
448 p_v_message_name => l_c_message_name
449 );
450 IF (l_c_manage_acc IS NULL) THEN
451 l_b_error := TRUE;
452 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
453 fnd_message.set_name('IGS',l_c_message_name);
454 fnd_msg_pub.add;
455 RAISE fnd_api.g_exc_error;
456 END IF;
457 END IF;
458
459
460 IF (p_person_id IS NOT NULL AND p_api_person_id IS NOT NULL) THEN
461 l_b_error := TRUE;
462 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
463 fnd_message.set_name('IGS','IGS_FI_PRSID_ALTID_INVALID');
464 fnd_message.set_token('PERSON_ID',p_person_id);
465 fnd_message.set_token('API_PERS_ID',p_api_person_id);
466 fnd_msg_pub.add;
467 END IF;
468 END IF;
469
470 -- check if the user has provided either person_id or alternate person details
471 IF (p_person_id IS NULL AND p_api_person_id IS NULL) THEN
472 l_b_error := TRUE;
473 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
474 fnd_message.set_name('IGS','IGS_FI_PRSID_OR_ALTID');
475 fnd_msg_pub.add;
476 END IF;
477 END IF;
478
479 --validate person id parameter if it is not null
480 IF p_person_id IS NOT NULL THEN
481 l_b_ret_status := validate_prsn(p_person_id);
482 IF NOT l_b_ret_status THEN
483 l_b_error := TRUE;
484 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
485 fnd_message.set_name('IGS','IGS_FI_INVALID_PRS_ID');
486 fnd_message.set_token('PERSON_ID',p_person_id);
487 fnd_msg_pub.add;
488 END IF;
489 END IF;
490 END IF;
491
492 -- When user inputs User Defined Person ID Type then validate User-Defined Person ID Type if it exists
493 -- and setup as Unique in the system
494 -- Since User-Defined Alternate ID should be passed when the user inputs API Person ID, validate User-Defined
495 -- Alternate Person Id only when API Person ID is not null.
496 IF p_api_person_id IS NOT NULL THEN
497 igs_fi_gen_006.validate_prsn_id_typ(p_c_usr_alt_prs_id_typ => p_alt_person_id_type,
498 p_c_unique => 'Y',
499 p_b_status => l_b_ret_status,
500 p_c_sys_alt_prs_id_typ => l_c_sys_alt_prs_id_typ);
501
502 IF NOT l_b_ret_status THEN
503 l_b_error := TRUE;
504 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
505 fnd_message.set_name('IGS','IGS_FI_INVALID_ALT_PERS_ID_TYP');
506 fnd_message.set_token('API_PERS_ID_TYPE',p_alt_person_id_type);
507 fnd_msg_pub.add;
508 END IF;
509 END IF;
510 END IF;
511
512 -- validate alternate person id parameter and determine person id
513 IF (p_api_person_id IS NOT NULL AND p_alt_person_id_type IS NOT NULL) THEN
514 validate_api_prsn_id(p_c_sys_prsn_id_typ => l_c_sys_alt_prs_id_typ,
515 p_c_usr_alt_prsn_id_typ => p_alt_person_id_type,
516 p_c_api_prsn_id => p_api_person_id,
517 p_b_ret_status => l_b_ret_status,
518 p_n_person_id => l_n_person_id);
519
520
521 IF NOT l_b_ret_status THEN
522 l_b_error := TRUE;
523 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
524 fnd_message.set_name('IGS','IGS_FI_INVALID_ALT_PERS_ID');
525 fnd_message.set_token('API_PERS_ID',p_api_person_id);
526 fnd_msg_pub.add;
527 END IF;
528 END IF;
529 END IF;
530
531 -- validate sponsor code parameter
532 IF p_sponsor_code IS NULL THEN
533 l_b_error := TRUE;
534 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
535 fnd_message.set_name('IGS','IGS_FI_SPNSR_CD_NULL');
536 fnd_msg_pub.add;
537 END IF;
538 ELSE
539 l_b_ret_status := validate_spnsr_cd(p_sponsor_code);
540 IF NOT l_b_ret_status THEN
541 l_b_error := TRUE;
542 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
543 fnd_message.set_name('IGS','IGS_FI_INVALID_SPNSR_CD');
544 fnd_message.set_token('SPONSOR_CODE',p_sponsor_code);
545 fnd_msg_pub.add;
546 END IF;
547 END IF;
548 END IF;
549
550 -- validate Award Calendar Instance parameters
551 IF (p_awd_ci_cal_type IS NULL OR p_awd_ci_sequence_number IS NULL) THEN
552 l_b_error := TRUE;
553 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
554 fnd_message.set_name('IGS','IGS_FI_AWD_INST_NULL');
555 fnd_msg_pub.add;
556 END IF;
557 ELSE
558 l_b_ret_status := validate_award_cal_inst(p_awd_ci_cal_type,p_awd_ci_sequence_number);
559 IF NOT l_b_ret_status THEN
560 l_b_error := TRUE;
561 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
562 fnd_message.set_name('IGS','IGS_FI_INVALID_AWD_CAL_INST');
563 fnd_message.set_token('AWARD_YR_TYPE',p_awd_ci_cal_type);
564 fnd_message.set_token('AWARD_YR_CAL_SEQ',p_awd_ci_sequence_number);
565 fnd_msg_pub.add;
566 END IF;
567 END IF;
568
569 --Validate the Award Year Status. If the status is not open, show the error message.
570 l_c_message_name := NULL;
571 igs_fi_crdapi_util.get_award_year_status( p_v_awd_cal_type => p_awd_ci_cal_type,
572 p_n_awd_seq_number => p_awd_ci_sequence_number,
573 p_v_awd_yr_status => l_v_awd_yr_status_cd,
574 p_v_message_name => l_c_message_name
575 );
576 IF l_c_message_name IS NOT NULL THEN
577 l_b_error := TRUE;
578 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
579 IF l_c_message_name = 'IGF_SP_INVALID_AWD_YR_STATUS' THEN
580 fnd_message.set_name('IGF',l_c_message_name);
581 ELSE
582 fnd_message.set_name('IGS',l_c_message_name);
583 END IF;
584 fnd_msg_pub.add;
585 END IF;
586 END IF;
587 END IF;
588
589 -- validate Load Calendar Instance parameters
590 IF (p_ld_cal_type IS NULL OR p_ld_ci_sequence_number IS NULL ) THEN
591 l_b_error := TRUE;
592 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
593 fnd_message.set_name('IGS','IGS_FI_LD_INST_NULL');
594 fnd_msg_pub.add;
595 END IF;
596 ELSE
597 l_b_ret_status := validate_load_cal_inst(p_ld_cal_type,p_ld_ci_sequence_number);
598 IF NOT l_b_ret_status THEN
599 l_b_error := TRUE;
600 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
601 fnd_message.set_name('IGS','IGS_FI_INVALID_TRM_CAL_INST');
602 fnd_message.set_token('LOAD_CAL_TYPE',p_ld_cal_type);
603 fnd_message.set_token('LOAD_CAL_SEQ',p_ld_ci_sequence_number);
604 fnd_msg_pub.add;
605 END IF;
606 END IF;
607 END IF;
608
609 -- Validate Amount parameter
610 IF (p_amount IS NULL OR p_amount < 0) THEN
611 l_b_error := TRUE;
612 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
613 fnd_message.set_name('IGS','IGS_FI_INVALID_SPR_AMT');
614 fnd_msg_pub.add;
615 END IF;
616 END IF;
617
618 IF (p_sponsor_code IS NOT NULL AND
619 p_awd_ci_cal_type IS NOT NULL AND
620 p_awd_ci_sequence_number IS NOT NULL AND
621 p_ld_cal_type IS NOT NULL AND
622 p_ld_ci_sequence_number IS NOT NULL ) THEN
623 -- Check if the Sponsor and Award Calendar Instance is pre-defined in the system
624 check_spnsr_awd_rel(p_c_fund_code=> p_sponsor_code,
625 p_c_aw_cal_type => p_awd_ci_cal_type,
626 p_n_aw_sequence_number => p_awd_ci_sequence_number,
627 p_c_ld_cal_type => p_ld_cal_type,
628 p_n_ld_sequence_number => p_ld_ci_sequence_number,
629 p_b_return_status => l_b_ret_status,
630 p_n_fund_id => l_n_fund_id );
631
632 IF NOT l_b_ret_status THEN
633 l_b_error := TRUE;
634 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
635 fnd_message.set_name('IGS','IGS_FI_SPNR_INACTIVE');
636 fnd_message.set_token('SPONSOR_CODE',p_sponsor_code);
637 fnd_message.set_token('AWARD_YR_TYPE',p_awd_ci_cal_type);
638 fnd_message.set_token('AWARD_YR_CAL_SEQ',p_awd_ci_sequence_number);
639 fnd_message.set_token('LOAD_CAL_TYPE',p_ld_cal_type);
640 fnd_message.set_token('LOAD_CAL_SEQ',p_ld_ci_sequence_number);
641 fnd_msg_pub.add;
642 END IF;
643 END IF;
644 END IF;
645
646 -- If any of the parameter validation fails then there is no need to proceed further.
647 -- For erroring out in this case, should RAISE g_exc_error exception
648 IF NOT l_b_error THEN
649 -- invoke local procedure to identify the base id of the student for the Award Calendar Instance and Person Id combination
650 check_create_fa_rec(NVL(p_person_id,l_n_person_id),p_awd_ci_cal_type,p_awd_ci_sequence_number,l_b_ret_status,l_n_base_id);
651
652 -- If the above procedure returns TRUE then the OUT variable l_n_base_id has the value for Base ID
653 IF l_b_ret_status THEN
654 l_b_ret_status:= check_spnsr_stdnt_rel(l_n_fund_id,
655 l_n_base_id,
656 p_ld_cal_type,
657 p_ld_ci_sequence_number);
658 IF l_b_ret_status THEN
659 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
660 fnd_message.set_name('IGS','IGS_FI_SPNR_STDNT_EXISTS');
661 fnd_message.set_token('PERSON_ID',NVL(p_person_id,l_n_person_id));
662 fnd_message.set_token('SPONSOR_CODE',p_sponsor_code);
663 fnd_message.set_token('AWARD_YR_TYPE',p_awd_ci_cal_type);
664 fnd_message.set_token('AWARD_YR_CAL_SEQ',p_awd_ci_sequence_number);
665 fnd_message.set_token('LOAD_CAL_TYPE',p_ld_cal_type);
666 fnd_message.set_token('LOAD_CAL_SEQ',p_ld_ci_sequence_number);
667 fnd_msg_pub.add;
668 RAISE fnd_api.g_exc_error;
669 END IF;
670 ELSE
671 -- If there is no relation then a new Sponsor-Student relation should be created.
672 -- Bug 5217319 Added call to format amount by rounding off to currency precision
673 igf_sp_stdnt_rel_pkg.insert_row (x_rowid => l_rowid,
674 x_spnsr_stdnt_id=> l_n_spr_stdnt_id,
675 x_fund_id => l_n_fund_id ,
676 x_base_id=> l_n_base_id,
677 x_person_id=> NVL(p_person_id, l_n_person_id),
678 x_ld_cal_type=> p_ld_cal_type,
679 x_ld_sequence_number=> p_ld_ci_sequence_number,
680 x_tot_spnsr_amount=> igs_fi_gen_gl.get_formatted_amount(p_amount),
681 x_min_credit_points=> NULL,
682 x_min_attendance_type=> NULL);
683 END IF;
684 END IF;
685 ELSE
686 RAISE fnd_api.g_exc_error;
687 END IF;
688
689 -- If the calling program has passed the parameter for committing the data and there
690 -- have been no errors in calling the balances process, then commit the work
691 IF fnd_api.to_boolean(p_commit) THEN
692 COMMIT WORK;
693 END IF;
694
695 fnd_msg_pub.count_and_get(p_count => x_msg_count,
696 p_data => x_msg_data);
697
698 EXCEPTION
699 WHEN fnd_api.g_exc_error THEN
700 ROLLBACK TO create_stdnt_spnsr_rel_pub;
701 x_return_status := fnd_api.g_ret_sts_error;
702 fnd_msg_pub.count_and_get( p_count => x_msg_count,
703 p_data => x_msg_data);
704 WHEN fnd_api.g_exc_unexpected_error THEN
705 ROLLBACK TO create_stdnt_spnsr_rel_pub;
706 x_return_status := fnd_api.g_ret_sts_unexp_error;
707 fnd_msg_pub.count_and_get( p_count => x_msg_count,
708 p_data => x_msg_data);
709 WHEN OTHERS THEN
710 ROLLBACK TO create_stdnt_spnsr_rel_pub;
711 x_return_status := fnd_api.g_ret_sts_unexp_error;
712 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
713 fnd_msg_pub.add_exc_msg(g_pkg_name,
714 l_api_name);
715 END IF;
716 fnd_msg_pub.count_and_get( p_count => x_msg_count,
717 p_data => x_msg_data);
718 END create_stdnt_spnsr_rel;
719 END igf_sp_assign_pub;
[Home] [Help]