[Home] [Help]
56: <> ben_prc_shd.g_old_rec.prtt_reimbmt_rqst_id) then
57: --
58: -- raise error as PK has changed
59: --
60: ben_prc_shd.constraint_error('BEN_PRTT_REIMBMT_RQST_F_PK');
61: --
62: elsif not l_api_updating then
63: --
64: -- check if PK is null
66: if p_prtt_reimbmt_rqst_id is not null then
67: --
68: -- raise error as PK is not null
69: --
70: ben_prc_shd.constraint_error('BEN_PRTT_REIMBMT_RQST_F_PK');
71: --
72: end if;
73: --
74: end if;
202: --
203: l_abr_prv c_abr_prv%rowtype;
204: l_abr_balance abr_balance%rowtype;
205: l_proc varchar2(100) := 'get_year_balance' ;
206: l_ptd_balance ben_prtt_reimbmt_rqst_f.rqst_amt%type ;
207: l_yr_start_date date;
208: l_yr_end_date date;
209: l_assignment_id number;
210: l_assignment_action_id number;
325: -- future created entry to be taken for calc so p_effective_Date is not validated with
326: -- start date and end date . thi may lead into a problem of entry is date tracked
327: -- curent entry is not taken for calc tath is added in calling proc this helps while updating
328: /*cursor c1 is select sum(nvl(prc.aprvd_for_pymt_amt,0))
329: from ben_prtt_reimbmt_rqst_f prc,
330: ben_pl_f pl,
331: ben_popl_yr_perd pyr,
332: ben_yr_perd yr
333: where prc.submitter_person_id = p_person_id
352: and yr.business_group_id = p_business_group_id;
353: */
354: cursor c_year_claim_amt1 (p_popl_yr_perd number) is
355: select sum(nvl(prc.amt_year1,0))
356: from ben_prtt_reimbmt_rqst_f prc
357: where prc.submitter_person_id = p_person_id
358: and prc.prtt_reimbmt_rqst_stat_cd not in ('DND','VOIDED','DPLICT')
359: and prc.pl_id = p_pl_id
360: and prc.effective_end_date = hr_api.g_eot
363: and prc.popl_yr_perd_id_1 = p_popl_yr_perd;
364: --
365: cursor c_year_claim_amt2 (p_popl_yr_perd number) is
366: select sum(nvl(prc.amt_year2,0)) --+ sum(nvl(prc.amt_year2,0))
367: from ben_prtt_reimbmt_rqst_f prc
368: where prc.submitter_person_id = p_person_id
369: and prc.prtt_reimbmt_rqst_stat_cd not in ('DND','VOIDED','DPLICT')
370: and prc.pl_id = p_pl_id
371: and prc.effective_end_date = hr_api.g_eot
373: or prc.prtt_reimbmt_rqst_id <> p_prtt_reimbmt_rqst_id)
374: and prc.popl_yr_perd_id_2 = p_popl_yr_perd;
375:
376:
377: l_year_bal ben_prtt_reimbmt_rqst_f.rqst_amt%type ;
378: l_proc varchar2(100) := ' plan_year_claim' ;
379:
380:
381: begin
488: --
489: -- raise error as FK does not relate to PK in ben_gd_or_svc_typ
490: -- table.
491: --
492: ben_prc_shd.constraint_error('BEN_PRTT_REIMBMT_RQST_F_FK1');
493: --
494: end if;
495: --
496: close c1;
582: --
583: -- raise error as FK does not relate to PK in hr_all_organization_units
584: -- table or PER_ALL_PEOPLE_F table.
585: --
586: ben_prc_shd.constraint_error('BEN_PRTT_REIMBMT_RQST_F_DT6');
587: --
588: end if;
589: --
590: close c2;
1193: and pln.effective_end_date;
1194: --
1195: l_cmpr_clms_to_cvg_or_bal_cd varchar2(300);
1196: l_c2_rec c2%rowtype;
1197: l_ptd_balance ben_prtt_reimbmt_rqst_f.rqst_amt%type ;
1198: l_amt_1 number;
1199: l_amt_2 number;
1200: prev_yr_cvg number;
1201: --
1506: p_effective_date in date
1507: ) is
1508:
1509: cursor c_prc is
1510: select 'x' from ben_prtt_reimbmt_rqst_f
1511: where prtt_reimbmt_rqst_id = p_prtt_reimbmt_rqst_id
1512: and effective_start_date > p_effective_date ;
1513:
1514: cursor c_asg is
2595: --
2596: cursor csr_leg_code is
2597: select a.legislation_code
2598: from per_business_groups a,
2599: ben_prtt_reimbmt_rqst_f b
2600: where b.prtt_reimbmt_rqst_id = p_prtt_reimbmt_rqst_id
2601: and a.business_group_id = b.business_group_id;
2602: --
2603: -- Declare local variables