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