[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
565: l_slab_5 := 175;
566: l_slab_6 := 200;
567: l_slab_6_2 := 300;
568: --
569: l_bg_id:=fnd_profile.value('PER_BUSINESS_GROUP_ID');
570:
571: fnd_file.put_line(fnd_file.log,'Creating the XML...');
572:
573: dbms_lob.createtemporary(g_xml_data,FALSE,DBMS_LOB.CALL);
1110: AND pai.action_context_type = 'AAP'
1111: AND pai.action_information2 = p_assess_year
1112: AND pai.action_information3 = p_gre_org_id
1113: AND pai.assignment_id = asg.assignment_id
1114: AND asg.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
1115: AND pai.source_id = paa.assignment_action_id
1116: GROUP BY pai.action_information1,pai.action_information17 );
1117:
1118:
1135: WHERE pai.action_information_category = 'IN_EOY_PERSON'
1136: AND pai.action_information3 = p_gre_org_id
1137: AND pai.action_information2 = p_assess_year
1138: AND pai.assignment_id = asg.assignment_id
1139: AND asg.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
1140: AND pai.action_context_type = 'AAP'
1141: AND pai.source_id = paa.assignment_action_id
1142: GROUP BY pai.action_information1,pai.action_information17,source_id;
1143:
1213: --
1214: fnd_file.put_line(fnd_file.log,'Creating XML for Employer Details.');
1215: l_sys_date_time:=TO_CHAR(SYSDATE,'DD-Mon-YYYY HH24:MI:SS');
1216: l_sys_date := TO_CHAR(SYSDATE,'DD-Mon-YYYY');
1217: l_bg_id:=fnd_profile.value('PER_BUSINESS_GROUP_ID');
1218: --System Date
1219: l_tag :=pay_in_xml_utils.getTag('c_current_date_in_hh_mm_ss',l_sys_date_time);
1220: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
1221:
1514: AND paa_asg.action_information1 = p_contribution_period
1515: AND paa_pay.action_information1 = p_contribution_period
1516: AND hou.organization_id=paa_pay.action_information2
1517: AND hou.organization_id=paa_asg.action_information2
1518: AND hou.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
1519: AND paa_asg.action_information13 BETWEEN p_effective_start_date AND p_effective_end_date
1520: ORDER BY paa_pay.action_information8 ASC;
1521:
1522: CURSOR c_assignment_id(p_pf_org_id VARCHAR2
1732: fnd_file.put_line(fnd_file.log,'Started...');
1733: --
1734: fnd_file.put_line(fnd_file.log,'Creating XML for Employer Details.');
1735: l_sys_date_time:=TO_CHAR(SYSDATE,'DD-Mon-YYYY HH24:MI:SS');
1736: l_bg_id:=fnd_profile.value('PER_BUSINESS_GROUP_ID');
1737: --System Date
1738: l_tag :=pay_in_xml_utils.getTag('c_current_date_in_hh_mm_ss',l_sys_date_time);
1739: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
1740:
2195: IS
2196: SELECT DISTINCT person_id
2197: FROM per_people_f
2198: WHERE per_information8 = p_pf_number
2199: AND business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID');
2200:
2201: CURSOR c_termination_check(p_person_id NUMBER)
2202: IS
2203: select '1'
2205: where actual_termination_date between p_effective_start_date and p_effective_end_date
2206: and date_start = (SELECT max(to_date(date_start,'DD-MM-YY'))
2207: FROM per_periods_of_service
2208: WHERE person_id = p_person_id
2209: AND business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
2210: )
2211: and person_id = p_person_id;
2212:
2213: CURSOR c_last_pay_count
2245: ,hr_soft_coding_keyflex hrscf
2246: ,per_people_f pep
2247: WHERE pea.person_id = pep.person_id
2248: AND pep.per_information8 = p_pf_number
2249: AND pep.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
2250: AND pea.soft_coding_keyflex_id=hrscf.soft_coding_keyflex_id
2251: AND hrscf.segment2=p_pf_org_id
2252: AND p_payroll_date BETWEEN TO_DATE(TO_CHAR(pea.effective_start_date,'DD-MM-YY'),'DD-MM-YY')
2253: AND TO_DATE(TO_CHAR(pea.effective_end_date,'DD-MM-YY'),'DD-MM-YY')
2565: AND paa_asg.action_information1=p_contribution_period
2566: AND paa_pay.action_information1=p_contribution_period
2567: AND hou.organization_id=paa_pay.action_information2
2568: AND hou.organization_id=paa_asg.action_information2
2569: AND hou.business_group_id = fnd_profile.VALUE('PER_BUSINESS_GROUP_ID')
2570: AND paa_asg.action_information13 BETWEEN p_effective_start_date AND p_effective_end_date
2571: ORDER BY paa_pay.action_information8 ASC;
2572:
2573: CURSOR c_assignment_id(p_pf_org_id VARCHAR2
2777: fnd_file.put_line(fnd_file.log,'Started...');
2778: --
2779: fnd_file.put_line(fnd_file.log,'Creating XML for Employee Personal Details.');
2780: l_sys_date_time:=to_char(SYSDATE,'DD-Mon-YYYY HH24:MI:SS');
2781: l_bg_id:=fnd_profile.value('PER_BUSINESS_GROUP_ID');
2782: --System Date
2783: l_tag :=pay_in_xml_utils.getTag('c_current_date_in_hh_mm_ss',l_sys_date_time);
2784: dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
2785:
3176: AND paa_pay.action_information7 LIKE DECODE(p_pf_org_id,NULL,'UEX','%') --PF Org Classification
3177: AND paa_asg.action_information2 = paa_pay.action_information2
3178: AND hou.organization_id=paa_pay.action_information2
3179: AND hou.organization_id=paa_asg.action_information2
3180: AND hou.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
3181: ORDER BY paa_asg.action_information14,paa_asg.action_information3 asc;
3182:
3183: CURSOR c_asg_id(p_pf_org_id VARCHAR2
3184: ,p_pf_number VARCHAR2
3365: pay_in_utils.trace('c_org.pf_org_id ',c_org.pf_org_id);
3366: END IF;
3367:
3368:
3369: l_bg_id:=fnd_profile.value('PER_BUSINESS_GROUP_ID');
3370: l_status:=NULL;
3371: IF employee_type(c_org.pf_num
3372: ,p_employee_type
3373: ,p_effective_start_date
3635: ,hr_organization_information hoi
3636: WHERE hou.organization_id = hoi.organization_id
3637: AND hoi.org_information_context ='PER_IN_PF_DF'
3638: AND hou.organization_id like nvl(p_pf_org_id,'%')
3639: AND hou.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
3640: AND EXISTS (SELECT 1
3641: FROM pay_action_information pai
3642: WHERE pai.action_information_category ='IN_PF_PAY'
3643: AND pai.action_information1 = p_contribution_period --Contribution period
3847: --
3848: -- Get the list of all exempted archived PF Organizations
3849: --
3850: p_cp_pf_org_id := p_pf_org_id;
3851: l_bg_id:= fnd_profile.value('PER_BUSINESS_GROUP_ID');
3852:
3853:
3854: fnd_file.put_line(fnd_file.log,'Start of Exempted Org list.');
3855: pay_in_utils.set_location(g_debug,l_procedure, 20);
4219: ,per_people_f pep
4220: ,hr_soft_coding_keyflex hrscf
4221: WHERE pea.person_id = pep.person_id
4222: AND pep.per_information9 = p_insurance_no
4223: AND pep.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
4224: AND pea.soft_coding_keyflex_id=hrscf.soft_coding_keyflex_id
4225: AND hrscf.segment4=p_esi_org_id
4226: AND p_payroll_date BETWEEN to_date(to_char(pea.effective_start_date,'DD-MM-YY'),'DD-MM-YY')
4227: AND to_date(to_char(pea.effective_end_date,'DD-MM-YY'),'DD-MM-YY');
4231: IS
4232: SELECT DISTINCT person_id
4233: FROM per_people_f
4234: WHERE per_information8 = p_insurance_no
4235: AND business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID');
4236:
4237: CURSOR c_termination_check(p_person_id NUMBER
4238: ,p_effective_start_date DATE
4239: ,p_effective_end_date DATE)
4243: where actual_termination_date between p_effective_start_date and p_effective_end_date
4244: and date_start = (SELECT max(to_date(date_start,'DD-MM-YY'))
4245: FROM per_periods_of_service
4246: WHERE person_id = p_person_id
4247: AND business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
4248: )
4249: and person_id = p_person_id;
4250:
4251: --Cursor to find date of death
4256: FROM per_people_f
4257: WHERE person_id = (select distinct person_id
4258: from per_people_f
4259: where per_information9 = p_insurance_no
4260: and business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID'))
4261: AND date_of_death BETWEEN p_start_date AND p_end_date;
4262:
4263: --Find Exemption status
4264: CURSOR c_esi_exemption(p_payroll_date DATE)
4540: AND action_context_type='PA'
4541: AND action_information1=p_contribution_period
4542: AND action_information2 LIKE nvl(p_esi_org_id,'%')
4543: AND hou.organization_id=action_information2
4544: AND hou.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
4545: ORDER BY action_information8 asc;
4546:
4547: --Select the highest payroll action id for an Org
4548: CURSOR c_max_pa_action_id(p_esi_org_id VARCHAR2)
4652: IS
4653: SELECT DISTINCT person_id
4654: FROM per_people_f
4655: WHERE per_information9 = p_insurance_no
4656: AND business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID');
4657: /*Bug 4282074*/
4658: CURSOR c_org_contr_days(p_esi_org_id VARCHAR2
4659: ,p_person_id NUMBER
4660: ,p_contr_start VARCHAR2
4746: pbf.input_value_id = piv.input_value_id
4747: AND piv.element_type_id = pet.element_type_id
4748: AND pbt.balance_type_id = pbf.balance_type_id
4749: AND pbt.balance_name ='ESI Computation Salary'
4750: AND pet.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
4751: AND p_sysdate BETWEEN pet.effective_start_date AND pet.effective_end_date
4752: AND p_sysdate BETWEEN piv.effective_start_date AND piv.effective_end_date
4753: AND p_sysdate BETWEEN pbf.effective_start_date AND pbf.effective_end_date;
4754:
4761: FROM hr_organization_units hou
4762: ,hr_organization_information hoi
4763: WHERE hoi.organization_id = hou.organization_id
4764: AND hoi.org_information_context = 'PER_IN_ESI_BANK_BRANCH_DTLS'
4765: AND hou.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
4766: AND hoi.org_information1 = p_bank_code
4767: AND hoi.org_information2 = p_branch_code ;
4768:
4769:
4837: pay_in_utils.trace('p_contribution_period',p_contribution_period);
4838: pay_in_utils.trace('p_esi_coverage',p_esi_coverage);
4839: pay_in_utils.trace('p_sysdate',p_sysdate);
4840:
4841: l_bg_id:=fnd_profile.value('PER_BUSINESS_GROUP_ID');
4842: fnd_file.put_line(fnd_file.log,'Creating the XML...');
4843: dbms_lob.createtemporary(g_xml_data,FALSE,DBMS_LOB.CALL);
4844: dbms_lob.open(g_xml_data,dbms_lob.lob_readwrite);
4845: --