[Home] [Help]
PACKAGE BODY: APPS.ARP_CR_ICR_PKG
Source
1 PACKAGE BODY ARP_CR_ICR_PKG AS
2 /* $Header: ARRIICRB.pls 120.8 2005/06/14 18:50:07 vcrisost ship $*/
3
4 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
5 --
6 --
7 /*--------------------------------------------------------+
8 | Dummy constants for use in update and lock operations |
9 +--------------------------------------------------------*/
10 --
11 AR_TEXT_DUMMY CONSTANT VARCHAR2(10) := '~~!@#$*&^';
12 AR_FLAG_DUMMY CONSTANT VARCHAR2(10) := '~';
13 AR_NUMBER_DUMMY CONSTANT NUMBER(15) := -999999999999999;
14 AR_DATE_DUMMY CONSTANT DATE := to_date(1, 'J');
15 --
16 /*---------------------------------------------------------------+
17 | Package global variable to hold the parsed update cursor. |
18 | This allows the cursors to be reused without being reparsed. |
19 +---------------------------------------------------------------*/
20 --
21 pg_cursor1 integer := '';
22 --
23 /*-------------------------------------+
24 | WHO column values from ARP_GLOBAL |
25 +-------------------------------------*/
26 --
27 pg_request_id number;
28 pg_program_application_id number;
29 pg_program_id number;
30 pg_program_update_date date;
31 pg_last_updated_by number;
32 pg_last_update_date date;
33 pg_last_update_login number;
34 pg_set_of_books_id number;
35 --
36 --
37 /*===========================================================================+
38 | PROCEDURE |
39 | insert_p |
40 | |
41 | DESCRIPTION |
42 | This function inserts a row into AR_INTERIM_CASH_RECEIPTS |
43 | |
44 | SCOPE - PUBLIC |
45 | |
46 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
47 | |
48 | ARGUMENTS : IN: |
49 | p_icr_rec - ICR Record structure |
50 | OUT: |
51 | p_icr_id - ICR Id of inserted ICR row |
52 | p_row_id - Row Id of inserted ICR row |
53 | |
54 | RETURNS : NONE |
55 | |
56 | NOTES - This is an overloaded procedure |
57 | |
58 | MODIFICATION HISTORY - Created by Ganesh Vaidee - 04/25/95 |
59 | |
60 +===========================================================================*/
61 PROCEDURE insert_p( p_row_id OUT NOCOPY VARCHAR2,
62 p_cr_id OUT NOCOPY ar_interim_cash_receipts.cash_receipt_id%TYPE,
63 p_icr_rec IN ar_interim_cash_receipts%ROWTYPE ) IS
64 l_cr_id ar_interim_cash_receipts.cash_receipt_id%TYPE;
65 l_row_id VARCHAR2( 20 );
66 BEGIN
67 arp_standard.debug( 'arp_cr_icr_pkg.insert_p()+' );
68 --
69 arp_cr_icr_pkg.insert_p( p_icr_rec, l_cr_id );
70 --
71 SELECT ROWID
72 INTO l_row_id
73 FROM ar_interim_cash_receipts
74 WHERE cash_receipt_id = l_cr_id;
75 --
76 p_cr_id := l_cr_id;
77 p_row_id := l_row_id;
78 --
79 arp_standard.debug( 'arp_cr_icr_pkg.insert_p()-' );
80 --
81 EXCEPTION
82 WHEN OTHERS THEN
83 arp_standard.debug( 'EXCEPTION: arp_cr_icr_pkg.insert_p' );
84 RAISE;
85 END;
86
87 /*===========================================================================+
88 | PROCEDURE |
89 | insert_p |
90 | |
91 | DESCRIPTION |
92 | This function inserts a row into AR_INTERIM_CASH_RECEIPTS |
93 | |
94 | SCOPE - PUBLIC |
95 | |
96 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
97 | |
98 | ARGUMENTS : IN: |
99 | p_icr_rec - ICR Record structure |
100 | OUT: |
101 | p_.at_id - ICR Id of inserted ICR row |
102 | |
103 | RETURNS : NONE |
104 | |
105 | NOTES - This is an overloaded procedure |
106 | |
107 | MODIFICATION HISTORY - Created by Ganesh Vaidee - 04/25/95 |
108 | |
109 | 10/28/98 K.Murphy Cross Currency Lockbox. |
110 | Added amount_applied and trans_to_receipt_rate |
111 | as created columns. |
112 | 05/01/02 D.Jancis Enh 2074200: Added application_notes |
113 | 12-24-02 K.Dhaliwal Bug 2707190 Added |
114 | application_ref_type,customer_reference and|
115 | customer_reason. |
116 +===========================================================================*/
117 PROCEDURE insert_p( p_icr_rec IN ar_interim_cash_receipts%ROWTYPE,
118 p_icr_id OUT NOCOPY ar_interim_cash_receipts.cash_receipt_id%TYPE ) IS
119 l_icr_id ar_interim_cash_receipts.cash_receipt_id%TYPE;
120 BEGIN
121 arp_standard.debug( '>>>>>>>> arp_cr_icr_pkg.insert_p' );
122 --
123 SELECT ar_cash_receipts_s.nextval
124 INTO l_icr_id
125 FROM dual;
126 --
127 INSERT INTO ar_interim_cash_receipts (
128 cash_receipt_id,
129 amount,
130 amount_applied,
131 trans_to_receipt_rate,
132 factor_discount_amount,
133 created_by,
134 creation_date,
135 currency_code,
136 last_updated_by,
137 last_update_date,
138 receipt_method_id,
139 remit_bank_acct_use_id,
140 batch_id,
141 comments,
142 customer_trx_id,
143 exchange_date,
144 exchange_rate,
145 exchange_rate_type,
146 gl_date,
147 gl_posted_date,
148 anticipated_clearing_date,
149 last_update_login,
150 payment_schedule_id,
151 pay_from_customer,
152 program_application_id,
153 program_id,
154 program_update_date,
155 receipt_date,
156 receipt_number,
157 request_id,
158 site_use_id,
159 special_type,
160 status,
161 type,
162 ussgl_transaction_code,
163 attribute_category,
164 attribute1,
165 attribute2,
166 attribute3,
167 attribute4,
168 attribute5,
169 attribute6,
170 attribute7,
171 attribute8,
172 attribute9,
173 attribute10,
174 attribute11,
175 attribute12,
176 attribute13,
177 attribute14,
178 attribute15,
179 ussgl_transaction_code_context,
180 customer_bank_account_id,
181 customer_bank_branch_id,
182 doc_sequence_id,
183 doc_sequence_value,
184 application_notes,
185 application_ref_type,
186 customer_reference,
187 customer_reason,
188 org_id
189 )
190 VALUES (
191 l_icr_id,
192 p_icr_rec.amount,
193 p_icr_rec.amount_applied,
194 p_icr_rec.trans_to_receipt_rate,
195 p_icr_rec.factor_discount_amount,
196 arp_global.created_by,
197 arp_global.creation_date,
198 p_icr_rec.currency_code,
199 arp_global.last_updated_by,
200 arp_global.last_update_date,
201 p_icr_rec.receipt_method_id,
202 p_icr_rec.remit_bank_acct_use_id,
203 p_icr_rec.batch_id,
204 p_icr_rec.comments,
205 p_icr_rec.customer_trx_id,
206 p_icr_rec.exchange_date,
207 p_icr_rec.exchange_rate,
208 p_icr_rec.exchange_rate_type,
209 p_icr_rec.gl_date,
210 p_icr_rec.gl_posted_date,
211 p_icr_rec.anticipated_clearing_date,
212 arp_global.last_update_login,
213 p_icr_rec.payment_schedule_id,
214 p_icr_rec.pay_from_customer,
215 arp_global.program_application_id,
216 arp_global.program_id,
217 arp_global.program_update_date,
218 p_icr_rec.receipt_date,
219 p_icr_rec.receipt_number,
220 p_icr_rec.request_id,
221 p_icr_rec.site_use_id,
222 p_icr_rec.special_type,
223 p_icr_rec.status,
224 p_icr_rec.type,
225 p_icr_rec.ussgl_transaction_code,
226 p_icr_rec.attribute_category,
227 p_icr_rec.attribute1,
228 p_icr_rec.attribute2,
229 p_icr_rec.attribute3,
230 p_icr_rec.attribute4,
231 p_icr_rec.attribute5,
232 p_icr_rec.attribute6,
233 p_icr_rec.attribute7,
234 p_icr_rec.attribute8,
235 p_icr_rec.attribute9,
236 p_icr_rec.attribute10,
237 p_icr_rec.attribute11,
238 p_icr_rec.attribute12,
239 p_icr_rec.attribute13,
240 p_icr_rec.attribute14,
241 p_icr_rec.attribute15,
242 p_icr_rec.ussgl_transaction_code_context,
243 p_icr_rec.customer_bank_account_id,
244 p_icr_rec.customer_bank_branch_id,
245 p_icr_rec.doc_sequence_id,
246 p_icr_rec.doc_sequence_value,
247 p_icr_rec.application_notes,
248 p_icr_rec.application_ref_type,
249 p_icr_rec.customer_reference,
250 p_icr_rec.customer_reason,
251 arp_standard.sysparm.org_id
252 );
253 p_icr_id := l_icr_id;
254 --
255 arp_standard.debug( '<<<<<<<< arp_cr_icr_pkg.insert_p' );
256 EXCEPTION
257 WHEN OTHERS THEN
258 arp_standard.debug( 'EXCEPTION: arp_cr_icr_pkg.insert_p' );
259 RAISE;
260 END;
261 --
262 /*===========================================================================+
263 | PROCEDURE |
264 | const_icr_update_stmt |
265 | |
266 | DESCRIPTION |
267 | |
268 | SCOPE - PUBLIC |
269 | |
270 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
271 | |
272 | ARGUMENTS : IN: |
273 | OUT: |
274 | |
275 | RETURNS : NONE |
276 | |
277 | |
278 | MODIFICATION HISTORY - Created by Ganesh Vaidee - 04/25/95 |
279 | 10/28/98 K.Murphy Cross Currency Lockbox. |
280 | Added amount_applied and trans_to_receipt_rate |
281 | as updated columns. |
282 | 05/01/02 D.Jancis Enh 2074220: added application_notes |
283 | |
284 | 12-24-02 K.Dhaliwal Bug 2707190 Added |
285 | application_ref_type,customer_reference and|
286 | customer_reason. |
287 +===========================================================================*/
288 --
289 PROCEDURE construct_icr_update_stmt( update_text OUT NOCOPY varchar2) IS
290 --
291 BEGIN
292 arp_standard.debug('arp_cr_icr_pkg.construct_icr_update_stmt()+');
293 --
294 update_text :=
295 'update ar_interim_cash_receipts
296 SET amount =
297 decode(:amount,
298 :ar_number_dummy, amount,
299 :amount),
300 amount_applied =
301 decode(:amount_applied,
302 :ar_number_dummy, amount_applied,
303 :amount_applied),
304 trans_to_receipt_rate =
305 decode(:trans_to_receipt_rate,
306 :ar_number_dummy, trans_to_receipt_rate,
307 :trans_to_receipt_rate),
308 factor_discount_amount =
309 decode(:factor_discount_amount,
310 :ar_number_dummy, factor_discount_amount,
311 :factor_discount_amount),
312 anticipated_clearing_date =
313 decode(:anticipated_clearing_date,
314 :ar_date_dummy, anticipated_clearing_date,
315 :anticipated_clearing_date),
316 customer_bank_branch_id =
317 decode(:customer_bank_branch_id,
318 :ar_number_dummy, customer_bank_branch_id,
319 :customer_bank_branch_id),
320 currency_code =
321 decode(:currency_code,
322 :ar_text_dummy, currency_code,
323 :currency_code),
324 last_updated_by = :pg_last_updated_by,
325 last_update_date = :pg_last_update_date,
326 receipt_method_id =
327 decode(:receipt_method_id,
328 :ar_number_dummy, receipt_method_id,
329 :receipt_method_id),
330 remit_bank_acct_use_id =
331 decode(:remit_bank_acct_use_id,
332 :ar_number_dummy, remit_bank_acct_use_id,
333 :remit_bank_acct_use_id),
334 batch_id =
335 decode(:batch_id,
336 :ar_number_dummy, batch_id,
337 :batch_id),
338 comments =
339 decode(:comments,
340 :ar_text_dummy, comments,
344 :ar_number_dummy, customer_trx_id,
341 :comments),
342 customer_trx_id =
343 decode(:customer_trx_id,
345 :customer_trx_id),
346 exchange_date =
347 decode(:exchange_date,
348 :ar_date_dummy, exchange_date,
349 :exchange_date),
350 exchange_rate =
351 decode(:exchange_rate,
352 :ar_number_dummy, exchange_rate,
353 :exchange_rate),
354 exchange_rate_type =
355 decode(:exchange_rate_type,
356 :ar_text_dummy, exchange_rate_type,
357 :exchange_rate_type),
358 gl_date =
359 decode(:gl_date,
360 :ar_date_dummy, gl_date,
361 :gl_date),
362 gl_posted_date =
363 decode(:gl_posted_date,
364 :ar_date_dummy, gl_posted_date,
365 :gl_posted_date),
366 last_update_login = :pg_last_update_login,
367
368 payment_schedule_id =
369 decode(:payment_schedule_id,
370 :ar_number_dummy, payment_schedule_id,
371 :payment_schedule_id),
372 pay_from_customer =
373 decode(:pay_from_customer,
374 :ar_number_dummy, pay_from_customer,
375 :pay_from_customer),
376 program_application_id =
377 NVL( :pg_program_application_id,
378 program_application_id),
379 program_id =
380 NVL( :pg_program_id,
381 program_id),
382 program_update_date =
383 NVL( :pg_program_update_date,
384 program_update_date),
385 receipt_date =
386 decode(:receipt_date,
387 :ar_date_dummy, receipt_date,
388 :receipt_date),
389 receipt_number =
390 decode(:receipt_number,
391 :ar_text_dummy, receipt_number,
392 :receipt_number),
393 request_id =
394 NVL( :pg_request_id,
395 request_id),
396 site_use_id =
397 decode(:site_use_id,
398 :ar_number_dummy, site_use_id,
399 :site_use_id),
400 special_type =
401 decode(:special_type,
402 :ar_text_dummy, special_type,
403 :special_type ),
404 status =
405 decode(:status,
406 :ar_text_dummy, status,
407 :status),
408 type =
409 decode(:type,
410 :ar_text_dummy, type,
411 :type),
412 ussgl_transaction_code =
413 decode(:ussgl_transaction_code,
414 :ar_text_dummy, ussgl_transaction_code,
415 :ussgl_transaction_code),
416 attribute_category =
417 decode(:attribute_category,
418 :ar_text_dummy, attribute_category,
419 :attribute_category),
420 attribute1 =
421 decode(:attribute1,
422 :ar_text_dummy, attribute1,
423 :attribute1),
424 attribute2 =
425 decode(:attribute2,
426 :ar_text_dummy, attribute2,
427 :attribute2),
428 attribute3 =
429 decode(:attribute3,
430 :ar_text_dummy, attribute3,
431 :attribute3),
432 attribute4 =
433 decode(:attribute4,
434 :ar_text_dummy, attribute4,
435 :attribute4),
436 attribute5 =
437 decode(:attribute5,
438 :ar_text_dummy, attribute5,
439 :attribute5),
440 attribute6 =
441 decode(:attribute6,
442 :ar_text_dummy, attribute6,
443 :attribute6),
444 attribute7 =
445 decode(:attribute7,
446 :ar_text_dummy, attribute7,
447 :attribute7),
448 attribute8 =
449 decode(:attribute8,
450 :ar_text_dummy, attribute8,
451 :attribute8),
452 attribute9 =
453 decode(:attribute9,
454 :ar_text_dummy, attribute9,
455 :attribute9),
456 attribute10 =
457 decode(:attribute10,
458 :ar_text_dummy, attribute10,
462 :ar_text_dummy, attribute11,
459 :attribute10),
460 attribute11 =
461 decode(:attribute11,
463 :attribute11),
464 attribute12 =
465 decode(:attribute12,
466 :ar_text_dummy, attribute12,
467 :attribute12),
468 attribute13 =
469 decode(:attribute13,
470 :ar_text_dummy, attribute13,
471 :attribute13),
472 attribute14 =
473 decode(:attribute14,
474 :ar_text_dummy, attribute14,
475 :attribute14),
476 attribute15 =
477 decode(:attribute15,
478 :ar_text_dummy, attribute15,
479 :attribute15),
480 ussgl_transaction_code_context =
481 decode(:ussgl_transaction_code_context,
482 :ar_text_dummy, ussgl_transaction_code_context,
483 :ussgl_transaction_code_context),
484 customer_bank_account_id =
485 decode(:customer_bank_account_id,
486 :ar_number_dummy, customer_bank_account_id,
487 :customer_bank_account_id),
488 doc_sequence_id =
489 decode(:doc_sequence_id,
490 :ar_number_dummy, doc_sequence_id,
491 :doc_sequence_id),
492 doc_sequence_value =
493 decode(:doc_sequence_value,
494 :ar_number_dummy, doc_sequence_value,
495 :doc_sequence_value),
496 application_notes =
497 decode(:application_notes,
498 :ar_text_dummy, application_notes,
499 :application_notes) ,
500
501 application_ref_type =
502 decode(:application_ref_type,
503 :ar_text_dummy, application_ref_type,
504 :application_ref_type),
505 customer_reference =
506 decode(:customer_reference,
507 :ar_text_dummy, customer_reference,
508 :customer_reference),
509
510 customer_reason =
511 decode(:customer_reason,
512 :ar_text_dummy, customer_reason,
513 :customer_reason)
514
515 ';
516 --
517 arp_standard.debug('arp_cr_icr_pkg.construct_icr_update_stmt()-');
518 --
519 EXCEPTION
520 WHEN OTHERS THEN
521 arp_standard.debug('EXCEPTION: arp_cr_icr_pkg .construct_icr_update_stmt()');
522 RAISE;
523 --
524 END;
525 --
526 /*===========================================================================+
527 | PROCEDURE |
528 | bind_icr_variables |
529 | |
530 | DESCRIPTION |
531 | |
532 | SCOPE - PUBLIC |
533 | |
534 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
535 | |
536 | ARGUMENTS : IN: |
537 | OUT: |
538 | |
539 | RETURNS : NONE |
540 | |
541 | |
542 | MODIFICATION HISTORY - Created by Ganesh Vaidee - 04/25/95 |
543 | 10/28/98 K.Murphy Cross Currency Lockbox. |
544 | Added amount_applied and trans_to_receipt_rate |
545 | as updated columns. |
546 | 05/01/02 D.Jancis ENH 2074220: added application_notes |
547 | |
548 | 12-24-02 K.Dhaliwal Bug 2707190 Added |
549 | application_ref_type,customer_reference and|
550 | customer_reason. |
551 +===========================================================================*/
552 --
553 PROCEDURE bind_icr_variables(p_update_cursor IN integer,
554 p_icr_rec IN ar_interim_cash_receipts%rowtype)
555 IS
556 --
557 BEGIN
558 --
559 arp_standard.debug('arp_cr_icr_pkg.bind_icr_variables()+');
560 --
561 --
562 /*------------------+
563 | Dummy constants |
564 +------------------*/
565 --
569 -- dbms_sql.bind_variable(p_update_cursor, ':ar_flag_dummy',
566 dbms_sql.bind_variable(p_update_cursor, ':ar_text_dummy',
567 AR_TEXT_DUMMY);
568 --
570 -- AR_FLAG_DUMMY);
571 --
572 dbms_sql.bind_variable(p_update_cursor, ':ar_number_dummy',
573 AR_NUMBER_DUMMY);
574 --
575 dbms_sql.bind_variable(p_update_cursor, ':ar_date_dummy',
576 AR_DATE_DUMMY);
577 arp_standard.debug('after duummy');
578 --
579 /*------------------+
580 | WHO variables |
581 +------------------*/
582 --
583 dbms_sql.bind_variable(p_update_cursor, ':pg_request_id',
584 pg_request_id);
585 --
586 dbms_sql.bind_variable(p_update_cursor, ':pg_program_application_id',
587 pg_program_application_id);
588 --
589 dbms_sql.bind_variable(p_update_cursor, ':pg_program_id',
590 pg_program_id);
591 --
592 dbms_sql.bind_variable(p_update_cursor, ':pg_program_update_date',
593 pg_program_update_date);
594 --
595 dbms_sql.bind_variable(p_update_cursor, ':pg_last_updated_by',
596 pg_last_updated_by);
597 --
598 dbms_sql.bind_variable(p_update_cursor, ':pg_last_update_date',
599 pg_last_update_date);
600 --
601 dbms_sql.bind_variable(p_update_cursor, ':pg_last_update_login',
602 pg_last_update_login);
603 arp_standard.debug('after who');
604 /*----------------------------------------------+
605 | Bind variables for all columns in the table |
606 +----------------------------------------------*/
607 --
608 --
609 dbms_sql.bind_variable(p_update_cursor, ':amount',
610 p_icr_rec.amount);
611 --
612 dbms_sql.bind_variable(p_update_cursor, ':amount_applied',
613 p_icr_rec.amount_applied);
614 --
615 dbms_sql.bind_variable(p_update_cursor, ':trans_to_receipt_rate',
616 p_icr_rec.trans_to_receipt_rate);
617 --
618 dbms_sql.bind_variable(p_update_cursor, ':factor_discount_amount',
619 p_icr_rec.factor_discount_amount);
620 --
621 dbms_sql.bind_variable(p_update_cursor, ':anticipated_clearing_date',
622 p_icr_rec.anticipated_clearing_date);
623 --
624 dbms_sql.bind_variable(p_update_cursor, ':customer_bank_branch_id',
625 p_icr_rec.customer_bank_branch_id);
626 --
627 dbms_sql.bind_variable(p_update_cursor, ':currency_code',
628 p_icr_rec.currency_code);
629 --
630 dbms_sql.bind_variable(p_update_cursor, ':receipt_method_id',
631 p_icr_rec.receipt_method_id);
632 --
633 dbms_sql.bind_variable(p_update_cursor, ':remit_bank_acct_use_id',
634 p_icr_rec.remit_bank_acct_use_id);
635 --
636 dbms_sql.bind_variable(p_update_cursor, ':batch_id',
637 p_icr_rec.batch_id);
638 --
639 dbms_sql.bind_variable(p_update_cursor, ':comments',
640 p_icr_rec.comments);
641 --
642 dbms_sql.bind_variable(p_update_cursor, ':customer_trx_id',
643 p_icr_rec.customer_trx_id);
644 --
645 dbms_sql.bind_variable(p_update_cursor, ':exchange_date',
646 p_icr_rec.exchange_date);
647 --
648 dbms_sql.bind_variable(p_update_cursor, ':exchange_rate',
649 p_icr_rec.exchange_rate);
650 --
651 dbms_sql.bind_variable(p_update_cursor, ':exchange_rate_type',
652 p_icr_rec.exchange_rate_type);
653 --
654 dbms_sql.bind_variable(p_update_cursor, ':gl_date',
655 p_icr_rec.gl_date);
656 --
657 dbms_sql.bind_variable(p_update_cursor, ':gl_posted_date',
658 p_icr_rec.gl_posted_date);
659 --
660 dbms_sql.bind_variable(p_update_cursor, ':payment_schedule_id',
661 p_icr_rec.payment_schedule_id);
662 --
663 dbms_sql.bind_variable(p_update_cursor, ':pay_from_customer',
664 p_icr_rec.pay_from_customer);
665 --
666 dbms_sql.bind_variable(p_update_cursor, ':receipt_date',
667 p_icr_rec.receipt_date);
668 --
669 dbms_sql.bind_variable(p_update_cursor, ':receipt_number',
670 p_icr_rec.receipt_number);
671 --
672 dbms_sql.bind_variable(p_update_cursor, ':site_use_id',
673 p_icr_rec.site_use_id);
674 --
675 dbms_sql.bind_variable(p_update_cursor, ':special_type',
676 p_icr_rec.special_type);
677 --
678 dbms_sql.bind_variable(p_update_cursor, ':status',
679 p_icr_rec.status);
680 --
681 dbms_sql.bind_variable(p_update_cursor, ':type',
682 p_icr_rec.type);
683 --
684 dbms_sql.bind_variable(p_update_cursor, ':ussgl_transaction_code',
685 p_icr_rec.ussgl_transaction_code);
686 --
687 dbms_sql.bind_variable(p_update_cursor, ':attribute_category',
688 p_icr_rec.attribute_category);
689 --
690 dbms_sql.bind_variable(p_update_cursor, ':attribute1',
691 p_icr_rec.attribute1);
692 --
696 dbms_sql.bind_variable(p_update_cursor, ':attribute3',
693 dbms_sql.bind_variable(p_update_cursor, ':attribute2',
694 p_icr_rec.attribute2);
695 --
697 p_icr_rec.attribute3);
698 --
699 dbms_sql.bind_variable(p_update_cursor, ':attribute4',
700 p_icr_rec.attribute4);
701 --
702 dbms_sql.bind_variable(p_update_cursor, ':attribute5',
703 p_icr_rec.attribute5);
704 --
705 dbms_sql.bind_variable(p_update_cursor, ':attribute6',
706 p_icr_rec.attribute6);
707 --
708 dbms_sql.bind_variable(p_update_cursor, ':attribute7',
709 p_icr_rec.attribute7);
710 --
711 dbms_sql.bind_variable(p_update_cursor, ':attribute8',
712 p_icr_rec.attribute8);
713 --
714 dbms_sql.bind_variable(p_update_cursor, ':attribute9',
715 p_icr_rec.attribute9);
716 --
717 dbms_sql.bind_variable(p_update_cursor, ':attribute10',
718 p_icr_rec.attribute10);
719 --
720 dbms_sql.bind_variable(p_update_cursor, ':attribute11',
721 p_icr_rec.attribute11);
722 --
723 dbms_sql.bind_variable(p_update_cursor, ':attribute12',
724 p_icr_rec.attribute12);
725 --
726 dbms_sql.bind_variable(p_update_cursor, ':attribute13',
727 p_icr_rec.attribute13);
728 --
729 dbms_sql.bind_variable(p_update_cursor, ':attribute14',
730 p_icr_rec.attribute14);
731 --
732 dbms_sql.bind_variable(p_update_cursor, ':attribute15',
733 p_icr_rec.attribute15);
734 --
735 dbms_sql.bind_variable(p_update_cursor, ':ussgl_transaction_code_context',
736 p_icr_rec.ussgl_transaction_code_context);
737 --
738 dbms_sql.bind_variable(p_update_cursor, ':customer_bank_account_id',
739 p_icr_rec.customer_bank_account_id);
740 --
741 dbms_sql.bind_variable(p_update_cursor, ':doc_sequence_id',
742 p_icr_rec.doc_sequence_id);
743 --
744 dbms_sql.bind_variable(p_update_cursor, ':doc_sequence_value',
745 p_icr_rec.doc_sequence_value);
746 --
747 -- enh 2074220
748 dbms_sql.bind_variable(p_update_cursor, ':application_notes',
749 p_icr_rec.application_notes);
750 --
751 -- Deductions Enhancement
752 dbms_sql.bind_variable(p_update_cursor, ':application_ref_type',
753 p_icr_rec.application_ref_type);
754 --
755 dbms_sql.bind_variable(p_update_cursor, ':customer_reference',
756 p_icr_rec.customer_reference);
757 --
758 dbms_sql.bind_variable(p_update_cursor, ':customer_reason',
759 p_icr_rec.customer_reason);
760 --
761
762 arp_standard.debug('arp_cr_icr_pkg.bind_icr_variables()-');
763 --
764 EXCEPTION
765 WHEN OTHERS THEN
766 arp_standard.debug('EXCEPTION: arp_cr_icr_pkg.bind_icr_variables()');
767 arp_standard.debug('Error at character: ' ||
768 to_char(dbms_sql.last_error_position));
769
770 RAISE;
771 --
772 END;
773 --
774 --
775 --
776 PROCEDURE generic_update(p_update_cursor IN OUT NOCOPY integer,
777 p_where_clause IN varchar2,
778 p_where1 IN number,
779 p_icr_rec IN ar_interim_cash_receipts%ROWTYPE)
780 IS
781 --
782 l_count number;
783 l_update_statement varchar2(20000);
784 --
785 BEGIN
786 arp_standard.debug('arp_cr_icr_pkg.generic_update()+');
787 --
788 /*--------------------------------------------------------------+
789 | If this update statement has not already been parsed, |
790 | construct the statement and parse it. |
791 | Otherwise, use the already parsed statement and rebind its |
792 | variables. |
793 +--------------------------------------------------------------*/
794 --
795 if (p_update_cursor is null)
796 then
797 --
798 p_update_cursor := dbms_sql.open_cursor;
799 --
800 /*---------------------------------+
801 | Construct the update statement |
802 +---------------------------------*/
803 --
804 arp_cr_icr_pkg.construct_icr_update_stmt(l_update_statement);
805 --
806 l_update_statement := l_update_statement || p_where_clause;
807 --
808 arp_standard.debug('after l_update_statement');
809 /*-----------------------------------------------+
810 | Parse, bind, execute and close the statement |
811 +-----------------------------------------------*/
812 --
813 dbms_sql.parse(p_update_cursor,
814 l_update_statement,
815 dbms_sql.v7);
816 --
817 arp_standard.debug('after dbms_sql');
818
819 end if;
820 --
821 arp_cr_icr_pkg.bind_icr_variables(p_update_cursor, p_icr_rec);
822 --
823 --
824 arp_standard.debug('after .bind_app');
825 /*-----------------------------------------+
826 | Bind the variables in the where clause |
830 then
827 +-----------------------------------------*/
828 --
829 if ( p_where1 is not null )
831 dbms_sql.bind_variable(p_update_cursor, ':where_1',
832 p_where1);
833 end if;
834 arp_standard.debug('after bind_variable ');
835 --
836 l_count := dbms_sql.execute(p_update_cursor);
837 --
838 arp_standard.debug( to_char(l_count) || ' rows updated');
839 --
840 --
841 /*------------------------------------------------------------+
842 | Raise the NO_DATA_FOUND exception if no rows were updated |
843 +------------------------------------------------------------*/
844 --
845 if (l_count = 0)
846 then raise NO_DATA_FOUND;
847 arp_standard.debug('after l_count = 0');
848 end if;
849 --
850 --
851 arp_standard.debug('arp_cr_icr_pkg.generic_update()-');
852 --
853 EXCEPTION
854 WHEN OTHERS THEN
855 arp_standard.debug('EXCEPTION: arp_cr_icr_pkg.generic_update()
856 ');
857 arp_standard.debug(l_update_statement);
858 arp_standard.debug('Error at character: ' ||
859 to_char(dbms_sql.last_error_position));
860 --arp_standard.debug('ERROR MESSAGE: ' ||
861 -- sqlerrm);
862 RAISE;
863 END;
864 --
865 --
866 --
867 PROCEDURE set_to_dummy( p_icr_rec OUT NOCOPY ar_interim_cash_receipts%rowtype)
868 IS
869 --
870 BEGIN
871 --
872 arp_standard.debug('arp_cr_icr_pkg.set_to_dummy()+');
873 --
874 p_icr_rec.cash_receipt_id := AR_NUMBER_DUMMY;
875 p_icr_rec.amount := AR_NUMBER_DUMMY;
876 p_icr_rec.factor_discount_amount := AR_NUMBER_DUMMY;
877 p_icr_rec.customer_bank_branch_id := AR_NUMBER_DUMMY;
878 p_icr_rec.anticipated_clearing_date := AR_DATE_DUMMY;
879 p_icr_rec.currency_code := AR_TEXT_DUMMY;
880 p_icr_rec.receipt_method_id := AR_NUMBER_DUMMY;
881 p_icr_rec.remit_bank_acct_use_id := AR_NUMBER_DUMMY;
882 p_icr_rec.batch_id := AR_NUMBER_DUMMY;
883 p_icr_rec.comments := AR_TEXT_DUMMY;
884 p_icr_rec.customer_trx_id := AR_NUMBER_DUMMY;
885 p_icr_rec.exchange_date := AR_DATE_DUMMY;
886 p_icr_rec.exchange_rate := AR_NUMBER_DUMMY;
887 p_icr_rec.exchange_rate_type := AR_TEXT_DUMMY;
888 p_icr_rec.gl_date := AR_DATE_DUMMY;
889 p_icr_rec.gl_posted_date := AR_DATE_DUMMY;
890 p_icr_rec.payment_schedule_id := AR_NUMBER_DUMMY;
891 p_icr_rec.pay_from_customer := AR_NUMBER_DUMMY;
892 p_icr_rec.receipt_date := AR_DATE_DUMMY;
893 p_icr_rec.receipt_number := AR_TEXT_DUMMY;
894 p_icr_rec.site_use_id := AR_NUMBER_DUMMY;
895 p_icr_rec.special_type := AR_TEXT_DUMMY;
896 p_icr_rec.status := AR_TEXT_DUMMY;
897 p_icr_rec.type := AR_TEXT_DUMMY;
898 p_icr_rec.ussgl_transaction_code := AR_TEXT_DUMMY;
899 p_icr_rec.attribute_category := AR_TEXT_DUMMY;
900 p_icr_rec.attribute1 := AR_TEXT_DUMMY;
901 p_icr_rec.attribute2 := AR_TEXT_DUMMY;
902 p_icr_rec.attribute3 := AR_TEXT_DUMMY;
903 p_icr_rec.attribute4 := AR_TEXT_DUMMY;
904 p_icr_rec.attribute5 := AR_TEXT_DUMMY;
905 p_icr_rec.attribute6 := AR_TEXT_DUMMY;
906 p_icr_rec.attribute7 := AR_TEXT_DUMMY;
907 p_icr_rec.attribute8 := AR_TEXT_DUMMY;
908 p_icr_rec.attribute9 := AR_TEXT_DUMMY;
909 p_icr_rec.attribute10 := AR_TEXT_DUMMY;
910 p_icr_rec.attribute11 := AR_TEXT_DUMMY;
911 p_icr_rec.attribute12 := AR_TEXT_DUMMY;
912 p_icr_rec.attribute13 := AR_TEXT_DUMMY;
913 p_icr_rec.attribute14 := AR_TEXT_DUMMY;
914 p_icr_rec.attribute15 := AR_TEXT_DUMMY;
915 p_icr_rec.ussgl_transaction_code_context := AR_TEXT_DUMMY;
916 p_icr_rec.customer_bank_account_id := AR_NUMBER_DUMMY;
917 p_icr_rec.doc_sequence_id := AR_NUMBER_DUMMY;
918 p_icr_rec.doc_sequence_value := AR_NUMBER_DUMMY;
919 p_icr_rec.application_notes := AR_TEXT_DUMMY;
920 p_icr_rec.application_ref_type := AR_TEXT_DUMMY;
921 p_icr_rec.customer_reference := AR_TEXT_DUMMY;
922 p_icr_rec.customer_reason := AR_TEXT_DUMMY;
923 --
924 arp_standard.debug('arp_cr_icr_pkg.set_to_dummy()-');
925 --
926 EXCEPTION
927 WHEN OTHERS THEN
928 arp_standard.debug('EXCEPTION: arp_cr_icr_pkg.set_to_dummy()');
929 RAISE;
930 --
931 END;
932 --
933 --
934 --
935 --
936 --
937 PROCEDURE update_p( p_icr_rec IN ar_interim_cash_receipts%ROWTYPE,
938 p_cash_receipt_id IN
939 ar_interim_cash_receipts.cash_receipt_id%TYPE) IS
940 --
941 BEGIN
942 --
943 arp_standard.debug('arp_cr_icr_pkg.update_p()+ ');
944 --
945 --
946 arp_cr_icr_pkg.generic_update( pg_cursor1,
950 --
947 ' WHERE cash_receipt_id = :where_1',
948 p_cash_receipt_id,
949 p_icr_rec);
951 arp_standard.debug('arp_cr_icr_pkg.update_p()- ' ||
952 to_char(sysdate, 'HH:MI:SS'));
953 --
954 --
955 EXCEPTION
956 WHEN OTHERS THEN
957 arp_standard.debug('EXCEPTION: arp_cr_icr_pkg.update_p()');
958 RAISE;
959 END;
960 --
961 /*===========================================================================+
962 | PROCEDURE |
963 | delete_p |
964 | |
965 | DESCRIPTION |
966 | This function deletes a row into AR_INTERIM_CASH_RECEIPTS |
967 | |
968 | SCOPE - PUBLIC |
969 | |
970 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
971 | |
972 | ARGUMENTS : IN: |
973 | p_icr_id - ICR Id to delete a row from ICR table |
974 | |
975 | RETURNS : NONE |
976 | |
977 | NOTES |
978 | |
979 | MODIFICATION HISTORY - Created by Ganesh Vaidee - 04/25/95 |
980 | |
981 +===========================================================================*/
982 PROCEDURE delete_p(
983 p_icr_id IN ar_interim_cash_receipts.cash_receipt_id%TYPE ) IS
984 BEGIN
985 arp_standard.debug( '>>>>>>>> arp_cr_icr_pkg.delete_p' );
986 --
987 DELETE FROM ar_interim_cash_receipts
988 WHERE cash_receipt_id = p_icr_id;
989 --
990 arp_standard.debug( '<<<<<<<< arp_cr_icr_pkg.delete_p' );
991 EXCEPTION
992 WHEN OTHERS THEN
993 arp_standard.debug( 'EXCEPTION: arp_cr_icr_pkg.delete_p' );
994 RAISE;
995 END;
996 --
997 /*===========================================================================+
998 | PROCEDURE |
999 | lock_p |
1000 | |
1001 | DESCRIPTION |
1002 | This function locks a row in AR_INTERIM_CASH_RECEIPTS |
1003 | |
1004 | SCOPE - PUBLIC |
1005 | |
1006 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
1007 | |
1008 | ARGUMENTS : IN: |
1009 | p_icr_id - Icr Id of row to be locked in ICR |
1010 | |
1011 | RETURNS : NONE |
1012 | |
1013 | NOTES |
1014 | |
1015 | MODIFICATION HISTORY - Created by Ganesh Vaidee - 04/25/95 |
1016 | |
1017 +===========================================================================*/
1018 PROCEDURE lock_p( p_icr_id IN ar_interim_cash_receipts.cash_receipt_id%TYPE ) IS
1019 l_icr_id ar_interim_cash_receipts.cash_receipt_id%TYPE;
1020 BEGIN
1021 arp_standard.debug( '>>>>>>>> arp_cr_icr_pkg.lock_p' );
1022 --
1023 SELECT cash_receipt_id
1024 INTO l_icr_id
1025 FROM ar_interim_cash_receipts
1026 WHERE cash_receipt_id = p_icr_id
1027 FOR UPDATE OF STATUS;
1028 --
1029 arp_standard.debug( '<<<<<<<< arp_cr_icr_pkg.lock_p' );
1030 EXCEPTION
1031 WHEN OTHERS THEN
1032 arp_standard.debug( 'EXCEPTION: arp_cr_icr_pkg.lock_p' );
1033 RAISE;
1034 END;
1035 --
1036 PROCEDURE nowaitlock_p( p_icr_id IN ar_interim_cash_receipts.cash_receipt_id%TYPE ) IS
1037 l_icr_id ar_interim_cash_receipts.cash_receipt_id%TYPE;
1038 BEGIN
1039 arp_standard.debug( '>>>>>>>> arp_cr_icr_pkg.nowaitlock_p' );
1040 --
1041 SELECT cash_receipt_id
1042 INTO l_icr_id
1043 FROM ar_interim_cash_receipts
1044 WHERE cash_receipt_id = p_icr_id
1045 FOR UPDATE OF STATUS NOWAIT;
1046 --
1047 arp_standard.debug( '<<<<<<<< arp_cr_icr_pkg.nowaitlock_p' );
1048 EXCEPTION
1049 WHEN OTHERS THEN
1053 --
1050 arp_standard.debug( 'EXCEPTION: arp_cr_icr_pkg.nowaitlock_p' );
1051 RAISE;
1052 END;
1054 /*===========================================================================+
1055 | PROCEDURE |
1056 | fetch_p |
1057 | |
1058 | DESCRIPTION |
1059 | This function fetches a row from AR_INTERIM_CASH_RECEIPTS |
1060 | |
1061 | SCOPE - PUBLIC |
1062 | |
1063 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
1064 | |
1065 | ARGUMENTS : IN: |
1066 | p_icr_id - Icr Id of row to be fetched from ICR |
1067 | OUT: |
1068 | p_adj_rec - ICR Record structure |
1069 | |
1070 | RETURNS : NONE |
1071 | |
1072 | NOTES |
1073 | |
1074 | MODIFICATION HISTORY - Created by Ganesh Vaidee - 04/25/95 |
1075 | |
1076 +===========================================================================*/
1077 PROCEDURE fetch_p( p_icr_id IN ar_interim_cash_receipts.cash_receipt_id%TYPE,
1078 p_icr_rec OUT NOCOPY ar_interim_cash_receipts%ROWTYPE ) IS
1079 BEGIN
1080 arp_standard.debug( '>>>>>>>> arp_cr_icr_pkg.fetch_p' );
1081 --
1082 SELECT *
1083 INTO p_icr_rec
1084 FROM ar_interim_cash_receipts
1085 WHERE cash_receipt_id = p_icr_id;
1086 --
1087 arp_standard.debug( '<<<<<<<<< arp_cr_icr_pkg.fetch_p' );
1088 EXCEPTION
1089 --
1090 WHEN OTHERS THEN
1091 arp_standard.debug( 'EXCEPTION: arp_cr_icr_pkg.fetch_p' );
1092 RAISE;
1093 END;
1094 --
1095
1096 PROCEDURE lock_fetch_p( p_icr_rec IN OUT NOCOPY ar_interim_cash_receipts%ROWTYPE ) IS
1097 BEGIN
1098 IF PG_DEBUG in ('Y', 'C') THEN
1099 arp_standard.debug( 'arp_cr_icr_pkg.lock_fetch_p()+' );
1100 END IF;
1101 --
1102 SELECT *
1103 INTO p_icr_rec
1104 FROM ar_interim_cash_receipts
1105 WHERE cash_receipt_id = p_icr_rec.cash_receipt_id
1106 FOR UPDATE OF STATUS;
1107 --
1108 IF PG_DEBUG in ('Y', 'C') THEN
1109 arp_standard.debug( 'arp_cr_icr_pkg.lock_fetch_p()-' );
1110 END IF;
1111 EXCEPTION
1112 WHEN OTHERS THEN
1113 IF PG_DEBUG in ('Y', 'C') THEN
1114 arp_standard.debug( 'EXCEPTION: arp_cr_icr_pkg.lock_fetch_p' );
1115 END IF;
1116 RAISE;
1117 END lock_fetch_p;
1118 --
1119 --
1120 PROCEDURE nowaitlock_fetch_p( p_icr_rec IN OUT NOCOPY ar_interim_cash_receipts%ROWTYPE ) IS
1121 BEGIN
1122 IF PG_DEBUG in ('Y', 'C') THEN
1123 arp_standard.debug( 'arp_cr_icr_pkg.nowaitlock_fetch_p()+' );
1124 END IF;
1125 --
1126 SELECT *
1127 INTO p_icr_rec
1128 FROM ar_interim_cash_receipts
1129 WHERE cash_receipt_id = p_icr_rec.cash_receipt_id
1130 FOR UPDATE OF STATUS NOWAIT;
1131 --
1132 IF PG_DEBUG in ('Y', 'C') THEN
1133 arp_standard.debug( 'arp_cr_icr_pkg.nowaitlock_fetch_p()-' );
1134 END IF;
1135 EXCEPTION
1136 WHEN OTHERS THEN
1137 IF PG_DEBUG in ('Y', 'C') THEN
1138 arp_standard.debug( 'EXCEPTION: arp_cr_icr_pkg.nowaitlock_fetch_p' );
1139 END IF;
1140 RAISE;
1141 END nowaitlock_fetch_p;
1142 --
1143 --
1144 --
1145 --
1146 --
1147 --
1148 --
1149 /*---------------------------------------------+
1150 | Package initialization section. |
1151 | Sets WHO column variables for later use. |
1152 +---------------------------------------------*/
1153 --
1154 BEGIN
1155 --
1156 pg_request_id := arp_global.request_id;
1157 pg_program_application_id := arp_global.program_application_id;
1158 pg_program_id := arp_global.program_id;
1159 pg_program_update_date := arp_global.program_update_date;
1160 pg_last_updated_by := arp_global.last_updated_by;
1161 pg_last_update_date := arp_global.last_update_date;
1162 pg_last_update_login := arp_global.last_update_login;
1163 pg_set_of_books_id := arp_global.set_of_books_id;
1164 --
1165 --
1166 END ARP_CR_ICR_PKG;
1167 --