1 package body QP_PARAMETERS_PKG as
2 /* $Header: QPXUPARB.pls 120.0 2005/08/05 00:26:47 nirmkuma noship $ */
3
4 procedure INSERT_ROW (
5 X_ROWID in out nocopy VARCHAR2,
6 X_PARAMETER_ID in NUMBER,
7 X_VALUE_SET_ID in NUMBER,
8 X_ADVANCED_PRICING_ONLY in VARCHAR2,
9 X_SEEDED_VALUE in VARCHAR2,
10 X_PARAMETER_CODE in VARCHAR2,
11 X_PARAMETER_LEVEL in VARCHAR2,
12 X_PARAMETER_NAME in VARCHAR2,
13 X_DESCRIPTION in VARCHAR2,
14 X_CREATION_DATE in DATE,
15 X_CREATED_BY in NUMBER,
16 X_LAST_UPDATE_DATE in DATE,
17 X_LAST_UPDATED_BY in NUMBER,
18 X_LAST_UPDATE_LOGIN in NUMBER
19 ) is
20 cursor C is select ROWID from QP_PARAMETERS_B
21 where PARAMETER_ID = X_PARAMETER_ID
22 ;
23 begin
24 insert into QP_PARAMETERS_B (
25 VALUE_SET_ID,
26 ADVANCED_PRICING_ONLY,
27 SEEDED_VALUE,
28 PARAMETER_CODE,
29 PARAMETER_ID,
30 PARAMETER_LEVEL,
31 CREATION_DATE,
32 CREATED_BY,
33 LAST_UPDATE_DATE,
34 LAST_UPDATED_BY,
35 LAST_UPDATE_LOGIN
36 ) values (
37 X_VALUE_SET_ID,
38 X_ADVANCED_PRICING_ONLY,
39 X_SEEDED_VALUE,
40 X_PARAMETER_CODE,
41 X_PARAMETER_ID,
42 X_PARAMETER_LEVEL,
43 X_CREATION_DATE,
44 X_CREATED_BY,
45 X_LAST_UPDATE_DATE,
46 X_LAST_UPDATED_BY,
47 X_LAST_UPDATE_LOGIN
48 );
49
50 insert into QP_PARAMETERS_TL (
51 PARAMETER_ID,
52 PARAMETER_NAME,
53 DESCRIPTION,
54 CREATION_DATE,
55 CREATED_BY,
56 LAST_UPDATE_DATE,
57 LAST_UPDATED_BY,
58 LAST_UPDATE_LOGIN,
59 LANGUAGE,
60 SOURCE_LANG
61 ) select
62 X_PARAMETER_ID,
63 X_PARAMETER_NAME,
64 X_DESCRIPTION,
65 X_CREATION_DATE,
66 X_CREATED_BY,
67 X_LAST_UPDATE_DATE,
68 X_LAST_UPDATED_BY,
69 X_LAST_UPDATE_LOGIN,
70 L.LANGUAGE_CODE,
71 userenv('LANG')
72 from FND_LANGUAGES L
73 where L.INSTALLED_FLAG in ('I', 'B')
74 and not exists
75 (select NULL
76 from QP_PARAMETERS_TL T
77 where T.PARAMETER_ID = X_PARAMETER_ID
78 and T.LANGUAGE = L.LANGUAGE_CODE);
79
80 open c;
81 fetch c into X_ROWID;
82 if (c%notfound) then
83 close c;
84 raise no_data_found;
85 end if;
86 close c;
87
88 QP_PARAM_UTIL.Populate_Parameter_Values( X_PARAMETER_ID,
89 X_SEEDED_VALUE,
90 X_PARAMETER_LEVEL);
91
92 end INSERT_ROW;
93
94 procedure LOCK_ROW (
95 X_PARAMETER_ID in NUMBER,
96 X_VALUE_SET_ID in NUMBER,
97 X_ADVANCED_PRICING_ONLY in VARCHAR2,
98 X_SEEDED_VALUE in VARCHAR2,
99 X_PARAMETER_CODE in VARCHAR2,
100 X_PARAMETER_LEVEL in VARCHAR2,
101 X_PARAMETER_NAME in VARCHAR2,
102 X_DESCRIPTION in VARCHAR2
103 ) is
104 cursor c is select
105 VALUE_SET_ID,
106 ADVANCED_PRICING_ONLY,
107 SEEDED_VALUE,
108 PARAMETER_CODE,
109 PARAMETER_LEVEL
110 from QP_PARAMETERS_B
111 where PARAMETER_ID = X_PARAMETER_ID
112 for update of PARAMETER_ID nowait;
113 recinfo c%rowtype;
114
115 cursor c1 is select
116 PARAMETER_NAME,
117 DESCRIPTION,
118 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
119 from QP_PARAMETERS_TL
120 where PARAMETER_ID = X_PARAMETER_ID
121 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
122 for update of PARAMETER_ID nowait;
123 begin
124 open c;
125 fetch c into recinfo;
126 if (c%notfound) then
127 close c;
128 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
129 app_exception.raise_exception;
130 end if;
131 close c;
132 if ( ((recinfo.VALUE_SET_ID = X_VALUE_SET_ID)
133 OR ((recinfo.VALUE_SET_ID is null) AND (X_VALUE_SET_ID is null)))
134 AND ((recinfo.ADVANCED_PRICING_ONLY = X_ADVANCED_PRICING_ONLY)
135 OR ((recinfo.ADVANCED_PRICING_ONLY is null) AND (X_ADVANCED_PRICING_ONLY is null)))
136 AND ((recinfo.SEEDED_VALUE = X_SEEDED_VALUE)
137 OR ((recinfo.SEEDED_VALUE is null) AND (X_SEEDED_VALUE is null)))
138 AND (recinfo.PARAMETER_CODE = X_PARAMETER_CODE)
139 AND (recinfo.PARAMETER_LEVEL = X_PARAMETER_LEVEL)
140 ) then
141 null;
142 else
143 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
144 app_exception.raise_exception;
145 end if;
146
147 for tlinfo in c1 loop
148 if (tlinfo.BASELANG = 'Y') then
149 if ( (tlinfo.PARAMETER_NAME = X_PARAMETER_NAME)
150 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
151 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
152 ) then
153 null;
154 else
155 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
156 app_exception.raise_exception;
157 end if;
158 end if;
159 end loop;
160 return;
161 end LOCK_ROW;
162
163 procedure UPDATE_ROW (
164 X_PARAMETER_ID in NUMBER,
165 X_VALUE_SET_ID in NUMBER,
166 X_ADVANCED_PRICING_ONLY in VARCHAR2,
167 X_SEEDED_VALUE in VARCHAR2,
168 X_PARAMETER_CODE in VARCHAR2,
169 X_PARAMETER_LEVEL in VARCHAR2,
170 X_PARAMETER_NAME in VARCHAR2,
171 X_DESCRIPTION in VARCHAR2,
172 X_LAST_UPDATE_DATE in DATE,
173 X_LAST_UPDATED_BY in NUMBER,
174 X_LAST_UPDATE_LOGIN in NUMBER
175 ) is
176 begin
177 update QP_PARAMETERS_B set
178 VALUE_SET_ID = X_VALUE_SET_ID,
179 ADVANCED_PRICING_ONLY = X_ADVANCED_PRICING_ONLY,
180 SEEDED_VALUE = X_SEEDED_VALUE,
181 PARAMETER_CODE = X_PARAMETER_CODE,
182 PARAMETER_LEVEL = X_PARAMETER_LEVEL,
183 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
184 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
185 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
186 where PARAMETER_ID = X_PARAMETER_ID;
187
188 if (sql%notfound) then
189 raise no_data_found;
190 end if;
191
192 update QP_PARAMETERS_TL set
193 PARAMETER_NAME = X_PARAMETER_NAME,
194 DESCRIPTION = X_DESCRIPTION,
195 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
196 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
197 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
198 SOURCE_LANG = userenv('LANG')
199 where PARAMETER_ID = X_PARAMETER_ID
200 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
201
202 if (sql%notfound) then
203 raise no_data_found;
204 end if;
205 end UPDATE_ROW;
206
207 procedure DELETE_ROW (
208 X_PARAMETER_ID in NUMBER
209 ) is
210 begin
211
212 delete from QP_PARAMETERS_TL
213 where PARAMETER_ID = X_PARAMETER_ID;
214
215 if (sql%notfound) then
216 raise no_data_found;
217 end if;
218
219 delete from QP_PARAMETERS_B
220 where PARAMETER_ID = X_PARAMETER_ID;
221
222 if (sql%notfound) then
223 raise no_data_found;
224 end if;
225
226 delete from QP_PARAMETER_VALUES
227 where PARAMETER_ID = X_PARAMETER_ID;
228
229 if (sql%notfound) then
230 raise no_data_found;
231 end if;
232
233 end DELETE_ROW;
234
235 procedure ADD_LANGUAGE
236 is
237 begin
238 delete from QP_PARAMETERS_TL T
239 where not exists
240 (select NULL
241 from QP_PARAMETERS_B B
242 where B.PARAMETER_ID = T.PARAMETER_ID
243 );
244
245 update QP_PARAMETERS_TL T set (
246 PARAMETER_NAME,
247 DESCRIPTION
248 ) = (select
249 B.PARAMETER_NAME,
250 B.DESCRIPTION
251 from QP_PARAMETERS_TL B
252 where B.PARAMETER_ID = T.PARAMETER_ID
253 and B.LANGUAGE = T.SOURCE_LANG)
254 where (
255 T.PARAMETER_ID,
256 T.LANGUAGE
257 ) in (select
258 SUBT.PARAMETER_ID,
259 SUBT.LANGUAGE
260 from QP_PARAMETERS_TL SUBB, QP_PARAMETERS_TL SUBT
261 where SUBB.PARAMETER_ID = SUBT.PARAMETER_ID
262 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
263 and (SUBB.PARAMETER_NAME <> SUBT.PARAMETER_NAME
264 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
265 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
266 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
267 ));
268
269 insert into QP_PARAMETERS_TL (
270 PARAMETER_ID,
271 PARAMETER_NAME,
272 DESCRIPTION,
273 CREATION_DATE,
274 CREATED_BY,
275 LAST_UPDATE_DATE,
276 LAST_UPDATED_BY,
277 LAST_UPDATE_LOGIN,
278 LANGUAGE,
279 SOURCE_LANG
280 ) select /*+ ORDERED */
281 B.PARAMETER_ID,
282 B.PARAMETER_NAME,
283 B.DESCRIPTION,
284 B.CREATION_DATE,
285 B.CREATED_BY,
286 B.LAST_UPDATE_DATE,
287 B.LAST_UPDATED_BY,
288 B.LAST_UPDATE_LOGIN,
289 L.LANGUAGE_CODE,
290 B.SOURCE_LANG
291 from QP_PARAMETERS_TL B, FND_LANGUAGES L
292 where L.INSTALLED_FLAG in ('I', 'B')
293 and B.LANGUAGE = userenv('LANG')
294 and not exists
295 (select NULL
296 from QP_PARAMETERS_TL T
297 where T.PARAMETER_ID = B.PARAMETER_ID
298 and T.LANGUAGE = L.LANGUAGE_CODE);
299 end ADD_LANGUAGE;
300
301 end QP_PARAMETERS_PKG;
[Home] [Help]