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