[Home] [Help]
PACKAGE BODY: APPS.ICX_CAT_CONTENT_ZONES_PVT
Source
1 PACKAGE BODY ICX_CAT_CONTENT_ZONES_PVT AS
2 /* $Header: ICXVZNEB.pls 120.8.12000000.3 2007/09/19 17:30:16 kkram ship $ */
3
4 PROCEDURE INSERT_ROW(X_ROWID IN OUT NOCOPY VARCHAR2,
5 X_ZONE_ID IN NUMBER,
6 X_TYPE IN VARCHAR2,
7 X_URL IN VARCHAR2,
8 X_IMAGE IN VARCHAR2,
9 X_NAME IN VARCHAR2,
10 X_DESCRIPTION IN VARCHAR2,
11 X_SUPPLIER_ATTRIBUTE_ACTION IN VARCHAR2,
12 X_CATEGORY_ATTRIBUTE_ACTION IN VARCHAR2,
13 X_ITEMS_WITHOUT_SUPPLIER IN VARCHAR2,
14 X_ITEMS_WITHOUT_SHOP_CATG IN VARCHAR2,
15 X_SECURITY_ASSIGNMENT_FLAG IN VARCHAR2,
16 X_CREATION_DATE IN DATE,
17 X_CREATED_BY IN NUMBER,
18 X_LAST_UPDATE_DATE IN DATE,
19 X_LAST_UPDATED_BY IN NUMBER,
20 X_LAST_UPDATE_LOGIN IN NUMBER) IS
21 CURSOR C IS
22 SELECT ROWID
23 FROM ICX_CAT_CONTENT_ZONES_B
24 WHERE ZONE_ID = X_ZONE_ID;
25 BEGIN
26
27 INSERT INTO ICX_CAT_CONTENT_ZONES_B
28 (ZONE_ID,
29 TYPE,
30 URL,
31 SUPPLIER_ATTRIBUTE_ACTION_FLAG,
32 CATEGORY_ATTRIBUTE_ACTION_FLAG,
33 ITEMS_WITHOUT_SUPPLIER_FLAG,
34 ITEMS_WITHOUT_SHOP_CATG_FLAG,
35 SECURITY_ASSIGNMENT_FLAG,
36 CREATION_DATE,
37 CREATED_BY,
38 LAST_UPDATE_DATE,
39 LAST_UPDATED_BY,
40 LAST_UPDATE_LOGIN)
41 VALUES
42 (X_ZONE_ID,
43 X_TYPE,
44 X_URL,
45 X_SUPPLIER_ATTRIBUTE_ACTION,
46 X_CATEGORY_ATTRIBUTE_ACTION,
47 X_ITEMS_WITHOUT_SUPPLIER,
48 X_ITEMS_WITHOUT_SHOP_CATG,
49 X_SECURITY_ASSIGNMENT_FLAG,
50 X_CREATION_DATE,
51 X_CREATED_BY,
52 X_LAST_UPDATE_DATE,
53 X_LAST_UPDATED_BY,
54 X_LAST_UPDATE_LOGIN);
55
56 INSERT INTO ICX_CAT_CONTENT_ZONES_TL
57 (ZONE_ID,
58 NAME,
59 DESCRIPTION,
60 IMAGE,
61 CREATION_DATE,
62 CREATED_BY,
63 LAST_UPDATE_DATE,
64 LAST_UPDATED_BY,
65 LAST_UPDATE_LOGIN,
66 LANGUAGE,
67 SOURCE_LANG)
68 SELECT X_ZONE_ID,
69 X_NAME,
70 X_DESCRIPTION,
71 X_IMAGE,
72 X_CREATION_DATE,
73 X_CREATED_BY,
74 X_LAST_UPDATE_DATE,
75 X_LAST_UPDATED_BY,
76 X_LAST_UPDATE_LOGIN,
77 L.LANGUAGE_CODE,
78 userenv('LANG')
79 FROM FND_LANGUAGES L
80 WHERE L.INSTALLED_FLAG IN ('I', 'B')
81 AND NOT EXISTS
82 (SELECT NULL
83 FROM ICX_CAT_CONTENT_ZONES_TL T
84 WHERE T.ZONE_ID = X_ZONE_ID
85 AND T.LANGUAGE = L.LANGUAGE_CODE);
86
87 OPEN c;
88 FETCH c
89 INTO X_ROWID;
90 IF (c%NOTFOUND)
91 THEN
92 CLOSE c;
93 RAISE no_data_found;
94 END IF;
95 CLOSE c;
96
97 END INSERT_ROW;
98
99 PROCEDURE UPDATE_ROW(X_ZONE_ID IN NUMBER,
100 X_TYPE IN VARCHAR2,
101 X_URL IN VARCHAR2,
102 X_IMAGE IN VARCHAR2,
103 X_NAME IN VARCHAR2,
104 X_DESCRIPTION IN VARCHAR2,
105 X_SUPPLIER_ATTRIBUTE_ACTION IN VARCHAR2,
106 X_CATEGORY_ATTRIBUTE_ACTION IN VARCHAR2,
107 X_ITEMS_WITHOUT_SUPPLIER IN VARCHAR2,
108 X_ITEMS_WITHOUT_SHOP_CATG IN VARCHAR2,
109 X_SECURITY_ASSIGNMENT_FLAG IN VARCHAR2,
110 X_LAST_UPDATE_DATE IN DATE,
111 X_LAST_UPDATED_BY IN NUMBER,
112 X_LAST_UPDATE_LOGIN IN NUMBER) IS
113 BEGIN
114 UPDATE ICX_CAT_CONTENT_ZONES_TL
115 SET NAME = X_NAME,
116 DESCRIPTION = X_DESCRIPTION,
117 IMAGE = X_IMAGE,
118 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
119 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
120 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
121 SOURCE_LANG = userenv('LANG')
122 WHERE ZONE_ID = X_ZONE_ID
123 AND userenv('LANG') IN (LANGUAGE, SOURCE_LANG);
124
125 IF (SQL%NOTFOUND)
126 THEN
127 INSERT INTO ICX_CAT_CONTENT_ZONES_TL
128 (ZONE_ID,
129 NAME,
130 DESCRIPTION,
131 IMAGE,
132 CREATION_DATE,
133 CREATED_BY,
134 LAST_UPDATE_DATE,
135 LAST_UPDATED_BY,
136 LAST_UPDATE_LOGIN,
137 LANGUAGE,
138 SOURCE_LANG)
139 VALUES
140 (X_ZONE_ID,
141 X_NAME,
142 X_DESCRIPTION,
143 X_IMAGE,
144 X_LAST_UPDATE_DATE,
145 X_LAST_UPDATED_BY,
146 X_LAST_UPDATE_DATE,
147 X_LAST_UPDATED_BY,
148 X_LAST_UPDATE_LOGIN,
149 userenv('LANG'),
150 userenv('LANG'));
151 END IF;
152 END UPDATE_ROW;
153
154 PROCEDURE TRANSLATE_ROW(X_ZONE_ID IN VARCHAR2,
155 X_OWNER IN VARCHAR2,
156 X_NAME IN VARCHAR2,
157 X_DESCRIPTION IN VARCHAR2,
158 X_IMAGE IN VARCHAR2,
159 X_CUSTOM_MODE IN VARCHAR2,
160 X_LAST_UPDATE_DATE IN VARCHAR2) IS
161 BEGIN
162 DECLARE
163 f_luby NUMBER; -- entity owner in file
164 f_ludate DATE; -- entity update in file
165 db_luby NUMBER; -- entity owner in db
166 db_ludate DATE; -- entity update in db
167 BEGIN
168 -- Translate owner to file_last_updated_by
169 f_luby := fnd_load_util.OWNER_ID(X_OWNER);
170 f_ludate := nvl(to_date(X_LAST_UPDATE_DATE,
171 'YYYY/MM/DD'),
172 SYSDATE);
173
174 SELECT LAST_UPDATED_BY,
175 LAST_UPDATE_DATE
176 INTO db_luby,
177 db_ludate
178 FROM ICX_CAT_CONTENT_ZONES_TL
179 WHERE LANGUAGE = userenv('LANG')
180 AND ZONE_ID = to_number(X_ZONE_ID);
181
182 -- Bug : 6120281 - Start
183 -- 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
184 IF db_luby = -1 and f_luby <> -1 and to_number(X_ZONE_ID) in (1,2) THEN
185 db_luby := f_luby;
186 END IF;
187 -- Bug : 6120281 - Start
188
189
190 -- Update record, honoring customization mode.
191 -- Record should be updated only if:
192 -- a. CUSTOM_MODE = FORCE, or
193 -- b. file owner is CUSTOM, db owner is SEED
194 -- c. owners are the same, and file_date > db_date
195 IF (fnd_load_util.UPLOAD_TEST(p_file_id => f_luby,
196 p_file_lud => f_ludate,
197 p_db_id => db_luby,
198 p_db_lud => db_ludate,
199 p_custom_mode => X_CUSTOM_MODE))
200 THEN
201 UPDATE ICX_CAT_CONTENT_ZONES_tl
202 SET NAME = nvl(X_NAME,
203 NAME),
204 description = nvl(X_DESCRIPTION,
205 DESCRIPTION),
206 image = nvl(X_IMAGE,
207 IMAGE),
208 last_update_date = f_ludate,
209 last_updated_by = f_luby,
210 last_update_login = 0,
211 source_lang = userenv('LANG')
212 WHERE ZONE_ID = to_number(X_ZONE_ID)
213 AND userenv('LANG') IN (LANGUAGE, source_lang);
214 END IF;
215 END;
216
217 END TRANSLATE_ROW;
218
219 PROCEDURE LOAD_ROW(X_ZONE_ID IN VARCHAR2,
220 X_OWNER IN VARCHAR2,
221 X_NAME IN VARCHAR2,
222 X_DESCRIPTION IN VARCHAR2,
223 X_TYPE IN VARCHAR2,
224 X_URL IN VARCHAR2,
225 X_IMAGE IN VARCHAR2,
226 X_SUPPLIER_ATTRIBUTE_ACTION IN VARCHAR2,
227 X_CATEGORY_ATTRIBUTE_ACTION IN VARCHAR2,
228 X_ITEMS_WITHOUT_SUPPLIER IN VARCHAR2,
229 X_ITEMS_WITHOUT_SHOP_CATG IN VARCHAR2,
230 X_SECURITY_ASSIGNMENT_FLAG IN VARCHAR2,
231 X_CUSTOM_MODE IN VARCHAR2,
232 X_LAST_UPDATE_DATE IN VARCHAR2) IS
233 BEGIN
234
235 DECLARE
236 row_id VARCHAR2(64);
237 f_luby NUMBER; -- entity owner in file
238 f_ludate DATE; -- entity update in file
239 db_luby NUMBER; -- entity owner in db
240 db_ludate DATE; -- entity update in db
241
242 BEGIN
243 -- Translate owner to file_last_updated_by
244 f_luby := fnd_load_util.OWNER_ID(X_OWNER);
245 f_ludate := nvl(to_date(X_LAST_UPDATE_DATE,
246 'YYYY/MM/DD'),
247 SYSDATE);
248
249 SELECT LAST_UPDATED_BY,
250 LAST_UPDATE_DATE
251 INTO db_luby,
252 db_ludate
253 FROM ICX_CAT_CONTENT_ZONES_TL
254 WHERE LANGUAGE = userenv('LANG')
255 AND ZONE_ID = to_number(X_ZONE_ID);
256
257 -- Bug#3219138
258 -- Always update the Type supported
259 -- irrespective of customization. Cst should not change the
260 -- type supported values.
261 UPDATE ICX_CAT_CONTENT_ZONES_B
262 SET TYPE = X_TYPE,
263 URL = X_URL,
264 SUPPLIER_ATTRIBUTE_ACTION_FLAG = X_SUPPLIER_ATTRIBUTE_ACTION,
265 CATEGORY_ATTRIBUTE_ACTION_FLAG = X_CATEGORY_ATTRIBUTE_ACTION,
266 ITEMS_WITHOUT_SUPPLIER_FLAG = X_ITEMS_WITHOUT_SUPPLIER,
267 ITEMS_WITHOUT_SHOP_CATG_FLAG = X_ITEMS_WITHOUT_SHOP_CATG,
268 SECURITY_ASSIGNMENT_FLAG = X_SECURITY_ASSIGNMENT_FLAG,
269 LAST_UPDATE_DATE = f_ludate,
270 LAST_UPDATED_BY = f_luby,
271 LAST_UPDATE_LOGIN = 0
272 WHERE ZONE_ID = X_ZONE_ID;
273
274 IF (SQL%NOTFOUND)
275 THEN
276 RAISE no_data_found;
277 END IF;
278
279 -- Bug : 6120281 - Start
280 -- 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
281 IF db_luby = -1 and f_luby <> -1 and to_number(X_ZONE_ID) in (1,2) THEN
282 db_luby := f_luby;
283 END IF;
284 -- Bug : 6120281 - Start
285
286
287 -- Update record, honoring customization mode.
288 -- Record should be updated only if:
289 -- a. CUSTOM_MODE = FORCE, or
290 -- b. file owner is CUSTOM, db owner is SEED
291 -- c. owners are the same, and file_date > db_date
292 IF (fnd_load_util.UPLOAD_TEST(p_file_id => f_luby,
293 p_file_lud => f_ludate,
294 p_db_id => db_luby,
295 p_db_lud => db_ludate,
296 p_custom_mode => X_CUSTOM_MODE))
297 THEN
298 ICX_CAT_CONTENT_ZONES_PVT.UPDATE_ROW(X_ZONE_ID => to_number(X_ZONE_ID),
299 X_TYPE => X_TYPE,
300 X_URL => X_URL,
301 X_IMAGE => X_IMAGE,
302 X_NAME => X_NAME,
303 X_DESCRIPTION => X_DESCRIPTION,
304 X_SUPPLIER_ATTRIBUTE_ACTION => X_SUPPLIER_ATTRIBUTE_ACTION,
305 X_CATEGORY_ATTRIBUTE_ACTION => X_CATEGORY_ATTRIBUTE_ACTION,
306 X_ITEMS_WITHOUT_SUPPLIER => X_ITEMS_WITHOUT_SUPPLIER,
307 X_ITEMS_WITHOUT_SHOP_CATG => X_ITEMS_WITHOUT_SHOP_CATG,
308 X_SECURITY_ASSIGNMENT_FLAG => X_SECURITY_ASSIGNMENT_FLAG,
309 X_LAST_UPDATE_DATE => f_ludate,
310 X_LAST_UPDATED_BY => f_luby,
311 X_LAST_UPDATE_LOGIN => 0);
312 END IF;
313 EXCEPTION
314 WHEN NO_DATA_FOUND THEN
315 ICX_CAT_CONTENT_ZONES_PVT.INSERT_ROW(X_ROWID => row_id,
316 X_ZONE_ID => to_number(X_ZONE_ID),
317 X_TYPE => X_TYPE,
318 X_URL => X_URL,
319 X_IMAGE => X_IMAGE,
320 X_NAME => X_NAME,
321 X_DESCRIPTION => X_DESCRIPTION,
322 X_SUPPLIER_ATTRIBUTE_ACTION => X_SUPPLIER_ATTRIBUTE_ACTION,
323 X_CATEGORY_ATTRIBUTE_ACTION => X_CATEGORY_ATTRIBUTE_ACTION,
324 X_ITEMS_WITHOUT_SUPPLIER => X_ITEMS_WITHOUT_SUPPLIER,
325 X_ITEMS_WITHOUT_SHOP_CATG => X_ITEMS_WITHOUT_SHOP_CATG,
326 X_SECURITY_ASSIGNMENT_FLAG => X_SECURITY_ASSIGNMENT_FLAG,
327 X_CREATION_DATE => f_ludate,
328 X_CREATED_BY => f_luby,
329 X_LAST_UPDATE_DATE => f_ludate,
330 X_LAST_UPDATED_BY => f_luby,
331 X_LAST_UPDATE_LOGIN => 0);
332 END;
333 END LOAD_ROW;
334
335 PROCEDURE ADD_LANGUAGE IS
336 BEGIN
337 DELETE FROM ICX_CAT_CONTENT_ZONES_TL T
338 WHERE NOT EXISTS (SELECT NULL
339 FROM ICX_CAT_CONTENT_ZONES_B B
340 WHERE B.ZONE_ID = T.ZONE_ID);
341
342 INSERT INTO ICX_CAT_CONTENT_ZONES_TL
343 (LAST_UPDATE_LOGIN,
344 LAST_UPDATE_DATE,
345 LAST_UPDATED_BY,
346 CREATION_DATE,
347 CREATED_BY,
348 ZONE_ID,
349 NAME,
350 DESCRIPTION,
351 IMAGE,
352 LANGUAGE,
353 SOURCE_LANG)
354 SELECT B.LAST_UPDATE_LOGIN,
355 B.LAST_UPDATE_DATE,
356 B.LAST_UPDATED_BY,
357 B.CREATION_DATE,
358 B.CREATED_BY,
359 B.ZONE_ID,
360 B.NAME,
361 B.DESCRIPTION,
362 B.IMAGE,
363 L.LANGUAGE_CODE,
364 B.SOURCE_LANG
365 FROM ICX_CAT_CONTENT_ZONES_TL B,
366 FND_LANGUAGES L
367 WHERE L.INSTALLED_FLAG IN ('I', 'B')
368 AND B.LANGUAGE = userenv('LANG')
369 AND NOT EXISTS
370 (SELECT NULL
371 FROM ICX_CAT_CONTENT_ZONES_TL T
372 WHERE T.ZONE_ID = B.ZONE_ID
373 AND T.LANGUAGE = L.LANGUAGE_CODE);
374 END ADD_LANGUAGE;
375
376 END ICX_CAT_CONTENT_ZONES_PVT;