[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT SUBSTR(legislative_parameters||' ',
INSTR(legislative_parameters||' ',p_token||'=')+(LENGTH(p_token||'=')),
INSTR(legislative_parameters||' ',' ',
INSTR(legislative_parameters||' ',p_token||'='))
- (INSTR(legislative_parameters||' ',p_token||'=')+LENGTH(p_token||'='))),
business_group_id
FROM pay_payroll_actions
WHERE payroll_action_id = p_pact_id;
SELECT 1
FROM pay_action_information pai
,pay_payroll_actions ppa
WHERE pai.action_information_category = 'IN_PF_BUSINESS_NUMBER'
AND pai.action_context_type = 'PA'
AND pai.action_information2 = g_arc_ref_no
AND pai.action_context_id = ppa.payroll_action_id
AND ppa.action_type = 'X'
AND ppa.action_status = 'C'
AND ppa.payroll_action_id <> p_payroll_action_id
AND ppa.report_type ='IN_PF_ARCHIVE'
AND ppa.business_group_id = g_bg_id;
SELECT
ppf.per_information8 pf_no, -- PF Number
ppf.per_information15 nssn, -- NSSN
pps.date_start , -- Hire Date
'D', -- Employee Type
pps.actual_termination_date term_date, -- Termination Date
pps.pds_information1 report, -- Termination Reason(Print)
pps.pds_information2 efile, -- Termination Reason(EFile)
scl.segment12 eps -- EPS on higher employer wages
FROM per_people_f ppf,
per_person_types ppt,
per_assignments_f paf,
per_periods_of_service pps,
hr_soft_coding_keyflex scl
WHERE paf.person_id = ppf.person_id
AND paf.period_of_service_id = pps.period_of_service_id
AND ppf.person_type_id = ppt.person_type_id
AND paf.assignment_id = p_assignment_id
AND scl.segment2 = p_pf_org
AND scl.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
AND scl.enabled_flag = 'Y'
AND (to_char(paf.effective_start_date,'Month-YYYY')=to_char(g_end_date,'Month-YYYY')
OR to_char(paf.effective_end_date,'Month-YYYY')=to_char(g_end_date,'Month-YYYY')
OR g_end_date between paf.effective_start_date and paf.effective_end_date)
AND paf.effective_end_date between ppf.effective_start_date and ppf.effective_end_date;
SELECT org_information3 classification
FROM hr_organization_information hr_pf_org
WHERE org_information_context = 'PER_IN_PF_DF'
AND hr_pf_org.organization_id = p_pf_org ;
SELECT pdb.defined_balance_id balance_id
,pbt.balance_name balance_name
FROM pay_balance_types pbt
,pay_balance_dimensions pbd
,pay_defined_balances pdb
WHERE pbt.balance_name IN('Employee Statutory PF Contribution'
,'Employee Voluntary PF Contribution'
,'Employer PF Contribution'
,'EPS Contribution'
,'PF Actual Salary'
,'Employer PF Administrative Charges'
,'Employer PF Inspection Charges'
,'Employer EDLI Administrative Charges'
,'Employer EDLI Inspection Charges'
,'EDLI Contribution'
,'Refund of Advance Employer PF Share'
,'Refund of Advance Employee PF Share'
,'Recovery of Over Payment of Employee PF Share'
,'Recovery of Over Payment of Employer PF Share'
,'Penalty Interest on Refund of Employer PF Share'
,'Penalty Interest on Refund of Employee PF Share'
)
AND pbd.dimension_name ='_ASG_ORG_PTD'
AND pbt.legislation_code = 'IN'
AND pbd.legislation_code = 'IN'
AND pdb.legislation_code = 'IN'
AND pbt.balance_type_id = pdb.balance_type_id
AND pbd.balance_dimension_id = pdb.balance_dimension_id;
SELECT pdb.defined_balance_id balance_id
,pbt.balance_name balance_name
FROM pay_balance_types pbt
,pay_balance_dimensions pbd
,pay_defined_balances pdb
WHERE pbt.balance_name IN('Non Contributory Period')
AND pbd.dimension_name ='_ASG_PTD'
AND pbt.legislation_code = 'IN'
AND pbd.legislation_code = 'IN'
AND pdb.legislation_code = 'IN'
AND pbt.balance_type_id = pdb.balance_type_id
AND pbd.balance_dimension_id = pdb.balance_dimension_id;
SELECT result_value
FROM pay_run_results prr,
pay_run_result_values prv,
pay_element_types_f pet,
pay_input_values_f piv,
pay_assignment_actions paa
WHERE prr.run_result_id = prv.run_result_id
AND prr.element_type_id = pet.element_type_id
AND pet.element_type_id = piv.element_type_id
AND piv.input_value_id = prv.input_value_id
AND paa.source_action_id = p_run_asg_action_id
AND paa.assignment_action_id =prr.assignment_action_id
AND pet.element_name = 'PF Information'
AND piv.NAME ='EE Voluntary PF Percent'
AND g_end_date BETWEEN pet.effective_start_date and pet.effective_end_date
AND g_end_date BETWEEN piv.effective_start_date and piv.effective_end_date ;
SELECT org_information3 classification
FROM hr_organization_information hr_pf_org
WHERE org_information_context = 'PER_IN_PF_DF'
AND hr_pf_org.organization_id = p_pf_org ;
SELECT COUNT(*)
FROM pay_action_information
WHERE action_context_id = p_arc_asg_action_id
AND action_information1 = p_business_number
AND action_information_category = 'IN_PF_SALARY'
AND action_context_type = 'AAP'
AND action_information3 = 'Voluntary PF Percent'
AND action_information5 = p_class ;
SELECT
organization_id org_id , --OrgId
org_information10 base_bus_no, --Base Business Number
org_information10||org_information9 bus_no , --Business Number
org_information3 classification --Classification
FROM hr_organization_information hr_pf_org
WHERE org_information_context = 'PER_IN_PF_DF'
AND hr_pf_org.org_information10||org_information9 = p_business_number ;
SELECT org_information4 --Legal Name
FROM hr_organization_information
WHERE org_information_context = 'PER_IN_COMPANY_DF'
AND org_information5 = p_base_bus_no ;
SELECT
org_information1 --Representative
FROM hr_organization_information hr_pf_org
WHERE org_information_context = 'PER_IN_PF_REP_DF'
AND hr_pf_org.organization_id = p_pf_org
AND g_end_date BETWEEN fnd_date.canonical_to_date(org_information2)
AND NVL(fnd_date.canonical_to_date(org_information3),TO_DATE('31-12-4712','DD-MM-YYYY'));
SELECT nvl(pos.name,job.name) name
FROM per_all_positions pos
,per_assignments_f asg
,per_jobs job
WHERE asg.position_id=pos.position_id(+)
AND asg.job_id=job.job_id(+)
AND asg.person_id = p_person_id
AND asg.primary_flag = 'Y'
AND g_end_date BETWEEN pos.date_effective(+) AND NVL(pos.date_end(+),TO_DATE('31-12-4712','DD-MM-YYYY'))
AND g_end_date BETWEEN job.date_from(+) AND NVL(job.date_to(+),TO_DATE('31-12-4712','DD-MM-YYYY'))
AND g_end_date BETWEEN asg.effective_start_date AND asg.effective_end_date;
SELECT DECODE(pep.title,NULL,hr_in_utility.per_in_full_name(pep.first_name,pep.middle_names,pep.last_name,pep.title)
,SUBSTR(hr_in_utility.per_in_full_name(pep.first_name,pep.middle_names,pep.last_name,pep.title)
,INSTR(hr_in_utility.per_in_full_name(pep.first_name,pep.middle_names,pep.last_name,pep.title),' ',1)+1)) name
FROM per_people_f pep
WHERE pep.person_id = p_person_id
AND g_end_date BETWEEN pep.effective_start_date AND pep.effective_end_date ;
SELECT business_group_id INTO l_bus_group_id --Business Group Id
FROM pay_payroll_actions
WHERE payroll_action_id =p_arc_pay_action_id;
SELECT hoi_challan.org_information4 Payment_Type
,hoi_challan.org_information5 Cheque_DD_No
,fnd_date.canonical_to_date(hoi_challan.org_information6) Cheque_DD_Date
,hoi_challan.org_information7 Bank_Code
,hoi_challan.org_information8 Branch_Code
,hoi_challan.org_information11 Dep_Bank_Code
,hoi_challan.org_information9 Dep_Branch_Code
,hoi_challan.org_information10 Dep_Base_Branch
FROM hr_organization_information hoi_challan
WHERE hoi_challan.organization_id = p_pf_org
AND hoi_challan.org_information_context ='PER_IN_PF_BANK_PAYMENT_DETAILS'
AND hoi_challan.org_information3= p_challan_ref;
SELECT hoi_challan.org_information11 Paid_Under_Protest
,fnd_date.canonical_to_date(hoi_challan.org_information9) Cheque_DD_Dep_Date
,fnd_number.canonical_to_number(NVL(hoi_challan.org_information3,0))
+fnd_number.canonical_to_number(NVL(hoi_challan.org_information4,0))
+fnd_number.canonical_to_number(NVL(hoi_challan.org_information5,0))
+fnd_number.canonical_to_number(NVL(hoi_challan.org_information6,0))
+fnd_number.canonical_to_number(NVL(hoi_challan.org_information7,0))
+fnd_number.canonical_to_number(NVL(hoi_challan.org_information8,0))
+fnd_number.canonical_to_number(NVL(hoi_challan.org_information13,0))
+fnd_number.canonical_to_number(NVL(hoi_challan.org_information14,0))
+fnd_number.canonical_to_number(NVL(hoi_challan.org_information15,0)) Amount
,hoi_challan.org_information12 Challan_Ref
,hoi_challan.org_information13 Legal_Charges
,hoi_challan.org_information14 Interest_Sec
,hoi_challan.org_information15 Penalty
FROM hr_organization_information hoi_challan
WHERE hoi_challan.organization_id = p_pf_org
AND hoi_challan.org_information_context ='PER_IN_PF_CHALLAN_INFO'
AND hoi_challan.org_information12= p_challan_ref;
SELECT hoi_challan.org_information1 Challan_Ref
,hoi_challan.org_information2 Prev_Mth
,hoi_challan.org_information3 Prev_Yr
,fnd_number.canonical_to_number(NVL(hoi_challan.org_information4,0))
+fnd_number.canonical_to_number(NVL(hoi_challan.org_information5,0)) Penal_Damages_Due
,fnd_number.canonical_to_number(hoi_challan.org_information6) EPS_Penal_Damages
,fnd_number.canonical_to_number(hoi_challan.org_information7) EDLI_Penal_Damages
,fnd_number.canonical_to_number(hoi_challan.org_information9) EPF_Penal_Damages
,fnd_number.canonical_to_number(hoi_challan.org_information8) Edli_Admin
FROM hr_organization_information hoi_challan
WHERE hoi_challan.organization_id = p_pf_org
AND hoi_challan.org_information_context ='PER_IN_PF_CHN_SEC14B'
AND hoi_challan.org_information1= p_challan_ref;
SELECT hoi_challan.org_information11 Challan_Ref
,hoi_challan.org_information1 Due_Mth
,hoi_challan.org_information2 Due_Yr
,fnd_number.canonical_to_number(NVL(hoi_challan.org_information3,0))
+fnd_number.canonical_to_number(NVL(hoi_challan.org_information4,0)) EPF_Damages
,fnd_number.canonical_to_number(hoi_challan.org_information5) EPS_Damages
,fnd_number.canonical_to_number(hoi_challan.org_information6) EDLI_Damages
,fnd_number.canonical_to_number(hoi_challan.org_information8) EPF_Admin
,fnd_number.canonical_to_number(hoi_challan.org_information7) EDLI_Admin
FROM hr_organization_information hoi_challan
WHERE hoi_challan.organization_id = p_pf_org
AND hoi_challan.org_information_context ='PER_IN_PF_SEC7Q_INFO'
AND hoi_challan.org_information11= p_challan_ref;
SELECT hoi_challan.org_information3 Challan_Ref
,fnd_number.canonical_to_number(NVL(hoi_challan.org_information4,0))
+fnd_number.canonical_to_number(NVL(hoi_challan.org_information5,0)) EPF_Misc_Pay
,hoi_challan.org_information6 EPF_Rem
,fnd_number.canonical_to_number(hoi_challan.org_information7) EPS_Misc_Pay
,hoi_challan.org_information8 EPS_Rem
,fnd_number.canonical_to_number(hoi_challan.org_information9) EDLI_Misc_Pay
,hoi_challan.org_information10 EDLI_Rem
,fnd_number.canonical_to_number(hoi_challan.org_information11) EPF_Admin
,hoi_challan.org_information12 EPF_Admin_Rem
,fnd_number.canonical_to_number(hoi_challan.org_information13) EDLI_Admin
,hoi_challan.org_information14 EDLI_Admin_Rem
FROM hr_organization_information hoi_challan
WHERE hoi_challan.organization_id = p_pf_org
AND hoi_challan.org_information_context ='PER_IN_PF_MIS_PAY_INFO'
AND hoi_challan.org_information3= p_challan_ref;
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_PF_BANK_BRANCH_DTLS'
AND hou.business_group_id = g_bg_id
AND hoi.org_information1 = p_bank_code
AND hoi.org_information2 = p_branch_code ;
SELECT DISTINCT paa_init.assignment_id
FROM pay_assignment_actions paa_init,
pay_payroll_actions ppa_init,
per_assignments_f paf,
hr_organization_information hoi
WHERE ppa_init.payroll_action_id = paa_init.payroll_action_id
AND ppa_init.action_type IN ('P','U','I')
AND ppa_init.action_status = 'C'
AND ppa_init.business_group_id = g_bg_id
AND p_payroll_action_id IS NOT NULL
AND paf.person_id BETWEEN
p_start_person AND p_end_person
AND paf.assignment_id = paa_init.assignment_id
AND (to_char(paf.effective_start_date,'Month-YYYY')=to_char(g_end_date,'Month-YYYY')
OR to_char(paf.effective_end_date,'Month-YYYY')=to_char(g_end_date,'Month-YYYY')
OR g_end_date between paf.effective_start_date and paf.effective_end_date)
AND ppa_init.effective_date BETWEEN g_start_date AND g_end_date
AND hoi.org_information_context = 'PER_IN_PF_DF'
AND hoi.org_information10||hoi.org_information9 = NVL(g_business_no,hoi.org_information10||hoi.org_information9)
AND hoi.org_information10 IS NOT NULL
AND hoi.org_information9 IS NOT NULL
AND TO_CHAR (hoi.organization_id) IN
(SELECT scl.segment2
FROM hr_soft_coding_keyflex scl
WHERE scl.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
AND scl.enabled_flag = 'Y');
SELECT DISTINCT paa_init.assignment_id
FROM pay_assignment_actions paa_init,
pay_payroll_actions ppa_init,
per_assignments_f paf,
hr_organization_information hoi
WHERE ppa_init.payroll_action_id = paa_init.payroll_action_id
AND ppa_init.action_type IN ('P','U','I')
AND ppa_init.action_status = 'C'
AND ppa_init.business_group_id = g_bg_id
AND p_payroll_action_id IS NOT NULL
AND paf.person_id BETWEEN
p_start_person AND p_end_person
AND paf.assignment_id = paa_init.assignment_id
AND (to_char(paf.effective_start_date,'Month-YYYY')=to_char(g_end_date,'Month-YYYY')
OR to_char(paf.effective_end_date,'Month-YYYY')=to_char(g_end_date,'Month-YYYY')
OR g_end_date between paf.effective_start_date and paf.effective_end_date)
AND ppa_init.effective_date BETWEEN g_start_date AND g_end_date
AND hoi.org_information_context = 'PER_IN_PF_DF'
AND hoi.org_information10||hoi.org_information9 = p_business_no
AND hoi.org_information10 IS NOT NULL
AND hoi.org_information9 IS NOT NULL
AND TO_CHAR (hoi.organization_id) IN
(SELECT scl.segment2
FROM hr_soft_coding_keyflex scl
WHERE scl.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
AND scl.enabled_flag = 'Y')
AND NOT EXISTS ( SELECT paa.assignment_id
FROM pay_assignment_actions paa
,pay_action_information pai
,hr_organization_information hoi
,pay_payroll_actions ppa
WHERE paa.assignment_id = paa_init.assignment_id
AND paa.assignment_action_id = pai.action_context_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.report_type = 'IN_PF_ARCHIVE'
AND ppa.action_type = 'X'
AND ppa.action_status = 'C'
AND pai.action_context_type = 'AAP'
AND hoi.org_information7 = pai.action_information2
AND pai.action_information_category = 'IN_PF_PERSON_DTLS'
AND hoi.org_information_context = 'PER_IN_COMPANY_RECEP_MAP'
AND hoi.org_information3 = p_business_no
AND hoi.org_information1 = g_challan_year
AND hoi.org_information2 = g_challan_mth
)
UNION
SELECT DISTINCT paa_init.assignment_id
FROM pay_assignment_actions paa_init,
per_periods_of_service pps,
pay_payroll_actions ppa_init,
per_assignments_f paf,
hr_organization_information hoi
WHERE ppa_init.payroll_action_id = paa_init.payroll_action_id
AND paf.period_of_service_id = pps.period_of_service_id
AND ppa_init.action_type IN ('P','U','I')
AND ppa_init.action_status = 'C'
AND ppa_init.business_group_id = g_bg_id
AND p_payroll_action_id IS NOT NULL
AND paf.person_id BETWEEN
p_start_person AND p_end_person
AND paf.assignment_id = paa_init.assignment_id
AND TO_CHAR(pps.actual_termination_date,'Month-YYYY') = to_char(g_start_date,'Month-YYYY')
AND (to_char(paf.effective_start_date,'Month-YYYY')=to_char(g_end_date,'Month-YYYY')
OR to_char(paf.effective_end_date,'Month-YYYY')=to_char(g_end_date,'Month-YYYY')
OR g_end_date between paf.effective_start_date and paf.effective_end_date)
AND ppa_init.effective_date BETWEEN g_start_date AND g_end_date
AND hoi.org_information_context = 'PER_IN_PF_DF'
AND hoi.org_information10||hoi.org_information9 = p_business_no
AND hoi.org_information10 IS NOT NULL
AND hoi.org_information9 IS NOT NULL
AND TO_CHAR (hoi.organization_id) IN
(SELECT scl.segment2
FROM hr_soft_coding_keyflex scl
WHERE scl.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
AND scl.enabled_flag = 'Y')
AND NOT EXISTS ( SELECT paa.assignment_id
FROM pay_assignment_actions paa
,pay_action_information pai
,hr_organization_information hoi
,hr_organization_units hou
,pay_payroll_actions ppa
WHERE paa.assignment_id = paa_init.assignment_id
AND paa.assignment_action_id = pai.action_context_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND hoi.organization_id = hou.organization_id
AND ppa.report_type = 'IN_PF_ARCHIVE'
AND ppa.action_type = 'X'
AND ppa.action_status = 'C'
AND pai.action_context_type = 'AAP'
AND hoi.org_information7 = pai.action_information2
AND pai.action_information_category = 'IN_PF_PERSON_DTLS'
AND hoi.org_information_context = 'PER_IN_COMPANY_RECEP_MAP'
AND hoi.org_information3 = p_business_no
AND hoi.org_information1 = g_challan_year
AND hoi.org_information2 = g_challan_mth
AND TO_CHAR(fnd_date.canonical_to_date(pai.action_information8),'Month-YYYY') = TO_CHAR (g_start_date,'Month-YYYY')
);
SELECT DISTINCT paa_init.assignment_id
FROM pay_assignment_actions paa_init,
pay_payroll_actions ppa_init,
per_assignments_f paf,
hr_organization_information hoi
WHERE ppa_init.payroll_action_id = paa_init.payroll_action_id
AND ppa_init.action_type IN ('P','U','I')
AND ppa_init.action_status = 'C'
AND ppa_init.business_group_id = g_bg_id
AND p_payroll_action_id IS NOT NULL
AND paf.person_id BETWEEN
p_start_person AND p_end_person
AND paf.assignment_id = paa_init.assignment_id
AND (to_char(paf.effective_start_date,'Month-YYYY')=to_char(g_end_date,'Month-YYYY')
OR to_char(paf.effective_end_date,'Month-YYYY')=to_char(g_end_date,'Month-YYYY')
OR g_end_date between paf.effective_start_date and paf.effective_end_date)
AND ppa_init.effective_date BETWEEN g_start_date AND g_end_date
AND hoi.org_information_context = 'PER_IN_PF_DF'
AND hoi.org_information10||hoi.org_information9 = p_business_no
AND hoi.org_information10 IS NOT NULL
AND hoi.org_information9 IS NOT NULL
AND TO_CHAR (hoi.organization_id) IN
(SELECT scl.segment2
FROM hr_soft_coding_keyflex scl
WHERE scl.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
AND scl.enabled_flag = 'Y');
SELECT
hoi_challan.org_information12 Challan_Ref
FROM hr_organization_information hoi_challan
WHERE hoi_challan.organization_id = p_pf_org
AND hoi_challan.org_information_context ='PER_IN_PF_CHALLAN_INFO'
AND hoi_challan.org_information1 = g_challan_year
AND hoi_challan.org_information2 = g_challan_mth
AND NOT EXISTS (SELECT pai.action_information8
FROM pay_action_information pai,
pay_payroll_Actions ppa
WHERE pai.action_information8 = hoi_challan.org_information12
AND pai.action_information_category = 'IN_PF_CHALLAN'
AND pai.action_context_type = 'PA'
AND pai.action_information3 = p_pf_org
AND pai.action_context_id = ppa.payroll_action_id
AND ppa.action_type ='X'
AND ppa.action_status='C'
AND ppa.report_type='IN_PF_ARCHIVE'
AND pai.action_context_id IN
(SELECT action_context_id
FROM pay_action_information painfo
WHERE painfo.action_information2 IN (SELECT hoi.org_information7
FROM hr_organization_information hoi ,
hr_organization_units hou
WHERE hoi.organization_id = hou.organization_id
AND hoi.org_information_context = 'PER_IN_COMPANY_RECEP_MAP'
AND hoi.org_information3 = p_business_no
AND hoi.org_information1 = g_challan_year
AND hoi.org_information2 = g_challan_mth
)
AND painfo.action_context_type = 'PA'
AND painfo.action_information_category = 'IN_PF_BUSINESS_NUMBER')
);
SELECT
hoi_challan.org_information12 Challan_Ref
FROM hr_organization_information hoi_challan
WHERE hoi_challan.organization_id = p_pf_org
AND hoi_challan.org_information_context ='PER_IN_PF_CHALLAN_INFO'
AND hoi_challan.org_information1 = g_challan_year
AND hoi_challan.org_information2 = g_challan_mth ;
SELECT org_information3 bus_no
FROM hr_organization_information
WHERE org_information_context = 'PER_IN_COMPANY_RECEP_MAP'
AND org_information1 = g_challan_year
AND org_information2 = g_challan_mth
AND org_information3 = NVL(g_business_no , org_information3)
AND org_information4 = 'R'
GROUP BY org_information3 ;
SELECT DISTINCT org_information7 Archive_Ref_No
FROM hr_organization_information
WHERE org_information_context = 'PER_IN_COMPANY_RECEP_MAP'
AND org_information1 = g_challan_year
AND org_information2 = g_challan_mth
AND org_information3 = NVL(p_business_no,org_information3) ;
SELECT DISTINCT action_context_id
FROM pay_action_information pai,
pay_assignment_actions paa,
pay_payroll_Actions ppa
WHERE action_information2 = p_arc_ref
AND action_context_type = 'AAP'
AND action_information_category = 'IN_PF_PERSON_DTLS'
AND action_information1 = nvl(p_business_no,action_information1)
AND pai.action_context_id = paa.assignment_action_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND ppa.action_type ='X'
AND ppa.action_status='C'
AND ppa.report_type='IN_PF_ARCHIVE'
;
SELECT hr_pf_org.organization_id pf_org --PF Org Id
FROM hr_organization_information hr_pf_org
WHERE org_information_context = 'PER_IN_PF_DF'
AND org_information10||org_information9 = p_business_no ;
SELECT 1
FROM pay_action_information pai
WHERE pai.action_information_category = 'IN_PF_ORG'
AND pai.action_context_type = 'PA'
AND pai.action_context_id = p_payroll_action_id
AND pai.action_information1 = p_business_no
AND ROWNUM =1;
SELECT 1
FROM pay_action_information pai
WHERE pai.action_information_category = 'IN_PF_CHALLAN'
AND pai.action_context_type = 'PA'
AND pai.action_context_id = p_payroll_action_id
AND pai.action_information3 = p_pf_org
AND ROWNUM =1;
SELECT pay_assignment_actions_s.NEXTVAL
INTO l_action_id
FROM dual;
SELECT pay_assignment_actions_s.NEXTVAL
INTO l_action_id
FROM dual;
SELECT pay_assignment_actions_s.NEXTVAL
INTO l_action_id
FROM dual;
SELECT paa.assignment_id
,paa.payroll_action_id
,paf.person_id
FROM pay_assignment_actions paa
,per_assignments_f paf
WHERE paa.assignment_action_id = p_assignment_action_id
AND paa.assignment_id = paf.assignment_id
AND ROWNUM =1;
SELECT TO_NUMBER(SUBSTR(MAX(LPAD(paa.action_sequence,15,'0')||paa.assignment_action_id),16)) run_asg_action_id
FROM pay_assignment_actions paa
,pay_payroll_actions ppa
,per_assignments_f paf
WHERE paf.assignment_id = paa.assignment_id
AND paf.assignment_id = p_assignment_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND ppa.action_type IN('R','Q','I','B')
AND ppa.payroll_id = paf.payroll_id
AND ppa.action_status ='C'
AND ppa.effective_date between p_start_date and p_end_date
AND paa.source_action_id IS NULL
AND (1 = DECODE(ppa.action_type,'I',1,0)
OR EXISTS (SELECT ''
FROM pay_action_interlocks intk,
pay_assignment_actions paa1,
pay_payroll_actions ppa1
WHERE intk.locked_action_id = paa.assignment_Action_id
AND intk.locking_action_id = paa1.assignment_action_id
AND paa1.payroll_action_id =ppa1.payroll_action_id
AND paa1.assignment_id = p_assignment_id
AND ppa1.action_type in('P','U')
AND ppa.action_type in('R','Q','B')
AND ppa1.action_status ='C'
AND ppa1.effective_date BETWEEN p_start_date and p_end_date
AND ROWNUM =1 ));
SELECT hr_pf_org.organization_id pf_org,
org_information10 base_bus_no,
org_information10||org_information9 business_number
FROM hr_organization_information hr_pf_org
,hr_organization_units hou
WHERE hou.organization_id = hr_pf_org.organization_id
AND hou.business_group_id = g_bg_id
AND org_information_context = 'PER_IN_PF_DF'
AND org_information10||org_information9 = NVL(g_business_no,org_information10||org_information9)
AND org_information10 IS NOT NULL
AND org_information9 IS NOT NULL ;
SELECT
hoi_challan.org_information12 Challan_Ref
FROM hr_organization_information hoi_challan
WHERE hoi_challan.organization_id = p_pf_org
AND hoi_challan.org_information_context ='PER_IN_PF_CHALLAN_INFO'
AND hoi_challan.org_information1 = g_challan_year
AND hoi_challan.org_information2 = g_challan_mth ;
SELECT 1
FROM pay_action_information pai
WHERE pai.action_information_category = 'IN_PF_ORG'
AND pai.action_context_type = 'PA'
AND pai.action_context_id = p_payroll_action_id
AND pai.action_information1 = p_business_no
AND ROWNUM =1;
SELECT 1
FROM pay_action_information pai
WHERE pai.action_information_category = 'IN_PF_CHALLAN'
AND pai.action_context_type = 'PA'
AND pai.action_context_id = p_payroll_action_id
AND pai.action_information3 = p_pf_org
AND ROWNUM =1;
SELECT 1
FROM per_assignments_f paf,
hr_soft_coding_keyflex scl
WHERE (to_char(paf.effective_start_date,'Month-YYYY')=to_char(g_end_date,'Month-YYYY')
OR to_char(paf.effective_end_date,'Month-YYYY')=to_char(g_end_date,'Month-YYYY')
OR g_end_date between paf.effective_start_date and paf.effective_end_date)
AND scl.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
AND scl.enabled_flag = 'Y'
AND scl.segment2 = p_pf_org
AND assignment_id = p_assignment;