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