[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
576:
577:
578: BEGIN
579: l_procedure := g_package ||'create_pt_form3_xml';
580: pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
581:
582: -- l_slab_2 := 30;
583: l_slab_3 := 60;
584: l_slab_4 := 120;
606: l_tag :=pay_in_xml_utils.getTag('c_sys_date_in_hh_mm_ss',l_sys_date_time);
607: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
608:
609: IF g_debug THEN
610: pay_in_utils.trace('Business Group id ',l_bg_id);
611: pay_in_utils.trace('System Date and Time ',l_sys_date_time);
612: END IF;
613:
614:
607: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
608:
609: IF g_debug THEN
610: pay_in_utils.trace('Business Group id ',l_bg_id);
611: pay_in_utils.trace('System Date and Time ',l_sys_date_time);
612: END IF;
613:
614:
615: pay_in_utils.set_location(g_debug,l_procedure, 20);
611: pay_in_utils.trace('System Date and Time ',l_sys_date_time);
612: END IF;
613:
614:
615: pay_in_utils.set_location(g_debug,l_procedure, 20);
616:
617: period_start_year := SUBSTR(p_year, 1, 4);
618: IF p_frequency = 'A' THEN
619: period_start := TO_DATE('01-03-'||period_start_year, 'DD-MM-YYYY');
628: period_start := ADD_MONTHS(TO_DATE('01-03-'|| period_start_year, 'DD-MM-YYYY'), MOD(TO_NUMBER(p_period),12));
629: period_end := ADD_MONTHS(period_start, 1) -1;
630: END IF;
631:
632: pay_in_utils.set_location(g_debug,l_procedure, 30);
633:
634: IF g_debug THEN
635: pay_in_utils.trace('period_start_year ',period_start_year);
636: pay_in_utils.trace('period_start ',period_start);
631:
632: pay_in_utils.set_location(g_debug,l_procedure, 30);
633:
634: IF g_debug THEN
635: pay_in_utils.trace('period_start_year ',period_start_year);
636: pay_in_utils.trace('period_start ',period_start);
637: pay_in_utils.trace('period_end ',period_end);
638: END IF;
639:
632: pay_in_utils.set_location(g_debug,l_procedure, 30);
633:
634: IF g_debug THEN
635: pay_in_utils.trace('period_start_year ',period_start_year);
636: pay_in_utils.trace('period_start ',period_start);
637: pay_in_utils.trace('period_end ',period_end);
638: END IF;
639:
640:
633:
634: IF g_debug THEN
635: pay_in_utils.trace('period_start_year ',period_start_year);
636: pay_in_utils.trace('period_start ',period_start);
637: pay_in_utils.trace('period_end ',period_end);
638: END IF;
639:
640:
641: FOR c_rec IN c_distinct_org (period_start
641: FOR c_rec IN c_distinct_org (period_start
642: ,period_end)
643: LOOP
644: l_tag :='
645: pay_in_utils.set_location(g_debug,l_procedure, 40);
646: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
647:
648: IF g_debug THEN
649: pay_in_utils.trace('c_rec.org_id ',c_rec.org_id);
645: pay_in_utils.set_location(g_debug,l_procedure, 40);
646: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
647:
648: IF g_debug THEN
649: pay_in_utils.trace('c_rec.org_id ',c_rec.org_id);
650: END IF;
651:
652: fnd_file.put_line(fnd_file.log,'c_rec.org_id '|| c_rec.org_id);
653:
657: FETCH c_max_pa_action_id INTO l_payroll_act_id;
658: CLOSE c_max_pa_action_id ;
659:
660: IF g_debug THEN
661: pay_in_utils.trace('Payroll Action id ',l_payroll_act_id);
662: END IF;
663:
664: pay_in_utils.set_location(g_debug,l_procedure, 50);
665:
660: IF g_debug THEN
661: pay_in_utils.trace('Payroll Action id ',l_payroll_act_id);
662: END IF;
663:
664: pay_in_utils.set_location(g_debug,l_procedure, 50);
665:
666: OPEN c_org_details(l_payroll_act_id
667: ,c_rec.org_id);
668: FETCH c_org_details INTO l_registered_no
672: ,l_rep_desg
673: ,l_org_name
674: ,l_bsrtc_no;
675: CLOSE c_org_details;
676: pay_in_utils.set_location(g_debug,l_procedure, 60);
677: -- Registered No
678: l_tag :=pay_in_xml_utils.getTag('c_registered_no', l_registered_no);
679: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
680: --Organization Name and Address
697: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
698: --Date
699: l_tag :=pay_in_xml_utils.getTag('c_date', substr(l_sys_date_time, 1, 11));
700: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
701: pay_in_utils.set_location(g_debug,l_procedure, 70);
702:
703: IF g_debug THEN
704: pay_in_utils.trace('Registered No ',l_registered_no);
705: pay_in_utils.trace('Registered Name ',l_registered_name);
700: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
701: pay_in_utils.set_location(g_debug,l_procedure, 70);
702:
703: IF g_debug THEN
704: pay_in_utils.trace('Registered No ',l_registered_no);
705: pay_in_utils.trace('Registered Name ',l_registered_name);
706: pay_in_utils.trace('Registered Address ',l_reg_address);
707: pay_in_utils.trace('BSRTC no ',l_bsrtc_no);
708: pay_in_utils.trace('Period Start ',period_start);
701: pay_in_utils.set_location(g_debug,l_procedure, 70);
702:
703: IF g_debug THEN
704: pay_in_utils.trace('Registered No ',l_registered_no);
705: pay_in_utils.trace('Registered Name ',l_registered_name);
706: pay_in_utils.trace('Registered Address ',l_reg_address);
707: pay_in_utils.trace('BSRTC no ',l_bsrtc_no);
708: pay_in_utils.trace('Period Start ',period_start);
709: pay_in_utils.trace('Period End ',period_end);
702:
703: IF g_debug THEN
704: pay_in_utils.trace('Registered No ',l_registered_no);
705: pay_in_utils.trace('Registered Name ',l_registered_name);
706: pay_in_utils.trace('Registered Address ',l_reg_address);
707: pay_in_utils.trace('BSRTC no ',l_bsrtc_no);
708: pay_in_utils.trace('Period Start ',period_start);
709: pay_in_utils.trace('Period End ',period_end);
710: END IF;
703: IF g_debug THEN
704: pay_in_utils.trace('Registered No ',l_registered_no);
705: pay_in_utils.trace('Registered Name ',l_registered_name);
706: pay_in_utils.trace('Registered Address ',l_reg_address);
707: pay_in_utils.trace('BSRTC no ',l_bsrtc_no);
708: pay_in_utils.trace('Period Start ',period_start);
709: pay_in_utils.trace('Period End ',period_end);
710: END IF;
711:
704: pay_in_utils.trace('Registered No ',l_registered_no);
705: pay_in_utils.trace('Registered Name ',l_registered_name);
706: pay_in_utils.trace('Registered Address ',l_reg_address);
707: pay_in_utils.trace('BSRTC no ',l_bsrtc_no);
708: pay_in_utils.trace('Period Start ',period_start);
709: pay_in_utils.trace('Period End ',period_end);
710: END IF;
711:
712: l_count_0 := 0;
705: pay_in_utils.trace('Registered Name ',l_registered_name);
706: pay_in_utils.trace('Registered Address ',l_reg_address);
707: pay_in_utils.trace('BSRTC no ',l_bsrtc_no);
708: pay_in_utils.trace('Period Start ',period_start);
709: pay_in_utils.trace('Period End ',period_end);
710: END IF;
711:
712: l_count_0 := 0;
713: -- l_count_30 := 0;
736: l_slab_3 := 0;
737: l_slab_4 := 0;
738: END IF;
739:
740: pay_in_utils.set_location(g_debug,l_procedure, 80);
741:
742: -- Getting the records for each month in the report period
743: WHILE l_date < period_end
744: LOOP
758: END IF;
759:
760:
761: IF g_debug THEN
762: pay_in_utils.trace('l_date ',l_date);
763: pay_in_utils.trace('l_period_month ',l_period_month);
764: END IF;
765: pay_in_utils.set_location(g_debug,l_procedure, 90);
766:
759:
760:
761: IF g_debug THEN
762: pay_in_utils.trace('l_date ',l_date);
763: pay_in_utils.trace('l_period_month ',l_period_month);
764: END IF;
765: pay_in_utils.set_location(g_debug,l_procedure, 90);
766:
767: FOR rec_sal in cur_sal_range(period_start)
761: IF g_debug THEN
762: pay_in_utils.trace('l_date ',l_date);
763: pay_in_utils.trace('l_period_month ',l_period_month);
764: END IF;
765: pay_in_utils.set_location(g_debug,l_procedure, 90);
766:
767: FOR rec_sal in cur_sal_range(period_start)
768: LOOP
769: -- Getting the no. of employees in each slab
809: l_slab := 6;
810: END IF;
811:
812: IF g_debug THEN
813: pay_in_utils.trace('PT slab rate ',rec_count.rate);
814: pay_in_utils.trace('Count of Records ',rec_count.count);
815: END IF;
816:
817: pay_in_utils.set_location(g_debug,l_procedure, 100);
810: END IF;
811:
812: IF g_debug THEN
813: pay_in_utils.trace('PT slab rate ',rec_count.rate);
814: pay_in_utils.trace('Count of Records ',rec_count.count);
815: END IF;
816:
817: pay_in_utils.set_location(g_debug,l_procedure, 100);
818:
813: pay_in_utils.trace('PT slab rate ',rec_count.rate);
814: pay_in_utils.trace('Count of Records ',rec_count.count);
815: END IF;
816:
817: pay_in_utils.set_location(g_debug,l_procedure, 100);
818:
819: IF l_slab <> 6 THEN
820: -- Writing to the report if the slab is not the highest slab
821: -- Highest slab is written to the report once the loop is over since a sum is to be taken for 200 and 300
828: END LOOP;
829: END LOOP;
830:
831: IF g_debug THEN
832: pay_in_utils.trace('l_slab_month_tag ',l_slab_month_tag);
833: pay_in_utils.trace('l_count_0 ',l_count_0);
834: -- pay_in_utils.trace('l_count_30 ',l_count_30);
835: pay_in_utils.trace('l_count_60 ',l_count_60);
836: pay_in_utils.trace('l_count_120 ',l_count_120);
829: END LOOP;
830:
831: IF g_debug THEN
832: pay_in_utils.trace('l_slab_month_tag ',l_slab_month_tag);
833: pay_in_utils.trace('l_count_0 ',l_count_0);
834: -- pay_in_utils.trace('l_count_30 ',l_count_30);
835: pay_in_utils.trace('l_count_60 ',l_count_60);
836: pay_in_utils.trace('l_count_120 ',l_count_120);
837: pay_in_utils.trace('l_count_175 ',l_count_175);
830:
831: IF g_debug THEN
832: pay_in_utils.trace('l_slab_month_tag ',l_slab_month_tag);
833: pay_in_utils.trace('l_count_0 ',l_count_0);
834: -- pay_in_utils.trace('l_count_30 ',l_count_30);
835: pay_in_utils.trace('l_count_60 ',l_count_60);
836: pay_in_utils.trace('l_count_120 ',l_count_120);
837: pay_in_utils.trace('l_count_175 ',l_count_175);
838: pay_in_utils.trace('l_count_200+l_count_300 ',(l_count_200 + l_count_300));
831: IF g_debug THEN
832: pay_in_utils.trace('l_slab_month_tag ',l_slab_month_tag);
833: pay_in_utils.trace('l_count_0 ',l_count_0);
834: -- pay_in_utils.trace('l_count_30 ',l_count_30);
835: pay_in_utils.trace('l_count_60 ',l_count_60);
836: pay_in_utils.trace('l_count_120 ',l_count_120);
837: pay_in_utils.trace('l_count_175 ',l_count_175);
838: pay_in_utils.trace('l_count_200+l_count_300 ',(l_count_200 + l_count_300));
839:
832: pay_in_utils.trace('l_slab_month_tag ',l_slab_month_tag);
833: pay_in_utils.trace('l_count_0 ',l_count_0);
834: -- pay_in_utils.trace('l_count_30 ',l_count_30);
835: pay_in_utils.trace('l_count_60 ',l_count_60);
836: pay_in_utils.trace('l_count_120 ',l_count_120);
837: pay_in_utils.trace('l_count_175 ',l_count_175);
838: pay_in_utils.trace('l_count_200+l_count_300 ',(l_count_200 + l_count_300));
839:
840: END IF;
833: pay_in_utils.trace('l_count_0 ',l_count_0);
834: -- pay_in_utils.trace('l_count_30 ',l_count_30);
835: pay_in_utils.trace('l_count_60 ',l_count_60);
836: pay_in_utils.trace('l_count_120 ',l_count_120);
837: pay_in_utils.trace('l_count_175 ',l_count_175);
838: pay_in_utils.trace('l_count_200+l_count_300 ',(l_count_200 + l_count_300));
839:
840: END IF;
841:
834: -- pay_in_utils.trace('l_count_30 ',l_count_30);
835: pay_in_utils.trace('l_count_60 ',l_count_60);
836: pay_in_utils.trace('l_count_120 ',l_count_120);
837: pay_in_utils.trace('l_count_175 ',l_count_175);
838: pay_in_utils.trace('l_count_200+l_count_300 ',(l_count_200 + l_count_300));
839:
840: END IF;
841:
842:
839:
840: END IF;
841:
842:
843: pay_in_utils.set_location(g_debug,l_procedure, 110);
844:
845: l_slab_month_count := l_count_300_temp + l_count_200_temp;
846:
847: IF l_slab_month_count <> 0 THEN
853:
854: l_date := ADD_MONTHS(l_date, 1);
855:
856: END LOOP;
857: pay_in_utils.set_location(g_debug,l_procedure, 120);
858:
859: -- Writing the total employees in a slab
860: l_tag := pay_in_xml_utils.getTag('c_emp_slab_1', l_count_0);
861: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
928: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
929: 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)));
930: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
931:
932: pay_in_utils.set_location(g_debug,l_procedure, 130);
933:
934: -- Getting the actual PT paid from the challans data
935: OPEN c_challan(c_rec.org_id
936: ,period_start
940: ,l_excess_tax;
941: CLOSE c_challan;
942:
943: IF g_debug THEN
944: pay_in_utils.trace('Tax Amount ',l_tax_amount);
945: pay_in_utils.trace('Interest Amount ',l_interest_amount);
946: pay_in_utils.trace('Excess Tax ',l_excess_tax);
947: END IF;
948:
941: CLOSE c_challan;
942:
943: IF g_debug THEN
944: pay_in_utils.trace('Tax Amount ',l_tax_amount);
945: pay_in_utils.trace('Interest Amount ',l_interest_amount);
946: pay_in_utils.trace('Excess Tax ',l_excess_tax);
947: END IF;
948:
949:
942:
943: IF g_debug THEN
944: pay_in_utils.trace('Tax Amount ',l_tax_amount);
945: pay_in_utils.trace('Interest Amount ',l_interest_amount);
946: pay_in_utils.trace('Excess Tax ',l_excess_tax);
947: END IF;
948:
949:
950: 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)));
960:
961: 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)));
962: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
963:
964: l_tag := pay_in_xml_utils.getTag('c_total_amount_words', initcap(pay_in_utils.number_to_words(l_net_amount)));
965: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
966:
967: l_tag :=pay_in_xml_utils.getTag('c_year', period_start_year);
968: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
970: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
971:
972: l_tag :='';
973: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
974: pay_in_utils.set_location(g_debug,l_procedure, 140);
975: fnd_file.put_line(fnd_file.log,'Org Over');
976: END LOOP;
977: l_tag :='';
978: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
976: END LOOP;
977: l_tag :='';
978: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
979: fnd_file.put_line(fnd_file.log,'XML Created.');
980: pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 150);
981:
982: END create_pt_form3_xml;
983:
984:
1006: l_tag VARCHAR2(10000);
1007: l_procedure VARCHAR2(100);
1008: BEGIN
1009: l_procedure := g_package ||'write_tag';
1010: pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1011:
1012: l_tag := pay_in_xml_utils.getTag( p_tag_name => p_tag_name
1013: , p_tag_value => p_tag_value
1014: );
1013: , p_tag_value => p_tag_value
1014: );
1015:
1016: dbms_lob.writeAppend(g_xml_data,length(l_tag),l_tag);
1017: pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 20);
1018: END write_tag;
1019:
1020: --------------------------------------------------------------------------
1021: -- --
1064: BEGIN
1065:
1066: g_debug := hr_utility.debug_enabled;
1067: l_procedure := g_package ||'get_location_details';
1068: pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1069:
1070: OPEN csr_add;
1071: 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;
1072: CLOSE csr_add;
1091: l_details := l_fax;
1092: END IF;
1093:
1094: IF g_debug THEN
1095: pay_in_utils.trace('Location id ',p_location_id);
1096: pay_in_utils.trace('Field ',p_field);
1097: pay_in_utils.trace('Address Details ',l_details);
1098: END IF;
1099:
1092: END IF;
1093:
1094: IF g_debug THEN
1095: pay_in_utils.trace('Location id ',p_location_id);
1096: pay_in_utils.trace('Field ',p_field);
1097: pay_in_utils.trace('Address Details ',l_details);
1098: END IF;
1099:
1100: pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 20);
1093:
1094: IF g_debug THEN
1095: pay_in_utils.trace('Location id ',p_location_id);
1096: pay_in_utils.trace('Field ',p_field);
1097: pay_in_utils.trace('Address Details ',l_details);
1098: END IF;
1099:
1100: pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 20);
1101:
1096: pay_in_utils.trace('Field ',p_field);
1097: pay_in_utils.trace('Address Details ',l_details);
1098: END IF;
1099:
1100: pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 20);
1101:
1102: RETURN l_details;
1103:
1104: END get_location_details;
1267: BEGIN
1268:
1269: g_debug := hr_utility.debug_enabled;
1270: l_procedure := g_package ||'create_form27A_xml';
1271: pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1272:
1273: IF g_debug THEN
1274: pay_in_utils.trace('GRE id ',p_gre_org_id);
1275: pay_in_utils.trace('Assessment year ',p_assess_year);
1270: l_procedure := g_package ||'create_form27A_xml';
1271: pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1272:
1273: IF g_debug THEN
1274: pay_in_utils.trace('GRE id ',p_gre_org_id);
1275: pay_in_utils.trace('Assessment year ',p_assess_year);
1276: END IF;
1277:
1278:
1271: pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1272:
1273: IF g_debug THEN
1274: pay_in_utils.trace('GRE id ',p_gre_org_id);
1275: pay_in_utils.trace('Assessment year ',p_assess_year);
1276: END IF;
1277:
1278:
1279: pay_in_xml_utils.gXMLTable.DELETE;
1288: l_tag := '
1289: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
1290: fnd_file.put_line(fnd_file.log,'Started...');
1291:
1292: pay_in_utils.set_location(g_debug,l_procedure, 20);
1293: --
1294: fnd_file.put_line(fnd_file.log,'Creating XML for Employer Details.');
1295: l_sys_date_time:=TO_CHAR(SYSDATE,'DD-Mon-YYYY HH24:MI:SS');
1296: l_sys_date := TO_CHAR(SYSDATE,'DD-Mon-YYYY');
1298: --System Date
1299: l_tag :=pay_in_xml_utils.getTag('c_current_date_in_hh_mm_ss',l_sys_date_time);
1300: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
1301:
1302: pay_in_utils.set_location(g_debug,l_procedure, 30);
1303:
1304: OPEN csr_org_max_action_context_id;
1305: FETCH csr_org_max_action_context_id INTO l_org_max_action_id;
1306: CLOSE csr_org_max_action_context_id;
1316: OPEN csr_tot_emp_cnt;
1317: FETCH csr_tot_emp_cnt INTO l_tot_emp;
1318: CLOSE csr_tot_emp_cnt;
1319:
1320: pay_in_utils.set_location(g_debug,l_procedure, 40);
1321:
1322: FOR i IN csr_get_max_cont_id
1323: LOOP
1324: pay_in_utils.set_location(g_debug,l_procedure, 50);
1320: pay_in_utils.set_location(g_debug,l_procedure, 40);
1321:
1322: FOR i IN csr_get_max_cont_id
1323: LOOP
1324: pay_in_utils.set_location(g_debug,l_procedure, 50);
1325: OPEN csr_tax_details('Income Tax Deduction',i.action_cont_id,i.sour_id);
1326: FETCH csr_tax_details INTO l_itd;
1327: CLOSE csr_tax_details;
1328:
1377: l_tag := '';
1378: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
1379:
1380: fnd_file.put_line(fnd_file.log,'XML Created.');
1381: pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 60);
1382:
1383:
1384: END create_form27A_xml;
1385:
1537:
1538: --hr_utility.trace_on(null,'ORACLE1234');
1539: g_debug := hr_utility.debug_enabled;
1540: l_procedure := g_package ||'create_ecr_xml';
1541: pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1542:
1543: IF g_debug THEN
1544: pay_in_utils.trace('PF Organization id ',p_pf_org_id);
1545: pay_in_utils.trace('Contribution Period ',p_contribution_period);
1540: l_procedure := g_package ||'create_ecr_xml';
1541: pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1542:
1543: IF g_debug THEN
1544: pay_in_utils.trace('PF Organization id ',p_pf_org_id);
1545: pay_in_utils.trace('Contribution Period ',p_contribution_period);
1546: pay_in_utils.trace('Month ',p_month);
1547: END IF;
1548:
1541: pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1542:
1543: IF g_debug THEN
1544: pay_in_utils.trace('PF Organization id ',p_pf_org_id);
1545: pay_in_utils.trace('Contribution Period ',p_contribution_period);
1546: pay_in_utils.trace('Month ',p_month);
1547: END IF;
1548:
1549: --
1542:
1543: IF g_debug THEN
1544: pay_in_utils.trace('PF Organization id ',p_pf_org_id);
1545: pay_in_utils.trace('Contribution Period ',p_contribution_period);
1546: pay_in_utils.trace('Month ',p_month);
1547: END IF;
1548:
1549: --
1550: fnd_file.put_line(fnd_file.log,'Creating the ECR XML...');
1559: END IF;
1560:
1561:
1562: IF g_debug THEN
1563: pay_in_utils.trace('l_month : ',l_mon);
1564: END IF;
1565:
1566: SELECT TO_NUMBER(GLOBAL_VALUE) INTO l_eps_age
1567: FROM ff_globals_f where global_name = 'IN_EPS_AGE';
1568:
1569:
1570:
1571: IF g_debug THEN
1572: pay_in_utils.trace('l_eps_age : ',l_eps_age);
1573: END IF;
1574:
1575: FOR c IN c_asg_count(p_pf_org_id --PF Org ID
1576: ,p_contribution_period --Contribution Period
1576: ,p_contribution_period --Contribution Period
1577: ,l_mon)
1578:
1579: LOOP
1580: pay_in_utils.set_location(g_debug,l_procedure, 20);
1581: IF g_debug THEN
1582: pay_in_utils.trace('asg_cnt : ',c.asg_cnt);
1583: pay_in_utils.trace('member_id : ',c.member_id);
1584: END IF;
1578:
1579: LOOP
1580: pay_in_utils.set_location(g_debug,l_procedure, 20);
1581: IF g_debug THEN
1582: pay_in_utils.trace('asg_cnt : ',c.asg_cnt);
1583: pay_in_utils.trace('member_id : ',c.member_id);
1584: END IF;
1585:
1586: IF(c.asg_cnt>1) THEN
1579: LOOP
1580: pay_in_utils.set_location(g_debug,l_procedure, 20);
1581: IF g_debug THEN
1582: pay_in_utils.trace('asg_cnt : ',c.asg_cnt);
1583: pay_in_utils.trace('member_id : ',c.member_id);
1584: END IF;
1585:
1586: IF(c.asg_cnt>1) THEN
1587: fnd_message.set_name('PER', 'PER_IN_DUPLICATE_PF_NUMBER');
1595: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
1596: l_tag :='
1597: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
1598:
1599: pay_in_utils.set_location(g_debug,l_procedure, 30);
1600:
1601: FOR c_rec IN c_asg_id(p_pf_org_id --PF Org ID
1602: ,p_contribution_period --Contribution Period
1603: ,l_mon) --PF Month
1601: FOR c_rec IN c_asg_id(p_pf_org_id --PF Org ID
1602: ,p_contribution_period --Contribution Period
1603: ,l_mon) --PF Month
1604: LOOP
1605: pay_in_utils.set_location(g_debug,l_procedure, 40);
1606:
1607: l_tag := '
1608: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
1609:
1626: ,l_memb_id
1627: ,l_memb_name)
1628: LOOP
1629:
1630: pay_in_utils.set_location(g_debug,l_procedure, 50);
1631:
1632: l_member_name_1 := NULL; /* initialize to null */
1633: l_epf_wages_1 := NULL;
1634: l_eps_wages_1 := NULL;
1664: /*assign values to local variabel */
1665:
1666: END LOOP;
1667:
1668: pay_in_utils.set_location(g_debug,l_procedure, 60);
1669:
1670: IF length(REPLACE((TRANSLATE(l_member_name_1,
1671: 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789. ',' ')),' ','')) > 0
1672: THEN
1765:
1766: l_tag :=pay_in_xml_utils.getTag('c_1_eps_arr_wages','0');
1767: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
1768:
1769: pay_in_utils.set_location(g_debug,l_procedure, 70);
1770:
1771: IF(to_date(l_hire_date_1,'DD/MM/YYYY')
1772: BETWEEN TRUNC(to_date(l_payroll_date_1,'DD/MM/YYYY'),'MM')
1773: AND last_day(to_date(l_payroll_date_1,'DD/MM/YYYY'))) THEN
1797:
1798: l_tag :=pay_in_xml_utils.getTag('c_1_doj_eps',l_hire_date_1);
1799: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
1800:
1801: pay_in_utils.set_location(g_debug,l_procedure, 80);
1802:
1803: END IF;
1804:
1805: IF(to_date(l_term_date_1,'DD/MM/YYYY')
1811:
1812: l_tag :=pay_in_xml_utils.getTag('c_1_leav_rsn',l_term_reason_1);
1813: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
1814:
1815: pay_in_utils.set_location(g_debug,l_procedure, 90);
1816:
1817: END IF;
1818:
1819: IF add_months(to_date(l_date_of_birth_1,'DD/MM/YYYY'),l_eps_age*12 ) BETWEEN
1828: l_leave_2:=to_char(to_date(l_leave,'DD/MM/RRRR'),'DD/MM/RRRR');
1829: l_tag :=pay_in_xml_utils.getTag('c_1_doe_eps',l_leave_2);
1830: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
1831:
1832: pay_in_utils.set_location(g_debug,l_procedure, 100);
1833:
1834: ELSE
1835: l_leave:=add_months(to_date(l_date_of_birth_1,'DD/MM/YYYY'),l_eps_age*12 );
1836: l_leave_2:=to_char(to_date(l_leave,'DD/MM/RRRR'),'DD/MM/RRRR');
1836: l_leave_2:=to_char(to_date(l_leave,'DD/MM/RRRR'),'DD/MM/RRRR');
1837: l_tag :=pay_in_xml_utils.getTag('c_1_doe_eps',l_leave_2);
1838: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
1839:
1840: pay_in_utils.set_location(g_debug,l_procedure, 110);
1841: END IF;
1842:
1843: ELSE
1844: IF to_date( l_term_date_1,'DD/MM/YYYY')
1849: THEN
1850: l_tag :=pay_in_xml_utils.getTag('c_1_doe_eps',null);
1851: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
1852:
1853: pay_in_utils.set_location(g_debug,l_procedure, 120);
1854: ELSE
1855: l_tag :=pay_in_xml_utils.getTag('c_1_doe_eps', l_term_date_1);
1856: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
1857:
1854: ELSE
1855: l_tag :=pay_in_xml_utils.getTag('c_1_doe_eps', l_term_date_1);
1856: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
1857:
1858: pay_in_utils.set_location(g_debug,l_procedure, 130);
1859: END IF;
1860: END IF;
1861: END IF;
1862:
1863: l_tag := '';
1864: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
1865: END LOOP;
1866:
1867: pay_in_utils.set_location(g_debug,l_procedure, 140);
1868: l_tag :='';
1869: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
1870: pay_in_utils.set_location(g_debug,l_procedure, 150);
1871: fnd_file.put_line(fnd_file.log,'XML Created.');
1866:
1867: pay_in_utils.set_location(g_debug,l_procedure, 140);
1868: l_tag :='';
1869: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
1870: pay_in_utils.set_location(g_debug,l_procedure, 150);
1871: fnd_file.put_line(fnd_file.log,'XML Created.');
1872: fnd_file.put_line(fnd_file.log,dbms_lob.substr(g_xml_data));
1873:
1874: EXCEPTION
1874: EXCEPTION
1875:
1876: WHEN OTHERS THEN
1877: fnd_file.put_line(fnd_file.log,'Exception');
1878: pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 160);
1879: RAISE;
1880: END create_ecr_xml;
1881: --------------------------------------------------------------------------
1882: -- --
1931: BEGIN
1932: --
1933: g_debug := hr_utility.debug_enabled;
1934: l_procedure := g_package ||'init_code';
1935: pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1936:
1937: IF p_form_type <> 'ESI6' and p_form_type <> 'PT3' AND p_form_type <> 'FORM27A' THEN
1938: l_effective_start_date:=to_date(('01-03-'||substr(p_contribution_period,1,4)),'DD-MM-YYYY');
1939: l_effective_end_date:= to_date(('01-03-'||substr(p_contribution_period,6)),'DD-MM-YYYY')-1;
1941: l_contribution_period := hr_general.decode_lookup('IN_ESI_CONTRIB_PERIOD',p_contribution_period);
1942: END IF;
1943:
1944: IF g_debug THEN
1945: pay_in_utils.trace('PF Organization id ',p_pf_org_id);
1946: pay_in_utils.trace('PF Number ',p_pf_number);
1947: pay_in_utils.trace('Employee Type ',p_employee_type);
1948: pay_in_utils.trace('Contribution Period ',p_contribution_period);
1949: pay_in_utils.trace('Effective Start Date',l_effective_start_date);
1942: END IF;
1943:
1944: IF g_debug THEN
1945: pay_in_utils.trace('PF Organization id ',p_pf_org_id);
1946: pay_in_utils.trace('PF Number ',p_pf_number);
1947: pay_in_utils.trace('Employee Type ',p_employee_type);
1948: pay_in_utils.trace('Contribution Period ',p_contribution_period);
1949: pay_in_utils.trace('Effective Start Date',l_effective_start_date);
1950: pay_in_utils.trace('Effective End Date ',l_effective_end_date);
1943:
1944: IF g_debug THEN
1945: pay_in_utils.trace('PF Organization id ',p_pf_org_id);
1946: pay_in_utils.trace('PF Number ',p_pf_number);
1947: pay_in_utils.trace('Employee Type ',p_employee_type);
1948: pay_in_utils.trace('Contribution Period ',p_contribution_period);
1949: pay_in_utils.trace('Effective Start Date',l_effective_start_date);
1950: pay_in_utils.trace('Effective End Date ',l_effective_end_date);
1951: pay_in_utils.trace('Pension Number ',p_pension_number);
1944: IF g_debug THEN
1945: pay_in_utils.trace('PF Organization id ',p_pf_org_id);
1946: pay_in_utils.trace('PF Number ',p_pf_number);
1947: pay_in_utils.trace('Employee Type ',p_employee_type);
1948: pay_in_utils.trace('Contribution Period ',p_contribution_period);
1949: pay_in_utils.trace('Effective Start Date',l_effective_start_date);
1950: pay_in_utils.trace('Effective End Date ',l_effective_end_date);
1951: pay_in_utils.trace('Pension Number ',p_pension_number);
1952: pay_in_utils.trace('ESI Org ID ',p_esi_org_id);
1945: pay_in_utils.trace('PF Organization id ',p_pf_org_id);
1946: pay_in_utils.trace('PF Number ',p_pf_number);
1947: pay_in_utils.trace('Employee Type ',p_employee_type);
1948: pay_in_utils.trace('Contribution Period ',p_contribution_period);
1949: pay_in_utils.trace('Effective Start Date',l_effective_start_date);
1950: pay_in_utils.trace('Effective End Date ',l_effective_end_date);
1951: pay_in_utils.trace('Pension Number ',p_pension_number);
1952: pay_in_utils.trace('ESI Org ID ',p_esi_org_id);
1953: pay_in_utils.trace('ESI Coverage ',p_esi_coverage);
1946: pay_in_utils.trace('PF Number ',p_pf_number);
1947: pay_in_utils.trace('Employee Type ',p_employee_type);
1948: pay_in_utils.trace('Contribution Period ',p_contribution_period);
1949: pay_in_utils.trace('Effective Start Date',l_effective_start_date);
1950: pay_in_utils.trace('Effective End Date ',l_effective_end_date);
1951: pay_in_utils.trace('Pension Number ',p_pension_number);
1952: pay_in_utils.trace('ESI Org ID ',p_esi_org_id);
1953: pay_in_utils.trace('ESI Coverage ',p_esi_coverage);
1954: pay_in_utils.trace('Contribution Period ',l_contribution_period);
1947: pay_in_utils.trace('Employee Type ',p_employee_type);
1948: pay_in_utils.trace('Contribution Period ',p_contribution_period);
1949: pay_in_utils.trace('Effective Start Date',l_effective_start_date);
1950: pay_in_utils.trace('Effective End Date ',l_effective_end_date);
1951: pay_in_utils.trace('Pension Number ',p_pension_number);
1952: pay_in_utils.trace('ESI Org ID ',p_esi_org_id);
1953: pay_in_utils.trace('ESI Coverage ',p_esi_coverage);
1954: pay_in_utils.trace('Contribution Period ',l_contribution_period);
1955: pay_in_utils.trace('Session Date ',p_sysdate);
1948: pay_in_utils.trace('Contribution Period ',p_contribution_period);
1949: pay_in_utils.trace('Effective Start Date',l_effective_start_date);
1950: pay_in_utils.trace('Effective End Date ',l_effective_end_date);
1951: pay_in_utils.trace('Pension Number ',p_pension_number);
1952: pay_in_utils.trace('ESI Org ID ',p_esi_org_id);
1953: pay_in_utils.trace('ESI Coverage ',p_esi_coverage);
1954: pay_in_utils.trace('Contribution Period ',l_contribution_period);
1955: pay_in_utils.trace('Session Date ',p_sysdate);
1956: pay_in_utils.trace('PT Org ID ',p_pt_org_id);
1949: pay_in_utils.trace('Effective Start Date',l_effective_start_date);
1950: pay_in_utils.trace('Effective End Date ',l_effective_end_date);
1951: pay_in_utils.trace('Pension Number ',p_pension_number);
1952: pay_in_utils.trace('ESI Org ID ',p_esi_org_id);
1953: pay_in_utils.trace('ESI Coverage ',p_esi_coverage);
1954: pay_in_utils.trace('Contribution Period ',l_contribution_period);
1955: pay_in_utils.trace('Session Date ',p_sysdate);
1956: pay_in_utils.trace('PT Org ID ',p_pt_org_id);
1957: pay_in_utils.trace('PT Frequency ',p_frequency);
1950: pay_in_utils.trace('Effective End Date ',l_effective_end_date);
1951: pay_in_utils.trace('Pension Number ',p_pension_number);
1952: pay_in_utils.trace('ESI Org ID ',p_esi_org_id);
1953: pay_in_utils.trace('ESI Coverage ',p_esi_coverage);
1954: pay_in_utils.trace('Contribution Period ',l_contribution_period);
1955: pay_in_utils.trace('Session Date ',p_sysdate);
1956: pay_in_utils.trace('PT Org ID ',p_pt_org_id);
1957: pay_in_utils.trace('PT Frequency ',p_frequency);
1958: pay_in_utils.trace('PT Year ',p_year);
1951: pay_in_utils.trace('Pension Number ',p_pension_number);
1952: pay_in_utils.trace('ESI Org ID ',p_esi_org_id);
1953: pay_in_utils.trace('ESI Coverage ',p_esi_coverage);
1954: pay_in_utils.trace('Contribution Period ',l_contribution_period);
1955: pay_in_utils.trace('Session Date ',p_sysdate);
1956: pay_in_utils.trace('PT Org ID ',p_pt_org_id);
1957: pay_in_utils.trace('PT Frequency ',p_frequency);
1958: pay_in_utils.trace('PT Year ',p_year);
1959: pay_in_utils.trace('Period ',p_period);
1952: pay_in_utils.trace('ESI Org ID ',p_esi_org_id);
1953: pay_in_utils.trace('ESI Coverage ',p_esi_coverage);
1954: pay_in_utils.trace('Contribution Period ',l_contribution_period);
1955: pay_in_utils.trace('Session Date ',p_sysdate);
1956: pay_in_utils.trace('PT Org ID ',p_pt_org_id);
1957: pay_in_utils.trace('PT Frequency ',p_frequency);
1958: pay_in_utils.trace('PT Year ',p_year);
1959: pay_in_utils.trace('Period ',p_period);
1960: END IF;
1953: pay_in_utils.trace('ESI Coverage ',p_esi_coverage);
1954: pay_in_utils.trace('Contribution Period ',l_contribution_period);
1955: pay_in_utils.trace('Session Date ',p_sysdate);
1956: pay_in_utils.trace('PT Org ID ',p_pt_org_id);
1957: pay_in_utils.trace('PT Frequency ',p_frequency);
1958: pay_in_utils.trace('PT Year ',p_year);
1959: pay_in_utils.trace('Period ',p_period);
1960: END IF;
1961:
1954: pay_in_utils.trace('Contribution Period ',l_contribution_period);
1955: pay_in_utils.trace('Session Date ',p_sysdate);
1956: pay_in_utils.trace('PT Org ID ',p_pt_org_id);
1957: pay_in_utils.trace('PT Frequency ',p_frequency);
1958: pay_in_utils.trace('PT Year ',p_year);
1959: pay_in_utils.trace('Period ',p_period);
1960: END IF;
1961:
1962:
1955: pay_in_utils.trace('Session Date ',p_sysdate);
1956: pay_in_utils.trace('PT Org ID ',p_pt_org_id);
1957: pay_in_utils.trace('PT Frequency ',p_frequency);
1958: pay_in_utils.trace('PT Year ',p_year);
1959: pay_in_utils.trace('Period ',p_period);
1960: END IF;
1961:
1962:
1963:
1963:
1964:
1965: IF p_form_type = 'FORM3A' THEN
1966: --
1967: pay_in_utils.set_location(g_debug,l_procedure, 20);
1968: create_form3a_xml(p_pf_org_id => p_pf_org_id
1969: ,p_pf_number => p_pf_number
1970: ,p_employee_type=> p_employee_type
1971: ,p_contribution_period=> p_contribution_period
1973: ,p_effective_end_date => l_effective_end_date
1974: );
1975: ELSIF p_form_type = 'FORM6A' THEN
1976: --
1977: pay_in_utils.set_location(g_debug,l_procedure, 30);
1978: create_form6a_xml(p_pf_org_id => p_pf_org_id
1979: ,p_effective_start_date => l_effective_start_date
1980: ,p_effective_end_date => l_effective_end_date
1981: ,p_contribution_period=>p_contribution_period
1981: ,p_contribution_period=>p_contribution_period
1982: );
1983: ELSIF p_form_type = 'FORM8' THEN
1984: --
1985: pay_in_utils.set_location(g_debug,l_procedure, 40);
1986: create_form8_xml(p_pf_org_id => p_pf_org_id
1987: ,p_contribution_period => p_contribution_period
1988: ,p_effective_start_date => l_effective_start_date
1989: ,p_effective_end_date => l_effective_end_date
1989: ,p_effective_end_date => l_effective_end_date
1990: );
1991: ELSIF p_form_type ='FORM7' THEN
1992: --
1993: pay_in_utils.set_location(g_debug,l_procedure, 50);
1994: create_form7_xml(p_pf_org_id => p_pf_org_id
1995: ,p_pension_number => p_pension_number
1996: ,p_employee_type => p_employee_type
1997: ,p_contribution_period => p_contribution_period
1999: ,p_effective_end_date => l_effective_end_date
2000: );
2001: ELSIF p_form_type ='ESI6' THEN
2002: --
2003: pay_in_utils.set_location(g_debug,l_procedure, 60);
2004: create_esi_xml(p_esi_org_id => p_esi_org_id
2005: ,p_contribution_period => l_contribution_period
2006: ,p_esi_coverage => p_esi_coverage
2007: ,p_sysdate => p_sysdate);
2007: ,p_sysdate => p_sysdate);
2008:
2009: ELSIF p_form_type ='PT3' THEN
2010: --
2011: pay_in_utils.set_location(g_debug,l_procedure, 70);
2012: create_pt_form3_xml(p_pt_org_id => p_pt_org_id
2013: ,p_frequency => p_frequency
2014: ,p_year => p_year
2015: ,p_period => p_period);
2014: ,p_year => p_year
2015: ,p_period => p_period);
2016:
2017: ELSIF p_form_type='FORM27A' THEN
2018: pay_in_utils.set_location(g_debug,l_procedure, 80);
2019: create_form27A_xml(P_GRE_ORG_ID => P_GRE_ORG_ID
2020: ,P_ASSESS_YEAR => P_ASSESS_YEAR);
2021:
2022: ELSIF p_form_type='INECR' THEN
2019: create_form27A_xml(P_GRE_ORG_ID => P_GRE_ORG_ID
2020: ,P_ASSESS_YEAR => P_ASSESS_YEAR);
2021:
2022: ELSIF p_form_type='INECR' THEN
2023: pay_in_utils.set_location(g_debug,l_procedure, 85);
2024: create_ecr_xml(p_pf_org_id => p_pf_org_id
2025: ,p_contribution_period=> p_contribution_period
2026: ,p_month => p_month);
2027:
2030: END IF;
2031:
2032:
2033: p_xml := g_xml_data;
2034: pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 90);
2035:
2036:
2037: END init_code;
2038:
2295:
2296: BEGIN
2297: g_debug := hr_utility.debug_enabled;
2298: l_procedure := g_package ||'create_form8_xml';
2299: pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
2300:
2301:
2302: pay_in_xml_utils.gXMLTable.DELETE;
2303: --
2318: --System Date
2319: l_tag :=pay_in_xml_utils.getTag('c_current_date_in_hh_mm_ss',l_sys_date_time);
2320: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
2321:
2322: pay_in_utils.set_location(g_debug,l_procedure, 20);
2323:
2324: FOR c_rec IN c_pf_org_id(p_pf_org_id
2325: ,p_contribution_period
2326: ,p_effective_start_date
2325: ,p_contribution_period
2326: ,p_effective_start_date
2327: ,p_effective_end_date)
2328: LOOP
2329: pay_in_utils.set_location(g_debug,l_procedure, 30);
2330: l_tag := '
2331: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
2332:
2333: IF g_debug THEN
2330: l_tag := '
2331: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
2332:
2333: IF g_debug THEN
2334: pay_in_utils.trace('PF Organization id ',p_pf_org_id);
2335: pay_in_utils.trace('Contribution Period ',p_contribution_period);
2336: END IF;
2337:
2338: l_count:=1;
2331: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
2332:
2333: IF g_debug THEN
2334: pay_in_utils.trace('PF Organization id ',p_pf_org_id);
2335: pay_in_utils.trace('Contribution Period ',p_contribution_period);
2336: END IF;
2337:
2338: l_count:=1;
2339: --PF Org Reg Name
2362:
2363: l_employee_no:=1;
2364: l_org_pf_ytd := 0;
2365: l_org_pension_ytd :=0;
2366: pay_in_utils.set_location(g_debug,l_procedure, 40);
2367:
2368: FOR assignment_rec IN c_assignment_id(c_rec.action_information2
2369: ,p_contribution_period
2370: ,p_effective_start_date
2369: ,p_contribution_period
2370: ,p_effective_start_date
2371: ,p_effective_end_date)
2372: LOOP
2373: pay_in_utils.set_location(g_debug,l_procedure, 50);
2374:
2375: IF g_debug THEN
2376: pay_in_utils.trace('Pension Number ',assignment_rec.action_information15);
2377: pay_in_utils.trace('PF Organization ID ',c_rec.action_information2);
2372: LOOP
2373: pay_in_utils.set_location(g_debug,l_procedure, 50);
2374:
2375: IF g_debug THEN
2376: pay_in_utils.trace('Pension Number ',assignment_rec.action_information15);
2377: pay_in_utils.trace('PF Organization ID ',c_rec.action_information2);
2378: END IF;
2379:
2380: FOR chk_excluded_employee IN csr_not_excluded_employee(c_rec.action_information2
2373: pay_in_utils.set_location(g_debug,l_procedure, 50);
2374:
2375: IF g_debug THEN
2376: pay_in_utils.trace('Pension Number ',assignment_rec.action_information15);
2377: pay_in_utils.trace('PF Organization ID ',c_rec.action_information2);
2378: END IF;
2379:
2380: FOR chk_excluded_employee IN csr_not_excluded_employee(c_rec.action_information2
2381: ,p_contribution_period
2380: FOR chk_excluded_employee IN csr_not_excluded_employee(c_rec.action_information2
2381: ,p_contribution_period
2382: ,assignment_rec.action_information15)
2383: LOOP
2384: pay_in_utils.set_location(g_debug,l_procedure, 60);
2385: -- Display only when employee status is not excluded
2386: IF chk_excluded_employee.status ='1' THEN
2387: OPEN c_emp_name(c_rec.action_information2
2388: ,assignment_rec.action_information15
2398: FOR c_mon_contr IN c_monthly_contributions(c_rec.action_information2
2399: ,assignment_rec.action_information15)
2400: LOOP
2401:
2402: l_cont_type_date_earned :=pay_in_utils.get_scl_segment_on_date(assignment_rec.assignment_id
2403: ,l_bg_id
2404: ,c_mon_contr.date_earned
2405: ,'segment12');
2406: IF(l_cont_type_date_earned = '0')THEN
2424: ,p_contribution_period
2425: ,assignment_rec.action_information15
2426: ,l_salary_ceiling)
2427: LOOP
2428: pay_in_utils.set_location(g_debug,l_procedure, 70);
2429: l_count:=1;
2430: --Sl No.
2431: pay_in_xml_utils.gXMLTable(l_count).Name := 'c_sl_no';
2432: pay_in_xml_utils.gXMLTable(l_count).Value := (l_employee_no);
2441: pay_in_xml_utils.gXMLTable(l_count).Value := l_employee_name ;
2442:
2443: l_count := l_count + 1;
2444: IF g_debug THEN
2445: pay_in_utils.trace('Employee Name ',pay_in_xml_utils.gXMLTable(l_count).Value);
2446: END IF;
2447:
2448:
2449: pay_in_utils.set_location(g_debug,l_procedure, 80);
2445: pay_in_utils.trace('Employee Name ',pay_in_xml_utils.gXMLTable(l_count).Value);
2446: END IF;
2447:
2448:
2449: pay_in_utils.set_location(g_debug,l_procedure, 80);
2450:
2451: l_org_pf_ytd := l_org_pf_ytd + asg_sum ;
2452: l_org_pension_ytd := l_org_pension_ytd + child_asg_rec.pension;
2453:
2460: 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));
2461: l_count := l_count + 1;
2462:
2463: IF g_debug THEN
2464: pay_in_utils.trace('PF Wages ',asg_sum);
2465: pay_in_utils.trace('Pension Amt ',child_asg_rec.pension);
2466: END IF;
2467:
2468: l_remarks := NULL;
2461: l_count := l_count + 1;
2462:
2463: IF g_debug THEN
2464: pay_in_utils.trace('PF Wages ',asg_sum);
2465: pay_in_utils.trace('Pension Amt ',child_asg_rec.pension);
2466: END IF;
2467:
2468: l_remarks := NULL;
2469: l_remarks_dummy := NULL;
2472: ,assignment_rec.action_information15
2473: ,p_effective_start_date
2474: ,p_effective_end_date)
2475: LOOP
2476: pay_in_utils.set_location(g_debug,l_procedure, 90);
2477: l_payroll_mon := TO_NUMBER(TO_CHAR(c_rec_child.mon,'MM'));
2478: IF (l_payroll_mon <4 ) THEN
2479: l_mon := l_payroll_mon + 9;
2480: ELSE
2505: END IF;
2506: END LOOP;
2507: END LOOP;
2508:
2509: pay_in_utils.set_location(g_debug,l_procedure, 100);
2510: pay_in_xml_utils.gXMLTable.delete;
2511:
2512: l_count:=1;
2513: pay_in_xml_utils.gXMLTable(l_count).Name := 'c_wage_ytd';
2518: l_count := l_count + 1;
2519: pay_in_xml_utils.multiColumnar('sum',pay_in_xml_utils.gXMLTable,l_count,g_xml_data);
2520:
2521: IF g_debug THEN
2522: pay_in_utils.trace('Total PF Wages ',l_org_pf_ytd);
2523: pay_in_utils.trace('Total Pension Amt ',l_org_pension_ytd);
2524: END IF;
2525:
2526:
2519: pay_in_xml_utils.multiColumnar('sum',pay_in_xml_utils.gXMLTable,l_count,g_xml_data);
2520:
2521: IF g_debug THEN
2522: pay_in_utils.trace('Total PF Wages ',l_org_pf_ytd);
2523: pay_in_utils.trace('Total Pension Amt ',l_org_pension_ytd);
2524: END IF;
2525:
2526:
2527: pay_in_xml_utils.gXMLTable.delete;
2533: l_admn_total:=0;
2534: l_edli_adm_total:=0;
2535: l_summation:=0;
2536:
2537: pay_in_utils.set_location(g_debug,l_procedure, 110);
2538:
2539: FOR c_pf_org_child_rec IN c_pf_employer(c_rec.action_information2 --PF Org ID
2540: ,p_effective_start_date
2541: ,p_effective_end_date)
2540: ,p_effective_start_date
2541: ,p_effective_end_date)
2542: LOOP
2543:
2544: pay_in_utils.set_location(g_debug,l_procedure, 120);
2545:
2546: IF g_debug THEN
2547: pay_in_utils.trace('Month Number ', c_pf_org_child_rec.mon);
2548: pay_in_utils.trace('Pension ', c_pf_org_child_rec.pension);
2543:
2544: pay_in_utils.set_location(g_debug,l_procedure, 120);
2545:
2546: IF g_debug THEN
2547: pay_in_utils.trace('Month Number ', c_pf_org_child_rec.mon);
2548: pay_in_utils.trace('Pension ', c_pf_org_child_rec.pension);
2549: END IF;
2550:
2551: IF (c_pf_employer%ROWCOUNT=1) THEN
2544: pay_in_utils.set_location(g_debug,l_procedure, 120);
2545:
2546: IF g_debug THEN
2547: pay_in_utils.trace('Month Number ', c_pf_org_child_rec.mon);
2548: pay_in_utils.trace('Pension ', c_pf_org_child_rec.pension);
2549: END IF;
2550:
2551: IF (c_pf_employer%ROWCOUNT=1) THEN
2552: IF c_pf_org_child_rec.mon>1 THEN
2586: pay_in_xml_utils.gXMLTable(l_count).Name := 'c_emp_no';
2587: pay_in_xml_utils.gXMLTable(l_count).Value := l_employee_no - 1;
2588: l_count := l_count + 1;
2589:
2590: pay_in_utils.set_location(g_debug,l_procedure, 130);
2591:
2592: --Employer Representative Signature
2593: pay_in_xml_utils.gXMLTable(l_count).Name := 'c_employer';
2594: OPEN c_rep_name(p_contribution_period
2603: l_tag := '';
2604: dbms_lob.writeAppend(g_xml_data, LENGTH(l_tag), l_tag);
2605: pay_in_xml_utils.gXMLTable.DELETE;
2606: l_row_count:=NULL;
2607: pay_in_utils.set_location(g_debug,l_procedure, 140);
2608: END LOOP;
2609:
2610: pay_in_utils.set_location(g_debug,l_procedure, 150);
2611:
2606: l_row_count:=NULL;
2607: pay_in_utils.set_location(g_debug,l_procedure, 140);
2608: END LOOP;
2609:
2610: pay_in_utils.set_location(g_debug,l_procedure, 150);
2611:
2612: l_tag := '';
2613: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
2614:
2614:
2615: fnd_file.put_line(fnd_file.log,'XML Created.');
2616: -- INSERT INTO temp_clob VALUES (g_xml_data);
2617:
2618: pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 160);
2619:
2620:
2621: END create_form8_xml;
2622:
2845: BEGIN
2846:
2847: g_debug := hr_utility.debug_enabled;
2848: l_procedure := g_package ||'employee_type';
2849: pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
2850:
2851: IF g_debug THEN
2852: pay_in_utils.trace('**************************************************','********************');
2853: pay_in_utils.trace('PF Number ',p_pf_number);
2848: l_procedure := g_package ||'employee_type';
2849: pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
2850:
2851: IF g_debug THEN
2852: pay_in_utils.trace('**************************************************','********************');
2853: pay_in_utils.trace('PF Number ',p_pf_number);
2854: pay_in_utils.trace('Employee Type ',p_employee_type);
2855: pay_in_utils.trace('Effective Start Date ',p_effective_start_date);
2856: pay_in_utils.trace('Effective End Date ',p_effective_end_date);
2849: pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
2850:
2851: IF g_debug THEN
2852: pay_in_utils.trace('**************************************************','********************');
2853: pay_in_utils.trace('PF Number ',p_pf_number);
2854: pay_in_utils.trace('Employee Type ',p_employee_type);
2855: pay_in_utils.trace('Effective Start Date ',p_effective_start_date);
2856: pay_in_utils.trace('Effective End Date ',p_effective_end_date);
2857: pay_in_utils.trace('CP PF Org id ',p_cp_pf_org_id);
2850:
2851: IF g_debug THEN
2852: pay_in_utils.trace('**************************************************','********************');
2853: pay_in_utils.trace('PF Number ',p_pf_number);
2854: pay_in_utils.trace('Employee Type ',p_employee_type);
2855: pay_in_utils.trace('Effective Start Date ',p_effective_start_date);
2856: pay_in_utils.trace('Effective End Date ',p_effective_end_date);
2857: pay_in_utils.trace('CP PF Org id ',p_cp_pf_org_id);
2858: pay_in_utils.trace('PF Org id ',p_pf_org_id);
2851: IF g_debug THEN
2852: pay_in_utils.trace('**************************************************','********************');
2853: pay_in_utils.trace('PF Number ',p_pf_number);
2854: pay_in_utils.trace('Employee Type ',p_employee_type);
2855: pay_in_utils.trace('Effective Start Date ',p_effective_start_date);
2856: pay_in_utils.trace('Effective End Date ',p_effective_end_date);
2857: pay_in_utils.trace('CP PF Org id ',p_cp_pf_org_id);
2858: pay_in_utils.trace('PF Org id ',p_pf_org_id);
2859: pay_in_utils.trace('**************************************************','********************');
2852: pay_in_utils.trace('**************************************************','********************');
2853: pay_in_utils.trace('PF Number ',p_pf_number);
2854: pay_in_utils.trace('Employee Type ',p_employee_type);
2855: pay_in_utils.trace('Effective Start Date ',p_effective_start_date);
2856: pay_in_utils.trace('Effective End Date ',p_effective_end_date);
2857: pay_in_utils.trace('CP PF Org id ',p_cp_pf_org_id);
2858: pay_in_utils.trace('PF Org id ',p_pf_org_id);
2859: pay_in_utils.trace('**************************************************','********************');
2860: END IF;
2853: pay_in_utils.trace('PF Number ',p_pf_number);
2854: pay_in_utils.trace('Employee Type ',p_employee_type);
2855: pay_in_utils.trace('Effective Start Date ',p_effective_start_date);
2856: pay_in_utils.trace('Effective End Date ',p_effective_end_date);
2857: pay_in_utils.trace('CP PF Org id ',p_cp_pf_org_id);
2858: pay_in_utils.trace('PF Org id ',p_pf_org_id);
2859: pay_in_utils.trace('**************************************************','********************');
2860: END IF;
2861:
2854: pay_in_utils.trace('Employee Type ',p_employee_type);
2855: pay_in_utils.trace('Effective Start Date ',p_effective_start_date);
2856: pay_in_utils.trace('Effective End Date ',p_effective_end_date);
2857: pay_in_utils.trace('CP PF Org id ',p_cp_pf_org_id);
2858: pay_in_utils.trace('PF Org id ',p_pf_org_id);
2859: pay_in_utils.trace('**************************************************','********************');
2860: END IF;
2861:
2862: l_org_count:=0;
2855: pay_in_utils.trace('Effective Start Date ',p_effective_start_date);
2856: pay_in_utils.trace('Effective End Date ',p_effective_end_date);
2857: pay_in_utils.trace('CP PF Org id ',p_cp_pf_org_id);
2858: pay_in_utils.trace('PF Org id ',p_pf_org_id);
2859: pay_in_utils.trace('**************************************************','********************');
2860: END IF;
2861:
2862: l_org_count:=0;
2863:
2865: FETCH c_person_id INTO l_person_id;
2866: CLOSE c_person_id;
2867:
2868: IF p_employee_type = 'TRANSFERRED' THEN
2869: pay_in_utils.set_location(g_debug,l_procedure, 20);
2870: /*This is for finding the Organization Change Count*/
2871: FOR c_rec IN c_transfer_check
2872: LOOP
2873: l_org_count:=l_org_count+1;
2871: FOR c_rec IN c_transfer_check
2872: LOOP
2873: l_org_count:=l_org_count+1;
2874: END LOOP;
2875: pay_in_utils.set_location(g_debug,l_procedure, 20);
2876:
2877:
2878: IF l_org_count <2 THEN /*This means there were'nt any changes*/
2879: pay_in_utils.set_location(g_debug,l_procedure, 30);
2875: pay_in_utils.set_location(g_debug,l_procedure, 20);
2876:
2877:
2878: IF l_org_count <2 THEN /*This means there were'nt any changes*/
2879: pay_in_utils.set_location(g_debug,l_procedure, 30);
2880: p_status:='CURRENT';
2881: RETURN FALSE;
2882: ELSIF p_cp_pf_org_id IS NOT NULL THEN /*There were some Organizational Changes*/
2883: pay_in_utils.set_location(g_debug,l_procedure, 40);
2879: pay_in_utils.set_location(g_debug,l_procedure, 30);
2880: p_status:='CURRENT';
2881: RETURN FALSE;
2882: ELSIF p_cp_pf_org_id IS NOT NULL THEN /*There were some Organizational Changes*/
2883: pay_in_utils.set_location(g_debug,l_procedure, 40);
2884: OPEN c_transfer_status(p_cp_pf_org_id);
2885: FETCH c_transfer_status INTO l_reason;
2886: CLOSE c_transfer_status;
2887:
2886: CLOSE c_transfer_status;
2887:
2888:
2889: IF l_reason IS NULL THEN --Thats when the last payroll archived didnt have the p_cp_pf_org_id
2890: pay_in_utils.set_location(g_debug,l_procedure, 50);
2891: p_status:='TRANSFERRED';
2892: RETURN TRUE;
2893: ELSE --This means that employee is still attached to that PF org. Now in the last archived data
2894: --there can be multiple records. So we have to check for records count
2891: p_status:='TRANSFERRED';
2892: RETURN TRUE;
2893: ELSE --This means that employee is still attached to that PF org. Now in the last archived data
2894: --there can be multiple records. So we have to check for records count
2895: pay_in_utils.set_location(g_debug,l_procedure, 60);
2896: OPEN c_last_pay_count;
2897: FETCH c_last_pay_count INTO l_org_count;
2898: CLOSE c_last_pay_count;
2899: IF l_org_count = 1 THEN --This org was present at last payroll action
2896: OPEN c_last_pay_count;
2897: FETCH c_last_pay_count INTO l_org_count;
2898: CLOSE c_last_pay_count;
2899: IF l_org_count = 1 THEN --This org was present at last payroll action
2900: pay_in_utils.set_location(g_debug,l_procedure, 70);
2901: p_status:='CURRENT';
2902: RETURN FALSE;
2903: ELSE
2904: pay_in_utils.set_location(g_debug,l_procedure, 80);
2900: pay_in_utils.set_location(g_debug,l_procedure, 70);
2901: p_status:='CURRENT';
2902: RETURN FALSE;
2903: ELSE
2904: pay_in_utils.set_location(g_debug,l_procedure, 80);
2905: l_reason:=NULL;
2906: OPEN c_last_pay_date;
2907: FETCH c_last_pay_date INTO l_pay_date;
2908: CLOSE c_last_pay_date; --Find the last archived payroll date
2910: OPEN c_final_check(p_cp_pf_org_id,l_pay_date);
2911: FETCH c_final_check INTO l_reason;--Find the presence of c_pf_org_id in SCL
2912: CLOSE c_final_check;
2913: IF l_reason IS NULL THEN --p_cp_pf_org_id is not in SCL
2914: pay_in_utils.set_location(g_debug,l_procedure, 90);
2915: p_status:='TRANSFERRED';
2916: RETURN TRUE;
2917: ELSE
2918: pay_in_utils.set_location(g_debug,l_procedure, 100);
2914: pay_in_utils.set_location(g_debug,l_procedure, 90);
2915: p_status:='TRANSFERRED';
2916: RETURN TRUE;
2917: ELSE
2918: pay_in_utils.set_location(g_debug,l_procedure, 100);
2919: p_status:='CURRENT';--p_cp_pf_org_id is in SCl
2920: RETURN FALSE;
2921: END IF;
2922: END IF;--End for l_org_count
2922: END IF;--End for l_org_count
2923: END IF;--End for l_reason being null
2924:
2925: ELSE
2926: pay_in_utils.set_location(g_debug,l_procedure, 110);
2927: l_reason:=NULL;
2928: OPEN c_transfer_status(p_pf_org_id);
2929: FETCH c_transfer_status INTO l_reason;
2930: CLOSE c_transfer_status;
2929: FETCH c_transfer_status INTO l_reason;
2930: CLOSE c_transfer_status;
2931:
2932: IF l_reason IS NULL THEN --Thats when the last payroll archived had the p_pf_org_id
2933: pay_in_utils.set_location(g_debug,l_procedure, 120);
2934: p_status:='TRANSFERRED';
2935: RETURN TRUE;
2936: ELSE --This means that employee is still attached to that PF org. Now in the last archived data
2937: --there can be multiple records. So we have to check for records count
2934: p_status:='TRANSFERRED';
2935: RETURN TRUE;
2936: ELSE --This means that employee is still attached to that PF org. Now in the last archived data
2937: --there can be multiple records. So we have to check for records count
2938: pay_in_utils.set_location(g_debug,l_procedure, 130);
2939: OPEN c_last_pay_count;
2940: FETCH c_last_pay_count INTO l_org_count;
2941: CLOSE c_last_pay_count;
2942: IF l_org_count < 2 THEN --This org was present at last payroll action
2939: OPEN c_last_pay_count;
2940: FETCH c_last_pay_count INTO l_org_count;
2941: CLOSE c_last_pay_count;
2942: IF l_org_count < 2 THEN --This org was present at last payroll action
2943: pay_in_utils.set_location(g_debug,l_procedure, 140);
2944: p_status:='CURRENT';
2945: RETURN FALSE;
2946: ELSE
2947: pay_in_utils.set_location(g_debug,l_procedure, 150);
2943: pay_in_utils.set_location(g_debug,l_procedure, 140);
2944: p_status:='CURRENT';
2945: RETURN FALSE;
2946: ELSE
2947: pay_in_utils.set_location(g_debug,l_procedure, 150);
2948: l_reason:=NULL;
2949: OPEN c_last_pay_date;
2950: FETCH c_last_pay_date INTO l_pay_date;
2951: CLOSE c_last_pay_date; --Find the last archived payroll date
2954: FETCH c_final_check INTO l_reason;--Find the presence of c_pf_org_id in SCL
2955: CLOSE c_final_check;
2956:
2957: IF l_reason IS NULL THEN --p_cp_pf_org_id is not in SCL
2958: pay_in_utils.set_location(g_debug,l_procedure, 160);
2959: p_status:='TRANSFERRED';
2960: RETURN TRUE;
2961: ELSE
2962: pay_in_utils.set_location(g_debug,l_procedure, 170);
2958: pay_in_utils.set_location(g_debug,l_procedure, 160);
2959: p_status:='TRANSFERRED';
2960: RETURN TRUE;
2961: ELSE
2962: pay_in_utils.set_location(g_debug,l_procedure, 170);
2963: p_status:='CURRENT';--p_cp_pf_org_id is in SCl
2964: RETURN FALSE;
2965: END IF;
2966: END IF;
2967:
2968: END IF;
2969: END IF;
2970: ELSIF p_employee_type = 'TERMINATED' THEN
2971: pay_in_utils.set_location(g_debug,l_procedure, 180);
2972: OPEN c_termination_check(l_person_id);
2973: FETCH c_termination_check INTO l_reason;
2974: CLOSE c_termination_check;
2975:
2974: CLOSE c_termination_check;
2975:
2976:
2977: IF l_reason IS NULL THEN
2978: pay_in_utils.set_location(g_debug,l_procedure, 190);
2979: RETURN FALSE;
2980: ELSE
2981: RETURN TRUE;
2982: END IF;
2981: RETURN TRUE;
2982: END IF;
2983:
2984: ELSIF p_employee_type = 'CURRENT' THEN
2985: pay_in_utils.set_location(g_debug,l_procedure, 200);
2986: OPEN c_termination_check(l_person_id);
2987: FETCH c_termination_check INTO l_reason;
2988: CLOSE c_termination_check;
2989:
2989:
2990:
2991: IF l_reason IS NULL THEN
2992: --The employee is not terminated, So find whether he is in that org or not
2993: pay_in_utils.set_location(g_debug,l_procedure, 210);
2994: IF p_cp_pf_org_id IS NOT NULL THEN
2995: OPEN c_transfer_status(p_cp_pf_org_id);
2996: FETCH c_transfer_status INTO l_reason;
2997: CLOSE c_transfer_status;
2995: OPEN c_transfer_status(p_cp_pf_org_id);
2996: FETCH c_transfer_status INTO l_reason;
2997: CLOSE c_transfer_status;
2998:
2999: pay_in_utils.set_location(g_debug,l_procedure, 220);
3000:
3001: IF l_reason IS NULL THEN
3002: pay_in_utils.set_location(g_debug,l_procedure, 230);
3003: p_status:='TRANSFERRED'; --This is because on last day the pf org is not as parameter passed
2998:
2999: pay_in_utils.set_location(g_debug,l_procedure, 220);
3000:
3001: IF l_reason IS NULL THEN
3002: pay_in_utils.set_location(g_debug,l_procedure, 230);
3003: p_status:='TRANSFERRED'; --This is because on last day the pf org is not as parameter passed
3004: RETURN FALSE; -- Bug 4033745
3005: ELSE
3006: --This means that assignment is still having p_cp_pf_org_id. But there may be multiple records
3004: RETURN FALSE; -- Bug 4033745
3005: ELSE
3006: --This means that assignment is still having p_cp_pf_org_id. But there may be multiple records
3007: --So first checking for them.
3008: pay_in_utils.set_location(g_debug,l_procedure, 240);
3009: OPEN c_last_pay_count;
3010: FETCH c_last_pay_count INTO l_org_count;
3011: CLOSE c_last_pay_count;
3012:
3010: FETCH c_last_pay_count INTO l_org_count;
3011: CLOSE c_last_pay_count;
3012:
3013: IF l_org_count = 1 THEN --This org was present at last payroll action as there were only 1 rec
3014: pay_in_utils.set_location(g_debug,l_procedure, 250);
3015: p_status:='CURRENT';
3016: RETURN TRUE;
3017: ELSE
3018: pay_in_utils.set_location(g_debug,l_procedure, 260);
3014: pay_in_utils.set_location(g_debug,l_procedure, 250);
3015: p_status:='CURRENT';
3016: RETURN TRUE;
3017: ELSE
3018: pay_in_utils.set_location(g_debug,l_procedure, 260);
3019: l_reason:=NULL;
3020: OPEN c_last_pay_date;
3021: FETCH c_last_pay_date INTO l_pay_date;
3022: CLOSE c_last_pay_date; --Find the last archived payroll date
3023: OPEN c_final_check(p_cp_pf_org_id,l_pay_date);
3024: FETCH c_final_check INTO l_reason;--Find the presence of c_pf_org_id in SCL
3025: CLOSE c_final_check;
3026: IF l_reason IS NULL THEN --p_cp_pf_org_id is not in SCL
3027: pay_in_utils.set_location(g_debug,l_procedure, 270);
3028: p_status:='TRANSFERRED';
3029: RETURN FALSE;
3030: ELSE
3031: pay_in_utils.set_location(g_debug,l_procedure, 280);
3027: pay_in_utils.set_location(g_debug,l_procedure, 270);
3028: p_status:='TRANSFERRED';
3029: RETURN FALSE;
3030: ELSE
3031: pay_in_utils.set_location(g_debug,l_procedure, 280);
3032: p_status:='CURRENT';--p_cp_pf_org_id is in SCl
3033: RETURN TRUE;
3034: END IF;
3035: END IF;
3034: END IF;
3035: END IF;
3036: END IF;
3037: ELSE
3038: pay_in_utils.set_location(g_debug,l_procedure, 290);
3039: OPEN c_transfer_status(p_pf_org_id);
3040: FETCH c_transfer_status INTO l_reason;
3041: CLOSE c_transfer_status;
3042:
3040: FETCH c_transfer_status INTO l_reason;
3041: CLOSE c_transfer_status;
3042:
3043: IF l_reason IS NULL THEN
3044: pay_in_utils.set_location(g_debug,l_procedure, 300);
3045: p_status:='TRANSFERRED'; --This is because on last day the pf org is not as parameter passed
3046: RETURN FALSE; -- Bug 4033745
3047: ELSE
3048: pay_in_utils.set_location(g_debug,l_procedure, 310);
3044: pay_in_utils.set_location(g_debug,l_procedure, 300);
3045: p_status:='TRANSFERRED'; --This is because on last day the pf org is not as parameter passed
3046: RETURN FALSE; -- Bug 4033745
3047: ELSE
3048: pay_in_utils.set_location(g_debug,l_procedure, 310);
3049: --This means that assignment is still having p_cp_pf_org_id. But there may be multiple records
3050: --So first checking for them.
3051: OPEN c_last_pay_count;
3052: FETCH c_last_pay_count INTO l_org_count;
3051: OPEN c_last_pay_count;
3052: FETCH c_last_pay_count INTO l_org_count;
3053: CLOSE c_last_pay_count;
3054: IF l_org_count = 1 THEN --This org was present at last payroll action as there were only 1 rec
3055: pay_in_utils.set_location(g_debug,l_procedure, 320);
3056: p_status:='CURRENT';
3057: RETURN TRUE;
3058: ELSE
3059: pay_in_utils.set_location(g_debug,l_procedure, 330);
3055: pay_in_utils.set_location(g_debug,l_procedure, 320);
3056: p_status:='CURRENT';
3057: RETURN TRUE;
3058: ELSE
3059: pay_in_utils.set_location(g_debug,l_procedure, 330);
3060: l_reason:=NULL;
3061: OPEN c_last_pay_date;
3062: FETCH c_last_pay_date INTO l_pay_date;
3063: CLOSE c_last_pay_date; --Find the last archived payroll date
3065: OPEN c_final_check(p_pf_org_id,l_pay_date);
3066: FETCH c_final_check INTO l_reason;--Find the presence of c_pf_org_id in SCL
3067: CLOSE c_final_check;
3068: IF l_reason IS NULL THEN --p_pf_org_id is not in SCL
3069: pay_in_utils.set_location(g_debug,l_procedure, 330);
3070: p_status:='TRANSFERRED';
3071: RETURN FALSE;
3072: ELSE
3073: pay_in_utils.set_location(g_debug,l_procedure, 340);
3069: pay_in_utils.set_location(g_debug,l_procedure, 330);
3070: p_status:='TRANSFERRED';
3071: RETURN FALSE;
3072: ELSE
3073: pay_in_utils.set_location(g_debug,l_procedure, 340);
3074: p_status:='CURRENT';--p_pf_org_id is in SCl
3075: RETURN TRUE;
3076: END IF;
3077: END IF;
3077: END IF;
3078: END IF;
3079: END IF;
3080: ELSE
3081: pay_in_utils.set_location(g_debug,l_procedure, 350);
3082: RETURN FALSE;
3083: END IF;
3084:
3085: ELSE
3082: RETURN FALSE;
3083: END IF;
3084:
3085: ELSE
3086: pay_in_utils.set_location(g_debug,l_procedure, 360);
3087: RETURN TRUE;
3088: END IF;
3089:
3090: END;
3351: pay_in_xml_utils.gXMLTable.DELETE;
3352:
3353: g_debug := hr_utility.debug_enabled;
3354: l_procedure := g_package ||'create_form6a_xml';
3355: pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
3356:
3357: --
3358: fnd_file.put_line(fnd_file.log,'Creating the XML...');
3359: dbms_lob.createtemporary(g_xml_data,FALSE,DBMS_LOB.CALL);
3372: --System Date
3373: l_tag :=pay_in_xml_utils.getTag('c_current_date_in_hh_mm_ss',l_sys_date_time);
3374: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
3375:
3376: pay_in_utils.set_location(g_debug,l_procedure, 20);
3377:
3378: FOR c_rec IN c_pf_org_id(p_pf_org_id
3379: ,p_effective_start_date
3380: ,p_effective_end_date
3379: ,p_effective_start_date
3380: ,p_effective_end_date
3381: ,p_contribution_period)
3382: LOOP
3383: pay_in_utils.set_location(g_debug,l_procedure, 30);
3384: l_org_pf_ytd := 0 ;
3385: l_org_employer := 0 ;
3386: l_org_employee := 0 ;
3387: l_org_pension := 0 ;
3403: pay_in_xml_utils.gXMLTable(l_count).Value := (c_rec.action_information6);
3404: l_count := l_count + 1;
3405:
3406: IF g_debug THEN
3407: pay_in_utils.trace('PF Organization ',c_rec.action_information3);
3408: END IF;
3409:
3410: --Statuory Rate of Contribution
3411: SELECT ROUND(fnd_number.canonical_to_number(GLOBAL_VALUE)*100,2) INTO l_rate
3425: pay_in_xml_utils.gXMLTable(l_count).Name := 'c_end_year';
3426: pay_in_xml_utils.gXMLTable(l_count).Value := (to_char(p_effective_end_date,'YYYY'));
3427: l_count := l_count + 1;
3428:
3429: pay_in_utils.set_location(g_debug,l_procedure, 30);
3430:
3431: --Number of people making Voluntary Contributions in that financial Year
3432: OPEN c_number_of_contr(c_rec.action_information2
3433: ,p_effective_start_date
3439: l_count := l_count + 1;
3440: pay_in_xml_utils.multiColumnar('org',pay_in_xml_utils.gXMLTable,l_count,g_xml_data);
3441: l_employee_no:=1;
3442:
3443: pay_in_utils.set_location(g_debug,l_procedure, 40);
3444:
3445: BEGIN
3446: OPEN c_assignment_id(c_rec.action_information2
3447: ,p_effective_start_date
3485:
3486: LOOP
3487: FETCH c_asg_id into assignment_rec;
3488: EXIT WHEN c_asg_id%NOTFOUND;
3489: pay_in_utils.set_location(g_debug,l_procedure, 50);
3490:
3491: IF g_debug THEN
3492: pay_in_utils.trace('PF Number ',assignment_rec.action_information3);
3493: END IF;
3488: EXIT WHEN c_asg_id%NOTFOUND;
3489: pay_in_utils.set_location(g_debug,l_procedure, 50);
3490:
3491: IF g_debug THEN
3492: pay_in_utils.trace('PF Number ',assignment_rec.action_information3);
3493: END IF;
3494:
3495: FOR child_asg_rec IN c_asg_summation_details(c_rec.action_information2
3496: ,assignment_rec.action_information3
3546:
3547: l_remarks := NULL;
3548: l_remarks_dummy := NULL;
3549:
3550: pay_in_utils.set_location(g_debug,l_procedure, 50);
3551:
3552: For c_rec_child IN c_asg_details(c_rec.action_information2 --PF Org ID
3553: ,assignment_rec.action_information3
3554: ,p_effective_start_date
3553: ,assignment_rec.action_information3
3554: ,p_effective_start_date
3555: ,p_effective_end_date)
3556: LOOP
3557: pay_in_utils.set_location(g_debug,l_procedure, 60);
3558: l_payroll_mon := TO_NUMBER(TO_CHAR(c_rec_child.mon,'MM'));
3559: IF (l_payroll_mon <4 ) THEN
3560: l_mon := l_payroll_mon + 9;
3561: ELSE
3575: l_remarks:=l_remarks_dummy;
3576: END IF;
3577: END LOOP;
3578:
3579: pay_in_utils.set_location(g_debug,l_procedure, 70);
3580:
3581: OPEN c_vol_rate(c_rec.action_information2 --PF Org ID
3582: ,assignment_rec.action_information3
3583: ,p_effective_start_date
3595: pay_in_xml_utils.gXMLTable(l_count).Name := 'c_remarks';
3596: pay_in_xml_utils.gXMLTable(l_count).Value := (l_remarks);
3597: l_count := l_count + 1;
3598: pay_in_xml_utils.multiColumnar('details',pay_in_xml_utils.gXMLTable,l_count,g_xml_data);
3599: pay_in_utils.set_location(g_debug,l_procedure, 80);
3600: END LOOP;
3601: END LOOP;
3602: IF c_asg_id%ISOPEN THEN
3603: CLOSE c_asg_id;
3601: END LOOP;
3602: IF c_asg_id%ISOPEN THEN
3603: CLOSE c_asg_id;
3604: END IF;
3605: pay_in_utils.set_location(g_debug,l_procedure, 90);
3606: pay_in_xml_utils.gXMLTable.delete;
3607: l_count:=1;
3608: pay_in_xml_utils.gXMLTable(l_count).Name := 'c_wage_ytd';
3609: pay_in_xml_utils.gXMLTable(l_count).Value :=
3632: l_edli_adm_total:=0;
3633: l_summation:=0;
3634:
3635: i := 0;
3636: pay_in_utils.set_location(g_debug,l_procedure, 200);
3637: WHILE i<12
3638: LOOP
3639: l_epf :=0;
3640: l_pension :=0;
3646: ,p_effective_start_date
3647: ,p_effective_end_date
3648: ,i)
3649: LOOP
3650: pay_in_utils.set_location(g_debug,l_procedure, 210);
3651: l_epf :=c_pf_org_child_rec.epf;
3652: l_pension :=c_pf_org_child_rec.pension;
3653: l_dli :=c_pf_org_child_rec.dli;
3654: l_admn :=c_pf_org_child_rec.admin;
3672: ,l_edli_adm
3673: ,l_summ);
3674: i:=i+1;
3675: END LOOP;
3676: pay_in_utils.set_location(g_debug,l_procedure, 220);
3677: pay_in_xml_utils.gXMLTable.delete;
3678: l_count:=1;
3679: --EPF Contributions Total
3680: pay_in_xml_utils.gXMLTable(l_count).Name := 'c_epf_org_total';
3708: l_count := l_count + 1;
3709: --No of employees in Form 6A
3710: pay_in_xml_utils.gXMLTable(l_count).Name := 'c_emp_no';
3711:
3712: pay_in_utils.set_location(g_debug,l_procedure, 230);
3713:
3714: SELECT COUNT(DISTINCT action_information3)
3715: INTO pay_in_xml_utils.gXMLTable(l_count).Value
3716: FROM pay_action_information
3729: ,p_effective_start_date
3730: ,p_effective_end_date);
3731: FETCH c_rep_name INTO pay_in_xml_utils.gXMLTable(l_count).Value;
3732: CLOSE c_rep_name;
3733: pay_in_utils.set_location(g_debug,l_procedure, 240);
3734: l_count := l_count + 1;
3735: pay_in_xml_utils.multiColumnar('pf_org_sum',pay_in_xml_utils.gXMLTable,l_count,g_xml_data);
3736: /* Ending Starts here*/
3737: l_tag := '';
3738: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
3739: pay_in_xml_utils.gXMLTable.delete;
3740: l_row_count:=null;
3741: END LOOP;
3742: pay_in_utils.set_location(g_debug,l_procedure, 240);
3743: l_tag := '';
3744: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
3745: fnd_file.put_line(fnd_file.log,'XML Created.');
3746:
3965:
3966: BEGIN
3967: g_debug := hr_utility.debug_enabled;
3968: l_procedure := g_package ||'create_form3a_xml';
3969: pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
3970:
3971: pay_in_xml_utils.gXMLTable.DELETE;
3972: --
3973: fnd_file.put_line(fnd_file.log,'Creating the XML...');
3987: l_tag :=pay_in_xml_utils.getTag('c_current_date_in_hh_mm_ss',l_sys_date_time);
3988: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
3989: l_cp_pf_org_id := p_pf_org_id;
3990:
3991: pay_in_utils.set_location(g_debug,l_procedure, 20);
3992:
3993: BEGIN
3994: OPEN c_distinct_org_id(p_pf_org_id
3995: ,p_pf_number
4071: )*/
4072: LOOP
4073: FETCH c_org_id into c_org;
4074: EXIT WHEN c_org_id%NOTFOUND;
4075: pay_in_utils.set_location(g_debug,l_procedure, 30);
4076:
4077: IF g_debug THEN
4078: pay_in_utils.trace('p_effective_start_date ',p_effective_start_date);
4079: pay_in_utils.trace('p_effective_end_date ',p_effective_end_date);
4074: EXIT WHEN c_org_id%NOTFOUND;
4075: pay_in_utils.set_location(g_debug,l_procedure, 30);
4076:
4077: IF g_debug THEN
4078: pay_in_utils.trace('p_effective_start_date ',p_effective_start_date);
4079: pay_in_utils.trace('p_effective_end_date ',p_effective_end_date);
4080: pay_in_utils.trace('p_employee_type ',p_employee_type);
4081: pay_in_utils.trace('c_org.pf_org_id ',c_org.pf_org_id);
4082: END IF;
4075: pay_in_utils.set_location(g_debug,l_procedure, 30);
4076:
4077: IF g_debug THEN
4078: pay_in_utils.trace('p_effective_start_date ',p_effective_start_date);
4079: pay_in_utils.trace('p_effective_end_date ',p_effective_end_date);
4080: pay_in_utils.trace('p_employee_type ',p_employee_type);
4081: pay_in_utils.trace('c_org.pf_org_id ',c_org.pf_org_id);
4082: END IF;
4083:
4076:
4077: IF g_debug THEN
4078: pay_in_utils.trace('p_effective_start_date ',p_effective_start_date);
4079: pay_in_utils.trace('p_effective_end_date ',p_effective_end_date);
4080: pay_in_utils.trace('p_employee_type ',p_employee_type);
4081: pay_in_utils.trace('c_org.pf_org_id ',c_org.pf_org_id);
4082: END IF;
4083:
4084:
4077: IF g_debug THEN
4078: pay_in_utils.trace('p_effective_start_date ',p_effective_start_date);
4079: pay_in_utils.trace('p_effective_end_date ',p_effective_end_date);
4080: pay_in_utils.trace('p_employee_type ',p_employee_type);
4081: pay_in_utils.trace('c_org.pf_org_id ',c_org.pf_org_id);
4082: END IF;
4083:
4084:
4085: l_bg_id:=fnd_profile.value('PER_BUSINESS_GROUP_ID');
4091: ,l_cp_pf_org_id
4092: ,c_org.pf_org_id
4093: ,l_status)
4094: THEN
4095: pay_in_utils.set_location(g_debug,l_procedure, 40);
4096:
4097: IF g_debug THEN
4098: pay_in_utils.trace('Status ',l_status);
4099: pay_in_utils.trace('Cp PF org id ',l_cp_pf_org_id);
4094: THEN
4095: pay_in_utils.set_location(g_debug,l_procedure, 40);
4096:
4097: IF g_debug THEN
4098: pay_in_utils.trace('Status ',l_status);
4099: pay_in_utils.trace('Cp PF org id ',l_cp_pf_org_id);
4100: pay_in_utils.trace('PF org id ',c_org.pf_org_id);
4101: pay_in_utils.trace('PF Num ',c_org.pf_num );
4102: pay_in_utils.trace('p_employee_type ',p_employee_type );
4095: pay_in_utils.set_location(g_debug,l_procedure, 40);
4096:
4097: IF g_debug THEN
4098: pay_in_utils.trace('Status ',l_status);
4099: pay_in_utils.trace('Cp PF org id ',l_cp_pf_org_id);
4100: pay_in_utils.trace('PF org id ',c_org.pf_org_id);
4101: pay_in_utils.trace('PF Num ',c_org.pf_num );
4102: pay_in_utils.trace('p_employee_type ',p_employee_type );
4103: END IF;
4096:
4097: IF g_debug THEN
4098: pay_in_utils.trace('Status ',l_status);
4099: pay_in_utils.trace('Cp PF org id ',l_cp_pf_org_id);
4100: pay_in_utils.trace('PF org id ',c_org.pf_org_id);
4101: pay_in_utils.trace('PF Num ',c_org.pf_num );
4102: pay_in_utils.trace('p_employee_type ',p_employee_type );
4103: END IF;
4104:
4097: IF g_debug THEN
4098: pay_in_utils.trace('Status ',l_status);
4099: pay_in_utils.trace('Cp PF org id ',l_cp_pf_org_id);
4100: pay_in_utils.trace('PF org id ',c_org.pf_org_id);
4101: pay_in_utils.trace('PF Num ',c_org.pf_num );
4102: pay_in_utils.trace('p_employee_type ',p_employee_type );
4103: END IF;
4104:
4105: FOR c_rec IN c_asg_id(c_org.pf_org_id --PF Org ID
4098: pay_in_utils.trace('Status ',l_status);
4099: pay_in_utils.trace('Cp PF org id ',l_cp_pf_org_id);
4100: pay_in_utils.trace('PF org id ',c_org.pf_org_id);
4101: pay_in_utils.trace('PF Num ',c_org.pf_num );
4102: pay_in_utils.trace('p_employee_type ',p_employee_type );
4103: END IF;
4104:
4105: FOR c_rec IN c_asg_id(c_org.pf_org_id --PF Org ID
4106: ,c_org.pf_num --PF Number
4110: ,p_contribution_period
4111: ,l_cp_pf_org_id
4112: ,l_status)
4113: LOOP
4114: pay_in_utils.set_location(g_debug,l_procedure, 50);
4115: OPEN c_name_fath_hus_name(c_org.pf_org_id --PF Org ID
4116: ,c_org.pf_num --PF Number
4117: ,p_effective_start_date
4118: ,p_effective_end_date
4150: l_row_count:=0;
4151: --Individual Month Record Determination
4152: FOR i IN 3..12
4153: LOOP
4154: pay_in_utils.set_location(g_debug,l_procedure, 60);
4155: l_pf_salary_ptd :=0;
4156: l_epf :=0;
4157: l_epf_diff :=0;
4158: l_pension_fund :=0;
4165: ,p_effective_end_date
4166: ,p_contribution_period
4167: ,i)
4168: LOOP
4169: pay_in_utils.set_location(g_debug,l_procedure, 70);
4170: l_pf_salary_ptd :=c_rec_child.action_information7;
4171: l_epf :=c_rec_child.action_information8;
4172: l_epf_diff :=c_rec_child.action_information9;
4173: l_pension_fund :=c_rec_child.action_information10;
4198: ,l_absence
4199: ,l_remarks);
4200:
4201: END LOOP;
4202: pay_in_utils.set_location(g_debug,l_procedure, 80);
4203:
4204: FOR i IN 1..2
4205: LOOP
4206: l_pf_salary_ptd :=0;
4208: l_epf_diff :=0;
4209: l_pension_fund :=0;
4210: l_absence :=0;
4211: l_remarks :=NULL;
4212: pay_in_utils.set_location(g_debug,l_procedure, 90);
4213:
4214: FOR c_rec_child IN c_asg_details(c_org.pf_org_id --PF Org ID
4215: ,c_org.pf_num --PF Number
4216: ,p_effective_start_date
4245: ,l_remarks);
4246:
4247: END LOOP;
4248:
4249: pay_in_utils.set_location(g_debug,l_procedure, 100);
4250: --Voluntary Higher Contr Rate
4251: l_tag :=pay_in_xml_utils.getTag('c_voluntary_rate',to_number(l_voluntary_contribution_rate,999.99));
4252: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
4253: --Employer Representative name
4275: --Total Employee Employer Contr
4276: 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));
4277: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
4278: END LOOP;
4279: pay_in_utils.set_location(g_debug,l_procedure, 110);
4280: --Current Date
4281: l_tag :=pay_in_xml_utils.getTag('c_current_date',to_char(SYSDATE,'DD-Mon-YYYY'));
4282: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
4283: --Starting Year
4296: l_tag :=pay_in_xml_utils.getTag('c_stat_rate',to_number(l_rate,99.99));
4297: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
4298: l_tag := '';
4299: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
4300: pay_in_utils.set_location(g_debug,l_procedure, 120);
4301: END LOOP;
4302: END IF;
4303: END LOOP;
4304: IF c_org_id%ISOPEN THEN
4304: IF c_org_id%ISOPEN THEN
4305: CLOSE c_org_id;
4306: END IF;
4307: --
4308: pay_in_utils.set_location(g_debug,l_procedure, 130);
4309: l_tag := '';
4310: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
4311: fnd_file.put_line(fnd_file.log,'XML Created.');
4312: END create_form3a_xml;
4531:
4532: BEGIN
4533: g_debug := hr_utility.debug_enabled;
4534: l_procedure := g_package ||'create_form7_xml';
4535: pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
4536:
4537: pay_in_xml_utils.gXMLTable.DELETE;
4538:
4539: --
4570: l_bg_id:= fnd_profile.value('PER_BUSINESS_GROUP_ID');
4571:
4572:
4573: fnd_file.put_line(fnd_file.log,'Start of Exempted Org list.');
4574: pay_in_utils.set_location(g_debug,l_procedure, 20);
4575:
4576: FOR c_pf_orglist IN csr_exempted_pf_orglist LOOP
4577: --
4578: -- Get details in sequence for each of the above organization and store it in a PLSQL table . This will be
4578: -- Get details in sequence for each of the above organization and store it in a PLSQL table . This will be
4579: -- used by each record returned by the inner query .i.e for each pension number
4580: -- that is attached to this PF org
4581: g_org_XMLTable.delete;
4582: pay_in_utils.set_location(g_debug,l_procedure, 30);
4583: OPEN csr_pf_org_details(c_pf_orglist.orgid);
4584: FETCH csr_pf_org_details INTO l_reg_name,l_rep_name,l_org_address,l_est_code;
4585: CLOSE csr_pf_org_details;
4586:
4610: FOR c_master_rec IN csr_pension_number(c_pf_orglist.orgid) LOOP
4611: -- Repeat this for all distinct (pension number,assignment id combinations . Rehire cases will not be
4612: -- considered.
4613: -- fnd_file.put_line(fnd_file.log,'Get details for the assignment id.'||c_master_rec.asg_id);
4614: pay_in_utils.set_location(g_debug,l_procedure, 40);
4615: IF (employee_type(c_master_rec.pension_number
4616: ,p_employee_type
4617: ,p_effective_start_date
4618: ,p_effective_end_date
4619: ,p_cp_pf_org_id -- Conc Program parameter
4620: ,c_pf_orglist.orgid
4621: ,p_status))
4622: THEN
4623: pay_in_utils.set_location(g_debug,l_procedure, 50);
4624: fnd_file.put_line(fnd_file.log,'Inside the Employee Type Check');
4625: OPEN csr_not_excluded_employee(c_pf_orglist.orgid
4626: ,p_contribution_period
4627: ,c_master_rec.pension_number);
4628: FETCH csr_not_excluded_employee INTO l_excluded_employee_status;
4629: CLOSE csr_not_excluded_employee;
4630:
4631: IF (l_excluded_employee_status = 'X') THEN
4632: pay_in_utils.set_location(g_debug,l_procedure, 60);
4633: OPEN csr_employee_details(c_master_rec.pension_number,c_pf_orglist.orgid);
4634: FETCH csr_employee_details INTO l_employee_name,l_fath_husb_name,l_hire_date;
4635: CLOSE csr_employee_details;
4636:
4654: FOR c_rec_child IN csr_month_contributions(c_pf_orglist.orgid
4655: ,c_master_rec.pension_number
4656: )
4657: LOOP
4658: pay_in_utils.set_location(g_debug,l_procedure, 70);
4659: l_month := MONTHS_BETWEEN(TRUNC(to_date(c_rec_child.date_earned,'DD-MM-YY'),'MM')
4660: ,p_effective_start_date);
4661: --
4662: -- Add null records in case no records exists
4662: -- Add null records in case no records exists
4663: --
4664: l_cont_type_date_earned := 'DEFAULT';
4665: IF l_cont_type = 'DEFAULT' THEN
4666: l_cont_type :=pay_in_utils.get_scl_segment_on_date(c_rec_child.assignment_id
4667: ,l_bg_id
4668: ,GREATEST(l_hire_date,TO_DATE ('1-Apr-'||SUBSTR(p_contribution_period,1,4),'DD-MM-YY'))
4669: ,'segment12');
4670: IF(l_cont_type = '0')THEN
4672: END IF;
4673: END IF;
4674:
4675: IF l_cont_type_date_earned = 'DEFAULT' THEN
4676: l_cont_type_date_earned :=pay_in_utils.get_scl_segment_on_date(c_rec_child.assignment_id
4677: ,l_bg_id
4678: ,c_rec_child.date_earned
4679: ,'segment12');
4680: IF(l_cont_type_date_earned = '0')THEN
4709: --
4710: END IF;
4711: IF l_row_count = l_month THEN
4712: --
4713: pay_in_utils.set_location(g_debug,l_procedure, 80);
4714: l_month_name:=c_rec_child.date_earned;
4715:
4716: l_row_count:=l_row_count+1;
4717: l_month_pf_wages := TO_NUMBER(c_rec_child.pf_wages);
4832: END IF;-- Excluded employee status end
4833: END IF; -- Employee Type Check End
4834:
4835: END LOOP; -- End each pension number
4836: pay_in_utils.set_location(g_debug,l_procedure, 90);
4837: fnd_file.put_line(fnd_file.log,'XML Created for a PF Org.Moving onto next');
4838: END LOOP; -- End Each Organization
4839:
4840: l_tag := '';
5004: BEGIN
5005:
5006: g_debug := hr_utility.debug_enabled;
5007: l_procedure := g_package ||'esi_employee_type';
5008: pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
5009:
5010: OPEN c_person_id;
5011: FETCH c_person_id INTO l_person_id;
5012: CLOSE c_person_id;
5016: l_reason := NULL;
5017: l_start_date :=to_date('01-'||SUBSTR(p_contribution_period,1,8),'DD-MM-YYYY');
5018: l_end_date :=add_months(to_date('01-'||SUBSTR(p_contribution_period,11),'DD-MM-YYYY'),1)-1;
5019:
5020: pay_in_utils.set_location(g_debug,l_procedure, 20);
5021:
5022: IF g_debug THEN
5023: pay_in_utils.trace('l_start_date ',l_start_date);
5024: pay_in_utils.trace('l_end_date ',l_end_date);
5019:
5020: pay_in_utils.set_location(g_debug,l_procedure, 20);
5021:
5022: IF g_debug THEN
5023: pay_in_utils.trace('l_start_date ',l_start_date);
5024: pay_in_utils.trace('l_end_date ',l_end_date);
5025: END IF;
5026:
5027: --Find the last payroll date
5020: pay_in_utils.set_location(g_debug,l_procedure, 20);
5021:
5022: IF g_debug THEN
5023: pay_in_utils.trace('l_start_date ',l_start_date);
5024: pay_in_utils.trace('l_end_date ',l_end_date);
5025: END IF;
5026:
5027: --Find the last payroll date
5028: OPEN c_last_pay_date;
5034: FETCH c_esi_exemption INTO l_exem_status;
5035: CLOSE c_esi_exemption;
5036:
5037: IF g_debug THEN
5038: pay_in_utils.trace('Exemption status ',l_exem_status);
5039: END IF;
5040:
5041:
5042: --Find date of death.
5050: END IF;
5051: l_reason := NULL;
5052: --Termination check
5053:
5054: pay_in_utils.set_location(g_debug,l_procedure, 30);
5055:
5056: OPEN c_termination_check(l_person_id,l_start_date,l_end_date);
5057: FETCH c_termination_check INTO l_term_date;
5058: CLOSE c_termination_check;
5063: END IF;
5064:
5065:
5066: IF g_debug THEN
5067: pay_in_utils.trace('Reason ',l_reason);
5068: pay_in_utils.trace('Termination ',l_term_date);
5069: pay_in_utils.trace('Sysdate ',p_sysdate);
5070: pay_in_utils.trace('p_esi_coverage ',p_esi_coverage);
5071: END IF;
5064:
5065:
5066: IF g_debug THEN
5067: pay_in_utils.trace('Reason ',l_reason);
5068: pay_in_utils.trace('Termination ',l_term_date);
5069: pay_in_utils.trace('Sysdate ',p_sysdate);
5070: pay_in_utils.trace('p_esi_coverage ',p_esi_coverage);
5071: END IF;
5072:
5065:
5066: IF g_debug THEN
5067: pay_in_utils.trace('Reason ',l_reason);
5068: pay_in_utils.trace('Termination ',l_term_date);
5069: pay_in_utils.trace('Sysdate ',p_sysdate);
5070: pay_in_utils.trace('p_esi_coverage ',p_esi_coverage);
5071: END IF;
5072:
5073: --If sysdate is greater than end date of contribution period then data for all active, terminated
5066: IF g_debug THEN
5067: pay_in_utils.trace('Reason ',l_reason);
5068: pay_in_utils.trace('Termination ',l_term_date);
5069: pay_in_utils.trace('Sysdate ',p_sysdate);
5070: pay_in_utils.trace('p_esi_coverage ',p_esi_coverage);
5071: END IF;
5072:
5073: --If sysdate is greater than end date of contribution period then data for all active, terminated
5074: --and transferred employee should be there.(Provided 3rd Pmt is left blank).
5094:
5095: IF p_esi_coverage = '-1' THEN
5096: l_exem_status:='-1';
5097: END IF;
5098: pay_in_utils.set_location(g_debug,l_procedure, 40);
5099:
5100: --Checking for termination
5101: OPEN c_termination_check(l_person_id,l_start_date,l_end_date);
5102: FETCH c_termination_check INTO l_term_date;
5108: ELSE
5109: RETURN FALSE;
5110: END IF;
5111: END IF;
5112: pay_in_utils.set_location(g_debug,l_procedure, 50);
5113: --Checking for death
5114: OPEN c_death_date(l_start_date,l_end_date);
5115: FETCH c_death_date INTO l_reason;
5116: CLOSE c_death_date;
5121: ELSE
5122: RETURN FALSE;
5123: END IF;
5124: END IF;
5125: pay_in_utils.set_location(g_debug,l_procedure, 60);
5126:
5127: FOR c_rec IN c_transfer_check
5128: LOOP
5129: l_org_count:=l_org_count+1;
5130: END LOOP;
5131: IF l_org_count <2 THEN /*This means there were'nt any changes, i.e the employee was there in the org thru out*/
5132: RETURN FALSE;
5133: END IF;
5134: pay_in_utils.set_location(g_debug,l_procedure, 70);
5135:
5136: IF g_debug THEN
5137: pay_in_utils.trace('l_org_count ',l_org_count);
5138: pay_in_utils.trace('Org ID ',p_org_id);
5133: END IF;
5134: pay_in_utils.set_location(g_debug,l_procedure, 70);
5135:
5136: IF g_debug THEN
5137: pay_in_utils.trace('l_org_count ',l_org_count);
5138: pay_in_utils.trace('Org ID ',p_org_id);
5139: END IF;
5140:
5141:
5134: pay_in_utils.set_location(g_debug,l_procedure, 70);
5135:
5136: IF g_debug THEN
5137: pay_in_utils.trace('l_org_count ',l_org_count);
5138: pay_in_utils.trace('Org ID ',p_org_id);
5139: END IF;
5140:
5141:
5142: IF p_org_id IS NOT NULL THEN
5139: END IF;
5140:
5141:
5142: IF p_org_id IS NOT NULL THEN
5143: pay_in_utils.set_location(g_debug,l_procedure, 80);
5144: --Now checking for transfer cases.
5145: --The following cursor checks for the presence of p_org_id in the archived data
5146: OPEN c_transfer_status(p_org_id);
5147: FETCH c_transfer_status INTO l_reason;
5147: FETCH c_transfer_status INTO l_reason;
5148: CLOSE c_transfer_status;
5149: -- hr_utility.set_location('Checking asg '||p_assignment_id||' for termination',7);
5150: IF l_reason IS NULL THEN -- This means that employee is no longer attached to that p_org_id
5151: pay_in_utils.set_location(g_debug,l_procedure, 90);
5152: IF l_exem_status=p_esi_coverage THEN
5153: RETURN TRUE; -- Hence we can safely return true and process that assignment.
5154: ELSE
5155: RETURN FALSE;
5154: ELSE
5155: RETURN FALSE;
5156: END IF;
5157: ELSE
5158: pay_in_utils.set_location(g_debug,l_procedure, 100);
5159: l_reason := NULL;
5160: l_org_count := 0;
5161: -- hr_utility.set_location('Asg '||p_assignment_id||' not terminated. Checking for org counts',7);
5162: --Here we know that in last archived record employee was attached to p_org_id.
5169: IF l_org_count < 2 THEN
5170: --This means there is only one record and employee is attached to it only.
5171: --Hence he is not a separted employee. So returning false
5172: -- hr_utility.set_location('Asg '||p_assignment_id||' has an org count of '||l_org_count||' and is thus active emp',7);
5173: pay_in_utils.set_location(g_debug,l_procedure, 110);
5174: RETURN FALSE;
5175: ELSE
5176: -- hr_utility.set_location('Asg '||p_assignment_id||' has an org count of '||l_org_count||' and might be transferred',7);
5177: --Employee has multiple records for the last run.
5177: --Employee has multiple records for the last run.
5178: --Now we have to make sure that for which one he is transferred.
5179: --For this we are using the last payroll date and then we shall find the SCL Information.
5180: --Find the last payroll date
5181: pay_in_utils.set_location(g_debug,l_procedure, 120);
5182: OPEN c_last_pay_date;
5183: FETCH c_last_pay_date INTO l_pay_date;
5184: CLOSE c_last_pay_date;
5185:
5189: FETCH c_final_check INTO l_reason;
5190: CLOSE c_final_check ;
5191:
5192: IF l_reason IS NULL AND l_exem_status=p_esi_coverage THEN
5193: pay_in_utils.set_location(g_debug,l_procedure, 130);
5194: --This means that SCL Information no longer has p_org_id.
5195: --Thus the employee has a transfer and we'll return true.
5196: RETURN TRUE ;
5197: ELSIF l_reason IS NOT NULL AND l_exem_status=p_esi_coverage THEN
5194: --This means that SCL Information no longer has p_org_id.
5195: --Thus the employee has a transfer and we'll return true.
5196: RETURN TRUE ;
5197: ELSIF l_reason IS NOT NULL AND l_exem_status=p_esi_coverage THEN
5198: pay_in_utils.set_location(g_debug,l_procedure, 140);
5199: OPEN c_esi_exemption(l_pay_date);
5200: FETCH c_esi_exemption INTO l_exem_status;
5201: CLOSE c_esi_exemption;
5202: IF l_exem_status = 'Y' THEN
5203: RETURN TRUE;
5204: ELSE RETURN FALSE;
5205: END IF;
5206: ELSE
5207: pay_in_utils.set_location(g_debug,l_procedure, 150);
5208: RETURN FALSE;
5209: END IF;
5210: END IF;
5211: END IF;
5549: BEGIN
5550: --
5551: g_debug := hr_utility.debug_enabled;
5552: l_procedure := g_package ||'create_esi_xml';
5553: pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
5554:
5555: pay_in_utils.trace('p_esi_org_id',p_esi_org_id);
5556: pay_in_utils.trace('p_contribution_period',p_contribution_period);
5557: pay_in_utils.trace('p_esi_coverage',p_esi_coverage);
5551: g_debug := hr_utility.debug_enabled;
5552: l_procedure := g_package ||'create_esi_xml';
5553: pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
5554:
5555: pay_in_utils.trace('p_esi_org_id',p_esi_org_id);
5556: pay_in_utils.trace('p_contribution_period',p_contribution_period);
5557: pay_in_utils.trace('p_esi_coverage',p_esi_coverage);
5558: pay_in_utils.trace('p_sysdate',p_sysdate);
5559:
5552: l_procedure := g_package ||'create_esi_xml';
5553: pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
5554:
5555: pay_in_utils.trace('p_esi_org_id',p_esi_org_id);
5556: pay_in_utils.trace('p_contribution_period',p_contribution_period);
5557: pay_in_utils.trace('p_esi_coverage',p_esi_coverage);
5558: pay_in_utils.trace('p_sysdate',p_sysdate);
5559:
5560: l_bg_id:=fnd_profile.value('PER_BUSINESS_GROUP_ID');
5553: pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
5554:
5555: pay_in_utils.trace('p_esi_org_id',p_esi_org_id);
5556: pay_in_utils.trace('p_contribution_period',p_contribution_period);
5557: pay_in_utils.trace('p_esi_coverage',p_esi_coverage);
5558: pay_in_utils.trace('p_sysdate',p_sysdate);
5559:
5560: l_bg_id:=fnd_profile.value('PER_BUSINESS_GROUP_ID');
5561: fnd_file.put_line(fnd_file.log,'Creating the XML...');
5554:
5555: pay_in_utils.trace('p_esi_org_id',p_esi_org_id);
5556: pay_in_utils.trace('p_contribution_period',p_contribution_period);
5557: pay_in_utils.trace('p_esi_coverage',p_esi_coverage);
5558: pay_in_utils.trace('p_sysdate',p_sysdate);
5559:
5560: l_bg_id:=fnd_profile.value('PER_BUSINESS_GROUP_ID');
5561: fnd_file.put_line(fnd_file.log,'Creating the XML...');
5562: dbms_lob.createtemporary(g_xml_data,FALSE,DBMS_LOB.CALL);
5572: l_sys_date_time:=to_char(SYSDATE,'DD-Mon-YYYY HH24:MI:SS');
5573: --System Date:
5574: l_tag :=pay_in_xml_utils.getTag('c_sys_date_in_hh_mm_ss',l_sys_date_time);
5575: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
5576: pay_in_utils.set_location(g_debug,l_procedure, 20);
5577:
5578: FOR c_rec IN c_distinct_org
5579: LOOP
5580:
5629: l_wages_tot := 0;
5630: l_con_tot := 0;
5631:
5632:
5633: pay_in_utils.set_location(g_debug,l_procedure, 30);
5634:
5635: FOR c_chd_asg IN c_asg_id(c_rec.org_id)
5636: LOOP
5637: -- l_asg_id := c_chd_asg.assignment_id;
5637: -- l_asg_id := c_chd_asg.assignment_id;
5638: l_insurance_no := c_chd_asg.insurance_no;
5639:
5640: -- check the assignment
5641: pay_in_utils.set_location(g_debug,l_procedure, 40);
5642: l_esi_coverage := NVL(p_esi_coverage,'-1');
5643: IF esi_employee_type(l_insurance_no,c_rec.org_id,p_contribution_period,p_sysdate,l_esi_coverage)
5644: THEN
5645: pay_in_utils.set_location(g_debug,l_procedure, 50);
5641: pay_in_utils.set_location(g_debug,l_procedure, 40);
5642: l_esi_coverage := NVL(p_esi_coverage,'-1');
5643: IF esi_employee_type(l_insurance_no,c_rec.org_id,p_contribution_period,p_sysdate,l_esi_coverage)
5644: THEN
5645: pay_in_utils.set_location(g_debug,l_procedure, 50);
5646:
5647: IF g_debug THEN
5648: pay_in_utils.trace('ESI Insurance Number',l_insurance_no);
5649: END IF;
5644: THEN
5645: pay_in_utils.set_location(g_debug,l_procedure, 50);
5646:
5647: IF g_debug THEN
5648: pay_in_utils.trace('ESI Insurance Number',l_insurance_no);
5649: END IF;
5650:
5651: l_esi_coverage := NULL;
5652: --Assignment Details
5657:
5658: FOR c_date IN c_max_date(c_rec.org_id,l_insurance_no)
5659: LOOP
5660: IF g_debug THEN
5661: pay_in_utils.trace('ESI Form payroll max date',c_date.maxdate);
5662: pay_in_utils.trace('ESI Exemption',p_esi_coverage);
5663: END IF;
5664:
5665: pay_in_utils.set_location(g_debug,l_procedure, 60);
5658: FOR c_date IN c_max_date(c_rec.org_id,l_insurance_no)
5659: LOOP
5660: IF g_debug THEN
5661: pay_in_utils.trace('ESI Form payroll max date',c_date.maxdate);
5662: pay_in_utils.trace('ESI Exemption',p_esi_coverage);
5663: END IF;
5664:
5665: pay_in_utils.set_location(g_debug,l_procedure, 60);
5666: FOR child_asg IN c_asg_details(l_insurance_no,c_rec.org_id,c_date.maxdate)
5661: pay_in_utils.trace('ESI Form payroll max date',c_date.maxdate);
5662: pay_in_utils.trace('ESI Exemption',p_esi_coverage);
5663: END IF;
5664:
5665: pay_in_utils.set_location(g_debug,l_procedure, 60);
5666: FOR child_asg IN c_asg_details(l_insurance_no,c_rec.org_id,c_date.maxdate)
5667: LOOP
5668: pay_in_utils.set_location(g_debug,l_procedure, 70);
5669: l_full_name := child_asg.full_name;
5664:
5665: pay_in_utils.set_location(g_debug,l_procedure, 60);
5666: FOR child_asg IN c_asg_details(l_insurance_no,c_rec.org_id,c_date.maxdate)
5667: LOOP
5668: pay_in_utils.set_location(g_debug,l_procedure, 70);
5669: l_full_name := child_asg.full_name;
5670: l_actual_salary := l_actual_salary + child_asg.actual_salary;
5671: l_employee_contr := l_employee_contr + child_asg.employee_contr;
5672: l_employer_contr := l_employer_contr + child_asg.employer_contr;
5675: FROM dual;
5676: EXIT;
5677: END LOOP;
5678: END LOOP;
5679: pay_in_utils.set_location(g_debug,l_procedure, 80);
5680: OPEN c_absence(l_insurance_no,c_rec.org_id);
5681: FETCH c_absence INTO l_absence;
5682: CLOSE c_absence;
5683:
5709: END IF;
5710:
5711: l_remarks_dummy := NULL;
5712:
5713: pay_in_utils.set_location(g_debug,l_procedure, 90);
5714:
5715: FOR c_rem IN c_remarks(l_insurance_no,c_rec.org_id)
5716: LOOP
5717: l_payroll_mon := TO_NUMBER(TO_CHAR(c_rem.mon,'MM'));
5793: dbms_lob.writeAppend(l_xml_data, length(l_tag), l_tag);
5794: --Challan Details
5795: l_count := 1;
5796: l_challan_tot := 0;
5797: pay_in_utils.set_location(g_debug,l_procedure, 100);
5798: FOR c_challan_rec IN c_challan(c_rec.org_id)
5799: LOOP
5800:
5801: OPEN c_branch_dtls(c_challan_rec.challan_bank_code, c_challan_rec.challan_branch_code) ;
5897: IF flag = FALSE THEN
5898: dbms_lob.Append(g_xml_data,l_xml_data);
5899: END IF;
5900: dbms_lob.close(l_xml_data);
5901: pay_in_utils.set_location(g_debug,l_procedure, 110);
5902: END LOOP;
5903: l_tag :='';
5904: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
5905: