[Home] [Help]
449: --
450: --
451: PROCEDURE write_daily_absences
452: (p_daily_absences IN pqp_absval_pkg.t_daily_absences
453: ,p_gap_absence_plan_id IN pqp_gap_absence_plans.gap_absence_plan_id%TYPE
454: )
455: IS
456:
457: TYPE t_gap_daily_absence_id IS TABLE OF NUMBER(15) INDEX BY BINARY_INTEGER;
1183: debug(l_proc_name, l_proc_step);
1184: END IF;
1185:
1186: DELETE
1187: FROM pqp_gap_absence_plans gap
1188: WHERE gap.gap_absence_plan_id = l_gap_absence_plan.gap_absence_plan_id;
1189:
1190: -- Call delete using API as we need to log the delete events for all the
1191: -- rows for the summary table
1217:
1218: l_proc_step := 50;
1219: IF g_debug THEN
1220: debug(SQL%ROWCOUNT);
1221: debug('pqp_gap_absence_plans rows deleted.');
1222: debug(l_proc_name, l_proc_step);
1223: END IF;
1224:
1225: ELSE -- there are still some daily absences left ie was a partial delete
2267: ,SUM(gda.duration) sum_of_duration
2268: ,SUM(gda.duration_in_hours) sum_of_duration_in_hours
2269: ,SUM(gda.duration/gda.working_days_per_week) sum_of_duration_per_week
2270: ,SUM(gda.duration_in_hours/fte) sum_of_fte_hours
2271: FROM pqp_gap_absence_plans gap
2272: ,ben_pl_f pln
2273: ,pqp_gap_daily_absences gda
2274: WHERE gap.assignment_id IN -- automatically makes the assignment list distinct
2275: (SELECT other_asg.assignment_id
2312: ,SUM(gda.duration) sum_of_duration
2313: ,SUM(gda.duration_in_hours) sum_of_duration_in_hours
2314: ,SUM(gda.duration/gda.working_days_per_week) sum_of_duration_per_week
2315: ,SUM(gda.duration_in_hours/fte) sum_of_fte_hours
2316: FROM pqp_gap_absence_plans gap
2317: ,ben_pl_f pln
2318: ,pqp_gap_daily_absences gda
2319: WHERE gap.assignment_id IN -- automatically makes the assignment list distinct
2320: (SELECT asg.assignment_id
2345: ,SUM(gda.duration_in_hours) sum_of_duration_in_hours
2346: ,SUM(gda.duration/gda.working_days_per_week) sum_of_duration_per_week
2347: -- LG/PT
2348: ,SUM(gda.duration_in_hours/fte) sum_of_fte_hours
2349: FROM pqp_gap_absence_plans gap
2350: ,ben_pl_f pln
2351: ,pqp_gap_daily_absences gda
2352: WHERE gap.assignment_id = p_assignment_id
2353: AND gda.gap_absence_plan_id = gap.gap_absence_plan_id
2444: DECODE(p_scheme_period_overlap_rule
2445: ,'NC',abs.date_end+1
2446: ,'FC',abs.date_start
2447: )
2448: FROM pqp_gap_absence_plans gap
2449: ,per_absence_attendances abs
2450: ,ben_pl_f pln
2451: WHERE gap.assignment_id = p_assignment_id --an absence for this assignment
2452: AND pln.pl_typ_id = p_pl_typ_id --which is relevant, ie enrolled
5336: l_generate_end_date DATE;
5337: l_band_val VARCHAR2(10) := 'NONE';
5338: l_datatype VARCHAR2(6);
5339: l_value VARCHAR2(240);
5340: l_gap_absence_plan_id pqp_gap_absence_plans.gap_absence_plan_id%TYPE;
5341:
5342: l_plan_information rec_plan_information;
5343: l_entitlements pqp_absval_pkg.t_entitlements;
5344: l_absences_taken_to_date pqp_absval_pkg.t_entitlements;
5344: l_absences_taken_to_date pqp_absval_pkg.t_entitlements;
5345: l_entitlements_remaining pqp_absval_pkg.t_entitlements;
5346: l_daily_absences pqp_absval_pkg.t_daily_absences;
5347: --table of pqp_gda_shd.g_rec_type
5348: l_object_version_number pqp_gap_absence_plans.object_version_number%TYPE;
5349: l_gap_absence_plan_id pqp_gap_absence_plans.gap_absence_plan_id%TYPE;
5350:
5351: l_error_code fnd_new_messages.message_number%TYPE;
5352: l_error_message fnd_new_messages.message_text%TYPE;
5345: l_entitlements_remaining pqp_absval_pkg.t_entitlements;
5346: l_daily_absences pqp_absval_pkg.t_daily_absences;
5347: --table of pqp_gda_shd.g_rec_type
5348: l_object_version_number pqp_gap_absence_plans.object_version_number%TYPE;
5349: l_gap_absence_plan_id pqp_gap_absence_plans.gap_absence_plan_id%TYPE;
5350:
5351: l_error_code fnd_new_messages.message_number%TYPE;
5352: l_error_message fnd_new_messages.message_text%TYPE;
5353: l_calendar_column_name VARCHAR2(30);
5917: --
5918: -- 3b) write to the cache (plsql table) to the datbase using bulk insert.
5919: --
5920:
5921: -- write the parent PQP_GAP_ABSENCE_PLANS row
5922: -- check first if it exists, as this create may
5923: -- have been called from an update. its actualy ineffecient
5924: -- to do this again as the update code has allready gap absence plans
5925: -- in that case gap_absence_plan_id should be a parameter
6368: -- wherever the query is changed the comment is wriiten accordingly.
6369: CURSOR csr_css_no_pay_days IS
6370: select NVL(SUM(gda.duration),0)
6371: from pqp_gap_daily_absences gda
6372: ,pqp_gap_absence_plans gap
6373: ,ben_pl_f pl
6374: ,hr_lookups hrl --added extra 7585452
6375: where pl.pl_id = gap.pl_id
6376: and pl.pl_typ_id = p_pl_typ_id
6397:
6398: CURSOR csr_css_no_pay_curpos IS
6399: select NVL(SUM(gda.duration),0)
6400: from pqp_gap_daily_absences gda
6401: ,pqp_gap_absence_plans gap
6402: ,ben_pl_f pl
6403: ,hr_lookups hrl --added extra 7585452
6404: where gap.assignment_id IN
6405: -- automatically makes the assignment list distinct
6437:
6438: CURSOR csr_css_no_pay_allpos(p_person_id IN NUMBER) IS
6439: select NVL(SUM(gda.duration),0)
6440: from pqp_gap_daily_absences gda
6441: ,pqp_gap_absence_plans gap
6442: ,ben_pl_f pl
6443: ,hr_lookups hrl --added extra 7585452
6444: where gap.assignment_id IN
6445: -- automatically makes the assignment list distinct