[Home] [Help]
Skip to content
PACKAGE BODY: APPS.PAY_GB_RTI_FPS_ADHOC
Source
1 PACKAGE BODY PAY_GB_RTI_FPS_ADHOC AS
2 /* $Header: pygbrtifpsadhoc.pkb 120.0.12020000.1 2013/03/09 06:27:07 ssanjays noship $ */
3 PROCEDURE per_gb_extra_info_update
4 (p_effective_date IN date
5 ,p_assignment_set_id IN number
6 ,p_irregular_payments IN varchar2 DEFAULT 'Y') IS
7 --Usage Notes:
8 /*
9 This procedure is used to update the Irregular Payment Info
10 for the assignments in assignment sets.
11 p_effective_date - Effective date for assignments to be picked
12 Mandatory)
13 p_assignment_set_id - Assignment set id of assignments that should be updated.
14 (Mandatory)
15 p_irregular_payments - Irregular Payment Flag.
16 (Non-Mandatory)
17 */
18 ----
19 process_asg_id number;
20 p_ovn per_assignment_extra_info.object_version_number%TYPE;
21 p_aei_id per_assignment_extra_info.assignment_extra_info_id%TYPE;
22 v_assignment_id number;
23 l_formula_id number;
24 l_include_flag varchar2(1) DEFAULT 'N';
25 l_tab_asg_set_amnds pqp_budget_maintenance.t_asg_set_amnds;
26 invalid_assg_set_id EXCEPTION;
27 CURSOR c_assignments
28 (p_effective_date_c IN date
29 ,p_assignment_set_id_c IN number) IS
30 SELECT DISTINCT
31 paf.assignment_id
32 FROM per_all_assignments_f paf
33 ,per_business_groups pbf
34 ,hr_assignment_set_amendments hasa
35 WHERE pbf.legislation_code = 'GB'
36 AND pbf.business_group_id = paf.business_group_id
37 AND (
38 p_effective_date_c BETWEEN paf.effective_start_date
39 AND paf.effective_end_date
40 )
41 AND hasa.assignment_set_id = p_assignment_set_id_c
42 AND hasa.assignment_id = paf.assignment_id;
43 CURSOR c_extra_info
44 (p_assignment_id IN number) IS
45 SELECT assignment_id
46 ,object_version_number
47 ,assignment_extra_info_id
48 FROM per_assignment_extra_info
49 WHERE assignment_id = p_assignment_id
50 AND information_type = 'GB_PAY_RTI'
51 AND aei_information_category = 'GB_PAY_RTI';
52 BEGIN
53 IF p_assignment_set_id IS NOT NULL THEN
54 pqp_budget_maintenance.get_asg_set_details
55 (p_assignment_set_id => p_assignment_set_id
56 ,p_formula_id => l_formula_id
57 ,p_tab_asg_set_amnds => l_tab_asg_set_amnds);
58 END IF;
59
60 IF l_formula_id IS NULL
61 AND l_tab_asg_set_amnds.count = 0 THEN
62 RAISE invalid_assg_set_id;
63 END IF;
64
65 FOR i IN c_assignments (p_effective_date
66 ,p_assignment_set_id) LOOP
67 l_include_flag := 'N';
68
69 l_include_flag := pqp_budget_maintenance.chk_is_asg_in_asg_set
70 (p_assignment_id => i.assignment_id
71 ,p_formula_id => l_formula_id
72 ,p_tab_asg_set_amnds => l_tab_asg_set_amnds
73 ,p_effective_date => p_effective_date);
74
75 IF l_include_flag = 'Y' THEN
76 OPEN c_extra_info (i.assignment_id);
77
78 FETCH c_extra_info
79 INTO process_asg_id
80 ,p_ovn
81 ,p_aei_id;
82
83 IF c_extra_info%FOUND THEN
84 hr_assignment_extra_info_api.update_assignment_extra_info
85 (p_validate => FALSE
86 ,p_assignment_extra_info_id => p_aei_id
87 ,p_object_version_number => p_ovn
88 ,p_aei_information_category => 'GB_PAY_RTI'
89 ,p_aei_information3 => p_irregular_payments);
90 ELSE
91 hr_utility.set_location ('Inside Create Assignment ID Processed: '|| i.assignment_id,12);
92
93 hr_assignment_extra_info_api.create_assignment_extra_info
94 (p_validate => FALSE
95 ,p_assignment_id => i.assignment_id
96 ,p_object_version_number => p_ovn
97 ,p_assignment_extra_info_id => p_aei_id
98 ,p_aei_information_category => 'GB_PAY_RTI'
99 ,p_aei_information3 => p_irregular_payments
100 ,p_information_type => 'GB_PAY_RTI');
101 END IF;
102
103 CLOSE c_extra_info;
104 END IF;
105 END LOOP;
106
107 COMMIT;
108 EXCEPTION
109 WHEN invalid_assg_set_id THEN
110 hr_utility.trace (' Error : Assignment set id entered '
111 || p_assignment_set_id
112 || ' is not valid');
113
114 raise_application_error (- 20001
115 ,' Assignment set id entered '
116 || p_assignment_set_id
117 || ' is not valid');
118 END per_gb_extra_info_update;
119
120 PROCEDURE create_sub_class_rules
121 (p_sub_classification_name IN varchar2
122 ,p_element_type_id IN number DEFAULT NULL
123 ,p_element_set_id IN number DEFAULT NULL) IS
124
125 --Usage Notes:
126 /*
127 This procedure is to attach the sub-classifications for passed elements
128 or element sets.
129 p_sub_classification_name - Secondary classification Name to be attached.
130 Mandatory)
131 p_element_type_id - Element type id to which sub-classification has to be attached.
132 (Non-Mandatory)
133 p_element_set_id - Element set id to which elements sub-classification has to be attached.
134 (Non-Mandatory)
135 */
136 ----
137
138 CURSOR csr_elements
139 (v_element_set_id IN number
140 ,v_element_type_id IN number) IS
141 SELECT DISTINCT
142 petf.element_type_id
143 FROM pay_element_type_rules petr
144 ,pay_element_types_f petf
145 WHERE (
146 petr.element_set_id = v_element_set_id
147 AND petr.element_type_id = petf.element_type_id
148 )
149 OR (
150 petf.element_type_id = v_element_type_id
151 )
152 ;
153 CURSOR csr_get_class_id
154 (v_element_type_id IN number) IS
155 SELECT classification_id
156 FROM pay_element_classifications
157 WHERE classification_name = p_sub_classification_name
158 AND legislation_code = 'GB'
159 AND business_group_id IS NULL
160 AND parent_classification_id IN
161 (
162 SELECT DISTINCT
163 petf.classification_id
164 FROM pay_element_types_f petf
165 WHERE (
166 petf.element_type_id = v_element_type_id
167 )
168 );
169 CURSOR csr_sub_class_present
170 (v_sub_clas_id IN number
171 ,v_element_type_id IN number) IS
172 SELECT sub_classification_rule_id
173 FROM pay_sub_classification_rules_f
174 WHERE element_type_id = v_element_type_id
175 AND classification_id = v_sub_clas_id;
176 l_proc_name varchar2(80) DEFAULT '';
177 l_rowid rowid;
178 l_sub_class_rule_id number;
179 l_sub_classification_id number;
180 l_user_id number DEFAULT fnd_global.user_id;
181 l_login_id number DEFAULT fnd_global.login_id;
182 l_business_group_id number;
183 l_effective_start_date date;
184 proc_element_type_id number;
185 duplicate_sub_clas EXCEPTION;
186 both_no_null EXCEPTION;
187 BEGIN
188 hr_utility.set_location ('Entering: '
189 || l_proc_name
190 ,10);
191
192 IF p_element_type_id IS NULL
193 AND p_element_set_id IS NULL THEN
194 RAISE both_no_null;
195 END IF;
196
197 OPEN csr_elements (p_element_set_id
198 ,p_element_type_id);
199
200 LOOP
201 FETCH csr_elements
202 INTO proc_element_type_id;
203
204 hr_utility.set_location ('Element Type id: '
205 || proc_element_type_id
206 ,10);
207
208 EXIT WHEN csr_elements%NOTFOUND;
209
210 SELECT min (effective_start_date)
211 ,min (business_group_id)
212 INTO l_effective_start_date
213 ,l_business_group_id
214 FROM pay_element_types_f
215 WHERE element_type_id = proc_element_type_id;
216
217 OPEN csr_get_class_id (proc_element_type_id);
218
219 FETCH csr_get_class_id
220 INTO l_sub_classification_id;
221
222 IF csr_get_class_id%NOTFOUND THEN
223 CLOSE csr_get_class_id;
224
225 fnd_message.set_name ('PAY'
226 ,'PAY_50060_ETM_BAD_ELE_CLASS');
227
228 fnd_message.set_token ('CLASSIFICATION'
229 ,p_sub_classification_name);
230
231 fnd_message.raise_error;
232 END IF;
233
234 CLOSE csr_get_class_id;
235
236 hr_utility.set_location (l_proc_name
237 ,20);
238
239 OPEN csr_sub_class_present (l_sub_classification_id
240 ,proc_element_type_id);
241
242 FETCH csr_sub_class_present
243 INTO l_sub_class_rule_id;
244
245 IF csr_sub_class_present%NOTFOUND THEN
246 hr_utility.set_location ('Sub Classification: '
247 || l_sub_classification_id
248 ,10);
249 select PAY_SUB_CLASSIFICATION_RULES_S.nextval
250 into l_sub_class_rule_id
251 from dual;
252
253 pay_sub_class_rules_pkg.insert_row
254 (p_rowid => l_rowid
255 ,p_sub_classification_rule_id => l_sub_class_rule_id
256 ,p_effective_start_date => l_effective_start_date
257 ,p_effective_end_date => hr_api.g_eot
258 ,p_element_type_id => proc_element_type_id
259 ,p_classification_id => l_sub_classification_id
260 ,p_business_group_id => l_business_group_id
261 ,p_legislation_code => NULL
262 ,p_creation_date => sysdate
263 ,p_created_by => l_user_id
264 ,p_last_update_date => sysdate
265 ,p_last_updated_by => l_user_id
266 ,p_last_update_login => l_login_id);
267 ELSE
268 RAISE duplicate_sub_clas;
269 END IF;
270
271 CLOSE csr_sub_class_present;
272 l_sub_class_rule_id:= 0;
273 END LOOP;
274
275 CLOSE csr_elements;
276
277 hr_utility.set_location ('Leaving: '
278 || l_proc_name
279 ,30);
280
281 COMMIT;
282 EXCEPTION
283 WHEN duplicate_sub_clas THEN
284 hr_utility.set_location ('Duplicate Sub classification '
285 || sqlerrm
286 ,30);
287
288 raise_application_error (- 20001
289 ,' Duplicate Sub classification ');
290
291 ROLLBACK;
292 WHEN both_no_null THEN
293 hr_utility.set_location ('Both cannot be null '
294 || sqlerrm
295 ,30);
296
297 raise_application_error (- 20001
298 ,' Both Element set id and Element type id cannot be null ');
299
300 ROLLBACK;
301 WHEN others THEN
302 hr_utility.set_location ('Error: '
303 || sqlerrm
304 ,30);
305
306 raise_application_error (- 20001
307 ,sqlerrm);
308
309 ROLLBACK;
310 END create_sub_class_rules;
311 END PAY_GB_RTI_FPS_ADHOC;