[Home] [Help]
PACKAGE BODY: APPS.ARP_BF_BILL
Source
6 INDEX BY BINARY_INTEGER;
1 PACKAGE BODY arp_bf_bill AS
2 /* $Header: ARPBFBIB.pls 120.51.12020000.9 2012/07/24 09:22:18 ashlkuma ship $ */
3
4 -- define structure to store data that needs to be overridden
5 TYPE tab_trx_id IS TABLE OF ra_customer_trx.customer_trx_id%TYPE
7 TYPE tab_term_id IS TABLE OF ra_customer_trx.term_id%TYPE
8 INDEX BY BINARY_INTEGER;
9 TYPE tab_billing_date IS TABLE OF ra_customer_trx.billing_date%TYPE
10 INDEX BY BINARY_INTEGER;
11 TYPE tab_due_date IS TABLE OF ra_customer_trx.term_due_date%TYPE
12 INDEX BY BINARY_INTEGER;
13
14
15 l_tab_trx_id tab_trx_id;
16 l_tab_term_id tab_term_id;
17 l_tab_billing_date tab_billing_date;
18 l_tab_due_date tab_due_date;
19 l_tab_idx BINARY_INTEGER := 0;
20
21
22 PROCEDURE write_debug_and_log(p_message IN VARCHAR2) IS
23
24 BEGIN
25
26 IF FND_GLOBAL.CONC_REQUEST_ID is not null THEN
27
28 fnd_file.put_line(FND_FILE.LOG,p_message);
29
30 END IF;
31
32 arp_standard.debug(p_message);
33
34 EXCEPTION
35 WHEN others THEN
36 NULL;
37 END;
38
39
40 /*----------------------------------------------------------------------------*
41 | PROCEDURE |
42 | reprint |
43 | |
44 | DESCRIPTION |
45 | Update rows of consolidated billing invoice or rows associated with |
46 | specified concurrent request id to print status of 'PENDING' so report |
47 | ARXCBI will print them. |
48 | |
49 | SCOPE - PRIVATE |
50 | |
51 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
52 | |
53 | ARGUMENTS : IN: |
54 | P_consinv_id - consolidated billing invoice |
55 | P_request_id - concurrent request id |
56 | OUT: |
57 | None |
58 | |
59 | RETURNS : NONE |
60 | |
61 | NOTES |
62 | |
63 | MODIFICATION HISTORY |
64 | |
65 *----------------------------------------------------------------------------*/
66 PROCEDURE reprint (P_consinv_id IN NUMBER, P_request_id IN NUMBER) IS
67
68 BEGIN
69 UPDATE ar_cons_inv
70 SET print_status = 'PENDING',
71 last_update_date = arp_global.last_update_date,
72 last_updated_by = arp_global.last_updated_by,
73 last_update_login = arp_global.last_update_login
74 WHERE cons_inv_id = nvl(P_consinv_id, cons_inv_id)
75 AND concurrent_request_id = DECODE(P_consinv_id,
76 NULL, P_request_id,
77 concurrent_request_id);
78 EXCEPTION
79 WHEN OTHERS THEN
80 write_debug_and_log( ' Exception: reprint: ');
81 write_debug_and_log( ' P_consinv_id: '||P_consinv_id );
82 write_debug_and_log( ' P_request_id: '||P_request_id );
83 RAISE;
84 END;
85
86 /*----------------------------------------------------------------------------*
87 | PROCEDURE |
88 | accept |
89 | |
90 | DESCRIPTION |
91 | Updates rows for draft versions of consolidated billing invoices to |
92 | status of 'PRINTED', from a prior status of 'DRAFT' |
93 | |
94 | SCOPE - PRIVATE |
95 | |
96 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
97 | |
98 | ARGUMENTS : IN: |
99 | P_consinv_id - Consolidated Billing Invoice id |
100 | P_request_id - Concurrent Request Id associated with |
101 | rows that are to be accepted. |
102 | OUT: |
103 | None |
104 | |
105 | RETURNS : NONE |
106 | |
107 | NOTES |
108 | |
109 | MODIFICATION HISTORY |
110 | |
111 *----------------------------------------------------------------------------*/
112 PROCEDURE accept( P_cust_num_low IN VARCHAR2,
113 P_cust_num_high IN VARCHAR2,
114 P_bill_site_low IN NUMBER,
115 P_bill_site_high IN NUMBER,
116 P_bill_date_low IN DATE,
117 P_bill_date_high IN DATE,
118 P_consinv_num_low IN NUMBER,
119 P_consinv_num_high IN NUMBER,
120 P_request_id IN NUMBER) IS
121
122 BEGIN
123
124 write_debug_and_log ( ' Parameters into accept:' );
125 write_debug_and_log ( ' P_cust_num_low : ' || P_cust_num_low);
126 write_debug_and_log ( ' P_cust_num_high : ' || P_cust_num_high);
127 write_debug_and_log ( ' P_bill_site_low : ' || P_bill_site_low);
128 write_debug_and_log ( ' P_bill_site_high : ' || P_bill_site_high);
129 write_debug_and_log ( ' P_bill_date_low : ' || P_bill_date_low);
130 write_debug_and_log ( ' P_bill_date_high : ' || P_bill_date_high);
131 write_debug_and_log ( ' P_consinv_num_low : ' || P_consinv_num_low);
132 write_debug_and_log ( ' P_consinv_num_high: ' || P_consinv_num_high);
133 write_debug_and_log ( ' P_request_id : ' || P_request_id);
134
135 --Bug 10023214.
136 UPDATE ar_cons_inv
137 SET status = 'ACCEPTED',
138 last_update_date = arp_global.last_update_date,
139 last_updated_by = arp_global.last_updated_by,
140 last_update_login = arp_global.last_update_login
141 WHERE customer_id in (select cust_account_id
142 from hz_cust_accounts c
143 where c.account_number
144 between nvl(P_cust_num_low, c.account_number)
145 and nvl(P_cust_num_high, c.account_number))
146 AND site_use_id between nvl(P_bill_site_low, site_use_id) and
150 nvl(P_bill_date_high, nvl(billing_date,cut_off_date))
147 nvl(P_bill_site_high, site_use_id)
148 AND nvl(billing_date,cut_off_date) between
149 nvl(P_bill_date_low, nvl(billing_date,cut_off_date)) and -- Bug 8810634
151 AND cons_billing_number between nvl(P_consinv_num_low, cons_billing_number) and
152 nvl(P_consinv_num_high, cons_billing_number)
153 AND concurrent_request_id = nvl(P_request_id, concurrent_request_id)
154 AND status = 'DRAFT';
155
156 commit;
157
158 EXCEPTION
159 WHEN OTHERS THEN
160 write_debug_and_log ( ' EXCEPTION: accept:' );
161 write_debug_and_log ( ' P_cust_num_low : ' || P_cust_num_low);
162 write_debug_and_log ( ' P_cust_num_high : ' || P_cust_num_high);
163 write_debug_and_log ( ' P_bill_site_low : ' || P_bill_site_low);
164 write_debug_and_log ( ' P_bill_site_high : ' || P_bill_site_high);
165 write_debug_and_log ( ' P_bill_date_low : ' || P_bill_date_low);
166 write_debug_and_log ( ' P_bill_date_high : ' || P_bill_date_high);
167 write_debug_and_log ( ' P_consinv_num_low : ' || P_consinv_num_low);
168 write_debug_and_log ( ' P_consinv_num_high: ' || P_consinv_num_high);
169 write_debug_and_log ( ' P_request_id : ' || P_request_id);
170
171 RAISE;
172 END;
173
174 /*----------------------------------------------------------------------------*
175 | PROCEDURE |
176 | reject |
177 | |
178 | DESCRIPTION |
179 | Will delete the consolidated billing invoice or all consolidated |
180 | billing invoices associated with the specified concurrent request id. |
181 | All of the AR tables that have been updated with these consolidated |
182 | billing invoice id's will be updated so that these deleted id's are |
183 | no longer referenced. |
184 | |
185 | SCOPE - PRIVATE |
186 | |
187 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
188 | |
189 | ARGUMENTS : IN: |
190 | P_consinv_id - Consolidated Billing Invoice id |
191 | P_request_id - Concurrent Request Id |
192 | OUT: |
193 | None |
194 | |
195 | RETURNS : NONE |
196 | |
197 | NOTES |
198 | |
199 | MODIFICATION HISTORY |
200 | |
201 | C M Clyde 28 Aug 97 Modified to include transaction types of |
202 | 'XSITE XCURR RECAPP', 'XSITE XCURR RECREV', |
203 | 'XCURR RECAPP', 'XCURR RECREV'. |
204 | |
205 *----------------------------------------------------------------------------*/
206 PROCEDURE reject( P_cust_num_low IN VARCHAR2,
207 P_cust_num_high IN VARCHAR2,
208 P_bill_site_low IN NUMBER,
209 P_bill_site_high IN NUMBER,
210 P_bill_date_low IN DATE,
211 P_bill_date_high IN DATE,
212 P_consinv_num_low IN NUMBER,
213 P_consinv_num_high IN NUMBER,
214 P_request_id IN NUMBER) IS
215
216 BEGIN
217
218 write_debug_and_log ( ' Parameters into Reject:');
219 write_debug_and_log ( ' P_cust_num_low : ' || P_cust_num_low);
220 write_debug_and_log ( ' P_cust_num_high : ' || P_cust_num_high);
221 write_debug_and_log ( ' P_bill_site_low : ' || P_bill_site_low);
222 write_debug_and_log ( ' P_bill_site_high : ' || P_bill_site_high);
223 write_debug_and_log ( ' P_bill_date_low : ' || P_bill_date_low);
224 write_debug_and_log ( ' P_bill_date_high : ' || P_bill_date_high);
225 write_debug_and_log ( ' P_consinv_num_low : ' || P_consinv_num_low);
226 write_debug_and_log ( ' P_consinv_num_high: ' || P_consinv_num_high);
227 write_debug_and_log ( ' P_request_id : ' || P_request_id);
228
229 --Bug 10023214
230 UPDATE ar_cons_inv
231 SET status = 'PRE_REJECTED',
232 print_status = 'PRINTED',
233 last_update_date = arp_global.last_update_date,
234 last_updated_by = arp_global.last_updated_by,
235 last_update_login = arp_global.last_update_login
236 WHERE customer_id in (select cust_account_id
240 and nvl(P_cust_num_high, c.account_number))
237 from hz_cust_accounts c
238 where c.account_number
239 between nvl(P_cust_num_low, c.account_number)
241 AND site_use_id between nvl(P_bill_site_low, site_use_id) and
242 nvl(P_bill_site_high, site_use_id)
243 AND nvl(billing_date,cut_off_date) between
244 nvl(P_bill_date_low, nvl(billing_date,cut_off_date)) and -- Bug 8810634
245 nvl(P_bill_date_high, nvl(billing_date,cut_off_date))
246 AND cons_billing_number between nvl(P_consinv_num_low, cons_billing_number) and
247 nvl(P_consinv_num_high, cons_billing_number)
248 AND concurrent_request_id = nvl(P_request_id, concurrent_request_id)
249 AND status = 'DRAFT';
250
251 UPDATE ra_customer_trx
252 SET printing_original_date =
253 DECODE(printing_count,
254 1, NULL,
255 printing_original_date),
256 printing_last_printed =
257 DECODE(printing_count,
258 1, NULL,
259 printing_last_printed),
260 printing_count = DECODE(printing_count,
261 1, NULL,
262 printing_count - 1)
263 WHERE customer_trx_id IN
264 (SELECT PS.customer_trx_id
265 FROM ar_payment_schedules PS,
266 ar_cons_inv_trx IT,
267 ar_cons_inv CI
268 WHERE IT.transaction_type IN ('INVOICE','CREDIT_MEMO','DEBIT_MEMO', 'DEPOSIT', 'CHARGEBACK')
269 AND CI.cons_inv_id = IT.cons_inv_id
270 AND CI.cons_inv_id in (select cons_inv_id from ar_cons_inv i
271 where i.status = 'PRE_REJECTED')
272 AND PS.payment_schedule_id = IT.adj_ps_id);
273
274 UPDATE ar_payment_schedules
275 SET cons_inv_id = NULL
276 WHERE payment_schedule_id IN
277 (SELECT IT.adj_ps_id
278 FROM ar_cons_inv CI,
279 ar_cons_inv_trx IT
280 WHERE IT.transaction_type IN ('INVOICE','CREDIT_MEMO', 'DEBIT_MEMO', 'DEPOSIT', 'CHARGEBACK',
281 'RECEIPT')
282 AND CI.cons_inv_id = IT.cons_inv_id
283 AND CI.cons_inv_id in (select cons_inv_id from ar_cons_inv i
284 where i.status = 'PRE_REJECTED'));
285
286 UPDATE ar_payment_schedules
287 SET cons_inv_id_rev = NULL
288 WHERE payment_schedule_id IN
289 (SELECT IT.adj_ps_id
290 FROM ar_cons_inv CI,
291 ar_cons_inv_trx IT
292 WHERE IT.transaction_type = 'RECEIPT REV'
293 AND CI.cons_inv_id = IT.cons_inv_id
294 AND CI.cons_inv_id in (select cons_inv_id from ar_cons_inv i
295 where i.status = 'PRE_REJECTED'));
296
297 UPDATE ar_receivable_applications
298 SET cons_inv_id = NULL
299 WHERE receivable_application_id IN
300 (SELECT IT.adj_ps_id
301 FROM ar_cons_inv CI,
302 ar_cons_inv_trx IT
303 WHERE IT.transaction_type IN ('XSITE RECREV', 'XSITE_CMREV',
304 'XCURR RECREV', 'XSITE XCURR RECREV',
305 'EXCLUDE RECREV', 'EXCLUDE_CMREV','RECEIPT ADJUST') /*Bug 9189970 */
306 AND CI.cons_inv_id = IT.cons_inv_id
307 AND CI.cons_inv_id in (select cons_inv_id from ar_cons_inv i
308 where i.status = 'PRE_REJECTED'));
309
310 UPDATE ar_receivable_applications
311 SET cons_inv_id_to = NULL
312 WHERE receivable_application_id IN
313 (SELECT IT.adj_ps_id
314 FROM ar_cons_inv CI,
315 ar_cons_inv_trx IT
316 WHERE IT.transaction_type IN ('XSITE RECAPP','XSITE_CMAPP',
317 'XCURR RECAPP', 'XSITE XCURR RECAPP' ,
318 'EXCLUDE RECAPP', 'EXCLUDE_CMAPP',
319 'DELAY_CMAPP')
320 AND CI.cons_inv_id = IT.cons_inv_id
321 AND CI.cons_inv_id in (select cons_inv_id from ar_cons_inv i
322 where i.status = 'PRE_REJECTED'));
323
324
325 UPDATE ar_adjustments
326 SET cons_inv_id = NULL
327 WHERE adjustment_id IN
328 (SELECT IT.adj_ps_id
329 FROM ar_cons_inv CI,
330 ar_cons_inv_trx IT
331 WHERE IT.transaction_type = 'ADJUSTMENT'
332 AND CI.cons_inv_id = IT.cons_inv_id
333 AND CI.cons_inv_id in (select cons_inv_id from ar_cons_inv i
334 where i.status = 'PRE_REJECTED'));
335
336
337 DELETE FROM ar_cons_inv_trx_lines
338 WHERE cons_inv_id IN
339 (SELECT CI.cons_inv_id
340 FROM ar_cons_inv CI
341 WHERE CI.cons_inv_id in (select cons_inv_id from ar_cons_inv i
342 where i.status = 'PRE_REJECTED'));
343
344 DELETE FROM ar_cons_inv_trx
345 WHERE cons_inv_id IN
346 (SELECT CI.cons_inv_id
347 FROM ar_cons_inv CI
348 WHERE CI.cons_inv_id in (select cons_inv_id from ar_cons_inv i
349 where i.status = 'PRE_REJECTED'));
350
351 UPDATE ar_cons_inv CI
352 SET status = 'REJECTED'
353 WHERE CI.cons_inv_id in (select cons_inv_id from ar_cons_inv i
354 where i.status = 'PRE_REJECTED');
355
356 commit;
357
358 EXCEPTION
359 WHEN OTHERS THEN
360 write_debug_and_log ( ' Exception: reject: ');
361 write_debug_and_log ( ' P_cust_num_low : ' || P_cust_num_low);
362 write_debug_and_log ( ' P_cust_num_high : ' || P_cust_num_high);
363 write_debug_and_log ( ' P_bill_site_low : ' || P_bill_site_low);
364 write_debug_and_log ( ' P_bill_site_high : ' || P_bill_site_high);
365 write_debug_and_log ( ' P_bill_date_low : ' || P_bill_date_low);
366 write_debug_and_log ( ' P_bill_date_high : ' || P_bill_date_high);
367 write_debug_and_log ( ' P_consinv_num_low : ' || P_consinv_num_low);
368 write_debug_and_log ( ' P_consinv_num_high: ' || P_consinv_num_high);
369 write_debug_and_log ( ' P_request_id : ' || P_request_id);
370
371 RAISE;
372 END;
373
374 /*----------------------------------------------------------------------------*
375 | PROCEDURE |
376 | process_override |
377 | |
378 | DESCRIPTION |
379 | This is a local procedure called from generate, which will process |
380 | override requests |
381 | |
382 | SCOPE - PRIVATE |
383 | |
384 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
385 | |
386 | ARGUMENTS : IN: |
387 | P_trx_id |
388 | P_class |
389 | P_init_trx_id |
390 | P_prev_trx_id |
391 | : OUT: |
392 | P_comments |
393 | P_new_term |
394 | P_new_bill |
395 | |
396 | RETURNS : NONE |
397 | |
398 | NOTES |
399 | |
400 | MODIFICATION HISTORY |
401 | |
402 | 22-DEC-05 VCRISOST Created
403 *----------------------------------------------------------------------------*/
404 PROCEDURE process_override
405 (P_field IN VARCHAR2,
406 P_trx_id IN NUMBER,
407 P_class IN VARCHAR2,
408 P_init_trx_id IN NUMBER,
409 P_prev_trx_id IN NUMBER,
410 P_trx_bill_date IN DATE,
411 P_trx_term_id IN NUMBER,
412 P_bill_date IN DATE,
413 P_due_date IN DATE,
414 P_term_id IN NUMBER,
415 P_comments IN OUT NOCOPY VARCHAR2,
416 P_bypass_trx IN OUT NOCOPY BOOLEAN) IS
417
418 activity_flag VARCHAR2(1);
419 proc_field VARCHAR2(12);
420 new_term_id NUMBER;
421 new_bill_date DATE;
422 new_due_date DATE;
423
424 BEGIN
425
426 -- initialize new values to current values, in case no override is done
430 new_due_date := P_due_date;
427 P_bypass_trx := FALSE;
428 new_term_id := P_trx_term_id;
429 new_bill_date := P_trx_bill_date;
431 proc_field := P_field;
432
433 activity_flag := arpt_sql_func_util.get_activity_flag(
434 P_trx_id,
435 'Y',
436 'Y',
437 P_class,
438 P_init_trx_id,
439 P_prev_trx_id);
440
441 IF activity_flag = 'N' THEN
442 l_tab_idx := l_tab_idx + 1;
443 l_tab_trx_id(l_tab_idx) := P_trx_id;
444
445 IF proc_field = 'TERM_ID' THEN
446 write_debug_and_log('...............OVERRIDE TERM ID');
447 P_comments := P_comments || ' OVERRIDE TERM ID : FROM ' || to_char(P_trx_term_id) ||
448 ' TO ' || to_char(P_term_id) || '. ';
449 new_term_id := P_term_id;
450
451 -- after changing term data, you need to process billing_date as well
452 proc_field := 'BILLING_DATE';
453 END IF;
454
455 IF proc_field = 'BILLING_DATE' THEN
456
457 IF nvl(P_trx_bill_date, P_bill_date - 1) < P_bill_date THEN
458 write_debug_and_log('...............OVERRIDE BILLING DATE');
459 P_comments := P_comments || ' OVERRIDE BILLING DATE : FROM ' || to_char(P_trx_bill_date) ||
460 ' TO ' || to_char(P_bill_date) || '. ';
461 new_bill_date := P_bill_date;
462
463 -- need to recalculate due_date
464 new_due_date := ar_bfb_utils_pvt.get_due_date(new_bill_date, new_term_id);
465
466 END IF;
467
468 END IF;
469
470 l_tab_term_id(l_tab_idx) := new_term_id;
471 l_tab_billing_date(l_tab_idx) := new_bill_date;
472 l_tab_due_date(l_tab_idx) := new_due_date;
473
474 -- Following update statements are moved from generate procedure to here
475 -- for the bug 6488683
476 -- update the RA_CUSTOMER_TRX with the new term id, billing date and due date
477 UPDATE RA_CUSTOMER_TRX
478 SET term_id = new_term_id,
479 billing_date = new_bill_date,
480 term_due_date = new_due_date
481 WHERE customer_trx_id = P_trx_id;
482
483 -- update AR_PAYMENT_SCHEDULES table with the new values
484 arp_process_header.post_commit( 'ARPBFBIB',
485 120.0,
486 P_trx_id, -- customer_trx_id
487 NULL, -- cm trx_id
488 'Y', -- complete_flag
489 NULL, -- INV open_rec flag
490 NULL, -- CM open_rec_flag
491 NULL, -- creation_sign,
492 NULL, -- allow_overapp_flag,
493 NULL, -- natural_app_only_flag,
494 NULL -- cash_receipt_id
495 );
496
497 -- update AR_PAYMENT_SCHEDULES with the due date.
498 UPDATE AR_PAYMENT_SCHEDULES
499 SET due_date = new_due_date
500 WHERE customer_trx_id = P_trx_id;
501
502 ELSE
503 -- activity exists, cannot override data in trx table, but this trx still
504 -- needs to be pulled into this BFB
505
506 IF proc_field = 'BILLING_DATE' THEN
507 write_debug_and_log('...............CANNOT OVERRIDE BILLING DATE');
508 P_comments := P_comments || ' CANNOT OVERRIDE BILLING DATE : ' || to_char(P_trx_bill_date) ||
509 ' BUT WILL PROCESS AS ' || to_char(P_bill_date) || '. ';
510
511 ELSIF proc_field = 'TERM_ID' THEN
512 write_debug_and_log('...............CANNOT OVERRIDE TERM_ID');
513 P_comments := P_comments || ' CANNOT OVERRIDE TERM ID : ' || to_char(P_trx_term_id) ||
514 ' BUT WILL PROCESS AS ' || to_char(P_term_id) || '. ';
515 END IF;
516 END IF;
517 END;
518
519 /*----------------------------------------------------------------------------*
520 | PROCEDURE |
521 | generate |
522 | |
523 | DESCRIPTION |
524 | Will create new Consolidated Billing Invoices for the specified user |
525 | criteria. It can either be in 'DRAFT' or 'PRINT'. |
526 | |
527 | SCOPE - PRIVATE |
528 | |
529 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
530 | |
531 | ARGUMENTS : IN: |
532 | P_print_option - 'DRAFT' or 'PRINT' |
533 | P_print_output - 'Y' or 'N' |
534 | P_billing_cycle_id - Billing Cycle Id |
535 | P_billing_date - Billing date required for EXTERNAL |
536 | P_currency - Currency Code |
537 | P_cust_num_low - Customer number low |
538 | P_cust_num_high - Customer number high |
539 | P_bill_site_low - Bill-to Site low |
540 | P_bill_site_high - Bill-to Site high |
541 | P_term_id - Payment Terms id |
542 | : OUT: |
543 | None |
544 | |
545 | RETURNS : NONE |
546 | |
547 | NOTES |
548 | |
549 | MODIFICATION HISTORY |
550 | |
551 *----------------------------------------------------------------------------*/
552 PROCEDURE generate (P_print_option IN VARCHAR2,
553 P_print_output IN VARCHAR2,
554 P_billing_cycle_id IN NUMBER,
555 P_billing_date IN DATE,
556 P_currency IN VARCHAR2,
557 P_cust_name_low IN VARCHAR2,
558 P_cust_name_high IN VARCHAR2,
559 P_cust_num_low IN VARCHAR2,
563 P_term_id IN NUMBER,
560 P_cust_num_high IN VARCHAR2,
561 P_bill_site_low IN NUMBER,
562 P_bill_site_high IN NUMBER,
564 /* Bug 5203710 do not pass p_detail_option */
565 P_detail_option IN VARCHAR2 DEFAULT NULL,
566
567 P_print_status IN VARCHAR2,
568 p_future_date_bill_flag IN VARCHAR2) IS --Bug 12739341, add a flag to check whether it can generate future date bill
569
570 l_beginning_balance NUMBER;
571 l_consinv_id NUMBER;
572 l_consinv_lineno NUMBER(15);
573 l_cons_billno VARCHAR2(30);
574 l_new_billed NUMBER;
575 l_period_trx NUMBER;
576 l_period_receipts NUMBER;
577 l_period_adj NUMBER;
578 l_period_finchrg NUMBER;
579 l_period_credits NUMBER;
580 l_period_tax NUMBER;
581 l_due_date DATE;
582 l_last_bill_date DATE;
583 l_request_id NUMBER;
584 l_new_schedule_id NUMBER; -- bug 6488683
585 l_check_override BINARY_INTEGER := 0; -- bug 6488683
586 l_cm_flag NUMBER; -- bug 9392028
587
588 CURSOR val_param1 (P_cust_num_low VARCHAR2, P_cust_num_high VARCHAR2,
589 P_bill_site_low NUMBER, P_bill_site_high NUMBER) IS
590 SELECT acct.cust_account_id customer_id,
591 nvl(cp1.cons_bill_level, cp2.cons_bill_level) site_bill_level,
592 cp2.cons_bill_level acct_bill_level,
593 nvl(cp1.standard_terms, cp2.standard_terms) site_term,
594 cp2.standard_terms acct_term
595 FROM hz_cust_accounts acct,
596 hz_cust_acct_sites acct_site,
597 hz_cust_site_uses site_uses,
598 hz_customer_profiles cp1,
599 hz_customer_profiles cp2
600 WHERE acct.account_number between P_cust_num_low and P_cust_num_high
601 AND acct_site.cust_account_id = acct.cust_account_id
602 AND acct_site.cust_acct_site_id = site_uses.cust_acct_site_id
603 AND site_uses.site_use_id between P_bill_site_low and P_bill_site_high
604 AND cp1.cust_account_id = acct.cust_account_id
605 AND cp1.site_use_id(+) = site_uses.site_use_id
606 AND cp2.cust_account_id = acct.cust_account_id
607 AND cp2.site_use_id IS NULL;
608
609
610 -- get the BFB payment trms
611 CURSOR c_terms (C_billing_cycle_id NUMBER, C_term_id NUMBER) IS
612 SELECT T.term_id term_id,
613 TL.due_days due_day,
614 TL.due_day_of_month due_dom,
615 TL.due_months_forward due_mf
616 FROM ra_terms T,
617 ra_terms_lines TL
618 WHERE TL.term_id = T.term_id
619 AND T.billing_cycle_id = C_billing_cycle_id
620 AND T.term_id = nvl(C_term_id, T.term_id)
621 order by 1;
622
623 /* this cursor will pick up all transactions for given site/currency
627 * a) transactions that completely comply with the BFB term and billing date
624 * that are not yet stamped with a cons_inv_id and have billing_date <=
625 * billing date in process, this will include :
626 *
628 * - no additional processing required
629 * b) transactions that have diff term/bill date
630 * - may require override
631 * c) transactions that are CM
632 *
633 */
634
635 CURSOR C_inv_trx (C_site_use_id NUMBER, C_billing_date DATE, C_use_currency VARCHAR2) IS
636 SELECT CT.customer_trx_id trx_id,
637 CT.trx_date trx_date,
638 CT.trx_number trx_number,
639 PS.class class,
640 PS.payment_schedule_id schedule_id,
641 PS.amount_due_original amount_due,
642 PS.tax_original tax,
643 PS.invoice_currency_code currency,
644 CT.term_id term_id,
645 CT.billing_date billing_date,
646 CT.initial_customer_trx_id init_trx_id,
647 CT.previous_customer_trx_id prev_trx_id,
648 CT.interface_header_attribute1 trx_desc,
649 CT.ship_to_site_use_id ship_id,
650 CT.term_due_date due_date
651 FROM ra_customer_trx CT,
652 ar_payment_schedules PS
653 WHERE PS.customer_site_use_id = C_site_use_id
654 AND PS.cons_inv_id IS NULL
655 AND PS.invoice_currency_code = nvl(C_use_currency, PS.invoice_currency_code)
656 AND CT.customer_trx_id = PS.customer_trx_id
657 AND CT.printing_option = 'PRI'
658 AND PS.class IN ('INV', 'DM', 'DEP', 'CB','CM')
659 AND nvl(PS.exclude_from_cons_bill_flag, 'N') <> 'Y'
660 AND nvl(CT.billing_date, CT.trx_date) <= C_billing_date
661 ORDER BY 10, 1;
662
663 /* This cursor is used to fetch the recently created payment scheduled id
664 * for a given customer_trx_id. This is used to get the newly created
665 * payment_schedule_id for the transactions for which the term_id is over ridden.
666 * bug 6488683
667 */
668 CURSOR ps_cur( C_trx_id number) IS
669 SELECT payment_schedule_id
670 FROM ar_payment_schedules
671 WHERE customer_trx_id = C_trx_id
672 ORDER BY creation_date DESC;
673
674 TYPE c_sites_type IS REF CURSOR ;
675 C_sites C_sites_type;
676 C_site_select VARCHAR2(5000);
677 C_site_from VARCHAR2(5000);
678 C_site_where VARCHAR2(5000);
679 C_site_stmt VARCHAR2(5000);
680
681
682 /* Bug 5203710 get detail_option from customer setup */
683 TYPE L_sites_type IS RECORD
684 ( customer_id NUMBER,
685 site_id NUMBER,
686 bill_level VARCHAR2(1),
687 override_terms VARCHAR2(1),
688 cons_inv_type hz_customer_profiles.cons_inv_type%TYPE );
689
690 L_sites L_sites_type ;
691
692 /* Bug 5203710 remove detail_option as limiting criteria */
693 --C_detail_option hz_customer_profiles.cons_inv_type%TYPE;
694
695 TYPE tab_line_id IS TABLE OF ra_customer_trx_lines_all.link_to_cust_trx_line_id%TYPE;
696 TYPE tab_num IS TABLE OF NUMBER ;
697
698 l_line_id tab_line_id ;
699 l_tax_sum tab_num ;
700 l_include_tax_sum tab_num ;
701
702 l_bulk_fetch_rows NUMBER := 10000 ;
703
704 CURSOR c_tax (l_trx_id NUMBER) IS
705 SELECT link_to_cust_trx_line_id,
706 sum(nvl(CTL.extended_amount,0)),
707 sum(decode(amount_includes_tax_flag, 'Y', nvl(CTL.extended_amount,0),0))
708 FROM ra_customer_trx_lines CTL
709 WHERE CTL.customer_trx_id = l_trx_id
710 AND CTL.line_type = 'TAX'
711 GROUP BY link_to_cust_trx_line_id;
712
713 l_comments VARCHAR2(200);
714 l_bypass_trx BOOLEAN;
715 l_bill_level VARCHAR2(1);
716 l_billing_date DATE;
717 l_site_term NUMBER;
718 l_acct_term NUMBER;
719 l_param_err VARCHAR2(1);
720 l_customer_id NUMBER; /* Added for bug fix 5232547 */
721 curr_customer_id NUMBER;
722 l_cust_name_low VARCHAR2(240);
723 l_cust_name_high VARCHAR2(240);
724 l_cust_num_low VARCHAR2(30);
725 l_cust_num_high VARCHAR2(30);
726 l_bill_site_low NUMBER;
727 l_bill_site_high NUMBER;
728 l_remit_to_address_rec arp_trx_defaults_3.address_rec_type;
729 l_remit_to_address_id NUMBER;
730
731 l_party_id NUMBER;
732 l_bucket_name AR_AGING_BUCKETS.BUCKET_NAME%TYPE;
733 l_outstanding_balance NUMBER;
734 l_bucket_titletop_0 AR_AGING_BUCKET_LINES.REPORT_HEADING1%TYPE;
735 l_bucket_titlebottom_0 AR_AGING_BUCKET_LINES.REPORT_HEADING2%TYPE;
736 l_bucket_amount_0 NUMBER;
737 l_bucket_titletop_1 AR_AGING_BUCKET_LINES.REPORT_HEADING1%TYPE;
738 l_bucket_titlebottom_1 AR_AGING_BUCKET_LINES.REPORT_HEADING2%TYPE;
739 l_bucket_amount_1 NUMBER;
740 l_bucket_titletop_2 AR_AGING_BUCKET_LINES.REPORT_HEADING1%TYPE;
741 l_bucket_titlebottom_2 AR_AGING_BUCKET_LINES.REPORT_HEADING2%TYPE;
742 l_bucket_amount_2 NUMBER;
743 l_bucket_titletop_3 AR_AGING_BUCKET_LINES.REPORT_HEADING1%TYPE;
744 l_bucket_titlebottom_3 AR_AGING_BUCKET_LINES.REPORT_HEADING2%TYPE;
745 l_bucket_amount_3 NUMBER;
746 l_bucket_titletop_4 AR_AGING_BUCKET_LINES.REPORT_HEADING1%TYPE;
747 l_bucket_titlebottom_4 AR_AGING_BUCKET_LINES.REPORT_HEADING2%TYPE;
748 l_bucket_amount_4 NUMBER;
749 l_bucket_titletop_5 AR_AGING_BUCKET_LINES.REPORT_HEADING1%TYPE;
750 l_bucket_titlebottom_5 AR_AGING_BUCKET_LINES.REPORT_HEADING2%TYPE;
751 l_bucket_amount_5 NUMBER;
752 l_bucket_titletop_6 AR_AGING_BUCKET_LINES.REPORT_HEADING1%TYPE;
753 l_bucket_titlebottom_6 AR_AGING_BUCKET_LINES.REPORT_HEADING2%TYPE;
754 l_bucket_amount_6 NUMBER;
755
756 l_error_message VARCHAR2(2000);
757 l_cycle_start_date DATE;
758 BEGIN
759
760 write_debug_and_log('And so it begins...');
761 write_debug_and_log('P_print_option : ' || P_print_option);
762 write_debug_and_log('P_print_output : ' || P_print_output);
763 write_debug_and_log('P_billing_cycle_id : ' || to_char(P_billing_cycle_id));
764 write_debug_and_log('P_billing_date : ' || to_char(P_billing_date));
765 write_debug_and_log('P_currency : ' || P_currency);
766 write_debug_and_log('P_cust_name_low : ' || P_cust_name_low);
767 write_debug_and_log('P_cust_name_high : ' || P_cust_name_high);
768 write_debug_and_log('P_cust_num_low : ' || P_cust_num_low);
769 write_debug_and_log('P_cust_num_high : ' || P_cust_num_high);
770 write_debug_and_log('P_bill_site_low : ' || to_char(P_bill_site_low));
771 write_debug_and_log('P_bill_site_high : ' || to_char(P_bill_site_high));
772 write_debug_and_log('P_term_id : ' || to_char(P_term_id));
773 write_debug_and_log('P_print_status : ' || P_print_status);
774 ----Bug 12739341, add a flag to check whether it can generate future date bill
775 write_debug_and_log('P_future_bill_date : ' || P_future_Date_bill_flag);
776
777 /* Bug 5203710 remove detail_option as limiting criteria */
778 -- C_detail_option := nvl(P_detail_option, 'SUMMARY') ;
779
780 /* Validate params passed in are a valid combination, PRIOR to processing */
781
782 l_param_err := 'N';
783
784 IF FND_GLOBAL.CONC_REQUEST_ID is null THEN
785 -- only do pre-validation for cases where call is not made from conc program
786
787 IF P_term_id IS NOT NULL THEN
788 IF ar_bfb_utils_pvt.get_billing_cycle(P_term_id) <> P_billing_cycle_id THEN
789 write_debug_and_log('P_billing_cycle_id and P_term_id are not compatible');
790 l_param_err := 'Y';
791 ELSE
792 write_debug_and_log('P_billing_cycle_id passed initial validation.');
793 END IF;
794
795 -- if user provides customer number and site do additional pre-validation
796 IF P_cust_num_low IS NOT NULL AND
797 P_bill_site_low IS NOT NULL THEN
798
799 FOR v in val_param1(P_cust_num_low, P_cust_num_high,
800 P_bill_site_low, P_bill_site_high) LOOP
801
802 IF v.site_bill_level = 'ACCOUNT' AND v.acct_term <> P_term_id THEN
803 write_debug_and_log('ACCT level : P_customer_id ' || to_char(v.customer_id) ||
804 ' and P_term_id ' || to_char(P_term_id) || ' are not compatible');
805 l_param_err := 'Y';
806 ELSIF v.site_bill_level = 'SITE' AND v.site_term <> P_term_id THEN
807 write_debug_and_log('SITE level : P_customer_id ' || to_char(v.customer_id) ||
808 ' and P_term_id ' || to_char(P_term_id) || ' are not compatible');
809 l_param_err := 'Y';
810 END IF;
811
812 END LOOP;
813
814 IF l_param_err <> 'Y' THEN
815 write_debug_and_log('P_customer_id and P_term_id passed initial validation.');
816 END IF;
817 END IF;
818
819 END IF;
820 END IF;
821
822
823 --get billing_cycle attributes
824
825 select start_date
826 into l_cycle_start_date
827 from ar_cons_bill_cycles_b
828 where billing_cycle_id = p_billing_cycle_id;
829
830 IF l_param_err = 'Y' THEN
831 write_debug_and_log('ERROR : Incompatible parameters passed');
832 ELSE
833
834 l_tab_idx := 0;
835
836 SELECT bucket_name
837 INTO l_bucket_name
838 FROM ar_aging_buckets
839 WHERE aging_bucket_id = 2;
840
841 l_billing_date := P_billing_date;
842
843 FOR L_terms IN c_terms(P_billing_cycle_id, P_term_id) LOOP
844
845 write_debug_and_log(' ');
846 write_debug_and_log('Loop c_terms');
847 write_debug_and_log('...term_id : ' || to_char(L_terms.term_id));
848
849 IF ar_bfb_utils_pvt.get_cycle_type(P_billing_cycle_id) = 'EVENT' THEN
850 -- for EXTERNAL cycles, billing date should be provided
851 IF P_billing_date IS NOT NULL THEN
852 write_debug_and_log( 'EXCEPTION: generate, P_billing_date is null.' );
853 APP_EXCEPTION.raise_exception;
854 END IF;
855 END IF;
856
857
858 write_debug_and_log('...billing_date : ' || to_char(l_billing_date));
859
860 -- only process billing cycles that have billing date <= sysdate
861 IF l_billing_date > sysdate and nvl(p_future_date_bill_flag, 'N') = 'N' then --Bug 12739341, add a flag to check whether it can generate future date bill
862 write_debug_and_log('...BYPASSING THIS CYCLE SINCE NEXT SCHEDULED BILLING DATE IS IN THE FUTURE');
863 ELSE
864
865 -- pick up BFB related data from hz_customer_profiles
866 -- if bill_level = 'SITE', get term from site level profile
867 -- = 'ACCOUNT', get term from account level profile
868
869 -- pre-process the parameter ranges passed in
870 l_cust_name_low := P_cust_name_low;
871 l_cust_name_high := P_cust_name_high;
872 l_cust_num_low := P_cust_num_low;
873 l_cust_num_high := P_cust_num_high;
874 l_bill_site_low := P_bill_site_low;
875 l_bill_site_high := P_bill_site_high;
876
877 if l_cust_name_low is not null then
878 if l_cust_name_high is null then
879 l_cust_name_high := l_cust_name_low;
880 end if;
881 else
882 if l_cust_name_high is not null then
883 l_cust_name_low := l_cust_name_high;
884 end if;
885 end if;
886
887 if l_cust_num_low is not null then
888 if l_cust_num_high is null then
889 l_cust_num_high := l_cust_num_low;
890 end if;
891 else
892 if l_cust_num_high is not null then
893 l_cust_num_low := l_cust_num_high;
894 end if;
895 end if;
896
897 if l_bill_site_low is not null then
898 if l_bill_site_high is null then
899 l_bill_site_high := l_bill_site_low;
900 end if;
901 else
902 if l_bill_site_high is not null then
903 l_bill_site_low := l_bill_site_high;
904 end if;
905 end if;
906
907 -- define generic SELECT portion of the statement
908 /* Bug 5203710 Add cons_inv_type as one of the params read */
909
910 c_site_select :=
911 'SELECT /*+ ORDERED */ ' ||
912 'acct_site.cust_account_id customer_id, ' ||
913 'site_uses.site_use_id site_id, ' ||
914 'ar_bfb_utils_pvt.get_bill_level(acct_site.cust_account_id) bill_level, ' ||
915 'decode(ar_bfb_utils_pvt.get_bill_level(acct_site.cust_account_id), ' ||
916 ' ''A'', CP.override_terms, ' ||
917 ' ''S'', SP.override_terms) override_terms, ' ||
918 'decode(ar_bfb_utils_pvt.get_bill_level(acct_site.cust_account_id), ' ||
919 ' ''A'', CP.cons_inv_type, ' ||
920 ' ''S'', SP.cons_inv_type) cons_inv_type ';
921
922 -- define generic FROM clause
923 c_site_from :=
924 'FROM ' ||
925 'hz_cust_acct_sites acct_site, ' ||
926 'hz_cust_site_uses site_uses, ' ||
927 'hz_customer_profiles CP, ' ||
928 'hz_customer_profiles SP ';
929
930 -- define generic WHERE clause
931 /* Bug 5203710 remove cons_inv_type as limiting criteria */
932 c_site_where :=
933 'WHERE site_uses.cust_acct_site_id = acct_site.cust_acct_site_id ' ||
934 'AND site_uses.site_use_code = ''BILL_TO'' ' ||
935 'AND CP.cust_account_id = acct_site.cust_account_id ' ||
936 'AND CP.site_use_id IS NULL ' ||
937 'AND SP.site_use_id(+) = site_uses.site_use_id ' ||
938 'AND ar_bfb_utils_pvt.is_payment_term_bfb(nvl(SP.standard_terms, CP.standard_terms)) = ''Y'' ' ||
939 'AND :term_id = ' ||
940 ' decode(ar_bfb_utils_pvt.get_bill_level(acct_site.cust_account_id), ' ||
941 ' ''A'', CP.standard_terms, ' ||
942 ' ''S'', SP.standard_terms) ' ||
943 'AND decode(ar_bfb_utils_pvt.get_bill_level(acct_site.cust_account_id), ' ||
944 ' ''A'', CP.cons_inv_flag, ' ||
945 ' ''S'', SP.cons_inv_flag) = ''Y'' ' ||
946 'AND NOT EXISTS ' ||
947 ' (SELECT NULL ' ||
948 ' FROM ar_cons_inv CI ' ||
949 ' WHERE CI.site_use_id = site_uses.site_use_id ' ||
950 ' AND CI.billing_date >= :billing_date ' ||
951 ' AND CI.currency_code = :currency ' ||
952 ' AND CI.status <> ''REJECTED'') ' ||
953 'AND NOT EXISTS ' ||
954 ' (SELECT NULL ' ||
955 ' FROM ar_cons_inv CI2 ' ||
956 ' WHERE CI2.site_use_id = site_uses.site_use_id ' ||
957 ' AND CI2.currency_code = :currency ' ||
958 ' AND CI2.status = ''DRAFT'') ';
959
960 -- add on tables/conditions depending on parameters passed in
961 IF l_cust_name_low is not null THEN
962 c_site_from := c_site_from || ', hz_parties party, hz_cust_accounts acct ';
963
964 c_site_where := c_site_where ||
965 'AND party.party_name between :cust_name_low and :cust_name_high ' ||
966 'AND party.party_id = acct.party_id ' ||
967 'AND acct.cust_account_id = acct_site.cust_account_id ';
968
969 IF l_cust_num_low is not null THEN
970 c_site_where := c_site_where ||
971 'AND acct.account_number between :cust_num_low and :cust_num_high ';
972 END IF;
973
974 ELSIF l_cust_num_low is not null THEN
975 c_site_from := c_site_from || ' ,hz_cust_accounts acct ';
976
977 c_site_where := c_site_where ||
978 'AND acct.account_number between :cust_num_low and :cust_num_high ' ||
979 'AND acct.cust_account_id = acct_site.cust_account_id ';
980
981 END IF;
982
983 IF l_bill_site_low is not null THEN
984
985 c_site_where := c_site_where ||
986 'AND site_uses.site_use_id between :bill_site_low and :bill_site_high ';
987 END IF;
988
989 -- put together dynamic SQL for the cursor C_Sites
990 c_site_stmt := c_site_select || c_site_from || c_site_where;
991
992 write_debug_and_log('c_site_stmt = ' || c_site_stmt);
993
994 -- handle eight combinations :
995 -- Name Y N Y Y Y N N N
996 -- Num Y N N N Y Y Y N
997 -- Site Y N N Y N Y N Y
998
999 -- Y Y Y
1000 IF l_cust_name_low is not null AND
1001 l_cust_num_low is not null AND
1002 l_bill_site_low is not null THEN
1003
1004 write_debug_and_log('...Name/Number/Site provided');
1005
1006 OPEN C_sites FOR c_site_stmt USING
1007 L_Terms.term_id,
1008 --Bug 5203710 C_detail_option,
1009 P_billing_date,
1010 P_currency,
1011 P_currency,
1012 l_cust_name_low,
1013 l_cust_name_high,
1014 l_cust_num_low,
1015 l_cust_num_high,
1016 l_bill_site_low,
1017 l_bill_site_high;
1018
1019 -- N N N
1020 ELSIF l_cust_name_low is null AND
1021 l_cust_num_low is null AND
1022 l_bill_site_low is null THEN
1023
1024 write_debug_and_log('...Name/Number/Site NOT provided');
1025
1026 OPEN C_sites FOR c_site_stmt USING
1027 L_Terms.term_id,
1028 -- bug 5203710 C_detail_option,
1029 P_billing_date,
1030 P_currency,
1031 P_currency;
1032
1033 -- Y N N
1034 ELSIF l_cust_name_low is not null AND
1035 l_cust_num_low is null AND
1036 l_bill_site_low is null THEN
1037
1038 write_debug_and_log('...Name provided, Number/site NOT provided');
1039
1040 OPEN C_sites FOR c_site_stmt USING
1041 L_Terms.term_id,
1042 -- bug 5203710 C_detail_option,
1043 P_billing_date,
1044 P_currency,
1045 P_currency,
1046 l_cust_name_low,
1047 l_cust_name_high;
1048
1049 -- Y N Y
1050 ELSIF l_cust_name_low is not null AND
1051 l_cust_num_low is null AND
1052 l_bill_site_low is not null THEN
1053
1054 write_debug_and_log('...Name/Site provided, Number NOT provided');
1055
1056 OPEN C_sites FOR c_site_stmt USING
1057 L_Terms.term_id,
1058 -- bug 5203710 C_detail_option,
1059 P_billing_date,
1060 P_currency,
1061 P_currency,
1062 l_cust_name_low,
1063 l_cust_name_high,
1064 l_bill_site_low,
1065 l_bill_site_high;
1066
1067 -- Y Y N
1068 ELSIF l_cust_name_low is not null AND
1069 l_cust_num_low is not null AND
1070 l_bill_site_low is null THEN
1071
1072 write_debug_and_log('...Name/Number provided, Site NOT provided');
1073
1074 OPEN C_sites FOR c_site_stmt USING
1075 L_Terms.term_id,
1076 -- bug 5203710 C_detail_option,
1077 P_billing_date,
1078 P_currency,
1079 P_currency,
1080 l_cust_name_low,
1081 l_cust_name_high,
1082 l_cust_num_low,
1083 l_cust_num_high;
1084
1085 -- N Y Y
1086 ELSIF l_cust_name_low is null AND
1087 l_cust_num_low is not null AND
1088 l_bill_site_low is not null THEN
1089
1090 write_debug_and_log('...Number/Site provided, Name NOT provided');
1091
1092 OPEN C_sites FOR c_site_stmt USING
1093 L_Terms.term_id,
1094 -- bug 5203710 C_detail_option,
1095 P_billing_date,
1096 P_currency,
1097 P_currency,
1098 l_cust_num_low,
1099 l_cust_num_high,
1100 l_bill_site_low,
1101 l_bill_site_high;
1102
1103 -- N Y N
1104 ELSIF l_cust_name_low is null AND
1105 l_cust_num_low is not null AND
1106 l_bill_site_low is null THEN
1107
1108 write_debug_and_log('...Number provided, Name/Site NOT provided');
1109
1110 OPEN C_sites FOR c_site_stmt USING
1111 L_Terms.term_id,
1112 -- bug 5203710 C_detail_option,
1113 P_billing_date,
1114 P_currency,
1115 P_currency,
1116 l_cust_num_low,
1117 l_cust_num_high;
1118
1119 -- N N Y
1120 ELSIF l_cust_name_low is null AND
1121 l_cust_num_low is null AND
1122 l_bill_site_low is not null THEN
1123
1124 write_debug_and_log('...Site provided, Name/Number NOT provided');
1125
1126 OPEN C_sites FOR c_site_stmt USING
1127 L_Terms.term_id,
1128 -- bug 5203710 C_detail_option,
1129 P_billing_date,
1130 P_currency,
1131 P_currency,
1132 l_bill_site_low,
1133 l_bill_site_high;
1134 END IF;
1135
1136 curr_customer_id := 0;
1137 l_customer_id := 0;
1138
1139 LOOP
1140 FETCH C_sites INTO L_sites;
1141
1142 IF C_sites%NOTFOUND THEN
1143
1144 /*-----------------------------------------------
1145 Show the message below only when cursor did not
1146 find any rows to process.
1147 ------------------------------------------------*/
1148
1149 IF C_sites%ROWCOUNT = 0 THEN
1150 FND_MESSAGE.SET_NAME( 'AR', 'AR_BFB_NO_RECORDS' );
1151 FND_MSG_PUB.ADD;
1155 l_error_message := FND_MSG_PUB.Get(
1152 FND_MSG_PUB.Reset;
1153
1154 FOR i IN 1..FND_MSG_PUB.Count_Msg LOOP
1156 p_msg_index => i,
1157 p_encoded => FND_API.G_FALSE);
1158 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, l_error_message );
1159 FND_FILE.PUT_LINE(FND_FILE.log, l_error_message );
1160 END LOOP;
1161 EXIT;
1162
1163 ELSE
1164 EXIT;
1165 END IF;
1166
1167 END IF;
1168
1169 /** get next billing invoice id, create header with zero totals. **/
1170
1171 SELECT ar_cons_inv_s.NEXTVAL
1172 INTO l_consinv_id
1173 FROM dual;
1174
1175
1176 write_debug_and_log(' ');
1177 write_debug_and_log('... Loop c_sites');
1178 write_debug_and_log('......customer_id : '||TO_CHAR(L_sites.customer_id));
1179 write_debug_and_log('......site_id : '||TO_CHAR(L_sites.site_id));
1180 write_debug_and_log('......bill_level : '||TO_CHAR(L_sites.bill_level));
1181 write_debug_and_log('......override_terms : '||TO_CHAR(L_sites.override_terms));
1182 write_debug_and_log('.......cons_inv_type : '||TO_CHAR(L_sites.cons_inv_type));
1183
1184 /* get party_id */
1185 select p.party_id
1186 into l_party_id
1187 from hz_parties p, hz_cust_accounts c
1188 where c.cust_account_id = L_sites.customer_id
1189 and c.party_id = p.party_id;
1190
1191
1192 /** get beginning balance for new billing invoice from prior billing invoice **/
1193 /*6933233, modified the query to include cons invoice with status = 'FINAL' as well
1194 for calculating beginning balance.*/
1195
1196 /* Bug 7677870: billing_date is replaced with nvl(billing_date,cut_off_date)
1197 to carry forward the ending balance of the last CBI run in 11i as
1198 beginning balance of the first BFB run in R12 */
1199
1200 BEGIN
1201
1202 SELECT sum(ending_balance), max(nvl(billing_date,cut_off_date))
1203 INTO l_beginning_balance, l_last_bill_date
1204 FROM ar_cons_inv CI1
1205 WHERE CI1.site_use_id = L_sites.site_id
1206 AND CI1.currency_code = P_currency
1207 AND (CI1.status IN ('ACCEPTED', 'FINAL')
1208 AND nvl(CI1.billing_date,CI1.cut_off_date) =
1209 (SELECT max(nvl(CI2.billing_date,CI2.cut_off_date))
1210 FROM ar_cons_inv CI2
1211 WHERE CI2.site_use_id = L_sites.site_id
1212 AND CI2.currency_code = P_currency
1213 AND CI2.status IN ('ACCEPTED', 'FINAL')));
1214
1215 EXCEPTION
1216 WHEN NO_DATA_FOUND THEN
1217 l_beginning_balance := 0;
1218 END;
1219
1220 write_debug_and_log('Get Billing Date for Bill');
1221
1222 IF L_sites.bill_level = 'A' THEN
1223 -- ACCT LEVEL : use same cons_billing_number for all sites of this customer
1224 IF curr_customer_id <> L_sites.customer_id THEN
1225 curr_customer_id := L_sites.customer_id;
1226
1227 l_cons_billno := to_char(l_consinv_id);
1228
1229
1230 --all sites under this ACCOUNT LEVEl bill will have same
1231 --same billing_date, so we will calc the billing_date right here.
1232
1233 IF ar_bfb_utils_pvt.get_cycle_type(P_billing_cycle_id) <> 'EVENT'
1234 THEN -- calculate next logical billing date
1235 IF l_billing_date is null THEN
1236 l_billing_date := ar_bfb_utils_pvt.get_bill_process_date
1237 (P_billing_cycle_id,
1238 trunc(sysdate),
1239 nvl(l_last_bill_date,l_cycle_start_date));
1240
1241 ELSE
1242 l_billing_date := ar_bfb_utils_pvt.get_bill_process_date
1243 (P_billing_cycle_id,
1244 l_billing_date,
1245 nvl(l_last_bill_date,l_cycle_start_date));
1246
1247 END IF;
1248
1249 END IF;
1250
1251 END IF;
1252
1253 ELSE
1254 -- SITE LEVEL : use one cons_billing_number per site
1255 l_cons_billno := to_char(l_consinv_id);
1256
1257 IF ar_bfb_utils_pvt.get_cycle_type(P_billing_cycle_id) <> 'EVENT'
1258 THEN -- calculate next logical billing date
1259 IF l_billing_date is null THEN
1260 l_billing_date := ar_bfb_utils_pvt.get_bill_process_date
1261 (P_billing_cycle_id,
1262 trunc(sysdate),
1263 nvl(l_last_bill_date,l_cycle_start_date));
1264
1265 ELSE
1266 l_billing_date := ar_bfb_utils_pvt.get_bill_process_date
1267 (P_billing_cycle_id,
1268 l_billing_date,
1269 nvl(l_last_bill_date,l_cycle_start_date));
1270
1271 END IF;
1272 END IF;
1273
1274 END IF;
1275 write_debug_and_log('........billing_date :'||l_billing_date);
1276
1277 /* Bug 8242289: Bypass the site if New Billing Date is same as last Billing Date */
1278 if l_billing_date = l_last_bill_date then
1279 write_debug_and_log('New Billing Date is same as the last Billing Date, so bypassing this Site: '||L_sites.site_id);
1280 else
1281
1282 /** calculate due date **/
1283
1284 l_due_date := ar_bfb_utils_pvt.get_due_date(l_billing_date, L_terms.term_id);
1285 write_debug_and_log('......l_due_date : '||TO_CHAR(l_due_date));
1286
1287
1288 /* get remit_to_address */
1289 BEGIN
1290 arp_trx_defaults_3.get_remit_to_address(
1291 null,
1292 null,
1293 null,
1294 null,
1295 L_sites.site_id,
1296 l_remit_to_address_id,
1297 l_remit_to_address_rec
1298 );
1299 EXCEPTION
1300 WHEN OTHERS THEN
1301 l_remit_to_address_id := NULL;
1302 END;
1303
1304 /** For Site: create header.
1305 note it is possible that only the header will created if no
1306 transactions are found.
1307 **/
1308
1309 write_debug_and_log('......insert to ar_cons_inv, ID: ' || TO_CHAR(l_consinv_id) ||
1310 ' number: ' || l_cons_billno);
1311
1312
1313 INSERT INTO ar_cons_inv (cons_inv_id,
1314 cons_billing_number,
1315 customer_id,
1316 site_use_id,
1317 concurrent_request_id,
1318 last_update_date,
1319 last_updated_by,
1320 creation_date,
1321 created_by,
1322 last_update_login,
1323 cons_inv_type,
1324 status,
1325 print_status,
1326 term_id,
1327 issue_date,
1328 due_date,
1329 currency_code,
1330 beginning_balance,
1331 ending_balance,
1332 org_id,
1333 billing_date,
1334 bill_level_flag,
1335 last_billing_date,
1336 billing_cycle_id,
1337 remit_to_address_id)
1338 VALUES (l_consinv_id,
1339 l_cons_billno,
1340 L_sites.customer_id,
1341 L_sites.site_id,
1342 arp_standard.profile.request_id,
1343 arp_global.last_update_date,
1344 arp_global.last_updated_by,
1345 arp_global.creation_date,
1346 arp_global.created_by,
1347 arp_global.last_update_login,
1348 --Bug 5203710 get the cons_inv_type from customer setup
1349 L_sites.cons_inv_type,
1350 P_print_option,
1351 P_print_status,
1355 P_currency,
1352 L_terms.term_id,
1353 sysdate,
1354 l_due_date,
1356 nvl(l_beginning_balance,0),
1357 0,
1358 arp_standard.sysparm.org_id,
1359 l_billing_date,
1360 L_sites.bill_level,
1361 l_last_bill_date,
1362 P_billing_cycle_id,
1363 l_remit_to_address_id);
1364
1365 /** For Site: process invoices, credit memos. Need loop to assign line no. **/
1366
1367 l_consinv_lineno := 1;
1368
1369 FOR L_inv_trx IN C_inv_trx(L_sites.site_id, l_billing_date, P_currency) LOOP
1370
1371 write_debug_and_log(' ');
1372 write_debug_and_log('.........Loop C_inv_trx for site = ' || to_char(L_sites.site_id));
1373 write_debug_and_log('.........trx_id :'||TO_CHAR(L_inv_trx.trx_id));
1374 write_debug_and_log('.........trx_date :'||TO_CHAR(L_inv_trx.trx_date));
1375 write_debug_and_log('.........trx_number :'||L_inv_trx.trx_number);
1376 write_debug_and_log('.........class :'||L_inv_trx.class);
1377 write_debug_and_log('.........term_id :'||TO_CHAR(L_inv_trx.term_id));
1378 write_debug_and_log('.........billing_date :'||TO_CHAR(L_inv_trx.billing_date));
1379
1380 -- initialize comment
1381 l_comments := '';
1382 l_bypass_trx := FALSE;
1383
1384 -- set the value of l_check_override to l_tab_idx so that we can check
1385 -- after process_override whether an update has taken place by comparing
1386 -- these two variables. bug 6488683
1387 l_check_override := l_tab_idx;
1388
1389 -- Prior to inserting into ar_cons_inv_trx, need to perform validation and override if necessary
1390 IF nvl(L_inv_trx.term_id,'-1') <> L_terms.term_id THEN
1391
1392 IF L_inv_trx.class = 'CM' THEN
1393 /* Bug 9392028 */
1394 SELECT count(*)
1395 INTO l_cm_flag
1396 FROM ar_payment_schedules ps,
1397 ra_customer_trx ct
1398 WHERE ps.customer_trx_id = l_inv_trx.trx_id
1399 AND ct.customer_trx_id = ps.customer_trx_id
1400 AND ps.status = 'OP'
1401 AND ct.previous_customer_trx_id IS NULL;
1402
1403 IF l_cm_flag = 0 THEN
1404 --Resetting the same flag l_cm_flag.
1405
1406 SELECT count(*)
1407 INTO l_cm_flag
1408 FROM RA_CUSTOMER_TRX ct,
1409 AR_PAYMENT_SCHEDULES ps
1410 WHERE ct.customer_trx_id IN (Select ra.APPLIED_CUSTOMER_TRX_ID
1411 FROM ar_receivable_applications ra
1412 WHERE ra.customer_trx_id = l_inv_trx.trx_id
1413 AND ra.status = 'APP'
1414 AND ra.application_type = 'CM'
1415 AND ra.apply_date <= l_billing_date
1416 GROUP BY ra.APPLIED_CUSTOMER_TRX_ID
1417 HAVING SUM(nvl(ra.amount_applied_from, ra.amount_applied)) <> 0)
1418 AND ct.customer_trx_id = ps.customer_trx_id
1419 -- AND ar_bfb_utils_pvt.is_payment_term_bfb(ct.term_id) = 'Y' -- commented on 08mar2012
1420 AND nvl(PS.exclude_from_cons_bill_flag, 'N') <> 'Y'
1421 ---Added below condition to verify if associated INV is there in prior BFB
1422 AND EXISTS (SELECT '*'
1423 FROM ar_cons_inv c,
1424 ar_cons_inv_trx ctrx
1425 WHERE ct.customer_trx_id = ctrx.customer_trx_id
1426 AND c.cons_inv_id = ctrx.cons_inv_id
1427 AND c.status <> 'REJECTED');
1428
1429 -- Added code to handle the case of receipt being applied against CM and CM.
1430 If l_cm_flag = 0 THEN
1431 SELECT count(*)
1432 INTO l_cm_flag
1433 FROM ar_receivable_applications
1434 WHERE APPLIED_CUSTOMER_TRX_ID = l_inv_trx.trx_id
1435 AND status = 'APP'
1436 AND application_type = 'CASH'
1437 AND cash_receipt_id IS NOT NULL;
1438 END IF;
1439
1440 If l_cm_flag = 0 THEN
1441 l_bypass_trx := TRUE;
1442 END IF;
1443 END IF;
1444
1445 -- bypass additional validation, since CM's have no term id / billing date
1446 write_debug_and_log('............bypassing BFB validation for CM');
1447
1448 ELSE
1449 -- perform additional validation
1450
1451 IF L_sites.override_terms = 'Y' THEN
1452
1453 IF ar_bfb_utils_pvt.is_payment_term_bfb(L_inv_trx.term_id) = 'N' THEN
1454 -- trx does not have BFB term, bypass it
1455 l_bypass_trx := TRUE;
1456 ELSE
1457
1458 write_debug_and_log('............override_terms = Y, term_id is different');
1459
1460 process_override
1461 ('TERM_ID',
1462 L_inv_trx.trx_id,
1463 L_inv_trx.class,
1464 L_inv_trx.init_trx_id,
1465 L_inv_trx.prev_trx_id,
1466 L_inv_trx.billing_date,
1467 L_inv_trx.term_id,
1468 l_billing_date,
1469 L_inv_trx.due_date,
1470 L_terms.term_id,
1471 l_comments,
1472 l_bypass_trx);
1473
1474 END IF;
1475
1476 ELSE
1477 write_debug_and_log('............override_terms = N, term_id is different');
1478
1479 /* Bug 9092366: When the transaction and the customer profile have different
1480 payment terms, and if the 'Override Terms' is unchecked, then that
1481 transaction should not be processed. */
1482 l_bypass_trx := TRUE;
1483
1484 END IF;
1485 END IF;
1486
1487 ELSE
1488
1489 IF nvl(trunc(L_inv_trx.billing_date),to_date('12/31/4712','MM/DD/YYYY')) =
1490 trunc(l_billing_date) THEN
1491
1492 write_debug_and_log('............BFB data fully compliant');
1493 l_comments := 'FULLY COMPLIANT';
1494 ELSE
1495 write_debug_and_log('............billing_date is different');
1496 process_override
1497 ('BILLING_DATE',
1498 L_inv_trx.trx_id,
1499 L_inv_trx.class,
1500 L_inv_trx.init_trx_id,
1501 L_inv_trx.prev_trx_id,
1502 L_inv_trx.billing_date,
1503 L_inv_trx.term_id,
1504 l_billing_date,
1505 L_inv_trx.due_date,
1506 L_terms.term_id,
1507 l_comments,
1508 l_bypass_trx);
1509 END IF;
1510 END IF;
1511
1512 IF NOT l_bypass_trx THEN
1513
1514 -- get the current value of payment schedule id in case
1515 -- it is changed for overridden payment terms .
1516 -- If it is not overridden, assign the actual value of
1517 -- payment_schedule_id . bug 6488683
1518 IF ( l_tab_idx > l_check_override ) THEN
1519 OPEN ps_cur(L_inv_trx.trx_id);
1520 FETCH ps_cur INTO l_new_schedule_id;
1521 CLOSE ps_cur;
1522 ELSE
1523 l_new_schedule_id := L_inv_trx.schedule_id;
1524 END IF;
1525
1526 write_debug_and_log('new payment schedule id ' || l_new_schedule_id );
1527 write_debug_and_log('insert to ar_cons_inv_trx for ' || L_inv_trx.trx_id);
1528
1529 INSERT INTO ar_cons_inv_trx (cons_inv_id,
1530 transaction_type,
1531 trx_number,
1532 transaction_date,
1533 amount_original,
1534 tax_original,
1535 adj_ps_id,
1536 cons_inv_line_number,
1537 org_id,
1538 justification,
1539 trx_description,
1540 customer_trx_id,
1541 ship_to_site_id)
1542 VALUES (l_consinv_id,
1543 DECODE(L_inv_trx.class,
1544 'CM','CREDIT_MEMO',
1545 'DM','DEBIT_MEMO',
1546 'DEP','DEPOSIT',
1547 'CB','CHARGEBACK',
1548 'INVOICE'),
1549 L_inv_trx.trx_number,
1550 L_inv_trx.trx_date,
1551 L_inv_trx.amount_due,
1552 L_inv_trx.tax,
1553 l_new_schedule_id, -- bug 6488683
1554 l_consinv_lineno,
1555 arp_standard.sysparm.org_id,
1556 l_comments,
1557 L_inv_trx.trx_desc,
1558 L_inv_trx.trx_id,
1559 L_inv_trx.ship_id);
1560
1561 /** For audit purposes, insert detail line information even if
1562 reporting in summary. Also note that cons_inv_line_number
1563 is one value for detail lines for a specific invoice. **/
1564
1565 write_debug_and_log('insert to ar_cons_inv_trx_lines for ' || L_inv_trx.trx_id);
1566 INSERT INTO ar_cons_inv_trx_lines (cons_inv_id,
1567 cons_inv_line_number,
1568 customer_trx_id,
1569 customer_trx_line_id,
1570 line_number,
1571 inventory_item_id,
1572 description,
1573 uom_code,
1574 quantity_invoiced,
1575 unit_selling_price,
1576 extended_amount,
1577 tax_amount,
1578 org_id)
1579 SELECT l_consinv_id,
1580 l_consinv_lineno,
1581 customer_trx_id,
1582 customer_trx_line_id,
1583 line_number,
1584 inventory_item_id,
1585 description,
1586 uom_code,
1587 decode(L_inv_trx.class,'CM', quantity_credited,quantity_invoiced),
1588 nvl (gross_unit_selling_price, unit_selling_price),
1589 nvl (gross_extended_amount, extended_amount),
1590 0,
1591 org_id
1592 FROM ra_customer_trx_lines
1593 WHERE customer_trx_id = L_inv_trx.trx_id
1594 AND line_type NOT IN ('TAX', 'FREIGHT');
1595
1596 /** now update lines with associated tax line **/
1597
1598 write_debug_and_log('update ar_cons_inv_trx_lines for TAX');
1599 OPEN c_tax(L_inv_trx.trx_id);
1600 LOOP
1601 FETCH c_tax BULK COLLECT INTO
1602 l_line_id , l_tax_sum, l_include_tax_sum LIMIT l_bulk_fetch_rows;
1603
1604 -- 1. Update tax_amount
1605 -- 2. Exclude inclusive tax amount total from extended_amount
1606 FORALL i IN 1..l_line_id.count
1607 UPDATE ar_cons_inv_trx_lines
1608 SET tax_amount = l_tax_sum(i),
1609 extended_amount = extended_amount - l_include_tax_sum(i)
1610 WHERE customer_trx_id = L_inv_trx.trx_id
1611 AND customer_trx_line_id = l_line_id(i) ;
1612
1613 EXIT WHEN c_tax%NOTFOUND ;
1614 END LOOP;
1615 CLOSE c_tax;
1616
1617 /** now create 1 summary row for freight **/
1618 write_debug_and_log('insert to ar_cons_inv_trx_lines for FREIGHT');
1619 INSERT INTO ar_cons_inv_trx_lines (cons_inv_id,
1620 cons_inv_line_number,
1621 customer_trx_id,
1622 customer_trx_line_id,
1623 line_number,
1624 inventory_item_id,
1625 description,
1626 uom_code,
1627 quantity_invoiced,
1628 unit_selling_price,
1629 extended_amount,
1630 tax_amount,
1631 org_id)
1632 SELECT
1633 l_consinv_id,
1634 l_consinv_lineno,
1635 max(customer_trx_id),
1636 max(customer_trx_line_id),
1637 max(line_number),
1638 NULL,
1639 'Freight',
1640 NULL,
1641 1,
1642 sum (nvl (gross_extended_amount, extended_amount)),
1643 sum (nvl (gross_extended_amount, extended_amount)),
1644 0,
1645 org_id
1646 FROM
1647 ra_customer_trx_lines
1648 WHERE
1649 customer_trx_id = L_inv_trx.trx_id
1650 AND line_type = 'FREIGHT'
1651 GROUP BY line_type,org_id;
1652
1653 l_consinv_lineno := l_consinv_lineno + 1;
1654
1655
1656
1657 END IF; /* NOT l_bypass_trx */
1658
1659 END LOOP; /* c_inv_trx */
1660
1661
1662 write_debug_and_log('.........Done with Loop C_inv_trx');
1663
1664 /** TRANSACTION ACTIVITY :
1665 Pick up all Receipts / CMs / Adjustments that affect the BFB balance **/
1666
1667 /* Bug 9392028 Modified Activities */
1668
1669 /* ACTIVITY 1 : ADJUSTMENTS
1670 pick up all adjustments except finance charges generated against this BFB site
1671 (fin charge is in next select ACTIVITY 1A) */
1672
1673 write_debug_and_log('.........ACTIVITY 1');
1674
1675 INSERT INTO ar_cons_inv_trx (cons_inv_id,
1676 transaction_type,
1677 trx_number,
1678 transaction_date,
1679 amount_original,
1680 tax_original,
1681 adj_ps_id,
1682 cons_inv_line_number,
1683 org_id)
1684 SELECT /*+ index (PS AR_PAYMENT_SCHEDULES_N5) */
1685 l_consinv_id,
1686 'ADJUSTMENT',
1687 PS.trx_number,
1688 ADJ.apply_date,
1689 ADJ.amount,
1690 NVL(ADJ.tax_adjusted, 0),
1691 ADJ.adjustment_id,
1692 NULL,
1693 ps.org_id
1694 FROM
1695 ar_adjustments ADJ,
1696 ar_payment_schedules PS
1697 WHERE
1698 ADJ.cons_inv_id is NULL
1699 AND ADJ.apply_date <= l_billing_date
1700 AND ADJ.type in ('CHARGES','FREIGHT','INVOICE','LINE','TAX')
1701 AND ADJ.created_from <> 'ARFCCF' -- exclude auto-generated finance charges
1702 AND ADJ.status = 'A'
1703 AND PS.payment_schedule_id = ADJ.payment_schedule_id
1704 AND PS.customer_site_use_id = L_sites.site_id
1705 AND PS.invoice_currency_code = P_currency
1706 AND PS.class||'' <> 'GUAR'
1707 AND nvl(PS.exclude_from_cons_bill_flag, 'N') <> 'Y'
1708 AND EXISTS (SELECT '*'
1709 FROM ar_cons_inv c,
1710 ar_cons_inv_trx ctrx
1711 WHERE adj.customer_trx_id = ctrx.customer_trx_id
1712 AND c.cons_inv_id = ctrx.cons_inv_id
1713 AND c.status <> 'REJECTED');
1714
1715
1716 /* ACTIVITY 1A : FINANCE CHARGES
1717 pick up all adjustments (only finance charges) generated against this BFB site */
1718
1719 write_debug_and_log('.........ACTIVITY 1A');
1720
1721 INSERT INTO ar_cons_inv_trx (cons_inv_id,
1722 transaction_type,
1723 trx_number,
1724 transaction_date,
1725 amount_original,
1726 tax_original,
1727 adj_ps_id,
1728 cons_inv_line_number,
1729 org_id)
1730 SELECT /*+ index (PS AR_PAYMENT_SCHEDULES_N5) */
1731 l_consinv_id,
1732 'FINANCE CHARGE',
1733 PS.trx_number,
1734 ADJ.apply_date,
1735 ADJ.amount,
1736 NVL(ADJ.tax_adjusted, 0),
1737 ADJ.adjustment_id,
1738 NULL,
1739 ps.org_id
1740 FROM
1741 ar_adjustments ADJ,
1742 ar_payment_schedules PS
1743 WHERE
1744 ADJ.cons_inv_id is NULL
1745 AND ADJ.apply_date <= l_billing_date
1746 AND ADJ.type = 'CHARGES'
1747 AND ADJ.created_from = 'ARFCCF'
1748 AND ADJ.status = 'A'
1749 AND PS.payment_schedule_id = ADJ.payment_schedule_id
1750 AND PS.customer_site_use_id = L_sites.site_id
1751 AND PS.invoice_currency_code = P_currency
1752 AND PS.class||'' <> 'GUAR'
1753 AND nvl(PS.exclude_from_cons_bill_flag, 'N') <> 'Y'
1754 AND EXISTS (SELECT '*'
1755 FROM ar_cons_inv c,
1756 ar_cons_inv_trx ctrx
1757 WHERE adj.customer_trx_id = ctrx.customer_trx_id
1758 AND c.cons_inv_id = ctrx.cons_inv_id
1759 AND c.status <> 'REJECTED');
1760
1761 /* ACTIVITY 2 : RECEIPTS
1762 pick up FULL receipt amount for receipts created against this BFB
1763 site (in ACTIVITY 4 : we back out amounts applied to diff site) */
1764
1765 write_debug_and_log('.........ACTIVITY 2');
1766
1767 INSERT INTO ar_cons_inv_trx (cons_inv_id,
1768 transaction_type,
1769 trx_number,
1770 transaction_date,
1771 amount_original,
1772 tax_original,
1773 adj_ps_id,
1774 cons_inv_line_number,
1775 org_id)
1776 SELECT
1777 l_consinv_id,
1778 'RECEIPT',
1779 PS.trx_number,
1780 CR.receipt_date,
1781 PS.amount_due_original,
1782 NULL,
1783 PS.payment_schedule_id,
1784 NULL,
1785 PS.org_id
1786 FROM
1787 ar_payment_schedules PS,
1788 ar_cash_receipts CR
1789 WHERE
1790 PS.customer_site_use_id = L_sites.site_id
1791 AND PS.cons_inv_id IS NULL
1792 AND PS.class = 'PMT'
1793 AND PS.invoice_currency_code = P_currency
1794 AND CR.cash_receipt_id = PS.cash_receipt_id
1795 AND CR.receipt_date <= l_billing_date
1796 AND nvl(PS.exclude_from_cons_bill_flag, 'N') <> 'Y'
1797 AND (PS.status = 'OP'
1798 OR (ps.amount_due_original +
1799 (SELECT nvl(SUM(nvl(ra.amount_applied_from, ra.amount_applied)), 0)
1800 FROM ar_receivable_applications ra,
1801 ar_payment_schedules ps_inv,
1802 ra_customer_trx inv_trx
1803 WHERE ra.cash_receipt_id = cr.cash_receipt_id
1804 AND inv_trx.customer_trx_id = ra.applied_customer_trx_id
1805 AND RA.status = 'APP'
1806 AND ra.application_type = 'CASH'
1807 AND ra.apply_date <= l_billing_date
1808 AND ps_inv.customer_trx_id = inv_trx.customer_trx_id
1809 AND (Decode(ps_inv.class ,'CM','Y',ar_bfb_utils_pvt.is_payment_term_bfb(inv_trx.term_id)) <> 'Y' /* Bug 13485325 */
1810 OR nvl(ps_inv.exclude_from_cons_bill_flag, 'N') = 'Y'))) <> 0);
1811
1812 /* ACTIVITY 3 : RECEIPT REVERSAL
1813 Reverse FULL receipt amount for receipt reversals of
1814 receipts created against this BFB site */
1815
1816 write_debug_and_log('.........ACTIVITY 3');
1817
1818 INSERT INTO ar_cons_inv_trx (cons_inv_id,
1819 transaction_type,
1820 trx_number,
1824 adj_ps_id,
1821 transaction_date,
1822 amount_original,
1823 tax_original,
1825 cons_inv_line_number,
1826 org_id)
1827 SELECT
1828 l_consinv_id,
1829 'RECEIPT REV',
1830 PS.trx_number,
1831 CR.reversal_date,
1832 (-1)*PS.amount_due_original,
1833 NULL,
1834 PS.payment_schedule_id,
1835 NULL,
1836 CR.org_id
1837 FROM
1838 ar_payment_schedules PS,
1839 ar_cash_receipts CR
1840 WHERE
1841 PS.customer_site_use_id = L_sites.site_id
1842 AND PS.cons_inv_id_rev IS NULL
1843 AND PS.invoice_currency_code = P_currency
1844 AND PS.class = 'PMT'
1845 AND CR.cash_receipt_id = PS.cash_receipt_id
1846 AND CR.reversal_date <= l_billing_date
1847 AND nvl(PS.exclude_from_cons_bill_flag, 'N') <> 'Y'
1848 AND EXISTS
1849 (SELECT '*'
1850 FROM ar_cons_inv c,
1851 ar_cons_inv_trx ctrx
1852 WHERE PS.payment_schedule_id = ctrx.adj_ps_id
1853 AND c.cons_inv_id = ctrx.cons_inv_id
1854 AND c.status <> 'REJECTED');
1855
1856 /* ACTIVITY 4 : Exclude RECEIPT APPLICATIONS for Receipts
1857 with this BFB site which are applied to TRX which have
1858 a different site */
1859
1860 write_debug_and_log('.........ACTIVITY 4');
1861
1862 INSERT INTO ar_cons_inv_trx (cons_inv_id,
1863 transaction_type,
1864 trx_number,
1865 transaction_date,
1866 amount_original,
1867 tax_original,
1868 adj_ps_id,
1869 cons_inv_line_number,
1870 org_id)
1871 SELECT
1872 l_consinv_id,
1873 DECODE (nvl(ps_inv.exclude_from_cons_bill_flag, 'N'), 'Y','EXCLUDE RECREV',
1874 DECODE (nvl (ps_cash.customer_site_use_id, -1), ps_inv.customer_site_use_id,
1875 DECODE (ps_cash.invoice_currency_code, ps_inv.invoice_currency_code,
1876 'XXXXXXXXXX', 'XCURR RECREV'),
1877 DECODE (ps_cash.invoice_currency_code, ps_inv.invoice_currency_code,
1878 'XSITE RECREV', 'XSITE XCURR RECREV')) ),
1879 ps_cash.trx_number,
1880 RA.apply_date,
1881 nvl (ra.amount_applied_from, RA.amount_applied),
1882 NULL,
1883 RA.receivable_application_id,
1884 NULL,
1885 ps_cash.org_id
1886 FROM
1887 ar_receivable_applications RA,
1888 ar_payment_schedules ps_cash,
1889 ar_payment_schedules ps_inv
1890 WHERE
1891 RA.cons_inv_id IS NULL
1892 AND RA.status = 'APP'
1893 AND RA.application_type = 'CASH'
1894 AND RA.apply_date <= l_billing_date
1895 AND ps_cash.payment_schedule_id = RA.payment_schedule_id
1896 AND ps_cash.customer_site_use_id = L_sites.site_id
1897 AND ps_cash.invoice_currency_code = P_currency
1898 AND ps_inv.payment_schedule_id = RA.applied_payment_schedule_id
1899 AND nvl(ps_cash.exclude_from_cons_bill_flag, 'N') <> 'Y'
1900 AND ( ps_cash.customer_site_use_id <> ps_inv.customer_site_use_id
1901 OR RA.amount_applied_from IS NOT NULL) --Bug 8208763
1902 -- OR nvl(ps_inv.exclude_from_cons_bill_flag, 'N') = 'Y')
1903 AND EXISTS
1904 (SELECT '*'
1905 FROM ar_cons_inv c,
1906 ar_cons_inv_trx ctrx
1907 WHERE RA.payment_schedule_id = ctrx.adj_ps_id
1908 AND c.cons_inv_id = ctrx.cons_inv_id
1909 AND c.status <> 'REJECTED')
1910 AND EXISTS --bug 12349325
1911 (SELECT '*'
1912 FROM ar_cons_inv c,
1913 ar_cons_inv_trx ctrx
1914 WHERE ps_inv.payment_schedule_id = ctrx.adj_ps_id
1915 AND c.cons_inv_id = ctrx.cons_inv_id
1916 AND c.status <> 'REJECTED');
1917
1918 /* ACTIVITY 5 : Include RECEIPT APPLICATIONS for Receipts created with
1919 different (or null) site, but applied to TRX with this BFB Site */
1920
1921 write_debug_and_log('.........ACTIVITY 5');
1922
1923 INSERT INTO ar_cons_inv_trx (cons_inv_id,
1924 transaction_type,
1925 trx_number,
1926 transaction_date,
1927 amount_original,
1928 tax_original,
1929 adj_ps_id,
1930 cons_inv_line_number,
1931 org_id)
1932 SELECT
1933 l_consinv_id,
1934 DECODE (nvl(ps_cash.exclude_from_cons_bill_flag, 'N'),'Y','EXCLUDE RECAPP',
1935 DECODE (nvl (ps_cash.customer_site_use_id, -1), ps_inv.customer_site_use_id,
1936 DECODE (ps_cash.invoice_currency_code, ps_inv.invoice_currency_code,
1937 'XXXXXXXXXX', 'XCURR RECAPP'),
1938 DECODE (ps_cash.invoice_currency_code, ps_inv.invoice_currency_code,
1939 'XSITE RECAPP', 'XSITE XCURR RECAPP')) ),
1940 ps_cash.trx_number,
1941 RA.apply_date,
1942 (-1)*RA.amount_applied,
1943 NULL,
1944 RA.receivable_application_id,
1945 NULL,
1946 ps_cash.org_id
1947 FROM
1948 ar_receivable_applications RA,
1949 ar_payment_schedules ps_cash,
1950 ar_payment_schedules ps_inv
1951 WHERE
1952 RA.cons_inv_id_to IS NULL
1953 AND RA.status = 'APP'
1954 AND RA.application_type = 'CASH'
1955 AND RA.apply_date <= l_billing_date
1956 AND ps_cash.payment_schedule_id = RA.payment_schedule_id
1957 AND ps_inv.payment_schedule_id = RA.applied_payment_schedule_id
1958 AND ps_inv.customer_site_use_id = L_sites.site_id
1959 AND ps_inv.invoice_currency_code = P_currency
1960 AND nvl(ps_inv.exclude_from_cons_bill_flag, 'N') <> 'Y'
1961 AND ( nvl(ps_cash.customer_site_use_id, -1) <> ps_inv.customer_site_use_id
1962 OR ra.amount_applied_from IS NOT NULL
1963 OR nvl(ps_cash.exclude_from_cons_bill_flag, 'N') = 'Y')
1964 AND EXISTS
1965 (SELECT '*'
1966 FROM ar_cons_inv c,
1967 ar_cons_inv_trx ctrx
1968 WHERE ps_inv.customer_trx_id = ctrx.customer_trx_id
1969 AND c.cons_inv_id = ctrx.cons_inv_id
1970 AND c.status <> 'REJECTED');
1971
1972 /* ACTIVITY 6 : When a receipt is originally created without a location,
1973 and is immediately applied to an invoice, the receipt's ps.customer_site_use_id
1974 remains NULL, hence such an application is considered in ACTIVITY 5.
1975 Now if later, that receipt is updated with a Location = this BFB site the
1976 receipt will now be picked up in ACTIVITY 2.
1977
1978 The following select is necessary to counter what was previously picked up
1979 in ACTIVITY 5, otherwise the receipt application is recorded twice */
1980
1981 write_debug_and_log('.........ACTIVITY 6');
1982
1983 INSERT INTO ar_cons_inv_trx (cons_inv_id,
1984 transaction_type,
1985 trx_number,
1986 transaction_date,
1987 amount_original,
1988 tax_original,
1989 adj_ps_id,
1990 cons_inv_line_number,
1991 org_id)
1992 SELECT
1993 l_consinv_id,
1997 RA.apply_date,
1994 DECODE (ps_cash.invoice_currency_code, ps_inv.invoice_currency_code,
1995 'XSITE RECREV', 'XSITE XCURR RECREV'),
1996 ps_cash.trx_number,
1998 nvl (ra.amount_applied_from, RA.amount_applied),
1999 NULL,
2000 RA.receivable_application_id,
2001 NULL,
2002 ps_cash.org_id
2003 FROM
2004 ar_cons_inv_trx inv_trx,
2005 ar_receivable_applications ra,
2006 ar_payment_schedules ps_cash,
2007 ar_payment_schedules ps_inv
2008 WHERE ra.cons_inv_id_to is not null
2009 AND ra.cons_inv_id is null
2010 AND ra.status = 'APP'
2011 AND ra.application_type = 'CASH'
2012 AND ra.apply_date <= l_billing_date
2013 AND ps_cash.payment_schedule_id = ra.payment_schedule_id
2014 AND ps_cash.customer_site_use_id = L_sites.site_id
2015 AND ps_cash.invoice_currency_code = P_currency
2016 AND ps_inv.payment_schedule_id = ra.applied_payment_schedule_id
2017 AND ps_cash.customer_site_use_id = ps_inv.customer_site_use_id
2018 AND ra.receivable_application_id = inv_trx.adj_ps_id
2019 AND inv_trx.transaction_type IN ('XSITE RECAPP','XSITE XCURR RECAPP');
2020
2021 /* ACTIVITY 7 : When a receipt is originally created without a location,
2022 and is immediately applied to an invoice, the receipt's ps.customer_site_use_id
2023 remains NULL, hence such an application is considered in ACTIVITY 5.
2024 Now if later, that receipt is updated with a Location different from this BFB site
2025 we need to exclude it. */
2026
2027 write_debug_and_log('.........ACTIVITY 7');
2028
2029 INSERT INTO ar_cons_inv_trx (cons_inv_id,
2030 transaction_type,
2031 trx_number,
2032 transaction_date,
2033 amount_original,
2034 tax_original,
2035 adj_ps_id,
2036 cons_inv_line_number,
2037 org_id)
2038 SELECT
2039 l_consinv_id,
2040 DECODE (ps_cash.invoice_currency_code, ps_inv.invoice_currency_code,
2041 'XSITE RECAPP', 'XSITE XCURR RECAPP'),
2042 ps_cash.trx_number,
2043 RA.apply_date,
2044 (-1)*RA.amount_applied,
2045 NULL,
2046 RA.receivable_application_id,
2047 NULL,
2048 ps_cash.org_id
2049 FROM
2050 ar_cons_inv_trx inv_trx,
2051 ar_receivable_applications ra,
2052 ar_payment_schedules ps_cash,
2053 ar_payment_schedules ps_inv
2054 WHERE ra.cons_inv_id_to is null
2055 AND ra.cons_inv_id is not null
2056 AND ra.status = 'APP'
2057 AND ra.application_type = 'CASH'
2058 AND ra.apply_date <= l_billing_date
2059 AND ps_cash.payment_schedule_id = RA.payment_schedule_id
2060 AND ps_inv.payment_schedule_id = RA.applied_payment_schedule_id
2061 AND ps_inv.customer_site_use_id = L_sites.site_id
2062 AND ps_inv.invoice_currency_code = P_currency
2063 AND ps_cash.customer_site_use_id = ps_inv.customer_site_use_id
2064 AND ra.receivable_application_id = inv_trx.adj_ps_id
2065 AND inv_trx.transaction_type IN ('XSITE RECREV','XSITE XCURR RECREV');
2066
2067 /* ACTIVITY 8 : CM applications where CM is for this BFB site, but
2068 applied to an invoice having a different site */
2069
2070 write_debug_and_log('.........ACTIVITY 8');
2071
2072 INSERT INTO ar_cons_inv_trx (cons_inv_id,
2073 transaction_type,
2074 trx_number,
2075 transaction_date,
2076 amount_original,
2077 tax_original,
2078 adj_ps_id,
2079 cons_inv_line_number,
2080 org_id)
2081 SELECT /*+ ORDERED */
2082 l_consinv_id,
2083 DECODE(nvl(PS_INV.exclude_from_cons_bill_flag,'N'),
2084 'Y', 'EXCLUDE_CMREV',
2085 'XSITE_CMREV'),
2086 PS_CM.trx_number,
2087 RA.apply_date,
2088 RA.amount_applied,
2089 NULL,
2093 FROM
2090 RA.receivable_application_id,
2091 NULL,
2092 PS_CM.org_id
2097 AR_CONS_INV_TRX CTRX,
2094 AR_PAYMENT_SCHEDULES PS_CM ,
2095 AR_RECEIVABLE_APPLICATIONS RA ,
2096 AR_PAYMENT_SCHEDULES PS_INV,
2098 AR_CONS_INV C
2099 WHERE
2100 RA.cons_inv_id IS NULL
2101 AND RA.status = 'APP'
2102 AND RA.application_type = 'CM'
2103 AND RA.apply_date <= l_billing_date
2104 AND PS_CM.payment_schedule_id = RA.payment_schedule_id
2105 AND PS_CM.customer_site_use_id = L_sites.site_id
2106 AND PS_CM.invoice_currency_code = P_currency
2107 AND nvl(PS_CM.exclude_from_cons_bill_flag, 'N') <> 'Y'
2108 AND PS_INV.payment_schedule_id = RA.applied_payment_schedule_id
2109 AND ( PS_INV.customer_site_use_id <> PS_CM.customer_site_use_id
2110 or nvl(PS_INV.exclude_from_cons_bill_flag, 'N') = 'Y' )
2111 AND PS_CM.customer_trx_id = ctrx.customer_trx_id
2112 AND c.cons_inv_id = ctrx.cons_inv_id
2113 AND c.status <> 'REJECTED';
2114
2115 /* ACTIVITY 8A : CM applied to INV both have same BFB site, BUT
2116 INV is not pulled into BFB yet, need to exclude */
2117
2118 write_debug_and_log('.........ACTIVITY 8A');
2119
2120 INSERT INTO ar_cons_inv_trx (cons_inv_id,
2121 transaction_type,
2122 trx_number,
2123 transaction_date,
2124 amount_original,
2125 tax_original,
2126 adj_ps_id,
2127 cons_inv_line_number,
2128 org_id)
2129 SELECT
2130 l_consinv_id,
2131 'DELAY_CMAPP',
2132 PS_CM.trx_number,
2133 RA.apply_date,
2134 RA.amount_applied,
2135 NULL,
2136 RA.receivable_application_id,
2137 NULL,
2138 PS_CM.org_id
2139 FROM
2140 ar_receivable_applications RA,
2141 ar_payment_schedules PS_CM,
2142 ar_payment_schedules PS_INV
2143 WHERE
2144 RA.cons_inv_id_to IS NULL
2145 AND RA.status = 'APP'
2146 AND RA.application_type = 'CM'
2147 AND RA.apply_date <= l_billing_date
2148 AND PS_CM.payment_schedule_id = RA.payment_schedule_id
2149 AND PS_CM.customer_site_use_id = L_sites.site_id
2150 AND PS_CM.invoice_currency_code = P_currency
2151 AND nvl(PS_CM.exclude_from_cons_bill_flag, 'N') <> 'Y'
2152 AND PS_INV.payment_schedule_id = RA.applied_payment_schedule_id
2153 AND PS_INV.customer_site_use_id = L_sites.site_id
2154 AND NOT EXISTS
2155 (SELECT /*+ no_unnest */ '*'
2156 FROM ar_cons_inv c,
2157 ar_cons_inv_trx ctrx
2158 WHERE PS_INV.customer_trx_id = ctrx.customer_trx_id
2159 AND c.cons_inv_id = ctrx.cons_inv_id
2160 AND c.status <> 'REJECTED')
2161 AND EXISTS
2162 (SELECT /*+ no_unnest */ '*'
2163 FROM ar_cons_inv c,
2164 ar_cons_inv_trx ctrx
2165 WHERE PS_CM.customer_trx_id = ctrx.customer_trx_id
2166 AND c.cons_inv_id = ctrx.cons_inv_id
2167 AND c.status <> 'REJECTED');
2168
2169 /* ACTIVITY 9 : CM Applications where CM site is different, but applied to
2170 TRX which has this BFB site
2171 NOTE : do not pull in CM application if the INV it is applied to is not
2172 part of an BFB yet */
2173
2174 write_debug_and_log('.........ACTIVITY 9');
2175
2176 INSERT INTO ar_cons_inv_trx (cons_inv_id,
2177 transaction_type,
2178 trx_number,
2179 transaction_date,
2180 amount_original,
2181 tax_original,
2182 adj_ps_id,
2183 cons_inv_line_number,
2184 org_id)
2185 SELECT
2186 l_consinv_id,
2187 DECODE( nvl(PS_CM.exclude_from_cons_bill_flag, 'N') , 'Y', 'EXCLUDE_CMAPP','XSITE_CMAPP') ,
2188 PS_INV.trx_number,
2189 RA.apply_date,
2190 (-1)*RA.amount_applied,
2191 NULL,
2192 RA.receivable_application_id,
2193 NULL,
2194 PS_INV.org_id
2195 FROM
2196 ar_receivable_applications RA,
2197 ar_payment_schedules PS_INV, -- INV
2198 ar_payment_schedules PS_CM -- CM
2199 WHERE
2200 RA.cons_inv_id_to IS NULL
2201 AND RA.status = 'APP'
2202 AND RA.application_type = 'CM'
2203 AND RA.apply_date <= l_billing_date
2204 AND PS_INV.payment_schedule_id = RA.applied_payment_schedule_id
2205 AND PS_INV.customer_site_use_id = L_sites.site_id
2206 AND PS_INV.invoice_currency_code = P_currency
2207 AND nvl(PS_INV.exclude_from_cons_bill_flag, 'N') <> 'Y'
2208 AND PS_CM.payment_schedule_id = RA.payment_schedule_id
2209 AND ( PS_CM.customer_site_use_id <> PS_INV.customer_site_use_id
2210 or nvl(PS_CM.exclude_from_cons_bill_flag, 'N') = 'Y')
2211 AND EXISTS (SELECT '*'
2212 FROM ar_cons_inv c,
2213 ar_cons_inv_trx ctrx
2214 WHERE PS_INV.customer_trx_id = ctrx.customer_trx_id
2215 AND c.cons_inv_id = ctrx.cons_inv_id
2216 AND c.status <> 'REJECTED');
2217
2218
2219 /* Bug fix 5232547 : Receipts without Billing Location */
2220 IF L_sites.bill_level = 'A' THEN
2221 -- Run the inserts only once for a customer
2222 IF l_customer_id <> L_sites.customer_id THEN
2223 l_customer_id := L_sites.customer_id;
2224
2225 write_debug_and_log('.........ACTIVITY 10 : Receipts with No Location');
2226
2227 INSERT INTO ar_cons_inv_trx (cons_inv_id,
2228 transaction_type,
2229 trx_number,
2233 adj_ps_id,
2230 transaction_date,
2231 amount_original,
2232 tax_original,
2234 cons_inv_line_number,
2235 org_id)
2236 SELECT
2237 l_consinv_id,
2238 'RECEIPT',
2239 PS.trx_number,
2240 CR.receipt_date,
2241 PS.amount_due_original,
2242 NULL,
2243 PS.payment_schedule_id,
2244 NULL,
2245 PS.org_id
2246 FROM
2247 ar_payment_schedules PS,
2248 ar_cash_receipts CR
2249 WHERE
2250 PS.customer_id = L_sites.customer_id
2251 AND PS.customer_site_use_id IS NULL
2252 AND PS.cons_inv_id IS NULL
2253 AND PS.class = 'PMT'
2254 AND PS.invoice_currency_code = P_currency
2255 AND CR.cash_receipt_id = PS.cash_receipt_id
2256 AND CR.receipt_date <= l_billing_date
2257 AND nvl(PS.exclude_from_cons_bill_flag, 'N') <> 'Y'
2258 AND (PS.status = 'OP'
2259 OR (ps.amount_due_original +
2260 (SELECT nvl(SUM(nvl(ra.amount_applied_from, ra.amount_applied)), 0)
2261 FROM ar_receivable_applications ra,
2262 ar_payment_schedules ps_inv,
2263 ra_customer_trx inv_trx
2264 WHERE ra.cash_receipt_id = cr.cash_receipt_id
2265 AND inv_trx.customer_trx_id = ra.applied_customer_trx_id
2266 AND RA.status = 'APP'
2267 AND ra.application_type = 'CASH'
2268 AND ra.apply_date <= l_billing_date
2269 AND ps_inv.customer_trx_id = inv_trx.customer_trx_id
2270 AND (ar_bfb_utils_pvt.is_payment_term_bfb(inv_trx.term_id) <> 'Y'
2271 OR nvl(ps_inv.exclude_from_cons_bill_flag, 'N') = 'Y'))) <> 0);
2272
2273 /* ACTIVITY 11 : RECEIPT REVERSAL
2274 Reverse FULL receipt amount for receipt reversals of
2275 receipts created without site */
2276
2277 write_debug_and_log('.........ACTIVITY 11: Reversal of receipts with no location');
2278
2279 INSERT INTO ar_cons_inv_trx (cons_inv_id,
2280 transaction_type,
2281 trx_number,
2282 transaction_date,
2283 amount_original,
2284 tax_original,
2285 adj_ps_id,
2286 cons_inv_line_number,
2287 org_id)
2288 SELECT
2289 l_consinv_id,
2290 'RECEIPT REV',
2291 PS.trx_number,
2292 CR.reversal_date,
2293 (-1)*PS.amount_due_original,
2294 NULL,
2295 PS.payment_schedule_id,
2296 NULL,
2297 CR.org_id
2298 FROM
2299 ar_payment_schedules PS,
2300 ar_cash_receipts CR
2301 WHERE
2302 PS.customer_id =L_sites.customer_id
2303 AND PS.customer_site_use_id IS NULL
2304 AND PS.cons_inv_id_rev IS NULL
2305 AND PS.invoice_currency_code = P_currency
2306 AND PS.class = 'PMT'
2307 AND CR.cash_receipt_id = PS.cash_receipt_id
2308 AND CR.reversal_date <= l_billing_date
2309 AND nvl(PS.exclude_from_cons_bill_flag, 'N') <> 'Y'
2310 AND EXISTS
2311 (SELECT '*'
2312 FROM ar_cons_inv c,
2313 ar_cons_inv_trx ctrx
2314 WHERE PS.payment_schedule_id = ctrx.adj_ps_id
2315 AND c.cons_inv_id = ctrx.cons_inv_id
2316 AND c.status <> 'REJECTED');
2317 END IF;
2318 END IF;
2319
2320 /* Code changes ends for bug 5232547 */
2321
2322 /* Bug 8832473 code changes start
2323
2324 ACTIVITY 12 : RECEIPT ADJUSTMENT
2325
2326 Below are the scenarios handled under this code:
2327
2328 1. When the receipt is fully applied (no unapplied amount) to Invoices
2329 with non-BFB term.
2330
2331 There are two scenarios here. First one is if the receipt was already
2332 included in a prior BFB and later applied to non-BFB invoice then the next
2333 BFB should include a Receipt Adjustment entry to remove the receipt impact on
2334 the BFB. For example if the receipt amount is 100 USD then receipt adjustment
2335 entry will be for 100 USD. Second scenario is receipt was not included in any
2336 prior BFB then in this case it should not appear on the current BFB.
2337
2338 2. When the receipt is partially applied (unapplied amount exists) to
2339 Invoices with non-BFB term.
2340
2341 In this scenario the BFB should contain the entry for the receipt for
2342 full amount and a receipt adjustment entry for the amount applied to non-BFB
2343 term invoice. Example, receipt is for 100 USD and 25 USD is applied to
2344 non-BFB term invoice. Then BFB will contain -100 USD for the receipt and 25
2345 USD for the receipt adjustment.
2346 */
2347
2348 write_debug_and_log('.........ACTIVITY 12 : RECEIPT ADJUSTMENT');
2349
2350 INSERT INTO ar_cons_inv_trx (cons_inv_id,
2351 transaction_type,
2352 trx_number,
2353 transaction_date,
2354 amount_original,
2355 tax_original,
2356 adj_ps_id,
2357 cons_inv_line_number,
2358 org_id)
2359 SELECT
2360 l_consinv_id,
2361 'RECEIPT ADJUST',
2362 ps_cash.trx_number,
2363 RA.apply_date,
2364 nvl (ra.amount_applied_from, RA.amount_applied),
2365 NULL,
2366 RA.receivable_application_id,
2367 NULL,
2368 ps_cash.org_id
2369 FROM
2370 ar_receivable_applications RA,
2371 ar_payment_schedules ps_cash,
2372 ra_customer_trx inv_trx,
2373 ar_payment_schedules ps_app
2374 WHERE
2375 RA.cons_inv_id IS NULL
2376 AND RA.status = 'APP'
2377 AND RA.application_type = 'CASH'
2378 AND RA.apply_date <= l_billing_date
2379 AND ps_cash.payment_schedule_id = RA.payment_schedule_id
2380 AND ps_cash.customer_site_use_id = L_sites.site_id
2381 AND ps_cash.invoice_currency_code = P_currency
2382 AND nvl(ps_cash.exclude_from_cons_bill_flag, 'N') <> 'Y'
2383 AND inv_trx.CUSTOMER_TRX_ID = RA.APPLIED_CUSTOMER_TRX_ID
2384 AND ra.applied_payment_schedule_id = ps_app.payment_schedule_id
2385 AND EXISTS
2386 (SELECT '*'
2387 FROM ar_cons_inv c,
2388 ar_cons_inv_trx ctrx
2389 WHERE ps_cash.payment_schedule_id = ctrx.adj_ps_id
2390 AND ctrx.transaction_type = 'RECEIPT'
2391 AND c.cons_inv_id = ctrx.cons_inv_id
2392 AND c.status <> 'REJECTED')
2393 AND NOT EXISTS (SELECT '*'
2394 FROM ar_cons_inv c,
2395 ar_cons_inv_trx ctrx
2396 WHERE inv_trx.customer_trx_id = ctrx.customer_trx_id
2397 AND c.cons_inv_id = ctrx.cons_inv_id
2398 AND c.status <> 'REJECTED'
2399 UNION ALL
2400 select '*'
2401 FROM ar_cons_inv c,
2402 ar_cons_inv_trx ctrx
2403 where c.cons_inv_id=ps_app.cons_inv_id
2404 AND c.status <> 'REJECTED'
2405 AND ctrx.cons_inv_id=c.cons_inv_id
2406 AND ctrx.customer_trx_id is null
2407 AND ctrx.adj_ps_id=ps_app.payment_schedule_id)
2408 --bug 12349325
2409 UNION
2410 SELECT
2411 l_consinv_id,
2412 'RECEIPT ADJUST',
2413 ps_cash.trx_number,
2414 RA.apply_date,
2415 nvl (ra.amount_applied_from, RA.amount_applied),
2416 NULL,
2417 RA.receivable_application_id,
2418 NULL,
2419 ps_cash.org_id
2420 FROM
2421 ar_receivable_applications RA,
2422 ar_payment_schedules ps_cash
2423 WHERE
2424 RA.cons_inv_id IS NULL
2425 AND RA.applied_payment_schedule_id = -3
2426 AND RA.application_type = 'CASH'
2427 AND RA.apply_date <= l_billing_date
2428 AND ps_cash.payment_schedule_id = RA.payment_schedule_id
2429 AND ps_cash.customer_site_use_id = L_sites.site_id
2430 AND ps_cash.invoice_currency_code = P_currency
2431 AND nvl(ps_cash.exclude_from_cons_bill_flag, 'N') <> 'Y'
2432 AND EXISTS
2433 (SELECT '*'
2434 FROM ar_cons_inv c,
2435 ar_cons_inv_trx ctrx
2436 WHERE ps_cash.payment_schedule_id = ctrx.adj_ps_id
2437 AND c.cons_inv_id = ctrx.cons_inv_id
2438 AND ctrx.transaction_type = 'RECEIPT'
2439 AND c.status <> 'REJECTED');
2440
2441 /* Bug 8832473 code changes end */
2442
2443
2444 /** For Site: calculate totals **/
2445 SELECT nvl(sum(amount_original),0)
2446 INTO l_period_trx
2447 FROM ar_cons_inv_trx
2448 WHERE cons_inv_id = l_consinv_id
2449 AND transaction_type IN ('INVOICE', 'DEBIT_MEMO', 'DEPOSIT', 'CHARGEBACK');
2450
2451 SELECT nvl(sum(amount_original),0)
2452 INTO l_period_receipts
2453 FROM ar_cons_inv_trx
2454 WHERE cons_inv_id = l_consinv_id
2455 AND transaction_type IN ('RECEIPT','RECEIPT REV','XSITE RECREV',
2456 'XSITE RECAPP',
2457 'XCURR RECAPP', 'XCURR RECREV',
2458 'XSITE XCURR RECAPP','XSITE XCURR RECREV',
2459 'EXCLUDE RECREV', 'EXCLUDE RECAPP','RECEIPT ADJUST');
2460
2461 SELECT nvl(sum(amount_original),0)
2462 INTO l_period_finchrg
2463 FROM ar_cons_inv_trx
2464 WHERE cons_inv_id = l_consinv_id
2465 AND transaction_type IN ('FINANCE CHARGE');
2466
2467 SELECT nvl(sum(amount_original),0)
2468 INTO l_period_adj
2469 FROM ar_cons_inv_trx
2470 WHERE cons_inv_id = l_consinv_id
2471 AND transaction_type = 'ADJUSTMENT';
2472
2473 SELECT nvl(sum(amount_original),0)
2474 INTO l_period_credits
2475 FROM ar_cons_inv_trx
2476 WHERE cons_inv_id = l_consinv_id
2477 AND transaction_type IN ('CREDIT_MEMO',
2478 'XSITE_CMREV','XSITE_CMAPP',
2479 'EXCLUDE_CMREV', 'EXCLUDE_CMAPP',
2480 'DELAY_CMAPP');
2481
2482 SELECT nvl(sum(tax_amount),0)
2483 INTO l_period_tax
2484 FROM ar_cons_inv_trx_lines
2485 WHERE cons_inv_id = l_consinv_id;
2486
2487
2488 UPDATE ar_cons_inv
2489 SET total_receipts_amt = l_period_receipts,
2490 total_adjustments_amt = l_period_adj,
2491 total_credits_amt = l_period_credits,
2492 total_finance_charges_amt = l_period_finchrg,
2493 total_trx_amt = l_period_trx,
2494 total_tax_amt = l_period_tax,
2495 ending_balance = beginning_balance + l_period_trx + l_period_receipts +
2496 l_period_adj + l_period_credits + l_period_finchrg
2497 WHERE cons_inv_id = l_consinv_id;
2498
2499 /** For Site: update ar_payment_schedules, ar_receivable_applications
2500 and ar_adjustments **/
2501
2502 write_debug_and_log('Updating AR_PAYMENT_SCHEDULES');
2503
2504 UPDATE ar_payment_schedules PS
2505 SET PS.cons_inv_id = l_consinv_id
2506 WHERE PS.payment_schedule_id IN
2507 (SELECT IT.adj_ps_id
2508 FROM ar_cons_inv_trx IT
2509 WHERE IT.cons_inv_id = l_consinv_id
2510 AND IT.transaction_type IN ('INVOICE','CREDIT_MEMO', 'DEBIT_MEMO', 'DEPOSIT', 'CHARGEBACK',
2511 'RECEIPT'));
2512
2513 UPDATE ar_payment_schedules PS
2514 SET PS.cons_inv_id_rev = l_consinv_id
2515 WHERE PS.payment_schedule_id IN
2516 (SELECT IT.adj_ps_id
2517 FROM ar_cons_inv_trx IT
2518 WHERE IT.cons_inv_id = l_consinv_id
2519 AND IT.transaction_type = 'RECEIPT REV');
2520
2521 write_debug_and_log('Updating AR_RECEIVABLE_APPLICATIONS');
2522
2523 UPDATE ar_receivable_applications RA
2524 SET RA.cons_inv_id = l_consinv_id
2525 WHERE RA.receivable_application_id IN
2526 (SELECT IT.adj_ps_id
2527 FROM ar_cons_inv_trx IT
2528 WHERE IT.cons_inv_id = l_consinv_id
2529 AND IT.transaction_type IN ('XSITE RECREV',
2530 'XSITE_CMREV',
2531 'XCURR RECREV',
2532 'XSITE XCURR RECREV',
2533 'EXCLUDE RECREV',
2534 'EXCLUDE_CMREV',
2535 'RECEIPT ADJUST',
2536 'DELAY_CMAPP')); -- Bug 8946152
2537
2538 UPDATE ar_receivable_applications RA
2539 SET RA.cons_inv_id_to = l_consinv_id
2540 WHERE RA.receivable_application_id IN
2541 (SELECT IT.adj_ps_id
2542 FROM ar_cons_inv_trx IT
2543 WHERE IT.cons_inv_id = l_consinv_id
2544 AND IT.transaction_type IN ('XSITE RECAPP',
2545 'XSITE_CMAPP',
2546 'XCURR RECAPP',
2547 'XSITE XCURR RECAPP',
2548 'EXCLUDE RECAPP',
2549 'EXCLUDE_CMAPP'));
2550
2551 write_debug_and_log('Updating AR_ADJUSTMENTS');
2552
2553 UPDATE ar_adjustments RA
2554 SET RA.cons_inv_id = l_consinv_id
2555 WHERE RA.adjustment_id IN
2556 (SELECT /*+ index (IT AR_CONS_INV_TRX_N1) */
2557 IT.adj_ps_id
2558 FROM ar_cons_inv_trx IT
2559 WHERE IT.cons_inv_id = l_consinv_id
2560 AND IT.transaction_type = 'ADJUSTMENT');
2561
2562 write_debug_and_log('Updating AR_CONS_INV');
2563
2564 -- 6955957
2565 -- update ra_customer_trx_all with the printing dates for all the transactions included.
2566 UPDATE ra_customer_trx trx
2567 SET printing_original_date = nvl(printing_original_date, SYSDATE),
2568 printing_last_printed = nvl(printing_last_printed, SYSDATE)
2569 WHERE trx.trx_number IN
2570 (SELECT trx_number
2571 FROM ar_cons_inv_trx IT
2572 WHERE IT.cons_inv_id = l_consinv_id );
2573
2574 --Get the Aging information and update the
2575 -- aging buckets on the Bill
2576 --
2577 ar_cmgt_aging.calc_aging_buckets(
2578 l_party_id,
2579 L_sites.customer_id,
2580 L_sites.site_id,
2581 P_currency,
2582 NULL,
2583 l_bucket_name,
2584 arp_standard.sysparm.org_id,
2585 NULL,
2586 'CONS_BILL',
2587 l_outstanding_balance,
2588 l_bucket_titletop_0,
2589 l_bucket_titlebottom_0,
2590 l_bucket_amount_0,
2591 l_bucket_titletop_1,
2592 l_bucket_titlebottom_1,
2593 l_bucket_amount_1,
2594 l_bucket_titletop_2,
2595 l_bucket_titlebottom_2,
2596 l_bucket_amount_2,
2597 l_bucket_titletop_3,
2598 l_bucket_titlebottom_3,
2599 l_bucket_amount_3,
2600 l_bucket_titletop_4,
2601 l_bucket_titlebottom_4,
2602 l_bucket_amount_4,
2603 l_bucket_titletop_5,
2604 l_bucket_titlebottom_5,
2605 l_bucket_amount_5,
2606 l_bucket_titletop_6,
2607 l_bucket_titlebottom_6,
2608 l_bucket_amount_6);
2609
2610 UPDATE ar_cons_inv
2611 SET aging_bucket1_amt = l_bucket_amount_0,
2612 aging_bucket2_amt = l_bucket_amount_1,
2613 aging_bucket3_amt = l_bucket_amount_2,
2614 aging_bucket4_amt = l_bucket_amount_3,
2615 aging_bucket5_amt = l_bucket_amount_4,
2616 aging_bucket6_amt = l_bucket_amount_5,
2617 aging_bucket7_amt = l_bucket_amount_6
2618 WHERE cons_inv_id = l_consinv_id;
2619
2620 END IF; /* Bug 8242289 Bypass the site if new billing date = last billing date */
2621
2622 END LOOP; /* c_site */
2623
2624 END IF; /* l_billing_date <= sysdate */
2625 END LOOP; /* c_terms */
2626
2627 -- commmented out the following code for bug 6488683
2628 -- This is moved to the process_override procedure and the update is done for
2629 -- individual transactions instead of doing it as a bulk here.
2630 -- This is in order to get the new payment schedule id while inserting
2631 -- into ar_cons_inv_trx.
2632 /*
2633 -- Following is the update for all data overridden in TRX table
2634 IF l_tab_idx > 0 THEN
2635 write_debug_and_log('Override : Updating RA_CUSTOMER_TRX');
2636 FORALL i IN 1..l_tab_idx
2637 UPDATE RA_CUSTOMER_TRX
2638 SET term_id = l_tab_term_id(i),
2639 billing_date = l_tab_billing_date(i),
2640 term_due_date = l_tab_due_date(i)
2641 WHERE customer_trx_id = l_tab_trx_id(i);
2642
2643 write_debug_and_log('Override : Updating AR_PAYMENT_SCHEDULES');
2644 FOR i IN 1..l_tab_idx LOOP
2645 arp_process_header.post_commit( 'ARPBFBIB',
2646 120.0,
2647 l_tab_trx_id(i), -- customer_trx_id
2648 NULL, -- cm trx_id
2649 'Y', -- complete_flag
2650 NULL, -- INV open_rec flag
2651 NULL, -- CM open_rec_flag
2652 NULL, -- creation_sign,
2653 NULL, -- allow_overapp_flag,
2654 NULL, -- natural_app_only_flag,
2655 NULL -- cash_receipt_id
2656 );
2657 END LOOP;
2658
2659 FORALL i IN 1..l_tab_idx
2660 UPDATE AR_PAYMENT_SCHEDULES
2661 SET due_date = l_tab_due_date(i)
2662 WHERE customer_trx_id = l_tab_trx_id(i);
2663
2664 END IF;
2665
2666 */
2667
2668 END IF;
2669
2670 commit;
2671
2672 EXCEPTION
2673 WHEN OTHERS THEN
2674 write_debug_and_log('EXCEPTION: generate:' );
2675 write_debug_and_log('P_print_option : ' || P_print_option);
2676 write_debug_and_log('P_print_output : ' || P_print_output);
2677 write_debug_and_log('P_billing_cycle_id : ' || to_char(P_billing_cycle_id));
2678 write_debug_and_log('P_billing_date : ' || to_char(P_billing_date));
2679 write_debug_and_log('P_currency : ' || P_currency);
2680 write_debug_and_log('P_cust_num_low : ' || to_char(P_cust_num_low));
2681 write_debug_and_log('P_cust_num_high : ' || to_char(P_cust_num_high));
2682 write_debug_and_log('P_bill_site_low : ' || to_char(P_bill_site_low));
2683 write_debug_and_log('P_bill_site_high : ' || to_char(P_bill_site_high));
2684 write_debug_and_log('P_term_id : ' || to_char(P_term_id));
2685 write_debug_and_log('P_print_status : ' || P_print_status);
2686 RAISE;
2687 END;
2688 --
2689 /*----------------------------------------------------------------------------*
2690 | PROCEDURE |
2691 | update_status |
2692 | |
2693 | DESCRIPTION |
2694 | After Consolidated Billing Invoices are printed successfully, update |
2695 | status of the billing invoices from 'PENDING' to 'PRINTED'. |
2696 | For NEW or DRAFT, parameters P_consinv_id and P_request_id are NULL. |
2697 | These parameters are specified by the user for a REPRINT only. |
2698 | |
2699 | SCOPE - PRIVATE |
2700 | |
2701 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
2702 | |
2703 | ARGUMENTS : IN: |
2704 | P_print_option - print option |
2705 | P_consinv_id - consolidated billing invoice |
2706 | P_request_id - concurrent request id |
2707 | |
2708 | OUT: |
2709 | None |
2710 | RETURNS : None |
2711 | |
2712 | MODIFICATION HISTORY |
2713 | 26-MAY-2005 MRAYMOND 4188835 - Added freeze call related to
2714 | etax. When a invoice is printed, we need
2715 | to notify etax that it will not change.
2716 *----------------------------------------------------------------------------*/
2717 PROCEDURE update_status (P_print_option IN VARCHAR,
2718 P_consinv_id IN NUMBER,
2719 P_request_id IN NUMBER) IS
2720
2721 CURSOR c_pending_trx IS
2722 SELECT PS.customer_trx_id
2723 FROM ar_payment_schedules PS,
2724 ar_cons_inv_trx IT,
2725 ar_cons_inv CI
2726 WHERE
2727 CI.print_status = 'PENDING'
2728 AND IT.cons_inv_id = CI.cons_inv_id
2729 AND IT.transaction_type IN ('INVOICE','CREDIT_MEMO',
2730 'DEBIT_MEMO', 'DEPOSIT', 'CHARGEBACK')
2731 AND PS.payment_schedule_id = IT.adj_ps_id;
2732
2733 BEGIN
2734
2735 /* bug3604391 Changed the sequence of following update stmts.
2736 Because ra_customer_trx was not updated after
2737 ar_cons_inv.print_status was changed.
2738 */
2739 UPDATE ra_customer_trx CT
2740 SET CT.printing_original_date =
2741 nvl(CT.printing_original_date,sysdate),
2742 CT.printing_last_printed = sysdate,
2743 CT.printing_count = nvl(CT.printing_count,0) +
2744 DECODE(P_print_option,
2745 'REPRINT', 0,
2746 1)
2747 WHERE CT.customer_trx_id IN
2748 (SELECT PS.customer_trx_id
2749 FROM ar_payment_schedules PS,
2750 ar_cons_inv_trx IT,
2751 ar_cons_inv CI
2752 WHERE (
2753 (P_print_option = 'REPRINT'
2754 AND CI.cons_inv_id=nvl(P_consinv_id,CI.cons_inv_id)
2755 AND CI.concurrent_request_id =
2756 nvl(P_request_id, CI.concurrent_request_id))
2757 OR
2758 (P_print_option IN ('DRAFT', 'PRINT')
2759 AND CI.print_status = 'PENDING')
2760 )
2761 AND IT.cons_inv_id = CI.cons_inv_id
2762 AND IT.transaction_type IN ('INVOICE','CREDIT_MEMO')
2763 AND PS.payment_schedule_id = IT.adj_ps_id);
2764
2765 /* 4188835 - If printing for first time, freeze trans for tax */
2766 IF P_print_option = 'PRINT'
2767 THEN
2768 FOR trx in c_pending_trx LOOP
2769 arp_etax_util.global_document_update(trx.customer_trx_id,
2770 null,
2771 'PRINT');
2772 END LOOP;
2773 END IF;
2774
2775 UPDATE ar_cons_inv
2776 SET print_status = 'PRINTED',
2777 last_update_date = arp_global.last_update_date,
2778 last_updated_by = arp_global.last_updated_by,
2779 last_update_login = arp_global.last_update_login
2780 WHERE (P_print_option = 'REPRINT'
2781 AND cons_inv_id = nvl(P_consinv_id,cons_inv_id)
2782 AND concurrent_request_id = DECODE (P_consinv_id,
2783 NULL, P_request_id,
2784 concurrent_request_id))
2785 OR (P_print_option IN ('DRAFT', 'PRINT')
2786 AND print_status = 'PENDING');
2787
2788 EXCEPTION
2789 WHEN OTHERS THEN
2790 write_debug_and_log( ' Exception: update_status: ');
2791 RAISE;
2792 END;
2793
2794
2795 PROCEDURE Report( P_report IN ReportParametersType) Is
2796
2797 BEGIN
2798
2799 write_debug_and_log('arp_bf_bill.Report3(+)');
2800
2801 IF P_report.print_option in ('DRAFT','FINAL') THEN
2802
2803 generate(P_Report.print_option,
2804 P_Report.print_output,
2805 P_Report.billing_cycle_id,
2806 P_Report.billing_date,
2807 P_Report.currency,
2808 P_Report.cust_name_low,
2809 P_Report.cust_name_high,
2810 P_Report.cust_num_low,
2811 P_Report.cust_num_high,
2812 P_Report.bill_site_low,
2813 P_Report.bill_site_high,
2814 P_Report.term_id,
2815 NULL, /*Bug 5203710 */
2816 P_report.print_status,
2817 --Bug 12739341, add a flag to check whether it can generate future date bill
2818 p_report.future_date_bill_flag);
2819
2820 ELSIF P_report.print_option = 'REPRINT' THEN
2821
2822 IF P_report.print_status = 'PENDING' THEN
2823 reprint(P_report.consinv_id_low,
2824 P_report.request_id);
2825 ELSE
2826 update_status(P_report.print_option,
2827 P_report.consinv_id_low,
2828 P_report.request_id);
2829 END IF;
2830
2831 ELSIF P_report.print_option = 'ACCEPT' THEN
2832 accept( P_report.cust_num_low,
2833 P_report.cust_num_high,
2834 P_report.bill_site_low,
2835 P_report.bill_site_high,
2836 P_report.bill_date_low,
2837 P_report.bill_date_high,
2838 P_report.consinv_id_low,
2842
2839 P_report.consinv_id_high,
2840 P_report.request_id);
2841
2843 ELSIF P_report.print_option = 'REJECT' THEN
2844
2845 reject( P_report.cust_num_low,
2846 P_report.cust_num_high,
2847 P_report.bill_site_low,
2848 P_report.bill_site_high,
2849 P_report.bill_date_low,
2850 P_report.bill_date_high,
2851 P_report.consinv_id_low,
2852 P_report.consinv_id_high,
2853 P_report.request_id);
2854
2855 END IF;
2856
2857 write_debug_and_log('arp_bf_bill.Report3(-)');
2858
2859 EXCEPTION
2860 WHEN OTHERS THEN
2861 write_debug_and_log( 'Exception: arp_bf_bill.Report3 :'||sqlerrm );
2862 RAISE_APPLICATION_ERROR( -20000, sqlerrm);
2863 END;
2864
2865 -- overloaded procedure called for Accept / Reject
2866 /* Bug 5137184 Billing Date param should be varchar */
2867
2868 PROCEDURE Report( Errbuf OUT NOCOPY VARCHAR2,
2869 Retcode OUT NOCOPY NUMBER,
2870 P_print_option IN VARCHAR2,
2871 P_org_id IN NUMBER,
2872 P_cust_num_low IN VARCHAR2,
2873 P_cust_num_high IN VARCHAR2,
2874 P_bill_site_low IN NUMBER,
2875 P_bill_site_high IN NUMBER,
2876 P_bill_date_low IN VARCHAR2,
2877 P_bill_date_high IN VARCHAR2,
2878 P_consinv_id_low IN NUMBER,
2879 P_consinv_id_high IN NUMBER,
2880 P_request_id IN NUMBER) IS
2881
2882
2883 l_report ReportParametersType;
2884 CURSOR org_rec is
2885 select org_id
2886 from ar_system_parameters
2887 where org_id = nvl(p_org_id,org_id);
2888 BEGIN
2889
2890 write_debug_and_log('arp_bf_bill.Report2 (+)');
2891
2892 mo_global.init('AR');
2893
2894 l_report.print_option := P_print_option;
2895 l_report.print_output := NULL;
2896 l_report.billing_cycle_id := NULL;
2897 l_report.billing_date := NULL;
2898 l_report.currency := NULL;
2899 l_report.cust_name_low := NULL;
2900 l_report.cust_name_high := NULL;
2901 l_report.cust_num_low := P_cust_num_low;
2902 l_report.cust_num_high := P_cust_num_high;
2903 l_report.bill_site_low := P_bill_site_low;
2904 l_report.bill_site_high := P_bill_site_high;
2905
2906 /* Bug 5137184 --Program ends when Billing Date is passed */
2907 l_report.bill_date_low := fnd_date.canonical_to_date(P_bill_date_low);
2908 l_report.bill_date_high := fnd_date.canonical_to_date(P_bill_date_high);
2909
2910
2911 l_report.term_id := NULL;
2912 l_report.detail_option := NULL;
2913 l_report.consinv_id_low := P_consinv_id_low;
2914 l_report.consinv_id_high := P_consinv_id_high;
2915 l_report.request_id := P_request_id;
2916 l_report.print_status := NULL;
2917
2918 IF P_org_id is not null THEN
2919 write_debug_and_log('ORG ID : '||p_org_id);
2920 mo_global.set_policy_context('S',p_org_id);
2921 arp_standard.init_standard(p_org_id);
2922 Report(l_report);
2923
2924 ELSE
2925
2926 FOR k in org_rec LOOP
2927 write_debug_and_log('ORG ID : '||k.org_id);
2928
2929 mo_global.set_policy_context('S',k.org_id);
2930 arp_standard.init_standard(p_org_id);
2931 Report(l_report);
2932
2933 END LOOP;
2934
2935 END IF;
2936 write_debug_and_log('arp_bf_bill.Report2 (-)');
2937
2938 EXCEPTION
2939 WHEN OTHERS THEN
2940 write_debug_and_log( 'Exception:arp_bf_bill.Report2:'||sqlerrm);
2941 RAISE_APPLICATION_ERROR( -20000, sqlerrm);
2942 END;
2943
2944
2945
2946 /*----------------------------------------------------------------------------+
2947 | PROCEDURE |
2948 | report |
2949 | |
2950 | DESCRIPTION |
2951 | Called by before-report trigger in report ARXCBI. Depending on value |
2952 | of parameter print_option, will call the appropriate procedure. |
2953 | The print_status will be 'PENDING' when called by the before-report |
2954 | trigger. |
2955 | The after-report trigger in report ARXCBI will execute this stored |
2956 | procedure with print_status 'PRINTED' to denote a successful print for |
2957 | print options 'DRAFT', 'PRINTED', 'REPRINT'. |
2958 | |
2959 | SCOPE - public |
2960 | |
2961 | EXTERNAL PROCEDURE/FUNCTIONS ACCESSED |
2962 | |
2963 | RETURNS : NONE |
2964 | |
2965 | NOTES |
2966 | |
2967 | MODIFICATION HISTORY |
2968 | May-23-2006 Jyoti Pandey Bug 5137184 Billing Date param should be varchar |
2969 | |
2970 *----------------------------------------------------------------------------*/
2971 PROCEDURE Report( Errbuf OUT NOCOPY VARCHAR2,
2972 Retcode OUT NOCOPY NUMBER,
2973 P_print_option IN VARCHAR2,
2974 P_org_id IN NUMBER,
2975 P_print_output IN VARCHAR2,
2976 P_billing_cycle_id IN NUMBER,
2977 --Bug 12739341, add a flag to check whether it can generate future date bill
2978 p_future_date_bill_flag IN VARCHAR2,
2979 P_billing_date IN VARCHAR2,
2980 P_currency IN VARCHAR2,
2981 P_cust_name_low IN VARCHAR2,
2982 P_cust_name_high IN VARCHAR2,
2983 P_cust_num_low IN VARCHAR2,
2984 P_cust_num_high IN VARCHAR2,
2985 P_bill_site_low IN NUMBER,
2986 P_bill_site_high IN NUMBER,
2987 P_term_id IN NUMBER,
2988 P_detail_option IN VARCHAR2,
2989 P_consinv_id IN NUMBER DEFAULT 0,
2990 P_request_id IN NUMBER DEFAULT 0) IS
2991
2992 l_report ReportParametersType;
2993 l_use_currency VARCHAR2(15);
2994 l_request_id NUMBER;
2995 CURSOR org_rec is
2996 select org_id
2997 from ar_system_parameters
2998 where org_id = nvl(p_org_id,org_id);
2999 BEGIN
3000
3001 mo_global.init('AR');
3002 write_debug_and_log('arp_bf_bill.Report1 (+)');
3003
3004 IF P_org_id is not null THEN
3005
3006 write_debug_and_log('ORG ID : '||p_org_id);
3007 mo_global.set_policy_context('S',p_org_id);
3008 arp_standard.init_standard(p_org_id);
3009
3010 if P_Print_option in ( 'PRINT', 'DRAFT') THEN
3011 l_report.print_option := 'DRAFT';
3012 else
3013 l_report.print_option := P_print_option;
3014 end if;
3015
3016 IF p_currency is NULL THEN
3017 -- default to functional currency
3018 SELECT sob.currency_code
3019 INTO l_use_currency
3020 FROM gl_sets_of_books sob
3021 WHERE sob.set_of_books_id = arp_standard.sysparm.set_of_books_id;
3022 ELSE
3023 l_use_currency := P_currency;
3024 END IF;
3025
3026 l_report.print_output := P_print_output;
3027 l_report.billing_cycle_id := P_billing_cycle_id;
3028
3029 /* Bug 5137184 --Program ends when Billing Date is passed */
3030 l_report.billing_date := fnd_date.canonical_to_date(P_billing_date);
3031 --Bug 12739341, add a flag to check whether it can generate future date bill
3032 l_report.future_date_bill_flag := p_future_date_bill_flag;
3033 l_report.currency := l_use_currency;
3034 l_report.cust_name_low := P_cust_name_low;
3035 l_report.cust_name_high := P_cust_name_high;
3036 l_report.cust_num_low := P_cust_num_low;
3037 l_report.cust_num_high := P_cust_num_high;
3038 l_report.bill_site_low := P_bill_site_low;
3039 l_report.bill_site_high := P_bill_site_high;
3040 l_report.term_id := P_term_id;
3041 l_report.detail_option := P_detail_option;
3042 l_report.consinv_id_low := P_consinv_id;
3043 l_report.request_id := P_request_id;
3044
3045 IF P_print_option = 'FINAL' THEN
3046 l_report.print_status := 'FINAL';
3047 ELSE
3048 l_report.print_status := 'PENDING';
3049 END IF;
3050
3051 Report(l_report);
3052
3053 ELSE
3054
3055 FOR k in org_rec LOOP
3056
3057 mo_global.set_policy_context('S',k.org_id);
3058 arp_standard.init_standard(p_org_id);
3059
3060 if P_Print_option in ( 'PRINT', 'DRAFT') THEN
3061 l_report.print_option := 'DRAFT';
3062 else
3063 l_report.print_option := P_print_option;
3064 end if;
3065
3066 IF p_currency is NULL THEN
3067 -- default to functional currency
3068 SELECT sob.currency_code
3069 INTO l_use_currency
3070 FROM gl_sets_of_books sob
3071 WHERE sob.set_of_books_id = arp_standard.sysparm.set_of_books_id;
3072 ELSE
3073 l_use_currency := P_currency;
3074 END IF;
3075
3076 l_report.print_output := P_print_output;
3077 l_report.billing_cycle_id := P_billing_cycle_id;
3078
3079 /* Bug 5137184 --Program ends when Billing Date is passed */
3080 l_report.billing_date := fnd_date.canonical_to_date(P_billing_date);
3081 --Bug 12868601, add a flag to check whether it can generate future date bill when the operating unit is null
3082 l_report.future_date_bill_flag := p_future_date_bill_flag;
3083 l_report.currency := l_use_currency;
3084 l_report.cust_name_low := P_cust_name_low;
3085 l_report.cust_name_high := P_cust_name_high;
3086 l_report.cust_num_low := P_cust_num_low;
3087 l_report.cust_num_high := P_cust_num_high;
3088 l_report.bill_site_low := P_bill_site_low;
3089 l_report.bill_site_high := P_bill_site_high;
3090 l_report.term_id := P_term_id;
3091 l_report.detail_option := P_detail_option;
3092 l_report.consinv_id_low := P_consinv_id;
3093 l_report.request_id := P_request_id;
3094 IF P_print_option = 'FINAL' THEN
3095 l_report.print_status := 'FINAL';
3096 ELSE
3097 l_report.print_status := 'PENDING';
3098 END IF;
3099
3100 Report(l_report);
3101
3102 END LOOP;
3103
3104 END IF;
3105
3106 /** LAUNCH THE BPA PRINT PROGRAM **/
3107
3108 write_debug_and_log('p_print_option : '||p_print_option);
3109
3110 IF p_print_option in ('DRAFT','PRINT','FINAL') AND
3111 p_print_output = 'Y' THEN
3112
3113 write_debug_and_log('Submitting call to ARBPBFMP');
3114
3115
3116 l_request_id := FND_REQUEST.SUBMIT_REQUEST(
3117 'AR',
3118 'ARBPBFMP',
3119 null,
3120 null,
3121 FALSE,
3122 to_char(null), -- Operating Unit
3123 to_char(null), -- Job Size
3124 to_char(null), -- Customer Number Low
3125 to_char(null), -- Customer Number High
3126 to_char(null), -- Location Low
3127 to_char(null), -- Location High
3128 to_char(null), -- Bill Date Low
3129 to_char(null), -- Bill Date High
3130 to_char(null), -- Bill Number Low
3131 to_char(null), -- Bill Number High
3132 arp_standard.profile.request_id, -- Conc Request ID
3133 to_char(null));
3134
3135 write_debug_and_log('... request ID is ' || to_char(l_request_id));
3136
3137 END IF;
3138
3139 write_debug_and_log('arp_bf_bill.Report1 (-)');
3140
3141
3142
3143
3144 EXCEPTION
3145 WHEN OTHERS THEN
3146 write_debug_and_log( 'Exception:arp_bf_bill.Report1:'||sqlerrm);
3147 RAISE_APPLICATION_ERROR( -20000, sqlerrm);
3148 END;
3149
3150 END arp_bf_bill;