[Home] [Help]
PACKAGE BODY: APPS.GMD_ITEM_TECHNICAL_DATA_PUB
Source
1 PACKAGE BODY GMD_ITEM_TECHNICAL_DATA_PUB AS
2 /* $Header: GMDPITDB.pls 120.4.12010000.2 2008/10/20 11:34:00 kannavar ship $ */
3
4 --Global Variables
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'GMD_ITEM_TECHNICAL_DATA_PUB';
6
7 --Procedure to validate parameters
8 PROCEDURE VALIDATE_INPUT_PARAMS
9 (
10 p_header_Rec IN GMD_ITEM_TECHNICAL_DATA_PUB.technical_data_hdr_rec
11 , p_dtl_tbl IN GMD_ITEM_TECHNICAL_DATA_PUB.technical_data_dtl_tab
12 , p_operation IN VARCHAR2
13 , x_return_status OUT NOCOPY VARCHAR2
14 );
15
16 --Start of comments
17 --+========================================================================+
18 --| API Name : INSERT_ITEM_TECHNICAL_DATA |
19 --| TYPE : Public |
20 --| Function : Inserts the Item technical data |
21 --| Notes : |
22 --| |
23 --| HISTORY |
24 --| S.Sriram 21-Feb-2005 Created |
25 --+========================================================================+
26 -- End of comments
27 PROCEDURE INSERT_ITEM_TECHNICAL_DATA
28 (
29 p_api_version IN NUMBER
30 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
31 , p_commit IN VARCHAR2 := FND_API.G_FALSE
32 , x_return_status OUT NOCOPY VARCHAR2
33 , x_msg_count OUT NOCOPY NUMBER
34 , x_msg_data OUT NOCOPY VARCHAR2
35 , p_header_rec IN OUT NOCOPY technical_data_hdr_rec
36 , p_dtl_tbl IN technical_data_dtl_tab
37 ) IS
38
39 l_api_name CONSTANT VARCHAR2(30) := 'Insert_Item_Technical_Data';
40 l_api_version CONSTANT NUMBER := 1.0;
41
42 -- Cursor to fetch data type for the technical parameter passed
43 CURSOR get_tech_data_type (l_tech_parm_id NUMBER, l_orgn_id NUMBER) IS
44 SELECT data_type
45 FROM gmd_tech_parameters_b
46 WHERE tech_parm_id = l_tech_parm_id
47 AND (organization_id = l_orgn_id OR organization_id IS NULL);
48
49 l_text_data VARCHAR2(200) := NULL;
50 l_num_data NUMBER;
51 l_bool_data NUMBER;
52 l_data_type NUMBER;
53 l_return_status VARCHAR2(10);
54 l_tech_parm_id NUMBER;
55 l_sort_seq NUMBER;
56
57 BEGIN
58
59 SAVEPOINT Insert_Item_Tech_Data_PUB; /* Changed in Bug No.7489645*/
60
61 -- Initialize message list if p_init_msg_list is set to TRUE.
62 IF FND_API.to_Boolean( p_init_msg_list ) THEN
63 FND_MSG_PUB.initialize;
64 END IF;
65
66 -- Standard call to check for call compatibility.
67 IF NOT FND_API.Compatible_API_Call ( l_api_version
68 , p_api_version
69 , l_api_name
70 , G_PKG_NAME
71 ) THEN
72
73 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
74 END IF;
75
76 -- Initialize API return status to success
77 x_return_status := FND_API.G_RET_STS_SUCCESS;
78
79 -- Validate i/p parm's here
80 Validate_Input_Params
81 (
82 p_header_rec => p_header_rec
83 , p_dtl_tbl => p_dtl_tbl
84 , p_operation => 'INSERT'
85 , x_return_status => l_return_status
86 );
87
88 IF l_return_status IN (FND_API.G_RET_STS_ERROR, FND_API.G_RET_STS_UNEXP_ERROR) THEN
89 FND_MESSAGE.SET_NAME('GMD','GMD_API_NO_ROWS_INS');
90 FND_MSG_PUB.ADD;
91 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
92 END IF;
93
94 -- Insert Item Technical Data header
95 GMD_ITEM_TECHNICAL_DATA_PVT.INSERT_ITEM_TECHNICAL_DATA_HDR
96 ( p_api_version => p_api_version
97 , p_init_msg_list => p_init_msg_list
98 , p_commit => p_commit
99 , x_return_status => x_return_status
100 , x_msg_count => x_msg_count
101 , x_msg_data => x_msg_data
102 , x_tech_data_id => p_header_rec.Tech_Data_Id
103 , p_organization_id => p_header_rec.Organization_Id
104 , p_inventory_item_id => p_header_rec.Inventory_Item_Id
105 , p_lot_no => p_header_rec.Lot_Number
106 , p_lot_organization_id => p_header_rec.Lot_Organization_Id
107 , p_formula_id => p_header_rec.Formula_Id
108 , p_batch_id => p_header_rec.Batch_Id
109 , p_delete_mark => 0
110 , p_text_code => p_header_rec.Text_Code
111 , p_creation_date => SYSDATE
112 , p_created_by => FND_GLOBAL.USER_ID
113 , p_last_update_date => SYSDATE
114 , p_last_updated_by => FND_GLOBAL.USER_ID
115 , p_last_update_login => FND_GLOBAL.LOGIN_ID
116 );
117
118
119 -- Insert Item Technical Data detail records
120 FOR i IN p_dtl_tbl.FIRST .. p_dtl_tbl.LAST
121 LOOP
122 l_num_data := NULL;
123 l_text_data := NULL;
124 l_bool_data := NULL;
125
126
127 OPEN get_tech_data_type(p_dtl_tbl(i).tech_parm_id, p_header_rec.Organization_Id );
128 FETCH get_tech_data_type INTO l_data_type;
129 CLOSE get_tech_data_type;
130
131
132 IF l_data_type IN (1,5,6,7,8,9,10,11) THEN
133 l_num_data := TO_NUMBER(p_dtl_tbl(i).Tech_Data);
134 ELSIF l_data_type IN (0,2, 4) THEN
135 l_text_data := p_dtl_tbl(i).Tech_Data;
136 ELSIF l_data_type = 3 THEN
137 l_bool_data := p_dtl_tbl(i).Tech_Data;
138 END IF;
139 -- data type 4 - verify
140 l_tech_parm_id := p_dtl_tbl(i).Tech_Parm_Id;
141 l_sort_seq := p_dtl_tbl(i).Sort_Seq;
142
143
144 GMD_ITEM_TECHNICAL_DATA_PVT.INSERT_ITEM_TECHNICAL_DATA_DTL
145 ( p_api_version => p_api_version
146 , p_init_msg_list => p_init_msg_list
147 , p_commit => p_commit
148 , x_return_status => x_return_status
149 , x_msg_count => x_msg_count
150 , x_msg_data => x_msg_data
151 , x_tech_data_id => p_header_rec.Tech_Data_Id
152 , x_tech_parm_id => l_tech_parm_id
153 , p_sort_seq => l_sort_seq
154 , p_text_data => l_text_data
155 , p_num_data => l_num_data
156 , p_boolean_data => l_bool_data
157 , p_text_code => p_header_rec.text_code
158 , p_creation_date => SYSDATE
159 , p_created_by => FND_GLOBAL.USER_ID
160 , p_last_update_date => SYSDATE
161 , p_last_updated_by => FND_GLOBAL.USER_ID
162 , p_last_update_login => FND_GLOBAL.LOGIN_ID
163 );
164 END LOOP;
165
166 IF FND_API.To_Boolean( p_commit ) THEN
167 COMMIT WORK;
168 END IF;
169
170 EXCEPTION
171 WHEN FND_API.G_EXC_ERROR THEN
172
173 ROLLBACK TO Insert_Item_Tech_Data_PUB;
174 x_return_status := FND_API.G_RET_STS_ERROR ;
175 fnd_msg_pub.count_and_get (
176 p_count => x_msg_count
177 ,p_encoded => FND_API.g_false
178 ,p_data => x_msg_data);
179
180
181 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
182
183 ROLLBACK TO Insert_Item_Tech_Data_PUB;
184
185 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
186 fnd_msg_pub.count_and_get (
187 p_count => x_msg_count
188 ,p_encoded => FND_API.g_false
189 ,p_data => x_msg_data);
190
191 WHEN OTHERS THEN
192
193 ROLLBACK TO Insert_Item_Tech_Data_PUB;
194
195 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
196 fnd_msg_pub.count_and_get (
197 p_count => x_msg_count
198 ,p_encoded => FND_API.g_false
199 ,p_data => x_msg_data);
200
201 END INSERT_ITEM_TECHNICAL_DATA;
202
203 --Start of comments
204 --+========================================================================+
205 --| API Name : UPDATE_ITEM_TECHNICAL_DATA |
206 --| TYPE : Public |
207 --| Function : Updates the Item technical data |
208 --| Notes : |
209 --| |
210 --| HISTORY |
211 --| S.Sriram 21-Feb-2005 Created |
212 --+========================================================================+
213 -- End of comments
214 PROCEDURE UPDATE_ITEM_TECHNICAL_DATA
215 (
216 p_api_version IN NUMBER
217 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
218 , p_commit IN VARCHAR2 := FND_API.G_FALSE
219 , x_return_status OUT NOCOPY VARCHAR2
220 , x_msg_count OUT NOCOPY NUMBER
221 , x_msg_data OUT NOCOPY VARCHAR2
222 , p_tech_data_id IN NUMBER
223 , p_dtl_tbl IN technical_data_dtl_tab
224 ) IS
225
226
227
228 l_api_name CONSTANT VARCHAR2(30) := 'Update_Item_Technical_Data';
229 l_api_version CONSTANT NUMBER := 1.0;
230
231
232 -- Cursor to fetch data type for the technical parameter passed
233 CURSOR get_tech_data_type (l_tech_parm_id NUMBER, l_orgn_id NUMBER) IS
234 SELECT data_type
235 FROM gmd_tech_parameters_b
236 WHERE tech_parm_id = l_tech_parm_id
237 AND (organization_id = l_orgn_id OR organization_id IS NULL);
238
239 CURSOR get_Orgn_id IS
240 SELECT organization_id
241 FROM gmd_technical_data_hdr
242 WHERE tech_data_id = p_tech_data_id;
243
244 l_text_data VARCHAR2(200) := NULL;
245 l_num_data NUMBER;
246 l_bool_data NUMBER;
247 l_return_status VARCHAR2(10);
248 l_data_type NUMBER;
249 l_orgn_id NUMBER;
250 l_tech_parm_id NUMBER;
251 l_sort_seq NUMBER;
252 l_text_code NUMBER;
253
254
255 BEGIN
256
257
258 SAVEPOINT Update_Item_Technical_Data;
259
260 -- Initialize message list if p_init_msg_list is set to TRUE.
261 IF FND_API.to_Boolean( p_init_msg_list )
262 THEN
263 FND_MSG_PUB.initialize;
264 END IF;
265
266
267 -- Standard call to check for call compatibility.
268 IF NOT FND_API.Compatible_API_Call (l_api_version ,
269 p_api_version ,
270 l_api_name ,
271 G_PKG_NAME
272 )
273 THEN
274 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
275 END IF;
276
277 -- Initialize API return status to success
278
279 x_return_status := FND_API.G_RET_STS_SUCCESS;
280
281 /*-- Validate i/p parm's here
282 Validate_Input_Params
283 (
284 p_header_rec => p_header_rec
285 , p_dtl_tbl => p_dtl_tbl
286 , p_operation => 'UPDATE'
287 , x_return_status => l_return_status
288 );
289
290
291 IF l_return_status IN (FND_API.G_RET_STS_ERROR, FND_API.G_RET_STS_UNEXP_ERROR) THEN
292 FND_MESSAGE.SET_NAME('GMD','GMF_API_NO_ROWS_UPD');
293 FND_MSG_PUB.ADD;
294 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
295 END IF;*/
296
297 OPEN get_Orgn_id;
298 FETCH get_Orgn_id INTO l_orgn_id;
299 CLOSE get_Orgn_id;
300
301 -- Update Item Technical Data detail records
302 FOR i IN p_dtl_tbl.FIRST .. p_dtl_tbl.LAST
303 LOOP
304 l_num_data := NULL;
305 l_text_data := NULL;
306 l_bool_data := NULL;
307
308 OPEN get_tech_data_type (p_dtl_tbl(i).tech_parm_id,l_orgn_id );
309 FETCH get_tech_data_type INTO l_data_type;
310 CLOSE get_tech_data_type;
311
312
313 IF l_data_type IN (1,5,6,7,8,9,10,11) THEN
314 l_num_data := TO_NUMBER(p_dtl_tbl(i).Tech_Data);
315 ELSIF l_data_type IN (0,2, 4) THEN
316 l_text_data := p_dtl_tbl(i).Tech_Data;
317 ELSIF l_data_type = 3 THEN
318 l_bool_data := p_dtl_tbl(i).Tech_Data;
319 END IF;
320 -- data type 4 - verify
321
322 l_tech_parm_id := p_dtl_tbl(i).Tech_Parm_Id;
323 l_sort_seq := p_dtl_tbl(i).Sort_Seq;
324 l_text_code := p_dtl_tbl(i).Text_Code;
325
326 GMD_ITEM_TECHNICAL_DATA_PVT.UPDATE_ITEM_TECHNICAL_DATA
327 ( p_api_version => p_api_version
328 , p_init_msg_list => p_init_msg_list
329 , p_commit => p_commit
330 , x_return_status => x_return_status
331 , x_msg_count => x_msg_count
332 , x_msg_data => x_msg_data
333 , p_tech_data_id => p_tech_data_id
334 , x_tech_parm_id => l_tech_parm_id
335 , p_sort_seq => l_sort_seq
336 , p_text_data => l_text_data
337 , p_num_data => l_num_data
338 , p_boolean_data => l_bool_data
339 , p_text_code => l_text_code
340 , p_last_update_date => SYSDATE
341 , p_last_updated_by => FND_GLOBAL.USER_ID
342 , p_last_update_login => FND_GLOBAL.LOGIN_ID
343 );
344 END LOOP;
345
346
347 IF FND_API.To_Boolean( p_commit ) THEN
348 COMMIT WORK;
349 END IF;
353 WHEN FND_API.G_EXC_ERROR THEN
350
351 EXCEPTION
352
354
355 ROLLBACK TO Update_Item_Tech_Data;
356 x_return_status := FND_API.G_RET_STS_ERROR ;
357 fnd_msg_pub.count_and_get (
358 p_count => x_msg_count
359 ,p_encoded => FND_API.g_false
360 ,p_data => x_msg_data);
361
362
363 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
364
365 ROLLBACK TO Update_Item_Tech_Data;
366 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
367 fnd_msg_pub.count_and_get (
368 p_count => x_msg_count
369 ,p_encoded => FND_API.g_false
370 ,p_data => x_msg_data);
371
372
373 WHEN OTHERS THEN
374
375 ROLLBACK TO Update_Item_Tech_Data;
376 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
377 fnd_msg_pub.count_and_get (
378 p_count => x_msg_count
379 ,p_encoded => FND_API.g_false
380 ,p_data => x_msg_data);
381
382 END UPDATE_ITEM_TECHNICAL_DATA;
383
384 --Start of comments
385 --+========================================================================+
386 --| API Name : DELETE_ITEM_TECHNICAL_DATA |
387 --| TYPE : Public |
388 --| Function : Deletes the Item technical data |
389 --| Notes : |
390 --| |
391 --| HISTORY |
392 --| S.Sriram 21-Feb-2005 Created |
393 --+========================================================================+
394 -- End of comments
395
396 PROCEDURE DELETE_ITEM_TECHNICAL_DATA
397 (
398 p_api_version IN NUMBER
399 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
400 , p_commit IN VARCHAR2 := FND_API.G_FALSE
401 , x_return_status OUT NOCOPY VARCHAR2
402 , x_msg_count OUT NOCOPY NUMBER
403 , x_msg_data OUT NOCOPY VARCHAR2
404 , p_tech_data_id IN NUMBER
405 )IS
406
407 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Item_Technical_Data';
408 l_api_version CONSTANT NUMBER := 1.0;
409
410 BEGIN
411
412 SAVEPOINT Delete_Item_Technical_Data;
413
414 -- Initialize message list if p_init_msg_list is set to TRUE.
415 IF FND_API.to_Boolean( p_init_msg_list )
416 THEN
417 FND_MSG_PUB.initialize;
418 END IF;
419
420 -- Standard call to check for call compatibility.
421 IF NOT FND_API.Compatible_API_Call (l_api_version ,
422 p_api_version ,
423 l_api_name ,
424 G_PKG_NAME
425 )
426 THEN
427 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
428 END IF;
429
430 -- Initialize API return status to success
431 x_return_status := FND_API.G_RET_STS_SUCCESS;
432
433 GMD_ITEM_TECHNICAL_DATA_PVT.DELETE_ITEM_TECHNICAL_DATA
434 ( p_api_version => p_api_version
435 , p_init_msg_list => FND_API.G_FALSE
436 , p_commit => FND_API.G_FALSE
437 , x_return_status => x_return_status
438 , x_msg_count => x_msg_count
439 , x_msg_data => x_msg_data
440 , p_tech_data_id => p_tech_data_id
441 );
442
443 IF FND_API.To_Boolean( p_commit ) THEN
444 COMMIT WORK;
445 END IF;
446
447 EXCEPTION
448 WHEN FND_API.G_EXC_ERROR THEN
449
450 ROLLBACK TO Delete_Item_Tech_Data;
451 x_return_status := FND_API.G_RET_STS_ERROR ;
452 fnd_msg_pub.count_and_get (
453 p_count => x_msg_count
454 ,p_encoded => FND_API.g_false
455 ,p_data => x_msg_data);
456
457
458 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
459
460 ROLLBACK TO Delete_Item_Tech_Data;
461 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
462 fnd_msg_pub.count_and_get (
463 p_count => x_msg_count
464 ,p_encoded => FND_API.g_false
465 ,p_data => x_msg_data);
466
467 WHEN OTHERS THEN
468
469 ROLLBACK TO Delete_Item_Tech_Data;
470 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
471 fnd_msg_pub.count_and_get (
472 p_count => x_msg_count
473 ,p_encoded => FND_API.g_false
474 ,p_data => x_msg_data);
475
476 END DELETE_ITEM_TECHNICAL_DATA;
477
478 --Start of comments
479 --+========================================================================+
480 --| API Name : VALIDATE_INPUT_PARAMS |
481 --| TYPE : Private |
482 --| Function : Validates whether the item technical data passed |
483 --| to the insert, update procedures are correct. |
484 --| Notes : |
488 --| Kalyani 23-Jun-2006 B5350197 Added check for serial |
485 --| |
486 --| HISTORY |
487 --| S.Sriram 21-Feb-2005 Created |
489 --| control |
490 --+========================================================================+
491 -- End of comments
492
493 --Procedure to validate parameters
494 PROCEDURE VALIDATE_INPUT_PARAMS
495 (
496 p_header_Rec IN GMD_ITEM_TECHNICAL_DATA_PUB.technical_data_hdr_rec
497 , p_dtl_Tbl IN GMD_ITEM_TECHNICAL_DATA_PUB.technical_data_dtl_tab
498 , p_operation IN VARCHAR2
499 , x_return_status OUT NOCOPY VARCHAR2
500 ) IS
501
502
503 CURSOR check_lab_orgn(l_orgn_id NUMBER) IS
504 SELECT 1
505 FROM org_access_view org, gmd_parameters_hdr p
506 WHERE org.organization_id = p.organization_id
507 AND p.organization_id = l_orgn_id
508 AND p.lab_ind = 1;
509
510 -- Bug 5350197
511 CURSOR check_item_is_valid(l_item_id NUMBER) IS
512 SELECT 1
513 FROM mtl_system_items
514 WHERE inventory_item_id = l_item_id
515 AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE,SYSDATE) AND NVL(END_DATE_ACTIVE,SYSDATE)
516 AND SERIAL_NUMBER_CONTROL_CODE = 1;
517
518
519 CURSOR check_lot_validity(l_lot_no VARCHAR2, l_lot_orgn_id NUMBER, l_item_id NUMBER) IS
520 SELECT 1
521 FROM mtl_lot_numbers
522 WHERE organization_id = l_lot_orgn_id
523 AND inventory_item_id = l_item_id
524 AND lot_number = l_lot_no;
525
526 CURSOR Cur_check_formula (l_item_id NUMBER, l_form_id NUMBER) IS
527 SELECT a.formula_id
528 FROM fm_form_mst a, fm_matl_dtl b
529 WHERE b.inventory_item_id = l_item_id
530 AND a.formula_id = b.formula_id
531 AND a.formula_id <> 0
532 AND b.line_type = 1
533 AND a.delete_mark =0
534 AND a.formula_id = l_form_id;
535
536
537 l_count NUMBER;
538 ITEM_MISSING EXCEPTION;
539 ORGANIZATION_MISSING EXCEPTION;
540 LOT_MISSING EXCEPTION;
541 LOT_ORGN_MISSING EXCEPTION;
542 INVALID_ITEM_NO EXCEPTION;
543 INVALID_LAB_ORGN EXCEPTION;
544 INVALID_LOT EXCEPTION;
545 INVALID_FORMULA EXCEPTION;
546
547
548 BEGIN
549
550 -- Initialize API return status to success
551 x_return_status := FND_API.G_RET_STS_SUCCESS;
552
553 -- Check if inventory_id is NOT NULL
554 IF p_header_rec.inventory_item_id IS NULL THEN
555 RAISE ITEM_MISSING;
556 END IF;
557
558 -- Check if organization_id is NOT NULL
559 IF p_header_rec.organization_id IS NULL THEN
560 RAISE ORGANIZATION_MISSING;
561 END IF;
562
563 -- Check whether the organization is a valid Lab
564 OPEN check_lab_orgn(p_header_rec.organization_id);
565 FETCH check_lab_orgn INTO l_count;
566 IF check_lab_orgn%NOTFOUND THEN
567 CLOSE check_lab_orgn;
568 RAISE INVALID_LAB_ORGN;
569 END IF;
570 CLOSE check_lab_orgn;
571
572
573 -- Check whether the Item is valid
574 OPEN check_item_is_valid(p_header_rec.inventory_item_id);
575 FETCH check_item_is_valid INTO l_count;
576 IF check_item_is_valid%NOTFOUND THEN
577 CLOSE check_item_is_valid;
578 RAISE INVALID_ITEM_NO;
579 END IF;
580 CLOSE check_item_is_valid;
581
582
583 -- If lot id is Not Null, verify that lot_organization_id is also Not Null and vice versa.
584 IF (p_header_rec.Lot_Number IS NOT NULL AND p_header_rec.lot_organization_id IS NULL) THEN
585 RAISE LOT_ORGN_MISSING;
586 END IF;
587
588 IF (p_header_rec.lot_organization_id IS NOT NULL AND p_header_rec.Lot_Number IS NULL) THEN
589 RAISE LOT_MISSING;
590 END IF;
591
592 -- Check whether the lot information is valid
593 IF (p_header_rec.Lot_Number IS NOT NULL AND p_header_rec.lot_organization_id IS NOT NULL) THEN
594 OPEN check_lot_validity(p_header_rec.Lot_Number, p_header_rec.lot_organization_id, p_header_rec.inventory_item_id);
595 FETCH check_lot_validity INTO l_count;
596 IF check_lot_validity%NOTFOUND THEN
597 RAISE INVALID_LOT;
598 CLOSE check_lot_validity;
599 END IF;
600 CLOSE check_lot_validity;
601 END IF;
602
603 /*-- Check that formula and batch id are both not present
604 IF (p_header_rec.FORMULA_ID IS NOT NULL AND p_header_rec.BATCH_ID IS NOT NULL) THEN
605 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
606 FND_MESSAGE.SET_TOKEN ('MISSING', 'FORMULA_ID');
607 FND_MSG_PUB.ADD;
608 x_return_status := FND_API.G_RET_STS_ERROR ;
609 RAISE FND_API.G_EXC_ERROR;
610 END IF;*/
611
612
613 -- Check whether the formula is valid
614 IF (p_header_rec.FORMULA_ID IS NOT NULL) THEN
615 OPEN Cur_check_formula(p_header_rec.inventory_item_id, p_header_rec.formula_id);
616 FETCH Cur_check_formula INTO l_count;
617 IF Cur_check_formula%NOTFOUND THEN
618 RAISE INVALID_FORMULA;
619 CLOSE Cur_check_formula;
620 END IF;
621 CLOSE Cur_check_formula;
622 END IF;
623
624 /*-- Check whether the batch is valid
625 IF (p_header_rec.BATCH_ID) IS NOT NULL THEN
626 OPEN check_batch(p_header_rec.batch_id);
627 FETCH check_batch INTO l_count;
628 IF check_batch%NOTFOUND THEN
629 FND_MESSAGE.SET_NAME ('GMD', 'GMD_BATCH_NOT_FOUND');
630 FND_MSG_PUB.ADD;
631 x_return_status := FND_API.G_RET_STS_ERROR ;
632 CLOSE check_batch;
633 RAISE FND_API.G_EXC_ERROR;
634 END IF;
635 CLOSE check_batch;
636 END IF;*/
637
638 EXCEPTION
639
640 WHEN ITEM_MISSING THEN
641 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
642 FND_MESSAGE.SET_TOKEN ('MISSING', 'INVENTORY_ITEM_ID');
643 FND_MSG_PUB.ADD;
644 x_return_status := FND_API.G_RET_STS_ERROR ;
645
646 WHEN ORGANIZATION_MISSING THEN
647 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
648 FND_MESSAGE.SET_TOKEN ('MISSING', 'ORGANIZATION_ID');
649 FND_MSG_PUB.ADD;
650 x_return_status := FND_API.G_RET_STS_ERROR ;
651
652 WHEN LOT_MISSING THEN
653 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
654 FND_MESSAGE.SET_TOKEN ('MISSING', 'LOT_NO');
655 FND_MSG_PUB.ADD;
656 x_return_status := FND_API.G_RET_STS_ERROR ;
657
658 WHEN LOT_ORGN_MISSING THEN
659 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
660 FND_MESSAGE.SET_TOKEN ('MISSING', 'LOT_ORGANIZATION_ID');
661 FND_MSG_PUB.ADD;
662 x_return_status := FND_API.G_RET_STS_ERROR ;
663
664 WHEN INVALID_ITEM_NO THEN
665 FND_MESSAGE.SET_NAME ('GMI', 'IC_INVALID_ITEM_NO');
666 FND_MSG_PUB.ADD;
667 x_return_status := FND_API.G_RET_STS_ERROR ;
668
669 WHEN INVALID_LAB_ORGN THEN
670 FND_MESSAGE.SET_NAME ('GMD', 'LM_BAD_LAB_TYPE');
671 FND_MSG_PUB.ADD;
672 x_return_status := FND_API.G_RET_STS_ERROR ;
673
674 WHEN INVALID_LOT THEN
675 FND_MESSAGE.SET_NAME ('GMI', 'IC_INVALID_LOT/SUBLOT');
676 FND_MSG_PUB.ADD;
677 x_return_status := FND_API.G_RET_STS_ERROR ;
678
679 WHEN INVALID_FORMULA THEN
680 FND_MESSAGE.SET_NAME ('GMD', 'LM_NOT_PROD');
681 FND_MSG_PUB.ADD;
682 x_return_status := FND_API.G_RET_STS_ERROR ;
683
684 END VALIDATE_INPUT_PARAMS;
685
686
687 --Start of comments
688 --+========================================================================+
689 --| API Name : FETCH_ITEM_TECHNICAL_DATA |
690 --| TYPE : Private |
691 --| Function : Fetches the Item technical data based on the input parm's|
692 --| passed. |
693 --| Notes : |
694 --| |
695 --| HISTORY |
696 --| S.Sriram 21-Feb-2005 Created |
697 --+========================================================================+
698 -- End of comments
699
700 PROCEDURE FETCH_ITEM_TECHNICAL_DATA (
701
702 p_api_version IN NUMBER
703 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
704 , x_msg_count OUT NOCOPY NUMBER
705 , x_msg_data OUT NOCOPY VARCHAR2
706 , p_header_rec IN technical_data_hdr_rec
707 , x_dtl_tbl OUT NOCOPY technical_data_dtl_tab
708 , x_return_status OUT NOCOPY VARCHAR2
709 ) IS
710
711 l_api_name CONSTANT VARCHAR2(30) := 'Fetch_Item_Technical_Data';
712 l_api_version CONSTANT NUMBER := 1.0;
713
714 l_return_status VARCHAR2(10);
715
716 BEGIN
717
718 -- Initialize message list if p_init_msg_list is set to TRUE.
719 IF FND_API.to_Boolean( p_init_msg_list ) THEN
720 FND_MSG_PUB.initialize;
721 END IF;
722
723 -- Standard call to check for call compatibility.
724 IF NOT FND_API.Compatible_API_Call (l_api_version ,
725 p_api_version ,
726 l_api_name ,
727 G_PKG_NAME
728 )
729 THEN
730 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
731 END IF;
732
733 -- Initialize API return status to success
734 x_return_status := FND_API.G_RET_STS_SUCCESS;
735
736 GMD_ITEM_TECHNICAL_DATA_PVT.FETCH_ITEM_TECHNICAL_DATA (
737 p_api_version => p_api_version
738 , p_init_msg_list => p_init_msg_list
739 , x_msg_count => x_msg_count
740 , x_msg_data => x_msg_data
741 , p_header_rec => p_header_rec
742 , x_dtl_tbl => x_dtl_tbl
743 , x_return_status => x_return_status
744 );
745
746 EXCEPTION
747
748 WHEN FND_API.G_EXC_ERROR THEN
749 x_return_status := FND_API.G_RET_STS_ERROR ;
750 fnd_msg_pub.count_and_get (
751 p_count => x_msg_count
752 ,p_encoded => FND_API.g_false
753 ,p_data => x_msg_data);
754
755
756 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
757 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
758 fnd_msg_pub.count_and_get (
759 p_count => x_msg_count
760 ,p_encoded => FND_API.g_false
761 ,p_data => x_msg_data);
762
763
764 WHEN OTHERS THEN
765 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
766 fnd_msg_pub.count_and_get (
767 p_count => x_msg_count
768 ,p_encoded => FND_API.g_false
769 ,p_data => x_msg_data);
770
771 END FETCH_ITEM_TECHNICAL_DATA;
772
773 END GMD_ITEM_TECHNICAL_DATA_PUB;
774