[Home] [Help]
PACKAGE BODY: APPS.ARP_GROUP_INV
Source
1 PACKAGE BODY arp_group_inv AS
2 /*$Header: ARPMINVB.pls 120.10 2008/04/30 13:44:31 mgaleti ship $*/
3 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
4
5 /** Store the Cons billing Number in a PL/SQL table of cons_inv_id **/
6
7 TYPE cons_billing_number_type IS
8 TABLE OF VARCHAR2(100)
9 INDEX BY BINARY_INTEGER;
10
11 TYPE cons_inv_id_type IS
12 TABLE OF NUMBER(15)
13 INDEX BY BINARY_INTEGER;
14
15 g_cons_billing_number cons_billing_number_type;
16
17 g_cons_inv_id cons_inv_id_type;
18
19
20 /*----------------------------------------------------------------------------*
21 | PROCEDURE |
22 | generate |
23 | |
24 | DESCRIPTION |
25 | Will create new Grouped Invoices for the given AutoInvoice Request |
26 | |
27 | SCOPE - PRIVATE |
28 | |
29 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
30 | |
31 | ARGUMENTS : IN: |
32 | P_request_id - Request id |
33 | : OUT: |
34 | None |
35 | |
36 | RETURNS : NONE |
37 | |
38 | NOTES |
39 | |
40 | MODIFICATION HISTORY |
41 | |
42 | 13-JUL-2000 Ramakant Alat Created |
43 *----------------------------------------------------------------------------*/
44 PROCEDURE generate ( p_request_id IN NUMBER) IS
45
46 CURSOR C_group_inv (C_request_id NUMBER) IS
47 SELECT
48 ps.cons_inv_id,
49 bill_to_customer_id customer_id,
50 bill_to_site_use_id site_use_id,
51 ct.invoice_currency_code currency_code,
52 decode(tt.type, 'CM', 'CREDIT MEMO', 'INVOICE') transaction_type,
53 ct.trx_number,
54 ct.trx_date,
55 ct.customer_trx_id,
56 ct.org_id
57 FROM
58 ar_payment_schedules ps,
59 ra_customer_trx ct,
60 ra_cust_trx_types tt
61 WHERE
62 ct.cust_trx_type_id = tt.cust_trx_type_id
63 AND ct.customer_trx_id = ps.customer_trx_id
64 AND ps.cons_inv_id > 0
65 AND ps.terms_sequence_number = 1
66 AND ct.customer_trx_id in ( SELECT customer_trx_id
67 FROM ra_interface_lines il
68 WHERE il.request_id = C_request_id
69 AND customer_trx_id IS NOT NULL
70 AND cons_billing_number IS NOT NULL
71 AND NVL(il.interface_status, '~') <> 'P'
72 )
73 ORDER BY
74 ps.cons_inv_id,
75 ct.customer_trx_id;
76
77 old_cons_inv_id AR_PAYMENT_SCHEDULES.CONS_INV_ID%TYPE:=-1;
78 new_cons_inv_id AR_PAYMENT_SCHEDULES.CONS_INV_ID%TYPE:=-1;
79
80 old_cons_billing_number AR_CONS_INV.CONS_BILLING_NUMBER%TYPE:='$$~$$';
81 new_cons_billing_number AR_CONS_INV.CONS_BILLING_NUMBER%TYPE:='$$~$$';
82 l_group_inv_line_number AR_CONS_INV_TRX.cons_inv_line_number%TYPE:=1;
83
84 BEGIN
85
86 arp_standard.debug('arp_group_inv.generate()+');
87
88 update_ps(p_request_id=>p_request_id);
89
90 FOR c_group_inv_rec IN c_group_inv (p_request_id) LOOP
91
92 new_cons_inv_id := c_group_inv_rec.cons_inv_id;
93
94 --
95 -- Check for the New Group
96 --
97
98 IF new_cons_inv_id <> old_cons_inv_id THEN
99
100 old_cons_inv_id := new_cons_inv_id; -- Set the old id for future comparison
101
102 new_cons_billing_number := g_cons_billing_number(new_cons_inv_id); -- From update_ps
103
104 /* bug3886862 */
105 SELECT nvl(max(cons_inv_line_number),0) + 1
106 INTO l_group_inv_line_number
107 FROM ar_cons_inv_trx
108 WHERE cons_inv_id = new_cons_inv_id ;
109
110 /* if there is no cons inv with the id, create new record */
111 IF l_group_inv_line_number = 1 THEN
112
113 INSERT INTO ar_cons_inv
114 (cons_inv_id,
115 cons_billing_number,
116 customer_id,
117 site_use_id,
118 concurrent_request_id,
119 last_update_date,
120 last_updated_by,
121 creation_date,
122 created_by,
123 last_update_login,
124 cons_inv_type,
125 status,
126 print_status,
127 issue_date,
128 cut_off_date,
129 due_date,
130 org_id)
131 VALUES
132 (new_cons_inv_id, -- Cons Inv Id
133 new_cons_billing_number, -- Cons Billing Number
134 c_group_inv_rec.customer_id, -- Customer Id
135 c_group_inv_rec.site_use_id, -- Site Use Id
136 arp_standard.profile.request_id, -- Request Id
137 arp_global.last_update_date, -- Last Update Date
138 arp_global.last_updated_by, -- Last Updated By
139 arp_global.creation_date, -- Creation Date
140 arp_global.created_by, -- Created By
141 arp_global.last_update_login, -- Last Update Login
142 'MINV', -- Cons Inv Type
143 'IMPORTED', -- Status
144 'PRINTED', -- Print Status
145 TRUNC(sysdate), -- Issue Date
146 NULL, -- Cutoff Date
147 NULL, -- Due Date
148 arp_standard.sysparm.org_id);
149
150 arp_standard.debug('Inserted cons :' || SQL%ROWCOUNT);
151
152 END IF;
153
154 END IF; /** New group ***/
155
156 INSERT INTO ar_cons_inv_trx
157 (cons_inv_id,
158 transaction_type,
159 trx_number,
160 transaction_date,
161 amount_original,
162 tax_original,
163 adj_ps_id,
164 cons_inv_line_number,
165 customer_trx_id,
166 org_id)
167 VALUES
168 (new_cons_inv_id, -- Cons Inv Id
169 c_group_inv_rec.transaction_type, -- Transaction Type
170 c_group_inv_rec.trx_number, -- Transaction Number
171 c_group_inv_rec.trx_date, -- Transaction Date
172 NULL, -- Amount original
173 NULL, -- Tax Original
174 NULL, -- PS Id
175 l_group_inv_line_number, -- Cons Inv Line Number
176 c_group_inv_rec.customer_trx_id, -- Customer Trx Id
177 c_group_inv_rec.org_id);
178
179 l_group_inv_line_number := l_group_inv_line_number + 1;
180
181 arp_standard.debug('Inserted ['|| l_group_inv_line_number||'] :' ||
182 SQL%ROWCOUNT);
183
184 END LOOP;
185
186 arp_standard.debug('arp_group_inv.generate()-');
187
188 EXCEPTION
189 WHEN OTHERS THEN
190 arp_standard.debug( 'EXCEPTION: arp_group_inv.generate()' );
191 RAISE;
192 END;
193 --
194 /*----------------------------------------------------------------------------*
195 | PROCEDURE |
196 | validate_data |
197 | |
198 | DESCRIPTION |
199 | Validate the interface data with respect to the Grouping of Invoice |
200 | |
201 | SCOPE - PRIVATE |
202 | |
203 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
204 | |
205 | ARGUMENTS : IN: |
206 | P_request_id - concurrent request id |
207 | |
208 | OUT: |
209 | none - |
210 | RETURNS : None |
211 | |
212 | MODIFICATION HISTORY |
213 | 25-JUL-2000 Ramakant Alat Created |
214 | |
215 *----------------------------------------------------------------------------*/
216
217 PROCEDURE validate_data (P_request_id IN NUMBER) IS
218 l_message_text VARCHAR2(2000);
219 BEGIN
220
221 arp_standard.debug('arp_group_inv.validate_data()+');
222
223 /******************************************************************
224 *** All the transactions grouped under one Group must belong to **
225 *** the same bill-to customer **
226 ******************************************************************/
227
228 l_message_text := arp_standard.fnd_message('AR_RAXTRX-1810');
229
230 INSERT INTO ra_interface_errors
231 (interface_line_id,
232 message_text,
233 invalid_value,
234 org_id)
235 SELECT l.interface_line_id,
236 l_message_text,
237 l.cons_billing_number,
238 l.org_id
239 FROM ra_interface_lines l
240 WHERE l.request_id = p_request_id
241 AND l.cons_billing_number IS NOT NULL
242 AND l.link_to_line_id IS NULL
243 AND l.orig_system_bill_customer_id IS NOT NULL
244 AND EXISTS (SELECT 'x'
245 FROM ra_interface_lines l2
246 WHERE l2.request_id = l.request_id
247 AND l2.cons_billing_number = l.cons_billing_number
248 AND l2.orig_system_bill_customer_id <> l.orig_system_bill_customer_id);
249
250 arp_standard.debug('Inserted [1]:' || SQL%ROWCOUNT);
251
252 /******************************************************************
253 *** Imported Billing Number must be unique for given type MINV **
254 ******************************************************************/
255
256 l_message_text := arp_standard.fnd_message('AR_RAXTRX-1811');
257
258 INSERT INTO RA_INTERFACE_ERRORS
259 (interface_line_id,
260 message_text,
261 invalid_value,
262 org_id)
263 SELECT l.interface_line_id,
264 l_message_text,
265 l.cons_billing_number,
266 l.org_id
267 FROM ra_interface_lines l
268 WHERE l.request_id = p_request_id
269 AND l.cons_billing_number IS NOT NULL
270 AND (EXISTS (SELECT 'X'
271 FROM ar_cons_inv
272 WHERE cons_billing_number = l.cons_billing_number
273 AND cons_inv_type = 'MINV' )
274 OR
275 EXISTS (SELECT 'X'
276 FROM ra_interface_lines l2
277 WHERE l2.request_id > 0
278 AND l2.request_id <> l.request_iD
279 AND l2.cons_billing_number = l.cons_billing_number));
280
281 arp_standard.debug('Inserted [2]:' || SQL%ROWCOUNT);
282
283 /******************************************************************
284 *** All the transactions grouped under one Group must belong to **
285 *** the same bill-to address **
286 ******************************************************************/
287
288 l_message_text := arp_standard.fnd_message('AR_RAXTRX-1812');
289
290 INSERT INTO RA_INTERFACE_ERRORS
291 (interface_line_id,
292 message_text,
293 invalid_value,
294 org_id)
295 SELECT l.interface_line_id,
296 l_message_text,
297 l.cons_billing_number,
298 l.org_id
299 FROM ra_interface_lines l
300 WHERE l.request_id = p_request_id
301 AND l.cons_billing_number IS NOT NULL
302 AND l.link_to_line_id IS NULL
303 AND l.orig_system_bill_address_id IS NOT NULL
304 AND EXISTS (SELECT 'X'
305 FROM ra_interface_lines l2
306 WHERE l2.request_id = l.request_id
307 AND l2.cons_billing_number = l.cons_billing_number
308 AND l2.orig_system_bill_address_id <> l.orig_system_bill_address_id);
309
310 arp_standard.debug('Inserted [3]:' || SQL%ROWCOUNT);
311
312 /******************************************************************
313 *** Customer must be enabled to Import billing Number **
314 ******************************************************************/
315 -- Bug 2501153: To avoid the merge join cartesian, added a table hz_cust_acct_sites
316
317 l_message_text := arp_standard.fnd_message('AR_RAXTRX-1814');
318
319 INSERT INTO RA_INTERFACE_ERRORS
320 (interface_line_id,
321 message_text,
322 invalid_value,
323 org_id)
324 SELECT l.interface_line_id,
325 l_message_text,
326 l.cons_billing_number,
327 l.org_id
328 FROM ra_interface_lines l
329 WHERE l.request_id = p_request_id
330 AND l.cons_billing_number IS NOT NULL
331 AND l.link_to_line_id IS NULL
332 AND l.orig_system_bill_customer_id IS NOT NULL
333 AND l.orig_system_bill_address_id IS NOT NULL
334 AND EXISTS (SELECT /*+ no_unnest */'X'
335 FROM
336 hz_cust_site_uses su,
337 hz_customer_profiles cp,
338 hz_customer_profiles sp,
339 hz_cust_acct_sites ac
340 WHERE su.cust_acct_site_id = l.orig_system_bill_address_id
341 AND su.site_use_code = 'BILL_TO'
342 AND su.status = 'A'
343 AND cp.cust_account_id = l.orig_system_bill_customer_id
344 AND cp.site_use_id IS NULL
345 AND ac.cust_acct_site_id = su.cust_acct_site_id
346 AND ac.cust_account_id = cp.cust_account_id
347 AND su.site_use_id = sp.site_use_id (+)
348 AND NVL(NVL(sp.cons_inv_flag, cp.cons_inv_flag), 'N') = 'N'
349 );
350 arp_standard.debug('Inserted [4]:' || SQL%ROWCOUNT);
351
352 /******************************************************************
353 *** You cannot import Billing Number for the customer using ***
354 *** Consolidated Billing functionality ***
355 ******************************************************************/
356 -- Bug 2501153: To avoid the merge join cartesian, added a table hz_cust_acct_sites
357
358 l_message_text := arp_standard.fnd_message('AR_RAXTRX-1813');
359
360 INSERT INTO RA_INTERFACE_ERRORS
361 (interface_line_id,
362 message_text,
363 invalid_value,
364 org_id)
365 SELECT l.interface_line_id,
366 l_message_text,
367 l.cons_billing_number,
368 l.org_id
369 FROM ra_interface_lines l
370 WHERE l.request_id = p_request_id
371 AND l.cons_billing_number IS NOT NULL
372 AND l.link_to_line_id IS NULL
373 AND l.orig_system_bill_customer_id IS NOT NULL
374 AND l.orig_system_bill_address_id IS NOT NULL
375 AND EXISTS (SELECT /*+ no_unnest */'X'
376 FROM
377 hz_cust_site_uses su,
378 hz_customer_profiles cp,
379 hz_customer_profiles sp,
380 hz_cust_acct_sites ac
381 WHERE su.cust_acct_site_id = l.orig_system_bill_address_id
382 AND su.site_use_code = 'BILL_TO'
383 AND su.status = 'A'
384 AND cp.cust_account_id = l.orig_system_bill_customer_id
385 AND cp.site_use_id IS NULL
386 AND ac.cust_acct_site_id = su.cust_acct_site_id
387 AND ac.cust_account_id = cp.cust_account_id
388 AND su.site_use_id = sp.site_use_id (+)
389 AND NVL(sp.cons_inv_flag, cp.cons_inv_flag) = 'Y'
390 AND NVL(sp.cons_inv_type, cp.cons_inv_type) <> 'IMPORTED'
391 );
392 arp_standard.debug('Inserted [5]:' || SQL%ROWCOUNT);
393
394 arp_standard.debug('arp_group_inv.validate_data()-');
395
396 EXCEPTION
397 WHEN OTHERS THEN
398 IF PG_DEBUG in ('Y', 'C') THEN
399 arp_standard.debug( ' Exception: validate_data: ');
400 END IF;
401 RAISE;
402 END validate_data;
403 --
404 /*----------------------------------------------------------------------------*
405 | PROCEDURE |
406 | update_ps |
407 | |
408 | DESCRIPTION |
409 | Update ar_payment_schedules |
410 | |
411 | SCOPE - PRIVATE |
412 | |
413 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
414 | |
415 | ARGUMENTS : IN: |
416 | P_request_id - concurrent request id |
417 | |
418 | OUT: |
419 | none - |
420 | RETURNS : None |
421 | |
422 | MODIFICATION HISTORY |
423 | 26-JUL-2000 Ramakant Alat Created |
424 | |
425 *----------------------------------------------------------------------------*/
426 PROCEDURE update_ps (p_request_id IN NUMBER) IS
427
428 CURSOR c01 IS
429 SELECT DISTINCT cons_billing_number, customer_trx_id
430 FROM ra_interface_lines li
431 WHERE request_id = p_request_id
432 and li.customer_trx_id IS NOT NULL
433 AND cons_billing_number IS NOT NULL
434 AND NVL(interface_status, '~') <> 'P' -- Only consider unprocessed Transactions
435 AND EXISTS (SELECT 1
436 FROM ra_customer_trx ct
437 WHERE ct.customer_trx_id = li.customer_trx_id)
438 ORDER BY cons_billing_number, customer_trx_id;
439
440 /* bug3886862 check if there is cons inv with the number */
441 /* bug3895741 modified l_cbi_number type */
442 CURSOR check_cbi_num (l_cbi_number
443 ar_cons_inv.cons_billing_number%TYPE) IS
444 SELECT cons_inv_id
445 FROM ar_cons_inv
446 WHERE cons_billing_number = l_cbi_number
447 AND cons_inv_type = 'MINV';
448
449 l_cons_inv_id ar_cons_inv.cons_inv_id%TYPE;
450 old_cons_billing_number ar_cons_inv.cons_billing_number%TYPE:='$$~$$';
451 l_tot_rec_updated NUMBER:=0;
452
453 BEGIN
454 --
455 arp_standard.debug('arp_group_inv.update_ps()+');
456 --
457 FOR c01_rec IN c01 LOOP
458 --
459 IF old_cons_billing_number <> c01_rec.cons_billing_number THEN
460
461 /* bug3886862 if there is cons inv already, get the cons_inv_id */
462 OPEN check_cbi_num(c01_rec.cons_billing_number) ;
463 FETCH check_cbi_num INTO l_cons_inv_id ;
464
465 /* the cons_billing_number is new */
466 IF check_cbi_num%NOTFOUND
467 THEN
468 --
469 SELECT ar_cons_inv_s.NEXTVAL INTO l_cons_inv_id FROM dual;
470 --
471 END IF;
472 CLOSE check_cbi_num ;
473
474 old_cons_billing_number := c01_rec.cons_billing_number;
475 --
476 -- Store the Group Invoice number in the PL/SQL table
477 -- This information will be useful during the group invoice creation.
478 --
479 g_cons_billing_number(l_cons_inv_id) := c01_rec.cons_billing_number;
480
481 END IF;
482 --
483 -- Store the cons_inv_id in the PL/SQL table
484 --
485 g_cons_inv_id(c01_rec.customer_trx_id) := l_cons_inv_id;
486 --
487 UPDATE ar_payment_schedules
488 SET cons_inv_id = l_cons_inv_id
489 WHERE customer_trx_id = c01_rec.customer_trx_id;
490 --
491 l_tot_rec_updated := l_tot_rec_updated + SQL%ROWCOUNT;
492 --
493 END LOOP;
494
495 --
496 arp_standard.debug('Updated :' || l_tot_rec_updated);
497 --
498 arp_standard.debug('arp_group_inv.update_ps()-');
499 --
500 EXCEPTION
501 WHEN OTHERS THEN
502 IF PG_DEBUG in ('Y', 'C') THEN
503 arp_standard.debug( ' Exception: arp_group_inv.update_ps()');
504 END IF;
505 RAISE;
506 END update_ps;
507
508 /*----------------------------------------------------------------------------*
509 | PROCEDURE |
510 | validate_group |
511 | |
512 | DESCRIPTION |
513 | Will check if any of the invoices having the same consolidated billing |
514 | no have been rejected. If so, then the other invoices are also rejected |
515 | |
516 | SCOPE |
517 | Public |
518 | |
519 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
520 | None |
521 | |
522 | ARGUMENTS |
523 | IN : P_request_id - Request id |
524 | OUT: O_rows_rejected - No of Rejections |
525 | |
526 | RETURNS : NONE |
527 | |
528 | NOTES |
529 | Is called from raavcb.lpc |
530 | |
531 | MODIFICATION HISTORY |
532 | 11-JUN-2002 Sahana Created |
533 *----------------------------------------------------------------------------*/
534 PROCEDURE validate_group(p_request_id IN NUMBER,o_rows_rejected OUT NOCOPY NUMBER) IS
535 l_message_text VARCHAR2(2000);
536 BEGIN
537 arp_standard.debug('arp_group_inv.validate_group()+');
538
539 l_message_text := arp_standard.fnd_message('AR_RAXTRX-1819');
540
541 INSERT INTO RA_INTERFACE_ERRORS
542 (interface_line_id,
543 message_text,
544 invalid_value,
545 org_id)
546 SELECT l.interface_line_id,
547 l_message_text,
548 l.cons_billing_number,
549 l.org_id
550 FROM ra_interface_lines_gt l
551 WHERE l.request_id = p_request_id
552 AND l.cons_billing_number IS NOT NULL
553 AND nvl(l.interface_status,'~') <> 'P'
554 AND l.link_to_line_id is null
555 AND l.customer_trx_id is not null
556 AND EXISTS
557 ( SELECT /*+ leading(L2) use_nl_with_index(E, RA_INTERFACE_ERRORS_N1) */ 'x'
558 FROM ra_interface_errors e, ra_interface_lines_gt l2
559 WHERE e.INTERFACE_LINE_ID = l2.INTERFACE_LINE_ID
560 AND l2.cons_billing_number = l.cons_billing_number
561 AND l2.request_id = l.request_id );
562
563 o_rows_rejected := SQL%ROWCOUNT;
564
565 arp_standard.debug('validate_group: No of Invoices Rejected- '||
566 o_rows_rejected);
567
568 arp_standard.debug('arp_group_inv.validate_group()-');
569
570 EXCEPTION
571 WHEN OTHERS THEN
572 IF PG_DEBUG in ('Y', 'C') THEN
573 arp_standard.debug( ' Exception: validate_group: '||SQLERRM);
574 END IF;
575 RAISE;
576 END validate_group;
577
578 END arp_group_inv;