[Home] [Help]
75: when no_data_found then null;
76: end;
77:
78: declare
79: tablename varchar2(100);
80: begin
81: select tname into tablename from tab where tname ='ADX_PRE_AUTOCONFIG' and tabtype in ('TABLE', 'SYNONYM');
82: exception WHEN NO_DATA_FOUND THEN
83: execute immediate 'create table APPLSYS.ADX_PRE_AUTOCONFIG (
77:
78: declare
79: tablename varchar2(100);
80: begin
81: select tname into tablename from tab where tname ='ADX_PRE_AUTOCONFIG' and tabtype in ('TABLE', 'SYNONYM');
82: exception WHEN NO_DATA_FOUND THEN
83: execute immediate 'create table APPLSYS.ADX_PRE_AUTOCONFIG (
84: application_id number,
85: profile_option_name varchar2(100),
79: tablename varchar2(100);
80: begin
81: select tname into tablename from tab where tname ='ADX_PRE_AUTOCONFIG' and tabtype in ('TABLE', 'SYNONYM');
82: exception WHEN NO_DATA_FOUND THEN
83: execute immediate 'create table APPLSYS.ADX_PRE_AUTOCONFIG (
84: application_id number,
85: profile_option_name varchar2(100),
86: level_id number,
87: level_value number,
135: end if;
136: end if;
137:
138: --
139: -- Update the ADX_PRE_AUTOCONFIG table for rollback
140: -- purposes.
141: --
142: declare
143: str varchar2(700);
157: execute immediate str INTO countrow
158: using p_application_id, p_profile_option_name, p_level_id,
159: p_level_value, p_context_name,update_flag,insert_flag;
160:
161: if (countrow>0) then -- update table since row already exist
162: str := 'update ADX_PRE_AUTOCONFIG set
163: profile_value=:1 where
164: application_id=:2 and profile_option_name=:3 and
165: level_id=:4 and level_value=:5 and
169: using old_pv_rec.profile_option_value, p_application_id,
170: p_profile_option_name, p_level_id, p_level_value,
171: p_context_name, update_flag, insert_flag;
172:
173: else -- insert new row into table since one does not exist yet
174: str:='insert into ADX_PRE_AUTOCONFIG
175: (application_id, profile_option_name,
176: level_id,level_value,profile_value,
177: level_value_app_id,context_name,
184: insert_flag;
185: end if;
186:
187: -- Call FND API to update/insert the profile option value in the
188: -- fnd_profile_option_values table
189: -- Note that for setting profiles at the SITE level
190: -- the last 3 parameters defined in the SAVE api are not required.
191:
192: ret_value := FND_PROFILE.SAVE(p_profile_option_name,
208: using p_application_id, p_profile_option_name, p_level_id,
209: p_level_value, level_value_app_id_temp, p_context_name,
210: update_flag,insert_flag, p_level_value2;
211:
212: if (countrow>0) then -- update table since row already exist
213: str := 'update ADX_PRE_AUTOCONFIG set
214: profile_value=:1 where
215: application_id=:2 and profile_option_name=:3 and
216: level_id=:4 and level_value=:5 and
222: p_profile_option_name, p_level_id, p_level_value,
223: level_value_app_id_temp, p_context_name, update_flag,
224: insert_flag, p_level_value2;
225:
226: else -- insert new row into table since one does not exist yet
227: str:='insert into ADX_PRE_AUTOCONFIG
228: values(:1, :2, :3, :4, :5, :6, :7, :8, :9, :10)';
229: execute immediate str
230: using p_application_id, p_profile_option_name, p_level_id,
233: insert_flag, p_level_value2;
234: end if;
235:
236: -- Call FND API to update/insert the profile option value in the
237: -- fnd_profile_option_values table
238:
239: ret_value := FND_PROFILE.SAVE(p_profile_option_name,
240: p_profile_value,
241: level_name,