[Home] [Help]
PACKAGE BODY: APPS.ARP_SELECTION_CRITERIA_PKG
Source
1 PACKAGE BODY ARP_SELECTION_CRITERIA_PKG AS
2 /* $Header: ARBRSELB.pls 120.5 2005/10/30 03:49:01 appldev ship $ */
3
4 /*--------------------------------------------------------+
5 | Dummy constants for use in update and lock operations |
6 +--------------------------------------------------------*/
7
8 AR_TEXT_DUMMY CONSTANT VARCHAR2(10) := '~~!@#$*&^';
9 AR_NUMBER_DUMMY CONSTANT NUMBER(15) := -999999999999999;
10 AR_DATE_DUMMY CONSTANT DATE := to_date(1, 'J');
11
12
13 /*-------------------------------------+
14 | WHO column values from ARP_GLOBAL |
15 +-------------------------------------*/
16
17 pg_request_id number;
18 pg_program_application_id number;
19 pg_program_id number;
20 pg_program_update_date date;
21 pg_last_updated_by number;
22 pg_last_update_date date;
23 pg_last_update_login number;
24 pg_set_of_books_id number;
25
26
27
28 /*===========================================================================+
29 | PROCEDURE |
30 | display_selection_rec |
31 | |
32 | DESCRIPTION |
33 | Displays the values of all columns except creation_date and |
34 | last_update_date. |
35 | |
36 | SCOPE - PRIVATE |
37 | |
38 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
39 | arp_util.debug |
40 | |
41 | ARGUMENTS : IN: |
42 | p_sel_rec |
43 | OUT: |
44 | None |
45 | IN/ OUT: |
46 | None |
47 | |
48 | RETURNS : NONE |
49 | |
50 | NOTES |
51 | |
52 | MODIFICATION HISTORY |
53 | 17-APR-2000 Tien Tran Created |
54 | |
55 | |
56 +===========================================================================*/
57
58 PROCEDURE display_selection_rec( p_sel_rec IN ar_selection_criteria%rowtype )
59
60 IS
61
62 BEGIN
63
64 arp_util.debug('arp_selection_criteria_pkg.display_selection_rec()+');
65
66 arp_util.debug('************** Dump of ar_selection_criteria record **************');
67 arp_util.debug('selection_criteria_id: ' || p_sel_rec.selection_criteria_id);
68 arp_util.debug('last_updated_by: ' || p_sel_rec.last_updated_by);
69 arp_util.debug('created_by: ' || p_sel_rec.created_by);
70 arp_util.debug('last_update_login: ' || p_sel_rec.last_update_login);
71 arp_util.debug('due_date_low: ' || p_sel_rec.due_date_low);
72 arp_util.debug('due_date_high: ' || p_sel_rec.due_date_high);
73 arp_util.debug('trx_date_low: ' || p_sel_rec.trx_date_low);
74 arp_util.debug('trx_date_high: ' || p_sel_rec.trx_date_high);
75 arp_util.debug('cust_trx_type_id: ' || p_sel_rec.cust_trx_type_id);
76 arp_util.debug('receipt_method_id: ' || p_sel_rec.receipt_method_id);
77 arp_util.debug('bank_branch_id: ' || p_sel_rec.bank_branch_id);
78 arp_util.debug('trx_number_low: ' || p_sel_rec.trx_number_low);
79 arp_util.debug('trx_number_high: ' || p_sel_rec.trx_number_high);
80 arp_util.debug('customer_class_code: ' || p_sel_rec.customer_class_code);
81 arp_util.debug('customer_category_code: ' || p_sel_rec.customer_category_code);
82 arp_util.debug('customer_id : ' || p_sel_rec.customer_id);
83 arp_util.debug('site_use_id: ' || p_sel_rec.site_use_id);
84
85 arp_util.debug('************** End ar_selection_criteria record **************');
86
87 arp_util.debug('arp_selection_criteria_pkg.display_selection_rec()-');
88
89 EXCEPTION
90 WHEN OTHERS THEN
91 arp_util.debug('EXCEPTION: arp_selection_criteria_pkg.display_selection_rec()');
92 RAISE;
93
94 END;
95
96
97 /*===========================================================================+
98 | PROCEDURE |
99 | display_selection |
100 | |
101 | DESCRIPTION |
102 | Selects and displays the values of all columns except creation_date |
103 | and last_update_date. |
104 | |
105 | SCOPE - PRIVATE |
106 | |
107 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
108 | arp_util.debug |
109 | |
110 | ARGUMENTS : IN: |
111 | p_sel_id |
112 | OUT: |
113 | None |
114 | IN/ OUT: |
115 | None |
116 | |
117 | RETURNS : NONE |
118 | |
119 | NOTES |
120 | |
121 | MODIFICATION HISTORY |
122 | 17-APR-2000 Tien Tran Created |
123 | |
124 +===========================================================================*/
125
126 PROCEDURE display_selection ( p_sel_id IN ar_selection_criteria.selection_criteria_id%TYPE)
127
128 IS
129
130 l_sel_rec ar_selection_criteria%rowtype;
131
132 BEGIN
133
134 arp_util.debug('arp_selection_criteria_pkg.display_selection()+');
135
136 ARP_SELECTION_CRITERIA_PKG.fetch_p(l_sel_rec, p_sel_id);
137
138 ARP_SELECTION_CRITERIA_PKG.display_selection_rec (l_sel_rec);
139
140 arp_util.debug('arp_selection_criteria_pkg.display_selection()-');
141
142 EXCEPTION
143 WHEN OTHERS THEN
144 arp_util.debug('EXCEPTION: arp_selection_criteria_pkg.display_selection()');
145 RAISE;
146
147 END;
148
149
150 /*===========================================================================+
151 | PROCEDURE |
152 | set_to_dummy |
153 | |
154 | DESCRIPTION |
155 | This procedure initializes all columns in the parameter selection |
156 | record to the appropriate dummy value for its datatype. |
157 | |
158 | The dummy values are defined in the following package level constants: |
159 | AR_TEXT_DUMMY |
160 | AR_NUMBER_DUMMY |
161 | AR_DATE_DUMMY |
162 | |
163 | SCOPE - PUBLIC |
164 | |
165 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
166 | arp_util.debug |
167 | |
168 | ARGUMENTS : IN: |
169 | None |
170 | OUT: |
171 | p_sel_rec - The record to initialize |
172 | |
173 | RETURNS : NONE |
174 | |
175 | NOTES |
176 | |
177 | MODIFICATION HISTORY |
178 | 17-APR-2000 Tien Tran Created |
179 | |
180 | |
181 +===========================================================================*/
182
183 PROCEDURE set_to_dummy( p_sel_rec OUT NOCOPY ar_selection_criteria%rowtype) IS
184
185 BEGIN
186
187 arp_util.debug('ARP_SELECTION_CRITERIA_PKG.set_to_dummy()+');
188
189 p_sel_rec.selection_criteria_id := AR_NUMBER_DUMMY;
190 p_sel_rec.last_update_date := AR_DATE_DUMMY;
191 p_sel_rec.last_updated_by := AR_NUMBER_DUMMY;
192 p_sel_rec.creation_date := AR_DATE_DUMMY;
193 p_sel_rec.created_by := AR_NUMBER_DUMMY;
194 p_sel_rec.last_update_login := AR_NUMBER_DUMMY;
195 p_sel_rec.due_date_low := AR_DATE_DUMMY;
196 p_sel_rec.due_date_high := AR_DATE_DUMMY;
197 p_sel_rec.trx_date_low := AR_DATE_DUMMY;
198 p_sel_rec.trx_date_high := AR_DATE_DUMMY;
199 p_sel_rec.cust_trx_type_id := AR_NUMBER_DUMMY;
200 p_sel_rec.receipt_method_id := AR_NUMBER_DUMMY;
201 p_sel_rec.bank_branch_id := AR_NUMBER_DUMMY;
202 p_sel_rec.trx_number_low := AR_TEXT_DUMMY;
203 p_sel_rec.trx_number_high := AR_TEXT_DUMMY;
204 p_sel_rec.customer_class_code := AR_TEXT_DUMMY;
205 p_sel_rec.customer_category_code := AR_TEXT_DUMMY;
206 p_sel_rec.customer_id := AR_NUMBER_DUMMY;
207 p_sel_rec.site_use_id := AR_NUMBER_DUMMY;
208
209 arp_util.debug('ARP_SELECTION_CRITERIA_PKG.set_to_dummy()-');
210
211 EXCEPTION
212 WHEN OTHERS THEN
213 arp_util.debug('EXCEPTION: ARP_SELECTION_CRITERIA_PKG.set_to_dummy()');
214 RAISE;
215
216 END;
217
218
219 /*===========================================================================+
220 | PROCEDURE |
221 | fetch_p |
222 | |
223 | DESCRIPTION |
224 | This procedure fetches a single row from ar_selection_criteria into a |
225 | variable specified as a parameter based on the table's primary key, |
226 | selection_criteria_id. |
227 | |
228 | SCOPE - PUBLIC |
229 | |
230 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
231 | arp_util.debug |
232 | |
233 | ARGUMENTS : IN: |
234 | p_sel_id - identifies the record to fetch |
235 | OUT: |
236 | p_sel_rec - contains the fetched record |
237 | |
238 | RETURNS : NONE |
239 | |
240 | NOTES |
241 | |
242 | MODIFICATION HISTORY |
243 | 17-APR-2000 Tien Tran Created |
244 | |
245 +===========================================================================*/
246
247 PROCEDURE fetch_p( p_sel_rec OUT NOCOPY ar_selection_criteria%rowtype ,
248 p_sel_id IN ar_selection_criteria.selection_criteria_id%TYPE ) IS
249
250 BEGIN
251 arp_util.debug('ARP_SELECTION_CRITERIA_PKG.fetch_p()+');
252
253 SELECT *
254 INTO p_sel_rec
255 FROM ar_selection_criteria
256 WHERE selection_criteria_id = p_sel_id;
257
258 arp_util.debug('ARP_SELECTION_CRITERIA_PKG.fetch_p()-');
259
260 EXCEPTION
261 WHEN OTHERS THEN
262 arp_util.debug('EXCEPTION: ARP_SELECTION_CRITERIA_PKG.fetch_p' );
263 RAISE;
264 END;
265
266
267
268 /*===========================================================================+
269 | PROCEDURE |
270 | lock_p |
271 | |
272 | DESCRIPTION |
273 | This procedure locks the ar_selection_criteria row identified by the |
274 | p_selection_criteria_id parameter. |
275 | |
276 | SCOPE - PUBLIC |
277 | |
278 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
279 | arp_util.debug |
280 | |
281 | ARGUMENTS : IN: |
282 | p_sel_id - identifies the row to lock |
283 | OUT: |
284 | None |
285 | |
286 | RETURNS : NONE |
287 | |
288 | NOTES |
289 | |
290 | MODIFICATION HISTORY |
291 | 17-APR-2000 Tien Tran Created |
292 | |
293 +===========================================================================*/
294
295 PROCEDURE lock_p( p_sel_id IN ar_selection_criteria.selection_criteria_id%TYPE )
296
297 IS
298
299 l_sel_id ar_selection_criteria.selection_criteria_id%TYPE;
300
301 BEGIN
302 arp_util.debug('ARP_SELECTION_CRITERIA_PKG.lock_p()+');
303
304 SELECT selection_criteria_id
305 INTO l_sel_id
306 FROM ar_selection_criteria
307 WHERE selection_criteria_id = p_sel_id
308 FOR UPDATE OF selection_criteria_id NOWAIT;
309
310 arp_util.debug('ARP_SELECTION_CRITERIA_PKG.lock_p()-');
311
312 EXCEPTION
313 WHEN OTHERS THEN
314 arp_util.debug( 'EXCEPTION: ARP_SELECTION_CRITERIA_PKG.lock_p' );
315 RAISE;
316 END;
317
318
319 /*===========================================================================+
320 | PROCEDURE |
321 | lock_fetch_p |
322 | |
323 | DESCRIPTION |
324 | This procedure locks the ar_selection_criteria row identified by the |
325 | p_sel_id parameter and populates the p_sel_rec parameter |
326 | with the row that was locked. |
327 | |
328 | SCOPE - PUBLIC |
329 | |
330 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
331 | arp_util.debug |
332 | |
333 | ARGUMENTS : IN: |
334 | p_sel_id - identifies the row to lock |
335 | OUT: |
336 | p_sel_rec - contains the locked row |
337 | |
338 | RETURNS : NONE |
339 | |
340 | NOTES |
341 | |
342 | MODIFICATION HISTORY |
343 | 17-APR-2000 Tien Tran Created |
344 | |
345 +===========================================================================*/
346
347 PROCEDURE lock_fetch_p( p_sel_rec IN OUT NOCOPY ar_selection_criteria%rowtype ,
348 p_sel_id IN ar_selection_criteria.selection_criteria_id%TYPE )
349 IS
350
351 BEGIN
352 arp_util.debug('ARP_SELECTION_CRITERIA_PKG.lock_fetch_p()+');
353
354 SELECT *
355 INTO p_sel_rec
356 FROM ar_selection_criteria
357 WHERE selection_criteria_id = p_sel_id
358 FOR UPDATE OF selection_criteria_id NOWAIT;
359
360 arp_util.debug('ARP_SELECTION_CRITERIA_PKG.lock_fetch_p()-');
361
362 EXCEPTION
363 WHEN OTHERS THEN
364 arp_util.debug( 'EXCEPTION: ARP_SELECTION_CRITERIA_PKG.lock_fetch_p' );
365 RAISE;
366 END;
367
368
369 /*===========================================================================+
370 | PROCEDURE |
371 | lock_compare_p |
372 | |
373 | DESCRIPTION |
374 | This procedure locks the ar_selection_criteria row identified by the |
375 | p_sel_id parameter only if no columns in that row have |
376 | changed from when they were first selected in the form |
377 | |
378 | SCOPE - PUBLIC |
379 | |
380 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
381 | arp_util.debug |
382 | |
383 | ARGUMENTS : IN: |
384 | p_sel_id - identifies the row to lock |
385 | p_sel_rec - selection record for comparison |
386 | OUT: |
387 | None |
388 | |
389 | RETURNS : NONE |
390 | |
391 | NOTES |
392 | |
393 | MODIFICATION HISTORY |
394 | 17-APR-2000 Tien Tran Created |
395 | |
396 | |
397 +===========================================================================*/
398
399 PROCEDURE lock_compare_p( p_sel_rec IN ar_selection_criteria%rowtype ,
400 p_sel_id IN ar_selection_criteria.selection_criteria_id%TYPE )
401 IS
402
403 l_new_sel_rec ar_selection_criteria%rowtype;
404
405 BEGIN
406 arp_util.debug('ARP_SELECTION_CRITERIA_PKG.lock_compare_p()+');
407
408 SELECT *
409 INTO l_new_sel_rec
410 FROM ar_selection_criteria tsel
411 WHERE tsel.selection_criteria_id = p_sel_id
412 AND NOT
413
414 (
415
416 NVL (tsel.selection_criteria_id, AR_NUMBER_DUMMY) <>
417 NVL (
418 DECODE( p_sel_rec.selection_criteria_id,
419 AR_NUMBER_DUMMY, tsel.selection_criteria_id,
420 p_sel_rec.selection_criteria_id ),
421 AR_NUMBER_DUMMY
422 )
423 OR
424 NVL (trunc(tsel.due_date_low), AR_DATE_DUMMY) <>
425 NVL (
426 DECODE( p_sel_rec.due_date_low,
427 AR_DATE_DUMMY, trunc(tsel.due_date_low),
428 p_sel_rec.due_date_low ),
429 AR_DATE_DUMMY
430 )
431 OR
432 NVL (trunc(tsel.due_date_high), AR_DATE_DUMMY) <>
433 NVL (
434 DECODE( p_sel_rec.due_date_high,
435 AR_DATE_DUMMY, trunc(tsel.due_date_high),
436 p_sel_rec.due_date_high ),
437 AR_DATE_DUMMY
438 )
439 OR
440 NVL (trunc(tsel.trx_date_low), AR_DATE_DUMMY) <>
441 NVL (
442 DECODE( p_sel_rec.trx_date_low,
443 AR_DATE_DUMMY, trunc(tsel.trx_date_low),
444 p_sel_rec.trx_date_low ),
445 AR_DATE_DUMMY
446 )
447 OR
448 NVL (trunc(tsel.trx_date_high), AR_DATE_DUMMY) <>
449 NVL (
450 DECODE( p_sel_rec.trx_date_high,
451 AR_DATE_DUMMY, trunc(tsel.trx_date_high),
452 p_sel_rec.trx_date_high ),
453 AR_DATE_DUMMY
454 )
455 OR
456 NVL (tsel.cust_trx_type_id, AR_NUMBER_DUMMY) <>
457 NVL (
458 DECODE( p_sel_rec.cust_trx_type_id,
459 AR_NUMBER_DUMMY, tsel.cust_trx_type_id,
460 p_sel_rec.cust_trx_type_id ),
461 AR_NUMBER_DUMMY
462 )
463 OR
464 NVL (tsel.receipt_method_id, AR_NUMBER_DUMMY) <>
465 NVL (
466 DECODE( p_sel_rec.receipt_method_id,
467 AR_NUMBER_DUMMY, tsel.receipt_method_id,
468 p_sel_rec.receipt_method_id ),
469 AR_NUMBER_DUMMY
470 )
471 OR
472 NVL (tsel.bank_branch_id, AR_NUMBER_DUMMY) <>
473 NVL (
474 DECODE( p_sel_rec.bank_branch_id,
475 AR_NUMBER_DUMMY, tsel.bank_branch_id,
476 p_sel_rec.bank_branch_id ),
477 AR_NUMBER_DUMMY
478 )
479 OR
480 NVL (tsel.trx_number_low, AR_TEXT_DUMMY) <>
481 NVL (
482 DECODE( p_sel_rec.trx_number_low,
483 AR_TEXT_DUMMY, tsel.trx_number_low,
484 p_sel_rec.trx_number_low ),
485 AR_TEXT_DUMMY
486 )
487 OR
488 NVL (tsel.trx_number_high, AR_TEXT_DUMMY) <>
489 NVL (
490 DECODE( p_sel_rec.trx_number_high,
491 AR_TEXT_DUMMY, tsel.trx_number_high,
492 p_sel_rec.trx_number_high ),
493 AR_TEXT_DUMMY
494 )
495 OR
496 NVL (tsel.customer_class_code, AR_TEXT_DUMMY) <>
497 NVL (
498 DECODE( p_sel_rec.customer_class_code,
499 AR_TEXT_DUMMY, tsel.customer_class_code,
500 p_sel_rec.customer_class_code ),
501 AR_TEXT_DUMMY
502 )
503 OR
504 NVL (tsel.customer_category_code, AR_TEXT_DUMMY) <>
505 NVL (
506 DECODE( p_sel_rec.customer_category_code,
507 AR_TEXT_DUMMY, tsel.customer_category_code,
508 p_sel_rec.customer_category_code ),
509 AR_TEXT_DUMMY
510 )
511 OR
512 NVL (tsel.customer_id, AR_NUMBER_DUMMY) <>
513 NVL (
514 DECODE( p_sel_rec.customer_id,
515 AR_NUMBER_DUMMY, tsel.customer_id,
516 p_sel_rec.customer_id ),
517 AR_NUMBER_DUMMY
518 )
519 OR
520 NVL (tsel.site_use_id, AR_NUMBER_DUMMY) <>
521 NVL (
522 DECODE( p_sel_rec.site_use_id,
523 AR_NUMBER_DUMMY, tsel.site_use_id,
524 p_sel_rec.site_use_id ),
525 AR_NUMBER_DUMMY
526 )
527
528 OR
529 NVL (tsel.last_update_date, AR_DATE_DUMMY) <>
530 NVL (
531 DECODE(p_sel_rec.last_update_date,
532 AR_DATE_DUMMY, tsel.last_update_date,
533 p_sel_rec.last_update_date),
534 AR_DATE_DUMMY
535 )
536 OR
537 NVL(tsel.last_updated_by, AR_NUMBER_DUMMY) <>
538 NVL(
539 DECODE(p_sel_rec.last_updated_by,
540 AR_NUMBER_DUMMY, tsel.last_updated_by,
541 p_sel_rec.last_updated_by),
542 AR_NUMBER_DUMMY
543 )
544 OR
545 NVL(tsel.creation_date, AR_DATE_DUMMY) <>
546 NVL(
547 DECODE(p_sel_rec.creation_date,
548 AR_DATE_DUMMY, tsel.creation_date,
549 p_sel_rec.creation_date),
550 AR_DATE_DUMMY
551 )
552 OR
553 NVL(tsel.created_by, AR_NUMBER_DUMMY) <>
554 NVL(
555 DECODE(p_sel_rec.created_by,
556 AR_NUMBER_DUMMY, tsel.created_by,
557 p_sel_rec.created_by),
558 AR_NUMBER_DUMMY
559 )
560 OR
561 NVL(tsel.last_update_login, AR_NUMBER_DUMMY) <>
562 NVL(
563 DECODE(p_sel_rec.last_update_login,
564 AR_NUMBER_DUMMY, tsel.last_update_login,
565 p_sel_rec.last_update_login),
566 AR_NUMBER_DUMMY
567 )
568
569 )
570 FOR UPDATE OF selection_criteria_id NOWAIT;
571
572 arp_util.debug('ARP_SELECTION_CRITERIA_PKG.lock_compare_p()-');
573
574 EXCEPTION
575 WHEN OTHERS THEN
576 arp_util.debug( 'EXCEPTION: ARP_SELECTION_CRITERIA_PKG.lock_compare_p' );
577 RAISE;
578 END;
579
580
581 /*===========================================================================+
582 | PROCEDURE |
583 | lock_compare_cover |
584 | |
585 | DESCRIPTION |
586 | Cover for calling the selection criteria table handler lock_compare_p |
587 | |
588 | SCOPE - PRIVATE |
589 | |
590 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
591 | arp_util.debug |
592 | |
593 | ARGUMENTS : IN: |
594 | p_form_name |
595 | p_form_version |
596 | p_selection_criteria_id |
597 | p_due_date_low |
598 | p_due_date_high |
599 | p_trx_date_low |
600 | p_trx_date_high |
601 | p_cust_trx_type_id |
602 | p_receipt_method_id |
603 | p_bank_branch_id |
604 | p_trx_number_low |
605 | p_trx_number_high |
606 | p_customer_class_code |
607 | p_customer_category_code |
608 | p_customer_id |
609 | p_site_use_id |
610 | |
611 | OUT: |
612 | None |
613 | IN OUT: |
614 | None |
615 | |
616 | RETURNS : NONE |
617 | |
618 | NOTES |
619 | |
620 | MODIFICATION HISTORY |
621 | 17-APR-2000 Tien Tran Created |
622 | |
623 | |
624 +===========================================================================*/
625
626 PROCEDURE lock_compare_cover(
627 p_form_name IN varchar2 ,
628 p_form_version IN number ,
629 p_selection_criteria_id IN ar_selection_criteria.selection_criteria_id%TYPE ,
630 p_due_date_low IN ar_selection_criteria.due_date_low%TYPE ,
631 p_due_date_high IN ar_selection_criteria.due_date_high%TYPE ,
632 p_trx_date_low IN ar_selection_criteria.trx_date_low%TYPE ,
633 p_trx_date_high IN ar_selection_criteria.trx_date_high%TYPE ,
634 p_cust_trx_type_id IN ar_selection_criteria.cust_trx_type_id%TYPE ,
635 p_receipt_method_id IN ar_selection_criteria.receipt_method_id%TYPE ,
636 p_bank_branch_id IN ar_selection_criteria.bank_branch_id%TYPE ,
637 p_trx_number_low IN ar_selection_criteria.trx_number_low%TYPE ,
638 p_trx_number_high IN ar_selection_criteria.trx_number_high%TYPE ,
639 p_customer_class_code IN ar_selection_criteria.customer_class_code%TYPE ,
640 p_customer_category_code IN ar_selection_criteria.customer_category_code%TYPE ,
641 p_customer_id IN ar_selection_criteria.customer_id%TYPE ,
642 p_site_use_id IN ar_selection_criteria.site_use_id%TYPE )
643
644 IS
645
646 l_sel_rec ar_selection_criteria%rowtype;
647
648 BEGIN
649 arp_util.debug('ARP_SELECTION_CRITERIA_PKG.lock_compare_cover()+');
650
651 ARP_SELECTION_CRITERIA_PKG.set_to_dummy(l_sel_rec);
652
653 l_sel_rec.selection_criteria_id := p_selection_criteria_id ;
654 l_sel_rec.due_date_low := p_due_date_low ;
655 l_sel_rec.due_date_high := p_due_date_high ;
656 l_sel_rec.trx_date_low := p_trx_date_low ;
657 l_sel_rec.trx_date_high := p_trx_date_high ;
658 l_sel_rec.cust_trx_type_id := p_cust_trx_type_id ;
659 l_sel_rec.receipt_method_id := p_receipt_method_id ;
660 l_sel_rec.bank_branch_id := p_bank_branch_id ;
661 l_sel_rec.trx_number_low := p_trx_number_low ;
662 l_sel_rec.trx_number_high := p_trx_number_high ;
663 l_sel_rec.customer_class_code := p_customer_class_code ;
664 l_sel_rec.customer_category_code := p_customer_category_code ;
665 l_sel_rec.customer_id := p_customer_id ;
666 l_sel_rec.site_use_id := p_site_use_id ;
667
668 ARP_SELECTION_CRITERIA_PKG.lock_compare_p(l_sel_rec, p_selection_criteria_id);
669
670 arp_util.debug('ARP_SELECTION_CRITERIA_PKG.lock_compare_cover()-');
671
672 EXCEPTION
673 WHEN OTHERS THEN
674 arp_util.debug('EXCEPTION : ARP_SELECTION_CRITERIA_PKG.lock_compare_cover');
675
676 arp_util.debug('************** Dump of ar_selection_criteria record **************');
677 arp_util.debug('p_selection_criteria_id: ' || p_selection_criteria_id);
678 arp_util.debug('p_due_date_low: ' || p_due_date_low);
679 arp_util.debug('p_due_date_high: ' || p_due_date_high);
680 arp_util.debug('p_trx_date_low: ' || p_trx_date_low);
681 arp_util.debug('p_trx_date_high: ' || p_trx_date_high);
682 arp_util.debug('p_cust_trx_type_id: ' || p_cust_trx_type_id);
683 arp_util.debug('p_receipt_method_id: ' || p_receipt_method_id);
684 arp_util.debug('p_bank_branch_id: ' || p_bank_branch_id);
685 arp_util.debug('p_trx_number_low: ' || p_trx_number_low);
686 arp_util.debug('p_trx_number_high: ' || p_trx_number_high);
687 arp_util.debug('p_customer_class_code: ' || p_customer_class_code);
688 arp_util.debug('p_customer_category_code: ' || p_customer_category_code);
689 arp_util.debug('p_customer_id : ' || p_customer_id);
690 arp_util.debug('p_site_use_id: ' || p_site_use_id);
691
692 arp_util.debug('************** End ar_selection_criteria record **************');
693
694
695 RAISE;
696 END;
697
698 /*===========================================================================+
699 | PROCEDURE |
700 | delete_p |
701 | |
702 | DESCRIPTION |
703 | This procedure deletes the ar_selection_criteria row identified by the |
704 | p_sel_id parameter. |
705 | |
706 | SCOPE - PUBLIC |
707 | |
708 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
709 | arp_util.debug |
710 | |
711 | ARGUMENTS : IN: |
712 | p_sel_id - identifies the row to delete |
713 | OUT: |
714 | None |
715 | |
716 | RETURNS : NONE |
717 | |
718 | NOTES |
719 | |
720 | MODIFICATION HISTORY |
721 | 17-APR-2000 Tien Tran Created |
722 | |
723 +===========================================================================*/
724
725 procedure delete_p( p_sel_id IN ar_selection_criteria.selection_criteria_id%TYPE) IS
726
727 BEGIN
728
729 arp_util.debug('ARP_SELECTION_CRITERIA_PKG.delete_p()+');
730
731 DELETE FROM ar_selection_criteria
732 WHERE selection_criteria_id = p_sel_id;
733
734 arp_util.debug('ARP_SELECTION_CRITERIA_PKG.delete_p()-');
735
736 EXCEPTION
737 WHEN OTHERS THEN
738 arp_util.debug('EXCEPTION: ARP_SELECTION_CRITERIA_PKG.delete_p()');
739 RAISE;
740
741 END;
742
743
744 /*===========================================================================+
745 | PROCEDURE |
746 | update_p |
747 | |
748 | DESCRIPTION |
749 | This procedure updates the ar_selection_criteria row identified by the |
750 | p_sel_id parameter. |
751 | |
752 | SCOPE - PUBLIC |
753 | |
754 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
755 | arp_util.debug |
756 | |
757 | ARGUMENTS : IN: |
758 | p_sel_id - identifies the row to update |
759 | p_sel_rec - contains the new column values |
760 | OUT: |
761 | None |
762 | |
763 | RETURNS : NONE |
764 | |
765 | NOTES |
766 | set_to_dummy must be called before the values in p_sel_rec are |
767 | changed and this function is called. |
768 | |
769 | MODIFICATION HISTORY |
770 | 17-APR-2000 Tien Tran Created |
771 | |
772 +===========================================================================*/
773
774 PROCEDURE update_p( p_sel_rec IN ar_selection_criteria%rowtype,
775 p_sel_id IN ar_selection_criteria.selection_criteria_id%TYPE) IS
776
777
778 BEGIN
779
780 arp_util.debug('ARP_SELECTION_CRITERIA_PKG.update_p()+');
781
782 UPDATE AR_SELECTION_CRITERIA SET
783
784 due_date_low =
785 DECODE( p_sel_rec.due_date_low,
786 AR_DATE_DUMMY, due_date_low,
787 p_sel_rec.due_date_low ),
788
789 due_date_high =
790 DECODE( p_sel_rec.due_date_high,
791 AR_DATE_DUMMY, due_date_high,
792 p_sel_rec.due_date_high ),
793
794 trx_date_low =
795 DECODE( p_sel_rec.trx_date_low,
796 AR_DATE_DUMMY, trx_date_low,
797 p_sel_rec.trx_date_low ),
798
799 trx_date_high =
800 DECODE( p_sel_rec.trx_date_high,
801 AR_DATE_DUMMY, trx_date_high,
802 p_sel_rec.trx_date_high ),
803
804 cust_trx_type_id =
805 DECODE( p_sel_rec.cust_trx_type_id,
806 AR_NUMBER_DUMMY, cust_trx_type_id,
807 p_sel_rec.cust_trx_type_id ),
808
809 receipt_method_id =
810 DECODE( p_sel_rec.receipt_method_id,
811 AR_NUMBER_DUMMY, receipt_method_id,
812 p_sel_rec.receipt_method_id ),
813
814 bank_branch_id =
815 DECODE( p_sel_rec.bank_branch_id,
816 AR_NUMBER_DUMMY, bank_branch_id,
817 p_sel_rec.bank_branch_id ),
818
819 trx_number_low =
820 DECODE( p_sel_rec.trx_number_low,
821 AR_TEXT_DUMMY, trx_number_low,
822 p_sel_rec.trx_number_low ),
823
824 trx_number_high =
825 DECODE( p_sel_rec.trx_number_high,
826 AR_TEXT_DUMMY, trx_number_high,
827 p_sel_rec.trx_number_high ),
828
829 customer_class_code =
830 DECODE( p_sel_rec.customer_class_code,
831 AR_TEXT_DUMMY, customer_class_code,
832 p_sel_rec.customer_class_code ),
833
834 customer_category_code =
835 DECODE( p_sel_rec.customer_category_code,
836 AR_TEXT_DUMMY, customer_category_code,
837 p_sel_rec.customer_category_code ),
838
839 customer_id =
840 DECODE( p_sel_rec.customer_id,
841 AR_NUMBER_DUMMY, customer_id,
842 p_sel_rec.customer_id ),
843
844 site_use_id =
845 DECODE( p_sel_rec.site_use_id,
846 AR_NUMBER_DUMMY, site_use_id,
847 p_sel_rec.site_use_id ),
848
849 last_update_login = pg_last_update_login,
850 last_update_date = pg_last_update_date,
851 last_updated_by = pg_last_updated_by
852
853 WHERE selection_criteria_id = p_sel_id;
854
855
856 arp_util.debug('ARP_SELECTION_CRITERIA_PKG.update_p()-');
857
858
859 EXCEPTION
860 WHEN OTHERS THEN
861 arp_util.debug('EXCEPTION: ARP_SELECTION_CRITERIA_PKG.update_p()');
862 RAISE;
863 END;
864
865
866 /*===========================================================================+
867 | PROCEDURE |
868 | insert_p |
869 | |
870 | DESCRIPTION |
871 | This procedure inserts a row into ar_selection_criteria that contains |
872 | the column values specified in the p_sel_rec parameter. |
873 | |
874 | SCOPE - PUBLIC |
875 | |
876 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
877 | arp_util.debug |
878 | arp_global.set_of_books_id |
879 | |
880 | ARGUMENTS : IN: |
881 | p_sel_rec - contains the new column values |
882 | OUT: |
883 | p_sel_id - unique ID of the new row |
884 | |
885 | RETURNS : NONE |
886 | |
887 | NOTES |
888 | |
889 | MODIFICATION HISTORY |
890 | 06-JUN-95 Charlie Tomberg Created |
891 | |
892 | |
893 +===========================================================================*/
894
895 PROCEDURE insert_p( p_sel_rec IN ar_selection_criteria%rowtype ,
896 p_sel_id OUT NOCOPY ar_selection_criteria.selection_criteria_id%TYPE )
897
898 IS
899 l_sel_id ar_selection_criteria.selection_criteria_id%TYPE;
900
901 BEGIN
902
903 arp_util.debug('ARP_SELECTION_CRITERIA_PKG.insert_p()+');
904
905 p_sel_id := '';
906
907 /*---------------------------*
908 | Get the unique identifier |
909 *---------------------------*/
910
911 SELECT AR_SELECTION_CRITERIA_S.NEXTVAL
912 INTO l_sel_id
913 FROM DUAL;
914
915 /*-------------------*
916 | Insert the record |
917 *-------------------*/
918
919 INSERT INTO ar_selection_criteria
920 (
921 selection_criteria_id,
922 last_update_date,
923 last_updated_by,
924 creation_date,
925 created_by,
926 last_update_login,
927 due_date_low,
928 due_date_high,
929 trx_date_low,
930 trx_date_high,
931 cust_trx_type_id,
932 receipt_method_id,
933 bank_branch_id,
934 trx_number_low,
935 trx_number_high,
936 customer_class_code,
937 customer_category_code,
938 customer_id,
939 site_use_id
940 )
941 VALUES
942 (
943 l_sel_id, /* selection_criteria_id */
944 sysdate, /* last_update_date */
945 arp_standard.profile.user_id, /* last_updated_by */
946 sysdate, /* creation_date */
947 arp_standard.profile.user_id, /* created_by */
948 NVL( arp_standard.profile.last_update_login,p_sel_rec.last_update_login ), /* last_update_login */
949 p_sel_rec.due_date_low,
950 p_sel_rec.due_date_high,
951 p_sel_rec.trx_date_low,
952 p_sel_rec.trx_date_high,
953 p_sel_rec.cust_trx_type_id,
954 p_sel_rec.receipt_method_id,
955 p_sel_rec.bank_branch_id,
956 p_sel_rec.trx_number_low,
957 p_sel_rec.trx_number_high,
958 p_sel_rec.customer_class_code,
959 p_sel_rec.customer_category_code,
960 p_sel_rec.customer_id,
961 p_sel_rec.site_use_id
962
963 );
964
965 p_sel_id := l_sel_id;
966
967 arp_util.debug('ARP_SELECTION_CRITERIA_PKG.insert_p()-');
968
969 EXCEPTION
970 WHEN OTHERS THEN
971 arp_util.debug('EXCEPTION: ARP_SELECTION_CRITERIA_PKG.insert_p()');
972 RAISE;
973 END;
974
975
976 /*---------------------------------------------+
977 | Package initialization section. |
978 | Sets WHO column variables for later use. |
979 +---------------------------------------------*/
980
981 BEGIN
982
983 pg_request_id := arp_global.request_id;
984 pg_program_application_id := arp_global.program_application_id;
985 pg_program_id := arp_global.program_id;
986 pg_program_update_date := arp_global.program_update_date;
987 pg_last_updated_by := arp_global.last_updated_by;
988 pg_last_update_date := arp_global.last_update_date;
989 pg_last_update_login := arp_global.last_update_login;
990 pg_set_of_books_id := arp_global.set_of_books_id;
991
992
993
994 END ARP_SELECTION_CRITERIA_PKG;