[Home] [Help]
PACKAGE BODY: APPS.IGS_AD_OFFRESP_STATUS_WF
Source
1 PACKAGE BODY igs_ad_offresp_status_wf AS
2 /* $Header: IGSADD0B.pls 115.2 2003/10/21 14:59:05 rboddu noship $ */
3 ---------------------------------------------------------------------------------------------------------------------------------------
4 -- Created By : rboddu
5 -- Date Created On : 07-OCT-2003
6 -- Purpose : Bug: 3132406. This package is used to raise the Offer Response Status change Business Events.
7 -- Change History
8 -- Who When What
9 --
10 ---------------------------------------------------------------------------------------------------------------------------------------
11 PROCEDURE adm_offer_response_changed (
12 p_person_id IN igs_ad_ps_appl_inst_all.person_id%TYPE,
13 p_admission_appl_number IN igs_ad_ps_appl_inst_all.admission_appl_number%TYPE,
14 p_nominated_course_cd IN igs_ad_ps_appl_inst_all.nominated_course_cd%TYPE,
15 p_sequence_number IN igs_ad_ps_appl_inst_all.sequence_number%TYPE,
16 p_old_offresp_status IN igs_ad_ps_appl_inst_all.adm_offer_resp_status%TYPE,
17 p_new_offresp_status IN igs_ad_ps_appl_inst_all.adm_offer_resp_status%TYPE
18 ) IS
19
20 -- Get a unique sequence number
21 CURSOR c_seq_num IS
22 SELECT igs_ad_offresp_stat_wf_s.NEXTVAL
23 FROM SYS.dual;
24
25 CURSOR cur_prof_value IS
26 SELECT FND_PROFILE.VALUE('IGS_WF_ENABLE') value
27 FROM dual;
28
29 ln_seq_val NUMBER;
30 lv_item_type VARCHAR2(100) ;
31 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
32 lv_raise_event VARCHAR2(100);
33 l_cur_prof_value cur_prof_value%ROWTYPE;
34
35
36 BEGIN
37
38 -- Checking if the Workflow is installed at the environment or not.
39 OPEN cur_prof_value;
40 FETCH cur_prof_value INTO l_cur_prof_value;
41 CLOSE cur_prof_value;
42
43 IF (l_cur_prof_value.value = 'Y') THEN
44
45 lv_raise_event := 'oracle.apps.igs.ad.offerresponse.changed';
46
47 -- Get the sequence value
48 OPEN c_seq_num;
49 FETCH c_seq_num INTO ln_seq_val ;
50 CLOSE c_seq_num ;
51
52 wf_event.AddParameterToList(p_name =>'P_PERSON_ID',
53 p_value => p_person_id,
54 p_parameterlist=> l_parameter_list);
55
56 wf_event.AddParameterToList(p_name =>'P_ADMISSION_APPL_NUMBER',
57 p_value => p_admission_appl_number,
58 p_parameterlist=> l_parameter_list);
59
60 wf_event.AddParameterToList(p_name =>'P_NOMINATED_COURSE_CD',
61 p_value => p_nominated_course_cd,
62 p_parameterlist=> l_parameter_list);
63
64 wf_event.AddParameterToList(p_name =>'P_SEQUENCE_NUMBER',
65 p_value => p_sequence_number,
66 p_parameterlist=> l_parameter_list);
67
68 wf_event.AddParameterToList(p_name =>'P_NEW_OFFER_RESPONSE_STATUS',
69 p_value => p_new_offresp_status,
70 p_parameterlist=> l_parameter_list);
71
72 wf_event.AddParameterToList(p_name =>'P_OLD_OFFER_RESPONSE_STATUS',
73 p_value => p_old_offresp_status,
74 p_parameterlist=> l_parameter_list);
75
76
77
78 -- Raise the event
79 wf_event.raise( p_event_name => lv_raise_event,
80 p_event_key => ln_seq_val,
81 p_parameters => l_parameter_list);
82
83 l_parameter_list.delete;
84 END IF;
85 EXCEPTION
86 WHEN OTHERS THEN
87 WF_CORE.CONTEXT('IGS_AD_OFFRESP_STATUS_WF', 'WF_RAISE_EVENT',
88 lv_item_type||ln_seq_val,lv_raise_event);
89 RAISE;
90
91 END adm_offer_response_changed;
92
93
94 PROCEDURE wf_get_person_attributes(
95 itemtype IN VARCHAR2 ,
96 itemkey IN VARCHAR2 ,
97 actid IN NUMBER ,
98 funcmode IN VARCHAR2 ,
99 resultout OUT NOCOPY VARCHAR2 )
100
101 ------------------------------------------------------------------
102 --Created by : rboddu, Oracle IDC
103 --Date created: 10-OCT-2003
104 --
105 --Purpose: Single Response Build : 3132406
106 --
107 --Known limitations/enhancements and/or remarks:
108 --
109 --Change History:
110 --Who When What
111 -------------------------------------------------------------------
112 IS
113 l_event_name VARCHAR2(200);
114 l_event_message WF_EVENT_T;
115 l_person_number igs_pe_person_base_v.person_number%TYPE;
116 l_full_name igs_pe_person_base_v.full_name%TYPE;
117 l_person_id igs_pe_person_base_v.person_id%TYPE;
118
119 CURSOR get_person_attr IS
120 SELECT person_number, full_name
121 FROM igs_pe_person_base_v
122 WHERE person_id = l_person_id;
123
124 CURSOR cur_user_name IS
125 SELECT user_name
126 FROM fnd_user
127 WHERE user_id = fnd_global.user_id;
128
129 l_wf_role fnd_user.user_name%TYPE;
130
131
132 BEGIN
133
134 IF funcmode='RUN' THEN
135
136 l_person_id := wf_engine.getitemattrnumber(itemtype,itemkey,'P_PERSON_ID');
137
138 OPEN get_person_attr;
139 FETCH get_person_attr INTO l_person_number, l_full_name;
140 CLOSE get_person_attr;
141
142 OPEN cur_user_name;
143 FETCH cur_user_name INTO l_wf_role;
144 CLOSE cur_user_name;
145
146 wf_engine.setitemattrtext(itemtype,itemkey,'P_PERSON_NUMBER',l_person_number);
147 wf_engine.setitemattrtext(itemtype,itemkey,'P_FULL_NAME',l_full_name);
148 wf_engine.setitemattrtext(itemtype,itemkey,'P_WF_ROLE',l_wf_role);
149
150 END IF;
151
152 END wf_get_person_attributes;
153
154
155 PROCEDURE check_single_response (
156 itemtype IN VARCHAR2 ,
157 itemkey IN VARCHAR2 ,
158 actid IN NUMBER ,
159 funcmode IN VARCHAR2 ,
160 resultout OUT NOCOPY VARCHAR2
161 ) AS
162
163 ------------------------------------------------------------------
164 --Created by : rboddu, Oracle IDC
165 --Date created: 10-OCT-2003
166 --
167 --Purpose: Single Response Build : 3132406
168 --
169 --Known limitations/enhancements and/or remarks:
170 --
171 --Change History:
172 --Who When What
173 -------------------------------------------------------------------
174
175 CURSOR get_person_attr (p_person_id igs_pe_person_base_v.person_id%TYPE) IS
176 SELECT person_number, full_name
177 FROM igs_pe_person_base_v
178 WHERE person_id = p_person_id;
179
180 CURSOR get_alternate_code ( p_cal_type igs_ca_inst.cal_type%TYPE,
181 p_sequence_number igs_ca_inst.sequence_number%TYPE) IS
182 SELECT alternate_code
183 FROM igs_ca_inst
184 WHERE cal_type = p_cal_type
185 AND sequence_number = p_sequence_number;
186
187 CURSOR get_single_response (p_person_id igs_ad_appl_all.person_id%TYPE,
188 p_admission_appl_number igs_ad_appl_all.admission_appl_number%TYPE,
189 p_admission_cat igs_ad_appl_all.admission_cat%TYPE,
190 p_s_admission_process_type igs_ad_appl_all.s_admission_process_type%TYPE) IS
191 SELECT admprd.single_response_flag
192 FROM igs_ad_prd_ad_prc_ca admprd,
193 igs_ad_appl_all appl,
194 igs_ad_ps_appl_inst_all aplinst
195 WHERE appl.person_id = p_person_id
196 AND appl.admission_appl_number = p_admission_appl_number
197 AND appl.person_id = aplinst.person_id
198 AND appl.admission_appl_number = aplinst.admission_appl_number
199 AND admprd.adm_cal_type = NVL(aplinst.adm_cal_type,appl.adm_cal_type)
200 AND admprd.adm_ci_sequence_number = NVL(aplinst.adm_ci_sequence_number,appl.adm_ci_sequence_number)
201 AND admprd.admission_cat = p_admission_cat
202 AND admprd.s_admission_process_type = p_s_admission_process_type;
203
204 CURSOR cur_user_name IS
205 SELECT user_name
206 FROM fnd_user
207 WHERE user_id = fnd_global.user_id;
208
209 l_wf_role fnd_user.user_name%TYPE;
210 l_event_message WF_EVENT_T;
211 l_person_number igs_pe_person_base_v.person_number%TYPE;
212 l_full_name igs_pe_person_base_v.full_name%TYPE;
213
214 l_person_id igs_pe_person_base_v.person_id%TYPE;
215 l_admission_appl_number igs_ad_ps_appl_inst_all.admission_appl_number%TYPE;
216 l_nominated_course_cd igs_ad_ps_appl_inst_all.nominated_course_cd%TYPE;
217 l_sequence_number igs_ad_ps_appl_inst_all.sequence_number%TYPE;
218
219 v_admission_cat igs_ad_appl.admission_cat%TYPE;
220 v_s_admission_process_type igs_ad_appl.s_admission_process_type%TYPE;
221 v_acad_cal_type igs_ad_appl.acad_cal_type%TYPE;
222 v_acad_ci_sequence_number igs_ad_appl.acad_ci_sequence_number%TYPE;
223 v_aa_adm_cal_type igs_ad_appl.adm_cal_type%TYPE;
224 v_aa_adm_ci_sequence_number igs_ad_appl.adm_ci_sequence_number%TYPE;
225 v_adm_cal_type igs_ad_appl.adm_cal_type%TYPE;
226 v_adm_ci_sequence_number igs_ad_appl.adm_ci_sequence_number%TYPE;
227 v_appl_dt igs_ad_appl.appl_dt%TYPE;
228 v_adm_appl_status igs_ad_appl.adm_appl_status%TYPE;
229 v_adm_fee_status igs_ad_appl.adm_fee_status%TYPE;
230 l_single_response_flag VARCHAR2(1);
231 l_acad_alt_code igs_ca_inst.alternate_code%TYPE;
232 l_adm_alt_code igs_ca_inst.alternate_code%TYPE;
233 l_new_offer_resp_status igs_ad_ps_appl_inst_all.adm_offer_resp_status%TYPE;
234
235 BEGIN
236 IF funcmode='RUN' THEN
237
238 l_person_id := wf_engine.getitemattrnumber(itemtype,itemkey,'P_PERSON_ID');
239 l_admission_appl_number := wf_engine.getitemattrnumber(itemtype,itemkey,'P_ADMISSION_APPL_NUMBER');
240 l_nominated_course_cd := wf_engine.getitemattrtext(itemtype,itemkey,'P_NOMINATED_COURSE_CD');
241 l_sequence_number := wf_engine.getitemattrnumber(itemtype,itemkey,'P_SEQUENCE_NUMBER');
242 l_new_offer_resp_status := wf_engine.getitemattrtext(itemtype,itemkey,'P_NEW_OFFER_RESPONSE_STATUS');
243
244 -- Fetch the Application details of the current application by calling the following standard API.
245 igs_ad_gen_002.admp_get_aa_dtl(
246 l_person_id,
247 l_admission_appl_number,
248 v_admission_cat,
249 v_s_admission_process_type,
250 v_acad_cal_type,
251 v_acad_ci_sequence_number,
252 v_aa_adm_cal_type,
253 v_aa_adm_ci_sequence_number,
254 v_appl_dt,
255 v_adm_appl_status,
256 v_adm_fee_status);
257
258 --If the Single Response is set, then set the Workflow attributes appropriately and return 'Y'
259 --Else return 'N'
260
261 IF igs_ad_gen_008.admp_get_saors(l_new_offer_resp_status) = 'ACCEPTED' THEN
262 --Fetch the Single Response flag set for the current Admission Period / APC combination.
263 OPEN get_single_response (l_person_id, l_admission_appl_number,v_admission_cat,v_s_admission_process_type);
264 FETCH get_single_response INTO l_single_response_flag;
265 CLOSE get_single_response;
266
267 IF l_single_response_flag = 'Y' THEN
268
269 OPEN cur_user_name;
270 FETCH cur_user_name INTO l_wf_role;
271 CLOSE cur_user_name;
272
273 OPEN get_person_attr(l_person_id);
274 FETCH get_person_attr INTO l_person_number, l_full_name;
275 CLOSE get_person_attr;
276
277 OPEN get_alternate_code(v_acad_cal_type,v_acad_ci_sequence_number);
278 FETCH get_alternate_code INTO l_acad_alt_code;
279 CLOSE get_alternate_code;
280
281 OPEN get_alternate_code(v_aa_adm_cal_type,v_aa_adm_ci_sequence_number);
282 FETCH get_alternate_code INTO l_adm_alt_code;
283 CLOSE get_alternate_code;
284
285 wf_engine.setitemattrtext(itemtype,itemkey,'P_PERSON_NUMBER',l_person_number);
286 wf_engine.setitemattrtext(itemtype,itemkey,'P_FULL_NAME',l_full_name);
287 wf_engine.setitemattrtext(itemtype,itemkey,'P_ACAD_ADM_CAL',l_acad_alt_code||'/'||l_adm_alt_code);
288 wf_engine.setitemattrtext(itemtype,itemkey,'P_WF_ROLE',l_wf_role);
289 resultout := 'COMPLETE:Y';
290 ELSE
291 resultout := 'COMPLETE:N';
292 END IF;
293 ELSE
294 resultout := 'COMPLETE:N';
295 END IF;
296 END IF;
297 END check_single_response;
298 END igs_ad_offresp_status_wf;