1 PACKAGE BODY ICX_CAT_SHOP_STORES_PVT AS
2 /* $Header: ICXVSTRB.pls 120.2.12000000.3 2007/09/19 17:30:57 kkram ship $ */
3
4 PROCEDURE INSERT_ROW(X_ROWID IN OUT NOCOPY VARCHAR2,
5 X_STORE_ID IN NUMBER,
6 X_SEQUENCE IN NUMBER,
7 X_LOCAL_CONTENT_FIRST_FLAG IN VARCHAR2,
8 X_NAME IN VARCHAR2,
9 X_DESCRIPTION IN VARCHAR2,
10 X_LONG_DESCRIPTION IN VARCHAR2,
11 X_IMAGE 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) IS
17 CURSOR C IS
18 SELECT ROWID
19 FROM ICX_CAT_SHOP_STORES_B
20 WHERE STORE_ID = X_STORE_ID;
21 BEGIN
22 INSERT INTO ICX_CAT_SHOP_STORES_B
23 (STORE_ID,
24 SEQUENCE,
25 LOCAL_CONTENT_FIRST_FLAG,
26 CREATION_DATE,
27 CREATED_BY,
28 LAST_UPDATE_DATE,
29 LAST_UPDATED_BY,
30 LAST_UPDATE_LOGIN)
31 VALUES
32 (X_STORE_ID,
33 X_SEQUENCE,
34 X_LOCAL_CONTENT_FIRST_FLAG,
35 X_CREATION_DATE,
36 X_CREATED_BY,
37 X_LAST_UPDATE_DATE,
38 X_LAST_UPDATED_BY,
39 X_LAST_UPDATE_LOGIN);
40
41 INSERT INTO ICX_CAT_SHOP_STORES_TL
42 (STORE_ID,
43 NAME,
44 DESCRIPTION,
45 LONG_DESCRIPTION,
46 IMAGE,
47 CREATED_BY,
48 CREATION_DATE,
49 LAST_UPDATED_BY,
50 LAST_UPDATE_DATE,
51 LAST_UPDATE_LOGIN,
52 LANGUAGE,
53 SOURCE_LANG)
54 SELECT X_STORE_ID,
55 X_NAME,
56 X_DESCRIPTION,
57 X_LONG_DESCRIPTION,
58 X_IMAGE,
59 X_CREATED_BY,
60 X_CREATION_DATE,
61 X_LAST_UPDATED_BY,
62 X_LAST_UPDATE_DATE,
63 X_LAST_UPDATE_LOGIN,
64 L.LANGUAGE_CODE,
65 userenv('LANG')
66 FROM FND_LANGUAGES L
67 WHERE L.INSTALLED_FLAG IN ('I', 'B')
68 AND NOT EXISTS
69 (SELECT NULL
70 FROM ICX_CAT_SHOP_STORES_TL T
71 WHERE T.STORE_ID = X_STORE_ID
72 AND T.LANGUAGE = L.LANGUAGE_CODE);
73
74 OPEN c;
75 FETCH c
76 INTO X_ROWID;
77 IF (c%NOTFOUND)
78 THEN
79 CLOSE c;
80 RAISE no_data_found;
81 END IF;
82 CLOSE c;
83
84 END INSERT_ROW;
85
86 PROCEDURE LOCK_ROW(X_STORE_ID IN NUMBER,
87 X_SEQUENCE IN NUMBER,
88 X_LOCAL_CONTENT_FIRST_FLAG IN VARCHAR2,
89 X_NAME IN VARCHAR2,
90 X_DESCRIPTION IN VARCHAR2,
91 X_LONG_DESCRIPTION IN VARCHAR2,
92 X_IMAGE IN VARCHAR2) IS
93 CURSOR c IS
94 SELECT SEQUENCE,
95 LOCAL_CONTENT_FIRST_FLAG
96 FROM ICX_CAT_SHOP_STORES_B
97 WHERE STORE_ID = X_STORE_ID
98 FOR UPDATE OF STORE_ID NOWAIT;
99 recinfo c%ROWTYPE;
100
101 CURSOR c1 IS
102 SELECT NAME,
103 DESCRIPTION,
104 LONG_DESCRIPTION,
105 IMAGE,
106 decode(LANGUAGE,
107 userenv('LANG'),
108 'Y',
109 'N') BASELANG
110 FROM ICX_CAT_SHOP_STORES_TL
111 WHERE STORE_ID = X_STORE_ID
112 AND userenv('LANG') IN (LANGUAGE, SOURCE_LANG)
113 FOR UPDATE OF STORE_ID NOWAIT;
114 BEGIN
115 OPEN c;
116 FETCH c
117 INTO recinfo;
118 IF (c%NOTFOUND)
119 THEN
120 CLOSE c;
121 fnd_message.set_name('FND',
122 'FORM_RECORD_DELETED');
123 app_exception.raise_exception;
124 END IF;
125 CLOSE c;
126 IF (((recinfo.SEQUENCE = X_SEQUENCE) OR
127 ((recinfo.SEQUENCE IS NULL) AND (X_SEQUENCE IS NULL))) AND
128 ((recinfo.LOCAL_CONTENT_FIRST_FLAG = X_LOCAL_CONTENT_FIRST_FLAG) OR
129 ((recinfo.LOCAL_CONTENT_FIRST_FLAG IS NULL) AND
130 (X_LOCAL_CONTENT_FIRST_FLAG IS NULL))))
131 THEN
132 NULL;
133 ELSE
134 fnd_message.set_name('FND',
135 'FORM_RECORD_CHANGED');
136 app_exception.raise_exception;
137 END IF;
138
139 FOR tlinfo IN c1
140 LOOP
141 IF (tlinfo.BASELANG = 'Y')
142 THEN
143 IF (((tlinfo.NAME = X_NAME) OR
144 ((tlinfo.NAME IS NULL) AND (X_NAME IS NULL))) AND
145 ((tlinfo.DESCRIPTION = X_DESCRIPTION) OR
146 ((tlinfo.DESCRIPTION IS NULL) AND (X_DESCRIPTION IS NULL))) AND
147 ((tlinfo.LONG_DESCRIPTION = X_LONG_DESCRIPTION) OR
148 ((tlinfo.LONG_DESCRIPTION IS NULL) AND
149 (X_LONG_DESCRIPTION IS NULL))) AND
150 ((tlinfo.IMAGE = X_IMAGE) OR
151 ((tlinfo.IMAGE IS NULL) AND (X_IMAGE IS NULL))))
152 THEN
153 NULL;
154 ELSE
155 fnd_message.set_name('FND',
156 'FORM_RECORD_CHANGED');
157 app_exception.raise_exception;
158 END IF;
159 END IF;
160 END LOOP;
161 RETURN;
162 END LOCK_ROW;
163
164 PROCEDURE UPDATE_ROW(X_STORE_ID IN NUMBER,
165 X_SEQUENCE IN NUMBER,
166 X_LOCAL_CONTENT_FIRST_FLAG IN VARCHAR2,
167 X_NAME IN VARCHAR2,
168 X_DESCRIPTION IN VARCHAR2,
169 X_LONG_DESCRIPTION IN VARCHAR2,
170 X_IMAGE IN VARCHAR2,
171 X_LAST_UPDATE_DATE IN DATE,
172 X_LAST_UPDATED_BY IN NUMBER,
173 X_LAST_UPDATE_LOGIN IN NUMBER) IS
174 BEGIN
175 UPDATE ICX_CAT_SHOP_STORES_B
176 SET SEQUENCE = X_SEQUENCE,
177 LOCAL_CONTENT_FIRST_FLAG = X_LOCAL_CONTENT_FIRST_FLAG,
178 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
179 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
180 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
181 WHERE STORE_ID = X_STORE_ID;
182
183 IF (SQL%NOTFOUND)
184 THEN
185 RAISE no_data_found;
186 END IF;
187
188 UPDATE ICX_CAT_SHOP_STORES_TL
189 SET NAME = X_NAME,
190 DESCRIPTION = X_DESCRIPTION,
191 LONG_DESCRIPTION = X_LONG_DESCRIPTION,
192 IMAGE = X_IMAGE,
193 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
194 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
195 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
196 SOURCE_LANG = userenv('LANG')
197 WHERE STORE_ID = X_STORE_ID
198 AND userenv('LANG') IN (LANGUAGE, SOURCE_LANG);
199
200 IF (SQL%NOTFOUND)
201 THEN
202 RAISE no_data_found;
203 END IF;
204 END UPDATE_ROW;
205
206 PROCEDURE TRANSLATE_ROW(X_STORE_ID IN VARCHAR2,
207 X_OWNER IN VARCHAR2,
208 X_NAME IN VARCHAR2,
209 X_DESCRIPTION IN VARCHAR2,
210 X_LONG_DESCRIPTION IN VARCHAR2,
211 X_IMAGE IN VARCHAR2,
212 X_CUSTOM_MODE IN VARCHAR2,
213 X_LAST_UPDATE_DATE IN VARCHAR2) IS
214 BEGIN
215 DECLARE
216 f_luby NUMBER; -- entity owner in file
217 f_ludate DATE; -- entity update in file
218 db_luby NUMBER; -- entity owner in db
219 db_ludate DATE; -- entity update in db
220 BEGIN
221 -- Translate owner to file_last_updated_by
222 f_luby := fnd_load_util.OWNER_ID(X_OWNER);
223 f_ludate := nvl(to_date(X_LAST_UPDATE_DATE,
224 'YYYY/MM/DD'),
225 SYSDATE);
226
227 SELECT LAST_UPDATED_BY,
228 LAST_UPDATE_DATE
229 INTO db_luby,
230 db_ludate
231 FROM ICX_CAT_SHOP_STORES_TL
232 WHERE LANGUAGE = userenv('LANG')
233 AND STORE_ID = to_number(X_STORE_ID);
234
235
236 -- Bug : 6120281 - Start
237 -- Seed data was loaded with wrong OWNER. So updated_by column in db was having -1. Which should be treated as seeded for the seeded rows with id 1,2
238 IF db_luby = -1 and f_luby <> -1 and to_number(X_STORE_ID) in (1,2) THEN
239 db_luby := f_luby;
240 END IF;
241 -- Bug : 6120281 - End
242
243
244 -- Update record, honoring customization mode.
245 -- Record should be updated only if:
246 -- a. CUSTOM_MODE = FORCE, or
247 -- b. file owner is CUSTOM, db owner is SEED
248 -- c. owners are the same, and file_date > db_date
249 IF (fnd_load_util.UPLOAD_TEST(p_file_id => f_luby,
250 p_file_lud => f_ludate,
251 p_db_id => db_luby,
252 p_db_lud => db_ludate,
253 p_custom_mode => X_CUSTOM_MODE))
254 THEN
255 UPDATE ICX_CAT_SHOP_STORES_TL
256 SET NAME = nvl(X_NAME,
257 NAME),
258 DESCRIPTION = nvl(X_DESCRIPTION,
259 DESCRIPTION),
260 LONG_DESCRIPTION = nvl(X_LONG_DESCRIPTION,
261 LONG_DESCRIPTION),
262 IMAGE = nvl(X_IMAGE,
263 IMAGE),
264 last_update_date = f_ludate,
265 last_updated_by = f_luby,
266 last_update_login = 0,
267 source_lang = userenv('LANG')
268 WHERE STORE_ID = to_number(X_STORE_ID)
269 AND userenv('LANG') IN (LANGUAGE, source_lang);
270 END IF;
271 END;
272
273 END TRANSLATE_ROW;
274
275
276 PROCEDURE LOAD_ROW(X_STORE_ID IN VARCHAR2,
277 X_OWNER IN VARCHAR2,
278 X_SEQUENCE IN VARCHAR2,
279 X_LOCAL_CONTENT_FIRST_FLAG IN VARCHAR2,
280 X_NAME IN VARCHAR2,
281 X_DESCRIPTION IN VARCHAR2,
282 X_LONG_DESCRIPTION IN VARCHAR2,
283 X_IMAGE IN VARCHAR2,
284 X_CUSTOM_MODE IN VARCHAR2,
285 X_LAST_UPDATE_DATE IN VARCHAR2) IS
286 BEGIN
287
288 DECLARE
289 row_id VARCHAR2(64);
290 f_luby NUMBER; -- entity owner in file
291 f_ludate DATE; -- entity update in file
292 db_luby NUMBER; -- entity owner in db
293 db_ludate DATE; -- entity update in db
294
295 BEGIN
296 -- Translate owner to file_last_updated_by
297 f_luby := fnd_load_util.OWNER_ID(X_OWNER);
298 f_ludate := nvl(to_date(X_LAST_UPDATE_DATE,
299 'YYYY/MM/DD'),
300 SYSDATE);
301
302 SELECT LAST_UPDATED_BY,
303 LAST_UPDATE_DATE
304 INTO db_luby,
305 db_ludate
306 FROM ICX_CAT_SHOP_STORES_TL
307 WHERE LANGUAGE = userenv('LANG')
308 AND STORE_ID = to_number(X_STORE_ID);
309
310 -- Bug : 6120281 - Start
311 -- Seed data was loaded with wrong OWNER. So updated_by column in db was having -1. Which should be treated as seeded for the seeded rows with id 1,2
312 IF db_luby = -1 and f_luby <> -1 and to_number(X_STORE_ID) in (1,2) THEN
313 db_luby := f_luby;
314 END IF;
315 -- Bug : 6120281 - End
316
317 -- Update record, honoring customization mode.
318 -- Record should be updated only if:
319 -- a. CUSTOM_MODE = FORCE, or
320 -- b. file owner is CUSTOM, db owner is SEED
321 -- c. owners are the same, and file_date > db_date
322 IF (fnd_load_util.UPLOAD_TEST(p_file_id => f_luby,
323 p_file_lud => f_ludate,
324 p_db_id => db_luby,
325 p_db_lud => db_ludate,
326 p_custom_mode => X_CUSTOM_MODE))
327 THEN
328 ICX_CAT_SHOP_STORES_PVT.UPDATE_ROW(X_STORE_ID => to_number(X_STORE_ID),
329 X_SEQUENCE => to_number(X_SEQUENCE),
330 X_LOCAL_CONTENT_FIRST_FLAG => X_LOCAL_CONTENT_FIRST_FLAG,
331 X_NAME => X_NAME,
332 X_DESCRIPTION => X_DESCRIPTION,
333 X_LONG_DESCRIPTION => X_LONG_DESCRIPTION,
334 X_IMAGE => X_IMAGE,
335 X_LAST_UPDATE_DATE => f_ludate,
336 X_LAST_UPDATED_BY => f_luby,
337 X_LAST_UPDATE_LOGIN => 0);
338 END IF;
339 EXCEPTION
340 WHEN NO_DATA_FOUND THEN
341
342 ICX_CAT_SHOP_STORES_PVT.INSERT_ROW(X_ROWID => row_id,
343 X_STORE_ID => to_number(X_STORE_ID),
344 X_SEQUENCE => to_number(X_SEQUENCE),
345 X_LOCAL_CONTENT_FIRST_FLAG => X_LOCAL_CONTENT_FIRST_FLAG,
346 X_NAME => X_NAME,
347 X_DESCRIPTION => X_DESCRIPTION,
348 X_LONG_DESCRIPTION => X_LONG_DESCRIPTION,
349 X_IMAGE => X_IMAGE,
350 X_CREATION_DATE => f_ludate,
351 X_CREATED_BY => f_luby,
352 X_LAST_UPDATE_DATE => f_ludate,
353 X_LAST_UPDATED_BY => f_luby,
354 X_LAST_UPDATE_LOGIN => 0);
355 END;
356 END LOAD_ROW;
357
358 PROCEDURE DELETE_ROW(X_STORE_ID IN NUMBER) IS
359 BEGIN
360 DELETE FROM ICX_CAT_SHOP_STORES_TL
361 WHERE STORE_ID = X_STORE_ID;
362
363 IF (SQL%NOTFOUND)
364 THEN
365 RAISE no_data_found;
366 END IF;
367
368 DELETE FROM ICX_CAT_SHOP_STORES_B
369 WHERE STORE_ID = X_STORE_ID;
370
374 END IF;
371 IF (SQL%NOTFOUND)
372 THEN
373 RAISE no_data_found;
375 END DELETE_ROW;
376
377 PROCEDURE ADD_LANGUAGE IS
378 BEGIN
379 DELETE FROM ICX_CAT_SHOP_STORES_TL T
380 WHERE NOT EXISTS (SELECT NULL
381 FROM ICX_CAT_SHOP_STORES_B B
382 WHERE B.STORE_ID = T.STORE_ID);
383
384 UPDATE ICX_CAT_SHOP_STORES_TL T
385 SET (NAME, DESCRIPTION, LONG_DESCRIPTION, IMAGE) = (SELECT B.NAME,
386 B.DESCRIPTION,
387 B.LONG_DESCRIPTION,
388 B.IMAGE
389 FROM ICX_CAT_SHOP_STORES_TL B
390 WHERE B.STORE_ID =
391 T.STORE_ID
392 AND
393 B.LANGUAGE =
394 T.SOURCE_LANG)
395 WHERE (T.STORE_ID, T.LANGUAGE) IN
396 (SELECT SUBT.STORE_ID,
397 SUBT.LANGUAGE
398 FROM ICX_CAT_SHOP_STORES_TL SUBB,
399 ICX_CAT_SHOP_STORES_TL SUBT
400 WHERE SUBB.STORE_ID = SUBT.STORE_ID
401 AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
402 AND (SUBB.NAME <> SUBT.NAME OR
403 (SUBB.NAME IS NULL AND SUBT.NAME IS NOT NULL) OR
404 (SUBB.NAME IS NOT NULL AND SUBT.NAME IS NULL) OR
405 SUBB.DESCRIPTION <> SUBT.DESCRIPTION OR
406 (SUBB.DESCRIPTION IS NULL AND
407 SUBT.DESCRIPTION IS NOT NULL) OR
408 (SUBB.DESCRIPTION IS NOT NULL AND
409 SUBT.DESCRIPTION IS NULL) OR
410 SUBB.LONG_DESCRIPTION <> SUBT.LONG_DESCRIPTION OR
411 (SUBB.LONG_DESCRIPTION IS NULL AND
412 SUBT.LONG_DESCRIPTION IS NOT NULL) OR
413 (SUBB.LONG_DESCRIPTION IS NOT NULL AND
414 SUBT.LONG_DESCRIPTION IS NULL) OR
415 SUBB.IMAGE <> SUBT.IMAGE OR
416 (SUBB.IMAGE IS NULL AND SUBT.IMAGE IS NOT NULL) OR
417 (SUBB.IMAGE IS NOT NULL AND SUBT.IMAGE IS NULL)));
418
419 INSERT INTO ICX_CAT_SHOP_STORES_TL
420 (STORE_ID,
421 NAME,
422 DESCRIPTION,
423 LONG_DESCRIPTION,
424 IMAGE,
425 CREATED_BY,
426 CREATION_DATE,
427 LAST_UPDATED_BY,
428 LAST_UPDATE_DATE,
429 LAST_UPDATE_LOGIN,
430 LANGUAGE,
431 SOURCE_LANG)
432 SELECT /*+ ORDERED */
433 B.STORE_ID,
434 B.NAME,
435 B.DESCRIPTION,
436 B.LONG_DESCRIPTION,
437 B.IMAGE,
438 B.CREATED_BY,
439 B.CREATION_DATE,
440 B.LAST_UPDATED_BY,
441 B.LAST_UPDATE_DATE,
442 B.LAST_UPDATE_LOGIN,
443 L.LANGUAGE_CODE,
444 B.SOURCE_LANG
445 FROM ICX_CAT_SHOP_STORES_TL B,
446 FND_LANGUAGES L
447 WHERE L.INSTALLED_FLAG IN ('I', 'B')
448 AND B.LANGUAGE = userenv('LANG')
449 AND NOT EXISTS
450 (SELECT NULL
451 FROM ICX_CAT_SHOP_STORES_TL T
452 WHERE T.STORE_ID = B.STORE_ID
453 AND T.LANGUAGE = L.LANGUAGE_CODE);
454 END ADD_LANGUAGE;
455
456 END ICX_CAT_SHOP_STORES_PVT;
[Home] [Help]