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