[Home] [Help]
PACKAGE BODY: APPS.PAY_NO_SC_ARCHIVE
Source
1 PACKAGE BODY PAY_NO_SC_ARCHIVE as
2 /* $Header: pynosfca.pkb 120.0.12000000.1 2007/05/20 09:27:43 rlingama noship $ */
3 TYPE lock_rec IS RECORD (
4 archive_assact_id NUMBER);
5 TYPE lock_table IS TABLE OF lock_rec INDEX BY BINARY_INTEGER;
6 g_debug boolean := hr_utility.debug_enabled;
7 g_lock_table lock_table;
8 g_package VARCHAR2(50) := ' PAY_NO_SC_ARCHIVE.';
9 g_business_group_id NUMBER;
10 g_legal_employer_id NUMBER;
11 g_employee_id NUMBER;
12 g_effective_date DATE;
13 g_archive VARCHAR2(50);
14 g_err_num NUMBER;
15 g_errm VARCHAR2(150);
16 /* GET PARAMETER */
17 FUNCTION GET_PARAMETER(
18 p_parameter_string IN VARCHAR2
19 ,p_token IN VARCHAR2
20 ,p_segment_number IN NUMBER default NULL ) RETURN VARCHAR2
21 IS
22 l_parameter pay_payroll_actions.legislative_parameters%TYPE:=NULL;
23 l_start_pos NUMBER;
24 l_delimiter VARCHAR2(1):=' ';
25 l_proc VARCHAR2(240):= g_package||' get parameter ';
26 BEGIN
27
28 IF g_debug THEN
29 hr_utility.set_location(' Entering Function GET_PARAMETER',10);
30 END IF;
31 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
32 --
33 IF l_start_pos = 0 THEN
34 l_delimiter := '|';
35 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
36 END IF;
37 IF l_start_pos <> 0 THEN
38 l_start_pos := l_start_pos + length(p_token||'=');
39 l_parameter := substr(p_parameter_string,
40 l_start_pos,
41 instr(p_parameter_string||' ',
42 l_delimiter,l_start_pos)
43 - l_start_pos);
44 IF p_segment_number IS NOT NULL THEN
45 l_parameter := ':'||l_parameter||':';
46 l_parameter := substr(l_parameter,
47 instr(l_parameter,':',1,p_segment_number)+1,
48 instr(l_parameter,':',1,p_segment_number+1) -1
49 - instr(l_parameter,':',1,p_segment_number));
50 END IF;
51 END IF;
52 --
53 IF g_debug THEN
54 hr_utility.set_location(' Leaving Function GET_PARAMETER',20);
55 END IF;
56 RETURN l_parameter;
57 END;
58 /* GET ALL PARAMETERS */
59 PROCEDURE GET_ALL_PARAMETERS(
60 p_payroll_action_id IN NUMBER
61 ,p_business_group_id OUT NOCOPY NUMBER
62 ,p_legal_employer_id OUT NOCOPY NUMBER
63 ,p_employee OUT NOCOPY NUMBER
64 ,p_archive OUT NOCOPY VARCHAR2
65 ,p_effective_date OUT NOCOPY DATE
66 ) IS
67 CURSOR csr_parameter_info(p_payroll_action_id NUMBER) IS
68 SELECT PAY_NO_ARC_HOLIDAY_ENTITLEMENT.GET_PARAMETER(legislative_parameters,
69 'LEGAL_EMPLOYER')
70 ,PAY_NO_ARC_HOLIDAY_ENTITLEMENT.GET_PARAMETER(legislative_parameters,'EMPLOYEEID')
71 ,PAY_NO_ARC_HOLIDAY_ENTITLEMENT.GET_PARAMETER(legislative_parameters,'ARCHIVE')
72 ,effective_date
73 ,business_group_id
74 FROM pay_payroll_actions
75 WHERE payroll_action_id = p_payroll_action_id;
76 l_proc VARCHAR2(240):= g_package||' GET_ALL_PARAMETERS ';
77 --
78 BEGIN
79 fnd_file.put_line (fnd_file.LOG, 'Entering Get all Parameters' );
80 OPEN csr_parameter_info (p_payroll_action_id);
81 FETCH csr_parameter_info
82 INTO p_legal_employer_id
83 ,p_employee
84 ,p_archive
85 ,p_effective_date
86 ,p_business_group_id;
87 CLOSE csr_parameter_info;
88 --
89 IF g_debug THEN
90 hr_utility.set_location(' Leaving Procedure GET_ALL_PARAMETERS',30);
91 END IF;
92 END GET_ALL_PARAMETERS;
93
94 /* RANGE CODE */
95 PROCEDURE RANGE_CODE (p_payroll_action_id IN NUMBER
96 ,p_sql OUT NOCOPY VARCHAR2)
97 IS
98
99 l_ovn NUMBER;
100 l_action_info_id NUMBER;
101
102 BEGIN
103
104
105 IF g_debug THEN
106 hr_utility.set_location(' Entering Procedure RANGE_CODE',10);
107 END IF;
108
109
110
111 PAY_NO_SC_ARCHIVE.GET_ALL_PARAMETERS( p_payroll_action_id
112 ,g_business_group_id
113 ,g_legal_employer_id
114 ,g_employee_id
115 ,g_archive
116 ,g_effective_date
117 );
118
119
120 /** Insert report parameters **/
121 pay_action_information_api.create_action_information (
122 p_action_information_id => l_action_info_id,
123 p_action_context_id => p_payroll_action_id,
124 p_action_context_type => 'PA',
125 p_object_version_number => l_ovn,
126 p_effective_date => g_effective_date,
127 p_source_id => NULL,
128 p_source_text => NULL,
129 p_action_information_category=> 'EMEA REPORT DETAILS',
130 p_action_information1 => 'PYNOSFCA',
131 p_action_information2 => g_legal_employer_id,
132 p_action_information3 => g_employee_id,
133 p_action_information4 => fnd_date.date_to_canonical(g_effective_date));
134
135
136 p_sql := 'SELECT DISTINCT person_id
137 FROM per_people_f ppf
138 ,pay_payroll_actions ppa
139 WHERE ppa.payroll_action_id = :payroll_action_id
140 AND ppa.business_group_id = ppf.business_group_id
141 ORDER BY ppf.person_id';
142
143
144 hr_utility.set_location(' Leaving Procedure RANGE_CODE',50);
145
146
147 EXCEPTION
148 WHEN OTHERS THEN
149 hr_utility.set_location('Exception****',70);
150 -- Return cursor that selects no rows
151 p_sql := 'select 1 from dual where to_char(:payroll_action_id) = dummy';
152 END RANGE_CODE;
153
154 /* ASSIGNMENT ACTION CODE PAY_NO_ARC_HOLIDAY_ENTITLEMENT.ASSIGNMENT_ACTION_CODE */
155
156 PROCEDURE ASSIGNMENT_ACTION_CODE
157 (p_payroll_action_id IN NUMBER
158 ,p_start_person IN NUMBER
159 ,p_end_person IN NUMBER
160 ,p_chunk IN NUMBER)
161 IS
162
163 l_actid number;
164 l_start_date date;
165 l_end_date date;
166 /****** Start Of Cursor ******/
167
168 CURSOR csr_action_creation(csr_v_business_group_id NUMBER,
169 csr_v_legal_employer_id NUMBER,
170 csr_v_effective_date DATE,
171 csr_v_start_date DATE,
172 csr_v_end_date DATE,
173 csr_v_person_id NUMBER
174 )
175 IS
176 SELECT paaf.assignment_id
177 FROM per_all_people_f papf,
178 per_all_assignments_f paaf,
179 per_assignment_status_types past,
180 hr_soft_coding_keyflex hsc,
181 hr_organization_information hoi
182 WHERE paaf.person_id = papf.person_id
183 AND papf.person_id = nvl(csr_v_person_id,papf.person_id)
184 AND paaf.assignment_status_type_id = past.assignment_status_type_id
185 AND papf.business_group_id = csr_v_business_group_id
186 AND hsc.soft_coding_keyflex_id = paaf.soft_coding_keyflex_id
187 AND hoi.org_information1 = hsc.segment2
188 AND hoi.organization_id = csr_v_legal_employer_id
189 AND hoi.org_information_context = 'NO_LOCAL_UNITS'
190 AND past.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN')
191 AND papf.person_id BETWEEN p_start_person and p_end_person
192 AND csr_v_effective_date BETWEEN papf.effective_start_date
193 AND papf.effective_end_date
194 AND csr_v_effective_date BETWEEN paaf.effective_start_date
195 AND paaf.effective_end_date
196 AND EXISTS(select paa.absence_attendance_id from per_absence_attendances paa, per_absence_attendance_types paat
197 where paa.person_id = papf.person_id
198 and paa.absence_attendance_type_id = paat.absence_attendance_type_id
199 and paat.absence_category in ('CMS','S','PTS')
200 and paa.abs_information1 = 'SC'
201 and paa.date_start between csr_v_start_date and csr_v_end_date
202 and paa.date_end between csr_v_start_date and csr_v_end_date );
203
204
205
206 /**** End Of Cursor ****/
207
208 BEGIN
209
210
211
212 PAY_NO_SC_ARCHIVE.GET_ALL_PARAMETERS( p_payroll_action_id
213 ,g_business_group_id
214 ,g_legal_employer_id
215 ,g_employee_id
216 ,g_archive
217 ,g_effective_date
218 );
219
220 l_start_date := add_months(g_effective_date,-12);
221 l_end_date := g_effective_date;
222
223
224 FOR csr_act IN csr_action_creation(g_business_group_id,g_legal_employer_id,g_effective_date,l_start_date,l_end_date,g_employee_id)
225 LOOP
226
227 SELECT pay_assignment_actions_s.NEXTVAL
228 INTO l_actid
229 FROM DUAL;
230
231 hr_nonrun_asact.insact (l_actid,
232 csr_act.assignment_id,
233 p_payroll_action_id,
234 p_chunk,
235 g_business_group_id
236 );
237 END LOOP;
238
239 IF g_debug THEN
240 hr_utility.set_location(' Entering Procedure ASSIGNMENT_ACTION_CODE',60);
241 END IF;
242 IF g_debug THEN
243 hr_utility.set_location(' Leaving Procedure ASSIGNMENT_ACTION_CODE',70);
244 END IF;
245 -- fnd_file.put_line (fnd_file.LOG, 'Exiting Assignment Action Code' );
246
247
248 END ASSIGNMENT_ACTION_CODE;
249 /* INITIALIZATION CODE */
250 PROCEDURE INITIALIZATION_CODE(p_payroll_action_id IN NUMBER)
251 IS
252 BEGIN
253 -- fnd_file.put_line (fnd_file.LOG, 'Entering Initialization Code' );
254 hr_utility.set_location(' Entering Procedure INITIALIZATION_CODE',80);
255
256
257
258 hr_utility.set_location(' Leaving Procedure INITIALIZATION_CODE',90);
259
260 -- fnd_file.put_line (fnd_file.LOG, 'Existing Initialization Code' );
261 EXCEPTION WHEN OTHERS THEN
262 g_err_num := SQLCODE;
263 IF g_debug THEN
264 hr_utility.set_location(' Err Procedure INITIALIZATION_CODE',110);
265 hr_utility.set_location('ORA_ERR: ' || g_err_num ||
266 'In INITIALIZATION_CODE',180);
267 END IF;
268 fnd_file.put_line (fnd_file.LOG, 'Error in Initialization Code' );
269 END INITIALIZATION_CODE;
270 /* ARCHIVE CODE */
271 PROCEDURE ARCHIVE_CODE(p_assignment_action_id IN NUMBER
272 ,p_effective_date IN DATE)
273 IS
274
275
276 l_start_date date;
277 l_end_date date;
278 l_arch_ovn number;
279 l_arch_action_info_id number;
280
281 /*** Start of Cursor ***/
282 Cursor csr_self_certificate(csr_v_business_group_id NUMBER,
283 csr_v_legal_employer_id NUMBER,
284 csr_v_effective_date DATE,
285 csr_v_start_date DATE,
286 csr_v_end_date DATE,
287 csr_v_person_id NUMBER
288 ) is
289 select papf.employee_number,
290 papf.full_name,
291 paaf.assignment_id,
292 count(paa.abs_information1) sc_days
293 from per_absence_attendances paa,
294 per_absence_attendance_types paat,
295 per_all_assignments_f paaf,
296 per_assignment_status_types past,
297 pay_assignment_actions paas,
298 per_all_people_f papf,
299 hr_soft_coding_keyflex hsc,
300 hr_organization_information hoi
301 where paas.assignment_action_id = p_assignment_action_id
302 and paa.person_id = papf.person_id
303 AND papf.person_id = nvl(csr_v_person_id,papf.person_id)
304 and paa.absence_attendance_type_id = paat.absence_attendance_type_id
305 and paaf.person_id = papf.person_id
306 AND paaf.assignment_status_type_id = past.assignment_status_type_id
307 and paaf.assignment_id = paas.assignment_id
308 and papf.business_group_id = csr_v_business_group_id
309 and hsc.soft_coding_keyflex_id = paaf.soft_coding_keyflex_id
310 and hoi.org_information1 = hsc.segment2
311 and hoi.organization_id = csr_v_legal_employer_id
312 AND past.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN')
313 and hoi.ORG_INFORMATION_CONTEXT = 'NO_LOCAL_UNITS'
314 and paaf.primary_flag = 'Y'
315 and paat.absence_category in ('CMS','S','PTS')
316 and paa.abs_information1 = 'SC'
317 and paa.date_start between csr_v_start_date and csr_v_end_date
318 and paa.date_end between csr_v_start_date and csr_v_end_date
319 and csr_v_effective_date between papf.effective_start_date and papf.effective_end_date
320 and csr_v_effective_date between paaf.effective_start_date and paaf.effective_end_date
321 group by papf.employee_number,papf.full_name,paaf.assignment_id;
322
323
324 /**** End of Cursor ***/
325
326 BEGIN
327 fnd_file.put_line (fnd_file.LOG, 'entering archive code' );
328
329
330 l_start_date := trunc(p_effective_date,'YY');
331 l_end_date := last_day(add_months(l_start_date,11));
332
333 FOR csr_he IN csr_self_certificate(g_business_group_id,g_legal_employer_id,p_effective_date,l_start_date,l_end_date,g_employee_id)
334 LOOP
335
336
337 pay_action_information_api.create_action_information (
338 p_action_context_id=> p_assignment_action_id,
339 p_action_context_type=> 'AAP',
340 p_action_information_category=> 'EMEA REPORT INFORMATION',
341 p_assignment_id => csr_he.assignment_id,
342 p_action_information1 => 'PYNOSFCA',
343 p_action_information2 => csr_he.employee_number,
344 p_action_information3 => csr_he.full_name,
345 p_action_information4 => csr_he.sc_days,
346 p_action_information_id=> l_arch_action_info_id,
347 p_object_version_number=> l_arch_ovn);
348 END LOOP;
349
350 IF g_debug THEN
351 hr_utility.set_location(' Entering Procedure ARCHIVE_CODE',80);
352 END IF;
353 IF g_debug THEN
354 hr_utility.set_location(' Leaving Procedure ARCHIVE_CODE',90);
355 END IF;
356 fnd_file.put_line (fnd_file.LOG, 'Exiting archive code' );
357 END ARCHIVE_CODE;
358 END PAY_NO_SC_ARCHIVE;