[Home] [Help]
PACKAGE BODY: APPS.ARP_BATCH_SOURCE
Source
1 PACKAGE BODY ARP_BATCH_SOURCE AS
2 /* $Header: ARPLBSUB.pls 120.2.12010000.2 2008/11/25 15:35:38 mraymond 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_country_code varchar2(30);
44 -- End Bug 6010774, 6903507
45
46 BEGIN
47
48 select min(ou.oracle_username)
49 into l_fnd_user
50 from FND_PRODUCT_INSTALLATIONS pi,
51 FND_ORACLE_USERID ou
52 where ou.oracle_id = pi.oracle_id
53 and application_id = 0;
54
55 /* SSA changes anukumar select max(org_id)
56 into l_org_id
57 from ar_system_parameters;
58 */
59 IF (p_org_id is NULL) THEN
60 l_sequence_name := 'RA_TRX_NUMBER_'||to_char(P_batch_source_id)||'_S';
61 -- Start Bug 6010774, 6903507
62 l_sequence_name_2 := 'JA_GUI_NUMBER_'||to_char(P_batch_source_id)||'_S';
63 l_sequence_name_3 := 'JL_ZZ_TRX_NUM_'||to_char(P_batch_source_id)||'_S';
64 -- End Bug 6010774, 6903507
65 ELSE
66 l_sequence_name := 'RA_TRX_NUMBER_' || to_char(P_batch_source_id)||
67 '_' || p_org_id || '_S';
68 -- Start Bug 6010774, 6903507
69 l_sequence_name_2 := 'JA_GUI_NUMBER_' || to_char(P_batch_source_id)||
70 '_' || p_org_id || '_S';
71 l_sequence_name_3 := 'JL_ZZ_TRX_NUM_' || to_char(P_batch_source_id)||
72 '_' || p_org_id || '_S';
73 -- End Bug 6010774, 6903507
74 END IF;
75
76
77 l_sql_stmt := 'create sequence '||l_sequence_name||
78 ' minvalue 1 maxvalue 99999999999999999999 start with '||
79 to_char(P_last_number + 1)||' cache 20';
80
81 ad_ddl.do_ddl(l_fnd_user, 'AR', ad_ddl.create_sequence, l_sql_stmt, l_sequence_name);
82
83 -- Start Bug 6010774, 6903507
84 fnd_profile.get('JGZZ_COUNTRY_CODE', l_country_code);
85 IF (l_country_code = 'TW') THEN
86 l_sql_stmt_2 := 'create sequence '||l_sequence_name_2||
87 ' minvalue 1 maxvalue 99999999999999999999 start with '||
88 to_char(P_last_number + 1)||' nocache';
89
90 ad_ddl.do_ddl(l_fnd_user, 'JA', ad_ddl.create_sequence, l_sql_stmt_2, l_sequence_name_2);
91 ELSIF (l_country_code = 'AR') THEN
92 l_sql_stmt_3 := 'create sequence '||l_sequence_name_3||
93 ' minvalue 1 maxvalue 99999999999999999999 start with '||
94 to_char(P_last_number + 1)||' nocache';
95
96 ad_ddl.do_ddl(l_fnd_user, 'JL', ad_ddl.create_sequence, l_sql_stmt_3, l_sequence_name_3);
97 END IF;
98 -- End Bug 6010774, 6903507
99 END; /* end of procedure create_trx_sequence */
100
101 END ARP_BATCH_SOURCE;