[Home] [Help]
59: || kumma Replaced the ref cursor type validatecur with 2 normal cursors
60: || validate_teach_cur and validate_cal , bug # 2423670
61: || kumma 16-JUN-2002 Added validations for START_DATE to be not null, bug # 2423988
62: || gmaheswa 1-nov-2004 calender type and sequence number can be of active/future load calender.
63: || Obsoleted start_date and end_date columns of igs_pe_housing_int and igs_pe_teach_periods_all
64: || Modified the duplicated record check to be based on cal_type and sequence_number of a person.
65: */
66:
67: l_rule VARCHAR2(1);
64: || Modified the duplicated record check to be based on cal_type and sequence_number of a person.
65: */
66:
67: l_rule VARCHAR2(1);
68: l_error_code igs_pe_housing_int.error_code%TYPE;
69: l_status igs_pe_housing_int.status%TYPE;
70: l_processed_records NUMBER(5) := 0;
71:
72:
65: */
66:
67: l_rule VARCHAR2(1);
68: l_error_code igs_pe_housing_int.error_code%TYPE;
69: l_status igs_pe_housing_int.status%TYPE;
70: l_processed_records NUMBER(5) := 0;
71:
72:
73: l_prog_label VARCHAR2(100);
79:
80: --Pick up the records for processing from the Housing Status Interface Table
81: CURSOR housing_cur(cp_interface_run_id igs_ad_interface_all.interface_run_id%TYPE) IS
82: SELECT ai.*, i.person_id
83: FROM igs_pe_housing_int ai, igs_ad_interface_all i
84: WHERE ai.interface_id = i.interface_id AND
85: ai.status = '2' AND
86: i.interface_run_id = cp_interface_run_id AND
87: ai.interface_run_id = cp_interface_run_id;
104: p_housing_rec IN housing_cur%ROWTYPE
105: ) AS
106: l_rowid VARCHAR2(25);
107: l_teaching_period_id igs_pe_teach_periods_all.teaching_period_id%TYPE;
108: l_error_code igs_pe_housing_int.error_code%TYPE;
109: l_org_id NUMBER(15);
110: BEGIN
111: -- Call Log header
112: IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
136: x_mode => 'R',
137: x_org_id => l_org_id
138: );
139: l_error_code := NULL;
140: UPDATE igs_pe_housing_int
141: SET status = '1',
142: error_code = l_error_code
143: WHERE interface_housing_id = p_housing_rec.interface_housing_id;
144:
145: EXCEPTION
146: WHEN OTHERS THEN
147: l_error_code := 'E109'; -- Person Housing Status Insertion Failed
148:
149: UPDATE igs_pe_housing_int
150: SET status = '3',
151: error_code = l_error_code
152: WHERE interface_housing_id = p_housing_rec.interface_housing_id;
153:
202: x_sequence_number => NVL(p_dup_rec.sequence_number,housing_rec.sequence_number),
203: x_mode => 'R'
204: );
205:
206: UPDATE igs_pe_housing_int
207: SET status = '1',
208: error_code = NULL,
209: match_ind = '18' -- '18' Match occured and used import values
210: WHERE interface_housing_id = p_housing_rec.interface_housing_id;
211:
212: EXCEPTION
213: WHEN OTHERS THEN
214:
215: UPDATE igs_pe_housing_int
216: SET status = '3',
217: error_code = 'E114'
218: WHERE interface_housing_id = p_housing_rec.interface_housing_id;
219:
269: CA.CAL_TYPE = c_cal_type AND
270: CA.SEQUENCE_NUMBER = c_seq_number;
271:
272:
273: l_error_code igs_pe_housing_int.error_code%TYPE;
274: l_rec VARCHAR2(1);
275: BEGIN
276: -- TEACH_PERIOD_RESID_STAT_CD
277: IF NOT(igs_pe_pers_imp_001.validate_lookup_type_code('PE_TEA_PER_RES',p_housing_rec.teach_period_resid_stat_cd,8405))
315:
316: RETURN TRUE;
317: EXCEPTION
318: WHEN OTHERS THEN
319: UPDATE igs_pe_housing_int
320: SET status = '3',
321: error_code = l_error_code
322: WHERE interface_housing_id = p_housing_rec.interface_housing_id;
323:
364:
365: -- 1. If the rule is E or I, and the match ind column is not null, update all the records to status 3 as they are invalids.
366:
367: IF ((l_rule='E') OR (l_rule='I')) THEN
368: UPDATE igs_pe_housing_int phi
369: SET status = '3',
370: error_code = 'E695'
371: WHERE phi.status = '2' AND
372: phi.interface_run_id = l_interface_run_id AND
375:
376: -- 2 . If rule is E and the match ind is null, we update the interface table for all duplicate records with status 1 and match ind 19.
377:
378: IF (l_rule = 'E') THEN
379: UPDATE igs_pe_housing_int phi
380: SET status = '1',
381: match_ind = '19'
382: WHERE phi.status = '2' AND
383: phi.interface_run_id = l_interface_run_id AND
393:
394: -- 3. If rule is R and the record status is 18,19,22,23 these records have been processed, but didn't get updated. Update them to 1
395:
396: IF (l_rule='R') THEN
397: UPDATE igs_pe_housing_int phi
398: SET status = '1'
399: WHERE phi.status = '2' AND
400: phi.interface_run_id = l_interface_run_id AND
401: phi.match_ind IN ('18','19','22','23');
403:
404: -- 4. If rule is R and the match ind is not null and is neither 21 nor 25, update it to errored record.
405:
406: IF (l_rule = 'R') THEN
407: UPDATE igs_pe_housing_int phi
408: SET status = '3', error_code = 'E695'
409: WHERE phi.status = '2' AND
410: phi.interface_run_id = l_interface_run_id AND
411: (phi.match_ind IS NOT NULL AND phi.match_ind NOT IN ('21','25'));
413:
414: -- 5. If rule = 'R' and there is no discprepency in duplicate records, update them to status 1 and match ind 23.
415:
416: IF (l_rule ='R') THEN
417: UPDATE igs_pe_housing_int phi
418: SET status = '1', match_ind = '23'
419: WHERE phi.status = '2' AND
420: phi.interface_run_id = l_interface_run_id AND
421: phi.match_ind IS NULL AND
434: -- 6. If rule is R and there are still some records, they are the ones for which there is some discrepency existing. Update them to status 3
435: -- and value from the OSS table.
436:
437: IF (l_rule ='R') THEN
438: UPDATE igs_pe_housing_int phi
439: SET status = 3,
440: match_ind = 20,
441: dup_teaching_period_id = (SELECT pi.teaching_period_id
442: FROM igs_pe_teach_periods_all pi, igs_ad_interface_all ai