[Home] [Help]
PACKAGE BODY: APPS.PAY_IN_24Q_ARCHIVE
Source
1 PACKAGE BODY pay_in_24q_archive AS
2 /* $Header: pyin24qa.pkb 120.18.12010000.2 2008/08/06 07:25:44 ubhat ship $ */
3
4 TYPE t_person_data_rec IS RECORD
5 ( person_id per_all_people_f.person_id%TYPE
6 ,pan_number per_all_people_f.per_information14%TYPE
7 ,pan_ref_number per_all_people_f.per_information14%TYPE
8 ,full_name per_all_people_f.full_name%TYPE
9 ,tax_rate per_assignment_extra_info.aei_information2 %TYPE
10 ,position per_all_positions.name%TYPE);
11
12 TYPE t_person_data_tab_type IS TABLE OF t_person_data_rec
13 INDEX BY binary_integer;
14
15 --------------------------------------------------------------------------
16 -- --
17 -- Name : GET_PARAMETERS --
18 -- Type : PROCEDURE --
19 -- Access : Private --
20 -- Description : This procedure determines the globals applicable --
21 -- through out the tenure of the process --
22 -- Parameters : --
23 -- IN : p_payroll_action_id NUMBER --
24 -- p_token_name VARCHAR2 --
25 -- OUT : p_token_value VARCHAR2 --
26 -- --
27 -- Change History : --
28 --------------------------------------------------------------------------
29 -- Rev# Date Userid Description --
30 --------------------------------------------------------------------------
31 -- 115.0 5-Jan-2006 lnagaraj Initial Version --
32 --------------------------------------------------------------------------
33
34 PROCEDURE get_parameters(p_payroll_action_id IN NUMBER,
35 p_token_name IN VARCHAR2,
36 p_token_value OUT NOCOPY VARCHAR2)
37 IS
38
39 CURSOR csr_parameter_info(p_pact_id NUMBER,
40 p_token CHAR) IS
41 SELECT SUBSTR(legislative_parameters,
42 INSTR(legislative_parameters,p_token)+(LENGTH(p_token)+1),
43 INSTR(legislative_parameters,' ',
44 INSTR(legislative_parameters,p_token))
45 - (INSTR(legislative_parameters,p_token)+LENGTH(p_token)))
46 ,business_group_id
47 FROM pay_payroll_actions
48 WHERE payroll_action_id = p_pact_id;
49
50 l_token_value VARCHAR2(150);
51 l_bg_id NUMBER;
52 l_proc VARCHAR2(100);
53 l_message VARCHAR2(255);
54
55 BEGIN
56 g_debug := hr_utility.debug_enabled;
57 l_proc := g_package||'get_parameters';
58
59 pay_in_utils.set_location(g_debug,'Entering : '||l_proc,10);
60
61 if g_debug then
62 pay_in_utils.trace('******************************','********************');
63 pay_in_utils.trace('p_payroll_action_id : ',p_payroll_action_id);
64 pay_in_utils.trace('p_token_name : ',p_token_name);
65 pay_in_utils.trace('******************************','********************');
66 end if;
67
68 OPEN csr_parameter_info(p_payroll_action_id,
69 p_token_name);
70 FETCH csr_parameter_info INTO l_token_value,l_bg_id;
71 CLOSE csr_parameter_info;
72
73 if g_debug then
74 pay_in_utils.trace('l_token_value : ',l_token_value);
75 pay_in_utils.trace('l_bg_id : ',l_bg_id);
76 end if;
77
78 p_token_value := TRIM(l_token_value);
79
80 if g_debug then
81 pay_in_utils.trace('p_token_value before : ',p_token_value);
82 end if;
83
84 IF (p_token_name = 'BG_ID') THEN
85 p_token_value := l_bg_id;
86 END IF;
87
88 IF (p_token_value IS NULL) THEN
89 p_token_value := '%';
90 END IF;
91
92 if g_debug then
93 pay_in_utils.trace('p_token_value after : ',p_token_value);
94 end if;
95
96 pay_in_utils.set_location(g_debug,'Leaving : '||l_proc,20);
97
98 END get_parameters;
99
100 --------------------------------------------------------------------------
101 -- --
102 -- Name : INITIALIZATION_CODE --
103 -- Type : PROCEDURE --
104 -- Access : Public --
105 -- Description : This procedure is used to set global contexts. --
106 -- Store 1.Challan Element type id --
107 -- 2.Challan input value id in a PL/SQL table --
108 -- 3.legislative parameters --
109 -- Parameters : --
110 -- IN : p_payroll_action_id NUMBER --
111 -- OUT : N/A --
112 -- --
113 -- Change History : --
114 --------------------------------------------------------------------------
115 -- Rev# Date Userid Description --
116 --------------------------------------------------------------------------
117 -- 115.0 05-Jan-2006 lnagaraj Initial Version --
118 --------------------------------------------------------------------------
119 --
120 PROCEDURE initialization_code (p_payroll_action_id IN NUMBER)
121 IS
122 --
123 l_proc VARCHAR2(100) ;
124 l_message VARCHAR2(255);
125 l_assess_yr_start DATE;
126 l_end_date DATE;
127 i NUMBER;
128 l_arch_ref_no_check NUMBER;
129 E_NON_UNIQUE_ARCH_REF_NO EXCEPTION;
130
131 CURSOR csr_challan_input_id
132 IS
133 SELECT pet.element_type_id element_type_id
134 ,piv.input_value_id input_value_id
135 ,piv.display_sequence indx
136 FROM pay_element_types_f pet
137 ,pay_input_values_f piv
138 WHERE pet.element_name ='Income Tax Challan Information'
139 AND pet.legislation_code='IN'
140 AND pet.element_type_id = piv.element_type_id
141 AND piv.name in('Challan or Voucher Number',
142 'Payment Date',
143 'Taxable Income',
144 'Income Tax Deducted',
145 'Surcharge Deducted',
146 'Education Cess Deducted',
147 'Amount Deposited')
148 AND g_session_date BETWEEN pet.effective_start_date AND pet.effective_end_date
149 AND g_session_date BETWEEN piv.effective_start_date AND piv.effective_end_date;
150 --
151 CURSOR csr_arch_ref_no(p_payroll_action_id NUMBER
152 ,p_bg_id NUMBER)
153 IS
154 SELECT 1
155 FROM pay_action_information pai
156 ,pay_payroll_actions ppa
157 ,hr_organization_units hou
158 WHERE pai.action_information_category = 'IN_24Q_ORG'
159 AND pai.action_context_type = 'PA'
160 AND pai.action_information1 like g_gre_id
161 AND pai.action_information3 = g_year||g_quarter
162 AND pai.action_information30 = g_archive_ref_no
163 AND pai.action_context_id = ppa.payroll_action_id
164 AND ppa.action_type = 'X'
165 AND ppa.action_status = 'C'
166 AND ppa.payroll_action_id <> p_payroll_action_id
167 AND hou.organization_id = pai.action_information1
168 AND hou.business_group_id = p_bg_id;
169
170 l_token_name pay_in_utils.char_tab_type;
171 l_token_value pay_in_utils.char_tab_type;
172
173 BEGIN
174 --
175
176 g_debug := hr_utility.debug_enabled;
177 l_proc := g_package || 'initialization_code';
178
179 pay_in_utils.set_location(g_debug,'Entering : '||l_proc,10);
180
181 if g_debug then
182 pay_in_utils.trace('******************************','********************');
183 pay_in_utils.trace('p_payroll_action_id : ',p_payroll_action_id);
184 pay_in_utils.trace('******************************','********************');
185 end if;
186
187 get_parameters(p_payroll_action_id,'YR',g_year);
188 get_parameters(p_payroll_action_id,'GRE',g_gre_id);
189 get_parameters(p_payroll_action_id,'QR',g_quarter);
190 get_parameters(p_payroll_action_id,'RN',g_archive_ref_no);
191 get_parameters(p_payroll_action_id,'BG_ID',g_bg_id);
192
193 if g_debug then
194 pay_in_utils.trace('g_year : ',g_year);
195 pay_in_utils.trace('g_gre_id : ',g_gre_id);
196 pay_in_utils.trace('g_quarter : ',g_quarter);
197 pay_in_utils.trace('g_bg_id : ',g_bg_id);
198 end if;
199
200 l_arch_ref_no_check := 0;
201 OPEN csr_arch_ref_no(p_payroll_action_id
202 ,g_bg_id);
203 FETCH csr_arch_ref_no INTO l_arch_ref_no_check;
204 CLOSE csr_arch_ref_no;
205 IF l_arch_ref_no_check = 1 THEN
206 l_token_name(1) := 'NUMBER_CATEGORY';
207 l_token_value(1) := hr_general.decode_lookup('IN_MESSAGE_TOKENS','ARCH_REF_NUM');--'Archive Reference Number';
208 RAISE E_NON_UNIQUE_ARCH_REF_NO;
209 END IF;
210
211 SELECT TRUNC(effective_date)
212 INTO g_session_date
213 FROM fnd_sessions
214 WHERE session_id = USERENV('sessionid');
215
216 if g_debug then
217 pay_in_utils.trace('g_session_date : ',g_session_date);
218 end if;
219
220 i := TO_NUMBER(SUBSTR(g_quarter,2,1)) - 1;
221 l_assess_yr_start := fnd_date.string_to_date(('01/04/'|| SUBSTR(g_year,1,4)),'DD/MM/YYYY');
222 g_tax_year := TO_CHAR((TO_NUMBER(SUBSTR(g_year,1,4)) - 1)||'-'||SUBSTR(g_year,1,4));
223 l_end_date := fnd_date.string_to_date(('31/03/'|| SUBSTR(g_year,6)),'DD/MM/YYYY');
224
225 if g_debug then
226 pay_in_utils.trace('i : ',i);
227 pay_in_utils.trace('l_assess_yr_start : ',l_assess_yr_start);
228 pay_in_utils.trace('g_tax_year : ',g_tax_year);
229 pay_in_utils.trace('l_end_date : ',l_end_date);
230 end if;
231
232 g_fin_start_date := ADD_MONTHS(l_assess_yr_start,-12);
233 g_fin_end_date := ADD_MONTHS(l_end_date,-12);
234 g_qr_start_date := ADD_MONTHS(l_assess_yr_start,(i*3)-12);
235 g_end_date := ADD_MONTHS(g_qr_start_date,3) -1;
236 g_payroll_action_id := p_payroll_action_id;
237
238 if g_debug then
239 pay_in_utils.trace('g_fin_start_date : ',g_fin_start_date);
240 pay_in_utils.trace('g_fin_end_date : ',g_fin_end_date);
241 pay_in_utils.trace('g_qr_start_date : ',g_qr_start_date);
242 pay_in_utils.trace('g_end_date : ',g_end_date);
243 pay_in_utils.trace('g_payroll_action_id : ',g_payroll_action_id);
244 end if;
245
246 pay_in_utils.set_location(g_debug,'Finding Globals : '||l_proc,20);
247
248 IF g_quarter ='Q4' THEN
249 g_start_date := ADD_MONTHS(l_assess_yr_start,-12);
250 ELSE
251 g_start_date := g_qr_start_date;
252 END IF;
253
254 if g_debug then
255 pay_in_utils.trace('g_start_date : ',g_start_date);
256 end if;
257
258 FOR crec in csr_challan_input_id LOOP
259 g_input_table_rec(crec.indx).input_value_id := crec.input_value_id;
260 g_chln_element_id := crec.element_type_id;
261 END LOOP;
262
263 pay_in_utils.set_location(g_debug,'Global1: '||g_year||' '||g_bg_id||' '||g_quarter||' '||g_gre_id,30);
264 pay_in_utils.set_location(g_debug,'Global2: '||g_start_date||g_end_date||g_qr_start_date,40);
265 pay_in_utils.set_location(g_debug,'Leaving : '||l_proc,50);
266
267 --
268 EXCEPTION
269 WHEN E_NON_UNIQUE_ARCH_REF_NO THEN
270 pay_in_utils.raise_message(800, 'PER_IN_NON_UNIQUE_VALUE', l_token_name, l_token_value);
271 fnd_file.put_line(fnd_file.log,'Archive Reference Number '|| g_archive_ref_no || 'is non-unique.');
272 pay_in_utils.set_location(g_debug,'Leaving : '||l_proc,60);
273 RAISE;
274 WHEN OTHERS THEN
275 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_proc, 'SQLERRMC:'||sqlerrm);
276 pay_in_utils.set_location(g_debug,'Leaving : '||l_proc,70);
277 pay_in_utils.trace(l_message,l_proc);
278 RAISE;
279 END initialization_code;
280
281 --------------------------------------------------------------------------
282 -- --
283 -- Name : RANGE_CODE --
284 -- Type : PROCEDURE --
285 -- Access : Public --
286 -- Description : This procedure returns a sql string to select a --
287 -- range of assignments eligible for archival. --
288 -- --
289 -- Parameters : --
290 -- IN : p_payroll_action_id NUMBER --
291 -- OUT : p_sql VARCHAR2 --
292 -- --
293 -- Change History : --
294 --------------------------------------------------------------------------
295 -- Rev# Date Userid Description --
296 --------------------------------------------------------------------------
297 -- 115.0 05-Jan-2006 lnagaraj Initial Version --
298 --------------------------------------------------------------------------
299 --
300
301 PROCEDURE range_code(p_payroll_action_id IN NUMBER
302 ,p_sql OUT NOCOPY VARCHAR2)
303 IS
304 --
305 l_proc VARCHAR2(100);
306 l_message VARCHAR2(255);
307 --
308 BEGIN
309 --
310
311 g_debug := hr_utility.debug_enabled;
312 l_proc := g_package || 'range_code';
313
314 hr_utility.set_location('Entering : '||l_proc,10);
315
319 pay_core_payslip_utils.range_cursor(p_payroll_action_id
316 -- Call core package to return SQL string to SELECT a range
317 -- of assignments eligible for archival
318 --
320 ,p_sql);
321
322 pay_in_utils.set_location(g_debug,'Leaving : '||l_proc,20);
323 --
324 EXCEPTION
325 WHEN OTHERS THEN
326 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_proc, 'SQLERRMC:'||sqlerrm);
327 pay_in_utils.set_location(g_debug,'Leaving : '||l_proc,30);
328 pay_in_utils.trace(l_message,l_proc);
329 RAISE;
330 --
331 END range_code;
332
333
334 --------------------------------------------------------------------------
335 -- --
336 -- Name : ASSIGNMENT_ACTION_CODE --
337 -- Type : PROCEDURE --
338 -- Access : Public --
339 -- Description : This procedure further restricts the assignment_id's--
340 -- returned by range_code. --
341 -- It selects assignments that have prepayments/balance--
342 -- initialization in the specified duration OR those --
343 -- that have Challan information entries --
344 -- of challans in the specified quarter --
345 -- Parameters : --
346 -- IN : p_payroll_action_id NUMBER --
347 -- p_start_person NUMBER --
348 -- p_end_person NUMBER --
349 -- p_chunk NUMBER --
350 -- OUT : N/A --
351 -- --
352 -- Change History : --
353 --------------------------------------------------------------------------
354 -- Rev# Date Userid Description --
355 --------------------------------------------------------------------------
356 -- 115.0 05-Jan-2006 lnagaraj Initial Version --
357 --------------------------------------------------------------------------
358 --
359 PROCEDURE assignment_action_code(p_payroll_action_id IN NUMBER
360 ,p_start_person IN NUMBER
361 ,p_end_person IN NUMBER
362 ,p_chunk IN NUMBER
363 )
364 IS
365 CURSOR c_process_assignments
366 IS
367 SELECT paf.assignment_id assignment_id
368 FROM per_assignments_f paf
369 ,pay_payroll_actions ppa
370 ,pay_assignment_actions paa
371 WHERE paf.business_group_id = g_bg_id
372 AND paf.person_id BETWEEN p_start_person AND p_end_person
373 AND p_payroll_action_id IS NOT NULL
374 AND paa.tax_unit_id LIKE g_gre_id
375 AND paa.assignment_id =paf.assignment_id
376 AND ppa.action_type IN('P','U','I')
377 AND paa.payroll_action_id = ppa.payroll_action_id
378 AND ppa.action_status = 'C'
379 AND ppa.effective_date BETWEEN g_start_date and g_end_date
380 AND paf.effective_start_date <= g_end_date
381 AND paf.effective_end_date >= g_start_date
382 AND ppa.business_group_id =g_bg_id
383 UNION
384 SELECT paf1.assignment_id
385 FROM pay_element_entries_f pee
386 ,per_assignments_f paf1
387 WHERE paf1.business_group_id = g_bg_id
388 AND paf1.person_id BETWEEN p_start_person AND p_end_person
389 AND pee.element_type_id = g_chln_element_id
390 AND p_payroll_action_id IS NOT NULL
391 AND paf1.effective_start_date <= g_fin_end_date
392 AND paf1.effective_end_date >= g_fin_start_date
393 AND pee.effective_start_date <= g_fin_end_date
394 AND pee.effective_end_date >= g_fin_start_date
395 AND pee.assignment_id = paf1.assignment_id
396 AND EXISTS (SELECT ''
397 FROM pay_element_entry_values_f peev
398 ,hr_organization_information hoi
399 WHERE peev.input_value_id = g_input_table_rec(1).input_value_id
400 AND peev.element_entry_id = pee.element_entry_id
401 AND peev.screen_entry_value = hoi.org_information3
402 AND hoi.org_information1 = g_tax_year
403 AND hoi.org_information13 = g_quarter
404 AND hoi.org_information_context ='PER_IN_IT_CHALLAN_INFO'
405 AND peev.effective_start_date <= g_fin_end_date
406 AND peev.effective_end_date >= g_fin_start_date
407 AND hoi.organization_id LIKE g_gre_id
408 AND ROWNUM =1);
409
410 l_proc VARCHAR2(100);
411 l_message VARCHAR2(255);
412 l_action_id NUMBER;
413 --
414 BEGIN
415 --
416
417 g_debug := hr_utility.debug_enabled;
418 l_proc := g_package || 'assignment_action_code';
419
423 pay_in_utils.trace('******************************','********************');
420 pay_in_utils.set_location(g_debug,'Entering : '||l_proc,10);
421
422 if g_debug then
424 pay_in_utils.trace('p_payroll_action_id : ',p_payroll_action_id);
425 pay_in_utils.trace('p_start_person : ',p_start_person);
426 pay_in_utils.trace('p_end_person : ',p_end_person);
427 pay_in_utils.trace('p_chunk : ',p_chunk);
428 pay_in_utils.trace('******************************','********************');
429 end if;
430
431 if g_debug then
432 pay_in_utils.trace('g_fin_start_date : ',g_fin_start_date);
433 end if;
434
435 -- need to initialise the global contexts again
436 IF g_fin_start_date IS NULL THEN
437 initialization_code (p_payroll_action_id);
438 END IF;
439
440 FOR csr_rec IN c_process_assignments
441 LOOP
442 SELECT pay_assignment_actions_s.NEXTVAL
443 INTO l_action_id
444 FROM dual;
445
446 if g_debug then
447 pay_in_utils.trace('l_action_id : ',l_action_id);
448 pay_in_utils.trace('csr_rec.assignment_id : ',csr_rec.assignment_id);
449 end if;
450
451 hr_nonrun_asact.insact(lockingactid => l_action_id
452 ,assignid => csr_rec.assignment_id
453 ,pactid => p_payroll_action_id
454 ,chunk => p_chunk
455 );
456
457 END LOOP;
458 pay_in_utils.set_location(g_debug,'Leaving : '||l_proc,30);
459 --
460 EXCEPTION
461 WHEN OTHERS THEN
462 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_proc, 'SQLERRMC:'||sqlerrm);
463 pay_in_utils.set_location(g_debug,'Leaving : '||l_proc,40);
464 pay_in_utils.trace(l_message,l_proc);
465 RAISE;
466 END assignment_action_code;
467
468 --------------------------------------------------------------------------
469 -- --
470 -- Name : ARCHIVE_CHALLAN_DATA --
471 -- Type : PROCEDURE --
472 -- Access : Private --
473 -- Description : This procedure archives organization level challan --
474 -- data belonging to a GRE in a quarter at PA level --
475 -- --
476 -- Parameters : --
477 -- IN : p_arc_pay_action_id NUMBER --
478 -- p_gre_id NUMBER --
479 -- OUT : N/A --
480 -- --
481 -- Change History : --
482 --------------------------------------------------------------------------
483 -- Rev# Date Userid Description --
484 --------------------------------------------------------------------------
485 -- 115.0 05-Jan-2006 lnagaraj Initial Version --
486 --------------------------------------------------------------------------
487 PROCEDURE archive_challan_data(p_arc_pay_action_id IN NUMBER
488 ,p_gre_id IN NUMBER
489 )
490 IS
491 CURSOR csr_challans_in_guarter
492 IS
493 SELECT hoi_challan.org_information2 Payment_date
494 ,hoi_challan.org_information5 Bank
495 ,hoi_challan.org_information3 challan_number
496 ,hoi_challan.org_information4 tax
497 ,hoi_challan.org_information6 remarks
498 ,hoi_challan.org_information7 surcharge
499 ,hoi_challan.org_information8 cess
500 ,hoi_challan.org_information9 interest
501 ,hoi_challan.org_information10 others
502 ,hoi_challan.org_information11 dd_cheq_num
503 ,hoi_challan.org_information12 book_entry
504 ,hoi_challan.org_information_id org_info_id
505 FROM hr_organization_information hoi_challan
506 WHERE hoi_challan.organization_id = p_gre_id
507 AND hoi_challan.org_information_context ='PER_IN_IT_CHALLAN_INFO'
508 AND hoi_challan.org_information1 = g_tax_year
509 AND hoi_challan.org_information13 = g_quarter
510 AND fnd_date.canonical_to_date(hoi_challan.org_information2) <= fnd_date.CHARDATE_TO_DATE(SYSDATE)
511 ORDER BY fnd_date.canonical_to_date(hoi_challan.org_information2);
512
513 CURSOR csr_challan_bank(p_bank_code VARCHAR2)
514 IS
515 SELECT hoi_bank.org_information4 Bank
516 FROM hr_organization_information hoi_bank
517 WHERE hoi_bank.organization_id = p_gre_id
518 AND hoi_bank.org_information_context = 'PER_IN_CHALLAN_BANK'
519 AND hoi_bank.org_information_id = p_bank_code;
520
521 TYPE t_challan_entry_asg_rec IS RECORD
522 (Payment_date hr_organization_information.org_information2%TYPE,
523 Bank hr_organization_information.org_information4%TYPE,
524 challan_number pay_element_entry_values_f.screen_entry_value%TYPE,
525 tax hr_organization_information.org_information4%TYPE,
526 remarks hr_organization_information.org_information4%TYPE,
527 surcharge hr_organization_information.org_information4%TYPE,
531 dd_cheq_num hr_organization_information.org_information4%TYPE,
528 cess hr_organization_information.org_information4%TYPE,
529 interest hr_organization_information.org_information4%TYPE,
530 others hr_organization_information.org_information4%TYPE,
532 book_entry hr_organization_information.org_information4%TYPE,
533 org_info_id hr_organization_information.org_information_id%TYPE
534 );
535 --
536
537 TYPE t_challan_entry_asg_tab_type IS TABLE OF t_challan_entry_asg_rec
538 INDEX BY binary_integer;
539
540 t_challan_entry_asg_tab t_challan_entry_asg_tab_type;
541
542 l_action_info_id NUMBER;
543 l_ovn NUMBER;
544 p_cnt NUMBER;
545 l_bank_code hr_organization_information.org_information4%TYPE;
546 l_proc VARCHAR2(100);
547 l_message VARCHAR2(255);
548 BEGIN
549
550 g_debug := hr_utility.debug_enabled;
551 l_proc := g_package || 'archive_challan_data';
552
553 pay_in_utils.set_location(g_debug,'Entering : '||l_proc,10);
554
555 if g_debug then
556 pay_in_utils.trace('******************************','********************');
557 pay_in_utils.trace('p_arc_pay_action_id : ',p_arc_pay_action_id);
558 pay_in_utils.trace('p_gre_id : ',p_gre_id);
559 pay_in_utils.trace('******************************','********************');
560 end if;
561
562 t_challan_entry_asg_tab.DELETE;
563
564 OPEN csr_challans_in_guarter ;
565 FETCH csr_challans_in_guarter BULK COLLECT INTO t_challan_entry_asg_tab;
566 CLOSE csr_challans_in_guarter;
567
568 p_cnt := t_challan_entry_asg_tab.COUNT;
569
570 if g_debug then
571 pay_in_utils.trace('p_cnt : ',p_cnt);
572 end if;
573
574 IF p_cnt >0 then
575 FOR i IN t_challan_entry_asg_tab.FIRST .. t_challan_entry_asg_tab.LAST LOOP
576 l_bank_code := NULL;
577 IF t_challan_entry_asg_tab.EXISTS(i) THEN
578 IF t_challan_entry_asg_tab(i).Bank IS NOT NULL THEN
579 OPEN csr_challan_bank(t_challan_entry_asg_tab(i).Bank);
580 FETCH csr_challan_bank INTO l_bank_code ;
581 CLOSE csr_challan_bank;
582 END IF;
583
584 if g_debug then
585 pay_in_utils.trace('challan_number : ',t_challan_entry_asg_tab(i).challan_number);
586 pay_in_utils.trace('g_year : ',g_year);
587 pay_in_utils.trace('g_quarter : ',g_quarter);
588 pay_in_utils.trace('l_bank_code : ',l_bank_code);
589 pay_in_utils.trace('Payment_date : ',t_challan_entry_asg_tab(i).Payment_date);
590 pay_in_utils.trace('tax : ',t_challan_entry_asg_tab(i).tax);
591 pay_in_utils.trace('surcharge : ',t_challan_entry_asg_tab(i).surcharge);
592 pay_in_utils.trace('interest : ',t_challan_entry_asg_tab(i).interest);
593 pay_in_utils.trace('others : ',t_challan_entry_asg_tab(i).others);
594 pay_in_utils.trace('dd_cheq_num : ',t_challan_entry_asg_tab(i).dd_cheq_num);
595 pay_in_utils.trace('book_entry : ',t_challan_entry_asg_tab(i).book_entry);
596 pay_in_utils.trace('remarks : ',t_challan_entry_asg_tab(i).remarks);
597 end if;
598
599 IF (NVL(t_challan_entry_asg_tab(i).tax,0)<>0 OR
600 NVL(t_challan_entry_asg_tab(i).surcharge,0)<>0 OR
601 NVL(t_challan_entry_asg_tab(i).cess,0)<>0 OR
602 NVL(t_challan_entry_asg_tab(i).interest,0)<>0 OR
603 NVL(t_challan_entry_asg_tab(i).others,0)<>0 ) THEN
604 pay_action_information_api.create_action_information
605 (p_action_context_id => p_arc_pay_action_id
606 ,p_action_context_type => 'PA'
607 ,p_action_information_category => 'IN_24Q_CHALLAN'
608 ,p_source_id => t_challan_entry_asg_tab(i).org_info_id
609 ,p_action_information1 => t_challan_entry_asg_tab(i).challan_number
610 ,p_action_information2 => g_year||g_quarter
611 ,p_action_information3 => p_gre_id
612 ,p_action_information4 => l_bank_code
613 ,p_action_information5 => t_challan_entry_asg_tab(i).Payment_date
614 ,p_action_information6 => nvl(t_challan_entry_asg_tab(i).tax,0)
615 ,p_action_information7 => nvl(t_challan_entry_asg_tab(i).surcharge,0)
616 ,p_action_information8 => nvl(t_challan_entry_asg_tab(i).cess,0)
617 ,p_action_information9 => nvl(t_challan_entry_asg_tab(i).interest,0)
618 ,p_action_information10 => nvl(t_challan_entry_asg_tab(i).others,0)
619 ,p_action_information11 => t_challan_entry_asg_tab(i).dd_cheq_num
620 ,p_action_information12 => t_challan_entry_asg_tab(i).book_entry
621 ,p_action_information13 => t_challan_entry_asg_tab(i).remarks
622 ,p_action_information25 => i
626
623 ,p_action_information_id => l_action_info_id
624 ,p_object_version_number => l_ovn
625 );
627 END IF;
628 END IF;
629
630 END LOOP;
631 END IF;
632 pay_in_utils.set_location(g_debug,'Leaving : '||l_proc,20);
633 --
634 END archive_challan_data;
635
636 --------------------------------------------------------------------------
637 -- --
638 -- Name : ARCHIVE_ORG_DATA --
639 -- Type : PROCEDURE --
640 -- Access : Public --
641 -- Description : Procedure to archive the Org/Representative --
642 -- data at PA level as on the quarter end date --
643 -- Parameters : --
644 -- IN : p_arc_pay_action_id NUMBER --
645 -- p_gre_id NUMBER --
646 -- p_effective_date DATE --
647 -- OUT : N/A --
648 -- --
649 -- Change History : --
650 --------------------------------------------------------------------------
651 -- Rev# Date Userid Description --
652 --------------------------------------------------------------------------
653 -- 115.0 05-Jan-2006 lnagaraj Initial Version --
654 -- 115.1 25-Sep-2007 rsaharay Modified cursors c_pos,c_rep_address --
655 --------------------------------------------------------------------------
656 PROCEDURE archive_org_data(p_arc_pay_action_id IN NUMBER
657 ,p_gre_id IN NUMBER
658 ,p_effective_date IN DATE
659 )
660 IS
661
662 CURSOR c_org_inc_tax_df_details
663 IS
664 SELECT hoi.org_information1 tan
665 ,hoi.org_information3 er_class
666 ,hoi.org_information4 reg_org_id
667 ,hoi.org_information7 division
668 ,hou.location_id location_id
669 FROM hr_organization_information hoi
670 ,hr_organization_units hou
671 WHERE hoi.organization_id = p_gre_id
672 AND hoi.org_information_context = 'PER_IN_INCOME_TAX_DF'
673 AND hou.organization_id = hoi.organization_id
674 AND hou.business_group_id = g_bg_id
675 AND p_effective_date BETWEEN hou.date_from AND NVL(hou.date_to,TO_DATE('31-12-4712','DD-MM-YYYY'));
676
677 CURSOR c_reg_org_details(p_reg_org_id NUMBER)
678 IS
679 SELECT hoi.org_information3 pan
680 ,hoi.org_information4 legal_name
681 FROM hr_organization_information hoi
682 ,hr_organization_units hou
683 WHERE hoi.organization_id = p_reg_org_id
684 AND hoi.org_information_context = 'PER_IN_COMPANY_DF'
685 AND hou.organization_id = hoi.organization_id
686 AND hou.business_group_id = g_bg_id
687 AND p_effective_date BETWEEN hou.date_from AND NVL(hou.date_to,TO_DATE('31-12-4712','DD-MM-YYYY'));
688
689 CURSOR c_pos(p_person_id NUMBER)
690 IS
691 SELECT nvl(pos.name,job.name) name
692 FROM per_positions pos
693 ,per_assignments_f asg
694 ,per_jobs job
695 WHERE asg.position_id=pos.position_id(+)
696 AND asg.job_id=job.job_id(+)
697 AND asg.person_id = p_person_id
698 AND asg.primary_flag = 'Y'
699 AND asg.business_group_id = g_bg_id
700 AND p_effective_date BETWEEN pos.date_effective(+) AND NVL(pos.date_end(+),TO_DATE('31-12-4712','DD-MM-YYYY'))
701 AND p_effective_date BETWEEN job.date_from(+) AND NVL(job.date_to(+),TO_DATE('31-12-4712','DD-MM-YYYY'))
702 AND p_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date;
703
704
705 CURSOR c_representative_id
706 IS
707 SELECT hoi.org_information1 person_id
708 ,hr_in_utility.per_in_full_name(pep.first_name,pep.middle_names,pep.last_name,pep.title) rep_name
709 ,pep.email_address email_id
710 FROM hr_organization_information hoi
711 ,hr_organization_units hou
712 ,per_people_f pep
713 WHERE hoi.org_information_context = 'PER_IN_INCOME_TAX_REP_DF'
714 AND hoi.organization_id = p_gre_id
715 AND hou.organization_id = hoi.organization_id
716 AND hou.business_group_id = g_bg_id
717 AND pep.person_id = hoi.org_information1
718 AND pep.business_group_id = hou.business_group_id
719 AND p_effective_date BETWEEN pep.effective_start_date AND pep.effective_end_date
720 AND p_effective_date BETWEEN fnd_date.canonical_to_date(hoi.org_information2)
721 AND NVL(fnd_date.canonical_to_date(hoi.org_information3),TO_DATE('31-12-4712','DD-MM-YYYY'))
722 AND p_effective_date BETWEEN hou.date_from AND NVL(hou.date_to,TO_DATE('31-12-4712','DD-MM-YYYY'));
723
724 CURSOR c_rep_address(p_person_id NUMBER)
725 IS
726 SELECT hou.location_id rep_location
727 FROM per_assignments_f asg
728 ,hr_organization_units hou
729 WHERE asg.person_id = p_person_id
730 AND asg.primary_flag = 'Y'
734 AND p_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
731 AND asg.business_group_id = g_bg_id
732 AND hou.organization_id = asg.organization_id
733 AND hou.business_group_id = asg.business_group_id
735 AND p_effective_date BETWEEN hou.date_from AND NVL(hou.date_to,TO_DATE('31-12-4712','DD-MM-YYYY'));
736
737 CURSOR c_rep_phone(p_person_id NUMBER)
738 IS
739 SELECT phone_number rep_phone_no
740 FROM per_phones
741 WHERE parent_id = p_person_id
742 AND phone_type = 'W1'
743 AND p_effective_date BETWEEN date_from AND NVL(date_to,TO_DATE('31-12-4712','DD-MM-YYYY'));
744
745 CURSOR csr_challan_recs
746 IS
747 SELECT COUNT(*)
748 FROM pay_action_information
749 WHERE action_information_category = 'IN_24Q_CHALLAN'
750 AND action_context_type = 'PA'
751 AND action_context_id = p_arc_pay_action_id
752 AND action_information3 = p_gre_id
753 AND action_information2 = g_year||g_quarter;
754
755 l_tan hr_organization_information.org_information1%TYPE;
756 l_er_class hr_organization_information.org_information3%TYPE;
757 l_reg_org_id hr_organization_information.org_information4%TYPE;
758 l_division hr_organization_information.org_information7%TYPE;
759 l_location_id hr_organization_units.location_id%TYPE;
760 l_pan hr_organization_information.org_information3%TYPE;
761 l_legal_name hr_organization_information.org_information4%TYPE;
762 l_rep_person_id per_all_people_f.person_id%TYPE;
763 l_rep_name per_all_people_f.full_name%TYPE;
764 l_position per_all_positions.name%TYPE;
765 l_rep_location hr_organization_units.location_id%TYPE;
766 l_rep_phone_no per_phones.phone_number%TYPE;
767 l_rep_email_id per_all_people_f.email_address%TYPE;
768 l_action_info_id NUMBER;
769 l_ovn NUMBER;
770 l_challan_count NUMBER;
771 l_nil_challan VARCHAR2(1);
772 l_proc VARCHAR2(100);
773 l_message VARCHAR2(255);
774
775 BEGIN
776 g_debug := hr_utility.debug_enabled;
777 l_proc := g_package || 'archive_org_data';
778
779 pay_in_utils.set_location(g_debug,'Entering : '||l_proc,10);
780
781 if g_debug then
782 pay_in_utils.trace('******************************','********************');
783 pay_in_utils.trace('p_arc_pay_action_id : ',p_arc_pay_action_id);
784 pay_in_utils.trace('p_gre_id : ',p_gre_id);
785 pay_in_utils.trace('p_effective_date : ',p_effective_date);
786 pay_in_utils.trace('******************************','********************');
787 end if;
788
789 OPEN c_org_inc_tax_df_details;
790 FETCH c_org_inc_tax_df_details INTO l_tan,l_er_class,l_reg_org_id,l_division,l_location_id;
791 CLOSE c_org_inc_tax_df_details;
792
793 OPEN c_reg_org_details(l_reg_org_id);
794 FETCH c_reg_org_details INTO l_pan,l_legal_name;
795 CLOSE c_reg_org_details;
796
797 OPEN c_representative_id;
798 FETCH c_representative_id INTO l_rep_person_id,l_rep_name,l_rep_email_id;
799 CLOSE c_representative_id;
800
801 OPEN c_pos(l_rep_person_id);
802 FETCH c_pos INTO l_position;
803 CLOSE c_pos;
804
805 OPEN c_rep_address(l_rep_person_id);
806 FETCH c_rep_address INTO l_rep_location;
807 CLOSE c_rep_address;
808
809 OPEN c_rep_phone(l_rep_person_id);
810 FETCH c_rep_phone INTO l_rep_phone_no;
811 CLOSE c_rep_phone;
812
813 OPEN csr_challan_recs;
814 FETCH csr_challan_recs INTO l_challan_count;
815 IF l_challan_count <> 0 THEN
816 l_nil_challan := 'N';
817 ELSE
818 l_nil_challan := 'Y';
819 END IF;
820 CLOSE csr_challan_recs;
821
822 if g_debug then
823 pay_in_utils.trace('l_tan : ',l_tan);
824 pay_in_utils.trace('g_year : ',g_year);
825 pay_in_utils.trace('g_quarter : ',g_quarter);
826 pay_in_utils.trace('l_reg_org_id : ',l_reg_org_id);
827 pay_in_utils.trace('l_division : ',l_division);
828 pay_in_utils.trace('l_location_id : ',l_location_id);
829 pay_in_utils.trace('l_pan : ',l_pan);
830 pay_in_utils.trace('l_legal_name : ',l_legal_name);
831 pay_in_utils.trace('l_rep_person_id : ',l_rep_person_id);
832 pay_in_utils.trace('l_rep_name : ',l_rep_name);
833 pay_in_utils.trace('l_rep_email_id : ',l_rep_email_id);
834 pay_in_utils.trace('l_position : ',l_position);
835 pay_in_utils.trace('l_rep_location : ',l_rep_location);
836 pay_in_utils.trace('l_rep_phone_no : ',l_rep_phone_no);
837 end if;
838
839 pay_action_information_api.create_action_information
840 (p_action_context_id => p_arc_pay_action_id
841 ,p_action_context_type => 'PA'
842 ,p_action_information_category => 'IN_24Q_ORG'
843 ,p_action_information1 => p_gre_id
844 ,p_action_information2 => l_tan
848 ,p_action_information6 => l_location_id
845 ,p_action_information3 => g_year||g_quarter
846 ,p_action_information4 => l_pan
847 ,p_action_information5 => l_legal_name
849 ,p_action_information7 => l_er_class
850 ,p_action_information8 => l_division
851 ,p_action_information9 => l_rep_name
852 ,p_action_information10 => l_rep_email_id
853 ,p_action_information11 => l_position
854 ,p_action_information12 => l_rep_location
855 ,p_action_information13 => l_rep_phone_no
856 ,p_action_information30 => g_archive_ref_no
857 ,p_action_information26 => l_nil_challan
858 ,p_action_information_id => l_action_info_id
859 ,p_object_version_number => l_ovn
860 );
861
862 pay_in_utils.set_location(g_debug,'Leaving : '||l_proc, 20);
863
864 END archive_org_data;
865
866 --------------------------------------------------------------------------
867 -- --
868 -- Name : ARCHIVE_PERSON_DATA --
869 -- Type : PROCEDURE --
870 -- Access : Public --
871 -- Description : This procedure archives the person data --
872 -- Parameters : --
873 -- IN : p_run_asg_action_id NUMBER --
874 -- p_arc_asg_action_id NUMBER --
875 -- p_assignment_id NUMBER --
876 -- p_gre_id NUMBER --
877 -- p_effective_start_date DATE --
878 -- p_effective_end_date DATE --
879 -- p_effective_date DATE --
880 -- p_termination_date DATE --
881 -- OUT : N/A --
882 -- --
883 -- Change History : --
884 --------------------------------------------------------------------------
885 -- Rev# Date Userid Description --
886 --------------------------------------------------------------------------
887 -- 115.0 05-Jan-2006 lnagaraaj Initial Version --
888 -- 115.1 25-Sep-2007 rsaharay Modified cursors c_pos --
889 --------------------------------------------------------------------------
890 --
891 PROCEDURE archive_person_data(p_run_asg_action_id IN NUMBER
892 ,p_arc_asg_action_id IN NUMBER
893 ,p_assignment_id IN NUMBER
894 ,p_gre_id IN NUMBER
895 ,p_effective_start_date IN DATE
896 ,p_effective_end_date IN DATE
897 ,p_effective_date IN DATE
898 ,p_termination_date IN DATE
899 ,p_person_table IN OUT NOCOPY t_person_data_tab_type
900 )
901 IS
902
903 CURSOR c_emp_no
904 IS
905 SELECT asg.person_id person_id
906 ,DECODE(pep.per_information4,NULL,DECODE(pep.per_information5,'Yes','APPLIEDFOR','PANNOTAVBL'),pep.per_information4) pan
907 ,pep.per_information14 pan_ref_num
908 ,hr_in_utility.per_in_full_name(pep.first_name,pep.middle_names,pep.last_name,pep.title) name
909 FROM per_assignments_f asg
910 ,per_people_f pep
911 WHERE asg.assignment_id = p_assignment_id
912 AND pep.person_id = asg.person_id
913 AND pep.business_group_id = g_bg_id
914 AND asg.business_group_id = g_bg_id
915 AND p_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
916 AND p_effective_date BETWEEN pep.effective_start_date AND pep.effective_end_date ;
917
918 CURSOR c_pos
919 IS
920 SELECT nvl(pos.name,job.name) name
921 FROM per_all_positions pos
922 ,per_assignments_f asg
923 ,per_jobs job
924 WHERE asg.position_id=pos.position_id(+)
925 AND asg.job_id=job.job_id(+)
926 AND asg.assignment_id = p_assignment_id
927 AND asg.business_group_id = g_bg_id
928 AND p_effective_date BETWEEN pos.date_effective(+) AND NVL(pos.date_end(+),TO_DATE('31-12-4712','DD-MM-YYYY'))
929 AND p_effective_date BETWEEN job.date_from(+) AND NVL(job.date_to(+),TO_DATE('31-12-4712','DD-MM-YYYY'))
930 AND p_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date;
931
932
933 CURSOR c_aei_tax_rate(p_person_id NUMBER)
934 IS
935 SELECT paei.aei_information2
936 FROM per_assignment_extra_info paei
937 ,per_assignments_f paa
938 WHERE paei.information_type = 'PER_IN_TAX_EXEMPTION_DF'
939 AND paei.aei_information_category = 'PER_IN_TAX_EXEMPTION_DF'
940 AND paei.assignment_id = paa.assignment_id
944 AND ROWNUM = 1;
941 AND paa.person_id = p_person_id
942 AND paei.aei_information1 = g_tax_year
943 AND p_effective_date BETWEEN paa.effective_start_date AND paa.effective_end_date
945
946 CURSOR csr_payroll_id(p_assignment_id NUMBER,p_date DATE)
947 IS
948 SELECT paf.payroll_id
949 FROM per_all_assignments_f paf
950 WHERE paf.assignment_id =p_assignment_id
951 AND p_date BETWEEN paf.effective_start_date AND paf.effective_end_date;
952
953 l_person_id per_all_people_f.person_id%TYPE;
954 l_pan per_all_people_f.per_information4%TYPE;
955 l_pan_ref_num per_all_people_f.per_information14%TYPE;
956 l_name per_all_people_f.full_name%TYPE;
957 l_pos per_all_positions.name%TYPE;
958 l_tax_rate per_assignment_extra_info.aei_information2%TYPE;
959 l_action_info_id NUMBER;
960 l_ovn NUMBER;
961 flag BOOLEAN;
962 l_full_name per_all_people_f.full_name%TYPE;
963
964 l_effective_start_date DATE;
965 l_effective_end_date DATE;
966 l_payroll_id NUMBER;
967 l_total_pay_period NUMBER;
968 l_current_pay_period NUMBER;
969
970 l_proc VARCHAR2(100);
971 l_message VARCHAR2(255);
972
973
974 BEGIN
975
976 g_debug := hr_utility.debug_enabled;
977 l_proc := g_package || 'archive_person_data';
978
979 pay_in_utils.set_location(g_debug,'Entering : '||l_proc,10);
980
981 if g_debug then
982 pay_in_utils.trace('******************************','********************');
983 pay_in_utils.trace('p_run_asg_action_id : ',p_run_asg_action_id);
984 pay_in_utils.trace('p_arc_asg_action_id : ',p_arc_asg_action_id);
985 pay_in_utils.trace('p_assignment_id : ',p_assignment_id);
986 pay_in_utils.trace('p_gre_id : ',p_gre_id);
987 pay_in_utils.trace('p_effective_start_date : ',p_effective_start_date);
988 pay_in_utils.trace('p_effective_end_date : ',p_effective_end_date);
989 pay_in_utils.trace('p_effective_date : ',p_effective_date);
990 pay_in_utils.trace('p_termination_date : ',p_termination_date);
991 pay_in_utils.trace('******************************','********************');
992 end if;
993
994 IF p_person_table.EXISTS(1) THEN
995 NULL;
996 ELSE
997 --
998 OPEN c_emp_no;
999 FETCH c_emp_no INTO l_person_id,l_pan,l_pan_ref_num,l_name;
1000 CLOSE c_emp_no;
1001
1002
1003 OPEN c_pos;
1004 FETCH c_pos INTO l_pos;
1005 CLOSE c_pos;
1006
1007 OPEN c_aei_tax_rate(l_person_id);
1008 FETCH c_aei_tax_rate INTO l_tax_rate;
1009 CLOSE c_aei_tax_rate;
1010
1011 p_person_table(1).person_id := l_person_id;
1012 p_person_table(1).pan_number := l_pan;
1013 p_person_table(1).pan_ref_number := l_pan_ref_num;
1014 p_person_table(1).full_name := l_name;
1015 p_person_table(1).tax_rate := l_tax_rate;
1016 p_person_table(1).position := l_pos;
1017 --
1018 END IF;
1019
1020 IF p_effective_start_date > LEAST(p_effective_end_date,p_termination_date) THEN
1021 l_effective_end_date := g_end_date;
1022 ELSE
1023 l_effective_end_date := LEAST(g_end_date,p_effective_end_date,p_termination_date);
1024 END IF;
1025
1026 IF g_quarter = 'Q4' THEN
1027 l_effective_start_date := p_effective_start_date;
1028 ELSE
1029 l_effective_start_date := GREATEST(g_qr_start_date,p_effective_start_date);
1030 END IF;
1031
1032 if g_debug then
1033 pay_in_utils.trace('person_id : ',p_person_table(1).person_id);
1034 pay_in_utils.trace('g_year : ',g_year);
1035 pay_in_utils.trace('g_quarter : ',g_quarter);
1036 pay_in_utils.trace('pan_number : ',p_person_table(1).pan_number);
1037 pay_in_utils.trace('pan_ref_number : ',p_person_table(1).pan_ref_number);
1038 pay_in_utils.trace('full_name : ',p_person_table(1).full_name);
1039 pay_in_utils.trace('tax_rate : ',p_person_table(1).tax_rate);
1040 pay_in_utils.trace('position : ',p_person_table(1).position);
1041 pay_in_utils.trace('l_effective_start_date : ',l_effective_start_date);
1042 pay_in_utils.trace('l_effective_end_date : ',l_effective_end_date);
1043 end if;
1044
1045 pay_action_information_api.create_action_information
1046 (p_action_context_id => p_arc_asg_action_id
1047 ,p_action_context_type => 'AAP'
1048 ,p_action_information_category => 'IN_24Q_PERSON'
1049 ,p_source_id => p_run_asg_action_id
1050 ,p_assignment_id => p_assignment_id
1051 ,p_action_information1 => p_person_table(1).person_id
1052 ,p_action_information2 => g_year||g_quarter
1053 ,p_action_information3 => p_gre_id
1057 ,p_action_information7 => p_person_table(1).tax_rate
1054 ,p_action_information4 => p_person_table(1).pan_number
1055 ,p_action_information5 => p_person_table(1).pan_ref_number
1056 ,p_action_information6 => p_person_table(1).full_name
1058 ,p_action_information8 => p_person_table(1).position
1059 ,p_action_information9 => fnd_date.date_to_canonical(l_effective_start_date)
1060 ,p_action_information10 => fnd_date.date_to_canonical(l_effective_end_date)
1061 ,p_action_information_id => l_action_info_id
1062 ,p_object_version_number => l_ovn
1063 );
1064
1065 pay_in_utils.set_location(g_debug,'Leaving: '||l_proc,20);
1066
1067 END archive_person_data;
1068
1069 --------------------------------------------------------------------------
1070 -- --
1071 -- Name : ARCHIVE_VIA_DETAILS --
1072 -- Type : PROCEDURE --
1073 -- Access : Public --
1074 -- Description : This procedure archives the Chapter VI A related --
1075 -- details under 3 heads - 80G, 80GG and 80OTHERS --
1076 -- Parameters : --
1077 -- IN : p_run_asg_action_id NUMBER --
1078 -- p_arc_pay_action_id NUMBER --
1079 -- p_gre_id NUMBER --
1080 -- p_assignment_id NUMBER --
1081 -- OUT : N/A --
1082 -- --
1083 -- Change History : --
1084 --------------------------------------------------------------------------
1085 -- Rev# Date Userid Description --
1086 --------------------------------------------------------------------------
1087 -- 115.0 05-Jan-2006 lnagaraj Initial Version --
1088 -- 115.1 26-Jun-2006 aaagarwa Modifed c_defined_balance_id --
1089 --------------------------------------------------------------------------
1090 PROCEDURE archive_via_details(p_run_asg_action_id IN NUMBER
1091 ,p_arc_asg_action_id IN NUMBER
1092 ,p_gre_id IN NUMBER
1093 ,p_assignment_id IN NUMBER
1094 )
1095 IS
1096
1097 CURSOR c_defined_balance_id
1098 IS
1099 SELECT pdb.defined_balance_id balance_id
1100 ,pbt.balance_name balance_name
1101 FROM pay_balance_types pbt
1102 ,pay_balance_dimensions pbd
1103 ,pay_defined_balances pdb
1104 WHERE pbt.balance_name IN('F16 Deductions Sec 80E'
1105 ,'F16 Deductions Sec 80U'
1106 ,'Gross Chapter VIA Deductions'
1107 ,'Deferred Annuity'
1108 ,'Senior Citizens Savings Scheme'
1109 ,'Pension Fund'
1110 ,'F16 Employee PF Contribution'
1111 ,'F16 Total Chapter VI A Deductions'
1112 ,'Deductions under Section 80CCE'
1113 ,'F16 Deductions Sec 80GG'
1114 ,'F16 Deductions Sec 80G'
1115 ,'F16 Deductions Sec 80CCE'
1116 ,'F16 ER Pension Contribution'
1117 )
1118 AND pbd.dimension_name='_ASG_LE_PTD'
1119 AND pbt.legislation_code = 'IN'
1120 AND pbd.legislation_code = 'IN'
1121 AND pdb.legislation_code = 'IN'
1122 AND pbt.balance_type_id = pdb.balance_type_id
1123 AND pbd.balance_dimension_id = pdb.balance_dimension_id
1124 ORDER BY pbt.balance_name;
1125
1126 g_bal_name_tab t_bal_name_tab;
1127 g_balance_value_tab pay_balance_pkg.t_balance_value_tab;
1128 g_context_table pay_balance_pkg.t_context_tab;
1129 g_result_table pay_balance_pkg.t_detailed_bal_out_tab;
1130 g_balance_value_tab1 pay_balance_pkg.t_balance_value_tab;
1131 g_result_table1 pay_balance_pkg.t_detailed_bal_out_tab;
1132
1133 i NUMBER;
1134 l_action_info_id NUMBER;
1135 l_ovn NUMBER;
1136 l_80g_gross NUMBER;
1137 l_via_gross NUMBER;
1138 l_tot_via_qa NUMBER;
1139 l_80gg_qa_gross NUMBER;
1140 l_80g_qa NUMBER;
1141 l_via_others_gross NUMBER;
1142 l_via_others_qa NUMBER;
1143 l_80cce_others NUMBER;
1144 l_q4_80cce_total NUMBER;
1145 l_q4_others_total NUMBER;
1146 l_proc VARCHAR2(100);
1147 l_message VARCHAR2(255);
1148
1149 BEGIN
1150 -- STEP 0 :Initialise variables
1151 i := 1;
1152 l_80g_gross := 0;
1153 l_via_gross := 0;
1154 l_tot_via_qa := 0;
1155 l_80gg_qa_gross := 0;
1156 l_80g_qa := 0 ;
1157 l_via_others_gross := 0;
1158 l_via_others_qa := 0;
1159 l_80cce_others := 0;
1163 l_proc := g_package||'archive_via_details';
1160 l_q4_80cce_total := 0;
1161 l_q4_others_total := 0;
1162 g_debug := hr_utility.debug_enabled;
1164 pay_in_utils.set_location(g_debug,'Entering : '||l_proc,10);
1165
1166 if g_debug then
1167 pay_in_utils.trace('******************************','********************');
1168 pay_in_utils.trace('p_run_asg_action_id : ',p_run_asg_action_id);
1169 pay_in_utils.trace('p_arc_asg_action_id : ',p_arc_asg_action_id);
1170 pay_in_utils.trace('p_gre_id : ',p_gre_id);
1171 pay_in_utils.trace('p_assignment_id : ',p_assignment_id);
1172 pay_in_utils.trace('******************************','********************');
1173 end if;
1174
1175
1176 -- STEP 1 : Gross Amount determination for 80G
1177 g_balance_value_tab1.DELETE;
1178 g_context_table.DELETE;
1179 g_result_table1.DELETE;
1180
1181 g_context_table(1).source_text2 := 'Donations'; -- 80G
1182 g_context_table(1).tax_unit_id := p_gre_id;
1183
1184 g_balance_value_tab1(1).defined_balance_id :=
1185 pay_in_tax_utils.get_defined_balance('Gross Chapter VIA Deductions','_ASG_LE_COMP_PTD');
1186
1187 pay_balance_pkg.get_value(p_assignment_action_id => p_run_asg_action_id
1188 ,p_defined_balance_lst => g_balance_value_tab1
1189 ,p_context_lst => g_context_table
1190 ,p_output_table => g_result_table1
1191 );
1192 l_80g_gross := g_result_table1(1).balance_value;
1193 pay_in_utils.set_location(g_debug,'80G Gross : '||l_80g_gross,20);
1194
1195 -- STEP 2 : Get Qualifying Amt of Deferred Annuity,Senior Citizens Savings Scheme, Pension Fund and LIC
1196 g_balance_value_tab1.DELETE;
1197 g_context_table.DELETE;
1198 g_result_table1.DELETE;
1199
1200 g_balance_value_tab1(1).defined_balance_id := pay_in_tax_utils.get_defined_balance
1201 ('Deductions under Section 80CCE','_ASG_LE_COMP_PTD');
1202 g_context_table(1).source_text2 := 'Deferred Annuity';
1203 g_context_table(1).tax_unit_id := p_gre_id;
1204 g_context_table(2).source_text2 := 'Pension Fund 80CCC';
1205 g_context_table(2).tax_unit_id := p_gre_id;
1206 g_context_table(3).source_text2 := 'Life Insurance Premium';
1207 g_context_table(3).tax_unit_id := p_gre_id;
1208 g_context_table(4).source_text2 := 'Senior Citizens Savings Scheme';
1209 g_context_table(4).tax_unit_id := p_gre_id;
1210
1211
1212 pay_balance_pkg.get_value(p_assignment_action_id => p_run_asg_action_id
1213 ,p_defined_balance_lst => g_balance_value_tab1
1214 ,p_context_lst => g_context_table
1215 ,p_output_table => g_result_table1
1216 );
1217 FOR i IN 1..4 LOOP
1218 l_80cce_others := l_80cce_others + g_result_table1(i).balance_value;
1219 END LOOP;
1220 pay_in_utils.set_location(g_debug,'Qualifying Amount of three 80CCE components: '||l_80cce_others,20);
1221
1222 -- STEP 3: Qualifying amt of 80GG ,80G, Total Qualifying Chapter VIA,Gross Amt of ALL chapter VIA Components
1223 g_context_table.DELETE;
1224 g_result_table.DELETE;
1225 g_balance_value_tab.DELETE;
1226 g_bal_name_tab.DELETE;
1227 g_context_table(1).tax_unit_id := p_gre_id;
1228
1229 FOR c_rec IN c_defined_balance_id
1230 LOOP
1231 g_balance_value_tab(i).defined_balance_id := c_rec.balance_id;
1232 g_bal_name_tab(i).balance_name := c_rec.balance_name;
1233 i := i + 1;
1234 END LOOP;
1235
1236 pay_balance_pkg.get_value(p_assignment_action_id => p_run_asg_action_id
1237 ,p_defined_balance_lst => g_balance_value_tab
1238 ,p_context_lst => g_context_table
1239 ,p_output_table => g_result_table
1240 );
1241 pay_in_utils.set_location(g_debug,'ASSACT: '||p_run_asg_action_id,30);
1242
1243 FOR i IN 1..g_balance_value_tab.COUNT
1244 LOOP
1245 pay_in_utils.set_location(g_debug,'Balance Name: '|| g_bal_name_tab(i).balance_name,32);
1246 pay_in_utils.set_location(g_debug,'Balance Value: '|| g_result_table(i).balance_value,34);
1247 IF (g_result_table(i).balance_value <> 0)
1248 THEN
1249 IF (g_bal_name_tab(i).balance_name IN('F16 Deductions Sec 80E'
1250 ,'F16 Deductions Sec 80U'
1251 ,'Gross Chapter VIA Deductions'
1252 ,'Deferred Annuity'
1253 ,'Senior Citizens Savings Scheme'
1254 ,'Pension Fund'
1255 ,'F16 Employee PF Contribution'
1256 ,'Deductions under Section 80CCE'
1257 ,'F16 ER Pension Contribution'
1258 )
1259 )
1260 THEN
1261 l_via_gross := l_via_gross + g_result_table(i).balance_value ;
1262 ELSIF (g_bal_name_tab(i).balance_name ='F16 Total Chapter VI A Deductions')
1263 THEN
1267 l_80gg_qa_gross := g_result_table(i).balance_value ;
1264 l_tot_via_qa := g_result_table(i).balance_value ;
1265 ELSIF (g_bal_name_tab(i).balance_name = 'F16 Deductions Sec 80GG')
1266 THEN
1268 ELSIF (g_bal_name_tab(i).balance_name = 'F16 Deductions Sec 80CCE')
1269 THEN
1270 l_q4_80cce_total := g_result_table(i).balance_value ;
1271 ELSE
1272 l_80g_qa := g_result_table(i).balance_value ;
1273 END IF;
1274
1275 END IF;
1276 END LOOP;
1277
1278 l_via_others_gross := l_via_gross - l_80g_gross -l_80cce_others;
1279 l_via_others_qa := l_tot_via_qa - (l_80g_qa + l_80gg_qa_gross);
1280
1281 l_q4_others_total := l_tot_via_qa - l_q4_80cce_total;
1282
1283 pay_in_utils.set_location(g_debug,'Gross Amt 80G: '||l_80g_gross,30);
1284 pay_in_utils.set_location(g_debug,'Qual Amt 80G: '||l_80g_qa,40);
1285 pay_in_utils.set_location(g_debug,'Both Amts 80GG: '||l_80gg_qa_gross,50);
1286 pay_in_utils.set_location(g_debug,'Gross Amt Others: '||l_via_others_gross,60);
1287 pay_in_utils.set_location(g_debug,'Qual Amt Others: '|| l_via_others_qa,70);
1288 pay_in_utils.set_location(g_debug,'Amount 80CCE: '|| l_q4_80cce_total,80);
1289 pay_in_utils.set_location(g_debug,'Amount Others: '|| l_q4_others_total,90);
1290 pay_in_utils.set_location(g_debug,'l_80cce_others: '|| l_80cce_others,100);
1291 pay_in_utils.set_location(g_debug,'l_q4_others_total '|| l_q4_others_total,100);
1292 -- STEP 4: Archive values
1293
1294 IF (g_quarter = 'Q4') THEN
1295 IF (l_q4_80cce_total <> 0) THEN
1296 pay_action_information_api.create_action_information
1297 (p_action_context_id => p_arc_asg_action_id
1298 ,p_action_context_type => 'AAP'
1299 ,p_action_information_category => 'IN_24Q_VIA'
1300 ,p_source_id => p_run_asg_action_id
1301 ,p_action_information1 => '80CCE'
1302 ,p_action_information2 => l_q4_80cce_total
1303 ,p_action_information3 => 0
1304 ,p_action_information_id => l_action_info_id
1305 ,p_object_version_number => l_ovn
1306 );
1307 END IF;
1308
1309 IF (l_q4_others_total <> 0) THEN
1310 pay_action_information_api.create_action_information
1311 (p_action_context_id => p_arc_asg_action_id
1312 ,p_action_context_type => 'AAP'
1313 ,p_action_information_category => 'IN_24Q_VIA'
1314 ,p_source_id => p_run_asg_action_id
1315 ,p_action_information1 => 'Others'
1316 ,p_action_information2 => l_q4_others_total
1317 ,p_action_information3 => 0
1318 ,p_action_information_id => l_action_info_id
1319 ,p_object_version_number => l_ovn
1320 );
1321 END IF;
1322 ELSE
1323 IF (l_80g_gross <>0 OR l_80g_qa <> 0) THEN
1324 pay_action_information_api.create_action_information
1325 (p_action_context_id => p_arc_asg_action_id
1326 ,p_action_context_type => 'AAP'
1327 ,p_action_information_category => 'IN_24Q_VIA'
1328 ,p_source_id => p_run_asg_action_id
1329 ,p_action_information1 => '80G'
1330 ,p_action_information2 => l_80g_qa
1331 ,p_action_information3 => l_80g_gross
1332 ,p_action_information_id => l_action_info_id
1333 ,p_object_version_number => l_ovn
1334 );
1335 END IF;
1336
1337 IF (l_80gg_qa_gross <> 0) THEN
1338 pay_action_information_api.create_action_information
1339 (p_action_context_id => p_arc_asg_action_id
1340 ,p_action_context_type => 'AAP'
1341 ,p_action_information_category => 'IN_24Q_VIA'
1342 ,p_source_id => p_run_asg_action_id
1343 ,p_action_information1 => '80GG'
1344 ,p_action_information2 => l_80gg_qa_gross
1345 ,p_action_information3 => l_80gg_qa_gross
1346 ,p_action_information_id => l_action_info_id
1347 ,p_object_version_number => l_ovn
1348 );
1349 END IF;
1350
1351 IF (l_via_others_gross <>0 OR l_via_others_qa <> 0) THEN
1352 pay_action_information_api.create_action_information
1353 (p_action_context_id => p_arc_asg_action_id
1354 ,p_action_context_type => 'AAP'
1355 ,p_action_information_category => 'IN_24Q_VIA'
1356 ,p_source_id => p_run_asg_action_id
1357 ,p_action_information1 => '80OTHERS'
1358 ,p_action_information2 => l_via_others_qa
1359 ,p_action_information3 => l_via_others_gross
1360 ,p_action_information_id => l_action_info_id
1364 END IF;
1361 ,p_object_version_number => l_ovn
1362 );
1363 END IF;
1365 -- STEP 5: Delete PL/SQL Tables
1366 g_bal_name_tab.DELETE;
1367 g_balance_value_tab.DELETE;
1368 g_balance_value_tab1.DELETE;
1369 g_result_table.DELETE;
1370 g_context_table.DELETE;
1371 g_result_table1.DELETE;
1372
1373 pay_in_utils.set_location(g_debug,'Leaving: '||l_proc,80);
1374 END archive_via_details;
1375
1376
1377 --------------------------------------------------------------------------
1378 -- --
1379 -- Name : ARCHIVE_ASG_SALARY --
1380 -- Type : PROCEDURE --
1381 -- Access : Public --
1382 -- Description : This procedure archives the various salary components-
1383 -- Parameters : --
1384 -- IN : p_run_asg_action_id NUMBER --
1385 -- p_arc_asg_action_id NUMBER --
1386 -- p_balance_periods NUMBER --
1387 -- p_gre_id NUMBER --
1388 -- pre_gre_asg_act_id NUMBER --
1389 -- OUT : N/A --
1390 -- --
1391 -- Change History : --
1392 --------------------------------------------------------------------------
1393 -- Rev# Date Userid Description --
1394 --------------------------------------------------------------------------
1395 -- 115.0 05-Jan-2006 lnagaraj Initial Version --
1396 -- 115.1 26-Jun-2006 aaagarwa Modifed c_f16_sal_balances --
1397 --------------------------------------------------------------------------
1398 PROCEDURE archive_asg_salary(p_run_asg_action_id IN NUMBER
1399 ,p_arc_asg_action_id IN NUMBER
1400 ,p_balance_periods IN NUMBER
1401 ,p_gre_id IN NUMBER
1402 ,pre_gre_asg_act_id IN NUMBER DEFAULT NULL)
1403 IS
1404
1405 CURSOR c_f16_sal_balances
1406 IS
1407 SELECT pdb.defined_balance_id balance_id
1408 ,pbt.balance_name balance_name
1409 FROM pay_balance_types pbt
1410 ,pay_balance_dimensions pbd
1411 ,pay_defined_balances pdb
1412 WHERE pbt.balance_name IN('F16 Salary Under Section 17'
1413 ,'F16 Profit in lieu of Salary'
1414 ,'F16 Value of Perquisites'
1415 ,'F16 Gross Salary less Allowances'
1416 ,'F16 Allowances Exempt'
1417 ,'F16 Deductions under Sec 16'
1418 ,'F16 Income Chargeable Under head Salaries'
1419 ,'F16 Other Income'
1420 ,'F16 Gross Total Income'
1421 ,'F16 Total Chapter VI A Deductions'
1422 ,'F16 Total Income'
1423 ,'F16 Tax on Total Income'
1424 ,'F16 Marginal Relief'
1425 ,'F16 Total Tax payable'
1426 ,'F16 Relief under Sec 89'
1427 ,'F16 Employment Tax'
1428 ,'F16 Entertainment Allowance'
1429 ,'Allowances Standard Value'
1430 ,'F16 Surcharge'
1431 ,'F16 Education Cess'
1432 ,'F16 Sec and HE Cess'
1433 ,'F16 TDS'
1434 )
1435 AND pbd.dimension_name ='_ASG_LE_PTD'
1436 AND pbt.legislation_code = 'IN'
1437 AND pbd.legislation_code = 'IN'
1438 AND pdb.legislation_code = 'IN'
1439 AND pbt.balance_type_id = pdb.balance_type_id
1440 AND pbd.balance_dimension_id = pdb.balance_dimension_id;
1441
1442 CURSOR c_er_excess_pf_balances
1443 IS
1444 SELECT pdb.defined_balance_id balance_id
1445 ,pbt.balance_name balance_name
1446 FROM pay_balance_types pbt
1447 ,pay_balance_dimensions pbd
1448 ,pay_defined_balances pdb
1449 WHERE pbt.balance_name IN( 'Excess Interest Amount'
1450 ,'Excess PF Amount'
1451 ,'Allowance Amount'
1452 )
1453 AND pbd.dimension_name='_ASG_YTD'
1454 AND pbt.legislation_code = 'IN'
1455 AND pbd.legislation_code = 'IN'
1456 AND pbt.balance_type_id = pdb.balance_type_id
1457 AND pbd.balance_dimension_id = pdb.balance_dimension_id;
1458
1459 g_bal_name_tab t_bal_name_tab;
1460 g_context_table pay_balance_pkg.t_context_tab;
1461 g_balance_value_tab pay_balance_pkg.t_balance_value_tab;
1462 g_result_table pay_balance_pkg.t_detailed_bal_out_tab;
1463 g_balance_value_tab1 pay_balance_pkg.t_balance_value_tab;
1464 g_balance_value_tab2 pay_balance_pkg.t_balance_value_tab;
1465
1466 l_allow_proj_value NUMBER;
1467 l_balance_value NUMBER;
1471 i NUMBER;
1468 l_action_info_id NUMBER;
1469 l_ovn NUMBER;
1470 l_in_tax_ded NUMBER :=0;
1472 l_total_cess NUMBER ;
1473
1474 l_proc VARCHAR2(100);
1475 l_message VARCHAR2(255);
1476
1477
1478 BEGIN
1479 g_debug := hr_utility.debug_enabled;
1480 l_proc := g_package||'archive_asg_salary';
1481
1482 pay_in_utils.set_location(g_debug,'Entering : '||l_proc,10);
1483
1484 if g_debug then
1485 pay_in_utils.trace('******************************','********************');
1486 pay_in_utils.trace('p_run_asg_action_id : ',p_run_asg_action_id);
1487 pay_in_utils.trace('p_arc_asg_action_id : ',p_arc_asg_action_id);
1488 pay_in_utils.trace('p_balance_periods : ',p_balance_periods);
1489 pay_in_utils.trace('p_gre_id : ',p_gre_id);
1490 pay_in_utils.trace('pre_gre_asg_act_id : ',pre_gre_asg_act_id);
1491 pay_in_utils.trace('******************************','********************');
1492 end if;
1493
1494 i := 1;
1495 g_bal_name_tab.DELETE;
1496 g_balance_value_tab.DELETE;
1497 g_result_table.DELETE;
1498 g_context_table.DELETE;
1499 g_context_table(1).tax_unit_id := p_gre_id;
1500 l_total_cess:=0;
1501
1502 --Step 1: Archive F16 balances,also get Projected Allowance Amount
1503 pay_in_utils.set_location(g_debug,'PERIODS '||p_balance_periods,10);
1504
1505 FOR c_rec IN c_f16_sal_balances
1506 LOOP
1507 g_balance_value_tab(i).defined_balance_id := c_rec.balance_id;
1508 g_bal_name_tab(i).balance_name := c_rec.balance_name;
1509 i := i + 1;
1510 END LOOP;
1511
1512 pay_balance_pkg.get_value(p_assignment_action_id => p_run_asg_action_id
1513 ,p_defined_balance_lst => g_balance_value_tab
1514 ,p_context_lst => g_context_table
1515 ,p_output_table => g_result_table
1516 );
1517
1518 l_allow_proj_value :=0;
1519
1520 FOR i IN 1..g_balance_value_tab.COUNT
1521 LOOP
1522 IF g_result_table(i).balance_value <> 0 AND(g_bal_name_tab(i).balance_name='F16 Education Cess' OR g_bal_name_tab(i).balance_name='F16 Sec and HE Cess' ) THEN
1523 l_total_cess:= l_total_cess + g_result_table(i).balance_value;
1524 END IF ;
1525 IF (g_result_table(i).balance_value <> 0)
1526 THEN
1527 IF g_bal_name_tab(i).balance_name = 'Allowances Standard Value' THEN
1528 l_allow_proj_value := g_result_table(i).balance_value * p_balance_periods;
1529 if g_debug then
1530 pay_in_utils.trace('l_allow_proj_value : ',l_allow_proj_value);
1531 end if;
1532 ELSIF g_bal_name_tab(i).balance_name <> 'F16 Education Cess' THEN
1533 if g_debug then
1534 pay_in_utils.trace('balance_name : ',g_bal_name_tab(i).balance_name);
1535 pay_in_utils.trace('balance_value : ',g_result_table(i).balance_value);
1536 end if;
1537 pay_action_information_api.create_action_information
1538 (p_action_context_id => p_arc_asg_action_id
1539 ,p_action_context_type => 'AAP'
1540 ,p_action_information_category => 'IN_24Q_SALARY'
1541 ,p_source_id => p_run_asg_action_id
1542 ,p_action_information1 => g_bal_name_tab(i).balance_name
1543 ,p_action_information2 => g_result_table(i).balance_value
1544 ,p_action_information_id => l_action_info_id
1545 ,p_object_version_number => l_ovn
1546 );
1547 END IF;
1548 END IF;
1549 END LOOP;
1550 IF l_total_cess <> 0 THEN
1551 pay_action_information_api.create_action_information
1552 (p_action_context_id => p_arc_asg_action_id
1553 ,p_action_context_type => 'AAP'
1554 ,p_action_information_category => 'IN_24Q_SALARY'
1555 ,p_source_id => p_run_asg_action_id
1556 ,p_action_information1 => 'F16 Education Cess'
1557 ,p_action_information2 => l_total_cess
1558 ,p_action_information_id => l_action_info_id
1559 ,p_object_version_number => l_ovn
1560 );
1561 END IF;
1562
1563 --Step 2: Get balances for Employer excess PF as total value -previous LE value
1564 g_bal_name_tab.DELETE;
1565 g_context_table.DELETE;
1566 g_balance_value_tab1.DELETE;
1567 g_balance_value_tab2.DELETE;
1568 g_result_table.DELETE;
1569 i := 1;
1570 l_total_cess:=0;
1571 FOR c_rec IN c_er_excess_pf_balances
1572 LOOP
1573 g_balance_value_tab1(i).defined_balance_id := c_rec.balance_id;
1574 g_balance_value_tab2(i).defined_balance_id := c_rec.balance_id;
1575 g_bal_name_tab(i).balance_name := c_rec.balance_name;
1576 i := i + 1;
1577 END LOOP;
1578
1579 pay_balance_pkg.get_value(p_run_asg_action_id,g_balance_value_tab1);
1580
1581 IF pre_gre_asg_act_id IS NOT NULL THEN
1582 pay_balance_pkg.get_value(pre_gre_asg_act_id,g_balance_value_tab2);
1586 FOR i IN 1..g_balance_value_tab1.COUNT
1583 END IF;
1584
1585 --Step 3:Archive values
1587 LOOP
1588 IF g_bal_name_tab(i).balance_name = 'Allowance Amount' THEN
1589 g_balance_value_tab1(i).balance_value := g_balance_value_tab1(i).balance_value + l_allow_proj_value;
1590 END IF;
1591
1592 IF pre_gre_asg_act_id IS NOT NULL THEN
1593 l_balance_value := g_balance_value_tab1(i).balance_value - g_balance_value_tab2(i).balance_value;
1594 ELSE
1595 l_balance_value := g_balance_value_tab1(i).balance_value;
1596 END IF;
1597
1598 if g_debug then
1599 pay_in_utils.trace('balance_name : ',g_bal_name_tab(i).balance_name);
1600 pay_in_utils.trace('l_balance_value : ',l_balance_value);
1601 end if;
1602
1603
1604
1605 IF (l_balance_value <> 0)
1606 THEN
1607 pay_action_information_api.create_action_information
1608 (p_action_context_id => p_arc_asg_action_id
1609 ,p_action_context_type => 'AAP'
1610 ,p_action_information_category => 'IN_24Q_SALARY'
1611 ,p_source_id => p_run_asg_action_id
1612 ,p_action_information1 => g_bal_name_tab(i).balance_name
1613 ,p_action_information2 => l_balance_value
1614 ,p_action_information_id => l_action_info_id
1615 ,p_object_version_number => l_ovn
1616 );
1617 END IF;
1618 END LOOP;
1619
1620
1621 -- Step 4:Delete all PL/SQL tables
1622 g_bal_name_tab.DELETE;
1623 g_context_table.DELETE;
1624 g_balance_value_tab.DELETE;
1625 g_result_table.DELETE;
1626 g_balance_value_tab1.DELETE;
1627 g_balance_value_tab2.DELETE;
1628
1629
1630 pay_in_utils.set_location(g_debug,'Leaving: '||l_proc,30);
1631
1632 END archive_asg_salary;
1633
1634 --------------------------------------------------------------------------
1635 -- --
1636 -- Name : balance_difference --
1637 -- Type : PROCEDURE --
1638 -- Access : Private --
1639 -- Description : This procedure is used to find the difference in --
1640 -- values of 2 PL/SQL tables --
1641 -- Parameters : --
1642 -- IN : g_result_table1 pay_balance_pkg.t_detailed_bal_out_tab --
1643 -- g_result_table2 pay_balance_pkg.t_detailed_bal_out_tab --
1644 -- IN/OUT : g_result_table --
1645 -- --
1646 -- Change History : --
1647 --------------------------------------------------------------------------
1648 -- Rev# Date Userid Description --
1649 --------------------------------------------------------------------------
1650 -- 115.0 05-Jan-2006 lnagaraj Initial Version --
1651 --------------------------------------------------------------------------
1652 PROCEDURE balance_difference(g_result_table1 IN pay_balance_pkg.t_detailed_bal_out_tab
1653 ,g_result_table2 IN pay_balance_pkg.t_detailed_bal_out_tab
1654 ,g_result_table IN OUT NOCOPY pay_balance_pkg.t_detailed_bal_out_tab
1655 )
1656 IS
1657 l_proc VARCHAR2(100);
1658 l_message VARCHAR2(255);
1659
1660 BEGIN
1661 g_debug := hr_utility.debug_enabled;
1662 l_proc := g_package||'balance_difference';
1663
1664 pay_in_utils.set_location(g_debug,'Entering : '||l_proc,10);
1665
1666 FOR i IN 1..GREATEST(g_result_table1.COUNT,g_result_table2.COUNT)
1667 LOOP
1668 g_result_table(i).balance_value := g_result_table1(i).balance_value
1669 - g_result_table2(i).balance_value;
1670 END LOOP;
1671
1672 pay_in_utils.set_location(g_debug,'Leaving : '||l_proc,20);
1673
1674 END;
1675
1676 --------------------------------------------------------------------------
1677 -- --
1678 -- Name : get_balances --
1679 -- Type : PROCEDURE --
1680 -- Access : Private --
1681 -- Description : Given a list of balances, current LE/Previous LE --
1682 -- assignment action id, this procedure finds the --
1683 -- balance values --
1684 -- Parameters : --
1685 -- IN : p_run_asg_action_id NUMBER --
1686 -- pre_gre_asg_act_id NUMBER --
1687 -- p_balance_name VARCHAR2 --
1688 -- p_balance_dimension VARCHAR2 --
1689 -- IN/OUT :g_context_table pay_balance_pkg.t_context_tab --
1690 -- g_balance_value_tab pay_balance_pkg.t_balance_value_tab--
1694 --------------------------------------------------------------------------
1691 -- g_result_table pay_balance_pkg.t_detailed_bal_out_tab --
1692 -- --
1693 -- Change History : --
1695 -- Rev# Date Userid Description --
1696 --------------------------------------------------------------------------
1697 -- 115.0 05-Jan-2006 lnagaraj Initial Version --
1698 --------------------------------------------------------------------------
1699 PROCEDURE get_balances(p_run_asg_action_id IN NUMBER
1700 ,pre_gre_asg_act_id IN NUMBER DEFAULT NULL
1701 ,p_balance_name IN VARCHAR2 DEFAULT NULL
1702 ,p_balance_dimension IN VARCHAR2 DEFAULT NULL
1703 ,g_context_table IN OUT NOCOPY pay_balance_pkg.t_context_tab
1704 ,g_balance_value_tab IN OUT NOCOPY pay_balance_pkg.t_balance_value_tab
1705 ,g_result_table IN OUT NOCOPY pay_balance_pkg.t_detailed_bal_out_tab
1706 )
1707 IS
1708
1709 l_result_table1 pay_balance_pkg.t_detailed_bal_out_tab;
1710 l_result_table2 pay_balance_pkg.t_detailed_bal_out_tab;
1711 l_proc VARCHAR2(100);
1712 l_message VARCHAR2(255);
1713
1714 BEGIN
1715 g_debug := hr_utility.debug_enabled;
1716 l_proc := g_package||'get_balances';
1717
1718 pay_in_utils.set_location(g_debug,'Entering : '||l_proc,10);
1719
1720 if g_debug then
1721 pay_in_utils.trace('******************************','********************');
1722 pay_in_utils.trace('p_run_asg_action_id : ',p_run_asg_action_id);
1723 pay_in_utils.trace('pre_gre_asg_act_id : ',pre_gre_asg_act_id);
1724 pay_in_utils.trace('p_balance_name : ',p_balance_name);
1725 pay_in_utils.trace('p_balance_dimension : ',p_balance_dimension);
1726 pay_in_utils.trace('******************************','********************');
1727 end if;
1728
1729 IF p_balance_name IS NOT NULL THEN
1730 g_balance_value_tab(1).defined_balance_id :=
1731 pay_in_tax_utils.get_defined_balance(p_balance_name,p_balance_dimension);
1732 END IF;
1733
1734 pay_balance_pkg.get_value(p_assignment_action_id => p_run_asg_action_id
1735 ,p_defined_balance_lst => g_balance_value_tab
1736 ,p_context_lst => g_context_table
1737 ,p_output_table => l_result_table1
1738 );
1739
1740 IF pre_gre_asg_act_id IS NOT NULL
1741 THEN
1742 pay_balance_pkg.get_value(p_assignment_action_id => pre_gre_asg_act_id
1743 ,p_defined_balance_lst => g_balance_value_tab
1744 ,p_context_lst => g_context_table
1745 ,p_output_table => l_result_table2
1746 );
1747 ELSE
1748 g_result_table := l_result_table1;
1749 pay_in_utils.set_location(g_debug,'Leaving : '||l_proc,20);
1750 RETURN;
1751 END IF;
1752
1753 balance_difference(l_result_table1,l_result_table2,g_result_table);
1754
1755 pay_in_utils.set_location(g_debug,'Leaving : '||l_proc,30);
1756
1757 END get_balances;
1758
1759 --------------------------------------------------------------------------
1760 -- --
1761 -- Name : ARCHIVE_PERQUISITES --
1762 -- Type : PROCEDURE --
1763 -- Access : Public --
1764 -- Description : This procedure archives the perqusite balance --
1765 -- Parameters : --
1766 -- IN : p_run_asg_action_id NUMBER --
1767 -- p_arc_pay_action_id NUMBER --
1768 -- p_gre_id NUMBER --
1769 -- pre_gre_asg_act_id NUMBER --
1770 -- OUT : N/A --
1771 -- --
1772 -- Change History : --
1773 --------------------------------------------------------------------------
1774 -- Rev# Date Userid Description --
1775 --------------------------------------------------------------------------
1776 -- 115.0 05-Jan-2006 lnagaraj Initial Version --
1777 --------------------------------------------------------------------------
1778
1779 PROCEDURE archive_perquisites(p_run_asg_action_id IN NUMBER
1780 ,p_arc_asg_action_id IN NUMBER
1781 ,p_gre_id IN NUMBER
1782 ,pre_gre_asg_act_id IN NUMBER DEFAULT NULL
1783 )
1784 IS
1785 CURSOR c_defined_balance_id
1786 IS
1787 SELECT pdb.defined_balance_id balance_id
1788 ,DECODE(pbt.balance_name,'Monthly Furniture Cost',1,
1789 'Furniture Perquisite',2,
1793 ,pay_balance_dimensions pbd
1790 'Taxable Perquisites',3) indx
1791 ,pbt.balance_name balance_name
1792 FROM pay_balance_types pbt
1794 ,pay_defined_balances pdb
1795 WHERE pbt.balance_name IN('Monthly Furniture Cost'
1796 ,'Furniture Perquisite'
1797 ,'Taxable Perquisites'
1798 )
1799 AND pbd.dimension_name='_ASG_YTD'
1800 AND pbt.legislation_code = 'IN'
1801 AND pbd.legislation_code = 'IN'
1802 AND pbt.balance_type_id = pdb.balance_type_id
1803 AND pbd.balance_dimension_id = pdb.balance_dimension_id;
1804
1805
1806 CURSOR c_proj_defined_balance_id
1807 IS
1808 SELECT pdb.defined_balance_id balance_id
1809 ,DECODE(pbt.balance_name,'Projected Furniture Cost',1,
1810 'Projected Furniture Perquisite',2,
1811 'Taxable Perquisites for Projection',3) indx
1812 FROM pay_balance_types pbt
1813 ,pay_balance_dimensions pbd
1814 ,pay_defined_balances pdb
1815 WHERE pbt.balance_name IN('Projected Furniture Cost'
1816 ,'Projected Furniture Perquisite'
1817 ,'Taxable Perquisites for Projection'
1818 )
1819 AND pbd.dimension_name='_ASG_PTD'
1820 AND pbt.legislation_code = 'IN'
1821 AND pbd.legislation_code = 'IN'
1822 AND pbt.balance_type_id = pdb.balance_type_id
1823 AND pbd.balance_dimension_id = pdb.balance_dimension_id;
1824
1825 g_balance_value_tab pay_balance_pkg.t_balance_value_tab;
1826 g_balance_value_tab1 pay_balance_pkg.t_balance_value_tab;
1827 g_balance_value_tab2 pay_balance_pkg.t_balance_value_tab;
1828 g_bal_name_tab t_bal_name_tab;
1829 g_context_table pay_balance_pkg.t_context_tab;
1830 g_result_table pay_balance_pkg.t_detailed_bal_out_tab;
1831 g_result_table1 pay_balance_pkg.t_detailed_bal_out_tab;
1832 g_result_table2 pay_balance_pkg.t_detailed_bal_out_tab;
1833
1834 l_balance_value NUMBER;
1835 l_defined_balance_id NUMBER;
1836 l_total_value NUMBER;
1837 l_prev_gre_value NUMBER;
1838 l_action_info_id NUMBER;
1839 l_ovn NUMBER;
1840
1841 l_ser_gas_edu_med_perq NUMBER;
1842 l_ser_gas_edu_med_proj_perq NUMBER;
1843 l_travel_perq NUMBER;
1844 l_travel_proj_perq NUMBER;
1845 l_others_proj NUMBER;
1846 i NUMBER;
1847 l_others NUMBER;
1848
1849
1850 l_proc VARCHAR2(100);
1851 l_message VARCHAR2(255);
1852
1853 BEGIN
1854 g_debug := hr_utility.debug_enabled;
1855 l_proc := 'pay_in_24q_archive.archive_perquisites';
1856
1857 --- Step 1: Company Accommodation :Cost and Rent of Furniture
1858 pay_in_utils.set_location(g_debug,'Entering : '||l_proc,10);
1859
1860 if g_debug then
1861 pay_in_utils.trace('******************************','********************');
1862 pay_in_utils.trace('p_run_asg_action_id : ',p_run_asg_action_id);
1863 pay_in_utils.trace('p_arc_asg_action_id : ',p_arc_asg_action_id);
1864 pay_in_utils.trace('p_gre_id : ',p_gre_id);
1865 pay_in_utils.trace('pre_gre_asg_act_id : ',pre_gre_asg_act_id);
1866 pay_in_utils.trace('******************************','********************');
1867 end if;
1868
1869 l_defined_balance_id :=pay_in_tax_utils.get_defined_balance('Cost and Rent of Furniture','_ASG_YTD');
1870
1871 l_total_value := pay_balance_pkg.get_value(p_assignment_action_id => p_run_asg_action_id,
1872 p_defined_balance_id => l_defined_balance_id);
1873
1874 if g_debug then
1875 pay_in_utils.trace('l_total_value : ',l_total_value);
1876 end if;
1877
1878 IF pre_gre_asg_act_id IS NOT NULL THEN
1879 l_prev_gre_value := pay_balance_pkg.get_value(p_assignment_action_id => pre_gre_asg_act_id,
1880 p_defined_balance_id => l_defined_balance_id);
1881 if g_debug then
1882 pay_in_utils.trace('l_prev_gre_value : ',l_prev_gre_value);
1883 end if;
1884
1885 l_balance_value := l_total_value - l_prev_gre_value;
1886 ELSE
1887 l_balance_value := l_total_value;
1888 END IF;
1889
1890 if g_debug then
1891 pay_in_utils.trace('l_balance_value : ',l_balance_value);
1892 end if;
1893
1894 IF (l_balance_value <> 0)
1895 THEN
1896 pay_action_information_api.create_action_information
1897 (p_action_context_id => p_arc_asg_action_id
1898 ,p_action_context_type => 'AAP'
1899 ,p_action_information_category => 'IN_24Q_PERQ'
1900 ,p_source_id => p_run_asg_action_id
1901 ,p_action_information1 => 'Cost and Rent of Furniture'
1902 ,p_action_information2 => l_balance_value
1903 ,p_action_information_id => l_action_info_id
1904 ,p_object_version_number => l_ovn
1908
1905 );
1906 END IF;
1907
1909 -- Step 2: Company Accommodation :Employee Contribution
1910 pay_in_utils.set_location(g_debug,'Entering : '||l_proc,20);
1911 g_context_table.DELETE;
1912 g_balance_value_tab1.DELETE;
1913 g_result_table1.DELETE;
1914 g_context_table(1).source_text2 := 'Company Accommodation';
1915
1916
1917 get_balances(p_run_asg_action_id => p_run_asg_action_id
1918 ,pre_gre_asg_act_id => pre_gre_asg_act_id
1919 ,p_balance_name => 'Perquisite Employee Contribution'
1920 ,p_balance_dimension => '_ASG_COMP_YTD'
1921 ,g_context_table => g_context_table
1922 ,g_balance_value_tab => g_balance_value_tab1
1923 ,g_result_table => g_result_table1
1924 );
1925 l_defined_balance_id :=pay_in_tax_utils.get_defined_balance('Projected Employee Contribution for Company Accommodation','_ASG_PTD');
1926 l_balance_value := pay_balance_pkg.get_value(p_assignment_action_id => p_run_asg_action_id,
1927 p_defined_balance_id => l_defined_balance_id);
1928
1929
1930 if g_debug then
1931 pay_in_utils.trace('balance_value : ',g_result_table1(1).balance_value);
1932 pay_in_utils.trace('l_balance_value : ',l_balance_value);
1933 end if;
1934
1935 IF (g_result_table1(1).balance_value <>0 OR l_balance_value <>0 ) THEN
1936 pay_action_information_api.create_action_information
1937 (p_action_context_id => p_arc_asg_action_id
1938 ,p_action_context_type => 'AAP'
1939 ,p_action_information_category => 'IN_24Q_PERQ'
1940 ,p_source_id => p_run_asg_action_id
1941 ,p_action_information1 => 'Employee Contribution for Company Accommodation'
1942 ,p_action_information2 => g_result_table1(1).balance_value
1943 ,p_action_information3 => l_balance_value
1944 ,p_action_information_id => l_action_info_id
1945 ,p_object_version_number => l_ovn
1946 );
1947 END IF;
1948
1949 --Step 3: Furniture Perquiste and taxable perquisite - Actual
1950 g_balance_value_tab1.DELETE;
1951 g_balance_value_tab2.DELETE;
1952 g_bal_name_tab.DELETE;
1953
1954 g_result_table1.DELETE;
1955 g_result_table2.DELETE;
1956
1957 FOR c_rec IN c_defined_balance_id
1958 LOOP
1959 i :=c_rec.indx;
1960 g_balance_value_tab1(i).defined_balance_id := c_rec.balance_id;
1961 g_balance_value_tab2(i).defined_balance_id := c_rec.balance_id;
1962 g_bal_name_tab(i).balance_name := c_rec.balance_name;
1963 END LOOP;
1964
1965 pay_balance_pkg.get_value(p_assignment_action_id => p_run_asg_action_id,
1966 p_defined_balance_lst => g_balance_value_tab1);
1967
1968 IF pre_gre_asg_act_id IS NOT NULL THEN
1969 pay_balance_pkg.get_value(p_assignment_action_id => pre_gre_asg_act_id,
1970 p_defined_balance_lst => g_balance_value_tab2);
1971 FOR i in 1..3
1972 LOOP
1973 g_balance_value_tab1(i).balance_value := g_balance_value_tab1(i).balance_value - g_balance_value_tab2(i).balance_value;
1974 END LOOP;
1975 END IF;
1976
1977 --Step 4: Furniture Perquiste and taxable perquiste - Projected
1978 g_balance_value_tab.DELETE;
1979 FOR c_rec IN c_proj_defined_balance_id
1980 LOOP
1981 i :=c_rec.indx;
1982 g_balance_value_tab(i).defined_balance_id := c_rec.balance_id;
1983 END LOOP;
1984
1985 pay_balance_pkg.get_value(p_assignment_action_id => p_run_asg_action_id,
1986 p_defined_balance_lst => g_balance_value_tab);
1987
1988
1989 if g_debug then
1990 pay_in_utils.trace('balance_name : ',g_bal_name_tab(i).balance_name);
1991 pay_in_utils.trace('tabl_balance_value : ',g_balance_value_tab1(i).balance_value);
1992 pay_in_utils.trace('tab_balance_value : ',g_balance_value_tab(i).balance_value);
1993 end if;
1994
1995 FOR i IN 1..2
1996 LOOP
1997 IF (( g_balance_value_tab1(i).balance_value <> 0)
1998 OR(g_balance_value_tab(i).balance_value <> 0)
1999 )
2000 THEN
2001 pay_action_information_api.create_action_information
2002 (p_action_context_id => p_arc_asg_action_id
2003 ,p_action_context_type => 'AAP'
2004 ,p_action_information_category => 'IN_24Q_PERQ'
2005 ,p_source_id => p_run_asg_action_id
2006 ,p_action_information1 => g_bal_name_tab(i).balance_name
2007 ,p_action_information2 => g_balance_value_tab1(i).balance_value
2008 ,p_action_information3 => g_balance_value_tab(i).balance_value
2009 ,p_action_information_id => l_action_info_id
2010 ,p_object_version_number => l_ovn
2011 );
2012 END IF;
2013 END LOOP;
2014
2015 l_others := g_balance_value_tab1(3).balance_value ;
2016 l_others_proj := g_balance_value_tab(3).balance_value ;
2017
2021 g_balance_value_tab.DELETE;
2018 pay_in_utils.set_location(g_debug,'Furniture and Total Perquisites '||l_proc,40);
2019
2020 -- Step 5 - Get individual perquisite values
2022 g_context_table.DELETE;
2023 g_result_table1.delete;
2024 g_result_table2.DELETE;
2025
2026 g_context_table(1).source_text2 := 'Company Accommodation';
2027 g_context_table(2).source_text2 := 'Motor Car Perquisite';
2028 g_context_table(3).source_text2 := 'Leave Travel Concession';
2029 g_context_table(4).source_text2 := 'Free Transport';
2030 g_context_table(5).source_text2 := 'Travel / Tour / Accommodation';
2031 g_context_table(6).source_text2 := 'Domestic Servant'; --
2032 g_context_table(7).source_text2 := 'Gas / Water / Electricity'; --
2033 g_context_table(8).source_text2 := 'Free Education';--
2034 g_context_table(9).source_text2 := 'Medical';--
2035
2036 pay_in_utils.set_location(g_debug,'Entering : '||l_proc,50);
2037
2038 get_balances(p_run_asg_action_id => p_run_asg_action_id
2039 ,pre_gre_asg_act_id => pre_gre_asg_act_id
2040 ,p_balance_name => 'Taxable Perquisites'
2041 ,p_balance_dimension => '_ASG_COMP_YTD'
2042 ,g_context_table => g_context_table
2043 ,g_balance_value_tab => g_balance_value_tab
2044 ,g_result_table => g_result_table1
2045 );
2046 get_balances(p_run_asg_action_id => p_run_asg_action_id
2047 ,pre_gre_asg_act_id => NULL
2048 ,p_balance_name => 'Taxable Perquisites for Projection'
2049 ,p_balance_dimension => '_ASG_COMP_PTD'
2050 ,g_context_table => g_context_table
2051 ,g_balance_value_tab => g_balance_value_tab
2052 ,g_result_table => g_result_table2
2053 );
2054
2055 l_travel_perq := 0;
2056 l_travel_proj_perq := 0;
2057
2058 l_ser_gas_edu_med_perq := 0;
2059 l_ser_gas_edu_med_proj_perq := 0;
2060
2061 FOR i IN 3..5 LOOP
2062 l_travel_perq := l_travel_perq + g_result_table1(i).balance_value ;
2063 l_travel_proj_perq := l_travel_proj_perq + g_result_table2(i).balance_value ;
2064 END LOOP ;
2065
2066 pay_in_utils.set_location(g_debug,l_proc,55);
2067 g_context_table(3).source_text2 := 'Leave Travel Concession';
2068 g_result_table1(3).balance_value := l_travel_perq;
2069 g_result_table2(3).balance_value := l_travel_proj_perq;
2070
2071 -- Step 6: Grp under company acco., LTC, Domestic and Personal Services Perquisite and remaining perq.
2072 FOR i in 6..9 LOOP
2073 l_ser_gas_edu_med_perq := l_ser_gas_edu_med_perq + g_result_table1(i).balance_value ;
2074 l_ser_gas_edu_med_proj_perq := l_ser_gas_edu_med_proj_perq + g_result_table2(i).balance_value ;
2075 END LOOP;
2076
2077 pay_in_utils.set_location(g_debug,l_proc,60);
2078 g_context_table(4).source_text2 := 'Domestic and Personal Services Perquisite';
2079 g_result_table1(4).balance_value := l_ser_gas_edu_med_perq;
2080 g_result_table2(4).balance_value := l_ser_gas_edu_med_proj_perq;
2081
2082 FOR i in 1..4 LOOP
2083 l_others := l_others - g_result_table1(i).balance_value ;
2084 l_others_proj := l_others_proj - g_result_table2(i).balance_value ;
2085 END LOOP;
2086
2087 pay_in_utils.set_location(g_debug,l_proc,70);
2088
2089 g_context_table(5).source_text2 := 'Other Perquisites';
2090 g_result_table1(5).balance_value := l_others;
2091 g_result_table2(5).balance_value := l_others_proj;
2092
2093 pay_in_utils.set_location(g_debug,l_proc,80);
2094
2095 if g_debug then
2096 pay_in_utils.trace('source_text2 : ',g_context_table(i).source_text2);
2097 pay_in_utils.trace('tablel_balance_value : ',g_result_table1(i).balance_value);
2098 pay_in_utils.trace('table2_balance_value : ',g_result_table2(i).balance_value);
2099 end if;
2100
2101 FOR i IN 1..5
2102 LOOP
2103 IF ((g_result_table1(i).balance_value <> 0)
2104 OR(g_result_table2(i).balance_value <> 0)
2105 )
2106 THEN
2107 pay_action_information_api.create_action_information
2108 (p_action_context_id => p_arc_asg_action_id
2109 ,p_action_context_type => 'AAP'
2110 ,p_action_information_category => 'IN_24Q_PERQ'
2111 ,p_source_id => p_run_asg_action_id
2112 ,p_action_information1 => g_context_table(i).source_text2
2113 ,p_action_information2 => g_result_table1(i).balance_value
2114 ,p_action_information3 => g_result_table2(i).balance_value
2115 ,p_action_information_id => l_action_info_id
2116 ,p_object_version_number => l_ovn
2117 );
2118 END IF;
2119 END LOOP;
2120
2121 -- Step 7 - Delete PL/SQL Tables
2122 pay_in_utils.set_location(g_debug,'Deleting PL/SQL tables in : '||l_proc,90);
2123 g_balance_value_tab.DELETE;
2124 g_balance_value_tab1.DELETE;
2125 g_balance_value_tab2.DELETE;
2126 g_bal_name_tab.DELETE;
2127 g_context_table.DELETE;
2128 g_result_table.DELETE;
2129 g_result_table1.DELETE;
2130 g_result_table2.DELETE;
2131
2132 END archive_perquisites;
2136 -- Type : PROCEDURE --
2133 --------------------------------------------------------------------------
2134 -- --
2135 -- Name : archive_challan_asg --
2137 -- Access : Public --
2138 -- Description : This procedure archives the challan details for --
2139 -- each assignment giving the tax,surcharge and cess --
2140 -- details --
2141 -- Parameters : --
2142 -- IN : p_arc_pay_action_id NUMBER --
2143 -- p_person_id NUMBER --
2144 -- p_assignment_id NUMBER --
2145 -- p_gre_id NUMBER --
2146 -- p_effective_date DATE --
2147 -- IN/ OUT : p_person_table t_person_data_tab_type --
2148 -- --
2149 -- Change History : --
2150 --------------------------------------------------------------------------
2151 -- Rev# Date Userid Description --
2152 --------------------------------------------------------------------------
2153 -- 115.0 05-Jan-2006 lnagaraj Initial Version --
2154 -- 115.1 25-Sep-2007 rsaharay Modified cursors c_pos --
2155 --------------------------------------------------------------------------
2156 PROCEDURE archive_challan_asg(p_arc_asg_action_id IN NUMBER
2157 ,p_person_id IN NUMBER
2158 ,p_assignment_id IN NUMBER
2159 ,p_gre_id IN NUMBER
2160 ,p_effective_date IN DATE
2161 ,p_person_table IN OUT NOCOPY t_person_data_tab_type
2162 )
2163 IS
2164 CURSOR csr_challan_asg
2165 IS
2166 SELECT pee.element_entry_id
2167 FROM pay_element_entries_f pee
2168 WHERE pee.element_type_id = g_chln_element_id
2169 AND pee.effective_start_date <= g_fin_end_date
2170 AND pee.effective_end_date >= g_fin_start_date
2171 AND pee.assignment_id = p_assignment_id
2172 AND EXISTS (SELECT ''
2173 FROM pay_element_entry_values_f peev
2174 ,hr_organization_information hoi
2175 WHERE peev.input_value_id = g_input_table_rec(1).input_value_id
2176 AND peev.element_entry_id = pee.element_entry_id
2177 AND peev.screen_entry_value = hoi.org_information3
2178 AND hoi.organization_id = p_gre_id
2179 AND hoi.org_information1 = g_tax_year
2180 AND hoi.org_information13 = g_quarter
2181 AND hoi.org_information_context ='PER_IN_IT_CHALLAN_INFO'
2182 AND peev.effective_start_date <= g_fin_end_date
2183 AND peev.effective_end_date >= g_fin_start_date
2184 AND ROWNUM =1);
2185
2186 CURSOR csr_person_data (p_person_id NUMBER)
2187 IS
2188 SELECT asg.person_id person_id
2189 ,DECODE(pep.per_information4,NULL,DECODE(pep.per_information5,'Yes','APPLIEDFOR','PANNOTAVBL'),pep.per_information4) pan
2190 ,pep.per_information14 pan_ref_num
2191 ,hr_in_utility.per_in_full_name(pep.first_name,pep.middle_names,pep.last_name,pep.title) name
2192 FROM per_assignments_f asg
2193 ,per_people_f pep
2194 WHERE asg.assignment_id = p_assignment_id
2195 AND pep.person_id = asg.person_id
2196 AND pep.business_group_id = g_bg_id
2197 AND asg.business_group_id = g_bg_id
2198 AND p_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
2199 AND p_effective_date BETWEEN pep.effective_start_date AND pep.effective_end_date ;
2200
2201 CURSOR c_pos
2202 IS
2203 SELECT nvl(pos.name,job.name) name
2204 FROM per_all_positions pos
2205 ,per_assignments_f asg
2206 ,per_jobs job
2207 WHERE asg.position_id=pos.position_id(+)
2208 AND asg.job_id=job.job_id(+)
2209 AND asg.assignment_id = p_assignment_id
2210 AND asg.business_group_id = g_bg_id
2211 AND p_effective_date BETWEEN pos.date_effective(+) AND NVL(pos.date_end(+),TO_DATE('31-12-4712','DD-MM-YYYY'))
2212 AND p_effective_date BETWEEN job.date_from(+) AND NVL(job.date_to(+),TO_DATE('31-12-4712','DD-MM-YYYY'))
2213 AND p_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date;
2214
2215
2216 CURSOR c_aei_tax_rate(p_person_id NUMBER)
2217 IS
2218 SELECT paei.aei_information2
2219 FROM per_assignment_extra_info paei
2220 ,per_assignments_f paa
2221 WHERE paei.information_type = 'PER_IN_TAX_EXEMPTION_DF'
2222 AND paei.aei_information_category = 'PER_IN_TAX_EXEMPTION_DF'
2223 AND paei.assignment_id = paa.assignment_id
2224 AND paa.person_id = p_person_id
2225 AND paei.aei_information1 = g_tax_year
2226 AND p_effective_date BETWEEN paa.effective_start_date AND paa.effective_end_date
2227 AND ROWNUM = 1;
2228
2229
2230
2234 ,piv.display_sequence indx
2231 CURSOR csr_get_element_entry_value(p_element_entry_id NUMBER)
2232 IS
2233 SELECT peev.screen_entry_value entry_value
2235 FROM pay_element_entry_values_f peev
2236 ,pay_input_values_f piv
2237 WHERE peev.element_entry_id = p_element_entry_id
2238 AND peev.input_value_id IN(g_input_table_rec(1).input_value_id
2239 ,g_input_table_rec(2).input_value_id
2240 ,g_input_table_rec(3).input_value_id
2241 ,g_input_table_rec(4).input_value_id
2242 ,g_input_table_rec(5).input_value_id
2243 ,g_input_table_rec(6).input_value_id
2244 ,g_input_table_rec(7).input_value_id)
2245 AND peev.input_value_id = piv.input_value_id
2246 AND g_fin_end_date BETWEEN piv.effective_start_date AND piv.effective_end_date;
2247
2248
2249
2250 t_person_tab t_person_data_tab_type;
2251
2252 TYPE t_challan_entry_asg_rec IS RECORD
2253 (element_entry_id pay_element_entries_f.element_entry_id%TYPE);
2254
2255 TYPE t_challan_entry_asg_tab_type is table of t_challan_entry_asg_rec index by binary_integer;
2256 t_challan_entry_asg_tab t_challan_entry_asg_tab_type;
2257
2258 TYPE t_entry_values_rec IS RECORD
2259 (screen_entry_value pay_element_entry_values_f.screen_entry_value%TYPE);
2260
2261 TYPE t_entry_values_tab_type is table of t_entry_values_rec index by binary_integer;
2262 t_entry_values_tab t_entry_values_tab_type;
2263
2264 l_action_info_id NUMBER;
2265 l_ovn NUMBER;
2266 l_cnt NUMBER;
2267 i NUMBER;
2268 l_person_id NUMBER;
2269 l_full_name per_all_people_f.full_name%TYPE;
2270
2271 l_pan per_all_people_f.per_information14%TYPE;
2272 l_pan_ref_num per_all_people_f.per_information14%TYPE;
2273 l_tax_rate per_assignment_extra_info.aei_information2 %TYPE;
2274 l_pos per_all_positions.name%TYPE;
2275 l_proc VARCHAR2(100);
2276 l_message VARCHAR2(255);
2277
2278
2279 BEGIN
2280 g_debug := hr_utility.debug_enabled;
2281 l_proc := g_package||'archive_challan_asg';
2282
2283 pay_in_utils.set_location(g_debug,'Entering : '||l_proc,10);
2284
2285 if g_debug then
2286 pay_in_utils.trace('******************************','********************');
2287 pay_in_utils.trace('p_arc_asg_action_id : ',p_arc_asg_action_id);
2288 pay_in_utils.trace('p_person_id : ',p_person_id);
2289 pay_in_utils.trace('p_assignment_id : ',p_assignment_id);
2290 pay_in_utils.trace('p_gre_id : ',p_gre_id);
2291 pay_in_utils.trace('p_effective_date : ',p_effective_date);
2292 pay_in_utils.trace('******************************','********************');
2293 end if;
2294
2295 t_challan_entry_asg_tab.DELETE;
2296
2297 -- Gets element entries for this assignment for all challans in this GRE in the given assessment year-quarter
2298
2299
2300 OPEN csr_challan_asg;
2301 FETCH csr_challan_asg BULK COLLECT INTO t_challan_entry_asg_tab;
2302 CLOSE csr_challan_asg;
2303
2304 l_cnt := t_challan_entry_asg_tab.COUNT;
2305
2306 IF l_cnt >0 THEN
2307 IF p_person_table.EXISTS(1) THEN
2308 NULL;
2309 ELSE
2310 OPEN csr_person_data(p_person_id);
2311 FETCH csr_person_data INTO l_person_id,l_pan, l_pan_ref_num, l_full_name;
2312 CLOSE csr_person_data;
2313
2314 OPEN c_pos;
2315 FETCH c_pos INTO l_pos;
2316 CLOSE c_pos;
2317
2318 OPEN c_aei_tax_rate(l_person_id);
2319 FETCH c_aei_tax_rate INTO l_tax_rate;
2320 CLOSE c_aei_tax_rate;
2321
2322 p_person_table(1).person_id := l_person_id;
2323 p_person_table(1).pan_number := l_pan;
2324 p_person_table(1).pan_ref_number := l_pan_ref_num;
2325 p_person_table(1).full_name := l_full_name;
2326 p_person_table(1).tax_rate := l_tax_rate;
2327 p_person_table(1).position := l_pos;
2328 END IF;
2329
2330
2331 --
2332 FOR i in t_challan_entry_asg_tab.FIRST .. t_challan_entry_asg_tab.LAST
2333 LOOP
2334 IF t_challan_entry_asg_tab.EXISTS(i) THEN
2335 FOR j in csr_get_element_entry_value(t_challan_entry_asg_tab(i).element_entry_id) LOOP
2336 t_entry_values_tab(j.indx).screen_entry_value := j.entry_value;
2337 END LOOP;
2338
2339 if g_debug then
2340 pay_in_utils.trace('element_entry_id : ',t_challan_entry_asg_tab(i).element_entry_id);
2341 pay_in_utils.trace('screen_entry_value1 : ',t_entry_values_tab(1).screen_entry_value);
2342 pay_in_utils.trace('person_id : ',p_person_table(1).person_id);
2343 pay_in_utils.trace('screen_entry_value2 : ',t_entry_values_tab(2).screen_entry_value);
2344 pay_in_utils.trace('screen_entry_value3 : ',t_entry_values_tab(3).screen_entry_value);
2345 pay_in_utils.trace('screen_entry_value4 : ',t_entry_values_tab(4).screen_entry_value);
2346 pay_in_utils.trace('screen_entry_value5 : ',t_entry_values_tab(5).screen_entry_value);
2347 pay_in_utils.trace('screen_entry_value6 : ',t_entry_values_tab(6).screen_entry_value);
2351 pay_in_utils.trace('pan_ref_number : ',p_person_table(1).pan_ref_number);
2348 pay_in_utils.trace('screen_entry_value7 : ',t_entry_values_tab(7).screen_entry_value);
2349 pay_in_utils.trace('screen_entry_value1 : ',t_entry_values_tab(1).screen_entry_value);
2350 pay_in_utils.trace('pan_number : ',p_person_table(1).pan_number);
2352 pay_in_utils.trace('full_name : ',p_person_table(1).full_name);
2353 pay_in_utils.trace('tax_rate : ',p_person_table(1).tax_rate);
2354 end if;
2355
2356 pay_action_information_api.create_action_information
2357 (p_action_context_id => p_arc_asg_action_id
2358 ,p_action_context_type => 'AAP'
2359 ,p_action_information_category => 'IN_24Q_DEDUCTEE'
2360 ,p_assignment_id => p_assignment_id
2361 ,p_source_id => t_challan_entry_asg_tab(i).element_entry_id
2362 ,p_action_information1 => t_entry_values_tab(1).screen_entry_value
2363 ,p_action_information2 => p_person_table(1).person_id
2364 ,p_action_information3 => p_gre_id
2365 ,p_action_information4 => t_entry_values_tab(2).screen_entry_value
2366 ,p_action_information5 => t_entry_values_tab(3).screen_entry_value
2367 ,p_action_information6 => t_entry_values_tab(4).screen_entry_value
2368 ,p_action_information7 => t_entry_values_tab(5).screen_entry_value
2369 ,p_action_information8 => t_entry_values_tab(6).screen_entry_value
2370 ,p_action_information9 => t_entry_values_tab(7).screen_entry_value
2371 ,p_action_information10 => p_person_table(1).pan_number
2372 ,p_action_information11 => p_person_table(1).pan_ref_number
2373 ,p_action_information12 => p_person_table(1).full_name
2374 ,p_action_information13 => p_person_table(1).tax_rate
2375 ,p_action_information_id => l_action_info_id
2376 ,p_object_version_number => l_ovn
2377 );
2378 END IF;
2379
2380 END LOOP;
2381 END IF;
2382
2383 pay_in_utils.set_location(g_debug,'Leaving : '||l_proc,20);
2384
2385 END archive_challan_asg;
2386 --------------------------------------------------------------------------
2387 -- --
2388 -- Name : ARCHIVE_CODE --
2389 -- Type : PROCEDURE --
2390 -- Access : Public --
2391 -- Description : Procedure to call the internal procedures to --
2392 -- actually archive the data. --
2393 -- Parameters : --
2394 -- IN : p_assignment_action_id NUMBER --
2395 -- p_effective_date DATE --
2396 -- --
2397 -- OUT : N/A --
2398 -- --
2399 -- Change History : --
2400 --------------------------------------------------------------------------
2401 -- Rev# Date Userid Description --
2402 --------------------------------------------------------------------------
2403 -- 115.0 05-Jan-2006 lnagaraj Initial Version --
2404 -- 115.1 26-Jun-2006 aaagarwa Modifed get_eoy_archival_details --
2405 --------------------------------------------------------------------------
2406 PROCEDURE archive_code ( p_assignment_action_id IN NUMBER
2407 ,p_effective_date IN DATE
2408 )
2409 IS
2410 CURSOR get_assignment_pact_id
2411 IS
2412 SELECT paa.assignment_id
2413 ,paa.payroll_action_id
2414 ,paf.person_id
2415 FROM pay_assignment_actions paa
2416 ,per_all_assignments_f paf
2417 WHERE paa.assignment_action_id = p_assignment_action_id
2418 AND paa.assignment_id = paf.assignment_id
2419 AND ROWNUM =1;
2420
2421 CURSOR c_get_distinct_gre(p_assignment_id NUMBER)
2422 IS
2423 SELECT DISTINCT(hscl.segment1) gre
2424 FROM per_all_assignments_f paf
2425 ,hr_soft_coding_keyflex hscl
2426 WHERE hscl.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
2427 AND paf.assignment_id =paf.assignment_id
2428 AND paf.assignment_id = p_assignment_id
2429 AND ( paf.effective_start_date BETWEEN g_fin_start_date AND g_end_date
2430 OR g_fin_start_date BETWEEN paf.effective_start_date AND paf.effective_end_date
2431 )
2432 AND hscl.segment1 LIKE g_gre_id;
2433
2434 CURSOR c_gre_records
2435 IS
2436 SELECT GREATEST(asg.effective_start_date,g_fin_start_date) start_date
2437 ,LEAST(asg.effective_end_date,g_fin_end_date) end_date
2438 ,scl.segment1
2439 FROM per_assignments_f asg
2443 AND paa.assignment_action_id = p_assignment_action_id
2440 ,hr_soft_coding_keyflex scl
2441 ,pay_assignment_actions paa
2442 WHERE asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
2444 AND asg.assignment_id = paa.assignment_id
2445 AND scl.segment1 LIKE g_gre_id
2446 AND ( asg.effective_start_date BETWEEN g_fin_start_date AND g_end_date
2447 OR g_fin_start_date BETWEEN asg.effective_start_date AND asg.effective_end_date
2448 )
2449 AND asg.business_group_id = g_bg_id
2450 ORDER BY 1 ;
2451
2452 CURSOR csr_payroll_id(p_assignment_id NUMBER,p_date DATE)
2453 IS
2454 SELECT paf.payroll_id
2455 FROM per_all_assignments_f paf
2456 WHERE paf.assignment_id =p_assignment_id
2457 AND p_date BETWEEN paf.effective_start_date AND paf.effective_end_date;
2458
2459 CURSOR get_eoy_archival_details(p_start_date DATE
2460 ,p_end_date DATE
2461 ,p_tax_unit_id NUMBER
2462 ,p_assignment_id NUMBER
2463 )
2464 IS
2465 SELECT TO_NUMBER(SUBSTR(MAX(LPAD(paa.action_sequence,15,'0')||paa.assignment_action_id),16)) run_asg_action_id
2466 FROM pay_assignment_actions paa
2467 ,pay_payroll_actions ppa
2468 ,per_assignments_f paf
2469 WHERE paf.assignment_id = paa.assignment_id
2470 AND paf.assignment_id = p_assignment_id
2471 AND paa.tax_unit_id = p_tax_unit_id
2472 AND paa.payroll_action_id = ppa.payroll_action_id
2473 AND ppa.action_type IN('R','Q','I','B')
2474 AND ppa.payroll_id = paf.payroll_id
2475 AND ppa.action_status ='C'
2476 AND ppa.effective_date between p_start_date and p_end_date
2477 AND paa.source_action_id IS NULL
2478 AND (1 = DECODE(ppa.action_type,'I',1,0)
2479 OR EXISTS (SELECT ''
2480 FROM pay_action_interlocks intk,
2481 pay_assignment_actions paa1,
2482 pay_payroll_actions ppa1
2483 WHERE intk.locked_action_id = paa.assignment_Action_id
2484 AND intk.locking_action_id = paa1.assignment_action_id
2485 AND paa1.payroll_action_id =ppa1.payroll_action_id
2486 AND paa1.assignment_id = p_assignment_id
2487 AND ppa1.action_type in('P','U')
2488 AND ppa.action_type in('R','Q','B')
2489 AND ppa1.action_status ='C'
2490 AND ppa1.effective_date BETWEEN p_start_date and p_end_date
2491 AND ROWNUM =1 ));
2492
2493 CURSOR c_get_date_earned(l_run_assact NUMBER)
2494 IS
2495 SELECT ppa.date_earned run_date
2496 ,ppa.payroll_id
2497 FROM pay_payroll_actions ppa,
2498 pay_assignment_actions paa
2499 WHERE paa.payroll_action_id = ppa.payroll_action_id
2500 AND paa.assignment_action_id = l_run_assact;
2501
2502 CURSOR c_pay_action_level_check(p_payroll_action_id NUMBER
2503 ,p_gre_id NUMBER)
2504 IS
2505 SELECT 1
2506 FROM pay_action_information pai,
2507 pay_assignment_actions paa
2508 WHERE pai.action_information_category = 'IN_24Q_ORG'
2509 AND pai.action_context_type = 'PA'
2510 AND pai.action_context_id = p_payroll_action_id
2511 AND pai.action_information1 = p_gre_id
2512 AND ROWNUM =1;
2513
2514 CURSOR c_termination_check(p_assignment_id NUMBER)
2515 IS
2516 SELECT NVL(pos.actual_termination_date,(fnd_date.string_to_date('31-12-4712','DD-MM-YYYY')))
2517 FROM per_all_assignments_f asg
2518 ,per_periods_of_service pos
2519 WHERE asg.person_id = pos.person_id
2520 AND asg.assignment_id = p_assignment_id
2521 AND asg.business_group_id = pos.business_group_id
2522 AND asg.business_group_id = g_bg_id
2523 AND NVL(pos.actual_termination_date,(to_date('31-12-4712','DD-MM-YYYY')))
2524 BETWEEN asg.effective_start_date AND asg.effective_end_date
2525 ORDER BY 1 DESC;
2526
2527
2528 l_proc VARCHAR2(100);
2529 l_message VARCHAR2(255);
2530 l_assignment_id NUMBER;
2531 l_run_asg_action_id NUMBER;
2532 l_person_id NUMBER;
2533 l_run_pay_action_id NUMBER;
2534 l_run_effective_date DATE;
2535 l_run_date_earned VARCHAR2(30);
2536 l_pre_asg_action_id NUMBER;
2537 l_source_id NUMBER;
2538 l_arc_pay_action_id NUMBER;
2539 l_check NUMBER;
2540 l_previous_gre_asg_action_id NUMBER;
2541 l_count NUMBER;
2542 l_periods NUMBER;
2543 l_payroll_id NUMBER;
2544 l_total_pay_period NUMBER;
2545 l_current_pay_period NUMBER;
2546 p_rem_pay_period NUMBER;
2547 p_arc_asg_action_id NUMBER;
2548 l_actual_term_date DATE;
2549 p_person_data t_person_data_tab_type;
2550
2551
2552 BEGIN
2553
2554
2555 g_debug := hr_utility.debug_enabled;
2556 l_proc := g_package||'archive_code';
2560 pay_in_utils.trace('******************************','********************');
2557 pay_in_utils.set_location(g_debug,'Entering : '||l_proc||p_assignment_action_id,10);
2558
2559 if g_debug then
2561 pay_in_utils.trace('p_assignment_action_id : ',p_assignment_action_id);
2562 pay_in_utils.trace('p_effective_date : ',p_effective_date);
2563 pay_in_utils.trace('******************************','********************');
2564 end if;
2565
2566 --
2567 l_count := 1;
2568 g_asg_tab.DELETE;
2569 p_person_data.DELETE;
2570 --
2571 OPEN get_assignment_pact_id;
2572 FETCH get_assignment_pact_id INTO l_assignment_id ,l_arc_pay_action_id,l_person_id;
2573 CLOSE get_assignment_pact_id;
2574
2575 OPEN c_termination_check(l_assignment_id);
2576 FETCH c_termination_check INTO l_actual_term_date;
2577 CLOSE c_termination_check;
2578
2579 if g_debug then
2580 pay_in_utils.trace('l_assignment_id : ',l_assignment_id);
2581 pay_in_utils.trace('l_arc_pay_action_id : ',l_arc_pay_action_id);
2582 pay_in_utils.trace('l_person_id : ',l_person_id);
2583 pay_in_utils.trace('l_actual_term_date : ',l_actual_term_date);
2584 end if;
2585
2586 pay_in_utils.set_location(g_debug,'Entering : '||l_assignment_id,11);
2587 FOR c_gre_rec IN c_get_distinct_gre(l_assignment_id)
2588 LOOP
2589 if g_debug then
2590 pay_in_utils.trace('c_gre_rec.gre : ',c_gre_rec.gre);
2591 pay_in_utils.trace('g_session_date : ',g_session_date);
2592 end if;
2593
2594 archive_challan_asg( p_arc_asg_action_id => p_assignment_action_id
2595 ,p_person_id => l_person_id
2596 ,p_assignment_id => l_assignment_id
2597 ,p_gre_id => c_gre_rec.gre
2598 ,p_effective_date => LEAST(g_session_date,l_actual_term_date)
2599 ,p_person_table => p_person_data
2600 );
2601 END LOOP;
2602
2603 -- Get all records from financial year start till current quarter end to find out
2604 -- previous GRE assignment_action_id and remaining pay periods
2605 pay_in_utils.set_location(g_debug,'Entering : '||l_assignment_id,12);
2606 FOR c_rec IN c_gre_records
2607 LOOP
2608 IF ((l_count <>1)
2609 AND
2610 (g_asg_tab(l_count-1).gre_id = c_rec.segment1)
2611 AND
2612 (g_asg_tab(l_count-1).end_date + 1 = c_rec.start_date)
2613 )
2614 THEN
2615 g_asg_tab(l_count-1).end_date := c_rec.end_date;
2616 l_count := l_count -1;
2617 ELSE
2618 g_asg_tab(l_count).gre_id := c_rec.segment1;
2619 g_asg_tab(l_count).start_date := c_rec.start_date;
2620 g_asg_tab(l_count).end_date := c_rec.end_date;
2621 END IF;
2622 l_count := l_count + 1;
2623 END LOOP;
2624
2625 pay_in_utils.set_location(g_debug,'l_count : '||l_count,20);
2626
2627 /* g_asg_tab.start/end date will contain the actual start/end of asg in a GRE or the the financial year .
2628 We need to change it to quarter date*/
2629
2630 FOR i IN 1..l_count-1
2631 LOOP
2632 --Archive only if it is a candidate for reporting in the specified quarter
2633 IF (g_start_date <= g_asg_tab(i).end_date AND
2634 g_end_date >= g_asg_tab(i).start_date) THEN
2635 pay_in_utils.set_location(g_debug,'l_assignment_id : '||l_assignment_id||' ' ||g_asg_tab(i).start_date||' ' ||g_asg_tab(i).end_date||' ' ||g_asg_tab(i).gre_id,30);
2636
2637 -- Get assignment action id corresponding to the maximum action sequence record
2638 OPEN get_eoy_archival_details(GREATEST(g_asg_tab(i).start_date,g_start_date)
2639 ,LEAST(g_asg_tab(i).end_date,g_end_date)
2640 ,g_asg_tab(i).gre_id
2641 ,l_assignment_id
2642 );
2643 FETCH get_eoy_archival_details INTO l_run_asg_action_id;
2644 CLOSE get_eoy_archival_details;
2645
2646 IF l_run_asg_action_id IS NOT NULL THEN
2647 OPEN c_get_date_earned(l_run_asg_action_id);
2648 FETCH c_get_date_earned INTO l_run_date_earned,l_payroll_id;
2649 CLOSE c_get_date_earned;
2650
2651 -- Get remaining pay periods
2652 OPEN csr_payroll_id(l_assignment_id,l_run_date_earned);
2653 FETCH csr_payroll_id INTO l_payroll_id;
2654 CLOSE csr_payroll_id;
2655
2656 l_total_pay_period := pay_in_tax_utils.get_period_number(l_payroll_id,LEAST(l_actual_term_date,g_asg_tab(i).end_date));
2657 l_current_pay_period := pay_in_tax_utils.get_period_number(l_payroll_id,l_run_date_earned);
2658 p_rem_pay_period := GREATEST((l_total_pay_period - l_current_pay_period),0);
2659
2660 -- Get Previous GRE's max assignment action id
2661 l_previous_gre_asg_action_id := NULL;
2662 IF (i > 1)
2663 THEN
2664 FOR c_rec IN get_eoy_archival_details(g_asg_tab(i-1).start_date,g_asg_tab(i-1).end_date,g_asg_tab(i-1).gre_id,l_assignment_id)
2665 LOOP
2666 l_previous_gre_asg_action_id := c_rec.run_asg_action_id;
2667 EXIT;
2668 END LOOP;
2669 END IF;
2673 -- archive_person_data uses p_person_data if available,else it would fetch the values .
2670
2671 -- Person Data as on actual termination date/session date
2672 -- Find p_person_data in archive_challan_asg
2674 archive_person_data(p_run_asg_action_id => l_run_asg_action_id
2675 ,p_arc_asg_action_id => p_assignment_action_id
2676 ,p_assignment_id => l_assignment_id
2677 ,p_gre_id => g_asg_tab(i).gre_id
2678 ,p_effective_start_date => g_asg_tab(i).start_date
2679 ,p_effective_end_date => g_asg_tab(i).end_date
2680 ,p_effective_date => LEAST(l_actual_term_date,g_session_date)
2681 ,p_termination_date => l_actual_term_date
2682 ,p_person_table => p_person_data);
2683
2684 archive_via_details(p_run_asg_action_id => l_run_asg_action_id
2685 ,p_arc_asg_action_id => p_assignment_action_id
2686 ,p_gre_id => g_asg_tab(i).gre_id
2687 ,p_assignment_id => l_assignment_id
2688 );
2689
2690 archive_asg_salary(p_run_asg_action_id => l_run_asg_action_id
2691 ,p_arc_asg_action_id => p_assignment_action_id
2692 ,p_balance_periods => p_rem_pay_period
2693 ,p_gre_id => g_asg_tab(i).gre_id
2694 ,pre_gre_asg_act_id => l_previous_gre_asg_action_id
2695 );
2696
2697 archive_perquisites(p_run_asg_action_id => l_run_asg_action_id
2698 ,p_arc_asg_action_id => p_assignment_action_id
2699 ,p_gre_id => g_asg_tab(i).gre_id
2700 ,pre_gre_asg_act_id => l_previous_gre_asg_action_id
2701 );
2702
2703 OPEN c_pay_action_level_check(l_arc_pay_action_id,g_asg_tab(i).gre_id);
2704 FETCH c_pay_action_level_check INTO l_check;
2705 CLOSE c_pay_action_level_check;
2706
2707 IF l_check IS NULL
2708 THEN
2709 archive_challan_data(p_arc_pay_action_id => l_arc_pay_action_id
2710 ,p_gre_id => g_asg_tab(i).gre_id
2711 );
2712
2713 archive_org_data(p_arc_pay_action_id => l_arc_pay_action_id
2714 ,p_gre_id => g_asg_tab(i).gre_id
2715 ,p_effective_date => g_session_date
2716 );
2717
2718 END IF;
2719 END IF; -- RUN Assact is not null
2720 END IF; -- End of Archive
2721 END LOOP;
2722
2723 pay_in_utils.set_location(g_debug,'Leaving : '||l_proc,40);
2724
2725 EXCEPTION
2726 WHEN OTHERS THEN
2727 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_proc, 'SQLERRMC:'||sqlerrm);
2728 pay_in_utils.set_location(g_debug,'Leaving : '||l_proc, 50);
2729 pay_in_utils.trace(l_message,l_proc);
2730 RAISE;
2731 END archive_code;
2732
2733 --------------------------------------------------------------------------
2734 -- --
2735 -- Name : DEINITIALIZATION_CODE --
2736 -- Type : PROCEDURE --
2737 -- Access : Public --
2738 -- Description : This procedure is used to update the sorting index --
2739 -- for deductee records --
2740 -- Parameters : --
2741 -- IN : p_payroll_action_id NUMBER --
2742 -- OUT : N/A --
2743 -- --
2744 -- Change History : --
2745 --------------------------------------------------------------------------
2746 -- Rev# Date Userid Description --
2747 --------------------------------------------------------------------------
2748 -- 115.0 05-Mar-2006 abhjain Initial Version --
2749 -- 115.1 07-Feb-2007 rpalli Modified to archive salary details --
2750 -- seq number --
2751 --------------------------------------------------------------------------
2752 PROCEDURE deinitialization_code (p_payroll_action_id IN NUMBER)
2753 IS
2754
2755 l_index NUMBER;
2756 l_proc VARCHAR2(100) ;
2757 l_message VARCHAR2(255);
2758
2759 CURSOR cur_challan_recs
2760 IS
2761 SELECT DISTINCT action_information1 challan_no
2762 ,action_information3 gre_id
2763 FROM pay_action_information
2764 WHERE action_information_category = 'IN_24Q_DEDUCTEE'
2765 AND action_context_type = 'AAP'
2766 AND action_context_id IN (SELECT assignment_action_id
2767 FROM pay_assignment_actions
2768 WHERE payroll_action_id = p_payroll_action_id)
2769 ORDER BY action_information3;
2770
2771
2772 CURSOR cur_deductee_recs(p_challan VARCHAR2, p_gre_id VARCHAR2)
2773 IS
2774 SELECT DISTINCT action_information2
2775 , action_information4
2776 , action_information_id
2777 , object_version_number
2778 FROM pay_action_information
2779 WHERE action_information_category = 'IN_24Q_DEDUCTEE'
2780 AND action_context_type = 'AAP'
2781 AND action_information3 = p_gre_id
2782 AND action_information1 = p_challan
2783 AND action_context_id IN (SELECT assignment_action_id
2784 FROM pay_assignment_actions
2785 WHERE payroll_action_id = p_payroll_action_id)
2786 ORDER BY action_information2
2787 , action_information4;
2788
2789
2790 CURSOR cur_salary_recs
2791 IS
2792 SELECT DISTINCT action_information3 gre_id
2793 FROM pay_action_information
2794 WHERE action_information_category = 'IN_24Q_PERSON'
2795 AND action_context_type = 'AAP'
2796 AND action_context_id IN (SELECT assignment_action_id
2797 FROM pay_assignment_actions
2798 WHERE payroll_action_id = p_payroll_action_id)
2799 ORDER BY action_information3;
2800
2801
2802 CURSOR cur_person_recs( p_gre_id VARCHAR2)
2803 IS
2804 SELECT DISTINCT action_information1 person_id
2805 , source_id
2806 , action_information_id
2807 , object_version_number
2808 FROM pay_action_information
2809 WHERE action_information_category = 'IN_24Q_PERSON'
2810 AND action_context_type = 'AAP'
2811 AND action_information3 = p_gre_id
2812 AND action_context_id IN (SELECT assignment_action_id
2813 FROM pay_assignment_actions
2814 WHERE payroll_action_id = p_payroll_action_id)
2815 ORDER BY LENGTH(action_information1)
2816 ,action_information1
2817 ,source_id;
2818
2819 BEGIN
2820
2821 g_debug := hr_utility.debug_enabled;
2822 l_proc := g_package || 'deinitialization_code';
2823
2824 pay_in_utils.set_location(g_debug,'Entering : '||l_proc,10);
2825
2826 FOR c_challan_rec IN cur_challan_recs
2827 LOOP
2828 l_index := 0;
2829 FOR cur_rec IN cur_deductee_recs(c_challan_rec.challan_no ,c_challan_rec.gre_id)
2830 LOOP
2831 l_index := l_index + 1;
2832 pay_action_information_api.update_action_information
2833 (p_validate => FALSE
2834 ,p_action_information_id => cur_rec.action_information_id
2835 ,p_object_version_number => cur_rec.object_version_number
2836 ,p_action_information25 => l_index
2837 );
2838 END LOOP;
2839 END LOOP;
2840
2841 pay_in_utils.set_location(g_debug,'Entering : '||l_proc,20);
2842
2843 FOR c_salary_rec IN cur_salary_recs
2844 LOOP
2845 l_index := 0;
2846 FOR cur_rec IN cur_person_recs(c_salary_rec.gre_id)
2847 LOOP
2848 l_index := l_index + 1;
2849 pay_action_information_api.update_action_information
2850 (p_validate => FALSE
2851 ,p_action_information_id => cur_rec.action_information_id
2852 ,p_object_version_number => cur_rec.object_version_number
2853 ,p_action_information11 => l_index
2854 );
2855 END LOOP;
2856 END LOOP;
2857
2858 pay_in_utils.set_location(g_debug,'Leaving : '||l_proc,50);
2859 EXCEPTION
2860 WHEN OTHERS THEN
2861 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_proc, 'SQLERRMC:'||sqlerrm);
2862 pay_in_utils.set_location(g_debug,'Leaving : '||l_proc,70);
2863 pay_in_utils.trace(l_message,l_proc);
2864 RAISE;
2865 END deinitialization_code;
2866
2867 END pay_in_24q_archive;