[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;
1214: where paa.assignment_action_id = p_assignment_action_id
1215: and paa.payroll_action_id = ppa.payroll_action_id
1216: and paa.action_status = 'C'
1217: and exists (select ''
1218: from pay_process_events ppe
1219: where ppe.assignment_id = paa.assignment_id
1220: and ppe.change_type in ('GRE', 'DATE_EARNED', 'DATE_PROCESSED')
1221: and ppe.creation_date > cp_last_cc_run_date
1222: and ppe.effective_date <= nvl(ppa.date_earned,ppa.effective_date)
1245: pay_action_interlocks pai1
1246: where pai1.locked_action_id = paa.assignment_action_id
1247: and pai1.locking_action_id = paa1.assignment_action_id)
1248: and (exists (select ''
1249: from pay_process_events ppe
1250: where ppe.assignment_id = paa.assignment_id
1251: and ppe.effective_date < ppa.effective_date
1252: and ppe.change_type in ('PAYMENT')
1253: and ppe.creation_date > cp_last_cc_run_date
1261: and pai.locked_action_id = paa2.assignment_action_id
1262: and paa2.payroll_action_id = ppa2.payroll_action_id
1263: and ppa2.action_type in ('R','Q')
1264: and exists (select ''
1265: from pay_process_events ppe
1266: where ppe.assignment_id = paa2.assignment_id
1267: and ppe.effective_date < ppa2.effective_date
1268: and ppe.change_type in ('GRE', 'DATE_EARNED', 'DATE_PROCESSED')
1269: and ppe.creation_date > cp_last_cc_run_date
1285: pay_action_interlocks pai1
1286: where pai1.locked_action_id = paa.assignment_action_id
1287: and pai1.locking_action_id = paa1.assignment_action_id)
1288: and exists (select ''
1289: from pay_process_events ppe
1290: where ppe.assignment_id = paa.assignment_id
1291: and ppe.effective_date < ppa.effective_date
1292: and ppe.change_type in ('COST_CENTRE')
1293: and ppe.creation_date > cp_last_cc_run_date
1300: and pai.locked_action_id = paa2.assignment_action_id
1301: and paa2.payroll_action_id = ppa2.payroll_action_id
1302: and ppa2.action_type in ('R','Q')
1303: and exists (select ''
1304: from pay_process_events ppe
1305: where ppe.assignment_id = paa2.assignment_id
1306: and ppe.effective_date < ppa2.effective_date
1307: and ppe.change_type in ('GRE', 'DATE_EARNED', 'DATE_PROCESSED')
1308: and ppe.creation_date > cp_last_cc_run_date
1896: cursor get_min_dates(p_asg_id number, p_cca_date date, p_sysdate date)
1897: is
1898: select min(effective_date) effective_date,
1899: change_type
1900: from pay_process_events
1901: where assignment_id = p_asg_id
1902: and creation_date between p_cca_date
1903: and p_sysdate
1904: and change_type in ('DATE_PROCESSED', 'DATE_EARNED')
1906: order by change_type desc;
1907:
1908: cursor get_group_events(p_cca_date date, p_sysdate date) is
1909: select pdt.table_name,ppe.surrogate_key
1910: from pay_process_events ppe,
1911: pay_event_updates peu,
1912: pay_dated_tables pdt
1913: where ppe.assignment_id is null
1914: and ppe.creation_date between p_cca_date
1917: and peu.event_update_id = ppe.event_update_id
1918: and peu.dated_table_id = pdt.dated_table_id;
1919:
1920: l_effective_date date;
1921: l_change_type pay_process_events.change_type%type;
1922: l_cca_date date;
1923: run_counts number;
1924: l_update_cc_date boolean;
1925: new_cc_date date;
1924: l_update_cc_date boolean;
1925: new_cc_date date;
1926: old_cc_date date;
1927: l_table_name pay_dated_tables.table_name%type;
1928: l_surrogate_key pay_process_events.surrogate_key%type;
1929: l_grp_event_valid varchar2(5);
1930:
1931: begin
1932: