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