[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.11 2012/03/30 02:37:30 maychen 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 --| Raju Bug 9659759 Modified the code below to insert/update based on |
213 --| Existing row count for that tech data and tech param. |
214 --+========================================================================+
215 -- End of comments
216 PROCEDURE UPDATE_ITEM_TECHNICAL_DATA
217 (
218 p_api_version IN NUMBER
219 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
220 , p_commit IN VARCHAR2 := FND_API.G_FALSE
221 , x_return_status OUT NOCOPY VARCHAR2
222 , x_msg_count OUT NOCOPY NUMBER
223 , x_msg_data OUT NOCOPY VARCHAR2
224 , p_tech_data_id IN NUMBER
225 , p_dtl_tbl IN technical_data_dtl_tab
226 ) IS
227
228
229
230 l_api_name CONSTANT VARCHAR2(30) := 'Update_Item_Technical_Data';
231 l_api_version CONSTANT NUMBER := 1.0;
232
233
234 -- Cursor to fetch data type for the technical parameter passed
235 CURSOR get_tech_data_type (l_tech_parm_id NUMBER, l_orgn_id NUMBER) IS
236 SELECT data_type
237 FROM gmd_tech_parameters_b
238 WHERE tech_parm_id = l_tech_parm_id
239 AND (organization_id = l_orgn_id OR organization_id IS NULL);
240
241 CURSOR get_Orgn_id IS
242 SELECT organization_id
243 FROM gmd_technical_data_hdr
244 WHERE tech_data_id = p_tech_data_id;
245
246 CURSOR get_row_count (l_tech_parm_id NUMBER)IS
247 SELECT COUNT(1)
248 FROM gmd_technical_data_dtl
249 WHERE tech_data_id = p_tech_data_id
250 AND tech_parm_id = l_tech_parm_id;
251
252 CURSOR Cur_check_valid IS
253 SELECT 1
254 FROM gmd_technical_data_dtl
255 WHERE tech_data_id = p_tech_data_id;
256
257 l_text_data VARCHAR2(200) := NULL;
258 l_num_data NUMBER;
259 l_bool_data NUMBER;
260 l_return_status VARCHAR2(10);
261 l_data_type NUMBER;
262 l_orgn_id NUMBER;
263 l_tech_parm_id NUMBER;
264 l_sort_seq NUMBER;
265 l_text_code NUMBER;
266 l_temp NUMBER;
267 l_count NUMBER;
268 l_tech_data_id NUMBER;
269 no_tech_data_value EXCEPTION;
270 invalid_tech_data_value EXCEPTION;
271 BEGIN
272
273
274 SAVEPOINT Update_Item_Tech_Data_PUB;
275 l_tech_data_id := p_tech_data_id;
276 -- Initialize message list if p_init_msg_list is set to TRUE.
277 IF FND_API.to_Boolean( p_init_msg_list )
278 THEN
279 FND_MSG_PUB.initialize;
280 END IF;
281
282
283 -- Standard call to check for call compatibility.
284 IF NOT FND_API.Compatible_API_Call (l_api_version ,
285 p_api_version ,
286 l_api_name ,
287 G_PKG_NAME
288 )
289 THEN
290 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
291 END IF;
292
293 -- Initialize API return status to success
294
295 x_return_status := FND_API.G_RET_STS_SUCCESS;
296
297 /*-- Validate i/p parm's here
298 Validate_Input_Params
299 (
300 p_header_rec => p_header_rec
301 , p_dtl_tbl => p_dtl_tbl
302 , p_operation => 'UPDATE'
303 , x_return_status => l_return_status
304 );
305
306
307 IF l_return_status IN (FND_API.G_RET_STS_ERROR, FND_API.G_RET_STS_UNEXP_ERROR) THEN
308 FND_MESSAGE.SET_NAME('GMD','GMF_API_NO_ROWS_UPD');
309 FND_MSG_PUB.ADD;
310 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
311 END IF;*/
312
313 --Checked for valid tech data id
314 IF p_tech_data_id IS NOT NULL THEN
315 OPEN Cur_check_valid;
316 FETCH Cur_check_valid INTO l_temp;
317 IF (Cur_check_valid%NOTFOUND) THEN
318 FND_MESSAGE.SET_NAME('GMP','GMP_INVALID_VALUE');
319 FND_MESSAGE.SET_TOKEN('FIELD','TECH_DATA_ID');
320 FND_MESSAGE.SET_TOKEN('VALUE',l_tech_data_id);
321 FND_MSG_PUB.ADD;
322 RAISE invalid_tech_data_value;
323 END IF;
324 CLOSE Cur_check_valid;
325 ELSE
326 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
327 FND_MESSAGE.SET_TOKEN ('MISSING', 'TECHDATAID');
328 FND_MSG_PUB.ADD;
329 RAISE no_tech_data_value;
330 END IF;
331
332 OPEN get_Orgn_id;
333 FETCH get_Orgn_id INTO l_orgn_id;
334 CLOSE get_Orgn_id;
335
336 -- Update Item Technical Data detail records
337 FOR i IN p_dtl_tbl.FIRST .. p_dtl_tbl.LAST
338 LOOP
339 l_num_data := NULL;
340 l_text_data := NULL;
341 l_bool_data := NULL;
342
343 OPEN get_tech_data_type (p_dtl_tbl(i).tech_parm_id,l_orgn_id );
344 FETCH get_tech_data_type INTO l_data_type;
345 CLOSE get_tech_data_type;
346
347
348 IF l_data_type IN (1,5,6,7,8,9,10,11) THEN
349 l_num_data := TO_NUMBER(p_dtl_tbl(i).Tech_Data);
350 ELSIF l_data_type IN (0,2, 4) THEN
351 l_text_data := p_dtl_tbl(i).Tech_Data;
352 ELSIF l_data_type = 3 THEN
353 l_bool_data := p_dtl_tbl(i).Tech_Data;
354 END IF;
355 -- data type 4 - verify
356
357 l_tech_parm_id := p_dtl_tbl(i).Tech_Parm_Id;
358 l_sort_seq := p_dtl_tbl(i).Sort_Seq;
359 l_text_code := p_dtl_tbl(i).Text_Code;
360
361 --Get the row count from the table and based on that call update or insert api.
362 OPEN get_row_count(p_dtl_tbl(i).tech_parm_id);
363 FETCH get_row_count INTO l_count;
364 CLOSE get_row_count;
365
366 --Added the following if elsif conditions for bug 9659759
367 IF (l_count = 0 AND p_dtl_tbl(i).tech_data IS NOT NULL) THEN
368 GMD_ITEM_TECHNICAL_DATA_PVT.INSERT_ITEM_TECHNICAL_DATA_DTL
369 (p_api_version => p_api_version
370 , p_init_msg_list => p_init_msg_list
371 , p_commit => p_commit
372 , x_return_status => x_return_status
373 , x_msg_count => x_msg_count
374 , x_msg_data => x_msg_data
375 , x_tech_data_id => l_tech_data_id
376 , x_tech_parm_id => l_tech_parm_id
377 , p_sort_seq => l_sort_seq
378 , p_text_data => l_text_data
379 , p_num_data => l_num_data
380 , p_boolean_data => l_bool_data
381 , p_text_code => l_text_code
382 , p_creation_date => SYSDATE
383 , p_created_by => FND_GLOBAL.USER_ID
384 , p_last_update_date => SYSDATE
385 , p_last_updated_by => FND_GLOBAL.USER_ID
386 , p_last_update_login => FND_GLOBAL.LOGIN_ID);
387 ELSIF (l_count > 0 AND p_dtl_tbl(i).tech_data IS NOT NULL) THEN
388 GMD_ITEM_TECHNICAL_DATA_PVT.UPDATE_ITEM_TECHNICAL_DATA
389 (p_api_version => p_api_version
390 , p_init_msg_list => p_init_msg_list
391 , p_commit => p_commit
392 , x_return_status => x_return_status
393 , x_msg_count => x_msg_count
394 , x_msg_data => x_msg_data
395 , p_tech_data_id => p_tech_data_id
396 , x_tech_parm_id => l_tech_parm_id
397 , p_sort_seq => l_sort_seq
398 , p_text_data => l_text_data
399 , p_num_data => l_num_data
400 , p_boolean_data => l_bool_data
401 , p_text_code => l_text_code
402 , p_last_update_date => SYSDATE
403 , p_last_updated_by => FND_GLOBAL.USER_ID
404 , p_last_update_login => FND_GLOBAL.LOGIN_ID);
405 END IF;
406 END LOOP;
407
408
409 IF FND_API.To_Boolean( p_commit ) THEN
410 COMMIT WORK;
411 END IF;
412
413 EXCEPTION
414
415 WHEN FND_API.G_EXC_ERROR THEN
416
417 ROLLBACK TO Update_Item_Tech_Data_PUB;
418 x_return_status := FND_API.G_RET_STS_ERROR ;
419 fnd_msg_pub.count_and_get (
420 p_count => x_msg_count
421 ,p_encoded => FND_API.g_false
422 ,p_data => x_msg_data);
423
424 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
425
426 ROLLBACK TO Update_Item_Tech_Data_PUB;
427 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
428 fnd_msg_pub.count_and_get (
429 p_count => x_msg_count
430 ,p_encoded => FND_API.g_false
431 ,p_data => x_msg_data);
432
433 WHEN no_tech_data_value THEN
434 ROLLBACK TO Update_Item_Tech_Data_PUB;
435 x_return_status := FND_API.G_RET_STS_ERROR;
436 FND_MSG_PUB.COUNT_AND_GET (P_count => x_msg_count,
437 P_data => x_msg_data);
438 WHEN invalid_tech_data_value THEN
439 ROLLBACK TO Update_Item_Tech_Data_PUB;
440 x_return_status := FND_API.G_RET_STS_ERROR;
441 FND_MSG_PUB.COUNT_AND_GET (P_count => x_msg_count,
442 P_data => x_msg_data);
443
444 WHEN OTHERS THEN
445
446 ROLLBACK TO Update_Item_Tech_Data_PUB;
447 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
448 fnd_msg_pub.count_and_get (
449 p_count => x_msg_count
450 ,p_encoded => FND_API.g_false
451 ,p_data => x_msg_data);
452
453 END UPDATE_ITEM_TECHNICAL_DATA;
454
455 --Start of comments
456 --+========================================================================+
457 --| API Name : DELETE_ITEM_TECHNICAL_DATA |
458 --| TYPE : Public |
459 --| Function : Deletes the Item technical data |
460 --| Notes : |
461 --| |
462 --| HISTORY |
463 --| S.Sriram 21-Feb-2005 Created |
464 --+========================================================================+
465 -- End of comments
466
467 PROCEDURE DELETE_ITEM_TECHNICAL_DATA
468 (
469 p_api_version IN NUMBER
470 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
471 , p_commit IN VARCHAR2 := FND_API.G_FALSE
472 , x_return_status OUT NOCOPY VARCHAR2
473 , x_msg_count OUT NOCOPY NUMBER
474 , x_msg_data OUT NOCOPY VARCHAR2
475 , p_tech_data_id IN NUMBER
476 )IS
477
478 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Item_Technical_Data';
479 l_api_version CONSTANT NUMBER := 1.0;
480
481 BEGIN
482
483 SAVEPOINT Delete_Item_Tech_Data_PUB;
484
485 -- Initialize message list if p_init_msg_list is set to TRUE.
486 IF FND_API.to_Boolean( p_init_msg_list )
487 THEN
488 FND_MSG_PUB.initialize;
489 END IF;
490
491 -- Standard call to check for call compatibility.
492 IF NOT FND_API.Compatible_API_Call (l_api_version ,
493 p_api_version ,
494 l_api_name ,
495 G_PKG_NAME
496 )
497 THEN
498 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
499 END IF;
500
501 -- Initialize API return status to success
502 x_return_status := FND_API.G_RET_STS_SUCCESS;
503
504 GMD_ITEM_TECHNICAL_DATA_PVT.DELETE_ITEM_TECHNICAL_DATA
505 ( p_api_version => p_api_version
506 , p_init_msg_list => FND_API.G_FALSE
507 , p_commit => FND_API.G_FALSE
508 , x_return_status => x_return_status
509 , x_msg_count => x_msg_count
510 , x_msg_data => x_msg_data
511 , p_tech_data_id => p_tech_data_id
512 );
513
514 IF FND_API.To_Boolean( p_commit ) THEN
515 COMMIT WORK;
516 END IF;
517
518 EXCEPTION
519 WHEN FND_API.G_EXC_ERROR THEN
520
521 ROLLBACK TO Delete_Item_Tech_Data_PUB;
522 x_return_status := FND_API.G_RET_STS_ERROR ;
523 fnd_msg_pub.count_and_get (
524 p_count => x_msg_count
525 ,p_encoded => FND_API.g_false
526 ,p_data => x_msg_data);
527
528
529 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
530
531 ROLLBACK TO Delete_Item_Tech_Data_PUB;
532 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
533 fnd_msg_pub.count_and_get (
534 p_count => x_msg_count
535 ,p_encoded => FND_API.g_false
536 ,p_data => x_msg_data);
537
538 WHEN OTHERS THEN
539
540 ROLLBACK TO Delete_Item_Tech_Data_PUB;
541 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
542 fnd_msg_pub.count_and_get (
543 p_count => x_msg_count
544 ,p_encoded => FND_API.g_false
545 ,p_data => x_msg_data);
546
547 END DELETE_ITEM_TECHNICAL_DATA;
548
549 --Start of comments
550 --+========================================================================+
551 --| API Name : VALIDATE_INPUT_PARAMS |
552 --| TYPE : Private |
553 --| Function : Validates whether the item technical data passed |
554 --| to the insert, update procedures are correct. |
555 --| Notes : |
556 --| |
557 --| HISTORY |
558 --| S.Sriram 21-Feb-2005 Created |
559 --| Kalyani 23-Jun-2006 B5350197 Added check for serial |
560 --| control |
561 --+========================================================================+
562 -- End of comments
563
564 --Procedure to validate parameters
565 PROCEDURE VALIDATE_INPUT_PARAMS
566 (
567 p_header_Rec IN GMD_ITEM_TECHNICAL_DATA_PUB.technical_data_hdr_rec
568 , p_dtl_Tbl IN GMD_ITEM_TECHNICAL_DATA_PUB.technical_data_dtl_tab
569 , p_operation IN VARCHAR2
570 , x_return_status OUT NOCOPY VARCHAR2
571 ) IS
572
573 CURSOR c_get_recs IS
574 SELECT count(*)
575 FROM gmd_technical_data_hdr
576 WHERE organization_id = p_header_rec.organization_id
577 AND inventory_item_id = p_header_rec.inventory_item_id
578 AND NVL(lot_number, -1) = nvl(p_header_rec.lot_number, -1) /* Added NVL in bug No.6051738 */
579 AND NVL(lot_organization_id,-1) = nvl(p_header_rec.lot_organization_id, -1) /* Added this condition in bug No.6051738 */
580 AND NVL(formula_id, 0) = NVL(p_header_rec.formula_id, 0)
581 AND NVL(batch_id, -1) = NVL(p_header_rec.batch_id, -1);
582
583 CURSOR check_lab_orgn(l_orgn_id NUMBER) IS
584 SELECT 1
585 FROM org_access_view org, gmd_parameters_hdr p
586 WHERE org.organization_id = p.organization_id
587 AND p.organization_id = l_orgn_id
588 AND p.lab_ind = 1;
589
590 -- Bug 5350197
591 CURSOR check_item_is_valid(l_item_id NUMBER) IS
592 SELECT 1
593 FROM mtl_system_items
594 WHERE inventory_item_id = l_item_id
595 AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE,SYSDATE) AND NVL(END_DATE_ACTIVE,SYSDATE)
596 AND SERIAL_NUMBER_CONTROL_CODE = 1;
597
598
599 CURSOR check_lot_validity(l_lot_no VARCHAR2, l_lot_orgn_id NUMBER, l_item_id NUMBER) IS
600 SELECT 1
601 FROM mtl_lot_numbers
602 WHERE organization_id = l_lot_orgn_id
603 AND inventory_item_id = l_item_id
604 AND lot_number = l_lot_no;
605
606 CURSOR Cur_check_formula (l_item_id NUMBER, l_form_id NUMBER) IS
607 SELECT a.formula_id
608 FROM fm_form_mst a, fm_matl_dtl b
609 WHERE b.inventory_item_id = l_item_id
610 AND a.formula_id = b.formula_id
611 AND a.formula_id <> 0
612 AND b.line_type = 1
613 AND a.delete_mark =0
614 AND a.formula_id = l_form_id;
615
616 X_cnt NUMBER;
617 l_count NUMBER;
618 ITEM_MISSING EXCEPTION;
619 ORGANIZATION_MISSING EXCEPTION;
620 LOT_MISSING EXCEPTION;
621 LOT_ORGN_MISSING EXCEPTION;
622 INVALID_ITEM_NO EXCEPTION;
623 INVALID_LAB_ORGN EXCEPTION;
624 INVALID_LOT EXCEPTION;
625 INVALID_FORMULA EXCEPTION;
626 DUPLICATE_RECORD EXCEPTION;
627
628 BEGIN
629
630 -- Initialize API return status to success
631 x_return_status := FND_API.G_RET_STS_SUCCESS;
632
633 -- Check if inventory_id is NOT NULL
634 IF p_header_rec.inventory_item_id IS NULL THEN
635 RAISE ITEM_MISSING;
636 END IF;
637
638 -- Check if organization_id is NOT NULL
639 IF p_header_rec.organization_id IS NULL THEN
640 RAISE ORGANIZATION_MISSING;
641 END IF;
642
643 -- Check whether the organization is a valid Lab
644 OPEN check_lab_orgn(p_header_rec.organization_id);
645 FETCH check_lab_orgn INTO l_count;
646 IF check_lab_orgn%NOTFOUND THEN
647 CLOSE check_lab_orgn;
648 RAISE INVALID_LAB_ORGN;
649 END IF;
650 CLOSE check_lab_orgn;
651
652
653 -- Check whether the Item is valid
654 OPEN check_item_is_valid(p_header_rec.inventory_item_id);
655 FETCH check_item_is_valid INTO l_count;
656 IF check_item_is_valid%NOTFOUND THEN
657 CLOSE check_item_is_valid;
658 RAISE INVALID_ITEM_NO;
659 END IF;
660 CLOSE check_item_is_valid;
661
662
663 -- If lot id is Not Null, verify that lot_organization_id is also Not Null and vice versa.
664 IF (p_header_rec.Lot_Number IS NOT NULL AND p_header_rec.lot_organization_id IS NULL) THEN
665 RAISE LOT_ORGN_MISSING;
666 END IF;
667
668 IF (p_header_rec.lot_organization_id IS NOT NULL AND p_header_rec.Lot_Number IS NULL) THEN
669 RAISE LOT_MISSING;
670 END IF;
671
672 -- Check whether the lot information is valid
673 IF (p_header_rec.Lot_Number IS NOT NULL AND p_header_rec.lot_organization_id IS NOT NULL) THEN
674 OPEN check_lot_validity(p_header_rec.Lot_Number, p_header_rec.lot_organization_id, p_header_rec.inventory_item_id);
675 FETCH check_lot_validity INTO l_count;
676 IF check_lot_validity%NOTFOUND THEN
677 RAISE INVALID_LOT;
678 CLOSE check_lot_validity;
679 END IF;
680 CLOSE check_lot_validity;
681 END IF;
682
683 /*-- Check that formula and batch id are both not present
684 IF (p_header_rec.FORMULA_ID IS NOT NULL AND p_header_rec.BATCH_ID IS NOT NULL) THEN
685 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
686 FND_MESSAGE.SET_TOKEN ('MISSING', 'FORMULA_ID');
687 FND_MSG_PUB.ADD;
688 x_return_status := FND_API.G_RET_STS_ERROR ;
689 RAISE FND_API.G_EXC_ERROR;
690 END IF;*/
691
692
693 -- Check whether the formula is valid
694 IF (p_header_rec.FORMULA_ID IS NOT NULL) THEN
695 OPEN Cur_check_formula(p_header_rec.inventory_item_id, p_header_rec.formula_id);
696 FETCH Cur_check_formula INTO l_count;
697 IF Cur_check_formula%NOTFOUND THEN
698 RAISE INVALID_FORMULA;
699 CLOSE Cur_check_formula;
700 END IF;
701 CLOSE Cur_check_formula;
702 END IF;
703
704 /*-- Check whether the batch is valid
705 IF (p_header_rec.BATCH_ID) IS NOT NULL THEN
706 OPEN check_batch(p_header_rec.batch_id);
707 FETCH check_batch INTO l_count;
708 IF check_batch%NOTFOUND THEN
709 FND_MESSAGE.SET_NAME ('GMD', 'GMD_BATCH_NOT_FOUND');
710 FND_MSG_PUB.ADD;
711 x_return_status := FND_API.G_RET_STS_ERROR ;
712 CLOSE check_batch;
713 RAISE FND_API.G_EXC_ERROR;
714 END IF;
715 CLOSE check_batch;
716 END IF;*/
717
718 OPEN c_get_recs;
719 FETCH c_get_recs INTO X_cnt;
720 CLOSE c_get_recs;
721 IF X_cnt > 0 THEN
722 RAISE DUPLICATE_RECORD;
723 END IF;
724
725 EXCEPTION
726
727 WHEN ITEM_MISSING THEN
728 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
729 FND_MESSAGE.SET_TOKEN ('MISSING', 'INVENTORY_ITEM_ID');
730 FND_MSG_PUB.ADD;
731 x_return_status := FND_API.G_RET_STS_ERROR ;
732
733 WHEN DUPLICATE_RECORD THEN
734 FND_MESSAGE.SET_NAME('GMP','PS_DUP_REC');
735 FND_MSG_PUB.ADD;
736 x_return_status := FND_API.G_RET_STS_ERROR ;
737
738 WHEN ORGANIZATION_MISSING THEN
739 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
740 FND_MESSAGE.SET_TOKEN ('MISSING', 'ORGANIZATION_ID');
741 FND_MSG_PUB.ADD;
742 x_return_status := FND_API.G_RET_STS_ERROR ;
743
744 WHEN LOT_MISSING THEN
745 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
746 FND_MESSAGE.SET_TOKEN ('MISSING', 'LOT_NO');
747 FND_MSG_PUB.ADD;
748 x_return_status := FND_API.G_RET_STS_ERROR ;
749
750 WHEN LOT_ORGN_MISSING THEN
751 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
752 FND_MESSAGE.SET_TOKEN ('MISSING', 'LOT_ORGANIZATION_ID');
753 FND_MSG_PUB.ADD;
754 x_return_status := FND_API.G_RET_STS_ERROR ;
755
756 WHEN INVALID_ITEM_NO THEN
757 FND_MESSAGE.SET_NAME ('GMI', 'IC_INVALID_ITEM_NO');
758 FND_MSG_PUB.ADD;
759 x_return_status := FND_API.G_RET_STS_ERROR ;
760
761 WHEN INVALID_LAB_ORGN THEN
762 FND_MESSAGE.SET_NAME ('GMD', 'LM_BAD_LAB_TYPE');
763 FND_MSG_PUB.ADD;
764 x_return_status := FND_API.G_RET_STS_ERROR ;
765
766 WHEN INVALID_LOT THEN
767 FND_MESSAGE.SET_NAME ('GMI', 'IC_INVALID_LOT/SUBLOT');
768 FND_MSG_PUB.ADD;
769 x_return_status := FND_API.G_RET_STS_ERROR ;
770
771 WHEN INVALID_FORMULA THEN
772 FND_MESSAGE.SET_NAME ('GMD', 'LM_NOT_PROD');
773 FND_MSG_PUB.ADD;
774 x_return_status := FND_API.G_RET_STS_ERROR ;
775
776 END VALIDATE_INPUT_PARAMS;
777
778
779 --Start of comments
780 --+========================================================================+
781 --| API Name : FETCH_ITEM_TECHNICAL_DATA |
782 --| TYPE : Private |
783 --| Function : Fetches the Item technical data based on the input parm's|
784 --| passed. |
785 --| Notes : |
786 --| |
787 --| HISTORY |
788 --| S.Sriram 21-Feb-2005 Created |
789 --+========================================================================+
790 -- End of comments
791
792 PROCEDURE FETCH_ITEM_TECHNICAL_DATA (
793
794 p_api_version IN NUMBER
795 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
796 , x_msg_count OUT NOCOPY NUMBER
797 , x_msg_data OUT NOCOPY VARCHAR2
798 , p_header_rec IN technical_data_hdr_rec
799 , x_dtl_tbl OUT NOCOPY technical_data_dtl_tab
800 , x_return_status OUT NOCOPY VARCHAR2
801 ) IS
802
803 l_api_name CONSTANT VARCHAR2(30) := 'Fetch_Item_Technical_Data';
804 l_api_version CONSTANT NUMBER := 1.0;
805
806 l_return_status VARCHAR2(10);
807
808 BEGIN
809
810 -- Initialize message list if p_init_msg_list is set to TRUE.
811 IF FND_API.to_Boolean( p_init_msg_list ) THEN
812 FND_MSG_PUB.initialize;
813 END IF;
814
815 -- Standard call to check for call compatibility.
816 IF NOT FND_API.Compatible_API_Call (l_api_version ,
817 p_api_version ,
818 l_api_name ,
819 G_PKG_NAME
820 )
821 THEN
822 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
823 END IF;
824
825 -- Initialize API return status to success
826 x_return_status := FND_API.G_RET_STS_SUCCESS;
827
828 GMD_ITEM_TECHNICAL_DATA_PVT.FETCH_ITEM_TECHNICAL_DATA (
829 p_api_version => p_api_version
830 , p_init_msg_list => p_init_msg_list
831 , x_msg_count => x_msg_count
832 , x_msg_data => x_msg_data
833 , p_header_rec => p_header_rec
834 , x_dtl_tbl => x_dtl_tbl
835 , x_return_status => x_return_status
836 );
837
838 EXCEPTION
839
840 WHEN FND_API.G_EXC_ERROR THEN
841 x_return_status := FND_API.G_RET_STS_ERROR ;
842 fnd_msg_pub.count_and_get (
843 p_count => x_msg_count
844 ,p_encoded => FND_API.g_false
845 ,p_data => x_msg_data);
846
847
848 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
849 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
850 fnd_msg_pub.count_and_get (
851 p_count => x_msg_count
852 ,p_encoded => FND_API.g_false
853 ,p_data => x_msg_data);
854
855
856 WHEN OTHERS THEN
857 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
858 fnd_msg_pub.count_and_get (
859 p_count => x_msg_count
860 ,p_encoded => FND_API.g_false
861 ,p_data => x_msg_data);
862
863 END FETCH_ITEM_TECHNICAL_DATA;
864
865 END GMD_ITEM_TECHNICAL_DATA_PUB;
866