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