eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.GMD_SAMPLES_PVT

Source


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