[Home] [Help]
PACKAGE BODY: APPS.GMD_RESULTS_PVT
Source
1 PACKAGE BODY GMD_RESULTS_PVT AS
2 /* $Header: GMDVRESB.pls 120.1 2006/06/26 12:57:12 ragsriva 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 : GMDVRESB.pls |
11 --| Package Name : GMD_RESULTS_PVT |
12 --| Type : Private |
13 --| |
14 --| Notes |
15 --| This package contains private layer APIs for Results |
16 --| |
17 --| HISTORY |
18 --| Chetan Nagar 09-Aug-2002 Created. |
19 --| Ravi Boddu 17-Mar-2005 Results Convergence Changes done |
20 --| RAGSRIVA 23-Jun-2006 set migrated_ind to 0 in insert_row |
21 --| |
22 --+==========================================================================+
23 -- End of comments
24
25
26 FUNCTION insert_row (
27 p_results IN GMD_RESULTS%ROWTYPE
28 , x_results OUT NOCOPY GMD_RESULTS%ROWTYPE) RETURN BOOLEAN IS
29 BEGIN
30
31 x_results := p_results;
32
33 INSERT INTO GMD_RESULTS
34 (
35 RESULT_ID
36 ,SAMPLE_ID
37 ,TEST_ID
38 ,TEST_QTY
39 ,TEST_QTY_UOM
40 ,TEST_METHOD_ID
41 ,CONSUMED_QTY
42 ,RESERVE_SAMPLE_ID
43 ,TEST_REPLICATE_CNT
44 ,LAB_ORGANIZATION_ID
45 ,RESULT_VALUE_NUM
46 ,RESULT_VALUE_CHAR
47 ,RESULT_DATE
48 ,TEST_KIT_INV_ITEM_ID
49 ,TEST_KIT_LOT_NUMBER
50 ,TESTER_ID
51 ,TEST_PROVIDER_ID
52 ,ASSAY_RETEST
53 ,AD_HOC_PRINT_ON_COA_IND
54 ,SEQ
55 ,DELETE_MARK
56 ,TEXT_CODE
57 ,ATTRIBUTE_CATEGORY
58 ,ATTRIBUTE1
59 ,ATTRIBUTE2
60 ,ATTRIBUTE3
61 ,ATTRIBUTE4
62 ,ATTRIBUTE5
63 ,ATTRIBUTE6
64 ,ATTRIBUTE7
65 ,ATTRIBUTE8
66 ,ATTRIBUTE9
67 ,ATTRIBUTE10
68 ,ATTRIBUTE11
69 ,ATTRIBUTE12
70 ,ATTRIBUTE13
71 ,ATTRIBUTE14
72 ,ATTRIBUTE15
73 ,ATTRIBUTE16
74 ,ATTRIBUTE17
75 ,ATTRIBUTE18
76 ,ATTRIBUTE19
77 ,ATTRIBUTE20
78 ,ATTRIBUTE21
79 ,ATTRIBUTE22
80 ,ATTRIBUTE23
81 ,ATTRIBUTE24
82 ,ATTRIBUTE25
83 ,ATTRIBUTE26
84 ,ATTRIBUTE27
85 ,ATTRIBUTE28
86 ,ATTRIBUTE29
87 ,ATTRIBUTE30
88 ,CREATION_DATE
89 ,CREATED_BY
90 ,LAST_UPDATED_BY
91 ,LAST_UPDATE_DATE
92 ,LAST_UPDATE_LOGIN
93 ,UPDATE_INSTANCE_ID
94 ,PLANNED_RESOURCE
95 ,PLANNED_RESOURCE_INSTANCE
96 ,ACTUAL_RESOURCE
97 ,ACTUAL_RESOURCE_INSTANCE
98 ,PLANNED_RESULT_DATE
99 ,TEST_BY_DATE
100 ,MIGRATED_IND
101 )
102 VALUES
103 (
104 gmd_qc_result_id_s.NEXTVAL
105 ,x_results.SAMPLE_ID
106 ,x_results.TEST_ID
107 ,x_results.TEST_QTY
108 ,x_results.TEST_QTY_UOM
109 ,x_results.TEST_METHOD_ID
110 ,x_results.CONSUMED_QTY
111 ,x_results.RESERVE_SAMPLE_ID
112 ,x_results.TEST_REPLICATE_CNT
113 ,x_results.LAB_ORGANIZATION_ID
114 ,x_results.RESULT_VALUE_NUM
115 ,x_results.RESULT_VALUE_CHAR
116 ,x_results.RESULT_DATE
117 ,x_results.TEST_KIT_INV_ITEM_ID
118 ,x_results.TEST_KIT_LOT_NUMBER
119 ,x_results.TESTER_ID
120 ,x_results.TEST_PROVIDER_ID
121 ,x_results.ASSAY_RETEST
122 ,x_results.AD_HOC_PRINT_ON_COA_IND
123 ,x_results.SEQ
124 ,x_results.DELETE_MARK
125 ,x_results.TEXT_CODE
126 ,x_results.ATTRIBUTE_CATEGORY
127 ,x_results.ATTRIBUTE1
128 ,x_results.ATTRIBUTE2
129 ,x_results.ATTRIBUTE3
130 ,x_results.ATTRIBUTE4
131 ,x_results.ATTRIBUTE5
132 ,x_results.ATTRIBUTE6
133 ,x_results.ATTRIBUTE7
134 ,x_results.ATTRIBUTE8
135 ,x_results.ATTRIBUTE9
136 ,x_results.ATTRIBUTE10
137 ,x_results.ATTRIBUTE11
138 ,x_results.ATTRIBUTE12
139 ,x_results.ATTRIBUTE13
140 ,x_results.ATTRIBUTE14
141 ,x_results.ATTRIBUTE15
142 ,x_results.ATTRIBUTE16
143 ,x_results.ATTRIBUTE17
144 ,x_results.ATTRIBUTE18
145 ,x_results.ATTRIBUTE19
146 ,x_results.ATTRIBUTE20
147 ,x_results.ATTRIBUTE21
148 ,x_results.ATTRIBUTE22
149 ,x_results.ATTRIBUTE23
150 ,x_results.ATTRIBUTE24
151 ,x_results.ATTRIBUTE25
152 ,x_results.ATTRIBUTE26
153 ,x_results.ATTRIBUTE27
154 ,x_results.ATTRIBUTE28
155 ,x_results.ATTRIBUTE29
156 ,x_results.ATTRIBUTE30
157 ,x_results.CREATION_DATE
158 ,x_results.CREATED_BY
159 ,x_results.LAST_UPDATED_BY
160 ,x_results.LAST_UPDATE_DATE
161 ,x_results.LAST_UPDATE_LOGIN
162 ,x_results.UPDATE_INSTANCE_ID
163 ,x_results.PLANNED_RESOURCE
164 ,x_results.PLANNED_RESOURCE_INSTANCE
165 ,x_results.ACTUAL_RESOURCE
166 ,x_results.ACTUAL_RESOURCE_INSTANCE
167 ,x_results.PLANNED_RESULT_DATE
168 ,x_results.TEST_BY_DATE
169 ,0
170 )
171 RETURNING result_id INTO x_results.result_id
172 ;
173
174 IF SQL%FOUND THEN
175 RETURN TRUE;
176 ELSE
177 RETURN FALSE;
178 END IF;
179
180 EXCEPTION
181 WHEN OTHERS THEN
182 fnd_msg_pub.add_exc_msg ('GMD_RESULTS_PVT', 'INSERT_ROW');
183 RETURN FALSE;
184
185 END insert_row;
186
187
188
189
190
191 FUNCTION delete_row (p_result_id IN NUMBER)
192 RETURN BOOLEAN IS
193
194 dummy PLS_INTEGER;
195
196 locked_by_other_user EXCEPTION;
197 PRAGMA EXCEPTION_INIT (locked_by_other_user,-54);
198
199 BEGIN
200 IF p_result_id IS NOT NULL THEN
201 SELECT 1
202 INTO dummy
203 FROM gmd_results
204 WHERE result_id = p_result_id
205 FOR UPDATE NOWAIT;
206
207 UPDATE gmd_results
208 SET delete_mark = 1,
209 last_updated_by = fnd_global.user_id,
210 last_update_date = SYSDATE
211 WHERE result_id = p_result_id
212 ;
213 ELSE
214 GMD_API_PUB.log_message('GMD_NO_KEYS','TABLE_NAME', 'GMD_RESULTS');
215 RETURN FALSE;
216 END IF;
217
218 IF (SQL%FOUND) THEN
219 RETURN TRUE;
220 ELSE
221 GMD_API_PUB.log_message('GMD_NO_DATA_FOUND','TABLE_NAME', 'GMD_RESULTS');
222 RETURN FALSE;
223 END IF;
224
225 EXCEPTION
226 WHEN locked_by_other_user THEN
227 GMD_API_PUB.log_message('GMD_RECORD_LOCKED',
228 'TABLE_NAME', 'GMD_RESULTS',
229 'RECORD','Result',
230 'KEY', p_result_id);
231 RETURN FALSE;
232
233 WHEN OTHERS THEN
234 fnd_msg_pub.add_exc_msg ('GMD_RESULTS_PVT', 'DELETE_ROW');
235 RETURN FALSE;
236
237 END delete_row;
238
239
240
241
242 FUNCTION lock_row (p_result_id IN NUMBER)
243 RETURN BOOLEAN IS
244
245 dummy PLS_INTEGER;
246
247 locked_by_other_user EXCEPTION;
248 PRAGMA EXCEPTION_INIT (locked_by_other_user,-54);
249
250 BEGIN
251 IF p_result_id IS NOT NULL THEN
252 SELECT 1
253 INTO dummy
254 FROM gmd_results
255 WHERE result_id = p_result_id
256 FOR UPDATE NOWAIT;
257 ELSE
258 GMD_API_PUB.log_message('GMD_NO_KEYS','TABLE_NAME', 'GMD_RESULTS');
259 RETURN FALSE;
260 END IF;
261
262 RETURN TRUE;
263
264 EXCEPTION
265 WHEN locked_by_other_user THEN
266 GMD_API_PUB.log_message('GMD_RECORD_LOCKED',
267 'TABLE_NAME', 'GMD_RESULTS',
268 'RECORD','Result',
269 'KEY', p_result_id);
270 RETURN FALSE;
271
272 WHEN OTHERS THEN
273 fnd_msg_pub.add_exc_msg ('GMD_RESULTS_PVT', 'LOCK_ROW');
274 RETURN FALSE;
275
276 END lock_row;
277
278
279
280 FUNCTION fetch_row (
281 p_results IN gmd_results%ROWTYPE
282 , x_results OUT NOCOPY gmd_results%ROWTYPE
283 )
284 RETURN BOOLEAN
285 IS
286 BEGIN
287
288 IF (p_results.result_id IS NOT NULL) THEN
289 SELECT *
290 INTO x_results
291 FROM gmd_results
292 WHERE result_id = p_results.result_id;
293
294 ELSIF (p_results.test_id IS NOT NULL AND
295 p_results.sample_id IS NOT NULL AND
296 p_results.test_replicate_cnt IS NOT NULL ) THEN
297
298 -- ADDED FOR BUG 2696353
299 -- TEST_ID, SAMPLE_ID and test_replicate_cnt
300 -- Should be the unique FK's to find a result record.
301
302 SELECT *
303 INTO x_results
304 FROM gmd_results
305 WHERE test_id = p_results.test_id
306 AND sample_id = p_results.sample_id
307 AND test_replicate_cnt = p_results.test_replicate_cnt;
308
309 ELSE
310 gmd_api_pub.log_message('GMD_NO_KEYS','TABLE_NAME', 'GMD_RESULTS');
311 RETURN FALSE;
312 END IF;
313
314 RETURN TRUE;
315
316 EXCEPTION
317 WHEN NO_DATA_FOUND
318 THEN
319 gmd_api_pub.log_message('GMD_NO_DATA_FOUND','TABLE_NAME', 'GMD_RESULTS');
320 RETURN FALSE;
321 WHEN OTHERS
322 THEN
323 fnd_msg_pub.add_exc_msg ('GMD_RESULTS_PVT', 'FETCH_ROW');
324 RETURN FALSE;
325
326 END fetch_row;
327
328 END GMD_RESULTS_PVT;