1 package body EGO_ITEM_GTN_ATTRS_PKG as
2 /* $Header: EGOVGTIB.pls 120.0 2005/10/14 04:09 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_DELIVERY_TO_MRKT_TEMP_MIN in NUMBER,
8 X_UOM_DELIVERY_TO_MRKT_TEMP_MI in VARCHAR2,
9 X_SUB_BRAND in VARCHAR2,
10 X_UOM_DEL_TO_DIST_CNTR_TEMP_MI in VARCHAR2,
11 X_DELIVERY_TO_MRKT_TEMP_MAX in NUMBER,
12 X_UOM_DELIVERY_TO_MRKT_TEMP_MA in VARCHAR2,
13 X_INVENTORY_ITEM_ID in NUMBER,
14 X_ORGANIZATION_ID in NUMBER,
15 X_ITEM_CATALOG_GROUP_ID in NUMBER,
16 X_REVISION_ID in NUMBER,
17 X_IS_TRADE_ITEM_A_CONSUMER_UNI in VARCHAR2,
18 X_IS_TRADE_ITEM_INFO_PRIVATE in VARCHAR2,
19 X_GROSS_WEIGHT in NUMBER,
20 X_UOM_GROSS_WEIGHT in VARCHAR2,
21 X_EFFECTIVE_DATE in DATE,
22 X_CANCELED_DATE in DATE,
23 X_DISCONTINUED_DATE in DATE,
24 X_END_AVAILABILITY_DATE_TIME in DATE,
25 X_START_AVAILABILITY_DATE_TIME in DATE,
26 X_BRAND_NAME in VARCHAR2,
27 X_IS_TRADE_ITEM_A_BASE_UNIT in VARCHAR2,
28 X_IS_TRADE_ITEM_A_VARIABLE_UNI in VARCHAR2,
29 X_IS_PACK_MARKED_WITH_EXP_DATE in VARCHAR2,
30 X_IS_PACK_MARKED_WITH_GREEN_DO in VARCHAR2,
31 X_IS_PACK_MARKED_WITH_INGRED in VARCHAR2,
32 X_IS_PACKAGE_MARKED_AS_REC in VARCHAR2,
33 X_IS_PACKAGE_MARKED_RET in VARCHAR2,
34 X_STACKING_FACTOR in NUMBER,
35 X_STACKING_WEIGHT_MAXIMUM in NUMBER,
36 X_UOM_STACKING_WEIGHT_MAXIMUM in VARCHAR2,
37 X_ORDERING_LEAD_TIME in NUMBER,
38 X_UOM_ORDERING_LEAD_TIME in VARCHAR2,
39 X_ORDER_QUANTITY_MAX in NUMBER,
40 X_ORDER_QUANTITY_MIN in NUMBER,
41 X_ORDER_QUANTITY_MULTIPLE in NUMBER,
42 X_ORDER_SIZING_FACTOR in NUMBER,
43 X_EFFECTIVE_START_DATE in DATE,
44 X_CATALOG_PRICE in NUMBER,
45 X_EFFECTIVE_END_DATE in DATE,
46 X_SUGGESTED_RETAIL_PRICE in NUMBER,
47 X_MATERIAL_SAFETY_DATA_SHEET_N in VARCHAR2,
48 X_HAS_BATCH_NUMBER in VARCHAR2,
49 X_IS_NON_SOLD_TRADE_RET_FLAG in VARCHAR2,
50 X_IS_TRADE_ITEM_MAR_REC_FLAG in VARCHAR2,
51 X_DIAMETER in NUMBER,
52 X_UOM_DIAMETER in VARCHAR2,
53 X_DRAINED_WEIGHT in NUMBER,
54 X_UOM_DRAINED_WEIGHT in VARCHAR2,
55 X_GENERIC_INGREDIENT in VARCHAR2,
56 X_GENERIC_INGREDIENT_STRGTH in NUMBER,
57 X_UOM_GENERIC_INGREDIENT_STRGT in VARCHAR2,
58 X_INGREDIENT_STRENGTH in VARCHAR2,
59 X_IS_NET_CONTENT_DEC_FLAG in VARCHAR2,
60 X_NET_CONTENT in NUMBER,
61 X_UOM_NET_CONTENT in VARCHAR2,
62 X_PEG_HORIZONTAL in NUMBER,
63 X_UOM_PEG_HORIZONTAL in VARCHAR2,
64 X_PEG_VERTICAL in NUMBER,
65 X_UOM_PEG_VERTICAL in VARCHAR2,
66 X_CONSUMER_AVAIL_DATE_TIME in DATE,
67 X_DEL_TO_DIST_CNTR_TEMP_MAX in NUMBER,
68 X_UOM_DEL_TO_DIST_CNTR_TEMP_MA in VARCHAR2,
69 X_DEL_TO_DIST_CNTR_TEMP_MIN in NUMBER,
70 X_TRADE_ITEM_DESCRIPTOR in VARCHAR2,
71 X_EANUCC_CODE in VARCHAR2,
72 X_EANUCC_TYPE in VARCHAR2,
73 X_RETAIL_PRICE_ON_TRADE_ITEM in NUMBER,
74 X_QUANTITY_OF_COMP_LAY_ITEM in NUMBER,
75 X_QUANITY_OF_ITEM_IN_LAYER in NUMBER,
76 X_QUANTITY_OF_ITEM_INNER_PACK in NUMBER,
77 X_TARGET_MARKET_DESC in VARCHAR2,
78 X_QUANTITY_OF_INNER_PACK in NUMBER,
79 X_BRAND_OWNER_GLN in VARCHAR2,
80 X_BRAND_OWNER_NAME in VARCHAR2,
81 X_STORAGE_HANDLING_TEMP_MAX in NUMBER,
82 X_UOM_STORAGE_HANDLING_TEMP_MA in VARCHAR2,
83 X_STORAGE_HANDLING_TEMP_MIN in NUMBER,
84 X_UOM_STORAGE_HANDLING_TEMP_MI in VARCHAR2,
85 X_TRADE_ITEM_COUPON in NUMBER,
86 X_DEGREE_OF_ORIGINAL_WORT in VARCHAR2,
87 X_FAT_PERCENT_IN_DRY_MATTER in NUMBER,
88 X_PERCENT_OF_ALCOHOL_BY_VOL in NUMBER,
89 X_ISBN_NUMBER in VARCHAR2,
90 X_ISSN_NUMBER in VARCHAR2,
91 X_IS_INGREDIENT_IRRADIATED in VARCHAR2,
92 X_IS_RAW_MATERIAL_IRRADIATED in VARCHAR2,
93 X_IS_TRADE_ITEM_GENETICALLY_MO in VARCHAR2,
94 X_IS_TRADE_ITEM_IRRADIATED in VARCHAR2,
95 X_PUBLICATION_STATUS in NUMBER,
96 X_TOP_GTIN in VARCHAR2,
97 X_SECURITY_TAG_LOCATION in VARCHAR2,
98 X_URL_FOR_WARRANTY in VARCHAR2,
99 X_NESTING_INCREMENT in NUMBER,
100 X_UOM_NESTING_INCREMENT in VARCHAR2,
101 X_IS_TRADE_ITEM_RECALLED in VARCHAR2,
102 X_MODEL_NUMBER in VARCHAR2,
103 X_PIECES_PER_TRADE_ITEM in NUMBER,
104 X_UOM_PIECES_PER_TRADE_ITEM in VARCHAR2,
105 X_DEPT_OF_TRNSPRT_DANG_GOODS_N in VARCHAR2,
106 X_RETURN_GOODS_POLICY in VARCHAR2,
107 X_IS_OUT_OF_BOX_PROVIDED in VARCHAR2,
108 X_REGISTRATION_UPDATE_DATE in DATE,
109 X_TP_NEUTRAL_UPDATE_DATE in DATE,
110 X_IS_BARCODE_SYMBOLOGY_DERIVAB in VARCHAR2,
111 X_INVOICE_NAME in VARCHAR2,
112 X_DESCRIPTIVE_SIZE in VARCHAR2,
113 X_FUNCTIONAL_NAME in VARCHAR2,
114 X_TRADE_ITEM_FORM_DESCRIPTION in VARCHAR2,
115 X_WARRANTY_DESCRIPTION in VARCHAR2,
116 X_TRADE_ITEM_FINISH_DESCRIPTIO in VARCHAR2,
117 X_DESCRIPTION_SHORT in VARCHAR2,
118 X_CREATION_DATE in DATE,
119 X_CREATED_BY in NUMBER,
120 X_LAST_UPDATE_DATE in DATE,
121 X_LAST_UPDATED_BY in NUMBER,
122 X_LAST_UPDATE_LOGIN in NUMBER
123 ) is
124 cursor C is select ROWID from EGO_ITEM_GTN_ATTRS_B
125 where EXTENSION_ID = X_EXTENSION_ID
126 ;
127 begin
128 insert into EGO_ITEM_GTN_ATTRS_B (
129 REQUEST_ID,
130 DELIVERY_TO_MRKT_TEMP_MIN,
131 UOM_DELIVERY_TO_MRKT_TEMP_MIN,
132 SUB_BRAND,
133 UOM_DEL_TO_DIST_CNTR_TEMP_MIN,
134 DELIVERY_TO_MRKT_TEMP_MAX,
135 UOM_DELIVERY_TO_MRKT_TEMP_MAX,
136 EXTENSION_ID,
137 INVENTORY_ITEM_ID,
138 ORGANIZATION_ID,
139 ITEM_CATALOG_GROUP_ID,
140 REVISION_ID,
141 IS_TRADE_ITEM_A_CONSUMER_UNIT,
142 IS_TRADE_ITEM_INFO_PRIVATE,
143 GROSS_WEIGHT,
144 UOM_GROSS_WEIGHT,
145 EFFECTIVE_DATE,
146 CANCELED_DATE,
147 DISCONTINUED_DATE,
148 END_AVAILABILITY_DATE_TIME,
149 START_AVAILABILITY_DATE_TIME,
150 BRAND_NAME,
151 IS_TRADE_ITEM_A_BASE_UNIT,
152 IS_TRADE_ITEM_A_VARIABLE_UNIT,
153 IS_PACK_MARKED_WITH_EXP_DATE,
154 IS_PACK_MARKED_WITH_GREEN_DOT,
155 IS_PACK_MARKED_WITH_INGRED,
156 IS_PACKAGE_MARKED_AS_REC,
157 IS_PACKAGE_MARKED_RET,
158 STACKING_FACTOR,
159 STACKING_WEIGHT_MAXIMUM,
160 UOM_STACKING_WEIGHT_MAXIMUM,
161 ORDERING_LEAD_TIME,
162 UOM_ORDERING_LEAD_TIME,
163 ORDER_QUANTITY_MAX,
164 ORDER_QUANTITY_MIN,
165 ORDER_QUANTITY_MULTIPLE,
166 ORDER_SIZING_FACTOR,
167 EFFECTIVE_START_DATE,
168 CATALOG_PRICE,
169 EFFECTIVE_END_DATE,
170 SUGGESTED_RETAIL_PRICE,
171 MATERIAL_SAFETY_DATA_SHEET_NO,
172 HAS_BATCH_NUMBER,
173 IS_NON_SOLD_TRADE_RET_FLAG,
174 IS_TRADE_ITEM_MAR_REC_FLAG,
175 DIAMETER,
176 UOM_DIAMETER,
177 DRAINED_WEIGHT,
178 UOM_DRAINED_WEIGHT,
179 GENERIC_INGREDIENT,
180 GENERIC_INGREDIENT_STRGTH,
181 UOM_GENERIC_INGREDIENT_STRGTH,
182 INGREDIENT_STRENGTH,
183 IS_NET_CONTENT_DEC_FLAG,
184 NET_CONTENT,
185 UOM_NET_CONTENT,
186 PEG_HORIZONTAL,
187 UOM_PEG_HORIZONTAL,
188 PEG_VERTICAL,
189 UOM_PEG_VERTICAL,
190 CONSUMER_AVAIL_DATE_TIME,
191 DEL_TO_DIST_CNTR_TEMP_MAX,
192 UOM_DEL_TO_DIST_CNTR_TEMP_MAX,
193 DEL_TO_DIST_CNTR_TEMP_MIN,
194 TRADE_ITEM_DESCRIPTOR,
195 EANUCC_CODE,
196 EANUCC_TYPE,
197 RETAIL_PRICE_ON_TRADE_ITEM,
198 QUANTITY_OF_COMP_LAY_ITEM,
199 QUANITY_OF_ITEM_IN_LAYER,
200 QUANTITY_OF_ITEM_INNER_PACK,
201 TARGET_MARKET_DESC,
202 QUANTITY_OF_INNER_PACK,
203 BRAND_OWNER_GLN,
204 BRAND_OWNER_NAME,
205 STORAGE_HANDLING_TEMP_MAX,
206 UOM_STORAGE_HANDLING_TEMP_MAX,
207 STORAGE_HANDLING_TEMP_MIN,
208 UOM_STORAGE_HANDLING_TEMP_MIN,
209 TRADE_ITEM_COUPON,
210 DEGREE_OF_ORIGINAL_WORT,
211 FAT_PERCENT_IN_DRY_MATTER,
212 PERCENT_OF_ALCOHOL_BY_VOL,
213 ISBN_NUMBER,
214 ISSN_NUMBER,
215 IS_INGREDIENT_IRRADIATED,
216 IS_RAW_MATERIAL_IRRADIATED,
217 IS_TRADE_ITEM_GENETICALLY_MOD,
218 IS_TRADE_ITEM_IRRADIATED,
219 PUBLICATION_STATUS,
220 TOP_GTIN,
221 SECURITY_TAG_LOCATION,
222 URL_FOR_WARRANTY,
223 NESTING_INCREMENT,
224 UOM_NESTING_INCREMENT,
225 IS_TRADE_ITEM_RECALLED,
226 MODEL_NUMBER,
227 PIECES_PER_TRADE_ITEM,
228 UOM_PIECES_PER_TRADE_ITEM,
229 DEPT_OF_TRNSPRT_DANG_GOODS_NUM,
230 RETURN_GOODS_POLICY,
231 IS_OUT_OF_BOX_PROVIDED,
232 REGISTRATION_UPDATE_DATE,
233 TP_NEUTRAL_UPDATE_DATE,
234 IS_BARCODE_SYMBOLOGY_DERIVABLE,
235 CREATION_DATE,
236 CREATED_BY,
237 LAST_UPDATE_DATE,
238 LAST_UPDATED_BY,
239 LAST_UPDATE_LOGIN
240 ) values (
241 X_REQUEST_ID,
242 X_DELIVERY_TO_MRKT_TEMP_MIN,
243 X_UOM_DELIVERY_TO_MRKT_TEMP_MI,
244 X_SUB_BRAND,
245 X_UOM_DEL_TO_DIST_CNTR_TEMP_MI,
246 X_DELIVERY_TO_MRKT_TEMP_MAX,
247 X_UOM_DELIVERY_TO_MRKT_TEMP_MA,
248 X_EXTENSION_ID,
249 X_INVENTORY_ITEM_ID,
250 X_ORGANIZATION_ID,
251 X_ITEM_CATALOG_GROUP_ID,
252 X_REVISION_ID,
253 X_IS_TRADE_ITEM_A_CONSUMER_UNI,
254 X_IS_TRADE_ITEM_INFO_PRIVATE,
255 X_GROSS_WEIGHT,
256 X_UOM_GROSS_WEIGHT,
257 X_EFFECTIVE_DATE,
258 X_CANCELED_DATE,
259 X_DISCONTINUED_DATE,
260 X_END_AVAILABILITY_DATE_TIME,
261 X_START_AVAILABILITY_DATE_TIME,
262 X_BRAND_NAME,
263 X_IS_TRADE_ITEM_A_BASE_UNIT,
264 X_IS_TRADE_ITEM_A_VARIABLE_UNI,
265 X_IS_PACK_MARKED_WITH_EXP_DATE,
266 X_IS_PACK_MARKED_WITH_GREEN_DO,
267 X_IS_PACK_MARKED_WITH_INGRED,
268 X_IS_PACKAGE_MARKED_AS_REC,
269 X_IS_PACKAGE_MARKED_RET,
270 X_STACKING_FACTOR,
271 X_STACKING_WEIGHT_MAXIMUM,
272 X_UOM_STACKING_WEIGHT_MAXIMUM,
273 X_ORDERING_LEAD_TIME,
274 X_UOM_ORDERING_LEAD_TIME,
275 X_ORDER_QUANTITY_MAX,
276 X_ORDER_QUANTITY_MIN,
277 X_ORDER_QUANTITY_MULTIPLE,
278 X_ORDER_SIZING_FACTOR,
279 X_EFFECTIVE_START_DATE,
280 X_CATALOG_PRICE,
281 X_EFFECTIVE_END_DATE,
282 X_SUGGESTED_RETAIL_PRICE,
283 X_MATERIAL_SAFETY_DATA_SHEET_N,
284 X_HAS_BATCH_NUMBER,
285 X_IS_NON_SOLD_TRADE_RET_FLAG,
286 X_IS_TRADE_ITEM_MAR_REC_FLAG,
287 X_DIAMETER,
288 X_UOM_DIAMETER,
289 X_DRAINED_WEIGHT,
290 X_UOM_DRAINED_WEIGHT,
291 X_GENERIC_INGREDIENT,
292 X_GENERIC_INGREDIENT_STRGTH,
293 X_UOM_GENERIC_INGREDIENT_STRGT,
294 X_INGREDIENT_STRENGTH,
295 X_IS_NET_CONTENT_DEC_FLAG,
296 X_NET_CONTENT,
297 X_UOM_NET_CONTENT,
298 X_PEG_HORIZONTAL,
299 X_UOM_PEG_HORIZONTAL,
300 X_PEG_VERTICAL,
301 X_UOM_PEG_VERTICAL,
302 X_CONSUMER_AVAIL_DATE_TIME,
303 X_DEL_TO_DIST_CNTR_TEMP_MAX,
304 X_UOM_DEL_TO_DIST_CNTR_TEMP_MA,
305 X_DEL_TO_DIST_CNTR_TEMP_MIN,
306 X_TRADE_ITEM_DESCRIPTOR,
307 X_EANUCC_CODE,
308 X_EANUCC_TYPE,
309 X_RETAIL_PRICE_ON_TRADE_ITEM,
310 X_QUANTITY_OF_COMP_LAY_ITEM,
311 X_QUANITY_OF_ITEM_IN_LAYER,
312 X_QUANTITY_OF_ITEM_INNER_PACK,
313 X_TARGET_MARKET_DESC,
314 X_QUANTITY_OF_INNER_PACK,
315 X_BRAND_OWNER_GLN,
316 X_BRAND_OWNER_NAME,
317 X_STORAGE_HANDLING_TEMP_MAX,
318 X_UOM_STORAGE_HANDLING_TEMP_MA,
319 X_STORAGE_HANDLING_TEMP_MIN,
320 X_UOM_STORAGE_HANDLING_TEMP_MI,
321 X_TRADE_ITEM_COUPON,
322 X_DEGREE_OF_ORIGINAL_WORT,
323 X_FAT_PERCENT_IN_DRY_MATTER,
324 X_PERCENT_OF_ALCOHOL_BY_VOL,
325 X_ISBN_NUMBER,
326 X_ISSN_NUMBER,
327 X_IS_INGREDIENT_IRRADIATED,
328 X_IS_RAW_MATERIAL_IRRADIATED,
329 X_IS_TRADE_ITEM_GENETICALLY_MO,
330 X_IS_TRADE_ITEM_IRRADIATED,
331 X_PUBLICATION_STATUS,
332 X_TOP_GTIN,
333 X_SECURITY_TAG_LOCATION,
334 X_URL_FOR_WARRANTY,
335 X_NESTING_INCREMENT,
336 X_UOM_NESTING_INCREMENT,
337 X_IS_TRADE_ITEM_RECALLED,
338 X_MODEL_NUMBER,
339 X_PIECES_PER_TRADE_ITEM,
340 X_UOM_PIECES_PER_TRADE_ITEM,
341 X_DEPT_OF_TRNSPRT_DANG_GOODS_N,
342 X_RETURN_GOODS_POLICY,
343 X_IS_OUT_OF_BOX_PROVIDED,
344 X_REGISTRATION_UPDATE_DATE,
345 X_TP_NEUTRAL_UPDATE_DATE,
346 X_IS_BARCODE_SYMBOLOGY_DERIVAB,
347 X_CREATION_DATE,
348 X_CREATED_BY,
349 X_LAST_UPDATE_DATE,
350 X_LAST_UPDATED_BY,
351 X_LAST_UPDATE_LOGIN
352 );
353
354 insert into EGO_ITEM_GTN_ATTRS_TL (
355 REQUEST_ID,
356 EXTENSION_ID,
357 INVENTORY_ITEM_ID,
358 ORGANIZATION_ID,
359 CREATED_BY,
360 CREATION_DATE,
361 LAST_UPDATED_BY,
362 LAST_UPDATE_DATE,
363 LAST_UPDATE_LOGIN,
364 ITEM_CATALOG_GROUP_ID,
365 REVISION_ID,
366 INVOICE_NAME,
367 DESCRIPTIVE_SIZE,
368 FUNCTIONAL_NAME,
369 TRADE_ITEM_FORM_DESCRIPTION,
370 WARRANTY_DESCRIPTION,
371 TRADE_ITEM_FINISH_DESCRIPTION,
372 DESCRIPTION_SHORT,
373 LANGUAGE,
374 SOURCE_LANG
375 ) select
376 X_REQUEST_ID,
377 X_EXTENSION_ID,
378 X_INVENTORY_ITEM_ID,
379 X_ORGANIZATION_ID,
380 X_CREATED_BY,
381 X_CREATION_DATE,
382 X_LAST_UPDATED_BY,
383 X_LAST_UPDATE_DATE,
384 X_LAST_UPDATE_LOGIN,
385 X_ITEM_CATALOG_GROUP_ID,
386 X_REVISION_ID,
387 X_INVOICE_NAME,
388 X_DESCRIPTIVE_SIZE,
389 X_FUNCTIONAL_NAME,
390 X_TRADE_ITEM_FORM_DESCRIPTION,
391 X_WARRANTY_DESCRIPTION,
392 X_TRADE_ITEM_FINISH_DESCRIPTIO,
393 X_DESCRIPTION_SHORT,
394 L.LANGUAGE_CODE,
395 userenv('LANG')
396 from FND_LANGUAGES L
397 where L.INSTALLED_FLAG in ('I', 'B')
398 and not exists
399 (select NULL
400 from EGO_ITEM_GTN_ATTRS_TL T
401 where T.EXTENSION_ID = X_EXTENSION_ID
402 and T.LANGUAGE = L.LANGUAGE_CODE);
403
404 open c;
405 fetch c into X_ROWID;
406 if (c%notfound) then
407 close c;
408 raise no_data_found;
409 end if;
410 close c;
411
412 end INSERT_ROW;
413
414 procedure LOCK_ROW (
415 X_EXTENSION_ID in NUMBER,
416 X_REQUEST_ID in NUMBER,
417 X_DELIVERY_TO_MRKT_TEMP_MIN in NUMBER,
418 X_UOM_DELIVERY_TO_MRKT_TEMP_MI in VARCHAR2,
419 X_SUB_BRAND in VARCHAR2,
420 X_UOM_DEL_TO_DIST_CNTR_TEMP_MI in VARCHAR2,
421 X_DELIVERY_TO_MRKT_TEMP_MAX in NUMBER,
422 X_UOM_DELIVERY_TO_MRKT_TEMP_MA in VARCHAR2,
423 X_INVENTORY_ITEM_ID in NUMBER,
424 X_ORGANIZATION_ID in NUMBER,
425 X_ITEM_CATALOG_GROUP_ID in NUMBER,
426 X_REVISION_ID in NUMBER,
427 X_IS_TRADE_ITEM_A_CONSUMER_UNI in VARCHAR2,
428 X_IS_TRADE_ITEM_INFO_PRIVATE in VARCHAR2,
429 X_GROSS_WEIGHT in NUMBER,
430 X_UOM_GROSS_WEIGHT in VARCHAR2,
431 X_EFFECTIVE_DATE in DATE,
432 X_CANCELED_DATE in DATE,
433 X_DISCONTINUED_DATE in DATE,
434 X_END_AVAILABILITY_DATE_TIME in DATE,
435 X_START_AVAILABILITY_DATE_TIME in DATE,
436 X_BRAND_NAME in VARCHAR2,
440 X_IS_PACK_MARKED_WITH_GREEN_DO in VARCHAR2,
437 X_IS_TRADE_ITEM_A_BASE_UNIT in VARCHAR2,
438 X_IS_TRADE_ITEM_A_VARIABLE_UNI in VARCHAR2,
439 X_IS_PACK_MARKED_WITH_EXP_DATE in VARCHAR2,
441 X_IS_PACK_MARKED_WITH_INGRED in VARCHAR2,
442 X_IS_PACKAGE_MARKED_AS_REC in VARCHAR2,
443 X_IS_PACKAGE_MARKED_RET in VARCHAR2,
444 X_STACKING_FACTOR in NUMBER,
445 X_STACKING_WEIGHT_MAXIMUM in NUMBER,
446 X_UOM_STACKING_WEIGHT_MAXIMUM in VARCHAR2,
447 X_ORDERING_LEAD_TIME in NUMBER,
448 X_UOM_ORDERING_LEAD_TIME in VARCHAR2,
449 X_ORDER_QUANTITY_MAX in NUMBER,
450 X_ORDER_QUANTITY_MIN in NUMBER,
451 X_ORDER_QUANTITY_MULTIPLE in NUMBER,
452 X_ORDER_SIZING_FACTOR in NUMBER,
453 X_EFFECTIVE_START_DATE in DATE,
454 X_CATALOG_PRICE in NUMBER,
455 X_EFFECTIVE_END_DATE in DATE,
456 X_SUGGESTED_RETAIL_PRICE in NUMBER,
457 X_MATERIAL_SAFETY_DATA_SHEET_N in VARCHAR2,
458 X_HAS_BATCH_NUMBER in VARCHAR2,
459 X_IS_NON_SOLD_TRADE_RET_FLAG in VARCHAR2,
460 X_IS_TRADE_ITEM_MAR_REC_FLAG in VARCHAR2,
461 X_DIAMETER in NUMBER,
462 X_UOM_DIAMETER in VARCHAR2,
463 X_DRAINED_WEIGHT in NUMBER,
464 X_UOM_DRAINED_WEIGHT in VARCHAR2,
465 X_GENERIC_INGREDIENT in VARCHAR2,
466 X_GENERIC_INGREDIENT_STRGTH in NUMBER,
467 X_UOM_GENERIC_INGREDIENT_STRGT in VARCHAR2,
468 X_INGREDIENT_STRENGTH in VARCHAR2,
469 X_IS_NET_CONTENT_DEC_FLAG in VARCHAR2,
470 X_NET_CONTENT in NUMBER,
471 X_UOM_NET_CONTENT in VARCHAR2,
472 X_PEG_HORIZONTAL in NUMBER,
473 X_UOM_PEG_HORIZONTAL in VARCHAR2,
474 X_PEG_VERTICAL in NUMBER,
475 X_UOM_PEG_VERTICAL in VARCHAR2,
476 X_CONSUMER_AVAIL_DATE_TIME in DATE,
477 X_DEL_TO_DIST_CNTR_TEMP_MAX in NUMBER,
478 X_UOM_DEL_TO_DIST_CNTR_TEMP_MA in VARCHAR2,
479 X_DEL_TO_DIST_CNTR_TEMP_MIN in NUMBER,
480 X_TRADE_ITEM_DESCRIPTOR in VARCHAR2,
481 X_EANUCC_CODE in VARCHAR2,
482 X_EANUCC_TYPE in VARCHAR2,
483 X_RETAIL_PRICE_ON_TRADE_ITEM in NUMBER,
484 X_QUANTITY_OF_COMP_LAY_ITEM in NUMBER,
485 X_QUANITY_OF_ITEM_IN_LAYER in NUMBER,
486 X_QUANTITY_OF_ITEM_INNER_PACK in NUMBER,
487 X_TARGET_MARKET_DESC in VARCHAR2,
488 X_QUANTITY_OF_INNER_PACK in NUMBER,
489 X_BRAND_OWNER_GLN in VARCHAR2,
490 X_BRAND_OWNER_NAME in VARCHAR2,
491 X_STORAGE_HANDLING_TEMP_MAX in NUMBER,
492 X_UOM_STORAGE_HANDLING_TEMP_MA in VARCHAR2,
493 X_STORAGE_HANDLING_TEMP_MIN in NUMBER,
494 X_UOM_STORAGE_HANDLING_TEMP_MI in VARCHAR2,
495 X_TRADE_ITEM_COUPON in NUMBER,
496 X_DEGREE_OF_ORIGINAL_WORT in VARCHAR2,
497 X_FAT_PERCENT_IN_DRY_MATTER in NUMBER,
498 X_PERCENT_OF_ALCOHOL_BY_VOL in NUMBER,
499 X_ISBN_NUMBER in VARCHAR2,
500 X_ISSN_NUMBER in VARCHAR2,
501 X_IS_INGREDIENT_IRRADIATED in VARCHAR2,
502 X_IS_RAW_MATERIAL_IRRADIATED in VARCHAR2,
503 X_IS_TRADE_ITEM_GENETICALLY_MO in VARCHAR2,
504 X_IS_TRADE_ITEM_IRRADIATED in VARCHAR2,
505 X_PUBLICATION_STATUS in NUMBER,
506 X_TOP_GTIN in VARCHAR2,
507 X_SECURITY_TAG_LOCATION in VARCHAR2,
508 X_URL_FOR_WARRANTY in VARCHAR2,
509 X_NESTING_INCREMENT in NUMBER,
510 X_UOM_NESTING_INCREMENT in VARCHAR2,
511 X_IS_TRADE_ITEM_RECALLED in VARCHAR2,
512 X_MODEL_NUMBER in VARCHAR2,
513 X_PIECES_PER_TRADE_ITEM in NUMBER,
514 X_UOM_PIECES_PER_TRADE_ITEM in VARCHAR2,
515 X_DEPT_OF_TRNSPRT_DANG_GOODS_N in VARCHAR2,
516 X_RETURN_GOODS_POLICY in VARCHAR2,
517 X_IS_OUT_OF_BOX_PROVIDED in VARCHAR2,
518 X_REGISTRATION_UPDATE_DATE in DATE,
519 X_TP_NEUTRAL_UPDATE_DATE in DATE,
520 X_IS_BARCODE_SYMBOLOGY_DERIVAB in VARCHAR2,
521 X_INVOICE_NAME in VARCHAR2,
522 X_DESCRIPTIVE_SIZE in VARCHAR2,
523 X_FUNCTIONAL_NAME in VARCHAR2,
524 X_TRADE_ITEM_FORM_DESCRIPTION in VARCHAR2,
525 X_WARRANTY_DESCRIPTION in VARCHAR2,
526 X_TRADE_ITEM_FINISH_DESCRIPTIO in VARCHAR2,
527 X_DESCRIPTION_SHORT in VARCHAR2
528 ) is
529 cursor c is select
530 REQUEST_ID,
531 DELIVERY_TO_MRKT_TEMP_MIN,
532 UOM_DELIVERY_TO_MRKT_TEMP_MIN,
533 SUB_BRAND,
534 UOM_DEL_TO_DIST_CNTR_TEMP_MIN,
535 DELIVERY_TO_MRKT_TEMP_MAX,
536 UOM_DELIVERY_TO_MRKT_TEMP_MAX,
537 INVENTORY_ITEM_ID,
538 ORGANIZATION_ID,
539 ITEM_CATALOG_GROUP_ID,
540 REVISION_ID,
541 IS_TRADE_ITEM_A_CONSUMER_UNIT,
542 IS_TRADE_ITEM_INFO_PRIVATE,
543 GROSS_WEIGHT,
544 UOM_GROSS_WEIGHT,
545 EFFECTIVE_DATE,
546 CANCELED_DATE,
547 DISCONTINUED_DATE,
548 END_AVAILABILITY_DATE_TIME,
549 START_AVAILABILITY_DATE_TIME,
550 BRAND_NAME,
551 IS_TRADE_ITEM_A_BASE_UNIT,
552 IS_TRADE_ITEM_A_VARIABLE_UNIT,
553 IS_PACK_MARKED_WITH_EXP_DATE,
554 IS_PACK_MARKED_WITH_GREEN_DOT,
555 IS_PACK_MARKED_WITH_INGRED,
556 IS_PACKAGE_MARKED_AS_REC,
557 IS_PACKAGE_MARKED_RET,
558 STACKING_FACTOR,
559 STACKING_WEIGHT_MAXIMUM,
560 UOM_STACKING_WEIGHT_MAXIMUM,
561 ORDERING_LEAD_TIME,
562 UOM_ORDERING_LEAD_TIME,
563 ORDER_QUANTITY_MAX,
564 ORDER_QUANTITY_MIN,
565 ORDER_QUANTITY_MULTIPLE,
566 ORDER_SIZING_FACTOR,
567 EFFECTIVE_START_DATE,
568 CATALOG_PRICE,
569 EFFECTIVE_END_DATE,
570 SUGGESTED_RETAIL_PRICE,
571 MATERIAL_SAFETY_DATA_SHEET_NO,
572 HAS_BATCH_NUMBER,
573 IS_NON_SOLD_TRADE_RET_FLAG,
574 IS_TRADE_ITEM_MAR_REC_FLAG,
575 DIAMETER,
576 UOM_DIAMETER,
577 DRAINED_WEIGHT,
578 UOM_DRAINED_WEIGHT,
579 GENERIC_INGREDIENT,
580 GENERIC_INGREDIENT_STRGTH,
581 UOM_GENERIC_INGREDIENT_STRGTH,
582 INGREDIENT_STRENGTH,
583 IS_NET_CONTENT_DEC_FLAG,
584 NET_CONTENT,
585 UOM_NET_CONTENT,
589 UOM_PEG_VERTICAL,
586 PEG_HORIZONTAL,
587 UOM_PEG_HORIZONTAL,
588 PEG_VERTICAL,
590 CONSUMER_AVAIL_DATE_TIME,
591 DEL_TO_DIST_CNTR_TEMP_MAX,
592 UOM_DEL_TO_DIST_CNTR_TEMP_MAX,
593 DEL_TO_DIST_CNTR_TEMP_MIN,
594 TRADE_ITEM_DESCRIPTOR,
595 EANUCC_CODE,
596 EANUCC_TYPE,
597 RETAIL_PRICE_ON_TRADE_ITEM,
598 QUANTITY_OF_COMP_LAY_ITEM,
599 QUANITY_OF_ITEM_IN_LAYER,
600 QUANTITY_OF_ITEM_INNER_PACK,
601 TARGET_MARKET_DESC,
602 QUANTITY_OF_INNER_PACK,
603 BRAND_OWNER_GLN,
604 BRAND_OWNER_NAME,
605 STORAGE_HANDLING_TEMP_MAX,
606 UOM_STORAGE_HANDLING_TEMP_MAX,
607 STORAGE_HANDLING_TEMP_MIN,
608 UOM_STORAGE_HANDLING_TEMP_MIN,
609 TRADE_ITEM_COUPON,
610 DEGREE_OF_ORIGINAL_WORT,
611 FAT_PERCENT_IN_DRY_MATTER,
612 PERCENT_OF_ALCOHOL_BY_VOL,
613 ISBN_NUMBER,
614 ISSN_NUMBER,
615 IS_INGREDIENT_IRRADIATED,
616 IS_RAW_MATERIAL_IRRADIATED,
617 IS_TRADE_ITEM_GENETICALLY_MOD,
618 IS_TRADE_ITEM_IRRADIATED,
619 PUBLICATION_STATUS,
620 TOP_GTIN,
621 SECURITY_TAG_LOCATION,
622 URL_FOR_WARRANTY,
623 NESTING_INCREMENT,
624 UOM_NESTING_INCREMENT,
625 IS_TRADE_ITEM_RECALLED,
626 MODEL_NUMBER,
627 PIECES_PER_TRADE_ITEM,
628 UOM_PIECES_PER_TRADE_ITEM,
629 DEPT_OF_TRNSPRT_DANG_GOODS_NUM,
630 RETURN_GOODS_POLICY,
631 IS_OUT_OF_BOX_PROVIDED,
632 REGISTRATION_UPDATE_DATE,
633 TP_NEUTRAL_UPDATE_DATE,
634 IS_BARCODE_SYMBOLOGY_DERIVABLE
635 from EGO_ITEM_GTN_ATTRS_B
636 where EXTENSION_ID = X_EXTENSION_ID
637 for update of EXTENSION_ID nowait;
638 recinfo c%rowtype;
639
640 cursor c1 is select
641 INVOICE_NAME,
642 DESCRIPTIVE_SIZE,
643 FUNCTIONAL_NAME,
644 TRADE_ITEM_FORM_DESCRIPTION,
645 WARRANTY_DESCRIPTION,
646 TRADE_ITEM_FINISH_DESCRIPTION,
647 DESCRIPTION_SHORT,
648 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
649 from EGO_ITEM_GTN_ATTRS_TL
650 where EXTENSION_ID = X_EXTENSION_ID
651 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
652 for update of EXTENSION_ID nowait;
653 begin
654 open c;
655 fetch c into recinfo;
656 if (c%notfound) then
657 close c;
658 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
659 app_exception.raise_exception;
660 end if;
661 close c;
662 if ( ((recinfo.REQUEST_ID = X_REQUEST_ID)
663 OR ((recinfo.REQUEST_ID is null) AND (X_REQUEST_ID is null)))
664 AND ((recinfo.DELIVERY_TO_MRKT_TEMP_MIN = X_DELIVERY_TO_MRKT_TEMP_MIN)
665 OR ((recinfo.DELIVERY_TO_MRKT_TEMP_MIN is null) AND (X_DELIVERY_TO_MRKT_TEMP_MIN is null)))
666 AND ((recinfo.UOM_DELIVERY_TO_MRKT_TEMP_MIN = X_UOM_DELIVERY_TO_MRKT_TEMP_MI)
667 OR ((recinfo.UOM_DELIVERY_TO_MRKT_TEMP_MIN is null) AND (X_UOM_DELIVERY_TO_MRKT_TEMP_MI is null)))
668 AND ((recinfo.SUB_BRAND = X_SUB_BRAND)
669 OR ((recinfo.SUB_BRAND is null) AND (X_SUB_BRAND is null)))
670 AND ((recinfo.UOM_DEL_TO_DIST_CNTR_TEMP_MIN = X_UOM_DEL_TO_DIST_CNTR_TEMP_MI)
671 OR ((recinfo.UOM_DEL_TO_DIST_CNTR_TEMP_MIN is null) AND (X_UOM_DEL_TO_DIST_CNTR_TEMP_MI is null)))
672 AND ((recinfo.DELIVERY_TO_MRKT_TEMP_MAX = X_DELIVERY_TO_MRKT_TEMP_MAX)
673 OR ((recinfo.DELIVERY_TO_MRKT_TEMP_MAX is null) AND (X_DELIVERY_TO_MRKT_TEMP_MAX is null)))
674 AND ((recinfo.UOM_DELIVERY_TO_MRKT_TEMP_MAX = X_UOM_DELIVERY_TO_MRKT_TEMP_MA)
675 OR ((recinfo.UOM_DELIVERY_TO_MRKT_TEMP_MAX is null) AND (X_UOM_DELIVERY_TO_MRKT_TEMP_MA is null)))
676 AND (recinfo.INVENTORY_ITEM_ID = X_INVENTORY_ITEM_ID)
677 AND (recinfo.ORGANIZATION_ID = X_ORGANIZATION_ID)
678 AND ((recinfo.ITEM_CATALOG_GROUP_ID = X_ITEM_CATALOG_GROUP_ID)
679 OR ((recinfo.ITEM_CATALOG_GROUP_ID is null) AND (X_ITEM_CATALOG_GROUP_ID is null)))
680 AND ((recinfo.REVISION_ID = X_REVISION_ID)
681 OR ((recinfo.REVISION_ID is null) AND (X_REVISION_ID is null)))
682 AND ((recinfo.IS_TRADE_ITEM_A_CONSUMER_UNIT = X_IS_TRADE_ITEM_A_CONSUMER_UNI)
683 OR ((recinfo.IS_TRADE_ITEM_A_CONSUMER_UNIT is null) AND (X_IS_TRADE_ITEM_A_CONSUMER_UNI is null)))
684 AND ((recinfo.IS_TRADE_ITEM_INFO_PRIVATE = X_IS_TRADE_ITEM_INFO_PRIVATE)
685 OR ((recinfo.IS_TRADE_ITEM_INFO_PRIVATE is null) AND (X_IS_TRADE_ITEM_INFO_PRIVATE is null)))
686 AND ((recinfo.GROSS_WEIGHT = X_GROSS_WEIGHT)
687 OR ((recinfo.GROSS_WEIGHT is null) AND (X_GROSS_WEIGHT is null)))
688 AND ((recinfo.UOM_GROSS_WEIGHT = X_UOM_GROSS_WEIGHT)
689 OR ((recinfo.UOM_GROSS_WEIGHT is null) AND (X_UOM_GROSS_WEIGHT is null)))
690 AND ((recinfo.EFFECTIVE_DATE = X_EFFECTIVE_DATE)
691 OR ((recinfo.EFFECTIVE_DATE is null) AND (X_EFFECTIVE_DATE is null)))
692 AND ((recinfo.CANCELED_DATE = X_CANCELED_DATE)
693 OR ((recinfo.CANCELED_DATE is null) AND (X_CANCELED_DATE is null)))
694 AND ((recinfo.DISCONTINUED_DATE = X_DISCONTINUED_DATE)
695 OR ((recinfo.DISCONTINUED_DATE is null) AND (X_DISCONTINUED_DATE is null)))
696 AND ((recinfo.END_AVAILABILITY_DATE_TIME = X_END_AVAILABILITY_DATE_TIME)
697 OR ((recinfo.END_AVAILABILITY_DATE_TIME is null) AND (X_END_AVAILABILITY_DATE_TIME is null)))
698 AND ((recinfo.START_AVAILABILITY_DATE_TIME = X_START_AVAILABILITY_DATE_TIME)
699 OR ((recinfo.START_AVAILABILITY_DATE_TIME is null) AND (X_START_AVAILABILITY_DATE_TIME is null)))
700 AND ((recinfo.BRAND_NAME = X_BRAND_NAME)
701 OR ((recinfo.BRAND_NAME is null) AND (X_BRAND_NAME is null)))
702 AND ((recinfo.IS_TRADE_ITEM_A_BASE_UNIT = X_IS_TRADE_ITEM_A_BASE_UNIT)
703 OR ((recinfo.IS_TRADE_ITEM_A_BASE_UNIT is null) AND (X_IS_TRADE_ITEM_A_BASE_UNIT is null)))
707 OR ((recinfo.IS_PACK_MARKED_WITH_EXP_DATE is null) AND (X_IS_PACK_MARKED_WITH_EXP_DATE is null)))
704 AND ((recinfo.IS_TRADE_ITEM_A_VARIABLE_UNIT = X_IS_TRADE_ITEM_A_VARIABLE_UNI)
705 OR ((recinfo.IS_TRADE_ITEM_A_VARIABLE_UNIT is null) AND (X_IS_TRADE_ITEM_A_VARIABLE_UNI is null)))
706 AND ((recinfo.IS_PACK_MARKED_WITH_EXP_DATE = X_IS_PACK_MARKED_WITH_EXP_DATE)
708 AND ((recinfo.IS_PACK_MARKED_WITH_GREEN_DOT = X_IS_PACK_MARKED_WITH_GREEN_DO)
709 OR ((recinfo.IS_PACK_MARKED_WITH_GREEN_DOT is null) AND (X_IS_PACK_MARKED_WITH_GREEN_DO is null)))
710 AND ((recinfo.IS_PACK_MARKED_WITH_INGRED = X_IS_PACK_MARKED_WITH_INGRED)
711 OR ((recinfo.IS_PACK_MARKED_WITH_INGRED is null) AND (X_IS_PACK_MARKED_WITH_INGRED is null)))
712 AND ((recinfo.IS_PACKAGE_MARKED_AS_REC = X_IS_PACKAGE_MARKED_AS_REC)
713 OR ((recinfo.IS_PACKAGE_MARKED_AS_REC is null) AND (X_IS_PACKAGE_MARKED_AS_REC is null)))
714 AND ((recinfo.IS_PACKAGE_MARKED_RET = X_IS_PACKAGE_MARKED_RET)
715 OR ((recinfo.IS_PACKAGE_MARKED_RET is null) AND (X_IS_PACKAGE_MARKED_RET is null)))
716 AND ((recinfo.STACKING_FACTOR = X_STACKING_FACTOR)
717 OR ((recinfo.STACKING_FACTOR is null) AND (X_STACKING_FACTOR is null)))
718 AND ((recinfo.STACKING_WEIGHT_MAXIMUM = X_STACKING_WEIGHT_MAXIMUM)
719 OR ((recinfo.STACKING_WEIGHT_MAXIMUM is null) AND (X_STACKING_WEIGHT_MAXIMUM is null)))
720 AND ((recinfo.UOM_STACKING_WEIGHT_MAXIMUM = X_UOM_STACKING_WEIGHT_MAXIMUM)
721 OR ((recinfo.UOM_STACKING_WEIGHT_MAXIMUM is null) AND (X_UOM_STACKING_WEIGHT_MAXIMUM is null)))
722 AND ((recinfo.ORDERING_LEAD_TIME = X_ORDERING_LEAD_TIME)
723 OR ((recinfo.ORDERING_LEAD_TIME is null) AND (X_ORDERING_LEAD_TIME is null)))
724 AND ((recinfo.UOM_ORDERING_LEAD_TIME = X_UOM_ORDERING_LEAD_TIME)
725 OR ((recinfo.UOM_ORDERING_LEAD_TIME is null) AND (X_UOM_ORDERING_LEAD_TIME is null)))
726 AND ((recinfo.ORDER_QUANTITY_MAX = X_ORDER_QUANTITY_MAX)
727 OR ((recinfo.ORDER_QUANTITY_MAX is null) AND (X_ORDER_QUANTITY_MAX is null)))
728 AND ((recinfo.ORDER_QUANTITY_MIN = X_ORDER_QUANTITY_MIN)
729 OR ((recinfo.ORDER_QUANTITY_MIN is null) AND (X_ORDER_QUANTITY_MIN is null)))
730 AND ((recinfo.ORDER_QUANTITY_MULTIPLE = X_ORDER_QUANTITY_MULTIPLE)
731 OR ((recinfo.ORDER_QUANTITY_MULTIPLE is null) AND (X_ORDER_QUANTITY_MULTIPLE is null)))
732 AND ((recinfo.ORDER_SIZING_FACTOR = X_ORDER_SIZING_FACTOR)
733 OR ((recinfo.ORDER_SIZING_FACTOR is null) AND (X_ORDER_SIZING_FACTOR is null)))
734 AND ((recinfo.EFFECTIVE_START_DATE = X_EFFECTIVE_START_DATE)
735 OR ((recinfo.EFFECTIVE_START_DATE is null) AND (X_EFFECTIVE_START_DATE is null)))
736 AND ((recinfo.CATALOG_PRICE = X_CATALOG_PRICE)
737 OR ((recinfo.CATALOG_PRICE is null) AND (X_CATALOG_PRICE is null)))
738 AND ((recinfo.EFFECTIVE_END_DATE = X_EFFECTIVE_END_DATE)
739 OR ((recinfo.EFFECTIVE_END_DATE is null) AND (X_EFFECTIVE_END_DATE is null)))
740 AND ((recinfo.SUGGESTED_RETAIL_PRICE = X_SUGGESTED_RETAIL_PRICE)
741 OR ((recinfo.SUGGESTED_RETAIL_PRICE is null) AND (X_SUGGESTED_RETAIL_PRICE is null)))
742 AND ((recinfo.MATERIAL_SAFETY_DATA_SHEET_NO = X_MATERIAL_SAFETY_DATA_SHEET_N)
743 OR ((recinfo.MATERIAL_SAFETY_DATA_SHEET_NO is null) AND (X_MATERIAL_SAFETY_DATA_SHEET_N is null)))
744 AND ((recinfo.HAS_BATCH_NUMBER = X_HAS_BATCH_NUMBER)
745 OR ((recinfo.HAS_BATCH_NUMBER is null) AND (X_HAS_BATCH_NUMBER is null)))
746 AND ((recinfo.IS_NON_SOLD_TRADE_RET_FLAG = X_IS_NON_SOLD_TRADE_RET_FLAG)
747 OR ((recinfo.IS_NON_SOLD_TRADE_RET_FLAG is null) AND (X_IS_NON_SOLD_TRADE_RET_FLAG is null)))
748 AND ((recinfo.IS_TRADE_ITEM_MAR_REC_FLAG = X_IS_TRADE_ITEM_MAR_REC_FLAG)
749 OR ((recinfo.IS_TRADE_ITEM_MAR_REC_FLAG is null) AND (X_IS_TRADE_ITEM_MAR_REC_FLAG is null)))
750 AND ((recinfo.DIAMETER = X_DIAMETER)
751 OR ((recinfo.DIAMETER is null) AND (X_DIAMETER is null)))
752 AND ((recinfo.UOM_DIAMETER = X_UOM_DIAMETER)
753 OR ((recinfo.UOM_DIAMETER is null) AND (X_UOM_DIAMETER is null)))
754 AND ((recinfo.DRAINED_WEIGHT = X_DRAINED_WEIGHT)
755 OR ((recinfo.DRAINED_WEIGHT is null) AND (X_DRAINED_WEIGHT is null)))
756 AND ((recinfo.UOM_DRAINED_WEIGHT = X_UOM_DRAINED_WEIGHT)
757 OR ((recinfo.UOM_DRAINED_WEIGHT is null) AND (X_UOM_DRAINED_WEIGHT is null)))
758 AND ((recinfo.GENERIC_INGREDIENT = X_GENERIC_INGREDIENT)
759 OR ((recinfo.GENERIC_INGREDIENT is null) AND (X_GENERIC_INGREDIENT is null)))
760 AND ((recinfo.GENERIC_INGREDIENT_STRGTH = X_GENERIC_INGREDIENT_STRGTH)
761 OR ((recinfo.GENERIC_INGREDIENT_STRGTH is null) AND (X_GENERIC_INGREDIENT_STRGTH is null)))
762 AND ((recinfo.UOM_GENERIC_INGREDIENT_STRGTH = X_UOM_GENERIC_INGREDIENT_STRGT)
763 OR ((recinfo.UOM_GENERIC_INGREDIENT_STRGTH is null) AND (X_UOM_GENERIC_INGREDIENT_STRGT is null)))
764 AND ((recinfo.INGREDIENT_STRENGTH = X_INGREDIENT_STRENGTH)
765 OR ((recinfo.INGREDIENT_STRENGTH is null) AND (X_INGREDIENT_STRENGTH is null)))
766 AND ((recinfo.IS_NET_CONTENT_DEC_FLAG = X_IS_NET_CONTENT_DEC_FLAG)
767 OR ((recinfo.IS_NET_CONTENT_DEC_FLAG is null) AND (X_IS_NET_CONTENT_DEC_FLAG is null)))
768 AND ((recinfo.NET_CONTENT = X_NET_CONTENT)
769 OR ((recinfo.NET_CONTENT is null) AND (X_NET_CONTENT is null)))
770 AND ((recinfo.UOM_NET_CONTENT = X_UOM_NET_CONTENT)
771 OR ((recinfo.UOM_NET_CONTENT is null) AND (X_UOM_NET_CONTENT is null)))
772 AND ((recinfo.PEG_HORIZONTAL = X_PEG_HORIZONTAL)
773 OR ((recinfo.PEG_HORIZONTAL is null) AND (X_PEG_HORIZONTAL is null)))
774 AND ((recinfo.UOM_PEG_HORIZONTAL = X_UOM_PEG_HORIZONTAL)
775 OR ((recinfo.UOM_PEG_HORIZONTAL is null) AND (X_UOM_PEG_HORIZONTAL is null)))
776 AND ((recinfo.PEG_VERTICAL = X_PEG_VERTICAL)
777 OR ((recinfo.PEG_VERTICAL is null) AND (X_PEG_VERTICAL is null)))
778 AND ((recinfo.UOM_PEG_VERTICAL = X_UOM_PEG_VERTICAL)
779 OR ((recinfo.UOM_PEG_VERTICAL is null) AND (X_UOM_PEG_VERTICAL is null)))
783 OR ((recinfo.DEL_TO_DIST_CNTR_TEMP_MAX is null) AND (X_DEL_TO_DIST_CNTR_TEMP_MAX is null)))
780 AND ((recinfo.CONSUMER_AVAIL_DATE_TIME = X_CONSUMER_AVAIL_DATE_TIME)
781 OR ((recinfo.CONSUMER_AVAIL_DATE_TIME is null) AND (X_CONSUMER_AVAIL_DATE_TIME is null)))
782 AND ((recinfo.DEL_TO_DIST_CNTR_TEMP_MAX = X_DEL_TO_DIST_CNTR_TEMP_MAX)
784 AND ((recinfo.UOM_DEL_TO_DIST_CNTR_TEMP_MAX = X_UOM_DEL_TO_DIST_CNTR_TEMP_MA)
785 OR ((recinfo.UOM_DEL_TO_DIST_CNTR_TEMP_MAX is null) AND (X_UOM_DEL_TO_DIST_CNTR_TEMP_MA is null)))
786 AND ((recinfo.DEL_TO_DIST_CNTR_TEMP_MIN = X_DEL_TO_DIST_CNTR_TEMP_MIN)
787 OR ((recinfo.DEL_TO_DIST_CNTR_TEMP_MIN is null) AND (X_DEL_TO_DIST_CNTR_TEMP_MIN is null)))
788 AND ((recinfo.TRADE_ITEM_DESCRIPTOR = X_TRADE_ITEM_DESCRIPTOR)
789 OR ((recinfo.TRADE_ITEM_DESCRIPTOR is null) AND (X_TRADE_ITEM_DESCRIPTOR is null)))
790 AND ((recinfo.EANUCC_CODE = X_EANUCC_CODE)
791 OR ((recinfo.EANUCC_CODE is null) AND (X_EANUCC_CODE is null)))
792 AND ((recinfo.EANUCC_TYPE = X_EANUCC_TYPE)
793 OR ((recinfo.EANUCC_TYPE is null) AND (X_EANUCC_TYPE is null)))
794 AND ((recinfo.RETAIL_PRICE_ON_TRADE_ITEM = X_RETAIL_PRICE_ON_TRADE_ITEM)
795 OR ((recinfo.RETAIL_PRICE_ON_TRADE_ITEM is null) AND (X_RETAIL_PRICE_ON_TRADE_ITEM is null)))
796 AND ((recinfo.QUANTITY_OF_COMP_LAY_ITEM = X_QUANTITY_OF_COMP_LAY_ITEM)
797 OR ((recinfo.QUANTITY_OF_COMP_LAY_ITEM is null) AND (X_QUANTITY_OF_COMP_LAY_ITEM is null)))
798 AND ((recinfo.QUANITY_OF_ITEM_IN_LAYER = X_QUANITY_OF_ITEM_IN_LAYER)
799 OR ((recinfo.QUANITY_OF_ITEM_IN_LAYER is null) AND (X_QUANITY_OF_ITEM_IN_LAYER is null)))
800 AND ((recinfo.QUANTITY_OF_ITEM_INNER_PACK = X_QUANTITY_OF_ITEM_INNER_PACK)
801 OR ((recinfo.QUANTITY_OF_ITEM_INNER_PACK is null) AND (X_QUANTITY_OF_ITEM_INNER_PACK is null)))
802 AND ((recinfo.TARGET_MARKET_DESC = X_TARGET_MARKET_DESC)
803 OR ((recinfo.TARGET_MARKET_DESC is null) AND (X_TARGET_MARKET_DESC is null)))
804 AND ((recinfo.QUANTITY_OF_INNER_PACK = X_QUANTITY_OF_INNER_PACK)
805 OR ((recinfo.QUANTITY_OF_INNER_PACK is null) AND (X_QUANTITY_OF_INNER_PACK is null)))
806 AND ((recinfo.BRAND_OWNER_GLN = X_BRAND_OWNER_GLN)
807 OR ((recinfo.BRAND_OWNER_GLN is null) AND (X_BRAND_OWNER_GLN is null)))
808 AND ((recinfo.BRAND_OWNER_NAME = X_BRAND_OWNER_NAME)
809 OR ((recinfo.BRAND_OWNER_NAME is null) AND (X_BRAND_OWNER_NAME is null)))
810 AND ((recinfo.STORAGE_HANDLING_TEMP_MAX = X_STORAGE_HANDLING_TEMP_MAX)
811 OR ((recinfo.STORAGE_HANDLING_TEMP_MAX is null) AND (X_STORAGE_HANDLING_TEMP_MAX is null)))
812 AND ((recinfo.UOM_STORAGE_HANDLING_TEMP_MAX = X_UOM_STORAGE_HANDLING_TEMP_MA)
813 OR ((recinfo.UOM_STORAGE_HANDLING_TEMP_MAX is null) AND (X_UOM_STORAGE_HANDLING_TEMP_MA is null)))
814 AND ((recinfo.STORAGE_HANDLING_TEMP_MIN = X_STORAGE_HANDLING_TEMP_MIN)
815 OR ((recinfo.STORAGE_HANDLING_TEMP_MIN is null) AND (X_STORAGE_HANDLING_TEMP_MIN is null)))
816 AND ((recinfo.UOM_STORAGE_HANDLING_TEMP_MIN = X_UOM_STORAGE_HANDLING_TEMP_MI)
817 OR ((recinfo.UOM_STORAGE_HANDLING_TEMP_MIN is null) AND (X_UOM_STORAGE_HANDLING_TEMP_MI is null)))
818 AND ((recinfo.TRADE_ITEM_COUPON = X_TRADE_ITEM_COUPON)
819 OR ((recinfo.TRADE_ITEM_COUPON is null) AND (X_TRADE_ITEM_COUPON is null)))
820 AND ((recinfo.DEGREE_OF_ORIGINAL_WORT = X_DEGREE_OF_ORIGINAL_WORT)
821 OR ((recinfo.DEGREE_OF_ORIGINAL_WORT is null) AND (X_DEGREE_OF_ORIGINAL_WORT is null)))
822 AND ((recinfo.FAT_PERCENT_IN_DRY_MATTER = X_FAT_PERCENT_IN_DRY_MATTER)
823 OR ((recinfo.FAT_PERCENT_IN_DRY_MATTER is null) AND (X_FAT_PERCENT_IN_DRY_MATTER is null)))
824 AND ((recinfo.PERCENT_OF_ALCOHOL_BY_VOL = X_PERCENT_OF_ALCOHOL_BY_VOL)
825 OR ((recinfo.PERCENT_OF_ALCOHOL_BY_VOL is null) AND (X_PERCENT_OF_ALCOHOL_BY_VOL is null)))
826 AND ((recinfo.ISBN_NUMBER = X_ISBN_NUMBER)
827 OR ((recinfo.ISBN_NUMBER is null) AND (X_ISBN_NUMBER is null)))
828 AND ((recinfo.ISSN_NUMBER = X_ISSN_NUMBER)
829 OR ((recinfo.ISSN_NUMBER is null) AND (X_ISSN_NUMBER is null)))
830 AND ((recinfo.IS_INGREDIENT_IRRADIATED = X_IS_INGREDIENT_IRRADIATED)
831 OR ((recinfo.IS_INGREDIENT_IRRADIATED is null) AND (X_IS_INGREDIENT_IRRADIATED is null)))
832 AND ((recinfo.IS_RAW_MATERIAL_IRRADIATED = X_IS_RAW_MATERIAL_IRRADIATED)
833 OR ((recinfo.IS_RAW_MATERIAL_IRRADIATED is null) AND (X_IS_RAW_MATERIAL_IRRADIATED is null)))
834 AND ((recinfo.IS_TRADE_ITEM_GENETICALLY_MOD = X_IS_TRADE_ITEM_GENETICALLY_MO)
835 OR ((recinfo.IS_TRADE_ITEM_GENETICALLY_MOD is null) AND (X_IS_TRADE_ITEM_GENETICALLY_MO is null)))
836 AND ((recinfo.IS_TRADE_ITEM_IRRADIATED = X_IS_TRADE_ITEM_IRRADIATED)
837 OR ((recinfo.IS_TRADE_ITEM_IRRADIATED is null) AND (X_IS_TRADE_ITEM_IRRADIATED is null)))
838 AND ((recinfo.PUBLICATION_STATUS = X_PUBLICATION_STATUS)
839 OR ((recinfo.PUBLICATION_STATUS is null) AND (X_PUBLICATION_STATUS is null)))
840 AND ((recinfo.TOP_GTIN = X_TOP_GTIN)
841 OR ((recinfo.TOP_GTIN is null) AND (X_TOP_GTIN is null)))
842 AND ((recinfo.SECURITY_TAG_LOCATION = X_SECURITY_TAG_LOCATION)
843 OR ((recinfo.SECURITY_TAG_LOCATION is null) AND (X_SECURITY_TAG_LOCATION is null)))
844 AND ((recinfo.URL_FOR_WARRANTY = X_URL_FOR_WARRANTY)
845 OR ((recinfo.URL_FOR_WARRANTY is null) AND (X_URL_FOR_WARRANTY is null)))
846 AND ((recinfo.NESTING_INCREMENT = X_NESTING_INCREMENT)
847 OR ((recinfo.NESTING_INCREMENT is null) AND (X_NESTING_INCREMENT is null)))
848 AND ((recinfo.UOM_NESTING_INCREMENT = X_UOM_NESTING_INCREMENT)
849 OR ((recinfo.UOM_NESTING_INCREMENT is null) AND (X_UOM_NESTING_INCREMENT is null)))
850 AND ((recinfo.IS_TRADE_ITEM_RECALLED = X_IS_TRADE_ITEM_RECALLED)
851 OR ((recinfo.IS_TRADE_ITEM_RECALLED is null) AND (X_IS_TRADE_ITEM_RECALLED is null)))
852 AND ((recinfo.MODEL_NUMBER = X_MODEL_NUMBER)
853 OR ((recinfo.MODEL_NUMBER is null) AND (X_MODEL_NUMBER is null)))
854 AND ((recinfo.PIECES_PER_TRADE_ITEM = X_PIECES_PER_TRADE_ITEM)
858 AND ((recinfo.DEPT_OF_TRNSPRT_DANG_GOODS_NUM = X_DEPT_OF_TRNSPRT_DANG_GOODS_N)
855 OR ((recinfo.PIECES_PER_TRADE_ITEM is null) AND (X_PIECES_PER_TRADE_ITEM is null)))
856 AND ((recinfo.UOM_PIECES_PER_TRADE_ITEM = X_UOM_PIECES_PER_TRADE_ITEM)
857 OR ((recinfo.UOM_PIECES_PER_TRADE_ITEM is null) AND (X_UOM_PIECES_PER_TRADE_ITEM is null)))
859 OR ((recinfo.DEPT_OF_TRNSPRT_DANG_GOODS_NUM is null) AND (X_DEPT_OF_TRNSPRT_DANG_GOODS_N is null)))
860 AND ((recinfo.RETURN_GOODS_POLICY = X_RETURN_GOODS_POLICY)
861 OR ((recinfo.RETURN_GOODS_POLICY is null) AND (X_RETURN_GOODS_POLICY is null)))
862 AND ((recinfo.IS_OUT_OF_BOX_PROVIDED = X_IS_OUT_OF_BOX_PROVIDED)
863 OR ((recinfo.IS_OUT_OF_BOX_PROVIDED is null) AND (X_IS_OUT_OF_BOX_PROVIDED is null)))
864 AND ((recinfo.REGISTRATION_UPDATE_DATE = X_REGISTRATION_UPDATE_DATE)
865 OR ((recinfo.REGISTRATION_UPDATE_DATE is null) AND (X_REGISTRATION_UPDATE_DATE is null)))
866 AND ((recinfo.TP_NEUTRAL_UPDATE_DATE = X_TP_NEUTRAL_UPDATE_DATE)
867 OR ((recinfo.TP_NEUTRAL_UPDATE_DATE is null) AND (X_TP_NEUTRAL_UPDATE_DATE is null)))
868 AND ((recinfo.IS_BARCODE_SYMBOLOGY_DERIVABLE = X_IS_BARCODE_SYMBOLOGY_DERIVAB)
869 OR ((recinfo.IS_BARCODE_SYMBOLOGY_DERIVABLE is null) AND (X_IS_BARCODE_SYMBOLOGY_DERIVAB is null)))
870 ) then
871 null;
872 else
873 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
874 app_exception.raise_exception;
875 end if;
876
877 for tlinfo in c1 loop
878 if (tlinfo.BASELANG = 'Y') then
879 if ( ((tlinfo.INVOICE_NAME = X_INVOICE_NAME)
880 OR ((tlinfo.INVOICE_NAME is null) AND (X_INVOICE_NAME is null)))
881 AND ((tlinfo.DESCRIPTIVE_SIZE = X_DESCRIPTIVE_SIZE)
882 OR ((tlinfo.DESCRIPTIVE_SIZE is null) AND (X_DESCRIPTIVE_SIZE is null)))
883 AND ((tlinfo.FUNCTIONAL_NAME = X_FUNCTIONAL_NAME)
884 OR ((tlinfo.FUNCTIONAL_NAME is null) AND (X_FUNCTIONAL_NAME is null)))
885 AND ((tlinfo.TRADE_ITEM_FORM_DESCRIPTION = X_TRADE_ITEM_FORM_DESCRIPTION)
886 OR ((tlinfo.TRADE_ITEM_FORM_DESCRIPTION is null) AND (X_TRADE_ITEM_FORM_DESCRIPTION is null)))
887 AND ((tlinfo.WARRANTY_DESCRIPTION = X_WARRANTY_DESCRIPTION)
888 OR ((tlinfo.WARRANTY_DESCRIPTION is null) AND (X_WARRANTY_DESCRIPTION is null)))
889 AND ((tlinfo.TRADE_ITEM_FINISH_DESCRIPTION = X_TRADE_ITEM_FINISH_DESCRIPTIO)
890 OR ((tlinfo.TRADE_ITEM_FINISH_DESCRIPTION is null) AND (X_TRADE_ITEM_FINISH_DESCRIPTIO is null)))
891 AND ((tlinfo.DESCRIPTION_SHORT = X_DESCRIPTION_SHORT)
892 OR ((tlinfo.DESCRIPTION_SHORT is null) AND (X_DESCRIPTION_SHORT is null)))
893 ) then
894 null;
895 else
896 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
897 app_exception.raise_exception;
898 end if;
899 end if;
900 end loop;
901 return;
902 end LOCK_ROW;
903
904 procedure UPDATE_ROW (
905 X_EXTENSION_ID in NUMBER,
906 X_REQUEST_ID in NUMBER,
907 X_DELIVERY_TO_MRKT_TEMP_MIN in NUMBER,
908 X_UOM_DELIVERY_TO_MRKT_TEMP_MI in VARCHAR2,
909 X_SUB_BRAND in VARCHAR2,
910 X_UOM_DEL_TO_DIST_CNTR_TEMP_MI in VARCHAR2,
911 X_DELIVERY_TO_MRKT_TEMP_MAX in NUMBER,
912 X_UOM_DELIVERY_TO_MRKT_TEMP_MA in VARCHAR2,
913 X_INVENTORY_ITEM_ID in NUMBER,
914 X_ORGANIZATION_ID in NUMBER,
915 X_ITEM_CATALOG_GROUP_ID in NUMBER,
916 X_REVISION_ID in NUMBER,
917 X_IS_TRADE_ITEM_A_CONSUMER_UNI in VARCHAR2,
918 X_IS_TRADE_ITEM_INFO_PRIVATE in VARCHAR2,
919 X_GROSS_WEIGHT in NUMBER,
920 X_UOM_GROSS_WEIGHT in VARCHAR2,
921 X_EFFECTIVE_DATE in DATE,
922 X_CANCELED_DATE in DATE,
923 X_DISCONTINUED_DATE in DATE,
924 X_END_AVAILABILITY_DATE_TIME in DATE,
925 X_START_AVAILABILITY_DATE_TIME in DATE,
926 X_BRAND_NAME in VARCHAR2,
927 X_IS_TRADE_ITEM_A_BASE_UNIT in VARCHAR2,
928 X_IS_TRADE_ITEM_A_VARIABLE_UNI in VARCHAR2,
929 X_IS_PACK_MARKED_WITH_EXP_DATE in VARCHAR2,
930 X_IS_PACK_MARKED_WITH_GREEN_DO in VARCHAR2,
931 X_IS_PACK_MARKED_WITH_INGRED in VARCHAR2,
932 X_IS_PACKAGE_MARKED_AS_REC in VARCHAR2,
933 X_IS_PACKAGE_MARKED_RET in VARCHAR2,
934 X_STACKING_FACTOR in NUMBER,
935 X_STACKING_WEIGHT_MAXIMUM in NUMBER,
936 X_UOM_STACKING_WEIGHT_MAXIMUM in VARCHAR2,
937 X_ORDERING_LEAD_TIME in NUMBER,
938 X_UOM_ORDERING_LEAD_TIME in VARCHAR2,
939 X_ORDER_QUANTITY_MAX in NUMBER,
940 X_ORDER_QUANTITY_MIN in NUMBER,
941 X_ORDER_QUANTITY_MULTIPLE in NUMBER,
942 X_ORDER_SIZING_FACTOR in NUMBER,
943 X_EFFECTIVE_START_DATE in DATE,
944 X_CATALOG_PRICE in NUMBER,
945 X_EFFECTIVE_END_DATE in DATE,
946 X_SUGGESTED_RETAIL_PRICE in NUMBER,
947 X_MATERIAL_SAFETY_DATA_SHEET_N in VARCHAR2,
948 X_HAS_BATCH_NUMBER in VARCHAR2,
949 X_IS_NON_SOLD_TRADE_RET_FLAG in VARCHAR2,
950 X_IS_TRADE_ITEM_MAR_REC_FLAG in VARCHAR2,
951 X_DIAMETER in NUMBER,
952 X_UOM_DIAMETER in VARCHAR2,
953 X_DRAINED_WEIGHT in NUMBER,
954 X_UOM_DRAINED_WEIGHT in VARCHAR2,
955 X_GENERIC_INGREDIENT in VARCHAR2,
956 X_GENERIC_INGREDIENT_STRGTH in NUMBER,
957 X_UOM_GENERIC_INGREDIENT_STRGT in VARCHAR2,
958 X_INGREDIENT_STRENGTH in VARCHAR2,
959 X_IS_NET_CONTENT_DEC_FLAG in VARCHAR2,
960 X_NET_CONTENT in NUMBER,
961 X_UOM_NET_CONTENT in VARCHAR2,
962 X_PEG_HORIZONTAL in NUMBER,
963 X_UOM_PEG_HORIZONTAL in VARCHAR2,
964 X_PEG_VERTICAL in NUMBER,
965 X_UOM_PEG_VERTICAL in VARCHAR2,
966 X_CONSUMER_AVAIL_DATE_TIME in DATE,
967 X_DEL_TO_DIST_CNTR_TEMP_MAX in NUMBER,
968 X_UOM_DEL_TO_DIST_CNTR_TEMP_MA in VARCHAR2,
969 X_DEL_TO_DIST_CNTR_TEMP_MIN in NUMBER,
970 X_TRADE_ITEM_DESCRIPTOR in VARCHAR2,
971 X_EANUCC_CODE in VARCHAR2,
972 X_EANUCC_TYPE in VARCHAR2,
973 X_RETAIL_PRICE_ON_TRADE_ITEM in NUMBER,
974 X_QUANTITY_OF_COMP_LAY_ITEM in NUMBER,
978 X_QUANTITY_OF_INNER_PACK in NUMBER,
975 X_QUANITY_OF_ITEM_IN_LAYER in NUMBER,
976 X_QUANTITY_OF_ITEM_INNER_PACK in NUMBER,
977 X_TARGET_MARKET_DESC in VARCHAR2,
979 X_BRAND_OWNER_GLN in VARCHAR2,
980 X_BRAND_OWNER_NAME in VARCHAR2,
981 X_STORAGE_HANDLING_TEMP_MAX in NUMBER,
982 X_UOM_STORAGE_HANDLING_TEMP_MA in VARCHAR2,
983 X_STORAGE_HANDLING_TEMP_MIN in NUMBER,
984 X_UOM_STORAGE_HANDLING_TEMP_MI in VARCHAR2,
985 X_TRADE_ITEM_COUPON in NUMBER,
986 X_DEGREE_OF_ORIGINAL_WORT in VARCHAR2,
987 X_FAT_PERCENT_IN_DRY_MATTER in NUMBER,
988 X_PERCENT_OF_ALCOHOL_BY_VOL in NUMBER,
989 X_ISBN_NUMBER in VARCHAR2,
990 X_ISSN_NUMBER in VARCHAR2,
991 X_IS_INGREDIENT_IRRADIATED in VARCHAR2,
992 X_IS_RAW_MATERIAL_IRRADIATED in VARCHAR2,
993 X_IS_TRADE_ITEM_GENETICALLY_MO in VARCHAR2,
994 X_IS_TRADE_ITEM_IRRADIATED in VARCHAR2,
995 X_PUBLICATION_STATUS in NUMBER,
996 X_TOP_GTIN in VARCHAR2,
997 X_SECURITY_TAG_LOCATION in VARCHAR2,
998 X_URL_FOR_WARRANTY in VARCHAR2,
999 X_NESTING_INCREMENT in NUMBER,
1000 X_UOM_NESTING_INCREMENT in VARCHAR2,
1001 X_IS_TRADE_ITEM_RECALLED in VARCHAR2,
1002 X_MODEL_NUMBER in VARCHAR2,
1003 X_PIECES_PER_TRADE_ITEM in NUMBER,
1004 X_UOM_PIECES_PER_TRADE_ITEM in VARCHAR2,
1005 X_DEPT_OF_TRNSPRT_DANG_GOODS_N in VARCHAR2,
1006 X_RETURN_GOODS_POLICY in VARCHAR2,
1007 X_IS_OUT_OF_BOX_PROVIDED in VARCHAR2,
1008 X_REGISTRATION_UPDATE_DATE in DATE,
1009 X_TP_NEUTRAL_UPDATE_DATE in DATE,
1010 X_IS_BARCODE_SYMBOLOGY_DERIVAB in VARCHAR2,
1011 X_INVOICE_NAME in VARCHAR2,
1012 X_DESCRIPTIVE_SIZE in VARCHAR2,
1013 X_FUNCTIONAL_NAME in VARCHAR2,
1014 X_TRADE_ITEM_FORM_DESCRIPTION in VARCHAR2,
1015 X_WARRANTY_DESCRIPTION in VARCHAR2,
1016 X_TRADE_ITEM_FINISH_DESCRIPTIO in VARCHAR2,
1017 X_DESCRIPTION_SHORT in VARCHAR2,
1018 X_LAST_UPDATE_DATE in DATE,
1019 X_LAST_UPDATED_BY in NUMBER,
1020 X_LAST_UPDATE_LOGIN in NUMBER
1021 ) is
1022 begin
1023 update EGO_ITEM_GTN_ATTRS_B set
1024 REQUEST_ID = X_REQUEST_ID,
1025 DELIVERY_TO_MRKT_TEMP_MIN = X_DELIVERY_TO_MRKT_TEMP_MIN,
1026 UOM_DELIVERY_TO_MRKT_TEMP_MIN = X_UOM_DELIVERY_TO_MRKT_TEMP_MI,
1027 SUB_BRAND = X_SUB_BRAND,
1028 UOM_DEL_TO_DIST_CNTR_TEMP_MIN = X_UOM_DEL_TO_DIST_CNTR_TEMP_MI,
1029 DELIVERY_TO_MRKT_TEMP_MAX = X_DELIVERY_TO_MRKT_TEMP_MAX,
1030 UOM_DELIVERY_TO_MRKT_TEMP_MAX = X_UOM_DELIVERY_TO_MRKT_TEMP_MA,
1031 INVENTORY_ITEM_ID = X_INVENTORY_ITEM_ID,
1032 ORGANIZATION_ID = X_ORGANIZATION_ID,
1033 ITEM_CATALOG_GROUP_ID = X_ITEM_CATALOG_GROUP_ID,
1034 REVISION_ID = X_REVISION_ID,
1035 IS_TRADE_ITEM_A_CONSUMER_UNIT = X_IS_TRADE_ITEM_A_CONSUMER_UNI,
1036 IS_TRADE_ITEM_INFO_PRIVATE = X_IS_TRADE_ITEM_INFO_PRIVATE,
1037 GROSS_WEIGHT = X_GROSS_WEIGHT,
1038 UOM_GROSS_WEIGHT = X_UOM_GROSS_WEIGHT,
1039 EFFECTIVE_DATE = X_EFFECTIVE_DATE,
1040 CANCELED_DATE = X_CANCELED_DATE,
1041 DISCONTINUED_DATE = X_DISCONTINUED_DATE,
1042 END_AVAILABILITY_DATE_TIME = X_END_AVAILABILITY_DATE_TIME,
1043 START_AVAILABILITY_DATE_TIME = X_START_AVAILABILITY_DATE_TIME,
1044 BRAND_NAME = X_BRAND_NAME,
1045 IS_TRADE_ITEM_A_BASE_UNIT = X_IS_TRADE_ITEM_A_BASE_UNIT,
1046 IS_TRADE_ITEM_A_VARIABLE_UNIT = X_IS_TRADE_ITEM_A_VARIABLE_UNI,
1047 IS_PACK_MARKED_WITH_EXP_DATE = X_IS_PACK_MARKED_WITH_EXP_DATE,
1048 IS_PACK_MARKED_WITH_GREEN_DOT = X_IS_PACK_MARKED_WITH_GREEN_DO,
1049 IS_PACK_MARKED_WITH_INGRED = X_IS_PACK_MARKED_WITH_INGRED,
1050 IS_PACKAGE_MARKED_AS_REC = X_IS_PACKAGE_MARKED_AS_REC,
1051 IS_PACKAGE_MARKED_RET = X_IS_PACKAGE_MARKED_RET,
1052 STACKING_FACTOR = X_STACKING_FACTOR,
1053 STACKING_WEIGHT_MAXIMUM = X_STACKING_WEIGHT_MAXIMUM,
1054 UOM_STACKING_WEIGHT_MAXIMUM = X_UOM_STACKING_WEIGHT_MAXIMUM,
1055 ORDERING_LEAD_TIME = X_ORDERING_LEAD_TIME,
1056 UOM_ORDERING_LEAD_TIME = X_UOM_ORDERING_LEAD_TIME,
1057 ORDER_QUANTITY_MAX = X_ORDER_QUANTITY_MAX,
1058 ORDER_QUANTITY_MIN = X_ORDER_QUANTITY_MIN,
1059 ORDER_QUANTITY_MULTIPLE = X_ORDER_QUANTITY_MULTIPLE,
1060 ORDER_SIZING_FACTOR = X_ORDER_SIZING_FACTOR,
1061 EFFECTIVE_START_DATE = X_EFFECTIVE_START_DATE,
1062 CATALOG_PRICE = X_CATALOG_PRICE,
1063 EFFECTIVE_END_DATE = X_EFFECTIVE_END_DATE,
1064 SUGGESTED_RETAIL_PRICE = X_SUGGESTED_RETAIL_PRICE,
1065 MATERIAL_SAFETY_DATA_SHEET_NO = X_MATERIAL_SAFETY_DATA_SHEET_N,
1066 HAS_BATCH_NUMBER = X_HAS_BATCH_NUMBER,
1067 IS_NON_SOLD_TRADE_RET_FLAG = X_IS_NON_SOLD_TRADE_RET_FLAG,
1068 IS_TRADE_ITEM_MAR_REC_FLAG = X_IS_TRADE_ITEM_MAR_REC_FLAG,
1069 DIAMETER = X_DIAMETER,
1070 UOM_DIAMETER = X_UOM_DIAMETER,
1071 DRAINED_WEIGHT = X_DRAINED_WEIGHT,
1072 UOM_DRAINED_WEIGHT = X_UOM_DRAINED_WEIGHT,
1073 GENERIC_INGREDIENT = X_GENERIC_INGREDIENT,
1074 GENERIC_INGREDIENT_STRGTH = X_GENERIC_INGREDIENT_STRGTH,
1075 UOM_GENERIC_INGREDIENT_STRGTH = X_UOM_GENERIC_INGREDIENT_STRGT,
1076 INGREDIENT_STRENGTH = X_INGREDIENT_STRENGTH,
1077 IS_NET_CONTENT_DEC_FLAG = X_IS_NET_CONTENT_DEC_FLAG,
1078 NET_CONTENT = X_NET_CONTENT,
1079 UOM_NET_CONTENT = X_UOM_NET_CONTENT,
1080 PEG_HORIZONTAL = X_PEG_HORIZONTAL,
1081 UOM_PEG_HORIZONTAL = X_UOM_PEG_HORIZONTAL,
1082 PEG_VERTICAL = X_PEG_VERTICAL,
1083 UOM_PEG_VERTICAL = X_UOM_PEG_VERTICAL,
1084 CONSUMER_AVAIL_DATE_TIME = X_CONSUMER_AVAIL_DATE_TIME,
1085 DEL_TO_DIST_CNTR_TEMP_MAX = X_DEL_TO_DIST_CNTR_TEMP_MAX,
1086 UOM_DEL_TO_DIST_CNTR_TEMP_MAX = X_UOM_DEL_TO_DIST_CNTR_TEMP_MA,
1087 DEL_TO_DIST_CNTR_TEMP_MIN = X_DEL_TO_DIST_CNTR_TEMP_MIN,
1088 TRADE_ITEM_DESCRIPTOR = X_TRADE_ITEM_DESCRIPTOR,
1089 EANUCC_CODE = X_EANUCC_CODE,
1090 EANUCC_TYPE = X_EANUCC_TYPE,
1091 RETAIL_PRICE_ON_TRADE_ITEM = X_RETAIL_PRICE_ON_TRADE_ITEM,
1092 QUANTITY_OF_COMP_LAY_ITEM = X_QUANTITY_OF_COMP_LAY_ITEM,
1093 QUANITY_OF_ITEM_IN_LAYER = X_QUANITY_OF_ITEM_IN_LAYER,
1094 QUANTITY_OF_ITEM_INNER_PACK = X_QUANTITY_OF_ITEM_INNER_PACK,
1098 BRAND_OWNER_NAME = X_BRAND_OWNER_NAME,
1095 TARGET_MARKET_DESC = X_TARGET_MARKET_DESC,
1096 QUANTITY_OF_INNER_PACK = X_QUANTITY_OF_INNER_PACK,
1097 BRAND_OWNER_GLN = X_BRAND_OWNER_GLN,
1099 STORAGE_HANDLING_TEMP_MAX = X_STORAGE_HANDLING_TEMP_MAX,
1100 UOM_STORAGE_HANDLING_TEMP_MAX = X_UOM_STORAGE_HANDLING_TEMP_MA,
1101 STORAGE_HANDLING_TEMP_MIN = X_STORAGE_HANDLING_TEMP_MIN,
1102 UOM_STORAGE_HANDLING_TEMP_MIN = X_UOM_STORAGE_HANDLING_TEMP_MI,
1103 TRADE_ITEM_COUPON = X_TRADE_ITEM_COUPON,
1104 DEGREE_OF_ORIGINAL_WORT = X_DEGREE_OF_ORIGINAL_WORT,
1105 FAT_PERCENT_IN_DRY_MATTER = X_FAT_PERCENT_IN_DRY_MATTER,
1106 PERCENT_OF_ALCOHOL_BY_VOL = X_PERCENT_OF_ALCOHOL_BY_VOL,
1107 ISBN_NUMBER = X_ISBN_NUMBER,
1108 ISSN_NUMBER = X_ISSN_NUMBER,
1109 IS_INGREDIENT_IRRADIATED = X_IS_INGREDIENT_IRRADIATED,
1110 IS_RAW_MATERIAL_IRRADIATED = X_IS_RAW_MATERIAL_IRRADIATED,
1111 IS_TRADE_ITEM_GENETICALLY_MOD = X_IS_TRADE_ITEM_GENETICALLY_MO,
1112 IS_TRADE_ITEM_IRRADIATED = X_IS_TRADE_ITEM_IRRADIATED,
1113 PUBLICATION_STATUS = X_PUBLICATION_STATUS,
1114 TOP_GTIN = X_TOP_GTIN,
1115 SECURITY_TAG_LOCATION = X_SECURITY_TAG_LOCATION,
1116 URL_FOR_WARRANTY = X_URL_FOR_WARRANTY,
1117 NESTING_INCREMENT = X_NESTING_INCREMENT,
1118 UOM_NESTING_INCREMENT = X_UOM_NESTING_INCREMENT,
1119 IS_TRADE_ITEM_RECALLED = X_IS_TRADE_ITEM_RECALLED,
1120 MODEL_NUMBER = X_MODEL_NUMBER,
1121 PIECES_PER_TRADE_ITEM = X_PIECES_PER_TRADE_ITEM,
1122 UOM_PIECES_PER_TRADE_ITEM = X_UOM_PIECES_PER_TRADE_ITEM,
1123 DEPT_OF_TRNSPRT_DANG_GOODS_NUM = X_DEPT_OF_TRNSPRT_DANG_GOODS_N,
1124 RETURN_GOODS_POLICY = X_RETURN_GOODS_POLICY,
1125 IS_OUT_OF_BOX_PROVIDED = X_IS_OUT_OF_BOX_PROVIDED,
1126 REGISTRATION_UPDATE_DATE = X_REGISTRATION_UPDATE_DATE,
1127 TP_NEUTRAL_UPDATE_DATE = X_TP_NEUTRAL_UPDATE_DATE,
1128 IS_BARCODE_SYMBOLOGY_DERIVABLE = X_IS_BARCODE_SYMBOLOGY_DERIVAB,
1129 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1130 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1131 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
1132 where EXTENSION_ID = X_EXTENSION_ID;
1133
1134 if (sql%notfound) then
1135 raise no_data_found;
1136 end if;
1137
1138 update EGO_ITEM_GTN_ATTRS_TL set
1139 INVOICE_NAME = X_INVOICE_NAME,
1140 DESCRIPTIVE_SIZE = X_DESCRIPTIVE_SIZE,
1141 FUNCTIONAL_NAME = X_FUNCTIONAL_NAME,
1142 TRADE_ITEM_FORM_DESCRIPTION = X_TRADE_ITEM_FORM_DESCRIPTION,
1143 WARRANTY_DESCRIPTION = X_WARRANTY_DESCRIPTION,
1144 TRADE_ITEM_FINISH_DESCRIPTION = X_TRADE_ITEM_FINISH_DESCRIPTIO,
1145 DESCRIPTION_SHORT = X_DESCRIPTION_SHORT,
1146 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1147 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1148 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
1149 SOURCE_LANG = userenv('LANG')
1150 where EXTENSION_ID = X_EXTENSION_ID
1151 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
1152
1153 if (sql%notfound) then
1154 raise no_data_found;
1155 end if;
1156 end UPDATE_ROW;
1157
1158 procedure DELETE_ROW (
1159 X_EXTENSION_ID in NUMBER
1160 ) is
1161 begin
1162 delete from EGO_ITEM_GTN_ATTRS_TL
1163 where EXTENSION_ID = X_EXTENSION_ID;
1164
1165 if (sql%notfound) then
1166 raise no_data_found;
1167 end if;
1168
1169 delete from EGO_ITEM_GTN_ATTRS_B
1170 where EXTENSION_ID = X_EXTENSION_ID;
1171
1172 if (sql%notfound) then
1173 raise no_data_found;
1174 end if;
1175 end DELETE_ROW;
1176
1177 procedure ADD_LANGUAGE
1178 is
1179 begin
1180 delete from EGO_ITEM_GTN_ATTRS_TL T
1181 where not exists
1182 (select NULL
1183 from EGO_ITEM_GTN_ATTRS_B B
1184 where B.EXTENSION_ID = T.EXTENSION_ID
1185 );
1186
1187 update EGO_ITEM_GTN_ATTRS_TL T set (
1188 INVOICE_NAME,
1189 DESCRIPTIVE_SIZE,
1190 FUNCTIONAL_NAME,
1191 TRADE_ITEM_FORM_DESCRIPTION,
1192 WARRANTY_DESCRIPTION,
1193 TRADE_ITEM_FINISH_DESCRIPTION,
1194 DESCRIPTION_SHORT
1195 ) = (select
1196 B.INVOICE_NAME,
1197 B.DESCRIPTIVE_SIZE,
1198 B.FUNCTIONAL_NAME,
1199 B.TRADE_ITEM_FORM_DESCRIPTION,
1200 B.WARRANTY_DESCRIPTION,
1201 B.TRADE_ITEM_FINISH_DESCRIPTION,
1202 B.DESCRIPTION_SHORT
1203 from EGO_ITEM_GTN_ATTRS_TL B
1204 where B.EXTENSION_ID = T.EXTENSION_ID
1205 and B.LANGUAGE = T.SOURCE_LANG)
1206 where (
1207 T.EXTENSION_ID,
1208 T.LANGUAGE
1209 ) in (select
1210 SUBT.EXTENSION_ID,
1211 SUBT.LANGUAGE
1212 from EGO_ITEM_GTN_ATTRS_TL SUBB, EGO_ITEM_GTN_ATTRS_TL SUBT
1213 where SUBB.EXTENSION_ID = SUBT.EXTENSION_ID
1214 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
1215 and (SUBB.INVOICE_NAME <> SUBT.INVOICE_NAME
1216 or (SUBB.INVOICE_NAME is null and SUBT.INVOICE_NAME is not null)
1217 or (SUBB.INVOICE_NAME is not null and SUBT.INVOICE_NAME is null)
1218 or SUBB.DESCRIPTIVE_SIZE <> SUBT.DESCRIPTIVE_SIZE
1219 or (SUBB.DESCRIPTIVE_SIZE is null and SUBT.DESCRIPTIVE_SIZE is not null)
1220 or (SUBB.DESCRIPTIVE_SIZE is not null and SUBT.DESCRIPTIVE_SIZE is null)
1221 or SUBB.FUNCTIONAL_NAME <> SUBT.FUNCTIONAL_NAME
1222 or (SUBB.FUNCTIONAL_NAME is null and SUBT.FUNCTIONAL_NAME is not null)
1223 or (SUBB.FUNCTIONAL_NAME is not null and SUBT.FUNCTIONAL_NAME is null)
1224 or SUBB.TRADE_ITEM_FORM_DESCRIPTION <> SUBT.TRADE_ITEM_FORM_DESCRIPTION
1225 or (SUBB.TRADE_ITEM_FORM_DESCRIPTION is null and SUBT.TRADE_ITEM_FORM_DESCRIPTION is not null)
1226 or (SUBB.TRADE_ITEM_FORM_DESCRIPTION is not null and SUBT.TRADE_ITEM_FORM_DESCRIPTION is null)
1227 or SUBB.WARRANTY_DESCRIPTION <> SUBT.WARRANTY_DESCRIPTION
1228 or (SUBB.WARRANTY_DESCRIPTION is null and SUBT.WARRANTY_DESCRIPTION is not null)
1229 or (SUBB.WARRANTY_DESCRIPTION is not null and SUBT.WARRANTY_DESCRIPTION is null)
1233 or SUBB.DESCRIPTION_SHORT <> SUBT.DESCRIPTION_SHORT
1230 or SUBB.TRADE_ITEM_FINISH_DESCRIPTION <> SUBT.TRADE_ITEM_FINISH_DESCRIPTION
1231 or (SUBB.TRADE_ITEM_FINISH_DESCRIPTION is null and SUBT.TRADE_ITEM_FINISH_DESCRIPTION is not null)
1232 or (SUBB.TRADE_ITEM_FINISH_DESCRIPTION is not null and SUBT.TRADE_ITEM_FINISH_DESCRIPTION is null)
1234 or (SUBB.DESCRIPTION_SHORT is null and SUBT.DESCRIPTION_SHORT is not null)
1235 or (SUBB.DESCRIPTION_SHORT is not null and SUBT.DESCRIPTION_SHORT is null)
1236 ));
1237
1238 insert into EGO_ITEM_GTN_ATTRS_TL (
1239 REQUEST_ID,
1240 EXTENSION_ID,
1241 INVENTORY_ITEM_ID,
1242 ORGANIZATION_ID,
1243 CREATED_BY,
1244 CREATION_DATE,
1245 LAST_UPDATED_BY,
1246 LAST_UPDATE_DATE,
1247 LAST_UPDATE_LOGIN,
1248 ITEM_CATALOG_GROUP_ID,
1249 REVISION_ID,
1250 INVOICE_NAME,
1251 DESCRIPTIVE_SIZE,
1252 FUNCTIONAL_NAME,
1253 TRADE_ITEM_FORM_DESCRIPTION,
1254 WARRANTY_DESCRIPTION,
1255 TRADE_ITEM_FINISH_DESCRIPTION,
1256 DESCRIPTION_SHORT,
1257 LANGUAGE,
1258 SOURCE_LANG
1259 ) select /*+ ORDERED */
1260 B.REQUEST_ID,
1261 B.EXTENSION_ID,
1262 B.INVENTORY_ITEM_ID,
1263 B.ORGANIZATION_ID,
1264 B.CREATED_BY,
1265 B.CREATION_DATE,
1266 B.LAST_UPDATED_BY,
1267 B.LAST_UPDATE_DATE,
1268 B.LAST_UPDATE_LOGIN,
1269 B.ITEM_CATALOG_GROUP_ID,
1270 B.REVISION_ID,
1271 B.INVOICE_NAME,
1272 B.DESCRIPTIVE_SIZE,
1273 B.FUNCTIONAL_NAME,
1274 B.TRADE_ITEM_FORM_DESCRIPTION,
1275 B.WARRANTY_DESCRIPTION,
1276 B.TRADE_ITEM_FINISH_DESCRIPTION,
1277 B.DESCRIPTION_SHORT,
1278 L.LANGUAGE_CODE,
1279 B.SOURCE_LANG
1280 from EGO_ITEM_GTN_ATTRS_TL B, FND_LANGUAGES L
1281 where L.INSTALLED_FLAG in ('I', 'B')
1282 and B.LANGUAGE = userenv('LANG')
1283 and not exists
1284 (select NULL
1285 from EGO_ITEM_GTN_ATTRS_TL T
1286 where T.EXTENSION_ID = B.EXTENSION_ID
1287 and T.LANGUAGE = L.LANGUAGE_CODE);
1288 end ADD_LANGUAGE;
1289
1290 end EGO_ITEM_GTN_ATTRS_PKG;
[Home] [Help]