[Home] [Help]
PACKAGE BODY: APPS.ARP_PROCESS_MISC_RECEIPTS2
Source
1 PACKAGE BODY ARP_PROCESS_MISC_RECEIPTS2 AS
2 /* $Header: ARREMT2B.pls 120.12.12010000.2 2008/11/11 10:22:58 rasarasw ship $ */
3 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
4
5 /* Declare subtype for vat tax accounting usage */
6 SUBTYPE l_ae_doc_rec_type IS arp_acct_main.ae_doc_rec_type ;
7
8 /* ---------------------- Public functions -------------------------------- */
9
10
11 FUNCTION revision RETURN VARCHAR2 IS
12 BEGIN
13
14 RETURN '$Revision: 120.12.12010000.2 $';
15
16 END revision;
17
18
19 /*===========================================================================+
20 | PROCEDURE |
21 | lock_misc_receipt |
22 | |
23 | DESCRIPTION |
24 | Locks a misc receipt for update. Checks if values displayed in form |
25 | are still the ones stored in the database. |
26 | |
27 | SCOPE - PUBLIC |
28 | |
29 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
30 | |
31 | NOTES |
32 | |
33 | MODIFICATION HISTORY |
34 | |
35 | 12-OCT-95 OSTEINME created |
36 | 05-FEB-2003 RVSHARMA Added parameter receipt_status.Bug 2688648. | | |
37 +===========================================================================*/
38
39
40 PROCEDURE lock_misc_receipt(
41 p_cash_receipt_id IN NUMBER,
42 p_currency_code IN VARCHAR2,
43 p_amount IN NUMBER,
44 p_receivables_trx_id IN NUMBER,
45 p_misc_payment_source IN VARCHAR2,
46 p_receipt_number IN VARCHAR2,
47 p_receipt_date IN DATE,
48 p_gl_date IN DATE,
49 p_comments IN VARCHAR2,
50 p_exchange_rate_type IN VARCHAR2,
51 p_exchange_rate IN NUMBER,
52 p_exchange_date IN DATE,
53 p_attribute_category IN VARCHAR2,
54 p_attribute1 IN VARCHAR2,
55 p_attribute2 IN VARCHAR2,
56 p_attribute3 IN VARCHAR2,
57 p_attribute4 IN VARCHAR2,
58 p_attribute5 IN VARCHAR2,
59 p_attribute6 IN VARCHAR2,
60 p_attribute7 IN VARCHAR2,
61 p_attribute8 IN VARCHAR2,
62 p_attribute9 IN VARCHAR2,
63 p_attribute10 IN VARCHAR2,
64 p_attribute11 IN VARCHAR2,
65 p_attribute12 IN VARCHAR2,
66 p_attribute13 IN VARCHAR2,
67 p_attribute14 IN VARCHAR2,
68 p_attribute15 IN VARCHAR2,
69 p_remittance_bank_account_id IN NUMBER,
70 p_deposit_date IN DATE,
71 p_receipt_method_id IN NUMBER,
72 p_doc_sequence_value IN NUMBER,
73 p_doc_sequence_id IN NUMBER,
74 p_distribution_set_id IN NUMBER,
75 p_reference_type IN VARCHAR2,
76 p_reference_id IN NUMBER,
77 p_vat_tax_id IN NUMBER,
78 p_ussgl_transaction_code IN VARCHAR2,
79 p_anticipated_clearing_date IN DATE,
80 --
81 -- ******* Global Flexfield parameters *******
82 --
83 p_global_attribute1 IN VARCHAR2,
84 p_global_attribute2 IN VARCHAR2,
85 p_global_attribute3 IN VARCHAR2,
86 p_global_attribute4 IN VARCHAR2,
87 p_global_attribute5 IN VARCHAR2,
88 p_global_attribute6 IN VARCHAR2,
89 p_global_attribute7 IN VARCHAR2,
90 p_global_attribute8 IN VARCHAR2,
91 p_global_attribute9 IN VARCHAR2,
92 p_global_attribute10 IN VARCHAR2,
93 p_global_attribute11 IN VARCHAR2,
94 p_global_attribute12 IN VARCHAR2,
95 p_global_attribute13 IN VARCHAR2,
96 p_global_attribute14 IN VARCHAR2,
97 p_global_attribute15 IN VARCHAR2,
98 p_global_attribute16 IN VARCHAR2,
99 p_global_attribute17 IN VARCHAR2,
100 p_global_attribute18 IN VARCHAR2,
101 p_global_attribute19 IN VARCHAR2,
102 p_global_attribute20 IN VARCHAR2,
103 p_global_attribute_category IN VARCHAR2,
104 ----
105 p_form_name IN varchar2,
106 p_form_version IN varchar2,
107 p_receipt_status IN VARCHAR2 ,
108 p_cash_receipt_history_id IN NUMBER,
109 p_state IN VARCHAR2,
110 p_posting_control_id IN NUMBER, /* Bug fix 2742388 */
111 p_rec_version_number IN NUMBER /* Bug fix 3032059 */
112 ) IS
113 l_cr_rec ar_cash_receipts%ROWTYPE;
114 l_crh_rec ar_cash_receipt_history%ROWTYPE;
115 l_ps_rec ar_payment_schedules%ROWTYPE;
116
117 BEGIN
118
119 IF PG_DEBUG in ('Y', 'C') THEN
120 arp_standard.debug('arp_process_misc_receipts2.lock_misc_receipt()+');
121 END IF;
122
123 arp_cash_receipts_pkg.set_to_dummy(l_cr_rec);
124
125 l_cr_rec.cash_receipt_id := p_cash_receipt_id;
126 l_cr_rec.currency_code := p_currency_code;
127 l_cr_rec.amount := p_amount;
128 l_cr_rec.receivables_trx_id := p_receivables_trx_id;
129 l_cr_rec.misc_payment_source := p_misc_payment_source;
130 l_cr_rec.receipt_number := p_receipt_number;
131 l_cr_rec.receipt_date := p_receipt_date;
132 l_cr_rec.comments := p_comments;
133 l_cr_rec.exchange_rate_type := p_exchange_rate_type;
134 l_cr_rec.exchange_rate := p_exchange_rate;
135 l_cr_rec.exchange_date := p_exchange_date;
136 l_cr_rec.attribute_category := p_attribute_category;
137 l_cr_rec.attribute1 := p_attribute1;
138 l_cr_rec.attribute2 := p_attribute2;
139 l_cr_rec.attribute3 := p_attribute3;
140 l_cr_rec.attribute4 := p_attribute4;
141 l_cr_rec.attribute5 := p_attribute5;
142 l_cr_rec.attribute6 := p_attribute6;
143 l_cr_rec.attribute7 := p_attribute7;
144 l_cr_rec.attribute8 := p_attribute8;
145 l_cr_rec.attribute9 := p_attribute9;
146 l_cr_rec.attribute10 := p_attribute10;
147 l_cr_rec.attribute11 := p_attribute11;
148 l_cr_rec.attribute12 := p_attribute12;
149 l_cr_rec.attribute13 := p_attribute13;
150 l_cr_rec.attribute14 := p_attribute14;
151 l_cr_rec.attribute15 := p_attribute15;
152 l_cr_rec.remit_bank_acct_use_id := p_remittance_bank_account_id;
153 l_cr_rec.deposit_date := p_deposit_date;
154 l_cr_rec.receipt_method_id := p_receipt_method_id;
155 l_cr_rec.doc_sequence_value := p_doc_sequence_value;
156 l_cr_rec.doc_sequence_id := p_doc_sequence_id;
157 l_cr_rec.distribution_set_id := p_distribution_set_id;
158 l_cr_rec.reference_type := p_reference_type;
159 l_cr_rec.vat_tax_id := p_vat_tax_id;
160 l_cr_rec.ussgl_transaction_code := p_ussgl_transaction_code;
161
162 l_cr_rec.global_attribute1 := p_global_attribute1;
163 l_cr_rec.global_attribute2 := p_global_attribute2;
164 l_cr_rec.global_attribute3 := p_global_attribute3;
165 l_cr_rec.global_attribute4 := p_global_attribute4;
166 l_cr_rec.global_attribute5 := p_global_attribute5;
167 l_cr_rec.global_attribute6 := p_global_attribute6;
168 l_cr_rec.global_attribute7 := p_global_attribute7;
169 l_cr_rec.global_attribute8 := p_global_attribute8;
170 l_cr_rec.global_attribute9 := p_global_attribute9;
171 l_cr_rec.global_attribute10 := p_global_attribute10;
172 l_cr_rec.global_attribute11 := p_global_attribute11;
173 l_cr_rec.global_attribute12 := p_global_attribute12;
174 l_cr_rec.global_attribute13 := p_global_attribute13;
175 l_cr_rec.global_attribute14 := p_global_attribute14;
176 l_cr_rec.global_attribute15 := p_global_attribute15;
177 l_cr_rec.global_attribute16 := p_global_attribute16;
178 l_cr_rec.global_attribute17 := p_global_attribute17;
179 l_cr_rec.global_attribute18 := p_global_attribute18;
180 l_cr_rec.global_attribute19 := p_global_attribute19;
181 l_cr_rec.global_attribute20 := p_global_attribute20;
182 l_cr_rec.global_attribute_category := p_global_attribute_category;
183 l_cr_rec.status := p_receipt_status; /* Bug 2688648 */
184 /* Bug fix 3032059 */
185 l_cr_rec.rec_version_number := p_rec_version_number;
186
187 /* Bug fix 2742388 */
188 arp_cr_history_pkg.set_to_dummy( l_crh_rec );
189 l_crh_rec.cash_receipt_id := p_cash_receipt_id;
190 l_crh_rec.cash_receipt_history_id := p_cash_receipt_history_id;
191 l_crh_rec.status := p_state;
192 l_crh_rec.amount := p_amount;
193 l_crh_rec.posting_control_id := p_posting_control_id;
194 arp_cr_history_pkg.lock_hist_compare_p(l_crh_rec);
195 /* End bug fix 2742388 */
196
197 arp_cash_receipts_pkg.lock_compare_p(l_cr_rec);
198
199 IF PG_DEBUG in ('Y', 'C') THEN
200 arp_util.debug('arp_process_misc_receipts2.lock_misc_receipt()-');
201 END IF;
202
203 EXCEPTION
204 WHEN NO_DATA_FOUND THEN
205 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
206 APP_EXCEPTION.Raise_Exception;
207 WHEN OTHERS THEN
208 IF PG_DEBUG in ('Y', 'C') THEN
209 arp_util.debug('EXCEPTION: arp_process_misc_receipts2.lock_misc_receipt()');
210 END IF;
211 RAISE;
212 END lock_misc_receipt;
213
214
215
216 /*===========================================================================+
217 | PROCEDURE |
218 | delete_misc_receipt |
219 | |
220 | DESCRIPTION |
221 | Entity handler that delete miscelleanous transactions. |
222 | |
223 | SCOPE - PUBLIC |
224 | |
225 | NOTES |
226 | |
227 | MODIFICATION HISTORY |
228 | |
229 | 25-OCT-95 OSTEINME created |
230 | 18-Sep-01 Debbie Jancis Modified for MRC trigger removal for |
231 | ar_misc_cash_distributions. Called |
232 | ar mrc engine for processing. |
233 | 21-Jan-02 Rahna Kader Modified delete_misc_receipt procedure |
234 | for deleting records in |
235 | ar_misc_cash_distributions table in cash |
236 | basis accounting.Refer Bug2189383 for details|
237 +===========================================================================*/
238
239 PROCEDURE delete_misc_receipt(
240 p_cash_receipt_id IN NUMBER,
241 p_batch_id IN NUMBER) IS
242
243 l_ae_doc_rec l_ae_doc_rec_type;
244 l_count NUMBER;
245 l_misc_cash_key_value_list gl_ca_utility_pkg.r_key_value_arr; /* MRC */
246 l_accounting_method varchar2(30); -- Bug 2189383
247 l_ar_dist_key_value_list gl_ca_utility_pkg.r_key_value_arr; /* MRC */
248 l_dist_cnt NUMBER; --bug5655154
249 BEGIN
250 IF PG_DEBUG in ('Y', 'C') THEN
251 arp_standard.debug('arp_process_misc_receipts2.delete_misc_receipt()+');
252 END IF;
253
254 -- lock receipt record to make sure no one else has it locked
255
256 arp_cash_receipts_pkg.lock_p(p_cash_receipt_id);
257
258 -- Bug 2189383
259 -- Get the accounting method
260 select arp_standard.sysparm.accounting_method into l_accounting_method from dual;
261
262 IF PG_DEBUG in ('Y', 'C') THEN
263 arp_standard.debug('delete_misc_receipt: ' || 'Acconting Method = '|| l_accounting_method);
264 END IF;
265
266 -- VAT: AR_DISTRIBUTION accounting entry records needs to be deleted
267 -- before deleting AR_CASH_RECEIPTS row
268
269 IF PG_DEBUG in ('Y', 'C') THEN
270 arp_standard.debug('delete_misc_receipt: ' || ' =====> BEGIN <=====');
271 END IF;
272
273 /************* begin bug5655154, commented and replaced with below code
274 -- Bug 2189383
275 -- There will be no 'MCD' records in ar_distributions for cash basis accounting
276
277 IF l_accounting_method ='CASH' THEN
278 SELECT count(mcd.misc_cash_distribution_id)
279 INTO l_count
280 FROM ar_misc_cash_distributions mcd
281 WHERE mcd.cash_receipt_id = p_cash_receipt_id
282 AND mcd.reversal_gl_date IS NULL --For rate adjustments picks up records with new rate
283 AND mcd.posting_control_id = -3 ; --Not posted
284 ELSE
285 SELECT count(mcd.misc_cash_distribution_id)
286 INTO l_count
287 FROM ar_misc_cash_distributions mcd
288 WHERE mcd.cash_receipt_id = p_cash_receipt_id
289 AND mcd.reversal_gl_date IS NULL --For rate adjustments picks up records with new rate
290 AND mcd.posting_control_id = -3 --Not posted
291 AND EXISTS (SELECT 'x'
292 FROM ar_distributions ard
293 WHERE ard.source_id = mcd.misc_cash_distribution_id
294 AND ard.source_table = 'MCD');
295 END IF;
296 *********** end bug5655154 ******/
297
298 --begin bug5655154
299 SELECT count(mcd.misc_cash_distribution_id)
300 INTO l_dist_cnt
301 FROM ar_misc_cash_distributions mcd
302 WHERE mcd.cash_receipt_id = p_cash_receipt_id
303 AND mcd.reversal_gl_date IS NULL --For rate adjustments picks up records with new rate
304 AND mcd.posting_control_id = -3 --Not posted
305 AND EXISTS (SELECT 'x'
306 FROM ar_distributions ard
307 WHERE ard.source_id = mcd.misc_cash_distribution_id
308 AND ard.source_table = 'MCD');
309
310 IF l_dist_cnt = 0 and l_accounting_method = 'CASH' THEN
311 SELECT count(mcd.misc_cash_distribution_id)
312 INTO l_count
313 FROM ar_misc_cash_distributions mcd
314 WHERE mcd.cash_receipt_id = p_cash_receipt_id
315 AND mcd.reversal_gl_date IS NULL --For rate adjustments picks up records with new rate
316 AND mcd.posting_control_id = -3 ; --Not posted
317 ELSE
318 l_count := l_dist_cnt ;
319 END IF ;
320 -- end bug5655154
321
322 IF PG_DEBUG in ('Y', 'C') THEN
323 arp_standard.debug('delete_misc_receipt: ' || ' l_count ' || TO_CHAR(l_count));
324 arp_standard.debug('delete_misc_receipt: ' || 'Delete Misc Cash Receipt start () +');
325 END IF;
326 --
327 IF (l_dist_cnt > 0) THEN -- bug5655154, replaced l_count with l_dist_cnt
328 l_ae_doc_rec.document_type := 'RECEIPT';
329 l_ae_doc_rec.document_id := p_cash_receipt_id;
330 l_ae_doc_rec.accounting_entity_level := 'ONE';
331 l_ae_doc_rec.source_table := 'MCD';
332 l_ae_doc_rec.source_id := '';
333
334 -- Call the delete routine
335 arp_acct_main.Delete_Acct_Entry(l_ae_doc_rec);
336 IF PG_DEBUG in ('Y', 'C') THEN
337 arp_standard.debug('delete_misc_receipt: ' || 'Delete Misc Cash Receipt start () -');
338 END IF;
339 END IF;
340
341 -- delete AR_CASH_RECEIPTS receipt record:
342
343 arp_cash_receipts_pkg.delete_p(p_cash_receipt_id);
344
345 -- delete AR_DISTRIBUTIONS records created for each
346 -- AR_CASH_RECEIPT_HISTORY record.
347
348 -- only delete when there is a AR_MIS_CASH_DISTRIBUTION
349 -- and AR_DISTRIBUTION record.
350 -- ie. when receipt amount is 0, no records for MCD
351 -- and ARD, hence no deletion is required
352
353 IF PG_DEBUG in ('Y', 'C') THEN
354 arp_standard.debug('delete_misc_receipt: ' || ' l_count ' || TO_CHAR(l_count));
355 END IF;
356
357 IF (l_count > 0) THEN
358 IF PG_DEBUG in ('Y', 'C') THEN
359 arp_standard.debug('delete_misc_receipt: ' || ' Delete AR_DISTRIBUTION');
360 END IF;
361
362 DELETE AR_DISTRIBUTIONS
363 WHERE source_table = 'CRH'
364 AND source_id IN (
365 SELECT cash_receipt_history_id
366 FROM ar_cash_receipt_history
367 WHERE cash_receipt_id = p_cash_receipt_id)
368 RETURNING line_id
369 BULK COLLECT INTO l_ar_dist_key_value_list;
370
371 /*---------------------------------+
372 | Calling central MRC library |
373 | for MRC Integration |
374 +---------------------------------*/
375
376 ar_mrc_engine.maintain_mrc_data(
377 p_event_mode => 'DELETE',
378 p_table_name => 'AR_DISTRIBUTIONS',
379 p_mode => 'BATCH',
380 p_key_value_list => l_ar_dist_key_value_list);
381
382 -- delete all AR_MISC_CASH distributions records created for
383 -- this receipt:
384 IF PG_DEBUG in ('Y', 'C') THEN
385 arp_standard.debug('delete_misc_receipt: ' || ' Delete MISC_CASH_DISTRIBUTION');
386 END IF;
387
388 /*--------------------------------+
389 | Added Bulk collect of the the |
390 | misc cash distribution id for |
391 | for use in the MRC engine |
392 +--------------------------------*/
393
394 DELETE AR_MISC_CASH_DISTRIBUTIONS
395 WHERE cash_receipt_id = p_cash_receipt_id
396 RETURNING misc_cash_distribution_id
397 BULK COLLECT INTO l_misc_cash_key_value_list;
398
399 /*---------------------------------+
400 | Calling central MRC library |
401 | for MRC Integration |
402 +---------------------------------*/
403
404 ar_mrc_engine.maintain_mrc_data(
405 p_event_mode => 'DELETE',
406 p_table_name => 'AR_MISC_CASH_DISTRIBUTIONS',
407 p_mode => 'BATCH',
408 p_key_value_list => l_misc_cash_key_value_list);
409
410
411 END IF;
412
413 -- delete all AR_CASH_RECEIPT_HISTORY records created for this
414 -- receipt:
415
416 -- Bug 2021718: call the entity handler for ar_cash_receipt_history rather
417 -- then doing the delete in this package.
418 /*6879698*/
419 ARP_XLA_EVENTS.delete_event
420 ( p_document_id => p_cash_receipt_id,
421 p_doc_table => 'CRH');
422
423 arp_cr_history_pkg.delete_p_cr(p_cash_receipt_id);
424 -- DELETE AR_CASH_RECEIPT_HISTORY
425 -- WHERE cash_receipt_id = p_cash_receipt_id;
426 -- update batch status
427
428 IF (p_batch_id IS NOT NULL) THEN
429 arp_rw_batches_check_pkg.update_batch_status(
430 p_batch_id);
431 END IF;
432
433 IF PG_DEBUG in ('Y', 'C') THEN
434 arp_standard.debug('arp_process_receipts.delete_misc_receipt()-');
435 END IF;
436
437 EXCEPTION
438 WHEN OTHERS THEN
439 IF PG_DEBUG in ('Y', 'C') THEN
440 arp_standard.debug('EXCEPTION: arp_process_misc_receipts2.delete_misc_receipts');
441 END IF;
442 RAISE;
443
444 END delete_misc_receipt;
445
446
447 END ARP_PROCESS_MISC_RECEIPTS2;