[Home] [Help]
PACKAGE BODY: APPS.AMW_VIOLATIONS_PKG
Source
1 PACKAGE BODY AMW_VIOLATIONS_PKG AS
2 /* $Header: amwtvlab.pls 120.1 2005/06/23 11:39:23 appldev noship $ */
3
4 -- ===============================================================
5 -- Package name
6 -- AMW_VIOLATIONS_PKG
7 -- Purpose
8 --
9 -- History
10 -- 11/11/2003 tsho Creates
11 -- 05/20/2005 tsho AMW.E add reval_request_id,reval_request_date, reval_requested_by_id
12 -- ===============================================================
13
14
15
16 -- ===============================================================
17 -- Procedure name
18 -- INSERT_ROW
19 -- Purpose
20 -- create new violation
21 -- in AMW_VIOLATIONS
22 -- History
23 -- 05.20.2005 tsho: AMW.E add reval_request_id,reval_request_date, reval_requested_by_id
24 -- ===============================================================
25 procedure INSERT_ROW (
26 X_ROWID in out nocopy VARCHAR2,
27 X_VIOLATION_ID in NUMBER,
28 X_CONSTRAINT_REV_ID in NUMBER,
29 X_REQUEST_ID in NUMBER,
30 X_REQUEST_DATE in DATE,
31 X_REQUESTED_BY_ID in NUMBER,
32 X_VIOLATOR_NUM in NUMBER,
33 X_STATUS_CODE in VARCHAR2,
34 X_LAST_UPDATED_BY in NUMBER,
35 X_LAST_UPDATE_DATE in DATE,
36 X_CREATED_BY in NUMBER,
37 X_CREATION_DATE in DATE,
38 X_LAST_UPDATE_LOGIN in NUMBER,
39 X_SECURITY_GROUP_ID in NUMBER,
40 X_OBJECT_VERSION_NUMBER in NUMBER,
41 X_ATTRIBUTE_CATEGORY in VARCHAR2 := NULL,
42 X_ATTRIBUTE1 in VARCHAR2 := NULL,
43 X_ATTRIBUTE2 in VARCHAR2 := NULL,
44 X_ATTRIBUTE3 in VARCHAR2 := NULL,
45 X_ATTRIBUTE4 in VARCHAR2 := NULL,
46 X_ATTRIBUTE5 in VARCHAR2 := NULL,
47 X_ATTRIBUTE6 in VARCHAR2 := NULL,
48 X_ATTRIBUTE7 in VARCHAR2 := NULL,
49 X_ATTRIBUTE8 in VARCHAR2 := NULL,
50 X_ATTRIBUTE9 in VARCHAR2 := NULL,
51 X_ATTRIBUTE10 in VARCHAR2 := NULL,
52 X_ATTRIBUTE11 in VARCHAR2 := NULL,
53 X_ATTRIBUTE12 in VARCHAR2 := NULL,
54 X_ATTRIBUTE13 in VARCHAR2 := NULL,
55 X_ATTRIBUTE14 in VARCHAR2 := NULL,
56 X_ATTRIBUTE15 in VARCHAR2 := NULL,
57 X_REVAL_REQUEST_ID in NUMBER := NULL,
58 X_REVAL_REQUEST_DATE in DATE := NULL,
59 X_REVAL_REQUESTED_BY_ID in NUMBER := NULL
60 ) is
61 cursor C is select ROWID from AMW_VIOLATIONS
62 where VIOLATION_ID = X_VIOLATION_ID
63 ;
64 begin
65 insert into AMW_VIOLATIONS (
66 VIOLATION_ID,
67 CONSTRAINT_REV_ID,
68 REQUEST_ID,
69 REQUEST_DATE,
70 REQUESTED_BY_ID,
71 VIOLATOR_NUM,
72 STATUS_CODE,
73 LAST_UPDATED_BY,
74 LAST_UPDATE_DATE,
75 CREATED_BY,
76 CREATION_DATE,
77 LAST_UPDATE_LOGIN,
78 SECURITY_GROUP_ID,
79 OBJECT_VERSION_NUMBER,
80 ATTRIBUTE_CATEGORY,
81 ATTRIBUTE1,
82 ATTRIBUTE2,
83 ATTRIBUTE3,
84 ATTRIBUTE4,
85 ATTRIBUTE5,
86 ATTRIBUTE6,
87 ATTRIBUTE7,
88 ATTRIBUTE8,
89 ATTRIBUTE9,
90 ATTRIBUTE10,
91 ATTRIBUTE11,
92 ATTRIBUTE12,
93 ATTRIBUTE13,
94 ATTRIBUTE14,
95 ATTRIBUTE15,
96 REVAL_REQUEST_ID,
97 REVAL_REQUEST_DATE,
98 REVAL_REQUESTED_BY_ID
99 ) values (
100 X_VIOLATION_ID,
101 X_CONSTRAINT_REV_ID,
102 X_REQUEST_ID,
103 X_REQUEST_DATE,
104 X_REQUESTED_BY_ID,
105 X_VIOLATOR_NUM,
106 X_STATUS_CODE,
107 X_LAST_UPDATED_BY,
108 X_LAST_UPDATE_DATE,
109 X_CREATED_BY,
110 X_CREATION_DATE,
111 X_LAST_UPDATE_LOGIN,
112 X_SECURITY_GROUP_ID,
113 X_OBJECT_VERSION_NUMBER,
114 X_ATTRIBUTE_CATEGORY,
115 X_ATTRIBUTE1,
116 X_ATTRIBUTE2,
117 X_ATTRIBUTE3,
118 X_ATTRIBUTE4,
119 X_ATTRIBUTE5,
120 X_ATTRIBUTE6,
121 X_ATTRIBUTE7,
122 X_ATTRIBUTE8,
123 X_ATTRIBUTE9,
124 X_ATTRIBUTE10,
125 X_ATTRIBUTE11,
126 X_ATTRIBUTE12,
127 X_ATTRIBUTE13,
128 X_ATTRIBUTE14,
129 X_ATTRIBUTE15,
130 X_REVAL_REQUEST_ID,
131 X_REVAL_REQUEST_DATE,
132 X_REVAL_REQUESTED_BY_ID
133 );
134
135 open c;
136 fetch c into X_ROWID;
137 if (c%notfound) then
138 close c;
139 raise no_data_found;
140 end if;
141 close c;
142
143 end INSERT_ROW;
144
145
146
147 -- ===============================================================
148 -- Procedure name
149 -- LOCK_ROW
150 -- Purpose
151 --
152 -- ===============================================================
153 procedure LOCK_ROW (
154 X_VIOLATION_ID in NUMBER,
155 X_CONSTRAINT_REV_ID in NUMBER,
156 X_REQUEST_ID in NUMBER,
157 X_REQUEST_DATE in DATE,
158 X_REQUESTED_BY_ID in NUMBER,
159 X_VIOLATOR_NUM in NUMBER,
160 X_STATUS_CODE in VARCHAR2,
161 X_SECURITY_GROUP_ID in NUMBER,
162 X_OBJECT_VERSION_NUMBER in NUMBER,
163 X_ATTRIBUTE_CATEGORY in VARCHAR2,
164 X_ATTRIBUTE1 in VARCHAR2,
165 X_ATTRIBUTE2 in VARCHAR2,
166 X_ATTRIBUTE3 in VARCHAR2,
167 X_ATTRIBUTE4 in VARCHAR2,
168 X_ATTRIBUTE5 in VARCHAR2,
169 X_ATTRIBUTE6 in VARCHAR2,
170 X_ATTRIBUTE7 in VARCHAR2,
171 X_ATTRIBUTE8 in VARCHAR2,
172 X_ATTRIBUTE9 in VARCHAR2,
173 X_ATTRIBUTE10 in VARCHAR2,
174 X_ATTRIBUTE11 in VARCHAR2,
175 X_ATTRIBUTE12 in VARCHAR2,
176 X_ATTRIBUTE13 in VARCHAR2,
177 X_ATTRIBUTE14 in VARCHAR2,
178 X_ATTRIBUTE15 in VARCHAR2,
179 X_REVAL_REQUEST_ID in NUMBER := NULL,
180 X_REVAL_REQUEST_DATE in DATE := NULL,
181 X_REVAL_REQUESTED_BY_ID in NUMBER := NULL
182 ) is
183 cursor c is select
184 CONSTRAINT_REV_ID,
185 REQUEST_ID,
186 REQUEST_DATE,
187 REQUESTED_BY_ID,
188 VIOLATOR_NUM,
189 STATUS_CODE,
190 SECURITY_GROUP_ID,
191 OBJECT_VERSION_NUMBER,
192 ATTRIBUTE_CATEGORY,
193 ATTRIBUTE1,
194 ATTRIBUTE2,
195 ATTRIBUTE3,
196 ATTRIBUTE4,
197 ATTRIBUTE5,
198 ATTRIBUTE6,
199 ATTRIBUTE7,
200 ATTRIBUTE8,
201 ATTRIBUTE9,
202 ATTRIBUTE10,
203 ATTRIBUTE11,
204 ATTRIBUTE12,
205 ATTRIBUTE13,
206 ATTRIBUTE14,
207 ATTRIBUTE15,
208 REVAL_REQUEST_ID,
209 REVAL_REQUEST_DATE,
210 REVAL_REQUESTED_BY_ID
211 from AMW_VIOLATIONS
212 where VIOLATION_ID = X_VIOLATION_ID
213 for update of VIOLATION_ID nowait;
214 recinfo c%rowtype;
215
216 begin
217 open c;
218 fetch c into recinfo;
219 if (c%notfound) then
220 close c;
221 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
222 app_exception.raise_exception;
223 end if;
224 close c;
225 if (
226 ((recinfo.CONSTRAINT_REV_ID = X_CONSTRAINT_REV_ID)
227 OR ((recinfo.CONSTRAINT_REV_ID is null) AND (X_CONSTRAINT_REV_ID is null)))
228 AND ((recinfo.REQUEST_ID = X_REQUEST_ID)
229 OR ((recinfo.REQUEST_ID is null) AND (X_REQUEST_ID is null)))
230 AND ((recinfo.REQUEST_DATE = X_REQUEST_DATE)
231 OR ((recinfo.REQUEST_DATE is null) AND (X_REQUEST_DATE is null)))
232 AND ((recinfo.REQUESTED_BY_ID = X_REQUESTED_BY_ID)
233 OR ((recinfo.REQUESTED_BY_ID is null) AND (X_REQUESTED_BY_ID is null)))
234 AND ((recinfo.VIOLATOR_NUM = X_VIOLATOR_NUM)
235 OR ((recinfo.VIOLATOR_NUM is null) AND (X_VIOLATOR_NUM is null)))
236 AND ((recinfo.STATUS_CODE = X_STATUS_CODE)
237 OR ((recinfo.STATUS_CODE is null) AND (X_STATUS_CODE is null)))
238 AND ((recinfo.SECURITY_GROUP_ID = X_SECURITY_GROUP_ID)
239 OR ((recinfo.SECURITY_GROUP_ID is null) AND (X_SECURITY_GROUP_ID is null)))
240 AND ((recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
241 OR ((recinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
242 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
243 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
244 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
245 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
246 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
247 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
248 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
249 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
250 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
251 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
252 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
253 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
254 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
255 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
256 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
257 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
258 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
259 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
260 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
261 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
262 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
263 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
264 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
265 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
266 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
267 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
268 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
269 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
270 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
271 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
272 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
273 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
274 AND ((recinfo.REVAL_REQUEST_ID = X_REVAL_REQUEST_ID)
275 OR ((recinfo.REVAL_REQUEST_ID is null) AND (X_REVAL_REQUEST_ID is null)))
276 AND ((recinfo.REVAL_REQUEST_DATE = X_REVAL_REQUEST_DATE)
277 OR ((recinfo.REVAL_REQUEST_DATE is null) AND (X_REVAL_REQUEST_DATE is null)))
278 AND ((recinfo.REVAL_REQUESTED_BY_ID = X_REVAL_REQUESTED_BY_ID)
279 OR ((recinfo.REVAL_REQUESTED_BY_ID is null) AND (X_REVAL_REQUESTED_BY_ID is null)))
280 ) then
281 null;
282 else
283 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
284 app_exception.raise_exception;
285 end if;
286
287 return;
288 end LOCK_ROW;
289
290
291
292 -- ===============================================================
293 -- Procedure name
294 -- UPDATE_ROW
295 -- Purpose
296 -- update AMW_VIOLATIONS
297 -- History
298 -- 05.20.2005 tsho: AMW.E add reval_request_id,reval_request_date, reval_requested_by_id
299 -- ===============================================================
300 procedure UPDATE_ROW (
301 X_VIOLATION_ID in NUMBER,
302 X_CONSTRAINT_REV_ID in NUMBER,
303 X_VIOLATOR_NUM in NUMBER,
304 X_STATUS_CODE in VARCHAR2,
305 X_LAST_UPDATED_BY in NUMBER,
306 X_LAST_UPDATE_DATE in DATE,
307 X_LAST_UPDATE_LOGIN in NUMBER,
308 X_SECURITY_GROUP_ID in NUMBER,
309 X_OBJECT_VERSION_NUMBER in NUMBER,
310 X_ATTRIBUTE_CATEGORY in VARCHAR2 := NULL,
311 X_ATTRIBUTE1 in VARCHAR2 := NULL,
312 X_ATTRIBUTE2 in VARCHAR2 := NULL,
313 X_ATTRIBUTE3 in VARCHAR2 := NULL,
314 X_ATTRIBUTE4 in VARCHAR2 := NULL,
315 X_ATTRIBUTE5 in VARCHAR2 := NULL,
316 X_ATTRIBUTE6 in VARCHAR2 := NULL,
317 X_ATTRIBUTE7 in VARCHAR2 := NULL,
318 X_ATTRIBUTE8 in VARCHAR2 := NULL,
319 X_ATTRIBUTE9 in VARCHAR2 := NULL,
320 X_ATTRIBUTE10 in VARCHAR2 := NULL,
321 X_ATTRIBUTE11 in VARCHAR2 := NULL,
322 X_ATTRIBUTE12 in VARCHAR2 := NULL,
323 X_ATTRIBUTE13 in VARCHAR2 := NULL,
324 X_ATTRIBUTE14 in VARCHAR2 := NULL,
325 X_ATTRIBUTE15 in VARCHAR2 := NULL,
326 X_REVAL_REQUEST_ID in NUMBER := NULL,
327 X_REVAL_REQUEST_DATE in DATE := NULL,
328 X_REVAL_REQUESTED_BY_ID in NUMBER := NULL
329 ) is
330 begin
331 update AMW_VIOLATIONS set
332 CONSTRAINT_REV_ID = X_CONSTRAINT_REV_ID,
333 VIOLATOR_NUM = X_VIOLATOR_NUM,
334 STATUS_CODE = X_STATUS_CODE,
335 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
336 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
337 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
338 SECURITY_GROUP_ID = X_SECURITY_GROUP_ID,
339 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
340 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
341 ATTRIBUTE1 = X_ATTRIBUTE1,
342 ATTRIBUTE2 = X_ATTRIBUTE2,
343 ATTRIBUTE3 = X_ATTRIBUTE3,
344 ATTRIBUTE4 = X_ATTRIBUTE4,
345 ATTRIBUTE5 = X_ATTRIBUTE5,
346 ATTRIBUTE6 = X_ATTRIBUTE6,
347 ATTRIBUTE7 = X_ATTRIBUTE7,
348 ATTRIBUTE8 = X_ATTRIBUTE8,
349 ATTRIBUTE9 = X_ATTRIBUTE9,
350 ATTRIBUTE10 = X_ATTRIBUTE10,
351 ATTRIBUTE11 = X_ATTRIBUTE11,
352 ATTRIBUTE12 = X_ATTRIBUTE12,
353 ATTRIBUTE13 = X_ATTRIBUTE13,
354 ATTRIBUTE14 = X_ATTRIBUTE14,
355 ATTRIBUTE15 = X_ATTRIBUTE15,
356 REVAL_REQUEST_ID = X_REVAL_REQUEST_ID,
357 REVAL_REQUEST_DATE = X_REVAL_REQUEST_DATE,
358 REVAL_REQUESTED_BY_ID = X_REVAL_REQUESTED_BY_ID
359 where VIOLATION_ID = X_VIOLATION_ID;
360
361 if (sql%notfound) then
362 raise no_data_found;
363 end if;
364
365
366 end UPDATE_ROW;
367
368
369 -- ===============================================================
370 -- Procedure name
371 -- DELETE_ROW
372 -- Purpose
373 --
374 -- ===============================================================
375 procedure DELETE_ROW (
376 X_VIOLATION_ID in NUMBER
377 ) is
378 begin
379 delete from AMW_VIOLATIONS
380 where VIOLATION_ID = X_VIOLATION_ID;
381
382 if (sql%notfound) then
383 raise no_data_found;
384 end if;
385
386 end DELETE_ROW;
387
388
389
390
391 -- ----------------------------------------------------------------------
392 end AMW_VIOLATIONS_PKG;
393