[Home] [Help]
10937: ,cpe.dml_operation,cpe.datetrack_mode from ben_copy_entity_results cpe,
10938: pqh_table_route tr
10939: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
10940: and cpe.table_route_id = tr.table_route_id
10941: -- and tr.where_clause = l_BEN_PTIP_F
10942: and tr.table_alias = l_table_alias
10943: and cpe.number_of_copies = 1 -- ADDITION
10944: group by cpe.information1,cpe.information2,cpe.information3, cpe.table_route_id,cpe.dml_operation,cpe.datetrack_mode
10945: order by information1, information2; --added for bug: 5151945
10975: c_business_group_id number,
10976: c_new_pk_id number) is
10977: select
10978: CTP.ptip_id new_value
10979: from BEN_PTIP_F CTP
10980: where
10981: --nvl(CTP.ACRS_PTIP_CVG_ID,-999) = nvl(l_ACRS_PTIP_CVG_ID,-999) and
10982: --nvl(CTP.CMBN_PTIP_ID,-999) = nvl(l_CMBN_PTIP_ID,-999) and
10983: --nvl(CTP.CMBN_PTIP_OPT_ID,-999) = nvl(l_CMBN_PTIP_OPT_ID,-999) and
10990: and effective_end_date ;
10991: --END TEMPIK
10992: /*TEMPIK
10993: and exists ( select null
10994: from BEN_PTIP_F CTP1
10995: where
10996: --nvl(CTP1.ACRS_PTIP_CVG_ID,-999) = nvl(l_ACRS_PTIP_CVG_ID,-999) and
10997: --nvl(CTP1.CMBN_PTIP_ID,-999) = nvl(l_CMBN_PTIP_ID,-999) and
10998: --nvl(CTP1.CMBN_PTIP_OPT_ID,-999) = nvl(l_CMBN_PTIP_OPT_ID,-999) and
11000: CTP1.PL_TYP_ID = l_PL_TYP_ID and
11001: CTP1.business_group_id = c_business_group_id
11002: and CTP1.effective_start_date <= c_effective_start_date )
11003: and exists ( select null
11004: from BEN_PTIP_F CTP2
11005: where
11006: --nvl(CTP2.ACRS_PTIP_CVG_ID,-999) = nvl(l_ACRS_PTIP_CVG_ID,-999) and
11007: --nvl(CTP2.CMBN_PTIP_ID,-999) = nvl(l_CMBN_PTIP_ID,-999) and
11008: --nvl(CTP2.CMBN_PTIP_OPT_ID,-999) = nvl(l_CMBN_PTIP_OPT_ID,-999) and
11039: l_min_esd date;
11040: l_max_eed date;
11041: l_effective_date date;
11042: --
11043: l_susp_if_dpnt_ssn_nt_prv_cd ben_ptip_f.susp_if_dpnt_ssn_nt_prv_cd%type;
11044: l_susp_if_dpnt_dob_nt_prv_cd ben_ptip_f.susp_if_dpnt_dob_nt_prv_cd%type;
11045: l_susp_if_dpnt_adr_nt_prv_cd ben_ptip_f.susp_if_dpnt_adr_nt_prv_cd%type;
11046: --
11047: begin
11040: l_max_eed date;
11041: l_effective_date date;
11042: --
11043: l_susp_if_dpnt_ssn_nt_prv_cd ben_ptip_f.susp_if_dpnt_ssn_nt_prv_cd%type;
11044: l_susp_if_dpnt_dob_nt_prv_cd ben_ptip_f.susp_if_dpnt_dob_nt_prv_cd%type;
11045: l_susp_if_dpnt_adr_nt_prv_cd ben_ptip_f.susp_if_dpnt_adr_nt_prv_cd%type;
11046: --
11047: begin
11048: -- Initialization
11041: l_effective_date date;
11042: --
11043: l_susp_if_dpnt_ssn_nt_prv_cd ben_ptip_f.susp_if_dpnt_ssn_nt_prv_cd%type;
11044: l_susp_if_dpnt_dob_nt_prv_cd ben_ptip_f.susp_if_dpnt_dob_nt_prv_cd%type;
11045: l_susp_if_dpnt_adr_nt_prv_cd ben_ptip_f.susp_if_dpnt_adr_nt_prv_cd%type;
11046: --
11047: begin
11048: -- Initialization
11049: l_object_found_in_target := false ;
11170: if c_find_CTP_in_target%found then
11171: --
11172: --TEMPIK
11173: l_dt_rec_found := dt_api.check_min_max_dates
11174: (p_base_table_name => 'BEN_PTIP_F',
11175: p_base_key_column => 'PTIP_ID',
11176: p_base_key_value => l_new_value,
11177: p_from_date => l_min_esd,
11178: p_to_date => l_max_eed );
11378: else
11379: --
11380: -- Call Update routine for the pk_id created in prev run .
11381: -- insert the table_name,old_pk_id,new_pk_id into a plsql record
11382: hr_utility.set_location(' BEN_PTIP_F UPDATE_PLAN_TYPE_IN_PROGRAM ',30);
11383: --UPD START
11384: hr_utility.set_location('Before call to get_dt_modes l_dt_mode'||l_datetrack_mode,5);
11385: --
11386: if l_update then
25219: end if;
25220: -- end if ;
25221: --
25222: close c_OTP_min_max_dates ;
25223: -- NEVER CREATE HERE. THIS IS DONE IN BEN_OIPL_F, BEN_PLIP_F, BEN_OPT_F and BEN_PTIP_F apis.
25224: /*
25225: if not l_object_found_in_target then
25226: --
25227: l_current_pk_id := r_OTP.information1;