[Home] [Help]
PACKAGE BODY: APPS.PAY_NO_PAYSLIP_ARCHIVE
Source
1 PACKAGE BODY PAY_NO_PAYSLIP_ARCHIVE AS
2 /* $Header: pynoparc.pkb 120.8.12020000.2 2013/01/11 10:08:29 smeduri ship $ */
3
4
5 ----------------- Globals , Record types , Tables -------------------------------------------------
6
7 g_debug boolean := hr_utility.debug_enabled;
8
9 /*
10 TYPE element_rec IS RECORD (
11 classification_name VARCHAR2(60)
12 ,element_name VARCHAR2(60)
13 ,element_type_id NUMBER
14 ,input_value_id NUMBER
15 ,element_type VARCHAR2(1)
16 ,uom VARCHAR2(1)
17 ,archive_flag VARCHAR2(1));
18
19 */
20
21 -- Bug Fix : 5909609, increasing the size of variable 'uom' from VARCHAR2(1) to VARCHAR2(10)
22
23 TYPE element_rec IS RECORD (
24 classification_name VARCHAR2(60)
25 ,element_name VARCHAR2(60)
26 ,element_type_id NUMBER
27 ,input_value_id NUMBER
28 ,element_type VARCHAR2(1)
29 -- ,uom VARCHAR2(1)
30 ,uom VARCHAR2(10)
31 ,prim_bal_def_bal_id NUMBER
32 -- ,basis_for_holiday_pay VARCHAR2(20)
33 -- ,basis_for_withholding_tax VARCHAR2(20)
34 ,archive_flag VARCHAR2(1)
35 ,lookup_type VARCHAR2(240)
36 ,value_set_id NUMBER );
37
38
39
40 TYPE balance_rec IS RECORD (
41 balance_name VARCHAR2(60),
42 defined_balance_id NUMBER,
43 balance_type_id NUMBER);
44
45 TYPE lock_rec IS RECORD ( archive_assact_id NUMBER);
46
47 TYPE tax_card_rec IS RECORD (inp_val_name pay_input_values_f.NAME%type , screen_entry_val pay_element_entry_values_f.SCREEN_ENTRY_VALUE%type );
48
49 TYPE bal_val_rec IS RECORD ( bal_name ff_database_items.USER_NAME%type , bal_val NUMBER(10,2) );
50
51
52 TYPE tax_card_table IS TABLE OF tax_card_rec INDEX BY BINARY_INTEGER;
53 TYPE bal_val_table IS TABLE OF bal_val_rec INDEX BY BINARY_INTEGER;
54 TYPE element_table IS TABLE OF element_rec INDEX BY BINARY_INTEGER;
55 TYPE balance_table IS TABLE OF balance_rec INDEX BY BINARY_INTEGER;
56 TYPE lock_table IS TABLE OF lock_rec INDEX BY BINARY_INTEGER;
57
58 g_tax_card_tab tax_card_table;
59 g_bal_val bal_val_table;
60 g_element_table element_table;
61 g_user_balance_table balance_table;
62 g_lock_table lock_table;
63 g_index NUMBER := -1;
64 g_index_assact NUMBER := -1;
65 g_index_bal NUMBER := -1;
66 g_package VARCHAR2(33) := ' PAY_NO_PAYSLIP_ARCHIVE.';
67 g_payroll_action_id NUMBER;
68 g_arc_payroll_action_id NUMBER;
69 g_business_group_id NUMBER;
70 g_format_mask VARCHAR2(50);
71 g_err_num NUMBER;
72 g_errm VARCHAR2(150);
73
74 ------------------------------ FUNCTION GET_PARAMETER --------------------------------------------------------------------
75
76 /* GET PARAMETER */
77 FUNCTION GET_PARAMETER(
78 p_parameter_string IN VARCHAR2
79 ,p_token IN VARCHAR2
80 ,p_segment_number IN NUMBER default NULL ) RETURN VARCHAR2
81 IS
82
83 l_parameter pay_payroll_actions.legislative_parameters%TYPE:=NULL;
84 l_start_pos NUMBER;
85 l_delimiter VARCHAR2(1):=' ';
86 l_proc VARCHAR2(40):= g_package||' get parameter ';
87
88 BEGIN
89 -- fnd_file.put_line(fnd_file.log,'Entering Function GET_PARAMETER');
90 --
91 IF g_debug THEN
92 hr_utility.set_location(' Entering Function GET_PARAMETER',10);
93 END IF;
94 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
95 --
96
97 IF l_start_pos = 0 THEN
98 l_delimiter := '|';
99 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
100 END IF;
101
102 IF l_start_pos <> 0 THEN
103 l_start_pos := l_start_pos + length(p_token||'=');
104 l_parameter := substr(p_parameter_string, l_start_pos, instr(p_parameter_string||' ', l_delimiter,l_start_pos) - l_start_pos);
105
106 IF p_segment_number IS NOT NULL THEN
107 l_parameter := ':'||l_parameter||':';
108 l_parameter := substr(l_parameter,
109 instr(l_parameter,':',1,p_segment_number)+1,
110 instr(l_parameter,':',1,p_segment_number+1) -1
111 - instr(l_parameter,':',1,p_segment_number));
112 END IF;
113 END IF;
114 --
115 RETURN l_parameter;
116
117 IF g_debug THEN
118 hr_utility.set_location(' Leaving Function GET_PARAMETER',20);
119 END IF;
120 -- fnd_file.put_line(fnd_file.log,'Leaving Function GET_PARAMETER');
121 END;
122
123 --------------------------------- PROCEDURE GET_ALL_PARAMETERS -----------------------------------------------------------------
124
125 /* GET ALL PARAMETERS */
126 PROCEDURE GET_ALL_PARAMETERS(
127 p_payroll_action_id IN NUMBER
128 ,p_business_group_id OUT NOCOPY NUMBER
129 ,p_start_date OUT NOCOPY VARCHAR2
130 ,p_end_date OUT NOCOPY VARCHAR2
131 ,p_effective_date OUT NOCOPY DATE
132 ,p_payroll_id OUT NOCOPY VARCHAR2
133 ,p_consolidation_set OUT NOCOPY VARCHAR2) IS
134 --
135 CURSOR csr_parameter_info(p_payroll_action_id NUMBER) IS
136 SELECT PAY_NO_PAYSLIP_ARCHIVE.GET_PARAMETER(legislative_parameters,'PAYROLL_ID')
137 ,PAY_NO_PAYSLIP_ARCHIVE.GET_PARAMETER(legislative_parameters,'CONSOLIDATION_SET_ID')
138 ,PAY_NO_PAYSLIP_ARCHIVE.GET_PARAMETER(legislative_parameters,'START_DATE')
139 ,PAY_NO_PAYSLIP_ARCHIVE.GET_PARAMETER(legislative_parameters,'END_DATE')
140 ,effective_date
141 ,business_group_id
142 FROM pay_payroll_actions
143 WHERE payroll_action_id = p_payroll_action_id;
144
145 l_proc VARCHAR2(240):= g_package||' GET_ALL_PARAMETERS ';
146 --
147
148 BEGIN
149 -- fnd_file.put_line(fnd_file.log,'Entering Procedure GET_ALL_PARAMETERS');
150
151 OPEN csr_parameter_info (p_payroll_action_id);
152 FETCH csr_parameter_info INTO p_payroll_id
153 ,p_consolidation_set
154 ,p_start_date
155 ,p_end_date
156 ,p_effective_date
157 ,p_business_group_id;
158 CLOSE csr_parameter_info;
159 --
160 IF g_debug THEN
161 hr_utility.set_location(' Leaving Procedure GET_ALL_PARAMETERS',30);
162 END IF;
163
164 -- fnd_file.put_line(fnd_file.log,'GET_ALL_PARAMETERS : p_payroll_action_id = ' || p_payroll_action_id );
165 -- fnd_file.put_line(fnd_file.log,'GET_ALL_PARAMETERS : p_payroll_id = ' || p_payroll_id );
166 -- fnd_file.put_line(fnd_file.log,'GET_ALL_PARAMETERS : p_consolidation_set = ' || p_consolidation_set);
167 -- fnd_file.put_line(fnd_file.log,'GET_ALL_PARAMETERS : p_start_date = ' || p_start_date );
168 -- fnd_file.put_line(fnd_file.log,'GET_ALL_PARAMETERS : p_end_date = ' || p_end_date );
169 -- fnd_file.put_line(fnd_file.log,'GET_ALL_PARAMETERS : p_effective_date = ' || p_effective_date );
170 -- fnd_file.put_line(fnd_file.log,'GET_ALL_PARAMETERS : p_business_group_id = ' || p_business_group_id );
171
172 -- fnd_file.put_line(fnd_file.log,'Leaving Procedure GET_ALL_PARAMETERS');
173
174 END GET_ALL_PARAMETERS;
175
176 ----------------------------------- PROCEDURE RANGE_CODE ---------------------------------------------------------------
177
178 /* RANGE CODE */
179 PROCEDURE RANGE_CODE (p_payroll_action_id IN NUMBER
180 ,p_sql OUT NOCOPY VARCHAR2)
181 IS
182
183 -----------------------------------------------------
184 -- MESSAGES
185 ----------------------------------------------------
186 -- Cursor to get the messages from Busineess Group:Payslip Info
187 CURSOR csr_get_message(p_bus_grp_id NUMBER) IS
188 SELECT org_information6 message
189 FROM hr_organization_information
190 WHERE organization_id = p_bus_grp_id
191 AND org_information_context = 'Business Group:Payslip Info'
192 AND org_information1 = 'MESG';
193
194 -----------------------------------------------------------------
195 -- BALANCES
196 -----------------------------------------------------------------
197
198 /* Cursor to retrieve Other Balances Information */
199 CURSOR csr_get_balance(p_bus_grp_id NUMBER) IS
200 SELECT org_information4 balance_type_id
201 ,org_information5 balance_dim_id
202 ,org_information7 narrative
203 FROM hr_organization_information
204 WHERE organization_id = p_bus_grp_id
205 AND org_information_context = 'Business Group:Payslip Info'
206 AND org_information1 = 'BALANCE';
207
208 /* Cursor to fetch defined balance id */
209 CURSOR csr_def_balance(bal_type_id NUMBER, bal_dim_id NUMBER) IS
210 SELECT defined_balance_id
211 FROM pay_defined_balances
212 WHERE balance_type_id = bal_type_id
213 AND balance_dimension_id = bal_dim_id;
214
215 -----------------------------------------------------
216 --ELEMENTS
217 ----------------------------------------------------
218
219 /* Cursor to retrieve Time Period Information */
220 CURSOR csr_time_periods(p_run_payact_id NUMBER ,p_payroll_id NUMBER) IS
221 SELECT ptp.end_date end_date,
222 ptp.start_date start_date,
223 ptp.period_name period_name,
224 ppf.payroll_name payroll_name
225 FROM per_time_periods ptp
226 ,pay_payroll_actions ppa
227 ,pay_payrolls_f ppf
228 WHERE ptp.payroll_id = ppa.payroll_id
229 AND ppa.payroll_action_id = p_run_payact_id
230 AND ppa.payroll_id = ppf.payroll_id
231 AND ppf.payroll_id = NVL(p_payroll_id , ppf.payroll_id)
232 AND ppa.date_earned BETWEEN ptp.start_date AND ptp.end_date
233 AND ppa.date_earned BETWEEN ppf.effective_start_date AND ppf.effective_end_date;
234
235 --------------------------------------------------------------
236 -- Additional Element
237 --------------------------------------------------------------
238
239 /* Cursor to retrieve Additional Element Information */
240
241 /*
242 CURSOR csr_get_element(p_bus_grp_id NUMBER, p_date_earned DATE) IS
243 SELECT hoi.org_information2 element_type_id
244 ,hoi.org_information3 input_value_id
245 ,hoi.org_information7 element_narrative
246 ,pec.classification_name ele_class
247 ,piv.uom uom
248 FROM hr_organization_information hoi
249 ,pay_element_classifications pec
250 ,pay_element_types_f pet
251 ,pay_input_values_f piv
252 WHERE hoi.organization_id = p_bus_grp_id
253 AND hoi.org_information_context = 'Business Group:Payslip Info'
254 AND hoi.org_information1 = 'ELEMENT'
255 AND hoi.org_information2 = pet.element_type_id
256 AND pec.classification_id = pet.classification_id
257 AND piv.input_value_id = hoi.org_information3
258 AND p_date_earned BETWEEN piv.effective_start_date AND piv.effective_end_date;
259 */
260
261 CURSOR csr_get_element(p_bus_grp_id NUMBER, p_date_earned DATE) IS
262 SELECT hoi.org_information2 element_type_id
263 ,hoi.org_information3 input_value_id
264 ,hoi.org_information7 element_narrative
265 ,pec.classification_name ele_class
266 ,piv.uom uom
267 ,piv.lookup_type lookup_type
268 ,piv.value_set_id value_set_id
269 FROM hr_organization_information hoi
270 ,pay_element_classifications pec
271 ,pay_element_types_f pet
272 ,pay_input_values_f piv
273 WHERE hoi.organization_id = p_bus_grp_id
274 AND hoi.org_information_context = 'Business Group:Payslip Info'
275 AND hoi.org_information1 = 'ELEMENT'
276 AND hoi.org_information2 = pet.element_type_id
277 AND pec.classification_id = pet.classification_id
278 AND piv.input_value_id = hoi.org_information3
279 AND p_date_earned BETWEEN piv.effective_start_date AND piv.effective_end_date;
280
281 ----------------------------------------------------------------
282
283 /* Cursor to get the first primary balance type id for an input value id */
284
285 CURSOR csr_prim_bal_type (p_iv_id NUMBER)IS
286 SELECT balance_type_id
287 FROM pay_balance_types
288 WHERE input_value_id = p_iv_id
289 AND ((business_group_id IS NULL AND legislation_code = 'NO')
290 OR (business_group_id = g_business_group_id AND legislation_code IS NULL))
291 AND rownum = 1 ;
292
293 ----------------------------------------------------------------
294
295 /* Cursor to get the defined balance id */
296
297 CURSOR csr_def_bal_id (p_prim_bal_type_id NUMBER)IS
298 SELECT defined_balance_id
299 FROM pay_defined_balances
300 WHERE balance_type_id = p_prim_bal_type_id
301 AND ((business_group_id IS NULL AND legislation_code = 'NO')
302 OR (business_group_id = g_business_group_id AND legislation_code IS NULL))
303 AND balance_dimension_id = ( select balance_dimension_id
304 from pay_balance_dimensions where legislation_code = 'NO'
305 and dimension_name = 'Assignment Calendar Year To Date' ) ;
306
307 ---------------------------------------------------------------
308
309 /* cursor to get the sub classifications for an element type */
310
311 /*
312 CURSOR csr_sub_class (p_ele_type_id NUMBER , p_date_earned DATE) IS
313 SELECT eleclass.classification_name
314 ,decode (eleclass.classification_name,'Taxable Pay _ Absence',1,'Taxable Pay _ Earnings',1
315 ,'Taxable Pay _ Supplemental Earnings',1,'Taxable Pay _ Taxable Benefits',1,0) table_base
316 ,decode (eleclass.classification_name,'Additional Taxable Pay _ Absence',2,'Additional Taxable Pay _ Earnings',2
317 ,'Additional Taxable Pay _ Supplemental Earnings',2,'Additional Taxable Pay _ Taxable Benefits',2,0) percent_base
318 ,decode (eleclass.classification_name,'Holiday Pay',3,0) holiday_pay
319 FROM pay_sub_classification_rules_f subclass
320 ,pay_element_classifications eleclass
321
322 WHERE subclass.element_type_id = p_ele_type_id
323 AND p_date_earned BETWEEN subclass.effective_start_date AND subclass.effective_end_date
324 AND ((subclass.business_group_id IS NULL AND subclass.legislation_code = 'NO')
325 OR (subclass.business_group_id = g_business_group_id AND subclass.legislation_code IS NULL))
326 AND ((eleclass.business_group_id IS NULL AND eleclass.legislation_code = 'NO')
327 OR (eleclass.business_group_id = g_business_group_id AND eleclass.legislation_code IS NULL))
328 AND eleclass.classification_id = subclass.classification_id ;
329
330 */
331
332
333 CURSOR csr_sub_class (p_date_earned date , p_ele_type_id NUMBER , p_ele_sub_class_name pay_element_classifications.classification_name%type ) IS
334 SELECT 1
335 FROM pay_sub_classification_rules_f subclass
336 ,pay_element_classifications eleclass
337 WHERE subclass.element_type_id = p_ele_type_id
338 AND eleclass.classification_name = p_ele_sub_class_name
339 AND p_date_earned BETWEEN subclass.effective_start_date AND subclass.effective_end_date
340 AND ((subclass.business_group_id IS NULL AND subclass.legislation_code = 'NO')
341 OR (subclass.business_group_id = g_business_group_id AND subclass.legislation_code IS NULL))
342 AND ((eleclass.business_group_id IS NULL AND eleclass.legislation_code = 'NO')
343 OR (eleclass.business_group_id = g_business_group_id AND eleclass.legislation_code IS NULL))
344 AND eleclass.classification_id = subclass.classification_id ;
345
346
347 -- cursor to check if an element has a holiday pay secondary classification
348
349 /*
350 CURSOR csr_hol_sub_class (p_date_earned date , p_ele_type_id NUMBER ) IS
351 SELECT 'YES'
352 FROM pay_sub_classification_rules_f subclass
353 ,pay_element_classifications eleclass
354 WHERE subclass.element_type_id = p_ele_type_id
355 AND eleclass.classification_id = subclass.classification_id
356 AND eleclass.classification_name in ( 'Holiday Pay Base _ Absence',
357 'Holiday Pay Base _ Earnings',
358 'Holiday Pay Base _ Earnings Adjustment',
359 'Holiday Pay Base _ Holiday Pay Earnings Adjustment',
360 'Holiday Pay Base _ Supplementary Earnings')
361 AND p_date_earned BETWEEN subclass.effective_start_date AND subclass.effective_end_date
362 AND ((subclass.business_group_id IS NULL AND subclass.legislation_code = 'NO')
363 OR (subclass.business_group_id = g_business_group_id AND subclass.legislation_code IS NULL))
364 AND ((eleclass.business_group_id IS NULL AND eleclass.legislation_code = 'NO')
365 OR (eleclass.business_group_id = g_business_group_id AND eleclass.legislation_code IS NULL)) ;
366
367 */
368
369 /* Change for element classification name from 'Holiday Pay Earnings Adjustment' to 'Holiday Pay Earnings Adjust' */
370
371 /*
372 CURSOR csr_hol_sub_class (p_date_earned date , p_ele_type_id NUMBER ) IS
373 SELECT 'YES'
374 FROM pay_sub_classification_rules_f subclass
375 ,pay_element_classifications eleclass
376 WHERE subclass.element_type_id = p_ele_type_id
377 AND eleclass.classification_id = subclass.classification_id
378 AND eleclass.classification_name in ( 'Holiday Pay Base _ Absence',
379 'Holiday Pay Base _ Earnings',
380 'Holiday Pay Base _ Earnings Adjustment',
381 'Holiday Pay Base _ Holiday Pay Earnings Adjust',
382 'Holiday Pay Base _ Supplementary Earnings')
383 AND p_date_earned BETWEEN subclass.effective_start_date AND subclass.effective_end_date
384 AND ((subclass.business_group_id IS NULL AND subclass.legislation_code = 'NO')
385 OR (subclass.business_group_id = g_business_group_id AND subclass.legislation_code IS NULL))
386 AND ((eleclass.business_group_id IS NULL AND eleclass.legislation_code = 'NO')
387 OR (eleclass.business_group_id = g_business_group_id AND eleclass.legislation_code IS NULL)) ;
388
389 */
390
391 /* Sub classification added for Impact of Absence on Holiday Pay */
392 /*
393 CURSOR csr_hol_sub_class (p_date_earned date , p_ele_type_id NUMBER ) IS
394 SELECT 'YES'
395 FROM pay_sub_classification_rules_f subclass
396 ,pay_element_classifications eleclass
397 WHERE subclass.element_type_id = p_ele_type_id
398 AND eleclass.classification_id = subclass.classification_id
399 AND eleclass.classification_name in ( 'Holiday Pay Base _ Absence',
400 'Holiday Pay Base _ Earnings',
401 'Holiday Pay Base _ Earnings Adjustment',
402 'Holiday Pay Base _ Holiday Pay Earnings Adjust',
403 'Holiday Pay Base _ Supplementary Earnings',
404 'Holiday Pay Base During Absence _ Information')
405 AND p_date_earned BETWEEN subclass.effective_start_date AND subclass.effective_end_date
406 AND ((subclass.business_group_id IS NULL AND subclass.legislation_code = 'NO')
407 OR (subclass.business_group_id = g_business_group_id AND subclass.legislation_code IS NULL))
408 AND ((eleclass.business_group_id IS NULL AND eleclass.legislation_code = 'NO')
409 OR (eleclass.business_group_id = g_business_group_id AND eleclass.legislation_code IS NULL)) ;
410
411 */
412
413 -- The following classifications have been obsoleted and will no longer be used.
414 -- Holiday Pay Base _ Holiday Pay Earnings Adjust => Holiday Pay Base _ Holiday Pay Earnings Adjust Obsolete
415
416 CURSOR csr_hol_sub_class (p_date_earned date , p_ele_type_id NUMBER ) IS
417 SELECT 'YES'
418 FROM pay_sub_classification_rules_f subclass
419 ,pay_element_classifications eleclass
420 WHERE subclass.element_type_id = p_ele_type_id
421 AND eleclass.classification_id = subclass.classification_id
422 AND eleclass.classification_name in ( 'Holiday Pay Base _ Absence',
423 'Holiday Pay Base _ Earnings',
424 'Holiday Pay Base _ Earnings Adjustment',
425 'Holiday Pay Base _ Supplementary Earnings',
426 'Holiday Pay Base During Absence _ Information')
427 AND p_date_earned BETWEEN subclass.effective_start_date AND subclass.effective_end_date
428 AND ((subclass.business_group_id IS NULL AND subclass.legislation_code = 'NO')
429 OR (subclass.business_group_id = g_business_group_id AND subclass.legislation_code IS NULL))
430 AND ((eleclass.business_group_id IS NULL AND eleclass.legislation_code = 'NO')
431 OR (eleclass.business_group_id = g_business_group_id AND eleclass.legislation_code IS NULL)) ;
432
433 --------------------------------------------------------------
434
435
436 --------------
437
438 rec_time_periods csr_time_periods%ROWTYPE;
439 rec_get_balance csr_get_balance%ROWTYPE;
440 rec_get_message csr_get_message%ROWTYPE;
441 rec_get_element csr_get_element%ROWTYPE;
442 l_action_info_id NUMBER;
443 l_ovn NUMBER;
444 l_business_group_id NUMBER;
445 l_start_date VARCHAR2(30);
446 l_end_date VARCHAR2(30);
447 l_effective_date DATE;
448 l_consolidation_set NUMBER;
449 l_defined_balance_id NUMBER := 0;
450 l_count NUMBER := 0;
451 l_prev_prepay NUMBER := 0;
452 l_canonical_start_date DATE;
453 l_canonical_end_date DATE;
454 l_payroll_id NUMBER;
455 l_prepay_action_id NUMBER;
456 l_actid NUMBER;
457 l_assignment_id NUMBER;
458 l_action_sequence NUMBER;
459 l_assact_id NUMBER;
460 l_pact_id NUMBER;
461 l_flag NUMBER := 0;
462 l_element_context VARCHAR2(5);
463 l_prim_bal_type_id NUMBER;
464 l_prim_def_bal_id NUMBER ;
465 l_table_basis NUMBER;
466 l_percent_basis NUMBER;
467 l_holiday_basis NUMBER;
468 l_ele_class VARCHAR2(240);
469 l_basis_text VARCHAR2(240);
470 l_holiday_text VARCHAR2(240);
471
472 ----------------
473
474 BEGIN
475 -- fnd_file.put_line(fnd_file.log,'Entering Procedure RANGE_CODE');
476 IF g_debug THEN
477 hr_utility.set_location(' Entering Procedure RANGE_CODE',40);
478 END IF;
479
480 PAY_NO_PAYSLIP_ARCHIVE.GET_ALL_PARAMETERS(p_payroll_action_id
481 ,l_business_group_id
482 ,l_start_date
483 ,l_end_date
484 ,l_effective_date
485 ,l_payroll_id
486 ,l_consolidation_set);
487
488 l_canonical_start_date := TO_DATE(l_start_date,'YYYY/MM/DD');
489 l_canonical_end_date := TO_DATE(l_end_date,'YYYY/MM/DD');
490
491 -- get the messages from Busineess Group:Payslip Info
492 OPEN csr_get_message(l_business_group_id);
493 LOOP
494 FETCH csr_get_message INTO rec_get_message;
495 EXIT WHEN csr_get_message%NOTFOUND;
496
497 -- archive the messages
498 pay_action_information_api.create_action_information (
499 p_action_information_id => l_action_info_id
500 ,p_action_context_id => p_payroll_action_id
501 ,p_action_context_type => 'PA'
502 ,p_object_version_number => l_ovn
503 ,p_effective_date => l_effective_date
504 ,p_source_id => NULL
505 ,p_source_text => NULL
506 ,p_action_information_category => 'EMPLOYEE OTHER INFORMATION'
507 ,p_action_information1 => l_business_group_id
508 ,p_action_information2 => 'MESG' -- Message Context
509 ,p_action_information3 => NULL
510 ,p_action_information4 => NULL
511 ,p_action_information5 => NULL
512 ,p_action_information6 => rec_get_message.message);
513
514 END LOOP;
515 CLOSE csr_get_message;
516
517 -------------------------------------------------------------------------------------
518 -- Initialize Balance Definitions
519 -------------------------------------------------------------------------------------
520
521 -- get the balances from Busineess Group:Payslip Info
522 OPEN csr_get_balance(l_business_group_id);
523 LOOP
524 FETCH csr_get_balance INTO rec_get_balance;
525 EXIT WHEN csr_get_balance%NOTFOUND;
526
527 -- get the defined balance id for the balances got above
528 OPEN csr_def_balance(rec_get_balance.balance_type_id,rec_get_balance.balance_dim_id);
529 FETCH csr_def_balance INTO l_defined_balance_id;
530 CLOSE csr_def_balance;
531
532 BEGIN
533 -- check if the balance has already been archived
534 SELECT 1 INTO l_flag
535 FROM pay_action_information
536 WHERE action_information_category = 'EMEA BALANCE DEFINITION'
537 AND action_context_id = p_payroll_action_id
538 AND action_information2 = l_defined_balance_id
539 AND action_information6 = 'OBAL'
540 AND action_information4 = rec_get_balance.narrative;
541
542 EXCEPTION WHEN NO_DATA_FOUND THEN
543
544 -- archive the balance definition as it has not been archived before
545 pay_action_information_api.create_action_information (
546 p_action_information_id => l_action_info_id
547 ,p_action_context_id => p_payroll_action_id
548 ,p_action_context_type => 'PA'
549 ,p_object_version_number => l_ovn
550 ,p_effective_date => l_effective_date
551 ,p_source_id => NULL
552 ,p_source_text => NULL
553 ,p_action_information_category => 'EMEA BALANCE DEFINITION'
554 ,p_action_information1 => NULL
555 ,p_action_information2 => l_defined_balance_id
556 ,p_action_information4 => rec_get_balance.narrative
557 ,p_action_information6 => 'OBAL');
558
559 WHEN OTHERS THEN
560 NULL;
561 END;
562
563 END LOOP;
564 CLOSE csr_get_balance;
565
566 -----------------------------------------------------------------------------
567 --Initialize Element Definitions
568 -----------------------------------------------------------------------------
569
570 g_business_group_id := l_business_group_id;
571
572 ARCHIVE_ELEMENT_INFO(p_payroll_action_id => p_payroll_action_id
573 ,p_effective_date => l_effective_date
574 ,p_date_earned => l_canonical_end_date
575 ,p_pre_payact_id => NULL);
576
577 -----------------------------------------------------------------------------
578 --Archive Additional Element Definitions
579 -----------------------------------------------------------------------------
580
581 l_element_context := 'F';
582
583 OPEN csr_get_element(l_business_group_id,l_canonical_end_date);
584 LOOP
585 FETCH csr_get_element INTO rec_get_element;
586 EXIT WHEN csr_get_element%NOTFOUND;
587 BEGIN
588
589 l_prim_bal_type_id := NULL ;
590 l_prim_def_bal_id := NULL ;
591 l_ele_class := NULL ;
592 l_table_basis := NULL ;
593 l_percent_basis := NULL ;
594 l_holiday_basis := NULL ;
595 l_basis_text := NULL ;
596 l_holiday_text := NULL ;
597
598
599 /* get the primary balance type id from the input value id */
600 OPEN csr_prim_bal_type (rec_get_element.input_value_id);
601 FETCH csr_prim_bal_type INTO l_prim_bal_type_id ;
602 CLOSE csr_prim_bal_type ;
603
604 /* get the defined balance id from the balance type id */
605 OPEN csr_def_bal_id (l_prim_bal_type_id ) ;
606 FETCH csr_def_bal_id INTO l_prim_def_bal_id ;
607 CLOSE csr_def_bal_id ;
608
609 /* get the table/percetage basis info */
610
611 l_basis_text := NULL ;
612
613 IF ( rec_get_element.ele_class = 'Pre-tax Deductions' )
614
615 THEN l_basis_text := 'TABLE';
616
617 ELSIF ( rec_get_element.ele_class = 'Taxable Expenses' )
618
619 THEN l_basis_text := 'PERCENTAGE';
620
621 ELSIF ( rec_get_element.ele_class = 'Absence' )
622
623 THEN
624 OPEN csr_sub_class ( l_canonical_end_date , rec_get_element.element_type_id , 'Taxable Pay _ Absence') ;
625 FETCH csr_sub_class INTO l_table_basis ;
626 IF (csr_sub_class%FOUND)
627 THEN l_basis_text := 'TABLE';
628 END IF;
629 CLOSE csr_sub_class ;
630
631
632 OPEN csr_sub_class ( l_canonical_end_date , rec_get_element.element_type_id , 'Additional Taxable Pay _ Absence') ;
633 FETCH csr_sub_class INTO l_percent_basis ;
634 IF (csr_sub_class%FOUND)
635 THEN l_basis_text := 'PERCENTAGE';
636 END IF;
637 CLOSE csr_sub_class ;
638
639
640 ELSIF ( rec_get_element.ele_class = 'Earnings' )
641
642 THEN
643 OPEN csr_sub_class ( l_canonical_end_date , rec_get_element.element_type_id , 'Taxable Pay _ Earnings') ;
644 FETCH csr_sub_class INTO l_table_basis ;
645 IF (csr_sub_class%FOUND)
646 THEN l_basis_text := 'TABLE';
647 END IF;
648 CLOSE csr_sub_class ;
649
650
651 OPEN csr_sub_class ( l_canonical_end_date , rec_get_element.element_type_id , 'Additional Taxable Pay _ Earnings') ;
652 FETCH csr_sub_class INTO l_percent_basis ;
653 IF (csr_sub_class%FOUND)
654 THEN l_basis_text := 'PERCENTAGE';
655 END IF;
656 CLOSE csr_sub_class ;
657
658 ELSIF ( rec_get_element.ele_class = 'Supplementary Earnings' )
659
660 THEN
661 OPEN csr_sub_class ( l_canonical_end_date , rec_get_element.element_type_id , 'Taxable Pay _ Supplemental Earnings') ;
662 FETCH csr_sub_class INTO l_table_basis ;
663 IF (csr_sub_class%FOUND)
664 THEN l_basis_text := 'TABLE';
665 END IF;
666 CLOSE csr_sub_class ;
667
668
669 OPEN csr_sub_class ( l_canonical_end_date , rec_get_element.element_type_id , 'Additional Taxable Pay _ Supplemental Earnings') ;
670 FETCH csr_sub_class INTO l_percent_basis ;
671 IF (csr_sub_class%FOUND)
672 THEN l_basis_text := 'PERCENTAGE';
673 END IF;
674 CLOSE csr_sub_class ;
675
676 ELSIF ( rec_get_element.ele_class = 'Taxable Benefits' )
677
678 THEN
679 OPEN csr_sub_class ( l_canonical_end_date , rec_get_element.element_type_id , 'Taxable Pay _ Taxable Benefits') ;
680 FETCH csr_sub_class INTO l_table_basis ;
681 IF (csr_sub_class%FOUND)
682 THEN l_basis_text := 'TABLE';
683 END IF;
684 CLOSE csr_sub_class ;
685
686
687 OPEN csr_sub_class ( l_canonical_end_date , rec_get_element.element_type_id , 'Additional Taxable Pay _ Taxable Benefits') ;
688 FETCH csr_sub_class INTO l_percent_basis ;
689 IF (csr_sub_class%FOUND)
690 THEN l_basis_text := 'PERCENTAGE';
691 END IF;
692 CLOSE csr_sub_class ;
693
694 ELSIF ( rec_get_element.ele_class = 'Earnings Adjustment' )
695
696 THEN
697 OPEN csr_sub_class ( l_canonical_end_date , rec_get_element.element_type_id , 'Taxable Pay _ Earnings Adjustment') ;
698 FETCH csr_sub_class INTO l_table_basis ;
699 IF (csr_sub_class%FOUND)
700 THEN l_basis_text := 'TABLE';
701 END IF;
702 CLOSE csr_sub_class ;
703
704
705 OPEN csr_sub_class ( l_canonical_end_date , rec_get_element.element_type_id , 'Additional Taxable Pay _ Earnings Adjustment') ;
706 FETCH csr_sub_class INTO l_percent_basis ;
707 IF (csr_sub_class%FOUND)
708 THEN l_basis_text := 'PERCENTAGE';
709 END IF;
710 CLOSE csr_sub_class ;
711
712 /* Change for element classification name from 'Holiday Pay Earnings Adjustment' to 'Holiday Pay Earnings Adjust' */
713
714 -- The following classifications have been obsoleted and will no longer be used.
715 -- Holiday Pay Earnings Adjust => Holiday Pay Earnings Adjust Obsolete
716 -- Taxable Pay _ Holiday Pay Earnings Adjust => Taxable Pay _ Holiday Pay Earnings Adjust Obsolete
717 -- Additional Taxable Pay _ Holiday Pay Earnings Adjust => Additional Taxable Pay _ Holiday Pay Earnings Adjust Obsolete
718 -- Commenting the code below.
719
720 /*
721
722 -- ELSIF ( rec_get_element.ele_class = 'Holiday Pay Earnings Adjustment' )
723 ELSIF ( rec_get_element.ele_class = 'Holiday Pay Earnings Adjust' )
724
725 THEN
726 -- OPEN csr_sub_class ( l_canonical_end_date , rec_get_element.element_type_id , 'Taxable Pay _ Holiday Pay Earnings Adjustment') ;
727 OPEN csr_sub_class ( l_canonical_end_date , rec_get_element.element_type_id , 'Taxable Pay _ Holiday Pay Earnings Adjust') ;
728 FETCH csr_sub_class INTO l_table_basis ;
729 IF (csr_sub_class%FOUND)
730 THEN l_basis_text := 'TABLE';
731 END IF;
732 CLOSE csr_sub_class ;
733
734
735 -- OPEN csr_sub_class ( l_canonical_end_date , rec_get_element.element_type_id , 'Additional Taxable Pay _ Holiday Pay Earnings Adjustment') ;
736 OPEN csr_sub_class ( l_canonical_end_date , rec_get_element.element_type_id , 'Additional Taxable Pay _ Holiday Pay Earnings Adjust') ;
737 FETCH csr_sub_class INTO l_percent_basis ;
738 IF (csr_sub_class%FOUND)
739 THEN l_basis_text := 'PERCENTAGE';
740 END IF;
741 CLOSE csr_sub_class ;
742 */
743
744 END IF;
745
746
747 /*
748 OPEN csr_sub_class (rec_get_element.element_type_id , l_canonical_end_date) ;
749 FETCH csr_sub_class INTO l_ele_class ,l_table_basis ,l_percent_basis ,l_holiday_basis ;
750 CLOSE csr_sub_class ;
751
752
753 l_basis_text := NULL ;
754
755 IF (l_table_basis = 1) THEN l_basis_text := 'TABLE';
756 ELSIF (l_percent_basis = 2) THEN l_basis_text := 'PERCENTAGE';
757 END IF;
758
759 */
760
761 -- l_holiday_text := NULL ;
762
763 -- check if the element is basis for holiday pay
764
765 OPEN csr_hol_sub_class (l_canonical_end_date , rec_get_element.element_type_id) ;
766 FETCH csr_hol_sub_class INTO l_holiday_text ;
767 CLOSE csr_hol_sub_class ;
768
769 /* (IF (l_holiday_basis = 3) THEN l_holiday_text := 'Y';
770 END IF ; */
771
772 IF (l_basis_text IS NOT NULL) THEN
773 l_basis_text := hr_general.decode_lookup('NO_REPORT_LABELS',l_basis_text);
774 END IF;
775
776 IF (l_holiday_text IS NOT NULL) THEN
777 l_holiday_text := hr_general.decode_lookup('NO_REPORT_LABELS',l_holiday_text);
778 END IF;
779
780
781 -- check if the element definition has already been archived
782 SELECT 1 INTO l_flag
783 FROM pay_action_information
784 WHERE action_context_id = p_payroll_action_id
785 AND action_information_category = 'NO ELEMENT DEFINITION'
786 AND action_information2 = rec_get_element.element_type_id
787 AND action_information3 = rec_get_element.input_value_id
788 AND action_information5 = l_element_context;
789
790 EXCEPTION WHEN NO_DATA_FOUND THEN
791 -- archive the element definition since it has not been archived
792 pay_action_information_api.create_action_information (
793 p_action_information_id => l_action_info_id
794 ,p_action_context_id => p_payroll_action_id
795 ,p_action_context_type => 'PA'
796 ,p_object_version_number => l_ovn
797 ,p_effective_date => l_effective_date
798 ,p_source_id => NULL
799 ,p_source_text => NULL
800 ,p_action_information_category => 'NO ELEMENT DEFINITION'
801 ,p_action_information1 => NULL
802 ,p_action_information2 => rec_get_element.element_type_id
803 ,p_action_information3 => rec_get_element.input_value_id
804 ,p_action_information4 => rec_get_element.element_narrative
805 ,p_action_information5 => l_element_context
806 ,p_action_information6 => rec_get_element.uom
807 ,p_action_information7 => l_element_context
808 ,p_action_information8 => l_prim_def_bal_id
809 ,p_action_information9 => 'PBAL'
810 ,p_action_information10 => l_holiday_text
811 ,p_action_information11 => l_basis_text
812 ,p_action_information12 => rec_get_element.ele_class
813 ,p_action_information13 => rec_get_element.lookup_type
814 ,p_action_information14 => rec_get_element.value_set_id );
815
816 WHEN OTHERS THEN
817 NULL;
818 END;
819
820 END LOOP;
821 CLOSE csr_get_element;
822
823 p_sql := 'SELECT DISTINCT person_id
824 FROM per_people_f ppf
825 ,pay_payroll_actions ppa
826 WHERE ppa.payroll_action_id = :payroll_action_id
827 AND ppa.business_group_id = ppf.business_group_id
828 ORDER BY ppf.person_id';
829
830 IF g_debug THEN
831 hr_utility.set_location(' Leaving Procedure RANGE_CODE',50);
832 END IF;
833
834 -- fnd_file.put_line(fnd_file.log,'Leaving Procedure RANGE_CODE');
835
836 EXCEPTION
837 WHEN OTHERS THEN
838 -- Return cursor that selects no rows
839 p_sql := 'select 1 from dual where to_char(:payroll_action_id) = dummy';
840
841 END RANGE_CODE;
842
843 ---------------------------------- PROCEDURE ASSIGNMENT_ACTION_CODE ----------------------------------------------------------------
844
845 /* ASSIGNMENT ACTION CODE */
846 PROCEDURE ASSIGNMENT_ACTION_CODE
847 (p_payroll_action_id IN NUMBER
848 ,p_start_person IN NUMBER
849 ,p_end_person IN NUMBER
850 ,p_chunk IN NUMBER)
851 IS
852
853 -----------
854
855 CURSOR csr_prepaid_assignments(p_payroll_action_id NUMBER,
856 p_start_person NUMBER,
857 p_end_person NUMBER,
858 p_payroll_id NUMBER,
859 p_consolidation_id NUMBER,
860 l_canonical_start_date DATE,
861 l_canonical_end_date DATE)
862 IS
863 SELECT act.assignment_id assignment_id,
864 act.assignment_action_id run_action_id,
865 act1.assignment_action_id prepaid_action_id
866 FROM pay_payroll_actions ppa,
867 pay_payroll_actions appa,
868 pay_payroll_actions appa2,
869 pay_assignment_actions act,
870 pay_assignment_actions act1,
871 pay_action_interlocks pai,
872 per_all_assignments_f as1
873 WHERE ppa.payroll_action_id = p_payroll_action_id
874 AND appa.consolidation_set_id = p_consolidation_id
875 AND appa.effective_date BETWEEN l_canonical_start_date AND l_canonical_end_date
876 AND as1.person_id BETWEEN p_start_person AND p_end_person
877 AND appa.action_type IN ('R','Q') -- Payroll Run or Quickpay Run
878 AND act.payroll_action_id = appa.payroll_action_id
879 AND act.source_action_id IS NULL -- Master Action
880 AND as1.assignment_id = act.assignment_id
881 AND ppa.effective_date BETWEEN as1.effective_start_date AND as1.effective_end_date
882 AND act.action_status IN ('C','S') -- 10229512
883 AND act.assignment_action_id = pai.locked_action_id
884 AND act1.assignment_action_id = pai.locking_action_id
885 AND act1.action_status IN ('C','S') -- 10229512
886 AND act1.payroll_action_id = appa2.payroll_action_id
887 AND appa2.action_type IN ('P','U') -- Prepayments or Quickpay Prepayments
888 AND appa2.effective_date BETWEEN l_canonical_start_date AND l_canonical_end_date
889 AND (as1.payroll_id = p_payroll_id OR p_payroll_id IS NULL)
890
891 AND NOT EXISTS (SELECT /* + ORDERED */ NULL
892 FROM pay_action_interlocks pai1,
893 pay_assignment_actions act2,
894 pay_payroll_actions appa3
895 WHERE pai1.locked_action_id = act.assignment_action_id
896 AND act2.assignment_action_id= pai1.locking_action_id
897 AND act2.payroll_action_id = appa3.payroll_action_id
898 AND appa3.action_type = 'X'
899 AND appa3.action_status = 'C'
900 AND appa3.report_type = 'PYNOARCHIVE')
901
902 AND NOT EXISTS ( SELECT /* + ORDERED */ NULL
903 FROM pay_action_interlocks pai1,
904 pay_assignment_actions act2,
905 pay_payroll_actions appa3
906 WHERE pai1.locked_action_id = act.assignment_action_id
907 AND act2.assignment_action_id= pai1.locking_action_id
908 AND act2.payroll_action_id = appa3.payroll_action_id
909 AND appa3.action_type = 'V'
910 AND appa3.action_status = 'C')
911
912 ORDER BY act.assignment_id;
913
914 -----------
915
916 l_count NUMBER := 0;
917 l_prev_prepay NUMBER := 0;
918 l_business_group_id NUMBER;
919 l_start_date VARCHAR2(20);
920 l_end_date VARCHAR2(20);
921 l_canonical_start_date DATE;
922 l_canonical_end_date DATE;
923 l_effective_date DATE;
924 l_payroll_id NUMBER;
925 l_consolidation_set NUMBER;
926 l_prepay_action_id NUMBER;
927 l_actid NUMBER;
928 l_assignment_id NUMBER;
929 l_action_sequence NUMBER;
930 l_assact_id NUMBER;
931 l_pact_id NUMBER;
932 l_flag NUMBER := 0;
933 l_defined_balance_id NUMBER := 0;
934 l_action_info_id NUMBER;
935 l_ovn NUMBER;
936 ----------------
937
938 BEGIN
939
940 -- fnd_file.put_line(fnd_file.log,'Entering Procedure ASSIGNMENT_ACTION_CODE');
941 IF g_debug THEN
942 hr_utility.set_location(' Entering Procedure ASSIGNMENT_ACTION_CODE',60);
943 END IF;
944
945 PAY_NO_PAYSLIP_ARCHIVE.GET_ALL_PARAMETERS(p_payroll_action_id
946 ,l_business_group_id
947 ,l_start_date
948 ,l_end_date
949 ,l_effective_date
950 ,l_payroll_id
951 ,l_consolidation_set);
952
953 l_canonical_start_date := TO_DATE(l_start_date,'YYYY/MM/DD');
954 l_canonical_end_date := TO_DATE(l_end_date,'YYYY/MM/DD');
955 l_prepay_action_id := 0;
956
957 FOR rec_prepaid_assignments IN csr_prepaid_assignments(p_payroll_action_id
958 ,p_start_person
959 ,p_end_person
960 ,l_payroll_id
961 ,l_consolidation_set
962 ,l_canonical_start_date
963 ,l_canonical_end_date) LOOP
964
965 IF l_prepay_action_id <> rec_prepaid_assignments.prepaid_action_id THEN
966
967 SELECT pay_assignment_actions_s.NEXTVAL
968 INTO l_actid
969 FROM dual;
970 --
971 g_index_assact := g_index_assact + 1;
972 g_lock_table(g_index_assact).archive_assact_id := l_actid; /* For Element archival */
973
974 -- Create the archive assignment action
975 hr_nonrun_asact.insact(l_actid
976 ,rec_prepaid_assignments.assignment_id
977 ,p_payroll_action_id
978 ,p_chunk
979 ,NULL);
980 -- Create archive to prepayment assignment action interlock
981 --
982 hr_nonrun_asact.insint(l_actid,rec_prepaid_assignments.prepaid_action_id);
983 END IF;
984
985 -- create archive to master assignment action interlock
986 hr_nonrun_asact.insint(l_actid,rec_prepaid_assignments.run_action_id);
987 l_prepay_action_id := rec_prepaid_assignments.prepaid_action_id;
988
989 END LOOP;
990
991 IF g_debug THEN
992 hr_utility.set_location(' Leaving Procedure ASSIGNMENT_ACTION_CODE',70);
993 END IF;
994 -- fnd_file.put_line(fnd_file.log,'Leaving Procedure ASSIGNMENT_ACTION_CODE');
995
996 END ASSIGNMENT_ACTION_CODE;
997
998 ------------------------------------- PROCEDURE INITIALIZATION_CODE -------------------------------------------------------------
999
1000 /* INITIALIZATION CODE */
1001
1002 PROCEDURE INITIALIZATION_CODE(p_payroll_action_id IN NUMBER)
1003 IS
1004
1005 -------------
1006
1007 CURSOR csr_prepay_id IS
1008 SELECT distinct prepay_payact.payroll_action_id prepay_payact_id
1009 ,run_payact.date_earned date_earned
1010 FROM pay_action_interlocks archive_intlck
1011 ,pay_assignment_actions prepay_assact
1012 ,pay_payroll_actions prepay_payact
1013 ,pay_action_interlocks prepay_intlck
1014 ,pay_assignment_actions run_assact
1015 ,pay_payroll_actions run_payact
1016 ,pay_assignment_actions archive_assact
1017 WHERE archive_intlck.locking_action_id = archive_assact.assignment_action_id
1018 and archive_assact.payroll_action_id = p_payroll_action_id
1019 AND prepay_assact.assignment_action_id = archive_intlck.locked_action_id
1020 AND prepay_payact.payroll_action_id = prepay_assact.payroll_action_id
1021 AND prepay_payact.action_type IN ('U','P')
1022 AND prepay_intlck.locking_action_id = prepay_assact.assignment_action_id
1023 AND run_assact.assignment_action_id = prepay_intlck.locked_action_id
1024 AND run_payact.payroll_action_id = run_assact.payroll_action_id
1025 AND run_payact.action_type IN ('Q', 'R')
1026 ORDER BY prepay_payact.payroll_action_id;
1027
1028 --------------
1029
1030 /* Cursor to retrieve Run Assignment Action Ids */
1031 CURSOR csr_runact_id IS
1032 SELECT distinct prepay_payact.payroll_action_id prepay_payact_id
1033 ,run_payact.date_earned date_earned
1034 ,run_payact.payroll_action_id run_payact_id
1035 FROM pay_action_interlocks archive_intlck
1036 ,pay_assignment_actions prepay_assact
1037 ,pay_payroll_actions prepay_payact
1038 ,pay_action_interlocks prepay_intlck
1039 ,pay_assignment_actions run_assact
1040 ,pay_payroll_actions run_payact
1041 ,pay_assignment_actions archive_assact
1042 WHERE archive_intlck.locking_action_id = archive_assact.assignment_action_id
1043 and archive_assact.payroll_action_id = p_payroll_action_id
1044 AND prepay_assact.assignment_action_id = archive_intlck.locked_action_id
1045 AND prepay_payact.payroll_action_id = prepay_assact.payroll_action_id
1046 AND prepay_payact.action_type IN ('U','P')
1047 AND prepay_intlck.locking_action_id = prepay_assact.assignment_action_id
1048 AND run_assact.assignment_action_id = prepay_intlck.locked_action_id
1049 AND run_payact.payroll_action_id = run_assact.payroll_action_id
1050 AND run_payact.action_type IN ('Q', 'R')
1051 ORDER BY prepay_payact.payroll_action_id;
1052
1053 -------------
1054
1055 rec_prepay_id csr_prepay_id%ROWTYPE;
1056 rec_runact_id csr_runact_id%ROWTYPE;
1057 l_action_info_id NUMBER;
1058 l_ovn NUMBER;
1059 l_count NUMBER := 0;
1060 l_business_group_id NUMBER;
1061 l_start_date VARCHAR2(20);
1062 l_end_date VARCHAR2(20);
1063 l_effective_date DATE;
1064 l_payroll_id NUMBER;
1065 l_consolidation_set NUMBER;
1066 l_prev_prepay NUMBER := 0;
1067
1068 ---------------
1069
1070 BEGIN
1071
1072 -- fnd_file.put_line(fnd_file.log,'Entering Procedure INITIALIZATION_CODE');
1073
1074 IF g_debug THEN
1075 hr_utility.set_location(' Entering Procedure INITIALIZATION_CODE',80);
1076 END IF;
1077
1078 /*fnd_file.put_line(fnd_file.log,'In INIT_CODE 0');*/
1079
1080 GET_ALL_PARAMETERS(p_payroll_action_id
1081 ,l_business_group_id
1082 ,l_start_date
1083 ,l_end_date
1084 ,l_effective_date
1085 ,l_payroll_id
1086 ,l_consolidation_set);
1087
1088 g_arc_payroll_action_id := p_payroll_action_id;
1089 g_business_group_id := l_business_group_id;
1090
1091 /* Archive Element Details */
1092 OPEN csr_prepay_id;
1093 LOOP
1094 FETCH csr_prepay_id INTO rec_prepay_id;
1095 EXIT WHEN csr_prepay_id%NOTFOUND;
1096 ---------------------------------------------------------
1097 --Initialize Global tables once every prepayment payroll
1098 --action id and once every thread
1099 ---------------------------------------------------------
1100 IF (rec_prepay_id.prepay_payact_id <> l_prev_prepay) THEN
1101 ARCHIVE_ADD_ELEMENT(p_archive_assact_id => NULL,
1102 p_assignment_action_id => NULL,
1103 p_assignment_id => NULL,
1104 p_payroll_action_id => p_payroll_action_id,
1105 p_date_earned => rec_prepay_id.date_earned,
1106 p_effective_date => l_effective_date,
1107 p_pre_payact_id => rec_prepay_id.prepay_payact_id,
1108 p_archive_flag => 'N');
1109
1110 END IF;
1111
1112 l_prev_prepay := rec_prepay_id.prepay_payact_id;
1113 END LOOP;
1114
1115 CLOSE csr_prepay_id;
1116
1117 /* Initialize Global tables for Balances */
1118 ARCHIVE_OTH_BALANCE(p_archive_assact_id => NULL,
1119 p_assignment_action_id => NULL,
1120 p_assignment_id => NULL,
1121 p_payroll_action_id => p_payroll_action_id,
1122 p_record_count => NULL,
1123 p_pre_payact_id => NULL, --rec_prepay_id.prepay_payact_id,
1124 p_effective_date => l_effective_date,
1125 p_date_earned => NULL,
1126 p_archive_flag => 'N');
1127
1128 IF g_debug THEN
1129 hr_utility.set_location(' Leaving Procedure INITIALIZATION_CODE',90);
1130 END IF;
1131
1132 -- fnd_file.put_line(fnd_file.log,'Leaving Procedure INITIALIZATION_CODE');
1133
1134
1135 EXCEPTION WHEN OTHERS THEN
1136 g_err_num := SQLCODE;
1137 -- fnd_file.put_line(fnd_file.log,'ORA_ERR: ' || g_err_num || 'In INITIALIZATION_CODE');
1138
1139 IF g_debug THEN
1140 hr_utility.set_location('ORA_ERR: ' || g_err_num || 'In INITIALIZATION_CODE',180);
1141 END IF;
1142
1143 -- fnd_file.put_line(fnd_file.log,'Leaving Procedure INITIALIZATION_CODE');
1144
1145 END INITIALIZATION_CODE;
1146
1147 ------------------------------------- PROCEDURE SETUP_ELEMENT_DEFINITIONS -------------------------------------------------------------
1148
1149 PROCEDURE SETUP_ELEMENT_DEFINITIONS( p_classification_name IN VARCHAR2
1150 ,p_element_name IN VARCHAR2
1151 ,p_element_type_id IN NUMBER
1152 ,p_input_value_id IN NUMBER
1153 ,p_element_type IN VARCHAR2
1154 ,p_uom IN VARCHAR2
1155 ,p_archive_flag IN VARCHAR2
1156 ,p_prim_bal_def_bal_id IN NUMBER
1157 ,p_lookup_type IN VARCHAR2
1158 ,p_value_set_id IN NUMBER )
1159 IS
1160
1161 BEGIN
1162
1163 IF g_debug THEN
1164 hr_utility.set_location(' Entering Procedure SETUP_ELEMENT_DEFINITIONS',100);
1165 END IF;
1166
1167 g_index := g_index + 1;
1168 /* Initialize global tables that hold Additional Element details */
1169 g_element_table(g_index).classification_name := p_classification_name;
1170 g_element_table(g_index).element_name := p_element_name;
1171 g_element_table(g_index).element_type := p_element_type;
1172 g_element_table(g_index).element_type_id := p_element_type_id;
1173 g_element_table(g_index).input_value_id := p_input_value_id;
1174 g_element_table(g_index).uom := p_uom;
1175 g_element_table(g_index).archive_flag := p_archive_flag;
1176 g_element_table(g_index).prim_bal_def_bal_id := p_prim_bal_def_bal_id ;
1177 -- new added
1178 g_element_table(g_index).lookup_type := p_lookup_type ;
1179 g_element_table(g_index).value_set_id := p_value_set_id ;
1180
1181
1182 IF g_debug THEN
1183 hr_utility.set_location(' Leaving Procedure SETUP_ELEMENT_DEFINITIONS',110);
1184 END IF;
1185
1186 END SETUP_ELEMENT_DEFINITIONS;
1187
1188 ------------------------------------ PROCEDURE SETUP_BALANCE_DEFINITIONS --------------------------------------------------------------
1189
1190 PROCEDURE SETUP_BALANCE_DEFINITIONS(p_balance_name IN VARCHAR2
1191 ,p_defined_balance_id IN NUMBER
1192 ,p_balance_type_id IN NUMBER)
1193 IS
1194 BEGIN
1195
1196 IF g_debug THEN
1197 hr_utility.set_location(' Entering Procedure SETUP_BALANCE_DEFINITIONS',120);
1198 END IF;
1199
1200 g_index_bal := g_index_bal + 1;
1201 /* Initialize global tables that hold Other Balances details */
1202 g_user_balance_table(g_index_bal).balance_name := p_balance_name;
1203 g_user_balance_table(g_index_bal).defined_balance_id := p_defined_balance_id;
1204 g_user_balance_table(g_index_bal).balance_type_id := p_balance_type_id;
1205
1206 --fnd_file.put_line(fnd_file.log,'SETUP_BALANCE_DEFINITIONS ' ||p_balance_name);
1207
1208 IF g_debug THEN
1209 hr_utility.set_location(' Leaving Procedure SETUP_BALANCE_DEFINITIONS',130);
1210 END IF;
1211
1212 END SETUP_BALANCE_DEFINITIONS;
1213
1214 ------------------------------------ FUNCTION GET_COUNTRY_NAME --------------------------------------------------------------
1215
1216 /* GET COUNTRY NAME FROM CODE */
1217
1218 FUNCTION GET_COUNTRY_NAME(p_territory_code VARCHAR2)
1219 RETURN VARCHAR2
1220 IS
1221
1222 CURSOR csr_get_territory_name(p_territory_code VARCHAR2) Is
1223 SELECT territory_short_name
1224 FROM fnd_territories_vl
1225 WHERE territory_code = p_territory_code;
1226
1227 l_country fnd_territories_vl.territory_short_name%TYPE;
1228
1229 BEGIN
1230
1231 IF g_debug THEN
1232 hr_utility.set_location(' Entering Function GET_COUNTRY_NAME',140);
1233 END IF;
1234
1235 OPEN csr_get_territory_name(p_territory_code);
1236 FETCH csr_get_territory_name into l_country;
1237 CLOSE csr_get_territory_name;
1238
1239 RETURN l_country;
1240
1241 IF g_debug THEN
1242 hr_utility.set_location(' Leaving Function GET_COUNTRY_NAME',150);
1243 END IF;
1244
1245 END GET_COUNTRY_NAME;
1246
1247 --------------------------------------- PROCEDURE ARCHIVE_EMPLOYEE_DETAILS -----------------------------------------------------------
1248
1249 /* EMPLOYEE DETAILS REGION */
1250
1251 PROCEDURE ARCHIVE_EMPLOYEE_DETAILS (p_archive_assact_id IN NUMBER
1252 ,p_assignment_id IN NUMBER
1253 ,p_assignment_action_id IN NUMBER
1254 ,p_payroll_action_id IN NUMBER
1255 ,p_time_period_id IN NUMBER
1256 ,p_date_earned IN DATE
1257 ,p_pay_date_earned IN DATE
1258 ,p_effective_date IN DATE) IS
1259
1260 -------------
1261 /* Cursor to retrieve person details about Employee */
1262 CURSOR csr_person_details(p_assignment_id NUMBER) IS
1263 SELECT ppf.person_id person_id,
1264 ppf.full_name full_name,
1265 ppf.national_identifier ni_number,
1266 ppf.nationality nationality,
1267 pps.date_start start_date,
1268 ppf.employee_number emp_num,
1269 ppf.first_name first_name,
1270 ppf.last_name last_name,
1271 ppf.title title,
1272 paf.location_id loc_id,
1273 paf.organization_id org_id, -- HR Org at Asg level
1274 paf.job_id job_id,
1275 paf.position_id pos_id,
1276 paf.grade_id grade_id,
1277 paf.business_group_id bus_grp_id,
1278 paf.assignment_number asg_num
1279 FROM per_assignments_f paf,
1280 per_all_people_f ppf,
1281 per_periods_of_service pps
1282 WHERE paf.person_id = ppf.person_id
1283 AND paf.assignment_id = p_assignment_id
1284 AND pps.person_id = ppf.person_id
1285 AND p_date_earned BETWEEN paf.effective_start_date AND paf.effective_end_date
1286 AND p_date_earned BETWEEN ppf.effective_start_date AND ppf.effective_end_date;
1287
1288 -------------
1289 /* Cursor to retrieve primary address of Employee */
1290 CURSOR csr_primary_address(p_person_id NUMBER) IS
1291 SELECT pa.person_id person_id,
1292 pa.style style,
1293 pa.address_type ad_type,
1294 pa.country country,
1295 pa.region_1 R1,
1296 pa.region_2 R2,
1297 pa.region_3 R3,
1298 pa.town_or_city city,
1299 pa.address_line1 AL1,
1300 pa.address_line2 AL2,
1301 pa.address_line3 AL3,
1302 pa.postal_code postal_code
1303 FROM per_addresses pa
1304 WHERE pa.primary_flag = 'Y'
1305 AND pa.person_id = p_person_id
1306 AND p_effective_date BETWEEN pa.date_from AND NVL(pa.date_to,to_date('31-12-4712','DD-MM-YYYY'));
1307
1308 -------------
1309 /* Cursor to retrieve Employer's Address */
1310 CURSOR csr_employer_address(p_organization_id NUMBER) IS
1311 SELECT hla.style style
1312 ,hla.country country
1313 ,hla.address_line_1 AL1
1314 ,hla.address_line_2 AL2
1315 ,hla.address_line_3 AL3
1316 ,hla.postal_code postal_code
1317 FROM hr_locations_all hla
1318 ,hr_organization_units hou
1319 WHERE hou.organization_id = p_organization_id
1320 AND hou.location_id = hla.location_id;
1321 -------------
1322 CURSOR csr_organization_address(p_organization_id NUMBER) IS
1323 SELECT hla.style style
1324 ,hla.address_line_1 AL1
1325 ,hla.address_line_2 AL2
1326 ,hla.address_line_3 AL3
1327 ,hla.country country
1328 ,hla.postal_code postal_code
1329 FROM hr_locations_all hla,
1330 hr_organization_units hoa
1331 WHERE hla.location_id = hoa.location_id
1332 AND hoa.organization_id = p_organization_id
1333 AND p_effective_date BETWEEN hoa.date_from AND NVL(hoa.date_to,to_date('31-12-4712','DD-MM-YYYY'));
1334
1335 --------------
1336 /* Cursor to retrieve Business Group Id */
1337 CURSOR csr_bus_grp_id(p_organization_id NUMBER) IS
1338 SELECT business_group_id
1339 FROM hr_organization_units
1340 WHERE organization_id = p_organization_id;
1341 --------------
1342 /* Cursor to retrieve Currency */
1343 CURSOR csr_currency(p_bg_id NUMBER) IS
1344 SELECT org_information10
1345 FROM hr_organization_information
1346 WHERE organization_id = p_bg_id
1347 AND org_information_context = 'Business Group Information';
1348
1349 --------------
1350 l_bg_id NUMBER;
1351 --------------
1352
1353 /* Cursor to get the Legal Employer from Local Unit */
1354
1355 CURSOR csr_legal_employer (p_organization_id NUMBER) IS
1356 SELECT hoi3.organization_id
1357 FROM HR_ORGANIZATION_UNITS o1
1358 , HR_ORGANIZATION_INFORMATION hoi1
1359 , HR_ORGANIZATION_INFORMATION hoi2
1360 , HR_ORGANIZATION_INFORMATION hoi3
1361 WHERE o1.business_group_id =l_bg_id
1362 AND hoi1.organization_id = o1.organization_id
1363 AND hoi1.organization_id = p_organization_id
1364 AND hoi1.org_information1 = 'NO_LOCAL_UNIT'
1365 AND hoi1.org_information_context = 'CLASS'
1366 AND o1.organization_id = hoi2.org_information1
1367 AND hoi2.ORG_INFORMATION_CONTEXT='NO_LOCAL_UNITS'
1368 AND hoi2.organization_id = hoi3.organization_id
1369 AND hoi3.ORG_INFORMATION_CONTEXT='CLASS'
1370 AND hoi3.org_information1 = 'HR_LEGAL_EMPLOYER';
1371
1372
1373 -------------
1374 /* Cursor to retrieve Grade of Employee */
1375 CURSOR csr_grade(p_grade_id NUMBER) IS
1376 SELECT pg.name
1377 FROM per_grades pg
1378 WHERE pg.grade_id = p_grade_id;
1379 -------------
1380 /* Cursor to retrieve Position of Employee */
1381 CURSOR csr_position(p_position_id NUMBER) IS
1382 SELECT pap.name
1383 FROM per_all_positions pap
1384 WHERE pap.position_id = p_position_id;
1385 -------------
1386 CURSOR csr_job (p_job_id NUMBER)IS
1387 SELECT name
1388 FROM per_jobs
1389 WHERE job_id = p_job_id;
1390 -------------
1391 /* Cursor to retrieve Cost Center */
1392 CURSOR csr_cost_center(p_assignment_id NUMBER) IS
1393 SELECT concatenated_segments
1394 FROM pay_cost_allocations_v
1395 WHERE assignment_id=p_assignment_id
1396 AND p_date_earned BETWEEN effective_start_date AND effective_end_date;
1397 -------------
1398 /* Cursor to pick up Payroll Location */
1399 CURSOR csr_pay_location(p_location_id NUMBER) IS
1400 SELECT location_code location
1401 FROM hr_locations_all
1402 WHERE location_id = p_location_id;
1403 -------------
1404 /* Cursor to pick Hire Date*/
1405 CURSOR csr_hire_date (p_assignment_id NUMBER) IS
1406 SELECT date_start
1407 FROM per_periods_of_service pps,
1408 per_all_assignments_f paa
1409 WHERE pps.period_of_service_id = paa.period_of_service_id
1410 AND p_date_earned between paa.effective_start_date and paa.effective_end_date
1411 AND paa.assignment_id = p_assignment_id;
1412 -------------
1413 /*Cursor to pick local unit*/
1414
1415 cursor csr_scl_details (p_assignment_id NUMBER) IS
1416 SELECT segment2
1417 from per_all_assignments_f paaf
1418 ,HR_SOFT_CODING_KEYFLEX hsck
1419 where paaf.assignment_id= p_assignment_id
1420 and p_date_earned BETWEEN paaf.effective_start_date and paaf.effective_end_date
1421 and paaf.SOFT_CODING_KEYFLEX_ID = hsck.SOFT_CODING_KEYFLEX_ID;
1422
1423 --------------
1424
1425 /* Ccursor to get the Legal Employer Org Number */
1426 CURSOR csr_le_org_num (l_legal_employer_id NUMBER) IS
1427 select ORG_INFORMATION1
1428 from hr_organization_information
1429 where organization_id = l_legal_employer_id
1430 and ORG_INFORMATION_CONTEXT = 'NO_LEGAL_EMPLOYER_DETAILS' ;
1431
1432
1433 /* Cursor to get the Org name */
1434 CURSOR csr_org_name (p_org_name hr_organization_units.name%type ) IS
1435 SELECT name
1436 FROM hr_organization_units
1437 WHERE organization_id = p_org_name ;
1438
1439
1440 /* cursor to get the primary assignment id */
1441
1442 CURSOR csr_get_prim_asg (p_date_earned DATE , p_asg_id NUMBER) IS
1443 SELECT asg2.assignment_id
1444 FROM per_all_assignments_f asg1
1445 ,per_all_assignments_f asg2
1446 WHERE asg1.assignment_id = p_asg_id
1447 AND asg1.person_id = asg2.person_id
1448 AND asg2.primary_flag = 'Y'
1449 AND p_date_earned BETWEEN asg1.effective_start_date AND asg1.effective_end_date
1450 AND p_date_earned BETWEEN asg2.effective_start_date AND asg2.effective_end_date ;
1451 /* bug 16097213 */
1452 CURSOR csr_reg_pay_date (p_time_period_id number) IS
1453 select REGULAR_PAYMENT_DATE
1454 from per_time_periods
1455 where time_period_id = p_time_period_id;
1456
1457 l_reg_payment_date per_time_periods.regular_payment_date%type;
1458
1459 -------------
1460 rec_person_details csr_person_details%ROWTYPE;
1461 rec_primary_address csr_primary_address%ROWTYPE;
1462 rec_employer_address csr_employer_address%ROWTYPE;
1463 rec_org_address csr_organization_address%ROWTYPE;
1464 l_nationality per_all_people_f.nationality%TYPE;
1465 l_position per_all_positions.name%TYPE;
1466 l_hire_date per_periods_of_service.date_start%TYPE;
1467 l_grade per_grades.name%TYPE;
1468 l_currency hr_organization_information.org_information10%TYPE;
1469 l_organization hr_organization_units.name%TYPE;
1470 l_pay_location hr_locations_all.address_line_1%TYPE;
1471 l_postal_code VARCHAR2(80);
1472 l_country VARCHAR2(30);
1473 l_emp_postal_code VARCHAR2(80);
1474 l_emp_country VARCHAR2(30);
1475 l_org_city VARCHAR2(20);
1476 l_org_country VARCHAR2(30);
1477 l_action_info_id NUMBER;
1478 l_ovn NUMBER;
1479 l_person_id NUMBER;
1480 l_employer_name hr_organization_units.name%TYPE;
1481 l_local_unit_id hr_organization_units.organization_id%TYPE;
1482 l_legal_employer_id hr_organization_units.organization_id%TYPE;
1483 l_job PER_JOBS.NAME%TYPE;
1484 l_org_struct_ver_id hr_organization_information.org_information1%TYPE;
1485 l_top_org_id per_org_structure_elements.organization_id_parent%TYPE;
1486 l_cost_center pay_cost_allocations_v.concatenated_segments%TYPE;
1487 l_defined_balance_id NUMBER;
1488 l_balance_value NUMBER;
1489 l_formatted_value VARCHAR2(50) := NULL;
1490 l_org_exists NUMBER :=0;
1491 le_phone_num VARCHAR2(240);
1492 le_phone_num_str VARCHAR2(1000);
1493 l_cvr_num VARCHAR2(240);
1494 l_le_org_num VARCHAR2(240);
1495
1496 l_prim_asg_id NUMBER;
1497 l_prim_local_unit NUMBER;
1498 l_prim_legal_emp NUMBER;
1499 l_diff_le_text VARCHAR2(2);
1500 l_msg_txt VARCHAR2(240);
1501
1502 -- l_lower_base NUMBER :=0;
1503 -- l_upper_base NUMBER :=0;
1504 -------------
1505
1506 BEGIN
1507
1508 IF g_debug THEN
1509 hr_utility.set_location(' Entering Procedure ARCHIVE_EMPLOYEE_DETAILS',160);
1510 END IF;
1511
1512 /*fnd_file.put_line(fnd_file.log,'Entering Procedure ARCHIVE_EMPLOYEE_DETAILS');*/
1513
1514 /* PERSON AND ADDRESS DETAILS */
1515 OPEN csr_person_details(p_assignment_id);
1516 FETCH csr_person_details INTO rec_person_details;
1517 CLOSE csr_person_details;
1518
1519 -- fnd_file.put_line(fnd_file.log,'after cursor csr_person_details ');
1520
1521 OPEN csr_primary_address(rec_person_details.person_id);
1522 FETCH csr_primary_address INTO rec_primary_address;
1523 CLOSE csr_primary_address;
1524
1525 -- fnd_file.put_line(fnd_file.log,'after cursor csr_primary_address ');
1526
1527 -- rec_person_details.org_id is the org_id of the HR org at asg level
1528
1529 OPEN csr_organization_address(rec_person_details.org_id);
1530 FETCH csr_organization_address INTO rec_org_address;
1531 CLOSE csr_organization_address;
1532
1533 -- fnd_file.put_line(fnd_file.log,'after cursor csr_organization_address ');
1534
1535 /*fnd_file.put_line(fnd_file.log,'Entering Procedure ARCHIVE_EMPLOYEE_DETAILS 2');*/
1536
1537 /* GRADE AND POSITION */
1538
1539 /* Changed IF condition construct */
1540 IF(rec_person_details.pos_id IS NOT NULL) THEN
1541 OPEN csr_position(rec_person_details.pos_id);
1542 FETCH csr_position INTO l_position;
1543 CLOSE csr_position;
1544 END IF;
1545
1546 IF(rec_person_details.grade_id IS NOT NULL) THEN
1547 OPEN csr_grade(rec_person_details.grade_id);
1548 FETCH csr_grade INTO l_grade;
1549 CLOSE csr_grade;
1550 END IF;
1551
1552 /*fnd_file.put_line(fnd_file.log,'Entering Procedure ARCHIVE_EMPLOYEE_DETAILS 3');*/
1553
1554 /* CURRENCY */
1555
1556
1557 -- rec_person_details.org_id is the org_id of the HR org at asg level
1558 OPEN csr_bus_grp_id(rec_person_details.org_id);
1559 FETCH csr_bus_grp_id INTO l_bg_id;
1560 CLOSE csr_bus_grp_id;
1561
1562 OPEN csr_currency(l_bg_id);
1563 FETCH csr_currency INTO l_currency;
1564 CLOSE csr_currency;
1565
1566 g_format_mask := FND_CURRENCY.GET_FORMAT_MASK(l_currency,40);
1567
1568 /* COST CENTER */
1569 OPEN csr_cost_center(p_assignment_id);
1570 FETCH csr_cost_center INTO l_cost_center;
1571 CLOSE csr_cost_center;
1572
1573
1574 /* HIRE DATE */
1575 OPEN csr_hire_date(p_assignment_id);
1576 FETCH csr_hire_date INTO l_hire_date;
1577 CLOSE csr_hire_date;
1578
1579 /*NATIONALITY*/
1580 l_nationality := hr_general.decode_lookup('NATIONALITY',rec_person_details.nationality);
1581
1582 /*fnd_file.put_line(fnd_file.log,'Entering Procedure ARCHIVE_EMPLOYEE_DETAILS 4');*/
1583
1584 /*Local Unit*/
1585
1586 OPEN csr_scl_details(p_assignment_id);
1587 FETCH csr_scl_details INTO l_local_unit_id;
1588 CLOSE csr_scl_details;
1589
1590
1591 /* Getting Legal Employer */
1592
1593 OPEN csr_legal_employer(l_local_unit_id);
1594 FETCH csr_legal_employer INTO l_legal_employer_id;
1595 CLOSE csr_legal_employer;
1596
1597 /*Legal Employer */
1598 /*
1599 OPEN csr_scl_details(p_assignment_id);
1600 FETCH csr_scl_details INTO l_legal_employer_id ;
1601 CLOSE csr_scl_details;
1602 */
1603
1604 OPEN csr_employer_address(l_legal_employer_id);
1605 FETCH csr_employer_address INTO rec_employer_address;
1606 CLOSE csr_employer_address;
1607
1608 IF(rec_person_details.loc_id IS NOT NULL) THEN
1609 l_pay_location := NULL;
1610
1611 OPEN csr_pay_location(rec_person_details.loc_id);
1612 FETCH csr_pay_location INTO l_pay_location;
1613 CLOSE csr_pay_location;
1614 ELSE
1615 l_pay_location := NULL;
1616 END IF;
1617
1618
1619 IF(rec_person_details.job_id IS NOT NULL) THEN
1620
1621 OPEN csr_job(rec_person_details.job_id);
1622 FETCH csr_job INTO l_job;
1623 CLOSE csr_job;
1624 ELSE
1625 l_job := NULL;
1626 END IF;
1627
1628 -- HR ORG at asg level Name
1629 OPEN csr_org_name (rec_person_details.org_id) ;
1630 FETCH csr_org_name INTO l_organization ;
1631 CLOSE csr_org_name ;
1632
1633 -- Legal Employer Name
1634 OPEN csr_org_name (l_legal_employer_id) ;
1635 FETCH csr_org_name INTO l_employer_name ;
1636 CLOSE csr_org_name ;
1637
1638
1639 /*fnd_file.put_line(fnd_file.log,'Entering Procedure ARCHIVE_EMPLOYEE_DETAILS 5');*/
1640
1641 IF rec_primary_address.style = 'NO' THEN
1642 l_postal_code := hr_general.decode_lookup('NO_POSTAL_CODE',rec_primary_address.postal_code);
1643 ELSE
1644 l_postal_code := rec_primary_address.postal_code;
1645 END IF;
1646
1647 l_country := PAY_NO_PAYSLIP_ARCHIVE.get_country_name(rec_primary_address.country);
1648
1649 IF rec_employer_address.style = 'NO' THEN
1650 l_emp_postal_code := hr_general.decode_lookup('NO_POSTAL_CODE',rec_employer_address.postal_code);
1651 ELSE
1652 l_emp_postal_code := rec_employer_address.postal_code;
1653 END IF;
1654
1655 l_emp_country := PAY_NO_PAYSLIP_ARCHIVE.get_country_name(rec_employer_address.country);
1656
1657 /* Getting Legal Employer Phone Number String */
1658
1659 le_phone_num_str := NULL;
1660
1661
1662 /* Get Legal Employer Org Number */
1663
1664 OPEN csr_le_org_num (l_legal_employer_id ) ;
1665 FETCH csr_le_org_num INTO l_le_org_num ;
1666 CLOSE csr_le_org_num ;
1667
1668 /* Archive a message if the current Legal Employer is different from the Legal Employer of the Primary Assignment */
1669
1670 l_diff_le_text := 'N' ;
1671
1672 /* get the primary assignment id */
1673 OPEN csr_get_prim_asg (p_date_earned , p_assignment_id );
1674 FETCH csr_get_prim_asg INTO l_prim_asg_id ;
1675 CLOSE csr_get_prim_asg ;
1676
1677 IF ( p_assignment_id <> l_prim_asg_id )
1678 THEN
1679
1680 /* the Local Unit for the current assignment = l_local_unit_id */
1681
1682 /* OPEN csr_scl_details (p_assignment_id ) ;
1683 FETCH csr_scl_details INTO l_local_unit ;
1684 CLOSE csr_scl_details ; */
1685
1686 /* get the Local Unit for the primary assignment */
1687 OPEN csr_scl_details (l_prim_asg_id ) ;
1688 FETCH csr_scl_details INTO l_prim_local_unit ;
1689 CLOSE csr_scl_details ;
1690
1691 IF (l_local_unit_id <> l_prim_local_unit)
1692 THEN
1693
1694 /* the Legal Employer for the current assignment = l_legal_employer_id */
1695
1696 /* OPEN csr_legal_employer (l_local_unit ) ;
1697 FETCH csr_legal_employer INTO l_legal_emp ;
1698 CLOSE csr_legal_employer ; */
1699
1700 /* get the Legal Employer for the primary assignment */
1701 OPEN csr_legal_employer (l_prim_local_unit ) ;
1702 FETCH csr_legal_employer INTO l_prim_legal_emp ;
1703 CLOSE csr_legal_employer ;
1704
1705 IF (l_legal_employer_id <> l_prim_legal_emp) THEN
1706
1707 l_diff_le_text := 'Y' ;
1708
1709 /* set the message name and get the message text */
1710 hr_utility.set_message (801, 'PAY_376864_NO_SEC_ASG_LE_DIFF');
1711 l_msg_txt := hr_utility.get_message ;
1712
1713 /* Arcvhive the message */
1714 pay_action_information_api.create_action_information (
1715 p_action_information_id => l_action_info_id
1716 ,p_action_context_id => p_archive_assact_id
1717 ,p_action_context_type => 'AAP'
1718 ,p_object_version_number => l_ovn
1719 ,p_effective_date => p_effective_date
1720 ,p_source_id => NULL
1721 ,p_source_text => NULL
1722 ,p_action_information_category => 'EMPLOYEE OTHER INFORMATION'
1723 ,p_action_information1 => l_bg_id
1724 ,p_action_information2 => 'MESG' -- Message Context
1725 ,p_action_information3 => NULL
1726 ,p_action_information4 => NULL
1727 ,p_action_information5 => NULL
1728 ,p_action_information6 => l_msg_txt );
1729
1730 END IF ;
1731
1732 END IF ;
1733
1734 END IF ;
1735
1736 /* Finished archiving a message if the current Legal Employer is different from the Legal Employer of the Primary Assignment */
1737
1738 /* INSERT PERSON DETAILS */
1739 /* Bug 16097213 */
1740 OPEN csr_reg_pay_date(p_time_period_id);
1741 FETCH csr_reg_pay_date INTO l_reg_payment_date;
1742 CLOSE csr_reg_pay_date;
1743
1744 pay_action_information_api.create_action_information (
1745 p_action_information_id => l_action_info_id
1746 ,p_action_context_id => p_archive_assact_id
1747 ,p_action_context_type => 'AAP'
1748 ,p_object_version_number => l_ovn
1749 ,p_effective_date => l_reg_payment_date --p_effective_date
1750 ,p_source_id => NULL
1751 ,p_source_text => NULL
1752 ,p_action_information_category => 'EMPLOYEE DETAILS'
1753 ,p_action_information1 => rec_person_details.full_name
1754 ,p_action_information2 => l_legal_employer_id -- Legal Employer Org ID
1755 ,p_action_information4 => rec_person_details.ni_number
1756 ,p_action_information7 => l_grade
1757 ,p_action_information10 => rec_person_details.emp_num
1758 ,p_action_information12 => fnd_date.date_to_displaydate(l_hire_date) -- fnd_date.date_to_canonical(l_hire_date)
1759 ,p_action_information14 => rec_person_details.asg_num
1760 ,p_action_information15 => l_organization -- name of HR Org at asg level
1761 ,p_action_information16 => p_time_period_id
1762 ,p_action_information17 => l_job
1763 ,p_action_information18 => l_employer_name -- Legal Employer Name
1764 ,p_action_information19 => l_position
1765 ,p_action_information25 => le_phone_num_str
1766 ,p_action_information30 => l_pay_location
1767 ,p_assignment_id => p_assignment_id);
1768
1769
1770 /* INSERT ADDRESS DETAILS */
1771 IF rec_primary_address.AL1 IS NOT NULL THEN /* CHECK IF EMPLOYEE HAS BEEN GIVEN A PRIMARY ADDRESS */
1772 pay_action_information_api.create_action_information (
1773 p_action_information_id => l_action_info_id
1774 ,p_action_context_id => p_archive_assact_id
1775 ,p_action_context_type => 'AAP'
1776 ,p_object_version_number => l_ovn
1777 ,p_effective_date => p_effective_date
1778 ,p_source_id => NULL
1779 ,p_source_text => NULL
1780 ,p_action_information_category => 'ADDRESS DETAILS'
1781 ,p_action_information1 => rec_primary_address.person_id
1782 ,p_action_information5 => rec_primary_address.AL1
1783 ,p_action_information6 => rec_primary_address.AL2
1784 ,p_action_information7 => rec_primary_address.AL3
1785 ,p_action_information12 => l_postal_code
1786 ,p_action_information13 => l_country
1787 ,p_action_information14 => 'Employee Address'
1788 ,p_assignment_id => p_assignment_id);
1789 ELSE
1790 /* INSERT EMPLOYER ADDRESS AS EMPLOYEE'S PRIMARY ADDRESS */
1791 pay_action_information_api.create_action_information (
1792 p_action_information_id => l_action_info_id
1793 ,p_action_context_id => p_archive_assact_id
1794 ,p_action_context_type => 'AAP'
1795 ,p_object_version_number => l_ovn
1796 ,p_effective_date => p_effective_date
1797 ,p_source_id => NULL
1798 ,p_source_text => NULL
1799 ,p_action_information_category => 'ADDRESS DETAILS'
1800 ,p_action_information1 => rec_primary_address.person_id
1801 ,p_action_information5 => NULL
1802 ,p_action_information6 => NULL
1803 ,p_action_information7 => NULL
1804 ,p_action_information8 => NULL
1805 ,p_action_information9 => NULL
1806 ,p_action_information10 => NULL
1807 ,p_action_information11 => NULL
1808 ,p_action_information12 => NULL
1809 ,p_action_information13 => NULL
1810 ,p_action_information14 => 'Employee Address'
1811 ,p_assignment_id => p_assignment_id);
1812 END IF;
1813
1814 /*fnd_file.put_line(fnd_file.log,'Entering Procedure ARCHIVE_EMPLOYEE_DETAILS 9');*/
1815
1816 /* INSERT EMPLOYER'S ADDRESS (ORGANIZATION ADDRESS)*/
1817
1818 BEGIN
1819 l_org_exists := 0;
1820
1821 SELECT 1
1822 INTO l_org_exists
1823 FROM pay_action_information
1824 WHERE action_context_id = p_payroll_action_id
1825 AND action_information1 = l_legal_employer_id -- rec_person_details.org_id
1826 AND effective_date = p_effective_date
1827 AND action_information_category = 'ADDRESS DETAILS';
1828
1829 EXCEPTION
1830
1831 WHEN NO_DATA_FOUND THEN
1832 pay_action_information_api.create_action_information (
1833 p_action_information_id => l_action_info_id
1834 ,p_action_context_id => p_payroll_action_id
1835 ,p_action_context_type => 'PA'
1836 ,p_object_version_number => l_ovn
1837 ,p_effective_date => p_effective_date
1838 ,p_source_id => NULL
1839 ,p_source_text => NULL
1840 ,p_action_information_category => 'ADDRESS DETAILS'
1841 ,p_action_information1 => l_legal_employer_id -- rec_person_details.org_id
1842 ,p_action_information5 => rec_employer_address.AL1
1843 ,p_action_information6 => rec_employer_address.AL2
1844 ,p_action_information7 => rec_employer_address.AL3
1845 ,p_action_information12 => l_emp_postal_code
1846 ,p_action_information13 => l_emp_country
1847 ,p_action_information14 => 'Employer Address'
1848 ,p_action_information26 => l_le_org_num ); -- using Localization Specific1 for Legal Employer CVR Number
1849
1850 WHEN OTHERS THEN
1851 NULL;
1852 END;
1853
1854 /*fnd_file.put_line(fnd_file.log,'Entering Procedure ARCHIVE_EMPLOYEE_DETAILS 10');*/
1855
1856 --
1857 IF g_debug THEN
1858 hr_utility.set_location(' Leaving Procedure ARCHIVE_EMPLOYEE_DETAILS',170);
1859 END IF;
1860 --
1861
1862 EXCEPTION WHEN OTHERS THEN
1863 g_err_num := SQLCODE;
1864 /*fnd_file.put_line(fnd_file.log,'ORA_ERR: ' || g_err_num || 'In ARCHIVE_EMPLOYEE_DETAILS');*/
1865
1866 IF g_debug THEN
1867 hr_utility.set_location('ORA_ERR: ' || g_err_num || 'In ARCHIVE_EMPLOYEE_DETAILS',180);
1868 END IF;
1869
1870 END ARCHIVE_EMPLOYEE_DETAILS;
1871
1872 ----------------------------------- PROCEDURE ARCHIVE_ELEMENT_INFO ---------------------------------------------------------------
1873
1874 /* EARNINGS REGION, DEDUCTIONS REGION */
1875
1876 PROCEDURE ARCHIVE_ELEMENT_INFO(p_payroll_action_id IN NUMBER
1877 ,p_effective_date IN DATE
1878 ,p_date_earned IN DATE
1879 ,p_pre_payact_id IN NUMBER)
1880 IS
1881 ----------------
1882
1883 /* Cursor to retrieve Earnings Element Information */
1884
1885 /*
1886
1887 CURSOR csr_ear_element_info IS
1888 SELECT nvl(pettl.reporting_name,pettl.element_name) rep_name
1889 ,et.element_type_id element_type_id
1890 ,iv.input_value_id input_value_id
1891 ,iv.uom uom
1892 FROM pay_element_types_f et
1893 , pay_element_types_f_tl pettl
1894 , pay_input_values_f iv
1895 , pay_element_classifications classification
1896 WHERE et.element_type_id = iv.element_type_id
1897 AND et.element_type_id = pettl.element_type_id
1898 AND pettl.language = USERENV('LANG')
1899 AND iv.name = 'Pay Value'
1900 AND classification.classification_id = et.classification_id
1901 AND classification.classification_name
1902 IN ('Absence','Direct Payments','Earnings','Supplementary Earnings')
1903 AND p_date_earned BETWEEN et.effective_start_date AND et.effective_end_date
1904 AND p_date_earned BETWEEN iv.effective_start_date AND iv.effective_end_date
1905 AND ((et.business_group_id IS NULL AND et.legislation_code = 'NO')
1906 OR (et.business_group_id = g_business_group_id AND et.legislation_code IS NULL));
1907
1908 */
1909 -----------------
1910
1911 /* Cursor to retrieve Deduction Element Information */
1912
1913 /*
1914 CURSOR csr_ded_element_info IS
1915 SELECT nvl(pettl.reporting_name,pettl.element_name) rep_name
1916 ,et.element_type_id element_type_id
1917 ,iv.input_value_id input_value_id
1918 ,iv.uom uom
1919 FROM pay_element_types_f et
1920 , pay_element_types_f_tl pettl
1921 , pay_input_values_f iv
1922 , pay_element_classifications classification
1923 WHERE et.element_type_id = iv.element_type_id
1924 AND et.element_type_id = pettl.element_type_id
1925 AND pettl.language = USERENV('LANG')
1926 AND iv.name = 'Pay Value'
1927 AND classification.classification_id = et.classification_id
1928 AND classification.classification_name
1929 IN ('Involuntary Deductions','Pre-tax Deductions','Statutory Deductions','Voluntary Deductions')
1930 AND p_date_earned BETWEEN et.effective_start_date AND et.effective_end_date
1931 AND p_date_earned BETWEEN iv.effective_start_date AND iv.effective_end_date
1932 AND ((et.business_group_id IS NULL AND et.legislation_code = 'NO')
1933 OR (et.business_group_id = g_business_group_id AND et.legislation_code IS NULL));
1934
1935 */
1936 ----------------------------------------------------------------------------------------------------------
1937
1938 /* Cursor to retrieve Element Information (For elements with Pay Value as Input Value) */
1939
1940 /*
1941 CURSOR csr_element_info IS
1942 SELECT nvl(pettl.reporting_name,pettl.element_name) rep_name
1943 ,et.element_type_id element_type_id
1944 ,iv.input_value_id input_value_id
1945 ,iv.uom uom
1946 ,classification.classification_name ele_class
1947 FROM pay_element_types_f et
1948 , pay_element_types_f_tl pettl
1949 , pay_input_values_f iv
1950 , pay_element_classifications classification
1951 WHERE et.element_type_id = iv.element_type_id
1952 AND et.element_type_id = pettl.element_type_id
1953 AND pettl.language = USERENV('LANG')
1954 AND iv.name = 'Pay Value'
1955 AND classification.classification_id = et.classification_id
1956 AND classification.classification_name
1957 IN ('Earnings','Supplementary Earnings','Absence','Direct Payments','Pre-tax Deductions',
1958 'Involuntary Deductions','Voluntary Deductions','Statutory Deductions','Reductions',
1959 'Taxable Benefits','Benefits Not Taxed','Expenses Information','Taxable Expenses')
1960 AND p_date_earned BETWEEN et.effective_start_date AND et.effective_end_date
1961 AND p_date_earned BETWEEN iv.effective_start_date AND iv.effective_end_date
1962 AND ((et.business_group_id IS NULL AND et.legislation_code = 'NO')
1963 OR (et.business_group_id = g_business_group_id AND et.legislation_code IS NULL))
1964 ORDER BY DECODE (classification.classification_name,
1965 'Earnings',1,'Supplementary Earnings',2,'Absence',3,'Direct Payments',4,
1966 'Pre-tax Deductions',5,'Involuntary Deductions',6,'Voluntary Deductions',7,
1967 'Statutory Deductions',8,'Reductions',9,'Taxable Benefits',10,
1968 'Benefits Not Taxed',11,'Expenses Information',12,'Taxable Expenses',13) ;
1969
1970 */
1971
1972 CURSOR csr_element_info (p_ele_class_name pay_element_classifications.classification_name%type ) IS
1973 SELECT nvl(pettl.reporting_name,pettl.element_name) rep_name
1974 ,et.element_type_id element_type_id
1975 ,iv.input_value_id input_value_id
1976 ,iv.uom uom
1977 ,classification.classification_name ele_class
1978 FROM pay_element_types_f et
1979 , pay_element_types_f_tl pettl
1980 , pay_input_values_f iv
1981 , pay_element_classifications classification
1982 WHERE et.element_type_id = iv.element_type_id
1983 AND et.element_type_id = pettl.element_type_id
1984 AND pettl.language = USERENV('LANG')
1985 AND iv.name = 'Pay Value'
1986 AND classification.classification_id = et.classification_id
1987 AND classification.classification_name = p_ele_class_name
1988 AND p_date_earned BETWEEN et.effective_start_date AND et.effective_end_date
1989 AND p_date_earned BETWEEN iv.effective_start_date AND iv.effective_end_date
1990 AND ((et.business_group_id IS NULL AND et.legislation_code = 'NO')
1991 OR (et.business_group_id = g_business_group_id AND et.legislation_code IS NULL)) ;
1992
1993
1994 ----------------------------------------------------------------------------------------------------------
1995
1996 /* Cursor to get the first primary balance type id for an input value id */
1997
1998 CURSOR csr_prim_bal_type (p_iv_id NUMBER)IS
1999 SELECT balance_type_id
2000 FROM pay_balance_types
2001 WHERE input_value_id = p_iv_id
2002 AND ((business_group_id IS NULL AND legislation_code = 'NO')
2003 OR (business_group_id = g_business_group_id AND legislation_code IS NULL))
2004 AND rownum = 1 ;
2005
2006 ----------------------------------------------------------------------------------------------------
2007
2008 /* Cursor to get the defined balance id */
2009
2010 CURSOR csr_def_bal_id (p_prim_bal_type_id NUMBER)IS
2011 SELECT defined_balance_id
2012 FROM pay_defined_balances
2013 WHERE balance_type_id = p_prim_bal_type_id
2014 AND ((business_group_id IS NULL AND legislation_code = 'NO')
2015 OR (business_group_id = g_business_group_id AND legislation_code IS NULL))
2016 AND balance_dimension_id = ( select balance_dimension_id
2017 from pay_balance_dimensions where legislation_code = 'NO'
2018 and dimension_name = 'Assignment Calendar Year To Date' ) ;
2019
2020 ----------------------------------------------------------------------------------------------------
2021
2022 /* cursor to get the sub classifications for an element type */
2023
2024 /*
2025 CURSOR csr_sub_class (p_ele_type_id NUMBER) IS
2026 SELECT eleclass.classification_name
2027 ,decode (eleclass.classification_name,'Taxable Pay _ Absence',1,'Taxable Pay _ Earnings',1
2028 ,'Taxable Pay _ Supplemental Earnings',1,'Taxable Pay _ Taxable Benefits',1,0) table_base
2029 ,decode (eleclass.classification_name,'Additional Taxable Pay _ Absence',2,'Additional Taxable Pay _ Earnings',2
2030 ,'Additional Taxable Pay _ Supplemental Earnings',2,'Additional Taxable Pay _ Taxable Benefits',2,0) percent_base
2031 ,decode (eleclass.classification_name,'Holiday Pay',3,0) holiday_pay
2032
2033 FROM pay_sub_classification_rules_f subclass
2034 ,pay_element_classifications eleclass
2035
2036 WHERE subclass.element_type_id = p_ele_type_id
2037 AND p_date_earned BETWEEN subclass.effective_start_date AND subclass.effective_end_date
2038 AND ((subclass.business_group_id IS NULL AND subclass.legislation_code = 'NO')
2039 OR (subclass.business_group_id = g_business_group_id AND subclass.legislation_code IS NULL))
2040 AND ((eleclass.business_group_id IS NULL AND eleclass.legislation_code = 'NO')
2041 OR (eleclass.business_group_id = g_business_group_id AND eleclass.legislation_code IS NULL))
2042 AND eleclass.classification_id = subclass.classification_id ;
2043
2044 */
2045
2046 CURSOR csr_sub_class (p_ele_type_id NUMBER , p_ele_sub_class_name pay_element_classifications.classification_name%type ) IS
2047 SELECT 1
2048 FROM pay_sub_classification_rules_f subclass
2049 ,pay_element_classifications eleclass
2050 WHERE subclass.element_type_id = p_ele_type_id
2051 AND eleclass.classification_name = p_ele_sub_class_name
2052 AND p_date_earned BETWEEN subclass.effective_start_date AND subclass.effective_end_date
2053 AND ((subclass.business_group_id IS NULL AND subclass.legislation_code = 'NO')
2054 OR (subclass.business_group_id = g_business_group_id AND subclass.legislation_code IS NULL))
2055 AND ((eleclass.business_group_id IS NULL AND eleclass.legislation_code = 'NO')
2056 OR (eleclass.business_group_id = g_business_group_id AND eleclass.legislation_code IS NULL))
2057 AND eleclass.classification_id = subclass.classification_id ;
2058
2059
2060 -- cursor to check if an element has a holiday pay secondary classification
2061
2062 /*
2063 CURSOR csr_hol_sub_class (p_ele_type_id NUMBER ) IS
2064 SELECT 'YES'
2065 FROM pay_sub_classification_rules_f subclass
2066 ,pay_element_classifications eleclass
2067 WHERE subclass.element_type_id = p_ele_type_id
2068 AND eleclass.classification_id = subclass.classification_id
2069 AND eleclass.classification_name in ( 'Holiday Pay Base _ Absence',
2070 'Holiday Pay Base _ Earnings',
2071 'Holiday Pay Base _ Earnings Adjustment',
2072 'Holiday Pay Base _ Holiday Pay Earnings Adjustment',
2073 'Holiday Pay Base _ Supplementary Earnings')
2074 AND p_date_earned BETWEEN subclass.effective_start_date AND subclass.effective_end_date
2075 AND ((subclass.business_group_id IS NULL AND subclass.legislation_code = 'NO')
2076 OR (subclass.business_group_id = g_business_group_id AND subclass.legislation_code IS NULL))
2077 AND ((eleclass.business_group_id IS NULL AND eleclass.legislation_code = 'NO')
2078 OR (eleclass.business_group_id = g_business_group_id AND eleclass.legislation_code IS NULL)) ;
2079
2080 */
2081
2082 /* Change for element classification name from 'Holiday Pay Earnings Adjustment' to 'Holiday Pay Earnings Adjust' */
2083
2084 /*
2085 CURSOR csr_hol_sub_class (p_ele_type_id NUMBER ) IS
2086 SELECT 'YES'
2087 FROM pay_sub_classification_rules_f subclass
2088 ,pay_element_classifications eleclass
2089 WHERE subclass.element_type_id = p_ele_type_id
2090 AND eleclass.classification_id = subclass.classification_id
2091 AND eleclass.classification_name in ( 'Holiday Pay Base _ Absence',
2092 'Holiday Pay Base _ Earnings',
2093 'Holiday Pay Base _ Earnings Adjustment',
2094 'Holiday Pay Base _ Holiday Pay Earnings Adjust',
2095 'Holiday Pay Base _ Supplementary Earnings')
2096 AND p_date_earned BETWEEN subclass.effective_start_date AND subclass.effective_end_date
2097 AND ((subclass.business_group_id IS NULL AND subclass.legislation_code = 'NO')
2098 OR (subclass.business_group_id = g_business_group_id AND subclass.legislation_code IS NULL))
2099 AND ((eleclass.business_group_id IS NULL AND eleclass.legislation_code = 'NO')
2100 OR (eleclass.business_group_id = g_business_group_id AND eleclass.legislation_code IS NULL)) ;
2101 */
2102
2103
2104 /* Sub classification added for Impact of Absence on Holiday Pay */
2105 /*
2106 CURSOR csr_hol_sub_class (p_ele_type_id NUMBER ) IS
2107 SELECT 'YES'
2108 FROM pay_sub_classification_rules_f subclass
2109 ,pay_element_classifications eleclass
2110 WHERE subclass.element_type_id = p_ele_type_id
2111 AND eleclass.classification_id = subclass.classification_id
2112 AND eleclass.classification_name in ( 'Holiday Pay Base _ Absence',
2113 'Holiday Pay Base _ Earnings',
2114 'Holiday Pay Base _ Earnings Adjustment',
2115 'Holiday Pay Base _ Holiday Pay Earnings Adjust',
2116 'Holiday Pay Base _ Supplementary Earnings',
2117 'Holiday Pay Base During Absence _ Information')
2118 AND p_date_earned BETWEEN subclass.effective_start_date AND subclass.effective_end_date
2119 AND ((subclass.business_group_id IS NULL AND subclass.legislation_code = 'NO')
2120 OR (subclass.business_group_id = g_business_group_id AND subclass.legislation_code IS NULL))
2121 AND ((eleclass.business_group_id IS NULL AND eleclass.legislation_code = 'NO')
2122 OR (eleclass.business_group_id = g_business_group_id AND eleclass.legislation_code IS NULL)) ;
2123
2124 */
2125
2126 -- The following classifications have been obsoleted and will no longer be used.
2127 -- Holiday Pay Base _ Holiday Pay Earnings Adjust => Holiday Pay Base _ Holiday Pay Earnings Adjust Obsolete
2128
2129 CURSOR csr_hol_sub_class (p_ele_type_id NUMBER ) IS
2130 SELECT 'YES'
2131 FROM pay_sub_classification_rules_f subclass
2132 ,pay_element_classifications eleclass
2133 WHERE subclass.element_type_id = p_ele_type_id
2134 AND eleclass.classification_id = subclass.classification_id
2135 AND eleclass.classification_name in ( 'Holiday Pay Base _ Absence',
2136 'Holiday Pay Base _ Earnings',
2137 'Holiday Pay Base _ Earnings Adjustment',
2138 'Holiday Pay Base _ Supplementary Earnings',
2139 'Holiday Pay Base During Absence _ Information')
2140 AND p_date_earned BETWEEN subclass.effective_start_date AND subclass.effective_end_date
2141 AND ((subclass.business_group_id IS NULL AND subclass.legislation_code = 'NO')
2142 OR (subclass.business_group_id = g_business_group_id AND subclass.legislation_code IS NULL))
2143 AND ((eleclass.business_group_id IS NULL AND eleclass.legislation_code = 'NO')
2144 OR (eleclass.business_group_id = g_business_group_id AND eleclass.legislation_code IS NULL)) ;
2145
2146
2147 ----------------------------------------------------------------------------------------------------------------------
2148
2149 ---------------------
2150 l_action_info_id NUMBER;
2151 l_ovn NUMBER;
2152 l_flag NUMBER := 0;
2153 l_prim_bal_type_id NUMBER;
2154 l_prim_def_bal_id NUMBER ;
2155 l_table_basis NUMBER;
2156 l_percent_basis NUMBER;
2157 l_holiday_basis NUMBER;
2158 l_ele_class VARCHAR2(240);
2159 l_basis_text VARCHAR2(240);
2160 l_holiday_text VARCHAR2(240);
2161 l_index NUMBER;
2162
2163 TYPE ele_class_rec IS RECORD ( ele_class_name pay_element_classifications.classification_name%type
2164 , ele_context VARCHAR2(20) );
2165
2166 TYPE ele_class_tab IS TABLE OF ele_class_rec INDEX BY BINARY_INTEGER;
2167
2168 ele_class_table ele_class_tab;
2169
2170 ----------------------
2171 BEGIN
2172
2173 IF g_debug THEN
2174 hr_utility.set_location(' Entering Procedure ARCHIVE_ELEMENT_INFO',210);
2175 END IF;
2176
2177 ------------------------
2178 /* ALL ELEMENTS */
2179
2180
2181 /* initializing table for element classification name */
2182 ele_class_table(1).ele_class_name := 'Earnings'; ele_class_table(1).ele_context := 'S' ;
2183 ele_class_table(2).ele_class_name := 'Supplementary Earnings'; ele_class_table(2).ele_context := 'S' ;
2184 ele_class_table(3).ele_class_name := 'Absence'; ele_class_table(3).ele_context := 'S' ;
2185 ele_class_table(4).ele_class_name := 'Direct Payments'; ele_class_table(4).ele_context := 'OR' ;
2186 ele_class_table(5).ele_class_name := 'Pre-tax Deductions'; ele_class_table(5).ele_context := 'OD' ;
2187 ele_class_table(6).ele_class_name := 'Involuntary Deductions'; ele_class_table(6).ele_context := 'OD' ;
2188 ele_class_table(7).ele_class_name := 'Voluntary Deductions'; ele_class_table(7).ele_context := 'OD' ;
2189 ele_class_table(8).ele_class_name := 'Statutory Deductions'; ele_class_table(8).ele_context := 'WT' ;
2190 ele_class_table(9).ele_class_name := 'Reductions'; ele_class_table(9).ele_context := 'OTH' ;
2191 ele_class_table(10).ele_class_name := 'Taxable Benefits'; ele_class_table(10).ele_context := 'OTH' ;
2192 ele_class_table(11).ele_class_name := 'Benefits Not Taxed'; ele_class_table(11).ele_context := 'OTH' ;
2193 ele_class_table(12).ele_class_name := 'Expenses Information'; ele_class_table(12).ele_context := 'OTH' ;
2194 ele_class_table(13).ele_class_name := 'Taxable Expenses'; ele_class_table(13).ele_context := 'OTH' ;
2195 -- new added
2196 -- ele_class_table(14).ele_class_name := 'Earnings Adjustment'; ele_class_table(14).ele_context := 'OD' ;
2197 -- ele_class_table(15).ele_class_name := 'Holiday Pay Earnings Adjustment'; ele_class_table(15).ele_context := 'OD' ;
2198
2199 -- should be displayed in the 'Total Salary' with a negative sign
2200 ele_class_table(14).ele_class_name := 'Earnings Adjustment'; ele_class_table(14).ele_context := 'S' ;
2201 -- ele_class_table(15).ele_class_name := 'Holiday Pay Earnings Adjustment'; ele_class_table(15).ele_context := 'S' ;
2202
2203 /* Change for element classification name from 'Holiday Pay Earnings Adjustment' to 'Holiday Pay Earnings Adjust' */
2204
2205 -- The following classifications have been obsoleted and will no longer be used.
2206 -- Holiday Pay Earnings Adjust => Holiday Pay Earnings Adjust Obsolete
2207 -- Commenting the code below.
2208
2209 -- ele_class_table(15).ele_class_name := 'Holiday Pay Earnings Adjust'; ele_class_table(15).ele_context := 'S' ;
2210
2211 /* Element classification 'Information' added for Impact of Absence on Holiday Pay */
2212 -- ele_class_table(16).ele_class_name := 'Information'; ele_class_table(16).ele_context := 'OTH' ;
2213
2214 -- Due to the obsoletion of the above classification , moving the index from 16 to 15
2215 ele_class_table(15).ele_class_name := 'Information'; ele_class_table(15).ele_context := 'OTH' ;
2216
2217 -- Added for advance pay --
2218 ele_class_table(16).ele_class_name := 'Advance Earnings'; ele_class_table(16).ele_context := 'S' ;
2219 --
2220
2221 FOR l_index IN ele_class_table.first.. ele_class_table.last LOOP
2222
2223 FOR rec_earnings IN csr_element_info(ele_class_table(l_index).ele_class_name) LOOP
2224
2225 BEGIN
2226
2227 l_prim_bal_type_id := NULL ;
2228 l_prim_def_bal_id := NULL ;
2229 l_ele_class := NULL ;
2230 l_table_basis := NULL ;
2231 l_percent_basis := NULL ;
2232 l_holiday_basis := NULL ;
2233 l_basis_text := NULL ;
2234 l_holiday_text := NULL ;
2235
2236 -- check if the element is basis for holiday pay
2237
2238 /* moving on top to check for information elements whiich are basis for holiday pay :
2239 element class : Information
2240 sec class : Holiday Pay Base During Absence _ Information */
2241
2242 OPEN csr_hol_sub_class (rec_earnings.element_type_id) ;
2243 FETCH csr_hol_sub_class INTO l_holiday_text ;
2244 CLOSE csr_hol_sub_class ;
2245
2246 IF ((ele_class_table(l_index).ele_class_name <> 'Information')
2247 OR
2248 ((ele_class_table(l_index).ele_class_name = 'Information') AND
2249 (l_holiday_text = 'YES'))) THEN
2250
2251 -- fnd_file.put_line(fnd_file.log,'ele_class_table(l_index).ele_class_name ' || ele_class_table(l_index).ele_class_name );
2252 -- fnd_file.put_line(fnd_file.log,'rec_earnings.element_type_id ' || rec_earnings.element_type_id );
2253 -- fnd_file.put_line(fnd_file.log,'l_holiday_text = ' || l_holiday_text );
2254 -- fnd_file.put_line(fnd_file.log,'--------------------------------------------- ');
2255
2256
2257
2258 /* get the primary balance type id from the input value id */
2259 OPEN csr_prim_bal_type (rec_earnings.input_value_id);
2260 FETCH csr_prim_bal_type INTO l_prim_bal_type_id ;
2261 CLOSE csr_prim_bal_type ;
2262
2263 /* get the defined balance id from the balance type id */
2264 OPEN csr_def_bal_id (l_prim_bal_type_id ) ;
2265 FETCH csr_def_bal_id INTO l_prim_def_bal_id ;
2266 CLOSE csr_def_bal_id ;
2267
2268 /* get the table/percetage basis info */
2269
2270 l_basis_text := NULL ;
2271
2272 IF ( ele_class_table(l_index).ele_class_name = 'Pre-tax Deductions' )
2273
2274 THEN l_basis_text := 'TABLE';
2275
2276 ELSIF ( ele_class_table(l_index).ele_class_name = 'Taxable Expenses' )
2277
2278 THEN l_basis_text := 'PERCENTAGE';
2279
2280 ELSIF ( ele_class_table(l_index).ele_class_name = 'Absence' )
2281
2282 THEN
2283 OPEN csr_sub_class (rec_earnings.element_type_id , 'Taxable Pay _ Absence') ;
2284 FETCH csr_sub_class INTO l_table_basis ;
2285 IF (csr_sub_class%FOUND)
2286 THEN l_basis_text := 'TABLE';
2287 END IF;
2288 CLOSE csr_sub_class ;
2289
2290
2291 OPEN csr_sub_class (rec_earnings.element_type_id , 'Additional Taxable Pay _ Absence') ;
2292 FETCH csr_sub_class INTO l_percent_basis ;
2293 IF (csr_sub_class%FOUND)
2294 THEN l_basis_text := 'PERCENTAGE';
2295 END IF;
2296 CLOSE csr_sub_class ;
2297
2298
2299 ELSIF ( ele_class_table(l_index).ele_class_name = 'Earnings' )
2300
2301 THEN
2302 OPEN csr_sub_class (rec_earnings.element_type_id , 'Taxable Pay _ Earnings') ;
2303 FETCH csr_sub_class INTO l_table_basis ;
2304 IF (csr_sub_class%FOUND)
2305 THEN l_basis_text := 'TABLE';
2306 END IF;
2307 CLOSE csr_sub_class ;
2308
2309
2310 OPEN csr_sub_class (rec_earnings.element_type_id , 'Additional Taxable Pay _ Earnings') ;
2311 FETCH csr_sub_class INTO l_percent_basis ;
2312 IF (csr_sub_class%FOUND)
2313 THEN l_basis_text := 'PERCENTAGE';
2314 END IF;
2315 CLOSE csr_sub_class ;
2316
2317 ELSIF ( ele_class_table(l_index).ele_class_name = 'Supplementary Earnings' )
2318
2319 THEN
2320 OPEN csr_sub_class (rec_earnings.element_type_id , 'Taxable Pay _ Supplemental Earnings') ;
2321 FETCH csr_sub_class INTO l_table_basis ;
2322 IF (csr_sub_class%FOUND)
2323 THEN l_basis_text := 'TABLE';
2324 END IF;
2325 CLOSE csr_sub_class ;
2326
2327
2328 OPEN csr_sub_class (rec_earnings.element_type_id , 'Additional Taxable Pay _ Supplemental Earnings') ;
2329 FETCH csr_sub_class INTO l_percent_basis ;
2330 IF (csr_sub_class%FOUND)
2331 THEN l_basis_text := 'PERCENTAGE';
2332 END IF;
2333 CLOSE csr_sub_class ;
2334
2335 ELSIF ( ele_class_table(l_index).ele_class_name = 'Taxable Benefits' )
2336
2337 THEN
2338 OPEN csr_sub_class (rec_earnings.element_type_id , 'Taxable Pay _ Taxable Benefits') ;
2339 FETCH csr_sub_class INTO l_table_basis ;
2340 IF (csr_sub_class%FOUND)
2341 THEN l_basis_text := 'TABLE';
2342 END IF;
2343 CLOSE csr_sub_class ;
2344
2345
2346 OPEN csr_sub_class (rec_earnings.element_type_id , 'Additional Taxable Pay _ Taxable Benefits') ;
2347 FETCH csr_sub_class INTO l_percent_basis ;
2348 IF (csr_sub_class%FOUND)
2349 THEN l_basis_text := 'PERCENTAGE';
2350 END IF;
2351 CLOSE csr_sub_class ;
2352
2353 ELSIF ( ele_class_table(l_index).ele_class_name = 'Earnings Adjustment' )
2354
2355 THEN
2356 OPEN csr_sub_class (rec_earnings.element_type_id , 'Taxable Pay _ Earnings Adjustment') ;
2357 FETCH csr_sub_class INTO l_table_basis ;
2358 IF (csr_sub_class%FOUND)
2359 THEN l_basis_text := 'TABLE';
2360 END IF;
2361 CLOSE csr_sub_class ;
2362
2363
2364 OPEN csr_sub_class (rec_earnings.element_type_id , 'Additional Taxable Pay _ Earnings Adjustment') ;
2365 FETCH csr_sub_class INTO l_percent_basis ;
2366 IF (csr_sub_class%FOUND)
2367 THEN l_basis_text := 'PERCENTAGE';
2368 END IF;
2369 CLOSE csr_sub_class ;
2370
2371 /* Change for element classification name from 'Holiday Pay Earnings Adjustment' to 'Holiday Pay Earnings Adjust' */
2372
2373 -- The following classifications have been obsoleted and will no longer be used.
2374 -- Holiday Pay Earnings Adjust => Holiday Pay Earnings Adjust Obsolete
2375 -- Taxable Pay _ Holiday Pay Earnings Adjust => Taxable Pay _ Holiday Pay Earnings Adjust Obsolete
2376 -- Additional Taxable Pay _ Holiday Pay Earnings Adjust => Additional Taxable Pay _ Holiday Pay Earnings Adjust Obsolete
2377 -- Commenting the code below.
2378
2379 /*
2380
2381 -- ELSIF ( ele_class_table(l_index).ele_class_name = 'Holiday Pay Earnings Adjustment' )
2382 ELSIF ( ele_class_table(l_index).ele_class_name = 'Holiday Pay Earnings Adjust' )
2383
2384 THEN
2385 -- OPEN csr_sub_class (rec_earnings.element_type_id , 'Taxable Pay _ Holiday Pay Earnings Adjustment') ;
2386 OPEN csr_sub_class (rec_earnings.element_type_id , 'Taxable Pay _ Holiday Pay Earnings Adjust') ;
2387 FETCH csr_sub_class INTO l_table_basis ;
2388 IF (csr_sub_class%FOUND)
2389 THEN l_basis_text := 'TABLE';
2390 END IF;
2391 CLOSE csr_sub_class ;
2392
2393
2394 -- OPEN csr_sub_class (rec_earnings.element_type_id , 'Additional Taxable Pay _ Holiday Pay Earnings Adjustment') ;
2395 OPEN csr_sub_class (rec_earnings.element_type_id , 'Additional Taxable Pay _ Holiday Pay Earnings Adjust') ;
2396 FETCH csr_sub_class INTO l_percent_basis ;
2397 IF (csr_sub_class%FOUND)
2398 THEN l_basis_text := 'PERCENTAGE';
2399 END IF;
2400 CLOSE csr_sub_class ;
2401
2402 */
2403
2404 END IF;
2405
2406 /*
2407 OPEN csr_sub_class (rec_earnings.element_type_id) ;
2408 FETCH csr_sub_class INTO l_ele_class ,l_table_basis ,l_percent_basis ,l_holiday_basis ;
2409 CLOSE csr_sub_class ;
2410
2411
2412 l_basis_text := NULL ;
2413
2414 IF (l_table_basis = 1) THEN l_basis_text := 'TABLE';
2415 ELSIF (l_percent_basis = 2) THEN l_basis_text := 'PERCENTAGE';
2416 END IF;
2417
2418 l_holiday_text := NULL ;
2419
2420 IF (l_holiday_basis = 3) THEN l_holiday_text := 'Y';
2421 END IF ;
2422
2423 */
2424
2425 /* still to incorporate solution for Basis for Holiday Pay */
2426
2427 -- l_holiday_text := NULL ;
2428
2429 -- check if the element is basis for holiday pay
2430
2431 /* moving on top to check for information elements whiich are basis for holiday pay :
2432 element class : Information
2433 sec class : Holiday Pay Base During Absence _ Information */
2434 /*
2435 OPEN csr_hol_sub_class (rec_earnings.element_type_id) ;
2436 FETCH csr_hol_sub_class INTO l_holiday_text ;
2437 CLOSE csr_hol_sub_class ;
2438 */
2439
2440 IF (l_basis_text IS NOT NULL) THEN
2441 l_basis_text := hr_general.decode_lookup('NO_REPORT_LABELS',l_basis_text);
2442 END IF;
2443
2444 IF (l_holiday_text IS NOT NULL) THEN
2445 l_holiday_text := hr_general.decode_lookup('NO_REPORT_LABELS',l_holiday_text);
2446 END IF;
2447
2448 -- fnd_file.put_line (fnd_file.LOG,'3: ' || rec_earnings.rep_name ||','|| l_basis_text ||','|| l_holiday_text );
2449
2450 BEGIN
2451 -- check if the Element definition has already been archived
2452 SELECT 1 INTO l_flag
2453 FROM pay_action_information
2454 WHERE action_context_id = p_payroll_action_id
2455 AND action_information_category = 'NO ELEMENT DEFINITION'
2456 AND action_context_type = 'PA'
2457 AND action_information2 = rec_earnings.element_type_id
2458 AND action_information3 = rec_earnings.input_value_id
2459 AND action_information5 = ele_class_table(l_index).ele_context ;
2460
2461 EXCEPTION WHEN NO_DATA_FOUND THEN
2462 -- archive the element definitio as it has not been archived
2463 pay_action_information_api.create_action_information (
2464 p_action_information_id => l_action_info_id
2465 ,p_action_context_id => p_payroll_action_id
2466 ,p_action_context_type => 'PA'
2467 ,p_object_version_number => l_ovn
2468 ,p_effective_date => p_effective_date
2469 ,p_source_id => NULL
2470 ,p_source_text => NULL
2471 ,p_action_information_category => 'NO ELEMENT DEFINITION'
2472 ,p_action_information1 => p_pre_payact_id
2473 ,p_action_information2 => rec_earnings.element_type_id
2474 ,p_action_information3 => rec_earnings.input_value_id
2475 ,p_action_information4 => rec_earnings.rep_name
2476 ,p_action_information5 => ele_class_table(l_index).ele_context
2477 ,p_action_information6 => rec_earnings.uom
2478 ,p_action_information7 => ele_class_table(l_index).ele_context
2479 ,p_action_information8 => l_prim_def_bal_id
2480 ,p_action_information9 => 'PBAL'
2481 ,p_action_information10 => l_holiday_text
2482 ,p_action_information11 => l_basis_text
2483 ,p_action_information12 => rec_earnings.ele_class );
2484 WHEN OTHERS THEN
2485 NULL;
2486 END;
2487
2488 END IF; -- end if clssification check for 'Information'
2489 END;
2490 END LOOP;
2491
2492 END LOOP;
2493
2494 IF g_debug THEN
2495 hr_utility.set_location(' Leaving Procedure ARCHIVE_ELEMENT_INFO',220);
2496 END IF;
2497
2498 EXCEPTION WHEN OTHERS THEN
2499 g_err_num := SQLCODE;
2500 /*fnd_file.put_line(fnd_file.log,'ORA_ERR: ' || g_err_num || 'In ARCHIVE_ELEMENT_INFO');*/
2501
2502 IF g_debug THEN
2503 hr_utility.set_location('ORA_ERR: ' || g_err_num || 'ARCHIVE_ELEMENT_INFO',230);
2504 END IF;
2505
2506 END ARCHIVE_ELEMENT_INFO;
2507
2508
2509 ------------------------------------ FUNCTION GET_DEFINED_BALANCE_ID --------------------------------------------------------------
2510
2511 /* GET DEFINED BALANCE ID */
2512
2513 FUNCTION GET_DEFINED_BALANCE_ID(p_user_name IN VARCHAR2) RETURN NUMBER
2514 IS
2515
2516 /* Cursor to retrieve Defined Balance Id */
2517
2518 CURSOR csr_def_bal_id(p_user_name VARCHAR2) IS
2519 SELECT u.creator_id
2520 FROM ff_user_entities u,
2521 ff_database_items d
2522 WHERE d.user_name = p_user_name
2523 AND u.user_entity_id = d.user_entity_id
2524 AND (u.legislation_code = 'NO' )
2525 AND (u.business_group_id IS NULL )
2526 AND u.creator_type = 'B';
2527
2528 l_defined_balance_id ff_user_entities.user_entity_id%TYPE;
2529
2530 BEGIN
2531
2532 IF g_debug THEN
2533 hr_utility.set_location(' Entering Function GET_DEFINED_BALANCE_ID',240);
2534 END IF;
2535
2536 OPEN csr_def_bal_id(p_user_name);
2537 FETCH csr_def_bal_id INTO l_defined_balance_id;
2538 CLOSE csr_def_bal_id;
2539 RETURN l_defined_balance_id;
2540
2541 IF g_debug THEN
2542 hr_utility.set_location(' Leaving Function GET_DEFINED_BALANCE_ID',250);
2543 END IF;
2544
2545 END GET_DEFINED_BALANCE_ID;
2546
2547 --------------------------------------------------------------------------------------------------
2548
2549
2550 /* PAYMENT INFORMATION REGION */
2551 PROCEDURE ARCHIVE_PAYMENT_INFO(p_archive_assact_id IN NUMBER,
2552 p_prepay_assact_id IN NUMBER,
2553 p_assignment_id IN NUMBER,
2554 p_date_earned IN DATE,
2555 p_effective_date IN DATE)
2556 IS
2557 -------------
2558 /* Cursor to fetch ppm and opm ids to check which payment method to archive */
2559 CURSOR csr_chk(p_prepay_assact_id NUMBER) IS
2560 SELECT personal_payment_method_id ppm_id,
2561 org_payment_method_id opm_id
2562 FROM pay_pre_payments
2563 WHERE assignment_action_id = p_prepay_assact_id;
2564
2565 ------------
2566 /* Cursor to check if bank details are attached with ppm */
2567 CURSOR csr_chk_bank(p_ppm_id NUMBER) IS
2568 SELECT ppm.external_account_id
2569 FROM pay_personal_payment_methods_f ppm
2570 WHERE ppm.personal_payment_method_id = p_ppm_id
2571 AND p_date_earned BETWEEN ppm.effective_start_date AND ppm.effective_end_date;
2572
2573 -------------
2574 /* Cursor to retrieve Organization Payment Method Information */
2575 CURSOR csr_get_org_pay(p_prepay_assact_id NUMBER, opm_id NUMBER) IS
2576 SELECT pop.org_payment_method_id opm_id,
2577 pop.org_payment_method_name opm_name,
2578 ppttl.payment_type_name pay_type,
2579 ppp.value value
2580 FROM pay_org_payment_methods_f pop,
2581 pay_assignment_actions paa,
2582 pay_payment_types ppt,
2583 pay_payment_types_tl ppttl,
2584 pay_pre_payments ppp
2585 WHERE paa.assignment_action_id = p_prepay_assact_id
2586 AND ppt.payment_type_id = pop.payment_type_id
2587 AND ppt.payment_type_id = ppttl.payment_type_id
2588 AND ppttl.language = userenv('LANG')
2589 AND ppp.org_payment_method_id = pop.org_payment_method_id
2590 AND pop.org_payment_method_id = opm_id
2591 AND ppp.assignment_action_id = paa.assignment_action_id
2592 AND p_date_earned BETWEEN pop.effective_start_date AND pop.effective_end_date;
2593
2594 -------------
2595 /* Cursor to retrieve Personal Payment Method Info*/
2596 CURSOR csr_get_pers_pay(p_prepay_assact_id NUMBER, ppm_id NUMBER) IS
2597 SELECT pea.segment1 bank_name,
2598 pea.segment2 branch,
2599 pea.segment3 acct_name,
2600 pea.segment4 acc_type,
2601 pea.segment5 acc_curr,
2602 pea.segment6 acct_num,
2603 ppm.org_payment_method_id opm_id,
2604 pop.external_account_id,
2605 pop.org_payment_method_name opm_name,
2606 ppm.personal_payment_method_id ppm_id,
2607 ppttl.payment_type_name pay_type,
2608 ppp.value value
2609 FROM pay_external_accounts pea,
2610 pay_org_payment_methods_f pop,
2611 pay_personal_payment_methods_f ppm,
2612 pay_assignment_actions paa,
2613 pay_payment_types ppt,
2614 pay_payment_types_tl ppttl,
2615 pay_pre_payments ppp
2616 WHERE
2617 -- pea.id_flex_num=20 AND
2618 pea.external_account_id = NVL(ppm.external_account_id,pop.external_account_id)
2619 AND paa.assignment_action_id = p_prepay_assact_id
2620 AND paa.assignment_id = ppm.assignment_id
2621 AND ppm.org_payment_method_id = pop.org_payment_method_id
2622 AND ppm.personal_payment_method_id = ppm_id
2623 AND ppt.payment_type_id = pop.payment_type_id
2624 AND ppt.payment_type_id = ppttl.payment_type_id
2625 AND ppttl.language = userenv('LANG')
2626 AND ppp.assignment_action_id = paa.assignment_action_id
2627 AND ppp.personal_payment_method_id = ppm.personal_payment_method_id
2628 AND p_date_earned BETWEEN pop.effective_start_date AND pop.effective_end_date
2629 AND p_date_earned BETWEEN ppm.effective_start_date AND ppm.effective_end_date;
2630 -------------
2631 l_bank_reg_num VARCHAR2(50);
2632 l_action_info_id NUMBER;
2633 l_ovn NUMBER;
2634 l_org NUMBER;
2635 l_pers VARCHAR2(40) := NULL;
2636 l_ext_acct NUMBER;
2637 rec_chk csr_chk%ROWTYPE;
2638 l_pay_value VARCHAR2(50) := NULL;
2639 l_bank_name VARCHAR2(240);
2640 l_acc_name VARCHAR2(240);
2641 l_acc_type VARCHAR2(240);
2642 l_acc_curr VARCHAR2(240);
2643
2644 ------------
2645
2646 BEGIN
2647
2648 IF g_debug THEN
2649 hr_utility.set_location(' Entering Procedure ARCHIVE_PAYMENT_INFO',260);
2650 END IF;
2651
2652 /*fnd_file.put_line(fnd_file.log,'In ARCHIVE_PAYMENT_INFO 1');*/
2653
2654 OPEN csr_chk(p_prepay_assact_id);
2655 LOOP
2656 FETCH csr_chk INTO rec_chk;
2657 EXIT WHEN csr_chk%NOTFOUND;
2658
2659 IF rec_chk.ppm_id IS NOT NULL THEN
2660 FOR rec_pers_pay IN csr_get_pers_pay(p_prepay_assact_id,rec_chk.ppm_id) LOOP
2661
2662 OPEN csr_chk_bank(rec_chk.ppm_id);
2663 FETCH csr_chk_bank INTO l_ext_acct;
2664 CLOSE csr_chk_bank;
2665
2666 l_pay_value := to_char (rec_pers_pay.value,g_format_mask);
2667
2668 IF (l_ext_acct IS NOT NULL) THEN
2669
2670 /*fnd_file.put_line(fnd_file.log,'In ARCHIVE_PAYMENT_INFO 2');*/
2671
2672 -- l_bank_reg_num := rec_pers_pay.bank_reg_num;
2673 -- l_bank_reg_num := rec_pers_pay.bank_reg_num || ' ' || hr_general.decode_lookup('HR_NO_BANK_REGISTRATION',rec_pers_pay.bank_reg_num);
2674
2675 l_bank_name := hr_general.decode_lookup('HR_NO_BANK',rec_pers_pay.bank_name);
2676 l_acc_name := hr_general.decode_lookup('HR_NO_ACCOUNT_NAME',rec_pers_pay.acct_name);
2677 l_acc_type := hr_general.decode_lookup('HR_NO_ACCOUNT_TYPE',rec_pers_pay.acc_type);
2678 l_acc_curr := rec_pers_pay.acc_curr ;
2679
2680
2681 pay_action_information_api.create_action_information (
2682 p_action_information_id => l_action_info_id
2683 ,p_action_context_id => p_archive_assact_id
2684 ,p_action_context_type => 'AAP'
2685 ,p_object_version_number => l_ovn
2686 ,p_effective_date => p_effective_date
2687 ,p_source_id => NULL
2688 ,p_source_text => NULL
2689 ,p_action_information_category => 'EMPLOYEE NET PAY DISTRIBUTION'
2690 ,p_action_information1 => rec_pers_pay.opm_id -- NULL
2691 ,p_action_information2 => rec_pers_pay.ppm_id
2692 ,p_action_information5 => l_bank_name
2693 ,p_action_information6 => rec_pers_pay.branch
2694 ,p_action_information7 => l_acc_name
2695 ,p_action_information8 => l_acc_type
2696 ,p_action_information9 => l_acc_curr
2697 ,p_action_information10 => rec_pers_pay.acct_num
2698 ,p_action_information11 => NULL
2699 ,p_action_information12 => NULL
2700 ,p_action_information13 => NULL
2701 ,p_action_information14 => NULL
2702 ,p_action_information15 => NULL
2703 ,p_action_information16 => fnd_number.number_to_canonical(rec_pers_pay.value) --l_pay_value
2704 ,p_action_information17 => NULL
2705 ,p_action_information18 => rec_pers_pay.opm_name -- rec_pers_pay.pay_type
2706 ,p_assignment_id => p_assignment_id);
2707 ELSE
2708
2709 /*fnd_file.put_line(fnd_file.log,'In ARCHIVE_PAYMENT_INFO 3');*/
2710
2711 pay_action_information_api.create_action_information (
2712 p_action_information_id => l_action_info_id
2713 ,p_action_context_id => p_archive_assact_id
2714 ,p_action_context_type => 'AAP'
2715 ,p_object_version_number => l_ovn
2716 ,p_effective_date => p_effective_date
2717 ,p_source_id => NULL
2718 ,p_source_text => NULL
2719 ,p_action_information_category => 'EMPLOYEE NET PAY DISTRIBUTION'
2720 ,p_action_information1 => rec_pers_pay.opm_id
2721 ,p_action_information2 => rec_pers_pay.ppm_id
2722 ,p_action_information5 => NULL
2723 ,p_action_information6 => NULL
2724 ,p_action_information7 => NULL
2725 ,p_action_information8 => NULL
2726 ,p_action_information9 => NULL
2727 ,p_action_information10 => NULL
2728 ,p_action_information11 => NULL
2729 ,p_action_information12 => NULL
2730 ,p_action_information13 => NULL
2731 ,p_action_information14 => NULL
2732 ,p_action_information15 => NULL
2733 ,p_action_information16 => fnd_number.number_to_canonical(rec_pers_pay.value) --l_pay_value
2734 ,p_action_information17 => NULL
2735 ,p_action_information18 => rec_pers_pay.opm_name -- rec_pers_pay.pay_type
2736 ,p_assignment_id => p_assignment_id);
2737 END IF;
2738 END LOOP;
2739 /*fnd_file.put_line(fnd_file.log,'In ARCHIVE_PAYMENT_INFO 4');*/
2740
2741 END IF;
2742
2743 IF (rec_chk.opm_id IS NOT NULL AND rec_chk.ppm_id IS NULL) THEN
2744
2745 /*fnd_file.put_line(fnd_file.log,'In ARCHIVE_PAYMENT_INFO 5');*/
2746
2747 FOR rec_org_pay IN csr_get_org_pay(p_prepay_assact_id,rec_chk.opm_id) LOOP
2748
2749 l_pay_value := to_char (rec_org_pay.value,g_format_mask);
2750
2751 pay_action_information_api.create_action_information (
2752 p_action_information_id => l_action_info_id
2753 ,p_action_context_id => p_archive_assact_id
2754 ,p_action_context_type => 'AAP'
2755 ,p_object_version_number => l_ovn
2756 ,p_effective_date => p_effective_date
2757 ,p_source_id => NULL
2758 ,p_source_text => NULL
2759 ,p_action_information_category => 'EMPLOYEE NET PAY DISTRIBUTION'
2760 ,p_action_information1 => rec_org_pay.opm_id
2761 ,p_action_information2 => NULL
2762 ,p_action_information5 => NULL
2763 ,p_action_information6 => NULL
2764 ,p_action_information7 => NULL
2765 ,p_action_information8 => NULL
2766 ,p_action_information9 => NULL
2767 ,p_action_information10 => NULL
2768 ,p_action_information11 => NULL
2769 ,p_action_information12 => NULL
2770 ,p_action_information13 => NULL
2771 ,p_action_information14 => NULL
2772 ,p_action_information15 => NULL
2773 ,p_action_information16 => fnd_number.number_to_canonical(rec_org_pay.value) --l_pay_value
2774 ,p_action_information17 => NULL
2775 ,p_action_information18 => rec_org_pay.opm_name -- rec_org_pay.pay_type
2776 ,p_assignment_id => p_assignment_id);
2777 END LOOP;
2778
2779 END IF;
2780
2781 END LOOP;
2782 CLOSE csr_chk;
2783
2784 IF g_debug THEN
2785 hr_utility.set_location(' Leaving Procedure ARCHIVE_PAYMENT_INFO',270);
2786 END IF;
2787
2788 EXCEPTION WHEN OTHERS THEN
2789 g_err_num := SQLCODE;
2790
2791 /*fnd_file.put_line(fnd_file.log,'ORA_ERR: ' || g_err_num || 'In ARCHIVE_PAYMENT_INFO');*/
2792
2793 IF g_debug THEN
2794 hr_utility.set_location('ORA_ERR: ' || g_err_num || 'In ARCHIVE_PAYMENT_INFO',280);
2795 END IF;
2796
2797 END ARCHIVE_PAYMENT_INFO;
2798 ----------------------------------------- PROCEDURE ARCHIVE_ACCRUAL_PLAN ---------------------------------------------------------
2799
2800 /* ACCRUALS REGION */
2801
2802 /* PROCEDURE ARCHIVE_ACCRUAL_PLAN ( p_assignment_id IN NUMBER
2803 ,p_date_earned IN DATE
2804 ,p_effective_date IN DATE
2805 ,p_archive_assact_id IN NUMBER
2806 ,p_run_assignment_action_id IN NUMBER
2807 ,p_period_end_date IN DATE
2808 ,p_period_start_date IN DATE
2809 )
2810 IS
2811 --
2812 -- Cursor to get the Leave Balance Details .
2813 CURSOR csr_leave_balance
2814 IS
2815 --
2816 SELECT pap.accrual_plan_name
2817 ,hr_general_utilities.get_lookup_meaning('US_PTO_ACCRUAL',pap.accrual_category)
2818 ,pap.accrual_units_of_measure
2819 ,ppa.payroll_id
2820 ,pap.business_group_id
2821 ,pap.accrual_plan_id
2822 FROM pay_accrual_plans pap
2823 ,pay_element_types_f pet
2824 ,pay_element_links_f pel
2825 ,pay_element_entries_f pee
2826 ,pay_assignment_actions paa
2827 ,pay_payroll_actions ppa
2828 WHERE pet.element_type_id = pap.accrual_plan_element_type_id
2829 AND pel.element_type_id = pet.element_type_id
2830 AND pee.element_link_id = pel.element_link_id
2831 AND paa.assignment_id = pee.assignment_id
2832 AND ppa.payroll_action_id = paa.payroll_action_id
2833 AND ppa.action_type IN ('R','Q')
2834 AND ppa.action_status = 'C'
2835 AND ppa.date_earned BETWEEN pet.effective_start_date
2836 AND pet.effective_end_date
2837 AND ppa.date_earned BETWEEN pel.effective_start_date
2838 AND pel.effective_end_date
2839 AND ppa.date_earned BETWEEN pee.effective_start_date
2840 AND pee.effective_end_date
2841 AND paa.assignment_id = p_assignment_id
2842 AND paa.assignment_action_id = p_run_assignment_action_id;
2843 --
2844 l_action_info_id NUMBER;
2845 l_accrual_plan_id pay_accrual_plans.accrual_plan_id%type;
2846 l_accrual_plan_name pay_accrual_plans.accrual_plan_name%type;
2847 l_accrual_category pay_accrual_plans.accrual_category%type;
2848 l_accrual_uom pay_accrual_plans.accrual_units_of_measure%type;
2849 l_payroll_id pay_all_payrolls_f.payroll_id%type;
2850 l_business_group_id NUMBER;
2851 l_effective_date DATE;
2852 l_annual_leave_balance NUMBER;
2853 l_ovn NUMBER;
2854 l_leave_taken NUMBER;
2855 l_start_date DATE;
2856 l_end_date DATE;
2857 l_accrual_end_date DATE;
2858 l_accrual NUMBER;
2859 l_total_leave_taken NUMBER;
2860 l_procedure VARCHAR2(100) := g_package || '.archive_accrual_details';
2861 --
2862 BEGIN
2863 --
2864 IF g_debug THEN
2865 hr_utility.set_location(' Entering Procedure ARCHIVE_ACCRUAL_PLAN',290);
2866 END IF;
2867 OPEN csr_leave_balance;
2868 FETCH csr_leave_balance INTO
2869 l_accrual_plan_name
2870 ,l_accrual_category
2871 ,l_accrual_uom
2872 ,l_payroll_id
2873 ,l_business_group_id
2874 ,l_accrual_plan_id;
2875 IF csr_leave_balance%FOUND THEN
2876 --
2877 -- Call to get annual leave balance
2878 per_accrual_calc_functions.get_net_accrual
2879 (
2880 p_assignment_id => p_assignment_id -- number in
2881 ,p_plan_id => l_accrual_plan_id -- number in
2882 ,p_payroll_id => l_payroll_id -- number in
2883 ,p_business_group_id => l_business_group_id -- number in
2884 ,p_calculation_date => p_date_earned -- date in
2885 ,p_start_date => l_start_date -- date out
2886 ,p_end_date => l_end_date -- date out
2887 ,p_accrual_end_date => l_accrual_end_date -- date out
2888 ,p_accrual => l_accrual -- number out
2889 ,p_net_entitlement => l_annual_leave_balance -- number out
2890 );
2891 IF l_annual_leave_balance IS NULL THEN
2892 --
2893 l_annual_leave_balance := 0;
2894 --
2895 END IF;
2896 l_leave_taken := per_accrual_calc_functions.get_absence
2897 (
2898 p_assignment_id
2899 ,l_accrual_plan_id
2900 ,p_period_end_date
2901 ,p_period_start_date
2902 );
2903 l_ovn :=1;
2904 IF l_accrual_plan_name IS NOT NULL THEN
2905 --
2906 pay_action_information_api.create_action_information (
2907 p_action_information_id => l_action_info_id
2908 ,p_action_context_id => p_archive_assact_id
2909 ,p_action_context_type => 'AAP'
2910 ,p_object_version_number => l_ovn
2911 ,p_effective_date => p_effective_date
2912 ,p_source_id => NULL
2913 ,p_source_text => NULL
2914 ,p_action_information_category => 'EMPLOYEE ACCRUALS'
2915 ,p_action_information4 => l_accrual_plan_name
2916 ,p_action_information5 => fnd_number.number_to_canonical(l_leave_taken)
2917 ,p_action_information6 => fnd_number.number_to_canonical(l_annual_leave_balance)
2918 ,p_assignment_id => p_assignment_id);
2919 --
2920 END IF;
2921 --
2922 --
2923 END IF;
2924 --
2925 CLOSE csr_leave_balance;
2926 IF g_debug THEN
2927 hr_utility.set_location(' Leaving Procedure ARCHIVE_ACCRUAL_PLAN',300);
2928 END IF;
2929 --
2930 EXCEPTION
2931 WHEN OTHERS THEN
2932 IF csr_leave_balance%ISOPEN THEN
2933 --
2934 CLOSE csr_leave_balance;
2935 --
2936 END IF;
2937 --
2938 g_err_num := SQLCODE;
2939 --fnd_file.put_line(fnd_file.log,'ORA_ERR: ' || g_err_num || 'In ARCHIVE_ACCRUAL_PLAN');
2940 IF g_debug THEN
2941 hr_utility.set_location('ORA_ERR: ' || g_err_num || ' In ARCHIVE_ACCRUAL_PLAN',310);
2942 END IF;
2943 RAISE;
2944 END ARCHIVE_ACCRUAL_PLAN;*/
2945
2946 ----------------------------------- PROCEDURE ARCHIVE_ADD_ELEMENT ---------------------------------------------------------------
2947
2948 /* ADDITIONAL ELEMENTS REGION */
2949
2950 PROCEDURE ARCHIVE_ADD_ELEMENT(p_archive_assact_id IN NUMBER,
2951 p_assignment_action_id IN NUMBER,
2952 p_assignment_id IN NUMBER,
2953 p_payroll_action_id IN NUMBER,
2954 p_date_earned IN DATE,
2955 p_effective_date IN DATE,
2956 p_pre_payact_id IN NUMBER,
2957 p_archive_flag IN VARCHAR2) IS
2958
2959 ------------------------------
2960 /* Cursor to retrieve Additional Element Information */
2961
2962 /*
2963 CURSOR csr_get_element(p_bus_grp_id NUMBER) IS
2964 SELECT hoi.org_information2 element_type_id
2965 ,hoi.org_information3 input_value_id
2966 ,hoi.org_information7 element_narrative
2967 ,pec.classification_name
2968 ,piv.uom
2969 FROM hr_organization_information hoi
2970 ,pay_element_classifications pec
2971 ,pay_element_types_f pet
2972 ,pay_input_values_f piv
2973 WHERE hoi.organization_id = p_bus_grp_id
2974 AND hoi.org_information_context = 'Business Group:Payslip Info'
2975 AND hoi.org_information1 = 'ELEMENT'
2976 AND hoi.org_information2 = pet.element_type_id
2977 AND pec.classification_id = pet.classification_id
2978 AND piv.input_value_id = hoi.org_information3
2979 AND p_date_earned BETWEEN piv.effective_start_date AND piv.effective_end_date;
2980
2981 */
2982
2983 /*
2984 CURSOR csr_get_add_elements (p_payroll_action_id NUMBER ) IS
2985 SELECT action_information2 element_type_id
2986 ,action_information3 input_value_id
2987 ,action_information4 element_narrative
2988 ,action_information5 element_context
2989 ,action_information6 uom
2990 ,action_information8 prim_def_bal_id
2991 ,action_information12 ele_class
2992 FROM pay_action_information
2993 WHERE action_context_id = p_payroll_action_id
2994 AND action_information_category = 'NO ELEMENT DEFINITION'
2995 AND action_context_type = 'PA'
2996 AND action_information5 = 'F' ;
2997 */
2998
2999 CURSOR csr_get_add_elements (p_payroll_action_id NUMBER ) IS
3000 SELECT action_information2 element_type_id
3001 ,action_information3 input_value_id
3002 ,action_information4 element_narrative
3003 ,action_information5 element_context
3004 ,action_information6 uom
3005 ,action_information8 prim_def_bal_id
3006 ,action_information12 ele_class
3007 ,action_information13 lookup_type
3008 ,action_information14 value_set_id
3009 FROM pay_action_information
3010 WHERE action_context_id = p_payroll_action_id
3011 AND action_information_category = 'NO ELEMENT DEFINITION'
3012 AND action_context_type = 'PA'
3013 AND action_information5 = 'F' ;
3014
3015
3016 ----------------------------------------------------------
3017
3018 /* cursor to get he tax unit id (Legal Employer) from assignment action id */
3019
3020 CURSOR csr_get_le_org_id (p_assignment_action_id NUMBER) IS
3021 SELECT tax_unit_id
3022 FROM pay_assignment_actions
3023 WHERE assignment_action_id = p_assignment_action_id ;
3024
3025 --------------------------------------------------------
3026
3027 /* cursor to get the element code */
3028
3029 /*
3030 CURSOR csr_ele_code (p_ele_type_id NUMBER , p_le_org_id NUMBER ) IS
3031 select eei_information1
3032 from pay_element_type_extra_info
3033 where element_type_id = p_ele_type_id
3034 and ( eei_information2 = p_le_org_id OR eei_information2 is null )
3035 and information_type = 'NO_ELEMENT_CODES'
3036 and eei_information_category = 'NO_ELEMENT_CODES'
3037 and rownum = 1
3038 order by eei_information2 , element_type_extra_info_id ;
3039 */
3040
3041 -- modifying the above cursor
3042
3043 cursor csr_ele_code(p_ele_type_id NUMBER , p_leg_emp_id NUMBER ) is
3044 select nvl((select eei_information1 from pay_element_type_extra_info petei
3045 where petei.information_type='NO_ELEMENT_CODES'
3046 and element_type_id = p_ele_type_id
3047 and petei.eei_information2 = p_leg_emp_id
3048 and rownum=1),
3049 (select eei_information1 from pay_element_type_extra_info petei
3050 where petei.information_type='NO_ELEMENT_CODES'
3051 and element_type_id = p_ele_type_id
3052 and eei_information2 is null
3053 and rownum=1)) from dual;
3054
3055 --------------------------------------------------------
3056
3057 /* cursor to get the further element entry info for payslip information */
3058
3059 CURSOR csr_payslip_info (p_ele_entry_id NUMBER) IS
3060 SELECT ENTRY_INFORMATION1
3061 FROM pay_element_entries_f
3062 where ELEMENT_ENTRY_ID = p_ele_entry_id ;
3063
3064 -------------------------------
3065 /* Cursor to retrieve run result value of Additional Elements */
3066 CURSOR csr_result_value(p_iv_id NUMBER
3067 ,p_ele_type_id NUMBER
3068 ,p_assignment_action_id NUMBER) IS
3069 SELECT rrv.result_value val
3070 ,rr.element_entry_id ele_entry_id
3071 FROM pay_run_result_values rrv
3072 ,pay_run_results rr
3073 ,pay_assignment_actions paa
3074 ,pay_payroll_actions ppa
3075 WHERE rrv.input_value_id = p_iv_id
3076 AND rr.element_type_id = p_ele_type_id
3077 AND rr.run_result_id = rrv.run_result_id
3078 AND rr.assignment_action_id = paa.assignment_action_id
3079 AND paa.assignment_action_id = p_assignment_action_id
3080 AND ppa.payroll_action_id = paa.payroll_action_id
3081 AND ppa.action_type IN ('Q','R')
3082 AND rrv.result_value IS NOT NULL;
3083 ------------------------------
3084
3085 -- rec_get_element csr_get_element%ROWTYPE;
3086 rec_get_add_element csr_get_add_elements%ROWTYPE;
3087
3088 l_result_value pay_run_result_values.result_value%TYPE := 0;
3089 l_balance_value NUMBER := 0;
3090 l_action_info_id NUMBER;
3091 l_ovn NUMBER;
3092 l_element_context VARCHAR2(10);
3093 l_index NUMBER := 0;
3094 l_formatted_value VARCHAR2(50) := NULL;
3095 l_flag NUMBER := 0;
3096 l_le_org_id NUMBER;
3097 l_ele_code VARCHAR2(240) := NULL ;
3098 l_ele_entry_id NUMBER;
3099 l_payslip_info varchar2(240);
3100 l_archive VARCHAR2(2);
3101 l_arhive_prim_bal VARCHAR2(5);
3102 ------------------------------
3103
3104 BEGIN
3105
3106 IF g_debug THEN
3107 hr_utility.set_location(' Entering Procedure ARCHIVE_ADD_ELEMENT',320);
3108 END IF;
3109
3110 IF p_archive_flag = 'N' THEN
3111 ---------------------------------------------------
3112 --Check if global table has already been populated
3113 ---------------------------------------------------
3114 IF g_element_table.count = 0 THEN
3115
3116 /*
3117 OPEN csr_get_element(g_business_group_id);
3118 LOOP
3119 FETCH csr_get_element INTO rec_get_element;
3120 EXIT WHEN csr_get_element%NOTFOUND;
3121
3122 l_element_context := 'F'; --Additional Element Context
3123 SETUP_ELEMENT_DEFINITIONS(p_classification_name => rec_get_element.classification_name
3124 ,p_element_name => rec_get_element.element_narrative
3125 ,p_element_type_id => rec_get_element.element_type_id
3126 ,p_input_value_id => rec_get_element.input_value_id
3127 ,p_element_type => l_element_context
3128 ,p_uom => rec_get_element.uom
3129 ,p_archive_flag => p_archive_flag);
3130
3131 END LOOP;
3132 CLOSE csr_get_element;
3133 */
3134
3135 OPEN csr_get_add_elements (p_payroll_action_id);
3136 LOOP
3137 FETCH csr_get_add_elements INTO rec_get_add_element;
3138 EXIT WHEN csr_get_add_elements%NOTFOUND;
3139
3140 l_element_context := 'F'; --Additional Element Context
3141 SETUP_ELEMENT_DEFINITIONS(p_classification_name => rec_get_add_element.ele_class
3142 ,p_element_name => rec_get_add_element.element_narrative
3143 ,p_element_type_id => rec_get_add_element.element_type_id
3144 ,p_input_value_id => rec_get_add_element.input_value_id
3145 ,p_element_type => l_element_context
3146 ,p_uom => rec_get_add_element.uom
3147 ,p_archive_flag => p_archive_flag
3148 ,p_prim_bal_def_bal_id => rec_get_add_element.prim_def_bal_id
3149 ,p_lookup_type => rec_get_add_element.lookup_type
3150 ,p_value_set_id => rec_get_add_element.value_set_id );
3151
3152 END LOOP;
3153 CLOSE csr_get_add_elements;
3154
3155
3156 END IF;
3157
3158 ELSIF p_archive_flag = 'Y' AND g_element_table.count > 0 THEN
3159
3160 FOR l_index IN g_element_table.first.. g_element_table.last LOOP
3161 l_result_value := NULL;
3162
3163 BEGIN
3164
3165 l_balance_value := NULL ;
3166
3167 IF (g_element_table(l_index).prim_bal_def_bal_id IS NOT NULL) THEN
3168
3169 -- get the primary balance ytd value for the element
3170 l_balance_value := pay_balance_pkg.get_value(g_element_table(l_index).prim_bal_def_bal_id , p_assignment_action_id );
3171
3172 END IF;
3173
3174 /* get the element run result value */
3175 OPEN csr_result_value(g_element_table(l_index).input_value_id
3176 ,g_element_table(l_index).element_type_id
3177 ,p_assignment_action_id);
3178 LOOP
3179 FETCH csr_result_value INTO l_result_value , l_ele_entry_id ;
3180
3181 IF (csr_result_value%NOTFOUND) AND
3182 (csr_result_value%ROWCOUNT < 1) AND
3183 (g_element_table(l_index).prim_bal_def_bal_id IS NOT NULL) AND
3184 (l_balance_value <> 0) THEN
3185
3186 l_arhive_prim_bal := 'Y' ;
3187
3188 END IF ;
3189
3190 EXIT WHEN csr_result_value%NOTFOUND;
3191
3192 IF (csr_result_value%ROWCOUNT > 1) THEN
3193 l_balance_value := NULL ;
3194 END IF;
3195
3196 -- fnd_file.put_line(fnd_file.log,'------ before : l_result_value = ' || l_result_value);
3197
3198 -- check if a lookup was attached to the input value and fetch the meaning
3199
3200 IF (l_result_value is not null) AND (g_element_table(l_index).lookup_type is not null) THEN
3201
3202 l_result_value := hr_general.decode_lookup(g_element_table(l_index).lookup_type , l_result_value);
3203
3204 ELSIF (l_result_value is not null) AND (g_element_table(l_index).value_set_id is not null) THEN
3205
3206 l_result_value := pay_input_values_pkg.decode_vset_value( g_element_table(l_index).value_set_id ,l_result_value );
3207
3208 END IF ;
3209
3210 -- fnd_file.put_line(fnd_file.log,'after : l_result_value = ' || l_result_value);
3211
3212 -- l_balance_value := NULL ;
3213
3214 -- l_balance_value := pay_balance_pkg.get_value(g_element_table(l_index).prim_bal_def_bal_id , p_assignment_action_id );
3215
3216 -- fnd_file.put_line(fnd_file.log,'l_result_value = ' || l_result_value);
3217 -- fnd_file.put_line(fnd_file.log,'l_ele_entry_id = ' || l_ele_entry_id);
3218
3219
3220 -- modifying the below condition to get the ytd value for primary balance
3221 -- only once in case of multiple element entries
3222
3223 -- IF (g_element_table(l_index).prim_bal_def_bal_id IS NOT NULL) THEN
3224
3225 /*
3226 IF (g_element_table(l_index).prim_bal_def_bal_id IS NOT NULL) and (csr_result_value%ROWCOUNT = 1) THEN
3227
3228 -- get the primary balance ytd value for the element
3229 l_balance_value := pay_balance_pkg.get_value(g_element_table(l_index).prim_bal_def_bal_id , p_assignment_action_id );
3230
3231 END IF;
3232 */
3233
3234 -- fnd_file.put_line(fnd_file.log,'csr_rec.prim_def_bal_id = ' || g_element_table(l_index).prim_bal_def_bal_id);
3235 -- fnd_file.put_line(fnd_file.log,'l_balance_value = ' || l_balance_value);
3236
3237 /* get the legal employer */
3238 OPEN csr_get_le_org_id (p_assignment_action_id );
3239 FETCH csr_get_le_org_id INTO l_le_org_id ;
3240 CLOSE csr_get_le_org_id ;
3241
3242 -- fnd_file.put_line(fnd_file.log,'l_le_org_id = ' || l_le_org_id);
3243
3244 /* get the element code from the legal employer */
3245 OPEN csr_ele_code (g_element_table(l_index).element_type_id , l_le_org_id );
3246 FETCH csr_ele_code INTO l_ele_code ;
3247 CLOSE csr_ele_code ;
3248
3249 /*
3250 IF (l_ele_code IS NOT NULL) THEN
3251 l_ele_code := hr_general.decode_lookup('NO_ELEMENT_CODES',l_ele_code);
3252 END IF;
3253 */
3254
3255 -- fnd_file.put_line(fnd_file.log,'l_ele_code = ' || l_ele_code);
3256
3257 /* get the payslip information */
3258 l_payslip_info := NULL ;
3259
3260 OPEN csr_payslip_info (l_ele_entry_id );
3261 FETCH csr_payslip_info INTO l_payslip_info ;
3262 CLOSE csr_payslip_info ;
3263
3264 -- fnd_file.put_line(fnd_file.log,'l_payslip_info = ' || l_payslip_info);
3265
3266 IF (l_result_value is not null) THEN l_archive := 'Y';
3267 -- ELSIF (g_element_table(l_index).prim_bal_def_bal_id IS NOT NULL) AND ( (l_balance_value <> 0) OR (l_balance_value IS NOT NULL) ) THEN l_archive := 'Y';
3268 ELSE l_archive := 'N';
3269 END IF;
3270
3271
3272 IF (l_archive = 'Y') THEN
3273 -- IF l_result_value is not null THEN
3274
3275 -- fnd_file.put_line(fnd_file.log,'l_result_value is not null ');
3276
3277 pay_action_information_api.create_action_information (
3278 p_action_information_id => l_action_info_id
3279 ,p_action_context_id => p_archive_assact_id
3280 ,p_action_context_type => 'AAP'
3281 ,p_object_version_number => l_ovn
3282 ,p_effective_date => p_effective_date
3283 ,p_source_id => NULL
3284 ,p_source_text => NULL
3285 ,p_action_information_category => 'NO ELEMENT INFO'
3286 ,p_action_information1 => g_element_table(l_index).element_type_id
3287 ,p_action_information2 => g_element_table(l_index).input_value_id
3288 ,p_action_information3 => g_element_table(l_index).element_type
3289 ,p_action_information4 => l_result_value --l_formatted_value
3290 ,p_action_information9 => 'Additional Element'
3291 ,p_action_information10 => g_element_table(l_index).prim_bal_def_bal_id
3292 ,p_action_information11 => 'PBAL'
3293 ,p_action_information12 => l_balance_value
3294 ,p_action_information13 => l_ele_code
3295 ,p_action_information14 => l_payslip_info
3296 ,p_assignment_id => p_assignment_id);
3297 END IF;
3298
3299 END LOOP;
3300 CLOSE csr_result_value;
3301
3302 -- archive results if run result was NULL but primary balance value is not null
3303
3304 IF (l_arhive_prim_bal = 'Y') THEN
3305
3306 -- though the run result is NULL, the ytd balance has a value
3307 -- so we will archive this result
3308
3309 l_result_value := NULL ;
3310 l_payslip_info := NULL ;
3311
3312 -- fnd_file.put_line(fnd_file.log,'LOC 5 => p_assignment_action_id , l_element_name = ' || p_assignment_action_id ||' , '||l_element_name);
3313
3314 pay_action_information_api.create_action_information (
3315 p_action_information_id => l_action_info_id
3316 ,p_action_context_id => p_archive_assact_id
3317 ,p_action_context_type => 'AAP'
3318 ,p_object_version_number => l_ovn
3319 ,p_effective_date => p_effective_date
3320 ,p_source_id => NULL
3321 ,p_source_text => NULL
3322 ,p_action_information_category => 'NO ELEMENT INFO'
3323 ,p_action_information1 => g_element_table(l_index).element_type_id
3324 ,p_action_information2 => g_element_table(l_index).input_value_id
3325 ,p_action_information3 => g_element_table(l_index).element_type
3326 ,p_action_information4 => l_result_value --l_formatted_value
3327 ,p_action_information9 => 'Additional Element'
3328 ,p_action_information10 => g_element_table(l_index).prim_bal_def_bal_id
3329 ,p_action_information11 => 'PBAL'
3330 ,p_action_information12 => l_balance_value
3331 ,p_action_information13 => l_ele_code
3332 ,p_action_information14 => l_payslip_info
3333 ,p_assignment_id => p_assignment_id);
3334
3335 END IF ;
3336
3337 EXCEPTION WHEN OTHERS THEN
3338 g_err_num := SQLCODE;
3339 /*fnd_file.put_line(fnd_file.log,'ORA_ERR: ' || g_err_num || 'In ARCHIVE_ADD_ELEMENT');*/
3340
3341 IF g_debug THEN
3342 hr_utility.set_location('ORA_ERR: ' || g_err_num || 'In ARCHIVE_ADD_ELEMENT',330);
3343 END IF;
3344 END;
3345 END LOOP;
3346
3347 END IF;
3348
3349 IF g_debug THEN
3350 hr_utility.set_location(' Leaving Procedure ARCHIVE_ADD_ELEMENT',340);
3351 END IF;
3352
3353 END ARCHIVE_ADD_ELEMENT;
3354
3355 ---------------------------------- PROCEDURE ARCHIVE_OTH_BALANCE ----------------------------------------------------------------
3356
3357 /* OTHER BALANCES REGION */
3358
3359 PROCEDURE ARCHIVE_OTH_BALANCE (p_archive_assact_id IN NUMBER,
3360 p_assignment_action_id IN NUMBER,
3361 p_assignment_id IN NUMBER,
3362 p_payroll_action_id IN NUMBER,
3363 p_record_count IN NUMBER,
3364 p_pre_payact_id IN NUMBER,
3365 p_effective_date IN DATE,
3366 p_date_earned IN DATE,
3367 p_archive_flag IN VARCHAR2) IS
3368
3369 ------------------
3370 /* Cursor to retrieve Other Balances Information */
3371 CURSOR csr_get_balance(p_bus_grp_id NUMBER) IS
3372 SELECT org_information4 balance_type_id
3373 ,org_information5 balance_dim_id
3374 ,org_information7 narrative
3375 FROM hr_organization_information
3376 WHERE organization_id = p_bus_grp_id
3377 AND org_information_context = 'Business Group:Payslip Info'
3378 AND org_information1 = 'BALANCE';
3379
3380 -----------------
3381 /* Cursor to retrieve Tax Unit Id for setting context */
3382 CURSOR csr_tax_unit (p_run_assact_id NUMBER) IS
3383 SELECT paa.tax_unit_id
3384 FROM pay_assignment_actions paa
3385 WHERE paa.assignment_action_id = p_run_assact_id;
3386 -----------------
3387 /* Cursor to fetch defined balance id */
3388 CURSOR csr_def_balance(bal_type_id NUMBER, bal_dim_id NUMBER) IS
3389 SELECT defined_balance_id
3390 FROM pay_defined_balances
3391 WHERE balance_type_id = bal_type_id
3392 AND balance_dimension_id = bal_dim_id;
3393 ----------------
3394 rec_get_balance csr_get_balance%ROWTYPE;
3395 l_balance_value NUMBER := 0;
3396 l_action_info_id NUMBER;
3397 l_ovn NUMBER;
3398 l_index NUMBER;
3399 l_tu_id NUMBER;
3400 l_defined_balance_id NUMBER :=0;
3401 l_formatted_value VARCHAR2(50) := NULL;
3402 l_flag NUMBER := 0;
3403
3404 ---------------
3405
3406 BEGIN
3407
3408 IF g_debug THEN
3409 hr_utility.set_location(' Entering Procedure ARCHIVE_OTH_BALANCE',350);
3410 END IF;
3411
3412 -- fnd_file.put_line(fnd_file.log,'In ARCHIVE_OTH_BALANCE 0');
3413 /*fnd_file.put_line(fnd_file.log,'Entering In ARCHIVE_OTH_BALANCE global');*/
3414
3415 IF p_archive_flag = 'N' THEN
3416 ---------------------------------------------------
3417 --Check if global table has already been populated
3418 ---------------------------------------------------
3419 IF g_user_balance_table.count = 0 THEN
3420 OPEN csr_get_balance(g_business_group_id);
3421 LOOP
3422 FETCH csr_get_balance INTO rec_get_balance;
3423 EXIT WHEN csr_get_balance%NOTFOUND;
3424
3425 OPEN csr_def_balance(rec_get_balance.balance_type_id,rec_get_balance.balance_dim_id);
3426 FETCH csr_def_balance INTO l_defined_balance_id;
3427 CLOSE csr_def_balance;
3428
3429 PAY_NO_PAYSLIP_ARCHIVE.SETUP_BALANCE_DEFINITIONS
3430 (p_balance_name => rec_get_balance.narrative
3431 ,p_defined_balance_id => l_defined_balance_id
3432 ,p_balance_type_id => rec_get_balance.balance_type_id);
3433 END LOOP;
3434 CLOSE csr_get_balance;
3435 END IF;
3436
3437 ---------------------------------------------------
3438 -- For Tax Card details ,
3439 -- Check if global table has already been populated
3440 -- if not then populate the values
3441 ---------------------------------------------------
3442 IF g_tax_card_tab.count = 0 THEN
3443
3444 g_tax_card_tab(1).inp_val_name := 'Method of Receipt';
3445 g_tax_card_tab(2).inp_val_name := 'Tax Municipality';
3446 g_tax_card_tab(3).inp_val_name := 'Tax Card Type';
3447 g_tax_card_tab(4).inp_val_name := 'Tax Percentage';
3448 g_tax_card_tab(5).inp_val_name := 'Tax Table Number';
3449 g_tax_card_tab(6).inp_val_name := 'Tax Table Type';
3450 g_tax_card_tab(7).inp_val_name := 'Tax Free Threshold';
3451 g_tax_card_tab(8).inp_val_name := 'Registration Date';
3452 g_tax_card_tab(9).inp_val_name := 'Date Returned';
3453 --g_tax_card_tab(10).inp_val_name := 'Date Returned';
3454
3455 END IF;
3456
3457 ---------------------------------------------------
3458 -- For Mandatory Balance Details ,
3459 -- Check if global table has already been populated
3460 -- if not hen populate the values
3461 ---------------------------------------------------
3462
3463 IF g_bal_val.count = 0 THEN
3464
3465 g_bal_val(1).bal_name := 'TOTAL_PAY_ASG_PTD'; -- Net salary , period
3466 g_bal_val(2).bal_name := 'TOTAL_PAY_ASG_YTD'; -- Net salary , ytd
3467 g_bal_val(3).bal_name := 'TAX_ASG_PTD'; -- Withholding tax , period
3468 g_bal_val(4).bal_name := 'TAX_ASG_YTD'; -- Withholding tax , ytd
3469 g_bal_val(5).bal_name := 'TAX_TABLE_DEDUCTION_BASIS_ASG_PTD'; -- for Tax Deduction Basis , period
3470 g_bal_val(6).bal_name := 'TAX_TABLE_DEDUCTION_BASIS_ASG_YTD'; -- for Tax Deduction Basis , ytd
3471 g_bal_val(7).bal_name := 'TAX_PERCENTAGE_DEDUCTION_BASIS_ASG_PTD'; -- for Tax Deduction Basis , period
3472 g_bal_val(8).bal_name := 'TAX_PERCENTAGE_DEDUCTION_BASIS_ASG_YTD'; -- for Tax Deduction Basis , ytd
3473
3474 g_bal_val(9).bal_name := 'TAX_TABLE_DEDUCTION_ASG_PTD'; -- Table Based Tax Period
3475 g_bal_val(10).bal_name := 'TAX_TABLE_DEDUCTION_ASG_YTD'; -- Table Based Tax Ytd
3476 g_bal_val(11).bal_name := 'TAX_PERCENTAGE_DEDUCTION_ASG_PTD'; -- Percentage Based Tax Period
3477 g_bal_val(12).bal_name := 'TAX_PERCENTAGE_DEDUCTION_ASG_YTD'; -- Percentage Based Tax Ytd
3478
3479 -- g_bal_val(13).bal_name := 'SEAMAN_ASG_PTD'; -- Seaman Deduction Basis Period
3480 -- g_bal_val(14).bal_name := 'SEAMAN_ASG_YTD'; -- Seaman Deduction Basis Ytd
3481
3482
3483 -- g_bal_val(15).bal_name := '' ; -- to be used for Holiday pay, due this year
3484 -- g_bal_val(16).bal_name := '' ; -- to be used for Accrued basis for holiday pay
3485
3486 g_bal_val(13).bal_name := 'HOLIDAY_PAY_REMAINING_ASG_YTD' ; -- for Holiday pay, due this year
3487 g_bal_val(14).bal_name := 'HOLIDAY_PAY_OVER_60_REMAINING_ASG_YTD' ; -- Holiday pay, due this year
3488 g_bal_val(15).bal_name := 'HOLIDAY_PAY_BASE_ASG_PTD' ; -- for basis for holiday pay
3489 g_bal_val(16).bal_name := 'HOLIDAY_PAY_BASE_ASG_YTD' ; -- for basis for holiday pay
3490
3491
3492 END IF;
3493
3494 ELSIF p_archive_flag = 'Y' THEN
3495
3496 OPEN csr_tax_unit(p_assignment_action_id);
3497 FETCH csr_tax_unit INTO l_tu_id;
3498 CLOSE csr_tax_unit;
3499
3500 PAY_BALANCE_PKG.SET_CONTEXT('TAX_UNIT_ID',l_tu_id);
3501 PAY_BALANCE_PKG.SET_CONTEXT('DATE_EARNED',fnd_date.date_to_canonical(p_date_earned));
3502
3503 IF g_user_balance_table.count > 0 THEN
3504 -- fnd_file.put_line(fnd_file.log,'In ARCHIVE_OTH_BALANCE 1');
3505
3506 FOR l_index IN g_user_balance_table.first.. g_user_balance_table.last LOOP
3507
3508 l_balance_value := pay_balance_pkg.get_value(g_user_balance_table(l_index).defined_balance_id,p_assignment_action_id);
3509
3510 IF l_balance_value > 0 THEN
3511 -- fnd_file.put_line(fnd_file.log,'In ARCHIVE_OTH_BALANCE 2 :' || l_balance_value);
3512 pay_action_information_api.create_action_information (
3513 p_action_information_id => l_action_info_id
3514 ,p_action_context_id => p_archive_assact_id
3515 ,p_action_context_type => 'AAP'
3516 ,p_object_version_number => l_ovn
3517 ,p_effective_date => p_effective_date
3518 ,p_source_id => NULL
3519 ,p_source_text => NULL
3520 ,p_action_information_category => 'EMEA BALANCES'
3521 ,p_action_information1 => g_user_balance_table(l_index).defined_balance_id
3522 ,p_action_information2 => 'OBAL' --Other Balances Context
3523 ,p_action_information4 => fnd_number.number_to_canonical(l_balance_value) --l_formatted_value
3524 ,p_action_information5 => NULL
3525 ,p_action_information6 => 'Other Balances'
3526 ,p_assignment_id => p_assignment_id);
3527 END IF;
3528 END LOOP;
3529 END IF; /* For table count check */
3530 END IF;
3531
3532 -- fnd_file.put_line(fnd_file.log,'Leaving ARCHIVE_OTH_BALANCE 1');
3533
3534 EXCEPTION WHEN OTHERS THEN
3535 g_err_num := SQLCODE;
3536 -- fnd_file.put_line(fnd_file.log,'ORA_ERR: ' || g_err_num || 'In ARCHIVE_OTH_BALANCE'||SQLERRM);
3537
3538 IF g_debug THEN
3539 hr_utility.set_location('ORA_ERR: ' || g_err_num || 'In ARCHIVE_OTH_BALANCE',360);
3540 END IF;
3541
3542 END ARCHIVE_OTH_BALANCE;
3543
3544 ----------------------------------------- PROCEDURE ARCHIVE_CODE ---------------------------------------------------------
3545
3546 /* ARCHIVE CODE */
3547
3548 PROCEDURE ARCHIVE_CODE(p_assignment_action_id IN NUMBER
3549 ,p_effective_date IN DATE)
3550 IS
3551
3552 ----------------------
3553 /* Cursor to retrieve Payroll and Prepayment related Ids for Archival */
3554 /*
3555
3556 CURSOR csr_archive_ids (p_locking_action_id NUMBER) IS
3557 SELECT prepay_assact.assignment_action_id prepay_assact_id
3558 ,prepay_assact.assignment_id prepay_assgt_id
3559 ,prepay_payact.payroll_action_id prepay_payact_id
3560 ,prepay_payact.effective_date prepay_effective_date
3561 ,run_assact.assignment_id run_assgt_id
3562 ,run_assact.assignment_action_id run_assact_id
3563 ,run_payact.payroll_action_id run_payact_id
3564 ,run_payact.payroll_id payroll_id
3565 FROM pay_action_interlocks archive_intlck
3566 ,pay_assignment_actions prepay_assact
3567 ,pay_payroll_actions prepay_payact
3568 ,pay_action_interlocks prepay_intlck
3569 ,pay_assignment_actions run_assact
3570 ,pay_payroll_actions run_payact
3571 WHERE archive_intlck.locking_action_id = p_locking_action_id
3572 AND prepay_assact.assignment_action_id = archive_intlck.locked_action_id
3573 AND prepay_payact.payroll_action_id = prepay_assact.payroll_action_id
3574 AND prepay_payact.action_type IN ('U','P')
3575 AND prepay_intlck.locking_action_id = prepay_assact.assignment_action_id
3576 AND run_assact.assignment_action_id = prepay_intlck.locked_action_id
3577 AND run_payact.payroll_action_id = run_assact.payroll_action_id
3578 AND run_payact.action_type IN ('Q', 'R')
3579 ORDER BY prepay_intlck.locking_action_id,prepay_intlck.locked_action_id desc;
3580
3581 */
3582
3583 CURSOR csr_archive_ids (p_locking_action_id NUMBER) IS
3584 SELECT prepay_assact.assignment_action_id prepay_assact_id
3585 ,prepay_assact.assignment_id prepay_assgt_id
3586 ,prepay_payact.payroll_action_id prepay_payact_id
3587 ,prepay_payact.effective_date prepay_effective_date
3588 ,run_assact.assignment_id run_assgt_id
3589 ,run_assact.assignment_action_id run_assact_id
3590 ,run_payact.payroll_action_id run_payact_id
3591 ,run_payact.payroll_id payroll_id
3592 FROM pay_action_interlocks archive_intlck
3593 ,pay_assignment_actions prepay_assact
3594 ,pay_payroll_actions prepay_payact
3595 ,pay_action_interlocks prepay_intlck
3596 ,pay_assignment_actions run_assact
3597 ,pay_payroll_actions run_payact
3598 WHERE archive_intlck.locking_action_id = p_locking_action_id
3599 AND prepay_assact.assignment_action_id = archive_intlck.locked_action_id
3600 AND prepay_payact.payroll_action_id = prepay_assact.payroll_action_id
3601 AND prepay_payact.action_type IN ('U','P')
3602 AND prepay_intlck.locking_action_id = prepay_assact.assignment_action_id
3603 AND run_assact.assignment_action_id = prepay_intlck.locked_action_id
3604 AND NOT EXISTS (SELECT NULL FROM pay_assignment_actions
3605 WHERE payroll_action_id = run_assact.payroll_action_id
3606 AND source_action_id = run_assact.ASSIGNMENT_action_id )
3607 AND run_payact.payroll_action_id = run_assact.payroll_action_id
3608 AND run_payact.action_type IN ('Q', 'R')
3609 ORDER BY prepay_intlck.locking_action_id,prepay_intlck.locked_action_id desc;
3610
3611 ---------------------
3612 /* Cursor to retrieve time period information */
3613 CURSOR csr_period_end_date(p_assact_id NUMBER,p_pay_act_id NUMBER) IS
3614 SELECT ptp.end_date end_date,
3615 ptp.regular_payment_date regular_payment_date,
3616 ptp.time_period_id time_period_id,
3617 ppa.date_earned date_earned,
3618 ppa.effective_date effective_date,
3619 ptp.start_date start_date
3620 FROM per_time_periods ptp
3621 ,pay_payroll_actions ppa
3622 ,pay_assignment_actions paa
3623 WHERE ptp.payroll_id =ppa.payroll_id
3624 AND ppa.payroll_action_id =paa.payroll_action_id
3625 AND paa.assignment_action_id =p_assact_id
3626 AND ppa.payroll_action_id =p_pay_act_id
3627 AND ppa.date_earned BETWEEN ptp.start_date AND ptp.end_date;
3628 -----------------
3629 /* Cursor to retrieve Archive Payroll Action Id */
3630 CURSOR csr_archive_payact(p_assignment_action_id NUMBER) IS
3631 SELECT payroll_action_id
3632 FROM pay_assignment_actions
3633 WHERE assignment_Action_id = p_assignment_action_id;
3634 -----------------
3635 l_archive_payact_id NUMBER;
3636 l_record_count NUMBER;
3637 l_actid NUMBER;
3638 l_end_date per_time_periods.end_date%TYPE;
3639 l_pre_end_date per_time_periods.end_date%TYPE;
3640 l_reg_payment_date per_time_periods.regular_payment_date%TYPE;
3641 l_pre_reg_payment_date per_time_periods.regular_payment_date%TYPE;
3642 l_date_earned pay_payroll_actions.date_earned%TYPE;
3643 l_pre_date_earned pay_payroll_actions.date_earned%TYPE;
3644 l_effective_date pay_payroll_actions.effective_date%TYPE;
3645 l_pre_effective_date pay_payroll_actions.effective_date%TYPE;
3646 l_run_payact_id NUMBER;
3647 l_action_context_id NUMBER;
3648 g_archive_pact NUMBER;
3649 p_assactid NUMBER;
3650 l_time_period_id per_time_periods.time_period_id%TYPE;
3651 l_pre_time_period_id per_time_periods.time_period_id%TYPE;
3652 l_start_date per_time_periods.start_date%TYPE;
3653 l_pre_start_date per_time_periods.start_date%TYPE;
3654 l_fnd_session NUMBER := 0;
3655 l_prev_prepay NUMBER := 0;
3656 ------------------
3657
3658 BEGIN
3659
3660 -- fnd_file.put_line(fnd_file.log,'Entering Procedure ARCHIVE_CODE');
3661
3662 IF g_debug THEN
3663 hr_utility.set_location(' Entering Procedure ARCHIVE_CODE',380);
3664 END IF;
3665
3666 OPEN csr_archive_payact(p_assignment_action_id);
3667 FETCH csr_archive_payact INTO l_archive_payact_id;
3668 CLOSE csr_archive_payact;
3669
3670 -- fnd_file.put_line(fnd_file.log,'closed csr_archive_payact');
3671
3672 l_record_count := 0;
3673
3674 -- fnd_file.put_line(fnd_file.log,'LOC 6 => p_assignment_action_id = ' || p_assignment_action_id);
3675
3676 FOR rec_archive_ids IN csr_archive_ids(p_assignment_action_id) LOOP
3677
3678 -- fnd_file.put_line(fnd_file.log,'LOC 7 => rec_archive_ids.run_assact_id = ' || rec_archive_ids.run_assact_id );
3679
3680 OPEN csr_period_end_date(rec_archive_ids.run_assact_id,rec_archive_ids.run_payact_id);
3681 FETCH csr_period_end_date INTO l_end_date,l_reg_payment_date,l_time_period_id,l_date_earned,l_effective_date,l_start_date;
3682 CLOSE csr_period_end_date;
3683
3684 -- fnd_file.put_line(fnd_file.log,'closed csr_period_end_date');
3685
3686 OPEN csr_period_end_date(rec_archive_ids.prepay_assact_id,rec_archive_ids.prepay_payact_id);
3687 FETCH csr_period_end_date INTO l_pre_end_date,l_pre_reg_payment_date,l_pre_time_period_id,l_pre_date_earned,l_pre_effective_date,l_pre_start_date;
3688 CLOSE csr_period_end_date;
3689
3690 -- fnd_file.put_line(fnd_file.log,'closed csr_period_end_date');
3691
3692 /*fnd_file.put_line(fnd_file.log,'ARCHIVE_EMPLOYEE_DETAILS');*/
3693
3694 -------------------------------------------------------------
3695 --Archive EMPLOYEE_DETAILS, PAYMENT_INFO and BALANCES
3696 --for every prepayment assignment action id
3697 -------------------------------------------------------------
3698
3699 IF (rec_archive_ids.prepay_assact_id <> l_prev_prepay) THEN
3700
3701 -- fnd_file.put_line(fnd_file.log,'in ARCHIVE_EMPLOYEE_DETAILS');
3702
3703 /*
3704 ARCHIVE_EMPLOYEE_DETAILS(p_archive_assact_id => p_assignment_action_id
3705 ,p_assignment_id => rec_archive_ids.run_assgt_id
3706 ,p_assignment_action_id => rec_archive_ids.run_assact_id
3707 ,p_payroll_action_id => l_archive_payact_id
3708 ,p_time_period_id => l_time_period_id
3709 ,p_date_earned => l_pre_date_earned
3710 ,p_pay_date_earned => l_date_earned
3711 ,p_effective_date => p_effective_date);
3712 */
3713
3714 -- using l_date_earned (Payroll Run Date Earned) instead of l_pre_date_earned (Prepayments Date Earned)
3715 -- because the date_earned column at table pay_payroll_actions for Prepayments may not always be populated.
3716
3717 ARCHIVE_EMPLOYEE_DETAILS(p_archive_assact_id => p_assignment_action_id
3718 ,p_assignment_id => rec_archive_ids.run_assgt_id
3719 ,p_assignment_action_id => rec_archive_ids.run_assact_id
3720 ,p_payroll_action_id => l_archive_payact_id
3721 ,p_time_period_id => l_time_period_id
3722 ,p_date_earned => l_date_earned
3723 ,p_pay_date_earned => l_date_earned
3724 ,p_effective_date => p_effective_date);
3725
3726 -- fnd_file.put_line(fnd_file.log,'out ARCHIVE_EMPLOYEE_DETAILS');
3727 -- fnd_file.put_line(fnd_file.log,'in ARCHIVE_ADDL_EMP_DETAILS');
3728
3729
3730 ARCHIVE_ADDL_EMP_DETAILS(p_archive_assact_id => p_assignment_action_id
3731 ,p_assignment_id => rec_archive_ids.run_assgt_id
3732 ,p_assignment_action_id => rec_archive_ids.run_assact_id
3733 ,p_effective_date => p_effective_date
3734 ,p_date_earned => l_date_earned
3735 ,p_payroll_action_id => l_archive_payact_id );
3736
3737
3738
3739 -- fnd_file.put_line(fnd_file.log,'out ARCHIVE_ADDL_EMP_DETAILS, currently not being processed');
3740 -- fnd_file.put_line(fnd_file.log,'in ARCHIVE_PAYMENT_INFO');
3741
3742 /*fnd_file.put_line(fnd_file.log,'ARCHIVE_PAYMENT_INFO');*/
3743
3744 /*
3745 ARCHIVE_PAYMENT_INFO(p_archive_assact_id => p_assignment_action_id,
3746 p_prepay_assact_id => rec_archive_ids.prepay_assact_id,
3747 p_assignment_id => rec_archive_ids.prepay_assgt_id,
3748 p_date_earned => l_pre_date_earned,
3749 p_effective_date => p_effective_date);
3750 */
3751
3752 -- using l_date_earned (Payroll Run Date Earned) instead of l_pre_date_earned (Prepayments Date Earned)
3753 -- because the date_earned column at table pay_payroll_actions for Prepayments may not always be populated.
3754
3755 ARCHIVE_PAYMENT_INFO(p_archive_assact_id => p_assignment_action_id,
3756 p_prepay_assact_id => rec_archive_ids.prepay_assact_id,
3757 p_assignment_id => rec_archive_ids.prepay_assgt_id,
3758 p_date_earned => l_date_earned,
3759 p_effective_date => p_effective_date);
3760
3761 -- fnd_file.put_line(fnd_file.log,'out ARCHIVE_PAYMENT_INFO');
3762 -- fnd_file.put_line(fnd_file.log,'in ARCHIVE_OTH_BALANCE');
3763
3764 /*fnd_file.put_line(fnd_file.log,'ARCHIVE_OTH_BALANCE');*/
3765
3766 ARCHIVE_OTH_BALANCE(p_archive_assact_id => p_assignment_action_id,
3767 p_assignment_action_id => rec_archive_ids.run_assact_id,
3768 p_assignment_id => rec_archive_ids.run_assgt_id,
3769 p_payroll_action_id => l_archive_payact_id,
3770 p_record_count => l_record_count,
3771 p_pre_payact_id => rec_archive_ids.prepay_payact_id,
3772 p_effective_date => p_effective_date,
3773 p_date_earned => l_date_earned,
3774 p_archive_flag => 'Y');
3775
3776 -- -- fnd_file.put_line(fnd_file.log,'out ARCHIVE_OTH_BALANCE');
3777 -- fnd_file.put_line(fnd_file.log,'before end if');
3778
3779
3780
3781 l_prev_prepay := rec_archive_ids.prepay_assact_id;
3782
3783 END IF;
3784
3785 /*fnd_file.put_line(fnd_file.log,'ARCHIVE_ACCRUAL_PLAN');*/
3786
3787 /* ARCHIVE_ACCRUAL_PLAN (p_assignment_id => rec_archive_ids.run_assgt_id,
3788 p_date_earned => l_date_earned,
3789 p_effective_date => p_effective_date,
3790 p_archive_assact_id => p_assignment_action_id,
3791 p_run_assignment_action_id => rec_archive_ids.run_assact_id,
3792 p_period_end_date => l_end_date,
3793 p_period_start_date => l_start_date);*/
3794
3795 /*fnd_file.put_line(fnd_file.log,'ARCHIVE_ADD_ELEMENT');*/
3796
3797 -- fnd_file.put_line(fnd_file.log,'in ARCHIVE_ADD_ELEMENT');
3798
3799
3800 ARCHIVE_ADD_ELEMENT(p_archive_assact_id => p_assignment_action_id,
3801 p_assignment_action_id => rec_archive_ids.run_assact_id,
3802 p_assignment_id => rec_archive_ids.run_assgt_id,
3803 p_payroll_action_id => l_archive_payact_id,
3804 p_date_earned => l_date_earned,
3805 p_effective_date => p_effective_date,
3806 p_pre_payact_id => rec_archive_ids.prepay_payact_id,
3807 p_archive_flag => 'Y');
3808
3809 -- fnd_file.put_line(fnd_file.log,'out ARCHIVE_ADD_ELEMENT');
3810 -- fnd_file.put_line(fnd_file.log,'in ARCHIVE_MAIN_ELEMENTS');
3811
3812 /*fnd_file.put_line(fnd_file.log,'Assact id: '|| p_assignment_action_id);*/
3813
3814 ARCHIVE_MAIN_ELEMENTS (p_archive_assact_id => p_assignment_action_id,
3815 p_assignment_action_id => rec_archive_ids.run_assact_id,
3816 p_assignment_id => rec_archive_ids.run_assgt_id,
3817 p_date_earned => l_date_earned,
3818 p_effective_date => p_effective_date,
3819 p_payroll_action_id => l_archive_payact_id ) ;
3820
3821 -- fnd_file.put_line(fnd_file.log,'out ARCHIVE_MAIN_ELEMENTS');
3822
3823
3824
3825 l_record_count := l_record_count + 1;
3826
3827 END LOOP;
3828
3829 IF g_debug THEN
3830 hr_utility.set_location(' Leaving Procedure ARCHIVE_CODE',390);
3831 END IF;
3832
3833 -- fnd_file.put_line(fnd_file.log,'Leaving Procedure ARCHIVE_CODE');
3834
3835 END ARCHIVE_CODE;
3836
3837
3838 ---------------------------------------- PROCEDURE ARCHIVE_ADDL_EMP_DETAILS --------------------------------------------------------------------------
3839
3840 /*Additional Employee Details*/
3841
3842 PROCEDURE ARCHIVE_ADDL_EMP_DETAILS(p_archive_assact_id IN NUMBER
3843 ,p_assignment_id IN NUMBER
3844 ,p_assignment_action_id IN NUMBER
3845 ,p_effective_date IN DATE
3846 ,p_date_earned IN DATE
3847 ,p_payroll_action_id IN NUMBER )
3848 IS
3849 -------------
3850 CURSOR CSR_ACTUAL_TERM_DATE (p_assignment_id NUMBER) IS
3851 SELECT actual_termination_date
3852 FROM per_periods_of_service pps,
3853 per_all_assignments_f paa
3854 WHERE pps.period_of_service_id = paa.period_of_service_id
3855 AND p_date_earned between paa.effective_start_date and paa.effective_end_date
3856 AND paa.assignment_id = p_assignment_id;
3857 -------------
3858
3859 CURSOR get_details(p_assignment_id NUMBER , p_input_value VARCHAR2 ) IS
3860 SELECT ee.effective_start_date effective_start_date
3861 ,eev1.screen_entry_value screen_entry_value
3862 FROM per_all_assignments_f asg1
3863 ,per_all_assignments_f asg2
3864 ,per_all_people_f per
3865 ,pay_element_links_f el
3866 ,pay_element_types_f et
3867 ,pay_input_values_f iv1
3868 ,pay_element_entries_f ee
3869 ,pay_element_entry_values_f eev1
3870 WHERE asg1.assignment_id = p_assignment_id
3871 AND p_date_earned BETWEEN asg1.effective_start_date AND asg1.effective_end_date
3872 AND p_date_earned BETWEEN asg2.effective_start_date AND asg2.effective_end_date
3873 AND per.person_id = asg1.person_id
3874 AND asg2.person_id = per.person_id
3875 AND asg2.primary_flag = 'Y'
3876 AND et.element_name = 'Tax Card'
3877 AND et.legislation_code = 'NO'
3878 AND iv1.element_type_id = et.element_type_id
3879 AND iv1.name = p_input_value
3880 AND el.business_group_id = per.business_group_id
3881 AND el.element_type_id = et.element_type_id
3882 AND ee.assignment_id = asg2.assignment_id
3883 AND ee.element_link_id = el.element_link_id
3884 AND eev1.element_entry_id = ee.element_entry_id
3885 AND eev1.input_value_id = iv1.input_value_id
3886 AND p_date_earned BETWEEN ee.effective_start_date AND ee.effective_end_date
3887 AND p_date_earned BETWEEN eev1.effective_start_date AND eev1.effective_end_date;
3888 ------------
3889 CURSOR csr_tax_details(p_assignment_id NUMBER, p_input_value VARCHAR2) IS
3890 SELECT ee.effective_start_date
3891 ,eev1.screen_entry_value screen_entry_value
3892 FROM per_all_assignments_f asg1
3893 ,pay_element_links_f el
3894 ,pay_element_types_f et
3895 ,pay_input_values_f iv1
3896 ,pay_element_entries_f ee
3897 ,pay_element_entry_values_f eev1
3898 WHERE asg1.assignment_id = p_assignment_id
3899 AND p_date_earned BETWEEN asg1.effective_start_date AND asg1.effective_end_date
3900 AND et.element_name = 'Tax'
3901 AND et.legislation_code = 'NO'
3902 AND iv1.element_type_id = et.element_type_id
3903 AND iv1.name = p_input_value
3904 AND el.element_type_id = et.element_type_id
3905 AND ee.assignment_id = asg1.assignment_id
3906 AND ee.element_link_id = el.element_link_id
3907 AND eev1.element_entry_id = ee.element_entry_id
3908 AND eev1.input_value_id = iv1.input_value_id
3909 AND p_date_earned BETWEEN ee.effective_start_date AND ee.effective_end_date
3910 AND p_date_earned BETWEEN eev1.effective_start_date AND eev1.effective_end_date;
3911 -------------
3912 CURSOR csr_tax_category (p_assignment_id NUMBER) IS
3913 SELECT segment13
3914 FROM per_all_assignments_f paa,
3915 hr_soft_coding_keyflex hsc
3916 WHERE paa.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
3917 AND p_date_earned BETWEEN paa.effective_start_date AND paa.effective_end_date
3918 AND paa.assignment_id = p_assignment_id;
3919 -------------
3920 CURSOR csr_global_value (p_global_name VARCHAR2) IS
3921 SELECT global_value
3922 FROM ff_globals_f
3923 WHERE global_name = p_global_name
3924 AND p_date_earned BETWEEN effective_start_date AND effective_end_date;
3925 -------------
3926 /* cursor to get the payroll_d */
3927 CURSOR csr_payroll (p_payroll_action_id NUMBER) IS
3928 SELECT PAY_NO_PAYSLIP_ARCHIVE.GET_PARAMETER(legislative_parameters,'PAYROLL_ID')
3929 FROM pay_payroll_actions
3930 WHERE payroll_action_id = p_payroll_action_id ;
3931
3932
3933 /* cursor to get the payroll details */
3934 CURSOR csr_payroll_details (l_payroll_id NUMBER) IS
3935 SELECT payroll_name , period_type
3936 FROM pay_all_payrolls_f
3937 WHERE payroll_id = l_payroll_id ;
3938 --------------
3939
3940 /* Cursor to get the Work Title from the assignment */
3941
3942 cursor csr_work_title (p_assignment_id NUMBER) IS
3943 SELECT hsck.segment4
3944 from per_all_assignments_f paaf
3945 ,hr_soft_coding_keyflex hsck
3946 where paaf.assignment_id= p_assignment_id
3947 and p_date_earned BETWEEN paaf.effective_start_date and paaf.effective_end_date
3948 and paaf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id;
3949 --------------
3950
3951 /* Cursor to get the Zone and meaning from UDT and lookup */
3952
3953 cursor get_code_meaning ( p_municipal_no VARCHAR2, l_eff_date DATE) IS
3954 SELECT hr_de_general.get_uci(l_eff_date, t.user_table_id, r.user_row_id,'ZONE')
3955 || ' ' || hr_general.decode_lookup('NO_TAX_MUNICIPALITY',
3956 hr_de_general.get_uci(l_eff_date, t.user_table_id, r.user_row_id, 'MAPPING_ID'))
3957 FROM pay_user_tables t
3958 ,pay_user_rows_f r
3959 WHERE t.user_table_name = 'NO_TAX_MUNICIPALITY'
3960 AND t.legislation_code = 'NO'
3961 AND r.user_table_id = t.user_table_id
3962 AND r.row_low_range_or_name = p_municipal_no
3963 AND l_eff_date BETWEEN r.effective_start_date AND r.effective_end_date;
3964
3965 ------------------------------------------------
3966
3967 l_actual_termination_date PER_PERIODS_OF_SERVICE.ACTUAL_TERMINATION_DATE%TYPE;
3968 l_tax_card_effective_date DATE;
3969 l_tax_card_type VARCHAR2(50);
3970 l_base_rate NUMBER(5,2);
3971 l_additional_rate NUMBER(5,2);
3972 l_yearly_income_limit NUMBER(10);
3973 l_previous_income NUMBER (10);
3974 l_ovn NUMBER ;
3975 l_rec get_details%ROWTYPE;
3976 l_tax_rec csr_tax_details%ROWTYPE;
3977 l_action_info_id pay_action_information.action_information_id%TYPE;
3978 l_tax_category hr_soft_coding_keyflex.segment13%TYPE;
3979 l_defined_balance_id pay_defined_balances.defined_balance_id%TYPE;
3980
3981 l_work_title hr_soft_coding_keyflex.segment4%type := NULL ;
3982 l_net_salary_ptd NUMBER;
3983 l_net_salary_ytd NUMBER;
3984 l_table_tax_basis_ptd NUMBER;
3985 l_table_tax_basis_ytd NUMBER;
3986 l_percent_tax_basis_ptd NUMBER;
3987 l_percent_tax_basis_ytd NUMBER;
3988 l_tax_deduction_basis_ptd NUMBER;
3989 l_tax_deduction_basis_ytd NUMBER;
3990 l_table_tax_ptd NUMBER;
3991 l_table_tax_ytd NUMBER;
3992 l_percent_tax_ptd NUMBER;
3993 l_percent_tax_ytd NUMBER;
3994 l_withholding_tax_ptd NUMBER;
3995 l_withholding_tax_ytd NUMBER;
3996 l_seaman_basis_ptd NUMBER;
3997 l_seaman_basis_ytd NUMBER;
3998
3999 l_threshold_remaining NUMBER;
4000 l_threshold_used_per_ytd NUMBER;
4001 l_hol_pay_due_this_yr_ytd NUMBER;
4002 l_basis_for_hol_pay_ptd NUMBER;
4003 l_basis_for_hol_pay_ytd NUMBER;
4004
4005 l_payroll_id NUMBER;
4006 l_payroll_name VARCHAR2(80);
4007 l_period_type VARCHAR2(80);
4008 --------------
4009 /*
4010 TYPE tax_card_rec IS RECORD (inp_val_name pay_input_values_f.NAME%type , screen_entry_val pay_input_values_f.NAME%type );
4011
4012 TYPE bal_val_rec IS RECORD ( bal_name ff_database_items.USER_NAME%type , bal_val NUMBER(10,2) );
4013
4014
4015 TYPE tax_card_table IS TABLE OF tax_card_rec INDEX BY BINARY_INTEGER;
4016
4017 TYPE bal_val_table IS TABLE OF bal_val_rec INDEX BY BINARY_INTEGER;
4018
4019
4020 g_tax_card_tab tax_card_table;
4021 g_bal_val bal_val_table;
4022
4023 */
4024
4025 -------------
4026
4027 BEGIN
4028
4029 -- fnd_file.put_line(fnd_file.log,'inside ARCHIVE_ADDL_EMP_DETAILS');
4030
4031 OPEN CSR_ACTUAL_TERM_DATE (p_assignment_id);
4032 FETCH CSR_ACTUAL_TERM_DATE INTO l_actual_termination_date;
4033 CLOSE CSR_ACTUAL_TERM_DATE;
4034
4035 -- fnd_file.put_line(fnd_file.log,'closed CSR_ACTUAL_TERM_DATE');
4036
4037 -- fnd_file.put_line(fnd_file.log,'before FOR g_tax_card_tab');
4038
4039 FOR l_index IN g_tax_card_tab.first.. g_tax_card_tab.last LOOP
4040
4041 OPEN get_details( p_assignment_id ,g_tax_card_tab(l_index).inp_val_name );
4042 FETCH get_details INTO l_rec;
4043 CLOSE get_details;
4044
4045 g_tax_card_tab(l_index).screen_entry_val := l_rec.screen_entry_value ;
4046
4047 END LOOP;
4048
4049 -- fnd_file.put_line(fnd_file.log,'end loop FOR g_tax_card_tab');
4050
4051 l_tax_card_effective_date := l_rec.effective_start_date;
4052
4053 ---------------------
4054
4055 -- fnd_file.put_line(fnd_file.log,'getting display values for Tax Card');
4056
4057 -- Getting the display value for Tax Card input values
4058 g_tax_card_tab(1).screen_entry_val := hr_general.decode_lookup('NO_METHOD_OF_RECEIPT',g_tax_card_tab(1).screen_entry_val);
4059 --
4060 OPEN get_code_meaning(g_tax_card_tab(2).screen_entry_val, l_tax_card_effective_date);
4061 FETCH get_code_meaning INTO g_tax_card_tab(2).screen_entry_val;
4062 CLOSE get_code_meaning;
4063 --g_tax_card_tab(2).screen_entry_val :=hr_general.decode_lookup('NO_TAX_MUNICIPALITY',g_tax_card_tab(2).screen_entry_val);
4064 --
4065 g_tax_card_tab(3).screen_entry_val := hr_general.decode_lookup('NO_TAX_CARD_TYPE',g_tax_card_tab(3).screen_entry_val);
4066 g_tax_card_tab(5).screen_entry_val := hr_general.decode_lookup('NO_TAX_TABLE_NO',g_tax_card_tab(5).screen_entry_val);
4067 g_tax_card_tab(6).screen_entry_val := hr_general.decode_lookup('NO_TAX_TABLE_TYPE',g_tax_card_tab(6).screen_entry_val);
4068 g_tax_card_tab(8).screen_entry_val := fnd_date.date_to_displaydate(fnd_date.canonical_to_date(g_tax_card_tab(8).screen_entry_val)) ; -- Registration Date
4069 g_tax_card_tab(9).screen_entry_val := fnd_date.date_to_displaydate(fnd_date.canonical_to_date(g_tax_card_tab(9).screen_entry_val)) ; -- Date Returned
4070
4071 -- calculating the Threshold Remaining
4072
4073 l_defined_balance_id := GET_DEFINED_BALANCE_ID('THRESHOLD_USED_PER_YTD');
4074 l_threshold_used_per_ytd := pay_balance_pkg.get_value(l_defined_balance_id, p_assignment_action_id);
4075 l_threshold_remaining := g_tax_card_tab(7).screen_entry_val - l_threshold_used_per_ytd ;
4076
4077
4078 -- fnd_file.put_line(fnd_file.log,'archiving ADDL EMPLOYEE DETAILS');
4079
4080 pay_action_information_api.create_action_information (
4081 p_action_information_id => l_action_info_id
4082 ,p_action_context_id => p_archive_assact_id
4083 ,p_action_context_type => 'AAP'
4084 ,p_object_version_number => l_ovn
4085 ,p_effective_date => p_effective_date
4086 ,p_source_id => NULL
4087 ,p_source_text => NULL
4088 ,p_action_information_category => 'ADDL EMPLOYEE DETAILS'
4089 ,p_action_information4 => g_tax_card_tab(1).screen_entry_val -- Method of Receipt
4090 ,p_action_information5 => g_tax_card_tab(2).screen_entry_val -- Tax Municipality
4091 ,p_action_information6 => g_tax_card_tab(3).screen_entry_val -- Tax Card Type
4092 ,p_action_information7 => g_tax_card_tab(4).screen_entry_val -- Tax Percentage
4093 ,p_action_information8 => g_tax_card_tab(5).screen_entry_val -- Tax Table Number
4094 ,p_action_information9 => g_tax_card_tab(6).screen_entry_val -- Tax Table Type
4095 ,p_action_information10 => g_tax_card_tab(7).screen_entry_val -- Tax Free Threshold
4096 ,p_action_information11 => g_tax_card_tab(8).screen_entry_val -- Registration Date
4097 ,p_action_information12 => g_tax_card_tab(9).screen_entry_val -- Date Returned
4098 ,p_action_information13 => l_threshold_remaining -- Threshold Remaining
4099 ,p_assignment_id => p_assignment_id );
4100
4101 -- fnd_file.put_line(fnd_file.log,'finished archiving ADDL EMPLOYEE DETAILS');
4102
4103 -------------------------------------------------------------------------------
4104
4105 -- fnd_file.put_line(fnd_file.log,'begin FOR g_bal_val');
4106
4107 -- fnd_file.put_line(fnd_file.log,'g_bal_val.first = '||to_char(g_bal_val.first));
4108 -- fnd_file.put_line(fnd_file.log,'g_bal_val.last = '||to_char(g_bal_val.last));
4109
4110
4111 FOR l_index IN g_bal_val.first.. g_bal_val.last LOOP
4112
4113 -- fnd_file.put_line(fnd_file.log,'l_index = '||to_char(l_index));
4114 -- fnd_file.put_line(fnd_file.log,'g_bal_val(l_index).bal_name = '||g_bal_val(l_index).bal_name);
4115
4116 l_defined_balance_id := GET_DEFINED_BALANCE_ID( g_bal_val(l_index).bal_name );
4117
4118 -- fnd_file.put_line(fnd_file.log,'l_defined_balance_id = '||to_char(l_defined_balance_id));
4119 -- fnd_file.put_line(fnd_file.log,'p_assignment_action_id = '||to_char(p_assignment_action_id));
4120
4121 g_bal_val(l_index).bal_val := pay_balance_pkg.get_value(l_defined_balance_id, p_assignment_action_id);
4122
4123 -- fnd_file.put_line(fnd_file.log,'g_bal_val(l_index).bal_val = '||to_char(g_bal_val(l_index).bal_val));
4124
4125 END LOOP;
4126
4127 -- fnd_file.put_line(fnd_file.log,'end loop FOR g_bal_val');
4128 -- fnd_file.put_line(fnd_file.log,'start asigning balance values');
4129
4130 l_net_salary_ptd := g_bal_val(1).bal_val ;
4131 l_net_salary_ytd := g_bal_val(2).bal_val ;
4132 l_table_tax_basis_ptd := g_bal_val(5).bal_val ;
4133 l_table_tax_basis_ytd := g_bal_val(6).bal_val ;
4134 l_percent_tax_basis_ptd := g_bal_val(7).bal_val ;
4135 l_percent_tax_basis_ytd := g_bal_val(8).bal_val ;
4136 l_tax_deduction_basis_ptd := l_table_tax_basis_ptd + l_percent_tax_basis_ptd ;
4137 l_tax_deduction_basis_ytd := l_table_tax_basis_ytd + l_percent_tax_basis_ytd ;
4138 l_table_tax_ptd := g_bal_val(9).bal_val ;
4139 l_table_tax_ytd := g_bal_val(10).bal_val ;
4140 l_percent_tax_ptd := g_bal_val(11).bal_val ;
4141 l_percent_tax_ytd := g_bal_val(12).bal_val ;
4142 l_withholding_tax_ptd := g_bal_val(3).bal_val ;
4143 l_withholding_tax_ytd := g_bal_val(4).bal_val ;
4144 l_hol_pay_due_this_yr_ytd := g_bal_val(13).bal_val + g_bal_val(14).bal_val ;
4145 l_basis_for_hol_pay_ptd := g_bal_val(15).bal_val ;
4146 l_basis_for_hol_pay_ytd := g_bal_val(16).bal_val ;
4147
4148 -- l_seaman_basis_ptd := g_bal_val(13).bal_val ;
4149 -- l_seaman_basis_ytd := g_bal_val(14).bal_val ;
4150
4151
4152
4153
4154
4155 -- fnd_file.put_line(fnd_file.log,'finish asigning balance values');
4156
4157 /* get the work title of the employee */
4158
4159 OPEN csr_work_title (p_assignment_id) ;
4160 FETCH csr_work_title INTO l_work_title ;
4161 CLOSE csr_work_title ;
4162
4163
4164 -- fnd_file.put_line(fnd_file.log,'after the select decode');
4165
4166 -- fnd_file.put_line(fnd_file.log,'starting archiving NO EMPLOYEE DETAILS');
4167
4168 pay_action_information_api.create_action_information (
4169 p_action_information_id => l_action_info_id
4170 ,p_action_context_id => p_archive_assact_id
4171 ,p_action_context_type => 'AAP'
4172 ,p_object_version_number => l_ovn
4173 ,p_effective_date => p_effective_date
4174 ,p_source_id => NULL
4175 ,p_source_text => NULL
4176 ,p_action_information_category => 'NO EMPLOYEE DETAILS'
4177 ,p_action_information1 => l_work_title -- Work Title
4178 ,p_action_information2 => l_net_salary_ptd -- Net Salary Period
4179 ,p_action_information3 => l_net_salary_ytd -- Net Salary Ytd
4180 ,p_action_information4 => l_withholding_tax_ptd -- Withholding Tax Period
4181 ,p_action_information5 => l_withholding_tax_ytd -- Withholding Tax Ytd
4182 ,p_action_information6 => l_tax_deduction_basis_ptd -- Tax Deduction Basis Period
4183 ,p_action_information7 => l_tax_deduction_basis_ytd -- Tax Deduction Basis Ytd
4184 ,p_action_information8 => l_table_tax_basis_ptd -- Table Based Tax Basis Period
4185 ,p_action_information9 => l_table_tax_basis_ytd -- Table Based Tax Basis Ytd
4186 ,p_action_information10 => l_table_tax_ptd -- Table Based Tax Period
4187 ,p_action_information11 => l_table_tax_ytd -- Table Based Tax Ytd
4188 ,p_action_information12 => l_percent_tax_basis_ptd -- Percentage Tax Basis Period
4189 ,p_action_information13 => l_percent_tax_basis_ytd -- Percentage Tax Basis Ytd
4190 ,p_action_information14 => l_percent_tax_ptd -- Percentage Based Tax Period
4191 ,p_action_information15 => l_percent_tax_ytd -- Percentage Based Tax Ytd
4192 -- ,p_action_information16 => l_seaman_basis_ptd -- Seaman Deduction Basis Period
4193 -- ,p_action_information17 => l_seaman_basis_ytd -- Seaman Deduction Basis Ytd
4194 ,p_action_information16 => l_hol_pay_due_this_yr_ytd -- Holiday Pay, due this year
4195 ,p_action_information17 => l_basis_for_hol_pay_ptd -- Basis for Holiday Pay Period
4196 ,p_action_information18 => l_basis_for_hol_pay_ytd -- Basis for Holiday Pay Ytd
4197 ,p_assignment_id => p_assignment_id);
4198
4199
4200 -- fnd_file.put_line(fnd_file.log,'finished archiving NO EMPLOYEE DETAILS');
4201 -- fnd_file.put_line(fnd_file.log,'leaving ARCHIVE_ADDL_EMP_DETAILS');
4202
4203
4204 END ARCHIVE_ADDL_EMP_DETAILS;
4205
4206 --------------------------------------- PROCEDURE ARCHIVE_MAIN_ELEMENTS ---------------------------------------------------------
4207
4208 /* ARCHIVE EARNINGS AND DEDUCTIONS ELEMENTS REGION */
4209
4210 PROCEDURE ARCHIVE_MAIN_ELEMENTS
4211 (p_archive_assact_id IN NUMBER,
4212 p_assignment_action_id IN NUMBER,
4213 p_assignment_id IN NUMBER,
4214 p_date_earned IN DATE,
4215 p_effective_date IN DATE,
4216 p_payroll_action_id IN NUMBER ) IS
4217
4218 ----------------
4219
4220 /* Cursor to retrieve Earnings Element Information */
4221 /*
4222 CURSOR csr_ear_element_info IS
4223 SELECT nvl(pettl.reporting_name,pettl.element_name) rep_name
4224 ,et.element_type_id element_type_id
4225 ,iv.input_value_id input_value_id
4226 ,iv.uom uom
4227 FROM pay_element_types_f et
4228 , pay_element_types_f_tl pettl
4229 , pay_input_values_f iv
4230 , pay_element_classifications classification
4231 WHERE et.element_type_id = iv.element_type_id
4232 AND et.element_type_id = pettl.element_type_id
4233 AND pettl.language = USERENV('LANG')
4234 AND iv.name = 'Pay Value'
4235 AND classification.classification_id = et.classification_id
4236 AND classification.classification_name
4237 IN ('Absence','Direct Payments','Earnings','Supplementary Earnings')
4238 AND p_date_earned BETWEEN et.effective_start_date AND et.effective_end_date
4239 AND p_date_earned BETWEEN iv.effective_start_date AND iv.effective_end_date
4240 AND ((et.business_group_id IS NULL AND et.legislation_code = 'NO')
4241 OR (et.business_group_id = g_business_group_id AND et.legislation_code IS NULL));
4242
4243 */
4244 ---------------
4245
4246 /* Cursor to retrieve Deduction Element Information */
4247
4248 /*
4249 CURSOR csr_ded_element_info IS
4250 SELECT nvl(pettl.reporting_name,pettl.element_name) rep_name
4251 ,et.element_type_id element_type_id
4252 ,iv.input_value_id input_value_id
4253 ,iv.uom uom
4254 FROM pay_element_types_f et
4255 , pay_element_types_f_tl pettl
4256 , pay_input_values_f iv
4257 , pay_element_classifications classification
4258 WHERE et.element_type_id = iv.element_type_id
4259 AND et.element_type_id = pettl.element_type_id
4260 AND pettl.language = USERENV('LANG')
4261 AND iv.name = 'Pay Value'
4262 AND classification.classification_id = et.classification_id
4263 AND classification.classification_name
4264 IN ('Involuntary Deductions','Pre-tax Deductions','Statutory Deductions','Voluntary Deductions')
4265 AND p_date_earned BETWEEN et.effective_start_date AND et.effective_end_date
4266 AND p_date_earned BETWEEN iv.effective_start_date AND iv.effective_end_date
4267 AND ((et.business_group_id IS NULL AND et.legislation_code = 'NO')
4268 OR (et.business_group_id = g_business_group_id AND et.legislation_code IS NULL));
4269
4270 */
4271
4272 ---------------
4273 /* Cursor to retrieve Main Element Information */
4274
4275 CURSOR csr_get_main_elements (p_payroll_action_id NUMBER ) IS
4276 SELECT action_information2 element_type_id
4277 ,action_information3 input_value_id
4278 ,action_information4 element_narrative
4279 ,action_information5 element_context
4280 ,action_information6 uom
4281 ,action_information8 prim_def_bal_id
4282 ,action_information12 ele_class
4283 FROM pay_action_information
4284 WHERE action_context_id = p_payroll_action_id
4285 AND action_information_category = 'NO ELEMENT DEFINITION'
4286 AND action_context_type = 'PA'
4287 AND action_information5 <> 'F';
4288
4289 ----------------------------------------------------------
4290
4291 /* cursor to get the tax unit id (Legal Employer) from assignment action id */
4292
4293 CURSOR csr_get_le_org_id (p_assignment_action_id NUMBER) IS
4294 SELECT tax_unit_id
4295 FROM pay_assignment_actions
4296 WHERE assignment_action_id = p_assignment_action_id ;
4297
4298 --------------------------------------------------------
4299
4300 /* cursor to get the element code */
4301
4302 /*
4303 CURSOR csr_ele_code (p_ele_type_id NUMBER , p_le_org_id NUMBER ) IS
4304 select eei_information1
4305 from pay_element_type_extra_info
4306 where element_type_id = p_ele_type_id
4307 and ( eei_information2 = p_le_org_id OR eei_information2 is null )
4308 and information_type = 'NO_ELEMENT_CODES'
4309 and eei_information_category = 'NO_ELEMENT_CODES'
4310 and rownum = 1
4311 order by eei_information2 , element_type_extra_info_id ;
4312 */
4313
4314 -- modifying the above cursor
4315
4316 cursor csr_ele_code(p_ele_type_id NUMBER , p_leg_emp_id NUMBER ) is
4317 select nvl((select eei_information1 from pay_element_type_extra_info petei
4318 where petei.information_type='NO_ELEMENT_CODES'
4319 and element_type_id = p_ele_type_id
4320 and petei.eei_information2 = p_leg_emp_id
4321 and rownum=1),
4322 (select eei_information1 from pay_element_type_extra_info petei
4323 where petei.information_type='NO_ELEMENT_CODES'
4324 and element_type_id = p_ele_type_id
4325 and eei_information2 is null
4326 and rownum=1)) from dual;
4327
4328 --------------------------------------------------------
4329
4330 /* cursor to get the further element entry info for payslip information */
4331
4332 CURSOR csr_payslip_info (p_ele_entry_id NUMBER) IS
4333 SELECT ENTRY_INFORMATION1
4334 FROM pay_element_entries_f
4335 where ELEMENT_ENTRY_ID = p_ele_entry_id ;
4336
4337 ----------------------------------
4338
4339 /* Cursor to retrieve run result value of Main Elements */
4340 CURSOR csr_result_value(p_iv_id NUMBER
4341 ,p_ele_type_id NUMBER
4342 ,p_assignment_action_id NUMBER) IS
4343 SELECT rrv.result_value val
4344 ,rr.element_entry_id ele_entry_id
4345 FROM pay_run_result_values rrv
4346 ,pay_run_results rr
4347 ,pay_assignment_actions paa
4348 ,pay_payroll_actions ppa
4349 WHERE rrv.input_value_id = p_iv_id
4350 AND rr.element_type_id = p_ele_type_id
4351 AND rr.run_result_id = rrv.run_result_id
4352 AND rr.assignment_action_id = paa.assignment_action_id
4353 AND paa.assignment_action_id = p_assignment_action_id
4354 AND ppa.payroll_action_id = paa.payroll_action_id
4355 AND ppa.action_type IN ('Q','R')
4356 AND rrv.result_value IS NOT NULL;
4357
4358 -----------------------------------------
4359
4360 /* cursor to get the element type id for 'Tax' element */
4361 /*
4362 CURSOR csr_get_tax_element IS
4363 SELECT element_type_id
4364 FROM pay_element_types_f
4365 WHERE element_name = 'Tax'
4366 AND legislation_code = 'NO'
4367 AND business_group_id IS NULL
4368 AND p_date_earned BETWEEN effective_start_date AND effective_end_date ;
4369 */
4370
4371 -----------------------------------------
4372
4373 -- cursor to get the name of the element
4374
4375 cursor csr_element_name (p_element_type_id NUMBER) IS
4376 select element_name
4377 from pay_element_types_f
4378 where element_type_id = p_element_type_id
4379 and p_date_earned between effective_start_date and effective_end_date ;
4380
4381 -----------------------------------------
4382
4383 -- cursor to get the input value id
4384
4385 cursor csr_inp_val_id (p_element_type_id NUMBER , p_inp_val_name pay_input_values_f.name%TYPE ) IS
4386 select input_value_id
4387 from pay_input_values_f
4388 where element_type_id = p_element_type_id
4389 and name = p_inp_val_name
4390 and business_group_id is null
4391 and legislation_code = 'NO'
4392 and p_date_earned between effective_start_date and effective_end_date ;
4393
4394 -----------------------------------------
4395
4396 /* Cursor to retrieve Business Group Id */
4397 CURSOR csr_bus_grp_id(p_organization_id NUMBER) IS
4398 SELECT business_group_id
4399 FROM hr_organization_units
4400 WHERE organization_id = p_organization_id;
4401
4402 -----------------------------------------
4403
4404 -- Cursor to get the 'Tax Period Override Element' details
4405 CURSOR csr_tax_period_override (p_assignment_id NUMBER, p_input_value VARCHAR2) IS
4406 SELECT eev1.screen_entry_value screen_entry_value
4407 FROM per_all_assignments_f asg1
4408 ,pay_element_links_f el
4409 ,pay_element_types_f et
4410 ,pay_input_values_f iv1
4411 ,pay_element_entries_f ee
4412 ,pay_element_entry_values_f eev1
4413 WHERE asg1.assignment_id = p_assignment_id
4414 AND p_date_earned BETWEEN asg1.effective_start_date AND asg1.effective_end_date
4415 AND et.element_name = 'Tax Period Override Element'
4416 AND et.legislation_code = 'NO'
4417 AND iv1.element_type_id = et.element_type_id
4418 AND iv1.name = p_input_value
4419 AND el.element_type_id = et.element_type_id
4420 AND ee.assignment_id = asg1.assignment_id
4421 AND ee.element_link_id = el.element_link_id
4422 AND eev1.element_entry_id = ee.element_entry_id
4423 AND eev1.input_value_id = iv1.input_value_id
4424 AND p_date_earned BETWEEN ee.effective_start_date AND ee.effective_end_date
4425 AND p_date_earned BETWEEN eev1.effective_start_date AND eev1.effective_end_date ;
4426 ---------------
4427
4428 -- cursor to get the reduced tax rule for the period
4429 CURSOR csr_get_reduced_tax_rule (p_asg_act_id number) IS
4430 SELECT ptp.prd_information1
4431 FROM per_time_periods ptp , pay_payroll_actions ppa , pay_assignment_actions paa
4432 WHERE paa.ASSIGNMENT_ACTION_ID = p_asg_act_id
4433 AND ppa.payroll_action_id = paa.PAYROLL_ACTION_ID
4434 AND ppa.time_period_id = ptp.time_period_id ;
4435
4436 -------------------------------------
4437
4438 l_result_value pay_run_result_values.result_value%TYPE := 0;
4439 l_action_info_id NUMBER;
4440 l_ovn NUMBER;
4441 l_element_context VARCHAR2(10);
4442 l_index NUMBER := 0;
4443 l_formatted_value VARCHAR2(50) := NULL;
4444 l_flag NUMBER := 0;
4445 l_le_org_id NUMBER;
4446 l_ele_code VARCHAR2(240) := NULL ;
4447 l_ele_entry_id NUMBER;
4448 l_payslip_info varchar2(240);
4449 l_balance_value NUMBER := 0;
4450 l_archive VARCHAR2(2);
4451 l_tax_ele_typ_id NUMBER;
4452
4453 l_element_name varchar2(240);
4454 l_inp_val_id NUMBER;
4455 l_suppl_method varchar2(240);
4456 l_bg_id NUMBER;
4457 l_arhive_prim_bal VARCHAR2(5);
4458
4459 l_msg_txt VARCHAR2(240);
4460 l_msg_name VARCHAR2(240);
4461 l_earn_period VARCHAR2(240);
4462 l_earn_period_mul VARCHAR2(240);
4463 l_tax_per_override_msg_txt VARCHAR2(240);
4464 l_reduced_tax_rule VARCHAR2(240);
4465 l_reduced_tax_rule_msg_txt VARCHAR2(240);
4466 ----------------
4467
4468 BEGIN
4469
4470 IF g_debug THEN
4471 hr_utility.set_location(' Entering Procedure ARCHIVE_MAIN_ELEMENTS',320);
4472 END IF;
4473
4474 -- Archiving Earnings Elements
4475
4476 -- fnd_file.put_line(fnd_file.log,'started archiving NO Earnings Elements');
4477
4478 -- fnd_file.put_line(fnd_file.log,'LOC 0 => p_archive_assact_id = ' || p_archive_assact_id );
4479
4480 /*
4481 OPEN csr_get_tax_element;
4482 FETCH csr_get_tax_element INTO l_tax_ele_typ_id ;
4483 CLOSE csr_get_tax_element;
4484 */
4485
4486 l_le_org_id := NULL ;
4487
4488 /* get the legal employer */
4489 OPEN csr_get_le_org_id (p_assignment_action_id );
4490 FETCH csr_get_le_org_id INTO l_le_org_id ;
4491 CLOSE csr_get_le_org_id ;
4492
4493 -- fnd_file.put_line(fnd_file.log,'l_le_org_id = ' || l_le_org_id);
4494
4495
4496 -- get the business group id for the LE
4497 OPEN csr_bus_grp_id(l_le_org_id);
4498 FETCH csr_bus_grp_id INTO l_bg_id;
4499 CLOSE csr_bus_grp_id;
4500
4501
4502 FOR csr_rec IN csr_get_main_elements (p_payroll_action_id ) LOOP
4503
4504
4505 l_arhive_prim_bal := 'N' ;
4506 l_ele_code := NULL ;
4507
4508 /* get the element code from the legal employer */
4509 OPEN csr_ele_code (csr_rec.element_type_id , l_le_org_id );
4510 FETCH csr_ele_code INTO l_ele_code ;
4511 CLOSE csr_ele_code ;
4512
4513 /*
4514 IF (l_ele_code IS NOT NULL) THEN
4515 l_ele_code := hr_general.decode_lookup('NO_ELEMENT_CODES',l_ele_code);
4516 END IF;
4517 */
4518
4519 -- fnd_file.put_line(fnd_file.log,'l_ele_code = ' || l_ele_code);
4520
4521 -- get the name of the element
4522 OPEN csr_element_name (csr_rec.element_type_id) ;
4523 FETCH csr_element_name INTO l_element_name ;
4524 CLOSE csr_element_name ;
4525
4526
4527 IF ( l_element_name = 'Tax' ) THEN
4528
4529 -- fnd_file.put_line(fnd_file.log,'Found Tax Element' );
4530 -- start processing to populate message for Supplementary Run
4531
4532 -- get the input value id for the 'Supplementary Run Method' input value on Tax element
4533
4534 OPEN csr_inp_val_id ( csr_rec.element_type_id , 'Supplementary Run Method' ) ;
4535 FETCH csr_inp_val_id INTO l_inp_val_id ;
4536 CLOSE csr_inp_val_id ;
4537
4538 -- get the 'Supplementary Run Method' run result value
4539 OPEN csr_result_value(l_inp_val_id
4540 ,csr_rec.element_type_id
4541 ,p_assignment_action_id);
4542
4543 FETCH csr_result_value INTO l_result_value , l_ele_entry_id ;
4544 CLOSE csr_result_value ;
4545
4546 -- fnd_file.put_line(fnd_file.log,'l_result_value (for Supplementary Run) = ' ||l_result_value );
4547
4548 IF (l_result_value = 'PERIOD') THEN
4549 l_msg_name := 'PAY_376884_NO_SUP_RUN_PERIOD' ;
4550 ELSIF (l_result_value = 'PERCENTAGE') THEN
4551 l_msg_name := 'PAY_376885_NO_SUP_RUN_PERCENT' ;
4552 END IF;
4553
4554 IF ( (l_result_value IS NOT NULL) AND (l_msg_name IS NOT NULL) ) THEN
4555 -- set the message name and get the message text
4556 hr_utility.set_message (801, l_msg_name);
4557 l_msg_txt := hr_utility.get_message ;
4558
4559 -- fnd_file.put_line(fnd_file.log,'l_msg_txt (for Supplementary Run) = ' ||l_msg_txt );
4560
4561 /* Arcvhive the message */
4562 pay_action_information_api.create_action_information (
4563 p_action_information_id => l_action_info_id
4564 ,p_action_context_id => p_archive_assact_id
4565 ,p_action_context_type => 'AAP'
4566 ,p_object_version_number => l_ovn
4567 ,p_effective_date => p_effective_date
4568 ,p_source_id => NULL
4569 ,p_source_text => NULL
4570 ,p_action_information_category => 'EMPLOYEE OTHER INFORMATION'
4571 ,p_action_information1 => l_bg_id
4572 ,p_action_information2 => 'MESG' -- Message Context
4573 ,p_action_information3 => NULL
4574 ,p_action_information4 => NULL
4575 ,p_action_information5 => NULL
4576 ,p_action_information6 => l_msg_txt );
4577
4578 END IF; -- end if l_msg_name IS NOT NULL
4579
4580 -- end processing to populate message for Supplementary Run
4581
4582
4583 -- start processing to populate message for 'Tax Period Override Element'
4584
4585 IF ( (l_result_value IS NULL) OR (l_result_value <> 'PERCENTAGE') ) THEN
4586
4587
4588 -- fnd_file.put_line(fnd_file.log,'l_result_value <> PERCENTAGE ' );
4589
4590 OPEN csr_tax_period_override (p_assignment_id , 'Earnings Period' ) ;
4591 FETCH csr_tax_period_override INTO l_earn_period ;
4592 CLOSE csr_tax_period_override ;
4593
4594 OPEN csr_tax_period_override (p_assignment_id , 'Earnings Period Multiplier' ) ;
4595 FETCH csr_tax_period_override INTO l_earn_period_mul ;
4596 CLOSE csr_tax_period_override ;
4597
4598
4599 IF ( (l_earn_period IS NOT NULL) AND (l_earn_period_mul IS NOT NULL) ) THEN
4600
4601 l_earn_period := hr_general.decode_lookup('NO_PAYROLL_PERIOD',l_earn_period) ;
4602 hr_utility.set_message (801, 'PAY_376886_NO_TAX_PER_OVERRIDE' );
4603 hr_utility.set_message_token (801, 'EARN_PERIOD', l_earn_period);
4604 hr_utility.set_message_token (801, 'EARN_PER_MUL', l_earn_period_mul);
4605 l_tax_per_override_msg_txt := hr_utility.get_message ;
4606
4607 -- fnd_file.put_line(fnd_file.log,'l_earn_period = ' ||l_earn_period );
4608 -- fnd_file.put_line(fnd_file.log,'l_earn_period_mul = ' ||l_earn_period_mul );
4609 -- fnd_file.put_line(fnd_file.log,'l_tax_per_override_msg_txt = ' ||l_tax_per_override_msg_txt );
4610
4611 /* Arcvhive the message */
4612 pay_action_information_api.create_action_information (
4613 p_action_information_id => l_action_info_id
4614 ,p_action_context_id => p_archive_assact_id
4615 ,p_action_context_type => 'AAP'
4616 ,p_object_version_number => l_ovn
4617 ,p_effective_date => p_effective_date
4618 ,p_source_id => NULL
4619 ,p_source_text => NULL
4620 ,p_action_information_category => 'EMPLOYEE OTHER INFORMATION'
4621 ,p_action_information1 => l_bg_id
4622 ,p_action_information2 => 'MESG' -- Message Context
4623 ,p_action_information3 => NULL
4624 ,p_action_information4 => NULL
4625 ,p_action_information5 => NULL
4626 ,p_action_information6 => l_tax_per_override_msg_txt );
4627
4628 END IF;
4629 END IF; -- end if l_result_value <> 'PERCENTAGE'
4630
4631 -- end processing to populate message for 'Tax Period Override Element'
4632
4633
4634 -- start processing to populate message for Reduced Tax Rule
4635
4636 -- fnd_file.put_line(fnd_file.log,'before csr_get_reduced_tax_rule ' );
4637
4638 OPEN csr_get_reduced_tax_rule (p_assignment_action_id) ;
4639 FETCH csr_get_reduced_tax_rule INTO l_reduced_tax_rule ;
4640 CLOSE csr_get_reduced_tax_rule ;
4641
4642 -- fnd_file.put_line(fnd_file.log,'l_reduced_tax_rule = ' ||l_reduced_tax_rule );
4643
4644 IF (l_reduced_tax_rule IN ('H','Z')) THEN
4645 l_reduced_tax_rule := hr_general.decode_lookup('NO_TAX_PERIOD_RULES',l_reduced_tax_rule) ;
4646 hr_utility.set_message (801, 'PAY_376887_NO_REDUCED_TAX');
4647 hr_utility.set_message_token (801, 'REDUCED_TAX', l_reduced_tax_rule);
4648 l_reduced_tax_rule_msg_txt := hr_utility.get_message ;
4649
4650 -- fnd_file.put_line(fnd_file.log,'l_reduced_tax_rule = ' || l_reduced_tax_rule );
4651 -- fnd_file.put_line(fnd_file.log,'l_reduced_tax_rule_msg_txt = ' ||l_reduced_tax_rule_msg_txt );
4652
4653 /* Arcvhive the message */
4654 pay_action_information_api.create_action_information (
4655 p_action_information_id => l_action_info_id
4656 ,p_action_context_id => p_archive_assact_id
4657 ,p_action_context_type => 'AAP'
4658 ,p_object_version_number => l_ovn
4659 ,p_effective_date => p_effective_date
4660 ,p_source_id => NULL
4661 ,p_source_text => NULL
4662 ,p_action_information_category => 'EMPLOYEE OTHER INFORMATION'
4663 ,p_action_information1 => l_bg_id
4664 ,p_action_information2 => 'MESG' -- Message Context
4665 ,p_action_information3 => NULL
4666 ,p_action_information4 => NULL
4667 ,p_action_information5 => NULL
4668 ,p_action_information6 => l_reduced_tax_rule_msg_txt );
4669
4670 END IF;
4671
4672 -- end processing to populate message for Reduced Tax Rule
4673
4674 END IF; -- end if l_element_name = 'Tax'
4675
4676 -- end processing to populate message for Supplementary Run
4677
4678
4679 l_result_value := NULL;
4680 l_arhive_prim_bal := 'N' ;
4681
4682 BEGIN
4683 /*
4684
4685 OPEN csr_result_value(csr_rec.input_value_id ,csr_rec.element_type_id ,p_assignment_action_id);
4686 FETCH csr_result_value INTO l_result_value;
4687 CLOSE csr_result_value;
4688
4689 IF l_result_value is not null THEN
4690 pay_action_information_api.create_action_information (
4691 p_action_information_id => l_action_info_id
4692 ,p_action_context_id => p_archive_assact_id
4693 ,p_action_context_type => 'AAP'
4694 ,p_object_version_number => l_ovn
4695 ,p_effective_date => p_effective_date
4696 ,p_source_id => NULL
4697 ,p_source_text => NULL
4698 ,p_action_information_category => 'EMEA ELEMENT INFO'
4699 ,p_action_information1 => csr_rec.element_type_id
4700 ,p_action_information2 => csr_rec.input_value_id
4701 ,p_action_information3 => 'E'
4702 ,p_action_information4 => l_result_value --l_formatted_value
4703 ,p_action_information9 => 'Earning Element'
4704 ,p_assignment_id => p_assignment_id);
4705 END IF;
4706
4707 */
4708
4709 -- fnd_file.put_line(fnd_file.log,'------------------------------------------');
4710 -- fnd_file.put_line(fnd_file.log,'csr_rec.element_narrative = ' || csr_rec.element_narrative);
4711 -- fnd_file.put_line(fnd_file.log,'csr_rec.element_type_id = ' || csr_rec.element_type_id);
4712 -- fnd_file.put_line(fnd_file.log,'csr_rec.input_value_id = ' || csr_rec.input_value_id);
4713 -- fnd_file.put_line(fnd_file.log,'p_assignment_action_id = ' || p_assignment_action_id);
4714
4715
4716 l_result_value := NULL ;
4717 l_ele_entry_id := NULL ;
4718 l_balance_value := NULL ;
4719
4720 IF (csr_rec.prim_def_bal_id IS NOT NULL) THEN
4721
4722 -- get the primary balance ytd value for the element
4723
4724
4725 -- fnd_file.put_line(fnd_file.log,'------ loc 1 ---------------');
4726 -- fnd_file.put_line(fnd_file.log,'l_element_name = ' || l_element_name);
4727 -- fnd_file.put_line(fnd_file.log,'csr_rec.prim_def_bal_id = ' || csr_rec.prim_def_bal_id );
4728
4729
4730 l_balance_value := pay_balance_pkg.get_value(csr_rec.prim_def_bal_id , p_assignment_action_id );
4731
4732 /* Change for element classification name from 'Holiday Pay Earnings Adjustment' to 'Holiday Pay Earnings Adjust' */
4733
4734 -- IF csr_rec.ele_class in ('Earnings Adjustment' , 'Holiday Pay Earnings Adjustment') THEN
4735
4736 -- The following classifications have been obsoleted and will no longer be used.
4737 -- Holiday Pay Earnings Adjust => Holiday Pay Earnings Adjust Obsolete
4738 -- Commenting the code below.
4739
4740 -- IF csr_rec.ele_class in ('Earnings Adjustment' , 'Holiday Pay Earnings Adjust') THEN
4741 IF csr_rec.ele_class in ('Earnings Adjustment') THEN
4742
4743 -- l_balance_value := fnd_number.number_to_canonical (0 - fnd_number.canonical_to_number (l_balance_value) ) ;
4744 -- Bug Fix : 5909576, pay_balance_pkg.get_value already returns a value in NUMBER format.
4745 -- So we need not do a fnd_number.canonical_to_number on l_balance_value.
4746
4747 l_balance_value := (0 - l_balance_value ) ;
4748
4749 END IF;
4750
4751 -- fnd_file.put_line(fnd_file.log,'l_balance_value = ' || l_balance_value);
4752 -- fnd_file.put_line(fnd_file.log,'---------------------');
4753
4754
4755 END IF;
4756
4757
4758 -- fnd_file.put_line(fnd_file.log,'l_element_name = ' || l_element_name||' |||||||| csr_rec.prim_def_bal_id = ' || csr_rec.prim_def_bal_id);
4759
4760 /* get the element run result value */
4761 OPEN csr_result_value(csr_rec.input_value_id
4762 ,csr_rec.element_type_id
4763 ,p_assignment_action_id);
4764 LOOP
4765 FETCH csr_result_value INTO l_result_value , l_ele_entry_id ;
4766
4767 IF (csr_result_value%NOTFOUND) AND
4768 (csr_result_value%ROWCOUNT < 1) AND
4769 (csr_rec.prim_def_bal_id IS NOT NULL) AND
4770 (l_balance_value <> 0) THEN
4771
4772 l_arhive_prim_bal := 'Y' ;
4773 -- fnd_file.put_line(fnd_file.log,'LOC 2 => p_assignment_action_id , l_element_name = ' || p_assignment_action_id ||' , '||l_element_name);
4774 END IF ;
4775
4776 EXIT WHEN csr_result_value%NOTFOUND;
4777
4778 -- fnd_file.put_line(fnd_file.log,'LOC 3 => p_assignment_action_id , l_element_name = ' || p_assignment_action_id ||' , '||l_element_name);
4779
4780 -- l_balance_value := NULL ;
4781
4782
4783
4784 -- fnd_file.put_line(fnd_file.log,'l_result_value = ' || l_result_value);
4785 -- fnd_file.put_line(fnd_file.log,'l_ele_entry_id = ' || l_ele_entry_id);
4786 -- fnd_file.put_line(fnd_file.log,'before l_balance_value = ' || l_balance_value);
4787
4788 -- modifying the below condition to get the ytd value for primary balance
4789 -- only once in case of multiple element entries
4790
4791 -- IF (csr_rec.prim_def_bal_id IS NOT NULL) THEN
4792
4793 -- fnd_file.put_line(fnd_file.log,'l_element_name = ' || l_element_name||' |||||||| csr_rec.prim_def_bal_id = ' || csr_rec.prim_def_bal_id);
4794
4795 /*
4796 IF (csr_rec.prim_def_bal_id IS NOT NULL) THEN -- AND (csr_result_value%ROWCOUNT = 1) THEN
4797
4798 -- get the primary balance ytd value for the element
4799
4800
4801 fnd_file.put_line(fnd_file.log,'---------------------');
4802 fnd_file.put_line(fnd_file.log,'l_element_name = ' || l_element_name);
4803 fnd_file.put_line(fnd_file.log,'csr_rec.prim_def_bal_id = ' || csr_rec.prim_def_bal_id );
4804
4805
4806 l_balance_value := pay_balance_pkg.get_value(csr_rec.prim_def_bal_id , p_assignment_action_id );
4807
4808 fnd_file.put_line(fnd_file.log,'l_balance_value = ' || l_balance_value);
4809 fnd_file.put_line(fnd_file.log,'---------------------');
4810
4811
4812 END IF;
4813
4814 */
4815
4816 IF (csr_result_value%ROWCOUNT > 1) THEN
4817 l_balance_value := NULL ;
4818 END IF;
4819
4820 -- fnd_file.put_line(fnd_file.log,'csr_rec.prim_def_bal_id = ' || csr_rec.prim_def_bal_id);
4821 -- fnd_file.put_line(fnd_file.log,'csr_result_value%ROWCOUNT = ' || csr_result_value%ROWCOUNT);
4822 -- fnd_file.put_line(fnd_file.log,'after l_balance_value = ' || l_balance_value);
4823 -- fnd_file.put_line(fnd_file.log,'csr_rec.prim_def_bal_id = ' || csr_rec.prim_def_bal_id);
4824 -- fnd_file.put_line(fnd_file.log,'l_balance_value = ' || l_balance_value);
4825
4826 /* get the payslip information */
4827 l_payslip_info := NULL ;
4828
4829 OPEN csr_payslip_info (l_ele_entry_id );
4830 FETCH csr_payslip_info INTO l_payslip_info ;
4831 CLOSE csr_payslip_info ;
4832
4833 -- fnd_file.put_line(fnd_file.log,'l_payslip_info = ' || l_payslip_info);
4834
4835 -- if the element classification is 'Earnings Adjustment' or 'Holiday Pay Earnings Adjustment',
4836 -- then store the result as negative value
4837
4838 /* Change for element classification name from 'Holiday Pay Earnings Adjustment' to 'Holiday Pay Earnings Adjust' */
4839
4840 -- IF csr_rec.ele_class in ('Earnings Adjustment' , 'Holiday Pay Earnings Adjustment') THEN
4841
4842 -- The following classifications have been obsoleted and will no longer be used.
4843 -- Holiday Pay Earnings Adjust => Holiday Pay Earnings Adjust Obsolete
4844 -- Commenting the code below.
4845
4846 -- IF csr_rec.ele_class in ('Earnings Adjustment' , 'Holiday Pay Earnings Adjust') THEN
4847 IF csr_rec.ele_class in ('Earnings Adjustment') THEN
4848
4849 -- fnd_file.put_line(fnd_file.log,'before l_result_value = ' ||l_result_value );
4850 l_result_value := fnd_number.number_to_canonical (0 - fnd_number.canonical_to_number (l_result_value) ) ;
4851 -- fnd_file.put_line(fnd_file.log,'after l_result_value = ' ||l_result_value );
4852
4853 END IF;
4854
4855 IF (l_result_value is not null) THEN l_archive := 'Y';
4856 -- ELSIF (csr_rec.prim_def_bal_id IS NOT NULL) AND ( (l_balance_value <> 0) OR (l_balance_value IS NOT NULL) ) THEN l_archive := 'Y';
4857 -- ELSIF (csr_rec.prim_def_bal_id IS NOT NULL) AND ( l_balance_value <> 0 ) THEN l_archive := 'Y';
4858 ELSE l_archive := 'N';
4859 END IF;
4860
4861
4862 IF (l_archive = 'Y') THEN
4863
4864 -- fnd_file.put_line(fnd_file.log,'l_result_value is not null ');
4865
4866 pay_action_information_api.create_action_information (
4867 p_action_information_id => l_action_info_id
4868 ,p_action_context_id => p_archive_assact_id
4869 ,p_action_context_type => 'AAP'
4870 ,p_object_version_number => l_ovn
4871 ,p_effective_date => p_effective_date
4872 ,p_source_id => NULL
4873 ,p_source_text => NULL
4874 ,p_action_information_category => 'NO ELEMENT INFO'
4875 ,p_action_information1 => csr_rec.element_type_id
4876 ,p_action_information2 => csr_rec.input_value_id
4877 ,p_action_information3 => csr_rec.element_context
4878 ,p_action_information4 => l_result_value --l_formatted_value
4879 ,p_action_information9 => 'Main Element'
4880 ,p_action_information10 => csr_rec.prim_def_bal_id
4881 ,p_action_information11 => 'PBAL'
4882 ,p_action_information12 => l_balance_value
4883 ,p_action_information13 => l_ele_code
4884 ,p_action_information14 => l_payslip_info
4885 ,p_assignment_id => p_assignment_id);
4886
4887 -- fnd_file.put_line(fnd_file.log,'******** archived = '||csr_rec.element_narrative );
4888
4889 END IF;
4890
4891 END LOOP;
4892 CLOSE csr_result_value;
4893
4894 -- archive results if run result was NULL but primary balance value is not null
4895
4896 IF (l_arhive_prim_bal = 'Y') THEN
4897
4898 -- though the run result is NULL, the ytd balance has a value
4899 -- so we will archive this result
4900
4901 l_result_value := NULL ;
4902 l_payslip_info := NULL ;
4903
4904 -- fnd_file.put_line(fnd_file.log,'LOC 5 => p_assignment_action_id , l_element_name = ' || p_assignment_action_id ||' , '||l_element_name);
4905
4906 pay_action_information_api.create_action_information (
4907 p_action_information_id => l_action_info_id
4908 ,p_action_context_id => p_archive_assact_id
4909 ,p_action_context_type => 'AAP'
4910 ,p_object_version_number => l_ovn
4911 ,p_effective_date => p_effective_date
4912 ,p_source_id => NULL
4913 ,p_source_text => NULL
4914 ,p_action_information_category => 'NO ELEMENT INFO'
4915 ,p_action_information1 => csr_rec.element_type_id
4916 ,p_action_information2 => csr_rec.input_value_id
4917 ,p_action_information3 => csr_rec.element_context
4918 ,p_action_information4 => l_result_value --l_formatted_value
4919 ,p_action_information9 => 'Main Element'
4920 ,p_action_information10 => csr_rec.prim_def_bal_id
4921 ,p_action_information11 => 'PBAL'
4922 ,p_action_information12 => l_balance_value
4923 ,p_action_information13 => l_ele_code
4924 ,p_action_information14 => l_payslip_info
4925 ,p_assignment_id => p_assignment_id);
4926
4927 END IF ;
4928
4929
4930 EXCEPTION WHEN OTHERS THEN
4931 g_err_num := SQLCODE;
4932 /*fnd_file.put_line(fnd_file.log,'ORA_ERR: ' || g_err_num || 'In ARCHIVE_MAIN_ELEMENTS');*/
4933
4934 IF g_debug THEN
4935 hr_utility.set_location('ORA_ERR: ' || g_err_num || 'In ARCHIVE_MAIN_ELEMENTS',330);
4936 END IF;
4937 END;
4938 END LOOP;
4939
4940 -- fnd_file.put_line(fnd_file.log,'finished archiving NO Earnings Elements');
4941
4942 IF g_debug THEN
4943 hr_utility.set_location(' Leaving Procedure ARCHIVE_MAIN_ELEMENTS',340);
4944 END IF;
4945
4946 END ARCHIVE_MAIN_ELEMENTS;
4947
4948 ------------------------------------ End of package ----------------------------------------------------------------
4949
4950 END PAY_NO_PAYSLIP_ARCHIVE;