PACKAGE BODY: APPS.JL_AR_AR_PREFIX_TRX_NUM

Source

1 PACKAGE BODY JL_AR_AR_PREFIX_TRX_NUM AS
2 /*$Header: jlarrutb.pls 120.3 2005/04/07 18:24:17 appradha ship $*/
3 
4 PROCEDURE update_trx_number_date (
5   p_batch_source_id           IN     ra_customer_trx_all.batch_source_id%TYPE,
6   p_trx_number                IN OUT NOCOPY ra_customer_trx_all.trx_number%TYPE,
7   p_trx_date                  IN OUT NOCOPY ra_customer_trx_all.trx_date%TYPE )
8 IS
9 
10   l_trx_num_cursor          INTEGER;
11   l_org_id                  NUMBER;
12   l_count                   NUMBER;
13   l_last_trx_date           DATE;
14   l_document_letter         VARCHAR2(1);
15   l_branch_number           VARCHAR2(4);
16   l_imported_source_id      ra_batch_sources_all.batch_source_id%TYPE;
17   l_auto_trx_numbering_flag ra_batch_sources_all.auto_trx_numbering_flag%TYPE;
18   l_batch_source_type       ra_batch_sources_all.batch_source_type%TYPE;
19   l_trx_number              ra_customer_trx_all.trx_number%TYPE;
20   l_country_code            VARCHAR2(5);
21 
22 
23 BEGIN
24 
25    l_org_id := MO_GLOBAL.get_current_org_id;
26 
27    l_country_code := JG_ZZ_SHARED_PKG.GET_COUNTRY(l_org_id,null);
28    IF l_country_code = 'AR' THEN
29 
30      --l_org_id := fnd_profile.value('ORG_ID');
31 
32      SELECT auto_trx_numbering_flag,
33             batch_source_type,
34             to_number(global_attribute1),
35             substr(global_attribute2,1,4),
36             substr(global_attribute3,1,1),
37             to_date(global_attribute4,'YYYY/MM/DD HH24:MI:SS')
38      INTO   l_auto_trx_numbering_flag,
39             l_batch_source_type,
40             l_imported_source_id,
41             l_branch_number,
42             l_document_letter,
43             l_last_trx_date
44      FROM   ra_batch_sources
45      WHERE  batch_source_id = p_batch_source_id;
46 
47      IF l_batch_source_type = 'INV' THEN
48 
49         l_trx_num_cursor := dbms_sql.open_cursor;
50 
51         dbms_sql.parse(l_trx_num_cursor,
52                        'select ra_trx_number_' ||
53                        to_char(l_imported_source_id) ||
54                        '_' ||
55                        to_char(l_org_id)||
56                        '_s.nextval trx_number ' ||
57                        'from dual ',
58                        dbms_sql.NATIVE);
59 
60         dbms_sql.define_column(l_trx_num_cursor, 1, l_trx_number, 20);
61 
62         l_count := dbms_sql.execute_and_fetch(l_trx_num_cursor,TRUE);
63 
64         dbms_sql.column_value(l_trx_num_cursor, 1, l_trx_number);
65 
66         dbms_sql.close_cursor(l_trx_num_cursor);
67 
68         SELECT substr(global_attribute2,1,4),
69                substr(global_attribute3,1,1),
70                to_date(global_attribute4,'YYYY/MM/DD HH24:MI:SS')
71         INTO   l_branch_number,
72                l_document_letter,
73                l_last_trx_date
74         FROM   ra_batch_sources
75         WHERE  batch_source_id = l_imported_source_id;
76 
77         p_trx_number := l_document_letter || '-' ||
78                         l_branch_number ||  '-' ||
79                         lpad(l_trx_number,8,'0');
80         p_trx_date := l_last_trx_date;
81 
82      ELSE
83 
84        IF l_auto_trx_numbering_flag = 'Y' THEN
85 
86          p_trx_number := l_document_letter ||  '-' ||
87                         l_branch_number ||  '-' ||
88                         lpad(p_trx_number,8,'0');
89          p_trx_date := l_last_trx_date;
90 
91        END IF;
92 
93      END IF;
94 
95    END IF;
96 
97 END update_trx_number_date;
98 
99 END JL_AR_AR_PREFIX_TRX_NUM;