[Home] [Help]
PACKAGE BODY: APPS.GMD_SAMPLING_EVENTS_PVT
Source
1 PACKAGE BODY GMD_SAMPLING_EVENTS_PVT AS
2 /* $Header: GMDVSEVB.pls 120.4 2009/03/18 15:55:19 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 : GMDVSEVB.pls |
11 --| Package Name : GMD_SAMPLING_EVENTS_PVT |
12 --| Type : Private |
13 --| |
14 --| Notes |
15 --| This package contains private layer APIs for Sampling Events |
16 --| |
17 --| HISTORY |
18 --| Chetan Nagar 06-Aug-2002 Created. |
19 --| S. Feinstein 18-OCT-2005 Added material detail id to samples |
20 --| RAGSRIVA 23-Jun-2006 set migrated_ind to 0 in insert_row |
21 --| RLNAGARA LPN ME 7027149 09-May-2008 Added LPN_ID to the INSERT statement|
22 --+==========================================================================+
23 -- End of comments
24
25
26 FUNCTION insert_row (
27 p_sampling_events IN GMD_SAMPLING_EVENTS%ROWTYPE
28 , x_sampling_events OUT NOCOPY GMD_SAMPLING_EVENTS%ROWTYPE) RETURN BOOLEAN IS
29 BEGIN
30
31 x_sampling_events := p_sampling_events;
32
33 INSERT INTO GMD_SAMPLING_EVENTS
34 (
35 SAMPLING_EVENT_ID
36 ,ORIGINAL_SPEC_VR_ID
37 ,ORGANIZATION_ID
38 ,DISPOSITION
39 ,SAMPLE_REQ_CNT
40 ,SAMPLE_TAKEN_CNT
41 ,ARCHIVED_TAKEN
42 ,RESERVED_TAKEN
43 ,SAMPLING_PLAN_ID
44 ,EVENT_TYPE_CODE
45 ,EVENT_ID
46 ,SAMPLE_TYPE
47 ,SOURCE
48 ,INVENTORY_ITEM_ID
49 ,REVISION
50 ,PARENT_LOT_NUMBER
51 ,LOT_NUMBER
52 ,SUBINVENTORY
53 ,LOCATOR_ID
54 ,BATCH_ID
55 ,RECIPE_ID
56 ,FORMULA_ID
57 ,FORMULALINE_ID
58 ,MATERIAL_DETAIL_ID
59 ,ROUTING_ID
60 ,STEP_ID
61 ,STEP_NO
62 ,OPRN_ID
63 ,CHARGE
64 ,CUST_ID
65 ,ORDER_ID
66 ,ORDER_LINE_ID
67 ,SHIP_TO_SITE_ID
68 ,ORG_ID
69 ,SUPPLIER_ID
70 ,SUPPLIER_SITE_ID
71 ,PO_LINE_ID
72 ,RECEIPT_LINE_ID
73 ,SUPPLIER_LOT_NO
74 ,RESOURCES
75 ,INSTANCE_ID
76 ,VARIANT_ID
77 ,TIME_POINT_ID
78 ,COMPLETE_IND
79 ,SAMPLE_ID_TO_EVALUATE
80 ,COMPOSITE_ID_TO_EVALUATE
81 ,TEXT_CODE
82 ,CREATION_DATE
83 ,CREATED_BY
84 ,LAST_UPDATED_BY
85 ,LAST_UPDATE_DATE
86 ,LAST_UPDATE_LOGIN
87 ,PO_HEADER_ID
88 ,RECEIPT_ID
89 ,LOT_RETEST_IND
90 ,SAMPLE_ACTIVE_CNT
91 ,MIGRATED_IND
92 ,LPN_ID
93 )
94 VALUES
95 (
96 gmd_qc_sampling_event_id_s.NEXTVAL
97 ,x_sampling_events.ORIGINAL_SPEC_VR_ID
98 ,x_sampling_events.ORGANIZATION_ID
99 ,x_sampling_events.DISPOSITION
100 ,x_sampling_events.SAMPLE_REQ_CNT
101 ,x_sampling_events.SAMPLE_TAKEN_CNT
102 ,x_sampling_events.ARCHIVED_TAKEN
103 ,x_sampling_events.RESERVED_TAKEN
104 ,x_sampling_events.SAMPLING_PLAN_ID
105 ,x_sampling_events.EVENT_TYPE_CODE
106 ,x_sampling_events.EVENT_ID
107 ,x_sampling_events.SAMPLE_TYPE
108 ,x_sampling_events.SOURCE
109 ,x_sampling_events.INVENTORY_ITEM_ID
110 ,x_sampling_events.REVISION
111 ,x_sampling_events.PARENT_LOT_NUMBER
112 ,x_sampling_events.LOT_NUMBER
113 ,x_sampling_events.SUBINVENTORY
114 ,x_sampling_events.LOCATOR_ID
115 ,x_sampling_events.BATCH_ID
116 ,x_sampling_events.RECIPE_ID
117 ,x_sampling_events.FORMULA_ID
118 ,x_sampling_events.FORMULALINE_ID
119 ,x_sampling_events.MATERIAL_DETAIL_ID
120 ,x_sampling_events.ROUTING_ID
121 ,x_sampling_events.STEP_ID
122 ,x_sampling_events.STEP_NO
123 ,x_sampling_events.OPRN_ID
124 ,x_sampling_events.CHARGE
125 ,x_sampling_events.CUST_ID
126 ,x_sampling_events.ORDER_ID
127 ,x_sampling_events.ORDER_LINE_ID
128 ,x_sampling_events.SHIP_TO_SITE_ID
129 ,x_sampling_events.ORG_ID
130 ,x_sampling_events.SUPPLIER_ID
131 ,x_sampling_events.SUPPLIER_SITE_ID
132 ,x_sampling_events.PO_LINE_ID
133 ,x_sampling_events.RECEIPT_LINE_ID
134 ,x_sampling_events.SUPPLIER_LOT_NO
135 ,x_sampling_events.RESOURCES
136 ,x_sampling_events.INSTANCE_ID
137 ,x_sampling_events.VARIANT_ID
138 ,x_sampling_events.TIME_POINT_ID
139 ,x_sampling_events.COMPLETE_IND
140 ,x_sampling_events.SAMPLE_ID_TO_EVALUATE
141 ,x_sampling_events.COMPOSITE_ID_TO_EVALUATE
142 ,x_sampling_events.TEXT_CODE
143 ,x_sampling_events.CREATION_DATE
144 ,x_sampling_events.CREATED_BY
145 ,x_sampling_events.LAST_UPDATED_BY
146 ,x_sampling_events.LAST_UPDATE_DATE
147 ,x_sampling_events.LAST_UPDATE_LOGIN
148 ,x_sampling_events.PO_HEADER_ID
149 ,x_sampling_events.RECEIPT_ID
150 ,x_sampling_events.LOT_RETEST_IND
151 ,x_sampling_events.SAMPLE_ACTIVE_CNT
152 ,0
153 ,x_sampling_events.LPN_ID
154 )
155 RETURNING sampling_event_id INTO x_sampling_events.sampling_event_id
156 ;
157
158 IF SQL%FOUND THEN
159 RETURN TRUE;
160 ELSE
161 RETURN FALSE;
162 END IF;
163
164 EXCEPTION
165 WHEN OTHERS THEN
166 fnd_msg_pub.add_exc_msg ('GMD_SAMPLING_EVENTS_PVT', 'INSERT_ROW');
167 RETURN FALSE;
168 END insert_row;
169
170
171
172
173
174 FUNCTION delete_row (p_sampling_event_id IN NUMBER)
175 RETURN BOOLEAN IS
176
177 dummy PLS_INTEGER;
178
179 locked_by_other_user EXCEPTION;
180 PRAGMA EXCEPTION_INIT (locked_by_other_user,-54);
181
182 BEGIN
183 IF p_sampling_event_id IS NOT NULL THEN
184 SELECT 1
185 INTO dummy
186 FROM gmd_sampling_events
187 WHERE sampling_event_id = p_sampling_event_id
188 FOR UPDATE NOWAIT;
189
190 DELETE gmd_sampling_events
191 WHERE sampling_event_id = p_sampling_event_id
192 ;
193 ELSE
194 GMD_API_PUB.log_message('GMD_NO_KEYS','TABLE_NAME', 'GMD_SAMPLING_EVENTS');
195 RETURN FALSE;
196 END IF;
197
198 IF (SQL%FOUND) THEN
199 RETURN TRUE;
200 ELSE
201 GMD_API_PUB.log_message('GMD_NO_DATA_FOUND','TABLE_NAME', 'GMD_SAMPLING_EVENTS');
202 RETURN FALSE;
203 END IF;
204
205 EXCEPTION
206 WHEN locked_by_other_user THEN
207 GMD_API_PUB.log_message('GMD_RECORD_LOCKED',
208 'TABLE_NAME', 'GMD_SAMPLING_EVENTS',
209 'RECORD','Sampling Event',
210 'KEY', p_sampling_event_id);
211 RETURN FALSE;
212
213 WHEN OTHERS THEN
214 fnd_msg_pub.add_exc_msg ('GMD_SAMPLING_EVENTS_PVT', 'DELETE_ROW');
215 RETURN FALSE;
216
217 END delete_row;
218
219
220
221
222 FUNCTION lock_row (p_sampling_event_id IN NUMBER)
223 RETURN BOOLEAN IS
224
225 dummy PLS_INTEGER;
226
227 locked_by_other_user EXCEPTION;
228 PRAGMA EXCEPTION_INIT (locked_by_other_user,-54);
229
230 BEGIN
231 IF p_sampling_event_id IS NOT NULL THEN
232 SELECT 1
233 INTO dummy
234 FROM gmd_sampling_events
235 WHERE sampling_event_id = p_sampling_event_id
236 FOR UPDATE NOWAIT;
237 ELSE
238 GMD_API_PUB.log_message('GMD_NO_KEYS','TABLE_NAME', 'GMD_SAMPLING_EVENTS');
239 RETURN FALSE;
240 END IF;
241
242 RETURN TRUE;
243
244 EXCEPTION
245 WHEN locked_by_other_user THEN
246 GMD_API_PUB.log_message('GMD_RECORD_LOCKED',
247 'TABLE_NAME', 'GMD_SAMPLING_EVENTS',
248 'RECORD','Sampling Event',
249 'KEY', p_sampling_event_id);
250 RETURN FALSE;
251
252 WHEN OTHERS THEN
253 fnd_msg_pub.add_exc_msg ('GMD_SAMPLING_EVENTS_PVT', 'DELETE_ROW');
254 RETURN FALSE;
255
256 END lock_row;
257
258
259
260 FUNCTION fetch_row (
261 p_sampling_events IN gmd_sampling_events%ROWTYPE
262 , x_sampling_events OUT NOCOPY gmd_sampling_events%ROWTYPE
263 )
264 RETURN BOOLEAN
265 IS
266 BEGIN
267
268 IF (p_sampling_events.sampling_event_id IS NOT NULL) THEN
269 SELECT *
270 INTO x_sampling_events
271 FROM gmd_sampling_events
272 WHERE sampling_event_id = p_sampling_events.sampling_event_id
273 ;
274 RETURN TRUE;
275 ELSE
276 gmd_api_pub.log_message('GMD_NO_KEYS','TABLE_NAME', 'GMD_SAMPLING_EVENTS');
277 RETURN FALSE;
278 END IF;
279
280 EXCEPTION
281 WHEN NO_DATA_FOUND
282 THEN
283 gmd_api_pub.log_message('GMD_NO_DATA_FOUND','TABLE_NAME', 'GMD_SAMPLING_EVENTS');
284 RETURN FALSE;
285 WHEN OTHERS
286 THEN
287 fnd_msg_pub.add_exc_msg ('GMD_SAMPLING_EVENTS_PVT', 'FETCH_ROW');
288 RETURN FALSE;
289
290 END fetch_row;
291
292 END GMD_SAMPLING_EVENTS_PVT;
293