[Home] [Help]
PACKAGE BODY: APPS.ICX_POR_ITEM_SOURCES_PKG
Source
1 PACKAGE BODY ICX_POR_ITEM_SOURCES_PKG AS
2 /* $Header: ICXSRCB.pls 115.8 2004/03/31 18:47:15 vkartik ship $ */
3
4 procedure INSERT_ROW (
5 X_ROWID in out NOCOPY VARCHAR2,
6 X_ITEM_SOURCE_ID in NUMBER,
7 X_TYPE in VARCHAR2,
8 X_PROTOCOL_SUPPORTED in VARCHAR2,
9 X_URL in VARCHAR2,
10 X_IMAGE_URL in VARCHAR2,
11 X_ITEM_SOURCE_NAME in VARCHAR2,
12 X_DESCRIPTION in VARCHAR2,
13 X_CREATION_DATE in DATE,
14 X_CREATED_BY in NUMBER,
15 X_LAST_UPDATE_DATE in DATE,
16 X_LAST_UPDATED_BY in NUMBER,
17 X_LAST_UPDATE_LOGIN in NUMBER
18 ) is
19 cursor C is select ROWID from ICX_POR_ITEM_SOURCES
20 where ITEM_SOURCE_ID = X_ITEM_SOURCE_ID
21 ;
22 begin
23
24 insert into ICX_POR_ITEM_SOURCES (
25 ITEM_SOURCE_ID,
26 TYPE,
27 PROTOCOL_SUPPORTED,
28 URL,
29 IMAGE_URL,
30 CREATION_DATE,
31 CREATED_BY,
32 LAST_UPDATE_DATE,
33 LAST_UPDATED_BY,
34 LAST_UPDATE_LOGIN
35 ) values (
36 X_ITEM_SOURCE_ID,
37 X_TYPE,
38 X_PROTOCOL_SUPPORTED,
39 X_URL,
40 X_IMAGE_URL,
41 X_CREATION_DATE,
42 X_CREATED_BY,
43 X_LAST_UPDATE_DATE,
44 X_LAST_UPDATED_BY,
45 X_LAST_UPDATE_LOGIN
46 );
47
48 insert into ICX_POR_ITEM_SOURCES_TL (
49 ITEM_SOURCE_ID,
50 ITEM_SOURCE_NAME,
51 DESCRIPTION,
52 CREATION_DATE,
53 CREATED_BY,
54 LAST_UPDATE_DATE,
55 LAST_UPDATED_BY,
56 LAST_UPDATE_LOGIN,
57 LANGUAGE,
58 SOURCE_LANG
59 ) select
60 X_ITEM_SOURCE_ID,
61 X_ITEM_SOURCE_NAME,
62 X_DESCRIPTION,
63 X_CREATION_DATE,
64 X_CREATED_BY,
65 X_LAST_UPDATE_DATE,
66 X_LAST_UPDATED_BY,
67 X_LAST_UPDATE_LOGIN,
68 L.LANGUAGE_CODE,
69 userenv('LANG')
70 from FND_LANGUAGES L
71 where L.INSTALLED_FLAG in ('I', 'B')
72 and not exists
73 (select NULL
74 from ICX_POR_ITEM_SOURCES_TL T
75 where T.ITEM_SOURCE_ID = X_ITEM_SOURCE_ID
76 and T.LANGUAGE = L.LANGUAGE_CODE);
77
78 open c;
79 fetch c into X_ROWID;
80 if (c%notfound) then
81 close c;
82 raise no_data_found;
83 end if;
84 close c;
85
86 end INSERT_ROW;
87
88 procedure UPDATE_ROW (
89 X_ITEM_SOURCE_ID in NUMBER,
90 X_TYPE in VARCHAR2,
91 X_PROTOCOL_SUPPORTED in VARCHAR2,
92 X_URL in VARCHAR2,
93 X_IMAGE_URL in VARCHAR2,
94 X_ITEM_SOURCE_NAME in VARCHAR2,
95 X_DESCRIPTION in VARCHAR2,
96 X_LAST_UPDATE_DATE in DATE,
97 X_LAST_UPDATED_BY in NUMBER,
98 X_LAST_UPDATE_LOGIN in NUMBER
99 ) is
100 begin
101 update ICX_POR_ITEM_SOURCES_TL set
102 ITEM_SOURCE_NAME = X_ITEM_SOURCE_NAME,
103 DESCRIPTION = X_DESCRIPTION,
104 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
105 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
106 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
107 SOURCE_LANG = userenv('LANG')
108 where ITEM_SOURCE_ID = X_ITEM_SOURCE_ID
109 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
110
111 if (sql%notfound) then
112 insert into ICX_POR_ITEM_SOURCES_TL (
113 ITEM_SOURCE_ID,
114 ITEM_SOURCE_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 ) values (
124 X_ITEM_SOURCE_ID,
125 X_ITEM_SOURCE_NAME,
126 X_DESCRIPTION,
127 X_LAST_UPDATE_DATE,
128 X_LAST_UPDATED_BY,
129 X_LAST_UPDATE_DATE,
130 X_LAST_UPDATED_BY,
131 X_LAST_UPDATE_LOGIN,
132 userenv('LANG'),
133 userenv('LANG'));
134 end if;
135 end UPDATE_ROW;
136
137 procedure TRANSLATE_ROW(
138 X_ITEM_SOURCE_ID in VARCHAR2,
139 X_OWNER in VARCHAR2,
140 X_ITEM_SOURCE_NAME in VARCHAR2,
141 X_DESCRIPTION in VARCHAR2,
142 X_CUSTOM_MODE in VARCHAR2,
143 X_LAST_UPDATE_DATE in VARCHAR2) IS
144 begin
145 declare
146 f_luby number; -- entity owner in file
147 f_ludate date; -- entity update in file
148 db_luby number; -- entity owner in db
149 db_ludate date; -- entity update in db
150 begin
151 -- Translate owner to file_last_updated_by
152 f_luby := fnd_load_util.OWNER_ID(X_OWNER);
153 f_ludate := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
154
155 select LAST_UPDATED_BY, LAST_UPDATE_DATE
156 into db_luby, db_ludate
157 from ICX_POR_ITEM_SOURCES_TL
158 where LANGUAGE = userenv('LANG')
159 and ITEM_SOURCE_ID = to_number(X_ITEM_SOURCE_ID);
160
161 -- Update record, honoring customization mode.
162 -- Record should be updated only if:
163 -- a. CUSTOM_MODE = FORCE, or
164 -- b. file owner is CUSTOM, db owner is SEED
165 -- c. owners are the same, and file_date > db_date
166 if (fnd_load_util.UPLOAD_TEST(
167 p_file_id => f_luby,
168 p_file_lud => f_ludate,
169 p_db_id => db_luby,
170 p_db_lud => db_ludate,
171 p_custom_mode => X_CUSTOM_MODE))
172 then
173 update icx_por_item_sources_tl set
174 item_source_name = nvl(X_ITEM_SOURCE_NAME, ITEM_SOURCE_NAME),
175 description = nvl(X_DESCRIPTION, DESCRIPTION),
176 last_update_date = sysdate,
177 last_updated_by = f_luby,
178 last_update_login = 0,
179 source_lang = userenv('LANG')
180 where ITEM_SOURCE_ID = to_number(X_ITEM_SOURCE_ID)
181 and userenv('LANG') in (language, source_lang);
182 end if;
183 end;
184
185 end TRANSLATE_ROW;
186
187
188 procedure LOAD_ROW(
189 X_ITEM_SOURCE_ID in VARCHAR2,
190 X_OWNER in VARCHAR2,
191 X_ITEM_SOURCE_NAME in VARCHAR2,
192 X_DESCRIPTION in VARCHAR2,
193 X_TYPE in VARCHAR2,
194 X_PROTOCOL_SUPPORTED in VARCHAR2,
195 X_URL in VARCHAR2,
196 X_IMAGE_URL in VARCHAR2,
197 X_CUSTOM_MODE in VARCHAR2,
198 X_LAST_UPDATE_DATE in VARCHAR2) IS
199 begin
200
201 declare
202 row_id varchar2(64);
203 f_luby number; -- entity owner in file
204 f_ludate date; -- entity update in file
205 db_luby number; -- entity owner in db
206 db_ludate date; -- entity update in db
207
208 begin
209 -- Translate owner to file_last_updated_by
210 f_luby := fnd_load_util.OWNER_ID(X_OWNER);
211 f_ludate := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
212
213 select LAST_UPDATED_BY, LAST_UPDATE_DATE
214 into db_luby, db_ludate
215 from ICX_POR_ITEM_SOURCES_TL
216 where LANGUAGE = userenv('LANG')
217 and ITEM_SOURCE_ID = to_number(X_ITEM_SOURCE_ID);
218
219 -- Bug#3219138
220 -- Always update the Type+Protocol_supported
221 -- irrespective of customization. Cst should not change the
222 -- type+protocol_supported values.
223 update ICX_POR_ITEM_SOURCES set
224 TYPE = X_TYPE,
225 PROTOCOL_SUPPORTED = X_PROTOCOL_SUPPORTED,
226 URL = X_URL,
227 IMAGE_URL = X_IMAGE_URL,
228 LAST_UPDATE_DATE = sysdate,
229 LAST_UPDATED_BY = f_luby,
230 LAST_UPDATE_LOGIN = 0
231 where ITEM_SOURCE_ID = X_ITEM_SOURCE_ID;
232
233 if (sql%notfound) then
234 raise no_data_found;
235 end if;
236
237 -- Update record, honoring customization mode.
238 -- Record should be updated only if:
239 -- a. CUSTOM_MODE = FORCE, or
240 -- b. file owner is CUSTOM, db owner is SEED
241 -- c. owners are the same, and file_date > db_date
242 if (fnd_load_util.UPLOAD_TEST(
243 p_file_id => f_luby,
244 p_file_lud => f_ludate,
245 p_db_id => db_luby,
246 p_db_lud => db_ludate,
247 p_custom_mode => X_CUSTOM_MODE))
248 then
249 ICX_POR_ITEM_SOURCES_PKG.UPDATE_ROW (
250 X_ITEM_SOURCE_ID => to_number(X_ITEM_SOURCE_ID),
251 X_TYPE => X_TYPE,
252 X_PROTOCOL_SUPPORTED => X_PROTOCOL_SUPPORTED,
253 X_URL => X_URL,
254 X_IMAGE_URL => X_IMAGE_URL,
255 X_ITEM_SOURCE_NAME => X_ITEM_SOURCE_NAME,
256 X_DESCRIPTION => X_DESCRIPTION,
257 X_LAST_UPDATE_DATE => sysdate,
258 X_LAST_UPDATED_BY => f_luby,
259 X_LAST_UPDATE_LOGIN => 0 );
260 end if;
261 exception
262 when NO_DATA_FOUND then
263 ICX_POR_ITEM_SOURCES_PKG.INSERT_ROW (
264 X_ROWID => row_id,
265 X_ITEM_SOURCE_ID => to_number(X_ITEM_SOURCE_ID),
266 X_TYPE => X_TYPE,
267 X_PROTOCOL_SUPPORTED => X_PROTOCOL_SUPPORTED,
268 X_URL => X_URL,
269 X_IMAGE_URL => X_IMAGE_URL,
270 X_ITEM_SOURCE_NAME => X_ITEM_SOURCE_NAME,
271 X_DESCRIPTION => X_DESCRIPTION,
272 X_CREATION_DATE => sysdate,
273 X_CREATED_BY => f_luby,
274 X_LAST_UPDATE_DATE => sysdate,
275 X_LAST_UPDATED_BY => f_luby,
276 X_LAST_UPDATE_LOGIN => 0 );
277 end;
278 end LOAD_ROW;
279
280
281 procedure ADD_LANGUAGE
282 is
283 begin
284 delete from ICX_POR_ITEM_SOURCES_TL T
285 where not exists
286 (select NULL
287 from ICX_POR_ITEM_SOURCES B
288 where B.ITEM_SOURCE_ID = T.ITEM_SOURCE_ID
289 );
290
291 /*
292 update ICX_POR_ITEM_SOURCES_TL T set (
293 ITEM_SOURCE_NAME,
294 DESCRIPTION
295 ) = (select
296 B.ITEM_SOURCE_NAME,
297 B.DESCRIPTION
298 from ICX_POR_ITEM_SOURCES_TL B
299 where B.ITEM_SOURCE_ID = T.ITEM_SOURCE_ID
300 and B.LANGUAGE = T.SOURCE_LANG)
301 where (
302 T.ITEM_SOURCE_ID,
303 T.LANGUAGE
304 ) in (select
305 SUBT.ITEM_SOURCE_ID,
306 SUBT.LANGUAGE
307 from ICX_POR_ITEM_SOURCES_TL SUBB, ICX_POR_ITEM_SOURCES_TL SUBT
308 where SUBB.ITEM_SOURCE_ID = SUBT.ITEM_SOURCE_ID
309 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
310 and (SUBB.ITEM_SOURCE_NAME <> SUBT.ITEM_SOURCE_NAME
311 or (SUBB.ITEM_SOURCE_NAME is null and SUBT.ITEM_SOURCE_NAME is not null)
312 or (SUBB.ITEM_SOURCE_NAME is not null and SUBT.ITEM_SOURCE_NAME is null)
313 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
314 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
315 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
316 ));
317 */
318
319 insert into ICX_POR_ITEM_SOURCES_TL (
320 LAST_UPDATE_LOGIN,
321 LAST_UPDATE_DATE,
322 LAST_UPDATED_BY,
323 CREATION_DATE,
324 CREATED_BY,
325 ITEM_SOURCE_ID,
326 ITEM_SOURCE_NAME,
327 DESCRIPTION,
328 LANGUAGE,
329 SOURCE_LANG
330 ) select
331 B.LAST_UPDATE_LOGIN,
332 B.LAST_UPDATE_DATE,
333 B.LAST_UPDATED_BY,
334 B.CREATION_DATE,
335 B.CREATED_BY,
336 B.ITEM_SOURCE_ID,
337 B.ITEM_SOURCE_NAME,
338 B.DESCRIPTION,
339 L.LANGUAGE_CODE,
340 B.SOURCE_LANG
341 from ICX_POR_ITEM_SOURCES_TL B, FND_LANGUAGES L
342 where L.INSTALLED_FLAG in ('I', 'B')
343 and B.LANGUAGE = userenv('LANG')
344 and not exists
345 (select NULL
346 from ICX_POR_ITEM_SOURCES_TL T
347 where T.ITEM_SOURCE_ID = B.ITEM_SOURCE_ID
348 and T.LANGUAGE = L.LANGUAGE_CODE);
349 end ADD_LANGUAGE;
350
351 end ICX_POR_ITEM_SOURCES_PKG;