[Home] [Help]
PACKAGE BODY: APPS.GMD_SAMPLES_PVT
Source
1 PACKAGE BODY GMD_SAMPLES_PVT AS
2 /* $Header: GMDVSMPB.pls 120.2.12010000.2 2009/03/18 15:59:12 rnalla ship $ */
3
4 -- Start of comments
5 --+==========================================================================+
6 --| Copyright (c) 1998 Oracle Corporation |
7 --| Redwood Shores, CA, USA |
8 --| All rights reserved. |
9 --+==========================================================================+
10 --| File Name : GMDVSMPB.pls |
11 --| Package Name : GMD_SAMPLES_PVT |
12 --| Type : Private |
13 --| |
14 --| Notes |
15 --| This package contains private layer APIs for Samples |
16 --| |
17 --| HISTORY |
18 --| Chetan Nagar 07-Aug-2002 Created. |
19 --| magupta 06-Jan-2003 2733495: Added source_wshe and locat-|
20 --| ion for insert_row. |
21 --| B. Stone 19-May-2003 2967055: Added sample instance |
22 --| |
23 --| S. Feinstein 30-Jan-2004 3401377: added instance_id, resources|
24 --| retrieval_date,date_received|
25 --| date_required. |
26 --| changed source_comment to |
27 --| comment. |
28 --| |
29 --| S. Feinstein 12-FEB-2004 3401377: added remaining_qty, retain_as|
30 --| |
31 --| Sai Kiran 10-May-2004 3576573: added PO_HEADER_ID, |
32 --| PO_LINE_ID,RECEIPT_ID |
33 --| RECEIPT_LINE_ID,SUPPLIER_LOT_NO|
34 --| to the 'insert_row' procedure |
35 --| S. Feinstein 11-MAR-2005 4165704: Inventory Convergence fields entered
36 --| S. Feinstein 18-OCT-2005 4640143: added material detail id to samples
37 --| |
38 --| P Lowe 14-JUN-2006 5283854: added storage_organization_id
39 --| to samples |
40 --| PLOWE 19-May-2008 7027149 added support for LPN |
41 --+==========================================================================+
42 -- End of comments
43
44
45 FUNCTION insert_row (
46 p_samples IN GMD_SAMPLES%ROWTYPE
47 , x_samples OUT NOCOPY GMD_SAMPLES%ROWTYPE) RETURN BOOLEAN IS
48 BEGIN
49
50 x_samples := p_samples;
51
52 INSERT INTO GMD_SAMPLES
53 (
54 SAMPLE_ID
55 ,SAMPLE_NO
56 ,SAMPLE_DESC
57 ,LAB_ORGANIZATION_ID
58 ,SAMPLE_DISPOSITION
59 ,RETAIN_AS
60 ,INVENTORY_ITEM_ID
61 ,ORGANIZATION_ID
62 ,SUBINVENTORY
63 ,LOCATOR_ID
64 ,EXPIRATION_DATE
65 ,PARENT_LOT_NUMBER
66 ,LOT_NUMBER
67 ,REVISION
68 ,BATCH_ID
69 ,RECIPE_ID
70 ,FORMULA_ID
71 ,FORMULALINE_ID
72 ,MATERIAL_DETAIL_ID
73 ,ROUTING_ID
74 ,OPRN_ID
75 ,CHARGE
76 ,CUST_ID
77 ,ORDER_ID
78 ,ORDER_LINE_ID
79 ,SHIP_TO_SITE_ID
80 ,ORG_ID
81 ,SUPPLIER_ID
82 ,SUPPLIER_SITE_ID
83 ,SAMPLE_QTY
84 ,SAMPLE_QTY_UOM
85 ,REMAINING_QTY
86 ,SOURCE
87 ,SAMPLE_INSTANCE
88 ,SAMPLER_ID
89 ,DATE_DRAWN
90 ,SOURCE_COMMENT
91 ,STORAGE_SUBINVENTORY
92 ,STORAGE_LOCATOR_ID
93 ,STORAGE_ORGANIZATION_ID -- 5283854
94 ,EXTERNAL_ID
95 ,SAMPLE_APPROVER_ID
96 ,INV_APPROVER_ID
97 ,PRIORITY
98 ,SAMPLE_INV_TRANS_IND
99 ,DELETE_MARK
100 ,TEXT_CODE
101 ,ATTRIBUTE_CATEGORY
102 ,ATTRIBUTE1
103 ,ATTRIBUTE2
104 ,ATTRIBUTE3
105 ,ATTRIBUTE4
106 ,ATTRIBUTE5
107 ,ATTRIBUTE6
108 ,ATTRIBUTE7
109 ,ATTRIBUTE8
110 ,ATTRIBUTE9
111 ,ATTRIBUTE10
112 ,ATTRIBUTE11
113 ,ATTRIBUTE12
114 ,ATTRIBUTE13
115 ,ATTRIBUTE14
116 ,ATTRIBUTE15
117 ,ATTRIBUTE16
118 ,ATTRIBUTE17
119 ,ATTRIBUTE18
120 ,ATTRIBUTE19
121 ,ATTRIBUTE20
122 ,ATTRIBUTE21
123 ,ATTRIBUTE22
124 ,ATTRIBUTE23
125 ,ATTRIBUTE24
126 ,ATTRIBUTE25
127 ,ATTRIBUTE26
128 ,ATTRIBUTE27
129 ,ATTRIBUTE28
130 ,ATTRIBUTE29
131 ,ATTRIBUTE30
132 ,CREATION_DATE
133 ,CREATED_BY
134 ,LAST_UPDATED_BY
135 ,LAST_UPDATE_DATE
136 ,LAST_UPDATE_LOGIN
137 ,STEP_ID
138 ,STEP_NO
139 ,SAMPLING_EVENT_ID
140 ,LOT_RETEST_IND
141 ,SOURCE_SUBINVENTORY
142 ,SOURCE_LOCATOR_ID
143 ,SAMPLE_TYPE
144 ,VARIANT_ID
145 ,TIME_POINT_ID
146 ,INSTANCE_ID
147 ,RESOURCES
148 ,RETRIEVAL_DATE
149 ,DATE_RECEIVED
150 ,DATE_REQUIRED
151 ,PO_HEADER_ID
152 ,PO_LINE_ID
153 ,RECEIPT_ID
154 ,RECEIPT_LINE_ID
155 ,SUPPLIER_LOT_NO
156 ,LPN_ID -- 7027149
157 )
158 VALUES
159 (
160 gmd_qc_sample_id_s.NEXTVAL
161 ,x_samples.SAMPLE_NO
162 ,x_samples.SAMPLE_DESC
163 ,x_samples.LAB_ORGANIZATION_ID
164 ,x_samples.SAMPLE_DISPOSITION
165 ,x_samples.RETAIN_AS
166 ,x_samples.INVENTORY_ITEM_ID
167 ,x_samples.ORGANIZATION_ID
168 ,x_samples.SUBINVENTORY
169 ,x_samples.LOCATOR_ID
170 ,x_samples.EXPIRATION_DATE
171 ,x_samples.PARENT_LOT_NUMBER
172 ,x_samples.LOT_NUMBER
173 ,x_samples.REVISION
174 ,x_samples.BATCH_ID
175 ,x_samples.RECIPE_ID
176 ,x_samples.FORMULA_ID
177 ,x_samples.FORMULALINE_ID
178 ,x_samples.MATERIAL_DETAIL_ID
179 ,x_samples.ROUTING_ID
180 ,x_samples.OPRN_ID
181 ,x_samples.CHARGE
182 ,x_samples.CUST_ID
183 ,x_samples.ORDER_ID
184 ,x_samples.ORDER_LINE_ID
185 ,x_samples.SHIP_TO_SITE_ID
186 ,x_samples.ORG_ID
187 ,x_samples.SUPPLIER_ID
188 ,x_samples.SUPPLIER_SITE_ID
189 ,x_samples.SAMPLE_QTY
190 ,x_samples.SAMPLE_QTY_UOM
191 ,x_samples.REMAINING_QTY
192 ,x_samples.SOURCE
193 ,x_samples.SAMPLE_INSTANCE
194 ,x_samples.SAMPLER_ID
195 ,x_samples.DATE_DRAWN
196 ,x_samples.SOURCE_COMMENT
197 ,x_samples.STORAGE_SUBINVENTORY
198 ,x_samples.STORAGE_LOCATOR_ID
199 ,x_samples.STORAGE_ORGANIZATION_ID -- 5283854
200 ,x_samples.EXTERNAL_ID
201 ,x_samples.SAMPLE_APPROVER_ID
202 ,x_samples.INV_APPROVER_ID
203 ,x_samples.PRIORITY
204 ,x_samples.SAMPLE_INV_TRANS_IND
205 ,x_samples.DELETE_MARK
206 ,x_samples.TEXT_CODE
207 ,x_samples.ATTRIBUTE_CATEGORY
208 ,x_samples.ATTRIBUTE1
209 ,x_samples.ATTRIBUTE2
210 ,x_samples.ATTRIBUTE3
211 ,x_samples.ATTRIBUTE4
212 ,x_samples.ATTRIBUTE5
213 ,x_samples.ATTRIBUTE6
214 ,x_samples.ATTRIBUTE7
215 ,x_samples.ATTRIBUTE8
216 ,x_samples.ATTRIBUTE9
217 ,x_samples.ATTRIBUTE10
218 ,x_samples.ATTRIBUTE11
219 ,x_samples.ATTRIBUTE12
220 ,x_samples.ATTRIBUTE13
221 ,x_samples.ATTRIBUTE14
222 ,x_samples.ATTRIBUTE15
223 ,x_samples.ATTRIBUTE16
224 ,x_samples.ATTRIBUTE17
225 ,x_samples.ATTRIBUTE18
226 ,x_samples.ATTRIBUTE19
227 ,x_samples.ATTRIBUTE20
228 ,x_samples.ATTRIBUTE21
229 ,x_samples.ATTRIBUTE22
230 ,x_samples.ATTRIBUTE23
231 ,x_samples.ATTRIBUTE24
232 ,x_samples.ATTRIBUTE25
233 ,x_samples.ATTRIBUTE26
234 ,x_samples.ATTRIBUTE27
235 ,x_samples.ATTRIBUTE28
236 ,x_samples.ATTRIBUTE29
237 ,x_samples.ATTRIBUTE30
238 ,x_samples.CREATION_DATE
239 ,x_samples.CREATED_BY
240 ,x_samples.LAST_UPDATED_BY
241 ,x_samples.LAST_UPDATE_DATE
242 ,x_samples.LAST_UPDATE_LOGIN
243 ,x_samples.STEP_ID
244 ,x_samples.STEP_NO
245 ,x_samples.SAMPLING_EVENT_ID
246 ,x_samples.LOT_RETEST_IND
247 ,x_samples.SOURCE_SUBINVENTORY
248 ,x_samples.SOURCE_LOCATOR_ID
249 ,x_samples.SAMPLE_TYPE
250 ,x_samples.VARIANT_ID
251 ,x_samples.TIME_POINT_ID
252 ,x_samples.INSTANCE_ID
253 ,x_samples.RESOURCES
254 ,x_samples.RETRIEVAL_DATE
255 ,x_samples.DATE_RECEIVED
256 ,x_samples.DATE_REQUIRED
257 ,x_samples.PO_HEADER_ID
258 ,x_samples.PO_LINE_ID
259 ,x_samples.RECEIPT_ID
260 ,x_samples.RECEIPT_LINE_ID
261 ,x_samples.SUPPLIER_LOT_NO
262 ,x_samples.LPN_ID -- 7027149
263 )
264 RETURNING sample_id INTO x_samples.sample_id
265 ;
266
267 IF SQL%FOUND THEN
268 RETURN TRUE;
269 ELSE
270 RETURN FALSE;
271 END IF;
272
273 EXCEPTION
274 WHEN OTHERS THEN
275 fnd_msg_pub.add_exc_msg ('GMD_SAMPLES_PVT', 'INSERT_ROW');
276 RETURN FALSE;
277
278 END insert_row;
279
280
281
282
283
284 FUNCTION delete_row (
285 p_sample_id IN NUMBER
286 , p_organization_id IN VARCHAR2
287 , p_sample_no IN VARCHAR2
288 ) RETURN BOOLEAN IS
289
290 dummy PLS_INTEGER;
291
292 locked_by_other_user EXCEPTION;
293 PRAGMA EXCEPTION_INIT (locked_by_other_user,-54);
294
295 BEGIN
296
297 IF p_sample_id IS NOT NULL THEN
298 SELECT 1
299 INTO dummy
300 FROM gmd_samples
301 WHERE sample_id = p_sample_id
302 FOR UPDATE NOWAIT;
303
304 UPDATE gmd_samples
305 SET delete_mark = 1,
306 last_updated_by = fnd_global.user_id,
307 last_update_date = SYSDATE
308 WHERE sample_id = p_sample_id
309 ;
310 ELSIF p_organization_id IS NOT NULL AND
311 p_sample_no IS NOT NULL THEN
312 SELECT 1
313 INTO dummy
314 FROM gmd_samples
315 WHERE organization_id = p_organization_id
316 AND sample_no = p_sample_no
317 FOR UPDATE NOWAIT;
318
319 UPDATE gmd_samples
320 SET delete_mark = 1,
321 last_updated_by = fnd_global.user_id,
322 last_update_date = SYSDATE
323 WHERE organization_id = p_organization_id
324 AND sample_no = p_sample_no
325 ;
326 ELSE
327 GMD_API_PUB.log_message('GMD_NO_KEYS','TABLE_NAME', 'GMD_SAMPLES');
328 RETURN FALSE;
329 END IF;
330
331 IF (SQL%FOUND) THEN
332 RETURN TRUE;
333 ELSE
334 GMD_API_PUB.log_message('GMD_NO_DATA_FOUND','TABLE_NAME', 'GMD_SAMPLES');
335 RETURN FALSE;
336 END IF;
337
338 EXCEPTION
339 WHEN locked_by_other_user THEN
340 GMD_API_PUB.log_message('GMD_RECORD_LOCKED',
341 'TABLE_NAME', 'GMD_SAMPLES',
342 'RECORD','Sample',
343 'KEY', p_organization_id || p_sample_no);
344 RETURN FALSE;
345
346 WHEN OTHERS THEN
347 fnd_msg_pub.add_exc_msg ('GMD_SAMPLES_PVT', 'DELETE_ROW');
348 RETURN FALSE;
349
350 END delete_row;
351
352
353
354
355 FUNCTION lock_row (
356 p_sample_id IN NUMBER
357 , p_organization_id IN VARCHAR2
358 , p_sample_no IN VARCHAR2 )
359 RETURN BOOLEAN IS
360
361 dummy PLS_INTEGER;
362
363 locked_by_other_user EXCEPTION;
364 PRAGMA EXCEPTION_INIT (locked_by_other_user,-54);
365
366 BEGIN
367 IF p_sample_id IS NOT NULL THEN
368 SELECT 1
369 INTO dummy
370 FROM gmd_samples
371 WHERE sample_id = p_sample_id
372 FOR UPDATE NOWAIT;
373 ELSIF p_organization_id IS NOT NULL AND
374 p_sample_no IS NOT NULL THEN
375 SELECT 1
376 INTO dummy
377 FROM gmd_samples
378 WHERE organization_id = p_organization_id
379 AND sample_no = p_sample_no
380 FOR UPDATE NOWAIT;
381 ELSE
382 GMD_API_PUB.log_message('GMD_NO_KEYS','TABLE_NAME', 'GMD_SAMPLES');
383 RETURN FALSE;
384 END IF;
385
386 RETURN TRUE;
387
388 EXCEPTION
389 WHEN locked_by_other_user THEN
390 GMD_API_PUB.log_message('GMD_RECORD_LOCKED',
391 'TABLE_NAME', 'GMD_SAMPLES',
392 'RECORD','Sampling Event',
393 'KEY', p_organization_id || p_sample_no);
394 RETURN FALSE;
395
396 WHEN OTHERS THEN
397 fnd_msg_pub.add_exc_msg ('GMD_SAMPLES_PVT', 'DELETE_ROW');
398 RETURN FALSE;
399
400 END lock_row;
401
402
403
404 FUNCTION fetch_row (
405 p_samples IN gmd_samples%ROWTYPE
406 , x_samples OUT NOCOPY gmd_samples%ROWTYPE
407 )
408 RETURN BOOLEAN
409 IS
410 BEGIN
411
412 IF (p_samples.sample_id IS NOT NULL) THEN
413 SELECT *
414 INTO x_samples
415 FROM gmd_samples
416 WHERE sample_id = p_samples.sample_id
417 ;
418 RETURN TRUE;
419
420 ELSIF (p_samples.organization_id IS NOT NULL AND
421 p_samples.sample_no IS NOT NULL) THEN
422 SELECT *
423 INTO x_samples
424 FROM gmd_samples
425 WHERE organization_id = p_samples.organization_id
426 AND sample_no = p_samples.sample_no
427 ;
428 RETURN TRUE;
429 ELSE
430 gmd_api_pub.log_message('GMD_NO_KEYS','TABLE_NAME', 'GMD_SAMPLES');
431 RETURN FALSE;
432 END IF;
433
434 RETURN FALSE;
435
436 EXCEPTION
437 WHEN NO_DATA_FOUND
438 THEN
439 gmd_api_pub.log_message('GMD_NO_DATA_FOUND','TABLE_NAME', 'GMD_SAMPLES');
440 RETURN FALSE;
441 WHEN OTHERS
442 THEN
443 fnd_msg_pub.add_exc_msg ('GMD_SAMPLES_PVT', 'FETCH_ROW');
444 RETURN FALSE;
445
446 END fetch_row;
447
448 END GMD_SAMPLES_PVT;