[Home] [Help]
PACKAGE BODY: APPS.GMD_SAMPLING_EVENTS_PVT
Source
1 PACKAGE BODY GMD_SAMPLING_EVENTS_PVT AS
2 /* $Header: GMDVSEVB.pls 120.3 2006/06/26 12:55:06 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 : 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 --| |
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 )
93 VALUES
94 (
95 gmd_qc_sampling_event_id_s.NEXTVAL
96 ,x_sampling_events.ORIGINAL_SPEC_VR_ID
97 ,x_sampling_events.ORGANIZATION_ID
98 ,x_sampling_events.DISPOSITION
99 ,x_sampling_events.SAMPLE_REQ_CNT
100 ,x_sampling_events.SAMPLE_TAKEN_CNT
101 ,x_sampling_events.ARCHIVED_TAKEN
102 ,x_sampling_events.RESERVED_TAKEN
103 ,x_sampling_events.SAMPLING_PLAN_ID
104 ,x_sampling_events.EVENT_TYPE_CODE
105 ,x_sampling_events.EVENT_ID
106 ,x_sampling_events.SAMPLE_TYPE
107 ,x_sampling_events.SOURCE
108 ,x_sampling_events.INVENTORY_ITEM_ID
109 ,x_sampling_events.REVISION
110 ,x_sampling_events.PARENT_LOT_NUMBER
111 ,x_sampling_events.LOT_NUMBER
112 ,x_sampling_events.SUBINVENTORY
113 ,x_sampling_events.LOCATOR_ID
114 ,x_sampling_events.BATCH_ID
115 ,x_sampling_events.RECIPE_ID
116 ,x_sampling_events.FORMULA_ID
117 ,x_sampling_events.FORMULALINE_ID
118 ,x_sampling_events.MATERIAL_DETAIL_ID
119 ,x_sampling_events.ROUTING_ID
120 ,x_sampling_events.STEP_ID
121 ,x_sampling_events.STEP_NO
122 ,x_sampling_events.OPRN_ID
123 ,x_sampling_events.CHARGE
124 ,x_sampling_events.CUST_ID
125 ,x_sampling_events.ORDER_ID
126 ,x_sampling_events.ORDER_LINE_ID
127 ,x_sampling_events.SHIP_TO_SITE_ID
128 ,x_sampling_events.ORG_ID
129 ,x_sampling_events.SUPPLIER_ID
130 ,x_sampling_events.SUPPLIER_SITE_ID
131 ,x_sampling_events.PO_LINE_ID
132 ,x_sampling_events.RECEIPT_LINE_ID
133 ,x_sampling_events.SUPPLIER_LOT_NO
134 ,x_sampling_events.RESOURCES
135 ,x_sampling_events.INSTANCE_ID
136 ,x_sampling_events.VARIANT_ID
137 ,x_sampling_events.TIME_POINT_ID
138 ,x_sampling_events.COMPLETE_IND
139 ,x_sampling_events.SAMPLE_ID_TO_EVALUATE
140 ,x_sampling_events.COMPOSITE_ID_TO_EVALUATE
141 ,x_sampling_events.TEXT_CODE
142 ,x_sampling_events.CREATION_DATE
143 ,x_sampling_events.CREATED_BY
144 ,x_sampling_events.LAST_UPDATED_BY
145 ,x_sampling_events.LAST_UPDATE_DATE
146 ,x_sampling_events.LAST_UPDATE_LOGIN
147 ,x_sampling_events.PO_HEADER_ID
148 ,x_sampling_events.RECEIPT_ID
149 ,x_sampling_events.LOT_RETEST_IND
150 ,x_sampling_events.SAMPLE_ACTIVE_CNT
151 ,0
152 )
153 RETURNING sampling_event_id INTO x_sampling_events.sampling_event_id
154 ;
155
156 IF SQL%FOUND THEN
157 RETURN TRUE;
158 ELSE
159 RETURN FALSE;
160 END IF;
161
162 EXCEPTION
163 WHEN OTHERS THEN
164 fnd_msg_pub.add_exc_msg ('GMD_SAMPLING_EVENTS_PVT', 'INSERT_ROW');
165 RETURN FALSE;
166 END insert_row;
167
168
169
170
171
172 FUNCTION delete_row (p_sampling_event_id IN NUMBER)
173 RETURN BOOLEAN IS
174
175 dummy PLS_INTEGER;
176
177 locked_by_other_user EXCEPTION;
178 PRAGMA EXCEPTION_INIT (locked_by_other_user,-54);
179
180 BEGIN
181 IF p_sampling_event_id IS NOT NULL THEN
182 SELECT 1
183 INTO dummy
184 FROM gmd_sampling_events
185 WHERE sampling_event_id = p_sampling_event_id
186 FOR UPDATE NOWAIT;
187
188 DELETE gmd_sampling_events
189 WHERE sampling_event_id = p_sampling_event_id
190 ;
191 ELSE
192 GMD_API_PUB.log_message('GMD_NO_KEYS','TABLE_NAME', 'GMD_SAMPLING_EVENTS');
193 RETURN FALSE;
194 END IF;
195
196 IF (SQL%FOUND) THEN
197 RETURN TRUE;
198 ELSE
199 GMD_API_PUB.log_message('GMD_NO_DATA_FOUND','TABLE_NAME', 'GMD_SAMPLING_EVENTS');
200 RETURN FALSE;
201 END IF;
202
203 EXCEPTION
204 WHEN locked_by_other_user THEN
205 GMD_API_PUB.log_message('GMD_RECORD_LOCKED',
206 'TABLE_NAME', 'GMD_SAMPLING_EVENTS',
207 'RECORD','Sampling Event',
208 'KEY', p_sampling_event_id);
209 RETURN FALSE;
210
211 WHEN OTHERS THEN
212 fnd_msg_pub.add_exc_msg ('GMD_SAMPLING_EVENTS_PVT', 'DELETE_ROW');
213 RETURN FALSE;
214
215 END delete_row;
216
217
218
219
220 FUNCTION lock_row (p_sampling_event_id IN NUMBER)
221 RETURN BOOLEAN IS
222
223 dummy PLS_INTEGER;
224
225 locked_by_other_user EXCEPTION;
226 PRAGMA EXCEPTION_INIT (locked_by_other_user,-54);
227
228 BEGIN
229 IF p_sampling_event_id IS NOT NULL THEN
230 SELECT 1
231 INTO dummy
232 FROM gmd_sampling_events
233 WHERE sampling_event_id = p_sampling_event_id
234 FOR UPDATE NOWAIT;
235 ELSE
236 GMD_API_PUB.log_message('GMD_NO_KEYS','TABLE_NAME', 'GMD_SAMPLING_EVENTS');
237 RETURN FALSE;
238 END IF;
239
240 RETURN TRUE;
241
242 EXCEPTION
243 WHEN locked_by_other_user THEN
244 GMD_API_PUB.log_message('GMD_RECORD_LOCKED',
245 'TABLE_NAME', 'GMD_SAMPLING_EVENTS',
246 'RECORD','Sampling Event',
247 'KEY', p_sampling_event_id);
248 RETURN FALSE;
249
250 WHEN OTHERS THEN
251 fnd_msg_pub.add_exc_msg ('GMD_SAMPLING_EVENTS_PVT', 'DELETE_ROW');
252 RETURN FALSE;
253
254 END lock_row;
255
256
257
258 FUNCTION fetch_row (
259 p_sampling_events IN gmd_sampling_events%ROWTYPE
260 , x_sampling_events OUT NOCOPY gmd_sampling_events%ROWTYPE
261 )
262 RETURN BOOLEAN
263 IS
264 BEGIN
265
266 IF (p_sampling_events.sampling_event_id IS NOT NULL) THEN
267 SELECT *
268 INTO x_sampling_events
269 FROM gmd_sampling_events
270 WHERE sampling_event_id = p_sampling_events.sampling_event_id
271 ;
272 RETURN TRUE;
273 ELSE
274 gmd_api_pub.log_message('GMD_NO_KEYS','TABLE_NAME', 'GMD_SAMPLING_EVENTS');
275 RETURN FALSE;
276 END IF;
277
278 EXCEPTION
279 WHEN NO_DATA_FOUND
280 THEN
281 gmd_api_pub.log_message('GMD_NO_DATA_FOUND','TABLE_NAME', 'GMD_SAMPLING_EVENTS');
282 RETURN FALSE;
283 WHEN OTHERS
284 THEN
285 fnd_msg_pub.add_exc_msg ('GMD_SAMPLING_EVENTS_PVT', 'FETCH_ROW');
286 RETURN FALSE;
287
288 END fetch_row;
289
290 END GMD_SAMPLING_EVENTS_PVT;
291