[Home] [Help]
PACKAGE BODY: APPS.PAY_SE_PAYSLIP_ARCHIVE
Source
1 PACKAGE BODY PAY_SE_PAYSLIP_ARCHIVE AS
2 /* $Header: pysepysa.pkb 120.5 2010/10/28 08:56:01 vijranga noship $ */
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 IN ('C','S') -- 10229507
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 IN ('C','S') -- 10229507
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 --Bug#8849455 fix Added space between 3 and 4 digits in postal code
971 l_postal_code := substr(l_postal_code,1,3)||' '||substr(l_postal_code,4,2);
972 l_country:=PAY_SE_PAYSLIP_ARCHIVE.get_country_name(rec_primary_address.country);
973 /*
974 IF rec_employer_address.style = 'SE' THEN
975 l_emp_postal_code := hr_general.decode_lookup('SE_POSTAL_CODE',rec_employer_address.postal_code);
976 ELSE
977 l_emp_postal_code := rec_employer_address.postal_code;
978 END IF;
979 l_emp_country:=PAY_SE_PAYSLIP_ARCHIVE.get_country_name(rec_employer_address.country);
980 */
981 --fnd_file.put_line(fnd_file.log,'Entering Procedure ARCHIVE_EMPLOYEE_DETAILS 6');
982 /* INSERT PERSON DETAILS */
983
984 pay_action_information_api.create_action_information (
985 p_action_information_id => l_action_info_id
986 ,p_action_context_id => p_archive_assact_id
987 ,p_action_context_type => 'AAP'
988 ,p_object_version_number => l_ovn
989 ,p_effective_date => p_effective_date
990 ,p_source_id => NULL
991 ,p_source_text => NULL
992 ,p_action_information_category => 'EMPLOYEE DETAILS'
993 ,p_action_information1 => rec_person_details.full_name
994 ,p_action_information2 => l_legal_employer_id
995 ,p_action_information4 => rec_person_details.ni_number
996 ,p_action_information7 => l_grade
997 ,p_action_information10 => rec_person_details.emp_num
998 ,p_action_information12 => fnd_date.date_to_displaydate(l_hire_date)
999 ,p_action_information14 => rec_person_details.assignment_num -- Bug 6625393
1000 ,p_action_information15 => l_organization
1001 ,p_action_information16 => p_time_period_id
1002 ,p_action_information17 => l_job
1003 ,p_action_information18 => l_employer_name
1004 ,p_action_information19 => l_position
1005 ,p_action_information30 => l_pay_location
1006 ,p_assignment_id => p_assignment_id);
1007
1008 --fnd_file.put_line(fnd_file.log,' l_action_info_id =='||l_action_info_id);
1009 --fnd_file.put_line(fnd_file.log,' p_archive_assact_id =='||p_archive_assact_id);
1010 --fnd_file.put_line(fnd_file.log,' rec_person_details.full_name =='||rec_person_details.full_name);
1011 --fnd_file.put_line(fnd_file.log,' l_legal_employer_id =='||l_legal_employer_id);
1012 --fnd_file.put_line(fnd_file.log,' rec_person_details.ni_number =='||rec_person_details.ni_number);
1013 --fnd_file.put_line(fnd_file.log,' l_grade =='||l_grade);
1014 --fnd_file.put_line(fnd_file.log,' to_char(trunc(l_hire_date)) =='||to_char(trunc(l_hire_date)));
1015 --fnd_file.put_line(fnd_file.log,' l_organization =='||l_organization);
1016 --fnd_file.put_line(fnd_file.log,' l_job =='||l_job);
1017 --fnd_file.put_line(fnd_file.log,' l_employer_name =='||l_employer_name);
1018 --fnd_file.put_line(fnd_file.log,' l_position =='||l_position);
1019 --fnd_file.put_line(fnd_file.log,' l_pay_location =='||l_pay_location);
1020 --fnd_file.put_line(fnd_file.log,' p_assignment_id =='||p_assignment_id);
1021
1022 /* INSERT ADDRESS DETAILS */
1023
1024 IF rec_primary_address.AL1 IS NOT NULL THEN /* CHECK IF EMPLOYEE HAS BEEN GIVEN A PRIMARY ADDRESS */
1025
1026 pay_action_information_api.create_action_information (
1027 p_action_information_id => l_action_info_id
1028 ,p_action_context_id => p_archive_assact_id
1029 ,p_action_context_type => 'AAP'
1030 ,p_object_version_number => l_ovn
1031 ,p_effective_date => p_effective_date
1032 ,p_source_id => NULL
1033 ,p_source_text => NULL
1034 ,p_action_information_category => 'ADDRESS DETAILS'
1035 ,p_action_information1 => rec_primary_address.person_id
1036 ,p_action_information5 => rec_primary_address.AL1
1037 ,p_action_information6 => rec_primary_address.AL2
1038 ,p_action_information7 => rec_primary_address.AL3
1039 ,p_action_information12 => l_postal_code
1040 ,p_action_information13 => l_country
1041 ,p_action_information14 => 'Employee Address'
1042 ,p_assignment_id => p_assignment_id);
1043
1044 --fnd_file.put_line(fnd_file.log,' l_action_info_id =='||l_action_info_id);
1045 --fnd_file.put_line(fnd_file.log,' p_archive_assact_id =='||p_archive_assact_id);
1046 --fnd_file.put_line(fnd_file.log,' rec_primary_address.person_id =='||rec_primary_address.person_id);
1047 --fnd_file.put_line(fnd_file.log,' rec_primary_address.AL1 =='|| rec_primary_address.AL1);
1048 --fnd_file.put_line(fnd_file.log,' rec_primary_address.AL2 =='|| rec_primary_address.AL2);
1049 --fnd_file.put_line(fnd_file.log,' rec_primary_address.AL3 =='|| rec_primary_address.AL3);
1050 --fnd_file.put_line(fnd_file.log,' l_postal_code =='||l_postal_code);
1051 --fnd_file.put_line(fnd_file.log,' l_country =='||l_country);
1052 --fnd_file.put_line(fnd_file.log,' p_assignment_id =='||p_assignment_id);
1053
1054 ELSE
1055 /* INSERT EMPLOYER ADDRESS AS EMPLOYEE'S PRIMARY ADDRESS */
1056
1057 pay_action_information_api.create_action_information (
1058 p_action_information_id => l_action_info_id
1059 ,p_action_context_id => p_archive_assact_id
1060 ,p_action_context_type => 'AAP'
1061 ,p_object_version_number => l_ovn
1062 ,p_effective_date => p_effective_date
1063 ,p_source_id => NULL
1064 ,p_source_text => NULL
1065 ,p_action_information_category => 'ADDRESS DETAILS'
1066 ,p_action_information1 => rec_person_details.person_id
1067 ,p_action_information5 => NULL
1068 ,p_action_information6 => NULL
1069 ,p_action_information7 => NULL
1070 ,p_action_information8 => NULL
1071 ,p_action_information9 => NULL
1072 ,p_action_information10 => NULL
1073 ,p_action_information11 => NULL
1074 ,p_action_information12 => NULL
1075 ,p_action_information13 => NULL
1076 ,p_action_information14 => 'Employee Address'
1077 ,p_assignment_id => p_assignment_id);
1078 END IF;
1079 /*fnd_file.put_line(fnd_file.log,'Entering Procedure ARCHIVE_EMPLOYEE_DETAILS 9');*/
1080 /* INSERT EMPLOYER'S ADDRESS (ORGANIZATION ADDRESS)*/
1081 /*
1082 BEGIN
1083 l_org_exists := 0;
1084 SELECT 1
1085 INTO l_org_exists
1086 FROM pay_action_information
1087 WHERE action_context_id = p_payroll_action_id
1088 AND action_information1 = rec_person_details.org_id
1089 AND effective_date = p_effective_date
1090 AND action_information_category = 'ADDRESS DETAILS';
1091 EXCEPTION
1092 WHEN NO_DATA_FOUND THEN
1093 --fnd_file.put_line(fnd_file.log,'PA Employer Address'||p_archive_assact_id);
1094 pay_action_information_api.create_action_information (
1095 p_action_information_id => l_action_info_id
1096 ,p_action_context_id => p_payroll_action_id
1097 ,p_action_context_type => 'PA'
1098 ,p_object_version_number => l_ovn
1099 ,p_effective_date => p_effective_date
1100 ,p_source_id => NULL
1101 ,p_source_text => NULL
1102 ,p_action_information_category => 'ADDRESS DETAILS'
1103 ,p_action_information1 => l_legal_employer_id
1104 ,p_action_information5 => rec_employer_address.AL1
1105 ,p_action_information6 => rec_employer_address.AL2
1106 ,p_action_information7 => rec_employer_address.AL3
1107 ,p_action_information12 => l_emp_postal_code
1108 ,p_action_information13 => l_emp_country
1109 ,p_action_information14 => 'Employer Address');
1110 WHEN OTHERS THEN
1111 NULL;
1112 END;
1113 */
1114 /*fnd_file.put_line(fnd_file.log,'Entering Procedure ARCHIVE_EMPLOYEE_DETAILS 10');*/
1115 --
1116 IF g_debug THEN
1117 hr_utility.set_location(' Leaving Procedure ARCHIVE_EMPLOYEE_DETAILS',170);
1118 END IF;
1119 --
1120 EXCEPTION WHEN OTHERS THEN
1121 g_err_num := SQLCODE;
1122 /*fnd_file.put_line(fnd_file.log,'ORA_ERR: ' || g_err_num || 'In ARCHIVE_EMPLOYEE_DETAILS');*/
1123 IF g_debug THEN
1124 hr_utility.set_location('ORA_ERR: ' || g_err_num || 'In ARCHIVE_EMPLOYEE_DETAILS',180);
1125 END IF;
1126 END ARCHIVE_EMPLOYEE_DETAILS;
1127 /* EARNINGS REGION, DEDUCTIONS REGION */
1128 PROCEDURE ARCHIVE_ELEMENT_INFO(p_payroll_action_id IN NUMBER
1129 ,p_effective_date IN DATE
1130 ,p_date_earned IN DATE
1131 ,p_pre_payact_id IN NUMBER)
1132 IS
1133 /* Cursor to retrieve Earnings Element Information */
1134 /* Archive ELEMENT DEFINITION */
1135 CURSOR csr_ear_element_info IS
1136 SELECT code.org_information2||','||nvl(pettl.reporting_name,pettl.element_name) rep_name
1137 ,et.element_type_id element_type_id
1138 ,iv.input_value_id input_value_id
1139 ,iv.uom uom
1140 FROM pay_element_types_f et
1141 , pay_element_types_f_tl pettl
1142 , pay_input_values_f iv
1143 , pay_element_classifications classification
1144 ,hr_organization_information code
1145 WHERE et.element_type_id = iv.element_type_id
1146 AND et.element_type_id = pettl.element_type_id
1147 AND pettl.language = USERENV('LANG')
1148 AND iv.name = 'Pay Value'
1149 AND classification.classification_id = et.classification_id
1150 AND classification.classification_name IN ( 'Salary in Money'
1151 ,'Lumpsum'
1152 ,'Other Payments Subject to Tax'
1153 ,'Retrospective Payments'
1154 ,'Direct Payments'
1155 )
1156 AND p_date_earned BETWEEN et.effective_start_date
1157 AND et.effective_end_date
1158 AND p_date_earned BETWEEN iv.effective_start_date
1159 AND iv.effective_end_date
1160 AND ((et.business_group_id IS NULL AND et.legislation_code = 'SE')
1161 OR (et.business_group_id = g_business_group_id AND et.legislation_code IS NULL))
1162 and code.organization_id(+) = g_business_group_id
1163 and code.org_information_context (+)='SE_SOE_ELEMENT_ADD_DETAILS'
1164 and et.element_type_id = code.org_information1 (+);
1165 /*SELECT nvl(pettl.reporting_name,pettl.element_name) rep_name
1166 ,et.element_type_id element_type_id
1167 ,iv.input_value_id input_value_id
1168 ,iv.uom uom
1169 FROM pay_element_types_f et
1170 , pay_element_types_f_tl pettl
1171 , pay_input_values_f iv
1172 , pay_element_classifications classification
1173 WHERE et.element_type_id = iv.element_type_id
1174 AND et.element_type_id = pettl.element_type_id
1175 AND pettl.language = USERENV('LANG')
1176 AND iv.name = 'Pay Value'
1177 AND classification.classification_id = et.classification_id
1178 AND classification.classification_name IN
1179 ('Absence'
1180 ,'Salary in Money'
1181 ,'Lumpsum'
1182 ,'Benefits in Kind'
1183 ,'Taxable Expenses'
1184 ,'Other Payments Subject to Tax'
1185 ,'Retrospective Payments'
1186 ,'Direct Payments'
1187 ,'Employer Charges'
1188 ,'External Expenses')
1189 AND p_date_earned BETWEEN et.effective_start_date
1190 AND et.effective_end_date
1191 AND p_date_earned BETWEEN iv.effective_start_date
1192 AND iv.effective_end_date
1193 AND ((et.business_group_id IS NULL AND et.legislation_code = 'SE')
1194 OR (et.business_group_id = g_business_group_id AND et.legislation_code IS NULL));*/
1195 /* Cursor to retrieve Deduction Element Information */
1196 CURSOR csr_ded_element_info IS
1197 SELECT code.org_information2||','||nvl(pettl.reporting_name,pettl.element_name) rep_name
1198 ,et.element_type_id element_type_id
1199 ,iv.input_value_id input_value_id
1200 ,iv.uom uom
1201 FROM pay_element_types_f et
1202 , pay_element_types_f_tl pettl
1203 , pay_input_values_f iv
1204 , pay_element_classifications classification
1205 ,hr_organization_information code
1206 WHERE et.element_type_id = iv.element_type_id
1207 AND et.element_type_id = pettl.element_type_id
1208 AND pettl.language = USERENV('LANG')
1209 AND iv.name = 'Pay Value'
1210 AND classification.classification_id = et.classification_id
1211 AND classification.classification_name IN ('Involuntary Deductions'
1212 ,'Voluntary Deductions'
1213 ,'Statutory Deductions'
1214 ,'Pre-Tax Deductions')
1215 AND p_date_earned BETWEEN et.effective_start_date
1216 AND et.effective_end_date
1217 AND p_date_earned BETWEEN iv.effective_start_date
1218 AND iv.effective_end_date
1219 AND ((et.business_group_id IS NULL AND et.legislation_code = 'SE')
1220 OR (et.business_group_id = g_business_group_id AND et.legislation_code IS NULL))
1221 and code.organization_id(+) = g_business_group_id
1222 and code.org_information_context (+)='SE_SOE_ELEMENT_ADD_DETAILS'
1223 and et.element_type_id = code.org_information1 (+);
1224 /*SELECT nvl(pettl.reporting_name,pettl.element_name) rep_name
1225 ,et.element_type_id element_type_id
1226 ,iv.input_value_id input_value_id
1227 ,iv.uom uom
1228 FROM pay_element_types_f et
1229 , pay_element_types_f_tl pettl
1230 , pay_input_values_f iv
1231 , pay_element_classifications classification
1232 WHERE et.element_type_id = iv.element_type_id
1233 AND et.element_type_id = pettl.element_type_id
1234 AND pettl.language = USERENV('LANG')
1235 AND iv.name = 'Pay Value'
1236 AND classification.classification_id = et.classification_id
1237 AND classification.classification_name IN
1238 ('Pre-Tax Deductions'
1239 ,'Involuntary Deductions'
1240 ,'Voluntary Deductions'
1241 ,'Statutory Deductions')
1242 AND p_date_earned BETWEEN et.effective_start_date
1243 AND et.effective_end_date
1244 AND p_date_earned BETWEEN iv.effective_start_date
1245 AND iv.effective_end_date
1246 AND ((et.business_group_id IS NULL AND et.legislation_code = 'SE')
1247 OR (et.business_group_id = g_business_group_id AND et.legislation_code IS NULL));*/
1248 l_action_info_id NUMBER;
1249 l_ovn NUMBER;
1250 l_flag NUMBER := 0;
1251 BEGIN
1252 IF g_debug THEN
1253 hr_utility.set_location(' Entering Procedure ARCHIVE_ELEMENT_INFO',210);
1254 END IF;
1255 --fnd_file.put_line(fnd_file.log,'Entering ARCHIVE_ELEMENT_INFO');
1256 --fnd_file.put_line(fnd_file.log,' EARNINGS ELEMENT');
1257 /* EARNINGS ELEMENT */
1258 FOR rec_earnings IN csr_ear_element_info LOOP
1259 BEGIN
1260 SELECT 1 INTO l_flag
1261 FROM pay_action_information
1262 WHERE action_context_id = p_payroll_action_id
1263 AND action_information_category = 'EMEA ELEMENT DEFINITION'
1264 AND action_information2 = rec_earnings.element_type_id
1265 AND action_information3 = rec_earnings.input_value_id
1266 AND action_information5 = 'E';
1267 EXCEPTION WHEN NO_DATA_FOUND THEN
1268 pay_action_information_api.create_action_information (
1269 p_action_information_id => l_action_info_id
1270 ,p_action_context_id => p_payroll_action_id
1271 ,p_action_context_type => 'PA'
1272 ,p_object_version_number => l_ovn
1273 ,p_effective_date => p_effective_date
1274 ,p_source_id => NULL
1275 ,p_source_text => NULL
1276 ,p_action_information_category => 'EMEA ELEMENT DEFINITION'
1277 ,p_action_information1 => p_pre_payact_id
1278 ,p_action_information2 => rec_earnings.element_type_id
1279 ,p_action_information3 => rec_earnings.input_value_id
1280 ,p_action_information4 => rec_earnings.rep_name
1281 ,p_action_information5 => 'E'
1282 ,p_action_information6 => rec_earnings.uom
1283 ,p_action_information7 => 'E'); --Earnings Element Context
1284
1285 ----fnd_file.put_line(fnd_file.log,'l_action_info_id '||l_action_info_id);
1286 --fnd_file.put_line(fnd_file.log,'p_payroll_action_id '||p_payroll_action_id);
1287 --fnd_file.put_line(fnd_file.log,'p_pre_payact_id '||p_pre_payact_id);
1288 --fnd_file.put_line(fnd_file.log,'rec_earnings.element_type_id '||rec_earnings.element_type_id);
1289 --fnd_file.put_line(fnd_file.log,'rec_earnings.input_value_id '||rec_earnings.input_value_id);
1290 --fnd_file.put_line(fnd_file.log,'rec_earnings.rep_name '||rec_earnings.rep_name);
1291 --fnd_file.put_line(fnd_file.log,'rec_earnings.uom '||rec_earnings.uom);
1292 WHEN OTHERS THEN
1293 NULL;
1294 END;
1295 END LOOP;
1296 /* DEDUCTION ELEMENT */
1297 --fnd_file.put_line(fnd_file.log,' DEDUCTION ELEMENT ');
1298 FOR rec_deduction IN csr_ded_element_info LOOP
1299 BEGIN
1300 SELECT 1 INTO l_flag
1301 FROM pay_action_information
1302 WHERE action_context_id = p_payroll_action_id
1303 AND action_information_category = 'EMEA ELEMENT DEFINITION'
1304 AND action_information2 = rec_deduction.element_type_id
1305 AND action_information3 = rec_deduction.input_value_id
1306 AND action_information5 = 'D';
1307 EXCEPTION WHEN NO_DATA_FOUND THEN
1308 pay_action_information_api.create_action_information (
1309 p_action_information_id => l_action_info_id
1310 ,p_action_context_id => p_payroll_action_id
1311 ,p_action_context_type => 'PA'
1312 ,p_object_version_number => l_ovn
1313 ,p_effective_date => p_effective_date
1314 ,p_source_id => NULL
1315 ,p_source_text => NULL
1316 ,p_action_information_category => 'EMEA ELEMENT DEFINITION'
1317 ,p_action_information1 => p_pre_payact_id
1318 ,p_action_information2 => rec_deduction.element_type_id
1319 ,p_action_information3 => rec_deduction.input_value_id
1320 ,p_action_information4 => rec_deduction.rep_name
1321 ,p_action_information5 => 'D'
1322 ,p_action_information6 => rec_deduction.uom
1323 ,p_action_information7 => 'D'); --Deduction Element Context
1324
1325 --fnd_file.put_line(fnd_file.log,'l_action_info_id '||l_action_info_id);
1326 --fnd_file.put_line(fnd_file.log,'p_payroll_action_id '||p_payroll_action_id);
1327 --fnd_file.put_line(fnd_file.log,'p_pre_payact_id '||p_pre_payact_id);
1328 --fnd_file.put_line(fnd_file.log,'rec_deduction.element_type_id '||rec_deduction.element_type_id);
1329 --fnd_file.put_line(fnd_file.log,'rec_deduction.input_value_id '||rec_deduction.input_value_id);
1330 --fnd_file.put_line(fnd_file.log,'rec_deduction.rep_name '||rec_deduction.rep_name);
1331 --fnd_file.put_line(fnd_file.log,'rec_deduction.uom '||rec_deduction.uom);
1332
1333 /*WHEN OTHERS THEN
1334 NULL;*/
1335 END;
1336 END LOOP;
1337 IF g_debug THEN
1338 hr_utility.set_location(' Leaving Procedure ARCHIVE_ELEMENT_INFO',220);
1339 END IF;
1340 EXCEPTION WHEN OTHERS THEN
1341 g_err_num := SQLCODE;
1342 /*fnd_file.put_line(fnd_file.log,'ORA_ERR: ' || g_err_num || 'In ARCHIVE_ELEMENT_INFO');*/
1343 IF g_debug THEN
1344 hr_utility.set_location('ORA_ERR: ' || g_err_num || 'ARCHIVE_ELEMENT_INFO',230);
1345 END IF;
1346 END ARCHIVE_ELEMENT_INFO;
1347 /* GET DEFINED BALANCE ID */
1348 FUNCTION GET_DEFINED_BALANCE_ID(p_user_name IN VARCHAR2) RETURN NUMBER
1349 IS
1350 /* Cursor to retrieve Defined Balance Id */
1351 CURSOR csr_def_bal_id(p_user_name VARCHAR2) IS
1352 SELECT u.creator_id
1353 FROM ff_user_entities u,
1354 ff_database_items d
1355 WHERE d.user_name = p_user_name
1356 AND u.user_entity_id = d.user_entity_id
1357 AND (u.legislation_code = 'SE' )
1358 AND (u.business_group_id IS NULL )
1359 AND u.creator_type = 'B';
1360 l_defined_balance_id ff_user_entities.user_entity_id%TYPE;
1361 BEGIN
1362 IF g_debug THEN
1363 hr_utility.set_location(' Entering Function GET_DEFINED_BALANCE_ID',240);
1364 END IF;
1365 OPEN csr_def_bal_id(p_user_name);
1366 FETCH csr_def_bal_id INTO l_defined_balance_id;
1367 CLOSE csr_def_bal_id;
1368 RETURN l_defined_balance_id;
1369 IF g_debug THEN
1370 hr_utility.set_location(' Leaving Function GET_DEFINED_BALANCE_ID',250);
1371 END IF;
1372 END GET_DEFINED_BALANCE_ID;
1373 /* PAYMENT INFORMATION REGION */
1374 PROCEDURE ARCHIVE_PAYMENT_INFO(p_archive_assact_id IN NUMBER,
1375 p_prepay_assact_id IN NUMBER,
1376 p_assignment_id IN NUMBER,
1377 p_date_earned IN DATE,
1378 p_effective_date IN DATE)
1379 IS
1380 /* Cursor to fetch ppm and opm ids to check which payment method to archive */
1381 CURSOR csr_chk(p_prepay_assact_id NUMBER) IS
1382 SELECT personal_payment_method_id ppm_id,
1383 org_payment_method_id opm_id
1384 FROM pay_pre_payments
1385 WHERE assignment_action_id = p_prepay_assact_id;
1386 /* Cursor to check if bank details are attached with ppm */
1387 CURSOR csr_chk_bank(p_ppm_id NUMBER) IS
1388 SELECT ppm.external_account_id
1389 FROM pay_personal_payment_methods_f ppm
1390 WHERE ppm.personal_payment_method_id = p_ppm_id
1391 AND p_date_earned BETWEEN ppm.effective_start_date
1392 AND ppm.effective_end_date;
1393 /* Cursor to retrieve Organization Payment Method Information */
1394 CURSOR csr_get_org_pay(p_prepay_assact_id NUMBER, opm_id NUMBER) IS
1395 SELECT pop.org_payment_method_id opm_id,
1396 pop.org_payment_method_name opm_name,
1397 ppttl.payment_type_name pay_type,
1398 ppp.value value
1399 FROM pay_org_payment_methods_f pop,
1400 pay_assignment_actions paa,
1401 pay_payment_types ppt,
1402 pay_payment_types_tl ppttl,
1403 pay_pre_payments ppp
1404 WHERE paa.assignment_action_id = p_prepay_assact_id
1405 AND ppt.payment_type_id = pop.payment_type_id
1406 AND ppt.payment_type_id = ppttl.payment_type_id
1407 AND ppttl.language = userenv('LANG')
1408 AND ppp.org_payment_method_id = pop.org_payment_method_id
1409 AND pop.org_payment_method_id = opm_id
1410 AND ppp.assignment_action_id = paa.assignment_action_id
1411 AND p_date_earned BETWEEN pop.effective_start_date
1412 AND pop.effective_end_date;
1413 /* Cursor to retrieve Personal Payment Method Info*/
1414 CURSOR csr_get_pers_pay(p_prepay_assact_id NUMBER, ppm_id NUMBER) IS
1415 SELECT pea.segment1 name_id,
1416 pea.segment2 branch,
1417 pea.segment3 acct_num,
1418 ppm.org_payment_method_id opm_id,
1419 pop.external_account_id,
1420 pop.org_payment_method_name opm_name,
1421 ppm.personal_payment_method_id ppm_id,
1422 ppttl.payment_type_name pay_type,
1423 ppp.value value
1424 FROM pay_external_accounts pea,
1425 pay_org_payment_methods_f pop,
1426 pay_personal_payment_methods_f ppm,
1427 pay_assignment_actions paa,
1428 pay_payment_types ppt,
1429 pay_payment_types_tl ppttl,
1430 pay_pre_payments ppp
1431 WHERE pea.external_account_id = NVL(ppm.external_account_id,pop.external_account_id)
1432 AND paa.assignment_action_id = p_prepay_assact_id
1433 AND paa.assignment_id = ppm.assignment_id
1434 AND ppm.org_payment_method_id = pop.org_payment_method_id
1435 AND ppm.personal_payment_method_id = ppm_id
1436 AND ppt.payment_type_id = pop.payment_type_id
1437 AND ppt.payment_type_id = ppttl.payment_type_id
1438 AND ppttl.language = userenv('LANG')
1439 AND ppp.assignment_action_id = paa.assignment_action_id
1440 AND ppp.personal_payment_method_id = ppm.personal_payment_method_id
1441 AND p_date_earned BETWEEN pop.effective_start_date
1442 AND pop.effective_end_date
1443 AND p_date_earned BETWEEN ppm.effective_start_date
1444 AND ppm.effective_end_date;
1445 l_bank_name VARCHAR2(50);
1446 l_action_info_id NUMBER;
1447 l_ovn NUMBER;
1448 l_org NUMBER;
1449 l_pers VARCHAR2(40) := NULL;
1450 l_ext_acct NUMBER;
1451 rec_chk csr_chk%ROWTYPE;
1452 l_pay_value VARCHAR2(50) := NULL;
1453 BEGIN
1454 IF g_debug THEN
1455 hr_utility.set_location(' Entering Procedure ARCHIVE_PAYMENT_INFO',260);
1456 END IF;
1457
1458 OPEN csr_chk(p_prepay_assact_id);
1459 LOOP
1460 FETCH csr_chk INTO rec_chk;
1461 EXIT WHEN csr_chk%NOTFOUND;
1462
1463 IF rec_chk.ppm_id IS NOT NULL THEN
1464
1465 FOR rec_pers_pay IN csr_get_pers_pay(p_prepay_assact_id,rec_chk.ppm_id) LOOP
1466
1467 OPEN csr_chk_bank(rec_chk.ppm_id);
1468
1469 FETCH csr_chk_bank INTO l_ext_acct;
1470 CLOSE csr_chk_bank;
1471 l_pay_value := to_char (rec_pers_pay.value,g_format_mask);
1472 IF (l_ext_acct IS NOT NULL) THEN
1473
1474 --fnd_file.put_line(fnd_file.log,'In ARCHIVE_PAYMENT_INFO 2');
1475 l_bank_name := rec_pers_pay.name_id;
1476 pay_action_information_api.create_action_information (
1477 p_action_information_id => l_action_info_id
1478 ,p_action_context_id => p_archive_assact_id
1479 ,p_action_context_type => 'AAP'
1480 ,p_object_version_number => l_ovn
1481 ,p_effective_date => p_effective_date
1482 ,p_source_id => NULL
1483 ,p_source_text => NULL
1484 ,p_action_information_category => 'EMPLOYEE NET PAY DISTRIBUTION'
1485 ,p_action_information1 => rec_pers_pay.opm_id
1486 ,p_action_information2 => rec_pers_pay.ppm_id
1487 ,p_action_information5 => l_bank_name
1488 ,p_action_information6 => rec_pers_pay.branch
1489 ,p_action_information7 => rec_pers_pay.acct_num
1490 ,p_action_information8 => NULL
1491 ,p_action_information9 => NULL
1492 ,p_action_information10 => NULL
1493 ,p_action_information11 => NULL
1494 ,p_action_information12 => NULL
1495 ,p_action_information13 => NULL
1496 ,p_action_information14 => NULL
1497 ,p_action_information15 => NULL
1498 ,p_action_information16 => fnd_number.number_to_canonical(rec_pers_pay.value) --l_pay_value
1499 ,p_action_information17 => NULL
1500 ,p_action_information18 => rec_pers_pay.opm_name
1501 ,p_assignment_id => p_assignment_id);
1502 ELSE
1503 --fnd_file.put_line(fnd_file.log,'In ARCHIVE_PAYMENT_INFO 3');
1504
1505 pay_action_information_api.create_action_information (
1506 p_action_information_id => l_action_info_id
1507 ,p_action_context_id => p_archive_assact_id
1508 ,p_action_context_type => 'AAP'
1509 ,p_object_version_number => l_ovn
1510 ,p_effective_date => p_effective_date
1511 ,p_source_id => NULL
1512 ,p_source_text => NULL
1513 ,p_action_information_category => 'EMPLOYEE NET PAY DISTRIBUTION'
1514 ,p_action_information1 => rec_pers_pay.opm_id
1515 ,p_action_information2 => rec_pers_pay.ppm_id
1516 ,p_action_information5 => NULL
1517 ,p_action_information6 => NULL
1518 ,p_action_information7 => NULL
1519 ,p_action_information8 => NULL
1520 ,p_action_information9 => NULL
1521 ,p_action_information10 => NULL
1522 ,p_action_information11 => NULL
1523 ,p_action_information12 => NULL
1524 ,p_action_information13 => NULL
1525 ,p_action_information14 => NULL
1526 ,p_action_information15 => NULL
1527 ,p_action_information16 => fnd_number.number_to_canonical(rec_pers_pay.value) --l_pay_value
1528 ,p_action_information17 => NULL
1529 ,p_action_information18 => rec_pers_pay.opm_name
1530 ,p_assignment_id => p_assignment_id);
1531 END IF;
1532 END LOOP;
1533 --fnd_file.put_line(fnd_file.log,'In ARCHIVE_PAYMENT_INFO 4');
1534 END IF;
1535 IF (rec_chk.opm_id IS NOT NULL AND rec_chk.ppm_id IS NULL) THEN
1536 --fnd_file.put_line(fnd_file.log,'In ARCHIVE_PAYMENT_INFO 5');
1537
1538 FOR rec_org_pay IN csr_get_org_pay(p_prepay_assact_id,rec_chk.opm_id) LOOP
1539
1540 l_pay_value := to_char (rec_org_pay.value,g_format_mask);
1541 pay_action_information_api.create_action_information (
1542 p_action_information_id => l_action_info_id
1543 ,p_action_context_id => p_archive_assact_id
1544 ,p_action_context_type => 'AAP'
1545 ,p_object_version_number => l_ovn
1546 ,p_effective_date => p_effective_date
1547 ,p_source_id => NULL
1548 ,p_source_text => NULL
1549 ,p_action_information_category => 'EMPLOYEE NET PAY DISTRIBUTION'
1550 ,p_action_information1 => rec_org_pay.opm_id
1551 ,p_action_information2 => NULL
1552 ,p_action_information5 => NULL
1553 ,p_action_information6 => NULL
1554 ,p_action_information7 => NULL
1555 ,p_action_information8 => NULL
1556 ,p_action_information9 => NULL
1557 ,p_action_information10 => NULL
1558 ,p_action_information11 => NULL
1559 ,p_action_information12 => NULL
1560 ,p_action_information13 => NULL
1561 ,p_action_information14 => NULL
1562 ,p_action_information15 => NULL
1563 ,p_action_information16 => fnd_number.number_to_canonical(rec_org_pay.value) --l_pay_value
1564 ,p_action_information17 => NULL
1565 ,p_action_information18 => rec_org_pay.opm_name
1566 ,p_assignment_id => p_assignment_id);
1567 END LOOP;
1568 END IF;
1569 END LOOP;
1570 CLOSE csr_chk;
1571
1572 IF g_debug THEN
1573 hr_utility.set_location(' Leaving Procedure ARCHIVE_PAYMENT_INFO',270);
1574 END IF;
1575 EXCEPTION WHEN OTHERS THEN
1576 g_err_num := SQLCODE;
1577 /*fnd_file.put_line(fnd_file.log,'ORA_ERR: ' || g_err_num || 'In ARCHIVE_PAYMENT_INFO');*/
1578 IF g_debug THEN
1579 hr_utility.set_location('ORA_ERR: ' || g_err_num || 'In ARCHIVE_PAYMENT_INFO',280);
1580 END IF;
1581 END ARCHIVE_PAYMENT_INFO;
1582
1583 /* ADDITIONAL ELEMENTS REGION */
1584 PROCEDURE archive_add_element(p_archive_assact_id IN NUMBER,
1585 p_assignment_action_id IN NUMBER,
1586 p_assignment_id IN NUMBER,
1587 p_payroll_action_id IN NUMBER,
1588 p_date_earned IN DATE,
1589 p_effective_date IN DATE,
1590 p_pre_payact_id IN NUMBER,
1591 p_archive_flag IN VARCHAR2) IS
1592 /* Cursor to retrieve Additional Element Information */
1593 CURSOR csr_get_element(p_bus_grp_id NUMBER) IS
1594 SELECT hoi.org_information2 element_type_id
1595 ,hoi.org_information3 input_value_id
1596 ,hoi.org_information7 element_narrative
1597 ,pec.classification_name
1598 ,piv.uom
1599 FROM hr_organization_information hoi
1600 ,pay_element_classifications pec
1601 ,pay_element_types_f pet
1602 ,pay_input_values_f piv
1603 WHERE hoi.organization_id = p_bus_grp_id
1604 AND hoi.org_information_context = 'Business Group:Payslip Info'
1605 AND hoi.org_information1 = 'ELEMENT'
1606 AND hoi.org_information2 = pet.element_type_id
1607 AND pec.classification_id = pet.classification_id
1608 AND piv.input_value_id = hoi.org_information3
1609 AND p_date_earned BETWEEN piv.effective_start_date
1610 AND piv.effective_end_date;/*
1611 SELECT hoi.org_information2 element_type_id
1612 ,hoi.org_information3 input_value_id
1613 ,hoi.org_information7 element_narrative
1614 ,pec.classification_name
1615 ,piv.uom
1616 ,code.org_information2 element_code
1617 FROM hr_organization_information hoi
1618 ,pay_element_classifications pec
1619 ,pay_element_types_f pet
1620 ,pay_input_values_f piv
1621 ,hr_organization_information code
1622 WHERE hoi.organization_id = p_bus_grp_id
1623 AND hoi.org_information_context = 'Business Group:Payslip Info'
1624 AND hoi.org_information1 = 'ELEMENT'
1625 AND hoi.org_information2 = pet.element_type_id
1626 AND pec.classification_id = pet.classification_id
1627 AND piv.input_value_id = hoi.org_information3
1628 AND p_date_earned BETWEEN piv.effective_start_date
1629 AND piv.effective_end_date
1630 and code.organization_id(+) = 75235
1631 and code.org_information_context (+)='SE_SOE_ELEMENT_ADD_DETAILS'
1632 and pet.element_type_id = code.org_information1 (+);*/
1633 /* Cursor to retrieve run result value of Additional Elements */
1634 CURSOR csr_result_value(p_iv_id NUMBER
1635 ,p_ele_type_id NUMBER
1636 ,p_assignment_action_id NUMBER) IS
1637 SELECT rrv.result_value ,rr.element_entry_id
1638 FROM pay_run_result_values rrv
1639 ,pay_run_results rr
1640 ,pay_assignment_actions paa
1641 ,pay_payroll_actions ppa
1642 WHERE rrv.input_value_id = p_iv_id
1643 AND rr.element_type_id = p_ele_type_id
1644 AND rr.run_result_id = rrv.run_result_id
1645 AND rr.assignment_action_id = paa.assignment_action_id
1646 AND paa.assignment_action_id = p_assignment_action_id
1647 AND ppa.payroll_action_id = paa.payroll_action_id
1648 AND ppa.action_type IN ('Q','R')
1649 AND rrv.result_value IS NOT NULL;
1650 -----------------------------------------------------------------------------
1651 /* Cursor to retrieve run result value of Main Elements */
1652 CURSOR csr_result_value_EE(p_iv_id NUMBER
1653 ,p_ele_type_id NUMBER
1654 ,p_assignment_action_id NUMBER
1655 ,p_EE_ID NUMBER) IS
1656 SELECT rrv.result_value
1657 FROM pay_run_result_values rrv
1658 ,pay_run_results rr
1659 ,pay_assignment_actions paa
1660 ,pay_payroll_actions ppa
1661 WHERE rrv.input_value_id = p_iv_id
1662 AND rr.element_type_id = p_ele_type_id
1663 AND rr.run_result_id = rrv.run_result_id
1664 AND rr.assignment_action_id = paa.assignment_action_id
1665 AND paa.assignment_action_id = p_assignment_action_id
1666 AND ppa.payroll_action_id = paa.payroll_action_id
1667 AND ppa.action_type IN ('Q','R')
1668 AND rrv.result_value IS NOT NULL
1669 AND rr.element_entry_id = p_EE_ID;
1670 -----------------------------------------------------------------------------
1671 -- Cursor to pick up segment from SE_SOE_ELEMENT_ADD_DETAILS context Org Developer DF
1672 CURSOR csr_group_by( p_ele_type_id NUMBER ) IS
1673 select ORG_INFORMATION3,ORG_INFORMATION6,ORG_INFORMATION7,ORG_INFORMATION8,ORG_INFORMATION9,ORG_INFORMATION10
1674 from hr_organization_information code
1675 where code.organization_id = g_business_group_id
1676 and code.org_information_context ='SE_SOE_ELEMENT_ADD_DETAILS'
1677 and code.org_information1 =p_ele_type_id;
1678
1679 rec_group_by csr_group_by%ROWTYPE;
1680 -----------------------------------------------------------------------------
1681 /* Cursor to retrieve sum of run result value for an given Main Element */
1682 -----------------------------------------------------------------------------
1683 CURSOR csr_sum_of_result_values(p_iv_id NUMBER
1684 ,p_ele_type_id NUMBER
1685 ,p_assignment_action_id NUMBER
1686 ) IS
1687 SELECT sum(rrv.result_value) result_value
1688 ,count(rrv.RUN_RESULT_ID) record_count
1689 ,rrv.result_value UNIT_PRICE
1690 FROM pay_run_result_values rrv
1691 ,pay_run_results rr
1692 ,pay_assignment_actions paa
1693 ,pay_payroll_actions ppa
1694 WHERE rrv.input_value_id = p_iv_id
1695 AND rr.element_type_id = p_ele_type_id
1696 AND rr.run_result_id = rrv.run_result_id
1697 AND rr.assignment_action_id = paa.assignment_action_id
1698 AND paa.assignment_action_id = p_assignment_action_id
1699 AND ppa.payroll_action_id = paa.payroll_action_id
1700 AND ppa.action_type IN ('Q','R')
1701 AND rrv.result_value IS NOT NULL
1702 group by rrv.result_value;
1703 /*CURSOR csr_sum_of_result_values(p_iv_id NUMBER
1704 ,p_ele_type_id NUMBER
1705 ,p_assignment_action_id NUMBER
1706 ,p_group_by NUMBER) IS
1707 SELECT sum(rrv.result_value) result_value
1708 ,count(rrv.RUN_RESULT_ID) record_count
1709 ,rrv.result_value UNIT_PRICE
1710 FROM pay_run_result_values pr
1711 ,pay_run_result_values rrv
1712 ,pay_run_results rr
1713 ,pay_assignment_actions paa
1714 ,pay_payroll_actions ppa
1715 WHERE pr.input_value_id(+) = p_group_by
1716 AND rrv.input_value_id = p_iv_id
1717 AND rr.element_type_id = p_ele_type_id
1718 AND rr.run_result_id = rrv.run_result_id
1719 AND rr.run_result_id = pr.run_result_id (+)
1720 AND rr.assignment_action_id = paa.assignment_action_id
1721 AND paa.assignment_action_id = p_assignment_action_id
1722 AND ppa.payroll_action_id = paa.payroll_action_id
1723 AND ppa.action_type IN ('Q','R')
1724 AND rrv.result_value IS NOT NULL
1725 --AND pr.result_value IS NOT NULL
1726 group by pr.result_value,rrv.result_value;*/
1727 /*CURSOR csr_sum_of_result_values(p_iv_id NUMBER
1728 ,p_ele_type_id NUMBER
1729 ,p_assignment_action_id NUMBER) IS
1730 SELECT sum(rrv.result_value) result_value
1731 ,count(rrv.RUN_RESULT_ID) record_count
1732 ,rrv.result_value UNIT_PRICE
1733 FROM pay_run_result_values rrv
1734 ,pay_run_results rr
1735 ,pay_assignment_actions paa
1736 ,pay_payroll_actions ppa
1737 WHERE rrv.input_value_id = p_iv_id
1738 AND rr.element_type_id = p_ele_type_id
1739 AND rr.run_result_id = rrv.run_result_id
1740 AND rr.assignment_action_id = paa.assignment_action_id
1741 AND paa.assignment_action_id = p_assignment_action_id
1742 AND ppa.payroll_action_id = paa.payroll_action_id
1743 AND ppa.action_type IN ('Q','R')
1744 AND rrv.result_value IS NOT NULL
1745 group by pr.result_value,rrv.result_value;*/
1746
1747 rec_sum_of_result_values csr_sum_of_result_values%ROWTYPE;
1748 -----------------------------------------------------------------------------
1749
1750 -----------------------------------------------------------------------------
1751 /* Cursor to retrieve sum of all run result value for an given Main Element */
1752 -----------------------------------------------------------------------------
1753 CURSOR csr_all_sum_result_values(p_iv_id_UNIT NUMBER,p_iv_id_AMOUNT NUMBER,p_iv_id_UNIT_PRICE NUMBER
1754 ,p_ele_type_id NUMBER
1755 ,p_assignment_action_id NUMBER
1756 ) IS
1757 SELECT rrv3.result_value UNIT_PRICE , sum(rrv1.result_value) UNIT, sum(rrv2.result_value) AMOUNT
1758 FROM pay_run_result_values rrv1
1759 ,pay_run_results rr1
1760 ,pay_assignment_actions paa
1761 ,pay_payroll_actions ppa
1762 ,pay_run_result_values rrv2
1763 ,pay_run_results rr2
1764 ,pay_run_result_values rrv3
1765 ,pay_run_results rr3
1766 WHERE rrv1.input_value_id = p_iv_id_UNIT
1767 AND rr1.element_type_id = p_ele_type_id
1768 AND rr1.run_result_id = rrv1.run_result_id
1769 AND rr1.assignment_action_id = paa.assignment_action_id
1770 AND paa.assignment_action_id = p_assignment_action_id
1771 AND ppa.payroll_action_id = paa.payroll_action_id
1772 AND ppa.action_type IN ('Q','R')
1773 and rrv2.input_value_id = p_iv_id_AMOUNT
1774 AND rr2.run_result_id = rrv2.run_result_id
1775 AND rr2.element_entry_id = rr1.element_entry_id
1776 AND rr2.assignment_action_id = paa.assignment_action_id
1777 and rrv3.input_value_id = p_iv_id_UNIT_PRICE
1778 AND rr3.run_result_id = rrv3.run_result_id
1779 AND rr3.element_entry_id = rr1.element_entry_id
1780 AND rr3.assignment_action_id = paa.assignment_action_id
1781 group by rrv3.result_value;
1782
1783 -----------------------------------------------------------------------------
1784 -----------------------------------------------------------------------------
1785 /* Cursor to retrieve sum of all run result value for an given Main Element */
1786 -----------------------------------------------------------------------------
1787 CURSOR csr_all_result_values(p_iv_id_UNIT NUMBER,p_iv_id_AMOUNT NUMBER,p_iv_id_UNIT_PRICE NUMBER
1788 ,p_ele_type_id NUMBER
1789 ,p_assignment_action_id NUMBER
1790 ) IS
1791 SELECT rrv3.result_value UNIT_PRICE , rrv1.result_value UNIT, rrv2.result_value AMOUNT
1792 FROM pay_run_result_values rrv1
1793 ,pay_run_results rr1
1794 ,pay_assignment_actions paa
1795 ,pay_payroll_actions ppa
1796 ,pay_run_result_values rrv2
1797 ,pay_run_results rr2
1798 ,pay_run_result_values rrv3
1799 ,pay_run_results rr3
1800 WHERE rrv1.input_value_id = p_iv_id_UNIT
1801 AND rr1.element_type_id = p_ele_type_id
1802 AND rr1.run_result_id = rrv1.run_result_id
1803 AND rr1.assignment_action_id = paa.assignment_action_id
1804 AND paa.assignment_action_id = p_assignment_action_id
1805 AND ppa.payroll_action_id = paa.payroll_action_id
1806 AND ppa.action_type IN ('Q','R')
1807 and rrv2.input_value_id = p_iv_id_AMOUNT
1808 AND rr2.run_result_id = rrv2.run_result_id
1809 AND rr2.element_entry_id = rr1.element_entry_id
1810 AND rr2.assignment_action_id = paa.assignment_action_id
1811 and rrv3.input_value_id = p_iv_id_UNIT_PRICE
1812 AND rr3.run_result_id = rrv3.run_result_id
1813 AND rr3.element_entry_id = rr1.element_entry_id
1814 AND rr3.assignment_action_id = paa.assignment_action_id;
1815
1816
1817 -----------------------------------------------------------------------------
1818
1819 rec_get_element csr_get_element%ROWTYPE;
1820 l_result_value pay_run_result_values.result_value%TYPE := 0;
1821 l_action_info_id NUMBER;
1822 l_ovn NUMBER;
1823 l_element_context VARCHAR2(10);
1824 l_index NUMBER := 0;
1825 l_formatted_value VARCHAR2(50) := NULL;
1826 l_flag NUMBER := 0;
1827 l_group_by number(10);
1828 l_unit_price NUMBER ;
1829 l_amount NUMBER;
1830 l_UNIT NUMBER;
1831 BEGIN
1832 IF g_debug THEN
1833 hr_utility.set_location(' Entering Procedure ARCHIVE_ADD_ELEMENT',320);
1834 END IF;
1835 IF p_archive_flag = 'N' THEN
1836 ---------------------------------------------------
1837 --Check if global table has already been populated
1838 ---------------------------------------------------
1839 IF g_element_table.count = 0 THEN
1840 OPEN csr_get_element(g_business_group_id);
1841 LOOP
1842 FETCH csr_get_element INTO rec_get_element;
1843 EXIT WHEN csr_get_element%NOTFOUND;
1844 l_element_context := 'F'; --Additional Element Context
1845 SETUP_ELEMENT_DEFINITIONS(p_classification_name => rec_get_element.classification_name
1846 ,p_element_name => rec_get_element.element_narrative
1847 ,p_element_type_id => rec_get_element.element_type_id
1848 ,p_input_value_id => rec_get_element.input_value_id
1849 ,p_element_type => l_element_context
1850 ,p_uom => rec_get_element.uom
1851 --,p_Element_code => rec_get_element.element_code
1852 ,p_archive_flag => p_archive_flag);
1853 END LOOP;
1854 CLOSE csr_get_element;
1855 END IF;
1856 ELSIF p_archive_flag = 'Y' AND g_element_table.count > 0 THEN
1857 FOR l_index IN g_element_table.first.. g_element_table.last LOOP
1858 l_result_value := NULL;
1859 l_group_by :=null;
1860 l_unit_price :=null;
1861 BEGIN
1862
1863 -- Conditions below are added to flush the Record Set After a Successful Query
1864 -- so that it starts afresh for the next element
1865 rec_group_by.ORG_INFORMATION6:= NULL;
1866 rec_group_by.ORG_INFORMATION3:= NULL;
1867
1868
1869 OPEN csr_group_by(g_element_table(l_index).element_type_id );
1870 FETCH csr_group_by
1871 INTO rec_group_by;
1872 CLOSE csr_group_by;
1873
1874 /*
1875 FOR csr_result_rec IN csr_sum_of_result_values(g_element_table(l_index).input_value_id
1876 ,g_element_table(l_index).element_type_id
1877 ,p_assignment_action_id
1878 ,rec_group_by.ORG_INFORMATION3)
1879 LOOP
1880 IF csr_result_rec.result_value is not null THEN
1881 pay_action_information_api.create_action_information (
1882 p_action_information_id => l_action_info_id
1883 ,p_action_context_id => p_archive_assact_id
1884 ,p_action_context_type => 'AAP'
1885 ,p_object_version_number => l_ovn
1886 ,p_effective_date => p_effective_date
1887 ,p_source_id => NULL
1888 ,p_source_text => NULL
1889 ,p_action_information_category => 'EMEA ELEMENT INFO'
1890 ,p_action_information1 => g_element_table(l_index).element_type_id
1891 ,p_action_information2 => g_element_table(l_index).input_value_id
1892 ,p_action_information3 => g_element_table(l_index).element_type
1893 ,p_action_information4 => fnd_number.number_to_canonical(csr_result_rec.result_value) --l_formatted_value
1894 ,p_action_information8 => csr_result_rec.record_count
1895 ,p_action_information9 => 'Additional Element unit per price :'||csr_result_rec.UNIT_PRICE
1896 ,p_assignment_id => p_assignment_id);
1897
1898 --fnd_file.put_line(fnd_file.log,' Group BY YES ++++++++ADD EMEA ELEMENT INFO ');
1899 --fnd_file.put_line(fnd_file.log,' l_action_info_id '||l_action_info_id);
1900 --fnd_file.put_line(fnd_file.log,' p_archive_assact_id '||p_archive_assact_id);
1901 --fnd_file.put_line(fnd_file.log,' g_element_table(l_index).element_type_id '||g_element_table(l_index).element_type_id);
1902 --fnd_file.put_line(fnd_file.log,' g_element_table(l_index).input_value_id '||g_element_table(l_index).input_value_id);
1903 --fnd_file.put_line(fnd_file.log,' g_element_table(l_index).element_type '||g_element_table(l_index).element_type);
1904 --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));
1905 --fnd_file.put_line(fnd_file.log,' p_assignment_id '||p_assignment_id);
1906 END IF;
1907 END LOOP;*/
1908
1909 -- The se_soe contains
1910 -- segment 3 = > I or O
1911 -- segment 6 = > Y or N
1912 -- segment 7 = > Input ID UNIT
1913 -- segment 8 = > Input ID UNIT PRICE
1914 -- segment 9 = > Input ID Amount
1915 IF ( (rec_group_by.ORG_INFORMATION3 = 'O') or (rec_group_by.ORG_INFORMATION3 IS NULL) )
1916 THEN
1917 -- Case for Group by or NOT
1918 -- Segemnt 6 is allowed here, as it makes sense.
1919 IF ( rec_group_by.ORG_INFORMATION6 = 'N' or (rec_group_by.ORG_INFORMATION3 IS NULL) )
1920 THEN
1921 -- This csae iis for individual representation of each element.
1922 -- unit and unit price should be absent.
1923 FOR csr_result_rec IN csr_result_value(g_element_table(l_index).input_value_id
1924 ,g_element_table(l_index).element_type_id
1925 ,p_assignment_action_id)
1926 LOOP
1927 IF csr_result_rec.result_value is not null THEN
1928 pay_action_information_api.create_action_information (
1929 p_action_information_id => l_action_info_id
1930 ,p_action_context_id => p_archive_assact_id
1931 ,p_action_context_type => 'AAP'
1932 ,p_object_version_number => l_ovn
1933 ,p_effective_date => p_effective_date
1934 ,p_source_id => NULL
1935 ,p_source_text => NULL
1936 ,p_action_information_category => 'EMEA ELEMENT INFO'
1937 ,p_action_information1 => g_element_table(l_index).element_type_id
1938 ,p_action_information2 => g_element_table(l_index).input_value_id
1939 ,p_action_information3 => g_element_table(l_index).element_type
1940 ,p_action_information4 => fnd_number.number_to_canonical(csr_result_rec.result_value) --l_formatted_value
1941 ,p_action_information8 => ''
1942 ,p_action_information9 => 'Additional Element:'
1943 ,p_assignment_id => p_assignment_id);
1944
1945 --fnd_file.put_line(fnd_file.log,'group by NO l_action_info_id ' || l_action_info_id );
1946 --fnd_file.put_line(fnd_file.log,'Earning Element ');
1947 --fnd_file.put_line(fnd_file.log,'csr_rec.element_type_id ' || g_element_table(l_index).element_type_id );
1948 --fnd_file.put_line(fnd_file.log,'csr_rec.input_value_id ' || g_element_table(l_index).input_value_id );
1949 --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) );
1950 END IF;
1951 END LOOP;
1952
1953 ELSIF rec_group_by.ORG_INFORMATION6 = 'Y'
1954 THEN
1955 -- This csae iis for Grouping by pay value of each element.
1956 -- unit and unit price should be present
1957 FOR csr_result_rec IN csr_sum_of_result_values(g_element_table(l_index).input_value_id
1958 ,g_element_table(l_index).element_type_id
1959 ,p_assignment_action_id )
1960 LOOP
1961 IF csr_result_rec.result_value is not null THEN
1962 pay_action_information_api.create_action_information (
1963 p_action_information_id => l_action_info_id
1964 ,p_action_context_id => p_archive_assact_id
1965 ,p_action_context_type => 'AAP'
1966 ,p_object_version_number => l_ovn
1967 ,p_effective_date => p_effective_date
1968 ,p_source_id => NULL
1969 ,p_source_text => NULL
1970 ,p_action_information_category => 'EMEA ELEMENT INFO'
1971 ,p_action_information1 => g_element_table(l_index).element_type_id
1972 ,p_action_information2 => g_element_table(l_index).input_value_id
1973 ,p_action_information3 => g_element_table(l_index).element_type
1974 ,p_action_information4 => fnd_number.number_to_canonical(csr_result_rec.result_value) --l_formatted_value
1975 ,p_action_information8 => csr_result_rec.record_count
1976 ,p_action_information9 => 'Additional Element unit per price:'||csr_result_rec.UNIT_PRICE
1977 ,p_assignment_id => p_assignment_id);
1978
1979 --fnd_file.put_line(fnd_file.log,'group by NO l_action_info_id ' || l_action_info_id );
1980 --fnd_file.put_line(fnd_file.log,'Earning Element ');
1981 --fnd_file.put_line(fnd_file.log,'csr_rec.element_type_id ' || g_element_table(l_index).element_type_id );
1982 --fnd_file.put_line(fnd_file.log,'csr_rec.input_value_id ' || g_element_table(l_index).input_value_id );
1983 --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) );
1984 END IF;
1985 END LOOP;
1986 END IF;
1987 ELSIF rec_group_by.ORG_INFORMATION3 = 'I'
1988 THEN
1989 -- Case for UNIT,PRICE,AMOUNT
1990 -- Segment 7,8,9 is allowed
1991 -- Segemnt 6 is not allowed here, as it doesn't makes sense.
1992 -- segment 7 = > Input ID UNIT
1993 -- segment 8 = > Input ID UNIT PRICE
1994 -- segment 9 = > Input ID Amount
1995 IF rec_group_by.ORG_INFORMATION7 IS NOT NULL AND
1996 rec_group_by.ORG_INFORMATION8 IS NOT NULL AND
1997 rec_group_by.ORG_INFORMATION9 IS NOT NULL
1998 THEN
1999 -- All three are selected, we can group by three in single query
2000
2001 IF rec_group_by.ORG_INFORMATION10 = 'Y'
2002 THEN
2003 FOR csr_result_rec IN csr_all_sum_result_values(rec_group_by.ORG_INFORMATION7
2004 ,rec_group_by.ORG_INFORMATION9
2005 ,rec_group_by.ORG_INFORMATION8
2006 ,g_element_table(l_index).element_type_id
2007 ,p_assignment_action_id )
2008 LOOP
2009 IF csr_result_rec.AMOUNT is not null THEN
2010 pay_action_information_api.create_action_information (
2011 p_action_information_id => l_action_info_id
2012 ,p_action_context_id => p_archive_assact_id
2013 ,p_action_context_type => 'AAP'
2014 ,p_object_version_number => l_ovn
2015 ,p_effective_date => p_effective_date
2016 ,p_source_id => NULL
2017 ,p_source_text => NULL
2018 ,p_action_information_category => 'EMEA ELEMENT INFO'
2019 ,p_action_information1 => g_element_table(l_index).element_type_id
2020 ,p_action_information2 => g_element_table(l_index).input_value_id
2021 ,p_action_information3 => g_element_table(l_index).element_type
2022 ,p_action_information4 => fnd_number.number_to_canonical(csr_result_rec.AMOUNT) --l_formatted_value
2023 ,p_action_information8 => csr_result_rec.UNIT
2024 ,p_action_information9 => 'Additional Element unit per price:'||csr_result_rec.UNIT_PRICE
2025 ,p_assignment_id => p_assignment_id);
2026
2027 --fnd_file.put_line(fnd_file.log,'group by NO l_action_info_id ' || l_action_info_id );
2028 --fnd_file.put_line(fnd_file.log,'Earning Element ');
2029 --fnd_file.put_line(fnd_file.log,'csr_result_rec.UNIT ' || csr_result_rec.UNIT );
2030 --fnd_file.put_line(fnd_file.log,'csr_result_rec.WP WP ' || csr_result_rec.UNIT_PRICE );
2031 --fnd_file.put_line(fnd_file.log,'fnd_number.number_to_canonical(l_result_value) ' || fnd_number.number_to_canonical(csr_result_rec.AMOUNT) );
2032 END IF;
2033 END LOOP;
2034 ELSE
2035 FOR csr_result_rec IN csr_all_result_values(rec_group_by.ORG_INFORMATION7
2036 ,rec_group_by.ORG_INFORMATION9
2037 ,rec_group_by.ORG_INFORMATION8
2038 ,g_element_table(l_index).element_type_id
2039 ,p_assignment_action_id )
2040 LOOP
2041 IF csr_result_rec.AMOUNT is not null THEN
2042 pay_action_information_api.create_action_information (
2043 p_action_information_id => l_action_info_id
2044 ,p_action_context_id => p_archive_assact_id
2045 ,p_action_context_type => 'AAP'
2046 ,p_object_version_number => l_ovn
2047 ,p_effective_date => p_effective_date
2048 ,p_source_id => NULL
2049 ,p_source_text => NULL
2050 ,p_action_information_category => 'EMEA ELEMENT INFO'
2051 ,p_action_information1 => g_element_table(l_index).element_type_id
2052 ,p_action_information2 => g_element_table(l_index).input_value_id
2053 ,p_action_information3 => g_element_table(l_index).element_type
2054 ,p_action_information4 => fnd_number.number_to_canonical(csr_result_rec.AMOUNT) --l_formatted_value
2055 ,p_action_information8 => csr_result_rec.UNIT
2056 ,p_action_information9 => 'Additional Element unit per price:'||csr_result_rec.UNIT_PRICE
2057 ,p_assignment_id => p_assignment_id);
2058
2059 --fnd_file.put_line(fnd_file.log,'group by NO l_action_info_id ' || l_action_info_id );
2060 --fnd_file.put_line(fnd_file.log,'Earning Element ');
2061 --fnd_file.put_line(fnd_file.log,'csr_result_rec.UNIT ' || csr_result_rec.UNIT );
2062 --fnd_file.put_line(fnd_file.log,'csr_result_rec.WP WP ' || csr_result_rec.UNIT_PRICE );
2063 --fnd_file.put_line(fnd_file.log,'fnd_number.number_to_canonical(l_result_value) ' || fnd_number.number_to_canonical(csr_result_rec.AMOUNT) );
2064 END IF;
2065 END LOOP;
2066
2067 END IF;-- end of rec_group_by.ORG_INFORMATION10 = 'Y'
2068 ELSE -- Three inputs are not selected.
2069 -- have to get the each input value id and find value for each
2070 -- and archive it if the amount is not null
2071
2072 -- Case for UNIT,PRICE,AMOUNT
2073 -- Segment 7,8,9 is allowed
2074 -- Segemnt 6 is not allowed here, as it doesn't makes sense.
2075 -- segment 7 = > Input ID UNIT
2076 -- segment 8 = > Input ID UNIT PRICE
2077 -- segment 9 = > Input ID Amount
2078 IF rec_group_by.ORG_INFORMATION9 IS NOT NULL
2079 THEN
2080 -- amount should not be null
2081 -- find the amount value and element entry id of this element
2082 -- for this element entry find the the other selected columns like UNIT , UNIT PRICE if selected
2083 --
2084 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)
2085 LOOP
2086
2087 -- we have EE id
2088 l_amount := csr_result_rec.result_value;
2089 IF rec_group_by.ORG_INFORMATION8 IS NOT NULL
2090 THEN
2091 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 );
2092 FETCH csr_result_value_EE
2093 INTO l_unit_price;
2094 CLOSE csr_result_value_EE;
2095 ELSE
2096 l_unit_price :=NULL;
2097 END IF; -- End if of segment 8 , unit price
2098
2099 IF rec_group_by.ORG_INFORMATION7 IS NOT NULL
2100 THEN
2101 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 );
2102 FETCH csr_result_value_EE
2103 INTO l_unit;
2104 CLOSE csr_result_value_EE;
2105 ELSE
2106 l_unit :=NULL;
2107 END IF; -- End if of segment 7 , unit
2108
2109 -- Resume again
2110 IF csr_result_rec.result_value is not null THEN
2111 pay_action_information_api.create_action_information (
2112 p_action_information_id => l_action_info_id
2113 ,p_action_context_id => p_archive_assact_id
2114 ,p_action_context_type => 'AAP'
2115 ,p_object_version_number => l_ovn
2116 ,p_effective_date => p_effective_date
2117 ,p_source_id => NULL
2118 ,p_source_text => NULL
2119 ,p_action_information_category => 'EMEA ELEMENT INFO'
2120 ,p_action_information1 => g_element_table(l_index).element_type_id
2121 ,p_action_information2 => g_element_table(l_index).input_value_id
2122 ,p_action_information3 => g_element_table(l_index).element_type
2123 ,p_action_information4 => fnd_number.number_to_canonical(l_amount) --l_formatted_value
2124 ,p_action_information8 => l_unit
2125 ,p_action_information9 => 'Additional Element unit per price:'||l_uNIT_PRICE
2126 ,p_assignment_id => p_assignment_id);
2127
2128 --fnd_file.put_line(fnd_file.log,'group by NO l_action_info_id ' || l_action_info_id );
2129 --fnd_file.put_line(fnd_file.log,'Earning Element ');
2130 --fnd_file.put_line(fnd_file.log,'csr_rec.element_type_id ' || g_element_table(l_index).element_type_id );
2131 --fnd_file.put_line(fnd_file.log,'csr_rec.input_value_id ' || g_element_table(l_index).input_value_id );
2132 --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) );
2133 END IF; -- end of csr_result_rec.result_value is not null
2134 END LOOP;
2135 END IF; -- end of rec_group_by.ORG_INFORMATION9 IS NOT NULL
2136
2137 END IF;
2138
2139
2140 END IF;
2141 --OPEN csr_result_value(g_element_table(l_index).input_value_id ,g_element_table(l_index).element_type_id
2142 --,p_assignment_action_id);
2143 --FETCH csr_result_value INTO l_result_value;
2144 --CLOSE csr_result_value;
2145 /*
2146 OPEN csr_group_by(g_element_table(l_index).element_type_id );
2147 FETCH csr_group_by INTO rec_group_by;
2148 CLOSE csr_group_by;
2149 l_group_by :=rec_group_by.ORG_INFORMATION3;
2150
2151
2152 OPEN csr_result_value(rec_group_by.ORG_INFORMATION4,g_element_table(l_index).element_type_id ,p_assignment_action_id);
2153 FETCH csr_result_value INTO l_unit_price;
2154 CLOSE csr_result_value;
2155
2156 IF l_group_by !='Y'
2157 THEN
2158 FOR csr_result_rec IN csr_result_value(g_element_table(l_index).input_value_id ,g_element_table(l_index).element_type_id
2159 ,p_assignment_action_id)
2160 LOOP
2161 IF csr_result_rec.result_value is not null THEN
2162 pay_action_information_api.create_action_information (
2163 p_action_information_id => l_action_info_id
2164 ,p_action_context_id => p_archive_assact_id
2165 ,p_action_context_type => 'AAP'
2166 ,p_object_version_number => l_ovn
2167 ,p_effective_date => p_effective_date
2168 ,p_source_id => NULL
2169 ,p_source_text => NULL
2170 ,p_action_information_category => 'EMEA ELEMENT INFO'
2171 ,p_action_information1 => g_element_table(l_index).element_type_id
2172 ,p_action_information2 => g_element_table(l_index).input_value_id
2173 ,p_action_information3 => g_element_table(l_index).element_type
2174 ,p_action_information4 => fnd_number.number_to_canonical(csr_result_rec.result_value) --l_formatted_value
2175 ,p_action_information8 => '1'
2176 ,p_action_information9 => 'Additional Element unit per price :'||l_unit_price
2177 ,p_assignment_id => p_assignment_id);
2178
2179 --fnd_file.put_line(fnd_file.log,' Group BY NO ++++++++ADD EMEA ELEMENT INFO ');
2180 --fnd_file.put_line(fnd_file.log,' l_action_info_id '||l_action_info_id);
2181 --fnd_file.put_line(fnd_file.log,' p_archive_assact_id '||p_archive_assact_id);
2182 --fnd_file.put_line(fnd_file.log,' g_element_table(l_index).element_type_id '||g_element_table(l_index).element_type_id);
2183 --fnd_file.put_line(fnd_file.log,' g_element_table(l_index).input_value_id '||g_element_table(l_index).input_value_id);
2184 --fnd_file.put_line(fnd_file.log,' g_element_table(l_index).element_type '||g_element_table(l_index).element_type);
2185 --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));
2186 --fnd_file.put_line(fnd_file.log,' p_assignment_id '||p_assignment_id);
2187
2188 END IF;
2189 END LOOP;
2190 l_group_by :=NULL;
2191 ELSE
2192
2193 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);
2194 FETCH csr_sum_of_result_values INTO rec_sum_of_result_values;
2195 CLOSE csr_sum_of_result_values;
2196
2197 l_result_value := rec_sum_of_result_values.result_value;
2198
2199 IF l_result_value is not null THEN
2200 pay_action_information_api.create_action_information (
2201 p_action_information_id => l_action_info_id
2202 ,p_action_context_id => p_archive_assact_id
2203 ,p_action_context_type => 'AAP'
2204 ,p_object_version_number => l_ovn
2205 ,p_effective_date => p_effective_date
2206 ,p_source_id => NULL
2207 ,p_source_text => NULL
2208 ,p_action_information_category => 'EMEA ELEMENT INFO'
2209 ,p_action_information1 => g_element_table(l_index).element_type_id
2210 ,p_action_information2 => g_element_table(l_index).input_value_id
2211 ,p_action_information3 => g_element_table(l_index).element_type
2212 ,p_action_information4 => fnd_number.number_to_canonical(l_result_value) --l_formatted_value
2213 ,p_action_information8 => rec_sum_of_result_values.record_count
2214 ,p_action_information9 => 'Additional Element unit per price :'||l_unit_price
2215 ,p_assignment_id => p_assignment_id);
2216
2217 --fnd_file.put_line(fnd_file.log,' Group BY YES ++++++++ADD EMEA ELEMENT INFO ');
2218 --fnd_file.put_line(fnd_file.log,' l_action_info_id '||l_action_info_id);
2219 --fnd_file.put_line(fnd_file.log,' p_archive_assact_id '||p_archive_assact_id);
2220 --fnd_file.put_line(fnd_file.log,' g_element_table(l_index).element_type_id '||g_element_table(l_index).element_type_id);
2221 --fnd_file.put_line(fnd_file.log,' g_element_table(l_index).input_value_id '||g_element_table(l_index).input_value_id);
2222 --fnd_file.put_line(fnd_file.log,' g_element_table(l_index).element_type '||g_element_table(l_index).element_type);
2223 --fnd_file.put_line(fnd_file.log,' fnd_number.number_to_canonical(l_result_value) '||fnd_number.number_to_canonical(l_result_value));
2224 --fnd_file.put_line(fnd_file.log,' p_assignment_id '||p_assignment_id);
2225 END IF;
2226 l_group_by :=NULL;
2227 END IF;-- End of l_group_by
2228 */
2229 EXCEPTION WHEN OTHERS THEN
2230 g_err_num := SQLCODE;
2231 /*fnd_file.put_line(fnd_file.log,'ORA_ERR: ' || g_err_num || 'In ARCHIVE_ADD_ELEMENT');*/
2232 IF g_debug THEN
2233 hr_utility.set_location('ORA_ERR: ' || g_err_num || 'In ARCHIVE_ADD_ELEMENT',330);
2234 END IF;
2235 END;
2236 END LOOP;
2237 END IF;
2238 IF g_debug THEN
2239 hr_utility.set_location(' Leaving Procedure ARCHIVE_ADD_ELEMENT',340);
2240 END IF;
2241 END ARCHIVE_ADD_ELEMENT;
2242 /* OTHER BALANCES REGION */
2243 PROCEDURE ARCHIVE_OTH_BALANCE (p_archive_assact_id IN NUMBER,
2244 p_assignment_action_id IN NUMBER,
2245 p_assignment_id IN NUMBER,
2246 p_payroll_action_id IN NUMBER,
2247 p_record_count IN NUMBER,
2248 p_pre_payact_id IN NUMBER,
2249 p_effective_date IN DATE,
2250 p_date_earned IN DATE,
2251 p_archive_flag IN VARCHAR2) IS
2252 /* Cursor to retrieve Other Balances Information */
2253 CURSOR csr_get_balance(p_bus_grp_id NUMBER) IS
2254 SELECT org_information4 balance_type_id
2255 ,org_information5 balance_dim_id
2256 ,org_information7 narrative
2257 FROM hr_organization_information
2258 WHERE organization_id = p_bus_grp_id
2259 AND org_information_context = 'Business Group:Payslip Info'
2260 AND org_information1 = 'BALANCE';
2261 /* Cursor to retrieve Tax Unit Id for setting context */
2262 CURSOR csr_tax_unit (p_run_assact_id NUMBER) IS
2263 SELECT paa.tax_unit_id
2264 FROM pay_assignment_actions paa
2265 WHERE paa.assignment_action_id = p_run_assact_id;
2266 /* Cursor to fetch defined balance id */
2267 CURSOR csr_def_balance(bal_type_id NUMBER, bal_dim_id NUMBER) IS
2268 SELECT defined_balance_id
2269 FROM pay_defined_balances
2270 WHERE balance_type_id = bal_type_id
2271 AND balance_dimension_id = bal_dim_id;
2272 rec_get_balance csr_get_balance%ROWTYPE;
2273 l_balance_value NUMBER := 0;
2274 l_action_info_id NUMBER;
2275 l_ovn NUMBER;
2276 l_index NUMBER;
2277 l_tu_id NUMBER;
2278 l_defined_balance_id NUMBER:=0;
2279 l_formatted_value VARCHAR2(50) := NULL;
2280 l_flag NUMBER := 0;
2281 BEGIN
2282 IF g_debug THEN
2283 hr_utility.set_location(' Entering Procedure ARCHIVE_OTH_BALANCE',350);
2284 END IF;
2285
2286 --fnd_file.put_line(fnd_file.log,'Entering In ARCHIVE_OTH_BALANCE global');
2287 IF p_archive_flag = 'N' THEN
2288 ---------------------------------------------------
2289 --Check if global table has already been populated
2290 ---------------------------------------------------
2291 --fnd_file.put_line(fnd_file.log,'g_business_group_id == '|| g_business_group_id);
2292 IF g_user_balance_table.count = 0 THEN
2293 OPEN csr_get_balance(g_business_group_id);
2294 LOOP
2295 FETCH csr_get_balance INTO rec_get_balance;
2296 EXIT WHEN csr_get_balance%NOTFOUND;
2297 OPEN csr_def_balance(rec_get_balance.balance_type_id,rec_get_balance.balance_dim_id);
2298 FETCH csr_def_balance INTO l_defined_balance_id;
2299 CLOSE csr_def_balance;
2300 --fnd_file.put_line(fnd_file.log,'Calling SETUP_BALANCE_DEFINITIONS in Archive oth balance');
2301 PAY_SE_PAYSLIP_ARCHIVE.SETUP_BALANCE_DEFINITIONS
2302 (p_balance_name => rec_get_balance.narrative
2303 ,p_defined_balance_id => l_defined_balance_id
2304 ,p_balance_type_id => rec_get_balance.balance_type_id);
2305 END LOOP;
2306 CLOSE csr_get_balance;
2307 END IF;
2308 ELSIF p_archive_flag = 'Y' THEN
2309
2310 OPEN csr_tax_unit(p_assignment_action_id);
2311 FETCH csr_tax_unit INTO l_tu_id;
2312 CLOSE csr_tax_unit;
2313
2314 PAY_BALANCE_PKG.SET_CONTEXT('TAX_UNIT_ID',l_tu_id);
2315 PAY_BALANCE_PKG.SET_CONTEXT('DATE_EARNED',fnd_date.date_to_canonical(p_date_earned));
2316 IF g_user_balance_table.count > 0 THEN
2317
2318 FOR l_index IN g_user_balance_table.first.. g_user_balance_table.last LOOP
2319 l_balance_value := pay_balance_pkg.get_value(g_user_balance_table(l_index).defined_balance_id,p_assignment_action_id);
2320 IF l_balance_value > 0 THEN
2321
2322 pay_action_information_api.create_action_information (
2323 p_action_information_id => l_action_info_id
2324 ,p_action_context_id => p_archive_assact_id
2325 ,p_action_context_type => 'AAP'
2326 ,p_object_version_number => l_ovn
2327 ,p_effective_date => p_effective_date
2328 ,p_source_id => NULL
2329 ,p_source_text => NULL
2330 ,p_action_information_category => 'EMEA BALANCES'
2331 ,p_action_information1 => g_user_balance_table(l_index).defined_balance_id
2332 ,p_action_information2 => 'OBAL' --Other Balances Context
2333 ,p_action_information4 => fnd_number.number_to_canonical(l_balance_value) --l_formatted_value
2334 ,p_action_information5 => NULL
2335 ,p_action_information6 => 'Other Balances'
2336 ,p_assignment_id => p_assignment_id);
2337
2338 --fnd_file.put_line(fnd_file.log,' Other Balances ');
2339 --fnd_file.put_line(fnd_file.log,' l_action_info_id '||l_action_info_id);
2340 --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);
2341 --fnd_file.put_line(fnd_file.log,' fnd_number.number_to_canonical(l_balance_value) '||fnd_number.number_to_canonical(l_balance_value));
2342
2343
2344 END IF;
2345 END LOOP;
2346 END IF; /* For table count check */
2347 END IF;
2348 --fnd_file.put_line(fnd_file.log,'Leaving ARCHIVE_OTH_BALANCE global');
2349 EXCEPTION WHEN OTHERS THEN
2350 g_err_num := SQLCODE;
2351 --fnd_file.put_line(fnd_file.log,'ORA_ERR: ' || g_err_num || 'In ARCHIVE_OTH_BALANCE'||SQLERRM);
2352 IF g_debug THEN
2353 hr_utility.set_location('ORA_ERR: ' || g_err_num || 'In ARCHIVE_OTH_BALANCE',360);
2354 END IF;
2355 END ARCHIVE_OTH_BALANCE;
2356 /*Additional Employee Details*/
2357 PROCEDURE ARCHIVE_ADDL_EMP_DETAILS(p_archive_assact_id IN NUMBER
2358 ,p_assignment_id IN NUMBER
2359 ,p_assignment_action_id IN NUMBER
2360 ,p_effective_date IN DATE
2361 ,p_date_earned IN DATE)
2362 IS
2363 CURSOR CSR_ACTUAL_TERM_DATE (p_assignment_id NUMBER) IS
2364 SELECT actual_termination_date
2365 FROM per_periods_of_service pps,
2366 per_all_assignments_f paa
2367 WHERE pps.period_of_service_id = paa.period_of_service_id
2368 AND p_date_earned between paa.effective_start_date and paa.effective_end_date
2369 AND paa.assignment_id = p_assignment_id;
2370 CURSOR get_details(p_assignment_id NUMBER , p_input_value VARCHAR2 ) IS
2371 SELECT ee.effective_start_date
2372 ,eev1.screen_entry_value screen_entry_value
2373 FROM per_all_assignments_f asg1
2374 ,per_all_assignments_f asg2
2375 ,per_all_people_f per
2376 ,pay_element_links_f el
2377 ,pay_element_types_f et
2378 ,pay_input_values_f iv1
2379 ,pay_element_entries_f ee
2380 ,pay_element_entry_values_f eev1
2381 WHERE asg1.assignment_id = p_assignment_id
2382 AND p_date_earned BETWEEN asg1.effective_start_date AND asg1.effective_end_date
2383 AND p_date_earned BETWEEN asg2.effective_start_date AND asg2.effective_end_date
2384 AND p_date_earned BETWEEN per.effective_start_date AND per.effective_end_date
2385 AND per.person_id = asg1.person_id
2386 AND asg2.person_id = per.person_id
2387 AND asg2.primary_flag = 'Y'
2388 AND et.element_name = 'Tax Card'
2389 AND et.legislation_code = 'SE'
2390 AND iv1.element_type_id = et.element_type_id
2391 AND iv1.name = p_input_value
2392 AND el.business_group_id = per.business_group_id
2393 AND el.element_type_id = et.element_type_id
2394 AND ee.assignment_id = asg2.assignment_id
2395 AND ee.element_link_id = el.element_link_id
2396 AND eev1.element_entry_id = ee.element_entry_id
2397 AND eev1.input_value_id = iv1.input_value_id
2398 AND p_date_earned BETWEEN ee.effective_start_date AND ee.effective_end_date
2399 AND p_date_earned BETWEEN eev1.effective_start_date AND eev1.effective_end_date;
2400 CURSOR csr_tax_details(p_assignment_id NUMBER, p_input_value VARCHAR2) IS
2401 SELECT ee.effective_start_date
2402 ,eev1.screen_entry_value screen_entry_value
2403 FROM per_all_assignments_f asg1
2404 ,pay_element_links_f el
2405 ,pay_element_types_f et
2406 ,pay_input_values_f iv1
2407 ,pay_element_entries_f ee
2408 ,pay_element_entry_values_f eev1
2409 WHERE asg1.assignment_id = p_assignment_id
2410 AND p_date_earned BETWEEN asg1.effective_start_date AND asg1.effective_end_date
2411 AND et.element_name = 'Tax'
2412 AND et.legislation_code = 'SE'
2413 AND iv1.element_type_id = et.element_type_id
2414 AND iv1.name = p_input_value
2415 AND el.element_type_id = et.element_type_id
2416 AND ee.assignment_id = asg1.assignment_id
2417 AND ee.element_link_id = el.element_link_id
2418 AND eev1.element_entry_id = ee.element_entry_id
2419 AND eev1.input_value_id = iv1.input_value_id
2420 AND p_date_earned BETWEEN ee.effective_start_date AND ee.effective_end_date
2421 AND p_date_earned BETWEEN eev1.effective_start_date AND eev1.effective_end_date;
2422 CURSOR csr_tax_category (p_assignment_id NUMBER) IS
2423 SELECT segment13
2424 FROM per_all_assignments_f paa,
2425 hr_soft_coding_keyflex hsc
2426 WHERE
2427 paa.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
2428 AND p_date_earned BETWEEN paa.effective_start_date
2429 AND paa.effective_end_date
2430 AND paa.assignment_id = p_assignment_id;
2431 CURSOR csr_global_value (p_global_name VARCHAR2) IS
2432 SELECT global_value
2433 FROM ff_globals_f
2434 WHERE global_name = p_global_name
2435 AND p_date_earned BETWEEN effective_start_date AND effective_end_date;
2436
2437 CURSOR c_bal_attrid (p_attribute_name VARCHAR2 ) IS
2438 SELECT attribute_id
2439 FROM pay_bal_attribute_definitions
2440 WHERE legislation_code='SE'
2441 AND attribute_name= p_attribute_name;
2442
2443
2444 CURSOR c_bal_defid (p_attribute_id NUMBER ) IS
2445 SELECT defined_balance_id
2446 FROM pay_balance_attributes
2447 WHERE attribute_id= p_attribute_id;
2448
2449
2450 l_actual_termination_date PER_PERIODS_OF_SERVICE.ACTUAL_TERMINATION_DATE%TYPE;
2451 l_tax_card_effective_date DATE;
2452 l_tax_card_type VARCHAR2(50);
2453 l_Tax_Percentage NUMBER(5,2);
2454 l_Tax_Table_Number NUMBER(5,2);
2455 l_Tax_Column NUMBER(10);
2456 l_Tax_Free_Threshold NUMBER (10);
2457 l_Calculation_Code varchar2(10);
2458 l_Calculation_Sum varchar2(10);
2459
2460 l_ovn NUMBER ;
2461 l_rec get_details%ROWTYPE;
2462 l_tax_rec csr_tax_details%ROWTYPE;
2463 l_action_info_id pay_action_information.action_information_id%TYPE;
2464
2465 l_defined_balance_id pay_defined_balances.defined_balance_id%TYPE;
2466
2467
2468 BEGIN
2469
2470 --fnd_file.put_line(fnd_file.log,' Entering Addl Employee details');
2471
2472 OPEN CSR_ACTUAL_TERM_DATE (p_assignment_id);
2473 FETCH CSR_ACTUAL_TERM_DATE INTO l_actual_termination_date;
2474 CLOSE CSR_ACTUAL_TERM_DATE;
2475 OPEN get_details(p_assignment_id ,'Tax Percentage' );
2476 FETCH get_details INTO l_rec;
2477 CLOSE get_details;
2478 l_Tax_Percentage := l_rec.screen_entry_value ;
2479 OPEN get_details(p_assignment_id , 'Tax Table Number' );
2480 FETCH get_details INTO l_rec;
2481 CLOSE get_details;
2482 l_Tax_Table_Number := l_rec.screen_entry_value ;
2483 OPEN get_details(p_assignment_id , 'Tax Column' );
2484 FETCH get_details INTO l_rec;
2485 CLOSE get_details;
2486 l_Tax_Column := l_rec.screen_entry_value ;
2487 OPEN get_details(p_assignment_id , 'Tax Free Threshold');
2488 FETCH get_details INTO l_rec;
2489 CLOSE get_details;
2490 l_Tax_Free_Threshold := l_rec.screen_entry_value ;
2491 OPEN get_details(p_assignment_id , 'Tax Card Type' );
2492 FETCH get_details INTO l_rec;
2493 CLOSE get_details;
2494 l_tax_card_effective_date := l_rec.effective_start_date;
2495 l_tax_card_type := l_rec.screen_entry_value ;
2496
2497 OPEN get_details(p_assignment_id , 'Calculation Code');
2498 FETCH get_details INTO l_rec;
2499 CLOSE get_details;
2500 l_Calculation_Code := null;
2501 l_Calculation_Code := l_rec.screen_entry_value ;
2502
2503 OPEN get_details(p_assignment_id , 'Calculation Sum');
2504 FETCH get_details INTO l_rec;
2505 CLOSE get_details;
2506 l_Calculation_Sum := null;
2507 l_Calculation_Sum := l_rec.screen_entry_value ;
2508 /* IF l_tax_card_type = 'TS' THEN
2509 IF l_base_rate IS NULL THEN
2510 OPEN csr_global_value ('FI_TAX_AT_SOURCE_PCT');
2511 FETCH csr_global_value INTO l_base_rate;
2512 CLOSE csr_global_value;
2513 END IF;
2514 END IF;
2515 IF l_tax_card_type = 'EI' THEN
2516 OPEN csr_tax_details(p_assignment_id, 'Extra Income Rate');
2517 FETCH csr_tax_details INTO l_tax_rec;
2518 CLOSE csr_tax_details;
2519 l_base_rate := l_rec.screen_entry_value ;
2520 OPEN csr_tax_details(p_assignment_id, 'Extra Income Additional Rate');
2521 FETCH csr_tax_details INTO l_tax_rec;
2522 CLOSE csr_tax_details;
2523 l_additional_rate := l_rec.screen_entry_value ;
2524 OPEN csr_tax_details(p_assignment_id, 'Extra Income Limit');
2525 FETCH csr_tax_details INTO l_tax_rec;
2526 CLOSE csr_tax_details;
2527 l_yearly_income_limit := l_rec.screen_entry_value ;
2528 OPEN csr_tax_details(p_assignment_id, 'Previous Extra Income Limit');
2529 FETCH csr_tax_details INTO l_tax_rec;
2530 CLOSE csr_tax_details;
2531 l_previous_income := l_rec.screen_entry_value ;
2532 l_tax_card_effective_date := l_tax_rec.effective_start_date;
2533 END IF;
2534 */
2535
2536 l_tax_card_type := hr_general.decode_lookup('SE_TAX_CARD_TYPE',l_tax_card_type ) ;
2537
2538 pay_action_information_api.create_action_information (
2539 p_action_information_id => l_action_info_id
2540 ,p_action_context_id => p_archive_assact_id
2541 ,p_action_context_type => 'AAP'
2542 ,p_object_version_number => l_ovn
2543 ,p_effective_date => p_effective_date
2544 ,p_source_id => NULL
2545 ,p_source_text => NULL
2546 ,p_action_information_category => 'ADDL EMPLOYEE DETAILS'
2547 ,p_action_information4 => fnd_date.date_to_displaydate(l_actual_termination_date)
2548 ,p_action_information5 => l_tax_card_type
2549 ,p_action_information6 => fnd_number.number_to_canonical(l_Tax_Percentage)
2550 ,p_action_information7 => fnd_number.number_to_canonical(l_Tax_Table_Number)
2551 ,p_action_information8 => fnd_number.number_to_canonical(l_Tax_Column)
2552 ,p_action_information9 => fnd_date.date_to_displaydate(l_tax_card_effective_date)
2553 ,p_action_information10 => fnd_number.number_to_canonical(l_Tax_Free_Threshold)
2554 ,p_action_information11 => fnd_number.number_to_canonical(l_Calculation_Code)
2555 ,p_action_information12 => fnd_number.number_to_canonical(l_Calculation_Sum)
2556 ,p_assignment_id => p_assignment_id);
2557
2558 -- fnd_file.put_line(fnd_file.log,' l_action_info_id '||l_action_info_id);
2559 --fnd_file.put_line(fnd_file.log,' p_archive_assact_id '||p_archive_assact_id);
2560 --fnd_file.put_line(fnd_file.log,' l_actual_termination_date '||l_actual_termination_date);
2561 -- fnd_file.put_line(fnd_file.log,' l_tax_card_type '||l_tax_card_type);
2562 -- fnd_file.put_line(fnd_file.log,' fnd_number.number_to_canonical(l_base_rate) '||fnd_number.number_to_canonical(l_Tax_Percentage));
2563
2564 -- removed Say Certificate
2565
2566 -- fnd_file.put_line(fnd_file.log,' Leaving Addl Employee details');
2567 EXCEPTION
2568 when others then
2569 NULL;
2570 END ARCHIVE_ADDL_EMP_DETAILS;
2571 /* ARCHIVE CODE */
2572 PROCEDURE ARCHIVE_CODE(p_assignment_action_id IN NUMBER
2573 ,p_effective_date IN DATE)
2574 IS
2575 /* Cursor to retrieve Payroll and Prepayment related Ids for Archival */
2576 CURSOR csr_archive_ids (p_locking_action_id NUMBER) IS
2577 SELECT prepay_assact.assignment_action_id prepay_assact_id
2578 ,prepay_assact.assignment_id prepay_assgt_id
2579 ,prepay_payact.payroll_action_id prepay_payact_id
2580 ,prepay_payact.effective_date prepay_effective_date
2581 ,run_assact.assignment_id run_assgt_id
2582 ,run_assact.assignment_action_id run_assact_id
2583 ,run_payact.payroll_action_id run_payact_id
2584 ,run_payact.payroll_id payroll_id
2585 FROM pay_action_interlocks archive_intlck
2586 ,pay_assignment_actions prepay_assact
2587 ,pay_payroll_actions prepay_payact
2588 ,pay_action_interlocks prepay_intlck
2589 ,pay_assignment_actions run_assact
2590 ,pay_payroll_actions run_payact
2591 WHERE archive_intlck.locking_action_id = p_locking_action_id
2592 AND prepay_assact.assignment_action_id = archive_intlck.locked_action_id
2593 AND prepay_payact.payroll_action_id = prepay_assact.payroll_action_id
2594 AND prepay_payact.action_type IN ('U','P')
2595 AND prepay_intlck.locking_action_id = prepay_assact.assignment_action_id
2596 AND run_assact.assignment_action_id = prepay_intlck.locked_action_id
2597 AND run_payact.payroll_action_id = run_assact.payroll_action_id
2598 AND run_payact.action_type IN ('Q', 'R')
2599 ORDER BY prepay_intlck.locking_action_id,prepay_intlck.locked_action_id desc;
2600 /* Cursor to retrieve time period information */
2601 CURSOR csr_period_end_date(p_assact_id NUMBER,p_pay_act_id NUMBER) IS
2602 SELECT ptp.end_date end_date,
2603 ptp.regular_payment_date regular_payment_date,
2604 ptp.time_period_id time_period_id,
2605 ppa.date_earned date_earned,
2606 ppa.effective_date effective_date,
2607 ptp.start_date start_date
2608 FROM per_time_periods ptp
2609 ,pay_payroll_actions ppa
2610 ,pay_assignment_actions paa
2611 WHERE ptp.payroll_id =ppa.payroll_id
2612 AND ppa.payroll_action_id =paa.payroll_action_id
2613 AND paa.assignment_action_id =p_assact_id
2614 AND ppa.payroll_action_id =p_pay_act_id
2615 AND ppa.date_earned BETWEEN ptp.start_date
2616 AND ptp.end_date;
2617 /* Cursor to retrieve Archive Payroll Action Id */
2618 CURSOR csr_archive_payact(p_assignment_action_id NUMBER) IS
2619 SELECT payroll_action_id
2620 FROM pay_assignment_actions
2621 WHERE assignment_Action_id = p_assignment_action_id;
2622 /* Cursor to retrieve Tax Unit Id for setting context */
2623 CURSOR csr_tax_unit (p_run_assact_id NUMBER) IS
2624 SELECT paa.tax_unit_id
2625 FROM pay_assignment_actions paa
2626 WHERE paa.assignment_action_id = p_run_assact_id;
2627
2628 l_tu_id NUMBER;
2629 l_archive_payact_id NUMBER;
2630 l_record_count NUMBER;
2631 l_actid NUMBER;
2632 l_end_date per_time_periods.end_date%TYPE;
2633 l_pre_end_date per_time_periods.end_date%TYPE;
2634 l_reg_payment_date per_time_periods.regular_payment_date%TYPE;
2635 l_pre_reg_payment_date per_time_periods.regular_payment_date%TYPE;
2636 l_date_earned pay_payroll_actions.date_earned%TYPE;
2637 l_pre_date_earned pay_payroll_actions.date_earned%TYPE;
2638 l_effective_date pay_payroll_actions.effective_date%TYPE;
2639 l_pre_effective_date pay_payroll_actions.effective_date%TYPE;
2640 l_run_payact_id NUMBER;
2641 l_action_context_id NUMBER;
2642 g_archive_pact NUMBER;
2643 p_assactid NUMBER;
2644 l_time_period_id per_time_periods.time_period_id%TYPE;
2645 l_pre_time_period_id per_time_periods.time_period_id%TYPE;
2646 l_start_date per_time_periods.start_date%TYPE;
2647 l_pre_start_date per_time_periods.start_date%TYPE;
2648 l_fnd_session NUMBER := 0;
2649 l_prev_prepay NUMBER := 0;
2650 BEGIN
2651 IF g_debug THEN
2652 hr_utility.set_location(' Entering Procedure ARCHIVE_CODE',380);
2653 END IF;
2654 --fnd_file.put_line(fnd_file.log,'Entering ARCHIVE_CODE ' || p_assignment_action_id );
2655
2656 OPEN csr_archive_payact(p_assignment_action_id);
2657 FETCH csr_archive_payact INTO l_archive_payact_id;
2658 CLOSE csr_archive_payact;
2659 l_record_count := 0;
2660 FOR rec_archive_ids IN csr_archive_ids(p_assignment_action_id) LOOP
2661
2662 OPEN csr_period_end_date(rec_archive_ids.run_assact_id,rec_archive_ids.run_payact_id);
2663 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;
2664 CLOSE csr_period_end_date;
2665 OPEN csr_period_end_date(rec_archive_ids.prepay_assact_id,rec_archive_ids.prepay_payact_id);
2666 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;
2667 CLOSE csr_period_end_date;
2668 OPEN csr_tax_unit(p_assignment_action_id);
2669 FETCH csr_tax_unit INTO l_tu_id;
2670 CLOSE csr_tax_unit;
2671
2672 --fnd_file.put_line(fnd_file.log,'p_assignment_action_id ' || p_assignment_action_id );
2673
2674 PAY_BALANCE_PKG.SET_CONTEXT('TAX_UNIT_ID',l_tu_id);
2675 PAY_BALANCE_PKG.SET_CONTEXT('DATE_EARNED',fnd_date.date_to_canonical(p_effective_date));
2676
2677 --fnd_file.put_line(fnd_file.log,'ARCHIVE_EMPLOYEE_DETAILS');
2678 -------------------------------------------------------------
2679 --Archive EMPLOYEE_DETAILS, PAYMENT_INFO and BALANCES
2680 --for every prepayment assignment action id
2681 -------------------------------------------------------------
2682 IF (rec_archive_ids.prepay_assact_id <> l_prev_prepay) THEN
2683
2684 ARCHIVE_EMPLOYEE_DETAILS
2685 (p_archive_assact_id => p_assignment_action_id
2686 ,p_assignment_id => rec_archive_ids.run_assgt_id
2687 ,p_assignment_action_id => rec_archive_ids.run_assact_id
2688 ,p_payroll_action_id => l_archive_payact_id
2689 ,p_time_period_id => l_time_period_id
2690 ,p_date_earned => l_date_earned
2691 ,p_pay_date_earned => l_date_earned
2692 ,p_effective_date => p_effective_date);
2693
2694 ARCHIVE_ADDL_EMP_DETAILS
2695 (p_archive_assact_id => p_assignment_action_id
2696 ,p_assignment_id => rec_archive_ids.run_assgt_id
2697 ,p_assignment_action_id => rec_archive_ids.run_assact_id
2698 ,p_effective_date => p_effective_date
2699 ,p_date_earned => l_date_earned);
2700
2701
2702 ARCHIVE_PAYMENT_INFO
2703 (p_archive_assact_id => p_assignment_action_id,
2704 p_prepay_assact_id => rec_archive_ids.prepay_assact_id,
2705 p_assignment_id => rec_archive_ids.prepay_assgt_id,
2706 p_date_earned => l_date_earned,
2707 p_effective_date => p_effective_date);
2708 --fnd_file.put_line(fnd_file.log,'ARCHIVE_OTH_BALANCE');
2709
2710 ARCHIVE_OTH_BALANCE
2711 (p_archive_assact_id => p_assignment_action_id,
2712 p_assignment_action_id => rec_archive_ids.run_assact_id,
2713 p_assignment_id => rec_archive_ids.run_assgt_id,
2714 p_payroll_action_id => l_archive_payact_id,
2715 p_record_count => l_record_count,
2716 p_pre_payact_id => rec_archive_ids.prepay_payact_id,
2717 p_effective_date => p_effective_date,
2718 p_date_earned => l_date_earned,
2719 p_archive_flag => 'Y');
2720 l_prev_prepay := rec_archive_ids.prepay_assact_id;
2721 END IF;
2722 /*fnd_file.put_line(fnd_file.log,'ARCHIVE_ACCRUAL_PLAN');*/
2723 /* ARCHIVE_ACCRUAL_PLAN (p_assignment_id => rec_archive_ids.run_assgt_id,
2724 p_date_earned => l_date_earned,
2725 p_effective_date => p_effective_date,
2726 p_archive_assact_id => p_assignment_action_id,
2727 p_run_assignment_action_id => rec_archive_ids.run_assact_id,
2728 p_period_end_date => l_end_date,
2729 p_period_start_date => l_start_date);*/
2730 --fnd_file.put_line(fnd_file.log,' vetr ARCHIVE_ADD_ELEMENT');
2731 ARCHIVE_ADD_ELEMENT
2732 (p_archive_assact_id => p_assignment_action_id,
2733 p_assignment_action_id => rec_archive_ids.run_assact_id,
2734 p_assignment_id => rec_archive_ids.run_assgt_id,
2735 p_payroll_action_id => l_archive_payact_id,
2736 p_date_earned => l_date_earned,
2737 p_effective_date => p_effective_date,
2738 p_pre_payact_id => rec_archive_ids.prepay_payact_id,
2739 p_archive_flag => 'Y');
2740 --fnd_file.put_line(fnd_file.log,'Assact id: '|| p_assignment_action_id);
2741
2742 ARCHIVE_MAIN_ELEMENTS (p_archive_assact_id => p_assignment_action_id,
2743 p_assignment_action_id => rec_archive_ids.run_assact_id,
2744 p_assignment_id => rec_archive_ids.run_assgt_id,
2745 p_date_earned => l_date_earned,
2746 p_effective_date => p_effective_date ) ;
2747
2748 l_record_count := l_record_count + 1;
2749 END LOOP;
2750 IF g_debug THEN
2751 hr_utility.set_location(' Leaving Procedure ARCHIVE_CODE',390);
2752 END IF;
2753 END ARCHIVE_CODE;
2754
2755 PROCEDURE ARCHIVE_MAIN_ELEMENTS
2756 (p_archive_assact_id IN NUMBER,
2757 p_assignment_action_id IN NUMBER,
2758 p_assignment_id IN NUMBER,
2759 p_date_earned IN DATE,
2760 p_effective_date IN DATE ) IS
2761
2762 -----------------------------------------------------------------------------
2763 /* Cursor to retrieve Earnings Element Information */
2764
2765 CURSOR csr_ear_element_info IS
2766 SELECT nvl(pettl.reporting_name,pettl.element_name) rep_name
2767 ,et.element_type_id element_type_id
2768 ,iv.input_value_id input_value_id
2769 ,iv.uom uom
2770 FROM pay_element_types_f et
2771 , pay_element_types_f_tl pettl
2772 , pay_input_values_f iv
2773 , pay_element_classifications classification
2774 WHERE et.element_type_id = iv.element_type_id
2775 AND et.element_type_id = pettl.element_type_id
2776 AND pettl.language = USERENV('LANG')
2777 AND iv.name = 'Pay Value'
2778 AND classification.classification_id = et.classification_id
2779 AND classification.classification_name IN
2780 ( 'Salary in Money'
2781 ,'Lumpsum'
2782 ,'Other Payments Subject to Tax'
2783 ,'Retrospective Payments'
2784 ,'Direct Payments'
2785 )
2786 AND p_date_earned BETWEEN et.effective_start_date
2787 AND et.effective_end_date
2788 AND p_date_earned BETWEEN iv.effective_start_date
2789 AND iv.effective_end_date
2790 AND ((et.business_group_id IS NULL AND et.legislation_code = 'SE')
2791 OR (et.business_group_id = g_business_group_id AND et.legislation_code IS NULL));
2792 /*
2793 SELECT nvl(pettl.reporting_name,pettl.element_name) rep_name
2794 ,et.element_type_id element_type_id
2795 ,iv.input_value_id input_value_id
2796 ,iv.uom uom
2797 ,code.org_information2 element_code
2798 FROM pay_element_types_f et
2799 , pay_element_types_f_tl pettl
2800 , pay_input_values_f iv
2801 , pay_element_classifications classification
2802 ,hr_organization_information code
2803 WHERE et.element_type_id = iv.element_type_id
2804 AND et.element_type_id = pettl.element_type_id
2805 AND pettl.language = USERENV('LANG')
2806 AND iv.name = 'Pay Value'
2807 AND classification.classification_id = et.classification_id
2808 AND classification.classification_name IN
2809 ('Absence'
2810 ,'Salary in Money'
2811 ,'Lumpsum'
2812 ,'Benefits in Kind'
2813 ,'Taxable Expenses'
2814 ,'Other Payments Subject to Tax'
2815 ,'Retrospective Payments'
2816 ,'Direct Payments'
2817 ,'Employer Charges'
2818 ,'External Expenses')
2819 AND p_date_earned BETWEEN et.effective_start_date
2820 AND et.effective_end_date
2821 AND p_date_earned BETWEEN iv.effective_start_date
2822 AND iv.effective_end_date
2823 AND ((et.business_group_id IS NULL AND et.legislation_code = 'SE')
2824 OR (et.business_group_id = g_business_group_id AND et.legislation_code IS NULL))
2825 and code.organization_id(+) = g_business_group_id
2826 and code.org_information_context (+)='SE_SOE_ELEMENT_ADD_DETAILS'
2827 and et.element_type_id = code.org_information1 (+);*/
2828
2829 ----------------------------------------------------------
2830 /* Cursor to retrieve Deduction Element Information */
2831
2832 CURSOR csr_ded_element_info IS
2833 SELECT nvl(pettl.reporting_name,pettl.element_name) rep_name
2834 ,et.element_type_id element_type_id
2835 ,iv.input_value_id input_value_id
2836 ,iv.uom uom
2837 FROM pay_element_types_f et
2838 , pay_element_types_f_tl pettl
2839 , pay_input_values_f iv
2840 , pay_element_classifications classification
2841 WHERE et.element_type_id = iv.element_type_id
2842 AND et.element_type_id = pettl.element_type_id
2843 AND pettl.language = USERENV('LANG')
2844 AND iv.name = 'Pay Value'
2845 AND classification.classification_id = et.classification_id
2846 AND classification.classification_name IN ('Involuntary Deductions'
2847 ,'Voluntary Deductions'
2848 ,'Statutory Deductions'
2849 ,'Pre-Tax Deductions')
2850 AND p_date_earned BETWEEN et.effective_start_date
2851 AND et.effective_end_date
2852 AND p_date_earned BETWEEN iv.effective_start_date
2853 AND iv.effective_end_date
2854 AND ((et.business_group_id IS NULL AND et.legislation_code = 'SE')
2855 OR (et.business_group_id = g_business_group_id AND et.legislation_code IS NULL));
2856 /*SELECT nvl(pettl.reporting_name,pettl.element_name) rep_name
2857 ,et.element_type_id element_type_id
2858 ,iv.input_value_id input_value_id
2859 ,iv.uom uom
2860 ,code.org_information2 element_code
2861 FROM pay_element_types_f et
2862 , pay_element_types_f_tl pettl
2863 , pay_input_values_f iv
2864 , pay_element_classifications classification
2865 ,hr_organization_information code
2866 WHERE et.element_type_id = iv.element_type_id
2867 AND et.element_type_id = pettl.element_type_id
2868 AND pettl.language = USERENV('LANG')
2869 AND iv.name = 'Pay Value'
2870 AND classification.classification_id = et.classification_id
2871 AND classification.classification_name IN ('Involuntary Deductions'
2872 ,'Voluntary Deductions'
2873 ,'Statutory Deductions')
2874 AND p_date_earned BETWEEN et.effective_start_date
2875 AND et.effective_end_date
2876 AND p_date_earned BETWEEN iv.effective_start_date
2877 AND iv.effective_end_date
2878 AND ((et.business_group_id IS NULL AND et.legislation_code = 'SE')
2879 OR (et.business_group_id = g_business_group_id AND et.legislation_code IS NULL))
2880 and code.organization_id(+) = g_business_group_id
2881 and code.org_information_context (+)='SE_SOE_ELEMENT_ADD_DETAILS'
2882 and et.element_type_id = code.org_information1 (+);*/
2883
2884
2885 -----------------------------------------------------------------------------
2886 /* Cursor to retrieve run result value of Main Elements */
2887 CURSOR csr_result_value(p_iv_id NUMBER
2888 ,p_ele_type_id NUMBER
2889 ,p_assignment_action_id NUMBER) IS
2890 SELECT rrv.result_value,rr.element_entry_id
2891 FROM pay_run_result_values rrv
2892 ,pay_run_results rr
2893 ,pay_assignment_actions paa
2894 ,pay_payroll_actions ppa
2895 WHERE rrv.input_value_id = p_iv_id
2896 AND rr.element_type_id = p_ele_type_id
2897 AND rr.run_result_id = rrv.run_result_id
2898 AND rr.assignment_action_id = paa.assignment_action_id
2899 AND paa.assignment_action_id = p_assignment_action_id
2900 AND ppa.payroll_action_id = paa.payroll_action_id
2901 AND ppa.action_type IN ('Q','R')
2902 AND rrv.result_value IS NOT NULL;
2903 -----------------------------------------------------------------------------
2904 /* Cursor to retrieve run result value of Main Elements */
2905 CURSOR csr_result_value_EE(p_iv_id NUMBER
2906 ,p_ele_type_id NUMBER
2907 ,p_assignment_action_id NUMBER
2908 ,p_EE_ID NUMBER) IS
2909 SELECT rrv.result_value
2910 FROM pay_run_result_values rrv
2911 ,pay_run_results rr
2912 ,pay_assignment_actions paa
2913 ,pay_payroll_actions ppa
2914 WHERE rrv.input_value_id = p_iv_id
2915 AND rr.element_type_id = p_ele_type_id
2916 AND rr.run_result_id = rrv.run_result_id
2917 AND rr.assignment_action_id = paa.assignment_action_id
2918 AND paa.assignment_action_id = p_assignment_action_id
2919 AND ppa.payroll_action_id = paa.payroll_action_id
2920 AND ppa.action_type IN ('Q','R')
2921 AND rrv.result_value IS NOT NULL
2922 AND rr.element_entry_id = p_EE_ID;
2923 -----------------------------------------------------------------------------
2924 -- Cursor to pick up segment from SE_SOE_ELEMENT_ADD_DETAILS context Org Developer DF
2925 CURSOR csr_group_by( p_ele_type_id NUMBER ) IS
2926 select ORG_INFORMATION3,ORG_INFORMATION6,ORG_INFORMATION7,ORG_INFORMATION8,ORG_INFORMATION9,ORG_INFORMATION10
2927 from hr_organization_information code
2928 where code.organization_id = g_business_group_id
2929 and code.org_information_context ='SE_SOE_ELEMENT_ADD_DETAILS'
2930 and code.org_information1 =p_ele_type_id;
2931 -----------------------------------------------------------------------------
2932 /* Cursor to retrieve sum of run result value for an given Main Element */
2933 -----------------------------------------------------------------------------
2934 CURSOR csr_sum_of_result_values(p_iv_id NUMBER
2935 ,p_ele_type_id NUMBER
2936 ,p_assignment_action_id NUMBER
2937 ) IS
2938 SELECT sum(rrv.result_value) result_value
2939 ,count(rrv.RUN_RESULT_ID) record_count
2940 ,rrv.result_value UNIT_PRICE
2941 FROM pay_run_result_values rrv
2942 ,pay_run_results rr
2943 ,pay_assignment_actions paa
2944 ,pay_payroll_actions ppa
2945 WHERE rrv.input_value_id = p_iv_id
2946 AND rr.element_type_id = p_ele_type_id
2947 AND rr.run_result_id = rrv.run_result_id
2948 AND rr.assignment_action_id = paa.assignment_action_id
2949 AND paa.assignment_action_id = p_assignment_action_id
2950 AND ppa.payroll_action_id = paa.payroll_action_id
2951 AND ppa.action_type IN ('Q','R')
2952 AND rrv.result_value IS NOT NULL
2953 group by rrv.result_value;
2954 /* CURSOR csr_sum_of_result_values(p_iv_id NUMBER
2955 ,p_ele_type_id NUMBER
2956 ,p_assignment_action_id NUMBER
2957 ,p_group_by NUMBER) IS
2958 SELECT sum(rrv.result_value) result_value
2959 ,count(rrv.RUN_RESULT_ID) record_count
2960 ,rrv.result_value UNIT_PRICE
2961 FROM pay_run_result_values pr
2962 ,pay_run_result_values rrv
2963 ,pay_run_results rr
2964 ,pay_assignment_actions paa
2965 ,pay_payroll_actions ppa
2966 WHERE pr.input_value_id(+) = p_group_by
2967 AND rrv.input_value_id = p_iv_id
2968 AND rr.element_type_id = p_ele_type_id
2969 AND rr.run_result_id = rrv.run_result_id
2970 AND rr.run_result_id = pr.run_result_id (+)
2971 AND rr.assignment_action_id = paa.assignment_action_id
2972 AND paa.assignment_action_id = p_assignment_action_id
2973 AND ppa.payroll_action_id = paa.payroll_action_id
2974 AND ppa.action_type IN ('Q','R')
2975 AND rrv.result_value IS NOT NULL
2976 --AND pr.result_value IS NOT NULL
2977 group by pr.result_value,rrv.result_value;*/
2978 /*CURSOR csr_sum_of_result_values(p_iv_id NUMBER
2979 ,p_ele_type_id NUMBER
2980 ,p_assignment_action_id NUMBER) IS
2981 SELECT sum(rrv.result_value) result_value
2982 FROM pay_run_result_values rrv
2983 ,pay_run_results rr
2984 ,pay_assignment_actions paa
2985 ,pay_payroll_actions ppa
2986 WHERE rrv.input_value_id = p_iv_id
2987 AND rr.element_type_id = p_ele_type_id
2988 AND rr.run_result_id = rrv.run_result_id
2989 AND rr.assignment_action_id = paa.assignment_action_id
2990 AND paa.assignment_action_id = p_assignment_action_id
2991 AND ppa.payroll_action_id = paa.payroll_action_id
2992 AND ppa.action_type IN ('Q','R')
2993 AND rrv.result_value IS NOT NULL
2994 group by rrv.result_value;
2995 */
2996
2997
2998 rec_group_by csr_group_by%ROWTYPE;
2999 l_result_value pay_run_result_values.result_value%TYPE := 0;
3000
3001 -----------------------------------------------------------------------------
3002 /* Cursor to retrieve sum of all run result value for an given Main Element */
3003 -----------------------------------------------------------------------------
3004 CURSOR csr_all_sum_result_values(p_iv_id_UNIT NUMBER,p_iv_id_AMOUNT NUMBER,p_iv_id_UNIT_PRICE NUMBER
3005 ,p_ele_type_id NUMBER
3006 ,p_assignment_action_id NUMBER
3007 ) IS
3008 SELECT rrv3.result_value UNIT_PRICE , sum(rrv1.result_value) UNIT, sum(rrv2.result_value) AMOUNT
3009 FROM pay_run_result_values rrv1
3010 ,pay_run_results rr1
3011 ,pay_assignment_actions paa
3012 ,pay_payroll_actions ppa
3013 ,pay_run_result_values rrv2
3014 ,pay_run_results rr2
3015 ,pay_run_result_values rrv3
3016 ,pay_run_results rr3
3017 WHERE rrv1.input_value_id = p_iv_id_UNIT
3018 AND rr1.element_type_id = p_ele_type_id
3019 AND rr1.run_result_id = rrv1.run_result_id
3020 AND rr1.assignment_action_id = paa.assignment_action_id
3021 AND paa.assignment_action_id = p_assignment_action_id
3022 AND ppa.payroll_action_id = paa.payroll_action_id
3023 AND ppa.action_type IN ('Q','R')
3024 and rrv2.input_value_id = p_iv_id_AMOUNT
3025 AND rr2.run_result_id = rrv2.run_result_id
3026 AND rr2.element_entry_id = rr1.element_entry_id
3027 AND rr2.assignment_action_id = paa.assignment_action_id
3028 and rrv3.input_value_id = p_iv_id_UNIT_PRICE
3029 AND rr3.run_result_id = rrv3.run_result_id
3030 AND rr3.element_entry_id = rr1.element_entry_id
3031 AND rr3.assignment_action_id = paa.assignment_action_id
3032 group by rrv3.result_value;
3033
3034 -----------------------------------------------------------------------------
3035 -----------------------------------------------------------------------------
3036 /* Cursor to retrieve sum of all run result value for an given Main Element */
3037 -----------------------------------------------------------------------------
3038 CURSOR csr_all_result_values(p_iv_id_UNIT NUMBER,p_iv_id_AMOUNT NUMBER,p_iv_id_UNIT_PRICE NUMBER
3039 ,p_ele_type_id NUMBER
3040 ,p_assignment_action_id NUMBER
3041 ) IS
3042 SELECT rrv3.result_value UNIT_PRICE , rrv1.result_value UNIT, rrv2.result_value AMOUNT
3043 FROM pay_run_result_values rrv1
3044 ,pay_run_results rr1
3045 ,pay_assignment_actions paa
3046 ,pay_payroll_actions ppa
3047 ,pay_run_result_values rrv2
3048 ,pay_run_results rr2
3049 ,pay_run_result_values rrv3
3050 ,pay_run_results rr3
3051 WHERE rrv1.input_value_id = p_iv_id_UNIT
3052 AND rr1.element_type_id = p_ele_type_id
3053 AND rr1.run_result_id = rrv1.run_result_id
3054 AND rr1.assignment_action_id = paa.assignment_action_id
3055 AND ppa.payroll_action_id = paa.payroll_action_id
3056 AND paa.assignment_action_id = p_assignment_action_id
3057 AND ppa.action_type IN ('Q','R')
3058 and rrv2.input_value_id = p_iv_id_AMOUNT
3059 AND rr2.run_result_id = rrv2.run_result_id
3060 AND rr2.element_entry_id = rr1.element_entry_id
3061 AND rr2.assignment_action_id = paa.assignment_action_id
3062 and rrv3.input_value_id = p_iv_id_UNIT_PRICE
3063 AND rr3.run_result_id = rrv3.run_result_id
3064 AND rr3.element_entry_id = rr1.element_entry_id
3065 AND rr3.assignment_action_id = paa.assignment_action_id;
3066
3067
3068 -----------------------------------------------------------------------------
3069
3070
3071 l_action_info_id NUMBER;
3072 l_ovn NUMBER;
3073 l_element_context VARCHAR2(10);
3074 l_index NUMBER := 0;
3075 l_formatted_value VARCHAR2(50) := NULL;
3076 l_flag NUMBER := 0;
3077 l_group_by number;
3078 l_unit_price NUMBER ;
3079 l_unit NUMBER ;
3080 l_amount NUMBER ;
3081 -----------------------------------------------------------------------------
3082
3083 BEGIN
3084
3085 IF g_debug THEN
3086 hr_utility.set_location(' Entering Procedure ARCHIVE_MAIN_ELEMENTS',320);
3087 END IF;
3088
3089 -- Archiving Earnings Elements
3090 FOR csr_rec IN csr_ear_element_info LOOP
3091
3092 l_result_value := NULL;
3093 l_group_by :=null;
3094 l_unit_price :=null;
3095
3096 BEGIN
3097 -- Conditions below are added to flush the Record Set After a Successful Query
3098 -- so that it starts afresh for the next element
3099 rec_group_by.ORG_INFORMATION6:= NULL;
3100 rec_group_by.ORG_INFORMATION3:= NULL;
3101
3102 OPEN csr_group_by(csr_rec.element_type_id );
3103 FETCH csr_group_by
3104 INTO rec_group_by;
3105 CLOSE csr_group_by;
3106 -- The se_soe contains
3107 -- segment 3 = > I or O
3108 -- segment 6 = > Y or N
3109 -- segment 7 = > Input ID UNIT
3110 -- segment 8 = > Input ID UNIT PRICE
3111 -- segment 9 = > Input ID Amount
3112
3113 IF ( (rec_group_by.ORG_INFORMATION3 = 'O') or (rec_group_by.ORG_INFORMATION3 IS NULL) )
3114 THEN
3115 -- Case for Group by or NOT
3116 -- Segemnt 6 is allowed here, as it makes sense.
3117 IF ( rec_group_by.ORG_INFORMATION6 = 'N' or (rec_group_by.ORG_INFORMATION3 IS NULL) )
3118 THEN
3119 -- This csae iis for individual representation of each element.
3120 -- unit and unit price should be absent.
3121 FOR csr_result_rec IN csr_result_value(csr_rec.input_value_id ,csr_rec.element_type_id ,p_assignment_action_id)
3122 LOOP
3123 IF csr_result_rec.result_value is not null THEN
3124 pay_action_information_api.create_action_information (
3125 p_action_information_id => l_action_info_id
3126 ,p_action_context_id => p_archive_assact_id
3127 ,p_action_context_type => 'AAP'
3128 ,p_object_version_number => l_ovn
3129 ,p_effective_date => p_effective_date
3130 ,p_source_id => NULL
3131 ,p_source_text => NULL
3132 ,p_action_information_category => 'EMEA ELEMENT INFO'
3133 ,p_action_information1 => csr_rec.element_type_id
3134 ,p_action_information2 => csr_rec.input_value_id
3135 ,p_action_information3 => 'E'
3136 ,p_action_information4 => fnd_number.number_to_canonical(csr_result_rec.result_value) --l_formatted_value
3137 ,p_action_information8 => ''
3138 ,p_action_information9 => 'Earning Element:'
3139 ,p_assignment_id => p_assignment_id);
3140
3141 --fnd_file.put_line(fnd_file.log,'group by NO l_action_info_id ' || l_action_info_id );
3142 --fnd_file.put_line(fnd_file.log,'Earning Element ');
3143 --fnd_file.put_line(fnd_file.log,'csr_rec.element_type_id ' || csr_rec.element_type_id );
3144 --fnd_file.put_line(fnd_file.log,'csr_rec.input_value_id ' || csr_rec.input_value_id );
3145 --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) );
3146 END IF;
3147 END LOOP;
3148
3149 ELSIF rec_group_by.ORG_INFORMATION6 = 'Y'
3150 THEN
3151 -- This csae iis for Grouping by pay value of each element.
3152 -- unit and unit price should be present
3153 FOR csr_result_rec IN csr_sum_of_result_values(csr_rec.input_value_id
3154 ,csr_rec.element_type_id
3155 ,p_assignment_action_id )
3156 LOOP
3157 IF csr_result_rec.result_value is not null THEN
3158 pay_action_information_api.create_action_information (
3159 p_action_information_id => l_action_info_id
3160 ,p_action_context_id => p_archive_assact_id
3161 ,p_action_context_type => 'AAP'
3162 ,p_object_version_number => l_ovn
3163 ,p_effective_date => p_effective_date
3164 ,p_source_id => NULL
3165 ,p_source_text => NULL
3166 ,p_action_information_category => 'EMEA ELEMENT INFO'
3167 ,p_action_information1 => csr_rec.element_type_id
3168 ,p_action_information2 => csr_rec.input_value_id
3169 ,p_action_information3 => 'E'
3170 ,p_action_information4 => fnd_number.number_to_canonical(csr_result_rec.result_value) --l_formatted_value
3171 ,p_action_information8 => csr_result_rec.record_count
3172 ,p_action_information9 => 'Earning Element unit per price:'||csr_result_rec.UNIT_PRICE
3173 ,p_assignment_id => p_assignment_id);
3174
3175 --fnd_file.put_line(fnd_file.log,'group by NO l_action_info_id ' || l_action_info_id );
3176 --fnd_file.put_line(fnd_file.log,'Earning Element ');
3177 --fnd_file.put_line(fnd_file.log,'csr_rec.element_type_id ' || csr_rec.element_type_id );
3178 --fnd_file.put_line(fnd_file.log,'csr_rec.input_value_id ' || csr_rec.input_value_id );
3179 --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) );
3180 END IF;
3181 END LOOP;
3182 END IF;
3183 ELSIF rec_group_by.ORG_INFORMATION3 = 'I'
3184 THEN
3185 -- Case for UNIT,PRICE,AMOUNT
3186 -- Segment 7,8,9 is allowed
3187 -- Segemnt 6 is not allowed here, as it doesn't makes sense.
3188 -- segment 7 = > Input ID UNIT
3189 -- segment 8 = > Input ID UNIT PRICE
3190 -- segment 9 = > Input ID Amount
3191 IF rec_group_by.ORG_INFORMATION7 IS NOT NULL AND
3192 rec_group_by.ORG_INFORMATION8 IS NOT NULL AND
3193 rec_group_by.ORG_INFORMATION9 IS NOT NULL
3194 THEN
3195 -- All three are selected, we can group by three in single query
3196
3197
3198 IF rec_group_by.ORG_INFORMATION10 = 'Y'
3199 THEN
3200 FOR csr_result_rec IN csr_all_sum_result_values(rec_group_by.ORG_INFORMATION7
3201 ,rec_group_by.ORG_INFORMATION9
3202 ,rec_group_by.ORG_INFORMATION8
3203 ,csr_rec.element_type_id
3204 ,p_assignment_action_id )
3205 LOOP
3206 IF csr_result_rec.AMOUNT is not null THEN
3207 pay_action_information_api.create_action_information (
3208 p_action_information_id => l_action_info_id
3209 ,p_action_context_id => p_archive_assact_id
3210 ,p_action_context_type => 'AAP'
3211 ,p_object_version_number => l_ovn
3212 ,p_effective_date => p_effective_date
3213 ,p_source_id => NULL
3214 ,p_source_text => NULL
3215 ,p_action_information_category => 'EMEA ELEMENT INFO'
3216 ,p_action_information1 => csr_rec.element_type_id
3217 ,p_action_information2 => csr_rec.input_value_id
3218 ,p_action_information3 => 'E'
3219 ,p_action_information4 => fnd_number.number_to_canonical(csr_result_rec.AMOUNT) --l_formatted_value
3220 ,p_action_information8 => csr_result_rec.UNIT
3221 ,p_action_information9 => 'Earning Element unit per price:'||csr_result_rec.UNIT_PRICE
3222 ,p_assignment_id => p_assignment_id);
3223
3224 --fnd_file.put_line(fnd_file.log,'group by NO l_action_info_id ' || l_action_info_id );
3225 --fnd_file.put_line(fnd_file.log,'Earning Element ');
3226 --fnd_file.put_line(fnd_file.log,'csr_result_rec.UNIT ' || csr_result_rec.UNIT );
3227 --fnd_file.put_line(fnd_file.log,'csr_result_rec.WP WP ' || csr_result_rec.UNIT_PRICE );
3228 --fnd_file.put_line(fnd_file.log,'fnd_number.number_to_canonical(l_result_value) ' || fnd_number.number_to_canonical(csr_result_rec.AMOUNT) );
3229 END IF;
3230 END LOOP;
3231 ELSE
3232 FOR csr_result_rec IN csr_all_result_values(rec_group_by.ORG_INFORMATION7
3233 ,rec_group_by.ORG_INFORMATION9
3234 ,rec_group_by.ORG_INFORMATION8
3235 ,csr_rec.element_type_id
3236 ,p_assignment_action_id )
3237 LOOP
3238 IF csr_result_rec.AMOUNT is not null THEN
3239 pay_action_information_api.create_action_information (
3240 p_action_information_id => l_action_info_id
3241 ,p_action_context_id => p_archive_assact_id
3242 ,p_action_context_type => 'AAP'
3243 ,p_object_version_number => l_ovn
3244 ,p_effective_date => p_effective_date
3245 ,p_source_id => NULL
3246 ,p_source_text => NULL
3247 ,p_action_information_category => 'EMEA ELEMENT INFO'
3248 ,p_action_information1 => csr_rec.element_type_id
3249 ,p_action_information2 => csr_rec.input_value_id
3250 ,p_action_information3 => 'E'
3251 ,p_action_information4 => fnd_number.number_to_canonical(csr_result_rec.AMOUNT) --l_formatted_value
3252 ,p_action_information8 => csr_result_rec.UNIT
3253 ,p_action_information9 => 'Earning Element unit per price:'||csr_result_rec.UNIT_PRICE
3254 ,p_assignment_id => p_assignment_id);
3255
3256 --fnd_file.put_line(fnd_file.log,'group by NO l_action_info_id ' || l_action_info_id );
3257 --fnd_file.put_line(fnd_file.log,'Earning Element ');
3258 --fnd_file.put_line(fnd_file.log,'csr_result_rec.UNIT ' || csr_result_rec.UNIT );
3259 --fnd_file.put_line(fnd_file.log,'csr_result_rec.WP WP ' || csr_result_rec.UNIT_PRICE );
3260 --fnd_file.put_line(fnd_file.log,'fnd_number.number_to_canonical(l_result_value) ' || fnd_number.number_to_canonical(csr_result_rec.AMOUNT) );
3261 END IF; -- end of csr_result_rec.AMOUNT is not null
3262 END LOOP;
3263
3264 END IF; -- end of rec_group_by.ORG_INFORMATION10 = 'Y'
3265 ELSE -- Three inputs are not selected.
3266 -- have to get the each input value id and find value for each
3267 -- and archive it if the amount is not null
3268
3269 -- Case for UNIT,PRICE,AMOUNT
3270 -- Segment 7,8,9 is allowed
3271 -- Segemnt 6 is not allowed here, as it doesn't makes sense.
3272 -- segment 7 = > Input ID UNIT
3273 -- segment 8 = > Input ID UNIT PRICE
3274 -- segment 9 = > Input ID Amount
3275 IF rec_group_by.ORG_INFORMATION9 IS NOT NULL
3276 THEN
3277 -- amount should not be null
3278 -- find the amount value and element entry id of this element
3279 -- for this element entry find the the other selected columns like UNIT , UNIT PRICE if selected
3280 --
3281 FOR csr_result_rec IN csr_result_value(rec_group_by.ORG_INFORMATION9 ,csr_rec.element_type_id ,p_assignment_action_id)
3282 LOOP
3283
3284 -- we have EE id
3285 l_amount := csr_result_rec.result_value;
3286 IF rec_group_by.ORG_INFORMATION8 IS NOT NULL
3287 THEN
3288 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 );
3289 FETCH csr_result_value_EE
3290 INTO l_unit_price;
3291 CLOSE csr_result_value_EE;
3292 ELSE
3293 l_unit_price :=NULL;
3294 END IF; -- End if of segment 8 , unit price
3295
3296 IF rec_group_by.ORG_INFORMATION7 IS NOT NULL
3297 THEN
3298 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 );
3299 FETCH csr_result_value_EE
3300 INTO l_unit;
3301 CLOSE csr_result_value_EE;
3302 ELSE
3303 l_unit :=NULL;
3304 END IF; -- End if of segment 7 , unit
3305
3306 -- Resume again
3307 IF csr_result_rec.result_value is not null THEN
3308 pay_action_information_api.create_action_information (
3309 p_action_information_id => l_action_info_id
3310 ,p_action_context_id => p_archive_assact_id
3311 ,p_action_context_type => 'AAP'
3312 ,p_object_version_number => l_ovn
3313 ,p_effective_date => p_effective_date
3314 ,p_source_id => NULL
3315 ,p_source_text => NULL
3316 ,p_action_information_category => 'EMEA ELEMENT INFO'
3317 ,p_action_information1 => csr_rec.element_type_id
3318 ,p_action_information2 => csr_rec.input_value_id
3319 ,p_action_information3 => 'E'
3320 ,p_action_information4 => fnd_number.number_to_canonical(l_amount) --l_formatted_value
3321 ,p_action_information8 => l_unit
3322 ,p_action_information9 => 'Earning Element unit per price:'||l_uNIT_PRICE
3323 ,p_assignment_id => p_assignment_id);
3324
3325 --fnd_file.put_line(fnd_file.log,'group by NO l_action_info_id ' || l_action_info_id );
3326 --fnd_file.put_line(fnd_file.log,'Earning Element ');
3327 --fnd_file.put_line(fnd_file.log,'csr_rec.element_type_id ' || csr_rec.element_type_id );
3328 --fnd_file.put_line(fnd_file.log,'csr_rec.input_value_id ' || csr_rec.input_value_id );
3329 --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) );
3330 END IF; -- end of csr_result_rec.result_value is not null
3331 END LOOP;
3332 END IF; -- end of rec_group_by.ORG_INFORMATION9 IS NOT NULL
3333
3334 END IF;
3335
3336 END IF;
3337 /*
3338 FOR csr_result_rec IN csr_sum_of_result_values(csr_rec.input_value_id
3339 ,csr_rec.element_type_id
3340 ,p_assignment_action_id
3341 ,rec_group_by.ORG_INFORMATION3)
3342 LOOP
3343 IF csr_result_rec.result_value is not null THEN
3344 pay_action_information_api.create_action_information (
3345 p_action_information_id => l_action_info_id
3346 ,p_action_context_id => p_archive_assact_id
3347 ,p_action_context_type => 'AAP'
3348 ,p_object_version_number => l_ovn
3349 ,p_effective_date => p_effective_date
3350 ,p_source_id => NULL
3351 ,p_source_text => NULL
3352 ,p_action_information_category => 'EMEA ELEMENT INFO'
3353 ,p_action_information1 => csr_rec.element_type_id
3354 ,p_action_information2 => csr_rec.input_value_id
3355 ,p_action_information3 => 'E'
3356 ,p_action_information4 => fnd_number.number_to_canonical(csr_result_rec.result_value) --l_formatted_value
3357 ,p_action_information8 => csr_result_rec.record_count
3358 ,p_action_information9 => 'Earning Element unit per price:'||csr_result_rec.UNIT_PRICE
3359 ,p_assignment_id => p_assignment_id);
3360
3361 --fnd_file.put_line(fnd_file.log,'group by NO l_action_info_id ' || l_action_info_id );
3362 --fnd_file.put_line(fnd_file.log,'Earning Element ');
3363 --fnd_file.put_line(fnd_file.log,'csr_rec.element_type_id ' || csr_rec.element_type_id );
3364 --fnd_file.put_line(fnd_file.log,'csr_rec.input_value_id ' || csr_rec.input_value_id );
3365 --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) );
3366 END IF;
3367 END LOOP;
3368 */
3369 --OPEN csr_result_value(csr_rec.input_value_id ,csr_rec.element_type_id ,p_assignment_action_id);
3370 --FETCH csr_result_value INTO l_result_value;
3371 --CLOSE csr_result_value;
3372 /*OPEN csr_group_by(csr_rec.element_type_id );
3373 FETCH csr_group_by INTO l_group_by;
3374 CLOSE csr_group_by;
3375 IF l_group_by !='Y'
3376 THEN
3377 FOR csr_result_rec IN csr_result_value(csr_rec.input_value_id ,csr_rec.element_type_id ,p_assignment_action_id)
3378 LOOP
3379 IF csr_result_rec.result_value is not null THEN
3380 pay_action_information_api.create_action_information (
3381 p_action_information_id => l_action_info_id
3382 ,p_action_context_id => p_archive_assact_id
3383 ,p_action_context_type => 'AAP'
3384 ,p_object_version_number => l_ovn
3385 ,p_effective_date => p_effective_date
3386 ,p_source_id => NULL
3387 ,p_source_text => NULL
3388 ,p_action_information_category => 'EMEA ELEMENT INFO'
3389 ,p_action_information1 => csr_rec.element_type_id
3390 ,p_action_information2 => csr_rec.input_value_id
3391 ,p_action_information3 => 'E'
3392 ,p_action_information4 => fnd_number.number_to_canonical(csr_result_rec.result_value) --l_formatted_value
3393 --,p_action_information5 => csr_rec.element_code
3394 ,p_action_information9 => 'Earning Element'
3395 ,p_assignment_id => p_assignment_id);
3396
3397 --fnd_file.put_line(fnd_file.log,'group by NO l_action_info_id ' || l_action_info_id );
3398 --fnd_file.put_line(fnd_file.log,'Earning Element ');
3399 --fnd_file.put_line(fnd_file.log,'csr_rec.element_type_id ' || csr_rec.element_type_id );
3400 --fnd_file.put_line(fnd_file.log,'csr_rec.input_value_id ' || csr_rec.input_value_id );
3401 --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) );
3402 END IF;
3403 END LOOP;
3404 --l_group_by :=NULL;
3405 --ELSE
3406
3407 OPEN csr_sum_of_result_values(csr_rec.input_value_id ,csr_rec.element_type_id ,p_assignment_action_id);
3408 FETCH csr_sum_of_result_values INTO l_result_value;
3409 CLOSE csr_sum_of_result_values;
3410
3411
3412 IF l_result_value is not null THEN
3413 pay_action_information_api.create_action_information (
3414 p_action_information_id => l_action_info_id
3415 ,p_action_context_id => p_archive_assact_id
3416 ,p_action_context_type => 'AAP'
3417 ,p_object_version_number => l_ovn
3418 ,p_effective_date => p_effective_date
3419 ,p_source_id => NULL
3420 ,p_source_text => NULL
3421 ,p_action_information_category => 'EMEA ELEMENT INFO'
3422 ,p_action_information1 => csr_rec.element_type_id
3423 ,p_action_information2 => csr_rec.input_value_id
3424 ,p_action_information3 => 'E'
3425 ,p_action_information4 => fnd_number.number_to_canonical(l_result_value) --l_formatted_value
3426 --,p_action_information5 => csr_rec.element_code
3427 ,p_action_information9 => 'Earning Element'
3428 ,p_assignment_id => p_assignment_id);
3429
3430 --fnd_file.put_line(fnd_file.log,'GROUP BY YES l_action_info_id ' || l_action_info_id );
3431 --fnd_file.put_line(fnd_file.log,'Earning Element ');
3432 --fnd_file.put_line(fnd_file.log,'csr_rec.element_type_id ' || csr_rec.element_type_id );
3433 --fnd_file.put_line(fnd_file.log,'csr_rec.input_value_id ' || csr_rec.input_value_id );
3434 --fnd_file.put_line(fnd_file.log,'fnd_number.number_to_canonical(l_result_value) ' || fnd_number.number_to_canonical(l_result_value) );
3435 END IF;
3436 l_group_by :=NULL;
3437 END IF; -- end if for the l_group_by*/
3438
3439 EXCEPTION WHEN OTHERS THEN
3440 g_err_num := SQLCODE;
3441 /*fnd_file.put_line(fnd_file.log,'ORA_ERR: ' || g_err_num || 'In ARCHIVE_MAIN_ELEMENTS');*/
3442
3443 IF g_debug THEN
3444 hr_utility.set_location('ORA_ERR: ' || g_err_num || 'In ARCHIVE_MAIN_ELEMENTS',330);
3445 END IF;
3446 END;
3447 END LOOP;
3448
3449
3450
3451 -- Archiving Deduction Elements
3452
3453 FOR csr_rec IN csr_ded_element_info LOOP
3454
3455 l_result_value := NULL;
3456 rec_group_by := NULL;
3457
3458 BEGIN
3459 -- Conditions below are added to flush the Record Set After a Successful Query
3460 -- so that it starts afresh for the next element
3461 rec_group_by.ORG_INFORMATION6:= NULL;
3462 rec_group_by.ORG_INFORMATION3:= NULL;
3463
3464 OPEN csr_group_by(csr_rec.element_type_id );
3465 FETCH csr_group_by
3466 INTO rec_group_by;
3467 CLOSE csr_group_by;
3468 -- The se_soe contains
3469 -- segment 3 = > I or O
3470 -- segment 6 = > Y or N
3471 -- segment 7 = > Input ID UNIT
3472 -- segment 8 = > Input ID UNIT PRICE
3473 -- segment 9 = > Input ID Amount
3474 --fnd_file.put_line(fnd_file.log,'Vetri1 : ' || 'In ARCHIVE_MAIN_ELEMENTS'||rec_group_by.ORG_INFORMATION3);
3475
3476 IF ( (rec_group_by.ORG_INFORMATION3 = 'O') or (rec_group_by.ORG_INFORMATION3 IS NULL) )
3477 THEN
3478 -- Case for Group by or NOT
3479 -- Segemnt 6 is allowed here, as it makes sense.
3480 --fnd_file.put_line(fnd_file.log,'Vetri 2: ' || 'In ARCHIVE_MAIN_ELEMENTS'||rec_group_by.ORG_INFORMATION3);
3481 IF ( rec_group_by.ORG_INFORMATION6 = 'N' or (rec_group_by.ORG_INFORMATION3 IS NULL) )
3482 THEN
3483 -- This csae iis for individual representation of each element.
3484 -- unit and unit price should be absent.
3485 --fnd_file.put_line(fnd_file.log,'Vetri 3: ' || 'In ARCHIVE_MAIN_ELEMENTS'||rec_group_by.ORG_INFORMATION3);
3486 FOR csr_result_rec IN csr_result_value(csr_rec.input_value_id ,csr_rec.element_type_id ,p_assignment_action_id)
3487 LOOP
3488 IF csr_result_rec.result_value is not null THEN
3489 pay_action_information_api.create_action_information (
3490 p_action_information_id => l_action_info_id
3491 ,p_action_context_id => p_archive_assact_id
3492 ,p_action_context_type => 'AAP'
3493 ,p_object_version_number => l_ovn
3494 ,p_effective_date => p_effective_date
3495 ,p_source_id => NULL
3496 ,p_source_text => NULL
3497 ,p_action_information_category => 'EMEA ELEMENT INFO'
3498 ,p_action_information1 => csr_rec.element_type_id
3499 ,p_action_information2 => csr_rec.input_value_id
3500 ,p_action_information3 => 'D'
3501 ,p_action_information4 => fnd_number.number_to_canonical(csr_result_rec.result_value) --l_formatted_value
3502 ,p_action_information8 => ''
3503 ,p_action_information9 => 'Deduction Element:'
3504 ,p_assignment_id => p_assignment_id);
3505
3506 --fnd_file.put_line(fnd_file.log,'group by NO l_action_info_id ' || l_action_info_id );
3507 --fnd_file.put_line(fnd_file.log,'Earning Element ');
3508 --fnd_file.put_line(fnd_file.log,'csr_rec.element_type_id ' || csr_rec.element_type_id );
3509 --fnd_file.put_line(fnd_file.log,'csr_rec.input_value_id ' || csr_rec.input_value_id );
3510 --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) );
3511 END IF;
3512 END LOOP;
3513
3514 ELSIF rec_group_by.ORG_INFORMATION6 = 'Y'
3515 THEN
3516 -- This csae iis for Grouping by pay value of each element.
3517 -- unit and unit price should be present
3518 FOR csr_result_rec IN csr_sum_of_result_values(csr_rec.input_value_id
3519 ,csr_rec.element_type_id
3520 ,p_assignment_action_id )
3521 LOOP
3522 IF csr_result_rec.result_value is not null THEN
3523 pay_action_information_api.create_action_information (
3524 p_action_information_id => l_action_info_id
3525 ,p_action_context_id => p_archive_assact_id
3526 ,p_action_context_type => 'AAP'
3527 ,p_object_version_number => l_ovn
3528 ,p_effective_date => p_effective_date
3529 ,p_source_id => NULL
3530 ,p_source_text => NULL
3531 ,p_action_information_category => 'EMEA ELEMENT INFO'
3532 ,p_action_information1 => csr_rec.element_type_id
3533 ,p_action_information2 => csr_rec.input_value_id
3534 ,p_action_information3 => 'D'
3535 ,p_action_information4 => fnd_number.number_to_canonical(csr_result_rec.result_value) --l_formatted_value
3536 ,p_action_information8 => csr_result_rec.record_count
3537 ,p_action_information9 => 'Deduction Element unit per price:'||csr_result_rec.UNIT_PRICE
3538 ,p_assignment_id => p_assignment_id);
3539
3540 --fnd_file.put_line(fnd_file.log,'group by NO l_action_info_id ' || l_action_info_id );
3541 --fnd_file.put_line(fnd_file.log,'Earning Element ');
3542 --fnd_file.put_line(fnd_file.log,'csr_rec.element_type_id ' || csr_rec.element_type_id );
3543 --fnd_file.put_line(fnd_file.log,'csr_rec.input_value_id ' || csr_rec.input_value_id );
3544 --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) );
3545 END IF;
3546 END LOOP;
3547 END IF;
3548 ELSIF rec_group_by.ORG_INFORMATION3 = 'I'
3549 THEN
3550 -- Case for UNIT,PRICE,AMOUNT
3551 -- Segment 7,8,9 is allowed
3552 -- Segemnt 6 is not allowed here, as it doesn't makes sense.
3553 -- segment 7 = > Input ID UNIT
3554 -- segment 8 = > Input ID UNIT PRICE
3555 -- segment 9 = > Input ID Amount
3556 IF rec_group_by.ORG_INFORMATION7 IS NOT NULL AND
3557 rec_group_by.ORG_INFORMATION8 IS NOT NULL AND
3558 rec_group_by.ORG_INFORMATION9 IS NOT NULL
3559 THEN
3560 -- All three are selected, we can group by three in single query
3561 IF rec_group_by.ORG_INFORMATION10 = 'Y'
3562 THEN
3563 FOR csr_result_rec IN csr_all_sum_result_values(rec_group_by.ORG_INFORMATION7
3564 ,rec_group_by.ORG_INFORMATION9
3565 ,rec_group_by.ORG_INFORMATION8
3566 ,csr_rec.element_type_id
3567 ,p_assignment_action_id )
3568 LOOP
3569 IF csr_result_rec.AMOUNT is not null THEN
3570 pay_action_information_api.create_action_information (
3571 p_action_information_id => l_action_info_id
3572 ,p_action_context_id => p_archive_assact_id
3573 ,p_action_context_type => 'AAP'
3574 ,p_object_version_number => l_ovn
3575 ,p_effective_date => p_effective_date
3576 ,p_source_id => NULL
3577 ,p_source_text => NULL
3578 ,p_action_information_category => 'EMEA ELEMENT INFO'
3579 ,p_action_information1 => csr_rec.element_type_id
3580 ,p_action_information2 => csr_rec.input_value_id
3581 ,p_action_information3 => 'D'
3582 ,p_action_information4 => fnd_number.number_to_canonical(csr_result_rec.AMOUNT) --l_formatted_value
3583 ,p_action_information8 => csr_result_rec.UNIT
3584 ,p_action_information9 => 'Deduction Element unit per price:'||csr_result_rec.UNIT_PRICE
3585 ,p_assignment_id => p_assignment_id);
3586
3587 --fnd_file.put_line(fnd_file.log,'group by NO l_action_info_id ' || l_action_info_id );
3588 --fnd_file.put_line(fnd_file.log,'Earning Element ');
3589 --fnd_file.put_line(fnd_file.log,'csr_result_rec.UNIT ' || csr_result_rec.UNIT );
3590 --fnd_file.put_line(fnd_file.log,'csr_result_rec.WP WP ' || csr_result_rec.UNIT_PRICE );
3591 --fnd_file.put_line(fnd_file.log,'fnd_number.number_to_canonical(l_result_value) ' || fnd_number.number_to_canonical(csr_result_rec.AMOUNT) );
3592 END IF;
3593 END LOOP;
3594 ELSE
3595 FOR csr_result_rec IN csr_all_result_values(rec_group_by.ORG_INFORMATION7
3596 ,rec_group_by.ORG_INFORMATION9
3597 ,rec_group_by.ORG_INFORMATION8
3598 ,csr_rec.element_type_id
3599 ,p_assignment_action_id )
3600 LOOP
3601 IF csr_result_rec.AMOUNT is not null THEN
3602 pay_action_information_api.create_action_information (
3603 p_action_information_id => l_action_info_id
3604 ,p_action_context_id => p_archive_assact_id
3605 ,p_action_context_type => 'AAP'
3606 ,p_object_version_number => l_ovn
3607 ,p_effective_date => p_effective_date
3608 ,p_source_id => NULL
3609 ,p_source_text => NULL
3610 ,p_action_information_category => 'EMEA ELEMENT INFO'
3611 ,p_action_information1 => csr_rec.element_type_id
3612 ,p_action_information2 => csr_rec.input_value_id
3613 ,p_action_information3 => 'D'
3614 ,p_action_information4 => fnd_number.number_to_canonical(csr_result_rec.AMOUNT) --l_formatted_value
3615 ,p_action_information8 => csr_result_rec.UNIT
3616 ,p_action_information9 => 'Deduction Element unit per price:'||csr_result_rec.UNIT_PRICE
3617 ,p_assignment_id => p_assignment_id);
3618
3619 --fnd_file.put_line(fnd_file.log,'group by NO l_action_info_id ' || l_action_info_id );
3620 --fnd_file.put_line(fnd_file.log,'Earning Element ');
3621 --fnd_file.put_line(fnd_file.log,'csr_result_rec.UNIT ' || csr_result_rec.UNIT );
3622 --fnd_file.put_line(fnd_file.log,'csr_result_rec.WP WP ' || csr_result_rec.UNIT_PRICE );
3623 --fnd_file.put_line(fnd_file.log,'fnd_number.number_to_canonical(l_result_value) ' || fnd_number.number_to_canonical(csr_result_rec.AMOUNT) );
3624 END IF;
3625 END LOOP;
3626
3627 END IF;-- end of rec_group_by.ORG_INFORMATION10 = 'Y'
3628 ELSE -- Three inputs are not selected.
3629 -- have to get the each input value id and find value for each
3630 -- and archive it if the amount is not null
3631 -- Case for UNIT,PRICE,AMOUNT
3632 -- Segment 7,8,9 is allowed
3633 -- Segemnt 6 is not allowed here, as it doesn't makes sense.
3634 -- segment 7 = > Input ID UNIT
3635 -- segment 8 = > Input ID UNIT PRICE
3636 -- segment 9 = > Input ID Amount
3637 IF rec_group_by.ORG_INFORMATION9 IS NOT NULL
3638 THEN
3639 -- amount should not be null
3640 -- find the amount value and element entry id of this element
3641 -- for this element entry find the the other selected columns like UNIT , UNIT PRICE if selected
3642 --
3643 FOR csr_result_rec IN csr_result_value(rec_group_by.ORG_INFORMATION9 ,csr_rec.element_type_id ,p_assignment_action_id)
3644 LOOP
3645
3646 -- we have EE id
3647 l_amount := csr_result_rec.result_value;
3648 IF rec_group_by.ORG_INFORMATION8 IS NOT NULL
3649 THEN
3650 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 );
3651 FETCH csr_result_value_EE
3652 INTO l_unit_price;
3653 CLOSE csr_result_value_EE;
3654 ELSE
3655 l_unit_price :=NULL;
3656 END IF; -- End if of segment 8 , unit price
3657
3658 IF rec_group_by.ORG_INFORMATION7 IS NOT NULL
3659 THEN
3660 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 );
3661 FETCH csr_result_value_EE
3662 INTO l_unit;
3663 CLOSE csr_result_value_EE;
3664 ELSE
3665 l_unit :=NULL;
3666 END IF; -- End if of segment 7 , unit
3667
3668 -- Resume again
3669 IF csr_result_rec.result_value is not null THEN
3670 pay_action_information_api.create_action_information (
3671 p_action_information_id => l_action_info_id
3672 ,p_action_context_id => p_archive_assact_id
3673 ,p_action_context_type => 'AAP'
3674 ,p_object_version_number => l_ovn
3675 ,p_effective_date => p_effective_date
3676 ,p_source_id => NULL
3677 ,p_source_text => NULL
3678 ,p_action_information_category => 'EMEA ELEMENT INFO'
3679 ,p_action_information1 => csr_rec.element_type_id
3680 ,p_action_information2 => csr_rec.input_value_id
3681 ,p_action_information3 => 'D'
3682 ,p_action_information4 => fnd_number.number_to_canonical(l_amount) --l_formatted_value
3683 ,p_action_information8 => l_unit
3684 ,p_action_information9 => 'Deduction Element unit per price:'||l_uNIT_PRICE
3685 ,p_assignment_id => p_assignment_id);
3686
3687 --fnd_file.put_line(fnd_file.log,'group by NO l_action_info_id ' || l_action_info_id );
3688 --fnd_file.put_line(fnd_file.log,'Earning Element ');
3689 --fnd_file.put_line(fnd_file.log,'csr_rec.element_type_id ' || csr_rec.element_type_id );
3690 --fnd_file.put_line(fnd_file.log,'csr_rec.input_value_id ' || csr_rec.input_value_id );
3691 --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) );
3692 END IF; -- end of csr_result_rec.result_value is not null
3693 END LOOP;
3694 END IF; -- end of rec_group_by.ORG_INFORMATION9 IS NOT NULL
3695 END IF;
3696 END IF;
3697 /*
3698 OPEN csr_group_by(csr_rec.element_type_id );
3699 FETCH csr_group_by
3700 INTO rec_group_by;
3701 CLOSE csr_group_by;
3702 FOR csr_result_rec IN csr_sum_of_result_values(csr_rec.input_value_id
3703 ,csr_rec.element_type_id
3704 ,p_assignment_action_id
3705 )
3706 LOOP
3707 IF csr_result_rec.result_value is not null THEN
3708 pay_action_information_api.create_action_information (
3709 p_action_information_id => l_action_info_id
3710 ,p_action_context_id => p_archive_assact_id
3711 ,p_action_context_type => 'AAP'
3712 ,p_object_version_number => l_ovn
3713 ,p_effective_date => p_effective_date
3714 ,p_source_id => NULL
3715 ,p_source_text => NULL
3716 ,p_action_information_category => 'EMEA ELEMENT INFO'
3717 ,p_action_information1 => csr_rec.element_type_id
3718 ,p_action_information2 => csr_rec.input_value_id
3719 ,p_action_information3 => 'D'
3720 ,p_action_information4 => fnd_number.number_to_canonical(csr_result_rec.result_value) --l_formatted_value
3721 ,p_action_information8 => csr_result_rec.record_count
3722 ,p_action_information9 => 'Deduction Element unit per price:'||csr_result_rec.UNIT_PRICE
3723 ,p_assignment_id => p_assignment_id);
3724 END IF;
3725 END LOOP;
3726 */
3727
3728 /* --OPEN csr_result_value(csr_rec.input_value_id ,csr_rec.element_type_id ,p_assignment_action_id);
3729 --FETCH csr_result_value INTO l_result_value;
3730 --CLOSE csr_result_value;
3731 FOR csr_result_rec IN csr_result_value(csr_rec.input_value_id ,csr_rec.element_type_id ,p_assignment_action_id)
3732 LOOP
3733
3734 IF csr_result_rec.result_value is not null THEN
3735
3736 pay_action_information_api.create_action_information (
3737 p_action_information_id => l_action_info_id
3738 ,p_action_context_id => p_archive_assact_id
3739 ,p_action_context_type => 'AAP'
3740 ,p_object_version_number => l_ovn
3741 ,p_effective_date => p_effective_date
3742 ,p_source_id => NULL
3743 ,p_source_text => NULL
3744 ,p_action_information_category => 'EMEA ELEMENT INFO'
3745 ,p_action_information1 => csr_rec.element_type_id
3746 ,p_action_information2 => csr_rec.input_value_id
3747 ,p_action_information3 => 'D'
3748 ,p_action_information4 => fnd_number.number_to_canonical(csr_result_rec.result_value) --l_formatted_value
3749 --,p_action_information5 => csr_rec.element_code
3750 ,p_action_information9 => 'Deduction Element'
3751 ,p_assignment_id => p_assignment_id);
3752
3753 END IF;
3754 END LOOP;*/
3755 EXCEPTION WHEN OTHERS THEN
3756 g_err_num := SQLCODE;
3757 /*fnd_file.put_line(fnd_file.log,'ORA_ERR: ' || g_err_num || 'In ARCHIVE_MAIN_ELEMENTS');*/
3758
3759 IF g_debug THEN
3760 hr_utility.set_location('ORA_ERR: ' || g_err_num || 'In ARCHIVE_MAIN_ELEMENTS',330);
3761 END IF;
3762 END;
3763 END LOOP;
3764
3765
3766 IF g_debug THEN
3767 hr_utility.set_location(' Leaving Procedure ARCHIVE_MAIN_ELEMENTS',340);
3768 END IF;
3769
3770 END ARCHIVE_MAIN_ELEMENTS;
3771
3772 PROCEDURE DEINITIALIZATION_CODE
3773 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type) is
3774
3775 CURSOR csr_scl_details (p_payroll_action_id pay_action_information.action_information1%TYPE , p_effective_date DATE ) IS
3776 SELECT DISTINCT segment2 local_unit , paaf.business_group_id
3777 FROM per_all_assignments_f paaf
3778 ,HR_SOFT_CODING_KEYFLEX hsck
3779 WHERE p_effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
3780 AND paaf.SOFT_CODING_KEYFLEX_ID = hsck.SOFT_CODING_KEYFLEX_ID
3781 AND paaf.assignment_id IN
3782 (SELECT DISTINCT assignment_id
3783 FROM pay_assignment_actions
3784 WHERE payroll_action_id= p_payroll_action_id );
3785
3786
3787 CURSOR csr_legal_emp(p_organization_id NUMBER , p_business_group_id NUMBER ) IS
3788 SELECT hoi3.organization_id
3789 FROM HR_ORGANIZATION_UNITS o1
3790 , HR_ORGANIZATION_INFORMATION hoi1
3791 , HR_ORGANIZATION_INFORMATION hoi2
3792 , HR_ORGANIZATION_INFORMATION hoi3
3793 WHERE o1.business_group_id =p_business_group_id
3794 AND hoi1.organization_id = o1.organization_id
3795 AND hoi1.organization_id = p_organization_id
3796 AND hoi1.org_information1 = 'SE_LOCAL_UNIT'
3797 AND hoi1.org_information_context = 'CLASS'
3798 AND o1.organization_id = hoi2.org_information1
3799 AND hoi2.ORG_INFORMATION_CONTEXT='SE_LOCAL_UNITS'
3800 AND hoi2.organization_id = hoi3.organization_id
3801 AND hoi3.ORG_INFORMATION_CONTEXT='CLASS'
3802 AND hoi3.org_information1 = 'HR_LEGAL_EMPLOYER';
3803
3804 CURSOR csr_employer_address(p_organization_id NUMBER) IS
3805 SELECT hla.style style
3806 ,hla.country country
3807 ,hla.address_line_1 AL1
3808 ,hla.address_line_2 AL2
3809 ,hla.address_line_3 AL3
3810 ,hla.postal_code postal_code
3811 FROM hr_locations_all hla
3812 ,hr_organization_units hou
3813 WHERE hou.organization_id = p_organization_id
3814 AND hou.location_id = hla.location_id;
3815
3816 CURSOR csr_effective_date (p_payroll_action_id pay_action_information.action_information1%TYPE ) IS
3817 SELECT effective_date
3818 FROM pay_payroll_actions
3819 WHERE payroll_action_id= p_payroll_action_id ;
3820
3821
3822 l_org_exists NUMBER ;
3823 l_action_info_id NUMBER;
3824 l_ovn NUMBER;
3825 l_effective_date DATE ;
3826 l_emp_postal_code VARCHAR2(80);
3827 l_emp_country VARCHAR2(30);
3828
3829
3830 BEGIN
3831 IF g_debug THEN
3832 hr_utility.set_location(' Entering Procedure DEINITIALIZATION_CODE',380);
3833 END IF;
3834
3835 OPEN csr_effective_date(p_payroll_action_id);
3836 FETCH csr_effective_date INTO l_effective_date ;
3837 CLOSE csr_effective_date;
3838
3839
3840 FOR csr_scl_details_rec IN csr_scl_details(p_payroll_action_id , l_effective_date)
3841 LOOP
3842
3843 FOR csr_legal_emp_rec IN csr_legal_emp(csr_scl_details_rec.local_unit , csr_scl_details_rec.business_group_id)
3844 LOOP
3845
3846 /* INSERT EMPLOYER'S ADDRESS (ORGANIZATION ADDRESS)*/
3847 BEGIN
3848
3849 l_org_exists := 0;
3850 SELECT 1
3851 INTO l_org_exists
3852 FROM pay_action_information
3853 WHERE action_context_id = p_payroll_action_id
3854 AND action_information1 = csr_legal_emp_rec.organization_id
3855 AND effective_date = l_effective_date
3856 AND action_information_category = 'ADDRESS DETAILS';
3857
3858 EXCEPTION
3859 WHEN NO_DATA_FOUND THEN
3860
3861
3862 FOR rec_employer_address IN csr_employer_address(csr_legal_emp_rec.organization_id)
3863 LOOP
3864
3865 IF rec_employer_address.style = 'FI' THEN
3866 l_emp_postal_code := hr_general.decode_lookup('SE_POSTAL_CODE',rec_employer_address.postal_code);
3867 ELSE
3868 l_emp_postal_code := rec_employer_address.postal_code;
3869 END IF;
3870 -- Bug#8849455 fix Added space between 3 and 4 digits in postal code
3871 l_emp_postal_code := substr(l_emp_postal_code,1,3)||' '||substr(l_emp_postal_code,4,2);
3872 l_emp_country:=PAY_SE_PAYSLIP_ARCHIVE.get_country_name(rec_employer_address.country);
3873
3874 pay_action_information_api.create_action_information (
3875 p_action_information_id => l_action_info_id
3876 ,p_action_context_id => p_payroll_action_id
3877 ,p_action_context_type => 'PA'
3878 ,p_object_version_number => l_ovn
3879 ,p_effective_date => l_effective_date
3880 ,p_source_id => NULL
3881 ,p_source_text => NULL
3882 ,p_action_information_category => 'ADDRESS DETAILS'
3883 ,p_action_information1 => csr_legal_emp_rec.organization_id
3884 ,p_action_information5 => rec_employer_address.AL1
3885 ,p_action_information6 => rec_employer_address.AL2
3886 ,p_action_information7 => rec_employer_address.AL3
3887 ,p_action_information12 => l_emp_postal_code
3888 ,p_action_information13 => l_emp_country
3889 ,p_action_information14 => 'Employer Address');
3890
3891 --fnd_file.put_line(fnd_file.log,'l_action_info_id ' || l_action_info_id );
3892 --fnd_file.put_line(fnd_file.log,'ADDRESS DETAILS ');
3893 --fnd_file.put_line(fnd_file.log,'csr_legal_emp_rec.organization_id ' || csr_legal_emp_rec.organization_id );
3894 --fnd_file.put_line(fnd_file.log,'rec_employer_address.AL1 ' || rec_employer_address.AL1 );
3895 --fnd_file.put_line(fnd_file.log,'rec_employer_address.AL2 ' || rec_employer_address.AL2 );
3896 --fnd_file.put_line(fnd_file.log,'rec_employer_address.AL3 ' || rec_employer_address.AL3 );
3897 --fnd_file.put_line(fnd_file.log,'l_emp_postal_code ' || l_emp_postal_code );
3898 --fnd_file.put_line(fnd_file.log,'l_emp_country ' || l_emp_country );
3899
3900 END LOOP;
3901
3902 WHEN OTHERS THEN
3903 NULL;
3904 END;
3905
3906 END LOOP;
3907
3908
3909
3910
3911 END LOOP;
3912
3913 /*fnd_file.put_line(fnd_file.log,'Entering Procedure ARCHIVE_EMPLOYEE_DETAILS 10');*/
3914 IF g_debug THEN
3915 hr_utility.set_location(' Leaving Procedure DEINITIALIZATION_CODE',390);
3916 END IF;
3917
3918 EXCEPTION
3919 WHEN others THEN
3920 IF g_debug THEN
3921 hr_utility.set_location('error raised in DEINITIALIZATION_CODE ',5);
3922 END if;
3923 RAISE;
3924 END;
3925
3926 END PAY_SE_PAYSLIP_ARCHIVE;