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