[Home] [Help]
PACKAGE BODY: APPS.GMIVDBX
Source
4 | Copyright (c) 1998 Oracle Corporation |
1 PACKAGE BODY GMIVDBX AS
2 /* $Header: GMIVDBXB.pls 120.0 2005/05/25 16:13:58 appldev noship $
3 +==========================================================================+
5 | Redwood Shores, CA, USA |
9 | GMIVDBXB.pls |
6 | All rights reserved. |
7 +==========================================================================+
8 | FILE NAME |
10 | |
11 | PACKAGE NAME |
12 | GMIVDBX |
13 | |
14 | TYPE |
15 | Private |
16 | |
17 | DESCRIPTION |
18 | This package contains the private database insert routines |
19 | for Process / Discrete Transfer only. |
20 | |
21 | CONTENTS |
22 | header_insert |
23 | line_insert |
24 | lot_insert |
25 | |
26 | |
27 | HISTORY |
28 | Created - Jalaj Srivastava |
29 | |
30 | |
31 +==========================================================================+
32 */
33
34 PROCEDURE log_msg(p_msg_text IN VARCHAR2);
35
36 /* Global variables */
37 G_PKG_NAME CONSTANT VARCHAR2(30) :='GMIVDBX';
38 G_tmp BOOLEAN := FND_MSG_PUB.Check_Msg_Level(0) ; -- temp call to initialize the
39 -- msg level threshhold gobal
40 -- variable.
41 G_debug_level NUMBER(2) := FND_MSG_PUB.G_Msg_Level_Threshold; -- Use this variable everywhere
42 -- to decide to log a debug msg.
43
44
45 /* +==========================================================================+
46 | PROCEDURE NAME |
47 | header_insert |
48 | |
49 | USAGE |
50 | Sets up and insert records in gmi_discrete_transfers |
51 | |
52 | RETURNS |
53 | Via x_ OUT parameters |
54 | |
55 | HISTORY |
56 | Created Jalaj Srivastava |
57 | |
58 +==========================================================================+ */
59 PROCEDURE header_insert
60 ( p_api_version IN NUMBER
61 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
62 , p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
63 , p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL
64 , x_return_status OUT NOCOPY VARCHAR2
65 , x_msg_count OUT NOCOPY NUMBER
66 , x_msg_data OUT NOCOPY VARCHAR2
67 , p_hdr_rec IN GMIVDX.hdr_type
68 , x_hdr_row OUT NOCOPY gmi_discrete_transfers%ROWTYPE
69 )
70 IS
71 l_api_name CONSTANT VARCHAR2(30) := 'header_insert' ;
72 l_api_version CONSTANT NUMBER := 1.0 ;
73
74
75 BEGIN
76
77 IF FND_API.to_boolean(p_init_msg_list) THEN
78 FND_MSG_PUB.Initialize;
79 END IF;
80
81 SAVEPOINT create_header;
82
83 -- Standard call to check for call compatibility.
84 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
85 p_api_version ,
86 l_api_name ,
87 G_PKG_NAME
88 ) THEN
89 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
90 END IF;
91
92 x_return_status :=FND_API.G_RET_STS_SUCCESS;
93
94 --assign the transfer id.
95 SELECT gmi_dxfr_transfer_id_s.nextval INTO x_hdr_row.transfer_id FROM DUAL;
96
97 x_hdr_row.orgn_code := p_hdr_rec.orgn_code;
98 x_hdr_row.co_code := p_hdr_rec.co_code;
99 x_hdr_row.transfer_number := p_hdr_rec.transfer_number;
100 x_hdr_row.transfer_type := p_hdr_rec.transfer_type;
101 x_hdr_row.trans_date := p_hdr_rec.trans_date;
102 x_hdr_row.comments := p_hdr_rec.comments;
103 x_hdr_row.attribute_category := p_hdr_rec.attribute_category;
104 x_hdr_row.attribute1 := p_hdr_rec.attribute1;
105 x_hdr_row.attribute2 := p_hdr_rec.attribute2;
106 x_hdr_row.attribute3 := p_hdr_rec.attribute3;
107 x_hdr_row.attribute4 := p_hdr_rec.attribute4;
108 x_hdr_row.attribute5 := p_hdr_rec.attribute5;
109 x_hdr_row.attribute6 := p_hdr_rec.attribute6;
113 x_hdr_row.attribute10 := p_hdr_rec.attribute10;
110 x_hdr_row.attribute7 := p_hdr_rec.attribute7;
111 x_hdr_row.attribute8 := p_hdr_rec.attribute8;
112 x_hdr_row.attribute9 := p_hdr_rec.attribute9;
114 x_hdr_row.attribute11 := p_hdr_rec.attribute11;
115 x_hdr_row.attribute12 := p_hdr_rec.attribute12;
116 x_hdr_row.attribute13 := p_hdr_rec.attribute13;
117 x_hdr_row.attribute14 := p_hdr_rec.attribute14;
118 x_hdr_row.attribute15 := p_hdr_rec.attribute15;
119 x_hdr_row.attribute16 := p_hdr_rec.attribute16;
120 x_hdr_row.attribute17 := p_hdr_rec.attribute17;
121 x_hdr_row.attribute18 := p_hdr_rec.attribute18;
122 x_hdr_row.attribute19 := p_hdr_rec.attribute19;
123 x_hdr_row.attribute20 := p_hdr_rec.attribute20;
124 x_hdr_row.attribute21 := p_hdr_rec.attribute21;
125 x_hdr_row.attribute22 := p_hdr_rec.attribute22;
126 x_hdr_row.attribute23 := p_hdr_rec.attribute23;
127 x_hdr_row.attribute24 := p_hdr_rec.attribute24;
128 x_hdr_row.attribute25 := p_hdr_rec.attribute25;
129 x_hdr_row.attribute26 := p_hdr_rec.attribute26;
130 x_hdr_row.attribute27 := p_hdr_rec.attribute27;
131 x_hdr_row.attribute28 := p_hdr_rec.attribute28;
132 x_hdr_row.attribute29 := p_hdr_rec.attribute29;
133 x_hdr_row.attribute30 := p_hdr_rec.attribute30;
134 x_hdr_row.created_by := FND_GLOBAL.USER_ID;
135 x_hdr_row.creation_date := SYSDATE;
136 x_hdr_row.last_updated_by := FND_GLOBAL.USER_ID;
137 x_hdr_row.last_update_date := SYSDATE;
138 x_hdr_row.last_update_login := FND_GLOBAL.LOGIN_ID;
139 x_hdr_row.delete_mark := 0;
140 x_hdr_row.text_code := NULL;
141
142
143 INSERT INTO gmi_discrete_transfers
144 ( transfer_id
145 , orgn_code
146 , co_code
147 , transfer_number
148 , transfer_type
149 , trans_date
150 , comments
151 , attribute_category
152 , attribute1
153 , attribute2
154 , attribute3
155 , attribute4
156 , attribute5
157 , attribute6
158 , attribute7
159 , attribute8
160 , attribute9
161 , attribute10
162 , attribute11
163 , attribute12
164 , attribute13
165 , attribute14
166 , attribute15
167 , attribute16
168 , attribute17
169 , attribute18
170 , attribute19
171 , attribute20
172 , attribute21
173 , attribute22
174 , attribute23
175 , attribute24
176 , attribute25
177 , attribute26
178 , attribute27
179 , attribute28
180 , attribute29
181 , attribute30
182 , created_by
183 , creation_date
184 , last_updated_by
185 , last_update_date
186 , last_update_login
187 , delete_mark
188 , text_code
189 )
190 VALUES
191 ( x_hdr_row.transfer_id
192 , x_hdr_row.orgn_code
193 , x_hdr_row.co_code
194 , x_hdr_row.transfer_number
195 , x_hdr_row.transfer_type
196 , x_hdr_row.trans_date
197 , x_hdr_row.comments
198 , x_hdr_row.attribute_category
199 , x_hdr_row.attribute1
200 , x_hdr_row.attribute2
201 , x_hdr_row.attribute3
202 , x_hdr_row.attribute4
203 , x_hdr_row.attribute5
204 , x_hdr_row.attribute6
205 , x_hdr_row.attribute7
206 , x_hdr_row.attribute8
207 , x_hdr_row.attribute9
208 , x_hdr_row.attribute10
209 , x_hdr_row.attribute11
210 , x_hdr_row.attribute12
211 , x_hdr_row.attribute13
212 , x_hdr_row.attribute14
213 , x_hdr_row.attribute15
214 , x_hdr_row.attribute16
215 , x_hdr_row.attribute17
216 , x_hdr_row.attribute18
217 , x_hdr_row.attribute19
218 , x_hdr_row.attribute20
219 , x_hdr_row.attribute21
220 , x_hdr_row.attribute22
221 , x_hdr_row.attribute23
222 , x_hdr_row.attribute24
223 , x_hdr_row.attribute25
224 , x_hdr_row.attribute26
225 , x_hdr_row.attribute27
226 , x_hdr_row.attribute28
227 , x_hdr_row.attribute29
228 , x_hdr_row.attribute30
229 , x_hdr_row.created_by
230 , x_hdr_row.creation_date
231 , x_hdr_row.last_updated_by
232 , x_hdr_row.last_update_date
233 , x_hdr_row.last_update_login
234 , x_hdr_row.delete_mark
235 , x_hdr_row.text_code
236 );
237
238 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
239 log_msg('Inserted 1 record in gmi_discrete_transfers');
240 END IF;
241
242 FND_MSG_PUB.Count_AND_GET
243 (p_count => x_msg_count, p_data => x_msg_data);
244
245
246 EXCEPTION
247
248 WHEN FND_API.G_EXC_ERROR THEN
249 ROLLBACK to create_header;
250 x_return_status := FND_API.G_RET_STS_ERROR;
251 FND_MSG_PUB.Count_AND_GET
252 (p_count => x_msg_count, p_data => x_msg_data);
253
254 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
255 ROLLBACK to create_header;
256 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
257 FND_MSG_PUB.Count_AND_GET
258 (p_count => x_msg_count, p_data => x_msg_data);
259
260 WHEN OTHERS THEN
261 ROLLBACK to create_header;
262 IF (SQLCODE IS NOT NULL) THEN
263 FND_MESSAGE.SET_NAME('GMI','GMI_DXFR_SQL_ERROR');
264 FND_MESSAGE.SET_TOKEN('ERRCODE',SQLCODE);
265 FND_MESSAGE.SET_TOKEN('ERRM',SQLERRM);
266 FND_MSG_PUB.Add;
267 END IF;
268 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
269 FND_MSG_PUB.Count_AND_GET
270 (p_count => x_msg_count, p_data => x_msg_data);
271
272 END header_insert;
273
277 | |
274 /* +==========================================================================+
275 | PROCEDURE NAME |
276 | line_insert |
278 | USAGE |
279 | Sets up and insert records in gmi_discrete_transfer_lines |
280 | |
281 | RETURNS |
282 | Via x_ OUT parameters |
283 | |
284 | HISTORY |
285 | Created Jalaj Srivastava |
286 | |
287 +==========================================================================+
288 */
289
290 PROCEDURE line_insert
291 ( p_api_version IN NUMBER
292 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
293 , p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
294 , p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL
295 , x_return_status OUT NOCOPY VARCHAR2
296 , x_msg_count OUT NOCOPY NUMBER
297 , x_msg_data OUT NOCOPY VARCHAR2
298 , p_hdr_row IN gmi_discrete_transfers%ROWTYPE
299 , p_line_rec IN GMIVDX.line_type
300 , x_line_row OUT NOCOPY gmi_discrete_transfer_lines%ROWTYPE
301 )
302 IS
303 l_api_name CONSTANT VARCHAR2(30) := 'line_insert' ;
304 l_api_version CONSTANT NUMBER := 1.0 ;
305
306
307 BEGIN
308
309 IF FND_API.to_boolean(p_init_msg_list) THEN
310 FND_MSG_PUB.Initialize;
311 END IF;
312
313 SAVEPOINT create_line;
314
315 -- Standard call to check for call compatibility.
316 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
317 p_api_version ,
318 l_api_name ,
319 G_PKG_NAME
320 ) THEN
321 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
322 END IF;
323
324 x_return_status :=FND_API.G_RET_STS_SUCCESS;
325
326 x_line_row.transfer_id := p_hdr_row.transfer_id;
327
328 --get the line id
329 SELECT gmi_dxfr_line_id_s.nextval INTO x_line_row.line_id FROM DUAL;
330
331 x_line_row.line_no := p_line_rec.line_no;
332 x_line_row.opm_item_id := p_line_rec.opm_item_id;
333 x_line_row.opm_whse_code := p_line_rec.opm_whse_code;
334 x_line_row.opm_location := p_line_rec.opm_location;
335 x_line_row.opm_lot_id := p_line_rec.opm_lot_id;
336 x_line_row.opm_lot_status := p_line_rec.opm_lot_status;
337 x_line_row.opm_grade := p_line_rec.opm_grade;
338 x_line_row.opm_charge_acct_id := p_line_rec.opm_charge_acct_id;
339 x_line_row.opm_charge_au_id := p_line_rec.opm_charge_au_id;
340 x_line_row.opm_reason_code := p_line_rec.opm_reason_code;
341 x_line_row.odm_inv_organization_id := p_line_rec.odm_inv_organization_id;
342 x_line_row.odm_item_id := p_line_rec.odm_item_id;
343 x_line_row.odm_item_revision := p_line_rec.odm_item_revision;
344 x_line_row.odm_subinventory := p_line_rec.odm_subinventory;
345 x_line_row.odm_locator_id := p_line_rec.odm_locator_id;
346 x_line_row.odm_lot_number := p_line_rec.odm_lot_number;
347 x_line_row.opm_lot_expiration_date := p_line_rec.opm_lot_expiration_date;
348 x_line_row.odm_lot_expiration_date := p_line_rec.odm_lot_expiration_date;
349 x_line_row.odm_charge_account_id := p_line_rec.odm_charge_account_id;
350 x_line_row.odm_period_id := p_line_rec.odm_period_id;
351 x_line_row.odm_unit_cost := p_line_rec.odm_unit_cost;
352 x_line_row.odm_reason_id := p_line_rec.odm_reason_id;
353 x_line_row.quantity := p_line_rec.quantity;
354 x_line_row.quantity_um := p_line_rec.quantity_um;
355 x_line_row.quantity2 := p_line_rec.quantity2;
356 x_line_row.opm_primary_quantity := p_line_rec.opm_primary_quantity;
357 x_line_row.odm_primary_quantity := p_line_rec.odm_primary_quantity;
358 x_line_row.lot_level := p_line_rec.lot_level;
359 x_line_row.attribute_category := p_line_rec.attribute_category;
360 x_line_row.attribute1 := p_line_rec.attribute1;
361 x_line_row.attribute2 := p_line_rec.attribute2;
362 x_line_row.attribute3 := p_line_rec.attribute3;
363 x_line_row.attribute4 := p_line_rec.attribute4;
364 x_line_row.attribute5 := p_line_rec.attribute5;
365 x_line_row.attribute6 := p_line_rec.attribute6;
366 x_line_row.attribute7 := p_line_rec.attribute7;
367 x_line_row.attribute8 := p_line_rec.attribute8;
368 x_line_row.attribute9 := p_line_rec.attribute9;
369 x_line_row.attribute10 := p_line_rec.attribute10;
370 x_line_row.attribute11 := p_line_rec.attribute11;
374 x_line_row.attribute15 := p_line_rec.attribute15;
371 x_line_row.attribute12 := p_line_rec.attribute12;
372 x_line_row.attribute13 := p_line_rec.attribute13;
373 x_line_row.attribute14 := p_line_rec.attribute14;
375 x_line_row.attribute16 := p_line_rec.attribute16;
376 x_line_row.attribute17 := p_line_rec.attribute17;
377 x_line_row.attribute18 := p_line_rec.attribute18;
378 x_line_row.attribute19 := p_line_rec.attribute19;
379 x_line_row.attribute20 := p_line_rec.attribute20;
380 x_line_row.attribute21 := p_line_rec.attribute21;
381 x_line_row.attribute22 := p_line_rec.attribute22;
382 x_line_row.attribute23 := p_line_rec.attribute23;
383 x_line_row.attribute24 := p_line_rec.attribute24;
384 x_line_row.attribute25 := p_line_rec.attribute25;
385 x_line_row.attribute26 := p_line_rec.attribute26;
386 x_line_row.attribute27 := p_line_rec.attribute27;
387 x_line_row.attribute28 := p_line_rec.attribute28;
388 x_line_row.attribute29 := p_line_rec.attribute29;
389 x_line_row.attribute30 := p_line_rec.attribute30;
390 x_line_row.created_by := FND_GLOBAL.USER_ID;
391 x_line_row.creation_date := SYSDATE;
392 x_line_row.last_updated_by := FND_GLOBAL.USER_ID;
393 x_line_row.last_update_date := SYSDATE;
394 x_line_row.last_update_login := FND_GLOBAL.LOGIN_ID;
395 x_line_row.delete_mark := 0;
396 x_line_row.text_code := NULL;
397
398
399 INSERT INTO gmi_discrete_transfer_lines
400 (
401 transfer_id
402 , line_id
403 , line_no
404 , opm_item_id
405 , opm_whse_code
406 , opm_location
407 , opm_lot_id
408 , opm_lot_expiration_date
409 , opm_lot_status
410 , opm_grade
411 , opm_charge_acct_id
412 , opm_charge_au_id
413 , opm_reason_code
414 , odm_inv_organization_id
415 , odm_item_id
416 , odm_item_revision
417 , odm_subinventory
418 , odm_locator_id
419 , odm_lot_number
420 , odm_lot_expiration_date
421 , odm_charge_account_id
422 , odm_period_id
423 , odm_unit_cost
424 , odm_reason_id
425 , quantity
426 , quantity_um
427 , quantity2
428 , opm_primary_quantity
429 , odm_primary_quantity
430 , lot_level
431 , attribute_category
432 , attribute1
433 , attribute2
434 , attribute3
435 , attribute4
436 , attribute5
437 , attribute6
438 , attribute7
439 , attribute8
440 , attribute9
441 , attribute10
442 , attribute11
443 , attribute12
444 , attribute13
445 , attribute14
446 , attribute15
447 , attribute16
448 , attribute17
449 , attribute18
450 , attribute19
451 , attribute20
452 , attribute21
453 , attribute22
454 , attribute23
455 , attribute24
456 , attribute25
457 , attribute26
458 , attribute27
459 , attribute28
460 , attribute29
461 , attribute30
462 , created_by
463 , creation_date
464 , last_updated_by
465 , last_update_date
466 , last_update_login
467 , delete_mark
468 , text_code
469 )
470 VALUES
471 (
472 x_line_row.transfer_id
473 , x_line_row.line_id
474 , x_line_row.line_no
475 , x_line_row.opm_item_id
476 , x_line_row.opm_whse_code
477 , x_line_row.opm_location
478 , x_line_row.opm_lot_id
479 , x_line_row.opm_lot_expiration_date
480 , x_line_row.opm_lot_status
481 , x_line_row.opm_grade
482 , x_line_row.opm_charge_acct_id
483 , x_line_row.opm_charge_au_id
484 , x_line_row.opm_reason_code
485 , x_line_row.odm_inv_organization_id
486 , x_line_row.odm_item_id
487 , x_line_row.odm_item_revision
488 , x_line_row.odm_subinventory
489 , x_line_row.odm_locator_id
490 , x_line_row.odm_lot_number
491 , x_line_row.odm_lot_expiration_date
492 , x_line_row.odm_charge_account_id
493 , x_line_row.odm_period_id
494 , x_line_row.odm_unit_cost
495 , x_line_row.odm_reason_id
496 , x_line_row.quantity
497 , x_line_row.quantity_um
498 , x_line_row.quantity2
499 , x_line_row.opm_primary_quantity
500 , x_line_row.odm_primary_quantity
501 , x_line_row.lot_level
502 , x_line_row.attribute_category
503 , x_line_row.attribute1
504 , x_line_row.attribute2
505 , x_line_row.attribute3
506 , x_line_row.attribute4
507 , x_line_row.attribute5
508 , x_line_row.attribute6
509 , x_line_row.attribute7
510 , x_line_row.attribute8
511 , x_line_row.attribute9
512 , x_line_row.attribute10
513 , x_line_row.attribute11
514 , x_line_row.attribute12
515 , x_line_row.attribute13
516 , x_line_row.attribute14
517 , x_line_row.attribute15
518 , x_line_row.attribute16
519 , x_line_row.attribute17
520 , x_line_row.attribute18
521 , x_line_row.attribute19
522 , x_line_row.attribute20
523 , x_line_row.attribute21
524 , x_line_row.attribute22
525 , x_line_row.attribute23
526 , x_line_row.attribute24
527 , x_line_row.attribute25
528 , x_line_row.attribute26
529 , x_line_row.attribute27
530 , x_line_row.attribute28
531 , x_line_row.attribute29
532 , x_line_row.attribute30
533 , x_line_row.created_by
534 , x_line_row.creation_date
535 , x_line_row.last_updated_by
536 , x_line_row.last_update_date
537 , x_line_row.last_update_login
538 , x_line_row.delete_mark
542 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
539 , x_line_row.text_code
540 );
541
543 log_msg('Inserted 1 record in gmi_discrete_transfer_lines');
544 END IF;
545
546 FND_MSG_PUB.Count_AND_GET
547 (p_count => x_msg_count, p_data => x_msg_data);
548
549
550 EXCEPTION
551
552 WHEN FND_API.G_EXC_ERROR THEN
553 ROLLBACK to create_line;
554 x_return_status := FND_API.G_RET_STS_ERROR;
555 FND_MSG_PUB.Count_AND_GET
556 (p_count => x_msg_count, p_data => x_msg_data);
557
558 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
559 ROLLBACK to create_line;
560 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
561 FND_MSG_PUB.Count_AND_GET
562 (p_count => x_msg_count, p_data => x_msg_data);
563
564 WHEN OTHERS THEN
565 ROLLBACK to create_line;
566 IF (SQLCODE IS NOT NULL) THEN
567 FND_MESSAGE.SET_NAME('GMI','GMI_DXFR_SQL_ERROR');
568 FND_MESSAGE.SET_TOKEN('ERRCODE',SQLCODE);
569 FND_MESSAGE.SET_TOKEN('ERRM',SQLERRM);
570 FND_MSG_PUB.Add;
571 END IF;
572 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
573 FND_MSG_PUB.Count_AND_GET
574 (p_count => x_msg_count, p_data => x_msg_data);
575
576 END line_insert;
577
578
579
580 /* +==========================================================================+
581 | PROCEDURE NAME |
582 | lot_insert |
583 | |
584 | USAGE |
585 | Sets up and insert records in gmi_discrete_transfer_lots |
586 | |
587 | RETURNS |
588 | Via x_ OUT parameters |
589 | |
590 | HISTORY |
591 | Created Jalaj Srivastava |
592 | |
593 +==========================================================================+
594 */
595 PROCEDURE lot_insert
596 ( p_api_version IN NUMBER
597 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
598 , p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
599 , p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL
600 , x_return_status OUT NOCOPY VARCHAR2
601 , x_msg_count OUT NOCOPY NUMBER
602 , x_msg_data OUT NOCOPY VARCHAR2
603 , p_line_row IN gmi_discrete_transfer_lines%ROWTYPE
604 , p_lot_rec IN GMIVDX.lot_type
605 , x_lot_row OUT NOCOPY gmi_discrete_transfer_lots%ROWTYPE
606 ) IS
607 l_api_name CONSTANT VARCHAR2(30) := 'lot_insert' ;
608 l_api_version CONSTANT NUMBER := 1.0 ;
609
610
611 BEGIN
612
613 IF FND_API.to_boolean(p_init_msg_list) THEN
614 FND_MSG_PUB.Initialize;
615 END IF;
616
617 SAVEPOINT create_lot;
618
619 -- Standard call to check for call compatibility.
620 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
621 p_api_version ,
622 l_api_name ,
623 G_PKG_NAME
624 ) THEN
625 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
626 END IF;
627
628 x_return_status :=FND_API.G_RET_STS_SUCCESS;
629
630 x_lot_row.transfer_id := p_line_row.transfer_id;
631 x_lot_row.line_id := p_line_row.line_id;
632
633 --get the line detail id
634 SELECT gmi_dxfr_line_detail_id_s.nextval INTO x_lot_row.line_detail_id FROM DUAL;
635
636 x_lot_row.opm_lot_id := p_lot_rec.opm_lot_id;
637 x_lot_row.opm_lot_expiration_date := p_lot_rec.opm_lot_expiration_date;
638 x_lot_row.opm_lot_status := p_lot_rec.opm_lot_status;
639 x_lot_row.opm_grade := p_lot_rec.opm_grade;
640 x_lot_row.odm_lot_number := p_lot_rec.odm_lot_number;
641 x_lot_row.odm_lot_expiration_date := p_lot_rec.odm_lot_expiration_date;
642 x_lot_row.quantity := p_lot_rec.quantity;
643 x_lot_row.quantity2 := p_lot_rec.quantity2;
644 x_lot_row.opm_primary_quantity := p_lot_rec.opm_primary_quantity;
645 x_lot_row.odm_primary_quantity := p_lot_rec.odm_primary_quantity;
646 x_lot_row.created_by := FND_GLOBAL.USER_ID;
647 x_lot_row.creation_date := SYSDATE;
648 x_lot_row.last_updated_by := FND_GLOBAL.USER_ID;
649 x_lot_row.last_update_date := SYSDATE;
650 x_lot_row.last_update_login := FND_GLOBAL.LOGIN_ID;
651 x_lot_row.delete_mark := 0;
652 x_lot_row.text_code := NULL;
653
654
655 INSERT INTO gmi_discrete_transfer_lots
656 (
657 transfer_id
658 , line_id
659 , line_detail_id
660 , opm_lot_id
661 , opm_lot_expiration_date
662 , opm_lot_status
663 , opm_grade
664 , odm_lot_number
665 , odm_lot_expiration_date
666 , quantity
667 , quantity2
668 , opm_primary_quantity
669 , odm_primary_quantity
670 , created_by
671 , creation_date
672 , last_updated_by
676 , text_code
673 , last_update_date
674 , last_update_login
675 , delete_mark
677 )
678 VALUES
679 (
680 x_lot_row.transfer_id
681 , x_lot_row.line_id
682 , x_lot_row.line_detail_id
683 , x_lot_row.opm_lot_id
684 , x_lot_row.opm_lot_expiration_date
685 , x_lot_row.opm_lot_status
686 , x_lot_row.opm_grade
687 , x_lot_row.odm_lot_number
688 , x_lot_row.odm_lot_expiration_date
689 , x_lot_row.quantity
690 , x_lot_row.quantity2
691 , x_lot_row.opm_primary_quantity
692 , x_lot_row.odm_primary_quantity
693 , x_lot_row.created_by
694 , x_lot_row.creation_date
695 , x_lot_row.last_updated_by
696 , x_lot_row.last_update_date
697 , x_lot_row.last_update_login
698 , x_lot_row.delete_mark
699 , x_lot_row.text_code
700 );
701
702 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
703 log_msg('Inserted 1 record in gmi_discrete_transfer_lots');
704 END IF;
705
706 FND_MSG_PUB.Count_AND_GET
707 (p_count => x_msg_count, p_data => x_msg_data);
708
709
710
711 EXCEPTION
712
713 WHEN FND_API.G_EXC_ERROR THEN
714 ROLLBACK to create_lot;
715 x_return_status := FND_API.G_RET_STS_ERROR;
716 FND_MSG_PUB.Count_AND_GET
717 (p_count => x_msg_count, p_data => x_msg_data);
718
719 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
720 ROLLBACK to create_lot;
721 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
722 FND_MSG_PUB.Count_AND_GET
723 (p_count => x_msg_count, p_data => x_msg_data);
724
725 WHEN OTHERS THEN
726 ROLLBACK to create_lot;
727 IF (SQLCODE IS NOT NULL) THEN
728 FND_MESSAGE.SET_NAME('GMI','GMI_DXFR_SQL_ERROR');
729 FND_MESSAGE.SET_TOKEN('ERRCODE',SQLCODE);
730 FND_MESSAGE.SET_TOKEN('ERRM',SQLERRM(SQLCODE));
731 FND_MSG_PUB.Add;
732 END IF;
733 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
734 FND_MSG_PUB.Count_AND_GET
735 (p_count => x_msg_count, p_data => x_msg_data);
736
737 END lot_insert;
738
739
740
741 /* +==========================================================================+
742 | PROCEDURE NAME |
743 | get_doc_no |
744 | |
745 | USAGE |
746 | This will get the doc no from sy_docs_mst and commit the no so that |
747 | there is no lock on the table. |
748 | It is a AUTONOMOUS_TRANSACTION. will commit before the main |
749 | transaction completes. |
750 | |
751 | RETURNS |
752 | Via x_ OUT parameters |
753 | |
754 | HISTORY |
755 | Created Jalaj Srivastava |
756 | |
757 +==========================================================================+
758 */
759 FUNCTION get_doc_no
760 ( x_return_status OUT NOCOPY VARCHAR2
761 , x_msg_count OUT NOCOPY NUMBER
762 , x_msg_data OUT NOCOPY VARCHAR2
763 , p_doc_type IN sy_docs_seq.doc_type%TYPE
764 , p_orgn_code IN sy_docs_seq.orgn_code%TYPE
765 ) RETURN VARCHAR2 IS
766 PRAGMA AUTONOMOUS_TRANSACTION;
767 l_doc_no VARCHAR2(10);
768
769 BEGIN
770
771 SAVEPOINT get_doc_no;
772
773 x_return_status :=FND_API.G_RET_STS_SUCCESS;
774
775 l_doc_no := GMA_GLOBAL_GRP.Get_doc_no (p_doc_type,p_orgn_code);
776
777 COMMIT;
778
779 return l_doc_no;
780
781 FND_MSG_PUB.Count_AND_GET
782 (p_count => x_msg_count, p_data => x_msg_data);
783
784 EXCEPTION
785
786 WHEN FND_API.G_EXC_ERROR THEN
787 ROLLBACK to get_doc_no;
788 x_return_status := FND_API.G_RET_STS_ERROR;
789 FND_MSG_PUB.Count_AND_GET
790 (p_count => x_msg_count, p_data => x_msg_data);
791
792 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
793 ROLLBACK to get_doc_no;
794 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
795 FND_MSG_PUB.Count_AND_GET
796 (p_count => x_msg_count, p_data => x_msg_data);
797
798 WHEN OTHERS THEN
799 ROLLBACK to get_doc_no;
800 IF (SQLCODE IS NOT NULL) THEN
801 FND_MESSAGE.SET_NAME('GMI','GMI_DXFR_SQL_ERROR');
802 FND_MESSAGE.SET_TOKEN('ERRCODE',SQLCODE);
803 FND_MESSAGE.SET_TOKEN('ERRM',SQLERRM);
804 FND_MSG_PUB.Add;
805 END IF;
806 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
807 FND_MSG_PUB.Count_AND_GET
808 (p_count => x_msg_count, p_data => x_msg_data);
809
810 END get_doc_no;
811
812 PROCEDURE log_msg(p_msg_text IN VARCHAR2) IS
813 BEGIN
814
815 FND_MESSAGE.SET_NAME('GMI','GMI_DEBUG_API');
816 FND_MESSAGE.SET_TOKEN('MSG',p_msg_text);
817 FND_MSG_PUB.Add;
818
819 END log_msg ;
820
821 END GMIVDBX;