[Home] [Help]
PACKAGE BODY: APPS.OZF_THRESHOLD_RULES_PKG
Source
1 PACKAGE BODY OZF_THRESHOLD_RULES_PKG as
2 /* $Header: ozfttrub.pls 115.1 2003/11/28 12:27:14 pkarthik noship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- OZF_THRESHOLD_RULES_PKG
7 -- Purpose
8 --
9 -- History
10 --
11 -- NOTE
12 --
13 -- End of Comments
14 -- ===============================================================
15
16
17 G_PKG_NAME CONSTANT VARCHAR2(30):= 'OZF_THRESHOLD_RULES_PKG';
18 G_FILE_NAME CONSTANT VARCHAR2(12) := 'ozfttrub.pls';
19
20
21 ----------------------------------------------------------
22 ---- MEDIA ----
23 ----------------------------------------------------------
24
25 -- ========================================================
26 --
27 -- NAME
28 -- createInsertBody
29 --
30 -- PURPOSE
31 --
32 -- NOTES
33 --
34 -- HISTORY
35 --
36 -- ========================================================
37 PROCEDURE Insert_Row(
38 px_threshold_rule_id IN OUT NOCOPY NUMBER,
39 p_last_update_date DATE,
40 p_last_updated_by NUMBER,
41 p_last_update_login NUMBER,
42 p_creation_date DATE,
43 p_created_by NUMBER,
44 p_created_from VARCHAR2,
45 p_request_id NUMBER,
46 p_program_application_id NUMBER,
47 p_program_id NUMBER,
48 p_program_update_date DATE,
49 p_period_type VARCHAR2,
50 p_enabled_flag VARCHAR2,
51 p_threshold_calendar VARCHAR2,
52 p_start_period_name VARCHAR2,
53 p_end_period_name VARCHAR2,
54 p_threshold_id NUMBER,
55 p_start_date DATE,
56 p_end_date DATE,
57 p_value_limit VARCHAR2,
58 p_operator_code VARCHAR2,
59 p_percent_amount NUMBER,
60 p_base_line VARCHAR2,
61 p_error_mode VARCHAR2,
62 p_repeat_frequency NUMBER,
63 p_frequency_period VARCHAR2,
64 p_attribute_category VARCHAR2,
65 p_attribute1 VARCHAR2,
66 p_attribute2 VARCHAR2,
67 p_attribute3 VARCHAR2,
68 p_attribute4 VARCHAR2,
69 p_attribute5 VARCHAR2,
70 p_attribute6 VARCHAR2,
71 p_attribute7 VARCHAR2,
72 p_attribute8 VARCHAR2,
73 p_attribute9 VARCHAR2,
74 p_attribute10 VARCHAR2,
75 p_attribute11 VARCHAR2,
76 p_attribute12 VARCHAR2,
77 p_attribute13 VARCHAR2,
78 p_attribute14 VARCHAR2,
79 p_attribute15 VARCHAR2,
80 p_org_id NUMBER,
81 p_security_group_id NUMBER,
82 p_converted_days NUMBER,
83 px_object_version_number IN OUT NOCOPY NUMBER,
84 p_comparison_type VARCHAR2,
85 p_alert_type VARCHAR2
86 )
87
88 IS
89 x_rowid VARCHAR2(30);
90
91
92 BEGIN
93 /*
94 IF (p_org_id IS NULL OR p_org_id = FND_API.G_MISS_NUM) THEN
95 SELECT NVL(SUBSTRB(USERENV('CLIENT_INFO'),1,10),-99)
96 INTO p_org_id
97 FROM DUAL;
98 END IF;
99 */
100
101 px_object_version_number := 1;
102
103
104 INSERT INTO OZF_THRESHOLD_RULES_ALL(
105 threshold_rule_id,
106 last_update_date,
107 last_updated_by,
108 last_update_login,
109 creation_date,
110 created_by,
111 created_from,
112 request_id,
113 program_application_id,
114 program_id,
115 program_update_date,
116 period_type,
117 enabled_flag,
118 threshold_calendar,
119 start_period_name,
120 end_period_name,
121 threshold_id,
122 start_date,
123 end_date,
124 value_limit,
125 operator_code,
126 percent_amount,
127 base_line,
128 error_mode,
129 repeat_frequency,
130 frequency_period,
131 attribute_category,
132 attribute1,
133 attribute2,
134 attribute3,
135 attribute4,
136 attribute5,
137 attribute6,
138 attribute7,
139 attribute8,
140 attribute9,
141 attribute10,
142 attribute11,
143 attribute12,
144 attribute13,
145 attribute14,
146 attribute15,
147 org_id,
148 security_group_id,
149 converted_days,
150 object_version_number,
151 comparison_type,
152 alert_type
153 ) VALUES (
154 px_threshold_rule_id,
155 p_last_update_date,
156 p_last_updated_by,
157 p_last_update_login,
158 p_creation_date,
159 p_created_by,
160 p_created_from,
161 p_request_id,
162 p_program_application_id,
163 p_program_id,
164 p_program_update_date,
165 p_period_type,
166 p_enabled_flag,
167 p_threshold_calendar,
168 p_start_period_name,
169 p_end_period_name,
170 p_threshold_id,
171 p_start_date,
172 p_end_date,
173 p_value_limit,
174 p_operator_code,
175 p_percent_amount,
176 p_base_line,
177 p_error_mode,
178 p_repeat_frequency,
179 p_frequency_period,
180 p_attribute_category,
181 p_attribute1,
182 p_attribute2,
183 p_attribute3,
184 p_attribute4,
185 p_attribute5,
186 p_attribute6,
187 p_attribute7,
188 p_attribute8,
189 p_attribute9,
190 p_attribute10,
191 p_attribute11,
192 p_attribute12,
193 p_attribute13,
194 p_attribute14,
195 p_attribute15,
196 p_org_id,
197 p_security_group_id,
198 p_converted_days,
199 px_object_version_number,
200 p_comparison_type,
201 p_alert_type);
202 END Insert_Row;
203
204
205 ----------------------------------------------------------
206 ---- MEDIA ----
207 ----------------------------------------------------------
208
209 -- ========================================================
210 --
211 -- NAME
212 -- createUpdateBody
213 --
214 -- PURPOSE
215 --
216 -- NOTES
217 --
218 -- HISTORY
219 --
220 -- ========================================================
221 PROCEDURE Update_Row(
222 p_threshold_rule_id NUMBER,
223 p_last_update_date DATE,
224 p_last_updated_by NUMBER,
225 p_last_update_login NUMBER,
226 p_created_from VARCHAR2,
227 p_request_id NUMBER,
228 p_program_application_id NUMBER,
229 p_program_id NUMBER,
230 p_program_update_date DATE,
231 p_period_type VARCHAR2,
232 p_enabled_flag VARCHAR2,
233 p_threshold_calendar VARCHAR2,
234 p_start_period_name VARCHAR2,
235 p_end_period_name VARCHAR2,
236 p_threshold_id NUMBER,
237 p_start_date DATE,
238 p_end_date DATE,
239 p_value_limit VARCHAR2,
240 p_operator_code VARCHAR2,
241 p_percent_amount NUMBER,
242 p_base_line VARCHAR2,
243 p_error_mode VARCHAR2,
244 p_repeat_frequency NUMBER,
245 p_frequency_period VARCHAR2,
246 p_attribute_category VARCHAR2,
247 p_attribute1 VARCHAR2,
248 p_attribute2 VARCHAR2,
249 p_attribute3 VARCHAR2,
250 p_attribute4 VARCHAR2,
251 p_attribute5 VARCHAR2,
252 p_attribute6 VARCHAR2,
253 p_attribute7 VARCHAR2,
254 p_attribute8 VARCHAR2,
255 p_attribute9 VARCHAR2,
256 p_attribute10 VARCHAR2,
257 p_attribute11 VARCHAR2,
258 p_attribute12 VARCHAR2,
259 p_attribute13 VARCHAR2,
260 p_attribute14 VARCHAR2,
261 p_attribute15 VARCHAR2,
262 p_org_id NUMBER,
263 p_security_group_id NUMBER,
264 p_converted_days NUMBER,
265 px_object_version_number IN OUT NOCOPY NUMBER,
266 p_comparison_type VARCHAR2,
267 p_alert_type VARCHAR2
268 )
269
270 IS
271 BEGIN
272 Update OZF_THRESHOLD_RULES_ALL
273 SET
274 threshold_rule_id = p_threshold_rule_id,
275 last_update_date = p_last_update_date,
276 last_updated_by = p_last_updated_by,
277 last_update_login = p_last_update_login,
278 created_from = p_created_from,
279 request_id = p_request_id,
280 program_application_id = p_program_application_id,
281 program_id = p_program_id,
282 program_update_date = p_program_update_date,
283 period_type = p_period_type,
284 enabled_flag = p_enabled_flag,
285 threshold_calendar = p_threshold_calendar,
286 start_period_name = p_start_period_name,
287 end_period_name = p_end_period_name,
288 threshold_id = p_threshold_id,
289 start_date = p_start_date,
290 end_date = p_end_date,
291 value_limit = p_value_limit,
292 operator_code = p_operator_code,
293 percent_amount = p_percent_amount,
294 base_line = p_base_line,
295 error_mode = p_error_mode,
296 repeat_frequency = p_repeat_frequency,
297 frequency_period = p_frequency_period,
298 attribute_category = p_attribute_category,
299 attribute1 = p_attribute1,
300 attribute2 = p_attribute2,
301 attribute3 = p_attribute3,
302 attribute4 = p_attribute4,
303 attribute5 = p_attribute5,
304 attribute6 = p_attribute6,
305 attribute7 = p_attribute7,
306 attribute8 = p_attribute8,
307 attribute9 = p_attribute9,
308 attribute10 = p_attribute10,
309 attribute11 = p_attribute11,
310 attribute12 = p_attribute12,
311 attribute13 = p_attribute13,
312 attribute14 = p_attribute14,
313 attribute15 = p_attribute15,
314 --org_id = p_org_id,
315 security_group_id = p_security_group_id,
316 converted_days = p_converted_days,
317 object_version_number = DECODE( px_object_version_number, FND_API.g_miss_num, object_version_number + 1, px_object_version_number + 1),
318 comparison_type = p_comparison_type,
319 alert_type = p_alert_type
320 WHERE THRESHOLD_RULE_ID = p_THRESHOLD_RULE_ID
321 AND object_version_number = px_object_version_number;
322
323 IF (SQL%NOTFOUND) THEN
324 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
325 END IF;
326
327 px_object_version_number := px_object_version_number + 1;
328
329 END Update_Row;
330
331
332 ----------------------------------------------------------
333 ---- MEDIA ----
334 ----------------------------------------------------------
335
336 -- ========================================================
337 --
338 -- NAME
339 -- createDeleteBody
340 --
341 -- PURPOSE
342 --
343 -- NOTES
344 --
345 -- HISTORY
346 --
347 -- ========================================================
348 PROCEDURE Delete_Row(
349 p_THRESHOLD_RULE_ID NUMBER)
350 IS
351 BEGIN
352 DELETE FROM OZF_THRESHOLD_RULES_ALL
353 WHERE THRESHOLD_RULE_ID = p_THRESHOLD_RULE_ID;
354 If (SQL%NOTFOUND) then
355 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
356 End If;
357 END Delete_Row ;
358
359
360
361 ----------------------------------------------------------
362 ---- MEDIA ----
363 ----------------------------------------------------------
364
365 -- ========================================================
366 --
367 -- NAME
368 -- createLockBody
369 --
370 -- PURPOSE
371 --
372 -- NOTES
373 --
374 -- HISTORY
375 --
376 -- ========================================================
377 PROCEDURE Lock_Row(
378 p_threshold_rule_id NUMBER,
379 p_last_update_date DATE,
380 p_last_updated_by NUMBER,
381 p_last_update_login NUMBER,
382 p_creation_date DATE,
383 p_created_by NUMBER,
384 p_created_from VARCHAR2,
385 p_request_id NUMBER,
386 p_program_application_id NUMBER,
387 p_program_id NUMBER,
388 p_program_update_date DATE,
389 p_period_type VARCHAR2,
390 p_enabled_flag VARCHAR2,
391 p_threshold_calendar VARCHAR2,
392 p_start_period_name VARCHAR2,
393 p_end_period_name VARCHAR2,
394 p_threshold_id NUMBER,
395 p_start_date DATE,
396 p_end_date DATE,
397 p_value_limit VARCHAR2,
398 p_operator_code VARCHAR2,
399 p_percent_amount NUMBER,
400 p_base_line VARCHAR2,
401 p_error_mode VARCHAR2,
402 p_repeat_frequency NUMBER,
403 p_frequency_period VARCHAR2,
404 p_attribute_category VARCHAR2,
405 p_attribute1 VARCHAR2,
406 p_attribute2 VARCHAR2,
407 p_attribute3 VARCHAR2,
408 p_attribute4 VARCHAR2,
409 p_attribute5 VARCHAR2,
410 p_attribute6 VARCHAR2,
411 p_attribute7 VARCHAR2,
412 p_attribute8 VARCHAR2,
413 p_attribute9 VARCHAR2,
414 p_attribute10 VARCHAR2,
415 p_attribute11 VARCHAR2,
416 p_attribute12 VARCHAR2,
417 p_attribute13 VARCHAR2,
418 p_attribute14 VARCHAR2,
419 p_attribute15 VARCHAR2,
420 p_org_id NUMBER,
421 p_security_group_id NUMBER,
422 p_converted_days NUMBER,
423 p_object_version_number NUMBER)
424
425 IS
426 CURSOR C IS
427 SELECT *
428 FROM OZF_THRESHOLD_RULES_ALL
429 WHERE THRESHOLD_RULE_ID = p_THRESHOLD_RULE_ID
430 FOR UPDATE of THRESHOLD_RULE_ID NOWAIT;
431 Recinfo C%ROWTYPE;
432 BEGIN
433 OPEN c;
434 FETCH c INTO Recinfo;
435 If (c%NOTFOUND) then
436 CLOSE c;
437 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
438 APP_EXCEPTION.RAISE_EXCEPTION;
439 END IF;
440 CLOSE C;
441 IF (
442 ( Recinfo.threshold_rule_id = p_threshold_rule_id)
443 AND ( ( Recinfo.last_update_date = p_last_update_date)
444 OR ( ( Recinfo.last_update_date IS NULL )
445 AND ( p_last_update_date IS NULL )))
446 AND ( ( Recinfo.last_updated_by = p_last_updated_by)
447 OR ( ( Recinfo.last_updated_by IS NULL )
448 AND ( p_last_updated_by IS NULL )))
449 AND ( ( Recinfo.last_update_login = p_last_update_login)
450 OR ( ( Recinfo.last_update_login IS NULL )
451 AND ( p_last_update_login IS NULL )))
452 AND ( ( Recinfo.creation_date = p_creation_date)
453 OR ( ( Recinfo.creation_date IS NULL )
454 AND ( p_creation_date IS NULL )))
455 AND ( ( Recinfo.created_by = p_created_by)
456 OR ( ( Recinfo.created_by IS NULL )
457 AND ( p_created_by IS NULL )))
458 AND ( ( Recinfo.created_from = p_created_from)
459 OR ( ( Recinfo.created_from IS NULL )
460 AND ( p_created_from IS NULL )))
461 AND ( ( Recinfo.request_id = p_request_id)
462 OR ( ( Recinfo.request_id IS NULL )
463 AND ( p_request_id IS NULL )))
464 AND ( ( Recinfo.program_application_id = p_program_application_id)
465 OR ( ( Recinfo.program_application_id IS NULL )
466 AND ( p_program_application_id IS NULL )))
467 AND ( ( Recinfo.program_id = p_program_id)
468 OR ( ( Recinfo.program_id IS NULL )
469 AND ( p_program_id IS NULL )))
470 AND ( ( Recinfo.program_update_date = p_program_update_date)
471 OR ( ( Recinfo.program_update_date IS NULL )
472 AND ( p_program_update_date IS NULL )))
473 AND ( ( Recinfo.period_type = p_period_type)
474 OR ( ( Recinfo.period_type IS NULL )
475 AND ( p_period_type IS NULL )))
476 AND ( ( Recinfo.enabled_flag = p_enabled_flag)
477 OR ( ( Recinfo.enabled_flag IS NULL )
478 AND ( p_enabled_flag IS NULL )))
479 AND ( ( Recinfo.threshold_calendar = p_threshold_calendar)
480 OR ( ( Recinfo.threshold_calendar IS NULL )
481 AND ( p_threshold_calendar IS NULL )))
482 AND ( ( Recinfo.start_period_name = p_start_period_name)
483 OR ( ( Recinfo.start_period_name IS NULL )
484 AND ( p_start_period_name IS NULL )))
485 AND ( ( Recinfo.end_period_name = p_end_period_name)
486 OR ( ( Recinfo.end_period_name IS NULL )
487 AND ( p_end_period_name IS NULL )))
488 AND ( ( Recinfo.threshold_id = p_threshold_id)
489 OR ( ( Recinfo.threshold_id IS NULL )
490 AND ( p_threshold_id IS NULL )))
491 AND ( ( Recinfo.start_date = p_start_date)
492 OR ( ( Recinfo.start_date IS NULL )
493 AND ( p_start_date IS NULL )))
494 AND ( ( Recinfo.end_date = p_end_date)
495 OR ( ( Recinfo.end_date IS NULL )
496 AND ( p_end_date IS NULL )))
497 AND ( ( Recinfo.value_limit = p_value_limit)
498 OR ( ( Recinfo.value_limit IS NULL )
499 AND ( p_value_limit IS NULL )))
500 AND ( ( Recinfo.operator_code = p_operator_code)
501 OR ( ( Recinfo.operator_code IS NULL )
502 AND ( p_operator_code IS NULL )))
503 AND ( ( Recinfo.percent_amount = p_percent_amount)
504 OR ( ( Recinfo.percent_amount IS NULL )
505 AND ( p_percent_amount IS NULL )))
506 AND ( ( Recinfo.base_line = p_base_line)
507 OR ( ( Recinfo.base_line IS NULL )
508 AND ( p_base_line IS NULL )))
509 AND ( ( Recinfo.error_mode = p_error_mode)
510 OR ( ( Recinfo.error_mode IS NULL )
511 AND ( p_error_mode IS NULL )))
512 AND ( ( Recinfo.repeat_frequency = p_repeat_frequency)
513 OR ( ( Recinfo.repeat_frequency IS NULL )
514 AND ( p_repeat_frequency IS NULL )))
515 AND ( ( Recinfo.frequency_period = p_frequency_period)
516 OR ( ( Recinfo.frequency_period IS NULL )
517 AND ( p_frequency_period IS NULL )))
518 AND ( ( Recinfo.attribute_category = p_attribute_category)
519 OR ( ( Recinfo.attribute_category IS NULL )
520 AND ( p_attribute_category IS NULL )))
521 AND ( ( Recinfo.attribute1 = p_attribute1)
522 OR ( ( Recinfo.attribute1 IS NULL )
523 AND ( p_attribute1 IS NULL )))
524 AND ( ( Recinfo.attribute2 = p_attribute2)
525 OR ( ( Recinfo.attribute2 IS NULL )
526 AND ( p_attribute2 IS NULL )))
527 AND ( ( Recinfo.attribute3 = p_attribute3)
528 OR ( ( Recinfo.attribute3 IS NULL )
529 AND ( p_attribute3 IS NULL )))
530 AND ( ( Recinfo.attribute4 = p_attribute4)
531 OR ( ( Recinfo.attribute4 IS NULL )
532 AND ( p_attribute4 IS NULL )))
533 AND ( ( Recinfo.attribute5 = p_attribute5)
534 OR ( ( Recinfo.attribute5 IS NULL )
535 AND ( p_attribute5 IS NULL )))
536 AND ( ( Recinfo.attribute6 = p_attribute6)
537 OR ( ( Recinfo.attribute6 IS NULL )
538 AND ( p_attribute6 IS NULL )))
539 AND ( ( Recinfo.attribute7 = p_attribute7)
540 OR ( ( Recinfo.attribute7 IS NULL )
541 AND ( p_attribute7 IS NULL )))
542 AND ( ( Recinfo.attribute8 = p_attribute8)
543 OR ( ( Recinfo.attribute8 IS NULL )
544 AND ( p_attribute8 IS NULL )))
545 AND ( ( Recinfo.attribute9 = p_attribute9)
546 OR ( ( Recinfo.attribute9 IS NULL )
547 AND ( p_attribute9 IS NULL )))
548 AND ( ( Recinfo.attribute10 = p_attribute10)
549 OR ( ( Recinfo.attribute10 IS NULL )
550 AND ( p_attribute10 IS NULL )))
551 AND ( ( Recinfo.attribute11 = p_attribute11)
552 OR ( ( Recinfo.attribute11 IS NULL )
553 AND ( p_attribute11 IS NULL )))
554 AND ( ( Recinfo.attribute12 = p_attribute12)
555 OR ( ( Recinfo.attribute12 IS NULL )
556 AND ( p_attribute12 IS NULL )))
557 AND ( ( Recinfo.attribute13 = p_attribute13)
558 OR ( ( Recinfo.attribute13 IS NULL )
559 AND ( p_attribute13 IS NULL )))
560 AND ( ( Recinfo.attribute14 = p_attribute14)
561 OR ( ( Recinfo.attribute14 IS NULL )
562 AND ( p_attribute14 IS NULL )))
563 AND ( ( Recinfo.attribute15 = p_attribute15)
564 OR ( ( Recinfo.attribute15 IS NULL )
565 AND ( p_attribute15 IS NULL )))
566 AND ( ( Recinfo.org_id = p_org_id)
567 OR ( ( Recinfo.org_id IS NULL )
568 AND ( p_org_id IS NULL )))
569 AND ( ( Recinfo.security_group_id = p_security_group_id)
570 OR ( ( Recinfo.security_group_id IS NULL )
571 AND ( p_security_group_id IS NULL )))
572 AND ( ( Recinfo.converted_days = p_converted_days)
573 OR ( ( Recinfo.converted_days IS NULL )
574 AND ( p_converted_days IS NULL )))
575 AND ( ( Recinfo.object_version_number = p_object_version_number)
576 OR ( ( Recinfo.object_version_number IS NULL )
577 AND ( p_object_version_number IS NULL )))
578 ) THEN
579 RETURN;
580 ELSE
581 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
582 APP_EXCEPTION.RAISE_EXCEPTION;
583 END IF;
584 END Lock_Row;
585
586 END OZF_THRESHOLD_RULES_PKG;