[Home] [Help]
PACKAGE BODY: APPS.PAY_DK_ABSR_PKG
Source
1 PACKAGE BODY PAY_DK_ABSR_PKG AS
2 /* $Header: pydkabsr.pkb 120.1 2010/08/21 19:43:10 rsahai noship $ */
3
4 --Global parameters
5 g_package CONSTANT varchar2(33) := 'PAY_DK_ABSR_PKG.';
6 g_debug BOOLEAN := hr_utility.debug_enabled;
7 g_err_num NUMBER;
8
9
10 -----------------------------------------------------------------------------
11 -- GET_LOOKUP_MEANING function used to get labels of items from a lookup
12 -----------------------------------------------------------------------------
13 FUNCTION GET_LOOKUP_MEANING (p_lookup_type varchar2,p_lookup_code varchar2) RETURN VARCHAR2 IS
14
15 CURSOR csr_lookup IS
16 SELECT meaning
17 FROM hr_lookups
18 WHERE lookup_type = p_lookup_type
19 AND lookup_code = p_lookup_code
20 AND enabled_flag = 'Y';
21
22 l_meaning hr_lookups.meaning%type;
23
24 BEGIN
25 OPEN csr_lookup;
26 FETCH csr_lookup INTO l_Meaning;
27 CLOSE csr_lookup;
28 RETURN l_meaning;
29
30 END GET_LOOKUP_MEANING;
31
32
33 -----------------------------------------------------------------------------
34 -- GET_PARAMETER used in SQL to decode legislative parameters
35 -----------------------------------------------------------------------------
36 FUNCTION GET_PARAMETER(
37 p_parameter_string IN VARCHAR2
38 ,p_token IN VARCHAR2
39 ,p_segment_number IN NUMBER default NULL ) RETURN VARCHAR2
40 IS
41 l_parameter pay_payroll_actions.legislative_parameters%TYPE:=NULL;
42 l_start_pos NUMBER;
43 l_delimiter VARCHAR2(1):=' ';
44
45 BEGIN
46 IF g_debug THEN
47 hr_utility.set_location(' Entering Function GET_PARAMETER',10);
48 END IF;
49
50 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
51
52 IF l_start_pos = 0 THEN
53 l_delimiter := '|';
54 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
55 END IF;
56
57 IF l_start_pos <> 0 THEN
58 l_start_pos := l_start_pos + length(p_token||'=');
59 l_parameter := substr(p_parameter_string, l_start_pos,
60 instr(p_parameter_string||' ', l_delimiter,l_start_pos) - l_start_pos);
61
62 IF p_segment_number IS NOT NULL THEN
63 l_parameter := ':'||l_parameter||':';
64 l_parameter := substr(l_parameter,
65 instr(l_parameter,':',1,p_segment_number)+1,
66 instr(l_parameter,':',1,p_segment_number+1) -1
67 - instr(l_parameter,':',1,p_segment_number));
68 END IF;
69 END IF;
70
71 RETURN l_parameter;
72 IF g_debug THEN
73 hr_utility.set_location(' Leaving Function GET_PARAMETER',20);
74 END IF;
75
76 END GET_PARAMETER;
77
78 --------------------------------------------------------------------------------------
79 -- GET_ALL_PARAMETERS used in SQL to cumulatively decode all legislative parameters
80 --------------------------------------------------------------------------------------
81 PROCEDURE GET_ALL_PARAMETERS
82 (p_payroll_action_id IN NUMBER
83 ,p_business_group_id OUT NOCOPY NUMBER
84 ,p_payroll_id OUT NOCOPY NUMBER
85 ,p_sender_id OUT NOCOPY NUMBER
86 ,p_year OUT NOCOPY VARCHAR2
87 ,p_effective_date OUT NOCOPY DATE
88 ,p_report_end_date OUT NOCOPY DATE
89 ,p_archive OUT NOCOPY VARCHAR2)
90 IS
91
92 CURSOR csr_parameter_info(p_payroll_action_id NUMBER) IS
93 SELECT
94 PAY_DK_ABSR_PKG.GET_PARAMETER(legislative_parameters,'LEGAL_EMPLOYER')
95 ,PAY_DK_ABSR_PKG.GET_PARAMETER(legislative_parameters,'PAYROLL')
96 ,PAY_DK_ABSR_PKG.GET_PARAMETER(legislative_parameters,'YEAR')
97 ,PAY_DK_ABSR_PKG.GET_PARAMETER(legislative_parameters,'ARCHIVE')
98 ,effective_date
99 ,fnd_date.canonical_to_date(PAY_DK_ABSR_PKG.GET_PARAMETER(legislative_parameters,'REPORT_END_DATE'))
100 ,business_group_id
101 FROM pay_payroll_actions
102 WHERE payroll_action_id = p_payroll_action_id;
103
104 l_proc VARCHAR2(240):= g_package||'.GET_ALL_PARAMETERS ';
105 --
106 BEGIN
107
108 IF g_debug THEN
109 hr_utility.set_location(' Entering procedure '||l_proc,10);
110 END IF;
111
112 OPEN csr_parameter_info (p_payroll_action_id);
113
114 FETCH csr_parameter_info
115 INTO p_sender_id
116 ,p_payroll_id
117 ,p_year
118 ,p_archive
119 ,p_effective_date
120 ,p_report_end_date
121 ,p_business_group_id;
122 CLOSE csr_parameter_info;
123 --
124 IF g_debug THEN
125 hr_utility.set_location(' Leaving procedure '||l_proc,20);
126 END IF;
127 END GET_ALL_PARAMETERS;
128
129 ----------------------------------------------------
130 -- GET_GLOBAL_VALUE used to fetch Global Values
131 ----------------------------------------------------
132
133 FUNCTION GET_GLOBAL_VALUE(
134 p_global_name VARCHAR2,
135 p_effective_date DATE)
136 RETURN ff_globals_f.global_value%TYPE IS
137
138 CURSOR csr_globals IS
139 SELECT global_value
140 FROM ff_globals_f
141 WHERE global_name = p_global_name
142 AND legislation_code = 'DK'
143 AND p_effective_date BETWEEN effective_start_date AND effective_END_date;
144
145 l_global_value ff_globals_f.global_value%TYPE;
146 l_proc varchar2(72) := g_package||'get_global_value';
147
148 BEGIN
149 IF g_debug THEN
150 hr_utility.set_location('Entering:'|| l_proc, 1);
151 END IF;
152
153 OPEN csr_globals;
154 FETCH csr_globals INTO l_global_value;
155 CLOSE csr_globals;
156
157 IF g_debug THEN
158 hr_utility.set_location('Entering:'|| l_proc, 2);
159 END IF;
160
161 RETURN l_global_value;
162 END GET_GLOBAL_VALUE;
163
164
165 --------------------------------------------------------------------------------------
166 -- RANGE_CODE to specify ranges of assignments to be processed in the archive.
167 --------------------------------------------------------------------------------------
168
169 PROCEDURE RANGE_CODE (pactid IN NUMBER
170 ,sqlstr OUT NOCOPY VARCHAR2)
171 IS
172
173 -- Variable's declarations
174
175 l_count NUMBER := 0;
176 l_action_info_id NUMBER;
177 l_ovn NUMBER;
178 l_business_group_id NUMBER;
179 l_payroll_id NUMBER;
180 l_sender_id NUMBER;
181 l_span VARCHAR(80);
182 l_year VARCHAR(80);
183 l_effective_date DATE;
184 l_report_end_date DATE;
185 l_archive VARCHAR2(80);
186
187 l_from_date VARCHAR2(80);
188 l_to_date VARCHAR2(80);
189
190 l_char_set VARCHAR2(240);
191 l_format VARCHAR2(240);
192 l_bg_da_sys_no VARCHAR2(240);
193 l_sys_name VARCHAR2(240);
194
195 l_le_id NUMBER;
196 l_le_cvr_no VARCHAR2(240);
197 l_le_ds_wpcode VARCHAR2(240);
198 l_le_da_scode VARCHAR2(240);
199 l_le_name VARCHAR2(240);
200 l_le_addr VARCHAR2(240);
201 l_le_pcode VARCHAR2(240);
202 l_le_punit VARCHAR2(10);
203
204 l_sender_cvr_no VARCHAR2(240);
205 l_sender_name VARCHAR2(240);
206 l_sender_addr VARCHAR2(240);
207 l_sender_pcode VARCHAR2(240);
208
209
210 e_no_da_sys_no EXCEPTION;
211 error_message BOOLEAN;
212
213 /* Cursor to check if Current Archive exists */
214 CURSOR csr_count is
215 SELECT count(*)
216 FROM pay_action_information
217 WHERE action_information_category = 'EMEA REPORT DETAILS'
218 AND action_information1 = 'PYDKSTATSA'
219 AND action_context_id = pactid;
220
221
222 /* Cursor to fetch the Sender's Details */
223 /* If p_sender_id is null=> No Legal Employer selected, hence Service Provider of the BG is the Sender */
224 CURSOR csr_get_sender_details(p_sender_id NUMBER, p_effective_date DATE, p_business_group_id NUMBER) IS
225 SELECT hoi2.org_information1 CVR_NO
226 ,hou1.name NAME
227 -- ,loc.ADDRESS_LINE_1||' '||loc.ADDRESS_LINE_2||' '||loc.ADDRESS_LINE_3 ADDR
228 ,SUBSTR (loc.ADDRESS_LINE_1,1,80)||' '||SUBSTR (loc.ADDRESS_LINE_2,1,80)||' '||SUBSTR (loc.ADDRESS_LINE_3,1,80) ADDR --Bug Fix-4998649
229 ,loc.postal_code PCODE
230 FROM HR_ORGANIZATION_UNITS hou1
231 , HR_ORGANIZATION_INFORMATION hoi1
232 , HR_ORGANIZATION_INFORMATION hoi2
233 , HR_LOCATIONS loc
234 WHERE hou1.business_group_id = p_business_group_id
235 and hou1.organization_id = nvl(p_sender_id ,hou1.organization_id)
236 and hou1.location_id = loc.LOCATION_ID(+)
237 and hoi1.organization_id = hou1.organization_id
238 and hoi1.ORG_INFORMATION_CONTEXT='CLASS'
239 and hoi1.org_information1 = nvl2(p_sender_id,'HR_LEGAL_EMPLOYER','DK_SERVICE_PROVIDER')
240 and hoi1.ORG_INFORMATION2 ='Y'
241 and hoi2.ORG_INFORMATION_CONTEXT= nvl2(p_sender_id,'DK_LEGAL_ENTITY_DETAILS','DK_SERVICE_PROVIDER_DETAILS')
242 and hoi2.organization_id = hoi1.organization_id
243 and p_effective_date BETWEEN hou1.DATE_FROM and nvl(hou1.DATE_TO, p_effective_date);
244
245
246 /* Cursor to fetch the Legal Employer Details */
247 CURSOR csr_get_le_details(p_sender_id NUMBER, p_sender_cvr_no VARCHAR2, p_effective_date DATE, p_business_group_id NUMBER) IS
248 SELECT hou.organization_id ORG_ID
249 ,hoi2.ORG_INFORMATION1 CVR_NO
250 ,hoi2.ORG_INFORMATION2 DS_WPCODE
251 ,hoi2.ORG_INFORMATION6 PUNIT
252 ,hou.name NAME
253 -- ,loc.ADDRESS_LINE_1||' '||loc.ADDRESS_LINE_2||' '||loc.ADDRESS_LINE_3 ADDR
254 ,SUBSTR (loc.ADDRESS_LINE_1,1,80)||' '||SUBSTR (loc.ADDRESS_LINE_2,1,80)||' '||SUBSTR (loc.ADDRESS_LINE_3,1,80) ADDR --Bug Fix-4998649
255 ,loc.postal_code PCODE
256 FROM HR_ORGANIZATION_UNITS hou
257 , HR_ORGANIZATION_INFORMATION hoi1
258 , HR_ORGANIZATION_INFORMATION hoi2
259 , HR_LOCATIONS loc
260 WHERE hou.business_group_id = p_business_group_id
261 and hoi1.organization_id = hou.organization_id
262 and hou.location_id = loc.LOCATION_ID(+)
263 and hoi1.ORG_INFORMATION_CONTEXT='CLASS'
264 and hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
265 and hoi1.ORG_INFORMATION2 = 'Y'
266 and hoi2.ORG_INFORMATION_CONTEXT='DK_LEGAL_ENTITY_DETAILS'
267 and hoi2.organization_id = hoi1.organization_id
268 --and nvl(hoi2.org_information1,0)= nvl2(p_sender_id,p_sender_cvr_no,nvl(hoi2.org_information1,0) )
269 and hou.organization_id = p_sender_id
270 and p_effective_date BETWEEN hou.DATE_FROM and nvl(hou.DATE_TO, p_effective_date);
271
272
273 /* Cursor to fetch the Legal Employer DA Office Codes */
274 /* Modified check on context for bug fix 4997786 */
275 CURSOR csr_get_le_da_off_codes(p_le_id NUMBER, p_effective_date DATE, p_business_group_id NUMBER) IS
276 SELECT hoi2.ORG_INFORMATION1 DA_SCODE
277 FROM HR_ORGANIZATION_UNITS hou
278 , HR_ORGANIZATION_INFORMATION hoi1
279 , HR_ORGANIZATION_INFORMATION hoi2
280 WHERE hou.business_group_id = p_business_group_id
281 and hoi1.organization_id = hou.organization_id
282 and hoi1.organization_id = p_le_id
283 and hoi1.ORG_INFORMATION_CONTEXT='CLASS'
284 and hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
285 and hoi1.ORG_INFORMATION2 = 'Y'
286 -- For bug fix 4997786
287 and hoi2.ORG_INFORMATION_CONTEXT= 'DK_DA_OFFICE_CODE' --'DK_EMPLOYMENT_DEFAULTS'
288 and hoi2.organization_id = hoi1.organization_id
289 and p_effective_date BETWEEN hou.DATE_FROM and nvl(hou.DATE_TO, p_effective_date);
290
291
292 /* Cursor to fetch the Business Group Details */
293 CURSOR csr_get_bg_details(p_business_group_id NUMBER, p_effective_date DATE) IS
294 SELECT hoi2.ORG_INFORMATION1 DA_SYS_NO
295 FROM HR_ORGANIZATION_UNITS hou
296 , HR_ORGANIZATION_INFORMATION hoi1
297 , HR_ORGANIZATION_INFORMATION hoi2
298 WHERE hou.business_group_id = p_business_group_id
299 and hoi1.organization_id = hou.organization_id
300 and hoi1.organization_id = p_business_group_id
301 and hoi1.ORG_INFORMATION_CONTEXT='CLASS'
302 and hoi1.org_information1 = 'HR_BG'
303 and hoi1.ORG_INFORMATION2 = 'Y'
304 and hoi2.ORG_INFORMATION_CONTEXT='DK_IDENTIFICATION_CODES'
305 and hoi2.organization_id = hoi1.organization_id
306 and p_effective_date BETWEEN hou.DATE_FROM and nvl(hou.DATE_TO, p_effective_date);
307
308
309 rec_sender_details csr_get_sender_details%ROWTYPE;
310 rec_le_details csr_get_le_details%ROWTYPE;
311 rec_get_le_da_off_codes csr_get_le_da_off_codes%ROWTYPE;
312 rec_bg_details csr_get_bg_details%ROWTYPE;
313
314
315 BEGIN
316
317 IF g_debug THEN
318 hr_utility.set_location(' Entering Procedure RANGE_CODE',10);
319 END IF;
320
321
322 -- The sql string to return
323 sqlstr := 'SELECT DISTINCT person_id
324 FROM per_people_f ppf
325 ,pay_payroll_actions ppa
326 WHERE ppa.payroll_action_id = :payroll_action_id
327 AND ppa.business_group_id = ppf.business_group_id
328 ORDER BY ppf.person_id';
329
330 -- Fetch the input parameter values
331 PAY_DK_ABSR_PKG.GET_ALL_PARAMETERS(
332 pactid
333 ,l_business_group_id
334 ,l_payroll_id
335 ,l_sender_id
336 ,l_year
337 ,l_effective_date
338 ,l_report_end_date
339 ,l_archive) ;
340
341
342 /* To set Character Set and Format */
343 l_from_date := to_char(to_date(l_year||'0101', 'YYYYMMDD'),'YYYYMMDD');
344 l_to_date := to_char(to_date(l_year||'1231', 'YYYYMMDD'),'YYYYMMDD');
345
346
347 l_char_set := '3';
348 l_format := '1';
349
350 /* To obtain Sender's details */
351 /* The Sender would be Service Provider if present in the system or else it would be the Legal Employer Specified */
352 /*
353 OPEN csr_get_sender_details(l_sender_id,fnd_date.canonical_to_date(l_to_date),l_business_group_id);
354 FETCH csr_get_sender_details INTO rec_sender_details;
355 CLOSE csr_get_sender_details;
356
357 l_sender_cvr_no := rec_sender_details.cvr_no;
358 l_sender_name := rec_sender_details.name;
359 l_sender_addr := rec_sender_details.addr;
360 l_sender_pcode := rec_sender_details.pcode;
361 */
362 /* To obtain Business Group details */
363
364 OPEN csr_get_bg_details(l_business_group_id,fnd_date.canonical_to_date(l_to_date));
365 FETCH csr_get_bg_details INTO rec_bg_details;
366 CLOSE csr_get_bg_details;
367
368 l_bg_da_sys_no := rec_bg_details.da_sys_no;
369 l_sys_name := GET_LOOKUP_MEANING ('DK_STATSR_LABELS','OP');
370 IF l_bg_da_sys_no IS NULL
371 THEN
372 RAISE e_no_da_sys_no;
373 END IF;
374
375 FOR rec_le_details IN csr_get_le_details(l_sender_id,l_sender_cvr_no,fnd_date.canonical_to_date(l_to_date),l_business_group_id)
376 LOOP
377 /* To obtain Legal Employer's details from details provided in File Header*/
378
379
380 l_le_cvr_no := rec_le_details.cvr_no;
381 l_le_ds_wpcode := rec_le_details.ds_wpcode;
382 l_le_name := rec_le_details.name;
383 l_le_addr := rec_le_details.addr;
384 l_le_pcode := rec_le_details.pcode;
385 l_le_id := rec_le_details.org_id;
386 l_le_punit := rec_le_details.punit;
387
388
389 OPEN csr_get_le_da_off_codes(l_le_id,fnd_date.canonical_to_date(l_to_date),l_business_group_id);
390 FETCH csr_get_le_da_off_codes INTO rec_get_le_da_off_codes;
391 CLOSE csr_get_le_da_off_codes;
392
393 l_le_da_scode := rec_get_le_da_off_codes.da_scode;
394
395 pay_action_information_api.create_action_information
396 (
397 p_action_information_id => l_action_info_id -- out parameter
398 ,p_object_version_number => l_ovn -- out parameter
399 ,p_action_context_id => pactid -- context id = payroll action id (of Archive)
400 ,p_action_context_type => 'PA' -- context type
401 ,p_effective_date => l_effective_date -- Date of Running the Archive
402 ,p_action_information_category => 'EMEA REPORT DETAILS' -- Information Category
403 ,p_tax_unit_id => l_le_id -- Legal Employer ID
404 ,p_jurisdiction_code => NULL -- Tax Municipality ID
405 ,p_action_information1 => 'PYDKASORA' -- Conc Prg Short Name
406 ,p_action_information2 => l_business_group_id -- Business Group ID
407 ,p_action_information3 => l_payroll_id -- Payroll ID
408 ,p_action_information4 => 'HDR' -- Specifies data is for File Header
409 ,p_action_information5 => l_year -- Span of report
410 ,p_action_information6 => l_from_date -- Report's from date
411 ,p_action_information7 => l_to_date -- Report's to date
412 ,p_action_information8 => l_char_set -- Character Set
413 ,p_action_information9 => l_format -- Format used
414 ,p_action_information10 => l_le_cvr_no -- LE's CVR number
415 ,p_action_information11 => l_le_name -- LE's Name
416 ,p_action_information12 => l_le_addr -- LE's Address
417 ,p_action_information13 => l_le_pcode -- LE's Postal Code
418 ,p_action_information14 => l_bg_da_sys_no -- BG's DA System Number
419 ,p_action_information15 => l_sys_name -- Payroll System Name
420 ,p_action_information16 => l_le_ds_wpcode -- LE's DS Workplace Code
421 ,p_action_information17 => l_le_da_scode -- LE's DA Society Code
422 ,p_action_information18 => l_le_punit -- LE's Production Unit Code
423 );
424
425 END LOOP;
426
427 IF g_debug THEN
428 hr_utility.set_location(' Leaving Procedure RANGE_CODE',20);
429 END IF;
430
431 EXCEPTION WHEN e_no_da_sys_no THEN
432 fnd_message.set_name('PAY','PAY_377058_DK_NO_DA_CODE_ERR');
433 fnd_file.put_line(fnd_file.log,fnd_message.get);
434 error_message:=fnd_concurrent.set_completion_status('ERROR','PAY_377058_DK_NO_DA_CODE_ERR');
435 END RANGE_CODE;
436
437
438 --------------------------------------------------------------------------------------
439 -- ASSIGNMENT_ACTION_CODE to create the assignment actions to be processed.
440 --------------------------------------------------------------------------------------
441
442 PROCEDURE ASSIGNMENT_ACTION_CODE
443 (p_payroll_action_id IN NUMBER
444 ,p_start_person IN NUMBER
445 ,p_end_person IN NUMBER
446 ,p_chunk IN NUMBER)
447 IS
448
449 /* Cursor to fetch useful header information to transfer to body records from already archived header information */
450 CURSOR csr_get_hdr_info(p_payroll_action_id NUMBER) IS
451 SELECT effective_date
452 ,fnd_date.CANONICAL_TO_DATE(action_information6) from_date
453 ,fnd_date.CANONICAL_TO_DATE(action_information7) to_date
454 ,to_number(action_information2) business_group_id
455 ,tax_unit_id
456 ,to_number(action_information3) PAYROLL_ID
457 ,action_information8 LE_NAME
458 ,action_information18 LE_PUNIT --8840262
459 ,action_information17 LE_DA_OFFICE_CODE
460 ,action_information10 LE_CVR_NUMBER
461 FROM pay_action_information pai
462 WHERE action_context_type = 'PA'
463 AND action_context_id = p_payroll_action_id
464 AND action_information_category = 'EMEA REPORT DETAILS'
465 AND action_information1 = 'PYDKASORA'
466 AND action_information4 = 'HDR';
467
468
469 /* Cursor to fetch the Legal Employer level Employment Defaults */
470 CURSOR csr_get_le_emp_dflts(p_le_id NUMBER, p_effective_date DATE, p_business_group_id NUMBER) IS
471 SELECT hoi2.ORG_INFORMATION1 COND_OF_EMP
472 ,hoi2.ORG_INFORMATION2 EMP_GRP
473 ,hoi2.ORG_INFORMATION3 WORK_HOURS
474 ,hoi2.ORG_INFORMATION4 FREQ
475 FROM HR_ORGANIZATION_UNITS hou
476 , HR_ORGANIZATION_INFORMATION hoi1
477 , HR_ORGANIZATION_INFORMATION hoi2
478 WHERE hou.business_group_id = p_business_group_id
479 and hoi1.organization_id = hou.organization_id
480 and hoi1.organization_id = p_le_id
481 and hoi1.ORG_INFORMATION_CONTEXT='CLASS'
482 and hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
483 and hoi1.ORG_INFORMATION2 = 'Y'
484 and hoi2.ORG_INFORMATION_CONTEXT='DK_EMPLOYMENT_DEFAULTS'
485 and hoi2.organization_id = hoi1.organization_id
486 and p_effective_date BETWEEN hou.DATE_FROM and nvl(hou.DATE_TO, p_effective_date);
487
488
489 /* Cursor to fetch the Legal Employer level Holiday Entitlement */
490 CURSOR csr_get_hol_entit(p_le_id NUMBER, p_effective_date DATE, p_business_group_id NUMBER) IS
491 SELECT hoi2.ORG_INFORMATION1 DEFAULT_WORK_PATT
492 ,hoi2.ORG_INFORMATION3 HOURLY_ACCR_RATE
493 ,hoi2.ORG_INFORMATION4 SAL_ALLOW_RATE
494 FROM HR_ORGANIZATION_UNITS hou
495 , HR_ORGANIZATION_INFORMATION hoi1
496 , HR_ORGANIZATION_INFORMATION hoi2
497 WHERE hou.business_group_id = p_business_group_id
498 and hoi1.organization_id = hou.organization_id
499 and hoi1.organization_id = p_le_id
500 and hoi1.ORG_INFORMATION_CONTEXT='CLASS'
501 and hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
502 and hoi1.ORG_INFORMATION2 = 'Y'
503 and hoi2.ORG_INFORMATION_CONTEXT='DK_HOLIDAY_ENTITLEMENT_INFO'
504 and hoi2.organization_id = hoi1.organization_id
505 and p_effective_date BETWEEN hou.DATE_FROM and nvl(hou.DATE_TO, p_effective_date);
506
507 /* Cursor to fetch the HR Org level Production Unit Code*/
508 CURSOR csr_get_hr_org_info(hr_org_id hr_organization_information.organization_id%type)
509 IS
510 SELECT hoi2.ORG_INFORMATION6
511 FROM hr_organization_information hoi1
512 , hr_organization_information hoi2
513 WHERE hoi1.organization_id = hoi2.organization_id
514 AND hoi1.organization_id = hr_org_id
515 AND hoi1.org_information1 = 'HR_ORG'
516 AND hoi1.org_information_context = 'CLASS'
517 AND hoi2.ORG_INFORMATION_CONTEXT='DK_EMPLOYMENT_DEFAULTS';
518
519 rg_csr_get_hr_org_info csr_get_hr_org_info%rowtype;
520
521 /* Cursor to fetch the Assgt location level Production Unit Code*/
522 CURSOR csr_location_info (p_location_id hr_location_extra_info.location_id%TYPE) IS
523 SELECT lei_information1
524 FROM hr_location_extra_info
525 WHERE location_id = p_location_id
526 AND information_type='DK_LOCATION_INFO';
527
528 rg_csr_location_info csr_location_info%ROWTYPE;
529
530 /* Cursor to fetch the Assignments, on which pre-payments has been completed */
531 CURSOR csr_assignments
532 ( p_payroll_action_id NUMBER
533 ,p_payroll_id NUMBER
534 ,p_start_person NUMBER
535 ,p_end_person NUMBER
536 ,p_date_from DATE
537 ,p_date_to DATE
538 ,p_le_id NUMBER
539 ,p_effective_date DATE
540 ) IS
541 SELECT distinct
542 paaf.assignment_id ASG_ID
543 ,ppf.payroll_name PAYROLL_NAME
544 ,paaf.assignment_number ASSIGNMENT_NUMBER
545 ,substr(to_char(papf.national_identifier),1,instr(to_char(papf.national_identifier),'-')-1)||substr(to_char(papf.national_identifier),instr(to_char(papf.national_identifier),'-')+1) CPR_NO
546 ,paaf.organization_id HR_ORG_ID
547 ,paaf.person_id PERSON_ID
548 ,paaf.location_id LOC_ID --8820009
549 ,papf.EMPLOYEE_NUMBER --8820009
550 FROM
551 per_all_people_f papf
552 ,per_all_assignments_f paaf
553 ,pay_payrolls_f ppf
554 ,hr_soft_coding_keyflex scl
555 --,pay_assignment_actions paa
556 --,pay_payroll_actions ppa
557 WHERE paaf.person_id BETWEEN p_start_person AND p_end_person
558 AND papf.PERSON_ID = paaf.PERSON_ID
559 AND ppf.payroll_id = nvl(p_payroll_id,ppf.payroll_id)
560 AND paaf.payroll_id = ppf.payroll_id
561 AND paaf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
562 AND scl.enabled_flag = 'Y'
563 /*
564 AND paa.assignment_id = paaf.assignment_id
565 AND ppa.payroll_action_id = paa.payroll_action_id
566 AND paa.action_status = 'C' -- Completed
567 AND ppa.action_type IN ('P','U') -- Pre-Payments
568 AND ppa.effective_date BETWEEN p_date_from AND p_date_to
569 AND ppa.effective_date <= paaf.EFFECTIVE_END_DATE
570 AND ppa.effective_date >= paaf.EFFECTIVE_start_DATE
571 */
572 AND paaf.EFFECTIVE_START_DATE <= p_date_to
573 AND paaf.EFFECTIVE_END_DATE >= p_date_from
574 AND papf.current_employee_flag = 'Y'
575 AND scl.segment1 = to_char(p_le_id)
576 ORDER BY asg_id, person_id;
577
578 /*
579 CURSOR csr_asg_end(
580 p_assignment_id1 NUMBER
581 ,p_date_from1 DATE
582 ,p_date_to1 DATE
583 ,p_job_occ_mkode VARCHAR2
584 ,p_job_status_mkode VARCHAR2
585 ,p_sal_basis_mkode VARCHAR2
586 ,p_time_off_lieu VARCHAR2
587 ,p_pre_asg_end_date DATE
588 ,p_loc_id NUMBER
589 ) is
590 select
591 paaf.effective_start_date ASG_START_DATE
592 ,paaf.effective_end_date ASG_END_DATE
593 FROM
594 per_all_assignments_f paaf
595 ,hr_soft_coding_keyflex scl
596 where
597 paaf.assignment_id = p_assignment_id1
598 AND paaf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
599 AND scl.enabled_flag = 'Y'
600 AND paaf.EFFECTIVE_START_DATE <= p_date_to1
601 AND paaf.EFFECTIVE_START_DATE >= p_pre_asg_end_date
602 AND paaf.EFFECTIVE_END_DATE >= p_date_from1
603 AND (scl.SEGMENT14 <> p_job_occ_mkode or scl.SEGMENT15 <> p_job_status_mkode
604 or scl.SEGMENT16 <> p_sal_basis_mkode or scl.SEGMENT17 <> p_time_off_lieu
605 or paaf.location_id <> p_loc_id);
606 csr_asg_end_check csr_asg_end%ROWTYPE;
607 */
608
609 rec_hdr_info csr_get_hdr_info%ROWTYPE;
610 rec_get_le_emp_dflts csr_get_le_emp_dflts%ROWTYPE;
611 rec_get_hol_entit csr_get_hol_entit%ROWTYPE;
612
613 --8766712 condition (Splitting the absence year wise)
614 CURSOR csr_abs(p_person_id NUMBER
615 ,p_business_group_id NUMBER
616 ,p_date_from DATE
617 ,p_date_to DATE
618 ,p_asg_id NUMBER)
619 IS
620 select paat.absence_attendance_id, paat.absence_attendance_type_id,
621 GREATEST(paat.date_start,p_date_from) date_start, LEAST(paat.date_end,p_date_to) date_end,
622 paat.date_start actual_date_start, paat.date_end actual_date_end,
623 replace(paat.time_start,':','.') time_start, replace(paat.time_end,':','.') time_end,
624 paatv.information1 ABS_CODE,
625 paatv.NAME
626 FROM
627 PER_ABSENCE_ATTENDANCES paat,
628 PER_ABSENCE_ATTENDANCE_TYPES paatv
629 where
630 paat.PERSON_ID = p_person_id
631 and paat.business_group_id = p_business_group_id
632 and paat.business_group_id = paatv.business_group_id
633 and paat.absence_attendance_type_id = paatv.absence_attendance_type_id
634 and nvl(paatv.information_category,'DK') = 'DK' --8789760
635 and (paat.date_start between p_date_from AND p_date_to
636 OR paat.date_end between p_date_from AND p_date_to
637 )
638 and exists
639 (
640 select 1
641 from
642 pay_element_types_f petf, --8917251
643 pay_element_entries_f peef,
644 pay_element_entry_values_f peevf
645 where
646 peef.assignment_id = p_asg_id
647 and petf.element_type_id = peef.element_type_id --8917251
648 and petf.business_group_id is NULL --8917251
649 and petf.legislation_code = 'DK' --8917251
650 and peef.element_entry_id = peevf.element_entry_id
651 and (peef.effective_start_date between p_date_from AND p_date_to
652 or peef.effective_end_date between p_date_from AND p_date_to)
653 and (peevf.effective_start_date between p_date_from AND p_date_to
654 or peevf.effective_end_date between p_date_from AND p_date_to)
655 and to_char(paat.absence_attendance_id) = peevf.screen_entry_value
656 )
657 --8917251
658 UNION
659 select paat.absence_attendance_id, paat.absence_attendance_type_id,
660 GREATEST(paat.date_start,p_date_from) date_start, LEAST(paat.date_end,p_date_to) date_end,
661 paat.date_start actual_date_start, paat.date_end actual_date_end,
662 replace(paat.time_start,':','.') time_start, replace(paat.time_end,':','.') time_end,
663 paatv.information1 ABS_CODE,
664 paatv.NAME
665 FROM
666 PER_ABSENCE_ATTENDANCES paat,
667 PER_ABSENCE_ATTENDANCE_TYPES paatv
668 where
669 paat.PERSON_ID = p_person_id
670 and paat.business_group_id = p_business_group_id
671 and paat.business_group_id = paatv.business_group_id
672 and paat.absence_attendance_type_id = paatv.absence_attendance_type_id
673 and nvl(paatv.information_category,'DK') = 'DK' --8789760
674 and (paat.date_start between p_date_from AND p_date_to
675 OR paat.date_end between p_date_from AND p_date_to
676 )
677 and exists
678 (
679 select 1
680 from
681 pay_element_types_f petf,
682 pay_element_entries_f peef,
683 per_business_groups_perf pbg
684 where
685 peef.assignment_id = p_asg_id
686 and petf.element_type_id = peef.element_type_id
687 and peef.creator_type = 'A'
688 and petf.business_group_id is NOT NULL
689 and pbg.business_group_id = petf.business_group_id
690 and pbg.legislation_code = 'DK'
691 and (peef.effective_start_date between p_date_from AND p_date_to
692 or peef.effective_end_date between p_date_from AND p_date_to)
693 and to_char(paat.absence_attendance_id) = peef.creator_id
694 )
695 Order By 9 Asc;
696 --8917251
697
698 rec_csr_abs csr_abs%ROWTYPE;
699
700 cursor cur_global(p_effective_date DATE)
701 IS
702 select global_value
703 from ff_globals_f
704 where
705 global_name = 'DK_HOURS_IN_DAY'
706 AND p_effective_date between effective_start_date AND effective_end_date;
707
708 l_global_value ff_globals_f.global_value%type;
709
710 -- Variable Declarations
711
712 l_count NUMBER := 0;
713 l_action_info_id NUMBER;
714 l_ovn NUMBER;
715 l_actid NUMBER;
716 l_asgid NUMBER := -999;
717 l_perid NUMBER := -9999; --8766712
718
719 l_archive VARCHAR2(240);
720 l_payroll_id NUMBER;
721 l_le_id NUMBER;
722 l_le_name VARCHAR2(240);
723 l_effective_date DATE;
724 l_date_from DATE;
725 l_date_to DATE;
726 l_bg_id NUMBER;
727
728 l_loc_punit VARCHAR2(10);
729
730 l_le_cvr_no pay_action_information.action_information1%type;
731 l_le_da_scode pay_action_information.action_information1%type;
732 l_assg_no pay_action_information.action_information1%type;
733 l_cpr_no pay_action_information.action_information1%type;
734 l_abs_code pay_action_information.action_information1%type;
735 l_time_units NUMBER;
736 l_sign_units pay_action_information.action_information1%type;
737 l_abs_start_date date;
738 l_abs_end_date date;
739 l_punit pay_action_information.action_information1%type;
740 l_punit_code pay_action_information.action_information1%type; --8840262
741 l_org_punit pay_action_information.action_information1%type; --8840262
742
743 l_abs_start_time pay_action_information.action_information1%type;
744 l_abs_end_time pay_action_information.action_information1%type;
745
746 l_hours_rate NUMBER;
747 l_freq VARCHAR2(80);
748 l_day_max_hrs NUMBER;
749 l_old_mkode0600 VARCHAR2(80);
750
751 l_dimension VARCHAR2(80);
752 l_year VARCHAR2(80);
753 l_asg_id NUMBER;
754 l_mul_factor VARCHAR2(80);
755
756 l_chk_asg_end_date DATE;
757 /*Changes for Lunar Payroll */
758 l_lnr_payroll_period Varchar2(3);
759
760
761 e_too_many_hours EXCEPTION;
762 e_no_emp_dflts EXCEPTION;
763 error_message BOOLEAN;
764
765 -- nprasath Added for Multiple Records
766 l_old_job_occ_mkode VARCHAR2(40);
767 l_old_job_status_mkode VARCHAR2(40);
768 l_old_sal_basis_mkode VARCHAR2(40);
769 l_old_time_off_lieu VARCHAR2(40);
770
771 l_loc_id NUMBER;
772 l_hr_org_id NUMBER;
773 l_old_loc_id NUMBER;
774
775 l_return NUMBER;
776 l_duration NUMBER;
777
778 --8766712
779 Type tab_per_abs_rec is table of Number index by BINARY_INTEGER;
780 l_tab_per_abs_rec tab_per_abs_rec;
781
782 --
783 BEGIN
784 hr_utility.trace('Inside the Statistics Report');
785
786 IF g_debug THEN
787 hr_utility.set_location(' Entering Procedure ASSIGNMENT_ACTION_CODE',10);
788 END IF;
789
790 SELECT PAY_DK_ABSR_PKG.GET_PARAMETER(legislative_parameters,'YEAR')
791 INTO l_year
792 FROM pay_payroll_actions
793 WHERE payroll_action_id = p_payroll_action_id;
794
795
796 hr_utility.trace(' Before for loop csr_get_hdr_info ');
797 -- Fetch Header and Sub-header details
798 l_assg_no := NULL;
799 l_cpr_no := NULL;
800 l_abs_code := NULL;
801 l_time_units := NULL;
802 l_sign_units := NULL;
803 l_abs_start_date := NULL;
804 l_abs_end_date := NULL;
805 l_abs_start_time := NULL;
806 l_abs_end_time := NULL;
807 l_sign_units := '+';
808 --8840262
809 l_punit_code := NULL;
810 l_loc_punit := NULL;
811 l_org_punit := NULL;
812 --8840262
813
814 FOR rec_hdr_info IN csr_get_hdr_info(p_payroll_action_id)
815 LOOP
816
817 hr_utility.trace(' Inside for loop csr_get_hdr_info :');
818 l_le_id := rec_hdr_info.tax_unit_id;
819 hr_utility.trace(' l_le_id :'||l_le_id);
820 l_le_name := rec_hdr_info.le_name;
821 hr_utility.trace(' l_le_name :'||l_le_name);
822 l_payroll_id := rec_hdr_info.payroll_id;
823 hr_utility.trace(' l_payroll_id :'||l_payroll_id);
824 l_punit := rec_hdr_info.le_punit;
825 hr_utility.trace(' l_punit :'||l_punit);
826
827 hr_utility.trace('Variables Initialisations New ');
828
829 l_le_cvr_no := to_char(rec_hdr_info.LE_CVR_NUMBER);
830 l_le_da_scode := to_char(rec_hdr_info.LE_DA_OFFICE_CODE);
831
832 hr_utility.trace(' After Variables Initialisations. ');
833 /*
834 OPEN csr_get_hdr_info(p_payroll_action_id);
835 FETCH csr_get_hdr_info INTO rec_hdr_info;
836 CLOSE csr_get_hdr_info;
837 */
838
839 l_effective_date := rec_hdr_info.effective_date;
840 --l_date_from := to_date(rec_hdr_info.from_date);
841 --l_date_to := to_date(rec_hdr_info.to_date);
842 l_bg_id := rec_hdr_info.business_group_id;
843
844 --Fixed for gscc error
845 l_date_from := rec_hdr_info.from_date;
846 l_date_to := rec_hdr_info.to_date;
847
848 OPEN cur_global(l_effective_date);
849 FETCH cur_global INTO l_global_value;
850 CLOSE cur_global;
851 hr_utility.trace(' l_global_value :'||l_global_value);
852 -- Fetch Assignment's details
853
854 FOR csr_rec IN csr_assignments( p_payroll_action_id
855 ,l_payroll_id
856 ,p_start_person
857 ,p_end_person
858 ,l_date_from
859 ,l_date_to
860 ,l_le_id
861 ,l_effective_date )
862 LOOP
863
864 hr_utility.trace(' Inside loop csr_assignments');
865 l_loc_id := csr_rec.loc_id; --8820009
866 l_hr_org_id := csr_rec.hr_org_id;
867 /*
868 l_hourly_salaried := csr_rec.hourly_salaried_code;
869 IF l_hourly_salaried IS NULL THEN
870 IF csr_rec.payroll_period = 1 THEN
871 l_hourly_salaried := 'S';
872 ELSE
873 l_hourly_salaried := 'H';
874 END IF ;
875 END IF ;
876 */
877 hr_utility.trace(' l_asgid :'||l_asgid);
878 hr_utility.trace(' csr_rec.asg_id :'||csr_rec.asg_id);
879
880 IF (csr_rec.asg_id <> l_asgid) THEN
881
882 hr_utility.trace(' ***** Intializing the old variables **** ');
883
884 l_assg_no := NULL;
885 l_cpr_no := NULL;
886 l_abs_code := NULL;
887 l_time_units := NULL;
888 l_sign_units := NULL;
889 l_abs_start_date := NULL;
890 l_abs_end_date := NULL;
891 l_abs_start_time := NULL;
892 l_abs_end_time := NULL;
893 l_sign_units := '+';
894 --8840262
895 l_punit_code := NULL;
896 l_loc_punit := NULL;
897 l_org_punit := NULL;
898 --8840262
899
900 End if;
901
902 hr_utility.trace(' l_perid :'||l_perid);
903 hr_utility.trace(' csr_rec.person_id :'||csr_rec.person_id);
904 --8766712
905 IF l_perid <> csr_rec.person_id
906 THEN
907 --clear the pl_sql_table;
908 l_tab_per_abs_rec.DELETE;
909 END IF;
910
911 -- nprasath added for Multiple Records
912 IF (csr_rec.asg_id <> l_asgid)
913 /*
914 or (csr_rec.asg_id = l_asgid
915 and ( csr_rec.job_occ_mkode <> l_old_job_occ_mkode
916 or csr_rec.job_status_mkode <> l_old_job_status_mkode
917 or csr_rec.sal_basis_mkode <> l_old_sal_basis_mkode
918 or csr_rec.time_off_lieu <> l_old_time_off_lieu
919 or csr_rec.loc_id <> l_old_loc_id)
920 ) */
921 THEN
922
923 hr_utility.trace(' Inside if csr_rec.asg_id <> l_asgid');
924 FOR rec_csr_abs IN csr_abs(csr_rec.person_id
925 ,l_bg_id
926 ,l_date_from
927 ,l_date_to
928 ,csr_rec.asg_id)
929 LOOP
930
931 --8766712
932 IF NOT l_tab_per_abs_rec.EXISTS(rec_csr_abs.absence_attendance_id) THEN
933 l_tab_per_abs_rec(rec_csr_abs.absence_attendance_id) := csr_rec.person_id;
934
935 hr_utility.trace(' Inside IF NOT l_tab_per_abs_rec.EXISTS');
936
937 l_assg_no := csr_rec.ASSIGNMENT_NUMBER;
938 l_cpr_no := csr_rec.CPR_NO;
939 l_abs_code := rec_csr_abs.ABS_CODE;
940 l_abs_start_date := rec_csr_abs.date_start;
941 l_abs_end_date := rec_csr_abs.date_end;
942
943 l_abs_start_time := rec_csr_abs.time_start;
944 l_abs_end_time := rec_csr_abs.time_end;
945
946 IF l_abs_start_time IS NOT NULL OR l_abs_end_time IS NOT NULL THEN
947
948 hr_utility.trace(' Inside Hour hr_loc_work_schedule');
949
950 l_return := hr_loc_work_schedule.calc_sch_based_dur ( csr_rec.asg_id ,
951 'H' ,
952 'Y' ,
953 l_abs_start_date ,
954 l_abs_end_date ,
955 l_abs_start_time ,
956 l_abs_end_time ,
957 l_duration );
958
959 l_duration := l_duration * 100 ;
960
961 ELSE
962
963 hr_utility.trace(' Inside Day hr_loc_work_schedule');
964
965 l_return := hr_loc_work_schedule.calc_sch_based_dur ( csr_rec.asg_id ,
966 'D' ,
967 'Y' ,
968 l_abs_start_date ,
969 l_abs_end_date ,
970 '00.01' ,
971 '23.59' ,
972 l_duration );
973
974 l_duration := l_duration * NVL(l_global_value,7.4) * 100 ;
975
976 END IF;
977
978 l_time_units := l_duration;
979
980 hr_utility.trace(' l_time_units:'||l_time_units);
981
982 IF l_time_units < 0
983 THEN
984 l_sign_units := '-';
985 ELSE
986 l_sign_units := '+';
987 END IF;
988
989 hr_utility.trace(' Inside if csr_rec.asg_id <> l_asgid after variable initialized');
990
991 BEGIN
992 SELECT pay_assignment_actions_s.NEXTVAL INTO l_actid FROM dual;
993 EXCEPTION
994 WHEN OTHERS THEN
995 NULL ;
996 END ;
997 -- Create the archive assignment action
998 hr_nonrun_asact.insact(l_actid ,csr_rec.asg_id ,p_payroll_action_id ,p_chunk,null);
999
1000 hr_utility.trace('After hr_nonrun_asact.insact');
1001
1002 hr_utility.trace(' csr_rec.asg_id l_loc_punit:'||l_loc_punit||'-'||csr_rec.asg_id );
1003 hr_utility.trace(' csr_rec.asg_id l_org_punit:'||l_org_punit||'-'||csr_rec.asg_id );
1004 hr_utility.trace(' csr_rec.asg_id l_punit:'||l_punit||'-'||csr_rec.asg_id );
1005 hr_utility.trace(' csr_rec.asg_id l_punit_code:'||l_punit_code||'-'||csr_rec.asg_id );
1006 --8820009
1007 IF l_loc_id IS NOT NULL THEN
1008 OPEN csr_location_info (l_loc_id);
1009 FETCH csr_location_info INTO rg_csr_location_info;
1010 IF csr_location_info%FOUND THEN
1011 l_loc_punit := rg_csr_location_info.lei_information1;
1012 hr_utility.trace(' csr_rec.asg_id l_loc_punit:'||l_loc_punit||'-'||csr_rec.asg_id );
1013 END IF;
1014 CLOSE csr_location_info;
1015 END IF;
1016 --8840262
1017 IF l_hr_org_id IS NOT NULL THEN
1018 OPEN csr_get_hr_org_info (l_hr_org_id);
1019 FETCH csr_get_hr_org_info INTO rg_csr_get_hr_org_info;
1020 IF csr_get_hr_org_info%FOUND THEN
1021 l_org_punit := rg_csr_get_hr_org_info.org_information6;
1022 hr_utility.trace(' csr_rec.asg_id l_loc_punit:'||l_org_punit||'-'||csr_rec.asg_id );
1023 END IF;
1024 CLOSE csr_get_hr_org_info;
1025 END IF;
1026
1027 IF l_loc_punit IS NOT NULL THEN
1028 l_punit_code := l_loc_punit;
1029 ELSIF l_org_punit IS NOT NULL THEN
1030 l_punit_code := l_org_punit;
1031 ELSE
1032 l_punit_code := l_punit;
1033 END IF;
1034
1035 /*
1036 IF l_loc_id IS NULL OR l_loc_punit IS NULL THEN
1037 OPEN csr_get_hr_org_info (l_hr_org_id);
1038 FETCH csr_get_hr_org_info INTO rg_csr_get_hr_org_info;
1039 IF csr_get_hr_org_info%FOUND THEN
1040 l_punit := nvl(rg_csr_get_hr_org_info.org_information6, l_punit);
1041 END IF;
1042 CLOSE csr_get_hr_org_info;
1043 ELSE
1044 l_punit := l_loc_punit;
1045 END IF;
1046 */
1047 --8840262
1048 --8820009
1049 hr_utility.trace(' csr_rec.asg_id l_loc_punit2:'||l_loc_punit||'-'||csr_rec.asg_id );
1050 hr_utility.trace(' csr_rec.asg_id l_punit2:'||l_punit||'-'||csr_rec.asg_id );
1051 hr_utility.trace(' csr_rec.asg_id l_loc_id2:'||l_loc_id||'-'||csr_rec.asg_id );
1052
1053 hr_utility.trace('l_punit: '||l_punit );
1054
1055 IF l_time_units <> 0 AND l_abs_code IS NOT NULL THEN --8789760
1056
1057 hr_utility.trace(' creating pay_action_information_api.create_action_information ');
1058
1059 pay_action_information_api.create_action_information
1060 ( p_action_information_id => l_action_info_id -- OUT parameter
1061 ,p_object_version_number => l_ovn -- OUT parameter
1062 ,p_action_context_id => l_actid -- Context id = assignment action id (of Archive)
1063 ,p_action_context_type => 'AAP' -- Context type
1064 ,p_effective_date => l_effective_date -- Date of running the archive
1065 ,p_assignment_id => csr_rec.asg_id -- Assignment ID
1066 ,p_action_information_category => 'EMEA REPORT INFORMATION' -- Information Category
1067 ,p_tax_unit_id => l_le_id -- Legal Employer ID
1068 ,p_jurisdiction_code => NULL -- Tax Municipality ID
1069 ,p_action_information1 => 'PYDKASORA' -- Con Program Short Name
1070 ,p_action_information2 => csr_rec.payroll_name -- Payroll Name
1071 ,p_action_information3 => p_payroll_action_id -- Payroll action id (of Archive)
1072 ,p_action_information4 => csr_rec.assignment_number -- Assignment Number
1073 ,p_action_information5 => csr_rec.cpr_no -- CPR Number of Employee
1074 ,p_action_information6 => 'ABS_DETAILS' -- Archive part 1 on context AAP
1075 ,p_action_information7 => l_le_cvr_no --LE's CVR Number
1076 ,p_action_information8 => l_le_da_scode --LE's DA Office Code
1077 ,p_action_information9 => l_assg_no --Assignment Number
1078 ,p_action_information10 => l_cpr_no --CPR number
1079 ,p_action_information11 => l_abs_code --Absence Code
1080 ,p_action_information12 => l_time_units --Time Unit (Number of hours)
1081 ,p_action_information13 => l_sign_units --Sign of the time unit (+/-)
1082 ,p_action_information14 => to_char(l_abs_start_date, 'YYYYMMDD') --Start date of Absence(YYYYMMDD)
1083 ,p_action_information15 => to_char(l_abs_end_date, 'YYYYMMDD') --End date of Absence(YYYYMMDD)
1084 ,p_action_information16 => nvl(l_punit_code,l_punit) --LE's P Unit Code --8840262
1085 ,p_action_information17 => l_abs_start_time --Start time of Absence
1086 ,p_action_information18 => l_abs_end_time --End time of Absence
1087 );
1088 hr_utility.trace('After pay_action_information_api.create_action_information ' );
1089
1090 END IF;
1091
1092 --8789760
1093 IF l_abs_code IS NULL THEN
1094 fnd_message.set_name('PER','HR_377107_DK_ABSENCE_REPORT');
1095 fnd_message.set_token('PER_ID',csr_rec.employee_number); --8820009
1096 fnd_message.set_token('ABS_TYPE',rec_csr_abs.NAME);
1097 Fnd_file.put_line(FND_FILE.LOG,fnd_message.get);
1098
1099 error_message := FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING',
1100 'Report completed with warning(s).');
1101 END IF;
1102
1103 END IF; --8766712
1104
1105 END LOOP;
1106
1107 l_abs_code := NULL;
1108 l_time_units := NULL;
1109 l_sign_units := NULL;
1110 l_abs_start_date := NULL;
1111 l_abs_end_date := NULL;
1112 l_abs_start_time := NULL;
1113 l_abs_end_time := NULL;
1114 l_sign_units := '+';
1115 --8840262
1116 l_punit_code := NULL;
1117 l_loc_punit := NULL;
1118 l_org_punit := NULL;
1119 --8840262
1120
1121 END IF;
1122
1123 l_asgid := csr_rec.asg_id;
1124 l_perid := csr_rec.person_id; --8766712
1125 /*
1126 l_old_job_occ_mkode := csr_rec.job_occ_mkode;
1127 l_old_job_status_mkode := csr_rec.job_status_mkode;
1128 l_old_sal_basis_mkode := csr_rec.sal_basis_mkode;
1129 l_old_time_off_lieu := csr_rec.time_off_lieu;
1130 l_old_loc_id := csr_rec.loc_id;
1131 */
1132
1133 END LOOP;
1134
1135 END LOOP;
1136
1137
1138 IF g_debug THEN
1139 hr_utility.set_location(' Leaving Procedure ASSIGNMENT_ACTION_CODE',20);
1140 END IF;
1141
1142 EXCEPTION
1143 WHEN e_too_many_hours THEN
1144 fnd_message.set_name('PAY','PAY_377033_DK_TOO_MANY_WKG_HRS');
1145 fnd_file.put_line(fnd_file.log,fnd_message.get);
1146 error_message:=fnd_concurrent.set_completion_status('ERROR','PAY_377033_DK_TOO_MANY_WKG_HRS');
1147
1148 WHEN e_no_emp_dflts THEN
1149 fnd_message.set_name('PAY','PAY_377061_DK_NO_LE_EMP_DFLTS');
1150 fnd_message.set_token('ITEM',l_le_name);
1151 fnd_file.put_line(fnd_file.log,substr(fnd_message.get,1,254));
1152 error_message:=fnd_concurrent.set_completion_status('ERROR','PAY_377061_DK_NO_LE_EMP_DFLTS');
1153
1154 END ASSIGNMENT_ACTION_CODE;
1155
1156
1157 PROCEDURE INITIALIZATION_CODE(p_payroll_action_id IN NUMBER)
1158 IS
1159
1160
1161 BEGIN
1162
1163 NULL;
1164
1165 IF g_debug THEN
1166 hr_utility.set_location(' Entering Procedure INITIALIZATION_CODE',10);
1167 END IF;
1168
1169
1170 IF g_debug THEN
1171 hr_utility.set_location(' Leaving Procedure INITIALIZATION_CODE',20);
1172 END IF;
1173
1174 EXCEPTION WHEN OTHERS THEN
1175 g_err_num := SQLCODE;
1176
1177 IF g_debug THEN
1178 hr_utility.set_location('ORA_ERR: ' || g_err_num || 'In INITIALIZATION_CODE',30);
1179 END IF;
1180
1181 END INITIALIZATION_CODE;
1182
1183
1184
1185 /* ARCHIVE CODE */
1186 PROCEDURE ARCHIVE_CODE(p_assignment_action_id IN NUMBER
1187 ,p_effective_date IN DATE)
1188 IS
1189
1190 BEGIN
1191 IF g_debug THEN
1192 hr_utility.set_location(' Entering Procedure ARCHIVE_CODE',10);
1193 END IF;
1194
1195 IF g_debug THEN
1196 hr_utility.set_location(' Leaving Procedure ARCHIVE_CODE',20);
1197 END IF;
1198
1199 END ARCHIVE_CODE;
1200
1201
1202 PROCEDURE POPULATE_DATA
1203 (p_business_group_id IN NUMBER,
1204 p_payroll_action_id IN VARCHAR2 ,
1205 p_template_name IN VARCHAR2,
1206 p_xml OUT NOCOPY CLOB)
1207 IS
1208
1209 /* Cursor to fetch File Start and End Record Information */
1210 CURSOR csr_get_hdr_info(p_payroll_action_id NUMBER)
1211 IS
1212 SELECT *
1213 FROM pay_action_information pai
1214 WHERE action_context_type = 'PA'
1215 AND action_context_id = p_payroll_action_id
1216 AND action_information_category = 'EMEA REPORT DETAILS'
1217 AND action_information1 = 'PYDKASORA'
1218 AND action_information4 = 'HDR';
1219
1220
1221 /* Cursors to fetch Personal and Salary Record Information */
1222 CURSOR csr_get_body_info(p_payroll_action_id NUMBER, p_tax_unit_id NUMBER)
1223 IS
1224 SELECT pai.*
1225 FROM
1226 pay_payroll_actions ppa,
1227 Pay_assignment_actions paa,
1228 pay_action_information pai
1229 WHERE
1230 ppa.payroll_action_id = p_payroll_action_id
1231 AND ppa.payroll_action_id = paa.payroll_action_id
1232 AND paa.assignment_action_id = pai.action_context_id
1233 AND pai.action_context_type = 'AAP'
1234 AND pai.action_information_category = 'EMEA REPORT INFORMATION'
1235 AND pai.action_information1 = 'PYDKASORA'
1236 AND pai.action_information6 = 'ABS_DETAILS'
1237 AND pai.action_information3 = to_char(p_payroll_action_id)
1238 AND pai.tax_unit_id = p_tax_unit_id;
1239
1240
1241 rec_get_hdr_info csr_get_hdr_info%ROWTYPE;
1242 rec_get_body_info csr_get_body_info%ROWTYPE;
1243
1244 l_counter NUMBER := 0;
1245 l_le_count NUMBER := 0;
1246 l_payroll_action_id NUMBER;
1247
1248 BEGIN
1249
1250 IF p_payroll_action_id IS NULL THEN
1251 BEGIN
1252 SELECT payroll_action_id
1253 INTO l_payroll_action_id
1254 FROM pay_payroll_actions ppa,
1255 fnd_conc_req_summary_v fcrs,
1256 fnd_conc_req_summary_v fcrs1
1257 WHERE fcrs.request_id = fnd_global.conc_request_id
1258 AND fcrs.priority_request_id = fcrs1.priority_request_id
1259 AND ppa.request_id between fcrs1.request_id and fcrs.request_id
1260 AND ppa.request_id = fcrs1.request_id;
1261
1262 EXCEPTION
1263 WHEN OTHERS THEN
1264 NULL;
1265 END ;
1266
1267 ELSE
1268
1269 l_payroll_action_id :=p_payroll_action_id;
1270
1271 END IF;
1272 hr_utility.set_location('Entered Procedure GETDATA',10);
1273
1274 /* Get the File Start and End Record Information */
1275 OPEN csr_get_hdr_info(l_payroll_action_id);
1276 FETCH csr_get_hdr_info INTO rec_get_hdr_info;
1277 CLOSE csr_get_hdr_info;
1278
1279 hr_utility.set_location('Before populating pl/sql table',20);
1280
1281 ----------------------------------HEADER
1282
1283 xml_tab(l_counter).TagName :='FILE_HEADER_FOOTER_START';
1284 xml_tab(l_counter).TagValue :='FILE_HEADER_FOOTER_START';
1285 l_counter := l_counter + 1;
1286
1287 xml_tab(l_counter).TagName :='CHAR_SET';
1288 xml_tab(l_counter).TagValue := rec_get_hdr_info.action_information8;
1289 l_counter := l_counter + 1;
1290
1291 xml_tab(l_counter).TagName :='FILE_FORMAT';
1292 xml_tab(l_counter).TagValue := rec_get_hdr_info.action_information9;
1293 l_counter := l_counter + 1;
1294
1295 xml_tab(l_counter).TagName :='COMPANY_CVR_NO';
1296 xml_tab(l_counter).TagValue := rec_get_hdr_info.action_information10;
1297 l_counter := l_counter + 1;
1298
1299 xml_tab(l_counter).TagName :='COMPANY_NAME';
1300 xml_tab(l_counter).TagValue := rec_get_hdr_info.action_information11;
1301 l_counter := l_counter + 1;
1302
1303 xml_tab(l_counter).TagName :='COMPANY_ADDR';
1304 xml_tab(l_counter).TagValue := rec_get_hdr_info.action_information12;
1305 l_counter := l_counter + 1;
1306
1307 xml_tab(l_counter).TagName :='COMPANY_PCODE';
1308 xml_tab(l_counter).TagValue := rec_get_hdr_info.action_information13;
1309 l_counter := l_counter + 1;
1310
1311 FOR i IN csr_get_body_info(l_payroll_action_id, rec_get_hdr_info.tax_unit_id)
1312 LOOP
1313 xml_tab(l_counter).TagName :='ABSENCE_RECO_START';
1314 xml_tab(l_counter).TagValue :='ABSENCE_RECO_START';
1315 l_counter := l_counter + 1;
1316
1317 xml_tab(l_counter).TagName :='LE_CVR_NO';
1318 xml_tab(l_counter).TagValue := i.action_information7;
1319 l_counter := l_counter + 1;
1320
1321 xml_tab(l_counter).TagName :='LE_DA_SCODE';
1322 xml_tab(l_counter).TagValue := i.action_information8;
1323 l_counter := l_counter + 1;
1324
1325 xml_tab(l_counter).TagName :='ASSG_NO';
1326 xml_tab(l_counter).TagValue := i.action_information9;
1327 l_counter := l_counter + 1;
1328
1329 xml_tab(l_counter).TagName :='CPR_NO';
1330 xml_tab(l_counter).TagValue := i.action_information10;
1331 l_counter := l_counter + 1;
1332
1333 xml_tab(l_counter).TagName :='ABS_CODE';
1334 xml_tab(l_counter).TagValue := i.action_information11;
1335 l_counter := l_counter + 1;
1336
1337 xml_tab(l_counter).TagName :='TIME_UNITS';
1338 xml_tab(l_counter).TagValue := i.action_information12;
1339 l_counter := l_counter + 1;
1340
1341 xml_tab(l_counter).TagName :='SIGN_UNITS';
1342 xml_tab(l_counter).TagValue := i.action_information13;
1343 l_counter := l_counter + 1;
1344
1345 xml_tab(l_counter).TagName :='START_DATE';
1346 xml_tab(l_counter).TagValue := i.action_information14;
1347 l_counter := l_counter + 1;
1348
1349 xml_tab(l_counter).TagName :='END_DATE';
1350 xml_tab(l_counter).TagValue := i.action_information15;
1351 l_counter := l_counter + 1;
1352
1353 xml_tab(l_counter).TagName :='P_NUMBER';
1354 xml_tab(l_counter).TagValue := i.action_information16;
1355 l_counter := l_counter + 1;
1356
1357 xml_tab(l_counter).TagName :='ABSENCE_RECO_START';
1358 xml_tab(l_counter).TagValue :='ABSENCE_RECO_END';
1359 l_counter := l_counter + 1;
1360
1361 END LOOP;
1362 ----------------------------------FOOTER
1363
1364 xml_tab(l_counter).TagName :='SENDER_CVR_NO';
1365 xml_tab(l_counter).TagValue := rec_get_hdr_info.action_information10;
1366 l_counter := l_counter + 1;
1367
1368 xml_tab(l_counter).TagName :='BG_DA_SYS_NO';
1369 xml_tab(l_counter).TagValue := rec_get_hdr_info.action_information14;
1370 l_counter := l_counter + 1;
1371
1372 xml_tab(l_counter).TagName :='UPDATE_DATE';
1373 xml_tab(l_counter).TagValue := to_char(rec_get_hdr_info.effective_date,'YYYYMMDD');
1374 l_counter := l_counter + 1;
1375
1376 xml_tab(l_counter).TagName :='PAYROLL_SYS_NAME';
1377 xml_tab(l_counter).TagValue := rec_get_hdr_info.action_information15;
1378 l_counter := l_counter + 1;
1379
1380 xml_tab(l_counter).TagName :='FILE_HEADER_FOOTER_START';
1381 xml_tab(l_counter).TagValue :='FILE_HEADER_FOOTER_END';
1382 l_counter := l_counter + 1;
1383
1384 hr_utility.set_location('After populating pl/sql table',30);
1385 hr_utility.set_location('Entered Procedure GETDATA',10);
1386
1387 WritetoCLOB (p_xml );
1388
1389 END POPULATE_DATA;
1390 /********************************************************/
1391
1392 PROCEDURE WritetoCLOB(p_xfdf_clob out nocopy CLOB) is
1393 l_xfdf_string clob;
1394 l_str1 varchar2(1000);
1395 l_str2 varchar2(20);
1396 l_str3 varchar2(20);
1397 l_str4 varchar2(20);
1398 l_str5 varchar2(20);
1399 l_str6 varchar2(30);
1400 l_str7 varchar2(1000);
1401 l_str8 varchar2(240);
1402 l_str9 varchar2(240);
1403 l_str10 varchar2(20);
1404 l_str11 varchar2(20);
1405 l_IANA_charset VARCHAR2 (50);
1406
1407 current_index pls_integer;
1408
1409 BEGIN
1410
1411 hr_utility.set_location('Entering WritetoCLOB ',10);
1412 l_IANA_charset :=PAY_DK_GENERAL.get_IANA_charset ;
1413 --l_str1 := '<?xml version="1.0" encoding="UTF-8"?> <ROOT><STATSR>' ;
1414 l_str1 := '<?xml version="1.0" encoding="'||l_IANA_charset||'"?> <ROOT><STATSR>';
1415 l_str2 := '<';
1416 l_str3 := '>';
1417 l_str4 := '</';
1418 l_str5 := '>';
1419 l_str6 := '</STATSR></ROOT>';
1420 --l_str7 := '<?xml version="1.0" encoding="UTF-8"?> <ROOT></ROOT>';
1421 l_str7 := '<?xml version="1.0" encoding="'||l_IANA_charset||'"?> <ROOT></ROOT>';
1422 l_str10 := '<STATSR>';
1423 l_str11 := '</STATSR>';
1424
1425
1426 dbms_lob.createtemporary(l_xfdf_string,FALSE,DBMS_LOB.CALL);
1427 dbms_lob.open(l_xfdf_string,dbms_lob.lob_readwrite);
1428
1429 current_index := 0;
1430
1431 IF xml_tab.count > 0 THEN
1432
1433 dbms_lob.writeAppend( l_xfdf_string, length(l_str1), l_str1 );
1434
1435
1436 FOR table_counter IN xml_tab.FIRST .. xml_tab.LAST LOOP
1437
1438 l_str8 := xml_tab(table_counter).TagName;
1439 l_str9 := xml_tab(table_counter).TagValue;
1440
1441 IF l_str9 IN ('FILE_HEADER_FOOTER_START', 'FILE_HEADER_FOOTER_END','ABSENCE_RECO_START' ,'ABSENCE_RECO_END')
1442 THEN
1443
1444 IF l_str9 IN ('FILE_HEADER_FOOTER_START' , 'ABSENCE_RECO_START' ) THEN
1445 dbms_lob.writeAppend(l_xfdf_string, length(l_str2), l_str2);
1446 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
1447 dbms_lob.writeAppend(l_xfdf_string, length(l_str3), l_str3);
1448 ELSE
1449 dbms_lob.writeAppend(l_xfdf_string, length(l_str4), l_str4);
1450 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
1451 dbms_lob.writeAppend(l_xfdf_string, length(l_str5), l_str5);
1452 END IF;
1453
1454 ELSE
1455
1456 if l_str9 is not null then
1457 l_str9 := hr_dk_utility.REPLACE_SPECIAL_CHARS(l_str9); /* Place the check after not null check*/
1458 dbms_lob.writeAppend(l_xfdf_string, length(l_str2), l_str2);
1459 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
1460 dbms_lob.writeAppend(l_xfdf_string, length(l_str3), l_str3);
1461 dbms_lob.writeAppend(l_xfdf_string, length(l_str9), l_str9);
1462 dbms_lob.writeAppend(l_xfdf_string, length(l_str4), l_str4);
1463 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
1464 dbms_lob.writeAppend(l_xfdf_string, length(l_str5), l_str5);
1465 else
1466
1467 dbms_lob.writeAppend(l_xfdf_string, length(l_str2), l_str2);
1468 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
1469 dbms_lob.writeAppend(l_xfdf_string, length(l_str3), l_str3);
1470 dbms_lob.writeAppend(l_xfdf_string, length(l_str4), l_str4);
1471 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
1472 dbms_lob.writeAppend(l_xfdf_string, length(l_str5), l_str5);
1473
1474 end if;
1475
1476 END IF;
1477
1478 END LOOP;
1479
1480 dbms_lob.writeAppend(l_xfdf_string, length(l_str6), l_str6 );
1481
1482 ELSE
1483 dbms_lob.writeAppend(l_xfdf_string, length(l_str7), l_str7 );
1484 END IF;
1485
1486 p_xfdf_clob := l_xfdf_string;
1487
1488 hr_utility.set_location('Leaving WritetoCLOB ',20);
1489
1490
1491 EXCEPTION
1492 WHEN OTHERS then
1493 HR_UTILITY.TRACE('sqlerrm ' || SQLERRM);
1494 HR_UTILITY.RAISE_ERROR;
1495 END WritetoCLOB;
1496
1497 END PAY_DK_ABSR_PKG;