[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT aei_information3
FROM per_assignment_extra_info
WHERE aei_information2 = LPAD(p_mon,2,'0')
AND aei_information1 = p_period
AND information_type = DECODE(p_report_type,'PF','PER_IN_PF_REMARKS','ESI','PER_IN_ESI_REMARKS')
AND assignment_id IN(SELECT DISTINCT pea.assignment_id
FROM per_people_f pep -- Reduced cost from 2294 to 69
,per_assignments_f pea-- Done this for bug 4774018
WHERE pep.person_id = pea.person_id
AND pep.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
AND p_number = DECODE(p_report_type,'PF'
,pep.per_information8
,pep.per_information9
)
);
PROCEDURE insert_record(p_row_num NUMBER
,p_xml_data IN OUT NOCOPY CLOB
,p_epf_org NUMBER DEFAULT null
,p_pension_org NUMBER DEFAULT null
,p_dli_contr NUMBER DEFAULT null
,p_admin_chrg NUMBER DEFAULT null
,p_edli_adm NUMBER DEFAULT null
,p_total NUMBER DEFAULT null
)
IS
l_count NUMBER;
l_procedure := g_package ||'insert_record';
pay_in_xml_utils.gXMLTable.delete;
pay_in_xml_utils.gXMLTable.delete;
END insert_record;
PROCEDURE insert_null_record(p_month_name VARCHAR2
,p_xml_data IN OUT NOCOPY CLOB
,p_pf_salary_ptd VARCHAR2 DEFAULT NULL
,p_epf VARCHAR2 DEFAULT NULL
,p_epf_diff VARCHAR2 DEFAULT NULL
,p_pension_fund VARCHAR2 DEFAULT NULL
,p_absence VARCHAR2 DEFAULT NULL
,p_remarks VARCHAR2 DEFAULT NULL )
IS
l_count NUMBER;
l_procedure := g_package ||'insert_null_record';
END insert_null_record;
PROCEDURE insert_ch_record(p_row_num IN NUMBER
,p_xml_data IN OUT NOCOPY CLOB
,p_pension_org IN NUMBER DEFAULT null
)
IS
l_count NUMBER;
l_procedure := g_package ||'insert_ch_record';
pay_in_xml_utils.gXMLTable.DELETE;
pay_in_xml_utils.gXMLTable.DELETE;
END insert_ch_record;
PROCEDURE insert_null_form7_record(p_month_name VARCHAR2
,p_xml_data IN OUT NOCOPY CLOB
,p_pf_salary_ptd VARCHAR2 DEFAULT NULL
,p_pension_fund VARCHAR2 DEFAULT NULL
,p_absence VARCHAR2 DEFAULT NULL
,p_remarks VARCHAR2 DEFAULT NULL )
IS
l_count NUMBER;
l_procedure := g_package ||'insert_null_form7_record';
END insert_null_form7_record;
SELECT DISTINCT source_id org_id
,action_information9 org_name
FROM pay_action_information
,hr_organization_units hou
WHERE action_information_category = 'IN_PT_ASG'
AND action_context_type = 'AAP'
AND source_id = NVL(p_pt_org_id, source_id)
AND jurisdiction_code = 'MH'
AND hou.organization_id = source_id
AND TO_DATE(action_information8, 'DD-MM-YYYY') BETWEEN p_period_start AND p_period_end
AND hou.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
ORDER BY action_information9 ASC;
SELECT MAX(pai.action_context_id)
FROM pay_action_information pai
,pay_assignment_actions pac
WHERE pai.action_information_category = 'IN_PT_PAY'
AND pai.action_context_type = 'PA'
AND pai.source_id = p_pt_org_id
AND pac.payroll_action_id = pai.action_context_id
AND pac.assignment_action_id IN
( SELECT action_context_id
FROM pay_action_information
WHERE action_information_category = 'IN_PT_ASG'
AND action_context_type = 'AAP'
AND action_information1 = p_year -- Bug 5231500
AND source_id = p_pt_org_id
AND TO_DATE(action_information8,'DD-MM-YY')=
( SELECT MAX(TO_DATE(action_information8,'DD-MM-YY'))
FROM pay_action_information
WHERE action_information_category = 'IN_PT_ASG'
AND action_context_type = 'AAP'
AND action_information1 = p_year -- Bug 5231500
AND TO_DATE(action_information8, 'DD-MM-YYYY') BETWEEN p_period_start AND p_period_end
AND source_id = p_pt_org_id
)
);
SELECT action_information7 employer_code
,action_information4 registered_name
,action_information6 reg_address
,action_information5 rep_name
,action_information9 rep_desg
,action_information8 org_name
,action_information3 bsrtc
FROM pay_action_information
WHERE action_information_category = 'IN_PT_PAY'
AND action_context_type = 'PA'
AND source_id = p_pt_org_id
AND action_context_id = p_payroll_action_id;
SELECT NVL(SUM(fnd_number.canonical_to_number(org_information5)),0) challan_amt
,NVL(SUM(fnd_number.canonical_to_number(org_information6)),0) interest_amt
,NVL(SUM(fnd_number.canonical_to_number(org_information8)),0) excess_amt
FROM hr_organization_information
WHERE organization_id = p_pt_org_id
AND org_information_context = 'PER_IN_PT_CHALLAN_INFO'
AND ADD_MONTHS(TO_DATE('01-'|| org_information1 || SUBSTR(org_information9, 1, 4), 'DD-MM-YYYY'), 3)
BETWEEN p_period_start AND p_period_end;
SELECT COUNT(*) count
,fnd_number.canonical_to_number(pai.action_information5) rate
FROM pay_action_information pai
WHERE pai.jurisdiction_code = 'MH'
AND pai.source_id = p_source_id
AND pai.action_information_category = 'IN_PT_ASG'
AND pai.action_information1 = p_year
AND TO_NUMBER(pai.action_information2) = p_month
AND pai.action_context_type = 'AAP'
AND pai.action_information6 <> 'Yes'
AND pai.action_information_id = (SELECT MAX(action_information_id)
FROM pay_action_information
WHERE action_information1 = p_year
AND TO_NUMBER(action_information2) = p_month
AND assignment_id = pai.assignment_id
AND action_context_type = 'AAP'
AND action_information_category = 'IN_PT_ASG'
AND jurisdiction_code = 'MH'
AND source_id = p_source_id)
AND fnd_number.canonical_to_number(pai.action_information4) BETWEEN p_min_sal and p_max_sal
GROUP BY pai.action_information5
ORDER BY pai.action_information5;
SELECT fnd_number.canonical_to_number(row_low_range_or_name) min_sal,
fnd_number.canonical_to_number(row_high_range) max_sal
FROM pay_user_rows_f
WHERE user_table_id IN (SELECT user_table_id
FROM pay_user_tables
WHERE user_table_name LIKE 'India Professional Tax Rate for MH'
AND legislation_code ='IN')
AND p_period_start BETWEEN effective_start_date and effective_end_date
ORDER BY user_row_id;
SELECT address_line_1,
address_line_2,
address_line_3,
loc_information14,
loc_information15,
hr_general.decode_lookup('IN_STATES',loc_information16),
postal_code,
telephone_number_1,
telephone_number_2
FROM hr_locations
WHERE location_id = p_location_id;
SELECT MAX(pai.action_context_id)
FROM pay_action_information pai
WHERE pai.action_information_category = 'IN_EOY_ORG'
AND pai.Action_information1 = p_gre_org_id
AND pai.action_information3 = p_assess_year
AND pai.action_context_type = 'PA';
SELECT pai.action_information4
, pai.action_information2
, pai.action_information8
, pay_in_reports_pkg.get_location_details(pai.action_information7,'EMPLOYER_ADDRESS1')
, pay_in_reports_pkg.get_location_details(pai.action_information7,'EMPLOYER_ADDRESS2')
, pay_in_reports_pkg.get_location_details(pai.action_information7,'EMPLOYER_ADDRESS3')
, pay_in_reports_pkg.get_location_details(pai.action_information7,'EMPLOYER_ADDRESS4')
, pay_in_reports_pkg.get_location_details(pai.action_information7,'CITY')
, pay_in_reports_pkg.get_location_details(pai.action_information7,'EMPLOYER_STATE')
, pay_in_reports_pkg.get_location_details(pai.action_information7,'POSTAL_CODE')
, pay_in_reports_pkg.get_location_details(pai.action_information7,'TELEPHONE')
, pay_in_reports_pkg.get_location_details(pai.action_information7,'FAX')
, pai.action_information11
, pay_in_reports_pkg.get_location_details(pai.action_information16,'EMPLOYER_ADDRESS1')
, pay_in_reports_pkg.get_location_details(pai.action_information16,'EMPLOYER_ADDRESS2')
, pay_in_reports_pkg.get_location_details(pai.action_information16,'EMPLOYER_ADDRESS3')
, pay_in_reports_pkg.get_location_details(pai.action_information16,'EMPLOYER_ADDRESS4')
, pay_in_reports_pkg.get_location_details(pai.action_information16,'CITY')
, pay_in_reports_pkg.get_location_details(pai.action_information16,'EMPLOYER_STATE')
, pay_in_reports_pkg.get_location_details(pai.action_information16,'POSTAL_CODE')
, pai.action_information17
, pai.action_information18
, pai.action_information13
FROM pay_action_information pai
, pay_payroll_actions ppa
WHERE pai.action_information_category = 'IN_EOY_ORG'
AND pai.action_context_type = 'PA'
AND pai.action_information1 = p_gre_org_id
AND pai.action_information3 = p_assess_year
AND pai.action_context_id = p_action_context_id
AND ppa.action_type='X'
AND ppa.action_status = 'C'
AND ppa.report_type='IN_EOY_ARCHIVE'
AND ppa.report_qualifier = 'IN'
AND ppa.payroll_action_id = pai.action_context_id;
SELECT COUNT(*)
FROM pay_action_information
WHERE action_information_category = 'IN_EOY_PERSON'
AND action_context_type = 'AAP'
AND action_information2 = p_assess_year
AND action_information3 = p_gre_org_id
AND action_context_id IN ( SELECT MAX(action_context_id)
FROM pay_action_information pai
,pay_assignment_actions paa
,per_assignments_f asg
WHERE pai.action_information_category = 'IN_EOY_PERSON'
AND pai.action_context_type = 'AAP'
AND pai.action_information2 = p_assess_year
AND pai.action_information3 = p_gre_org_id
AND pai.assignment_id = asg.assignment_id
AND asg.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
AND pai.source_id = paa.assignment_action_id
GROUP BY pai.action_information1,pai.action_information17 );
SELECT NVL(SUM(fnd_number.canonical_to_number(action_information2)),0)
FROM pay_action_information
WHERE action_information_category = 'IN_EOY_ASG_SAL'
AND action_context_type = 'AAP'
AND action_information1 = p_balance
AND action_context_id = p_action_context_id
AND source_id = p_source_id;
SELECT MAX(pai.action_context_id) action_cont_id
,source_id sour_id
FROM pay_action_information pai
,pay_assignment_actions paa
,per_assignments_f asg
WHERE pai.action_information_category = 'IN_EOY_PERSON'
AND pai.action_information3 = p_gre_org_id
AND pai.action_information2 = p_assess_year
AND pai.assignment_id = asg.assignment_id
AND asg.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
AND pai.action_context_type = 'AAP'
AND pai.source_id = paa.assignment_action_id
GROUP BY pai.action_information1,pai.action_information17,source_id;
pay_in_xml_utils.gXMLTable.DELETE;
SELECT
substr(paa_asg.action_information3 ,instr(paa_asg.action_information3,'/',-1)+1 ) member_id
,count(DISTINCT paa_asg.action_information4) asg_cnt
FROM pay_action_information paa_asg
,pay_action_information paa_pay
,pay_assignment_actions paa
WHERE paa_asg.action_information_category='IN_PF_ASG'
AND paa_pay.action_information_category='IN_PF_PAY'
AND paa_asg.ACTION_CONTEXT_TYPE='AAP'
AND paa_pay.ACTION_CONTEXT_TYPE='PA'
AND paa.assignment_action_id=paa_asg.action_context_id
AND paa.payroll_action_id=paa_pay.action_context_id
AND paa_pay.action_information7 NOT IN ('EXEM')
AND paa_asg.action_information3 IS NOT NULL
AND paa_asg.action_information24=p_contribution_period
AND paa_pay.action_information24=p_contribution_period
AND paa_asg.action_information2=p_pf_org_id
AND paa_pay.action_information2=p_pf_org_id
AND to_number(to_char(to_date(paa_asg.action_information13,'DD/MM/YY'),'MM'))=p_mon_number
GROUP BY substr(paa_asg.action_information3 ,instr(paa_asg.action_information3,'/',-1)+1 )
HAVING count(DISTINCT paa_asg.action_information4) > 1
ORDER BY substr(paa_asg.action_information3 ,instr(paa_asg.action_information3,'/',-1)+1 ) ASC;
SELECT
DISTINCT substr(paa_asg.action_information3 ,instr(paa_asg.action_information3,'/',-1)+1 ) member_id
,paa_asg.action_information4 member_name
FROM pay_action_information paa_asg
,pay_action_information paa_pay
,pay_assignment_actions paa
WHERE paa_asg.action_information_category='IN_PF_ASG'
AND paa_pay.action_information_category='IN_PF_PAY'
AND paa_asg.ACTION_CONTEXT_TYPE='AAP'
AND paa_pay.ACTION_CONTEXT_TYPE='PA'
AND paa.assignment_action_id=paa_asg.action_context_id
AND paa.payroll_action_id=paa_pay.action_context_id
AND paa_pay.action_information7 NOT IN ('EXEM')
AND paa_asg.action_information3 IS NOT NULL
AND paa_asg.action_information24=p_contribution_period
AND paa_pay.action_information24=p_contribution_period
AND paa_asg.action_information2=p_pf_org_id
AND paa_pay.action_information2=p_pf_org_id
AND to_number(to_char(to_date(paa_asg.action_information13,'DD/MM/YY'),'MM'))=p_mon_number
ORDER BY substr(paa_asg.action_information3 ,instr(paa_asg.action_information3,'/',-1)+1 ) ASC;
SELECT
paa_asg.action_information4 member_name
,paa_asg.action_information7 epf_wages
,paa_asg.action_information27 eps_wages
,paa_asg.action_information8 epf_contribution_employee
,paa_asg.action_information10 employer_contr_towards_eps
,paa_asg.action_information9 employer_contr_towards_pf
,paa_asg.action_information11 absence
,paa_asg.action_information5 father_husband_name
,decode(paa_asg.action_information29,'JP_FT','F','S','S',null) contact_type
,to_char(to_date(paa_asg.action_information28,'DD/MM/RRRR'),'DD/MM/RRRR') date_of_birth
,paa_asg.action_information30 gender
,to_char(to_date(paa_asg.action_information16,'DD/MM/RRRR'),'DD/MM/RRRR') hire_date
,to_char(to_date(paa_asg.action_information25,'DD/MM/RRRR'),'DD/MM/RRRR') term_date
,paa_asg.action_information26 term_reason
,to_char(to_date(paa_asg.action_information13,'DD/MM/RRRR'),'DD/MM/RRRR') payroll_date
FROM pay_action_information paa_asg
,pay_action_information paa_pay
,pay_assignment_actions paa
WHERE paa_asg.action_information_category='IN_PF_ASG'
AND paa_pay.action_information_category='IN_PF_PAY'
AND paa_asg.ACTION_CONTEXT_TYPE='AAP'
AND paa_pay.ACTION_CONTEXT_TYPE='PA'
AND paa.assignment_action_id=paa_asg.action_context_id
AND paa.payroll_action_id=paa_pay.action_context_id
AND paa_pay.action_information7 NOT IN ('EXEM')
AND paa_asg.action_information3 IS NOT NULL
AND paa_asg.action_information24=p_contribution_period
AND paa_pay.action_information24=p_contribution_period
AND paa_asg.action_information2=p_pf_org_id
AND paa_pay.action_information2=p_pf_org_id
AND substr(paa_asg.action_information3 ,instr(paa_asg.action_information3,'/',-1)+1 )=p_pf_number
AND paa_asg.action_information4 = p_member_name
AND to_number(to_char(to_date(paa_asg.action_information13,'DD/MM/YY'),'MM'))=p_mon_number
ORDER BY substr(paa_asg.action_information3 ,instr(paa_asg.action_information3,'/',-1)+1 ),fnd_number.canonical_to_number(paa_asg.action_information7) ASC;
SELECT TO_NUMBER(GLOBAL_VALUE) INTO l_eps_age
FROM ff_globals_f where global_name = 'IN_EPS_AGE';
SELECT DISTINCT paa_pay.action_information2 --PF Org Id
,paa_pay.action_information3 --PF Org Reg Name
,paa_pay.action_information5 --Address
,paa_pay.action_information6 --Code
,paa_pay.action_information8 --PF Org Name
FROM pay_action_information paa_asg
,pay_action_information paa_pay
,pay_assignment_actions paa
,hr_organization_units hou
WHERE paa_asg.action_information_category = 'IN_PF_ASG'
AND paa_pay.action_information_category = 'IN_PF_PAY'
AND paa_asg.ACTION_CONTEXT_TYPE = 'AAP'
AND paa_pay.ACTION_CONTEXT_TYPE = 'PA'
AND paa.assignment_action_id = paa_asg.action_context_id
AND paa.payroll_action_id = paa_pay.action_context_id
AND paa_asg.action_information2 LIKE NVL(p_pf_org_id,'%') --PF Organization ID
AND paa_pay.action_information7 LIKE DECODE(p_pf_org_id,NULL,'EXEM','%') --PF Org Class
AND paa_asg.action_information2 = paa_pay.action_information2
AND paa_asg.action_information15 IS NOT NULL
AND paa_asg.action_information1 = p_contribution_period
AND paa_pay.action_information1 = p_contribution_period
AND hou.organization_id=paa_pay.action_information2
AND hou.organization_id=paa_asg.action_information2
AND hou.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
AND paa_asg.action_information13 BETWEEN p_effective_start_date AND p_effective_end_date
ORDER BY paa_pay.action_information8 ASC;
SELECT DISTINCT action_information15,assignment_id
FROM pay_action_information
WHERE action_information_category = 'IN_PF_ASG'
AND action_information2 = p_pf_org_id --PF Organization ID
AND action_information1 = p_contribution_period
AND action_information13 BETWEEN p_effective_start_date AND p_effective_end_date
AND assignment_id is not null
ORDER BY action_information15 ASC;
SELECT fnd_number.canonical_to_number(glb.global_value)
FROM ff_globals_f glb
WHERE glb.global_name = p_name
AND glb.LEGISLATION_CODE ='IN'
AND p_effective_end_date BETWEEN glb.effective_start_date and glb.effective_end_date;
SELECT '1' status
FROM pay_action_information pai
WHERE pai.action_information_category ='IN_PF_ASG'
AND pai.action_information1=p_contribution_period
AND pai.action_information2 = p_pf_org_id
AND pai.action_information15 = p_pension_number
AND NVL(pai.action_information18,'0') = '0'
AND ROWNUM <2;
SELECT
SUM(fnd_number.canonical_to_number(pai_mas.action_information10)) pension
FROM pay_action_information pai_mas
where pai_mas.action_information_category ='IN_PF_ASG'
and pai_mas.action_information1 = p_contribution_period
and pai_mas.action_information2 = p_pf_org_id
and pai_mas.action_information15 = p_pension_number
and pai_mas.action_information_id in (SELECT MAX(pai1.action_information_id)
FROM pay_action_information pai1
WHERE pai1.action_information1 = p_contribution_period
AND pai1.action_information2 = p_pf_org_id
AND pai1.action_information15 = p_pension_number
GROUP BY TRUNC(TO_DATE(pai1.action_information13,'DD-MM-YY'),'MM')
);
SELECT DISTINCT TO_DATE(action_information13,'DD-MM-YYYY') mon
FROM pay_action_information
WHERE action_information_category='IN_PF_ASG'
AND action_information3 =p_pf_number
AND action_information2 =p_pf_org_id --PF Organization ID
AND action_information1 = p_contribution_period -- Bug 5231500
AND action_information13 BETWEEN p_effective_start_date --Payroll Date
AND p_effective_end_date
ORDER BY TO_DATE(action_information13,'DD-MM-YYYY') ASC;
SELECT fnd_number.canonical_to_number(org_information2) mon --Month Number
,SUM(fnd_number.canonical_to_number(org_information5)) pension --Pension Fund Contributions A/c No.10
FROM hr_organization_information
WHERE organization_id = p_pf_org_id
AND ORG_INFORMATION_CONTEXT = 'PER_IN_PF_CHALLAN_INFO'
AND org_information1=TO_CHAR(p_effective_start_date,'YYYY')||'-'||TO_CHAR(p_effective_end_date,'YYYY')
GROUP BY org_information2
ORDER BY org_information2 ASC;
SELECT action_information4 --Full Name
FROM pay_action_information
WHERE action_information_category='IN_PF_ASG'
AND action_information1 = p_contribution_period -- Bug 5231500
AND action_information2 = p_pf_org_id
AND action_information15 = p_pension_number
AND TO_DATE(action_information13,'DD-MM-YY') BETWEEN p_effective_start_date AND p_effective_end_date
ORDER BY TO_DATE(action_information13,'DD-MM-YY') DESC;
SELECT paa_pay.action_information4 rep_name
FROM pay_action_information paa_asg
,pay_action_information paa_pay
,pay_assignment_actions paa
WHERE paa_asg.action_information_category='IN_PF_ASG'
AND paa_pay.action_information_category='IN_PF_PAY'
AND paa_asg.ACTION_CONTEXT_TYPE='AAP'
AND paa_pay.ACTION_CONTEXT_TYPE='PA'
AND paa.assignment_action_id=paa_asg.action_context_id
AND paa.payroll_action_id=paa_pay.action_context_id
AND paa_pay.action_information7 = 'EXEM'
AND paa_asg.action_information1=p_contribution_period
AND paa_pay.action_information1=p_contribution_period
AND paa_pay.action_information2=p_pf_org_id
AND paa_asg.action_information2=p_pf_org_id
AND paa_asg.action_information1=paa_pay.action_information1
AND paa_asg.action_information2=paa_pay.action_information2
AND paa_asg.action_information13 BETWEEN p_effective_start_date AND p_effective_end_date
ORDER BY TO_DATE(paa_asg.action_information13,'DD-MM-YYYY') DESC;
SELECT fnd_number.canonical_to_number(pai.action_information7) pf_wages
,pai.action_information13 date_earned
FROM pay_action_information pai
WHERE pai.action_information_category ='IN_PF_ASG'
AND pai.action_information1 = p_contribution_period
AND pai.action_information2 = p_pf_org_id
AND pai.action_information15 = p_pension_no
AND pai.assignment_id IS NOT NULL
GROUP BY pai.action_information13
,pai.action_information7
,pai.action_information10
,pai.action_information11
,pai.action_information17
,pai.action_information18
,pai.action_information_id
,pai.assignment_id
HAVING pai.action_information_id = (SELECT MAX(pai1.action_information_id)
FROM pay_Action_information pai1
WHERE pai1.action_information_category ='IN_PF_ASG'
AND pai1.action_information1 = p_contribution_period
AND pai1.action_information2 = p_pf_org_id
AND TRUNC(TO_DATE(pai.action_information13,'DD-MM-YY'),'MM') = TRUNC(TO_DATE(pai1.action_information13,'DD-MM-YY'),'MM')
AND pai1.action_information15 = p_pension_no
)
ORDER BY TO_DATE(pai.action_information13,'DD-MM-YY'), pai.action_information_id desc;
pay_in_xml_utils.gXMLTable.DELETE;
pay_in_xml_utils.gXMLTable.delete;
pay_in_xml_utils.gXMLTable.delete;
insert_ch_record(i,g_xml_data);
insert_ch_record(c_pf_org_child_rec.mon
,g_xml_data
,c_pf_org_child_rec.pension);
insert_ch_record(i,g_xml_data);
pay_in_xml_utils.gXMLTable.delete;
pay_in_xml_utils.gXMLTable.DELETE;
SELECT pdf.dis_information1
FROM per_disabilities_f pdf,
per_assignments_f paf
WHERE paf.assignment_id = p_assignment_id
AND paf.person_id = pdf.person_id
AND p_earn_date BETWEEN paf.effective_start_date AND paf.effective_end_date
AND p_earn_date BETWEEN pdf.effective_start_date AND pdf.effective_end_date;
SELECT target.org_information3
FROM per_assignments_f assign,
hr_soft_coding_keyflex scl,
hr_organization_information target
WHERE assign.assignment_id = p_assignment_id
AND p_earn_date BETWEEN ASSIGN.effective_start_date AND ASSIGN.effective_end_date
AND assign.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
AND scl.segment1 = target.organization_id
AND target.org_information_context = 'PER_IN_INCOME_TAX_DF';
SELECT service.date_start
FROM per_assignments_f assign,
per_periods_of_service service
WHERE p_earn_date BETWEEN ASSIGN.effective_start_date AND assign.effective_end_date
AND assign.assignment_id = p_assignment_id
AND service.period_of_service_id (+)= assign.period_of_service_id;
SELECT action_information2
FROM pay_action_information paa
WHERE paa.action_information_category='IN_PF_ASG'
AND paa.action_context_type='AAP'
AND paa.action_information1=to_char(p_effective_start_date,'YYYY')||'-'||to_char(p_effective_end_date,'YYYY')
AND paa.action_information3=p_pf_number
GROUP BY action_information2;
SELECT 1
FROM pay_action_information paa
WHERE paa.action_information_category='IN_PF_ASG'
AND paa.action_context_type='AAP'
AND paa.action_information1=to_char(p_effective_start_date,'YYYY')||'-'||to_char(p_effective_end_date,'YYYY')
AND paa.action_information3=p_pf_number
and paa.action_information2=p_pf_org_id
and TO_DATE(paa.action_information13,'DD-MM-YY')=
(
SELECT MAX(to_date(action_information13,'DD-MM-YY'))
FROM pay_action_information paa
WHERE paa.action_information_category='IN_PF_ASG'
AND paa.action_context_type='AAP'
AND paa.action_information1=to_char(p_effective_start_date,'YYYY')||'-'||to_char(p_effective_end_date,'YYYY')
AND paa.action_information3=p_pf_number
);
SELECT DISTINCT person_id
FROM per_people_f
WHERE per_information8 = p_pf_number
AND business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID');
select '1'
from per_periods_of_service
where actual_termination_date between p_effective_start_date and p_effective_end_date
and date_start = (SELECT max(to_date(date_start,'DD-MM-YY'))
FROM per_periods_of_service
WHERE person_id = p_person_id
AND business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
)
and person_id = p_person_id;
SELECT COUNT(action_information2)
FROM pay_action_information
WHERE action_information_category ='IN_PF_ASG'
AND action_information3=p_pf_number
AND action_context_type='AAP'
AND action_information1=to_char(p_effective_start_date,'YYYY')||'-'||to_char(p_effective_end_date,'YYYY')
AND TO_DATE(action_information13,'DD-MM-YY')=
(
SELECT MAX(TO_DATE(action_information13,'DD-MM-YY'))
FROM pay_action_information paa
WHERE paa.action_information_category='IN_PF_ASG'
AND paa.action_context_type='AAP'
AND paa.action_information1=to_char(p_effective_start_date,'YYYY')||'-'||to_char(p_effective_end_date,'YYYY')
AND paa.action_information3=p_pf_number
);
SELECT MAX(TO_DATE(action_information13,'DD-MM-YY'))
FROM pay_action_information paa
WHERE paa.action_information_category='IN_PF_ASG'
AND paa.action_context_type='AAP'
AND paa.action_information1=to_char(p_effective_start_date,'YYYY')||'-'||to_char(p_effective_end_date,'YYYY')
AND paa.action_information3=p_pf_number;
SELECT 1 -- Modified for bug 4774108
FROM per_assignments_f pea
,hr_soft_coding_keyflex hrscf
,per_people_f pep
WHERE pea.person_id = pep.person_id
AND pep.per_information8 = p_pf_number
AND pep.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
AND pea.soft_coding_keyflex_id=hrscf.soft_coding_keyflex_id
AND hrscf.segment2=p_pf_org_id
AND p_payroll_date BETWEEN TO_DATE(TO_CHAR(pea.effective_start_date,'DD-MM-YY'),'DD-MM-YY')
AND TO_DATE(TO_CHAR(pea.effective_end_date,'DD-MM-YY'),'DD-MM-YY')
AND p_payroll_date BETWEEN TO_DATE(TO_CHAR(pep.effective_start_date,'DD-MM-YY'),'DD-MM-YY')
AND TO_DATE(TO_CHAR(pep.effective_end_date,'DD-MM-YY'),'DD-MM-YY');
SELECT DISTINCT paa_pay.action_information2 --PF Org Id
,paa_pay.action_information3 --PF Org Reg Name
,paa_pay.action_information5 --Address
,paa_pay.action_information6 --Code
,paa_pay.action_information8 --PF Org Name
FROM pay_action_information paa_asg
,pay_action_information paa_pay
,pay_assignment_actions paa
,hr_organization_units hou
WHERE paa_asg.action_information_category='IN_PF_ASG'
AND paa_pay.action_information_category='IN_PF_PAY'
AND paa_asg.ACTION_CONTEXT_TYPE='AAP'
AND paa_pay.ACTION_CONTEXT_TYPE='PA'
AND paa.assignment_action_id=paa_asg.action_context_id
AND paa.payroll_action_id=paa_pay.action_context_id
AND paa_asg.action_information2 LIKE NVL(p_pf_org_id,'%') --PF Organization ID
AND paa_pay.action_information7 LIKE DECODE(p_pf_org_id,null,'UEX','%') --PF Org Class
AND paa_asg.action_information2 = paa_pay.action_information2
AND paa_pay.action_information7 NOT IN ('EXEM')
AND paa_asg.action_information3 IS NOT NULL
AND paa_asg.action_information1=p_contribution_period
AND paa_pay.action_information1=p_contribution_period
AND hou.organization_id=paa_pay.action_information2
AND hou.organization_id=paa_asg.action_information2
AND hou.business_group_id = fnd_profile.VALUE('PER_BUSINESS_GROUP_ID')
AND paa_asg.action_information13 BETWEEN p_effective_start_date AND p_effective_end_date
ORDER BY paa_pay.action_information8 ASC;
SELECT DISTINCT action_information3
,substr(action_information3 ,instr(action_information3,'/',-1)+1 ) pf_acc
FROM pay_action_information
WHERE action_information_category='IN_PF_ASG'
AND action_information2 =p_pf_org_id --PF Organization ID
AND action_information1 =p_contribution_period
AND action_information13 BETWEEN p_effective_start_date --Payroll Date
AND p_effective_end_date
AND action_information3 IS NOT NULL
ORDER BY to_number(pf_acc) ASC;
SELECT SUM(fnd_number.canonical_to_number(action_information7)) pf_ytd --PF Salary
,SUM(fnd_number.canonical_to_number(action_information8)) employee --Total Employee Contr
,SUM(fnd_number.canonical_to_number(action_information9)) employer --Employer Contr towards PF
,SUM(fnd_number.canonical_to_number(action_information10)) pension --Employer Contr towards Pension
FROM pay_action_information
WHERE action_information2 = p_pf_org_id
AND action_information3 = p_pf_number
AND action_information1 = p_contribution_period
AND action_information_id IN(
SELECT MAX(action_information_id)
FROM pay_action_information
WHERE action_information2 = p_pf_org_id
AND action_information3 = p_pf_number
AND action_information1 = p_contribution_period
GROUP BY TO_DATE('01'||substr(action_information13,3),'DD-MM-YYYY'))
AND TO_DATE(action_information13,'DD-MM-YY') BETWEEN p_effective_start_date
AND p_effective_end_date;
SELECT DISTINCT TO_DATE(action_information13,'DD-MM-YYYY') mon
FROM pay_action_information
WHERE action_information_category='IN_PF_ASG'
AND action_information3 =p_pf_number
AND action_information2 =p_pf_org_id --PF Organization ID
AND action_information1 = p_contribution_period -- Bug 5231500
AND action_information13 BETWEEN p_effective_start_date --Payroll Date
AND p_effective_end_date
ORDER BY TO_DATE(action_information13,'DD-MM-YYYY') ASC;
SELECT action_information6 --Voluntary Contribution Rate
,TO_DATE(action_information13,'DD-MM-YYYY')
FROM pay_action_information
WHERE action_information_category='IN_PF_ASG'
AND action_information3 = p_pf_number
AND action_information2 = p_pf_org_id --PF Organization ID
AND action_information1 = p_contribution_period -- Bug 5231500
AND action_information13 BETWEEN p_effective_start_date --Payroll Date
AND p_effective_end_date
ORDER BY TO_DATE(action_information13,'DD-MM-YYYY') DESC;
SELECT count(DISTINCT action_information3)--assignment_id)
FROM pay_action_information
WHERE action_information_category='IN_PF_ASG'
AND to_number(action_information6)>0
AND action_information13 BETWEEN p_effective_start_date
AND p_effective_end_date
AND action_information1 = p_contribution_period -- Bug 5231500
AND action_information2=p_pf_org_id;
SELECT
TO_NUMBER(org_information2) mon --Month Number
,SUM(fnd_number.canonical_to_number(org_information3)+fnd_number.canonical_to_number(org_information4)) epf
,SUM(fnd_number.canonical_to_number(org_information5)) pension --Pension Fund Contributions A/c No.10
,SUM(fnd_number.canonical_to_number(org_information6)) dli --DLI Contribution A/c No.21
,SUM(fnd_number.canonical_to_number(org_information7)) edli --EDLI ADM. Charges A/c No.22
,SUM(fnd_number.canonical_to_number(org_information8)) admin --Adm. Charges A/c No.2
,SUM(fnd_number.canonical_to_number(org_information6)+fnd_number.canonical_to_number(org_information7)+fnd_number.canonical_to_number(org_information8)) agg
FROM hr_organization_information
WHERE organization_id=p_pf_org_id
AND ORG_INFORMATION_CONTEXT='PER_IN_PF_CHALLAN_INFO'
AND org_information1=to_char(p_effective_start_date,'YYYY')||'-'||to_char(p_effective_end_date,'YYYY')
AND org_information2=to_number(TO_CHAR(TO_DATE('01-03-2004','DD-MM-YYYY'),'MM'))+decode(i,0,9,-3+i)
GROUP BY org_information2
ORDER BY org_information2 ASC;
SELECT action_information4 --Full Name
,assignment_id
FROM pay_action_information
WHERE action_information_category='IN_PF_ASG'
AND action_information2 =p_pf_org_id --PF Organization ID
AND action_information3 =p_pf_number --PF Number
AND action_information1 = p_contribution_period -- Bug 5231500
AND to_date(action_information13,'DD-MM-YY') BETWEEN p_effective_start_date AND p_effective_end_date
ORDER BY to_date(action_information13,'DD-MM-YY') DESC;
SELECT paa_pay.action_information4 rep_name
FROM pay_action_information paa_asg
,pay_action_information paa_pay
,pay_assignment_actions paa
WHERE paa_asg.action_information_category ='IN_PF_ASG'
AND paa_pay.action_information_category ='IN_PF_PAY'
AND paa_asg.ACTION_CONTEXT_TYPE ='AAP'
AND paa_pay.ACTION_CONTEXT_TYPE ='PA'
AND paa.assignment_action_id = paa_asg.action_context_id
AND paa.payroll_action_id = paa_pay.action_context_id
AND paa_pay.action_information7 NOT IN ('EXEM')
AND paa_asg.action_information3 IS NOT NULL
AND paa_asg.action_information1 = p_contribution_period
AND paa_pay.action_information1 = p_contribution_period
AND paa_pay.action_information2 = p_pf_org_id
AND paa_asg.action_information2 = p_pf_org_id
AND paa_asg.action_information13 BETWEEN p_effective_start_date AND p_effective_end_date
ORDER BY TO_DATE(paa_asg.action_information13,'DD-MM-YYYY') DESC;
pay_in_xml_utils.gXMLTable.DELETE;
SELECT ROUND(fnd_number.canonical_to_number(GLOBAL_VALUE)*100,2) INTO l_rate
FROM FF_GLOBALS_F
WHERE GLOBAL_NAME ='IN_EMPLOYEE_PF_PERCENT'
AND LEGISLATION_CODE='IN'
AND p_effective_start_date BETWEEN effective_start_date AND p_effective_end_date;
SELECT DISTINCT action_information3
,substr(action_information3 ,instr(action_information3,'/',-1)+1 ) pf_acc
FROM pay_action_information
WHERE action_information_category='IN_PF_ASG'
AND action_information2 = c_rec.action_information2 --PF Organization ID
AND action_information1 = p_contribution_period
AND action_information13 BETWEEN p_effective_start_date --Payroll Date
AND p_effective_end_date
AND action_information3 IS NOT NULL
ORDER BY TO_NUMBER(pf_acc) asc;
SELECT DISTINCT action_information3
,substr(action_information3 ,instr(action_information3,'/',-1)+1 ) pf_acc
FROM pay_action_information
WHERE action_information_category='IN_PF_ASG'
AND action_information2 = c_rec.action_information2 --PF Organization ID
AND action_information1 = p_contribution_period
AND action_information13 BETWEEN p_effective_start_date --Payroll Date
AND p_effective_end_date
AND action_information3 IS NOT NULL
ORDER BY action_information3 asc;
pay_in_xml_utils.gXMLTable.delete;
pay_in_xml_utils.gXMLTable.delete;
insert_record(i
,g_xml_data
,l_epf
,l_pension
,l_dli
,l_admn
,l_edli_adm
,l_summ);
pay_in_xml_utils.gXMLTable.delete;
SELECT COUNT(DISTINCT action_information3)
INTO pay_in_xml_utils.gXMLTable(l_count).Value
FROM pay_action_information
WHERE action_information_category='IN_PF_ASG'
AND action_information1 = p_contribution_period -- Bug 5231500
AND action_information2 = c_rec.action_information2 --PF Organization ID
AND action_information3 IS NOT NULL
AND action_information13 BETWEEN p_effective_start_date --Payroll Date
AND p_effective_end_date;
pay_in_xml_utils.gXMLTable.delete;
SELECT DISTINCT paa_asg.action_information14 --PF Org Name
,paa_asg.action_information3 pf_num --PF Number
,paa_asg.action_information2 pf_org_id --PF Org ID
,substr(paa_asg.action_information3 ,instr(paa_asg.action_information3,'/',-1)+1 ) pf_acc
FROM pay_action_information paa_asg
,pay_action_information paa_pay
,pay_assignment_actions paa
,hr_organization_units hou
WHERE paa_asg.action_information_category='IN_PF_ASG'
AND paa_pay.action_information_category='IN_PF_PAY'
AND paa_asg.ACTION_CONTEXT_TYPE='AAP'
AND paa_pay.ACTION_CONTEXT_TYPE='PA'
AND paa.assignment_action_id=paa_asg.action_context_id
AND paa.payroll_action_id=paa_pay.action_context_id
AND paa_pay.action_information7 NOT IN ('EXEM')
AND paa_asg.action_information3 IS NOT NULL
AND paa_asg.action_information1=p_contribution_period
AND paa_pay.action_information1=p_contribution_period
AND paa_pay.action_information2 LIKE NVL(p_pf_org_id,'%') --PF Organization ID
AND paa_asg.action_information2 LIKE NVL(p_pf_org_id,'%') --PF Organization ID
AND paa_asg.action_information3 LIKE DECODE(p_employee_type,'SPECIFIC',p_pf_number,'%') --PF Number
AND paa_pay.action_information7 LIKE DECODE(p_pf_org_id,NULL,'UEX','%') --PF Org Classification
AND paa_asg.action_information2 = paa_pay.action_information2
AND hou.organization_id=paa_pay.action_information2
AND hou.organization_id=paa_asg.action_information2
AND hou.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
ORDER BY paa_asg.action_information14,to_number(pf_acc) ASC;
SELECT DISTINCT paa_pay.action_information5 --Address
,paa_pay.action_information3 reg --Registered Name
FROM pay_action_information paa_asg
,pay_action_information paa_pay
,pay_assignment_actions paa
WHERE paa_asg.action_information_category='IN_PF_ASG'
AND paa_pay.action_information_category='IN_PF_PAY'
AND paa_asg.ACTION_CONTEXT_TYPE='AAP'
AND paa_pay.ACTION_CONTEXT_TYPE='PA'
AND paa.assignment_action_id=paa_asg.action_context_id
AND paa.payroll_action_id=paa_pay.action_context_id
AND paa_pay.action_information7 NOT IN ('EXEM')
AND paa_asg.action_information3 IS NOT NULL
AND paa_asg.action_information1 = p_contribution_period
AND paa_pay.action_information1 = p_contribution_period
AND paa_pay.action_information2 = p_pf_org_id --PF Organization ID
AND paa_asg.action_information2 = p_pf_org_id --PF Organization ID
AND paa_asg.action_information3 = p_pf_number --PF Number
AND paa_asg.action_information2 LIKE DECODE(p_employee_type,'CURRENT',nvl(p_cp_pf_org_id,'%'),'%')
AND paa_pay.action_information2 LIKE DECODE(p_employee_type,'CURRENT',nvl(p_cp_pf_org_id,'%'),'%')
AND nvl(p_status,-1) LIKE DECODE(p_employee_type,'CURRENT','CURRENT','TRANSFERRED','TRANSFERRED',-1)
AND paa_asg.action_information13 BETWEEN p_effective_start_date AND p_effective_end_date;
SELECT action_information4 --Full Name
,action_information5 --Father/Husband Name
,action_information13 --Payroll Date
,action_information6 --Voluntary Contribution Rate
FROM pay_action_information
WHERE action_information_category='IN_PF_ASG'
AND action_information1 =p_contribution_period --PF Contribution Period
AND action_information2 =p_pf_org_id --PF Organization ID
AND action_information3 =p_pf_number --PF Number
AND TO_DATE(action_information13,'DD-MM-YY') BETWEEN p_effective_start_date AND p_effective_end_date
ORDER BY TO_DATE(action_information13,'DD-MM-YY') DESC;
SELECT paa_asg.action_information7 --PF Salary
,paa_asg.action_information8 --Total Employee Contr
,paa_asg.action_information9 --Employer Contr towards PF
,paa_asg.action_information10 --Employer Contr towards Pension
,paa_asg.action_information11 --Absence
-- ,paa_asg.action_information12 --Remarks
,paa_pay.action_information4 --PF Rep Name
,paa_asg.action_information13 --Payroll Month
,paa_asg.assignment_id --Assignment ID
FROM pay_action_information paa_asg
,pay_action_information paa_pay
,pay_assignment_actions paa
WHERE paa_asg.action_information_category='IN_PF_ASG'
AND paa_pay.action_information_category='IN_PF_PAY'
AND paa_asg.ACTION_CONTEXT_TYPE='AAP'
AND paa_pay.ACTION_CONTEXT_TYPE='PA'
AND paa.assignment_action_id=paa_asg.action_context_id
AND paa.payroll_action_id=paa_pay.action_context_id
AND paa_pay.action_information7 NOT IN ('EXEM')
AND paa_asg.action_information3 IS NOT NULL
AND paa_asg.action_information1=p_contribution_period
AND paa_pay.action_information1=p_contribution_period
AND paa_asg.action_information2=p_pf_org_id
AND paa_asg.action_information3=p_pf_number
AND paa_pay.action_information2=p_pf_org_id
AND to_number(to_char(to_date(paa_asg.action_information13,'DD-MM-YY'),'MM'))=p_mon_number
AND paa_asg.action_information13 BETWEEN p_effective_start_date AND p_effective_end_date
ORDER BY to_date(paa_asg.action_information13,'DD-MM-YYYY'), fnd_number.canonical_to_number(paa_asg.action_information7) ASC;
SELECT SUM(fnd_number.canonical_to_number(action_information7)) pf_ytd --PF Salary
,SUM(fnd_number.canonical_to_number(action_information8)) employee --Total Employee Contr
,SUM(fnd_number.canonical_to_number(action_information9)) employer --Employer Contr towards PF
,SUM(fnd_number.canonical_to_number(action_information10)) pension --Employer Contr towards Pension
,SUM(fnd_number.canonical_to_number(action_information8) + fnd_number.canonical_to_number(action_information9)) total --Total Employee Employer Contr
FROM pay_action_information
WHERE action_information2 = p_pf_org_id
AND action_information3 = p_pf_number
AND action_information1 = p_contribution_period
AND action_information_id IN(
SELECT MAX(action_information_id)
FROM pay_action_information
WHERE action_information2 = p_pf_org_id
AND action_information3 = p_pf_number
AND action_information1 = p_contribution_period
GROUP BY to_date('01'||substr(action_information13,3),'DD-MM-YYYY'))
AND to_date(action_information13,'DD-MM-YY') BETWEEN p_effective_start_date
AND p_effective_end_date;
pay_in_xml_utils.gXMLTable.DELETE;
SELECT DISTINCT paa_asg.action_information14 pf_org --PF Org Name
,paa_asg.action_information3 pf_num --PF Number
,paa_asg.action_information2 pf_org_id --PF Org ID
,substr(paa_asg.action_information3 ,instr(paa_asg.action_information3,'/',-1)+1 ) pf_acc
FROM pay_action_information paa_asg
,pay_action_information paa_pay
,pay_assignment_actions paa
,hr_organization_units hou
WHERE paa_asg.action_information_category='IN_PF_ASG'
AND paa_pay.action_information_category='IN_PF_PAY'
AND paa_asg.ACTION_CONTEXT_TYPE='AAP'
AND paa_pay.ACTION_CONTEXT_TYPE='PA'
AND paa.assignment_action_id=paa_asg.action_context_id
AND paa.payroll_action_id=paa_pay.action_context_id
AND paa_pay.action_information7 NOT IN ('EXEM')
AND paa_asg.action_information3 IS NOT NULL
AND paa_asg.action_information1=p_contribution_period
AND paa_pay.action_information1=p_contribution_period
AND paa_pay.action_information2 LIKE NVL(p_pf_org_id,'%') --PF Organization ID
AND paa_asg.action_information2 LIKE NVL(p_pf_org_id,'%') --PF Organization ID
AND paa_asg.action_information3 LIKE DECODE(p_employee_type,'SPECIFIC',p_pf_number,'%') --PF Number
AND paa_pay.action_information7 LIKE DECODE(p_pf_org_id,NULL,'UEX','%') --PF Org Classification
AND paa_asg.action_information2 = paa_pay.action_information2
AND hou.organization_id=paa_pay.action_information2
AND hou.organization_id=paa_asg.action_information2
AND hou.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
ORDER BY paa_asg.action_information14,to_number(pf_acc) asc;
SELECT DISTINCT paa_asg.action_information14 pf_org --PF Org Name
,paa_asg.action_information3 pf_num --PF Number
,paa_asg.action_information2 pf_org_id --PF Org ID
,substr(paa_asg.action_information3 ,instr(paa_asg.action_information3,'/',-1)+1 ) pf_acc
FROM pay_action_information paa_asg
,pay_action_information paa_pay
,pay_assignment_actions paa
,hr_organization_units hou
WHERE paa_asg.action_information_category='IN_PF_ASG'
AND paa_pay.action_information_category='IN_PF_PAY'
AND paa_asg.ACTION_CONTEXT_TYPE='AAP'
AND paa_pay.ACTION_CONTEXT_TYPE='PA'
AND paa.assignment_action_id=paa_asg.action_context_id
AND paa.payroll_action_id=paa_pay.action_context_id
AND paa_pay.action_information7 NOT IN ('EXEM')
AND paa_asg.action_information3 IS NOT NULL
AND paa_asg.action_information1=p_contribution_period
AND paa_pay.action_information1=p_contribution_period
AND paa_pay.action_information2 LIKE NVL(p_pf_org_id,'%') --PF Organization ID
AND paa_asg.action_information2 LIKE NVL(p_pf_org_id,'%') --PF Organization ID
AND paa_asg.action_information3 LIKE DECODE(p_employee_type,'SPECIFIC',p_pf_number,'%') --PF Number
AND paa_pay.action_information7 LIKE DECODE(p_pf_org_id,NULL,'UEX','%') --PF Org Classification
AND paa_asg.action_information2 = paa_pay.action_information2
AND hou.organization_id=paa_pay.action_information2
AND hou.organization_id=paa_asg.action_information2
AND hou.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
ORDER BY paa_asg.action_information14,pf_num asc;
insert_null_record(to_char(add_months(to_date('01-12-2003','DD-MM-YYYY'),i),'Mon')
,g_xml_data
,l_pf_salary_ptd
,l_epf
,l_epf_diff
,l_pension_fund
,l_absence
,l_remarks);
insert_null_record(TO_CHAR(ADD_MONTHS(TO_DATE('01-12-2003','DD-MM-YYYY'),i),'Mon')
,g_xml_data
,l_pf_salary_ptd
,l_epf
,l_epf_diff
,l_pension_fund
,l_absence
,l_remarks);
SELECT ROUND(fnd_number.canonical_to_number(GLOBAL_VALUE)*100,2) INTO l_rate
FROM FF_GLOBALS_F
WHERE GLOBAL_NAME ='IN_EMPLOYEE_PF_PERCENT'
AND LEGISLATION_CODE='IN'
AND p_effective_start_date BETWEEN effective_start_date AND p_effective_end_date;
SELECT hou.organization_id orgid
FROM hr_all_organization_units hou
,hr_organization_information hoi
WHERE hou.organization_id = hoi.organization_id
AND hoi.org_information_context ='PER_IN_PF_DF'
AND hou.organization_id like nvl(p_pf_org_id,'%')
AND hou.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
AND EXISTS (SELECT 1
FROM pay_action_information pai
WHERE pai.action_information_category ='IN_PF_PAY'
AND pai.action_information1 = p_contribution_period --Contribution period
AND pai.action_information2 = hou.organization_id -- Org ID
AND pai.action_information7 = 'EXEM' -- PF Org Classification
AND ROWNUM <2)
ORDER BY hou.name;
SELECT paa_pay.action_information3 Registered_Name
,paa_pay.action_information4 Rep_name
,paa_pay.action_information5 Address
,paa_pay.action_information6 Code
FROM pay_action_information paa_asg
,pay_action_information paa_pay
,pay_assignment_actions paa
WHERE paa_asg.action_information_category='IN_PF_ASG'
AND paa_pay.action_information_category='IN_PF_PAY'
AND paa_asg.action_context_type='AAP'
AND paa_pay.action_context_type='PA'
AND paa.assignment_action_id=paa_asg.action_context_id
AND paa.payroll_action_id=paa_pay.action_context_id
AND paa_pay.action_information7 = 'EXEM'
AND paa_asg.action_information1=p_contribution_period
AND paa_pay.action_information1=p_contribution_period
AND paa_pay.action_information2=p_pf_org_id
AND paa_asg.action_information2=p_pf_org_id
AND paa_asg.action_information1=paa_pay.action_information1
AND paa_asg.action_information2=paa_pay.action_information2
AND paa_asg.action_information13 BETWEEN p_effective_start_date AND p_effective_end_date
ORDER BY TO_DATE(paa_asg.action_information13,'DD-MM-YYYY') DESC;
SELECT DISTINCT pai.action_information15 pension_number
FROM pay_action_information pai
WHERE pai.action_information_category ='IN_PF_ASG'
AND pai.action_information1 = p_contribution_period
AND pai.action_information2 = l_pf_org_id
AND pai.action_information15 IS NOT NULL
AND pai.action_information15 LIKE NVL(p_pension_number,'%')
ORDER BY pai.action_information15;
SELECT 'X' status
FROM pay_action_information pai
WHERE pai.action_information_category ='IN_PF_ASG'
AND pai.action_information1=p_contribution_period
AND pai.action_information2 = p_pf_org_id
AND pai.action_information15 = p_pension_number
AND NVL(pai.action_information18,'0') = '0'
AND ROWNUM <2;
SELECT pai.action_information4 Emp_name
,pai.action_information5 Fath_husb_name
,pai.action_information16 Hire_date
FROM pay_action_information pai
WHERE pai.action_information_category ='IN_PF_ASG'
AND pai.action_information1 = p_contribution_period
AND pai.action_information2 = p_pf_org_id
AND pai.action_information15 = l_pension_number
ORDER BY TO_DATE(action_information13,'DD-MM-YY') DESC;
SELECT pai.action_information7 pf_wages
,pai.action_information10 pension
,pai.action_information11 absence
,pai.action_information13 date_earned
,pai.action_information17 Contribution_sal
,pai.action_information18 Excluded_employee_status
,pai.action_information_id action_information_id
,pai.assignment_id assignment_id
FROM pay_action_information pai
WHERE pai.action_information_category ='IN_PF_ASG'
AND pai.action_information1 = p_contribution_period
AND pai.action_information2 = p_pf_org_id
AND pai.action_information15 = p_pension_number
AND pai.assignment_id IS NOT NULL
GROUP BY pai.action_information13
,pai.action_information7
,pai.action_information10
,pai.action_information11
,pai.action_information17
,pai.action_information18
,pai.action_information_id
,pai.assignment_id
HAVING pai.action_information_id = (SELECT MAX(pai1.action_information_id)
FROM pay_Action_information pai1
WHERE pai1.action_information_category ='IN_PF_ASG'
AND pai1.action_information1 = p_contribution_period
AND pai1.action_information2 = p_pf_org_id
AND TRUNC(TO_DATE(pai.action_information13,'DD-MM-YY'),'MM') = TRUNC(TO_DATE(pai1.action_information13,'DD-MM-YY'),'MM')
AND pai1.action_information15 = p_pension_number
)
ORDER BY TO_DATE(pai.action_information13,'DD-MM-YY'), pai.action_information_id desc;
SELECT fnd_number.canonical_to_number(glb.global_value)
FROM ff_globals_f glb
WHERE glb.global_name = p_name
AND glb.LEGISLATION_CODE ='IN'
AND p_effective_end_date BETWEEN glb.effective_start_date and glb.effective_end_date;
pay_in_xml_utils.gXMLTable.DELETE;
g_org_XMLTable.delete;
insert_null_form7_record(to_char(add_months(to_date('01-02-2004','DD-MM-YYYY'),i),'Mon'),g_xml_data);
insert_null_form7_record(to_char(to_date(l_month_name,'DD-MM-YYYY'),'Mon')
,g_xml_data
,l_month_pf_wages
,c_rec_child.pension
,c_rec_child.absence
,get_eit_remarks(c_master_rec.pension_number,'PF',p_contribution_period,l_mon)
);
insert_null_form7_record(to_char(add_months(to_date(l_month_name,'DD-MM-YYYY'),i),'Mon')
,g_xml_data);
SELECT action_information2
FROM pay_action_information paa
WHERE paa.action_information_category='IN_ESI_ASG'
AND paa.action_context_type='AAP'
AND paa.action_information1=p_contribution_period
AND paa.action_information3=p_insurance_no
GROUP BY action_information2;
SELECT 1
FROM pay_action_information paa
WHERE paa.action_information_category='IN_ESI_ASG'
AND paa.action_context_type='AAP'
AND paa.action_information1=p_contribution_period
AND paa.action_information3=p_insurance_no
and paa.action_information2=p_esi_org_id
and to_date(paa.action_information11,'DD-MM-YY')=
(
SELECT MAX(to_date(action_information11,'DD-MM-YY'))
FROM pay_action_information paa
WHERE paa.action_information_category='IN_ESI_ASG'
AND paa.action_context_type='AAP'
AND paa.action_information1=p_contribution_period
AND paa.action_information3=p_insurance_no
);
SELECT count(action_information2)
FROM pay_action_information
WHERE action_information_category ='IN_ESI_ASG'
AND action_information3=p_insurance_no
AND action_context_type='AAP'
AND action_information1=p_contribution_period
AND to_date(action_information11,'DD-MM-YY')=
(
SELECT MAX(to_date(action_information11,'DD-MM-YY'))
FROM pay_action_information paa
WHERE paa.action_information_category='IN_ESI_ASG'
AND paa.action_context_type='AAP'
AND paa.action_information1=p_contribution_period
AND paa.action_information3=p_insurance_no
);
SELECT MAX(to_date(action_information11,'DD-MM-YY'))
FROM pay_action_information paa
WHERE paa.action_information_category='IN_ESI_ASG'
AND paa.action_context_type='AAP'
AND paa.action_information1=p_contribution_period
AND paa.action_information3=p_insurance_no;
SELECT 1
FROM per_assignments_f pea--Modified for bug 4774108
,per_people_f pep
,hr_soft_coding_keyflex hrscf
WHERE pea.person_id = pep.person_id
AND pep.per_information9 = p_insurance_no
AND pep.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
AND pea.soft_coding_keyflex_id=hrscf.soft_coding_keyflex_id
AND hrscf.segment4=p_esi_org_id
AND p_payroll_date BETWEEN to_date(to_char(pea.effective_start_date,'DD-MM-YY'),'DD-MM-YY')
AND to_date(to_char(pea.effective_end_date,'DD-MM-YY'),'DD-MM-YY');
SELECT DISTINCT person_id
FROM per_people_f
WHERE per_information8 = p_insurance_no
AND business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID');
select nvl(actual_termination_date,to_date('31-12-4712','DD-MM-YYYY'))
from per_periods_of_service
where actual_termination_date between p_effective_start_date and p_effective_end_date
and date_start = (SELECT max(to_date(date_start,'DD-MM-YY'))
FROM per_periods_of_service
WHERE person_id = p_person_id
AND business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
)
and person_id = p_person_id;
SELECT '1'
FROM per_people_f
WHERE person_id = (select distinct person_id
from per_people_f
where per_information9 = p_insurance_no
and business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID'))
AND date_of_death BETWEEN p_start_date AND p_end_date;
SELECT SUBSTR(action_information9,1,1)
FROM pay_action_information
WHERE action_information_category='IN_ESI_ASG'
AND action_information3=p_insurance_no
AND action_information2=p_org_id
AND action_information1=p_contribution_period -- Bug 5231500
AND to_date(action_information11,'DD-MM-YY')=p_payroll_date;
SELECT DISTINCT action_information2 org_id
,action_information8 org_name
FROM pay_action_information
,hr_organization_units hou
WHERE action_information_category='IN_ESI_PAY'
AND action_context_type='PA'
AND action_information1=p_contribution_period
AND action_information2 LIKE nvl(p_esi_org_id,'%')
AND hou.organization_id=action_information2
AND hou.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
ORDER BY action_information8 asc;
SELECT max(pai.action_context_id)
FROM pay_action_information pai
,pay_assignment_actions pac
WHERE pai.action_information_category='IN_ESI_PAY'
AND pai.action_context_type='PA'
AND pai.action_information1=p_contribution_period
AND pai.action_information2=p_esi_org_id
AND pac.payroll_action_id=pai.action_context_id
AND pac.assignment_action_id in
( SELECT action_context_id
FROM pay_action_information
WHERE action_information_category='IN_ESI_ASG'
AND action_context_type='AAP'
AND action_information1=p_contribution_period
AND action_information2=p_esi_org_id
AND action_information11=
( SELECT max(to_date(action_information11,'DD-MM-YY'))
FROM pay_action_information
WHERE action_information_category='IN_ESI_ASG'
AND action_context_type='AAP'
AND action_information1=p_contribution_period
AND action_information2=p_esi_org_id
)
);
SELECT action_information6 employer_code
,action_information3 registered_name
,action_information5 reg_address
,action_information4 rep_name
,action_information9 rep_desg
,action_information7 rep_addr
,action_information8 org_name
FROM pay_action_information
WHERE action_information_category='IN_ESI_PAY'
AND action_context_type = 'PA'
AND action_information1 = p_contribution_period
AND action_information2 = p_esi_org_id
AND action_context_id = p_payroll_action_id;
SELECT DISTINCT action_information3 insurance_no
FROM pay_action_information
WHERE action_information_category='IN_ESI_ASG'
AND action_context_type='AAP'
AND action_information1=p_contribution_period
AND action_information2=p_esi_org_id
ORDER BY TO_NUMBER(action_information3) ASC;
SELECT max(to_date(action_information11,'DD-MM-YY')) maxdate
FROM pay_action_information
WHERE action_information_category='IN_ESI_ASG'
AND action_context_type='AAP'
AND action_information1=p_contribution_period
AND action_information2=p_org_id
AND action_information3=p_insurance_no
GROUP BY assignment_id
ORDER BY 1;
SELECT action_information4 full_name
,fnd_number.canonical_to_number(action_information6) actual_salary
,fnd_number.canonical_to_number(action_information7) employee_contr
,fnd_number.canonical_to_number(action_information8) employer_contr
,action_information9 esi_coverage
,action_information11 payroll_date
FROM pay_action_information
WHERE action_information_category='IN_ESI_ASG'
AND action_context_type='AAP'
AND action_information1=p_contribution_period
AND action_information2=p_esi_org_id
AND action_information3=p_insurance_no
AND to_date(action_information11,'DD-MM-YY')=p_payroll_date
ORDER BY to_date(action_information11,'DD-MM-YYYY') DESC ,fnd_number.canonical_to_number(action_information6)DESC;
SELECT sum(nvl(action_information5,0)) absence
FROM pay_action_information
WHERE action_information_category='IN_ESI_ASG'
AND action_context_type='AAP'
AND action_information1=p_contribution_period
AND action_information2=p_esi_org_id
AND action_information3=p_insurance_no;
SELECT DISTINCT person_id
FROM per_people_f
WHERE per_information9 = p_insurance_no
AND business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID');
SELECT TO_NUMBER
(
LEAST
(NVL(pps.actual_termination_date,TO_DATE('31-12-4712','DD-MM-YYYY')),(ADD_MONTHS(TO_DATE('01-'||p_contr_end,'DD-MM-YYYY'),1)-1)
)
-
GREATEST
( pps.date_start,TO_DATE('01-'||p_contr_start,'DD-MM-YYYY'))
) + 1 days
FROM per_periods_of_service pps
WHERE pps.period_of_service_id IN
(SELECT asg.period_of_service_id
FROM hr_organization_units hoi
,hr_soft_coding_keyflex scf
,per_all_assignments_f asg
WHERE asg.person_id = p_person_id
AND asg.soft_coding_keyflex_id = scf.soft_coding_keyflex_id
AND hoi.organization_id = scf.segment4
AND hoi.organization_id = p_esi_org_id
AND asg.primary_flag = 'Y' );
SELECT DISTINCT TO_DATE(action_information11,'DD-MM-YYYY') mon
FROM pay_action_information
WHERE action_information_category='IN_ESI_ASG'
AND action_context_type='AAP'
AND action_information1=p_contribution_period
AND action_information2=p_esi_org_id
AND action_information3=p_insurance_no
ORDER BY TO_DATE(action_information11,'DD-MM-YYYY') ASC;
SELECT fnd_date.CANONICAL_TO_DATE(org_information2) challan_date
,fnd_number.canonical_to_number(org_information3) challan_amt
,org_information5 challan_bank_code
,org_information6 challan_branch_code
,hr_general.decode_lookup('IN_ESI_BANKS',org_information5) challan_bank
,hr_general.decode_lookup('IN_CALENDAR_MONTH',org_information7) month
FROM hr_organization_information
WHERE organization_id=p_esi_org_id
AND ORG_INFORMATION_CONTEXT='PER_IN_ESI_CHALLAN_INFO'
AND hr_general.decode_lookup('IN_ESI_CONTRIB_PERIOD',ORG_INFORMATION1)=p_contribution_period
ORDER BY fnd_date.CANONICAL_TO_DATE(org_information2) ASC;
SELECT
TO_NUMBER(org_information1) declaration_forms
,TO_NUMBER(org_information2) tic
,TO_NUMBER(org_information3) pic_received
,TO_NUMBER(org_information4) pic_distributed
,TO_NUMBER(org_information5) accidents_reported
,TO_NUMBER(org_information6) direct_covered_employees
,org_information14 direct_covered_wages
,TO_NUMBER(org_information7) direct_not_covered_employees
,org_information8 direct_not_covered_wages
,TO_NUMBER(org_information9) immediate_employer_covered
,org_information10 wages_immediate_emplr_covered
,TO_NUMBER(org_information11) immediate_emplr_not_covered
,org_information12 wages_immd_emplr_not_covered
FROM hr_organization_information
WHERE organization_id = p_esi_org_id
AND org_information_context = 'PER_IN_ESI_FORM5'
AND hr_general.decode_lookup('IN_ESI_CONTRIB_PERIOD',org_information13) = p_contribution_period;
SELECT
NVL(pet.reporting_name, pet.element_name) element_name
FROM
pay_element_types_f pet,
pay_input_values_f piv,
pay_balance_feeds_f pbf,
pay_balance_types pbt
WHERE
pbf.input_value_id = piv.input_value_id
AND piv.element_type_id = pet.element_type_id
AND pbt.balance_type_id = pbf.balance_type_id
AND pbt.balance_name ='ESI Computation Salary'
AND pet.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
AND p_sysdate BETWEEN pet.effective_start_date AND pet.effective_end_date
AND p_sysdate BETWEEN piv.effective_start_date AND piv.effective_end_date
AND p_sysdate BETWEEN pbf.effective_start_date AND pbf.effective_end_date;
SELECT hoi.org_information3 branch_name
,hoi.org_information4 branch_add
FROM hr_organization_units hou
,hr_organization_information hoi
WHERE hoi.organization_id = hou.organization_id
AND hoi.org_information_context = 'PER_IN_ESI_BANK_BRANCH_DTLS'
AND hou.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
AND hoi.org_information1 = p_bank_code
AND hoi.org_information2 = p_branch_code ;
SELECT decode(child_asg.esi_coverage,'Yes','No','No','Yes')
INTO l_esi_coverage
FROM dual;