[Home] [Help]
PACKAGE BODY: APPS.WMS_RULE_CONSISTENCIES_PKG
Source
1 package body WMS_RULE_CONSISTENCIES_PKG as
2 /* $Header: WMSHPCOB.pls 120.1 2005/06/21 10:12:49 appldev ship $*/
3
4 procedure INSERT_ROW (
5 X_ROWID in out NOCOPY VARCHAR2,
6 X_CONSISTENCY_ID in NUMBER,
7 X_RULE_ID in NUMBER,
8 X_CREATION_DATE in DATE,
9 X_CREATED_BY in NUMBER,
10 X_LAST_UPDATE_DATE in DATE,
11 X_LAST_UPDATED_BY in NUMBER,
12 X_LAST_UPDATE_LOGIN in NUMBER,
13 X_PARAMETER_ID in NUMBER,
14 X_ATTRIBUTE_CATEGORY in VARCHAR2,
15 X_ATTRIBUTE1 in VARCHAR2,
16 X_ATTRIBUTE2 in VARCHAR2,
17 X_ATTRIBUTE3 in VARCHAR2,
18 X_ATTRIBUTE4 in VARCHAR2,
19 X_ATTRIBUTE5 in VARCHAR2,
20 X_ATTRIBUTE6 in VARCHAR2,
21 X_ATTRIBUTE7 in VARCHAR2,
22 X_ATTRIBUTE8 in VARCHAR2,
23 X_ATTRIBUTE9 in VARCHAR2,
24 X_ATTRIBUTE10 in VARCHAR2,
25 X_ATTRIBUTE11 in VARCHAR2,
26 X_ATTRIBUTE12 in VARCHAR2,
27 X_ATTRIBUTE13 in VARCHAR2,
28 X_ATTRIBUTE14 in VARCHAR2,
29 X_ATTRIBUTE15 in VARCHAR2
30 ) is
31 cursor C is select ROWID from WMS_RULE_CONSISTENCIES
32 where RULE_ID = X_RULE_ID
33 and CONSISTENCY_ID = X_CONSISTENCY_ID
34 ;
35 begin
36 insert into WMS_RULE_CONSISTENCIES (
37 CONSISTENCY_ID,
38 RULE_ID,
39 PARAMETER_ID,
40 LAST_UPDATED_BY,
41 LAST_UPDATE_DATE,
42 CREATED_BY,
43 CREATION_DATE,
44 LAST_UPDATE_LOGIN,
45 ATTRIBUTE_CATEGORY,
46 ATTRIBUTE1,
47 ATTRIBUTE2,
48 ATTRIBUTE3,
49 ATTRIBUTE4,
50 ATTRIBUTE5,
51 ATTRIBUTE6,
52 ATTRIBUTE7,
53 ATTRIBUTE8,
54 ATTRIBUTE9,
55 ATTRIBUTE10,
56 ATTRIBUTE11,
57 ATTRIBUTE12,
58 ATTRIBUTE13,
59 ATTRIBUTE14,
60 ATTRIBUTE15
61 ) values (
62 X_CONSISTENCY_ID,
63 X_RULE_ID,
64 X_PARAMETER_ID,
65 X_LAST_UPDATED_BY,
66 X_LAST_UPDATE_DATE,
67 X_CREATED_BY,
68 X_CREATION_DATE,
69 X_LAST_UPDATE_LOGIN,
70 X_ATTRIBUTE_CATEGORY,
71 X_ATTRIBUTE1,
72 X_ATTRIBUTE2,
73 X_ATTRIBUTE3,
74 X_ATTRIBUTE4,
75 X_ATTRIBUTE5,
76 X_ATTRIBUTE6,
77 X_ATTRIBUTE7,
78 X_ATTRIBUTE8,
79 X_ATTRIBUTE9,
80 X_ATTRIBUTE10,
81 X_ATTRIBUTE11,
82 X_ATTRIBUTE12,
83 X_ATTRIBUTE13,
84 X_ATTRIBUTE14,
85 X_ATTRIBUTE15
86 );
87 open c;
88 fetch c into X_ROWID;
89 if (c%notfound) then
90 close c;
91 raise no_data_found;
92 end if;
93 close c;
94
95 end INSERT_ROW;
96
97 procedure LOCK_ROW (
98 X_CONSISTENCY_ID in NUMBER,
99 X_RULE_ID in NUMBER,
100 X_PARAMETER_ID in NUMBER,
101 X_ATTRIBUTE_CATEGORY in VARCHAR2,
102 X_ATTRIBUTE1 in VARCHAR2,
103 X_ATTRIBUTE2 in VARCHAR2,
104 X_ATTRIBUTE3 in VARCHAR2,
105 X_ATTRIBUTE4 in VARCHAR2,
106 X_ATTRIBUTE5 in VARCHAR2,
107 X_ATTRIBUTE6 in VARCHAR2,
108 X_ATTRIBUTE7 in VARCHAR2,
109 X_ATTRIBUTE8 in VARCHAR2,
110 X_ATTRIBUTE9 in VARCHAR2,
111 X_ATTRIBUTE10 in VARCHAR2,
112 X_ATTRIBUTE11 in VARCHAR2,
113 X_ATTRIBUTE12 in VARCHAR2,
114 X_ATTRIBUTE13 in VARCHAR2,
115 X_ATTRIBUTE14 in VARCHAR2,
116 X_ATTRIBUTE15 in VARCHAR2
117 ) is
118 cursor c1 is select
119 CONSISTENCY_ID,
120 RULE_ID,
121 PARAMETER_ID,
122 ATTRIBUTE_CATEGORY,
123 ATTRIBUTE1,
124 ATTRIBUTE2,
125 ATTRIBUTE3,
126 ATTRIBUTE4,
127 ATTRIBUTE5,
128 ATTRIBUTE6,
129 ATTRIBUTE7,
130 ATTRIBUTE8,
131 ATTRIBUTE9,
132 ATTRIBUTE10,
133 ATTRIBUTE11,
134 ATTRIBUTE12,
135 ATTRIBUTE13,
136 ATTRIBUTE14,
137 ATTRIBUTE15
138 from WMS_RULE_CONSISTENCIES
139 where RULE_ID = X_RULE_ID
140 and CONSISTENCY_ID = X_CONSISTENCY_ID
141 for update of CONSISTENCY_ID nowait;
142
143 tlinfo c1%ROWTYPE;
144 begin
145 OPEN c1;
146 FETCH c1 INTO tlinfo;
147 IF (c1%notfound) THEN
148 CLOSE c1;
149 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
150 app_exception.raise_exception;
151 END IF;
152 CLOSE c1;
153
154 IF ( (tlinfo.CONSISTENCY_ID = X_CONSISTENCY_ID)
155 AND (tlinfo.RULE_ID = X_RULE_ID)
156 AND (tlinfo.PARAMETER_ID = X_PARAMETER_ID)
157 AND ((tlinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
158 OR ((tlinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
159 AND ((tlinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
160 OR ((tlinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
161 AND ((tlinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
162 OR ((tlinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
163 AND ((tlinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
164 OR ((tlinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
165 AND ((tlinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
166 OR ((tlinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
167 AND ((tlinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
168 OR ((tlinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
169 AND ((tlinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
170 OR ((tlinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
171 AND ((tlinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
172 OR ((tlinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
173 AND ((tlinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
174 OR ((tlinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
175 AND ((tlinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
176 OR ((tlinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
177 AND ((tlinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
178 OR ((tlinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
179 AND ((tlinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
180 OR ((tlinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
181 AND ((tlinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
182 OR ((tlinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
183 AND ((tlinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
184 OR ((tlinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
185 AND ((tlinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
186 OR ((tlinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
187 AND ((tlinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
188 OR ((tlinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
189 ) then
190 null;
191 else
192 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
193 app_exception.raise_exception;
194 end if;
195 end LOCK_ROW;
196
197 procedure UPDATE_ROW (
198 X_CONSISTENCY_ID in NUMBER,
199 X_RULE_ID in NUMBER,
200 X_LAST_UPDATE_DATE in DATE,
201 X_LAST_UPDATED_BY in NUMBER,
202 X_LAST_UPDATE_LOGIN in NUMBER,
203 X_PARAMETER_ID in NUMBER,
204 X_ATTRIBUTE_CATEGORY in VARCHAR2,
205 X_ATTRIBUTE1 in VARCHAR2,
206 X_ATTRIBUTE2 in VARCHAR2,
207 X_ATTRIBUTE3 in VARCHAR2,
208 X_ATTRIBUTE4 in VARCHAR2,
209 X_ATTRIBUTE5 in VARCHAR2,
210 X_ATTRIBUTE6 in VARCHAR2,
211 X_ATTRIBUTE7 in VARCHAR2,
212 X_ATTRIBUTE8 in VARCHAR2,
213 X_ATTRIBUTE9 in VARCHAR2,
214 X_ATTRIBUTE10 in VARCHAR2,
215 X_ATTRIBUTE11 in VARCHAR2,
216 X_ATTRIBUTE12 in VARCHAR2,
217 X_ATTRIBUTE13 in VARCHAR2,
218 X_ATTRIBUTE14 in VARCHAR2,
219 X_ATTRIBUTE15 in VARCHAR2
220 ) is
221 begin
222 update WMS_RULE_CONSISTENCIES set
223 RULE_ID = X_RULE_ID,
224 PARAMETER_ID = X_PARAMETER_ID,
225 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
226 ATTRIBUTE1 = X_ATTRIBUTE1,
227 ATTRIBUTE2 = X_ATTRIBUTE2,
228 ATTRIBUTE3 = X_ATTRIBUTE3,
229 ATTRIBUTE4 = X_ATTRIBUTE4,
230 ATTRIBUTE5 = X_ATTRIBUTE5,
231 ATTRIBUTE6 = X_ATTRIBUTE6,
232 ATTRIBUTE7 = X_ATTRIBUTE7,
233 ATTRIBUTE8 = X_ATTRIBUTE8,
234 ATTRIBUTE9 = X_ATTRIBUTE9,
235 ATTRIBUTE10 = X_ATTRIBUTE10,
236 ATTRIBUTE11 = X_ATTRIBUTE11,
237 ATTRIBUTE12 = X_ATTRIBUTE12,
238 ATTRIBUTE13 = X_ATTRIBUTE13,
239 ATTRIBUTE14 = X_ATTRIBUTE14,
240 ATTRIBUTE15 = X_ATTRIBUTE15,
241 CONSISTENCY_ID = X_CONSISTENCY_ID,
242 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
243 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
244 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
245 where CONSISTENCY_ID = X_CONSISTENCY_ID;
246
247 if (sql%notfound) then
248 raise no_data_found;
249 end if;
250 end UPDATE_ROW;
251
252 procedure DELETE_ROW (
253 X_ROWID in VARCHAR2
254 ) is
255 begin
256 delete from WMS_RULE_CONSISTENCIES
257 where rowid = X_ROWID;
258
259 if (sql%notfound) then
260 raise no_data_found;
261 end if;
262
263 end DELETE_ROW;
264
265 PROCEDURE LOAD_ROW (
266 X_CONSISTENCY_ID IN NUMBER
267 ,X_OWNER IN VARCHAR2
268 ,X_RULE_ID IN NUMBER
269 ,X_PARAMETER_ID IN NUMBER
270 ,X_ATTRIBUTE_CATEGORY IN VARCHAR2
271 ,X_ATTRIBUTE1 IN VARCHAR2
272 ,X_ATTRIBUTE2 IN VARCHAR2
273 ,X_ATTRIBUTE3 IN VARCHAR2
274 ,X_ATTRIBUTE4 IN VARCHAR2
275 ,X_ATTRIBUTE5 IN VARCHAR2
276 ,X_ATTRIBUTE6 IN VARCHAR2
277 ,X_ATTRIBUTE7 IN VARCHAR2
278 ,X_ATTRIBUTE8 IN VARCHAR2
279 ,X_ATTRIBUTE9 IN VARCHAR2
280 ,X_ATTRIBUTE10 IN VARCHAR2
281 ,X_ATTRIBUTE11 IN VARCHAR2
282 ,X_ATTRIBUTE12 IN VARCHAR2
283 ,X_ATTRIBUTE13 IN VARCHAR2
284 ,X_ATTRIBUTE14 IN VARCHAR2
285 ,X_ATTRIBUTE15 IN VARCHAR2
286 ) IS
287 BEGIN
288 DECLARE
289 l_rule_id NUMBER;
290 l_consistency_id NUMBER;
291 l_parameter_id NUMBER;
292 l_user_id NUMBER := 0;
293 l_row_id VARCHAR2(64);
294 l_sysdate DATE;
295 BEGIN
296 IF (x_owner = 'SEED') THEN
297 l_user_id := 1;
298 END IF;
299 --
300 SELECT Sysdate INTO l_sysdate FROM dual;
301 l_rule_id := fnd_number.canonical_to_number(x_rule_id);
302 l_parameter_id := fnd_number.canonical_to_number(x_parameter_id );
303 l_consistency_id :=
304 fnd_number.canonical_to_number(x_consistency_id );
305
306 wms_rule_consistencies_pkg.update_row
307 (
308 x_consistency_id => l_consistency_id
309 ,x_rule_id => l_rule_id
310 ,x_last_update_date => l_sysdate
311 ,x_last_updated_by => l_user_id
312 ,x_last_update_login => 0
313 ,x_parameter_id => l_parameter_id
314 ,x_attribute_category => x_attribute_category
315 ,x_attribute1 => x_attribute1
316 ,x_attribute2 => x_attribute2
317 ,x_attribute3 => x_attribute3
318 ,x_attribute4 => x_attribute4
319 ,x_attribute5 => x_attribute5
320 ,x_attribute6 => x_attribute6
321 ,x_attribute7 => x_attribute7
322 ,x_attribute8 => x_attribute8
323 ,x_attribute9 => x_attribute9
324 ,x_attribute10 => x_attribute10
325 ,x_attribute11 => x_attribute11
326 ,x_attribute12 => x_attribute12
327 ,x_attribute13 => x_attribute13
328 ,x_attribute14 => x_attribute14
329 ,x_attribute15 => x_attribute15
330 );
331 EXCEPTION
332 WHEN no_data_found THEN
333 wms_rule_consistencies_pkg.insert_row
334 (
335 x_rowid => l_row_id
336 ,x_consistency_id => l_consistency_id
337 ,x_rule_id => l_rule_id
338 ,x_creation_date => l_sysdate
339 ,x_created_by => l_user_id
340 ,x_last_update_date => l_sysdate
341 ,x_last_updated_by => l_user_id
342 ,x_last_update_login => 0
343 ,x_parameter_id => l_parameter_id
344 ,x_attribute_category => x_attribute_category
345 ,x_attribute1 => x_attribute1
346 ,x_attribute2 => x_attribute2
347 ,x_attribute3 => x_attribute3
348 ,x_attribute4 => x_attribute4
349 ,x_attribute5 => x_attribute5
350 ,x_attribute6 => x_attribute6
351 ,x_attribute7 => x_attribute7
352 ,x_attribute8 => x_attribute8
353 ,x_attribute9 => x_attribute9
354 ,x_attribute10 => x_attribute10
355 ,x_attribute11 => x_attribute11
356 ,x_attribute12 => x_attribute12
357 ,x_attribute13 => x_attribute13
358 ,x_attribute14 => x_attribute14
359 ,x_attribute15 => x_attribute15
360 );
361 END;
362 END load_row;
363 end WMS_RULE_CONSISTENCIES_PKG;