[Home] [Help]
PACKAGE BODY: APPS.GMIVDBL
Source
1 PACKAGE BODY GMIVDBL AS
2 /* $Header: GMIVDBLB.pls 115.22 2004/02/25 15:43:40 mkalyani ship $ */
3 /* +=========================================================================+
4 | Copyright (c) 2000 Oracle Corporation |
5 | TVP, Reading, England |
6 | All rights reserved |
7 +=========================================================================+
8 | FILENAME |
9 | GMIVDBLB.pls |
10 | |
11 | DESCRIPTION |
12 | |
13 | This package body contains routines which are privately |
14 | accessible from the inventory API processing layers. No other use, |
15 | either public or private is supported. |
16 | |
17 | API NAME |
18 | GMIVDBL Inventory API Database Layer |
19 | |
20 | TYPE |
21 | Private |
22 | |
23 | HISTORY |
24 | 12-May-2000 P.J.Schofield, OPM Development, Oracle UK |
25 | Created for Inventory API Release 3.0 |
26 | |
27 | 30-May-2001 Joe DiIorio 11.5.1G BUG#1806025 |
28 | uncommented exit and commit statements. |
29 | |
30 | 13-Jun-2001 A. Mundhe Bug 1764383 - Added code to all the functions|
31 | to return sqlerrm in case of unexpected |
32 | database errors. |
33 | 7-Sep-2001 Jalaj Srivastava Bug 1977956
34 | All the select functions should not log messages for
35 | No data found exception. This error is expected
36 | 21/Feb/2002 P Lowe Bug 2233859 - Field ont_pricing_qty_source |
37 | added - (validation (default is 0))in |
38 | item_rec_typ record for the |
39 | Pricing by Quantity 2 project. |
40 | 07/24/02 Jalaj Srivastava BUg 2483656 |
41 | Modified ic_jrnl_mst insert to not insert journal no |
42 | if it already exists |
43 | 17-Nov-2002 Joe DiIorio Bug 1977956 11.5.1J - added nocopy. |
44 | 15-Apr-2003 Joe DiIorio Bug 2880585 11.5.1K - added conversion_id |
45 | to insert of ic_item_cnv. Added insert of |
46 | gmi_item_conv_audit. |
47 | 24-Jun-2003 Joe DiIorio Bug 3022564 11.5.10K - Changed sequennce |
48 | retrieval for gmi_item_conv_audit to |
49 | gmi_conv_audit_id_s. Was incorrectly calling |
50 | gmi_conv_audit_detail_id_s. |
51 | 11-Sep-2003 Teresa Wong B2378017 - Modified code to support new |
52 | classes. 1) Moved the call to gmi_item_categories from |
53 | ic_item_mst_insert to GMIGAPIB.Create_Item. 2) Added |
54 | p_item_rec parameter to gmi_item_categories. 3) Added |
55 | code to get category set information and to call |
56 | gmi_item_categories_insert. |
57 | 24-Feb-2004 Anoop Baddam B3151733 - Added a new procedure |
58 | mtl_item_categories_insert that inserts data into |
59 | mtl_item_categories table. This procedure is called from|
60 | GMI_ITEM_CATEGORIES_INSERT procedure. |
61 +=========================================================================+
62 */
63 /* All of the following routines take a rowtype record appropriate to */
64 /* the table being accessed and return rowtype records, also appropriate to */
65 /* the table being accessed. In the 'select' variants only the key fields */
66 /* for the desired row need be filled in. The row, if located will be */
67 /* returned in the record passed back to the caller. In the 'insert' */
68 /* variants the row inserted, if the insertion succeeds, will be returned */
69 /* with the id columns (doc_id, journal_id, line_id, item_id etc.) filled */
70 /* in. Any id column values which were passed in will be used 'as is'. */
71
72 /* All routines will update 'return_status' and 'error_text' in line */
73 /* with the return value from the database. */
74
75 FUNCTION ic_item_mst_insert
76 (p_ic_item_mst_row IN ic_item_mst%ROWTYPE, x_ic_item_mst_row IN OUT NOCOPY ic_item_mst%ROWTYPE)
77 RETURN BOOLEAN
78 IS
79 BEGIN
80
81 /* Copy input record to output record */
82
83 x_ic_item_mst_row := p_ic_item_mst_row;
84
85 /* Fill in any missing surrogates; */
86 IF x_ic_item_mst_row.item_id IS NULL
87 THEN
88 SELECT gem5_item_id_s.nextval INTO x_ic_item_mst_row.item_id FROM dual;
89 END IF;
90
91 IF x_ic_item_mst_row.whse_item_id IS NULL
92 THEN
93 x_ic_item_mst_row.whse_item_id := x_ic_item_mst_row.item_id;
94 END IF;
95
96
97
98 INSERT INTO ic_item_mst
99 ( item_id
100 , item_no
101 , item_desc1
102 , item_desc2
103 , alt_itema
104 , alt_itemb
105 , item_um
106 , dualum_ind
107 , item_um2
108 , deviation_lo
109 , deviation_hi
110 , level_code
111 , lot_ctl
112 , lot_indivisible
113 , sublot_ctl
114 , loct_ctl
115 , noninv_ind
116 , match_type
117 , inactive_ind
118 , inv_type
119 , shelf_life
120 , retest_interval
121 , item_abccode
122 , gl_class
123 , inv_class
124 , sales_class
125 , ship_class
126 , frt_class
127 , price_class
128 , storage_class
129 , purch_class
130 , tax_class
131 , customs_class
132 , alloc_class
133 , planning_class
134 , itemcost_class
135 , cost_mthd_code
136 , upc_code
137 , grade_ctl
138 , status_ctl
139 , qc_grade
140 , lot_status
141 , bulk_id
142 , pkg_id
143 , qcitem_id
144 , qchold_res_code
145 , expaction_code
146 , fill_qty
147 , fill_um
148 , expaction_interval
149 , phantom_type
150 , whse_item_id
151 , experimental_ind
152 , exported_date
153 , created_by
154 , creation_date
155 , last_updated_by
156 , last_update_date
157 , last_update_login
158 , trans_cnt
159 , delete_mark
160 , text_code
161 , seq_dpnd_class
162 , commodity_code
163 , attribute1
164 , attribute2
165 , attribute3
166 , attribute4
167 , attribute5
168 , attribute6
169 , attribute7
170 , attribute8
171 , attribute9
172 , attribute10
173 , attribute11
174 , attribute12
175 , attribute13
176 , attribute14
177 , attribute15
178 , attribute16
179 , attribute17
180 , attribute18
181 , attribute19
182 , attribute20
183 , attribute21
184 , attribute22
185 , attribute23
186 , attribute24
187 , attribute25
188 , attribute26
189 , attribute27
190 , attribute28
191 , attribute29
192 , attribute30
193 , attribute_category
194 , ont_pricing_qty_source -- P Lowe Bug 2233859
195 )
196 VALUES
197 ( x_ic_item_mst_row.item_id
198 , x_ic_item_mst_row.item_no
199 , x_ic_item_mst_row.item_desc1
200 , x_ic_item_mst_row.item_desc2
201 , x_ic_item_mst_row.alt_itema
202 , x_ic_item_mst_row.alt_itemb
203 , x_ic_item_mst_row.item_um
204 , x_ic_item_mst_row.dualum_ind
205 , x_ic_item_mst_row.item_um2
206 , x_ic_item_mst_row.deviation_lo
207 , x_ic_item_mst_row.deviation_hi
208 , x_ic_item_mst_row.level_code
209 , x_ic_item_mst_row.lot_ctl
210 , x_ic_item_mst_row.lot_indivisible
211 , x_ic_item_mst_row.sublot_ctl
212 , x_ic_item_mst_row.loct_ctl
213 , x_ic_item_mst_row.noninv_ind
214 , x_ic_item_mst_row.match_type
215 , x_ic_item_mst_row.inactive_ind
216 , x_ic_item_mst_row.inv_type
217 , x_ic_item_mst_row.shelf_life
218 , x_ic_item_mst_row.retest_interval
219 , x_ic_item_mst_row.item_abccode
220 , x_ic_item_mst_row.gl_class
221 , x_ic_item_mst_row.inv_class
222 , x_ic_item_mst_row.sales_class
223 , x_ic_item_mst_row.ship_class
224 , x_ic_item_mst_row.frt_class
225 , x_ic_item_mst_row.price_class
226 , x_ic_item_mst_row.storage_class
227 , x_ic_item_mst_row.purch_class
228 , x_ic_item_mst_row.tax_class
229 , x_ic_item_mst_row.customs_class
230 , x_ic_item_mst_row.alloc_class
231 , x_ic_item_mst_row.planning_class
232 , x_ic_item_mst_row.itemcost_class
233 , x_ic_item_mst_row.cost_mthd_code
234 , x_ic_item_mst_row.upc_code
235 , x_ic_item_mst_row.grade_ctl
236 , x_ic_item_mst_row.status_ctl
237 , x_ic_item_mst_row.qc_grade
238 , x_ic_item_mst_row.lot_status
239 , x_ic_item_mst_row.bulk_id
240 , x_ic_item_mst_row.pkg_id
241 , x_ic_item_mst_row.qcitem_id
242 , x_ic_item_mst_row.qchold_res_code
243 , x_ic_item_mst_row.expaction_code
244 , x_ic_item_mst_row.fill_qty
245 , x_ic_item_mst_row.fill_um
246 , x_ic_item_mst_row.expaction_interval
247 , x_ic_item_mst_row.phantom_type
248 , x_ic_item_mst_row.whse_item_id
249 , x_ic_item_mst_row.experimental_ind
250 , x_ic_item_mst_row.exported_date
251 , x_ic_item_mst_row.created_by
252 , x_ic_item_mst_row.creation_date
253 , x_ic_item_mst_row.last_updated_by
254 , x_ic_item_mst_row.last_update_date
255 , x_ic_item_mst_row.last_update_login
256 , x_ic_item_mst_row.trans_cnt
257 , x_ic_item_mst_row.delete_mark
258 , x_ic_item_mst_row.text_code
259 , x_ic_item_mst_row.seq_dpnd_class
260 , x_ic_item_mst_row.commodity_code
261 , x_ic_item_mst_row.attribute1
262 , x_ic_item_mst_row.attribute2
263 , x_ic_item_mst_row.attribute3
264 , x_ic_item_mst_row.attribute4
265 , x_ic_item_mst_row.attribute5
266 , x_ic_item_mst_row.attribute6
267 , x_ic_item_mst_row.attribute7
268 , x_ic_item_mst_row.attribute8
269 , x_ic_item_mst_row.attribute9
270 , x_ic_item_mst_row.attribute10
271 , x_ic_item_mst_row.attribute11
272 , x_ic_item_mst_row.attribute12
273 , x_ic_item_mst_row.attribute13
274 , x_ic_item_mst_row.attribute14
275 , x_ic_item_mst_row.attribute15
276 , x_ic_item_mst_row.attribute16
277 , x_ic_item_mst_row.attribute17
278 , x_ic_item_mst_row.attribute18
279 , x_ic_item_mst_row.attribute19
280 , x_ic_item_mst_row.attribute20
281 , x_ic_item_mst_row.attribute21
282 , x_ic_item_mst_row.attribute22
283 , x_ic_item_mst_row.attribute23
284 , x_ic_item_mst_row.attribute24
285 , x_ic_item_mst_row.attribute25
286 , x_ic_item_mst_row.attribute26
287 , x_ic_item_mst_row.attribute27
288 , x_ic_item_mst_row.attribute28
289 , x_ic_item_mst_row.attribute29
290 , x_ic_item_mst_row.attribute30
291 , x_ic_item_mst_row.attribute_category
292 , x_ic_item_mst_row.ont_pricing_qty_source -- P Lowe Bug 2233859
293 );
294
295 GMIGUTL.DB_ERRNUM := NULL;
296
297 -- TKW 9/11/2003 B2378017 Moved gmi_item_categories to Create_Item procedure.
298 --Jalaj Srivastava Bug 1735676
299 --Item Categories convergence
300 -- gmi_item_categories(x_ic_item_mst_row);
301
302 RETURN TRUE;
303
304 EXCEPTION
305 WHEN OTHERS THEN
306 -- Bug 1764383
307 -- Added code to return sqlerrm in case of unexpected database errors.
308 FND_MESSAGE.SET_NAME('GMI', 'GMI_UNEXPECTED_ERROR');
309 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
310 FND_MSG_PUB.ADD;
311
312 GMIGUTL.DB_ERRNUM := SQLCODE;
313 GMIGUTL.DB_ERRMSG:= SQLERRM;
314 RETURN FALSE;
315
316 END ic_item_mst_insert;
317
318
319 --BEGIN BUG#3151733 Anoop.
320 PROCEDURE mtl_item_categories_insert(p_ic_item_mst_row IN ic_item_mst%ROWTYPE,
321 l_category_set_id IN NUMBER,l_category_id IN NUMBER)
322 IS
323 CURSOR c_inventory_org IS
324 SELECT organization_id
325 FROM gmi_item_organizations
326 UNION
327 SELECT distinct p.master_organization_id
328 FROM mtl_parameters p,
329 gmi_item_organizations g
330 WHERE p.organization_id = g.organization_id;
331
332 CURSOR get_inventory_item_id(C_item_no VARCHAR2) IS
333 SELECT inventory_item_id
334 FROM mtl_system_items
335 WHERE segment1 = C_item_no and
336 ROWNUM = 1;
337
338 CURSOR get_mult_item_cat_assign_flag IS
339 SELECT mult_item_cat_assign_flag
340 FROM mtl_category_sets
341 WHERE category_set_id = l_category_set_id;
342
343 l_inventory_item_id NUMBER;
344 l_mult_item_cat_assign_flag mtl_category_sets.mult_item_cat_assign_flag%TYPE;
345 BEGIN
346 OPEN get_inventory_item_id(p_ic_item_mst_row.ITEM_NO);
347 FETCH get_inventory_item_id into l_inventory_item_id;
348 CLOSE get_inventory_item_id;
349
350 OPEN get_mult_item_cat_assign_flag;
351 FETCH get_mult_item_cat_assign_flag into l_mult_item_cat_assign_flag;
352 CLOSE get_mult_item_cat_assign_flag;
353
354 FOR Cur_get_organizations_rec IN c_inventory_org
355 LOOP
356
357 IF (l_mult_item_cat_assign_flag = 'N') THEN
358 UPDATE mtl_item_categories
359 SET category_id = l_category_id
360 WHERE inventory_item_id = l_inventory_item_id
361 AND organization_id = Cur_get_organizations_rec.organization_id
362 AND category_set_id = l_category_set_id;
363 END IF;
364
365 IF ( (SQL%ROWCOUNT = 0) OR
366 (l_mult_item_cat_assign_flag = 'Y')) THEN
367 INSERT INTO mtl_item_categories(
368 INVENTORY_ITEM_ID,
369 ORGANIZATION_ID,
370 CATEGORY_SET_ID,
371 CATEGORY_ID,
372 LAST_UPDATE_DATE,
373 LAST_UPDATED_BY,
374 CREATION_DATE,
375 CREATED_BY,
376 LAST_UPDATE_LOGIN,
377 REQUEST_ID,
378 PROGRAM_APPLICATION_ID,
379 PROGRAM_ID,
380 PROGRAM_UPDATE_DATE,
381 WH_UPDATE_DATE
382 )
383 VALUES(
384 l_inventory_item_id,
385 Cur_get_organizations_rec.organization_id,
386 l_category_set_id,
387 l_category_id,
388 p_ic_item_mst_row.last_update_date,
389 p_ic_item_mst_row.last_updated_by,
390 p_ic_item_mst_row.creation_date,
391 p_ic_item_mst_row.created_by,
392 NULL,
393 NULL,
394 NULL,
395 NULL,
396 NULL,
397 NULL
398 );
399 END IF;
400
401 END LOOP;
402 EXCEPTION
403 WHEN OTHERS THEN
404
405 FND_MESSAGE.SET_NAME('GMI', 'GMI_UNEXPECTED_ERROR');
406 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
407 FND_MSG_PUB.ADD;
408
409 GMIGUTL.DB_ERRNUM := SQLCODE;
410 GMIGUTL.DB_ERRMSG:= SQLERRM;
411
412 END mtl_item_categories_insert;
413 --END BUG#3151733 Anoop.
414
415 -- TKW 9/11/2003 B2378017 Changed signature of proc below.
416 PROCEDURE GMI_ITEM_CATEGORIES (p_item_rec IN GMIGAPI.item_rec_typ, p_ic_item_mst_row IN ic_item_mst%ROWTYPE)
417 IS
418 Cursor get_category_set_id(Vopm_class gmi_category_sets.opm_class%TYPE) IS
419 SELECT gmi.category_set_id,mtl.structure_id
420 FROM gmi_category_sets gmi,
421 mtl_category_sets mtl
422 WHERE gmi.opm_class = Vopm_class
423 AND mtl.category_set_id = gmi.category_set_id;
424
425 l_category_set_id NUMBER;
426 l_category_id NUMBER;
427 l_structure_id NUMBER;
428
429 BEGIN
430
431 IF (p_ic_item_mst_row.alloc_class IS NOT NULL) THEN
432
433 OPEN get_category_set_id('ALLOC_CLASS');
434 FETCH get_category_set_id into l_category_set_id,l_structure_id;
435 CLOSE get_category_set_id;
436
437 gmi_item_categories_insert(p_ic_item_mst_row,
438 l_category_set_id,
439 p_ic_item_mst_row.alloc_class,
440 l_structure_id,
441 l_category_id);
442
443 UPDATE ic_item_mst set alloc_category_id = l_category_id
444 WHERE item_id = p_ic_item_mst_row.item_id;
445
446 END IF;
447
448 IF (p_ic_item_mst_row.itemcost_class IS NOT NULL) THEN
449
450 OPEN get_category_set_id('COST_CLASS');
451 FETCH get_category_set_id into l_category_set_id,l_structure_id;
452 CLOSE get_category_set_id;
453
454 gmi_item_categories_insert(p_ic_item_mst_row,
455 l_category_set_id,
456 p_ic_item_mst_row.itemcost_class,
457 l_structure_id,
458 l_category_id);
459
460 UPDATE ic_item_mst set cost_category_id = l_category_id
461 WHERE item_id = p_ic_item_mst_row.item_id;
462
463 END IF;
464
465 IF (p_ic_item_mst_row.customs_class IS NOT NULL) THEN
466
467 OPEN get_category_set_id('CUSTOMS_CLASS');
468 FETCH get_category_set_id into l_category_set_id,l_structure_id;
469 CLOSE get_category_set_id;
470
471 gmi_item_categories_insert(p_ic_item_mst_row,
472 l_category_set_id,
473 p_ic_item_mst_row.customs_class,
474 l_structure_id,
475 l_category_id);
476
477 UPDATE ic_item_mst set customs_category_id = l_category_id
478 WHERE item_id = p_ic_item_mst_row.item_id;
479
480 END IF;
481
482
483 IF (p_ic_item_mst_row.frt_class IS NOT NULL) THEN
484
485 OPEN get_category_set_id('FRT_CLASS');
486 FETCH get_category_set_id into l_category_set_id,l_structure_id;
487 CLOSE get_category_set_id;
488
489 gmi_item_categories_insert(p_ic_item_mst_row,
490 l_category_set_id,
491 p_ic_item_mst_row.frt_class,
492 l_structure_id,
493 l_category_id);
494
495 UPDATE ic_item_mst set frt_category_id = l_category_id
496 WHERE item_id = p_ic_item_mst_row.item_id;
497
498 END IF;
499
500
501 IF (p_ic_item_mst_row.gl_class IS NOT NULL) THEN
502
503 OPEN get_category_set_id('GL_CLASS');
504 FETCH get_category_set_id into l_category_set_id,l_structure_id;
505 CLOSE get_category_set_id;
506
507 gmi_item_categories_insert(p_ic_item_mst_row,
508 l_category_set_id,
509 p_ic_item_mst_row.gl_class,
510 l_structure_id,
511 l_category_id);
512
513 UPDATE ic_item_mst set gl_category_id = l_category_id
514 WHERE item_id = p_ic_item_mst_row.item_id;
515
516 END IF;
517
518 IF (p_ic_item_mst_row.inv_class IS NOT NULL) THEN
519
520 OPEN get_category_set_id('INV_CLASS');
521 FETCH get_category_set_id into l_category_set_id,l_structure_id;
522 CLOSE get_category_set_id;
523
524 gmi_item_categories_insert(p_ic_item_mst_row,
525 l_category_set_id,
526 p_ic_item_mst_row.inv_class,
527 l_structure_id,
528 l_category_id);
529
530 UPDATE ic_item_mst set inv_category_id = l_category_id
531 WHERE item_id = p_ic_item_mst_row.item_id;
532
533 END IF;
534
535 IF (p_ic_item_mst_row.price_class IS NOT NULL) THEN
536
537 OPEN get_category_set_id('PRICE_CLASS');
538 FETCH get_category_set_id into l_category_set_id,l_structure_id;
539 CLOSE get_category_set_id;
540
541 gmi_item_categories_insert(p_ic_item_mst_row,
542 l_category_set_id,
543 p_ic_item_mst_row.price_class,
544 l_structure_id,
545 l_category_id);
546
547 UPDATE ic_item_mst set price_category_id = l_category_id
548 WHERE item_id = p_ic_item_mst_row.item_id;
549
550 END IF;
551
552 IF (p_ic_item_mst_row.purch_class IS NOT NULL) THEN
553
554 OPEN get_category_set_id('PURCH_CLASS');
555 FETCH get_category_set_id into l_category_set_id,l_structure_id;
556 CLOSE get_category_set_id;
557
558 gmi_item_categories_insert(p_ic_item_mst_row,
559 l_category_set_id,
560 p_ic_item_mst_row.purch_class,
561 l_structure_id,
562 l_category_id);
563
564 UPDATE ic_item_mst set purch_category_id = l_category_id
565 WHERE item_id = p_ic_item_mst_row.item_id;
566
567 END IF;
568
569
570 IF (p_ic_item_mst_row.sales_class IS NOT NULL) THEN
571
572 OPEN get_category_set_id('SALES_CLASS');
573 FETCH get_category_set_id into l_category_set_id,l_structure_id;
574 CLOSE get_category_set_id;
575
576 gmi_item_categories_insert(p_ic_item_mst_row,
577 l_category_set_id,
578 p_ic_item_mst_row.sales_class,
579 l_structure_id,
580 l_category_id);
581
582 UPDATE ic_item_mst set sales_category_id = l_category_id
583 WHERE item_id = p_ic_item_mst_row.item_id;
584
585 END IF;
586
587
588 IF (p_ic_item_mst_row.ship_class IS NOT NULL) THEN
589
590 OPEN get_category_set_id('SHIP_CLASS');
591 FETCH get_category_set_id into l_category_set_id,l_structure_id;
592 CLOSE get_category_set_id;
593
594 gmi_item_categories_insert(p_ic_item_mst_row,
595 l_category_set_id,
596 p_ic_item_mst_row.ship_class,
597 l_structure_id,
598 l_category_id);
599
600 UPDATE ic_item_mst set ship_category_id = l_category_id
601 WHERE item_id = p_ic_item_mst_row.item_id;
602
603 END IF;
604
605
606 IF (p_ic_item_mst_row.storage_class IS NOT NULL) THEN
607
608 OPEN get_category_set_id('STORAGE_CLASS');
609 FETCH get_category_set_id into l_category_set_id,l_structure_id;
610 CLOSE get_category_set_id;
611
612 gmi_item_categories_insert(p_ic_item_mst_row,
613 l_category_set_id,
614 p_ic_item_mst_row.storage_class,
615 l_structure_id,
616 l_category_id);
617
618 UPDATE ic_item_mst set storage_category_id = l_category_id
619 WHERE item_id = p_ic_item_mst_row.item_id;
620
621 END IF;
622
623
624 IF (p_ic_item_mst_row.tax_class IS NOT NULL) THEN
625
626 OPEN get_category_set_id('TAX_CLASS');
627 FETCH get_category_set_id into l_category_set_id,l_structure_id;
628 CLOSE get_category_set_id;
629
630 gmi_item_categories_insert(p_ic_item_mst_row,
631 l_category_set_id,
632 p_ic_item_mst_row.tax_class,
633 l_structure_id,
634 l_category_id);
635
636 UPDATE ic_item_mst set tax_class = null
637 WHERE item_id = p_ic_item_mst_row.item_id;
638
639 INSERT INTO IC_TAXN_ASC(
640 ictax_class,
641 tax_category_id,
642 item_id,
643 trans_cnt,
644 text_code,
645 delete_mark,
646 creation_date,
647 created_by,
648 last_update_date,
649 last_updated_by,
650 last_update_login)
651 VALUES(
652 p_ic_item_mst_row.tax_class,
653 l_category_id,
654 p_ic_item_mst_row.item_id,
655 0,
656 NULL,
657 0,
658 p_ic_item_mst_row.creation_date,
659 p_ic_item_mst_row.created_by,
660 p_ic_item_mst_row.last_update_date,
661 p_ic_item_mst_row.last_updated_by,
662 p_ic_item_mst_row.last_update_login);
663
664 END IF;
665
666
667 IF (p_ic_item_mst_row.planning_class IS NOT NULL) THEN
668
669 OPEN get_category_set_id('PLANNING_CLASS');
670 FETCH get_category_set_id into l_category_set_id,l_structure_id;
671 CLOSE get_category_set_id;
672
673 gmi_item_categories_insert(p_ic_item_mst_row,
674 l_category_set_id,
675 p_ic_item_mst_row.planning_class,
676 l_structure_id,
677 l_category_id);
678
679 UPDATE ic_item_mst set planning_category_id = l_category_id
680 WHERE item_id = p_ic_item_mst_row.item_id;
681
682 END IF;
683
684
685 IF (p_ic_item_mst_row.seq_dpnd_class IS NOT NULL) THEN
686
687 OPEN get_category_set_id('SEQ_CLASS');
688 FETCH get_category_set_id into l_category_set_id,l_structure_id;
689 CLOSE get_category_set_id;
690
691 gmi_item_categories_insert(p_ic_item_mst_row,
692 l_category_set_id,
693 p_ic_item_mst_row.seq_dpnd_class,
694 l_structure_id,
695 l_category_id);
696
697 UPDATE ic_item_mst set seq_category_id = l_category_id
698 WHERE item_id = p_ic_item_mst_row.item_id;
699
700 END IF;
701
702 -- TKW 9/11/2003 B2378017 Added four new classes.
703 IF (p_item_rec.gl_business_class IS NOT NULL) THEN
704
705 OPEN get_category_set_id('GL_BUSINESS_CLASS');
706 FETCH get_category_set_id into l_category_set_id,l_structure_id;
707 CLOSE get_category_set_id;
708
709 gmi_item_categories_insert(p_ic_item_mst_row,
710 l_category_set_id,
711 p_item_rec.gl_business_class,
712 l_structure_id,
713 l_category_id);
714
715 END IF;
716
717
718 IF (p_item_rec.gl_prod_line IS NOT NULL) THEN
719
720 OPEN get_category_set_id('GL_PRODUCT_LINE');
721 FETCH get_category_set_id into l_category_set_id,l_structure_id;
722 CLOSE get_category_set_id;
723
724 gmi_item_categories_insert(p_ic_item_mst_row,
725 l_category_set_id,
726 p_item_rec.gl_prod_line,
727 l_structure_id,
728 l_category_id);
729
730 END IF;
731
732
733 IF (p_item_rec.sub_standard_class IS NOT NULL) THEN
734
735 OPEN get_category_set_id('SUB_STANDARD_CLASS');
736 FETCH get_category_set_id into l_category_set_id,l_structure_id;
737 CLOSE get_category_set_id;
738
739 gmi_item_categories_insert(p_ic_item_mst_row,
740 l_category_set_id,
741 p_item_rec.sub_standard_class,
742 l_structure_id,
743 l_category_id);
744
745 END IF;
746
747
748 IF (p_item_rec.tech_class IS NOT NULL) THEN
749
750 OPEN get_category_set_id('TECH_CLASS');
751 FETCH get_category_set_id into l_category_set_id,l_structure_id;
752 CLOSE get_category_set_id;
753
754 gmi_item_categories_insert(p_ic_item_mst_row,
755 l_category_set_id,
756 p_item_rec.tech_class,
757 l_structure_id,
758 l_category_id);
759
760 END IF;
761
762 END GMI_ITEM_CATEGORIES;
763
764 PROCEDURE GMI_ITEM_CATEGORIES_INSERT (p_ic_item_mst_row IN ic_item_mst%ROWTYPE,
765 p_category_set_id NUMBER,
766 p_category_concat_segs mtl_categories_v.category_concat_segs%TYPE,
767 p_structure_id NUMBER,
768 p_category_id IN OUT NOCOPY NUMBER)
769 IS
770 Cursor get_category_id(Vcategory_concat_segs mtl_categories_v.category_concat_segs%TYPE,
771 Vstructure_id NUMBER) IS
772 SELECT category_id
773 FROM mtl_categories_v
774 WHERE category_concat_segs = Vcategory_concat_segs
775 AND structure_id = Vstructure_id;
776
777 BEGIN
778 OPEN get_category_id(p_category_concat_segs,p_structure_id);
779 FETCH get_category_id INTO p_category_id;
780 CLOSE get_category_id;
781
782 INSERT INTO gmi_item_categories(
783 item_id,
784 category_set_id,
785 category_id,
786 created_by,
787 creation_date,
788 last_updated_by,
789 last_update_date,
790 last_update_login
791 )
792 VALUES (
793 p_ic_item_mst_row.item_id,
794 p_category_set_id,
795 p_category_id,
796 p_ic_item_mst_row.created_by,
797 p_ic_item_mst_row.creation_date,
798 p_ic_item_mst_row.last_updated_by,
799 p_ic_item_mst_row.last_update_date,
800 p_ic_item_mst_row.last_update_login
801 );
802 --BUG#3151733 Anoop.
803 mtl_item_categories_insert(p_ic_item_mst_row,p_category_set_id,p_category_id);
804 END GMI_ITEM_CATEGORIES_INSERT;
805
806
807 FUNCTION ic_item_mst_select
808 (p_ic_item_mst_row IN ic_item_mst%ROWTYPE, x_ic_item_mst_row IN OUT NOCOPY ic_item_mst%ROWTYPE)
809 RETURN BOOLEAN
810 IS
811 BEGIN
812
813 IF p_ic_item_mst_row.item_no IS NOT NULL
814 THEN
815 SELECT * INTO x_ic_item_mst_row FROM ic_item_mst
816 WHERE item_no=p_ic_item_mst_row.item_no;
817 ELSE
818 SELECT * INTO x_ic_item_mst_row FROM ic_item_mst
819 WHERE item_id=p_ic_item_mst_row.item_id;
820 END IF;
821
822 GMIGUTL.DB_ERRNUM := NULL;
823 RETURN TRUE;
824
825 EXCEPTION
826 WHEN NO_DATA_FOUND THEN
827 --Jalaj Srivastava Bug 1977956
828 --Do not add error using fnd_msg_pub.add
829 --This is a expected error.
830
831 GMIGUTL.DB_ERRNUM := SQLCODE;
832 GMIGUTL.DB_ERRMSG:= SQLERRM;
833 RETURN FALSE;
834
835 WHEN OTHERS THEN
836 -- Bug 1764383
837 -- Added code to return sqlerrm in case of unexpected database errors.
838 FND_MESSAGE.SET_NAME('GMI', 'GMI_UNEXPECTED_ERROR');
839 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
840 FND_MSG_PUB.ADD;
841
842 GMIGUTL.DB_ERRNUM := SQLCODE;
843 GMIGUTL.DB_ERRMSG:= SQLERRM;
844 RETURN FALSE;
845
846 END ic_item_mst_select;
847
848 FUNCTION ic_lots_mst_insert
849 (p_ic_lots_mst_row IN ic_lots_mst%ROWTYPE, x_ic_lots_mst_row IN OUT NOCOPY ic_lots_mst%ROWTYPE)
850 RETURN BOOLEAN
851 IS
852 BEGIN
853
854 /* Copy input to output assuming success */
855
856 x_ic_lots_mst_row := p_ic_lots_mst_row;
857
858 IF x_ic_lots_mst_row.lot_id IS NULL
859 THEN
860 SELECT gem5_lot_id_s.nextval INTO x_ic_lots_mst_row.lot_id FROM dual;
861 END IF;
862
863 INSERT INTO ic_lots_mst
864 ( item_id
865 , lot_no
866 , sublot_no
867 , lot_id
868 , lot_desc
869 , qc_grade
870 , expaction_code
871 , expaction_date
872 , lot_created
873 , expire_date
874 , retest_date
875 , strength
876 , inactive_ind
877 , origination_type
878 , shipvend_id
879 , vendor_lot_no
880 , creation_date
881 , last_update_date
882 , created_by
883 , last_updated_by
884 , trans_cnt
885 , delete_mark
886 , text_code
887 , attribute1
888 , attribute2
889 , attribute3
890 , attribute4
891 , attribute5
892 , attribute6
893 , attribute7
894 , attribute8
895 , attribute9
896 , attribute10
897 , attribute11
898 , attribute12
899 , attribute13
900 , attribute14
901 , attribute15
902 , attribute16
903 , attribute17
904 , attribute18
905 , attribute19
906 , attribute20
907 , attribute21
908 , attribute22
909 , attribute23
910 , attribute24
911 , attribute25
912 , attribute26
913 , attribute27
914 , attribute28
915 , attribute29
916 , attribute30
917 , attribute_category
918 )
919 VALUES
920 ( x_ic_lots_mst_row.item_id
921 , x_ic_lots_mst_row.lot_no
922 , x_ic_lots_mst_row.sublot_no
923 , x_ic_lots_mst_row.lot_id
924 , x_ic_lots_mst_row.lot_desc
925 , x_ic_lots_mst_row.qc_grade
926 , x_ic_lots_mst_row.expaction_code
927 , x_ic_lots_mst_row.expaction_date
928 , x_ic_lots_mst_row.lot_created
929 , x_ic_lots_mst_row.expire_date
930 , x_ic_lots_mst_row.retest_date
931 , x_ic_lots_mst_row.strength
932 , x_ic_lots_mst_row.inactive_ind
933 , x_ic_lots_mst_row.origination_type
934 , x_ic_lots_mst_row.shipvend_id
935 , x_ic_lots_mst_row.vendor_lot_no
936 , x_ic_lots_mst_row.creation_date
937 , x_ic_lots_mst_row.last_update_date
938 , x_ic_lots_mst_row.created_by
939 , x_ic_lots_mst_row.last_updated_by
940 , x_ic_lots_mst_row.trans_cnt
941 , x_ic_lots_mst_row.delete_mark
942 , x_ic_lots_mst_row.text_code
943 , x_ic_lots_mst_row.attribute1
944 , x_ic_lots_mst_row.attribute2
945 , x_ic_lots_mst_row.attribute3
946 , x_ic_lots_mst_row.attribute4
947 , x_ic_lots_mst_row.attribute5
948 , x_ic_lots_mst_row.attribute6
949 , x_ic_lots_mst_row.attribute7
950 , x_ic_lots_mst_row.attribute8
951 , x_ic_lots_mst_row.attribute9
952 , x_ic_lots_mst_row.attribute10
953 , x_ic_lots_mst_row.attribute11
954 , x_ic_lots_mst_row.attribute12
955 , x_ic_lots_mst_row.attribute13
956 , x_ic_lots_mst_row.attribute14
957 , x_ic_lots_mst_row.attribute15
958 , x_ic_lots_mst_row.attribute16
959 , x_ic_lots_mst_row.attribute17
960 , x_ic_lots_mst_row.attribute18
961 , x_ic_lots_mst_row.attribute19
962 , x_ic_lots_mst_row.attribute20
963 , x_ic_lots_mst_row.attribute21
964 , x_ic_lots_mst_row.attribute22
965 , x_ic_lots_mst_row.attribute23
966 , x_ic_lots_mst_row.attribute24
967 , x_ic_lots_mst_row.attribute25
968 , x_ic_lots_mst_row.attribute26
969 , x_ic_lots_mst_row.attribute27
970 , x_ic_lots_mst_row.attribute28
971 , x_ic_lots_mst_row.attribute29
972 , x_ic_lots_mst_row.attribute30
973 , x_ic_lots_mst_row.attribute_category
974 );
975
976 GMIGUTL.DB_ERRNUM := NULL;
977 RETURN TRUE;
978
979 EXCEPTION
980 WHEN OTHERS THEN
981 -- Bug 1764383
982 -- Added code to return sqlerrm in case of unexpected database errors.
983 FND_MESSAGE.SET_NAME('GMI', 'GMI_UNEXPECTED_ERROR');
984 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
985 FND_MSG_PUB.ADD;
986
987 GMIGUTL.DB_ERRNUM := SQLCODE;
988 GMIGUTL.DB_ERRMSG:= SQLERRM;
989 RETURN FALSE;
990 END ic_lots_mst_insert;
991
992 FUNCTION ic_lots_mst_select
993 (p_ic_lots_mst_row IN ic_lots_mst%ROWTYPE, x_ic_lots_mst_row IN OUT NOCOPY ic_lots_mst%ROWTYPE)
994 RETURN BOOLEAN
995 IS
996 BEGIN
997 SELECT * INTO x_ic_lots_mst_row FROM ic_lots_mst
998 WHERE item_id = p_ic_lots_mst_row.item_id AND
999 lot_no = p_ic_lots_mst_row.lot_no AND
1000 NVL(sublot_no,' ')=NVL(p_ic_lots_mst_row.sublot_no,' ');
1001 GMIGUTL.DB_ERRNUM := NULL;
1002 RETURN TRUE;
1003
1004 EXCEPTION
1005 WHEN NO_DATA_FOUND THEN
1006 --Jalaj Srivastava Bug 1977956
1007 --Do not add error using fnd_msg_pub.add
1008 --This is a expected error.
1009
1010 GMIGUTL.DB_ERRNUM := SQLCODE;
1011 GMIGUTL.DB_ERRMSG:= SQLERRM;
1012 RETURN FALSE;
1013
1014 WHEN OTHERS THEN
1015 -- Bug 1764383
1016 -- Added code to return sqlerrm in case of unexpected database errors.
1017 FND_MESSAGE.SET_NAME('GMI', 'GMI_UNEXPECTED_ERROR');
1018 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
1019 FND_MSG_PUB.ADD;
1020
1021 GMIGUTL.DB_ERRNUM := SQLCODE;
1022 GMIGUTL.DB_ERRMSG:= SQLERRM;
1023 RETURN FALSE;
1024 END ic_lots_mst_select;
1025
1026 FUNCTION ic_item_cpg_insert
1027 (p_ic_item_cpg_row IN ic_item_cpg%ROWTYPE, x_ic_item_cpg_row IN OUT NOCOPY ic_item_cpg%ROWTYPE)
1028 RETURN BOOLEAN
1029 IS
1030 BEGIN
1031 /* Copy input to output */
1032
1033 x_ic_item_cpg_row := p_ic_item_cpg_row;
1034
1035 INSERT INTO ic_item_cpg
1036 ( item_id
1037 , ic_matr_days
1038 , ic_hold_days
1039 , created_by
1040 , creation_date
1041 , last_updated_by
1042 , last_update_date
1043 , last_update_login
1044 )
1045 VALUES
1046 ( x_ic_item_cpg_row.item_id
1047 , x_ic_item_cpg_row.ic_matr_days
1048 , x_ic_item_cpg_row.ic_hold_days
1049 , x_ic_item_cpg_row.created_by
1050 , x_ic_item_cpg_row.creation_date
1051 , x_ic_item_cpg_row.last_updated_by
1052 , x_ic_item_cpg_row.last_update_date
1053 , x_ic_item_cpg_row.last_update_login
1054 );
1055
1056 GMIGUTL.DB_ERRNUM := NULL;
1057 RETURN TRUE;
1058
1059 EXCEPTION
1060 WHEN OTHERS THEN
1061 -- Bug 1764383
1062 -- Added code to return sqlerrm in case of unexpected database errors.
1063 FND_MESSAGE.SET_NAME('GMI', 'GMI_UNEXPECTED_ERROR');
1064 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
1065 FND_MSG_PUB.ADD;
1066
1067 GMIGUTL.DB_ERRNUM := SQLCODE;
1068 GMIGUTL.DB_ERRMSG:= SQLERRM;
1069 RETURN FALSE;
1070 END ic_item_cpg_insert;
1071
1072 FUNCTION ic_item_cpg_select
1073 (p_ic_item_cpg_row IN ic_item_cpg%ROWTYPE, x_ic_item_cpg_row IN OUT NOCOPY ic_item_cpg%ROWTYPE)
1074 RETURN BOOLEAN
1075 IS
1076 BEGIN
1077 SELECT * INTO x_ic_item_cpg_row FROM ic_item_cpg
1078 WHERE item_id = p_ic_item_cpg_row.item_id;
1079
1080 GMIGUTL.DB_ERRNUM := NULL;
1081 RETURN TRUE;
1082
1083 EXCEPTION
1084 WHEN NO_DATA_FOUND THEN
1085 --Jalaj Srivastava Bug 1977956
1086 --Do not add error using fnd_msg_pub.add
1087 --This is a expected error.
1088
1089 GMIGUTL.DB_ERRNUM := SQLCODE;
1090 GMIGUTL.DB_ERRMSG:= SQLERRM;
1091 RETURN FALSE;
1092
1093 WHEN OTHERS THEN
1094 -- Bug 1764383
1095 -- Added code to return sqlerrm in case of unexpected database errors.
1096 FND_MESSAGE.SET_NAME('GMI', 'GMI_UNEXPECTED_ERROR');
1097 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
1098 FND_MSG_PUB.ADD;
1099
1100 GMIGUTL.DB_ERRNUM := SQLCODE;
1101 GMIGUTL.DB_ERRMSG:= SQLERRM;
1102 RETURN FALSE;
1103 END ic_item_cpg_select;
1104
1105 FUNCTION ic_lots_cpg_insert
1106 (p_ic_lots_cpg_row IN ic_lots_cpg%ROWTYPE, x_ic_lots_cpg_row IN OUT NOCOPY ic_lots_cpg%ROWTYPE)
1107 RETURN BOOLEAN
1108 IS
1109 BEGIN
1110 /* Copy input to output */
1111
1112 x_ic_lots_cpg_row := p_ic_lots_cpg_row;
1113
1114 INSERT INTO ic_lots_cpg
1115 ( item_id
1116 , lot_id
1117 , ic_matr_date
1118 , ic_hold_date
1119 , created_by
1120 , creation_date
1121 , last_update_date
1122 , last_updated_by
1123 , last_update_login
1124 )
1125 VALUES
1126 ( x_ic_lots_cpg_row.item_id
1127 , x_ic_lots_cpg_row.lot_id
1128 , x_ic_lots_cpg_row.ic_matr_date
1129 , x_ic_lots_cpg_row.ic_hold_date
1130 , x_ic_lots_cpg_row.created_by
1131 , x_ic_lots_cpg_row.creation_date
1132 , x_ic_lots_cpg_row.last_update_date
1133 , x_ic_lots_cpg_row.last_updated_by
1134 , x_ic_lots_cpg_row.last_update_login
1135 );
1136
1137 GMIGUTL.DB_ERRNUM := NULL;
1138 RETURN TRUE;
1139
1140 EXCEPTION
1141 WHEN OTHERS THEN
1142 -- Bug 1764383
1143 -- Added code to return sqlerrm in case of unexpected database errors.
1144 FND_MESSAGE.SET_NAME('GMI', 'GMI_UNEXPECTED_ERROR');
1145 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
1146 FND_MSG_PUB.ADD;
1147
1148 GMIGUTL.DB_ERRNUM := SQLCODE;
1149 GMIGUTL.DB_ERRMSG:= SQLERRM;
1150 RETURN FALSE;
1151 END ic_lots_cpg_insert;
1152
1153 FUNCTION ic_lots_cpg_select
1154 (p_ic_lots_cpg_row IN ic_lots_cpg%ROWTYPE, x_ic_lots_cpg_row IN OUT NOCOPY ic_lots_cpg%ROWTYPE)
1155 RETURN BOOLEAN
1156 IS BEGIN
1157
1158 SELECT * INTO x_ic_lots_cpg_row FROM ic_lots_cpg
1159 WHERE item_id = p_ic_lots_cpg_row.item_id AND
1160 lot_id = p_ic_lots_cpg_row.lot_id;
1161 GMIGUTL.DB_ERRNUM := NULL;
1162 RETURN TRUE;
1163
1164 EXCEPTION
1165 WHEN NO_DATA_FOUND THEN
1166 --Jalaj Srivastava Bug 1977956
1167 --Do not add error using fnd_msg_pub.add
1168 --This is a expected error.
1169
1170 GMIGUTL.DB_ERRNUM := SQLCODE;
1171 GMIGUTL.DB_ERRMSG:= SQLERRM;
1172 RETURN FALSE;
1173
1174 WHEN OTHERS THEN
1175 -- Bug 1764383
1176 -- Added code to return sqlerrm in case of unexpected database errors.
1177 FND_MESSAGE.SET_NAME('GMI', 'GMI_UNEXPECTED_ERROR');
1178 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
1179 FND_MSG_PUB.ADD;
1180
1181 GMIGUTL.DB_ERRNUM := SQLCODE;
1182 GMIGUTL.DB_ERRMSG:= SQLERRM;
1183 RETURN FALSE;
1184 END ic_lots_cpg_select;
1185
1186 FUNCTION ic_lots_sts_select
1187 (p_ic_lots_sts_row IN ic_lots_sts%ROWTYPE, x_ic_lots_sts_row IN OUT NOCOPY ic_lots_sts%ROWTYPE)
1188 RETURN BOOLEAN
1189 IS BEGIN
1190
1191 SELECT * INTO x_ic_lots_sts_row FROM ic_lots_sts
1192 WHERE lot_status= p_ic_lots_sts_row.lot_status;
1193
1194 GMIGUTL.DB_ERRNUM := NULL;
1195 RETURN TRUE;
1196
1197 EXCEPTION
1198 WHEN NO_DATA_FOUND THEN
1199 --Jalaj Srivastava Bug 1977956
1200 --Do not add error using fnd_msg_pub.add
1201 --This is a expected error.
1202
1203 GMIGUTL.DB_ERRNUM := SQLCODE;
1204 GMIGUTL.DB_ERRMSG:= SQLERRM;
1205 RETURN FALSE;
1206
1207 WHEN OTHERS THEN
1208 -- Bug 1764383
1209 -- Added code to return sqlerrm in case of unexpected database errors.
1210 FND_MESSAGE.SET_NAME('GMI', 'GMI_UNEXPECTED_ERROR');
1211 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
1212 FND_MSG_PUB.ADD;
1213
1214 GMIGUTL.DB_ERRNUM := SQLCODE;
1215 GMIGUTL.DB_ERRMSG:= SQLERRM;
1216 RETURN FALSE;
1217 END ic_lots_sts_select;
1218
1219 FUNCTION ic_jrnl_mst_insert
1220 (p_ic_jrnl_mst_row IN ic_jrnl_mst%ROWTYPE, x_ic_jrnl_mst_row IN OUT NOCOPY ic_jrnl_mst%ROWTYPE)
1221 RETURN BOOLEAN
1222 IS
1223 BEGIN
1224 /* Copy input to output */
1225 --Jalaj Srivastava Bug 2483656
1226 --If journal no exists, no need to insert it
1227 IF ( NOT ic_jrnl_mst_select
1228 (p_ic_jrnl_mst_row => p_ic_jrnl_mst_row,
1229 x_ic_jrnl_mst_row => x_ic_jrnl_mst_row
1230 )
1231 ) THEN
1232 x_ic_jrnl_mst_row := p_ic_jrnl_mst_row;
1233
1234 IF x_ic_jrnl_mst_row.journal_id IS NULL
1235 THEN
1236 SELECT gem5_journal_id_s.nextval INTO x_ic_jrnl_mst_row.journal_id FROM DUAL;
1237 END IF;
1238
1239 INSERT INTO ic_jrnl_mst
1240 ( journal_id
1241 , journal_no
1242 , journal_comment
1243 , posting_id
1244 , print_cnt
1245 , posted_ind
1246 , orgn_code
1247 , creation_date
1248 , last_update_date
1249 , created_by
1250 , last_updated_by
1251 , delete_mark
1252 , text_code
1253 , in_use
1254 , attribute1
1255 , attribute2
1256 , attribute3
1257 , attribute4
1258 , attribute5
1259 , attribute6
1260 , attribute7
1261 , attribute8
1262 , attribute9
1263 , attribute10
1264 , attribute11
1265 , attribute12
1266 , attribute13
1267 , attribute14
1268 , attribute15
1269 , attribute16
1270 , attribute17
1271 , attribute18
1272 , attribute19
1273 , attribute20
1274 , attribute21
1275 , attribute22
1276 , attribute23
1277 , attribute24
1278 , attribute25
1279 , attribute26
1280 , attribute27
1281 , attribute28
1282 , attribute29
1283 , attribute30
1284 , attribute_category
1285 )
1286 VALUES
1287 ( x_ic_jrnl_mst_row.journal_id
1288 , x_ic_jrnl_mst_row.journal_no
1289 , x_ic_jrnl_mst_row.journal_comment
1290 , x_ic_jrnl_mst_row.posting_id
1291 , x_ic_jrnl_mst_row.print_cnt
1292 , x_ic_jrnl_mst_row.posted_ind
1293 , x_ic_jrnl_mst_row.orgn_code
1294 , x_ic_jrnl_mst_row.creation_date
1295 , x_ic_jrnl_mst_row.last_update_date
1296 , x_ic_jrnl_mst_row.created_by
1297 , x_ic_jrnl_mst_row.last_updated_by
1298 , x_ic_jrnl_mst_row.delete_mark
1299 , x_ic_jrnl_mst_row.text_code
1300 , x_ic_jrnl_mst_row.in_use
1301 , x_ic_jrnl_mst_row.attribute1
1302 , x_ic_jrnl_mst_row.attribute2
1303 , x_ic_jrnl_mst_row.attribute3
1304 , x_ic_jrnl_mst_row.attribute4
1305 , x_ic_jrnl_mst_row.attribute5
1306 , x_ic_jrnl_mst_row.attribute6
1307 , x_ic_jrnl_mst_row.attribute7
1308 , x_ic_jrnl_mst_row.attribute8
1309 , x_ic_jrnl_mst_row.attribute9
1310 , x_ic_jrnl_mst_row.attribute10
1311 , x_ic_jrnl_mst_row.attribute11
1312 , x_ic_jrnl_mst_row.attribute12
1313 , x_ic_jrnl_mst_row.attribute13
1314 , x_ic_jrnl_mst_row.attribute14
1315 , x_ic_jrnl_mst_row.attribute15
1316 , x_ic_jrnl_mst_row.attribute16
1317 , x_ic_jrnl_mst_row.attribute17
1318 , x_ic_jrnl_mst_row.attribute18
1319 , x_ic_jrnl_mst_row.attribute19
1320 , x_ic_jrnl_mst_row.attribute20
1321 , x_ic_jrnl_mst_row.attribute21
1322 , x_ic_jrnl_mst_row.attribute22
1323 , x_ic_jrnl_mst_row.attribute23
1324 , x_ic_jrnl_mst_row.attribute24
1325 , x_ic_jrnl_mst_row.attribute25
1326 , x_ic_jrnl_mst_row.attribute26
1327 , x_ic_jrnl_mst_row.attribute27
1328 , x_ic_jrnl_mst_row.attribute28
1329 , x_ic_jrnl_mst_row.attribute29
1330 , x_ic_jrnl_mst_row.attribute30
1331 , x_ic_jrnl_mst_row.attribute_category
1332 );
1333 END IF;
1334 GMIGUTL.DB_ERRNUM := NULL;
1335 RETURN TRUE;
1336
1337 EXCEPTION
1338 WHEN OTHERS THEN
1339 -- Bug 1764383
1340 -- Added code to return sqlerrm in case of unexpected database errors.
1341 FND_MESSAGE.SET_NAME('GMI', 'GMI_UNEXPECTED_ERROR');
1342 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
1343 FND_MSG_PUB.ADD;
1344
1345 GMIGUTL.DB_ERRNUM := SQLCODE;
1346 GMIGUTL.DB_ERRMSG:= SQLERRM;
1347 RETURN FALSE;
1348 END ic_jrnl_mst_insert;
1349
1350 FUNCTION ic_jrnl_mst_select
1351 (p_ic_jrnl_mst_row IN ic_jrnl_mst%ROWTYPE, x_ic_jrnl_mst_row IN OUT NOCOPY ic_jrnl_mst%ROWTYPE)
1352 RETURN BOOLEAN
1353 IS
1354 BEGIN
1355
1356 IF p_ic_jrnl_mst_row.journal_no IS NOT NULL AND
1357 p_ic_jrnl_mst_row.orgn_code IS NOT NULL
1358 THEN
1359 SELECT * INTO x_ic_jrnl_mst_row FROM ic_jrnl_mst
1360 WHERE orgn_code = p_ic_jrnl_mst_row.orgn_code AND
1361 journal_no = p_ic_jrnl_mst_row.journal_no;
1362 ELSE
1363 SELECT * INTO x_ic_jrnl_mst_row FROM ic_jrnl_mst
1364 WHERE journal_id = p_ic_jrnl_mst_row.journal_id;
1365 END IF;
1366 GMIGUTL.DB_ERRNUM := NULL;
1367 RETURN TRUE;
1368
1369 EXCEPTION
1370 WHEN NO_DATA_FOUND THEN
1371 --Jalaj Srivastava Bug 1977956
1372 --Do not add error using fnd_msg_pub.add
1373 --This is a expected error.
1374
1375 GMIGUTL.DB_ERRNUM := SQLCODE;
1376 GMIGUTL.DB_ERRMSG:= SQLERRM;
1377 RETURN FALSE;
1378
1379 WHEN OTHERS THEN
1380 -- Bug 1764383
1381 -- Added code to return sqlerrm in case of unexpected database errors.
1382 FND_MESSAGE.SET_NAME('GMI', 'GMI_UNEXPECTED_ERROR');
1383 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
1384 FND_MSG_PUB.ADD;
1385
1386 GMIGUTL.DB_ERRNUM := SQLCODE;
1387 GMIGUTL.DB_ERRMSG:= SQLERRM;
1388 RETURN FALSE;
1389 END ic_jrnl_mst_select;
1390
1391 FUNCTION ic_adjs_jnl_insert
1392 (p_ic_adjs_jnl_row IN ic_adjs_jnl%ROWTYPE, x_ic_adjs_jnl_row IN OUT NOCOPY ic_adjs_jnl%ROWTYPE)
1393 RETURN BOOLEAN
1394 IS
1395 BEGIN
1396 /* Copy input to output */
1397
1398 x_ic_adjs_jnl_row := p_ic_adjs_jnl_row;
1399
1400 IF x_ic_adjs_jnl_row.doc_id IS NULL
1401 THEN
1402 SELECT gem5_doc_id_s.nextval INTO x_ic_adjs_jnl_row.doc_id FROM dual;
1403 END IF;
1404
1405 IF x_ic_adjs_jnl_row.line_id IS NULL
1406 THEN
1407 SELECT gem5_line_id_s.nextval INTO x_ic_adjs_jnl_row.line_id FROM dual;
1408 END IF;
1409
1410 INSERT INTO ic_adjs_jnl
1411 ( trans_type
1412 , trans_flag
1413 , doc_id
1414 , doc_line
1415 , journal_id
1416 , completed_ind
1417 , whse_code
1418 , reason_code
1419 , doc_date
1420 , item_id
1421 , item_um
1422 , item_um2
1423 , lot_id
1424 , location
1425 , qty
1426 , qty2
1427 , qc_grade
1428 , lot_status
1429 , line_type
1430 , line_id
1431 , co_code
1432 , orgn_code
1433 , no_inv
1434 , no_trans
1435 , creation_date
1436 , created_by
1437 , last_update_date
1438 , trans_cnt
1439 , last_updated_by
1440 , acctg_unit_id
1441 , acct_id
1442 )
1443 VALUES
1444 ( x_ic_adjs_jnl_row.trans_type
1445 , x_ic_adjs_jnl_row.trans_flag
1446 , x_ic_adjs_jnl_row.doc_id
1447 , x_ic_adjs_jnl_row.doc_line
1448 , x_ic_adjs_jnl_row.journal_id
1449 , x_ic_adjs_jnl_row.completed_ind
1450 , x_ic_adjs_jnl_row.whse_code
1451 , x_ic_adjs_jnl_row.reason_code
1452 , x_ic_adjs_jnl_row.doc_date
1453 , x_ic_adjs_jnl_row.item_id
1454 , x_ic_adjs_jnl_row.item_um
1455 , x_ic_adjs_jnl_row.item_um2
1456 , x_ic_adjs_jnl_row.lot_id
1457 , x_ic_adjs_jnl_row.location
1458 , x_ic_adjs_jnl_row.qty
1459 , x_ic_adjs_jnl_row.qty2
1460 , x_ic_adjs_jnl_row.qc_grade
1461 , x_ic_adjs_jnl_row.lot_status
1462 , x_ic_adjs_jnl_row.line_type
1463 , x_ic_adjs_jnl_row.line_id
1464 , x_ic_adjs_jnl_row.co_code
1465 , x_ic_adjs_jnl_row.orgn_code
1466 , x_ic_adjs_jnl_row.no_inv
1467 , x_ic_adjs_jnl_row.no_trans
1468 , x_ic_adjs_jnl_row.creation_date
1469 , x_ic_adjs_jnl_row.created_by
1470 , x_ic_adjs_jnl_row.last_update_date
1471 , x_ic_adjs_jnl_row.trans_cnt
1472 , x_ic_adjs_jnl_row.last_updated_by
1473 , x_ic_adjs_jnl_row.acctg_unit_id
1474 , x_ic_adjs_jnl_row.acct_id
1475 );
1476
1477 GMIGUTL.DB_ERRNUM := NULL;
1478 RETURN TRUE;
1479
1480 EXCEPTION
1481 WHEN OTHERS THEN
1482 -- Bug 1764383
1483 -- Added code to return sqlerrm in case of unexpected database errors.
1484 FND_MESSAGE.SET_NAME('GMI', 'GMI_UNEXPECTED_ERROR');
1485 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
1486 FND_MSG_PUB.ADD;
1487
1488 GMIGUTL.DB_ERRNUM := SQLCODE;
1489 GMIGUTL.DB_ERRMSG:= SQLERRM;
1490 RETURN FALSE;
1491 END ic_adjs_jnl_insert;
1492
1493 FUNCTION sy_reas_cds_select
1494 (p_sy_reas_cds_row IN sy_reas_cds%ROWTYPE, x_sy_reas_cds_row IN OUT NOCOPY sy_reas_cds%ROWTYPE)
1495 RETURN BOOLEAN
1496 IS
1497 BEGIN
1498 SELECT * INTO x_sy_reas_cds_row FROM sy_reas_cds
1499 WHERE reason_code = p_sy_reas_cds_row.reason_code;
1500
1501 GMIGUTL.DB_ERRNUM := NULL;
1502 RETURN TRUE;
1503
1504 EXCEPTION
1505 WHEN NO_DATA_FOUND THEN
1506 --Jalaj Srivastava Bug 1977956
1507 --Do not add error using fnd_msg_pub.add
1508 --This is a expected error.
1509
1510 GMIGUTL.DB_ERRNUM := SQLCODE;
1511 GMIGUTL.DB_ERRMSG:= SQLERRM;
1512 RETURN FALSE;
1513
1514 WHEN OTHERS THEN
1515 -- Bug 1764383
1516 -- Added code to return sqlerrm in case of unexpected database errors.
1517 FND_MESSAGE.SET_NAME('GMI', 'GMI_UNEXPECTED_ERROR');
1518 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
1519 FND_MSG_PUB.ADD;
1520
1521 GMIGUTL.DB_ERRNUM := SQLCODE;
1522 GMIGUTL.DB_ERRMSG:= SQLERRM;
1523 RETURN FALSE;
1524 END sy_reas_cds_select;
1525
1526
1527
1528
1529
1530 FUNCTION ic_item_cnv_insert
1531 (p_ic_item_cnv_row IN ic_item_cnv%ROWTYPE, x_ic_item_cnv_row IN OUT NOCOPY ic_item_cnv%ROWTYPE)
1532 RETURN BOOLEAN
1533
1534
1535 IS
1536 x_conv_audit_id GMI_ITEM_CONV_AUDIT.CONV_AUDIT_ID%TYPE;
1537 x_reason_code SY_REAS_CDS.REASON_CODE%TYPE := null;
1538 x_type_factor IC_ITEM_CNV.TYPE_FACTOR%TYPE := null;
1539 x_event_spec_disp_id IC_ITEM_CNV.EVENT_SPEC_DISP_ID%TYPE := null;
1540
1541 BEGIN
1542
1543 x_ic_item_cnv_row := p_ic_item_cnv_row;
1544
1545 IF (x_ic_item_cnv_row.conversion_id IS NULL) THEN
1546 select gmi_conversion_id_s.nextval into
1547 x_ic_item_cnv_row.conversion_id from dual;
1548 END IF;
1549 INSERT INTO ic_item_cnv
1550 ( item_id
1551 , lot_id
1552 , um_type
1553 , type_factor
1554 , creation_date
1555 , last_update_date
1556 , created_by
1557 , last_updated_by
1558 , trans_cnt
1559 , delete_mark
1560 , text_code
1561 , type_factorrev
1562 , last_update_login
1563 , conversion_id
1564 )
1565 VALUES
1566 ( p_ic_item_cnv_row.item_id
1567 , p_ic_item_cnv_row.lot_id
1568 , p_ic_item_cnv_row.um_type
1569 , p_ic_item_cnv_row.type_factor
1570 , p_ic_item_cnv_row.creation_date
1571 , p_ic_item_cnv_row.last_update_date
1572 , p_ic_item_cnv_row.created_by
1573 , p_ic_item_cnv_row.last_updated_by
1574 , p_ic_item_cnv_row.trans_cnt
1575 , p_ic_item_cnv_row.delete_mark
1576 , p_ic_item_cnv_row.text_code
1577 , p_ic_item_cnv_row.type_factorrev
1578 , p_ic_item_cnv_row.last_update_login
1579 , p_ic_item_cnv_row.conversion_id
1580 );
1581
1582 /* 15-Apr-2003 Joe DiIorio Bug 2880585 11.5.1K - */
1583
1584 /* 24-June-2003 Joe DiIorio Bug 3022564 11.5.10K - */
1585 select gmi_conv_audit_id_s.nextval into
1586 x_conv_audit_id from dual;
1587
1588 INSERT INTO gmi_item_conv_audit
1589 ( conv_audit_id
1590 , conversion_id
1591 , conversion_date
1592 , reason_code
1593 , old_type_factor
1594 , new_type_factor
1595 , event_spec_disp_id
1596 , created_by
1597 , creation_date
1598 , last_updated_by
1599 , last_update_login
1600 , last_update_date
1601 )
1602 VALUES
1603 ( x_conv_audit_id
1604 , p_ic_item_cnv_row.conversion_id
1605 , p_ic_item_cnv_row.creation_date
1606 , x_reason_code
1607 , x_type_factor
1608 , p_ic_item_cnv_row.type_factor
1609 , x_event_spec_disp_id
1610 , p_ic_item_cnv_row.created_by
1611 , p_ic_item_cnv_row.creation_date
1612 , p_ic_item_cnv_row.last_updated_by
1613 , p_ic_item_cnv_row.last_update_login
1614 , p_ic_item_cnv_row.last_update_date
1615 );
1616
1617
1618 GMIGUTL.DB_ERRNUM := NULL;
1619 RETURN TRUE;
1620
1621 EXCEPTION
1622 WHEN OTHERS THEN
1623 -- Bug 1764383
1624 -- Added code to return sqlerrm in case of unexpected database errors.
1625 FND_MESSAGE.SET_NAME('GMI', 'GMI_UNEXPECTED_ERROR');
1626 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
1627 FND_MSG_PUB.ADD;
1628
1629 GMIGUTL.DB_ERRNUM := SQLCODE;
1630 GMIGUTL.DB_ERRMSG:= SQLERRM;
1631 RETURN FALSE;
1632 END ic_item_cnv_insert;
1633
1634 FUNCTION sy_uoms_mst_select
1635 (p_sy_uoms_mst_row IN sy_uoms_mst%ROWTYPE, x_sy_uoms_mst_row IN OUT NOCOPY sy_uoms_mst%ROWTYPE)
1636 RETURN BOOLEAN
1637 IS
1638 BEGIN
1639 SELECT * INTO x_sy_uoms_mst_row FROM sy_uoms_mst
1640 WHERE um_code = p_sy_uoms_mst_row.um_code AND delete_mark=0;
1641
1642 GMIGUTL.DB_ERRNUM := NULL;
1643 RETURN TRUE;
1644
1645 EXCEPTION
1646 WHEN NO_DATA_FOUND THEN
1647 --Jalaj Srivastava Bug 1977956
1648 --Do not add error using fnd_msg_pub.add
1649 --This is a expected error.
1650
1651 GMIGUTL.DB_ERRNUM := SQLCODE;
1652 GMIGUTL.DB_ERRMSG:= SQLERRM;
1653 RETURN FALSE;
1654
1655 WHEN OTHERS THEN
1656 -- Bug 1764383
1657 -- Added code to return sqlerrm in case of unexpected database errors.
1658 FND_MESSAGE.SET_NAME('GMI', 'GMI_UNEXPECTED_ERROR');
1659 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
1660 FND_MSG_PUB.ADD;
1661
1662 GMIGUTL.DB_ERRNUM := SQLCODE;
1663 GMIGUTL.DB_ERRMSG:= SQLERRM;
1664 RETURN FALSE;
1665 END sy_uoms_mst_select;
1666
1667 FUNCTION sy_uoms_typ_select
1668 (p_sy_uoms_typ_row IN sy_uoms_typ%ROWTYPE, x_sy_uoms_typ_row IN OUT NOCOPY sy_uoms_typ%ROWTYPE)
1669 RETURN BOOLEAN
1670 IS
1671 BEGIN
1672 SELECT * INTO x_sy_uoms_typ_row FROM sy_uoms_typ
1673 WHERE um_type = p_sy_uoms_typ_row.um_type AND delete_mark=0;
1674
1675 GMIGUTL.DB_ERRNUM := NULL;
1676 RETURN TRUE;
1677
1678 EXCEPTION
1679 WHEN NO_DATA_FOUND THEN
1680 --Jalaj Srivastava Bug 1977956
1681 --Do not add error using fnd_msg_pub.add
1682 --This is a expected error.
1683
1684 GMIGUTL.DB_ERRNUM := SQLCODE;
1685 GMIGUTL.DB_ERRMSG:= SQLERRM;
1686 RETURN FALSE;
1687
1688 WHEN OTHERS THEN
1689 -- Bug 1764383
1690 -- Added code to return sqlerrm in case of unexpected database errors.
1691 FND_MESSAGE.SET_NAME('GMI', 'GMI_UNEXPECTED_ERROR');
1692 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
1693 FND_MSG_PUB.ADD;
1694
1695 GMIGUTL.DB_ERRNUM := SQLCODE;
1696 GMIGUTL.DB_ERRMSG:= SQLERRM;
1697 RETURN FALSE;
1698 END sy_uoms_typ_select;
1699
1700
1701
1702
1703
1704
1705 FUNCTION ic_whse_mst_select
1706 (p_ic_whse_mst_row IN ic_whse_mst%ROWTYPE, x_ic_whse_mst_row IN OUT NOCOPY ic_whse_mst%ROWTYPE)
1707 RETURN BOOLEAN
1708 IS
1709 BEGIN
1710 SELECT * INTO x_ic_whse_mst_row FROM ic_whse_mst
1711 WHERE whse_code = p_ic_whse_mst_row.whse_code AND delete_mark=0;
1712
1713 GMIGUTL.DB_ERRNUM := NULL;
1714 RETURN TRUE;
1715
1716 EXCEPTION
1717 WHEN NO_DATA_FOUND THEN
1718 --Jalaj Srivastava Bug 1977956
1719 --Do not add error using fnd_msg_pub.add
1720 --This is a expected error.
1721
1722 GMIGUTL.DB_ERRNUM := SQLCODE;
1723 GMIGUTL.DB_ERRMSG:= SQLERRM;
1724 RETURN FALSE;
1725
1726 WHEN OTHERS THEN
1727 -- Bug 1764383
1728 -- Added code to return sqlerrm in case of unexpected database errors.
1729 FND_MESSAGE.SET_NAME('GMI', 'GMI_UNEXPECTED_ERROR');
1730 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
1731 FND_MSG_PUB.ADD;
1732
1733 GMIGUTL.DB_ERRNUM := SQLCODE;
1734 GMIGUTL.DB_ERRMSG:= SQLERRM;
1735 RETURN FALSE;
1736 END ic_whse_mst_select;
1737
1738 FUNCTION ic_loct_inv_select
1739 (p_ic_loct_inv_row IN ic_loct_inv%ROWTYPE, x_ic_loct_inv_row IN OUT NOCOPY ic_loct_inv%ROWTYPE)
1740 RETURN BOOLEAN
1741 IS
1742 BEGIN
1743 SELECT * INTO x_ic_loct_inv_row FROM ic_loct_inv
1744 WHERE whse_code = NVL(p_ic_loct_inv_row.whse_code, whse_code) AND
1745 item_id = p_ic_loct_inv_row.item_id AND
1746 lot_id = p_ic_loct_inv_row.lot_id AND
1747 location = NVL(p_ic_loct_inv_row.location, location) AND
1748 ROWNUM = 1
1749 AND delete_mark=0;
1750
1751 GMIGUTL.DB_ERRNUM := NULL;
1752 RETURN TRUE;
1753
1754 EXCEPTION
1755 WHEN NO_DATA_FOUND
1756 THEN
1757 x_ic_loct_inv_row.loct_onhand:= NULL;
1758 GMIGUTL.DB_ERRNUM := SQLCODE;
1759 GMIGUTL.DB_ERRMSG:= SQLERRM;
1760 RETURN FALSE;
1761
1762 WHEN OTHERS THEN
1763 -- Bug 1764383
1764 -- Added code to return sqlerrm in case of unexpected database errors.
1765 FND_MESSAGE.SET_NAME('GMI', 'GMI_UNEXPECTED_ERROR');
1766 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
1767 FND_MSG_PUB.ADD;
1768
1769 GMIGUTL.DB_ERRNUM := SQLCODE;
1770 GMIGUTL.DB_ERRMSG:= SQLERRM;
1771 RETURN FALSE;
1772 END ic_loct_inv_select;
1773
1774 FUNCTION qc_grad_mst_select
1775 (p_qc_grad_mst_row IN qc_grad_mst%ROWTYPE, x_qc_grad_mst_row IN OUT NOCOPY qc_grad_mst%ROWTYPE)
1776 RETURN BOOLEAN
1777 IS
1778 BEGIN
1779 SELECT * INTO x_qc_grad_mst_row FROM qc_grad_mst
1780 WHERE qc_grade = p_qc_grad_mst_row.qc_grade AND delete_mark=0;
1781
1782 GMIGUTL.DB_ERRNUM := NULL;
1783 RETURN TRUE;
1784
1785 EXCEPTION
1786 WHEN NO_DATA_FOUND THEN
1787 --Jalaj Srivastava Bug 1977956
1788 --Do not add error using fnd_msg_pub.add
1789 --This is a expected error.
1790
1791 GMIGUTL.DB_ERRNUM := SQLCODE;
1792 GMIGUTL.DB_ERRMSG:= SQLERRM;
1793 RETURN FALSE;
1794
1795 WHEN OTHERS THEN
1796 -- Bug 1764383
1797 -- Added code to return sqlerrm in case of unexpected database errors.
1798 FND_MESSAGE.SET_NAME('GMI', 'GMI_UNEXPECTED_ERROR');
1799 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
1800 FND_MSG_PUB.ADD;
1801
1802 GMIGUTL.DB_ERRNUM := SQLCODE;
1803 GMIGUTL.DB_ERRMSG:= SQLERRM;
1804 RETURN FALSE;
1805 END qc_grad_mst_select;
1806
1807 FUNCTION qc_actn_mst_select
1808 (p_qc_actn_mst_row IN qc_actn_mst%ROWTYPE, x_qc_actn_mst_row IN OUT NOCOPY qc_actn_mst%ROWTYPE)
1809 RETURN BOOLEAN
1810 IS
1811 BEGIN
1812 SELECT * INTO x_qc_actn_mst_row FROM qc_actn_mst
1813 WHERE action_code = p_qc_actn_mst_row.action_code AND delete_mark=0;
1814
1815 GMIGUTL.DB_ERRNUM := NULL;
1816 RETURN TRUE;
1817
1818 EXCEPTION
1819 WHEN NO_DATA_FOUND THEN
1820 --Jalaj Srivastava Bug 1977956
1821 --Do not add error using fnd_msg_pub.add
1822 --This is a expected error.
1823
1824 GMIGUTL.DB_ERRNUM := SQLCODE;
1825 GMIGUTL.DB_ERRMSG:= SQLERRM;
1826 RETURN FALSE;
1827
1828 WHEN OTHERS THEN
1829 -- Bug 1764383
1830 -- Added code to return sqlerrm in case of unexpected database errors.
1831 FND_MESSAGE.SET_NAME('GMI', 'GMI_UNEXPECTED_ERROR');
1832 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
1833 FND_MSG_PUB.ADD;
1834
1835 GMIGUTL.DB_ERRNUM := SQLCODE;
1836 GMIGUTL.DB_ERRMSG:= SQLERRM;
1837 RETURN FALSE;
1838 END qc_actn_mst_select;
1839
1840 FUNCTION po_vend_mst_select
1841 (p_po_vend_mst_row IN po_vend_mst%ROWTYPE, x_po_vend_mst_row IN OUT NOCOPY po_vend_mst%ROWTYPE)
1842 RETURN BOOLEAN
1843 IS
1844 BEGIN
1845 SELECT * INTO x_po_vend_mst_row FROM po_vend_mst
1846 WHERE vendor_no=p_po_vend_mst_row.vendor_no AND delete_mark=0 AND
1847 rownum=1;
1848
1849 GMIGUTL.DB_ERRNUM := NULL;
1850 RETURN TRUE;
1851
1852 EXCEPTION
1853 WHEN NO_DATA_FOUND THEN
1854 --Jalaj Srivastava Bug 1977956
1855 --Do not add error using fnd_msg_pub.add
1856 --This is a expected error.
1857
1858 GMIGUTL.DB_ERRNUM := SQLCODE;
1859 GMIGUTL.DB_ERRMSG:= SQLERRM;
1860 RETURN FALSE;
1861
1862 WHEN OTHERS THEN
1863 -- Bug 1764383
1864 -- Added code to return sqlerrm in case of unexpected database errors.
1865 FND_MESSAGE.SET_NAME('GMI', 'GMI_UNEXPECTED_ERROR');
1866 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
1867 FND_MSG_PUB.ADD;
1868
1869 GMIGUTL.DB_ERRNUM := SQLCODE;
1870 GMIGUTL.DB_ERRMSG:= SQLERRM;
1871 RETURN FALSE;
1872 END po_vend_mst_select;
1873 /*
1874
1875 FUNCTION ic_xfer_mst_select
1876 (p_ic_xfer_mst_row IN ic_xfer_mst%ROWTYPE, x_ic_xfer_mst_row IN OUT NOCOPY ic_xfer_mst%ROWTYPE)
1877 RETURN BOOLEAN
1878 IS
1879 BEGIN
1880 SELECT * INTO x_ic_xfer_mst_row FROM ic_xfer_mst
1881 WHERE orgn_code = p_ic_xfer_mst_row.orgn_code AND
1882 transfer_no = p_ic_xfer_mst_row.transfer_no;
1883
1884 GMIGUTL.DB_ERRNUM := NULL;
1885 RETURN TRUE;
1886
1887 EXCEPTION
1888 WHEN NO_DATA_FOUND THEN
1889 --Jalaj Srivastava Bug 1977956
1890 --Do not add error using fnd_msg_pub.add
1891 --This is a expected error.
1892
1893 GMIGUTL.DB_ERRNUM := SQLCODE;
1894 GMIGUTL.DB_ERRMSG:= SQLERRM;
1895 RETURN FALSE;
1896
1897 WHEN OTHERS THEN
1898 -- Bug 1764383
1899 -- Added code to return sqlerrm in case of unexpected database errors.
1900 FND_MESSAGE.SET_NAME('GMI', 'GMI_UNEXPECTED_ERROR');
1901 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
1902 FND_MSG_PUB.ADD;
1903
1904 GMIGUTL.DB_ERRNUM := SQLCODE;
1905 GMIGUTL.DB_ERRMSG:= SQLERRM;
1906 RETURN FALSE;
1907 END ic_xfer_mst_select;
1908
1909
1910 FUNCTION ic_xfer_mst_insert
1911 ( p_ic_xfer_mst_row IN ic_xfer_mst%ROWTYPE
1912 , x_ic_xfer_mst_row IN OUT NOCOPY ic_xfer_mst%ROWTYPE)
1913 RETURN BOOLEAN
1914 IS
1915 BEGIN
1916 x_ic_xfer_mst_row := p_ic_xfer_mst_row;
1917 IF x_ic_xfer_mst_row.transfer_id IS NULL
1918 THEN
1919 SELECT gem5_transfer_id.nextval INTO x_ic_xfer_mst_row.transfer_id
1920 FROM dual;
1921 END IF;
1922
1923 INSERT INTO ic_xfer_mst
1924 (transfer_id,
1925 transfer_no,
1926 orgn_code,
1927 transfer_status,
1928 item_id,
1929 lot_id,
1930 lot_status,
1931 release_reason_code,
1932 receive_reason_code,
1933 cancel_reason_code,
1934 from_warehouse,
1935 from_location,
1936 to_warehouse,
1937 to_location,
1938 release_quantity1,
1939 release_quantity2,
1940 release_uom1,
1941 release_uom2,
1942 receive_quantity1,
1943 receive_quantity2,
1944 scheduled_release_date,
1945 actual_release_date,
1946 scheduled_receive_date,
1947 actual_receive_date,
1948 cancel_date,
1949 delete_mark,
1950 received_by,
1951 released_by,
1952 canceled_by,
1953 text_code,
1954 comments,
1955 attribute_category ,
1956 attribute1 ,
1957 attribute2 ,
1958 attribute3 ,
1959 attribute4 ,
1960 attribute5 ,
1961 attribute6 ,
1962 attribute7 ,
1963 attribute8 ,
1964 attribute9 ,
1965 attribute10 ,
1966 attribute11 ,
1967 attribute12 ,
1968 attribute13 ,
1969 attribute14 ,
1970 attribute15 ,
1971 attribute16 ,
1972 attribute17 ,
1973 attribute18 ,
1974 attribute19 ,
1975 attribute20 ,
1976 attribute21 ,
1977 attribute22 ,
1978 attribute23 ,
1979 attribute24 ,
1980 attribute25 ,
1981 attribute26 ,
1982 attribute27 ,
1983 attribute28 ,
1984 attribute29 ,
1985 attribute30 ,
1986 created_by ,
1987 creation_date ,
1988 last_updated_by ,
1989 last_update_date ,
1990 last_update_login)
1991 VALUES
1992 (x_ic_xfer_mst_row.transfer_id ,
1993 x_ic_xfer_mst_row.transfer_no ,
1994 x_ic_xfer_mst_row.orgn_code ,
1995 x_ic_xfer_mst_row.transfer_status ,
1996 x_ic_xfer_mst_row.item_id ,
1997 x_ic_xfer_mst_row.lot_id ,
1998 x_ic_xfer_mst_row.lot_status ,
1999 x_ic_xfer_mst_row.release_reason_code ,
2000 x_ic_xfer_mst_row.receive_reason_code ,
2001 x_ic_xfer_mst_row.cancel_reason_code ,
2002 x_ic_xfer_mst_row.from_warehouse ,
2003 x_ic_xfer_mst_row.from_location ,
2004 x_ic_xfer_mst_row.to_warehouse ,
2005 x_ic_xfer_mst_row.to_location ,
2006 x_ic_xfer_mst_row.release_quantity1 ,
2007 x_ic_xfer_mst_row.release_quantity2 ,
2008 x_ic_xfer_mst_row.release_uom1,
2009 x_ic_xfer_mst_row.release_uom2,
2010 x_ic_xfer_mst_row.receive_quantity1,
2011 x_ic_xfer_mst_row.receive_quantity2,
2012 x_ic_xfer_mst_row.scheduled_release_date ,
2013 x_ic_xfer_mst_row.actual_release_date ,
2014 x_ic_xfer_mst_row.scheduled_receive_date ,
2015 x_ic_xfer_mst_row.actual_receive_date ,
2016 x_ic_xfer_mst_row.cancel_date , x_ic_xfer_mst_row.delete_mark ,
2017 x_ic_xfer_mst_row.received_by ,
2018 x_ic_xfer_mst_row.released_by ,
2019 x_ic_xfer_mst_row.canceled_by ,
2020 x_ic_xfer_mst_row.text_code ,
2021 x_ic_xfer_mst_row.comments ,
2022 x_ic_xfer_mst_row.attribute_category ,
2023 x_ic_xfer_mst_row.attribute1 ,
2024 x_ic_xfer_mst_row.attribute2 ,
2025 x_ic_xfer_mst_row.attribute3 ,
2026 x_ic_xfer_mst_row.attribute4 ,
2027 x_ic_xfer_mst_row.attribute5 ,
2028 x_ic_xfer_mst_row.attribute6 ,
2029 x_ic_xfer_mst_row.attribute7 ,
2030 x_ic_xfer_mst_row.attribute8 ,
2031 x_ic_xfer_mst_row.attribute9 ,
2032 x_ic_xfer_mst_row.attribute10 ,
2033 x_ic_xfer_mst_row.attribute11 ,
2034 x_ic_xfer_mst_row.attribute12 ,
2035 x_ic_xfer_mst_row.attribute13 ,
2036 x_ic_xfer_mst_row.attribute14 ,
2037 x_ic_xfer_mst_row.attribute15 ,
2038 x_ic_xfer_mst_row.attribute16 ,
2039 x_ic_xfer_mst_row.attribute17 ,
2040 x_ic_xfer_mst_row.attribute18 ,
2041 x_ic_xfer_mst_row.attribute19 ,
2042 x_ic_xfer_mst_row.attribute20 ,
2043 x_ic_xfer_mst_row.attribute21 ,
2044 x_ic_xfer_mst_row.attribute22 ,
2045 x_ic_xfer_mst_row.attribute23 ,
2046 x_ic_xfer_mst_row.attribute24 ,
2047 x_ic_xfer_mst_row.attribute25 ,
2048 x_ic_xfer_mst_row.attribute26 ,
2049 x_ic_xfer_mst_row.attribute27 ,
2050 x_ic_xfer_mst_row.attribute28 ,
2051 x_ic_xfer_mst_row.attribute29 ,
2052 x_ic_xfer_mst_row.attribute30 ,
2053 x_ic_xfer_mst_row.created_by ,
2054 x_ic_xfer_mst_row.creation_date ,
2055 x_ic_xfer_mst_row.last_updated_by ,
2056 x_ic_xfer_mst_row.last_update_date ,
2057 x_ic_xfer_mst_row.last_update_login);
2058
2059 GMIGUTL.DB_ERRNUM := NULL;
2060 RETURN TRUE;
2061
2062 EXCEPTION
2063 WHEN OTHERS THEN
2064 -- Bug 1764383
2065 -- Added code to return sqlerrm in case of unexpected database errors.
2066 FND_MESSAGE.SET_NAME('GMI', 'GMI_UNEXPECTED_ERROR');
2067 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
2068 FND_MSG_PUB.ADD;
2069
2070 GMIGUTL.DB_ERRNUM := SQLCODE;
2071 GMIGUTL.DB_ERRMSG:= SQLERRM;
2072 RETURN FALSE;
2073
2074 END ic_xfer_mst_insert;
2075
2076
2077 FUNCTION ic_xfer_mst_update
2078 ( p_ic_xfer_mst_row IN ic_xfer_mst%ROWTYPE
2079 , x_ic_xfer_mst_row IN OUT NOCOPY ic_xfer_mst%ROWTYPE
2080 )
2081 RETURN BOOLEAN
2082 IS
2083 BEGIN
2084
2085 UPDATE ic_xfer_mst
2086 SET
2087 transfer_status = x_ic_xfer_mst_row.transfer_status,
2088 item_id = x_ic_xfer_mst_row.item_id,
2089 lot_id = x_ic_xfer_mst_row.lot_id,
2090 lot_status = x_ic_xfer_mst_row.lot_status,
2091 release_reason_code = x_ic_xfer_mst_row.release_reason_code,
2092 receive_reason_code = x_ic_xfer_mst_row.receive_reason_code,
2093 cancel_reason_code = x_ic_xfer_mst_row.cancel_reason_code,
2094 from_warehouse = x_ic_xfer_mst_row.from_warehouse,
2095 from_location = x_ic_xfer_mst_row.from_location,
2096 to_warehouse = x_ic_xfer_mst_row.to_warehouse,
2097 to_location = x_ic_xfer_mst_row.to_location,
2098 release_quantity1 = x_ic_xfer_mst_row.release_quantity1,
2099 release_quantity2 = x_ic_xfer_mst_row.release_quantity2,
2100 release_uom1 = x_ic_xfer_mst_row.release_uom1,
2101 release_uom2 = x_ic_xfer_mst_row.release_uom2,
2102 receive_quantity1 = x_ic_xfer_mst_row.receive_quantity1,
2103 receive_quantity2 = x_ic_xfer_mst_row.receive_quantity2,
2104 scheduled_release_date = x_ic_xfer_mst_row.scheduled_release_date,
2105 actual_release_date = x_ic_xfer_mst_row.actual_release_date,
2106 scheduled_receive_date = x_ic_xfer_mst_row.scheduled_receive_date,
2107 actual_receive_date = x_ic_xfer_mst_row.actual_receive_date,
2108 cancel_date = x_ic_xfer_mst_row.cancel_date,
2109 delete_mark = x_ic_xfer_mst_row.delete_mark,
2110 received_by = x_ic_xfer_mst_row.received_by,
2111 released_by = x_ic_xfer_mst_row.released_by,
2112 canceled_by = x_ic_xfer_mst_row.canceled_by,
2113 text_code = x_ic_xfer_mst_row.text_code,
2114 comments = x_ic_xfer_mst_row.comments,
2115 attribute_category = x_ic_xfer_mst_row.attribute_category,
2116 attribute1 = x_ic_xfer_mst_row.attribute1,
2117 attribute2 = x_ic_xfer_mst_row.attribute2,
2118 attribute3 = x_ic_xfer_mst_row.attribute3,
2119 attribute4 = x_ic_xfer_mst_row.attribute4,
2120 attribute5 = x_ic_xfer_mst_row.attribute5,
2121 attribute6 = x_ic_xfer_mst_row.attribute6,
2122 attribute7 = x_ic_xfer_mst_row.attribute7,
2123 attribute8 = x_ic_xfer_mst_row.attribute8,
2124 attribute9 = x_ic_xfer_mst_row.attribute9,
2125 attribute10 = x_ic_xfer_mst_row.attribute10,
2126 attribute11 = x_ic_xfer_mst_row.attribute11,
2127 attribute12 = x_ic_xfer_mst_row.attribute12,
2128 attribute13 = x_ic_xfer_mst_row.attribute13,
2129 attribute14 = x_ic_xfer_mst_row.attribute14,
2130 attribute15 = x_ic_xfer_mst_row.attribute15,
2131 attribute16 = x_ic_xfer_mst_row.attribute16,
2132 attribute17 = x_ic_xfer_mst_row.attribute17,
2133 attribute18 = x_ic_xfer_mst_row.attribute18,
2134 attribute19 = x_ic_xfer_mst_row.attribute19,
2135 attribute20 = x_ic_xfer_mst_row.attribute20,
2136 attribute21 = x_ic_xfer_mst_row.attribute21,
2137 attribute22 = x_ic_xfer_mst_row.attribute22,
2138 attribute23 = x_ic_xfer_mst_row.attribute23,
2139 attribute24 = x_ic_xfer_mst_row.attribute24,
2140 attribute25 = x_ic_xfer_mst_row.attribute25,
2141 attribute26 = x_ic_xfer_mst_row.attribute26,
2142 attribute27 = x_ic_xfer_mst_row.attribute27,
2143 attribute28 = x_ic_xfer_mst_row.attribute28,
2144 attribute29 = x_ic_xfer_mst_row.attribute29,
2145 attribute30 = x_ic_xfer_mst_row.attribute30,
2146 created_by = x_ic_xfer_mst_row.created_by,
2147 creation_date = x_ic_xfer_mst_row.creation_date,
2148 last_updated_by = x_ic_xfer_mst_row.last_updated_by,
2149 last_update_date = x_ic_xfer_mst_row.last_update_date,
2150 last_update_login = x_ic_xfer_mst_row.last_update_login
2151 WHERE
2152 transfer_no= p_ic_xfer_mst_row.transfer_no AND
2153 orgn_code = p_ic_xfer_mst_row.orgn_code;
2154 GMIGUTL.DB_ERRNUM := NULL;
2155 RETURN TRUE;
2156
2157 EXCEPTION
2158 WHEN OTHERS THEN
2159 -- Bug 1764383
2160 -- Added code to return sqlerrm in case of unexpected database errors.
2161 FND_MESSAGE.SET_NAME('GMI', 'GMI_UNEXPECTED_ERROR');
2162 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
2163 FND_MSG_PUB.ADD;
2164
2165 GMIGUTL.DB_ERRNUM := SQLCODE;
2166 GMIGUTL.DB_ERRMSG:= SQLERRM;
2167 RETURN FALSE;
2168
2169 END ic_xfer_mst_update;
2170 */
2171 END GMIVDBL;