[Home] [Help]
60:
61: BEGIN
62:
63: l_procedure := g_package ||'get_eit_remarks';
64: pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
65:
66: IF g_debug THEN
67: pay_in_utils.trace('PF/ESI Number : ',p_number);
68: pay_in_utils.trace('Report Type : ',p_report_type);
63: l_procedure := g_package ||'get_eit_remarks';
64: pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
65:
66: IF g_debug THEN
67: pay_in_utils.trace('PF/ESI Number : ',p_number);
68: pay_in_utils.trace('Report Type : ',p_report_type);
69: pay_in_utils.trace('Period : ',p_period);
70: pay_in_utils.trace('Month Number : ',p_mon);
71: END IF;
64: pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
65:
66: IF g_debug THEN
67: pay_in_utils.trace('PF/ESI Number : ',p_number);
68: pay_in_utils.trace('Report Type : ',p_report_type);
69: pay_in_utils.trace('Period : ',p_period);
70: pay_in_utils.trace('Month Number : ',p_mon);
71: END IF;
72:
65:
66: IF g_debug THEN
67: pay_in_utils.trace('PF/ESI Number : ',p_number);
68: pay_in_utils.trace('Report Type : ',p_report_type);
69: pay_in_utils.trace('Period : ',p_period);
70: pay_in_utils.trace('Month Number : ',p_mon);
71: END IF;
72:
73: OPEN c_remarks;
66: IF g_debug THEN
67: pay_in_utils.trace('PF/ESI Number : ',p_number);
68: pay_in_utils.trace('Report Type : ',p_report_type);
69: pay_in_utils.trace('Period : ',p_period);
70: pay_in_utils.trace('Month Number : ',p_mon);
71: END IF;
72:
73: OPEN c_remarks;
74: FETCH c_remarks INTO l_remarks;
74: FETCH c_remarks INTO l_remarks;
75: CLOSE c_remarks;
76:
77: IF g_debug THEN
78: pay_in_utils.trace('Remarks at AEI level : ',l_remarks);
79: END IF;
80:
81: pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 20);
82:
77: IF g_debug THEN
78: pay_in_utils.trace('Remarks at AEI level : ',l_remarks);
79: END IF;
80:
81: pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 20);
82:
83: RETURN l_remarks;
84: END get_eit_remarks;
85:
123:
124:
125: BEGIN
126: l_procedure := g_package ||'insert_record';
127: pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
128:
129: l_bg_id:=fnd_profile.value('PER_BUSINESS_GROUP_ID');
130:
131: -- pay_us_employee_payslip_web.get_format_value(l_bg_id,nvl(,0));
168: l_count := l_count + 1;
169: pay_in_xml_utils.multiColumnar('monthly',pay_in_xml_utils.gXMLTable,l_count,p_xml_data);
170: pay_in_xml_utils.gXMLTable.delete;
171:
172: pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 20);
173:
174: END insert_record;
175:
176: --------------------------------------------------------------------------
211: l_procedure VARCHAR2(100);
212:
213: BEGIN
214: l_procedure := g_package ||'insert_null_record';
215: pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
216:
217:
218: l_bg_id:=fnd_profile.value('PER_BUSINESS_GROUP_ID');
219: l_count:=1;
254: pay_in_xml_utils.gXMLTable(l_count).Value :=p_month_name;
255: END IF;
256: l_count := l_count + 1;
257: pay_in_xml_utils.multiColumnar('t_month',pay_in_xml_utils.gXMLTable,l_count,p_xml_data);
258: pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 20);
259:
260: END insert_null_record;
261:
262: --------------------------------------------------------------------------
290: l_procedure VARCHAR2(100);
291:
292: BEGIN
293: l_procedure := g_package ||'insert_ch_record';
294: pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
295:
296: pay_in_xml_utils.gXMLTable.DELETE;
297: l_bg_id:=fnd_profile.value('PER_BUSINESS_GROUP_ID');
298: l_count:=1;
302: l_count := l_count + 1;
303:
304: pay_in_xml_utils.multiColumnar('monthly',pay_in_xml_utils.gXMLTable,l_count,p_xml_data);
305: pay_in_xml_utils.gXMLTable.DELETE;
306: pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 20);
307:
308: END insert_ch_record;
309:
310: --------------------------------------------------------------------------
343:
344: BEGIN
345:
346: l_procedure := g_package ||'insert_null_form7_record';
347: pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
348:
349: l_bg_id:= fnd_profile.value('PER_BUSINESS_GROUP_ID');
350: l_count:=1;
351: --PF Salary _ASG_ORG_PTD
556:
557:
558: BEGIN
559: l_procedure := g_package ||'create_pt_form3_xml';
560: pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
561:
562: -- l_slab_2 := 30;
563: l_slab_3 := 60;
564: l_slab_4 := 120;
586: l_tag :=pay_in_xml_utils.getTag('c_sys_date_in_hh_mm_ss',l_sys_date_time);
587: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
588:
589: IF g_debug THEN
590: pay_in_utils.trace('Business Group id ',l_bg_id);
591: pay_in_utils.trace('System Date and Time ',l_sys_date_time);
592: END IF;
593:
594:
587: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
588:
589: IF g_debug THEN
590: pay_in_utils.trace('Business Group id ',l_bg_id);
591: pay_in_utils.trace('System Date and Time ',l_sys_date_time);
592: END IF;
593:
594:
595: pay_in_utils.set_location(g_debug,l_procedure, 20);
591: pay_in_utils.trace('System Date and Time ',l_sys_date_time);
592: END IF;
593:
594:
595: pay_in_utils.set_location(g_debug,l_procedure, 20);
596:
597: period_start_year := SUBSTR(p_year, 1, 4);
598: IF p_frequency = 'A' THEN
599: period_start := TO_DATE('01-03-'||period_start_year, 'DD-MM-YYYY');
608: period_start := ADD_MONTHS(TO_DATE('01-03-'|| period_start_year, 'DD-MM-YYYY'), MOD(TO_NUMBER(p_period),12));
609: period_end := ADD_MONTHS(period_start, 1) -1;
610: END IF;
611:
612: pay_in_utils.set_location(g_debug,l_procedure, 30);
613:
614: IF g_debug THEN
615: pay_in_utils.trace('period_start_year ',period_start_year);
616: pay_in_utils.trace('period_start ',period_start);
611:
612: pay_in_utils.set_location(g_debug,l_procedure, 30);
613:
614: IF g_debug THEN
615: pay_in_utils.trace('period_start_year ',period_start_year);
616: pay_in_utils.trace('period_start ',period_start);
617: pay_in_utils.trace('period_end ',period_end);
618: END IF;
619:
612: pay_in_utils.set_location(g_debug,l_procedure, 30);
613:
614: IF g_debug THEN
615: pay_in_utils.trace('period_start_year ',period_start_year);
616: pay_in_utils.trace('period_start ',period_start);
617: pay_in_utils.trace('period_end ',period_end);
618: END IF;
619:
620:
613:
614: IF g_debug THEN
615: pay_in_utils.trace('period_start_year ',period_start_year);
616: pay_in_utils.trace('period_start ',period_start);
617: pay_in_utils.trace('period_end ',period_end);
618: END IF;
619:
620:
621: FOR c_rec IN c_distinct_org (period_start
621: FOR c_rec IN c_distinct_org (period_start
622: ,period_end)
623: LOOP
624: l_tag :='
625: pay_in_utils.set_location(g_debug,l_procedure, 40);
626: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
627:
628: IF g_debug THEN
629: pay_in_utils.trace('c_rec.org_id ',c_rec.org_id);
625: pay_in_utils.set_location(g_debug,l_procedure, 40);
626: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
627:
628: IF g_debug THEN
629: pay_in_utils.trace('c_rec.org_id ',c_rec.org_id);
630: END IF;
631:
632: fnd_file.put_line(fnd_file.log,'c_rec.org_id '|| c_rec.org_id);
633:
637: FETCH c_max_pa_action_id INTO l_payroll_act_id;
638: CLOSE c_max_pa_action_id ;
639:
640: IF g_debug THEN
641: pay_in_utils.trace('Payroll Action id ',l_payroll_act_id);
642: END IF;
643:
644: pay_in_utils.set_location(g_debug,l_procedure, 50);
645:
640: IF g_debug THEN
641: pay_in_utils.trace('Payroll Action id ',l_payroll_act_id);
642: END IF;
643:
644: pay_in_utils.set_location(g_debug,l_procedure, 50);
645:
646: OPEN c_org_details(l_payroll_act_id
647: ,c_rec.org_id);
648: FETCH c_org_details INTO l_registered_no
652: ,l_rep_desg
653: ,l_org_name
654: ,l_bsrtc_no;
655: CLOSE c_org_details;
656: pay_in_utils.set_location(g_debug,l_procedure, 60);
657: -- Registered No
658: l_tag :=pay_in_xml_utils.getTag('c_registered_no', l_registered_no);
659: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
660: --Organization Name and Address
677: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
678: --Date
679: l_tag :=pay_in_xml_utils.getTag('c_date', substr(l_sys_date_time, 1, 11));
680: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
681: pay_in_utils.set_location(g_debug,l_procedure, 70);
682:
683: IF g_debug THEN
684: pay_in_utils.trace('Registered No ',l_registered_no);
685: pay_in_utils.trace('Registered Name ',l_registered_name);
680: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
681: pay_in_utils.set_location(g_debug,l_procedure, 70);
682:
683: IF g_debug THEN
684: pay_in_utils.trace('Registered No ',l_registered_no);
685: pay_in_utils.trace('Registered Name ',l_registered_name);
686: pay_in_utils.trace('Registered Address ',l_reg_address);
687: pay_in_utils.trace('BSRTC no ',l_bsrtc_no);
688: pay_in_utils.trace('Period Start ',period_start);
681: pay_in_utils.set_location(g_debug,l_procedure, 70);
682:
683: IF g_debug THEN
684: pay_in_utils.trace('Registered No ',l_registered_no);
685: pay_in_utils.trace('Registered Name ',l_registered_name);
686: pay_in_utils.trace('Registered Address ',l_reg_address);
687: pay_in_utils.trace('BSRTC no ',l_bsrtc_no);
688: pay_in_utils.trace('Period Start ',period_start);
689: pay_in_utils.trace('Period End ',period_end);
682:
683: IF g_debug THEN
684: pay_in_utils.trace('Registered No ',l_registered_no);
685: pay_in_utils.trace('Registered Name ',l_registered_name);
686: pay_in_utils.trace('Registered Address ',l_reg_address);
687: pay_in_utils.trace('BSRTC no ',l_bsrtc_no);
688: pay_in_utils.trace('Period Start ',period_start);
689: pay_in_utils.trace('Period End ',period_end);
690: END IF;
683: IF g_debug THEN
684: pay_in_utils.trace('Registered No ',l_registered_no);
685: pay_in_utils.trace('Registered Name ',l_registered_name);
686: pay_in_utils.trace('Registered Address ',l_reg_address);
687: pay_in_utils.trace('BSRTC no ',l_bsrtc_no);
688: pay_in_utils.trace('Period Start ',period_start);
689: pay_in_utils.trace('Period End ',period_end);
690: END IF;
691:
684: pay_in_utils.trace('Registered No ',l_registered_no);
685: pay_in_utils.trace('Registered Name ',l_registered_name);
686: pay_in_utils.trace('Registered Address ',l_reg_address);
687: pay_in_utils.trace('BSRTC no ',l_bsrtc_no);
688: pay_in_utils.trace('Period Start ',period_start);
689: pay_in_utils.trace('Period End ',period_end);
690: END IF;
691:
692: l_count_0 := 0;
685: pay_in_utils.trace('Registered Name ',l_registered_name);
686: pay_in_utils.trace('Registered Address ',l_reg_address);
687: pay_in_utils.trace('BSRTC no ',l_bsrtc_no);
688: pay_in_utils.trace('Period Start ',period_start);
689: pay_in_utils.trace('Period End ',period_end);
690: END IF;
691:
692: l_count_0 := 0;
693: -- l_count_30 := 0;
707: l_interest_amount := 0;
708:
709: l_date := period_start;
710:
711: pay_in_utils.set_location(g_debug,l_procedure, 80);
712:
713: -- Getting the records for each month in the report period
714: WHILE l_date < period_end
715: LOOP
729: END IF;
730:
731:
732: IF g_debug THEN
733: pay_in_utils.trace('l_date ',l_date);
734: pay_in_utils.trace('l_period_month ',l_period_month);
735: END IF;
736: pay_in_utils.set_location(g_debug,l_procedure, 90);
737:
730:
731:
732: IF g_debug THEN
733: pay_in_utils.trace('l_date ',l_date);
734: pay_in_utils.trace('l_period_month ',l_period_month);
735: END IF;
736: pay_in_utils.set_location(g_debug,l_procedure, 90);
737:
738: -- Getting the no. of employees in each slab
732: IF g_debug THEN
733: pay_in_utils.trace('l_date ',l_date);
734: pay_in_utils.trace('l_period_month ',l_period_month);
735: END IF;
736: pay_in_utils.set_location(g_debug,l_procedure, 90);
737:
738: -- Getting the no. of employees in each slab
739: FOR rec_count in cur_get_records_number(c_rec.org_id
740: ,l_period_year
770: l_slab := 6;
771: END IF;
772:
773: IF g_debug THEN
774: pay_in_utils.trace('PT slab rate ',rec_count.rate);
775: pay_in_utils.trace('Count of Records ',rec_count.count);
776: END IF;
777:
778: pay_in_utils.set_location(g_debug,l_procedure, 100);
771: END IF;
772:
773: IF g_debug THEN
774: pay_in_utils.trace('PT slab rate ',rec_count.rate);
775: pay_in_utils.trace('Count of Records ',rec_count.count);
776: END IF;
777:
778: pay_in_utils.set_location(g_debug,l_procedure, 100);
779:
774: pay_in_utils.trace('PT slab rate ',rec_count.rate);
775: pay_in_utils.trace('Count of Records ',rec_count.count);
776: END IF;
777:
778: pay_in_utils.set_location(g_debug,l_procedure, 100);
779:
780: IF l_slab <> 6 THEN
781: -- Writing to the report if the slab is not the highest slab
782: -- Highest slab is written to the report once the loop is over since a sum is to be taken for 200 and 300
788:
789: END LOOP;
790:
791: IF g_debug THEN
792: pay_in_utils.trace('l_slab_month_tag ',l_slab_month_tag);
793: pay_in_utils.trace('l_count_0 ',l_count_0);
794: -- pay_in_utils.trace('l_count_30 ',l_count_30);
795: pay_in_utils.trace('l_count_60 ',l_count_60);
796: pay_in_utils.trace('l_count_120 ',l_count_120);
789: END LOOP;
790:
791: IF g_debug THEN
792: pay_in_utils.trace('l_slab_month_tag ',l_slab_month_tag);
793: pay_in_utils.trace('l_count_0 ',l_count_0);
794: -- pay_in_utils.trace('l_count_30 ',l_count_30);
795: pay_in_utils.trace('l_count_60 ',l_count_60);
796: pay_in_utils.trace('l_count_120 ',l_count_120);
797: pay_in_utils.trace('l_count_175 ',l_count_175);
790:
791: IF g_debug THEN
792: pay_in_utils.trace('l_slab_month_tag ',l_slab_month_tag);
793: pay_in_utils.trace('l_count_0 ',l_count_0);
794: -- pay_in_utils.trace('l_count_30 ',l_count_30);
795: pay_in_utils.trace('l_count_60 ',l_count_60);
796: pay_in_utils.trace('l_count_120 ',l_count_120);
797: pay_in_utils.trace('l_count_175 ',l_count_175);
798: pay_in_utils.trace('l_count_200+l_count_300 ',(l_count_200 + l_count_300));
791: IF g_debug THEN
792: pay_in_utils.trace('l_slab_month_tag ',l_slab_month_tag);
793: pay_in_utils.trace('l_count_0 ',l_count_0);
794: -- pay_in_utils.trace('l_count_30 ',l_count_30);
795: pay_in_utils.trace('l_count_60 ',l_count_60);
796: pay_in_utils.trace('l_count_120 ',l_count_120);
797: pay_in_utils.trace('l_count_175 ',l_count_175);
798: pay_in_utils.trace('l_count_200+l_count_300 ',(l_count_200 + l_count_300));
799:
792: pay_in_utils.trace('l_slab_month_tag ',l_slab_month_tag);
793: pay_in_utils.trace('l_count_0 ',l_count_0);
794: -- pay_in_utils.trace('l_count_30 ',l_count_30);
795: pay_in_utils.trace('l_count_60 ',l_count_60);
796: pay_in_utils.trace('l_count_120 ',l_count_120);
797: pay_in_utils.trace('l_count_175 ',l_count_175);
798: pay_in_utils.trace('l_count_200+l_count_300 ',(l_count_200 + l_count_300));
799:
800: END IF;
793: pay_in_utils.trace('l_count_0 ',l_count_0);
794: -- pay_in_utils.trace('l_count_30 ',l_count_30);
795: pay_in_utils.trace('l_count_60 ',l_count_60);
796: pay_in_utils.trace('l_count_120 ',l_count_120);
797: pay_in_utils.trace('l_count_175 ',l_count_175);
798: pay_in_utils.trace('l_count_200+l_count_300 ',(l_count_200 + l_count_300));
799:
800: END IF;
801:
794: -- pay_in_utils.trace('l_count_30 ',l_count_30);
795: pay_in_utils.trace('l_count_60 ',l_count_60);
796: pay_in_utils.trace('l_count_120 ',l_count_120);
797: pay_in_utils.trace('l_count_175 ',l_count_175);
798: pay_in_utils.trace('l_count_200+l_count_300 ',(l_count_200 + l_count_300));
799:
800: END IF;
801:
802:
799:
800: END IF;
801:
802:
803: pay_in_utils.set_location(g_debug,l_procedure, 110);
804:
805: l_slab_month_count := l_count_300_temp + l_count_200_temp;
806:
807: IF l_slab_month_count <> 0 THEN
813:
814: l_date := ADD_MONTHS(l_date, 1);
815:
816: END LOOP;
817: pay_in_utils.set_location(g_debug,l_procedure, 120);
818:
819: -- Writing the total employees in a slab
820: l_tag := pay_in_xml_utils.getTag('c_emp_slab_1', l_count_0);
821: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
848: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
849: l_tag := pay_in_xml_utils.getTag('c_tax_slab_6', pay_us_employee_payslip_web.get_format_value(l_bg_id,nvl(l_tax_slab_6,0)));
850: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
851:
852: pay_in_utils.set_location(g_debug,l_procedure, 130);
853:
854: -- Getting the actual PT paid from the challans data
855: OPEN c_challan(c_rec.org_id
856: ,period_start
860: ,l_excess_tax;
861: CLOSE c_challan;
862:
863: IF g_debug THEN
864: pay_in_utils.trace('Tax Amount ',l_tax_amount);
865: pay_in_utils.trace('Interest Amount ',l_interest_amount);
866: pay_in_utils.trace('Excess Tax ',l_excess_tax);
867: END IF;
868:
861: CLOSE c_challan;
862:
863: IF g_debug THEN
864: pay_in_utils.trace('Tax Amount ',l_tax_amount);
865: pay_in_utils.trace('Interest Amount ',l_interest_amount);
866: pay_in_utils.trace('Excess Tax ',l_excess_tax);
867: END IF;
868:
869:
862:
863: IF g_debug THEN
864: pay_in_utils.trace('Tax Amount ',l_tax_amount);
865: pay_in_utils.trace('Interest Amount ',l_interest_amount);
866: pay_in_utils.trace('Excess Tax ',l_excess_tax);
867: END IF;
868:
869:
870: l_tag := pay_in_xml_utils.getTag('c_tax_amount', pay_us_employee_payslip_web.get_format_value(l_bg_id,nvl(l_tax_amount,0)));
880:
881: l_tag := pay_in_xml_utils.getTag('c_net_amount', pay_us_employee_payslip_web.get_format_value(l_bg_id,nvl(l_net_amount,0)));
882: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
883:
884: l_tag := pay_in_xml_utils.getTag('c_total_amount_words', initcap(pay_in_utils.number_to_words(l_net_amount)));
885: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
886:
887: l_tag :=pay_in_xml_utils.getTag('c_year', period_start_year);
888: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
890: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
891:
892: l_tag :='';
893: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
894: pay_in_utils.set_location(g_debug,l_procedure, 140);
895: fnd_file.put_line(fnd_file.log,'Org Over');
896: END LOOP;
897: l_tag :='';
898: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
896: END LOOP;
897: l_tag :='';
898: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
899: fnd_file.put_line(fnd_file.log,'XML Created.');
900: pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 150);
901:
902: END create_pt_form3_xml;
903:
904:
926: l_tag VARCHAR2(10000);
927: l_procedure VARCHAR2(100);
928: BEGIN
929: l_procedure := g_package ||'write_tag';
930: pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
931:
932: l_tag := pay_in_xml_utils.getTag( p_tag_name => p_tag_name
933: , p_tag_value => p_tag_value
934: );
933: , p_tag_value => p_tag_value
934: );
935:
936: dbms_lob.writeAppend(g_xml_data,length(l_tag),l_tag);
937: pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 20);
938: END write_tag;
939:
940: --------------------------------------------------------------------------
941: -- --
984: BEGIN
985:
986: g_debug := hr_utility.debug_enabled;
987: l_procedure := g_package ||'get_location_details';
988: pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
989:
990: OPEN csr_add;
991: FETCH csr_add INTO l_add_1, l_add_2, l_add_3, l_add_4, l_add_5, l_state, l_pin,l_tel,l_fax;
992: CLOSE csr_add;
1011: l_details := l_fax;
1012: END IF;
1013:
1014: IF g_debug THEN
1015: pay_in_utils.trace('Location id ',p_location_id);
1016: pay_in_utils.trace('Field ',p_field);
1017: pay_in_utils.trace('Address Details ',l_details);
1018: END IF;
1019:
1012: END IF;
1013:
1014: IF g_debug THEN
1015: pay_in_utils.trace('Location id ',p_location_id);
1016: pay_in_utils.trace('Field ',p_field);
1017: pay_in_utils.trace('Address Details ',l_details);
1018: END IF;
1019:
1020: pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 20);
1013:
1014: IF g_debug THEN
1015: pay_in_utils.trace('Location id ',p_location_id);
1016: pay_in_utils.trace('Field ',p_field);
1017: pay_in_utils.trace('Address Details ',l_details);
1018: END IF;
1019:
1020: pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 20);
1021:
1016: pay_in_utils.trace('Field ',p_field);
1017: pay_in_utils.trace('Address Details ',l_details);
1018: END IF;
1019:
1020: pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 20);
1021:
1022: RETURN l_details;
1023:
1024: END get_location_details;
1187: BEGIN
1188:
1189: g_debug := hr_utility.debug_enabled;
1190: l_procedure := g_package ||'create_form27A_xml';
1191: pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1192:
1193: IF g_debug THEN
1194: pay_in_utils.trace('GRE id ',p_gre_org_id);
1195: pay_in_utils.trace('Assessment year ',p_assess_year);
1190: l_procedure := g_package ||'create_form27A_xml';
1191: pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1192:
1193: IF g_debug THEN
1194: pay_in_utils.trace('GRE id ',p_gre_org_id);
1195: pay_in_utils.trace('Assessment year ',p_assess_year);
1196: END IF;
1197:
1198:
1191: pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1192:
1193: IF g_debug THEN
1194: pay_in_utils.trace('GRE id ',p_gre_org_id);
1195: pay_in_utils.trace('Assessment year ',p_assess_year);
1196: END IF;
1197:
1198:
1199: pay_in_xml_utils.gXMLTable.DELETE;
1208: l_tag := '
1209: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
1210: fnd_file.put_line(fnd_file.log,'Started...');
1211:
1212: pay_in_utils.set_location(g_debug,l_procedure, 20);
1213: --
1214: fnd_file.put_line(fnd_file.log,'Creating XML for Employer Details.');
1215: l_sys_date_time:=TO_CHAR(SYSDATE,'DD-Mon-YYYY HH24:MI:SS');
1216: l_sys_date := TO_CHAR(SYSDATE,'DD-Mon-YYYY');
1218: --System Date
1219: l_tag :=pay_in_xml_utils.getTag('c_current_date_in_hh_mm_ss',l_sys_date_time);
1220: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
1221:
1222: pay_in_utils.set_location(g_debug,l_procedure, 30);
1223:
1224: OPEN csr_org_max_action_context_id;
1225: FETCH csr_org_max_action_context_id INTO l_org_max_action_id;
1226: CLOSE csr_org_max_action_context_id;
1236: OPEN csr_tot_emp_cnt;
1237: FETCH csr_tot_emp_cnt INTO l_tot_emp;
1238: CLOSE csr_tot_emp_cnt;
1239:
1240: pay_in_utils.set_location(g_debug,l_procedure, 40);
1241:
1242: FOR i IN csr_get_max_cont_id
1243: LOOP
1244: pay_in_utils.set_location(g_debug,l_procedure, 50);
1240: pay_in_utils.set_location(g_debug,l_procedure, 40);
1241:
1242: FOR i IN csr_get_max_cont_id
1243: LOOP
1244: pay_in_utils.set_location(g_debug,l_procedure, 50);
1245: OPEN csr_tax_details('Income Tax Deduction',i.action_cont_id,i.sour_id);
1246: FETCH csr_tax_details INTO l_itd;
1247: CLOSE csr_tax_details;
1248:
1297: l_tag := '';
1298: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
1299:
1300: fnd_file.put_line(fnd_file.log,'XML Created.');
1301: pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 60);
1302:
1303:
1304: END create_form27A_xml;
1305:
1356: BEGIN
1357: --
1358: g_debug := hr_utility.debug_enabled;
1359: l_procedure := g_package ||'init_code';
1360: pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1361:
1362: IF p_form_type <> 'ESI6' and p_form_type <> 'PT3' AND p_form_type <> 'FORM27A' THEN
1363: l_effective_start_date:=to_date(('01-03-'||substr(p_contribution_period,1,4)),'DD-MM-YYYY');
1364: l_effective_end_date:= to_date(('01-03-'||substr(p_contribution_period,6)),'DD-MM-YYYY')-1;
1366: l_contribution_period := hr_general.decode_lookup('IN_ESI_CONTRIB_PERIOD',p_contribution_period);
1367: END IF;
1368:
1369: IF g_debug THEN
1370: pay_in_utils.trace('PF Organization id ',p_pf_org_id);
1371: pay_in_utils.trace('PF Number ',p_pf_number);
1372: pay_in_utils.trace('Employee Type ',p_employee_type);
1373: pay_in_utils.trace('Contribution Period ',p_contribution_period);
1374: pay_in_utils.trace('Effective Start Date',l_effective_start_date);
1367: END IF;
1368:
1369: IF g_debug THEN
1370: pay_in_utils.trace('PF Organization id ',p_pf_org_id);
1371: pay_in_utils.trace('PF Number ',p_pf_number);
1372: pay_in_utils.trace('Employee Type ',p_employee_type);
1373: pay_in_utils.trace('Contribution Period ',p_contribution_period);
1374: pay_in_utils.trace('Effective Start Date',l_effective_start_date);
1375: pay_in_utils.trace('Effective End Date ',l_effective_end_date);
1368:
1369: IF g_debug THEN
1370: pay_in_utils.trace('PF Organization id ',p_pf_org_id);
1371: pay_in_utils.trace('PF Number ',p_pf_number);
1372: pay_in_utils.trace('Employee Type ',p_employee_type);
1373: pay_in_utils.trace('Contribution Period ',p_contribution_period);
1374: pay_in_utils.trace('Effective Start Date',l_effective_start_date);
1375: pay_in_utils.trace('Effective End Date ',l_effective_end_date);
1376: pay_in_utils.trace('Pension Number ',p_pension_number);
1369: IF g_debug THEN
1370: pay_in_utils.trace('PF Organization id ',p_pf_org_id);
1371: pay_in_utils.trace('PF Number ',p_pf_number);
1372: pay_in_utils.trace('Employee Type ',p_employee_type);
1373: pay_in_utils.trace('Contribution Period ',p_contribution_period);
1374: pay_in_utils.trace('Effective Start Date',l_effective_start_date);
1375: pay_in_utils.trace('Effective End Date ',l_effective_end_date);
1376: pay_in_utils.trace('Pension Number ',p_pension_number);
1377: pay_in_utils.trace('ESI Org ID ',p_esi_org_id);
1370: pay_in_utils.trace('PF Organization id ',p_pf_org_id);
1371: pay_in_utils.trace('PF Number ',p_pf_number);
1372: pay_in_utils.trace('Employee Type ',p_employee_type);
1373: pay_in_utils.trace('Contribution Period ',p_contribution_period);
1374: pay_in_utils.trace('Effective Start Date',l_effective_start_date);
1375: pay_in_utils.trace('Effective End Date ',l_effective_end_date);
1376: pay_in_utils.trace('Pension Number ',p_pension_number);
1377: pay_in_utils.trace('ESI Org ID ',p_esi_org_id);
1378: pay_in_utils.trace('ESI Coverage ',p_esi_coverage);
1371: pay_in_utils.trace('PF Number ',p_pf_number);
1372: pay_in_utils.trace('Employee Type ',p_employee_type);
1373: pay_in_utils.trace('Contribution Period ',p_contribution_period);
1374: pay_in_utils.trace('Effective Start Date',l_effective_start_date);
1375: pay_in_utils.trace('Effective End Date ',l_effective_end_date);
1376: pay_in_utils.trace('Pension Number ',p_pension_number);
1377: pay_in_utils.trace('ESI Org ID ',p_esi_org_id);
1378: pay_in_utils.trace('ESI Coverage ',p_esi_coverage);
1379: pay_in_utils.trace('Contribution Period ',l_contribution_period);
1372: pay_in_utils.trace('Employee Type ',p_employee_type);
1373: pay_in_utils.trace('Contribution Period ',p_contribution_period);
1374: pay_in_utils.trace('Effective Start Date',l_effective_start_date);
1375: pay_in_utils.trace('Effective End Date ',l_effective_end_date);
1376: pay_in_utils.trace('Pension Number ',p_pension_number);
1377: pay_in_utils.trace('ESI Org ID ',p_esi_org_id);
1378: pay_in_utils.trace('ESI Coverage ',p_esi_coverage);
1379: pay_in_utils.trace('Contribution Period ',l_contribution_period);
1380: pay_in_utils.trace('Session Date ',p_sysdate);
1373: pay_in_utils.trace('Contribution Period ',p_contribution_period);
1374: pay_in_utils.trace('Effective Start Date',l_effective_start_date);
1375: pay_in_utils.trace('Effective End Date ',l_effective_end_date);
1376: pay_in_utils.trace('Pension Number ',p_pension_number);
1377: pay_in_utils.trace('ESI Org ID ',p_esi_org_id);
1378: pay_in_utils.trace('ESI Coverage ',p_esi_coverage);
1379: pay_in_utils.trace('Contribution Period ',l_contribution_period);
1380: pay_in_utils.trace('Session Date ',p_sysdate);
1381: pay_in_utils.trace('PT Org ID ',p_pt_org_id);
1374: pay_in_utils.trace('Effective Start Date',l_effective_start_date);
1375: pay_in_utils.trace('Effective End Date ',l_effective_end_date);
1376: pay_in_utils.trace('Pension Number ',p_pension_number);
1377: pay_in_utils.trace('ESI Org ID ',p_esi_org_id);
1378: pay_in_utils.trace('ESI Coverage ',p_esi_coverage);
1379: pay_in_utils.trace('Contribution Period ',l_contribution_period);
1380: pay_in_utils.trace('Session Date ',p_sysdate);
1381: pay_in_utils.trace('PT Org ID ',p_pt_org_id);
1382: pay_in_utils.trace('PT Frequency ',p_frequency);
1375: pay_in_utils.trace('Effective End Date ',l_effective_end_date);
1376: pay_in_utils.trace('Pension Number ',p_pension_number);
1377: pay_in_utils.trace('ESI Org ID ',p_esi_org_id);
1378: pay_in_utils.trace('ESI Coverage ',p_esi_coverage);
1379: pay_in_utils.trace('Contribution Period ',l_contribution_period);
1380: pay_in_utils.trace('Session Date ',p_sysdate);
1381: pay_in_utils.trace('PT Org ID ',p_pt_org_id);
1382: pay_in_utils.trace('PT Frequency ',p_frequency);
1383: pay_in_utils.trace('PT Year ',p_year);
1376: pay_in_utils.trace('Pension Number ',p_pension_number);
1377: pay_in_utils.trace('ESI Org ID ',p_esi_org_id);
1378: pay_in_utils.trace('ESI Coverage ',p_esi_coverage);
1379: pay_in_utils.trace('Contribution Period ',l_contribution_period);
1380: pay_in_utils.trace('Session Date ',p_sysdate);
1381: pay_in_utils.trace('PT Org ID ',p_pt_org_id);
1382: pay_in_utils.trace('PT Frequency ',p_frequency);
1383: pay_in_utils.trace('PT Year ',p_year);
1384: pay_in_utils.trace('Period ',p_period);
1377: pay_in_utils.trace('ESI Org ID ',p_esi_org_id);
1378: pay_in_utils.trace('ESI Coverage ',p_esi_coverage);
1379: pay_in_utils.trace('Contribution Period ',l_contribution_period);
1380: pay_in_utils.trace('Session Date ',p_sysdate);
1381: pay_in_utils.trace('PT Org ID ',p_pt_org_id);
1382: pay_in_utils.trace('PT Frequency ',p_frequency);
1383: pay_in_utils.trace('PT Year ',p_year);
1384: pay_in_utils.trace('Period ',p_period);
1385: END IF;
1378: pay_in_utils.trace('ESI Coverage ',p_esi_coverage);
1379: pay_in_utils.trace('Contribution Period ',l_contribution_period);
1380: pay_in_utils.trace('Session Date ',p_sysdate);
1381: pay_in_utils.trace('PT Org ID ',p_pt_org_id);
1382: pay_in_utils.trace('PT Frequency ',p_frequency);
1383: pay_in_utils.trace('PT Year ',p_year);
1384: pay_in_utils.trace('Period ',p_period);
1385: END IF;
1386:
1379: pay_in_utils.trace('Contribution Period ',l_contribution_period);
1380: pay_in_utils.trace('Session Date ',p_sysdate);
1381: pay_in_utils.trace('PT Org ID ',p_pt_org_id);
1382: pay_in_utils.trace('PT Frequency ',p_frequency);
1383: pay_in_utils.trace('PT Year ',p_year);
1384: pay_in_utils.trace('Period ',p_period);
1385: END IF;
1386:
1387:
1380: pay_in_utils.trace('Session Date ',p_sysdate);
1381: pay_in_utils.trace('PT Org ID ',p_pt_org_id);
1382: pay_in_utils.trace('PT Frequency ',p_frequency);
1383: pay_in_utils.trace('PT Year ',p_year);
1384: pay_in_utils.trace('Period ',p_period);
1385: END IF;
1386:
1387:
1388:
1388:
1389:
1390: IF p_form_type = 'FORM3A' THEN
1391: --
1392: pay_in_utils.set_location(g_debug,l_procedure, 20);
1393: create_form3a_xml(p_pf_org_id => p_pf_org_id
1394: ,p_pf_number => p_pf_number
1395: ,p_employee_type=> p_employee_type
1396: ,p_contribution_period=> p_contribution_period
1398: ,p_effective_end_date => l_effective_end_date
1399: );
1400: ELSIF p_form_type = 'FORM6A' THEN
1401: --
1402: pay_in_utils.set_location(g_debug,l_procedure, 30);
1403: create_form6a_xml(p_pf_org_id => p_pf_org_id
1404: ,p_effective_start_date => l_effective_start_date
1405: ,p_effective_end_date => l_effective_end_date
1406: ,p_contribution_period=>p_contribution_period
1406: ,p_contribution_period=>p_contribution_period
1407: );
1408: ELSIF p_form_type = 'FORM8' THEN
1409: --
1410: pay_in_utils.set_location(g_debug,l_procedure, 40);
1411: create_form8_xml(p_pf_org_id => p_pf_org_id
1412: ,p_contribution_period => p_contribution_period
1413: ,p_effective_start_date => l_effective_start_date
1414: ,p_effective_end_date => l_effective_end_date
1414: ,p_effective_end_date => l_effective_end_date
1415: );
1416: ELSIF p_form_type ='FORM7' THEN
1417: --
1418: pay_in_utils.set_location(g_debug,l_procedure, 50);
1419: create_form7_xml(p_pf_org_id => p_pf_org_id
1420: ,p_pension_number => p_pension_number
1421: ,p_employee_type => p_employee_type
1422: ,p_contribution_period => p_contribution_period
1424: ,p_effective_end_date => l_effective_end_date
1425: );
1426: ELSIF p_form_type ='ESI6' THEN
1427: --
1428: pay_in_utils.set_location(g_debug,l_procedure, 60);
1429: create_esi_xml(p_esi_org_id => p_esi_org_id
1430: ,p_contribution_period => l_contribution_period
1431: ,p_esi_coverage => p_esi_coverage
1432: ,p_sysdate => p_sysdate);
1432: ,p_sysdate => p_sysdate);
1433:
1434: ELSIF p_form_type ='PT3' THEN
1435: --
1436: pay_in_utils.set_location(g_debug,l_procedure, 70);
1437: create_pt_form3_xml(p_pt_org_id => p_pt_org_id
1438: ,p_frequency => p_frequency
1439: ,p_year => p_year
1440: ,p_period => p_period);
1439: ,p_year => p_year
1440: ,p_period => p_period);
1441:
1442: ELSIF p_form_type='FORM27A' THEN
1443: pay_in_utils.set_location(g_debug,l_procedure, 80);
1444: create_form27A_xml(P_GRE_ORG_ID => P_GRE_ORG_ID
1445: ,P_ASSESS_YEAR => P_ASSESS_YEAR);
1446:
1447: ELSE
1449: END IF;
1450:
1451:
1452: p_xml := g_xml_data;
1453: pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 90);
1454:
1455:
1456: END init_code;
1457:
1714:
1715: BEGIN
1716: g_debug := hr_utility.debug_enabled;
1717: l_procedure := g_package ||'create_form8_xml';
1718: pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1719:
1720:
1721: pay_in_xml_utils.gXMLTable.DELETE;
1722: --
1737: --System Date
1738: l_tag :=pay_in_xml_utils.getTag('c_current_date_in_hh_mm_ss',l_sys_date_time);
1739: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
1740:
1741: pay_in_utils.set_location(g_debug,l_procedure, 20);
1742:
1743: FOR c_rec IN c_pf_org_id(p_pf_org_id
1744: ,p_contribution_period
1745: ,p_effective_start_date
1744: ,p_contribution_period
1745: ,p_effective_start_date
1746: ,p_effective_end_date)
1747: LOOP
1748: pay_in_utils.set_location(g_debug,l_procedure, 30);
1749: l_tag := '
1750: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
1751:
1752: IF g_debug THEN
1749: l_tag := '
1750: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
1751:
1752: IF g_debug THEN
1753: pay_in_utils.trace('PF Organization id ',p_pf_org_id);
1754: pay_in_utils.trace('Contribution Period ',p_contribution_period);
1755: END IF;
1756:
1757: l_count:=1;
1750: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
1751:
1752: IF g_debug THEN
1753: pay_in_utils.trace('PF Organization id ',p_pf_org_id);
1754: pay_in_utils.trace('Contribution Period ',p_contribution_period);
1755: END IF;
1756:
1757: l_count:=1;
1758: --PF Org Reg Name
1781:
1782: l_employee_no:=1;
1783: l_org_pf_ytd := 0;
1784: l_org_pension_ytd :=0;
1785: pay_in_utils.set_location(g_debug,l_procedure, 40);
1786:
1787: FOR assignment_rec IN c_assignment_id(c_rec.action_information2
1788: ,p_contribution_period
1789: ,p_effective_start_date
1788: ,p_contribution_period
1789: ,p_effective_start_date
1790: ,p_effective_end_date)
1791: LOOP
1792: pay_in_utils.set_location(g_debug,l_procedure, 50);
1793:
1794: IF g_debug THEN
1795: pay_in_utils.trace('Pension Number ',assignment_rec.action_information15);
1796: pay_in_utils.trace('PF Organization ID ',c_rec.action_information2);
1791: LOOP
1792: pay_in_utils.set_location(g_debug,l_procedure, 50);
1793:
1794: IF g_debug THEN
1795: pay_in_utils.trace('Pension Number ',assignment_rec.action_information15);
1796: pay_in_utils.trace('PF Organization ID ',c_rec.action_information2);
1797: END IF;
1798:
1799: FOR chk_excluded_employee IN csr_not_excluded_employee(c_rec.action_information2
1792: pay_in_utils.set_location(g_debug,l_procedure, 50);
1793:
1794: IF g_debug THEN
1795: pay_in_utils.trace('Pension Number ',assignment_rec.action_information15);
1796: pay_in_utils.trace('PF Organization ID ',c_rec.action_information2);
1797: END IF;
1798:
1799: FOR chk_excluded_employee IN csr_not_excluded_employee(c_rec.action_information2
1800: ,p_contribution_period
1799: FOR chk_excluded_employee IN csr_not_excluded_employee(c_rec.action_information2
1800: ,p_contribution_period
1801: ,assignment_rec.action_information15)
1802: LOOP
1803: pay_in_utils.set_location(g_debug,l_procedure, 60);
1804: -- Display only when employee status is not excluded
1805: IF chk_excluded_employee.status ='1' THEN
1806: OPEN c_emp_name(c_rec.action_information2
1807: ,assignment_rec.action_information15
1817: FOR c_mon_contr IN c_monthly_contributions(c_rec.action_information2
1818: ,assignment_rec.action_information15)
1819: LOOP
1820:
1821: l_cont_type_date_earned :=pay_in_utils.get_scl_segment_on_date(assignment_rec.assignment_id
1822: ,l_bg_id
1823: ,c_mon_contr.date_earned
1824: ,'segment12');
1825: IF(l_cont_type_date_earned = '0')THEN
1843: ,p_contribution_period
1844: ,assignment_rec.action_information15
1845: ,l_salary_ceiling)
1846: LOOP
1847: pay_in_utils.set_location(g_debug,l_procedure, 70);
1848: l_count:=1;
1849: --Sl No.
1850: pay_in_xml_utils.gXMLTable(l_count).Name := 'c_sl_no';
1851: pay_in_xml_utils.gXMLTable(l_count).Value := (l_employee_no);
1860: pay_in_xml_utils.gXMLTable(l_count).Value := l_employee_name ;
1861:
1862: l_count := l_count + 1;
1863: IF g_debug THEN
1864: pay_in_utils.trace('Employee Name ',pay_in_xml_utils.gXMLTable(l_count).Value);
1865: END IF;
1866:
1867:
1868: pay_in_utils.set_location(g_debug,l_procedure, 80);
1864: pay_in_utils.trace('Employee Name ',pay_in_xml_utils.gXMLTable(l_count).Value);
1865: END IF;
1866:
1867:
1868: pay_in_utils.set_location(g_debug,l_procedure, 80);
1869:
1870: l_org_pf_ytd := l_org_pf_ytd + asg_sum ;
1871: l_org_pension_ytd := l_org_pension_ytd + child_asg_rec.pension;
1872:
1879: pay_in_xml_utils.gXMLTable(l_count).Value := pay_us_employee_payslip_web.get_format_value(l_bg_id,nvl(child_asg_rec.pension,0));
1880: l_count := l_count + 1;
1881:
1882: IF g_debug THEN
1883: pay_in_utils.trace('PF Wages ',asg_sum);
1884: pay_in_utils.trace('Pension Amt ',child_asg_rec.pension);
1885: END IF;
1886:
1887: l_remarks := NULL;
1880: l_count := l_count + 1;
1881:
1882: IF g_debug THEN
1883: pay_in_utils.trace('PF Wages ',asg_sum);
1884: pay_in_utils.trace('Pension Amt ',child_asg_rec.pension);
1885: END IF;
1886:
1887: l_remarks := NULL;
1888: l_remarks_dummy := NULL;
1891: ,assignment_rec.action_information15
1892: ,p_effective_start_date
1893: ,p_effective_end_date)
1894: LOOP
1895: pay_in_utils.set_location(g_debug,l_procedure, 90);
1896: l_payroll_mon := TO_NUMBER(TO_CHAR(c_rec_child.mon,'MM'));
1897: IF (l_payroll_mon <4 ) THEN
1898: l_mon := l_payroll_mon + 9;
1899: ELSE
1924: END IF;
1925: END LOOP;
1926: END LOOP;
1927:
1928: pay_in_utils.set_location(g_debug,l_procedure, 100);
1929: pay_in_xml_utils.gXMLTable.delete;
1930:
1931: l_count:=1;
1932: pay_in_xml_utils.gXMLTable(l_count).Name := 'c_wage_ytd';
1937: l_count := l_count + 1;
1938: pay_in_xml_utils.multiColumnar('sum',pay_in_xml_utils.gXMLTable,l_count,g_xml_data);
1939:
1940: IF g_debug THEN
1941: pay_in_utils.trace('Total PF Wages ',l_org_pf_ytd);
1942: pay_in_utils.trace('Total Pension Amt ',l_org_pension_ytd);
1943: END IF;
1944:
1945:
1938: pay_in_xml_utils.multiColumnar('sum',pay_in_xml_utils.gXMLTable,l_count,g_xml_data);
1939:
1940: IF g_debug THEN
1941: pay_in_utils.trace('Total PF Wages ',l_org_pf_ytd);
1942: pay_in_utils.trace('Total Pension Amt ',l_org_pension_ytd);
1943: END IF;
1944:
1945:
1946: pay_in_xml_utils.gXMLTable.delete;
1952: l_admn_total:=0;
1953: l_edli_adm_total:=0;
1954: l_summation:=0;
1955:
1956: pay_in_utils.set_location(g_debug,l_procedure, 110);
1957:
1958: FOR c_pf_org_child_rec IN c_pf_employer(c_rec.action_information2 --PF Org ID
1959: ,p_effective_start_date
1960: ,p_effective_end_date)
1959: ,p_effective_start_date
1960: ,p_effective_end_date)
1961: LOOP
1962:
1963: pay_in_utils.set_location(g_debug,l_procedure, 120);
1964:
1965: IF g_debug THEN
1966: pay_in_utils.trace('Month Number ', c_pf_org_child_rec.mon);
1967: pay_in_utils.trace('Pension ', c_pf_org_child_rec.pension);
1962:
1963: pay_in_utils.set_location(g_debug,l_procedure, 120);
1964:
1965: IF g_debug THEN
1966: pay_in_utils.trace('Month Number ', c_pf_org_child_rec.mon);
1967: pay_in_utils.trace('Pension ', c_pf_org_child_rec.pension);
1968: END IF;
1969:
1970: IF (c_pf_employer%ROWCOUNT=1) THEN
1963: pay_in_utils.set_location(g_debug,l_procedure, 120);
1964:
1965: IF g_debug THEN
1966: pay_in_utils.trace('Month Number ', c_pf_org_child_rec.mon);
1967: pay_in_utils.trace('Pension ', c_pf_org_child_rec.pension);
1968: END IF;
1969:
1970: IF (c_pf_employer%ROWCOUNT=1) THEN
1971: IF c_pf_org_child_rec.mon>1 THEN
2005: pay_in_xml_utils.gXMLTable(l_count).Name := 'c_emp_no';
2006: pay_in_xml_utils.gXMLTable(l_count).Value := l_employee_no - 1;
2007: l_count := l_count + 1;
2008:
2009: pay_in_utils.set_location(g_debug,l_procedure, 130);
2010:
2011: --Employer Representative Signature
2012: pay_in_xml_utils.gXMLTable(l_count).Name := 'c_employer';
2013: OPEN c_rep_name(p_contribution_period
2022: l_tag := '';
2023: dbms_lob.writeAppend(g_xml_data, LENGTH(l_tag), l_tag);
2024: pay_in_xml_utils.gXMLTable.DELETE;
2025: l_row_count:=NULL;
2026: pay_in_utils.set_location(g_debug,l_procedure, 140);
2027: END LOOP;
2028:
2029: pay_in_utils.set_location(g_debug,l_procedure, 150);
2030:
2025: l_row_count:=NULL;
2026: pay_in_utils.set_location(g_debug,l_procedure, 140);
2027: END LOOP;
2028:
2029: pay_in_utils.set_location(g_debug,l_procedure, 150);
2030:
2031: l_tag := '';
2032: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
2033:
2033:
2034: fnd_file.put_line(fnd_file.log,'XML Created.');
2035: -- INSERT INTO temp_clob VALUES (g_xml_data);
2036:
2037: pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 160);
2038:
2039:
2040: END create_form8_xml;
2041:
2264: BEGIN
2265:
2266: g_debug := hr_utility.debug_enabled;
2267: l_procedure := g_package ||'employee_type';
2268: pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
2269:
2270: IF g_debug THEN
2271: pay_in_utils.trace('**************************************************','********************');
2272: pay_in_utils.trace('PF Number ',p_pf_number);
2267: l_procedure := g_package ||'employee_type';
2268: pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
2269:
2270: IF g_debug THEN
2271: pay_in_utils.trace('**************************************************','********************');
2272: pay_in_utils.trace('PF Number ',p_pf_number);
2273: pay_in_utils.trace('Employee Type ',p_employee_type);
2274: pay_in_utils.trace('Effective Start Date ',p_effective_start_date);
2275: pay_in_utils.trace('Effective End Date ',p_effective_end_date);
2268: pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
2269:
2270: IF g_debug THEN
2271: pay_in_utils.trace('**************************************************','********************');
2272: pay_in_utils.trace('PF Number ',p_pf_number);
2273: pay_in_utils.trace('Employee Type ',p_employee_type);
2274: pay_in_utils.trace('Effective Start Date ',p_effective_start_date);
2275: pay_in_utils.trace('Effective End Date ',p_effective_end_date);
2276: pay_in_utils.trace('CP PF Org id ',p_cp_pf_org_id);
2269:
2270: IF g_debug THEN
2271: pay_in_utils.trace('**************************************************','********************');
2272: pay_in_utils.trace('PF Number ',p_pf_number);
2273: pay_in_utils.trace('Employee Type ',p_employee_type);
2274: pay_in_utils.trace('Effective Start Date ',p_effective_start_date);
2275: pay_in_utils.trace('Effective End Date ',p_effective_end_date);
2276: pay_in_utils.trace('CP PF Org id ',p_cp_pf_org_id);
2277: pay_in_utils.trace('PF Org id ',p_pf_org_id);
2270: IF g_debug THEN
2271: pay_in_utils.trace('**************************************************','********************');
2272: pay_in_utils.trace('PF Number ',p_pf_number);
2273: pay_in_utils.trace('Employee Type ',p_employee_type);
2274: pay_in_utils.trace('Effective Start Date ',p_effective_start_date);
2275: pay_in_utils.trace('Effective End Date ',p_effective_end_date);
2276: pay_in_utils.trace('CP PF Org id ',p_cp_pf_org_id);
2277: pay_in_utils.trace('PF Org id ',p_pf_org_id);
2278: pay_in_utils.trace('**************************************************','********************');
2271: pay_in_utils.trace('**************************************************','********************');
2272: pay_in_utils.trace('PF Number ',p_pf_number);
2273: pay_in_utils.trace('Employee Type ',p_employee_type);
2274: pay_in_utils.trace('Effective Start Date ',p_effective_start_date);
2275: pay_in_utils.trace('Effective End Date ',p_effective_end_date);
2276: pay_in_utils.trace('CP PF Org id ',p_cp_pf_org_id);
2277: pay_in_utils.trace('PF Org id ',p_pf_org_id);
2278: pay_in_utils.trace('**************************************************','********************');
2279: END IF;
2272: pay_in_utils.trace('PF Number ',p_pf_number);
2273: pay_in_utils.trace('Employee Type ',p_employee_type);
2274: pay_in_utils.trace('Effective Start Date ',p_effective_start_date);
2275: pay_in_utils.trace('Effective End Date ',p_effective_end_date);
2276: pay_in_utils.trace('CP PF Org id ',p_cp_pf_org_id);
2277: pay_in_utils.trace('PF Org id ',p_pf_org_id);
2278: pay_in_utils.trace('**************************************************','********************');
2279: END IF;
2280:
2273: pay_in_utils.trace('Employee Type ',p_employee_type);
2274: pay_in_utils.trace('Effective Start Date ',p_effective_start_date);
2275: pay_in_utils.trace('Effective End Date ',p_effective_end_date);
2276: pay_in_utils.trace('CP PF Org id ',p_cp_pf_org_id);
2277: pay_in_utils.trace('PF Org id ',p_pf_org_id);
2278: pay_in_utils.trace('**************************************************','********************');
2279: END IF;
2280:
2281: l_org_count:=0;
2274: pay_in_utils.trace('Effective Start Date ',p_effective_start_date);
2275: pay_in_utils.trace('Effective End Date ',p_effective_end_date);
2276: pay_in_utils.trace('CP PF Org id ',p_cp_pf_org_id);
2277: pay_in_utils.trace('PF Org id ',p_pf_org_id);
2278: pay_in_utils.trace('**************************************************','********************');
2279: END IF;
2280:
2281: l_org_count:=0;
2282:
2284: FETCH c_person_id INTO l_person_id;
2285: CLOSE c_person_id;
2286:
2287: IF p_employee_type = 'TRANSFERRED' THEN
2288: pay_in_utils.set_location(g_debug,l_procedure, 20);
2289: /*This is for finding the Organization Change Count*/
2290: FOR c_rec IN c_transfer_check
2291: LOOP
2292: l_org_count:=l_org_count+1;
2290: FOR c_rec IN c_transfer_check
2291: LOOP
2292: l_org_count:=l_org_count+1;
2293: END LOOP;
2294: pay_in_utils.set_location(g_debug,l_procedure, 20);
2295:
2296:
2297: IF l_org_count <2 THEN /*This means there were'nt any changes*/
2298: pay_in_utils.set_location(g_debug,l_procedure, 30);
2294: pay_in_utils.set_location(g_debug,l_procedure, 20);
2295:
2296:
2297: IF l_org_count <2 THEN /*This means there were'nt any changes*/
2298: pay_in_utils.set_location(g_debug,l_procedure, 30);
2299: p_status:='CURRENT';
2300: RETURN FALSE;
2301: ELSIF p_cp_pf_org_id IS NOT NULL THEN /*There were some Organizational Changes*/
2302: pay_in_utils.set_location(g_debug,l_procedure, 40);
2298: pay_in_utils.set_location(g_debug,l_procedure, 30);
2299: p_status:='CURRENT';
2300: RETURN FALSE;
2301: ELSIF p_cp_pf_org_id IS NOT NULL THEN /*There were some Organizational Changes*/
2302: pay_in_utils.set_location(g_debug,l_procedure, 40);
2303: OPEN c_transfer_status(p_cp_pf_org_id);
2304: FETCH c_transfer_status INTO l_reason;
2305: CLOSE c_transfer_status;
2306:
2305: CLOSE c_transfer_status;
2306:
2307:
2308: IF l_reason IS NULL THEN --Thats when the last payroll archived didnt have the p_cp_pf_org_id
2309: pay_in_utils.set_location(g_debug,l_procedure, 50);
2310: p_status:='TRANSFERRED';
2311: RETURN TRUE;
2312: ELSE --This means that employee is still attached to that PF org. Now in the last archived data
2313: --there can be multiple records. So we have to check for records count
2310: p_status:='TRANSFERRED';
2311: RETURN TRUE;
2312: ELSE --This means that employee is still attached to that PF org. Now in the last archived data
2313: --there can be multiple records. So we have to check for records count
2314: pay_in_utils.set_location(g_debug,l_procedure, 60);
2315: OPEN c_last_pay_count;
2316: FETCH c_last_pay_count INTO l_org_count;
2317: CLOSE c_last_pay_count;
2318: IF l_org_count = 1 THEN --This org was present at last payroll action
2315: OPEN c_last_pay_count;
2316: FETCH c_last_pay_count INTO l_org_count;
2317: CLOSE c_last_pay_count;
2318: IF l_org_count = 1 THEN --This org was present at last payroll action
2319: pay_in_utils.set_location(g_debug,l_procedure, 70);
2320: p_status:='CURRENT';
2321: RETURN FALSE;
2322: ELSE
2323: pay_in_utils.set_location(g_debug,l_procedure, 80);
2319: pay_in_utils.set_location(g_debug,l_procedure, 70);
2320: p_status:='CURRENT';
2321: RETURN FALSE;
2322: ELSE
2323: pay_in_utils.set_location(g_debug,l_procedure, 80);
2324: l_reason:=NULL;
2325: OPEN c_last_pay_date;
2326: FETCH c_last_pay_date INTO l_pay_date;
2327: CLOSE c_last_pay_date; --Find the last archived payroll date
2329: OPEN c_final_check(p_cp_pf_org_id,l_pay_date);
2330: FETCH c_final_check INTO l_reason;--Find the presence of c_pf_org_id in SCL
2331: CLOSE c_final_check;
2332: IF l_reason IS NULL THEN --p_cp_pf_org_id is not in SCL
2333: pay_in_utils.set_location(g_debug,l_procedure, 90);
2334: p_status:='TRANSFERRED';
2335: RETURN TRUE;
2336: ELSE
2337: pay_in_utils.set_location(g_debug,l_procedure, 100);
2333: pay_in_utils.set_location(g_debug,l_procedure, 90);
2334: p_status:='TRANSFERRED';
2335: RETURN TRUE;
2336: ELSE
2337: pay_in_utils.set_location(g_debug,l_procedure, 100);
2338: p_status:='CURRENT';--p_cp_pf_org_id is in SCl
2339: RETURN FALSE;
2340: END IF;
2341: END IF;--End for l_org_count
2341: END IF;--End for l_org_count
2342: END IF;--End for l_reason being null
2343:
2344: ELSE
2345: pay_in_utils.set_location(g_debug,l_procedure, 110);
2346: l_reason:=NULL;
2347: OPEN c_transfer_status(p_pf_org_id);
2348: FETCH c_transfer_status INTO l_reason;
2349: CLOSE c_transfer_status;
2348: FETCH c_transfer_status INTO l_reason;
2349: CLOSE c_transfer_status;
2350:
2351: IF l_reason IS NULL THEN --Thats when the last payroll archived had the p_pf_org_id
2352: pay_in_utils.set_location(g_debug,l_procedure, 120);
2353: p_status:='TRANSFERRED';
2354: RETURN TRUE;
2355: ELSE --This means that employee is still attached to that PF org. Now in the last archived data
2356: --there can be multiple records. So we have to check for records count
2353: p_status:='TRANSFERRED';
2354: RETURN TRUE;
2355: ELSE --This means that employee is still attached to that PF org. Now in the last archived data
2356: --there can be multiple records. So we have to check for records count
2357: pay_in_utils.set_location(g_debug,l_procedure, 130);
2358: OPEN c_last_pay_count;
2359: FETCH c_last_pay_count INTO l_org_count;
2360: CLOSE c_last_pay_count;
2361: IF l_org_count < 2 THEN --This org was present at last payroll action
2358: OPEN c_last_pay_count;
2359: FETCH c_last_pay_count INTO l_org_count;
2360: CLOSE c_last_pay_count;
2361: IF l_org_count < 2 THEN --This org was present at last payroll action
2362: pay_in_utils.set_location(g_debug,l_procedure, 140);
2363: p_status:='CURRENT';
2364: RETURN FALSE;
2365: ELSE
2366: pay_in_utils.set_location(g_debug,l_procedure, 150);
2362: pay_in_utils.set_location(g_debug,l_procedure, 140);
2363: p_status:='CURRENT';
2364: RETURN FALSE;
2365: ELSE
2366: pay_in_utils.set_location(g_debug,l_procedure, 150);
2367: l_reason:=NULL;
2368: OPEN c_last_pay_date;
2369: FETCH c_last_pay_date INTO l_pay_date;
2370: CLOSE c_last_pay_date; --Find the last archived payroll date
2373: FETCH c_final_check INTO l_reason;--Find the presence of c_pf_org_id in SCL
2374: CLOSE c_final_check;
2375:
2376: IF l_reason IS NULL THEN --p_cp_pf_org_id is not in SCL
2377: pay_in_utils.set_location(g_debug,l_procedure, 160);
2378: p_status:='TRANSFERRED';
2379: RETURN TRUE;
2380: ELSE
2381: pay_in_utils.set_location(g_debug,l_procedure, 170);
2377: pay_in_utils.set_location(g_debug,l_procedure, 160);
2378: p_status:='TRANSFERRED';
2379: RETURN TRUE;
2380: ELSE
2381: pay_in_utils.set_location(g_debug,l_procedure, 170);
2382: p_status:='CURRENT';--p_cp_pf_org_id is in SCl
2383: RETURN FALSE;
2384: END IF;
2385: END IF;
2386:
2387: END IF;
2388: END IF;
2389: ELSIF p_employee_type = 'TERMINATED' THEN
2390: pay_in_utils.set_location(g_debug,l_procedure, 180);
2391: OPEN c_termination_check(l_person_id);
2392: FETCH c_termination_check INTO l_reason;
2393: CLOSE c_termination_check;
2394:
2393: CLOSE c_termination_check;
2394:
2395:
2396: IF l_reason IS NULL THEN
2397: pay_in_utils.set_location(g_debug,l_procedure, 190);
2398: RETURN FALSE;
2399: ELSE
2400: RETURN TRUE;
2401: END IF;
2400: RETURN TRUE;
2401: END IF;
2402:
2403: ELSIF p_employee_type = 'CURRENT' THEN
2404: pay_in_utils.set_location(g_debug,l_procedure, 200);
2405: OPEN c_termination_check(l_person_id);
2406: FETCH c_termination_check INTO l_reason;
2407: CLOSE c_termination_check;
2408:
2408:
2409:
2410: IF l_reason IS NULL THEN
2411: --The employee is not terminated, So find whether he is in that org or not
2412: pay_in_utils.set_location(g_debug,l_procedure, 210);
2413: IF p_cp_pf_org_id IS NOT NULL THEN
2414: OPEN c_transfer_status(p_cp_pf_org_id);
2415: FETCH c_transfer_status INTO l_reason;
2416: CLOSE c_transfer_status;
2414: OPEN c_transfer_status(p_cp_pf_org_id);
2415: FETCH c_transfer_status INTO l_reason;
2416: CLOSE c_transfer_status;
2417:
2418: pay_in_utils.set_location(g_debug,l_procedure, 220);
2419:
2420: IF l_reason IS NULL THEN
2421: pay_in_utils.set_location(g_debug,l_procedure, 230);
2422: p_status:='TRANSFERRED'; --This is because on last day the pf org is not as parameter passed
2417:
2418: pay_in_utils.set_location(g_debug,l_procedure, 220);
2419:
2420: IF l_reason IS NULL THEN
2421: pay_in_utils.set_location(g_debug,l_procedure, 230);
2422: p_status:='TRANSFERRED'; --This is because on last day the pf org is not as parameter passed
2423: RETURN FALSE; -- Bug 4033745
2424: ELSE
2425: --This means that assignment is still having p_cp_pf_org_id. But there may be multiple records
2423: RETURN FALSE; -- Bug 4033745
2424: ELSE
2425: --This means that assignment is still having p_cp_pf_org_id. But there may be multiple records
2426: --So first checking for them.
2427: pay_in_utils.set_location(g_debug,l_procedure, 240);
2428: OPEN c_last_pay_count;
2429: FETCH c_last_pay_count INTO l_org_count;
2430: CLOSE c_last_pay_count;
2431:
2429: FETCH c_last_pay_count INTO l_org_count;
2430: CLOSE c_last_pay_count;
2431:
2432: IF l_org_count = 1 THEN --This org was present at last payroll action as there were only 1 rec
2433: pay_in_utils.set_location(g_debug,l_procedure, 250);
2434: p_status:='CURRENT';
2435: RETURN TRUE;
2436: ELSE
2437: pay_in_utils.set_location(g_debug,l_procedure, 260);
2433: pay_in_utils.set_location(g_debug,l_procedure, 250);
2434: p_status:='CURRENT';
2435: RETURN TRUE;
2436: ELSE
2437: pay_in_utils.set_location(g_debug,l_procedure, 260);
2438: l_reason:=NULL;
2439: OPEN c_last_pay_date;
2440: FETCH c_last_pay_date INTO l_pay_date;
2441: CLOSE c_last_pay_date; --Find the last archived payroll date
2442: OPEN c_final_check(p_cp_pf_org_id,l_pay_date);
2443: FETCH c_final_check INTO l_reason;--Find the presence of c_pf_org_id in SCL
2444: CLOSE c_final_check;
2445: IF l_reason IS NULL THEN --p_cp_pf_org_id is not in SCL
2446: pay_in_utils.set_location(g_debug,l_procedure, 270);
2447: p_status:='TRANSFERRED';
2448: RETURN FALSE;
2449: ELSE
2450: pay_in_utils.set_location(g_debug,l_procedure, 280);
2446: pay_in_utils.set_location(g_debug,l_procedure, 270);
2447: p_status:='TRANSFERRED';
2448: RETURN FALSE;
2449: ELSE
2450: pay_in_utils.set_location(g_debug,l_procedure, 280);
2451: p_status:='CURRENT';--p_cp_pf_org_id is in SCl
2452: RETURN TRUE;
2453: END IF;
2454: END IF;
2453: END IF;
2454: END IF;
2455: END IF;
2456: ELSE
2457: pay_in_utils.set_location(g_debug,l_procedure, 290);
2458: OPEN c_transfer_status(p_pf_org_id);
2459: FETCH c_transfer_status INTO l_reason;
2460: CLOSE c_transfer_status;
2461:
2459: FETCH c_transfer_status INTO l_reason;
2460: CLOSE c_transfer_status;
2461:
2462: IF l_reason IS NULL THEN
2463: pay_in_utils.set_location(g_debug,l_procedure, 300);
2464: p_status:='TRANSFERRED'; --This is because on last day the pf org is not as parameter passed
2465: RETURN FALSE; -- Bug 4033745
2466: ELSE
2467: pay_in_utils.set_location(g_debug,l_procedure, 310);
2463: pay_in_utils.set_location(g_debug,l_procedure, 300);
2464: p_status:='TRANSFERRED'; --This is because on last day the pf org is not as parameter passed
2465: RETURN FALSE; -- Bug 4033745
2466: ELSE
2467: pay_in_utils.set_location(g_debug,l_procedure, 310);
2468: --This means that assignment is still having p_cp_pf_org_id. But there may be multiple records
2469: --So first checking for them.
2470: OPEN c_last_pay_count;
2471: FETCH c_last_pay_count INTO l_org_count;
2470: OPEN c_last_pay_count;
2471: FETCH c_last_pay_count INTO l_org_count;
2472: CLOSE c_last_pay_count;
2473: IF l_org_count = 1 THEN --This org was present at last payroll action as there were only 1 rec
2474: pay_in_utils.set_location(g_debug,l_procedure, 320);
2475: p_status:='CURRENT';
2476: RETURN TRUE;
2477: ELSE
2478: pay_in_utils.set_location(g_debug,l_procedure, 330);
2474: pay_in_utils.set_location(g_debug,l_procedure, 320);
2475: p_status:='CURRENT';
2476: RETURN TRUE;
2477: ELSE
2478: pay_in_utils.set_location(g_debug,l_procedure, 330);
2479: l_reason:=NULL;
2480: OPEN c_last_pay_date;
2481: FETCH c_last_pay_date INTO l_pay_date;
2482: CLOSE c_last_pay_date; --Find the last archived payroll date
2484: OPEN c_final_check(p_pf_org_id,l_pay_date);
2485: FETCH c_final_check INTO l_reason;--Find the presence of c_pf_org_id in SCL
2486: CLOSE c_final_check;
2487: IF l_reason IS NULL THEN --p_pf_org_id is not in SCL
2488: pay_in_utils.set_location(g_debug,l_procedure, 330);
2489: p_status:='TRANSFERRED';
2490: RETURN FALSE;
2491: ELSE
2492: pay_in_utils.set_location(g_debug,l_procedure, 340);
2488: pay_in_utils.set_location(g_debug,l_procedure, 330);
2489: p_status:='TRANSFERRED';
2490: RETURN FALSE;
2491: ELSE
2492: pay_in_utils.set_location(g_debug,l_procedure, 340);
2493: p_status:='CURRENT';--p_pf_org_id is in SCl
2494: RETURN TRUE;
2495: END IF;
2496: END IF;
2496: END IF;
2497: END IF;
2498: END IF;
2499: ELSE
2500: pay_in_utils.set_location(g_debug,l_procedure, 350);
2501: RETURN FALSE;
2502: END IF;
2503:
2504: ELSE
2501: RETURN FALSE;
2502: END IF;
2503:
2504: ELSE
2505: pay_in_utils.set_location(g_debug,l_procedure, 360);
2506: RETURN TRUE;
2507: END IF;
2508:
2509: END;
2761: pay_in_xml_utils.gXMLTable.DELETE;
2762:
2763: g_debug := hr_utility.debug_enabled;
2764: l_procedure := g_package ||'create_form6a_xml';
2765: pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
2766:
2767: --
2768: fnd_file.put_line(fnd_file.log,'Creating the XML...');
2769: dbms_lob.createtemporary(g_xml_data,FALSE,DBMS_LOB.CALL);
2782: --System Date
2783: l_tag :=pay_in_xml_utils.getTag('c_current_date_in_hh_mm_ss',l_sys_date_time);
2784: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
2785:
2786: pay_in_utils.set_location(g_debug,l_procedure, 20);
2787:
2788: FOR c_rec IN c_pf_org_id(p_pf_org_id
2789: ,p_effective_start_date
2790: ,p_effective_end_date
2789: ,p_effective_start_date
2790: ,p_effective_end_date
2791: ,p_contribution_period)
2792: LOOP
2793: pay_in_utils.set_location(g_debug,l_procedure, 30);
2794: l_org_pf_ytd := 0 ;
2795: l_org_employer := 0 ;
2796: l_org_employee := 0 ;
2797: l_org_pension := 0 ;
2813: pay_in_xml_utils.gXMLTable(l_count).Value := (c_rec.action_information6);
2814: l_count := l_count + 1;
2815:
2816: IF g_debug THEN
2817: pay_in_utils.trace('PF Organization ',c_rec.action_information3);
2818: END IF;
2819:
2820: --Statuory Rate of Contribution
2821: SELECT ROUND(fnd_number.canonical_to_number(GLOBAL_VALUE)*100,2) INTO l_rate
2835: pay_in_xml_utils.gXMLTable(l_count).Name := 'c_end_year';
2836: pay_in_xml_utils.gXMLTable(l_count).Value := (to_char(p_effective_end_date,'YYYY'));
2837: l_count := l_count + 1;
2838:
2839: pay_in_utils.set_location(g_debug,l_procedure, 30);
2840:
2841: --Number of people making Voluntary Contributions in that financial Year
2842: OPEN c_number_of_contr(c_rec.action_information2
2843: ,p_effective_start_date
2849: l_count := l_count + 1;
2850: pay_in_xml_utils.multiColumnar('org',pay_in_xml_utils.gXMLTable,l_count,g_xml_data);
2851: l_employee_no:=1;
2852:
2853: pay_in_utils.set_location(g_debug,l_procedure, 40);
2854:
2855: FOR assignment_rec IN c_assignment_id(c_rec.action_information2
2856: ,p_effective_start_date
2857: ,p_effective_end_date
2856: ,p_effective_start_date
2857: ,p_effective_end_date
2858: ,p_contribution_period)
2859: LOOP
2860: pay_in_utils.set_location(g_debug,l_procedure, 50);
2861:
2862: IF g_debug THEN
2863: pay_in_utils.trace('PF Number ',assignment_rec.action_information3);
2864: END IF;
2859: LOOP
2860: pay_in_utils.set_location(g_debug,l_procedure, 50);
2861:
2862: IF g_debug THEN
2863: pay_in_utils.trace('PF Number ',assignment_rec.action_information3);
2864: END IF;
2865:
2866: FOR child_asg_rec IN c_asg_summation_details(c_rec.action_information2
2867: ,assignment_rec.action_information3
2917:
2918: l_remarks := NULL;
2919: l_remarks_dummy := NULL;
2920:
2921: pay_in_utils.set_location(g_debug,l_procedure, 50);
2922:
2923: For c_rec_child IN c_asg_details(c_rec.action_information2 --PF Org ID
2924: ,assignment_rec.action_information3
2925: ,p_effective_start_date
2924: ,assignment_rec.action_information3
2925: ,p_effective_start_date
2926: ,p_effective_end_date)
2927: LOOP
2928: pay_in_utils.set_location(g_debug,l_procedure, 60);
2929: l_payroll_mon := TO_NUMBER(TO_CHAR(c_rec_child.mon,'MM'));
2930: IF (l_payroll_mon <4 ) THEN
2931: l_mon := l_payroll_mon + 9;
2932: ELSE
2946: l_remarks:=l_remarks_dummy;
2947: END IF;
2948: END LOOP;
2949:
2950: pay_in_utils.set_location(g_debug,l_procedure, 70);
2951:
2952: OPEN c_vol_rate(c_rec.action_information2 --PF Org ID
2953: ,assignment_rec.action_information3
2954: ,p_effective_start_date
2966: pay_in_xml_utils.gXMLTable(l_count).Name := 'c_remarks';
2967: pay_in_xml_utils.gXMLTable(l_count).Value := (l_remarks);
2968: l_count := l_count + 1;
2969: pay_in_xml_utils.multiColumnar('details',pay_in_xml_utils.gXMLTable,l_count,g_xml_data);
2970: pay_in_utils.set_location(g_debug,l_procedure, 80);
2971: END LOOP;
2972: END LOOP;
2973: pay_in_utils.set_location(g_debug,l_procedure, 90);
2974: pay_in_xml_utils.gXMLTable.delete;
2969: pay_in_xml_utils.multiColumnar('details',pay_in_xml_utils.gXMLTable,l_count,g_xml_data);
2970: pay_in_utils.set_location(g_debug,l_procedure, 80);
2971: END LOOP;
2972: END LOOP;
2973: pay_in_utils.set_location(g_debug,l_procedure, 90);
2974: pay_in_xml_utils.gXMLTable.delete;
2975: l_count:=1;
2976: pay_in_xml_utils.gXMLTable(l_count).Name := 'c_wage_ytd';
2977: pay_in_xml_utils.gXMLTable(l_count).Value :=
3000: l_edli_adm_total:=0;
3001: l_summation:=0;
3002:
3003: i := 0;
3004: pay_in_utils.set_location(g_debug,l_procedure, 200);
3005: WHILE i<12
3006: LOOP
3007: l_epf :=0;
3008: l_pension :=0;
3014: ,p_effective_start_date
3015: ,p_effective_end_date
3016: ,i)
3017: LOOP
3018: pay_in_utils.set_location(g_debug,l_procedure, 210);
3019: l_epf :=c_pf_org_child_rec.epf;
3020: l_pension :=c_pf_org_child_rec.pension;
3021: l_dli :=c_pf_org_child_rec.dli;
3022: l_admn :=c_pf_org_child_rec.admin;
3040: ,l_edli_adm
3041: ,l_summ);
3042: i:=i+1;
3043: END LOOP;
3044: pay_in_utils.set_location(g_debug,l_procedure, 220);
3045: pay_in_xml_utils.gXMLTable.delete;
3046: l_count:=1;
3047: --EPF Contributions Total
3048: pay_in_xml_utils.gXMLTable(l_count).Name := 'c_epf_org_total';
3076: l_count := l_count + 1;
3077: --No of employees in Form 6A
3078: pay_in_xml_utils.gXMLTable(l_count).Name := 'c_emp_no';
3079:
3080: pay_in_utils.set_location(g_debug,l_procedure, 230);
3081:
3082: SELECT COUNT(DISTINCT action_information3)
3083: INTO pay_in_xml_utils.gXMLTable(l_count).Value
3084: FROM pay_action_information
3097: ,p_effective_start_date
3098: ,p_effective_end_date);
3099: FETCH c_rep_name INTO pay_in_xml_utils.gXMLTable(l_count).Value;
3100: CLOSE c_rep_name;
3101: pay_in_utils.set_location(g_debug,l_procedure, 240);
3102: l_count := l_count + 1;
3103: pay_in_xml_utils.multiColumnar('pf_org_sum',pay_in_xml_utils.gXMLTable,l_count,g_xml_data);
3104: /* Ending Starts here*/
3105: l_tag := '';
3106: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
3107: pay_in_xml_utils.gXMLTable.delete;
3108: l_row_count:=null;
3109: END LOOP;
3110: pay_in_utils.set_location(g_debug,l_procedure, 240);
3111: l_tag := '';
3112: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
3113: fnd_file.put_line(fnd_file.log,'XML Created.');
3114:
3323:
3324: BEGIN
3325: g_debug := hr_utility.debug_enabled;
3326: l_procedure := g_package ||'create_form3a_xml';
3327: pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
3328:
3329: pay_in_xml_utils.gXMLTable.DELETE;
3330: --
3331: fnd_file.put_line(fnd_file.log,'Creating the XML...');
3345: l_tag :=pay_in_xml_utils.getTag('c_current_date_in_hh_mm_ss',l_sys_date_time);
3346: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
3347: l_cp_pf_org_id := p_pf_org_id;
3348:
3349: pay_in_utils.set_location(g_debug,l_procedure, 20);
3350:
3351: FOR c_org IN c_distinct_org_id (p_pf_org_id
3352: ,p_pf_number
3353: ,p_contribution_period
3355: ,p_effective_start_date
3356: ,p_effective_end_date
3357: )
3358: LOOP
3359: pay_in_utils.set_location(g_debug,l_procedure, 30);
3360:
3361: IF g_debug THEN
3362: pay_in_utils.trace('p_effective_start_date ',p_effective_start_date);
3363: pay_in_utils.trace('p_effective_end_date ',p_effective_end_date);
3358: LOOP
3359: pay_in_utils.set_location(g_debug,l_procedure, 30);
3360:
3361: IF g_debug THEN
3362: pay_in_utils.trace('p_effective_start_date ',p_effective_start_date);
3363: pay_in_utils.trace('p_effective_end_date ',p_effective_end_date);
3364: pay_in_utils.trace('p_employee_type ',p_employee_type);
3365: pay_in_utils.trace('c_org.pf_org_id ',c_org.pf_org_id);
3366: END IF;
3359: pay_in_utils.set_location(g_debug,l_procedure, 30);
3360:
3361: IF g_debug THEN
3362: pay_in_utils.trace('p_effective_start_date ',p_effective_start_date);
3363: pay_in_utils.trace('p_effective_end_date ',p_effective_end_date);
3364: pay_in_utils.trace('p_employee_type ',p_employee_type);
3365: pay_in_utils.trace('c_org.pf_org_id ',c_org.pf_org_id);
3366: END IF;
3367:
3360:
3361: IF g_debug THEN
3362: pay_in_utils.trace('p_effective_start_date ',p_effective_start_date);
3363: pay_in_utils.trace('p_effective_end_date ',p_effective_end_date);
3364: pay_in_utils.trace('p_employee_type ',p_employee_type);
3365: pay_in_utils.trace('c_org.pf_org_id ',c_org.pf_org_id);
3366: END IF;
3367:
3368:
3361: IF g_debug THEN
3362: pay_in_utils.trace('p_effective_start_date ',p_effective_start_date);
3363: pay_in_utils.trace('p_effective_end_date ',p_effective_end_date);
3364: pay_in_utils.trace('p_employee_type ',p_employee_type);
3365: pay_in_utils.trace('c_org.pf_org_id ',c_org.pf_org_id);
3366: END IF;
3367:
3368:
3369: l_bg_id:=fnd_profile.value('PER_BUSINESS_GROUP_ID');
3375: ,l_cp_pf_org_id
3376: ,c_org.pf_org_id
3377: ,l_status)
3378: THEN
3379: pay_in_utils.set_location(g_debug,l_procedure, 40);
3380:
3381: IF g_debug THEN
3382: pay_in_utils.trace('Status ',l_status);
3383: pay_in_utils.trace('Cp PF org id ',l_cp_pf_org_id);
3378: THEN
3379: pay_in_utils.set_location(g_debug,l_procedure, 40);
3380:
3381: IF g_debug THEN
3382: pay_in_utils.trace('Status ',l_status);
3383: pay_in_utils.trace('Cp PF org id ',l_cp_pf_org_id);
3384: pay_in_utils.trace('PF org id ',c_org.pf_org_id);
3385: pay_in_utils.trace('PF Num ',c_org.pf_num );
3386: pay_in_utils.trace('p_employee_type ',p_employee_type );
3379: pay_in_utils.set_location(g_debug,l_procedure, 40);
3380:
3381: IF g_debug THEN
3382: pay_in_utils.trace('Status ',l_status);
3383: pay_in_utils.trace('Cp PF org id ',l_cp_pf_org_id);
3384: pay_in_utils.trace('PF org id ',c_org.pf_org_id);
3385: pay_in_utils.trace('PF Num ',c_org.pf_num );
3386: pay_in_utils.trace('p_employee_type ',p_employee_type );
3387: END IF;
3380:
3381: IF g_debug THEN
3382: pay_in_utils.trace('Status ',l_status);
3383: pay_in_utils.trace('Cp PF org id ',l_cp_pf_org_id);
3384: pay_in_utils.trace('PF org id ',c_org.pf_org_id);
3385: pay_in_utils.trace('PF Num ',c_org.pf_num );
3386: pay_in_utils.trace('p_employee_type ',p_employee_type );
3387: END IF;
3388:
3381: IF g_debug THEN
3382: pay_in_utils.trace('Status ',l_status);
3383: pay_in_utils.trace('Cp PF org id ',l_cp_pf_org_id);
3384: pay_in_utils.trace('PF org id ',c_org.pf_org_id);
3385: pay_in_utils.trace('PF Num ',c_org.pf_num );
3386: pay_in_utils.trace('p_employee_type ',p_employee_type );
3387: END IF;
3388:
3389: FOR c_rec IN c_asg_id(c_org.pf_org_id --PF Org ID
3382: pay_in_utils.trace('Status ',l_status);
3383: pay_in_utils.trace('Cp PF org id ',l_cp_pf_org_id);
3384: pay_in_utils.trace('PF org id ',c_org.pf_org_id);
3385: pay_in_utils.trace('PF Num ',c_org.pf_num );
3386: pay_in_utils.trace('p_employee_type ',p_employee_type );
3387: END IF;
3388:
3389: FOR c_rec IN c_asg_id(c_org.pf_org_id --PF Org ID
3390: ,c_org.pf_num --PF Number
3394: ,p_contribution_period
3395: ,l_cp_pf_org_id
3396: ,l_status)
3397: LOOP
3398: pay_in_utils.set_location(g_debug,l_procedure, 50);
3399: OPEN c_name_fath_hus_name(c_org.pf_org_id --PF Org ID
3400: ,c_org.pf_num --PF Number
3401: ,p_effective_start_date
3402: ,p_effective_end_date
3434: l_row_count:=0;
3435: --Individual Month Record Determination
3436: FOR i IN 3..12
3437: LOOP
3438: pay_in_utils.set_location(g_debug,l_procedure, 60);
3439: l_pf_salary_ptd :=0;
3440: l_epf :=0;
3441: l_epf_diff :=0;
3442: l_pension_fund :=0;
3449: ,p_effective_end_date
3450: ,p_contribution_period
3451: ,i)
3452: LOOP
3453: pay_in_utils.set_location(g_debug,l_procedure, 70);
3454: l_pf_salary_ptd :=c_rec_child.action_information7;
3455: l_epf :=c_rec_child.action_information8;
3456: l_epf_diff :=c_rec_child.action_information9;
3457: l_pension_fund :=c_rec_child.action_information10;
3482: ,l_absence
3483: ,l_remarks);
3484:
3485: END LOOP;
3486: pay_in_utils.set_location(g_debug,l_procedure, 80);
3487:
3488: FOR i IN 1..2
3489: LOOP
3490: l_pf_salary_ptd :=0;
3492: l_epf_diff :=0;
3493: l_pension_fund :=0;
3494: l_absence :=0;
3495: l_remarks :=NULL;
3496: pay_in_utils.set_location(g_debug,l_procedure, 90);
3497:
3498: FOR c_rec_child IN c_asg_details(c_org.pf_org_id --PF Org ID
3499: ,c_org.pf_num --PF Number
3500: ,p_effective_start_date
3529: ,l_remarks);
3530:
3531: END LOOP;
3532:
3533: pay_in_utils.set_location(g_debug,l_procedure, 100);
3534: --Voluntary Higher Contr Rate
3535: l_tag :=pay_in_xml_utils.getTag('c_voluntary_rate',to_number(l_voluntary_contribution_rate,999.99));
3536: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
3537: --Employer Representative name
3559: --Total Employee Employer Contr
3560: l_tag :=pay_in_xml_utils.getTag('c_employer_employee_ytd',pay_us_employee_payslip_web.get_format_value(l_bg_id,c_sum.total));
3561: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
3562: END LOOP;
3563: pay_in_utils.set_location(g_debug,l_procedure, 110);
3564: --Current Date
3565: l_tag :=pay_in_xml_utils.getTag('c_current_date',to_char(SYSDATE,'DD-Mon-YYYY'));
3566: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
3567: --Starting Year
3580: l_tag :=pay_in_xml_utils.getTag('c_stat_rate',to_number(l_rate,99.99));
3581: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
3582: l_tag := '';
3583: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
3584: pay_in_utils.set_location(g_debug,l_procedure, 120);
3585: END LOOP;
3586: END IF;
3587: END LOOP;
3588: --
3585: END LOOP;
3586: END IF;
3587: END LOOP;
3588: --
3589: pay_in_utils.set_location(g_debug,l_procedure, 130);
3590: l_tag := '';
3591: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
3592: fnd_file.put_line(fnd_file.log,'XML Created.');
3593: END create_form3a_xml;
3812:
3813: BEGIN
3814: g_debug := hr_utility.debug_enabled;
3815: l_procedure := g_package ||'create_form7_xml';
3816: pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
3817:
3818: pay_in_xml_utils.gXMLTable.DELETE;
3819:
3820: --
3851: l_bg_id:= fnd_profile.value('PER_BUSINESS_GROUP_ID');
3852:
3853:
3854: fnd_file.put_line(fnd_file.log,'Start of Exempted Org list.');
3855: pay_in_utils.set_location(g_debug,l_procedure, 20);
3856:
3857: FOR c_pf_orglist IN csr_exempted_pf_orglist LOOP
3858: --
3859: -- Get details in sequence for each of the above organization and store it in a PLSQL table . This will be
3859: -- Get details in sequence for each of the above organization and store it in a PLSQL table . This will be
3860: -- used by each record returned by the inner query .i.e for each pension number
3861: -- that is attached to this PF org
3862: g_org_XMLTable.delete;
3863: pay_in_utils.set_location(g_debug,l_procedure, 30);
3864: OPEN csr_pf_org_details(c_pf_orglist.orgid);
3865: FETCH csr_pf_org_details INTO l_reg_name,l_rep_name,l_org_address,l_est_code;
3866: CLOSE csr_pf_org_details;
3867:
3891: FOR c_master_rec IN csr_pension_number(c_pf_orglist.orgid) LOOP
3892: -- Repeat this for all distinct (pension number,assignment id combinations . Rehire cases will not be
3893: -- considered.
3894: -- fnd_file.put_line(fnd_file.log,'Get details for the assignment id.'||c_master_rec.asg_id);
3895: pay_in_utils.set_location(g_debug,l_procedure, 40);
3896: IF (employee_type(c_master_rec.pension_number
3897: ,p_employee_type
3898: ,p_effective_start_date
3899: ,p_effective_end_date
3900: ,p_cp_pf_org_id -- Conc Program parameter
3901: ,c_pf_orglist.orgid
3902: ,p_status))
3903: THEN
3904: pay_in_utils.set_location(g_debug,l_procedure, 50);
3905: fnd_file.put_line(fnd_file.log,'Inside the Employee Type Check');
3906: OPEN csr_not_excluded_employee(c_pf_orglist.orgid
3907: ,p_contribution_period
3908: ,c_master_rec.pension_number);
3909: FETCH csr_not_excluded_employee INTO l_excluded_employee_status;
3910: CLOSE csr_not_excluded_employee;
3911:
3912: IF (l_excluded_employee_status = 'X') THEN
3913: pay_in_utils.set_location(g_debug,l_procedure, 60);
3914: OPEN csr_employee_details(c_master_rec.pension_number,c_pf_orglist.orgid);
3915: FETCH csr_employee_details INTO l_employee_name,l_fath_husb_name,l_hire_date;
3916: CLOSE csr_employee_details;
3917:
3935: FOR c_rec_child IN csr_month_contributions(c_pf_orglist.orgid
3936: ,c_master_rec.pension_number
3937: )
3938: LOOP
3939: pay_in_utils.set_location(g_debug,l_procedure, 70);
3940: l_month := MONTHS_BETWEEN(TRUNC(to_date(c_rec_child.date_earned,'DD-MM-YY'),'MM')
3941: ,p_effective_start_date);
3942: --
3943: -- Add null records in case no records exists
3943: -- Add null records in case no records exists
3944: --
3945: l_cont_type_date_earned := 'DEFAULT';
3946: IF l_cont_type = 'DEFAULT' THEN
3947: l_cont_type :=pay_in_utils.get_scl_segment_on_date(c_rec_child.assignment_id
3948: ,l_bg_id
3949: ,GREATEST(l_hire_date,TO_DATE ('1-Apr-'||SUBSTR(p_contribution_period,1,4),'DD-MM-YY'))
3950: ,'segment12');
3951: IF(l_cont_type = '0')THEN
3953: END IF;
3954: END IF;
3955:
3956: IF l_cont_type_date_earned = 'DEFAULT' THEN
3957: l_cont_type_date_earned :=pay_in_utils.get_scl_segment_on_date(c_rec_child.assignment_id
3958: ,l_bg_id
3959: ,c_rec_child.date_earned
3960: ,'segment12');
3961: IF(l_cont_type_date_earned = '0')THEN
3990: --
3991: END IF;
3992: IF l_row_count = l_month THEN
3993: --
3994: pay_in_utils.set_location(g_debug,l_procedure, 80);
3995: l_month_name:=c_rec_child.date_earned;
3996:
3997: l_row_count:=l_row_count+1;
3998: l_month_pf_wages := TO_NUMBER(c_rec_child.pf_wages);
4113: END IF;-- Excluded employee status end
4114: END IF; -- Employee Type Check End
4115:
4116: END LOOP; -- End each pension number
4117: pay_in_utils.set_location(g_debug,l_procedure, 90);
4118: fnd_file.put_line(fnd_file.log,'XML Created for a PF Org.Moving onto next');
4119: END LOOP; -- End Each Organization
4120:
4121: l_tag := '';
4285: BEGIN
4286:
4287: g_debug := hr_utility.debug_enabled;
4288: l_procedure := g_package ||'esi_employee_type';
4289: pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
4290:
4291: OPEN c_person_id;
4292: FETCH c_person_id INTO l_person_id;
4293: CLOSE c_person_id;
4297: l_reason := NULL;
4298: l_start_date :=to_date('01-'||SUBSTR(p_contribution_period,1,8),'DD-MM-YYYY');
4299: l_end_date :=add_months(to_date('01-'||SUBSTR(p_contribution_period,11),'DD-MM-YYYY'),1)-1;
4300:
4301: pay_in_utils.set_location(g_debug,l_procedure, 20);
4302:
4303: IF g_debug THEN
4304: pay_in_utils.trace('l_start_date ',l_start_date);
4305: pay_in_utils.trace('l_end_date ',l_end_date);
4300:
4301: pay_in_utils.set_location(g_debug,l_procedure, 20);
4302:
4303: IF g_debug THEN
4304: pay_in_utils.trace('l_start_date ',l_start_date);
4305: pay_in_utils.trace('l_end_date ',l_end_date);
4306: END IF;
4307:
4308: --Find the last payroll date
4301: pay_in_utils.set_location(g_debug,l_procedure, 20);
4302:
4303: IF g_debug THEN
4304: pay_in_utils.trace('l_start_date ',l_start_date);
4305: pay_in_utils.trace('l_end_date ',l_end_date);
4306: END IF;
4307:
4308: --Find the last payroll date
4309: OPEN c_last_pay_date;
4315: FETCH c_esi_exemption INTO l_exem_status;
4316: CLOSE c_esi_exemption;
4317:
4318: IF g_debug THEN
4319: pay_in_utils.trace('Exemption status ',l_exem_status);
4320: END IF;
4321:
4322:
4323: --Find date of death.
4331: END IF;
4332: l_reason := NULL;
4333: --Termination check
4334:
4335: pay_in_utils.set_location(g_debug,l_procedure, 30);
4336:
4337: OPEN c_termination_check(l_person_id,l_start_date,l_end_date);
4338: FETCH c_termination_check INTO l_term_date;
4339: CLOSE c_termination_check;
4344: END IF;
4345:
4346:
4347: IF g_debug THEN
4348: pay_in_utils.trace('Reason ',l_reason);
4349: pay_in_utils.trace('Termination ',l_term_date);
4350: pay_in_utils.trace('Sysdate ',p_sysdate);
4351: pay_in_utils.trace('p_esi_coverage ',p_esi_coverage);
4352: END IF;
4345:
4346:
4347: IF g_debug THEN
4348: pay_in_utils.trace('Reason ',l_reason);
4349: pay_in_utils.trace('Termination ',l_term_date);
4350: pay_in_utils.trace('Sysdate ',p_sysdate);
4351: pay_in_utils.trace('p_esi_coverage ',p_esi_coverage);
4352: END IF;
4353:
4346:
4347: IF g_debug THEN
4348: pay_in_utils.trace('Reason ',l_reason);
4349: pay_in_utils.trace('Termination ',l_term_date);
4350: pay_in_utils.trace('Sysdate ',p_sysdate);
4351: pay_in_utils.trace('p_esi_coverage ',p_esi_coverage);
4352: END IF;
4353:
4354: --If sysdate is greater than end date of contribution period then data for all active, terminated
4347: IF g_debug THEN
4348: pay_in_utils.trace('Reason ',l_reason);
4349: pay_in_utils.trace('Termination ',l_term_date);
4350: pay_in_utils.trace('Sysdate ',p_sysdate);
4351: pay_in_utils.trace('p_esi_coverage ',p_esi_coverage);
4352: END IF;
4353:
4354: --If sysdate is greater than end date of contribution period then data for all active, terminated
4355: --and transferred employee should be there.(Provided 3rd Pmt is left blank).
4375:
4376: IF p_esi_coverage = '-1' THEN
4377: l_exem_status:='-1';
4378: END IF;
4379: pay_in_utils.set_location(g_debug,l_procedure, 40);
4380:
4381: --Checking for termination
4382: OPEN c_termination_check(l_person_id,l_start_date,l_end_date);
4383: FETCH c_termination_check INTO l_term_date;
4389: ELSE
4390: RETURN FALSE;
4391: END IF;
4392: END IF;
4393: pay_in_utils.set_location(g_debug,l_procedure, 50);
4394: --Checking for death
4395: OPEN c_death_date(l_start_date,l_end_date);
4396: FETCH c_death_date INTO l_reason;
4397: CLOSE c_death_date;
4402: ELSE
4403: RETURN FALSE;
4404: END IF;
4405: END IF;
4406: pay_in_utils.set_location(g_debug,l_procedure, 60);
4407:
4408: FOR c_rec IN c_transfer_check
4409: LOOP
4410: l_org_count:=l_org_count+1;
4411: END LOOP;
4412: IF l_org_count <2 THEN /*This means there were'nt any changes, i.e the employee was there in the org thru out*/
4413: RETURN FALSE;
4414: END IF;
4415: pay_in_utils.set_location(g_debug,l_procedure, 70);
4416:
4417: IF g_debug THEN
4418: pay_in_utils.trace('l_org_count ',l_org_count);
4419: pay_in_utils.trace('Org ID ',p_org_id);
4414: END IF;
4415: pay_in_utils.set_location(g_debug,l_procedure, 70);
4416:
4417: IF g_debug THEN
4418: pay_in_utils.trace('l_org_count ',l_org_count);
4419: pay_in_utils.trace('Org ID ',p_org_id);
4420: END IF;
4421:
4422:
4415: pay_in_utils.set_location(g_debug,l_procedure, 70);
4416:
4417: IF g_debug THEN
4418: pay_in_utils.trace('l_org_count ',l_org_count);
4419: pay_in_utils.trace('Org ID ',p_org_id);
4420: END IF;
4421:
4422:
4423: IF p_org_id IS NOT NULL THEN
4420: END IF;
4421:
4422:
4423: IF p_org_id IS NOT NULL THEN
4424: pay_in_utils.set_location(g_debug,l_procedure, 80);
4425: --Now checking for transfer cases.
4426: --The following cursor checks for the presence of p_org_id in the archived data
4427: OPEN c_transfer_status(p_org_id);
4428: FETCH c_transfer_status INTO l_reason;
4428: FETCH c_transfer_status INTO l_reason;
4429: CLOSE c_transfer_status;
4430: -- hr_utility.set_location('Checking asg '||p_assignment_id||' for termination',7);
4431: IF l_reason IS NULL THEN -- This means that employee is no longer attached to that p_org_id
4432: pay_in_utils.set_location(g_debug,l_procedure, 90);
4433: IF l_exem_status=p_esi_coverage THEN
4434: RETURN TRUE; -- Hence we can safely return true and process that assignment.
4435: ELSE
4436: RETURN FALSE;
4435: ELSE
4436: RETURN FALSE;
4437: END IF;
4438: ELSE
4439: pay_in_utils.set_location(g_debug,l_procedure, 100);
4440: l_reason := NULL;
4441: l_org_count := 0;
4442: -- hr_utility.set_location('Asg '||p_assignment_id||' not terminated. Checking for org counts',7);
4443: --Here we know that in last archived record employee was attached to p_org_id.
4450: IF l_org_count < 2 THEN
4451: --This means there is only one record and employee is attached to it only.
4452: --Hence he is not a separted employee. So returning false
4453: -- hr_utility.set_location('Asg '||p_assignment_id||' has an org count of '||l_org_count||' and is thus active emp',7);
4454: pay_in_utils.set_location(g_debug,l_procedure, 110);
4455: RETURN FALSE;
4456: ELSE
4457: -- hr_utility.set_location('Asg '||p_assignment_id||' has an org count of '||l_org_count||' and might be transferred',7);
4458: --Employee has multiple records for the last run.
4458: --Employee has multiple records for the last run.
4459: --Now we have to make sure that for which one he is transferred.
4460: --For this we are using the last payroll date and then we shall find the SCL Information.
4461: --Find the last payroll date
4462: pay_in_utils.set_location(g_debug,l_procedure, 120);
4463: OPEN c_last_pay_date;
4464: FETCH c_last_pay_date INTO l_pay_date;
4465: CLOSE c_last_pay_date;
4466:
4470: FETCH c_final_check INTO l_reason;
4471: CLOSE c_final_check ;
4472:
4473: IF l_reason IS NULL AND l_exem_status=p_esi_coverage THEN
4474: pay_in_utils.set_location(g_debug,l_procedure, 130);
4475: --This means that SCL Information no longer has p_org_id.
4476: --Thus the employee has a transfer and we'll return true.
4477: RETURN TRUE ;
4478: ELSIF l_reason IS NOT NULL AND l_exem_status=p_esi_coverage THEN
4475: --This means that SCL Information no longer has p_org_id.
4476: --Thus the employee has a transfer and we'll return true.
4477: RETURN TRUE ;
4478: ELSIF l_reason IS NOT NULL AND l_exem_status=p_esi_coverage THEN
4479: pay_in_utils.set_location(g_debug,l_procedure, 140);
4480: OPEN c_esi_exemption(l_pay_date);
4481: FETCH c_esi_exemption INTO l_exem_status;
4482: CLOSE c_esi_exemption;
4483: IF l_exem_status = 'Y' THEN
4484: RETURN TRUE;
4485: ELSE RETURN FALSE;
4486: END IF;
4487: ELSE
4488: pay_in_utils.set_location(g_debug,l_procedure, 150);
4489: RETURN FALSE;
4490: END IF;
4491: END IF;
4492: END IF;
4830: BEGIN
4831: --
4832: g_debug := hr_utility.debug_enabled;
4833: l_procedure := g_package ||'create_esi_xml';
4834: pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
4835:
4836: pay_in_utils.trace('p_esi_org_id',p_esi_org_id);
4837: pay_in_utils.trace('p_contribution_period',p_contribution_period);
4838: pay_in_utils.trace('p_esi_coverage',p_esi_coverage);
4832: g_debug := hr_utility.debug_enabled;
4833: l_procedure := g_package ||'create_esi_xml';
4834: pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
4835:
4836: pay_in_utils.trace('p_esi_org_id',p_esi_org_id);
4837: pay_in_utils.trace('p_contribution_period',p_contribution_period);
4838: pay_in_utils.trace('p_esi_coverage',p_esi_coverage);
4839: pay_in_utils.trace('p_sysdate',p_sysdate);
4840:
4833: l_procedure := g_package ||'create_esi_xml';
4834: pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
4835:
4836: pay_in_utils.trace('p_esi_org_id',p_esi_org_id);
4837: pay_in_utils.trace('p_contribution_period',p_contribution_period);
4838: pay_in_utils.trace('p_esi_coverage',p_esi_coverage);
4839: pay_in_utils.trace('p_sysdate',p_sysdate);
4840:
4841: l_bg_id:=fnd_profile.value('PER_BUSINESS_GROUP_ID');
4834: pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
4835:
4836: pay_in_utils.trace('p_esi_org_id',p_esi_org_id);
4837: pay_in_utils.trace('p_contribution_period',p_contribution_period);
4838: pay_in_utils.trace('p_esi_coverage',p_esi_coverage);
4839: pay_in_utils.trace('p_sysdate',p_sysdate);
4840:
4841: l_bg_id:=fnd_profile.value('PER_BUSINESS_GROUP_ID');
4842: fnd_file.put_line(fnd_file.log,'Creating the XML...');
4835:
4836: pay_in_utils.trace('p_esi_org_id',p_esi_org_id);
4837: pay_in_utils.trace('p_contribution_period',p_contribution_period);
4838: pay_in_utils.trace('p_esi_coverage',p_esi_coverage);
4839: pay_in_utils.trace('p_sysdate',p_sysdate);
4840:
4841: l_bg_id:=fnd_profile.value('PER_BUSINESS_GROUP_ID');
4842: fnd_file.put_line(fnd_file.log,'Creating the XML...');
4843: dbms_lob.createtemporary(g_xml_data,FALSE,DBMS_LOB.CALL);
4853: l_sys_date_time:=to_char(SYSDATE,'DD-Mon-YYYY HH24:MI:SS');
4854: --System Date:
4855: l_tag :=pay_in_xml_utils.getTag('c_sys_date_in_hh_mm_ss',l_sys_date_time);
4856: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
4857: pay_in_utils.set_location(g_debug,l_procedure, 20);
4858:
4859: FOR c_rec IN c_distinct_org
4860: LOOP
4861:
4910: l_wages_tot := 0;
4911: l_con_tot := 0;
4912:
4913:
4914: pay_in_utils.set_location(g_debug,l_procedure, 30);
4915:
4916: FOR c_chd_asg IN c_asg_id(c_rec.org_id)
4917: LOOP
4918: -- l_asg_id := c_chd_asg.assignment_id;
4918: -- l_asg_id := c_chd_asg.assignment_id;
4919: l_insurance_no := c_chd_asg.insurance_no;
4920:
4921: -- check the assignment
4922: pay_in_utils.set_location(g_debug,l_procedure, 40);
4923: l_esi_coverage := NVL(p_esi_coverage,'-1');
4924: IF esi_employee_type(l_insurance_no,c_rec.org_id,p_contribution_period,p_sysdate,l_esi_coverage)
4925: THEN
4926: pay_in_utils.set_location(g_debug,l_procedure, 50);
4922: pay_in_utils.set_location(g_debug,l_procedure, 40);
4923: l_esi_coverage := NVL(p_esi_coverage,'-1');
4924: IF esi_employee_type(l_insurance_no,c_rec.org_id,p_contribution_period,p_sysdate,l_esi_coverage)
4925: THEN
4926: pay_in_utils.set_location(g_debug,l_procedure, 50);
4927:
4928: IF g_debug THEN
4929: pay_in_utils.trace('ESI Insurance Number',l_insurance_no);
4930: END IF;
4925: THEN
4926: pay_in_utils.set_location(g_debug,l_procedure, 50);
4927:
4928: IF g_debug THEN
4929: pay_in_utils.trace('ESI Insurance Number',l_insurance_no);
4930: END IF;
4931:
4932: l_esi_coverage := NULL;
4933: --Assignment Details
4938:
4939: FOR c_date IN c_max_date(c_rec.org_id,l_insurance_no)
4940: LOOP
4941: IF g_debug THEN
4942: pay_in_utils.trace('ESI Form payroll max date',c_date.maxdate);
4943: pay_in_utils.trace('ESI Exemption',p_esi_coverage);
4944: END IF;
4945:
4946: pay_in_utils.set_location(g_debug,l_procedure, 60);
4939: FOR c_date IN c_max_date(c_rec.org_id,l_insurance_no)
4940: LOOP
4941: IF g_debug THEN
4942: pay_in_utils.trace('ESI Form payroll max date',c_date.maxdate);
4943: pay_in_utils.trace('ESI Exemption',p_esi_coverage);
4944: END IF;
4945:
4946: pay_in_utils.set_location(g_debug,l_procedure, 60);
4947: FOR child_asg IN c_asg_details(l_insurance_no,c_rec.org_id,c_date.maxdate)
4942: pay_in_utils.trace('ESI Form payroll max date',c_date.maxdate);
4943: pay_in_utils.trace('ESI Exemption',p_esi_coverage);
4944: END IF;
4945:
4946: pay_in_utils.set_location(g_debug,l_procedure, 60);
4947: FOR child_asg IN c_asg_details(l_insurance_no,c_rec.org_id,c_date.maxdate)
4948: LOOP
4949: pay_in_utils.set_location(g_debug,l_procedure, 70);
4950: l_full_name := child_asg.full_name;
4945:
4946: pay_in_utils.set_location(g_debug,l_procedure, 60);
4947: FOR child_asg IN c_asg_details(l_insurance_no,c_rec.org_id,c_date.maxdate)
4948: LOOP
4949: pay_in_utils.set_location(g_debug,l_procedure, 70);
4950: l_full_name := child_asg.full_name;
4951: l_actual_salary := l_actual_salary + child_asg.actual_salary;
4952: l_employee_contr := l_employee_contr + child_asg.employee_contr;
4953: l_employer_contr := l_employer_contr + child_asg.employer_contr;
4956: FROM dual;
4957: EXIT;
4958: END LOOP;
4959: END LOOP;
4960: pay_in_utils.set_location(g_debug,l_procedure, 80);
4961: OPEN c_absence(l_insurance_no,c_rec.org_id);
4962: FETCH c_absence INTO l_absence;
4963: CLOSE c_absence;
4964:
4990: END IF;
4991:
4992: l_remarks_dummy := NULL;
4993:
4994: pay_in_utils.set_location(g_debug,l_procedure, 90);
4995:
4996: FOR c_rem IN c_remarks(l_insurance_no,c_rec.org_id)
4997: LOOP
4998: l_payroll_mon := TO_NUMBER(TO_CHAR(c_rem.mon,'MM'));
5074: dbms_lob.writeAppend(l_xml_data, length(l_tag), l_tag);
5075: --Challan Details
5076: l_count := 1;
5077: l_challan_tot := 0;
5078: pay_in_utils.set_location(g_debug,l_procedure, 100);
5079: FOR c_challan_rec IN c_challan(c_rec.org_id)
5080: LOOP
5081:
5082: OPEN c_branch_dtls(c_challan_rec.challan_bank_code, c_challan_rec.challan_branch_code) ;
5178: IF flag = FALSE THEN
5179: dbms_lob.Append(g_xml_data,l_xml_data);
5180: END IF;
5181: dbms_lob.close(l_xml_data);
5182: pay_in_utils.set_location(g_debug,l_procedure, 110);
5183: END LOOP;
5184: l_tag :='';
5185: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
5186: