1 package body FND_STAGE_FN_PARAMETERS_PKG as
2 /* $Header: AFCPSFPB.pls 120.2 2005/08/19 14:36:26 susghosh ship $ */
3
4 procedure INSERT_ROW (
5 X_ROWID in out NOCOPY VARCHAR2,
6 X_APPLICATION_ID in NUMBER,
7 X_FUNCTION_ID in NUMBER,
8 X_PARAMETER_ID in NUMBER,
9 X_PARAMETER_NAME in VARCHAR2,
10 X_USER_PARAMETER_NAME in VARCHAR2,
11 X_DESCRIPTION in VARCHAR2,
12 X_CREATION_DATE in DATE,
13 X_CREATED_BY in NUMBER,
14 X_LAST_UPDATE_DATE in DATE,
15 X_LAST_UPDATED_BY in NUMBER,
16 X_LAST_UPDATE_LOGIN in NUMBER
17 ) is
18 cursor C is select ROWID from FND_STAGE_FN_PARAMETERS_TL
19 where APPLICATION_ID = X_APPLICATION_ID
20 and FUNCTION_ID = X_FUNCTION_ID
21 and PARAMETER_ID = X_PARAMETER_ID
22 and LANGUAGE = userenv('LANG')
23 ;
24 begin
25 insert into FND_STAGE_FN_PARAMETERS_TL (
26 APPLICATION_ID,
27 FUNCTION_ID,
28 PARAMETER_ID,
29 PARAMETER_NAME,
30 CREATION_DATE,
31 CREATED_BY,
32 LAST_UPDATE_DATE,
33 LAST_UPDATED_BY,
34 LAST_UPDATE_LOGIN,
35 USER_PARAMETER_NAME,
36 DESCRIPTION,
37 LANGUAGE,
38 SOURCE_LANG
39 ) select
40 X_APPLICATION_ID,
41 X_FUNCTION_ID,
42 X_PARAMETER_ID,
43 X_PARAMETER_NAME,
44 X_CREATION_DATE,
45 X_CREATED_BY,
46 X_LAST_UPDATE_DATE,
47 X_LAST_UPDATED_BY,
48 X_LAST_UPDATE_LOGIN,
49 X_USER_PARAMETER_NAME,
50 X_DESCRIPTION,
51 L.LANGUAGE_CODE,
52 userenv('LANG')
53 from FND_LANGUAGES L
54 where L.INSTALLED_FLAG in ('I', 'B')
55 and not exists
56 (select NULL
57 from FND_STAGE_FN_PARAMETERS_TL T
58 where T.APPLICATION_ID = X_APPLICATION_ID
59 and T.FUNCTION_ID = X_FUNCTION_ID
60 and T.PARAMETER_ID = X_PARAMETER_ID
61 and T.LANGUAGE = L.LANGUAGE_CODE);
62
63 open c;
64 fetch c into X_ROWID;
65 if (c%notfound) then
66 close c;
67 raise no_data_found;
68 end if;
69 close c;
70
71 end INSERT_ROW;
72
73 procedure LOCK_ROW (
74 X_APPLICATION_ID in NUMBER,
75 X_FUNCTION_ID in NUMBER,
76 X_PARAMETER_ID in NUMBER,
77 X_PARAMETER_NAME in VARCHAR2,
78 X_USER_PARAMETER_NAME in VARCHAR2,
79 X_DESCRIPTION in VARCHAR2
80 ) is
81 cursor c1 is select
82 PARAMETER_NAME,
83 USER_PARAMETER_NAME,
84 DESCRIPTION
85 from FND_STAGE_FN_PARAMETERS_TL
86 where APPLICATION_ID = X_APPLICATION_ID
87 and FUNCTION_ID = X_FUNCTION_ID
88 and PARAMETER_ID = X_PARAMETER_ID
89 and LANGUAGE = userenv('LANG')
90 for update of APPLICATION_ID nowait;
91 tlinfo c1%rowtype;
92
93 begin
94 open c1;
95 fetch c1 into tlinfo;
96 if (c1%notfound) then
97 close c1;
98 return;
99 end if;
100 close c1;
101
102 if ( (tlinfo.USER_PARAMETER_NAME = X_USER_PARAMETER_NAME)
103 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
104 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
105 AND (tlinfo.PARAMETER_NAME = X_PARAMETER_NAME)
106 ) then
107 null;
108 else
109 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
110 app_exception.raise_exception;
111 end if;
112 return;
113 end LOCK_ROW;
114
115 procedure UPDATE_ROW (
116 X_APPLICATION_ID in NUMBER,
117 X_FUNCTION_ID in NUMBER,
118 X_PARAMETER_ID in NUMBER,
119 X_PARAMETER_NAME in VARCHAR2,
120 X_USER_PARAMETER_NAME in VARCHAR2,
121 X_DESCRIPTION in VARCHAR2,
122 X_LAST_UPDATE_DATE in DATE,
123 X_LAST_UPDATED_BY in NUMBER,
124 X_LAST_UPDATE_LOGIN in NUMBER
125 ) is
126 begin
127 update FND_STAGE_FN_PARAMETERS_TL set
128 PARAMETER_NAME = X_PARAMETER_NAME,
129 USER_PARAMETER_NAME = X_USER_PARAMETER_NAME,
130 DESCRIPTION = X_DESCRIPTION,
131 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
132 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
133 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
134 SOURCE_LANG = userenv('LANG')
135 where APPLICATION_ID = X_APPLICATION_ID
136 and FUNCTION_ID = X_FUNCTION_ID
137 and PARAMETER_ID = X_PARAMETER_ID
138 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
139
140 if (sql%notfound) then
141 raise no_data_found;
142 end if;
143 end UPDATE_ROW;
144
145 procedure DELETE_ROW (
146 X_APPLICATION_ID in NUMBER,
147 X_FUNCTION_ID in NUMBER,
148 X_PARAMETER_ID in NUMBER
149 ) is
150 begin
151 delete from FND_STAGE_FN_PARAMETERS_TL
152 where APPLICATION_ID = X_APPLICATION_ID
153 and FUNCTION_ID = X_FUNCTION_ID
154 and PARAMETER_ID = X_PARAMETER_ID;
155
156 if (sql%notfound) then
157 raise no_data_found;
158 end if;
159 end DELETE_ROW;
160
161 procedure ADD_LANGUAGE
162 is
163 begin
164 /* Mar/19/03 requested by Ric Ginsberg */
165 /* The following update statements are commented out */
166 /* as a quick workaround to fix the time-consuming table handler issue */
167 /* Eventually we'll need to turn them into a separate fix_language procedure */
168 /*
169
170 update FND_STAGE_FN_PARAMETERS_TL T set (
171 USER_PARAMETER_NAME,
172 DESCRIPTION
173 ) = (select
174 B.USER_PARAMETER_NAME,
175 B.DESCRIPTION
176 from FND_STAGE_FN_PARAMETERS_TL B
177 where B.APPLICATION_ID = T.APPLICATION_ID
178 and B.FUNCTION_ID = T.FUNCTION_ID
179 and B.PARAMETER_ID = T.PARAMETER_ID
180 and B.LANGUAGE = T.SOURCE_LANG)
181 where (
182 T.APPLICATION_ID,
183 T.FUNCTION_ID,
184 T.PARAMETER_ID,
185 T.LANGUAGE
186 ) in (select
187 SUBT.APPLICATION_ID,
188 SUBT.FUNCTION_ID,
189 SUBT.PARAMETER_ID,
190 SUBT.LANGUAGE
191 from FND_STAGE_FN_PARAMETERS_TL SUBB, FND_STAGE_FN_PARAMETERS_TL SUBT
192 where SUBB.APPLICATION_ID = SUBT.APPLICATION_ID
193 and SUBB.FUNCTION_ID = SUBT.FUNCTION_ID
194 and SUBB.PARAMETER_ID = SUBT.PARAMETER_ID
195 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
196 and (SUBB.USER_PARAMETER_NAME <> SUBT.USER_PARAMETER_NAME
197 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
198 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
199 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
200 ));
201 */
202
203 insert into FND_STAGE_FN_PARAMETERS_TL (
204 APPLICATION_ID,
205 FUNCTION_ID,
206 PARAMETER_ID,
207 PARAMETER_NAME,
208 CREATION_DATE,
209 CREATED_BY,
210 LAST_UPDATE_DATE,
211 LAST_UPDATED_BY,
212 LAST_UPDATE_LOGIN,
213 USER_PARAMETER_NAME,
214 DESCRIPTION,
215 LANGUAGE,
216 SOURCE_LANG
217 ) select
218 B.APPLICATION_ID,
219 B.FUNCTION_ID,
220 B.PARAMETER_ID,
221 B.PARAMETER_NAME,
222 B.CREATION_DATE,
223 B.CREATED_BY,
224 B.LAST_UPDATE_DATE,
225 B.LAST_UPDATED_BY,
226 B.LAST_UPDATE_LOGIN,
227 B.USER_PARAMETER_NAME,
228 B.DESCRIPTION,
229 L.LANGUAGE_CODE,
230 userenv('LANG')
231 from FND_STAGE_FN_PARAMETERS_TL B, FND_LANGUAGES L
232 where L.INSTALLED_FLAG in ('I', 'B')
233 and B.LANGUAGE = userenv('LANG')
234 and not exists
235 (select NULL
236 from FND_STAGE_FN_PARAMETERS_TL T
237 where T.APPLICATION_ID = B.APPLICATION_ID
238 and T.FUNCTION_ID = B.FUNCTION_ID
239 and T.PARAMETER_ID = B.PARAMETER_ID
240 and T.LANGUAGE = L.LANGUAGE_CODE);
241 end ADD_LANGUAGE;
242
243 end FND_STAGE_FN_PARAMETERS_PKG;