[Home] [Help]
PACKAGE BODY: APPS.PAY_DK_STATSR_PKG
Source
1 PACKAGE BODY PAY_DK_STATSR_PKG AS
2 /* $Header: pydkstatsr.pkb 120.11.12000000.6 2007/06/08 11:49:28 nprasath noship $ */
3
4 --Global parameters
5 g_package CONSTANT varchar2(33) := 'PAY_DK_STATSR_PKG.';
6 g_debug BOOLEAN := hr_utility.debug_enabled;
7 g_err_num NUMBER;
8
9
10
11 -----------------------------------------------------------------------------
12 -- GET_LOOKUP_MEANING function used to get labels of items from a lookup
13 -----------------------------------------------------------------------------
14 FUNCTION GET_LOOKUP_MEANING (p_lookup_type varchar2,p_lookup_code varchar2) RETURN VARCHAR2 IS
15
16 CURSOR csr_lookup IS
17 SELECT meaning
18 FROM hr_lookups
19 WHERE lookup_type = p_lookup_type
20 AND lookup_code = p_lookup_code
21 AND enabled_flag = 'Y';
22
23 l_meaning hr_lookups.meaning%type;
24
25 BEGIN
26 OPEN csr_lookup;
27 FETCH csr_lookup INTO l_Meaning;
28 CLOSE csr_lookup;
29 RETURN l_meaning;
30
31 END GET_LOOKUP_MEANING;
32
33
34 -----------------------------------------------------------------------------
35 -- GET_PARAMETER used in SQL to decode legislative parameters
36 -----------------------------------------------------------------------------
37 FUNCTION GET_PARAMETER(
38 p_parameter_string IN VARCHAR2
39 ,p_token IN VARCHAR2
40 ,p_segment_number IN NUMBER default NULL ) RETURN VARCHAR2
41 IS
42 l_parameter pay_payroll_actions.legislative_parameters%TYPE:=NULL;
43 l_start_pos NUMBER;
44 l_delimiter VARCHAR2(1):=' ';
45
46 BEGIN
47 IF g_debug THEN
48 hr_utility.set_location(' Entering Function GET_PARAMETER',10);
49 END IF;
50
51 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
52
53 IF l_start_pos = 0 THEN
54 l_delimiter := '|';
55 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
56 END IF;
57
58 IF l_start_pos <> 0 THEN
59 l_start_pos := l_start_pos + length(p_token||'=');
60 l_parameter := substr(p_parameter_string, l_start_pos,
61 instr(p_parameter_string||' ', l_delimiter,l_start_pos) - l_start_pos);
62
63 IF p_segment_number IS NOT NULL THEN
64 l_parameter := ':'||l_parameter||':';
65 l_parameter := substr(l_parameter,
66 instr(l_parameter,':',1,p_segment_number)+1,
67 instr(l_parameter,':',1,p_segment_number+1) -1
68 - instr(l_parameter,':',1,p_segment_number));
69 END IF;
70 END IF;
71
72 RETURN l_parameter;
73 IF g_debug THEN
74 hr_utility.set_location(' Leaving Function GET_PARAMETER',20);
75 END IF;
76
77 END GET_PARAMETER;
78
79 --------------------------------------------------------------------------------------
80 -- GET_ALL_PARAMETERS used in SQL to cumulatively decode all legislative parameters
81 --------------------------------------------------------------------------------------
82 PROCEDURE GET_ALL_PARAMETERS
83 (p_payroll_action_id IN NUMBER
84 ,p_business_group_id OUT NOCOPY NUMBER
85 ,p_payroll_id OUT NOCOPY NUMBER
86 ,p_sender_id OUT NOCOPY NUMBER
87 ,p_span OUT NOCOPY VARCHAR2
88 ,p_effective_date OUT NOCOPY DATE
89 ,p_report_end_date OUT NOCOPY DATE
90 ,p_archive OUT NOCOPY VARCHAR2)
91 IS
92
93 CURSOR csr_parameter_info(p_payroll_action_id NUMBER) IS
94 SELECT
95 PAY_DK_STATSR_PKG.GET_PARAMETER(legislative_parameters,'LEGAL_EMPLOYER')
96 ,PAY_DK_STATSR_PKG.GET_PARAMETER(legislative_parameters,'PAYROLL')
97 ,PAY_DK_STATSR_PKG.GET_PARAMETER(legislative_parameters,'SPAN_RPT')
98 ,PAY_DK_STATSR_PKG.GET_PARAMETER(legislative_parameters,'ARCHIVE')
99 ,effective_date
100 ,fnd_date.canonical_to_date(PAY_DK_STATSR_PKG.GET_PARAMETER(legislative_parameters,'REPORT_END_DATE'))
101 ,business_group_id
102 FROM pay_payroll_actions
103 WHERE payroll_action_id = p_payroll_action_id;
104
105 l_proc VARCHAR2(240):= g_package||'.GET_ALL_PARAMETERS ';
106 --
107 BEGIN
108
109 IF g_debug THEN
110 hr_utility.set_location(' Entering procedure '||l_proc,10);
111 END IF;
112
113 OPEN csr_parameter_info (p_payroll_action_id);
114
115 FETCH csr_parameter_info
116 INTO p_sender_id
117 ,p_payroll_id
118 ,p_span
119 ,p_archive
120 ,p_effective_date
121 ,p_report_end_date
122 ,p_business_group_id;
123 CLOSE csr_parameter_info;
124 --
125 IF g_debug THEN
126 hr_utility.set_location(' Leaving procedure '||l_proc,20);
127 END IF;
128 END GET_ALL_PARAMETERS;
129
130 ----------------------------------------------------
131 -- GET_GLOBAL_VALUE used to fetch Global Values
132 ----------------------------------------------------
133
134 FUNCTION GET_GLOBAL_VALUE(
135 p_global_name VARCHAR2,
136 p_effective_date DATE)
137 RETURN ff_globals_f.global_value%TYPE IS
138
139 CURSOR csr_globals IS
140 SELECT global_value
141 FROM ff_globals_f
142 WHERE global_name = p_global_name
143 AND legislation_code = 'DK'
144 AND p_effective_date BETWEEN effective_start_date AND effective_END_date;
145
146 l_global_value ff_globals_f.global_value%TYPE;
147 l_proc varchar2(72) := g_package||'get_global_value';
148
149 BEGIN
150 IF g_debug THEN
151 hr_utility.set_location('Entering:'|| l_proc, 1);
152 END IF;
153
154 OPEN csr_globals;
155 FETCH csr_globals INTO l_global_value;
156 CLOSE csr_globals;
157
158 IF g_debug THEN
159 hr_utility.set_location('Entering:'|| l_proc, 2);
160 END IF;
161
162 RETURN l_global_value;
163 END GET_GLOBAL_VALUE;
164
165
166 ---------------------------------------------------------------------
167 -- GET_DEFINED_BALANCE_VALUE used to fetch value of Defined Balance
168 --------------------------------------------------------------------
169
170 FUNCTION GET_DEFINED_BALANCE_VALUE
171 (p_assignment_id IN NUMBER
172 ,p_balance_name IN VARCHAR2
173 ,p_balance_dim IN VARCHAR2
174 ,p_virtual_date IN DATE) RETURN NUMBER IS
175
176 l_context1 PAY_DEFINED_BALANCES.DEFINED_BALANCE_ID%TYPE;
177 l_value NUMBER;
178
179
180 CURSOR get_dbal_id(p_balance_name VARCHAR2 , p_balance_dim VARCHAR2) IS
181 SELECT pdb.defined_balance_id
182 FROM pay_defined_balances pdb
183 ,pay_balance_types pbt
184 ,pay_balance_dimensions pbd
185 WHERE pbt.legislation_code='DK'
186 AND pbt.balance_name = p_balance_name
187 AND pbd.legislation_code = 'DK'
188 AND pbd.database_item_suffix = p_balance_dim
189 AND pdb.balance_type_id = pbt.balance_type_id
190 AND pdb.balance_dimension_id = pbd.balance_dimension_id;
191
192
193 BEGIN
194
195 OPEN get_dbal_id(p_balance_name, p_balance_dim);
196 FETCH get_dbal_id INTO l_context1;
197 CLOSE get_dbal_id;
198
199 l_value := pay_balance_pkg.get_value(l_context1,p_assignment_id,p_virtual_date);
200
201 RETURN l_value;
202
203 END GET_DEFINED_BALANCE_VALUE ;
204
205
206
207 ---------------------------------------------------------------------------------------
208 -- GET_BALANCE_CATEGORY_VALUE used to fetch value of Balances on a defined Category
209 ---------------------------------------------------------------------------------------
210 FUNCTION GET_BALANCE_CATEGORY_VALUE
211 (p_assignment_id IN NUMBER
212 ,p_balance_cat_name IN VARCHAR2
213 ,p_balance_dim IN VARCHAR2
214 ,p_virtual_date IN DATE) RETURN NUMBER IS
215
216 l_context1 PAY_DEFINED_BALANCES.DEFINED_BALANCE_ID%TYPE;
217 l_tot_value NUMBER;
218
219
220 CURSOR get_dbal_id(p_balance_cat_name VARCHAR2 , p_balance_dim VARCHAR2) IS
221 SELECT pdb.defined_balance_id DBAL_ID
222 FROM pay_defined_balances pdb
223 ,pay_balance_types pbt
224 ,pay_balance_dimensions pbd
225 ,pay_balance_categories_f pbc
226 WHERE pbc.category_name = p_balance_cat_name
227 AND pbt.balance_category_id = pbc.balance_category_id
228 AND pbd.legislation_code = 'DK'
229 AND pbd.database_item_suffix = p_balance_dim
230 AND pdb.balance_type_id = pbt.balance_type_id
231 AND pdb.balance_dimension_id = pbd.balance_dimension_id;
232
233
234 BEGIN
235
236 l_tot_value :=0;
237
238 FOR rec_get_dbal_id IN get_dbal_id(p_balance_cat_name, p_balance_dim)
239 LOOP
240 l_context1 := rec_get_dbal_id.dbal_id;
241
242 l_tot_value := l_tot_value + pay_balance_pkg.get_value(l_context1,p_assignment_id,p_virtual_date);
243
244 END LOOP;
245
246 RETURN l_tot_value;
247
248 END GET_BALANCE_CATEGORY_VALUE ;
249
250 --------------------------------------------------------------------------------------
251 -- RANGE_CODE to specify ranges of assignments to be processed in the archive.
252 --------------------------------------------------------------------------------------
253
254 PROCEDURE RANGE_CODE (pactid IN NUMBER
255 ,sqlstr OUT NOCOPY VARCHAR2)
256 IS
257
258 -- Variable's declarations
259
260 l_count NUMBER := 0;
261 l_action_info_id NUMBER;
262 l_ovn NUMBER;
263 l_business_group_id NUMBER;
264 l_payroll_id NUMBER;
265 l_sender_id NUMBER;
266 l_span VARCHAR(80);
267 l_effective_date DATE;
268 l_report_end_date DATE;
269 l_archive VARCHAR2(80);
270
271 l_from_date VARCHAR2(80);
272 l_to_date VARCHAR2(80);
273
274 l_char_set VARCHAR2(240);
275 l_format VARCHAR2(240);
276 l_bg_da_sys_no VARCHAR2(240);
277 l_sys_name VARCHAR2(240);
278
279 l_le_id NUMBER;
280 l_le_cvr_no VARCHAR2(240);
281 l_le_ds_wpcode VARCHAR2(240);
282 l_le_da_scode VARCHAR2(240);
283 l_le_name VARCHAR2(240);
284 l_le_addr VARCHAR2(240);
285 l_le_pcode VARCHAR2(240);
286
287 l_sender_cvr_no VARCHAR2(240);
288 l_sender_name VARCHAR2(240);
289 l_sender_addr VARCHAR2(240);
290 l_sender_pcode VARCHAR2(240);
291
292
293 e_no_da_sys_no EXCEPTION;
294 error_message BOOLEAN;
295
296 /* Cursor to check if Current Archive exists */
297 CURSOR csr_count is
298 SELECT count(*)
299 FROM pay_action_information
300 WHERE action_information_category = 'EMEA REPORT DETAILS'
301 AND action_information1 = 'PYDKSTATSA'
302 AND action_context_id = pactid;
303
304
305 /* Cursor to fetch the Sender's Details */
306 /* If p_sender_id is null=> No Legal Employer selected, hence Service Provider of the BG is the Sender */
307 CURSOR csr_get_sender_details(p_sender_id NUMBER, p_effective_date DATE, p_business_group_id NUMBER) IS
308 SELECT hoi2.org_information1 CVR_NO
309 ,hou1.name NAME
310 -- ,loc.ADDRESS_LINE_1||' '||loc.ADDRESS_LINE_2||' '||loc.ADDRESS_LINE_3 ADDR
311 ,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
312 ,loc.postal_code PCODE
313 FROM HR_ORGANIZATION_UNITS hou1
314 , HR_ORGANIZATION_INFORMATION hoi1
315 , HR_ORGANIZATION_INFORMATION hoi2
316 , HR_LOCATIONS loc
317 WHERE hou1.business_group_id = p_business_group_id
318 and hou1.organization_id = nvl(p_sender_id ,hou1.organization_id)
319 and hou1.location_id = loc.LOCATION_ID(+)
320 and hoi1.organization_id = hou1.organization_id
321 and hoi1.ORG_INFORMATION_CONTEXT='CLASS'
322 and hoi1.org_information1 = nvl2(p_sender_id,'HR_LEGAL_EMPLOYER','DK_SERVICE_PROVIDER')
323 and hoi1.ORG_INFORMATION2 ='Y'
324 and hoi2.ORG_INFORMATION_CONTEXT= nvl2(p_sender_id,'DK_LEGAL_ENTITY_DETAILS','DK_SERVICE_PROVIDER_DETAILS')
325 and hoi2.organization_id = hoi1.organization_id
326 and p_effective_date BETWEEN hou1.DATE_FROM and nvl(hou1.DATE_TO, p_effective_date);
327
328
329 /* Cursor to fetch the Legal Employer Details */
330 CURSOR csr_get_le_details(p_sender_id NUMBER, p_sender_cvr_no VARCHAR2, p_effective_date DATE, p_business_group_id NUMBER) IS
331 SELECT hou.organization_id ORG_ID
332 ,hoi2.ORG_INFORMATION1 CVR_NO
333 ,hoi2.ORG_INFORMATION2 DS_WPCODE
334 ,hou.name NAME
335 -- ,loc.ADDRESS_LINE_1||' '||loc.ADDRESS_LINE_2||' '||loc.ADDRESS_LINE_3 ADDR
336 ,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
337 ,loc.postal_code PCODE
338 FROM HR_ORGANIZATION_UNITS hou
339 , HR_ORGANIZATION_INFORMATION hoi1
340 , HR_ORGANIZATION_INFORMATION hoi2
341 , HR_LOCATIONS loc
342 WHERE hou.business_group_id = p_business_group_id
343 and hoi1.organization_id = hou.organization_id
344 and hou.location_id = loc.LOCATION_ID(+)
345 and hoi1.ORG_INFORMATION_CONTEXT='CLASS'
346 and hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
347 and hoi1.ORG_INFORMATION2 = 'Y'
348 and hoi2.ORG_INFORMATION_CONTEXT='DK_LEGAL_ENTITY_DETAILS'
349 and hoi2.organization_id = hoi1.organization_id
350 and nvl(hoi2.org_information1,0)= nvl2(p_sender_id,p_sender_cvr_no,nvl(hoi2.org_information1,0) )
351 and p_effective_date BETWEEN hou.DATE_FROM and nvl(hou.DATE_TO, p_effective_date);
352
353
354 /* Cursor to fetch the Legal Employer DA Office Codes */
355 /* Modified check on context for bug fix 4997786 */
356 CURSOR csr_get_le_da_off_codes(p_le_id NUMBER, p_effective_date DATE, p_business_group_id NUMBER) IS
357 SELECT hoi2.ORG_INFORMATION1 DA_SCODE
358 FROM HR_ORGANIZATION_UNITS hou
359 , HR_ORGANIZATION_INFORMATION hoi1
360 , HR_ORGANIZATION_INFORMATION hoi2
361 WHERE hou.business_group_id = p_business_group_id
362 and hoi1.organization_id = hou.organization_id
363 and hoi1.organization_id = p_le_id
364 and hoi1.ORG_INFORMATION_CONTEXT='CLASS'
365 and hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
366 and hoi1.ORG_INFORMATION2 = 'Y'
367 -- For bug fix 4997786
368 and hoi2.ORG_INFORMATION_CONTEXT= 'DK_DA_OFFICE_CODE' --'DK_EMPLOYMENT_DEFAULTS'
369 and hoi2.organization_id = hoi1.organization_id
370 and p_effective_date BETWEEN hou.DATE_FROM and nvl(hou.DATE_TO, p_effective_date);
371
372
373 /* Cursor to fetch the Business Group Details */
374 CURSOR csr_get_bg_details(p_business_group_id NUMBER, p_effective_date DATE) IS
375 SELECT hoi2.ORG_INFORMATION1 DA_SYS_NO
376 FROM HR_ORGANIZATION_UNITS hou
377 , HR_ORGANIZATION_INFORMATION hoi1
378 , HR_ORGANIZATION_INFORMATION hoi2
379 WHERE hou.business_group_id = p_business_group_id
380 and hoi1.organization_id = hou.organization_id
381 and hoi1.organization_id = p_business_group_id
382 and hoi1.ORG_INFORMATION_CONTEXT='CLASS'
383 and hoi1.org_information1 = 'HR_BG'
384 and hoi1.ORG_INFORMATION2 = 'Y'
385 and hoi2.ORG_INFORMATION_CONTEXT='DK_IDENTIFICATION_CODES'
386 and hoi2.organization_id = hoi1.organization_id
387 and p_effective_date BETWEEN hou.DATE_FROM and nvl(hou.DATE_TO, p_effective_date);
388
389
390 rec_sender_details csr_get_sender_details%ROWTYPE;
391 rec_le_details csr_get_le_details%ROWTYPE;
392 rec_get_le_da_off_codes csr_get_le_da_off_codes%ROWTYPE;
393 rec_bg_details csr_get_bg_details%ROWTYPE;
394
395
396 BEGIN
397
398 IF g_debug THEN
399 hr_utility.set_location(' Entering Procedure RANGE_CODE',10);
400 END IF;
401
402
403 -- The sql string to return
404 sqlstr := 'SELECT DISTINCT person_id
405 FROM per_people_f ppf
406 ,pay_payroll_actions ppa
407 WHERE ppa.payroll_action_id = :payroll_action_id
408 AND ppa.business_group_id = ppf.business_group_id
409 ORDER BY ppf.person_id';
410
411 -- Fetch the input parameter values
412 PAY_DK_STATSR_PKG.GET_ALL_PARAMETERS(
413 pactid
414 ,l_business_group_id
415 ,l_payroll_id
416 ,l_sender_id
417 ,l_span
418 ,l_effective_date
419 ,l_report_end_date
420 ,l_archive) ;
421
422 -- Check if we have to archive again
423 IF (l_archive = 'Y') THEN
424 -- Check if record for current archive exists
425 OPEN csr_count;
426 FETCH csr_count INTO l_count;
427 CLOSE csr_count;
428
429 -- Archive Report Details only if no record exists
430 IF (l_count < 1) THEN
431
432
433 /* To obtain Reporting From and Reporting To Dates from Span specified in parameters */
434
435 l_to_date := to_char(l_report_end_date,'YYYYMMDD');
436
437 IF (l_span ='Q') THEN
438
439 l_from_date := to_char(trunc(l_report_end_date,'Q'),'YYYYMMDD');
440
441 ELSIF (l_span ='HY') THEN
442
443 l_from_date := to_char(trunc(trunc(l_report_end_date,'Q')-1,'Q'),'YYYYMMDD');
444
445 ELSIF (l_span ='Y') THEN
446
447 l_from_date := to_char(trunc(l_report_end_date,'Y'),'YYYYMMDD');
448
449 END IF;
450
451
452 /* To set Character Set and Format */
453
454 l_char_set := '3';
455 l_format := '1';
456
457 /* To obtain Sender's details */
458 /* The Sender would be Service Provider if present in the system or else it would be the Legal Employer Specified */
459
460 OPEN csr_get_sender_details(l_sender_id,fnd_date.canonical_to_date(l_to_date),l_business_group_id);
461 FETCH csr_get_sender_details INTO rec_sender_details;
462 CLOSE csr_get_sender_details;
463
464 l_sender_cvr_no := rec_sender_details.cvr_no;
465 l_sender_name := rec_sender_details.name;
466 l_sender_addr := rec_sender_details.addr;
467 l_sender_pcode := rec_sender_details.pcode;
468
469 /* To obtain Business Group details */
470
471 OPEN csr_get_bg_details(l_business_group_id,fnd_date.canonical_to_date(l_to_date));
472 FETCH csr_get_bg_details INTO rec_bg_details;
473 CLOSE csr_get_bg_details;
474
475 l_bg_da_sys_no := rec_bg_details.da_sys_no;
476 l_sys_name := GET_LOOKUP_MEANING ('DK_STATSR_LABELS','OP');
477 IF l_bg_da_sys_no IS NULL
478 THEN
479 RAISE e_no_da_sys_no;
480 END IF;
481
482
483 -- Archive the REPORT DETAILS
484
485 pay_action_information_api.create_action_information
486 (p_action_information_id => l_action_info_id -- out parameter
487 ,p_object_version_number => l_ovn -- out parameter
488 ,p_action_context_id => pactid -- context id = payroll action id (of Archive)
489 ,p_action_context_type => 'PA' -- context type
490 ,p_effective_date => l_effective_date -- Date of Running the Archive
491 ,p_action_information_category => 'EMEA REPORT DETAILS' -- Information Category
492 ,p_tax_unit_id => NULL -- Legal Employer ID
493 ,p_jurisdiction_code => NULL -- Tax Municipality ID
494 ,p_action_information1 => 'PYDKSTATSA' -- Conc Prg Short Name
495 ,p_action_information2 => l_business_group_id -- Business Group ID
496 ,p_action_information3 => l_payroll_id -- Payroll ID
497 ,p_action_information4 => 'HDR' -- Specifies data is for File Header
498 ,p_action_information5 => l_span -- Span of report
499 ,p_action_information6 => l_from_date -- Report's from date
500 ,p_action_information7 => l_to_date -- Report's to date
501 ,p_action_information8 => l_char_set -- Character Set
502 ,p_action_information9 => l_format -- Format used
503 ,p_action_information10 => l_sender_cvr_no -- Sender's CVR number
504 ,p_action_information11 => l_sender_name -- Sender's Name
505 ,p_action_information12 => l_sender_addr -- Sender's Address
506 ,p_action_information13 => l_sender_pcode -- Sender's Postal Code
507 ,p_action_information14 => l_bg_da_sys_no -- BG's DA System Number
508 ,p_action_information15 => l_sys_name -- Payroll System Name
509 );
510
511
512 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)
513 LOOP
514 /* To obtain Legal Employer's details from details provided in File Header*/
515
516
517 l_le_cvr_no := rec_le_details.cvr_no;
518 l_le_ds_wpcode := rec_le_details.ds_wpcode;
519 l_le_name := rec_le_details.name;
520 l_le_addr := rec_le_details.addr;
521 l_le_pcode := rec_le_details.pcode;
522 l_le_id := rec_le_details.org_id;
523
524
525 OPEN csr_get_le_da_off_codes(l_le_id,fnd_date.canonical_to_date(l_to_date),l_business_group_id);
526 FETCH csr_get_le_da_off_codes INTO rec_get_le_da_off_codes;
527 CLOSE csr_get_le_da_off_codes;
528
529 l_le_da_scode := rec_get_le_da_off_codes.da_scode;
530
531 pay_action_information_api.create_action_information
532 (
533 p_action_information_id => l_action_info_id -- out parameter
534 ,p_object_version_number => l_ovn -- out parameter
535 ,p_action_context_id => pactid -- context id = payroll action id (of Archive)
536 ,p_action_context_type => 'PA' -- context type
537 ,p_effective_date => l_effective_date -- Date of Running the Archive
538 ,p_action_information_category => 'EMEA REPORT DETAILS' -- Information Category
539 ,p_tax_unit_id => l_le_id -- Legal Employer ID
540 ,p_jurisdiction_code => NULL -- Tax Municipality ID
541 ,p_action_information1 => 'PYDKSTATSA' -- Conc Prg Short Name
542 ,p_action_information2 => l_business_group_id -- Business Group ID
543 ,p_action_information3 => l_payroll_id -- Payroll ID
544 ,p_action_information4 => 'CHDR' -- Specifies data is for File Sub-Header for Company
545 ,p_action_information5 => l_le_cvr_no -- LE's CVR number
546 ,p_action_information6 => l_le_ds_wpcode -- LE's DS Workplace Code
547 ,p_action_information7 => l_le_da_scode -- LE's DA Society Code
548 ,p_action_information8 => l_le_name -- LE's Name
549 ,p_action_information9 => l_le_addr -- LE's Address
550 ,p_action_information10 => l_le_pcode -- LE's Postal Code
551 );
552
553 END LOOP;
554
555 END IF;
556
557 END IF;
558
559 IF g_debug THEN
560 hr_utility.set_location(' Leaving Procedure RANGE_CODE',20);
561 END IF;
562
563 EXCEPTION WHEN e_no_da_sys_no THEN
564 fnd_message.set_name('PAY','PAY_377058_DK_NO_DA_CODE_ERR');
565 fnd_file.put_line(fnd_file.log,fnd_message.get);
566 error_message:=fnd_concurrent.set_completion_status('ERROR','PAY_377058_DK_NO_DA_CODE_ERR');
567 END RANGE_CODE;
568
569
570 --------------------------------------------------------------------------------------
571 -- ASSIGNMENT_ACTION_CODE to create the assignment actions to be processed.
572 --------------------------------------------------------------------------------------
573
574 PROCEDURE ASSIGNMENT_ACTION_CODE
575 (p_payroll_action_id IN NUMBER
576 ,p_start_person IN NUMBER
577 ,p_end_person IN NUMBER
578 ,p_chunk IN NUMBER)
579 IS
580
581 /* Cursor to fetch useful header information to transfer to body records from already archived header information */
582 CURSOR csr_get_hdr_info(p_payroll_action_id NUMBER) IS
583 SELECT effective_date
584 ,fnd_date.CANONICAL_TO_DATE(action_information6) from_date
585 ,fnd_date.CANONICAL_TO_DATE(action_information7) to_date
586 ,to_number(action_information2) business_group_id
587 FROM pay_action_information pai
588 WHERE action_context_type = 'PA'
589 AND action_context_id = p_payroll_action_id
590 AND action_information_category = 'EMEA REPORT DETAILS'
591 AND action_information1 = 'PYDKSTATSA'
592 AND action_information4 = 'HDR';
593
594
595 /* Cursor to fetch useful sub-header information to transfer to body records from already archived sub-header information */
596 CURSOR csr_get_sub_hdr_info(p_payroll_action_id NUMBER) IS
597 SELECT tax_unit_id
598 ,to_number(action_information3) PAYROLL_ID
599 ,action_information8 LE_NAME
600 FROM pay_action_information pai
601 WHERE action_context_type = 'PA'
602 AND action_context_id = p_payroll_action_id
603 AND action_information_category = 'EMEA REPORT DETAILS'
604 AND action_information1 = 'PYDKSTATSA'
605 AND action_information4 = 'CHDR';
606
607 /* Cursor to fetch the Legal Employer level Employment Defaults */
608 CURSOR csr_get_le_emp_dflts(p_le_id NUMBER, p_effective_date DATE, p_business_group_id NUMBER) IS
609 SELECT hoi2.ORG_INFORMATION1 COND_OF_EMP
610 ,hoi2.ORG_INFORMATION2 EMP_GRP
611 ,hoi2.ORG_INFORMATION3 WORK_HOURS
612 ,hoi2.ORG_INFORMATION4 FREQ
613 FROM HR_ORGANIZATION_UNITS hou
614 , HR_ORGANIZATION_INFORMATION hoi1
615 , HR_ORGANIZATION_INFORMATION hoi2
616 WHERE hou.business_group_id = p_business_group_id
617 and hoi1.organization_id = hou.organization_id
618 and hoi1.organization_id = p_le_id
619 and hoi1.ORG_INFORMATION_CONTEXT='CLASS'
620 and hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
621 and hoi1.ORG_INFORMATION2 = 'Y'
622 and hoi2.ORG_INFORMATION_CONTEXT='DK_EMPLOYMENT_DEFAULTS'
623 and hoi2.organization_id = hoi1.organization_id
624 and p_effective_date BETWEEN hou.DATE_FROM and nvl(hou.DATE_TO, p_effective_date);
625
626
627 /* Cursor to fetch the Legal Employer level Holiday Entitlement */
628 CURSOR csr_get_hol_entit(p_le_id NUMBER, p_effective_date DATE, p_business_group_id NUMBER) IS
629 SELECT hoi2.ORG_INFORMATION1 DEFAULT_WORK_PATT
630 ,hoi2.ORG_INFORMATION3 HOURLY_ACCR_RATE
631 ,hoi2.ORG_INFORMATION4 SAL_ALLOW_RATE
632 FROM HR_ORGANIZATION_UNITS hou
633 , HR_ORGANIZATION_INFORMATION hoi1
634 , HR_ORGANIZATION_INFORMATION hoi2
635 WHERE hou.business_group_id = p_business_group_id
636 and hoi1.organization_id = hou.organization_id
637 and hoi1.organization_id = p_le_id
638 and hoi1.ORG_INFORMATION_CONTEXT='CLASS'
639 and hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
640 and hoi1.ORG_INFORMATION2 = 'Y'
641 and hoi2.ORG_INFORMATION_CONTEXT='DK_HOLIDAY_ENTITLEMENT_INFO'
642 and hoi2.organization_id = hoi1.organization_id
643 and p_effective_date BETWEEN hou.DATE_FROM and nvl(hou.DATE_TO, p_effective_date);
644
645
646 /* Cursor to fetch the Assignments, on which pre-payments has been completed */
647 CURSOR csr_assignments
648 ( p_payroll_action_id NUMBER
649 ,p_payroll_id NUMBER
650 ,p_start_person NUMBER
651 ,p_end_person NUMBER
652 ,p_date_from DATE
653 ,p_date_to DATE
654 ,p_le_id NUMBER
655 ,p_effective_date DATE
656 ) IS
657 SELECT distinct
658 paaf.assignment_id ASG_ID
659 ,ppf.payroll_name PAYROLL_NAME
660 ,paaf.assignment_number ASSIGNMENT_NUMBER
661 ,to_char(paaf.effective_start_date,'YYYYMMDD') ASG_START_DATE
662 -- Selecting assignments effective end date to fetch correct balance value - Bug 5003744
663 ,paaf.effective_end_date ASG_END_DATE
664 ,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
665 ,scl.SEGMENT3 COND_OF_EMP
666 ,scl.SEGMENT4 EMP_GRP
667 ,scl.SEGMENT14 JOB_OCC_MKODE
668 ,scl.SEGMENT15 JOB_STATUS_MKODE
669 ,paaf.NORMAL_HOURS NORMAL_HOURS
670 ,paaf.FREQUENCY FREQ
671 ,scl.SEGMENT10 DEFAULT_WORK_PATT
672 ,scl.SEGMENT11 HOURLY_ACCR_RATE
673 ,scl.SEGMENT13 SAL_ALLOW_RATE
674 ,decode(ppf.PERIOD_TYPE
675 ,'Calendar Month','1'
676 ,'Bi-Week' ,'2'
677 ,'Week' ,'3'
678 ,'Lunar Month' ,'4') PAYROLL_PERIOD /*Changes for Lunar Payroll */
679 ,scl.SEGMENT16 SAL_BASIS_MKODE
680 ,scl.SEGMENT17 TIME_OFF_LIEU
681 ,paaf.hourly_salaried_code HOURLY_SALARIED_CODE
682 FROM
683 per_all_people_f papf
684 ,per_all_assignments_f paaf
685 ,pay_payrolls_f ppf
686 ,hr_soft_coding_keyflex scl
687 ,pay_assignment_actions paa
688 ,pay_payroll_actions ppa
689 WHERE paaf.person_id BETWEEN p_start_person AND p_end_person
690 AND papf.PERSON_ID = paaf.PERSON_ID
691 AND ppf.payroll_id = nvl(p_payroll_id,ppf.payroll_id)
692 AND paaf.payroll_id = ppf.payroll_id
693 AND paaf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
694 AND scl.enabled_flag = 'Y'
695 AND paa.assignment_id = paaf.assignment_id
696 AND ppa.payroll_action_id = paa.payroll_action_id
697 AND paa.action_status = 'C' -- Completed
698 AND ppa.action_type IN ('P','U') -- Pre-Payments
699 AND ppa.effective_date BETWEEN p_date_from AND p_date_to
700 /* Modified for bug 5003744 - Start */
701 --AND p_date_to BETWEEN paaf.EFFECTIVE_START_DATE AND paaf.EFFECTIVE_END_DATE
702 -- Added for Multi Record nprasath
703 AND ppa.effective_date <= paaf.EFFECTIVE_END_DATE
704 AND ppa.effective_date >= paaf.EFFECTIVE_start_DATE
705 AND paaf.EFFECTIVE_START_DATE <= p_date_to
706 AND paaf.EFFECTIVE_END_DATE >= p_date_from
707 AND papf.current_employee_flag = 'Y'
708 /* Modified for bug 5003744 - End */
709 AND scl.segment1 = to_char(p_le_id)
710 ORDER BY asg_id;
711
712 -- Added by nprasath for check the assignment end date for bug 5034129
713 CURSOR csr_asg_end(
714 p_assignment_id1 NUMBER
715 ,p_date_from1 DATE
716 ,p_date_to1 DATE
717 ,p_job_occ_mkode VARCHAR2
718 ,p_job_status_mkode VARCHAR2
719 ,p_sal_basis_mkode VARCHAR2
720 ,p_time_off_lieu VARCHAR2
721 ,p_pre_asg_end_date DATE
722 ) is
723 select
724 paaf.effective_start_date ASG_START_DATE
725 ,paaf.effective_end_date ASG_END_DATE
726 FROM
727 per_all_assignments_f paaf
728 ,hr_soft_coding_keyflex scl
729 where
730 paaf.assignment_id = p_assignment_id1
731 AND paaf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
732 AND scl.enabled_flag = 'Y'
733 AND paaf.EFFECTIVE_START_DATE <= p_date_to1
734 AND paaf.EFFECTIVE_START_DATE >= p_pre_asg_end_date
735 AND paaf.EFFECTIVE_END_DATE >= p_date_from1
736 AND (scl.SEGMENT14 <> p_job_occ_mkode or scl.SEGMENT15 <> p_job_status_mkode
737 or scl.SEGMENT16 <> p_sal_basis_mkode or scl.SEGMENT17 <> p_time_off_lieu);
738
739 csr_asg_end_check csr_asg_end%ROWTYPE;
740 rec_hdr_info csr_get_hdr_info%ROWTYPE;
741 rec_get_le_emp_dflts csr_get_le_emp_dflts%ROWTYPE;
742 rec_get_hol_entit csr_get_hol_entit%ROWTYPE;
743
744 -- Variable Declarations
745
746 l_count NUMBER := 0;
747 l_action_info_id NUMBER;
748 l_ovn NUMBER;
749 l_actid NUMBER;
750 l_asgid NUMBER := -999;
751
752 l_archive VARCHAR2(240);
753 l_payroll_id NUMBER;
754 l_le_id NUMBER;
755 l_le_name VARCHAR2(240);
756 l_effective_date DATE;
757 l_date_from DATE;
758 l_date_to DATE;
759 l_bg_id NUMBER;
760
761 l_mkode0100 VARCHAR2(80) := ' ';
762 l_mkode0200 VARCHAR2(80) := ' ';
763 l_mkode0600 VARCHAR2(80) := ' ';
764 l_mkode0610 VARCHAR2(80) := ' ';
765 l_mkode0620 VARCHAR2(80) := ' ';
766 l_hours_rate NUMBER;
767 l_freq VARCHAR2(80);
768 l_day_max_hrs NUMBER;
769 l_old_mkode0600 VARCHAR2(80);
770
771 l_dimension VARCHAR2(80);
772 l_span VARCHAR2(80);
773 l_asg_id NUMBER;
774 l_mul_factor VARCHAR2(80);
775
776
777 l_iltype_0010_bal NUMBER := 0;
778 l_iltype_0011_bal NUMBER := 0;
779 l_iltype_0013_bal NUMBER := 0;
780 l_iltype_0015_bal NUMBER := 0;
781 l_iltype_0021_bal NUMBER := 0;
782 l_iltype_0022_bal NUMBER := 0;
783 l_iltype_0023_bal NUMBER := 0;
784 l_iltype_0024_bal NUMBER := 0;
785 l_iltype_0025_bal NUMBER := 0;
786 l_iltype_0026_bal NUMBER := 0;
787 l_iltype_0032_bal NUMBER := 0;
788 l_iltype_0034_bal NUMBER := 0;
789 l_iltype_0036_bal NUMBER := 0;
790 l_iltype_0037_bal NUMBER := 0;
791
792 -- Added for Multiple Records
793 l_old_iltype_0010_bal NUMBER := 0;
794 l_old_iltype_0011_bal NUMBER := 0;
795 l_old_iltype_0013_bal NUMBER := 0;
796 l_old_iltype_0015_bal NUMBER := 0;
797 l_old_iltype_0021_bal NUMBER := 0;
798 l_old_iltype_0022_bal NUMBER := 0;
799 l_old_iltype_0023_bal NUMBER := 0;
800 l_old_iltype_0024_bal NUMBER := 0;
801 l_old_iltype_0025_bal NUMBER := 0;
802 l_old_iltype_0026_bal NUMBER := 0;
803 l_old_iltype_0032_bal NUMBER := 0;
804 l_old_iltype_0034_bal NUMBER := 0;
805 l_old_iltype_0036_bal NUMBER := 0;
806 l_old_iltype_0037_bal NUMBER := 0;
807
808 l_iltype_0010_unit NUMBER := 0;
809 l_iltype_0011_unit NUMBER := 0;
810 l_iltype_0013_unit NUMBER := 0;
811 l_iltype_0015_unit NUMBER := 0;
812 l_iltype_0021_unit NUMBER := 0;
813 l_iltype_0022_unit NUMBER := 0;
814 l_iltype_0023_unit NUMBER := 0;
815 l_iltype_0024_unit NUMBER := 0;
816 l_iltype_0025_unit NUMBER := 0;
817 l_iltype_0026_unit NUMBER := 0;
818 l_iltype_0032_unit NUMBER := 0;
819 l_iltype_0034_unit NUMBER := 0;
820 l_iltype_0036_unit NUMBER := 0;
821 l_iltype_0037_unit NUMBER := 0;
822 l_iltype_0023_hr_rate NUMBER := 0;
823
824 -- nprasath added for Multiple Records
825 l_s_old_iltype_0010_unit NUMBER := 0;
826 l_h_old_iltype_0010_unit NUMBER := 0;
827 l_old_iltype_0011_unit NUMBER := 0;
828 l_old_iltype_0013_unit NUMBER := 0;
829 l_old_iltype_0015_unit NUMBER := 0;
830 l_old_iltype_0021_unit NUMBER := 0;
831 l_old_iltype_0022_unit NUMBER := 0;
832 l_s_old_iltype_0023_unit NUMBER := 0;
833 l_h_old_iltype_0023_unit NUMBER := 0;
834 l_old_iltype_0024_unit NUMBER := 0;
835 l_old_iltype_0025_unit NUMBER := 0;
836 l_old_iltype_0026_unit NUMBER := 0;
837 l_old_iltype_0032_unit NUMBER := 0;
838 l_old_iltype_0034_unit NUMBER := 0;
839 l_old_iltype_0036_unit NUMBER := 0;
840 l_old_iltype_0037_unit NUMBER := 0;
841 l_old_iltype_0023_hr_rate NUMBER := 0;
842 l_chk_asg_end_date DATE;
843 /*Changes for Lunar Payroll */
844 l_lnr_payroll_period Varchar2(3);
845
846
847 e_too_many_hours EXCEPTION;
848 e_no_emp_dflts EXCEPTION;
849 error_message BOOLEAN;
850
851 -- nprasath Added for Multiple Records
852 l_old_job_occ_mkode VARCHAR2(40);
853 l_old_job_status_mkode VARCHAR2(40);
854 l_old_sal_basis_mkode VARCHAR2(40);
855 l_old_time_off_lieu VARCHAR2(40);
856
857 l_bal_todate DATE;
858 l_hourly_salaried per_all_assignments_f.hourly_salaried_code%TYPE ;
859 BEGIN
860 hr_utility.trace('Inside the Statistics Report');
861
862 IF g_debug THEN
863 hr_utility.set_location(' Entering Procedure ASSIGNMENT_ACTION_CODE',10);
864 END IF;
865
866 SELECT PAY_DK_STATSR_PKG.GET_PARAMETER(legislative_parameters,'SPAN_RPT')
867 INTO l_span
868 FROM pay_payroll_actions
869 WHERE payroll_action_id = p_payroll_action_id;
870
871 -- Fetch Header and Sub-header details
872 FOR rec_sub_hdr_info IN csr_get_sub_hdr_info(p_payroll_action_id)
873 LOOP
874
875 l_le_id := rec_sub_hdr_info.tax_unit_id;
876 l_le_name := rec_sub_hdr_info.le_name;
877 l_payroll_id := rec_sub_hdr_info.payroll_id;
878
879 OPEN csr_get_hdr_info(p_payroll_action_id);
880 FETCH csr_get_hdr_info INTO rec_hdr_info;
881 CLOSE csr_get_hdr_info;
882
883 l_effective_date := rec_hdr_info.effective_date;
884 --l_date_from := to_date(rec_hdr_info.from_date);
885 --l_date_to := to_date(rec_hdr_info.to_date);
886 l_bg_id := rec_hdr_info.business_group_id;
887
888 --Fixed for gscc error
889 l_date_from := rec_hdr_info.from_date;
890 l_date_to := rec_hdr_info.to_date;
891
892 -- Fetch Assignment's details
893
894 FOR csr_rec IN csr_assignments( p_payroll_action_id
895 ,l_payroll_id
896 ,p_start_person
897 ,p_end_person
898 ,l_date_from
899 ,l_date_to
900 ,l_le_id
901 ,l_effective_date )
902 LOOP
903
904 -- Bug 5003744 - If the assignment end date is <= report end date then use assignment end date
905 -- else use report end date while fetching balance values.
906
907 l_hourly_salaried := csr_rec.hourly_salaried_code;
908 IF l_hourly_salaried IS NULL THEN
909 IF csr_rec.payroll_period = 1 THEN
910 l_hourly_salaried := 'S';
911 ELSE
912 l_hourly_salaried := 'H';
913 END IF ;
914 END IF ;
915
916 l_chk_asg_end_date := csr_rec.asg_end_date;
917
918 IF csr_rec.asg_end_date <> hr_general.end_of_time Then
919 --Bug 5034129 Check for any changes occured on Job Occupation Employee Code or Job Status Employee Code or Salary Basis Employee Code or Time Off in Lieu
920
921 open csr_asg_end(csr_rec.asg_id,
922 l_date_from,
923 l_date_to,
924 csr_rec.job_occ_mkode,
925 csr_rec.job_status_mkode,
926 csr_rec.sal_basis_mkode,
927 csr_rec.time_off_lieu,
928 csr_rec.asg_end_date
929 );
930
931 Fetch csr_asg_end into csr_asg_end_check;
932
933 IF csr_asg_end%NOTFOUND THEN
934 l_chk_asg_end_date := hr_general.end_of_time;
935 End if;
936 close csr_asg_end;
937
938 End If;
939
940 IF l_chk_asg_end_date <= l_date_to THEN
941 l_bal_todate := l_chk_asg_end_date;
942 ELSE
943 l_bal_todate := l_date_to;
944 END IF;
945
946 /*Check for Change of Assignment ID to Create New Assignment Action ID
947 and for Archiving the data Bug Fix-5003220*/
948 -- nprasath added for Multiple Records
949 IF (csr_rec.asg_id <> l_asgid) THEN
950
951 hr_utility.trace(' ***** Intializing the old variables **** ');
952
953 l_old_iltype_0010_bal := 0;
954 l_old_iltype_0011_bal := 0;
955 l_old_iltype_0013_bal := 0;
956 l_old_iltype_0015_bal := 0;
957 l_old_iltype_0021_bal := 0;
958 l_old_iltype_0022_bal := 0;
959 l_old_iltype_0023_bal := 0;
960 l_old_iltype_0024_bal := 0;
961 l_old_iltype_0025_bal := 0;
962 l_old_iltype_0026_bal := 0;
963 l_old_iltype_0032_bal := 0;
964 l_old_iltype_0034_bal := 0;
965 l_old_iltype_0036_bal := 0;
966 l_old_iltype_0037_bal := 0;
967 l_s_old_iltype_0010_unit := 0;
968 l_h_old_iltype_0010_unit := 0;
969 l_old_iltype_0011_unit := 0;
970 l_old_iltype_0013_unit := 0;
971 l_old_iltype_0015_unit := 0;
972 l_old_iltype_0021_unit := 0;
973 l_old_iltype_0022_unit := 0;
974 l_s_old_iltype_0023_unit := 0;
975 l_h_old_iltype_0023_unit := 0;
976 l_old_iltype_0024_unit := 0;
977 l_old_iltype_0025_unit := 0;
978 l_old_iltype_0026_unit := 0;
979 l_old_iltype_0032_unit := 0;
980 l_old_iltype_0034_unit := 0;
981 l_old_iltype_0036_unit := 0;
982 l_old_iltype_0037_unit := 0;
983 l_old_iltype_0023_hr_rate := 0;
984 End if;
985
986 -- nprasath added for Multiple Records
987
988
989 IF (csr_rec.asg_id <> l_asgid)
990 or (csr_rec.asg_id = l_asgid
991 and ( csr_rec.job_occ_mkode <> l_old_job_occ_mkode
992 or csr_rec.job_status_mkode <> l_old_job_status_mkode
993 or csr_rec.sal_basis_mkode <> l_old_sal_basis_mkode
994 or csr_rec.time_off_lieu <> l_old_time_off_lieu )
995 ) THEN
996
997
998 BEGIN
999 SELECT pay_assignment_actions_s.NEXTVAL INTO l_actid FROM dual;
1000 EXCEPTION
1001 WHEN OTHERS THEN
1002 NULL ;
1003 END ;
1004 -- Create the archive assignment action
1005 hr_nonrun_asact.insact(l_actid ,csr_rec.asg_id ,p_payroll_action_id ,p_chunk,null);
1006
1007 OPEN csr_get_le_emp_dflts(l_le_id,l_date_to,l_bg_id) ;
1008 FETCH csr_get_le_emp_dflts INTO rec_get_le_emp_dflts;
1009 IF csr_get_le_emp_dflts%NOTFOUND THEN
1010 /* For bug fix 4997994 */
1011 /* Added check to check if corresponding assignment level details are present */
1012 --RAISE e_no_emp_dflts;
1013 IF(csr_rec.cond_of_emp IS NULL OR csr_rec.emp_grp IS NULL OR csr_rec.normal_hours IS NULL
1014 OR csr_rec.freq IS NULL) THEN
1015 RAISE e_no_emp_dflts;
1016 END IF;
1017 END IF;
1018 CLOSE csr_get_le_emp_dflts;
1019
1020 OPEN csr_get_hol_entit(l_le_id,l_date_to,l_bg_id) ;
1021 FETCH csr_get_hol_entit INTO rec_get_hol_entit;
1022 CLOSE csr_get_hol_entit;
1023
1024 /************** Access the values for the IPTYPE values **************/
1025 /* Logic for selecting mkode0100 */
1026
1027 IF( nvl(csr_rec.cond_of_emp, rec_get_le_emp_dflts.cond_of_emp) IN('3','4')) THEN
1028 l_mkode0100 := '2';
1029 ELSE
1030 l_mkode0100 := '1';
1031 END IF;
1032 /* Logic for selecting mkode0200 */
1033 IF( nvl(csr_rec.emp_grp, rec_get_le_emp_dflts.emp_grp) = '1') THEN
1034 l_mkode0200 := '1';
1035 ELSIF ( nvl(csr_rec.emp_grp, rec_get_le_emp_dflts.emp_grp) = '2') THEN
1036 l_mkode0200 := '2';
1037 ELSE
1038 l_mkode0200 := '3';
1039 END IF;
1040
1041 /* Logic for selecting mkode0600 */
1042 /* Bug 5030983 Fixes - Start */
1043 /* For salary record mkode0600,if salary basis not in 81,82,83,84 and payroll period is weekly
1044 and biweekly then get working hours balance and divide by 13, 26 or 52 based on report span. */
1045 IF (l_span ='Q') THEN
1046 l_dimension := '_ASG_LE_QTD';
1047 ELSIF (l_span ='HY') THEN
1048 l_dimension := '_ASG_LE_HYTD';
1049 ELSIF (l_span ='Y') THEN
1050 l_dimension := '_ASG_LE_YTD';
1051 END IF;
1052
1053 pay_balance_pkg.set_context('TAX_UNIT_ID',l_le_id);
1054
1055 l_asg_id :=csr_rec.asg_id;
1056 IF csr_rec.SAL_BASIS_MKODE IN ('81','82','83','84') THEN
1057 /*Changes for Lunar Payroll */
1058 IF csr_rec.payroll_period IN ('1', '2', '3','4') THEN
1059 l_day_max_hrs := 24;
1060 l_hours_rate := nvl(csr_rec.normal_hours,rec_get_le_emp_dflts.work_hours) ;
1061 l_freq := nvl(csr_rec.freq,rec_get_le_emp_dflts.freq);
1062 IF(l_freq = 'D') THEN
1063 IF(l_hours_rate > l_day_max_hrs) THEN
1064 RAISE e_too_many_hours;
1065 ELSE
1066 l_mul_factor :=5;
1067 END IF;
1068 ELSIF (l_freq = 'W') THEN
1069 IF(l_hours_rate > l_day_max_hrs*7) THEN
1070 RAISE e_too_many_hours;
1071 ELSE
1072 l_mul_factor :=1;
1073 END IF;
1074 ELSIF (l_freq = 'M') THEN
1075 IF(l_hours_rate > l_day_max_hrs*31) THEN
1076 RAISE e_too_many_hours;
1077 ELSE
1078 l_mul_factor := 5/22;
1079 END IF;
1080 ELSIF (l_freq = 'Y') THEN
1081 IF(l_hours_rate > l_day_max_hrs*366) THEN
1082 RAISE e_too_many_hours;
1083 END IF;
1084 ELSE
1085 l_mul_factor :=5/260;
1086 END IF;
1087
1088 l_mkode0600 := ROUND(l_hours_rate * l_mul_factor,2);
1089 END IF;
1090
1091 ELSE -- MKODE not in 81,82,83,84
1092 /*IF csr_rec.payroll_period = '1' THEN*/
1093 IF l_hourly_salaried = 'S' THEN
1094 l_day_max_hrs := 24;
1095 l_hours_rate := nvl(csr_rec.normal_hours,rec_get_le_emp_dflts.work_hours) ;
1096 l_freq := nvl(csr_rec.freq,rec_get_le_emp_dflts.freq);
1097 IF(l_freq = 'D') THEN
1098 IF(l_hours_rate > l_day_max_hrs) THEN
1099 RAISE e_too_many_hours;
1100 ELSE
1101 l_mul_factor :=5;
1102 END IF;
1103 ELSIF (l_freq = 'W') THEN
1104 IF(l_hours_rate > l_day_max_hrs*7) THEN
1105 RAISE e_too_many_hours;
1106 ELSE
1107 l_mul_factor :=1;
1108 END IF;
1109 ELSIF (l_freq = 'M') THEN
1110 IF(l_hours_rate > l_day_max_hrs*31) THEN
1111 RAISE e_too_many_hours;
1112 ELSE
1113 l_mul_factor := 5/22;
1114 END IF;
1115 ELSIF (l_freq = 'Y') THEN
1116 IF(l_hours_rate > l_day_max_hrs*366) THEN
1117 RAISE e_too_many_hours;
1118 END IF;
1119 ELSE
1120 l_mul_factor :=5/260;
1121 END IF;
1122
1123 l_mkode0600 := ROUND(l_hours_rate * l_mul_factor,2);
1124
1125 /*Changes for Lunar Payroll */
1126
1127 /*ELSIF csr_rec.payroll_period IN ('2','3','4') THEN*/
1128 ELSIF l_hourly_salaried = 'H' THEN
1129 l_mkode0600 := PAY_DK_STATSR_PKG.GET_DEFINED_BALANCE_VALUE(l_asg_id, 'Worked Hours',l_dimension ,l_bal_todate) - l_old_mkode0600; -- l_date_to);
1130 l_old_mkode0600 := l_mkode0600 + l_old_mkode0600;
1131
1132 IF l_span = 'Q' THEN
1133 l_mkode0600 := l_mkode0600 / 13;
1134 ELSIF l_span = 'HY' THEN
1135 l_mkode0600 := l_mkode0600 / 26;
1136 ELSIF l_span = 'Y' THEN
1137 l_mkode0600 := l_mkode0600 / 52;
1138 END IF;
1139 END IF;
1140
1141 END IF; /* Bug 5030983 Fixes - End */
1142
1143 /* Logic for selecting mkode0610 */
1144 IF( nvl(csr_rec.default_work_patt,rec_get_hol_entit.default_work_patt) = '5DAY') THEN
1145 l_mkode0610 := to_char(PAY_DK_STATSR_PKG.GET_GLOBAL_VALUE('DK_5DAY_WEEK_HOLIDAY_ENTITLEMENT', l_date_to));
1146 ELSIF ( nvl(csr_rec.default_work_patt,rec_get_hol_entit.default_work_patt) = '6DAY') THEN
1147 l_mkode0610 := to_char(PAY_DK_STATSR_PKG.GET_GLOBAL_VALUE('DK_6DAY_WEEK_HOLIDAY_ENTITLEMENT', l_date_to));
1148 /* Added new condition for bug fix 5003621 */
1149 ELSE
1150 l_mkode0610 := to_char(PAY_DK_STATSR_PKG.GET_GLOBAL_VALUE('DK_5DAY_WEEK_HOLIDAY_ENTITLEMENT', l_date_to));
1151 END IF;
1152
1153 /* Logic for selecting l_mkode0620 */
1154 /*Changes for Lunar Payroll */
1155 /*IF(csr_rec.payroll_period = '2' OR csr_rec.payroll_period = '3' OR csr_rec.payroll_period = '4') THEN*/
1156 IF l_hourly_salaried = 'H' THEN
1157 l_mkode0620 := nvl(nvl(csr_rec.hourly_accr_rate,rec_get_hol_entit.hourly_accr_rate)
1158 ,PAY_DK_STATSR_PKG.GET_GLOBAL_VALUE('DK_HOLIDAY_PAY_LEGSL_PERCENTAGE', l_date_to));
1159 /*ELSIF(csr_rec.payroll_period = '1') THEN*/
1160 ELSIF l_hourly_salaried = 'S' THEN
1161 l_mkode0620 := nvl(nvl(csr_rec.sal_allow_rate,rec_get_hol_entit.sal_allow_rate)
1162 ,PAY_DK_STATSR_PKG.GET_GLOBAL_VALUE('DK_HOLIDAY_ALLOWANCE_LEGSL_PERCENTAGE', l_date_to));
1163 END IF;
1164
1165 /************** Access the balance values for the ILTYPE balances **************/
1166
1167 /* Logic for fetching l_iltype_0010_bal */
1168 l_iltype_0010_bal := PAY_DK_STATSR_PKG.GET_DEFINED_BALANCE_VALUE(l_asg_id, 'Total Income',l_dimension ,l_bal_todate) - l_old_iltype_0010_bal; -- l_date_to);
1169 l_old_iltype_0010_bal := l_old_iltype_0010_bal + l_iltype_0010_bal;
1170 /* Bug 5030983 Fixes - Start */
1171 IF csr_rec.SAL_BASIS_MKODE IN ('81','82','83','84') THEN
1172 l_iltype_0010_unit := 0;
1173 ELSE
1174 /* Take the calculated values from mkode600 and bring to Monthly Payroll Frequency */
1175 /*IF(csr_rec.payroll_period = '1') THEN*/
1176 IF l_hourly_salaried = 'S' THEN
1177 /* Changed this for bug fix 5034129 */
1178 /*l_iltype_0010_unit := ROUND(l_hours_rate * l_mul_factor * 22/5,2);*/
1179 l_iltype_0010_unit := PAY_DK_STATSR_PKG.GET_DEFINED_BALANCE_VALUE(l_asg_id, 'Total ATP Hours',l_dimension ,l_bal_todate)
1180 - PAY_DK_STATSR_PKG.GET_DEFINED_BALANCE_VALUE(l_asg_id, 'Worked Hours',l_dimension ,l_bal_todate) - l_s_old_iltype_0010_unit;
1181 l_s_old_iltype_0010_unit := l_iltype_0010_unit + l_s_old_iltype_0010_unit;
1182 /*Changes for Lunar Payroll */
1183 /*ELSIF(csr_rec.payroll_period = '2' OR csr_rec.payroll_period = '3' OR csr_rec.payroll_period = '4') THEN*/
1184 ELSIF l_hourly_salaried = 'H' THEN
1185 l_iltype_0010_unit := PAY_DK_STATSR_PKG.GET_DEFINED_BALANCE_VALUE(l_asg_id, 'Worked Hours',l_dimension ,l_bal_todate) - l_h_old_iltype_0010_unit; -- l_date_to);
1186 l_h_old_iltype_0010_unit := l_h_old_iltype_0010_unit + l_iltype_0010_unit;
1187 END IF;
1188 END IF; /* Bug 5030983 Fixes - End */
1189 /* Logic for fetching l_iltype_0011_bal */
1190 l_iltype_0011_bal := PAY_DK_STATSR_PKG.GET_DEFINED_BALANCE_VALUE(l_asg_id, 'Special Pay',l_dimension ,l_bal_todate) - l_old_iltype_0011_bal; -- l_date_to);
1191 l_old_iltype_0011_bal := l_iltype_0011_bal + l_old_iltype_0011_bal;
1192 l_iltype_0011_unit := 1;
1193
1194 /* Logic for fetching l_iltype_0013_bal */
1195 l_iltype_0013_bal := PAY_DK_STATSR_PKG.GET_DEFINED_BALANCE_VALUE(l_asg_id, 'Holidayable Pay',l_dimension ,l_bal_todate) - l_old_iltype_0013_bal; -- l_date_to);
1196 l_old_iltype_0013_bal := l_old_iltype_0013_bal + l_iltype_0013_bal;
1197 /* Added for bug 5050964*/
1198 l_iltype_0013_unit := l_iltype_0010_unit;
1199
1200 /* Logic for fetching l_iltype_0015_bal */
1201 l_iltype_0015_bal := PAY_DK_STATSR_PKG.GET_DEFINED_BALANCE_VALUE(l_asg_id, 'Total Benefits in Kind',l_dimension ,l_bal_todate) - l_old_iltype_0015_bal; -- l_date_to);
1202 l_old_iltype_0015_bal := l_old_iltype_0015_bal + l_iltype_0015_bal;
1203 l_iltype_0015_unit := 1;
1204
1205 /* Logic for fetching l_iltype_0021_bal */
1206 l_iltype_0021_bal := PAY_DK_STATSR_PKG.GET_DEFINED_BALANCE_VALUE(l_asg_id, 'Employee ATP Deductions',l_dimension ,l_bal_todate) -- l_date_to)
1207 +PAY_DK_STATSR_PKG.GET_DEFINED_BALANCE_VALUE(l_asg_id, 'Employer ATP Deductions',l_dimension ,l_bal_todate) -- l_date_to)
1208 +PAY_DK_STATSR_PKG.GET_DEFINED_BALANCE_VALUE(l_asg_id, 'Employee Pension Deductions',l_dimension ,l_bal_todate) -- l_date_to)
1209 +PAY_DK_STATSR_PKG.GET_DEFINED_BALANCE_VALUE(l_asg_id, 'Employer Pension Deductions',l_dimension ,l_bal_todate)
1210 - l_old_iltype_0021_bal; -- l_date_to);
1211 l_old_iltype_0021_bal := l_iltype_0021_bal + l_old_iltype_0021_bal;
1212 l_iltype_0021_unit := 1;
1213 /* Added condition for bug fix 4998238 */
1214 /*Changes for Lunar Payroll */
1215 /*IF(csr_rec.payroll_period = '2' OR csr_rec.payroll_period = '3' OR csr_rec.payroll_period = '4') THEN*/
1216 IF l_hourly_salaried = 'H' THEN
1217 l_iltype_0021_unit := PAY_DK_STATSR_PKG.GET_DEFINED_BALANCE_VALUE(l_asg_id, 'Worked Hours',l_dimension ,l_bal_todate) - l_old_iltype_0021_unit; -- l_date_to);
1218 l_old_iltype_0021_unit := l_iltype_0021_unit + l_old_iltype_0021_unit;
1219 END IF;
1220
1221 /* Logic for fetching l_iltype_0022_bal */
1222 l_iltype_0022_bal := PAY_DK_STATSR_PKG.GET_DEFINED_BALANCE_VALUE(l_asg_id, 'Total G_Dage Pay',l_dimension ,l_bal_todate) - l_old_iltype_0022_bal; -- l_date_to);
1223 l_old_iltype_0022_bal := l_iltype_0022_bal + l_old_iltype_0022_bal;
1224 /* After FS changes, now for both salaried and non-salaried, to report Total G_Dage_Days_ASG_XXX as units
1225 , earlier was Total G_Dage Hours for non-salaried */
1226 /* Commenting code below and re-writing to achieve this */
1227 /*IF(csr_rec.payroll_period = '1') THEN
1228 l_iltype_0022_unit := PAY_DK_STATSR_PKG.GET_DEFINED_BALANCE_VALUE(l_asg_id, 'Total G_Dage Days',l_dimension ,l_date_to);
1229 ELSIF(csr_rec.payroll_period = '2' OR csr_rec.payroll_period = '3') THEN
1230 l_iltype_0022_unit := PAY_DK_STATSR_PKG.GET_DEFINED_BALANCE_VALUE(l_asg_id, 'Total G_Dage Hours',l_dimension ,l_date_to);
1231 END IF;*/
1232 l_iltype_0022_unit := PAY_DK_STATSR_PKG.GET_DEFINED_BALANCE_VALUE(l_asg_id, 'Total G_Dage Days',l_dimension ,l_bal_todate) - l_old_iltype_0022_unit; -- l_date_to);
1233 l_old_iltype_0022_unit := l_iltype_0022_unit + l_old_iltype_0022_unit;
1234 /*Bug 5026906 fix- Changing the '<' operator to '=' in order to display
1235 the negative values*/
1236 -- IF(l_iltype_0022_unit <0) THEN
1237 IF(l_iltype_0022_unit = 0) THEN
1238 l_iltype_0022_unit := 1;
1239 END IF;
1240
1241 /* Logic for fetching l_iltype_0023_bal */
1242 /*IF(csr_rec.payroll_period = '1' ) THEN*/
1243 IF l_hourly_salaried = 'S' THEN
1244 l_iltype_0023_unit := PAY_DK_STATSR_PKG.GET_BALANCE_CATEGORY_VALUE(l_asg_id, 'Salaried Paid Absence Hours',l_dimension ,l_bal_todate) - l_s_old_iltype_0023_unit; -- l_date_to);
1245 l_s_old_iltype_0023_unit := l_s_old_iltype_0023_unit + l_iltype_0023_unit;
1246
1247 /* Calculate Hourly Rate Modified with if clause to avoid zero divide error */
1248 if l_iltype_0010_unit <> 0 then
1249 l_iltype_0023_hr_rate := ROUND((PAY_DK_STATSR_PKG.GET_BALANCE_CATEGORY_VALUE(l_asg_id, 'Salary Reporting',l_dimension ,l_bal_todate) - l_old_iltype_0023_hr_rate)/l_iltype_0010_unit,2);
1250 l_old_iltype_0023_hr_rate := PAY_DK_STATSR_PKG.GET_BALANCE_CATEGORY_VALUE(l_asg_id, 'Salary Reporting',l_dimension ,l_bal_todate);
1251 end if;
1252
1253 l_iltype_0023_bal := l_iltype_0023_hr_rate*l_iltype_0023_unit;
1254 /*Changes for Lunar Payroll */
1255 /*ELSIF(csr_rec.payroll_period = '2' OR csr_rec.payroll_period = '3' OR csr_rec.payroll_period = '4') THEN*/
1256 ELSIF l_hourly_salaried = 'H' THEN
1257 l_iltype_0023_bal := PAY_DK_STATSR_PKG.GET_BALANCE_CATEGORY_VALUE(l_asg_id, 'Hourly Paid Absence Pay',l_dimension ,l_bal_todate) - l_old_iltype_0023_bal; -- l_date_to);
1258 l_old_iltype_0023_bal := l_iltype_0023_bal + l_old_iltype_0023_bal;
1259 l_iltype_0023_unit := PAY_DK_STATSR_PKG.GET_BALANCE_CATEGORY_VALUE(l_asg_id, 'Hourly Paid Absence Hours',l_dimension ,l_bal_todate) - l_h_old_iltype_0023_unit; -- l_date_to);
1260 l_h_old_iltype_0023_unit := l_iltype_0023_unit + l_h_old_iltype_0023_unit;
1261 END IF;
1262 /* Logic for fetching l_iltype_0024_bal */
1263 /* After FS changes, it is now valid only for non-salaried ppl with balance Holiday_Accrual_Amount_ASG_XXX
1264 , whereas earlier it was valid only for salaried ppl with 'Holiday Allowance Paid' as balance
1265 and Holiday Absence Days as units.*/
1266 /* Commenting code below and re-writing to achieve this */
1267 /*
1268 IF(csr_rec.payroll_period = '1' ) THEN
1269 l_iltype_0024_bal := PAY_DK_STATSR_PKG.GET_DEFINED_BALANCE_VALUE(l_asg_id, 'Holiday Allowance Paid',l_dimension ,l_date_to);
1270 l_iltype_0024_unit := PAY_DK_STATSR_PKG.GET_DEFINED_BALANCE_VALUE(l_asg_id, 'Holiday Absence Days',l_dimension ,l_date_to);
1271 */
1272 /*Changes for Lunar Payroll */
1273 /*IF(csr_rec.payroll_period = '2' OR csr_rec.payroll_period = '3' OR csr_rec.payroll_period = '4') THEN*/
1274 IF l_hourly_salaried = 'H' THEN
1275 /* Changed for bug 5012411*/
1276 l_iltype_0024_bal := PAY_DK_STATSR_PKG.GET_DEFINED_BALANCE_VALUE(l_asg_id, 'Holiday Bank Pay',l_dimension ,l_bal_todate) - l_old_iltype_0024_bal; -- l_date_to);
1277 l_old_iltype_0024_bal := l_iltype_0024_bal + l_old_iltype_0024_bal;
1278 l_iltype_0024_unit := 1;
1279 END IF;
1280
1281 /* Logic for fetching l_iltype_0025_bal */
1282 /* After FS changes, , it is now valid only for salaried ppl with balance with balance Holiday_Accrual_Amount_ASG_XXX,
1283 whereas earlier it was valid only for non-salaried ppl with balance Holiday Bank Pay
1284 and units Holiday Absence Hours */
1285 /* Commenting code below and re-writing to achieve this */
1286 /*
1287 IF(csr_rec.payroll_period = '2' OR csr_rec.payroll_period = '3') THEN
1288 l_iltype_0025_bal := PAY_DK_STATSR_PKG.GET_DEFINED_BALANCE_VALUE(l_asg_id, 'Holiday Bank Pay',l_dimension ,l_date_to);
1289 l_iltype_0025_unit := PAY_DK_STATSR_PKG.GET_DEFINED_BALANCE_VALUE(l_asg_id, 'Holiday Absence Hours',l_dimension ,l_date_to);
1290 */
1291 /*IF(csr_rec.payroll_period = '1' ) THEN*/
1292 IF l_hourly_salaried = 'S' THEN
1293 l_iltype_0025_bal := PAY_DK_STATSR_PKG.GET_DEFINED_BALANCE_VALUE(l_asg_id, 'Holiday Accrual Amount',l_dimension ,l_bal_todate) - l_old_iltype_0025_bal; -- l_date_to);
1294 l_old_iltype_0025_bal := l_old_iltype_0025_bal + l_iltype_0025_bal;
1295 l_iltype_0025_unit := 1;
1296 END IF;
1297
1298 /* Logic for fetching l_iltype_0026_bal */
1299 l_iltype_0026_bal := 0;
1300 l_iltype_0026_unit := PAY_DK_STATSR_PKG.GET_DEFINED_BALANCE_VALUE(l_asg_id, 'Holiday Unpaid Days',l_dimension ,l_bal_todate) - l_old_iltype_0026_unit; -- l_date_to);
1301 l_old_iltype_0026_unit := l_iltype_0026_unit + l_old_iltype_0026_unit;
1302
1303 /* Logic for fetching l_iltype_0032_bal */
1304 l_iltype_0032_bal := PAY_DK_STATSR_PKG.GET_BALANCE_CATEGORY_VALUE(l_asg_id, 'Other Pay',l_dimension ,l_bal_todate) - l_old_iltype_0032_bal; -- l_date_to);
1305 l_old_iltype_0032_bal := l_old_iltype_0032_bal + l_iltype_0032_bal;
1306 l_iltype_0032_unit := 1;
1307
1308 /* Logic for fetching l_iltype_0034_bal */
1309 /* After FS changes, now applicable to Salaried Payrolls only now, earlier was for all*/
1310 /* Adding new condition, to achieve this */
1311 /*IF(csr_rec.payroll_period = '1') THEN*/
1312 IF l_hourly_salaried = 'S' THEN
1313 l_iltype_0034_bal := 0;
1314 l_iltype_0034_unit := PAY_DK_STATSR_PKG.GET_BALANCE_CATEGORY_VALUE(l_asg_id, 'Salaried Paid Absence Hours',l_dimension ,l_bal_todate) - l_old_iltype_0034_unit; -- l_date_to);
1315 l_old_iltype_0034_unit := l_iltype_0034_unit + l_old_iltype_0034_unit;
1316 END IF;
1317 /* Logic for fetching l_iltype_0036_bal */
1318 /* After FS changes, now applicable to non-salaried Payrolls only now, earlier was for all*/
1319 /* Commenting code and putting everything into the first IF condition, to achieve this */
1320 /*l_iltype_0036_unit := PAY_DK_STATSR_PKG.GET_BALANCE_CATEGORY_VALUE(l_asg_id, 'Hourly Overtime Pay',l_dimension ,l_date_to);*/
1321 /*Changes for Lunar Payroll */
1322 /*IF(csr_rec.payroll_period = '2' OR csr_rec.payroll_period = '3' OR csr_rec.payroll_period = '4') THEN*/
1323 IF l_hourly_salaried = 'H' THEN
1324 /*l_iltype_0036_bal := 0;
1325 ELSIF(csr_rec.payroll_period = '1' ) THEN */
1326 /*Bug 5020527 fix - Assigning the 'Hourly Overtime Hours' balance value to l_iltype_0036_unit
1327 and 'Hourly Overtime Pay' balance value to l_iltype_0036_bal*/
1328 /*l_iltype_0036_bal := PAY_DK_STATSR_PKG.GET_BALANCE_CATEGORY_VALUE(l_asg_id, 'Hourly Overtime Hours',l_dimension ,l_date_to);
1329 l_iltype_0036_unit := PAY_DK_STATSR_PKG.GET_BALANCE_CATEGORY_VALUE(l_asg_id, 'Hourly Overtime Pay',l_dimension ,l_date_to);*/
1330
1331 l_iltype_0036_unit := PAY_DK_STATSR_PKG.GET_BALANCE_CATEGORY_VALUE(l_asg_id, 'Hourly Overtime Hours',l_dimension ,l_bal_todate) - l_old_iltype_0036_unit; -- l_date_to);
1332 l_old_iltype_0036_unit := l_iltype_0036_unit + l_old_iltype_0036_unit;
1333 l_iltype_0036_bal := PAY_DK_STATSR_PKG.GET_BALANCE_CATEGORY_VALUE(l_asg_id, 'Hourly Overtime Pay',l_dimension ,l_bal_todate) - l_old_iltype_0036_bal; -- l_date_to);
1334 l_old_iltype_0036_bal := l_old_iltype_0036_bal + l_iltype_0036_bal;
1335 /*Bug 5026906 fix- Changing the '<' operator to '=' in order to display
1336 the negative values*/
1337 -- IF(l_iltype_0036_unit <0) THEN
1338 IF(l_iltype_0036_unit = 0) THEN
1339 l_iltype_0036_unit := 1;
1340 END IF;
1341 END IF;
1342
1343 /* Logic for fetching l_iltype_0037_bal */
1344 /* After FS changes, now applicable to Salaried Payrolls only now, earlier was for all*/
1345 /* Adding new condition, to achieve this */
1346 /*IF(csr_rec.payroll_period = '1') THEN*/
1347 IF l_hourly_salaried = 'S' THEN
1348 l_iltype_0037_bal := PAY_DK_STATSR_PKG.GET_BALANCE_CATEGORY_VALUE(l_asg_id, 'Salaried Overtime Pay',l_dimension ,l_bal_todate) - l_old_iltype_0037_bal; -- l_date_to);
1349 l_old_iltype_0037_bal := l_old_iltype_0037_bal + l_iltype_0037_bal;
1350 l_iltype_0037_unit := PAY_DK_STATSR_PKG.GET_BALANCE_CATEGORY_VALUE(l_asg_id, 'Salaried Overtime Hours',l_dimension ,l_bal_todate) - l_old_iltype_0037_unit; -- l_date_to);
1351 l_old_iltype_0037_unit := l_old_iltype_0037_unit + l_iltype_0037_unit;
1352 /*Bug 5026906 fix- Changing the '<' operator to '=' in order to display
1353 the negative values*/
1354 --IF(l_iltype_0037_unit <0) THEN
1355 IF(l_iltype_0037_unit = 0) THEN
1356 l_iltype_0037_unit := 1;
1357 END IF;
1358 END IF;
1359
1360
1361 /* Added to fix issues due to varying numeric formats */
1362 l_mkode0600 := fnd_number.number_to_canonical(l_mkode0600 );
1363 l_mkode0610 := fnd_number.number_to_canonical(l_mkode0610);
1364 l_mkode0620 := fnd_number.number_to_canonical(l_mkode0620);
1365 l_iltype_0010_bal := fnd_number.number_to_canonical(l_iltype_0010_bal);
1366 l_iltype_0011_bal := fnd_number.number_to_canonical(l_iltype_0011_bal);
1367 l_iltype_0013_bal := fnd_number.number_to_canonical(l_iltype_0013_bal);
1368 l_iltype_0015_bal := fnd_number.number_to_canonical(l_iltype_0015_bal);
1369 l_iltype_0021_bal := fnd_number.number_to_canonical(l_iltype_0021_bal);
1370 l_iltype_0022_bal := fnd_number.number_to_canonical(l_iltype_0022_bal);
1371 l_iltype_0023_bal := fnd_number.number_to_canonical(l_iltype_0023_bal);
1372 l_iltype_0024_bal := fnd_number.number_to_canonical(l_iltype_0024_bal);
1373 l_iltype_0025_bal := fnd_number.number_to_canonical(l_iltype_0025_bal);
1374 l_iltype_0026_bal := fnd_number.number_to_canonical(l_iltype_0026_bal);
1375 l_iltype_0032_bal := fnd_number.number_to_canonical(l_iltype_0032_bal);
1376 l_iltype_0034_bal := fnd_number.number_to_canonical(l_iltype_0034_bal);
1377 l_iltype_0036_bal := fnd_number.number_to_canonical(l_iltype_0036_bal);
1378 l_iltype_0037_bal := fnd_number.number_to_canonical(l_iltype_0037_bal);
1379
1380 l_iltype_0010_unit := fnd_number.number_to_canonical(l_iltype_0010_unit);
1381 l_iltype_0011_unit := fnd_number.number_to_canonical(l_iltype_0011_unit);
1382 l_iltype_0013_unit := fnd_number.number_to_canonical(l_iltype_0013_unit);
1383 l_iltype_0015_unit := fnd_number.number_to_canonical(l_iltype_0015_unit);
1384 l_iltype_0021_unit := fnd_number.number_to_canonical(l_iltype_0021_unit);
1385 l_iltype_0022_unit := fnd_number.number_to_canonical(l_iltype_0022_unit);
1386 l_iltype_0023_unit := fnd_number.number_to_canonical(l_iltype_0023_unit);
1387 l_iltype_0024_unit := fnd_number.number_to_canonical(l_iltype_0024_unit);
1388 l_iltype_0025_unit := fnd_number.number_to_canonical(l_iltype_0025_unit);
1389 l_iltype_0026_unit := fnd_number.number_to_canonical(l_iltype_0026_unit);
1390 l_iltype_0032_unit := fnd_number.number_to_canonical(l_iltype_0032_unit);
1391 l_iltype_0034_unit := fnd_number.number_to_canonical(l_iltype_0034_unit);
1392 l_iltype_0036_unit := fnd_number.number_to_canonical(l_iltype_0036_unit);
1393 l_iltype_0037_unit := fnd_number.number_to_canonical(l_iltype_0037_unit);
1394 -- Creating Initial Archive Entries
1395 /*Changes for Lunar Payroll */
1396 If csr_rec.payroll_period = '4' then
1397 l_lnr_payroll_period := '1';
1398 Else
1399 l_lnr_payroll_period := csr_rec.payroll_period;
1400 End if;
1401
1402 pay_action_information_api.create_action_information
1403 ( p_action_information_id => l_action_info_id -- OUT parameter
1404 ,p_object_version_number => l_ovn -- OUT parameter
1405 ,p_action_context_id => l_actid -- Context id = assignment action id (of Archive)
1406 ,p_action_context_type => 'AAP' -- Context type
1407 ,p_effective_date => l_effective_date -- Date of running the archive
1408 ,p_assignment_id => csr_rec.asg_id -- Assignment ID
1409 ,p_action_information_category => 'EMEA REPORT INFORMATION' -- Information Category
1410 ,p_tax_unit_id => l_le_id -- Legal Employer ID
1411 ,p_jurisdiction_code => NULL -- Tax Municipality ID
1412 ,p_action_information1 => 'PYDKSTATSA' -- Con Program Short Name
1413 ,p_action_information2 => csr_rec.payroll_name -- Payroll Name
1414 ,p_action_information3 => p_payroll_action_id -- Payroll action id (of Archive)
1415 ,p_action_information4 => csr_rec.assignment_number -- Assignment Number
1416 ,p_action_information5 => csr_rec.cpr_no -- CPR Number of Employee
1417 ,p_action_information6 => 'PART1' -- Archive part 1 on context AAP
1418 ,p_action_information7 => l_mkode0100 -- MKODE of IPTYPE 0100
1419 ,p_action_information8 => l_mkode0200 -- MKODE of IPTYPE 0200
1420 ,p_action_information9 => csr_rec.job_occ_mkode -- MKODE of IPTYPE 0300
1421 ,p_action_information10 => csr_rec.job_status_mkode -- MKODE of IPTYPE 0400
1422 ,p_action_information11 => l_mkode0600 -- MKODE of IPTYPE 0600
1423 ,p_action_information12 => l_mkode0610 -- MKODE of IPTYPE 0610
1424 ,p_action_information13 => l_mkode0620 -- MKODE of IPTYPE 0620
1425 ,p_action_information14 => to_char(l_lnr_payroll_period) -- MKODE of IPTYPE 0700 /*Changes for Lunar Payroll */
1426 ,p_action_information15 => csr_rec.sal_basis_mkode -- MKODE of IPTYPE 0800
1427 ,p_action_information16 => l_iltype_0010_bal -- Balance for ILTYPE 0010
1428 ,p_action_information17 => l_iltype_0011_bal -- Balance for ILTYPE 0011
1429 ,p_action_information18 => l_iltype_0013_bal -- Balance for ILTYPE 0013
1430 ,p_action_information19 => l_iltype_0015_bal -- Balance for ILTYPE 0015
1431 ,p_action_information20 => l_iltype_0021_bal -- Balance for ILTYPE 0021
1432 ,p_action_information21 => l_iltype_0022_bal -- Balance for ILTYPE 0022
1433 ,p_action_information22 => l_iltype_0023_bal -- Balance for ILTYPE 0023
1434 ,p_action_information23 => l_iltype_0024_bal -- Balance for ILTYPE 0024
1435 ,p_action_information24 => l_iltype_0025_bal -- Balance for ILTYPE 0025
1436 ,p_action_information25 => l_iltype_0026_bal -- Balance for ILTYPE 0026
1437 ,p_action_information26 => l_iltype_0032_bal -- Balance for ILTYPE 0032
1438 ,p_action_information27 => l_iltype_0034_bal -- Balance for ILTYPE 0034
1439 ,p_action_information28 => l_iltype_0036_bal -- Balance for ILTYPE 0036
1440 ,p_action_information29 => l_iltype_0037_bal -- Balance for ILTYPE 0037
1441 ,p_action_information30 => l_hourly_salaried -- Hourly/Salaried
1442 );
1443
1444 pay_action_information_api.create_action_information
1445 ( p_action_information_id => l_action_info_id -- OUT parameter
1446 ,p_object_version_number => l_ovn -- OUT parameter
1447 ,p_action_context_id => l_actid -- Context id = assignment action id (of Archive)
1448 ,p_action_context_type => 'AAP' -- Context type
1449 ,p_effective_date => l_effective_date -- Date of running the archive
1450 ,p_assignment_id => csr_rec.asg_id -- Assignment ID
1451 ,p_action_information_category => 'EMEA REPORT INFORMATION' -- Information Category
1452 ,p_tax_unit_id => l_le_id -- Legal Employer ID
1453 ,p_jurisdiction_code => NULL -- Tax Municipality ID
1454 ,p_action_information1 => 'PYDKSTATSA' -- Con Program Short Name
1455 ,p_action_information2 => csr_rec.payroll_name -- Payroll Name
1456 ,p_action_information3 => p_payroll_action_id -- Payroll action id (of Archive)
1457 ,p_action_information4 => csr_rec.time_off_lieu -- Time Off in Lieu
1458 ,p_action_information5 => csr_rec.asg_start_date -- Assignment Start Date
1459 ,p_action_information6 => 'PART2' -- Archive part 2 on context AAP
1460 ,p_action_information7 => l_iltype_0010_unit -- Units for ILTYPE 0010
1461 ,p_action_information8 => l_iltype_0011_unit -- Units for ILTYPE 0011
1462 ,p_action_information9 => l_iltype_0013_unit -- Units for ILTYPE 0013
1463 ,p_action_information10 => l_iltype_0015_unit -- Units for ILTYPE 0015
1464 ,p_action_information11 => l_iltype_0021_unit -- Units for ILTYPE 0021
1465 ,p_action_information12 => l_iltype_0022_unit -- Units for ILTYPE 0022
1466 ,p_action_information13 => l_iltype_0023_unit -- Units for ILTYPE 0023
1467 ,p_action_information14 => l_iltype_0024_unit -- Units for ILTYPE 0024
1468 ,p_action_information15 => l_iltype_0025_unit -- Units for ILTYPE 0025
1469 ,p_action_information16 => l_iltype_0026_unit -- Units for ILTYPE 0026
1470 ,p_action_information17 => l_iltype_0032_unit -- Units for ILTYPE 0032
1471 ,p_action_information18 => l_iltype_0034_unit -- Units for ILTYPE 0034
1472 ,p_action_information19 => l_iltype_0036_unit -- Units for ILTYPE 0036
1473 ,p_action_information20 => l_iltype_0037_unit -- Units for ILTYPE 0037
1474 ,p_action_information21 => to_char(l_bal_todate,'YYYYMMDD') -- Added for bug 5003220 to display end date instead of ass end date
1475 );
1476
1477
1478 END IF; --Bug Fix 5003220,Archiving the data only once for an assignment
1479
1480 l_asgid := csr_rec.asg_id;
1481 l_old_job_occ_mkode := csr_rec.job_occ_mkode;
1482 l_old_job_status_mkode := csr_rec.job_status_mkode;
1483 l_old_sal_basis_mkode := csr_rec.sal_basis_mkode;
1484 l_old_time_off_lieu := csr_rec.time_off_lieu;
1485
1486
1487 END LOOP;
1488
1489 END LOOP;
1490
1491
1492 IF g_debug THEN
1493 hr_utility.set_location(' Leaving Procedure ASSIGNMENT_ACTION_CODE',20);
1494 END IF;
1495
1496 EXCEPTION
1497 WHEN e_too_many_hours THEN
1498 fnd_message.set_name('PAY','PAY_377033_DK_TOO_MANY_WKG_HRS');
1499 fnd_file.put_line(fnd_file.log,fnd_message.get);
1500 error_message:=fnd_concurrent.set_completion_status('ERROR','PAY_377033_DK_TOO_MANY_WKG_HRS');
1501
1502 WHEN e_no_emp_dflts THEN
1503 fnd_message.set_name('PAY','PAY_377061_DK_NO_LE_EMP_DFLTS');
1504 fnd_message.set_token('ITEM',l_le_name);
1505 fnd_file.put_line(fnd_file.log,substr(fnd_message.get,1,254));
1506 error_message:=fnd_concurrent.set_completion_status('ERROR','PAY_377061_DK_NO_LE_EMP_DFLTS');
1507
1508 END ASSIGNMENT_ACTION_CODE;
1509
1510
1511 PROCEDURE INITIALIZATION_CODE(p_payroll_action_id IN NUMBER)
1512 IS
1513
1514
1515 BEGIN
1516
1517 NULL;
1518
1519 IF g_debug THEN
1520 hr_utility.set_location(' Entering Procedure INITIALIZATION_CODE',10);
1521 END IF;
1522
1523
1524 IF g_debug THEN
1525 hr_utility.set_location(' Leaving Procedure INITIALIZATION_CODE',20);
1526 END IF;
1527
1528 EXCEPTION WHEN OTHERS THEN
1529 g_err_num := SQLCODE;
1530
1531 IF g_debug THEN
1532 hr_utility.set_location('ORA_ERR: ' || g_err_num || 'In INITIALIZATION_CODE',30);
1533 END IF;
1534
1535 END INITIALIZATION_CODE;
1536
1537
1538
1539 /* ARCHIVE CODE */
1540 PROCEDURE ARCHIVE_CODE(p_assignment_action_id IN NUMBER
1541 ,p_effective_date IN DATE)
1542 IS
1543
1544 BEGIN
1545 IF g_debug THEN
1546 hr_utility.set_location(' Entering Procedure ARCHIVE_CODE',10);
1547 END IF;
1548
1549 IF g_debug THEN
1550 hr_utility.set_location(' Leaving Procedure ARCHIVE_CODE',20);
1551 END IF;
1552
1553 END ARCHIVE_CODE;
1554
1555
1556 PROCEDURE POPULATE_DATA
1557 (p_business_group_id IN NUMBER,
1558 p_payroll_action_id IN VARCHAR2 ,
1559 p_template_name IN VARCHAR2,
1560 p_xml OUT NOCOPY CLOB)
1561 IS
1562
1563 /* Cursor to fetch File Start and End Record Information */
1564 CURSOR csr_get_hdr_info(p_payroll_action_id NUMBER)
1565 IS
1566 SELECT *
1567 FROM pay_action_information pai
1568 WHERE action_context_type = 'PA'
1569 AND action_context_id = p_payroll_action_id
1570 AND action_information_category = 'EMEA REPORT DETAILS'
1571 AND action_information1 = 'PYDKSTATSA'
1572 AND action_information4 = 'HDR';
1573
1574
1575 /* Cursor to fetch Company Start and End Record Information */
1576 CURSOR csr_get_chdr_info(p_payroll_action_id NUMBER)
1577 IS
1578 SELECT *
1579 FROM pay_action_information pai
1580 WHERE action_context_type = 'PA'
1581 AND action_context_id = p_payroll_action_id
1582 AND action_information_category = 'EMEA REPORT DETAILS'
1583 AND action_information1 = 'PYDKSTATSA'
1584 AND action_information4 = 'CHDR';
1585
1586
1587 /* Cursors to fetch Personal and Salary Record Information */
1588 CURSOR csr_get_body_info1(p_payroll_action_id NUMBER, p_tax_unit_id NUMBER)
1589 IS
1590 SELECT *
1591 FROM pay_action_information pai
1592 WHERE action_context_type = 'AAP'
1593 AND action_information3 = to_char(p_payroll_action_id)
1594 AND action_information_category = 'EMEA REPORT INFORMATION'
1595 AND action_information1 = 'PYDKSTATSA'
1596 AND action_information6 ='PART1'
1597 AND tax_unit_id = p_tax_unit_id
1598 -- Add check on MKODE800 not being 91 or 92
1599 AND action_information15 NOT IN('91','92');
1600
1601 CURSOR csr_get_body_info2(p_payroll_action_id NUMBER, p_tax_unit_id NUMBER,p_action_context_id NUMBER, p_effective_date DATE, p_assignment_id NUMBER)
1602 IS
1603 SELECT *
1604 FROM pay_action_information pai
1605 WHERE action_context_type = 'AAP'
1606 AND action_information3 = p_payroll_action_id
1607 AND action_information_category = 'EMEA REPORT INFORMATION'
1608 AND action_information1 = 'PYDKSTATSA'
1609 AND action_information6 ='PART2'
1610 AND tax_unit_id = p_tax_unit_id
1611 AND action_context_id = p_action_context_id
1612 AND effective_date = p_effective_date
1613 AND assignment_id = p_assignment_id;
1614
1615 rec_get_hdr_info csr_get_hdr_info%ROWTYPE;
1616 rec_get_body_info2 csr_get_body_info2%ROWTYPE;
1617
1618 l_counter NUMBER := 0;
1619 l_le_count NUMBER := 0;
1620 l_payroll_action_id NUMBER;
1621
1622 l_sign VARCHAR2(80);
1623 l_bal VARCHAR2(80);
1624
1625 TYPE iptype_rec_type IS RECORD
1626 (
1627 iptype VARCHAR2(240) := ' ',
1628 mkode VARCHAR2(240) := ' '
1629 );
1630
1631 TYPE iltype_rec_type IS RECORD
1632 (
1633 iltype VARCHAR2(240) := ' ',
1634 bal VARCHAR2(240) := ' ',
1635 units VARCHAR2(240) := ' '
1636 );
1637
1638
1639 TYPE iptype_tab_type
1640 IS TABLE OF iptype_rec_type
1641 INDEX BY BINARY_INTEGER;
1642
1643
1644 TYPE iltype_tab_type
1645 IS TABLE OF iltype_rec_type
1646 INDEX BY BINARY_INTEGER;
1647
1648
1649 iptype_tab iptype_tab_type;
1650 iltype_tab iltype_tab_type;
1651
1652 BEGIN
1653
1654
1655
1656
1657 IF p_payroll_action_id IS NULL THEN
1658
1659 BEGIN
1660
1661 SELECT payroll_action_id
1662 INTO l_payroll_action_id
1663 FROM pay_payroll_actions ppa,
1664 fnd_conc_req_summary_v fcrs,
1665 fnd_conc_req_summary_v fcrs1
1666 WHERE fcrs.request_id = fnd_global.conc_request_id
1667 AND fcrs.priority_request_id = fcrs1.priority_request_id
1668 AND ppa.request_id between fcrs1.request_id and fcrs.request_id
1669 AND ppa.request_id = fcrs1.request_id;
1670
1671 EXCEPTION
1672 WHEN OTHERS THEN
1673 NULL;
1674 END ;
1675
1676 ELSE
1677
1678 l_payroll_action_id :=p_payroll_action_id;
1679
1680 END IF;
1681 hr_utility.set_location('Entered Procedure GETDATA',10);
1682
1683 /* Get the File Start and End Record Information */
1684 OPEN csr_get_hdr_info(l_payroll_action_id);
1685 FETCH csr_get_hdr_info INTO rec_get_hdr_info;
1686 CLOSE csr_get_hdr_info;
1687
1688 hr_utility.set_location('Before populating pl/sql table',20);
1689
1690 xml_tab(l_counter).TagName :='FILE_HEADER_FOOTER_START';
1691 xml_tab(l_counter).TagValue :='FILE_HEADER_FOOTER_START';
1692 l_counter := l_counter + 1;
1693
1694 xml_tab(l_counter).TagName :='CHAR_SET';
1695 xml_tab(l_counter).TagValue := rec_get_hdr_info.action_information8;
1696 l_counter := l_counter + 1;
1697
1698 xml_tab(l_counter).TagName :='FILE_FORMAT';
1699 xml_tab(l_counter).TagValue := rec_get_hdr_info.action_information9;
1700 l_counter := l_counter + 1;
1701
1702 xml_tab(l_counter).TagName :='SENDER_CVR_NO';
1703 xml_tab(l_counter).TagValue := rec_get_hdr_info.action_information10;
1704 l_counter := l_counter + 1;
1705
1706 xml_tab(l_counter).TagName :='SENDER_NAME';
1707 xml_tab(l_counter).TagValue := rec_get_hdr_info.action_information11;
1708 l_counter := l_counter + 1;
1709
1710 xml_tab(l_counter).TagName :='SENDER_ADDR';
1711 xml_tab(l_counter).TagValue := rec_get_hdr_info.action_information12;
1712 l_counter := l_counter + 1;
1713
1714 xml_tab(l_counter).TagName :='SENDER_PCODE';
1715 xml_tab(l_counter).TagValue := rec_get_hdr_info.action_information13;
1716 l_counter := l_counter + 1;
1717
1718 xml_tab(l_counter).TagName :='ITYPE_FILE_START';
1719 xml_tab(l_counter).TagValue := '1';
1720 l_counter := l_counter + 1;
1721
1722 FOR rec_get_chdr_info IN csr_get_chdr_info(l_payroll_action_id)
1723 LOOP
1724
1725 xml_tab(l_counter).TagName :='COMPANY_HEADER_FOOTER_START';
1726 xml_tab(l_counter).TagValue :='COMPANY_HEADER_FOOTER_START';
1727 l_counter := l_counter + 1;
1728
1729 l_le_count := 0;
1730
1731 xml_tab(l_counter).TagName :='LE_CVR_NO';
1732 xml_tab(l_counter).TagValue := rec_get_chdr_info.action_information5;
1733 l_counter := l_counter + 1;
1734
1735 xml_tab(l_counter).TagName :='LE_DS_WCODE';
1736 xml_tab(l_counter).TagValue := rec_get_chdr_info.action_information6;
1737 l_counter := l_counter + 1;
1738
1739 xml_tab(l_counter).TagName :='LE_DA_SCODE';
1740 xml_tab(l_counter).TagValue := rec_get_chdr_info.action_information7;
1741 l_counter := l_counter + 1;
1742
1743 xml_tab(l_counter).TagName :='LE_NAME';
1744 xml_tab(l_counter).TagValue := rec_get_chdr_info.action_information8;
1745 l_counter := l_counter + 1;
1746
1747 xml_tab(l_counter).TagName :='LE_ADDR';
1748 xml_tab(l_counter).TagValue := rec_get_chdr_info.action_information9;
1749 l_counter := l_counter + 1;
1750
1751 xml_tab(l_counter).TagName :='LE_PCODE';
1752 xml_tab(l_counter).TagValue := rec_get_chdr_info.action_information10;
1753 l_counter := l_counter + 1;
1754
1755 xml_tab(l_counter).TagName :='ITYPE_COMPANY_START';
1756 xml_tab(l_counter).TagValue := '2';
1757 l_counter := l_counter + 1;
1758
1759 FOR rec_get_body_info IN csr_get_body_info1(l_payroll_action_id,rec_get_chdr_info.tax_unit_id)
1760 LOOP
1761
1762 OPEN csr_get_body_info2(l_payroll_action_id ,rec_get_body_info.tax_unit_id,rec_get_body_info.action_context_id, rec_get_body_info.effective_date, rec_get_body_info.assignment_id);
1763 FETCH csr_get_body_info2 INTO rec_get_body_info2;
1764 CLOSE csr_get_body_info2;
1765
1766 iptype_tab(1).iptype := '0100';
1767 iptype_tab(1).mkode := rec_get_body_info.action_information7;
1768
1769 iptype_tab(2).iptype := '0200';
1770 iptype_tab(2).mkode := rec_get_body_info.action_information8;
1771
1772 iptype_tab(3).iptype := '0300';
1773 iptype_tab(3).mkode := rec_get_body_info.action_information9;
1774
1775 iptype_tab(4).iptype := '0400';
1776 iptype_tab(4).mkode := rec_get_body_info.action_information10;
1777
1778 iptype_tab(5).iptype := '0600';
1779 /* Modified for bug number 4998056. Multiply the amount with 100 to avoid decimal point */
1780 iptype_tab(5).mkode := nvl(round(FND_NUMBER.CANONICAL_TO_NUMBER(trim(rec_get_body_info.action_information11)),2) * 100,0);
1781
1782 iptype_tab(6).iptype := '0610';
1783 /* Modified for bug number 4998056. Multiply the amount with 100 to avoid decimal point */
1784 iptype_tab(6).mkode := nvl(round(FND_NUMBER.CANONICAL_TO_NUMBER(trim(rec_get_body_info.action_information12)),2) * 100,0);
1785
1786 iptype_tab(7).iptype := '0620';
1787 /* Modified for bug number 4998056. Multiply the amount with 100 to avoid decimal point */
1788 iptype_tab(7).mkode := nvl(round(FND_NUMBER.CANONICAL_TO_NUMBER(trim(rec_get_body_info.action_information13)),2) * 100,0);
1789
1790 iptype_tab(8).iptype := '0700';
1791 iptype_tab(8).mkode := rec_get_body_info.action_information14;
1792
1793 iptype_tab(9).iptype := '0800';
1794 iptype_tab(9).mkode := rec_get_body_info.action_information15;
1795
1796 /* Fixed to be 0010 for bug fix 4998180 */
1797 iltype_tab(1).iltype := '0010'; --'0100';
1798 iltype_tab(1).bal := rec_get_body_info.action_information16;
1799 iltype_tab(1).units := rec_get_body_info2.action_information7;
1800
1801 iltype_tab(2).iltype := '0011';
1802 iltype_tab(2).bal := rec_get_body_info.action_information17;
1803 iltype_tab(2).units := rec_get_body_info2.action_information8;
1804
1805 iltype_tab(3).iltype := '0013';
1806 iltype_tab(3).bal := rec_get_body_info.action_information18;
1807 iltype_tab(3).units := rec_get_body_info2.action_information9;
1808
1809 iltype_tab(4).iltype := '0015';
1810 iltype_tab(4).bal := rec_get_body_info.action_information19;
1811 iltype_tab(4).units := rec_get_body_info2.action_information10;
1812
1813 iltype_tab(5).iltype := '0021';
1814 iltype_tab(5).bal := rec_get_body_info.action_information20;
1815 iltype_tab(5).units := rec_get_body_info2.action_information11;
1816
1817 iltype_tab(6).iltype := '0022';
1818 iltype_tab(6).bal := rec_get_body_info.action_information21;
1819 iltype_tab(6).units := rec_get_body_info2.action_information12;
1820
1821 iltype_tab(7).iltype := '0023';
1822 iltype_tab(7).bal := rec_get_body_info.action_information22;
1823 iltype_tab(7).units := rec_get_body_info2.action_information13;
1824
1825 iltype_tab(8).iltype := '0024';
1826 iltype_tab(8).bal := rec_get_body_info.action_information23;
1827 iltype_tab(8).units := rec_get_body_info2.action_information14;
1828
1829 iltype_tab(9).iltype := '0025';
1830 iltype_tab(9).bal := rec_get_body_info.action_information24;
1831 iltype_tab(9).units := rec_get_body_info2.action_information15;
1832
1833 iltype_tab(10).iltype := '0026';
1834 iltype_tab(10).bal := rec_get_body_info.action_information25;
1835 iltype_tab(10).units := rec_get_body_info2.action_information16;
1836
1837 iltype_tab(11).iltype := '0032';
1838 iltype_tab(11).bal := rec_get_body_info.action_information26;
1839 iltype_tab(11).units := rec_get_body_info2.action_information17;
1840
1841 iltype_tab(12).iltype := '0034';
1842 iltype_tab(12).bal := rec_get_body_info.action_information27;
1843 iltype_tab(12).units := rec_get_body_info2.action_information18;
1844
1845 iltype_tab(13).iltype := '0036';
1846 iltype_tab(13).bal := rec_get_body_info.action_information28;
1847 iltype_tab(13).units := rec_get_body_info2.action_information19;
1848
1849 iltype_tab(14).iltype := '0037';
1850 iltype_tab(14).bal := rec_get_body_info.action_information29;
1851 iltype_tab(14).units := rec_get_body_info2.action_information20;
1852
1853
1854 FOR i IN 1..iptype_tab.COUNT
1855 LOOP
1856
1857 -- IF(iptype_tab(i).mkode <> ' ') THEN
1858 /* Bug Fix 5030983 - Commented the if condition below which is restricting the
1859 display of personal record 0600 with payroll period as weekly and biweekly.*/
1860 -- IF NOT(iptype_tab(i).iptype ='0600' AND rec_get_body_info.action_information14 IN('2','3')) THEN
1861
1862 xml_tab(l_counter).TagName :='PERSON_RECO_START';
1863 xml_tab(l_counter).TagValue :='PERSON_RECO_START';
1864 l_counter := l_counter + 1;
1865
1866 l_le_count := l_le_count + 1;
1867
1868 xml_tab(l_counter).TagName :='LE_CVR_NO';
1869 xml_tab(l_counter).TagValue := rec_get_chdr_info.action_information5;
1870 l_counter := l_counter + 1;
1871
1872 xml_tab(l_counter).TagName :='LE_DS_WCODE';
1873 xml_tab(l_counter).TagValue := rec_get_chdr_info.action_information6;
1874 l_counter := l_counter + 1;
1875
1876 xml_tab(l_counter).TagName :='LE_DA_SCODE';
1877 xml_tab(l_counter).TagValue := rec_get_chdr_info.action_information7;
1878 l_counter := l_counter + 1;
1879
1880 xml_tab(l_counter).TagName :='ASSG_NO';
1881 xml_tab(l_counter).TagValue := rec_get_body_info.action_information4;
1882 l_counter := l_counter + 1;
1883
1884 xml_tab(l_counter).TagName :='CPR_NO';
1885 xml_tab(l_counter).TagValue := rec_get_body_info.action_information5;
1886 l_counter := l_counter + 1;
1887
1888 xml_tab(l_counter).TagName :='IPTYPE';
1889 xml_tab(l_counter).TagValue := iptype_tab(i).iptype;
1890 l_counter := l_counter + 1;
1891
1892 xml_tab(l_counter).TagName :='MKODE';
1893 xml_tab(l_counter).TagValue := iptype_tab(i).mkode;
1894 l_counter := l_counter + 1;
1895
1896 xml_tab(l_counter).TagName :='EFF_DATE';
1897 xml_tab(l_counter).TagValue := rec_get_body_info2.action_information5;
1898 l_counter := l_counter + 1;
1899
1900 xml_tab(l_counter).TagName :='DATE_FROM';
1901 xml_tab(l_counter).TagValue := rec_get_hdr_info.action_information6;
1902 l_counter := l_counter + 1;
1903
1904 xml_tab(l_counter).TagName :='DATE_TO';
1905 xml_tab(l_counter).TagValue := rec_get_hdr_info.action_information7;
1906 l_counter := l_counter + 1;
1907
1908 xml_tab(l_counter).TagName :='ITYPE_PERSON';
1909 xml_tab(l_counter).TagValue := '3';
1910 l_counter := l_counter + 1;
1911
1912 xml_tab(l_counter).TagName :='PERSON_RECO_START';
1913 xml_tab(l_counter).TagValue :='PERSON_RECO_END';
1914 l_counter := l_counter + 1;
1915
1916 -- END IF; -- Bug Fix 5030983 - Commented
1917
1918 END LOOP;
1919 FOR j IN 1..iltype_tab.COUNT
1920 LOOP
1921
1922 /* Modified condition to show balances only if they are non-zero after FS changes */
1923
1924 /*IF NOT(iltype_tab(j).iltype IN ('0011','0015','0022','0024','0026','0032','0036') AND iltype_tab(j).bal = '0')
1925 OR (iltype_tab(j).iltype = '0037' AND rec_get_body_info.action_information4 <>'N')
1926 OR (iltype_tab(j).iltype = '0024' AND rec_get_body_info.action_information14 IN('2','3'))
1927 OR (iltype_tab(j).iltype = '0025' AND rec_get_body_info.action_information14 ='1')
1928 */
1929 /*Modified with or clause for 0026 for bug5009836 */
1930 /* IF ( iltype_tab(j).bal <> '0'
1931 OR (iltype_tab(j).iltype = '0037' AND rec_get_body_info.action_information4 ='N')
1932 OR (iltype_tab(j).iltype = '0024' AND rec_get_body_info.action_information14 IN('2','3'))
1933 OR (iltype_tab(j).iltype = '0025' AND rec_get_body_info.action_information14 ='1')
1934 OR (iltype_tab(j).iltype = '0026') )
1935 THEN*/
1936 /* pgopal - Bug 5747199 fix - Checking Hourly/Salaried*/
1937 /*Bug fix 5009836 include a check on unit for record 026 */
1938 IF ( iltype_tab(j).bal <> '0'
1939 OR (iltype_tab(j).iltype = '0037' AND rec_get_body_info.action_information4 ='N')
1940 OR (iltype_tab(j).iltype = '0024' AND rec_get_body_info.action_information30 ='H')
1941 OR (iltype_tab(j).iltype = '0025' AND rec_get_body_info.action_information30 ='S')
1942 OR (iltype_tab(j).iltype = '0026' AND iltype_tab(j).units <> '0') )
1943 THEN
1944
1945 xml_tab(l_counter).TagName :='SALARY_RECO_START';
1946 xml_tab(l_counter).TagValue :='SALARY_RECO_START';
1947 l_counter := l_counter + 1;
1948
1949 l_le_count := l_le_count + 1;
1950
1951 xml_tab(l_counter).TagName :='LE_CVR_NO';
1952 xml_tab(l_counter).TagValue := rec_get_chdr_info.action_information5;
1953 l_counter := l_counter + 1;
1954
1955 xml_tab(l_counter).TagName :='LE_DS_WCODE';
1956 xml_tab(l_counter).TagValue := rec_get_chdr_info.action_information6;
1957 l_counter := l_counter + 1;
1958
1959 xml_tab(l_counter).TagName :='LE_DA_SCODE';
1960 xml_tab(l_counter).TagValue := rec_get_chdr_info.action_information7;
1961 l_counter := l_counter + 1;
1962
1963 xml_tab(l_counter).TagName :='ASSG_NO';
1964 xml_tab(l_counter).TagValue := rec_get_body_info.action_information4;
1965 l_counter := l_counter + 1;
1966
1967 xml_tab(l_counter).TagName :='CPR_NO';
1968 xml_tab(l_counter).TagValue := rec_get_body_info.action_information5;
1969 l_counter := l_counter + 1;
1970
1971 xml_tab(l_counter).TagName :='ILTYPE';
1972 xml_tab(l_counter).TagValue := iltype_tab(j).iltype;
1973 l_counter := l_counter + 1;
1974
1975 IF (substr(iltype_tab(j).units,1,1) = '-') THEN
1976 l_sign := '-';
1977 l_bal := substr(iltype_tab(j).units,2);
1978 ELSE
1979 l_sign := '+';
1980 l_bal := iltype_tab(j).units;
1981 END IF;
1982
1983 xml_tab(l_counter).TagName :='TIME_UNITS';
1984 /* Modified for bug number 4997824. Multiply the balances with 100 to avoid decimal point */
1985 xml_tab(l_counter).TagValue := round(FND_NUMBER.CANONICAL_TO_NUMBER(l_bal),2) * 100;
1986 l_counter := l_counter + 1;
1987
1988 xml_tab(l_counter).TagName :='SIGN_UNITS';
1989 xml_tab(l_counter).TagValue := l_sign;
1990 l_counter := l_counter + 1;
1991
1992
1993 IF (substr(iltype_tab(j).bal,1,1) = '-') THEN
1994 l_sign := '-';
1995 l_bal := substr(iltype_tab(j).bal,2);
1996 ELSE
1997 l_sign := '+';
1998 l_bal := iltype_tab(j).bal;
1999 END IF;
2000
2001 xml_tab(l_counter).TagName :='ILTYPE_BAL';
2002 /* Modified for bug number 4997824. Multiply the amount with 100 to avoid decimal point */
2003 xml_tab(l_counter).TagValue := round(FND_NUMBER.CANONICAL_TO_NUMBER(l_bal),2) * 100;
2004 l_counter := l_counter + 1;
2005
2006 xml_tab(l_counter).TagName :='SIGN_BAL';
2007 xml_tab(l_counter).TagValue := l_sign;
2008 l_counter := l_counter + 1;
2009 -- Changed for bug 5003220 to display end date instead of ass end date
2010 xml_tab(l_counter).TagName :='DATE_FROM';
2011 xml_tab(l_counter).TagValue := rec_get_body_info2.action_information5;
2012 l_counter := l_counter + 1;
2013
2014 xml_tab(l_counter).TagName :='DATE_TO';
2015 xml_tab(l_counter).TagValue := rec_get_body_info2.action_information21;
2016 l_counter := l_counter + 1;
2017
2018 xml_tab(l_counter).TagName :='ITYPE_SALARY';
2019 xml_tab(l_counter).TagValue := '4';
2020 l_counter := l_counter + 1;
2021
2022 xml_tab(l_counter).TagName :='SALARY_RECO_START';
2023 xml_tab(l_counter).TagValue :='SALARY_RECO_END';
2024 l_counter := l_counter + 1;
2025
2026 END IF;
2027
2028 END LOOP;
2029
2030 END LOOP;
2031
2032 xml_tab(l_counter).TagName :='ITYPE_COMPANY_END';
2033 xml_tab(l_counter).TagValue := '7';
2034 l_counter := l_counter + 1;
2035
2036 xml_tab(l_counter).TagName :='COUNT_LE';
2037 xml_tab(l_counter).TagValue := l_le_count;
2038 l_counter := l_counter + 1;
2039
2040 xml_tab(l_counter).TagName :='COMPANY_HEADER_FOOTER_START';
2041 xml_tab(l_counter).TagValue :='COMPANY_HEADER_FOOTER_END';
2042 l_counter := l_counter + 1;
2043
2044 END LOOP;
2045
2046 xml_tab(l_counter).TagName :='ITYPE_FILE_END';
2047 xml_tab(l_counter).TagValue := '9';
2048 l_counter := l_counter + 1;
2049
2050 xml_tab(l_counter).TagName :='BG_DA_SYS_NO';
2051 xml_tab(l_counter).TagValue := rec_get_hdr_info.action_information14;
2052 l_counter := l_counter + 1;
2053
2054 xml_tab(l_counter).TagName :='UPDATE_DATE';
2055 xml_tab(l_counter).TagValue := to_char(rec_get_hdr_info.effective_date,'YYYYMMDD');
2056 l_counter := l_counter + 1;
2057
2058 xml_tab(l_counter).TagName :='PAYROLL_SYS_NAME';
2059 xml_tab(l_counter).TagValue := rec_get_hdr_info.action_information15;
2060 l_counter := l_counter + 1;
2061
2062 xml_tab(l_counter).TagName :='FILE_HEADER_FOOTER_START';
2063 xml_tab(l_counter).TagValue :='FILE_HEADER_FOOTER_END';
2064 l_counter := l_counter + 1;
2065
2066 hr_utility.set_location('After populating pl/sql table',30);
2067 hr_utility.set_location('Entered Procedure GETDATA',10);
2068
2069
2070 WritetoCLOB (p_xml );
2071
2072
2073 END POPULATE_DATA;
2074 /********************************************************/
2075
2076 PROCEDURE WritetoCLOB(p_xfdf_clob out nocopy CLOB) is
2077 l_xfdf_string clob;
2078 l_str1 varchar2(1000);
2079 l_str2 varchar2(20);
2080 l_str3 varchar2(20);
2081 l_str4 varchar2(20);
2082 l_str5 varchar2(20);
2083 l_str6 varchar2(30);
2084 l_str7 varchar2(1000);
2085 l_str8 varchar2(240);
2086 l_str9 varchar2(240);
2087 l_str10 varchar2(20);
2088 l_str11 varchar2(20);
2089 l_IANA_charset VARCHAR2 (50);
2090
2091 current_index pls_integer;
2092
2093 BEGIN
2094
2095 hr_utility.set_location('Entering WritetoCLOB ',10);
2096 l_IANA_charset :=PAY_DK_GENERAL.get_IANA_charset ;
2097 --l_str1 := '<?xml version="1.0" encoding="UTF-8"?> <ROOT><STATSR>' ;
2098 l_str1 := '<?xml version="1.0" encoding="'||l_IANA_charset||'"?> <ROOT><STATSR>';
2099 l_str2 := '<';
2100 l_str3 := '>';
2101 l_str4 := '</';
2102 l_str5 := '>';
2103 l_str6 := '</STATSR></ROOT>';
2104 --l_str7 := '<?xml version="1.0" encoding="UTF-8"?> <ROOT></ROOT>';
2105 l_str7 := '<?xml version="1.0" encoding="'||l_IANA_charset||'"?> <ROOT></ROOT>';
2106 l_str10 := '<STATSR>';
2107 l_str11 := '</STATSR>';
2108
2109
2110 dbms_lob.createtemporary(l_xfdf_string,FALSE,DBMS_LOB.CALL);
2111 dbms_lob.open(l_xfdf_string,dbms_lob.lob_readwrite);
2112
2113 current_index := 0;
2114
2115 IF xml_tab.count > 0 THEN
2116
2117 dbms_lob.writeAppend( l_xfdf_string, length(l_str1), l_str1 );
2118
2119
2120 FOR table_counter IN xml_tab.FIRST .. xml_tab.LAST LOOP
2121
2122 l_str8 := xml_tab(table_counter).TagName;
2123 l_str9 := xml_tab(table_counter).TagValue;
2124
2125 IF l_str9 IN ('FILE_HEADER_FOOTER_START', 'FILE_HEADER_FOOTER_END','COMPANY_HEADER_FOOTER_START' ,'COMPANY_HEADER_FOOTER_END'
2126 ,'PERSON_RECO_START','PERSON_RECO_END','SALARY_RECO_START','SALARY_RECO_END') THEN
2127
2128 IF l_str9 IN ('FILE_HEADER_FOOTER_START' , 'COMPANY_HEADER_FOOTER_START' , 'PERSON_RECO_START','SALARY_RECO_START') THEN
2129 dbms_lob.writeAppend(l_xfdf_string, length(l_str2), l_str2);
2130 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
2131 dbms_lob.writeAppend(l_xfdf_string, length(l_str3), l_str3);
2132 ELSE
2133 dbms_lob.writeAppend(l_xfdf_string, length(l_str4), l_str4);
2134 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
2135 dbms_lob.writeAppend(l_xfdf_string, length(l_str5), l_str5);
2136 END IF;
2137
2138 ELSE
2139
2140 if l_str9 is not null then
2141 l_str9 := hr_dk_utility.REPLACE_SPECIAL_CHARS(l_str9); /* Place the check after not null check*/
2142 dbms_lob.writeAppend(l_xfdf_string, length(l_str2), l_str2);
2143 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
2144 dbms_lob.writeAppend(l_xfdf_string, length(l_str3), l_str3);
2145 dbms_lob.writeAppend(l_xfdf_string, length(l_str9), l_str9);
2146 dbms_lob.writeAppend(l_xfdf_string, length(l_str4), l_str4);
2147 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
2148 dbms_lob.writeAppend(l_xfdf_string, length(l_str5), l_str5);
2149 else
2150
2151 dbms_lob.writeAppend(l_xfdf_string, length(l_str2), l_str2);
2152 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
2153 dbms_lob.writeAppend(l_xfdf_string, length(l_str3), l_str3);
2154 dbms_lob.writeAppend(l_xfdf_string, length(l_str4), l_str4);
2155 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
2156 dbms_lob.writeAppend(l_xfdf_string, length(l_str5), l_str5);
2157
2158 end if;
2159
2160 END IF;
2161
2162 END LOOP;
2163
2164 dbms_lob.writeAppend(l_xfdf_string, length(l_str6), l_str6 );
2165
2166 ELSE
2167 dbms_lob.writeAppend(l_xfdf_string, length(l_str7), l_str7 );
2168 END IF;
2169
2170 p_xfdf_clob := l_xfdf_string;
2171
2172 hr_utility.set_location('Leaving WritetoCLOB ',20);
2173
2174
2175 EXCEPTION
2176 WHEN OTHERS then
2177 HR_UTILITY.TRACE('sqlerrm ' || SQLERRM);
2178 HR_UTILITY.RAISE_ERROR;
2179 END WritetoCLOB;
2180
2181 END PAY_DK_STATSR_PKG;