[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT tpd.start_date,
tpd.end_date
FROM per_time_periods tpd
WHERE payroll_id = p_payroll_id
AND ((start_date >= p_start_date
AND end_date < p_end_date )
OR
(start_date <= p_start_date
AND end_date >= p_start_date ))
ORDER BY start_date DESC;
SELECT tpd.end_date
FROM per_time_periods tpd
WHERE tpd.payroll_id = p_payroll_id
AND p_start_date BETWEEN
tpd.start_date
AND tpd.end_date;
SELECT MAX(service.date_start) hire_date
FROM per_periods_of_service service,
per_assignments_f asg
WHERE asg.assignment_id = p_asg_id
AND p_end_date BETWEEN
asg.effective_start_date
AND asg.effective_end_date
AND asg.person_id = service.person_id
AND service.date_start BETWEEN
asg.effective_start_date
AND asg.effective_end_date
AND service.date_start <= p_end_date
AND service.date_start >= p_start_date;
SELECT 1
FROM pay_taxability_rules_dates trd,
pay_taxability_rules txr
WHERE txr.classification_id = p_class_id
AND txr.tax_type = 'EIM'
AND txr.tax_category = p_tax_cat
AND trd.taxability_rules_date_id = txr.taxability_rules_date_id
AND p_eff_date BETWEEN trd.valid_date_from
AND trd.valid_date_to;
SELECT tpd.period_type,
tpd.start_date,
tpd.end_date
FROM per_time_periods tpd
WHERE tpd.payroll_id = p_payroll_id
AND p_date BETWEEN tpd.start_date
AND tpd.end_date;
SELECT /*+ RULE */
SUM(NVL(rrv.result_value, 0) * blf.scale) total_dp_hours
FROM pay_ca_emp_fed_tax_info_f fti,
pay_assignment_actions asa,
pay_payroll_actions pya,
pay_run_results rrs,
pay_run_result_values rrv,
pay_element_types_f ele,
pay_input_values_f ipv,
pay_balance_feeds_f blf,
pay_balance_types blt
WHERE asa.assignment_id = p_asg_id
AND asa.tax_unit_id = p_gre
AND pya.payroll_id = p_payroll_id
AND pya.payroll_action_id = asa.payroll_action_id
AND pya.effective_date BETWEEN p_start_date
AND p_end_date
AND fti.assignment_id = p_asg_id
AND NVL(fti.ei_exempt_flag,'N') = 'N'
AND pya.effective_date BETWEEN fti.effective_start_date
AND fti.effective_end_date
AND fti.assignment_id = asa.assignment_id
AND rrs.assignment_action_id = asa.assignment_action_id
AND ele.element_type_id = rrs.element_type_id
AND pay_ca_roe_ei_pkg.date_paid_or_date_earned
(ele.element_type_id,
'DP',
ele.element_information3) = 'TRUE'
AND pya.effective_date BETWEEN ele.effective_start_date
AND ele.effective_end_date
AND rrv.run_result_id = rrs.run_result_id
AND ipv.input_value_id = rrv.input_value_id
AND pya.effective_date BETWEEN ipv.effective_start_date
AND ipv.effective_end_date
AND blf.input_value_id = ipv.input_value_id
AND pya.effective_date BETWEEN blf.effective_start_date
AND blf.effective_end_date
AND blf.balance_type_id = blt.balance_type_id
AND blt.balance_name = 'EI Hours'
AND blt.legislation_code = 'CA';
SELECT /*+ RULE */
SUM(NVL(rrv.result_value, 0) * blf.scale) total_dp_hours
FROM pay_assignment_actions asa,
pay_payroll_actions pya,
pay_run_results rrs,
pay_run_result_values rrv,
pay_element_types_f ele,
pay_input_values_f ipv,
pay_balance_feeds_f blf,
pay_balance_types blt
WHERE asa.assignment_id = p_asg_id
AND asa.tax_unit_id = p_gre
AND pya.payroll_id = p_payroll_id
AND pya.payroll_action_id = asa.payroll_action_id
AND pya.effective_date BETWEEN p_start_date
AND p_end_date
AND rrs.assignment_action_id = asa.assignment_action_id
AND ele.element_type_id = rrs.element_type_id
AND pay_ca_roe_ei_pkg.date_paid_or_date_earned
(ele.element_type_id,
'DP',
ele.element_information3) = 'TRUE'
AND pya.effective_date BETWEEN ele.effective_start_date
AND ele.effective_end_date
AND rrv.run_result_id = rrs.run_result_id
AND ipv.input_value_id = rrv.input_value_id
AND pya.effective_date BETWEEN ipv.effective_start_date
AND ipv.effective_end_date
AND blf.input_value_id = ipv.input_value_id
AND pya.effective_date BETWEEN blf.effective_start_date
AND blf.effective_end_date
AND blf.balance_type_id = blt.balance_type_id
AND blt.balance_name = 'EI Hours'
AND blt.legislation_code = 'CA';
SELECT /*+ RULE */
SUM(NVL(rrv.result_value, 0) * blf.scale) total_de_hours
FROM pay_ca_emp_fed_tax_info_f fti,
pay_assignment_actions asa,
pay_payroll_actions pya,
pay_run_results rrs,
pay_run_result_values rrv,
pay_element_types_f ele,
pay_input_values_f ipv,
pay_balance_feeds_f blf,
pay_balance_types blt
WHERE asa.assignment_id = p_asg_id
AND asa.tax_unit_id = p_gre
AND pya.payroll_id = p_payroll_id
AND pya.payroll_action_id = asa.payroll_action_id
AND pya.date_earned BETWEEN p_start_date
AND p_end_date
AND fti.assignment_id = p_asg_id
AND fti.assignment_id = asa.assignment_id
AND NVL(fti.ei_exempt_flag,'N') = 'N'
AND pya.date_earned BETWEEN fti.effective_start_date
AND fti.effective_end_date
AND rrs.assignment_action_id = asa.assignment_action_id
AND ele.element_type_id = rrs.element_type_id
AND pay_ca_roe_ei_pkg.date_paid_or_date_earned
(ele.element_type_id,
'DE',
ele.element_information3) = 'TRUE'
AND pya.date_earned BETWEEN ele.effective_start_date
AND ele.effective_end_date
AND rrv.run_result_id = rrs.run_result_id
AND ipv.input_value_id = rrv.input_value_id
AND pya.date_earned BETWEEN ipv.effective_start_date
AND ipv.effective_end_date
AND blf.input_value_id = ipv.input_value_id
AND pya.date_earned BETWEEN blf.effective_start_date
AND blf.effective_end_date
AND blf.balance_type_id = blt.balance_type_id
AND blt.balance_name = 'EI Hours'
AND blt.legislation_code = 'CA';
SELECT /*+ RULE */
SUM(NVL(rrv.result_value, 0) * blf.scale) total_de_hours
FROM pay_assignment_actions asa,
pay_payroll_actions pya,
pay_run_results rrs,
pay_run_result_values rrv,
pay_element_types_f ele,
pay_input_values_f ipv,
pay_balance_feeds_f blf,
pay_balance_types blt
WHERE asa.assignment_id = p_asg_id
AND asa.tax_unit_id = p_gre
AND pya.payroll_id = p_payroll_id
AND pya.payroll_action_id = asa.payroll_action_id
AND pya.date_earned BETWEEN p_start_date
AND p_end_date
AND rrs.assignment_action_id = asa.assignment_action_id
AND ele.element_type_id = rrs.element_type_id
AND pay_ca_roe_ei_pkg.date_paid_or_date_earned
(ele.element_type_id,
'DE',
ele.element_information3) = 'TRUE'
AND pya.date_earned BETWEEN ele.effective_start_date
AND ele.effective_end_date
AND rrv.run_result_id = rrs.run_result_id
AND ipv.input_value_id = rrv.input_value_id
AND pya.date_earned BETWEEN ipv.effective_start_date
AND ipv.effective_end_date
AND blf.input_value_id = ipv.input_value_id
AND pya.date_earned BETWEEN blf.effective_start_date
AND blf.effective_end_date
AND blf.balance_type_id = blt.balance_type_id
AND blt.balance_name = 'EI Hours'
AND blt.legislation_code = 'CA';
SELECT /*+ RULE */
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
1, NVL(rrv.result_value, 0)*blf.scale,0)),0) +
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
2, NVL(rrv.result_value, 0)*blf.scale,0)),0) +
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
3, NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
4, NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
5, NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
6, NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
7, NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
8, NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
9, NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
10,NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
11,NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
12,NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
13,NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
14,NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
15,NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
16,NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
17,NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
18,NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
19,NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
20,NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
21,NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
22,NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
23,NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
24,NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
25,NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
26,NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
27,NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
28,NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
29,NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
30, NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
31, NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
32, NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
33, NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
34, NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
35, NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
36, NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
37,NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
38,NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
39,NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
40,NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
41,NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
42,NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
43,NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
44,NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
45,NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
46,NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
47,NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
48,NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
49,NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
50,NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
51,NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
52,NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
53,NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
54,NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
55,NVL(rrv.result_value, 0)*blf.scale,0)),0)
FROM pay_assignment_actions asa,
pay_payroll_actions pya,
pay_run_results rrs,
pay_run_result_values rrv,
pay_element_types_f ele,
pay_element_classifications elc,
pay_input_values_f ipv,
pay_balance_feeds_f blf,
pay_balance_types blt
WHERE asa.assignment_id = p_asg_id
AND asa.tax_unit_id = p_gre
AND pya.payroll_id = p_payroll_id
AND pya.payroll_action_id = asa.payroll_action_id
AND pya.effective_date BETWEEN p_start_date
AND p_end_date
AND rrs.assignment_action_id = asa.assignment_action_id
AND ele.element_type_id = rrs.element_type_id
AND NVL(ele.element_information3,'DP') = 'DP'
AND pya.effective_date BETWEEN ele.effective_start_date
AND ele.effective_end_date
AND elc.classification_id = ele.classification_id
AND elc.classification_name IN ('Earnings',
'Supplemental Earnings',
'Taxable Benefits',
'Balance Initialization')
AND pay_ca_roe_ei_pkg.taxability_rule_exists(elc.classification_name,
elc.classification_id,
ele.element_information1,
pya.effective_date,
blt.tax_type) = 'TRUE'
AND rrv.run_result_id = rrs.run_result_id
AND ipv.input_value_id = rrv.input_value_id
AND pya.effective_date BETWEEN ipv.effective_start_date
AND ipv.effective_end_date
AND blf.input_value_id = ipv.input_value_id
AND pya.effective_date BETWEEN blf.effective_start_date
AND blf.effective_end_date
AND blf.balance_type_id = blt.balance_type_id
AND blt.balance_name IN ('Regular Earnings',
'Supplemental Earnings for EI',
'Taxable Benefits for EI')
AND blt.legislation_code = 'CA'
AND NVL(pay_ca_emp_tax_inf.get_tax_detail_char(p_asg_id,
SYSDATE,SYSDATE, pya.effective_date, 'EIEXEMPT'),'N') = 'N';
SELECT /*+ RULE */
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
1, NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
2, NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
3, NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
4, NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
5, NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
6, NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
7, NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
8, NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
9, NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
10,NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
11,NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
12,NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
13,NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
14,NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
15,NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
16,NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
17,NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
18,NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
19,NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
20,NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
21,NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
22,NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
23,NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
24,NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
25,NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
26,NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
27,NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
28, NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
29, NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
30, NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
31, NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
32, NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
33, NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
34, NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
35, NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
36,NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
37,NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
38,NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
39,NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
40,NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
41,NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
42,NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
43,NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
44,NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
45,NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
46,NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
47,NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
48,NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
49,NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
50,NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
51,NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
52,NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
53,NVL(rrv.result_value, 0)*blf.scale,0)),0)
FROM pay_assignment_actions asa,
pay_payroll_actions pya,
pay_run_results rrs,
pay_run_result_values rrv,
pay_element_types_f ele,
pay_element_classifications elc,
pay_input_values_f ipv,
pay_balance_feeds_f blf,
pay_balance_types blt
WHERE asa.assignment_id = p_asg_id
AND asa.tax_unit_id = p_gre
AND pya.payroll_id = p_payroll_id
AND pya.payroll_action_id = asa.payroll_action_id
AND pya.effective_date BETWEEN p_start_date
AND p_end_date
AND rrs.assignment_action_id = asa.assignment_action_id
AND ele.element_type_id = rrs.element_type_id
AND NVL(ele.element_information3,'DP') = 'DP'
AND pya.effective_date BETWEEN ele.effective_start_date
AND ele.effective_end_date
AND elc.classification_id = ele.classification_id
AND elc.classification_name IN ('Earnings',
'Supplemental Earnings',
'Taxable Benefits',
'Balance Initialization')
AND pay_ca_roe_ei_pkg.taxability_rule_exists(elc.classification_name,
elc.classification_id,
ele.element_information1,
pya.effective_date,
blt.tax_type) = 'TRUE'
AND rrv.run_result_id = rrs.run_result_id
AND ipv.input_value_id = rrv.input_value_id
AND pya.effective_date BETWEEN ipv.effective_start_date
AND ipv.effective_end_date
AND blf.input_value_id = ipv.input_value_id
AND pya.effective_date BETWEEN blf.effective_start_date
AND blf.effective_end_date
AND blf.balance_type_id = blt.balance_type_id
AND blt.balance_name IN ('Regular Earnings',
'Supplemental Earnings for EI',
'Taxable Benefits for EI')
AND blt.legislation_code = 'CA'
AND NVL(pay_ca_emp_tax_inf.get_tax_detail_char(p_asg_id,
SYSDATE,SYSDATE, pya.effective_date, 'EIEXEMPT'),'N') = 'N';
SELECT /*+ RULE */
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
1, NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
2, NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
3, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
4, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
5, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
6, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
7, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
8, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
9, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
10, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
11, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
12, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
13, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
14, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
15, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
16, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
17, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
18, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
19, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
20, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
21, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
22, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
23, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
24, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
25, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
26, NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
27, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
28, NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
29, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
30, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
31, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
32, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
33, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
34, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
35, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
36, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
37, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
38, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
39, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
40, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
41, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
42, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
43, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
44, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
45, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
46, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
47, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
48, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
49, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
50, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
51, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
52, NVL(rrv.result_value, 0)*blf.scale,0)),0),
NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
53, NVL(rrv.result_value, 0)*blf.scale,0)),0)
FROM pay_assignment_actions asa,
pay_payroll_actions pya,
pay_run_results rrs,
pay_run_result_values rrv,
pay_element_types_f ele,
pay_element_classifications elc,
pay_input_values_f ipv,
pay_balance_feeds_f blf,
pay_balance_types blt
WHERE asa.assignment_id = p_asg_id
AND asa.tax_unit_id = p_gre
AND pya.payroll_id = p_payroll_id
AND pya.payroll_action_id = asa.payroll_action_id
AND pya.date_earned BETWEEN p_start_date
AND p_end_date
AND rrs.assignment_action_id = asa.assignment_action_id
AND ele.element_type_id = rrs.element_type_id
AND NVL(ele.element_information3,'DP') = 'DE'
AND pya.date_earned BETWEEN ele.effective_start_date
AND ele.effective_end_date
AND elc.classification_id = ele.classification_id
AND elc.classification_name IN ('Earnings',
'Supplemental Earnings',
'Taxable Benefits')
AND pay_ca_roe_ei_pkg.taxability_rule_exists(elc.classification_name,
elc.classification_id,
ele.element_information1,
pya.date_earned,
blt.tax_type) = 'TRUE'
AND rrv.run_result_id = rrs.run_result_id
AND ipv.input_value_id = rrv.input_value_id
AND pya.date_earned BETWEEN ipv.effective_start_date
AND ipv.effective_end_date
AND blf.input_value_id = ipv.input_value_id
AND pya.date_earned BETWEEN blf.effective_start_date
AND blf.effective_end_date
AND blf.balance_type_id = blt.balance_type_id
AND blt.balance_name IN ('Regular Earnings',
'Supplemental Earnings for EI',
'Taxable Benefits for EI')
AND blt.legislation_code = 'CA'
AND NVL(pay_ca_emp_tax_inf.get_tax_detail_char(p_asg_id,
SYSDATE, SYSDATE, pya.date_earned, 'EIEXEMPT'),'N') = 'N';
SELECT COUNT(*)
FROM per_time_periods
WHERE payroll_id = p_payroll_id
AND end_date >= p_start_date1
AND start_date <= p_end_date;
select max(service.date_start) hire_date
from per_periods_of_service service,
per_assignments_f asg
where asg.assignment_id = p_assignment_id
and p_end_date BETWEEN
asg.effective_start_date
AND asg.effective_end_date
and asg.person_id = service.person_id(+)
and service.date_start <= p_end_date;
select
ptp.end_date + 1
from
per_time_periods ptp
where
ptp.payroll_id = p_payroll_id and
p_start_date between
ptp.start_date and ptp.end_date;
select start_date
from per_time_periods
where payroll_id = p_payroll_id
and start_date = p_start_date;
select
ptp2.start_date,
ptp2.end_date
from
per_time_periods ptp,
per_time_periods ptp1,
per_time_periods ptp2
where
ptp.payroll_id = p_payroll_id and
p_date between
ptp.start_date and
ptp.end_date and
ptp.payroll_id = ptp1.payroll_id and
ptp.end_date + 1 between
ptp1.start_date and
ptp1.end_date and
ptp1.payroll_id = ptp2.payroll_id and
ptp1.end_date + 1 between
ptp2.start_date and
ptp2.end_date;
select
ppa.payroll_action_id,
ppa.effective_date,
ppa.start_date
from
pay_payroll_actions ppa,
pay_assignment_actions paa
where
paa.assignment_id = p_assignment_id and
paa.tax_unit_id = p_gre and
ppa.payroll_id = p_payroll_id and
paa.payroll_action_id = ppa.payroll_action_id and
ppa.action_type = 'L' and
ppa.action_status = 'C' and
ppa.start_date between p_start_date and
p_end_date;
select
ptp.start_date,
ptp.end_date
from
per_time_periods ptp
where
ptp.payroll_id = p_payroll_id and
p_date between
ptp.start_date and
ptp.end_date;
select
'X'
from
pay_payroll_actions ppa,
pay_assignment_actions paa
where
ppa.action_status = 'C' and
ppa.action_type in ('Q','R') and
ppa.date_earned between p_pay_period_start_date and
p_pay_period_end_date and
ppa.payroll_action_id = paa.payroll_action_id and
paa.assignment_id = p_assignment_id;
select
pet.element_type_id,
pee.element_entry_id,
pee.creator_type,
pee.source_id,
pee.source_asg_action_id,
nvl(pet.element_information3,'DE') element_information3,
peev.screen_entry_value,
pec.classification_name,
pec.classification_id,
pet.element_information1
from
pay_element_entries_f pee,
pay_element_links_f pel,
pay_element_types_f pet,
pay_element_entry_values_f peev,
pay_input_values_f piv,
pay_retro_component_usages prcu,
pay_element_span_usages pesu,
pay_retro_components prc,
pay_time_spans pts,
pay_element_classifications pec
where
pee.assignment_id = p_assignment_id and
pee.creator_type in ('EE','RR') and
pee.effective_start_date <= p_end_date and
pee.effective_end_date >= p_start_date and
pee.element_link_id = pel.element_link_id and
pel.effective_start_date <= p_end_date and
pel.effective_end_date >= p_start_date and
pel.element_type_id = pet.element_type_id and
pet.effective_start_date <= p_end_date and
pet.effective_end_date >= p_start_date and
pet.element_type_id = pesu.retro_element_type_id and
pesu.time_span_id = pts.time_span_id and
pesu.retro_component_usage_id = prcu.retro_component_usage_id and
prcu.retro_component_id = prc.retro_component_id and
pts.creator_id = prc.retro_component_id and
prc.legislation_code = 'CA' and
prc.short_name = 'Retropay' and
pee.element_entry_id = peev.element_entry_id and
peev.effective_start_date <= p_end_date and
peev.effective_end_date >= p_start_date and
peev.input_value_id = piv.input_value_id and
piv.element_type_id = pet.element_type_id and
piv.effective_start_date <= p_end_date and
piv.effective_end_date >= p_start_date and
piv.name = 'Pay Value' and
pet.classification_id = pec.classification_id and
pec.classification_name in ('Earnings',
'Supplemental Earnings',
'Taxable Benefits');
select ppa.date_earned
from
pay_run_results prr,
pay_assignment_actions paa,
pay_payroll_actions ppa
where ppa.payroll_action_id = paa.payroll_action_id
and ppa.date_earned between p_start_date
and p_end_date
and prr.assignment_action_id = paa.assignment_action_id
and prr.run_result_id = p_run_result_id;
select ppa.date_earned
from
pay_assignment_actions paa,
pay_payroll_actions ppa
where ppa.payroll_action_id = paa.payroll_action_id
and ppa.date_earned between p_start_date
and p_end_date
and paa.assignment_action_id = p_asg_action_id;
SELECT
'X'
FROM
pay_ca_emp_fed_tax_info_f
WHERE
assignment_id = p_assignment_id AND
effective_start_date <= p_ftr_end_date AND
effective_end_date >= p_ftr_start_date;
SELECT
pbt.balance_type_id
FROM
pay_balance_types pbt
WHERE
pbt.balance_name = 'EI Hours' and
pbt.legislation_code = 'CA';
SELECT DISTINCT ele.element_type_id
FROM pay_element_types_f ele,
pay_template_core_objects tco1,
pay_shadow_element_types sel,
pay_element_templates etp,
pay_template_core_objects tco2
WHERE tco2.core_object_type = 'ET'
AND etp.template_id = tco2.template_id
AND sel.template_id = etp.template_id
AND sel.element_name NOT LIKE ('%Special Inputs')
AND sel.element_type_id = tco1.shadow_object_id
AND tco1.core_object_type = 'ET'
AND ele.element_type_id = tco1.core_object_id
AND NVL(ele.element_information3,'DP') = p_dp_or_de
UNION ALL
SELECT DISTINCT ele.element_type_id
FROM pay_element_types_f ele,
pay_element_classifications pec
WHERE ele.business_group_id is NULL
AND ele.legislation_code = 'CA'
AND pec.legislation_code = 'CA'
AND pec.classification_name = 'Earnings'
AND ele.classification_id = pec.classification_id
AND p_dp_or_de = 'DE'; */
SELECT
pet.element_type_id
FROM
pay_element_types_f pet
WHERE
pet.business_group_id = p_bg_id and
NVL(pet.element_information3,'DP') = p_dp_or_de and
EXISTS
(SELECT 'X' FROM
pay_input_values_f piv,
pay_balance_feeds_f pbf
WHERE
piv.element_type_id = pet.element_type_id AND
piv.input_value_id = pbf.input_value_id AND
pbf.balance_type_id = l_bal_type_id)
UNION ALL
SELECT DISTINCT ele.element_type_id
FROM pay_element_types_f ele,
pay_element_classifications pec
WHERE ele.business_group_id is NULL
AND ele.legislation_code = 'CA'
AND pec.legislation_code = 'CA'
AND pec.classification_name = 'Earnings'
AND ele.classification_id = pec.classification_id
AND p_dp_or_de = 'DE';