[Home] [Help]
PACKAGE BODY: APPS.JL_BR_AR_GENERATE_DEBIT_MEMO
Source
1 PACKAGE BODY jl_br_ar_generate_debit_memo AS
2 /*$Header: jlbrrdmb.pls 120.18.12010000.2 2008/08/04 12:49:28 vgadde ship $*/
3
4 /************************************************************************/
5 /* Function get_account */
6 /* Parameters: account type, */
7 /* transaction id, */
8 /* salesrep id */
9 /* Purpose: get the account id from Gl_code_combination */
10 /************************************************************************/
11 FUNCTION get_account(
12 X_account_type VARCHAR2,
13 X_cust_trx_type_id NUMBER,
14 X_salesrep_id NUMBER,
15 x_int_revenue_ccid NUMBER,
16 x_struct_num NUMBER,
17 x_error_code OUT NOCOPY NUMBER,
18 x_error_msg OUT NOCOPY VARCHAR2,
19 x_token OUT NOCOPY VARCHAR2
20 ) RETURN NUMBER IS
21
22 X_increment NUMBER;
23 X_counter NUMBER;
24 X_gl_default_id NUMBER;
25 X_select1 INTEGER;
26 X_select2 INTEGER;
27 X_cust_rev_id NUMBER;
28 X_cust_rec_id NUMBER;
29 X_sale_rev_id NUMBER;
30 X_sale_rec_id NUMBER;
31 X_remittance_bank_account_id NUMBER;
32 X_receipt_method_id NUMBER;
33 X_currency_code NUMBER;
34 X_gl_id NUMBER;
35 X_amount_id NUMBER;
36 X_segment_name VARCHAR2(30);
37 X_table_name VARCHAR2(100);
38 X_constant VARCHAR2(50);
39 X_segment_amount VARCHAR2(25);
40 X_condition VARCHAR2(500);
41 X_selection INTEGER;
42 X_first_time boolean;
43 X_memo_rev_id NUMBER;
44 X_ccid NUMBER;
45 X_segs FND_FLEX_EXT.SegmentArray;
46 X_dummy BOOLEAN;
47 x_flexfield VARCHAR2(2000);
48 x_dyn_insert VARCHAR2(2) ;
49 x_delimiter VARCHAR2(1);
50
51 /* Select the segment number, table name and constant to get the account */
52
53 CURSOR c1 IS
54 SELECT segment, table_name, constant
55 FROM ra_account_default_segments
56 WHERE gl_default_id = X_gl_default_id;
57
58 BEGIN
59 X_first_time := TRUE;
60 x_dyn_insert := 'N';
61
62 /* Get the id to access RA_ACCOUNT_DEFAULT_SEGMENTS table */
63
64 SELECT gl_default_id
65 INTO X_gl_default_id
66 FROM ra_account_defaults
67 WHERE type=X_account_type;
68
69 /* Get number of segments to the transaction type */
70
71 SELECT count(*)
72 INTO X_counter
73 FROM ra_account_default_segments
74 WHERE gl_default_id = X_gl_default_id;
75
76 /* Get the account id to the Revene and Receivables tables
77 RA_CUST_TRX_TYPES e RA_SALESREPS AR_MEMO_LINES*/
78
79 BEGIN
80 SELECT gl_id_rev, gl_id_rec
81 INTO X_cust_rev_id, X_cust_rec_id
82 FROM ra_cust_trx_types
83 WHERE cust_trx_type_id = X_cust_trx_type_id;
84
85 EXCEPTION
86 WHEN NO_DATA_FOUND THEN NULL;
87 END;
88
89 BEGIN
90 SELECT gl_id_rev, gl_id_rec
91 INTO X_sale_rev_id, X_sale_rec_id
92 FROM ra_salesreps
93 WHERE salesrep_id = X_salesrep_id;
94
95 EXCEPTION
96 WHEN NO_DATA_FOUND THEN NULL;
97 END;
98
99 x_error_code := 0;
100
101 OPEN c1;
102 FOR X_increment IN 1..X_counter
103 LOOP
104 FETCH c1 INTO X_segment_name, X_table_name, X_constant;
105 EXIT WHEN c1%NOTFOUND;
106
107 X_select1 := DBMS_SQL.OPEN_CURSOR;
108
109 /* Se o segmento nao e' uma X_constant entao
110 Verifica se o segmento esta' na tab. RA_CUST_TRX_TYPES entao
111 se o tipo de conta for Revenue Account entao
112 busca o id da conta Rev. da RA_CUST_TRX_TYPES
113 senao se o tipo de conta for Receivable Account entao
114 busca o id da conta Rec. da RA_CUST_TRX_TYPES
115 senao verifica se segmento esta tab. RA_SALESREPS entao
116 se o tipo de conta for Revenue Account entao
117 busca o id da conta Rev. da RA_SALESREPS
118 senao se o tipo de conta for Receivable Account entao
119 busca o id da conta Rec. da RA_SALESREPS
120 */
121 IF X_constant IS NULL THEN
122 IF X_table_name = 'RA_CUST_TRX_TYPES' THEN
123 IF X_account_type = 'REV' THEN
124 X_gl_id := X_cust_rev_id;
125 ELSIF X_account_type = 'REC' THEN
126 X_gl_id := X_cust_rec_id;
127 END IF;
128 ELSIF X_table_name = 'RA_SALESREPS' THEN
129 IF X_account_type = 'REV' THEN
130 X_gl_id := X_sale_rev_id;
131 ELSIF X_account_type = 'REC' THEN
132 X_gl_id := X_sale_rec_id;
133 END IF;
134 /*ELSIF X_table_name = 'RA_STD_TRX_LINES' THEN*/
135 ELSE
136 X_gl_id := x_int_revenue_ccid;
137 END IF;
138
139 /* Mount the select dynamically to find the segment (X) in
140 gl_code_combinations table */
141
142 /*Bug 2939830 - SQL Bind compliance project
143 DBMS_SQL.PARSE(X_select1, 'SELECT '||X_segment_name||'
144 FROM gl_code_combinations WHERE code_combination_id = '
145 || X_gl_id, DBMS_SQL.v7);
146 */
147
148 DBMS_SQL.PARSE(X_select1, 'SELECT '||X_segment_name||'
149 FROM gl_code_combinations WHERE code_combination_id = :x and chart_of_accounts_id = :y ', DBMS_SQL.v7);
150
151 DBMS_SQL.BIND_VARIABLE (X_select1, ':x', X_gl_id) ;
152 DBMS_SQL.BIND_VARIABLE (X_select1, ':y', X_struct_num) ;
153 -- End of fix for SQL Bind Compliance
154
155 /* Mount the where clause to get the gl_code_combinations id */
156
157 DBMS_SQL.DEFINE_COLUMN(X_select1,1,X_segment_amount,25);
158 X_selection := DBMS_SQL.EXECUTE(X_select1);
159 IF DBMS_SQL.FETCH_ROWS (X_select1) > 0 THEN
160 DBMS_SQL.COLUMN_VALUE(X_select1,1,X_segment_amount);
161 IF X_first_time THEN
162 X_condition := X_segment_name||'='||''''||X_segment_amount||'''';
163 X_segs(x_increment) := x_segment_amount;
164 X_first_time := FALSE;
165 ELSE
166 X_condition := X_condition||' and '||X_segment_name||'='||''''||X_segment_amount||'''';
167 X_segs(x_increment) := x_segment_amount;
168 END IF;
169 END IF;
170 ELSE
171 IF X_first_time THEN
172 X_condition := X_segment_name||'='||''''||X_constant||'''';
173 X_segs(x_increment) := x_constant;
174 X_first_time := FALSE;
175 ELSE
176 X_condition := X_condition||' and '||X_segment_name||'='||''''||X_constant||'''';
177 X_segs(x_increment) := x_constant;
178 END IF;
179 END IF;
180 DBMS_SQL.CLOSE_CURSOR(X_select1);
181 END LOOP;
182
183 X_condition := X_condition||' and chart_of_accounts_id ='||to_char(x_struct_num);
184
185 /* Mount the select to get the account on gl_code_combinations */
186 -- Bug 2089230 following close cursor was moved above - before end loop.
187 --DBMS_SQL.CLOSE_CURSOR(X_select1);
188 BEGIN
189 X_select2 := DBMS_SQL.OPEN_CURSOR;
190
191 /*ignored conversion for bug 2939830 since entire where clause cannot be passed as bind variable - GBUZSAK*/
192 DBMS_SQL.PARSE(X_select2,'SELECT code_combination_id FROM gl_code_combinations WHERE '||X_condition,DBMS_SQL.v7);
193
194 DBMS_SQL.DEFINE_COLUMN(X_select2,1,X_amount_id);
195 X_selection := DBMS_SQL.EXECUTE(X_select2);
196 IF DBMS_SQL.FETCH_ROWS (X_select2) > 0 THEN
197 DBMS_SQL.COLUMN_VALUE(X_select2,1,X_amount_id);
198 END IF;
199 DBMS_SQL.CLOSE_CURSOR(X_select2);
200 EXCEPTION
201 WHEN OTHERS THEN NULL;
202 END;
203
204 CLOSE c1;
205
206 IF x_amount_id is NULL THEN
207
208 x_delimiter := fnd_flex_ext.get_delimiter('SQLGL','GL#',x_struct_num);
209
210 BEGIN
211
212 SELECT DYNAMIC_INSERTS_ALLOWED_FLAG
213 INTO x_dyn_insert
214 FROM fnd_id_flex_Structures ffs
215 WHERE ffs.APPLICATION_ID = 101
216 AND ffs.ID_FLEX_CODE = 'GL#'
217 AND ffs.ID_FLEX_NUM = x_struct_num;
218
219 EXCEPTION
220 WHEN OTHERS THEN
221 x_dyn_insert := 'N';
222 END;
223
224 IF X_dyn_insert = 'Y' THEN
225
226 x_dummy := FND_FLEX_EXT.get_combination_id ('SQLGL', 'GL#',x_struct_num,
227 sysdate, x_counter,
228 x_segs,x_amount_id);
229
230 IF NOT(x_dummy) THEN
231
232 x_flexfield := fnd_flex_ext.concatenate_segments( x_counter,
233 x_segs,
234 x_delimiter);
235
236 x_error_msg := 'JL_CO_FA_CCID_NOT_CREATED';
237 x_error_code := 62324;
238 x_token := x_flexfield;
239
240 END IF;
241
242 ELSE
243
244 x_error_msg := 'JL_DYN_INS_NOT_ALLOWED';
245
246 x_error_code := 61245;
247
248 END IF;
249
250 END IF;
251
252 RETURN (X_amount_id);
253
254 END get_account;
255
256 /************************************************************************/
257 /* Procedure ins_ra_batches */
258 /* Purpose : Get all the fields to insert row in ra_batches */
259 /************************************************************************/
260
261 PROCEDURE ins_ra_batches (
262 X_batch_source_id IN NUMBER,
263 X_invoice_amount IN NUMBER,
264 X_invoice_currency_code IN VARCHAR2,
265 X_user_id IN NUMBER,
266 X_batch_id IN OUT NOCOPY NUMBER
267 ) IS
268 X_batch_name VARCHAR2(50);
269 X_set_of_books_id NUMBER(15);
270 X_batch_rec ra_batches%ROWTYPE;
271 BEGIN
272
273 SELECT set_of_books_id
274 INTO X_set_of_books_id
275 FROM ar_system_parameters;
276
277 X_batch_rec.batch_date := sysdate;
278 X_batch_rec.gl_date := sysdate;
279 X_batch_rec.status := 'NB';
280 X_batch_rec.batch_source_id := X_batch_source_id;
281 X_batch_rec.set_of_books_id := X_set_of_books_id;
282 X_batch_rec.control_count := 1;
283 X_batch_rec.control_amount := X_invoice_amount;
284 X_batch_rec.currency_code := X_invoice_currency_code;
285 --arp_tbat_pkg.insert_p(X_batch_rec, X_batch_id, X_batch_name);
286
287 END ins_ra_batches;
288
289 /************************************************************************/
290 /* Function : Generate_Interest_DM_Number */
291 /* Purpose : Get the number to the transaction */
292 /************************************************************************/
293
294 FUNCTION generate_interest_DM_number(
295 X_original_customer_trx_id NUMBER,
296 X_payment_schedule_id NUMBER
297 ) RETURN VARCHAR2 IS
298 X_next_sequence NUMBER;
299 X_first_position NUMBER;
300 X_terms_sequence NUMBER;
301 X_trx_number VARCHAR2(30);
302 BEGIN
303
304 BEGIN
305
306 /* Find the string '-NDJ' to know which number will be
307 the Debit Memo transaction */
308
309 SELECT nvl(max(instr(trx_number,'-NDJ')) + 4,0) --bug 6011423
310 INTO X_first_position
311 FROM ra_customer_trx
312 WHERE related_customer_trx_id=X_original_customer_trx_id
313 AND trx_number like '%-NDJ%';
314
315 SELECT nvl(MAX(TO_NUMBER(SUBSTR(trx_number,X_first_position,LENGTH(trx_number)-
316 X_first_position+1)))+1,1) --bug 6011423
317 INTO X_next_sequence
318 FROM ra_customer_trx
319 WHERE related_customer_trx_id=X_original_customer_trx_id
320 AND trx_number LIKE '%-NDJ%';
321
322 /* If the selects failure, then this is the first
323 Interest Debit Memo to this transaction */
324
325 EXCEPTION
326 WHEN NO_DATA_FOUND THEN
327 X_next_sequence := 1;
328 END;
329
330 /* Get the transaction number to mount the Interest Debit
331 Memo transaction number */
332
333 SELECT trx_number, terms_sequence_number
334 INTO X_trx_number, X_terms_sequence
335 FROM ar_payment_schedules
336 WHERE payment_schedule_id = X_payment_schedule_id;
337
338 X_trx_number := X_trx_number||'-'||X_terms_sequence||'-NDJ'||X_next_sequence;
339 RETURN X_trx_number;
340 END generate_interest_DM_number;
341
342 /************************************************************************/
343 /* Procedure ins_ra_customer_trx */
344 /* Purpose : Get the fields to insert into ra_customer_trx */
345 /************************************************************************/
346
347 PROCEDURE ins_ra_customer_trx (
348 X_inv_cust_trx_id IN NUMBER,
349 X_new_cust_trx_id IN OUT NOCOPY NUMBER,
350 X_set_of_books_id IN OUT NOCOPY NUMBER,
351 X_lastlogin IN OUT NOCOPY NUMBER,
352 X_primary_salesrep_id IN OUT NOCOPY NUMBER,
353 X_billto_customer_id IN OUT NOCOPY NUMBER,
354 X_billto_site_use_id IN OUT NOCOPY NUMBER,
355 X_invoice_currencycode IN OUT NOCOPY VARCHAR2,
356 X_trx_number IN OUT NOCOPY VARCHAR2,
357 X_termid IN OUT NOCOPY NUMBER,
358 X_cust_trx_type_id IN NUMBER,
359 X_payment_schedule_id IN NUMBER,
360 X_user_id IN NUMBER,
361 X_batch_source_id IN NUMBER,
362 X_receipt_method_id IN NUMBER,
363 X_batch_id IN NUMBER,
364 X_idm_date IN DATE
365 ) IS
366 X_sold_to_customer_id NUMBER(15);
367 X_ship_to_customer_id NUMBER(15);
368 X_ship_to_site_use_id NUMBER(15);
369 X_remit_to_address_id NUMBER(15);
370 X_printing_option VARCHAR2(20);
371 X_territory_id NUMBER(15);
372 X_attribute1 VARCHAR2(150);
373 X_global_attribute1 VARCHAR2(150);
374 X_global_attribute2 VARCHAR2(150);
375 X_global_attribute3 VARCHAR2(150);
376 X_global_attribute4 VARCHAR2(150);
377 X_global_attribute5 VARCHAR2(150);
378 X_global_attribute6 VARCHAR2(150);
379 X_global_attribute7 VARCHAR2(150);
380 X_org_id NUMBER(15);
381
382 l_trx_rec ra_customer_trx%ROWTYPE;
383
384
385 BEGIN
386 /* SELECT ra_customer_trx_s.nextval
387 INTO X_new_cust_trx_id
388 FROM sys.dual;
389 */
390
391 X_trx_number := jl_br_ar_generate_debit_memo.generate_interest_DM_number(X_inv_cust_trx_id,
392 X_payment_schedule_id);
393
394 SELECT to_number(global_attribute20)
395 INTO X_termid
396 FROM ar_system_parameters;
397
398 SELECT last_update_login,
399 set_of_books_id,
400 sold_to_customer_id,
401 bill_to_customer_id,
402 bill_to_site_use_id,
403 ship_to_customer_id,
404 ship_to_site_use_id,
405 remit_to_address_id,
406 primary_salesrep_id,
407 printing_option,
408 territory_id,
409 invoice_currency_code,
410 attribute1,
411 global_attribute1,
412 global_attribute2,
413 global_attribute3,
414 global_attribute4,
415 global_attribute5,
416 global_attribute6,
417 global_attribute7,
418 org_id
419 INTO X_lastlogin,
420 X_set_of_books_id,
421 X_sold_to_customer_id,
425 X_ship_to_site_use_id,
422 X_billto_customer_id,
423 X_billto_site_use_id,
424 X_ship_to_customer_id,
426 X_remit_to_address_id,
427 X_primary_salesrep_id,
428 X_printing_option,
429 X_territory_id,
430 X_invoice_currencycode,
431 X_attribute1,
432 X_global_attribute1,
433 X_global_attribute2,
434 X_global_attribute3,
435 X_global_attribute4,
436 X_global_attribute5,
437 X_global_attribute6,
438 X_global_attribute7,
439 X_org_id
440 FROM ra_customer_trx
441 WHERE customer_trx_id = X_inv_cust_trx_id;
442
443 /* INSERT INTO ra_customer_trx (
444 customer_trx_id,
445 last_update_date,
446 last_updated_by,
447 creation_date,
448 created_by,
449 last_update_login,
450 trx_number,
451 related_customer_trx_id,
452 cust_trx_type_id,
453 trx_date,
454 set_of_books_id,
455 batch_source_id,
456 batch_id,
457 sold_to_customer_id,
458 bill_to_customer_id,
459 bill_to_site_use_id,
460 ship_to_customer_id,
461 ship_to_site_use_id,
462 remit_to_address_id,
463 term_id,
464 primary_salesrep_id,
465 printing_option,
466 printing_pending,
467 territory_id,
468 invoice_currency_code,
469 attribute1,
470 complete_flag,
471 receipt_method_id,
472 status_trx,
473 default_tax_exempt_flag,
474 created_from,
475 global_attribute1,
476 global_attribute2,
477 global_attribute3,
478 global_attribute4,
479 global_attribute5,
480 global_attribute6,
481 global_attribute7
482 ) VALUES (
483 X_new_cust_trx_id,
484 sysdate,
485 X_user_id,
486 sysdate,
487 X_user_id,
488 X_lastlogin,
489 X_trx_number,
490 X_inv_cust_trx_id,
491 X_cust_trx_type_id,
492 X_idm_date,
493 X_set_of_books_id,
494 X_batch_source_id,
495 X_batch_id,
496 X_sold_to_customer_id,
497 X_billto_customer_id,
498 X_billto_site_use_id,
499 X_ship_to_customer_id,
500 X_ship_to_site_use_id,
501 X_remit_to_address_id,
502 X_termid,
503 X_primary_salesrep_id,
504 X_printing_option,
505 'N',
506 X_territory_id,
507 X_invoice_currencycode,
508 X_attribute1,
509 'Y',
510 X_receipt_method_id,
511 'OP',
512 'S',
513 'RAXMATRX',
514 X_global_attribute1,
515 X_global_attribute2,
516 X_global_attribute3,
517 X_global_attribute4,
518 X_global_attribute5,
519 X_global_attribute6,
520 X_global_attribute7
521 );
522 */
523
524 /* Replace Insert by AR's Table Handlers. Bug # 2249731 */
525
526 l_trx_rec.last_update_date := sysdate;
527 l_trx_rec.last_updated_by := X_user_id;
528 l_trx_rec.creation_date := sysdate;
529 l_trx_rec.created_by := X_user_id;
530 l_trx_rec.last_update_login := X_lastlogin;
531 l_trx_rec.trx_number := X_trx_number;
532 l_trx_rec.related_customer_trx_id := X_inv_cust_trx_id;
533 l_trx_rec.cust_trx_type_id := X_cust_trx_type_id;
534 l_trx_rec.trx_date := X_idm_date ;
535 l_trx_rec.set_of_books_id := X_set_of_books_id;
536 l_trx_rec.batch_source_id := X_batch_source_id;
537 l_trx_rec.batch_id := X_batch_id;
538 l_trx_rec.sold_to_customer_id := X_sold_to_customer_id;
539 l_trx_rec.bill_to_customer_id := X_billto_customer_id;
540 l_trx_rec.bill_to_site_use_id := X_billto_site_use_id;
541 l_trx_rec.ship_to_customer_id := X_ship_to_customer_id;
542 l_trx_rec.ship_to_site_use_id := X_ship_to_site_use_id ;
543 l_trx_rec.remit_to_address_id := X_remit_to_address_id;
544 l_trx_rec.term_id := X_termid;
545 l_trx_rec.primary_salesrep_id := X_primary_salesrep_id;
546 l_trx_rec.printing_option := X_printing_option;
547 l_trx_rec.printing_pending := 'N';
548 l_trx_rec.territory_id := X_territory_id;
549 l_trx_rec.invoice_currency_code := X_invoice_currencycode;
550 l_trx_rec.attribute1 := X_attribute1;
551 l_trx_rec.complete_flag := 'Y';
552 l_trx_rec.receipt_method_id := X_receipt_method_id;
553 l_trx_rec.status_trx := 'OP';
554 l_trx_rec.default_tax_exempt_flag := 'S';
555 l_trx_rec.created_from := 'RAXMATRX';
556 l_trx_rec.global_attribute1 := X_global_attribute1;
557 l_trx_rec.global_attribute2 := X_global_attribute2;
558 l_trx_rec.global_attribute3 := X_global_attribute3;
559 l_trx_rec.global_attribute4 := X_global_attribute4;
560 l_trx_rec.global_attribute5 := X_global_attribute5;
561 l_trx_rec.global_attribute6 := X_global_attribute6;
562 l_trx_rec.global_attribute7 := X_global_attribute7;
563 l_trx_rec.org_id := X_org_id;
564
565 arp_ct_pkg.insert_p(l_trx_rec, X_trx_number, X_new_cust_trx_id);
566
567 EXCEPTION
568 WHEN NO_DATA_FOUND THEN
572
569 RAISE_APPLICATION_ERROR( -20000,
570 'IL: Id da Transacao Invalido'||sqlerrm );
571 END ins_ra_customer_trx;
573 /************************************************************************/
574 /* Procedure ins_ra_customer_trx_lines */
575 /* Purpose : Get the fields to insert into ra_customer_trx_lines */
576 /************************************************************************/
577
578 PROCEDURE ins_ra_customer_trx_lines (
579 X_new_customer_trx_id IN NUMBER,
580 X_invoice_amount IN NUMBER,
581 X_set_of_books_id IN NUMBER,
582 X_user_id IN NUMBER,
583 X_last_login IN NUMBER,
584 X_customertrx_line_id IN OUT NOCOPY NUMBER
585 ) IS
586 l_org_id NUMBER(15);
587 BEGIN
588 SELECT ra_customer_trx_lines_s.nextval
589 INTO X_customertrx_line_id
590 FROM dual;
591
592 SELECT org_id into l_org_id
593 FROM ra_customer_trx_all
594 Where customer_trx_id = x_new_customer_trx_id;
595
596 INSERT INTO ra_customer_trx_lines (
597 customer_trx_line_id,
598 last_update_date,
599 last_updated_by,
600 creation_date,
601 created_by,
602 last_update_login,
603 customer_trx_id,
604 line_number,
605 set_of_books_id,
606 description,
607 quantity_invoiced,
608 unit_selling_price,
609 line_type,
610 extended_amount,
611 revenue_amount,
612 tax_exempt_flag,
613 org_id
614 ) VALUES (
615 X_customertrx_line_id,
616 sysdate,
617 X_user_id,
618 sysdate,
619 X_user_id,
620 X_last_login,
621 X_new_customer_trx_id,
622 '1',
623 X_set_of_books_id,
624 'Nota de Debito Juros',
625 '1',
626 X_invoice_amount,
627 'LINE',
628 X_invoice_amount,
629 X_invoice_amount,
630 'S',
631 l_org_id
632 );
633 END ins_ra_customer_trx_lines;
634
635 /***************************************************************************/
636 /* Procedure ins_ra_cust_trx_line_salesreps */
637 /* Purpose : Get the fields to insert into ra_cust_trx_line_salesreps */
638 /***************************************************************************/
639 PROCEDURE ins_ra_cust_trx_line_salesreps (
640 X_new_cust_trx_id IN NUMBER,
641 X_new_cust_trx_line_id IN NUMBER,
642 X_salesrep_id IN NUMBER,
643 X_user_id IN NUMBER,
644 X_last_login IN NUMBER,
645 X_invoice_amount IN NUMBER
646 ) IS
647 l_org_id NUMBER(15);
648 BEGIN
649 SELECT org_id into l_org_id
650 FROM ra_customer_trx_all where
651 customer_trx_id = x_new_cust_trx_id;
652
653 INSERT INTO ra_cust_trx_line_salesreps (
654 cust_trx_line_salesrep_id,
655 last_update_date,
656 last_updated_by,
657 creation_date,
658 created_by,
659 last_update_login,
660 customer_trx_id,
661 salesrep_id,
662 revenue_percent_split,
663 revenue_amount_split,
664 org_id
665 ) VALUES (
666 ra_cust_trx_line_salesreps_s.nextval,
667 sysdate,
668 X_user_id,
669 sysdate,
670 X_user_id,
671 X_last_login,
672 X_new_cust_trx_id,
673 X_salesrep_id,
674 '100',
675 X_invoice_amount,
676 l_org_id
677 );
678
679 INSERT INTO ra_cust_trx_line_salesreps (
680 cust_trx_line_salesrep_id,
681 last_update_date,
682 last_updated_by,
683 creation_date,
684 created_by,
685 last_update_login,
686 customer_trx_id,
687 customer_trx_line_id,
688 salesrep_id,
689 revenue_percent_split,
690 revenue_amount_split,
691 org_id
692 ) VALUES (
693 ra_cust_trx_line_salesreps_s.nextval,
694 sysdate,
695 X_user_id,
696 sysdate,
697 X_user_id,
698 X_last_login,
699 X_new_cust_trx_id,
700 X_new_cust_trx_line_id,
701 X_salesrep_id,
702 '100',
703 X_invoice_amount,
704 l_org_id
705 );
706 END ins_ra_cust_trx_line_salesreps;
707
708 /************************************************************************/
709 /* Procedure ins_ra_cust_trx_line_gl_dist */
710 /* Purpose : Get the fields to insert into ra_cust_trx_line_gl_dist*/
711 /************************************************************************/
712 PROCEDURE ins_ra_cust_trx_line_gl_dist (
713 X_customer_trx_id IN NUMBER,
714 X_customer_trx_line_id IN OUT NOCOPY NUMBER,
715 X_invoice_amount IN NUMBER,
716 X_set_of_books_id IN NUMBER,
717 X_user_id IN NUMBER,
718 X_batch_source_id IN NUMBER,
719 X_last_login IN NUMBER,
720 X_cust_trx_type_id IN NUMBER,
721 X_salesrep_id IN NUMBER,
722 X_account_type IN VARCHAR,
723 X_idm_date IN DATE,
724 x_int_revenue_ccid IN NUMBER,
725 X_invoice_currency_code IN VARCHAR2,
726 X_minimum_accountable_unit IN NUMBER,
727 X_precision IN NUMBER,
728 x_error_code OUT NOCOPY NUMBER,
729 x_error_msg OUT NOCOPY VARCHAR2,
733 X_gl_date DATE;
730 x_token OUT NOCOPY VARCHAR2
731 ) IS
732 X_code_id NUMBER(15);
734 X_post_gl VARCHAR2(1);
735 --X_latest_rec_flag VARCHAR2(1);
736 X_line_salesrepid NUMBER(15);
737 X_custtrx_line_id NUMBER(15);
738 X_memo_line_id NUMBER(15);
739 X_cust_trx_line_gl_dist_id NUMBER(15);
740 l_dist_rec ra_cust_trx_line_gl_dist%ROWTYPE;
741 x_struct_num NUMBER(15);
742 invalid_account EXCEPTION;
743 l_org_id NUMBER(15);
744
745 BEGIN
746
747 SELECT org_id into l_org_id from ra_customer_trx_all where
748 customer_trx_id = x_customer_trx_id;
749 SELECT chart_of_accounts_id into x_struct_num FROM gl_sets_of_books
750 WHERE set_of_books_id = x_set_of_books_id;
751
752 X_code_id := jl_br_ar_generate_debit_memo.get_account(X_account_type,X_cust_trx_type_id, X_salesrep_id,x_int_revenue_ccid,x_struct_num,x_error_code, x_error_msg,x_token);
753
754 IF X_code_id is NULL THEN
755
756 Raise invalid_account;
757
758 END IF;
759
760 IF X_account_type = 'REC' THEN
761 --X_latest_rec_flag := 'Y'; --Bug 3067731 - ARs table handler handles it
762 X_custtrx_line_id := NULL;
763 X_line_salesrepid := NULL;
764 ELSE
765 BEGIN
766 X_custtrx_line_id := X_customer_trx_line_id;
767 SELECT ra_cust_trx_line_salesreps_s.nextval
768 INTO X_line_salesrepid
769 FROM sys.dual;
770 END;
771 END IF;
772
773 /* Check if this transaction has to be posted to GL */
774 SELECT post_to_gl
775 INTO X_post_gl
776 FROM ra_cust_trx_types
777 WHERE cust_trx_type_id = X_cust_trx_type_id;
778
779 IF X_post_gl = 'Y' THEN
780 X_gl_date := X_idm_date;
781 ELSE
782 X_gl_date := NULL;
783 END IF;
784
785 --Commented out for Bug 3067731; Need to call AR table handler instead of direct insert
786 /* INSERT INTO ra_cust_trx_line_gl_dist (
787 cust_trx_line_gl_dist_id,
788 customer_trx_line_id,
789 code_combination_id,
790 set_of_books_id,
791 last_update_date,
792 last_updated_by,
793 creation_date,
794 created_by,
795 percent,
796 amount,
797 gl_date,
798 cust_trx_line_salesrep_id,
799 original_gl_date,
800 posting_control_id,
801 account_class,
802 customer_trx_id,
803 account_set_flag,
804 acctd_amount,
805 latest_rec_flag
806 ) VALUES (
807 ra_cust_trx_line_gl_dist_s.nextval,
808 X_custtrx_line_id,
809 X_code_id,
810 X_set_of_books_id,
811 sysdate,
812 X_user_id,
813 sysdate,
814 X_user_id,
815 '100',
816 X_invoice_amount,
817 X_gl_date,
818 X_line_salesrepid,
819 X_gl_date,
820 '-3',
821 X_account_type,
822 X_customer_trx_id,
823 'N',
824 X_invoice_amount,
825 X_latest_rec_flag
826 );
827 */
828 l_dist_rec.customer_trx_line_id := x_custtrx_line_id;
829 l_dist_rec.customer_trx_id := x_customer_trx_id;
830 l_dist_rec.code_combination_id := x_code_id;
831 l_dist_rec.set_of_books_id := x_set_of_books_id;
832 l_dist_rec.percent := '100';
833 l_dist_rec.amount := x_invoice_amount;
834 l_dist_rec.gl_date := x_gl_date;
835 l_dist_rec.cust_trx_line_salesrep_id := X_line_salesrepid;
836 l_dist_rec.original_gl_date := x_gl_date;
837 l_dist_rec.account_class := x_account_type;
838 l_dist_rec.account_set_flag := 'N';
839 l_dist_rec.acctd_amount := x_invoice_amount;
840 l_dist_rec.org_id := l_org_id;
841
842 -- Inserted new parameters for fixing 3498430.......
843 ARP_CTLGD_PKG.insert_p (l_dist_rec, X_cust_trx_line_gl_dist_id,NULL,X_invoice_currency_code,X_precision,X_minimum_accountable_unit);
844 --End of fix for bug 3067731
845
846 EXCEPTION
847 WHEN invalid_account THEN
848 Raise;
849 WHEN OTHERS THEN
850 x_error_code := sqlcode;
851 x_error_msg := sqlerrm;
852
853 END ins_ra_cust_trx_line_gl_dist;
854
855 /************************************************************************/
856 /* Procedure ins_ar_payment_schedules */
857 /* Purpose : Get the fields to insert into ar_payment_schedules */
858 /************************************************************************/
859 PROCEDURE ins_ar_payment_schedules (
860 X_user_id IN NUMBER,
861 X_last_login IN NUMBER,
862 X_invoice_amount IN NUMBER,
863 X_invoice_currency_code IN VARCHAR2,
864 X_cust_trx_type_id IN NUMBER,
865 X_customer_id IN NUMBER,
866 X_customer_site_use_id IN NUMBER,
867 X_customer_trx_id IN NUMBER,
868 X_term_id IN NUMBER,
869 X_trx_number IN VARCHAR2,
870 X_idm_date IN DATE
871 ) IS
872 X_payment_scheduleid NUMBER(15);
873 l_org_id NUMBER(15);
874 l_ps_rec ar_payment_schedules%ROWTYPE;
875 X_due_date DATE;
876 BEGIN
877 SELECT ar_payment_schedules_s.nextval
878 INTO X_payment_scheduleid
879 FROM sys.dual;
880
881
885 last_updated_by,
882 /* INSERT INTO ar_payment_schedules (
883 payment_schedule_id,
884 last_update_date,
886 creation_date,
887 created_by,
888 last_update_login,
889 due_date,
890 amount_due_original,
891 amount_due_remaining,
892 number_of_due_dates,
893 status,
894 invoice_currency_code,
895 class,
896 cust_trx_type_id,
897 customer_id,
898 customer_site_use_id,
899 customer_trx_id,
900 term_id,
901 terms_sequence_number,
902 gl_date_closed,
903 actual_date_closed,
904 amount_line_items_original,
905 amount_line_items_remaining,
906 trx_number,
907 trx_date,
908 gl_date,
909 acctd_amount_due_remaining
910 ) VALUES (
911 X_payment_scheduleid,
912 sysdate,
913 X_user_id,
914 sysdate,
915 X_user_id,
916 X_last_login,
917 X_idm_date,
918 X_invoice_amount,
919 X_invoice_amount,
920 '1',
921 'OP',
922 X_invoice_currency_code,
923 'DM',
924 X_cust_trx_type_id,
925 X_customer_id,
926 X_customer_site_use_id,
927 X_customer_trx_id,
928 X_term_id,
929 '1',
930 to_date('31124712','DDMMYYYY'),
931 to_date('31124712','DDMMYYYY'),
932 X_invoice_amount,
933 X_invoice_amount,
934 X_trx_number,
935 X_idm_date,
936 X_idm_date,
937 X_invoice_amount
938 );
939 */
940
941 BEGIN
942 X_due_date := ARPT_SQL_FUNC_UTIL.Get_first_due_date(X_term_id, X_idm_date);
943 EXCEPTION
944 WHEN others THEN
945 X_due_date := X_idm_date;
946 END;
947
948 IF X_due_date is NULL THEN
949 X_due_date := X_idm_date;
950 END IF;
951
952 /* Replace Insert by AR's table handler. Bug # 2249731 */
953
954 SELECT org_id into l_org_id from
955 ra_customer_trx_all
956 where customer_trx_id = X_customer_trx_id;
957 l_ps_rec.last_update_date := sysdate;
958 l_ps_rec.last_updated_by := X_user_id;
959 l_ps_rec.creation_date := sysdate;
960 l_ps_rec.created_by := X_user_id;
961 l_ps_rec.last_update_login := X_last_login;
962 l_ps_rec.due_date := X_due_date;
963 l_ps_rec.amount_due_original := X_invoice_amount;
964 l_ps_rec.amount_due_remaining := X_invoice_amount;
965 l_ps_rec.number_of_due_dates := '1';
966 l_ps_rec.status := 'OP';
967 l_ps_rec.invoice_currency_code := X_invoice_currency_code;
968 l_ps_rec.class := 'DM';
969 l_ps_rec.cust_trx_type_id := X_cust_trx_type_id;
970 l_ps_rec.customer_id := X_customer_id;
971 l_ps_rec.customer_site_use_id := X_customer_site_use_id;
972 l_ps_rec.customer_trx_id := X_customer_trx_id;
973 l_ps_rec.term_id := X_term_id;
974 l_ps_rec.terms_sequence_number := '1';
975 l_ps_rec.gl_date_closed := to_date('31124712','DDMMYYYY');
976 l_ps_rec.actual_date_closed := to_date('31124712','DDMMYYYY');
977 l_ps_rec.amount_line_items_original := X_invoice_amount;
978 l_ps_rec.amount_line_items_remaining := X_invoice_amount;
979 l_ps_rec.trx_number := X_trx_number;
980 l_ps_rec.trx_date := X_idm_date;
981 l_ps_rec.gl_date := X_idm_date;
982 l_ps_rec.org_id := l_org_id;
983 l_ps_rec.acctd_amount_due_remaining := X_invoice_amount;
984
985 arp_ps_pkg.insert_p(l_ps_rec, X_payment_scheduleid);
986
987 END ins_ar_payment_schedules;
988
989 /************************************************************************/
990 /* Procedure sla_create_event */
991 /* Purpose : Call AR procedure to create SLA accounting */
992 /* event for new Debit Memo transaction */
993 /* SLA KI - bug 4301543 */
994 /************************************************************************/
995 PROCEDURE sla_create_event (
996 X_customer_trx_id IN NUMBER
997 ) IS
998 l_ev_rec arp_xla_events.xla_events_type;
999 BEGIN
1000 l_ev_rec.xla_from_doc_id := X_customer_trx_id;
1001 l_ev_rec.xla_to_doc_id := X_customer_trx_id;
1002 l_ev_rec.xla_req_id := NULL;
1003 l_ev_rec.xla_dist_id := NULL;
1004 l_ev_rec.xla_doc_table := 'CT';
1005 l_ev_rec.xla_doc_event := NULL;
1006 l_ev_rec.xla_mode := 'O';
1007 l_ev_rec.xla_call := 'B';
1008 l_ev_rec.xla_fetch_size := 999;
1009 arp_xla_events.create_events(p_xla_ev_rec => l_ev_rec );
1010 END sla_create_event;
1011
1012 PROCEDURE jl_br_interest_debit_memo (
1013 X_original_customer_trx_id IN NUMBER,
1014 X_invoice_amount IN NUMBER,
1015 X_user_id IN NUMBER,
1016 X_cust_trx_type_id IN NUMBER,
1017 X_batch_source_id IN NUMBER,
1018 X_receipt_method_id IN NUMBER,
1019 X_payment_schedule_id IN NUMBER,
1020 X_interest_date IN VARCHAR2,
1021 X_exit OUT NOCOPY VARCHAR2,
1022 x_int_revenue_ccid IN NUMBER,
1023 X_error_code OUT NOCOPY NUMBER,
1024 X_error_msg OUT NOCOPY VARCHAR2,
1025 X_token OUT NOCOPY VARCHAR2
1026 ) IS
1027 X_batch_id NUMBER(15);
1028 X_new_customer_trx_id NUMBER(15);
1029 X_set_of_books_id NUMBER(15);
1030 X_last_login NUMBER(15);
1031 X_salesrep_id NUMBER(15);
1032 X_bill_to_customer_id NUMBER(15);
1033 X_bill_to_site_use_id NUMBER(15);
1034 X_invoice_currency_code VARCHAR2(15);
1035 X_minimum_accountable_unit NUMBER(15);
1036 x_precision NUMBER(15);
1037 X_trx_number VARCHAR2(20);
1038 X_term_id NUMBER(15);
1039 X_new_customer_trx_line_id NUMBER(15);
1040 X_interest_DM_date DATE;
1041
1042 BEGIN
1043 select to_date(X_interest_date,'DD-MM-YYYY')
1044 into X_interest_DM_date
1045 from dual;
1046
1047 -----------------------------------------------------
1048 -- Bug 3378555. Retrieves information for the invoice
1049 -- currency code.
1050 -----------------------------------------------------
1051 SELECT invoice_currency_code, minimum_accountable_unit, precision
1052 INTO X_invoice_currency_code, x_minimum_accountable_unit, x_precision
1053 FROM ra_customer_trx, fnd_currencies_vl
1054 WHERE customer_trx_id = X_original_customer_trx_id
1055 AND invoice_currency_code = currency_code;
1056
1057 jl_br_ar_generate_debit_memo.ins_ra_batches ( X_batch_source_id,
1058 X_invoice_amount,
1059 X_invoice_currency_code,
1060 X_user_id,
1061 X_batch_id );
1062
1063
1064 jl_br_ar_generate_debit_memo.ins_ra_customer_trx (
1065 X_original_customer_trx_id,
1066 X_new_customer_trx_id,
1067 X_set_of_books_id,
1068 X_last_login,
1069 X_salesrep_id,
1070 X_bill_to_customer_id,
1071 X_bill_to_site_use_id,
1072 X_invoice_currency_code,
1073 X_trx_number,
1074 X_term_id,
1075 X_cust_trx_type_id,
1076 X_payment_schedule_id,
1077 X_user_id,
1078 X_batch_source_id,
1079 X_receipt_method_id,
1080 X_batch_id,
1081 X_interest_DM_date
1082 );
1083
1084 jl_br_ar_generate_debit_memo.ins_ra_customer_trx_lines (
1085 X_new_customer_trx_id,
1086 X_invoice_amount,
1087 X_set_of_books_id,
1088 X_user_id,
1089 X_last_login,
1090 X_new_customer_trx_line_id
1091 );
1092 IF X_salesrep_id IS NOT NULL THEN
1093 jl_br_ar_generate_debit_memo.ins_ra_cust_trx_line_salesreps (
1094 X_new_customer_trx_id,
1095 X_new_customer_trx_line_id,
1096 X_salesrep_id,
1097 X_user_id,
1098 X_last_login,
1099 X_invoice_amount
1100 );
1101 END IF;
1102
1103 jl_br_ar_generate_debit_memo.ins_ra_cust_trx_line_gl_dist (
1104 X_new_customer_trx_id,
1105 X_new_customer_trx_line_id,
1106 X_invoice_amount,
1107 X_set_of_books_id,
1108 X_user_id,
1109 X_batch_source_id,
1110 X_last_login,
1111 X_cust_trx_type_id,
1112 X_salesrep_id,
1113 'REC',
1114 X_interest_DM_date,
1115 x_int_revenue_ccid,
1116 X_invoice_currency_code,
1117 X_minimum_accountable_unit,
1118 x_precision,
1119 x_error_code,
1120 x_error_msg,
1121 x_token
1122 );
1123
1124 jl_br_ar_generate_debit_memo.ins_ra_cust_trx_line_gl_dist (
1125 X_new_customer_trx_id,
1126 X_new_customer_trx_line_id,
1127 X_invoice_amount,
1128 X_set_of_books_id,
1129 X_user_id,
1130 X_batch_source_id,
1131 X_last_login,
1132 X_cust_trx_type_id,
1133 X_salesrep_id,
1134 'REV',
1135 X_interest_DM_date,
1136 x_int_revenue_ccid,
1137 X_invoice_currency_code,
1138 x_minimum_accountable_unit,
1139 x_precision,
1140 x_error_code,
1141 x_error_msg,
1142 x_token
1143 );
1144
1145 jl_br_ar_generate_debit_memo.ins_ar_payment_schedules (
1146 X_user_id,
1147 X_last_login,
1148 X_invoice_amount,
1149 X_invoice_currency_code,
1150 X_cust_trx_type_id,
1151 X_bill_to_customer_id,
1152 X_bill_to_site_use_id,
1153 X_new_customer_trx_id,
1154 X_term_id,
1155 X_trx_number,
1156 X_interest_DM_date
1157 );
1158
1159 /* SLA KI - bug 4301543 */
1160 jl_br_ar_generate_debit_memo.sla_create_event (
1161 X_new_customer_trx_id
1162 );
1163
1164 X_exit := '0';
1165 END jl_br_interest_debit_memo;
1166
1167 END jl_br_ar_generate_debit_memo;