[Home] [Help]
PACKAGE BODY: APPS.GMD_INVENTORY_SPEC_VRS_PVT
Source
1 PACKAGE BODY GMD_INVENTORY_SPEC_VRS_PVT AS
2 /* $Header: GMDVIVRB.pls 120.1 2005/10/04 06:52:11 svankada 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 : GMDVIVRB.pls |
11 --| Package Name : GMD_INVENTORY_SPEC_VRS_PVT |
12 --| Type : Private |
13 --| |
14 --| Notes |
15 --| This package contains private layer APIs for Inventory VR. |
16 --| |
17 --| HISTORY |
18 --| Chetan Nagar 07-Aug-2002 Created. |
19 --| |
20 --|SaiKiran Vankadari 13-Apr-2004 BUG#3464772 |
21 --| Added column AUTO_SAMPLE_IND in the INSERT statement |
22 --| |
23 --|SaiKiran Vankadari 04-May-2004 ENHANCEMENT#3476560 |
24 --| Added column DELAYED_LOT_ENTRY in the INSERT |
25 --| statement. |
26 --|SaiKiran Vankadari 13-Apr-2005 Convergence Changes |
27 --|SaiKiran Vankadari 04-Oct-2005 Added migrated_ind to the insert statement|
28 --+==========================================================================+
29 -- End of comments
30
31 FUNCTION insert_row (
32 p_inventory_spec_vrs IN GMD_INVENTORY_SPEC_VRS%ROWTYPE
33 , x_inventory_spec_vrs OUT NOCOPY GMD_INVENTORY_SPEC_VRS%ROWTYPE
34 )
35 RETURN BOOLEAN IS
36 BEGIN
37
38 x_inventory_spec_vrs := p_inventory_spec_vrs;
39
40 INSERT INTO GMD_INVENTORY_SPEC_VRS
41 (
42 SPEC_VR_ID
43 ,SPEC_ID
44 ,ORGANIZATION_ID
45 ,PARENT_LOT_NUMBER
46 ,LOT_NUMBER
47 ,SUBINVENTORY
48 ,LOCATOR_ID
49 ,SPEC_VR_STATUS
50 ,START_DATE
51 ,END_DATE
52 ,SAMPLING_PLAN_ID
53 ,SAMPLE_INV_TRANS_IND
54 ,CONTROL_LOT_ATTRIB_IND
55 ,LOT_OPTIONAL_ON_SAMPLE
56 ,OUT_OF_SPEC_LOT_STATUS_ID
57 ,IN_SPEC_LOT_STATUS_ID
58 ,CONTROL_BATCH_STEP_IND
59 ,COA_TYPE
60 ,COA_AT_SHIP_IND
61 ,COA_AT_INVOICE_IND
62 ,COA_REQ_FROM_SUPL_IND
63 ,DELETE_MARK
64 ,TEXT_CODE
65 ,ATTRIBUTE_CATEGORY
66 ,ATTRIBUTE1
67 ,ATTRIBUTE2
68 ,ATTRIBUTE3
69 ,ATTRIBUTE4
70 ,ATTRIBUTE5
71 ,ATTRIBUTE6
72 ,ATTRIBUTE7
73 ,ATTRIBUTE8
74 ,ATTRIBUTE9
75 ,ATTRIBUTE10
76 ,ATTRIBUTE11
77 ,ATTRIBUTE12
78 ,ATTRIBUTE13
79 ,ATTRIBUTE14
80 ,ATTRIBUTE15
81 ,ATTRIBUTE16
82 ,ATTRIBUTE17
83 ,ATTRIBUTE18
84 ,ATTRIBUTE19
85 ,ATTRIBUTE20
86 ,ATTRIBUTE21
87 ,ATTRIBUTE22
88 ,ATTRIBUTE23
89 ,ATTRIBUTE24
90 ,ATTRIBUTE25
91 ,ATTRIBUTE26
92 ,ATTRIBUTE27
93 ,ATTRIBUTE28
94 ,ATTRIBUTE29
95 ,ATTRIBUTE30
96 ,CREATION_DATE
97 ,CREATED_BY
98 ,LAST_UPDATED_BY
99 ,LAST_UPDATE_DATE
100 ,LAST_UPDATE_LOGIN
101 ,AUTO_SAMPLE_IND
102 ,DELAYED_LOT_ENTRY
103 ,MIGRATED_IND --To differentiate R12 data from previous data during migration
104 )
105 VALUES
106 (
107 gmd_qc_spec_vr_id_s.NEXTVAL
108 ,x_inventory_spec_vrs.SPEC_ID
109 ,x_inventory_spec_vrs.ORGANIZATION_ID
110 ,x_inventory_spec_vrs.PARENT_LOT_NUMBER
111 ,x_inventory_spec_vrs.LOT_NUMBER
112 ,x_inventory_spec_vrs.SUBINVENTORY
113 ,x_inventory_spec_vrs.LOCATOR_ID
114 ,x_inventory_spec_vrs.SPEC_VR_STATUS
115 ,x_inventory_spec_vrs.START_DATE
116 ,x_inventory_spec_vrs.END_DATE
117 ,x_inventory_spec_vrs.SAMPLING_PLAN_ID
118 ,x_inventory_spec_vrs.SAMPLE_INV_TRANS_IND
119 ,x_inventory_spec_vrs.CONTROL_LOT_ATTRIB_IND
120 ,x_inventory_spec_vrs.LOT_OPTIONAL_ON_SAMPLE
121 ,x_inventory_spec_vrs.OUT_OF_SPEC_LOT_STATUS_ID
122 ,x_inventory_spec_vrs.IN_SPEC_LOT_STATUS_ID
123 ,x_inventory_spec_vrs.CONTROL_BATCH_STEP_IND
124 ,x_inventory_spec_vrs.COA_TYPE
125 ,x_inventory_spec_vrs.COA_AT_SHIP_IND
126 ,x_inventory_spec_vrs.COA_AT_INVOICE_IND
127 ,x_inventory_spec_vrs.COA_REQ_FROM_SUPL_IND
128 ,x_inventory_spec_vrs.DELETE_MARK
129 ,x_inventory_spec_vrs.TEXT_CODE
130 ,x_inventory_spec_vrs.ATTRIBUTE_CATEGORY
131 ,x_inventory_spec_vrs.ATTRIBUTE1
132 ,x_inventory_spec_vrs.ATTRIBUTE2
133 ,x_inventory_spec_vrs.ATTRIBUTE3
134 ,x_inventory_spec_vrs.ATTRIBUTE4
135 ,x_inventory_spec_vrs.ATTRIBUTE5
136 ,x_inventory_spec_vrs.ATTRIBUTE6
137 ,x_inventory_spec_vrs.ATTRIBUTE7
138 ,x_inventory_spec_vrs.ATTRIBUTE8
139 ,x_inventory_spec_vrs.ATTRIBUTE9
140 ,x_inventory_spec_vrs.ATTRIBUTE10
141 ,x_inventory_spec_vrs.ATTRIBUTE11
142 ,x_inventory_spec_vrs.ATTRIBUTE12
143 ,x_inventory_spec_vrs.ATTRIBUTE13
144 ,x_inventory_spec_vrs.ATTRIBUTE14
145 ,x_inventory_spec_vrs.ATTRIBUTE15
146 ,x_inventory_spec_vrs.ATTRIBUTE16
147 ,x_inventory_spec_vrs.ATTRIBUTE17
148 ,x_inventory_spec_vrs.ATTRIBUTE18
149 ,x_inventory_spec_vrs.ATTRIBUTE19
150 ,x_inventory_spec_vrs.ATTRIBUTE20
151 ,x_inventory_spec_vrs.ATTRIBUTE21
152 ,x_inventory_spec_vrs.ATTRIBUTE22
153 ,x_inventory_spec_vrs.ATTRIBUTE23
154 ,x_inventory_spec_vrs.ATTRIBUTE24
155 ,x_inventory_spec_vrs.ATTRIBUTE25
156 ,x_inventory_spec_vrs.ATTRIBUTE26
157 ,x_inventory_spec_vrs.ATTRIBUTE27
158 ,x_inventory_spec_vrs.ATTRIBUTE28
159 ,x_inventory_spec_vrs.ATTRIBUTE29
160 ,x_inventory_spec_vrs.ATTRIBUTE30
161 ,x_inventory_spec_vrs.CREATION_DATE
162 ,x_inventory_spec_vrs.CREATED_BY
163 ,x_inventory_spec_vrs.LAST_UPDATED_BY
164 ,x_inventory_spec_vrs.LAST_UPDATE_DATE
165 ,x_inventory_spec_vrs.LAST_UPDATE_LOGIN
166 ,x_inventory_spec_vrs.AUTO_SAMPLE_IND
167 ,x_inventory_spec_vrs.DELAYED_LOT_ENTRY
168 ,0
169 )
170 RETURNING spec_vr_id INTO x_inventory_spec_vrs.spec_vr_id
171 ;
172
173 IF SQL%FOUND THEN
174 RETURN TRUE;
175 ELSE
176 RETURN FALSE;
177 END IF;
178
179 EXCEPTION
180 WHEN OTHERS THEN
181 fnd_msg_pub.add_exc_msg ('GMD_INVENTORY_SPEC_VRS_PVT', 'INSERT_ROW');
182 RETURN FALSE;
183
184 END insert_row;
185
186
187
188 FUNCTION delete_row (p_spec_vr_id IN NUMBER,
189 p_last_update_date IN DATE ,
190 p_last_updated_by IN NUMBER ,
191 p_last_update_login 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_spec_vr_id IS NOT NULL THEN
201 SELECT 1
202 INTO dummy
203 FROM gmd_inventory_spec_vrs
204 WHERE spec_vr_id = p_spec_vr_id
205 FOR UPDATE NOWAIT;
206
207 UPDATE gmd_inventory_spec_vrs
208 SET delete_mark = 1,
209 last_update_date = NVL(p_last_update_date,SYSDATE),
210 last_updated_by = NVL(p_last_updated_by,FND_GLOBAL.USER_ID),
211 last_update_login = NVL(p_last_update_login,FND_GLOBAL.LOGIN_ID)
212 WHERE spec_vr_id = p_spec_vr_id
213 ;
214 ELSE
215 GMD_API_PUB.log_message('GMD_NO_KEYS','TABLE_NAME', 'GMD_INVENTORY_SPEC_VRS');
216 RETURN FALSE;
217 END IF;
218
219 IF (SQL%FOUND) THEN
220 RETURN TRUE;
221 ELSE
222 GMD_API_PUB.log_message('GMD_NO_DATA_FOUND','TABLE_NAME', 'GMD_INVENTORY_SPEC_VRS');
223 RETURN FALSE;
224 END IF;
225
226 EXCEPTION
227 WHEN locked_by_other_user THEN
228 GMD_API_PUB.log_message('GMD_RECORD_LOCKED',
229 'TABLE_NAME', 'GMD_INVENTORY_SPEC_VRS',
230 'RECORD','Inventory Spec Validity Rule',
231 'KEY', p_spec_vr_id);
232 RETURN FALSE;
233
234 WHEN OTHERS THEN
235 fnd_msg_pub.add_exc_msg ('GMD_INVENTORY_SPEC_VRS_PVT', 'DELETE_ROW');
236 RETURN FALSE;
237
238 END delete_row;
239
240
241
242 FUNCTION lock_row (p_spec_vr_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_spec_vr_id IS NOT NULL THEN
252 SELECT 1
253 INTO dummy
254 FROM gmd_inventory_spec_vrs
255 WHERE spec_vr_id = p_spec_vr_id
256 FOR UPDATE NOWAIT;
257 ELSE
258 GMD_API_PUB.log_message('GMD_NO_KEYS','TABLE_NAME', 'GMD_INVENTORY_SPEC_VRS');
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_INVENTORY_SPEC_VRS',
268 'RECORD','Inventory Spec Validity Rule',
269 'KEY', p_spec_vr_id);
270 RETURN FALSE;
271
272 WHEN OTHERS THEN
273 fnd_msg_pub.add_exc_msg ('GMD_INVENTORY_SPEC_VRS_PVT', 'DELETE_ROW');
274 RETURN FALSE;
275
276 END lock_row;
277
278
279
280 FUNCTION fetch_row (
281 p_inventory_spec_vrs IN gmd_inventory_spec_vrs%ROWTYPE
282 , x_inventory_spec_vrs OUT NOCOPY gmd_inventory_spec_vrs%ROWTYPE
283 )
284 RETURN BOOLEAN
285 IS
286 BEGIN
287
288 IF (p_inventory_spec_vrs.spec_vr_id IS NOT NULL) THEN
289 SELECT *
290 INTO x_inventory_spec_vrs
291 FROM gmd_inventory_spec_vrs
292 WHERE spec_vr_id = p_inventory_spec_vrs.spec_vr_id
293 ;
294 ELSE
295 gmd_api_pub.log_message('GMD_NO_KEYS','TABLE_NAME', 'GMD_INVENTORY_SPEC_VRS');
296 RETURN FALSE;
297 END IF;
298
299 RETURN TRUE;
300
301 EXCEPTION
302 WHEN NO_DATA_FOUND
303 THEN
304 gmd_api_pub.log_message('GMD_NO_DATA_FOUND','TABLE_NAME', 'GMD_INVENTORY_SPEC_VRS');
305 RETURN FALSE;
306 WHEN OTHERS
307 THEN
308 fnd_msg_pub.add_exc_msg ('GMD_INVENTORY_SPEC_VRS_PVT', 'FETCH_ROW');
309 RETURN FALSE;
310
311 END fetch_row;
312
313 END GMD_INVENTORY_SPEC_VRS_PVT;