[Home] [Help]
PACKAGE BODY: APPS.GMD_SUPPLIER_SPEC_VRS_PVT
Source
1 PACKAGE BODY GMD_SUPPLIER_SPEC_VRS_PVT AS
2 /* $Header: GMDVSVRB.pls 120.1.12010000.2 2009/03/18 16:11:43 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 : GMDVSVRB.pls |
11 --| Package Name : GMD_SUPPLIER_SPEC_VRS_PVT |
12 --| Type : Private |
13 --| |
14 --| Notes |
15 --| This package contains private layer APIs for Supplier VR. |
16 --| |
17 --| HISTORY |
18 --| Chetan Nagar 07-Aug-2002 Created. |
19 --| |
20 --| Vipul Vaish 20-Feb-2004 BUG#3440311 |
21 --| Added columns AUTO_SAMPLE_IND,CONTROL_LOT_ATTRIB_IND,|
22 --| IN_SPEC_LOT_STATUS,OUT_OF_SPEC_LOT_STATUS in the |
23 --| INSERT statement. |
24 --| |
25 --|SaiKiran Vankadari 04-May-2004 ENHANCEMENT#3476560 |
26 --| Added column DELAYED_LOT_ENTRY in the INSERT |
27 --| statement. |
28 --|SaiKiran Vankadari 13-Apr-2005 Convergence Changes |
29 --| Added org_id,organization_id, out_of_spec_lot_status_id,|
30 --| and in_spec_lot_status_id columns to insert_row() procedure|
31 --|SaiKiran Vankadari 04-Oct-2005 Added migrated_ind to the insert statement|
32 --|RLNAGARA LPN ME 7027149 08-May-2008 Added Delayed_LPN_Entry in the INSERT |
33 --+==========================================================================+
34 -- End of comments
35
36 FUNCTION insert_row (
37 p_supplier_spec_vrs IN GMD_SUPPLIER_SPEC_VRS%ROWTYPE
38 , x_supplier_spec_vrs OUT NOCOPY GMD_SUPPLIER_SPEC_VRS%ROWTYPE
39 )
40 RETURN BOOLEAN IS
41 BEGIN
42
43 x_supplier_spec_vrs := p_supplier_spec_vrs;
44
45 INSERT INTO GMD_SUPPLIER_SPEC_VRS
46 (
47 SPEC_VR_ID
48 ,SPEC_ID
49 ,ORGANIZATION_ID
50 ,ORG_ID
51 ,SUPPLIER_ID
52 ,SUPPLIER_SITE_ID
53 ,PO_LINE_ID
54 ,SPEC_VR_STATUS
55 ,START_DATE
56 ,END_DATE
57 ,SAMPLING_PLAN_ID
58 ,SAMPLE_INV_TRANS_IND
59 ,LOT_OPTIONAL_ON_SAMPLE
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 ,PO_HEADER_ID
103 ,AUTO_SAMPLE_IND
104 ,CONTROL_LOT_ATTRIB_IND
105 ,IN_SPEC_LOT_STATUS_ID
106 ,OUT_OF_SPEC_LOT_STATUS_ID
107 ,DELAYED_LOT_ENTRY
108 ,MIGRATED_IND --To differentiate R12 data from previous data during migration
109 ,DELAYED_LPN_ENTRY --RLNAGARA LPN ME 7027149
110 )
111 VALUES
112 (
113 gmd_qc_spec_vr_id_s.NEXTVAL
114 ,x_supplier_spec_vrs.SPEC_ID
115 ,x_supplier_spec_vrs.ORGANIZATION_ID
116 ,x_supplier_spec_vrs.ORG_ID
117 ,x_supplier_spec_vrs.SUPPLIER_ID
118 ,x_supplier_spec_vrs.SUPPLIER_SITE_ID
119 ,x_supplier_spec_vrs.PO_LINE_ID
120 ,x_supplier_spec_vrs.SPEC_VR_STATUS
121 ,x_supplier_spec_vrs.START_DATE
122 ,x_supplier_spec_vrs.END_DATE
123 ,x_supplier_spec_vrs.SAMPLING_PLAN_ID
124 ,x_supplier_spec_vrs.SAMPLE_INV_TRANS_IND
125 ,x_supplier_spec_vrs.LOT_OPTIONAL_ON_SAMPLE
126 ,x_supplier_spec_vrs.COA_TYPE
127 ,x_supplier_spec_vrs.COA_AT_SHIP_IND
128 ,x_supplier_spec_vrs.COA_AT_INVOICE_IND
129 ,x_supplier_spec_vrs.COA_REQ_FROM_SUPL_IND
130 ,x_supplier_spec_vrs.DELETE_MARK
131 ,x_supplier_spec_vrs.TEXT_CODE
132 ,x_supplier_spec_vrs.ATTRIBUTE_CATEGORY
133 ,x_supplier_spec_vrs.ATTRIBUTE1
134 ,x_supplier_spec_vrs.ATTRIBUTE2
135 ,x_supplier_spec_vrs.ATTRIBUTE3
136 ,x_supplier_spec_vrs.ATTRIBUTE4
137 ,x_supplier_spec_vrs.ATTRIBUTE5
138 ,x_supplier_spec_vrs.ATTRIBUTE6
139 ,x_supplier_spec_vrs.ATTRIBUTE7
140 ,x_supplier_spec_vrs.ATTRIBUTE8
141 ,x_supplier_spec_vrs.ATTRIBUTE9
142 ,x_supplier_spec_vrs.ATTRIBUTE10
143 ,x_supplier_spec_vrs.ATTRIBUTE11
144 ,x_supplier_spec_vrs.ATTRIBUTE12
145 ,x_supplier_spec_vrs.ATTRIBUTE13
146 ,x_supplier_spec_vrs.ATTRIBUTE14
147 ,x_supplier_spec_vrs.ATTRIBUTE15
148 ,x_supplier_spec_vrs.ATTRIBUTE16
149 ,x_supplier_spec_vrs.ATTRIBUTE17
150 ,x_supplier_spec_vrs.ATTRIBUTE18
151 ,x_supplier_spec_vrs.ATTRIBUTE19
152 ,x_supplier_spec_vrs.ATTRIBUTE20
153 ,x_supplier_spec_vrs.ATTRIBUTE21
154 ,x_supplier_spec_vrs.ATTRIBUTE22
155 ,x_supplier_spec_vrs.ATTRIBUTE23
156 ,x_supplier_spec_vrs.ATTRIBUTE24
157 ,x_supplier_spec_vrs.ATTRIBUTE25
158 ,x_supplier_spec_vrs.ATTRIBUTE26
159 ,x_supplier_spec_vrs.ATTRIBUTE27
160 ,x_supplier_spec_vrs.ATTRIBUTE28
161 ,x_supplier_spec_vrs.ATTRIBUTE29
162 ,x_supplier_spec_vrs.ATTRIBUTE30
163 ,x_supplier_spec_vrs.CREATION_DATE
164 ,x_supplier_spec_vrs.CREATED_BY
165 ,x_supplier_spec_vrs.LAST_UPDATED_BY
166 ,x_supplier_spec_vrs.LAST_UPDATE_DATE
167 ,x_supplier_spec_vrs.LAST_UPDATE_LOGIN
168 ,x_supplier_spec_vrs.PO_HEADER_ID
169 ,x_supplier_spec_vrs.AUTO_SAMPLE_IND
170 ,x_supplier_spec_vrs.CONTROL_LOT_ATTRIB_IND
171 ,x_supplier_spec_vrs.IN_SPEC_LOT_STATUS_ID
172 ,x_supplier_spec_vrs.OUT_OF_SPEC_LOT_STATUS_ID
173 ,x_supplier_spec_vrs.DELAYED_LOT_ENTRY
174 ,0
175 ,x_supplier_spec_vrs.DELAYED_LPN_ENTRY --RLNAGARA LPN ME 7027149
176 )
177 RETURNING spec_vr_id INTO x_supplier_spec_vrs.spec_vr_id
178 ;
179
180 IF SQL%FOUND THEN
181 RETURN TRUE;
182 ELSE
183 RETURN FALSE;
184 END IF;
185
186 EXCEPTION
187 WHEN OTHERS THEN
188 fnd_msg_pub.add_exc_msg ('GMD_SUPPLIER_SPEC_VRS_PVT', 'INSERT_ROW');
189 RETURN FALSE;
190
191 END insert_row;
192
193
194
195
196
197 FUNCTION delete_row (p_spec_vr_id IN NUMBER,
198 p_last_update_date IN DATE ,
199 p_last_updated_by IN NUMBER ,
200 p_last_update_login IN NUMBER )
201 RETURN BOOLEAN IS
202
203 dummy PLS_INTEGER;
204
205 locked_by_other_user EXCEPTION;
206 PRAGMA EXCEPTION_INIT (locked_by_other_user,-54);
207
208 BEGIN
209 IF p_spec_vr_id IS NOT NULL THEN
210 SELECT 1
211 INTO dummy
212 FROM gmd_supplier_spec_vrs
213 WHERE spec_vr_id = p_spec_vr_id
214 FOR UPDATE NOWAIT;
215
216 UPDATE gmd_supplier_spec_vrs
217 SET delete_mark = 1,
218 last_update_date = NVL(p_last_update_date,SYSDATE),
219 last_updated_by = NVL(p_last_updated_by,FND_GLOBAL.USER_ID),
220 last_update_login = NVL(p_last_update_login,FND_GLOBAL.LOGIN_ID)
221 WHERE spec_vr_id = p_spec_vr_id
222 ;
223 ELSE
224 GMD_API_PUB.log_message('GMD_NO_KEYS','TABLE_NAME', 'GMD_SUPPLIER_SPEC_VRS');
225 RETURN FALSE;
226 END IF;
227
228 IF (SQL%FOUND) THEN
229 RETURN TRUE;
230 ELSE
231 GMD_API_PUB.log_message('GMD_NO_DATA_FOUND','TABLE_NAME', 'GMD_SUPPLIER_SPEC_VRS');
232 RETURN FALSE;
233 END IF;
234
235 EXCEPTION
236 WHEN locked_by_other_user THEN
237 GMD_API_PUB.log_message('GMD_RECORD_LOCKED',
238 'TABLE_NAME', 'GMD_SUPPLIER_SPEC_VRS',
239 'RECORD','Supplier Spec Validity Rule',
240 'KEY', p_spec_vr_id);
241 RETURN FALSE;
242
243 WHEN OTHERS THEN
244 fnd_msg_pub.add_exc_msg ('GMD_SUPPLIER_SPEC_VRS_PVT', 'DELETE_ROW');
245 RETURN FALSE;
246
247 END delete_row;
248
249
250
251
252 FUNCTION lock_row (p_spec_vr_id IN NUMBER)
253 RETURN BOOLEAN IS
254
255 dummy PLS_INTEGER;
256
257 locked_by_other_user EXCEPTION;
258 PRAGMA EXCEPTION_INIT (locked_by_other_user,-54);
259
260 BEGIN
261 IF p_spec_vr_id IS NOT NULL THEN
262 SELECT 1
263 INTO dummy
264 FROM gmd_supplier_spec_vrs
265 WHERE spec_vr_id = p_spec_vr_id
266 FOR UPDATE NOWAIT;
267 ELSE
268 GMD_API_PUB.log_message('GMD_NO_KEYS','TABLE_NAME', 'GMD_SUPPLIER_SPEC_VRS');
269 RETURN FALSE;
270 END IF;
271
272 RETURN TRUE;
273
274 EXCEPTION
275 WHEN locked_by_other_user THEN
276 GMD_API_PUB.log_message('GMD_RECORD_LOCKED',
277 'TABLE_NAME', 'GMD_SUPPLIER_SPEC_VRS',
278 'RECORD','Supplier Spec Validity Rule',
279 'KEY', p_spec_vr_id);
280 RETURN FALSE;
281
282 WHEN OTHERS THEN
283 fnd_msg_pub.add_exc_msg ('GMD_SUPPLIER_SPEC_VRS_PVT', 'DELETE_ROW');
284 RETURN FALSE;
285
286 END lock_row;
287
288
289
290 FUNCTION fetch_row (
291 p_supplier_spec_vrs IN GMD_SUPPLIER_SPEC_VRS%ROWTYPE
292 , x_supplier_spec_vrs OUT NOCOPY GMD_SUPPLIER_SPEC_VRS%ROWTYPE
293 )
294 RETURN BOOLEAN
295 IS
296 BEGIN
297
298 IF (p_supplier_spec_vrs.spec_vr_id IS NOT NULL) THEN
299 SELECT *
300 INTO x_supplier_spec_vrs
301 FROM gmd_supplier_spec_vrs
302 WHERE spec_vr_id = p_supplier_spec_vrs.spec_vr_id
303 ;
304 ELSE
305 gmd_api_pub.log_message('GMD_NO_KEYS','TABLE_NAME', 'GMD_SUPPLIER_SPEC_VRS');
306 RETURN FALSE;
307 END IF;
308
309 RETURN TRUE;
310
311 EXCEPTION
312 WHEN NO_DATA_FOUND
313 THEN
314 gmd_api_pub.log_message('GMD_NO_DATA_FOUND','TABLE_NAME', 'GMD_SUPPLIER_SPEC_VRS');
315 RETURN FALSE;
316 WHEN OTHERS
317 THEN
318 fnd_msg_pub.add_exc_msg ('GMD_SUPPLIER_SPEC_VRS_PVT', 'FETCH_ROW');
319 RETURN FALSE;
320
321 END fetch_row;
322
323 END GMD_SUPPLIER_SPEC_VRS_PVT;