[Home] [Help]
232: rpt.pl_id,
233: group_oipl.oipl_id group_oipl_id,
234: rpt.person_id
235: FROM ben_oipl_f o,
236: ben_cwb_rpt_detail rpt,
237: ben_oipl_f local_oipl,
238: ben_opt_f local_opt,
239: ben_opt_f group_opt,
240: ben_oipl_f group_oipl
246: AND group_opt.group_opt_id = local_opt.group_opt_id
247: AND group_oipl.opt_id = group_opt.group_opt_id
248: AND NOT EXISTS
249: (SELECT NULL
250: FROM ben_cwb_rpt_detail
251: WHERE oipl_id = o.oipl_id
252: AND benefit_action_id = v_benefit_action_id)
253: GROUP BY o.oipl_id,
254: rpt.group_per_in_ler_id,
411:
412: CURSOR c_error_per_summary (v_benefit_action_id IN NUMBER)
413: IS
414: SELECT COUNT (*) amount
415: FROM ben_cwb_rpt_detail
416: WHERE person_rate_id = -9999
417: AND status_cd = 'E'
418: AND benefit_action_id = v_benefit_action_id;
419:
419:
420: CURSOR c_succ_per_summary (v_benefit_action_id IN NUMBER)
421: IS
422: SELECT COUNT (*) amount
423: FROM ben_cwb_rpt_detail
424: WHERE person_rate_id = -9999
425: AND status_cd IN ('WC', 'SC', 'W')
426: AND benefit_action_id = v_benefit_action_id;
427:
427:
428: CURSOR c_lf_evt_open_summary (v_benefit_action_id IN NUMBER)
429: IS
430: SELECT COUNT (*) amount
431: FROM ben_cwb_rpt_detail
432: WHERE person_rate_id = -9999
433: AND lf_evt_closed_flag = 'N'
434: AND benefit_action_id = v_benefit_action_id;
435:
435:
436: CURSOR c_lf_evt_close_summary (v_benefit_action_id IN NUMBER)
437: IS
438: SELECT COUNT (*) amount
439: FROM ben_cwb_rpt_detail
440: WHERE person_rate_id = -9999
441: AND lf_evt_closed_flag = 'Y'
442: AND benefit_action_id = v_benefit_action_id;
443:
1670: g_cache_cwb_rpt_person (i).amount = 0) THEN
1671: g_cache_cwb_rpt_person (i).prev_sal := null;
1672: g_cache_cwb_rpt_person (i).new_sal := null;
1673: END IF;*/
1674: INSERT INTO ben_cwb_rpt_detail
1675: (benefit_action_id
1676: , person_rate_id
1677: , pl_id
1678: , person_id
1861: , g_cache_cwb_rpt_person (i).assignment_changed
1862: , g_cache_cwb_rpt_person (i).status
1863: , g_cache_cwb_rpt_person (i).lf_evt_closed
1864: , l_message_text
1865: , ben_cwb_rpt_detail_s.NEXTVAL
1866: , g_cache_cwb_rpt_person (i).base_salary_currency
1867: , g_cache_cwb_rpt_person (i).currency
1868: , round(g_cache_cwb_rpt_person (i).base_salary*g_cache_cwb_rpt_person (i).pay_ann_factor/g_cache_cwb_rpt_person (i).pl_ann_factor,
1869: nvl(g_cache_cwb_rpt_person (i).uom_precision,2))
2006: IF l_previous <> g_cache_cwb_rpt_person (i).person_id
2007: THEN
2008: l_previous := g_cache_cwb_rpt_person (i).person_id;
2009:
2010: INSERT INTO ben_cwb_rpt_detail
2011: (benefit_action_id
2012: , person_rate_id
2013: , person_id
2014: , country_code
2025: , g_cache_cwb_sum_person (g_cache_cwb_rpt_person (i).person_id).bg_id
2026: , g_cache_cwb_sum_person (g_cache_cwb_rpt_person (i).person_id).bg_name
2027: , g_cache_cwb_sum_person (g_cache_cwb_rpt_person (i).person_id).status
2028: , g_cache_cwb_sum_person (g_cache_cwb_rpt_person (i).person_id).lf_evt_closed
2029: , ben_cwb_rpt_detail_s.NEXTVAL
2030: );
2031:
2032: IF (g_cache_cwb_sum_person (g_cache_cwb_rpt_person (i).person_id).status = 'E')
2033: THEN
2072: BEGIN
2073: WRITE('table corrections');
2074: FOR l_table_correction_rec IN c_table_correction_data(p_benefit_action_id)
2075: LOOP
2076: INSERT INTO ben_cwb_rpt_detail (
2077: benefit_action_id,
2078: person_id,
2079: pl_id,
2080: oipl_id,
2088: l_table_correction_rec.pl_id,
2089: l_table_correction_rec.oipl_id,
2090: l_table_correction_rec.group_per_in_ler_id,
2091: l_table_correction_rec.group_oipl_id,
2092: ben_cwb_rpt_detail_s.NEXTVAL
2093: );
2094: WRITE(
2095: p_benefit_action_id||'-'||
2096: l_table_correction_rec.person_id||'-'||