[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
hou.name, hou.business_group_id
FROM
hr_organization_units hou
WHERE hou.organization_id = p_legal_employer_id;
SELECT
pay.payroll_name
FROM
pay_payrolls_f pay
WHERE pay.payroll_id = p_payroll_id
AND p_effective_date BETWEEN pay.effective_start_date AND pay.effective_end_date;
SELECT
fnd_number.canonical_to_number(global_value) -- 10034051
FROM
ff_globals_f
WHERE global_name = p_global_name
AND legislation_code = 'DK'
AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
SELECT
defined_balance_id
FROM
pay_balance_types pbt,
pay_defined_balances pdb,
pay_balance_dimensions pbd
WHERE pdb.balance_type_id = pbt.balance_type_id
AND pdb.balance_dimension_id = pbd.balance_dimension_id
AND pbt.balance_name = bal_name
AND pbd.database_item_suffix = dim_name
AND pbt.legislation_code = 'DK'
AND pdb.legislation_code = 'DK'
AND pbd.legislation_code = 'DK';
SELECT
asg.assignment_id assignment_id,
MAX(pap.per_information3) trainee,
MAX(paa.assignment_action_id) assignment_action_id,
MAX(pap.date_of_birth) date_of_birth, --9059957
MAX(asg.payroll_id) payroll_id --9059957
FROM
per_all_people_f pap,
per_all_assignments_f asg,
pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_run_results prr,
pay_element_types_f pet
WHERE pap.person_id = asg.person_id
AND asg.payroll_id = ppa.payroll_id
AND asg.assignment_id = paa.assignment_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.action_type IN ( 'R' , 'B' , 'I' , 'Q' , 'V' )
AND paa.assignment_action_id = prr.assignment_action_id
AND pet.element_type_id = prr.element_type_id
AND pet.legislation_code = 'DK'
AND asg.payroll_id=NVL(p_payroll_id,asg.payroll_id)
AND ppa.business_group_id = p_business_group_id
AND paa.tax_unit_id = p_legal_employer_id
AND pet.element_name IN ( 'Employee ATP' , 'Employer ATP' )
AND ppa.date_earned BETWEEN p_date_from AND p_date_to
AND asg.assignment_status_type_id = 1
AND pap.current_employee_flag = 'Y'
AND ppa.date_earned BETWEEN pap.effective_start_date AND pap.effective_end_date
AND ppa.date_earned BETWEEN asg.effective_start_date AND asg.effective_end_date
AND ppa.date_earned BETWEEN pet.effective_start_date AND pet.effective_end_date
GROUP BY
asg.assignment_id;
SELECT
asg.assignment_id assignment_id,
max(paa.assignment_action_id) assignment_action_id
FROM
per_all_people_f pap,
per_all_assignments_f asg,
pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_run_results prr
WHERE pap.person_id = asg.person_id
AND pap.current_employee_flag = 'Y'
AND asg.payroll_id = ppa.payroll_id
AND asg.assignment_id = paa.assignment_id
AND asg.payroll_id = nvl(p_payroll_id
,asg.payroll_id)
AND asg.assignment_status_type_id = 1
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.action_type IN ( 'R' , 'B' , 'I' , 'Q' , 'V' )
AND ppa.business_group_id = p_business_group_id
AND ppa.date_earned BETWEEN p_date_from AND p_date_to
AND ppa.date_earned BETWEEN pap.effective_start_date AND pap.effective_end_date
AND ppa.date_earned BETWEEN asg.effective_start_date AND asg.effective_end_date
AND paa.tax_unit_id = p_legal_employer_id
AND paa.assignment_action_id = prr.assignment_action_id
AND NOT EXISTS (
SELECT
1
FROM
pay_payroll_actions ppa1,
pay_assignment_actions paa1,
pay_run_results prr1,
pay_element_types_f pet
WHERE pet.legislation_code = 'DK'
AND pet.element_name IN ( 'Employee ATP' , 'Employer ATP' )
AND pet.element_type_id = prr1.element_type_id
AND ppa1.payroll_action_id = paa1.payroll_action_id
AND paa1.assignment_id = asg.assignment_id
AND paa1.assignment_action_id = prr1.assignment_action_id
AND paa1.tax_unit_id = p_legal_employer_id
AND ppa1.action_type IN ( 'R' , 'B' , 'I' , 'Q' , 'V' )
AND ppa1.business_group_id = p_business_group_id
AND ppa1.date_earned BETWEEN p_date_from AND p_date_to
AND ppa1.date_earned BETWEEN pet.effective_start_date AND pet.effective_end_date)
GROUP BY
asg.assignment_id;
SELECT
start_date-1 before_date
FROM
per_time_periods
WHERE payroll_id = p_payroll_id
AND to_date(to_char(p_date_of_birth,'ddmm')||p_report_year,'ddmmyyyy')
BETWEEN start_date AND end_date;
SELECT
min(end_date) first_end_date,
max(end_date) last_end_date
FROM
per_time_periods
WHERE payroll_id = p_payroll_id
AND to_char(end_date,'yyyy') = p_report_year;
SELECT
max(paa.assignment_action_id) assignment_action_id
FROM
pay_payroll_actions ppa,
pay_assignment_actions paa
WHERE paa.assignment_id = p_assignment_id
AND paa.tax_unit_id = p_legal_employer_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.action_type IN ( 'R' , 'B' , 'I' , 'Q' , 'V' )
AND ppa.business_group_id = p_business_group_id
AND ppa.payroll_id = p_payroll_id
AND ppa.date_earned BETWEEN p_date_from AND p_date_to;