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