[Home] [Help]
21: /* Effective date will be set to sysdate for CC*/
22: sqlstr := 'select distinct asg.person_id
23: from
24: per_assignments_f asg,
25: pay_payroll_actions pa1
26: where pa1.payroll_action_id = :payroll_action_id
27: and asg.payroll_id =
28: pay_core_utils.get_parameter(''PAYROLL_ID'',
29: pa1.legislative_parameters)
56: select distinct ppe.assignment_id,ppe.creation_date
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
73:
74: CURSOR csr_act_info(cp_actid number) is
75: select
76: pay_core_utils.get_parameter('PAYROLL_ID',ppa.legislative_parameters)
77: from pay_payroll_actions ppa
78: where ppa.payroll_action_id = cp_actid;
79:
80:
81: l_lockingactid NUMBER;
152: and change_type = p_change_type;
153: --
154: cursor get_costings(p_assact_id number, p_effdate date) is
155: select paa.assignment_action_id
156: from pay_payroll_actions ppa,
157: pay_assignment_actions paa,
158: pay_assignment_actions paa_arch
159: where paa_arch.assignment_action_id = p_assact_id
160: and paa.assignment_id = paa_arch.assignment_id
168: order by paa.action_sequence desc;
169: --
170: cursor get_prepay(p_assact_id number, p_effdate date) is
171: select paa.assignment_action_id
172: from pay_payroll_actions ppa,
173: pay_assignment_actions paa,
174: pay_assignment_actions paa_arch
175: where paa_arch.assignment_action_id = p_assact_id
176: and paa.assignment_id = paa_arch.assignment_id
186: cursor get_run(p_assact_id number, p_effdate date) is
187: select paa.assignment_action_id,
188: ppa.effective_date,
189: paa.assignment_id
190: from pay_payroll_actions ppa,
191: pay_assignment_actions paa,
192: pay_assignment_actions paa_arch
193: where paa_arch.assignment_action_id = p_assact_id
194: and paa.assignment_id = paa_arch.assignment_id
199: and ppa.effective_date >= p_effdate
200: and not exists (select ''
201: from pay_action_interlocks pai,
202: pay_assignment_actions paa2,
203: pay_payroll_actions ppa2,
204: pay_action_interlocks pai2
205: where pai.locked_action_id = paa.assignment_action_id
206: and pai.locking_action_id = paa2.assignment_action_id
207: and paa2.payroll_action_id = ppa2.payroll_action_id
210: )
211: and not exists (
212: select null
213: from pay_action_classifications acl,
214: pay_payroll_actions pa2,
215: pay_assignment_actions ac2
216: where ac2.assignment_id = paa.assignment_id
217: and pa2.payroll_action_id = ac2.payroll_action_id
218: and acl.classification_name = 'SEQUENCED'
222: )
223: and not exists (
224: select null
225: from pay_action_classifications acl,
226: pay_payroll_actions pa2,
227: pay_assignment_actions ac2
228: where ac2.assignment_id = paa.assignment_id
229: and pa2.payroll_action_id = ac2.payroll_action_id
230: and acl.classification_name = 'SEQUENCED'
233: and ac2.action_sequence > paa.action_sequence
234: and exists (select ''
235: from pay_action_interlocks pai,
236: pay_assignment_actions paa2,
237: pay_payroll_actions ppa2,
238: pay_action_interlocks pai2
239: where pai.locked_action_id = ac2.assignment_action_id
240: and pai.locking_action_id = paa2.assignment_action_id
241: and paa2.payroll_action_id = ppa2.payroll_action_id
248: cursor get_locked_actions (p_assact_id number) is
249: select paa.assignment_action_id
250: from pay_assignment_actions paa,
251: pay_action_interlocks pai,
252: pay_payroll_actions ppa
253: where pai.locked_action_id = p_assact_id
254: and pai.locking_action_id = paa.assignment_action_id
255: and paa.payroll_action_id = ppa.payroll_action_id
256: and paa.action_status = 'C'
263: paa.assignment_id,
264: pay_core_utils.get_parameter('PAYROLL_ID',ppa.legislative_parameters),
265: ppa.business_group_id
266: from pay_assignment_actions paa,
267: pay_payroll_actions ppa
268: where paa.payroll_action_id = ppa.payroll_action_id
269: and paa.assignment_action_id = cp_actid;
270:
271:
271:
272:
273: cursor csr_get_de_min(cp_assact_id number) is
274: select min(ppa.date_earned)
275: from pay_payroll_actions ppa,
276: pay_assignment_actions paa,
277: pay_assignment_actions paa_arch
278: where paa_arch.assignment_action_id = cp_assact_id
279: and paa.assignment_id = paa_arch.assignment_id
284: and not exists (
285: select 1
286: from pay_action_interlocks pai,
287: pay_assignment_actions paa2,
288: pay_payroll_actions ppa2,
289: pay_action_interlocks pai2
290: where pai.locked_action_id = paa.assignment_action_id
291: and pai.locking_action_id = paa2.assignment_action_id
292: and paa2.payroll_action_id = ppa2.payroll_action_id
295: )
296: and not exists (
297: select 1
298: from pay_action_classifications acl,
299: pay_payroll_actions pa2,
300: pay_assignment_actions ac2
301: where ac2.assignment_id = paa.assignment_id
302: and pa2.payroll_action_id = ac2.payroll_action_id
303: and acl.classification_name = 'SEQUENCED'
312: prr.source_id entry
313: , pet.recalc_event_group_id event_group
314: FROM pay_run_results prr
315: , pay_assignment_actions paa
316: , pay_payroll_actions ppa
317: , pay_assignment_actions paa_arch
318: , pay_element_types_f pet
319: WHERE paa_arch.assignment_action_id = cp_assact_id
320: and paa.assignment_id = paa_arch.assignment_id
534: --So we now fish out the eff_date corresponding to this date_earned.
535:
536: select nvl(min(ppa.effective_date), hr_api.g_eot)
537: into l_aact_min_dedate_eff
538: from pay_payroll_actions ppa,
539: pay_assignment_actions paa,
540: pay_assignment_actions paa_arch
541: where paa_arch.assignment_action_id = p_assactid
542: and paa.assignment_id = paa_arch.assignment_id
640: cursor csr_params(cp_pactid number) is
641: select
642: pay_core_utils.get_parameter('REMOVE_ACT',pa1.legislative_parameters),
643: pay_core_utils.get_parameter('PAYROLL_ID',pa1.legislative_parameters)
644: from pay_payroll_actions pa1
645: where pa1.payroll_action_id = cp_pactid;
646:
647: l_remove_act varchar2(10);
648: l_payroll_id number;
1230:
1231: cursor get_payroll (cp_asg_act_id in number) is
1232: select ppa.payroll_id
1233: from
1234: pay_payroll_actions ppa,
1235: pay_assignment_actions paa
1236: where paa.assignment_action_id = cp_asg_act_id
1237: and paa.payroll_action_id = ppa.payroll_action_id;
1238:
1250: cursor c_is_voided ( p_assignment_action_id in number ) is
1251: select intloc.locking_action_id
1252: from pay_assignment_actions assact,
1253: pay_action_interlocks intloc,
1254: pay_payroll_actions pact
1255: where intloc.locked_action_id = p_assignment_action_id
1256: and intloc.locking_action_id = assact.assignment_action_id
1257: and assact.payroll_action_id = pact.payroll_action_id
1258: and pact.action_type = 'D';
1260: cursor run_modified (p_assignment_action_id in number,
1261: cp_last_cc_run_date in date ) is
1262: select paa.assignment_action_id
1263: from
1264: pay_payroll_actions ppa,
1265: pay_assignment_actions paa
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'
1286: cursor prepay_modified (p_assignment_action_id in number,
1287: cp_last_cc_run_date in date ) is
1288: select paa.assignment_action_id
1289: from
1290: pay_payroll_actions ppa,
1291: pay_assignment_actions paa
1292: where paa.assignment_action_id = p_assignment_action_id
1293: and paa.payroll_action_id = ppa.payroll_action_id
1294: and paa.action_status = 'C'
1307: or
1308: exists (select ''
1309: from pay_action_interlocks pai,
1310: pay_assignment_actions paa2,
1311: pay_payroll_actions ppa2
1312: where pai.locking_action_id = paa.assignment_action_id
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')
1326: cursor cost_modified (p_assignment_action_id in number,
1327: cp_last_cc_run_date in date ) is
1328: select paa.assignment_action_id
1329: from
1330: pay_payroll_actions ppa,
1331: pay_assignment_actions paa
1332: where paa.assignment_action_id = p_assignment_action_id
1333: and paa.payroll_action_id = ppa.payroll_action_id
1334: and paa.action_status = 'C'
1346: )
1347: and exists (select ''
1348: from pay_action_interlocks pai,
1349: pay_assignment_actions paa2,
1350: pay_payroll_actions ppa2
1351: where pai.locking_action_id = paa.assignment_action_id
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')
2022: if (l_change_type = 'DATE_PROCESSED') then
2023:
2024: select count(*)
2025: into run_counts
2026: from pay_payroll_actions ppa,
2027: pay_assignment_actions paa,
2028: pay_assignment_actions paa_curr
2029: where paa_curr.assignment_action_id = p_assact_id
2030: and paa.assignment_id = p_asg_id
2041: elsif (l_change_type = 'DATE_EARNED') then
2042:
2043: select count(*)
2044: into run_counts
2045: from pay_payroll_actions ppa,
2046: pay_assignment_actions paa,
2047: pay_assignment_actions paa_curr
2048: where paa_curr.assignment_action_id = p_assact_id
2049: and paa.assignment_id = p_asg_id