9003: from ben_copy_entity_results cpe,
9004: pqh_table_route tr
9005: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
9006: and cpe.table_route_id = tr.table_route_id
9007: -- and tr.where_clause = l_BEN_OPT_F
9008: and tr.table_alias = l_table_alias
9009: and cpe.number_of_copies = 1 --ADDITION
9010: group by decode(cpe.information264,cpe.information1,1,2),
9011: cpe.information1,
9048: c_business_group_id number,
9049: c_new_pk_id number) is
9050: select
9051: OPT.opt_id new_value
9052: from BEN_OPT_F OPT
9053: where OPT.name = c_OPT_name
9054: and OPT.business_group_id = c_business_group_id
9055: and OPT.opt_id <> c_new_pk_id
9056: --TEMPIK
9058: and effective_end_date ;
9059: --END TEMPIK
9060: /*TEMPIK
9061: and exists ( select null
9062: from BEN_OPT_F OPT1
9063: where OPT1.name = c_OPT_name
9064: and OPT1.business_group_id = c_business_group_id
9065: and OPT1.effective_start_date <= c_effective_start_date )
9066: and exists ( select null
9063: where OPT1.name = c_OPT_name
9064: and OPT1.business_group_id = c_business_group_id
9065: and OPT1.effective_start_date <= c_effective_start_date )
9066: and exists ( select null
9067: from BEN_OPT_F OPT2
9068: where OPT2.name = c_OPT_name
9069: and OPT2.business_group_id = c_business_group_id
9070: and OPT2.effective_end_date >= c_effective_end_date )
9071: ;
9078: --
9079: cursor c_get_grp_opt(p_grp_opt_name in varchar2,
9080: p_effective_date in date) is
9081: select name, opt_id
9082: from ben_opt_f
9083: where name = p_grp_opt_name
9084: and p_effective_date between effective_start_date and effective_end_date;
9085:
9086: l_get_grp_opt c_get_grp_opt%rowtype;
9203: if c_find_OPT_in_target%found then
9204: --
9205: --TEMPIK
9206: l_dt_rec_found := dt_api.check_min_max_dates
9207: (p_base_table_name => 'BEN_OPT_F',
9208: p_base_key_column => 'OPT_ID',
9209: p_base_key_value => l_new_value,
9210: p_from_date => l_min_esd,
9211: p_to_date => l_max_eed );
9299: l_CMBN_PTIP_OPT_ID := get_fk('CMBN_PTIP_OPT_ID', r_OPT.INFORMATION249,l_dml_operation);
9300: l_MAPPING_TABLE_PK_ID := get_fk('MAPPING_TABLE_PK_ID', r_OPT.INFORMATION257,l_dml_operation);
9301: l_RQD_PERD_ENRT_NENRT_RL := get_fk('FORMULA_ID', r_OPT.INFORMATION258,l_dml_operation);
9302: --
9303: ben_pd_copy_to_ben_one.ben_chk_col_len('NAME' ,'BEN_OPT_F',l_prefix || r_OPT.INFORMATION170 || l_suffix);
9304: --
9305:
9306: l_effective_date := r_OPT.information2;
9307: if ( ben_pd_copy_to_ben_one.g_copy_effective_date is not null and
9310: end if;
9311: --
9312: if l_first_rec and not l_update then
9313: -- Call Create routine.
9314: hr_utility.set_location(' BEN_OPT_F CREATE_OPTION_DEFINITION ',20);
9315: BEN_OPTION_DEFINITION_API.CREATE_OPTION_DEFINITION(
9316: --
9317: P_VALIDATE => false
9318: ,P_EFFECTIVE_DATE => l_effective_date
9418: IF l_update OR l_dml_operation <> 'UPDATE' THEN
9419: --UPD END
9420: -- Call Update routine for the pk_id created in prev run .
9421: -- insert the table_name,old_pk_id,new_pk_id into a plsql record
9422: hr_utility.set_location(' BEN_OPT_F UPDATE_OPTION_DEFINITION ',30);
9423: BEN_OPTION_DEFINITION_API.UPDATE_OPTION_DEFINITION(
9424: --
9425: P_VALIDATE => false
9426: ,P_EFFECTIVE_DATE => l_effective_date