[Home] [Help]
PACKAGE BODY: APPS.ARP_BATCH_SOURCE
Source
1 PACKAGE BODY ARP_BATCH_SOURCE AS
2 /* $Header: ARPLBSUB.pls 120.2.12010000.3 2009/11/24 08:55:41 pnallabo ship $ */
3
4 /*---------------------------------------------------------------------------+
5 | PUBLIC PROCEDURE |
6 | create_trx_sequence |
7 | |
8 | DESCRIPTION |
9 | This function dynamically creates trx_number sequences for batch |
10 | sources with automatic transaction numbering. It calls the |
11 | bb_dist.create_sequence() procedure so that this will work in |
12 | distributed environments. |
13 | |
14 | REQUIRES |
15 | p_batch_source_id |
16 | P_last_number |
17 | |
18 | KNOWN BUGS |
19 | |
20 | NOTES |
21 | |
22 | HISTORY |
23 | 9-DEC-94 Charlie Tomberg Created. |
24 | |
25 +---------------------------------------------------------------------------*/
26 PROCEDURE create_trx_sequence (
27 P_batch_source_id IN number,
28 P_org_id IN number default null,
29 P_last_number IN number
30 ) IS
31
32
33 l_sequence_name varchar2(1024);
34 l_app_short_name varchar2(3);
35 l_org_id varchar2(30);
36 l_sql_stmt varchar2(2000);
37 l_fnd_user varchar2(30);
38 -- Start Bug 6010774, 6903507
39 l_sequence_name_2 varchar2(1024);
40 l_sql_stmt_2 varchar2(2000);
41 l_sequence_name_3 varchar2(1024);
42 l_sql_stmt_3 varchar2(2000);
43 l_sequence_name_4 varchar2(1024);
44 l_sql_stmt_4 varchar2(2000); /* bug 9131562 */
45 l_country_code varchar2(30);
46 -- End Bug 6010774, 6903507
47
48 BEGIN
49
50 select min(ou.oracle_username)
51 into l_fnd_user
52 from FND_PRODUCT_INSTALLATIONS pi,
53 FND_ORACLE_USERID ou
54 where ou.oracle_id = pi.oracle_id
55 and application_id = 0;
56
57 /* SSA changes anukumar select max(org_id)
58 into l_org_id
59 from ar_system_parameters;
60 */
61 IF (p_org_id is NULL) THEN
62 l_sequence_name := 'RA_TRX_NUMBER_'||to_char(P_batch_source_id)||'_S';
63 -- Start Bug 6010774, 6903507
64 l_sequence_name_2 := 'JA_GUI_NUMBER_'||to_char(P_batch_source_id)||'_S';
65 l_sequence_name_3 := 'JL_ZZ_TRX_NUM_'||to_char(P_batch_source_id)||'_S';
66 -- End Bug 6010774, 6903507
67 l_sequence_name_4 := 'JL_BR_TRX_NUM_'||to_char(P_batch_source_id)||'_S';
68 /* bug 9131562 */
69 ELSE
70 l_sequence_name := 'RA_TRX_NUMBER_' || to_char(P_batch_source_id)||
71 '_' || p_org_id || '_S';
72 -- Start Bug 6010774, 6903507
73 l_sequence_name_2 := 'JA_GUI_NUMBER_' || to_char(P_batch_source_id)||
74 '_' || p_org_id || '_S';
75 l_sequence_name_3 := 'JL_ZZ_TRX_NUM_' || to_char(P_batch_source_id)||
76 '_' || p_org_id || '_S';
77 -- End Bug 6010774, 6903507
78 l_sequence_name_4 := 'JL_BR_TRX_NUM_' || to_char(P_batch_source_id)||
79 '_' || p_org_id || '_S'; /* bug 9131562 */
80 END IF;
81
82
83 l_sql_stmt := 'create sequence '||l_sequence_name||
84 ' minvalue 1 maxvalue 99999999999999999999 start with '||
85 to_char(P_last_number + 1)||' cache 20';
86
87 ad_ddl.do_ddl(l_fnd_user, 'AR', ad_ddl.create_sequence, l_sql_stmt, l_sequence_name);
88
89 -- Start Bug 6010774, 6903507
90 fnd_profile.get('JGZZ_COUNTRY_CODE', l_country_code);
91 IF (l_country_code = 'TW') THEN
92 l_sql_stmt_2 := 'create sequence '||l_sequence_name_2||
93 ' minvalue 1 maxvalue 99999999999999999999 start with '||
94 to_char(P_last_number + 1)||' nocache';
95
96 ad_ddl.do_ddl(l_fnd_user, 'JA', ad_ddl.create_sequence, l_sql_stmt_2, l_sequence_name_2);
97 ELSIF (l_country_code = 'BR') THEN
98 l_sql_stmt_4 := 'create sequence '||l_sequence_name_4||
99 ' minvalue 1 maxvalue 99999999999999999999 start with '||
100 to_char(P_last_number + 1)||' nocache';
101 /* bug 9131562 */
102 ad_ddl.do_ddl(l_fnd_user, 'JL', ad_ddl.create_sequence, l_sql_stmt_4,l_sequence_name_4);
103 ELSIF (l_country_code = 'AR') THEN
104 l_sql_stmt_3 := 'create sequence '||l_sequence_name_3||
105 ' minvalue 1 maxvalue 99999999999999999999 start with '||
106 to_char(P_last_number + 1)||' nocache';
107
108 ad_ddl.do_ddl(l_fnd_user, 'JL', ad_ddl.create_sequence, l_sql_stmt_3, l_sequence_name_3);
109 END IF;
110 -- End Bug 6010774, 6903507
111 END; /* end of procedure create_trx_sequence */
112
113 END ARP_BATCH_SOURCE;