1 package body EGO_ITM_GTN_MUL_ATTRS_PKG as
2 /* $Header: EGOVGTMB.pls 120.0 2005/10/14 04:10 jcgeorge noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_EXTENSION_ID in NUMBER,
6 X_REQUEST_ID in NUMBER,
7 X_SIZE_CODE_LIST_AGENCY in VARCHAR2,
8 X_SIZE_CODE_VALUE in VARCHAR2,
9 X_INVENTORY_ITEM_ID in NUMBER,
10 X_ORGANIZATION_ID in NUMBER,
11 X_BAR_CODE_TYPE in VARCHAR2,
12 X_COLOR_CODE_LIST_AGENCY in VARCHAR2,
13 X_COLOR_CODE_VALUE in VARCHAR2,
14 X_CLASS_OF_DANGEROUS_CODE in VARCHAR2,
15 X_DANGEROUS_GOODS_MARGIN_NUMBE in VARCHAR2,
16 X_DANGEROUS_GOODS_HAZARDOUS_CO in VARCHAR2,
17 X_DANGEROUS_GOODS_PACK_GROUP in VARCHAR2,
18 X_DANGEROUS_GOODS_REG_CODE in VARCHAR2,
19 X_DANGEROUS_GOODS_SHIPPING_NAM in VARCHAR2,
20 X_UNITED_NATIONS_DANG_GOODS_NO in NUMBER,
21 X_FLASH_POINT_TEMP in NUMBER,
22 X_UOM_FLASH_POINT_TEMP in VARCHAR2,
23 X_COUNTRY_OF_ORIGIN in VARCHAR2,
24 X_HARMONIZED_TARIFF_SYS_ID_COD in NUMBER,
25 X_DELIVERY_METHOD_INDICATOR in VARCHAR2,
26 X_ATTR_GROUP_ID in NUMBER,
27 X_MANUFACTURER_GLN in VARCHAR2,
28 X_MANUFACTURER_ID in NUMBER,
29 X_PARTY_RECEIVING_PRIVATE_DATA in VARCHAR2,
30 X_ITEM_CATALOG_GROUP_ID in NUMBER,
31 X_REVISION_ID in NUMBER,
32 X_HANDLING_INSTRUCTIONS_CODE in VARCHAR2,
33 X_DANGEROUS_GOODS_TECHNICAL_NA 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 EGO_ITM_GTN_MUL_ATTRS_B
41 where EXTENSION_ID = X_EXTENSION_ID
42 ;
43 begin
44 insert into EGO_ITM_GTN_MUL_ATTRS_B (
45 REQUEST_ID,
46 SIZE_CODE_LIST_AGENCY,
47 SIZE_CODE_VALUE,
48 EXTENSION_ID,
49 INVENTORY_ITEM_ID,
50 ORGANIZATION_ID,
51 BAR_CODE_TYPE,
52 COLOR_CODE_LIST_AGENCY,
53 COLOR_CODE_VALUE,
54 CLASS_OF_DANGEROUS_CODE,
55 DANGEROUS_GOODS_MARGIN_NUMBER,
56 DANGEROUS_GOODS_HAZARDOUS_CODE,
57 DANGEROUS_GOODS_PACK_GROUP,
58 DANGEROUS_GOODS_REG_CODE,
59 DANGEROUS_GOODS_SHIPPING_NAME,
60 UNITED_NATIONS_DANG_GOODS_NO,
61 FLASH_POINT_TEMP,
62 UOM_FLASH_POINT_TEMP,
63 COUNTRY_OF_ORIGIN,
64 HARMONIZED_TARIFF_SYS_ID_CODE,
65 DELIVERY_METHOD_INDICATOR,
66 ATTR_GROUP_ID,
67 MANUFACTURER_GLN,
68 MANUFACTURER_ID,
69 PARTY_RECEIVING_PRIVATE_DATA,
70 ITEM_CATALOG_GROUP_ID,
71 REVISION_ID,
72 CREATION_DATE,
73 CREATED_BY,
74 LAST_UPDATE_DATE,
75 LAST_UPDATED_BY,
76 LAST_UPDATE_LOGIN
77 ) values (
78 X_REQUEST_ID,
79 X_SIZE_CODE_LIST_AGENCY,
80 X_SIZE_CODE_VALUE,
81 X_EXTENSION_ID,
82 X_INVENTORY_ITEM_ID,
83 X_ORGANIZATION_ID,
84 X_BAR_CODE_TYPE,
85 X_COLOR_CODE_LIST_AGENCY,
86 X_COLOR_CODE_VALUE,
87 X_CLASS_OF_DANGEROUS_CODE,
88 X_DANGEROUS_GOODS_MARGIN_NUMBE,
89 X_DANGEROUS_GOODS_HAZARDOUS_CO,
90 X_DANGEROUS_GOODS_PACK_GROUP,
91 X_DANGEROUS_GOODS_REG_CODE,
92 X_DANGEROUS_GOODS_SHIPPING_NAM,
93 X_UNITED_NATIONS_DANG_GOODS_NO,
94 X_FLASH_POINT_TEMP,
95 X_UOM_FLASH_POINT_TEMP,
96 X_COUNTRY_OF_ORIGIN,
97 X_HARMONIZED_TARIFF_SYS_ID_COD,
98 X_DELIVERY_METHOD_INDICATOR,
99 X_ATTR_GROUP_ID,
100 X_MANUFACTURER_GLN,
101 X_MANUFACTURER_ID,
102 X_PARTY_RECEIVING_PRIVATE_DATA,
103 X_ITEM_CATALOG_GROUP_ID,
104 X_REVISION_ID,
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 EGO_ITM_GTN_MUL_ATTRS_TL (
113 REQUEST_ID,
114 EXTENSION_ID,
115 INVENTORY_ITEM_ID,
116 ORGANIZATION_ID,
117 ATTR_GROUP_ID,
118 CREATED_BY,
119 CREATION_DATE,
120 LAST_UPDATED_BY,
121 LAST_UPDATE_DATE,
122 LAST_UPDATE_LOGIN,
123 ITEM_CATALOG_GROUP_ID,
124 REVISION_ID,
125 HANDLING_INSTRUCTIONS_CODE,
126 DANGEROUS_GOODS_TECHNICAL_NAME,
127 LANGUAGE,
128 SOURCE_LANG
129 ) select
130 X_REQUEST_ID,
131 X_EXTENSION_ID,
132 X_INVENTORY_ITEM_ID,
133 X_ORGANIZATION_ID,
134 X_ATTR_GROUP_ID,
135 X_CREATED_BY,
136 X_CREATION_DATE,
137 X_LAST_UPDATED_BY,
138 X_LAST_UPDATE_DATE,
139 X_LAST_UPDATE_LOGIN,
140 X_ITEM_CATALOG_GROUP_ID,
141 X_REVISION_ID,
142 X_HANDLING_INSTRUCTIONS_CODE,
143 X_DANGEROUS_GOODS_TECHNICAL_NA,
144 L.LANGUAGE_CODE,
145 userenv('LANG')
146 from FND_LANGUAGES L
147 where L.INSTALLED_FLAG in ('I', 'B')
148 and not exists
149 (select NULL
150 from EGO_ITM_GTN_MUL_ATTRS_TL T
151 where T.EXTENSION_ID = X_EXTENSION_ID
152 and T.LANGUAGE = L.LANGUAGE_CODE);
153
154 open c;
155 fetch c into X_ROWID;
156 if (c%notfound) then
157 close c;
158 raise no_data_found;
159 end if;
160 close c;
161
162 end INSERT_ROW;
163
164 procedure LOCK_ROW (
165 X_EXTENSION_ID in NUMBER,
166 X_REQUEST_ID in NUMBER,
167 X_SIZE_CODE_LIST_AGENCY in VARCHAR2,
168 X_SIZE_CODE_VALUE in VARCHAR2,
169 X_INVENTORY_ITEM_ID in NUMBER,
170 X_ORGANIZATION_ID in NUMBER,
171 X_BAR_CODE_TYPE in VARCHAR2,
172 X_COLOR_CODE_LIST_AGENCY in VARCHAR2,
173 X_COLOR_CODE_VALUE in VARCHAR2,
174 X_CLASS_OF_DANGEROUS_CODE in VARCHAR2,
175 X_DANGEROUS_GOODS_MARGIN_NUMBE in VARCHAR2,
176 X_DANGEROUS_GOODS_HAZARDOUS_CO in VARCHAR2,
177 X_DANGEROUS_GOODS_PACK_GROUP in VARCHAR2,
178 X_DANGEROUS_GOODS_REG_CODE in VARCHAR2,
179 X_DANGEROUS_GOODS_SHIPPING_NAM in VARCHAR2,
180 X_UNITED_NATIONS_DANG_GOODS_NO in NUMBER,
181 X_FLASH_POINT_TEMP in NUMBER,
182 X_UOM_FLASH_POINT_TEMP in VARCHAR2,
183 X_COUNTRY_OF_ORIGIN in VARCHAR2,
184 X_HARMONIZED_TARIFF_SYS_ID_COD in NUMBER,
185 X_DELIVERY_METHOD_INDICATOR in VARCHAR2,
186 X_ATTR_GROUP_ID in NUMBER,
187 X_MANUFACTURER_GLN in VARCHAR2,
188 X_MANUFACTURER_ID in NUMBER,
189 X_PARTY_RECEIVING_PRIVATE_DATA in VARCHAR2,
190 X_ITEM_CATALOG_GROUP_ID in NUMBER,
191 X_REVISION_ID in NUMBER,
192 X_HANDLING_INSTRUCTIONS_CODE in VARCHAR2,
193 X_DANGEROUS_GOODS_TECHNICAL_NA in VARCHAR2
194 ) is
195 cursor c is select
196 REQUEST_ID,
197 SIZE_CODE_LIST_AGENCY,
198 SIZE_CODE_VALUE,
199 INVENTORY_ITEM_ID,
200 ORGANIZATION_ID,
201 BAR_CODE_TYPE,
202 COLOR_CODE_LIST_AGENCY,
203 COLOR_CODE_VALUE,
204 CLASS_OF_DANGEROUS_CODE,
205 DANGEROUS_GOODS_MARGIN_NUMBER,
206 DANGEROUS_GOODS_HAZARDOUS_CODE,
207 DANGEROUS_GOODS_PACK_GROUP,
208 DANGEROUS_GOODS_REG_CODE,
209 DANGEROUS_GOODS_SHIPPING_NAME,
210 UNITED_NATIONS_DANG_GOODS_NO,
211 FLASH_POINT_TEMP,
212 UOM_FLASH_POINT_TEMP,
213 COUNTRY_OF_ORIGIN,
214 HARMONIZED_TARIFF_SYS_ID_CODE,
215 DELIVERY_METHOD_INDICATOR,
216 ATTR_GROUP_ID,
217 MANUFACTURER_GLN,
218 MANUFACTURER_ID,
219 PARTY_RECEIVING_PRIVATE_DATA,
220 ITEM_CATALOG_GROUP_ID,
221 REVISION_ID
222 from EGO_ITM_GTN_MUL_ATTRS_B
223 where EXTENSION_ID = X_EXTENSION_ID
224 for update of EXTENSION_ID nowait;
225 recinfo c%rowtype;
226
227 cursor c1 is select
228 HANDLING_INSTRUCTIONS_CODE,
229 DANGEROUS_GOODS_TECHNICAL_NAME,
230 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
231 from EGO_ITM_GTN_MUL_ATTRS_TL
232 where EXTENSION_ID = X_EXTENSION_ID
233 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
234 for update of EXTENSION_ID nowait;
235 begin
236 open c;
237 fetch c into recinfo;
238 if (c%notfound) then
239 close c;
240 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
241 app_exception.raise_exception;
242 end if;
243 close c;
244 if ( ((recinfo.REQUEST_ID = X_REQUEST_ID)
245 OR ((recinfo.REQUEST_ID is null) AND (X_REQUEST_ID is null)))
246 AND ((recinfo.SIZE_CODE_LIST_AGENCY = X_SIZE_CODE_LIST_AGENCY)
247 OR ((recinfo.SIZE_CODE_LIST_AGENCY is null) AND (X_SIZE_CODE_LIST_AGENCY is null)))
248 AND ((recinfo.SIZE_CODE_VALUE = X_SIZE_CODE_VALUE)
249 OR ((recinfo.SIZE_CODE_VALUE is null) AND (X_SIZE_CODE_VALUE is null)))
250 AND (recinfo.INVENTORY_ITEM_ID = X_INVENTORY_ITEM_ID)
251 AND (recinfo.ORGANIZATION_ID = X_ORGANIZATION_ID)
252 AND ((recinfo.BAR_CODE_TYPE = X_BAR_CODE_TYPE)
253 OR ((recinfo.BAR_CODE_TYPE is null) AND (X_BAR_CODE_TYPE is null)))
254 AND ((recinfo.COLOR_CODE_LIST_AGENCY = X_COLOR_CODE_LIST_AGENCY)
255 OR ((recinfo.COLOR_CODE_LIST_AGENCY is null) AND (X_COLOR_CODE_LIST_AGENCY is null)))
256 AND ((recinfo.COLOR_CODE_VALUE = X_COLOR_CODE_VALUE)
257 OR ((recinfo.COLOR_CODE_VALUE is null) AND (X_COLOR_CODE_VALUE is null)))
258 AND ((recinfo.CLASS_OF_DANGEROUS_CODE = X_CLASS_OF_DANGEROUS_CODE)
259 OR ((recinfo.CLASS_OF_DANGEROUS_CODE is null) AND (X_CLASS_OF_DANGEROUS_CODE is null)))
260 AND ((recinfo.DANGEROUS_GOODS_MARGIN_NUMBER = X_DANGEROUS_GOODS_MARGIN_NUMBE)
261 OR ((recinfo.DANGEROUS_GOODS_MARGIN_NUMBER is null) AND (X_DANGEROUS_GOODS_MARGIN_NUMBE is null)))
262 AND ((recinfo.DANGEROUS_GOODS_HAZARDOUS_CODE = X_DANGEROUS_GOODS_HAZARDOUS_CO)
263 OR ((recinfo.DANGEROUS_GOODS_HAZARDOUS_CODE is null) AND (X_DANGEROUS_GOODS_HAZARDOUS_CO is null)))
264 AND ((recinfo.DANGEROUS_GOODS_PACK_GROUP = X_DANGEROUS_GOODS_PACK_GROUP)
265 OR ((recinfo.DANGEROUS_GOODS_PACK_GROUP is null) AND (X_DANGEROUS_GOODS_PACK_GROUP is null)))
266 AND ((recinfo.DANGEROUS_GOODS_REG_CODE = X_DANGEROUS_GOODS_REG_CODE)
267 OR ((recinfo.DANGEROUS_GOODS_REG_CODE is null) AND (X_DANGEROUS_GOODS_REG_CODE is null)))
268 AND ((recinfo.DANGEROUS_GOODS_SHIPPING_NAME = X_DANGEROUS_GOODS_SHIPPING_NAM)
269 OR ((recinfo.DANGEROUS_GOODS_SHIPPING_NAME is null) AND (X_DANGEROUS_GOODS_SHIPPING_NAM is null)))
270 AND ((recinfo.UNITED_NATIONS_DANG_GOODS_NO = X_UNITED_NATIONS_DANG_GOODS_NO)
271 OR ((recinfo.UNITED_NATIONS_DANG_GOODS_NO is null) AND (X_UNITED_NATIONS_DANG_GOODS_NO is null)))
272 AND ((recinfo.FLASH_POINT_TEMP = X_FLASH_POINT_TEMP)
273 OR ((recinfo.FLASH_POINT_TEMP is null) AND (X_FLASH_POINT_TEMP is null)))
274 AND ((recinfo.UOM_FLASH_POINT_TEMP = X_UOM_FLASH_POINT_TEMP)
275 OR ((recinfo.UOM_FLASH_POINT_TEMP is null) AND (X_UOM_FLASH_POINT_TEMP is null)))
276 AND ((recinfo.COUNTRY_OF_ORIGIN = X_COUNTRY_OF_ORIGIN)
277 OR ((recinfo.COUNTRY_OF_ORIGIN is null) AND (X_COUNTRY_OF_ORIGIN is null)))
278 AND ((recinfo.HARMONIZED_TARIFF_SYS_ID_CODE = X_HARMONIZED_TARIFF_SYS_ID_COD)
279 OR ((recinfo.HARMONIZED_TARIFF_SYS_ID_CODE is null) AND (X_HARMONIZED_TARIFF_SYS_ID_COD is null)))
280 AND ((recinfo.DELIVERY_METHOD_INDICATOR = X_DELIVERY_METHOD_INDICATOR)
281 OR ((recinfo.DELIVERY_METHOD_INDICATOR is null) AND (X_DELIVERY_METHOD_INDICATOR is null)))
282 AND ((recinfo.ATTR_GROUP_ID = X_ATTR_GROUP_ID)
283 OR ((recinfo.ATTR_GROUP_ID is null) AND (X_ATTR_GROUP_ID is null)))
284 AND ((recinfo.MANUFACTURER_GLN = X_MANUFACTURER_GLN)
285 OR ((recinfo.MANUFACTURER_GLN is null) AND (X_MANUFACTURER_GLN is null)))
286 AND ((recinfo.MANUFACTURER_ID = X_MANUFACTURER_ID)
287 OR ((recinfo.MANUFACTURER_ID is null) AND (X_MANUFACTURER_ID is null)))
288 AND ((recinfo.PARTY_RECEIVING_PRIVATE_DATA = X_PARTY_RECEIVING_PRIVATE_DATA)
289 OR ((recinfo.PARTY_RECEIVING_PRIVATE_DATA is null) AND (X_PARTY_RECEIVING_PRIVATE_DATA is null)))
290 AND ((recinfo.ITEM_CATALOG_GROUP_ID = X_ITEM_CATALOG_GROUP_ID)
291 OR ((recinfo.ITEM_CATALOG_GROUP_ID is null) AND (X_ITEM_CATALOG_GROUP_ID is null)))
292 AND ((recinfo.REVISION_ID = X_REVISION_ID)
293 OR ((recinfo.REVISION_ID is null) AND (X_REVISION_ID is null)))
294 ) then
295 null;
296 else
297 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
298 app_exception.raise_exception;
299 end if;
300
301 for tlinfo in c1 loop
302 if (tlinfo.BASELANG = 'Y') then
303 if ( ((tlinfo.HANDLING_INSTRUCTIONS_CODE = X_HANDLING_INSTRUCTIONS_CODE)
304 OR ((tlinfo.HANDLING_INSTRUCTIONS_CODE is null) AND (X_HANDLING_INSTRUCTIONS_CODE is null)))
305 AND ((tlinfo.DANGEROUS_GOODS_TECHNICAL_NAME = X_DANGEROUS_GOODS_TECHNICAL_NA)
306 OR ((tlinfo.DANGEROUS_GOODS_TECHNICAL_NAME is null) AND (X_DANGEROUS_GOODS_TECHNICAL_NA is null)))
307 ) then
308 null;
309 else
310 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
311 app_exception.raise_exception;
312 end if;
313 end if;
314 end loop;
315 return;
316 end LOCK_ROW;
317
318 procedure UPDATE_ROW (
319 X_EXTENSION_ID in NUMBER,
320 X_REQUEST_ID in NUMBER,
321 X_SIZE_CODE_LIST_AGENCY in VARCHAR2,
322 X_SIZE_CODE_VALUE in VARCHAR2,
323 X_INVENTORY_ITEM_ID in NUMBER,
324 X_ORGANIZATION_ID in NUMBER,
325 X_BAR_CODE_TYPE in VARCHAR2,
329 X_DANGEROUS_GOODS_MARGIN_NUMBE in VARCHAR2,
326 X_COLOR_CODE_LIST_AGENCY in VARCHAR2,
327 X_COLOR_CODE_VALUE in VARCHAR2,
328 X_CLASS_OF_DANGEROUS_CODE in VARCHAR2,
330 X_DANGEROUS_GOODS_HAZARDOUS_CO in VARCHAR2,
331 X_DANGEROUS_GOODS_PACK_GROUP in VARCHAR2,
332 X_DANGEROUS_GOODS_REG_CODE in VARCHAR2,
333 X_DANGEROUS_GOODS_SHIPPING_NAM in VARCHAR2,
334 X_UNITED_NATIONS_DANG_GOODS_NO in NUMBER,
335 X_FLASH_POINT_TEMP in NUMBER,
336 X_UOM_FLASH_POINT_TEMP in VARCHAR2,
337 X_COUNTRY_OF_ORIGIN in VARCHAR2,
338 X_HARMONIZED_TARIFF_SYS_ID_COD in NUMBER,
339 X_DELIVERY_METHOD_INDICATOR in VARCHAR2,
340 X_ATTR_GROUP_ID in NUMBER,
341 X_MANUFACTURER_GLN in VARCHAR2,
342 X_MANUFACTURER_ID in NUMBER,
343 X_PARTY_RECEIVING_PRIVATE_DATA in VARCHAR2,
344 X_ITEM_CATALOG_GROUP_ID in NUMBER,
345 X_REVISION_ID in NUMBER,
346 X_HANDLING_INSTRUCTIONS_CODE in VARCHAR2,
347 X_DANGEROUS_GOODS_TECHNICAL_NA in VARCHAR2,
348 X_LAST_UPDATE_DATE in DATE,
349 X_LAST_UPDATED_BY in NUMBER,
350 X_LAST_UPDATE_LOGIN in NUMBER
351 ) is
352 begin
353 update EGO_ITM_GTN_MUL_ATTRS_B set
354 REQUEST_ID = X_REQUEST_ID,
355 SIZE_CODE_LIST_AGENCY = X_SIZE_CODE_LIST_AGENCY,
356 SIZE_CODE_VALUE = X_SIZE_CODE_VALUE,
357 INVENTORY_ITEM_ID = X_INVENTORY_ITEM_ID,
358 ORGANIZATION_ID = X_ORGANIZATION_ID,
359 BAR_CODE_TYPE = X_BAR_CODE_TYPE,
360 COLOR_CODE_LIST_AGENCY = X_COLOR_CODE_LIST_AGENCY,
361 COLOR_CODE_VALUE = X_COLOR_CODE_VALUE,
362 CLASS_OF_DANGEROUS_CODE = X_CLASS_OF_DANGEROUS_CODE,
363 DANGEROUS_GOODS_MARGIN_NUMBER = X_DANGEROUS_GOODS_MARGIN_NUMBE,
364 DANGEROUS_GOODS_HAZARDOUS_CODE = X_DANGEROUS_GOODS_HAZARDOUS_CO,
365 DANGEROUS_GOODS_PACK_GROUP = X_DANGEROUS_GOODS_PACK_GROUP,
366 DANGEROUS_GOODS_REG_CODE = X_DANGEROUS_GOODS_REG_CODE,
367 DANGEROUS_GOODS_SHIPPING_NAME = X_DANGEROUS_GOODS_SHIPPING_NAM,
368 UNITED_NATIONS_DANG_GOODS_NO = X_UNITED_NATIONS_DANG_GOODS_NO,
369 FLASH_POINT_TEMP = X_FLASH_POINT_TEMP,
370 UOM_FLASH_POINT_TEMP = X_UOM_FLASH_POINT_TEMP,
371 COUNTRY_OF_ORIGIN = X_COUNTRY_OF_ORIGIN,
372 HARMONIZED_TARIFF_SYS_ID_CODE = X_HARMONIZED_TARIFF_SYS_ID_COD,
373 DELIVERY_METHOD_INDICATOR = X_DELIVERY_METHOD_INDICATOR,
374 ATTR_GROUP_ID = X_ATTR_GROUP_ID,
375 MANUFACTURER_GLN = X_MANUFACTURER_GLN,
376 MANUFACTURER_ID = X_MANUFACTURER_ID,
377 PARTY_RECEIVING_PRIVATE_DATA = X_PARTY_RECEIVING_PRIVATE_DATA,
378 ITEM_CATALOG_GROUP_ID = X_ITEM_CATALOG_GROUP_ID,
379 REVISION_ID = X_REVISION_ID,
380 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
381 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
382 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
383 where EXTENSION_ID = X_EXTENSION_ID;
384
385 if (sql%notfound) then
386 raise no_data_found;
387 end if;
388
389 update EGO_ITM_GTN_MUL_ATTRS_TL set
390 HANDLING_INSTRUCTIONS_CODE = X_HANDLING_INSTRUCTIONS_CODE,
391 DANGEROUS_GOODS_TECHNICAL_NAME = X_DANGEROUS_GOODS_TECHNICAL_NA,
392 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
393 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
394 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
395 SOURCE_LANG = userenv('LANG')
396 where EXTENSION_ID = X_EXTENSION_ID
397 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
398
399 if (sql%notfound) then
400 raise no_data_found;
401 end if;
402 end UPDATE_ROW;
403
404 procedure DELETE_ROW (
405 X_EXTENSION_ID in NUMBER
406 ) is
407 begin
408 delete from EGO_ITM_GTN_MUL_ATTRS_TL
409 where EXTENSION_ID = X_EXTENSION_ID;
410
411 if (sql%notfound) then
412 raise no_data_found;
413 end if;
414
415 delete from EGO_ITM_GTN_MUL_ATTRS_B
416 where EXTENSION_ID = X_EXTENSION_ID;
417
418 if (sql%notfound) then
419 raise no_data_found;
420 end if;
421 end DELETE_ROW;
422
423 procedure ADD_LANGUAGE
424 is
425 begin
426 delete from EGO_ITM_GTN_MUL_ATTRS_TL T
427 where not exists
428 (select NULL
429 from EGO_ITM_GTN_MUL_ATTRS_B B
430 where B.EXTENSION_ID = T.EXTENSION_ID
431 );
432
433 update EGO_ITM_GTN_MUL_ATTRS_TL T set (
434 HANDLING_INSTRUCTIONS_CODE,
435 DANGEROUS_GOODS_TECHNICAL_NAME
436 ) = (select
437 B.HANDLING_INSTRUCTIONS_CODE,
438 B.DANGEROUS_GOODS_TECHNICAL_NAME
439 from EGO_ITM_GTN_MUL_ATTRS_TL B
440 where B.EXTENSION_ID = T.EXTENSION_ID
441 and B.LANGUAGE = T.SOURCE_LANG)
442 where (
443 T.EXTENSION_ID,
444 T.LANGUAGE
445 ) in (select
446 SUBT.EXTENSION_ID,
447 SUBT.LANGUAGE
448 from EGO_ITM_GTN_MUL_ATTRS_TL SUBB, EGO_ITM_GTN_MUL_ATTRS_TL SUBT
449 where SUBB.EXTENSION_ID = SUBT.EXTENSION_ID
450 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
451 and (SUBB.HANDLING_INSTRUCTIONS_CODE <> SUBT.HANDLING_INSTRUCTIONS_CODE
452 or (SUBB.HANDLING_INSTRUCTIONS_CODE is null and SUBT.HANDLING_INSTRUCTIONS_CODE is not null)
453 or (SUBB.HANDLING_INSTRUCTIONS_CODE is not null and SUBT.HANDLING_INSTRUCTIONS_CODE is null)
454 or SUBB.DANGEROUS_GOODS_TECHNICAL_NAME <> SUBT.DANGEROUS_GOODS_TECHNICAL_NAME
455 or (SUBB.DANGEROUS_GOODS_TECHNICAL_NAME is null and SUBT.DANGEROUS_GOODS_TECHNICAL_NAME is not null)
456 or (SUBB.DANGEROUS_GOODS_TECHNICAL_NAME is not null and SUBT.DANGEROUS_GOODS_TECHNICAL_NAME is null)
457 ));
458
459 insert into EGO_ITM_GTN_MUL_ATTRS_TL (
460 REQUEST_ID,
461 EXTENSION_ID,
462 INVENTORY_ITEM_ID,
463 ORGANIZATION_ID,
464 ATTR_GROUP_ID,
465 CREATED_BY,
466 CREATION_DATE,
467 LAST_UPDATED_BY,
468 LAST_UPDATE_DATE,
469 LAST_UPDATE_LOGIN,
470 ITEM_CATALOG_GROUP_ID,
471 REVISION_ID,
472 HANDLING_INSTRUCTIONS_CODE,
473 DANGEROUS_GOODS_TECHNICAL_NAME,
474 LANGUAGE,
475 SOURCE_LANG
476 ) select /*+ ORDERED */
477 B.REQUEST_ID,
478 B.EXTENSION_ID,
479 B.INVENTORY_ITEM_ID,
480 B.ORGANIZATION_ID,
481 B.ATTR_GROUP_ID,
482 B.CREATED_BY,
483 B.CREATION_DATE,
484 B.LAST_UPDATED_BY,
485 B.LAST_UPDATE_DATE,
486 B.LAST_UPDATE_LOGIN,
487 B.ITEM_CATALOG_GROUP_ID,
488 B.REVISION_ID,
489 B.HANDLING_INSTRUCTIONS_CODE,
490 B.DANGEROUS_GOODS_TECHNICAL_NAME,
491 L.LANGUAGE_CODE,
492 B.SOURCE_LANG
493 from EGO_ITM_GTN_MUL_ATTRS_TL B, FND_LANGUAGES L
494 where L.INSTALLED_FLAG in ('I', 'B')
495 and B.LANGUAGE = userenv('LANG')
496 and not exists
497 (select NULL
498 from EGO_ITM_GTN_MUL_ATTRS_TL T
499 where T.EXTENSION_ID = B.EXTENSION_ID
500 and T.LANGUAGE = L.LANGUAGE_CODE);
501 end ADD_LANGUAGE;
502
503 end EGO_ITM_GTN_MUL_ATTRS_PKG;
[Home] [Help]