[Home] [Help]
PACKAGE BODY: APPS.ARP_CR_HISTORY_PKG
Source
1 PACKAGE BODY ARP_CR_HISTORY_PKG AS
2 /*$Header: ARRICRHB.pls 120.10.12010000.1 2008/07/24 16:52:13 appldev 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(10) := '~';
12 AR_NUMBER_DUMMY CONSTANT NUMBER(15) := -999999999999999;
13 AR_DATE_DUMMY CONSTANT DATE := to_date(1, 'J');
14 --
15 /*-------------------------------------+
16 | WHO column values from ARP_GLOBAL |
17 +-------------------------------------*/
18 --
19 pg_request_id number;
20 pg_program_application_id number;
21 pg_program_id number;
22 pg_program_update_date date;
23 pg_last_updated_by number;
24 pg_last_update_date date;
25 pg_last_update_login number;
26 pg_set_of_books_id number;
27 --
28 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
29
30 PROCEDURE set_to_dummy( p_crh_rec OUT NOCOPY ar_cash_receipt_history%ROWTYPE ) IS
31 BEGIN
32 IF PG_DEBUG in ('Y', 'C') THEN
33 arp_standard.debug( 'arp_cr_history_pkg.set_to_dummy()+' );
34 END IF;
35 --
36 p_crh_rec.amount := AR_NUMBER_DUMMY;
37 p_crh_rec.acctd_amount := AR_NUMBER_DUMMY;
38 p_crh_rec.factor_flag := AR_FLAG_DUMMY;
39 p_crh_rec.first_posted_record_flag := AR_FLAG_DUMMY;
40 p_crh_rec.gl_date := AR_DATE_DUMMY;
41 p_crh_rec.postable_flag := AR_FLAG_DUMMY;
42 p_crh_rec.status := AR_TEXT_DUMMY;
43 p_crh_rec.trx_date := AR_DATE_DUMMY;
44 p_crh_rec.acctd_factor_discount_amount := AR_NUMBER_DUMMY;
45 p_crh_rec.account_code_combination_id := AR_NUMBER_DUMMY;
46 p_crh_rec.bank_charge_account_ccid := AR_NUMBER_DUMMY;
47 p_crh_rec.batch_id := AR_NUMBER_DUMMY;
48 p_crh_rec.current_record_flag := AR_FLAG_DUMMY;
49 p_crh_rec.exchange_date := AR_DATE_DUMMY;
50 p_crh_rec.exchange_rate := AR_NUMBER_DUMMY;
51 p_crh_rec.exchange_rate_type := AR_TEXT_DUMMY;
52 p_crh_rec.factor_discount_amount := AR_NUMBER_DUMMY;
53 p_crh_rec.gl_posted_date := AR_DATE_DUMMY;
54 p_crh_rec.posting_control_id := AR_NUMBER_DUMMY;
55 p_crh_rec.reversal_cash_receipt_hist_id := AR_NUMBER_DUMMY;
56 p_crh_rec.reversal_gl_date := AR_DATE_DUMMY;
57 p_crh_rec.reversal_gl_posted_date := AR_DATE_DUMMY;
58 p_crh_rec.reversal_posting_control_id := AR_NUMBER_DUMMY;
59 p_crh_rec.prv_stat_cash_receipt_hist_id := AR_NUMBER_DUMMY;
60 p_crh_rec.reversal_created_from := AR_TEXT_DUMMY;
61 p_crh_rec.attribute_category := AR_TEXT_DUMMY;
62 p_crh_rec.attribute1 := AR_TEXT_DUMMY;
63 p_crh_rec.attribute2 := AR_TEXT_DUMMY;
64 p_crh_rec.attribute3 := AR_TEXT_DUMMY;
65 p_crh_rec.attribute4 := AR_TEXT_DUMMY;
66 p_crh_rec.attribute5 := AR_TEXT_DUMMY;
67 p_crh_rec.attribute6 := AR_TEXT_DUMMY;
68 p_crh_rec.attribute7 := AR_TEXT_DUMMY;
69 p_crh_rec.attribute8 := AR_TEXT_DUMMY;
70 p_crh_rec.attribute9 := AR_TEXT_DUMMY;
71 p_crh_rec.attribute10 := AR_TEXT_DUMMY;
72 p_crh_rec.attribute11 := AR_TEXT_DUMMY;
73 p_crh_rec.attribute12 := AR_TEXT_DUMMY;
74 p_crh_rec.attribute13 := AR_TEXT_DUMMY;
75 p_crh_rec.attribute14 := AR_TEXT_DUMMY;
76 p_crh_rec.attribute15 := AR_TEXT_DUMMY;
77 --
78 IF PG_DEBUG in ('Y', 'C') THEN
79 arp_standard.debug( 'arp_cr_history_pkg.set_to_dummy()-' );
80 END IF;
81 --
82 EXCEPTION
83 WHEN OTHERS THEN
84 IF PG_DEBUG in ('Y', 'C') THEN
85 arp_standard.debug( 'EXCEPTION: arp_cr_history_pkg.set_to_dummy' );
86 END IF;
87 RAISE;
88 END set_to_dummy;
89 --
90 -- New update_p procedure
91 --
92 PROCEDURE update_p( p_crh_rec IN ar_cash_receipt_history%ROWTYPE,
93 p_crh_id IN ar_cash_receipt_history.cash_receipt_history_id%TYPE ) IS
94 BEGIN
95 IF PG_DEBUG in ('Y', 'C') THEN
96 arp_standard.debug( 'arp_cr_history_pkg.update_p()+' );
97 END IF;
98 --
99 UPDATE ar_cash_receipt_history SET
100 amount = DECODE( p_crh_rec.amount,
101 AR_NUMBER_DUMMY, amount,
102 p_crh_rec.amount ),
103 acctd_amount = DECODE( p_crh_rec.acctd_amount,
104 AR_NUMBER_DUMMY, acctd_amount,
105 p_crh_rec.acctd_amount ),
106 factor_flag = DECODE( p_crh_rec.factor_flag,
107 AR_FLAG_DUMMY, factor_flag,
108 p_crh_rec.factor_flag ),
109 first_posted_record_flag =
110 DECODE( p_crh_rec.first_posted_record_flag,
111 AR_FLAG_DUMMY, first_posted_record_flag,
112 p_crh_rec.first_posted_record_flag ),
113 gl_date = DECODE( p_crh_rec.gl_date,
114 AR_DATE_DUMMY, gl_date,
115 p_crh_rec.gl_date ),
116 postable_flag = DECODE( p_crh_rec.postable_flag,
117 AR_FLAG_DUMMY, postable_flag,
118 p_crh_rec.postable_flag ),
119 status = DECODE( p_crh_rec.status,
120 AR_TEXT_DUMMY, status,
121 p_crh_rec.status ),
122 trx_date = DECODE( p_crh_rec.trx_date,
123 AR_DATE_DUMMY, trx_date,
124 p_crh_rec.trx_date ),
125 acctd_factor_discount_amount =
126 DECODE( p_crh_rec.acctd_factor_discount_amount,
127 AR_NUMBER_DUMMY, acctd_factor_discount_amount,
128 p_crh_rec.acctd_factor_discount_amount ),
129 account_code_combination_id =
130 DECODE( p_crh_rec.account_code_combination_id,
131 AR_NUMBER_DUMMY, account_code_combination_id,
132 p_crh_rec.account_code_combination_id ),
133 bank_charge_account_ccid =
134 DECODE( p_crh_rec.bank_charge_account_ccid,
135 AR_NUMBER_DUMMY, bank_charge_account_ccid,
136 p_crh_rec.amount ),
137 batch_id = DECODE( p_crh_rec.batch_id,
138 AR_NUMBER_DUMMY, batch_id,
139 p_crh_rec.batch_id ),
140 current_record_flag = DECODE( p_crh_rec.current_record_flag,
141 AR_FLAG_DUMMY, current_record_flag,
142 p_crh_rec.current_record_flag ),
143 exchange_date = DECODE( p_crh_rec.exchange_date,
144 AR_DATE_DUMMY, exchange_date,
145 p_crh_rec.exchange_date ),
146 exchange_rate = DECODE( p_crh_rec.exchange_rate,
147 AR_NUMBER_DUMMY, exchange_rate,
148 p_crh_rec.exchange_rate ),
149 exchange_rate_type = DECODE( p_crh_rec.exchange_rate_type,
150 AR_TEXT_DUMMY, exchange_rate_type,
151 p_crh_rec.exchange_rate_type ),
152 factor_discount_amount =
153 DECODE( p_crh_rec.factor_discount_amount,
154 AR_NUMBER_DUMMY, factor_discount_amount,
155 p_crh_rec.factor_discount_amount ),
156 gl_posted_date = DECODE( p_crh_rec.gl_posted_date,
157 AR_DATE_DUMMY, gl_posted_date,
158 p_crh_rec.gl_posted_date ),
159 posting_control_id = DECODE( p_crh_rec.posting_control_id,
160 AR_NUMBER_DUMMY, posting_control_id,
161 p_crh_rec.posting_control_id ),
162 reversal_cash_receipt_hist_id =
163 DECODE( p_crh_rec.reversal_cash_receipt_hist_id,
164 AR_NUMBER_DUMMY, reversal_cash_receipt_hist_id,
165 p_crh_rec.reversal_cash_receipt_hist_id ),
166 reversal_gl_date = DECODE( p_crh_rec.reversal_gl_date,
167 AR_DATE_DUMMY, reversal_gl_date,
168 p_crh_rec.reversal_gl_date ),
169 reversal_gl_posted_date =
170 DECODE( p_crh_rec.reversal_gl_posted_date,
171 AR_DATE_DUMMY, reversal_gl_posted_date,
172 p_crh_rec.reversal_gl_posted_date ),
173 reversal_posting_control_id =
174 DECODE( p_crh_rec.reversal_posting_control_id,
175 AR_NUMBER_DUMMY, reversal_posting_control_id,
176 p_crh_rec.reversal_posting_control_id ),
177 request_id = pg_request_id,
178 program_application_id =
179 pg_program_application_id,
180 program_id = pg_program_id,
181 program_update_date = pg_program_update_date,
182 last_updated_by = pg_last_updated_by,
183 last_update_date = pg_last_update_date,
184 last_update_login = pg_last_update_login,
185 prv_stat_cash_receipt_hist_id =
186 DECODE( p_crh_rec.prv_stat_cash_receipt_hist_id,
187 AR_NUMBER_DUMMY, prv_stat_cash_receipt_hist_id,
188 p_crh_rec.prv_stat_cash_receipt_hist_id ),
189 reversal_created_from =
190 DECODE( p_crh_rec.reversal_created_from,
191 AR_TEXT_DUMMY, reversal_created_from,
192 p_crh_rec.reversal_created_from ),
193 attribute_category = DECODE( p_crh_rec.attribute_category,
194 AR_TEXT_DUMMY, attribute_category,
195 p_crh_rec.attribute_category ),
196 attribute1 = DECODE( p_crh_rec.attribute1,
197 AR_TEXT_DUMMY, attribute1,
198 p_crh_rec.attribute1 ),
199 attribute2 = DECODE( p_crh_rec.attribute2,
200 AR_TEXT_DUMMY, attribute2,
201 p_crh_rec.attribute2 ),
202 attribute3 = DECODE( p_crh_rec.attribute3,
203 AR_TEXT_DUMMY, attribute3,
204 p_crh_rec.attribute3 ),
205 attribute4 = DECODE( p_crh_rec.attribute4,
206 AR_TEXT_DUMMY, attribute3,
207 p_crh_rec.attribute4 ),
208 attribute5 = DECODE( p_crh_rec.attribute5,
209 AR_TEXT_DUMMY, attribute5,
210 p_crh_rec.attribute5 ),
211 attribute6 = DECODE( p_crh_rec.attribute6,
212 AR_TEXT_DUMMY, attribute2,
213 p_crh_rec.attribute6 ),
214 attribute7 = DECODE( p_crh_rec.attribute7,
215 AR_TEXT_DUMMY, attribute2,
216 p_crh_rec.attribute7 ),
217 attribute8 = DECODE( p_crh_rec.attribute8,
218 AR_TEXT_DUMMY, attribute8,
219 p_crh_rec.attribute8 ),
220 attribute9 = DECODE( p_crh_rec.attribute9,
221 AR_TEXT_DUMMY, attribute9,
222 p_crh_rec.attribute9 ),
223 attribute10 = DECODE( p_crh_rec.attribute10,
224 AR_TEXT_DUMMY, attribute10,
225 p_crh_rec.attribute10 ),
226 attribute11 = DECODE( p_crh_rec.attribute11,
227 AR_TEXT_DUMMY, attribute11,
228 p_crh_rec.attribute11 ),
229 attribute12 = DECODE( p_crh_rec.attribute12,
230 AR_TEXT_DUMMY, attribute12,
231 p_crh_rec.attribute12 ),
232 attribute13 = DECODE( p_crh_rec.attribute13,
233 AR_TEXT_DUMMY, attribute13,
234 p_crh_rec.attribute13 ),
235 attribute14 = DECODE( p_crh_rec.attribute14,
236 AR_TEXT_DUMMY, attribute14,
237 p_crh_rec.attribute14 ),
238 attribute15 = DECODE( p_crh_rec.attribute15,
239 AR_TEXT_DUMMY, attribute15,
240 p_crh_rec.attribute15 )
241 WHERE cash_receipt_history_id = p_crh_rec.cash_receipt_history_id;
242 --
243 /*----------------------------------------------------+
244 | Call central MRC library for the generic update |
245 | made above. This is done here rather then in |
246 | the generic update as the where clause changes |
247 | and that information is needed for the MRC engine |
248 +----------------------------------------------------*/
249
250 ar_mrc_engine.maintain_mrc_data(
251 p_event_mode => 'UPDATE',
252 p_table_name => 'AR_CASH_RECEIPT_HISTORY',
253 p_mode => 'SINGLE',
254 p_key_value => p_crh_rec.cash_receipt_history_id
255 );
256
257 IF PG_DEBUG in ('Y', 'C') THEN
258 arp_standard.debug( 'arp_cr_history_pkg.update_p()-' );
259 END IF;
260 --
261 EXCEPTION
262 WHEN OTHERS THEN
263 IF PG_DEBUG in ('Y', 'C') THEN
264 arp_standard.debug( 'EXCEPTION: arp_cr_history_pkg.update_p' );
265 END IF;
266 RAISE;
267 END update_p;
268 --
269 PROCEDURE insert_p(
270 p_crh_rec IN ar_cash_receipt_history%ROWTYPE,
271 p_crh_id OUT NOCOPY ar_cash_receipt_history.cash_receipt_history_id%TYPE ) IS
272 --
273 l_crh_id ar_cash_receipt_history.cash_receipt_history_id%TYPE;
274 BEGIN
275 IF PG_DEBUG in ('Y', 'C') THEN
276 arp_standard.debug('update_p: ' || 'arp_cr_history_pkg.insert_p()+' );
277 END IF;
278 --
279 SELECT ar_cash_receipt_history_s.nextval
280 INTO l_crh_id
281 FROM dual;
282 --
283 INSERT INTO ar_cash_receipt_history (
284 cash_receipt_history_id,
285 amount,
286 acctd_amount,
287 cash_receipt_id,
288 factor_flag,
289 first_posted_record_flag,
290 gl_date,
291 postable_flag,
292 status,
293 trx_date,
294 acctd_factor_discount_amount,
295 account_code_combination_id,
296 bank_charge_account_ccid,
297 batch_id,
298 current_record_flag,
299 exchange_date,
300 exchange_rate,
301 exchange_rate_type,
302 factor_discount_amount,
303 gl_posted_date,
304 posting_control_id,
305 reversal_cash_receipt_hist_id,
306 reversal_gl_date,
307 reversal_gl_posted_date,
308 reversal_posting_control_id,
309 request_id,
310 program_application_id,
311 program_id,
312 program_update_date,
313 created_by,
314 creation_date,
315 last_updated_by,
316 last_update_date,
317 last_update_login,
318 prv_stat_cash_receipt_hist_id,
319 created_from,
320 reversal_created_from,
321 attribute_category,
322 attribute1,
323 attribute2,
324 attribute3,
325 attribute4,
326 attribute5,
327 attribute6,
328 attribute7,
329 attribute8,
330 attribute9,
331 attribute10,
332 attribute11,
333 attribute12,
334 attribute13,
335 attribute14,
336 attribute15
337 ,org_id
338 )
339 VALUES ( l_crh_id,
340 p_crh_rec.amount,
341 p_crh_rec.acctd_amount,
342 p_crh_rec.cash_receipt_id,
343 p_crh_rec.factor_flag,
344 p_crh_rec.first_posted_record_flag,
345 p_crh_rec.gl_date,
346 p_crh_rec.postable_flag,
347 p_crh_rec.status,
348 p_crh_rec.trx_date,
349 p_crh_rec.acctd_factor_discount_amount,
350 p_crh_rec.account_code_combination_id,
351 p_crh_rec.bank_charge_account_ccid,
352 p_crh_rec.batch_id,
353 p_crh_rec.current_record_flag,
354 p_crh_rec.exchange_date,
355 p_crh_rec.exchange_rate,
356 p_crh_rec.exchange_rate_type,
357 p_crh_rec.factor_discount_amount,
358 p_crh_rec.gl_posted_date,
359 p_crh_rec.posting_control_id,
360 p_crh_rec.reversal_cash_receipt_hist_id,
361 p_crh_rec.reversal_gl_date,
362 p_crh_rec.reversal_gl_posted_date,
363 p_crh_rec.reversal_posting_control_id,
364 NVL( arp_standard.profile.request_id, p_crh_rec.request_id ),
365 NVL( arp_standard.profile.program_application_id,
366 p_crh_rec.program_application_id ),
367 NVL( arp_standard.profile.program_id,
368 p_crh_rec.program_id ),
369 DECODE( arp_standard.profile.program_id,
370 NULL, NULL,
371 SYSDATE
372 ),
373 arp_global.last_updated_by, /* FP Bug 5715840 arp_standard.profile.user_id,*/
374 SYSDATE,
375 arp_global.last_updated_by, /* FP Bug 5715840 arp_standard.profile.user_id,*/
376 SYSDATE,
377 NVL( arp_global.last_update_login,
378 p_crh_rec.last_update_login ),
379 p_crh_rec.prv_stat_cash_receipt_hist_id,
380 p_crh_rec.created_from,
381 p_crh_rec.reversal_created_from,
382 p_crh_rec.attribute_category,
383 p_crh_rec.attribute1,
384 p_crh_rec.attribute2,
385 p_crh_rec.attribute3,
386 p_crh_rec.attribute4,
387 p_crh_rec.attribute5,
388 p_crh_rec.attribute6,
389 p_crh_rec.attribute7,
390 p_crh_rec.attribute8,
391 p_crh_rec.attribute9,
392 p_crh_rec.attribute10,
393 p_crh_rec.attribute11,
394 p_crh_rec.attribute12,
395 p_crh_rec.attribute13,
396 p_crh_rec.attribute14,
397 p_crh_rec.attribute15
398 ,arp_standard.sysparm.org_id /* SSA changes anuj */
399 );
400 --
401 /*-------------------------------------------+
402 | Call central MRC library for insertion |
403 | into MRC tables |
404 +-------------------------------------------*/
405
406 ar_mrc_engine.maintain_mrc_data(
407 p_event_mode => 'INSERT',
408 p_table_name => 'AR_CASH_RECEIPT_HISTORY',
409 p_mode => 'SINGLE',
410 p_key_value => l_crh_id);
411
412 p_crh_id := l_crh_id;
413 --
414 IF PG_DEBUG in ('Y', 'C') THEN
415 arp_standard.debug('update_p: ' || 'arp_cr_history_pkg.insert_p()-' );
416 END IF;
417 --
418 EXCEPTION
419 WHEN OTHERS THEN
420 IF PG_DEBUG in ('Y', 'C') THEN
421 arp_standard.debug('update_p: ' || 'EXCEPTION: arp_cr_history_pkg.insert_p' );
422 END IF;
423 RAISE;
424 END insert_p;
425 --
426 -- Old update_p procedure retianed for compatibiltiy sake
427 --
428 PROCEDURE update_p( p_crh_rec IN ar_cash_receipt_history%ROWTYPE ) IS
429 BEGIN
430 IF PG_DEBUG in ('Y', 'C') THEN
431 arp_standard.debug( 'arp_cr_history_pkg.update_p()+' );
432 END IF;
433 --
434 UPDATE ar_cash_receipt_history SET
435 amount = p_crh_rec.amount,
436 acctd_amount = p_crh_rec.acctd_amount,
437 factor_flag = p_crh_rec.factor_flag,
438 first_posted_record_flag =
439 p_crh_rec.first_posted_record_flag,
440 gl_date = p_crh_rec.gl_date,
441 postable_flag = p_crh_rec.postable_flag,
442 status = p_crh_rec.status,
443 trx_date = p_crh_rec.trx_date,
444 acctd_factor_discount_amount =
445 p_crh_rec.acctd_factor_discount_amount,
446 account_code_combination_id =
447 p_crh_rec.account_code_combination_id,
448 bank_charge_account_ccid = p_crh_rec.bank_charge_account_ccid,
449 batch_id = p_crh_rec.batch_id,
450 current_record_flag = p_crh_rec.current_record_flag,
451 exchange_date = p_crh_rec.exchange_date,
452 exchange_rate = p_crh_rec.exchange_rate,
453 exchange_rate_type = p_crh_rec.exchange_rate_type,
454 factor_discount_amount = p_crh_rec.factor_discount_amount,
455 gl_posted_date = p_crh_rec.gl_posted_date,
456 posting_control_id = p_crh_rec.posting_control_id,
457 reversal_cash_receipt_hist_id =
458 p_crh_rec.reversal_cash_receipt_hist_id,
459 reversal_gl_date = p_crh_rec.reversal_gl_date,
460 reversal_gl_posted_date = p_crh_rec.reversal_gl_posted_date,
461 reversal_posting_control_id =
462 p_crh_rec.reversal_posting_control_id,
463 request_id = NVL( arp_standard.profile.request_id,
464 p_crh_rec.request_id ),
465 program_application_id =
466 NVL( arp_standard.profile.program_application_id,
467 p_crh_rec.program_application_id ),
468 program_id = NVL( arp_standard.profile.program_id,
469 p_crh_rec.program_id ),
470 program_update_date =
471 DECODE( arp_standard.profile.program_id,
472 NULL, NULL,
473 SYSDATE
474 ),
475 last_updated_by = arp_global.last_updated_by, /* FP Bug 5715840 arp_standard.profile.user_id,*/
476 last_update_date = SYSDATE,
477 last_update_login =
478 NVL( arp_global.last_update_login,
479 p_crh_rec.last_update_login ),
480 prv_stat_cash_receipt_hist_id =
481 p_crh_rec.prv_stat_cash_receipt_hist_id,
482 created_from = p_crh_rec.created_from,
483 reversal_created_from = p_crh_rec.reversal_created_from,
484 attribute_category = p_crh_rec.attribute_category,
485 attribute1 = p_crh_rec.attribute1,
486 attribute2 = p_crh_rec.attribute2,
487 attribute3 = p_crh_rec.attribute3,
488 attribute4 = p_crh_rec.attribute4,
489 attribute5 = p_crh_rec.attribute5,
490 attribute6 = p_crh_rec.attribute6,
491 attribute7 = p_crh_rec.attribute7,
492 attribute8 = p_crh_rec.attribute8,
493 attribute9 = p_crh_rec.attribute9,
494 attribute10 = p_crh_rec.attribute10,
495 attribute11 = p_crh_rec.attribute11,
496 attribute12 = p_crh_rec.attribute12,
497 attribute13 = p_crh_rec.attribute13,
498 attribute14 = p_crh_rec.attribute14,
499 attribute15 = p_crh_rec.attribute15
500 WHERE cash_receipt_history_id = p_crh_rec.cash_receipt_history_id;
501 --
502
503 /*----------------------------------------------------+
504 | Call central MRC library for the generic update |
505 | made above. This is done here rather then in |
506 | the generic update as the where clause changes |
507 | and that information is needed for the MRC engine |
508 +----------------------------------------------------*/
509
510 ar_mrc_engine.maintain_mrc_data(
511 p_event_mode => 'UPDATE',
512 p_table_name => 'AR_CASH_RECEIPT_HISTORY',
513 p_mode => 'SINGLE',
514 p_key_value => p_crh_rec.cash_receipt_history_id
515 );
516
517 IF PG_DEBUG in ('Y', 'C') THEN
518 arp_standard.debug( 'arp_cr_history_pkg.update_p()-' );
519 END IF;
520 --
521 EXCEPTION
522 WHEN OTHERS THEN
523 IF PG_DEBUG in ('Y', 'C') THEN
524 arp_standard.debug( 'EXCEPTION: arp_cr_history_pkg.update_p' );
525 END IF;
526 RAISE;
527 END update_p;
528 --
529 PROCEDURE delete_p(
530 p_crh_id IN ar_cash_receipt_history.cash_receipt_history_id%TYPE ) IS
531 BEGIN
532 IF PG_DEBUG in ('Y', 'C') THEN
533 arp_standard.debug( 'arp_cr_history_pkg.delete_p()+' );
534 END IF;
535 --
536 DELETE FROM ar_cash_receipt_history
537 WHERE cash_receipt_history_id = p_crh_id;
538 --
539 /*---------------------------------+
540 | Calling central MRC library |
541 | for MRC Integration |
542 +---------------------------------*/
543
544 ar_mrc_engine.maintain_mrc_data(
545 p_event_mode => 'DELETE',
546 p_table_name => 'AR_CASH_RECEIPT_HISTORY',
547 p_mode => 'SINGLE',
548 p_key_value => p_crh_id);
549
550
551 IF PG_DEBUG in ('Y', 'C') THEN
552 arp_standard.debug( 'arp_cr_history_pkg.delete_p()-' );
553 END IF;
554 EXCEPTION
555 WHEN OTHERS THEN
556 IF PG_DEBUG in ('Y', 'C') THEN
557 arp_standard.debug( 'EXCEPTION: arp_cr_history_pkg.delete_p' );
558 END IF;
559 RAISE;
560 END delete_p;
561 --
562 PROCEDURE delete_p_cr(
563 p_cr_id IN ar_cash_receipts.cash_receipt_id%TYPE ) IS
564
565 l_rec_hist_key_value_list gl_ca_utility_pkg.r_key_value_arr;
566 BEGIN
567 IF PG_DEBUG in ('Y', 'C') THEN
568 arp_standard.debug( 'arp_cr_history_pkg.delete_p_cr()+' );
569 END IF;
570 --
571 DELETE FROM ar_cash_receipt_history
572 WHERE cash_receipt_id = p_cr_id
573 RETURNING cash_receipt_history_id
574 BULK COLLECT INTO l_rec_hist_key_value_list;
575
576 /*---------------------------------+
577 | Calling central MRC library |
578 | for MRC Integration |
579 +---------------------------------*/
580
581 ar_mrc_engine.maintain_mrc_data(
582 p_event_mode => 'DELETE',
583 p_table_name => 'AR_CASH_RECEIPT_HISTORY',
584 p_mode => 'BATCH',
585 p_key_value_list => l_rec_hist_key_value_list
586 );
587 --
588 IF PG_DEBUG in ('Y', 'C') THEN
589 arp_standard.debug( 'arp_cr_history_pkg.delete_p_cr()-' );
590 END IF;
591 EXCEPTION
592 WHEN OTHERS THEN
593 IF PG_DEBUG in ('Y', 'C') THEN
594 arp_standard.debug( 'EXCEPTION: arp_cr_history_pkg.delete_p_cr' );
595 END IF;
596 RAISE;
597 END delete_p_cr;
598 --
599 PROCEDURE lock_p(
600 p_crh_id IN ar_cash_receipt_history.cash_receipt_history_id%TYPE ) IS
601 l_crh_id ar_cash_receipt_history.cash_receipt_history_id%TYPE;
602 BEGIN
603 IF PG_DEBUG in ('Y', 'C') THEN
604 arp_standard.debug( 'arp_cr_history_pkg.lock_p()+' );
605 END IF;
606 --
607 SELECT cash_receipt_history_id
608 INTO l_crh_id
609 FROM ar_cash_receipt_history
610 WHERE cash_receipt_history_id = p_crh_id
611 FOR UPDATE OF STATUS;
612 --
613 IF PG_DEBUG in ('Y', 'C') THEN
614 arp_standard.debug( 'arp_cr_history_pkg.lock_p()-' );
615 END IF;
616 EXCEPTION
617 WHEN OTHERS THEN
618 IF PG_DEBUG in ('Y', 'C') THEN
619 arp_standard.debug( 'EXCPETION: arp_cr_history_pkg.lock_p' );
620 END IF;
621 RAISE;
622 END lock_p;
623 --
624 PROCEDURE nowaitlock_p(
625 p_crh_id IN ar_cash_receipt_history.cash_receipt_history_id%TYPE ) IS
626 l_crh_id ar_cash_receipt_history.cash_receipt_history_id%TYPE;
627 BEGIN
628 IF PG_DEBUG in ('Y', 'C') THEN
629 arp_standard.debug( 'arp_cr_history_pkg.nowaitlock_p()+' );
630 END IF;
631 --
632 SELECT cash_receipt_history_id
633 INTO l_crh_id
634 FROM ar_cash_receipt_history
635 WHERE cash_receipt_history_id = p_crh_id
636 FOR UPDATE OF status NOWAIT;
637 --
638 IF PG_DEBUG in ('Y', 'C') THEN
639 arp_standard.debug( 'arp_cr_history_pkg.nowaitlock_p()-' );
640 END IF;
641 EXCEPTION
642 WHEN OTHERS THEN
643 IF PG_DEBUG in ('Y', 'C') THEN
644 arp_standard.debug( 'EXCPETION: arp_cr_history_pkg.nowaitlock_p' );
645 END IF;
646 RAISE;
647 END nowaitlock_p;
648 --
649 PROCEDURE fetch_p(
650 p_crh_id IN ar_cash_receipt_history.cash_receipt_history_id%TYPE,
651 p_crh_rec OUT NOCOPY ar_cash_receipt_history%ROWTYPE ) IS
652 BEGIN
653 --
654 IF PG_DEBUG in ('Y', 'C') THEN
655 arp_standard.debug( 'arp_cr_history_pkg.fetch_p()+' );
656 END IF;
657 --
658 SELECT *
659 INTO p_crh_rec
660 FROM ar_cash_receipt_history
661 WHERE cash_receipt_history_id = p_crh_id;
662 IF PG_DEBUG in ('Y', 'C') THEN
663 arp_standard.debug( 'arp_cr_history_pkg.fetch_p()-' );
664 END IF;
665 --
666 EXCEPTION
667 WHEN OTHERS THEN
668 IF PG_DEBUG in ('Y', 'C') THEN
669 arp_standard.debug( 'EXCEPTION: arp_cr_history_pkg.fetch_p' );
670 END IF;
671 RAISE;
672 END fetch_p;
673 --
674 PROCEDURE fetch_f_crid(
675 p_cr_id IN ar_cash_receipts.cash_receipt_id%TYPE,
676 p_crh_rec OUT NOCOPY ar_cash_receipt_history%ROWTYPE ) IS
677 BEGIN
678 IF PG_DEBUG in ('Y', 'C') THEN
679 arp_standard.debug( 'arp_cr_history_pkg.fetch_f_crid()+' );
680 END IF;
681 --
682 SELECT *
683 INTO p_crh_rec
684 FROM ar_cash_receipt_history
685 WHERE cash_receipt_id = p_cr_id AND
686 current_record_flag = 'Y'
687 FOR UPDATE OF status;
688
689 --
690 IF PG_DEBUG in ('Y', 'C') THEN
691 arp_standard.debug( 'arp_cr_history_pkg.fetch_f_crid()-' );
692 END IF;
693 --
694 EXCEPTION
695 WHEN OTHERS THEN
696 IF PG_DEBUG in ('Y', 'C') THEN
697 arp_standard.debug('fetch_f_crid: ' ||
698 'EXCEPTION: arp_cr_history_pkg.fetch_f_crid' );
699 END IF;
700 RAISE;
701 END fetch_f_crid;
702 --
703 --
704 PROCEDURE fetch_f_cr_id(
705 p_crh_rec IN OUT NOCOPY ar_cash_receipt_history%ROWTYPE ) IS
706 BEGIN
707 IF PG_DEBUG in ('Y', 'C') THEN
708 arp_standard.debug( 'arp_cr_history_pkg.fetch_f_cr_id()+' );
709 END IF;
710 --
711 SELECT *
712 INTO p_crh_rec
713 FROM ar_cash_receipt_history
714 WHERE cash_receipt_id = p_crh_rec.cash_receipt_id AND
715 current_record_flag = 'Y';
716 --
717 IF PG_DEBUG in ('Y', 'C') THEN
718 arp_standard.debug( 'arp_cr_history_pkg.fetch_f_cr_id()-' );
719 END IF;
720 --
721 EXCEPTION
722 WHEN OTHERS THEN
723 IF PG_DEBUG in ('Y', 'C') THEN
724 arp_standard.debug('fetch_f_cr_id: ' ||
725 'EXCEPTION: arp_cr_history_pkg.fetch_f_cr_id' );
726 END IF;
727 RAISE;
728 END fetch_f_cr_id;
729 --
730 --
731 PROCEDURE lock_fetch_p(
732 p_crh_rec IN OUT NOCOPY ar_cash_receipt_history%ROWTYPE) IS
733 BEGIN
734 IF PG_DEBUG in ('Y', 'C') THEN
735 arp_standard.debug( 'arp_cr_history_pkg.lock_fetch_p()+' );
736 END IF;
737 --
738 SELECT *
739 INTO p_crh_rec
740 FROM ar_cash_receipt_history
741 WHERE cash_receipt_history_id = p_crh_rec.cash_receipt_history_id
742 FOR UPDATE OF status;
743 --
744 IF PG_DEBUG in ('Y', 'C') THEN
745 arp_standard.debug( 'arp_cr_history_pkg.lock_fetch_p()-' );
746 END IF;
747 --
748 EXCEPTION
749 WHEN OTHERS THEN
750 IF PG_DEBUG in ('Y', 'C') THEN
751 arp_standard.debug('lock_fetch_p: ' ||
752 'EXCEPTION: arp_cr_history_pkg.lock_fetch_p' );
753 END IF;
754 RAISE;
755 END lock_fetch_p;
756 --
757 --
758 PROCEDURE nowaitlock_fetch_p(
759 p_crh_rec IN OUT NOCOPY ar_cash_receipt_history%ROWTYPE) IS
760 BEGIN
761 IF PG_DEBUG in ('Y', 'C') THEN
762 arp_standard.debug( 'arp_cr_history_pkg.nowaitlock_fetch_p()+' );
763 END IF;
764 --
765 SELECT *
766 INTO p_crh_rec
767 FROM ar_cash_receipt_history
768 WHERE cash_receipt_history_id = p_crh_rec.cash_receipt_history_id
769 FOR UPDATE OF status NOWAIT;
770 --
771 IF PG_DEBUG in ('Y', 'C') THEN
772 arp_standard.debug( 'arp_cr_history_pkg.nowaitlock_fetch_p()-' );
773 END IF;
774 --
775 EXCEPTION
776 WHEN OTHERS THEN
777 IF PG_DEBUG in ('Y', 'C') THEN
778 arp_standard.debug('nowaitlock_fetch_p: ' ||
779 'EXCEPTION: arp_cr_history_pkg.nowaitlock_fetch_p' );
780 END IF;
781 RAISE;
782 END nowaitlock_fetch_p;
783 --
784 --
785 PROCEDURE lock_f_batch_id(
786 p_batch_id IN ar_batches.batch_id%TYPE ) IS
787 --
788 CURSOR lock_C IS
789 SELECT 'lock'
790 FROM ar_cash_receipt_history
791 WHERE batch_id = p_batch_id
792 FOR UPDATE OF status;
793 --
794 BEGIN
795 IF PG_DEBUG in ('Y', 'C') THEN
796 arp_standard.debug( 'arp_cr_history_pkg.lock_f_batch_id()+' );
797 END IF;
798 --
799 OPEN lock_C;
800 CLOSE lock_C;
801 --
802 IF PG_DEBUG in ('Y', 'C') THEN
803 arp_standard.debug( 'arp_cr_history_pkg.lock_f_batch_id()-' );
804 END IF;
805 --
806 EXCEPTION
807 WHEN OTHERS THEN
808 IF lock_C%ISOPEN THEN
809 CLOSE lock_C;
810 END IF;
811 --
812 IF PG_DEBUG in ('Y', 'C') THEN
813 arp_standard.debug('lock_f_batch_id: ' ||
814 'EXCEPTION: arp_cr_history_pkg.lock_f_batch_id' );
815 END IF;
816 RAISE;
817 END lock_f_batch_id;
818 --
819 --
820 PROCEDURE nowaitlock_f_batch_id(
821 p_batch_id IN ar_batches.batch_id%TYPE ) IS
822 --
823 CURSOR lock_C IS
824 SELECT 'lock'
825 FROM ar_cash_receipt_history
826 WHERE batch_id = p_batch_id
827 FOR UPDATE OF status NOWAIT;
828 --
829 BEGIN
830 IF PG_DEBUG in ('Y', 'C') THEN
831 arp_standard.debug( 'arp_cr_history_pkg.nowaitlock_f_batch_id()+' );
832 END IF;
833 --
834 OPEN lock_C;
835 CLOSE lock_C;
836 --
837 IF PG_DEBUG in ('Y', 'C') THEN
838 arp_standard.debug( 'arp_cr_history_pkg.nowaitlock_f_batch_id()-' );
839 END IF;
840 --
841 EXCEPTION
842 WHEN OTHERS THEN
843 IF lock_C%ISOPEN THEN
844 CLOSE lock_C;
845 END IF;
846 --
847 IF PG_DEBUG in ('Y', 'C') THEN
848 arp_standard.debug('nowaitlock_f_batch_id: ' ||
849 'EXCEPTION: arp_cr_history_pkg.nowaitlock_f_batch_id' );
850 END IF;
851 RAISE;
852 END nowaitlock_f_batch_id;
853 --
854 --
855 PROCEDURE lock_f_cr_id(
856 p_cr_id IN ar_cash_receipts.cash_receipt_id%TYPE ) IS
857 --
858 CURSOR lock_C IS
859 SELECT 'lock'
860 FROM ar_cash_receipt_history
861 WHERE cash_receipt_id = p_cr_id
862 FOR UPDATE OF status;
863 --
864 BEGIN
865 IF PG_DEBUG in ('Y', 'C') THEN
866 arp_standard.debug( 'arp_cr_history_pkg.lock_f_cr_id()+' );
867 END IF;
868 --
869 OPEN lock_C;
870 CLOSE lock_C;
871 --
872 IF PG_DEBUG in ('Y', 'C') THEN
873 arp_standard.debug( 'arp_cr_history_pkg.lock_f_cr_id()-' );
874 END IF;
875 --
876 EXCEPTION
877 WHEN OTHERS THEN
878 IF lock_C%ISOPEN THEN
879 CLOSE lock_C;
880 END IF;
881 --
882 IF PG_DEBUG in ('Y', 'C') THEN
883 arp_standard.debug('lock_f_cr_id: ' ||
884 'EXCEPTION: arp_cr_history_pkg.lock_f_cr_id' );
885 END IF;
886 RAISE;
887 END lock_f_cr_id;
888 --
889 --
890 PROCEDURE nowaitlock_f_cr_id(
891 p_cr_id IN ar_cash_receipts.cash_receipt_id%TYPE ) IS
892 --
893 CURSOR lock_C IS
894 SELECT 'lock'
895 FROM ar_cash_receipt_history
896 WHERE cash_receipt_id = p_cr_id
897 FOR UPDATE OF status NOWAIT;
898 --
899 BEGIN
900 IF PG_DEBUG in ('Y', 'C') THEN
901 arp_standard.debug( 'arp_cr_history_pkg.nowaitlock_f_cr_id()+' );
902 END IF;
903 --
904 OPEN lock_C;
905 CLOSE lock_C;
906 --
907 IF PG_DEBUG in ('Y', 'C') THEN
908 arp_standard.debug( 'arp_cr_history_pkg.nowaitlock_f_cr_id()-' );
909 END IF;
910 --
911 EXCEPTION
912 WHEN OTHERS THEN
913 IF lock_C%ISOPEN THEN
914 CLOSE lock_C;
915 END IF;
916 --
917 IF PG_DEBUG in ('Y', 'C') THEN
918 arp_standard.debug('nowaitlock_f_cr_id: ' ||
919 'EXCEPTION: arp_cr_history_pkg.nowaitlock_f_cr_id' );
920 END IF;
921 RAISE;
922 END nowaitlock_f_cr_id;
923 --
924 PROCEDURE lock_fetch_f_cr_id(
925 p_crh_rec IN OUT NOCOPY ar_cash_receipt_history%ROWTYPE) IS
926 BEGIN
927 IF PG_DEBUG in ('Y', 'C') THEN
928 arp_standard.debug( 'arp_cr_history_pkg.lock_fetch_f_cr_id()+' );
929 END IF;
930 --
931 SELECT *
932 INTO p_crh_rec
933 FROM ar_cash_receipt_history
934 WHERE cash_receipt_id = p_crh_rec.cash_receipt_id
935 AND current_record_flag = 'Y'
936 FOR UPDATE OF status;
937 --
938 IF PG_DEBUG in ('Y', 'C') THEN
939 arp_standard.debug( 'arp_cr_history_pkg.lock_fetch_f_cr_id()-' );
940 END IF;
941 --
942 EXCEPTION
943 WHEN OTHERS THEN
944 IF PG_DEBUG in ('Y', 'C') THEN
945 arp_standard.debug('lock_fetch_f_cr_id: ' ||
946 'EXCEPTION: arp_cr_history_pkg.lock_fetch_f_cr_id' );
947 END IF;
948 RAISE;
949 END lock_fetch_f_cr_id;
950 --
951 --
952 PROCEDURE nowaitlock_fetch_f_cr_id(
953 p_crh_rec IN OUT NOCOPY ar_cash_receipt_history%ROWTYPE) IS
954 BEGIN
955 IF PG_DEBUG in ('Y', 'C') THEN
956 arp_standard.debug( 'arp_cr_history_pkg.nowaitlock_fetch_f_cr_id()+' );
957 END IF;
958 --
959 SELECT *
960 INTO p_crh_rec
961 FROM ar_cash_receipt_history
962 WHERE cash_receipt_id = p_crh_rec.cash_receipt_id
963 AND current_record_flag = 'Y'
964 FOR UPDATE OF status NOWAIT;
965 --
966 IF PG_DEBUG in ('Y', 'C') THEN
967 arp_standard.debug( 'arp_cr_history_pkg.nowaitlock_fetch_f_cr_id()-' );
968 END IF;
969 --
970 EXCEPTION
971 WHEN OTHERS THEN
972 IF PG_DEBUG in ('Y', 'C') THEN
973 arp_standard.debug('nowaitlock_fetch_f_cr_id: ' ||
974 'EXCEPTION: arp_cr_history_pkg.nowaitlock_fetch_f_cr_id' );
975 END IF;
976 RAISE;
977 END nowaitlock_fetch_f_cr_id;
978 --
979 /* Bug fix 2742388 */
980 PROCEDURE lock_hist_compare_p(
981 p_crh_rec IN ar_cash_receipt_history%ROWTYPE) IS
982 l_new_crh_rec ar_cash_receipt_history%ROWTYPE;
983 BEGIN
984 IF PG_DEBUG in ('Y', 'C') THEN
985 arp_util.debug('arp_cr_history_pkg.lock_hist_compare_p()+');
986 arp_util.debug(' Cash receipt_id = '||to_char(p_crh_rec.cash_receipt_id));
987 arp_util.debug('History_id = '||to_char(p_crh_rec.cash_receipt_history_id));
988 arp_util.debug('Amount = '||to_char(p_crh_rec.amount));
989 arp_util.debug('Status ='||p_crh_rec.status);
990 arp_util.debug('PC IS = '||to_char(p_crh_rec.posting_control_id));
991 END IF;
992 /*4354354 included nvl(factor_discount_amount,0) in the existing amount condition*/
993 SELECT *
994 INTO
995 l_new_crh_rec
996 FROM
997 ar_cash_receipt_history crh
998 WHERE
999 crh.cash_receipt_history_id = p_crh_rec.cash_receipt_history_id
1000 AND crh.cash_receipt_id = p_crh_rec.cash_receipt_id
1001 AND
1002 NVL((crh.amount+NVL(crh.factor_discount_amount,0)), AR_NUMBER_DUMMY) =
1003 NVL(
1004 DECODE(p_crh_rec.amount,
1005 AR_NUMBER_DUMMY, (crh.amount+NVL(crh.factor_discount_amount,0)),
1006 p_crh_rec.amount),
1007 AR_NUMBER_DUMMY
1008 )
1009 AND
1010 NVL(crh.status , AR_TEXT_DUMMY) =
1011 NVL(
1012 DECODE(p_crh_rec.status ,
1013 AR_TEXT_DUMMY, crh.status,
1014 p_crh_rec.status),
1015 AR_TEXT_DUMMY
1016 )
1017 AND NVL(crh.posting_control_id,AR_NUMBER_DUMMY) =
1018 NVL(
1019 DECODE(p_crh_rec.posting_control_id,
1020 AR_NUMBER_DUMMY,crh.posting_control_id,
1021 p_crh_rec.posting_control_id),
1022 AR_NUMBER_DUMMY
1023 )
1024 AND NVL(crh.current_record_flag,AR_FLAG_DUMMY) = 'Y'
1025 FOR UPDATE NOWAIT;
1026 arp_util.debug('arp_cr_history_pkg.lock_hist_compare_p()-');
1027 EXCEPTION
1028 WHEN OTHERS THEN
1029 IF PG_DEBUG in ('Y', 'C') THEN
1030 arp_util.debug('EXCEPTION: arp_cr_history_pkg.lock_hist_compare_p()');
1031 END IF;
1032 RAISE;
1033 END lock_hist_compare_p;
1034 /* End bug fix 2742388 */
1035
1036
1037 --
1038 BEGIN
1039 --
1040 pg_request_id := arp_global.request_id;
1041 pg_program_application_id := arp_global.program_application_id;
1042 pg_program_id := arp_global.program_id;
1043 pg_program_update_date := arp_global.program_update_date;
1044 pg_last_updated_by := arp_global.last_updated_by;
1045 pg_last_update_date := arp_global.last_update_date;
1046 pg_last_update_login := arp_global.last_update_login;
1047 pg_set_of_books_id := arp_global.set_of_books_id;
1048 --
1049 END ARP_CR_HISTORY_PKG;