[Home] [Help]
PACKAGE BODY: APPS.AS_CARD_RULE_QUAL_VALUES_PKG
Source
1 PACKAGE BODY as_card_rule_qual_values_pkg AS
2 /* $Header: asxtcqvb.pls 120.1 2005/06/24 16:58:11 appldev ship $ */
3 PROCEDURE insert_row(
4 x_rowid IN OUT NOCOPY VARCHAR2
5 , x_qual_value_id NUMBER
6 , x_last_update_date DATE
7 , x_last_updated_by NUMBER
8 , x_creation_date DATE
9 , x_created_by NUMBER
10 , x_last_update_login NUMBER
11 , x_scorecard_id NUMBER
12 , x_score NUMBER
13 , x_card_rule_id NUMBER
14 , x_seed_qual_id NUMBER
15 , x_high_value_number NUMBER
16 , x_low_value_number NUMBER
17 , x_high_value_char VARCHAR2
18 , x_low_value_char VARCHAR2
19 , x_currency_code VARCHAR2
20 , x_low_value_date DATE
21 , x_high_value_date DATE
22 , x_start_date_active DATE
23 , x_end_date_active DATE
24 ) IS
25 CURSOR l_insert IS
26 SELECT ROWID
27 FROM as_card_rule_qual_values
28 WHERE qual_value_id = x_qual_value_id;
29 BEGIN
30 INSERT INTO as_card_rule_qual_values (
31 qual_value_id
32 , last_update_date
33 , last_updated_by
34 , creation_date
35 , created_by
36 , last_update_login
37 , scorecard_id
38 , score
39 , card_rule_id
40 , seed_qual_id
41 , high_value_number
42 , low_value_number
43 , high_value_char
44 , low_value_char
45 , currency_code
46 , low_value_date
47 , high_value_date
48 , start_date_active
49 , end_date_active
50 ) VALUES (
51 x_qual_value_id
52 , DECODE(x_last_update_date,FND_API.G_MISS_DATE,TO_DATE(NULL),x_last_update_date)
53 , DECODE(x_last_updated_by,FND_API.G_MISS_NUM,NULL,x_last_updated_by)
54 , DECODE(x_creation_date,FND_API.G_MISS_DATE,TO_DATE(NULL),x_creation_date)
55 , DECODE(x_created_by,FND_API.G_MISS_NUM,NULL,x_created_by)
56 , DECODE(x_last_update_login,FND_API.G_MISS_NUM,NULL,x_last_update_login)
57 , DECODE(x_scorecard_id,FND_API.G_MISS_NUM,NULL,x_scorecard_id)
58 , DECODE(x_score,FND_API.G_MISS_NUM,NULL,x_score)
59 , DECODE(x_card_rule_id,FND_API.G_MISS_NUM,NULL,x_card_rule_id)
60 , DECODE(x_seed_qual_id,FND_API.G_MISS_NUM,NULL,x_seed_qual_id)
61 , DECODE(x_high_value_number,FND_API.G_MISS_NUM,NULL,x_high_value_number)
62 , DECODE(x_low_value_number,FND_API.G_MISS_NUM,NULL,x_low_value_number)
63 , DECODE(x_high_value_char,FND_API.G_MISS_CHAR,NULL,x_high_value_char)
64 , DECODE(x_low_value_char,FND_API.G_MISS_CHAR,NULL,x_low_value_char)
65 , DECODE(x_currency_code,FND_API.G_MISS_CHAR,NULL,x_currency_code)
66 , DECODE(x_low_value_date,FND_API.G_MISS_DATE,TO_DATE(NULL),x_low_value_date)
67 , DECODE(x_high_value_date,FND_API.G_MISS_DATE,TO_DATE(NULL),x_high_value_date)
68 , DECODE(x_start_date_active,FND_API.G_MISS_DATE,TO_DATE(NULL),x_start_date_active)
69 , DECODE(x_end_date_active,FND_API.G_MISS_DATE,TO_DATE(NULL),x_end_date_active)
70 );
71
72 OPEN l_insert;
73 FETCH l_insert INTO x_rowid;
74 IF (l_insert%NOTFOUND) THEN
75 CLOSE l_insert;
76 RAISE NO_DATA_FOUND;
77 END IF;
78 END insert_row;
79
80 PROCEDURE delete_row(
81 x_qual_value_id NUMBER
82 ) IS
83 BEGIN
84 DELETE FROM as_card_rule_qual_values
85 WHERE qual_value_id = x_qual_value_id;
86 IF (SQL%NOTFOUND) THEN
87 RAISE NO_DATA_FOUND;
88 END IF;
89 END delete_row;
90
91 PROCEDURE update_row(
92 x_rowid VARCHAR2
93 , x_qual_value_id NUMBER
94 , x_last_update_date DATE
95 , x_last_updated_by NUMBER
96 , x_last_update_login NUMBER
97 , x_scorecard_id NUMBER
98 , x_score NUMBER
99 , x_card_rule_id NUMBER
100 , x_seed_qual_id NUMBER
101 , x_high_value_number NUMBER
102 , x_low_value_number NUMBER
103 , x_high_value_char VARCHAR2
104 , x_low_value_char VARCHAR2
105 , x_currency_code VARCHAR2
106 , x_low_value_date DATE
107 , x_high_value_date DATE
108 , x_start_date_active DATE
109 , x_end_date_active DATE
110 ) IS
111 BEGIN
112 UPDATE as_card_rule_qual_values
113 SET
114 qual_value_id=DECODE(x_qual_value_id,FND_API.G_MISS_NUM,NULL,x_qual_value_id)
115 , last_update_date=DECODE(x_last_update_date,FND_API.G_MISS_DATE,TO_DATE(NULL),x_last_update_date)
116 , last_updated_by=DECODE(x_last_updated_by,FND_API.G_MISS_NUM,NULL,x_last_updated_by)
117 , last_update_login=DECODE(x_last_update_login,FND_API.G_MISS_NUM,NULL,x_last_update_login)
118 , scorecard_id=DECODE(x_scorecard_id,FND_API.G_MISS_NUM,NULL,x_scorecard_id)
119 , score=DECODE(x_score,FND_API.G_MISS_NUM,NULL,x_score)
120 , card_rule_id=DECODE(x_card_rule_id,FND_API.G_MISS_NUM,NULL,x_card_rule_id)
121 , seed_qual_id=DECODE(x_seed_qual_id,FND_API.G_MISS_NUM,NULL,x_seed_qual_id)
122 , high_value_number=DECODE(x_high_value_number,FND_API.G_MISS_NUM,NULL,x_high_value_number)
123 , low_value_number=DECODE(x_low_value_number,FND_API.G_MISS_NUM,NULL,x_low_value_number)
124 , high_value_char=DECODE(x_high_value_char,FND_API.G_MISS_CHAR,NULL,x_high_value_char)
125 , low_value_char=DECODE(x_low_value_char,FND_API.G_MISS_CHAR,NULL,x_low_value_char)
126 , currency_code=DECODE(x_currency_code,FND_API.G_MISS_CHAR,NULL,x_currency_code)
127 , low_value_date=DECODE(x_low_value_date,FND_API.G_MISS_DATE,TO_DATE(NULL),x_low_value_date)
128 , high_value_date=DECODE(x_high_value_date,FND_API.G_MISS_DATE,TO_DATE(NULL),x_high_value_date)
129 , start_date_active=DECODE(x_start_date_active,FND_API.G_MISS_DATE,TO_DATE(NULL),x_start_date_active)
130 , end_date_active=DECODE(x_end_date_active,FND_API.G_MISS_DATE,TO_DATE(NULL),x_end_date_active)
131 WHERE ROWID = x_rowid;
132 IF (SQL%NOTFOUND) THEN
133 RAISE NO_DATA_FOUND;
134 END IF;
135 END update_row;
136 PROCEDURE update_row(
137 x_qual_value_id NUMBER
138 , x_last_update_date DATE
139 , x_last_updated_by NUMBER
140 , x_last_update_login NUMBER
141 , x_scorecard_id NUMBER
142 , x_score NUMBER
143 , x_card_rule_id NUMBER
144 , x_seed_qual_id NUMBER
145 , x_high_value_number NUMBER
146 , x_low_value_number NUMBER
147 , x_high_value_char VARCHAR2
148 , x_low_value_char VARCHAR2
149 , x_currency_code VARCHAR2
150 , x_low_value_date DATE
151 , x_high_value_date DATE
152 , x_start_date_active DATE
153 , x_end_date_active DATE
154 ) IS
155 BEGIN
156 UPDATE as_card_rule_qual_values
157 SET
158 qual_value_id=DECODE(x_qual_value_id,FND_API.G_MISS_NUM,NULL,x_qual_value_id)
159 , last_update_date=DECODE(x_last_update_date,FND_API.G_MISS_DATE,TO_DATE(NULL),x_last_update_date)
160 , last_updated_by=DECODE(x_last_updated_by,FND_API.G_MISS_NUM,NULL,x_last_updated_by)
161 , last_update_login=DECODE(x_last_update_login,FND_API.G_MISS_NUM,NULL,x_last_update_login)
162 , scorecard_id=DECODE(x_scorecard_id,FND_API.G_MISS_NUM,NULL,x_scorecard_id)
163 , score=DECODE(x_score,FND_API.G_MISS_NUM,NULL,x_score)
164 , card_rule_id=DECODE(x_card_rule_id,FND_API.G_MISS_NUM,NULL,x_card_rule_id)
165 , seed_qual_id=DECODE(x_seed_qual_id,FND_API.G_MISS_NUM,NULL,x_seed_qual_id)
166 , high_value_number=DECODE(x_high_value_number,FND_API.G_MISS_NUM,NULL,x_high_value_number)
167 , low_value_number=DECODE(x_low_value_number,FND_API.G_MISS_NUM,NULL,x_low_value_number)
168 , high_value_char=DECODE(x_high_value_char,FND_API.G_MISS_CHAR,NULL,x_high_value_char)
169 , low_value_char=DECODE(x_low_value_char,FND_API.G_MISS_CHAR,NULL,x_low_value_char)
170 , currency_code=DECODE(x_currency_code,FND_API.G_MISS_CHAR,NULL,x_currency_code)
171 , low_value_date=DECODE(x_low_value_date,FND_API.G_MISS_DATE,TO_DATE(NULL),x_low_value_date)
172 , high_value_date=DECODE(x_high_value_date,FND_API.G_MISS_DATE,TO_DATE(NULL),x_high_value_date)
173 , start_date_active=DECODE(x_start_date_active,FND_API.G_MISS_DATE,TO_DATE(NULL),x_start_date_active)
174 , end_date_active=DECODE(x_end_date_active,FND_API.G_MISS_DATE,TO_DATE(NULL),x_end_date_active)
175 WHERE qual_value_id = x_qual_value_id;
176 IF (SQL%NOTFOUND) THEN
177 RAISE NO_DATA_FOUND;
178 END IF;
179 END update_row;
180
181
182 PROCEDURE lock_row(
183 x_rowid VARCHAR2
184 , x_qual_value_id NUMBER
185 , x_last_update_date DATE
186 , x_last_updated_by NUMBER
187 , x_creation_date DATE
188 , x_created_by NUMBER
189 , x_last_update_login NUMBER
190 , x_scorecard_id NUMBER
191 , x_score NUMBER
192 , x_card_rule_id NUMBER
193 , x_seed_qual_id NUMBER
194 , x_high_value_number NUMBER
195 , x_low_value_number NUMBER
196 , x_high_value_char VARCHAR2
197 , x_low_value_char VARCHAR2
198 , x_currency_code VARCHAR2
199 , x_low_value_date DATE
200 , x_high_value_date DATE
201 , x_start_date_active DATE
202 , x_end_date_active DATE
203 ) IS
204 CURSOR l_lock IS
205 SELECT *
206 FROM as_card_rule_qual_values
207 WHERE rowid = x_rowid
208 FOR UPDATE OF qual_value_id NOWAIT;
209 l_table_rec l_lock%ROWTYPE;
210 BEGIN
211 OPEN l_lock;
212 FETCH l_lock INTO l_table_rec;
213 IF (l_lock%NOTFOUND) THEN
214 CLOSE l_lock;
215 FND_MESSAGE.SET_NAME('FND','FORM_RECORD_DELETED');
216 APP_EXCEPTION.RAISE_EXCEPTION;
217 END IF;
218 CLOSE l_lock;
219 IF (
220 ((l_table_rec.qual_value_id = x_qual_value_id)
221 OR ((l_table_rec.qual_value_id IS NULL)
222 AND ( x_qual_value_id IS NULL)))
223 AND ((l_table_rec.last_update_date = x_last_update_date)
224 OR ((l_table_rec.last_update_date IS NULL)
225 AND ( x_last_update_date IS NULL)))
226 AND ((l_table_rec.last_updated_by = x_last_updated_by)
227 OR ((l_table_rec.last_updated_by IS NULL)
228 AND ( x_last_updated_by IS NULL)))
229 AND ((l_table_rec.creation_date = x_creation_date)
230 OR ((l_table_rec.creation_date IS NULL)
231 AND ( x_creation_date IS NULL)))
232 AND ((l_table_rec.created_by = x_created_by)
233 OR ((l_table_rec.created_by IS NULL)
234 AND ( x_created_by IS NULL)))
235 AND ((l_table_rec.last_update_login = x_last_update_login)
236 OR ((l_table_rec.last_update_login IS NULL)
237 AND ( x_last_update_login IS NULL)))
238 AND ((l_table_rec.scorecard_id = x_scorecard_id)
239 OR ((l_table_rec.scorecard_id IS NULL)
240 AND ( x_scorecard_id IS NULL)))
241 AND ((l_table_rec.score = x_score)
242 OR ((l_table_rec.score IS NULL)
243 AND ( x_score IS NULL)))
244 AND ((l_table_rec.card_rule_id = x_card_rule_id)
245 OR ((l_table_rec.card_rule_id IS NULL)
246 AND ( x_card_rule_id IS NULL)))
247 AND ((l_table_rec.seed_qual_id = x_seed_qual_id)
248 OR ((l_table_rec.seed_qual_id IS NULL)
249 AND ( x_seed_qual_id IS NULL)))
250 AND ((l_table_rec.high_value_number = x_high_value_number)
251 OR ((l_table_rec.high_value_number IS NULL)
252 AND ( x_high_value_number IS NULL)))
253 AND ((l_table_rec.low_value_number = x_low_value_number)
254 OR ((l_table_rec.low_value_number IS NULL)
255 AND ( x_low_value_number IS NULL)))
256 AND ((l_table_rec.high_value_char = x_high_value_char)
257 OR ((l_table_rec.high_value_char IS NULL)
258 AND ( x_high_value_char IS NULL)))
262 AND ((l_table_rec.currency_code = x_currency_code)
259 AND ((l_table_rec.low_value_char = x_low_value_char)
260 OR ((l_table_rec.low_value_char IS NULL)
261 AND ( x_low_value_char IS NULL)))
263 OR ((l_table_rec.currency_code IS NULL)
264 AND ( x_currency_code IS NULL)))
265 AND ((l_table_rec.low_value_date = x_low_value_date)
266 OR ((l_table_rec.low_value_date IS NULL)
267 AND ( x_low_value_date IS NULL)))
268 AND ((l_table_rec.high_value_date = x_high_value_date)
269 OR ((l_table_rec.high_value_date IS NULL)
270 AND ( x_high_value_date IS NULL)))
271 AND ((l_table_rec.start_date_active = x_start_date_active)
272 OR ((l_table_rec.start_date_active IS NULL)
273 AND ( x_start_date_active IS NULL)))
274 AND ((l_Table_rec.end_date_active = x_end_date_active)
275 OR ((l_table_rec.end_date_active IS NULL)
276 AND ( x_end_date_active IS NULL)))
277 ) THEN
278 RETURN;
279 ELSE
280 FND_MESSAGE.SET_NAME('FND','FORM_RECORD_DELETED');
281 APP_EXCEPTION.RAISE_EXCEPTION;
282 END IF;
283 END lock_row;
284
285
286 PROCEDURE load_row(
287 x_qual_value_id NUMBER
288 , x_scorecard_id NUMBER
289 , x_score NUMBER
290 , x_card_rule_id NUMBER
291 , x_seed_qual_id NUMBER
292 , x_high_value_number NUMBER
293 , x_low_value_number NUMBER
294 , x_high_value_char VARCHAR2
295 , x_low_value_char VARCHAR2
296 , x_currency_code VARCHAR2
297 , x_low_value_date DATE
298 , x_high_value_date DATE
299 , x_start_date_active DATE
300 , x_end_date_active DATE
301 , x_owner VARCHAR2
302 )
303 IS
304 user_id number := 0;
305 row_id varchar2(64);
306
307 CURSOR c_get_last_updated (c_qual_value_id NUMBER) IS
308 SELECT last_updated_by
309 FROM as_card_rule_qual_values
310 WHERE qual_value_id = c_qual_value_id;
311 l_last_updated_by NUMBER;
312 l_rowid varchar2(64);
313
314 BEGIN
315 -- If last_updated_by is not 1, means this record has been updated by
316 -- customer, we should not overwrite it.
317 OPEN c_get_last_updated (x_qual_value_id );
318 FETCH c_get_last_updated INTO l_last_updated_by;
319 CLOSE c_get_last_updated;
320
321 IF nvl(l_last_updated_by, 1) = 1
322 THEN
323 if (X_OWNER = 'SEED') then
324 user_id := 1;
325 end if;
326
327 Update_Row(
328 x_qual_value_id => x_qual_value_id
329 , x_last_update_date => SYSDATE
330 , x_last_updated_by => user_id
331 , x_last_update_login => 0
332 , x_scorecard_id => x_scorecard_id
333 , x_score => x_score
334 , x_card_rule_id => x_card_rule_id
335 , x_seed_qual_id => x_seed_qual_id
336 , x_high_value_number => x_high_value_number
337 , x_low_value_number => x_low_value_number
338 , x_high_value_char => x_high_value_char
339 , x_low_value_char => x_low_value_char
340 , x_currency_code => x_currency_code
341 , x_low_value_date => x_low_value_date
342 , x_high_value_date => x_high_value_date
343 , x_start_date_active => x_start_date_active
344 , x_end_date_active => x_end_date_active
345 );
346
347
348 END IF;
349
350 EXCEPTION
351 when no_data_found then
352 Insert_Row(
353 x_rowid => l_rowid
354 , x_qual_value_id => x_qual_value_id
355 , x_last_update_date => SYSDATE
356 , x_last_updated_by => user_id
357 , x_creation_date => sysdate
358 , x_created_by => 0
359 , x_last_update_login => 0
360 , x_scorecard_id => x_scorecard_id
361 , x_score => x_score
362 , x_card_rule_id => x_card_rule_id
363 , x_seed_qual_id => x_seed_qual_id
364 , x_high_value_number => x_high_value_number
365 , x_low_value_number => x_low_value_number
366 , x_high_value_char => x_high_value_char
367 , x_low_value_char => x_low_value_char
368 , x_currency_code => x_currency_code
369 , x_low_value_date => x_low_value_date
370 , x_high_value_date => x_high_value_date
371 , x_start_date_active => x_start_date_active
372 , x_end_date_active => x_end_date_active
373 );
374
375 END load_row;
376
377 END as_card_rule_qual_values_pkg;