[Home] [Help]
PACKAGE BODY: APPS.IGS_FI_WAV_DTLS_WF
Source
1 PACKAGE BODY igs_fi_wav_dtls_wf AS
2 /* $Header: IGSFI96B.pls 120.2 2006/05/12 04:18:59 abshriva noship $ */
3
4 PROCEDURE log_to_fnd ( p_v_module IN VARCHAR2,
5 p_v_string IN VARCHAR2 ) IS
6 /******************************************************************
7 Created By : Umesh Udayaprakash
8 Date Created By : 8/5/2005
9 Purpose : Procedure for logging
10
11 Known limitations,enhancements,remarks:
12 Change History
13 Who When What
14 ***************************************************************** */
15 BEGIN
16
17 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
18 fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_fi_wav_dtls_wf.' || p_v_module, p_v_string);
19 END IF;
20
21 END log_to_fnd;
22
23 PROCEDURE raise_wavtrandtlstofa_event( p_n_person_id IN hz_parties.party_id%TYPE,
24 p_v_waiver_name IN igs_fi_waiver_pgms.waiver_name%TYPE,
25 p_c_fee_cal_type IN igs_fi_f_typ_ca_inst.fee_cal_type%TYPE,
26 p_n_fee_ci_seq_number IN igs_fi_f_typ_ca_inst.fee_ci_sequence_number%TYPE,
27 p_n_waiver_amount IN igs_fi_inv_int_all.invoice_amount%TYPE) AS
28 /******************************************************************
29 Created By : Umesh Udayaprakash
30 Date Created By : 8/5/2005
31 Purpose : Procedure for raise workflow notification for Financial Aid
32
33 Known limitations,enhancements,remarks:
34 Change History
35 Who When What
36 abshriva 12-May-2006 Bug 5217319: Amount Precision change, added API call to allow correct precison into DB
37 uudayapr 10/27/2005 Bug 4704177 Added the workflow parameter P_FIN_AID_ADMIN to
38 pass the Notifying user based on the profile Value
39 IGS_FI_WAV_NOTIFY_USER
40 ***************************************************************** */
41 CURSOR cur_cal_desc(c_cal_load_cal_type IN igs_ca_inst_all.cal_type%TYPE,
42 c_n_load_ci_seq_number IN igs_ca_inst_all.sequence_number%TYPE
43 ) IS
44 SELECT description
45 FROM IGS_CA_INST_ALL
46 WHERE cal_type = c_cal_load_cal_type
47 AND sequence_number = c_n_load_ci_seq_number;
48
49 CURSOR cur_person_details(c_n_person_id IN hz_parties.party_id%TYPE) IS
50 SELECT person_number, full_name
51 FROM IGS_PE_PERSON_BASE_V
52 WHERE person_id = c_n_person_id;
53
54 CURSOR cur_alt_person_id (c_n_person_id IN hz_parties.party_id%TYPE) IS
55 SELECT api_person_id, person_id_type
56 FROM igs_pe_person_id_type_v
57 WHERE pe_person_id = c_n_person_id;
58
59 CURSOR c_seq_num IS
60 SELECT igs_fi_wav_wf_trans_fa_s.NEXTVAL
61 FROM sys.dual;
62
63 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
64 l_c_load_cal_type igs_ca_inst_all.cal_type%TYPE;
65 l_n_load_ci_seq_number igs_ca_inst_all.sequence_number%TYPE;
66 l_v_message_name fnd_new_messages.message_name%TYPE;
67 l_b_returnflag BOOLEAN;
68
69 l_v_load_description igs_ca_inst_all.description%TYPE;
70 l_cur_person_details cur_person_details%ROWTYPE;
71 l_cur_alt_person_id cur_alt_person_id%ROWTYPE;
72 ln_seq_val NUMBER;
73 lv_raise_event VARCHAR2(100);
74 BEGIN
75 --Check whether the workflow profile has been enabled.
76 IF NVL(FND_PROFILE.VALUE('IGS_WF_ENABLE'),'N') = 'Y' THEN
77
78 lv_raise_event := 'oracle.apps.igs.fi.WaiverTransDtlsToFA';
79
80 --Call Out to derive the load period.
81 l_b_returnflag := igs_fi_gen_001.finp_get_lfci_reln ( p_cal_type => p_c_fee_cal_type,
82 p_ci_sequence_number => p_n_fee_ci_seq_number,
83 p_cal_category => 'FEE' ,
84 p_ret_cal_type => l_c_load_cal_type,
85 p_ret_ci_sequence_number => l_n_load_ci_seq_number,
86 p_message_name => l_v_message_name);
87 IF NOT l_b_returnflag THEN
88 log_to_fnd (p_v_module => 'raise_wavtrandtlstofa_event',
89 p_v_string => l_v_message_name);
90 ELSE
91 --Callout to get the calendar Description
92 OPEN cur_cal_desc(c_cal_load_cal_type => l_c_load_cal_type,
93 c_n_load_ci_seq_number =>l_n_load_ci_seq_number );
94 FETCH cur_cal_desc INTO l_v_load_description;
95 CLOSE cur_cal_desc;
96
97 --Call out to get the person details
98 OPEN cur_person_details(c_n_person_id => p_n_person_id);
99 FETCH cur_person_details INTO l_cur_person_details;
100 CLOSE cur_person_details;
101 --Call out to get the alternate person id
102 OPEN cur_alt_person_id(c_n_person_id => p_n_person_id);
103 FETCH cur_alt_person_id INTO l_cur_alt_person_id;
104 CLOSE cur_alt_person_id;
105 --Creation of paramter List
106 wf_event.AddParameterToList(p_name => 'PERSON_ID',
107 p_value => p_n_person_id,
108 p_parameterlist=> l_parameter_list);
109
110 wf_event.AddParameterToList(p_name => 'PERSON_NUMBER',
111 p_value => l_cur_person_details.person_number,
112 p_parameterlist=> l_parameter_list);
113
114 wf_event.AddParameterToList(p_name => 'PERSON_NAME',
115 p_value => l_cur_person_details.full_name,
116 p_parameterlist=> l_parameter_list);
117
118 wf_event.AddParameterToList(p_name => 'PERSON_ID_TYPE',
119 p_value => l_cur_alt_person_id.person_id_type,
120 p_parameterlist=> l_parameter_list);
121
122 wf_event.AddParameterToList(p_name => 'ALTERNATE_ID',
123 p_value => l_cur_alt_person_id.api_person_id,
124 p_parameterlist=> l_parameter_list);
125
126 wf_event.AddParameterToList(p_name => 'WAIVER_NAME',
127 p_value => p_v_waiver_name,
128 p_parameterlist=> l_parameter_list);
129
130 wf_event.AddParameterToList(p_name => 'LOAD_CAL_TYPE',
131 p_value => l_c_load_cal_type,
132 p_parameterlist=> l_parameter_list);
133
134 wf_event.AddParameterToList(p_name => 'LOAD_CI_SEQUENCE_NUMBER',
135 p_value => l_n_load_ci_seq_number,
136 p_parameterlist=> l_parameter_list);
137
138 wf_event.AddParameterToList(p_name => 'LOAD_CAL_DESC',
139 p_value => l_v_load_description,
140 p_parameterlist=> l_parameter_list);
141
142 wf_event.AddParameterToList(p_name => 'WAIVER_AMOUNT',
143 p_value => igs_fi_gen_gl.get_formatted_amount(p_n_waiver_amount),
144 p_parameterlist=> l_parameter_list);
145
146 wf_event.AddParameterToList(p_name => 'TRANSACTION_DATE',
147 p_value => TRUNC(sysdate),
148 p_parameterlist=> l_parameter_list);
149
150 wf_event.AddParameterToList(p_name => 'P_FIN_AID_ADMIN',
151 p_value => NVL(FND_PROFILE.VALUE('IGS_FI_WAV_NOTIFY_USER'),'SYSADMIN'),
152 p_parameterlist=> l_parameter_list);
153 --Call out to generate the sequence number
154 OPEN c_seq_num;
155 FETCH c_seq_num INTO ln_seq_val ;
156 CLOSE c_seq_num ;
157 --Raising the Bussiness event
158 wf_event.raise( p_event_name => lv_raise_event,
159 p_event_key => 'WTRANTOFA' || ln_seq_val,
160 p_parameters => l_parameter_list);
161 --Deleting the parameter List
162 l_parameter_list.DELETE;
163
164 END IF;
165 END IF ;
166 EXCEPTION
167 WHEN OTHERS THEN
168 WF_CORE.CONTEXT('IGS_FI_WAV_DTLS_WF', 'raise_wavtrandtlstofa_event',ln_seq_val,lv_raise_event);
169 raise;
170 END raise_wavtrandtlstofa_event;
171
172 /**
173 Procedure for raising student waiver assignment event
174 */
175
176 PROCEDURE raise_stdntwavassign_event(p_n_person_id IN hz_parties.party_id%TYPE,
177 p_v_waiver_name IN igs_fi_waiver_pgms.waiver_name%TYPE,
178 p_c_fee_cal_type IN igs_fi_f_typ_ca_inst.fee_cal_type%TYPE,
179 p_n_fee_ci_seq_number IN igs_fi_f_typ_ca_inst.fee_ci_sequence_number%TYPE) AS
180
181 CURSOR cur_person_details(c_n_person_id IN hz_parties.party_id%TYPE) IS
182 SELECT person_number, full_name
183 FROM IGS_PE_PERSON_BASE_V
184 WHERE person_id = c_n_person_id;
185
186 CURSOR c_seq_num IS
187 SELECT igs_fi_wav_wf_stud_s.NEXTVAL
188 FROM sys.dual;
189
190 l_cur_person_details cur_person_details%ROWTYPE;
191 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
192 lv_raise_event VARCHAR2(100);
193 ln_seq_val NUMBER;
194 BEGIN
195 --Check whether the workflow profile has been enabled.
196 IF NVL(FND_PROFILE.VALUE('IGS_WF_ENABLE'),'N') = 'Y' THEN
197 lv_raise_event := 'oracle.apps.igs.fi.StudWavAssignments';
198 --Call out to get the person details
199 OPEN cur_person_details(c_n_person_id => p_n_person_id);
200 FETCH cur_person_details INTO l_cur_person_details;
201 CLOSE cur_person_details;
202 --Creation of paramter List
203 wf_event.AddParameterToList(p_name => 'PERSON_ID',
204 p_value => p_n_person_id,
205 p_parameterlist=> l_parameter_list);
206
207 wf_event.AddParameterToList(p_name => 'PERSON_NUMBER',
208 p_value => l_cur_person_details.person_number,
209 p_parameterlist=> l_parameter_list);
210
211 wf_event.AddParameterToList(p_name => 'PERSON_NAME',
212 p_value => l_cur_person_details.full_name,
213 p_parameterlist=> l_parameter_list);
214
215 wf_event.AddParameterToList(p_name => 'WAIVER_NAME',
216 p_value => p_v_waiver_name,
217 p_parameterlist=> l_parameter_list);
218
219 wf_event.AddParameterToList(p_name => 'FEE_CAL_TYPE',
220 p_value => p_c_fee_cal_type,
221 p_parameterlist=> l_parameter_list);
222
223 wf_event.AddParameterToList(p_name => 'FEE_CI_SEQUENCE_NUMBER',
224 p_value => p_n_fee_ci_seq_number,
225 p_parameterlist=> l_parameter_list);
226
227 OPEN c_seq_num;
228 FETCH c_seq_num INTO ln_seq_val ;
229 CLOSE c_seq_num ;
230 --Raising the Bussiness event
231 wf_event.raise( p_event_name => lv_raise_event,
232 p_event_key => 'WAVASSGN' || ln_seq_val,
233 p_parameters => l_parameter_list);
234 --Deleting the parameter List
235 l_parameter_list.DELETE;
236 END IF;
237 EXCEPTION
238 WHEN OTHERS THEN
239 WF_CORE.CONTEXT('IGS_FI_WAV_DTLS_WF', 'raise_stdntwavassign_event',ln_seq_val,lv_raise_event);
240 raise;
241
242 END raise_stdntwavassign_event;
243
244 END igs_fi_wav_dtls_wf;