[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT ppf.full_name name
,ppf.employee_number employee_number
,to_char(ppf.date_of_birth,'dd-Mon-yyyy') dob
,to_char(ppos.date_start,'dd-Mon-yyyy') doj
,round((months_between(ppos.actual_termination_date
, ppf.date_of_birth))/12) age
,hr_general.decode_lookup('LEAV_REAS'
,ppos.leaving_reason) leaving_reason
,to_char(ppos.actual_termination_date,'dd-Mon-yyyy') dol
,trunc(months_between(ppos.actual_termination_date
, ppos.date_start)/12) || ' Years and '
|| (1 + ppos.actual_termination_date - add_months(ppos.date_start
,12*trunc(months_between
(ppos.actual_termination_date, ppos.date_start)/12))) ||' Days' los
,hou.name department
FROM per_people_f ppf
,per_assignments_f paf
,per_periods_of_service ppos
,hr_soft_coding_keyflex hsck
,hr_organization_units hou
WHERE ppf.business_group_id = p_bus_grp_id
AND ppf.employee_number = p_employee_number
AND ppf.person_id = paf.person_id
AND ppos.period_of_service_id = paf.period_of_service_id
AND ppos.business_group_id = ppf.business_group_id
AND ppf.person_id = ppos.person_id
AND hsck.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
AND hou.organization_id = hsck.segment1
AND ppos.actual_termination_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND ppos.actual_termination_date BETWEEN paf.effective_start_date AND paf.effective_end_date
AND ppos.actual_termination_date = p_term_date;
SELECT nvl(pp.name,pj.name) designation
FROM per_positions pp
,per_jobs pj
,per_people_f ppf
,per_assignments_f paf
,per_periods_of_service ppos
WHERE ppf.business_group_id = p_bus_grp_id
AND ppf.employee_number = p_employee_number
AND ppf.person_id = paf.person_id
AND ppos.period_of_service_id = paf.period_of_service_id
AND ppos.business_group_id = ppf.business_group_id
AND ppf.person_id = ppos.person_id
AND pp.position_id(+) = paf.position_id
AND pj.job_id(+) = paf.job_id
AND ppos.actual_termination_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND ppos.actual_termination_date BETWEEN paf.effective_start_date AND paf.effective_end_date
AND ppos.actual_termination_date = p_term_date
AND paf.effective_end_date between date_effective(+) AND nvl(date_end(+), to_date('31-12-4712', 'DD-MM-YYYY'))
AND paf.effective_end_date between pj.date_from(+) AND NVL(pj.date_to(+),TO_DATE('31-12-4712','DD-MM-YYYY'));
SELECT nvl(pet.reporting_name, pet.element_name) description
,sum(prrv.result_value) amount
,pec.classification_name classification
FROM per_assignments_f paf
,per_people_f ppf
,pay_element_types_f pet
,pay_input_values_f piv
,pay_assignment_actions paa
,pay_run_results prr
,pay_run_result_values prrv
,per_periods_of_service ppos
,pay_element_classifications pec
WHERE paf.business_group_id = p_bus_grp_id
AND ppf.employee_number = p_employee_number
AND ppf.person_id = paf.person_id
AND paf.period_of_service_id = ppos.period_of_service_id
AND ppf.person_id = ppos.person_id
AND paf.assignment_id = paa.assignment_id /*Added as per 7283019 */
AND pec.classification_name IN ('Earnings',
'Allowances',
'Deductions',
'Employer Charges',
'Perquisites',
'Termination Payments',
'Involuntary Deductions',
'Voluntary Deductions',
'Pre Tax Deductions',
'Tax Deductions',
'Advances', --Added for bug fix 6660147
'Fringe Benefits') --Added for bug fix 6660147
AND (pet.business_group_id = paf.business_group_id or pet.legislation_code = 'IN')
AND pec.classification_id = pet.classification_id
AND pec.legislation_code = 'IN'
AND piv.element_type_id = pet.element_type_id
AND piv.name = 'Pay Value'
AND paa.source_action_id = p_max_asg_id
AND prr.assignment_action_id = paa.assignment_action_id
AND prr.element_type_id = pet.element_type_id
AND pet.element_name <> 'Loan Recovery'
AND prr.status IN ('P', 'PA')
AND prr.run_result_id = prrv.run_result_id
AND prrv.input_value_id = piv.input_value_id
--AND fnd_number.canonical_to_number(prrv.result_value) <> 0)
AND prrv.result_value <> '0'
AND ppos.actual_termination_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND ppos.actual_termination_date BETWEEN paf.effective_start_date AND paf.effective_end_date
AND ppos.actual_termination_date BETWEEN pet.effective_start_date AND pet.effective_end_date
AND ppos.actual_termination_date BETWEEN piv.effective_start_date AND piv.effective_end_date
GROUP BY nvl(pet.reporting_name, pet.element_name)
,pec.classification_name ;
SELECT max(paa.assignment_action_id)
FROM pay_assignment_actions paa
,pay_payroll_actions ppa
,per_people_f ppf
,per_assignments_f paf
,per_periods_of_service ppos
WHERE ppf.employee_number = p_employee_number
AND ppf.person_id = paf.person_id
AND paf.period_of_service_id = ppos.period_of_service_id
AND ppf.person_id = ppos.person_id
AND paf.business_group_id = ppf.business_group_id
AND ppf.business_group_id = ppa.business_group_id
AND ppa.business_group_id = p_bus_grp_id
AND paa.assignment_id = paf.assignment_id
AND ppa.payroll_id = paf.payroll_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND paa.action_status = 'C'
AND paa.source_action_id IS NULL
AND ppa.action_type in ('R','Q')
AND ppa.action_status = 'C'
AND ppos.actual_termination_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND ppos.actual_termination_date BETWEEN paf.effective_start_date AND paf.effective_end_date
AND ppos.actual_termination_date = p_term_date;
SELECT org_information6
FROM hr_organization_information
WHERE organization_id = p_bus_grp_id
AND org_information_context = 'PER_IN_STAT_SETUP_DF';
SELECT max(last_day(pos.last_standard_process_date))
FROM per_periods_of_service pos
,per_people_f ppf
WHERE ppf.employee_number = p_employee_number
AND ppf.person_id = pos.person_id
AND ppf.business_group_id = p_bus_grp_id
AND pos.actual_termination_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND pos.actual_termination_date = p_term_date;
SELECT max(paa.assignment_action_id)
FROM pay_assignment_actions paa
,pay_payroll_actions ppa
,per_people_f ppf
,per_assignments_f paf
,per_periods_of_service ppos
WHERE ppf.employee_number = p_employee_number
AND ppf.person_id = paf.person_id
AND paf.period_of_service_id = ppos.period_of_service_id
AND ppf.person_id = ppos.person_id
AND paf.business_group_id = ppf.business_group_id
AND ppf.business_group_id = ppa.business_group_id
AND ppa.business_group_id = p_bus_grp_id
AND paa.assignment_id = paf.assignment_id
AND ppa.payroll_id = paf.payroll_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND paa.action_status = 'C'
AND paa.source_action_id IS NULL
AND ppa.action_type in ('R','Q')
AND ppa.action_status = 'C'
AND last_day(ppa.date_earned) = p_process_date
AND ppos.actual_termination_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND ppos.actual_termination_date BETWEEN paf.effective_start_date AND paf.effective_end_date
AND ppos.actual_termination_date = p_term_date;
SELECT max(paa.assignment_action_id)
FROM pay_assignment_actions paa
,pay_payroll_actions ppa
,per_people_f ppf
,per_assignments_f paf
,per_periods_of_service ppos
WHERE ppf.employee_number = p_employee_number
AND ppf.person_id = paf.person_id
AND paf.period_of_service_id = ppos.period_of_service_id
AND ppf.person_id = ppos.person_id
AND paf.business_group_id = ppf.business_group_id
AND ppf.business_group_id = ppa.business_group_id
AND ppa.business_group_id = p_bus_grp_id
AND paa.assignment_id = paf.assignment_id
AND ppa.payroll_id = paf.payroll_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND paa.action_status = 'C'
AND paa.source_action_id IS NOT NULL
AND ppa.action_type in ('R','Q')
AND ppa.action_status = 'C'
AND ppos.actual_termination_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND ppos.actual_termination_date BETWEEN paf.effective_start_date AND paf.effective_end_date
AND ppos.actual_termination_date = p_term_date;
SELECT max(paa.assignment_action_id)
FROM pay_assignment_actions paa
,pay_payroll_actions ppa
,per_people_f ppf
,per_assignments_f paf
,per_periods_of_service ppos
WHERE ppf.employee_number = p_employee_number
AND ppf.person_id = paf.person_id
AND paf.period_of_service_id = ppos.period_of_service_id
AND ppf.person_id = ppos.person_id
AND paf.business_group_id = ppf.business_group_id
AND ppf.business_group_id = ppa.business_group_id
AND ppa.business_group_id = p_bus_grp_id
AND paa.assignment_id = paf.assignment_id
AND ppa.payroll_id = paf.payroll_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND paa.action_status = 'C'
AND paa.source_action_id IS NOT NULL
AND ppa.action_type in ('R','Q')
AND ppa.action_status = 'C'
AND last_day(ppa.date_earned) = p_process_date
AND ppos.actual_termination_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND ppos.actual_termination_date BETWEEN paf.effective_start_date AND paf.effective_end_date
AND ppos.actual_termination_date = p_term_date;
SELECT piv.name description
,INITCAP(peev.screen_entry_value) loan_type
,prrv.result_value amount
FROM per_assignments_f paf
,per_people_f ppf
,pay_element_types_f pet
,pay_input_values_f piv
,pay_element_entries_f pee
,pay_element_entry_values_f peev
,pay_element_classifications pec
,pay_element_links_f pel
,pay_assignment_actions paa
,pay_payroll_actions ppa
,pay_run_results prr
,pay_run_result_values prrv
,per_periods_of_service ppos
WHERE paf.business_group_id = p_bus_grp_id
AND ppf.employee_number = p_employee_number
AND ppf.person_id = paf.person_id
AND paf.period_of_service_id = ppos.period_of_service_id
AND pec.classification_name = 'Termination Payments'
AND pec.legislation_code = 'IN'
AND pec.classification_id = pet.classification_id
AND pee.assignment_id = paf.assignment_id
AND pee.element_type_id = pet.element_type_id
AND pet.element_name = 'Loan Recovery'
AND pel.element_link_id = pee.element_link_id
AND pel.business_group_id = paf.business_group_id
AND (pel.payroll_id = paf.payroll_id OR pel.link_to_all_payrolls_flag IS NOT NULL)
AND piv.element_type_id = pet.element_type_id
AND ((piv.name = 'Pay Value' ) --AND fnd_number.canonical_to_number(prrv.result_value) < 0)
OR piv.name = 'Loan Type')
AND paa.source_action_id = p_asg_action_id
AND prr.assignment_action_id = paa.assignment_action_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND prr.element_entry_id = pee.element_entry_id
AND prr.element_type_id = pet.element_type_id
AND prr.status in ('P', 'PA')
AND prr.run_result_id = prrv.run_result_id
AND prrv.input_value_id = piv.input_value_id
AND peev.input_value_id =piv.input_value_id
AND peev.element_entry_id = pee.element_entry_id
AND ppos.actual_termination_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND ppos.actual_termination_date BETWEEN paf.effective_start_date AND paf.effective_end_date
AND ppa.effective_date BETWEEN pee.effective_start_date AND pee.effective_end_date
AND ppos.actual_termination_date BETWEEN pet.effective_start_date AND pet.effective_end_date
AND ppos.actual_termination_date BETWEEN piv.effective_start_date AND piv.effective_end_date
AND ppa.effective_date BETWEEN peev.effective_start_date AND peev.effective_end_date
ORDER BY pee.element_entry_id
,piv.name;
SELECT piv.name description
,prrv.result_value amount
FROM per_assignments_f paf
,per_people_f ppf
,pay_element_types_f pet
,pay_input_values_f piv
,pay_element_entries_f pee
,pay_element_classifications pec
,pay_element_links_f pel
,pay_assignment_actions paa
,pay_payroll_actions ppa
,pay_run_results prr
,pay_run_result_values prrv
,per_periods_of_service ppos
WHERE paf.business_group_id = p_bus_grp_id
AND ppf.employee_number = p_employee_number
AND ppf.person_id = paf.person_id
AND paf.period_of_service_id = ppos.period_of_service_id
AND pec.classification_name = 'Information'
AND pec.legislation_code = 'IN'
AND pec.classification_id = pet.classification_id
AND pee.assignment_id = paf.assignment_id
AND pee.element_type_id = pet.element_type_id
AND pet.element_name = 'Leave Encashment Information'
AND pel.element_link_id = pee.element_link_id
AND pel.business_group_id = paf.business_group_id
AND (pel.payroll_id = paf.payroll_id OR pel.link_to_all_payrolls_flag IS NOT NULL)
AND piv.element_type_id = pet.element_type_id
AND piv.name IN ('Leave Type'
, 'Leave Balance Days'
, 'Leave Adjusted Days'
, 'Encashment Amount')
AND paa.source_action_id = p_asg_action_id
AND prr.assignment_action_id = paa.assignment_action_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND prr.element_entry_id = pee.element_entry_id
AND prr.element_type_id = pet.element_type_id
AND prr.status IN ('P', 'PA')
AND prr.run_result_id = prrv.run_result_id
AND prrv.input_value_id = piv.input_value_id
AND ppos.actual_termination_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND ppos.actual_termination_date BETWEEN paf.effective_start_date AND paf.effective_end_date
AND ppa.effective_date BETWEEN pee.effective_start_date AND pee.effective_end_date
AND ppos.actual_termination_date BETWEEN pet.effective_start_date AND pet.effective_end_date
AND ppos.actual_termination_date BETWEEN piv.effective_start_date AND piv.effective_end_date
AND ppa.effective_date BETWEEN pel.effective_start_date AND pel.effective_end_date
ORDER BY pee.element_entry_id,piv.name DESC;
SELECT prrv.result_value amount
,piv.name description
FROM per_assignments_f paf
,per_people_f ppf
,pay_element_types_f pet
,pay_input_values_f piv
,pay_assignment_actions paa
,pay_run_results prr
,pay_run_result_values prrv
,per_periods_of_service ppos
,pay_element_classifications pec
WHERE paf.business_group_id = p_bus_grp_id
AND ppf.employee_number = p_employee_number
AND ppf.person_id = paf.person_id
AND paf.period_of_service_id = ppos.period_of_service_id
AND ppf.person_id = ppos.person_id
AND pet.element_name IN ('Gratuity Information'
, 'Gratuity Payment')
AND pec.classification_name IN ('Information',
'Termination Payments')
AND (pet.business_group_id = paf.business_group_id or pet.legislation_code = 'IN')
AND pec.classification_id = pet.classification_id
AND pec.legislation_code = 'IN'
AND piv.element_type_id = pet.element_type_id
AND piv.name IN ( 'Pay Value'
,'Base Salary Used'
,'Completed Service Years'
,'Forfeiture Amount'
,'Forfeiture Reason'
,'Calculated Amount')
AND paa.source_action_id = p_max_asg_id
AND prr.assignment_action_id = paa.assignment_action_id
AND paa.assignment_id = paf.assignment_id
AND prr.element_type_id = pet.element_type_id
AND prr.status IN ('P', 'PA')
AND prr.run_result_id = prrv.run_result_id
AND prrv.input_value_id = piv.input_value_id
AND ppos.actual_termination_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND ppos.actual_termination_date BETWEEN paf.effective_start_date AND paf.effective_end_date
AND ppos.actual_termination_date BETWEEN pet.effective_start_date AND pet.effective_end_date
AND ppos.actual_termination_date BETWEEN piv.effective_start_date AND piv.effective_end_date
ORDER BY piv.name;
SELECT piv.name description
,prrv.result_value amount
,nvl(pet.reporting_name, pet.element_name) Element
FROM per_assignments_f paf
,per_people_f ppf
,pay_element_types_f pet
,pay_input_values_f piv
,pay_assignment_actions paa
,pay_run_results prr
,pay_run_result_values prrv
,per_periods_of_service ppos
,pay_element_classifications pec
WHERE paf.business_group_id = p_bus_grp_id
AND ppf.employee_number = p_employee_number
AND ppf.person_id = paf.person_id
AND paf.period_of_service_id = ppos.period_of_service_id
AND ppf.person_id = ppos.person_id
AND pec.classification_name = 'Termination Payments'
AND pet.legislation_code = 'IN'
AND pec.classification_id = pet.classification_id
AND pec.legislation_code = 'IN'
AND ((piv.name = 'Pay Value') --AND fnd_number.canonical_to_number(prrv.result_value) > 0
OR piv.name IN ('Taxable Amount'
, 'Non Taxable Amount'))
AND piv.element_type_id = pet.element_type_id
AND paa.source_action_id = p_max_asg_id
AND prr.assignment_action_id = paa.assignment_action_id
AND pet.element_name IN ('Commuted Pension'
,'Gratuity Payment'
,'PF Settlement'
,'Leave Encashment')
AND prr.element_type_id = pet.element_type_id
AND prr.status IN ('P', 'PA')
AND paf.assignment_id = paa.assignment_id
AND prr.run_result_id = prrv.run_result_id
AND prrv.input_value_id = piv.input_value_id
AND ppos.actual_termination_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND ppos.actual_termination_date BETWEEN paf.effective_start_date AND paf.effective_end_date
AND ppos.actual_termination_date BETWEEN pet.effective_start_date AND pet.effective_end_date
AND ppos.actual_termination_date BETWEEN piv.effective_start_date AND piv.effective_end_date
ORDER BY pet.element_name
,piv.name;
SELECT piv.name description
,prrv.result_value amount
,nvl(pet.reporting_name, pet.element_name) Element
FROM per_assignments_f paf
,per_people_f ppf
,pay_element_types_f pet
,pay_input_values_f piv
,pay_assignment_actions paa
,pay_run_results prr
,pay_run_result_values prrv
,per_periods_of_service ppos
,pay_element_classifications pec
WHERE paf.business_group_id = p_bus_grp_id
AND ppf.employee_number = p_employee_number
AND ppf.person_id = paf.person_id
AND paf.period_of_service_id = ppos.period_of_service_id
AND ppf.person_id = ppos.person_id
AND pec.classification_name IN ('Information','Termination Payments')
AND pet.legislation_code = 'IN'
AND pec.classification_id = pet.classification_id
AND pec.legislation_code = 'IN'
AND piv.name IN ('Taxable Amount'
, 'Non Taxable Amount')
AND piv.element_type_id = pet.element_type_id
AND paa.source_action_id = p_max_asg_id
AND prr.assignment_action_id = paa.assignment_action_id
AND pet.element_name IN ('Retrenchment Compensation Information'
,'Voluntary Retirement Information'
,'Other Termination Payments')
AND prr.element_type_id = pet.element_type_id
AND prr.status IN ('P', 'PA')
AND paf.assignment_id = paa.assignment_id
AND prr.run_result_id = prrv.run_result_id
AND prrv.input_value_id = piv.input_value_id
AND ppos.actual_termination_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND ppos.actual_termination_date BETWEEN paf.effective_start_date AND paf.effective_end_date
AND ppos.actual_termination_date BETWEEN pet.effective_start_date AND pet.effective_end_date
AND ppos.actual_termination_date BETWEEN piv.effective_start_date AND piv.effective_end_date
ORDER BY prr.element_entry_id
,piv.name;
SELECT sum(prrv.result_value) amount
,nvl(pet.reporting_name, pet.element_name) description
FROM per_assignments_f paf
,per_people_f ppf
,pay_element_types_f pet
,pay_input_values_f piv
,pay_assignment_actions paa
,pay_run_results prr
,pay_run_result_values prrv
,per_periods_of_service ppos
,pay_element_classifications pec
WHERE ppf.business_group_id = p_bus_grp_id
AND ppf.employee_number = p_employee_number
AND ppf.person_id = paf.person_id
AND paf.period_of_service_id = ppos.period_of_service_id
AND ppf.person_id = ppos.person_id
AND ppos.business_group_id = ppf.business_group_id
AND pec.classification_name = 'Termination Payments'
AND (pet.business_group_id = ppf.business_group_id OR pet.legislation_code = 'IN')
AND pec.classification_id = pet.classification_id
AND pec.legislation_code = 'IN'
AND piv.name = 'Pay Value'
-- AND fnd_number.canonical_to_number(prrv.result_value) > 0
AND piv.element_type_id = pet.element_type_id
AND paa.source_action_id = p_max_asg_id
AND paa.assignment_id = paf.assignment_id
AND prr.assignment_action_id = paa.assignment_action_id
AND pet.element_name NOT IN ('Commuted Pension'
,'Gratuity Payment'
,'PF Settlement'
,'Leave Encashment'
,'Other Termination Payments')
AND pet.element_name NOT IN (select petf.element_name
from pay_balance_feeds_f pbff
,pay_balance_types pbt
,pay_input_values_f pivf
,pay_element_types_f petf
where pbff.balance_type_id = pbt.balance_type_id
and pbt.balance_name in ('Retrenchment Compensation'
, 'Voluntary Retirement Benefits')
and pbff.input_value_id = pivf.input_value_id
and pivf.name = 'Pay Value'
and petf.element_type_id = pivf.element_type_id
and pbt.legislation_code = 'IN')
AND prr.element_type_id = pet.element_type_id
AND prr.status IN ('P', 'PA')
AND prr.run_result_id = prrv.run_result_id
AND prrv.input_value_id = piv.input_value_id
AND ppos.actual_termination_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND ppos.actual_termination_date BETWEEN paf.effective_start_date AND paf.effective_end_date
AND ppos.actual_termination_date BETWEEN pet.effective_start_date AND pet.effective_end_date
AND ppos.actual_termination_date BETWEEN piv.effective_start_date AND piv.effective_end_date
GROUP BY nvl(pet.reporting_name, pet.element_name) ;
SELECT /*+ ORDERED */
SUM(ABS(fnd_number.canonical_to_number(prrv.result_value))) amount
FROM per_assignments_f paf
,per_people_f ppf
,pay_element_types_f pet
,pay_input_values_f piv
,pay_assignment_actions paa
-- ,pay_element_entries_f peef-- Added as a part of bug fix 4774108
,pay_run_results prr
,pay_run_result_values prrv
,per_periods_of_service ppos
,pay_element_classifications pec
WHERE paf.business_group_id = p_bus_grp_id
AND ppf.employee_number = p_employee_number
AND ppf.person_id = paf.person_id
AND paf.period_of_service_id = ppos.period_of_service_id
AND ppf.person_id = ppos.person_id
AND ((pec.classification_name IN ( 'Termination Payments'
, 'Earnings') AND fnd_number.canonical_to_number(prrv.result_value) < 0)
OR (pec.classification_name IN ( 'Tax Deductions') AND fnd_number.canonical_to_number(prrv.result_value) > 0))
AND (pet.business_group_id = paf.business_group_id OR pet.legislation_code = 'IN')
AND pec.classification_id = pet.classification_id
AND pec.legislation_code = 'IN'
AND piv.name = 'Pay Value'
AND piv.element_type_id = pet.element_type_id
AND paa.source_action_id = p_max_asg_id
AND prr.assignment_action_id = paa.assignment_action_id -- Modified as per bug 4774108
AND prr.element_type_id = pet.element_type_id
AND prr.status IN ('P', 'PA')
AND paf.assignment_id = paa.assignment_id
AND prr.run_result_id = prrv.run_result_id
AND prrv.input_value_id = piv.input_value_id
AND ppos.actual_termination_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND ppos.actual_termination_date BETWEEN paf.effective_start_date AND paf.effective_end_date
AND ppos.actual_termination_date BETWEEN pet.effective_start_date AND pet.effective_end_date
AND ppos.actual_termination_date BETWEEN piv.effective_start_date AND piv.effective_end_date;
SELECT to_number(pay_balance_pkg.get_value(pdb.defined_balance_id
, paa.assignment_action_id)) amount
, pbt.balance_name description
FROM per_assignments_f paf
,per_people_f ppf
,pay_assignment_actions paa
,per_periods_of_service ppos
,pay_balance_types pbt
,pay_balance_dimensions pbd
,pay_defined_balances pdb
WHERE paf.business_group_id = p_bus_grp_id
AND ppf.employee_number = p_employee_number
AND ppf.person_id = paf.person_id
AND paf.period_of_service_id = ppos.period_of_service_id
AND ppf.person_id = ppos.person_id
AND paa.source_action_id = p_max_asg_id
AND paf.assignment_id = paa.assignment_id -- Modified for 4774108
AND pbt.balance_name IN ('Net Pay')
AND pbt.legislation_code = 'IN'
AND pbd.dimension_name = '_ASG_RUN'
AND pbd.legislation_code = 'IN'
AND pdb.balance_type_id = pbt.balance_type_id
AND pdb.balance_dimension_id = pbd.balance_dimension_id
AND ppos.actual_termination_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND ppos.actual_termination_date BETWEEN paf.effective_start_date AND paf.effective_end_date;
SELECT hr_general.decode_lookup('IN_BANK',pea.segment3) bank
,hr_general.decode_lookup('IN_BANK_BRANCH',pea.segment4) branch
,pea.segment1 account_number
,ppt.payment_type_name payment_type
,ppp.value amount
,ppa.effective_date payment_date
FROM pay_external_accounts pea
,pay_pre_payments ppp
,pay_org_payment_methods_f pop
,pay_personal_payment_methods_f ppm
,pay_payment_types ppt
,pay_action_interlocks pci
,pay_payroll_actions ppa
,pay_assignment_actions paa
,per_people_f ppf
,per_assignments_f paf
,per_periods_of_service ppos
WHERE ppp.assignment_action_id = pci.locking_action_id
AND ppp.personal_payment_method_id = ppm.personal_payment_method_id (+)
AND ppp.org_payment_method_id = pop.org_payment_method_id
AND ppm.external_account_id = pea.external_account_id (+)
AND pop.payment_type_id = ppt.payment_type_id
AND pci.locked_action_id = paa.assignment_action_id
AND ppf.business_group_id = p_bus_grp_id
AND ppf.employee_number = p_employee_number
AND ppf.person_id = paf.person_id
AND paf.period_of_service_id = ppos.period_of_service_id
AND ppos.business_group_id = ppf.business_group_id
AND ppf.person_id = ppos.person_id
AND paf.assignment_id = paa.assignment_id
AND paa.assignment_action_id = p_max_payment_asg_id
AND paa.payroll_Action_id = ppa.payroll_action_id
AND ppa.effective_date BETWEEN pop.effective_start_date AND pop.effective_end_date
AND ppos.actual_termination_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND ppos.actual_termination_date BETWEEN paf.effective_start_date AND paf.effective_end_date
AND ppa.effective_date BETWEEN nvl(ppm.effective_start_date,ppa.effective_date)
AND nvl(ppm.effective_end_date,ppa.effective_date);
SELECT to_number(pay_balance_pkg.get_value(pdb.defined_balance_id
, paa.assignment_action_id)) amount
, pbt.balance_name description
FROM per_assignments_f paf
,per_people_f ppf
,pay_assignment_actions paa
,per_periods_of_service ppos
,pay_balance_types pbt
,pay_balance_dimensions pbd
,pay_defined_balances pdb
WHERE paf.business_group_id = p_bus_grp_id
AND ppf.employee_number = p_employee_number
AND ppf.person_id = paf.person_id
AND paf.period_of_service_id = ppos.period_of_service_id
AND ppf.person_id = ppos.person_id
AND paa.source_action_id = p_max_asg_id
AND paf.assignment_id = paa.assignment_id -- Modified for 4774108
AND pbt.balance_name IN ('Gratuity Eligible Salary')
AND pbt.legislation_code = 'IN'
AND pbd.dimension_name = '_ASG_PTD'
AND pbd.legislation_code = 'IN'
AND pdb.balance_type_id = pbt.balance_type_id
AND pdb.balance_dimension_id = pbd.balance_dimension_id
AND ppos.actual_termination_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND ppos.actual_termination_date BETWEEN paf.effective_start_date AND paf.effective_end_date;
gXMLTable.DELETE;