[Home] [Help]
PACKAGE BODY: APPS.PAY_IN_PF_ARCHIVE
Source
1 PACKAGE BODY pay_in_pf_archive AS
2 /* $Header: pyinmpfa.pkb 120.4 2010/05/24 10:25:33 mdubasi noship $ */
3
4 ----------------------------------------------------------------------+
5 -- This is a global variable used to store Archive assignment action id
6 ----------------------------------------------------------------------+
7
8 g_archive_pact NUMBER;
9 g_package CONSTANT VARCHAR2(100) := 'pay_in_pf_archive.';
10 g_debug BOOLEAN;
11
12
13 g_year VARCHAR2(50) ;
14 g_challan_year VARCHAR2(50) ;
15 g_month VARCHAR2(50) ;
16 g_challan_mth VARCHAR2(50) ;
17 g_business_no VARCHAR2(50);
18 g_return_type VARCHAR2(50) ;
19 g_arc_ref_no VARCHAR2(50);
20 g_bg_id VARCHAR2(50);
21 g_start_date DATE ;
22 g_end_date DATE ;
23
24
25
26 --------------------------------------------------------------------------
27 -- --
28 -- Name : RANGE_CODE --
29 -- Type : PROCEDURE --
30 -- Access : Public --
31 -- Description : This procedure returns a sql string to select a --
32 -- range of assignments eligible for archival. --
33 -- --
34 -- Parameters : --
35 -- IN : p_payroll_action_id NUMBER --
36 -- OUT : p_sql VARCHAR2 --
37 -- --
38 -- Change History : --
39 --------------------------------------------------------------------------
40 -- Rev# Date Userid Description --
41 --------------------------------------------------------------------------
42 -- 115.0 01-Aug-2007 rsaharay Initial Version --
43 --------------------------------------------------------------------------
44 PROCEDURE range_code(p_payroll_action_id IN NUMBER
45 ,p_sql OUT NOCOPY VARCHAR2
46 )
47 IS
48 --
49 l_procedure VARCHAR2(100);
50 l_message VARCHAR2(250);
51 --
52 BEGIN
53 --
54
55 g_debug := hr_utility.debug_enabled;
56 l_procedure := g_package || '.range_code';
57
58 pay_in_utils.set_location(g_debug,'Entering : '||l_procedure,10);
59
60 -- Call core package to return SQL string to SELECT a range
61 -- of assignments eligible for archival
62 --
63 pay_core_payslip_utils.range_cursor(p_payroll_action_id
64 ,p_sql);
65
66 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure,20);
67
68
69 --
70 EXCEPTION
71 WHEN OTHERS THEN
72 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR',
73 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
74 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 150);
75 RAISE ;
76 --
77 END range_code;
78
79 --------------------------------------------------------------------------
80 -- --
81 -- Name : GET_PARAMETERS --
82 -- Type : PROCEDURE --
83 -- Access : Public --
84 -- Description : This procedure determines the globals applicable --
85 -- through out the tenure of the process --
86 -- Parameters : --
87 -- IN : p_payroll_action_id NUMBER --
88 -- IN : p_token_name VARCHAR2 --
89 -- OUT : p_token_value VARCHAR2 --
90 -- --
91 -- Change History : --
92 --------------------------------------------------------------------------
93 -- Rev# Date Userid Description --
94 --------------------------------------------------------------------------
95 -- 115.0 01-Aug-2007 rsaharay Initial Version --
96 --------------------------------------------------------------------------
97
98 PROCEDURE get_parameters(p_payroll_action_id IN NUMBER,
99 p_token_name IN VARCHAR2,
100 p_token_value OUT NOCOPY VARCHAR2) IS
101
102 CURSOR csr_parameter_info(p_pact_id NUMBER,
103 p_token CHAR) IS
104 SELECT SUBSTR(legislative_parameters||' ',
105 INSTR(legislative_parameters||' ',p_token||'=')+(LENGTH(p_token||'=')),
106 INSTR(legislative_parameters||' ',' ',
107 INSTR(legislative_parameters||' ',p_token||'='))
108 - (INSTR(legislative_parameters||' ',p_token||'=')+LENGTH(p_token||'='))),
109 business_group_id
110 FROM pay_payroll_actions
111 WHERE payroll_action_id = p_pact_id;
112
113 l_business_group_id VARCHAR2(20);
114 l_token_value VARCHAR2(50);
115
116 l_procedure VARCHAR2(50);
117
118 BEGIN
119
120 l_procedure := g_package || 'get_parameters';
121
122 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
123
124
125 OPEN csr_parameter_info(p_payroll_action_id,
126 p_token_name);
127 FETCH csr_parameter_info INTO l_token_value,
128 l_business_group_id;
129 CLOSE csr_parameter_info;
130
131 IF p_token_name = 'BG_ID'
132 THEN
133 p_token_value := l_business_group_id;
134 ELSE
135 p_token_value := l_token_value;
136 END IF;
137
138 IF g_debug THEN
139 pay_in_utils.trace('Token Name ',p_token_name);
140 pay_in_utils.trace('Token Value ',p_token_value);
141 END IF;
142
143 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 20);
144
145
146 END get_parameters;
147
148
149 --------------------------------------------------------------------------
150 -- --
151 -- Name : INITIALIZATION_CODE --
152 -- Type : PROCEDURE --
153 -- Access : Public --
154 -- Description : This procedure is used to set global contexts. --
155 -- --
156 -- Parameters : --
157 -- IN : p_payroll_action_id NUMBER --
158 -- OUT : N/A --
159 -- --
160 -- Change History : --
161 --------------------------------------------------------------------------
162 -- Rev# Date Userid Description --
163 --------------------------------------------------------------------------
164 -- 115.0 01-Aug-2007 rsaharay Initial Version --
165 -- 115.1 24-Oct-2007 rsaharay Modified for Currency Period --
166 -- 115.2 08-Jan-2008 mdubasi Modified the cursor csr_arch_ref_no --
167 --------------------------------------------------------------------------
168 PROCEDURE initialization_code (
169 p_payroll_action_id IN NUMBER
170 )
171 IS
172 --
173 l_procedure VARCHAR2(100) ;
174 l_message VARCHAR2(255);
175 l_temp_month NUMBER ;
176
177 CURSOR csr_arch_ref_no
178 IS
179 SELECT 1
180 FROM pay_action_information pai
181 ,pay_payroll_actions ppa
182 WHERE pai.action_information_category = 'IN_PF_BUSINESS_NUMBER'
183 AND pai.action_context_type = 'PA'
184 AND pai.action_information2 = g_arc_ref_no
185 AND pai.action_context_id = ppa.payroll_action_id
186 AND ppa.action_type = 'X'
187 AND ppa.action_status = 'C'
188 AND ppa.payroll_action_id <> p_payroll_action_id
189 AND ppa.report_type ='IN_PF_ARCHIVE'
190 AND ppa.business_group_id = g_bg_id;
191
192 l_token_name pay_in_utils.char_tab_type;
193 l_token_value pay_in_utils.char_tab_type;
194 l_arch_ref_no_check NUMBER ;
195 E_NON_UNIQUE_ARCH_REF_NO EXCEPTION;
196 --
197 BEGIN
198 --
199 l_procedure := g_package || 'initialization_code';
200
201 g_debug := hr_utility.debug_enabled;
202 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
203
204 g_archive_pact := p_payroll_action_id;
205
206 get_parameters(p_payroll_action_id,'YR',g_year);
207 get_parameters(p_payroll_action_id,'MTH',g_month);
208 get_parameters(p_payroll_action_id,'PF',g_business_no);
209 get_parameters(p_payroll_action_id,'RT',g_return_type);
210 get_parameters(p_payroll_action_id,'REF',g_arc_ref_no);
211 get_parameters(p_payroll_action_id,'BG_ID',g_bg_id);
212
213 g_year := TRIM(g_year);
214 g_month := TRIM(g_month);
215 g_return_type := TRIM(g_return_type);
216 g_arc_ref_no := TRIM(g_arc_ref_no);
217 g_business_no := TRIM(g_business_no);
218
219
220
221 g_challan_mth := g_month ;
222 g_challan_year := g_year ;
223
224
225
226
227
228 g_month := TO_NUMBER(g_month) + 3 ;
229 g_year := SUBSTR(g_year,1,4);
230 IF g_month > 12 THEN
231 g_month := g_month - 12 ;
232 g_year := TO_NUMBER(g_year) + 1;
233 END IF ;
234
235 IF g_month = 3 THEN
236 g_year := g_year - 1;
237 END IF ;
238
239 g_start_date := TO_DATE(('01/'||SUBSTR(g_month,1,2)||'/'|| SUBSTR(g_year,1,4)),'DD/MM/YYYY');
240 g_end_date := LAST_DAY(g_start_date);
241
242
243 l_arch_ref_no_check := 0;
244 OPEN csr_arch_ref_no;
245 FETCH csr_arch_ref_no INTO l_arch_ref_no_check;
246 CLOSE csr_arch_ref_no;
247 IF l_arch_ref_no_check = 1 THEN
248 l_token_name(1) := 'NUMBER_CATEGORY';
249 l_token_value(1) := hr_general.decode_lookup('IN_MESSAGE_TOKENS','ARCH_REF_NUM');--'Archive Reference Number';
250 RAISE E_NON_UNIQUE_ARCH_REF_NO;
251 END IF;
252
253 pay_in_utils.set_location(g_debug,'g_year : '||g_year, 5);
254 pay_in_utils.set_location(g_debug,'g_month : '||g_month, 5);
255 pay_in_utils.set_location(g_debug,'g_challan_year : '||g_challan_year, 5);
256 pay_in_utils.set_location(g_debug,'g_challan_mth : '||g_challan_mth, 5);
257 pay_in_utils.set_location(g_debug,'g_return_type : '||g_return_type, 5);
258 pay_in_utils.set_location(g_debug,'g_arc_ref_no : '||g_arc_ref_no, 5);
259 pay_in_utils.set_location(g_debug,'g_bg_id : '||g_bg_id, 5);
260 pay_in_utils.set_location(g_debug,'g_start_date : '||g_start_date, 5);
261 pay_in_utils.set_location(g_debug,'g_end_date : '||g_end_date, 5);
262 pay_in_utils.set_location(g_debug,'g_business_no : '||g_business_no, 5);
263 pay_in_utils.set_location(g_debug,'p_payroll_action_id : '||p_payroll_action_id, 5);
264
265
266 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 20);
267 --
268 EXCEPTION
269 WHEN E_NON_UNIQUE_ARCH_REF_NO THEN
270 pay_in_utils.raise_message(800, 'PER_IN_NON_UNIQUE_VALUE', l_token_name, l_token_value);
271 fnd_file.put_line(fnd_file.log,'Archive Reference Number '|| g_arc_ref_no || 'is non-unique.');
272 RAISE;
273 WHEN OTHERS THEN
274 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR',
275 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
276 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 30);
277 pay_in_utils.trace(l_message,l_procedure);
278 RAISE;
279 --
280 END initialization_code;
281
282
283 --------------------------------------------------------------------------
284 -- --
285 -- Name : ARCHIVE_PF_EMP_DTLS --
286 -- Type : PROCEDURE --
287 -- Access : Private --
288 -- Description : This procedure gets the Employee --
289 -- Level Data like the PF Number,NSSN,Hire Date, --
290 -- Employee Type,Termination Date,Termination Reason, --
291 -- EPS on Higher Employer Wages --
292 -- and archives them. --
293 -- --
294 -- Parameters : --
295 -- IN : p_run_asg_action_id NUMBER --
296 -- p_arc_asg_action_id NUMBER --
297 -- p_assignment_id NUMBER --
298 -- p_pf_org NUMBER --
299 -- p_business_number NUMBER --
300 -- --
301 -- OUT : N/A --
302 -- --
303 -- Change History : --
304 --------------------------------------------------------------------------
305 -- Rev# Date Userid Description --
306 --------------------------------------------------------------------------
307 -- 115.0 01-Aug-2007 rsaharay Initial Version --
308 -- 115.1 26-Oct-2007 rsaharay Modified Cursor csr_pf_people --
309 --------------------------------------------------------------------------
310
311 PROCEDURE archive_pf_emp_dtls( p_run_asg_action_id IN NUMBER
312 ,p_arc_asg_action_id IN NUMBER
313 ,p_assignment_id IN NUMBER
314 ,p_pf_org IN NUMBER
315 ,p_business_number IN NUMBER)
316 IS
317
318 l_procedure VARCHAR2(100);
319 l_message VARCHAR2(255);
320 l_action_info_id NUMBER ;
321 l_archived NUMBER ;
322 l_ovn NUMBER ;
323 l_pf_no PER_PEOPLE_F.PER_INFORMATION8%TYPE ;
324 l_nssn PER_PEOPLE_F.PER_INFORMATION15%TYPE ;
325 l_hire_date DATE ;
326 l_emp_type VARCHAR2(2);
327 l_term_date DATE ;
328 l_report PER_PERIODS_OF_SERVICE.PDS_INFORMATION1%TYPE;
329 l_efile PER_PERIODS_OF_SERVICE.PDS_INFORMATION2%TYPE;
330 l_eps HR_SOFT_CODING_KEYFLEX.SEGMENT12%TYPE;
331 l_classification HR_ORGANIZATION_INFORMATION.ORG_INFORMATION3%TYPE ;
332
333 CURSOR csr_pf_people IS
334 SELECT
335 ppf.per_information8 pf_no, -- PF Number
336 ppf.per_information15 nssn, -- NSSN
337 pps.date_start , -- Hire Date
338 'D', -- Employee Type
339 pps.actual_termination_date term_date, -- Termination Date
340 pps.pds_information1 report, -- Termination Reason(Print)
341 pps.pds_information2 efile, -- Termination Reason(EFile)
342 scl.segment12 eps -- EPS on higher employer wages
343 FROM per_people_f ppf,
344 per_person_types ppt,
345 per_assignments_f paf,
346 per_periods_of_service pps,
347 hr_soft_coding_keyflex scl
348 WHERE paf.person_id = ppf.person_id
349 AND paf.period_of_service_id = pps.period_of_service_id
350 AND ppf.person_type_id = ppt.person_type_id
351 AND paf.assignment_id = p_assignment_id
352 AND scl.segment2 = p_pf_org
353 AND scl.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
354 AND scl.enabled_flag = 'Y'
355 AND (to_char(paf.effective_start_date,'Month-YYYY')=to_char(g_end_date,'Month-YYYY')
356 OR to_char(paf.effective_end_date,'Month-YYYY')=to_char(g_end_date,'Month-YYYY')
357 OR g_end_date between paf.effective_start_date and paf.effective_end_date)
358 AND paf.effective_end_date between ppf.effective_start_date and ppf.effective_end_date;
359 -- AND g_end_date between ppf.effective_start_date and ppf.effective_end_date ;
360
361
362 CURSOR csr_pf_classification
363 IS
364 SELECT org_information3 classification
365 FROM hr_organization_information hr_pf_org
366 WHERE org_information_context = 'PER_IN_PF_DF'
367 AND hr_pf_org.organization_id = p_pf_org ;
368
369 BEGIN
370 g_debug := hr_utility.debug_enabled;
371 l_procedure := g_package || 'archive_pf_emp_dtls';
372 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
373
374 OPEN csr_pf_classification ;
375 FETCH csr_pf_classification INTO l_classification ;
376 CLOSE csr_pf_classification ;
377
378
379 OPEN csr_pf_people;
380 FETCH csr_pf_people INTO l_pf_no,l_nssn,l_hire_date,l_emp_type,l_term_date,l_report,l_efile,l_eps;
381 CLOSE csr_pf_people;
382
383 pay_in_utils.set_location(g_debug,'Assignment Id : '||p_assignment_id,15);
384
385 pay_action_information_api.create_action_information
386 (p_action_context_id => p_arc_asg_action_id
387 ,p_action_context_type => 'AAP'
388 ,p_action_information_category => 'IN_PF_PERSON_DTLS'
389 ,p_source_id => p_run_asg_action_id
390 ,p_action_information1 => p_business_number
391 ,p_action_information2 => g_arc_ref_no
392 ,p_action_information3 => g_month||g_year
393 ,p_action_information4 => l_pf_no
394 ,p_action_information5 => l_nssn
395 ,p_action_information6 => fnd_date.date_to_canonical(l_hire_date)
396 ,p_action_information7 => l_emp_type
397 ,p_action_information8 => fnd_date.date_to_canonical(l_term_date)
398 ,p_action_information9 => l_report
399 ,p_action_information10 => l_efile
400 ,p_action_information11 => l_eps
401 ,p_action_information12 => p_pf_org
402 ,p_action_information13 => l_classification
403 ,p_action_information_id => l_action_info_id
404 ,p_object_version_number => l_ovn
405 );
406
407 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
408
409 EXCEPTION
410 WHEN OTHERS THEN
411 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
412 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 30);
413 pay_in_utils.trace(l_message,l_procedure);
414 RAISE;
415 --
416 END archive_pf_emp_dtls;
417
418
419 --------------------------------------------------------------------------
420 -- --
421 -- Name : ARCHIVE_PF_BALANCES --
422 -- Type : PROCEDURE --
423 -- Access : Private --
424 -- Description : This procedure calls pay_balance_pkg.get_value --
425 -- to get the _ASG_ORG_PTD values of the following --
426 -- balances --
427 -- 1. Employee Statutory PF Contribution --
428 -- 2. Employee Voluntary PF Contribution --
429 -- 3. Employer PF Contribution --
430 -- 4. EPS Contribution --
431 -- 5. PF Actual Salary --
432 -- 6. Employer PF Administrative Charges --
433 -- 7. Employer PF Inspection Charges --
434 -- 8. Employer EDLI Administrative Charges --
435 -- 9. Employer EDLI Inspection Charges --
436 -- 10. EDLI Contribution --
437 -- --
438 -- It also gets the element entries for the element --
439 -- 'PF Refund Information' and the Vol PF Percentage --
440 -- 'EE Voluntary PF Percent'. --
441 -- --
442 -- It then archives individual balances. --
443 -- --
444 -- Parameters : --
445 -- IN : p_run_asg_action_id NUMBER --
446 -- p_arc_asg_action_id NUMBER --
447 -- p_assignment_id NUMBER --
448 -- p_pf_org NUMBER --
449 -- p_business_number NUMBER --
450 -- --
451 -- --
452 -- OUT : N/A --
453 -- --
454 -- Change History : --
455 --------------------------------------------------------------------------
456 -- Rev# Date Userid Description --
457 --------------------------------------------------------------------------
458 -- 115.0 01-Aug-2007 rsaharay Initial Version --
459 --------------------------------------------------------------------------
460
461 PROCEDURE archive_pf_balances( p_run_asg_action_id IN NUMBER
462 ,p_arc_asg_action_id IN NUMBER
463 ,p_assignment_id IN NUMBER
464 ,p_pf_org IN NUMBER
465 ,p_business_number IN NUMBER
466 )
467 IS
468
469 l_value NUMBER;
470 l_procedure VARCHAR2(100);
471 l_message VARCHAR2(255);
472 l_classification HR_ORGANIZATION_INFORMATION.ORG_INFORMATION3%TYPE ;
473 l_action_info_id NUMBER ;
474 l_ovn NUMBER ;
475 l_vol_pf_rate NUMBER ;
476 l_pf_org NUMBER ;
477 result_val NUMBER:=0;
478 l_vpf_count NUMBER:=0;
479
480
481
482 CURSOR csr_pf_balances
483 IS
484 SELECT pdb.defined_balance_id balance_id
485 ,pbt.balance_name balance_name
486 FROM pay_balance_types pbt
487 ,pay_balance_dimensions pbd
488 ,pay_defined_balances pdb
489 WHERE pbt.balance_name IN('Employee Statutory PF Contribution'
490 ,'Employee Voluntary PF Contribution'
491 ,'Employer PF Contribution'
492 ,'EPS Contribution'
493 ,'PF Actual Salary'
494 ,'Employer PF Administrative Charges'
495 ,'Employer PF Inspection Charges'
496 ,'Employer EDLI Administrative Charges'
497 ,'Employer EDLI Inspection Charges'
498 ,'EDLI Contribution'
499 ,'Refund of Advance Employer PF Share'
500 ,'Refund of Advance Employee PF Share'
501 ,'Recovery of Over Payment of Employee PF Share'
502 ,'Recovery of Over Payment of Employer PF Share'
503 ,'Penalty Interest on Refund of Employer PF Share'
504 ,'Penalty Interest on Refund of Employee PF Share'
505
506 )
507 AND pbd.dimension_name ='_ASG_ORG_PTD'
508 AND pbt.legislation_code = 'IN'
509 AND pbd.legislation_code = 'IN'
510 AND pdb.legislation_code = 'IN'
511 AND pbt.balance_type_id = pdb.balance_type_id
512 AND pbd.balance_dimension_id = pdb.balance_dimension_id;
513
514
515 CURSOR csr_pf_balances_ptd
516 IS
517 SELECT pdb.defined_balance_id balance_id
518 ,pbt.balance_name balance_name
519 FROM pay_balance_types pbt
520 ,pay_balance_dimensions pbd
521 ,pay_defined_balances pdb
522 WHERE pbt.balance_name IN('Non Contributory Period')
523 AND pbd.dimension_name ='_ASG_PTD'
524 AND pbt.legislation_code = 'IN'
525 AND pbd.legislation_code = 'IN'
526 AND pdb.legislation_code = 'IN'
527 AND pbt.balance_type_id = pdb.balance_type_id
528 AND pbd.balance_dimension_id = pdb.balance_dimension_id;
529
530 CURSOR csr_pf_vol_rate
531 IS
532 SELECT result_value
533 FROM pay_run_results prr,
534 pay_run_result_values prv,
535 pay_element_types_f pet,
536 pay_input_values_f piv,
537 pay_assignment_actions paa
538 WHERE prr.run_result_id = prv.run_result_id
539 AND prr.element_type_id = pet.element_type_id
540 AND pet.element_type_id = piv.element_type_id
541 AND piv.input_value_id = prv.input_value_id
542 AND paa.source_action_id = p_run_asg_action_id
543 AND paa.assignment_action_id =prr.assignment_action_id
544 AND pet.element_name = 'PF Information'
545 AND piv.NAME ='EE Voluntary PF Percent'
546 AND g_end_date BETWEEN pet.effective_start_date and pet.effective_end_date
547 AND g_end_date BETWEEN piv.effective_start_date and piv.effective_end_date ;
548
549
550
551 CURSOR csr_pf_classification
552 IS
553 SELECT org_information3 classification
554 FROM hr_organization_information hr_pf_org
555 WHERE org_information_context = 'PER_IN_PF_DF'
556 AND hr_pf_org.organization_id = p_pf_org ;
557
558
559
560
561 CURSOR csr_chk_vpf(p_class VARCHAR2 )
562 IS
563 SELECT COUNT(*)
564 FROM pay_action_information
565 WHERE action_context_id = p_arc_asg_action_id
566 AND action_information1 = p_business_number
567 AND action_information_category = 'IN_PF_SALARY'
568 AND action_context_type = 'AAP'
569 AND action_information3 = 'Voluntary PF Percent'
570 AND action_information5 = p_class ;
571
572
573 BEGIN
574 g_debug := hr_utility.debug_enabled;
575 l_procedure := g_package || 'archive_pf_balances';
576 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
577
578 OPEN csr_pf_classification ;
579 FETCH csr_pf_classification INTO l_classification ;
580 CLOSE csr_pf_classification ;
581
582 FOR c_rec IN csr_pf_balances
583 LOOP
584
585 result_val := pay_balance_pkg.get_value(p_defined_balance_id => c_rec.balance_id,
586 p_assignment_action_id => p_run_asg_action_id,
587 p_tax_unit_id => NULL ,
588 p_jurisdiction_code => NULL ,
589 p_source_id => p_pf_org,
590 p_tax_group => NULL ,
591 p_date_earned => NULL );
592
593 pay_in_utils.set_location(g_debug,'balance_name: '||c_rec.balance_name,20);
594 pay_in_utils.set_location(g_debug,'result_val: '||result_val,20);
595
596 IF result_val <> 0 THEN
597 pay_action_information_api.create_action_information
598 (p_action_context_id => p_arc_asg_action_id
599 ,p_action_context_type => 'AAP'
600 ,p_action_information_category => 'IN_PF_SALARY'
601 ,p_source_id => p_run_asg_action_id
602 ,p_action_information1 => p_business_number
603 ,p_action_information2 => p_pf_org
604 ,p_action_information3 => c_rec.balance_name
605 ,p_action_information4 => fnd_number.number_to_canonical(result_val)
606 ,p_action_information5 => l_classification
607 ,p_action_information_id => l_action_info_id
608 ,p_object_version_number => l_ovn
609 );
610 END IF ;
611 result_val := 0;
612 END LOOP ;
613
614 FOR rec_pf_balances_ptd IN csr_pf_balances_ptd
615 LOOP
616 result_val := pay_balance_pkg.get_value(p_defined_balance_id =>rec_pf_balances_ptd.balance_id,
617 p_assignment_action_id =>p_run_asg_action_id,
618 p_tax_unit_id => NULL ,
619 p_jurisdiction_code => NULL ,
620 p_source_id => NULL ,
621 p_tax_group => NULL ,
622 p_date_earned => NULL );
623
624 pay_in_utils.set_location(g_debug,'balance_name: '||rec_pf_balances_ptd.balance_name,20);
625 pay_in_utils.set_location(g_debug,'result_val: '||result_val,20);
626
627 IF result_val <> 0 THEN
628 pay_action_information_api.create_action_information
629 (p_action_context_id => p_arc_asg_action_id
630 ,p_action_context_type => 'AAP'
631 ,p_action_information_category => 'IN_PF_SALARY'
632 ,p_source_id => p_run_asg_action_id
633 ,p_action_information1 => p_business_number
634 ,p_action_information2 => p_pf_org
635 ,p_action_information3 => rec_pf_balances_ptd.balance_name
636 ,p_action_information4 => fnd_number.number_to_canonical(result_val)
637 ,p_action_information5 => l_classification
638 ,p_action_information_id => l_action_info_id
639 ,p_object_version_number => l_ovn
640 );
641 END IF ;
642
643 result_val := 0;
644 END LOOP ;
645
646 OPEN csr_chk_vpf(l_classification) ;
647 FETCH csr_chk_vpf INTO l_vpf_count ;
648 CLOSE csr_chk_vpf ;
649
650 IF l_vpf_count = 0 THEN
651
652 OPEN csr_pf_vol_rate;
653 FETCH csr_pf_vol_rate INTO l_vol_pf_rate;
654 CLOSE csr_pf_vol_rate;
655
656 pay_action_information_api.create_action_information
657 (p_action_context_id => p_arc_asg_action_id
658 ,p_action_context_type => 'AAP'
659 ,p_action_information_category => 'IN_PF_SALARY'
660 ,p_source_id => p_run_asg_action_id
661 ,p_action_information1 => p_business_number
662 ,p_action_information2 => p_pf_org
663 ,p_action_information3 => 'Voluntary PF Percent'
664 ,p_action_information4 => fnd_number.number_to_canonical(l_vol_pf_rate)
665 ,p_action_information5 => l_classification
666 ,p_action_information_id => l_action_info_id
667 ,p_object_version_number => l_ovn
668 );
669
670 END IF ;
671
672 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
673 EXCEPTION
674 WHEN OTHERS THEN
675 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
676 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 30);
677 pay_in_utils.trace(l_message,l_procedure);
678 RAISE;
679 --
680 END archive_pf_balances;
681
682
683 --------------------------------------------------------------------------
684 -- --
685 -- Name : ARCHIVE_PF_ORG_DTLS --
686 -- Type : PROCEDURE --
687 -- Access : Private --
688 -- Description : This procedure gets the PF Organization --
689 -- Level Data like the Business Number,PF Type --
690 -- Registered Company Name,Representative Details --
691 -- and archives them. --
692 -- --
693 -- Parameters : --
694 -- IN : p_arc_pay_action_id NUMBER --
695 -- p_business_number NUMBER --
696 -- --
697 -- OUT : N/A --
698 -- --
699 -- Change History : --
700 --------------------------------------------------------------------------
701 -- Rev# Date Userid Description --
702 --------------------------------------------------------------------------
703 -- 115.0 01-Aug-2007 rsaharay Initial Version --
704 -- 115.1 25-Aug-2007 rsaharay Modified cursor csr_pos --
705 -- 115.2 19-Jun-2007 mdubasi Modified to store Business Group Id --
706 --------------------------------------------------------------------------
707
708 PROCEDURE archive_pf_org_dtls(p_arc_pay_action_id IN NUMBER
709 ,p_business_number IN NUMBER)
710 IS
711
712 l_procedure VARCHAR2(100);
713 l_message VARCHAR2(255);
714 l_action_info_id NUMBER ;
715 l_ovn NUMBER ;
716 l_base_bus_no VARCHAR2(50) ;
717 l_classification HR_ORGANIZATION_INFORMATION.ORG_INFORMATION3%TYPE ;
718 l_reg_comp_name HR_ORGANIZATION_INFORMATION.ORG_INFORMATION4%TYPE ;
719 l_representative HR_ORGANIZATION_INFORMATION.ORG_INFORMATION1%TYPE;
720 l_representative_name PER_PEOPLE_F.FIRST_NAME%TYPE;
721 l_representative_desig PER_ALL_POSITIONS.NAME%TYPE;
722 l_bus_group_id VARCHAR2(20);
723
724
725
726 CURSOR csr_pf_org IS
727 SELECT
728 organization_id org_id , --OrgId
729 org_information10 base_bus_no, --Base Business Number
730 org_information10||org_information9 bus_no , --Business Number
731 org_information3 classification --Classification
732 FROM hr_organization_information hr_pf_org
733 WHERE org_information_context = 'PER_IN_PF_DF'
734 AND hr_pf_org.org_information10||org_information9 = p_business_number ;
735
736
737 CURSOR csr_reg_company(p_base_bus_no VARCHAR2 )
738 IS
739 SELECT org_information4 --Legal Name
740 FROM hr_organization_information
741 WHERE org_information_context = 'PER_IN_COMPANY_DF'
742 AND org_information5 = p_base_bus_no ;
743
744 CURSOR csr_pf_representative(p_pf_org NUMBER )
745 IS
746 SELECT
747 org_information1 --Representative
748 FROM hr_organization_information hr_pf_org
749 WHERE org_information_context = 'PER_IN_PF_REP_DF'
750 AND hr_pf_org.organization_id = p_pf_org
751 AND g_end_date BETWEEN fnd_date.canonical_to_date(org_information2)
752 AND NVL(fnd_date.canonical_to_date(org_information3),TO_DATE('31-12-4712','DD-MM-YYYY'));
753
754 CURSOR csr_pos(p_person_id VARCHAR2)
755 IS
756 SELECT nvl(pos.name,job.name) name
757 FROM per_all_positions pos
758 ,per_assignments_f asg
759 ,per_jobs job
760 WHERE asg.position_id=pos.position_id(+)
761 AND asg.job_id=job.job_id(+)
762 AND asg.person_id = p_person_id
763 AND asg.primary_flag = 'Y'
764 AND g_end_date BETWEEN pos.date_effective(+) AND NVL(pos.date_end(+),TO_DATE('31-12-4712','DD-MM-YYYY'))
765 AND g_end_date BETWEEN job.date_from(+) AND NVL(job.date_to(+),TO_DATE('31-12-4712','DD-MM-YYYY'))
766 AND g_end_date BETWEEN asg.effective_start_date AND asg.effective_end_date;
767
768 CURSOR csr_emp_name(p_person_id VARCHAR2)
769 IS
770 SELECT DECODE(pep.title,NULL,hr_in_utility.per_in_full_name(pep.first_name,pep.middle_names,pep.last_name,pep.title)
771 ,SUBSTR(hr_in_utility.per_in_full_name(pep.first_name,pep.middle_names,pep.last_name,pep.title)
772 ,INSTR(hr_in_utility.per_in_full_name(pep.first_name,pep.middle_names,pep.last_name,pep.title),' ',1)+1)) name
773 FROM per_people_f pep
774 WHERE pep.person_id = p_person_id
775 AND g_end_date BETWEEN pep.effective_start_date AND pep.effective_end_date ;
776
777
778
779
780 BEGIN
781 g_debug := hr_utility.debug_enabled;
782 l_procedure := g_package || 'archive_pf_org_dtls';
783 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
784
785
786 FOR rec_pf_org IN csr_pf_org
787 LOOP
788
789 pay_in_utils.set_location(g_debug,'Org Id : '||rec_pf_org.org_id,5);
790
791 l_representative := NULL ;
792 l_representative_desig := NULL ;
793 l_representative_name := NULL ;
794
795 OPEN csr_pf_representative(rec_pf_org.org_id) ;
796 FETCH csr_pf_representative INTO l_representative ;
797 CLOSE csr_pf_representative ;
798
799 OPEN csr_pos(l_representative);
800 FETCH csr_pos INTO l_representative_desig;
801 CLOSE csr_pos ;
802
803 OPEN csr_emp_name(l_representative);
804 FETCH csr_emp_name INTO l_representative_name;
805 CLOSE csr_emp_name ;
806
807 pay_action_information_api.create_action_information
808 (p_action_context_id => p_arc_pay_action_id
809 ,p_action_context_type => 'PA'
810 ,p_action_information_category => 'IN_PF_ORG'
811 ,p_action_information1 => rec_pf_org.bus_no
812 ,p_action_information2 => rec_pf_org.org_id
813 ,p_action_information3 => rec_pf_org.classification
814 ,p_action_information4 => l_representative_name
815 ,p_action_information5 => l_representative_desig
816 ,p_action_information_id => l_action_info_id
817 ,p_object_version_number => l_ovn
818 );
819
820 l_base_bus_no := rec_pf_org.base_bus_no ;
821 END LOOP ;
822
823 OPEN csr_reg_company(l_base_bus_no);
824 FETCH csr_reg_company INTO l_reg_comp_name ;
825 CLOSE csr_reg_company ;
826
827 SELECT business_group_id INTO l_bus_group_id --Business Group Id
828 FROM pay_payroll_actions
829 WHERE payroll_action_id =p_arc_pay_action_id;
830
831 pay_in_utils.set_location(g_debug,'Business Number : '||p_business_number,15);
832
833 pay_action_information_api.create_action_information
834 (p_action_context_id => p_arc_pay_action_id
835 ,p_action_context_type => 'PA'
836 ,p_action_information_category => 'IN_PF_BUSINESS_NUMBER'
837 ,p_action_information1 => p_business_number
838 ,p_action_information2 => g_arc_ref_no
839 ,p_action_information3 => g_month||g_year
840 ,p_action_information4 => g_return_type
841 ,p_action_information5 => l_reg_comp_name
842 ,p_action_information_id => l_action_info_id
843 ,p_object_version_number => l_ovn
844 );
845
846 pay_action_information_api.create_action_information
847 (p_action_context_id => p_arc_pay_action_id
848 ,p_action_context_type => 'PA'
849 ,p_action_information_category => 'IN_PF_ARC_REF_NUMBER'
850 ,p_action_information1 => g_return_type
851 ,p_action_information2 => g_arc_ref_no
852 ,p_action_information3 => g_month||g_year
853 ,p_action_information4 => p_business_number
854 ,p_action_information5 => l_bus_group_id
855 ,p_action_information_id => l_action_info_id
856 ,p_object_version_number => l_ovn
857 );
858
859
860 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
861 EXCEPTION
862 WHEN OTHERS THEN
863 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
864 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 30);
865 pay_in_utils.trace(l_message,l_procedure);
866 RAISE;
867 --
868 END archive_pf_org_dtls;
869
870
871 --------------------------------------------------------------------------
872 -- --
873 -- Name : ARCHIVE_PF_CHALLAN_DTLS --
874 -- Type : PROCEDURE --
875 -- Access : Private --
876 -- Description : This procedure gets the Challan Information Details --
877 -- of the PF Organization and archives them. --
878 -- --
879 -- Parameters : --
880 -- IN : p_arc_pay_action_id NUMBER --
881 -- p_pf_org NUMBER --
882 -- p_challan_ref VARCHAR2 --
883 -- p_business_number NUMBER --
884 -- --
885 -- OUT : N/A --
886 -- --
887 -- Change History : --
888 --------------------------------------------------------------------------
889 -- Rev# Date Userid Description --
890 --------------------------------------------------------------------------
891 -- 115.0 01-Aug-2007 rsaharay Initial Version --
892 -- 115.1 24-Oct-2007 rsaharay Modified for Currency Period --
893 --------------------------------------------------------------------------
894
895 PROCEDURE archive_pf_challan_dtls(p_arc_pay_action_id IN NUMBER
896 ,p_pf_org IN NUMBER
897 ,p_challan_ref IN VARCHAR2
898 ,p_business_number IN NUMBER )
899 IS
900
901 l_procedure VARCHAR2(100);
902 l_message VARCHAR2(255);
903 l_action_info_id NUMBER ;
904 l_ovn NUMBER ;
905
906 l_Paid_Under_Protest HR_ORGANIZATION_INFORMATION.ORG_INFORMATION1%TYPE;
907 l_Payment_Type HR_ORGANIZATION_INFORMATION.ORG_INFORMATION1%TYPE;
908 l_Cheque_DD_No HR_ORGANIZATION_INFORMATION.ORG_INFORMATION1%TYPE;
909 l_Cheque_DD_Date DATE ;
910 l_Cheque_DD_Dep_Date DATE ;
911 l_Amount HR_ORGANIZATION_INFORMATION.ORG_INFORMATION1%TYPE;
912 l_Bank_Code HR_ORGANIZATION_INFORMATION.ORG_INFORMATION1%TYPE;
913 l_Branch_Code HR_ORGANIZATION_INFORMATION.ORG_INFORMATION1%TYPE;
914 l_Challan_Ref HR_ORGANIZATION_INFORMATION.ORG_INFORMATION1%TYPE;
915 l_Dep_Bank_Code HR_ORGANIZATION_INFORMATION.ORG_INFORMATION1%TYPE;
916 l_Dep_Branch_Code HR_ORGANIZATION_INFORMATION.ORG_INFORMATION1%TYPE;
917 l_Dep_Base_Branch HR_ORGANIZATION_INFORMATION.ORG_INFORMATION1%TYPE;
918 l_Interest_Sec HR_ORGANIZATION_INFORMATION.ORG_INFORMATION1%TYPE;
919 l_Legal_Charges HR_ORGANIZATION_INFORMATION.ORG_INFORMATION1%TYPE;
920 l_Penalty HR_ORGANIZATION_INFORMATION.ORG_INFORMATION1%TYPE;
921 l_Branch_Name HR_ORGANIZATION_INFORMATION.ORG_INFORMATION1%TYPE;
922 l_Branch_Addr HR_ORGANIZATION_INFORMATION.ORG_INFORMATION1%TYPE;
923 l_Dep_Branch_Name HR_ORGANIZATION_INFORMATION.ORG_INFORMATION1%TYPE;
924 l_Dep_Branch_Addr HR_ORGANIZATION_INFORMATION.ORG_INFORMATION1%TYPE;
925
926 l_14B_Prev_Mth HR_ORGANIZATION_INFORMATION.ORG_INFORMATION1%TYPE;
927 l_14B_Prev_Yr HR_ORGANIZATION_INFORMATION.ORG_INFORMATION1%TYPE;
928 l_14B_Challan_Ref HR_ORGANIZATION_INFORMATION.ORG_INFORMATION1%TYPE;
929 l_14B_Penal_Damages_Due HR_ORGANIZATION_INFORMATION.ORG_INFORMATION1%TYPE;
930 l_14B_EPS_Penal_Damages HR_ORGANIZATION_INFORMATION.ORG_INFORMATION1%TYPE;
931 l_14B_EDLI_Penal_Damages HR_ORGANIZATION_INFORMATION.ORG_INFORMATION1%TYPE;
932 l_14B_EPF_Penal_Damages HR_ORGANIZATION_INFORMATION.ORG_INFORMATION1%TYPE;
933 l_14B_Edli_Admin HR_ORGANIZATION_INFORMATION.ORG_INFORMATION1%TYPE;
934
935 l_7Q_Due_Mth HR_ORGANIZATION_INFORMATION.ORG_INFORMATION1%TYPE ;
936 l_7Q_Due_Yr HR_ORGANIZATION_INFORMATION.ORG_INFORMATION1%TYPE;
937 l_7Q_Challan_Ref HR_ORGANIZATION_INFORMATION.ORG_INFORMATION1%TYPE;
938 l_7Q_EPF_Damages HR_ORGANIZATION_INFORMATION.ORG_INFORMATION1%TYPE;
939 l_7Q_EPS_Damages HR_ORGANIZATION_INFORMATION.ORG_INFORMATION1%TYPE;
940 l_7Q_EDLI_Damages HR_ORGANIZATION_INFORMATION.ORG_INFORMATION1%TYPE;
941 l_7Q_EPF_Admin HR_ORGANIZATION_INFORMATION.ORG_INFORMATION1%TYPE;
942 l_7Q_EDLI_Admin HR_ORGANIZATION_INFORMATION.ORG_INFORMATION1%TYPE ;
943
944 l_Misc_Challan_Ref HR_ORGANIZATION_INFORMATION.ORG_INFORMATION1%TYPE;
945 l_Misc_EPF_Pay HR_ORGANIZATION_INFORMATION.ORG_INFORMATION1%TYPE ;
946 l_Misc_EPF_Rem HR_ORGANIZATION_INFORMATION.ORG_INFORMATION1%TYPE ;
947 l_Misc_EPS_Pay HR_ORGANIZATION_INFORMATION.ORG_INFORMATION1%TYPE ;
948 l_Misc_EPS_Rem HR_ORGANIZATION_INFORMATION.ORG_INFORMATION1%TYPE ;
949 l_Misc_EDLI_Pay HR_ORGANIZATION_INFORMATION.ORG_INFORMATION1%TYPE ;
950 l_Misc_EDLI_Rem HR_ORGANIZATION_INFORMATION.ORG_INFORMATION1%TYPE ;
951 l_Misc_EPF_Admin HR_ORGANIZATION_INFORMATION.ORG_INFORMATION1%TYPE ;
952 l_Misc_EPF_Admin_Rem HR_ORGANIZATION_INFORMATION.ORG_INFORMATION1%TYPE ;
953 l_Misc_EDLI_Admin HR_ORGANIZATION_INFORMATION.ORG_INFORMATION1%TYPE ;
954 l_Misc_EDLI_Admin_Rem HR_ORGANIZATION_INFORMATION.ORG_INFORMATION1%TYPE ;
955
956
957
958
959 CURSOR csr_challans
960 IS
961 SELECT hoi_challan.org_information4 Payment_Type
962 ,hoi_challan.org_information5 Cheque_DD_No
963 ,fnd_date.canonical_to_date(hoi_challan.org_information6) Cheque_DD_Date
964 ,hoi_challan.org_information7 Bank_Code
965 ,hoi_challan.org_information8 Branch_Code
966 ,hoi_challan.org_information11 Dep_Bank_Code
967 ,hoi_challan.org_information9 Dep_Branch_Code
968 ,hoi_challan.org_information10 Dep_Base_Branch
969 FROM hr_organization_information hoi_challan
970 WHERE hoi_challan.organization_id = p_pf_org
971 AND hoi_challan.org_information_context ='PER_IN_PF_BANK_PAYMENT_DETAILS'
972 AND hoi_challan.org_information3= p_challan_ref;
973
974
975 CURSOR csr_challans_info
976 IS
977 SELECT hoi_challan.org_information11 Paid_Under_Protest
978 ,fnd_date.canonical_to_date(hoi_challan.org_information9) Cheque_DD_Dep_Date
979 ,fnd_number.canonical_to_number(NVL(hoi_challan.org_information3,0))
980 +fnd_number.canonical_to_number(NVL(hoi_challan.org_information4,0))
981 +fnd_number.canonical_to_number(NVL(hoi_challan.org_information5,0))
982 +fnd_number.canonical_to_number(NVL(hoi_challan.org_information6,0))
983 +fnd_number.canonical_to_number(NVL(hoi_challan.org_information7,0))
984 +fnd_number.canonical_to_number(NVL(hoi_challan.org_information8,0))
985 +fnd_number.canonical_to_number(NVL(hoi_challan.org_information13,0))
986 +fnd_number.canonical_to_number(NVL(hoi_challan.org_information14,0))
987 +fnd_number.canonical_to_number(NVL(hoi_challan.org_information15,0)) Amount
988 ,hoi_challan.org_information12 Challan_Ref
989 ,hoi_challan.org_information13 Legal_Charges
990 ,hoi_challan.org_information14 Interest_Sec
991 ,hoi_challan.org_information15 Penalty
992 FROM hr_organization_information hoi_challan
993 WHERE hoi_challan.organization_id = p_pf_org
994 AND hoi_challan.org_information_context ='PER_IN_PF_CHALLAN_INFO'
995 AND hoi_challan.org_information12= p_challan_ref;
996
997 CURSOR csr_penal_damages
998 IS
999 SELECT hoi_challan.org_information1 Challan_Ref
1000 ,hoi_challan.org_information2 Prev_Mth
1001 ,hoi_challan.org_information3 Prev_Yr
1002 ,fnd_number.canonical_to_number(NVL(hoi_challan.org_information4,0))
1003 +fnd_number.canonical_to_number(NVL(hoi_challan.org_information5,0)) Penal_Damages_Due
1004 ,fnd_number.canonical_to_number(hoi_challan.org_information6) EPS_Penal_Damages
1005 ,fnd_number.canonical_to_number(hoi_challan.org_information7) EDLI_Penal_Damages
1006 ,fnd_number.canonical_to_number(hoi_challan.org_information9) EPF_Penal_Damages
1007 ,fnd_number.canonical_to_number(hoi_challan.org_information8) Edli_Admin
1008 FROM hr_organization_information hoi_challan
1009 WHERE hoi_challan.organization_id = p_pf_org
1010 AND hoi_challan.org_information_context ='PER_IN_PF_CHN_SEC14B'
1011 AND hoi_challan.org_information1= p_challan_ref;
1012
1013 CURSOR csr_7Q
1014 IS
1015 SELECT hoi_challan.org_information11 Challan_Ref
1016 ,hoi_challan.org_information1 Due_Mth
1017 ,hoi_challan.org_information2 Due_Yr
1018 ,fnd_number.canonical_to_number(NVL(hoi_challan.org_information3,0))
1019 +fnd_number.canonical_to_number(NVL(hoi_challan.org_information4,0)) EPF_Damages
1020 ,fnd_number.canonical_to_number(hoi_challan.org_information5) EPS_Damages
1021 ,fnd_number.canonical_to_number(hoi_challan.org_information6) EDLI_Damages
1022 ,fnd_number.canonical_to_number(hoi_challan.org_information8) EPF_Admin
1023 ,fnd_number.canonical_to_number(hoi_challan.org_information7) EDLI_Admin
1024 FROM hr_organization_information hoi_challan
1025 WHERE hoi_challan.organization_id = p_pf_org
1026 AND hoi_challan.org_information_context ='PER_IN_PF_SEC7Q_INFO'
1027 AND hoi_challan.org_information11= p_challan_ref;
1028
1029 CURSOR csr_misc
1030 IS
1031 SELECT hoi_challan.org_information3 Challan_Ref
1032 ,fnd_number.canonical_to_number(NVL(hoi_challan.org_information4,0))
1033 +fnd_number.canonical_to_number(NVL(hoi_challan.org_information5,0)) EPF_Misc_Pay
1034 ,hoi_challan.org_information6 EPF_Rem
1035 ,fnd_number.canonical_to_number(hoi_challan.org_information7) EPS_Misc_Pay
1036 ,hoi_challan.org_information8 EPS_Rem
1037 ,fnd_number.canonical_to_number(hoi_challan.org_information9) EDLI_Misc_Pay
1038 ,hoi_challan.org_information10 EDLI_Rem
1039 ,fnd_number.canonical_to_number(hoi_challan.org_information11) EPF_Admin
1040 ,hoi_challan.org_information12 EPF_Admin_Rem
1041 ,fnd_number.canonical_to_number(hoi_challan.org_information13) EDLI_Admin
1042 ,hoi_challan.org_information14 EDLI_Admin_Rem
1043 FROM hr_organization_information hoi_challan
1044 WHERE hoi_challan.organization_id = p_pf_org
1045 AND hoi_challan.org_information_context ='PER_IN_PF_MIS_PAY_INFO'
1046 AND hoi_challan.org_information3= p_challan_ref;
1047
1048
1049 CURSOR csr_branch_dtls(p_bank_code VARCHAR2
1050 ,p_branch_code VARCHAR2 )
1051 IS
1052 SELECT hoi.org_information3 branch_name
1053 ,hoi.org_information4 branch_add
1054 FROM hr_organization_units hou
1055 ,hr_organization_information hoi
1056 WHERE hoi.organization_id = hou.organization_id
1057 AND hoi.org_information_context = 'PER_IN_PF_BANK_BRANCH_DTLS'
1058 AND hou.business_group_id = g_bg_id
1059 AND hoi.org_information1 = p_bank_code
1060 AND hoi.org_information2 = p_branch_code ;
1061
1062
1063 BEGIN
1064 g_debug := hr_utility.debug_enabled;
1065 l_procedure := g_package || 'archive_pf_challan_dtls';
1066 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1067
1068
1069
1070 OPEN csr_challans;
1071 FETCH csr_challans INTO l_Payment_Type ,
1072 l_Cheque_DD_No ,
1073 l_Cheque_DD_Date ,
1074 l_Bank_Code ,
1075 l_Branch_Code ,
1076 l_Dep_Bank_Code ,
1077 l_Dep_Branch_Code ,
1078 l_Dep_Base_Branch ;
1079 CLOSE csr_challans;
1080
1081
1082
1083 OPEN csr_challans_info;
1084 FETCH csr_challans_info INTO l_Paid_Under_Protest ,
1085 l_Cheque_DD_Dep_Date ,
1086 l_Amount ,
1087 l_Challan_Ref ,
1088 l_Legal_Charges ,
1089 l_Interest_Sec ,
1090 l_Penalty ;
1091 CLOSE csr_challans_info;
1092
1093
1094
1095
1096 OPEN csr_branch_dtls(l_Bank_Code,l_Branch_Code);
1097 FETCH csr_branch_dtls INTO l_Branch_Name , l_Branch_Addr ;
1098 CLOSE csr_branch_dtls ;
1099
1100 OPEN csr_branch_dtls(l_Dep_Bank_Code,l_Dep_Branch_Code);
1101 FETCH csr_branch_dtls INTO l_Dep_Branch_Name , l_Dep_Branch_Addr ;
1102 CLOSE csr_branch_dtls ;
1103
1104
1105 OPEN csr_penal_damages;
1106 FETCH csr_penal_damages INTO l_14B_Challan_Ref ,
1107 l_14B_Prev_Mth ,
1108 l_14B_Prev_Yr ,
1109 l_14B_Penal_Damages_Due ,
1110 l_14B_EPS_Penal_Damages ,
1111 l_14B_EDLI_Penal_Damages ,
1112 l_14B_EPF_Penal_Damages ,
1113 l_14B_Edli_Admin ;
1114 CLOSE csr_penal_damages;
1115
1116 OPEN csr_7Q;
1117 FETCH csr_7Q INTO l_7Q_Challan_Ref ,
1118 l_7Q_Due_Mth ,
1119 l_7Q_Due_Yr ,
1120 l_7Q_EPF_Damages ,
1121 l_7Q_EPS_Damages ,
1122 l_7Q_EDLI_Damages ,
1123 l_7Q_EPF_Admin ,
1124 l_7Q_EDLI_Admin ;
1125 CLOSE csr_7Q;
1126
1127 OPEN csr_misc;
1128 FETCH csr_misc INTO l_Misc_Challan_Ref ,
1129 l_Misc_EPF_Pay ,
1130 l_Misc_EPF_Rem ,
1131 l_Misc_EPS_Pay ,
1132 l_Misc_EPS_Rem ,
1133 l_Misc_EDLI_Pay ,
1134 l_Misc_EDLI_Rem ,
1135 l_Misc_EPF_Admin ,
1136 l_Misc_EPF_Admin_Rem ,
1137 l_Misc_EDLI_Admin ,
1138 l_Misc_EDLI_Admin_Rem ;
1139 CLOSE csr_misc;
1140
1141 l_Amount := NVL(l_Amount,0) +
1142 NVL(l_14B_Penal_Damages_Due,0) +
1143 NVL(l_14B_EPS_Penal_Damages,0) +
1144 NVL(l_14B_EDLI_Penal_Damages,0) +
1145 NVL(l_14B_EPF_Penal_Damages,0) +
1146 NVL(l_14B_Edli_Admin,0) +
1147 NVL(l_7Q_EPF_Damages,0) +
1148 NVL(l_7Q_EPS_Damages,0) +
1149 NVL(l_7Q_EDLI_Damages,0) +
1150 NVL(l_7Q_EPF_Admin,0) +
1151 NVL(l_7Q_EDLI_Admin,0) +
1152 NVL(l_Misc_EPF_Pay,0) +
1153 NVL(l_Misc_EPS_Pay,0) +
1154 NVL(l_Misc_EDLI_Pay,0) +
1155 NVL(l_Misc_EPF_Admin,0) +
1156 NVL(l_Misc_EDLI_Admin,0) ;
1157
1158 l_14B_Prev_Mth := TO_NUMBER(l_14B_Prev_Mth) + 3 ;
1159 l_14B_Prev_Yr := SUBSTR(l_14B_Prev_Yr,1,4);
1160 IF l_14B_Prev_Mth > 12 THEN
1161 l_14B_Prev_Mth := l_14B_Prev_Mth - 12 ;
1162 l_14B_Prev_Yr := TO_NUMBER(l_14B_Prev_Yr) + 1;
1163 END IF ;
1164 IF l_14B_Prev_Mth = 3 THEN
1165 l_14B_Prev_Yr := l_14B_Prev_Yr - 1;
1166 END IF ;
1167
1168 l_7Q_Due_Mth := TO_NUMBER(l_7Q_Due_Mth) + 3 ;
1169 l_7Q_Due_Yr := SUBSTR(l_7Q_Due_Yr,1,4);
1170 IF l_7Q_Due_Mth > 12 THEN
1171 l_7Q_Due_Mth := l_7Q_Due_Mth - 12 ;
1172 l_7Q_Due_Yr := TO_NUMBER(l_7Q_Due_Yr) + 1;
1173 END IF ;
1174 IF l_7Q_Due_Mth = 3 THEN
1175 l_7Q_Due_Yr := l_7Q_Due_Yr - 1;
1176 END IF ;
1177
1178
1179 IF l_Challan_Ref IS NOT NULL
1180 THEN
1181 pay_in_utils.set_location(g_debug,'Archiving : IN_PF_CHALLAN'||l_Challan_Ref,5);
1182 pay_action_information_api.create_action_information
1183 (p_action_context_id => p_arc_pay_action_id
1184 ,p_action_context_type => 'PA'
1185 ,p_action_information_category => 'IN_PF_CHALLAN'
1186 ,p_action_information1 => p_business_number
1187 ,p_action_information2 => l_Payment_Type
1188 ,p_action_information3 => p_pf_org
1189 ,p_action_information4 => l_Cheque_DD_No
1190 ,p_action_information5 => fnd_date.date_to_canonical(l_Cheque_DD_Date)
1191 ,p_action_information6 => fnd_date.date_to_canonical(l_Cheque_DD_Dep_Date)
1192 ,p_action_information7 => fnd_number.number_to_canonical(l_Amount)
1193 ,p_action_information8 => l_Challan_Ref
1194 ,p_action_information9 => l_Bank_Code
1195 ,p_action_information11 => l_Branch_Code
1196 ,p_action_information12 => l_Branch_Name
1197 ,p_action_information13 => l_Branch_Addr
1198 ,p_action_information14 => l_Dep_Bank_Code
1199 ,p_action_information15 => l_Dep_Branch_Code
1200 ,p_action_information16 => l_Dep_Branch_Name
1201 ,p_action_information17 => l_Dep_Branch_Addr
1202 ,p_action_information18 => l_Dep_Base_Branch
1203 ,p_action_information20 => l_Paid_Under_Protest
1204 ,p_action_information21 => fnd_number.number_to_canonical(l_Interest_Sec)
1205 ,p_action_information22 => fnd_number.number_to_canonical(l_Legal_Charges)
1206 ,p_action_information23 => fnd_number.number_to_canonical(l_Penalty)
1207 ,p_action_information_id => l_action_info_id
1208 ,p_object_version_number => l_ovn
1209 );
1210 END IF ;
1211
1212 IF l_14B_Challan_Ref IS NOT NULL
1213 THEN
1214
1215 pay_in_utils.set_location(g_debug,'Archiving : IN_PF_14B'||l_14B_Challan_Ref,6);
1216 pay_action_information_api.create_action_information
1217 (p_action_context_id => p_arc_pay_action_id
1218 ,p_action_context_type => 'PA'
1219 ,p_action_information_category => 'IN_PF_14B'
1220 ,p_action_information1 => l_14B_Prev_Mth
1221 ,p_action_information2 => l_14B_Prev_Yr
1222 ,p_action_information3 => l_14B_Challan_Ref
1223 ,p_action_information4 => fnd_number.number_to_canonical(l_14B_Penal_Damages_Due)
1224 ,p_action_information5 => fnd_number.number_to_canonical(l_14B_EPS_Penal_Damages)
1225 ,p_action_information6 => fnd_number.number_to_canonical(l_14B_EDLI_Penal_Damages)
1226 ,p_action_information7 => fnd_number.number_to_canonical(l_14B_EPF_Penal_Damages)
1227 ,p_action_information8 => fnd_number.number_to_canonical(l_14B_Edli_Admin)
1228 ,p_action_information_id => l_action_info_id
1229 ,p_object_version_number => l_ovn
1230 );
1231 END IF ;
1232
1233 IF l_7Q_Challan_Ref IS NOT NULL
1234 THEN
1235 pay_in_utils.set_location(g_debug,'Archiving : IN_PF_7Q'||l_7Q_Challan_Ref,7);
1236 pay_action_information_api.create_action_information
1237 (p_action_context_id => p_arc_pay_action_id
1238 ,p_action_context_type => 'PA'
1239 ,p_action_information_category => 'IN_PF_7Q'
1240 ,p_action_information1 => l_7Q_Due_Mth
1241 ,p_action_information2 => l_7Q_Due_Yr
1242 ,p_action_information3 => l_7Q_Challan_Ref
1243 ,p_action_information4 => fnd_number.number_to_canonical(l_7Q_EPF_Damages)
1244 ,p_action_information5 => fnd_number.number_to_canonical(l_7Q_EPS_Damages)
1245 ,p_action_information6 => fnd_number.number_to_canonical(l_7Q_EDLI_Damages)
1246 ,p_action_information7 => fnd_number.number_to_canonical(l_7Q_EPF_Admin)
1247 ,p_action_information8 => fnd_number.number_to_canonical(l_7Q_EDLI_Admin)
1248 ,p_action_information_id => l_action_info_id
1249 ,p_object_version_number => l_ovn
1250 );
1251 END IF ;
1252
1253 IF l_Misc_Challan_Ref IS NOT NULL
1254 THEN
1255 pay_in_utils.set_location(g_debug,'Archiving : IN_PF_MISC'||l_Misc_Challan_Ref,8);
1256 pay_action_information_api.create_action_information
1257 (p_action_context_id => p_arc_pay_action_id
1258 ,p_action_context_type => 'PA'
1259 ,p_action_information_category => 'IN_PF_MISC'
1260 ,p_action_information1 => l_Misc_Challan_Ref
1261 ,p_action_information2 => fnd_number.number_to_canonical(l_Misc_EPF_Pay)
1262 ,p_action_information3 => l_Misc_EPF_Rem
1263 ,p_action_information4 => fnd_number.number_to_canonical(l_Misc_EPS_Pay)
1264 ,p_action_information5 => l_Misc_EPS_Rem
1265 ,p_action_information6 => fnd_number.number_to_canonical(l_Misc_EDLI_Pay)
1266 ,p_action_information7 => l_Misc_EDLI_Rem
1267 ,p_action_information8 => fnd_number.number_to_canonical(l_Misc_EPF_Admin)
1268 ,p_action_information9 => l_Misc_EPF_Admin_Rem
1269 ,p_action_information10 => fnd_number.number_to_canonical(l_Misc_EDLI_Admin)
1270 ,p_action_information11 => l_Misc_EDLI_Admin_Rem
1271 ,p_action_information_id => l_action_info_id
1272 ,p_object_version_number => l_ovn
1273 );
1274 END IF ;
1275
1276
1277 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
1278
1279
1280 EXCEPTION
1281 WHEN OTHERS THEN
1282 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
1283 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 30);
1284 pay_in_utils.trace(l_message,l_procedure);
1285 RAISE;
1286 --
1287 END archive_pf_challan_dtls;
1288
1289
1290 --------------------------------------------------------------------------
1291 -- --
1292 -- Name : ASSIGNMENT_ACTION_CODE --
1293 -- Type : PROCEDURE --
1294 -- Access : Public --
1295 -- Description : This procedure further restricts the assignment_id's--
1296 -- returned by range_code. --
1297 -- It filters the assignments selected by range_code --
1298 -- procedure. --
1299 -- --
1300 -- Parameters : --
1301 -- IN : p_payroll_action_id NUMBER --
1302 -- p_start_person NUMBER --
1303 -- p_end_person NUMBER --
1304 -- p_chunk NUMBER --
1305 -- OUT : N/A --
1306 -- --
1307 -- Change History : --
1308 --------------------------------------------------------------------------
1309 -- Rev# Date Userid Description --
1310 --------------------------------------------------------------------------
1311 -- 115.0 01-Aug-2007 rsaharay Initial Version --
1312 -- 115.1 24-Oct-2007 rsaharay Modified csr_challans --
1313 --------------------------------------------------------------------------
1314 --
1315
1316 PROCEDURE assignment_action_code (
1317 p_payroll_action_id IN NUMBER
1318 ,p_start_person IN NUMBER
1319 ,p_end_person IN NUMBER
1320 ,p_chunk IN NUMBER
1321 )
1322 IS
1323
1324 l_procedure VARCHAR2(100);
1325 l_action_id NUMBER;
1326 l_payroll_id NUMBER;
1327 l_message VARCHAR2(255);
1328 l_match BOOLEAN := FALSE ;
1329 l_challan_match BOOLEAN := FALSE ;
1330 l_rev_chk_asg BOOLEAN := FALSE ;
1331 l_supp BOOLEAN := FALSE ;
1332 l_check NUMBER ;
1333 l_reg_check NUMBER ;
1334 l_action_info_id NUMBER ;
1335 l_ovn NUMBER ;
1336 l_pf_check NUMBER ;
1337
1338
1339
1340
1341 /*Cursor to get the Assignments that needs to be archived for
1342 the Regular Monthly Return.
1343 Will pick up assignments having 'P','U','I' in Current Month and
1344 attached to a PF Organization having the Business Number*/
1345
1346 CURSOR csr_process_assignments
1347 IS
1348 SELECT DISTINCT paa_init.assignment_id
1349 FROM pay_assignment_actions paa_init,
1350 pay_payroll_actions ppa_init,
1351 per_assignments_f paf,
1352 hr_organization_information hoi
1353 WHERE ppa_init.payroll_action_id = paa_init.payroll_action_id
1354 AND ppa_init.action_type IN ('P','U','I')
1355 AND ppa_init.action_status = 'C'
1356 AND ppa_init.business_group_id = g_bg_id
1357 AND p_payroll_action_id IS NOT NULL
1358 AND paf.person_id BETWEEN
1359 p_start_person AND p_end_person
1360 AND paf.assignment_id = paa_init.assignment_id
1361 AND (to_char(paf.effective_start_date,'Month-YYYY')=to_char(g_end_date,'Month-YYYY')
1362 OR to_char(paf.effective_end_date,'Month-YYYY')=to_char(g_end_date,'Month-YYYY')
1363 OR g_end_date between paf.effective_start_date and paf.effective_end_date)
1364 AND ppa_init.effective_date BETWEEN g_start_date AND g_end_date
1365 AND hoi.org_information_context = 'PER_IN_PF_DF'
1366 AND hoi.org_information10||hoi.org_information9 = NVL(g_business_no,hoi.org_information10||hoi.org_information9)
1367 AND hoi.org_information10 IS NOT NULL
1368 AND hoi.org_information9 IS NOT NULL
1369 AND TO_CHAR (hoi.organization_id) IN
1370 (SELECT scl.segment2
1371 FROM hr_soft_coding_keyflex scl
1372 WHERE scl.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
1373 AND scl.enabled_flag = 'Y');
1374
1375
1376 /*Cursor to get the Assignments that needs to be archived for
1377 the Supplementary Monthly Return.
1378 The Cursor will pick up the new Assignments and the Terminated
1379 Assignments not archived in the Regular Return having 'P','U','I'
1380 in Current Month and attached to a PF Organization having the Business Number*/
1381
1382 CURSOR csr_process_supp_assignments(p_business_no VARCHAR2 )
1383 IS
1384 SELECT DISTINCT paa_init.assignment_id
1385 FROM pay_assignment_actions paa_init,
1386 pay_payroll_actions ppa_init,
1387 per_assignments_f paf,
1388 hr_organization_information hoi
1389 WHERE ppa_init.payroll_action_id = paa_init.payroll_action_id
1390 AND ppa_init.action_type IN ('P','U','I')
1391 AND ppa_init.action_status = 'C'
1392 AND ppa_init.business_group_id = g_bg_id
1393 AND p_payroll_action_id IS NOT NULL
1394 AND paf.person_id BETWEEN
1395 p_start_person AND p_end_person
1396 AND paf.assignment_id = paa_init.assignment_id
1397 AND (to_char(paf.effective_start_date,'Month-YYYY')=to_char(g_end_date,'Month-YYYY')
1398 OR to_char(paf.effective_end_date,'Month-YYYY')=to_char(g_end_date,'Month-YYYY')
1399 OR g_end_date between paf.effective_start_date and paf.effective_end_date)
1400 AND ppa_init.effective_date BETWEEN g_start_date AND g_end_date
1401 AND hoi.org_information_context = 'PER_IN_PF_DF'
1402 AND hoi.org_information10||hoi.org_information9 = p_business_no
1403 AND hoi.org_information10 IS NOT NULL
1404 AND hoi.org_information9 IS NOT NULL
1405 AND TO_CHAR (hoi.organization_id) IN
1406 (SELECT scl.segment2
1407 FROM hr_soft_coding_keyflex scl
1408 WHERE scl.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
1409 AND scl.enabled_flag = 'Y')
1410 AND NOT EXISTS ( SELECT paa.assignment_id
1411 FROM pay_assignment_actions paa
1412 ,pay_action_information pai
1413 ,hr_organization_information hoi
1414 ,pay_payroll_actions ppa
1415 WHERE paa.assignment_id = paa_init.assignment_id
1416 AND paa.assignment_action_id = pai.action_context_id
1417 AND ppa.payroll_action_id = paa.payroll_action_id
1418 AND ppa.report_type = 'IN_PF_ARCHIVE'
1419 AND ppa.action_type = 'X'
1420 AND ppa.action_status = 'C'
1421 AND pai.action_context_type = 'AAP'
1422 AND hoi.org_information7 = pai.action_information2
1423 AND pai.action_information_category = 'IN_PF_PERSON_DTLS'
1424 AND hoi.org_information_context = 'PER_IN_COMPANY_RECEP_MAP'
1425 AND hoi.org_information3 = p_business_no
1426 AND hoi.org_information1 = g_challan_year
1427 AND hoi.org_information2 = g_challan_mth
1428 )
1429 UNION
1430 SELECT DISTINCT paa_init.assignment_id
1431 FROM pay_assignment_actions paa_init,
1432 per_periods_of_service pps,
1433 pay_payroll_actions ppa_init,
1434 per_assignments_f paf,
1435 hr_organization_information hoi
1436 WHERE ppa_init.payroll_action_id = paa_init.payroll_action_id
1437 AND paf.period_of_service_id = pps.period_of_service_id
1438 AND ppa_init.action_type IN ('P','U','I')
1439 AND ppa_init.action_status = 'C'
1440 AND ppa_init.business_group_id = g_bg_id
1441 AND p_payroll_action_id IS NOT NULL
1442 AND paf.person_id BETWEEN
1443 p_start_person AND p_end_person
1444 AND paf.assignment_id = paa_init.assignment_id
1445 AND TO_CHAR(pps.actual_termination_date,'Month-YYYY') = to_char(g_start_date,'Month-YYYY')
1446 AND (to_char(paf.effective_start_date,'Month-YYYY')=to_char(g_end_date,'Month-YYYY')
1447 OR to_char(paf.effective_end_date,'Month-YYYY')=to_char(g_end_date,'Month-YYYY')
1448 OR g_end_date between paf.effective_start_date and paf.effective_end_date)
1449 AND ppa_init.effective_date BETWEEN g_start_date AND g_end_date
1450 AND hoi.org_information_context = 'PER_IN_PF_DF'
1451 AND hoi.org_information10||hoi.org_information9 = p_business_no
1452 AND hoi.org_information10 IS NOT NULL
1453 AND hoi.org_information9 IS NOT NULL
1454 AND TO_CHAR (hoi.organization_id) IN
1455 (SELECT scl.segment2
1456 FROM hr_soft_coding_keyflex scl
1457 WHERE scl.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
1458 AND scl.enabled_flag = 'Y')
1459 AND NOT EXISTS ( SELECT paa.assignment_id
1460 FROM pay_assignment_actions paa
1461 ,pay_action_information pai
1462 ,hr_organization_information hoi
1463 ,hr_organization_units hou
1464 ,pay_payroll_actions ppa
1465 WHERE paa.assignment_id = paa_init.assignment_id
1466 AND paa.assignment_action_id = pai.action_context_id
1467 AND ppa.payroll_action_id = paa.payroll_action_id
1468 AND hoi.organization_id = hou.organization_id
1469 AND ppa.report_type = 'IN_PF_ARCHIVE'
1470 AND ppa.action_type = 'X'
1471 AND ppa.action_status = 'C'
1472 AND pai.action_context_type = 'AAP'
1473 AND hoi.org_information7 = pai.action_information2
1474 AND pai.action_information_category = 'IN_PF_PERSON_DTLS'
1475 AND hoi.org_information_context = 'PER_IN_COMPANY_RECEP_MAP'
1476 AND hoi.org_information3 = p_business_no
1477 AND hoi.org_information1 = g_challan_year
1478 AND hoi.org_information2 = g_challan_mth
1479 AND TO_CHAR(fnd_date.canonical_to_date(pai.action_information8),'Month-YYYY') = TO_CHAR (g_start_date,'Month-YYYY')
1480 );
1481
1482
1483 /*Cursor to get the Assignments that needs to be archived for
1484 the Revised Monthly Return.Will pick up assignments having
1485 'P','U','I' in Current Month and attached to a PF Organization
1486 having the Business Number*/
1487
1488 CURSOR csr_process_rev_assignments(p_business_no VARCHAR2)
1489 IS
1490 SELECT DISTINCT paa_init.assignment_id
1491 FROM pay_assignment_actions paa_init,
1492 pay_payroll_actions ppa_init,
1493 per_assignments_f paf,
1494 hr_organization_information hoi
1495 WHERE ppa_init.payroll_action_id = paa_init.payroll_action_id
1496 AND ppa_init.action_type IN ('P','U','I')
1497 AND ppa_init.action_status = 'C'
1498 AND ppa_init.business_group_id = g_bg_id
1499 AND p_payroll_action_id IS NOT NULL
1500 AND paf.person_id BETWEEN
1501 p_start_person AND p_end_person
1502 AND paf.assignment_id = paa_init.assignment_id
1503 AND (to_char(paf.effective_start_date,'Month-YYYY')=to_char(g_end_date,'Month-YYYY')
1504 OR to_char(paf.effective_end_date,'Month-YYYY')=to_char(g_end_date,'Month-YYYY')
1505 OR g_end_date between paf.effective_start_date and paf.effective_end_date)
1506 AND ppa_init.effective_date BETWEEN g_start_date AND g_end_date
1507 AND hoi.org_information_context = 'PER_IN_PF_DF'
1508 AND hoi.org_information10||hoi.org_information9 = p_business_no
1509 AND hoi.org_information10 IS NOT NULL
1510 AND hoi.org_information9 IS NOT NULL
1511 AND TO_CHAR (hoi.organization_id) IN
1512 (SELECT scl.segment2
1513 FROM hr_soft_coding_keyflex scl
1514 WHERE scl.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
1515 AND scl.enabled_flag = 'Y');
1516
1517
1518 /*Cursor to get the new Challans that needs to be archived for
1519 the Supplementary Monthly Return.
1520 Will pick up the Challans for the Business Numbers which were
1521 not archived in the Archive mapped to the 'PER_IN_COMPANY_RECEP_MAP'*/
1522
1523 CURSOR csr_challans(p_pf_org NUMBER
1524 ,p_business_no NUMBER )
1525 IS
1526 SELECT
1527 hoi_challan.org_information12 Challan_Ref
1528 FROM hr_organization_information hoi_challan
1529 WHERE hoi_challan.organization_id = p_pf_org
1530 AND hoi_challan.org_information_context ='PER_IN_PF_CHALLAN_INFO'
1531 AND hoi_challan.org_information1 = g_challan_year
1532 AND hoi_challan.org_information2 = g_challan_mth
1533 AND NOT EXISTS (SELECT pai.action_information8
1534 FROM pay_action_information pai,
1535 pay_payroll_Actions ppa
1536 WHERE pai.action_information8 = hoi_challan.org_information12
1537 AND pai.action_information_category = 'IN_PF_CHALLAN'
1538 AND pai.action_context_type = 'PA'
1539 AND pai.action_information3 = p_pf_org
1540 AND pai.action_context_id = ppa.payroll_action_id
1541 AND ppa.action_type ='X'
1542 AND ppa.action_status='C'
1543 AND ppa.report_type='IN_PF_ARCHIVE'
1544 AND pai.action_context_id IN
1545 (SELECT action_context_id
1546 FROM pay_action_information painfo
1547 WHERE painfo.action_information2 IN (SELECT hoi.org_information7
1548 FROM hr_organization_information hoi ,
1549 hr_organization_units hou
1550 WHERE hoi.organization_id = hou.organization_id
1551 AND hoi.org_information_context = 'PER_IN_COMPANY_RECEP_MAP'
1552 AND hoi.org_information3 = p_business_no
1553 AND hoi.org_information1 = g_challan_year
1554 AND hoi.org_information2 = g_challan_mth
1555 )
1556 AND painfo.action_context_type = 'PA'
1557 AND painfo.action_information_category = 'IN_PF_BUSINESS_NUMBER')
1558
1559 );
1560
1561 CURSOR csr_revised_challans(p_pf_org NUMBER)
1562 IS
1563 SELECT
1564 hoi_challan.org_information12 Challan_Ref
1565 FROM hr_organization_information hoi_challan
1566 WHERE hoi_challan.organization_id = p_pf_org
1567 AND hoi_challan.org_information_context ='PER_IN_PF_CHALLAN_INFO'
1568 AND hoi_challan.org_information1 = g_challan_year
1569 AND hoi_challan.org_information2 = g_challan_mth ;
1570
1571
1572 /*Cursor to get the Business Numbers for which Regular Return has
1573 been mapped to 'PER_IN_COMPANY_RECEP_MAP'.
1574 For a Business Number to run Supplementary/Revised archive a
1575 Regular archive needs to be mapped to 'PER_IN_COMPANY_RECEP_MAP'.
1576 */
1577 CURSOR csr_reg_return
1578 IS
1579 SELECT org_information3 bus_no
1580 FROM hr_organization_information
1581 WHERE org_information_context = 'PER_IN_COMPANY_RECEP_MAP'
1582 AND org_information1 = g_challan_year
1583 AND org_information2 = g_challan_mth
1584 AND org_information3 = NVL(g_business_no , org_information3)
1585 AND org_information4 = 'R'
1586 GROUP BY org_information3 ;
1587
1588 CURSOR csr_arch_ref(p_business_no IN NUMBER)
1589 IS
1590 SELECT DISTINCT org_information7 Archive_Ref_No
1591 FROM hr_organization_information
1592 WHERE org_information_context = 'PER_IN_COMPANY_RECEP_MAP'
1593 AND org_information1 = g_challan_year
1594 AND org_information2 = g_challan_mth
1595 AND org_information3 = NVL(p_business_no,org_information3) ;
1596
1597
1598 CURSOR csr_action_context_id(p_arc_ref VARCHAR2
1599 ,p_business_no IN NUMBER)
1600 IS
1601 SELECT DISTINCT action_context_id
1602 FROM pay_action_information pai,
1603 pay_assignment_actions paa,
1604 pay_payroll_Actions ppa
1605 WHERE action_information2 = p_arc_ref
1606 AND action_context_type = 'AAP'
1607 AND action_information_category = 'IN_PF_PERSON_DTLS'
1608 AND action_information1 = nvl(p_business_no,action_information1)
1609 AND pai.action_context_id = paa.assignment_action_id
1610 AND paa.payroll_action_id = ppa.payroll_action_id
1611 AND ppa.action_type ='X'
1612 AND ppa.action_status='C'
1613 AND ppa.report_type='IN_PF_ARCHIVE'
1614 ;
1615
1616
1617
1618
1619 CURSOR csr_pf_org_id(p_business_no IN NUMBER)
1620 IS
1621 SELECT hr_pf_org.organization_id pf_org --PF Org Id
1622 FROM hr_organization_information hr_pf_org
1623 WHERE org_information_context = 'PER_IN_PF_DF'
1624 AND org_information10||org_information9 = p_business_no ;
1625
1626
1627
1628
1629
1630 CURSOR csr_pay_action_level_check(p_payroll_action_id NUMBER
1631 ,p_business_no IN NUMBER
1632 )
1633 IS
1634 SELECT 1
1635 FROM pay_action_information pai
1636 WHERE pai.action_information_category = 'IN_PF_ORG'
1637 AND pai.action_context_type = 'PA'
1638 AND pai.action_context_id = p_payroll_action_id
1639 AND pai.action_information1 = p_business_no
1640 AND ROWNUM =1;
1641
1642 CURSOR csr_pay_action_level_pf_check(p_payroll_action_id NUMBER
1643 ,p_pf_org IN NUMBER
1644 )
1645 IS
1646 SELECT 1
1647 FROM pay_action_information pai
1648 WHERE pai.action_information_category = 'IN_PF_CHALLAN'
1649 AND pai.action_context_type = 'PA'
1650 AND pai.action_context_id = p_payroll_action_id
1651 AND pai.action_information3 = p_pf_org
1652 AND ROWNUM =1;
1653
1654
1655
1656
1657
1658 BEGIN
1659 --
1660 g_debug := hr_utility.debug_enabled;
1661 l_procedure := g_package || 'assignment_action_code';
1662 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1663 pay_in_utils.set_location(g_debug,'p_start_person: '||p_start_person,10);
1664 pay_in_utils.set_location(g_debug,'p_end_person: '||p_end_person,10);
1665
1666 initialization_code (p_payroll_action_id);
1667
1668
1669 IF g_return_type = 'S' --Archive for Supplementary Return Starts.
1670 THEN
1671 pay_in_utils.set_location(g_debug,'Entering: Supplementary',5);
1672 FOR rec_reg_return IN csr_reg_return
1673 LOOP
1674 pay_in_utils.set_location(g_debug,'Business Number:'||rec_reg_return.bus_no,15);
1675 FOR rec_pf_org_id IN csr_pf_org_id(rec_reg_return.bus_no)
1676 LOOP
1677 l_pf_check := NULL ;
1678
1679 OPEN csr_pay_action_level_pf_check(p_payroll_action_id,rec_pf_org_id.pf_org);
1680 FETCH csr_pay_action_level_pf_check INTO l_pf_check;
1681 CLOSE csr_pay_action_level_pf_check;
1682 IF l_pf_check IS NULL THEN
1683 FOR rec_challans IN csr_challans(rec_pf_org_id.pf_org,rec_reg_return.bus_no)
1684 LOOP
1685 archive_pf_challan_dtls(p_payroll_action_id,rec_pf_org_id.pf_org,rec_challans.Challan_Ref,rec_reg_return.bus_no);
1686 l_supp := TRUE ;
1687 END LOOP;
1688 END IF ;
1689
1690
1691 END LOOP ;
1692 FOR csr_rec IN csr_process_supp_assignments(rec_reg_return.bus_no)
1693 LOOP
1694 SELECT pay_assignment_actions_s.NEXTVAL
1695 INTO l_action_id
1696 FROM dual;
1697
1698 IF g_debug THEN
1699 pay_in_utils.trace('l_action_id : ',l_action_id);
1700 pay_in_utils.trace('csr_rec.assignment_id : ',csr_rec.assignment_id);
1701 END IF ;
1702
1703 hr_nonrun_asact.insact(lockingactid => l_action_id
1704 ,assignid => csr_rec.assignment_id
1705 ,pactid => p_payroll_action_id
1706 ,chunk => p_chunk
1707 );
1708
1709 /*Locks all the Assignment_action_id s for the archives that have been
1710 mapped to 'PER_IN_COMPANY_RECEP_MAP'*/
1711 FOR rec_arch_ref IN csr_arch_ref(rec_reg_return.bus_no)
1712 LOOP
1713 FOR rec_action_context_id IN csr_action_context_id(rec_arch_ref.Archive_Ref_No,rec_reg_return.bus_no)
1714 LOOP
1715 hr_nonrun_asact.insint(l_action_id,rec_action_context_id.action_context_id);
1716 END LOOP ;
1717 END LOOP ;
1718 l_supp := TRUE ;
1719 END LOOP ;
1720
1721 /*If some new Challan/Assignment is archived then only archive the
1722 PF Org Details/Business Number Details.*/
1723 IF l_supp THEN
1724 l_check := NULL ;
1725
1726 OPEN csr_pay_action_level_check(p_payroll_action_id,rec_reg_return.bus_no);
1727 FETCH csr_pay_action_level_check INTO l_check;
1728 CLOSE csr_pay_action_level_check;
1729
1730 IF l_check IS NULL
1731 THEN
1732 archive_pf_org_dtls(p_payroll_action_id,rec_reg_return.bus_no);
1733 END IF ;
1734 END IF ;
1735 l_supp := FALSE ;
1736 END LOOP ;
1737 --Archive for Supplementary Return Ends.
1738
1739 ELSIF g_return_type = 'V' --Archive for Revised Return Starts.
1740 THEN
1741 pay_in_utils.set_location(g_debug,'Entering: Revised',5);
1742 FOR rec_reg_return IN csr_reg_return
1743 LOOP
1744 pay_in_utils.set_location(g_debug,'Business Number:'||rec_reg_return.bus_no,15);
1745 FOR rec_pf_org_id IN csr_pf_org_id(rec_reg_return.bus_no)
1746 LOOP
1747
1748 l_pf_check := NULL ;
1749
1750 OPEN csr_pay_action_level_pf_check(p_payroll_action_id,rec_pf_org_id.pf_org);
1751 FETCH csr_pay_action_level_pf_check INTO l_pf_check;
1752 CLOSE csr_pay_action_level_pf_check;
1753
1754 IF l_pf_check IS NULL THEN
1755 FOR rec_challans IN csr_revised_challans(rec_pf_org_id.pf_org)
1756 LOOP
1757 archive_pf_challan_dtls(p_payroll_action_id,rec_pf_org_id.pf_org,rec_challans.Challan_Ref,rec_reg_return.bus_no);
1758 l_supp := TRUE ;
1759 END LOOP;
1760 END IF ;
1761
1762
1763 END LOOP ;
1764
1765 FOR csr_rec IN csr_process_rev_assignments(rec_reg_return.bus_no)
1766 LOOP
1767 SELECT pay_assignment_actions_s.NEXTVAL
1768 INTO l_action_id
1769 FROM dual;
1770
1771 IF g_debug THEN
1772 pay_in_utils.trace('l_action_id : ',l_action_id);
1773 pay_in_utils.trace('csr_rec.assignment_id : ',csr_rec.assignment_id);
1774 END IF ;
1775
1776 hr_nonrun_asact.insact(lockingactid => l_action_id
1777 ,assignid => csr_rec.assignment_id
1778 ,pactid => p_payroll_action_id
1779 ,chunk => p_chunk
1780 );
1781
1782 /*Locks all the Assignment_action_id s for the archives that have been
1783 mapped to 'PER_IN_COMPANY_RECEP_MAP'*/
1784 FOR rec_arch_ref IN csr_arch_ref(rec_reg_return.bus_no)
1785 LOOP
1786 FOR rec_action_context_id IN csr_action_context_id(rec_arch_ref.Archive_Ref_No,rec_reg_return.bus_no)
1787 LOOP
1788 hr_nonrun_asact.insint(l_action_id,rec_action_context_id.action_context_id);
1789 END LOOP ;
1790 END LOOP ;
1791 l_supp := TRUE ;
1792 END LOOP ;
1793
1794 /*If some Challan/Assignment is archived then only archive the
1795 PF Org Details/Business Number Details.*/
1796 IF l_supp THEN
1797 l_check := NULL ;
1798
1799 OPEN csr_pay_action_level_check(p_payroll_action_id,rec_reg_return.bus_no);
1800 FETCH csr_pay_action_level_check INTO l_check;
1801 CLOSE csr_pay_action_level_check;
1802
1803 IF l_check IS NULL
1804 THEN
1805 archive_pf_org_dtls(p_payroll_action_id,rec_reg_return.bus_no);
1806 END IF ;
1807 END IF ;
1808 l_supp := FALSE ;
1809 END LOOP ;
1810 --Archive for Revised Return Ends.
1811
1812 ELSE --Archive for Regular Return Starts.
1813 pay_in_utils.set_location(g_debug,'Entering: Regular',5);
1814 FOR csr_rec IN csr_process_assignments
1815 LOOP
1816
1817 SELECT pay_assignment_actions_s.NEXTVAL
1818 INTO l_action_id
1819 FROM dual;
1820
1821 IF g_debug THEN
1822 pay_in_utils.trace('l_action_id : ',l_action_id);
1823 pay_in_utils.trace('csr_rec.assignment_id : ',csr_rec.assignment_id);
1824 END IF ;
1825
1826 hr_nonrun_asact.insact(lockingactid => l_action_id
1827 ,assignid => csr_rec.assignment_id
1828 ,pactid => p_payroll_action_id
1829 ,chunk => p_chunk
1830 );
1831
1832 END LOOP ;
1833 --Archive for Regular Return Ends.
1834 END IF ;
1835
1836 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure,30);
1837 --
1838 EXCEPTION
1839 --
1840 WHEN OTHERS THEN
1841 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR',
1842 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
1843 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 50);
1844 pay_in_utils.trace(l_message,l_procedure);
1845 RAISE;
1846 --
1847 END assignment_action_code;
1848
1849
1850
1851 --------------------------------------------------------------------------
1852 -- --
1853 -- Name : ARCHIVE_CODE --
1854 -- Type : PROCEDURE --
1855 -- Access : Public --
1856 -- Description : Procedure to call the internal procedures to --
1857 -- actually archive the data. The procedures --
1858 -- called are --
1859 -- archive_pf_balances --
1860 -- archive_pf_emp_dtls --
1861 -- archive_pf_org_dtls --
1862 -- archive_pf_challan_dtls --
1863 -- --
1864 -- Parameters : --
1865 -- IN : p_assignment_action_id NUMBER --
1866 -- p_effective_date DATE --
1867 -- --
1868 -- OUT : N/A --
1869 -- --
1870 -- Change History : --
1871 --------------------------------------------------------------------------
1872 -- Rev# Date Userid Description --
1873 --------------------------------------------------------------------------
1874 -- 115.0 01-Aug-2007 rsaharay Initial Version --
1875 --------------------------------------------------------------------------
1876 --
1877
1878 PROCEDURE archive_code (
1879 p_assignment_action_id IN NUMBER
1880 ,p_effective_date IN DATE
1881
1882 )
1883 IS
1884 --
1885 l_procedure VARCHAR2(100);
1886 l_message VARCHAR2(255);
1887 l_assignment_id NUMBER ;
1888 l_arc_pay_action_id NUMBER ;
1889 l_person_id NUMBER ;
1890 l_run_asg_action_id NUMBER ;
1891 l_check NUMBER;
1892 l_pf_chk NUMBER;
1893 l_pf_check NUMBER;
1894
1895
1896 CURSOR csr_get_assignment_pact_id
1897 IS
1898 SELECT paa.assignment_id
1899 ,paa.payroll_action_id
1900 ,paf.person_id
1901 FROM pay_assignment_actions paa
1902 ,per_assignments_f paf
1903 WHERE paa.assignment_action_id = p_assignment_action_id
1904 AND paa.assignment_id = paf.assignment_id
1905 AND ROWNUM =1;
1906
1907
1908 CURSOR csr_get_pf_archival_details(p_start_date DATE
1909 ,p_end_date DATE
1910 ,p_assignment_id NUMBER)
1911 IS
1912 SELECT TO_NUMBER(SUBSTR(MAX(LPAD(paa.action_sequence,15,'0')||paa.assignment_action_id),16)) run_asg_action_id
1913 FROM pay_assignment_actions paa
1914 ,pay_payroll_actions ppa
1915 ,per_assignments_f paf
1916 WHERE paf.assignment_id = paa.assignment_id
1917 AND paf.assignment_id = p_assignment_id
1918 AND paa.payroll_action_id = ppa.payroll_action_id
1919 AND ppa.action_type IN('R','Q','I','B')
1920 AND ppa.payroll_id = paf.payroll_id
1921 AND ppa.action_status ='C'
1922 AND ppa.effective_date between p_start_date and p_end_date
1923 AND paa.source_action_id IS NULL
1924 AND (1 = DECODE(ppa.action_type,'I',1,0)
1925 OR EXISTS (SELECT ''
1926 FROM pay_action_interlocks intk,
1927 pay_assignment_actions paa1,
1928 pay_payroll_actions ppa1
1929 WHERE intk.locked_action_id = paa.assignment_Action_id
1930 AND intk.locking_action_id = paa1.assignment_action_id
1931 AND paa1.payroll_action_id =ppa1.payroll_action_id
1932 AND paa1.assignment_id = p_assignment_id
1933 AND ppa1.action_type in('P','U')
1934 AND ppa.action_type in('R','Q','B')
1935 AND ppa1.action_status ='C'
1936 AND ppa1.effective_date BETWEEN p_start_date and p_end_date
1937 AND ROWNUM =1 ));
1938
1939 CURSOR csr_get_pf_org
1940 IS
1941 SELECT hr_pf_org.organization_id pf_org,
1942 org_information10 base_bus_no,
1943 org_information10||org_information9 business_number
1944 FROM hr_organization_information hr_pf_org
1945 ,hr_organization_units hou
1946 WHERE hou.organization_id = hr_pf_org.organization_id
1947 AND hou.business_group_id = g_bg_id
1948 AND org_information_context = 'PER_IN_PF_DF'
1949 AND org_information10||org_information9 = NVL(g_business_no,org_information10||org_information9)
1950 AND org_information10 IS NOT NULL
1951 AND org_information9 IS NOT NULL ;
1952
1953
1954
1955 CURSOR csr_challans(p_pf_org VARCHAR2 )
1956 IS
1957 SELECT
1958 hoi_challan.org_information12 Challan_Ref
1959 FROM hr_organization_information hoi_challan
1960 WHERE hoi_challan.organization_id = p_pf_org
1961 AND hoi_challan.org_information_context ='PER_IN_PF_CHALLAN_INFO'
1962 AND hoi_challan.org_information1 = g_challan_year
1963 AND hoi_challan.org_information2 = g_challan_mth ;
1964
1965
1966 CURSOR csr_pay_action_level_check(p_payroll_action_id NUMBER
1967 ,p_business_no IN NUMBER
1968 )
1969 IS
1970 SELECT 1
1971 FROM pay_action_information pai
1972 WHERE pai.action_information_category = 'IN_PF_ORG'
1973 AND pai.action_context_type = 'PA'
1974 AND pai.action_context_id = p_payroll_action_id
1975 AND pai.action_information1 = p_business_no
1976 AND ROWNUM =1;
1977
1978
1979 CURSOR csr_pay_action_level_pf_check(p_payroll_action_id NUMBER
1980 ,p_pf_org IN NUMBER
1981 )
1982 IS
1983 SELECT 1
1984 FROM pay_action_information pai
1985 WHERE pai.action_information_category = 'IN_PF_CHALLAN'
1986 AND pai.action_context_type = 'PA'
1987 AND pai.action_context_id = p_payroll_action_id
1988 AND pai.action_information3 = p_pf_org
1989 AND ROWNUM =1;
1990
1991
1992 CURSOR csr_chk_pf_org(p_pf_org NUMBER ,
1993 p_assignment NUMBER )
1994 IS
1995 SELECT 1
1996 FROM per_assignments_f paf,
1997 hr_soft_coding_keyflex scl
1998 WHERE (to_char(paf.effective_start_date,'Month-YYYY')=to_char(g_end_date,'Month-YYYY')
1999 OR to_char(paf.effective_end_date,'Month-YYYY')=to_char(g_end_date,'Month-YYYY')
2000 OR g_end_date between paf.effective_start_date and paf.effective_end_date)
2001 AND scl.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
2002 AND scl.enabled_flag = 'Y'
2003 AND scl.segment2 = p_pf_org
2004 AND assignment_id = p_assignment;
2005
2006 --
2007 BEGIN
2008 --
2009 g_debug := hr_utility.debug_enabled;
2010 l_procedure := g_package || 'archive_code';
2011 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
2012
2013 OPEN csr_get_assignment_pact_id;
2014 FETCH csr_get_assignment_pact_id INTO l_assignment_id ,l_arc_pay_action_id,l_person_id;
2015 CLOSE csr_get_assignment_pact_id;
2016
2017 OPEN csr_get_pf_archival_details(g_start_date,g_end_date,l_assignment_id);
2018 FETCH csr_get_pf_archival_details INTO l_run_asg_action_id;
2019 CLOSE csr_get_pf_archival_details;
2020
2021 IF l_run_asg_action_id IS NOT NULL
2022 THEN
2023
2024
2025 FOR rec_pf_org IN csr_get_pf_org
2026 LOOP
2027 l_pf_chk:=0;
2028 OPEN csr_chk_pf_org(rec_pf_org.pf_org,l_assignment_id);
2029 FETCH csr_chk_pf_org INTO l_pf_chk;
2030 CLOSE csr_chk_pf_org ;
2031
2032 IF l_pf_chk =1
2033 THEN
2034 pay_in_utils.set_location(g_debug,'Archiving Employee Dtls ',5);
2035 archive_pf_emp_dtls(l_run_asg_action_id,p_assignment_action_id,l_assignment_id,rec_pf_org.pf_org,rec_pf_org.business_number);
2036 archive_pf_balances(l_run_asg_action_id,p_assignment_action_id,l_assignment_id,rec_pf_org.pf_org,rec_pf_org.business_number);
2037 END IF ;
2038
2039
2040 OPEN csr_pay_action_level_check(l_arc_pay_action_id,rec_pf_org.business_number);
2041 FETCH csr_pay_action_level_check INTO l_check;
2042 CLOSE csr_pay_action_level_check;
2043
2044 /*Org Level Data is archived in Procedure assignment_action_code for Revised/Supplementary Return.*/
2045 IF l_check IS NULL AND g_return_type = 'R'
2046 THEN
2047 pay_in_utils.set_location(g_debug,'Archiving Org Dtls ',15);
2048 archive_pf_org_dtls(l_arc_pay_action_id,rec_pf_org.business_number);
2049 END IF ;
2050 l_check := NULL ;
2051
2052 OPEN csr_pay_action_level_pf_check(l_arc_pay_action_id,rec_pf_org.pf_org);
2053 FETCH csr_pay_action_level_pf_check INTO l_pf_check;
2054 CLOSE csr_pay_action_level_pf_check;
2055
2056 /*Org Level Data is archived in Procedure assignment_action_code for Revised/Supplementary Return.*/
2057 IF l_pf_check IS NULL AND g_return_type = 'R'
2058 THEN
2059 FOR rec_challans IN csr_challans(rec_pf_org.pf_org)
2060 LOOP
2061 pay_in_utils.set_location(g_debug,'Archiving Challan Dtls ',25);
2062 archive_pf_challan_dtls(l_arc_pay_action_id,rec_pf_org.pf_org,rec_challans.Challan_Ref,rec_pf_org.business_number);
2063 END LOOP ;
2064 END IF ;
2065 l_pf_check := NULL ;
2066
2067
2068 END LOOP ;
2069 END IF ;
2070
2071 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure,20);
2072
2073 EXCEPTION
2074 WHEN OTHERS THEN
2075
2076 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR',
2077 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
2078 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 280);
2079 pay_in_utils.trace(l_message,l_procedure);
2080
2081 RAISE;
2082 --
2083 END archive_code;
2084
2085 END ;