[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.12010000.2 2009/03/18 21:29:13 plowe 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 : 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 --|RLNAGARA LPN ME 7027149 08-May-2008 Added Delayed_LPN_Entry in the INSERT |
29 --+==========================================================================+
30 -- End of comments
31
32 FUNCTION insert_row (
33 p_inventory_spec_vrs IN GMD_INVENTORY_SPEC_VRS%ROWTYPE
34 , x_inventory_spec_vrs OUT NOCOPY GMD_INVENTORY_SPEC_VRS%ROWTYPE
35 )
36 RETURN BOOLEAN IS
37 BEGIN
38
39 x_inventory_spec_vrs := p_inventory_spec_vrs;
40
41 INSERT INTO GMD_INVENTORY_SPEC_VRS
42 (
43 SPEC_VR_ID
44 ,SPEC_ID
45 ,ORGANIZATION_ID
46 ,PARENT_LOT_NUMBER
47 ,LOT_NUMBER
48 ,SUBINVENTORY
49 ,LOCATOR_ID
50 ,SPEC_VR_STATUS
51 ,START_DATE
52 ,END_DATE
53 ,SAMPLING_PLAN_ID
54 ,SAMPLE_INV_TRANS_IND
55 ,CONTROL_LOT_ATTRIB_IND
56 ,LOT_OPTIONAL_ON_SAMPLE
57 ,OUT_OF_SPEC_LOT_STATUS_ID
58 ,IN_SPEC_LOT_STATUS_ID
59 ,CONTROL_BATCH_STEP_IND
60 ,COA_TYPE
61 ,COA_AT_SHIP_IND
62 ,COA_AT_INVOICE_IND
63 ,COA_REQ_FROM_SUPL_IND
64 ,DELETE_MARK
65 ,TEXT_CODE
66 ,ATTRIBUTE_CATEGORY
67 ,ATTRIBUTE1
68 ,ATTRIBUTE2
69 ,ATTRIBUTE3
70 ,ATTRIBUTE4
71 ,ATTRIBUTE5
72 ,ATTRIBUTE6
73 ,ATTRIBUTE7
74 ,ATTRIBUTE8
75 ,ATTRIBUTE9
76 ,ATTRIBUTE10
77 ,ATTRIBUTE11
78 ,ATTRIBUTE12
79 ,ATTRIBUTE13
80 ,ATTRIBUTE14
81 ,ATTRIBUTE15
82 ,ATTRIBUTE16
83 ,ATTRIBUTE17
84 ,ATTRIBUTE18
85 ,ATTRIBUTE19
86 ,ATTRIBUTE20
87 ,ATTRIBUTE21
88 ,ATTRIBUTE22
89 ,ATTRIBUTE23
90 ,ATTRIBUTE24
91 ,ATTRIBUTE25
92 ,ATTRIBUTE26
93 ,ATTRIBUTE27
94 ,ATTRIBUTE28
95 ,ATTRIBUTE29
96 ,ATTRIBUTE30
97 ,CREATION_DATE
98 ,CREATED_BY
99 ,LAST_UPDATED_BY
100 ,LAST_UPDATE_DATE
101 ,LAST_UPDATE_LOGIN
102 ,AUTO_SAMPLE_IND
103 ,DELAYED_LOT_ENTRY
104 ,MIGRATED_IND --To differentiate R12 data from previous data during migration
105 ,DELAYED_LPN_ENTRY --RLNAGARA LPN ME 7027149
106 )
107 VALUES
108 (
109 gmd_qc_spec_vr_id_s.NEXTVAL
110 ,x_inventory_spec_vrs.SPEC_ID
111 ,x_inventory_spec_vrs.ORGANIZATION_ID
112 ,x_inventory_spec_vrs.PARENT_LOT_NUMBER
113 ,x_inventory_spec_vrs.LOT_NUMBER
114 ,x_inventory_spec_vrs.SUBINVENTORY
115 ,x_inventory_spec_vrs.LOCATOR_ID
116 ,x_inventory_spec_vrs.SPEC_VR_STATUS
117 ,x_inventory_spec_vrs.START_DATE
118 ,x_inventory_spec_vrs.END_DATE
119 ,x_inventory_spec_vrs.SAMPLING_PLAN_ID
120 ,x_inventory_spec_vrs.SAMPLE_INV_TRANS_IND
121 ,x_inventory_spec_vrs.CONTROL_LOT_ATTRIB_IND
122 ,x_inventory_spec_vrs.LOT_OPTIONAL_ON_SAMPLE
123 ,x_inventory_spec_vrs.OUT_OF_SPEC_LOT_STATUS_ID
124 ,x_inventory_spec_vrs.IN_SPEC_LOT_STATUS_ID
125 ,x_inventory_spec_vrs.CONTROL_BATCH_STEP_IND
126 ,x_inventory_spec_vrs.COA_TYPE
127 ,x_inventory_spec_vrs.COA_AT_SHIP_IND
128 ,x_inventory_spec_vrs.COA_AT_INVOICE_IND
129 ,x_inventory_spec_vrs.COA_REQ_FROM_SUPL_IND
130 ,x_inventory_spec_vrs.DELETE_MARK
131 ,x_inventory_spec_vrs.TEXT_CODE
132 ,x_inventory_spec_vrs.ATTRIBUTE_CATEGORY
133 ,x_inventory_spec_vrs.ATTRIBUTE1
134 ,x_inventory_spec_vrs.ATTRIBUTE2
135 ,x_inventory_spec_vrs.ATTRIBUTE3
136 ,x_inventory_spec_vrs.ATTRIBUTE4
137 ,x_inventory_spec_vrs.ATTRIBUTE5
138 ,x_inventory_spec_vrs.ATTRIBUTE6
139 ,x_inventory_spec_vrs.ATTRIBUTE7
140 ,x_inventory_spec_vrs.ATTRIBUTE8
141 ,x_inventory_spec_vrs.ATTRIBUTE9
142 ,x_inventory_spec_vrs.ATTRIBUTE10
143 ,x_inventory_spec_vrs.ATTRIBUTE11
144 ,x_inventory_spec_vrs.ATTRIBUTE12
145 ,x_inventory_spec_vrs.ATTRIBUTE13
146 ,x_inventory_spec_vrs.ATTRIBUTE14
147 ,x_inventory_spec_vrs.ATTRIBUTE15
148 ,x_inventory_spec_vrs.ATTRIBUTE16
149 ,x_inventory_spec_vrs.ATTRIBUTE17
150 ,x_inventory_spec_vrs.ATTRIBUTE18
151 ,x_inventory_spec_vrs.ATTRIBUTE19
152 ,x_inventory_spec_vrs.ATTRIBUTE20
153 ,x_inventory_spec_vrs.ATTRIBUTE21
154 ,x_inventory_spec_vrs.ATTRIBUTE22
155 ,x_inventory_spec_vrs.ATTRIBUTE23
156 ,x_inventory_spec_vrs.ATTRIBUTE24
157 ,x_inventory_spec_vrs.ATTRIBUTE25
158 ,x_inventory_spec_vrs.ATTRIBUTE26
159 ,x_inventory_spec_vrs.ATTRIBUTE27
160 ,x_inventory_spec_vrs.ATTRIBUTE28
161 ,x_inventory_spec_vrs.ATTRIBUTE29
162 ,x_inventory_spec_vrs.ATTRIBUTE30
163 ,x_inventory_spec_vrs.CREATION_DATE
164 ,x_inventory_spec_vrs.CREATED_BY
165 ,x_inventory_spec_vrs.LAST_UPDATED_BY
166 ,x_inventory_spec_vrs.LAST_UPDATE_DATE
167 ,x_inventory_spec_vrs.LAST_UPDATE_LOGIN
168 ,x_inventory_spec_vrs.AUTO_SAMPLE_IND
169 ,x_inventory_spec_vrs.DELAYED_LOT_ENTRY
170 ,0
171 ,x_inventory_spec_vrs.DELAYED_LPN_ENTRY --RLNAGARA LPN ME 7027149
172 )
173 RETURNING spec_vr_id INTO x_inventory_spec_vrs.spec_vr_id
174 ;
175
176 IF SQL%FOUND THEN
177 RETURN TRUE;
178 ELSE
179 RETURN FALSE;
180 END IF;
181
182 EXCEPTION
183 WHEN OTHERS THEN
184 fnd_msg_pub.add_exc_msg ('GMD_INVENTORY_SPEC_VRS_PVT', 'INSERT_ROW');
185 RETURN FALSE;
186
187 END insert_row;
188
189
190
191 FUNCTION delete_row (p_spec_vr_id IN NUMBER,
192 p_last_update_date IN DATE ,
193 p_last_updated_by IN NUMBER ,
194 p_last_update_login IN NUMBER )
195 RETURN BOOLEAN IS
196
197 dummy PLS_INTEGER;
198
199 locked_by_other_user EXCEPTION;
200 PRAGMA EXCEPTION_INIT (locked_by_other_user,-54);
201
202 BEGIN
203 IF p_spec_vr_id IS NOT NULL THEN
204 SELECT 1
205 INTO dummy
206 FROM gmd_inventory_spec_vrs
207 WHERE spec_vr_id = p_spec_vr_id
208 FOR UPDATE NOWAIT;
209
210 UPDATE gmd_inventory_spec_vrs
211 SET delete_mark = 1,
212 last_update_date = NVL(p_last_update_date,SYSDATE),
213 last_updated_by = NVL(p_last_updated_by,FND_GLOBAL.USER_ID),
214 last_update_login = NVL(p_last_update_login,FND_GLOBAL.LOGIN_ID)
215 WHERE spec_vr_id = p_spec_vr_id
216 ;
217 ELSE
218 GMD_API_PUB.log_message('GMD_NO_KEYS','TABLE_NAME', 'GMD_INVENTORY_SPEC_VRS');
219 RETURN FALSE;
220 END IF;
221
222 IF (SQL%FOUND) THEN
223 RETURN TRUE;
224 ELSE
225 GMD_API_PUB.log_message('GMD_NO_DATA_FOUND','TABLE_NAME', 'GMD_INVENTORY_SPEC_VRS');
226 RETURN FALSE;
227 END IF;
228
229 EXCEPTION
230 WHEN locked_by_other_user THEN
231 GMD_API_PUB.log_message('GMD_RECORD_LOCKED',
232 'TABLE_NAME', 'GMD_INVENTORY_SPEC_VRS',
233 'RECORD','Inventory Spec Validity Rule',
234 'KEY', p_spec_vr_id);
235 RETURN FALSE;
236
237 WHEN OTHERS THEN
238 fnd_msg_pub.add_exc_msg ('GMD_INVENTORY_SPEC_VRS_PVT', 'DELETE_ROW');
239 RETURN FALSE;
240
241 END delete_row;
242
243
244
245 FUNCTION lock_row (p_spec_vr_id IN NUMBER)
246 RETURN BOOLEAN IS
247
248 dummy PLS_INTEGER;
249
250 locked_by_other_user EXCEPTION;
251 PRAGMA EXCEPTION_INIT (locked_by_other_user,-54);
252
253 BEGIN
254 IF p_spec_vr_id IS NOT NULL THEN
255 SELECT 1
256 INTO dummy
257 FROM gmd_inventory_spec_vrs
258 WHERE spec_vr_id = p_spec_vr_id
259 FOR UPDATE NOWAIT;
260 ELSE
261 GMD_API_PUB.log_message('GMD_NO_KEYS','TABLE_NAME', 'GMD_INVENTORY_SPEC_VRS');
262 RETURN FALSE;
263 END IF;
264
265 RETURN TRUE;
266
267 EXCEPTION
268 WHEN locked_by_other_user THEN
269 GMD_API_PUB.log_message('GMD_RECORD_LOCKED',
270 'TABLE_NAME', 'GMD_INVENTORY_SPEC_VRS',
271 'RECORD','Inventory Spec Validity Rule',
272 'KEY', p_spec_vr_id);
273 RETURN FALSE;
274
275 WHEN OTHERS THEN
276 fnd_msg_pub.add_exc_msg ('GMD_INVENTORY_SPEC_VRS_PVT', 'DELETE_ROW');
277 RETURN FALSE;
278
279 END lock_row;
280
281
282
283 FUNCTION fetch_row (
284 p_inventory_spec_vrs IN gmd_inventory_spec_vrs%ROWTYPE
285 , x_inventory_spec_vrs OUT NOCOPY gmd_inventory_spec_vrs%ROWTYPE
286 )
287 RETURN BOOLEAN
288 IS
289 BEGIN
290
291 IF (p_inventory_spec_vrs.spec_vr_id IS NOT NULL) THEN
292 SELECT *
293 INTO x_inventory_spec_vrs
294 FROM gmd_inventory_spec_vrs
295 WHERE spec_vr_id = p_inventory_spec_vrs.spec_vr_id
296 ;
297 ELSE
298 gmd_api_pub.log_message('GMD_NO_KEYS','TABLE_NAME', 'GMD_INVENTORY_SPEC_VRS');
299 RETURN FALSE;
300 END IF;
301
302 RETURN TRUE;
303
304 EXCEPTION
305 WHEN NO_DATA_FOUND
306 THEN
307 gmd_api_pub.log_message('GMD_NO_DATA_FOUND','TABLE_NAME', 'GMD_INVENTORY_SPEC_VRS');
308 RETURN FALSE;
309 WHEN OTHERS
310 THEN
311 fnd_msg_pub.add_exc_msg ('GMD_INVENTORY_SPEC_VRS_PVT', 'FETCH_ROW');
312 RETURN FALSE;
313
314 END fetch_row;
315
316 END GMD_INVENTORY_SPEC_VRS_PVT;