[Home] [Help]
57: from
58: per_all_assignments_f paf,
59: per_periods_of_service pos,
60: pay_payroll_actions ppa,
61: pay_process_events ppe
62: where ppa.payroll_action_id = cp_pactid
63: and paf.payroll_id = cp_payroll_id
64: and ppe.effective_date between paf.effective_start_date
65: and paf.effective_end_date
144: --
145: cursor get_dates (assact_id number, p_change_type varchar2) is
146: select nvl(min(ppe.effective_date), hr_api.g_eot)
147: effective_date
148: from pay_process_events ppe,
149: pay_assignment_actions paa
150: where paa.assignment_action_id = assact_id
151: and paa.assignment_id = ppe.assignment_id
152: and change_type = p_change_type;
347: cursor csr_min_date(cp_creation_date_from date,
348: cp_change_type varchar2,
349: cp_ass_id number) is
350: select least(effective_date)
351: from pay_process_events
352: where creation_date > cp_creation_date_from
353: and change_type = cp_change_type
354: and status <> 'C'
355: and assignment_id = cp_ass_id;
1266: where paa.assignment_action_id = p_assignment_action_id
1267: and paa.payroll_action_id = ppa.payroll_action_id
1268: and paa.action_status = 'C'
1269: and exists (select ''
1270: from pay_process_events ppe
1271: where ppe.assignment_id = paa.assignment_id
1272: and ppe.change_type in ('GRE', 'DATE_EARNED', 'DATE_PROCESSED')
1273: and ppe.creation_date > cp_last_cc_run_date
1274: and ppe.effective_date <= nvl(ppa.date_earned,ppa.effective_date)
1297: pay_action_interlocks pai1
1298: where pai1.locked_action_id = paa.assignment_action_id
1299: and pai1.locking_action_id = paa1.assignment_action_id)
1300: and (exists (select ''
1301: from pay_process_events ppe
1302: where ppe.assignment_id = paa.assignment_id
1303: and ppe.effective_date < ppa.effective_date
1304: and ppe.change_type in ('PAYMENT')
1305: and ppe.creation_date > cp_last_cc_run_date
1313: and pai.locked_action_id = paa2.assignment_action_id
1314: and paa2.payroll_action_id = ppa2.payroll_action_id
1315: and ppa2.action_type in ('R','Q')
1316: and exists (select ''
1317: from pay_process_events ppe
1318: where ppe.assignment_id = paa2.assignment_id
1319: and ppe.effective_date < ppa2.effective_date
1320: and ppe.change_type in ('GRE', 'DATE_EARNED', 'DATE_PROCESSED')
1321: and ppe.creation_date > cp_last_cc_run_date
1337: pay_action_interlocks pai1
1338: where pai1.locked_action_id = paa.assignment_action_id
1339: and pai1.locking_action_id = paa1.assignment_action_id)
1340: and exists (select ''
1341: from pay_process_events ppe
1342: where ppe.assignment_id = paa.assignment_id
1343: and ppe.effective_date < ppa.effective_date
1344: and ppe.change_type in ('COST_CENTRE')
1345: and ppe.creation_date > cp_last_cc_run_date
1352: and pai.locked_action_id = paa2.assignment_action_id
1353: and paa2.payroll_action_id = ppa2.payroll_action_id
1354: and ppa2.action_type in ('R','Q')
1355: and exists (select ''
1356: from pay_process_events ppe
1357: where ppe.assignment_id = paa2.assignment_id
1358: and ppe.effective_date < ppa2.effective_date
1359: and ppe.change_type in ('GRE', 'DATE_EARNED', 'DATE_PROCESSED')
1360: and ppe.creation_date > cp_last_cc_run_date
1970: cursor get_min_dates(p_asg_id number, p_cca_date date, p_sysdate date)
1971: is
1972: select min(effective_date) effective_date,
1973: change_type
1974: from pay_process_events
1975: where assignment_id = p_asg_id
1976: and creation_date between p_cca_date
1977: and p_sysdate
1978: and change_type in ('DATE_PROCESSED', 'DATE_EARNED')
1980: order by change_type desc;
1981:
1982: cursor get_group_events(p_cca_date date, p_sysdate date) is
1983: select pdt.table_name,ppe.surrogate_key
1984: from pay_process_events ppe,
1985: pay_event_updates peu,
1986: pay_dated_tables pdt
1987: where ppe.assignment_id is null
1988: and ppe.creation_date between p_cca_date
1992: and peu.dated_table_id = pdt.dated_table_id
1993: and pdt.table_name in ('PAY_GRADE_RULES_F','PQH_RATE_MATRIX_RATES_F','FF_GLOBALS_F','PAY_USER_COLUMN_INSTANCES_F'); /*Added for Bug 8302596 */
1994:
1995: l_effective_date date;
1996: l_change_type pay_process_events.change_type%type;
1997: l_cca_date date;
1998: run_counts number;
1999: l_update_cc_date boolean;
2000: new_cc_date date;
1999: l_update_cc_date boolean;
2000: new_cc_date date;
2001: old_cc_date date;
2002: l_table_name pay_dated_tables.table_name%type;
2003: l_surrogate_key pay_process_events.surrogate_key%type;
2004: l_grp_event_valid varchar2(5);
2005:
2006: begin
2007: