[Home] [Help]
211: SELECT item.opt_id
212: FROM per_cagr_entitlement_items item
213: WHERE item.cagr_entitlement_item_id = p_params.entitlement_item_id
214: AND exists (select 'x'
215: from per_collective_agreements cagr, per_all_assignments_f asg
216: where cagr.status = 'A'
217: and p_params.effective_date >= cagr.start_date
218: and cagr.collective_agreement_id in (select distinct(pce.collective_agreement_id)
219: from per_cagr_entitlements pce, per_cagr_entitlement_lines_f pcel
234: AND asg.effective_end_date);
235:
236: CURSOR csr_BE_plan is
237: SELECT agr.pl_id
238: FROM per_collective_agreements agr
239: WHERE agr.collective_agreement_id = p_params.collective_agreement_id;
240:
241:
242:
241:
242:
243: CURSOR csr_BE_assignments_to_process IS
244: SELECT cagr.collective_agreement_id, asg.assignment_id, asg.grade_id, asg.person_id
245: FROM per_collective_agreements cagr, per_all_assignments_f asg
246: WHERE cagr.status = 'A'
247: AND p_params.effective_date >= cagr.start_date
248: AND cagr.collective_agreement_id in (select distinct(pce.collective_agreement_id)
249: from per_cagr_entitlements pce
264: --
265:
266: CURSOR csr_SC_drive_benmngle IS
267: SELECT pl_id
268: FROM per_collective_agreements cagr
269: WHERE cagr.COLLECTIVE_AGREEMENT_ID = p_params.COLLECTIVE_AGREEMENT_ID
270: AND p_params.effective_date BETWEEN cagr.START_DATE
271: AND nvl(cagr.END_DATE, hr_general.end_of_time)
272: AND cagr.STATUS = 'A'
328: pcel.FROM_STEP_ID,
329: pcel.TO_STEP_ID,
330: pcel.OIPL_ID,
331: pcel.ELIGY_PRFL_ID -- BEN elig profile
332: FROM per_collective_agreements cagr,
333: per_cagr_entitlements pce,
334: per_cagr_entitlement_items pcei,
335: per_cagr_entitlement_lines_f pcel
336: WHERE cagr.COLLECTIVE_AGREEMENT_ID = p_params.collective_agreement_id
359: CURSOR csr_SA_drive_benmngle IS
360: SELECT asg.COLLECTIVE_AGREEMENT_ID,
361: asg.PERSON_ID,
362: cagr.PL_ID
363: FROM per_all_assignments_f asg, per_collective_agreements cagr
364: WHERE asg.ASSIGNMENT_ID = p_params.assignment_id
365: AND p_params.effective_date BETWEEN asg.effective_start_date
366: AND asg.effective_end_date
367: AND asg.PRIMARY_FLAG = 'Y'
419: pcel.TO_STEP_ID,
420: pcel.OIPL_ID, -- BEN comp obj
421: pcel.ELIGY_PRFL_ID -- BEN elig profile
422: FROM per_all_assignments_f asg,
423: per_collective_agreements cagr,
424: per_cagr_entitlements pce,
425: per_cagr_entitlement_items pcei,
426: per_cagr_entitlement_lines_f pcel
427: WHERE asg.ASSIGNMENT_ID = p_params.assignment_id
452: CURSOR csr_SE_drive_benmngle IS
453: SELECT asg.COLLECTIVE_AGREEMENT_ID,
454: asg.PERSON_ID,
455: cagr.PL_ID
456: FROM per_all_assignments_f asg, per_collective_agreements cagr
457: WHERE asg.assignment_id = p_params.assignment_id
458: AND p_params.effective_date BETWEEN asg.effective_start_date
459: AND asg.effective_end_date
460: AND asg.PRIMARY_FLAG = 'Y'
512: pcel.TO_STEP_ID,
513: pcel.OIPL_ID, -- BEN comp obj
514: pcel.ELIGY_PRFL_ID -- BEN elig profile
515: FROM per_all_assignments_f asg,
516: per_collective_agreements cagr,
517: per_cagr_entitlements pce,
518: per_cagr_entitlement_items pcei,
519: per_cagr_entitlement_lines_f pcel
520: WHERE asg.ASSIGNMENT_ID = p_params.assignment_id
1766: WHERE pcrr.assignment_id = p_assignment_id
1767: AND cagr_entitlement_item_id = p_cagr_entitlement_item_id
1768: AND p_params.effective_date BETWEEN pcrr.START_DATE AND nvl(pcrr.END_DATE,hr_general.end_of_time)
1769: AND EXISTS (select 'x'
1770: from per_collective_agreements pca
1771: where pca.collective_agreement_id = pcrr.collective_agreement_id
1772: and pca.STATUS = 'A'
1773: and p_params.effective_date >= pca.START_DATE)
1774: AND EXISTS (select 'x'
2109: and p_params.effective_date BETWEEN asg.effective_start_date
2110: AND asg.effective_end_date
2111: and asg.PRIMARY_FLAG = 'Y')
2112: AND EXISTS (select 'x'
2113: from per_collective_agreements pca
2114: where pca.collective_agreement_id = pcrr.collective_agreement_id
2115: and pca.STATUS = 'A'
2116: and p_params.effective_date >= pca.START_DATE)
2117: AND EXISTS (select 'x'