1 PACKAGE BODY igs_pe_gen_003 AS
2 /* $Header: IGSPE18B.pls 120.9 2006/01/23 06:33:29 gmaheswa noship $ */
3 /*
4 || Created By : gmaheswa
5 || Created On : 2-NOV-2004
6 || Purpose : Created to process FA todo items in case of insert/update of housing status/residency status
7 || Known limitations, enhancements or remarks :
8 || Change History :
9 || (reverse chronological order - newest change first)
10 || Who When What
11 || skpandey 18-AUG-2005 Bug#: 4378028
12 || Added procedure raise_person_type_event and resp_assignment to handle person type responsibility enhancements
13 */
14 PROCEDURE process_res_dtls(
15 p_action IN VARCHAR2 , -- I/U I-INSERT U-UPDATE,
16 p_old_record IN igs_pe_res_dtls_all%ROWTYPE,
17 p_new_record IN igs_pe_res_dtls_all%ROWTYPE
18 ) AS
19 l_label VARCHAR2(100);
20 l_debug_str VARCHAR2(2000);
21 l_residency_class igs_pe_res_dtls.residency_class_cd%TYPE;
22 BEGIN
23 fnd_profile.get('IGS_FI_RES_CLASS_ID',l_residency_class);
24 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
25 fnd_log.string(fnd_log.level_statement,'igs.plsql.igs_pe_gen_003.process_res_dtls.debug','l_residency_class:'||l_residency_class);
26 END IF;
27 IF (p_action = 'I' AND NVL(l_residency_class,'*') = p_new_record.residency_class_cd)
28 OR (p_action = 'U' AND NVL(l_residency_class,'*') = p_new_record.residency_class_cd AND NVL(p_old_record.residency_status_cd,'*') <> NVL(p_new_record.residency_status_cd,'*')) THEN
29 igf_aw_coa_gen.ins_coa_todo(
30 p_new_record.person_id,
31 'IGSPE18B'
32 );
33 END IF;
34 EXCEPTION
35 WHEN OTHERS THEN
36 l_label := 'igs.plsql.igs_pe_gen_003.process_res_dtls.exception';
37 IF fnd_log.test(fnd_log.level_exception,l_label) THEN
38 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
39 l_debug_str := fnd_message.get || '. Residency Details Id : '||P_NEW_RECORD.RESIDENT_DETAILS_ID ||' '|| SQLERRM;
40 fnd_log.string_with_context( fnd_log.level_exception,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
41 END IF;
42 END process_res_dtls;
43
44 PROCEDURE process_housing_dtls(
45 p_action IN VARCHAR2 , -- I/U I-INSERT U-UPDATE,
46 p_old_record IN igs_pe_teach_periods_all%ROWTYPE,
47 p_new_record IN igs_pe_teach_periods_all%ROWTYPE
48 ) AS
49 l_label VARCHAR2(100);
50 l_debug_str VARCHAR2(2000);
51
52 BEGIN
53 IF p_action = 'I' OR (p_action = 'U' AND NVL(p_old_record.teach_period_resid_stat_cd,'*') <> NVL(p_new_record.teach_period_resid_stat_cd,'*')) THEN
54 igf_aw_coa_gen.ins_coa_todo(
55 p_new_record.person_id,
56 'IGSPE18B'
57 );
58 END IF;
59 EXCEPTION
60 WHEN OTHERS THEN
61 l_label := 'igs.plsql.igs_pe_gen_003.process_housing_dtls.exception';
62 IF fnd_log.test(fnd_log.level_exception,l_label) THEN
63 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
64 l_debug_str := fnd_message.get || '. Teaching Period Id : '||P_NEW_RECORD.TEACHING_PERIOD_ID ||' '|| SQLERRM;
65 fnd_log.string_with_context( fnd_log.level_exception,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
66 END IF;
67 END process_housing_dtls;
68
69
70 PROCEDURE RAISE_PERSON_TYPE_EVENT(
71 p_person_id IN NUMBER,
72 p_person_type_code IN VARCHAR2,
73 p_action IN VARCHAR2,
74 p_end_date IN DATE
75 ) AS
76
77 /*
78 || Created By : skpandey
79 || Created On : 2-aug-2005
80 || Purpose : 4378028
81 || Known limitations, enhancements or remarks :
82 || Change History :
83 || (reverse chronological order - newest change first)
84 || Who When What
85 || ssawhney 18-AUG-2005 Bug#: 4378028 added end date.
86 */
87 ---- Cursor to get system_type_person
88 CURSOR get_sys_person_id(cp_person_type_code igs_pe_typ_instances_all.person_type_code%TYPE) IS
89 SELECT ppt.system_type
90 FROM igs_pe_person_types ppt
91 WHERE ppt.person_type_code = cp_person_type_code;
92
93 ---- Cursor to get next sequence number
94 CURSOR c_seq_num IS
95 SELECT IGS_PE_PER_TYP_WF_S.nextval
96 FROM DUAL;
97
98 l_system_type IGS_PE_PERSON_TYPES.system_type%TYPE;
99 ln_seq_val NUMBER;
100 l_event_t wf_event_t;
101 l_parameter_list_t wf_parameter_list_t := wf_parameter_list_t();
102 l_prog_label CONSTANT VARCHAR2(100) := 'igs.plsql.igs_pe_gen_003.raise_person_type_event';
103 l_label VARCHAR2(500);
104 l_debug_str VARCHAR2(3200);
105
106 BEGIN
107 --Get system type
108 OPEN get_sys_person_id(p_person_type_code);
109 FETCH get_sys_person_id INTO l_system_type ;
110 CLOSE get_sys_person_id ;
111
112 -- initialize the parameter list.
113 wf_event_t.Initialize(l_event_t);
114
115 -- set the parameters.
116 wf_event.AddParameterToList ( p_name => 'PERSON_ID', p_value => p_person_id, p_parameterlist => l_parameter_list_t);
117 wf_event.AddParameterToList ( p_name => 'S_PERSON_TYPE', p_value => l_system_type, p_parameterlist => l_parameter_list_t);
118 wf_event.AddParameterToList ( p_name => 'ACTION', p_value => p_action, p_parameterList => l_parameter_list_t);
119 wf_event.AddParameterToList ( p_name => 'END_DATE', p_value => p_end_date, p_parameterList => l_parameter_list_t);
120
121
122 -- get the sequence value to be added to EVENT KEY to make it unique.
123 OPEN c_seq_num;
124 FETCH c_seq_num INTO ln_seq_val ;
125 CLOSE c_seq_num ;
126
127 -- fnd_logging before raising business event
128 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
129 l_label := 'igs.plsql.igs_pe_gen_003.raise_person_type_event.raise_event';
130 l_debug_str := fnd_message.get || ' Action : ' || p_action ||'/' ||' Person : ' ||p_person_id ||'/'||' S person type : '||l_system_type ||'/' || ' Sequence is : '||ln_seq_val ;
131 fnd_log.string_with_context( fnd_log.level_procedure,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
132 END IF;
133
134 -- raise event
135 WF_EVENT.RAISE (p_event_name => 'oracle.apps.igs.pe.person_type_change',
136 p_event_key => 'RESP_ASSIGNMENT'||ln_seq_val,
137 p_parameters => l_parameter_list_t
138 );
139
140 -- Delete parameter_list
141 l_parameter_list_t.DELETE;
142
143 --COMMIT; should not be doing commit inside BE flow, AD has further processsing.
144
145 -- Exception Handling
146 EXCEPTION
147 WHEN OTHERS THEN
148
149 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
150 l_label := 'igs.plsql.igs_pe_gen_003.raise_person_type_event.execption';
151 l_debug_str := fnd_message.get || 'Person : ' ||p_person_id ||'/'||' S person type : '||l_system_type ||'/' || ' sequence is : '||ln_seq_val ||'/'|| SQLERRM ;
152 fnd_log.string_with_context( fnd_log.level_procedure,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
153 END IF;
154
155 END RAISE_PERSON_TYPE_EVENT;
156
157
158
159 FUNCTION RESP_ASSIGNMENT(
160 p_subscription_guid in raw,
161 p_event in out NOCOPY wf_event_t
162 ) RETURN VARCHAR2 AS
163 /*
164 || Created By : skpandey
165 || Created On : 2-aug-2005
166 || Purpose : 4378028
167 || Known limitations, enhancements or remarks :
168 || Change History :
169 || (reverse chronological order - newest change first)
170 || Who When What
171 || ssawhney 18-AUG-2005 Bug#: 4378028 added end date logic in insert/update.
172 */
173
174 l_default_date DATE := TO_DATE('4712/12/31','YYYY/MM/DD');
175 ---- Get the FND User Information for the Person ID passed.
176 CURSOR get_user_id_cur(cp_person_id fnd_user.person_party_id%type) IS
177 SELECT user_id
178 FROM fnd_user
179 WHERE person_party_id = cp_person_id;
180
181 -- Exclusively for Insert.
182 ---- Get the responsibilities associated with the System Person Type, which are not associated with the person already. INSERT CASE
183 CURSOR get_resp_info_cur(cp_system_person_typ igs_pe_typ_rsp_dflt.s_person_type%type, cp_user_id fnd_user.user_id%type) IS
184 SELECT default_resp.responsibility_key, oss.application_short_name, default_resp.responsibility_id, default_resp.application_id, default_resp.description
185 FROM igs_pe_typ_rsp_dflt oss , fnd_responsibility_vl default_resp
186 WHERE oss.s_person_type= cp_system_person_typ
187 AND oss.responsibility_key = default_resp.responsibility_key
188 AND NOT EXISTS
189 (SELECT 1
190 FROM fnd_user_resp_groups_direct resp_group
191 WHERE user_id = cp_user_id AND
192 resp_group.responsibility_id = default_resp.responsibility_id AND
193 resp_group.responsibility_application_id = default_resp.application_id
194 );
195
196
197 -- Get the Default responsibilities mapping with the System Person Type ,
198 -- that are present with the user. Update the end date. Both INSERT/UPDATE case
199 CURSOR get_inactive_resp_cur(cp_user_id fnd_user.user_id%type, cp_system_person_type igs_pe_typ_rsp_dflt.s_person_type%type) IS
200 SELECT resp.responsibility_key, resp.responsibility_id, resp.application_id, resp_group.start_date, Resp_group.end_date, fnd.application_short_name
201 FROM fnd_user_resp_groups_direct resp_group, fnd_responsibility resp , igs_pe_typ_rsp_dflt oss, fnd_application fnd
202 WHERE user_id = cp_user_id AND
203 Resp.responsibility_id = resp_group.Responsibility_id AND
204 Resp.application_id = resp_group.responsibility_application_id AND
205 oss.s_person_type = cp_system_person_type AND
206 oss.responsibility_key = resp.responsibility_key AND
207 resp.application_id = fnd.application_id;
208
209
210 -- if a resp is through 2 pti. then since the event is fired after_dml, then max of null and static would return static
211 -- hence need to use l_default.
212 CURSOR get_max_date (cp_person_id NUMBER, cp_resp_key VARCHAR2,cp_app_short_name VARCHAR2) IS
213 SELECT max(NVL(pti.end_date , l_default_date))
214 FROM igs_pe_typ_instances_all pti , igs_pe_person_types typ , igs_pe_typ_rsp_dflt dflt
215 WHERE pti.person_id = cp_person_id AND
216 pti.person_type_code = typ.person_type_code AND
217 typ.system_type = dflt.s_person_type AND
218 dflt.responsibility_key = cp_resp_key AND
219 dflt.application_short_name = cp_app_short_name;
220
221
222
223
224 ---- Get all those responsibilities associated with the System Person Type of OTHER. DELETE CASE
225 CURSOR get_resp_sys_cur(cp_user_id fnd_user.user_id%type, cp_system_person_type igs_pe_typ_rsp_dflt.s_person_type%type) IS
226 SELECT resp.application_id, resp.responsibility_id , resp.responsibility_key, resp_group.start_date, Resp_group.end_date
227 FROM fnd_user_resp_groups_direct resp_group, fnd_responsibility resp
228 WHERE user_id = cp_user_id AND
229 Resp.responsibility_id = resp_group.Responsibility_id AND
230 Resp.application_id = resp_group.responsibility_application_id
231 --AND Resp_group.end_date IS NOT NULL
232 AND EXISTS
233 (SELECT 1
234 FROM igs_pe_typ_rsp_dflt oss
235 WHERE oss.s_person_type= cp_system_person_type
236 AND oss.responsibility_key = resp.responsibility_key);
237
238
239 l_parameter_list_t wf_parameter_list_t := wf_parameter_list_t();
240 l_person_id NUMBER(15);
241 l_system_person_type VARCHAR2(30);
242 l_action VARCHAR2(10);
243 l_user_id fnd_user.user_id%type;
244 l_result VARCHAR2(100);
245 l_prog_label CONSTANT VARCHAR2(100) := 'igs.plsql.igs_pe_gen_003.resp_assignment';
246 l_label VARCHAR2(500);
247 l_debug_str VARCHAR2(3200);
248 l_end_date DATE;
249
250 l_update_resp BOOLEAN := FALSE;
251 l_max_end_date DATE;
252 ---------Main Start-------------------------------------------------
253 BEGIN
254
255 l_parameter_list_t := p_event.getparameterlist;
256 l_person_id := TO_NUMBER(wf_event.getvalueforparameter('PERSON_ID',l_parameter_list_t));
257 l_system_person_type := wf_event.getvalueforparameter('S_PERSON_TYPE',l_parameter_list_t);
258 l_action := wf_event.getvalueforparameter('ACTION',l_parameter_list_t);
259 l_end_date := igs_ge_date.igsdate(igs_ge_date.igschar(wf_event.getvalueforparameter('END_DATE',l_parameter_list_t))) ;
260
261
262 --Get User id
263 OPEN get_user_id_cur(l_person_id);
264 FETCH get_user_id_cur INTO l_user_id;
265 CLOSE get_user_id_cur;
266
267 ---- l_action is INSERT
268 IF l_action IN ('INSERT', 'UPDATE') THEN
269
270 FOR inactive_resp_rec IN get_inactive_resp_cur(l_user_id, l_system_person_type) LOOP
271
272 --fnd_logging
273 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
274 l_label := 'igs.plsql.igs_pe_gen_003.resp_assignment.'||l_action;
275 l_debug_str := fnd_message.get || 'System Person Type : '||l_system_person_type ||'/'|| ' User id : ' ||l_user_id || ' End Date ' ||'/' ||l_end_date;
276 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
277 END IF;
278
279 IF l_action = 'INSERT' THEN
280 -- if any existing resp exists, then update them with the MAX end date between PTI end date and existing resp end date.
281 IF ( NVL(inactive_resp_rec.end_date, l_default_date) < NVL(TRUNC(l_end_date), l_default_date) ) THEN
282 l_update_resp := TRUE;
283 END IF;
284 ELSE
285 --l_action is update.
286 OPEN get_max_date (l_person_id,inactive_resp_rec.responsibility_key, inactive_resp_rec.application_short_name);
287 FETCH get_max_date INTO l_max_end_date;
288 CLOSE get_max_date;
289
290 IF (l_max_end_date IS NULL AND inactive_resp_rec.end_date IS NULL) OR
291 (l_max_end_date = inactive_resp_rec.end_date ) THEN
292 l_update_resp := FALSE;
293 ELSE
294 l_update_resp := TRUE;
295
296 IF l_end_date > l_max_end_date THEN
297 -- This is the exceptional scnerio when record from import process is updated with end date < current date
298 -- And Current date is passed in place of that from the TBH
299 l_end_date := l_end_date ;
300 ELSE
301 -- This is the usual scenerio
302 l_end_date := l_max_end_date ;
303 END IF;
304
305 --after assignment, if end_date = default then set end date to null.
306 IF l_end_date = l_default_date THEN
307 l_end_date := null;
308 END IF;
309 END IF;
310
311 END IF;
312
313 --Call update
314 IF l_update_resp THEN
315 fnd_user_resp_groups_api.update_assignment (
316 user_id => l_user_id,
317 responsibility_id => inactive_resp_rec.responsibility_id,
318 responsibility_application_id => inactive_resp_rec.application_id,
319 security_group_id => 0,
320 start_date => inactive_resp_rec.start_date,
321 end_date => TRUNC(l_end_date),
322 description => NULL);
323 END IF;
324 END LOOP;
325
326 FOR resp_info_rec IN get_resp_info_cur(l_system_person_type, l_user_id) LOOP
327 --fnd_logging
328 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
329 l_label := 'igs.plsql.igs_pe_gen_003.resp_assignment.'||l_action;
330 l_debug_str := fnd_message.get || 'System Person Type : '||l_system_person_type ||'/'|| ' User id : ' ||l_user_id || ' End Date ' ||'/' ||l_end_date;
331 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
332 END IF;
333 Fnd_User_Resp_Groups_api.insert_assignment (
334 user_id => l_user_id,
335 responsibility_id => resp_info_rec.responsibility_id,
336 responsibility_application_id => resp_info_rec.application_id,
337 security_group_id => 0,
338 start_date => SYSDATE,
339 end_date => TRUNC(l_end_date),
340 description => resp_info_rec.description);
341 END LOOP;
342
343 ---- l_action is DELETE
344 ELSIF l_action = 'DELETE' THEN
345
346 FOR resp_sys_rec IN get_resp_sys_cur(l_user_id, l_system_person_type) LOOP
347 --fnd_logging
348 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
349 l_label := 'igs.plsql.igs_pe_gen_003.resp_assignment.delete';
350 l_debug_str := fnd_message.get || 'System Person Type : '||l_system_person_type ||'/'|| ' User id : ' ||l_user_id || ' Resp ' ||resp_sys_rec.responsibility_id ;
351 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
352 END IF;
353
354 -- Delete event will only be called for Others person type. And will always be for sysdate.
355 -- if the resp is already end date with a past end date. Then dont touch it.
356 IF ( NVL(resp_sys_rec.end_date, sysdate) >= sysdate) THEN
357 fnd_user_resp_groups_api.update_assignment (
358 user_id => l_user_id,
359 responsibility_id => resp_sys_rec.responsibility_id,
360 responsibility_application_id => resp_sys_rec.application_id,
361 security_group_id => 0,
362 start_date => resp_sys_rec.start_date,
363 end_date => TRUNC(SYSDATE),
364 description => NULL);
365 END IF;
366 END LOOP;
367 END IF;
368
369 l_result := wf_rule.default_rule(p_subscription_guid, p_event);
370
371 RETURN(l_result);
372
373 EXCEPTION
374 WHEN OTHERS THEN
375
376 --fnd_logging
377 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
378 l_label := 'igs.plsql.igs_pe_gen_003.resp_assignment.exception';
379 l_debug_str := fnd_message.get || 'System Person Type : '||l_system_person_type ||'/'|| ' User id : ' ||l_user_id || SQLERRM;
380 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
381 END IF;
382
383 WF_CORE.CONTEXT('IGS_PE_ELEARNING_PKG','RESP_ASSIGNMENT',1, p_subscription_guid);
384 wf_event.setErrorInfo(p_event,'ERROR');
385
386 END RESP_ASSIGNMENT;
387
388 PROCEDURE TURNOFF_TCA_BE(
389 p_turnoff VARCHAR2
390 ) AS
391 /*
392 || Created By : gmaheswa
393 || Created On : 17-Jan-2006
394 || Purpose : 4938278
395 || Known limitations, enhancements or remarks :
396 || Change History :
397 || (reverse chronological order - newest change first)
398 || Who When What
399 */
400 i BOOLEAN;
401 BEGIN
402
403 IF p_turnoff = 'Y' THEN
404 g_hz_api_callouts_profl := FND_PROFILE.VALUE('HZ_EXECUTE_API_CALLOUTS');
405 IF g_hz_api_callouts_profl <> 'N' THEN
406 FND_PROFILE.PUT('HZ_EXECUTE_API_CALLOUTS','N');
407 END IF;
408 ELSIF p_turnoff = 'N' THEN
409 IF g_hz_api_callouts_profl <> 'N' THEN
410 FND_PROFILE.PUT('HZ_EXECUTE_API_CALLOUTS',g_hz_api_callouts_profl);
411 END IF;
412 END IF;
413 END TURNOFF_TCA_BE;
414
415
416 END igs_pe_gen_003;
[Home] [Help]