[Home] [Help]
PACKAGE BODY: APPS.PAY_NO_PAYSLIP_ARCHIVE
Source
1 PACKAGE BODY PAY_NO_PAYSLIP_ARCHIVE AS
2 /* $Header: pynoparc.pkb 120.7 2007/07/13 11:57:10 nmuthusa noship $ */
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 = 'C' -- Completed
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 = 'C' -- Completed
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
1452 -------------
1453 rec_person_details csr_person_details%ROWTYPE;
1454 rec_primary_address csr_primary_address%ROWTYPE;
1455 rec_employer_address csr_employer_address%ROWTYPE;
1456 rec_org_address csr_organization_address%ROWTYPE;
1457 l_nationality per_all_people_f.nationality%TYPE;
1458 l_position per_all_positions.name%TYPE;
1459 l_hire_date per_periods_of_service.date_start%TYPE;
1460 l_grade per_grades.name%TYPE;
1461 l_currency hr_organization_information.org_information10%TYPE;
1462 l_organization hr_organization_units.name%TYPE;
1463 l_pay_location hr_locations_all.address_line_1%TYPE;
1464 l_postal_code VARCHAR2(80);
1465 l_country VARCHAR2(30);
1466 l_emp_postal_code VARCHAR2(80);
1467 l_emp_country VARCHAR2(30);
1468 l_org_city VARCHAR2(20);
1469 l_org_country VARCHAR2(30);
1470 l_action_info_id NUMBER;
1471 l_ovn NUMBER;
1472 l_person_id NUMBER;
1473 l_employer_name hr_organization_units.name%TYPE;
1474 l_local_unit_id hr_organization_units.organization_id%TYPE;
1475 l_legal_employer_id hr_organization_units.organization_id%TYPE;
1476 l_job PER_JOBS.NAME%TYPE;
1477 l_org_struct_ver_id hr_organization_information.org_information1%TYPE;
1478 l_top_org_id per_org_structure_elements.organization_id_parent%TYPE;
1479 l_cost_center pay_cost_allocations_v.concatenated_segments%TYPE;
1480 l_defined_balance_id NUMBER;
1481 l_balance_value NUMBER;
1482 l_formatted_value VARCHAR2(50) := NULL;
1483 l_org_exists NUMBER :=0;
1484 le_phone_num VARCHAR2(240);
1485 le_phone_num_str VARCHAR2(1000);
1486 l_cvr_num VARCHAR2(240);
1487 l_le_org_num VARCHAR2(240);
1488
1489 l_prim_asg_id NUMBER;
1490 l_prim_local_unit NUMBER;
1491 l_prim_legal_emp NUMBER;
1492 l_diff_le_text VARCHAR2(2);
1493 l_msg_txt VARCHAR2(240);
1494
1495 -- l_lower_base NUMBER :=0;
1496 -- l_upper_base NUMBER :=0;
1497 -------------
1498
1499 BEGIN
1500
1501 IF g_debug THEN
1502 hr_utility.set_location(' Entering Procedure ARCHIVE_EMPLOYEE_DETAILS',160);
1503 END IF;
1504
1505 /*fnd_file.put_line(fnd_file.log,'Entering Procedure ARCHIVE_EMPLOYEE_DETAILS');*/
1506
1507 /* PERSON AND ADDRESS DETAILS */
1508 OPEN csr_person_details(p_assignment_id);
1509 FETCH csr_person_details INTO rec_person_details;
1510 CLOSE csr_person_details;
1511
1512 -- fnd_file.put_line(fnd_file.log,'after cursor csr_person_details ');
1513
1514 OPEN csr_primary_address(rec_person_details.person_id);
1515 FETCH csr_primary_address INTO rec_primary_address;
1516 CLOSE csr_primary_address;
1517
1518 -- fnd_file.put_line(fnd_file.log,'after cursor csr_primary_address ');
1519
1520 -- rec_person_details.org_id is the org_id of the HR org at asg level
1521
1522 OPEN csr_organization_address(rec_person_details.org_id);
1523 FETCH csr_organization_address INTO rec_org_address;
1524 CLOSE csr_organization_address;
1525
1526 -- fnd_file.put_line(fnd_file.log,'after cursor csr_organization_address ');
1527
1528 /*fnd_file.put_line(fnd_file.log,'Entering Procedure ARCHIVE_EMPLOYEE_DETAILS 2');*/
1529
1530 /* GRADE AND POSITION */
1531
1532 /* Changed IF condition construct */
1533 IF(rec_person_details.pos_id IS NOT NULL) THEN
1534 OPEN csr_position(rec_person_details.pos_id);
1535 FETCH csr_position INTO l_position;
1536 CLOSE csr_position;
1537 END IF;
1538
1539 IF(rec_person_details.grade_id IS NOT NULL) THEN
1540 OPEN csr_grade(rec_person_details.grade_id);
1541 FETCH csr_grade INTO l_grade;
1542 CLOSE csr_grade;
1543 END IF;
1544
1545 /*fnd_file.put_line(fnd_file.log,'Entering Procedure ARCHIVE_EMPLOYEE_DETAILS 3');*/
1546
1547 /* CURRENCY */
1548
1549
1550 -- rec_person_details.org_id is the org_id of the HR org at asg level
1551 OPEN csr_bus_grp_id(rec_person_details.org_id);
1552 FETCH csr_bus_grp_id INTO l_bg_id;
1553 CLOSE csr_bus_grp_id;
1554
1555 OPEN csr_currency(l_bg_id);
1556 FETCH csr_currency INTO l_currency;
1557 CLOSE csr_currency;
1558
1559 g_format_mask := FND_CURRENCY.GET_FORMAT_MASK(l_currency,40);
1560
1561 /* COST CENTER */
1562 OPEN csr_cost_center(p_assignment_id);
1563 FETCH csr_cost_center INTO l_cost_center;
1564 CLOSE csr_cost_center;
1565
1566
1567 /* HIRE DATE */
1568 OPEN csr_hire_date(p_assignment_id);
1569 FETCH csr_hire_date INTO l_hire_date;
1570 CLOSE csr_hire_date;
1571
1572 /*NATIONALITY*/
1573 l_nationality := hr_general.decode_lookup('NATIONALITY',rec_person_details.nationality);
1574
1575 /*fnd_file.put_line(fnd_file.log,'Entering Procedure ARCHIVE_EMPLOYEE_DETAILS 4');*/
1576
1577 /*Local Unit*/
1578
1579 OPEN csr_scl_details(p_assignment_id);
1580 FETCH csr_scl_details INTO l_local_unit_id;
1581 CLOSE csr_scl_details;
1582
1583
1584 /* Getting Legal Employer */
1585
1586 OPEN csr_legal_employer(l_local_unit_id);
1587 FETCH csr_legal_employer INTO l_legal_employer_id;
1588 CLOSE csr_legal_employer;
1589
1590 /*Legal Employer */
1591 /*
1592 OPEN csr_scl_details(p_assignment_id);
1593 FETCH csr_scl_details INTO l_legal_employer_id ;
1594 CLOSE csr_scl_details;
1595 */
1596
1597 OPEN csr_employer_address(l_legal_employer_id);
1598 FETCH csr_employer_address INTO rec_employer_address;
1599 CLOSE csr_employer_address;
1600
1601 IF(rec_person_details.loc_id IS NOT NULL) THEN
1602 l_pay_location := NULL;
1603
1604 OPEN csr_pay_location(rec_person_details.loc_id);
1605 FETCH csr_pay_location INTO l_pay_location;
1606 CLOSE csr_pay_location;
1607 ELSE
1608 l_pay_location := NULL;
1609 END IF;
1610
1611
1612 IF(rec_person_details.job_id IS NOT NULL) THEN
1613
1614 OPEN csr_job(rec_person_details.job_id);
1615 FETCH csr_job INTO l_job;
1616 CLOSE csr_job;
1617 ELSE
1618 l_job := NULL;
1619 END IF;
1620
1621 -- HR ORG at asg level Name
1622 OPEN csr_org_name (rec_person_details.org_id) ;
1623 FETCH csr_org_name INTO l_organization ;
1624 CLOSE csr_org_name ;
1625
1626 -- Legal Employer Name
1627 OPEN csr_org_name (l_legal_employer_id) ;
1628 FETCH csr_org_name INTO l_employer_name ;
1629 CLOSE csr_org_name ;
1630
1631
1632 /*fnd_file.put_line(fnd_file.log,'Entering Procedure ARCHIVE_EMPLOYEE_DETAILS 5');*/
1633
1634 IF rec_primary_address.style = 'NO' THEN
1635 l_postal_code := hr_general.decode_lookup('NO_POSTAL_CODE',rec_primary_address.postal_code);
1636 ELSE
1637 l_postal_code := rec_primary_address.postal_code;
1638 END IF;
1639
1640 l_country := PAY_NO_PAYSLIP_ARCHIVE.get_country_name(rec_primary_address.country);
1641
1642 IF rec_employer_address.style = 'NO' THEN
1643 l_emp_postal_code := hr_general.decode_lookup('NO_POSTAL_CODE',rec_employer_address.postal_code);
1644 ELSE
1645 l_emp_postal_code := rec_employer_address.postal_code;
1646 END IF;
1647
1648 l_emp_country := PAY_NO_PAYSLIP_ARCHIVE.get_country_name(rec_employer_address.country);
1649
1650 /* Getting Legal Employer Phone Number String */
1651
1652 le_phone_num_str := NULL;
1653
1654
1655 /* Get Legal Employer Org Number */
1656
1657 OPEN csr_le_org_num (l_legal_employer_id ) ;
1658 FETCH csr_le_org_num INTO l_le_org_num ;
1659 CLOSE csr_le_org_num ;
1660
1661 /* Archive a message if the current Legal Employer is different from the Legal Employer of the Primary Assignment */
1662
1663 l_diff_le_text := 'N' ;
1664
1665 /* get the primary assignment id */
1666 OPEN csr_get_prim_asg (p_date_earned , p_assignment_id );
1667 FETCH csr_get_prim_asg INTO l_prim_asg_id ;
1668 CLOSE csr_get_prim_asg ;
1669
1670 IF ( p_assignment_id <> l_prim_asg_id )
1671 THEN
1672
1673 /* the Local Unit for the current assignment = l_local_unit_id */
1674
1675 /* OPEN csr_scl_details (p_assignment_id ) ;
1676 FETCH csr_scl_details INTO l_local_unit ;
1677 CLOSE csr_scl_details ; */
1678
1679 /* get the Local Unit for the primary assignment */
1680 OPEN csr_scl_details (l_prim_asg_id ) ;
1681 FETCH csr_scl_details INTO l_prim_local_unit ;
1682 CLOSE csr_scl_details ;
1683
1684 IF (l_local_unit_id <> l_prim_local_unit)
1685 THEN
1686
1687 /* the Legal Employer for the current assignment = l_legal_employer_id */
1688
1689 /* OPEN csr_legal_employer (l_local_unit ) ;
1690 FETCH csr_legal_employer INTO l_legal_emp ;
1691 CLOSE csr_legal_employer ; */
1692
1693 /* get the Legal Employer for the primary assignment */
1694 OPEN csr_legal_employer (l_prim_local_unit ) ;
1695 FETCH csr_legal_employer INTO l_prim_legal_emp ;
1696 CLOSE csr_legal_employer ;
1697
1698 IF (l_legal_employer_id <> l_prim_legal_emp) THEN
1699
1700 l_diff_le_text := 'Y' ;
1701
1702 /* set the message name and get the message text */
1703 hr_utility.set_message (801, 'PAY_376864_NO_SEC_ASG_LE_DIFF');
1704 l_msg_txt := hr_utility.get_message ;
1705
1706 /* Arcvhive the message */
1707 pay_action_information_api.create_action_information (
1708 p_action_information_id => l_action_info_id
1709 ,p_action_context_id => p_archive_assact_id
1710 ,p_action_context_type => 'AAP'
1711 ,p_object_version_number => l_ovn
1712 ,p_effective_date => p_effective_date
1713 ,p_source_id => NULL
1714 ,p_source_text => NULL
1715 ,p_action_information_category => 'EMPLOYEE OTHER INFORMATION'
1716 ,p_action_information1 => l_bg_id
1717 ,p_action_information2 => 'MESG' -- Message Context
1718 ,p_action_information3 => NULL
1719 ,p_action_information4 => NULL
1720 ,p_action_information5 => NULL
1721 ,p_action_information6 => l_msg_txt );
1722
1723 END IF ;
1724
1725 END IF ;
1726
1727 END IF ;
1728
1729 /* Finished archiving a message if the current Legal Employer is different from the Legal Employer of the Primary Assignment */
1730
1731 /* INSERT PERSON DETAILS */
1732
1733 pay_action_information_api.create_action_information (
1734 p_action_information_id => l_action_info_id
1735 ,p_action_context_id => p_archive_assact_id
1736 ,p_action_context_type => 'AAP'
1737 ,p_object_version_number => l_ovn
1738 ,p_effective_date => p_effective_date
1739 ,p_source_id => NULL
1740 ,p_source_text => NULL
1741 ,p_action_information_category => 'EMPLOYEE DETAILS'
1742 ,p_action_information1 => rec_person_details.full_name
1743 ,p_action_information2 => l_legal_employer_id -- Legal Employer Org ID
1744 ,p_action_information4 => rec_person_details.ni_number
1745 ,p_action_information7 => l_grade
1746 ,p_action_information10 => rec_person_details.emp_num
1747 ,p_action_information12 => fnd_date.date_to_displaydate(l_hire_date) -- fnd_date.date_to_canonical(l_hire_date)
1748 ,p_action_information14 => rec_person_details.asg_num
1749 ,p_action_information15 => l_organization -- name of HR Org at asg level
1750 ,p_action_information16 => p_time_period_id
1751 ,p_action_information17 => l_job
1752 ,p_action_information18 => l_employer_name -- Legal Employer Name
1753 ,p_action_information19 => l_position
1754 ,p_action_information25 => le_phone_num_str
1755 ,p_action_information30 => l_pay_location
1756 ,p_assignment_id => p_assignment_id);
1757
1758
1759 /* INSERT ADDRESS DETAILS */
1760 IF rec_primary_address.AL1 IS NOT NULL THEN /* CHECK IF EMPLOYEE HAS BEEN GIVEN A PRIMARY ADDRESS */
1761 pay_action_information_api.create_action_information (
1762 p_action_information_id => l_action_info_id
1763 ,p_action_context_id => p_archive_assact_id
1764 ,p_action_context_type => 'AAP'
1765 ,p_object_version_number => l_ovn
1766 ,p_effective_date => p_effective_date
1767 ,p_source_id => NULL
1768 ,p_source_text => NULL
1769 ,p_action_information_category => 'ADDRESS DETAILS'
1770 ,p_action_information1 => rec_primary_address.person_id
1771 ,p_action_information5 => rec_primary_address.AL1
1772 ,p_action_information6 => rec_primary_address.AL2
1773 ,p_action_information7 => rec_primary_address.AL3
1774 ,p_action_information12 => l_postal_code
1775 ,p_action_information13 => l_country
1776 ,p_action_information14 => 'Employee Address'
1777 ,p_assignment_id => p_assignment_id);
1778 ELSE
1779 /* INSERT EMPLOYER ADDRESS AS EMPLOYEE'S PRIMARY ADDRESS */
1780 pay_action_information_api.create_action_information (
1781 p_action_information_id => l_action_info_id
1782 ,p_action_context_id => p_archive_assact_id
1783 ,p_action_context_type => 'AAP'
1784 ,p_object_version_number => l_ovn
1785 ,p_effective_date => p_effective_date
1786 ,p_source_id => NULL
1787 ,p_source_text => NULL
1788 ,p_action_information_category => 'ADDRESS DETAILS'
1789 ,p_action_information1 => rec_primary_address.person_id
1790 ,p_action_information5 => NULL
1791 ,p_action_information6 => NULL
1792 ,p_action_information7 => NULL
1793 ,p_action_information8 => NULL
1794 ,p_action_information9 => NULL
1795 ,p_action_information10 => NULL
1796 ,p_action_information11 => NULL
1797 ,p_action_information12 => NULL
1798 ,p_action_information13 => NULL
1799 ,p_action_information14 => 'Employee Address'
1800 ,p_assignment_id => p_assignment_id);
1801 END IF;
1802
1803 /*fnd_file.put_line(fnd_file.log,'Entering Procedure ARCHIVE_EMPLOYEE_DETAILS 9');*/
1804
1805 /* INSERT EMPLOYER'S ADDRESS (ORGANIZATION ADDRESS)*/
1806
1807 BEGIN
1808 l_org_exists := 0;
1809
1810 SELECT 1
1811 INTO l_org_exists
1812 FROM pay_action_information
1813 WHERE action_context_id = p_payroll_action_id
1814 AND action_information1 = l_legal_employer_id -- rec_person_details.org_id
1815 AND effective_date = p_effective_date
1816 AND action_information_category = 'ADDRESS DETAILS';
1817
1818 EXCEPTION
1819
1820 WHEN NO_DATA_FOUND THEN
1821 pay_action_information_api.create_action_information (
1822 p_action_information_id => l_action_info_id
1823 ,p_action_context_id => p_payroll_action_id
1824 ,p_action_context_type => 'PA'
1825 ,p_object_version_number => l_ovn
1826 ,p_effective_date => p_effective_date
1827 ,p_source_id => NULL
1828 ,p_source_text => NULL
1829 ,p_action_information_category => 'ADDRESS DETAILS'
1830 ,p_action_information1 => l_legal_employer_id -- rec_person_details.org_id
1831 ,p_action_information5 => rec_employer_address.AL1
1832 ,p_action_information6 => rec_employer_address.AL2
1833 ,p_action_information7 => rec_employer_address.AL3
1834 ,p_action_information12 => l_emp_postal_code
1835 ,p_action_information13 => l_emp_country
1836 ,p_action_information14 => 'Employer Address'
1837 ,p_action_information26 => l_le_org_num ); -- using Localization Specific1 for Legal Employer CVR Number
1838
1839 WHEN OTHERS THEN
1840 NULL;
1841 END;
1842
1843 /*fnd_file.put_line(fnd_file.log,'Entering Procedure ARCHIVE_EMPLOYEE_DETAILS 10');*/
1844
1845 --
1846 IF g_debug THEN
1847 hr_utility.set_location(' Leaving Procedure ARCHIVE_EMPLOYEE_DETAILS',170);
1848 END IF;
1849 --
1850
1851 EXCEPTION WHEN OTHERS THEN
1852 g_err_num := SQLCODE;
1853 /*fnd_file.put_line(fnd_file.log,'ORA_ERR: ' || g_err_num || 'In ARCHIVE_EMPLOYEE_DETAILS');*/
1854
1855 IF g_debug THEN
1856 hr_utility.set_location('ORA_ERR: ' || g_err_num || 'In ARCHIVE_EMPLOYEE_DETAILS',180);
1857 END IF;
1858
1859 END ARCHIVE_EMPLOYEE_DETAILS;
1860
1861 ----------------------------------- PROCEDURE ARCHIVE_ELEMENT_INFO ---------------------------------------------------------------
1862
1863 /* EARNINGS REGION, DEDUCTIONS REGION */
1864
1865 PROCEDURE ARCHIVE_ELEMENT_INFO(p_payroll_action_id IN NUMBER
1866 ,p_effective_date IN DATE
1867 ,p_date_earned IN DATE
1868 ,p_pre_payact_id IN NUMBER)
1869 IS
1870 ----------------
1871
1872 /* Cursor to retrieve Earnings Element Information */
1873
1874 /*
1875
1876 CURSOR csr_ear_element_info IS
1877 SELECT nvl(pettl.reporting_name,pettl.element_name) rep_name
1878 ,et.element_type_id element_type_id
1879 ,iv.input_value_id input_value_id
1880 ,iv.uom uom
1881 FROM pay_element_types_f et
1882 , pay_element_types_f_tl pettl
1883 , pay_input_values_f iv
1884 , pay_element_classifications classification
1885 WHERE et.element_type_id = iv.element_type_id
1886 AND et.element_type_id = pettl.element_type_id
1887 AND pettl.language = USERENV('LANG')
1888 AND iv.name = 'Pay Value'
1889 AND classification.classification_id = et.classification_id
1890 AND classification.classification_name
1891 IN ('Absence','Direct Payments','Earnings','Supplementary Earnings')
1892 AND p_date_earned BETWEEN et.effective_start_date AND et.effective_end_date
1893 AND p_date_earned BETWEEN iv.effective_start_date AND iv.effective_end_date
1894 AND ((et.business_group_id IS NULL AND et.legislation_code = 'NO')
1895 OR (et.business_group_id = g_business_group_id AND et.legislation_code IS NULL));
1896
1897 */
1898 -----------------
1899
1900 /* Cursor to retrieve Deduction Element Information */
1901
1902 /*
1903 CURSOR csr_ded_element_info IS
1904 SELECT nvl(pettl.reporting_name,pettl.element_name) rep_name
1905 ,et.element_type_id element_type_id
1906 ,iv.input_value_id input_value_id
1907 ,iv.uom uom
1908 FROM pay_element_types_f et
1909 , pay_element_types_f_tl pettl
1910 , pay_input_values_f iv
1911 , pay_element_classifications classification
1912 WHERE et.element_type_id = iv.element_type_id
1913 AND et.element_type_id = pettl.element_type_id
1914 AND pettl.language = USERENV('LANG')
1915 AND iv.name = 'Pay Value'
1916 AND classification.classification_id = et.classification_id
1917 AND classification.classification_name
1918 IN ('Involuntary Deductions','Pre-tax Deductions','Statutory Deductions','Voluntary Deductions')
1919 AND p_date_earned BETWEEN et.effective_start_date AND et.effective_end_date
1920 AND p_date_earned BETWEEN iv.effective_start_date AND iv.effective_end_date
1921 AND ((et.business_group_id IS NULL AND et.legislation_code = 'NO')
1922 OR (et.business_group_id = g_business_group_id AND et.legislation_code IS NULL));
1923
1924 */
1925 ----------------------------------------------------------------------------------------------------------
1926
1927 /* Cursor to retrieve Element Information (For elements with Pay Value as Input Value) */
1928
1929 /*
1930 CURSOR csr_element_info IS
1931 SELECT nvl(pettl.reporting_name,pettl.element_name) rep_name
1932 ,et.element_type_id element_type_id
1933 ,iv.input_value_id input_value_id
1934 ,iv.uom uom
1935 ,classification.classification_name ele_class
1936 FROM pay_element_types_f et
1937 , pay_element_types_f_tl pettl
1938 , pay_input_values_f iv
1939 , pay_element_classifications classification
1940 WHERE et.element_type_id = iv.element_type_id
1941 AND et.element_type_id = pettl.element_type_id
1942 AND pettl.language = USERENV('LANG')
1943 AND iv.name = 'Pay Value'
1944 AND classification.classification_id = et.classification_id
1945 AND classification.classification_name
1946 IN ('Earnings','Supplementary Earnings','Absence','Direct Payments','Pre-tax Deductions',
1947 'Involuntary Deductions','Voluntary Deductions','Statutory Deductions','Reductions',
1948 'Taxable Benefits','Benefits Not Taxed','Expenses Information','Taxable Expenses')
1949 AND p_date_earned BETWEEN et.effective_start_date AND et.effective_end_date
1950 AND p_date_earned BETWEEN iv.effective_start_date AND iv.effective_end_date
1951 AND ((et.business_group_id IS NULL AND et.legislation_code = 'NO')
1952 OR (et.business_group_id = g_business_group_id AND et.legislation_code IS NULL))
1953 ORDER BY DECODE (classification.classification_name,
1954 'Earnings',1,'Supplementary Earnings',2,'Absence',3,'Direct Payments',4,
1955 'Pre-tax Deductions',5,'Involuntary Deductions',6,'Voluntary Deductions',7,
1956 'Statutory Deductions',8,'Reductions',9,'Taxable Benefits',10,
1957 'Benefits Not Taxed',11,'Expenses Information',12,'Taxable Expenses',13) ;
1958
1959 */
1960
1961 CURSOR csr_element_info (p_ele_class_name pay_element_classifications.classification_name%type ) IS
1962 SELECT nvl(pettl.reporting_name,pettl.element_name) rep_name
1963 ,et.element_type_id element_type_id
1964 ,iv.input_value_id input_value_id
1965 ,iv.uom uom
1966 ,classification.classification_name ele_class
1967 FROM pay_element_types_f et
1968 , pay_element_types_f_tl pettl
1969 , pay_input_values_f iv
1970 , pay_element_classifications classification
1971 WHERE et.element_type_id = iv.element_type_id
1972 AND et.element_type_id = pettl.element_type_id
1973 AND pettl.language = USERENV('LANG')
1974 AND iv.name = 'Pay Value'
1975 AND classification.classification_id = et.classification_id
1976 AND classification.classification_name = p_ele_class_name
1977 AND p_date_earned BETWEEN et.effective_start_date AND et.effective_end_date
1978 AND p_date_earned BETWEEN iv.effective_start_date AND iv.effective_end_date
1979 AND ((et.business_group_id IS NULL AND et.legislation_code = 'NO')
1980 OR (et.business_group_id = g_business_group_id AND et.legislation_code IS NULL)) ;
1981
1982
1983 ----------------------------------------------------------------------------------------------------------
1984
1985 /* Cursor to get the first primary balance type id for an input value id */
1986
1987 CURSOR csr_prim_bal_type (p_iv_id NUMBER)IS
1988 SELECT balance_type_id
1989 FROM pay_balance_types
1990 WHERE input_value_id = p_iv_id
1991 AND ((business_group_id IS NULL AND legislation_code = 'NO')
1992 OR (business_group_id = g_business_group_id AND legislation_code IS NULL))
1993 AND rownum = 1 ;
1994
1995 ----------------------------------------------------------------------------------------------------
1996
1997 /* Cursor to get the defined balance id */
1998
1999 CURSOR csr_def_bal_id (p_prim_bal_type_id NUMBER)IS
2000 SELECT defined_balance_id
2001 FROM pay_defined_balances
2002 WHERE balance_type_id = p_prim_bal_type_id
2003 AND ((business_group_id IS NULL AND legislation_code = 'NO')
2004 OR (business_group_id = g_business_group_id AND legislation_code IS NULL))
2005 AND balance_dimension_id = ( select balance_dimension_id
2006 from pay_balance_dimensions where legislation_code = 'NO'
2007 and dimension_name = 'Assignment Calendar Year To Date' ) ;
2008
2009 ----------------------------------------------------------------------------------------------------
2010
2011 /* cursor to get the sub classifications for an element type */
2012
2013 /*
2014 CURSOR csr_sub_class (p_ele_type_id NUMBER) IS
2015 SELECT eleclass.classification_name
2016 ,decode (eleclass.classification_name,'Taxable Pay _ Absence',1,'Taxable Pay _ Earnings',1
2017 ,'Taxable Pay _ Supplemental Earnings',1,'Taxable Pay _ Taxable Benefits',1,0) table_base
2018 ,decode (eleclass.classification_name,'Additional Taxable Pay _ Absence',2,'Additional Taxable Pay _ Earnings',2
2019 ,'Additional Taxable Pay _ Supplemental Earnings',2,'Additional Taxable Pay _ Taxable Benefits',2,0) percent_base
2020 ,decode (eleclass.classification_name,'Holiday Pay',3,0) holiday_pay
2021
2022 FROM pay_sub_classification_rules_f subclass
2023 ,pay_element_classifications eleclass
2024
2025 WHERE subclass.element_type_id = p_ele_type_id
2026 AND p_date_earned BETWEEN subclass.effective_start_date AND subclass.effective_end_date
2027 AND ((subclass.business_group_id IS NULL AND subclass.legislation_code = 'NO')
2028 OR (subclass.business_group_id = g_business_group_id AND subclass.legislation_code IS NULL))
2029 AND ((eleclass.business_group_id IS NULL AND eleclass.legislation_code = 'NO')
2030 OR (eleclass.business_group_id = g_business_group_id AND eleclass.legislation_code IS NULL))
2031 AND eleclass.classification_id = subclass.classification_id ;
2032
2033 */
2034
2035 CURSOR csr_sub_class (p_ele_type_id NUMBER , p_ele_sub_class_name pay_element_classifications.classification_name%type ) IS
2036 SELECT 1
2037 FROM pay_sub_classification_rules_f subclass
2038 ,pay_element_classifications eleclass
2039 WHERE subclass.element_type_id = p_ele_type_id
2040 AND eleclass.classification_name = p_ele_sub_class_name
2041 AND p_date_earned BETWEEN subclass.effective_start_date AND subclass.effective_end_date
2042 AND ((subclass.business_group_id IS NULL AND subclass.legislation_code = 'NO')
2043 OR (subclass.business_group_id = g_business_group_id AND subclass.legislation_code IS NULL))
2044 AND ((eleclass.business_group_id IS NULL AND eleclass.legislation_code = 'NO')
2045 OR (eleclass.business_group_id = g_business_group_id AND eleclass.legislation_code IS NULL))
2046 AND eleclass.classification_id = subclass.classification_id ;
2047
2048
2049 -- cursor to check if an element has a holiday pay secondary classification
2050
2051 /*
2052 CURSOR csr_hol_sub_class (p_ele_type_id NUMBER ) IS
2053 SELECT 'YES'
2054 FROM pay_sub_classification_rules_f subclass
2055 ,pay_element_classifications eleclass
2056 WHERE subclass.element_type_id = p_ele_type_id
2057 AND eleclass.classification_id = subclass.classification_id
2058 AND eleclass.classification_name in ( 'Holiday Pay Base _ Absence',
2059 'Holiday Pay Base _ Earnings',
2060 'Holiday Pay Base _ Earnings Adjustment',
2061 'Holiday Pay Base _ Holiday Pay Earnings Adjustment',
2062 'Holiday Pay Base _ Supplementary Earnings')
2063 AND p_date_earned BETWEEN subclass.effective_start_date AND subclass.effective_end_date
2064 AND ((subclass.business_group_id IS NULL AND subclass.legislation_code = 'NO')
2065 OR (subclass.business_group_id = g_business_group_id AND subclass.legislation_code IS NULL))
2066 AND ((eleclass.business_group_id IS NULL AND eleclass.legislation_code = 'NO')
2067 OR (eleclass.business_group_id = g_business_group_id AND eleclass.legislation_code IS NULL)) ;
2068
2069 */
2070
2071 /* Change for element classification name from 'Holiday Pay Earnings Adjustment' to 'Holiday Pay Earnings Adjust' */
2072
2073 /*
2074 CURSOR csr_hol_sub_class (p_ele_type_id NUMBER ) IS
2075 SELECT 'YES'
2076 FROM pay_sub_classification_rules_f subclass
2077 ,pay_element_classifications eleclass
2078 WHERE subclass.element_type_id = p_ele_type_id
2079 AND eleclass.classification_id = subclass.classification_id
2080 AND eleclass.classification_name in ( 'Holiday Pay Base _ Absence',
2081 'Holiday Pay Base _ Earnings',
2082 'Holiday Pay Base _ Earnings Adjustment',
2083 'Holiday Pay Base _ Holiday Pay Earnings Adjust',
2084 'Holiday Pay Base _ Supplementary Earnings')
2085 AND p_date_earned BETWEEN subclass.effective_start_date AND subclass.effective_end_date
2086 AND ((subclass.business_group_id IS NULL AND subclass.legislation_code = 'NO')
2087 OR (subclass.business_group_id = g_business_group_id AND subclass.legislation_code IS NULL))
2088 AND ((eleclass.business_group_id IS NULL AND eleclass.legislation_code = 'NO')
2089 OR (eleclass.business_group_id = g_business_group_id AND eleclass.legislation_code IS NULL)) ;
2090 */
2091
2092
2093 /* Sub classification added for Impact of Absence on Holiday Pay */
2094 /*
2095 CURSOR csr_hol_sub_class (p_ele_type_id NUMBER ) IS
2096 SELECT 'YES'
2097 FROM pay_sub_classification_rules_f subclass
2098 ,pay_element_classifications eleclass
2099 WHERE subclass.element_type_id = p_ele_type_id
2100 AND eleclass.classification_id = subclass.classification_id
2101 AND eleclass.classification_name in ( 'Holiday Pay Base _ Absence',
2102 'Holiday Pay Base _ Earnings',
2103 'Holiday Pay Base _ Earnings Adjustment',
2104 'Holiday Pay Base _ Holiday Pay Earnings Adjust',
2105 'Holiday Pay Base _ Supplementary Earnings',
2106 'Holiday Pay Base During Absence _ Information')
2107 AND p_date_earned BETWEEN subclass.effective_start_date AND subclass.effective_end_date
2108 AND ((subclass.business_group_id IS NULL AND subclass.legislation_code = 'NO')
2109 OR (subclass.business_group_id = g_business_group_id AND subclass.legislation_code IS NULL))
2110 AND ((eleclass.business_group_id IS NULL AND eleclass.legislation_code = 'NO')
2111 OR (eleclass.business_group_id = g_business_group_id AND eleclass.legislation_code IS NULL)) ;
2112
2113 */
2114
2115 -- The following classifications have been obsoleted and will no longer be used.
2116 -- Holiday Pay Base _ Holiday Pay Earnings Adjust => Holiday Pay Base _ Holiday Pay Earnings Adjust Obsolete
2117
2118 CURSOR csr_hol_sub_class (p_ele_type_id NUMBER ) IS
2119 SELECT 'YES'
2120 FROM pay_sub_classification_rules_f subclass
2121 ,pay_element_classifications eleclass
2122 WHERE subclass.element_type_id = p_ele_type_id
2123 AND eleclass.classification_id = subclass.classification_id
2124 AND eleclass.classification_name in ( 'Holiday Pay Base _ Absence',
2125 'Holiday Pay Base _ Earnings',
2126 'Holiday Pay Base _ Earnings Adjustment',
2127 'Holiday Pay Base _ Supplementary Earnings',
2128 'Holiday Pay Base During Absence _ Information')
2129 AND p_date_earned BETWEEN subclass.effective_start_date AND subclass.effective_end_date
2130 AND ((subclass.business_group_id IS NULL AND subclass.legislation_code = 'NO')
2131 OR (subclass.business_group_id = g_business_group_id AND subclass.legislation_code IS NULL))
2132 AND ((eleclass.business_group_id IS NULL AND eleclass.legislation_code = 'NO')
2133 OR (eleclass.business_group_id = g_business_group_id AND eleclass.legislation_code IS NULL)) ;
2134
2135
2136 ----------------------------------------------------------------------------------------------------------------------
2137
2138 ---------------------
2139 l_action_info_id NUMBER;
2140 l_ovn NUMBER;
2141 l_flag NUMBER := 0;
2142 l_prim_bal_type_id NUMBER;
2143 l_prim_def_bal_id NUMBER ;
2144 l_table_basis NUMBER;
2145 l_percent_basis NUMBER;
2146 l_holiday_basis NUMBER;
2147 l_ele_class VARCHAR2(240);
2148 l_basis_text VARCHAR2(240);
2149 l_holiday_text VARCHAR2(240);
2150 l_index NUMBER;
2151
2152 TYPE ele_class_rec IS RECORD ( ele_class_name pay_element_classifications.classification_name%type
2153 , ele_context VARCHAR2(20) );
2154
2155 TYPE ele_class_tab IS TABLE OF ele_class_rec INDEX BY BINARY_INTEGER;
2156
2157 ele_class_table ele_class_tab;
2158
2159 ----------------------
2160 BEGIN
2161
2162 IF g_debug THEN
2163 hr_utility.set_location(' Entering Procedure ARCHIVE_ELEMENT_INFO',210);
2164 END IF;
2165
2166 ------------------------
2167 /* ALL ELEMENTS */
2168
2169
2170 /* initializing table for element classification name */
2171 ele_class_table(1).ele_class_name := 'Earnings'; ele_class_table(1).ele_context := 'S' ;
2172 ele_class_table(2).ele_class_name := 'Supplementary Earnings'; ele_class_table(2).ele_context := 'S' ;
2173 ele_class_table(3).ele_class_name := 'Absence'; ele_class_table(3).ele_context := 'S' ;
2174 ele_class_table(4).ele_class_name := 'Direct Payments'; ele_class_table(4).ele_context := 'OR' ;
2175 ele_class_table(5).ele_class_name := 'Pre-tax Deductions'; ele_class_table(5).ele_context := 'OD' ;
2176 ele_class_table(6).ele_class_name := 'Involuntary Deductions'; ele_class_table(6).ele_context := 'OD' ;
2177 ele_class_table(7).ele_class_name := 'Voluntary Deductions'; ele_class_table(7).ele_context := 'OD' ;
2178 ele_class_table(8).ele_class_name := 'Statutory Deductions'; ele_class_table(8).ele_context := 'WT' ;
2179 ele_class_table(9).ele_class_name := 'Reductions'; ele_class_table(9).ele_context := 'OTH' ;
2180 ele_class_table(10).ele_class_name := 'Taxable Benefits'; ele_class_table(10).ele_context := 'OTH' ;
2181 ele_class_table(11).ele_class_name := 'Benefits Not Taxed'; ele_class_table(11).ele_context := 'OTH' ;
2182 ele_class_table(12).ele_class_name := 'Expenses Information'; ele_class_table(12).ele_context := 'OTH' ;
2183 ele_class_table(13).ele_class_name := 'Taxable Expenses'; ele_class_table(13).ele_context := 'OTH' ;
2184 -- new added
2185 -- ele_class_table(14).ele_class_name := 'Earnings Adjustment'; ele_class_table(14).ele_context := 'OD' ;
2186 -- ele_class_table(15).ele_class_name := 'Holiday Pay Earnings Adjustment'; ele_class_table(15).ele_context := 'OD' ;
2187
2188 -- should be displayed in the 'Total Salary' with a negative sign
2189 ele_class_table(14).ele_class_name := 'Earnings Adjustment'; ele_class_table(14).ele_context := 'S' ;
2190 -- ele_class_table(15).ele_class_name := 'Holiday Pay Earnings Adjustment'; ele_class_table(15).ele_context := 'S' ;
2191
2192 /* Change for element classification name from 'Holiday Pay Earnings Adjustment' to 'Holiday Pay Earnings Adjust' */
2193
2194 -- The following classifications have been obsoleted and will no longer be used.
2195 -- Holiday Pay Earnings Adjust => Holiday Pay Earnings Adjust Obsolete
2196 -- Commenting the code below.
2197
2198 -- ele_class_table(15).ele_class_name := 'Holiday Pay Earnings Adjust'; ele_class_table(15).ele_context := 'S' ;
2199
2200 /* Element classification 'Information' added for Impact of Absence on Holiday Pay */
2201 -- ele_class_table(16).ele_class_name := 'Information'; ele_class_table(16).ele_context := 'OTH' ;
2202
2203 -- Due to the obsoletion of the above classification , moving the index from 16 to 15
2204 ele_class_table(15).ele_class_name := 'Information'; ele_class_table(15).ele_context := 'OTH' ;
2205
2206 -- Added for advance pay --
2207 ele_class_table(16).ele_class_name := 'Advance Earnings'; ele_class_table(16).ele_context := 'S' ;
2208 --
2209
2210 FOR l_index IN ele_class_table.first.. ele_class_table.last LOOP
2211
2212 FOR rec_earnings IN csr_element_info(ele_class_table(l_index).ele_class_name) LOOP
2213
2214 BEGIN
2215
2216 l_prim_bal_type_id := NULL ;
2217 l_prim_def_bal_id := NULL ;
2218 l_ele_class := NULL ;
2219 l_table_basis := NULL ;
2220 l_percent_basis := NULL ;
2221 l_holiday_basis := NULL ;
2222 l_basis_text := NULL ;
2223 l_holiday_text := NULL ;
2224
2225 -- check if the element is basis for holiday pay
2226
2227 /* moving on top to check for information elements whiich are basis for holiday pay :
2228 element class : Information
2229 sec class : Holiday Pay Base During Absence _ Information */
2230
2231 OPEN csr_hol_sub_class (rec_earnings.element_type_id) ;
2232 FETCH csr_hol_sub_class INTO l_holiday_text ;
2233 CLOSE csr_hol_sub_class ;
2234
2235 IF ((ele_class_table(l_index).ele_class_name <> 'Information')
2236 OR
2237 ((ele_class_table(l_index).ele_class_name = 'Information') AND
2238 (l_holiday_text = 'YES'))) THEN
2239
2240 -- fnd_file.put_line(fnd_file.log,'ele_class_table(l_index).ele_class_name ' || ele_class_table(l_index).ele_class_name );
2241 -- fnd_file.put_line(fnd_file.log,'rec_earnings.element_type_id ' || rec_earnings.element_type_id );
2242 -- fnd_file.put_line(fnd_file.log,'l_holiday_text = ' || l_holiday_text );
2243 -- fnd_file.put_line(fnd_file.log,'--------------------------------------------- ');
2244
2245
2246
2247 /* get the primary balance type id from the input value id */
2248 OPEN csr_prim_bal_type (rec_earnings.input_value_id);
2249 FETCH csr_prim_bal_type INTO l_prim_bal_type_id ;
2250 CLOSE csr_prim_bal_type ;
2251
2252 /* get the defined balance id from the balance type id */
2253 OPEN csr_def_bal_id (l_prim_bal_type_id ) ;
2254 FETCH csr_def_bal_id INTO l_prim_def_bal_id ;
2255 CLOSE csr_def_bal_id ;
2256
2257 /* get the table/percetage basis info */
2258
2259 l_basis_text := NULL ;
2260
2261 IF ( ele_class_table(l_index).ele_class_name = 'Pre-tax Deductions' )
2262
2263 THEN l_basis_text := 'TABLE';
2264
2265 ELSIF ( ele_class_table(l_index).ele_class_name = 'Taxable Expenses' )
2266
2267 THEN l_basis_text := 'PERCENTAGE';
2268
2269 ELSIF ( ele_class_table(l_index).ele_class_name = 'Absence' )
2270
2271 THEN
2272 OPEN csr_sub_class (rec_earnings.element_type_id , 'Taxable Pay _ Absence') ;
2273 FETCH csr_sub_class INTO l_table_basis ;
2274 IF (csr_sub_class%FOUND)
2275 THEN l_basis_text := 'TABLE';
2276 END IF;
2277 CLOSE csr_sub_class ;
2278
2279
2280 OPEN csr_sub_class (rec_earnings.element_type_id , 'Additional Taxable Pay _ Absence') ;
2281 FETCH csr_sub_class INTO l_percent_basis ;
2282 IF (csr_sub_class%FOUND)
2283 THEN l_basis_text := 'PERCENTAGE';
2284 END IF;
2285 CLOSE csr_sub_class ;
2286
2287
2288 ELSIF ( ele_class_table(l_index).ele_class_name = 'Earnings' )
2289
2290 THEN
2291 OPEN csr_sub_class (rec_earnings.element_type_id , 'Taxable Pay _ Earnings') ;
2292 FETCH csr_sub_class INTO l_table_basis ;
2293 IF (csr_sub_class%FOUND)
2294 THEN l_basis_text := 'TABLE';
2295 END IF;
2296 CLOSE csr_sub_class ;
2297
2298
2299 OPEN csr_sub_class (rec_earnings.element_type_id , 'Additional Taxable Pay _ Earnings') ;
2300 FETCH csr_sub_class INTO l_percent_basis ;
2301 IF (csr_sub_class%FOUND)
2302 THEN l_basis_text := 'PERCENTAGE';
2303 END IF;
2304 CLOSE csr_sub_class ;
2305
2306 ELSIF ( ele_class_table(l_index).ele_class_name = 'Supplementary Earnings' )
2307
2308 THEN
2309 OPEN csr_sub_class (rec_earnings.element_type_id , 'Taxable Pay _ Supplemental Earnings') ;
2310 FETCH csr_sub_class INTO l_table_basis ;
2311 IF (csr_sub_class%FOUND)
2312 THEN l_basis_text := 'TABLE';
2313 END IF;
2314 CLOSE csr_sub_class ;
2315
2316
2317 OPEN csr_sub_class (rec_earnings.element_type_id , 'Additional Taxable Pay _ Supplemental Earnings') ;
2318 FETCH csr_sub_class INTO l_percent_basis ;
2319 IF (csr_sub_class%FOUND)
2320 THEN l_basis_text := 'PERCENTAGE';
2321 END IF;
2322 CLOSE csr_sub_class ;
2323
2324 ELSIF ( ele_class_table(l_index).ele_class_name = 'Taxable Benefits' )
2325
2326 THEN
2327 OPEN csr_sub_class (rec_earnings.element_type_id , 'Taxable Pay _ Taxable Benefits') ;
2328 FETCH csr_sub_class INTO l_table_basis ;
2329 IF (csr_sub_class%FOUND)
2330 THEN l_basis_text := 'TABLE';
2331 END IF;
2332 CLOSE csr_sub_class ;
2333
2334
2335 OPEN csr_sub_class (rec_earnings.element_type_id , 'Additional Taxable Pay _ Taxable Benefits') ;
2336 FETCH csr_sub_class INTO l_percent_basis ;
2337 IF (csr_sub_class%FOUND)
2338 THEN l_basis_text := 'PERCENTAGE';
2339 END IF;
2340 CLOSE csr_sub_class ;
2341
2342 ELSIF ( ele_class_table(l_index).ele_class_name = 'Earnings Adjustment' )
2343
2344 THEN
2345 OPEN csr_sub_class (rec_earnings.element_type_id , 'Taxable Pay _ Earnings Adjustment') ;
2346 FETCH csr_sub_class INTO l_table_basis ;
2347 IF (csr_sub_class%FOUND)
2348 THEN l_basis_text := 'TABLE';
2349 END IF;
2350 CLOSE csr_sub_class ;
2351
2352
2353 OPEN csr_sub_class (rec_earnings.element_type_id , 'Additional Taxable Pay _ Earnings Adjustment') ;
2354 FETCH csr_sub_class INTO l_percent_basis ;
2355 IF (csr_sub_class%FOUND)
2356 THEN l_basis_text := 'PERCENTAGE';
2357 END IF;
2358 CLOSE csr_sub_class ;
2359
2360 /* Change for element classification name from 'Holiday Pay Earnings Adjustment' to 'Holiday Pay Earnings Adjust' */
2361
2362 -- The following classifications have been obsoleted and will no longer be used.
2363 -- Holiday Pay Earnings Adjust => Holiday Pay Earnings Adjust Obsolete
2364 -- Taxable Pay _ Holiday Pay Earnings Adjust => Taxable Pay _ Holiday Pay Earnings Adjust Obsolete
2365 -- Additional Taxable Pay _ Holiday Pay Earnings Adjust => Additional Taxable Pay _ Holiday Pay Earnings Adjust Obsolete
2366 -- Commenting the code below.
2367
2368 /*
2369
2370 -- ELSIF ( ele_class_table(l_index).ele_class_name = 'Holiday Pay Earnings Adjustment' )
2371 ELSIF ( ele_class_table(l_index).ele_class_name = 'Holiday Pay Earnings Adjust' )
2372
2373 THEN
2374 -- OPEN csr_sub_class (rec_earnings.element_type_id , 'Taxable Pay _ Holiday Pay Earnings Adjustment') ;
2375 OPEN csr_sub_class (rec_earnings.element_type_id , 'Taxable Pay _ Holiday Pay Earnings Adjust') ;
2376 FETCH csr_sub_class INTO l_table_basis ;
2377 IF (csr_sub_class%FOUND)
2378 THEN l_basis_text := 'TABLE';
2379 END IF;
2380 CLOSE csr_sub_class ;
2381
2382
2383 -- OPEN csr_sub_class (rec_earnings.element_type_id , 'Additional Taxable Pay _ Holiday Pay Earnings Adjustment') ;
2384 OPEN csr_sub_class (rec_earnings.element_type_id , 'Additional Taxable Pay _ Holiday Pay Earnings Adjust') ;
2385 FETCH csr_sub_class INTO l_percent_basis ;
2386 IF (csr_sub_class%FOUND)
2387 THEN l_basis_text := 'PERCENTAGE';
2388 END IF;
2389 CLOSE csr_sub_class ;
2390
2391 */
2392
2393 END IF;
2394
2395 /*
2396 OPEN csr_sub_class (rec_earnings.element_type_id) ;
2397 FETCH csr_sub_class INTO l_ele_class ,l_table_basis ,l_percent_basis ,l_holiday_basis ;
2398 CLOSE csr_sub_class ;
2399
2400
2401 l_basis_text := NULL ;
2402
2403 IF (l_table_basis = 1) THEN l_basis_text := 'TABLE';
2404 ELSIF (l_percent_basis = 2) THEN l_basis_text := 'PERCENTAGE';
2405 END IF;
2406
2407 l_holiday_text := NULL ;
2408
2409 IF (l_holiday_basis = 3) THEN l_holiday_text := 'Y';
2410 END IF ;
2411
2412 */
2413
2414 /* still to incorporate solution for Basis for Holiday Pay */
2415
2416 -- l_holiday_text := NULL ;
2417
2418 -- check if the element is basis for holiday pay
2419
2420 /* moving on top to check for information elements whiich are basis for holiday pay :
2421 element class : Information
2422 sec class : Holiday Pay Base During Absence _ Information */
2423 /*
2424 OPEN csr_hol_sub_class (rec_earnings.element_type_id) ;
2425 FETCH csr_hol_sub_class INTO l_holiday_text ;
2426 CLOSE csr_hol_sub_class ;
2427 */
2428
2429 IF (l_basis_text IS NOT NULL) THEN
2430 l_basis_text := hr_general.decode_lookup('NO_REPORT_LABELS',l_basis_text);
2431 END IF;
2432
2433 IF (l_holiday_text IS NOT NULL) THEN
2434 l_holiday_text := hr_general.decode_lookup('NO_REPORT_LABELS',l_holiday_text);
2435 END IF;
2436
2437 -- fnd_file.put_line (fnd_file.LOG,'3: ' || rec_earnings.rep_name ||','|| l_basis_text ||','|| l_holiday_text );
2438
2439 BEGIN
2440 -- check if the Element definition has already been archived
2441 SELECT 1 INTO l_flag
2442 FROM pay_action_information
2443 WHERE action_context_id = p_payroll_action_id
2444 AND action_information_category = 'NO ELEMENT DEFINITION'
2445 AND action_context_type = 'PA'
2446 AND action_information2 = rec_earnings.element_type_id
2447 AND action_information3 = rec_earnings.input_value_id
2448 AND action_information5 = ele_class_table(l_index).ele_context ;
2449
2450 EXCEPTION WHEN NO_DATA_FOUND THEN
2451 -- archive the element definitio as it has not been archived
2452 pay_action_information_api.create_action_information (
2453 p_action_information_id => l_action_info_id
2454 ,p_action_context_id => p_payroll_action_id
2455 ,p_action_context_type => 'PA'
2456 ,p_object_version_number => l_ovn
2457 ,p_effective_date => p_effective_date
2458 ,p_source_id => NULL
2459 ,p_source_text => NULL
2460 ,p_action_information_category => 'NO ELEMENT DEFINITION'
2461 ,p_action_information1 => p_pre_payact_id
2462 ,p_action_information2 => rec_earnings.element_type_id
2463 ,p_action_information3 => rec_earnings.input_value_id
2464 ,p_action_information4 => rec_earnings.rep_name
2465 ,p_action_information5 => ele_class_table(l_index).ele_context
2466 ,p_action_information6 => rec_earnings.uom
2467 ,p_action_information7 => ele_class_table(l_index).ele_context
2468 ,p_action_information8 => l_prim_def_bal_id
2469 ,p_action_information9 => 'PBAL'
2470 ,p_action_information10 => l_holiday_text
2471 ,p_action_information11 => l_basis_text
2472 ,p_action_information12 => rec_earnings.ele_class );
2473 WHEN OTHERS THEN
2474 NULL;
2475 END;
2476
2477 END IF; -- end if clssification check for 'Information'
2478 END;
2479 END LOOP;
2480
2481 END LOOP;
2482
2483 IF g_debug THEN
2484 hr_utility.set_location(' Leaving Procedure ARCHIVE_ELEMENT_INFO',220);
2485 END IF;
2486
2487 EXCEPTION WHEN OTHERS THEN
2488 g_err_num := SQLCODE;
2489 /*fnd_file.put_line(fnd_file.log,'ORA_ERR: ' || g_err_num || 'In ARCHIVE_ELEMENT_INFO');*/
2490
2491 IF g_debug THEN
2492 hr_utility.set_location('ORA_ERR: ' || g_err_num || 'ARCHIVE_ELEMENT_INFO',230);
2493 END IF;
2494
2495 END ARCHIVE_ELEMENT_INFO;
2496
2497
2498 ------------------------------------ FUNCTION GET_DEFINED_BALANCE_ID --------------------------------------------------------------
2499
2500 /* GET DEFINED BALANCE ID */
2501
2502 FUNCTION GET_DEFINED_BALANCE_ID(p_user_name IN VARCHAR2) RETURN NUMBER
2503 IS
2504
2505 /* Cursor to retrieve Defined Balance Id */
2506
2507 CURSOR csr_def_bal_id(p_user_name VARCHAR2) IS
2508 SELECT u.creator_id
2509 FROM ff_user_entities u,
2510 ff_database_items d
2511 WHERE d.user_name = p_user_name
2512 AND u.user_entity_id = d.user_entity_id
2513 AND (u.legislation_code = 'NO' )
2514 AND (u.business_group_id IS NULL )
2515 AND u.creator_type = 'B';
2516
2517 l_defined_balance_id ff_user_entities.user_entity_id%TYPE;
2518
2519 BEGIN
2520
2521 IF g_debug THEN
2522 hr_utility.set_location(' Entering Function GET_DEFINED_BALANCE_ID',240);
2523 END IF;
2524
2525 OPEN csr_def_bal_id(p_user_name);
2526 FETCH csr_def_bal_id INTO l_defined_balance_id;
2527 CLOSE csr_def_bal_id;
2528 RETURN l_defined_balance_id;
2529
2530 IF g_debug THEN
2531 hr_utility.set_location(' Leaving Function GET_DEFINED_BALANCE_ID',250);
2532 END IF;
2533
2534 END GET_DEFINED_BALANCE_ID;
2535
2536 --------------------------------------------------------------------------------------------------
2537
2538
2539 /* PAYMENT INFORMATION REGION */
2540 PROCEDURE ARCHIVE_PAYMENT_INFO(p_archive_assact_id IN NUMBER,
2541 p_prepay_assact_id IN NUMBER,
2542 p_assignment_id IN NUMBER,
2543 p_date_earned IN DATE,
2544 p_effective_date IN DATE)
2545 IS
2546 -------------
2547 /* Cursor to fetch ppm and opm ids to check which payment method to archive */
2548 CURSOR csr_chk(p_prepay_assact_id NUMBER) IS
2549 SELECT personal_payment_method_id ppm_id,
2550 org_payment_method_id opm_id
2551 FROM pay_pre_payments
2552 WHERE assignment_action_id = p_prepay_assact_id;
2553
2554 ------------
2555 /* Cursor to check if bank details are attached with ppm */
2556 CURSOR csr_chk_bank(p_ppm_id NUMBER) IS
2557 SELECT ppm.external_account_id
2558 FROM pay_personal_payment_methods_f ppm
2559 WHERE ppm.personal_payment_method_id = p_ppm_id
2560 AND p_date_earned BETWEEN ppm.effective_start_date AND ppm.effective_end_date;
2561
2562 -------------
2563 /* Cursor to retrieve Organization Payment Method Information */
2564 CURSOR csr_get_org_pay(p_prepay_assact_id NUMBER, opm_id NUMBER) IS
2565 SELECT pop.org_payment_method_id opm_id,
2566 pop.org_payment_method_name opm_name,
2567 ppttl.payment_type_name pay_type,
2568 ppp.value value
2569 FROM pay_org_payment_methods_f pop,
2570 pay_assignment_actions paa,
2571 pay_payment_types ppt,
2572 pay_payment_types_tl ppttl,
2573 pay_pre_payments ppp
2574 WHERE paa.assignment_action_id = p_prepay_assact_id
2575 AND ppt.payment_type_id = pop.payment_type_id
2576 AND ppt.payment_type_id = ppttl.payment_type_id
2577 AND ppttl.language = userenv('LANG')
2578 AND ppp.org_payment_method_id = pop.org_payment_method_id
2579 AND pop.org_payment_method_id = opm_id
2580 AND ppp.assignment_action_id = paa.assignment_action_id
2581 AND p_date_earned BETWEEN pop.effective_start_date AND pop.effective_end_date;
2582
2583 -------------
2584 /* Cursor to retrieve Personal Payment Method Info*/
2585 CURSOR csr_get_pers_pay(p_prepay_assact_id NUMBER, ppm_id NUMBER) IS
2586 SELECT pea.segment1 bank_name,
2587 pea.segment2 branch,
2588 pea.segment3 acct_name,
2589 pea.segment4 acc_type,
2590 pea.segment5 acc_curr,
2591 pea.segment6 acct_num,
2592 ppm.org_payment_method_id opm_id,
2593 pop.external_account_id,
2594 pop.org_payment_method_name opm_name,
2595 ppm.personal_payment_method_id ppm_id,
2596 ppttl.payment_type_name pay_type,
2597 ppp.value value
2598 FROM pay_external_accounts pea,
2599 pay_org_payment_methods_f pop,
2600 pay_personal_payment_methods_f ppm,
2601 pay_assignment_actions paa,
2602 pay_payment_types ppt,
2603 pay_payment_types_tl ppttl,
2604 pay_pre_payments ppp
2605 WHERE
2606 -- pea.id_flex_num=20 AND
2607 pea.external_account_id = NVL(ppm.external_account_id,pop.external_account_id)
2608 AND paa.assignment_action_id = p_prepay_assact_id
2609 AND paa.assignment_id = ppm.assignment_id
2610 AND ppm.org_payment_method_id = pop.org_payment_method_id
2611 AND ppm.personal_payment_method_id = ppm_id
2612 AND ppt.payment_type_id = pop.payment_type_id
2613 AND ppt.payment_type_id = ppttl.payment_type_id
2614 AND ppttl.language = userenv('LANG')
2615 AND ppp.assignment_action_id = paa.assignment_action_id
2616 AND ppp.personal_payment_method_id = ppm.personal_payment_method_id
2617 AND p_date_earned BETWEEN pop.effective_start_date AND pop.effective_end_date
2618 AND p_date_earned BETWEEN ppm.effective_start_date AND ppm.effective_end_date;
2619 -------------
2620 l_bank_reg_num VARCHAR2(50);
2621 l_action_info_id NUMBER;
2622 l_ovn NUMBER;
2623 l_org NUMBER;
2624 l_pers VARCHAR2(40) := NULL;
2625 l_ext_acct NUMBER;
2626 rec_chk csr_chk%ROWTYPE;
2627 l_pay_value VARCHAR2(50) := NULL;
2628 l_bank_name VARCHAR2(240);
2629 l_acc_name VARCHAR2(240);
2630 l_acc_type VARCHAR2(240);
2631 l_acc_curr VARCHAR2(240);
2632
2633 ------------
2634
2635 BEGIN
2636
2637 IF g_debug THEN
2638 hr_utility.set_location(' Entering Procedure ARCHIVE_PAYMENT_INFO',260);
2639 END IF;
2640
2641 /*fnd_file.put_line(fnd_file.log,'In ARCHIVE_PAYMENT_INFO 1');*/
2642
2643 OPEN csr_chk(p_prepay_assact_id);
2644 LOOP
2645 FETCH csr_chk INTO rec_chk;
2646 EXIT WHEN csr_chk%NOTFOUND;
2647
2648 IF rec_chk.ppm_id IS NOT NULL THEN
2649 FOR rec_pers_pay IN csr_get_pers_pay(p_prepay_assact_id,rec_chk.ppm_id) LOOP
2650
2651 OPEN csr_chk_bank(rec_chk.ppm_id);
2652 FETCH csr_chk_bank INTO l_ext_acct;
2653 CLOSE csr_chk_bank;
2654
2655 l_pay_value := to_char (rec_pers_pay.value,g_format_mask);
2656
2657 IF (l_ext_acct IS NOT NULL) THEN
2658
2659 /*fnd_file.put_line(fnd_file.log,'In ARCHIVE_PAYMENT_INFO 2');*/
2660
2661 -- l_bank_reg_num := rec_pers_pay.bank_reg_num;
2662 -- l_bank_reg_num := rec_pers_pay.bank_reg_num || ' ' || hr_general.decode_lookup('HR_NO_BANK_REGISTRATION',rec_pers_pay.bank_reg_num);
2663
2664 l_bank_name := hr_general.decode_lookup('HR_NO_BANK',rec_pers_pay.bank_name);
2665 l_acc_name := hr_general.decode_lookup('HR_NO_ACCOUNT_NAME',rec_pers_pay.acct_name);
2666 l_acc_type := hr_general.decode_lookup('HR_NO_ACCOUNT_TYPE',rec_pers_pay.acc_type);
2667 l_acc_curr := rec_pers_pay.acc_curr ;
2668
2669
2670 pay_action_information_api.create_action_information (
2671 p_action_information_id => l_action_info_id
2672 ,p_action_context_id => p_archive_assact_id
2673 ,p_action_context_type => 'AAP'
2674 ,p_object_version_number => l_ovn
2675 ,p_effective_date => p_effective_date
2676 ,p_source_id => NULL
2677 ,p_source_text => NULL
2678 ,p_action_information_category => 'EMPLOYEE NET PAY DISTRIBUTION'
2679 ,p_action_information1 => rec_pers_pay.opm_id -- NULL
2680 ,p_action_information2 => rec_pers_pay.ppm_id
2681 ,p_action_information5 => l_bank_name
2682 ,p_action_information6 => rec_pers_pay.branch
2683 ,p_action_information7 => l_acc_name
2684 ,p_action_information8 => l_acc_type
2685 ,p_action_information9 => l_acc_curr
2686 ,p_action_information10 => rec_pers_pay.acct_num
2687 ,p_action_information11 => NULL
2688 ,p_action_information12 => NULL
2689 ,p_action_information13 => NULL
2690 ,p_action_information14 => NULL
2691 ,p_action_information15 => NULL
2692 ,p_action_information16 => fnd_number.number_to_canonical(rec_pers_pay.value) --l_pay_value
2693 ,p_action_information17 => NULL
2694 ,p_action_information18 => rec_pers_pay.opm_name -- rec_pers_pay.pay_type
2695 ,p_assignment_id => p_assignment_id);
2696 ELSE
2697
2698 /*fnd_file.put_line(fnd_file.log,'In ARCHIVE_PAYMENT_INFO 3');*/
2699
2700 pay_action_information_api.create_action_information (
2701 p_action_information_id => l_action_info_id
2702 ,p_action_context_id => p_archive_assact_id
2703 ,p_action_context_type => 'AAP'
2704 ,p_object_version_number => l_ovn
2705 ,p_effective_date => p_effective_date
2706 ,p_source_id => NULL
2707 ,p_source_text => NULL
2708 ,p_action_information_category => 'EMPLOYEE NET PAY DISTRIBUTION'
2709 ,p_action_information1 => rec_pers_pay.opm_id
2710 ,p_action_information2 => rec_pers_pay.ppm_id
2711 ,p_action_information5 => NULL
2712 ,p_action_information6 => NULL
2713 ,p_action_information7 => NULL
2714 ,p_action_information8 => NULL
2715 ,p_action_information9 => NULL
2716 ,p_action_information10 => NULL
2717 ,p_action_information11 => NULL
2718 ,p_action_information12 => NULL
2719 ,p_action_information13 => NULL
2720 ,p_action_information14 => NULL
2721 ,p_action_information15 => NULL
2722 ,p_action_information16 => fnd_number.number_to_canonical(rec_pers_pay.value) --l_pay_value
2723 ,p_action_information17 => NULL
2724 ,p_action_information18 => rec_pers_pay.opm_name -- rec_pers_pay.pay_type
2725 ,p_assignment_id => p_assignment_id);
2726 END IF;
2727 END LOOP;
2728 /*fnd_file.put_line(fnd_file.log,'In ARCHIVE_PAYMENT_INFO 4');*/
2729
2730 END IF;
2731
2732 IF (rec_chk.opm_id IS NOT NULL AND rec_chk.ppm_id IS NULL) THEN
2733
2734 /*fnd_file.put_line(fnd_file.log,'In ARCHIVE_PAYMENT_INFO 5');*/
2735
2736 FOR rec_org_pay IN csr_get_org_pay(p_prepay_assact_id,rec_chk.opm_id) LOOP
2737
2738 l_pay_value := to_char (rec_org_pay.value,g_format_mask);
2739
2740 pay_action_information_api.create_action_information (
2741 p_action_information_id => l_action_info_id
2742 ,p_action_context_id => p_archive_assact_id
2743 ,p_action_context_type => 'AAP'
2744 ,p_object_version_number => l_ovn
2745 ,p_effective_date => p_effective_date
2746 ,p_source_id => NULL
2747 ,p_source_text => NULL
2748 ,p_action_information_category => 'EMPLOYEE NET PAY DISTRIBUTION'
2749 ,p_action_information1 => rec_org_pay.opm_id
2750 ,p_action_information2 => NULL
2751 ,p_action_information5 => NULL
2752 ,p_action_information6 => NULL
2753 ,p_action_information7 => NULL
2754 ,p_action_information8 => NULL
2755 ,p_action_information9 => NULL
2756 ,p_action_information10 => NULL
2757 ,p_action_information11 => NULL
2758 ,p_action_information12 => NULL
2759 ,p_action_information13 => NULL
2760 ,p_action_information14 => NULL
2761 ,p_action_information15 => NULL
2762 ,p_action_information16 => fnd_number.number_to_canonical(rec_org_pay.value) --l_pay_value
2763 ,p_action_information17 => NULL
2764 ,p_action_information18 => rec_org_pay.opm_name -- rec_org_pay.pay_type
2765 ,p_assignment_id => p_assignment_id);
2766 END LOOP;
2767
2768 END IF;
2769
2770 END LOOP;
2771 CLOSE csr_chk;
2772
2773 IF g_debug THEN
2774 hr_utility.set_location(' Leaving Procedure ARCHIVE_PAYMENT_INFO',270);
2775 END IF;
2776
2777 EXCEPTION WHEN OTHERS THEN
2778 g_err_num := SQLCODE;
2779
2780 /*fnd_file.put_line(fnd_file.log,'ORA_ERR: ' || g_err_num || 'In ARCHIVE_PAYMENT_INFO');*/
2781
2782 IF g_debug THEN
2783 hr_utility.set_location('ORA_ERR: ' || g_err_num || 'In ARCHIVE_PAYMENT_INFO',280);
2784 END IF;
2785
2786 END ARCHIVE_PAYMENT_INFO;
2787 ----------------------------------------- PROCEDURE ARCHIVE_ACCRUAL_PLAN ---------------------------------------------------------
2788
2789 /* ACCRUALS REGION */
2790
2791 /* PROCEDURE ARCHIVE_ACCRUAL_PLAN ( p_assignment_id IN NUMBER
2792 ,p_date_earned IN DATE
2793 ,p_effective_date IN DATE
2794 ,p_archive_assact_id IN NUMBER
2795 ,p_run_assignment_action_id IN NUMBER
2796 ,p_period_end_date IN DATE
2797 ,p_period_start_date IN DATE
2798 )
2799 IS
2800 --
2801 -- Cursor to get the Leave Balance Details .
2802 CURSOR csr_leave_balance
2803 IS
2804 --
2805 SELECT pap.accrual_plan_name
2806 ,hr_general_utilities.get_lookup_meaning('US_PTO_ACCRUAL',pap.accrual_category)
2807 ,pap.accrual_units_of_measure
2808 ,ppa.payroll_id
2809 ,pap.business_group_id
2810 ,pap.accrual_plan_id
2811 FROM pay_accrual_plans pap
2812 ,pay_element_types_f pet
2813 ,pay_element_links_f pel
2814 ,pay_element_entries_f pee
2815 ,pay_assignment_actions paa
2816 ,pay_payroll_actions ppa
2817 WHERE pet.element_type_id = pap.accrual_plan_element_type_id
2818 AND pel.element_type_id = pet.element_type_id
2819 AND pee.element_link_id = pel.element_link_id
2820 AND paa.assignment_id = pee.assignment_id
2821 AND ppa.payroll_action_id = paa.payroll_action_id
2822 AND ppa.action_type IN ('R','Q')
2823 AND ppa.action_status = 'C'
2824 AND ppa.date_earned BETWEEN pet.effective_start_date
2825 AND pet.effective_end_date
2826 AND ppa.date_earned BETWEEN pel.effective_start_date
2827 AND pel.effective_end_date
2828 AND ppa.date_earned BETWEEN pee.effective_start_date
2829 AND pee.effective_end_date
2830 AND paa.assignment_id = p_assignment_id
2831 AND paa.assignment_action_id = p_run_assignment_action_id;
2832 --
2833 l_action_info_id NUMBER;
2834 l_accrual_plan_id pay_accrual_plans.accrual_plan_id%type;
2835 l_accrual_plan_name pay_accrual_plans.accrual_plan_name%type;
2836 l_accrual_category pay_accrual_plans.accrual_category%type;
2837 l_accrual_uom pay_accrual_plans.accrual_units_of_measure%type;
2838 l_payroll_id pay_all_payrolls_f.payroll_id%type;
2839 l_business_group_id NUMBER;
2840 l_effective_date DATE;
2841 l_annual_leave_balance NUMBER;
2842 l_ovn NUMBER;
2843 l_leave_taken NUMBER;
2844 l_start_date DATE;
2845 l_end_date DATE;
2846 l_accrual_end_date DATE;
2847 l_accrual NUMBER;
2848 l_total_leave_taken NUMBER;
2849 l_procedure VARCHAR2(100) := g_package || '.archive_accrual_details';
2850 --
2851 BEGIN
2852 --
2853 IF g_debug THEN
2854 hr_utility.set_location(' Entering Procedure ARCHIVE_ACCRUAL_PLAN',290);
2855 END IF;
2856 OPEN csr_leave_balance;
2857 FETCH csr_leave_balance INTO
2858 l_accrual_plan_name
2859 ,l_accrual_category
2860 ,l_accrual_uom
2861 ,l_payroll_id
2862 ,l_business_group_id
2863 ,l_accrual_plan_id;
2864 IF csr_leave_balance%FOUND THEN
2865 --
2866 -- Call to get annual leave balance
2867 per_accrual_calc_functions.get_net_accrual
2868 (
2869 p_assignment_id => p_assignment_id -- number in
2870 ,p_plan_id => l_accrual_plan_id -- number in
2871 ,p_payroll_id => l_payroll_id -- number in
2872 ,p_business_group_id => l_business_group_id -- number in
2873 ,p_calculation_date => p_date_earned -- date in
2874 ,p_start_date => l_start_date -- date out
2875 ,p_end_date => l_end_date -- date out
2876 ,p_accrual_end_date => l_accrual_end_date -- date out
2877 ,p_accrual => l_accrual -- number out
2878 ,p_net_entitlement => l_annual_leave_balance -- number out
2879 );
2880 IF l_annual_leave_balance IS NULL THEN
2881 --
2882 l_annual_leave_balance := 0;
2883 --
2884 END IF;
2885 l_leave_taken := per_accrual_calc_functions.get_absence
2886 (
2887 p_assignment_id
2888 ,l_accrual_plan_id
2889 ,p_period_end_date
2890 ,p_period_start_date
2891 );
2892 l_ovn :=1;
2893 IF l_accrual_plan_name IS NOT NULL THEN
2894 --
2895 pay_action_information_api.create_action_information (
2896 p_action_information_id => l_action_info_id
2897 ,p_action_context_id => p_archive_assact_id
2898 ,p_action_context_type => 'AAP'
2899 ,p_object_version_number => l_ovn
2900 ,p_effective_date => p_effective_date
2901 ,p_source_id => NULL
2902 ,p_source_text => NULL
2903 ,p_action_information_category => 'EMPLOYEE ACCRUALS'
2904 ,p_action_information4 => l_accrual_plan_name
2905 ,p_action_information5 => fnd_number.number_to_canonical(l_leave_taken)
2906 ,p_action_information6 => fnd_number.number_to_canonical(l_annual_leave_balance)
2907 ,p_assignment_id => p_assignment_id);
2908 --
2909 END IF;
2910 --
2911 --
2912 END IF;
2913 --
2914 CLOSE csr_leave_balance;
2915 IF g_debug THEN
2916 hr_utility.set_location(' Leaving Procedure ARCHIVE_ACCRUAL_PLAN',300);
2917 END IF;
2918 --
2919 EXCEPTION
2920 WHEN OTHERS THEN
2921 IF csr_leave_balance%ISOPEN THEN
2922 --
2923 CLOSE csr_leave_balance;
2924 --
2925 END IF;
2926 --
2927 g_err_num := SQLCODE;
2928 --fnd_file.put_line(fnd_file.log,'ORA_ERR: ' || g_err_num || 'In ARCHIVE_ACCRUAL_PLAN');
2929 IF g_debug THEN
2930 hr_utility.set_location('ORA_ERR: ' || g_err_num || ' In ARCHIVE_ACCRUAL_PLAN',310);
2931 END IF;
2932 RAISE;
2933 END ARCHIVE_ACCRUAL_PLAN;*/
2934
2935 ----------------------------------- PROCEDURE ARCHIVE_ADD_ELEMENT ---------------------------------------------------------------
2936
2937 /* ADDITIONAL ELEMENTS REGION */
2938
2939 PROCEDURE ARCHIVE_ADD_ELEMENT(p_archive_assact_id IN NUMBER,
2940 p_assignment_action_id IN NUMBER,
2941 p_assignment_id IN NUMBER,
2942 p_payroll_action_id IN NUMBER,
2943 p_date_earned IN DATE,
2944 p_effective_date IN DATE,
2945 p_pre_payact_id IN NUMBER,
2946 p_archive_flag IN VARCHAR2) IS
2947
2948 ------------------------------
2949 /* Cursor to retrieve Additional Element Information */
2950
2951 /*
2952 CURSOR csr_get_element(p_bus_grp_id NUMBER) IS
2953 SELECT hoi.org_information2 element_type_id
2954 ,hoi.org_information3 input_value_id
2955 ,hoi.org_information7 element_narrative
2956 ,pec.classification_name
2957 ,piv.uom
2958 FROM hr_organization_information hoi
2959 ,pay_element_classifications pec
2960 ,pay_element_types_f pet
2961 ,pay_input_values_f piv
2962 WHERE hoi.organization_id = p_bus_grp_id
2963 AND hoi.org_information_context = 'Business Group:Payslip Info'
2964 AND hoi.org_information1 = 'ELEMENT'
2965 AND hoi.org_information2 = pet.element_type_id
2966 AND pec.classification_id = pet.classification_id
2967 AND piv.input_value_id = hoi.org_information3
2968 AND p_date_earned BETWEEN piv.effective_start_date AND piv.effective_end_date;
2969
2970 */
2971
2972 /*
2973 CURSOR csr_get_add_elements (p_payroll_action_id NUMBER ) IS
2974 SELECT action_information2 element_type_id
2975 ,action_information3 input_value_id
2976 ,action_information4 element_narrative
2977 ,action_information5 element_context
2978 ,action_information6 uom
2979 ,action_information8 prim_def_bal_id
2980 ,action_information12 ele_class
2981 FROM pay_action_information
2982 WHERE action_context_id = p_payroll_action_id
2983 AND action_information_category = 'NO ELEMENT DEFINITION'
2984 AND action_context_type = 'PA'
2985 AND action_information5 = 'F' ;
2986 */
2987
2988 CURSOR csr_get_add_elements (p_payroll_action_id NUMBER ) IS
2989 SELECT action_information2 element_type_id
2990 ,action_information3 input_value_id
2991 ,action_information4 element_narrative
2992 ,action_information5 element_context
2993 ,action_information6 uom
2994 ,action_information8 prim_def_bal_id
2995 ,action_information12 ele_class
2996 ,action_information13 lookup_type
2997 ,action_information14 value_set_id
2998 FROM pay_action_information
2999 WHERE action_context_id = p_payroll_action_id
3000 AND action_information_category = 'NO ELEMENT DEFINITION'
3001 AND action_context_type = 'PA'
3002 AND action_information5 = 'F' ;
3003
3004
3005 ----------------------------------------------------------
3006
3007 /* cursor to get he tax unit id (Legal Employer) from assignment action id */
3008
3009 CURSOR csr_get_le_org_id (p_assignment_action_id NUMBER) IS
3010 SELECT tax_unit_id
3011 FROM pay_assignment_actions
3012 WHERE assignment_action_id = p_assignment_action_id ;
3013
3014 --------------------------------------------------------
3015
3016 /* cursor to get the element code */
3017
3018 /*
3019 CURSOR csr_ele_code (p_ele_type_id NUMBER , p_le_org_id NUMBER ) IS
3020 select eei_information1
3021 from pay_element_type_extra_info
3022 where element_type_id = p_ele_type_id
3023 and ( eei_information2 = p_le_org_id OR eei_information2 is null )
3024 and information_type = 'NO_ELEMENT_CODES'
3025 and eei_information_category = 'NO_ELEMENT_CODES'
3026 and rownum = 1
3027 order by eei_information2 , element_type_extra_info_id ;
3028 */
3029
3030 -- modifying the above cursor
3031
3032 cursor csr_ele_code(p_ele_type_id NUMBER , p_leg_emp_id NUMBER ) is
3033 select nvl((select eei_information1 from pay_element_type_extra_info petei
3034 where petei.information_type='NO_ELEMENT_CODES'
3035 and element_type_id = p_ele_type_id
3036 and petei.eei_information2 = p_leg_emp_id
3037 and rownum=1),
3038 (select eei_information1 from pay_element_type_extra_info petei
3039 where petei.information_type='NO_ELEMENT_CODES'
3040 and element_type_id = p_ele_type_id
3041 and eei_information2 is null
3042 and rownum=1)) from dual;
3043
3044 --------------------------------------------------------
3045
3046 /* cursor to get the further element entry info for payslip information */
3047
3048 CURSOR csr_payslip_info (p_ele_entry_id NUMBER) IS
3049 SELECT ENTRY_INFORMATION1
3050 FROM pay_element_entries_f
3051 where ELEMENT_ENTRY_ID = p_ele_entry_id ;
3052
3053 -------------------------------
3054 /* Cursor to retrieve run result value of Additional Elements */
3055 CURSOR csr_result_value(p_iv_id NUMBER
3056 ,p_ele_type_id NUMBER
3057 ,p_assignment_action_id NUMBER) IS
3058 SELECT rrv.result_value val
3059 ,rr.element_entry_id ele_entry_id
3060 FROM pay_run_result_values rrv
3061 ,pay_run_results rr
3062 ,pay_assignment_actions paa
3063 ,pay_payroll_actions ppa
3064 WHERE rrv.input_value_id = p_iv_id
3065 AND rr.element_type_id = p_ele_type_id
3066 AND rr.run_result_id = rrv.run_result_id
3067 AND rr.assignment_action_id = paa.assignment_action_id
3068 AND paa.assignment_action_id = p_assignment_action_id
3069 AND ppa.payroll_action_id = paa.payroll_action_id
3070 AND ppa.action_type IN ('Q','R')
3071 AND rrv.result_value IS NOT NULL;
3072 ------------------------------
3073
3074 -- rec_get_element csr_get_element%ROWTYPE;
3075 rec_get_add_element csr_get_add_elements%ROWTYPE;
3076
3077 l_result_value pay_run_result_values.result_value%TYPE := 0;
3078 l_balance_value NUMBER := 0;
3079 l_action_info_id NUMBER;
3080 l_ovn NUMBER;
3081 l_element_context VARCHAR2(10);
3082 l_index NUMBER := 0;
3083 l_formatted_value VARCHAR2(50) := NULL;
3084 l_flag NUMBER := 0;
3085 l_le_org_id NUMBER;
3086 l_ele_code VARCHAR2(240) := NULL ;
3087 l_ele_entry_id NUMBER;
3088 l_payslip_info varchar2(240);
3089 l_archive VARCHAR2(2);
3090 l_arhive_prim_bal VARCHAR2(5);
3091 ------------------------------
3092
3093 BEGIN
3094
3095 IF g_debug THEN
3096 hr_utility.set_location(' Entering Procedure ARCHIVE_ADD_ELEMENT',320);
3097 END IF;
3098
3099 IF p_archive_flag = 'N' THEN
3100 ---------------------------------------------------
3101 --Check if global table has already been populated
3102 ---------------------------------------------------
3103 IF g_element_table.count = 0 THEN
3104
3105 /*
3106 OPEN csr_get_element(g_business_group_id);
3107 LOOP
3108 FETCH csr_get_element INTO rec_get_element;
3109 EXIT WHEN csr_get_element%NOTFOUND;
3110
3111 l_element_context := 'F'; --Additional Element Context
3112 SETUP_ELEMENT_DEFINITIONS(p_classification_name => rec_get_element.classification_name
3113 ,p_element_name => rec_get_element.element_narrative
3114 ,p_element_type_id => rec_get_element.element_type_id
3115 ,p_input_value_id => rec_get_element.input_value_id
3116 ,p_element_type => l_element_context
3117 ,p_uom => rec_get_element.uom
3118 ,p_archive_flag => p_archive_flag);
3119
3120 END LOOP;
3121 CLOSE csr_get_element;
3122 */
3123
3124 OPEN csr_get_add_elements (p_payroll_action_id);
3125 LOOP
3126 FETCH csr_get_add_elements INTO rec_get_add_element;
3127 EXIT WHEN csr_get_add_elements%NOTFOUND;
3128
3129 l_element_context := 'F'; --Additional Element Context
3130 SETUP_ELEMENT_DEFINITIONS(p_classification_name => rec_get_add_element.ele_class
3131 ,p_element_name => rec_get_add_element.element_narrative
3132 ,p_element_type_id => rec_get_add_element.element_type_id
3133 ,p_input_value_id => rec_get_add_element.input_value_id
3134 ,p_element_type => l_element_context
3135 ,p_uom => rec_get_add_element.uom
3136 ,p_archive_flag => p_archive_flag
3137 ,p_prim_bal_def_bal_id => rec_get_add_element.prim_def_bal_id
3138 ,p_lookup_type => rec_get_add_element.lookup_type
3139 ,p_value_set_id => rec_get_add_element.value_set_id );
3140
3141 END LOOP;
3142 CLOSE csr_get_add_elements;
3143
3144
3145 END IF;
3146
3147 ELSIF p_archive_flag = 'Y' AND g_element_table.count > 0 THEN
3148
3149 FOR l_index IN g_element_table.first.. g_element_table.last LOOP
3150 l_result_value := NULL;
3151
3152 BEGIN
3153
3154 l_balance_value := NULL ;
3155
3156 IF (g_element_table(l_index).prim_bal_def_bal_id IS NOT NULL) THEN
3157
3158 -- get the primary balance ytd value for the element
3159 l_balance_value := pay_balance_pkg.get_value(g_element_table(l_index).prim_bal_def_bal_id , p_assignment_action_id );
3160
3161 END IF;
3162
3163 /* get the element run result value */
3164 OPEN csr_result_value(g_element_table(l_index).input_value_id
3165 ,g_element_table(l_index).element_type_id
3166 ,p_assignment_action_id);
3167 LOOP
3168 FETCH csr_result_value INTO l_result_value , l_ele_entry_id ;
3169
3170 IF (csr_result_value%NOTFOUND) AND
3171 (csr_result_value%ROWCOUNT < 1) AND
3172 (g_element_table(l_index).prim_bal_def_bal_id IS NOT NULL) AND
3173 (l_balance_value <> 0) THEN
3174
3175 l_arhive_prim_bal := 'Y' ;
3176
3177 END IF ;
3178
3179 EXIT WHEN csr_result_value%NOTFOUND;
3180
3181 IF (csr_result_value%ROWCOUNT > 1) THEN
3182 l_balance_value := NULL ;
3183 END IF;
3184
3185 -- fnd_file.put_line(fnd_file.log,'------ before : l_result_value = ' || l_result_value);
3186
3187 -- check if a lookup was attached to the input value and fetch the meaning
3188
3189 IF (l_result_value is not null) AND (g_element_table(l_index).lookup_type is not null) THEN
3190
3191 l_result_value := hr_general.decode_lookup(g_element_table(l_index).lookup_type , l_result_value);
3192
3193 ELSIF (l_result_value is not null) AND (g_element_table(l_index).value_set_id is not null) THEN
3194
3195 l_result_value := pay_input_values_pkg.decode_vset_value( g_element_table(l_index).value_set_id ,l_result_value );
3196
3197 END IF ;
3198
3199 -- fnd_file.put_line(fnd_file.log,'after : l_result_value = ' || l_result_value);
3200
3201 -- l_balance_value := NULL ;
3202
3203 -- l_balance_value := pay_balance_pkg.get_value(g_element_table(l_index).prim_bal_def_bal_id , p_assignment_action_id );
3204
3205 -- fnd_file.put_line(fnd_file.log,'l_result_value = ' || l_result_value);
3206 -- fnd_file.put_line(fnd_file.log,'l_ele_entry_id = ' || l_ele_entry_id);
3207
3208
3209 -- modifying the below condition to get the ytd value for primary balance
3210 -- only once in case of multiple element entries
3211
3212 -- IF (g_element_table(l_index).prim_bal_def_bal_id IS NOT NULL) THEN
3213
3214 /*
3215 IF (g_element_table(l_index).prim_bal_def_bal_id IS NOT NULL) and (csr_result_value%ROWCOUNT = 1) THEN
3216
3217 -- get the primary balance ytd value for the element
3218 l_balance_value := pay_balance_pkg.get_value(g_element_table(l_index).prim_bal_def_bal_id , p_assignment_action_id );
3219
3220 END IF;
3221 */
3222
3223 -- fnd_file.put_line(fnd_file.log,'csr_rec.prim_def_bal_id = ' || g_element_table(l_index).prim_bal_def_bal_id);
3224 -- fnd_file.put_line(fnd_file.log,'l_balance_value = ' || l_balance_value);
3225
3226 /* get the legal employer */
3227 OPEN csr_get_le_org_id (p_assignment_action_id );
3228 FETCH csr_get_le_org_id INTO l_le_org_id ;
3229 CLOSE csr_get_le_org_id ;
3230
3231 -- fnd_file.put_line(fnd_file.log,'l_le_org_id = ' || l_le_org_id);
3232
3233 /* get the element code from the legal employer */
3234 OPEN csr_ele_code (g_element_table(l_index).element_type_id , l_le_org_id );
3235 FETCH csr_ele_code INTO l_ele_code ;
3236 CLOSE csr_ele_code ;
3237
3238 /*
3239 IF (l_ele_code IS NOT NULL) THEN
3240 l_ele_code := hr_general.decode_lookup('NO_ELEMENT_CODES',l_ele_code);
3241 END IF;
3242 */
3243
3244 -- fnd_file.put_line(fnd_file.log,'l_ele_code = ' || l_ele_code);
3245
3246 /* get the payslip information */
3247 l_payslip_info := NULL ;
3248
3249 OPEN csr_payslip_info (l_ele_entry_id );
3250 FETCH csr_payslip_info INTO l_payslip_info ;
3251 CLOSE csr_payslip_info ;
3252
3253 -- fnd_file.put_line(fnd_file.log,'l_payslip_info = ' || l_payslip_info);
3254
3255 IF (l_result_value is not null) THEN l_archive := 'Y';
3256 -- 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';
3257 ELSE l_archive := 'N';
3258 END IF;
3259
3260
3261 IF (l_archive = 'Y') THEN
3262 -- IF l_result_value is not null THEN
3263
3264 -- fnd_file.put_line(fnd_file.log,'l_result_value is not null ');
3265
3266 pay_action_information_api.create_action_information (
3267 p_action_information_id => l_action_info_id
3268 ,p_action_context_id => p_archive_assact_id
3269 ,p_action_context_type => 'AAP'
3270 ,p_object_version_number => l_ovn
3271 ,p_effective_date => p_effective_date
3272 ,p_source_id => NULL
3273 ,p_source_text => NULL
3274 ,p_action_information_category => 'NO ELEMENT INFO'
3275 ,p_action_information1 => g_element_table(l_index).element_type_id
3276 ,p_action_information2 => g_element_table(l_index).input_value_id
3277 ,p_action_information3 => g_element_table(l_index).element_type
3278 ,p_action_information4 => l_result_value --l_formatted_value
3279 ,p_action_information9 => 'Additional Element'
3280 ,p_action_information10 => g_element_table(l_index).prim_bal_def_bal_id
3281 ,p_action_information11 => 'PBAL'
3282 ,p_action_information12 => l_balance_value
3283 ,p_action_information13 => l_ele_code
3284 ,p_action_information14 => l_payslip_info
3285 ,p_assignment_id => p_assignment_id);
3286 END IF;
3287
3288 END LOOP;
3289 CLOSE csr_result_value;
3290
3291 -- archive results if run result was NULL but primary balance value is not null
3292
3293 IF (l_arhive_prim_bal = 'Y') THEN
3294
3295 -- though the run result is NULL, the ytd balance has a value
3296 -- so we will archive this result
3297
3298 l_result_value := NULL ;
3299 l_payslip_info := NULL ;
3300
3301 -- fnd_file.put_line(fnd_file.log,'LOC 5 => p_assignment_action_id , l_element_name = ' || p_assignment_action_id ||' , '||l_element_name);
3302
3303 pay_action_information_api.create_action_information (
3304 p_action_information_id => l_action_info_id
3305 ,p_action_context_id => p_archive_assact_id
3306 ,p_action_context_type => 'AAP'
3307 ,p_object_version_number => l_ovn
3308 ,p_effective_date => p_effective_date
3309 ,p_source_id => NULL
3310 ,p_source_text => NULL
3311 ,p_action_information_category => 'NO ELEMENT INFO'
3312 ,p_action_information1 => g_element_table(l_index).element_type_id
3313 ,p_action_information2 => g_element_table(l_index).input_value_id
3314 ,p_action_information3 => g_element_table(l_index).element_type
3315 ,p_action_information4 => l_result_value --l_formatted_value
3316 ,p_action_information9 => 'Additional Element'
3317 ,p_action_information10 => g_element_table(l_index).prim_bal_def_bal_id
3318 ,p_action_information11 => 'PBAL'
3319 ,p_action_information12 => l_balance_value
3320 ,p_action_information13 => l_ele_code
3321 ,p_action_information14 => l_payslip_info
3322 ,p_assignment_id => p_assignment_id);
3323
3324 END IF ;
3325
3326 EXCEPTION WHEN OTHERS THEN
3327 g_err_num := SQLCODE;
3328 /*fnd_file.put_line(fnd_file.log,'ORA_ERR: ' || g_err_num || 'In ARCHIVE_ADD_ELEMENT');*/
3329
3330 IF g_debug THEN
3331 hr_utility.set_location('ORA_ERR: ' || g_err_num || 'In ARCHIVE_ADD_ELEMENT',330);
3332 END IF;
3333 END;
3334 END LOOP;
3335
3336 END IF;
3337
3338 IF g_debug THEN
3339 hr_utility.set_location(' Leaving Procedure ARCHIVE_ADD_ELEMENT',340);
3340 END IF;
3341
3342 END ARCHIVE_ADD_ELEMENT;
3343
3344 ---------------------------------- PROCEDURE ARCHIVE_OTH_BALANCE ----------------------------------------------------------------
3345
3346 /* OTHER BALANCES REGION */
3347
3348 PROCEDURE ARCHIVE_OTH_BALANCE (p_archive_assact_id IN NUMBER,
3349 p_assignment_action_id IN NUMBER,
3350 p_assignment_id IN NUMBER,
3351 p_payroll_action_id IN NUMBER,
3352 p_record_count IN NUMBER,
3353 p_pre_payact_id IN NUMBER,
3354 p_effective_date IN DATE,
3355 p_date_earned IN DATE,
3356 p_archive_flag IN VARCHAR2) IS
3357
3358 ------------------
3359 /* Cursor to retrieve Other Balances Information */
3360 CURSOR csr_get_balance(p_bus_grp_id NUMBER) IS
3361 SELECT org_information4 balance_type_id
3362 ,org_information5 balance_dim_id
3363 ,org_information7 narrative
3364 FROM hr_organization_information
3365 WHERE organization_id = p_bus_grp_id
3366 AND org_information_context = 'Business Group:Payslip Info'
3367 AND org_information1 = 'BALANCE';
3368
3369 -----------------
3370 /* Cursor to retrieve Tax Unit Id for setting context */
3371 CURSOR csr_tax_unit (p_run_assact_id NUMBER) IS
3372 SELECT paa.tax_unit_id
3373 FROM pay_assignment_actions paa
3374 WHERE paa.assignment_action_id = p_run_assact_id;
3375 -----------------
3376 /* Cursor to fetch defined balance id */
3377 CURSOR csr_def_balance(bal_type_id NUMBER, bal_dim_id NUMBER) IS
3378 SELECT defined_balance_id
3379 FROM pay_defined_balances
3380 WHERE balance_type_id = bal_type_id
3381 AND balance_dimension_id = bal_dim_id;
3382 ----------------
3383 rec_get_balance csr_get_balance%ROWTYPE;
3384 l_balance_value NUMBER := 0;
3385 l_action_info_id NUMBER;
3386 l_ovn NUMBER;
3387 l_index NUMBER;
3388 l_tu_id NUMBER;
3389 l_defined_balance_id NUMBER :=0;
3390 l_formatted_value VARCHAR2(50) := NULL;
3391 l_flag NUMBER := 0;
3392
3393 ---------------
3394
3395 BEGIN
3396
3397 IF g_debug THEN
3398 hr_utility.set_location(' Entering Procedure ARCHIVE_OTH_BALANCE',350);
3399 END IF;
3400
3401 -- fnd_file.put_line(fnd_file.log,'In ARCHIVE_OTH_BALANCE 0');
3402 /*fnd_file.put_line(fnd_file.log,'Entering In ARCHIVE_OTH_BALANCE global');*/
3403
3404 IF p_archive_flag = 'N' THEN
3405 ---------------------------------------------------
3406 --Check if global table has already been populated
3407 ---------------------------------------------------
3408 IF g_user_balance_table.count = 0 THEN
3409 OPEN csr_get_balance(g_business_group_id);
3410 LOOP
3411 FETCH csr_get_balance INTO rec_get_balance;
3412 EXIT WHEN csr_get_balance%NOTFOUND;
3413
3414 OPEN csr_def_balance(rec_get_balance.balance_type_id,rec_get_balance.balance_dim_id);
3415 FETCH csr_def_balance INTO l_defined_balance_id;
3416 CLOSE csr_def_balance;
3417
3418 PAY_NO_PAYSLIP_ARCHIVE.SETUP_BALANCE_DEFINITIONS
3419 (p_balance_name => rec_get_balance.narrative
3420 ,p_defined_balance_id => l_defined_balance_id
3421 ,p_balance_type_id => rec_get_balance.balance_type_id);
3422 END LOOP;
3423 CLOSE csr_get_balance;
3424 END IF;
3425
3426 ---------------------------------------------------
3427 -- For Tax Card details ,
3428 -- Check if global table has already been populated
3429 -- if not then populate the values
3430 ---------------------------------------------------
3431 IF g_tax_card_tab.count = 0 THEN
3432
3433 g_tax_card_tab(1).inp_val_name := 'Method of Receipt';
3434 g_tax_card_tab(2).inp_val_name := 'Tax Municipality';
3435 g_tax_card_tab(3).inp_val_name := 'Tax Card Type';
3436 g_tax_card_tab(4).inp_val_name := 'Tax Percentage';
3437 g_tax_card_tab(5).inp_val_name := 'Tax Table Number';
3438 g_tax_card_tab(6).inp_val_name := 'Tax Table Type';
3439 g_tax_card_tab(7).inp_val_name := 'Tax Free Threshold';
3440 g_tax_card_tab(8).inp_val_name := 'Registration Date';
3441 g_tax_card_tab(9).inp_val_name := 'Date Returned';
3442 --g_tax_card_tab(10).inp_val_name := 'Date Returned';
3443
3444 END IF;
3445
3446 ---------------------------------------------------
3447 -- For Mandatory Balance Details ,
3448 -- Check if global table has already been populated
3449 -- if not hen populate the values
3450 ---------------------------------------------------
3451
3452 IF g_bal_val.count = 0 THEN
3453
3454 g_bal_val(1).bal_name := 'TOTAL_PAY_ASG_PTD'; -- Net salary , period
3455 g_bal_val(2).bal_name := 'TOTAL_PAY_ASG_YTD'; -- Net salary , ytd
3456 g_bal_val(3).bal_name := 'TAX_ASG_PTD'; -- Withholding tax , period
3457 g_bal_val(4).bal_name := 'TAX_ASG_YTD'; -- Withholding tax , ytd
3458 g_bal_val(5).bal_name := 'TAX_TABLE_DEDUCTION_BASIS_ASG_PTD'; -- for Tax Deduction Basis , period
3459 g_bal_val(6).bal_name := 'TAX_TABLE_DEDUCTION_BASIS_ASG_YTD'; -- for Tax Deduction Basis , ytd
3460 g_bal_val(7).bal_name := 'TAX_PERCENTAGE_DEDUCTION_BASIS_ASG_PTD'; -- for Tax Deduction Basis , period
3461 g_bal_val(8).bal_name := 'TAX_PERCENTAGE_DEDUCTION_BASIS_ASG_YTD'; -- for Tax Deduction Basis , ytd
3462
3463 g_bal_val(9).bal_name := 'TAX_TABLE_DEDUCTION_ASG_PTD'; -- Table Based Tax Period
3464 g_bal_val(10).bal_name := 'TAX_TABLE_DEDUCTION_ASG_YTD'; -- Table Based Tax Ytd
3465 g_bal_val(11).bal_name := 'TAX_PERCENTAGE_DEDUCTION_ASG_PTD'; -- Percentage Based Tax Period
3466 g_bal_val(12).bal_name := 'TAX_PERCENTAGE_DEDUCTION_ASG_YTD'; -- Percentage Based Tax Ytd
3467
3468 -- g_bal_val(13).bal_name := 'SEAMAN_ASG_PTD'; -- Seaman Deduction Basis Period
3469 -- g_bal_val(14).bal_name := 'SEAMAN_ASG_YTD'; -- Seaman Deduction Basis Ytd
3470
3471
3472 -- g_bal_val(15).bal_name := '' ; -- to be used for Holiday pay, due this year
3473 -- g_bal_val(16).bal_name := '' ; -- to be used for Accrued basis for holiday pay
3474
3475 g_bal_val(13).bal_name := 'HOLIDAY_PAY_REMAINING_ASG_YTD' ; -- for Holiday pay, due this year
3476 g_bal_val(14).bal_name := 'HOLIDAY_PAY_OVER_60_REMAINING_ASG_YTD' ; -- Holiday pay, due this year
3477 g_bal_val(15).bal_name := 'HOLIDAY_PAY_BASE_ASG_PTD' ; -- for basis for holiday pay
3478 g_bal_val(16).bal_name := 'HOLIDAY_PAY_BASE_ASG_YTD' ; -- for basis for holiday pay
3479
3480
3481 END IF;
3482
3483 ELSIF p_archive_flag = 'Y' THEN
3484
3485 OPEN csr_tax_unit(p_assignment_action_id);
3486 FETCH csr_tax_unit INTO l_tu_id;
3487 CLOSE csr_tax_unit;
3488
3489 PAY_BALANCE_PKG.SET_CONTEXT('TAX_UNIT_ID',l_tu_id);
3490 PAY_BALANCE_PKG.SET_CONTEXT('DATE_EARNED',fnd_date.date_to_canonical(p_date_earned));
3491
3492 IF g_user_balance_table.count > 0 THEN
3493 -- fnd_file.put_line(fnd_file.log,'In ARCHIVE_OTH_BALANCE 1');
3494
3495 FOR l_index IN g_user_balance_table.first.. g_user_balance_table.last LOOP
3496
3497 l_balance_value := pay_balance_pkg.get_value(g_user_balance_table(l_index).defined_balance_id,p_assignment_action_id);
3498
3499 IF l_balance_value > 0 THEN
3500 -- fnd_file.put_line(fnd_file.log,'In ARCHIVE_OTH_BALANCE 2 :' || l_balance_value);
3501 pay_action_information_api.create_action_information (
3502 p_action_information_id => l_action_info_id
3503 ,p_action_context_id => p_archive_assact_id
3504 ,p_action_context_type => 'AAP'
3505 ,p_object_version_number => l_ovn
3506 ,p_effective_date => p_effective_date
3507 ,p_source_id => NULL
3508 ,p_source_text => NULL
3509 ,p_action_information_category => 'EMEA BALANCES'
3510 ,p_action_information1 => g_user_balance_table(l_index).defined_balance_id
3511 ,p_action_information2 => 'OBAL' --Other Balances Context
3512 ,p_action_information4 => fnd_number.number_to_canonical(l_balance_value) --l_formatted_value
3513 ,p_action_information5 => NULL
3514 ,p_action_information6 => 'Other Balances'
3515 ,p_assignment_id => p_assignment_id);
3516 END IF;
3517 END LOOP;
3518 END IF; /* For table count check */
3519 END IF;
3520
3521 -- fnd_file.put_line(fnd_file.log,'Leaving ARCHIVE_OTH_BALANCE 1');
3522
3523 EXCEPTION WHEN OTHERS THEN
3524 g_err_num := SQLCODE;
3525 -- fnd_file.put_line(fnd_file.log,'ORA_ERR: ' || g_err_num || 'In ARCHIVE_OTH_BALANCE'||SQLERRM);
3526
3527 IF g_debug THEN
3528 hr_utility.set_location('ORA_ERR: ' || g_err_num || 'In ARCHIVE_OTH_BALANCE',360);
3529 END IF;
3530
3531 END ARCHIVE_OTH_BALANCE;
3532
3533 ----------------------------------------- PROCEDURE ARCHIVE_CODE ---------------------------------------------------------
3534
3535 /* ARCHIVE CODE */
3536
3537 PROCEDURE ARCHIVE_CODE(p_assignment_action_id IN NUMBER
3538 ,p_effective_date IN DATE)
3539 IS
3540
3541 ----------------------
3542 /* Cursor to retrieve Payroll and Prepayment related Ids for Archival */
3543 /*
3544
3545 CURSOR csr_archive_ids (p_locking_action_id NUMBER) IS
3546 SELECT prepay_assact.assignment_action_id prepay_assact_id
3547 ,prepay_assact.assignment_id prepay_assgt_id
3548 ,prepay_payact.payroll_action_id prepay_payact_id
3549 ,prepay_payact.effective_date prepay_effective_date
3550 ,run_assact.assignment_id run_assgt_id
3551 ,run_assact.assignment_action_id run_assact_id
3552 ,run_payact.payroll_action_id run_payact_id
3553 ,run_payact.payroll_id payroll_id
3554 FROM pay_action_interlocks archive_intlck
3555 ,pay_assignment_actions prepay_assact
3556 ,pay_payroll_actions prepay_payact
3557 ,pay_action_interlocks prepay_intlck
3558 ,pay_assignment_actions run_assact
3559 ,pay_payroll_actions run_payact
3560 WHERE archive_intlck.locking_action_id = p_locking_action_id
3561 AND prepay_assact.assignment_action_id = archive_intlck.locked_action_id
3562 AND prepay_payact.payroll_action_id = prepay_assact.payroll_action_id
3563 AND prepay_payact.action_type IN ('U','P')
3564 AND prepay_intlck.locking_action_id = prepay_assact.assignment_action_id
3565 AND run_assact.assignment_action_id = prepay_intlck.locked_action_id
3566 AND run_payact.payroll_action_id = run_assact.payroll_action_id
3567 AND run_payact.action_type IN ('Q', 'R')
3568 ORDER BY prepay_intlck.locking_action_id,prepay_intlck.locked_action_id desc;
3569
3570 */
3571
3572 CURSOR csr_archive_ids (p_locking_action_id NUMBER) IS
3573 SELECT prepay_assact.assignment_action_id prepay_assact_id
3574 ,prepay_assact.assignment_id prepay_assgt_id
3575 ,prepay_payact.payroll_action_id prepay_payact_id
3576 ,prepay_payact.effective_date prepay_effective_date
3577 ,run_assact.assignment_id run_assgt_id
3578 ,run_assact.assignment_action_id run_assact_id
3579 ,run_payact.payroll_action_id run_payact_id
3580 ,run_payact.payroll_id payroll_id
3581 FROM pay_action_interlocks archive_intlck
3582 ,pay_assignment_actions prepay_assact
3583 ,pay_payroll_actions prepay_payact
3584 ,pay_action_interlocks prepay_intlck
3585 ,pay_assignment_actions run_assact
3586 ,pay_payroll_actions run_payact
3587 WHERE archive_intlck.locking_action_id = p_locking_action_id
3588 AND prepay_assact.assignment_action_id = archive_intlck.locked_action_id
3589 AND prepay_payact.payroll_action_id = prepay_assact.payroll_action_id
3590 AND prepay_payact.action_type IN ('U','P')
3591 AND prepay_intlck.locking_action_id = prepay_assact.assignment_action_id
3592 AND run_assact.assignment_action_id = prepay_intlck.locked_action_id
3593 AND NOT EXISTS (SELECT NULL FROM pay_assignment_actions
3594 WHERE payroll_action_id = run_assact.payroll_action_id
3595 AND source_action_id = run_assact.ASSIGNMENT_action_id )
3596 AND run_payact.payroll_action_id = run_assact.payroll_action_id
3597 AND run_payact.action_type IN ('Q', 'R')
3598 ORDER BY prepay_intlck.locking_action_id,prepay_intlck.locked_action_id desc;
3599
3600 ---------------------
3601 /* Cursor to retrieve time period information */
3602 CURSOR csr_period_end_date(p_assact_id NUMBER,p_pay_act_id NUMBER) IS
3603 SELECT ptp.end_date end_date,
3604 ptp.regular_payment_date regular_payment_date,
3605 ptp.time_period_id time_period_id,
3606 ppa.date_earned date_earned,
3607 ppa.effective_date effective_date,
3608 ptp.start_date start_date
3609 FROM per_time_periods ptp
3610 ,pay_payroll_actions ppa
3611 ,pay_assignment_actions paa
3612 WHERE ptp.payroll_id =ppa.payroll_id
3613 AND ppa.payroll_action_id =paa.payroll_action_id
3614 AND paa.assignment_action_id =p_assact_id
3615 AND ppa.payroll_action_id =p_pay_act_id
3616 AND ppa.date_earned BETWEEN ptp.start_date AND ptp.end_date;
3617 -----------------
3618 /* Cursor to retrieve Archive Payroll Action Id */
3619 CURSOR csr_archive_payact(p_assignment_action_id NUMBER) IS
3620 SELECT payroll_action_id
3621 FROM pay_assignment_actions
3622 WHERE assignment_Action_id = p_assignment_action_id;
3623 -----------------
3624 l_archive_payact_id NUMBER;
3625 l_record_count NUMBER;
3626 l_actid NUMBER;
3627 l_end_date per_time_periods.end_date%TYPE;
3628 l_pre_end_date per_time_periods.end_date%TYPE;
3629 l_reg_payment_date per_time_periods.regular_payment_date%TYPE;
3630 l_pre_reg_payment_date per_time_periods.regular_payment_date%TYPE;
3631 l_date_earned pay_payroll_actions.date_earned%TYPE;
3632 l_pre_date_earned pay_payroll_actions.date_earned%TYPE;
3633 l_effective_date pay_payroll_actions.effective_date%TYPE;
3634 l_pre_effective_date pay_payroll_actions.effective_date%TYPE;
3635 l_run_payact_id NUMBER;
3636 l_action_context_id NUMBER;
3637 g_archive_pact NUMBER;
3638 p_assactid NUMBER;
3639 l_time_period_id per_time_periods.time_period_id%TYPE;
3640 l_pre_time_period_id per_time_periods.time_period_id%TYPE;
3641 l_start_date per_time_periods.start_date%TYPE;
3642 l_pre_start_date per_time_periods.start_date%TYPE;
3643 l_fnd_session NUMBER := 0;
3644 l_prev_prepay NUMBER := 0;
3645 ------------------
3646
3647 BEGIN
3648
3649 -- fnd_file.put_line(fnd_file.log,'Entering Procedure ARCHIVE_CODE');
3650
3651 IF g_debug THEN
3652 hr_utility.set_location(' Entering Procedure ARCHIVE_CODE',380);
3653 END IF;
3654
3655 OPEN csr_archive_payact(p_assignment_action_id);
3656 FETCH csr_archive_payact INTO l_archive_payact_id;
3657 CLOSE csr_archive_payact;
3658
3659 -- fnd_file.put_line(fnd_file.log,'closed csr_archive_payact');
3660
3661 l_record_count := 0;
3662
3663 -- fnd_file.put_line(fnd_file.log,'LOC 6 => p_assignment_action_id = ' || p_assignment_action_id);
3664
3665 FOR rec_archive_ids IN csr_archive_ids(p_assignment_action_id) LOOP
3666
3667 -- fnd_file.put_line(fnd_file.log,'LOC 7 => rec_archive_ids.run_assact_id = ' || rec_archive_ids.run_assact_id );
3668
3669 OPEN csr_period_end_date(rec_archive_ids.run_assact_id,rec_archive_ids.run_payact_id);
3670 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;
3671 CLOSE csr_period_end_date;
3672
3673 -- fnd_file.put_line(fnd_file.log,'closed csr_period_end_date');
3674
3675 OPEN csr_period_end_date(rec_archive_ids.prepay_assact_id,rec_archive_ids.prepay_payact_id);
3676 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;
3677 CLOSE csr_period_end_date;
3678
3679 -- fnd_file.put_line(fnd_file.log,'closed csr_period_end_date');
3680
3681 /*fnd_file.put_line(fnd_file.log,'ARCHIVE_EMPLOYEE_DETAILS');*/
3682
3683 -------------------------------------------------------------
3684 --Archive EMPLOYEE_DETAILS, PAYMENT_INFO and BALANCES
3685 --for every prepayment assignment action id
3686 -------------------------------------------------------------
3687
3688 IF (rec_archive_ids.prepay_assact_id <> l_prev_prepay) THEN
3689
3690 -- fnd_file.put_line(fnd_file.log,'in ARCHIVE_EMPLOYEE_DETAILS');
3691
3692 /*
3693 ARCHIVE_EMPLOYEE_DETAILS(p_archive_assact_id => p_assignment_action_id
3694 ,p_assignment_id => rec_archive_ids.run_assgt_id
3695 ,p_assignment_action_id => rec_archive_ids.run_assact_id
3696 ,p_payroll_action_id => l_archive_payact_id
3697 ,p_time_period_id => l_time_period_id
3698 ,p_date_earned => l_pre_date_earned
3699 ,p_pay_date_earned => l_date_earned
3700 ,p_effective_date => p_effective_date);
3701 */
3702
3703 -- using l_date_earned (Payroll Run Date Earned) instead of l_pre_date_earned (Prepayments Date Earned)
3704 -- because the date_earned column at table pay_payroll_actions for Prepayments may not always be populated.
3705
3706 ARCHIVE_EMPLOYEE_DETAILS(p_archive_assact_id => p_assignment_action_id
3707 ,p_assignment_id => rec_archive_ids.run_assgt_id
3708 ,p_assignment_action_id => rec_archive_ids.run_assact_id
3709 ,p_payroll_action_id => l_archive_payact_id
3710 ,p_time_period_id => l_time_period_id
3711 ,p_date_earned => l_date_earned
3712 ,p_pay_date_earned => l_date_earned
3713 ,p_effective_date => p_effective_date);
3714
3715 -- fnd_file.put_line(fnd_file.log,'out ARCHIVE_EMPLOYEE_DETAILS');
3716 -- fnd_file.put_line(fnd_file.log,'in ARCHIVE_ADDL_EMP_DETAILS');
3717
3718
3719 ARCHIVE_ADDL_EMP_DETAILS(p_archive_assact_id => p_assignment_action_id
3720 ,p_assignment_id => rec_archive_ids.run_assgt_id
3721 ,p_assignment_action_id => rec_archive_ids.run_assact_id
3722 ,p_effective_date => p_effective_date
3723 ,p_date_earned => l_date_earned
3724 ,p_payroll_action_id => l_archive_payact_id );
3725
3726
3727
3728 -- fnd_file.put_line(fnd_file.log,'out ARCHIVE_ADDL_EMP_DETAILS, currently not being processed');
3729 -- fnd_file.put_line(fnd_file.log,'in ARCHIVE_PAYMENT_INFO');
3730
3731 /*fnd_file.put_line(fnd_file.log,'ARCHIVE_PAYMENT_INFO');*/
3732
3733 /*
3734 ARCHIVE_PAYMENT_INFO(p_archive_assact_id => p_assignment_action_id,
3735 p_prepay_assact_id => rec_archive_ids.prepay_assact_id,
3736 p_assignment_id => rec_archive_ids.prepay_assgt_id,
3737 p_date_earned => l_pre_date_earned,
3738 p_effective_date => p_effective_date);
3739 */
3740
3741 -- using l_date_earned (Payroll Run Date Earned) instead of l_pre_date_earned (Prepayments Date Earned)
3742 -- because the date_earned column at table pay_payroll_actions for Prepayments may not always be populated.
3743
3744 ARCHIVE_PAYMENT_INFO(p_archive_assact_id => p_assignment_action_id,
3745 p_prepay_assact_id => rec_archive_ids.prepay_assact_id,
3746 p_assignment_id => rec_archive_ids.prepay_assgt_id,
3747 p_date_earned => l_date_earned,
3748 p_effective_date => p_effective_date);
3749
3750 -- fnd_file.put_line(fnd_file.log,'out ARCHIVE_PAYMENT_INFO');
3751 -- fnd_file.put_line(fnd_file.log,'in ARCHIVE_OTH_BALANCE');
3752
3753 /*fnd_file.put_line(fnd_file.log,'ARCHIVE_OTH_BALANCE');*/
3754
3755 ARCHIVE_OTH_BALANCE(p_archive_assact_id => p_assignment_action_id,
3756 p_assignment_action_id => rec_archive_ids.run_assact_id,
3757 p_assignment_id => rec_archive_ids.run_assgt_id,
3758 p_payroll_action_id => l_archive_payact_id,
3759 p_record_count => l_record_count,
3760 p_pre_payact_id => rec_archive_ids.prepay_payact_id,
3761 p_effective_date => p_effective_date,
3762 p_date_earned => l_date_earned,
3763 p_archive_flag => 'Y');
3764
3765 -- -- fnd_file.put_line(fnd_file.log,'out ARCHIVE_OTH_BALANCE');
3766 -- fnd_file.put_line(fnd_file.log,'before end if');
3767
3768
3769
3770 l_prev_prepay := rec_archive_ids.prepay_assact_id;
3771
3772 END IF;
3773
3774 /*fnd_file.put_line(fnd_file.log,'ARCHIVE_ACCRUAL_PLAN');*/
3775
3776 /* ARCHIVE_ACCRUAL_PLAN (p_assignment_id => rec_archive_ids.run_assgt_id,
3777 p_date_earned => l_date_earned,
3778 p_effective_date => p_effective_date,
3779 p_archive_assact_id => p_assignment_action_id,
3780 p_run_assignment_action_id => rec_archive_ids.run_assact_id,
3781 p_period_end_date => l_end_date,
3782 p_period_start_date => l_start_date);*/
3783
3784 /*fnd_file.put_line(fnd_file.log,'ARCHIVE_ADD_ELEMENT');*/
3785
3786 -- fnd_file.put_line(fnd_file.log,'in ARCHIVE_ADD_ELEMENT');
3787
3788
3789 ARCHIVE_ADD_ELEMENT(p_archive_assact_id => p_assignment_action_id,
3790 p_assignment_action_id => rec_archive_ids.run_assact_id,
3791 p_assignment_id => rec_archive_ids.run_assgt_id,
3792 p_payroll_action_id => l_archive_payact_id,
3793 p_date_earned => l_date_earned,
3794 p_effective_date => p_effective_date,
3795 p_pre_payact_id => rec_archive_ids.prepay_payact_id,
3796 p_archive_flag => 'Y');
3797
3798 -- fnd_file.put_line(fnd_file.log,'out ARCHIVE_ADD_ELEMENT');
3799 -- fnd_file.put_line(fnd_file.log,'in ARCHIVE_MAIN_ELEMENTS');
3800
3801 /*fnd_file.put_line(fnd_file.log,'Assact id: '|| p_assignment_action_id);*/
3802
3803 ARCHIVE_MAIN_ELEMENTS (p_archive_assact_id => p_assignment_action_id,
3804 p_assignment_action_id => rec_archive_ids.run_assact_id,
3805 p_assignment_id => rec_archive_ids.run_assgt_id,
3806 p_date_earned => l_date_earned,
3807 p_effective_date => p_effective_date,
3808 p_payroll_action_id => l_archive_payact_id ) ;
3809
3810 -- fnd_file.put_line(fnd_file.log,'out ARCHIVE_MAIN_ELEMENTS');
3811
3812
3813
3814 l_record_count := l_record_count + 1;
3815
3816 END LOOP;
3817
3818 IF g_debug THEN
3819 hr_utility.set_location(' Leaving Procedure ARCHIVE_CODE',390);
3820 END IF;
3821
3822 -- fnd_file.put_line(fnd_file.log,'Leaving Procedure ARCHIVE_CODE');
3823
3824 END ARCHIVE_CODE;
3825
3826
3827 ---------------------------------------- PROCEDURE ARCHIVE_ADDL_EMP_DETAILS --------------------------------------------------------------------------
3828
3829 /*Additional Employee Details*/
3830
3831 PROCEDURE ARCHIVE_ADDL_EMP_DETAILS(p_archive_assact_id IN NUMBER
3832 ,p_assignment_id IN NUMBER
3833 ,p_assignment_action_id IN NUMBER
3834 ,p_effective_date IN DATE
3835 ,p_date_earned IN DATE
3836 ,p_payroll_action_id IN NUMBER )
3837 IS
3838 -------------
3839 CURSOR CSR_ACTUAL_TERM_DATE (p_assignment_id NUMBER) IS
3840 SELECT actual_termination_date
3841 FROM per_periods_of_service pps,
3842 per_all_assignments_f paa
3843 WHERE pps.period_of_service_id = paa.period_of_service_id
3844 AND p_date_earned between paa.effective_start_date and paa.effective_end_date
3845 AND paa.assignment_id = p_assignment_id;
3846 -------------
3847
3848 CURSOR get_details(p_assignment_id NUMBER , p_input_value VARCHAR2 ) IS
3849 SELECT ee.effective_start_date effective_start_date
3850 ,eev1.screen_entry_value screen_entry_value
3851 FROM per_all_assignments_f asg1
3852 ,per_all_assignments_f asg2
3853 ,per_all_people_f per
3854 ,pay_element_links_f el
3855 ,pay_element_types_f et
3856 ,pay_input_values_f iv1
3857 ,pay_element_entries_f ee
3858 ,pay_element_entry_values_f eev1
3859 WHERE asg1.assignment_id = p_assignment_id
3860 AND p_date_earned BETWEEN asg1.effective_start_date AND asg1.effective_end_date
3861 AND p_date_earned BETWEEN asg2.effective_start_date AND asg2.effective_end_date
3862 AND per.person_id = asg1.person_id
3863 AND asg2.person_id = per.person_id
3864 AND asg2.primary_flag = 'Y'
3865 AND et.element_name = 'Tax Card'
3866 AND et.legislation_code = 'NO'
3867 AND iv1.element_type_id = et.element_type_id
3868 AND iv1.name = p_input_value
3869 AND el.business_group_id = per.business_group_id
3870 AND el.element_type_id = et.element_type_id
3871 AND ee.assignment_id = asg2.assignment_id
3872 AND ee.element_link_id = el.element_link_id
3873 AND eev1.element_entry_id = ee.element_entry_id
3874 AND eev1.input_value_id = iv1.input_value_id
3875 AND p_date_earned BETWEEN ee.effective_start_date AND ee.effective_end_date
3876 AND p_date_earned BETWEEN eev1.effective_start_date AND eev1.effective_end_date;
3877 ------------
3878 CURSOR csr_tax_details(p_assignment_id NUMBER, p_input_value VARCHAR2) IS
3879 SELECT ee.effective_start_date
3880 ,eev1.screen_entry_value screen_entry_value
3881 FROM per_all_assignments_f asg1
3882 ,pay_element_links_f el
3883 ,pay_element_types_f et
3884 ,pay_input_values_f iv1
3885 ,pay_element_entries_f ee
3886 ,pay_element_entry_values_f eev1
3887 WHERE asg1.assignment_id = p_assignment_id
3888 AND p_date_earned BETWEEN asg1.effective_start_date AND asg1.effective_end_date
3889 AND et.element_name = 'Tax'
3890 AND et.legislation_code = 'NO'
3891 AND iv1.element_type_id = et.element_type_id
3892 AND iv1.name = p_input_value
3893 AND el.element_type_id = et.element_type_id
3894 AND ee.assignment_id = asg1.assignment_id
3895 AND ee.element_link_id = el.element_link_id
3896 AND eev1.element_entry_id = ee.element_entry_id
3897 AND eev1.input_value_id = iv1.input_value_id
3898 AND p_date_earned BETWEEN ee.effective_start_date AND ee.effective_end_date
3899 AND p_date_earned BETWEEN eev1.effective_start_date AND eev1.effective_end_date;
3900 -------------
3901 CURSOR csr_tax_category (p_assignment_id NUMBER) IS
3902 SELECT segment13
3903 FROM per_all_assignments_f paa,
3904 hr_soft_coding_keyflex hsc
3905 WHERE paa.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
3906 AND p_date_earned BETWEEN paa.effective_start_date AND paa.effective_end_date
3907 AND paa.assignment_id = p_assignment_id;
3908 -------------
3909 CURSOR csr_global_value (p_global_name VARCHAR2) IS
3910 SELECT global_value
3911 FROM ff_globals_f
3912 WHERE global_name = p_global_name
3913 AND p_date_earned BETWEEN effective_start_date AND effective_end_date;
3914 -------------
3915 /* cursor to get the payroll_d */
3916 CURSOR csr_payroll (p_payroll_action_id NUMBER) IS
3917 SELECT PAY_NO_PAYSLIP_ARCHIVE.GET_PARAMETER(legislative_parameters,'PAYROLL_ID')
3918 FROM pay_payroll_actions
3919 WHERE payroll_action_id = p_payroll_action_id ;
3920
3921
3922 /* cursor to get the payroll details */
3923 CURSOR csr_payroll_details (l_payroll_id NUMBER) IS
3924 SELECT payroll_name , period_type
3925 FROM pay_all_payrolls_f
3926 WHERE payroll_id = l_payroll_id ;
3927 --------------
3928
3929 /* Cursor to get the Work Title from the assignment */
3930
3931 cursor csr_work_title (p_assignment_id NUMBER) IS
3932 SELECT hsck.segment4
3933 from per_all_assignments_f paaf
3934 ,hr_soft_coding_keyflex hsck
3935 where paaf.assignment_id= p_assignment_id
3936 and p_date_earned BETWEEN paaf.effective_start_date and paaf.effective_end_date
3937 and paaf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id;
3938 --------------
3939
3940 /* Cursor to get the Zone and meaning from UDT and lookup */
3941
3942 cursor get_code_meaning ( p_municipal_no VARCHAR2, l_eff_date DATE) IS
3943 SELECT hr_de_general.get_uci(l_eff_date, t.user_table_id, r.user_row_id,'ZONE')
3944 || ' ' || hr_general.decode_lookup('NO_TAX_MUNICIPALITY',
3945 hr_de_general.get_uci(l_eff_date, t.user_table_id, r.user_row_id, 'MAPPING_ID'))
3946 FROM pay_user_tables t
3947 ,pay_user_rows_f r
3948 WHERE t.user_table_name = 'NO_TAX_MUNICIPALITY'
3949 AND t.legislation_code = 'NO'
3950 AND r.user_table_id = t.user_table_id
3951 AND r.row_low_range_or_name = p_municipal_no
3952 AND l_eff_date BETWEEN r.effective_start_date AND r.effective_end_date;
3953
3954 ------------------------------------------------
3955
3956 l_actual_termination_date PER_PERIODS_OF_SERVICE.ACTUAL_TERMINATION_DATE%TYPE;
3957 l_tax_card_effective_date DATE;
3958 l_tax_card_type VARCHAR2(50);
3959 l_base_rate NUMBER(5,2);
3960 l_additional_rate NUMBER(5,2);
3961 l_yearly_income_limit NUMBER(10);
3962 l_previous_income NUMBER (10);
3963 l_ovn NUMBER ;
3964 l_rec get_details%ROWTYPE;
3965 l_tax_rec csr_tax_details%ROWTYPE;
3966 l_action_info_id pay_action_information.action_information_id%TYPE;
3967 l_tax_category hr_soft_coding_keyflex.segment13%TYPE;
3968 l_defined_balance_id pay_defined_balances.defined_balance_id%TYPE;
3969
3970 l_work_title hr_soft_coding_keyflex.segment4%type := NULL ;
3971 l_net_salary_ptd NUMBER;
3972 l_net_salary_ytd NUMBER;
3973 l_table_tax_basis_ptd NUMBER;
3974 l_table_tax_basis_ytd NUMBER;
3975 l_percent_tax_basis_ptd NUMBER;
3976 l_percent_tax_basis_ytd NUMBER;
3977 l_tax_deduction_basis_ptd NUMBER;
3978 l_tax_deduction_basis_ytd NUMBER;
3979 l_table_tax_ptd NUMBER;
3980 l_table_tax_ytd NUMBER;
3981 l_percent_tax_ptd NUMBER;
3982 l_percent_tax_ytd NUMBER;
3983 l_withholding_tax_ptd NUMBER;
3984 l_withholding_tax_ytd NUMBER;
3985 l_seaman_basis_ptd NUMBER;
3986 l_seaman_basis_ytd NUMBER;
3987
3988 l_threshold_remaining NUMBER;
3989 l_threshold_used_per_ytd NUMBER;
3990 l_hol_pay_due_this_yr_ytd NUMBER;
3991 l_basis_for_hol_pay_ptd NUMBER;
3992 l_basis_for_hol_pay_ytd NUMBER;
3993
3994 l_payroll_id NUMBER;
3995 l_payroll_name VARCHAR2(80);
3996 l_period_type VARCHAR2(80);
3997 --------------
3998 /*
3999 TYPE tax_card_rec IS RECORD (inp_val_name pay_input_values_f.NAME%type , screen_entry_val pay_input_values_f.NAME%type );
4000
4001 TYPE bal_val_rec IS RECORD ( bal_name ff_database_items.USER_NAME%type , bal_val NUMBER(10,2) );
4002
4003
4004 TYPE tax_card_table IS TABLE OF tax_card_rec INDEX BY BINARY_INTEGER;
4005
4006 TYPE bal_val_table IS TABLE OF bal_val_rec INDEX BY BINARY_INTEGER;
4007
4008
4009 g_tax_card_tab tax_card_table;
4010 g_bal_val bal_val_table;
4011
4012 */
4013
4014 -------------
4015
4016 BEGIN
4017
4018 -- fnd_file.put_line(fnd_file.log,'inside ARCHIVE_ADDL_EMP_DETAILS');
4019
4020 OPEN CSR_ACTUAL_TERM_DATE (p_assignment_id);
4021 FETCH CSR_ACTUAL_TERM_DATE INTO l_actual_termination_date;
4022 CLOSE CSR_ACTUAL_TERM_DATE;
4023
4024 -- fnd_file.put_line(fnd_file.log,'closed CSR_ACTUAL_TERM_DATE');
4025
4026 -- fnd_file.put_line(fnd_file.log,'before FOR g_tax_card_tab');
4027
4028 FOR l_index IN g_tax_card_tab.first.. g_tax_card_tab.last LOOP
4029
4030 OPEN get_details( p_assignment_id ,g_tax_card_tab(l_index).inp_val_name );
4031 FETCH get_details INTO l_rec;
4032 CLOSE get_details;
4033
4034 g_tax_card_tab(l_index).screen_entry_val := l_rec.screen_entry_value ;
4035
4036 END LOOP;
4037
4038 -- fnd_file.put_line(fnd_file.log,'end loop FOR g_tax_card_tab');
4039
4040 l_tax_card_effective_date := l_rec.effective_start_date;
4041
4042 ---------------------
4043
4044 -- fnd_file.put_line(fnd_file.log,'getting display values for Tax Card');
4045
4046 -- Getting the display value for Tax Card input values
4047 g_tax_card_tab(1).screen_entry_val := hr_general.decode_lookup('NO_METHOD_OF_RECEIPT',g_tax_card_tab(1).screen_entry_val);
4048 --
4049 OPEN get_code_meaning(g_tax_card_tab(2).screen_entry_val, l_tax_card_effective_date);
4050 FETCH get_code_meaning INTO g_tax_card_tab(2).screen_entry_val;
4051 CLOSE get_code_meaning;
4052 --g_tax_card_tab(2).screen_entry_val :=hr_general.decode_lookup('NO_TAX_MUNICIPALITY',g_tax_card_tab(2).screen_entry_val);
4053 --
4054 g_tax_card_tab(3).screen_entry_val := hr_general.decode_lookup('NO_TAX_CARD_TYPE',g_tax_card_tab(3).screen_entry_val);
4055 g_tax_card_tab(5).screen_entry_val := hr_general.decode_lookup('NO_TAX_TABLE_NO',g_tax_card_tab(5).screen_entry_val);
4056 g_tax_card_tab(6).screen_entry_val := hr_general.decode_lookup('NO_TAX_TABLE_TYPE',g_tax_card_tab(6).screen_entry_val);
4057 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
4058 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
4059
4060 -- calculating the Threshold Remaining
4061
4062 l_defined_balance_id := GET_DEFINED_BALANCE_ID('THRESHOLD_USED_PER_YTD');
4063 l_threshold_used_per_ytd := pay_balance_pkg.get_value(l_defined_balance_id, p_assignment_action_id);
4064 l_threshold_remaining := g_tax_card_tab(7).screen_entry_val - l_threshold_used_per_ytd ;
4065
4066
4067 -- fnd_file.put_line(fnd_file.log,'archiving ADDL EMPLOYEE DETAILS');
4068
4069 pay_action_information_api.create_action_information (
4070 p_action_information_id => l_action_info_id
4071 ,p_action_context_id => p_archive_assact_id
4072 ,p_action_context_type => 'AAP'
4073 ,p_object_version_number => l_ovn
4074 ,p_effective_date => p_effective_date
4075 ,p_source_id => NULL
4076 ,p_source_text => NULL
4077 ,p_action_information_category => 'ADDL EMPLOYEE DETAILS'
4078 ,p_action_information4 => g_tax_card_tab(1).screen_entry_val -- Method of Receipt
4079 ,p_action_information5 => g_tax_card_tab(2).screen_entry_val -- Tax Municipality
4080 ,p_action_information6 => g_tax_card_tab(3).screen_entry_val -- Tax Card Type
4081 ,p_action_information7 => g_tax_card_tab(4).screen_entry_val -- Tax Percentage
4082 ,p_action_information8 => g_tax_card_tab(5).screen_entry_val -- Tax Table Number
4083 ,p_action_information9 => g_tax_card_tab(6).screen_entry_val -- Tax Table Type
4084 ,p_action_information10 => g_tax_card_tab(7).screen_entry_val -- Tax Free Threshold
4085 ,p_action_information11 => g_tax_card_tab(8).screen_entry_val -- Registration Date
4086 ,p_action_information12 => g_tax_card_tab(9).screen_entry_val -- Date Returned
4087 ,p_action_information13 => l_threshold_remaining -- Threshold Remaining
4088 ,p_assignment_id => p_assignment_id );
4089
4090 -- fnd_file.put_line(fnd_file.log,'finished archiving ADDL EMPLOYEE DETAILS');
4091
4092 -------------------------------------------------------------------------------
4093
4094 -- fnd_file.put_line(fnd_file.log,'begin FOR g_bal_val');
4095
4096 -- fnd_file.put_line(fnd_file.log,'g_bal_val.first = '||to_char(g_bal_val.first));
4097 -- fnd_file.put_line(fnd_file.log,'g_bal_val.last = '||to_char(g_bal_val.last));
4098
4099
4100 FOR l_index IN g_bal_val.first.. g_bal_val.last LOOP
4101
4102 -- fnd_file.put_line(fnd_file.log,'l_index = '||to_char(l_index));
4103 -- fnd_file.put_line(fnd_file.log,'g_bal_val(l_index).bal_name = '||g_bal_val(l_index).bal_name);
4104
4105 l_defined_balance_id := GET_DEFINED_BALANCE_ID( g_bal_val(l_index).bal_name );
4106
4107 -- fnd_file.put_line(fnd_file.log,'l_defined_balance_id = '||to_char(l_defined_balance_id));
4108 -- fnd_file.put_line(fnd_file.log,'p_assignment_action_id = '||to_char(p_assignment_action_id));
4109
4110 g_bal_val(l_index).bal_val := pay_balance_pkg.get_value(l_defined_balance_id, p_assignment_action_id);
4111
4112 -- fnd_file.put_line(fnd_file.log,'g_bal_val(l_index).bal_val = '||to_char(g_bal_val(l_index).bal_val));
4113
4114 END LOOP;
4115
4116 -- fnd_file.put_line(fnd_file.log,'end loop FOR g_bal_val');
4117 -- fnd_file.put_line(fnd_file.log,'start asigning balance values');
4118
4119 l_net_salary_ptd := g_bal_val(1).bal_val ;
4120 l_net_salary_ytd := g_bal_val(2).bal_val ;
4121 l_table_tax_basis_ptd := g_bal_val(5).bal_val ;
4122 l_table_tax_basis_ytd := g_bal_val(6).bal_val ;
4123 l_percent_tax_basis_ptd := g_bal_val(7).bal_val ;
4124 l_percent_tax_basis_ytd := g_bal_val(8).bal_val ;
4125 l_tax_deduction_basis_ptd := l_table_tax_basis_ptd + l_percent_tax_basis_ptd ;
4126 l_tax_deduction_basis_ytd := l_table_tax_basis_ytd + l_percent_tax_basis_ytd ;
4127 l_table_tax_ptd := g_bal_val(9).bal_val ;
4128 l_table_tax_ytd := g_bal_val(10).bal_val ;
4129 l_percent_tax_ptd := g_bal_val(11).bal_val ;
4130 l_percent_tax_ytd := g_bal_val(12).bal_val ;
4131 l_withholding_tax_ptd := g_bal_val(3).bal_val ;
4132 l_withholding_tax_ytd := g_bal_val(4).bal_val ;
4133 l_hol_pay_due_this_yr_ytd := g_bal_val(13).bal_val + g_bal_val(14).bal_val ;
4134 l_basis_for_hol_pay_ptd := g_bal_val(15).bal_val ;
4135 l_basis_for_hol_pay_ytd := g_bal_val(16).bal_val ;
4136
4137 -- l_seaman_basis_ptd := g_bal_val(13).bal_val ;
4138 -- l_seaman_basis_ytd := g_bal_val(14).bal_val ;
4139
4140
4141
4142
4143
4144 -- fnd_file.put_line(fnd_file.log,'finish asigning balance values');
4145
4146 /* get the work title of the employee */
4147
4148 OPEN csr_work_title (p_assignment_id) ;
4149 FETCH csr_work_title INTO l_work_title ;
4150 CLOSE csr_work_title ;
4151
4152
4153 -- fnd_file.put_line(fnd_file.log,'after the select decode');
4154
4155 -- fnd_file.put_line(fnd_file.log,'starting archiving NO EMPLOYEE DETAILS');
4156
4157 pay_action_information_api.create_action_information (
4158 p_action_information_id => l_action_info_id
4159 ,p_action_context_id => p_archive_assact_id
4160 ,p_action_context_type => 'AAP'
4161 ,p_object_version_number => l_ovn
4162 ,p_effective_date => p_effective_date
4163 ,p_source_id => NULL
4164 ,p_source_text => NULL
4165 ,p_action_information_category => 'NO EMPLOYEE DETAILS'
4166 ,p_action_information1 => l_work_title -- Work Title
4167 ,p_action_information2 => l_net_salary_ptd -- Net Salary Period
4168 ,p_action_information3 => l_net_salary_ytd -- Net Salary Ytd
4169 ,p_action_information4 => l_withholding_tax_ptd -- Withholding Tax Period
4170 ,p_action_information5 => l_withholding_tax_ytd -- Withholding Tax Ytd
4171 ,p_action_information6 => l_tax_deduction_basis_ptd -- Tax Deduction Basis Period
4172 ,p_action_information7 => l_tax_deduction_basis_ytd -- Tax Deduction Basis Ytd
4173 ,p_action_information8 => l_table_tax_basis_ptd -- Table Based Tax Basis Period
4174 ,p_action_information9 => l_table_tax_basis_ytd -- Table Based Tax Basis Ytd
4175 ,p_action_information10 => l_table_tax_ptd -- Table Based Tax Period
4176 ,p_action_information11 => l_table_tax_ytd -- Table Based Tax Ytd
4177 ,p_action_information12 => l_percent_tax_basis_ptd -- Percentage Tax Basis Period
4178 ,p_action_information13 => l_percent_tax_basis_ytd -- Percentage Tax Basis Ytd
4179 ,p_action_information14 => l_percent_tax_ptd -- Percentage Based Tax Period
4180 ,p_action_information15 => l_percent_tax_ytd -- Percentage Based Tax Ytd
4181 -- ,p_action_information16 => l_seaman_basis_ptd -- Seaman Deduction Basis Period
4182 -- ,p_action_information17 => l_seaman_basis_ytd -- Seaman Deduction Basis Ytd
4183 ,p_action_information16 => l_hol_pay_due_this_yr_ytd -- Holiday Pay, due this year
4184 ,p_action_information17 => l_basis_for_hol_pay_ptd -- Basis for Holiday Pay Period
4185 ,p_action_information18 => l_basis_for_hol_pay_ytd -- Basis for Holiday Pay Ytd
4186 ,p_assignment_id => p_assignment_id);
4187
4188
4189 -- fnd_file.put_line(fnd_file.log,'finished archiving NO EMPLOYEE DETAILS');
4190 -- fnd_file.put_line(fnd_file.log,'leaving ARCHIVE_ADDL_EMP_DETAILS');
4191
4192
4193 END ARCHIVE_ADDL_EMP_DETAILS;
4194
4195 --------------------------------------- PROCEDURE ARCHIVE_MAIN_ELEMENTS ---------------------------------------------------------
4196
4197 /* ARCHIVE EARNINGS AND DEDUCTIONS ELEMENTS REGION */
4198
4199 PROCEDURE ARCHIVE_MAIN_ELEMENTS
4200 (p_archive_assact_id IN NUMBER,
4201 p_assignment_action_id IN NUMBER,
4202 p_assignment_id IN NUMBER,
4203 p_date_earned IN DATE,
4204 p_effective_date IN DATE,
4205 p_payroll_action_id IN NUMBER ) IS
4206
4207 ----------------
4208
4209 /* Cursor to retrieve Earnings Element Information */
4210 /*
4211 CURSOR csr_ear_element_info IS
4212 SELECT nvl(pettl.reporting_name,pettl.element_name) rep_name
4213 ,et.element_type_id element_type_id
4214 ,iv.input_value_id input_value_id
4215 ,iv.uom uom
4216 FROM pay_element_types_f et
4217 , pay_element_types_f_tl pettl
4218 , pay_input_values_f iv
4219 , pay_element_classifications classification
4220 WHERE et.element_type_id = iv.element_type_id
4221 AND et.element_type_id = pettl.element_type_id
4222 AND pettl.language = USERENV('LANG')
4223 AND iv.name = 'Pay Value'
4224 AND classification.classification_id = et.classification_id
4225 AND classification.classification_name
4226 IN ('Absence','Direct Payments','Earnings','Supplementary Earnings')
4227 AND p_date_earned BETWEEN et.effective_start_date AND et.effective_end_date
4228 AND p_date_earned BETWEEN iv.effective_start_date AND iv.effective_end_date
4229 AND ((et.business_group_id IS NULL AND et.legislation_code = 'NO')
4230 OR (et.business_group_id = g_business_group_id AND et.legislation_code IS NULL));
4231
4232 */
4233 ---------------
4234
4235 /* Cursor to retrieve Deduction Element Information */
4236
4237 /*
4238 CURSOR csr_ded_element_info IS
4239 SELECT nvl(pettl.reporting_name,pettl.element_name) rep_name
4240 ,et.element_type_id element_type_id
4241 ,iv.input_value_id input_value_id
4242 ,iv.uom uom
4243 FROM pay_element_types_f et
4244 , pay_element_types_f_tl pettl
4245 , pay_input_values_f iv
4246 , pay_element_classifications classification
4247 WHERE et.element_type_id = iv.element_type_id
4248 AND et.element_type_id = pettl.element_type_id
4249 AND pettl.language = USERENV('LANG')
4250 AND iv.name = 'Pay Value'
4251 AND classification.classification_id = et.classification_id
4252 AND classification.classification_name
4253 IN ('Involuntary Deductions','Pre-tax Deductions','Statutory Deductions','Voluntary Deductions')
4254 AND p_date_earned BETWEEN et.effective_start_date AND et.effective_end_date
4255 AND p_date_earned BETWEEN iv.effective_start_date AND iv.effective_end_date
4256 AND ((et.business_group_id IS NULL AND et.legislation_code = 'NO')
4257 OR (et.business_group_id = g_business_group_id AND et.legislation_code IS NULL));
4258
4259 */
4260
4261 ---------------
4262 /* Cursor to retrieve Main Element Information */
4263
4264 CURSOR csr_get_main_elements (p_payroll_action_id NUMBER ) IS
4265 SELECT action_information2 element_type_id
4266 ,action_information3 input_value_id
4267 ,action_information4 element_narrative
4268 ,action_information5 element_context
4269 ,action_information6 uom
4270 ,action_information8 prim_def_bal_id
4271 ,action_information12 ele_class
4272 FROM pay_action_information
4273 WHERE action_context_id = p_payroll_action_id
4274 AND action_information_category = 'NO ELEMENT DEFINITION'
4275 AND action_context_type = 'PA'
4276 AND action_information5 <> 'F';
4277
4278 ----------------------------------------------------------
4279
4280 /* cursor to get the tax unit id (Legal Employer) from assignment action id */
4281
4282 CURSOR csr_get_le_org_id (p_assignment_action_id NUMBER) IS
4283 SELECT tax_unit_id
4284 FROM pay_assignment_actions
4285 WHERE assignment_action_id = p_assignment_action_id ;
4286
4287 --------------------------------------------------------
4288
4289 /* cursor to get the element code */
4290
4291 /*
4292 CURSOR csr_ele_code (p_ele_type_id NUMBER , p_le_org_id NUMBER ) IS
4293 select eei_information1
4294 from pay_element_type_extra_info
4295 where element_type_id = p_ele_type_id
4296 and ( eei_information2 = p_le_org_id OR eei_information2 is null )
4297 and information_type = 'NO_ELEMENT_CODES'
4298 and eei_information_category = 'NO_ELEMENT_CODES'
4299 and rownum = 1
4300 order by eei_information2 , element_type_extra_info_id ;
4301 */
4302
4303 -- modifying the above cursor
4304
4305 cursor csr_ele_code(p_ele_type_id NUMBER , p_leg_emp_id NUMBER ) is
4306 select nvl((select eei_information1 from pay_element_type_extra_info petei
4307 where petei.information_type='NO_ELEMENT_CODES'
4308 and element_type_id = p_ele_type_id
4309 and petei.eei_information2 = p_leg_emp_id
4310 and rownum=1),
4311 (select eei_information1 from pay_element_type_extra_info petei
4312 where petei.information_type='NO_ELEMENT_CODES'
4313 and element_type_id = p_ele_type_id
4314 and eei_information2 is null
4315 and rownum=1)) from dual;
4316
4317 --------------------------------------------------------
4318
4319 /* cursor to get the further element entry info for payslip information */
4320
4321 CURSOR csr_payslip_info (p_ele_entry_id NUMBER) IS
4322 SELECT ENTRY_INFORMATION1
4323 FROM pay_element_entries_f
4324 where ELEMENT_ENTRY_ID = p_ele_entry_id ;
4325
4326 ----------------------------------
4327
4328 /* Cursor to retrieve run result value of Main Elements */
4329 CURSOR csr_result_value(p_iv_id NUMBER
4330 ,p_ele_type_id NUMBER
4331 ,p_assignment_action_id NUMBER) IS
4332 SELECT rrv.result_value val
4333 ,rr.element_entry_id ele_entry_id
4334 FROM pay_run_result_values rrv
4335 ,pay_run_results rr
4336 ,pay_assignment_actions paa
4337 ,pay_payroll_actions ppa
4338 WHERE rrv.input_value_id = p_iv_id
4339 AND rr.element_type_id = p_ele_type_id
4340 AND rr.run_result_id = rrv.run_result_id
4341 AND rr.assignment_action_id = paa.assignment_action_id
4342 AND paa.assignment_action_id = p_assignment_action_id
4343 AND ppa.payroll_action_id = paa.payroll_action_id
4344 AND ppa.action_type IN ('Q','R')
4345 AND rrv.result_value IS NOT NULL;
4346
4347 -----------------------------------------
4348
4349 /* cursor to get the element type id for 'Tax' element */
4350 /*
4351 CURSOR csr_get_tax_element IS
4352 SELECT element_type_id
4353 FROM pay_element_types_f
4354 WHERE element_name = 'Tax'
4355 AND legislation_code = 'NO'
4356 AND business_group_id IS NULL
4357 AND p_date_earned BETWEEN effective_start_date AND effective_end_date ;
4358 */
4359
4360 -----------------------------------------
4361
4362 -- cursor to get the name of the element
4363
4364 cursor csr_element_name (p_element_type_id NUMBER) IS
4365 select element_name
4366 from pay_element_types_f
4367 where element_type_id = p_element_type_id
4368 and p_date_earned between effective_start_date and effective_end_date ;
4369
4370 -----------------------------------------
4371
4372 -- cursor to get the input value id
4373
4374 cursor csr_inp_val_id (p_element_type_id NUMBER , p_inp_val_name pay_input_values_f.name%TYPE ) IS
4375 select input_value_id
4376 from pay_input_values_f
4377 where element_type_id = p_element_type_id
4378 and name = p_inp_val_name
4379 and business_group_id is null
4380 and legislation_code = 'NO'
4381 and p_date_earned between effective_start_date and effective_end_date ;
4382
4383 -----------------------------------------
4384
4385 /* Cursor to retrieve Business Group Id */
4386 CURSOR csr_bus_grp_id(p_organization_id NUMBER) IS
4387 SELECT business_group_id
4388 FROM hr_organization_units
4389 WHERE organization_id = p_organization_id;
4390
4391 -----------------------------------------
4392
4393 -- Cursor to get the 'Tax Period Override Element' details
4394 CURSOR csr_tax_period_override (p_assignment_id NUMBER, p_input_value VARCHAR2) IS
4395 SELECT eev1.screen_entry_value screen_entry_value
4396 FROM per_all_assignments_f asg1
4397 ,pay_element_links_f el
4398 ,pay_element_types_f et
4399 ,pay_input_values_f iv1
4400 ,pay_element_entries_f ee
4401 ,pay_element_entry_values_f eev1
4402 WHERE asg1.assignment_id = p_assignment_id
4403 AND p_date_earned BETWEEN asg1.effective_start_date AND asg1.effective_end_date
4404 AND et.element_name = 'Tax Period Override Element'
4405 AND et.legislation_code = 'NO'
4406 AND iv1.element_type_id = et.element_type_id
4407 AND iv1.name = p_input_value
4408 AND el.element_type_id = et.element_type_id
4409 AND ee.assignment_id = asg1.assignment_id
4410 AND ee.element_link_id = el.element_link_id
4411 AND eev1.element_entry_id = ee.element_entry_id
4412 AND eev1.input_value_id = iv1.input_value_id
4413 AND p_date_earned BETWEEN ee.effective_start_date AND ee.effective_end_date
4414 AND p_date_earned BETWEEN eev1.effective_start_date AND eev1.effective_end_date ;
4415 ---------------
4416
4417 -- cursor to get the reduced tax rule for the period
4418 CURSOR csr_get_reduced_tax_rule (p_asg_act_id number) IS
4419 SELECT ptp.prd_information1
4420 FROM per_time_periods ptp , pay_payroll_actions ppa , pay_assignment_actions paa
4421 WHERE paa.ASSIGNMENT_ACTION_ID = p_asg_act_id
4422 AND ppa.payroll_action_id = paa.PAYROLL_ACTION_ID
4423 AND ppa.time_period_id = ptp.time_period_id ;
4424
4425 -------------------------------------
4426
4427 l_result_value pay_run_result_values.result_value%TYPE := 0;
4428 l_action_info_id NUMBER;
4429 l_ovn NUMBER;
4430 l_element_context VARCHAR2(10);
4431 l_index NUMBER := 0;
4432 l_formatted_value VARCHAR2(50) := NULL;
4433 l_flag NUMBER := 0;
4434 l_le_org_id NUMBER;
4435 l_ele_code VARCHAR2(240) := NULL ;
4436 l_ele_entry_id NUMBER;
4437 l_payslip_info varchar2(240);
4438 l_balance_value NUMBER := 0;
4439 l_archive VARCHAR2(2);
4440 l_tax_ele_typ_id NUMBER;
4441
4442 l_element_name varchar2(240);
4443 l_inp_val_id NUMBER;
4444 l_suppl_method varchar2(240);
4445 l_bg_id NUMBER;
4446 l_arhive_prim_bal VARCHAR2(5);
4447
4448 l_msg_txt VARCHAR2(240);
4449 l_msg_name VARCHAR2(240);
4450 l_earn_period VARCHAR2(240);
4451 l_earn_period_mul VARCHAR2(240);
4452 l_tax_per_override_msg_txt VARCHAR2(240);
4453 l_reduced_tax_rule VARCHAR2(240);
4454 l_reduced_tax_rule_msg_txt VARCHAR2(240);
4455 ----------------
4456
4457 BEGIN
4458
4459 IF g_debug THEN
4460 hr_utility.set_location(' Entering Procedure ARCHIVE_MAIN_ELEMENTS',320);
4461 END IF;
4462
4463 -- Archiving Earnings Elements
4464
4465 -- fnd_file.put_line(fnd_file.log,'started archiving NO Earnings Elements');
4466
4467 -- fnd_file.put_line(fnd_file.log,'LOC 0 => p_archive_assact_id = ' || p_archive_assact_id );
4468
4469 /*
4470 OPEN csr_get_tax_element;
4471 FETCH csr_get_tax_element INTO l_tax_ele_typ_id ;
4472 CLOSE csr_get_tax_element;
4473 */
4474
4475 l_le_org_id := NULL ;
4476
4477 /* get the legal employer */
4478 OPEN csr_get_le_org_id (p_assignment_action_id );
4479 FETCH csr_get_le_org_id INTO l_le_org_id ;
4480 CLOSE csr_get_le_org_id ;
4481
4482 -- fnd_file.put_line(fnd_file.log,'l_le_org_id = ' || l_le_org_id);
4483
4484
4485 -- get the business group id for the LE
4486 OPEN csr_bus_grp_id(l_le_org_id);
4487 FETCH csr_bus_grp_id INTO l_bg_id;
4488 CLOSE csr_bus_grp_id;
4489
4490
4491 FOR csr_rec IN csr_get_main_elements (p_payroll_action_id ) LOOP
4492
4493
4494 l_arhive_prim_bal := 'N' ;
4495 l_ele_code := NULL ;
4496
4497 /* get the element code from the legal employer */
4498 OPEN csr_ele_code (csr_rec.element_type_id , l_le_org_id );
4499 FETCH csr_ele_code INTO l_ele_code ;
4500 CLOSE csr_ele_code ;
4501
4502 /*
4503 IF (l_ele_code IS NOT NULL) THEN
4504 l_ele_code := hr_general.decode_lookup('NO_ELEMENT_CODES',l_ele_code);
4505 END IF;
4506 */
4507
4508 -- fnd_file.put_line(fnd_file.log,'l_ele_code = ' || l_ele_code);
4509
4510 -- get the name of the element
4511 OPEN csr_element_name (csr_rec.element_type_id) ;
4512 FETCH csr_element_name INTO l_element_name ;
4513 CLOSE csr_element_name ;
4514
4515
4516 IF ( l_element_name = 'Tax' ) THEN
4517
4518 -- fnd_file.put_line(fnd_file.log,'Found Tax Element' );
4519 -- start processing to populate message for Supplementary Run
4520
4521 -- get the input value id for the 'Supplementary Run Method' input value on Tax element
4522
4523 OPEN csr_inp_val_id ( csr_rec.element_type_id , 'Supplementary Run Method' ) ;
4524 FETCH csr_inp_val_id INTO l_inp_val_id ;
4525 CLOSE csr_inp_val_id ;
4526
4527 -- get the 'Supplementary Run Method' run result value
4528 OPEN csr_result_value(l_inp_val_id
4529 ,csr_rec.element_type_id
4530 ,p_assignment_action_id);
4531
4532 FETCH csr_result_value INTO l_result_value , l_ele_entry_id ;
4533 CLOSE csr_result_value ;
4534
4535 -- fnd_file.put_line(fnd_file.log,'l_result_value (for Supplementary Run) = ' ||l_result_value );
4536
4537 IF (l_result_value = 'PERIOD') THEN
4538 l_msg_name := 'PAY_376884_NO_SUP_RUN_PERIOD' ;
4539 ELSIF (l_result_value = 'PERCENTAGE') THEN
4540 l_msg_name := 'PAY_376885_NO_SUP_RUN_PERCENT' ;
4541 END IF;
4542
4543 IF ( (l_result_value IS NOT NULL) AND (l_msg_name IS NOT NULL) ) THEN
4544 -- set the message name and get the message text
4545 hr_utility.set_message (801, l_msg_name);
4546 l_msg_txt := hr_utility.get_message ;
4547
4548 -- fnd_file.put_line(fnd_file.log,'l_msg_txt (for Supplementary Run) = ' ||l_msg_txt );
4549
4550 /* Arcvhive the message */
4551 pay_action_information_api.create_action_information (
4552 p_action_information_id => l_action_info_id
4553 ,p_action_context_id => p_archive_assact_id
4554 ,p_action_context_type => 'AAP'
4555 ,p_object_version_number => l_ovn
4556 ,p_effective_date => p_effective_date
4557 ,p_source_id => NULL
4558 ,p_source_text => NULL
4559 ,p_action_information_category => 'EMPLOYEE OTHER INFORMATION'
4560 ,p_action_information1 => l_bg_id
4561 ,p_action_information2 => 'MESG' -- Message Context
4562 ,p_action_information3 => NULL
4563 ,p_action_information4 => NULL
4564 ,p_action_information5 => NULL
4565 ,p_action_information6 => l_msg_txt );
4566
4567 END IF; -- end if l_msg_name IS NOT NULL
4568
4569 -- end processing to populate message for Supplementary Run
4570
4571
4572 -- start processing to populate message for 'Tax Period Override Element'
4573
4574 IF ( (l_result_value IS NULL) OR (l_result_value <> 'PERCENTAGE') ) THEN
4575
4576
4577 -- fnd_file.put_line(fnd_file.log,'l_result_value <> PERCENTAGE ' );
4578
4579 OPEN csr_tax_period_override (p_assignment_id , 'Earnings Period' ) ;
4580 FETCH csr_tax_period_override INTO l_earn_period ;
4581 CLOSE csr_tax_period_override ;
4582
4583 OPEN csr_tax_period_override (p_assignment_id , 'Earnings Period Multiplier' ) ;
4584 FETCH csr_tax_period_override INTO l_earn_period_mul ;
4585 CLOSE csr_tax_period_override ;
4586
4587
4588 IF ( (l_earn_period IS NOT NULL) AND (l_earn_period_mul IS NOT NULL) ) THEN
4589
4590 l_earn_period := hr_general.decode_lookup('NO_PAYROLL_PERIOD',l_earn_period) ;
4591 hr_utility.set_message (801, 'PAY_376886_NO_TAX_PER_OVERRIDE' );
4592 hr_utility.set_message_token (801, 'EARN_PERIOD', l_earn_period);
4593 hr_utility.set_message_token (801, 'EARN_PER_MUL', l_earn_period_mul);
4594 l_tax_per_override_msg_txt := hr_utility.get_message ;
4595
4596 -- fnd_file.put_line(fnd_file.log,'l_earn_period = ' ||l_earn_period );
4597 -- fnd_file.put_line(fnd_file.log,'l_earn_period_mul = ' ||l_earn_period_mul );
4598 -- fnd_file.put_line(fnd_file.log,'l_tax_per_override_msg_txt = ' ||l_tax_per_override_msg_txt );
4599
4600 /* Arcvhive the message */
4601 pay_action_information_api.create_action_information (
4602 p_action_information_id => l_action_info_id
4603 ,p_action_context_id => p_archive_assact_id
4604 ,p_action_context_type => 'AAP'
4605 ,p_object_version_number => l_ovn
4606 ,p_effective_date => p_effective_date
4607 ,p_source_id => NULL
4608 ,p_source_text => NULL
4609 ,p_action_information_category => 'EMPLOYEE OTHER INFORMATION'
4610 ,p_action_information1 => l_bg_id
4611 ,p_action_information2 => 'MESG' -- Message Context
4612 ,p_action_information3 => NULL
4613 ,p_action_information4 => NULL
4614 ,p_action_information5 => NULL
4615 ,p_action_information6 => l_tax_per_override_msg_txt );
4616
4617 END IF;
4618 END IF; -- end if l_result_value <> 'PERCENTAGE'
4619
4620 -- end processing to populate message for 'Tax Period Override Element'
4621
4622
4623 -- start processing to populate message for Reduced Tax Rule
4624
4625 -- fnd_file.put_line(fnd_file.log,'before csr_get_reduced_tax_rule ' );
4626
4627 OPEN csr_get_reduced_tax_rule (p_assignment_action_id) ;
4628 FETCH csr_get_reduced_tax_rule INTO l_reduced_tax_rule ;
4629 CLOSE csr_get_reduced_tax_rule ;
4630
4631 -- fnd_file.put_line(fnd_file.log,'l_reduced_tax_rule = ' ||l_reduced_tax_rule );
4632
4633 IF (l_reduced_tax_rule IN ('H','Z')) THEN
4634 l_reduced_tax_rule := hr_general.decode_lookup('NO_TAX_PERIOD_RULES',l_reduced_tax_rule) ;
4635 hr_utility.set_message (801, 'PAY_376887_NO_REDUCED_TAX');
4636 hr_utility.set_message_token (801, 'REDUCED_TAX', l_reduced_tax_rule);
4637 l_reduced_tax_rule_msg_txt := hr_utility.get_message ;
4638
4639 -- fnd_file.put_line(fnd_file.log,'l_reduced_tax_rule = ' || l_reduced_tax_rule );
4640 -- fnd_file.put_line(fnd_file.log,'l_reduced_tax_rule_msg_txt = ' ||l_reduced_tax_rule_msg_txt );
4641
4642 /* Arcvhive the message */
4643 pay_action_information_api.create_action_information (
4644 p_action_information_id => l_action_info_id
4645 ,p_action_context_id => p_archive_assact_id
4646 ,p_action_context_type => 'AAP'
4647 ,p_object_version_number => l_ovn
4648 ,p_effective_date => p_effective_date
4649 ,p_source_id => NULL
4650 ,p_source_text => NULL
4651 ,p_action_information_category => 'EMPLOYEE OTHER INFORMATION'
4652 ,p_action_information1 => l_bg_id
4653 ,p_action_information2 => 'MESG' -- Message Context
4654 ,p_action_information3 => NULL
4655 ,p_action_information4 => NULL
4656 ,p_action_information5 => NULL
4657 ,p_action_information6 => l_reduced_tax_rule_msg_txt );
4658
4659 END IF;
4660
4661 -- end processing to populate message for Reduced Tax Rule
4662
4663 END IF; -- end if l_element_name = 'Tax'
4664
4665 -- end processing to populate message for Supplementary Run
4666
4667
4668 l_result_value := NULL;
4669 l_arhive_prim_bal := 'N' ;
4670
4671 BEGIN
4672 /*
4673
4674 OPEN csr_result_value(csr_rec.input_value_id ,csr_rec.element_type_id ,p_assignment_action_id);
4675 FETCH csr_result_value INTO l_result_value;
4676 CLOSE csr_result_value;
4677
4678 IF l_result_value is not null THEN
4679 pay_action_information_api.create_action_information (
4680 p_action_information_id => l_action_info_id
4681 ,p_action_context_id => p_archive_assact_id
4682 ,p_action_context_type => 'AAP'
4683 ,p_object_version_number => l_ovn
4684 ,p_effective_date => p_effective_date
4685 ,p_source_id => NULL
4686 ,p_source_text => NULL
4687 ,p_action_information_category => 'EMEA ELEMENT INFO'
4688 ,p_action_information1 => csr_rec.element_type_id
4689 ,p_action_information2 => csr_rec.input_value_id
4690 ,p_action_information3 => 'E'
4691 ,p_action_information4 => l_result_value --l_formatted_value
4692 ,p_action_information9 => 'Earning Element'
4693 ,p_assignment_id => p_assignment_id);
4694 END IF;
4695
4696 */
4697
4698 -- fnd_file.put_line(fnd_file.log,'------------------------------------------');
4699 -- fnd_file.put_line(fnd_file.log,'csr_rec.element_narrative = ' || csr_rec.element_narrative);
4700 -- fnd_file.put_line(fnd_file.log,'csr_rec.element_type_id = ' || csr_rec.element_type_id);
4701 -- fnd_file.put_line(fnd_file.log,'csr_rec.input_value_id = ' || csr_rec.input_value_id);
4702 -- fnd_file.put_line(fnd_file.log,'p_assignment_action_id = ' || p_assignment_action_id);
4703
4704
4705 l_result_value := NULL ;
4706 l_ele_entry_id := NULL ;
4707 l_balance_value := NULL ;
4708
4709 IF (csr_rec.prim_def_bal_id IS NOT NULL) THEN
4710
4711 -- get the primary balance ytd value for the element
4712
4713
4714 -- fnd_file.put_line(fnd_file.log,'------ loc 1 ---------------');
4715 -- fnd_file.put_line(fnd_file.log,'l_element_name = ' || l_element_name);
4716 -- fnd_file.put_line(fnd_file.log,'csr_rec.prim_def_bal_id = ' || csr_rec.prim_def_bal_id );
4717
4718
4719 l_balance_value := pay_balance_pkg.get_value(csr_rec.prim_def_bal_id , p_assignment_action_id );
4720
4721 /* Change for element classification name from 'Holiday Pay Earnings Adjustment' to 'Holiday Pay Earnings Adjust' */
4722
4723 -- IF csr_rec.ele_class in ('Earnings Adjustment' , 'Holiday Pay Earnings Adjustment') THEN
4724
4725 -- The following classifications have been obsoleted and will no longer be used.
4726 -- Holiday Pay Earnings Adjust => Holiday Pay Earnings Adjust Obsolete
4727 -- Commenting the code below.
4728
4729 -- IF csr_rec.ele_class in ('Earnings Adjustment' , 'Holiday Pay Earnings Adjust') THEN
4730 IF csr_rec.ele_class in ('Earnings Adjustment') THEN
4731
4732 -- l_balance_value := fnd_number.number_to_canonical (0 - fnd_number.canonical_to_number (l_balance_value) ) ;
4733 -- Bug Fix : 5909576, pay_balance_pkg.get_value already returns a value in NUMBER format.
4734 -- So we need not do a fnd_number.canonical_to_number on l_balance_value.
4735
4736 l_balance_value := (0 - l_balance_value ) ;
4737
4738 END IF;
4739
4740 -- fnd_file.put_line(fnd_file.log,'l_balance_value = ' || l_balance_value);
4741 -- fnd_file.put_line(fnd_file.log,'---------------------');
4742
4743
4744 END IF;
4745
4746
4747 -- 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);
4748
4749 /* get the element run result value */
4750 OPEN csr_result_value(csr_rec.input_value_id
4751 ,csr_rec.element_type_id
4752 ,p_assignment_action_id);
4753 LOOP
4754 FETCH csr_result_value INTO l_result_value , l_ele_entry_id ;
4755
4756 IF (csr_result_value%NOTFOUND) AND
4757 (csr_result_value%ROWCOUNT < 1) AND
4758 (csr_rec.prim_def_bal_id IS NOT NULL) AND
4759 (l_balance_value <> 0) THEN
4760
4761 l_arhive_prim_bal := 'Y' ;
4762 -- fnd_file.put_line(fnd_file.log,'LOC 2 => p_assignment_action_id , l_element_name = ' || p_assignment_action_id ||' , '||l_element_name);
4763 END IF ;
4764
4765 EXIT WHEN csr_result_value%NOTFOUND;
4766
4767 -- fnd_file.put_line(fnd_file.log,'LOC 3 => p_assignment_action_id , l_element_name = ' || p_assignment_action_id ||' , '||l_element_name);
4768
4769 -- l_balance_value := NULL ;
4770
4771
4772
4773 -- fnd_file.put_line(fnd_file.log,'l_result_value = ' || l_result_value);
4774 -- fnd_file.put_line(fnd_file.log,'l_ele_entry_id = ' || l_ele_entry_id);
4775 -- fnd_file.put_line(fnd_file.log,'before l_balance_value = ' || l_balance_value);
4776
4777 -- modifying the below condition to get the ytd value for primary balance
4778 -- only once in case of multiple element entries
4779
4780 -- IF (csr_rec.prim_def_bal_id IS NOT NULL) THEN
4781
4782 -- 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);
4783
4784 /*
4785 IF (csr_rec.prim_def_bal_id IS NOT NULL) THEN -- AND (csr_result_value%ROWCOUNT = 1) THEN
4786
4787 -- get the primary balance ytd value for the element
4788
4789
4790 fnd_file.put_line(fnd_file.log,'---------------------');
4791 fnd_file.put_line(fnd_file.log,'l_element_name = ' || l_element_name);
4792 fnd_file.put_line(fnd_file.log,'csr_rec.prim_def_bal_id = ' || csr_rec.prim_def_bal_id );
4793
4794
4795 l_balance_value := pay_balance_pkg.get_value(csr_rec.prim_def_bal_id , p_assignment_action_id );
4796
4797 fnd_file.put_line(fnd_file.log,'l_balance_value = ' || l_balance_value);
4798 fnd_file.put_line(fnd_file.log,'---------------------');
4799
4800
4801 END IF;
4802
4803 */
4804
4805 IF (csr_result_value%ROWCOUNT > 1) THEN
4806 l_balance_value := NULL ;
4807 END IF;
4808
4809 -- fnd_file.put_line(fnd_file.log,'csr_rec.prim_def_bal_id = ' || csr_rec.prim_def_bal_id);
4810 -- fnd_file.put_line(fnd_file.log,'csr_result_value%ROWCOUNT = ' || csr_result_value%ROWCOUNT);
4811 -- fnd_file.put_line(fnd_file.log,'after l_balance_value = ' || l_balance_value);
4812 -- fnd_file.put_line(fnd_file.log,'csr_rec.prim_def_bal_id = ' || csr_rec.prim_def_bal_id);
4813 -- fnd_file.put_line(fnd_file.log,'l_balance_value = ' || l_balance_value);
4814
4815 /* get the payslip information */
4816 l_payslip_info := NULL ;
4817
4818 OPEN csr_payslip_info (l_ele_entry_id );
4819 FETCH csr_payslip_info INTO l_payslip_info ;
4820 CLOSE csr_payslip_info ;
4821
4822 -- fnd_file.put_line(fnd_file.log,'l_payslip_info = ' || l_payslip_info);
4823
4824 -- if the element classification is 'Earnings Adjustment' or 'Holiday Pay Earnings Adjustment',
4825 -- then store the result as negative value
4826
4827 /* Change for element classification name from 'Holiday Pay Earnings Adjustment' to 'Holiday Pay Earnings Adjust' */
4828
4829 -- IF csr_rec.ele_class in ('Earnings Adjustment' , 'Holiday Pay Earnings Adjustment') THEN
4830
4831 -- The following classifications have been obsoleted and will no longer be used.
4832 -- Holiday Pay Earnings Adjust => Holiday Pay Earnings Adjust Obsolete
4833 -- Commenting the code below.
4834
4835 -- IF csr_rec.ele_class in ('Earnings Adjustment' , 'Holiday Pay Earnings Adjust') THEN
4836 IF csr_rec.ele_class in ('Earnings Adjustment') THEN
4837
4838 -- fnd_file.put_line(fnd_file.log,'before l_result_value = ' ||l_result_value );
4839 l_result_value := fnd_number.number_to_canonical (0 - fnd_number.canonical_to_number (l_result_value) ) ;
4840 -- fnd_file.put_line(fnd_file.log,'after l_result_value = ' ||l_result_value );
4841
4842 END IF;
4843
4844 IF (l_result_value is not null) THEN l_archive := 'Y';
4845 -- 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';
4846 -- ELSIF (csr_rec.prim_def_bal_id IS NOT NULL) AND ( l_balance_value <> 0 ) THEN l_archive := 'Y';
4847 ELSE l_archive := 'N';
4848 END IF;
4849
4850
4851 IF (l_archive = 'Y') THEN
4852
4853 -- fnd_file.put_line(fnd_file.log,'l_result_value is not null ');
4854
4855 pay_action_information_api.create_action_information (
4856 p_action_information_id => l_action_info_id
4857 ,p_action_context_id => p_archive_assact_id
4858 ,p_action_context_type => 'AAP'
4859 ,p_object_version_number => l_ovn
4860 ,p_effective_date => p_effective_date
4861 ,p_source_id => NULL
4862 ,p_source_text => NULL
4863 ,p_action_information_category => 'NO ELEMENT INFO'
4864 ,p_action_information1 => csr_rec.element_type_id
4865 ,p_action_information2 => csr_rec.input_value_id
4866 ,p_action_information3 => csr_rec.element_context
4867 ,p_action_information4 => l_result_value --l_formatted_value
4868 ,p_action_information9 => 'Main Element'
4869 ,p_action_information10 => csr_rec.prim_def_bal_id
4870 ,p_action_information11 => 'PBAL'
4871 ,p_action_information12 => l_balance_value
4872 ,p_action_information13 => l_ele_code
4873 ,p_action_information14 => l_payslip_info
4874 ,p_assignment_id => p_assignment_id);
4875
4876 -- fnd_file.put_line(fnd_file.log,'******** archived = '||csr_rec.element_narrative );
4877
4878 END IF;
4879
4880 END LOOP;
4881 CLOSE csr_result_value;
4882
4883 -- archive results if run result was NULL but primary balance value is not null
4884
4885 IF (l_arhive_prim_bal = 'Y') THEN
4886
4887 -- though the run result is NULL, the ytd balance has a value
4888 -- so we will archive this result
4889
4890 l_result_value := NULL ;
4891 l_payslip_info := NULL ;
4892
4893 -- fnd_file.put_line(fnd_file.log,'LOC 5 => p_assignment_action_id , l_element_name = ' || p_assignment_action_id ||' , '||l_element_name);
4894
4895 pay_action_information_api.create_action_information (
4896 p_action_information_id => l_action_info_id
4897 ,p_action_context_id => p_archive_assact_id
4898 ,p_action_context_type => 'AAP'
4899 ,p_object_version_number => l_ovn
4900 ,p_effective_date => p_effective_date
4901 ,p_source_id => NULL
4902 ,p_source_text => NULL
4903 ,p_action_information_category => 'NO ELEMENT INFO'
4904 ,p_action_information1 => csr_rec.element_type_id
4905 ,p_action_information2 => csr_rec.input_value_id
4906 ,p_action_information3 => csr_rec.element_context
4907 ,p_action_information4 => l_result_value --l_formatted_value
4908 ,p_action_information9 => 'Main Element'
4909 ,p_action_information10 => csr_rec.prim_def_bal_id
4910 ,p_action_information11 => 'PBAL'
4911 ,p_action_information12 => l_balance_value
4912 ,p_action_information13 => l_ele_code
4913 ,p_action_information14 => l_payslip_info
4914 ,p_assignment_id => p_assignment_id);
4915
4916 END IF ;
4917
4918
4919 EXCEPTION WHEN OTHERS THEN
4920 g_err_num := SQLCODE;
4921 /*fnd_file.put_line(fnd_file.log,'ORA_ERR: ' || g_err_num || 'In ARCHIVE_MAIN_ELEMENTS');*/
4922
4923 IF g_debug THEN
4924 hr_utility.set_location('ORA_ERR: ' || g_err_num || 'In ARCHIVE_MAIN_ELEMENTS',330);
4925 END IF;
4926 END;
4927 END LOOP;
4928
4929 -- fnd_file.put_line(fnd_file.log,'finished archiving NO Earnings Elements');
4930
4931 IF g_debug THEN
4932 hr_utility.set_location(' Leaving Procedure ARCHIVE_MAIN_ELEMENTS',340);
4933 END IF;
4934
4935 END ARCHIVE_MAIN_ELEMENTS;
4936
4937 ------------------------------------ End of package ----------------------------------------------------------------
4938
4939 END PAY_NO_PAYSLIP_ARCHIVE;