[Home] [Help]
PACKAGE BODY: APPS.ARP_CASH_RECEIPTS_PKG
Source
1 PACKAGE BODY ARP_CASH_RECEIPTS_PKG AS
2 /* $Header: ARRICRB.pls 120.14.12010000.5 2009/03/20 06:03:25 mpsingh ship $*/
3 --
4 --
5 --
6 /*--------------------------------------------------------+
7 | Dummy constants for use in update and lock operations |
8 +--------------------------------------------------------*/
9 --
10 AR_TEXT_DUMMY CONSTANT VARCHAR2(10) := '~~!@#$*&^';
11 AR_FLAG_DUMMY CONSTANT VARCHAR2(1) := '~';
12 AR_NUMBER_DUMMY CONSTANT NUMBER(15) := -999999999999999;
13 AR_DATE_DUMMY CONSTANT DATE := to_date(1, 'J');
14 /*-------------------------------------+
15 | WHO column values from ARP_GLOBAL |
16 +-------------------------------------*/
17 --
18 pg_request_id number;
19 pg_program_application_id number;
20 pg_program_id number;
21 pg_program_update_date date;
22 pg_last_updated_by number;
23 pg_last_update_date date;
24 pg_last_update_login number;
25 -- pg_set_of_books_id number;
26
27 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
28
29 PROCEDURE set_to_dummy( p_cr_rec OUT NOCOPY ar_cash_receipts%ROWTYPE ) IS
30 BEGIN
31 IF PG_DEBUG in ('Y', 'C') THEN
32 arp_util.debug( 'arp_cash_receipts_pkg.set_to_dummy()+' );
33 END IF;
34 --
35 p_cr_rec.amount := AR_NUMBER_DUMMY;
36 p_cr_rec.currency_code := AR_TEXT_DUMMY;
37 p_cr_rec.receipt_method_id := AR_NUMBER_DUMMY;
38 p_cr_rec.set_of_books_id := AR_NUMBER_DUMMY;
39 p_cr_rec.comments := AR_TEXT_DUMMY;
40 p_cr_rec.confirmed_flag := AR_FLAG_DUMMY;
41 p_cr_rec.customer_bank_account_id := AR_NUMBER_DUMMY;
42 p_cr_rec.customer_bank_branch_id := AR_NUMBER_DUMMY;
43 p_cr_rec.customer_site_use_id := AR_NUMBER_DUMMY;
44 p_cr_rec.deposit_date := AR_DATE_DUMMY;
45 p_cr_rec.distribution_set_id := AR_NUMBER_DUMMY;
46 p_cr_rec.exchange_date := AR_DATE_DUMMY;
47 p_cr_rec.exchange_rate := AR_NUMBER_DUMMY;
48 p_cr_rec.exchange_rate_type := AR_TEXT_DUMMY;
49 p_cr_rec.misc_payment_source := AR_TEXT_DUMMY;
50 p_cr_rec.pay_from_customer := AR_NUMBER_DUMMY;
51 p_cr_rec.receipt_date := AR_DATE_DUMMY;
52 p_cr_rec.receipt_number := AR_TEXT_DUMMY;
53 p_cr_rec.receivables_trx_id := AR_NUMBER_DUMMY;
54 p_cr_rec.remit_bank_acct_use_id := AR_NUMBER_DUMMY;
55 p_cr_rec.reversal_category := AR_TEXT_DUMMY;
56 p_cr_rec.reversal_comments := AR_TEXT_DUMMY;
57 p_cr_rec.reversal_date := AR_DATE_DUMMY;
58 p_cr_rec.selected_for_factoring_flag := AR_FLAG_DUMMY;
59 p_cr_rec.selected_remittance_batch_id := AR_NUMBER_DUMMY;
60 p_cr_rec.status := AR_TEXT_DUMMY;
61 p_cr_rec.type := AR_TEXT_DUMMY;
62 p_cr_rec.attribute_category := AR_TEXT_DUMMY;
63 p_cr_rec.attribute1 := AR_TEXT_DUMMY;
64 p_cr_rec.attribute2 := AR_TEXT_DUMMY;
65 p_cr_rec.attribute3 := AR_TEXT_DUMMY;
66 p_cr_rec.attribute4 := AR_TEXT_DUMMY;
67 p_cr_rec.attribute5 := AR_TEXT_DUMMY;
68 p_cr_rec.attribute6 := AR_TEXT_DUMMY;
69 p_cr_rec.attribute7 := AR_TEXT_DUMMY;
70 p_cr_rec.attribute8 := AR_TEXT_DUMMY;
71 p_cr_rec.attribute9 := AR_TEXT_DUMMY;
72 p_cr_rec.attribute10 := AR_TEXT_DUMMY;
73 p_cr_rec.attribute11 := AR_TEXT_DUMMY;
74 p_cr_rec.attribute12 := AR_TEXT_DUMMY;
75 p_cr_rec.attribute13 := AR_TEXT_DUMMY;
76 p_cr_rec.attribute14 := AR_TEXT_DUMMY;
77 p_cr_rec.attribute15 := AR_TEXT_DUMMY;
78 p_cr_rec.factor_discount_amount := AR_NUMBER_DUMMY;
79 p_cr_rec.ussgl_transaction_code := AR_TEXT_DUMMY;
80 p_cr_rec.ussgl_transaction_code_context := AR_TEXT_DUMMY;
81 p_cr_rec.reversal_reason_code := AR_TEXT_DUMMY;
82 p_cr_rec.doc_sequence_id := AR_NUMBER_DUMMY;
83 p_cr_rec.doc_sequence_value := AR_NUMBER_DUMMY;
84 p_cr_rec.vat_tax_id := AR_NUMBER_DUMMY;
85 p_cr_rec.reference_type := AR_TEXT_DUMMY;
86 p_cr_rec.reference_id := AR_NUMBER_DUMMY;
87 p_cr_rec.customer_receipt_reference := AR_TEXT_DUMMY;
88 p_cr_rec.override_remit_account_flag := AR_FLAG_DUMMY;
89 p_cr_rec.anticipated_clearing_date := AR_DATE_DUMMY;
90 p_cr_rec.global_attribute_category := AR_TEXT_DUMMY;
91 p_cr_rec.global_attribute1 := AR_TEXT_DUMMY;
92 p_cr_rec.global_attribute2 := AR_TEXT_DUMMY;
93 p_cr_rec.global_attribute3 := AR_TEXT_DUMMY;
94 p_cr_rec.global_attribute4 := AR_TEXT_DUMMY;
95 p_cr_rec.global_attribute5 := AR_TEXT_DUMMY;
96 p_cr_rec.global_attribute6 := AR_TEXT_DUMMY;
97 p_cr_rec.global_attribute7 := AR_TEXT_DUMMY;
98 p_cr_rec.global_attribute8 := AR_TEXT_DUMMY;
99 p_cr_rec.global_attribute9 := AR_TEXT_DUMMY;
100 p_cr_rec.global_attribute10 := AR_TEXT_DUMMY;
101 p_cr_rec.global_attribute11 := AR_TEXT_DUMMY;
102 p_cr_rec.global_attribute12 := AR_TEXT_DUMMY;
103 p_cr_rec.global_attribute13 := AR_TEXT_DUMMY;
104 p_cr_rec.global_attribute14 := AR_TEXT_DUMMY;
105 p_cr_rec.global_attribute15 := AR_TEXT_DUMMY;
106 p_cr_rec.global_attribute16 := AR_TEXT_DUMMY;
107 p_cr_rec.global_attribute17 := AR_TEXT_DUMMY;
108 p_cr_rec.global_attribute18 := AR_TEXT_DUMMY;
109 p_cr_rec.global_attribute19 := AR_TEXT_DUMMY;
110 p_cr_rec.global_attribute20 := AR_TEXT_DUMMY;
111 --
112 -- Notes Receivable additional information
113 --
114 p_cr_rec.issuer_name := AR_TEXT_DUMMY;
115 p_cr_rec.issue_date := AR_DATE_DUMMY;
116 p_cr_rec.issuer_bank_branch_id := AR_NUMBER_DUMMY;
117
118 -- ARTA Changes
119 p_cr_rec.postmark_date := AR_DATE_DUMMY;
120
121 -- OSTEINME 3/12/01: need to set credit card fields to
122 -- dummy values (bug 1683007)
123 /* Bug 7427809 Obsoleted this column in R12
124 p_cr_rec.payment_server_order_num := AR_TEXT_DUMMY; */
125 p_cr_rec.approval_code := AR_TEXT_DUMMY;
126
127 -- enhancement 2074220
128 p_cr_rec.application_notes := AR_TEXT_DUMMY;
129
130 /* Bug fix 3226723 */
131 p_cr_rec.rec_version_number := AR_NUMBER_DUMMY;
132
133 p_cr_rec.legal_entity_id := AR_NUMBER_DUMMY; /* LE */
134
135 p_cr_rec.payment_trxn_extension_id := AR_NUMBER_DUMMY; /* bichatte payment uptake project */
136 p_cr_rec.automatch_set_id := AR_NUMBER_DUMMY; /* ER Automatch Application */
137 p_cr_rec.autoapply_flag := AR_FLAG_DUMMY;
138 --
139 IF PG_DEBUG in ('Y', 'C') THEN
140 arp_util.debug( 'arp_cash_receipts_pkg.set_to_dummy_p()-' );
141 END IF;
142 --
143 EXCEPTION
144 WHEN OTHERS THEN
145 IF PG_DEBUG in ('Y', 'C') THEN
146 arp_util.debug( 'EXCEPTION: arp_cash_receipts_pkg.set_to_dummy' );
147 END IF;
148 RAISE;
149 END set_to_dummy;
150 --
151 PROCEDURE insert_p(
152 p_cr_rec IN OUT NOCOPY ar_cash_receipts%ROWTYPE ) IS
153
154 l_cr_id ar_cash_receipts.cash_receipt_id%TYPE;
155 l_cr_key_value_list gl_ca_utility_pkg.r_key_value_arr;
156 l_rec_version_number ar_cash_receipts.rec_version_number%TYPE := 1 ; /* Bug fix 3226723 */
157 --begin LE
158 l_legal_entity_id number;
159 --end LE
160
161 BEGIN
162 IF PG_DEBUG in ('Y', 'C') THEN
163 arp_util.debug( 'arp_cash_receipts_pkg.insert_p()+' );
164 END IF;
165 --
166 SELECT ar_cash_receipts_s.nextval
167 INTO l_cr_id
168 FROM dual;
169 --
170
171 INSERT INTO ar_cash_receipts (
172 cash_receipt_id,
173 amount,
174 currency_code,
175 receipt_method_id,
176 set_of_books_id,
177 comments,
178 confirmed_flag,
179 customer_bank_account_id,
180 customer_bank_branch_id,
181 customer_site_use_id,
182 deposit_date,
183 distribution_set_id,
184 exchange_date,
185 exchange_rate,
186 exchange_rate_type,
187 misc_payment_source,
188 pay_from_customer,
189 receipt_date,
190 receipt_number,
191 receivables_trx_id,
192 remit_bank_acct_use_id,
193 reversal_category,
194 reversal_comments,
195 reversal_date,
196 selected_for_factoring_flag,
197 selected_remittance_batch_id,
198 status,
199 type,
200 attribute_category,
201 attribute1,
202 attribute2,
203 attribute3,
204 attribute4,
205 attribute5,
206 attribute6,
207 attribute7,
208 attribute8,
209 attribute9,
210 attribute10,
211 attribute11,
212 attribute12,
213 attribute13,
214 attribute14,
215 attribute15,
216 request_id,
217 program_application_id,
218 program_id,
219 program_update_date,
220 created_by,
221 creation_date,
222 last_updated_by,
223 last_update_date,
224 last_update_login,
225 factor_discount_amount,
226 ussgl_transaction_code,
227 ussgl_transaction_code_context,
228 reversal_reason_code,
229 doc_sequence_id,
230 doc_sequence_value,
231 vat_tax_id,
232 reference_type,
233 reference_id,
234 customer_receipt_reference,
235 override_remit_account_flag,
236 anticipated_clearing_date,
237 global_attribute_category,
238 global_attribute1,
239 global_attribute2,
240 global_attribute3,
241 global_attribute4,
242 global_attribute5,
243 global_attribute6,
244 global_attribute7,
245 global_attribute8,
246 global_attribute9,
247 global_attribute10,
248 global_attribute11,
249 global_attribute12,
250 global_attribute13,
251 global_attribute14,
252 global_attribute15,
253 global_attribute16,
254 global_attribute17,
255 global_attribute18,
256 global_attribute19,
257 global_attribute20,
258 issuer_name,
259 issue_date,
260 issuer_bank_branch_id,
261 tax_rate,
262 postmark_date, -- ARTA Changes
263 /* Bug 7427809 Obsoleted this column in R12
264 payment_server_order_num, --apandit Bug 1820063 */
265 approval_code,
266 application_notes -- djancis 2074220
267 ,org_id
268 ,legal_entity_id
269 ,payment_trxn_extension_id -- bichatte payment uptake project
270 ,rec_version_number /* Bug fix 3226723 */
271 ,automatch_set_id
272 ,autoapply_flag
273 )
274 VALUES ( l_cr_id,
275 p_cr_rec.amount,
276 p_cr_rec.currency_code,
277 p_cr_rec.receipt_method_id,
278 arp_global.set_of_books_id,
279 p_cr_rec.comments,
280 p_cr_rec.confirmed_flag,
281 p_cr_rec.customer_bank_account_id,
282 p_cr_rec.customer_bank_branch_id,
283 p_cr_rec.customer_site_use_id,
284 p_cr_rec.deposit_date,
285 p_cr_rec.distribution_set_id,
286 p_cr_rec.exchange_date,
287 p_cr_rec.exchange_rate,
288 p_cr_rec.exchange_rate_type,
289 p_cr_rec.misc_payment_source,
290 p_cr_rec.pay_from_customer,
291 p_cr_rec.receipt_date,
292 p_cr_rec.receipt_number,
293 p_cr_rec.receivables_trx_id,
294 p_cr_rec.remit_bank_acct_use_id,
295 p_cr_rec.reversal_category,
296 p_cr_rec.reversal_comments,
297 p_cr_rec.reversal_date,
298 p_cr_rec.selected_for_factoring_flag,
299 p_cr_rec.selected_remittance_batch_id,
300 p_cr_rec.status,
301 p_cr_rec.type,
302 p_cr_rec.attribute_category,
303 p_cr_rec.attribute1,
304 p_cr_rec.attribute2,
305 p_cr_rec.attribute3,
306 p_cr_rec.attribute4,
307 p_cr_rec.attribute5,
308 p_cr_rec.attribute6,
309 p_cr_rec.attribute7,
310 p_cr_rec.attribute8,
311 p_cr_rec.attribute9,
312 p_cr_rec.attribute10,
313 p_cr_rec.attribute11,
314 p_cr_rec.attribute12,
315 p_cr_rec.attribute13,
316 p_cr_rec.attribute14,
317 p_cr_rec.attribute15,
318 pg_request_id,
319 pg_program_application_id,
320 pg_program_id,
321 DECODE( pg_program_id,
322 NULL, NULL,
323 SYSDATE),
324 arp_global.last_updated_by, /* FP Bug 5715840 pg_last_updated_by,*/
325 SYSDATE,
326 arp_global.last_updated_by, /* FP Bug 5715840 pg_last_updated_by,*/
327 SYSDATE,
328 arp_global.last_update_login, /* FP Bug 5715840 pg_last_update_login,*/
329 p_cr_rec.factor_discount_amount,
330 p_cr_rec.ussgl_transaction_code,
331 p_cr_rec.ussgl_transaction_code_context,
332 p_cr_rec.reversal_reason_code,
333 p_cr_rec.doc_sequence_id,
334 p_cr_rec.doc_sequence_value,
335 p_cr_rec.vat_tax_id,
336 p_cr_rec.reference_type,
337 p_cr_rec.reference_id,
338 p_cr_rec.customer_receipt_reference,
339 p_cr_rec.override_remit_account_flag,
340 p_cr_rec.anticipated_clearing_date,
341 p_cr_rec.global_attribute_category,
342 p_cr_rec.global_attribute1,
343 p_cr_rec.global_attribute2,
344 p_cr_rec.global_attribute3,
348 p_cr_rec.global_attribute7,
345 p_cr_rec.global_attribute4,
346 p_cr_rec.global_attribute5,
347 p_cr_rec.global_attribute6,
349 p_cr_rec.global_attribute8,
350 p_cr_rec.global_attribute9,
351 p_cr_rec.global_attribute10,
352 p_cr_rec.global_attribute11,
353 p_cr_rec.global_attribute12,
354 p_cr_rec.global_attribute13,
355 p_cr_rec.global_attribute14,
356 p_cr_rec.global_attribute15,
357 p_cr_rec.global_attribute16,
358 p_cr_rec.global_attribute17,
359 p_cr_rec.global_attribute18,
360 p_cr_rec.global_attribute19,
361 p_cr_rec.global_attribute20,
362 p_cr_rec.issuer_name,
363 p_cr_rec.issue_date,
364 p_cr_rec.issuer_bank_branch_id,
365 p_cr_rec.tax_rate,
366 p_cr_rec.postmark_date,
367 /* Bug 7427809 Obsoleted this column in R12
368 p_cr_rec.payment_server_order_num, --apandit Bug 1820063. */
369 p_cr_rec.approval_code,
370 p_cr_rec.application_notes, -- djancis 2074220
371 arp_standard.sysparm.org_id, /* SSA changes */
372 p_cr_rec.legal_entity_id, /* LE */
373 p_cr_rec.payment_trxn_extension_id, /* bichatte payment uptake */
374 l_rec_version_number, /* Bug fix 3226723 */
375 p_cr_rec.automatch_set_id,
376 p_cr_rec.autoapply_flag
377 );
378 p_cr_rec.cash_receipt_id := l_cr_id;
379
380 /*-----------------------------------+
381 | Calling Central MRC library for |
382 | MRC integration. |
383 +-----------------------------------*/
384
385 ar_mrc_engine.maintain_mrc_data(
386 p_event_mode => 'INSERT',
387 p_table_name => 'AR_CASH_RECEIPTS',
388 p_mode => 'SINGLE',
389 p_key_value => l_cr_id);
390
391 --
392 IF PG_DEBUG in ('Y', 'C') THEN
393 arp_util.debug( 'arp_cash_receipts_pkg.insert_p()-' );
394 END IF;
395 --
396 EXCEPTION
397 WHEN OTHERS THEN
398 IF PG_DEBUG in ('Y', 'C') THEN
399 arp_util.debug( 'EXCEPTION: arp_cash_receipts_pkg.insert_p' );
400 END IF;
401 RAISE;
402 END insert_p;
403 --
404 PROCEDURE update_p( p_cr_rec IN ar_cash_receipts%ROWTYPE,
405 p_cr_id IN ar_cash_receipts.cash_receipt_id%TYPE ) IS
406 BEGIN
407 IF PG_DEBUG in ('Y', 'C') THEN
408 arp_util.debug( 'arp_cash_receipts_pkg.update_p()+' );
409 END IF;
410 --
411 IF PG_DEBUG in ('Y', 'C') THEN
412 arp_util.debug('update_p: ' || 'before call to update ar_cash_receipts');
413 END IF;
414 UPDATE ar_cash_receipts SET
415 amount = DECODE( p_cr_rec.amount,
416 AR_NUMBER_DUMMY, amount,
417 p_cr_rec.amount ),
418 currency_code = DECODE( p_cr_rec.currency_code,
419 AR_TEXT_DUMMY, currency_code,
420 p_cr_rec.currency_code ),
421 receipt_method_id = DECODE( p_cr_rec.receipt_method_id,
422 AR_NUMBER_DUMMY, receipt_method_id,
423 p_cr_rec.receipt_method_id ),
424 set_of_books_id = DECODE( p_cr_rec.set_of_books_id,
425 AR_NUMBER_DUMMY, set_of_books_id,
426 -- pg_set_of_books_id ),
427 arp_global.set_of_books_id ),-- FP Bug 5964025
428 comments = DECODE( p_cr_rec.comments,
429 AR_TEXT_DUMMY, comments,
430 p_cr_rec.comments ),
431 confirmed_flag = DECODE( p_cr_rec.confirmed_flag,
432 AR_FLAG_DUMMY, confirmed_flag,
433 p_cr_rec.confirmed_flag ),
434 customer_bank_account_id = DECODE( p_cr_rec.customer_bank_account_id,
435 AR_NUMBER_DUMMY, customer_bank_account_id,
436 p_cr_rec.customer_bank_account_id ),
437 customer_bank_branch_id = DECODE( p_cr_rec.customer_bank_branch_id,
438 AR_NUMBER_DUMMY, customer_bank_branch_id,
439 p_cr_rec.customer_bank_branch_id ),
440 customer_site_use_id = DECODE( p_cr_rec.customer_site_use_id,
441 AR_NUMBER_DUMMY, customer_site_use_id,
442 p_cr_rec.customer_site_use_id ),
443 deposit_date = DECODE( p_cr_rec.deposit_date,
444 AR_DATE_DUMMY, deposit_date,
445 p_cr_rec.deposit_date ),
446 distribution_set_id = DECODE( p_cr_rec.distribution_set_id,
447 AR_NUMBER_DUMMY, distribution_set_id,
448 p_cr_rec.distribution_set_id ),
449 exchange_date = DECODE( p_cr_rec.exchange_date,
450 AR_DATE_DUMMY, exchange_date,
451 p_cr_rec.exchange_date ),
452 exchange_rate = DECODE( p_cr_rec.exchange_rate,
453 AR_NUMBER_DUMMY, exchange_rate,
454 p_cr_rec.exchange_rate ),
455 exchange_rate_type = DECODE( p_cr_rec.exchange_rate_type,
456 AR_TEXT_DUMMY, exchange_rate_type,
457 p_cr_rec.exchange_rate_type ),
458 misc_payment_source = DECODE( p_cr_rec.misc_payment_source,
459 AR_TEXT_DUMMY, misc_payment_source,
460 p_cr_rec.misc_payment_source ),
461 pay_from_customer = DECODE( p_cr_rec.pay_from_customer,
462 AR_NUMBER_DUMMY, pay_from_customer,
463 p_cr_rec.pay_from_customer ),
464 receipt_date = DECODE( p_cr_rec.receipt_date,
465 AR_DATE_DUMMY, receipt_date,
466 p_cr_rec.receipt_date ),
467 receipt_number = DECODE( p_cr_rec.receipt_number,
468 AR_TEXT_DUMMY, receipt_number,
469 p_cr_rec.receipt_number ),
470 receivables_trx_id = DECODE( p_cr_rec.receivables_trx_id,
471 AR_NUMBER_DUMMY, receivables_trx_id,
472 p_cr_rec.receivables_trx_id ),
473 remit_bank_acct_use_id =
474 DECODE( p_cr_rec.remit_bank_acct_use_id,
475 AR_NUMBER_DUMMY, remit_bank_acct_use_id,
476 p_cr_rec.remit_bank_acct_use_id ),
477 reversal_category = DECODE( p_cr_rec.reversal_category,
478 AR_TEXT_DUMMY, reversal_category,
479 p_cr_rec.reversal_category ),
480 reversal_comments = DECODE( p_cr_rec.reversal_comments,
481 AR_TEXT_DUMMY, reversal_comments,
482 p_cr_rec.reversal_comments ),
483 reversal_date = DECODE( p_cr_rec.reversal_date,
484 AR_DATE_DUMMY, reversal_date,
485 p_cr_rec.reversal_date ),
486 selected_for_factoring_flag =
490 selected_remittance_batch_id =
487 DECODE( p_cr_rec.selected_for_factoring_flag,
488 AR_FLAG_DUMMY, selected_for_factoring_flag,
489 p_cr_rec.selected_for_factoring_flag ),
491 DECODE( p_cr_rec.selected_remittance_batch_id,
492 AR_NUMBER_DUMMY, selected_remittance_batch_id,
493 p_cr_rec.selected_remittance_batch_id ),
494 status = DECODE( p_cr_rec.status,
495 AR_TEXT_DUMMY, status,
496 p_cr_rec.status ),
497 type = DECODE( p_cr_rec.type,
498 AR_TEXT_DUMMY, type,
499 p_cr_rec.type ),
500 attribute_category = DECODE( p_cr_rec.attribute_category,
501 AR_TEXT_DUMMY, attribute_category,
502 p_cr_rec.attribute_category ),
503 attribute1 = DECODE( p_cr_rec.attribute1,
504 AR_TEXT_DUMMY, attribute1,
505 p_cr_rec.attribute1 ),
506 attribute2 = DECODE( p_cr_rec.attribute2,
507 AR_TEXT_DUMMY, attribute2,
508 p_cr_rec.attribute2 ),
509 attribute3 = DECODE( p_cr_rec.attribute3,
510 AR_TEXT_DUMMY, attribute3,
511 p_cr_rec.attribute3 ),
512 attribute4 = DECODE( p_cr_rec.attribute4,
513 AR_TEXT_DUMMY, attribute4,
514 p_cr_rec.attribute4 ),
515 attribute5 = DECODE( p_cr_rec.attribute5,
516 AR_TEXT_DUMMY, attribute5,
517 p_cr_rec.attribute5 ),
518 attribute6 = DECODE( p_cr_rec.attribute6,
519 AR_TEXT_DUMMY, attribute6,
520 p_cr_rec.attribute6 ),
521 attribute7 = DECODE( p_cr_rec.attribute7,
522 AR_TEXT_DUMMY, attribute7,
523 p_cr_rec.attribute7 ),
524 attribute8 = DECODE( p_cr_rec.attribute8,
525 AR_TEXT_DUMMY, attribute8,
526 p_cr_rec.attribute8 ),
527 attribute9 = DECODE( p_cr_rec.attribute9,
528 AR_TEXT_DUMMY, attribute9,
529 p_cr_rec.attribute9 ),
530 attribute10 = DECODE( p_cr_rec.attribute10,
531 AR_TEXT_DUMMY, attribute10,
532 p_cr_rec.attribute10 ),
533 attribute11 = DECODE( p_cr_rec.attribute11,
534 AR_TEXT_DUMMY, attribute11,
535 p_cr_rec.attribute11 ),
536 attribute12 = DECODE( p_cr_rec.attribute12,
537 AR_TEXT_DUMMY, attribute12,
538 p_cr_rec.attribute12 ),
539 attribute13 = DECODE( p_cr_rec.attribute13,
540 AR_TEXT_DUMMY, attribute13,
541 p_cr_rec.attribute13 ),
542 attribute14 = DECODE( p_cr_rec.attribute14,
543 AR_TEXT_DUMMY, attribute14,
544 p_cr_rec.attribute14 ),
545 attribute15 = DECODE( p_cr_rec.attribute15,
546 AR_TEXT_DUMMY, attribute15,
547 p_cr_rec.attribute15 ),
548 request_id = pg_request_id,
549 program_application_id = pg_program_application_id,
550 program_id = pg_program_id,
551 program_update_date = DECODE( pg_program_id,
552 NULL, NULL,
553 SYSDATE),
554 last_updated_by = arp_global.last_updated_by, /* FP Bug 5715840 pg_last_updated_by, */
555 last_update_date = SYSDATE,
556 last_update_login = arp_global.last_update_login, /* FP Bug 5715840 pg_last_update_login, */
557 factor_discount_amount = DECODE( p_cr_rec.factor_discount_amount,
558 AR_NUMBER_DUMMY, factor_discount_amount,
559 p_cr_rec.factor_discount_amount ),
560 ussgl_transaction_code = DECODE( p_cr_rec.ussgl_transaction_code,
561 AR_TEXT_DUMMY, ussgl_transaction_code,
562 p_cr_rec.ussgl_transaction_code ),
563 ussgl_transaction_code_context =
564 DECODE( p_cr_rec.ussgl_transaction_code_context,
565 AR_TEXT_DUMMY, ussgl_transaction_code_context,
566 p_cr_rec.ussgl_transaction_code_context ),
567 reversal_reason_code = DECODE( p_cr_rec.reversal_reason_code,
568 AR_TEXT_DUMMY, reversal_reason_code,
569 p_cr_rec.reversal_reason_code ),
570 doc_sequence_id = DECODE( p_cr_rec.doc_sequence_id,
571 AR_NUMBER_DUMMY, doc_sequence_id,
572 p_cr_rec.doc_sequence_id ),
573 doc_sequence_value = DECODE( p_cr_rec.doc_sequence_value,
574 AR_NUMBER_DUMMY, doc_sequence_value,
575 p_cr_rec.doc_sequence_value ),
576 vat_tax_id = DECODE( p_cr_rec.vat_tax_id,
577 AR_NUMBER_DUMMY, vat_tax_id,
578 p_cr_rec.vat_tax_id ),
579 reference_type = DECODE( p_cr_rec.reference_type,
580 AR_TEXT_DUMMY, reference_type,
581 p_cr_rec.reference_type ),
582 reference_id = DECODE( p_cr_rec.reference_id,
583 AR_NUMBER_DUMMY, reference_id,
584 p_cr_rec.reference_id ),
585 customer_receipt_reference =
586 DECODE( p_cr_rec.customer_receipt_reference,
587 AR_TEXT_DUMMY, customer_receipt_reference,
588 p_cr_rec.customer_receipt_reference ),
589 override_remit_account_flag =
590 DECODE( p_cr_rec.override_remit_account_flag,
591 AR_FLAG_DUMMY, override_remit_account_flag,
592 p_cr_rec.override_remit_account_flag ),
593 anticipated_clearing_date =
594 DECODE( p_cr_rec.anticipated_clearing_date,
595 AR_DATE_DUMMY, anticipated_clearing_date,
596 p_cr_rec.anticipated_clearing_date ),
597 global_attribute_category = DECODE( p_cr_rec.global_attribute_category,
598 AR_TEXT_DUMMY, global_attribute_category,
599 p_cr_rec.global_attribute_category ),
600 global_attribute1 = DECODE( p_cr_rec.global_attribute1,
601 AR_TEXT_DUMMY, global_attribute1,
602 p_cr_rec.global_attribute1 ),
603 global_attribute2 = DECODE( p_cr_rec.global_attribute2,
604 AR_TEXT_DUMMY, global_attribute2,
605 p_cr_rec.global_attribute2 ),
609 global_attribute4 = DECODE( p_cr_rec.global_attribute4,
606 global_attribute3 = DECODE( p_cr_rec.global_attribute3,
607 AR_TEXT_DUMMY, global_attribute3,
608 p_cr_rec.global_attribute3 ),
610 AR_TEXT_DUMMY, global_attribute4,
611 p_cr_rec.global_attribute4 ),
612 global_attribute5 = DECODE( p_cr_rec.global_attribute5,
613 AR_TEXT_DUMMY, global_attribute5,
614 p_cr_rec.global_attribute5 ),
615 global_attribute6 = DECODE( p_cr_rec.global_attribute6,
616 AR_TEXT_DUMMY, global_attribute6,
617 p_cr_rec.global_attribute6 ),
618 global_attribute7 = DECODE( p_cr_rec.global_attribute7,
619 AR_TEXT_DUMMY, global_attribute7,
620 p_cr_rec.global_attribute7 ),
621 global_attribute8 = DECODE( p_cr_rec.global_attribute8,
622 AR_TEXT_DUMMY, global_attribute8,
623 p_cr_rec.global_attribute8 ),
624 global_attribute9 = DECODE( p_cr_rec.global_attribute9,
625 AR_TEXT_DUMMY, global_attribute9,
626 p_cr_rec.global_attribute9 ),
627 global_attribute10 = DECODE( p_cr_rec.global_attribute10,
628 AR_TEXT_DUMMY, global_attribute10,
629 p_cr_rec.global_attribute10 ),
630 global_attribute11 = DECODE( p_cr_rec.global_attribute11,
631 AR_TEXT_DUMMY, global_attribute11,
632 p_cr_rec.global_attribute11 ),
633 global_attribute12 = DECODE( p_cr_rec.global_attribute12,
634 AR_TEXT_DUMMY, global_attribute12,
635 p_cr_rec.global_attribute12 ),
636 global_attribute13 = DECODE( p_cr_rec.global_attribute13,
637 AR_TEXT_DUMMY, global_attribute13,
638 p_cr_rec.global_attribute13 ),
639 global_attribute14 = DECODE( p_cr_rec.global_attribute14,
640 AR_TEXT_DUMMY, global_attribute14,
641 p_cr_rec.global_attribute14 ),
642 global_attribute15 = DECODE( p_cr_rec.global_attribute15,
643 AR_TEXT_DUMMY, global_attribute15,
644 p_cr_rec.global_attribute15 ),
645 global_attribute16 = DECODE( p_cr_rec.global_attribute16,
646 AR_TEXT_DUMMY, global_attribute16,
647 p_cr_rec.global_attribute16 ),
648 global_attribute17 = DECODE( p_cr_rec.global_attribute17,
649 AR_TEXT_DUMMY, global_attribute17,
650 p_cr_rec.global_attribute17 ),
651 global_attribute18 = DECODE( p_cr_rec.global_attribute18,
652 AR_TEXT_DUMMY, global_attribute18,
653 p_cr_rec.global_attribute18 ),
654 global_attribute19 = DECODE( p_cr_rec.global_attribute19,
655 AR_TEXT_DUMMY, global_attribute19,
656 p_cr_rec.global_attribute19 ),
657 global_attribute20 = DECODE( p_cr_rec.global_attribute20,
658 AR_TEXT_DUMMY, global_attribute20,
659 p_cr_rec.global_attribute20 ),
660 issuer_name = DECODE (p_cr_rec.issuer_name,
661 AR_TEXT_DUMMY, issuer_name,
662 p_cr_rec.issuer_name),
663 issue_date = DECODE (p_cr_rec.issue_date,
664 AR_DATE_DUMMY, issue_date,
665 p_cr_rec.issue_date),
666 issuer_bank_branch_id = DECODE (p_cr_rec.issuer_bank_branch_id,
667 AR_NUMBER_DUMMY, issuer_bank_branch_id,
668 p_cr_rec.issuer_bank_branch_id),
669 /* Bug 7427809 Obsoleted this column in R12
670 payment_server_order_num = DECODE (p_cr_rec.payment_server_order_num,
671 AR_TEXT_DUMMY, payment_server_order_num,
672 p_cr_rec.payment_server_order_num), */
673 approval_code = DECODE (p_cr_rec.approval_code,
674 AR_TEXT_DUMMY, approval_code,
675 p_cr_rec.approval_code),
676 tax_rate = DECODE (p_cr_rec.tax_rate,
677 AR_NUMBER_DUMMY, tax_rate,
678 p_cr_rec.tax_rate),
679 -- ARTA Changes
680 postmark_date = DECODE( p_cr_rec.postmark_date, AR_DATE_DUMMY,
681 postmark_date,p_cr_rec.postmark_date),
682 -- enhancement 2074220
683 application_notes = DECODE(p_cr_rec.application_notes,
684 AR_TEXT_DUMMY, application_notes,
685 p_cr_rec.application_notes),
686 rec_version_number = nvl(rec_version_number,1)+1, /* Bug fix 3226723*/
687 legal_entity_id = DECODE (p_cr_rec.legal_entity_id,
688 AR_NUMBER_DUMMY, legal_entity_id,
689 p_cr_rec.legal_entity_id), /* LE */
690 payment_trxn_extension_id = DECODE (p_cr_rec.payment_trxn_extension_id,
691 AR_NUMBER_DUMMY, payment_trxn_extension_id,
692 p_cr_rec.payment_trxn_extension_id), /* bichatte payment uptake*/
693 work_item_status_code = DECODE(work_item_status_code,
694 null, work_item_status_code,
695 DECODE (p_cr_rec.status,
696 'APP', 'CLOSED',
697 'REV', 'CLOSED',
698 'NSF', 'CLOSED',
699 'STOP', 'CLOSED',
700 'CC_CHARGEBACK_REV', 'CLOSED',
701 'UNAPP', decode(status , 'APP', 'NEW', work_item_status_code),
702 work_item_status_code)),
703 automatch_set_id = DECODE (p_cr_rec.automatch_set_id,
704 AR_NUMBER_DUMMY, automatch_set_id,
705 p_cr_rec.automatch_set_id),
706 autoapply_flag = DECODE (p_cr_rec.autoapply_flag,
707 AR_FLAG_DUMMY, autoapply_flag,
708 p_cr_rec.autoapply_flag)
709 WHERE
710 cash_receipt_id = p_cr_id; -- OSTEINME 3/12/01 bug 1683007
711
712 IF PG_DEBUG in ('Y', 'C') THEN
713 arp_util.debug('update_p: ' || 'after updating cash_receipts');
714 arp_util.debug('update_p: ' || 'exchange_rate = ' || to_char(p_cr_rec.exchange_rate));
715 arp_util.debug('update_p: ' || 'exchange date = ' || to_char(p_cr_rec.exchange_date));
716 END IF;
717 --
718
719 /*-----------------------------------+
720 | Calling Central MRC library for |
721 | MRC integration. |
722 +-----------------------------------*/
723
724 ar_mrc_engine.maintain_mrc_data(
725 p_event_mode => 'UPDATE',
726 p_table_name => 'AR_CASH_RECEIPTS',
727 p_mode => 'SINGLE',
728 p_key_value => p_cr_id);
729
730 --
731 IF PG_DEBUG in ('Y', 'C') THEN
732 arp_util.debug( 'arp_cash_receipts_pkg.update_p()-' );
733 END IF;
734 --
735 EXCEPTION
736 WHEN OTHERS THEN
737 IF PG_DEBUG in ('Y', 'C') THEN
738 arp_util.debug( 'EXCEPTION: arp_cash_receipts_pkg.update_p' );
739 END IF;
740 RAISE;
741 END update_p;
742 ---------------------------------------------------------------------------------
743 -- Caroline M Clyde October 22, 1997
744 -- Log 454787
745 --
746 -- Removed the UPDATE statement from this procedure. It now calls the new
747 -- update_p to physically do the update.
748 ---------------------------------------------------------------------------------
749 PROCEDURE update_p( p_cr_rec IN ar_cash_receipts%ROWTYPE ) IS
750 BEGIN
751 IF PG_DEBUG in ('Y', 'C') THEN
752 arp_util.debug( 'arp_cash_receipts_pkg.update_p()+' );
753 END IF;
754 --
755 arp_cash_receipts_pkg.update_p (p_cr_rec,
756 p_cr_rec.cash_receipt_id);
757 --
758 IF PG_DEBUG in ('Y', 'C') THEN
759 arp_util.debug( 'arp_cash_receipts_pkg.update_p()-' );
760 END IF;
761 --
762 END update_p;
763 --
764 PROCEDURE delete_p(
765 p_cr_id IN ar_cash_receipts.cash_receipt_id%TYPE ) IS
766 BEGIN
767 IF PG_DEBUG in ('Y', 'C') THEN
768 arp_util.debug( 'arp_cash_receipts_pkg.delete_p()+' );
769 END IF;
770 --
771 DELETE FROM ar_cash_receipts
772 WHERE cash_receipt_id = p_cr_id;
773
774 /*-----------------------------------+
775 | Calling Central MRC library for |
776 | MRC integration. |
777 +-----------------------------------*/
778
779 ar_mrc_engine.maintain_mrc_data(
780 p_event_mode => 'DELETE',
781 p_table_name => 'AR_CASH_RECEIPTS',
782 p_mode => 'SINGLE',
783 p_key_value => p_cr_id);
784
785 --
786 IF PG_DEBUG in ('Y', 'C') THEN
787 arp_util.debug( 'arp_cash_receipts_pkg.delete_p()-' );
788 END IF;
789 EXCEPTION
790 WHEN OTHERS THEN
791 IF PG_DEBUG in ('Y', 'C') THEN
792 arp_util.debug( 'Exception: arp_cash_receipts_pkg.delete_p' );
793 END IF;
794 RAISE;
795 END delete_p;
796 --
797 PROCEDURE lock_p(
798 p_cr_id IN ar_cash_receipts.cash_receipt_id%TYPE ) IS
799 l_cr_id ar_cash_receipts.cash_receipt_id%TYPE;
800 BEGIN
801 IF PG_DEBUG in ('Y', 'C') THEN
802 arp_util.debug( 'arp_cash_receipts_pkg.lock_p()+' );
803 END IF;
804 --
805 SELECT cash_receipt_id
806 INTO l_cr_id
807 FROM ar_cash_receipts
808 WHERE cash_receipt_id = p_cr_id
809 FOR UPDATE OF STATUS;
810 --
811 IF PG_DEBUG in ('Y', 'C') THEN
812 arp_util.debug( 'arp_cash_receipts_pkg.lock_p()-' );
813 END IF;
814 EXCEPTION
815 WHEN OTHERS THEN
816 IF PG_DEBUG in ('Y', 'C') THEN
817 arp_util.debug( 'EXCEPTION: arp_cash_receipts_pkg.lock_p' );
818 END IF;
819 RAISE;
820 END lock_p;
821 --
822 --
823 PROCEDURE nowaitlock_p(
824 p_cr_id IN ar_cash_receipts.cash_receipt_id%TYPE ) IS
825 l_cr_id ar_cash_receipts.cash_receipt_id%TYPE;
826 BEGIN
827 IF PG_DEBUG in ('Y', 'C') THEN
828 arp_util.debug( 'arp_cash_receipts_pkg.nowaitlock_p()+' );
829 END IF;
830 --
831 SELECT cash_receipt_id
832 INTO l_cr_id
833 FROM ar_cash_receipts
834 WHERE cash_receipt_id = p_cr_id
835 FOR UPDATE OF STATUS NOWAIT;
836 --
837 IF PG_DEBUG in ('Y', 'C') THEN
838 arp_util.debug( 'arp_cash_receipts_pkg.nowaitlock_p()-' );
839 END IF;
840 EXCEPTION
841 WHEN OTHERS THEN
842 IF PG_DEBUG in ('Y', 'C') THEN
843 arp_util.debug( 'EXCEPTION: arp_cash_receipts_pkg.nowaitlock_p' );
844 END IF;
845 RAISE;
846 END nowaitlock_p;
847 --
848 /* Bug fix 3226723
849 Locking procedure with additional parameter rec_version_number added */
850 PROCEDURE nowaitlock_version_p(
851 p_cr_id IN ar_cash_receipts.cash_receipt_id%TYPE,
852 p_rec_version_number IN ar_cash_receipts.rec_version_number%TYPE DEFAULT NULL ) IS
853 l_cr_id ar_cash_receipts.cash_receipt_id%TYPE;
854 BEGIN
855 IF PG_DEBUG in ('Y', 'C') THEN
856 arp_util.debug( 'arp_cash_receipts_pkg.nowaitlock_version_p()+' );
857 arp_util.debug('Cash receipt_id = '||to_char(p_cr_id));
858 arp_util.debug('receipt version number = '||to_char(p_rec_version_number));
859 END IF;
860 --
861
862 SELECT cash_receipt_id
863 INTO l_cr_id
864 FROM ar_cash_receipts
865 WHERE cash_receipt_id = p_cr_id
866 AND (
867 rec_version_number = p_rec_version_number
868 OR
869 (rec_version_number is NULL
870 AND p_rec_version_number = 1)
871 )
872 FOR UPDATE OF REC_VERSION_NUMBER NOWAIT;
873 --
874 IF PG_DEBUG in ('Y', 'C') THEN
875 arp_util.debug( 'arp_cash_receipts_pkg.nowaitlock_version_p()-' );
876 END IF;
877 EXCEPTION
878 WHEN OTHERS THEN
879 IF PG_DEBUG in ('Y', 'C') THEN
880 arp_util.debug( 'EXCEPTION: arp_cash_receipts_pkg.nowaitlock_version_p()' );
881 END IF;
882 RAISE;
883 END nowaitlock_version_p;
884
885 PROCEDURE update_version_number(p_cr_id IN ar_cash_receipts.cash_receipt_id%TYPE) IS
886 BEGIN
887 IF PG_DEBUG in ('Y', 'C') THEN
888 arp_util.debug( 'arp_cash_receipts_pkg.update_version_number()+' );
889 END IF;
890
891 update ar_cash_receipts
892 set rec_version_number = nvl(rec_version_number,1)+1
893 where cash_receipt_id = p_cr_id;
894
895 IF PG_DEBUG in ('Y', 'C') THEN
896 arp_util.debug( 'arp_cash_receipts_pkg.update_version_number()-');
897 END IF;
898 EXCEPTION
899 WHEN OTHERS THEN
900 IF PG_DEBUG in ('Y', 'C') THEN
901 arp_util.debug( 'EXCEPTION: arp_cash_receipts_pkg.update_version_number()' );
902 END IF;
903 RAISE;
904 END update_version_number;
905
906 /* End bug fix 3226723 */
907 PROCEDURE fetch_p(
908 p_cr_rec IN OUT NOCOPY ar_cash_receipts%ROWTYPE) IS
909 BEGIN
910 IF PG_DEBUG in ('Y', 'C') THEN
911 arp_util.debug( 'arp_cash_receipts_pkg.fetch_p()+' );
912 END IF;
913 --
914 SELECT *
915 INTO p_cr_rec
916 FROM ar_cash_receipts
917 WHERE cash_receipt_id = p_cr_rec.cash_receipt_id;
918 --
919 IF PG_DEBUG in ('Y', 'C') THEN
920 arp_util.debug( 'arp_cash_receipts_pkg.fetch_p()-' );
921 END IF;
922 --
923 EXCEPTION
924 WHEN OTHERS THEN
925 IF PG_DEBUG in ('Y', 'C') THEN
926 arp_util.debug('fetch_p: ' ||
927 'EXCEPTION: arp_cash_receipts_pkg.fetch error' );
928 END IF;
929 RAISE;
930 END fetch_p;
931 --
932 --
933 PROCEDURE lock_fetch_p(
934 p_cr_rec IN OUT NOCOPY ar_cash_receipts%ROWTYPE) IS
935 BEGIN
936 IF PG_DEBUG in ('Y', 'C') THEN
937 arp_util.debug( 'arp_cash_receipts_pkg.lock_fetch_p()+' );
938 END IF;
939 --
940 SELECT *
941 INTO p_cr_rec
942 FROM ar_cash_receipts
943 WHERE cash_receipt_id = p_cr_rec.cash_receipt_id
944 FOR UPDATE OF status;
945 --
946 IF PG_DEBUG in ('Y', 'C') THEN
947 arp_util.debug( 'arp_cash_receipts_pkg.lock_fetch_p()-' );
948 END IF;
949 --
950 EXCEPTION
951 WHEN OTHERS THEN
952 IF PG_DEBUG in ('Y', 'C') THEN
953 arp_util.debug('lock_fetch_p: ' ||
954 'EXCEPTION: arp_cash_receipts_pkg.lock_fetch_p' );
955 END IF;
956 RAISE;
957 END lock_fetch_p;
958 --
959 --
960 PROCEDURE nowaitlock_fetch_p(
961 p_cr_rec IN OUT NOCOPY ar_cash_receipts%ROWTYPE) IS
962 BEGIN
963 IF PG_DEBUG in ('Y', 'C') THEN
964 arp_util.debug( 'arp_cash_receipts_pkg.nowaitlock_fetch_p()+' );
965 END IF;
966 --
967 SELECT *
968 INTO p_cr_rec
969 FROM ar_cash_receipts
970 WHERE cash_receipt_id = p_cr_rec.cash_receipt_id
971 FOR UPDATE OF status NOWAIT;
972 --
973 IF PG_DEBUG in ('Y', 'C') THEN
974 arp_util.debug( 'arp_cash_receipts_pkg.nowaitlock_fetch_p()-' );
975 END IF;
976 --
977 EXCEPTION
978 WHEN OTHERS THEN
979 IF PG_DEBUG in ('Y', 'C') THEN
980 arp_util.debug('nowaitlock_fetch_p: ' ||
981 'EXCEPTION: arp_cash_receipts_pkg.nowaitlock_fetch_p' );
982 END IF;
983 RAISE;
984 END nowaitlock_fetch_p;
985 --
986 --
987 PROCEDURE lock_f_batch_id(
988 p_batch_id IN ar_batches.batch_id%TYPE ) IS
989 --
990 CURSOR lock_C IS
991 SELECT 'lock'
992 FROM ar_cash_receipts cr,
993 ar_cash_receipt_history crh
994 WHERE cr.cash_receipt_id = crh.cash_receipt_id
995 AND crh.batch_id = p_batch_id
996 FOR UPDATE OF cr.status;
997 --
998 BEGIN
999 IF PG_DEBUG in ('Y', 'C') THEN
1000 arp_util.debug( 'arp_cash_receipts_pkg.lock_f_batch_id()+' );
1001 END IF;
1002 --
1003 OPEN lock_C;
1004 CLOSE lock_C;
1005 --
1006 IF PG_DEBUG in ('Y', 'C') THEN
1007 arp_util.debug( 'arp_cash_receipts_pkg.lock_f_batch_id()-' );
1008 END IF;
1009 --
1010 EXCEPTION
1011 WHEN OTHERS THEN
1012 IF lock_C%ISOPEN THEN
1013 CLOSE lock_C;
1014 END IF;
1015 --
1016 IF PG_DEBUG in ('Y', 'C') THEN
1017 arp_util.debug('lock_f_batch_id: ' ||
1018 'EXCEPTION: arp_cash_receipts_pkg.lock_f_batch_id' );
1019 END IF;
1020 RAISE;
1021 END lock_f_batch_id;
1022 --
1023 --
1024 PROCEDURE nowaitlock_f_batch_id(
1025 p_batch_id IN ar_batches.batch_id%TYPE ) IS
1026 --
1027 CURSOR lock_C IS
1028 SELECT 'lock'
1029 FROM ar_cash_receipts cr,
1030 ar_cash_receipt_history crh
1031 WHERE cr.cash_receipt_id = crh.cash_receipt_id
1032 AND crh.batch_id = p_batch_id
1033 FOR UPDATE OF cr.status NOWAIT;
1034 --
1035 BEGIN
1036 IF PG_DEBUG in ('Y', 'C') THEN
1037 arp_util.debug( 'arp_cash_receipts_pkg.nowaitlock_f_batch_id()+' );
1038 END IF;
1039 --
1040 OPEN lock_C;
1041 CLOSE lock_C;
1042 --
1043 IF PG_DEBUG in ('Y', 'C') THEN
1044 arp_util.debug( 'arp_cash_receipts_pkg.nowaitlock_f_batch_id()-' );
1045 END IF;
1046 --
1047 EXCEPTION
1048 WHEN OTHERS THEN
1049 IF lock_C%ISOPEN THEN
1050 CLOSE lock_C;
1051 END IF;
1052 --
1053 IF PG_DEBUG in ('Y', 'C') THEN
1054 arp_util.debug('nowaitlock_f_batch_id: ' ||
1055 'EXCEPTION: arp_cash_receipts_pkg.nowaitlock_f_batch_id' );
1056 END IF;
1057 RAISE;
1058 END nowaitlock_f_batch_id;
1059 --
1060 --
1061 PROCEDURE lock_compare_p( p_cr_rec IN ar_cash_receipts%ROWTYPE ) IS
1062
1063 l_new_cr_rec ar_cash_receipts%ROWTYPE;
1064 l_exchange_date DATE;
1065
1066 BEGIN
1067 IF PG_DEBUG in ('Y', 'C') THEN
1068 arp_util.debug('arp_cash_receipts_pkg.lock_compare_p()+');
1069 END IF;
1070
1071 /* For testing only:
1072
1073 IF PG_DEBUG in ('Y', 'C') THEN
1074 arp_standard.debug('lock_compare_p: ' || 'Exchange date: ' ||
1075 TO_CHAR(p_cr_rec.exchange_date, 'DD-MON-YYYY HH24-MM-SS'));
1076 END IF;
1077
1078 SELECT exchange_date
1079 INTO l_exchange_date
1080 FROM ar_cash_receipts cr
1081 WHERE cr.cash_receipt_id = p_cr_rec.cash_receipt_id
1082 AND TO_CHAR(cr.exchange_date,'DD-MON-RR') =
1083 TO_CHAR(p_cr_rec.exchange_date,'DD-MON-RR');
1084
1085 IF PG_DEBUG in ('Y', 'C') THEN
1086 arp_standard.debug('lock_compare_p: ' || 'Exchange date in DB: ' ||
1087 TO_CHAR(l_exchange_date, 'DD-MON-YYYY HH24-MM-SS'));
1088 END IF;
1089
1090 */
1091
1092 SELECT *
1093 INTO
1094 l_new_cr_rec
1095 FROM
1096 ar_cash_receipts cr
1097 WHERE
1098 cr.cash_receipt_id = p_cr_rec.cash_receipt_id
1099 AND
1100 NVL(cr.amount, AR_NUMBER_DUMMY) =
1101 NVL(
1102 DECODE(p_cr_rec.amount,
1103 AR_NUMBER_DUMMY, cr.amount,
1104 p_cr_rec.amount),
1105 AR_NUMBER_DUMMY
1106 )
1107 AND
1108 NVL(cr.set_of_books_id , AR_NUMBER_DUMMY) =
1109 NVL(
1110 DECODE(p_cr_rec.set_of_books_id,
1111 AR_NUMBER_DUMMY, cr.set_of_books_id,
1112 p_cr_rec.set_of_books_id),
1113 AR_NUMBER_DUMMY
1114 )
1115 AND
1116 NVL(cr.currency_code , AR_TEXT_DUMMY) =
1117 NVL(
1118 DECODE(p_cr_rec.currency_code,
1119 AR_TEXT_DUMMY, cr.currency_code,
1120 p_cr_rec.currency_code),
1121 AR_TEXT_DUMMY
1122 )
1123 AND
1124 NVL(cr.receivables_trx_id , AR_NUMBER_DUMMY) =
1125 NVL(
1126 DECODE(p_cr_rec.receivables_trx_id,
1127 AR_NUMBER_DUMMY, cr.receivables_trx_id,
1128 p_cr_rec.receivables_trx_id),
1129 AR_NUMBER_DUMMY
1130 )
1131 AND
1132 NVL(cr.pay_from_customer , AR_NUMBER_DUMMY) =
1133 NVL(
1134 DECODE(p_cr_rec.pay_from_customer ,
1135 AR_NUMBER_DUMMY, cr.pay_from_customer,
1136 p_cr_rec.pay_from_customer),
1137 AR_NUMBER_DUMMY
1138 )
1139 AND
1140 NVL(cr.status , AR_TEXT_DUMMY) =
1141 NVL(
1142 DECODE(p_cr_rec.status ,
1143 AR_TEXT_DUMMY, cr.status,
1144 p_cr_rec.status),
1145 AR_TEXT_DUMMY
1146 )
1147 AND
1148 NVL(cr.type , AR_TEXT_DUMMY) =
1149 NVL(
1150 DECODE(p_cr_rec.type ,
1151 AR_TEXT_DUMMY, cr.type,
1152 p_cr_rec.type),
1153 AR_TEXT_DUMMY
1154 )
1155 AND
1156 NVL(cr.receipt_number , AR_TEXT_DUMMY) =
1157 NVL(
1158 DECODE(p_cr_rec.receipt_number ,
1159 AR_TEXT_DUMMY, cr.receipt_number,
1160 p_cr_rec.receipt_number),
1161 AR_TEXT_DUMMY
1162 )
1163 AND
1164 NVL(cr.receipt_date , AR_DATE_DUMMY) =
1165 NVL(
1166 DECODE(p_cr_rec.receipt_date ,
1167 AR_DATE_DUMMY, cr.receipt_date,
1168 p_cr_rec.receipt_date),
1169 AR_DATE_DUMMY
1170 )
1171 AND
1172 NVL(cr.misc_payment_source , AR_TEXT_DUMMY) =
1173 NVL(
1174 DECODE(p_cr_rec.misc_payment_source ,
1175 AR_TEXT_DUMMY, cr.misc_payment_source,
1176 p_cr_rec.misc_payment_source),
1177 AR_TEXT_DUMMY
1178 )
1179 AND
1180 NVL(cr.comments , AR_TEXT_DUMMY) =
1181 NVL(
1182 DECODE(p_cr_rec.comments ,
1183 AR_TEXT_DUMMY, cr.comments,
1184 p_cr_rec.comments),
1185 AR_TEXT_DUMMY
1186 )
1187 AND
1188 NVL(cr.distribution_set_id , AR_NUMBER_DUMMY) =
1189 NVL(
1190 DECODE(p_cr_rec.distribution_set_id ,
1191 AR_NUMBER_DUMMY, cr.distribution_set_id,
1192 p_cr_rec.distribution_set_id),
1193 AR_NUMBER_DUMMY
1194 )
1195 AND
1196 NVL(cr.reversal_date , AR_DATE_DUMMY) =
1197 NVL(
1198 DECODE(p_cr_rec.reversal_date ,
1199 AR_DATE_DUMMY, cr.reversal_date,
1200 p_cr_rec.reversal_date),
1201 AR_DATE_DUMMY
1202 )
1203 AND
1204 NVL(cr.reversal_category , AR_TEXT_DUMMY) =
1205 NVL(
1206 DECODE(p_cr_rec.reversal_category ,
1207 AR_TEXT_DUMMY, cr.reversal_category,
1208 p_cr_rec.reversal_category),
1209 AR_TEXT_DUMMY
1210 )
1211 AND
1212 NVL(cr.reversal_reason_code , AR_TEXT_DUMMY) =
1213 NVL(
1214 DECODE(p_cr_rec.reversal_reason_code ,
1215 AR_TEXT_DUMMY, cr.reversal_reason_code,
1216 p_cr_rec.reversal_reason_code),
1217 AR_TEXT_DUMMY
1218 )
1219 AND
1220 NVL(cr.reversal_comments , AR_TEXT_DUMMY) =
1221 NVL(
1222 DECODE(p_cr_rec.reversal_comments ,
1223 AR_TEXT_DUMMY, cr.reversal_comments,
1224 p_cr_rec.reversal_comments),
1225 AR_TEXT_DUMMY
1226 )
1227 AND
1228 NVL(cr.exchange_rate_type , AR_TEXT_DUMMY) =
1229 NVL(
1230 DECODE(p_cr_rec.exchange_rate_type ,
1231 AR_TEXT_DUMMY, cr.exchange_rate_type,
1232 p_cr_rec.exchange_rate_type),
1233 AR_TEXT_DUMMY
1234 )
1235 AND
1236 NVL(cr.exchange_rate , AR_NUMBER_DUMMY) =
1237 NVL(
1238 DECODE(p_cr_rec.exchange_rate ,
1239 AR_NUMBER_DUMMY, cr.exchange_rate,
1240 p_cr_rec.exchange_rate),
1241 AR_NUMBER_DUMMY
1242 )
1243 AND
1244 NVL(cr.exchange_date , AR_DATE_DUMMY) =
1245 NVL(
1246 DECODE(p_cr_rec.exchange_date ,
1247 AR_DATE_DUMMY, cr.exchange_date,
1248 p_cr_rec.exchange_date),
1249 AR_DATE_DUMMY
1250 )
1251 AND
1252 NVL(cr.attribute_category , AR_TEXT_DUMMY) =
1253 NVL(
1254 DECODE(p_cr_rec.attribute_category ,
1255 AR_TEXT_DUMMY, cr.attribute_category,
1256 p_cr_rec.attribute_category),
1257 AR_TEXT_DUMMY
1258 )
1259 AND
1260 NVL(cr.attribute1 , AR_TEXT_DUMMY) =
1261 NVL(
1262 DECODE(p_cr_rec.attribute1 ,
1263 AR_TEXT_DUMMY, cr.attribute1,
1264 p_cr_rec.attribute1),
1265 AR_TEXT_DUMMY
1266 )
1267 AND
1268 NVL(cr.attribute2 , AR_TEXT_DUMMY) =
1269 NVL(
1270 DECODE(p_cr_rec.attribute2 ,
1271 AR_TEXT_DUMMY, cr.attribute2,
1272 p_cr_rec.attribute2),
1273 AR_TEXT_DUMMY
1274 )
1275 AND
1276 NVL(cr.attribute3 , AR_TEXT_DUMMY) =
1277 NVL(
1278 DECODE(p_cr_rec.attribute3 ,
1279 AR_TEXT_DUMMY, cr.attribute3,
1280 p_cr_rec.attribute3),
1281 AR_TEXT_DUMMY
1282 )
1283 AND
1284 NVL(cr.attribute4 , AR_TEXT_DUMMY) =
1285 NVL(
1286 DECODE(p_cr_rec.attribute4 ,
1287 AR_TEXT_DUMMY, cr.attribute4,
1288 p_cr_rec.attribute4),
1289 AR_TEXT_DUMMY
1290 )
1291 AND
1292 NVL(cr.attribute5 , AR_TEXT_DUMMY) =
1293 NVL(
1294 DECODE(p_cr_rec.attribute5 ,
1295 AR_TEXT_DUMMY, cr.attribute5,
1296 p_cr_rec.attribute5),
1297 AR_TEXT_DUMMY
1298 )
1299 AND
1300 NVL(cr.attribute6 , AR_TEXT_DUMMY) =
1301 NVL(
1302 DECODE(p_cr_rec.attribute6 ,
1303 AR_TEXT_DUMMY, cr.attribute6,
1304 p_cr_rec.attribute6),
1305 AR_TEXT_DUMMY
1306 )
1307 AND
1308 NVL(cr.attribute7 , AR_TEXT_DUMMY) =
1309 NVL(
1310 DECODE(p_cr_rec.attribute7 ,
1311 AR_TEXT_DUMMY, cr.attribute7,
1312 p_cr_rec.attribute7),
1313 AR_TEXT_DUMMY
1314 )
1315 AND
1316 NVL(cr.attribute8 , AR_TEXT_DUMMY) =
1317 NVL(
1318 DECODE(p_cr_rec.attribute8 ,
1319 AR_TEXT_DUMMY, cr.attribute8,
1320 p_cr_rec.attribute8),
1321 AR_TEXT_DUMMY
1322 )
1323 AND
1324 NVL(cr.attribute9 , AR_TEXT_DUMMY) =
1325 NVL(
1326 DECODE(p_cr_rec.attribute9 ,
1327 AR_TEXT_DUMMY, cr.attribute9,
1328 p_cr_rec.attribute9),
1329 AR_TEXT_DUMMY
1330 )
1331 AND
1332 NVL(cr.attribute10 , AR_TEXT_DUMMY) =
1333 NVL(
1334 DECODE(p_cr_rec.attribute10 ,
1335 AR_TEXT_DUMMY, cr.attribute10,
1336 p_cr_rec.attribute10),
1337 AR_TEXT_DUMMY
1338 )
1339 AND
1340 NVL(cr.attribute11 , AR_TEXT_DUMMY) =
1341 NVL(
1342 DECODE(p_cr_rec.attribute11 ,
1343 AR_TEXT_DUMMY, cr.attribute11,
1344 p_cr_rec.attribute11),
1345 AR_TEXT_DUMMY
1346 )
1347 AND
1348 NVL(cr.attribute12 , AR_TEXT_DUMMY) =
1349 NVL(
1350 DECODE(p_cr_rec.attribute12 ,
1351 AR_TEXT_DUMMY, cr.attribute12,
1352 p_cr_rec.attribute12),
1353 AR_TEXT_DUMMY
1354 )
1355 AND
1356 NVL(cr.attribute13 , AR_TEXT_DUMMY) =
1357 NVL(
1358 DECODE(p_cr_rec.attribute13 ,
1359 AR_TEXT_DUMMY, cr.attribute13,
1360 p_cr_rec.attribute13),
1361 AR_TEXT_DUMMY
1362 )
1363 AND
1364 NVL(cr.attribute14 , AR_TEXT_DUMMY) =
1365 NVL(
1366 DECODE(p_cr_rec.attribute14 ,
1367 AR_TEXT_DUMMY, cr.attribute14,
1368 p_cr_rec.attribute14),
1369 AR_TEXT_DUMMY
1370 )
1371 AND
1372 NVL(cr.attribute15 , AR_TEXT_DUMMY) =
1373 NVL(
1374 DECODE(p_cr_rec.attribute15 ,
1375 AR_TEXT_DUMMY, cr.attribute15,
1376 p_cr_rec.attribute15),
1377 AR_TEXT_DUMMY
1378 )
1379 AND
1380 NVL(cr.receipt_method_id , AR_NUMBER_DUMMY) =
1381 NVL(
1382 DECODE(p_cr_rec.receipt_method_id ,
1383 AR_NUMBER_DUMMY, cr.receipt_method_id,
1384 p_cr_rec.receipt_method_id),
1385 AR_NUMBER_DUMMY
1386 )
1387 AND
1388 NVL(cr.confirmed_flag , AR_FLAG_DUMMY) =
1389 NVL(
1390 DECODE(p_cr_rec.confirmed_flag ,
1391 AR_FLAG_DUMMY, cr.confirmed_flag,
1392 p_cr_rec.confirmed_flag),
1393 AR_FLAG_DUMMY
1394 )
1395 AND
1396 NVL(cr.customer_bank_account_id , AR_NUMBER_DUMMY) =
1397 NVL(
1398 DECODE(p_cr_rec.customer_bank_account_id ,
1399 AR_NUMBER_DUMMY, cr.customer_bank_account_id,
1400 p_cr_rec.customer_bank_account_id),
1401 AR_NUMBER_DUMMY
1402 )
1403 AND
1404 NVL(cr.customer_bank_branch_id , AR_NUMBER_DUMMY) =
1405 NVL(
1406 DECODE(p_cr_rec.customer_bank_branch_id ,
1407 AR_NUMBER_DUMMY, cr.customer_bank_branch_id,
1408 p_cr_rec.customer_bank_branch_id),
1409 AR_NUMBER_DUMMY
1410 )
1411 AND
1412 NVL(cr.customer_site_use_id , AR_NUMBER_DUMMY) =
1413 NVL(
1414 DECODE(p_cr_rec.customer_site_use_id ,
1415 AR_NUMBER_DUMMY, cr.customer_site_use_id,
1416 p_cr_rec.customer_site_use_id),
1417 AR_NUMBER_DUMMY
1418 )
1419 AND
1420 NVL(cr.deposit_date , AR_DATE_DUMMY) =
1421 NVL(
1422 DECODE(p_cr_rec.deposit_date ,
1423 AR_DATE_DUMMY, cr.deposit_date,
1424 p_cr_rec.deposit_date),
1425 AR_DATE_DUMMY
1426 )
1427 AND
1428 NVL(cr.remit_bank_acct_use_id , AR_NUMBER_DUMMY) =
1429 NVL(
1430 DECODE(p_cr_rec.remit_bank_acct_use_id ,
1431 AR_NUMBER_DUMMY, cr.remit_bank_acct_use_id,
1432 p_cr_rec.remit_bank_acct_use_id),
1433 AR_NUMBER_DUMMY
1434 )
1435 AND
1436 NVL(cr.selected_for_factoring_flag , AR_FLAG_DUMMY) =
1437 NVL(
1438 DECODE(p_cr_rec.selected_for_factoring_flag ,
1439 AR_FLAG_DUMMY, cr.selected_for_factoring_flag,
1440 p_cr_rec.selected_for_factoring_flag),
1441 AR_FLAG_DUMMY
1442 )
1443 AND
1444 NVL(cr.selected_remittance_batch_id , AR_NUMBER_DUMMY) =
1445 NVL(
1446 DECODE(p_cr_rec.selected_remittance_batch_id ,
1447 AR_NUMBER_DUMMY, cr.selected_remittance_batch_id,
1448 p_cr_rec.selected_remittance_batch_id),
1449 AR_NUMBER_DUMMY
1450 )
1451 AND
1452 NVL(cr.factor_discount_amount , AR_NUMBER_DUMMY) =
1453 NVL(
1454 DECODE(p_cr_rec.factor_discount_amount ,
1455 AR_NUMBER_DUMMY, cr.factor_discount_amount,
1456 p_cr_rec.factor_discount_amount),
1457 AR_NUMBER_DUMMY
1458 )
1459 AND
1460 NVL(cr.ussgl_transaction_code , AR_TEXT_DUMMY) =
1461 NVL(
1462 DECODE(p_cr_rec.ussgl_transaction_code ,
1463 AR_TEXT_DUMMY, cr.ussgl_transaction_code,
1464 p_cr_rec.ussgl_transaction_code),
1465 AR_TEXT_DUMMY
1466 )
1467 AND
1468 NVL(cr.ussgl_transaction_code_context , AR_TEXT_DUMMY) =
1469 NVL(
1470 DECODE(p_cr_rec.ussgl_transaction_code_context ,
1471 AR_TEXT_DUMMY, cr.ussgl_transaction_code_context,
1472 p_cr_rec.ussgl_transaction_code_context),
1473 AR_TEXT_DUMMY
1474 )
1475 AND
1476 NVL(cr.doc_sequence_id , AR_NUMBER_DUMMY) =
1477 NVL(
1478 DECODE(p_cr_rec.doc_sequence_id ,
1479 AR_NUMBER_DUMMY, cr.doc_sequence_id,
1480 p_cr_rec.doc_sequence_id),
1481 AR_NUMBER_DUMMY
1482 )
1483 AND
1484 NVL(cr.doc_sequence_value , AR_NUMBER_DUMMY) =
1485 NVL(
1486 DECODE(p_cr_rec.doc_sequence_value ,
1487 AR_NUMBER_DUMMY, cr.doc_sequence_value,
1488 p_cr_rec.doc_sequence_value),
1489 AR_NUMBER_DUMMY
1490 )
1491 AND
1492 NVL(cr.vat_tax_id , AR_NUMBER_DUMMY) =
1493 NVL(
1494 DECODE(p_cr_rec.vat_tax_id ,
1495 AR_NUMBER_DUMMY, cr.vat_tax_id,
1496 p_cr_rec.vat_tax_id),
1497 AR_NUMBER_DUMMY
1498 )
1499 AND
1500 NVL(cr.reference_type , AR_TEXT_DUMMY) =
1501 NVL(
1502 DECODE(p_cr_rec.reference_type ,
1503 AR_TEXT_DUMMY, cr.reference_type,
1504 p_cr_rec.reference_type),
1505 AR_TEXT_DUMMY
1506 )
1507 AND
1508 NVL(cr.reference_id , AR_NUMBER_DUMMY) =
1509 NVL(
1510 DECODE(p_cr_rec.reference_id ,
1511 AR_NUMBER_DUMMY, cr.reference_id,
1512 p_cr_rec.reference_id),
1513 AR_NUMBER_DUMMY
1514 )
1515 AND
1516 NVL(cr.customer_receipt_reference , AR_TEXT_DUMMY) =
1517 NVL(
1518 DECODE(p_cr_rec.customer_receipt_reference ,
1519 AR_TEXT_DUMMY, cr.customer_receipt_reference,
1520 p_cr_rec.customer_receipt_reference),
1521 AR_TEXT_DUMMY
1522 )
1523 AND
1524 NVL(cr.override_remit_account_flag , 'Y') =
1525 NVL(
1526 DECODE(p_cr_rec.override_remit_account_flag ,
1527 AR_FLAG_DUMMY, NVL(cr.override_remit_account_flag, 'Y'),
1528 p_cr_rec.override_remit_account_flag),
1529 AR_FLAG_DUMMY
1530 )
1531 AND
1532 NVL(cr.anticipated_clearing_date , AR_DATE_DUMMY) =
1533 NVL(
1534 DECODE(p_cr_rec.anticipated_clearing_date ,
1535 AR_DATE_DUMMY, cr.anticipated_clearing_date,
1536 p_cr_rec.anticipated_clearing_date),
1537 AR_DATE_DUMMY
1538 )
1539 AND
1540 NVL(cr.global_attribute_category , AR_TEXT_DUMMY) =
1541 NVL(
1542 DECODE(p_cr_rec.global_attribute_category ,
1543 AR_TEXT_DUMMY, cr.global_attribute_category,
1544 p_cr_rec.global_attribute_category),
1545 AR_TEXT_DUMMY
1546 )
1547 AND
1548 NVL(cr.global_attribute1 , AR_TEXT_DUMMY) =
1549 NVL(
1550 DECODE(p_cr_rec.global_attribute1 ,
1551 AR_TEXT_DUMMY, cr.global_attribute1,
1552 p_cr_rec.global_attribute1),
1553 AR_TEXT_DUMMY
1554 )
1555 AND
1556 NVL(cr.global_attribute2 , AR_TEXT_DUMMY) =
1557 NVL(
1558 DECODE(p_cr_rec.global_attribute2 ,
1559 AR_TEXT_DUMMY, cr.global_attribute2,
1560 p_cr_rec.global_attribute2),
1561 AR_TEXT_DUMMY
1562 )
1563 AND
1564 NVL(cr.global_attribute3 , AR_TEXT_DUMMY) =
1565 NVL(
1566 DECODE(p_cr_rec.global_attribute3 ,
1567 AR_TEXT_DUMMY, cr.global_attribute3,
1568 p_cr_rec.global_attribute3),
1569 AR_TEXT_DUMMY
1570 )
1571 AND
1572 NVL(cr.global_attribute4 , AR_TEXT_DUMMY) =
1573 NVL(
1574 DECODE(p_cr_rec.global_attribute4 ,
1575 AR_TEXT_DUMMY, cr.global_attribute4,
1576 p_cr_rec.global_attribute4),
1577 AR_TEXT_DUMMY
1578 )
1579 AND
1580 NVL(cr.global_attribute5 , AR_TEXT_DUMMY) =
1581 NVL(
1582 DECODE(p_cr_rec.global_attribute5 ,
1583 AR_TEXT_DUMMY, cr.global_attribute5,
1584 p_cr_rec.global_attribute5),
1585 AR_TEXT_DUMMY
1586 )
1587 AND
1588 NVL(cr.global_attribute6 , AR_TEXT_DUMMY) =
1589 NVL(
1590 DECODE(p_cr_rec.global_attribute6 ,
1591 AR_TEXT_DUMMY, cr.global_attribute6,
1592 p_cr_rec.global_attribute6),
1593 AR_TEXT_DUMMY
1594 )
1595 AND
1596 NVL(cr.global_attribute7 , AR_TEXT_DUMMY) =
1597 NVL(
1598 DECODE(p_cr_rec.global_attribute7 ,
1599 AR_TEXT_DUMMY, cr.global_attribute7,
1600 p_cr_rec.global_attribute7),
1601 AR_TEXT_DUMMY
1602 )
1603 AND
1604 NVL(cr.global_attribute8 , AR_TEXT_DUMMY) =
1605 NVL(
1606 DECODE(p_cr_rec.global_attribute8 ,
1607 AR_TEXT_DUMMY, cr.global_attribute8,
1608 p_cr_rec.global_attribute8),
1609 AR_TEXT_DUMMY
1610 )
1611 AND
1612 NVL(cr.global_attribute9 , AR_TEXT_DUMMY) =
1613 NVL(
1614 DECODE(p_cr_rec.global_attribute9 ,
1615 AR_TEXT_DUMMY, cr.global_attribute9,
1616 p_cr_rec.global_attribute9),
1617 AR_TEXT_DUMMY
1618 )
1619 AND
1620 NVL(cr.global_attribute10 , AR_TEXT_DUMMY) =
1621 NVL(
1622 DECODE(p_cr_rec.global_attribute10 ,
1623 AR_TEXT_DUMMY, cr.global_attribute10,
1624 p_cr_rec.global_attribute10),
1625 AR_TEXT_DUMMY
1626 )
1627 AND
1628 NVL(cr.global_attribute11 , AR_TEXT_DUMMY) =
1629 NVL(
1630 DECODE(p_cr_rec.global_attribute11 ,
1631 AR_TEXT_DUMMY, cr.global_attribute11,
1632 p_cr_rec.global_attribute11),
1633 AR_TEXT_DUMMY
1634 )
1635 AND
1636 NVL(cr.global_attribute12 , AR_TEXT_DUMMY) =
1637 NVL(
1638 DECODE(p_cr_rec.global_attribute12 ,
1639 AR_TEXT_DUMMY, cr.global_attribute12,
1640 p_cr_rec.global_attribute12),
1641 AR_TEXT_DUMMY
1642 )
1643 AND
1644 NVL(cr.global_attribute13 , AR_TEXT_DUMMY) =
1645 NVL(
1646 DECODE(p_cr_rec.global_attribute13 ,
1647 AR_TEXT_DUMMY, cr.global_attribute13,
1648 p_cr_rec.global_attribute13),
1649 AR_TEXT_DUMMY
1650 )
1651 AND
1652 NVL(cr.global_attribute14 , AR_TEXT_DUMMY) =
1653 NVL(
1654 DECODE(p_cr_rec.global_attribute14 ,
1655 AR_TEXT_DUMMY, cr.global_attribute14,
1656 p_cr_rec.global_attribute14),
1657 AR_TEXT_DUMMY
1658 )
1659 AND
1660 NVL(cr.global_attribute15 , AR_TEXT_DUMMY) =
1661 NVL(
1662 DECODE(p_cr_rec.global_attribute15 ,
1663 AR_TEXT_DUMMY, cr.global_attribute15,
1664 p_cr_rec.global_attribute15),
1665 AR_TEXT_DUMMY
1666 )
1667 AND
1668 NVL(cr.global_attribute16 , AR_TEXT_DUMMY) =
1669 NVL(
1670 DECODE(p_cr_rec.global_attribute16 ,
1671 AR_TEXT_DUMMY, cr.global_attribute16,
1672 p_cr_rec.global_attribute16),
1673 AR_TEXT_DUMMY
1674 )
1675 AND
1676 NVL(cr.global_attribute17 , AR_TEXT_DUMMY) =
1677 NVL(
1678 DECODE(p_cr_rec.global_attribute17 ,
1679 AR_TEXT_DUMMY, cr.global_attribute17,
1680 p_cr_rec.global_attribute17),
1681 AR_TEXT_DUMMY
1682 )
1683 AND
1684 NVL(cr.global_attribute18 , AR_TEXT_DUMMY) =
1685 NVL(
1686 DECODE(p_cr_rec.global_attribute18 ,
1687 AR_TEXT_DUMMY, cr.global_attribute18,
1688 p_cr_rec.global_attribute18),
1689 AR_TEXT_DUMMY
1690 )
1691 AND
1692 NVL(cr.global_attribute19 , AR_TEXT_DUMMY) =
1693 NVL(
1694 DECODE(p_cr_rec.global_attribute19 ,
1695 AR_TEXT_DUMMY, cr.global_attribute19,
1696 p_cr_rec.global_attribute19),
1697 AR_TEXT_DUMMY
1698 )
1699 AND
1700 NVL(cr.global_attribute20 , AR_TEXT_DUMMY) =
1701 NVL(
1702 DECODE(p_cr_rec.global_attribute20 ,
1703 AR_TEXT_DUMMY, cr.global_attribute20,
1704 p_cr_rec.global_attribute20),
1705 AR_TEXT_DUMMY
1706 )
1707 AND NVL (cr.issuer_name, AR_TEXT_DUMMY) =
1708 NVL (DECODE (p_cr_rec.issuer_name,
1709 AR_TEXT_DUMMY, cr.issuer_name,
1710 p_cr_rec.issuer_name),
1711 AR_TEXT_DUMMY)
1712 AND NVL (cr.issue_date, AR_DATE_DUMMY) =
1713 NVL (DECODE (p_cr_rec.issue_date,
1714 AR_DATE_DUMMY, cr.issue_date,
1715 p_cr_rec.issue_date),
1716 AR_DATE_DUMMY)
1717 AND NVL (cr.issuer_bank_branch_id, AR_NUMBER_DUMMY) =
1718 NVL (DECODE (p_cr_rec.issuer_bank_branch_id,
1719 AR_NUMBER_DUMMY, cr.issuer_bank_branch_id,
1720 p_cr_rec.issuer_bank_branch_id),
1721 AR_NUMBER_DUMMY)
1722 /* Bug 7427809 Obsoleted this column in R12
1723 AND NVL (cr.payment_server_order_num, AR_TEXT_DUMMY) =
1724 NVL (DECODE (p_cr_rec.payment_server_order_num,
1725 AR_TEXT_DUMMY, cr.payment_server_order_num,
1726 p_cr_rec.payment_server_order_num),
1727 AR_TEXT_DUMMY) */
1728 AND NVL (cr.approval_code, AR_TEXT_DUMMY) =
1729 NVL (DECODE (p_cr_rec.approval_code,
1730 AR_TEXT_DUMMY, cr.approval_code,
1731 p_cr_rec.approval_code),
1732 AR_TEXT_DUMMY)
1733 -- ARTA Changes
1734 AND
1735 NVL(cr.postmark_date , AR_DATE_DUMMY) =
1736 NVL(DECODE(p_cr_rec.postmark_date,
1737 AR_DATE_DUMMY, cr.postmark_date,
1738 p_cr_rec.postmark_date),
1739 AR_DATE_DUMMY)
1740 AND
1741 -- enhancement 2074220
1742 NVL(cr.application_notes, AR_TEXT_DUMMY) =
1743 NVL(DECODE(p_cr_rec.application_notes,
1744 AR_TEXT_DUMMY, cr.application_notes,
1745 p_cr_rec.application_notes),
1746 AR_TEXT_DUMMY)
1747 /* Bug fix 3226723 */
1748 AND
1749 (NVL(cr.rec_version_number,AR_NUMBER_DUMMY) =
1750 NVL(DECODE(p_cr_rec.rec_version_number,
1751 AR_NUMBER_DUMMY,cr.rec_version_number,
1752 p_cr_rec.rec_version_number),
1753 AR_NUMBER_DUMMY)
1754 OR
1755 (cr.rec_version_number is NULL
1756 AND p_cr_rec.rec_version_number = 1)
1757 )
1758 /* Legal entity project */
1759 AND
1760 NVL(cr.legal_entity_id , AR_NUMBER_DUMMY) =
1761 NVL(
1762 DECODE(p_cr_rec.legal_entity_id,
1763 AR_NUMBER_DUMMY, cr.legal_entity_id,
1764 p_cr_rec.legal_entity_id),
1765 AR_NUMBER_DUMMY
1766 )
1767 /* bichatte payment uptake */
1768 AND
1769 NVL(cr.payment_trxn_extension_id , AR_NUMBER_DUMMY) =
1770 NVL(
1771 DECODE(p_cr_rec.payment_trxn_extension_id,
1772 AR_NUMBER_DUMMY, cr.payment_trxn_extension_id,
1773 p_cr_rec.payment_trxn_extension_id),
1774 AR_NUMBER_DUMMY
1775 )
1776 /* ER Automatch Application */
1777 AND
1778 NVL(cr.automatch_set_id , AR_NUMBER_DUMMY) =
1779 NVL(
1780 DECODE(p_cr_rec.automatch_set_id,
1781 AR_NUMBER_DUMMY, cr.automatch_set_id,
1782 p_cr_rec.automatch_set_id),
1783 AR_NUMBER_DUMMY
1784 )
1785 AND
1786 NVL(cr.autoapply_flag , AR_FLAG_DUMMY) =
1787 NVL(
1788 DECODE(p_cr_rec.autoapply_flag,
1789 AR_FLAG_DUMMY, cr.autoapply_flag,
1790 p_cr_rec.autoapply_flag),
1791 AR_FLAG_DUMMY
1792 )
1793 FOR UPDATE NOWAIT;
1794
1795 IF PG_DEBUG in ('Y', 'C') THEN
1796 arp_util.debug('arp_cash_receipts_pkg.lock_compare_p()-');
1797 END IF;
1798
1799 EXCEPTION
1800 WHEN OTHERS THEN
1801 IF PG_DEBUG in ('Y', 'C') THEN
1802 arp_util.debug('EXCEPTION: arp_cash_receipts_pkg.lock_compare_p()');
1803 END IF;
1804 RAISE;
1805
1806 END lock_compare_p;
1807
1808
1809 --
1810 /*---------------------------------------------+
1811 | Package initialization section. |
1812 | Sets WHO column variables for later use. |
1813 +---------------------------------------------*/
1814 --
1815 BEGIN
1816 --
1817 pg_request_id := arp_global.request_id;
1818 pg_program_application_id := arp_global.program_application_id;
1819 pg_program_id := arp_global.program_id;
1820 pg_program_update_date := arp_global.program_update_date;
1821 pg_last_updated_by := arp_global.last_updated_by;
1822 pg_last_update_date := arp_global.last_update_date;
1823 pg_last_update_login := arp_global.last_update_login;
1824 -- pg_set_of_books_id := arp_global.set_of_books_id;
1825 --
1826 --
1827 END ARP_CASH_RECEIPTS_PKG;