[Home] [Help]
PACKAGE BODY: APPS.ARP_CONSINV
Source
1 PACKAGE BODY arp_consinv AS
2 /* $Header: ARPCBIB.pls 120.27 2005/06/14 18:53:10 vcrisost ship $ */
3
4 /* bug2778646 : Added 'MERGE_PENDING','DRAFT_MERGE' and 'MERGED' status to
5 correspond to customer merge as status of AR_CONS_INV_ALL table.
6
7 MERGE_PENDING -- Not merged yet. Next CBI has to pick up.
8 DRAFT_MERGE -- Merged to new draft CBI. Other CBI cannot get it untill
9 the new draft CBI is rejected.
10 MERGED -- Merged to new accept CBI. Other CBI cannot get it.
11
12 In merge process , ARCMCONB.pls updates customer_id , site_id and status of
13 ar_cons_inv table. Update status to 'MERGED' except latest CBI.
14 The ending_balance of latest CBI should be added to new customer site's CBI.
15 The status of latest CBI is 'MERGE_PENDING'.
16
17 In generic procedure, added up 'MERGE_PENDING' and latest 'ACCEPTED' CBI for
18 beginning_balance.
19
20 If create DRAFT CBI, status is from MERGE_PENDING to DRAFT_MERGE.
21 If accecpt DRAFT CBI, status is from DRAFT_MERGE to MERGED.
22 If reject DRAFT CBI , status is from DRAFT_MERGE to MERGE.
23 */
24
25 /*----------------------------------------------------------------------------*
26 | PROCEDURE |
27 | reprint |
28 | |
29 | DESCRIPTION |
30 | Update rows of consolidated billing invoice or rows associated with |
31 | specified concurrent request id to print status of 'PENDING' so report |
32 | ARXCBI will print them. |
33 | |
34 | SCOPE - PRIVATE |
35 | |
36 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
37 | |
38 | ARGUMENTS : IN: |
39 | P_consinv_id - consolidated billing invoice |
40 | P_request_id - concurrent request id |
41 | OUT: |
42 | None |
43 | |
44 | RETURNS : NONE |
45 | |
46 | NOTES |
47 | |
48 | MODIFICATION HISTORY |
49 | |
50 *----------------------------------------------------------------------------*/
51 PROCEDURE reprint (P_consinv_id IN NUMBER, P_request_id IN NUMBER) IS
52
53 BEGIN
54 UPDATE ar_cons_inv
55 SET print_status = 'PENDING',
56 last_update_date = arp_global.last_update_date,
57 last_updated_by = arp_global.last_updated_by,
58 last_update_login = arp_global.last_update_login
59 WHERE cons_inv_id = nvl(P_consinv_id, cons_inv_id)
60 AND concurrent_request_id = DECODE(P_consinv_id,
61 NULL, P_request_id,
62 concurrent_request_id);
63 EXCEPTION
64 WHEN OTHERS THEN
65 arp_standard.debug( ' Exception: reprint: ');
66 arp_standard.debug( ' P_consinv_id: '||P_consinv_id );
67 arp_standard.debug( ' P_request_id: '||P_request_id );
68 RAISE;
69 END;
70
71 /*----------------------------------------------------------------------------*
72 | PROCEDURE |
73 | accept |
74 | |
75 | DESCRIPTION |
76 | Updates rows for draft versions of consolidated billing invoices to |
77 | status of 'PRINTED', from a prior status of 'DRAFT' |
78 | |
79 | SCOPE - PRIVATE |
80 | |
81 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
82 | |
83 | ARGUMENTS : IN: |
84 | P_consinv_id - Consolidated Billing Invoice id |
85 | P_request_id - Concurrent Request Id associated with |
86 | rows that are to be accepted. |
87 | OUT: |
88 | None |
89 | |
90 | RETURNS : NONE |
91 | |
92 | NOTES |
93 | |
94 | MODIFICATION HISTORY |
95 | |
96 *----------------------------------------------------------------------------*/
97 PROCEDURE accept (P_consinv_id IN NUMBER, P_request_id IN NUMBER) IS
98 -- bug2778646 start
99 TYPE tab_site_use_id IS TABLE OF ar_cons_inv_all.site_use_id%TYPE;
100 TYPE tab_currency_code IS TABLE OF ar_cons_inv_all.currency_code%TYPE;
101 TYPE tab_cut_off_date IS TABLE OF ar_cons_inv_all.cut_off_date%TYPE;
102
103 l_site_use_id tab_site_use_id ;
104 l_currency_code tab_currency_code;
105 l_cut_off_date tab_cut_off_date ;
106
107 CURSOR c_cons_inv IS
108 SELECT site_use_id,
109 currency_code,
110 cut_off_date
111 FROM ar_cons_inv
112 WHERE cons_inv_id = nvl(P_consinv_id, cons_inv_id)
113 AND concurrent_request_id = DECODE(P_consinv_id,
114 NULL, P_request_id,
115 concurrent_request_id)
116 AND status = 'DRAFT' ;
117 -- bug2778646 end
118
119 BEGIN
120
121 -- bug2778646 Added for merged customer's cbi.
122 -- Change status from 'DRAFT_MERGE' to 'MERGED'
123 OPEN c_cons_inv;
124 FETCH c_cons_inv
125 BULK COLLECT INTO
126 l_site_use_id,
127 l_currency_code,
128 l_cut_off_date ;
129
130 FORALL i IN 1..l_site_use_id.count
131 UPDATE ar_cons_inv
132 SET status = 'MERGED',
133 last_update_date = arp_global.last_update_date,
134 last_updated_by = arp_global.last_updated_by,
135 last_update_login = arp_global.last_update_login
136 WHERE status = 'DRAFT_MERGE'
137 AND site_use_id = l_site_use_id(i)
138 AND currency_code = l_currency_code(i)
139 AND cut_off_date <= l_cut_off_date(i) ;
140 -- bug2778646 end
141
142 UPDATE ar_cons_inv
143 SET status = 'ACCEPTED',
144 last_update_date = arp_global.last_update_date,
145 last_updated_by = arp_global.last_updated_by,
146 last_update_login = arp_global.last_update_login
147 WHERE cons_inv_id = nvl(P_consinv_id, cons_inv_id)
148 AND concurrent_request_id = DECODE(P_consinv_id,
149 NULL, P_request_id,
150 concurrent_request_id)
151 AND status = 'DRAFT';
152 EXCEPTION
153 WHEN OTHERS THEN
154 arp_standard.debug ( ' EXCEPTION: accept:' );
155 arp_standard.debug ( ' P_consinv_id: '||P_consinv_id);
156 arp_standard.debug ( ' P_request_id: '||P_request_id);
157 RAISE;
158 END;
159
160 /*----------------------------------------------------------------------------*
161 | PROCEDURE |
162 | reject |
163 | |
164 | DESCRIPTION |
165 | Will delete the consolidated billing invoice or all consolidated |
166 | billing invoices associated with the specified concurrent request id. |
167 | All of the AR tables that have been updated with these consolidated |
168 | billing invoice id's will be updated so that these deleted id's are |
169 | no longer referenced. |
170 | |
171 | SCOPE - PRIVATE |
172 | |
173 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
174 | |
175 | ARGUMENTS : IN: |
176 | P_consinv_id - Consolidated Billing Invoice id |
177 | P_request_id - Concurrent Request Id |
178 | OUT: |
179 | None |
180 | |
181 | RETURNS : NONE |
182 | |
183 | NOTES |
184 | |
185 | MODIFICATION HISTORY |
186 | |
187 | C M Clyde 28 Aug 97 Modified to include transaction types of |
188 | 'XSITE XCURR RECAPP', 'XSITE XCURR RECREV', |
189 | 'XCURR RECAPP', 'XCURR RECREV'. |
190 | |
191 *----------------------------------------------------------------------------*/
192 PROCEDURE reject (P_consinv_id IN NUMBER, P_request_id IN NUMBER) IS
193
194 -- bug2778646 start
195 TYPE tab_site_use_id IS TABLE OF ar_cons_inv_all.site_use_id%TYPE;
196 TYPE tab_currency_code IS TABLE OF ar_cons_inv_all.currency_code%TYPE;
197 TYPE tab_cut_off_date IS TABLE OF ar_cons_inv_all.cut_off_date%TYPE;
198
199 l_site_use_id tab_site_use_id ;
200 l_currency_code tab_currency_code;
201 l_cut_off_date tab_cut_off_date ;
202
203 CURSOR c_cons_inv IS
204 SELECT site_use_id,
205 currency_code,
206 cut_off_date
207 FROM ar_cons_inv
208 WHERE cons_inv_id = nvl(P_consinv_id, cons_inv_id)
209 AND concurrent_request_id = DECODE(P_consinv_id,
210 NULL, P_request_id,
211 concurrent_request_id)
212 AND status = 'DRAFT' ;
213 -- bug2778646 end
214
215 BEGIN
216 UPDATE ra_customer_trx
217 SET printing_original_date =
218 DECODE(printing_count,
219 1, NULL,
220 printing_original_date),
221 printing_last_printed =
222 DECODE(printing_count,
223 1, NULL,
224 printing_last_printed),
225 printing_count = DECODE(printing_count,
226 1, NULL,
227 printing_count - 1)
228 WHERE customer_trx_id IN
229 (SELECT PS.customer_trx_id
230 FROM ar_payment_schedules PS,
231 ar_cons_inv_trx IT,
232 ar_cons_inv CI
233 WHERE IT.transaction_type IN ('INVOICE','CREDIT_MEMO')
234 AND CI.cons_inv_id = IT.cons_inv_id
235 AND CI.cons_inv_id = nvl(P_consinv_id,CI.cons_inv_id)
236 AND CI.concurrent_request_id = DECODE (P_consinv_id,
237 NULL, P_request_id,
238 CI.concurrent_request_id)
239 AND CI.status = 'DRAFT'
240 AND PS.payment_schedule_id = IT.adj_ps_id);
241
242 UPDATE ar_payment_schedules
243 SET cons_inv_id = NULL
244 WHERE payment_schedule_id IN
245 (SELECT IT.adj_ps_id
246 FROM ar_cons_inv CI,
247 ar_cons_inv_trx IT
248 WHERE IT.transaction_type IN ('INVOICE','CREDIT_MEMO',
249 'RECEIPT')
250 AND CI.cons_inv_id = IT.cons_inv_id
251 AND CI.cons_inv_id = nvl(P_consinv_id, CI.cons_inv_id)
252 AND CI.concurrent_request_id = DECODE (P_consinv_id,
253 NULL, P_request_id,
254 CI.concurrent_request_id)
255 AND CI.status = 'DRAFT');
256
257 UPDATE ar_payment_schedules
258 SET cons_inv_id_rev = NULL
259 WHERE payment_schedule_id IN
260 (SELECT IT.adj_ps_id
261 FROM ar_cons_inv CI,
262 ar_cons_inv_trx IT
263 WHERE IT.transaction_type = 'RECEIPT REV'
264 AND CI.cons_inv_id = IT.cons_inv_id
265 AND CI.cons_inv_id = nvl(P_consinv_id, CI.cons_inv_id)
266 AND CI.concurrent_request_id = DECODE(P_consinv_id,
267 NULL, P_request_id,
268 CI.concurrent_request_id)
269 AND CI.status = 'DRAFT');
270
271
272 /* bug2882196 : Added 'EXCLUDE RECREV' and 'EXCLUDE_CMREV' */
273 UPDATE ar_receivable_applications
274 SET cons_inv_id = NULL
275 WHERE receivable_application_id IN
276 (SELECT IT.adj_ps_id
277 FROM ar_cons_inv CI,
278 ar_cons_inv_trx IT
279 WHERE IT.transaction_type IN ('XSITE RECREV', 'XSITE_CMREV',
280 'XCURR RECREV', 'XSITE XCURR RECREV',
281 'EXCLUDE RECREV', 'EXCLUDE_CMREV')
282 AND CI.cons_inv_id = IT.cons_inv_id
283 AND CI.cons_inv_id = nvl(P_consinv_id, CI.cons_inv_id)
284 AND CI.concurrent_request_id = DECODE(P_consinv_id,
285 NULL, P_request_id,
286 CI.concurrent_request_id)
287 AND CI.status = 'DRAFT');
288
289 /* bug2882196 : Added 'EXCLUDE RECAPP' and 'EXCLUDE_CMAPP' */
290 UPDATE ar_receivable_applications
291 SET cons_inv_id_to = NULL
292 WHERE receivable_application_id IN
293 (SELECT IT.adj_ps_id
294 FROM ar_cons_inv CI,
295 ar_cons_inv_trx IT
296 WHERE IT.transaction_type IN ('XSITE RECAPP','XSITE_CMAPP',
297 'XCURR RECAPP', 'XSITE XCURR RECAPP' ,
298 'EXCLUDE RECAPP', 'EXCLUDE_CMAPP')
299 AND CI.cons_inv_id = IT.cons_inv_id
300 AND CI.cons_inv_id = nvl(P_consinv_id, CI.cons_inv_id)
301 AND CI.concurrent_request_id = DECODE(P_consinv_id,
305
302 NULL, P_request_id,
303 CI.concurrent_request_id)
304 AND CI.status = 'DRAFT');
306 UPDATE ar_adjustments
307 SET cons_inv_id = NULL
308 WHERE adjustment_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 = 'ADJUSTMENT'
313 AND CI.cons_inv_id = IT.cons_inv_id
314 AND CI.cons_inv_id = nvl(P_consinv_id, CI.cons_inv_id)
315 AND CI.concurrent_request_id = DECODE (P_consinv_id,
316 NULL, P_request_id,
317 CI.concurrent_request_id)
318 AND CI.status = 'DRAFT');
319
320 -- bug2778646 Added for merged customer's cbi.
321 -- Changed status from 'DRAFT_MERGE' to 'MERGE_PENDING'
322 OPEN c_cons_inv;
323 FETCH c_cons_inv
324 BULK COLLECT INTO
325 l_site_use_id,
326 l_currency_code,
327 l_cut_off_date ;
328
329 FORALL i IN 1..l_site_use_id.count
330 UPDATE ar_cons_inv
331 SET status = 'MERGE_PENDING',
332 last_update_date = arp_global.last_update_date,
333 last_updated_by = arp_global.last_updated_by,
334 last_update_login = arp_global.last_update_login
335 WHERE status = 'DRAFT_MERGE'
336 AND site_use_id = l_site_use_id(i)
337 AND currency_code = l_currency_code(i)
338 AND cut_off_date <= l_cut_off_date(i) ;
339 -- bug2778646 end
340
341 DELETE FROM ar_cons_inv_trx_lines
342 WHERE cons_inv_id IN
343 (SELECT CI.cons_inv_id
344 FROM ar_cons_inv CI
345 WHERE CI.cons_inv_id = nvl(P_consinv_id, CI.cons_inv_id)
346 AND CI.concurrent_request_id = DECODE (P_consinv_id,
347 NULL, P_request_id,
348 CI.concurrent_request_id)
349 AND CI.status = 'DRAFT');
350
351 DELETE FROM ar_cons_inv_trx
352 WHERE cons_inv_id IN
353 (SELECT CI.cons_inv_id
354 FROM ar_cons_inv CI
355 WHERE CI.cons_inv_id = nvl(P_consinv_id, CI.cons_inv_id)
356 AND CI.concurrent_request_id = DECODE (P_consinv_id,
357 NULL, P_request_id,
358 CI.concurrent_request_id)
359 AND CI.status = 'DRAFT');
360
361 UPDATE ar_cons_inv
362 SET status = 'REJECTED',
363 print_status = 'PRINTED'
364 WHERE cons_inv_id = nvl(P_consinv_id, cons_inv_id)
365 AND concurrent_request_id = DECODE(P_consinv_id,
366 NULL, P_request_id,
367 concurrent_request_id)
368 AND status = 'DRAFT';
369
370 EXCEPTION
371 WHEN OTHERS THEN
372 arp_standard.debug( ' Exception: reject: ');
373 arp_standard.debug( 'P_consinv_id: '||P_consinv_id);
374 arp_standard.debug( 'P_request_id: '||P_request_id);
375 RAISE;
376 END;
377
378 /*----------------------------------------------------------------------------*
379 | PROCEDURE |
380 | generate |
381 | |
382 | DESCRIPTION |
383 | Will create new Consolidated Billing Invoices for the specified user |
384 | criteria. It can either be in 'DRAFT' or 'PRINT'. |
385 | |
386 | SCOPE - PRIVATE |
387 | |
388 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
389 | |
390 | ARGUMENTS : IN: |
391 | P_print_option - 'DRAFT' or 'PRINT' |
392 | P_detail_option - 'DETAIL' or 'SUMMARY' |
393 | P_currency - Currency Code |
394 | P_customer_id - Customer id |
395 | P_customer_number - Customer number |
396 | P_bill_to_site - Bill-to Site |
397 | P_cutoff_date - Cut-off Date |
398 | P_term_id - Payment Terms id |
399 | : OUT: |
400 | None |
401 | |
402 | RETURNS : NONE |
406 | MODIFICATION HISTORY |
403 | |
404 | NOTES |
405 | |
407 | 05-AUG-97 Jack Martinez bug 499781: |
408 | insert into ar_cons_inv_trx for type |
409 | 'XSITE RECAPP' should not negate amount|
410 | 06-AUG-97 Jack Martinez bug 522890: |
411 | ignore guarantees when collecting |
412 | adjustments. When an invoice is |
413 | applied against a guarantee, a row is |
414 | created in ar_adjustments and is |
415 | applied against the payment schedule of|
416 | the guarantee. When gathering adjust- |
417 | ments, ignore if the class of the |
418 | related payment schedule is 'GUAR'. |
419 | 22-AUG-97 Jack Martinez bug 531330: |
420 | patch 499781 incorrect. 'XSITE RECAPP' |
421 | should be negated. and 'XSITE RECREV' |
422 | is not negated. |
423 | 27-AUG-97 Jack Martinez bug 536361: |
424 | amounts for credit memo should not be |
425 | negated. |
426 | 28-AUG-97 C M Clyde Cross Currency functionality |
427 | Modified to include transaction types |
428 | of 'XSITE XCURR RECAPP', |
429 | 'XSITE XCURR RECREV', 'XCURR RECAPP', |
430 | 'XCURR RECREV'. |
431 | 01-JUN-99 Frank Breslin 889478: Replaced the check to the terms|
432 | in the C_SITES cursor against the terms|
433 | parameter because we were losing the |
434 | ability to only select customer sites |
435 | with the given term. |
436 | 08-JUL-99 Frank Breslin 857820: Implement the use of Last Day |
437 | of Month type terms. |
438 | 23-JUL-99 Frank Breslin 940744: Terms check in C_SITES was |
439 | causing a problem when there was no |
440 | term defined at the Bill To Site level.|
441 | 25-AUG-99 Frank Breslin 919100: Modifed the cursor C_SITES in |
442 | generate to specifically exclude |
443 | Bill To sites with a terms code that |
444 | does not have a day of month / months |
445 | ahead type due day. |
446 | 27-SEP-99 Frank Breslin 1006767: Changed all occurance of |
447 | PS.class to PS.class||'' in the WHERE |
448 | clause of SQL in the generate function |
449 | in order to supress the use of index |
450 | AR_PAYMENT_SCHEDULES_N11. |
451 | 12-DEC-01 Hiroshi Yoshiahra 2134375: Added "+1" to C_cutoff_date |
452 | of c_inv_trx cursor in generate |
453 | procedure when P_last_day_of_month |
454 | flag is 'Y' and C_cutoff_date is last |
455 | day of month. |
456 | 06-SEP-02 Hiroshi Yoshiahra 2501071: Created c_types cursor to |
457 | fix cartesian join of c_sites cursor. |
458 | 07-NOV-02 Hiroshi Yoshiahra 2656229: Added codition to c_sites cursor
459 | 19-Nov-02 Sahana 2650786: Corrected a typo in |
460 | Consolidated Bill Transaction Types. |
461 | Used XCURR RECREV and XSITE XCURR RECREV
462 | instead of XCURR RECREC and XSITE |
463 | XCURR RECREC in the update statement for
464 | ar_receivable_applications table |
465 | 13-Dec-02 Sahana Shetty Bug2677085: Period receipt amounts |
466 | were calculated incorrectly when |
467 | receipt location was filled in after |
468 | applications were made to invoices. |
469 | 25-DEC-02 Hiroshi Yoshiahra 2700662: Removed link to ra_customer_trx
470 | table from sub-query of two insert stmts,
471 | one is for XSITE_CMREV,other is for |
472 | XSITE_CMAPP. |
473 | 09-JUN-05 V Crisostomo Bug 4367354: SSA, add org_id to inserts|
474 *----------------------------------------------------------------------------*/
475 PROCEDURE generate (P_print_option IN VARCHAR2,
479 P_customer_number IN VARCHAR2,
476 P_detail_option IN VARCHAR2,
477 P_currency IN VARCHAR2,
478 P_customer_id IN NUMBER,
480 P_bill_to_site IN NUMBER,
481 P_cutoff_date IN DATE,
482 P_last_day_of_month IN VARCHAR2,
483 P_term_id IN NUMBER) IS
484 l_cutoff_day NUMBER(15);
485 l_beginning_balance NUMBER;
486 l_consinv_id NUMBER;
487 l_consinv_lineno NUMBER(15);
488 l_cons_billno VARCHAR2(30);
489 l_new_billed NUMBER;
490 l_period_receipts NUMBER;
491 l_due_date DATE;
492 l_due_last_day_of_month DATE;
493 -- bug2434295
494 l_real_cutoff_date DATE;
495
496 -- bug2501071 : Created to fix cartesian join of c_sites
497 CURSOR C_types (C_cutoff_day NUMBER, C_term_id NUMBER) IS
498 SELECT T.term_id term_id ,
499 TL1.due_day_of_month day_due,
500 TL1.due_months_forward months_forward
501 FROM ra_terms T,
502 ra_terms_lines TL1
503 WHERE TL1.term_id = T.term_id
504 AND T.term_id = nvl(C_term_id, T.term_id)
505 AND T.due_cutoff_day = C_cutoff_day
506 AND TL1.due_day_of_month IS NOT NULL
507 AND TL1.due_months_forward IS NOT NULL
508 AND 1 = (select count(*)
509 from ra_terms_lines TL2
510 where TL2.term_id = TL1.term_id) ;
511
512 /* bug2892106 Broke up this stmt into 3 stmt based on parameter.
513 -- bug2501071 : Moved ra_terms/(_lines) to c_types in order to
514 -- fix cartesian join
515 -- bug2656229 : Added NOT EXISTS condition to prevent from data corruption
516 CURSOR C_sites (C_detail_option VARCHAR, C_customer_id NUMBER,
517 C_site_use_id NUMBER, C_cutoff_date DATE,
518 C_term_id NUMBER ) IS
519 SELECT
520 CP.cust_account_id customer_id,
521 site_uses.site_use_id site_id,
522 acct_site.cust_acct_site_id,
523 nvl(SP.cons_inv_type,
524 nvl(CP.cons_inv_type,'SUMMARY')) cons_inv_type
525 FROM
526 hz_cust_accounts cust_acct,
527 hz_customer_profiles CP,
528 hz_customer_profiles SP,
529 hz_cust_site_uses site_uses,
530 hz_cust_acct_sites acct_site
531 WHERE
532 site_uses.site_use_code = 'BILL_TO'
533 AND site_uses.site_use_id = nvl(C_site_use_id, site_uses.site_use_id)
534 AND SP.site_use_id(+) = site_uses.site_use_id
535 AND acct_site.cust_acct_site_id = site_uses.cust_acct_site_id
536 AND cust_acct.cust_account_id = acct_site.cust_account_id
537 AND cust_acct.account_number = nvl(P_customer_number,cust_acct.account_number)
538 AND CP.cust_account_id = cust_acct.cust_account_id
539 AND CP.site_use_id IS NULL
540 AND C_term_id = nvl(site_uses.payment_term_id,
541 nvl(SP.standard_terms,
542 CP.standard_terms))
543 AND nvl(SP.cons_inv_flag,
544 CP.cons_inv_flag) = 'Y'
545 AND nvl(SP.cons_inv_type,
546 nvl(CP.cons_inv_type,
547 'SUMMARY')) = C_detail_option
548 AND NOT EXISTS
549 (SELECT NULL
550 FROM ar_cons_inv CI
551 WHERE CI.site_use_id = site_uses.site_use_id
552 AND CI.cut_off_date = to_date(C_cutoff_date)
553 AND CI.currency_code = P_currency
554 AND CI.status <> 'REJECTED')
555 AND NOT EXISTS
556 (SELECT NULL
557 FROM ar_cons_inv CI2
558 WHERE CI2.site_use_id = site_uses.site_use_id
559 AND CI2.currency_code = P_currency
560 AND CI2.status = 'DRAFT') ;
561 */
562
563 CURSOR C_inv_trx (C_site_use_id NUMBER, C_cutoff_date DATE) IS
564 SELECT
565 CT.customer_trx_id trx_id,
566 CT.trx_date trx_date,
567 CT.trx_number trx_number,
568 PS.class class,
569 PS.payment_schedule_id schedule_id,
570 PS.amount_due_original amount_due,
571 PS.tax_original tax,
572 PS.invoice_currency_code currency
573 FROM
574 ra_customer_trx CT,
575 ar_payment_schedules PS
576 WHERE
577 PS.customer_site_use_id = C_site_use_id
578 AND PS.cons_inv_id IS NULL
579 AND PS.invoice_currency_code = P_currency
580 AND CT.customer_trx_id = PS.customer_trx_id
581 AND CT.trx_date < C_cutoff_date
582 /* bug2434295 C_cutoff_date was already calculated.
583 + decode(C_last_day_of_month, 'Y',
584 decode(C_cutoff_date, Last_day(C_cutoff_date),
585 1 , 0) , 0 ) -- bug2134375
586 */
587 AND PS.class||'' IN ('INV', 'DM', 'CM', 'DEP', 'CB')
588 AND nvl(PS.exclude_from_cons_bill_flag, 'N') <> 'Y' -- bug2882196
589 ORDER BY PS.trx_date, PS.customer_trx_id;
590
594
591 -- bug2892106 Added new cursor variable
592 TYPE c_sites_type IS REF CURSOR ;
593 C_sites C_sites_type ;
595 -- bug2892106 Added new record variable because cannot use cursor
596 -- variable in FOR LOOP.
597 TYPE L_sites_type IS RECORD
598 ( customer_id NUMBER ,
599 site_id NUMBER );
600 L_sites L_sites_type ;
601
602 -- bug2892106 Removed cons_inv_type column from select stmt because
603 -- it must be same value with P_detal_option.
604 C_detail_option hz_customer_profiles.cons_inv_type%TYPE;
605
606 -- bug3039537
607 -- Calculate all tax amount and inclusive tax amount
608 TYPE tab_line_id IS TABLE OF ra_customer_trx_lines_all.link_to_cust_trx_line_id%TYPE;
609 TYPE tab_num IS TABLE OF NUMBER ;
610
611 l_line_id tab_line_id ;
612 l_tax_sum tab_num ;
613 l_include_tax_sum tab_num ;
614
615 l_bulk_fetch_rows NUMBER := 10000 ;
616
617 CURSOR c_tax (l_trx_id NUMBER)
618 IS
619 SELECT link_to_cust_trx_line_id,
620 sum(nvl(CTL.extended_amount,0)),
621 sum(decode(amount_includes_tax_flag, 'Y', nvl(CTL.extended_amount,0),0))
622 FROM ra_customer_trx_lines CTL
623 WHERE CTL.customer_trx_id = l_trx_id
624 AND CTL.line_type = 'TAX'
625 GROUP BY link_to_cust_trx_line_id;
626 -- bug3039537
627
628 BEGIN
629
630 -- bug2892106
631 C_detail_option := nvl(P_detail_option, 'SUMMARY') ;
632
633 /* Use for debugging...
634 dbms_output.put_line('And so it begins...');
635 dbms_output.put_line('P_print_option : '||P_print_option);
636 dbms_output.put_line('P_detail_option : '||P_detail_option);
637 dbms_output.put_line('P_currency : '||P_currency);
638 dbms_output.put_line('P_customer_id : '||TO_CHAR(P_customer_id));
639 dbms_output.put_line('P_customer_number : '||P_customer_number);
640 dbms_output.put_line('P_bill_to_site : '||TO_CHAR(p_bill_to_site));
641 dbms_output.put_line('P_cutoff_date : '||TO_CHAR(P_cutoff_date));
642 dbms_output.put_line('P_last_day_of_month: '||P_last_day_of_month);
643 dbms_output.put_line('P_term_id : '||TO_CHAR(P_term_id));
644 */
645
646 /** need day of month of cut-off date to match against ra_terms. **/
647 l_cutoff_day := P_cutoff_date -
648 trunc(P_cutoff_date,'MONTH') + 1;
649 /*
650 857820: If P_last_day_of_month = 'Y' then use last day of month type terms
651 */
652
653 if P_last_day_of_month = 'Y' then
654 l_cutoff_day := 32;
655
656 /* 2434295 start
657 P_cutoff_date is not real cutoff date when p_last_day_of_month
658 is 'Y' and P_cutoff_date is last day of month. In this case,
659 should add 1 to P_cutoff_date for selecting invoices , adjustments
660 and receipts.
661 */
662 if P_cutoff_date = Last_day(P_cutoff_date) then
663 l_real_cutoff_date := P_cutoff_date + 1 ;
664 else
665 l_real_cutoff_date := P_cutoff_date;
666 end if;
667
668 else
669 l_real_cutoff_date := P_cutoff_date;
670
671 /* bug2434295 end */
672
673 end if;
674
675 /* Use for debugging
676 dbms_output.put_line('Parameters for L_sites cursor open...');
677 dbms_output.put_line('P_detail_option :'||P_detail_option);
678 dbms_output.put_line('P_customer_id :'||TO_CHAR(P_customer_id));
679 dbms_output.put_line('P_bill_to_site :'||TO_CHAR(P_bill_to_site));
680 dbms_output.put_line('P_cutoff_date :'||TO_CHAR(P_cutoff_date));
681 dbms_output.put_line('L_cutoff_day :'||TO_CHAR(l_cutoff_day));
682 dbms_output.put_line('P_term_id :'||TO_CHAR(P_term_id));
683 */
684
685 -- bug2501071 : Added c_types LOOP
686 FOR L_types IN C_types(l_cutoff_day, p_term_id ) LOOP
687
688 /* bug2892106 Removed
689 FOR L_sites IN C_sites(P_detail_option, P_customer_id,
690 P_bill_to_site, P_cutoff_date,
691 L_types.term_id) LOOP
692 */
693
694 -- bug2892106 These are 3 stmt instead of previous c_sites cursor
695 IF P_customer_id is not null THEN
696
697 IF P_bill_to_site is not null THEN
698
699 -- with customer and site id
700 OPEN C_sites FOR
701 SELECT /*+ ORDERED */
702 P_customer_id customer_id ,
703 P_bill_to_site site_id
704 FROM
705 hz_cust_site_uses site_uses,
706 hz_customer_profiles CP,
707 hz_customer_profiles SP
708 WHERE
709 site_uses.site_use_id = P_bill_to_site
710 AND CP.cust_account_id = P_customer_id
711 AND CP.site_use_id IS NULL
712 AND SP.site_use_id(+) = site_uses.site_use_id
713 AND L_types.term_id = nvl(site_uses.payment_term_id,
714 nvl(SP.standard_terms,CP.standard_terms))
715 AND nvl(SP.cons_inv_flag, CP.cons_inv_flag) = 'Y'
716 AND nvl(nvl(SP.cons_inv_type,CP.cons_inv_type),'SUMMARY')
717 = C_detail_option
718 AND NOT EXISTS
722 -- bug3129948 added '>'
719 (SELECT NULL
720 FROM ar_cons_inv CI
721 WHERE CI.site_use_id = site_uses.site_use_id
723 AND CI.cut_off_date >=P_cutoff_date
724 AND CI.currency_code = P_currency
725 AND CI.status <> 'REJECTED')
726 AND NOT EXISTS
727 (SELECT NULL
728 FROM ar_cons_inv CI2
729 WHERE CI2.site_use_id = site_uses.site_use_id
730 AND CI2.currency_code = P_currency
731 AND CI2.status = 'DRAFT') ;
732 ELSE
733
734 -- with customer id only
735 OPEN C_sites FOR
736 SELECT /*+ ORDERED */
737 P_customer_id customer_id ,
738 site_uses.site_use_id site_id
739 FROM
740 hz_cust_acct_sites acct_site,
741 hz_cust_site_uses site_uses,
742 hz_customer_profiles CP,
743 hz_customer_profiles SP
744 WHERE
745 acct_site.cust_account_id = P_customer_id
746 AND site_uses.cust_acct_site_id = acct_site.cust_acct_site_id
747 AND site_uses.site_use_code = 'BILL_TO'
748 AND CP.cust_account_id = P_customer_id
749 AND CP.site_use_id IS NULL
750 AND SP.site_use_id(+) = site_uses.site_use_id
751 AND L_types.term_id = nvl(site_uses.payment_term_id,
752 nvl(SP.standard_terms,CP.standard_terms))
753 AND nvl(SP.cons_inv_flag, CP.cons_inv_flag) = 'Y'
754 AND nvl(nvl(SP.cons_inv_type,CP.cons_inv_type),'SUMMARY')
755 = C_detail_option
756 AND NOT EXISTS
757 (SELECT NULL
758 FROM ar_cons_inv CI
759 WHERE CI.site_use_id = site_uses.site_use_id
760 -- bug3129948 added '>'
761 AND CI.cut_off_date >= P_cutoff_date
762 AND CI.currency_code = P_currency
763 AND CI.status <> 'REJECTED')
764 AND NOT EXISTS
765 (SELECT NULL
766 FROM ar_cons_inv CI2
767 WHERE CI2.site_use_id = site_uses.site_use_id
768 AND CI2.currency_code = P_currency
769 AND CI2.status = 'DRAFT') ;
770 END IF ;
771
772 ELSE
773
774 -- without customer and site id
775 OPEN C_sites FOR
776 SELECT
777 acct_site.cust_account_id customer_id ,
778 site_uses.site_use_id site_id
779 FROM
780 hz_cust_acct_sites acct_site,
781 hz_cust_site_uses site_uses,
782 hz_customer_profiles CP,
783 hz_customer_profiles SP
784 WHERE
785 site_uses.cust_acct_site_id = acct_site.cust_acct_site_id
786 AND site_uses.site_use_code = 'BILL_TO'
787 AND CP.cust_account_id = acct_site.cust_account_id
788 AND CP.site_use_id IS NULL
789 AND SP.site_use_id(+) = site_uses.site_use_id
790 AND L_types.term_id = nvl(site_uses.payment_term_id,
791 nvl(SP.standard_terms,CP.standard_terms))
792 AND nvl(SP.cons_inv_flag, CP.cons_inv_flag) = 'Y'
793 AND nvl(nvl(SP.cons_inv_type,CP.cons_inv_type),'SUMMARY')
794 = C_detail_option
795 AND NOT EXISTS
796 (SELECT NULL
797 FROM ar_cons_inv CI
798 WHERE CI.site_use_id = site_uses.site_use_id
799 -- bug3129948 added '>'
800 AND CI.cut_off_date >= P_cutoff_date
801 AND CI.currency_code = P_currency
802 AND CI.status <> 'REJECTED')
803 AND NOT EXISTS
804 (SELECT NULL
805 FROM ar_cons_inv CI2
806 WHERE CI2.site_use_id = site_uses.site_use_id
807 AND CI2.currency_code = P_currency
808 AND CI2.status = 'DRAFT') ;
809 END IF;
810
811 LOOP
812 FETCH C_sites INTO L_sites;
813 EXIT WHEN C_sites%NOTFOUND;
814 -- bug2892106
815
816 /* Use for debugging ...
817 dbms_output.put_line(' ');
818 dbms_output.put_line('Process a row from cursor C_SITES');
819 dbms_output.put_line('customer_id : '||TO_CHAR(L_SITES.customer_id));
820 dbms_output.put_line('site_id : '||TO_CHAR(L_SITES.site_id));
821 dbms_output.put_line('term_id : '||TO_CHAR(L_SITES.term_id));
822 dbms_output.put_line('address_id : '||TO_CHAR(L_SITES.cust_acct_site_id));
823 dbms_output.put_line('cons_inv_type : '||L_SITES.cons_inv_type);
824 dbms_output.put_line('day_due : '||TO_CHAR(L_SITES.day_due));
825 dbms_output.put_line('months_forward : '||TO_CHAR(L_SITES.months_forward));
826 */
827
828 /** For site: process invoices first, add invoice detail. **/
829
830 l_consinv_lineno := 1;
831
835 l_cons_billno := to_char(l_consinv_id);
832 /** For Site: get next billing invoice id, create header with zero totals.**/
833
834 SELECT ar_cons_inv_s.NEXTVAL INTO l_consinv_id FROM dual;
836
837 /** calculate due date **/
838
839 l_due_date := add_months(trunc(P_cutoff_date,'month'),
840 nvl(L_types.months_forward,0))+L_types.day_due-1;
841
842 /** if the due day is 29 or greater, it is possible that the due month does
843 not have that many days, so will need to use last day of month instead **/
844
845 l_due_last_day_of_month :=
846 add_months(trunc(P_cutoff_date,'month'),
847 nvl(L_types.months_forward,0)+1)-1;
848
849 IF l_due_date > l_due_last_day_of_month
850 THEN l_due_date := l_due_last_day_of_month;
851 END IF;
852
853 /** get beginning balance for new billing invoice from prior billing invoice**/
854 /** bug 632412: do not use term_id in where clause and subquery in case the **/
855 /** terms code was changed for the site. **/
856
857 BEGIN
858
859 /* bug2778646 Modified this select stmt to get balance of merged cbi.
860 SELECT sum(ending_balance)
861 INTO l_beginning_balance
862 FROM ar_cons_inv CI1
863 WHERE CI1.site_use_id = L_sites.site_id
864 AND CI1.currency_code = P_currency
865 AND CI1.status <> 'REJECTED'
866 AND CI1.cut_off_date =
867 (SELECT max(CI2.cut_off_date)
868 FROM ar_cons_inv CI2
869 WHERE CI2.site_use_id = L_sites.site_id
870 AND CI2.currency_code = P_currency
871 AND CI2.cut_off_date < P_cutoff_date
872 AND CI2.status <> 'REJECTED');
873 */
874
875 SELECT sum(ending_balance)
876 INTO l_beginning_balance
877 FROM ar_cons_inv CI1
878 WHERE CI1.site_use_id = L_sites.site_id
879 AND CI1.currency_code = P_currency
880 AND ((CI1.status = 'ACCEPTED'
881 AND CI1.cut_off_date =
882 (SELECT max(CI2.cut_off_date)
883 FROM ar_cons_inv CI2
884 WHERE CI2.site_use_id = L_sites.site_id
885 AND CI2.currency_code = P_currency
886 AND CI2.cut_off_date < P_cutoff_date
887 AND CI2.status = 'ACCEPTED'))
888 OR (CI1.status = 'MERGE_PENDING'
889 AND CI1.cut_off_date <= P_cutoff_date) );
890
891 EXCEPTION
892 WHEN NO_DATA_FOUND THEN
893 l_beginning_balance := 0;
894 END;
895
896 /** For Site: create header. **/
897 /** note it is possible that only the header will created if no **/
898 /** transactions are found. **/
899 INSERT INTO ar_cons_inv (cons_inv_id,
900 cons_billing_number,
901 customer_id,
902 site_use_id,
903 concurrent_request_id,
904 last_update_date,
905 last_updated_by,
906 creation_date,
907 created_by,
908 last_update_login,
909 cons_inv_type,
910 status,
911 print_status,
912 term_id,
913 issue_date,
914 cut_off_date,
915 due_date,
916 currency_code,
917 beginning_balance,
918 ending_balance,
919 org_id)
920 VALUES (l_consinv_id,
921 l_cons_billno,
922 L_sites.customer_id,
923 L_sites.site_id,
924 arp_standard.profile.request_id,
925 arp_global.last_update_date,
926 arp_global.last_updated_by,
927 arp_global.creation_date,
928 arp_global.created_by,
929 arp_global.last_update_login,
930 C_detail_option,
931 DECODE(P_print_option,
932 'DRAFT', 'DRAFT',
933 'ACCEPTED'),
934 'PENDING',
935 L_types.term_id,
936 sysdate,
937 P_cutoff_date,
938 l_due_date,
939 P_currency,
940 nvl(l_beginning_balance,0),
941 0,
945 /** 536361 - do not negate credit memo amounts. **/
942 arp_standard.sysparm.org_id);
943
944 /** For Site: process invoices, credit memos. Need loop to assign line no. **/
946 l_consinv_lineno := 1;
947
948 /* Use for debugging
949 dbms_output.put_line('Parameters to cursor C_INV_TRX...');
950 dbms_output.put_line('L_sites.site_id :'||TO_CHAR(L_sites.site_id));
951 dbms_output.put_line('P_cutoff_date :'||TO_CHAR(P_cutoff_date));
952 */
953 /* bug2134375 Added P_last_day_of_month argument */
954 /* bug2434295 Removed P_last_day_of_month argument
955 Changed P_cutoff_date to l_real_cutoff_date */
956 FOR L_inv_trx IN C_inv_trx(L_sites.site_id, l_real_cutoff_date ) LOOP
957 /* Use for debugging
958 dbms_output.put_line('process a row from CURSOR C_INV_TRX...');
959 dbms_output.put_line('trx_id :'||TO_CHAR(l_inv_trx.trx_id));
960 dbms_output.put_line('trx_date :'||TO_CHAR(l_inv_trx.trx_date));
961 dbms_output.put_line('trx_number :'||l_inv_trx.trx_number);
962 */
963 INSERT INTO ar_cons_inv_trx (cons_inv_id,
964 transaction_type,
965 trx_number,
966 transaction_date,
967 amount_original,
968 tax_original,
969 adj_ps_id,
970 cons_inv_line_number,
971 org_id)
972 VALUES (l_consinv_id,
973 DECODE(L_inv_trx.class,
974 'CM','CREDIT_MEMO',
975 'INVOICE'),
976 L_inv_trx.trx_number,
977 L_inv_trx.trx_date,
978 L_inv_trx.amount_due,
979 L_inv_trx.tax,
980 L_inv_trx.schedule_id,
981 l_consinv_lineno,
982 arp_standard.sysparm.org_id);
983
984 /** For audit purposes, insert detail line information even if reporting **/
985 /** in summary. **/
986 /** also note that cons_inv_line_number is one value for detail lines for
987 /** a specific invoice. **/
988
989 /* Bug 586099: For credit memo, quantity is stored in
990 quantity_credited rather than quantity_invoiced. */
991 IF (L_inv_trx.class = 'CM') THEN
992 INSERT INTO ar_cons_inv_trx_lines (cons_inv_id,
993 cons_inv_line_number,
994 customer_trx_id,
995 customer_trx_line_id,
996 line_number,
997 inventory_item_id,
998 description,
999 uom_code,
1000 quantity_invoiced,
1001 unit_selling_price,
1002 extended_amount,
1003 tax_amount,
1004 org_id)
1005 SELECT
1006 l_consinv_id,
1007 l_consinv_lineno,
1008 customer_trx_id,
1009 customer_trx_line_id,
1010 line_number,
1011 inventory_item_id,
1012 description,
1013 uom_code,
1014 quantity_credited,
1015 nvl (gross_unit_selling_price, unit_selling_price),
1016 nvl (gross_extended_amount, extended_amount),
1017 0,
1018 org_id
1019 FROM
1020 ra_customer_trx_lines
1021 WHERE
1022 customer_trx_id = L_inv_trx.trx_id
1023 AND line_type NOT IN ('TAX', 'FREIGHT');
1024
1025 ELSE
1026 INSERT INTO ar_cons_inv_trx_lines (cons_inv_id,
1027 cons_inv_line_number,
1028 customer_trx_id,
1029 customer_trx_line_id,
1030 line_number,
1031 inventory_item_id,
1032 description,
1033 uom_code,
1034 quantity_invoiced,
1035 unit_selling_price,
1036 extended_amount,
1037 tax_amount,
1038 org_id)
1039 SELECT
1040 l_consinv_id,
1041 l_consinv_lineno,
1042 customer_trx_id,
1043 customer_trx_line_id,
1044 line_number,
1045 inventory_item_id,
1046 description,
1047 uom_code,
1048 quantity_invoiced,
1052 org_id
1049 nvl (gross_unit_selling_price, unit_selling_price),
1050 nvl (gross_extended_amount, extended_amount),
1051 0,
1053 FROM
1054 ra_customer_trx_lines
1055 WHERE
1056 customer_trx_id = L_inv_trx.trx_id
1057 AND line_type NOT IN ('TAX', 'FREIGHT');
1058 END IF;
1059
1060 /** now update lines with associated tax line **/
1061 /* bug3039537 : Removed
1062 UPDATE ar_cons_inv_trx_lines TL
1063 set TL.tax_amount =
1064 (SELECT sum(nvl(CTL.extended_amount,0))
1065 FROM ra_customer_trx_lines CTL
1066 WHERE CTL.link_to_cust_trx_line_id =
1067 TL.customer_trx_line_id
1068 AND CTL.line_type = 'TAX')
1069 WHERE
1070 TL.customer_trx_id = L_inv_trx.trx_id;
1071 */
1072
1073 -- bug3039537
1074 -- Get all tax total amount and inclusive tax total amount
1075 OPEN c_tax(L_inv_trx.trx_id);
1076 LOOP
1077 FETCH c_tax BULK COLLECT INTO
1078 l_line_id , l_tax_sum, l_include_tax_sum LIMIT l_bulk_fetch_rows;
1079
1080 -- 1. Update tax_amount
1081 -- 2. Exclude inclusive tax amount total from extended_amount
1082 FORALL i IN 1..l_line_id.count
1083 UPDATE ar_cons_inv_trx_lines
1084 SET tax_amount = l_tax_sum(i),
1085 extended_amount = extended_amount - l_include_tax_sum(i)
1086 WHERE customer_trx_id = L_inv_trx.trx_id /*4413567*/
1087 AND
1088 customer_trx_line_id = l_line_id(i) ;
1089
1090 EXIT WHEN c_tax%NOTFOUND ;
1091 END LOOP;
1092 CLOSE c_tax;
1093
1094 /** now create 1 summary row for freight **/
1095 INSERT INTO ar_cons_inv_trx_lines (cons_inv_id,
1096 cons_inv_line_number,
1097 customer_trx_id,
1098 customer_trx_line_id,
1099 line_number,
1100 inventory_item_id,
1101 description,
1102 uom_code,
1103 quantity_invoiced,
1104 unit_selling_price,
1105 extended_amount,
1106 tax_amount,
1107 org_id)
1108 SELECT
1109 l_consinv_id,
1110 l_consinv_lineno,
1111 max(customer_trx_id),
1112 max(customer_trx_line_id),
1113 max(line_number),
1114 NULL,
1115 'Freight',
1116 NULL,
1117 1,
1118 sum (nvl (gross_extended_amount, extended_amount)),
1119 sum (nvl (gross_extended_amount, extended_amount)),
1120 0,
1121 org_id
1122 FROM
1123 ra_customer_trx_lines
1124 WHERE
1125 customer_trx_id = L_inv_trx.trx_id
1126 AND line_type = 'FREIGHT'
1127 GROUP BY line_type,org_id;
1128
1129 l_consinv_lineno := l_consinv_lineno + 1;
1130
1131 END LOOP;
1132
1133 /** For site: adjustments **/
1134 /** bug 522890 - ignore guarantees. When an invoice is applied against a **/
1135 /** guarantee, an adjustment row is created and is applied **/
1136 /** against the payment schedule of the guarantee. When **/
1137 /** gathering adjustments, check the class of the related **/
1138 /** payment schedule and omit if class = 'GUAR'. **/
1139
1140 /*
1141 1357024 fbreslin put AR_ADJUSTMENTS.tax_adjusted into AR_CONS_INV.TAX_ORIGINAL
1142 */
1143
1144 /*
1145 1340426 fbreslin: Only include approved adjustments
1146 */
1147
1148 /* bug2882196 : Added exclude_from_cons_bill_flag condition not to get legacy
1149 transactions. */
1150 /* bug2922922 : Added hint */
1151 INSERT INTO ar_cons_inv_trx (cons_inv_id,
1152 transaction_type,
1153 trx_number,
1154 transaction_date,
1155 amount_original,
1156 tax_original,
1157 adj_ps_id,
1158 cons_inv_line_number,
1159 org_id)
1160 SELECT /*+ index (PS AR_PAYMENT_SCHEDULES_N5) */
1161 l_consinv_id,
1162 'ADJUSTMENT',
1163 PS.trx_number,
1164 RA.gl_date,
1165 RA.amount,
1166 NVL(RA.tax_adjusted, 0),
1167 RA.adjustment_id,
1168 NULL,
1169 ps.org_id
1170 FROM
1171 ar_adjustments RA,
1172 ar_payment_schedules PS
1173 WHERE
1177 AND RA.type in ('CHARGES','FREIGHT','INVOICE','LINE','TAX')
1174 RA.cons_inv_id is NULL
1175 /* bug2434295 Changed P_cutoff_date to l_real_cutoff_date */
1176 AND RA.gl_date < l_real_cutoff_date
1178 AND RA.status = 'A'
1179 AND PS.payment_schedule_id = RA.payment_schedule_id
1180 AND PS.customer_site_use_id = L_sites.site_id
1181 AND PS.invoice_currency_code = P_currency
1182 AND PS.class||'' <> 'GUAR'
1183 AND nvl(PS.exclude_from_cons_bill_flag, 'N') <> 'Y';
1184
1185 /** For Site: cash receipts. **/
1186 /* bug2882196 : Added exclude_from_cons_bill_flag condition not to get legacy
1187 receipts. */
1188 INSERT INTO ar_cons_inv_trx (cons_inv_id,
1189 transaction_type,
1190 trx_number,
1191 transaction_date,
1192 amount_original,
1193 tax_original,
1194 adj_ps_id,
1195 cons_inv_line_number,
1196 org_id)
1197 SELECT
1198 l_consinv_id,
1199 'RECEIPT',
1200 PS.trx_number,
1201 CR.receipt_date,
1202 PS.amount_due_original,
1203 NULL,
1204 PS.payment_schedule_id,
1205 NULL,
1206 PS.org_id
1207 FROM
1208 ar_payment_schedules PS,
1209 ar_cash_receipts CR
1210 WHERE
1211 PS.customer_site_use_id = L_sites.site_id
1212 AND PS.cons_inv_id IS NULL
1213 AND PS.class||'' = 'PMT'
1214 AND PS.invoice_currency_code = P_currency
1215 AND CR.cash_receipt_id = PS.cash_receipt_id
1216 /* bug2434295 Changed P_cutoff_date to l_real_cutoff_date */
1217 AND CR.receipt_date < l_real_cutoff_date
1218 AND nvl(PS.exclude_from_cons_bill_flag, 'N') <> 'Y';
1219
1220 /** For Site: cash receipts reversals. **/
1221 /* bug2882196 : Added exclude_from_cons_bill_flag condition not to get legacy
1222 receipts. */
1223 INSERT INTO ar_cons_inv_trx (cons_inv_id,
1224 transaction_type,
1225 trx_number,
1226 transaction_date,
1227 amount_original,
1228 tax_original,
1229 adj_ps_id,
1230 cons_inv_line_number,
1231 org_id)
1232 SELECT
1233 l_consinv_id,
1234 'RECEIPT REV',
1235 PS.trx_number,
1236 CR.reversal_date,
1237 (-1)*PS.amount_due_original,
1238 NULL,
1239 PS.payment_schedule_id,
1240 NULL,
1241 CR.org_id
1242 FROM
1243 ar_payment_schedules PS,
1244 ar_cash_receipts CR
1245 WHERE
1246 PS.customer_site_use_id = L_sites.site_id
1247 AND PS.cons_inv_id_rev IS NULL
1248 AND PS.invoice_currency_code = P_currency
1249 AND PS.class||'' = 'PMT'
1250 AND CR.cash_receipt_id = PS.cash_receipt_id
1251 /* bug2434295 Changed P_cutoff_date to l_real_cutoff_date */
1252 AND CR.reversal_date < l_real_cutoff_date
1253 AND nvl(PS.exclude_from_cons_bill_flag, 'N') <> 'Y';
1254
1255 /** For Site: need to reverse cash receipts if applied to a different **/
1256 /** bill-to. **/
1257 /** 531330 - changed '(-1)*RA.amount_applied' to 'RA.amount_applied **/
1258 /** Cross Currency functionality implemented. **/
1259 /* bug2882196 : Added 'EXCLUDE RECREV' for when applied to legacy invoices */
1260
1261 INSERT INTO ar_cons_inv_trx (cons_inv_id,
1262 transaction_type,
1263 trx_number,
1264 transaction_date,
1265 amount_original,
1266 tax_original,
1267 adj_ps_id,
1268 cons_inv_line_number,
1269 org_id)
1270 SELECT
1271 l_consinv_id,
1272 DECODE (nvl(ps_inv.exclude_from_cons_bill_flag, 'N'), 'Y','EXCLUDE RECREV',
1273 DECODE (nvl (ps_cash.customer_site_use_id, -1), ps_inv.customer_site_use_id,
1274 DECODE (ps_cash.invoice_currency_code, ps_inv.invoice_currency_code,
1275 'XXXXXXXXXX', 'XCURR RECREV'),
1276 DECODE (ps_cash.invoice_currency_code, ps_inv.invoice_currency_code,
1277 'XSITE RECREV', 'XSITE XCURR RECREV')) ),
1278 ps_cash.trx_number,
1279 RA.apply_date,
1280 nvl (ra.amount_applied_from, RA.amount_applied),
1281 NULL,
1282 RA.receivable_application_id,
1283 NULL,
1284 ps_cash.org_id
1285 FROM
1286 ar_receivable_applications RA,
1290 RA.cons_inv_id IS NULL
1287 ar_payment_schedules ps_cash,
1288 ar_payment_schedules ps_inv
1289 WHERE
1291 AND RA.status = 'APP'
1292 AND RA.application_type = 'CASH'
1293 /* bug2434295 Changed P_cutoff_date to l_real_cutoff_date */
1294 AND RA.apply_date < l_real_cutoff_date
1295 AND ps_cash.payment_schedule_id = RA.payment_schedule_id
1296 AND ps_cash.customer_site_use_id = L_sites.site_id
1297 AND ps_cash.invoice_currency_code = P_currency
1298 AND ps_inv.payment_schedule_id = RA.applied_payment_schedule_id
1299 AND nvl(ps_cash.exclude_from_cons_bill_flag, 'N') <> 'Y'
1300 AND ( ps_cash.customer_site_use_id <> ps_inv.customer_site_use_id
1301 OR ra.amount_applied_from IS NOT NULL
1302 OR nvl(ps_inv.exclude_from_cons_bill_flag, 'N') = 'Y');
1303
1304 /*Bug2677085- Added a select statement to pick up those applications which were considered as XSITE RECAPP but now have the same bill to site as that of the
1305 invoice being processed by the CBI. A XSITE RECREV (or XSITE XCURR RECREV) is
1306 created to negate the application from receipt amount. */
1307
1308 INSERT INTO ar_cons_inv_trx (cons_inv_id,
1309 transaction_type,
1310 trx_number,
1311 transaction_date,
1312 amount_original,
1313 tax_original,
1314 adj_ps_id,
1315 cons_inv_line_number,
1316 org_id)
1317 SELECT
1318 l_consinv_id,
1319 DECODE (ps_cash.invoice_currency_code, ps_inv.invoice_currency_code,
1320 'XSITE RECREV', 'XSITE XCURR RECREV'),
1321 ps_cash.trx_number,
1322 RA.apply_date,
1323 nvl (ra.amount_applied_from, RA.amount_applied),
1324 NULL,
1325 RA.receivable_application_id,
1326 NULL,
1327 ps_cash.org_id
1328 FROM
1329 ar_cons_inv_trx inv_trx,
1330 ar_receivable_applications ra,
1331 ar_payment_schedules ps_cash,
1332 ar_payment_schedules ps_inv
1333 WHERE ra.cons_inv_id_to is not null
1334 AND ra.cons_inv_id is null
1335 AND ra.status = 'APP'
1336 AND ra.application_type = 'CASH'
1337 AND ra.apply_date < l_real_cutoff_date
1338 AND ps_cash.payment_schedule_id = ra.payment_schedule_id
1339 AND ps_cash.customer_site_use_id = L_sites.site_id
1340 AND ps_cash.invoice_currency_code = P_currency
1341 AND ps_inv.payment_schedule_id = ra.applied_payment_schedule_id
1342 AND ps_cash.customer_site_use_id = ps_inv.customer_site_use_id
1343 /* bug2786667 : Modified bad join condition.
1344 AND ra.cons_inv_id_to = inv_trx.cons_inv_id
1345 */
1346 AND ra.receivable_application_id = inv_trx.adj_ps_id
1347 AND inv_trx.transaction_type IN ('XSITE RECAPP','XSITE XCURR RECAPP');
1348
1349
1350
1351 /** For Site: applied cash receipts where cash receipt bill-to is different **/
1352
1353 /** bug 499781 - changed '(-1)*RA.amount_applied' to 'RA.amount_applied' **/
1354 /** BUG 531330 - changed back to (-1)*RA.amount_applied **/
1355 /** Cross Currency functionality has been added. **/
1356 /* bug2882196 : Added 'EXCLUDE RECAPP' for when legacy receipt applied to
1357 non-legacy invoices. */
1358
1359 INSERT INTO ar_cons_inv_trx (cons_inv_id,
1360 transaction_type,
1361 trx_number,
1362 transaction_date,
1363 amount_original,
1364 tax_original,
1365 adj_ps_id,
1366 cons_inv_line_number,
1367 org_id)
1368 SELECT
1369 l_consinv_id,
1370 DECODE (nvl(ps_cash.exclude_from_cons_bill_flag, 'N'),'Y','EXCLUDE RECAPP',
1371 DECODE (nvl (ps_cash.customer_site_use_id, -1), ps_inv.customer_site_use_id,
1372 DECODE (ps_cash.invoice_currency_code, ps_inv.invoice_currency_code,
1373 'XXXXXXXXXX', 'XCURR RECAPP'),
1374 DECODE (ps_cash.invoice_currency_code, ps_inv.invoice_currency_code,
1375 'XSITE RECAPP', 'XSITE XCURR RECAPP')) ),
1376 ps_cash.trx_number,
1377 RA.apply_date,
1378 (-1)*RA.amount_applied,
1379 NULL,
1380 RA.receivable_application_id,
1381 NULL,
1382 ps_cash.org_id
1383 FROM
1384 ar_receivable_applications RA,
1385 ar_payment_schedules ps_cash,
1386 ar_payment_schedules ps_inv
1387 WHERE
1388 RA.cons_inv_id_to IS NULL
1389 AND RA.status = 'APP'
1390 AND RA.application_type = 'CASH'
1391 /* bug2434295 Changed P_cutoff_date to l_real_cutoff_date */
1392 AND RA.apply_date < l_real_cutoff_date
1393 AND ps_cash.payment_schedule_id = RA.payment_schedule_id
1394 AND ps_inv.payment_schedule_id = RA.applied_payment_schedule_id
1395 AND ps_inv.customer_site_use_id = L_sites.site_id
1396 AND ps_inv.invoice_currency_code = P_currency
1400 OR nvl(ps_cash.exclude_from_cons_bill_flag, 'N') = 'Y');
1397 AND nvl(ps_inv.exclude_from_cons_bill_flag, 'N') <> 'Y'
1398 AND ( nvl(ps_cash.customer_site_use_id,-1) <> ps_inv.customer_site_use_id
1399 OR ra.amount_applied_from IS NOT NULL
1401
1402 /* Bug2778646- Added a select statement to pick up those applications which were
1403 considered as XSITE RECREV but now have the same bill to site as that of the
1404 invoice being processed by the CBI. A XSITE RECAPP (or XSITE XCURR RECAPP) is
1405 created to negate the application from receipt amount. */
1406 INSERT INTO ar_cons_inv_trx (cons_inv_id,
1407 transaction_type,
1408 trx_number,
1409 transaction_date,
1410 amount_original,
1411 tax_original,
1412 adj_ps_id,
1413 cons_inv_line_number,
1414 org_id)
1415 SELECT
1416 l_consinv_id,
1417 DECODE (ps_cash.invoice_currency_code, ps_inv.invoice_currency_code,
1418 'XSITE RECAPP', 'XSITE XCURR RECAPP'),
1419 ps_cash.trx_number,
1420 RA.apply_date,
1421 (-1)*RA.amount_applied,
1422 NULL,
1423 RA.receivable_application_id,
1424 NULL,
1425 ps_cash.org_id
1426 FROM
1427 ar_cons_inv_trx inv_trx,
1428 ar_receivable_applications ra,
1429 ar_payment_schedules ps_cash,
1430 ar_payment_schedules ps_inv
1431 WHERE ra.cons_inv_id_to is null
1432 AND ra.cons_inv_id is not null
1433 AND ra.status = 'APP'
1434 AND ra.application_type = 'CASH'
1435 AND ra.apply_date < l_real_cutoff_date
1436 AND ps_cash.payment_schedule_id = RA.payment_schedule_id
1437 AND ps_inv.payment_schedule_id = RA.applied_payment_schedule_id
1438 AND ps_inv.customer_site_use_id = L_sites.site_id
1439 AND ps_inv.invoice_currency_code = P_currency
1440 AND ps_cash.customer_site_use_id = ps_inv.customer_site_use_id
1441 AND ra.receivable_application_id = inv_trx.adj_ps_id
1442 AND inv_trx.transaction_type IN ('XSITE RECREV','XSITE XCURR RECREV');
1443
1444 /** For Site: get on-account credit memo's applied to different bill-to. **/
1445 /** Will need to add a reversal line because Credit Memo was used **/
1446 /** for a bill-to that is different from the current bill-to. **/
1447 /* bug2882196 : Added 'EXCLUDE_CMREV' for when credit memo applied to
1448 legacy invoices */
1449 INSERT INTO ar_cons_inv_trx (cons_inv_id,
1450 transaction_type,
1451 trx_number,
1452 transaction_date,
1453 amount_original,
1454 tax_original,
1455 adj_ps_id,
1456 cons_inv_line_number,
1457 org_id)
1458 SELECT
1459 l_consinv_id,
1460 DECODE ( nvl(PS2.exclude_from_cons_bill_flag, 'N'), 'Y', 'EXCLUDE_CMREV', 'XSITE_CMREV'),
1461 PS1.trx_number,
1462 RA.apply_date,
1463 RA.amount_applied,
1464 NULL,
1465 RA.receivable_application_id,
1466 NULL,
1467 PS1.org_id
1468 FROM
1469 ar_receivable_applications RA,
1470 ar_payment_schedules PS1,
1471 ar_payment_schedules PS2
1472 /* bug2700662 Removed
1473 ra_customer_trx CT
1474 */
1475 WHERE
1476 RA.cons_inv_id IS NULL
1477 AND RA.status = 'APP'
1478 AND RA.application_type = 'CM'
1479 /* bug2434295 Changed P_cutoff_date to l_real_cutoff_date */
1480 AND RA.apply_date < l_real_cutoff_date
1481 /* bug2700662 Removed
1482 AND CT.customer_trx_id = RA.customer_trx_id
1483 */
1484 AND PS1.payment_schedule_id = RA.payment_schedule_id
1485 AND PS1.customer_site_use_id = L_sites.site_id
1486 AND PS1.invoice_currency_code = P_currency
1487 AND nvl(PS1.exclude_from_cons_bill_flag, 'N') <> 'Y'
1488 AND PS2.payment_schedule_id = RA.applied_payment_schedule_id
1489 AND ( PS2.customer_site_use_id <> PS1.customer_site_use_id
1490 or nvl(PS2.exclude_from_cons_bill_flag, 'N') = 'Y' ) ;
1491
1492
1493 /** For Site: get on-account credit memos assigned to different bill-to but **/
1494 /** applied against invoice for current bill-to. **/
1495 /* bug2882196 : Added 'EXCLUDE_CMAPP' for when legacy credit memo applied to
1496 non-legacy invoice. */
1497 INSERT INTO ar_cons_inv_trx (cons_inv_id,
1498 transaction_type,
1499 trx_number,
1500 transaction_date,
1501 amount_original,
1502 tax_original,
1503 adj_ps_id,
1504 cons_inv_line_number,
1505 org_id)
1506 SELECT
1507 l_consinv_id,
1511 (-1)*RA.amount_applied,
1508 DECODE( nvl(PS2.exclude_from_cons_bill_flag, 'N') , 'Y', 'EXCLUDE_CMAPP','XSITE_CMAPP') ,
1509 PS1.trx_number,
1510 RA.apply_date,
1512 NULL,
1513 RA.receivable_application_id,
1514 NULL,
1515 PS1.org_id
1516 FROM
1517 ar_receivable_applications RA,
1518 ar_payment_schedules PS1,
1519 ar_payment_schedules PS2
1520 /* bug2700662 Removed
1521 ra_customer_trx CT
1522 */
1523 WHERE
1524 RA.cons_inv_id_to IS NULL
1525 AND RA.status = 'APP'
1526 AND RA.application_type = 'CM'
1527 /* bug2434295 Changed P_cutoff_date to l_real_cutoff_date */
1528 AND RA.apply_date < l_real_cutoff_date
1529 /* bug2700662 Removed
1530 AND CT.customer_trx_id = RA.customer_trx_id
1531 AND CT.previous_customer_trx_id IS NULL
1532 */
1533 AND PS1.payment_schedule_id = RA.applied_payment_schedule_id
1534 AND PS1.customer_site_use_id = L_sites.site_id
1535 AND PS1.invoice_currency_code = P_currency
1536 AND nvl(PS1.exclude_from_cons_bill_flag, 'N') <> 'Y'
1537 AND PS2.payment_schedule_id = RA.payment_schedule_id
1538 AND ( PS2.customer_site_use_id <> PS1.customer_site_use_id
1539 or nvl(PS2.exclude_from_cons_bill_flag, 'N') = 'Y');
1540
1541 /** For Site: update header for totals. **/
1542 /* bug2882196 Added EXCLUDE_CMREV/APP transaction_type */
1543 SELECT nvl(sum(amount_original),0)
1544 INTO l_new_billed
1545 FROM ar_cons_inv_trx
1546 WHERE cons_inv_id = l_consinv_id
1547 AND transaction_type IN ('INVOICE','CREDIT_MEMO','ADJUSTMENT',
1548 'XSITE_CMREV','XSITE_CMAPP',
1549 'EXCLUDE_CMREV', 'EXCLUDE_CMAPP');
1550
1551 /* bug2786667 Added XCURR transaction_type */
1552 /* bug2882196 Added EXCLUDE RECREV/APP transaction_type */
1553 SELECT nvl(sum(amount_original),0)
1554 INTO l_period_receipts
1555 FROM ar_cons_inv_trx
1556 WHERE cons_inv_id = l_consinv_id
1557 AND transaction_type IN ('RECEIPT','RECEIPT REV','XSITE RECREV',
1558 'XSITE RECAPP',
1559 'XSITE XCURR RECAPP','XSITE XCURR RECREV',
1560 'EXCLUDE RECREV', 'EXCLUDE RECAPP');
1561
1562 UPDATE ar_cons_inv
1563 SET ending_balance =
1564 beginning_balance + l_new_billed + l_period_receipts
1565 WHERE cons_inv_id = l_consinv_id;
1566
1567 /** For Site: update ar_payment_schedules, ar_receivable_applications **/
1568 /** and ar_adjustments **/
1569 /** Cross Currency functionality. **/
1570
1571 UPDATE ar_payment_schedules PS
1572 SET PS.cons_inv_id = l_consinv_id
1573 WHERE PS.payment_schedule_id IN
1574 (SELECT IT.adj_ps_id
1575 FROM ar_cons_inv_trx IT
1576 WHERE IT.cons_inv_id = l_consinv_id
1577 AND IT.transaction_type IN ('INVOICE','CREDIT_MEMO',
1578 'RECEIPT'));
1579
1580 UPDATE ar_payment_schedules PS
1581 SET PS.cons_inv_id_rev = l_consinv_id
1582 WHERE PS.payment_schedule_id IN
1583 (SELECT IT.adj_ps_id
1584 FROM ar_cons_inv_trx IT
1585 WHERE IT.cons_inv_id = l_consinv_id
1586 AND IT.transaction_type = 'RECEIPT REV');
1587
1588 /* bug2882196 Added 'EXCLUDE RECREV' and 'EXCLUDE_CMREV' */
1589 UPDATE ar_receivable_applications RA
1590 SET RA.cons_inv_id = l_consinv_id
1591 WHERE RA.receivable_application_id IN
1592 (SELECT IT.adj_ps_id
1593 FROM ar_cons_inv_trx IT
1594 WHERE IT.cons_inv_id = l_consinv_id
1595 AND IT.transaction_type IN ('XSITE RECREV',
1596 'XSITE_CMREV',
1597 'XCURR RECREV',
1598 'XSITE XCURR RECREV',
1599 'EXCLUDE RECREV',
1600 'EXCLUDE_CMREV'));
1601 /*Bug 2650786: Corrected Typo in above statement */
1602
1603
1604 /* bug2882196 Added 'EXCLUDE RECAPP' and 'EXCLUDE_CMAPP' */
1605 UPDATE ar_receivable_applications RA
1606 SET RA.cons_inv_id_to = l_consinv_id
1607 WHERE RA.receivable_application_id IN
1608 (SELECT IT.adj_ps_id
1609 FROM ar_cons_inv_trx IT
1610 WHERE IT.cons_inv_id = l_consinv_id
1611 AND IT.transaction_type IN ('XSITE RECAPP',
1612 'XSITE_CMAPP',
1613 'XCURR RECAPP',
1614 'XSITE XCURR RECAPP',
1615 'EXCLUDE RECAPP',
1616 'EXCLUDE_CMAPP'));
1617
1618 /* bug2922922 : Added hint */
1619 UPDATE ar_adjustments RA
1620 SET RA.cons_inv_id = l_consinv_id
1621 WHERE RA.adjustment_id IN
1622 (SELECT /*+ index (IT AR_CONS_INV_TRX_N1) */
1623 IT.adj_ps_id
1624 FROM ar_cons_inv_trx IT
1625 WHERE IT.cons_inv_id = l_consinv_id
1626 AND IT.transaction_type = 'ADJUSTMENT');
1630 UPDATE ar_cons_inv ci
1627
1628 -- bug2778646 Changed status of selected merged cbi.
1629 -- DRAFT_MERGE/MERGED status CBI is not selected by other CBI.
1631 SET status = DECODE(P_print_option, 'DRAFT', 'DRAFT_MERGE','MERGED')
1632 WHERE status = 'MERGE_PENDING'
1633 AND site_use_id = L_sites.site_id
1634 AND currency_code = P_currency
1635 AND cut_off_date <= P_cutoff_date ;
1636
1637
1638 /** set cons_inv_id to -1 for all rows where unapplied bill-to is same **/
1639 /** as bill-to of apply-to. **/
1640 /*
1641 1226201 fbreslin: change the order of the tables in the WHERE cluase of the
1642 sub-query for performance purposes.
1643 */
1644 /* bug2706497 : Removed meaningless update stmt.
1645 UPDATE ar_receivable_applications RA
1646 SET RA.cons_inv_id = -1
1647 WHERE RA.cons_inv_id IN
1648 (SELECT RA1.cons_inv_id
1649 FROM ar_payment_schedules PS1,
1650 ar_payment_schedules PS2,
1651 ar_receivable_applications RA1
1652 WHERE RA1.cons_inv_id IS NULL
1653 AND RA1.status = 'APP'
1654 AND RA1.application_type IN ('CM', 'CASH')
1655 AND RA1.apply_date < to_date(l_real_cutoff_date)
1656 AND PS1.payment_schedule_id = RA1.payment_schedule_id
1657 AND PS1.customer_site_use_id = L_sites.site_id
1658 AND PS1.invoice_currency_code = P_currency
1659 AND PS2.payment_schedule_id = RA1.applied_payment_schedule_id
1660 AND PS1.customer_site_use_id = PS2.customer_site_use_id);
1661 */
1662
1663 /** For Site: finished. Get another site. **/
1664 END LOOP;
1665
1666 -- bug2501071 : for C_types cursor loop.
1667 END LOOP;
1668
1669 EXCEPTION
1670 WHEN OTHERS THEN
1671 arp_standard.debug( 'EXCEPTION: generate:' );
1672 arp_standard.debug( 'P_customer_id: '||P_customer_id);
1673 arp_standard.debug( 'P_customer_number: '||P_customer_number);
1674 arp_standard.debug( 'P_bill_to_site: '||P_bill_to_site);
1675 arp_standard.debug( 'P_term_id: '||P_term_id);
1676 arp_standard.debug( 'P_cutoff_date: '||P_cutoff_date);
1677 arp_standard.debug( 'P_print_option: '||P_print_option);
1678 arp_standard.debug( 'P_currency: '||P_currency);
1679 RAISE;
1680 END;
1681 --
1682 /*----------------------------------------------------------------------------*
1683 | PROCEDURE |
1684 | update_status |
1685 | |
1686 | DESCRIPTION |
1687 | After Consolidated Billing Invoices are printed successfully, update |
1688 | status of the billing invoices from 'PENDING' to 'PRINTED'. |
1689 | For NEW or DRAFT, parameters P_consinv_id and P_request_id are NULL. |
1690 | These parameters are specified by the user for a REPRINT only. |
1691 | |
1692 | SCOPE - PRIVATE |
1693 | |
1694 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
1695 | |
1696 | ARGUMENTS : IN: |
1697 | P_print_option - print option |
1698 | P_consinv_id - consolidated billing invoice |
1699 | P_request_id - concurrent request id |
1700 | |
1701 | OUT: |
1702 | None |
1703 | RETURNS : None |
1704 | |
1705 | MODIFICATION HISTORY |
1706 | 26-MAY-2005 MRAYMOND 4188835 - Added freeze call related to
1707 | etax. When a invoice is printed, we need
1708 | to notify etax that it will not change.
1709 *----------------------------------------------------------------------------*/
1710 PROCEDURE update_status (P_print_option IN VARCHAR,
1711 P_consinv_id IN NUMBER,
1712 P_request_id IN NUMBER) IS
1713
1714 CURSOR c_pending_trx IS
1715 SELECT PS.customer_trx_id
1716 FROM ar_payment_schedules PS,
1717 ar_cons_inv_trx IT,
1718 ar_cons_inv CI
1719 WHERE
1720 CI.print_status = 'PENDING'
1721 AND IT.cons_inv_id = CI.cons_inv_id
1722 AND IT.transaction_type IN ('INVOICE','CREDIT_MEMO')
1723 AND PS.payment_schedule_id = IT.adj_ps_id;
1724
1725 BEGIN
1726
1727 /* bug3604391 Changed the sequence of following update stmts.
1728 Because ra_customer_trx was not updated after
1729 ar_cons_inv.print_status was changed.
1733 nvl(CT.printing_original_date,sysdate),
1730 */
1731 UPDATE ra_customer_trx CT
1732 SET CT.printing_original_date =
1734 CT.printing_last_printed = sysdate,
1735 CT.printing_count = nvl(CT.printing_count,0) +
1736 DECODE(P_print_option,
1737 'REPRINT', 0,
1738 1)
1739 WHERE CT.customer_trx_id IN
1740 (SELECT PS.customer_trx_id
1741 FROM ar_payment_schedules PS,
1742 ar_cons_inv_trx IT,
1743 ar_cons_inv CI
1744 WHERE (
1745 (P_print_option = 'REPRINT'
1746 AND CI.cons_inv_id=nvl(P_consinv_id,CI.cons_inv_id)
1747 AND CI.concurrent_request_id =
1748 nvl(P_request_id, CI.concurrent_request_id))
1749 OR
1750 (P_print_option IN ('DRAFT', 'PRINT')
1751 AND CI.print_status = 'PENDING')
1752 )
1753 AND IT.cons_inv_id = CI.cons_inv_id
1754 AND IT.transaction_type IN ('INVOICE','CREDIT_MEMO')
1755 AND PS.payment_schedule_id = IT.adj_ps_id);
1756
1757 /* 4188835 - If printing for first time, freeze trans for tax */
1758 IF P_print_option = 'PRINT'
1759 THEN
1760 FOR trx in c_pending_trx LOOP
1761 arp_etax_util.global_document_update(trx.customer_trx_id,
1762 null,
1763 'PRINT');
1764 END LOOP;
1765 END IF;
1766
1767 UPDATE ar_cons_inv
1768 SET print_status = 'PRINTED',
1769 last_update_date = arp_global.last_update_date,
1770 last_updated_by = arp_global.last_updated_by,
1771 last_update_login = arp_global.last_update_login
1772 WHERE (P_print_option = 'REPRINT'
1773 AND cons_inv_id = nvl(P_consinv_id,cons_inv_id)
1774 AND concurrent_request_id = DECODE (P_consinv_id,
1775 NULL, P_request_id,
1776 concurrent_request_id))
1777 OR (P_print_option IN ('DRAFT', 'PRINT')
1778 AND print_status = 'PENDING');
1779
1780 EXCEPTION
1781 WHEN OTHERS THEN
1782 arp_standard.debug( ' Exception: update_status: ');
1783 RAISE;
1784 END;
1785 --
1786 PROCEDURE Report( P_report IN ReportParametersType) Is
1787 BEGIN
1788 IF P_report.print_option = 'PRINT' OR
1789 P_report.print_option = 'DRAFT'
1790 THEN
1791 IF P_report.print_status = 'PENDING' THEN
1792 generate (P_report.print_option,
1793 P_report.detail_option,
1794 P_report.currency_code,
1795 P_report.customer_id,
1796 P_report.customer_number,
1797 P_report.bill_to_site,
1798 P_report.cutoff_date,
1799 P_report.last_day_of_month,
1800 P_report.term_id);
1801
1802 /**after-report trigger: update status from 'PENDING' to 'PRINTED' **/
1803 /** to denote a successful print. Pass current concurrent request id **/
1804 ELSE
1805 update_status(P_report.print_option,
1806 P_report.consinv_id,
1807 P_report.request_id);
1808 END IF;
1809
1810 ELSIF P_report.print_option = 'REPRINT' THEN
1811 IF P_report.print_status = 'PENDING' THEN
1812 reprint(P_report.consinv_id,
1813 P_report.request_id);
1814 ELSE
1815 /**after-report trigger: update status from 'PENDING' to 'PRINTED' **/
1816 /** to indicate a successful print. Pass concurrent request id **/
1817 update_status(P_report.print_option,
1818 P_report.consinv_id,
1819 P_report.request_id);
1820 END IF;
1821
1822 ELSIF P_report.print_option = 'DRAFT_ACCEPT' THEN
1823 accept(P_report.consinv_id,
1824 P_report.request_id);
1825
1826 ELSIF P_report.print_option = 'DRAFT_REJECT' THEN
1827 reject(P_report.consinv_id,
1828 P_report.request_id);
1829 END IF;
1830
1831 EXCEPTION
1832 WHEN OTHERS THEN
1833 arp_standard.debug( 'Exception: arp_consinv( P_report):'||sqlerrm );
1834 RAISE_APPLICATION_ERROR( -20000, sqlerrm||'$Revision 70.00 $:Report (
1835 P_report ):');
1836 END;
1837 --
1838 /*----------------------------------------------------------------------------+
1839 | PROCEDURE |
1840 | report |
1841 | |
1842 | DESCRIPTION |
1843 | Called by before-report trigger in report ARXCBI. Depending on value |
1844 | of parameter print_option, will call the appropriate procedure. |
1845 | The print_status will be 'PENDING' when called by the before-report |
1846 | trigger. |
1850 | |
1847 | The after-report trigger in report ARXCBI will execute this stored |
1848 | procedure with print_status 'PRINTED' to denote a successful print for |
1849 | print options 'DRAFT', 'PRINTED', 'REPRINT'. |
1851 | SCOPE - public |
1852 | |
1853 | EXTERNAL PROCEDURE/FUNCTIONS ACCESSED |
1854 | |
1855 | ARGUMENTS : IN: |
1856 | P_print_option - Print Option (required) |
1857 | P_detail_option - Detail/Summary (not required) |
1858 | P_currency_code - Currency code (required) |
1859 | P_customer_id - Customer id (not required) |
1860 | P_bill_to_site - Bill-to site (not required) |
1861 | P_term_id - Term id (not required) |
1862 | P_cutoff_date - cut-off date (required) |
1863 | P_consinv_id - Consolidated Billing Invoice id |
1864 | (not required) |
1865 | P_request_id - Concurrent Request id |
1866 | (not required) |
1867 | P_print_status - print status (required) |
1868 | OUT: |
1869 | None |
1870 | |
1871 | RETURNS : NONE |
1872 | |
1873 | NOTES |
1874 | |
1875 | MODIFICATION HISTORY |
1876 | |
1877 *----------------------------------------------------------------------------*/
1878 PROCEDURE Report( P_print_option VARCHAR2,
1879 P_detail_option VARCHAR2,
1880 P_currency_code VARCHAR2,
1881 P_customer_id NUMBER,
1882 P_customer_number VARCHAR2,
1883 P_bill_to_site NUMBER,
1884 P_cutoff_date DATE,
1885 P_last_day_of_month VARCHAR2,
1886 P_term_id NUMBER,
1887 P_consinv_id NUMBER,
1888 P_request_id NUMBER,
1889 P_print_status VARCHAR2) IS
1890 l_report ReportParametersType;
1891 BEGIN
1892 l_report.print_option := P_print_option;
1893 l_report.detail_option := P_detail_option;
1894 l_report.currency_code := P_currency_code;
1895 l_report.customer_id := P_customer_id;
1896 l_report.customer_number := P_customer_number;
1897 l_report.bill_to_site := P_bill_to_site;
1898 l_report.cutoff_date := P_cutoff_date;
1899 l_report.last_day_of_month := P_last_day_of_month;
1900 l_report.term_id := P_term_id;
1901 l_report.consinv_id := P_consinv_id;
1902 l_report.request_id := P_request_id;
1903 l_report.print_status := P_print_status;
1904 --
1905 Report(l_report);
1906 --
1907 EXCEPTION
1908 WHEN OTHERS THEN
1909 arp_standard.debug( 'Exception:arp_consinv.Report( ...):'||sqlerrm);
1910 RAISE_APPLICATION_ERROR( -20000, sqlerrm||'$Revision 70.00 $:Report(
1911 ... ):' );
1912 END;
1913 --
1914 END arp_consinv;