1 PACKAGE BODY GMD_ITEM_TECHNICAL_DATA_PVT AS
2 /* $Header: GMDVITDB.pls 120.4 2006/03/16 00:44:44 kmotupal noship $ */
3
4 --Global Variables
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'GMD_ITEM_TECHNICAL_DATA_PVT';
6
7 --Start of comments
8 --+========================================================================+
9 --| API Name : INSERT_ITEM_TECHNICAL_DATA_HDR |
10 --| TYPE : Private |
11 --| Function : Inserts the Item technical data header record |
12 --| Notes : |
13 --| |
14 --| HISTORY |
15 --| S.Sriram 21-Feb-2005 Created |
16 --+========================================================================+
17 -- End of comments
18
19 PROCEDURE INSERT_ITEM_TECHNICAL_DATA_HDR
20 (
21 p_api_version IN NUMBER
22 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
23 , p_commit IN VARCHAR2 := FND_API.G_FALSE
24 , x_return_status OUT NOCOPY VARCHAR2
25 , x_msg_count OUT NOCOPY NUMBER
26 , x_msg_data OUT NOCOPY VARCHAR2
27 , x_tech_data_id IN OUT NOCOPY NUMBER
28 , p_organization_id IN NUMBER
29 , p_inventory_item_id IN NUMBER
30 , p_lot_no IN VARCHAR2
31 , p_lot_organization_id IN NUMBER
32 , p_formula_id IN NUMBER
33 , p_batch_id IN NUMBER
34 , p_delete_mark IN NUMBER
35 , p_text_code IN NUMBER
36 , p_creation_date IN DATE
37 , p_created_by IN NUMBER
38 , p_last_update_date IN DATE
39 , p_last_updated_by IN NUMBER
40 , p_last_update_login IN NUMBER
41 ) IS
42
43 l_api_name CONSTANT VARCHAR2(40) := 'Insert_Item_Technical_Data_hdr';
44 l_api_version CONSTANT NUMBER := 1.0;
45
46 l_tech_data_id NUMBER;
47 l_data_type NUMBER;
48
49 BEGIN
50
51 -- Standard Start of API savepoint
52 SAVEPOINT Insert_Item_Technical_Data ;
53
54 -- Initialize message list if p_init_msg_list is set to TRUE.
55 IF FND_API.to_Boolean( p_init_msg_list ) THEN
56 FND_MSG_PUB.initialize;
57 END IF;
58
59 -- Standard call to check for call compatibility.
60 IF NOT FND_API.Compatible_API_Call (l_api_version ,
61 p_api_version ,
62 l_api_name ,
63 G_PKG_NAME
64 )
65 THEN
66
67 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
68 END IF;
69
70 -- Initialize API return status to success
71 x_return_status := FND_API.G_RET_STS_SUCCESS;
72
73 IF (x_tech_data_id IS NULL) THEN
74 SELECT GMD_TECH_DATA_ID_S.NEXTVAL
75 INTO l_tech_data_id
76 FROM FND_DUAL;
77 ELSE
78 l_tech_data_id := x_tech_data_id;
79 END IF;
80 x_tech_data_id := l_tech_data_id;
81
82 INSERT INTO GMD_TECHNICAL_DATA_HDR (
83 TECH_DATA_ID,
84 INVENTORY_ITEM_ID,
85 ORGANIZATION_ID,
86 LOT_ORGANIZATION_ID,
87 LOT_NUMBER,
88 FORMULA_ID,
89 BATCH_ID,
90 DELETE_MARK,
91 TEXT_CODE,
92 CREATION_DATE,
93 CREATED_BY,
94 LAST_UPDATE_DATE,
95 LAST_UPDATED_BY,
96 LAST_UPDATE_LOGIN
97 ) VALUES (
98 x_tech_data_id,
99 p_inventory_item_id,
100 p_organization_id,
101 p_lot_organization_id,
102 p_lot_no,
103 p_formula_id,
104 p_batch_id,
105 p_delete_mark,
106 p_text_code,
107 p_creation_date,
108 p_created_by,
109 p_last_update_date,
110 p_last_updated_by,
111 p_last_update_login
112 );
113
114 IF (SQL%ROWCOUNT < 1) THEN
115 RAISE NO_DATA_FOUND;
116 END IF;
117
118 EXCEPTION
119 WHEN OTHERS THEN
120 ROLLBACK TO Insert_Item_Technical_Data;
121 FND_MSG_PUB.Add_Exc_Msg('GMD_ITEM_TECHNICAL_DATA_PVT', 'INSERT_ITEM_TECHNICAL_DATA_HDR');
122 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
123 fnd_msg_pub.count_and_get (
124 p_count => x_msg_count
125 ,p_encoded => FND_API.g_false
126 ,p_data => x_msg_data);
127
128 END INSERT_ITEM_TECHNICAL_DATA_HDR;
129
130 --Start of comments
131 --+========================================================================+
132 --| API Name : INSERT_ITEM_TECHNICAL_DATA_DTL |
133 --| TYPE : Private |
134 --| Function : Inserts the Item technical data detail records |
135 --| Notes : |
136 --| |
137 --| HISTORY |
138 --| S.Sriram 21-Feb-2005 Created |
139 --+========================================================================+
140 -- End of comments
141
142 PROCEDURE INSERT_ITEM_TECHNICAL_DATA_DTL
143 (
144 p_api_version IN NUMBER
145 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
146 , p_commit IN VARCHAR2 := FND_API.G_FALSE
147 , x_return_status OUT NOCOPY VARCHAR2
148 , x_msg_count OUT NOCOPY NUMBER
149 , x_msg_data OUT NOCOPY VARCHAR2
150 , x_tech_data_id IN OUT NOCOPY NUMBER
151 , x_tech_parm_id IN OUT NOCOPY NUMBER
152 , p_sort_seq IN NUMBER
153 , p_text_data IN VARCHAR2
154 , p_num_data IN NUMBER
155 , p_boolean_data IN NUMBER
156 , p_text_code IN NUMBER
157 , p_creation_date IN DATE
158 , p_created_by IN NUMBER
159 , p_last_update_date IN DATE
160 , p_last_updated_by IN NUMBER
161 , p_last_update_login IN NUMBER
162 ) IS
163
164 l_api_name CONSTANT VARCHAR2(30) := 'Insert_Item_Technical_Data_dtl';
165 l_api_version CONSTANT NUMBER := 1.0;
166
167 l_tech_data_id NUMBER;
168 l_data_type NUMBER;
169
170 BEGIN
171
172 -- Standard Start of API savepoint
173 SAVEPOINT Insert_Item_Technical_Data ;
174
175 -- Initialize message list if p_init_msg_list is set to TRUE.
176 IF FND_API.to_Boolean( p_init_msg_list )
177 THEN
178 FND_MSG_PUB.initialize;
179 END IF;
180
181 -- Standard call to check for call compatibility.
182 IF NOT FND_API.Compatible_API_Call (l_api_version ,
183 p_api_version ,
184 l_api_name ,
185 G_PKG_NAME
186 )
187 THEN
188 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
189 END IF;
190
191 -- Initialize API return status to success
192 x_return_status := FND_API.G_RET_STS_SUCCESS;
193
194 INSERT INTO GMD_TECHNICAL_DATA_DTL (
195 TECH_DATA_ID,
196 TECH_PARM_ID,
197 SORT_SEQ,
198 TEXT_DATA,
199 NUM_DATA,
200 BOOLEAN_DATA,
201 TEXT_CODE,
202 CREATION_DATE,
203 CREATED_BY,
204 LAST_UPDATE_DATE,
205 LAST_UPDATED_BY,
206 LAST_UPDATE_LOGIN
207 ) VALUES (
208 x_tech_data_id,
209 x_tech_parm_id,
210 p_sort_seq,
211 p_text_data,
212 p_num_data,
213 p_boolean_data,
214 p_text_code,
215 p_creation_date,
216 p_created_by,
217 p_last_update_date,
218 p_last_updated_by,
219 p_last_update_login
220
221 );
222
223 IF (SQL%ROWCOUNT < 1) THEN
224 RAISE NO_DATA_FOUND;
225 END IF;
226
227 EXCEPTION
228 WHEN OTHERS THEN
229 ROLLBACK TO Insert_Item_Technical_Data;
230 FND_MSG_PUB.Add_Exc_Msg('GMD_ITEM_TECHNICAL_DATA_PVT', 'INSERT_ITEM_TECHNICAL_DATA_DTL');
231 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
232 fnd_msg_pub.count_and_get (
233 p_count => x_msg_count
234 ,p_encoded => FND_API.g_false
235 ,p_data => x_msg_data);
236
237
238 END INSERT_ITEM_TECHNICAL_DATA_DTL;
239
240 --Start of comments
241 --+========================================================================+
242 --| API Name : UPDATE_ITEM_TECHNICAL_DATA |
243 --| TYPE : Private |
244 --| Function : Updates the Item technical data detail records |
245 --| Notes : |
246 --| |
247 --| HISTORY |
248 --| S.Sriram 21-Feb-2005 Created |
249 --+========================================================================+
250 -- End of comments
251
252 PROCEDURE UPDATE_ITEM_TECHNICAL_DATA
253 (
254 p_api_version IN NUMBER
255 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
256 , p_commit IN VARCHAR2 := FND_API.G_FALSE
257 , x_return_status OUT NOCOPY VARCHAR2
258 , x_msg_count OUT NOCOPY NUMBER
259 , x_msg_data OUT NOCOPY VARCHAR2
260 , p_tech_data_id IN NUMBER
261 , x_tech_parm_id IN OUT NOCOPY NUMBER
262 , p_sort_seq IN NUMBER
263 , p_text_data IN VARCHAR2
264 , p_num_data IN NUMBER
265 , p_boolean_data IN NUMBER
266 , p_text_code IN NUMBER
267 , p_last_update_date IN DATE
268 , p_last_updated_by IN NUMBER
269 , p_last_update_login IN NUMBER
270 )IS
271
272 l_api_name CONSTANT VARCHAR2(30) := 'Update_Item_Technical_Data';
273 l_api_version CONSTANT NUMBER := 1.0;
274 l_data_type NUMBER;
275
276 BEGIN
277
278 -- Standard Start of API savepoint
279 SAVEPOINT Update_Item_Technical_Data ;
280
281 -- Initialize message list if p_init_msg_list is set to TRUE.
282 IF FND_API.to_Boolean( p_init_msg_list )
283 THEN
284 FND_MSG_PUB.initialize;
285 END IF;
286
287 -- Standard call to check for call compatibility.
288 IF NOT FND_API.Compatible_API_Call (l_api_version ,
289 p_api_version ,
290 l_api_name ,
291 G_PKG_NAME
292 )
293 THEN
294 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
295 END IF;
296
297 -- Initialize API return status to success
298 x_return_status := FND_API.G_RET_STS_SUCCESS;
299
300 IF (p_tech_data_id IS NOT NULL AND x_tech_parm_id IS NOT NULL) THEN
301 UPDATE gmd_technical_data_dtl
302 SET SORT_SEQ = p_sort_seq
303 , TEXT_DATA = p_text_data
304 , NUM_DATA = p_num_data
305 , BOOLEAN_DATA = p_boolean_data
306 , TEXT_CODE = p_text_code
307 , LAST_UPDATED_BY = p_last_updated_by
308 , LAST_UPDATE_LOGIN = p_last_update_login
309 , LAST_UPDATE_DATE = p_last_update_date
310 WHERE TECH_DATA_ID = p_tech_data_id
311 AND TECH_PARM_ID = x_tech_parm_id;
312 IF (SQL%ROWCOUNT < 1) THEN
313 RAISE NO_DATA_FOUND;
314 END IF;
315 END IF;
316
317 EXCEPTION
318 WHEN OTHERS THEN
319 ROLLBACK TO Insert_Item_Technical_Data;
320 FND_MSG_PUB.Add_Exc_Msg('GMD_ITEM_TECHNICAL_DATA_PVT', 'UPDATE_ITEM_TECHNICAL_DATA');
321 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
322 fnd_msg_pub.count_and_get (
323 p_count => x_msg_count
324 ,p_encoded => FND_API.g_false
325 ,p_data => x_msg_data);
326
327 END UPDATE_ITEM_TECHNICAL_DATA;
328
329 --Start of comments
330 --+========================================================================+
331 --| API Name : DELETE_ITEM_TECHNICAL_DATA |
332 --| TYPE : Private |
333 --| Function : Deletes the Item technical data |
334 --| Notes : |
335 --| |
336 --| HISTORY |
337 --| S.Sriram 21-Feb-2005 Created |
338 --+========================================================================+
339 -- End of comments
340 PROCEDURE DELETE_ITEM_TECHNICAL_DATA
341 (
342 p_api_version IN NUMBER
343 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
344 , p_commit IN VARCHAR2 := FND_API.G_FALSE
345 , x_return_status OUT NOCOPY VARCHAR2
346 , x_msg_count OUT NOCOPY NUMBER
347 , x_msg_data OUT NOCOPY VARCHAR2
348 , p_tech_data_id IN NUMBER
349 ) IS
350
351
352 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Item_Technical_Data';
353 l_api_version CONSTANT NUMBER := 1.0;
354 l_tech_dtl_cnt NUMBER;
355
356
357 BEGIN
358
359 -- Standard Start of API savepoint
360 SAVEPOINT Delete_Item_Technical_Data ;
361
362
363 -- Initialize message list if p_init_msg_list is set to TRUE.
364 IF FND_API.to_Boolean( p_init_msg_list ) THEN
365 FND_MSG_PUB.initialize;
366 END IF;
367
368
369 -- Standard call to check for call compatibility.
370 IF NOT FND_API.Compatible_API_Call (l_api_version ,
371 p_api_version ,
372 l_api_name ,
373 G_PKG_NAME
374 )
375 THEN
376 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
377 END IF;
378
379
380 -- Initialize API return status to success
381 x_return_status := FND_API.G_RET_STS_SUCCESS;
382
383 IF p_tech_data_id IS NOT NULL THEN
384 -- Delete in OPM is not Physical delete; Set delete mark to 1 (Mark for Purge)
385 UPDATE gmd_technical_data_hdr
386 SET DELETE_MARK = 1
387 ,LAST_UPDATED_BY = FND_GLOBAL.USER_ID
388 ,LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
389 ,LAST_UPDATE_DATE = SYSDATE
390 WHERE TECH_DATA_ID = p_tech_data_id;
391 IF (SQL%ROWCOUNT < 1) THEN
392 RAISE NO_DATA_FOUND;
393 END IF;
394
395 END IF;
396
397 EXCEPTION
398 WHEN OTHERS THEN
399 ROLLBACK TO Delete_Item_Technical_Data;
400 FND_MSG_PUB.Add_Exc_Msg('GMD_ITEM_TECHNICAL_DATA_PVT', 'DELETE_ITEM_TECHNICAL_DATA');
401 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
402 fnd_msg_pub.count_and_get (
403 p_count => x_msg_count
404 ,p_encoded => FND_API.g_false
405 ,p_data => x_msg_data);
406
407 END DELETE_ITEM_TECHNICAL_DATA;
408
409 --Start of comments
410 --+========================================================================+
411 --| API Name : FETCH_ITEM_TECHNICAL_DATA |
412 --| TYPE : Private |
413 --| Function : Fetches the Item technical data based on the input parm's|
414 --| passed. |
415 --| Notes : |
416 --| |
417 --| HISTORY |
418 --| S.Sriram 21-Feb-2005 Created |
419 --+========================================================================+
420 -- End of comments
421
422 PROCEDURE FETCH_ITEM_TECHNICAL_DATA (
423 p_api_version IN NUMBER
424 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
425 , x_msg_count OUT NOCOPY NUMBER
426 , x_msg_data OUT NOCOPY VARCHAR2
427 , p_header_rec IN GMD_ITEM_TECHNICAL_DATA_PUB.technical_data_hdr_rec
428 , x_dtl_tbl OUT NOCOPY GMD_ITEM_TECHNICAL_DATA_PUB.technical_data_dtl_tab
429 , x_return_status OUT NOCOPY VARCHAR2
430 ) IS
431
432
433 l_api_name CONSTANT VARCHAR2(30) := 'Fetch_Item_Technical_Data';
434 l_api_version CONSTANT NUMBER := 1.0;
435
436
437 -- Cursor to fetch the tech_data_id based on the input data
438 CURSOR get_tech_data_id IS
439 SELECT tech_data_id
440 FROM gmd_technical_data_hdr h
441 WHERE h.inventory_item_id = p_header_rec.inventory_item_id
442 AND h.organization_id = p_header_rec.organization_id
443 AND (h.lot_number = p_header_rec.Lot_Number OR p_header_rec.Lot_Number IS NULL)
444 AND (h.lot_organization_id = p_header_rec.lot_organization_id OR p_header_rec.lot_organization_id IS NULL)
445 AND (h.formula_id = p_header_rec.formula_id OR p_header_rec.formula_id IS NULL)
446 AND (h.batch_id = p_header_rec.batch_id OR p_header_rec.batch_id IS NULL)
447 AND h.tech_data_id IS NOT NULL;
448
449
450 -- Cursor to fetch technical data rec's based on tech_data_id
451 CURSOR get_tech_data_rec (l_tech_data_id NUMBER) IS
452 SELECT tech_parm_id, text_code, text_data, num_data, boolean_data, sort_seq
453 FROM gmd_technical_data_dtl
454 WHERE tech_data_id = l_tech_data_id;
455
456 -- Cursor to fetch data type for the technical parameter passed
457 CURSOR get_tech_data_type (l_tech_parm_id NUMBER, l_orgn_id NUMBER) IS
458 SELECT data_type
459 FROM gmd_tech_parameters_b
460 WHERE tech_parm_id = l_tech_parm_id
461 AND (organization_id = l_orgn_id OR organization_id IS NULL);
462
463 l_tech_data_id NUMBER;
464 I NUMBER;
465 l_data_type NUMBER;
466
467 BEGIN
468
469 -- Initialize message list if p_init_msg_list is set to TRUE.
470 IF FND_API.to_Boolean( p_init_msg_list )
471 THEN
472 FND_MSG_PUB.initialize;
473 END IF;
474
475 -- Standard call to check for call compatibility.
476 IF NOT FND_API.Compatible_API_Call (l_api_version ,
477 p_api_version ,
478 l_api_name ,
479 G_PKG_NAME
480 )
481 THEN
482 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
483 END IF;
484
485 -- Initialize API return status to success
486 x_return_status := FND_API.G_RET_STS_SUCCESS;
487
488 -- FETCH IMPLEMENTATION
489 -- Get the tech_data_id based on i/p parameters
490 OPEN get_tech_data_id;
491 FETCH get_tech_data_id INTO l_tech_data_id;
492 CLOSE get_tech_data_id;
493
494 IF l_tech_data_id IS NOT NULL THEN
495 -- Get detail record based on tech_data_id
496 FOR get_rec IN get_tech_data_rec(l_tech_data_id)
497 LOOP
498 I := I + 1;
499 x_dtl_tbl(I).tech_parm_id := get_rec.tech_parm_id;
500 x_dtl_tbl(I).sort_seq := get_rec.sort_seq;
501 x_dtl_tbl(I).text_code := get_rec.text_code;
502
503 -- Get the data type of the tech parameter
504 OPEN get_tech_data_type(get_rec.tech_parm_id, p_header_rec.organization_id);
505 FETCH get_tech_data_type INTO l_data_type;
506 CLOSE get_tech_data_type;
507
508 IF l_data_type IN (1,5,6,7,8,9,10,11) THEN
509 x_dtl_tbl(I).tech_data := get_rec.Num_Data;
510 ELSIF l_data_type IN (0,2, 4) THEN
511 x_dtl_tbl(I).tech_data := get_rec.Text_Data;
512 ELSIF l_data_type = 3 THEN
513 x_dtl_tbl(I).tech_data := get_rec.Boolean_Data;
514 END IF;
515 END LOOP;
516 END IF;
517
518 EXCEPTION
519 WHEN OTHERS THEN
520 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
521 FND_MSG_PUB.Add_Exc_Msg('GMD_ITEM_TECHNICAL_DATA_PVT', 'FETCH_ITEM_TECHNICAL_DATA');
522 fnd_msg_pub.count_and_get (
523 p_count => x_msg_count
524 ,p_encoded => FND_API.g_false
525 ,p_data => x_msg_data);
526
527
528 END FETCH_ITEM_TECHNICAL_DATA;
529
530 END GMD_ITEM_TECHNICAL_DATA_PVT;
531
[Home] [Help]