[Home] [Help]
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;