1 package body IC_ITEM_MST_PKG as
2 /* $Header: gmiitemb.pls 115.3 2003/03/07 15:11:35 jdiiorio noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_ITEM_ID in NUMBER,
6 X_ITEM_NO in VARCHAR2,
7 X_ALT_ITEMA in VARCHAR2,
8 X_ALT_ITEMB in VARCHAR2,
9 X_ITEM_UM in VARCHAR2,
10 X_DUALUM_IND in NUMBER,
11 X_ITEM_UM2 in VARCHAR2,
12 X_DEVIATION_LO in NUMBER,
13 X_DEVIATION_HI in NUMBER,
14 X_LEVEL_CODE in NUMBER,
15 X_LOT_CTL in NUMBER,
16 X_LOT_INDIVISIBLE in NUMBER,
17 X_SUBLOT_CTL in NUMBER,
18 X_LOCT_CTL in NUMBER,
19 X_NONINV_IND in NUMBER,
20 X_MATCH_TYPE in NUMBER,
21 X_INACTIVE_IND in NUMBER,
22 X_INV_TYPE in VARCHAR2,
23 X_SHELF_LIFE in NUMBER,
24 X_RETEST_INTERVAL in NUMBER,
25 X_GL_CLASS in VARCHAR2,
26 X_INV_CLASS in VARCHAR2,
27 X_SALES_CLASS in VARCHAR2,
28 X_SHIP_CLASS in VARCHAR2,
29 X_FRT_CLASS in VARCHAR2,
30 X_PRICE_CLASS in VARCHAR2,
31 X_STORAGE_CLASS in VARCHAR2,
32 X_PURCH_CLASS in VARCHAR2,
33 X_TAX_CLASS in VARCHAR2,
34 X_CUSTOMS_CLASS in VARCHAR2,
35 X_ALLOC_CLASS in VARCHAR2,
36 X_PLANNING_CLASS in VARCHAR2,
37 X_ITEMCOST_CLASS in VARCHAR2,
38 X_COST_MTHD_CODE in VARCHAR2,
39 X_UPC_CODE in VARCHAR2,
40 X_GRADE_CTL in NUMBER,
41 X_STATUS_CTL in NUMBER,
42 X_QC_GRADE in VARCHAR2,
43 X_LOT_STATUS in VARCHAR2,
44 X_BULK_ID in NUMBER,
45 X_PKG_ID in NUMBER,
46 X_QCITEM_ID in NUMBER,
47 X_QCHOLD_RES_CODE in VARCHAR2,
48 X_EXPACTION_CODE in VARCHAR2,
49 X_FILL_QTY in NUMBER,
50 X_FILL_UM in VARCHAR2,
51 X_PLANNING_CATEGORY_ID in NUMBER,
52 X_PRICE_CATEGORY_ID in NUMBER,
53 X_EXPACTION_INTERVAL in NUMBER,
54 X_PHANTOM_TYPE in NUMBER,
55 X_WHSE_ITEM_ID in NUMBER,
56 X_EXPERIMENTAL_IND in NUMBER,
57 X_EXPORTED_DATE in DATE,
58 X_TRANS_CNT in NUMBER,
59 X_DELETE_MARK in NUMBER,
60 X_TEXT_CODE in NUMBER,
61 X_SEQ_DPND_CLASS in VARCHAR2,
62 X_COMMODITY_CODE in VARCHAR2,
63 X_REQUEST_ID in NUMBER,
64 X_ATTRIBUTE1 in VARCHAR2,
65 X_ATTRIBUTE2 in VARCHAR2,
66 X_ATTRIBUTE3 in VARCHAR2,
67 X_ATTRIBUTE4 in VARCHAR2,
68 X_ATTRIBUTE5 in VARCHAR2,
69 X_ATTRIBUTE6 in VARCHAR2,
70 X_ATTRIBUTE7 in VARCHAR2,
71 X_ATTRIBUTE8 in VARCHAR2,
72 X_ATTRIBUTE9 in VARCHAR2,
73 X_ATTRIBUTE10 in VARCHAR2,
74 X_ATTRIBUTE11 in VARCHAR2,
75 X_ATTRIBUTE12 in VARCHAR2,
76 X_ATTRIBUTE13 in VARCHAR2,
77 X_ATTRIBUTE14 in VARCHAR2,
78 X_ATTRIBUTE15 in VARCHAR2,
79 X_ATTRIBUTE16 in VARCHAR2,
80 X_ATTRIBUTE17 in VARCHAR2,
81 X_ATTRIBUTE18 in VARCHAR2,
82 X_ATTRIBUTE19 in VARCHAR2,
83 X_ATTRIBUTE20 in VARCHAR2,
84 X_ATTRIBUTE21 in VARCHAR2,
85 X_ATTRIBUTE22 in VARCHAR2,
86 X_ATTRIBUTE23 in VARCHAR2,
87 X_ATTRIBUTE24 in VARCHAR2,
88 X_ATTRIBUTE25 in VARCHAR2,
89 X_ATTRIBUTE26 in VARCHAR2,
90 X_ATTRIBUTE27 in VARCHAR2,
91 X_ATTRIBUTE28 in VARCHAR2,
92 X_ATTRIBUTE29 in VARCHAR2,
93 X_ATTRIBUTE30 in VARCHAR2,
94 X_ATTRIBUTE_CATEGORY in VARCHAR2,
95 X_ITEM_ABCCODE in VARCHAR2,
96 X_ALLOC_CATEGORY_ID in NUMBER,
97 X_CUSTOMS_CATEGORY_ID in NUMBER,
98 X_FRT_CATEGORY_ID in NUMBER,
99 X_GL_CATEGORY_ID in NUMBER,
100 X_INV_CATEGORY_ID in NUMBER,
101 X_COST_CATEGORY_ID in NUMBER,
102 X_PURCH_CATEGORY_ID in NUMBER,
103 X_SALES_CATEGORY_ID in NUMBER,
104 X_SEQ_CATEGORY_ID in NUMBER,
105 X_SHIP_CATEGORY_ID in NUMBER,
106 X_STORAGE_CATEGORY_ID in NUMBER,
107 X_TAX_CATEGORY_ID in NUMBER,
108 X_ITEM_DESC1 in VARCHAR2,
109 X_ITEM_DESC2 in VARCHAR2,
110 X_ONT_PRICING_QTY_SOURCE in NUMBER, -- added for pricing by qty2 project
111 X_AUTOLOT_ACTIVE_INDICATOR in NUMBER DEFAULT 0,
112 X_LOT_PREFIX in VARCHAR2 DEFAULT NULL,
113 X_LOT_SUFFIX in NUMBER DEFAULT 0,
114 X_SUBLOT_PREFIX in VARCHAR2 DEFAULT NULL,
115 X_SUBLOT_SUFFIX in NUMBER DEFAULT 0,
116 X_CREATION_DATE in DATE,
117 X_CREATED_BY in NUMBER,
118 X_LAST_UPDATE_DATE in DATE,
119 X_LAST_UPDATED_BY in NUMBER,
120 X_LAST_UPDATE_LOGIN in NUMBER
121 ) is
122 cursor C is select ROWID from IC_ITEM_MST_B
123 where ITEM_ID = X_ITEM_ID
124 ;
125 begin
126 insert into IC_ITEM_MST_B (
127 ITEM_ID,
128 ITEM_NO,
129 ITEM_DESC1,
130 ITEM_DESC2,
131 ALT_ITEMA,
132 ALT_ITEMB,
133 ITEM_UM,
134 DUALUM_IND,
135 ITEM_UM2,
136 DEVIATION_LO,
137 DEVIATION_HI,
138 LEVEL_CODE,
139 LOT_CTL,
140 LOT_INDIVISIBLE,
141 SUBLOT_CTL,
142 LOCT_CTL,
143 NONINV_IND,
144 MATCH_TYPE,
145 INACTIVE_IND,
146 INV_TYPE,
147 SHELF_LIFE,
148 RETEST_INTERVAL,
149 GL_CLASS,
150 INV_CLASS,
151 SALES_CLASS,
152 SHIP_CLASS,
153 FRT_CLASS,
154 PRICE_CLASS,
155 STORAGE_CLASS,
156 PURCH_CLASS,
157 TAX_CLASS,
158 CUSTOMS_CLASS,
159 ALLOC_CLASS,
160 PLANNING_CLASS,
161 ITEMCOST_CLASS,
162 COST_MTHD_CODE,
163 UPC_CODE,
164 GRADE_CTL,
165 STATUS_CTL,
166 QC_GRADE,
167 LOT_STATUS,
168 BULK_ID,
169 PKG_ID,
170 QCITEM_ID,
171 QCHOLD_RES_CODE,
172 EXPACTION_CODE,
173 FILL_QTY,
174 FILL_UM,
175 PLANNING_CATEGORY_ID,
176 PRICE_CATEGORY_ID,
177 EXPACTION_INTERVAL,
178 PHANTOM_TYPE,
179 WHSE_ITEM_ID,
180 EXPERIMENTAL_IND,
181 EXPORTED_DATE,
182 TRANS_CNT,
183 DELETE_MARK,
184 TEXT_CODE,
185 SEQ_DPND_CLASS,
186 COMMODITY_CODE,
187 REQUEST_ID,
188 ATTRIBUTE1,
189 ATTRIBUTE2,
190 ATTRIBUTE3,
191 ATTRIBUTE4,
192 ATTRIBUTE5,
193 ATTRIBUTE6,
194 ATTRIBUTE7,
195 ATTRIBUTE8,
196 ATTRIBUTE9,
197 ATTRIBUTE10,
198 ATTRIBUTE11,
199 ATTRIBUTE12,
200 ATTRIBUTE13,
201 ATTRIBUTE14,
202 ATTRIBUTE15,
203 ATTRIBUTE16,
204 ATTRIBUTE17,
205 ATTRIBUTE18,
206 ATTRIBUTE19,
207 ATTRIBUTE20,
208 ATTRIBUTE21,
209 ATTRIBUTE22,
210 ATTRIBUTE23,
211 ATTRIBUTE24,
212 ATTRIBUTE25,
213 ATTRIBUTE26,
214 ATTRIBUTE27,
215 ATTRIBUTE28,
216 ATTRIBUTE29,
217 ATTRIBUTE30,
218 ATTRIBUTE_CATEGORY,
219 ITEM_ABCCODE,
220 ALLOC_CATEGORY_ID,
221 CUSTOMS_CATEGORY_ID,
222 FRT_CATEGORY_ID,
223 GL_CATEGORY_ID,
224 INV_CATEGORY_ID,
225 COST_CATEGORY_ID,
226 PURCH_CATEGORY_ID,
227 SALES_CATEGORY_ID,
228 SEQ_CATEGORY_ID,
229 SHIP_CATEGORY_ID,
230 STORAGE_CATEGORY_ID,
231 TAX_CATEGORY_ID,
232 ONT_PRICING_QTY_SOURCE, -- added for pricing by qty2 project
233 AUTOLOT_ACTIVE_INDICATOR,
234 LOT_PREFIX,
235 LOT_SUFFIX,
236 SUBLOT_PREFIX,
237 SUBLOT_SUFFIX,
238 CREATION_DATE,
239 CREATED_BY,
240 LAST_UPDATE_DATE,
241 LAST_UPDATED_BY,
242 LAST_UPDATE_LOGIN
243 ) values (
244 X_ITEM_ID,
245 X_ITEM_NO,
246 X_ITEM_DESC1,
247 X_ITEM_DESC2,
248 X_ALT_ITEMA,
249 X_ALT_ITEMB,
250 X_ITEM_UM,
251 X_DUALUM_IND,
252 X_ITEM_UM2,
253 X_DEVIATION_LO,
254 X_DEVIATION_HI,
255 X_LEVEL_CODE,
256 X_LOT_CTL,
257 X_LOT_INDIVISIBLE,
258 X_SUBLOT_CTL,
259 X_LOCT_CTL,
260 X_NONINV_IND,
261 X_MATCH_TYPE,
262 X_INACTIVE_IND,
263 X_INV_TYPE,
264 X_SHELF_LIFE,
265 X_RETEST_INTERVAL,
266 X_GL_CLASS,
267 X_INV_CLASS,
268 X_SALES_CLASS,
269 X_SHIP_CLASS,
270 X_FRT_CLASS,
271 X_PRICE_CLASS,
272 X_STORAGE_CLASS,
273 X_PURCH_CLASS,
274 X_TAX_CLASS,
275 X_CUSTOMS_CLASS,
276 X_ALLOC_CLASS,
277 X_PLANNING_CLASS,
278 X_ITEMCOST_CLASS,
279 X_COST_MTHD_CODE,
280 X_UPC_CODE,
281 X_GRADE_CTL,
282 X_STATUS_CTL,
283 X_QC_GRADE,
284 X_LOT_STATUS,
285 X_BULK_ID,
286 X_PKG_ID,
287 X_QCITEM_ID,
288 X_QCHOLD_RES_CODE,
289 X_EXPACTION_CODE,
290 X_FILL_QTY,
291 X_FILL_UM,
292 X_PLANNING_CATEGORY_ID,
293 X_PRICE_CATEGORY_ID,
294 X_EXPACTION_INTERVAL,
295 X_PHANTOM_TYPE,
296 X_WHSE_ITEM_ID,
297 X_EXPERIMENTAL_IND,
298 X_EXPORTED_DATE,
299 X_TRANS_CNT,
300 X_DELETE_MARK,
301 X_TEXT_CODE,
302 X_SEQ_DPND_CLASS,
303 X_COMMODITY_CODE,
304 X_REQUEST_ID,
305 X_ATTRIBUTE1,
306 X_ATTRIBUTE2,
307 X_ATTRIBUTE3,
308 X_ATTRIBUTE4,
309 X_ATTRIBUTE5,
310 X_ATTRIBUTE6,
311 X_ATTRIBUTE7,
312 X_ATTRIBUTE8,
313 X_ATTRIBUTE9,
314 X_ATTRIBUTE10,
315 X_ATTRIBUTE11,
316 X_ATTRIBUTE12,
317 X_ATTRIBUTE13,
318 X_ATTRIBUTE14,
319 X_ATTRIBUTE15,
320 X_ATTRIBUTE16,
321 X_ATTRIBUTE17,
322 X_ATTRIBUTE18,
323 X_ATTRIBUTE19,
324 X_ATTRIBUTE20,
325 X_ATTRIBUTE21,
326 X_ATTRIBUTE22,
327 X_ATTRIBUTE23,
328 X_ATTRIBUTE24,
329 X_ATTRIBUTE25,
330 X_ATTRIBUTE26,
331 X_ATTRIBUTE27,
332 X_ATTRIBUTE28,
333 X_ATTRIBUTE29,
334 X_ATTRIBUTE30,
335 X_ATTRIBUTE_CATEGORY,
336 X_ITEM_ABCCODE,
337 X_ALLOC_CATEGORY_ID,
338 X_CUSTOMS_CATEGORY_ID,
339 X_FRT_CATEGORY_ID,
340 X_GL_CATEGORY_ID,
341 X_INV_CATEGORY_ID,
342 X_COST_CATEGORY_ID,
343 X_PURCH_CATEGORY_ID,
344 X_SALES_CATEGORY_ID,
345 X_SEQ_CATEGORY_ID,
346 X_SHIP_CATEGORY_ID,
347 X_STORAGE_CATEGORY_ID,
348 X_TAX_CATEGORY_ID,
349 X_ONT_PRICING_QTY_SOURCE, -- added for pricing by qty2 project
350 X_AUTOLOT_ACTIVE_INDICATOR,
351 X_LOT_PREFIX,
352 X_LOT_SUFFIX,
353 X_SUBLOT_PREFIX,
354 X_SUBLOT_SUFFIX,
355 X_CREATION_DATE,
356 X_CREATED_BY,
357 X_LAST_UPDATE_DATE,
358 X_LAST_UPDATED_BY,
359 X_LAST_UPDATE_LOGIN
360 );
361
362 insert into IC_ITEM_MST_TL (
363 ITEM_ID,
364 ITEM_DESC1,
365 ITEM_DESC2,
366 CREATION_DATE,
367 CREATED_BY,
368 LAST_UPDATE_DATE,
369 LAST_UPDATED_BY,
370 LAST_UPDATE_LOGIN,
371 LANGUAGE,
372 SOURCE_LANG
373 ) select
374 X_ITEM_ID,
375 X_ITEM_DESC1,
376 X_ITEM_DESC2,
377 X_CREATION_DATE,
378 X_CREATED_BY,
379 X_LAST_UPDATE_DATE,
380 X_LAST_UPDATED_BY,
381 X_LAST_UPDATE_LOGIN,
382 L.LANGUAGE_CODE,
383 userenv('LANG')
384 from FND_LANGUAGES L
385 where L.INSTALLED_FLAG in ('I', 'B')
386 and not exists
387 (select NULL
388 from IC_ITEM_MST_TL T
389 where T.ITEM_ID = X_ITEM_ID
390 and T.LANGUAGE = L.LANGUAGE_CODE);
391
392 open c;
393 fetch c into X_ROWID;
394 if (c%notfound) then
395 close c;
396 raise no_data_found;
397 end if;
398 close c;
399
400 end INSERT_ROW;
401
402 procedure LOCK_ROW (
403 X_ITEM_ID in NUMBER,
404 X_ITEM_NO in VARCHAR2,
405 X_ALT_ITEMA in VARCHAR2,
406 X_ALT_ITEMB in VARCHAR2,
407 X_ITEM_UM in VARCHAR2,
408 X_DUALUM_IND in NUMBER,
409 X_ITEM_UM2 in VARCHAR2,
410 X_DEVIATION_LO in NUMBER,
411 X_DEVIATION_HI in NUMBER,
412 X_LEVEL_CODE in NUMBER,
413 X_LOT_CTL in NUMBER,
414 X_LOT_INDIVISIBLE in NUMBER,
415 X_SUBLOT_CTL in NUMBER,
416 X_LOCT_CTL in NUMBER,
417 X_NONINV_IND in NUMBER,
418 X_MATCH_TYPE in NUMBER,
419 X_INACTIVE_IND in NUMBER,
420 X_INV_TYPE in VARCHAR2,
421 X_SHELF_LIFE in NUMBER,
422 X_RETEST_INTERVAL in NUMBER,
423 X_GL_CLASS in VARCHAR2,
424 X_INV_CLASS in VARCHAR2,
425 X_SALES_CLASS in VARCHAR2,
426 X_SHIP_CLASS in VARCHAR2,
427 X_FRT_CLASS in VARCHAR2,
428 X_PRICE_CLASS in VARCHAR2,
429 X_STORAGE_CLASS in VARCHAR2,
430 X_PURCH_CLASS in VARCHAR2,
431 X_TAX_CLASS in VARCHAR2,
432 X_CUSTOMS_CLASS in VARCHAR2,
433 X_ALLOC_CLASS in VARCHAR2,
434 X_PLANNING_CLASS in VARCHAR2,
435 X_ITEMCOST_CLASS in VARCHAR2,
436 X_COST_MTHD_CODE in VARCHAR2,
437 X_UPC_CODE in VARCHAR2,
438 X_GRADE_CTL in NUMBER,
439 X_STATUS_CTL in NUMBER,
440 X_QC_GRADE in VARCHAR2,
441 X_LOT_STATUS in VARCHAR2,
442 X_BULK_ID in NUMBER,
443 X_PKG_ID in NUMBER,
444 X_QCITEM_ID in NUMBER,
445 X_QCHOLD_RES_CODE in VARCHAR2,
446 X_EXPACTION_CODE in VARCHAR2,
447 X_FILL_QTY in NUMBER,
448 X_FILL_UM in VARCHAR2,
449 X_PLANNING_CATEGORY_ID in NUMBER,
450 X_PRICE_CATEGORY_ID in NUMBER,
451 X_EXPACTION_INTERVAL in NUMBER,
452 X_PHANTOM_TYPE in NUMBER,
453 X_WHSE_ITEM_ID in NUMBER,
454 X_EXPERIMENTAL_IND in NUMBER,
455 X_EXPORTED_DATE in DATE,
456 X_TRANS_CNT in NUMBER,
457 X_DELETE_MARK in NUMBER,
458 X_TEXT_CODE in NUMBER,
459 X_SEQ_DPND_CLASS in VARCHAR2,
460 X_COMMODITY_CODE in VARCHAR2,
461 X_REQUEST_ID in NUMBER,
462 X_ATTRIBUTE1 in VARCHAR2,
466 X_ATTRIBUTE5 in VARCHAR2,
463 X_ATTRIBUTE2 in VARCHAR2,
464 X_ATTRIBUTE3 in VARCHAR2,
465 X_ATTRIBUTE4 in VARCHAR2,
467 X_ATTRIBUTE6 in VARCHAR2,
468 X_ATTRIBUTE7 in VARCHAR2,
469 X_ATTRIBUTE8 in VARCHAR2,
470 X_ATTRIBUTE9 in VARCHAR2,
471 X_ATTRIBUTE10 in VARCHAR2,
472 X_ATTRIBUTE11 in VARCHAR2,
473 X_ATTRIBUTE12 in VARCHAR2,
474 X_ATTRIBUTE13 in VARCHAR2,
475 X_ATTRIBUTE14 in VARCHAR2,
476 X_ATTRIBUTE15 in VARCHAR2,
477 X_ATTRIBUTE16 in VARCHAR2,
478 X_ATTRIBUTE17 in VARCHAR2,
479 X_ATTRIBUTE18 in VARCHAR2,
480 X_ATTRIBUTE19 in VARCHAR2,
481 X_ATTRIBUTE20 in VARCHAR2,
482 X_ATTRIBUTE21 in VARCHAR2,
483 X_ATTRIBUTE22 in VARCHAR2,
484 X_ATTRIBUTE23 in VARCHAR2,
485 X_ATTRIBUTE24 in VARCHAR2,
486 X_ATTRIBUTE25 in VARCHAR2,
487 X_ATTRIBUTE26 in VARCHAR2,
488 X_ATTRIBUTE27 in VARCHAR2,
489 X_ATTRIBUTE28 in VARCHAR2,
490 X_ATTRIBUTE29 in VARCHAR2,
491 X_ATTRIBUTE30 in VARCHAR2,
492 X_ATTRIBUTE_CATEGORY in VARCHAR2,
493 X_ITEM_ABCCODE in VARCHAR2,
494 X_ALLOC_CATEGORY_ID in NUMBER,
495 X_CUSTOMS_CATEGORY_ID in NUMBER,
496 X_FRT_CATEGORY_ID in NUMBER,
497 X_GL_CATEGORY_ID in NUMBER,
498 X_INV_CATEGORY_ID in NUMBER,
499 X_COST_CATEGORY_ID in NUMBER,
500 X_PURCH_CATEGORY_ID in NUMBER,
501 X_SALES_CATEGORY_ID in NUMBER,
502 X_SEQ_CATEGORY_ID in NUMBER,
503 X_SHIP_CATEGORY_ID in NUMBER,
504 X_STORAGE_CATEGORY_ID in NUMBER,
505 X_TAX_CATEGORY_ID in NUMBER,
506 X_ITEM_DESC1 in VARCHAR2,
507 X_ITEM_DESC2 in VARCHAR2,
508 X_ONT_PRICING_QTY_SOURCE in NUMBER, -- added for pricing by qty2 project
509 X_AUTOLOT_ACTIVE_INDICATOR in NUMBER DEFAULT 0,
510 X_LOT_PREFIX in VARCHAR2 DEFAULT NULL,
511 X_LOT_SUFFIX in NUMBER DEFAULT 0,
512 X_SUBLOT_PREFIX in VARCHAR2 DEFAULT NULL,
513 X_SUBLOT_SUFFIX in NUMBER DEFAULT 0
514 ) is
515 cursor c is select
516 ITEM_NO,
517 ALT_ITEMA,
518 ALT_ITEMB,
519 ITEM_UM,
520 DUALUM_IND,
521 ITEM_UM2,
522 DEVIATION_LO,
523 DEVIATION_HI,
524 LEVEL_CODE,
525 LOT_CTL,
526 LOT_INDIVISIBLE,
527 SUBLOT_CTL,
528 LOCT_CTL,
529 NONINV_IND,
530 MATCH_TYPE,
531 INACTIVE_IND,
532 INV_TYPE,
533 SHELF_LIFE,
534 RETEST_INTERVAL,
535 GL_CLASS,
536 INV_CLASS,
537 SALES_CLASS,
538 SHIP_CLASS,
539 FRT_CLASS,
540 PRICE_CLASS,
541 STORAGE_CLASS,
542 PURCH_CLASS,
543 TAX_CLASS,
544 CUSTOMS_CLASS,
545 ALLOC_CLASS,
546 PLANNING_CLASS,
547 ITEMCOST_CLASS,
548 COST_MTHD_CODE,
549 UPC_CODE,
550 GRADE_CTL,
551 STATUS_CTL,
552 QC_GRADE,
553 LOT_STATUS,
554 BULK_ID,
555 PKG_ID,
556 QCITEM_ID,
557 QCHOLD_RES_CODE,
558 EXPACTION_CODE,
559 FILL_QTY,
560 FILL_UM,
561 PLANNING_CATEGORY_ID,
562 PRICE_CATEGORY_ID,
563 EXPACTION_INTERVAL,
564 PHANTOM_TYPE,
565 WHSE_ITEM_ID,
566 EXPERIMENTAL_IND,
567 EXPORTED_DATE,
568 TRANS_CNT,
569 DELETE_MARK,
570 TEXT_CODE,
571 SEQ_DPND_CLASS,
572 COMMODITY_CODE,
573 REQUEST_ID,
574 ATTRIBUTE1,
575 ATTRIBUTE2,
576 ATTRIBUTE3,
577 ATTRIBUTE4,
578 ATTRIBUTE5,
579 ATTRIBUTE6,
580 ATTRIBUTE7,
581 ATTRIBUTE8,
582 ATTRIBUTE9,
583 ATTRIBUTE10,
584 ATTRIBUTE11,
585 ATTRIBUTE12,
586 ATTRIBUTE13,
587 ATTRIBUTE14,
588 ATTRIBUTE15,
589 ATTRIBUTE16,
590 ATTRIBUTE17,
591 ATTRIBUTE18,
592 ATTRIBUTE19,
593 ATTRIBUTE20,
594 ATTRIBUTE21,
595 ATTRIBUTE22,
596 ATTRIBUTE23,
597 ATTRIBUTE24,
598 ATTRIBUTE25,
599 ATTRIBUTE26,
600 ATTRIBUTE27,
601 ATTRIBUTE28,
602 ATTRIBUTE29,
603 ATTRIBUTE30,
604 ATTRIBUTE_CATEGORY,
605 ITEM_ABCCODE,
606 ALLOC_CATEGORY_ID,
607 CUSTOMS_CATEGORY_ID,
608 FRT_CATEGORY_ID,
609 GL_CATEGORY_ID,
610 INV_CATEGORY_ID,
611 COST_CATEGORY_ID,
612 PURCH_CATEGORY_ID,
613 SALES_CATEGORY_ID,
614 SEQ_CATEGORY_ID,
615 SHIP_CATEGORY_ID,
616 STORAGE_CATEGORY_ID,
617 TAX_CATEGORY_ID,
618 ONT_PRICING_QTY_SOURCE, -- added for pricing by qty2 project
619 AUTOLOT_ACTIVE_INDICATOR,
620 LOT_PREFIX,
621 LOT_SUFFIX,
622 SUBLOT_PREFIX,
623 SUBLOT_SUFFIX
624 from IC_ITEM_MST_B
625 where ITEM_ID = X_ITEM_ID
626 for update of ITEM_ID nowait;
627 recinfo c%rowtype;
628
629 cursor c1 is select
630 ITEM_DESC1,
631 ITEM_DESC2,
632 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
633 from IC_ITEM_MST_TL
634 where ITEM_ID = X_ITEM_ID
635 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
636 for update of ITEM_ID nowait;
637 begin
638 open c;
639 fetch c into recinfo;
640 if (c%notfound) then
644 end if;
641 close c;
642 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
643 app_exception.raise_exception;
645 close c;
646 if ( (recinfo.ITEM_NO = X_ITEM_NO)
647 AND ((recinfo.ALT_ITEMA = X_ALT_ITEMA)
648 OR ((recinfo.ALT_ITEMA is null) AND (X_ALT_ITEMA is null)))
649 AND ((recinfo.ALT_ITEMB = X_ALT_ITEMB)
650 OR ((recinfo.ALT_ITEMB is null) AND (X_ALT_ITEMB is null)))
651 AND (recinfo.ITEM_UM = X_ITEM_UM)
652 AND (recinfo.DUALUM_IND = X_DUALUM_IND)
653 AND ((recinfo.ITEM_UM2 = X_ITEM_UM2)
654 OR ((recinfo.ITEM_UM2 is null) AND (X_ITEM_UM2 is null)))
655 AND (recinfo.DEVIATION_LO = X_DEVIATION_LO)
656 AND (recinfo.DEVIATION_HI = X_DEVIATION_HI)
657 AND ((recinfo.LEVEL_CODE = X_LEVEL_CODE)
658 OR ((recinfo.LEVEL_CODE is null) AND (X_LEVEL_CODE is null)))
659 AND (recinfo.LOT_CTL = X_LOT_CTL)
660 AND (recinfo.LOT_INDIVISIBLE = X_LOT_INDIVISIBLE)
661 AND (recinfo.SUBLOT_CTL = X_SUBLOT_CTL)
662 AND (recinfo.LOCT_CTL = X_LOCT_CTL)
663 AND (recinfo.NONINV_IND = X_NONINV_IND)
664 AND (recinfo.MATCH_TYPE = X_MATCH_TYPE)
665 AND (recinfo.INACTIVE_IND = X_INACTIVE_IND)
666 AND ((recinfo.INV_TYPE = X_INV_TYPE)
667 OR ((recinfo.INV_TYPE is null) AND (X_INV_TYPE is null)))
668 AND (recinfo.SHELF_LIFE = X_SHELF_LIFE)
669 AND (recinfo.RETEST_INTERVAL = X_RETEST_INTERVAL)
670 AND ((recinfo.GL_CLASS = X_GL_CLASS)
671 OR ((recinfo.GL_CLASS is null) AND (X_GL_CLASS is null)))
672 AND ((recinfo.INV_CLASS = X_INV_CLASS)
673 OR ((recinfo.INV_CLASS is null) AND (X_INV_CLASS is null)))
674 AND ((recinfo.SALES_CLASS = X_SALES_CLASS)
675 OR ((recinfo.SALES_CLASS is null) AND (X_SALES_CLASS is null)))
676 AND ((recinfo.SHIP_CLASS = X_SHIP_CLASS)
677 OR ((recinfo.SHIP_CLASS is null) AND (X_SHIP_CLASS is null)))
678 AND ((recinfo.FRT_CLASS = X_FRT_CLASS)
679 OR ((recinfo.FRT_CLASS is null) AND (X_FRT_CLASS is null)))
680 AND ((recinfo.PRICE_CLASS = X_PRICE_CLASS)
681 OR ((recinfo.PRICE_CLASS is null) AND (X_PRICE_CLASS is null)))
682 AND ((recinfo.STORAGE_CLASS = X_STORAGE_CLASS)
683 OR ((recinfo.STORAGE_CLASS is null) AND (X_STORAGE_CLASS is null)))
684 AND ((recinfo.PURCH_CLASS = X_PURCH_CLASS)
685 OR ((recinfo.PURCH_CLASS is null) AND (X_PURCH_CLASS is null)))
686 AND ((recinfo.TAX_CLASS = X_TAX_CLASS)
687 OR ((recinfo.TAX_CLASS is null) AND (X_TAX_CLASS is null)))
688 AND ((recinfo.CUSTOMS_CLASS = X_CUSTOMS_CLASS)
689 OR ((recinfo.CUSTOMS_CLASS is null) AND (X_CUSTOMS_CLASS is null)))
690 AND ((recinfo.ALLOC_CLASS = X_ALLOC_CLASS)
691 OR ((recinfo.ALLOC_CLASS is null) AND (X_ALLOC_CLASS is null)))
692 AND ((recinfo.PLANNING_CLASS = X_PLANNING_CLASS)
693 OR ((recinfo.PLANNING_CLASS is null) AND (X_PLANNING_CLASS is null)))
694 AND ((recinfo.ITEMCOST_CLASS = X_ITEMCOST_CLASS)
695 OR ((recinfo.ITEMCOST_CLASS is null) AND (X_ITEMCOST_CLASS is null)))
696 AND ((recinfo.COST_MTHD_CODE = X_COST_MTHD_CODE)
697 OR ((recinfo.COST_MTHD_CODE is null) AND (X_COST_MTHD_CODE is null)))
698 AND ((recinfo.UPC_CODE = X_UPC_CODE)
699 OR ((recinfo.UPC_CODE is null) AND (X_UPC_CODE is null)))
700 AND (recinfo.GRADE_CTL = X_GRADE_CTL)
701 AND (recinfo.STATUS_CTL = X_STATUS_CTL)
702 AND ((recinfo.QC_GRADE = X_QC_GRADE)
703 OR ((recinfo.QC_GRADE is null) AND (X_QC_GRADE is null)))
704 AND ((recinfo.LOT_STATUS = X_LOT_STATUS)
705 OR ((recinfo.LOT_STATUS is null) AND (X_LOT_STATUS is null)))
706 AND ((recinfo.BULK_ID = X_BULK_ID)
707 OR ((recinfo.BULK_ID is null) AND (X_BULK_ID is null)))
708 AND ((recinfo.PKG_ID = X_PKG_ID)
709 OR ((recinfo.PKG_ID is null) AND (X_PKG_ID is null)))
710 AND ((recinfo.QCITEM_ID = X_QCITEM_ID)
711 OR ((recinfo.QCITEM_ID is null) AND (X_QCITEM_ID is null)))
712 AND ((recinfo.QCHOLD_RES_CODE = X_QCHOLD_RES_CODE)
713 OR ((recinfo.QCHOLD_RES_CODE is null) AND (X_QCHOLD_RES_CODE is null)))
714 AND ((recinfo.EXPACTION_CODE = X_EXPACTION_CODE)
715 OR ((recinfo.EXPACTION_CODE is null) AND (X_EXPACTION_CODE is null)))
716 AND (recinfo.FILL_QTY = X_FILL_QTY)
717 AND ((recinfo.FILL_UM = X_FILL_UM)
718 OR ((recinfo.FILL_UM is null) AND (X_FILL_UM is null)))
719 AND ((recinfo.PLANNING_CATEGORY_ID = X_PLANNING_CATEGORY_ID)
720 OR ((recinfo.PLANNING_CATEGORY_ID is null) AND (X_PLANNING_CATEGORY_ID is null)))
721 AND ((recinfo.PRICE_CATEGORY_ID = X_PRICE_CATEGORY_ID)
722 OR ((recinfo.PRICE_CATEGORY_ID is null) AND (X_PRICE_CATEGORY_ID is null)))
723 AND (recinfo.EXPACTION_INTERVAL = X_EXPACTION_INTERVAL)
724 AND (recinfo.PHANTOM_TYPE = X_PHANTOM_TYPE)
725 AND (recinfo.WHSE_ITEM_ID = X_WHSE_ITEM_ID)
726 AND (recinfo.EXPERIMENTAL_IND = X_EXPERIMENTAL_IND)
727 AND (recinfo.EXPORTED_DATE = X_EXPORTED_DATE)
728 AND ((recinfo.TRANS_CNT = X_TRANS_CNT)
729 OR ((recinfo.TRANS_CNT is null) AND (X_TRANS_CNT is null)))
730 AND (recinfo.DELETE_MARK = X_DELETE_MARK)
731 AND ((recinfo.TEXT_CODE = X_TEXT_CODE)
732 OR ((recinfo.TEXT_CODE is null) AND (X_TEXT_CODE is null)))
733 AND ((recinfo.SEQ_DPND_CLASS = X_SEQ_DPND_CLASS)
734 OR ((recinfo.SEQ_DPND_CLASS is null) AND (X_SEQ_DPND_CLASS is null)))
735 AND ((recinfo.COMMODITY_CODE = X_COMMODITY_CODE)
739 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
736 OR ((recinfo.COMMODITY_CODE is null) AND (X_COMMODITY_CODE is null)))
737 AND ((recinfo.REQUEST_ID = X_REQUEST_ID)
738 OR ((recinfo.REQUEST_ID is null) AND (X_REQUEST_ID is null)))
740 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
741 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
742 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
743 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
744 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
745 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
746 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
747 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
748 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
749 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
750 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
751 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
752 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
753 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
754 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
755 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
756 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
757 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
758 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
759 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
760 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
761 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
762 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
763 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
764 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
765 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
766 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
767 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
768 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
769 AND ((recinfo.ATTRIBUTE16 = X_ATTRIBUTE16)
770 OR ((recinfo.ATTRIBUTE16 is null) AND (X_ATTRIBUTE16 is null)))
771 AND ((recinfo.ATTRIBUTE17 = X_ATTRIBUTE17)
772 OR ((recinfo.ATTRIBUTE17 is null) AND (X_ATTRIBUTE17 is null)))
773 AND ((recinfo.ATTRIBUTE18 = X_ATTRIBUTE18)
774 OR ((recinfo.ATTRIBUTE18 is null) AND (X_ATTRIBUTE18 is null)))
775 AND ((recinfo.ATTRIBUTE19 = X_ATTRIBUTE19)
776 OR ((recinfo.ATTRIBUTE19 is null) AND (X_ATTRIBUTE19 is null)))
777 AND ((recinfo.ATTRIBUTE20 = X_ATTRIBUTE20)
778 OR ((recinfo.ATTRIBUTE20 is null) AND (X_ATTRIBUTE20 is null)))
779 AND ((recinfo.ATTRIBUTE21 = X_ATTRIBUTE21)
780 OR ((recinfo.ATTRIBUTE21 is null) AND (X_ATTRIBUTE21 is null)))
781 AND ((recinfo.ATTRIBUTE22 = X_ATTRIBUTE22)
782 OR ((recinfo.ATTRIBUTE22 is null) AND (X_ATTRIBUTE22 is null)))
783 AND ((recinfo.ATTRIBUTE23 = X_ATTRIBUTE23)
784 OR ((recinfo.ATTRIBUTE23 is null) AND (X_ATTRIBUTE23 is null)))
785 AND ((recinfo.ATTRIBUTE24 = X_ATTRIBUTE24)
786 OR ((recinfo.ATTRIBUTE24 is null) AND (X_ATTRIBUTE24 is null)))
787 AND ((recinfo.ATTRIBUTE25 = X_ATTRIBUTE25)
788 OR ((recinfo.ATTRIBUTE25 is null) AND (X_ATTRIBUTE25 is null)))
789 AND ((recinfo.ATTRIBUTE26 = X_ATTRIBUTE26)
790 OR ((recinfo.ATTRIBUTE26 is null) AND (X_ATTRIBUTE26 is null)))
791 AND ((recinfo.ATTRIBUTE27 = X_ATTRIBUTE27)
792 OR ((recinfo.ATTRIBUTE27 is null) AND (X_ATTRIBUTE27 is null)))
793 AND ((recinfo.ATTRIBUTE28 = X_ATTRIBUTE28)
794 OR ((recinfo.ATTRIBUTE28 is null) AND (X_ATTRIBUTE28 is null)))
795 AND ((recinfo.ATTRIBUTE29 = X_ATTRIBUTE29)
796 OR ((recinfo.ATTRIBUTE29 is null) AND (X_ATTRIBUTE29 is null)))
797 AND ((recinfo.ATTRIBUTE30 = X_ATTRIBUTE30)
798 OR ((recinfo.ATTRIBUTE30 is null) AND (X_ATTRIBUTE30 is null)))
799 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
800 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
801 AND ((recinfo.ITEM_ABCCODE = X_ITEM_ABCCODE)
802 OR ((recinfo.ITEM_ABCCODE is null) AND (X_ITEM_ABCCODE is null)))
803 AND ((recinfo.ALLOC_CATEGORY_ID = X_ALLOC_CATEGORY_ID)
804 OR ((recinfo.ALLOC_CATEGORY_ID is null) AND (X_ALLOC_CATEGORY_ID is null)))
805 AND ((recinfo.CUSTOMS_CATEGORY_ID = X_CUSTOMS_CATEGORY_ID)
806 OR ((recinfo.CUSTOMS_CATEGORY_ID is null) AND (X_CUSTOMS_CATEGORY_ID is null)))
807 AND ((recinfo.FRT_CATEGORY_ID = X_FRT_CATEGORY_ID)
808 OR ((recinfo.FRT_CATEGORY_ID is null) AND (X_FRT_CATEGORY_ID is null)))
809 AND ((recinfo.GL_CATEGORY_ID = X_GL_CATEGORY_ID)
810 OR ((recinfo.GL_CATEGORY_ID is null) AND (X_GL_CATEGORY_ID is null)))
811 AND ((recinfo.INV_CATEGORY_ID = X_INV_CATEGORY_ID)
812 OR ((recinfo.INV_CATEGORY_ID is null) AND (X_INV_CATEGORY_ID is null)))
813 AND ((recinfo.COST_CATEGORY_ID = X_COST_CATEGORY_ID)
814 OR ((recinfo.COST_CATEGORY_ID is null) AND (X_COST_CATEGORY_ID is null)))
815 AND ((recinfo.PURCH_CATEGORY_ID = X_PURCH_CATEGORY_ID)
816 OR ((recinfo.PURCH_CATEGORY_ID is null) AND (X_PURCH_CATEGORY_ID is null)))
817 AND ((recinfo.SALES_CATEGORY_ID = X_SALES_CATEGORY_ID)
821 AND ((recinfo.SHIP_CATEGORY_ID = X_SHIP_CATEGORY_ID)
818 OR ((recinfo.SALES_CATEGORY_ID is null) AND (X_SALES_CATEGORY_ID is null)))
819 AND ((recinfo.SEQ_CATEGORY_ID = X_SEQ_CATEGORY_ID)
820 OR ((recinfo.SEQ_CATEGORY_ID is null) AND (X_SEQ_CATEGORY_ID is null)))
822 OR ((recinfo.SHIP_CATEGORY_ID is null) AND (X_SHIP_CATEGORY_ID is null)))
823 AND ((recinfo.STORAGE_CATEGORY_ID = X_STORAGE_CATEGORY_ID)
824 OR ((recinfo.STORAGE_CATEGORY_ID is null) AND (X_STORAGE_CATEGORY_ID is null)))
825 AND ((recinfo.TAX_CATEGORY_ID = X_TAX_CATEGORY_ID)
826 OR ((recinfo.TAX_CATEGORY_ID is null) AND (X_TAX_CATEGORY_ID is null)))
827 AND ((recinfo.ONT_PRICING_QTY_SOURCE = X_ONT_PRICING_QTY_SOURCE) -- added for pricing by qty2 project
828 OR ((recinfo.ONT_PRICING_QTY_SOURCE is null) AND (X_ONT_PRICING_QTY_SOURCE is null)))
829 AND ((recinfo.AUTOLOT_ACTIVE_INDICATOR = X_AUTOLOT_ACTIVE_INDICATOR)
830 OR ((recinfo.AUTOLOT_ACTIVE_INDICATOR is null) AND (X_AUTOLOT_ACTIVE_INDICATOR is null)))
831 AND ((recinfo.LOT_PREFIX = X_LOT_PREFIX)
832 OR ((recinfo.LOT_PREFIX is null) AND (X_LOT_PREFIX is null)))
833 AND ((recinfo.LOT_SUFFIX = X_LOT_SUFFIX)
834 OR ((recinfo.LOT_SUFFIX is null) AND (X_LOT_SUFFIX is null)))
835 AND ((recinfo.SUBLOT_PREFIX = X_SUBLOT_PREFIX)
836 OR ((recinfo.SUBLOT_PREFIX is null) AND (X_SUBLOT_PREFIX is null)))
837 AND ((recinfo.SUBLOT_SUFFIX = X_SUBLOT_SUFFIX)
838 OR ((recinfo.SUBLOT_SUFFIX is null) AND (X_SUBLOT_SUFFIX is null)))
839 ) then
840 null;
841 else
842 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
843 app_exception.raise_exception;
844 end if;
845
846 for tlinfo in c1 loop
847 if (tlinfo.BASELANG = 'Y') then
848 if ( (tlinfo.ITEM_DESC1 = X_ITEM_DESC1)
849 AND ((tlinfo.ITEM_DESC2 = X_ITEM_DESC2)
850 OR ((tlinfo.ITEM_DESC2 is null) AND (X_ITEM_DESC2 is null)))
851 ) then
852 null;
853 else
854 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
855 app_exception.raise_exception;
856 end if;
857 end if;
858 end loop;
859 return;
860 end LOCK_ROW;
861
862 procedure UPDATE_ROW (
863 X_ITEM_ID in NUMBER,
864 X_ITEM_NO in VARCHAR2,
865 X_ALT_ITEMA in VARCHAR2,
866 X_ALT_ITEMB in VARCHAR2,
867 X_ITEM_UM in VARCHAR2,
868 X_DUALUM_IND in NUMBER,
869 X_ITEM_UM2 in VARCHAR2,
870 X_DEVIATION_LO in NUMBER,
871 X_DEVIATION_HI in NUMBER,
872 X_LEVEL_CODE in NUMBER,
873 X_LOT_CTL in NUMBER,
874 X_LOT_INDIVISIBLE in NUMBER,
875 X_SUBLOT_CTL in NUMBER,
876 X_LOCT_CTL in NUMBER,
877 X_NONINV_IND in NUMBER,
878 X_MATCH_TYPE in NUMBER,
879 X_INACTIVE_IND in NUMBER,
880 X_INV_TYPE in VARCHAR2,
881 X_SHELF_LIFE in NUMBER,
882 X_RETEST_INTERVAL in NUMBER,
883 X_GL_CLASS in VARCHAR2,
884 X_INV_CLASS in VARCHAR2,
885 X_SALES_CLASS in VARCHAR2,
886 X_SHIP_CLASS in VARCHAR2,
887 X_FRT_CLASS in VARCHAR2,
888 X_PRICE_CLASS in VARCHAR2,
889 X_STORAGE_CLASS in VARCHAR2,
890 X_PURCH_CLASS in VARCHAR2,
891 X_TAX_CLASS in VARCHAR2,
892 X_CUSTOMS_CLASS in VARCHAR2,
893 X_ALLOC_CLASS in VARCHAR2,
894 X_PLANNING_CLASS in VARCHAR2,
895 X_ITEMCOST_CLASS in VARCHAR2,
896 X_COST_MTHD_CODE in VARCHAR2,
897 X_UPC_CODE in VARCHAR2,
898 X_GRADE_CTL in NUMBER,
899 X_STATUS_CTL in NUMBER,
900 X_QC_GRADE in VARCHAR2,
901 X_LOT_STATUS in VARCHAR2,
902 X_BULK_ID in NUMBER,
903 X_PKG_ID in NUMBER,
904 X_QCITEM_ID in NUMBER,
905 X_QCHOLD_RES_CODE in VARCHAR2,
906 X_EXPACTION_CODE in VARCHAR2,
907 X_FILL_QTY in NUMBER,
908 X_FILL_UM in VARCHAR2,
909 X_PLANNING_CATEGORY_ID in NUMBER,
910 X_PRICE_CATEGORY_ID in NUMBER,
911 X_EXPACTION_INTERVAL in NUMBER,
912 X_PHANTOM_TYPE in NUMBER,
913 X_WHSE_ITEM_ID in NUMBER,
914 X_EXPERIMENTAL_IND in NUMBER,
915 X_EXPORTED_DATE in DATE,
916 X_TRANS_CNT in NUMBER,
917 X_DELETE_MARK in NUMBER,
918 X_TEXT_CODE in NUMBER,
919 X_SEQ_DPND_CLASS in VARCHAR2,
920 X_COMMODITY_CODE in VARCHAR2,
921 X_REQUEST_ID in NUMBER,
922 X_ATTRIBUTE1 in VARCHAR2,
923 X_ATTRIBUTE2 in VARCHAR2,
924 X_ATTRIBUTE3 in VARCHAR2,
925 X_ATTRIBUTE4 in VARCHAR2,
926 X_ATTRIBUTE5 in VARCHAR2,
927 X_ATTRIBUTE6 in VARCHAR2,
928 X_ATTRIBUTE7 in VARCHAR2,
929 X_ATTRIBUTE8 in VARCHAR2,
930 X_ATTRIBUTE9 in VARCHAR2,
931 X_ATTRIBUTE10 in VARCHAR2,
932 X_ATTRIBUTE11 in VARCHAR2,
933 X_ATTRIBUTE12 in VARCHAR2,
934 X_ATTRIBUTE13 in VARCHAR2,
935 X_ATTRIBUTE14 in VARCHAR2,
936 X_ATTRIBUTE15 in VARCHAR2,
937 X_ATTRIBUTE16 in VARCHAR2,
938 X_ATTRIBUTE17 in VARCHAR2,
939 X_ATTRIBUTE18 in VARCHAR2,
940 X_ATTRIBUTE19 in VARCHAR2,
941 X_ATTRIBUTE20 in VARCHAR2,
942 X_ATTRIBUTE21 in VARCHAR2,
943 X_ATTRIBUTE22 in VARCHAR2,
944 X_ATTRIBUTE23 in VARCHAR2,
945 X_ATTRIBUTE24 in VARCHAR2,
946 X_ATTRIBUTE25 in VARCHAR2,
947 X_ATTRIBUTE26 in VARCHAR2,
948 X_ATTRIBUTE27 in VARCHAR2,
952 X_ATTRIBUTE_CATEGORY in VARCHAR2,
949 X_ATTRIBUTE28 in VARCHAR2,
950 X_ATTRIBUTE29 in VARCHAR2,
951 X_ATTRIBUTE30 in VARCHAR2,
953 X_ITEM_ABCCODE in VARCHAR2,
954 X_ALLOC_CATEGORY_ID in NUMBER,
955 X_CUSTOMS_CATEGORY_ID in NUMBER,
956 X_FRT_CATEGORY_ID in NUMBER,
957 X_GL_CATEGORY_ID in NUMBER,
958 X_INV_CATEGORY_ID in NUMBER,
959 X_COST_CATEGORY_ID in NUMBER,
960 X_PURCH_CATEGORY_ID in NUMBER,
961 X_SALES_CATEGORY_ID in NUMBER,
962 X_SEQ_CATEGORY_ID in NUMBER,
963 X_SHIP_CATEGORY_ID in NUMBER,
964 X_STORAGE_CATEGORY_ID in NUMBER,
965 X_TAX_CATEGORY_ID in NUMBER,
966 X_ITEM_DESC1 in VARCHAR2,
967 X_ITEM_DESC2 in VARCHAR2,
968 X_ONT_PRICING_QTY_SOURCE in NUMBER, -- added for pricing by qty2 project
969 X_AUTOLOT_ACTIVE_INDICATOR in NUMBER DEFAULT 0,
970 X_LOT_PREFIX in VARCHAR2 DEFAULT NULL,
971 X_LOT_SUFFIX in NUMBER DEFAULT 0,
972 X_SUBLOT_PREFIX in VARCHAR2 DEFAULT NULL,
973 X_SUBLOT_SUFFIX in NUMBER DEFAULT 0,
974 X_LAST_UPDATE_DATE in DATE,
975 X_LAST_UPDATED_BY in NUMBER,
976 X_LAST_UPDATE_LOGIN in NUMBER
977 ) is
978 begin
979 update IC_ITEM_MST_B set
980 ITEM_NO = X_ITEM_NO,
981 ITEM_DESC1 = X_ITEM_DESC1,
982 ITEM_DESC2 = X_ITEM_DESC2,
983 ALT_ITEMA = X_ALT_ITEMA,
984 ALT_ITEMB = X_ALT_ITEMB,
985 ITEM_UM = X_ITEM_UM,
986 DUALUM_IND = X_DUALUM_IND,
987 ITEM_UM2 = X_ITEM_UM2,
988 DEVIATION_LO = X_DEVIATION_LO,
989 DEVIATION_HI = X_DEVIATION_HI,
990 LEVEL_CODE = X_LEVEL_CODE,
991 LOT_CTL = X_LOT_CTL,
992 LOT_INDIVISIBLE = X_LOT_INDIVISIBLE,
993 SUBLOT_CTL = X_SUBLOT_CTL,
994 LOCT_CTL = X_LOCT_CTL,
995 NONINV_IND = X_NONINV_IND,
996 MATCH_TYPE = X_MATCH_TYPE,
997 INACTIVE_IND = X_INACTIVE_IND,
998 INV_TYPE = X_INV_TYPE,
999 SHELF_LIFE = X_SHELF_LIFE,
1000 RETEST_INTERVAL = X_RETEST_INTERVAL,
1001 GL_CLASS = X_GL_CLASS,
1002 INV_CLASS = X_INV_CLASS,
1003 SALES_CLASS = X_SALES_CLASS,
1004 SHIP_CLASS = X_SHIP_CLASS,
1005 FRT_CLASS = X_FRT_CLASS,
1006 PRICE_CLASS = X_PRICE_CLASS,
1007 STORAGE_CLASS = X_STORAGE_CLASS,
1008 PURCH_CLASS = X_PURCH_CLASS,
1009 TAX_CLASS = X_TAX_CLASS,
1010 CUSTOMS_CLASS = X_CUSTOMS_CLASS,
1011 ALLOC_CLASS = X_ALLOC_CLASS,
1012 PLANNING_CLASS = X_PLANNING_CLASS,
1013 ITEMCOST_CLASS = X_ITEMCOST_CLASS,
1014 COST_MTHD_CODE = X_COST_MTHD_CODE,
1015 UPC_CODE = X_UPC_CODE,
1016 GRADE_CTL = X_GRADE_CTL,
1017 STATUS_CTL = X_STATUS_CTL,
1018 QC_GRADE = X_QC_GRADE,
1019 LOT_STATUS = X_LOT_STATUS,
1020 BULK_ID = X_BULK_ID,
1021 PKG_ID = X_PKG_ID,
1022 QCITEM_ID = X_QCITEM_ID,
1023 QCHOLD_RES_CODE = X_QCHOLD_RES_CODE,
1024 EXPACTION_CODE = X_EXPACTION_CODE,
1025 FILL_QTY = X_FILL_QTY,
1026 FILL_UM = X_FILL_UM,
1027 PLANNING_CATEGORY_ID = X_PLANNING_CATEGORY_ID,
1028 PRICE_CATEGORY_ID = X_PRICE_CATEGORY_ID,
1029 EXPACTION_INTERVAL = X_EXPACTION_INTERVAL,
1030 PHANTOM_TYPE = X_PHANTOM_TYPE,
1031 WHSE_ITEM_ID = X_WHSE_ITEM_ID,
1032 EXPERIMENTAL_IND = X_EXPERIMENTAL_IND,
1033 EXPORTED_DATE = X_EXPORTED_DATE,
1034 TRANS_CNT = X_TRANS_CNT,
1035 DELETE_MARK = X_DELETE_MARK,
1036 TEXT_CODE = X_TEXT_CODE,
1037 SEQ_DPND_CLASS = X_SEQ_DPND_CLASS,
1038 COMMODITY_CODE = X_COMMODITY_CODE,
1039 REQUEST_ID = X_REQUEST_ID,
1040 ATTRIBUTE1 = X_ATTRIBUTE1,
1041 ATTRIBUTE2 = X_ATTRIBUTE2,
1042 ATTRIBUTE3 = X_ATTRIBUTE3,
1043 ATTRIBUTE4 = X_ATTRIBUTE4,
1044 ATTRIBUTE5 = X_ATTRIBUTE5,
1045 ATTRIBUTE6 = X_ATTRIBUTE6,
1046 ATTRIBUTE7 = X_ATTRIBUTE7,
1047 ATTRIBUTE8 = X_ATTRIBUTE8,
1048 ATTRIBUTE9 = X_ATTRIBUTE9,
1049 ATTRIBUTE10 = X_ATTRIBUTE10,
1050 ATTRIBUTE11 = X_ATTRIBUTE11,
1051 ATTRIBUTE12 = X_ATTRIBUTE12,
1052 ATTRIBUTE13 = X_ATTRIBUTE13,
1053 ATTRIBUTE14 = X_ATTRIBUTE14,
1054 ATTRIBUTE15 = X_ATTRIBUTE15,
1055 ATTRIBUTE16 = X_ATTRIBUTE16,
1056 ATTRIBUTE17 = X_ATTRIBUTE17,
1057 ATTRIBUTE18 = X_ATTRIBUTE18,
1058 ATTRIBUTE19 = X_ATTRIBUTE19,
1059 ATTRIBUTE20 = X_ATTRIBUTE20,
1060 ATTRIBUTE21 = X_ATTRIBUTE21,
1061 ATTRIBUTE22 = X_ATTRIBUTE22,
1062 ATTRIBUTE23 = X_ATTRIBUTE23,
1063 ATTRIBUTE24 = X_ATTRIBUTE24,
1064 ATTRIBUTE25 = X_ATTRIBUTE25,
1065 ATTRIBUTE26 = X_ATTRIBUTE26,
1066 ATTRIBUTE27 = X_ATTRIBUTE27,
1067 ATTRIBUTE28 = X_ATTRIBUTE28,
1068 ATTRIBUTE29 = X_ATTRIBUTE29,
1069 ATTRIBUTE30 = X_ATTRIBUTE30,
1070 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
1071 ITEM_ABCCODE = X_ITEM_ABCCODE,
1072 ALLOC_CATEGORY_ID = X_ALLOC_CATEGORY_ID,
1073 CUSTOMS_CATEGORY_ID = X_CUSTOMS_CATEGORY_ID,
1074 FRT_CATEGORY_ID = X_FRT_CATEGORY_ID,
1075 GL_CATEGORY_ID = X_GL_CATEGORY_ID,
1076 INV_CATEGORY_ID = X_INV_CATEGORY_ID,
1077 COST_CATEGORY_ID = X_COST_CATEGORY_ID,
1078 PURCH_CATEGORY_ID = X_PURCH_CATEGORY_ID,
1079 SALES_CATEGORY_ID = X_SALES_CATEGORY_ID,
1080 SEQ_CATEGORY_ID = X_SEQ_CATEGORY_ID,
1081 SHIP_CATEGORY_ID = X_SHIP_CATEGORY_ID,
1082 STORAGE_CATEGORY_ID = X_STORAGE_CATEGORY_ID,
1083 ONT_PRICING_QTY_SOURCE = X_ONT_PRICING_QTY_SOURCE, -- added for pricing by qty2 project
1087 SUBLOT_PREFIX = X_SUBLOT_PREFIX,
1084 AUTOLOT_ACTIVE_INDICATOR = X_AUTOLOT_ACTIVE_INDICATOR,
1085 LOT_PREFIX = X_LOT_PREFIX,
1086 LOT_SUFFIX = X_LOT_SUFFIX,
1088 SUBLOT_SUFFIX = X_SUBLOT_SUFFIX,
1089 TAX_CATEGORY_ID = X_TAX_CATEGORY_ID,
1090 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1091 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1092 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
1093 where ITEM_ID = X_ITEM_ID;
1094
1095 if (sql%notfound) then
1096 raise no_data_found;
1097 end if;
1098
1099 update IC_ITEM_MST_TL set
1100 ITEM_DESC1 = X_ITEM_DESC1,
1101 ITEM_DESC2 = X_ITEM_DESC2,
1102 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1103 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1104 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
1105 SOURCE_LANG = userenv('LANG')
1106 where ITEM_ID = X_ITEM_ID
1107 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
1108
1109 if (sql%notfound) then
1110 raise no_data_found;
1111 end if;
1112 end UPDATE_ROW;
1113
1114 procedure DELETE_ROW (
1115 X_ITEM_ID in NUMBER
1116 ) is
1117 begin
1118 /*********
1119 delete from IC_ITEM_MST_TL
1120 where ITEM_ID = X_ITEM_ID;
1121
1122 if (sql%notfound) then
1123 raise no_data_found;
1124 end if;
1125 **************** */
1126
1127 update IC_ITEM_MST_B set delete_mark = 1
1128 where ITEM_ID = X_ITEM_ID;
1129
1130 if (sql%notfound) then
1131 raise no_data_found;
1132 end if;
1133 end DELETE_ROW;
1134
1135 procedure ADD_LANGUAGE
1136 is
1137 begin
1138 delete from IC_ITEM_MST_TL T
1139 where not exists
1140 (select NULL
1141 from IC_ITEM_MST_B B
1142 where B.ITEM_ID = T.ITEM_ID
1143 );
1144
1145 update IC_ITEM_MST_TL T set (
1146 ITEM_DESC1,
1147 ITEM_DESC2
1148 ) = (select
1149 B.ITEM_DESC1,
1150 B.ITEM_DESC2
1151 from IC_ITEM_MST_TL B
1152 where B.ITEM_ID = T.ITEM_ID
1153 and B.LANGUAGE = T.SOURCE_LANG)
1154 where (
1155 T.ITEM_ID,
1156 T.LANGUAGE
1157 ) in (select
1158 SUBT.ITEM_ID,
1159 SUBT.LANGUAGE
1160 from IC_ITEM_MST_TL SUBB, IC_ITEM_MST_TL SUBT
1161 where SUBB.ITEM_ID = SUBT.ITEM_ID
1162 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
1163 and (SUBB.ITEM_DESC1 <> SUBT.ITEM_DESC1
1164 or SUBB.ITEM_DESC2 <> SUBT.ITEM_DESC2
1165 or (SUBB.ITEM_DESC2 is null and SUBT.ITEM_DESC2 is not null)
1166 or (SUBB.ITEM_DESC2 is not null and SUBT.ITEM_DESC2 is null)
1167 ));
1168
1169 insert into IC_ITEM_MST_TL (
1170 ITEM_ID,
1171 ITEM_DESC1,
1172 ITEM_DESC2,
1173 CREATION_DATE,
1174 CREATED_BY,
1175 LAST_UPDATE_DATE,
1176 LAST_UPDATED_BY,
1177 LAST_UPDATE_LOGIN,
1178 LANGUAGE,
1179 SOURCE_LANG
1180 ) select
1181 B.ITEM_ID,
1182 B.ITEM_DESC1,
1183 B.ITEM_DESC2,
1184 B.CREATION_DATE,
1185 B.CREATED_BY,
1186 B.LAST_UPDATE_DATE,
1187 B.LAST_UPDATED_BY,
1188 B.LAST_UPDATE_LOGIN,
1189 L.LANGUAGE_CODE,
1190 B.SOURCE_LANG
1191 from IC_ITEM_MST_TL B, FND_LANGUAGES L
1192 where L.INSTALLED_FLAG in ('I', 'B')
1193 and B.LANGUAGE = userenv('LANG')
1194 and not exists
1195 (select NULL
1196 from IC_ITEM_MST_TL T
1197 where T.ITEM_ID = B.ITEM_ID
1198 and T.LANGUAGE = L.LANGUAGE_CODE);
1199 end ADD_LANGUAGE;
1200
1201 end IC_ITEM_MST_PKG;
[Home] [Help]