1 PACKAGE BODY igf_se_gen_001 AS
2 /* $Header: IGFSE01B.pls 120.7 2006/02/01 02:56:25 ridas ship $ */
3
4 PROCEDURE display_auth_params(p_awd_cal_type IN igs_ca_inst.cal_type%TYPE,
5 p_awd_seq_num IN igs_ca_inst.sequence_number%TYPE,
6 p_fund_id IN igf_aw_fund_mast_all.fund_id%TYPE,
7 p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE
8 ) IS
9 ------------------------------------------------------------------------------------
10 --Created by : brajendr
11 --Date created : 16-May-2002
12 --Purpose : Displays all the paramters which are passed into the Job
13 --Known limitations/enhancements and/or remarks:
14 --Change History:
15 --Who When What
16 --ridas 29/Jul/2005 Bug #3536039. Raise exception IGFSEGEN001 if p_call = 'LEGACY' in procedure SEND_WORK_AUTH
17 --veramach July 2004 Bug #3709292 Parameters are printed only if the value is not null
18 --cdcruz 14/Jan/2004 Logging Messges added to track Bug# 3346948
19 -- No check was present for First/Last Name, the same has been done
20 --rasingh 6/Jan/2003 Performance Tuning Fixes: 2620242,2620259,2620264
21 -------------------------------------------------------------------------------------
22
23 --Cursor to find the User Parameter Award Year (which is same as Alternate Code) to display in the Log
24 CURSOR c_alternate_code(cp_ci_cal_type igs_ca_inst.cal_type%TYPE,
25 cp_ci_sequence_number igs_ca_inst.sequence_number%TYPE) IS
26 SELECT alternate_code
27 FROM igs_ca_inst
28 WHERE cal_type = cp_ci_cal_type
29 AND sequence_number = cp_ci_sequence_number;
30
31 CURSOR c_get_parameters IS
32 SELECT meaning, lookup_code
33 FROM igf_lookups_view
34 WHERE lookup_type='IGF_GE_PARAMETERS'
35 AND lookup_code IN ('AWARD_YEAR','FUND_CODE','PERSON_NUMBER','PARAMETER_PASS');
36
37 CURSOR c_person_dtls (c_base_id igf_ap_fa_base_rec_all.base_id%TYPE) IS
38 SELECT party_number
39 FROM hz_parties hz,
40 igf_ap_fa_base_rec_all fa
41 WHERE fa.person_id = hz.party_id
42 AND fa.base_id = c_base_id;
43
44 CURSOR c_fund_dtls (c_fund_id igf_aw_fund_mast_all.fund_id%TYPE) IS
45 SELECT fund_code
46 FROM igf_aw_fund_mast
47 WHERE fund_id = c_fund_id;
48
49 parameter_rec c_get_parameters%ROWTYPE;
50 l_award_year igf_lookups_view.meaning%TYPE;
51 l_fund_code igf_lookups_view.meaning%TYPE;
52 l_person_number igf_lookups_view.meaning%TYPE;
53 l_para_pass igf_lookups_view.meaning%TYPE;
54 l_awd_alternate_code igs_ca_inst.alternate_code%TYPE := NULL;
55 l_fund_id igf_aw_fund_mast_all.fund_code%TYPE := NULL;
56 l_base_id igs_pe_person.person_number%TYPE := NULL;
57
58 BEGIN
59
60 -- Get all the Parameters
61 OPEN c_get_parameters;
62 LOOP
63 FETCH c_get_parameters INTO parameter_rec;
64 EXIT WHEN c_get_parameters%NOTFOUND;
65 IF parameter_rec.lookup_code ='AWARD_YEAR' THEN
66 l_award_year := TRIM(parameter_rec.meaning);
67 ELSIF parameter_rec.lookup_code ='FUND_CODE' THEN
68 l_fund_code := TRIM(parameter_rec.meaning);
69 ELSIF parameter_rec.lookup_code ='PERSON_NUMBER' THEN
70 l_person_number := TRIM(parameter_rec.meaning);
71 ELSIF parameter_rec.lookup_code ='PARAMETER_PASS' THEN
72 l_para_pass := TRIM(parameter_rec.meaning);
73 END IF;
74 END LOOP;
75 CLOSE c_get_parameters;
76
77 -- Get the Award Year Alternate Code
78 OPEN c_alternate_code(p_awd_cal_type,p_awd_seq_num);
79 FETCH c_alternate_code INTO l_awd_alternate_code;
80 CLOSE c_alternate_code;
81
82 -- Get the Load Calendar Alternate Code
83 OPEN c_person_dtls(p_base_id);
84 FETCH c_person_dtls INTO l_base_id;
85 CLOSE c_person_dtls;
86
87 -- Get the Load Calendar Alternate Code
88 OPEN c_fund_dtls(p_fund_id);
89 FETCH c_fund_dtls INTO l_fund_id;
90 CLOSE c_fund_dtls;
91
92 /* Print the Parameters Passed */
93 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
94 FND_FILE.PUT_LINE(FND_FILE.LOG,l_para_pass); --------------Parameters Passed--------------
95 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
96 IF l_awd_alternate_code IS NOT NULL THEN
97 FND_FILE.PUT_LINE(FND_FILE.LOG,RPAD(l_award_year,30) || ' : '|| l_awd_alternate_code);
98 END IF;
99 IF l_fund_id IS NOT NULL THEN
100 FND_FILE.PUT_LINE(FND_FILE.LOG,RPAD(l_fund_code,30) || ' : '|| l_fund_id);
101 END IF;
102 IF l_base_id IS NOT NULL THEN
103 FND_FILE.PUT_LINE(FND_FILE.LOG,RPAD(l_person_number,30) || ' : '|| l_base_id);
104 END IF;
105 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
106 FND_FILE.PUT_LINE(FND_FILE.LOG,'-------------------------------------------------------------');
107 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
108 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
109
110 END display_auth_params;
111
112
113 PROCEDURE display_auth_process_log(p_person_number IN igs_pe_person.person_number%TYPE,
114 p_fund_code IN igf_aw_fund_mast_all.fund_code%TYPE,
115 p_award_id IN igf_aw_award_v.award_id%TYPE
116 ) IS
117 ------------------------------------------------------------------------------------
118 --Created by : brajendr
119 --Date created : 16-May-2002
120 --Purpose : Displays all the paramters which are passed into the Job
121 --Known limitations/enhancements and/or remarks:
122 --Change History:
123 -- Who When What
124 --veramach July 2004 Bug #3709292 Parameters are printed only if the value is not null
125 -- masehgal 228-dec-2002 # 2445830 Changed log to display the load calendar and
126 -- award id to make it less ambiguous.
127 -------------------------------------------------------------------------------------
128
129 CURSOR c_get_parameters IS
130 SELECT meaning, lookup_code
131 FROM igf_lookups_view
132 WHERE lookup_type = 'IGF_GE_PARAMETERS'
133 AND lookup_code IN ('FUND_CODE','PERSON_NUMBER','AWARD_ID');
134
135 parameter_rec c_get_parameters%ROWTYPE;
136 l_fund_code igf_lookups_view.meaning%TYPE;
137 l_person_number igf_lookups_view.meaning%TYPE;
138 l_award_id igf_lookups_view.meaning%TYPE;
139
140 BEGIN
141
142 -- Get all the Parameters
143 OPEN c_get_parameters;
144 LOOP
145 FETCH c_get_parameters INTO parameter_rec;
146 EXIT WHEN c_get_parameters%NOTFOUND;
147 IF parameter_rec.lookup_code ='FUND_CODE' THEN
148 l_fund_code := TRIM(parameter_rec.meaning);
149 ELSIF parameter_rec.lookup_code ='PERSON_NUMBER' THEN
150 l_person_number := TRIM(parameter_rec.meaning);
151 ELSIF parameter_rec.lookup_code ='AWARD_ID' THEN
152 l_award_id := TRIM(parameter_rec.meaning);
153 END IF;
154 END LOOP;
155 CLOSE c_get_parameters;
156
157 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
158 IF p_person_number IS NOT NULL THEN
159 FND_FILE.PUT_LINE(FND_FILE.LOG,RPAD(l_person_number,30) || ' : '||p_person_number);
160 END IF;
161
162 IF p_fund_code IS NOT NULL THEN
163 FND_FILE.PUT_LINE(FND_FILE.LOG,RPAD(l_fund_code,30) || ' : '||p_fund_code);
164 END IF;
165
166 IF p_award_id IS NOT NULL THEN
167 FND_FILE.PUT_LINE(FND_FILE.LOG,RPAD(l_award_id,30) || ' : '||p_award_id);
168 END IF;
169
170 END display_auth_process_log;
171
172
173 PROCEDURE display_payroll_params(p_batch_id IN igf_se_payment_int.batch_id%TYPE,
174 p_auth_id IN igf_se_auth.auth_id%TYPE,
175 p_validation_lvl IN VARCHAR2
176 ) IS
177 ------------------------------------------------------------------------------------
178 --Created by : brajendr
179 --Date created : 16-May-2002
180 --Purpose : Displays all the paramters which are passed into the Job
181 --Known limitations/enhancements and/or remarks:
182 --Change History:
183 --Who When What
184 -------------------------------------------------------------------------------------
185
186 --Cursor to find the User Parameter Award Year (which is same as Alternate Code) to display in the Log
187 CURSOR c_get_parameters IS
188 SELECT meaning, lookup_code
189 FROM igf_lookups_view
190 WHERE lookup_type='IGF_GE_PARAMETERS'
191 AND lookup_code IN ('BATCH_ID','AUTH_ID','VALIDATION_LVL','PARAMETER_PASS');
192
193 parameter_rec c_get_parameters%ROWTYPE;
194 l_batch_id igf_lookups_view.meaning%TYPE;
195 l_auth_id igf_lookups_view.meaning%TYPE;
196 l_validation_lvl igf_lookups_view.meaning%TYPE;
197 l_para_pass igf_lookups_view.meaning%TYPE;
198
199 BEGIN
200
201 -- Get all the Parameters
202 OPEN c_get_parameters;
203 LOOP
204 FETCH c_get_parameters INTO parameter_rec;
205 EXIT WHEN c_get_parameters%NOTFOUND;
206
207 IF parameter_rec.lookup_code ='BATCH_ID' THEN
208 l_batch_id := TRIM(parameter_rec.meaning);
209
210 ELSIF parameter_rec.lookup_code ='AUTH_ID' THEN
211 l_auth_id := TRIM(parameter_rec.meaning);
212
213 ELSIF parameter_rec.lookup_code ='VALIDATION_LVL' THEN
214 l_validation_lvl := TRIM(parameter_rec.meaning);
215
216 ELSIF parameter_rec.lookup_code ='PARAMETER_PASS' THEN
217 l_para_pass := TRIM(parameter_rec.meaning);
218
219 END IF;
220
221 END LOOP;
222 CLOSE c_get_parameters;
223
224 -- Print the Parameters Passed
225 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
226 FND_FILE.PUT_LINE(FND_FILE.LOG,l_para_pass); --------------Parameters Passed--------------
227 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
228 FND_FILE.PUT_LINE(FND_FILE.LOG,RPAD(l_batch_id,30) || ' : '|| p_batch_id);
229 FND_FILE.PUT_LINE(FND_FILE.LOG,RPAD(l_auth_id,30) || ' : '|| p_auth_id);
230 FND_FILE.PUT_LINE(FND_FILE.LOG,RPAD(l_validation_lvl,30) || ' : '|| p_validation_lvl);
231 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
232 FND_FILE.PUT_LINE(FND_FILE.LOG,'-------------------------------------------------------------');
233 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
234 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
235
236 END display_payroll_params;
237
238
239 PROCEDURE send_work_auth(p_base_id IN igf_ap_fa_base_rec.base_id%TYPE,
240 p_person_id IN hz_parties.party_id%TYPE,
241 p_fund_id IN igf_aw_fund_mast.fund_id%TYPE,
242 p_award_id IN igf_aw_award.award_id%TYPE,
243 p_ld_cal_type IN igs_ca_inst.cal_type%TYPE,
244 p_ld_seq_no IN igs_ca_inst.sequence_number%TYPE,
245 p_call IN VARCHAR2,
246 p_auth_date IN DATE
247 ) IS
248 ------------------------------------------------------------------------------------
249 -- Created by : ssawhney ( Oracle IDC)
250 -- Date created: 2nd jan
251 -- Purpose: This procedure will be used to create work authorizations for a student
252 -- for an award and for a term. The authorization details will be used by Student
253 -- Employment module to pass to the external/legacy HR system
254 --
255 -- Known limitations/enhancements and/or remarks:
256 -- Change History:
257 -- Who When What
258 --veramach July 2004 FA 151 HR Integration (Bug# 3709292) Changes
259 -- New parameter(AUTH_DATE) added
260 -- brajendr 03-Jun-2002 Changed the where clause from the Cursor c_awd. Now the
261 -- cursor will pick all the records.
262 -- Added one more Validation, SSN is a mandatory for all
263 -- FWS awarded students.
264 -------------------------------------------------------------------------------------
265
266 CURSOR c_fund_mast ( cv_fund_id igf_aw_fund_mast.fund_id%TYPE) IS
267 SELECT threshold_perct, threshold_value
268 FROM igf_aw_fund_mast
269 WHERE fund_id = cv_fund_id;
270
271 CURSOR c_awd (cv_fund_id igf_aw_fund_mast.fund_id%TYPE,
272 cv_award_id igf_aw_award.award_id%TYPE,
273 cv_base_id igf_ap_fa_base_rec.base_id%TYPE ) IS
274 SELECT pit.api_person_id ssn,
275 fmast.ci_cal_type,
276 fmast.ci_sequence_number,
277 awd.base_id,
278 awd.award_id
279 FROM igf_aw_award awd,
280 igf_aw_fund_cat fcat,
281 igf_aw_fund_mast fmast,
282 igf_ap_fa_base_rec farec,
283 igs_pe_alt_pers_id_v pit,
284 igs_pe_person_id_typ pit_2
285 WHERE awd.fund_id = fmast.fund_id
286 AND awd.base_id = cv_base_id
287 AND awd.base_id = farec.base_id
288 AND fcat.fund_code = fmast.fund_code
289 AND fcat.fed_fund_code = 'FWS'
290 AND awd.fund_id = cv_fund_id
291 AND awd.award_id = cv_award_id
292 AND farec.person_id = pit.pe_person_id (+)
293 AND pit.person_id_type = pit_2.person_id_type
294 AND pit_2.s_person_id_type = 'SSN'
295 AND SYSDATE BETWEEN pit.start_dt AND NVL(pit.end_dt, SYSDATE);
296
297 -- Cursor used to get the Language transulated tokens
298 CURSOR c_get_tokens IS
299 SELECT meaning, lookup_code
300 FROM igf_lookups_view
301 WHERE lookup_type = 'IGF_MATCH_CRITERIA'
302 AND lookup_code = 'SSN';
303
304 CURSOR c_hzp (cv_person_id hz_parties.party_id%TYPE ) IS
305 SELECT person_first_name, person_last_name, address1, address2, address3, address4, city,
306 state, province, county, country
307 FROM hz_parties
308 WHERE party_id = cv_person_id;
309
310 CURSOR c_pe (cv_person_id hz_parties.party_id%TYPE ) IS
311 SELECT gender sex, birth_date birth_dt
312 FROM igs_pe_person_base_v
313 WHERE person_id = cv_person_id;
314
315 CURSOR c_visa (cv_person_id hz_parties.party_id%TYPE ) IS
316 SELECT a.visa_type,a.visa_category,a.visa_number,a.visa_expiry_date, b.visit_start_date entry_date
317 FROM igs_pe_visa a,igs_pe_visit_histry b
318 WHERE a.person_id = cv_person_id
319 AND NVL(a.visa_expiry_date,SYSDATE) >= SYSDATE
320 AND a.visa_id = b.visa_id
321 ORDER BY a.visa_expiry_date DESC;
322
323 CURSOR c_stat (cv_person_id hz_parties.party_id%TYPE ) IS
324 SELECT NVL(marital_status,'NA') marital_status
325 FROM HZ_PERSON_PROFILES
326 WHERE party_id = cv_person_id
327 AND SYSDATE BETWEEN EFFECTIVE_START_DATE AND NVL(EFFECTIVE_END_DATE, SYSDATE);
328
329 CURSOR c_accept_amnt(
330 cp_award_id igf_aw_award_all.award_id%TYPE
331 ) IS
332 SELECT NVL(awd.accepted_amt,0) accepted_amt
333 FROM igf_aw_award awd
334 WHERE awd.award_id = cp_award_id;
335
336 CURSOR c_next_auth IS
337 SELECT igf_se_auth_s1.NEXTVAL
338 FROM dual;
339
340 CURSOR c_auth_check(cv_award_id igf_aw_award.award_id%TYPE,
341 cv_ld_cal_type igs_ca_inst.cal_type%TYPE,
342 cv_ld_seq_no igs_ca_inst.sequence_number%TYPE
343 ) IS
344 SELECT auth_id
345 FROM igf_se_auth
346 WHERE award_id = cv_award_id
347 AND auth_id IS NOT NULL
348 AND flag = 'A';
349
350
351 CURSOR c_old_auth (cv_auth_id igf_se_auth.auth_id%TYPE) IS
352 SELECT rowid, sai.*
353 FROM igf_se_auth sai
354 WHERE sai.auth_id = cv_auth_id
355 AND sai.flag ='A'
356 FOR UPDATE NOWAIT;
357
358 next_record EXCEPTION;
359 visa_details_not_found EXCEPTION;
360
361 fund_mast_rec c_fund_mast%ROWTYPE;
362 awd_rec c_awd%ROWTYPE;
363 hzp_rec c_hzp%ROWTYPE;
364 pe_rec c_pe%ROWTYPE;
365 visa_rec c_visa%ROWTYPE;
366 stat_rec c_stat%ROWTYPE;
367 accept_amnt_rec c_accept_amnt%ROWTYPE;
368 auth_check_rec c_auth_check%ROWTYPE;
369 old_auth_rec c_old_auth%ROWTYPE;
370 tokens_rec c_get_tokens%ROWTYPE;
371
372 l_place VARCHAR2(30);
373 l_person_id hz_parties.party_id%TYPE;
374 l_fund_id igf_aw_fund_mast.fund_id%TYPE;
375 l_ld_cal_type igs_ca_inst.cal_type%TYPE;
376 l_ld_seq_no igs_ca_inst.sequence_number%TYPE;
377 l_auth_id igf_se_auth.auth_id%TYPE := 0;
378 l_sequence_no igf_se_auth.sequence_no%TYPE := 0;
379 l_rowid VARCHAR2(30) := 0;
380 l_visa_type igf_se_auth.visa_type%TYPE;
381 l_visa_category igf_se_auth.visa_category%TYPE;
382 l_visa_number igf_se_auth.visa_number%TYPE;
383 l_visa_expiry_dt igf_se_auth.visa_expiry_dt%TYPE;
384 l_entry_date igf_se_auth.entry_date%TYPE;
385 l_warning VARCHAR2(200);
386
387 PROCEDURE insert_auth IS
388 --------------------------------------------------------
389 --Created by : ssawhney on 2nd Jan
390 --Purpose : local procedure to insert into igf_se_auth table
391 --Change History :
392 --Who When What
393 ----------------------------------------------------------------
394 l_rowid VARCHAR2(30);
395 l_sequence_no igf_se_auth.sequence_no%TYPE;
396 BEGIN
397 -- insert a new auth rec
398
399 l_rowid := NULL;
400 l_sequence_no := NULL;
401
402 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
403
404 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_se_gen_001.send_work_auth.insert_auth','x_flag =>' || 'A');
405 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_se_gen_001.send_work_auth.insert_auth','x_person_id =>' || l_person_id);
406 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_se_gen_001.send_work_auth.insert_auth','x_first_name =>' || hzp_rec.person_first_name);
407 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_se_gen_001.send_work_auth.insert_auth','x_last_name =>' || hzp_rec.person_last_name);
408 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_se_gen_001.send_work_auth.insert_auth','x_address1 =>' || NVL(hzp_rec.address1,'NA'));
409 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_se_gen_001.send_work_auth.insert_auth','x_address2 =>' || hzp_rec.address2);
410 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_se_gen_001.send_work_auth.insert_auth','x_address3 =>' || hzp_rec.address3);
411 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_se_gen_001.send_work_auth.insert_auth','x_address4 =>' || hzp_rec.address4);
412 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_se_gen_001.send_work_auth.insert_auth','x_city =>' || hzp_rec.city);
413 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_se_gen_001.send_work_auth.insert_auth','x_state =>' || hzp_rec.state);
414 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_se_gen_001.send_work_auth.insert_auth','x_province =>' || hzp_rec.province);
415 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_se_gen_001.send_work_auth.insert_auth','x_county =>' || hzp_rec.county);
416 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_se_gen_001.send_work_auth.insert_auth','x_country =>' || hzp_rec.country);
417 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_se_gen_001.send_work_auth.insert_auth','x_sex =>' || pe_rec.sex);
418 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_se_gen_001.send_work_auth.insert_auth','x_birth_dt =>' || NVL(pe_rec.birth_dt,SYSDATE));
419 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_se_gen_001.send_work_auth.insert_auth','x_ssn_no =>' || awd_rec.ssn);
420 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_se_gen_001.send_work_auth.insert_auth','x_marital_status =>' || NVL(stat_rec.marital_status,'NA'));
421 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_se_gen_001.send_work_auth.insert_auth','x_visa_type =>' || l_visa_type);
422 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_se_gen_001.send_work_auth.insert_auth','x_visa_category =>' || l_visa_category);
423 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_se_gen_001.send_work_auth.insert_auth','x_visa_number =>' || l_visa_number);
424 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_se_gen_001.send_work_auth.insert_auth','x_visa_expiry_dt =>' || l_visa_expiry_dt);
425 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_se_gen_001.send_work_auth.insert_auth','x_entry_date =>' || l_entry_date);
426 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_se_gen_001.send_work_auth.insert_auth','x_fund_id =>' || l_fund_id);
427 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_se_gen_001.send_work_auth.insert_auth','x_threshold_perct =>' || fund_mast_rec.threshold_perct);
428 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_se_gen_001.send_work_auth.insert_auth','x_threshold_value =>' || fund_mast_rec.threshold_value);
429 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_se_gen_001.send_work_auth.insert_auth','x_accepted_amnt =>' || accept_amnt_rec.accepted_amt);
430 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_se_gen_001.send_work_auth.insert_auth','x_aw_cal_type =>' || awd_rec.ci_cal_type);
431 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_se_gen_001.send_work_auth.insert_auth','x_aw_sequence_number =>' || awd_rec.ci_sequence_number);
432 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_se_gen_001.send_work_auth.insert_auth','x_award_id =>' || awd_rec.award_id);
433 END IF;
434
435
436 igf_se_auth_pkg.insert_row(
437 x_rowid => l_rowid,
438 x_sequence_no => l_sequence_no,
439 x_auth_id => l_auth_id,
440 x_flag => 'A', -- this is the active record now.
441 x_person_id => l_person_id,
442 x_first_name => hzp_rec.person_first_name,
443 x_last_name => hzp_rec.person_last_name,
444 x_address1 => NVL(hzp_rec.address1,'NA'),
445 x_address2 => hzp_rec.address2,
446 x_address3 => hzp_rec.address3,
447 x_address4 => hzp_rec.address4,
448 x_city => hzp_rec.city,
449 x_state => hzp_rec.state,
450 x_province => hzp_rec.province,
451 x_county => hzp_rec.county,
452 x_country => hzp_rec.country,
453 x_sex => pe_rec.sex,
454 x_birth_dt => NVL(pe_rec.birth_dt,SYSDATE),
455 x_ssn_no => awd_rec.ssn,
456 x_marital_status => NVL(stat_rec.marital_status,'NA'),
457 x_visa_type => l_visa_type,
458 x_visa_category => l_visa_category,
459 x_visa_number => l_visa_number,
460 x_visa_expiry_dt => l_visa_expiry_dt,
461 x_entry_date => l_entry_date,
462 x_fund_id => l_fund_id,
463 x_threshold_perct => fund_mast_rec.threshold_perct,
464 x_threshold_value => fund_mast_rec.threshold_value,
465 x_accepted_amnt => accept_amnt_rec.accepted_amt,
466 x_aw_cal_type => awd_rec.ci_cal_type,
467 x_aw_sequence_number => awd_rec.ci_sequence_number,
468 x_award_id => awd_rec.award_id,
469 x_authorization_date => SYSDATE,
470 x_notification_date => NULL
471 );
472
473 EXCEPTION
474 WHEN OTHERS THEN
475 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
476 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_se_gen_001.send_work_auth.insert_auth',' Unhandled Exception ->' || SQLERRM);
477 END IF;
478
479 FND_MESSAGE.SET_NAME('IGF','IGF_GE_UNHANDLED_EXP');
480 FND_MESSAGE.SET_TOKEN('NAME','igf_se_gen_001.send_work_auth:igf_se_auth.insert') ;
481 IGS_GE_MSG_STACK.ADD;
482
483 IF p_call = 'JOB' THEN -- continue for next student dont stop
484 RAISE NEXT_RECORD; -- user defined exception
485 ELSE -- this means its called from FORM
486 APP_EXCEPTION.RAISE_EXCEPTION;
487 END IF;
488
489 END insert_auth;
490
491 -- begin the main procedure.
492 BEGIN
493 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
494 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_se_gen_001.send_work_auth',
495 'Parameters p_base_id->'|| TO_CHAR(p_base_id) ||
496 ' p_person_id'|| TO_CHAR(p_person_id) ||
497 ' p_fund_id'|| TO_CHAR(p_fund_id) ||
498 ' p_award_id'|| TO_CHAR(p_award_id) ||
499 ' p_call'|| p_call);
500 END IF;
501
502 l_person_id := p_person_id ;
503 l_fund_id := p_fund_id;
504
505 -- check all the parameters are NOT NULL
506 IF (l_person_id IS NULL)
507 OR (l_fund_id IS NULL) THEN
508
509 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
510 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_se_gen_001.send_work_auth',' Parameter Error');
511 END IF;
512
513 FND_MESSAGE.SET_NAME('IGF','IGF_AW_PARAM_ERR');
514 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
515 IGS_GE_MSG_STACK.ADD;
516 APP_EXCEPTION.RAISE_EXCEPTION;
517 END IF;
518
519 -- get fund setup details.
520 OPEN c_fund_mast (l_fund_id);
521 FETCH c_fund_mast INTO fund_mast_rec;
522
523 IF c_fund_mast%NOTFOUND THEN
524 CLOSE c_fund_mast;
525 l_place :='FUND';
526 RAISE NO_DATA_FOUND;
527 END IF;
528 CLOSE c_fund_mast;
529
530 -- get award set up details
531 OPEN c_awd (l_fund_id, p_award_id, p_base_id);
532 FETCH c_awd INTO awd_rec;
533 IF awd_rec.ssn IS NULL THEN
534 l_place :='SSN';
535 CLOSE c_awd;
536 RAISE NO_DATA_FOUND;
537 END IF;
538 CLOSE c_awd;
539
540 -- get person specific details
541 OPEN c_hzp (l_person_id);
542 FETCH c_hzp INTO hzp_rec;
543
544 IF c_hzp%NOTFOUND THEN
545 CLOSE c_hzp;
546 l_place :='HZ';
547 RAISE no_data_found;
548 END IF;
549 CLOSE c_hzp;
550
551 OPEN c_pe (l_person_id);
552 FETCH c_pe INTO pe_rec;
553 IF c_pe%NOTFOUND THEN
554 CLOSE c_pe;
555 l_place :='PE';
556 RAISE no_data_found;
557 END IF;
558 CLOSE c_pe;
559
560 IF hzp_rec.person_first_name IS NULL THEN
561 l_place :='F_NAME';
562 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
563 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_se_gen_001.send_work_auth',' First Name is null');
564 END IF;
565 RAISE no_data_found;
566 END IF;
567
568 IF hzp_rec.person_last_name IS NULL THEN
569 l_place :='L_NAME';
570 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
571 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_se_gen_001.send_work_auth',' Last Name is null');
572 END IF;
573 RAISE no_data_found;
574 END IF;
575
576 IF hzp_rec.country IS NULL THEN
577 l_place :='COUNTRY';
578 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
579 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_se_gen_001.send_work_auth',' Country is null');
580 END IF;
581 RAISE no_data_found;
582 END IF;
583
584 -- get visa details
585 OPEN c_visa (l_person_id);
586 FETCH c_visa INTO visa_rec;
587
588 -- Visa Details are not necessary for US residents / citizens
589 IF c_visa%NOTFOUND AND (hzp_rec.country IS NULL OR hzp_rec.country <> 'US' ) AND p_call <> 'LEGACY' THEN
590 CLOSE c_visa;
591 l_place :='VISA';
592 RAISE no_data_found;
593 ELSIF c_visa%FOUND THEN
594 l_visa_type := visa_rec.visa_type;
595 l_visa_category := visa_rec.visa_category;
596 l_visa_number := visa_rec.visa_number;
597 l_visa_expiry_dt := visa_rec.visa_expiry_date;
598 l_entry_date := visa_rec.entry_date;
599
600 END IF;
601 CLOSE c_visa;
602
603 -- get marital details
604 OPEN c_stat (l_person_id);
605 FETCH c_stat INTO stat_rec;
606 IF c_stat%NOTFOUND THEN
607 CLOSE c_stat;
608 l_place :='MARITAL';
609 RAISE no_data_found;
610 END IF;
611 CLOSE c_stat;
612
613 -- get the accepted amount by the student for the term.
614 OPEN c_accept_amnt(p_award_id);
615 FETCH c_accept_amnt INTO accept_amnt_rec;
616 CLOSE c_accept_amnt;
617
618 -- check if the authorization rec is new
619 BEGIN
620 -- start for auth creation
621 -- issue a save point.
622
623 SAVEPOINT se_payment;
624
625 l_auth_id :=NULL;
626 OPEN c_auth_check(awd_rec.award_id,l_ld_cal_type,l_ld_seq_no);
627 FETCH c_auth_check INTO l_auth_id;
628
629 -- if auth is not present then it means its a new rec
630 IF c_auth_check%NOTFOUND THEN
631
632 -- get the next auth_id from the sequence no
633 OPEN c_next_auth;
634 FETCH c_next_auth INTO l_auth_id;
635 CLOSE c_next_auth;
636
637 -- insert a new auth rec
638 insert_auth ;
639
640 CLOSE c_auth_check;
641
642 ELSE -- this means that auth_id is present and l_auth_id will have a value in this case
643
644 -- get the record of the interface table which has the FLAG=A and update it as INACTIVE
645 -- update the old record which was ACTIVE. There should only be one such rec.
646
647 OPEN c_old_auth (l_auth_id);
648 FETCH c_old_auth INTO old_auth_rec;
649 CLOSE c_old_auth;
650
651 BEGIN
652
653 igf_se_auth_pkg.update_row (
654 old_auth_rec.rowid,
655 old_auth_rec.sequence_no,
656 old_auth_rec.auth_id,
657 'I', -- this record is inactive now
658 old_auth_rec.person_id,
659 old_auth_rec.first_name,
660 old_auth_rec.last_name,
661 old_auth_rec.address1,
662 old_auth_rec.address2,
663 old_auth_rec.address3,
664 old_auth_rec.address4,
665 old_auth_rec.city,
666 old_auth_rec.state,
667 old_auth_rec.province,
668 old_auth_rec.county,
669 old_auth_rec.country,
670 old_auth_rec.sex,
671 old_auth_rec.birth_dt,
672 old_auth_rec.ssn_no,
673 old_auth_rec.marital_status,
674 old_auth_rec.visa_type,
675 old_auth_rec.visa_category,
676 old_auth_rec.visa_number,
677 old_auth_rec.visa_expiry_dt,
678 old_auth_rec.entry_date,
679 old_auth_rec.fund_id,
680 old_auth_rec.threshold_perct,
681 old_auth_rec.threshold_value,
682 old_auth_rec.accepted_amnt,
683 old_auth_rec.aw_cal_type,
684 old_auth_rec.aw_sequence_number,
685 'R',
686 old_auth_rec.award_id,
687 old_auth_rec.authorization_date,
688 old_auth_rec.notification_date
689 );
690
691 -- insert a new auth rec with the updated information in the igf_se_auth table.
692 -- this will be the active record now.
693 insert_auth;
694
695 EXCEPTION
696 WHEN OTHERS THEN
697 IF c_auth_check%ISOPEN THEN
698 CLOSE c_auth_check;
699 END IF;
700
701 FND_MESSAGE.SET_NAME('IGF','IGF_GE_UNHANDLED_EXP');
702 FND_MESSAGE.SET_TOKEN('NAME','igf_se_gen_001.send_work_auth:igf_se_auth.update');
703 IGS_GE_MSG_STACK.ADD;
704
705 IF p_call = 'JOB' THEN -- continue for next student dont stop
706 RAISE next_record; -- user defined exception
707 ELSE -- this means its called from FORM
708 app_exception.raise_exception;
709 END IF;
710 END;
711 END IF;
712
713 IF c_auth_check%ISOPEN THEN
714 CLOSE c_auth_check;
715 END IF;
716
717 EXCEPTION -- for auth creation
718 WHEN next_record THEN
719 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
720 ROLLBACK TO se_payment;
721 END ; -- for auth generation
722
723 IF p_call IN ('JOB','SE003') THEN
724 /*
725 If called from AW016, COMMIT should not be issued.
726 */
727 COMMIT; --Committing the Transaction
728 fnd_message.set_name('IGF','IGF_SE_REQUERY_AUTH');
729 fnd_file.put_line(fnd_file.log,fnd_message.get);
730 END IF;
731
732 EXCEPTION -- for main block
733 WHEN no_data_found THEN
734
735 FND_MESSAGE.SET_NAME('IGF','IGF_SE_INVALID_SETUP');
736 IF l_place = 'FUND' THEN
737 FND_MESSAGE.SET_TOKEN('PLACE','FUND');
738 ELSIF l_place = 'HZ' THEN
739 FND_MESSAGE.SET_TOKEN('PLACE','HZ:PERSON');
740 ELSIF l_place = 'PE' THEN
741 FND_MESSAGE.SET_TOKEN('PLACE','PERSON');
742 ELSIF l_place = 'VISA' THEN
743 FND_MESSAGE.SET_TOKEN('PLACE','VISA');
744
745 ELSIF l_place = 'COUNTRY' THEN
746 FND_MESSAGE.SET_TOKEN('PLACE',IGF_AP_GEN.GET_LOOKUP_MEANING('IGF_AP_MAP_PROFILE','COUNTRY'));
747 ELSIF l_place = 'MARITAL' THEN
748 FND_MESSAGE.SET_TOKEN('PLACE',IGF_AP_GEN.GET_LOOKUP_MEANING('IGF_AP_MAP_PROFILE','MARITAL_STATUS'));
749 ELSIF l_place = 'F_NAME' THEN
750 FND_MESSAGE.SET_TOKEN('PLACE',IGF_AP_GEN.GET_LOOKUP_MEANING('IGF_AP_MAP_PROFILE','FIRST_NAME'));
751 ELSIF l_place = 'L_NAME' THEN
752 FND_MESSAGE.SET_TOKEN('PLACE',IGF_AP_GEN.GET_LOOKUP_MEANING('IGF_AP_MAP_PROFILE','LAST_NAME'));
753
754 ELSIF l_place = 'SSN' THEN
755 OPEN c_get_tokens;
756 FETCH c_get_tokens INTO tokens_rec;
757 FND_MESSAGE.SET_TOKEN('PLACE',TRIM(tokens_rec.meaning));
758 CLOSE c_get_tokens;
759 END IF;
760
761 IGS_GE_MSG_STACK.ADD;
762
763 -- Bug #3536039. Raise exception IGFSEGEN001
764 IF p_call = 'LEGACY' THEN -- throw exception
765 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
766 RAISE IGFSEGEN001;
767 END IF;
768
769 IF p_call = 'JOB' THEN -- continue for next student dont stop
770 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
771 ELSE -- this means its called from FORM
772 APP_EXCEPTION.RAISE_EXCEPTION;
773 END IF;
774
775 WHEN OTHERS THEN
776 fnd_file.put_line(fnd_file.log,SQLERRM);
777 FND_MESSAGE.SET_NAME('IGF','IGF_GE_UNHANDLED_EXP');
778 FND_MESSAGE.SET_TOKEN('NAME','igf_se_gen_001.send_work_auth' || SQLERRM);
779 IGS_GE_MSG_STACK.ADD;
780
781 APP_EXCEPTION.RAISE_EXCEPTION;
782 END send_work_auth;
783
784
785 PROCEDURE send_work_auth_job(errbuf OUT NOCOPY VARCHAR2,
786 retcode OUT NOCOPY NUMBER,
787 p_awd_cal IN VARCHAR2,
788 p_fund_id IN igf_aw_fund_mast_all.fund_id%TYPE,
789 p_dummy IN NUMBER,
790 p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE
791 ) IS
792 ------------------------------------------------------------------------------------
793 -- Created by : ssawhney ( Oracle IDC)
794 -- Date created: 2nd jan
795 -- Purpose: This procedure will be used to create work authorizations for all student
796 -- for an award and for a term whose authorizations have not been created.
797 -- This will in turn be calling send_work_auth
798 --
799 -- Known limitations/enhancements and/or remarks:
800 -- Change History:
801 -- Who When What
802 --tsailaja 15/Jan/2006 Bug 4947880 Added invocation of igf_aw_gen.set_org_id(NULL);
803 --veramach July 2004 FA 151(HR integration) Added a new dummy parameter
804 --rasahoo 25/Aug/2003 #3101894 If the procedur is called from legacy process
805 -- and VISA details are not provided by the context person
806 -- It will create work authorization giving warning.
807 -- masehgal 228-dec-2002 # 2445830 Restricted process to pick up only accepted
808 -- awards. Changed call to display log
809 -- brajendr 20-May-2002 added 3 new parameters as per the bug # 2363887
810 -- Award Year, Fund_id, and Base Id
811 --
812 -- brajendr 28-Jun-2002 Added a new message called IGF_SE_WRK_ATUH_NO_MATCH
813 -- This message will be shown if there are no records to process
814 --
815 -------------------------------------------------------------------------------------
816
817 CURSOR c_auth(cp_awd_cal_type igs_ca_inst.cal_type%TYPE,
818 cp_awd_seq_no igs_ca_inst.sequence_number%TYPE,
819 cp_fund_id igf_aw_fund_mast_all.fund_id%TYPE,
820 cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE
821 ) IS
822 SELECT awd.award_id,
823 awd.fund_id fund_id,
824 fa.person_id person_id,
825 awd.base_id,
826 hz.party_number person_number,
827 fmast.fund_code
828 FROM igf_aw_award_all awd,
829 igf_aw_fund_mast_all fmast,
830 igf_aw_fund_cat_all fcat,
831 igf_ap_fa_base_rec_all fa,
832 hz_parties hz
833 WHERE fcat.fed_fund_code = 'FWS'
834 AND awd.award_status = 'ACCEPTED'
835 AND awd.fund_id = NVL(cp_fund_id, awd.fund_id)
836 AND awd.base_id = NVL(cp_base_id, awd.base_id)
837 AND fa.ci_cal_type = cp_awd_cal_type
838 AND fa.ci_sequence_number = cp_awd_seq_no
839 AND fmast.fund_id = awd.fund_id
840 AND fmast.fund_code = fcat.fund_code
841 AND awd.base_id = fa.base_id
842 AND fa.person_id = hz.party_id;
843
844 -- Check if authorization already exists
845 CURSOR c_auth_exists(
846 cp_award_id igf_aw_award_all.award_id%TYPE
847 ) IS
848 SELECT 'x'
849 FROM igf_se_auth
850 WHERE award_id = cp_award_id
851 AND flag = 'A';
852 l_auth_exists c_auth_exists%ROWTYPE;
853
854 -- Check if accepted amounts are equal at the award and authorization levels
855 CURSOR c_amounts(
856 cp_award_id igf_aw_award_all.award_id%TYPE
857 ) IS
858 SELECT awd.accepted_amt accepted_amt,
859 auth.accepted_amnt accepted_amnt
860 FROM igf_aw_award_all awd,
861 igf_se_auth auth
862 WHERE awd.award_id = auth.award_id
863 AND awd.award_id = cp_award_id
864 AND auth.flag = 'A';
865 l_amounts c_amounts%ROWTYPE;
866
867 l_ld_cal_type igs_ca_inst.cal_type%TYPE;
868 l_ld_seq_no igs_ca_inst.sequence_number%TYPE;
869 l_awd_cal_type igs_ca_inst.cal_type%TYPE;
870 l_awd_seq_no igs_ca_inst.sequence_number%TYPE;
871
872 auth_rec c_auth%ROWTYPE;
873 l_record VARCHAR2(50);
874
875 lb_rec_found BOOLEAN := FALSE;
876
877 BEGIN
878 igf_aw_gen.set_org_id(NULL);
879 -- capture the variables
880 l_awd_cal_type := LTRIM(RTRIM(SUBSTR(p_awd_cal,1,10)));
881 l_awd_seq_no := TO_NUMBER(SUBSTR(p_awd_cal,11));
882
883 -- set the flag to success
884 retcode :=0;
885 lb_rec_found := FALSE;
886
887 -- Print all the parameters passed for the Job
888 display_auth_params(l_awd_cal_type, l_awd_seq_no, p_fund_id, p_base_id);
889
890 OPEN c_auth (l_awd_cal_type, l_awd_seq_no, p_fund_id, p_base_id) ;
891 LOOP
892 FETCH c_auth INTO auth_rec;
893 EXIT WHEN c_auth%NOTFOUND;
894
895 IF (c_auth%FOUND) THEN
896 -- call the send auth procedure
897 -- display parameters
898 lb_rec_found := TRUE;
899 display_auth_process_log( auth_rec.person_number, auth_rec.fund_code, auth_rec.award_id);
900
901 l_auth_exists := NULL;
902 OPEN c_auth_exists(auth_rec.award_id);
903 FETCH c_auth_exists INTO l_auth_exists;
904 IF c_auth_exists%FOUND THEN
905 CLOSE c_auth_exists;
906
907 l_amounts := NULL;
908 OPEN c_amounts(auth_rec.award_id);
909 FETCH c_amounts INTO l_amounts;
910 CLOSE c_amounts;
911
912 IF l_amounts.accepted_amt = l_amounts.accepted_amnt THEN
913 --no need to recreate authorization
914 --log a message
915 fnd_message.set_name('IGF','IGF_SE_AUTH_GENERATED');
916 fnd_file.put_line(fnd_file.log,fnd_message.get);
917 ELSE
918 igf_se_gen_001.send_work_auth(
919 auth_rec.base_id,
920 auth_rec.person_id,
921 auth_rec.fund_id,
922 auth_rec.award_id,
923 NULL,
924 NULL,
925 'JOB'
926 );
927 END IF;
928 ELSE
929 CLOSE c_auth_exists;
930 --authorization not exists
931 igf_se_gen_001.send_work_auth(
932 auth_rec.base_id,
933 auth_rec.person_id,
934 auth_rec.fund_id,
935 auth_rec.award_id,
936 NULL,
937 NULL,
938 'JOB'
939 );
940 END IF;
941 END IF;
942 END LOOP;
943
944 -- close the loop
945 IF c_auth%ISOPEN THEN
946 CLOSE c_auth;
947 END IF;
948
949 IF lb_rec_found = FALSE THEN
950 FND_MESSAGE.SET_NAME('IGF','IGF_SE_WRK_ATUH_NO_MATCH');
951 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
952 END IF;
953 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
954
955 EXCEPTION
956 WHEN OTHERS THEN
957 ROLLBACK;
958 retcode:=2;
959 FND_MESSAGE.SET_TOKEN('NAME','igf_se_gen_001.send_work_auth_job');
960 IGS_GE_MSG_STACK.ADD;
961 errbuf := FND_MESSAGE.GET ;
962 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL ;
963 END send_work_auth_job;
964
965
966 PROCEDURE payroll_uplaod(errbuf OUT NOCOPY VARCHAR2,
967 retcode OUT NOCOPY NUMBER,
968 p_batch_id IN igf_se_payment_int.batch_id%TYPE,
969 p_auth_id IN igf_se_auth.auth_id%TYPE,
970 p_level IN VARCHAR2) IS
971 ------------------------------------------------------------------------------------
972 --Created by : ssawhney ( Oracle IDC)
973 --Date created: 2nd jan
974 --Purpose:
975 --
976 --Known limitations/enhancements and/or remarks:
977 --Change History:
978 --Who When What
979 --tsailaja 15/Jan/2006 Bug 4947880 Added invocation of igf_aw_gen.set_org_id(NULL);
980 --brajendr 12-Jun-2002 Modified the log messages
981 -- Added check to by pass the validation of not null columns
982 -------------------------------------------------------------------------------------
983
984 -- if auth_id is NULL then take all records for the corresponding batch_id
985 CURSOR c_payment(cv_batch_id igf_se_payment_int.batch_id%TYPE,
986 cv_auth_id igf_se_auth.auth_id%TYPE) IS
987 SELECT rowid, sei.*
988 FROM IGF_SE_PAYMENT_INT sei
989 WHERE sei.batch_id = cv_batch_id
990 AND sei.auth_id = NVL(cv_auth_id,sei.auth_id)
991 AND sei.status IN ('NEW', 'UPLOAD')
992 ORDER BY auth_id, person_id
993 FOR UPDATE NOWAIT;
994
995 CURSOR c_auth_count(cv_auth_id igf_se_auth.auth_id%TYPE,
996 cv_person_id hz_parties.party_id%TYPE) IS
997 SELECT COUNT(*) count
998 FROM igf_se_auth c,igf_aw_award a, igf_ap_fa_base_rec b
999 WHERE c.auth_id = cv_auth_id
1000 AND c.award_id = a.award_id
1001 AND a.base_id = b.base_id
1002 AND b.person_id = cv_person_id
1003 AND flag = 'A';
1004
1005 CURSOR c_get_se_errors(c_error_cd igf_se_payment_int.error_code%TYPE) IS
1006 SELECT meaning
1007 FROM igf_lookups_view
1008 WHERE lookup_type = 'IGF_STUD_EMP_ERROR'
1009 AND lookup_code = c_error_cd;
1010
1011 CURSOR cur_pymt_int IS
1012 SELECT pint.status, pint.error_code, hz.party_number, pint.auth_id
1013 FROM igf_se_payment_int pint, hz_parties hz
1014 WHERE pint.status IN ('DONE','ERROR')
1015 AND pint.person_id = hz.party_id
1016 AND pint.batch_id = p_batch_id;
1017
1018 payment_rec c_payment%ROWTYPE;
1019 l_source igf_se_payment.source%TYPE;
1020 l_error_cd igf_se_payment_int.error_code%TYPE DEFAULT NULL;
1021 l_transaction_id igf_se_payment_int.transaction_id%TYPE;
1022 l_batch_id igf_se_payment_int.batch_id%TYPE;
1023 l_auth_id igf_se_payment_int.auth_id%TYPE;
1024 l_ld_cal_type igs_ca_inst.cal_type%TYPE;
1025 l_ld_sequence_number igs_ca_inst.sequence_number%TYPE;
1026 l_error_meaming igf_lookups_view.meaning%TYPE;
1027 l_level VARCHAR2(1);
1028 l_auth_count NUMBER(2) DEFAULT 0;
1029 l_rowid VARCHAR2(30);
1030 skip_record EXCEPTION;
1031 l_rec_count NUMBER DEFAULT 0;
1032
1033 PROCEDURE update_record(payment_rec IN c_payment%ROWTYPE,
1034 p_error_cd IN igf_se_payment_int.error_code%TYPE) IS
1035 --------------------------------------------------------
1036 --Created by : ssawhney on 2nd Jan
1037 --Purpose : local procedure to update IGF_SE_PAYMENT_INT based on p_error_cd
1038 --Change History :
1039 --Who When What
1040 ----------------------------------------------------------------
1041
1042
1043 l_error_cd igf_se_payment_int.error_code%TYPE;
1044 l_status igf_se_payment_int.status%TYPE;
1045 l_source igf_se_payment.source%TYPE;
1046
1047 BEGIN
1048
1049 -- update is being called 2 times, if the error code passed is NULL then
1050 -- it means the record was successfully passed from PAYMENT_INT to PAYMENT table.
1051 l_error_cd := p_error_cd;
1052 IF l_error_cd IS NULL THEN
1053 l_status :='DONE';
1054 ELSIF l_error_cd IS NOT NULL THEN
1055 l_status :='ERROR';
1056 END IF;
1057
1058 igf_se_payment_int_pkg.update_row(
1059 x_rowid => payment_rec.rowid,
1060 x_transaction_id => payment_rec.transaction_id,
1061 x_batch_id => payment_rec.batch_id,
1062 x_payroll_id => payment_rec.payroll_id,
1063 x_payroll_date => payment_rec.payroll_date,
1064 x_auth_id => payment_rec.auth_id,
1065 x_person_id => payment_rec.person_id,
1066 x_fund_id => payment_rec.fund_id,
1067 x_paid_amount => payment_rec.paid_amount,
1068 x_org_unit_cd => payment_rec.org_unit_cd,
1069 x_status => l_status,
1070 x_error_code => l_error_cd
1071 );
1072
1073 EXCEPTION
1074 WHEN OTHERS THEN
1075 FND_MESSAGE.SET_NAME('IGF','IGF_GE_UNHANDLED_EXP');
1076 FND_MESSAGE.SET_TOKEN('NAME','igf_se_gen_001.payroll_upload:igf_se_payment_int.update');
1077 FND_FILE.PUT_LINE(FND_FILE.LOG,fnd_message.get);
1078 IGS_GE_MSG_STACK.ADD;
1079 -- app_exception.raise_exception;
1080
1081 END update_record;
1082
1083 -- begin main procedure
1084 BEGIN
1085 igf_aw_gen.set_org_id(NULL);
1086
1087 l_batch_id := p_batch_id ;
1088 l_auth_id := p_auth_id;
1089 l_level := p_level;
1090
1091 -- set the flag to success
1092 retcode:=0;
1093 l_rec_count := 0;
1094
1095 -- Display all the passed Paramters
1096 display_payroll_params(l_batch_id, l_auth_id, l_level);
1097
1098 -- batch_id is mandatory parameter.
1099 IF l_batch_id IS NULL THEN
1100 FND_MESSAGE.SET_NAME('IGF','IGF_AW_PARAM_ERR');
1101 FND_FILE.PUT_LINE(FND_FILE.LOG,fnd_message.get);
1102 IGS_GE_MSG_STACK.ADD;
1103 APP_EXCEPTION.RAISE_EXCEPTION;
1104 END IF;
1105
1106 -- loop for all records. If auth_id is present then there will be only one record with the combination
1107 FOR payment_rec IN c_payment (l_batch_id,l_auth_id)
1108 LOOP
1109 l_rec_count := l_rec_count + 1;
1110 BEGIN
1111 -- validate the auth and person combination
1112 OPEN c_auth_count(payment_rec.auth_id,payment_rec.person_id);
1113 FETCH c_auth_count INTO l_auth_count;
1114 CLOSE c_auth_count;
1115 IF (l_auth_count > 0) THEN -- authorization id passed exists for a person.
1116 l_source :='UPLOAD';
1117 -- move record from SE_PAYMENT_INT to SE_PAYMENT once the validation was done
1118 BEGIN
1119 SAVEPOINT se_adjust;
1120 -- validate threshold will not be called from here.
1121 igf_se_payment_pkg.insert_row(
1122 x_rowid => l_rowid,
1123 x_transaction_id => l_transaction_id,
1124 x_payroll_id => payment_rec.payroll_id,
1125 x_payroll_date => payment_rec.payroll_date,
1126 x_auth_id => payment_rec.auth_id,
1127 x_person_id => payment_rec.person_id,
1128 x_fund_id => payment_rec.fund_id,
1129 x_paid_amount => payment_rec.paid_amount,
1130 x_org_unit_cd => payment_rec.org_unit_cd,
1131 x_source => l_source
1132 );
1133 EXCEPTION
1134 WHEN OTHERS THEN
1135 fnd_file.put_line(fnd_file.log,fnd_message.get);
1136 ROLLBACK TO se_adjust;
1137 l_error_cd := 'SE005';
1138 update_record(payment_rec,l_error_cd);
1139 RAISE SKIP_RECORD;
1140 END;
1141 ELSE -- l_auth_count < 0
1142 l_error_cd :='SE006';
1143 update_record(payment_rec,l_error_cd);
1144 END IF; -- l_auth_count >0
1145 EXCEPTION -- handle user raised exception
1146 WHEN skip_record THEN
1147 NULL;
1148 END;
1149 END LOOP; --payment_rec
1150
1151 -- Purge the records now which are already moved successfully
1152 -- And also Log the relavent messages
1153 BEGIN
1154
1155 FOR cur_pymt_int_rec IN cur_pymt_int LOOP
1156 IF cur_pymt_int_rec.status = 'DONE' THEN
1157 FND_MESSAGE.SET_NAME('IGF','IGF_SE_SUCCESS');
1158 FND_MESSAGE.SET_TOKEN('NUMBER',cur_pymt_int_rec.party_number);
1159 FND_MESSAGE.SET_TOKEN('AUTHID',cur_pymt_int_rec.auth_id);
1160 FND_FILE.PUT_LINE(FND_FILE.LOG,fnd_message.get);
1161
1162 ELSIF cur_pymt_int_rec.status = 'ERROR' THEN
1163 OPEN c_get_se_errors( cur_pymt_int_rec.error_code);
1164 FETCH c_get_se_errors INTO l_error_meaming;
1165 CLOSE c_get_se_errors;
1166
1167 FND_MESSAGE.SET_NAME('IGF','IGF_SE_NOT_SUCCESS');
1168 FND_MESSAGE.SET_TOKEN('NUMBER',cur_pymt_int_rec.party_number);
1169 FND_MESSAGE.SET_TOKEN('AUTHID',cur_pymt_int_rec.auth_id);
1170 FND_MESSAGE.SET_TOKEN('ERROR',l_error_meaming);
1171 FND_FILE.PUT_LINE(FND_FILE.LOG,fnd_message.get);
1172
1173 END IF;
1174 END LOOP;
1175
1176 DELETE FROM igf_se_payment_int
1177 WHERE status = 'DONE'
1178 AND batch_id = l_batch_id;
1179
1180 EXCEPTION
1181 WHEN OTHERS THEN
1182 FND_MESSAGE.SET_NAME('IGF','IGF_GE_UNHANDLED_EXP');
1183 FND_MESSAGE.SET_TOKEN('NAME','igf_se_gen_001.payroll_upload: delete record');
1184 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
1185 IGS_GE_MSG_STACK.ADD;
1186 END;
1187
1188 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
1189
1190 IF l_rec_count = 0 THEN
1191 -- There are no Records to process
1192 FND_MESSAGE.SET_NAME('IGS','IGS_UC_HE_NO_DATA');
1193 FND_FILE.PUT_LINE(FND_FILE.LOG,fnd_message.get);
1194
1195 ELSE
1196 -- Total Records Processed : RCOUNT
1197 FND_MESSAGE.SET_NAME('IGS','IGS_AD_TOT_REC_PRC');
1198 FND_MESSAGE.SET_TOKEN('RCOUNT',l_rec_count);
1199 FND_FILE.PUT_LINE(FND_FILE.LOG,fnd_message.get);
1200
1201 END IF;
1202 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
1203
1204 EXCEPTION -- main exception handling
1205 WHEN OTHERS THEN
1206 ROLLBACK ;
1207 retcode:=2;
1208 FND_MESSAGE.SET_TOKEN('NAME','igf_se_gen_001.payroll_upload');
1209 IGS_GE_MSG_STACK.ADD;
1210 errbuf := FND_MESSAGE.GET ;
1211 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL ;
1212
1213 END payroll_uplaod;
1214
1215
1216 PROCEDURE payroll_adjust(
1217 p_payment_rec IN igf_se_payment%ROWTYPE,
1218 p_status OUT NOCOPY igf_se_payment_int.status%TYPE,
1219 p_error_cd OUT NOCOPY igf_se_payment_int.error_code%TYPE
1220 )IS
1221 ------------------------------------------------------------------------------------
1222 -- Created by : ssawhney ( Oracle IDC)
1223 -- Date created: 2nd jan
1224 -- Purpose: This procedure will be used to adjust the payroll amount into IGF_AW_AWD_DISB
1225 -- This will be used at the time of moving record from IGF_SE_PAYMENT_INT to IGF_SE_PAYMENT
1226 -- Hence it will be present in the TBH of IGF_SE_PAYMENT
1227 -- Known limitations/enhancements and/or remarks:
1228 -- Change History:
1229 -- Who When What
1230 --veramach July 2004 bug 3709292 FA 151 HR integration changes
1231 -- brajendr 18-Jul-2002 Bug # 2450456
1232 -- Added a check for negative adjustments
1233 -------------------------------------------------------------------------------------
1234 l_auth_id igf_se_auth.auth_id%TYPE;
1235 l_payroll_date igf_se_payment_int.payroll_date%TYPE;
1236 l_paid_amount igf_se_payment_int.paid_amount%TYPE;
1237 l_status igf_se_payment_int.status%TYPE;
1238 l_error_cd igf_se_payment_int.error_code%TYPE;
1239 l_disb_amount igf_aw_awd_disb.disb_accepted_amt%TYPE DEFAULT 0;
1240 l_sys_awd_status igf_ap_batch_aw_map.award_year_status_code%TYPE;
1241
1242 CURSOR c_auth (cv_auth_id igf_se_auth.auth_id%TYPE) IS
1243 SELECT rowid row_id,auth.*
1244 FROM igf_se_auth auth
1245 WHERE auth_id = cv_auth_id
1246 AND flag='A';
1247
1248 CURSOR c_award_det(cp_cal_type VARCHAR2,
1249 cp_seq_number NUMBER) IS
1250 SELECT award_year_status_code
1251 FROM igf_ap_batch_aw_map
1252 WHERE ci_cal_type = cp_cal_type
1253 AND ci_sequence_number = cp_seq_number;
1254
1255 CURSOR c_sum(
1256 cv_auth_id igf_se_auth.auth_id%TYPE
1257 ) IS
1258 SELECT NVL(accepted_amt,NVL(offered_amt,0)) accepted_amt,
1259 base_id
1260 FROM igf_aw_award_all awd,
1261 igf_se_auth se
1262 WHERE se.award_id = awd.award_id
1263 AND se.auth_id = cv_auth_id
1264 AND se.flag = 'A';
1265
1266
1267 CURSOR c_award(
1268 cp_auth_id igf_se_auth.auth_id%TYPE
1269 ) IS
1270 SELECT awd.*
1271 FROM igf_aw_award awd,
1272 igf_se_auth auth
1273 WHERE auth.auth_id = cp_auth_id
1274 AND auth.award_id = awd.award_id;
1275
1276 CURSOR c_payment_total(cv_auth_id igf_se_auth.auth_id%TYPE) IS
1277 SELECT SUM( NVL(paid_amount, 0))
1278 FROM igf_se_payment
1279 WHERE auth_id = cv_auth_id;
1280
1281 sum_rec c_sum%ROWTYPE;
1282 l_award c_award%ROWTYPE;
1283 auth_rec c_auth%ROWTYPE;
1284 l_fund_id igf_aw_fund_mast.fund_id%TYPE;
1285 l_person_id igs_pe_person.person_id%TYPE;
1286 ln_total_paid_amount igf_se_payment.paid_amount%TYPE;
1287 l_notification_date igf_se_auth.notification_date%TYPE;
1288
1289 -----------local function begin
1290 FUNCTION validate_threshold(
1291 p_fund_id IN igf_aw_fund_mast.fund_id%TYPE,
1292 p_paid_amount IN igf_aw_awds_sum_v.paid_amt%TYPE,
1293 p_accepted_amt IN igf_aw_awds_sum_v.accepted_amt%TYPE
1294 ) RETURN BOOLEAN IS
1295 ------------------------------------------------------------------------------------
1296 --Created by : ssawhney ( Oracle IDC) Date created: 2nd jan
1297 --Purpose: Local function : This function will be called to validate if the Paid amount for the fund
1298 -- has reached its threshold limits as set in the FUND MASTER.
1299 --
1300 --Change History:
1301 --Who When What
1302 --masehgal 26-Dec-2002 # 2516712 Copied Trunc( Sysdate) in notification date
1303 -- to resolve locking error.
1304 -------------------------------------------------------------------------------------
1305
1306 CURSOR c_fund ( cv_fund_id igf_aw_fund_mast.fund_id%TYPE) IS
1307 SELECT threshold_perct, threshold_value
1308 FROM igf_aw_fund_mast
1309 WHERE fund_id = cv_fund_id;
1310
1311 fund_rec c_fund%ROWTYPE;
1312 l_fund_id igf_aw_fund_mast.fund_id%TYPE;
1313 l_paid_amount NUMBER;
1314 l_perct NUMBER(5,2);
1315 l_accepted_amt igf_aw_awds_sum_v.accepted_amt%TYPE;
1316
1317 BEGIN
1318
1319 l_fund_id := p_fund_id;
1320 l_paid_amount := p_paid_amount ;
1321 l_accepted_amt := p_accepted_amt;
1322
1323 -- get fund setup details. If no data found error with the Fund details
1324
1325 OPEN c_fund (l_fund_id);
1326 FETCH c_fund INTO fund_rec;
1327 IF c_fund%NOTFOUND THEN
1328 CLOSE c_fund;
1329 RETURN FALSE;
1330 END IF;
1331 CLOSE c_fund;
1332
1333 -- validate the threshold if threshold value is present
1334 IF fund_rec.threshold_value IS NOT NULL THEN
1335 IF l_paid_amount >= fund_rec.threshold_value THEN
1336 RETURN TRUE;
1337 END IF;
1338
1339 -- validate the threshold if threshold percent is present
1340 ELSIF fund_rec.threshold_perct IS NOT NULL THEN
1341 BEGIN
1342 l_perct := ROUND(l_paid_amount/l_accepted_amt)*100;
1343 IF l_perct >= ROUND(fund_rec.threshold_perct) THEN
1344 RETURN TRUE;
1345 END IF;
1346 EXCEPTION
1347 WHEN ZERO_DIVIDE THEN
1348 -- there can be a condition where the accepted amount is 0 and we get error.
1349 RETURN FALSE;
1350 END;
1351 END IF;
1352
1353 RETURN FALSE;
1354
1355 EXCEPTION
1356 WHEN OTHERS THEN
1357 RETURN FALSE;
1358
1359 END validate_threshold;
1360
1361 -----------local function end
1362 BEGIN
1363
1364 l_auth_id := p_payment_rec.auth_id;
1365 l_fund_id := p_payment_rec.fund_id;
1366 l_person_id := p_payment_rec.person_id;
1367 l_paid_amount := NVL(p_payment_rec.paid_amount,0);
1368 l_notification_date := NULL;
1369
1370 -- set OUT NOCOPY variables as TRUE.
1371 p_error_cd := NULL;
1372 p_status := 'DONE';
1373
1374 OPEN c_auth(l_auth_id);
1375 FETCH c_auth INTO auth_rec;
1376 IF c_auth%NOTFOUND THEN
1377 p_error_cd := 'SE006';
1378 p_status := 'ERROR';
1379 CLOSE c_auth;
1380 RETURN;
1381 -- app_exception.raise_exception;
1382 END IF;
1383 CLOSE c_auth;
1384
1385 OPEN c_sum(l_auth_id);
1386 FETCH c_sum INTO sum_rec;
1387 IF c_sum%NOTFOUND THEN
1388 p_error_cd := 'SE008';
1389 p_status := 'ERROR';
1390 CLOSE c_sum;
1391 RETURN;
1392 END IF;
1393 CLOSE c_sum;
1394
1395 -- Check the total payment amount for an auth id and if it is less than zero then show an errror to user
1396 ln_total_paid_amount := 0;
1397 OPEN c_payment_total(p_payment_rec.auth_id);
1398 FETCH c_payment_total INTO ln_total_paid_amount;
1399 CLOSE c_payment_total;
1400
1401 -- compare the payroll amount and the accepted amount by the student for the fund
1402 -- in the award year in that term. The payroll amount should be less than the accepted amount.
1403 IF ln_total_paid_amount > sum_rec.accepted_amt THEN
1404 p_error_cd := 'SE009';
1405 p_status := 'ERROR';
1406 RETURN;
1407
1408 ELSIF l_paid_amount < 0 AND ln_total_paid_amount < 0 THEN
1409 p_error_cd := 'SE012';
1410 p_status := 'ERROR';
1411 RETURN;
1412
1413 END IF;
1414
1415 -- check up for threshold limits
1416 IF validate_threshold (l_fund_id,ln_total_paid_amount,sum_rec.accepted_amt) THEN
1417 l_notification_date := TRUNC(SYSDATE);
1418 END IF;
1419
1420 --update the paid amount
1421 BEGIN
1422 OPEN c_award(l_auth_id);
1423 FETCH c_award INTO l_award;
1424 CLOSE c_award;
1425
1426 igf_aw_award_pkg.update_row(
1427 x_rowid => l_award.row_id,
1428 x_award_id => l_award.award_id,
1429 x_fund_id => l_award.fund_id,
1430 x_base_id => l_award.base_id,
1431 x_offered_amt => l_award.offered_amt,
1432 x_accepted_amt => l_award.accepted_amt,
1433 x_paid_amt => ln_total_paid_amount,
1434 x_packaging_type => l_award.packaging_type,
1435 x_batch_id => l_award.batch_id,
1436 x_manual_update => l_award.manual_update,
1437 x_rules_override => l_award.rules_override,
1438 x_award_date => l_award.award_date,
1439 x_award_status => l_award.award_status,
1440 x_attribute_category => l_award.attribute_category,
1441 x_attribute1 => l_award.attribute1,
1442 x_attribute2 => l_award.attribute2,
1443 x_attribute3 => l_award.attribute3,
1444 x_attribute4 => l_award.attribute4,
1445 x_attribute5 => l_award.attribute5,
1446 x_attribute6 => l_award.attribute6,
1447 x_attribute7 => l_award.attribute7,
1448 x_attribute8 => l_award.attribute8,
1449 x_attribute9 => l_award.attribute9,
1450 x_attribute10 => l_award.attribute10,
1451 x_attribute11 => l_award.attribute11,
1452 x_attribute12 => l_award.attribute12,
1453 x_attribute13 => l_award.attribute13,
1454 x_attribute14 => l_award.attribute14,
1455 x_attribute15 => l_award.attribute15,
1456 x_attribute16 => l_award.attribute16,
1457 x_attribute17 => l_award.attribute17,
1458 x_attribute18 => l_award.attribute18,
1459 x_attribute19 => l_award.attribute19,
1460 x_attribute20 => l_award.attribute20,
1461 x_rvsn_id => l_award.rvsn_id,
1462 x_alt_pell_schedule => l_award.alt_pell_schedule,
1463 x_mode => 'R',
1464 x_award_number_txt => l_award.award_number_txt,
1465 x_legacy_record_flag => l_award.legacy_record_flag,
1466 x_adplans_id => l_award.adplans_id,
1467 x_lock_award_flag => l_award.lock_award_flag,
1468 x_app_trans_num_txt => l_award.app_trans_num_txt,
1469 x_awd_proc_status_code => l_award.awd_proc_status_code,
1470 x_notification_status_code => l_award.notification_status_code,
1471 x_notification_status_date => l_award.notification_status_date,
1472 x_publish_in_ss_flag => l_award.publish_in_ss_flag
1473 );
1474 -- reset the variables
1475 l_disb_amount:=0;
1476 EXCEPTION
1477 WHEN OTHERS THEN
1478 p_error_cd := 'SE007';
1479 p_status := 'ERROR';
1480 RETURN;
1481 --app_exception.raise_exception;
1482 -- reset the variables
1483 l_disb_amount :=0;
1484 END;
1485
1486 -- call Notification
1487 IF l_notification_date IS NOT NULL AND TRUNC(l_notification_date) = TRUNC(SYSDATE) THEN
1488 -- Initializing Award status
1489 l_sys_awd_status := 'LD';
1490
1491 OPEN c_award_det(auth_rec.aw_cal_type,auth_rec.aw_sequence_number);
1492 FETCH c_award_det INTO l_sys_awd_status;
1493 CLOSE c_award_det;
1494
1495 IF l_sys_awd_status = 'O' THEN
1496 igf_se_gen_001.se_notify (l_person_id, l_fund_id,NULL,NULL,auth_rec.award_id);
1497 igf_se_auth_pkg.update_row(
1498 x_rowid => auth_rec.row_id,
1499 x_sequence_no => auth_rec.sequence_no,
1500 x_auth_id => auth_rec.auth_id,
1501 x_flag => auth_rec.flag,
1502 x_person_id => auth_rec.person_id,
1503 x_first_name => auth_rec.first_name,
1504 x_last_name => auth_rec.last_name,
1505 x_address1 => auth_rec.address1,
1506 x_address2 => auth_rec.address2,
1507 x_address3 => auth_rec.address3,
1508 x_address4 => auth_rec.address4,
1509 x_city => auth_rec.city,
1510 x_state => auth_rec.state,
1511 x_province => auth_rec.province,
1512 x_county => auth_rec.county,
1513 x_country => auth_rec.country,
1514 x_sex => auth_rec.sex,
1515 x_birth_dt => auth_rec.birth_dt,
1516 x_ssn_no => auth_rec.ssn_no,
1517 x_marital_status => auth_rec.marital_status,
1518 x_visa_type => auth_rec.visa_type,
1519 x_visa_category => auth_rec.visa_category,
1520 x_visa_number => auth_rec.visa_number,
1521 x_visa_expiry_dt => auth_rec.visa_expiry_dt,
1522 x_entry_date => auth_rec.entry_date,
1523 x_fund_id => auth_rec.fund_id,
1524 x_threshold_perct => auth_rec.threshold_perct,
1525 x_threshold_value => auth_rec.threshold_value,
1526 x_accepted_amnt => auth_rec.accepted_amnt,
1527 x_aw_cal_type => auth_rec.aw_cal_type,
1528 x_aw_sequence_number => auth_rec.aw_sequence_number,
1529 x_mode => 'R',
1530 x_award_id => auth_rec.award_id,
1531 x_authorization_date => auth_rec.authorization_date,
1532 x_notification_date => l_notification_date
1533 );
1534 END IF;
1535 END IF;
1536
1537
1538 -- set OUT NOCOPY variables as TRUE.
1539 p_error_cd := NULL;
1540 p_status := 'DONE';
1541
1542
1543 EXCEPTION
1544 WHEN OTHERS THEN
1545 IF p_error_cd IS NULL THEN
1546 p_error_cd := 'SE007';
1547 END IF;
1548
1549 p_status := 'ERROR';
1550 -- app_exception.raise_exception;
1551 END payroll_adjust;
1552
1553
1554 PROCEDURE se_notify(p_person_id IN hz_parties.party_id%TYPE,
1555 p_fund_id IN igf_aw_fund_mast.fund_id%TYPE,
1556 p_ld_cal_type IN igs_ca_inst.cal_type%TYPE,
1557 p_ld_seq_no IN igs_ca_inst.sequence_number%TYPE,
1558 p_award_id IN igf_aw_award_all.award_id%TYPE
1559 ) IS
1560 ------------------------------------------------------------------------------------
1561 --Created by : ssawhney ( Oracle IDC)
1562 --Date created: 2nd jan
1563 --Purpose: This procedure will be used to generate Work Flow notifications for the
1564 -- concerned records, which exceed threshold payment limits.
1565 --
1566 --Known limitations/enhancements and/or remarks:
1567 --Change History:
1568 --Who When What
1569 --veramach July 2004 FA 151 HR integration - process raises a business event instead of initiating workflow
1570 -------------------------------------------------------------------------------------
1571 CURSOR c_person IS
1572 SELECT fa.person_number, fa.full_name
1573 FROM igs_pe_person_base_v fa
1574 WHERE fa.person_id = p_person_id ;
1575
1576 CURSOR c_fund IS
1577 SELECT fund_code,threshold_perct,threshold_value
1578 FROM igf_aw_fund_mast
1579 WHERE fund_id = p_fund_id;
1580
1581 CURSOR c_earned_amount IS
1582 SELECT paid_amt
1583 FROM igf_se_work_awd_prg_v
1584 WHERE award_id=p_award_id;
1585
1586 person_rec c_person%ROWTYPE ;
1587 fund_rec c_fund%ROWTYPE;
1588 earned_amt_rec c_earned_amount%ROWTYPE;
1589
1590 l_seq_val NUMBER;
1591 l_wf_installed fnd_lookups.lookup_code%TYPE;
1592
1593 l_wf_event_t WF_EVENT_T;
1594 l_wf_parameter_list_t WF_PARAMETER_LIST_T;
1595
1596 BEGIN
1597
1598 -- get the profile value that is set for checking if workflow is installed
1599 fnd_profile.get('IGS_WF_ENABLE',l_wf_installed);
1600
1601 -- if workflow is installed then carry on with the sending notification
1602 IF NVL(RTRIM(l_wf_installed),'Y') ='Y' THEN
1603
1604 -- fetch data from all the cursors
1605 OPEN c_person;
1606 FETCH c_person INTO person_rec;
1607 CLOSE c_person;
1608
1609 OPEN c_fund;
1610 FETCH c_fund INTO fund_rec;
1611 CLOSE c_fund;
1612
1613 OPEN c_earned_amount;
1614 FETCH c_earned_amount INTO earned_amt_rec;
1615 CLOSE c_earned_amount;
1616
1617 -- Getting a unique number from the sequence
1618 -- using a IGS_PE sequence for this.
1619
1620 SELECT igs_pe_res_chg_s.nextval INTO l_seq_val from DUAL;
1621
1622 -- Initialize the wf_event_t object
1623 WF_EVENT_T.Initialize(l_wf_event_t);
1624
1625 -- Set the event name
1626 l_wf_event_t.setEventName(pEventName => 'oracle.apps.igf.se.earnings.limit.reached');
1627
1628 -- Set the event key
1629 l_wf_event_t.setEventKey(
1630 pEventKey => 'oracle.apps.igf.se.earnings.limit.reached' || l_seq_val
1631 );
1632
1633 -- Set the parameter list
1634 l_wf_event_t.setParameterList(
1635 pParameterList => l_wf_parameter_list_t
1636 );
1637
1638 -- Pass Person Number, Person Name, Fund Code, Earned Amount and threshold percentage or value that is marked at the Fund Level as the event parameters
1639 fnd_message.set_name('IGF','IGF_SE_MSG_SUBJ');
1640 wf_event.addparametertolist(
1641 p_name => 'SUBJECT',
1642 p_value => fnd_message.get,
1643 p_parameterlist => l_wf_parameter_list_t
1644 );
1645
1646 -- Get the body of the mail from fnd_new_messages and assign it to the attribute defined in the workflow definition
1647 fnd_message.set_name('IGF','IGF_AP_SAP_MSG_SUBJ');
1648 wf_event.addparametertolist(
1649 p_name => 'MESSGAE_BODY',
1650 p_value => fnd_message.get,
1651 p_parameterlist => l_wf_parameter_list_t
1652 );
1653
1654
1655 wf_event.addparametertolist(
1656 p_name => 'PERSON_NUMBER',
1657 p_value => person_rec.person_number,
1658 p_parameterlist => l_wf_parameter_list_t
1659 );
1660
1661 wf_event.addparametertolist(
1662 p_name => 'NAME',
1663 p_value => person_rec.full_name,
1664 p_parameterlist => l_wf_parameter_list_t
1665 );
1666
1667 wf_event.addparametertolist(
1668 p_name => 'FUND_CODE',
1669 p_value => fund_rec.fund_code,
1670 p_parameterlist => l_wf_parameter_list_t
1671 );
1672
1673 wf_event.addparametertolist(
1674 p_name => 'EARNED_AMOUNT',
1675 p_value => earned_amt_rec.paid_amt,
1676 p_parameterlist => l_wf_parameter_list_t
1677 );
1678
1679 IF fund_rec.threshold_perct IS NOT NULL THEN
1680 wf_event.addparametertolist(
1681 p_name => 'THRESHOLD_PERCT',
1682 p_value => fund_rec.threshold_perct,
1683 p_parameterlist => l_wf_parameter_list_t
1684 );
1685 ELSE
1686 wf_event.addparametertolist(
1687 p_name => 'THRESHOLD_VALUE',
1688 p_value => fund_rec.threshold_value,
1689 p_parameterlist => l_wf_parameter_list_t
1690 );
1691 END IF;
1692 wf_Event.raise(
1693 p_event_name => 'oracle.apps.igf.se.earnings.limit.reached',
1694 p_event_key => 'oracle.apps.igf.se.earnings.limit.reached' || l_seq_val,
1695 p_parameters => l_wf_parameter_list_t
1696 );
1697
1698 END IF;
1699
1700 EXCEPTION
1701 WHEN OTHERS THEN
1702 FND_MESSAGE.SET_NAME('IGF','IGF_GE_UNHANDLED_EXP');
1703 FND_MESSAGE.SET_TOKEN('NAME','igf_se_gen_001.se_notify');
1704 IGS_GE_MSG_STACK.ADD;
1705 APP_EXCEPTION.RAISE_EXCEPTION;
1706 END se_notify;
1707
1708 END igf_se_gen_001;
[Home] [Help]