[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT past.assignment_status_type_id
FROM per_assignment_status_types past
WHERE past.user_status = c_status_name
AND past.legislation_code = 'AU';
SELECT pdb.defined_balance_id
FROM pay_defined_balances pdb
,pay_balance_types pbt
,pay_balance_dimensions pbd
WHERE pdb.balance_type_id = pbt.balance_type_id
AND pdb.balance_dimension_id = pbd.balance_dimension_id
AND pbt.legislation_code = 'AU'
AND pbd.legislation_code = 'AU'
AND pbt.balance_name = 'Foreign Worker Days'
AND pbd.dimension_name = '_ASG_LE_PTD';
SELECT 'Y'
FROM DUAL
WHERE EXISTS
( SELECT 1
FROM per_assignments_f paf
,hr_soft_coding_keyflex hsc
WHERE paf.assignment_id = c_assignment_id
AND paf.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
AND hsc.segment1 = c_tax_unit_id
AND paf.assignment_status_type_id = c_status_id
AND paf.effective_start_date <= c_end_date
AND paf.effective_end_date >= c_start_date
);
SELECT pps.actual_termination_date
FROM per_assignments_f paf
,hr_soft_coding_keyflex hsc
,per_periods_of_service pps
WHERE paf.assignment_id = c_assignment_id
AND paf.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
AND hsc.segment1 LIKE c_tax_unit_id
AND paf.period_of_service_id = pps.period_of_service_id
AND pps.actual_termination_date IS NOT NULL
AND pps.actual_termination_date BETWEEN c_start_date AND c_end_date
AND pps.actual_termination_date BETWEEN paf.effective_start_date and paf.effective_end_date
AND paf.assignment_status_type_id = c_status_id;
SELECT paf.assignment_id
,paf.effective_start_date
,paf.effective_end_date
,paf.payroll_id
,hsc.segment1 tax_unit_id
FROM per_assignments_f paf
,hr_soft_coding_keyflex hsc
WHERE paf.assignment_id = c_assignment_id
AND paf.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
AND hsc.segment1 LIKE c_tax_unit_id
AND to_char(paf.assignment_status_type_id) like c_status_id
AND paf.effective_start_date <= c_end_date
AND paf.effective_end_date >= c_start_date
ORDER BY paf.effective_start_date ASC;
SELECT pdb.defined_balance_id
FROM pay_defined_balances pdb
,pay_balance_dimensions pbd
,pay_balance_types pbt
WHERE pbt.balance_type_id = c_balance_type_id
AND pdb.balance_type_id = pbt.balance_type_id
AND pdb.balance_dimension_id = pbd.balance_dimension_id
AND pbd.dimension_name = c_dim_name;
l_def_bal_tab.DELETE;
SELECT 'Y'
FROM DUAL
WHERE EXISTS
( SELECT '1'
FROM per_assignments_f paf
,hr_soft_coding_keyflex hsc
,per_time_periods ptp
WHERE ptp.payroll_id = c_payroll_id
AND ptp.time_period_id = c_time_period_id
AND paf.assignment_id = c_assignment_id
AND paf.effective_start_date <= ptp.end_date
AND paf.effective_end_date >= ptp.start_date
AND paf.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
AND hsc.segment1 <> c_tax_unit_id
);
SELECT 'Y'
FROM DUAL
WHERE EXISTS
( SELECT '1'
FROM per_assignments_f paf
,hr_soft_coding_keyflex hsc
,per_time_periods ptp
WHERE ptp.payroll_id = c_payroll_id
AND ptp.time_period_id = c_time_period_id
AND paf.assignment_id = c_assignment_id
AND ptp.end_date BETWEEN paf.effective_start_date AND paf.effective_end_date
AND paf.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
AND hsc.segment1 = c_tax_unit_id
);
SELECT ptp.time_period_id
,GREATEST(paf.effective_start_date,ptp.start_date) prorate_start
,LEAST(paf.effective_end_date,ptp.end_date) prorate_end
FROM per_assignments_f paf
,per_time_periods ptp
WHERE paf.assignment_id = c_assignment_id
AND ptp.payroll_id = c_payroll_id
AND ptp.time_period_id = c_time_period_id
AND paf.assignment_status_type_id = c_status_id
AND ptp.start_date <= paf.effective_end_date
AND ptp.end_date >= paf.effective_start_date;
SELECT ptp.time_period_id
,GREATEST(c_start_date,c_fw_start_date,ptp.start_date) prorate_start
,LEAST(c_end_date,c_fw_end_date,ptp.end_date) prorate_end
,((ptp.end_date - ptp.start_date) + 1) denom_factor
,ptp.start_date
,ptp.end_date
,ptp.payroll_id
,pay_au_foreign_workers.is_le_changed(c_assignment_id,p_tax_unit_id,ptp.payroll_id,ptp.time_period_id) le_change_flag
FROM per_assignments_f paf
,per_time_periods ptp
WHERE paf.assignment_id = c_assignment_id
AND c_fw_end_date BETWEEN paf.effective_start_date AND paf.effective_end_date
AND paf.payroll_id = ptp.payroll_id
AND ptp.start_date <= c_fw_end_date
AND ptp.end_date >= c_fw_start_date
AND ptp.start_date <= c_end_date
AND ptp.end_date >= c_start_date;
SELECT TO_NUMBER(SUBSTR(MAX(LPAD(paa.action_sequence,15,'0')||paa.assignment_action_id),16)) assignment_action_id
-- ,MAX(paa.action_sequence) action_sequence
FROM pay_assignment_actions paa,
pay_payroll_actions ppa,
per_assignments_f paf,
per_time_periods ptp
WHERE paa.assignment_id = paf.assignment_id
AND paf.assignment_id = c_assignment_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.effective_date BETWEEN c_start_date AND c_end_date /* Join Ensures I pick Ass Act within year only */
AND ppa.payroll_id = paf.payroll_id
AND ppa.action_type IN ('R', 'Q', 'I', 'V', 'B')
AND ppa.effective_date BETWEEN paf.effective_start_date and paf.effective_end_date
AND paa.action_status = 'C'
AND paa.tax_unit_id = c_tax_unit_id
AND ptp.time_period_id = c_time_period_id
AND ppa.date_earned BETWEEN ptp.start_date AND ptp.end_date;
SELECT TO_NUMBER(SUBSTR(MAX(LPAD(paa.action_sequence,15,'0')||paa.assignment_action_id),16)) assignment_action_id
-- ,MAX(paa.action_sequence) action_sequence
FROM pay_assignment_actions paa,
pay_payroll_actions ppa,
per_assignments_f paf
WHERE paa.assignment_id = paf.assignment_id
AND paf.assignment_id = c_assignment_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.effective_date BETWEEN c_start_date AND c_end_date /* Join Ensures I pick Ass Act within year only */
AND ppa.payroll_id = paf.payroll_id
AND ppa.action_type IN ('R', 'Q', 'I', 'V', 'B')
AND ppa.effective_date BETWEEN paf.effective_start_date and paf.effective_end_date
AND paa.action_status = 'C'
AND paa.tax_unit_id = c_tax_unit_id;
l_ytd_balance_tab.DELETE ;
l_output_tab.DELETE;
l_return_tab.DELETE;
l_ptd_balance_tab.DELETE ;
l_output_tab.DELETE;
l_return_tab.DELETE;