[Home] [Help]
PACKAGE BODY: APPS.ARP_PROCESS_BOE
Source
1 PACKAGE BODY ARP_PROCESS_BOE AS
2 /* $Header: ARREBOEB.pls 120.15.12010000.2 2010/01/28 19:50:48 aghoraka ship $ */
3 l_debug VARCHAR2(30);
4
5 /* ------------ Private procedures used by the package --------------------- */
6
7 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
8 /* Bug fix 3927024 */
9 l_actual_amount_total NUMBER;
10 l_actual_count_total NUMBER;
11 l_batch_id NUMBER;
12
13 PROCEDURE val_args_add_or_rm_remit_rec(
14 p_cr_id IN ar_cash_receipts.cash_receipt_id%TYPE,
15 p_ps_id IN ar_payment_schedules.payment_schedule_id%TYPE,
16 p_crh_id IN ar_cash_receipt_history.cash_receipt_history_id%TYPE,
17 p_remittance_bank_account_id IN
18 ar_cash_receipts.remit_bank_acct_use_id%type,
19 p_maturity_date IN
20 ar_payment_schedules.due_date%TYPE,
21 p_batch_id IN NUMBER );
22
23 PROCEDURE val_args_add_or_rm_txn_rec(
24 p_ct_id IN ra_customer_trx.customer_trx_id%TYPE,
25 p_ps_id IN ar_payment_schedules.payment_schedule_id%TYPE,
26 p_paying_customer_id IN ra_customer_trx.paying_customer_id%TYPE,
27 p_customer_bank_account_id IN
28 ra_customer_trx.customer_bank_account_id%TYPE );
29
30 PROCEDURE Val_Create_Auto_Batch_Submit(p_batch_id ar_batches.batch_id%TYPE);
31
32 /* ---------------------- Public functions -------------------------------- */
33
34 /*===========================================================================+
35 | PROCEDURE |
36 | add_or_rm_remit_rec_to_batch |
37 | |
38 | DESCRIPTION |
39 | Procedure called during the create remittance batch process to add |
40 | or remove receipts from a batch. Besides adding or removing receipts |
41 | from a batch, the user can also change certain receipt information |
42 | such as remittance bank account, override_remit_account_flag, bank |
43 | charges and maturity date. |
44 | |
45 | SCOPE - PUBLIC |
46 | |
47 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
48 | arp_cr_history_pkg.update_p - Updates CRH table bank charges info |
49 | arp_cash_receipts_pkg.update_p- Updates CR table with remittance bank |
50 | account, customer bank account and |
51 | override_remit_account_flag |
52 | arp_ps_pkg.update_p - Updates maturity date in PS table. |
53 | arp_util.debug |
54 | |
55 | ARGUMENTS : IN: |
56 | p_cr_id - Cash receipt ID |
57 | p_ps_id - Payment Schedule ID |
58 | p_crh_id - Cash receipt History ID |
59 | p_selected_remittance_batch_id - This field indicates if a |
60 | receipt has been selected to belong to a remittance |
61 | batch. If the field is not NULL, then the field points |
62 | to the remittance batch, the receipt belongs to, else |
63 | the field is NULL. |
64 | p_remittance_bank_account_id - Remittance bank account id |
65 | of the receipt. |
66 | p_override_remit_account_flag - override_remit_account_flag|
67 | of the receipt. |
68 | p_customer_bank_account_id - Custome bank account ID of the|
69 | receipt. |
70 | p_bank_charges- Bank charges, to update CRH row |
71 | p_maturity_date - Maturity date to update DUE_DATE in PS |
72 | p_module_name - Name of module that called this proc. |
73 | p_module_version - Version of the module that called |
74 | this procedure |
75 | OUT: |
76 | p_batch_applied_status - currenct batch aplied status if |
77 | conc. req was started. |
78 | p_request_id - Request id of conc. request. |
79 | |
80 | RETURNS : NONE |
81 | |
82 | NOTES |
83 | |
84 | MODIFICATION HISTORY - Created by Ganesh Vaidee - 10/05/95 |
85 | 11/20/1995 - The procedure now calls the conc. request to start |
86 | approval or formatting of a batch if need be |
87 | depending on the p_call_conc_req flag |
88 | 07/11/1996 OSTEINME Changed code to store bank charges in |
89 | factor_discount_amount of ar_cash_receipts |
90 | table instead of cash_receipt_history table. |
91 | (bug 376326) |
92 | |
93 +===========================================================================*/
94 PROCEDURE add_or_rm_remit_rec_to_batch (
95 p_cr_id IN ar_cash_receipts.cash_receipt_id%TYPE,
96 p_ps_id IN ar_payment_schedules.payment_schedule_id%TYPE,
97 p_crh_id IN ar_cash_receipt_history.cash_receipt_history_id%TYPE,
98 p_selected_remittance_batch_id IN
99 ar_cash_receipts.selected_remittance_batch_id%TYPE,
100 p_remittance_bank_account_id IN
101 ar_cash_receipts.remit_bank_acct_use_id%type,
102 p_override_remit_account_flag IN
103 ar_cash_receipts.override_remit_account_flag%TYPE,
104 p_customer_bank_account_id IN
105 ar_cash_receipts.customer_bank_account_id%TYPE,
106 p_bank_charges IN
107 ar_cash_receipt_history.factor_discount_amount%TYPE,
108 p_maturity_date IN
109 ar_payment_schedules.due_date%TYPE,
110 p_batch_id IN NUMBER,
111 p_control_count IN NUMBER,
112 p_control_amount IN NUMBER,
113 p_module_name IN VARCHAR2,
114 p_module_version IN VARCHAR2 ) IS
115 --
116 l_cr_rec ar_cash_receipts%ROWTYPE;
117 l_ps_rec ar_payment_schedules%ROWTYPE;
118 l_crh_rec ar_cash_receipt_history%ROWTYPE;
119 l_batch_rec ar_batches%ROWTYPE;
120 --
121 l_approve_flag VARCHAR2(1) DEFAULT 'N';
122 l_format_flag VARCHAR2(1) DEFAULT 'N';
123 --
124 --
125 BEGIN
126 IF PG_DEBUG in ('Y', 'C') THEN
127 arp_standard.debug('val_args_add_or_rm_remit_rec: ' || 'arp_process_boe.add_or_rm_remit_rec_to_batch()+');
128 END IF;
129 --
130 IF PG_DEBUG in ('Y', 'C') THEN
131 arp_standard.debug('val_args_add_or_rm_remit_rec: ' || 'cr_id = '||to_char( p_cr_id ) );
132 arp_standard.debug('val_args_add_or_rm_remit_rec: ' || 'ps_id = '||to_char( p_ps_id ) );
133 arp_standard.debug('val_args_add_or_rm_remit_rec: ' || 'crh_id = '||to_char( p_crh_id ) );
134 arp_standard.debug('val_args_add_or_rm_remit_rec: ' || 'selected_remittance_batch_id = '||
135 to_char( p_selected_remittance_batch_id ) );
136 arp_standard.debug('val_args_add_or_rm_remit_rec: ' || 'remit_bank_acct_use_id = '||
137 to_char( p_remittance_bank_account_id ) );
138 arp_standard.debug('val_args_add_or_rm_remit_rec: ' || 'override_remit_account_flag = '||
139 p_override_remit_account_flag );
140 arp_standard.debug('val_args_add_or_rm_remit_rec: ' || 'customer_bank_account_id = '||
141 to_char( p_customer_bank_account_id ) );
142 arp_standard.debug('val_args_add_or_rm_remit_rec: ' || 'bank_charges = '||
143 to_char( p_bank_charges ) );
144 arp_standard.debug('val_args_add_or_rm_remit_rec: ' || 'maturity_date = '||
145 to_char( p_maturity_date ) );
146 arp_standard.debug('val_args_add_or_rm_remit_rec: ' || 'batch_id = '||
147 to_char( p_batch_id ) );
148 arp_standard.debug('val_args_add_or_rm_remit_rec: ' || 'control_count = '||
149 to_char( p_control_count ) );
150 arp_standard.debug('val_args_add_or_rm_remit_rec: ' || 'control_amount = '||
151 to_char( p_control_amount ) );
152 END IF;
153 --
154 -- Validate input arguments
155 --
156 IF ( p_module_name IS NOT NULL and p_module_version IS NOT NULL ) THEN
157 val_args_add_or_rm_remit_rec( p_cr_id, p_ps_id,
158 p_crh_id, p_remittance_bank_account_id,
159 p_maturity_date, p_batch_id );
160 END IF;
161 --
162 -- Update Cash receipts table
163 --
164 l_cr_rec.cash_receipt_id := p_cr_id;
165 arp_cash_receipts_pkg.fetch_p( l_cr_rec );
166 l_cr_rec.selected_remittance_batch_id := p_selected_remittance_batch_id;
167 l_cr_rec.remit_bank_acct_use_id := p_remittance_bank_account_id;
168 l_cr_rec.override_remit_account_flag := p_override_remit_account_flag;
169 l_cr_rec.customer_bank_account_id := p_customer_bank_account_id;
170
171 -- The following line was added to fix bug 376326 (see below):
172
173 l_cr_rec.factor_discount_amount := p_bank_charges;
174
175 arp_cash_receipts_pkg.update_p( l_cr_rec );
176
177 /* -------------------------------------------------------------------------
178
179 Bug 376326:
180
181 The following code has been removed, because it incorrectly
182 updates the crh record of a confirmed receipt, instead of
183 updating the actual cr record. Instead, I've added the update
184 of the cr.factor_discount_amount column above. OSTEINME, 7/11/96
185
186 --
187 -- Update CRH table with bank charges
188 --
189 arp_cr_history_pkg.set_to_dummy( l_crh_rec );
190 l_crh_rec.cash_receipt_history_id := p_crh_id;
191 l_crh_rec.factor_discount_amount := p_bank_charges;
192 l_crh_rec.amount := l_cr_rec.amount - NVL( p_bank_charges, 0 );
193 arp_cr_history_pkg.update_p( l_crh_rec, l_crh_rec.cash_receipt_history_id );
194
195 --------------------------------------------------------------------------*/
196 --
197 -- Update PS table with due date
198 --
199 /*--------------------------------------------------------------------
200 Modified for MISC receipts remittance 377583
201 Payment Schedule should be updated only for CASH receipts
202 --------------------------------------------------------------------*/
203
204 IF (p_ps_id IS NOT NULL)
205 THEN
206 arp_ps_pkg.set_to_dummy( l_ps_rec );
207 l_ps_rec.payment_schedule_id := p_ps_id;
208 l_ps_rec.due_date := p_maturity_date;
209 arp_ps_pkg.update_p( l_ps_rec, l_ps_rec.payment_schedule_id );
210 END IF;
211
212 --
213 -- Update Batch table with control count and control cmount
214 --
215 /* Bug fix 3927024 */
216 IF l_batch_id IS NULL OR l_batch_id <> p_batch_id THEN
217
218 select nvl(control_amount,0), nvl(control_count,0)
219 into l_actual_amount_total,l_actual_count_total
220 from ar_batches
221 where batch_id = p_batch_id;
222
223 l_batch_id := p_batch_id;
224
225 END IF;
226
227 l_actual_count_total := l_actual_count_total + p_control_count;
228 l_actual_amount_total := l_actual_amount_total + p_control_amount;
229
230 IF PG_DEBUG in ('Y', 'C') THEN
231 arp_standard.debug('l_actual_count_total = '||to_char(l_actual_count_total));
232 arp_standard.debug('l_actual_amount_total = '||to_char(l_actual_amount_total));
233 END IF;
234 /* End Bug fix 3927024 */
235
236 arp_cr_batches_pkg.set_to_dummy( l_batch_rec );
237 l_batch_rec.batch_id := p_batch_id;
238 l_batch_rec.control_count := l_actual_count_total; /* Bug 3927024 */
239 l_batch_rec.control_amount := l_actual_amount_total; /* bug 3927024 */
240 arp_cr_batches_pkg.update_p( l_batch_rec, l_batch_rec.batch_id );
241 --
242 IF PG_DEBUG in ('Y', 'C') THEN
243 arp_standard.debug('val_args_add_or_rm_remit_rec: ' || 'arp_process_boe.add_or_rm_remit_rec_to_batch()-');
244 END IF;
245 --
246 EXCEPTION
247 WHEN OTHERS THEN
248 IF PG_DEBUG in ('Y', 'C') THEN
249 arp_standard.debug('val_args_add_or_rm_remit_rec: ' ||
250 'EXCEPTION: arp_process_boe.add_or_rm_remit_rec_to_batch' );
251 END IF;
252 RAISE;
253 --
254 END add_or_rm_remit_rec_to_batch;
255 --
256 /*===========================================================================+
257 | PROCEDURE |
258 | val_args_add_or_rm_remit_rec |
259 | |
260 | DESCRIPTION |
261 | Validate arguments passed to add_or_rm_remit_rec_to_batch procedure |
262 | |
263 | SCOPE - PRIVATE |
264 | |
265 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
266 | |
267 | ARGUMENTS : IN: |
268 | OUT: |
269 | |
270 | RETURNS : NONE |
271 | |
272 | NOTES |
273 | |
274 | MODIFICATION HISTORY - Created by Ganesh Vaidee - 10/05/95 |
275 | 11/20/1995 - Added code to check batch id and which action if |
276 | conc. req flag is set to 'Y' |
277 | |
278 +===========================================================================*/
279 PROCEDURE val_args_add_or_rm_remit_rec(
280 p_cr_id IN ar_cash_receipts.cash_receipt_id%TYPE,
281 p_ps_id IN ar_payment_schedules.payment_schedule_id%TYPE,
282 p_crh_id IN ar_cash_receipt_history.cash_receipt_history_id%TYPE,
283 p_remittance_bank_account_id IN
284 ar_cash_receipts.remit_bank_acct_use_id%type,
285 p_maturity_date IN
286 ar_payment_schedules.due_date%TYPE,
287 p_batch_id IN NUMBER ) IS
288
289 cr_type ar_cash_receipts.type%TYPE;
290
291 BEGIN
292 IF PG_DEBUG in ('Y', 'C') THEN
293 arp_standard.debug( 'arp_process_boe.val_args_add_or_rm_remit_rec() +');
294 END IF;
295 --
296 /*---------------------------------------------------------------------
297 Modified by Nilesh for MISC receipt remittance 377583
298 Payment Schedule should exist only for CASH receipts
299 ---------------------------------------------------------------------*/
300 -- IF ( p_cr_id is NULL OR p_ps_id is NULL OR
301 IF ( p_cr_id is NULL OR
302 p_crh_id is NULL OR p_remittance_bank_account_id is NULL OR
303 p_maturity_date is NULL OR p_batch_id is NULL ) THEN
304 FND_MESSAGE.set_name ('AR', 'AR_ARGUEMENTS_FAIL' );
305 APP_EXCEPTION.raise_exception;
306 END IF;
307
308 SELECT type INTO cr_type FROM ar_cash_receipts WHERE cash_receipt_id = p_cr_id;
309
310 IF (cr_type = 'CASH' AND p_ps_id is NULL) THEN
311
312 FND_MESSAGE.set_name ('AR', 'AR_ARGUEMENTS_FAIL' );
313 APP_EXCEPTION.raise_exception;
314 END IF;
315
316 --
317 IF PG_DEBUG in ('Y', 'C') THEN
318 arp_standard.debug( 'arp_process_boe.val_args_add_or_rm_remit_rec() -');
319 END IF;
320 EXCEPTION
321 WHEN OTHERS THEN
322 IF PG_DEBUG in ('Y', 'C') THEN
323 arp_standard.debug('val_args_add_or_rm_remit_rec: ' ||
324 'EXCEPTION: arp_process_boe.val_args_add_or_rm_remit_rec' );
325 END IF;
326 RAISE;
327 END val_args_add_or_rm_remit_rec;
328 --
329 PROCEDURE create_remit_batch_conc_req( p_create_flag IN VARCHAR2,
330 p_approve_flag IN VARCHAR2,
331 p_format_flag IN VARCHAR2,
332 p_batch_id IN ar_batches.batch_id%TYPE,
333 p_due_date_low IN ar_payment_schedules.due_date%TYPE,
334 p_due_date_high IN ar_payment_schedules.due_date%TYPE,
335 p_receipt_date_low IN ar_cash_receipts.receipt_date%TYPE,
336 p_receipt_date_high IN ar_cash_receipts.receipt_date%TYPE,
337 p_receipt_number_low IN ar_cash_receipts.receipt_number%TYPE,
338 p_receipt_number_high IN ar_cash_receipts.receipt_number%TYPE,
339 p_document_number_low IN NUMBER,
340 p_document_number_high IN NUMBER,
341 p_customer_number_low IN hz_cust_accounts.account_number%TYPE,
342 p_customer_number_high IN hz_cust_accounts.account_number%TYPE,
343 p_customer_name_low IN hz_parties.party_name%TYPE,
344 p_customer_name_high IN hz_parties.party_name%TYPE,
345 p_customer_id IN hz_cust_accounts.cust_account_id%TYPE,
346 p_location_low IN hz_cust_site_uses.location%TYPE,
347 p_location_high IN hz_cust_site_uses.location%TYPE,
348 p_site_use_id IN hz_cust_site_uses.site_use_id%TYPE,
349 p_remit_total_low IN NUMBER,
350 p_remit_total_high IN NUMBER,
351 p_request_id OUT NOCOPY NUMBER,
352 p_batch_applied_status OUT NOCOPY VARCHAR2,
353 p_module_name IN VARCHAR2,
354 p_module_version IN VARCHAR2 ) IS
355 l_request_id NUMBER;
356 l_bat_rec ar_batches%ROWTYPE;
357 l_org_id NUMBER;
358 BEGIN
359 --
360 IF PG_DEBUG in ('Y', 'C') THEN
361 arp_standard.debug( 'arp_process_boe.create_remit_batch_conc_req()+');
362 END IF;
363 --
364 IF PG_DEBUG in ('Y', 'C') THEN
365 arp_standard.debug('create_remit_batch_conc_req: ' || 'Batch Id '||p_batch_id );
366 arp_standard.debug('create_remit_batch_conc_req: ' || 'create_flag = '||p_create_flag );
367 arp_standard.debug('create_remit_batch_conc_req: ' || 'Approve_flag = '||p_approve_flag );
368 arp_standard.debug('create_remit_batch_conc_req: ' || 'Format_flag = '||p_format_flag );
369 arp_standard.debug('create_remit_batch_conc_req: ' || 'batch Id = '||to_char( p_batch_id ) );
370 arp_standard.debug('create_remit_batch_conc_req: ' || 'receipt_num_low = '||p_receipt_number_low );
371 arp_standard.debug('create_remit_batch_conc_req: ' || 'receipt_nue_hi = '||p_receipt_number_high );
372 arp_standard.debug('create_remit_batch_conc_req: ' || 'receipt_date_low = '||to_char( p_receipt_date_low ));
373 arp_standard.debug('create_remit_batch_conc_req: ' || 'receipt_date_hi = '||to_char( p_receipt_date_high));
374 arp_standard.debug('create_remit_batch_conc_req: ' || 'due_date_low = '||to_char( p_due_date_low));
375 arp_standard.debug('create_remit_batch_conc_req: ' || 'due_date_high = '||to_char( p_due_date_high));
376 arp_standard.debug('create_remit_batch_conc_req: ' || 'doc_num_low = '||to_char( p_document_number_low));
377 arp_standard.debug('create_remit_batch_conc_req: ' || 'doc_num_high = '||to_char( p_document_number_high));
378 arp_standard.debug('create_remit_batch_conc_req: ' || 'cust_num_low = '||p_document_number_low );
379 arp_standard.debug('create_remit_batch_conc_req: ' || 'cust_num_high = '||p_customer_number_high );
380 arp_standard.debug('create_remit_batch_conc_req: ' || 'cust_name_low = '||p_customer_name_low );
381 arp_standard.debug('create_remit_batch_conc_req: ' || 'cust_name_high = '||p_customer_name_high );
382 arp_standard.debug('create_remit_batch_conc_req: ' || 'customer_id = '||to_char( p_customer_id ) );
383 arp_standard.debug('create_remit_batch_conc_req: ' || 'site_use_low = '||p_location_low );
384 arp_standard.debug('create_remit_batch_conc_req: ' || 'site_use_high = '||p_location_high );
385 arp_standard.debug('create_remit_batch_conc_req: ' || 'site_use_id = '||to_char( p_site_use_id ) );
386 END IF;
387 --
388 -- Validate input arguments
389 --
390 IF ( p_module_name IS NOT NULL and p_module_version IS NOT NULL ) THEN
391 IF ( p_batch_id IS NULL ) THEN
392 FND_MESSAGE.set_name ('AR', 'AR_ARGUEMENTS_FAIL' );
393 APP_EXCEPTION.raise_exception;
394 END IF;
395 END IF;
396 --
397 -- Call the concurrent program
398 --
399 -- Shiv Ragunat , 9/11/96 ,Modified the Date parameters to convert it
400 -- to DD-MON-YYYY format, so that the call succeeds for any
401 -- NLS date format.
402 --
403 /* Bug 5190715 */
404 /* Bug 5699734 - Changed the logic of getting the org_id - Getting from ar_system_parameters is more reliable */
405 IF PG_DEBUG in ('Y', 'C') THEN
406 arp_standard.debug('Selecting org_id from ar_system_parameters and p_batch_id : '||p_batch_id);
407 END IF;
408 -- l_org_id := TO_NUMBER(FND_PROFILE.value('ORG_ID'));
409 SELECT org_id
410 INTO l_org_id
411 FROM ar_system_parameters;
412 --l_org_id := nvl(mo_global.get_current_org_id, mo_utils.get_default_org_id);
413
414 fnd_request.set_org_id(l_org_id);
415 l_request_id := FND_REQUEST.submit_request( 'AR',
416 'AUTOREMAPI', --PAYMENT_UPTAKE ARZCAR_REMIT
417 NULL,
418 -- 'Create Automatic remittance receipt Batch',
419 TO_CHAR(SYSDATE,'DD-MON-YYYY'),
420 FALSE, 'REMIT',
421 NULL, -- Batch Date
422 NULL, -- Batch GL Date
423 p_create_flag,
424 p_approve_flag, p_format_flag, p_batch_id,
425 l_debug,
426 NULL, -- Batch Currency
427 NULL, -- Exchange Date
428 NULL, -- Exchange Rate
429 NULL, -- Exchange Rate Type
430 NULL, -- Remit Method Code
431 NULL, -- Receipt Class
432 NULL, -- Payment Method
433 NULL, -- Media Reference
434 NULL, -- Remit Bank Branch
435 NULL, -- Remit Bank Account
436 NULL, -- Bank Deposit Number
437 NULL, -- Batch Comments
438 fnd_date.date_to_canonical(p_receipt_date_low),
439 fnd_date.date_to_canonical(p_receipt_date_high),
440 fnd_date.date_to_canonical(p_due_date_low),
441 fnd_date.date_to_canonical(p_due_date_high),
442 p_receipt_number_low,
443 p_receipt_number_high,
444 p_document_number_low,
445 p_document_number_high,
446 p_customer_number_low,
447 p_customer_number_high,
448 p_customer_name_low, p_customer_name_high,
449 p_customer_id,
450 p_location_low, p_location_high,
451 p_site_use_id,
452 fnd_number.number_to_canonical(p_remit_total_low),
453 fnd_number.number_to_canonical(p_remit_total_high),
454 NULL,
455 NULL,
456 NULL,
457 NULL
458 );
459 --
460 IF PG_DEBUG in ('Y', 'C') THEN
461 arp_standard.debug('create_remit_batch_conc_req: ' || 'Out Request ID = '||to_char( l_request_id ) );
462 END IF;
463 p_request_id := l_request_id;
464 --
465 --
466 -- Update batches row with payment method id and batch applied status
467 --
468 arp_cr_batches_pkg.set_to_dummy( l_bat_rec );
469 l_bat_rec.batch_id := p_batch_id;
470 l_bat_rec.operation_request_id := l_request_id;
471 IF ( p_create_flag = 'Y' ) THEN
472 l_bat_rec.batch_applied_status := 'STARTED_CREATION';
473 ELSIF ( p_approve_flag = 'Y' ) THEN
474 l_bat_rec.batch_applied_status := 'STARTED_APPROVAL';
475 ELSE
476 l_bat_rec.batch_applied_status := 'STARTED_FORMAT';
477 END IF;
478 --
479 arp_cr_batches_pkg.update_p( l_bat_rec, l_bat_rec.batch_id );
480 p_batch_applied_status := l_bat_rec.batch_applied_status;
481 --
482 IF PG_DEBUG in ('Y', 'C') THEN
483 arp_standard.debug( 'arp_process_boe.create_remit_batch_conc_req()-');
484 END IF;
485 --
486 EXCEPTION
487 WHEN OTHERS THEN
488 IF PG_DEBUG in ('Y', 'C') THEN
489 arp_standard.debug('create_remit_batch_conc_req: ' ||
490 'EXCEPTION: arp_process_boe.create_remit_batch_conc_req' );
491 END IF;
492 RAISE;
493 --
494 END create_remit_batch_conc_req;
495 --
496 --
497 --
498 PROCEDURE app_fmt_remit_batch_conc_req( p_approve_flag IN VARCHAR2,
499 p_format_flag IN VARCHAR2,
500 p_batch_id IN ar_batches.batch_id%TYPE,
501 p_request_id OUT NOCOPY NUMBER,
502 p_batch_applied_status OUT NOCOPY VARCHAR2,
503 p_module_name IN VARCHAR2,
504 p_module_version IN VARCHAR2 ) IS
505 l_request_id NUMBER;
506 l_bat_rec ar_batches%ROWTYPE;
507 l_org_id NUMBER;
508 BEGIN
509 --
510 IF PG_DEBUG in ('Y', 'C') THEN
511 arp_standard.debug( 'arp_process_boe.app_fmt_remit_batch_conc_req()+');
512 END IF;
513 --
514 IF PG_DEBUG in ('Y', 'C') THEN
515 arp_standard.debug('app_fmt_remit_batch_conc_req: ' || 'Batch Id '||p_batch_id );
516 arp_standard.debug('app_fmt_remit_batch_conc_req: ' || 'Approve_flag = '||p_approve_flag );
517 arp_standard.debug('app_fmt_remit_batch_conc_req: ' || 'Format_flag = '||p_format_flag );
518 END IF;
519 --
520 IF ( p_module_name IS NOT NULL and p_module_version IS NOT NULL ) THEN
521 IF ( p_batch_id IS NULL ) THEN
522 FND_MESSAGE.set_name ('AR', 'AR_ARGUEMENTS_FAIL' );
523 APP_EXCEPTION.raise_exception;
524 END IF;
525 END IF;
526 --
527 -- Call the concurrent program
528 --
529 /* Additional Fix identified as a part of Bug 5699734 */
530 /* Bug 5699734 - Changed logic to get org_id - Getting from ar_system_parameters would be more reliable */
531 --l_org_id := TO_NUMBER(FND_PROFILE.value('ORG_ID'));
532 IF PG_DEBUG in ('Y', 'C') THEN
533 arp_standard.debug('Selecting org_id from ar_system_parameters and p_batch_id : '||p_batch_id);
534 END IF;
535 SELECT org_id
536 INTO l_org_id
537 FROM ar_system_parameters;
538 -- l_org_id := nvl(mo_global.get_current_org_id, mo_utils.get_default_org_id);
539 /* Fix bug 5699734 ends */
540
541 fnd_request.set_org_id(l_org_id);
542
543 /* Adding additional debug messages - As a part of Bug 5699734 */
544 IF PG_DEBUG in ('Y', 'C') THEN
545 arp_standard.debug('app_fmt_remit_batch_conc_req: Submitting Conc Req for Approve/Format Remittance Receipt Batch with org : ' ||l_org_id);
546 END IF;
547 /* End of additional Debug messages */
548 l_request_id := FND_REQUEST.submit_request( 'AR',
549 'AUTOREMAPI', --PAYMENT_UPTAKE ARZCAR_REMIT
550 NULL,
551 -- 'Approve/Format Remittance Receipt Batch',
552 SYSDATE, FALSE, 'REMIT',
553 NULL, -- Batch Date
554 NULL, -- Batch GL Date
555 'N', -- p_create_flag,
556 p_approve_flag, p_format_flag, p_batch_id,
557 l_debug,
558 NULL, -- Batch Currency
559 NULL, -- Exchange Date
560 NULL, -- Exchange Rate
561 NULL, -- Exchange Rate Type
562 NULL, -- Remit Method Code
563 NULL, -- Receipt Class
564 NULL, -- Payment Method
565 NULL, -- Media Reference
566 NULL, -- Remit Bank Branch
567 NULL, -- Remit Bank Account
568 NULL, -- Bank Deposit Number
569 NULL, -- Batch Comments
570 NULL, NULL,
571 NULL, NULL,
572 NULL, NULL,
573 NULL, NULL,
574 NULL, NULL,
575 NULL, NULL,
576 NULL,
577 NULL, NULL,
578 NULL,
579 NULL, NULL,
580 NULL, NULL,
581 NULL, NULL
582 );
583 --
584 IF PG_DEBUG in ('Y', 'C') THEN
585 arp_standard.debug('app_fmt_remit_batch_conc_req: ' || 'Out Request ID = '||to_char( l_request_id ) );
586 END IF;
587 p_request_id := l_request_id;
588 --
589 -- Update batch to set batch applied_status
590 --
591 arp_cr_batches_pkg.set_to_dummy( l_bat_rec );
592 l_bat_rec.batch_id := p_batch_id;
593 l_bat_rec.operation_request_id := l_request_id;
594 IF ( p_approve_flag = 'Y' ) THEN
595 l_bat_rec.batch_applied_status := 'STARTED_APPROVAL';
596 ELSE
597 l_bat_rec.batch_applied_status := 'STARTED_FORMAT';
598 END IF;
599 arp_cr_batches_pkg.update_p( l_bat_rec, l_bat_rec.batch_id );
600 p_batch_applied_status := l_bat_rec.batch_applied_status;
601 --
602 IF PG_DEBUG in ('Y', 'C') THEN
603 arp_standard.debug( 'arp_process_boe.app_fmt_remit_batch_conc_req()-');
604 END IF;
605 --
606 EXCEPTION
607 WHEN OTHERS THEN
608 IF PG_DEBUG in ('Y', 'C') THEN
609 arp_standard.debug('app_fmt_remit_batch_conc_req: ' ||
610 'EXCEPTION: arp_process_boe.app_fmt_remit_batch_conc_req' );
611 END IF;
612 RAISE;
613 --
614 END app_fmt_remit_batch_conc_req;
615 --
616 PROCEDURE app_fmt_auto_batch_conc_req( p_approve_flag IN VARCHAR2,
617 p_format_flag IN VARCHAR2,
618 p_batch_id IN ar_batches.batch_id%TYPE,
619 p_request_id OUT NOCOPY NUMBER,
620 p_batch_applied_status OUT NOCOPY VARCHAR2,
621 p_module_name IN VARCHAR2,
622 p_module_version IN VARCHAR2 ) IS
623 l_request_id NUMBER;
624 l_bat_rec ar_batches%ROWTYPE;
625 l_org_id NUMBER;
626 BEGIN
627 --
628 IF PG_DEBUG in ('Y', 'C') THEN
629 arp_standard.debug( 'arp_process_boe.app_fmt_auto_batch_conc_req()+');
630 END IF;
631 --
632 IF PG_DEBUG in ('Y', 'C') THEN
633 arp_standard.debug('app_fmt_auto_batch_conc_req: ' || 'Batch Id '||p_batch_id );
634 arp_standard.debug('app_fmt_auto_batch_conc_req: ' || 'Approve_flag = '||p_approve_flag );
635 arp_standard.debug('app_fmt_auto_batch_conc_req: ' || 'Format_flag = '||p_format_flag );
636 END IF;
637 --
638 IF ( p_module_name IS NOT NULL and p_module_version IS NOT NULL ) THEN
639 IF ( p_batch_id IS NULL ) THEN
640 FND_MESSAGE.set_name ('AR', 'AR_ARGUEMENTS_FAIL' );
641 APP_EXCEPTION.raise_exception;
642 END IF;
643 END IF;
644 --
645 -- Call the concurrent program
646 --
647 /* Additional Fix identified as a part of Bug 5699734*/
648 /* Bug 5699734 - Changed the logic of getting org_id - Getting from ar_system_parameters would be more reliable */
649 --l_org_id := TO_NUMBER(FND_PROFILE.value('ORG_ID'));
650 IF PG_DEBUG in ('Y', 'C') THEN
651 arp_standard.debug('Selecting org_id from ar_system_parameters and p_batch_id : '||p_batch_id);
652 END IF;
653 SELECT org_id
654 INTO l_org_id
655 FROM ar_system_parameters;
656 --l_org_id := nvl(mo_global.get_current_org_id, mo_utils.get_default_org_id);
657 /* Fix for bug 5699734 ends */
658
659 fnd_request.set_org_id(l_org_id);
660
661 /* Additional Debug Messages as a part of bug 5699734*/
662 IF PG_DEBUG in ('Y', 'C') THEN
663 arp_standard.debug('app_fmt_auto_batch_conc_req: Submitting Conc Req Approve/Format Automatic receipt Batch with org :'||l_org_id);
664 END IF;
665 /* Additional debug messages ends */
666
667 l_request_id := FND_REQUEST.submit_request( 'AR',
668 'AR_AUTORECAPI', -- PAYMENT_UPTAKE ARZCAR_RECEIPT
669 NULL,
670 -- 'Approve/Format Automatic receipt Batch',
671 SYSDATE, FALSE, 'RECEIPT',
672 NULL, -- Batch Date
673 NULL, -- Batch GL Date
674 'N', -- p_create_flag,
675 p_approve_flag, p_format_flag, p_batch_id,
676 l_debug,
677 NULL, -- Batch Currency
678 NULL, -- Exchange Date
679 NULL, -- Exchange Rate
680 NULL, -- Exchange Rate Type
681 NULL, -- Remit Method Code
682 NULL, -- Receipt Class
683 NULL, -- Payment Method
684 NULL, -- Media Reference
685 NULL, -- Remit Bank Branch
686 NULL, -- Remit Bank Account
687 NULL, -- Bank Deposit Number
688 NULL, -- Batch Comments
689 NULL, NULL,
690 NULL, NULL,
691 NULL, NULL,
692 NULL, NULL,
693 NULL, NULL,
694 NULL, NULL,
695 NULL,
696 NULL, NULL,
697 NULL,
698 NULL, NULL,
699 NULL, NULL,
700 NULL, NULL
701 );
702 --
703 IF PG_DEBUG in ('Y', 'C') THEN
704 arp_standard.debug('app_fmt_auto_batch_conc_req: ' || 'Out Request ID = '||to_char( l_request_id ) );
705 END IF;
706 p_request_id := l_request_id;
707 --
708 --
709 -- Update batch to set batch applied_status
710 --
711 arp_cr_batches_pkg.set_to_dummy( l_bat_rec );
712 l_bat_rec.batch_id := p_batch_id;
713 l_bat_rec.operation_request_id := l_request_id;
714 IF ( p_approve_flag = 'Y' ) THEN
715 l_bat_rec.batch_applied_status := 'STARTED_APPROVAL';
716 ELSE
717 l_bat_rec.batch_applied_status := 'STARTED_FORMAT';
718 END IF;
719 arp_cr_batches_pkg.update_p( l_bat_rec, l_bat_rec.batch_id );
720 p_batch_applied_status := l_bat_rec.batch_applied_status;
721 --
722 IF PG_DEBUG in ('Y', 'C') THEN
723 arp_standard.debug( 'arp_process_boe.app_fmt_auto_batch_conc_req()-');
724 END IF;
725 --
726 EXCEPTION
727 WHEN OTHERS THEN
728 IF PG_DEBUG in ('Y', 'C') THEN
729 arp_standard.debug('app_fmt_auto_batch_conc_req: ' ||
730 'EXCEPTION: arp_process_boe.app_fmt_auto_batch_conc_req' );
731 END IF;
732 RAISE;
733 --
734 END app_fmt_auto_batch_conc_req;
735 --
736 PROCEDURE create_auto_batch_conc_req( p_create_flag IN VARCHAR2,
737 p_approve_flag IN VARCHAR2,
738 p_format_flag IN VARCHAR2,
739 p_batch_id IN ar_batches.batch_id%TYPE,
740 p_due_date_low IN ar_payment_schedules.due_date%TYPE,
741 p_due_date_high IN ar_payment_schedules.due_date%TYPE,
742 p_trx_date_low IN ra_customer_trx.trx_date%TYPE,
743 p_trx_date_high IN ra_customer_trx.trx_date%TYPE,
744 p_trx_number_low IN ra_customer_trx.trx_number%TYPE,
745 p_trx_number_high IN ra_customer_trx.trx_number%TYPE,
746 p_document_number_low IN NUMBER,
747 p_document_number_high IN NUMBER,
748 p_customer_number_low IN hz_cust_accounts.account_number%TYPE,
749 p_customer_number_high IN hz_cust_accounts.account_number%TYPE,
750 p_customer_name_low IN hz_parties.party_name%TYPE,
751 p_customer_name_high IN hz_parties.party_name%TYPE,
752 p_customer_id IN hz_cust_accounts.cust_account_id%TYPE,
753 p_location_low IN hz_cust_site_uses.location%TYPE,
754 p_location_high IN hz_cust_site_uses.location%TYPE,
755 p_site_use_id IN hz_cust_site_uses.site_use_id%TYPE,
756 p_billing_number_low IN ar_cons_inv.cons_billing_number%TYPE,
757 p_billing_number_high IN ar_cons_inv.cons_billing_number%TYPE,
758 p_request_id OUT NOCOPY NUMBER,
759 p_batch_applied_status OUT NOCOPY VARCHAR2,
760 p_module_name IN VARCHAR2,
761 p_module_version IN VARCHAR2,
762 p_bank_account_low IN VARCHAR2,
763 p_bank_account_high IN VARCHAR2 ) IS
764
765 l_request_id NUMBER;
766 l_bat_rec ar_batches%ROWTYPE;
767 l_org_id NUMBER;
768 l_program_name ap_payment_programs.program_name%TYPE;
769 l_batch_app_status ar_batches.batch_applied_status%TYPE;
770 BEGIN
771 --
772 IF PG_DEBUG in ('Y', 'C') THEN
773 arp_standard.debug( 'arp_process_boe.create_auto_batch_conc_req()+');
774 END IF;
775 --
776 IF PG_DEBUG in ('Y', 'C') THEN
777 arp_standard.debug('create_auto_batch_conc_req: ' || 'create_flag = '||p_create_flag );
778 arp_standard.debug('create_auto_batch_conc_req: ' || 'Approve_flag = '||p_approve_flag );
779 arp_standard.debug('create_auto_batch_conc_req: ' || 'Format_flag = '||p_format_flag );
780 arp_standard.debug('create_auto_batch_conc_req: ' || 'batch Id = '||to_char( p_batch_id ) );
781 arp_standard.debug('create_auto_batch_conc_req: ' || 'trx_num_low = '||p_trx_number_low );
782 arp_standard.debug('create_auto_batch_conc_req: ' || 'trx_num_hi = '||p_trx_number_high );
783 arp_standard.debug('create_auto_batch_conc_req: ' || 'trx_date_low = '||to_char( p_trx_date_low ));
784 arp_standard.debug('create_auto_batch_conc_req: ' || 'trx_date_hi = '||to_char( p_trx_date_high));
785 arp_standard.debug('create_auto_batch_conc_req: ' || 'due_date_low = '||to_char( p_due_date_low));
786 arp_standard.debug('create_auto_batch_conc_req: ' || 'due_date_high = '||to_char( p_due_date_high));
787 arp_standard.debug('create_auto_batch_conc_req: ' || 'doc_num_low = '||to_char( p_document_number_low));
788 arp_standard.debug('create_auto_batch_conc_req: ' || 'doc_num_high = '||to_char( p_document_number_high));
789 arp_standard.debug('create_auto_batch_conc_req: ' || 'cust_num_low = '||p_document_number_low );
790 arp_standard.debug('create_auto_batch_conc_req: ' || 'cust_num_high = '||p_customer_number_high );
791 arp_standard.debug('create_auto_batch_conc_req: ' || 'cust_name_low = '||p_customer_name_low );
792 arp_standard.debug('create_auto_batch_conc_req: ' || 'cust_name_high = '||p_customer_name_high );
793 arp_standard.debug('create_auto_batch_conc_req: ' || 'customer_id = '||to_char( p_customer_id ) );
794 arp_standard.debug('create_auto_batch_conc_req: ' || 'site_use_low = '||p_location_low );
795 arp_standard.debug('create_auto_batch_conc_req: ' || 'site_use_high = '||p_location_high );
796 arp_standard.debug('create_auto_batch_conc_req: ' || 'site_use_id = '||to_char( p_site_use_id ) );
797 arp_standard.debug('create_auto_batch_conc_req: ' || 'billing_number_low = '|| p_billing_number_low );
798 arp_standard.debug('create_auto_batch_conc_req: ' || 'billing_number_high = '|| p_billing_number_high );
799 arp_standard.debug('create_auto_batch_conc_req: ' || 'bank_account_low = '|| p_bank_account_low );
800 arp_standard.debug('create_auto_batch_conc_req: ' || 'bank_account_high = '|| p_bank_account_high );
801 END IF;
802 --
803 -- Validate input arguments
804 --
805 IF ( p_module_name IS NOT NULL ) THEN
806 IF ( p_batch_id IS NULL ) THEN
807 FND_MESSAGE.set_name ('AR', 'AR_ARGUEMENTS_FAIL' );
808 APP_EXCEPTION.raise_exception;
809 END IF;
810 END IF;
811
812 ARP_PROCESS_BOE.Val_Create_Auto_Batch_Submit(p_batch_id);
813
814 --
815 -- Call the concurrent program
816 --
817 -- Shiv Ragunat , 9/11/96 ,Modified the Date parameters to convert it
818 -- to DD-MON-YYYY format, so that the call succeeds for any
819 -- NLS date format.
820 --
821 /* Fix for Bug 5699734 - Wrong org being set because of FND_PROFILE.value('ORG_ID') */
822 /* Bug 5699734 - Changed logic for getting the org_id - Getting from ar_system_parameters would be more reliable */
823 --l_org_id := TO_NUMBER(FND_PROFILE.value('ORG_ID'));
824 IF PG_DEBUG in ('Y', 'C') THEN
825 arp_standard.debug('Selecting org_id from ar_system_parameters and p_batch_id : '||p_batch_id);
826 END IF;
827 SELECT org_id
828 INTO l_org_id
829 FROM ar_system_parameters;
830 --l_org_id := nvl(mo_global.get_current_org_id, mo_utils.get_default_org_id);
831 /* Fix for Bug 5699734 ends */
832
833 fnd_request.set_org_id(l_org_id);
834
835 /* Additional Debug Messages as a part of Bug 5699734 */
836 IF PG_DEBUG in ('Y', 'C') THEN
837 arp_standard.debug('create_auto_batch_conc_req: Submitting Conc Req Create Automatic Receipts with org :'||l_org_id);
838 END IF;
839 /* Additional Debug Messages ends */
840
841 l_request_id := FND_REQUEST.submit_request( 'AR',
842 'AR_AUTORECAPI', -- PAYMENT_UPTAKE ARZCAR_RECEIPT
843 NULL,
844 -- 'Create Automatic Receipts',
845 TO_CHAR(SYSDATE,'DD-MON-YYYY'),
846 FALSE, 'RECEIPT',
847 NULL, -- Batch Date
848 NULL, -- Batch GL Date
849 p_create_flag,
850 p_approve_flag, p_format_flag, p_batch_id,
851 l_debug,
852 NULL, -- Batch Currency
853 NULL, -- Exchange Date
854 NULL, -- Exchange Rate
855 NULL, -- Exchange Rate Type
856 NULL, -- Remit Method Code
857 NULL, -- Receipt Class
858 NULL, -- Payment Method
859 NULL, -- Media Reference
860 NULL, -- Remit Bank Branch
861 NULL, -- Remit Bank Account
862 NULL, -- Bank Deposit Number
863 NULL, -- Batch Comments
864 fnd_date.date_to_canonical(p_trx_date_low),
865 fnd_date.date_to_canonical(p_trx_date_high),
866 fnd_date.date_to_canonical(p_due_date_low),
867 fnd_date.date_to_canonical(p_due_date_high),
868 p_trx_number_low, p_trx_number_high,
869 p_document_number_low, p_document_number_high,
870 p_customer_number_low, p_customer_number_high,
871 p_customer_name_low, p_customer_name_high,
872 p_customer_id,
873 p_location_low, p_location_high,
874 p_site_use_id,
875 NULL, NULL,
876 p_billing_number_low, p_billing_number_high,
877 p_bank_account_low, p_bank_account_high
878 );
879 --
880 IF PG_DEBUG in ('Y', 'C') THEN
881 arp_standard.debug('create_auto_batch_conc_req: ' || 'Out Request ID = '||to_char( l_request_id ) );
882 END IF;
883 p_request_id := l_request_id;
884 --
885 -- Update batch record to set batch applied status
886 --
887 arp_cr_batches_pkg.set_to_dummy( l_bat_rec );
888 l_bat_rec.batch_id := p_batch_id;
889 l_bat_rec.operation_request_id := l_request_id;
890 --
891 IF ( p_create_flag = 'Y' ) THEN
892 l_bat_rec.batch_applied_status := 'STARTED_CREATION';
893 ELSIF ( p_approve_flag = 'Y' ) THEN
894 l_bat_rec.batch_applied_status := 'STARTED_APPROVAL';
895 ELSE
896 SELECT bat.batch_applied_status,
897 app.program_name
898 INTO l_batch_app_status,
899 l_program_name
900 FROM ar_batches bat,
901 ar_receipt_methods rm,
902 ap_payment_programs app
903 WHERE bat.batch_id = p_batch_id
904 AND bat.receipt_method_id = rm.receipt_method_id
905 AND rm.auto_print_program_id = app.program_id;
906
907 IF ( l_program_name = 'ARSEPADNT') THEN
908 IF l_batch_app_status <> 'COMPLETED_FORMAT' THEN
909 UPDATE ar_cash_receipts
910 SET seq_type_last = 'Y'
911 WHERE cash_receipt_id IN (
912 SELECT crh.cash_receipt_id
913 FROM ar_cash_receipt_history crh,
914 ar_receivable_applications ra,
915 ra_customer_trx ct,
916 iby_fndcpt_tx_extensions ext
917 WHERE crh.batch_id = p_batch_id
918 AND crh.current_record_flag = 'Y'
919 AND crh.status = 'CONFIRMED'
920 AND ra.cash_receipt_id = crh.cash_receipt_id
921 AND ra.application_type = 'CASH'
922 AND ra.status = 'APP'
923 AND ct.customer_trx_id = ra.applied_customer_trx_id
924 AND ext.trxn_extension_id = ct.payment_trxn_extension_id
925 AND NVL(ext.seq_type_last, 'N') = 'Y');
926 END IF;
927 END IF;
928 l_bat_rec.batch_applied_status := 'STARTED_FORMAT';
929 END IF;
930 --
931 arp_cr_batches_pkg.update_p( l_bat_rec, l_bat_rec.batch_id );
932 p_batch_applied_status := l_bat_rec.batch_applied_status;
933 --
934 IF PG_DEBUG in ('Y', 'C') THEN
935 arp_standard.debug( 'arp_process_boe.create_auto_batch_conc_req()-');
936 END IF;
937 --
938 EXCEPTION
939 WHEN OTHERS THEN
940 IF PG_DEBUG in ('Y', 'C') THEN
941 arp_standard.debug('create_auto_batch_conc_req: ' ||
942 'EXCEPTION: arp_process_boe.create_auto_batch_conc_req' );
943 END IF;
944 RAISE;
945 --
946 END create_auto_batch_conc_req;
947 --
948 /*===========================================================================+
949 | PROCEDURE |
950 | add_or_rm_txn_from_auto_batch |
951 | |
952 | DESCRIPTION |
953 | Procedure called during the create/approve of automatic receipts batch |
954 | to remove or add an invoice to be automatically paid. |
955 | The user can change certain invoice information such as paying custome|
956 | and customer bank account. |
957 | |
958 | SCOPE - PUBLIC |
959 | |
960 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
961 | arp_ct_pkg.update_p - Updates paying_customer_id and |
962 | p_customer_bank_account_id in RA_CUSTOMER_TRX |
963 | arp_ps_pkg.update_p - Updates selected_for_receipt_batch_id in PS tab.|
964 | arp_util.debug |
965 | |
966 | ARGUMENTS : IN: |
967 | p_ct_id - Customer Trx ID |
968 | p_ps_id - Payment Schedule ID |
969 | p_selected_for_rec_batch_id - This field indicates if an |
970 | invoice has been selected to be paid by a receipt e |
971 | belonging to an automatic batch. Note: This field is |
972 | used to update the PS table and the RA_CUSTOMER_TRX |
973 | table. |
974 | p_paying_customer_id - Paying customer of the invoice. |
975 | p_customer_bank_account_id - Custome bank account ID of the|
976 | invoice. |
977 | p_module_version - Version of the module that called |
978 | this procedure |
979 | OUT: |
980 | p_batch_applied_status - currenct batch aplied status if |
981 | conc. req was started. |
982 | p_request_id - Request id of conc. request. |
983 | |
984 | RETURNS : NONE |
985 | |
986 | NOTES |
987 | |
988 | MODIFICATION HISTORY - Created by Ganesh Vaidee - 10/05/95 |
989 | 11/20/1995 - The procedure now calls the conc. request to start |
990 | approval or formatting of a batch if need be |
991 | depending on the p_call_conc_req flag |
992 | |
993 +===========================================================================*/
994 PROCEDURE add_or_rm_txn_from_auto_batch(
995 p_ct_id IN ra_customer_trx.customer_trx_id%TYPE,
996 p_ps_id IN ar_payment_schedules.payment_schedule_id%TYPE,
997 p_selected_for_rec_batch_id IN
998 ar_payment_schedules.selected_for_receipt_batch_id%TYPE,
999 p_paying_customer_id IN ra_customer_trx.paying_customer_id%TYPE,
1000 p_customer_bank_account_id IN
1001 ra_customer_trx.customer_bank_account_id%TYPE,
1002 p_module_name IN VARCHAR2,
1003 p_module_version IN VARCHAR2 ) IS
1004 l_ct_rec ra_customer_trx%ROWTYPE;
1005 l_ps_rec ar_payment_schedules%ROWTYPE;
1006 --
1007 l_approve_flag VARCHAR2(1) DEFAULT 'N';
1008 l_format_flag VARCHAR2(1) DEFAULT 'N';
1009 --
1010 --
1011 BEGIN
1012 IF PG_DEBUG in ('Y', 'C') THEN
1013 arp_standard.debug( 'arp_process_boe.add_or_rm_txn_from_auto_batch()+');
1014 END IF;
1015 --
1016 IF PG_DEBUG in ('Y', 'C') THEN
1017 arp_standard.debug('add_or_rm_txn_from_auto_batch: ' || 'ct_id = '||to_char( p_ct_id ) );
1018 arp_standard.debug('add_or_rm_txn_from_auto_batch: ' || 'ps_id = '||to_char( p_ps_id ) );
1019 arp_standard.debug('add_or_rm_txn_from_auto_batch: ' || 'p_selected_for_rec_batch_id = '||
1020 to_char( p_selected_for_rec_batch_id ) );
1021 arp_standard.debug('add_or_rm_txn_from_auto_batch: ' || 'customer_bank_account_id = '||
1022 to_char( p_customer_bank_account_id ) );
1023 arp_standard.debug('add_or_rm_txn_from_auto_batch: ' || 'paying_customer_id = '||
1024 to_char( p_paying_customer_id ) );
1025 END IF;
1026 --
1027 -- Validate input arguments
1028 --
1029 IF ( p_module_name IS NOT NULL and p_module_version IS NOT NULL ) THEN
1030 val_args_add_or_rm_txn_rec( p_ct_id, p_ps_id,
1031 p_paying_customer_id, p_customer_bank_account_id );
1032
1033 END IF;
1034 --
1035 -- Update Cash receipts table
1036 --
1037 arp_ct_pkg.set_to_dummy( l_ct_rec );
1038 l_ct_rec.customer_trx_id := p_ct_id;
1039 l_ct_rec.paying_customer_id := p_paying_customer_id;
1040 l_ct_rec.customer_bank_account_id := p_customer_bank_account_id;
1041 arp_ct_pkg.update_p( l_ct_rec, l_ct_rec.customer_trx_id );
1042 --
1043 -- Update PS table with due date
1044 --
1045 arp_ps_pkg.set_to_dummy( l_ps_rec );
1046 l_ps_rec.payment_schedule_id := p_ps_id;
1047 l_ps_rec.selected_for_receipt_batch_id := p_selected_for_rec_batch_id;
1048 arp_ps_pkg.update_p( l_ps_rec, l_ps_rec.payment_schedule_id );
1049 --
1050 IF PG_DEBUG in ('Y', 'C') THEN
1051 arp_standard.debug( 'arp_process_boe.add_or_rm_txn_from_auto_batch()-');
1052 END IF;
1053 --
1054 EXCEPTION
1055 WHEN OTHERS THEN
1056 IF PG_DEBUG in ('Y', 'C') THEN
1057 arp_standard.debug('add_or_rm_txn_from_auto_batch: ' ||
1058 'EXCEPTION: arp_process_boe.add_or_rm_txn_from_auto_batch');
1059 END IF;
1060 RAISE;
1061 --
1062 END add_or_rm_txn_from_auto_batch;
1063 /*===========================================================================+
1064 | PROCEDURE |
1065 | val_args_add_or_rm_txn_rec |
1066 | |
1067 | DESCRIPTION |
1068 | Validate arguments passed to dd_or_rm_txn_from_auto_batch procedure |
1069 | |
1070 | SCOPE - PRIVATE |
1071 | |
1072 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
1073 | |
1074 | ARGUMENTS : IN: |
1075 | p_ct_id - Customer Trx ID |
1076 | p_ps_id - Payment Schedule ID |
1077 | p_paying_customer_id - Paying customer of the invoice. |
1078 | p_customer_bank_account_id - Custome bank account ID of the|
1079 | invoice. |
1080 | OUT: |
1081 | |
1082 | RETURNS : NONE |
1083 | |
1084 | NOTES |
1085 | |
1086 | MODIFICATION HISTORY - Created by Ganesh Vaidee - 10/05/95 |
1087 | 11/20/1995 - Added code to check batch id and which action if |
1088 | conc. req flag is set to 'Y' |
1089 | |
1090 +===========================================================================*/
1091 PROCEDURE val_args_add_or_rm_txn_rec(
1092 p_ct_id IN ra_customer_trx.customer_trx_id%TYPE,
1093 p_ps_id IN ar_payment_schedules.payment_schedule_id%TYPE,
1094 p_paying_customer_id IN ra_customer_trx.paying_customer_id%TYPE,
1095 p_customer_bank_account_id IN
1096 ra_customer_trx.customer_bank_account_id%TYPE ) IS
1097 BEGIN
1098 IF PG_DEBUG in ('Y', 'C') THEN
1099 arp_standard.debug( 'arp_process_boe.val_args_add_or_rm_txn_rec() +');
1100 END IF;
1101 --
1102 IF ( p_ct_id is NULL OR p_ps_id is NULL OR
1103 p_paying_customer_id is NULL) THEN
1104 FND_MESSAGE.set_name ('AR', 'AR_ARGUEMENTS_FAIL' );
1105 APP_EXCEPTION.raise_exception;
1106 END IF;
1107 --
1108 IF PG_DEBUG in ('Y', 'C') THEN
1109 arp_standard.debug( 'arp_process_boe.val_args_add_or_rm_txn_rec() -');
1110 END IF;
1111 EXCEPTION
1112 WHEN OTHERS THEN
1113 IF PG_DEBUG in ('Y', 'C') THEN
1114 arp_standard.debug('val_args_add_or_rm_txn_rec: ' ||
1115 'EXCEPTION: arp_process_boe.val_args_add_or_rm_txn_rec' );
1116 END IF;
1117 RAISE;
1118 END val_args_add_or_rm_txn_rec;
1119
1120 /*===========================================================================+
1121 | PROCEDURE |
1122 | Val_Create_Auto_Batch_Submit |
1123 | |
1124 | DESCRIPTION |
1125 | Checks whether Automatic Receipt Creation Batch can be submitted |
1126 | |
1127 | SCOPE - PRIVATE |
1128 | |
1129 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
1130 | |
1131 | ARGUMENTS : IN: p_batch_id |
1132 | OUT: |
1133 | |
1134 | RETURNS : NONE |
1135 | |
1136 | NOTES |
1137 | |
1138 | MODIFICATION HISTORY - Created by J Rautiainen BugFix 951138 - 15-SEP-1999|
1139 | |
1140 +===========================================================================*/
1141 PROCEDURE Val_Create_Auto_Batch_Submit(p_batch_id ar_batches.batch_id%TYPE) IS
1142
1143 /* 15-SEP-1999 J Rautiainen BugFix 951138 Start
1144 * Need the payment method name and batch date in order to check
1145 * whether an active document sequence assignment exists for the
1146 * payment method. Automatic Receipt Creation process cannot submitted
1147 * if an active document sequence assignment does not exist */
1148
1149 CURSOR batch_cursor IS
1150 select rm.name rm_name, b.batch_date batch_date
1151 from ar_receipt_methods rm,
1152 ar_batches b
1153 where b.batch_id = p_batch_id
1154 AND rm.receipt_method_id = b.receipt_method_id;
1155
1156 l_batch_rec batch_cursor%ROWTYPE;
1157 l_doc_seq_result NUMBER :=0;
1158 l_Doc_Sequence_ID number;
1159 l_Sequence_Type char;
1160 l_Sequence_Name varchar2(30);
1161 l_DB_Seq_Gen_Name varchar2(30);
1162 l_Sequence_Assignment_Id number;
1163 l_Product_Table_Name varchar2(30);
1164 l_Audit_Table_name varchar2(30);
1165 l_Message_Flag char;
1166
1167 BEGIN
1168
1169 /* Automatic Receipt Creation process cannot submitted
1170 * if an active document sequence assignment does not exist
1171 * or the profile option Sequential Numbering is set to 'Not Used' */
1172
1173 IF (fnd_profile.value('UNIQUE:SEQ_NUMBERS') in (NULL, 'N' )) THEN
1174
1175 /* Cannot submit Automatic Receipt Creation process because the profile option
1176 * Sequential Numbering is set to Not Used. Please set this option to Always Used
1177 * or Partially Used, then resubmit. */
1178
1179 FND_MESSAGE.set_name ('AR', 'AR_RW_AUTOBAT_SEQ_NOT_USED');
1180 APP_EXCEPTION.raise_exception;
1181
1182 ELSE
1183 /* Fetch the payment method code and batch date */
1184
1185 OPEN batch_cursor;
1186 FETCH batch_cursor INTO l_batch_rec;
1187
1188 IF batch_cursor%FOUND THEN
1189 /* If an payment method was found check that an active automatic document
1190 * sequence assignment exists for it */
1191
1192 l_doc_seq_result := FND_SEQNUM.GET_SEQ_INFO(
1193 222,
1194 l_batch_rec.rm_name,
1195 arp_global.set_of_books_id,
1196 'A',
1197 l_batch_rec.batch_date,
1198 l_Doc_Sequence_ID,
1199 l_Sequence_Type,
1200 l_Sequence_Name,
1201 l_DB_Seq_Gen_Name,
1202 l_Sequence_Assignment_Id,
1203 l_Product_Table_Name,
1204 l_Audit_Table_name,
1205 l_Message_Flag,
1206 'Y',
1207 'Y');
1208
1209 /* If 0 is returned an assignment exists, otherwise an error occured */
1210 IF l_doc_seq_result <> 0 THEN
1211
1212 /* Cannot submit Automatic Receipt Creation process because an active document
1213 * sequence assignment does not exist for this payment method. Please define a
1214 * document sequence assignment or enter a different payment method. */
1215 CLOSE batch_cursor;
1216 FND_MESSAGE.set_name ('AR', 'AR_RW_AUTOBAT_NO_PM_ASSIGNMENT');
1217 APP_EXCEPTION.raise_exception;
1218
1219 END IF;
1220 END IF;
1221
1222 CLOSE batch_cursor;
1223 END IF;
1224
1225 END Val_Create_Auto_Batch_Submit;
1226 --
1227 BEGIN
1228 l_debug := fnd_profile.value('AFLOG_ENABLED');
1229 IF (l_debug <> 'Y') THEN
1230 l_debug := 'N';
1231 END IF;
1232 END ARP_PROCESS_BOE;