[Home] [Help]
PACKAGE BODY: APPS.PAY_DK_STATSR_PKG
Source
1 PACKAGE BODY PAY_DK_STATSR_PKG AS
2 /* $Header: pydkstatsr.pkb 120.36 2012/01/19 09:10:45 rpahune ship $ */
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 -- 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_span 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_STATSR_PKG.GET_PARAMETER(legislative_parameters,'LEGAL_EMPLOYER')
95 ,PAY_DK_STATSR_PKG.GET_PARAMETER(legislative_parameters,'PAYROLL')
96 ,PAY_DK_STATSR_PKG.GET_PARAMETER(legislative_parameters,'SPAN_RPT')
97 ,PAY_DK_STATSR_PKG.GET_PARAMETER(legislative_parameters,'ARCHIVE')
98 ,effective_date
99 ,fnd_date.canonical_to_date(PAY_DK_STATSR_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_span
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 -- GET_DEFINED_BALANCE_VALUE used to fetch value of Defined Balance
167 --------------------------------------------------------------------
168
169 FUNCTION GET_DEFINED_BALANCE_VALUE
170 (p_assignment_id IN NUMBER
171 ,p_balance_name IN VARCHAR2
172 ,p_balance_dim IN VARCHAR2
173 ,p_virtual_date IN DATE) RETURN NUMBER IS
174
175 l_context1 PAY_DEFINED_BALANCES.DEFINED_BALANCE_ID%TYPE;
176 l_value NUMBER;
177
178
179 CURSOR get_dbal_id(p_balance_name VARCHAR2 , p_balance_dim VARCHAR2) IS
180 SELECT pdb.defined_balance_id
181 FROM pay_defined_balances pdb
182 ,pay_balance_types pbt
183 ,pay_balance_dimensions pbd
184 WHERE pbt.legislation_code='DK'
185 AND pbt.balance_name = p_balance_name
186 AND pbd.legislation_code = 'DK'
187 AND pbd.database_item_suffix = p_balance_dim
188 AND pdb.balance_type_id = pbt.balance_type_id
189 AND pdb.balance_dimension_id = pbd.balance_dimension_id;
190
191
192 BEGIN
193
194 OPEN get_dbal_id(p_balance_name, p_balance_dim);
195 FETCH get_dbal_id INTO l_context1;
196 CLOSE get_dbal_id;
197
198 l_value := pay_balance_pkg.get_value(l_context1,p_assignment_id,p_virtual_date);
199
200 RETURN l_value;
201
202 END GET_DEFINED_BALANCE_VALUE ;
203
204
205
206 ---------------------------------------------------------------------------------------
207 -- GET_BALANCE_CATEGORY_VALUE used to fetch value of Balances on a defined Category
208 ---------------------------------------------------------------------------------------
209 FUNCTION GET_BALANCE_CATEGORY_VALUE
210 (p_assignment_id IN NUMBER
211 ,p_balance_cat_name IN VARCHAR2
212 ,p_balance_dim IN VARCHAR2
213 ,p_virtual_date IN DATE) RETURN NUMBER IS
214
215 l_context1 PAY_DEFINED_BALANCES.DEFINED_BALANCE_ID%TYPE;
216 l_tot_value NUMBER;
217
218
219 CURSOR get_dbal_id(p_balance_cat_name VARCHAR2 , p_balance_dim VARCHAR2) IS
220 SELECT pdb.defined_balance_id DBAL_ID
221 FROM pay_defined_balances pdb
222 ,pay_balance_types pbt
223 ,pay_balance_dimensions pbd
224 ,pay_balance_categories_f pbc
225 WHERE pbc.category_name = p_balance_cat_name
226 AND pbt.balance_category_id = pbc.balance_category_id
227 AND pbd.legislation_code = 'DK'
228 AND pbd.database_item_suffix = p_balance_dim
229 AND pdb.balance_type_id = pbt.balance_type_id
230 AND pdb.balance_dimension_id = pbd.balance_dimension_id;
231
232
233 BEGIN
234
235 l_tot_value :=0;
236
237 FOR rec_get_dbal_id IN get_dbal_id(p_balance_cat_name, p_balance_dim)
238 LOOP
239 l_context1 := rec_get_dbal_id.dbal_id;
240
241 l_tot_value := l_tot_value + pay_balance_pkg.get_value(l_context1,p_assignment_id,p_virtual_date);
242
243 END LOOP;
244
245 RETURN l_tot_value;
246
247 END GET_BALANCE_CATEGORY_VALUE ;
248
249 --------------------------------------------------------------------------------------
250 -- RANGE_CODE to specify ranges of assignments to be processed in the archive.
251 --------------------------------------------------------------------------------------
252
253 PROCEDURE RANGE_CODE (pactid IN NUMBER
254 ,sqlstr OUT NOCOPY VARCHAR2)
255 IS
256
257 -- Variable's declarations
258
259 l_count NUMBER := 0;
260 l_action_info_id NUMBER;
261 l_ovn NUMBER;
262 l_business_group_id NUMBER;
263 l_payroll_id NUMBER;
264 l_sender_id NUMBER;
265 l_span VARCHAR(80);
266 l_effective_date DATE;
267 l_report_end_date DATE;
268 l_archive VARCHAR2(80);
269
270 l_from_date VARCHAR2(80);
271 l_to_date VARCHAR2(80);
272
273 l_char_set VARCHAR2(240);
274 l_format VARCHAR2(240);
275 l_bg_da_sys_no VARCHAR2(240);
276 l_sys_name VARCHAR2(240);
277
278 l_le_id NUMBER;
279 l_le_cvr_no VARCHAR2(240);
280 l_le_ds_wpcode VARCHAR2(240);
281 l_le_da_scode VARCHAR2(240);
282 l_le_name VARCHAR2(240);
283 l_le_addr VARCHAR2(240);
284 l_le_pcode VARCHAR2(240);
285 l_le_punit VARCHAR2(10);
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 ,hoi2.ORG_INFORMATION6 PUNIT
335 ,hou.name NAME
336 -- ,loc.ADDRESS_LINE_1||' '||loc.ADDRESS_LINE_2||' '||loc.ADDRESS_LINE_3 ADDR
337 ,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
338 ,loc.postal_code PCODE
339 FROM HR_ORGANIZATION_UNITS hou
340 , HR_ORGANIZATION_INFORMATION hoi1
341 , HR_ORGANIZATION_INFORMATION hoi2
342 , HR_LOCATIONS loc
343 WHERE hou.business_group_id = p_business_group_id
344 and hoi1.organization_id = hou.organization_id
345 and hou.location_id = loc.LOCATION_ID(+)
346 and hoi1.ORG_INFORMATION_CONTEXT='CLASS'
347 and hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
348 and hoi1.ORG_INFORMATION2 = 'Y'
349 and hoi2.ORG_INFORMATION_CONTEXT='DK_LEGAL_ENTITY_DETAILS'
350 and hoi2.organization_id = hoi1.organization_id
351 and nvl(hoi2.org_information1,0)= nvl2(p_sender_id,p_sender_cvr_no,nvl(hoi2.org_information1,0) )
352 and p_effective_date BETWEEN hou.DATE_FROM and nvl(hou.DATE_TO, p_effective_date);
353
354
355 /* Cursor to fetch the Legal Employer DA Office Codes */
356 /* Modified check on context for bug fix 4997786 */
357 CURSOR csr_get_le_da_off_codes(p_le_id NUMBER, p_effective_date DATE, p_business_group_id NUMBER) IS
358 SELECT hoi2.ORG_INFORMATION1 DA_SCODE
359 FROM HR_ORGANIZATION_UNITS hou
360 , HR_ORGANIZATION_INFORMATION hoi1
361 , HR_ORGANIZATION_INFORMATION hoi2
362 WHERE hou.business_group_id = p_business_group_id
363 and hoi1.organization_id = hou.organization_id
364 and hoi1.organization_id = p_le_id
365 and hoi1.ORG_INFORMATION_CONTEXT='CLASS'
366 and hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
367 and hoi1.ORG_INFORMATION2 = 'Y'
368 -- For bug fix 4997786
369 and hoi2.ORG_INFORMATION_CONTEXT= 'DK_DA_OFFICE_CODE' --'DK_EMPLOYMENT_DEFAULTS'
370 and hoi2.organization_id = hoi1.organization_id
371 and p_effective_date BETWEEN hou.DATE_FROM and nvl(hou.DATE_TO, p_effective_date);
372
373
374 /* Cursor to fetch the Business Group Details */
375 CURSOR csr_get_bg_details(p_business_group_id NUMBER, p_effective_date DATE) IS
376 SELECT hoi2.ORG_INFORMATION1 DA_SYS_NO
377 FROM HR_ORGANIZATION_UNITS hou
378 , HR_ORGANIZATION_INFORMATION hoi1
379 , HR_ORGANIZATION_INFORMATION hoi2
380 WHERE hou.business_group_id = p_business_group_id
381 and hoi1.organization_id = hou.organization_id
382 and hoi1.organization_id = p_business_group_id
383 and hoi1.ORG_INFORMATION_CONTEXT='CLASS'
384 and hoi1.org_information1 = 'HR_BG'
385 and hoi1.ORG_INFORMATION2 = 'Y'
386 and hoi2.ORG_INFORMATION_CONTEXT='DK_IDENTIFICATION_CODES'
387 and hoi2.organization_id = hoi1.organization_id
388 and p_effective_date BETWEEN hou.DATE_FROM and nvl(hou.DATE_TO, p_effective_date);
389
390
391 rec_sender_details csr_get_sender_details%ROWTYPE;
392 rec_le_details csr_get_le_details%ROWTYPE;
393 rec_get_le_da_off_codes csr_get_le_da_off_codes%ROWTYPE;
394 rec_bg_details csr_get_bg_details%ROWTYPE;
395
396 --8848543
397 /* Cursor to fetch the Period dates*/
398 Cursor csr_pd_dates (p_end_date DATE, p_payroll_id NUMBER)
399 IS
400 select *
401 from
402 per_time_periods
403 where
404 payroll_id = p_payroll_id
405 and p_end_date between START_DATE AND END_DATE;
406
407 l_rec_pd_dates csr_pd_dates%rowtype;
408
409 --8848543
410
411 BEGIN
412
413 IF g_debug THEN
414 hr_utility.set_location(' Entering Procedure RANGE_CODE',10);
415 END IF;
416
417
418 -- The sql string to return
419 sqlstr := 'SELECT DISTINCT person_id
420 FROM per_people_f ppf
421 ,pay_payroll_actions ppa
422 WHERE ppa.payroll_action_id = :payroll_action_id
423 AND ppa.business_group_id = ppf.business_group_id
424 ORDER BY ppf.person_id';
425
426 -- Fetch the input parameter values
427 PAY_DK_STATSR_PKG.GET_ALL_PARAMETERS(
428 pactid
429 ,l_business_group_id
430 ,l_payroll_id
431 ,l_sender_id
432 ,l_span
433 ,l_effective_date
434 ,l_report_end_date
435 ,l_archive) ;
436
437 -- Check if we have to archive again
438 IF (l_archive = 'Y') THEN
439 -- Check if record for current archive exists
440 OPEN csr_count;
441 FETCH csr_count INTO l_count;
442 CLOSE csr_count;
443
444 -- Archive Report Details only if no record exists
445 IF (l_count < 1) THEN
446
447
448 /* To obtain Reporting From and Reporting To Dates from Span specified in parameters */
449
450 l_to_date := to_char(l_report_end_date,'YYYYMMDD');
451
452 IF (l_span ='Q') THEN
453
454 l_from_date := to_char(trunc(l_report_end_date,'Q'),'YYYYMMDD');
455
456 ELSIF (l_span ='HY') THEN
457
458 l_from_date := to_char(trunc(trunc(l_report_end_date,'Q')-1,'Q'),'YYYYMMDD');
459
460 ELSIF (l_span ='Y') THEN
461
462 l_from_date := to_char(trunc(l_report_end_date,'Y'),'YYYYMMDD');
463
464 END IF;
465
466
467 /* To set Character Set and Format */
468
469 l_char_set := '3';
470 l_format := '1';
471
472 /* To obtain Sender's details */
473 /* The Sender would be Service Provider if present in the system or else it would be the Legal Employer Specified */
474
475 OPEN csr_get_sender_details(l_sender_id,fnd_date.canonical_to_date(l_to_date),l_business_group_id);
476 FETCH csr_get_sender_details INTO rec_sender_details;
477 CLOSE csr_get_sender_details;
478
479 l_sender_cvr_no := rec_sender_details.cvr_no;
480 l_sender_name := rec_sender_details.name;
481 l_sender_addr := rec_sender_details.addr;
482 l_sender_pcode := rec_sender_details.pcode;
483
484 /* To obtain Business Group details */
485
486 OPEN csr_get_bg_details(l_business_group_id,fnd_date.canonical_to_date(l_to_date));
487 FETCH csr_get_bg_details INTO rec_bg_details;
488 CLOSE csr_get_bg_details;
489
490 l_bg_da_sys_no := rec_bg_details.da_sys_no;
491 l_sys_name := GET_LOOKUP_MEANING ('DK_STATSR_LABELS','OP');
492 IF l_bg_da_sys_no IS NULL
493 THEN
494 RAISE e_no_da_sys_no;
495 END IF;
496
497 --8848543
498 IF l_span ='P' AND l_payroll_id IS NOT NULL THEN
499 OPEN csr_pd_dates (l_report_end_date, l_payroll_id);
500 FETCH csr_pd_dates INTO l_rec_pd_dates;
501 CLOSE csr_pd_dates;
502
503 l_from_date := to_char(l_rec_pd_dates.start_date,'YYYYMMDD');
504 l_to_date := to_char(l_rec_pd_dates.end_date,'YYYYMMDD');
505 END IF;
506 --8848543
507
508 -- Archive the REPORT DETAILS
509
510 pay_action_information_api.create_action_information
511 (p_action_information_id => l_action_info_id -- out parameter
512 ,p_object_version_number => l_ovn -- out parameter
513 ,p_action_context_id => pactid -- context id = payroll action id (of Archive)
514 ,p_action_context_type => 'PA' -- context type
515 ,p_effective_date => l_effective_date -- Date of Running the Archive
516 ,p_action_information_category => 'EMEA REPORT DETAILS' -- Information Category
517 ,p_tax_unit_id => NULL -- Legal Employer ID
518 ,p_jurisdiction_code => NULL -- Tax Municipality ID
519 ,p_action_information1 => 'PYDKSTATSA' -- Conc Prg Short Name
520 ,p_action_information2 => l_business_group_id -- Business Group ID
521 ,p_action_information3 => l_payroll_id -- Payroll ID
522 ,p_action_information4 => 'HDR' -- Specifies data is for File Header
523 ,p_action_information5 => l_span -- Span of report
524 ,p_action_information6 => l_from_date -- Report's from date
525 ,p_action_information7 => l_to_date -- Report's to date
526 ,p_action_information8 => l_char_set -- Character Set
527 ,p_action_information9 => l_format -- Format used
528 ,p_action_information10 => l_sender_cvr_no -- Sender's CVR number
529 ,p_action_information11 => l_sender_name -- Sender's Name
530 ,p_action_information12 => l_sender_addr -- Sender's Address
531 ,p_action_information13 => l_sender_pcode -- Sender's Postal Code
532 ,p_action_information14 => l_bg_da_sys_no -- BG's DA System Number
533 ,p_action_information15 => l_sys_name -- Payroll System Name
534 );
535
536
537 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)
538 LOOP
539 /* To obtain Legal Employer's details from details provided in File Header*/
540
541
542 l_le_cvr_no := rec_le_details.cvr_no;
543 l_le_ds_wpcode := rec_le_details.ds_wpcode;
544 l_le_name := rec_le_details.name;
545 l_le_addr := rec_le_details.addr;
546 l_le_pcode := rec_le_details.pcode;
547 l_le_id := rec_le_details.org_id;
548 l_le_punit := rec_le_details.punit;
549
550
551 OPEN csr_get_le_da_off_codes(l_le_id,fnd_date.canonical_to_date(l_to_date),l_business_group_id);
552 FETCH csr_get_le_da_off_codes INTO rec_get_le_da_off_codes;
553 CLOSE csr_get_le_da_off_codes;
554
555 l_le_da_scode := rec_get_le_da_off_codes.da_scode;
556
557 pay_action_information_api.create_action_information
558 (
559 p_action_information_id => l_action_info_id -- out parameter
560 ,p_object_version_number => l_ovn -- out parameter
561 ,p_action_context_id => pactid -- context id = payroll action id (of Archive)
562 ,p_action_context_type => 'PA' -- context type
563 ,p_effective_date => l_effective_date -- Date of Running the Archive
564 ,p_action_information_category => 'EMEA REPORT DETAILS' -- Information Category
565 ,p_tax_unit_id => l_le_id -- Legal Employer ID
566 ,p_jurisdiction_code => NULL -- Tax Municipality ID
567 ,p_action_information1 => 'PYDKSTATSA' -- Conc Prg Short Name
568 ,p_action_information2 => l_business_group_id -- Business Group ID
569 ,p_action_information3 => l_payroll_id -- Payroll ID
570 ,p_action_information4 => 'CHDR' -- Specifies data is for File Sub-Header for Company
571 ,p_action_information5 => l_le_cvr_no -- LE's CVR number
572 ,p_action_information6 => l_le_ds_wpcode -- LE's DS Workplace Code
573 ,p_action_information7 => l_le_da_scode -- LE's DA Society Code
574 ,p_action_information8 => l_le_name -- LE's Name
575 ,p_action_information9 => l_le_addr -- LE's Address
576 ,p_action_information10 => l_le_pcode -- LE's Postal Code
577 ,p_action_information11 => l_le_punit -- LE's Production Unit Code
578 );
579
580 END LOOP;
581
582 END IF;
583
584 END IF;
585
586 IF g_debug THEN
587 hr_utility.set_location(' Leaving Procedure RANGE_CODE',20);
588 END IF;
589
590 EXCEPTION WHEN e_no_da_sys_no THEN
591 fnd_message.set_name('PAY','PAY_377058_DK_NO_DA_CODE_ERR');
592 fnd_file.put_line(fnd_file.log,fnd_message.get);
593 error_message:=fnd_concurrent.set_completion_status('ERROR','PAY_377058_DK_NO_DA_CODE_ERR');
594 END RANGE_CODE;
595
596
597 --------------------------------------------------------------------------------------
598 -- ASSIGNMENT_ACTION_CODE to create the assignment actions to be processed.
599 --------------------------------------------------------------------------------------
600
601 PROCEDURE ASSIGNMENT_ACTION_CODE
602 (p_payroll_action_id IN NUMBER
603 ,p_start_person IN NUMBER
604 ,p_end_person IN NUMBER
605 ,p_chunk IN NUMBER)
606 IS
607
608 /* Cursor to fetch useful header information to transfer to body records from already archived header information */
609 CURSOR csr_get_hdr_info(p_payroll_action_id NUMBER) IS
610 SELECT effective_date
611 ,fnd_date.CANONICAL_TO_DATE(action_information6) from_date
612 ,fnd_date.CANONICAL_TO_DATE(action_information7) to_date
613 ,to_number(action_information2) business_group_id
614 FROM pay_action_information pai
615 WHERE action_context_type = 'PA'
616 AND action_context_id = p_payroll_action_id
617 AND action_information_category = 'EMEA REPORT DETAILS'
618 AND action_information1 = 'PYDKSTATSA'
619 AND action_information4 = 'HDR';
620
621
622 /* Cursor to fetch useful sub-header information to transfer to body records from already archived sub-header information */
623 CURSOR csr_get_sub_hdr_info(p_payroll_action_id NUMBER) IS
624 SELECT tax_unit_id
625 ,to_number(action_information3) PAYROLL_ID
626 ,action_information8 LE_NAME
627 , action_information11 LE_PUNIT
628 FROM pay_action_information pai
629 WHERE action_context_type = 'PA'
630 AND action_context_id = p_payroll_action_id
631 AND action_information_category = 'EMEA REPORT DETAILS'
632 AND action_information1 = 'PYDKSTATSA'
633 AND action_information4 = 'CHDR';
634
635 /* Cursor to fetch the Legal Employer level Employment Defaults */
636 CURSOR csr_get_le_emp_dflts(p_le_id NUMBER, p_effective_date DATE, p_business_group_id NUMBER) IS
637 SELECT hoi2.ORG_INFORMATION1 COND_OF_EMP
638 ,hoi2.ORG_INFORMATION2 EMP_GRP
639 ,hoi2.ORG_INFORMATION3 WORK_HOURS
640 ,hoi2.ORG_INFORMATION4 FREQ
641 FROM HR_ORGANIZATION_UNITS hou
642 , HR_ORGANIZATION_INFORMATION hoi1
643 , HR_ORGANIZATION_INFORMATION hoi2
644 WHERE hou.business_group_id = p_business_group_id
645 and hoi1.organization_id = hou.organization_id
646 and hoi1.organization_id = p_le_id
647 and hoi1.ORG_INFORMATION_CONTEXT='CLASS'
648 and hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
649 and hoi1.ORG_INFORMATION2 = 'Y'
650 and hoi2.ORG_INFORMATION_CONTEXT='DK_EMPLOYMENT_DEFAULTS'
651 and hoi2.organization_id = hoi1.organization_id
652 and p_effective_date BETWEEN hou.DATE_FROM and nvl(hou.DATE_TO, p_effective_date);
653
654
655 /* Cursor to fetch the Legal Employer level Holiday Entitlement */
656 CURSOR csr_get_hol_entit(p_le_id NUMBER, p_effective_date DATE, p_business_group_id NUMBER) IS
657 SELECT hoi2.ORG_INFORMATION1 DEFAULT_WORK_PATT
658 ,hoi2.ORG_INFORMATION3 HOURLY_ACCR_RATE
659 ,hoi2.ORG_INFORMATION4 SAL_ALLOW_RATE
660 FROM HR_ORGANIZATION_UNITS hou
661 , HR_ORGANIZATION_INFORMATION hoi1
662 , HR_ORGANIZATION_INFORMATION hoi2
663 WHERE hou.business_group_id = p_business_group_id
664 and hoi1.organization_id = hou.organization_id
665 and hoi1.organization_id = p_le_id
666 and hoi1.ORG_INFORMATION_CONTEXT='CLASS'
667 and hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
668 and hoi1.ORG_INFORMATION2 = 'Y'
669 and hoi2.ORG_INFORMATION_CONTEXT='DK_HOLIDAY_ENTITLEMENT_INFO'
670 and hoi2.organization_id = hoi1.organization_id
671 and p_effective_date BETWEEN hou.DATE_FROM and nvl(hou.DATE_TO, p_effective_date);
672
673 /* Cursor to fetch the HR Org level Production Unit Code*/
674 CURSOR csr_get_hr_org_info(hr_org_id hr_organization_information.organization_id%type)
675 IS
676 SELECT hoi2.ORG_INFORMATION6
677 FROM hr_organization_information hoi1
678 , hr_organization_information hoi2
679 WHERE hoi1.organization_id = hoi2.organization_id
680 AND hoi1.organization_id = hr_org_id
681 AND hoi1.org_information1 = 'HR_ORG'
682 AND hoi1.org_information_context = 'CLASS'
683 AND hoi2.ORG_INFORMATION_CONTEXT='DK_EMPLOYMENT_DEFAULTS';
684
685 rg_csr_get_hr_org_info csr_get_hr_org_info%rowtype;
686
687 /* Cursor to fetch the Assgt location level Production Unit Code*/
688 CURSOR csr_location_info (p_location_id hr_location_extra_info.location_id%TYPE) IS
689 SELECT lei_information1
690 FROM hr_location_extra_info
691 WHERE location_id = p_location_id
692 AND information_type='DK_LOCATION_INFO';
693
694 rg_csr_location_info csr_location_info%ROWTYPE;
695
696 /* Cursor to fetch the Assignments, on which pre-payments has been completed */
697 CURSOR csr_assignments
698 ( p_payroll_action_id NUMBER
699 ,p_payroll_id NUMBER
700 ,p_start_person NUMBER
701 ,p_end_person NUMBER
702 ,p_date_from DATE
703 ,p_date_to DATE
704 ,p_le_id NUMBER
705 ,p_effective_date DATE
706 ,p_pd_date_to DATE --8848543
707 ) IS
708 SELECT distinct
709 paaf.assignment_id ASG_ID
710 ,ppf.payroll_name PAYROLL_NAME
711 ,ppf.payroll_id --8848543
712 ,paaf.assignment_number ASSIGNMENT_NUMBER
713 -- For Bug 9002015
714 ,to_char(paaf.effective_start_date,'YYYYMMDD') ASG_START_DATE --9865127
715 ,paaf.effective_start_date ASG_START_DATE1 --9865127
716 -- Selecting assignments effective end date to fetch correct balance value - Bug 5003744
717 ,paaf.effective_end_date ASG_END_DATE
718 ,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
719 ,scl.SEGMENT3 COND_OF_EMP
720 ,scl.SEGMENT4 EMP_GRP
721 ,scl.SEGMENT14 JOB_OCC_MKODE
722 ,scl.SEGMENT15 JOB_STATUS_MKODE
723 ,paaf.NORMAL_HOURS NORMAL_HOURS
724 ,paaf.FREQUENCY FREQ
725 ,scl.SEGMENT10 DEFAULT_WORK_PATT
726 ,scl.SEGMENT11 HOURLY_ACCR_RATE
727 ,scl.SEGMENT13 SAL_ALLOW_RATE
728 ,decode(ppf.PERIOD_TYPE
729 ,'Calendar Month','1'
730 ,'Bi-Week' ,'2'
731 ,'Week' ,'3'
732 ,'Lunar Month' ,'4') PAYROLL_PERIOD /*Changes for Lunar Payroll */
733 ,scl.SEGMENT16 SAL_BASIS_MKODE
734 ,scl.SEGMENT17 TIME_OFF_LIEU
735 ,paaf.hourly_salaried_code HOURLY_SALARIED_CODE
736 ,paaf.organization_id HR_ORG_ID
737 ,paaf.location_id LOC_ID
738 ,papf.person_id --9865127
739 FROM
740 per_all_people_f papf
741 ,per_all_assignments_f paaf
742 ,pay_payrolls_f ppf
743 ,hr_soft_coding_keyflex scl
744 ,pay_assignment_actions paa
745 ,pay_payroll_actions ppa
746 ,per_time_periods ptp --8848543
747 WHERE paaf.person_id BETWEEN p_start_person AND p_end_person
748 AND papf.PERSON_ID = paaf.PERSON_ID
749 AND ppf.payroll_id = nvl(p_payroll_id,ppf.payroll_id)
750 AND paaf.payroll_id = ppf.payroll_id
751 AND ptp.payroll_id = paaf.payroll_id --8848543
752 AND ptp.payroll_id = ppf.payroll_id --8848543
753 AND paaf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
754 AND scl.enabled_flag = 'Y'
755 AND paa.assignment_id = paaf.assignment_id
756 AND ppa.payroll_action_id = paa.payroll_action_id
757 AND paa.action_status IN ('C','S') -- 10229494
758 AND ppa.action_type IN ('P','U') -- Pre-Payments
759 --8848543
760 --AND ppa.effective_date BETWEEN p_date_from AND p_date_to
761 AND ppa.effective_date BETWEEN NVL(p_date_from,ptp.start_date) and NVL(p_date_to,ptp.end_date)
762 AND nvl(p_pd_date_to,ptp.start_date) BETWEEN ptp.start_date and ptp.end_date
763 --8848543
764 /* Modified for bug 5003744 - Start */
765 --AND p_date_to BETWEEN paaf.EFFECTIVE_START_DATE AND paaf.EFFECTIVE_END_DATE
766 -- Added for Multi Record nprasath
767 -- For Bug 9192911
768 -- AND ppa.effective_date <= paaf.EFFECTIVE_END_DATE
769 -- AND ppa.effective_date >= paaf.EFFECTIVE_start_DATE
770 --8848543
771 --AND paaf.EFFECTIVE_START_DATE <= p_date_to
772 --AND paaf.EFFECTIVE_END_DATE >= p_date_from
773 AND paaf.EFFECTIVE_START_DATE <= NVL(p_date_to,ptp.end_date)
774 AND paaf.EFFECTIVE_END_DATE >= NVL(p_date_from,ptp.start_date)
775 --8848543
776 AND papf.current_employee_flag = 'Y'
777 /* Modified for bug 5003744 - End */
778 AND scl.segment1 = to_char(p_le_id)
779 ORDER BY asg_id, ASG_START_DATE; --9865127
780
781 --9865127
782 CURSOR csr_assignments_all
783 ( p_payroll_id NUMBER
784 ,p_person_id NUMBER
785 ,p_date_to DATE
786 ,p_le_id NUMBER
787 ,p_effective_date DATE
788 ,p_assignment_id NUMBER
789 ) IS
790 SELECT distinct
791 paaf.assignment_id ASG_ID
792 ,ppf.payroll_name PAYROLL_NAME
793 ,ppf.payroll_id --8848543
794 ,paaf.assignment_number ASSIGNMENT_NUMBER
795 -- For Bug 9002015
796 ,to_char(paaf.effective_start_date,'YYYYMMDD') ASG_START_DATE --9865127
797 --,paaf.effective_start_date ASG_START_DATE1 --9865127
798 -- Selecting assignments effective end date to fetch correct balance value - Bug 5003744
799 ,paaf.effective_end_date ASG_END_DATE
800 ,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
801 ,scl.SEGMENT3 COND_OF_EMP
802 ,scl.SEGMENT4 EMP_GRP
803 ,scl.SEGMENT14 JOB_OCC_MKODE
804 ,scl.SEGMENT15 JOB_STATUS_MKODE
805 ,paaf.NORMAL_HOURS NORMAL_HOURS
806 ,paaf.FREQUENCY FREQ
807 ,scl.SEGMENT10 DEFAULT_WORK_PATT
808 ,scl.SEGMENT11 HOURLY_ACCR_RATE
809 ,scl.SEGMENT13 SAL_ALLOW_RATE
810 ,decode(ppf.PERIOD_TYPE
811 ,'Calendar Month','1'
812 ,'Bi-Week' ,'2'
813 ,'Week' ,'3'
814 ,'Lunar Month' ,'4') PAYROLL_PERIOD /*Changes for Lunar Payroll */
815 ,scl.SEGMENT16 SAL_BASIS_MKODE
816 ,scl.SEGMENT17 TIME_OFF_LIEU
817 ,paaf.hourly_salaried_code HOURLY_SALARIED_CODE
818 ,paaf.organization_id HR_ORG_ID
819 ,paaf.location_id LOC_ID
820 FROM
821 per_all_people_f papf
822 ,per_all_assignments_f paaf
823 ,pay_payrolls_f ppf
824 ,hr_soft_coding_keyflex scl
825 WHERE paaf.person_id = p_person_id
826 AND papf.PERSON_ID = paaf.PERSON_ID
827 AND ppf.payroll_id = p_payroll_id
828 AND paaf.payroll_id = ppf.payroll_id
829 AND paaf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
830 AND scl.enabled_flag = 'Y'
831 AND paaf.assignment_id = p_assignment_id
832 AND paaf.EFFECTIVE_START_DATE <= p_date_to
833 AND papf.current_employee_flag = 'Y'
834 AND scl.segment1 = to_char(p_le_id)
835 ORDER BY asg_id, ASG_START_DATE;
836 --9865127
837
838 -- Added by nprasath for check the assignment end date for bug 5034129
839 CURSOR csr_asg_end(
840 p_assignment_id1 NUMBER
841 ,p_date_from1 DATE
842 ,p_date_to1 DATE
843 ,p_job_occ_mkode VARCHAR2
844 ,p_job_status_mkode VARCHAR2
845 ,p_sal_basis_mkode VARCHAR2
846 ,p_time_off_lieu VARCHAR2
847 ,p_pre_asg_end_date DATE
848 ,p_loc_id NUMBER
849 ,p_cond_of_emp VARCHAR2 -- For Bug 9266075
850 ,p_emp_grp VARCHAR2
851 ) is
852 select
853 paaf.effective_start_date ASG_START_DATE
854 ,paaf.effective_end_date ASG_END_DATE
855 FROM
856 per_all_assignments_f paaf
857 ,hr_soft_coding_keyflex scl
858 where
859 paaf.assignment_id = p_assignment_id1
860 AND paaf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
861 AND scl.enabled_flag = 'Y'
862 AND paaf.EFFECTIVE_START_DATE <= p_date_to1
863 AND paaf.EFFECTIVE_START_DATE >= p_pre_asg_end_date
864 AND paaf.EFFECTIVE_END_DATE >= p_date_from1
865 AND (scl.SEGMENT14 <> p_job_occ_mkode or scl.SEGMENT15 <> p_job_status_mkode
866 or scl.SEGMENT16 <> p_sal_basis_mkode or scl.SEGMENT17 <> p_time_off_lieu
867 or scl.segment3 <> p_cond_of_emp or scl.segment4 <> p_emp_grp -- Bug 9266075
868 or paaf.location_id <> p_loc_id);
869
870 --9865127
871 CURSOR csr_asg_end_other(
872 p_assignment_id1 NUMBER
873 ,p_date_from1 DATE
874 ,p_date_to1 DATE
875 --,p_job_occ_mkode VARCHAR2
876 --,p_job_status_mkode VARCHAR2
877 --,p_sal_basis_mkode VARCHAR2
878 --,p_time_off_lieu VARCHAR2
879 ,p_pre_asg_end_date DATE
880 --,p_loc_id NUMBER
881 --,p_cond_of_emp VARCHAR2 -- For Bug 9266075
882 --,p_emp_grp VARCHAR2
883 ) is
884 select
885 paaf.effective_start_date ASG_START_DATE
886 ,paaf.effective_end_date ASG_END_DATE
887 ,NVL(scl.SEGMENT14,'XXX') SEGMENT14
888 ,NVL(scl.SEGMENT15,'XXX') SEGMENT15
889 ,NVL(scl.SEGMENT16,'XXX') SEGMENT16
890 ,NVL(scl.SEGMENT17,'XXX') SEGMENT17
891 ,NVL(scl.segment3,'XXX') segment3
892 ,NVL(scl.segment4,'XXX') segment4
893 ,NVL(paaf.location_id,-999) location_id
894 FROM
895 per_all_assignments_f paaf
896 ,hr_soft_coding_keyflex scl
897 where
898 paaf.assignment_id = p_assignment_id1
899 AND paaf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
900 AND scl.enabled_flag = 'Y'
901 AND paaf.EFFECTIVE_START_DATE <= p_date_to1
902 AND paaf.EFFECTIVE_START_DATE >= p_pre_asg_end_date
903 AND paaf.EFFECTIVE_END_DATE >= p_date_from1
904 Order by ASG_START_DATE;
905
906 --9865127
907
908 csr_asg_end_check csr_asg_end%ROWTYPE;
909 rec_hdr_info csr_get_hdr_info%ROWTYPE;
910 rec_get_le_emp_dflts csr_get_le_emp_dflts%ROWTYPE;
911 rec_get_hol_entit csr_get_hol_entit%ROWTYPE;
912
913 -- Variable Declarations
914
915 l_count NUMBER := 0;
916 l_action_info_id NUMBER;
917 l_ovn NUMBER;
918 l_actid NUMBER;
919 l_asgid NUMBER := -999;
920 l_asgid_prev NUMBER := -999; --9865127
921
922 l_archive VARCHAR2(240);
923 l_payroll_id NUMBER;
924 l_le_id NUMBER;
925 l_le_name VARCHAR2(240);
926 l_effective_date DATE;
927 l_date_from DATE;
928 l_date_to DATE;
929 l_bg_id NUMBER;
930 l_punit VARCHAR2(10);
931 l_loc_punit VARCHAR2(10);
932
933 l_mkode0100 VARCHAR2(80) := ' ';
934 l_mkode0200 VARCHAR2(80) := ' ';
935 l_mkode0600 VARCHAR2(80) := ' ';
936 l_mkode0610 VARCHAR2(80) := ' ';
937 l_mkode0620 VARCHAR2(80) := ' ';
938 l_hours_rate NUMBER;
939 l_freq VARCHAR2(80);
940 l_day_max_hrs NUMBER;
941 l_old_mkode0600 VARCHAR2(80);
942
943 l_dimension VARCHAR2(80);
944 l_span VARCHAR2(80);
945 l_asg_id NUMBER;
946 l_mul_factor VARCHAR2(80);
947
948 -- For Bug 9002015
949 l_chk_asg_start_date DATE;
950 l_start_date_from VARCHAR2(10);
951
952
953 l_iltype_0010_bal NUMBER := 0;
954 l_iltype_0011_bal NUMBER := 0;
955 l_iltype_0013_bal NUMBER := 0;
956 l_iltype_0015_bal NUMBER := 0;
957 --l_iltype_0021_bal NUMBER := 0;
958 l_iltype_0022_bal NUMBER := 0;
959 l_iltype_0023_bal NUMBER := 0;
960 l_iltype_0024_bal NUMBER := 0;
961 l_iltype_0025_bal NUMBER := 0;
962 l_iltype_0026_bal NUMBER := 0;
963 --l_iltype_0032_bal NUMBER := 0;
964 l_iltype_0034_bal NUMBER := 0;
965 l_iltype_0036_bal NUMBER := 0;
966 l_iltype_0037_bal NUMBER := 0;
967
968 l_iltype_0121_bal NUMBER := 0;
969 l_iltype_0122_bal NUMBER := 0;
970 l_iltype_0027_bal NUMBER := 0;
971 l_iltype_0029_bal NUMBER := 0;
972 l_iltype_0035_bal NUMBER := 0;
973 l_iltype_0091_bal NUMBER := 0;
974 l_iltype_0210_bal NUMBER := 0;
975 l_iltype_0132_bal NUMBER := 0;
976 l_iltype_0232_bal NUMBER := 0;
977 l_iltype_0332_bal NUMBER := 0;
978
979
980 -- Added for Multiple Records
981 l_old_iltype_0010_bal NUMBER := 0;
982 l_old_iltype_0011_bal NUMBER := 0;
983 l_old_iltype_0013_bal NUMBER := 0;
984 l_old_iltype_0015_bal NUMBER := 0;
985 --l_old_iltype_0021_bal NUMBER := 0;
986 l_old_iltype_0022_bal NUMBER := 0;
987 l_old_iltype_0023_bal NUMBER := 0;
988 l_old_iltype_0024_bal NUMBER := 0;
989 l_old_iltype_0025_bal NUMBER := 0;
990 l_old_iltype_0026_bal NUMBER := 0;
991 --l_old_iltype_0032_bal NUMBER := 0;
992 l_old_iltype_0034_bal NUMBER := 0;
993 l_old_iltype_0036_bal NUMBER := 0;
994 l_old_iltype_0037_bal NUMBER := 0;
995
996 l_old_iltype_0121_bal NUMBER := 0;
997 l_old_iltype_0122_bal NUMBER := 0;
998 l_old_iltype_0027_bal NUMBER := 0;
999 l_old_iltype_0029_bal NUMBER := 0;
1000 l_old_iltype_0035_bal NUMBER := 0;
1001 l_old_iltype_0091_bal NUMBER := 0;
1002 l_old_iltype_0210_bal NUMBER := 0;
1003 l_old_iltype_0132_bal NUMBER := 0;
1004 l_old_iltype_0232_bal NUMBER := 0;
1005 l_old_iltype_0332_bal NUMBER := 0;
1006
1007 l_iltype_0010_unit NUMBER := 0;
1008 l_iltype_0011_unit NUMBER := 0;
1009 l_iltype_0013_unit NUMBER := 0;
1010 l_iltype_0015_unit NUMBER := 0;
1011 --l_iltype_0021_unit NUMBER := 0;
1012 l_iltype_0022_unit NUMBER := 0;
1013 l_iltype_0023_unit NUMBER := 0;
1014 l_iltype_0024_unit NUMBER := 0;
1015 l_iltype_0025_unit NUMBER := 0;
1016 l_iltype_0026_unit NUMBER := 0;
1017 --l_iltype_0032_unit NUMBER := 0;
1018 l_iltype_0034_unit NUMBER := 0;
1019 l_iltype_0036_unit NUMBER := 0;
1020 l_iltype_0037_unit NUMBER := 0;
1021
1022 l_iltype_0121_unit NUMBER := 0;
1023 l_iltype_0122_unit NUMBER := 0;
1024 l_iltype_0027_unit NUMBER := 0;
1025 l_iltype_0029_unit NUMBER := 0;
1026 l_iltype_0035_unit NUMBER := 0;
1027 l_iltype_0091_unit NUMBER := 0;
1028 l_iltype_0210_unit NUMBER := 0;
1029 l_iltype_0132_unit NUMBER := 0;
1030 l_iltype_0232_unit NUMBER := 0;
1031 l_iltype_0332_unit NUMBER := 0;
1032
1033 l_iltype_0023_hr_rate NUMBER := 0;
1034
1035 -- nprasath added for Multiple Records
1036 l_s_old_iltype_0010_unit NUMBER := 0;
1037 l_h_old_iltype_0010_unit NUMBER := 0;
1038 l_old_iltype_0011_unit NUMBER := 0;
1039 l_old_iltype_0013_unit NUMBER := 0;
1040 l_old_iltype_0015_unit NUMBER := 0;
1041 l_old_iltype_0021_unit NUMBER := 0;
1042 l_old_iltype_0022_unit NUMBER := 0;
1043 l_s_old_iltype_0023_unit NUMBER := 0;
1044 l_h_old_iltype_0023_unit NUMBER := 0;
1045 l_old_iltype_0024_unit NUMBER := 0;
1046 l_old_iltype_0025_unit NUMBER := 0;
1047 l_old_iltype_0026_unit NUMBER := 0;
1048 l_old_iltype_0032_unit NUMBER := 0;
1049 l_old_iltype_0034_unit NUMBER := 0;
1050 l_old_iltype_0036_unit NUMBER := 0;
1051 l_old_iltype_0037_unit NUMBER := 0;
1052 l_old_iltype_0023_hr_rate NUMBER := 0;
1053
1054 l_old_iltype_0121_unit NUMBER := 0;
1055 l_old_iltype_0122_unit NUMBER := 0;
1056 l_old_iltype_0027_unit NUMBER := 0;
1057 l_old_iltype_0029_unit NUMBER := 0;
1058 l_old_iltype_0035_unit NUMBER := 0;
1059 l_old_iltype_0091_unit NUMBER := 0;
1060 l_old_iltype_0210_unit NUMBER := 0;
1061 l_old_iltype_0132_unit NUMBER := 0;
1062 l_old_iltype_0232_unit NUMBER := 0;
1063 l_old_iltype_0332_unit NUMBER := 0;
1064
1065 l_chk_asg_end_date DATE;
1066 /*Changes for Lunar Payroll */
1067 l_lnr_payroll_period Varchar2(3);
1068
1069
1070 e_too_many_hours EXCEPTION;
1071 e_no_emp_dflts EXCEPTION;
1072 error_message BOOLEAN;
1073
1074 -- nprasath Added for Multiple Records
1075 l_old_job_occ_mkode VARCHAR2(40);
1076 l_old_job_status_mkode VARCHAR2(40);
1077 l_old_sal_basis_mkode VARCHAR2(40);
1078 l_old_time_off_lieu VARCHAR2(40);
1079 -- For bug 9192911
1080 l_old_emp_grp VARCHAR2(40);
1081 l_old_cond_of_emp VARCHAR2(40);
1082
1083 --9865127 START
1084 l_first_asg_start_date VARCHAR2(40);
1085 l_job_occ_mkode_date VARCHAR2(40);
1086 l_job_status_mkode_date VARCHAR2(40);
1087 l_sal_basis_mkode_date VARCHAR2(40);
1088 l_mkode0200_date VARCHAR2(40);
1089 l_mkode0100_date VARCHAR2(40);
1090 l_time_off_lieu_date VARCHAR2(40);
1091 l_punit_date VARCHAR2(40);
1092
1093 l_prev_job_occ_mkode_date VARCHAR2(40);
1094 l_prev_job_status_mkode_date VARCHAR2(40);
1095 l_prev_sal_basis_mkode_date VARCHAR2(40);
1096 l_prev_mkode0200_date VARCHAR2(40);
1097 l_prev_mkode0100_date VARCHAR2(40);
1098 l_prev_time_off_lieu_date VARCHAR2(40);
1099 l_prev_punit_date VARCHAR2(40);
1100 --9865127 END
1101
1102 --9865127
1103 l_prev_job_occ_mkode VARCHAR2(40);
1104 l_prev_job_status_mkode VARCHAR2(40);
1105 l_prev_sal_basis_mkode VARCHAR2(40);
1106 l_prev_time_off_lieu VARCHAR2(40);
1107 l_prev_loc_id VARCHAR2(40);
1108 l_prev_emp_grp VARCHAR2(40);
1109 l_prev_cond_of_emp VARCHAR2(40);
1110
1111 --9865127
1112
1113 l_bal_todate DATE;
1114 l_hourly_salaried per_all_assignments_f.hourly_salaried_code%TYPE ;
1115 l_loc_id NUMBER;
1116 l_hr_org_id NUMBER;
1117 l_old_loc_id NUMBER;
1118 l_pd_date_to DATE; --8848543
1119
1120 --8848543
1121 /* Cursor to fetch the Period dates*/
1122 Cursor csr_pd_dts (p_end_date DATE, p_payroll_id NUMBER)
1123 IS
1124 select *
1125 from
1126 per_time_periods
1127 where
1128 payroll_id = p_payroll_id
1129 and p_end_date between START_DATE AND END_DATE;
1130
1131 l_rec_pd_dts csr_pd_dts%rowtype;
1132
1133 --8848543
1134 --
1135 BEGIN
1136 hr_utility.trace('Inside the Statistics Report');
1137
1138 IF g_debug THEN
1139 hr_utility.set_location(' Entering Procedure ASSIGNMENT_ACTION_CODE',10);
1140 END IF;
1141
1142 SELECT PAY_DK_STATSR_PKG.GET_PARAMETER(legislative_parameters,'SPAN_RPT')
1143 ,fnd_date.canonical_to_date(PAY_DK_STATSR_PKG.GET_PARAMETER(legislative_parameters,'REPORT_END_DATE')) --8848543
1144 INTO l_span, l_pd_date_to --8848543
1145 FROM pay_payroll_actions
1146 WHERE payroll_action_id = p_payroll_action_id;
1147 --8848543
1148 IF l_span <> 'P' THEN
1149 l_pd_date_to := NULL;
1150 END IF;
1151 --8848543
1152
1153 -- Fetch Header and Sub-header details
1154 FOR rec_sub_hdr_info IN csr_get_sub_hdr_info(p_payroll_action_id)
1155 LOOP
1156
1157 l_le_id := rec_sub_hdr_info.tax_unit_id;
1158 l_le_name := rec_sub_hdr_info.le_name;
1159 l_payroll_id := rec_sub_hdr_info.payroll_id;
1160 l_punit := rec_sub_hdr_info.le_punit; -- default if not present at hr_org/location
1161
1162 OPEN csr_get_hdr_info(p_payroll_action_id);
1163 FETCH csr_get_hdr_info INTO rec_hdr_info;
1164 CLOSE csr_get_hdr_info;
1165
1166 l_effective_date := rec_hdr_info.effective_date;
1167 --l_date_from := to_date(rec_hdr_info.from_date);
1168 --l_date_to := to_date(rec_hdr_info.to_date);
1169 l_bg_id := rec_hdr_info.business_group_id;
1170
1171 --Fixed for gscc error
1172 --8848543
1173 IF l_span <> 'P' THEN
1174 l_date_from := rec_hdr_info.from_date;
1175 l_date_to := rec_hdr_info.to_date;
1176 ELSIF l_span = 'P' THEN
1177 l_date_from := NULL;
1178 l_date_to := NULL;
1179 END IF;
1180 --8848543
1181
1182 -- Fetch Assignment's details
1183
1184 FOR csr_rec IN csr_assignments( p_payroll_action_id
1185 ,l_payroll_id
1186 ,p_start_person
1187 ,p_end_person
1188 ,l_date_from
1189 ,l_date_to
1190 ,l_le_id
1191 ,l_effective_date
1192 ,l_pd_date_to) --8848543
1193 LOOP
1194
1195 --8848543
1196 IF l_span = 'P' THEN
1197 OPEN csr_pd_dts (l_pd_date_to, csr_rec.payroll_id);
1198 FETCH csr_pd_dts INTO l_rec_pd_dts;
1199 CLOSE csr_pd_dts;
1200
1201 l_date_from := l_rec_pd_dts.start_date;
1202 l_date_to := l_rec_pd_dts.end_date;
1203 END IF;
1204 --8848543
1205
1206
1207 -- For Bug 9002015 BEGIN
1208 -- Check if assignment Start date is greater than Report Start Date
1209 -- Archive the greatest of Report start date or the assignment start date
1210
1211 l_chk_asg_start_date := csr_rec.ASG_START_DATE1; --9865127
1212
1213 IF l_chk_asg_start_date < l_date_from
1214 THEN
1215 l_start_date_from := to_char(l_date_from,'YYYYMMDD');
1216 ELSE
1217 l_start_date_from := to_char(csr_rec.ASG_START_DATE1,'YYYYMMDD'); --9865127
1218 END IF;
1219 -- For Bug 9002015 END
1220
1221
1222 -- Bug 5003744 - If the assignment end date is <= report end date then use assignment end date
1223 -- else use report end date while fetching balance values.
1224 l_loc_id := csr_rec.loc_id;
1225 l_hr_org_id := csr_rec.hr_org_id;
1226
1227 l_hourly_salaried := csr_rec.hourly_salaried_code;
1228 IF l_hourly_salaried IS NULL THEN
1229 IF csr_rec.payroll_period = 1 THEN
1230 l_hourly_salaried := 'S';
1231 ELSE
1232 l_hourly_salaried := 'H';
1233 END IF ;
1234 END IF ;
1235
1236 l_chk_asg_end_date := csr_rec.asg_end_date;
1237
1238 IF csr_rec.asg_end_date <> hr_general.end_of_time Then
1239 --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
1240
1241 --9865127 START
1242 FOR I IN csr_asg_end_other(csr_rec.asg_id,l_date_from,l_date_to,csr_rec.asg_end_date)
1243 LOOP
1244 IF I.SEGMENT14 = NVL(csr_rec.job_occ_mkode,'XXX') AND I.SEGMENT15 = NVL(csr_rec.job_status_mkode,'XXX')
1245 AND I.SEGMENT16 = NVL(csr_rec.sal_basis_mkode,'XXX') AND I.SEGMENT17 = NVL(csr_rec.time_off_lieu,'XXX')
1246 AND I.segment3 = NVL(csr_rec.cond_of_emp,'XXX') AND I.segment4 = NVL(csr_rec.emp_grp,'XXX') -- Bug 9266075
1247 AND I.location_id = NVL(csr_rec.loc_id,-999)
1248 THEN
1249 l_chk_asg_end_date := I.ASG_END_DATE;
1250 ELSE
1251 EXIT;
1252 END IF;
1253 END LOOP; --9865127 END;
1254
1255 open csr_asg_end(csr_rec.asg_id,
1256 l_date_from,
1257 l_date_to,
1258 csr_rec.job_occ_mkode,
1259 csr_rec.job_status_mkode,
1260 csr_rec.sal_basis_mkode,
1261 csr_rec.time_off_lieu,
1262 csr_rec.asg_end_date,
1263 csr_rec.loc_id,
1264 csr_rec.cond_of_emp, -- For Bug 9266075
1265 csr_rec.emp_grp
1266 );
1267
1268 Fetch csr_asg_end into csr_asg_end_check;
1269
1270 IF csr_asg_end%NOTFOUND THEN
1271 l_chk_asg_end_date := csr_rec.asg_end_date; --hr_general.end_of_time; --9822284
1272 End if;
1273 close csr_asg_end;
1274
1275 End If;
1276
1277 IF l_chk_asg_end_date <= l_date_to THEN
1278 l_bal_todate := l_chk_asg_end_date;
1279 ELSE
1280 l_bal_todate := l_date_to;
1281 END IF;
1282
1283 /*Check for Change of Assignment ID to Create New Assignment Action ID
1284 and for Archiving the data Bug Fix-5003220*/
1285 -- nprasath added for Multiple Records
1286 IF (csr_rec.asg_id <> l_asgid) THEN
1287
1288 hr_utility.trace(' ***** Intializing the old variables **** ');
1289
1290 l_old_iltype_0010_bal := 0;
1291 l_old_iltype_0011_bal := 0;
1292 l_old_iltype_0013_bal := 0;
1293 l_old_iltype_0015_bal := 0;
1294 -- l_old_iltype_0021_bal := 0;
1295 l_old_iltype_0121_bal := 0;
1296 l_old_iltype_0122_bal := 0;
1297 l_old_iltype_0022_bal := 0;
1298 l_old_iltype_0023_bal := 0;
1299 l_old_iltype_0024_bal := 0;
1300 l_old_iltype_0025_bal := 0;
1301 l_old_iltype_0026_bal := 0;
1302 l_old_iltype_0027_bal := 0;
1303 l_old_iltype_0029_bal := 0;
1304 -- l_old_iltype_0032_bal := 0;
1305 l_old_iltype_0034_bal := 0;
1306 l_old_iltype_0035_bal := 0;
1307 l_old_iltype_0036_bal := 0;
1308 l_old_iltype_0037_bal := 0;
1309 l_old_iltype_0091_bal := 0;
1310 l_old_iltype_0210_bal := 0;
1311 l_old_iltype_0132_bal := 0;
1312 l_old_iltype_0232_bal := 0;
1313 l_old_iltype_0332_bal := 0;
1314
1315 l_s_old_iltype_0010_unit := 0;
1316 l_h_old_iltype_0010_unit := 0;
1317 l_old_iltype_0011_unit := 0;
1318 l_old_iltype_0013_unit := 0;
1319 l_old_iltype_0015_unit := 0;
1320 -- l_old_iltype_0021_unit := 0;
1321 l_old_iltype_0121_unit := 0;
1322 l_old_iltype_0122_unit := 0;
1323 l_old_iltype_0022_unit := 0;
1324 l_s_old_iltype_0023_unit := 0;
1325 l_h_old_iltype_0023_unit := 0;
1326 l_old_iltype_0024_unit := 0;
1327 l_old_iltype_0025_unit := 0;
1328 l_old_iltype_0026_unit := 0;
1329 l_old_iltype_0027_unit := 0;
1330 l_old_iltype_0029_unit := 0;
1331 -- l_old_iltype_0032_unit := 0;
1332 l_old_iltype_0034_unit := 0;
1333 l_old_iltype_0035_unit := 0;
1334 l_old_iltype_0036_unit := 0;
1335 l_old_iltype_0037_unit := 0;
1336 l_old_iltype_0091_unit := 0;
1337 l_old_iltype_0210_unit := 0;
1338 l_old_iltype_0132_unit := 0;
1339 l_old_iltype_0232_unit := 0;
1340 l_old_iltype_0332_unit := 0;
1341 l_old_iltype_0023_hr_rate := 0;
1342 End if;
1343
1344 -- nprasath added for Multiple Records
1345
1346
1347 IF (csr_rec.asg_id <> l_asgid)
1348 or (csr_rec.asg_id = l_asgid
1349 and ( csr_rec.job_occ_mkode <> l_old_job_occ_mkode
1350 or csr_rec.job_status_mkode <> l_old_job_status_mkode
1351 or csr_rec.sal_basis_mkode <> l_old_sal_basis_mkode
1352 or csr_rec.time_off_lieu <> l_old_time_off_lieu
1353 -- For Bug 9192911
1354 or csr_rec.emp_grp <> l_old_emp_grp
1355 or csr_rec.cond_of_emp <> l_old_cond_of_emp
1356 or csr_rec.loc_id <> l_old_loc_id)
1357 ) THEN
1358
1359
1360 BEGIN
1361 SELECT pay_assignment_actions_s.NEXTVAL INTO l_actid FROM dual;
1362 EXCEPTION
1363 WHEN OTHERS THEN
1364 NULL ;
1365 END ;
1366 -- Create the archive assignment action
1367 hr_nonrun_asact.insact(l_actid ,csr_rec.asg_id ,p_payroll_action_id ,p_chunk,null);
1368
1369 OPEN csr_get_le_emp_dflts(l_le_id,l_date_to,l_bg_id) ;
1370 FETCH csr_get_le_emp_dflts INTO rec_get_le_emp_dflts;
1371 IF csr_get_le_emp_dflts%NOTFOUND THEN
1372 /* For bug fix 4997994 */
1373 /* Added check to check if corresponding assignment level details are present */
1374 --RAISE e_no_emp_dflts;
1375 IF(csr_rec.cond_of_emp IS NULL OR csr_rec.emp_grp IS NULL OR csr_rec.normal_hours IS NULL
1376 OR csr_rec.freq IS NULL) THEN
1377 RAISE e_no_emp_dflts;
1378 END IF;
1379 END IF;
1380 CLOSE csr_get_le_emp_dflts;
1381
1382 OPEN csr_get_hol_entit(l_le_id,l_date_to,l_bg_id) ;
1383 FETCH csr_get_hol_entit INTO rec_get_hol_entit;
1384 CLOSE csr_get_hol_entit;
1385
1386 IF l_loc_id IS NOT NULL THEN
1387 OPEN csr_location_info (l_loc_id);
1388 FETCH csr_location_info INTO rg_csr_location_info;
1389 IF csr_location_info%FOUND THEN
1390 l_loc_punit := rg_csr_location_info.lei_information1;
1391 END IF;
1392 CLOSE csr_location_info;
1393 END IF;
1394 IF l_loc_id IS NULL OR l_loc_punit IS NULL THEN
1395 OPEN csr_get_hr_org_info (l_hr_org_id);
1396 FETCH csr_get_hr_org_info INTO rg_csr_get_hr_org_info;
1397 IF csr_get_hr_org_info%FOUND THEN
1398 l_punit := nvl(rg_csr_get_hr_org_info.org_information6, l_punit);
1399 END IF;
1400 CLOSE csr_get_hr_org_info;
1401 ELSE
1402 l_punit := l_loc_punit;
1403 END IF;
1404
1405 /************** Access the values for the IPTYPE values **************/
1406 /* Logic for selecting mkode0100 */
1407
1408 IF( nvl(csr_rec.cond_of_emp, rec_get_le_emp_dflts.cond_of_emp) IN('3','4')) THEN
1409 l_mkode0100 := '2';
1410 ELSE
1411 l_mkode0100 := '1';
1412 END IF;
1413 /* Logic for selecting mkode0200 */
1414 IF( nvl(csr_rec.emp_grp, rec_get_le_emp_dflts.emp_grp) = '1') THEN
1415 l_mkode0200 := '1';
1416 ELSIF ( nvl(csr_rec.emp_grp, rec_get_le_emp_dflts.emp_grp) = '2') THEN
1417 l_mkode0200 := '2';
1418 ELSE
1419 l_mkode0200 := '3';
1420 END IF;
1421
1422 /* Logic for selecting mkode0600 */
1423 /* Bug 5030983 Fixes - Start */
1424 /* For salary record mkode0600,if salary basis not in 81,82,83,84 and payroll period is weekly
1425 and biweekly then get working hours balance and divide by 13, 26 or 52 based on report span. */
1426 IF (l_span ='Q') THEN
1427 l_dimension := '_ASG_LE_QTD';
1428 ELSIF (l_span ='HY') THEN
1429 l_dimension := '_ASG_LE_HYTD';
1430 ELSIF (l_span ='Y') THEN
1431 l_dimension := '_ASG_LE_YTD';
1432 --8848543
1433 ELSIF (l_span ='P') THEN
1434 l_dimension := '_ASG_PTD';
1435 --8848543
1436 END IF;
1437
1438 pay_balance_pkg.set_context('TAX_UNIT_ID',l_le_id);
1439
1440 l_asg_id :=csr_rec.asg_id;
1441 IF csr_rec.SAL_BASIS_MKODE IN ('81','82','83','84') THEN
1442 /*Changes for Lunar Payroll */
1443 IF csr_rec.payroll_period IN ('1', '2', '3','4') THEN
1444 l_day_max_hrs := 24;
1445 l_hours_rate := nvl(csr_rec.normal_hours,rec_get_le_emp_dflts.work_hours) ;
1446 l_freq := nvl(csr_rec.freq,rec_get_le_emp_dflts.freq);
1447 IF(l_freq = 'D') THEN
1448 IF(l_hours_rate > l_day_max_hrs) THEN
1449 RAISE e_too_many_hours;
1450 ELSE
1451 l_mul_factor :=5;
1452 END IF;
1453 ELSIF (l_freq = 'W') THEN
1454 IF(l_hours_rate > l_day_max_hrs*7) THEN
1455 RAISE e_too_many_hours;
1456 ELSE
1457 l_mul_factor :=1;
1458 END IF;
1459 ELSIF (l_freq = 'M') THEN
1460 IF(l_hours_rate > l_day_max_hrs*31) THEN
1461 RAISE e_too_many_hours;
1462 ELSE
1463 --l_mul_factor := 5/22; --9888286
1464 l_mul_factor := 12/52; --9888286
1465 END IF;
1466 ELSIF (l_freq = 'Y') THEN
1467 IF(l_hours_rate > l_day_max_hrs*366) THEN
1468 RAISE e_too_many_hours;
1469 --END IF; --9888286
1470 ELSE
1471 l_mul_factor :=5/260;
1472 END IF; --9888286
1473 END IF;
1474
1475 l_mkode0600 := ROUND(l_hours_rate * l_mul_factor,2);
1476 END IF;
1477
1478 ELSE -- MKODE not in 81,82,83,84
1479 /*IF csr_rec.payroll_period = '1' THEN*/
1480 IF l_hourly_salaried = 'S' THEN
1481 l_day_max_hrs := 24;
1482 l_hours_rate := nvl(csr_rec.normal_hours,rec_get_le_emp_dflts.work_hours) ;
1483 l_freq := nvl(csr_rec.freq,rec_get_le_emp_dflts.freq);
1484 IF(l_freq = 'D') THEN
1485 IF(l_hours_rate > l_day_max_hrs) THEN
1486 RAISE e_too_many_hours;
1487 ELSE
1488 l_mul_factor :=5;
1489 END IF;
1490 ELSIF (l_freq = 'W') THEN
1491 IF(l_hours_rate > l_day_max_hrs*7) THEN
1492 RAISE e_too_many_hours;
1493 ELSE
1494 l_mul_factor :=1;
1495 END IF;
1496 ELSIF (l_freq = 'M') THEN
1497 IF(l_hours_rate > l_day_max_hrs*31) THEN
1498 RAISE e_too_many_hours;
1499 ELSE
1500 --l_mul_factor := 5/22; --9888286
1501 l_mul_factor := 12/52; --9888286
1502 END IF;
1503 ELSIF (l_freq = 'Y') THEN
1504 IF(l_hours_rate > l_day_max_hrs*366) THEN
1505 RAISE e_too_many_hours;
1506 -- END IF; --9888286
1507 ELSE
1508 l_mul_factor :=5/260;
1509 END IF; --9888286
1510 END IF;
1511
1512 l_mkode0600 := ROUND(l_hours_rate * l_mul_factor,2);
1513
1514 /*Changes for Lunar Payroll */
1515
1516 /*ELSIF csr_rec.payroll_period IN ('2','3','4') THEN*/
1517 ELSIF l_hourly_salaried = 'H' THEN
1518 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);
1519 l_old_mkode0600 := l_mkode0600 + l_old_mkode0600;
1520
1521 IF l_span = 'Q' THEN
1522 l_mkode0600 := l_mkode0600 / 13;
1523 ELSIF l_span = 'HY' THEN
1524 l_mkode0600 := l_mkode0600 / 26;
1525 ELSIF l_span = 'Y' THEN
1526 l_mkode0600 := l_mkode0600 / 52;
1527 --8848543
1528 ELSIF l_span = 'P' THEN
1529 l_mkode0600 := l_mkode0600 / 1;
1530 --8848543
1531 END IF;
1532 END IF;
1533
1534 END IF; /* Bug 5030983 Fixes - End */
1535
1536 /* Logic for selecting mkode0610 */
1537 IF( nvl(csr_rec.default_work_patt,rec_get_hol_entit.default_work_patt) = '5DAY') THEN
1538 l_mkode0610 := to_char(PAY_DK_STATSR_PKG.GET_GLOBAL_VALUE('DK_5DAY_WEEK_HOLIDAY_ENTITLEMENT', l_date_to));
1539 ELSIF ( nvl(csr_rec.default_work_patt,rec_get_hol_entit.default_work_patt) = '6DAY') THEN
1540 l_mkode0610 := to_char(PAY_DK_STATSR_PKG.GET_GLOBAL_VALUE('DK_6DAY_WEEK_HOLIDAY_ENTITLEMENT', l_date_to));
1541 /* Added new condition for bug fix 5003621 */
1542 ELSE
1543 l_mkode0610 := to_char(PAY_DK_STATSR_PKG.GET_GLOBAL_VALUE('DK_5DAY_WEEK_HOLIDAY_ENTITLEMENT', l_date_to));
1544 END IF;
1545
1546 /* Logic for selecting l_mkode0620 */
1547 /*Changes for Lunar Payroll */
1548 /*IF(csr_rec.payroll_period = '2' OR csr_rec.payroll_period = '3' OR csr_rec.payroll_period = '4') THEN*/
1549 IF l_hourly_salaried = 'H' THEN
1550 l_mkode0620 := to_char(nvl(nvl(csr_rec.hourly_accr_rate,rec_get_hol_entit.hourly_accr_rate)
1551 ,to_char(PAY_DK_STATSR_PKG.GET_GLOBAL_VALUE('DK_HOLIDAY_PAY_LEGSL_PERCENTAGE', l_date_to))));
1552 /*ELSIF(csr_rec.payroll_period = '1') THEN*/
1553 ELSIF l_hourly_salaried = 'S' THEN
1554 l_mkode0620 := to_char(nvl(nvl(csr_rec.sal_allow_rate,rec_get_hol_entit.sal_allow_rate)
1555 ,to_char(PAY_DK_STATSR_PKG.GET_GLOBAL_VALUE('DK_HOLIDAY_ALLOWANCE_LEGSL_PERCENTAGE', l_date_to))));
1556 END IF;
1557
1558 /************** Access the balance values for the ILTYPE balances **************/
1559
1560 /* Logic for fetching l_iltype_0010_bal */
1561 l_iltype_0010_bal := PAY_DK_STATSR_PKG.GET_DEFINED_BALANCE_VALUE(l_asg_id, 'Total Income',l_dimension ,l_bal_todate)
1562 - l_old_iltype_0010_bal; -- l_date_to);
1563 l_old_iltype_0010_bal := l_old_iltype_0010_bal + l_iltype_0010_bal;
1564 /* Bug 5030983 Fixes - Start */
1565 IF csr_rec.SAL_BASIS_MKODE IN ('81','82','83','84') THEN
1566 l_iltype_0010_unit := 0;
1567 --ELSE --9888286
1568 ELSIF csr_rec.SAL_BASIS_MKODE IN ('01','02') THEN --9888286
1569 /* Take the calculated values from mkode600 and bring to Monthly Payroll Frequency */
1570 /*IF(csr_rec.payroll_period = '1') THEN*/
1571 IF l_hourly_salaried = 'S' THEN
1572 /* Changed this for bug fix 5034129 */
1573 /*l_iltype_0010_unit := ROUND(l_hours_rate * l_mul_factor * 22/5,2);*/
1574 l_iltype_0010_unit := PAY_DK_STATSR_PKG.GET_DEFINED_BALANCE_VALUE(l_asg_id, 'Total ATP Hours',l_dimension ,l_bal_todate)
1575 - PAY_DK_STATSR_PKG.GET_DEFINED_BALANCE_VALUE(l_asg_id, 'Worked Hours',l_dimension ,l_bal_todate) - l_s_old_iltype_0010_unit;
1576 l_s_old_iltype_0010_unit := l_iltype_0010_unit + l_s_old_iltype_0010_unit;
1577 /*Changes for Lunar Payroll */
1578 /*ELSIF(csr_rec.payroll_period = '2' OR csr_rec.payroll_period = '3' OR csr_rec.payroll_period = '4') THEN*/
1579 ELSIF l_hourly_salaried = 'H' THEN
1580 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);
1581 l_h_old_iltype_0010_unit := l_h_old_iltype_0010_unit + l_iltype_0010_unit;
1582 END IF;
1583 END IF; /* Bug 5030983 Fixes - End */
1584 /* Logic for fetching l_iltype_0011_bal */
1585 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);
1586 l_old_iltype_0011_bal := l_iltype_0011_bal + l_old_iltype_0011_bal;
1587 --l_iltype_0011_unit := 1; --9888286
1588 l_iltype_0011_unit := 0; --9888286
1589
1590 /* Logic for fetching l_iltype_0013_bal */
1591 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);
1592 l_old_iltype_0013_bal := l_old_iltype_0013_bal + l_iltype_0013_bal;
1593 /* Added for bug 5050964*/
1594 l_iltype_0013_unit := l_iltype_0010_unit;
1595
1596 /* Logic for fetching l_iltype_0015_bal */
1597 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);
1598 l_old_iltype_0015_bal := l_old_iltype_0015_bal + l_iltype_0015_bal;
1599 -- l_iltype_0015_unit := 1; --9888286
1600 l_iltype_0015_unit := 0; --9888286
1601
1602 /* Logic for fetching l_iltype_0021_bal */
1603 /*
1604 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)
1605 +PAY_DK_STATSR_PKG.GET_DEFINED_BALANCE_VALUE(l_asg_id, 'Employer ATP Deductions',l_dimension ,l_bal_todate) -- l_date_to)
1606 +PAY_DK_STATSR_PKG.GET_DEFINED_BALANCE_VALUE(l_asg_id, 'Employee Pension Deductions',l_dimension ,l_bal_todate) -- l_date_to)
1607 +PAY_DK_STATSR_PKG.GET_DEFINED_BALANCE_VALUE(l_asg_id, 'Employer Pension Deductions',l_dimension ,l_bal_todate)
1608 - l_old_iltype_0021_bal; -- l_date_to);
1609 l_old_iltype_0021_bal := l_iltype_0021_bal + l_old_iltype_0021_bal;
1610 l_iltype_0021_unit := 1;*/
1611 /* IL Type 0121 */
1612 l_iltype_0121_bal := PAY_DK_STATSR_PKG.GET_DEFINED_BALANCE_VALUE(l_asg_id, 'Employee ATP Deductions',l_dimension ,l_bal_todate) -- l_date_to)
1613 +PAY_DK_STATSR_PKG.GET_DEFINED_BALANCE_VALUE(l_asg_id, 'Employee Pension Deductions',l_dimension ,l_bal_todate) -- l_date_to)
1614 - l_old_iltype_0121_bal; -- l_date_to);
1615 l_old_iltype_0121_bal := l_iltype_0121_bal + l_old_iltype_0121_bal;
1616 --l_iltype_0121_unit := 1; --9888286
1617 l_iltype_0121_unit := 0; --9888286
1618 /* IL Type 0122 */
1619 l_iltype_0122_bal := PAY_DK_STATSR_PKG.GET_DEFINED_BALANCE_VALUE(l_asg_id, 'Employer ATP Deductions',l_dimension ,l_bal_todate) -- l_date_to)
1620 +PAY_DK_STATSR_PKG.GET_DEFINED_BALANCE_VALUE(l_asg_id, 'Employer Pension Deductions',l_dimension ,l_bal_todate)
1621 - l_old_iltype_0122_bal; -- l_date_to);
1622 l_old_iltype_0122_bal := l_iltype_0122_bal + l_old_iltype_0122_bal;
1623 --l_iltype_0122_unit := 1; --9888286
1624 l_iltype_0122_unit := 0; --9888286
1625
1626 /* Added condition for bug fix 4998238 */
1627 /*Changes for Lunar Payroll */
1628 /*IF(csr_rec.payroll_period = '2' OR csr_rec.payroll_period = '3' OR csr_rec.payroll_period = '4') THEN*/
1629 /* IF l_hourly_salaried = 'H' THEN
1630 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);
1631 l_old_iltype_0021_unit := l_iltype_0021_unit + l_old_iltype_0021_unit;
1632 END IF;*/
1633
1634 /* Logic for fetching l_iltype_0022_bal */
1635 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);
1636 l_old_iltype_0022_bal := l_iltype_0022_bal + l_old_iltype_0022_bal;
1637 /* After FS changes, now for both salaried and non-salaried, to report Total G_Dage_Days_ASG_XXX as units
1638 , earlier was Total G_Dage Hours for non-salaried */
1639 /* Commenting code below and re-writing to achieve this */
1640 /*IF(csr_rec.payroll_period = '1') THEN
1641 l_iltype_0022_unit := PAY_DK_STATSR_PKG.GET_DEFINED_BALANCE_VALUE(l_asg_id, 'Total G_Dage Days',l_dimension ,l_date_to);
1642 ELSIF(csr_rec.payroll_period = '2' OR csr_rec.payroll_period = '3') THEN
1643 l_iltype_0022_unit := PAY_DK_STATSR_PKG.GET_DEFINED_BALANCE_VALUE(l_asg_id, 'Total G_Dage Hours',l_dimension ,l_date_to);
1644 END IF;*/
1645 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);
1646 l_old_iltype_0022_unit := l_iltype_0022_unit + l_old_iltype_0022_unit;
1647 /*Bug 5026906 fix- Changing the '<' operator to '=' in order to display
1648 the negative values*/
1649 -- IF(l_iltype_0022_unit <0) THEN
1650 IF(l_iltype_0022_unit = 0) THEN
1651 --l_iltype_0022_unit := 1; --9888286
1652 l_iltype_0022_unit := 0; --9888286
1653 END IF;
1654
1655 /* Logic for fetching l_iltype_0023_bal */
1656 /*IF(csr_rec.payroll_period = '1' ) THEN*/
1657 IF l_hourly_salaried = 'S' THEN
1658 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);
1659 l_s_old_iltype_0023_unit := l_s_old_iltype_0023_unit + l_iltype_0023_unit;
1660
1661 /* Calculate Hourly Rate Modified with if clause to avoid zero divide error */
1662 if l_iltype_0010_unit <> 0 then
1663 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);
1664 l_old_iltype_0023_hr_rate := PAY_DK_STATSR_PKG.GET_BALANCE_CATEGORY_VALUE(l_asg_id, 'Salary Reporting',l_dimension ,l_bal_todate);
1665 end if;
1666
1667 l_iltype_0023_bal := l_iltype_0023_hr_rate*l_iltype_0023_unit;
1668 /*Changes for Lunar Payroll */
1669 /*ELSIF(csr_rec.payroll_period = '2' OR csr_rec.payroll_period = '3' OR csr_rec.payroll_period = '4') THEN*/
1670 ELSIF l_hourly_salaried = 'H' THEN
1671 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);
1672 l_old_iltype_0023_bal := l_iltype_0023_bal + l_old_iltype_0023_bal;
1673 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);
1674 l_h_old_iltype_0023_unit := l_iltype_0023_unit + l_h_old_iltype_0023_unit;
1675 END IF;
1676 /* Logic for fetching l_iltype_0024_bal */
1677 /* After FS changes, it is now valid only for non-salaried ppl with balance Holiday_Accrual_Amount_ASG_XXX
1678 , whereas earlier it was valid only for salaried ppl with 'Holiday Allowance Paid' as balance
1679 and Holiday Absence Days as units.*/
1680 /* Commenting code below and re-writing to achieve this */
1681 /*
1682 IF(csr_rec.payroll_period = '1' ) THEN
1683 l_iltype_0024_bal := PAY_DK_STATSR_PKG.GET_DEFINED_BALANCE_VALUE(l_asg_id, 'Holiday Allowance Paid',l_dimension ,l_date_to);
1684 l_iltype_0024_unit := PAY_DK_STATSR_PKG.GET_DEFINED_BALANCE_VALUE(l_asg_id, 'Holiday Absence Days',l_dimension ,l_date_to);
1685 */
1686 /*Changes for Lunar Payroll */
1687 /*IF(csr_rec.payroll_period = '2' OR csr_rec.payroll_period = '3' OR csr_rec.payroll_period = '4') THEN*/
1688 IF l_hourly_salaried = 'H' THEN
1689 /* Changed for bug 5012411*/
1690 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);
1691 l_old_iltype_0024_bal := l_iltype_0024_bal + l_old_iltype_0024_bal;
1692 --l_iltype_0024_unit := 1; --9888286
1693 l_iltype_0024_unit := 0; --9888286
1694 END IF;
1695
1696 /* Logic for fetching l_iltype_0025_bal */
1697 /* After FS changes, , it is now valid only for salaried ppl with balance with balance Holiday_Accrual_Amount_ASG_XXX,
1698 whereas earlier it was valid only for non-salaried ppl with balance Holiday Bank Pay
1699 and units Holiday Absence Hours */
1700 /* Commenting code below and re-writing to achieve this */
1701 /*
1702 IF(csr_rec.payroll_period = '2' OR csr_rec.payroll_period = '3') THEN
1703 l_iltype_0025_bal := PAY_DK_STATSR_PKG.GET_DEFINED_BALANCE_VALUE(l_asg_id, 'Holiday Bank Pay',l_dimension ,l_date_to);
1704 l_iltype_0025_unit := PAY_DK_STATSR_PKG.GET_DEFINED_BALANCE_VALUE(l_asg_id, 'Holiday Absence Hours',l_dimension ,l_date_to);
1705 */
1706 /*IF(csr_rec.payroll_period = '1' ) THEN*/
1707 IF l_hourly_salaried = 'S' THEN
1708 --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); --9888286
1709 l_iltype_0025_bal := PAY_DK_STATSR_PKG.GET_DEFINED_BALANCE_VALUE(l_asg_id, 'Holiday Allowance Paid',l_dimension ,l_bal_todate) --9888286
1710 + PAY_DK_STATSR_PKG.GET_DEFINED_BALANCE_VALUE(l_asg_id, 'Other Holiday Payments',l_dimension ,l_bal_todate) --9888286
1711 - l_old_iltype_0025_bal; -- l_date_to); --9888286
1712 l_old_iltype_0025_bal := l_old_iltype_0025_bal + l_iltype_0025_bal;
1713 --l_iltype_0025_unit := 1; --9888286
1714 l_iltype_0025_unit := 0; --9888286
1715 END IF;
1716
1717 /* Logic for fetching l_iltype_0026_bal */
1718 l_iltype_0026_bal := 0;
1719 /* For Bug 9072985 . Bug 9278107
1720 Currently 0026 field is been archived with the unpaid holiday days available rather than the unpaid holidays spent
1721 Changed the balance name, such that it reported the Unpaid holidays taken rather than those available */
1722 -- 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);
1723 -- l_iltype_0026_unit := PAY_DK_STATSR_PKG.GET_DEFINED_BALANCE_VALUE(l_asg_id, 'Holiday Absence Days',l_dimension ,l_bal_todate) - l_old_iltype_0026_unit; -- l_date_to);
1724 l_iltype_0026_unit := trunc(PAY_DK_STATSR_PKG.GET_DEFINED_BALANCE_VALUE(l_asg_id, 'Holiday Unpaid Days Taken',l_dimension ,l_bal_todate)) - l_old_iltype_0026_unit; -- l_date_to);
1725 l_old_iltype_0026_unit := l_iltype_0026_unit + l_old_iltype_0026_unit;
1726
1727 /* IL Type 0027 */
1728 l_iltype_0027_bal := PAY_DK_STATSR_PKG.GET_DEFINED_BALANCE_VALUE(l_asg_id, 'Other Absence Amount',l_dimension ,l_bal_todate) - l_old_iltype_0027_bal; -- l_date_to);
1729 l_old_iltype_0027_bal := l_old_iltype_0027_bal + l_iltype_0027_bal;
1730 l_iltype_0027_unit := PAY_DK_STATSR_PKG.GET_DEFINED_BALANCE_VALUE(l_asg_id, 'Other Absence Days',l_dimension ,l_bal_todate) - l_old_iltype_0027_unit;
1731 l_old_iltype_0027_unit := l_old_iltype_0027_unit + l_iltype_0027_unit;
1732
1733 /* IL Type 0029 */
1734 l_iltype_0029_bal := PAY_DK_STATSR_PKG.GET_DEFINED_BALANCE_VALUE(l_asg_id, 'Elective Scheme Amount',l_dimension ,l_bal_todate) - l_old_iltype_0029_bal; -- l_date_to);
1735 l_old_iltype_0029_bal := l_old_iltype_0029_bal + l_iltype_0029_bal;
1736 --l_iltype_0029_unit := 1; --9888286
1737 l_iltype_0029_unit := 0; --9888286
1738
1739 /* Logic for fetching l_iltype_0032_bal */
1740 /*
1741 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);
1742 l_old_iltype_0032_bal := l_old_iltype_0032_bal + l_iltype_0032_bal;
1743 l_iltype_0032_unit := 1;
1744 */
1745 /* Logic for fetching l_iltype_0132_bal */
1746 l_iltype_0132_bal := PAY_DK_STATSR_PKG.GET_DEFINED_BALANCE_VALUE(l_asg_id, 'Performance Irregular Payments',l_dimension ,l_bal_todate) - l_old_iltype_0132_bal; -- l_date_to);
1747 l_old_iltype_0132_bal := l_old_iltype_0132_bal + l_iltype_0132_bal;
1748 --l_iltype_0132_unit := 1; --9888286
1749 l_iltype_0132_unit := 0; --9888286
1750
1751 /* Logic for fetching l_iltype_0232_bal */
1752 l_iltype_0232_bal := PAY_DK_STATSR_PKG.GET_DEFINED_BALANCE_VALUE(l_asg_id, 'Compensation Irregular Payments',l_dimension ,l_bal_todate) - l_old_iltype_0232_bal; -- l_date_to);
1753 l_old_iltype_0232_bal := l_old_iltype_0232_bal + l_iltype_0232_bal;
1754 -- l_iltype_0232_unit := 1; --9888286
1755 l_iltype_0232_unit := 0; --9888286
1756
1757 /* Logic for fetching l_iltype_0332_bal */
1758 l_iltype_0332_bal := PAY_DK_STATSR_PKG.GET_DEFINED_BALANCE_VALUE(l_asg_id, 'Other Irregular Payments',l_dimension ,l_bal_todate) - l_old_iltype_0332_bal; -- l_date_to);
1759 l_old_iltype_0332_bal := l_old_iltype_0332_bal + l_iltype_0332_bal;
1760 --l_iltype_0332_unit := 1; --9888286
1761 l_iltype_0332_unit := 0; --9888286
1762
1763 /* Logic for fetching l_iltype_0034_bal */
1764 /* After FS changes, now applicable to Salaried Payrolls only now, earlier was for all*/
1765 /* Adding new condition, to achieve this */
1766 /*IF(csr_rec.payroll_period = '1') THEN*/
1767 IF l_hourly_salaried = 'S' THEN
1768 l_iltype_0034_bal := 0;
1769 /* For Bug 9072985
1770 IL0034 absence with payment (except holiday) should report in days and not in hours and Holidays should not be included.
1771 A seeded balance is now provided and user can feed the elements accordingly */
1772 -- 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);
1773 l_iltype_0034_unit := PAY_DK_STATSR_PKG.GET_DEFINED_BALANCE_VALUE(l_asg_id, 'Salaried Paid Absence Balance',l_dimension ,l_bal_todate) - l_old_iltype_0034_unit; -- l_date_to);
1774 l_old_iltype_0034_unit := l_iltype_0034_unit + l_old_iltype_0034_unit;
1775 END IF;
1776
1777 /* IL Type 0035 */
1778 l_iltype_0035_bal := PAY_DK_STATSR_PKG.GET_DEFINED_BALANCE_VALUE(l_asg_id, 'Nuisance Pay',l_dimension ,l_bal_todate) - l_old_iltype_0035_bal; -- l_date_to);
1779 l_old_iltype_0035_bal := l_old_iltype_0035_bal + l_iltype_0035_bal;
1780 -- l_iltype_0035_unit := 1; --9888286
1781 l_iltype_0035_unit := 0; --9888286
1782
1783 /* Logic for fetching l_iltype_0036_bal */
1784 /* After FS changes, now applicable to non-salaried Payrolls only now, earlier was for all*/
1785 /* Commenting code and putting everything into the first IF condition, to achieve this */
1786 /*l_iltype_0036_unit := PAY_DK_STATSR_PKG.GET_BALANCE_CATEGORY_VALUE(l_asg_id, 'Hourly Overtime Pay',l_dimension ,l_date_to);*/
1787 /*Changes for Lunar Payroll */
1788 /*IF(csr_rec.payroll_period = '2' OR csr_rec.payroll_period = '3' OR csr_rec.payroll_period = '4') THEN*/
1789 IF l_hourly_salaried = 'H' THEN
1790 /*l_iltype_0036_bal := 0;
1791 ELSIF(csr_rec.payroll_period = '1' ) THEN */
1792 /*Bug 5020527 fix - Assigning the 'Hourly Overtime Hours' balance value to l_iltype_0036_unit
1793 and 'Hourly Overtime Pay' balance value to l_iltype_0036_bal*/
1794 /*l_iltype_0036_bal := PAY_DK_STATSR_PKG.GET_BALANCE_CATEGORY_VALUE(l_asg_id, 'Hourly Overtime Hours',l_dimension ,l_date_to);
1795 l_iltype_0036_unit := PAY_DK_STATSR_PKG.GET_BALANCE_CATEGORY_VALUE(l_asg_id, 'Hourly Overtime Pay',l_dimension ,l_date_to);*/
1796
1797 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);
1798 l_old_iltype_0036_unit := l_iltype_0036_unit + l_old_iltype_0036_unit;
1799 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);
1800 l_old_iltype_0036_bal := l_old_iltype_0036_bal + l_iltype_0036_bal;
1801 /*Bug 5026906 fix- Changing the '<' operator to '=' in order to display
1802 the negative values*/
1803 -- IF(l_iltype_0036_unit <0) THEN
1804 IF(l_iltype_0036_unit = 0) THEN
1805 --l_iltype_0036_unit := 1; --9888286
1806 l_iltype_0036_unit := 0; --9888286
1807 END IF;
1808 END IF;
1809
1810 /* Logic for fetching l_iltype_0037_bal */
1811 /* After FS changes, now applicable to Salaried Payrolls only now, earlier was for all*/
1812 /* Adding new condition, to achieve this */
1813 /*IF(csr_rec.payroll_period = '1') THEN*/
1814 IF l_hourly_salaried = 'S' THEN
1815 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);
1816 l_old_iltype_0037_bal := l_old_iltype_0037_bal + l_iltype_0037_bal;
1817 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);
1818 l_old_iltype_0037_unit := l_old_iltype_0037_unit + l_iltype_0037_unit;
1819 /*Bug 5026906 fix- Changing the '<' operator to '=' in order to display
1820 the negative values*/
1821 --IF(l_iltype_0037_unit <0) THEN
1822 IF(l_iltype_0037_unit = 0) THEN
1823 --l_iltype_0037_unit := 1; --9888286
1824 l_iltype_0037_unit := 0; --9888286
1825 END IF;
1826 END IF;
1827
1828 /* IL Type 0091 */
1829 l_iltype_0091_bal := PAY_DK_STATSR_PKG.GET_DEFINED_BALANCE_VALUE(l_asg_id, 'Gross Deduction',l_dimension ,l_bal_todate) - l_old_iltype_0091_bal;
1830 l_old_iltype_0091_bal := l_old_iltype_0091_bal + l_iltype_0091_bal;
1831 --l_iltype_0091_unit := 1; --9888286
1832 l_iltype_0091_unit := 0; --9888286
1833
1834 /* IL Type 0210 */
1835 l_iltype_0210_bal := 0;
1836 l_iltype_0210_unit := PAY_DK_STATSR_PKG.GET_DEFINED_BALANCE_VALUE(l_asg_id, 'Paid Work Hours',l_dimension ,l_bal_todate) - l_old_iltype_0210_unit;
1837 l_old_iltype_0210_unit := l_old_iltype_0210_unit + l_iltype_0210_unit;
1838
1839 /* Added to fix issues due to varying numeric formats */
1840 /* Commented the below code as the below conversion fails if the application numeric format
1841 is set to ',' and handled the conversion while inserting the data in archive tables throug API*/
1842 /*l_mkode0600 := fnd_number.number_to_canonical(l_mkode0600 );
1843 l_mkode0610 := fnd_number.number_to_canonical(l_mkode0610);
1844 l_mkode0620 := fnd_number.number_to_canonical(l_mkode0620);
1845 l_iltype_0010_bal := fnd_number.number_to_canonical(l_iltype_0010_bal);
1846 l_iltype_0011_bal := fnd_number.number_to_canonical(l_iltype_0011_bal);
1847 l_iltype_0013_bal := fnd_number.number_to_canonical(l_iltype_0013_bal);
1848 l_iltype_0015_bal := fnd_number.number_to_canonical(l_iltype_0015_bal);
1849 --l_iltype_0021_bal := fnd_number.number_to_canonical(l_iltype_0021_bal);
1850 l_iltype_0121_bal := fnd_number.number_to_canonical(l_iltype_0121_bal);
1851 l_iltype_0122_bal := fnd_number.number_to_canonical(l_iltype_0122_bal);
1852 l_iltype_0027_bal := fnd_number.number_to_canonical(l_iltype_0027_bal);
1853 l_iltype_0022_bal := fnd_number.number_to_canonical(l_iltype_0022_bal);
1854 l_iltype_0023_bal := fnd_number.number_to_canonical(l_iltype_0023_bal);
1855 l_iltype_0024_bal := fnd_number.number_to_canonical(l_iltype_0024_bal);
1856 l_iltype_0025_bal := fnd_number.number_to_canonical(l_iltype_0025_bal);
1857 l_iltype_0026_bal := fnd_number.number_to_canonical(l_iltype_0026_bal);
1858 l_iltype_0029_bal := fnd_number.number_to_canonical(l_iltype_0029_bal);
1859 --l_iltype_0032_bal := fnd_number.number_to_canonical(l_iltype_0032_bal);
1860 l_iltype_0034_bal := fnd_number.number_to_canonical(l_iltype_0034_bal);
1861 l_iltype_0035_bal := fnd_number.number_to_canonical(l_iltype_0035_bal);
1862 l_iltype_0036_bal := fnd_number.number_to_canonical(l_iltype_0036_bal);
1863 l_iltype_0037_bal := fnd_number.number_to_canonical(l_iltype_0037_bal);
1864 l_iltype_0091_bal := fnd_number.number_to_canonical(l_iltype_0091_bal);
1865 l_iltype_0210_bal := fnd_number.number_to_canonical(l_iltype_0210_bal);
1866 l_iltype_0132_bal := fnd_number.number_to_canonical(l_iltype_0132_bal);
1867 l_iltype_0232_bal := fnd_number.number_to_canonical(l_iltype_0232_bal);
1868 l_iltype_0332_bal := fnd_number.number_to_canonical(l_iltype_0332_bal);
1869
1870 l_iltype_0010_unit := fnd_number.number_to_canonical(l_iltype_0010_unit);
1871 l_iltype_0011_unit := fnd_number.number_to_canonical(l_iltype_0011_unit);
1872 l_iltype_0013_unit := fnd_number.number_to_canonical(l_iltype_0013_unit);
1873 l_iltype_0015_unit := fnd_number.number_to_canonical(l_iltype_0015_unit);
1874 --l_iltype_0021_unit := fnd_number.number_to_canonical(l_iltype_0021_unit);
1875 l_iltype_0121_unit := fnd_number.number_to_canonical(l_iltype_0121_unit);
1876 l_iltype_0122_unit := fnd_number.number_to_canonical(l_iltype_0122_unit);
1877 l_iltype_0022_unit := fnd_number.number_to_canonical(l_iltype_0022_unit);
1878 l_iltype_0023_unit := fnd_number.number_to_canonical(l_iltype_0023_unit);
1879 l_iltype_0024_unit := fnd_number.number_to_canonical(l_iltype_0024_unit);
1880 l_iltype_0025_unit := fnd_number.number_to_canonical(l_iltype_0025_unit);
1881 l_iltype_0026_unit := fnd_number.number_to_canonical(l_iltype_0026_unit);
1882 l_iltype_0027_unit := fnd_number.number_to_canonical(l_iltype_0027_unit);
1883 l_iltype_0029_unit := fnd_number.number_to_canonical(l_iltype_0029_unit);
1884 --l_iltype_0032_unit := fnd_number.number_to_canonical(l_iltype_0032_unit);
1885 l_iltype_0034_unit := fnd_number.number_to_canonical(l_iltype_0034_unit);
1886 l_iltype_0035_unit := fnd_number.number_to_canonical(l_iltype_0035_unit);
1887 l_iltype_0036_unit := fnd_number.number_to_canonical(l_iltype_0036_unit);
1888 l_iltype_0037_unit := fnd_number.number_to_canonical(l_iltype_0037_unit);
1889 l_iltype_0091_unit := fnd_number.number_to_canonical(l_iltype_0091_unit);
1890 l_iltype_0210_unit := fnd_number.number_to_canonical(l_iltype_0210_unit);
1891 l_iltype_0132_unit := fnd_number.number_to_canonical(l_iltype_0132_unit);
1892 l_iltype_0232_unit := fnd_number.number_to_canonical(l_iltype_0232_unit);
1893 l_iltype_0332_unit := fnd_number.number_to_canonical(l_iltype_0332_unit); */
1894
1895 -- Creating Initial Archive Entries
1896 /*Changes for Lunar Payroll */
1897 If csr_rec.payroll_period = '4' then
1898 l_lnr_payroll_period := '1';
1899 Else
1900 l_lnr_payroll_period := csr_rec.payroll_period;
1901 End if;
1902
1903 pay_action_information_api.create_action_information
1904 ( p_action_information_id => l_action_info_id -- OUT parameter
1905 ,p_object_version_number => l_ovn -- OUT parameter
1906 ,p_action_context_id => l_actid -- Context id = assignment action id (of Archive)
1907 ,p_action_context_type => 'AAP' -- Context type
1908 ,p_effective_date => l_effective_date -- Date of running the archive
1909 ,p_assignment_id => csr_rec.asg_id -- Assignment ID
1910 ,p_action_information_category => 'EMEA REPORT INFORMATION' -- Information Category
1911 ,p_tax_unit_id => l_le_id -- Legal Employer ID
1912 ,p_jurisdiction_code => NULL -- Tax Municipality ID
1913 ,p_action_information1 => 'PYDKSTATSA' -- Con Program Short Name
1914 ,p_action_information2 => csr_rec.payroll_name -- Payroll Name
1915 ,p_action_information3 => p_payroll_action_id -- Payroll action id (of Archive)
1916 ,p_action_information4 => csr_rec.assignment_number -- Assignment Number
1917 ,p_action_information5 => csr_rec.cpr_no -- CPR Number of Employee
1918 ,p_action_information6 => 'PART1' -- Archive part 1 on context AAP
1919 ,p_action_information7 => l_mkode0100 -- MKODE of IPTYPE 0100
1920 ,p_action_information8 => l_mkode0200 -- MKODE of IPTYPE 0200
1921 ,p_action_information9 => csr_rec.job_occ_mkode -- MKODE of IPTYPE 0300
1922 ,p_action_information10 => csr_rec.job_status_mkode -- MKODE of IPTYPE 0400
1923 ,p_action_information11 => l_mkode0600 -- MKODE of IPTYPE 0600
1924 ,p_action_information12 => l_mkode0610 -- MKODE of IPTYPE 0610
1925 ,p_action_information13 => l_mkode0620 -- MKODE of IPTYPE 0620
1926 ,p_action_information14 => to_char(l_lnr_payroll_period) -- MKODE of IPTYPE 0700 /*Changes for Lunar Payroll */
1927 ,p_action_information15 => csr_rec.sal_basis_mkode -- MKODE of IPTYPE 0800
1928 ,p_action_information16 => fnd_number.number_to_canonical(l_iltype_0010_bal) -- Balance for ILTYPE 0010
1929 ,p_action_information17 => fnd_number.number_to_canonical(l_iltype_0011_bal) -- Balance for ILTYPE 0011
1930 ,p_action_information18 => fnd_number.number_to_canonical(l_iltype_0013_bal) -- Balance for ILTYPE 0013
1931 ,p_action_information19 => fnd_number.number_to_canonical(l_iltype_0015_bal) -- Balance for ILTYPE 0015
1932 ,p_action_information20 => NULL -- Balance for ILTYPE 0021
1933 ,p_action_information21 => fnd_number.number_to_canonical(l_iltype_0022_bal) -- Balance for ILTYPE 0022
1934 ,p_action_information22 => fnd_number.number_to_canonical(l_iltype_0023_bal) -- Balance for ILTYPE 0023
1935 ,p_action_information23 => fnd_number.number_to_canonical(l_iltype_0024_bal) -- Balance for ILTYPE 0024
1936 ,p_action_information24 => fnd_number.number_to_canonical(l_iltype_0025_bal) -- Balance for ILTYPE 0025
1937 ,p_action_information25 => fnd_number.number_to_canonical(l_iltype_0026_bal) -- Balance for ILTYPE 0026
1938 ,p_action_information26 => NULL -- Balance for ILTYPE 0032
1939 ,p_action_information27 => fnd_number.number_to_canonical(l_iltype_0034_bal) -- Balance for ILTYPE 0034
1940 ,p_action_information28 => fnd_number.number_to_canonical(l_iltype_0036_bal) -- Balance for ILTYPE 0036
1941 ,p_action_information29 => fnd_number.number_to_canonical(l_iltype_0037_bal) -- Balance for ILTYPE 0037
1942 ,p_action_information30 => l_hourly_salaried -- Hourly/Salaried
1943 );
1944
1945 --9865127 START
1946 l_asgid_prev := -999;
1947
1948 l_prev_job_occ_mkode_date := NULL;
1949 l_prev_job_status_mkode_date := NULL;
1950 l_prev_sal_basis_mkode_date := NULL;
1951 l_prev_mkode0200_date := NULL;
1952 l_prev_mkode0100_date := NULL;
1953 l_prev_time_off_lieu_date := NULL;
1954 l_prev_punit_date := NULL;
1955
1956 l_prev_job_occ_mkode := NULL;
1957 l_prev_job_status_mkode := NULL;
1958 l_prev_sal_basis_mkode := NULL;
1959 l_prev_time_off_lieu := NULL;
1960 l_prev_loc_id := NULL;
1961 l_prev_emp_grp := NULL;
1962 l_prev_cond_of_emp := NULL;
1963
1964 FOR csr_rec_all IN csr_assignments_all
1965 ( --l_payroll_id
1966 csr_rec.payroll_id
1967 ,csr_rec.person_id
1968 ,l_bal_todate
1969 ,l_le_id
1970 ,l_effective_date
1971 ,csr_rec.ASG_ID
1972 )
1973 LOOP
1974
1975 IF (csr_rec_all.asg_id <> l_asgid_prev) THEN
1976
1977 l_first_asg_start_date := csr_rec_all.ASG_START_DATE;
1978 l_job_occ_mkode_date := csr_rec_all.ASG_START_DATE;
1979 l_job_status_mkode_date := csr_rec_all.ASG_START_DATE;
1980 l_sal_basis_mkode_date := csr_rec_all.ASG_START_DATE;
1981 l_mkode0200_date := csr_rec_all.ASG_START_DATE;
1982 l_mkode0100_date := csr_rec_all.ASG_START_DATE;
1983 l_time_off_lieu_date := csr_rec_all.ASG_START_DATE;
1984 l_punit_date := csr_rec_all.ASG_START_DATE;
1985
1986 ELSE
1987 IF (csr_rec_all.asg_id = l_asgid_prev) THEN
1988
1989 IF NVL(csr_rec_all.job_occ_mkode,'XXX') <> NVL(l_prev_job_occ_mkode,'XXX') THEN
1990 l_job_occ_mkode_date := csr_rec_all.ASG_START_DATE;
1991 ELSE
1992 l_job_occ_mkode_date := l_prev_job_occ_mkode_date;
1993 END IF;
1994
1995 IF csr_rec_all.job_status_mkode <> l_prev_job_status_mkode THEN
1996 l_job_status_mkode_date := csr_rec_all.ASG_START_DATE;
1997 ELSE
1998 l_job_status_mkode_date := l_prev_job_status_mkode_date;
1999 END IF;
2000
2001 IF NVL(csr_rec_all.sal_basis_mkode,'XXX') <> NVL(l_prev_sal_basis_mkode,'XXX') THEN
2002 l_sal_basis_mkode_date := csr_rec_all.ASG_START_DATE;
2003 ELSE
2004 l_sal_basis_mkode_date := l_prev_sal_basis_mkode_date;
2005 END IF;
2006
2007 IF NVL(csr_rec_all.time_off_lieu,'XXX') <> NVL(l_prev_time_off_lieu,'XXX') THEN
2008 l_time_off_lieu_date := csr_rec_all.ASG_START_DATE;
2009 ELSE
2010 l_time_off_lieu_date := l_prev_time_off_lieu_date;
2011 END IF;
2012
2013 IF NVL(csr_rec_all.emp_grp,'XXX') <> NVL(l_prev_emp_grp,'XXX') THEN
2014 l_mkode0200_date := csr_rec_all.ASG_START_DATE;
2015 ELSE
2016 l_mkode0200_date := l_prev_mkode0200_date;
2017 END IF;
2018
2019 IF NVL(csr_rec_all.cond_of_emp,'XXX') <> NVL(l_prev_cond_of_emp,'XXX') THEN
2020 l_mkode0100_date := csr_rec_all.ASG_START_DATE;
2021 ELSE
2022 l_mkode0100_date := l_prev_mkode0100_date;
2023 END IF;
2024
2025 IF csr_rec_all.loc_id <> l_prev_loc_id THEN
2026 l_punit_date := csr_rec_all.ASG_START_DATE;
2027 ELSE
2028 l_punit_date := l_prev_punit_date;
2029 END IF;
2030
2031 END IF;
2032
2033 END IF;
2034
2035 l_prev_job_occ_mkode := csr_rec_all.job_occ_mkode;
2036 l_prev_job_status_mkode := csr_rec_all.job_status_mkode;
2037 l_prev_sal_basis_mkode := csr_rec_all.sal_basis_mkode;
2038 l_prev_time_off_lieu := csr_rec_all.time_off_lieu;
2039 l_prev_loc_id := csr_rec_all.loc_id;
2040 l_prev_emp_grp := csr_rec_all.emp_grp;
2041 l_prev_cond_of_emp := csr_rec_all.cond_of_emp;
2042
2043 l_prev_job_occ_mkode_date := l_job_occ_mkode_date;
2044 l_prev_job_status_mkode_date := l_job_status_mkode_date;
2045 l_prev_sal_basis_mkode_date := l_sal_basis_mkode_date;
2046 l_prev_mkode0200_date := l_mkode0200_date;
2047 l_prev_mkode0100_date := l_mkode0100_date;
2048 l_prev_time_off_lieu_date := l_time_off_lieu_date;
2049 l_prev_punit_date := l_punit_date;
2050 l_asgid_prev := csr_rec_all.asg_id;
2051
2052 END LOOP;
2053
2054 pay_action_information_api.create_action_information
2055 ( p_action_information_id => l_action_info_id -- OUT parameter
2056 ,p_object_version_number => l_ovn -- OUT parameter
2057 ,p_action_context_id => l_actid -- Context id = assignment action id (of Archive)
2058 ,p_action_context_type => 'AAP' -- Context type
2059 ,p_effective_date => l_effective_date -- Date of running the archive
2060 ,p_assignment_id => csr_rec.asg_id -- Assignment ID
2061 ,p_action_information_category => 'EMEA REPORT INFORMATION' -- Information Category
2062 ,p_tax_unit_id => l_le_id -- Legal Employer ID
2063 ,p_jurisdiction_code => NULL -- Tax Municipality ID
2064 ,p_action_information1 => 'PYDKSTATSA' -- Con Program Short Name
2065 ,p_action_information2 => NULL -- Payroll Name
2066 ,p_action_information3 => p_payroll_action_id -- Payroll action id (of Archive)
2067 ,p_action_information4 => l_time_off_lieu_date --csr_rec.time_off_lieu -- Assignment Number
2068 ,p_action_information5 => l_first_asg_start_date --first start date of assignment
2069 ,p_action_information6 => 'PART1_PART2_DATE_TRACKS' -- Archive part 1 on context AAP
2070 ,p_action_information7 => l_mkode0100_date --l_mkode0100 -- MKODE of IPTYPE 0100
2071 ,p_action_information8 => l_mkode0200_date --l_mkode0200 -- MKODE of IPTYPE 0200
2072 ,p_action_information9 => l_job_occ_mkode_date --csr_rec.job_occ_mkode -- MKODE of IPTYPE 0300
2073 ,p_action_information10 => l_job_status_mkode_date --csr_rec.job_status_mkode -- MKODE of IPTYPE 0400
2074 ,p_action_information11 => NULL -- MKODE of IPTYPE 0600
2075 ,p_action_information12 => NULL -- MKODE of IPTYPE 0610
2076 ,p_action_information13 => NULL -- MKODE of IPTYPE 0620
2077 ,p_action_information14 => NULL -- MKODE of IPTYPE 0700 /*Changes for Lunar Payroll */
2078 ,p_action_information15 => l_sal_basis_mkode_date --csr_rec.sal_basis_mkode -- MKODE of IPTYPE 0800
2079 ,p_action_information16 => NULL -- Balance for ILTYPE 0010
2080 ,p_action_information17 => NULL -- Balance for ILTYPE 0011
2081 ,p_action_information18 => NULL -- Balance for ILTYPE 0013
2082 ,p_action_information19 => NULL -- Balance for ILTYPE 0015
2083 ,p_action_information20 => NULL -- Balance for ILTYPE 0021
2084 ,p_action_information21 => NULL -- Balance for ILTYPE 0022
2085 ,p_action_information22 => l_punit_date --l_punit
2086 ,p_action_information23 => NULL -- Balance for ILTYPE 0024
2087 ,p_action_information24 => NULL -- Balance for ILTYPE 0025
2088 ,p_action_information25 => NULL -- Balance for ILTYPE 0026
2089 ,p_action_information26 => NULL -- Balance for ILTYPE 0032
2090 ,p_action_information27 => NULL -- Balance for ILTYPE 0034
2091 ,p_action_information28 => NULL -- Balance for ILTYPE 0036
2092 ,p_action_information29 => NULL -- Balance for ILTYPE 0037
2093 ,p_action_information30 => NULL -- Hourly/Salaried
2094 );
2095
2096 --9865127 END
2097
2098
2099 pay_action_information_api.create_action_information
2100 ( p_action_information_id => l_action_info_id -- OUT parameter
2101 ,p_object_version_number => l_ovn -- OUT parameter
2102 ,p_action_context_id => l_actid -- Context id = assignment action id (of Archive)
2103 ,p_action_context_type => 'AAP' -- Context type
2104 ,p_effective_date => l_effective_date -- Date of running the archive
2105 ,p_assignment_id => csr_rec.asg_id -- Assignment ID
2106 ,p_action_information_category => 'EMEA REPORT INFORMATION' -- Information Category
2107 ,p_tax_unit_id => l_le_id -- Legal Employer ID
2108 ,p_jurisdiction_code => NULL -- Tax Municipality ID
2109 ,p_action_information1 => 'PYDKSTATSA' -- Con Program Short Name
2110 ,p_action_information2 => csr_rec.payroll_name -- Payroll Name
2111 ,p_action_information3 => p_payroll_action_id -- Payroll action id (of Archive)
2112 ,p_action_information4 => csr_rec.time_off_lieu -- Time Off in Lieu
2113 ,p_action_information5 => l_start_date_from -- csr_rec.asg_start_date -- Assignment Start Date - For Bug 9002015
2114 ,p_action_information6 => 'PART2' -- Archive part 2 on context AAP
2115 ,p_action_information7 => fnd_number.number_to_canonical(l_iltype_0010_unit) -- Units for ILTYPE 0010
2116 ,p_action_information8 => fnd_number.number_to_canonical(l_iltype_0011_unit) -- Units for ILTYPE 0011
2117 ,p_action_information9 => fnd_number.number_to_canonical(l_iltype_0013_unit) -- Units for ILTYPE 0013
2118 ,p_action_information10 => fnd_number.number_to_canonical(l_iltype_0015_unit) -- Units for ILTYPE 0015
2119 ,p_action_information11 => NULL -- Units for ILTYPE 0021
2120 ,p_action_information12 => fnd_number.number_to_canonical(l_iltype_0022_unit) -- Units for ILTYPE 0022
2121 ,p_action_information13 => fnd_number.number_to_canonical(l_iltype_0023_unit) -- Units for ILTYPE 0023
2122 ,p_action_information14 => fnd_number.number_to_canonical(l_iltype_0024_unit) -- Units for ILTYPE 0024
2123 ,p_action_information15 => fnd_number.number_to_canonical(l_iltype_0025_unit) -- Units for ILTYPE 0025
2124 ,p_action_information16 => fnd_number.number_to_canonical(l_iltype_0026_unit) -- Units for ILTYPE 0026
2125 ,p_action_information17 => NULL -- Units for ILTYPE 0032
2126 ,p_action_information18 => fnd_number.number_to_canonical(l_iltype_0034_unit) -- Units for ILTYPE 0034
2127 ,p_action_information19 => fnd_number.number_to_canonical(l_iltype_0036_unit) -- Units for ILTYPE 0036
2128 ,p_action_information20 => fnd_number.number_to_canonical(l_iltype_0037_unit) -- Units for ILTYPE 0037
2129 ,p_action_information21 => to_char(l_bal_todate,'YYYYMMDD') -- Added for bug 5003220 to display end date instead of ass end date
2130 ,p_action_information22 => l_punit -- Assignment_level Production Unit Code
2131 --8848543
2132 ,p_action_information23 => l_start_date_from -- For Bug 9266075 to_char(l_date_from,'YYYYMMDD')
2133 ,p_action_information24 => to_char(l_bal_todate,'YYYYMMDD') -- For Bug 9266075 to_char(l_date_to,'YYYYMMDD')
2134 --8848543
2135 );
2136
2137 pay_action_information_api.create_action_information
2138 ( p_action_information_id => l_action_info_id -- OUT parameter
2139 ,p_object_version_number => l_ovn -- OUT parameter
2140 ,p_action_context_id => l_actid -- Context id = assignment action id (of Archive)
2141 ,p_action_context_type => 'AAP' -- Context type
2142 ,p_effective_date => l_effective_date -- Date of running the archive
2143 ,p_assignment_id => csr_rec.asg_id -- Assignment ID
2144 ,p_action_information_category => 'EMEA REPORT INFORMATION' -- Information Category
2145 ,p_tax_unit_id => l_le_id -- Legal Employer ID
2146 ,p_jurisdiction_code => NULL -- Tax Municipality ID
2147 ,p_action_information1 => 'PYDKSTATSA' -- Con Program Short Name
2148 ,p_action_information2 => csr_rec.payroll_name -- Payroll Name
2149 ,p_action_information3 => p_payroll_action_id -- Payroll action id (of Archive)
2150 ,p_action_information4 => null
2151 ,p_action_information5 => null
2152 ,p_action_information6 => 'PART3'
2153 ,p_action_information7 => fnd_number.number_to_canonical(l_iltype_0121_unit)
2154 ,p_action_information8 => fnd_number.number_to_canonical(l_iltype_0122_unit)
2155 ,p_action_information9 => fnd_number.number_to_canonical(l_iltype_0027_unit)
2156 ,p_action_information10 => fnd_number.number_to_canonical(l_iltype_0029_unit)
2157 ,p_action_information11 => fnd_number.number_to_canonical(l_iltype_0035_unit)
2158 ,p_action_information12 => fnd_number.number_to_canonical(l_iltype_0091_unit)
2159 ,p_action_information13 => fnd_number.number_to_canonical(l_iltype_0210_unit)
2160 ,p_action_information14 => fnd_number.number_to_canonical(l_iltype_0132_unit)
2161 ,p_action_information15 => fnd_number.number_to_canonical(l_iltype_0232_unit)
2162 ,p_action_information16 => fnd_number.number_to_canonical(l_iltype_0332_unit)
2163 ,p_action_information17 => NULL -- Reserved for future use
2164 ,p_action_information18 => NULL -- Reserved for future use
2165 ,p_action_information19 => fnd_number.number_to_canonical(l_iltype_0121_bal)
2166 ,p_action_information20 => fnd_number.number_to_canonical(l_iltype_0122_bal)
2167 ,p_action_information21 => fnd_number.number_to_canonical(l_iltype_0027_bal)
2168 ,p_action_information22 => fnd_number.number_to_canonical(l_iltype_0029_bal)
2169 ,p_action_information23 => fnd_number.number_to_canonical(l_iltype_0035_bal)
2170 ,p_action_information24 => fnd_number.number_to_canonical(l_iltype_0091_bal)
2171 ,p_action_information25 => fnd_number.number_to_canonical(l_iltype_0210_bal)
2172 ,p_action_information26 => fnd_number.number_to_canonical(l_iltype_0132_bal)
2173 ,p_action_information27 => fnd_number.number_to_canonical(l_iltype_0232_bal)
2174 ,p_action_information28 => fnd_number.number_to_canonical(l_iltype_0332_bal)
2175 ,p_action_information29 => NULL -- Reserved for future use
2176 ,p_action_information30 => NULL -- Reserved for future use
2177 );
2178
2179
2180 END IF; --Bug Fix 5003220,Archiving the data only once for an assignment
2181
2182 l_asgid := csr_rec.asg_id;
2183 l_old_job_occ_mkode := csr_rec.job_occ_mkode;
2184 l_old_job_status_mkode := csr_rec.job_status_mkode;
2185 l_old_sal_basis_mkode := csr_rec.sal_basis_mkode;
2186 l_old_time_off_lieu := csr_rec.time_off_lieu;
2187 l_old_loc_id := csr_rec.loc_id;
2188 -- For bug 9192911
2189 l_old_emp_grp := csr_rec.emp_grp;
2190 l_old_cond_of_emp := csr_rec.cond_of_emp;
2191
2192
2193 END LOOP;
2194
2195 END LOOP;
2196
2197
2198 IF g_debug THEN
2199 hr_utility.set_location(' Leaving Procedure ASSIGNMENT_ACTION_CODE',20);
2200 END IF;
2201
2202 EXCEPTION
2203 WHEN e_too_many_hours THEN
2204 fnd_message.set_name('PAY','PAY_377033_DK_TOO_MANY_WKG_HRS');
2205 fnd_file.put_line(fnd_file.log,fnd_message.get);
2206 error_message:=fnd_concurrent.set_completion_status('ERROR','PAY_377033_DK_TOO_MANY_WKG_HRS');
2207
2208 WHEN e_no_emp_dflts THEN
2209 fnd_message.set_name('PAY','PAY_377061_DK_NO_LE_EMP_DFLTS');
2210 fnd_message.set_token('ITEM',l_le_name);
2211 fnd_file.put_line(fnd_file.log,substr(fnd_message.get,1,254));
2212 error_message:=fnd_concurrent.set_completion_status('ERROR','PAY_377061_DK_NO_LE_EMP_DFLTS');
2213
2214 END ASSIGNMENT_ACTION_CODE;
2215
2216
2217 PROCEDURE INITIALIZATION_CODE(p_payroll_action_id IN NUMBER)
2218 IS
2219
2220
2221 BEGIN
2222
2223 NULL;
2224
2225 IF g_debug THEN
2226 hr_utility.set_location(' Entering Procedure INITIALIZATION_CODE',10);
2227 END IF;
2228
2229
2230 IF g_debug THEN
2231 hr_utility.set_location(' Leaving Procedure INITIALIZATION_CODE',20);
2232 END IF;
2233
2234 EXCEPTION WHEN OTHERS THEN
2235 g_err_num := SQLCODE;
2236
2237 IF g_debug THEN
2238 hr_utility.set_location('ORA_ERR: ' || g_err_num || 'In INITIALIZATION_CODE',30);
2239 END IF;
2240
2241 END INITIALIZATION_CODE;
2242
2243
2244
2245 /* ARCHIVE CODE */
2246 PROCEDURE ARCHIVE_CODE(p_assignment_action_id IN NUMBER
2247 ,p_effective_date IN DATE)
2248 IS
2249
2250 BEGIN
2251 IF g_debug THEN
2252 hr_utility.set_location(' Entering Procedure ARCHIVE_CODE',10);
2253 END IF;
2254
2255 IF g_debug THEN
2256 hr_utility.set_location(' Leaving Procedure ARCHIVE_CODE',20);
2257 END IF;
2258
2259 END ARCHIVE_CODE;
2260
2261
2262 PROCEDURE POPULATE_DATA
2263 (p_business_group_id IN NUMBER,
2264 p_payroll_action_id IN VARCHAR2 ,
2265 p_template_name IN VARCHAR2,
2266 p_xml OUT NOCOPY CLOB)
2267 IS
2268
2269 /* Cursor to fetch File Start and End Record Information */
2270 CURSOR csr_get_hdr_info(p_payroll_action_id NUMBER)
2271 IS
2272 SELECT *
2273 FROM pay_action_information pai
2274 WHERE action_context_type = 'PA'
2275 AND action_context_id = p_payroll_action_id
2276 AND action_information_category = 'EMEA REPORT DETAILS'
2277 AND action_information1 = 'PYDKSTATSA'
2278 AND action_information4 = 'HDR';
2279
2280
2281 /* Cursor to fetch Company Start and End Record Information */
2282 CURSOR csr_get_chdr_info(p_payroll_action_id NUMBER)
2283 IS
2284 SELECT *
2285 FROM pay_action_information pai
2286 WHERE action_context_type = 'PA'
2287 AND action_context_id = p_payroll_action_id
2288 AND action_information_category = 'EMEA REPORT DETAILS'
2289 AND action_information1 = 'PYDKSTATSA'
2290 AND action_information4 = 'CHDR';
2291
2292
2293 /* Cursors to fetch Personal and Salary Record Information */
2294 CURSOR csr_get_body_info1(p_payroll_action_id NUMBER, p_tax_unit_id NUMBER)
2295 IS
2296 SELECT *
2297 FROM pay_action_information pai
2298 WHERE action_context_type = 'AAP'
2299 AND action_information3 = to_char(p_payroll_action_id)
2300 AND action_information_category = 'EMEA REPORT INFORMATION'
2301 AND action_information1 = 'PYDKSTATSA'
2302 AND action_information6 ='PART1'
2303 AND tax_unit_id = p_tax_unit_id
2304 -- Add check on MKODE800 not being 91 or 92
2305 AND action_information15 NOT IN('91','92');
2306
2307 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)
2308 IS
2309 SELECT *
2310 FROM pay_action_information pai
2311 WHERE action_context_type = 'AAP'
2312 AND action_information3 = p_payroll_action_id
2313 AND action_information_category = 'EMEA REPORT INFORMATION'
2314 AND action_information1 = 'PYDKSTATSA'
2315 AND action_information6 ='PART2'
2316 AND tax_unit_id = p_tax_unit_id
2317 AND action_context_id = p_action_context_id
2318 AND effective_date = p_effective_date
2319 AND assignment_id = p_assignment_id;
2320
2321 CURSOR csr_get_body_info3(p_payroll_action_id NUMBER, p_tax_unit_id NUMBER,p_action_context_id NUMBER, p_effective_date DATE, p_assignment_id NUMBER)
2322 IS
2323 SELECT *
2324 FROM pay_action_information pai
2325 WHERE action_context_type = 'AAP'
2326 AND action_information3 = p_payroll_action_id
2327 AND action_information_category = 'EMEA REPORT INFORMATION'
2328 AND action_information1 = 'PYDKSTATSA'
2329 AND action_information6 ='PART3'
2330 AND tax_unit_id = p_tax_unit_id
2331 AND action_context_id = p_action_context_id
2332 AND effective_date = p_effective_date
2333 AND assignment_id = p_assignment_id;
2334
2335
2336 --9865127
2337 CURSOR csr_get_date_tracks(p_payroll_action_id NUMBER, p_tax_unit_id NUMBER,p_action_context_id NUMBER, p_effective_date DATE, p_assignment_id NUMBER)
2338 IS
2339 SELECT *
2340 FROM pay_action_information pai
2341 WHERE action_context_type = 'AAP'
2342 AND action_information3 = p_payroll_action_id
2343 AND action_information_category = 'EMEA REPORT INFORMATION'
2344 AND action_information1 = 'PYDKSTATSA'
2345 AND action_information6 ='PART1_PART2_DATE_TRACKS'
2346 AND tax_unit_id = p_tax_unit_id
2347 AND action_context_id = p_action_context_id
2348 AND effective_date = p_effective_date
2349 AND assignment_id = p_assignment_id;
2350 --9865127
2351
2352 rec_get_hdr_info csr_get_hdr_info%ROWTYPE;
2353 rec_get_body_info2 csr_get_body_info2%ROWTYPE;
2354 rec_get_body_info3 csr_get_body_info3%ROWTYPE;
2355 rec_get_date_tracks csr_get_date_tracks%ROWTYPE; --9865127
2356
2357
2358 l_counter NUMBER := 0;
2359 l_le_count NUMBER := 0;
2360 l_payroll_action_id NUMBER;
2361
2362 l_sign VARCHAR2(80);
2363 l_bal VARCHAR2(80);
2364
2365 TYPE iptype_rec_type IS RECORD
2366 (
2367 iptype VARCHAR2(240) := ' ',
2368 mkode VARCHAR2(240) := ' '
2369 );
2370
2371 TYPE iltype_rec_type IS RECORD
2372 (
2373 iltype VARCHAR2(240) := ' ',
2374 bal VARCHAR2(240) := ' ',
2375 units VARCHAR2(240) := ' '
2376 );
2377
2378
2379 TYPE iptype_tab_type
2380 IS TABLE OF iptype_rec_type
2381 INDEX BY BINARY_INTEGER;
2382
2383
2384 TYPE iltype_tab_type
2385 IS TABLE OF iltype_rec_type
2386 INDEX BY BINARY_INTEGER;
2387
2388
2389 iptype_tab iptype_tab_type;
2390 iltype_tab iltype_tab_type;
2391
2392 BEGIN
2393
2394 IF p_payroll_action_id IS NULL THEN
2395 BEGIN
2396 SELECT payroll_action_id
2397 INTO l_payroll_action_id
2398 FROM pay_payroll_actions ppa,
2399 fnd_conc_req_summary_v fcrs,
2400 fnd_conc_req_summary_v fcrs1
2401 WHERE fcrs.request_id = fnd_global.conc_request_id
2402 AND fcrs.priority_request_id = fcrs1.priority_request_id
2403 AND ppa.request_id between fcrs1.request_id and fcrs.request_id
2404 AND ppa.request_id = fcrs1.request_id;
2405
2406 EXCEPTION
2407 WHEN OTHERS THEN
2408 NULL;
2409 END ;
2410
2411 ELSE
2412
2413 l_payroll_action_id :=p_payroll_action_id;
2414
2415 END IF;
2416 hr_utility.set_location('Entered Procedure GETDATA',10);
2417
2418 /* Get the File Start and End Record Information */
2419 OPEN csr_get_hdr_info(l_payroll_action_id);
2420 FETCH csr_get_hdr_info INTO rec_get_hdr_info;
2421 CLOSE csr_get_hdr_info;
2422
2423 hr_utility.set_location('Before populating pl/sql table',20);
2424
2425 xml_tab(l_counter).TagName :='FILE_HEADER_FOOTER_START';
2426 xml_tab(l_counter).TagValue :='FILE_HEADER_FOOTER_START';
2427 l_counter := l_counter + 1;
2428
2429 xml_tab(l_counter).TagName :='CHAR_SET';
2430 xml_tab(l_counter).TagValue := rec_get_hdr_info.action_information8;
2431 l_counter := l_counter + 1;
2432
2433 xml_tab(l_counter).TagName :='FILE_FORMAT';
2434 xml_tab(l_counter).TagValue := rec_get_hdr_info.action_information9;
2435 l_counter := l_counter + 1;
2436
2437 xml_tab(l_counter).TagName :='SENDER_CVR_NO';
2438 xml_tab(l_counter).TagValue := rec_get_hdr_info.action_information10;
2439 l_counter := l_counter + 1;
2440
2441 xml_tab(l_counter).TagName :='SENDER_NAME';
2442 xml_tab(l_counter).TagValue := rec_get_hdr_info.action_information11;
2443 l_counter := l_counter + 1;
2444
2445 xml_tab(l_counter).TagName :='SENDER_ADDR';
2446 xml_tab(l_counter).TagValue := rec_get_hdr_info.action_information12;
2447 l_counter := l_counter + 1;
2448
2449 xml_tab(l_counter).TagName :='SENDER_PCODE';
2450 xml_tab(l_counter).TagValue := rec_get_hdr_info.action_information13;
2451 l_counter := l_counter + 1;
2452
2453 xml_tab(l_counter).TagName :='ITYPE_FILE_START';
2454 xml_tab(l_counter).TagValue := '1';
2455 l_counter := l_counter + 1;
2456
2457 FOR rec_get_chdr_info IN csr_get_chdr_info(l_payroll_action_id)
2458 LOOP
2459
2460 xml_tab(l_counter).TagName :='COMPANY_HEADER_FOOTER_START';
2461 xml_tab(l_counter).TagValue :='COMPANY_HEADER_FOOTER_START';
2462 l_counter := l_counter + 1;
2463
2464 l_le_count := 0;
2465
2466 xml_tab(l_counter).TagName :='LE_CVR_NO';
2467 xml_tab(l_counter).TagValue := rec_get_chdr_info.action_information5;
2468 l_counter := l_counter + 1;
2469
2470 xml_tab(l_counter).TagName :='LE_DS_WCODE';
2471 xml_tab(l_counter).TagValue := rec_get_chdr_info.action_information6;
2472 l_counter := l_counter + 1;
2473
2474 xml_tab(l_counter).TagName :='LE_DA_SCODE';
2475 xml_tab(l_counter).TagValue := rec_get_chdr_info.action_information7;
2476 l_counter := l_counter + 1;
2477
2478 xml_tab(l_counter).TagName :='LE_NAME';
2479 xml_tab(l_counter).TagValue := rec_get_chdr_info.action_information8;
2480 l_counter := l_counter + 1;
2481
2482 xml_tab(l_counter).TagName :='LE_ADDR';
2483 xml_tab(l_counter).TagValue := rec_get_chdr_info.action_information9;
2484 l_counter := l_counter + 1;
2485
2486 xml_tab(l_counter).TagName :='LE_PCODE';
2487 xml_tab(l_counter).TagValue := rec_get_chdr_info.action_information10;
2488 l_counter := l_counter + 1;
2489
2490 xml_tab(l_counter).TagName :='LE_PUNIT';
2491 xml_tab(l_counter).TagValue := rec_get_chdr_info.action_information11;
2492 l_counter := l_counter + 1;
2493
2494 xml_tab(l_counter).TagName :='ITYPE_COMPANY_START';
2495 xml_tab(l_counter).TagValue := '2';
2496 l_counter := l_counter + 1;
2497
2498 FOR rec_get_body_info IN csr_get_body_info1(l_payroll_action_id,rec_get_chdr_info.tax_unit_id)
2499 LOOP
2500
2501 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);
2502 FETCH csr_get_body_info2 INTO rec_get_body_info2;
2503 CLOSE csr_get_body_info2;
2504
2505 OPEN csr_get_body_info3(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);
2506 FETCH csr_get_body_info3 INTO rec_get_body_info3;
2507 CLOSE csr_get_body_info3;
2508
2509 --9865127
2510 OPEN csr_get_date_tracks(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);
2511 FETCH csr_get_date_tracks INTO rec_get_date_tracks;
2512 CLOSE csr_get_date_tracks;
2513 --9865127
2514
2515 iptype_tab(1).iptype := '0100';
2516 iptype_tab(1).mkode := rec_get_body_info.action_information7;
2517
2518 iptype_tab(2).iptype := '0200';
2519 iptype_tab(2).mkode := rec_get_body_info.action_information8;
2520
2521 -- For Bug 9192751
2522 --iptype_tab(3).iptype := '0300';
2523 iptype_tab(3).iptype := '0350';
2524 iptype_tab(3).mkode := rec_get_body_info.action_information9;
2525
2526 iptype_tab(4).iptype := '0400';
2527 iptype_tab(4).mkode := rec_get_body_info.action_information10;
2528
2529 iptype_tab(5).iptype := '0600';
2530 /* Modified for bug number 4998056. Multiply the amount with 100 to avoid decimal point */
2531 iptype_tab(5).mkode := nvl(round(FND_NUMBER.CANONICAL_TO_NUMBER(trim(rec_get_body_info.action_information11)),2) * 100,0);
2532
2533 iptype_tab(6).iptype := '0610';
2534 /* Modified for bug number 4998056. Multiply the amount with 100 to avoid decimal point */
2535 iptype_tab(6).mkode := nvl(round(FND_NUMBER.CANONICAL_TO_NUMBER(trim(rec_get_body_info.action_information12)),2) * 100,0);
2536
2537 iptype_tab(7).iptype := '0620';
2538 /* Modified for bug number 4998056. Multiply the amount with 100 to avoid decimal point */
2539 iptype_tab(7).mkode := nvl(round(FND_NUMBER.CANONICAL_TO_NUMBER(trim(rec_get_body_info.action_information13)),2) * 100,0);
2540
2541 iptype_tab(8).iptype := '0700';
2542 iptype_tab(8).mkode := rec_get_body_info.action_information14;
2543
2544 iptype_tab(9).iptype := '0800';
2545 iptype_tab(9).mkode := rec_get_body_info.action_information15;
2546
2547 /* Fixed to be 0010 for bug fix 4998180 */
2548 iltype_tab(1).iltype := '0010'; --'0100';
2549 iltype_tab(1).bal := rec_get_body_info.action_information16;
2550 iltype_tab(1).units := rec_get_body_info2.action_information7;
2551
2552 iltype_tab(2).iltype := '0011';
2553 iltype_tab(2).bal := rec_get_body_info.action_information17;
2554 iltype_tab(2).units := rec_get_body_info2.action_information8;
2555
2556 iltype_tab(3).iltype := '0013';
2557 iltype_tab(3).bal := rec_get_body_info.action_information18;
2558 iltype_tab(3).units := rec_get_body_info2.action_information9;
2559
2560 iltype_tab(4).iltype := '0015';
2561 iltype_tab(4).bal := rec_get_body_info.action_information19;
2562 iltype_tab(4).units := rec_get_body_info2.action_information10;
2563
2564 /*
2565 iltype_tab(5).iltype := '0021';
2566 iltype_tab(5).bal := rec_get_body_info.action_information20;
2567 iltype_tab(5).units := rec_get_body_info2.action_information11;*/
2568
2569 iltype_tab(5).iltype := '0121';
2570 iltype_tab(5).bal := rec_get_body_info3.action_information19;
2571 iltype_tab(5).units := rec_get_body_info3.action_information7;
2572
2573 iltype_tab(6).iltype := '0122';
2574 iltype_tab(6).bal := rec_get_body_info3.action_information20;
2575 iltype_tab(6).units := rec_get_body_info3.action_information8;
2576
2577 iltype_tab(7).iltype := '0022';
2578 iltype_tab(7).bal := rec_get_body_info.action_information21;
2579 iltype_tab(7).units := rec_get_body_info2.action_information12;
2580
2581 iltype_tab(8).iltype := '0023';
2582 iltype_tab(8).bal := rec_get_body_info.action_information22;
2583 iltype_tab(8).units := rec_get_body_info2.action_information13;
2584
2585 iltype_tab(9).iltype := '0024';
2586 iltype_tab(9).bal := rec_get_body_info.action_information23;
2587 iltype_tab(9).units := rec_get_body_info2.action_information14;
2588
2589 iltype_tab(10).iltype := '0025';
2590 iltype_tab(10).bal := rec_get_body_info.action_information24;
2591 iltype_tab(10).units := rec_get_body_info2.action_information15;
2592
2593 iltype_tab(11).iltype := '0026';
2594 iltype_tab(11).bal := rec_get_body_info.action_information25;
2595 iltype_tab(11).units := rec_get_body_info2.action_information16;
2596
2597 iltype_tab(12).iltype := '0027';
2598 iltype_tab(12).bal := rec_get_body_info3.action_information21;
2599 iltype_tab(12).units := rec_get_body_info3.action_information9;
2600
2601 iltype_tab(13).iltype := '0029';
2602 iltype_tab(13).bal := rec_get_body_info3.action_information22;
2603 iltype_tab(13).units := rec_get_body_info3.action_information10;
2604
2605 iltype_tab(14).iltype := '0032';
2606 iltype_tab(14).bal := rec_get_body_info.action_information26;
2607 iltype_tab(14).units := rec_get_body_info2.action_information17;
2608
2609 iltype_tab(15).iltype := '0034';
2610 iltype_tab(15).bal := rec_get_body_info.action_information27;
2611 iltype_tab(15).units := rec_get_body_info2.action_information18;
2612
2613 iltype_tab(16).iltype := '0035';
2614 iltype_tab(16).bal := rec_get_body_info3.action_information23;
2615 iltype_tab(16).units := rec_get_body_info3.action_information11;
2616
2617 iltype_tab(17).iltype := '0036';
2618 iltype_tab(17).bal := rec_get_body_info.action_information28;
2619 iltype_tab(17).units := rec_get_body_info2.action_information19;
2620
2621 iltype_tab(18).iltype := '0037';
2622 iltype_tab(18).bal := rec_get_body_info.action_information29;
2623 iltype_tab(18).units := rec_get_body_info2.action_information20;
2624
2625 iltype_tab(19).iltype := '0091';
2626 iltype_tab(19).bal := rec_get_body_info3.action_information24;
2627 iltype_tab(19).units := rec_get_body_info3.action_information12;
2628
2629 iltype_tab(20).iltype := '0210';
2630 iltype_tab(20).bal := rec_get_body_info3.action_information25;
2631 iltype_tab(20).units := rec_get_body_info3.action_information13;
2632
2633 iltype_tab(21).iltype := '0132';
2634 iltype_tab(21).bal := rec_get_body_info3.action_information26;
2635 iltype_tab(21).units := rec_get_body_info3.action_information14;
2636
2637 iltype_tab(22).iltype := '0232';
2638 iltype_tab(22).bal := rec_get_body_info3.action_information27;
2639 iltype_tab(22).units := rec_get_body_info3.action_information15;
2640
2641 iltype_tab(23).iltype := '0332';
2642 iltype_tab(23).bal := rec_get_body_info3.action_information28;
2643 iltype_tab(23).units := rec_get_body_info3.action_information16;
2644
2645 FOR i IN 1..iptype_tab.COUNT
2646 LOOP
2647
2648 -- IF(iptype_tab(i).mkode <> ' ') THEN
2649 /* Bug Fix 5030983 - Commented the if condition below which is restricting the
2650 display of personal record 0600 with payroll period as weekly and biweekly.*/
2651 -- IF NOT(iptype_tab(i).iptype ='0600' AND rec_get_body_info.action_information14 IN('2','3')) THEN
2652
2653 xml_tab(l_counter).TagName :='PERSON_RECO_START';
2654 xml_tab(l_counter).TagValue :='PERSON_RECO_START';
2655 l_counter := l_counter + 1;
2656
2657 l_le_count := l_le_count + 1;
2658
2659 xml_tab(l_counter).TagName :='LE_CVR_NO';
2660 xml_tab(l_counter).TagValue := rec_get_chdr_info.action_information5;
2661 l_counter := l_counter + 1;
2662
2663 xml_tab(l_counter).TagName :='LE_DS_WCODE';
2664 xml_tab(l_counter).TagValue := rec_get_chdr_info.action_information6;
2665 l_counter := l_counter + 1;
2666
2667 xml_tab(l_counter).TagName :='LE_DA_SCODE';
2668 xml_tab(l_counter).TagValue := rec_get_chdr_info.action_information7;
2669 l_counter := l_counter + 1;
2670
2671 xml_tab(l_counter).TagName :='ASSG_NO';
2672 xml_tab(l_counter).TagValue := rec_get_body_info.action_information4;
2673 l_counter := l_counter + 1;
2674
2675 xml_tab(l_counter).TagName :='CPR_NO';
2676 xml_tab(l_counter).TagValue := rec_get_body_info.action_information5;
2677 l_counter := l_counter + 1;
2678
2679 xml_tab(l_counter).TagName :='IPTYPE';
2680 xml_tab(l_counter).TagValue := iptype_tab(i).iptype;
2681 l_counter := l_counter + 1;
2682
2683 xml_tab(l_counter).TagName :='MKODE';
2684 xml_tab(l_counter).TagValue := iptype_tab(i).mkode;
2685 l_counter := l_counter + 1;
2686
2687 xml_tab(l_counter).TagName :='EFF_DATE';
2688 --9865127 START
2689 --xml_tab(l_counter).TagValue := rec_get_body_info2.action_information5;
2690 IF iptype_tab(i).iptype = '0100' THEN
2691 xml_tab(l_counter).TagValue := rec_get_date_tracks.action_information7;
2692 ELSIF iptype_tab(i).iptype = '0200' THEN
2693 xml_tab(l_counter).TagValue := rec_get_date_tracks.action_information8;
2694 ELSIF iptype_tab(i).iptype = '0350' THEN
2695 xml_tab(l_counter).TagValue := rec_get_date_tracks.action_information9;
2696 ELSIF iptype_tab(i).iptype = '0400' THEN
2697 xml_tab(l_counter).TagValue := rec_get_date_tracks.action_information10;
2698 ELSIF iptype_tab(i).iptype = '0800' THEN
2699 xml_tab(l_counter).TagValue := rec_get_date_tracks.action_information15;
2700 ELSE
2701 xml_tab(l_counter).TagValue := rec_get_date_tracks.action_information5;
2702 END IF;
2703 --9865127 END
2704 l_counter := l_counter + 1;
2705
2706 xml_tab(l_counter).TagName :='DATE_FROM';
2707 xml_tab(l_counter).TagValue := rec_get_body_info2.action_information23; --rec_get_hdr_info.action_information6; --8848543
2708 l_counter := l_counter + 1;
2709
2710 xml_tab(l_counter).TagName :='DATE_TO';
2711 xml_tab(l_counter).TagValue := rec_get_body_info2.action_information24; --rec_get_hdr_info.action_information7; --8848543
2712 l_counter := l_counter + 1;
2713
2714 xml_tab(l_counter).TagName :='ITYPE_PERSON';
2715 xml_tab(l_counter).TagValue := '3';
2716 l_counter := l_counter + 1;
2717
2718 xml_tab(l_counter).TagName :='ASG_PUNIT';
2719 xml_tab(l_counter).TagValue := rec_get_body_info2.action_information22;
2720 l_counter := l_counter + 1;
2721
2722
2723 xml_tab(l_counter).TagName :='PERSON_RECO_START';
2724 xml_tab(l_counter).TagValue :='PERSON_RECO_END';
2725 l_counter := l_counter + 1;
2726
2727 -- END IF; -- Bug Fix 5030983 - Commented
2728
2729 END LOOP;
2730 FOR j IN 1..iltype_tab.COUNT
2731 LOOP
2732
2733 /* Modified condition to show balances only if they are non-zero after FS changes */
2734
2735 /*IF NOT(iltype_tab(j).iltype IN ('0011','0015','0022','0024','0026','0032','0036') AND iltype_tab(j).bal = '0')
2736 OR (iltype_tab(j).iltype = '0037' AND rec_get_body_info.action_information4 <>'N')
2737 OR (iltype_tab(j).iltype = '0024' AND rec_get_body_info.action_information14 IN('2','3'))
2738 OR (iltype_tab(j).iltype = '0025' AND rec_get_body_info.action_information14 ='1')
2739 */
2740 /*Modified with or clause for 0026 for bug5009836 */
2741 /* IF ( iltype_tab(j).bal <> '0'
2742 OR (iltype_tab(j).iltype = '0037' AND rec_get_body_info.action_information4 ='N')
2743 OR (iltype_tab(j).iltype = '0024' AND rec_get_body_info.action_information14 IN('2','3'))
2744 OR (iltype_tab(j).iltype = '0025' AND rec_get_body_info.action_information14 ='1')
2745 OR (iltype_tab(j).iltype = '0026') )
2746 THEN*/
2747 /* pgopal - Bug 5747199 fix - Checking Hourly/Salaried*/
2748 /*Bug fix 5009836 include a check on unit for record 026 */
2749 IF ( iltype_tab(j).bal <> '0'
2750 OR (iltype_tab(j).iltype = '0037' AND rec_get_body_info.action_information4 ='N')
2751 OR (iltype_tab(j).iltype = '0024' AND rec_get_body_info.action_information30 ='H')
2752 OR (iltype_tab(j).iltype = '0025' AND rec_get_body_info.action_information30 ='S' AND iltype_tab(j).bal <> '0' ) --9888286
2753 OR (iltype_tab(j).iltype IN ('0026', '0210', '0034') AND iltype_tab(j).units <> '0') ) --8848543
2754 THEN
2755
2756 xml_tab(l_counter).TagName :='SALARY_RECO_START';
2757 xml_tab(l_counter).TagValue :='SALARY_RECO_START';
2758 l_counter := l_counter + 1;
2759
2760 l_le_count := l_le_count + 1;
2761
2762 xml_tab(l_counter).TagName :='LE_CVR_NO';
2763 xml_tab(l_counter).TagValue := rec_get_chdr_info.action_information5;
2764 l_counter := l_counter + 1;
2765
2766 xml_tab(l_counter).TagName :='LE_DS_WCODE';
2767 xml_tab(l_counter).TagValue := rec_get_chdr_info.action_information6;
2768 l_counter := l_counter + 1;
2769
2770 xml_tab(l_counter).TagName :='LE_DA_SCODE';
2771 xml_tab(l_counter).TagValue := rec_get_chdr_info.action_information7;
2772 l_counter := l_counter + 1;
2773
2774 xml_tab(l_counter).TagName :='ASSG_NO';
2775 xml_tab(l_counter).TagValue := rec_get_body_info.action_information4;
2776 l_counter := l_counter + 1;
2777
2778 xml_tab(l_counter).TagName :='CPR_NO';
2779 xml_tab(l_counter).TagValue := rec_get_body_info.action_information5;
2780 l_counter := l_counter + 1;
2781
2782 xml_tab(l_counter).TagName :='ILTYPE';
2783 xml_tab(l_counter).TagValue := iltype_tab(j).iltype;
2784 l_counter := l_counter + 1;
2785
2786 IF (substr(iltype_tab(j).units,1,1) = '-') THEN
2787 l_sign := '-';
2788 l_bal := substr(iltype_tab(j).units,2);
2789 ELSE
2790 l_sign := '+';
2791 l_bal := iltype_tab(j).units;
2792 END IF;
2793
2794 xml_tab(l_counter).TagName :='TIME_UNITS';
2795 /* Modified for bug number 4997824. Multiply the balances with 100 to avoid decimal point */
2796 xml_tab(l_counter).TagValue := round(FND_NUMBER.CANONICAL_TO_NUMBER(l_bal),2) * 100;
2797 l_counter := l_counter + 1;
2798
2799 xml_tab(l_counter).TagName :='SIGN_UNITS';
2800 xml_tab(l_counter).TagValue := l_sign;
2801 l_counter := l_counter + 1;
2802
2803
2804 IF (substr(iltype_tab(j).bal,1,1) = '-') THEN
2805 l_sign := '-';
2806 l_bal := substr(iltype_tab(j).bal,2);
2807 ELSE
2808 l_sign := '+';
2809 l_bal := iltype_tab(j).bal;
2810 END IF;
2811
2812 xml_tab(l_counter).TagName :='ILTYPE_BAL';
2813 /* Modified for bug number 4997824. Multiply the amount with 100 to avoid decimal point */
2814 xml_tab(l_counter).TagValue := round(FND_NUMBER.CANONICAL_TO_NUMBER(l_bal),2) * 100;
2815 l_counter := l_counter + 1;
2816
2817 xml_tab(l_counter).TagName :='SIGN_BAL';
2818 xml_tab(l_counter).TagValue := l_sign;
2819 l_counter := l_counter + 1;
2820 -- Changed for bug 5003220 to display end date instead of ass end date
2821 xml_tab(l_counter).TagName :='DATE_FROM';
2822 xml_tab(l_counter).TagValue := rec_get_body_info2.action_information23; --9865127 --rec_get_body_info2.action_information5;
2823 l_counter := l_counter + 1;
2824
2825 xml_tab(l_counter).TagName :='DATE_TO';
2826 xml_tab(l_counter).TagValue := rec_get_body_info2.action_information21;
2827 l_counter := l_counter + 1;
2828
2829 xml_tab(l_counter).TagName :='ITYPE_SALARY';
2830 xml_tab(l_counter).TagValue := '4';
2831 l_counter := l_counter + 1;
2832
2833 xml_tab(l_counter).TagName :='ASG_PUNIT';
2834 xml_tab(l_counter).TagValue := rec_get_body_info2.action_information22;
2835 l_counter := l_counter + 1;
2836
2837
2838 xml_tab(l_counter).TagName :='SALARY_RECO_START';
2839 xml_tab(l_counter).TagValue :='SALARY_RECO_END';
2840 l_counter := l_counter + 1;
2841
2842 END IF;
2843
2844 END LOOP;
2845
2846 END LOOP;
2847
2848 xml_tab(l_counter).TagName :='ITYPE_COMPANY_END';
2849 xml_tab(l_counter).TagValue := '7';
2850 l_counter := l_counter + 1;
2851
2852 xml_tab(l_counter).TagName :='COUNT_LE';
2853 xml_tab(l_counter).TagValue := l_le_count;
2854 l_counter := l_counter + 1;
2855
2856 xml_tab(l_counter).TagName :='LE_PUNIT';
2857 xml_tab(l_counter).TagValue := rec_get_chdr_info.action_information11;
2858 l_counter := l_counter + 1;
2859
2860 xml_tab(l_counter).TagName :='COMPANY_HEADER_FOOTER_START';
2861 xml_tab(l_counter).TagValue :='COMPANY_HEADER_FOOTER_END';
2862 l_counter := l_counter + 1;
2863
2864 END LOOP;
2865
2866 xml_tab(l_counter).TagName :='ITYPE_FILE_END';
2867 xml_tab(l_counter).TagValue := '9';
2868 l_counter := l_counter + 1;
2869
2870 xml_tab(l_counter).TagName :='BG_DA_SYS_NO';
2871 xml_tab(l_counter).TagValue := rec_get_hdr_info.action_information14;
2872 l_counter := l_counter + 1;
2873
2874 xml_tab(l_counter).TagName :='UPDATE_DATE';
2875 xml_tab(l_counter).TagValue := to_char(rec_get_hdr_info.effective_date,'YYYYMMDD');
2876 l_counter := l_counter + 1;
2877
2878 xml_tab(l_counter).TagName :='PAYROLL_SYS_NAME';
2879 xml_tab(l_counter).TagValue := rec_get_hdr_info.action_information15;
2880 l_counter := l_counter + 1;
2881
2882 xml_tab(l_counter).TagName :='FILE_HEADER_FOOTER_START';
2883 xml_tab(l_counter).TagValue :='FILE_HEADER_FOOTER_END';
2884 l_counter := l_counter + 1;
2885
2886 hr_utility.set_location('After populating pl/sql table',30);
2887 hr_utility.set_location('Entered Procedure GETDATA',10);
2888
2889
2890 WritetoCLOB (p_xml );
2891
2892
2893 END POPULATE_DATA;
2894 /********************************************************/
2895
2896 PROCEDURE WritetoCLOB(p_xfdf_clob out nocopy CLOB) is
2897 l_xfdf_string clob;
2898 l_str1 varchar2(1000);
2899 l_str2 varchar2(20);
2900 l_str3 varchar2(20);
2901 l_str4 varchar2(20);
2902 l_str5 varchar2(20);
2903 l_str6 varchar2(30);
2904 l_str7 varchar2(1000);
2905 l_str8 varchar2(240);
2906 l_str9 varchar2(240);
2907 l_str10 varchar2(20);
2908 l_str11 varchar2(20);
2909 l_IANA_charset VARCHAR2 (50);
2910
2911 current_index pls_integer;
2912
2913 BEGIN
2914
2915 hr_utility.set_location('Entering WritetoCLOB ',10);
2916 l_IANA_charset :=PAY_DK_GENERAL.get_IANA_charset ;
2917 --l_str1 := '<?xml version="1.0" encoding="UTF-8"?> <ROOT><STATSR>' ;
2918 l_str1 := '<?xml version="1.0" encoding="'||l_IANA_charset||'"?> <ROOT><STATSR>';
2919 l_str2 := '<';
2920 l_str3 := '>';
2921 l_str4 := '</';
2922 l_str5 := '>';
2923 l_str6 := '</STATSR></ROOT>';
2924 --l_str7 := '<?xml version="1.0" encoding="UTF-8"?> <ROOT></ROOT>';
2925 l_str7 := '<?xml version="1.0" encoding="'||l_IANA_charset||'"?> <ROOT></ROOT>';
2926 l_str10 := '<STATSR>';
2927 l_str11 := '</STATSR>';
2928
2929
2930 dbms_lob.createtemporary(l_xfdf_string,FALSE,DBMS_LOB.CALL);
2931 dbms_lob.open(l_xfdf_string,dbms_lob.lob_readwrite);
2932
2933 current_index := 0;
2934
2935 IF xml_tab.count > 0 THEN
2936
2937 dbms_lob.writeAppend( l_xfdf_string, length(l_str1), l_str1 );
2938
2939
2940 FOR table_counter IN xml_tab.FIRST .. xml_tab.LAST LOOP
2941
2942 l_str8 := xml_tab(table_counter).TagName;
2943 l_str9 := xml_tab(table_counter).TagValue;
2944
2945 IF l_str9 IN ('FILE_HEADER_FOOTER_START', 'FILE_HEADER_FOOTER_END','COMPANY_HEADER_FOOTER_START' ,'COMPANY_HEADER_FOOTER_END'
2946 ,'PERSON_RECO_START','PERSON_RECO_END','SALARY_RECO_START','SALARY_RECO_END') THEN
2947
2948 IF l_str9 IN ('FILE_HEADER_FOOTER_START' , 'COMPANY_HEADER_FOOTER_START' , 'PERSON_RECO_START','SALARY_RECO_START') THEN
2949 dbms_lob.writeAppend(l_xfdf_string, length(l_str2), l_str2);
2950 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
2951 dbms_lob.writeAppend(l_xfdf_string, length(l_str3), l_str3);
2952 ELSE
2953 dbms_lob.writeAppend(l_xfdf_string, length(l_str4), l_str4);
2954 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
2955 dbms_lob.writeAppend(l_xfdf_string, length(l_str5), l_str5);
2956 END IF;
2957
2958 ELSE
2959
2960 if l_str9 is not null then
2961 l_str9 := hr_dk_utility.REPLACE_SPECIAL_CHARS(l_str9); /* Place the check after not null check*/
2962 dbms_lob.writeAppend(l_xfdf_string, length(l_str2), l_str2);
2963 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
2964 dbms_lob.writeAppend(l_xfdf_string, length(l_str3), l_str3);
2965 dbms_lob.writeAppend(l_xfdf_string, length(l_str9), l_str9);
2966 dbms_lob.writeAppend(l_xfdf_string, length(l_str4), l_str4);
2967 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
2968 dbms_lob.writeAppend(l_xfdf_string, length(l_str5), l_str5);
2969 else
2970
2971 dbms_lob.writeAppend(l_xfdf_string, length(l_str2), l_str2);
2972 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
2973 dbms_lob.writeAppend(l_xfdf_string, length(l_str3), l_str3);
2974 dbms_lob.writeAppend(l_xfdf_string, length(l_str4), l_str4);
2975 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
2976 dbms_lob.writeAppend(l_xfdf_string, length(l_str5), l_str5);
2977
2978 end if;
2979
2980 END IF;
2981
2982 END LOOP;
2983
2984 dbms_lob.writeAppend(l_xfdf_string, length(l_str6), l_str6 );
2985
2986 ELSE
2987 dbms_lob.writeAppend(l_xfdf_string, length(l_str7), l_str7 );
2988 END IF;
2989
2990 p_xfdf_clob := l_xfdf_string;
2991
2992 hr_utility.set_location('Leaving WritetoCLOB ',20);
2993
2994
2995 EXCEPTION
2996 WHEN OTHERS then
2997 HR_UTILITY.TRACE('sqlerrm ' || SQLERRM);
2998 HR_UTILITY.RAISE_ERROR;
2999 END WritetoCLOB;
3000
3001 END PAY_DK_STATSR_PKG;