1 package body SO_PRICE_LISTS_PKG as
2 /* $Header: OEXPLSTB.pls 115.2 99/07/16 08:14:49 porting shi $ */
3 procedure INSERT_ROW (
4 X_ROWID in out VARCHAR2,
5 X_PRICE_LIST_ID in NUMBER,
6 X_ATTRIBUTE9 in VARCHAR2,
7 X_ATTRIBUTE10 in VARCHAR2,
8 X_ATTRIBUTE11 in VARCHAR2,
9 X_ATTRIBUTE12 in VARCHAR2,
10 X_ATTRIBUTE13 in VARCHAR2,
11 X_ATTRIBUTE14 in VARCHAR2,
12 X_ATTRIBUTE15 in VARCHAR2,
13 X_REQUEST_ID in NUMBER,
14 X_CURRENCY_CODE in VARCHAR2,
15 X_ROUNDING_FACTOR in NUMBER,
16 X_SECONDARY_PRICE_LIST_ID in NUMBER,
17 X_SHIP_METHOD_CODE in VARCHAR2,
18 X_FREIGHT_TERMS_CODE in VARCHAR2,
19 X_TERMS_ID in NUMBER,
20 X_START_DATE_ACTIVE in DATE,
21 X_END_DATE_ACTIVE in DATE,
22 X_COMMENTS in VARCHAR2,
23 X_CONTEXT in VARCHAR2,
24 X_ATTRIBUTE1 in VARCHAR2,
25 X_ATTRIBUTE2 in VARCHAR2,
26 X_ATTRIBUTE3 in VARCHAR2,
27 X_ATTRIBUTE4 in VARCHAR2,
28 X_ATTRIBUTE5 in VARCHAR2,
29 X_ATTRIBUTE6 in VARCHAR2,
30 X_ATTRIBUTE7 in VARCHAR2,
31 X_ATTRIBUTE8 in VARCHAR2,
32 X_NAME in VARCHAR2,
33 X_DESCRIPTION in VARCHAR2,
34 X_CREATION_DATE in DATE,
35 X_CREATED_BY in NUMBER,
36 X_LAST_UPDATE_DATE in DATE,
37 X_LAST_UPDATED_BY in NUMBER,
38 X_LAST_UPDATE_LOGIN in NUMBER
39 ) is
40 cursor C is select ROWID from SO_PRICE_LISTS_B
41 where PRICE_LIST_ID = X_PRICE_LIST_ID
42 ;
43 begin
44 insert into SO_PRICE_LISTS_B (
45 ATTRIBUTE9,
46 ATTRIBUTE10,
47 ATTRIBUTE11,
48 ATTRIBUTE12,
49 ATTRIBUTE13,
50 ATTRIBUTE14,
51 ATTRIBUTE15,
52 PRICE_LIST_ID,
53 REQUEST_ID,
54 CURRENCY_CODE,
55 ROUNDING_FACTOR,
56 SECONDARY_PRICE_LIST_ID,
57 SHIP_METHOD_CODE,
58 FREIGHT_TERMS_CODE,
59 TERMS_ID,
60 START_DATE_ACTIVE,
61 END_DATE_ACTIVE,
62 COMMENTS,
63 CONTEXT,
64 ATTRIBUTE1,
65 ATTRIBUTE2,
66 ATTRIBUTE3,
67 ATTRIBUTE4,
68 ATTRIBUTE5,
69 ATTRIBUTE6,
70 ATTRIBUTE7,
71 ATTRIBUTE8,
72 CREATION_DATE,
73 CREATED_BY,
74 LAST_UPDATE_DATE,
75 LAST_UPDATED_BY,
76 LAST_UPDATE_LOGIN
77 ) values (
78 X_ATTRIBUTE9,
79 X_ATTRIBUTE10,
80 X_ATTRIBUTE11,
81 X_ATTRIBUTE12,
82 X_ATTRIBUTE13,
83 X_ATTRIBUTE14,
84 X_ATTRIBUTE15,
85 X_PRICE_LIST_ID,
86 X_REQUEST_ID,
87 X_CURRENCY_CODE,
88 X_ROUNDING_FACTOR,
89 X_SECONDARY_PRICE_LIST_ID,
90 X_SHIP_METHOD_CODE,
91 X_FREIGHT_TERMS_CODE,
92 X_TERMS_ID,
93 X_START_DATE_ACTIVE,
94 X_END_DATE_ACTIVE,
95 X_COMMENTS,
96 X_CONTEXT,
97 X_ATTRIBUTE1,
98 X_ATTRIBUTE2,
99 X_ATTRIBUTE3,
100 X_ATTRIBUTE4,
101 X_ATTRIBUTE5,
102 X_ATTRIBUTE6,
103 X_ATTRIBUTE7,
104 X_ATTRIBUTE8,
105 X_CREATION_DATE,
106 X_CREATED_BY,
107 X_LAST_UPDATE_DATE,
108 X_LAST_UPDATED_BY,
109 X_LAST_UPDATE_LOGIN
110 );
111
112 insert into SO_PRICE_LISTS_TL (
113 PRICE_LIST_ID,
114 NAME,
115 DESCRIPTION,
116 CREATION_DATE,
117 CREATED_BY,
118 LAST_UPDATE_DATE,
119 LAST_UPDATED_BY,
120 LAST_UPDATE_LOGIN,
121 LANGUAGE,
122 SOURCE_LANG
123 ) select
124 X_PRICE_LIST_ID,
125 X_NAME,
126 X_DESCRIPTION,
127 X_CREATION_DATE,
128 X_CREATED_BY,
129 X_LAST_UPDATE_DATE,
130 X_LAST_UPDATED_BY,
131 X_LAST_UPDATE_LOGIN,
132 L.LANGUAGE_CODE,
133 userenv('LANG')
134 from FND_LANGUAGES L
135 where L.INSTALLED_FLAG in ('I', 'B')
136 and not exists
137 (select NULL
138 from SO_PRICE_LISTS_TL T
139 where T.PRICE_LIST_ID = X_PRICE_LIST_ID
140 and T.LANGUAGE = L.LANGUAGE_CODE);
141
142 open c;
143 fetch c into X_ROWID;
144 if (c%notfound) then
145 close c;
146 raise no_data_found;
147 end if;
148 close c;
149
150 end INSERT_ROW;
151
152 procedure LOCK_ROW (
153 X_PRICE_LIST_ID in NUMBER,
154 X_ATTRIBUTE9 in VARCHAR2,
155 X_ATTRIBUTE10 in VARCHAR2,
156 X_ATTRIBUTE11 in VARCHAR2,
157 X_ATTRIBUTE12 in VARCHAR2,
158 X_ATTRIBUTE13 in VARCHAR2,
159 X_ATTRIBUTE14 in VARCHAR2,
160 X_ATTRIBUTE15 in VARCHAR2,
161 X_REQUEST_ID in NUMBER,
162 X_CURRENCY_CODE in VARCHAR2,
163 X_ROUNDING_FACTOR in NUMBER,
164 X_SECONDARY_PRICE_LIST_ID in NUMBER,
165 X_SHIP_METHOD_CODE in VARCHAR2,
166 X_FREIGHT_TERMS_CODE in VARCHAR2,
167 X_TERMS_ID in NUMBER,
168 X_START_DATE_ACTIVE in DATE,
169 X_END_DATE_ACTIVE in DATE,
170 X_COMMENTS in VARCHAR2,
171 X_CONTEXT in VARCHAR2,
172 X_ATTRIBUTE1 in VARCHAR2,
173 X_ATTRIBUTE2 in VARCHAR2,
174 X_ATTRIBUTE3 in VARCHAR2,
175 X_ATTRIBUTE4 in VARCHAR2,
176 X_ATTRIBUTE5 in VARCHAR2,
177 X_ATTRIBUTE6 in VARCHAR2,
178 X_ATTRIBUTE7 in VARCHAR2,
179 X_ATTRIBUTE8 in VARCHAR2,
180 X_NAME in VARCHAR2,
181 X_DESCRIPTION in VARCHAR2
182 ) is
183 cursor c is select
184 ATTRIBUTE9,
185 ATTRIBUTE10,
186 ATTRIBUTE11,
187 ATTRIBUTE12,
188 ATTRIBUTE13,
189 ATTRIBUTE14,
190 ATTRIBUTE15,
191 REQUEST_ID,
192 CURRENCY_CODE,
193 ROUNDING_FACTOR,
194 SECONDARY_PRICE_LIST_ID,
195 SHIP_METHOD_CODE,
196 FREIGHT_TERMS_CODE,
197 TERMS_ID,
198 START_DATE_ACTIVE,
199 END_DATE_ACTIVE,
200 COMMENTS,
201 CONTEXT,
202 ATTRIBUTE1,
203 ATTRIBUTE2,
204 ATTRIBUTE3,
205 ATTRIBUTE4,
206 ATTRIBUTE5,
207 ATTRIBUTE6,
208 ATTRIBUTE7,
209 ATTRIBUTE8
210 from SO_PRICE_LISTS_B
211 where PRICE_LIST_ID = X_PRICE_LIST_ID
212 for update of PRICE_LIST_ID nowait;
213 recinfo c%rowtype;
214
215 cursor c1 is select
216 NAME,
217 DESCRIPTION,
218 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
219 from SO_PRICE_LISTS_TL
220 where PRICE_LIST_ID = X_PRICE_LIST_ID
221 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
222 for update of PRICE_LIST_ID nowait;
223 begin
224 open c;
225 fetch c into recinfo;
226 if (c%notfound) then
227 close c;
228 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
229 app_exception.raise_exception;
230 end if;
231 close c;
232 if ( ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
233 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
234 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
235 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
236 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
237 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
238 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
239 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
240 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
241 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
242 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
243 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
244 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
245 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
246 AND ((recinfo.REQUEST_ID = X_REQUEST_ID)
247 OR ((recinfo.REQUEST_ID is null) AND (X_REQUEST_ID is null)))
248 AND (recinfo.CURRENCY_CODE = X_CURRENCY_CODE)
249 AND (recinfo.ROUNDING_FACTOR = X_ROUNDING_FACTOR)
250 AND ((recinfo.SECONDARY_PRICE_LIST_ID = X_SECONDARY_PRICE_LIST_ID)
251 OR ((recinfo.SECONDARY_PRICE_LIST_ID is null) AND (X_SECONDARY_PRICE_LIST_ID is null)))
252 AND ((recinfo.SHIP_METHOD_CODE = X_SHIP_METHOD_CODE)
253 OR ((recinfo.SHIP_METHOD_CODE is null) AND (X_SHIP_METHOD_CODE is null)))
254 AND ((recinfo.FREIGHT_TERMS_CODE = X_FREIGHT_TERMS_CODE)
255 OR ((recinfo.FREIGHT_TERMS_CODE is null) AND (X_FREIGHT_TERMS_CODE is null)))
256 AND ((recinfo.TERMS_ID = X_TERMS_ID)
257 OR ((recinfo.TERMS_ID is null) AND (X_TERMS_ID is null)))
258 AND ((recinfo.START_DATE_ACTIVE = X_START_DATE_ACTIVE)
259 OR ((recinfo.START_DATE_ACTIVE is null) AND (X_START_DATE_ACTIVE is null)))
260 AND ((recinfo.END_DATE_ACTIVE = X_END_DATE_ACTIVE)
261 OR ((recinfo.END_DATE_ACTIVE is null) AND (X_END_DATE_ACTIVE is null)))
262 AND ((recinfo.COMMENTS = X_COMMENTS)
263 OR ((recinfo.COMMENTS is null) AND (X_COMMENTS is null)))
264 AND ((recinfo.CONTEXT = X_CONTEXT)
265 OR ((recinfo.CONTEXT is null) AND (X_CONTEXT is null)))
266 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
267 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
268 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
269 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
270 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
271 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
272 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
273 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
274 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
275 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
276 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
277 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
278 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
279 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
280 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
281 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
282 ) then
283 null;
284 else
285 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
286 app_exception.raise_exception;
287 end if;
288
289 for tlinfo in c1 loop
290 if (tlinfo.BASELANG = 'Y') then
291 if ( (tlinfo.NAME = X_NAME)
292 AND (tlinfo.DESCRIPTION = X_DESCRIPTION)
293 ) then
294 null;
295 else
296 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
297 app_exception.raise_exception;
298 end if;
299 end if;
300 end loop;
301 return;
302 end LOCK_ROW;
303
304 procedure UPDATE_ROW (
305 X_PRICE_LIST_ID in NUMBER,
306 X_ATTRIBUTE9 in VARCHAR2,
307 X_ATTRIBUTE10 in VARCHAR2,
308 X_ATTRIBUTE11 in VARCHAR2,
309 X_ATTRIBUTE12 in VARCHAR2,
310 X_ATTRIBUTE13 in VARCHAR2,
311 X_ATTRIBUTE14 in VARCHAR2,
312 X_ATTRIBUTE15 in VARCHAR2,
313 X_REQUEST_ID in NUMBER,
314 X_CURRENCY_CODE in VARCHAR2,
315 X_ROUNDING_FACTOR in NUMBER,
316 X_SECONDARY_PRICE_LIST_ID in NUMBER,
317 X_SHIP_METHOD_CODE in VARCHAR2,
318 X_FREIGHT_TERMS_CODE in VARCHAR2,
319 X_TERMS_ID in NUMBER,
320 X_START_DATE_ACTIVE in DATE,
321 X_END_DATE_ACTIVE in DATE,
322 X_COMMENTS in VARCHAR2,
323 X_CONTEXT in VARCHAR2,
324 X_ATTRIBUTE1 in VARCHAR2,
325 X_ATTRIBUTE2 in VARCHAR2,
326 X_ATTRIBUTE3 in VARCHAR2,
327 X_ATTRIBUTE4 in VARCHAR2,
328 X_ATTRIBUTE5 in VARCHAR2,
329 X_ATTRIBUTE6 in VARCHAR2,
330 X_ATTRIBUTE7 in VARCHAR2,
331 X_ATTRIBUTE8 in VARCHAR2,
332 X_NAME in VARCHAR2,
333 X_DESCRIPTION in VARCHAR2,
334 X_LAST_UPDATE_DATE in DATE,
335 X_LAST_UPDATED_BY in NUMBER,
336 X_LAST_UPDATE_LOGIN in NUMBER
337 ) is
338 begin
339 update SO_PRICE_LISTS_B set
340 ATTRIBUTE9 = X_ATTRIBUTE9,
341 ATTRIBUTE10 = X_ATTRIBUTE10,
342 ATTRIBUTE11 = X_ATTRIBUTE11,
343 ATTRIBUTE12 = X_ATTRIBUTE12,
344 ATTRIBUTE13 = X_ATTRIBUTE13,
345 ATTRIBUTE14 = X_ATTRIBUTE14,
346 ATTRIBUTE15 = X_ATTRIBUTE15,
347 REQUEST_ID = X_REQUEST_ID,
348 CURRENCY_CODE = X_CURRENCY_CODE,
349 ROUNDING_FACTOR = X_ROUNDING_FACTOR,
350 SECONDARY_PRICE_LIST_ID = X_SECONDARY_PRICE_LIST_ID,
351 SHIP_METHOD_CODE = X_SHIP_METHOD_CODE,
352 FREIGHT_TERMS_CODE = X_FREIGHT_TERMS_CODE,
353 TERMS_ID = X_TERMS_ID,
354 START_DATE_ACTIVE = X_START_DATE_ACTIVE,
355 END_DATE_ACTIVE = X_END_DATE_ACTIVE,
356 COMMENTS = X_COMMENTS,
357 CONTEXT = X_CONTEXT,
358 ATTRIBUTE1 = X_ATTRIBUTE1,
359 ATTRIBUTE2 = X_ATTRIBUTE2,
360 ATTRIBUTE3 = X_ATTRIBUTE3,
361 ATTRIBUTE4 = X_ATTRIBUTE4,
362 ATTRIBUTE5 = X_ATTRIBUTE5,
363 ATTRIBUTE6 = X_ATTRIBUTE6,
364 ATTRIBUTE7 = X_ATTRIBUTE7,
365 ATTRIBUTE8 = X_ATTRIBUTE8,
366 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
367 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
368 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
369 where PRICE_LIST_ID = X_PRICE_LIST_ID;
370
371 if (sql%notfound) then
372 raise no_data_found;
373 end if;
374
375 update SO_PRICE_LISTS_TL set
376 NAME = X_NAME,
377 DESCRIPTION = X_DESCRIPTION,
378 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
379 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
380 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
381 SOURCE_LANG = userenv('LANG')
382 where PRICE_LIST_ID = X_PRICE_LIST_ID
383 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
384
385 if (sql%notfound) then
386 raise no_data_found;
387 end if;
388 end UPDATE_ROW;
389
390 procedure DELETE_ROW (
391 X_PRICE_LIST_ID in NUMBER
392 ) is
393 begin
394 delete from SO_PRICE_LISTS_TL
395 where PRICE_LIST_ID = X_PRICE_LIST_ID;
396
397 if (sql%notfound) then
398 raise no_data_found;
399 end if;
400
401 delete from SO_PRICE_LISTS_B
402 where PRICE_LIST_ID = X_PRICE_LIST_ID;
403
404 if (sql%notfound) then
405 raise no_data_found;
406 end if;
407 end DELETE_ROW;
408
409 procedure ADD_LANGUAGE
410 is
411 begin
412 delete from SO_PRICE_LISTS_TL T
413 where not exists
414 (select NULL
415 from SO_PRICE_LISTS_B B
416 where B.PRICE_LIST_ID = T.PRICE_LIST_ID
417 );
418
419 update SO_PRICE_LISTS_TL T set (
420 NAME,
421 DESCRIPTION
422 ) = (select
423 B.NAME,
424 B.DESCRIPTION
425 from SO_PRICE_LISTS_TL B
426 where B.PRICE_LIST_ID = T.PRICE_LIST_ID
427 and B.LANGUAGE = T.SOURCE_LANG)
428 where (
429 T.PRICE_LIST_ID,
430 T.LANGUAGE
431 ) in (select
432 SUBT.PRICE_LIST_ID,
433 SUBT.LANGUAGE
434 from SO_PRICE_LISTS_TL SUBB, SO_PRICE_LISTS_TL SUBT
435 where SUBB.PRICE_LIST_ID = SUBT.PRICE_LIST_ID
436 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
437 and (SUBB.NAME <> SUBT.NAME
438 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
439 ));
440
441 insert into SO_PRICE_LISTS_TL (
442 PRICE_LIST_ID,
443 NAME,
444 DESCRIPTION,
445 CREATION_DATE,
446 CREATED_BY,
447 LAST_UPDATE_DATE,
448 LAST_UPDATED_BY,
449 LAST_UPDATE_LOGIN,
450 LANGUAGE,
451 SOURCE_LANG
452 ) select
453 B.PRICE_LIST_ID,
454 B.NAME,
455 B.DESCRIPTION,
456 B.CREATION_DATE,
457 B.CREATED_BY,
458 B.LAST_UPDATE_DATE,
459 B.LAST_UPDATED_BY,
460 B.LAST_UPDATE_LOGIN,
461 L.LANGUAGE_CODE,
462 B.SOURCE_LANG
463 from SO_PRICE_LISTS_TL B, FND_LANGUAGES L
464 where L.INSTALLED_FLAG in ('I', 'B')
465 and B.LANGUAGE = userenv('LANG')
466 and not exists
467 (select NULL
468 from SO_PRICE_LISTS_TL T
469 where T.PRICE_LIST_ID = B.PRICE_LIST_ID
470 and T.LANGUAGE = L.LANGUAGE_CODE);
471 end ADD_LANGUAGE;
472
473 end SO_PRICE_LISTS_PKG;
[Home] [Help]