[Home] [Help]
45: AND assignment_id IN(SELECT DISTINCT pea.assignment_id
46: FROM per_people_f pep -- Reduced cost from 2294 to 69
47: ,per_assignments_f pea-- Done this for bug 4774018
48: WHERE pep.person_id = pea.person_id
49: AND pep.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
50: AND p_number = DECODE(p_report_type,'PF'
51: ,pep.per_information8
52: ,pep.per_information9
53: )
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));
132:
133: pay_in_xml_utils.gXMLTable.delete;
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;
220: --PF Salary _ASG_ORG_PTD
221: pay_in_xml_utils.gXMLTable(l_count).Name := 'c_pf_salary_ptd';
222: pay_in_xml_utils.gXMLTable(l_count).Value :=
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;
299:
300: pay_in_xml_utils.gXMLTable(l_count).Name := 'c_pension_org'||p_row_num;
301: pay_in_xml_utils.gXMLTable(l_count).Value := pay_us_employee_payslip_web.get_format_value(l_bg_id,p_pension_org);
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
352: pay_in_xml_utils.gXMLTable(l_count).Name := 'c_pf_salary_ptd';
353: pay_in_xml_utils.gXMLTable(l_count).Value := pay_us_employee_payslip_web.get_format_value(l_bg_id,nvl(p_pf_salary_ptd,0));
416: AND source_id = NVL(p_pt_org_id, source_id)
417: AND jurisdiction_code = 'MH'
418: AND hou.organization_id = source_id
419: AND TO_DATE(action_information8, 'DD-MM-YYYY') BETWEEN p_period_start AND p_period_end
420: AND hou.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
421: ORDER BY action_information9 ASC;
422:
423: --Select the highest payroll action id for an Org
424: CURSOR c_max_pa_action_id(p_pt_org_id NUMBER
585: l_slab_5 := 175;
586: l_slab_6 := 200;
587: l_slab_6_2 := 300;
588: --
589: l_bg_id:=fnd_profile.value('PER_BUSINESS_GROUP_ID');
590:
591: fnd_file.put_line(fnd_file.log,'Creating the XML...');
592:
593: dbms_lob.createtemporary(g_xml_data,FALSE,DBMS_LOB.CALL);
1190: AND pai.action_context_type = 'AAP'
1191: AND pai.action_information2 = p_assess_year
1192: AND pai.action_information3 = p_gre_org_id
1193: AND pai.assignment_id = asg.assignment_id
1194: AND asg.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
1195: AND pai.source_id = paa.assignment_action_id
1196: GROUP BY pai.action_information1,pai.action_information17 );
1197:
1198:
1215: WHERE pai.action_information_category = 'IN_EOY_PERSON'
1216: AND pai.action_information3 = p_gre_org_id
1217: AND pai.action_information2 = p_assess_year
1218: AND pai.assignment_id = asg.assignment_id
1219: AND asg.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
1220: AND pai.action_context_type = 'AAP'
1221: AND pai.source_id = paa.assignment_action_id
1222: GROUP BY pai.action_information1,pai.action_information17,source_id;
1223:
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');
1297: l_bg_id:=fnd_profile.value('PER_BUSINESS_GROUP_ID');
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:
2095: AND paa_asg.action_information1 = p_contribution_period
2096: AND paa_pay.action_information1 = p_contribution_period
2097: AND hou.organization_id=paa_pay.action_information2
2098: AND hou.organization_id=paa_asg.action_information2
2099: AND hou.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
2100: AND paa_asg.action_information13 BETWEEN p_effective_start_date AND p_effective_end_date
2101: ORDER BY paa_pay.action_information8 ASC;
2102:
2103: CURSOR c_assignment_id(p_pf_org_id VARCHAR2
2313: fnd_file.put_line(fnd_file.log,'Started...');
2314: --
2315: fnd_file.put_line(fnd_file.log,'Creating XML for Employer Details.');
2316: l_sys_date_time:=TO_CHAR(SYSDATE,'DD-Mon-YYYY HH24:MI:SS');
2317: l_bg_id:=fnd_profile.value('PER_BUSINESS_GROUP_ID');
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:
2776: IS
2777: SELECT DISTINCT person_id
2778: FROM per_people_f
2779: WHERE per_information8 = p_pf_number
2780: AND business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID');
2781:
2782: CURSOR c_termination_check(p_person_id NUMBER)
2783: IS
2784: select '1'
2786: where actual_termination_date between p_effective_start_date and p_effective_end_date
2787: and date_start = (SELECT max(to_date(date_start,'DD-MM-YY'))
2788: FROM per_periods_of_service
2789: WHERE person_id = p_person_id
2790: AND business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
2791: )
2792: and person_id = p_person_id;
2793:
2794: CURSOR c_last_pay_count
2826: ,hr_soft_coding_keyflex hrscf
2827: ,per_people_f pep
2828: WHERE pea.person_id = pep.person_id
2829: AND pep.per_information8 = p_pf_number
2830: AND pep.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
2831: AND pea.soft_coding_keyflex_id=hrscf.soft_coding_keyflex_id
2832: AND hrscf.segment2=p_pf_org_id
2833: AND p_payroll_date BETWEEN TO_DATE(TO_CHAR(pea.effective_start_date,'DD-MM-YY'),'DD-MM-YY')
2834: AND TO_DATE(TO_CHAR(pea.effective_end_date,'DD-MM-YY'),'DD-MM-YY')
3153: AND paa_asg.action_information1=p_contribution_period
3154: AND paa_pay.action_information1=p_contribution_period
3155: AND hou.organization_id=paa_pay.action_information2
3156: AND hou.organization_id=paa_asg.action_information2
3157: AND hou.business_group_id = fnd_profile.VALUE('PER_BUSINESS_GROUP_ID')
3158: AND paa_asg.action_information13 BETWEEN p_effective_start_date AND p_effective_end_date
3159: ORDER BY paa_pay.action_information8 ASC;
3160:
3161: CURSOR c_assignment_id(p_pf_org_id VARCHAR2
3367: fnd_file.put_line(fnd_file.log,'Started...');
3368: --
3369: fnd_file.put_line(fnd_file.log,'Creating XML for Employee Personal Details.');
3370: l_sys_date_time:=to_char(SYSDATE,'DD-Mon-YYYY HH24:MI:SS');
3371: l_bg_id:=fnd_profile.value('PER_BUSINESS_GROUP_ID');
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:
3817: AND paa_pay.action_information7 LIKE DECODE(p_pf_org_id,NULL,'UEX','%') --PF Org Classification
3818: AND paa_asg.action_information2 = paa_pay.action_information2
3819: AND hou.organization_id=paa_pay.action_information2
3820: AND hou.organization_id=paa_asg.action_information2
3821: AND hou.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
3822: ORDER BY paa_asg.action_information14,to_number(pf_acc) ASC;
3823:
3824: CURSOR c_asg_id(p_pf_org_id VARCHAR2
3825: ,p_pf_number VARCHAR2
4025: AND paa_pay.action_information7 LIKE DECODE(p_pf_org_id,NULL,'UEX','%') --PF Org Classification
4026: AND paa_asg.action_information2 = paa_pay.action_information2
4027: AND hou.organization_id=paa_pay.action_information2
4028: AND hou.organization_id=paa_asg.action_information2
4029: AND hou.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
4030: ORDER BY paa_asg.action_information14,to_number(pf_acc) asc;
4031: END IF;
4032: CLOSE c_distinct_org_id;
4033: EXCEPTION WHEN INVALID_NUMBER THEN
4056: AND paa_pay.action_information7 LIKE DECODE(p_pf_org_id,NULL,'UEX','%') --PF Org Classification
4057: AND paa_asg.action_information2 = paa_pay.action_information2
4058: AND hou.organization_id=paa_pay.action_information2
4059: AND hou.organization_id=paa_asg.action_information2
4060: AND hou.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
4061: ORDER BY paa_asg.action_information14,pf_num asc;
4062: CLOSE c_distinct_org_id;
4063: END;
4064:
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');
4086: l_status:=NULL;
4087: IF employee_type(c_org.pf_num
4088: ,p_employee_type
4089: ,p_effective_start_date
4354: ,hr_organization_information hoi
4355: WHERE hou.organization_id = hoi.organization_id
4356: AND hoi.org_information_context ='PER_IN_PF_DF'
4357: AND hou.organization_id like nvl(p_pf_org_id,'%')
4358: AND hou.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
4359: AND EXISTS (SELECT 1
4360: FROM pay_action_information pai
4361: WHERE pai.action_information_category ='IN_PF_PAY'
4362: AND pai.action_information1 = p_contribution_period --Contribution period
4566: --
4567: -- Get the list of all exempted archived PF Organizations
4568: --
4569: p_cp_pf_org_id := p_pf_org_id;
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);
4938: ,per_people_f pep
4939: ,hr_soft_coding_keyflex hrscf
4940: WHERE pea.person_id = pep.person_id
4941: AND pep.per_information9 = p_insurance_no
4942: AND pep.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
4943: AND pea.soft_coding_keyflex_id=hrscf.soft_coding_keyflex_id
4944: AND hrscf.segment4=p_esi_org_id
4945: AND p_payroll_date BETWEEN to_date(to_char(pea.effective_start_date,'DD-MM-YY'),'DD-MM-YY')
4946: AND to_date(to_char(pea.effective_end_date,'DD-MM-YY'),'DD-MM-YY');
4950: IS
4951: SELECT DISTINCT person_id
4952: FROM per_people_f
4953: WHERE per_information8 = p_insurance_no
4954: AND business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID');
4955:
4956: CURSOR c_termination_check(p_person_id NUMBER
4957: ,p_effective_start_date DATE
4958: ,p_effective_end_date DATE)
4962: where actual_termination_date between p_effective_start_date and p_effective_end_date
4963: and date_start = (SELECT max(to_date(date_start,'DD-MM-YY'))
4964: FROM per_periods_of_service
4965: WHERE person_id = p_person_id
4966: AND business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
4967: )
4968: and person_id = p_person_id;
4969:
4970: --Cursor to find date of death
4975: FROM per_people_f
4976: WHERE person_id = (select distinct person_id
4977: from per_people_f
4978: where per_information9 = p_insurance_no
4979: and business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID'))
4980: AND date_of_death BETWEEN p_start_date AND p_end_date;
4981:
4982: --Find Exemption status
4983: CURSOR c_esi_exemption(p_payroll_date DATE)
5259: AND action_context_type='PA'
5260: AND action_information1=p_contribution_period
5261: AND action_information2 LIKE nvl(p_esi_org_id,'%')
5262: AND hou.organization_id=action_information2
5263: AND hou.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
5264: ORDER BY action_information8 asc;
5265:
5266: --Select the highest payroll action id for an Org
5267: CURSOR c_max_pa_action_id(p_esi_org_id VARCHAR2)
5371: IS
5372: SELECT DISTINCT person_id
5373: FROM per_people_f
5374: WHERE per_information9 = p_insurance_no
5375: AND business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID');
5376: /*Bug 4282074*/
5377: CURSOR c_org_contr_days(p_esi_org_id VARCHAR2
5378: ,p_person_id NUMBER
5379: ,p_contr_start VARCHAR2
5465: pbf.input_value_id = piv.input_value_id
5466: AND piv.element_type_id = pet.element_type_id
5467: AND pbt.balance_type_id = pbf.balance_type_id
5468: AND pbt.balance_name ='ESI Computation Salary'
5469: AND pet.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
5470: AND p_sysdate BETWEEN pet.effective_start_date AND pet.effective_end_date
5471: AND p_sysdate BETWEEN piv.effective_start_date AND piv.effective_end_date
5472: AND p_sysdate BETWEEN pbf.effective_start_date AND pbf.effective_end_date;
5473:
5480: FROM hr_organization_units hou
5481: ,hr_organization_information hoi
5482: WHERE hoi.organization_id = hou.organization_id
5483: AND hoi.org_information_context = 'PER_IN_ESI_BANK_BRANCH_DTLS'
5484: AND hou.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
5485: AND hoi.org_information1 = p_bank_code
5486: AND hoi.org_information2 = p_branch_code ;
5487:
5488:
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);
5563: dbms_lob.open(g_xml_data,dbms_lob.lob_readwrite);
5564: --