[Home] [Help]
PACKAGE BODY: APPS.PAY_SE_PAYSLIP_ARCHIVE
Source
1 PACKAGE BODY PAY_SE_PAYSLIP_ARCHIVE AS
2 /* $Header: pysepysa.pkb 120.1.12010000.2 2008/08/06 08:18:15 ubhat ship $ */
3 g_debug boolean := hr_utility.debug_enabled;
4 TYPE element_rec IS RECORD (
5 classification_name VARCHAR2(60)
6 ,element_name VARCHAR2(60)
7 ,element_type_id NUMBER
8 ,input_value_id NUMBER
9 ,element_type VARCHAR2(1)
10 ,uom VARCHAR2(1)
11 ,archive_flag VARCHAR2(1)
12 );
13 TYPE balance_rec IS RECORD (
14 balance_name VARCHAR2(60),
15 defined_balance_id NUMBER,
16 balance_type_id NUMBER
17 );
18 TYPE lock_rec IS RECORD (
19 archive_assact_id NUMBER
20 );
21 TYPE element_table IS TABLE OF element_rec INDEX BY BINARY_INTEGER;
22 TYPE balance_table IS TABLE OF balance_rec INDEX BY BINARY_INTEGER;
23 TYPE lock_table IS TABLE OF lock_rec INDEX BY BINARY_INTEGER;
24
25 g_element_table element_table;
26 g_user_balance_table balance_table;
27 g_lock_table lock_table;
28 g_index NUMBER := -1;
29 g_index_assact NUMBER := -1;
30 g_index_bal NUMBER := -1;
31 g_package VARCHAR2(33) := ' PAY_SE_PAYSLIP_ARCHIVE.';
32 g_payroll_action_id NUMBER;
33 g_arc_payroll_action_id NUMBER;
34 g_business_group_id NUMBER;
35 g_format_mask VARCHAR2(50);
36 g_err_num NUMBER;
37 g_errm VARCHAR2(150);
38
39 /* Forward declaration of ARCHIVE_MAIN_ELEMENTS */
40 PROCEDURE ARCHIVE_MAIN_ELEMENTS
41 (p_archive_assact_id IN NUMBER,
42 p_assignment_action_id IN NUMBER,
43 p_assignment_id IN NUMBER,
44 p_date_earned IN DATE,
45 p_effective_date IN DATE
46 );
47
48 /* GET PARAMETER */
49 FUNCTION GET_PARAMETER(
50 p_parameter_string IN VARCHAR2
51 ,p_token IN VARCHAR2
52 ,p_segment_number IN NUMBER default NULL ) RETURN VARCHAR2
53 IS
54 l_parameter pay_payroll_actions.legislative_parameters%TYPE:=NULL;
55 l_start_pos NUMBER;
56 l_delimiter VARCHAR2(1):=' ';
57 l_proc VARCHAR2(40):= g_package||' get parameter ';
58 BEGIN
59 --
60 IF g_debug THEN
61 hr_utility.set_location(' Entering Function GET_PARAMETER',10);
62 END IF;
63 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
64 --
65 IF l_start_pos = 0 THEN
66 l_delimiter := '|';
67 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
68 END IF;
69 IF l_start_pos <> 0 THEN
70 l_start_pos := l_start_pos + length(p_token||'=');
71 l_parameter := substr(p_parameter_string,
72 l_start_pos,
73 instr(p_parameter_string||' ',
74 l_delimiter,l_start_pos)
75 - l_start_pos);
76 IF p_segment_number IS NOT NULL THEN
77 l_parameter := ':'||l_parameter||':';
78 l_parameter := substr(l_parameter,
79 instr(l_parameter,':',1,p_segment_number)+1,
80 instr(l_parameter,':',1,p_segment_number+1) -1
81 - instr(l_parameter,':',1,p_segment_number));
82 END IF;
83 END IF;
84 --
85 RETURN l_parameter;
86 IF g_debug THEN
87 hr_utility.set_location(' Leaving Function GET_PARAMETER',20);
88 END IF;
89 END;
90 /* GET ALL PARAMETERS */
91
92 PROCEDURE GET_ALL_PARAMETERS(
93 p_payroll_action_id IN NUMBER
94 ,p_business_group_id OUT NOCOPY NUMBER
95 ,p_start_date OUT NOCOPY VARCHAR2
96 ,p_end_date OUT NOCOPY VARCHAR2
97 ,p_effective_date OUT NOCOPY DATE
98 ,p_payroll_id OUT NOCOPY VARCHAR2
99 ,p_consolidation_set OUT NOCOPY VARCHAR2) IS
100 --
101 CURSOR csr_parameter_info(p_payroll_action_id NUMBER) IS
102 SELECT PAY_SE_PAYSLIP_ARCHIVE.GET_PARAMETER(legislative_parameters,'PAYROLL_ID')
103 ,PAY_SE_PAYSLIP_ARCHIVE.GET_PARAMETER(legislative_parameters,'CONSOLIDATION_SET_ID')
104 ,PAY_SE_PAYSLIP_ARCHIVE.GET_PARAMETER(legislative_parameters,'START_DATE')
105 ,PAY_SE_PAYSLIP_ARCHIVE.GET_PARAMETER(legislative_parameters,'END_DATE')
106 ,effective_date
107 ,business_group_id
108 FROM pay_payroll_actions
109 WHERE payroll_action_id = p_payroll_action_id;
110 l_proc VARCHAR2(240):= g_package||' GET_ALL_PARAMETERS ';
111 --
112 BEGIN
113 OPEN csr_parameter_info (p_payroll_action_id);
114 FETCH csr_parameter_info INTO p_payroll_id
115 ,p_consolidation_set
116 ,p_start_date
117 ,p_end_date
118 ,p_effective_date
119 ,p_business_group_id;
120 CLOSE csr_parameter_info;
121 --
122 IF g_debug THEN
123 hr_utility.set_location(' Leaving Procedure GET_ALL_PARAMETERS',30);
124 END IF;
125 END GET_ALL_PARAMETERS;
126 /* RANGE CODE */
127 PROCEDURE RANGE_CODE (p_payroll_action_id IN NUMBER
128 ,p_sql OUT NOCOPY VARCHAR2)
129 IS
130 CURSOR csr_get_message(p_bus_grp_id NUMBER) IS
131 SELECT org_information6 message
132 FROM hr_organization_information
133 WHERE organization_id = p_bus_grp_id
134 AND org_information_context = 'Business Group:Payslip Info'
135 AND org_information1 = 'MESG';
136 -----------------------------------------------------------------
137 -- BALANCES
138 -----------------------------------------------------------------
139 /* Cursor to retrieve Other Balances Information */
140 CURSOR csr_get_balance(p_bus_grp_id NUMBER) IS
141 SELECT org_information4 balance_type_id
142 ,org_information5 balance_dim_id
143 ,org_information7 narrative
144 FROM hr_organization_information
145 WHERE organization_id = p_bus_grp_id
146 AND org_information_context = 'Business Group:Payslip Info'
147 AND org_information1 = 'BALANCE';
148 /* Cursor to fetch defined balance id */
149 CURSOR csr_def_balance(bal_type_id NUMBER, bal_dim_id NUMBER) IS
150 SELECT defined_balance_id
151 FROM pay_defined_balances
152 WHERE balance_type_id = bal_type_id
153 AND balance_dimension_id = bal_dim_id;
154 -----------------------------------------------------
155 --ELEMENTS
156 ----------------------------------------------------
157 /* Cursor to retrieve Time Period Information */
158 CURSOR csr_time_periods(p_run_payact_id NUMBER
159 ,p_payroll_id NUMBER) IS
160 SELECT ptp.end_date end_date,
161 ptp.start_date start_date,
162 ptp.period_name period_name,
163 ppf.payroll_name payroll_name
164 FROM per_time_periods ptp
165 ,pay_payroll_actions ppa
166 ,pay_payrolls_f ppf
167 WHERE ptp.payroll_id = ppa.payroll_id
168 AND ppa.payroll_action_id = p_run_payact_id
169 AND ppa.payroll_id = ppf.payroll_id
170 AND ppf.payroll_id = NVL(p_payroll_id , ppf.payroll_id)
171 AND ppa.date_earned BETWEEN ptp.start_date
172 AND ptp.end_date
173 AND ppa.date_earned BETWEEN ppf.effective_start_date
174 AND ppf.effective_end_date;
175 --------------------------------------------------------------
176 -- Additional Element
177 --------------------------------------------------------------
178 /* Cursor to retrieve Additional Element Information */
179 CURSOR csr_get_element(p_bus_grp_id NUMBER, p_date_earned DATE) IS
180 SELECT hoi.org_information2 element_type_id
181 ,hoi.org_information3 input_value_id
182 ,code.org_information2||','||hoi.org_information7 element_narrative
183 ,pec.classification_name
184 ,piv.uom
185 --,code.org_information2 element_code
186 FROM hr_organization_information hoi
187 ,hr_organization_information code
188 ,pay_element_classifications pec
189 ,pay_element_types_f pet
190 ,pay_input_values_f piv
191 WHERE hoi.organization_id = p_bus_grp_id
192 AND hoi.org_information_context = 'Business Group:Payslip Info'
193 AND hoi.org_information1 = 'ELEMENT'
194 AND hoi.org_information2 = pet.element_type_id
195 AND pec.classification_id = pet.classification_id
196 AND piv.input_value_id = hoi.org_information3
197 AND p_date_earned BETWEEN piv.effective_start_date AND piv.effective_end_date
198 and code.organization_id (+)= p_bus_grp_id
199 and code.org_information_context (+)='SE_SOE_ELEMENT_ADD_DETAILS'
200 and pet.element_type_id = code.org_information1(+);
201
202 rec_time_periods csr_time_periods%ROWTYPE;
203 rec_get_balance csr_get_balance%ROWTYPE;
204 rec_get_message csr_get_message%ROWTYPE;
205 rec_get_element csr_get_element%ROWTYPE;
206 l_action_info_id NUMBER;
207 l_ovn NUMBER;
208 l_business_group_id NUMBER;
209 l_start_date VARCHAR2(30);
210 l_end_date VARCHAR2(30);
211 l_effective_date DATE;
212 l_consolidation_set NUMBER;
213 l_defined_balance_id NUMBER := 0;
214 l_count NUMBER := 0;
215 l_prev_prepay NUMBER := 0;
216 l_canonical_start_date DATE;
217 l_canonical_end_date DATE;
218 l_payroll_id NUMBER;
219 l_prepay_action_id NUMBER;
220 l_actid NUMBER;
221 l_assignment_id NUMBER;
222 l_action_sequence NUMBER;
223 l_assact_id NUMBER;
224 l_pact_id NUMBER;
225 l_flag NUMBER := 0;
226 l_element_context VARCHAR2(5);
227 BEGIN
228 IF g_debug THEN
229 hr_utility.set_location(' Entering Procedure RANGE_CODE',40);
230 END IF;
231 --fnd_file.put_line(fnd_file.log,'Entering Procedure RANGE_CODE ');
232 PAY_SE_PAYSLIP_ARCHIVE.GET_ALL_PARAMETERS(p_payroll_action_id
233 ,l_business_group_id
234 ,l_start_date
235 ,l_end_date
236 ,l_effective_date
237 ,l_payroll_id
238 ,l_consolidation_set);
239 l_canonical_start_date := TO_DATE(l_start_date,'YYYY/MM/DD');
240 l_canonical_end_date := TO_DATE(l_end_date,'YYYY/MM/DD');
241 --fnd_file.put_line(fnd_file.log,' Before Get message');
242 OPEN csr_get_message(l_business_group_id);
243 LOOP
244 FETCH csr_get_message INTO rec_get_message;
245 EXIT WHEN csr_get_message%NOTFOUND;
246 pay_action_information_api.create_action_information (
247 p_action_information_id => l_action_info_id
248 ,p_action_context_id => p_payroll_action_id
249 ,p_action_context_type => 'PA'
250 ,p_object_version_number => l_ovn
251 ,p_effective_date => l_effective_date
252 ,p_source_id => NULL
253 ,p_source_text => NULL
254 ,p_action_information_category => 'EMPLOYEE OTHER INFORMATION'
255 ,p_action_information1 => l_business_group_id
256 ,p_action_information2 => 'MESG' -- Message Context
257 ,p_action_information3 => NULL
258 ,p_action_information4 => NULL
259 ,p_action_information5 => NULL
260 ,p_action_information6 => rec_get_message.message);
261
262 --fnd_file.put_line(fnd_file.log,' l_action_info_id '||l_action_info_id);
263 --fnd_file.put_line(fnd_file.log,' rec_get_message.message '||rec_get_message.message);
264
265
266 END LOOP;
267 CLOSE csr_get_message;
268 -------------------------------------------------------------------------------------
269 -- Initialize Balance Definitions
270 --fnd_file.put_line(fnd_file.log,' Before Get Balance');
271 -------------------------------------------------------------------------------------
272 OPEN csr_get_balance(l_business_group_id);
273 LOOP
274 FETCH csr_get_balance INTO rec_get_balance;
275 EXIT WHEN csr_get_balance%NOTFOUND;
276 OPEN csr_def_balance(rec_get_balance.balance_type_id,rec_get_balance.balance_dim_id);
277 FETCH csr_def_balance INTO l_defined_balance_id;
278 CLOSE csr_def_balance;
279 BEGIN
280 SELECT 1 INTO l_flag
281 FROM pay_action_information
282 WHERE action_information_category = 'EMEA BALANCE DEFINITION'
283 AND action_context_id = p_payroll_action_id
284 AND action_information2 = l_defined_balance_id
285 AND action_information6 = 'OBAL'
286 AND action_information4 = rec_get_balance.narrative;
287 EXCEPTION WHEN NO_DATA_FOUND THEN
288 pay_action_information_api.create_action_information (
289 p_action_information_id => l_action_info_id
290 ,p_action_context_id => p_payroll_action_id
291 ,p_action_context_type => 'PA'
292 ,p_object_version_number => l_ovn
293 ,p_effective_date => l_effective_date
294 ,p_source_id => NULL
295 ,p_source_text => NULL
296 ,p_action_information_category => 'EMEA BALANCE DEFINITION'
297 ,p_action_information1 => NULL
298 ,p_action_information2 => l_defined_balance_id
299 ,p_action_information4 => rec_get_balance.narrative
300 ,p_action_information6 => 'OBAL');
301
302 --fnd_file.put_line(fnd_file.log,' l_action_info_id '||l_action_info_id);
303 --fnd_file.put_line(fnd_file.log,' rec_get_balance.narrative '||rec_get_balance.narrative);
304 WHEN OTHERS THEN
305 NULL;
306 END;
307 END LOOP;
308 CLOSE csr_get_balance;
309 -----------------------------------------------------------------------------
310 --Initialize Element Definitions
311 --fnd_file.put_line(fnd_file.log,' Before Get Element');
312 -----------------------------------------------------------------------------
313 g_business_group_id := l_business_group_id;
314 ARCHIVE_ELEMENT_INFO(p_payroll_action_id => p_payroll_action_id
315 ,p_effective_date => l_effective_date
316 ,p_date_earned => l_canonical_end_date
317 ,p_pre_payact_id => NULL);
318 -----------------------------------------------------------------------------
319 --Archive Additional Element Definitions
320 --fnd_file.put_line(fnd_file.log,' Before Get Additional Element');
321 -----------------------------------------------------------------------------
322 l_element_context := 'F';
323 OPEN csr_get_element(l_business_group_id,l_canonical_end_date);
324 LOOP
325 FETCH csr_get_element INTO rec_get_element;
326 EXIT WHEN csr_get_element%NOTFOUND;
327 BEGIN
328 SELECT 1 INTO l_flag
329 FROM pay_action_information
330 WHERE action_context_id = p_payroll_action_id
331 AND action_information_category = 'EMEA ELEMENT DEFINITION'
332 AND action_information2 = rec_get_element.element_type_id
333 AND action_information3 = rec_get_element.input_value_id
334 AND action_information5 = l_element_context;
335 EXCEPTION WHEN NO_DATA_FOUND THEN
336 pay_action_information_api.create_action_information (
337 p_action_information_id => l_action_info_id
338 ,p_action_context_id => p_payroll_action_id
339 ,p_action_context_type => 'PA'
340 ,p_object_version_number => l_ovn
341 ,p_effective_date => l_effective_date
342 ,p_source_id => NULL
343 ,p_source_text => NULL
344 ,p_action_information_category => 'EMEA ELEMENT DEFINITION'
345 ,p_action_information1 => NULL
346 ,p_action_information2 => rec_get_element.element_type_id
347 ,p_action_information3 => rec_get_element.input_value_id
348 ,p_action_information4 => rec_get_element.element_narrative
349 ,p_action_information5 => l_element_context
350 ,p_action_information6 => rec_get_element.uom
351 ,p_action_information7 => l_element_context);
352
353 --fnd_file.put_line(fnd_file.log,' l_action_info_id '||l_action_info_id);
354 --fnd_file.put_line(fnd_file.log,' rec_get_element.element_narrative '||rec_get_element.element_narrative);
355 --fnd_file.put_line(fnd_file.log,' l_element_context '||l_element_context);
356
357 WHEN OTHERS THEN
358 NULL;
359 END;
360 END LOOP;
361 CLOSE csr_get_element;
362 p_sql := 'SELECT DISTINCT person_id
363 FROM per_people_f ppf
364 ,pay_payroll_actions ppa
365 WHERE ppa.payroll_action_id = :payroll_action_id
366 AND ppa.business_group_id = ppf.business_group_id
367 ORDER BY ppf.person_id';
368 --fnd_file.put_line(fnd_file.log,' Leaving Procedure RANGE_CODE');
369 IF g_debug THEN
370 hr_utility.set_location(' Leaving Procedure RANGE_CODE',50);
371 END IF;
372 EXCEPTION
373 WHEN OTHERS THEN
374 -- Return cursor that selects no rows
375 p_sql := 'select 1 from dual where to_char(:payroll_action_id) = dummy';
376 END RANGE_CODE;
377 /* ASSIGNMENT ACTION CODE */
378 PROCEDURE ASSIGNMENT_ACTION_CODE
379 (p_payroll_action_id IN NUMBER
380 ,p_start_person IN NUMBER
381 ,p_end_person IN NUMBER
382 ,p_chunk IN NUMBER)
383 IS
384 CURSOR csr_prepaid_assignments(p_payroll_action_id NUMBER,
385 p_start_person NUMBER,
386 p_end_person NUMBER,
387 p_payroll_id NUMBER,
388 p_consolidation_id NUMBER,
389 l_canonical_start_date DATE,
390 l_canonical_end_date DATE)
391 IS
392 SELECT act.assignment_id assignment_id,
393 act.assignment_action_id run_action_id,
394 act1.assignment_action_id prepaid_action_id
395 FROM pay_payroll_actions ppa,
396 pay_payroll_actions appa,
397 pay_payroll_actions appa2,
398 pay_assignment_actions act,
399 pay_assignment_actions act1,
400 pay_action_interlocks pai,
401 per_all_assignments_f as1
402 WHERE ppa.payroll_action_id = p_payroll_action_id
403 AND appa.consolidation_set_id = p_consolidation_id
404 AND appa.effective_date BETWEEN l_canonical_start_date
405 AND l_canonical_end_date
406 AND as1.person_id BETWEEN p_start_person
407 AND p_end_person
408 AND appa.action_type IN ('R','Q')
409 -- Payroll Run or Quickpay Run
410 AND act.payroll_action_id = appa.payroll_action_id
411 AND act.source_action_id IS NULL -- Master Action
412 AND as1.assignment_id = act.assignment_id
413 AND ppa.effective_date BETWEEN as1.effective_start_date
414 AND as1.effective_end_date
415 AND act.action_status = 'C' -- Completed
416 AND act.assignment_action_id = pai.locked_action_id
417 AND act1.assignment_action_id = pai.locking_action_id
418 AND act1.action_status = 'C' -- Completed
419 AND act1.payroll_action_id = appa2.payroll_action_id
420 AND appa2.action_type IN ('P','U')
421 AND appa2.effective_date BETWEEN l_canonical_start_date
422 AND l_canonical_end_date
423 -- Prepayments or Quickpay Prepayments
424 AND (as1.payroll_id = p_payroll_id OR p_payroll_id IS NULL)
425 AND NOT EXISTS (SELECT /* + ORDERED */ NULL
426 FROM pay_action_interlocks pai1,
427 pay_assignment_actions act2,
428 pay_payroll_actions appa3
429 WHERE pai1.locked_action_id = act.assignment_action_id
430 AND act2.assignment_action_id= pai1.locking_action_id
431 AND act2.payroll_action_id = appa3.payroll_action_id
432 AND appa3.action_type = 'X'
433 AND appa3.action_status = 'C'
434 AND appa3.report_type = 'SE_ARCHIVE')
435 AND NOT EXISTS ( SELECT /* + ORDERED */ NULL
436 FROM pay_action_interlocks pai1,
437 pay_assignment_actions act2,
438 pay_payroll_actions appa3
439 WHERE pai1.locked_action_id = act.assignment_action_id
440 AND act2.assignment_action_id= pai1.locking_action_id
441 AND act2.payroll_action_id = appa3.payroll_action_id
442 AND appa3.action_type = 'V'
443 AND appa3.action_status = 'C')
444 ORDER BY act.assignment_id;
445 l_count NUMBER := 0;
446 l_prev_prepay NUMBER := 0;
447 l_business_group_id NUMBER;
448 l_start_date VARCHAR2(20);
449 l_end_date VARCHAR2(20);
450 l_canonical_start_date DATE;
451 l_canonical_end_date DATE;
452 l_effective_date DATE;
453 l_payroll_id NUMBER;
454 l_consolidation_set 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_defined_balance_id NUMBER :=0;
463 l_action_info_id NUMBER;
464 l_ovn NUMBER;
465 BEGIN
466 IF g_debug THEN
467 hr_utility.set_location(' Entering Procedure ASSIGNMENT_ACTION_CODE',60);
468 END IF;
469 --fnd_file.put_line(fnd_file.log,'Entering Procedure ASSIGNMENT_ACTION_CODE ');
470 --fnd_file.put_line(fnd_file.log,'p_payroll_action_id === ' || p_payroll_action_id);
471
472 PAY_SE_PAYSLIP_ARCHIVE.GET_ALL_PARAMETERS(p_payroll_action_id
473 ,l_business_group_id
474 ,l_start_date
475 ,l_end_date
476 ,l_effective_date
477 ,l_payroll_id
478 ,l_consolidation_set);
479 l_canonical_start_date := TO_DATE(l_start_date,'YYYY/MM/DD');
480 l_canonical_end_date := TO_DATE(l_end_date,'YYYY/MM/DD');
481 l_prepay_action_id := 0;
482 FOR rec_prepaid_assignments IN csr_prepaid_assignments(p_payroll_action_id
483 ,p_start_person
484 ,p_end_person
485 ,l_payroll_id
486 ,l_consolidation_set
487 ,l_canonical_start_date
488 ,l_canonical_end_date) LOOP
489 IF l_prepay_action_id <> rec_prepaid_assignments.prepaid_action_id THEN
490 SELECT pay_assignment_actions_s.NEXTVAL
491 INTO l_actid
492 FROM dual;
493 --
494 g_index_assact := g_index_assact + 1;
495 g_lock_table(g_index_assact).archive_assact_id := l_actid; /* For Element archival */
496 -- Create the archive assignment action
497 hr_nonrun_asact.insact(l_actid
498 ,rec_prepaid_assignments.assignment_id
499 ,p_payroll_action_id
500 ,p_chunk
501 ,NULL);
502 -- Create archive to prepayment assignment action interlock
503 --
504 hr_nonrun_asact.insint(l_actid,rec_prepaid_assignments.prepaid_action_id);
505 END IF;
506 -- create archive to master assignment action interlock
507 hr_nonrun_asact.insint(l_actid,rec_prepaid_assignments.run_action_id);
508 l_prepay_action_id := rec_prepaid_assignments.prepaid_action_id;
509 END LOOP;
510 --fnd_file.put_line(fnd_file.log,'Leaving Procedure ASSIGNMENT_ACTION_CODE ');
511 IF g_debug THEN
512 hr_utility.set_location(' Leaving Procedure ASSIGNMENT_ACTION_CODE',70);
513 END IF;
514 END ASSIGNMENT_ACTION_CODE;
515 /* INITIALIZATION CODE */
516 PROCEDURE INITIALIZATION_CODE(p_payroll_action_id IN NUMBER)
517 IS
518 CURSOR csr_prepay_id IS
519 SELECT distinct prepay_payact.payroll_action_id prepay_payact_id
520 ,run_payact.date_earned date_earned
521 FROM pay_action_interlocks archive_intlck
522 ,pay_assignment_actions prepay_assact
523 ,pay_payroll_actions prepay_payact
524 ,pay_action_interlocks prepay_intlck
525 ,pay_assignment_actions run_assact
526 ,pay_payroll_actions run_payact
527 ,pay_assignment_actions archive_assact
528 WHERE archive_intlck.locking_action_id = archive_assact.assignment_action_id
529 and archive_assact.payroll_action_id = p_payroll_action_id
530 AND prepay_assact.assignment_action_id = archive_intlck.locked_action_id
531 AND prepay_payact.payroll_action_id = prepay_assact.payroll_action_id
532 AND prepay_payact.action_type IN ('U','P')
533 AND prepay_intlck.locking_action_id = prepay_assact.assignment_action_id
534 AND run_assact.assignment_action_id = prepay_intlck.locked_action_id
535 AND run_payact.payroll_action_id = run_assact.payroll_action_id
536 AND run_payact.action_type IN ('Q', 'R')
537 ORDER BY prepay_payact.payroll_action_id;
538 /* Cursor to retrieve Run Assignment Action Ids */
539 CURSOR csr_runact_id IS
540 SELECT distinct prepay_payact.payroll_action_id prepay_payact_id
541 ,run_payact.date_earned date_earned
542 ,run_payact.payroll_action_id run_payact_id
543 FROM pay_action_interlocks archive_intlck
544 ,pay_assignment_actions prepay_assact
545 ,pay_payroll_actions prepay_payact
546 ,pay_action_interlocks prepay_intlck
547 ,pay_assignment_actions run_assact
548 ,pay_payroll_actions run_payact
549 ,pay_assignment_actions archive_assact
550 WHERE archive_intlck.locking_action_id = archive_assact.assignment_action_id
551 and archive_assact.payroll_action_id = p_payroll_action_id
552 AND prepay_assact.assignment_action_id = archive_intlck.locked_action_id
553 AND prepay_payact.payroll_action_id = prepay_assact.payroll_action_id
554 AND prepay_payact.action_type IN ('U','P')
555 AND prepay_intlck.locking_action_id = prepay_assact.assignment_action_id
556 AND run_assact.assignment_action_id = prepay_intlck.locked_action_id
557 AND run_payact.payroll_action_id = run_assact.payroll_action_id
558 AND run_payact.action_type IN ('Q', 'R')
559 ORDER BY prepay_payact.payroll_action_id;
560 rec_prepay_id csr_prepay_id%ROWTYPE;
561 rec_runact_id csr_runact_id%ROWTYPE;
562 l_action_info_id NUMBER;
563 l_ovn NUMBER;
564 l_count NUMBER := 0;
565 l_business_group_id NUMBER;
566 l_start_date VARCHAR2(20);
567 l_end_date VARCHAR2(20);
568 l_effective_date DATE;
569 l_payroll_id NUMBER;
570 l_consolidation_set NUMBER;
571 l_prev_prepay NUMBER := 0;
572 BEGIN
573 IF g_debug THEN
574 hr_utility.set_location(' Entering Procedure INITIALIZATION_CODE',80);
575 END IF;
576
577 --fnd_file.put_line(fnd_file.log,'In INITIALIZATION_CODE 0');
578 GET_ALL_PARAMETERS(p_payroll_action_id
579 ,l_business_group_id
580 ,l_start_date
581 ,l_end_date
582 ,l_effective_date
583 ,l_payroll_id
584 ,l_consolidation_set);
585 g_arc_payroll_action_id := p_payroll_action_id;
586 g_business_group_id := l_business_group_id;
587 /* Archive Element Details */
588 OPEN csr_prepay_id;
589 LOOP
590 FETCH csr_prepay_id INTO rec_prepay_id;
591 EXIT WHEN csr_prepay_id%NOTFOUND;
592 ---------------------------------------------------------
593 --Initialize Global tables once every prepayment payroll
594 --action id and once every thread
595 ---------------------------------------------------------
596 IF (rec_prepay_id.prepay_payact_id <> l_prev_prepay) THEN
597 ARCHIVE_ADD_ELEMENT(p_archive_assact_id => NULL,
598 p_assignment_action_id => NULL,
599 p_assignment_id => NULL,
600 p_payroll_action_id => p_payroll_action_id,
601 p_date_earned => rec_prepay_id.date_earned,
602 p_effective_date => l_effective_date,
603 p_pre_payact_id => rec_prepay_id.prepay_payact_id,
604 p_archive_flag => 'N');
605 END IF;
606 l_prev_prepay := rec_prepay_id.prepay_payact_id;
607 END LOOP;
608 CLOSE csr_prepay_id;
609 /* Initialize Global tables for Balances */
610 --fnd_file.put_line(fnd_file.log,'Calling from init ARCHIVE_OTH_BALANCE');
611 ARCHIVE_OTH_BALANCE(p_archive_assact_id => NULL,
612 p_assignment_action_id => NULL,
613 p_assignment_id => NULL,
614 p_payroll_action_id => p_payroll_action_id,
615 p_record_count => NULL,
616 p_pre_payact_id => NULL, --rec_prepay_id.prepay_payact_id,
617 p_effective_date => l_effective_date,
618 p_date_earned => NULL,
619 p_archive_flag => 'N');
620
621 --fnd_file.put_line(fnd_file.log,'Leaving INITIALIZATION_CODE 0');
622 IF g_debug THEN
623 hr_utility.set_location(' Leaving Procedure INITIALIZATION_CODE',90);
624 END IF;
625 EXCEPTION WHEN OTHERS THEN
626 g_err_num := SQLCODE;
627 /*fnd_file.put_line(fnd_file.log,'ORA_ERR: ' || g_err_num || 'In INITIALIZATION_CODE');*/
628 IF g_debug THEN
629 hr_utility.set_location('ORA_ERR: ' || g_err_num || 'In INITIALIZATION_CODE',180);
630 END IF;
631 END INITIALIZATION_CODE;
632 PROCEDURE SETUP_ELEMENT_DEFINITIONS( p_classification_name IN VARCHAR2
633 ,p_element_name IN VARCHAR2
634 ,p_element_type_id IN NUMBER
635 ,p_input_value_id IN NUMBER
636 ,p_element_type IN VARCHAR2
637 ,p_uom IN VARCHAR2
638 --,p_Element_code IN VARCHAR2
639 ,p_archive_flag IN VARCHAR2)
640 IS
641 BEGIN
642 IF g_debug THEN
643 hr_utility.set_location(' Entering Procedure SETUP_ELEMENT_DEFINITIONS',100);
644 END IF;
645 g_index := g_index + 1;
646 /* Initialize global tables that hold Additional Element details */
647 g_element_table(g_index).classification_name := p_classification_name;
648 g_element_table(g_index).element_name := p_element_name;
649 g_element_table(g_index).element_type := p_element_type;
650 g_element_table(g_index).element_type_id := p_element_type_id;
651 g_element_table(g_index).input_value_id := p_input_value_id;
652 g_element_table(g_index).uom := p_uom;
653 --g_element_table(g_index).Element_code := p_uom;
654 g_element_table(g_index).archive_flag := p_archive_flag;
655 IF g_debug THEN
656 hr_utility.set_location(' Leaving Procedure SETUP_ELEMENT_DEFINITIONS',110);
657 END IF;
658 END SETUP_ELEMENT_DEFINITIONS;
659 PROCEDURE SETUP_BALANCE_DEFINITIONS(p_balance_name IN VARCHAR2
660 ,p_defined_balance_id IN NUMBER
661 ,p_balance_type_id IN NUMBER)
662 IS
663 BEGIN
664 IF g_debug THEN
665 hr_utility.set_location(' Entering Procedure SETUP_BALANCE_DEFINITIONS',120);
666 END IF;
667 g_index_bal := g_index_bal + 1;
668 /* Initialize global tables that hold Other Balances details */
669 g_user_balance_table(g_index_bal).balance_name := p_balance_name;
670 g_user_balance_table(g_index_bal).defined_balance_id := p_defined_balance_id;
671 g_user_balance_table(g_index_bal).balance_type_id := p_balance_type_id;
672 /*fnd_file.put_line(fnd_file.log,'SETUP_BALANCE_DEFINITIONS ' ||p_balance_name); */
673 IF g_debug THEN
674 hr_utility.set_location(' Leaving Procedure SETUP_BALANCE_DEFINITIONS',130);
675 END IF;
676 END SETUP_BALANCE_DEFINITIONS;
677 /* GET COUNTRY NAME FROM CODE */
678 FUNCTION GET_COUNTRY_NAME(p_territory_code VARCHAR2)
679 RETURN VARCHAR2
680 IS
681 CURSOR csr_get_territory_name(p_territory_code VARCHAR2) Is
682 SELECT territory_short_name
683 FROM fnd_territories_vl
684 WHERE territory_code = p_territory_code;
685 l_country fnd_territories_vl.territory_short_name%TYPE;
686 BEGIN
687 IF g_debug THEN
688 hr_utility.set_location(' Entering Function GET_COUNTRY_NAME',140);
689 END IF;
690 OPEN csr_get_territory_name(p_territory_code);
691 FETCH csr_get_territory_name into l_country;
692 CLOSE csr_get_territory_name;
693 RETURN l_country;
694 IF g_debug THEN
695 hr_utility.set_location(' Leaving Function GET_COUNTRY_NAME',150);
696 END IF;
697 END GET_COUNTRY_NAME;
698 /* EMPLOYEE DETAILS REGION */
699 PROCEDURE ARCHIVE_EMPLOYEE_DETAILS (p_archive_assact_id IN NUMBER
700 ,p_assignment_id IN NUMBER
701 ,p_assignment_action_id IN NUMBER
702 ,p_payroll_action_id IN NUMBER
703 ,p_time_period_id IN NUMBER
704 ,p_date_earned IN DATE
705 ,p_pay_date_earned IN DATE
706 ,p_effective_date IN DATE) IS
707 /* Cursor to retrieve person details about Employee */
708 CURSOR csr_person_details(p_assignment_id NUMBER) IS
709 SELECT ppf.person_id person_id,
710 ppf.full_name full_name,
711 ppf.national_identifier ni_number,
712 ppf.nationality nationality,
713 pps.date_start start_date,
714 ppf.employee_number emp_num,
715 ppf.first_name first_name,
716 ppf.last_name last_name,
717 ppf.title title,
718 paf.location_id loc_id,
719 paf.organization_id org_id,
720 paf.assignment_number assignment_num,
721 paf.job_id job_id,
722 paf.position_id pos_id,
723 paf.grade_id grade_id,
724 paf.business_group_id bus_grp_id
725 FROM per_assignments_f paf,
726 per_all_people_f ppf,
727 per_periods_of_service pps
728 WHERE paf.person_id = ppf.person_id
729 AND paf.assignment_id = p_assignment_id
730 AND pps.person_id = ppf.person_id
731 AND p_date_earned BETWEEN paf.effective_start_date
732 AND paf.effective_end_date
733 AND p_date_earned BETWEEN ppf.effective_start_date
734 AND ppf.effective_end_date;
735 /* Cursor to retrieve primary address of Employee */
736 CURSOR csr_primary_address(p_person_id NUMBER) IS
737 SELECT pa.person_id person_id,
738 pa.style style,
739 pa.address_type ad_type,
740 pa.country country,
741 pa.region_1 R1,
742 pa.region_2 R2,
743 pa.region_3 R3,
744 pa.town_or_city city,
745 pa.address_line1 AL1,
746 pa.address_line2 AL2,
747 pa.address_line3 AL3,
748 pa.postal_code postal_code
749 FROM per_addresses pa
750 WHERE pa.primary_flag = 'Y'
751 AND pa.person_id = p_person_id
752 AND p_effective_date BETWEEN pa.date_from
753 AND NVL(pa.date_to,to_date('31-12-4712','DD-MM-YYYY'));
754 /* Cursor to retrieve Employer's Address */
755 CURSOR csr_employer_address(p_organization_id NUMBER) IS
756 SELECT hla.style style
757 ,hla.country country
758 ,hla.address_line_1 AL1
759 ,hla.address_line_2 AL2
760 ,hla.address_line_3 AL3
761 ,hla.postal_code postal_code
762 FROM hr_locations_all hla
763 ,hr_organization_units hou
764 WHERE hou.organization_id = p_organization_id
765 AND hou.location_id = hla.location_id;
766 CURSOR csr_organization_address(p_organization_id NUMBER) IS
767 SELECT hla.style style
768 ,hla.address_line_1 AL1
769 ,hla.address_line_2 AL2
770 ,hla.address_line_3 AL3
771 ,hla.country country
772 ,hla.postal_code postal_code
773 FROM hr_locations_all hla,
774 hr_organization_units hoa
775 WHERE hla.location_id = hoa.location_id
776 AND hoa.organization_id = p_organization_id
777 AND p_effective_date BETWEEN hoa.date_from
778 AND NVL(hoa.date_to,to_date('31-12-4712','DD-MM-YYYY'));
779 /* Cursor to retrieve Business Group Id */
780 CURSOR csr_bus_grp_id(p_organization_id NUMBER) IS
781 SELECT business_group_id
782 FROM hr_organization_units
783 WHERE organization_id = p_organization_id;
784 /* Cursor to retrieve Currency */
785 CURSOR csr_currency(p_bg_id NUMBER) IS
786 SELECT org_information10
787 FROM hr_organization_information
788 WHERE organization_id = p_bg_id
789 AND org_information_context = 'Business Group Information';
790 l_bg_id NUMBER;
791 CURSOR csr_legal_employer (p_organization_id NUMBER) IS
792 SELECT hoi3.organization_id
793 FROM HR_ORGANIZATION_UNITS o1
794 , HR_ORGANIZATION_INFORMATION hoi1
795 , HR_ORGANIZATION_INFORMATION hoi2
796 , HR_ORGANIZATION_INFORMATION hoi3
797 WHERE o1.business_group_id =l_bg_id
798 AND hoi1.organization_id = o1.organization_id
799 AND hoi1.organization_id = p_organization_id
800 AND hoi1.org_information1 = 'SE_LOCAL_UNIT'
801 AND hoi1.org_information_context = 'CLASS'
802 AND o1.organization_id = hoi2.org_information1
803 AND hoi2.ORG_INFORMATION_CONTEXT='SE_LOCAL_UNITS'
804 AND hoi2.organization_id = hoi3.organization_id
805 AND hoi3.ORG_INFORMATION_CONTEXT='CLASS'
806 AND hoi3.org_information1 = 'HR_LEGAL_EMPLOYER';
807 /* Cursor to retrieve Grade of Employee */
808 CURSOR csr_grade(p_grade_id NUMBER) IS
809 SELECT pg.name
810 FROM per_grades pg
811 WHERE pg.grade_id = p_grade_id;
812 /* Cursor to retrieve Position of Employee */
813 CURSOR csr_position(p_position_id NUMBER) IS
814 SELECT pap.name
815 FROM per_all_positions pap
816 WHERE pap.position_id = p_position_id;
817 CURSOR csr_job (p_job_id NUMBER)IS
818 SELECT name
819 FROM per_jobs
820 WHERE job_id = p_job_id;
821 /* Cursor to retrieve Cost Center */
822 CURSOR csr_cost_center(p_assignment_id NUMBER) IS
823 SELECT concatenated_segments
824 FROM pay_cost_allocations_v
825 WHERE assignment_id=p_assignment_id
826 AND p_date_earned BETWEEN effective_start_date
827 AND effective_end_date;
828 /* Cursor to pick up Payroll Location */
829 CURSOR csr_pay_location(p_location_id NUMBER) IS
830 SELECT location_code location
831 FROM hr_locations_all
832 WHERE location_id = p_location_id;
833 /* Cursor to pick Hire Date*/
834 CURSOR csr_hire_date (p_assignment_id NUMBER) IS
835 SELECT trunc(date_start) date_start
836 FROM per_periods_of_service pps,
837 per_all_assignments_f paa
838 WHERE pps.period_of_service_id = paa.period_of_service_id
839 AND p_date_earned between paa.effective_start_date and paa.effective_end_date
840 AND paa.assignment_id = p_assignment_id;
841 /*Cursor to pick local unit*/
842 cursor csr_scl_details (p_assignment_id NUMBER) IS
843 SELECT segment2
844 from per_all_assignments_f paaf
845 ,HR_SOFT_CODING_KEYFLEX hsck
846 where paaf.assignment_id= p_assignment_id
847 and p_date_earned BETWEEN paaf.effective_start_date and paaf.effective_end_date
848 and paaf.SOFT_CODING_KEYFLEX_ID = hsck.SOFT_CODING_KEYFLEX_ID;
849 rec_person_details csr_person_details%ROWTYPE;
850 rec_primary_address csr_primary_address%ROWTYPE;
851 rec_employer_address csr_employer_address%ROWTYPE;
852 rec_org_address csr_organization_address%ROWTYPE;
853 l_nationality per_all_people_f.nationality%TYPE;
854 l_position per_all_positions.name%TYPE;
855 l_hire_date per_periods_of_service.date_start%TYPE;
856 l_grade per_grades.name%TYPE;
857 l_currency hr_organization_information.org_information10%TYPE;
858 l_organization hr_organization_units.name%TYPE;
859 l_pay_location hr_locations_all.address_line_1%TYPE;
860 l_postal_code VARCHAR2(80);
861 l_country VARCHAR2(30);
862 l_emp_postal_code VARCHAR2(80);
863 l_emp_country VARCHAR2(30);
864 l_org_city VARCHAR2(20);
865 l_org_country VARCHAR2(30);
866 l_action_info_id NUMBER;
867 l_ovn NUMBER;
868 l_person_id NUMBER;
869 l_employer_name hr_organization_units.name%TYPE;
870 l_local_unit_id hr_organization_units.organization_id%TYPE;
871 l_legal_employer_id hr_organization_units.organization_id%TYPE;
872 l_job PER_JOBS.NAME%TYPE;
873 l_org_struct_ver_id hr_organization_information.org_information1%TYPE;
874 l_top_org_id per_org_structure_elements.organization_id_parent%TYPE;
875 l_cost_center pay_cost_allocations_v.concatenated_segments%TYPE;
876 l_defined_balance_id NUMBER;
877 l_balance_value NUMBER;
878 l_formatted_value VARCHAR2(50) := NULL;
879 l_org_exists NUMBER :=0;
880 -- l_lower_base NUMBER :=0;
881 -- l_upper_base NUMBER :=0;
882 BEGIN
883 IF g_debug THEN
884 hr_utility.set_location(' Entering Procedure ARCHIVE_EMPLOYEE_DETAILS',160);
885 END IF;
886 --fnd_file.put_line(fnd_file.log,'Entering Procedure ARCHIVE_EMPLOYEE_DETAILS');
887 /* PERSON AND ADDRESS DETAILS */
888 OPEN csr_person_details(p_assignment_id);
889 FETCH csr_person_details INTO rec_person_details;
890 CLOSE csr_person_details;
891 OPEN csr_primary_address(rec_person_details.person_id);
892 FETCH csr_primary_address INTO rec_primary_address;
893 CLOSE csr_primary_address;
894 OPEN csr_organization_address(rec_person_details.org_id);
895 FETCH csr_organization_address INTO rec_org_address;
896 CLOSE csr_organization_address;
897 --fnd_file.put_line(fnd_file.log,'Entering Procedure ARCHIVE_EMPLOYEE_DETAILS 2');
898 /* GRADE AND POSITION */
899 /* Changed IF condition construct to fix Bug 3583862 */
900 IF(rec_person_details.pos_id IS NOT NULL) THEN
901 OPEN csr_position(rec_person_details.pos_id);
902 FETCH csr_position INTO l_position;
903 CLOSE csr_position;
904 END IF;
905 IF(rec_person_details.grade_id IS NOT NULL) THEN
906 OPEN csr_grade(rec_person_details.grade_id);
907 FETCH csr_grade INTO l_grade;
908 CLOSE csr_grade;
909 END IF;
910 --fnd_file.put_line(fnd_file.log,'Entering Procedure ARCHIVE_EMPLOYEE_DETAILS 3');
911 /* CURRENCY */
912 OPEN csr_bus_grp_id(rec_person_details.org_id);
913 FETCH csr_bus_grp_id INTO l_bg_id;
914 CLOSE csr_bus_grp_id;
915 OPEN csr_currency(l_bg_id);
916 FETCH csr_currency INTO l_currency;
917 CLOSE csr_currency;
918 g_format_mask := FND_CURRENCY.GET_FORMAT_MASK(l_currency,40);
919 /* COST CENTER */
920 OPEN csr_cost_center(p_assignment_id);
921 FETCH csr_cost_center INTO l_cost_center;
922 CLOSE csr_cost_center;
923 /* HIRE DATE */
924 OPEN csr_hire_date(p_assignment_id);
925 FETCH csr_hire_date INTO l_hire_date;
926 CLOSE csr_hire_date;
927 /*NATIONALITY*/
928 l_nationality := hr_general.decode_lookup('NATIONALITY',rec_person_details.nationality);
929 --fnd_file.put_line(fnd_file.log,'Entering Procedure ARCHIVE_EMPLOYEE_DETAILS 4');
930 /*Local Unit*/
931 OPEN csr_scl_details(p_assignment_id);
932 FETCH csr_scl_details INTO l_local_unit_id;
933 CLOSE csr_scl_details;
934 OPEN csr_legal_employer(l_local_unit_id);
935 FETCH csr_legal_employer INTO l_legal_employer_id;
936 CLOSE csr_legal_employer;
937 /*
938 OPEN csr_employer_address(l_legal_employer_id);
939 FETCH csr_employer_address INTO rec_employer_address;
940 CLOSE csr_employer_address;
941 */
942 IF(rec_person_details.loc_id IS NOT NULL) THEN
943 l_pay_location := NULL;
944 OPEN csr_pay_location(rec_person_details.loc_id);
945 FETCH csr_pay_location INTO l_pay_location;
946 CLOSE csr_pay_location;
947 ELSE
948 l_pay_location := NULL;
949 END IF;
950 IF(rec_person_details.job_id IS NOT NULL) THEN
951 OPEN csr_job(rec_person_details.job_id);
952 FETCH csr_job INTO l_job;
953 CLOSE csr_job;
954 ELSE
955 l_job := NULL;
956 END IF;
957 SELECT name INTO l_organization
958 FROM hr_organization_units
959 WHERE organization_id = rec_person_details.org_id;
960
961 SELECT name INTO l_employer_name
962 FROM hr_organization_units
963 WHERE organization_id = l_legal_employer_id;
964 --fnd_file.put_line(fnd_file.log,'Entering Procedure ARCHIVE_EMPLOYEE_DETAILS 5');
965 IF rec_primary_address.style = 'SE' THEN
966 l_postal_code := hr_general.decode_lookup('SE_POSTAL_CODE',rec_primary_address.postal_code);
967 ELSE
968 l_postal_code := rec_primary_address.postal_code;
969 END IF;
970 l_country:=PAY_SE_PAYSLIP_ARCHIVE.get_country_name(rec_primary_address.country);
971 /*
972 IF rec_employer_address.style = 'SE' THEN
973 l_emp_postal_code := hr_general.decode_lookup('SE_POSTAL_CODE',rec_employer_address.postal_code);
974 ELSE
975 l_emp_postal_code := rec_employer_address.postal_code;
976 END IF;
977 l_emp_country:=PAY_SE_PAYSLIP_ARCHIVE.get_country_name(rec_employer_address.country);
978 */
979 --fnd_file.put_line(fnd_file.log,'Entering Procedure ARCHIVE_EMPLOYEE_DETAILS 6');
980 /* INSERT PERSON DETAILS */
981
982 pay_action_information_api.create_action_information (
983 p_action_information_id => l_action_info_id
984 ,p_action_context_id => p_archive_assact_id
985 ,p_action_context_type => 'AAP'
986 ,p_object_version_number => l_ovn
987 ,p_effective_date => p_effective_date
988 ,p_source_id => NULL
989 ,p_source_text => NULL
990 ,p_action_information_category => 'EMPLOYEE DETAILS'
991 ,p_action_information1 => rec_person_details.full_name
992 ,p_action_information2 => l_legal_employer_id
993 ,p_action_information4 => rec_person_details.ni_number
994 ,p_action_information7 => l_grade
995 ,p_action_information10 => rec_person_details.emp_num
996 ,p_action_information12 => fnd_date.date_to_displaydate(l_hire_date)
997 ,p_action_information14 => rec_person_details.assignment_num -- Bug 6625393
998 ,p_action_information15 => l_organization
999 ,p_action_information16 => p_time_period_id
1000 ,p_action_information17 => l_job
1001 ,p_action_information18 => l_employer_name
1002 ,p_action_information19 => l_position
1003 ,p_action_information30 => l_pay_location
1004 ,p_assignment_id => p_assignment_id);
1005
1006 --fnd_file.put_line(fnd_file.log,' l_action_info_id =='||l_action_info_id);
1007 --fnd_file.put_line(fnd_file.log,' p_archive_assact_id =='||p_archive_assact_id);
1008 --fnd_file.put_line(fnd_file.log,' rec_person_details.full_name =='||rec_person_details.full_name);
1009 --fnd_file.put_line(fnd_file.log,' l_legal_employer_id =='||l_legal_employer_id);
1010 --fnd_file.put_line(fnd_file.log,' rec_person_details.ni_number =='||rec_person_details.ni_number);
1011 --fnd_file.put_line(fnd_file.log,' l_grade =='||l_grade);
1012 --fnd_file.put_line(fnd_file.log,' to_char(trunc(l_hire_date)) =='||to_char(trunc(l_hire_date)));
1013 --fnd_file.put_line(fnd_file.log,' l_organization =='||l_organization);
1014 --fnd_file.put_line(fnd_file.log,' l_job =='||l_job);
1015 --fnd_file.put_line(fnd_file.log,' l_employer_name =='||l_employer_name);
1016 --fnd_file.put_line(fnd_file.log,' l_position =='||l_position);
1017 --fnd_file.put_line(fnd_file.log,' l_pay_location =='||l_pay_location);
1018 --fnd_file.put_line(fnd_file.log,' p_assignment_id =='||p_assignment_id);
1019
1020 /* INSERT ADDRESS DETAILS */
1021
1022 IF rec_primary_address.AL1 IS NOT NULL THEN /* CHECK IF EMPLOYEE HAS BEEN GIVEN A PRIMARY ADDRESS */
1023
1024 pay_action_information_api.create_action_information (
1025 p_action_information_id => l_action_info_id
1026 ,p_action_context_id => p_archive_assact_id
1027 ,p_action_context_type => 'AAP'
1028 ,p_object_version_number => l_ovn
1029 ,p_effective_date => p_effective_date
1030 ,p_source_id => NULL
1031 ,p_source_text => NULL
1032 ,p_action_information_category => 'ADDRESS DETAILS'
1033 ,p_action_information1 => rec_primary_address.person_id
1034 ,p_action_information5 => rec_primary_address.AL1
1035 ,p_action_information6 => rec_primary_address.AL2
1036 ,p_action_information7 => rec_primary_address.AL3
1037 ,p_action_information12 => l_postal_code
1038 ,p_action_information13 => l_country
1039 ,p_action_information14 => 'Employee Address'
1040 ,p_assignment_id => p_assignment_id);
1041
1042 --fnd_file.put_line(fnd_file.log,' l_action_info_id =='||l_action_info_id);
1043 --fnd_file.put_line(fnd_file.log,' p_archive_assact_id =='||p_archive_assact_id);
1044 --fnd_file.put_line(fnd_file.log,' rec_primary_address.person_id =='||rec_primary_address.person_id);
1045 --fnd_file.put_line(fnd_file.log,' rec_primary_address.AL1 =='|| rec_primary_address.AL1);
1046 --fnd_file.put_line(fnd_file.log,' rec_primary_address.AL2 =='|| rec_primary_address.AL2);
1047 --fnd_file.put_line(fnd_file.log,' rec_primary_address.AL3 =='|| rec_primary_address.AL3);
1048 --fnd_file.put_line(fnd_file.log,' l_postal_code =='||l_postal_code);
1049 --fnd_file.put_line(fnd_file.log,' l_country =='||l_country);
1050 --fnd_file.put_line(fnd_file.log,' p_assignment_id =='||p_assignment_id);
1051
1052 ELSE
1053 /* INSERT EMPLOYER ADDRESS AS EMPLOYEE'S PRIMARY ADDRESS */
1054
1055 pay_action_information_api.create_action_information (
1056 p_action_information_id => l_action_info_id
1057 ,p_action_context_id => p_archive_assact_id
1058 ,p_action_context_type => 'AAP'
1059 ,p_object_version_number => l_ovn
1060 ,p_effective_date => p_effective_date
1061 ,p_source_id => NULL
1062 ,p_source_text => NULL
1063 ,p_action_information_category => 'ADDRESS DETAILS'
1064 ,p_action_information1 => rec_person_details.person_id
1065 ,p_action_information5 => NULL
1066 ,p_action_information6 => NULL
1067 ,p_action_information7 => NULL
1068 ,p_action_information8 => NULL
1069 ,p_action_information9 => NULL
1070 ,p_action_information10 => NULL
1071 ,p_action_information11 => NULL
1072 ,p_action_information12 => NULL
1073 ,p_action_information13 => NULL
1074 ,p_action_information14 => 'Employee Address'
1075 ,p_assignment_id => p_assignment_id);
1076 END IF;
1077 /*fnd_file.put_line(fnd_file.log,'Entering Procedure ARCHIVE_EMPLOYEE_DETAILS 9');*/
1078 /* INSERT EMPLOYER'S ADDRESS (ORGANIZATION ADDRESS)*/
1079 /*
1080 BEGIN
1081 l_org_exists := 0;
1082 SELECT 1
1083 INTO l_org_exists
1084 FROM pay_action_information
1085 WHERE action_context_id = p_payroll_action_id
1086 AND action_information1 = rec_person_details.org_id
1087 AND effective_date = p_effective_date
1088 AND action_information_category = 'ADDRESS DETAILS';
1089 EXCEPTION
1090 WHEN NO_DATA_FOUND THEN
1091 --fnd_file.put_line(fnd_file.log,'PA Employer Address'||p_archive_assact_id);
1092 pay_action_information_api.create_action_information (
1093 p_action_information_id => l_action_info_id
1094 ,p_action_context_id => p_payroll_action_id
1095 ,p_action_context_type => 'PA'
1096 ,p_object_version_number => l_ovn
1097 ,p_effective_date => p_effective_date
1098 ,p_source_id => NULL
1099 ,p_source_text => NULL
1100 ,p_action_information_category => 'ADDRESS DETAILS'
1101 ,p_action_information1 => l_legal_employer_id
1102 ,p_action_information5 => rec_employer_address.AL1
1103 ,p_action_information6 => rec_employer_address.AL2
1104 ,p_action_information7 => rec_employer_address.AL3
1105 ,p_action_information12 => l_emp_postal_code
1106 ,p_action_information13 => l_emp_country
1107 ,p_action_information14 => 'Employer Address');
1108 WHEN OTHERS THEN
1109 NULL;
1110 END;
1111 */
1112 /*fnd_file.put_line(fnd_file.log,'Entering Procedure ARCHIVE_EMPLOYEE_DETAILS 10');*/
1113 --
1114 IF g_debug THEN
1115 hr_utility.set_location(' Leaving Procedure ARCHIVE_EMPLOYEE_DETAILS',170);
1116 END IF;
1117 --
1118 EXCEPTION WHEN OTHERS THEN
1119 g_err_num := SQLCODE;
1120 /*fnd_file.put_line(fnd_file.log,'ORA_ERR: ' || g_err_num || 'In ARCHIVE_EMPLOYEE_DETAILS');*/
1121 IF g_debug THEN
1122 hr_utility.set_location('ORA_ERR: ' || g_err_num || 'In ARCHIVE_EMPLOYEE_DETAILS',180);
1123 END IF;
1124 END ARCHIVE_EMPLOYEE_DETAILS;
1125 /* EARNINGS REGION, DEDUCTIONS REGION */
1126 PROCEDURE ARCHIVE_ELEMENT_INFO(p_payroll_action_id IN NUMBER
1127 ,p_effective_date IN DATE
1128 ,p_date_earned IN DATE
1129 ,p_pre_payact_id IN NUMBER)
1130 IS
1131 /* Cursor to retrieve Earnings Element Information */
1132 /* Archive ELEMENT DEFINITION */
1133 CURSOR csr_ear_element_info IS
1134 SELECT code.org_information2||','||nvl(pettl.reporting_name,pettl.element_name) rep_name
1135 ,et.element_type_id element_type_id
1136 ,iv.input_value_id input_value_id
1137 ,iv.uom uom
1138 FROM pay_element_types_f et
1139 , pay_element_types_f_tl pettl
1140 , pay_input_values_f iv
1141 , pay_element_classifications classification
1142 ,hr_organization_information code
1143 WHERE et.element_type_id = iv.element_type_id
1144 AND et.element_type_id = pettl.element_type_id
1145 AND pettl.language = USERENV('LANG')
1146 AND iv.name = 'Pay Value'
1147 AND classification.classification_id = et.classification_id
1148 AND classification.classification_name IN ( 'Salary in Money'
1149 ,'Lumpsum'
1150 ,'Other Payments Subject to Tax'
1151 ,'Retrospective Payments'
1152 ,'Direct Payments'
1153 )
1154 AND p_date_earned BETWEEN et.effective_start_date
1155 AND et.effective_end_date
1156 AND p_date_earned BETWEEN iv.effective_start_date
1157 AND iv.effective_end_date
1158 AND ((et.business_group_id IS NULL AND et.legislation_code = 'SE')
1159 OR (et.business_group_id = g_business_group_id AND et.legislation_code IS NULL))
1160 and code.organization_id(+) = g_business_group_id
1161 and code.org_information_context (+)='SE_SOE_ELEMENT_ADD_DETAILS'
1162 and et.element_type_id = code.org_information1 (+);
1163 /*SELECT nvl(pettl.reporting_name,pettl.element_name) rep_name
1164 ,et.element_type_id element_type_id
1165 ,iv.input_value_id input_value_id
1166 ,iv.uom uom
1167 FROM pay_element_types_f et
1168 , pay_element_types_f_tl pettl
1169 , pay_input_values_f iv
1170 , pay_element_classifications classification
1171 WHERE et.element_type_id = iv.element_type_id
1172 AND et.element_type_id = pettl.element_type_id
1173 AND pettl.language = USERENV('LANG')
1174 AND iv.name = 'Pay Value'
1175 AND classification.classification_id = et.classification_id
1176 AND classification.classification_name IN
1177 ('Absence'
1178 ,'Salary in Money'
1179 ,'Lumpsum'
1180 ,'Benefits in Kind'
1181 ,'Taxable Expenses'
1182 ,'Other Payments Subject to Tax'
1183 ,'Retrospective Payments'
1184 ,'Direct Payments'
1185 ,'Employer Charges'
1186 ,'External Expenses')
1187 AND p_date_earned BETWEEN et.effective_start_date
1188 AND et.effective_end_date
1189 AND p_date_earned BETWEEN iv.effective_start_date
1190 AND iv.effective_end_date
1191 AND ((et.business_group_id IS NULL AND et.legislation_code = 'SE')
1192 OR (et.business_group_id = g_business_group_id AND et.legislation_code IS NULL));*/
1193 /* Cursor to retrieve Deduction Element Information */
1194 CURSOR csr_ded_element_info IS
1195 SELECT code.org_information2||','||nvl(pettl.reporting_name,pettl.element_name) rep_name
1196 ,et.element_type_id element_type_id
1197 ,iv.input_value_id input_value_id
1198 ,iv.uom uom
1199 FROM pay_element_types_f et
1200 , pay_element_types_f_tl pettl
1201 , pay_input_values_f iv
1202 , pay_element_classifications classification
1203 ,hr_organization_information code
1204 WHERE et.element_type_id = iv.element_type_id
1205 AND et.element_type_id = pettl.element_type_id
1206 AND pettl.language = USERENV('LANG')
1207 AND iv.name = 'Pay Value'
1208 AND classification.classification_id = et.classification_id
1209 AND classification.classification_name IN ('Involuntary Deductions'
1210 ,'Voluntary Deductions'
1211 ,'Statutory Deductions'
1212 ,'Pre-Tax Deductions')
1213 AND p_date_earned BETWEEN et.effective_start_date
1214 AND et.effective_end_date
1215 AND p_date_earned BETWEEN iv.effective_start_date
1216 AND iv.effective_end_date
1217 AND ((et.business_group_id IS NULL AND et.legislation_code = 'SE')
1218 OR (et.business_group_id = g_business_group_id AND et.legislation_code IS NULL))
1219 and code.organization_id(+) = g_business_group_id
1220 and code.org_information_context (+)='SE_SOE_ELEMENT_ADD_DETAILS'
1221 and et.element_type_id = code.org_information1 (+);
1222 /*SELECT nvl(pettl.reporting_name,pettl.element_name) rep_name
1223 ,et.element_type_id element_type_id
1224 ,iv.input_value_id input_value_id
1225 ,iv.uom uom
1226 FROM pay_element_types_f et
1227 , pay_element_types_f_tl pettl
1228 , pay_input_values_f iv
1229 , pay_element_classifications classification
1230 WHERE et.element_type_id = iv.element_type_id
1231 AND et.element_type_id = pettl.element_type_id
1232 AND pettl.language = USERENV('LANG')
1233 AND iv.name = 'Pay Value'
1234 AND classification.classification_id = et.classification_id
1235 AND classification.classification_name IN
1236 ('Pre-Tax Deductions'
1237 ,'Involuntary Deductions'
1238 ,'Voluntary Deductions'
1239 ,'Statutory Deductions')
1240 AND p_date_earned BETWEEN et.effective_start_date
1241 AND et.effective_end_date
1242 AND p_date_earned BETWEEN iv.effective_start_date
1243 AND iv.effective_end_date
1244 AND ((et.business_group_id IS NULL AND et.legislation_code = 'SE')
1245 OR (et.business_group_id = g_business_group_id AND et.legislation_code IS NULL));*/
1246 l_action_info_id NUMBER;
1247 l_ovn NUMBER;
1248 l_flag NUMBER := 0;
1249 BEGIN
1250 IF g_debug THEN
1251 hr_utility.set_location(' Entering Procedure ARCHIVE_ELEMENT_INFO',210);
1252 END IF;
1253 --fnd_file.put_line(fnd_file.log,'Entering ARCHIVE_ELEMENT_INFO');
1254 --fnd_file.put_line(fnd_file.log,' EARNINGS ELEMENT');
1255 /* EARNINGS ELEMENT */
1256 FOR rec_earnings IN csr_ear_element_info LOOP
1257 BEGIN
1258 SELECT 1 INTO l_flag
1259 FROM pay_action_information
1260 WHERE action_context_id = p_payroll_action_id
1261 AND action_information_category = 'EMEA ELEMENT DEFINITION'
1262 AND action_information2 = rec_earnings.element_type_id
1263 AND action_information3 = rec_earnings.input_value_id
1264 AND action_information5 = 'E';
1265 EXCEPTION WHEN NO_DATA_FOUND THEN
1266 pay_action_information_api.create_action_information (
1267 p_action_information_id => l_action_info_id
1268 ,p_action_context_id => p_payroll_action_id
1269 ,p_action_context_type => 'PA'
1270 ,p_object_version_number => l_ovn
1271 ,p_effective_date => p_effective_date
1272 ,p_source_id => NULL
1273 ,p_source_text => NULL
1274 ,p_action_information_category => 'EMEA ELEMENT DEFINITION'
1275 ,p_action_information1 => p_pre_payact_id
1276 ,p_action_information2 => rec_earnings.element_type_id
1277 ,p_action_information3 => rec_earnings.input_value_id
1278 ,p_action_information4 => rec_earnings.rep_name
1279 ,p_action_information5 => 'E'
1280 ,p_action_information6 => rec_earnings.uom
1281 ,p_action_information7 => 'E'); --Earnings Element Context
1282
1283 ----fnd_file.put_line(fnd_file.log,'l_action_info_id '||l_action_info_id);
1284 --fnd_file.put_line(fnd_file.log,'p_payroll_action_id '||p_payroll_action_id);
1285 --fnd_file.put_line(fnd_file.log,'p_pre_payact_id '||p_pre_payact_id);
1286 --fnd_file.put_line(fnd_file.log,'rec_earnings.element_type_id '||rec_earnings.element_type_id);
1287 --fnd_file.put_line(fnd_file.log,'rec_earnings.input_value_id '||rec_earnings.input_value_id);
1288 --fnd_file.put_line(fnd_file.log,'rec_earnings.rep_name '||rec_earnings.rep_name);
1289 --fnd_file.put_line(fnd_file.log,'rec_earnings.uom '||rec_earnings.uom);
1290 WHEN OTHERS THEN
1291 NULL;
1292 END;
1293 END LOOP;
1294 /* DEDUCTION ELEMENT */
1295 --fnd_file.put_line(fnd_file.log,' DEDUCTION ELEMENT ');
1296 FOR rec_deduction IN csr_ded_element_info LOOP
1297 BEGIN
1298 SELECT 1 INTO l_flag
1299 FROM pay_action_information
1300 WHERE action_context_id = p_payroll_action_id
1301 AND action_information_category = 'EMEA ELEMENT DEFINITION'
1302 AND action_information2 = rec_deduction.element_type_id
1303 AND action_information3 = rec_deduction.input_value_id
1304 AND action_information5 = 'D';
1305 EXCEPTION WHEN NO_DATA_FOUND THEN
1306 pay_action_information_api.create_action_information (
1307 p_action_information_id => l_action_info_id
1308 ,p_action_context_id => p_payroll_action_id
1309 ,p_action_context_type => 'PA'
1310 ,p_object_version_number => l_ovn
1311 ,p_effective_date => p_effective_date
1312 ,p_source_id => NULL
1313 ,p_source_text => NULL
1314 ,p_action_information_category => 'EMEA ELEMENT DEFINITION'
1315 ,p_action_information1 => p_pre_payact_id
1316 ,p_action_information2 => rec_deduction.element_type_id
1317 ,p_action_information3 => rec_deduction.input_value_id
1318 ,p_action_information4 => rec_deduction.rep_name
1319 ,p_action_information5 => 'D'
1320 ,p_action_information6 => rec_deduction.uom
1321 ,p_action_information7 => 'D'); --Deduction Element Context
1322
1323 --fnd_file.put_line(fnd_file.log,'l_action_info_id '||l_action_info_id);
1324 --fnd_file.put_line(fnd_file.log,'p_payroll_action_id '||p_payroll_action_id);
1325 --fnd_file.put_line(fnd_file.log,'p_pre_payact_id '||p_pre_payact_id);
1326 --fnd_file.put_line(fnd_file.log,'rec_deduction.element_type_id '||rec_deduction.element_type_id);
1327 --fnd_file.put_line(fnd_file.log,'rec_deduction.input_value_id '||rec_deduction.input_value_id);
1328 --fnd_file.put_line(fnd_file.log,'rec_deduction.rep_name '||rec_deduction.rep_name);
1329 --fnd_file.put_line(fnd_file.log,'rec_deduction.uom '||rec_deduction.uom);
1330
1331 /*WHEN OTHERS THEN
1332 NULL;*/
1333 END;
1334 END LOOP;
1335 IF g_debug THEN
1336 hr_utility.set_location(' Leaving Procedure ARCHIVE_ELEMENT_INFO',220);
1337 END IF;
1338 EXCEPTION WHEN OTHERS THEN
1339 g_err_num := SQLCODE;
1340 /*fnd_file.put_line(fnd_file.log,'ORA_ERR: ' || g_err_num || 'In ARCHIVE_ELEMENT_INFO');*/
1341 IF g_debug THEN
1342 hr_utility.set_location('ORA_ERR: ' || g_err_num || 'ARCHIVE_ELEMENT_INFO',230);
1343 END IF;
1344 END ARCHIVE_ELEMENT_INFO;
1345 /* GET DEFINED BALANCE ID */
1346 FUNCTION GET_DEFINED_BALANCE_ID(p_user_name IN VARCHAR2) RETURN NUMBER
1347 IS
1348 /* Cursor to retrieve Defined Balance Id */
1349 CURSOR csr_def_bal_id(p_user_name VARCHAR2) IS
1350 SELECT u.creator_id
1351 FROM ff_user_entities u,
1352 ff_database_items d
1353 WHERE d.user_name = p_user_name
1354 AND u.user_entity_id = d.user_entity_id
1355 AND (u.legislation_code = 'SE' )
1356 AND (u.business_group_id IS NULL )
1357 AND u.creator_type = 'B';
1358 l_defined_balance_id ff_user_entities.user_entity_id%TYPE;
1359 BEGIN
1360 IF g_debug THEN
1361 hr_utility.set_location(' Entering Function GET_DEFINED_BALANCE_ID',240);
1362 END IF;
1363 OPEN csr_def_bal_id(p_user_name);
1364 FETCH csr_def_bal_id INTO l_defined_balance_id;
1365 CLOSE csr_def_bal_id;
1366 RETURN l_defined_balance_id;
1367 IF g_debug THEN
1368 hr_utility.set_location(' Leaving Function GET_DEFINED_BALANCE_ID',250);
1369 END IF;
1370 END GET_DEFINED_BALANCE_ID;
1371 /* PAYMENT INFORMATION REGION */
1372 PROCEDURE ARCHIVE_PAYMENT_INFO(p_archive_assact_id IN NUMBER,
1373 p_prepay_assact_id IN NUMBER,
1374 p_assignment_id IN NUMBER,
1375 p_date_earned IN DATE,
1376 p_effective_date IN DATE)
1377 IS
1378 /* Cursor to fetch ppm and opm ids to check which payment method to archive */
1379 CURSOR csr_chk(p_prepay_assact_id NUMBER) IS
1380 SELECT personal_payment_method_id ppm_id,
1381 org_payment_method_id opm_id
1382 FROM pay_pre_payments
1383 WHERE assignment_action_id = p_prepay_assact_id;
1384 /* Cursor to check if bank details are attached with ppm */
1385 CURSOR csr_chk_bank(p_ppm_id NUMBER) IS
1386 SELECT ppm.external_account_id
1387 FROM pay_personal_payment_methods_f ppm
1388 WHERE ppm.personal_payment_method_id = p_ppm_id
1389 AND p_date_earned BETWEEN ppm.effective_start_date
1390 AND ppm.effective_end_date;
1391 /* Cursor to retrieve Organization Payment Method Information */
1392 CURSOR csr_get_org_pay(p_prepay_assact_id NUMBER, opm_id NUMBER) IS
1393 SELECT pop.org_payment_method_id opm_id,
1394 pop.org_payment_method_name opm_name,
1395 ppttl.payment_type_name pay_type,
1396 ppp.value value
1397 FROM pay_org_payment_methods_f pop,
1398 pay_assignment_actions paa,
1399 pay_payment_types ppt,
1400 pay_payment_types_tl ppttl,
1401 pay_pre_payments ppp
1402 WHERE paa.assignment_action_id = p_prepay_assact_id
1403 AND ppt.payment_type_id = pop.payment_type_id
1404 AND ppt.payment_type_id = ppttl.payment_type_id
1405 AND ppttl.language = userenv('LANG')
1406 AND ppp.org_payment_method_id = pop.org_payment_method_id
1407 AND pop.org_payment_method_id = opm_id
1408 AND ppp.assignment_action_id = paa.assignment_action_id
1409 AND p_date_earned BETWEEN pop.effective_start_date
1410 AND pop.effective_end_date;
1411 /* Cursor to retrieve Personal Payment Method Info*/
1412 CURSOR csr_get_pers_pay(p_prepay_assact_id NUMBER, ppm_id NUMBER) IS
1413 SELECT pea.segment1 name_id,
1414 pea.segment2 branch,
1415 pea.segment3 acct_num,
1416 ppm.org_payment_method_id opm_id,
1417 pop.external_account_id,
1418 pop.org_payment_method_name opm_name,
1419 ppm.personal_payment_method_id ppm_id,
1420 ppttl.payment_type_name pay_type,
1421 ppp.value value
1422 FROM pay_external_accounts pea,
1423 pay_org_payment_methods_f pop,
1424 pay_personal_payment_methods_f ppm,
1425 pay_assignment_actions paa,
1426 pay_payment_types ppt,
1427 pay_payment_types_tl ppttl,
1428 pay_pre_payments ppp
1429 WHERE pea.external_account_id = NVL(ppm.external_account_id,pop.external_account_id)
1430 AND paa.assignment_action_id = p_prepay_assact_id
1431 AND paa.assignment_id = ppm.assignment_id
1432 AND ppm.org_payment_method_id = pop.org_payment_method_id
1433 AND ppm.personal_payment_method_id = ppm_id
1434 AND ppt.payment_type_id = pop.payment_type_id
1435 AND ppt.payment_type_id = ppttl.payment_type_id
1436 AND ppttl.language = userenv('LANG')
1437 AND ppp.assignment_action_id = paa.assignment_action_id
1438 AND ppp.personal_payment_method_id = ppm.personal_payment_method_id
1439 AND p_date_earned BETWEEN pop.effective_start_date
1440 AND pop.effective_end_date
1441 AND p_date_earned BETWEEN ppm.effective_start_date
1442 AND ppm.effective_end_date;
1443 l_bank_name VARCHAR2(50);
1444 l_action_info_id NUMBER;
1445 l_ovn NUMBER;
1446 l_org NUMBER;
1447 l_pers VARCHAR2(40) := NULL;
1448 l_ext_acct NUMBER;
1449 rec_chk csr_chk%ROWTYPE;
1450 l_pay_value VARCHAR2(50) := NULL;
1451 BEGIN
1452 IF g_debug THEN
1453 hr_utility.set_location(' Entering Procedure ARCHIVE_PAYMENT_INFO',260);
1454 END IF;
1455
1456 OPEN csr_chk(p_prepay_assact_id);
1457 LOOP
1458 FETCH csr_chk INTO rec_chk;
1459 EXIT WHEN csr_chk%NOTFOUND;
1460
1461 IF rec_chk.ppm_id IS NOT NULL THEN
1462
1463 FOR rec_pers_pay IN csr_get_pers_pay(p_prepay_assact_id,rec_chk.ppm_id) LOOP
1464
1465 OPEN csr_chk_bank(rec_chk.ppm_id);
1466
1467 FETCH csr_chk_bank INTO l_ext_acct;
1468 CLOSE csr_chk_bank;
1469 l_pay_value := to_char (rec_pers_pay.value,g_format_mask);
1470 IF (l_ext_acct IS NOT NULL) THEN
1471
1472 --fnd_file.put_line(fnd_file.log,'In ARCHIVE_PAYMENT_INFO 2');
1473 l_bank_name := rec_pers_pay.name_id;
1474 pay_action_information_api.create_action_information (
1475 p_action_information_id => l_action_info_id
1476 ,p_action_context_id => p_archive_assact_id
1477 ,p_action_context_type => 'AAP'
1478 ,p_object_version_number => l_ovn
1479 ,p_effective_date => p_effective_date
1480 ,p_source_id => NULL
1481 ,p_source_text => NULL
1482 ,p_action_information_category => 'EMPLOYEE NET PAY DISTRIBUTION'
1483 ,p_action_information1 => rec_pers_pay.opm_id
1484 ,p_action_information2 => rec_pers_pay.ppm_id
1485 ,p_action_information5 => l_bank_name
1486 ,p_action_information6 => rec_pers_pay.branch
1487 ,p_action_information7 => rec_pers_pay.acct_num
1488 ,p_action_information8 => NULL
1489 ,p_action_information9 => NULL
1490 ,p_action_information10 => NULL
1491 ,p_action_information11 => NULL
1492 ,p_action_information12 => NULL
1493 ,p_action_information13 => NULL
1494 ,p_action_information14 => NULL
1495 ,p_action_information15 => NULL
1496 ,p_action_information16 => fnd_number.number_to_canonical(rec_pers_pay.value) --l_pay_value
1497 ,p_action_information17 => NULL
1498 ,p_action_information18 => rec_pers_pay.opm_name
1499 ,p_assignment_id => p_assignment_id);
1500 ELSE
1501 --fnd_file.put_line(fnd_file.log,'In ARCHIVE_PAYMENT_INFO 3');
1502
1503 pay_action_information_api.create_action_information (
1504 p_action_information_id => l_action_info_id
1505 ,p_action_context_id => p_archive_assact_id
1506 ,p_action_context_type => 'AAP'
1507 ,p_object_version_number => l_ovn
1508 ,p_effective_date => p_effective_date
1509 ,p_source_id => NULL
1510 ,p_source_text => NULL
1511 ,p_action_information_category => 'EMPLOYEE NET PAY DISTRIBUTION'
1512 ,p_action_information1 => rec_pers_pay.opm_id
1513 ,p_action_information2 => rec_pers_pay.ppm_id
1514 ,p_action_information5 => NULL
1515 ,p_action_information6 => NULL
1516 ,p_action_information7 => NULL
1517 ,p_action_information8 => NULL
1518 ,p_action_information9 => NULL
1519 ,p_action_information10 => NULL
1520 ,p_action_information11 => NULL
1521 ,p_action_information12 => NULL
1522 ,p_action_information13 => NULL
1523 ,p_action_information14 => NULL
1524 ,p_action_information15 => NULL
1525 ,p_action_information16 => fnd_number.number_to_canonical(rec_pers_pay.value) --l_pay_value
1526 ,p_action_information17 => NULL
1527 ,p_action_information18 => rec_pers_pay.opm_name
1528 ,p_assignment_id => p_assignment_id);
1529 END IF;
1530 END LOOP;
1531 --fnd_file.put_line(fnd_file.log,'In ARCHIVE_PAYMENT_INFO 4');
1532 END IF;
1533 IF (rec_chk.opm_id IS NOT NULL AND rec_chk.ppm_id IS NULL) THEN
1534 --fnd_file.put_line(fnd_file.log,'In ARCHIVE_PAYMENT_INFO 5');
1535
1536 FOR rec_org_pay IN csr_get_org_pay(p_prepay_assact_id,rec_chk.opm_id) LOOP
1537
1538 l_pay_value := to_char (rec_org_pay.value,g_format_mask);
1539 pay_action_information_api.create_action_information (
1540 p_action_information_id => l_action_info_id
1541 ,p_action_context_id => p_archive_assact_id
1542 ,p_action_context_type => 'AAP'
1543 ,p_object_version_number => l_ovn
1544 ,p_effective_date => p_effective_date
1545 ,p_source_id => NULL
1546 ,p_source_text => NULL
1547 ,p_action_information_category => 'EMPLOYEE NET PAY DISTRIBUTION'
1548 ,p_action_information1 => rec_org_pay.opm_id
1549 ,p_action_information2 => NULL
1550 ,p_action_information5 => NULL
1551 ,p_action_information6 => NULL
1552 ,p_action_information7 => NULL
1553 ,p_action_information8 => NULL
1554 ,p_action_information9 => NULL
1555 ,p_action_information10 => NULL
1556 ,p_action_information11 => NULL
1557 ,p_action_information12 => NULL
1558 ,p_action_information13 => NULL
1559 ,p_action_information14 => NULL
1560 ,p_action_information15 => NULL
1561 ,p_action_information16 => fnd_number.number_to_canonical(rec_org_pay.value) --l_pay_value
1562 ,p_action_information17 => NULL
1563 ,p_action_information18 => rec_org_pay.opm_name
1564 ,p_assignment_id => p_assignment_id);
1565 END LOOP;
1566 END IF;
1567 END LOOP;
1568 CLOSE csr_chk;
1569
1570 IF g_debug THEN
1571 hr_utility.set_location(' Leaving Procedure ARCHIVE_PAYMENT_INFO',270);
1572 END IF;
1573 EXCEPTION WHEN OTHERS THEN
1574 g_err_num := SQLCODE;
1575 /*fnd_file.put_line(fnd_file.log,'ORA_ERR: ' || g_err_num || 'In ARCHIVE_PAYMENT_INFO');*/
1576 IF g_debug THEN
1577 hr_utility.set_location('ORA_ERR: ' || g_err_num || 'In ARCHIVE_PAYMENT_INFO',280);
1578 END IF;
1579 END ARCHIVE_PAYMENT_INFO;
1580
1581 /* ADDITIONAL ELEMENTS REGION */
1582 PROCEDURE archive_add_element(p_archive_assact_id IN NUMBER,
1583 p_assignment_action_id IN NUMBER,
1584 p_assignment_id IN NUMBER,
1585 p_payroll_action_id IN NUMBER,
1586 p_date_earned IN DATE,
1587 p_effective_date IN DATE,
1588 p_pre_payact_id IN NUMBER,
1589 p_archive_flag IN VARCHAR2) IS
1590 /* Cursor to retrieve Additional Element Information */
1591 CURSOR csr_get_element(p_bus_grp_id NUMBER) IS
1592 SELECT hoi.org_information2 element_type_id
1593 ,hoi.org_information3 input_value_id
1594 ,hoi.org_information7 element_narrative
1595 ,pec.classification_name
1596 ,piv.uom
1597 FROM hr_organization_information hoi
1598 ,pay_element_classifications pec
1599 ,pay_element_types_f pet
1600 ,pay_input_values_f piv
1601 WHERE hoi.organization_id = p_bus_grp_id
1602 AND hoi.org_information_context = 'Business Group:Payslip Info'
1603 AND hoi.org_information1 = 'ELEMENT'
1604 AND hoi.org_information2 = pet.element_type_id
1605 AND pec.classification_id = pet.classification_id
1606 AND piv.input_value_id = hoi.org_information3
1607 AND p_date_earned BETWEEN piv.effective_start_date
1608 AND piv.effective_end_date;/*
1609 SELECT hoi.org_information2 element_type_id
1610 ,hoi.org_information3 input_value_id
1611 ,hoi.org_information7 element_narrative
1612 ,pec.classification_name
1613 ,piv.uom
1614 ,code.org_information2 element_code
1615 FROM hr_organization_information hoi
1616 ,pay_element_classifications pec
1617 ,pay_element_types_f pet
1618 ,pay_input_values_f piv
1619 ,hr_organization_information code
1620 WHERE hoi.organization_id = p_bus_grp_id
1621 AND hoi.org_information_context = 'Business Group:Payslip Info'
1622 AND hoi.org_information1 = 'ELEMENT'
1623 AND hoi.org_information2 = pet.element_type_id
1624 AND pec.classification_id = pet.classification_id
1625 AND piv.input_value_id = hoi.org_information3
1626 AND p_date_earned BETWEEN piv.effective_start_date
1627 AND piv.effective_end_date
1628 and code.organization_id(+) = 75235
1629 and code.org_information_context (+)='SE_SOE_ELEMENT_ADD_DETAILS'
1630 and pet.element_type_id = code.org_information1 (+);*/
1631 /* Cursor to retrieve run result value of Additional Elements */
1632 CURSOR csr_result_value(p_iv_id NUMBER
1633 ,p_ele_type_id NUMBER
1634 ,p_assignment_action_id NUMBER) IS
1635 SELECT rrv.result_value ,rr.element_entry_id
1636 FROM pay_run_result_values rrv
1637 ,pay_run_results rr
1638 ,pay_assignment_actions paa
1639 ,pay_payroll_actions ppa
1640 WHERE rrv.input_value_id = p_iv_id
1641 AND rr.element_type_id = p_ele_type_id
1642 AND rr.run_result_id = rrv.run_result_id
1643 AND rr.assignment_action_id = paa.assignment_action_id
1644 AND paa.assignment_action_id = p_assignment_action_id
1645 AND ppa.payroll_action_id = paa.payroll_action_id
1646 AND ppa.action_type IN ('Q','R')
1647 AND rrv.result_value IS NOT NULL;
1648 -----------------------------------------------------------------------------
1649 /* Cursor to retrieve run result value of Main Elements */
1650 CURSOR csr_result_value_EE(p_iv_id NUMBER
1651 ,p_ele_type_id NUMBER
1652 ,p_assignment_action_id NUMBER
1653 ,p_EE_ID NUMBER) IS
1654 SELECT rrv.result_value
1655 FROM pay_run_result_values rrv
1656 ,pay_run_results rr
1657 ,pay_assignment_actions paa
1658 ,pay_payroll_actions ppa
1659 WHERE rrv.input_value_id = p_iv_id
1660 AND rr.element_type_id = p_ele_type_id
1661 AND rr.run_result_id = rrv.run_result_id
1662 AND rr.assignment_action_id = paa.assignment_action_id
1663 AND paa.assignment_action_id = p_assignment_action_id
1664 AND ppa.payroll_action_id = paa.payroll_action_id
1665 AND ppa.action_type IN ('Q','R')
1666 AND rrv.result_value IS NOT NULL
1667 AND rr.element_entry_id = p_EE_ID;
1668 -----------------------------------------------------------------------------
1669 -- Cursor to pick up segment from SE_SOE_ELEMENT_ADD_DETAILS context Org Developer DF
1670 CURSOR csr_group_by( p_ele_type_id NUMBER ) IS
1671 select ORG_INFORMATION3,ORG_INFORMATION6,ORG_INFORMATION7,ORG_INFORMATION8,ORG_INFORMATION9,ORG_INFORMATION10
1672 from hr_organization_information code
1673 where code.organization_id = g_business_group_id
1674 and code.org_information_context ='SE_SOE_ELEMENT_ADD_DETAILS'
1675 and code.org_information1 =p_ele_type_id;
1676
1677 rec_group_by csr_group_by%ROWTYPE;
1678 -----------------------------------------------------------------------------
1679 /* Cursor to retrieve sum of run result value for an given Main Element */
1680 -----------------------------------------------------------------------------
1681 CURSOR csr_sum_of_result_values(p_iv_id NUMBER
1682 ,p_ele_type_id NUMBER
1683 ,p_assignment_action_id NUMBER
1684 ) IS
1685 SELECT sum(rrv.result_value) result_value
1686 ,count(rrv.RUN_RESULT_ID) record_count
1687 ,rrv.result_value UNIT_PRICE
1688 FROM pay_run_result_values rrv
1689 ,pay_run_results rr
1690 ,pay_assignment_actions paa
1691 ,pay_payroll_actions ppa
1692 WHERE rrv.input_value_id = p_iv_id
1693 AND rr.element_type_id = p_ele_type_id
1694 AND rr.run_result_id = rrv.run_result_id
1695 AND rr.assignment_action_id = paa.assignment_action_id
1696 AND paa.assignment_action_id = p_assignment_action_id
1697 AND ppa.payroll_action_id = paa.payroll_action_id
1698 AND ppa.action_type IN ('Q','R')
1699 AND rrv.result_value IS NOT NULL
1700 group by rrv.result_value;
1701 /*CURSOR csr_sum_of_result_values(p_iv_id NUMBER
1702 ,p_ele_type_id NUMBER
1703 ,p_assignment_action_id NUMBER
1704 ,p_group_by NUMBER) IS
1705 SELECT sum(rrv.result_value) result_value
1706 ,count(rrv.RUN_RESULT_ID) record_count
1707 ,rrv.result_value UNIT_PRICE
1708 FROM pay_run_result_values pr
1709 ,pay_run_result_values rrv
1710 ,pay_run_results rr
1711 ,pay_assignment_actions paa
1712 ,pay_payroll_actions ppa
1713 WHERE pr.input_value_id(+) = p_group_by
1714 AND rrv.input_value_id = p_iv_id
1715 AND rr.element_type_id = p_ele_type_id
1716 AND rr.run_result_id = rrv.run_result_id
1717 AND rr.run_result_id = pr.run_result_id (+)
1718 AND rr.assignment_action_id = paa.assignment_action_id
1719 AND paa.assignment_action_id = p_assignment_action_id
1720 AND ppa.payroll_action_id = paa.payroll_action_id
1721 AND ppa.action_type IN ('Q','R')
1722 AND rrv.result_value IS NOT NULL
1723 --AND pr.result_value IS NOT NULL
1724 group by pr.result_value,rrv.result_value;*/
1725 /*CURSOR csr_sum_of_result_values(p_iv_id NUMBER
1726 ,p_ele_type_id NUMBER
1727 ,p_assignment_action_id NUMBER) IS
1728 SELECT sum(rrv.result_value) result_value
1729 ,count(rrv.RUN_RESULT_ID) record_count
1730 ,rrv.result_value UNIT_PRICE
1731 FROM pay_run_result_values rrv
1732 ,pay_run_results rr
1733 ,pay_assignment_actions paa
1734 ,pay_payroll_actions ppa
1735 WHERE rrv.input_value_id = p_iv_id
1736 AND rr.element_type_id = p_ele_type_id
1737 AND rr.run_result_id = rrv.run_result_id
1738 AND rr.assignment_action_id = paa.assignment_action_id
1739 AND paa.assignment_action_id = p_assignment_action_id
1740 AND ppa.payroll_action_id = paa.payroll_action_id
1741 AND ppa.action_type IN ('Q','R')
1742 AND rrv.result_value IS NOT NULL
1743 group by pr.result_value,rrv.result_value;*/
1744
1745 rec_sum_of_result_values csr_sum_of_result_values%ROWTYPE;
1746 -----------------------------------------------------------------------------
1747
1748 -----------------------------------------------------------------------------
1749 /* Cursor to retrieve sum of all run result value for an given Main Element */
1750 -----------------------------------------------------------------------------
1751 CURSOR csr_all_sum_result_values(p_iv_id_UNIT NUMBER,p_iv_id_AMOUNT NUMBER,p_iv_id_UNIT_PRICE NUMBER
1752 ,p_ele_type_id NUMBER
1753 ,p_assignment_action_id NUMBER
1754 ) IS
1755 SELECT rrv3.result_value UNIT_PRICE , sum(rrv1.result_value) UNIT, sum(rrv2.result_value) AMOUNT
1756 FROM pay_run_result_values rrv1
1757 ,pay_run_results rr1
1758 ,pay_assignment_actions paa
1759 ,pay_payroll_actions ppa
1760 ,pay_run_result_values rrv2
1761 ,pay_run_results rr2
1762 ,pay_run_result_values rrv3
1763 ,pay_run_results rr3
1764 WHERE rrv1.input_value_id = p_iv_id_UNIT
1765 AND rr1.element_type_id = p_ele_type_id
1766 AND rr1.run_result_id = rrv1.run_result_id
1767 AND rr1.assignment_action_id = paa.assignment_action_id
1768 AND paa.assignment_action_id = p_assignment_action_id
1769 AND ppa.payroll_action_id = paa.payroll_action_id
1770 AND ppa.action_type IN ('Q','R')
1771 and rrv2.input_value_id = p_iv_id_AMOUNT
1772 AND rr2.run_result_id = rrv2.run_result_id
1773 AND rr2.element_entry_id = rr1.element_entry_id
1774 and rrv3.input_value_id = p_iv_id_UNIT_PRICE
1775 AND rr3.run_result_id = rrv3.run_result_id
1776 AND rr3.element_entry_id = rr1.element_entry_id
1777 group by rrv3.result_value;
1778
1779 -----------------------------------------------------------------------------
1780 -----------------------------------------------------------------------------
1781 /* Cursor to retrieve sum of all run result value for an given Main Element */
1782 -----------------------------------------------------------------------------
1783 CURSOR csr_all_result_values(p_iv_id_UNIT NUMBER,p_iv_id_AMOUNT NUMBER,p_iv_id_UNIT_PRICE NUMBER
1784 ,p_ele_type_id NUMBER
1785 ,p_assignment_action_id NUMBER
1786 ) IS
1787 SELECT rrv3.result_value UNIT_PRICE , rrv1.result_value UNIT, rrv2.result_value AMOUNT
1788 FROM pay_run_result_values rrv1
1789 ,pay_run_results rr1
1790 ,pay_assignment_actions paa
1791 ,pay_payroll_actions ppa
1792 ,pay_run_result_values rrv2
1793 ,pay_run_results rr2
1794 ,pay_run_result_values rrv3
1795 ,pay_run_results rr3
1796 WHERE rrv1.input_value_id = p_iv_id_UNIT
1797 AND rr1.element_type_id = p_ele_type_id
1798 AND rr1.run_result_id = rrv1.run_result_id
1799 AND rr1.assignment_action_id = paa.assignment_action_id
1800 AND paa.assignment_action_id = p_assignment_action_id
1801 AND ppa.payroll_action_id = paa.payroll_action_id
1802 AND ppa.action_type IN ('Q','R')
1803 and rrv2.input_value_id = p_iv_id_AMOUNT
1804 AND rr2.run_result_id = rrv2.run_result_id
1805 AND rr2.element_entry_id = rr1.element_entry_id
1806 and rrv3.input_value_id = p_iv_id_UNIT_PRICE
1807 AND rr3.run_result_id = rrv3.run_result_id
1808 AND rr3.element_entry_id = rr1.element_entry_id ;
1809
1810
1811 -----------------------------------------------------------------------------
1812
1813 rec_get_element csr_get_element%ROWTYPE;
1814 l_result_value pay_run_result_values.result_value%TYPE := 0;
1815 l_action_info_id NUMBER;
1816 l_ovn NUMBER;
1817 l_element_context VARCHAR2(10);
1818 l_index NUMBER := 0;
1819 l_formatted_value VARCHAR2(50) := NULL;
1820 l_flag NUMBER := 0;
1821 l_group_by number(10);
1822 l_unit_price NUMBER ;
1823 l_amount NUMBER;
1824 l_UNIT NUMBER;
1825 BEGIN
1826 IF g_debug THEN
1827 hr_utility.set_location(' Entering Procedure ARCHIVE_ADD_ELEMENT',320);
1828 END IF;
1829 IF p_archive_flag = 'N' THEN
1830 ---------------------------------------------------
1831 --Check if global table has already been populated
1832 ---------------------------------------------------
1833 IF g_element_table.count = 0 THEN
1834 OPEN csr_get_element(g_business_group_id);
1835 LOOP
1836 FETCH csr_get_element INTO rec_get_element;
1837 EXIT WHEN csr_get_element%NOTFOUND;
1838 l_element_context := 'F'; --Additional Element Context
1839 SETUP_ELEMENT_DEFINITIONS(p_classification_name => rec_get_element.classification_name
1840 ,p_element_name => rec_get_element.element_narrative
1841 ,p_element_type_id => rec_get_element.element_type_id
1842 ,p_input_value_id => rec_get_element.input_value_id
1843 ,p_element_type => l_element_context
1844 ,p_uom => rec_get_element.uom
1845 --,p_Element_code => rec_get_element.element_code
1846 ,p_archive_flag => p_archive_flag);
1847 END LOOP;
1848 CLOSE csr_get_element;
1849 END IF;
1850 ELSIF p_archive_flag = 'Y' AND g_element_table.count > 0 THEN
1851 FOR l_index IN g_element_table.first.. g_element_table.last LOOP
1852 l_result_value := NULL;
1853 l_group_by :=null;
1854 l_unit_price :=null;
1855 BEGIN
1856
1857 OPEN csr_group_by(g_element_table(l_index).element_type_id );
1858 FETCH csr_group_by
1859 INTO rec_group_by;
1860 CLOSE csr_group_by;
1861
1862 /*
1863 FOR csr_result_rec IN csr_sum_of_result_values(g_element_table(l_index).input_value_id
1864 ,g_element_table(l_index).element_type_id
1865 ,p_assignment_action_id
1866 ,rec_group_by.ORG_INFORMATION3)
1867 LOOP
1868 IF csr_result_rec.result_value is not null THEN
1869 pay_action_information_api.create_action_information (
1870 p_action_information_id => l_action_info_id
1871 ,p_action_context_id => p_archive_assact_id
1872 ,p_action_context_type => 'AAP'
1873 ,p_object_version_number => l_ovn
1874 ,p_effective_date => p_effective_date
1875 ,p_source_id => NULL
1876 ,p_source_text => NULL
1877 ,p_action_information_category => 'EMEA ELEMENT INFO'
1878 ,p_action_information1 => g_element_table(l_index).element_type_id
1879 ,p_action_information2 => g_element_table(l_index).input_value_id
1880 ,p_action_information3 => g_element_table(l_index).element_type
1881 ,p_action_information4 => fnd_number.number_to_canonical(csr_result_rec.result_value) --l_formatted_value
1882 ,p_action_information8 => csr_result_rec.record_count
1883 ,p_action_information9 => 'Additional Element unit per price :'||csr_result_rec.UNIT_PRICE
1884 ,p_assignment_id => p_assignment_id);
1885
1886 --fnd_file.put_line(fnd_file.log,' Group BY YES ++++++++ADD EMEA ELEMENT INFO ');
1887 --fnd_file.put_line(fnd_file.log,' l_action_info_id '||l_action_info_id);
1888 --fnd_file.put_line(fnd_file.log,' p_archive_assact_id '||p_archive_assact_id);
1889 --fnd_file.put_line(fnd_file.log,' g_element_table(l_index).element_type_id '||g_element_table(l_index).element_type_id);
1890 --fnd_file.put_line(fnd_file.log,' g_element_table(l_index).input_value_id '||g_element_table(l_index).input_value_id);
1891 --fnd_file.put_line(fnd_file.log,' g_element_table(l_index).element_type '||g_element_table(l_index).element_type);
1892 --fnd_file.put_line(fnd_file.log,' fnd_number.number_to_canonical(l_result_value) '||fnd_number.number_to_canonical(csr_result_rec.result_value));
1893 --fnd_file.put_line(fnd_file.log,' p_assignment_id '||p_assignment_id);
1894 END IF;
1895 END LOOP;*/
1896
1897 -- The se_soe contains
1898 -- segment 3 = > I or O
1899 -- segment 6 = > Y or N
1900 -- segment 7 = > Input ID UNIT
1901 -- segment 8 = > Input ID UNIT PRICE
1902 -- segment 9 = > Input ID Amount
1903 IF ( (rec_group_by.ORG_INFORMATION3 = 'O') or (rec_group_by.ORG_INFORMATION3 IS NULL) )
1904 THEN
1905 -- Case for Group by or NOT
1906 -- Segemnt 6 is allowed here, as it makes sense.
1907 IF ( rec_group_by.ORG_INFORMATION6 = 'N' or (rec_group_by.ORG_INFORMATION3 IS NULL) )
1908 THEN
1909 -- This csae iis for individual representation of each element.
1910 -- unit and unit price should be absent.
1911 FOR csr_result_rec IN csr_result_value(g_element_table(l_index).input_value_id
1912 ,g_element_table(l_index).element_type_id
1913 ,p_assignment_action_id)
1914 LOOP
1915 IF csr_result_rec.result_value is not null THEN
1916 pay_action_information_api.create_action_information (
1917 p_action_information_id => l_action_info_id
1918 ,p_action_context_id => p_archive_assact_id
1919 ,p_action_context_type => 'AAP'
1920 ,p_object_version_number => l_ovn
1921 ,p_effective_date => p_effective_date
1922 ,p_source_id => NULL
1923 ,p_source_text => NULL
1924 ,p_action_information_category => 'EMEA ELEMENT INFO'
1925 ,p_action_information1 => g_element_table(l_index).element_type_id
1926 ,p_action_information2 => g_element_table(l_index).input_value_id
1927 ,p_action_information3 => g_element_table(l_index).element_type
1928 ,p_action_information4 => fnd_number.number_to_canonical(csr_result_rec.result_value) --l_formatted_value
1929 ,p_action_information8 => ''
1930 ,p_action_information9 => 'Additional Element:'
1931 ,p_assignment_id => p_assignment_id);
1932
1933 --fnd_file.put_line(fnd_file.log,'group by NO l_action_info_id ' || l_action_info_id );
1934 --fnd_file.put_line(fnd_file.log,'Earning Element ');
1935 --fnd_file.put_line(fnd_file.log,'csr_rec.element_type_id ' || g_element_table(l_index).element_type_id );
1936 --fnd_file.put_line(fnd_file.log,'csr_rec.input_value_id ' || g_element_table(l_index).input_value_id );
1937 --fnd_file.put_line(fnd_file.log,'fnd_number.number_to_canonical(l_result_value) ' || fnd_number.number_to_canonical(csr_result_rec.result_value) );
1938 END IF;
1939 END LOOP;
1940
1941 ELSIF rec_group_by.ORG_INFORMATION6 = 'Y'
1942 THEN
1943 -- This csae iis for Grouping by pay value of each element.
1944 -- unit and unit price should be present
1945 FOR csr_result_rec IN csr_sum_of_result_values(g_element_table(l_index).input_value_id
1946 ,g_element_table(l_index).element_type_id
1947 ,p_assignment_action_id )
1948 LOOP
1949 IF csr_result_rec.result_value is not null THEN
1950 pay_action_information_api.create_action_information (
1951 p_action_information_id => l_action_info_id
1952 ,p_action_context_id => p_archive_assact_id
1953 ,p_action_context_type => 'AAP'
1954 ,p_object_version_number => l_ovn
1955 ,p_effective_date => p_effective_date
1956 ,p_source_id => NULL
1957 ,p_source_text => NULL
1958 ,p_action_information_category => 'EMEA ELEMENT INFO'
1959 ,p_action_information1 => g_element_table(l_index).element_type_id
1960 ,p_action_information2 => g_element_table(l_index).input_value_id
1961 ,p_action_information3 => g_element_table(l_index).element_type
1962 ,p_action_information4 => fnd_number.number_to_canonical(csr_result_rec.result_value) --l_formatted_value
1963 ,p_action_information8 => csr_result_rec.record_count
1964 ,p_action_information9 => 'Additional Element unit per price:'||csr_result_rec.UNIT_PRICE
1965 ,p_assignment_id => p_assignment_id);
1966
1967 --fnd_file.put_line(fnd_file.log,'group by NO l_action_info_id ' || l_action_info_id );
1968 --fnd_file.put_line(fnd_file.log,'Earning Element ');
1969 --fnd_file.put_line(fnd_file.log,'csr_rec.element_type_id ' || g_element_table(l_index).element_type_id );
1970 --fnd_file.put_line(fnd_file.log,'csr_rec.input_value_id ' || g_element_table(l_index).input_value_id );
1971 --fnd_file.put_line(fnd_file.log,'fnd_number.number_to_canonical(l_result_value) ' || fnd_number.number_to_canonical(csr_result_rec.result_value) );
1972 END IF;
1973 END LOOP;
1974 END IF;
1975 ELSIF rec_group_by.ORG_INFORMATION3 = 'I'
1976 THEN
1977 -- Case for UNIT,PRICE,AMOUNT
1978 -- Segment 7,8,9 is allowed
1979 -- Segemnt 6 is not allowed here, as it doesn't makes sense.
1980 -- segment 7 = > Input ID UNIT
1981 -- segment 8 = > Input ID UNIT PRICE
1982 -- segment 9 = > Input ID Amount
1983 IF rec_group_by.ORG_INFORMATION7 IS NOT NULL AND
1984 rec_group_by.ORG_INFORMATION8 IS NOT NULL AND
1985 rec_group_by.ORG_INFORMATION9 IS NOT NULL
1986 THEN
1987 -- All three are selected, we can group by three in single query
1988
1989 IF rec_group_by.ORG_INFORMATION10 = 'Y'
1990 THEN
1991 FOR csr_result_rec IN csr_all_sum_result_values(rec_group_by.ORG_INFORMATION7
1992 ,rec_group_by.ORG_INFORMATION9
1993 ,rec_group_by.ORG_INFORMATION8
1994 ,g_element_table(l_index).element_type_id
1995 ,p_assignment_action_id )
1996 LOOP
1997 IF csr_result_rec.AMOUNT is not null THEN
1998 pay_action_information_api.create_action_information (
1999 p_action_information_id => l_action_info_id
2000 ,p_action_context_id => p_archive_assact_id
2001 ,p_action_context_type => 'AAP'
2002 ,p_object_version_number => l_ovn
2003 ,p_effective_date => p_effective_date
2004 ,p_source_id => NULL
2005 ,p_source_text => NULL
2006 ,p_action_information_category => 'EMEA ELEMENT INFO'
2007 ,p_action_information1 => g_element_table(l_index).element_type_id
2008 ,p_action_information2 => g_element_table(l_index).input_value_id
2009 ,p_action_information3 => g_element_table(l_index).element_type
2010 ,p_action_information4 => fnd_number.number_to_canonical(csr_result_rec.AMOUNT) --l_formatted_value
2011 ,p_action_information8 => csr_result_rec.UNIT
2012 ,p_action_information9 => 'Additional Element unit per price:'||csr_result_rec.UNIT_PRICE
2013 ,p_assignment_id => p_assignment_id);
2014
2015 --fnd_file.put_line(fnd_file.log,'group by NO l_action_info_id ' || l_action_info_id );
2016 --fnd_file.put_line(fnd_file.log,'Earning Element ');
2017 --fnd_file.put_line(fnd_file.log,'csr_result_rec.UNIT ' || csr_result_rec.UNIT );
2018 --fnd_file.put_line(fnd_file.log,'csr_result_rec.WP WP ' || csr_result_rec.UNIT_PRICE );
2019 --fnd_file.put_line(fnd_file.log,'fnd_number.number_to_canonical(l_result_value) ' || fnd_number.number_to_canonical(csr_result_rec.AMOUNT) );
2020 END IF;
2021 END LOOP;
2022 ELSE
2023 FOR csr_result_rec IN csr_all_result_values(rec_group_by.ORG_INFORMATION7
2024 ,rec_group_by.ORG_INFORMATION9
2025 ,rec_group_by.ORG_INFORMATION8
2026 ,g_element_table(l_index).element_type_id
2027 ,p_assignment_action_id )
2028 LOOP
2029 IF csr_result_rec.AMOUNT is not null THEN
2030 pay_action_information_api.create_action_information (
2031 p_action_information_id => l_action_info_id
2032 ,p_action_context_id => p_archive_assact_id
2033 ,p_action_context_type => 'AAP'
2034 ,p_object_version_number => l_ovn
2035 ,p_effective_date => p_effective_date
2036 ,p_source_id => NULL
2037 ,p_source_text => NULL
2038 ,p_action_information_category => 'EMEA ELEMENT INFO'
2039 ,p_action_information1 => g_element_table(l_index).element_type_id
2040 ,p_action_information2 => g_element_table(l_index).input_value_id
2041 ,p_action_information3 => g_element_table(l_index).element_type
2042 ,p_action_information4 => fnd_number.number_to_canonical(csr_result_rec.AMOUNT) --l_formatted_value
2043 ,p_action_information8 => csr_result_rec.UNIT
2044 ,p_action_information9 => 'Additional Element unit per price:'||csr_result_rec.UNIT_PRICE
2045 ,p_assignment_id => p_assignment_id);
2046
2047 --fnd_file.put_line(fnd_file.log,'group by NO l_action_info_id ' || l_action_info_id );
2048 --fnd_file.put_line(fnd_file.log,'Earning Element ');
2049 --fnd_file.put_line(fnd_file.log,'csr_result_rec.UNIT ' || csr_result_rec.UNIT );
2050 --fnd_file.put_line(fnd_file.log,'csr_result_rec.WP WP ' || csr_result_rec.UNIT_PRICE );
2051 --fnd_file.put_line(fnd_file.log,'fnd_number.number_to_canonical(l_result_value) ' || fnd_number.number_to_canonical(csr_result_rec.AMOUNT) );
2052 END IF;
2053 END LOOP;
2054
2055 END IF;-- end of rec_group_by.ORG_INFORMATION10 = 'Y'
2056 ELSE -- Three inputs are not selected.
2057 -- have to get the each input value id and find value for each
2058 -- and archive it if the amount is not null
2059
2060 -- Case for UNIT,PRICE,AMOUNT
2061 -- Segment 7,8,9 is allowed
2062 -- Segemnt 6 is not allowed here, as it doesn't makes sense.
2063 -- segment 7 = > Input ID UNIT
2064 -- segment 8 = > Input ID UNIT PRICE
2065 -- segment 9 = > Input ID Amount
2066 IF rec_group_by.ORG_INFORMATION9 IS NOT NULL
2067 THEN
2068 -- amount should not be null
2069 -- find the amount value and element entry id of this element
2070 -- for this element entry find the the other selected columns like UNIT , UNIT PRICE if selected
2071 --
2072 FOR csr_result_rec IN csr_result_value(rec_group_by.ORG_INFORMATION9 ,g_element_table(l_index).element_type_id ,p_assignment_action_id)
2073 LOOP
2074
2075 -- we have EE id
2076 l_amount := csr_result_rec.result_value;
2077 IF rec_group_by.ORG_INFORMATION8 IS NOT NULL
2078 THEN
2079 OPEN csr_result_value_EE(rec_group_by.ORG_INFORMATION8 ,g_element_table(l_index).element_type_id ,p_assignment_action_id,csr_result_rec.element_entry_id );
2080 FETCH csr_result_value_EE
2081 INTO l_unit_price;
2082 CLOSE csr_result_value_EE;
2083 ELSE
2084 l_unit_price :=NULL;
2085 END IF; -- End if of segment 8 , unit price
2086
2087 IF rec_group_by.ORG_INFORMATION7 IS NOT NULL
2088 THEN
2089 OPEN csr_result_value_EE(rec_group_by.ORG_INFORMATION8 ,g_element_table(l_index).element_type_id ,p_assignment_action_id,csr_result_rec.element_entry_id );
2090 FETCH csr_result_value_EE
2091 INTO l_unit;
2092 CLOSE csr_result_value_EE;
2093 ELSE
2094 l_unit :=NULL;
2095 END IF; -- End if of segment 7 , unit
2096
2097 -- Resume again
2098 IF csr_result_rec.result_value is not null THEN
2099 pay_action_information_api.create_action_information (
2100 p_action_information_id => l_action_info_id
2101 ,p_action_context_id => p_archive_assact_id
2102 ,p_action_context_type => 'AAP'
2103 ,p_object_version_number => l_ovn
2104 ,p_effective_date => p_effective_date
2105 ,p_source_id => NULL
2106 ,p_source_text => NULL
2107 ,p_action_information_category => 'EMEA ELEMENT INFO'
2108 ,p_action_information1 => g_element_table(l_index).element_type_id
2109 ,p_action_information2 => g_element_table(l_index).input_value_id
2110 ,p_action_information3 => g_element_table(l_index).element_type
2111 ,p_action_information4 => fnd_number.number_to_canonical(l_amount) --l_formatted_value
2112 ,p_action_information8 => l_unit
2113 ,p_action_information9 => 'Additional Element unit per price:'||l_uNIT_PRICE
2114 ,p_assignment_id => p_assignment_id);
2115
2116 --fnd_file.put_line(fnd_file.log,'group by NO l_action_info_id ' || l_action_info_id );
2117 --fnd_file.put_line(fnd_file.log,'Earning Element ');
2118 --fnd_file.put_line(fnd_file.log,'csr_rec.element_type_id ' || g_element_table(l_index).element_type_id );
2119 --fnd_file.put_line(fnd_file.log,'csr_rec.input_value_id ' || g_element_table(l_index).input_value_id );
2120 --fnd_file.put_line(fnd_file.log,'fnd_number.number_to_canonical(l_result_value) ' || fnd_number.number_to_canonical(csr_result_rec.result_value) );
2121 END IF; -- end of csr_result_rec.result_value is not null
2122 END LOOP;
2123 END IF; -- end of rec_group_by.ORG_INFORMATION9 IS NOT NULL
2124
2125 END IF;
2126
2127
2128 END IF;
2129 --OPEN csr_result_value(g_element_table(l_index).input_value_id ,g_element_table(l_index).element_type_id
2130 --,p_assignment_action_id);
2131 --FETCH csr_result_value INTO l_result_value;
2132 --CLOSE csr_result_value;
2133 /*
2134 OPEN csr_group_by(g_element_table(l_index).element_type_id );
2135 FETCH csr_group_by INTO rec_group_by;
2136 CLOSE csr_group_by;
2137 l_group_by :=rec_group_by.ORG_INFORMATION3;
2138
2139
2140 OPEN csr_result_value(rec_group_by.ORG_INFORMATION4,g_element_table(l_index).element_type_id ,p_assignment_action_id);
2141 FETCH csr_result_value INTO l_unit_price;
2142 CLOSE csr_result_value;
2143
2144 IF l_group_by !='Y'
2145 THEN
2146 FOR csr_result_rec IN csr_result_value(g_element_table(l_index).input_value_id ,g_element_table(l_index).element_type_id
2147 ,p_assignment_action_id)
2148 LOOP
2149 IF csr_result_rec.result_value is not null THEN
2150 pay_action_information_api.create_action_information (
2151 p_action_information_id => l_action_info_id
2152 ,p_action_context_id => p_archive_assact_id
2153 ,p_action_context_type => 'AAP'
2154 ,p_object_version_number => l_ovn
2155 ,p_effective_date => p_effective_date
2156 ,p_source_id => NULL
2157 ,p_source_text => NULL
2158 ,p_action_information_category => 'EMEA ELEMENT INFO'
2159 ,p_action_information1 => g_element_table(l_index).element_type_id
2160 ,p_action_information2 => g_element_table(l_index).input_value_id
2161 ,p_action_information3 => g_element_table(l_index).element_type
2162 ,p_action_information4 => fnd_number.number_to_canonical(csr_result_rec.result_value) --l_formatted_value
2163 ,p_action_information8 => '1'
2164 ,p_action_information9 => 'Additional Element unit per price :'||l_unit_price
2165 ,p_assignment_id => p_assignment_id);
2166
2167 --fnd_file.put_line(fnd_file.log,' Group BY NO ++++++++ADD EMEA ELEMENT INFO ');
2168 --fnd_file.put_line(fnd_file.log,' l_action_info_id '||l_action_info_id);
2169 --fnd_file.put_line(fnd_file.log,' p_archive_assact_id '||p_archive_assact_id);
2170 --fnd_file.put_line(fnd_file.log,' g_element_table(l_index).element_type_id '||g_element_table(l_index).element_type_id);
2171 --fnd_file.put_line(fnd_file.log,' g_element_table(l_index).input_value_id '||g_element_table(l_index).input_value_id);
2172 --fnd_file.put_line(fnd_file.log,' g_element_table(l_index).element_type '||g_element_table(l_index).element_type);
2173 --fnd_file.put_line(fnd_file.log,' fnd_number.number_to_canonical(l_result_value) '||fnd_number.number_to_canonical(csr_result_rec.result_value));
2174 --fnd_file.put_line(fnd_file.log,' p_assignment_id '||p_assignment_id);
2175
2176 END IF;
2177 END LOOP;
2178 l_group_by :=NULL;
2179 ELSE
2180
2181 OPEN csr_sum_of_result_values(g_element_table(l_index).input_value_id ,g_element_table(l_index).element_type_id ,p_assignment_action_id);
2182 FETCH csr_sum_of_result_values INTO rec_sum_of_result_values;
2183 CLOSE csr_sum_of_result_values;
2184
2185 l_result_value := rec_sum_of_result_values.result_value;
2186
2187 IF l_result_value is not null THEN
2188 pay_action_information_api.create_action_information (
2189 p_action_information_id => l_action_info_id
2190 ,p_action_context_id => p_archive_assact_id
2191 ,p_action_context_type => 'AAP'
2192 ,p_object_version_number => l_ovn
2193 ,p_effective_date => p_effective_date
2194 ,p_source_id => NULL
2195 ,p_source_text => NULL
2196 ,p_action_information_category => 'EMEA ELEMENT INFO'
2197 ,p_action_information1 => g_element_table(l_index).element_type_id
2198 ,p_action_information2 => g_element_table(l_index).input_value_id
2199 ,p_action_information3 => g_element_table(l_index).element_type
2200 ,p_action_information4 => fnd_number.number_to_canonical(l_result_value) --l_formatted_value
2201 ,p_action_information8 => rec_sum_of_result_values.record_count
2202 ,p_action_information9 => 'Additional Element unit per price :'||l_unit_price
2203 ,p_assignment_id => p_assignment_id);
2204
2205 --fnd_file.put_line(fnd_file.log,' Group BY YES ++++++++ADD EMEA ELEMENT INFO ');
2206 --fnd_file.put_line(fnd_file.log,' l_action_info_id '||l_action_info_id);
2207 --fnd_file.put_line(fnd_file.log,' p_archive_assact_id '||p_archive_assact_id);
2208 --fnd_file.put_line(fnd_file.log,' g_element_table(l_index).element_type_id '||g_element_table(l_index).element_type_id);
2209 --fnd_file.put_line(fnd_file.log,' g_element_table(l_index).input_value_id '||g_element_table(l_index).input_value_id);
2210 --fnd_file.put_line(fnd_file.log,' g_element_table(l_index).element_type '||g_element_table(l_index).element_type);
2211 --fnd_file.put_line(fnd_file.log,' fnd_number.number_to_canonical(l_result_value) '||fnd_number.number_to_canonical(l_result_value));
2212 --fnd_file.put_line(fnd_file.log,' p_assignment_id '||p_assignment_id);
2213 END IF;
2214 l_group_by :=NULL;
2215 END IF;-- End of l_group_by
2216 */
2217 EXCEPTION WHEN OTHERS THEN
2218 g_err_num := SQLCODE;
2219 /*fnd_file.put_line(fnd_file.log,'ORA_ERR: ' || g_err_num || 'In ARCHIVE_ADD_ELEMENT');*/
2220 IF g_debug THEN
2221 hr_utility.set_location('ORA_ERR: ' || g_err_num || 'In ARCHIVE_ADD_ELEMENT',330);
2222 END IF;
2223 END;
2224 END LOOP;
2225 END IF;
2226 IF g_debug THEN
2227 hr_utility.set_location(' Leaving Procedure ARCHIVE_ADD_ELEMENT',340);
2228 END IF;
2229 END ARCHIVE_ADD_ELEMENT;
2230 /* OTHER BALANCES REGION */
2231 PROCEDURE ARCHIVE_OTH_BALANCE (p_archive_assact_id IN NUMBER,
2232 p_assignment_action_id IN NUMBER,
2233 p_assignment_id IN NUMBER,
2234 p_payroll_action_id IN NUMBER,
2235 p_record_count IN NUMBER,
2236 p_pre_payact_id IN NUMBER,
2237 p_effective_date IN DATE,
2238 p_date_earned IN DATE,
2239 p_archive_flag IN VARCHAR2) IS
2240 /* Cursor to retrieve Other Balances Information */
2241 CURSOR csr_get_balance(p_bus_grp_id NUMBER) IS
2242 SELECT org_information4 balance_type_id
2243 ,org_information5 balance_dim_id
2244 ,org_information7 narrative
2245 FROM hr_organization_information
2246 WHERE organization_id = p_bus_grp_id
2247 AND org_information_context = 'Business Group:Payslip Info'
2248 AND org_information1 = 'BALANCE';
2249 /* Cursor to retrieve Tax Unit Id for setting context */
2250 CURSOR csr_tax_unit (p_run_assact_id NUMBER) IS
2251 SELECT paa.tax_unit_id
2252 FROM pay_assignment_actions paa
2253 WHERE paa.assignment_action_id = p_run_assact_id;
2254 /* Cursor to fetch defined balance id */
2255 CURSOR csr_def_balance(bal_type_id NUMBER, bal_dim_id NUMBER) IS
2256 SELECT defined_balance_id
2257 FROM pay_defined_balances
2258 WHERE balance_type_id = bal_type_id
2259 AND balance_dimension_id = bal_dim_id;
2260 rec_get_balance csr_get_balance%ROWTYPE;
2261 l_balance_value NUMBER := 0;
2262 l_action_info_id NUMBER;
2263 l_ovn NUMBER;
2264 l_index NUMBER;
2265 l_tu_id NUMBER;
2266 l_defined_balance_id NUMBER:=0;
2267 l_formatted_value VARCHAR2(50) := NULL;
2268 l_flag NUMBER := 0;
2269 BEGIN
2270 IF g_debug THEN
2271 hr_utility.set_location(' Entering Procedure ARCHIVE_OTH_BALANCE',350);
2272 END IF;
2273
2274 --fnd_file.put_line(fnd_file.log,'Entering In ARCHIVE_OTH_BALANCE global');
2275 IF p_archive_flag = 'N' THEN
2276 ---------------------------------------------------
2277 --Check if global table has already been populated
2278 ---------------------------------------------------
2279 --fnd_file.put_line(fnd_file.log,'g_business_group_id == '|| g_business_group_id);
2280 IF g_user_balance_table.count = 0 THEN
2281 OPEN csr_get_balance(g_business_group_id);
2282 LOOP
2283 FETCH csr_get_balance INTO rec_get_balance;
2284 EXIT WHEN csr_get_balance%NOTFOUND;
2285 OPEN csr_def_balance(rec_get_balance.balance_type_id,rec_get_balance.balance_dim_id);
2286 FETCH csr_def_balance INTO l_defined_balance_id;
2287 CLOSE csr_def_balance;
2288 --fnd_file.put_line(fnd_file.log,'Calling SETUP_BALANCE_DEFINITIONS in Archive oth balance');
2289 PAY_SE_PAYSLIP_ARCHIVE.SETUP_BALANCE_DEFINITIONS
2290 (p_balance_name => rec_get_balance.narrative
2291 ,p_defined_balance_id => l_defined_balance_id
2292 ,p_balance_type_id => rec_get_balance.balance_type_id);
2293 END LOOP;
2294 CLOSE csr_get_balance;
2295 END IF;
2296 ELSIF p_archive_flag = 'Y' THEN
2297
2298 OPEN csr_tax_unit(p_assignment_action_id);
2299 FETCH csr_tax_unit INTO l_tu_id;
2300 CLOSE csr_tax_unit;
2301
2302 PAY_BALANCE_PKG.SET_CONTEXT('TAX_UNIT_ID',l_tu_id);
2303 PAY_BALANCE_PKG.SET_CONTEXT('DATE_EARNED',fnd_date.date_to_canonical(p_date_earned));
2304 IF g_user_balance_table.count > 0 THEN
2305
2306 FOR l_index IN g_user_balance_table.first.. g_user_balance_table.last LOOP
2307 l_balance_value := pay_balance_pkg.get_value(g_user_balance_table(l_index).defined_balance_id,p_assignment_action_id);
2308 IF l_balance_value > 0 THEN
2309
2310 pay_action_information_api.create_action_information (
2311 p_action_information_id => l_action_info_id
2312 ,p_action_context_id => p_archive_assact_id
2313 ,p_action_context_type => 'AAP'
2314 ,p_object_version_number => l_ovn
2315 ,p_effective_date => p_effective_date
2316 ,p_source_id => NULL
2317 ,p_source_text => NULL
2318 ,p_action_information_category => 'EMEA BALANCES'
2319 ,p_action_information1 => g_user_balance_table(l_index).defined_balance_id
2320 ,p_action_information2 => 'OBAL' --Other Balances Context
2321 ,p_action_information4 => fnd_number.number_to_canonical(l_balance_value) --l_formatted_value
2322 ,p_action_information5 => NULL
2323 ,p_action_information6 => 'Other Balances'
2324 ,p_assignment_id => p_assignment_id);
2325
2326 --fnd_file.put_line(fnd_file.log,' Other Balances ');
2327 --fnd_file.put_line(fnd_file.log,' l_action_info_id '||l_action_info_id);
2328 --fnd_file.put_line(fnd_file.log,' g_user_balance_table(l_index).defined_balance_id '||g_user_balance_table(l_index).defined_balance_id);
2329 --fnd_file.put_line(fnd_file.log,' fnd_number.number_to_canonical(l_balance_value) '||fnd_number.number_to_canonical(l_balance_value));
2330
2331
2332 END IF;
2333 END LOOP;
2334 END IF; /* For table count check */
2335 END IF;
2336 --fnd_file.put_line(fnd_file.log,'Leaving ARCHIVE_OTH_BALANCE global');
2337 EXCEPTION WHEN OTHERS THEN
2338 g_err_num := SQLCODE;
2339 --fnd_file.put_line(fnd_file.log,'ORA_ERR: ' || g_err_num || 'In ARCHIVE_OTH_BALANCE'||SQLERRM);
2340 IF g_debug THEN
2341 hr_utility.set_location('ORA_ERR: ' || g_err_num || 'In ARCHIVE_OTH_BALANCE',360);
2342 END IF;
2343 END ARCHIVE_OTH_BALANCE;
2344 /*Additional Employee Details*/
2345 PROCEDURE ARCHIVE_ADDL_EMP_DETAILS(p_archive_assact_id IN NUMBER
2346 ,p_assignment_id IN NUMBER
2347 ,p_assignment_action_id IN NUMBER
2348 ,p_effective_date IN DATE
2349 ,p_date_earned IN DATE)
2350 IS
2351 CURSOR CSR_ACTUAL_TERM_DATE (p_assignment_id NUMBER) IS
2352 SELECT actual_termination_date
2353 FROM per_periods_of_service pps,
2354 per_all_assignments_f paa
2355 WHERE pps.period_of_service_id = paa.period_of_service_id
2356 AND p_date_earned between paa.effective_start_date and paa.effective_end_date
2357 AND paa.assignment_id = p_assignment_id;
2358 CURSOR get_details(p_assignment_id NUMBER , p_input_value VARCHAR2 ) IS
2359 SELECT ee.effective_start_date
2360 ,eev1.screen_entry_value screen_entry_value
2361 FROM per_all_assignments_f asg1
2362 ,per_all_assignments_f asg2
2363 ,per_all_people_f per
2364 ,pay_element_links_f el
2365 ,pay_element_types_f et
2366 ,pay_input_values_f iv1
2367 ,pay_element_entries_f ee
2368 ,pay_element_entry_values_f eev1
2369 WHERE asg1.assignment_id = p_assignment_id
2370 AND p_date_earned BETWEEN asg1.effective_start_date AND asg1.effective_end_date
2371 AND p_date_earned BETWEEN asg2.effective_start_date AND asg2.effective_end_date
2372 AND p_date_earned BETWEEN per.effective_start_date AND per.effective_end_date
2373 AND per.person_id = asg1.person_id
2374 AND asg2.person_id = per.person_id
2375 AND asg2.primary_flag = 'Y'
2376 AND et.element_name = 'Tax Card'
2377 AND et.legislation_code = 'SE'
2378 AND iv1.element_type_id = et.element_type_id
2379 AND iv1.name = p_input_value
2380 AND el.business_group_id = per.business_group_id
2381 AND el.element_type_id = et.element_type_id
2382 AND ee.assignment_id = asg2.assignment_id
2383 AND ee.element_link_id = el.element_link_id
2384 AND eev1.element_entry_id = ee.element_entry_id
2385 AND eev1.input_value_id = iv1.input_value_id
2386 AND p_date_earned BETWEEN ee.effective_start_date AND ee.effective_end_date
2387 AND p_date_earned BETWEEN eev1.effective_start_date AND eev1.effective_end_date;
2388 CURSOR csr_tax_details(p_assignment_id NUMBER, p_input_value VARCHAR2) IS
2389 SELECT ee.effective_start_date
2390 ,eev1.screen_entry_value screen_entry_value
2391 FROM per_all_assignments_f asg1
2392 ,pay_element_links_f el
2393 ,pay_element_types_f et
2394 ,pay_input_values_f iv1
2395 ,pay_element_entries_f ee
2396 ,pay_element_entry_values_f eev1
2397 WHERE asg1.assignment_id = p_assignment_id
2398 AND p_date_earned BETWEEN asg1.effective_start_date AND asg1.effective_end_date
2399 AND et.element_name = 'Tax'
2400 AND et.legislation_code = 'SE'
2401 AND iv1.element_type_id = et.element_type_id
2402 AND iv1.name = p_input_value
2403 AND el.element_type_id = et.element_type_id
2404 AND ee.assignment_id = asg1.assignment_id
2405 AND ee.element_link_id = el.element_link_id
2406 AND eev1.element_entry_id = ee.element_entry_id
2407 AND eev1.input_value_id = iv1.input_value_id
2408 AND p_date_earned BETWEEN ee.effective_start_date AND ee.effective_end_date
2409 AND p_date_earned BETWEEN eev1.effective_start_date AND eev1.effective_end_date;
2410 CURSOR csr_tax_category (p_assignment_id NUMBER) IS
2411 SELECT segment13
2412 FROM per_all_assignments_f paa,
2413 hr_soft_coding_keyflex hsc
2414 WHERE
2415 paa.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
2416 AND p_date_earned BETWEEN paa.effective_start_date
2417 AND paa.effective_end_date
2418 AND paa.assignment_id = p_assignment_id;
2419 CURSOR csr_global_value (p_global_name VARCHAR2) IS
2420 SELECT global_value
2421 FROM ff_globals_f
2422 WHERE global_name = p_global_name
2423 AND p_date_earned BETWEEN effective_start_date AND effective_end_date;
2424
2425 CURSOR c_bal_attrid (p_attribute_name VARCHAR2 ) IS
2426 SELECT attribute_id
2427 FROM pay_bal_attribute_definitions
2428 WHERE legislation_code='SE'
2429 AND attribute_name= p_attribute_name;
2430
2431
2432 CURSOR c_bal_defid (p_attribute_id NUMBER ) IS
2433 SELECT defined_balance_id
2434 FROM pay_balance_attributes
2435 WHERE attribute_id= p_attribute_id;
2436
2437
2438 l_actual_termination_date PER_PERIODS_OF_SERVICE.ACTUAL_TERMINATION_DATE%TYPE;
2439 l_tax_card_effective_date DATE;
2440 l_tax_card_type VARCHAR2(50);
2441 l_Tax_Percentage NUMBER(5,2);
2442 l_Tax_Table_Number NUMBER(5,2);
2443 l_Tax_Column NUMBER(10);
2444 l_Tax_Free_Threshold NUMBER (10);
2445 l_Calculation_Code varchar2(10);
2446 l_Calculation_Sum varchar2(10);
2447
2448 l_ovn NUMBER ;
2449 l_rec get_details%ROWTYPE;
2450 l_tax_rec csr_tax_details%ROWTYPE;
2451 l_action_info_id pay_action_information.action_information_id%TYPE;
2452
2453 l_defined_balance_id pay_defined_balances.defined_balance_id%TYPE;
2454
2455
2456 BEGIN
2457
2458 --fnd_file.put_line(fnd_file.log,' Entering Addl Employee details');
2459
2460 OPEN CSR_ACTUAL_TERM_DATE (p_assignment_id);
2461 FETCH CSR_ACTUAL_TERM_DATE INTO l_actual_termination_date;
2462 CLOSE CSR_ACTUAL_TERM_DATE;
2463 OPEN get_details(p_assignment_id ,'Tax Percentage' );
2464 FETCH get_details INTO l_rec;
2465 CLOSE get_details;
2466 l_Tax_Percentage := l_rec.screen_entry_value ;
2467 OPEN get_details(p_assignment_id , 'Tax Table Number' );
2468 FETCH get_details INTO l_rec;
2469 CLOSE get_details;
2470 l_Tax_Table_Number := l_rec.screen_entry_value ;
2471 OPEN get_details(p_assignment_id , 'Tax Column' );
2472 FETCH get_details INTO l_rec;
2473 CLOSE get_details;
2474 l_Tax_Column := l_rec.screen_entry_value ;
2475 OPEN get_details(p_assignment_id , 'Tax Free Threshold');
2476 FETCH get_details INTO l_rec;
2477 CLOSE get_details;
2478 l_Tax_Free_Threshold := l_rec.screen_entry_value ;
2479 OPEN get_details(p_assignment_id , 'Tax Card Type' );
2480 FETCH get_details INTO l_rec;
2481 CLOSE get_details;
2482 l_tax_card_effective_date := l_rec.effective_start_date;
2483 l_tax_card_type := l_rec.screen_entry_value ;
2484
2485 OPEN get_details(p_assignment_id , 'Calculation Code');
2486 FETCH get_details INTO l_rec;
2487 CLOSE get_details;
2488 l_Calculation_Code := null;
2489 l_Calculation_Code := l_rec.screen_entry_value ;
2490
2491 OPEN get_details(p_assignment_id , 'Calculation Sum');
2492 FETCH get_details INTO l_rec;
2493 CLOSE get_details;
2494 l_Calculation_Sum := null;
2495 l_Calculation_Sum := l_rec.screen_entry_value ;
2496 /* IF l_tax_card_type = 'TS' THEN
2497 IF l_base_rate IS NULL THEN
2498 OPEN csr_global_value ('FI_TAX_AT_SOURCE_PCT');
2499 FETCH csr_global_value INTO l_base_rate;
2500 CLOSE csr_global_value;
2501 END IF;
2502 END IF;
2503 IF l_tax_card_type = 'EI' THEN
2504 OPEN csr_tax_details(p_assignment_id, 'Extra Income Rate');
2505 FETCH csr_tax_details INTO l_tax_rec;
2506 CLOSE csr_tax_details;
2507 l_base_rate := l_rec.screen_entry_value ;
2508 OPEN csr_tax_details(p_assignment_id, 'Extra Income Additional Rate');
2509 FETCH csr_tax_details INTO l_tax_rec;
2510 CLOSE csr_tax_details;
2511 l_additional_rate := l_rec.screen_entry_value ;
2512 OPEN csr_tax_details(p_assignment_id, 'Extra Income Limit');
2513 FETCH csr_tax_details INTO l_tax_rec;
2514 CLOSE csr_tax_details;
2515 l_yearly_income_limit := l_rec.screen_entry_value ;
2516 OPEN csr_tax_details(p_assignment_id, 'Previous Extra Income Limit');
2517 FETCH csr_tax_details INTO l_tax_rec;
2518 CLOSE csr_tax_details;
2519 l_previous_income := l_rec.screen_entry_value ;
2520 l_tax_card_effective_date := l_tax_rec.effective_start_date;
2521 END IF;
2522 */
2523
2524 l_tax_card_type := hr_general.decode_lookup('SE_TAX_CARD_TYPE',l_tax_card_type ) ;
2525
2526 pay_action_information_api.create_action_information (
2527 p_action_information_id => l_action_info_id
2528 ,p_action_context_id => p_archive_assact_id
2529 ,p_action_context_type => 'AAP'
2530 ,p_object_version_number => l_ovn
2531 ,p_effective_date => p_effective_date
2532 ,p_source_id => NULL
2533 ,p_source_text => NULL
2534 ,p_action_information_category => 'ADDL EMPLOYEE DETAILS'
2535 ,p_action_information4 => fnd_date.date_to_displaydate(l_actual_termination_date)
2536 ,p_action_information5 => l_tax_card_type
2537 ,p_action_information6 => fnd_number.number_to_canonical(l_Tax_Percentage)
2538 ,p_action_information7 => fnd_number.number_to_canonical(l_Tax_Table_Number)
2539 ,p_action_information8 => fnd_number.number_to_canonical(l_Tax_Column)
2540 ,p_action_information9 => fnd_date.date_to_displaydate(l_tax_card_effective_date)
2541 ,p_action_information10 => fnd_number.number_to_canonical(l_Tax_Free_Threshold)
2542 ,p_action_information11 => fnd_number.number_to_canonical(l_Calculation_Code)
2543 ,p_action_information12 => fnd_number.number_to_canonical(l_Calculation_Sum)
2544 ,p_assignment_id => p_assignment_id);
2545
2546 -- fnd_file.put_line(fnd_file.log,' l_action_info_id '||l_action_info_id);
2547 --fnd_file.put_line(fnd_file.log,' p_archive_assact_id '||p_archive_assact_id);
2548 --fnd_file.put_line(fnd_file.log,' l_actual_termination_date '||l_actual_termination_date);
2549 -- fnd_file.put_line(fnd_file.log,' l_tax_card_type '||l_tax_card_type);
2550 -- fnd_file.put_line(fnd_file.log,' fnd_number.number_to_canonical(l_base_rate) '||fnd_number.number_to_canonical(l_Tax_Percentage));
2551
2552 -- removed Say Certificate
2553
2554 -- fnd_file.put_line(fnd_file.log,' Leaving Addl Employee details');
2555 EXCEPTION
2556 when others then
2557 NULL;
2558 END ARCHIVE_ADDL_EMP_DETAILS;
2559 /* ARCHIVE CODE */
2560 PROCEDURE ARCHIVE_CODE(p_assignment_action_id IN NUMBER
2561 ,p_effective_date IN DATE)
2562 IS
2563 /* Cursor to retrieve Payroll and Prepayment related Ids for Archival */
2564 CURSOR csr_archive_ids (p_locking_action_id NUMBER) IS
2565 SELECT prepay_assact.assignment_action_id prepay_assact_id
2566 ,prepay_assact.assignment_id prepay_assgt_id
2567 ,prepay_payact.payroll_action_id prepay_payact_id
2568 ,prepay_payact.effective_date prepay_effective_date
2569 ,run_assact.assignment_id run_assgt_id
2570 ,run_assact.assignment_action_id run_assact_id
2571 ,run_payact.payroll_action_id run_payact_id
2572 ,run_payact.payroll_id payroll_id
2573 FROM pay_action_interlocks archive_intlck
2574 ,pay_assignment_actions prepay_assact
2575 ,pay_payroll_actions prepay_payact
2576 ,pay_action_interlocks prepay_intlck
2577 ,pay_assignment_actions run_assact
2578 ,pay_payroll_actions run_payact
2579 WHERE archive_intlck.locking_action_id = p_locking_action_id
2580 AND prepay_assact.assignment_action_id = archive_intlck.locked_action_id
2581 AND prepay_payact.payroll_action_id = prepay_assact.payroll_action_id
2582 AND prepay_payact.action_type IN ('U','P')
2583 AND prepay_intlck.locking_action_id = prepay_assact.assignment_action_id
2584 AND run_assact.assignment_action_id = prepay_intlck.locked_action_id
2585 AND run_payact.payroll_action_id = run_assact.payroll_action_id
2586 AND run_payact.action_type IN ('Q', 'R')
2587 ORDER BY prepay_intlck.locking_action_id,prepay_intlck.locked_action_id desc;
2588 /* Cursor to retrieve time period information */
2589 CURSOR csr_period_end_date(p_assact_id NUMBER,p_pay_act_id NUMBER) IS
2590 SELECT ptp.end_date end_date,
2591 ptp.regular_payment_date regular_payment_date,
2592 ptp.time_period_id time_period_id,
2593 ppa.date_earned date_earned,
2594 ppa.effective_date effective_date,
2595 ptp.start_date start_date
2596 FROM per_time_periods ptp
2597 ,pay_payroll_actions ppa
2598 ,pay_assignment_actions paa
2599 WHERE ptp.payroll_id =ppa.payroll_id
2600 AND ppa.payroll_action_id =paa.payroll_action_id
2601 AND paa.assignment_action_id =p_assact_id
2602 AND ppa.payroll_action_id =p_pay_act_id
2603 AND ppa.date_earned BETWEEN ptp.start_date
2604 AND ptp.end_date;
2605 /* Cursor to retrieve Archive Payroll Action Id */
2606 CURSOR csr_archive_payact(p_assignment_action_id NUMBER) IS
2607 SELECT payroll_action_id
2608 FROM pay_assignment_actions
2609 WHERE assignment_Action_id = p_assignment_action_id;
2610 /* Cursor to retrieve Tax Unit Id for setting context */
2611 CURSOR csr_tax_unit (p_run_assact_id NUMBER) IS
2612 SELECT paa.tax_unit_id
2613 FROM pay_assignment_actions paa
2614 WHERE paa.assignment_action_id = p_run_assact_id;
2615
2616 l_tu_id NUMBER;
2617 l_archive_payact_id NUMBER;
2618 l_record_count NUMBER;
2619 l_actid NUMBER;
2620 l_end_date per_time_periods.end_date%TYPE;
2621 l_pre_end_date per_time_periods.end_date%TYPE;
2622 l_reg_payment_date per_time_periods.regular_payment_date%TYPE;
2623 l_pre_reg_payment_date per_time_periods.regular_payment_date%TYPE;
2624 l_date_earned pay_payroll_actions.date_earned%TYPE;
2625 l_pre_date_earned pay_payroll_actions.date_earned%TYPE;
2626 l_effective_date pay_payroll_actions.effective_date%TYPE;
2627 l_pre_effective_date pay_payroll_actions.effective_date%TYPE;
2628 l_run_payact_id NUMBER;
2629 l_action_context_id NUMBER;
2630 g_archive_pact NUMBER;
2631 p_assactid NUMBER;
2632 l_time_period_id per_time_periods.time_period_id%TYPE;
2633 l_pre_time_period_id per_time_periods.time_period_id%TYPE;
2634 l_start_date per_time_periods.start_date%TYPE;
2635 l_pre_start_date per_time_periods.start_date%TYPE;
2636 l_fnd_session NUMBER := 0;
2637 l_prev_prepay NUMBER := 0;
2638 BEGIN
2639 IF g_debug THEN
2640 hr_utility.set_location(' Entering Procedure ARCHIVE_CODE',380);
2641 END IF;
2642 --fnd_file.put_line(fnd_file.log,'Entering ARCHIVE_CODE ' || p_assignment_action_id );
2643
2644 OPEN csr_archive_payact(p_assignment_action_id);
2645 FETCH csr_archive_payact INTO l_archive_payact_id;
2646 CLOSE csr_archive_payact;
2647 l_record_count := 0;
2648 FOR rec_archive_ids IN csr_archive_ids(p_assignment_action_id) LOOP
2649
2650 OPEN csr_period_end_date(rec_archive_ids.run_assact_id,rec_archive_ids.run_payact_id);
2651 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;
2652 CLOSE csr_period_end_date;
2653 OPEN csr_period_end_date(rec_archive_ids.prepay_assact_id,rec_archive_ids.prepay_payact_id);
2654 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;
2655 CLOSE csr_period_end_date;
2656 OPEN csr_tax_unit(p_assignment_action_id);
2657 FETCH csr_tax_unit INTO l_tu_id;
2658 CLOSE csr_tax_unit;
2659
2660 --fnd_file.put_line(fnd_file.log,'p_assignment_action_id ' || p_assignment_action_id );
2661
2662 PAY_BALANCE_PKG.SET_CONTEXT('TAX_UNIT_ID',l_tu_id);
2663 PAY_BALANCE_PKG.SET_CONTEXT('DATE_EARNED',fnd_date.date_to_canonical(p_effective_date));
2664
2665 --fnd_file.put_line(fnd_file.log,'ARCHIVE_EMPLOYEE_DETAILS');
2666 -------------------------------------------------------------
2667 --Archive EMPLOYEE_DETAILS, PAYMENT_INFO and BALANCES
2668 --for every prepayment assignment action id
2669 -------------------------------------------------------------
2670 IF (rec_archive_ids.prepay_assact_id <> l_prev_prepay) THEN
2671
2672 ARCHIVE_EMPLOYEE_DETAILS
2673 (p_archive_assact_id => p_assignment_action_id
2674 ,p_assignment_id => rec_archive_ids.run_assgt_id
2675 ,p_assignment_action_id => rec_archive_ids.run_assact_id
2676 ,p_payroll_action_id => l_archive_payact_id
2677 ,p_time_period_id => l_time_period_id
2678 ,p_date_earned => l_date_earned
2679 ,p_pay_date_earned => l_date_earned
2680 ,p_effective_date => p_effective_date);
2681
2682 ARCHIVE_ADDL_EMP_DETAILS
2683 (p_archive_assact_id => p_assignment_action_id
2684 ,p_assignment_id => rec_archive_ids.run_assgt_id
2685 ,p_assignment_action_id => rec_archive_ids.run_assact_id
2686 ,p_effective_date => p_effective_date
2687 ,p_date_earned => l_date_earned);
2688
2689
2690 ARCHIVE_PAYMENT_INFO
2691 (p_archive_assact_id => p_assignment_action_id,
2692 p_prepay_assact_id => rec_archive_ids.prepay_assact_id,
2693 p_assignment_id => rec_archive_ids.prepay_assgt_id,
2694 p_date_earned => l_date_earned,
2695 p_effective_date => p_effective_date);
2696 --fnd_file.put_line(fnd_file.log,'ARCHIVE_OTH_BALANCE');
2697
2698 ARCHIVE_OTH_BALANCE
2699 (p_archive_assact_id => p_assignment_action_id,
2700 p_assignment_action_id => rec_archive_ids.run_assact_id,
2701 p_assignment_id => rec_archive_ids.run_assgt_id,
2702 p_payroll_action_id => l_archive_payact_id,
2703 p_record_count => l_record_count,
2704 p_pre_payact_id => rec_archive_ids.prepay_payact_id,
2705 p_effective_date => p_effective_date,
2706 p_date_earned => l_date_earned,
2707 p_archive_flag => 'Y');
2708 l_prev_prepay := rec_archive_ids.prepay_assact_id;
2709 END IF;
2710 /*fnd_file.put_line(fnd_file.log,'ARCHIVE_ACCRUAL_PLAN');*/
2711 /* ARCHIVE_ACCRUAL_PLAN (p_assignment_id => rec_archive_ids.run_assgt_id,
2712 p_date_earned => l_date_earned,
2713 p_effective_date => p_effective_date,
2714 p_archive_assact_id => p_assignment_action_id,
2715 p_run_assignment_action_id => rec_archive_ids.run_assact_id,
2716 p_period_end_date => l_end_date,
2717 p_period_start_date => l_start_date);*/
2718 --fnd_file.put_line(fnd_file.log,' vetr ARCHIVE_ADD_ELEMENT');
2719 ARCHIVE_ADD_ELEMENT
2720 (p_archive_assact_id => p_assignment_action_id,
2721 p_assignment_action_id => rec_archive_ids.run_assact_id,
2722 p_assignment_id => rec_archive_ids.run_assgt_id,
2723 p_payroll_action_id => l_archive_payact_id,
2724 p_date_earned => l_date_earned,
2725 p_effective_date => p_effective_date,
2726 p_pre_payact_id => rec_archive_ids.prepay_payact_id,
2727 p_archive_flag => 'Y');
2728 --fnd_file.put_line(fnd_file.log,'Assact id: '|| p_assignment_action_id);
2729
2730 ARCHIVE_MAIN_ELEMENTS (p_archive_assact_id => p_assignment_action_id,
2731 p_assignment_action_id => rec_archive_ids.run_assact_id,
2732 p_assignment_id => rec_archive_ids.run_assgt_id,
2733 p_date_earned => l_date_earned,
2734 p_effective_date => p_effective_date ) ;
2735
2736 l_record_count := l_record_count + 1;
2737 END LOOP;
2738 IF g_debug THEN
2739 hr_utility.set_location(' Leaving Procedure ARCHIVE_CODE',390);
2740 END IF;
2741 END ARCHIVE_CODE;
2742
2743 PROCEDURE ARCHIVE_MAIN_ELEMENTS
2744 (p_archive_assact_id IN NUMBER,
2745 p_assignment_action_id IN NUMBER,
2746 p_assignment_id IN NUMBER,
2747 p_date_earned IN DATE,
2748 p_effective_date IN DATE ) IS
2749
2750 -----------------------------------------------------------------------------
2751 /* Cursor to retrieve Earnings Element Information */
2752
2753 CURSOR csr_ear_element_info IS
2754 SELECT nvl(pettl.reporting_name,pettl.element_name) rep_name
2755 ,et.element_type_id element_type_id
2756 ,iv.input_value_id input_value_id
2757 ,iv.uom uom
2758 FROM pay_element_types_f et
2759 , pay_element_types_f_tl pettl
2760 , pay_input_values_f iv
2761 , pay_element_classifications classification
2762 WHERE et.element_type_id = iv.element_type_id
2763 AND et.element_type_id = pettl.element_type_id
2764 AND pettl.language = USERENV('LANG')
2765 AND iv.name = 'Pay Value'
2766 AND classification.classification_id = et.classification_id
2767 AND classification.classification_name IN
2768 ( 'Salary in Money'
2769 ,'Lumpsum'
2770 ,'Other Payments Subject to Tax'
2771 ,'Retrospective Payments'
2772 ,'Direct Payments'
2773 )
2774 AND p_date_earned BETWEEN et.effective_start_date
2775 AND et.effective_end_date
2776 AND p_date_earned BETWEEN iv.effective_start_date
2777 AND iv.effective_end_date
2778 AND ((et.business_group_id IS NULL AND et.legislation_code = 'SE')
2779 OR (et.business_group_id = g_business_group_id AND et.legislation_code IS NULL));
2780 /*
2781 SELECT nvl(pettl.reporting_name,pettl.element_name) rep_name
2782 ,et.element_type_id element_type_id
2783 ,iv.input_value_id input_value_id
2784 ,iv.uom uom
2785 ,code.org_information2 element_code
2786 FROM pay_element_types_f et
2787 , pay_element_types_f_tl pettl
2788 , pay_input_values_f iv
2789 , pay_element_classifications classification
2790 ,hr_organization_information code
2791 WHERE et.element_type_id = iv.element_type_id
2792 AND et.element_type_id = pettl.element_type_id
2793 AND pettl.language = USERENV('LANG')
2794 AND iv.name = 'Pay Value'
2795 AND classification.classification_id = et.classification_id
2796 AND classification.classification_name IN
2797 ('Absence'
2798 ,'Salary in Money'
2799 ,'Lumpsum'
2800 ,'Benefits in Kind'
2801 ,'Taxable Expenses'
2802 ,'Other Payments Subject to Tax'
2803 ,'Retrospective Payments'
2804 ,'Direct Payments'
2805 ,'Employer Charges'
2806 ,'External Expenses')
2807 AND p_date_earned BETWEEN et.effective_start_date
2808 AND et.effective_end_date
2809 AND p_date_earned BETWEEN iv.effective_start_date
2810 AND iv.effective_end_date
2811 AND ((et.business_group_id IS NULL AND et.legislation_code = 'SE')
2812 OR (et.business_group_id = g_business_group_id AND et.legislation_code IS NULL))
2813 and code.organization_id(+) = g_business_group_id
2814 and code.org_information_context (+)='SE_SOE_ELEMENT_ADD_DETAILS'
2815 and et.element_type_id = code.org_information1 (+);*/
2816
2817 ----------------------------------------------------------
2818 /* Cursor to retrieve Deduction Element Information */
2819
2820 CURSOR csr_ded_element_info IS
2821 SELECT nvl(pettl.reporting_name,pettl.element_name) rep_name
2822 ,et.element_type_id element_type_id
2823 ,iv.input_value_id input_value_id
2824 ,iv.uom uom
2825 FROM pay_element_types_f et
2826 , pay_element_types_f_tl pettl
2827 , pay_input_values_f iv
2828 , pay_element_classifications classification
2829 WHERE et.element_type_id = iv.element_type_id
2830 AND et.element_type_id = pettl.element_type_id
2831 AND pettl.language = USERENV('LANG')
2832 AND iv.name = 'Pay Value'
2833 AND classification.classification_id = et.classification_id
2834 AND classification.classification_name IN ('Involuntary Deductions'
2835 ,'Voluntary Deductions'
2836 ,'Statutory Deductions'
2837 ,'Pre-Tax Deductions')
2838 AND p_date_earned BETWEEN et.effective_start_date
2839 AND et.effective_end_date
2840 AND p_date_earned BETWEEN iv.effective_start_date
2841 AND iv.effective_end_date
2842 AND ((et.business_group_id IS NULL AND et.legislation_code = 'SE')
2843 OR (et.business_group_id = g_business_group_id AND et.legislation_code IS NULL));
2844 /*SELECT nvl(pettl.reporting_name,pettl.element_name) rep_name
2845 ,et.element_type_id element_type_id
2846 ,iv.input_value_id input_value_id
2847 ,iv.uom uom
2848 ,code.org_information2 element_code
2849 FROM pay_element_types_f et
2850 , pay_element_types_f_tl pettl
2851 , pay_input_values_f iv
2852 , pay_element_classifications classification
2853 ,hr_organization_information code
2854 WHERE et.element_type_id = iv.element_type_id
2855 AND et.element_type_id = pettl.element_type_id
2856 AND pettl.language = USERENV('LANG')
2857 AND iv.name = 'Pay Value'
2858 AND classification.classification_id = et.classification_id
2859 AND classification.classification_name IN ('Involuntary Deductions'
2860 ,'Voluntary Deductions'
2861 ,'Statutory Deductions')
2862 AND p_date_earned BETWEEN et.effective_start_date
2863 AND et.effective_end_date
2864 AND p_date_earned BETWEEN iv.effective_start_date
2865 AND iv.effective_end_date
2866 AND ((et.business_group_id IS NULL AND et.legislation_code = 'SE')
2867 OR (et.business_group_id = g_business_group_id AND et.legislation_code IS NULL))
2868 and code.organization_id(+) = g_business_group_id
2869 and code.org_information_context (+)='SE_SOE_ELEMENT_ADD_DETAILS'
2870 and et.element_type_id = code.org_information1 (+);*/
2871
2872
2873 -----------------------------------------------------------------------------
2874 /* Cursor to retrieve run result value of Main Elements */
2875 CURSOR csr_result_value(p_iv_id NUMBER
2876 ,p_ele_type_id NUMBER
2877 ,p_assignment_action_id NUMBER) IS
2878 SELECT rrv.result_value,rr.element_entry_id
2879 FROM pay_run_result_values rrv
2880 ,pay_run_results rr
2881 ,pay_assignment_actions paa
2882 ,pay_payroll_actions ppa
2883 WHERE rrv.input_value_id = p_iv_id
2884 AND rr.element_type_id = p_ele_type_id
2885 AND rr.run_result_id = rrv.run_result_id
2886 AND rr.assignment_action_id = paa.assignment_action_id
2887 AND paa.assignment_action_id = p_assignment_action_id
2888 AND ppa.payroll_action_id = paa.payroll_action_id
2889 AND ppa.action_type IN ('Q','R')
2890 AND rrv.result_value IS NOT NULL;
2891 -----------------------------------------------------------------------------
2892 /* Cursor to retrieve run result value of Main Elements */
2893 CURSOR csr_result_value_EE(p_iv_id NUMBER
2894 ,p_ele_type_id NUMBER
2895 ,p_assignment_action_id NUMBER
2896 ,p_EE_ID NUMBER) IS
2897 SELECT rrv.result_value
2898 FROM pay_run_result_values rrv
2899 ,pay_run_results rr
2900 ,pay_assignment_actions paa
2901 ,pay_payroll_actions ppa
2902 WHERE rrv.input_value_id = p_iv_id
2903 AND rr.element_type_id = p_ele_type_id
2904 AND rr.run_result_id = rrv.run_result_id
2905 AND rr.assignment_action_id = paa.assignment_action_id
2906 AND paa.assignment_action_id = p_assignment_action_id
2907 AND ppa.payroll_action_id = paa.payroll_action_id
2908 AND ppa.action_type IN ('Q','R')
2909 AND rrv.result_value IS NOT NULL
2910 AND rr.element_entry_id = p_EE_ID;
2911 -----------------------------------------------------------------------------
2912 -- Cursor to pick up segment from SE_SOE_ELEMENT_ADD_DETAILS context Org Developer DF
2913 CURSOR csr_group_by( p_ele_type_id NUMBER ) IS
2914 select ORG_INFORMATION3,ORG_INFORMATION6,ORG_INFORMATION7,ORG_INFORMATION8,ORG_INFORMATION9,ORG_INFORMATION10
2915 from hr_organization_information code
2916 where code.organization_id = g_business_group_id
2917 and code.org_information_context ='SE_SOE_ELEMENT_ADD_DETAILS'
2918 and code.org_information1 =p_ele_type_id;
2919 -----------------------------------------------------------------------------
2920 /* Cursor to retrieve sum of run result value for an given Main Element */
2921 -----------------------------------------------------------------------------
2922 CURSOR csr_sum_of_result_values(p_iv_id NUMBER
2923 ,p_ele_type_id NUMBER
2924 ,p_assignment_action_id NUMBER
2925 ) IS
2926 SELECT sum(rrv.result_value) result_value
2927 ,count(rrv.RUN_RESULT_ID) record_count
2928 ,rrv.result_value UNIT_PRICE
2929 FROM pay_run_result_values rrv
2930 ,pay_run_results rr
2931 ,pay_assignment_actions paa
2932 ,pay_payroll_actions ppa
2933 WHERE rrv.input_value_id = p_iv_id
2934 AND rr.element_type_id = p_ele_type_id
2935 AND rr.run_result_id = rrv.run_result_id
2936 AND rr.assignment_action_id = paa.assignment_action_id
2937 AND paa.assignment_action_id = p_assignment_action_id
2938 AND ppa.payroll_action_id = paa.payroll_action_id
2939 AND ppa.action_type IN ('Q','R')
2940 AND rrv.result_value IS NOT NULL
2941 group by rrv.result_value;
2942 /* CURSOR csr_sum_of_result_values(p_iv_id NUMBER
2943 ,p_ele_type_id NUMBER
2944 ,p_assignment_action_id NUMBER
2945 ,p_group_by NUMBER) IS
2946 SELECT sum(rrv.result_value) result_value
2947 ,count(rrv.RUN_RESULT_ID) record_count
2948 ,rrv.result_value UNIT_PRICE
2949 FROM pay_run_result_values pr
2950 ,pay_run_result_values rrv
2951 ,pay_run_results rr
2952 ,pay_assignment_actions paa
2953 ,pay_payroll_actions ppa
2954 WHERE pr.input_value_id(+) = p_group_by
2955 AND rrv.input_value_id = p_iv_id
2956 AND rr.element_type_id = p_ele_type_id
2957 AND rr.run_result_id = rrv.run_result_id
2958 AND rr.run_result_id = pr.run_result_id (+)
2959 AND rr.assignment_action_id = paa.assignment_action_id
2960 AND paa.assignment_action_id = p_assignment_action_id
2961 AND ppa.payroll_action_id = paa.payroll_action_id
2962 AND ppa.action_type IN ('Q','R')
2963 AND rrv.result_value IS NOT NULL
2964 --AND pr.result_value IS NOT NULL
2965 group by pr.result_value,rrv.result_value;*/
2966 /*CURSOR csr_sum_of_result_values(p_iv_id NUMBER
2967 ,p_ele_type_id NUMBER
2968 ,p_assignment_action_id NUMBER) IS
2969 SELECT sum(rrv.result_value) result_value
2970 FROM pay_run_result_values rrv
2971 ,pay_run_results rr
2972 ,pay_assignment_actions paa
2973 ,pay_payroll_actions ppa
2974 WHERE rrv.input_value_id = p_iv_id
2975 AND rr.element_type_id = p_ele_type_id
2976 AND rr.run_result_id = rrv.run_result_id
2977 AND rr.assignment_action_id = paa.assignment_action_id
2978 AND paa.assignment_action_id = p_assignment_action_id
2979 AND ppa.payroll_action_id = paa.payroll_action_id
2980 AND ppa.action_type IN ('Q','R')
2981 AND rrv.result_value IS NOT NULL
2982 group by rrv.result_value;
2983 */
2984
2985
2986 rec_group_by csr_group_by%ROWTYPE;
2987 l_result_value pay_run_result_values.result_value%TYPE := 0;
2988
2989 -----------------------------------------------------------------------------
2990 /* Cursor to retrieve sum of all run result value for an given Main Element */
2991 -----------------------------------------------------------------------------
2992 CURSOR csr_all_sum_result_values(p_iv_id_UNIT NUMBER,p_iv_id_AMOUNT NUMBER,p_iv_id_UNIT_PRICE NUMBER
2993 ,p_ele_type_id NUMBER
2994 ,p_assignment_action_id NUMBER
2995 ) IS
2996 SELECT rrv3.result_value UNIT_PRICE , sum(rrv1.result_value) UNIT, sum(rrv2.result_value) AMOUNT
2997 FROM pay_run_result_values rrv1
2998 ,pay_run_results rr1
2999 ,pay_assignment_actions paa
3000 ,pay_payroll_actions ppa
3001 ,pay_run_result_values rrv2
3002 ,pay_run_results rr2
3003 ,pay_run_result_values rrv3
3004 ,pay_run_results rr3
3005 WHERE rrv1.input_value_id = p_iv_id_UNIT
3006 AND rr1.element_type_id = p_ele_type_id
3007 AND rr1.run_result_id = rrv1.run_result_id
3008 AND rr1.assignment_action_id = paa.assignment_action_id
3009 AND paa.assignment_action_id = p_assignment_action_id
3010 AND ppa.payroll_action_id = paa.payroll_action_id
3011 AND ppa.action_type IN ('Q','R')
3012 and rrv2.input_value_id = p_iv_id_AMOUNT
3013 AND rr2.run_result_id = rrv2.run_result_id
3014 AND rr2.element_entry_id = rr1.element_entry_id
3015 and rrv3.input_value_id = p_iv_id_UNIT_PRICE
3016 AND rr3.run_result_id = rrv3.run_result_id
3017 AND rr3.element_entry_id = rr1.element_entry_id
3018 group by rrv3.result_value;
3019
3020 -----------------------------------------------------------------------------
3021 -----------------------------------------------------------------------------
3022 /* Cursor to retrieve sum of all run result value for an given Main Element */
3023 -----------------------------------------------------------------------------
3024 CURSOR csr_all_result_values(p_iv_id_UNIT NUMBER,p_iv_id_AMOUNT NUMBER,p_iv_id_UNIT_PRICE NUMBER
3025 ,p_ele_type_id NUMBER
3026 ,p_assignment_action_id NUMBER
3027 ) IS
3028 SELECT rrv3.result_value UNIT_PRICE , rrv1.result_value UNIT, rrv2.result_value AMOUNT
3029 FROM pay_run_result_values rrv1
3030 ,pay_run_results rr1
3031 ,pay_assignment_actions paa
3032 ,pay_payroll_actions ppa
3033 ,pay_run_result_values rrv2
3034 ,pay_run_results rr2
3035 ,pay_run_result_values rrv3
3036 ,pay_run_results rr3
3037 WHERE rrv1.input_value_id = p_iv_id_UNIT
3038 AND rr1.element_type_id = p_ele_type_id
3039 AND rr1.run_result_id = rrv1.run_result_id
3040 AND rr1.assignment_action_id = paa.assignment_action_id
3041 AND paa.assignment_action_id = p_assignment_action_id
3042 AND ppa.payroll_action_id = paa.payroll_action_id
3043 AND ppa.action_type IN ('Q','R')
3044 and rrv2.input_value_id = p_iv_id_AMOUNT
3045 AND rr2.run_result_id = rrv2.run_result_id
3046 AND rr2.element_entry_id = rr1.element_entry_id
3047 and rrv3.input_value_id = p_iv_id_UNIT_PRICE
3048 AND rr3.run_result_id = rrv3.run_result_id
3049 AND rr3.element_entry_id = rr1.element_entry_id ;
3050
3051
3052 -----------------------------------------------------------------------------
3053
3054
3055 l_action_info_id NUMBER;
3056 l_ovn NUMBER;
3057 l_element_context VARCHAR2(10);
3058 l_index NUMBER := 0;
3059 l_formatted_value VARCHAR2(50) := NULL;
3060 l_flag NUMBER := 0;
3061 l_group_by number;
3062 l_unit_price NUMBER ;
3063 l_unit NUMBER ;
3064 l_amount NUMBER ;
3065 -----------------------------------------------------------------------------
3066
3067 BEGIN
3068
3069 IF g_debug THEN
3070 hr_utility.set_location(' Entering Procedure ARCHIVE_MAIN_ELEMENTS',320);
3071 END IF;
3072
3073 -- Archiving Earnings Elements
3074 FOR csr_rec IN csr_ear_element_info LOOP
3075
3076 l_result_value := NULL;
3077 l_group_by :=null;
3078 l_unit_price :=null;
3079
3080 BEGIN
3081
3082 OPEN csr_group_by(csr_rec.element_type_id );
3083 FETCH csr_group_by
3084 INTO rec_group_by;
3085 CLOSE csr_group_by;
3086 -- The se_soe contains
3087 -- segment 3 = > I or O
3088 -- segment 6 = > Y or N
3089 -- segment 7 = > Input ID UNIT
3090 -- segment 8 = > Input ID UNIT PRICE
3091 -- segment 9 = > Input ID Amount
3092
3093 IF ( (rec_group_by.ORG_INFORMATION3 = 'O') or (rec_group_by.ORG_INFORMATION3 IS NULL) )
3094 THEN
3095 -- Case for Group by or NOT
3096 -- Segemnt 6 is allowed here, as it makes sense.
3097 IF ( rec_group_by.ORG_INFORMATION6 = 'N' or (rec_group_by.ORG_INFORMATION3 IS NULL) )
3098 THEN
3099 -- This csae iis for individual representation of each element.
3100 -- unit and unit price should be absent.
3101 FOR csr_result_rec IN csr_result_value(csr_rec.input_value_id ,csr_rec.element_type_id ,p_assignment_action_id)
3102 LOOP
3103 IF csr_result_rec.result_value is not null THEN
3104 pay_action_information_api.create_action_information (
3105 p_action_information_id => l_action_info_id
3106 ,p_action_context_id => p_archive_assact_id
3107 ,p_action_context_type => 'AAP'
3108 ,p_object_version_number => l_ovn
3109 ,p_effective_date => p_effective_date
3110 ,p_source_id => NULL
3111 ,p_source_text => NULL
3112 ,p_action_information_category => 'EMEA ELEMENT INFO'
3113 ,p_action_information1 => csr_rec.element_type_id
3114 ,p_action_information2 => csr_rec.input_value_id
3115 ,p_action_information3 => 'E'
3116 ,p_action_information4 => fnd_number.number_to_canonical(csr_result_rec.result_value) --l_formatted_value
3117 ,p_action_information8 => ''
3118 ,p_action_information9 => 'Earning Element:'
3119 ,p_assignment_id => p_assignment_id);
3120
3121 --fnd_file.put_line(fnd_file.log,'group by NO l_action_info_id ' || l_action_info_id );
3122 --fnd_file.put_line(fnd_file.log,'Earning Element ');
3123 --fnd_file.put_line(fnd_file.log,'csr_rec.element_type_id ' || csr_rec.element_type_id );
3124 --fnd_file.put_line(fnd_file.log,'csr_rec.input_value_id ' || csr_rec.input_value_id );
3125 --fnd_file.put_line(fnd_file.log,'fnd_number.number_to_canonical(l_result_value) ' || fnd_number.number_to_canonical(csr_result_rec.result_value) );
3126 END IF;
3127 END LOOP;
3128
3129 ELSIF rec_group_by.ORG_INFORMATION6 = 'Y'
3130 THEN
3131 -- This csae iis for Grouping by pay value of each element.
3132 -- unit and unit price should be present
3133 FOR csr_result_rec IN csr_sum_of_result_values(csr_rec.input_value_id
3134 ,csr_rec.element_type_id
3135 ,p_assignment_action_id )
3136 LOOP
3137 IF csr_result_rec.result_value is not null THEN
3138 pay_action_information_api.create_action_information (
3139 p_action_information_id => l_action_info_id
3140 ,p_action_context_id => p_archive_assact_id
3141 ,p_action_context_type => 'AAP'
3142 ,p_object_version_number => l_ovn
3143 ,p_effective_date => p_effective_date
3144 ,p_source_id => NULL
3145 ,p_source_text => NULL
3146 ,p_action_information_category => 'EMEA ELEMENT INFO'
3147 ,p_action_information1 => csr_rec.element_type_id
3148 ,p_action_information2 => csr_rec.input_value_id
3149 ,p_action_information3 => 'E'
3150 ,p_action_information4 => fnd_number.number_to_canonical(csr_result_rec.result_value) --l_formatted_value
3151 ,p_action_information8 => csr_result_rec.record_count
3152 ,p_action_information9 => 'Earning Element unit per price:'||csr_result_rec.UNIT_PRICE
3153 ,p_assignment_id => p_assignment_id);
3154
3155 --fnd_file.put_line(fnd_file.log,'group by NO l_action_info_id ' || l_action_info_id );
3156 --fnd_file.put_line(fnd_file.log,'Earning Element ');
3157 --fnd_file.put_line(fnd_file.log,'csr_rec.element_type_id ' || csr_rec.element_type_id );
3158 --fnd_file.put_line(fnd_file.log,'csr_rec.input_value_id ' || csr_rec.input_value_id );
3159 --fnd_file.put_line(fnd_file.log,'fnd_number.number_to_canonical(l_result_value) ' || fnd_number.number_to_canonical(csr_result_rec.result_value) );
3160 END IF;
3161 END LOOP;
3162 END IF;
3163 ELSIF rec_group_by.ORG_INFORMATION3 = 'I'
3164 THEN
3165 -- Case for UNIT,PRICE,AMOUNT
3166 -- Segment 7,8,9 is allowed
3167 -- Segemnt 6 is not allowed here, as it doesn't makes sense.
3168 -- segment 7 = > Input ID UNIT
3169 -- segment 8 = > Input ID UNIT PRICE
3170 -- segment 9 = > Input ID Amount
3171 IF rec_group_by.ORG_INFORMATION7 IS NOT NULL AND
3172 rec_group_by.ORG_INFORMATION8 IS NOT NULL AND
3173 rec_group_by.ORG_INFORMATION9 IS NOT NULL
3174 THEN
3175 -- All three are selected, we can group by three in single query
3176
3177
3178 IF rec_group_by.ORG_INFORMATION10 = 'Y'
3179 THEN
3180 FOR csr_result_rec IN csr_all_sum_result_values(rec_group_by.ORG_INFORMATION7
3181 ,rec_group_by.ORG_INFORMATION9
3182 ,rec_group_by.ORG_INFORMATION8
3183 ,csr_rec.element_type_id
3184 ,p_assignment_action_id )
3185 LOOP
3186 IF csr_result_rec.AMOUNT is not null THEN
3187 pay_action_information_api.create_action_information (
3188 p_action_information_id => l_action_info_id
3189 ,p_action_context_id => p_archive_assact_id
3190 ,p_action_context_type => 'AAP'
3191 ,p_object_version_number => l_ovn
3192 ,p_effective_date => p_effective_date
3193 ,p_source_id => NULL
3194 ,p_source_text => NULL
3195 ,p_action_information_category => 'EMEA ELEMENT INFO'
3196 ,p_action_information1 => csr_rec.element_type_id
3197 ,p_action_information2 => csr_rec.input_value_id
3198 ,p_action_information3 => 'E'
3199 ,p_action_information4 => fnd_number.number_to_canonical(csr_result_rec.AMOUNT) --l_formatted_value
3200 ,p_action_information8 => csr_result_rec.UNIT
3201 ,p_action_information9 => 'Earning Element unit per price:'||csr_result_rec.UNIT_PRICE
3202 ,p_assignment_id => p_assignment_id);
3203
3204 --fnd_file.put_line(fnd_file.log,'group by NO l_action_info_id ' || l_action_info_id );
3205 --fnd_file.put_line(fnd_file.log,'Earning Element ');
3206 --fnd_file.put_line(fnd_file.log,'csr_result_rec.UNIT ' || csr_result_rec.UNIT );
3207 --fnd_file.put_line(fnd_file.log,'csr_result_rec.WP WP ' || csr_result_rec.UNIT_PRICE );
3208 --fnd_file.put_line(fnd_file.log,'fnd_number.number_to_canonical(l_result_value) ' || fnd_number.number_to_canonical(csr_result_rec.AMOUNT) );
3209 END IF;
3210 END LOOP;
3211 ELSE
3212 FOR csr_result_rec IN csr_all_result_values(rec_group_by.ORG_INFORMATION7
3213 ,rec_group_by.ORG_INFORMATION9
3214 ,rec_group_by.ORG_INFORMATION8
3215 ,csr_rec.element_type_id
3216 ,p_assignment_action_id )
3217 LOOP
3218 IF csr_result_rec.AMOUNT is not null THEN
3219 pay_action_information_api.create_action_information (
3220 p_action_information_id => l_action_info_id
3221 ,p_action_context_id => p_archive_assact_id
3222 ,p_action_context_type => 'AAP'
3223 ,p_object_version_number => l_ovn
3224 ,p_effective_date => p_effective_date
3225 ,p_source_id => NULL
3226 ,p_source_text => NULL
3227 ,p_action_information_category => 'EMEA ELEMENT INFO'
3228 ,p_action_information1 => csr_rec.element_type_id
3229 ,p_action_information2 => csr_rec.input_value_id
3230 ,p_action_information3 => 'E'
3231 ,p_action_information4 => fnd_number.number_to_canonical(csr_result_rec.AMOUNT) --l_formatted_value
3232 ,p_action_information8 => csr_result_rec.UNIT
3233 ,p_action_information9 => 'Earning Element unit per price:'||csr_result_rec.UNIT_PRICE
3234 ,p_assignment_id => p_assignment_id);
3235
3236 --fnd_file.put_line(fnd_file.log,'group by NO l_action_info_id ' || l_action_info_id );
3237 --fnd_file.put_line(fnd_file.log,'Earning Element ');
3238 --fnd_file.put_line(fnd_file.log,'csr_result_rec.UNIT ' || csr_result_rec.UNIT );
3239 --fnd_file.put_line(fnd_file.log,'csr_result_rec.WP WP ' || csr_result_rec.UNIT_PRICE );
3240 --fnd_file.put_line(fnd_file.log,'fnd_number.number_to_canonical(l_result_value) ' || fnd_number.number_to_canonical(csr_result_rec.AMOUNT) );
3241 END IF; -- end of csr_result_rec.AMOUNT is not null
3242 END LOOP;
3243
3244 END IF; -- end of rec_group_by.ORG_INFORMATION10 = 'Y'
3245 ELSE -- Three inputs are not selected.
3246 -- have to get the each input value id and find value for each
3247 -- and archive it if the amount is not null
3248
3249 -- Case for UNIT,PRICE,AMOUNT
3250 -- Segment 7,8,9 is allowed
3251 -- Segemnt 6 is not allowed here, as it doesn't makes sense.
3252 -- segment 7 = > Input ID UNIT
3253 -- segment 8 = > Input ID UNIT PRICE
3254 -- segment 9 = > Input ID Amount
3255 IF rec_group_by.ORG_INFORMATION9 IS NOT NULL
3256 THEN
3257 -- amount should not be null
3258 -- find the amount value and element entry id of this element
3259 -- for this element entry find the the other selected columns like UNIT , UNIT PRICE if selected
3260 --
3261 FOR csr_result_rec IN csr_result_value(rec_group_by.ORG_INFORMATION9 ,csr_rec.element_type_id ,p_assignment_action_id)
3262 LOOP
3263
3264 -- we have EE id
3265 l_amount := csr_result_rec.result_value;
3266 IF rec_group_by.ORG_INFORMATION8 IS NOT NULL
3267 THEN
3268 OPEN csr_result_value_EE(rec_group_by.ORG_INFORMATION8 ,csr_rec.element_type_id ,p_assignment_action_id,csr_result_rec.element_entry_id );
3269 FETCH csr_result_value_EE
3270 INTO l_unit_price;
3271 CLOSE csr_result_value_EE;
3272 ELSE
3273 l_unit_price :=NULL;
3274 END IF; -- End if of segment 8 , unit price
3275
3276 IF rec_group_by.ORG_INFORMATION7 IS NOT NULL
3277 THEN
3278 OPEN csr_result_value_EE(rec_group_by.ORG_INFORMATION8 ,csr_rec.element_type_id ,p_assignment_action_id,csr_result_rec.element_entry_id );
3279 FETCH csr_result_value_EE
3280 INTO l_unit;
3281 CLOSE csr_result_value_EE;
3282 ELSE
3283 l_unit :=NULL;
3284 END IF; -- End if of segment 7 , unit
3285
3286 -- Resume again
3287 IF csr_result_rec.result_value is not null THEN
3288 pay_action_information_api.create_action_information (
3289 p_action_information_id => l_action_info_id
3290 ,p_action_context_id => p_archive_assact_id
3291 ,p_action_context_type => 'AAP'
3292 ,p_object_version_number => l_ovn
3293 ,p_effective_date => p_effective_date
3294 ,p_source_id => NULL
3295 ,p_source_text => NULL
3296 ,p_action_information_category => 'EMEA ELEMENT INFO'
3297 ,p_action_information1 => csr_rec.element_type_id
3298 ,p_action_information2 => csr_rec.input_value_id
3299 ,p_action_information3 => 'E'
3300 ,p_action_information4 => fnd_number.number_to_canonical(l_amount) --l_formatted_value
3301 ,p_action_information8 => l_unit
3302 ,p_action_information9 => 'Earning Element unit per price:'||l_uNIT_PRICE
3303 ,p_assignment_id => p_assignment_id);
3304
3305 --fnd_file.put_line(fnd_file.log,'group by NO l_action_info_id ' || l_action_info_id );
3306 --fnd_file.put_line(fnd_file.log,'Earning Element ');
3307 --fnd_file.put_line(fnd_file.log,'csr_rec.element_type_id ' || csr_rec.element_type_id );
3308 --fnd_file.put_line(fnd_file.log,'csr_rec.input_value_id ' || csr_rec.input_value_id );
3309 --fnd_file.put_line(fnd_file.log,'fnd_number.number_to_canonical(l_result_value) ' || fnd_number.number_to_canonical(csr_result_rec.result_value) );
3310 END IF; -- end of csr_result_rec.result_value is not null
3311 END LOOP;
3312 END IF; -- end of rec_group_by.ORG_INFORMATION9 IS NOT NULL
3313
3314 END IF;
3315
3316 END IF;
3317 /*
3318 FOR csr_result_rec IN csr_sum_of_result_values(csr_rec.input_value_id
3319 ,csr_rec.element_type_id
3320 ,p_assignment_action_id
3321 ,rec_group_by.ORG_INFORMATION3)
3322 LOOP
3323 IF csr_result_rec.result_value is not null THEN
3324 pay_action_information_api.create_action_information (
3325 p_action_information_id => l_action_info_id
3326 ,p_action_context_id => p_archive_assact_id
3327 ,p_action_context_type => 'AAP'
3328 ,p_object_version_number => l_ovn
3329 ,p_effective_date => p_effective_date
3330 ,p_source_id => NULL
3331 ,p_source_text => NULL
3332 ,p_action_information_category => 'EMEA ELEMENT INFO'
3333 ,p_action_information1 => csr_rec.element_type_id
3334 ,p_action_information2 => csr_rec.input_value_id
3335 ,p_action_information3 => 'E'
3336 ,p_action_information4 => fnd_number.number_to_canonical(csr_result_rec.result_value) --l_formatted_value
3337 ,p_action_information8 => csr_result_rec.record_count
3338 ,p_action_information9 => 'Earning Element unit per price:'||csr_result_rec.UNIT_PRICE
3339 ,p_assignment_id => p_assignment_id);
3340
3341 --fnd_file.put_line(fnd_file.log,'group by NO l_action_info_id ' || l_action_info_id );
3342 --fnd_file.put_line(fnd_file.log,'Earning Element ');
3343 --fnd_file.put_line(fnd_file.log,'csr_rec.element_type_id ' || csr_rec.element_type_id );
3344 --fnd_file.put_line(fnd_file.log,'csr_rec.input_value_id ' || csr_rec.input_value_id );
3345 --fnd_file.put_line(fnd_file.log,'fnd_number.number_to_canonical(l_result_value) ' || fnd_number.number_to_canonical(csr_result_rec.result_value) );
3346 END IF;
3347 END LOOP;
3348 */
3349 --OPEN csr_result_value(csr_rec.input_value_id ,csr_rec.element_type_id ,p_assignment_action_id);
3350 --FETCH csr_result_value INTO l_result_value;
3351 --CLOSE csr_result_value;
3352 /*OPEN csr_group_by(csr_rec.element_type_id );
3353 FETCH csr_group_by INTO l_group_by;
3354 CLOSE csr_group_by;
3355 IF l_group_by !='Y'
3356 THEN
3357 FOR csr_result_rec IN csr_result_value(csr_rec.input_value_id ,csr_rec.element_type_id ,p_assignment_action_id)
3358 LOOP
3359 IF csr_result_rec.result_value is not null THEN
3360 pay_action_information_api.create_action_information (
3361 p_action_information_id => l_action_info_id
3362 ,p_action_context_id => p_archive_assact_id
3363 ,p_action_context_type => 'AAP'
3364 ,p_object_version_number => l_ovn
3365 ,p_effective_date => p_effective_date
3366 ,p_source_id => NULL
3367 ,p_source_text => NULL
3368 ,p_action_information_category => 'EMEA ELEMENT INFO'
3369 ,p_action_information1 => csr_rec.element_type_id
3370 ,p_action_information2 => csr_rec.input_value_id
3371 ,p_action_information3 => 'E'
3372 ,p_action_information4 => fnd_number.number_to_canonical(csr_result_rec.result_value) --l_formatted_value
3373 --,p_action_information5 => csr_rec.element_code
3374 ,p_action_information9 => 'Earning Element'
3375 ,p_assignment_id => p_assignment_id);
3376
3377 --fnd_file.put_line(fnd_file.log,'group by NO l_action_info_id ' || l_action_info_id );
3378 --fnd_file.put_line(fnd_file.log,'Earning Element ');
3379 --fnd_file.put_line(fnd_file.log,'csr_rec.element_type_id ' || csr_rec.element_type_id );
3380 --fnd_file.put_line(fnd_file.log,'csr_rec.input_value_id ' || csr_rec.input_value_id );
3381 --fnd_file.put_line(fnd_file.log,'fnd_number.number_to_canonical(l_result_value) ' || fnd_number.number_to_canonical(csr_result_rec.result_value) );
3382 END IF;
3383 END LOOP;
3384 --l_group_by :=NULL;
3385 --ELSE
3386
3387 OPEN csr_sum_of_result_values(csr_rec.input_value_id ,csr_rec.element_type_id ,p_assignment_action_id);
3388 FETCH csr_sum_of_result_values INTO l_result_value;
3389 CLOSE csr_sum_of_result_values;
3390
3391
3392 IF l_result_value is not null THEN
3393 pay_action_information_api.create_action_information (
3394 p_action_information_id => l_action_info_id
3395 ,p_action_context_id => p_archive_assact_id
3396 ,p_action_context_type => 'AAP'
3397 ,p_object_version_number => l_ovn
3398 ,p_effective_date => p_effective_date
3399 ,p_source_id => NULL
3400 ,p_source_text => NULL
3401 ,p_action_information_category => 'EMEA ELEMENT INFO'
3402 ,p_action_information1 => csr_rec.element_type_id
3403 ,p_action_information2 => csr_rec.input_value_id
3404 ,p_action_information3 => 'E'
3405 ,p_action_information4 => fnd_number.number_to_canonical(l_result_value) --l_formatted_value
3406 --,p_action_information5 => csr_rec.element_code
3407 ,p_action_information9 => 'Earning Element'
3408 ,p_assignment_id => p_assignment_id);
3409
3410 --fnd_file.put_line(fnd_file.log,'GROUP BY YES l_action_info_id ' || l_action_info_id );
3411 --fnd_file.put_line(fnd_file.log,'Earning Element ');
3412 --fnd_file.put_line(fnd_file.log,'csr_rec.element_type_id ' || csr_rec.element_type_id );
3413 --fnd_file.put_line(fnd_file.log,'csr_rec.input_value_id ' || csr_rec.input_value_id );
3414 --fnd_file.put_line(fnd_file.log,'fnd_number.number_to_canonical(l_result_value) ' || fnd_number.number_to_canonical(l_result_value) );
3415 END IF;
3416 l_group_by :=NULL;
3417 END IF; -- end if for the l_group_by*/
3418
3419 EXCEPTION WHEN OTHERS THEN
3420 g_err_num := SQLCODE;
3421 /*fnd_file.put_line(fnd_file.log,'ORA_ERR: ' || g_err_num || 'In ARCHIVE_MAIN_ELEMENTS');*/
3422
3423 IF g_debug THEN
3424 hr_utility.set_location('ORA_ERR: ' || g_err_num || 'In ARCHIVE_MAIN_ELEMENTS',330);
3425 END IF;
3426 END;
3427 END LOOP;
3428
3429
3430
3431 -- Archiving Deduction Elements
3432
3433 FOR csr_rec IN csr_ded_element_info LOOP
3434
3435 l_result_value := NULL;
3436 rec_group_by := NULL;
3437
3438 BEGIN
3439 OPEN csr_group_by(csr_rec.element_type_id );
3440 FETCH csr_group_by
3441 INTO rec_group_by;
3442 CLOSE csr_group_by;
3443 -- The se_soe contains
3444 -- segment 3 = > I or O
3445 -- segment 6 = > Y or N
3446 -- segment 7 = > Input ID UNIT
3447 -- segment 8 = > Input ID UNIT PRICE
3448 -- segment 9 = > Input ID Amount
3449 --fnd_file.put_line(fnd_file.log,'Vetri1 : ' || 'In ARCHIVE_MAIN_ELEMENTS'||rec_group_by.ORG_INFORMATION3);
3450
3451 IF ( (rec_group_by.ORG_INFORMATION3 = 'O') or (rec_group_by.ORG_INFORMATION3 IS NULL) )
3452 THEN
3453 -- Case for Group by or NOT
3454 -- Segemnt 6 is allowed here, as it makes sense.
3455 --fnd_file.put_line(fnd_file.log,'Vetri 2: ' || 'In ARCHIVE_MAIN_ELEMENTS'||rec_group_by.ORG_INFORMATION3);
3456 IF ( rec_group_by.ORG_INFORMATION6 = 'N' or (rec_group_by.ORG_INFORMATION3 IS NULL) )
3457 THEN
3458 -- This csae iis for individual representation of each element.
3459 -- unit and unit price should be absent.
3460 --fnd_file.put_line(fnd_file.log,'Vetri 3: ' || 'In ARCHIVE_MAIN_ELEMENTS'||rec_group_by.ORG_INFORMATION3);
3461 FOR csr_result_rec IN csr_result_value(csr_rec.input_value_id ,csr_rec.element_type_id ,p_assignment_action_id)
3462 LOOP
3463 IF csr_result_rec.result_value is not null THEN
3464 pay_action_information_api.create_action_information (
3465 p_action_information_id => l_action_info_id
3466 ,p_action_context_id => p_archive_assact_id
3467 ,p_action_context_type => 'AAP'
3468 ,p_object_version_number => l_ovn
3469 ,p_effective_date => p_effective_date
3470 ,p_source_id => NULL
3471 ,p_source_text => NULL
3472 ,p_action_information_category => 'EMEA ELEMENT INFO'
3473 ,p_action_information1 => csr_rec.element_type_id
3474 ,p_action_information2 => csr_rec.input_value_id
3475 ,p_action_information3 => 'D'
3476 ,p_action_information4 => fnd_number.number_to_canonical(csr_result_rec.result_value) --l_formatted_value
3477 ,p_action_information8 => ''
3478 ,p_action_information9 => 'Deduction Element:'
3479 ,p_assignment_id => p_assignment_id);
3480
3481 --fnd_file.put_line(fnd_file.log,'group by NO l_action_info_id ' || l_action_info_id );
3482 --fnd_file.put_line(fnd_file.log,'Earning Element ');
3483 --fnd_file.put_line(fnd_file.log,'csr_rec.element_type_id ' || csr_rec.element_type_id );
3484 --fnd_file.put_line(fnd_file.log,'csr_rec.input_value_id ' || csr_rec.input_value_id );
3485 --fnd_file.put_line(fnd_file.log,'fnd_number.number_to_canonical(l_result_value) ' || fnd_number.number_to_canonical(csr_result_rec.result_value) );
3486 END IF;
3487 END LOOP;
3488
3489 ELSIF rec_group_by.ORG_INFORMATION6 = 'Y'
3490 THEN
3491 -- This csae iis for Grouping by pay value of each element.
3492 -- unit and unit price should be present
3493 FOR csr_result_rec IN csr_sum_of_result_values(csr_rec.input_value_id
3494 ,csr_rec.element_type_id
3495 ,p_assignment_action_id )
3496 LOOP
3497 IF csr_result_rec.result_value is not null THEN
3498 pay_action_information_api.create_action_information (
3499 p_action_information_id => l_action_info_id
3500 ,p_action_context_id => p_archive_assact_id
3501 ,p_action_context_type => 'AAP'
3502 ,p_object_version_number => l_ovn
3503 ,p_effective_date => p_effective_date
3504 ,p_source_id => NULL
3505 ,p_source_text => NULL
3506 ,p_action_information_category => 'EMEA ELEMENT INFO'
3507 ,p_action_information1 => csr_rec.element_type_id
3508 ,p_action_information2 => csr_rec.input_value_id
3509 ,p_action_information3 => 'D'
3510 ,p_action_information4 => fnd_number.number_to_canonical(csr_result_rec.result_value) --l_formatted_value
3511 ,p_action_information8 => csr_result_rec.record_count
3512 ,p_action_information9 => 'Deduction Element unit per price:'||csr_result_rec.UNIT_PRICE
3513 ,p_assignment_id => p_assignment_id);
3514
3515 --fnd_file.put_line(fnd_file.log,'group by NO l_action_info_id ' || l_action_info_id );
3516 --fnd_file.put_line(fnd_file.log,'Earning Element ');
3517 --fnd_file.put_line(fnd_file.log,'csr_rec.element_type_id ' || csr_rec.element_type_id );
3518 --fnd_file.put_line(fnd_file.log,'csr_rec.input_value_id ' || csr_rec.input_value_id );
3519 --fnd_file.put_line(fnd_file.log,'fnd_number.number_to_canonical(l_result_value) ' || fnd_number.number_to_canonical(csr_result_rec.result_value) );
3520 END IF;
3521 END LOOP;
3522 END IF;
3523 ELSIF rec_group_by.ORG_INFORMATION3 = 'I'
3524 THEN
3525 -- Case for UNIT,PRICE,AMOUNT
3526 -- Segment 7,8,9 is allowed
3527 -- Segemnt 6 is not allowed here, as it doesn't makes sense.
3528 -- segment 7 = > Input ID UNIT
3529 -- segment 8 = > Input ID UNIT PRICE
3530 -- segment 9 = > Input ID Amount
3531 IF rec_group_by.ORG_INFORMATION7 IS NOT NULL AND
3532 rec_group_by.ORG_INFORMATION8 IS NOT NULL AND
3533 rec_group_by.ORG_INFORMATION9 IS NOT NULL
3534 THEN
3535 -- All three are selected, we can group by three in single query
3536 IF rec_group_by.ORG_INFORMATION10 = 'Y'
3537 THEN
3538 FOR csr_result_rec IN csr_all_sum_result_values(rec_group_by.ORG_INFORMATION7
3539 ,rec_group_by.ORG_INFORMATION9
3540 ,rec_group_by.ORG_INFORMATION8
3541 ,csr_rec.element_type_id
3542 ,p_assignment_action_id )
3543 LOOP
3544 IF csr_result_rec.AMOUNT is not null THEN
3545 pay_action_information_api.create_action_information (
3546 p_action_information_id => l_action_info_id
3547 ,p_action_context_id => p_archive_assact_id
3548 ,p_action_context_type => 'AAP'
3549 ,p_object_version_number => l_ovn
3550 ,p_effective_date => p_effective_date
3551 ,p_source_id => NULL
3552 ,p_source_text => NULL
3553 ,p_action_information_category => 'EMEA ELEMENT INFO'
3554 ,p_action_information1 => csr_rec.element_type_id
3555 ,p_action_information2 => csr_rec.input_value_id
3556 ,p_action_information3 => 'D'
3557 ,p_action_information4 => fnd_number.number_to_canonical(csr_result_rec.AMOUNT) --l_formatted_value
3558 ,p_action_information8 => csr_result_rec.UNIT
3559 ,p_action_information9 => 'Deduction Element unit per price:'||csr_result_rec.UNIT_PRICE
3560 ,p_assignment_id => p_assignment_id);
3561
3562 --fnd_file.put_line(fnd_file.log,'group by NO l_action_info_id ' || l_action_info_id );
3563 --fnd_file.put_line(fnd_file.log,'Earning Element ');
3564 --fnd_file.put_line(fnd_file.log,'csr_result_rec.UNIT ' || csr_result_rec.UNIT );
3565 --fnd_file.put_line(fnd_file.log,'csr_result_rec.WP WP ' || csr_result_rec.UNIT_PRICE );
3566 --fnd_file.put_line(fnd_file.log,'fnd_number.number_to_canonical(l_result_value) ' || fnd_number.number_to_canonical(csr_result_rec.AMOUNT) );
3567 END IF;
3568 END LOOP;
3569 ELSE
3570 FOR csr_result_rec IN csr_all_result_values(rec_group_by.ORG_INFORMATION7
3571 ,rec_group_by.ORG_INFORMATION9
3572 ,rec_group_by.ORG_INFORMATION8
3573 ,csr_rec.element_type_id
3574 ,p_assignment_action_id )
3575 LOOP
3576 IF csr_result_rec.AMOUNT is not null THEN
3577 pay_action_information_api.create_action_information (
3578 p_action_information_id => l_action_info_id
3579 ,p_action_context_id => p_archive_assact_id
3580 ,p_action_context_type => 'AAP'
3581 ,p_object_version_number => l_ovn
3582 ,p_effective_date => p_effective_date
3583 ,p_source_id => NULL
3584 ,p_source_text => NULL
3585 ,p_action_information_category => 'EMEA ELEMENT INFO'
3586 ,p_action_information1 => csr_rec.element_type_id
3587 ,p_action_information2 => csr_rec.input_value_id
3588 ,p_action_information3 => 'D'
3589 ,p_action_information4 => fnd_number.number_to_canonical(csr_result_rec.AMOUNT) --l_formatted_value
3590 ,p_action_information8 => csr_result_rec.UNIT
3591 ,p_action_information9 => 'Deduction Element unit per price:'||csr_result_rec.UNIT_PRICE
3592 ,p_assignment_id => p_assignment_id);
3593
3594 --fnd_file.put_line(fnd_file.log,'group by NO l_action_info_id ' || l_action_info_id );
3595 --fnd_file.put_line(fnd_file.log,'Earning Element ');
3596 --fnd_file.put_line(fnd_file.log,'csr_result_rec.UNIT ' || csr_result_rec.UNIT );
3597 --fnd_file.put_line(fnd_file.log,'csr_result_rec.WP WP ' || csr_result_rec.UNIT_PRICE );
3598 --fnd_file.put_line(fnd_file.log,'fnd_number.number_to_canonical(l_result_value) ' || fnd_number.number_to_canonical(csr_result_rec.AMOUNT) );
3599 END IF;
3600 END LOOP;
3601
3602 END IF;-- end of rec_group_by.ORG_INFORMATION10 = 'Y'
3603 ELSE -- Three inputs are not selected.
3604 -- have to get the each input value id and find value for each
3605 -- and archive it if the amount is not null
3606 -- Case for UNIT,PRICE,AMOUNT
3607 -- Segment 7,8,9 is allowed
3608 -- Segemnt 6 is not allowed here, as it doesn't makes sense.
3609 -- segment 7 = > Input ID UNIT
3610 -- segment 8 = > Input ID UNIT PRICE
3611 -- segment 9 = > Input ID Amount
3612 IF rec_group_by.ORG_INFORMATION9 IS NOT NULL
3613 THEN
3614 -- amount should not be null
3615 -- find the amount value and element entry id of this element
3616 -- for this element entry find the the other selected columns like UNIT , UNIT PRICE if selected
3617 --
3618 FOR csr_result_rec IN csr_result_value(rec_group_by.ORG_INFORMATION9 ,csr_rec.element_type_id ,p_assignment_action_id)
3619 LOOP
3620
3621 -- we have EE id
3622 l_amount := csr_result_rec.result_value;
3623 IF rec_group_by.ORG_INFORMATION8 IS NOT NULL
3624 THEN
3625 OPEN csr_result_value_EE(rec_group_by.ORG_INFORMATION8 ,csr_rec.element_type_id ,p_assignment_action_id,csr_result_rec.element_entry_id );
3626 FETCH csr_result_value_EE
3627 INTO l_unit_price;
3628 CLOSE csr_result_value_EE;
3629 ELSE
3630 l_unit_price :=NULL;
3631 END IF; -- End if of segment 8 , unit price
3632
3633 IF rec_group_by.ORG_INFORMATION7 IS NOT NULL
3634 THEN
3635 OPEN csr_result_value_EE(rec_group_by.ORG_INFORMATION8 ,csr_rec.element_type_id ,p_assignment_action_id,csr_result_rec.element_entry_id );
3636 FETCH csr_result_value_EE
3637 INTO l_unit;
3638 CLOSE csr_result_value_EE;
3639 ELSE
3640 l_unit :=NULL;
3641 END IF; -- End if of segment 7 , unit
3642
3643 -- Resume again
3644 IF csr_result_rec.result_value is not null THEN
3645 pay_action_information_api.create_action_information (
3646 p_action_information_id => l_action_info_id
3647 ,p_action_context_id => p_archive_assact_id
3648 ,p_action_context_type => 'AAP'
3649 ,p_object_version_number => l_ovn
3650 ,p_effective_date => p_effective_date
3651 ,p_source_id => NULL
3652 ,p_source_text => NULL
3653 ,p_action_information_category => 'EMEA ELEMENT INFO'
3654 ,p_action_information1 => csr_rec.element_type_id
3655 ,p_action_information2 => csr_rec.input_value_id
3656 ,p_action_information3 => 'D'
3657 ,p_action_information4 => fnd_number.number_to_canonical(l_amount) --l_formatted_value
3658 ,p_action_information8 => l_unit
3659 ,p_action_information9 => 'Deduction Element unit per price:'||l_uNIT_PRICE
3660 ,p_assignment_id => p_assignment_id);
3661
3662 --fnd_file.put_line(fnd_file.log,'group by NO l_action_info_id ' || l_action_info_id );
3663 --fnd_file.put_line(fnd_file.log,'Earning Element ');
3664 --fnd_file.put_line(fnd_file.log,'csr_rec.element_type_id ' || csr_rec.element_type_id );
3665 --fnd_file.put_line(fnd_file.log,'csr_rec.input_value_id ' || csr_rec.input_value_id );
3666 --fnd_file.put_line(fnd_file.log,'fnd_number.number_to_canonical(l_result_value) ' || fnd_number.number_to_canonical(csr_result_rec.result_value) );
3667 END IF; -- end of csr_result_rec.result_value is not null
3668 END LOOP;
3669 END IF; -- end of rec_group_by.ORG_INFORMATION9 IS NOT NULL
3670 END IF;
3671 END IF;
3672 /*
3673 OPEN csr_group_by(csr_rec.element_type_id );
3674 FETCH csr_group_by
3675 INTO rec_group_by;
3676 CLOSE csr_group_by;
3677 FOR csr_result_rec IN csr_sum_of_result_values(csr_rec.input_value_id
3678 ,csr_rec.element_type_id
3679 ,p_assignment_action_id
3680 )
3681 LOOP
3682 IF csr_result_rec.result_value is not null THEN
3683 pay_action_information_api.create_action_information (
3684 p_action_information_id => l_action_info_id
3685 ,p_action_context_id => p_archive_assact_id
3686 ,p_action_context_type => 'AAP'
3687 ,p_object_version_number => l_ovn
3688 ,p_effective_date => p_effective_date
3689 ,p_source_id => NULL
3690 ,p_source_text => NULL
3691 ,p_action_information_category => 'EMEA ELEMENT INFO'
3692 ,p_action_information1 => csr_rec.element_type_id
3693 ,p_action_information2 => csr_rec.input_value_id
3694 ,p_action_information3 => 'D'
3695 ,p_action_information4 => fnd_number.number_to_canonical(csr_result_rec.result_value) --l_formatted_value
3696 ,p_action_information8 => csr_result_rec.record_count
3697 ,p_action_information9 => 'Deduction Element unit per price:'||csr_result_rec.UNIT_PRICE
3698 ,p_assignment_id => p_assignment_id);
3699 END IF;
3700 END LOOP;
3701 */
3702
3703 /* --OPEN csr_result_value(csr_rec.input_value_id ,csr_rec.element_type_id ,p_assignment_action_id);
3704 --FETCH csr_result_value INTO l_result_value;
3705 --CLOSE csr_result_value;
3706 FOR csr_result_rec IN csr_result_value(csr_rec.input_value_id ,csr_rec.element_type_id ,p_assignment_action_id)
3707 LOOP
3708
3709 IF csr_result_rec.result_value is not null THEN
3710
3711 pay_action_information_api.create_action_information (
3712 p_action_information_id => l_action_info_id
3713 ,p_action_context_id => p_archive_assact_id
3714 ,p_action_context_type => 'AAP'
3715 ,p_object_version_number => l_ovn
3716 ,p_effective_date => p_effective_date
3717 ,p_source_id => NULL
3718 ,p_source_text => NULL
3719 ,p_action_information_category => 'EMEA ELEMENT INFO'
3720 ,p_action_information1 => csr_rec.element_type_id
3721 ,p_action_information2 => csr_rec.input_value_id
3722 ,p_action_information3 => 'D'
3723 ,p_action_information4 => fnd_number.number_to_canonical(csr_result_rec.result_value) --l_formatted_value
3724 --,p_action_information5 => csr_rec.element_code
3725 ,p_action_information9 => 'Deduction Element'
3726 ,p_assignment_id => p_assignment_id);
3727
3728 END IF;
3729 END LOOP;*/
3730 EXCEPTION WHEN OTHERS THEN
3731 g_err_num := SQLCODE;
3732 /*fnd_file.put_line(fnd_file.log,'ORA_ERR: ' || g_err_num || 'In ARCHIVE_MAIN_ELEMENTS');*/
3733
3734 IF g_debug THEN
3735 hr_utility.set_location('ORA_ERR: ' || g_err_num || 'In ARCHIVE_MAIN_ELEMENTS',330);
3736 END IF;
3737 END;
3738 END LOOP;
3739
3740
3741 IF g_debug THEN
3742 hr_utility.set_location(' Leaving Procedure ARCHIVE_MAIN_ELEMENTS',340);
3743 END IF;
3744
3745 END ARCHIVE_MAIN_ELEMENTS;
3746
3747 PROCEDURE DEINITIALIZATION_CODE
3748 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type) is
3749
3750 CURSOR csr_scl_details (p_payroll_action_id pay_action_information.action_information1%TYPE , p_effective_date DATE ) IS
3751 SELECT DISTINCT segment2 local_unit , paaf.business_group_id
3752 FROM per_all_assignments_f paaf
3753 ,HR_SOFT_CODING_KEYFLEX hsck
3754 WHERE p_effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
3755 AND paaf.SOFT_CODING_KEYFLEX_ID = hsck.SOFT_CODING_KEYFLEX_ID
3756 AND paaf.assignment_id IN
3757 (SELECT DISTINCT assignment_id
3758 FROM pay_assignment_actions
3759 WHERE payroll_action_id= p_payroll_action_id );
3760
3761
3762 CURSOR csr_legal_emp(p_organization_id NUMBER , p_business_group_id NUMBER ) IS
3763 SELECT hoi3.organization_id
3764 FROM HR_ORGANIZATION_UNITS o1
3765 , HR_ORGANIZATION_INFORMATION hoi1
3766 , HR_ORGANIZATION_INFORMATION hoi2
3767 , HR_ORGANIZATION_INFORMATION hoi3
3768 WHERE o1.business_group_id =p_business_group_id
3769 AND hoi1.organization_id = o1.organization_id
3770 AND hoi1.organization_id = p_organization_id
3771 AND hoi1.org_information1 = 'SE_LOCAL_UNIT'
3772 AND hoi1.org_information_context = 'CLASS'
3773 AND o1.organization_id = hoi2.org_information1
3774 AND hoi2.ORG_INFORMATION_CONTEXT='SE_LOCAL_UNITS'
3775 AND hoi2.organization_id = hoi3.organization_id
3776 AND hoi3.ORG_INFORMATION_CONTEXT='CLASS'
3777 AND hoi3.org_information1 = 'HR_LEGAL_EMPLOYER';
3778
3779 CURSOR csr_employer_address(p_organization_id NUMBER) IS
3780 SELECT hla.style style
3781 ,hla.country country
3782 ,hla.address_line_1 AL1
3783 ,hla.address_line_2 AL2
3784 ,hla.address_line_3 AL3
3785 ,hla.postal_code postal_code
3786 FROM hr_locations_all hla
3787 ,hr_organization_units hou
3788 WHERE hou.organization_id = p_organization_id
3789 AND hou.location_id = hla.location_id;
3790
3791 CURSOR csr_effective_date (p_payroll_action_id pay_action_information.action_information1%TYPE ) IS
3792 SELECT effective_date
3793 FROM pay_payroll_actions
3794 WHERE payroll_action_id= p_payroll_action_id ;
3795
3796
3797 l_org_exists NUMBER ;
3798 l_action_info_id NUMBER;
3799 l_ovn NUMBER;
3800 l_effective_date DATE ;
3801 l_emp_postal_code VARCHAR2(80);
3802 l_emp_country VARCHAR2(30);
3803
3804
3805 BEGIN
3806 IF g_debug THEN
3807 hr_utility.set_location(' Entering Procedure DEINITIALIZATION_CODE',380);
3808 END IF;
3809
3810 OPEN csr_effective_date(p_payroll_action_id);
3811 FETCH csr_effective_date INTO l_effective_date ;
3812 CLOSE csr_effective_date;
3813
3814
3815 FOR csr_scl_details_rec IN csr_scl_details(p_payroll_action_id , l_effective_date)
3816 LOOP
3817
3818 FOR csr_legal_emp_rec IN csr_legal_emp(csr_scl_details_rec.local_unit , csr_scl_details_rec.business_group_id)
3819 LOOP
3820
3821 /* INSERT EMPLOYER'S ADDRESS (ORGANIZATION ADDRESS)*/
3822 BEGIN
3823
3824 l_org_exists := 0;
3825 SELECT 1
3826 INTO l_org_exists
3827 FROM pay_action_information
3828 WHERE action_context_id = p_payroll_action_id
3829 AND action_information1 = csr_legal_emp_rec.organization_id
3830 AND effective_date = l_effective_date
3831 AND action_information_category = 'ADDRESS DETAILS';
3832
3833 EXCEPTION
3834 WHEN NO_DATA_FOUND THEN
3835
3836
3837 FOR rec_employer_address IN csr_employer_address(csr_legal_emp_rec.organization_id)
3838 LOOP
3839
3840 IF rec_employer_address.style = 'FI' THEN
3841 l_emp_postal_code := hr_general.decode_lookup('SE_POSTAL_CODE',rec_employer_address.postal_code);
3842 ELSE
3843 l_emp_postal_code := rec_employer_address.postal_code;
3844 END IF;
3845 l_emp_country:=PAY_SE_PAYSLIP_ARCHIVE.get_country_name(rec_employer_address.country);
3846
3847 pay_action_information_api.create_action_information (
3848 p_action_information_id => l_action_info_id
3849 ,p_action_context_id => p_payroll_action_id
3850 ,p_action_context_type => 'PA'
3851 ,p_object_version_number => l_ovn
3852 ,p_effective_date => l_effective_date
3853 ,p_source_id => NULL
3854 ,p_source_text => NULL
3855 ,p_action_information_category => 'ADDRESS DETAILS'
3856 ,p_action_information1 => csr_legal_emp_rec.organization_id
3857 ,p_action_information5 => rec_employer_address.AL1
3858 ,p_action_information6 => rec_employer_address.AL2
3859 ,p_action_information7 => rec_employer_address.AL3
3860 ,p_action_information12 => l_emp_postal_code
3861 ,p_action_information13 => l_emp_country
3862 ,p_action_information14 => 'Employer Address');
3863
3864 --fnd_file.put_line(fnd_file.log,'l_action_info_id ' || l_action_info_id );
3865 --fnd_file.put_line(fnd_file.log,'ADDRESS DETAILS ');
3866 --fnd_file.put_line(fnd_file.log,'csr_legal_emp_rec.organization_id ' || csr_legal_emp_rec.organization_id );
3867 --fnd_file.put_line(fnd_file.log,'rec_employer_address.AL1 ' || rec_employer_address.AL1 );
3868 --fnd_file.put_line(fnd_file.log,'rec_employer_address.AL2 ' || rec_employer_address.AL2 );
3869 --fnd_file.put_line(fnd_file.log,'rec_employer_address.AL3 ' || rec_employer_address.AL3 );
3870 --fnd_file.put_line(fnd_file.log,'l_emp_postal_code ' || l_emp_postal_code );
3871 --fnd_file.put_line(fnd_file.log,'l_emp_country ' || l_emp_country );
3872
3873 END LOOP;
3874
3875 WHEN OTHERS THEN
3876 NULL;
3877 END;
3878
3879 END LOOP;
3880
3881
3882
3883
3884 END LOOP;
3885
3886 /*fnd_file.put_line(fnd_file.log,'Entering Procedure ARCHIVE_EMPLOYEE_DETAILS 10');*/
3887 IF g_debug THEN
3888 hr_utility.set_location(' Leaving Procedure DEINITIALIZATION_CODE',390);
3889 END IF;
3890
3891 EXCEPTION
3892 WHEN others THEN
3893 IF g_debug THEN
3894 hr_utility.set_location('error raised in DEINITIALIZATION_CODE ',5);
3895 END if;
3896 RAISE;
3897 END;
3898
3899 END PAY_SE_PAYSLIP_ARCHIVE;