[Home] [Help]
PACKAGE BODY: APPS.GMIVITM
Source
1 PACKAGE BODY GMIVITM AS
2 /* $Header: GMIVITMB.pls 115.28 2003/10/13 16:47:01 jsrivast ship $ */
3
4
5 /* Global variables */
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'GMIVITM';
7
8 FUNCTION v_classes (p_item_rec IN GMIGAPI.item_rec_typ)
9 RETURN BOOLEAN
10 IS
11 row_count number;
12
13 /*=====================================================
14 01/14/02 Joe DiIorio - BUG#2177942 11.5.1I
15 ====================================================*/
16 wk_rank_count number;
17
18 CURSOR Get_rank
19 IS
20 Select count(*)
21 from ic_rank_mst
22 where abc_code=p_item_rec.item_abccode and delete_mark = 0;
23
24
25
26 BEGIN
27
28 IF (p_item_rec.alloc_class IS NOT NULL) THEN
29 -- BUG#2461984 VAK
30 select 1 into row_count from ic_allc_cls
31 where alloc_class=RTRIM(p_item_rec.alloc_class) and delete_mark = 0;
32
33 IF (row_count <> 1) THEN
34 Return FALSE;
35 END IF;
36
37 END IF;
38
39 IF (p_item_rec.itemcost_class IS NOT NULL) THEN
40 -- BUG#2461984 VAK
41 select 1 into row_count from ic_cost_cls
42 where itemcost_class=RTRIM(p_item_rec.itemcost_class) and delete_mark = 0;
43
44 IF (row_count <> 1) THEN
45 Return FALSE;
46 END IF;
47
48 END IF;
49
50 IF (p_item_rec.customs_class IS NOT NULL) THEN
51 -- BUG#2461984 VAK
52 select 1 into row_count from ic_ctms_cls
53 where iccustoms_class=RTRIM(p_item_rec.customs_class) and delete_mark = 0;
54
55 IF (row_count <> 1) THEN
56 Return FALSE;
57 END IF;
58
59 END IF;
60
61
62 IF (p_item_rec.frt_class IS NOT NULL) THEN
63 -- BUG#2461984 VAK
64 select 1 into row_count from ic_frgt_cls
65 where icfrt_class=RTRIM(p_item_rec.frt_class) and delete_mark = 0;
66
67 IF (row_count <> 1) THEN
68 Return FALSE;
69 END IF;
70
71 END IF;
72
73
74 IF (p_item_rec.gl_class IS NOT NULL) THEN
75 -- BUG#2461984 VAK
76 select 1 into row_count from ic_gled_cls
77 where icgl_class=RTRIM(p_item_rec.gl_class) and delete_mark = 0;
78
79 IF (row_count <> 1) THEN
80 Return FALSE;
81 END IF;
82
83 END IF;
84
85 IF (p_item_rec.inv_class IS NOT NULL) THEN
86 -- BUG#2461984 VAK
87 select 1 into row_count from ic_invn_cls
88 where icinv_class=RTRIM(p_item_rec.inv_class) and delete_mark = 0;
89
90 IF (row_count <> 1) THEN
91 Return FALSE;
92 END IF;
93
94 END IF;
95
96 IF (p_item_rec.price_class IS NOT NULL) THEN
97 -- BUG#2461984 VAK
98 select 1 into row_count from ic_prce_cls
99 where icprice_class=RTRIM(p_item_rec.price_class) and delete_mark = 0;
100
101 IF (row_count <> 1) THEN
102 Return FALSE;
103 END IF;
104
105 END IF;
106
107 IF (p_item_rec.purch_class IS NOT NULL) THEN
108 -- BUG#2461984 VAK
109 select 1 into row_count from ic_prch_cls
110 where icpurch_class=RTRIM(p_item_rec.purch_class) and delete_mark = 0;
111
112 IF (row_count <> 1) THEN
113 Return FALSE;
114 END IF;
115
116 END IF;
117
118
119 /*=====================================================
120 01/14/02 Joe DiIorio - BUG#2177942 11.5.1I
121 ====================================================*/
122 IF (p_item_rec.item_abccode IS NOT NULL) THEN
123 OPEN Get_rank;
124 FETCH Get_rank into wk_rank_count;
125 CLOSE Get_rank;
126 IF (wk_rank_count = 0) THEN
127 Return FALSE;
128 END IF;
129 END IF;
130
131 IF (p_item_rec.sales_class IS NOT NULL) THEN
132 -- BUG#2461984 VAK
133 select 1 into row_count from ic_sale_cls
134 where icsales_class=RTRIM(p_item_rec.sales_class) and delete_mark = 0;
135
136 IF (row_count <> 1) THEN
137 Return FALSE;
138 END IF;
139
140 END IF;
141
142
143 IF (p_item_rec.ship_class IS NOT NULL) THEN
144 -- BUG#2461984 VAK
145 select 1 into row_count from ic_ship_cls
146 where icship_class=RTRIM(p_item_rec.ship_class) and delete_mark = 0;
147
148 IF (row_count <> 1) THEN
149 Return FALSE;
150 END IF;
151
152 END IF;
153
154
155 IF (p_item_rec.storage_class IS NOT NULL) THEN
156 -- BUG#2461984 VAK
157 select 1 into row_count from ic_stor_cls
158 where icstorage_class=RTRIM(p_item_rec.storage_class) and delete_mark = 0;
159
160 IF (row_count <> 1) THEN
161 Return FALSE;
162 END IF;
163
164 END IF;
165
166
167 IF (p_item_rec.tax_class IS NOT NULL) THEN
168 -- BUG#2461984 VAK
169 select 1 into row_count from ic_taxn_cls
170 where ictax_class=RTRIM(p_item_rec.tax_class) and delete_mark = 0;
171
172 IF (row_count <> 1) THEN
173 Return FALSE;
174 END IF;
175
176 END IF;
177
178
179 IF (p_item_rec.planning_class IS NOT NULL) THEN
180 -- BUG#2461984 VAK
181 select 1 into row_count from ps_plng_cls
182 where planning_class=RTRIM(p_item_rec.planning_class) and delete_mark = 0;
183
184 IF (row_count <> 1) THEN
185 Return FALSE;
186 END IF;
187
188 END IF;
189
190
191 IF (p_item_rec.qchold_res_code IS NOT NULL) THEN
192 -- BUG#2461984 VAK
193 select 1 into row_count from qc_hres_mst
194 where qchold_res_code=RTRIM(p_item_rec.qchold_res_code) and delete_mark = 0;
195
196 IF (row_count <> 1) THEN
197 Return FALSE;
198 END IF;
199
200 END IF;
201
202
203 IF (p_item_rec.seq_dpnd_class IS NOT NULL) THEN
204 -- BUG#2461984 VAK
205 select 1 into row_count from cr_sqdt_cls
206 where seq_dpnd_class=RTRIM(p_item_rec.seq_dpnd_class) and delete_mark = 0;
207
208 IF (row_count <> 1) THEN
209 Return FALSE;
210 END IF;
211
212 END IF;
213
214
215 IF (p_item_rec.inv_type IS NOT NULL) THEN
216 -- BUG#2461984 VAK
217 select 1 into row_count from ic_invn_typ
218 where inv_type=RTRIM(p_item_rec.inv_type) and delete_mark = 0;
219
220 IF (row_count <> 1) THEN
221 Return FALSE;
222 END IF;
223
224 END IF;
225
226
227 IF (p_item_rec.cost_mthd_code IS NOT NULL) THEN
228 -- BUG#2461984 VAK
229 select 1 into row_count from cm_mthd_mst
230 where cost_mthd_code=RTRIM(p_item_rec.cost_mthd_code) and delete_mark = 0;
231
232 IF (row_count <> 1) THEN
233 Return FALSE;
234 END IF;
235
236 END IF;
237
238
239 -- TKW 9/11/2003 B2378017
240 -- Added validation for four new classes.
241 IF (p_item_rec.gl_business_class IS NOT NULL) THEN
242
243 select 1 into row_count from gl_business_cls_vw
244 where gl_business_class=RTRIM(p_item_rec.gl_business_class) and delete_mark = 0;
245
246 IF (row_count <> 1) THEN
247 Return FALSE;
248 END IF;
249
250 END IF;
251
252
253 IF (p_item_rec.gl_prod_line IS NOT NULL) THEN
254
255 select 1 into row_count from gl_prod_line_vw
256 where gl_product_line=RTRIM(p_item_rec.gl_prod_line) and delete_mark = 0;
257
258 IF (row_count <> 1) THEN
259 Return FALSE;
260 END IF;
261
262 END IF;
263
264
265 IF (p_item_rec.sub_standard_class IS NOT NULL) THEN
266
267 select 1 into row_count from sub_std_item_cls_vw
268 where sub_standard_class=RTRIM(p_item_rec.sub_standard_class) and delete_mark = 0;
269
270 IF (row_count <> 1) THEN
271 Return FALSE;
272 END IF;
273
274 END IF;
275
276
277 IF (p_item_rec.tech_class IS NOT NULL) THEN
278
279 select 1 into row_count from tech_cls_subcls_vw
280 where tech_class=RTRIM(p_item_rec.tech_class) and delete_mark = 0;
281
282 IF (row_count <> 1) THEN
283 Return FALSE;
284 END IF;
285
286 END IF;
287
288
289 RETURN TRUE;
290
291 EXCEPTION
292 WHEN OTHERS
293 THEN
294 RETURN FALSE;
295 END v_classes;
296
297 FUNCTION v_commodity_code(p_item_rec IN GMIGAPI.item_rec_typ)
298 RETURN BOOLEAN
299 IS
300 l_row_count NUMBER;
301 BEGIN
302 SELECT 1 INTO l_row_count
303 FROM ic_comd_cds
304 WHERE commodity_code=UPPER(p_item_rec.commodity_code)
305 AND delete_mark = 0;
306
307 IF l_row_count > 0
308 THEN
309 RETURN TRUE;
310 END IF;
311
312 RETURN FALSE;
313
314 EXCEPTION
315 WHEN OTHERS
316 THEN RETURN FALSE;
317 END v_commodity_code;
318
319
320
321
322 /* +==========================================================================+
323 | PROCEDURE NAME |
324 | Validate_Item |
325 | |
326 | TYPE |
327 | Private |
328 | |
329 | USAGE |
330 | Validate item record |
331 | |
332 | PARAMETERS |
333 | p_api_version IN NUMBER - Api Version |
334 | p_validation_level IN VARCHAR2 - Validation Level Indicator |
335 | p_item_rec IN item_rec_typ - Item Master details |
336 | x_ic_item_mst_row OUT ic_item_mst%ROWTYPE |
337 | x_ic_item_cpg_row OUT ic_item_cpg%ROWTYPE |
338 | x_return_status OUT VARCHAR2 - Return Status |
339 | x_msg_count OUT NUMBER - Number of messages |
340 | x_msg_data OUT VARCHAR2 - Messages in encoded format |
341 | |
342 | RETURNS |
343 | Via x_ parameters |
344 | |
345 | HISTORY |
346 | |
347 | 21/Feb/2002 P Lowe Bug 2233859 - Field ont_pricing_qty_source |
348 | added - (no validation (default is 0))in |
349 | item_rec_typ record for the |
350 | Pricing by Quantity 2 project. |
351 | |
352 | 07-18-2002 V. Ajay Kumar BUG#2461984 Prefixed the 'RTRIM' |
353 | function for all the VARCHAR2 type fields |
354 | in order to suppress the trailing spaces. |
355 | |
356 | 13-Aug-2002 A. Mundhe Bug 2506207 - Removed the 'UPPER' function |
357 | on all attribute columns. |
358 +==========================================================================+
359 */
360 PROCEDURE Validate_item
361 ( p_api_version IN NUMBER
362 , p_validation_level IN VARCHAR2 :=FND_API.G_VALID_LEVEL_FULL
363 , p_item_rec IN GMIGAPI.item_rec_typ
364 , x_ic_item_mst_row OUT NOCOPY ic_item_mst%ROWTYPE
365 , x_ic_item_cpg_row OUT NOCOPY ic_item_cpg%ROWTYPE
366 , x_return_status OUT NOCOPY VARCHAR2
367 , x_msg_count OUT NOCOPY NUMBER
368 , x_msg_data OUT NOCOPY VARCHAR2
369 )
370 IS
371 l_ic_item_mst_row ic_item_mst%ROWTYPE;
372 l_ic_item_cpg_row ic_item_cpg%ROWTYPE;
373 l_sy_uoms_mst_row sy_uoms_mst%ROWTYPE;
374 l_sy_uoms_typ_row sy_uoms_typ%ROWTYPE;
375 l_qc_grad_mst_row qc_grad_mst%ROWTYPE;
376 l_qc_actn_mst_row qc_actn_mst%ROWTYPE;
377 l_ic_lots_sts_row ic_lots_sts%ROWTYPE;
378 l_api_name VARCHAR2(30) := 'Validate Item';
379 BEGIN
380
381 /* Standard call to check for call compatibility. */
382 IF NOT FND_API.Compatible_API_CALL
383 (GMIGUTL.API_VERSION, p_api_version, 'Validate_Item', G_PKG_NAME)
384 THEN
385 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
386 END IF;
387
388 /* Validate input record and setup output rows */
389
390 /* Item ID/Number/Descriptions/Alternatives */
391 x_ic_item_mst_row.item_id := NULL;
392 -- BEGIN BUG#2461984 VAK
393 x_ic_item_mst_row.item_no := UPPER(RTRIM(p_item_rec.item_no));
394 x_ic_item_mst_row.item_desc1 := RTRIM(p_item_rec.item_desc1);
395 IF NVL(p_item_rec.item_desc1, ' ') = ' '
396 THEN
397 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_ITEM_DESC1');
398
399 FND_MESSAGE.SET_TOKEN('ITEM_NO',p_item_rec.item_no);
400 FND_MSG_PUB.Add;
401 ELSE
402 x_ic_item_mst_row.item_desc2 := RTRIM(p_item_rec.item_desc2);
403 END IF;
404
405 x_ic_item_mst_row.alt_itema := RTRIM(p_item_rec.alt_itema);
406 x_ic_item_mst_row.alt_itemb := RTRIM(p_item_rec.alt_itemb);
407
408 /* Unit of Measure */
409
410 GMIGUTL.get_um(RTRIM(p_item_rec.item_um), l_sy_uoms_mst_row, l_sy_uoms_typ_row);
411 IF l_sy_uoms_mst_row.um_code IS NOT NULL
412 THEN
413 x_ic_item_mst_row.item_um := RTRIM(l_sy_uoms_mst_row.um_code);
414 ELSE
415 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_UOM');
416 FND_MESSAGE.SET_TOKEN('ITEM_NO',p_item_rec.item_no);
417 FND_MESSAGE.SET_TOKEN('UOM',p_item_rec.item_um);
418 FND_MSG_PUB.Add;
419 END IF;
420 -- END BUG#2461984 VAK
421 /* Dual unit of measure indicator */
422 IF p_item_rec.dualum_ind BETWEEN 0 AND 3
423 THEN
424 x_ic_item_mst_row.dualum_ind := p_item_rec.dualum_ind;
425 ELSE
426 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_DUALUM_IND');
427 FND_MESSAGE.SET_TOKEN('ITEM_NO',p_item_rec.item_no);
428 FND_MSG_PUB.Add;
429 END IF;
430
431 /* Secondary Unit of Measure - ignore if dualum_ind=0 */
432 /* For any other case, ensure it's present and not the */
433 /* same as the primary um. If the unit of meaure conversion */
434 /* is not fixed then validate the deviations too. As these */
435 /* columns are not null, set them to zero before continuing. */
436
437 x_ic_item_mst_row.deviation_lo := 0;
438 x_ic_item_mst_row.deviation_hi := 0;
439
440 IF x_ic_item_mst_row.dualum_ind > 0
441 THEN
442 IF NVL(p_item_rec.item_um2, x_ic_item_mst_row.item_um) <>
443 x_ic_item_mst_row.item_um
444 THEN
445 -- BUG#2461984 VAK
446 GMIGUTL.get_um(RTRIM(p_item_rec.item_um2), l_sy_uoms_mst_row, l_sy_uoms_typ_row);
447 IF l_sy_uoms_mst_row.um_code IS NOT NULL
448 THEN
449 -- BUG#2461984 VAK
450 x_ic_item_mst_row.item_um2 := RTRIM(l_sy_uoms_mst_row.um_code);
451 END IF;
452 END IF;
453
454 IF x_ic_item_mst_row.item_um2 IS NULL
455 THEN
456 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_UOM');
457 FND_MESSAGE.SET_TOKEN('ITEM_NO',p_item_rec.item_no);
458 FND_MESSAGE.SET_TOKEN('UOM',p_item_rec.item_um2);
459 FND_MSG_PUB.Add;
460 ELSE
461 IF x_ic_item_mst_row.dualum_ind > 1
462 THEN
463 IF NVL(p_item_rec.deviation_lo,0) <= 0 OR
464 NVL(p_item_rec.deviation_hi,0) <= 0
465 THEN
466 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_DEVIATION');
467 FND_MESSAGE.SET_TOKEN('ITEM_NO',p_item_rec.item_no);
468 FND_MSG_PUB.Add;
469 ELSE
470 x_ic_item_mst_row.deviation_lo := p_item_rec.deviation_lo;
471 x_ic_item_mst_row.deviation_hi := p_item_rec.deviation_hi;
472 END IF;
473 END IF;
474 END IF;
475 ELSE
476 x_ic_item_mst_row.deviation_lo := 0;
477 x_ic_item_mst_row.deviation_hi := 0;
478 END IF;
479
480 /* Level Code. Unused, so use 'as is' */
481 x_ic_item_mst_row.level_code := p_item_rec.level_code;
482
483 /* lot/sublot/indivisible/Noninv flags */
484
485 x_ic_item_mst_row.noninv_ind := NVL(p_item_rec.noninv_ind,0);
486
487 IF x_ic_item_mst_row.noninv_ind BETWEEN 0 AND 1
488 THEN
489 NULL;
490 ELSE
491 x_ic_item_mst_row.noninv_ind:= NULL;
492 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_NONINV_IND');
493 FND_MESSAGE.SET_TOKEN('ITEM_NO',p_item_rec.item_no);
494 FND_MSG_PUB.Add;
495 END IF;
496
497 IF NVL(p_item_rec.lot_ctl, -1) BETWEEN 0 AND 1
498 THEN
499 x_ic_item_mst_row.lot_ctl := p_item_rec.lot_ctl;
500
501 IF x_ic_item_mst_row.lot_ctl = 1
502 THEN
503 IF NVL(p_item_rec.sublot_ctl, -1) BETWEEN 0 AND 1
504 THEN
505 x_ic_item_mst_row.sublot_ctl := p_item_rec.sublot_ctl;
506 ELSE
507 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_SUBLOT_CTL');
508 FND_MESSAGE.SET_TOKEN('ITEM_NO',p_item_rec.item_no);
509 FND_MSG_PUB.Add;
510 END IF;
511
512 IF NVL(p_item_rec.lot_indivisible, -1) BETWEEN 0 AND 1
513 THEN
514 x_ic_item_mst_row.lot_indivisible := p_item_rec.lot_indivisible;
515 ELSE
516 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_LOT_INDIVISIBLE');
517 FND_MESSAGE.SET_TOKEN('ITEM_NO',p_item_rec.item_no);
518 FND_MSG_PUB.Add;
519 END IF;
520
521 IF x_ic_item_mst_row.noninv_ind = 1
522 THEN
523 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_NONINV_IND');
524 FND_MESSAGE.SET_TOKEN('ITEM_NO',p_item_rec.item_no);
525 FND_MSG_PUB.Add;
526 END IF;
527 ELSE
528 x_ic_item_mst_row.sublot_ctl := 0;
529 x_ic_item_mst_row.lot_indivisible := 0;
530 END IF;
531 ELSE
532 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_LOT_CTL');
533 FND_MESSAGE.SET_TOKEN('ITEM_NO',p_item_rec.item_no);
534 FND_MSG_PUB.Add;
535 END IF;
536
537 /* Match type is not used(?) */
538 x_ic_item_mst_row.match_type := 3;
539
540 /* Inactive indicator */
541 x_ic_item_mst_row.inactive_ind := NVL(p_item_rec.inactive_ind,0);
542 IF x_ic_item_mst_row.inactive_ind BETWEEN 0 AND 1
543 THEN
544 NULL;
545 ELSE
546 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_INACTIVE_IND');
547 FND_MESSAGE.SET_TOKEN('ITEM_NO',p_item_rec.item_no);
548 FND_MSG_PUB.Add;
549 END IF;
550
551 /* Location control flag */
552 x_ic_item_mst_row.loct_ctl := NVL(p_item_rec.loct_ctl,0);
553 IF x_ic_item_mst_row.loct_ctl BETWEEN 0 AND 2
554 THEN
555 NULL;
556 ELSE
557 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_LOCT_CTL');
558 FND_MESSAGE.SET_TOKEN('ITEM_NO',p_item_rec.item_no);
559 FND_MSG_PUB.Add;
560 END IF;
561
562 /* Grade, status control etc. If item is lot controlled, */
563 /* validate everything, else default it. */
564
565 IF x_ic_item_mst_row.lot_ctl = 1
566 THEN
567 x_ic_item_mst_row.grade_ctl := NVL(p_item_rec.grade_ctl,0);
568 x_ic_item_mst_row.shelf_life := NVL(p_item_rec.shelf_life,0);
569 x_ic_item_mst_row.retest_interval := NVL(p_item_rec.retest_interval,0);
570 x_ic_item_mst_row.expaction_interval := NVL(p_item_rec.expaction_interval,0);
571
572 IF x_ic_item_mst_row.grade_ctl BETWEEN 0 AND 1
573 THEN
574 IF x_ic_item_mst_row.grade_ctl = 1
575 THEN
576 IF NVL(p_item_rec.qc_grade, ' ') <> ' '
577 THEN
578 -- BUG#2461984 VAK
579 IF GMIGUTL.v_qc_grade(UPPER(RTRIM(p_item_rec.qc_grade)), l_qc_grad_mst_row)
580 THEN
581 x_ic_item_mst_row.qc_grade := l_qc_grad_mst_row.qc_grade;
582 END IF;
583 END IF;
584
585 IF x_ic_item_mst_row.qc_grade IS NULL
586 THEN
587 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_QC_GRADE');
588 FND_MESSAGE.SET_TOKEN('ITEM_NO',p_item_rec.item_no);
589 FND_MSG_PUB.Add;
590 END IF;
591 END IF; /* moved here for bug#1653385 */
592
593 x_ic_item_mst_row.retest_interval := NVL(p_item_rec.retest_interval,0);
594 IF x_ic_item_mst_row.retest_interval < 0
595 THEN
596 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_RETEST_INTERVAL');
597 FND_MESSAGE.SET_TOKEN('ITEM_NO',p_item_rec.item_no);
598 FND_MSG_PUB.Add;
599 END IF;
600
601 x_ic_item_mst_row.shelf_life := NVL(p_item_rec.shelf_life,0);
602 IF x_ic_item_mst_row.shelf_life < 0
603 THEN
604 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_SHELF_LIFE');
605 FND_MESSAGE.SET_TOKEN('ITEM_NO',p_item_rec.item_no);
606 FND_MSG_PUB.Add;
607 END IF;
608
609 IF NVL(p_item_rec.expaction_code, ' ') <> ' '
610 THEN
611 -- BUG#2461984 VAK
612 IF GMIGUTL.v_expaction_code(UPPER(RTRIM(p_item_rec.expaction_code)),l_qc_actn_mst_row)
613 THEN
614 x_ic_item_mst_row.expaction_code := l_qc_actn_mst_row.action_code;
615 END IF;
616 END IF;
617
618 --Jalaj Srivastava Bug 1617398
619 --expaction code should not be a required field
620 /* IF x_ic_item_mst_row.expaction_code IS NULL
621 THEN
622 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_EXPACTION_CODE');
623 FND_MESSAGE.SET_TOKEN('ITEM_NO',p_item_rec.item_no);
624 FND_MSG_PUB.Add;
625 END IF; */
626
627 x_ic_item_mst_row.expaction_interval := NVL(p_item_rec.expaction_interval,0);
628 IF x_ic_item_mst_row.expaction_interval < 0
629 THEN
630 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_EXPACTION_INTERVAL');
631 FND_MESSAGE.SET_TOKEN('ITEM_NO',p_item_rec.item_no);
632 FND_MSG_PUB.Add;
633 END IF;
634 /*************************************************
635 BUG#1653385 - moved this end if up.
636 END IF;
637 ************************************************/
638 ELSE
639 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_GRADE_CTL');
640 FND_MESSAGE.SET_TOKEN('ITEM_NO',p_item_rec.item_no);
641 FND_MSG_PUB.Add;
642 END IF;
643
644 x_ic_item_mst_row.status_ctl := NVL(p_item_rec.status_ctl,0);
645
646 IF x_ic_item_mst_row.status_ctl BETWEEN 0 AND 1
647 THEN
648 IF x_ic_item_mst_row.status_ctl = 1
649 THEN
650 IF NVL(p_item_rec.lot_status, ' ') <> ' '
651 THEN
652 IF GMIGUTL.v_lot_status(UPPER(p_item_rec.lot_status), l_ic_lots_sts_row)
653 THEN
654 x_ic_item_mst_row.lot_status := l_ic_lots_sts_row.lot_status;
655 END IF;
656 END IF;
657
658 IF x_ic_item_mst_row.lot_status IS NULL
659 THEN
660 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_LOT_STATUS');
661 FND_MESSAGE.SET_TOKEN('ITEM_NO',p_item_rec.item_no);
662 FND_MESSAGE.SET_TOKEN('LOT_STATUS', p_item_rec.lot_status);
663 FND_MSG_PUB.Add;
664 END IF;
665 END IF;
666 ELSE
667 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_STATUS_CTL');
668 FND_MESSAGE.SET_TOKEN('ITEM_NO',p_item_rec.item_no);
669 FND_MSG_PUB.Add;
670 END IF;
671 ELSE
672 x_ic_item_mst_row.grade_ctl := 0;
673 x_ic_item_mst_row.qc_grade := NULL;
674 x_ic_item_mst_row.status_ctl := 0;
675 x_ic_item_mst_row.lot_status := NULL;
676 x_ic_item_mst_row.expaction_interval := 0;
677 x_ic_item_mst_row.retest_interval := 0;
678 x_ic_item_mst_row.expaction_code := NULL;
679 x_ic_item_mst_row.shelf_life := 0;
680 END IF;
681
682 /* Experimental Indicator */
683
684 x_ic_item_mst_row.experimental_ind := NVL(p_item_rec.experimental_ind,0);
685 IF x_ic_item_mst_row.experimental_ind BETWEEN 0 AND 1
686 THEN
687 NULL;
688 ELSE
689 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_EXPERIMENTAL');
690 FND_MESSAGE.SET_TOKEN('ITEM_NO',p_item_rec.item_no);
691 FND_MSG_PUB.Add;
692 END IF;
693
694 /* GL class/Inv Class/Sales Class/Ship Class/Frt Class/ */
695 /* Price Class/Storage Class/Purch Class/Tax Class/Customs Class/ */
696 /* Alloc Class/Planning Class/Itemcost Class/Cost Mthd Code/UPC Code/ */
697 /* ABC Code */
698
699 IF GMIVITM.v_classes(p_item_rec)
700 THEN
701 --BEGIN BUG#2461984 VAK
702 x_ic_item_mst_row.item_abccode := UPPER(RTRIM(p_item_rec.item_abccode));
703 x_ic_item_mst_row.gl_class := UPPER(RTRIM(p_item_rec.gl_class));
704 x_ic_item_mst_row.inv_class := UPPER(RTRIM(p_item_rec.inv_class));
705 x_ic_item_mst_row.sales_class := UPPER(RTRIM(p_item_rec.sales_class));
706 x_ic_item_mst_row.ship_class := UPPER(RTRIM(p_item_rec.ship_class));
707 x_ic_item_mst_row.frt_class := UPPER(RTRIM(p_item_rec.frt_class));
708 x_ic_item_mst_row.price_class := UPPER(RTRIM(p_item_rec.price_class));
709 x_ic_item_mst_row.storage_class := UPPER(RTRIM(p_item_rec.storage_class));
710 x_ic_item_mst_row.purch_class := UPPER(RTRIM(p_item_rec.purch_class));
711 x_ic_item_mst_row.tax_class := UPPER(RTRIM(p_item_rec.tax_class));
712 x_ic_item_mst_row.customs_class := UPPER(RTRIM(p_item_rec.customs_class));
713 x_ic_item_mst_row.alloc_class := UPPER(RTRIM(p_item_rec.alloc_class));
714 x_ic_item_mst_row.planning_class := UPPER(RTRIM(p_item_rec.planning_class));
715 x_ic_item_mst_row.itemcost_class := UPPER(RTRIM(p_item_rec.itemcost_class));
716 x_ic_item_mst_row.cost_mthd_code := UPPER(RTRIM(p_item_rec.cost_mthd_code));
717 -- x_ic_item_mst_row.item_abccode := UPPER(p_item_rec.item_abccode);
718 x_ic_item_mst_row.qchold_res_code := UPPER(RTRIM(p_item_rec.qchold_res_code));
719 x_ic_item_mst_row.seq_dpnd_class := UPPER(RTRIM(p_item_rec.seq_dpnd_class));
720 x_ic_item_mst_row.inv_type := UPPER(RTRIM(p_item_rec.inv_type));
721 --END BUG#2461984
722 ELSE
723 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_CLASS');
724 FND_MSG_PUB.Add;
725 END IF;
726
727 /*=================================================
728 Joe DiIorio 10/23/2001 BUG#1989860 11.5.1H
729 Removed intrastat check and always set commodity
730 code to null.
731 ================================================*/
732 x_ic_item_mst_row.commodity_code := NULL;
733
734
735 /*=====================================================
736 Joe DiIorio 01/02/2001 BUG#2106212
737 Changed this line to get input upccode..
738 ====================================================*/
739 -- BUG#2461984 VAK
740 x_ic_item_mst_row.upc_code := UPPER(RTRIM(p_item_rec.upc_code));
741
742 /* Unused columns, although some of these are not null on the databse */
743 x_ic_item_mst_row.bulk_id := NULL;
744 x_ic_item_mst_row.pkg_id := NULL;
745 x_ic_item_mst_row.fill_qty := 0;
746 x_ic_item_mst_row.fill_um := NULL;
747 x_ic_item_mst_row.phantom_type := 0;
748
749
750 /* warehouse item and QC reference item */
751
752 IF NVL(p_item_rec.whse_item_no,p_item_rec.item_no) = p_item_rec.item_no
753 THEN
754 x_ic_item_mst_row.whse_item_id:= NULL;
755 ELSE
756 -- BUG#2461984 VAK
757 GMIGUTL.get_item(UPPER(RTRIM(p_item_rec.whse_item_no)), l_ic_item_mst_row, l_ic_item_cpg_row);
758 IF l_ic_item_mst_row.item_id IS NOT NULL
759 THEN
760 x_ic_item_mst_row.whse_item_id := l_ic_item_mst_row.item_id;
761 ELSE
762 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_WHSE_ITEM_NO');
763 FND_MESSAGE.SET_TOKEN('ITEM_NO',p_item_rec.item_no);
764 FND_MSG_PUB.Add;
765 END IF;
766 END IF;
767
768
769 IF NVL(p_item_rec.qcitem_no,' ') = ' '
770 THEN
771 x_ic_item_mst_row.qcitem_id:= NULL;
772 ELSE
773 -- BUG#2461984 VAK
774 GMIGUTL.get_item(UPPER(RTRIM(p_item_rec.qcitem_no)),l_ic_item_mst_row, l_ic_item_cpg_row);
775 IF l_ic_item_mst_row.item_id IS NOT NULL
776 THEN
777 x_ic_item_mst_row.qcitem_id := l_ic_item_mst_row.item_id;
778 ELSE
779 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_QCITEM_NO');
780 FND_MESSAGE.SET_TOKEN('ITEM_NO',p_item_rec.qcitem_no);
781 FND_MSG_PUB.Add;
782 END IF;
783 END IF;
784
785 /* Exported date */
786 x_ic_item_mst_row.exported_date := GMA_GLOBAL_GRP.SY$MIN_DATE;
787
788 /* Audit columns */
789 x_ic_item_mst_row.creation_date := SYSDATE;
790 x_ic_item_mst_row.last_update_date := SYSDATE;
791 x_ic_item_mst_row.created_by := GMIGUTL.DEFAULT_USER_ID;
792 x_ic_item_mst_row.last_updated_by := GMIGUTL.DEFAULT_USER_ID;
793 x_ic_item_mst_row.last_update_login := GMIGUTL.DEFAULT_LOGIN;
794 x_ic_item_mst_row.delete_mark := 0;
795 x_ic_item_mst_row.trans_cnt := 1;
796
797 /* Bug 2506207 */
798 /* Removed UPPER function on all attribute columns */
799 /* Setup attributes 'as is' */
800 --BEGIN BUG#2461984 VAK
801 x_ic_item_mst_row.attribute1 := RTRIM(p_item_rec.attribute1);
802 x_ic_item_mst_row.attribute2 := RTRIM(p_item_rec.attribute2);
803 x_ic_item_mst_row.attribute3 := RTRIM(p_item_rec.attribute3);
804 x_ic_item_mst_row.attribute4 := RTRIM(p_item_rec.attribute4);
805 x_ic_item_mst_row.attribute5 := RTRIM(p_item_rec.attribute5);
806 x_ic_item_mst_row.attribute6 := RTRIM(p_item_rec.attribute6);
807 x_ic_item_mst_row.attribute7 := RTRIM(p_item_rec.attribute7);
808 x_ic_item_mst_row.attribute8 := RTRIM(p_item_rec.attribute8);
809 x_ic_item_mst_row.attribute9 := RTRIM(p_item_rec.attribute9);
810 x_ic_item_mst_row.attribute10 := RTRIM(p_item_rec.attribute10);
811 x_ic_item_mst_row.attribute11 := RTRIM(p_item_rec.attribute11);
812 x_ic_item_mst_row.attribute12 := RTRIM(p_item_rec.attribute12);
813 x_ic_item_mst_row.attribute13 := RTRIM(p_item_rec.attribute13);
814 x_ic_item_mst_row.attribute14 := RTRIM(p_item_rec.attribute14);
815 x_ic_item_mst_row.attribute15 := RTRIM(p_item_rec.attribute15);
816 x_ic_item_mst_row.attribute16 := RTRIM(p_item_rec.attribute16);
817 x_ic_item_mst_row.attribute17 := RTRIM(p_item_rec.attribute17);
818 x_ic_item_mst_row.attribute18 := RTRIM(p_item_rec.attribute18);
819 x_ic_item_mst_row.attribute19 := RTRIM(p_item_rec.attribute19);
820 x_ic_item_mst_row.attribute20 := RTRIM(p_item_rec.attribute20);
821 x_ic_item_mst_row.attribute21 := RTRIM(p_item_rec.attribute21);
822 x_ic_item_mst_row.attribute22 := RTRIM(p_item_rec.attribute22);
823 x_ic_item_mst_row.attribute23 := RTRIM(p_item_rec.attribute23);
824 x_ic_item_mst_row.attribute24 := RTRIM(p_item_rec.attribute24);
825 x_ic_item_mst_row.attribute25 := RTRIM(p_item_rec.attribute25);
826 x_ic_item_mst_row.attribute26 := RTRIM(p_item_rec.attribute26);
827 x_ic_item_mst_row.attribute27 := RTRIM(p_item_rec.attribute27);
828 x_ic_item_mst_row.attribute28 := RTRIM(p_item_rec.attribute28);
829 x_ic_item_mst_row.attribute29 := RTRIM(p_item_rec.attribute29);
830 x_ic_item_mst_row.attribute30 := RTRIM(p_item_rec.attribute30);
831 x_ic_item_mst_row.attribute_category := RTRIM(p_item_rec.attribute_category);
832 --END BUG#2461984
833 -- 21/Feb/2002 P Lowe Bug 2233859 - Field ont_pricing_qty_source
834
835
836 IF p_item_rec.ont_pricing_qty_source BETWEEN 0 AND 1
837 THEN
838 x_ic_item_mst_row.ont_pricing_qty_source := NVL(p_item_rec.ont_pricing_qty_source,0);
839 ELSE
840 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_ONT_SOURCE');
841 FND_MESSAGE.SET_TOKEN('ITEM_NO',p_item_rec.item_no);
842 FND_MSG_PUB.Add;
843 END IF;
844
845 IF (p_item_rec.ont_pricing_qty_source > 0
846 and x_ic_item_mst_row.dualum_ind < 1 )
847 or (p_item_rec.ont_pricing_qty_source > 0
848 and UPPER(NVL(FND_PROFILE.VALUE('GML_OM_INTEGRATION'),'N')) = 'N' )
849
850 THEN
851 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_ONT_SOURCE');
852 FND_MESSAGE.SET_TOKEN('ITEM_NO',p_item_rec.item_no);
853 FND_MSG_PUB.Add;
854 END IF;
855
856 x_ic_item_mst_row.ont_pricing_qty_source := NVL(p_item_rec.ont_pricing_qty_source,0);
857
858 /* Jalaj Srivastava Bug 3158806
859 Replace check for CPG_INSTALL with lot control */
860 IF p_item_rec.lot_ctl = 1
861 THEN
862 /* Validate/setup CPG fields */
863 x_ic_item_cpg_row.item_id := NULL;
864 x_ic_item_cpg_row.ic_matr_days := NVL(p_item_rec.ic_matr_days,0);
865 IF x_ic_item_cpg_row.ic_matr_days < 0
866 THEN
867 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_MATR_DAYS');
868 FND_MESSAGE.SET_TOKEN('ITEM_NO',p_item_rec.item_no);
869 FND_MSG_PUB.Add;
870 END IF;
871
872 x_ic_item_cpg_row.ic_hold_days := p_item_rec.ic_hold_days;
873 /* Jalaj Srivastava Bug 3158806
874 Removed nvl above so that hold days in cpg table go as null if user did not sepcify
875 hold days.
876 Populate dummy column level code in ic_item_mst with hold days. */
877 x_ic_item_mst_row.level_code := x_ic_item_cpg_row.ic_hold_days;
878
879 IF x_ic_item_cpg_row.ic_matr_days < 0
880 THEN
881 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_HOLD_DAYS');
882 FND_MESSAGE.SET_TOKEN('ITEM_NO',p_item_rec.item_no);
883 FND_MSG_PUB.Add;
884 END IF;
885 END IF;
886 /* See how we got on: */
887
888 FND_MSG_PUB.Count_AND_GET ( p_count => x_msg_count
889 , p_data => x_msg_data
890 );
891 IF x_msg_count > 0
892 THEN
893 /* dbms_output.put_line(x_msg_data); */
894 RAISE FND_API.G_EXC_ERROR;
895 END IF;
896
897 /* Jalaj Srivastava Bug 3158806
898 Replace check for CPG_INSTALL with lot control */
899 IF p_item_rec.lot_ctl = 1
900 THEN
901 x_ic_item_cpg_row.creation_date := SYSDATE;
902 x_ic_item_cpg_row.last_update_date := SYSDATE;
903 x_ic_item_cpg_row.created_by := GMIGUTL.DEFAULT_USER_ID;
904 x_ic_item_cpg_row.last_updated_by := GMIGUTL.DEFAULT_USER_ID;
905 x_ic_item_cpg_row.last_update_login := GMIGUTL.DEFAULT_LOGIN;
906 END IF;
907
908 x_return_status := FND_API.G_RET_STS_SUCCESS;
909
910 EXCEPTION
911 WHEN FND_API.G_EXC_ERROR THEN
912 x_return_status := FND_API.G_RET_STS_ERROR;
913 FND_MSG_PUB.Count_AND_GET ( p_count => x_msg_count
914 , p_data => x_msg_data
915 );
916 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
917
918 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
919 FND_MSG_PUB.Count_AND_GET ( p_count => x_msg_count
920 , p_data => x_msg_data
921 );
922 WHEN OTHERS THEN
923 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
924 /* IF FND_MSG_PUB.check_msg_level */
925 /* (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) */
926 /* THEN */
927
928 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
929 , l_api_name
930 );
931
932 /* END IF; */
933 FND_MSG_PUB.Count_AND_GET ( p_count => x_msg_count
934 , p_data => x_msg_data
935 );
936
937 END Validate_Item;
938 END GMIVITM;