1 PACKAGE BODY IGS_GE_PRC_TRANSFER AS
2 /* $Header: IGSGE07B.pls 115.5 2002/11/29 00:32:33 nsidana ship $ */
3 /* Bug 1956374
4 Who msrinivi
5 What duplicate removal Rremoved genp_prc_clear_rowid,genp_set_row_id
6 */
7 l_rowid varchar2(25);
8 --
9 -- To get the alternate person ids for the data transfer mechanism.
10 FUNCTION GENP_GET_ALT_PE_ID(
11 p_person_id IN NUMBER ,
12 p_person_id_type IN VARCHAR2 )
13 RETURN VARCHAR2 AS
14 v_alt_person_id VARCHAR2(20);
15 CURSOR c_get_alt_pe_id IS
16 SELECT api_person_id
17 FROM IGS_PE_ALT_PERS_ID
18 WHERE person_id_type = p_person_id_type AND
19 pe_person_id = p_person_id;
20 BEGIN
21 OPEN c_get_alt_pe_id;
22 FETCH c_get_alt_pe_id INTO v_alt_person_id;
23 IF c_get_alt_pe_id%NOTFOUND THEN
24 CLOSE c_get_alt_pe_id;
25 RETURN NULL;
26 ELSE
27 CLOSE c_get_alt_pe_id;
28 RETURN v_alt_person_id;
29 END IF;
30 END genp_get_alt_pe_id;
31 --
32 -- To get the person statistics location description for the data transf.
33 FUNCTION GENP_GET_PS_LOCATION(
34 p_person_id IN NUMBER ,
35 p_start_dt IN DATE ,
36 p_location_type IN VARCHAR2 )
37 RETURN VARCHAR2 AS
38 v_location_country IGS_PE_STATISTICS.term_location_country%TYPE;
39 v_location_postcode IGS_PE_STATISTICS.term_location_postcode%TYPE;
40 CURSOR c_get_term_location_data IS
41 SELECT term_location_country, term_location_postcode
42 FROM IGS_PE_STATISTICS
43 WHERE person_id = p_person_id AND
44 start_dt = p_start_dt;
45 CURSOR c_get_home_location_data IS
46 SELECT home_location_country, home_location_postcode
47 FROM IGS_PE_STATISTICS
48 WHERE person_id = p_person_id AND
49 start_dt = p_start_dt;
50 BEGIN
51 IF p_location_type = cst_term_location_type THEN
52 OPEN c_get_term_location_data;
53 FETCH c_get_term_location_data INTO v_location_country, v_location_postcode;
54 CLOSE c_get_term_location_data;
55 IF v_location_country IS NOT NULL THEN
56 RETURN v_location_country;
57 ELSE
58 IF v_location_postcode IS NOT NULL THEN
59 RETURN v_location_postcode;
60 END IF;
61 END IF;
62 RETURN NULL;
63 END IF;
64 IF p_location_type = cst_home_location_type THEN
65 OPEN c_get_home_location_data;
66 FETCH c_get_home_location_data INTO v_location_country, v_location_postcode;
67 CLOSE c_get_home_location_data;
68 IF v_location_country IS NOT NULL THEN
69 RETURN v_location_country;
70 ELSE
71 IF v_location_postcode IS NOT NULL THEN
72 RETURN v_location_postcode;
73 END IF;
74 END IF;
75 RETURN NULL;
76 END IF;
77 END genp_get_ps_location;
78 --
79 -- To insert data transfer IGS_PE_STD_TODO entries
80 PROCEDURE GENP_INS_TRNSFR_TODO(
81 p_person_id IN NUMBER ,
82 p_s_student_todo_type IN VARCHAR2 ,
83 p_todo_dt IN DATE )
84 AS
85 e_resource_busy EXCEPTION;
86 PRAGMA EXCEPTION_INIT(e_resource_busy, -54);
87 gv_other_details VARCHAR2(255);
88 l_val number;
89 BEGIN
90 DECLARE
91 v_st_sequence_number IGS_PE_STD_TODO.sequence_number%TYPE;
92 v_insert_not_req_flag BOOLEAN DEFAULT FALSE;
93 CURSOR c_chk_todo_dt_exists (
94 cp_person_id IGS_PE_STD_TODO.person_id%TYPE,
95 cp_s_student_todo_type IGS_PE_STD_TODO.s_student_todo_type%TYPE ) IS
96 SELECT sequence_number
97 FROM IGS_PE_STD_TODO
98 WHERE person_id = cp_person_id AND
99 s_student_todo_type = cp_s_student_todo_type AND
100 logical_delete_dt IS NULL;
101 CURSOR c_get_todo_rec_for_update (
102 cp_person_id IGS_PE_STD_TODO.person_id%TYPE,
103 cp_s_student_todo_type IGS_PE_STD_TODO.s_student_todo_type%TYPE,
104 cp_sequence_number NUMBER ) IS
105 SELECT IGS_PE_STD_TODO.* , ROWID
106 FROM IGS_PE_STD_TODO
107 WHERE person_id = cp_person_id AND
108 s_student_todo_type = cp_s_student_todo_type AND
109 sequence_number = cp_sequence_number
110 FOR UPDATE OF todo_dt
111 NOWAIT;
112 v_existing_st_record C_GET_TODO_REC_FOR_UPDATE%ROWTYPE;
113 BEGIN
114 --- Check for previous insertion during transaction as multiple records
115 --- for a given person/todo_type aren't necessary. If one is found and it
116 --- isn't logically deleted, then update the todo_dt to postpone processing
117 --- (10 minutes is added).
118 OPEN c_chk_todo_dt_exists(p_person_id, p_s_student_todo_type);
119 FETCH c_chk_todo_dt_exists INTO v_st_sequence_number;
120 LOOP
121 EXIT WHEN c_chk_todo_dt_exists%NOTFOUND;
122 -- The following block selects the record for updating. If a lock is present,
123 -- then the record isn't updated and processing continues.
124 BEGIN
125 OPEN c_get_todo_rec_for_update( p_person_id,
126 p_s_student_todo_type,
127 v_st_sequence_number );
128 FETCH c_get_todo_rec_for_update INTO v_existing_st_record;
129 LOOP
130 EXIT WHEN c_get_todo_rec_for_update%NOTFOUND;
131 v_insert_not_req_flag := TRUE;
132 SELECT IGS_PE_STD_TODO_SEQ_NUM_S.NEXTVAL INTO L_VAL
133 FROM DUAL ;
134 IGS_PE_STD_TODO_PKG.UPDATE_ROW(
135 x_rowid => v_existing_st_record.ROWID ,
136 X_PERSON_ID => v_existing_st_record.PERSON_ID,
137 X_S_STUDENT_TODO_TYPE => v_existing_st_record.S_STUDENT_TODO_TYPE,
138 X_SEQUENCE_NUMBER => L_VAL,
139 X_TODO_DT => SYSDATE + 1/144 ,
140 X_LOGICAL_DELETE_DT => v_existing_st_record.LOGICAL_DELETE_DT,
141 X_MODE=> 'R'
142 );
143 FETCH c_get_todo_rec_for_update INTO v_existing_st_record;
144 END LOOP;
145 CLOSE c_get_todo_rec_for_update;
146 EXCEPTION
147 WHEN e_resource_busy THEN
148 NULL;
149 WHEN OTHERS THEN
150 RAISE;
151 END;
152 FETCH c_chk_todo_dt_exists INTO v_st_sequence_number;
153 END LOOP;
154 CLOSE c_chk_todo_dt_exists;
155 --- If an update was performed or a previous record exists then exit
156 -- the procedure.
157 IF v_insert_not_req_flag THEN
158 RETURN;
159 END IF;
160 --- Insert a record into the IGS_PE_STD_TODO table using the parameters.
161 SELECT IGS_PE_STD_TODO_SEQ_NUM_S.nextval INTO L_VAL
162 FROM DUAL ;
163 IGS_PE_STD_TODO_PKG.INSERT_ROW (
164 X_ROWID => L_ROWID,
165 x_person_id =>p_person_id,
166 x_s_student_todo_type =>p_s_student_todo_type,
167 x_sequence_number => L_VAL ,
168 x_todo_dt => p_todo_dt,
169 x_logical_delete_dt => NULL,
170 x_mode => 'R'
171 );
172
173 END;
174 EXCEPTION
175 WHEN OTHERS THEN
176 Fnd_Message.Set_Name('IGS' , 'IGS_GE_UNHANDLED_EXCEPTION');
177 IGS_GE_MSG_STACK.ADD;
178 App_Exception.Raise_Exception ;
179 END genp_ins_trnsfr_todo;
180 --
181
182 -- Process PE rowids in a PL/SQL TABLE for the current commit.
183 FUNCTION genp_prc_pe_rowids (
184 p_inserting IN BOOLEAN ,
185 p_updating IN BOOLEAN ,
186 p_deleting IN BOOLEAN ,
187 p_message_name IN OUT NOCOPY VARCHAR2 )
188 RETURN BOOLEAN AS
189 v_index BINARY_INTEGER;
190 v_other_detail VARCHAR2(255);
191 r_pe IGS_PE_PERSON%ROWTYPE;
192 v_todo_dt DATE;
193
194 CURSOR per_cur (lcrowid VARCHAR2) IS
195 SELECT * FROM IGS_PE_PERSON
196 WHERE rowid = lcrowid;
197
198 BEGIN
199 -- Process saved rows.
200 FOR v_index IN 1..gv_table_index - 1
201 LOOP
202
203 begin
204 OPEN per_cur (gt_rowid_table(v_index));
205 FETCH per_cur INTO r_pe;
206 IF per_cur%NOTFOUND THEN
207 RAISE no_data_found ;
208 ELSE
209 -- Allow for a 10 minute interval
210 v_todo_dt := SYSDATE + 1/144;
211 -- Insert person into IGS_PE_STD_TODO table
212 IGS_GE_PRC_TRANSFER.genp_ins_trnsfr_todo(r_pe.person_id,
213 'PERS_TRANS',
214 v_todo_dt);
215
216
217 END IF;
218 CLOSE Per_Cur;
219 EXCEPTION
220 WHEN no_data_found THEN
221 CLOSE per_cur;
222 WHEN others THEN
223 IF per_cur%ISOPEN THEN
224 CLOSE per_cur;
225 END IF;
226 Fnd_Message.Set_Name('IGS' , 'IGS_GE_UNHANDLED_EXCEPTION');
227 IGS_GE_MSG_STACK.ADD;
228 App_Exception.Raise_Exception ;
229
230 END;
231
232 END LOOP;
233 RETURN TRUE;
234 END genp_prc_pe_rowids;
235 END IGS_GE_PRC_TRANSFER;
[Home] [Help]