PACKAGE BODY: APPS.FA_STANDARD_DDL_PKG

Source

1 PACKAGE BODY FA_STANDARD_DDL_PKG as
2 /* $Header: faxsddlb.pls 120.4 2005/07/25 09:58:33 yyoon ship $ */
3 
4   procedure create_sequence(X_name varchar2,
5 			    X_start_num number,
6 			    X_max_num number default 2000000000,
7 			    X_Calling_Fn VARCHAR2,
8              p_log_level_rec        IN     FA_API_TYPES.log_level_rec_type default null) is
9    out_oracle_schema varchar2(100);
10    out_status varchar2(100);
11    out_industry varchar2(100);
12    L_x boolean;
13    L_chkLastNumber number := -1;
14    L_count number default 0;
15 
16    l_schema   varchar2(50);
17    l_status   varchar2(50);
18    l_industry varchar2(50);
19 
20    schema_err exception;
21 
22   begin
23     L_x :=  fnd_installation.get_app_info('FND', out_status,
24 				out_industry, out_oracle_schema);
25 
26     if not (fnd_installation.get_app_info (
27                  application_short_name => 'OFA',
28                  status                 => l_status,
29                  industry               => l_industry,
30                  oracle_schema          => l_schema)) then
31       raise schema_err;
32     end if;
33 
34     select count(*) into L_count
35     from   all_sequences
36     where  sequence_name = X_name
37     and    sequence_owner = l_schema;
38 
39     if L_count > 0 then
40       /* BUG# 1499077 - changing the statement type to correctly be
41                         drop_sequence instead of create
42           -- bridgway 11/29/00
43        */
44 
45       ad_ddl.do_ddl(out_oracle_schema, 'OFA',
46 		    ad_ddl.drop_sequence,
47 		    'drop sequence ' || X_name,X_name);
48     end if;
49 
50     ad_ddl.do_ddl(out_oracle_schema, 'OFA',
51 		ad_ddl.create_sequence,
52 		'create sequence ' ||
53 		X_name ||
54 		' start with ' ||
55 		to_char(X_start_num) ||
56 		' MAXVALUE ' ||
57 		to_char(X_max_num) ||
58 		' ORDER',X_name);
59 
60     select last_number into L_chkLastNumber
61     from   all_sequences
62     where  sequence_name = X_name
63     and    sequence_owner = l_schema;
64 
65     if (L_chkLastNumber <> X_start_num) then
66 	-- Error while creating the sequence
67      fa_standard_pkg.raise_error(
68 	 CALLED_FN => 'fa_standard_pkg.create_sequence',
69 	 CALLING_FN => X_Calling_Fn,
70 	 NAME => 'FA_SHARED_FATAL_ERROR'
71 	 ,p_log_level_rec => p_log_level_rec);
72     end if;
73 
74   exception
75    when schema_err then
76         fa_standard_pkg.raise_error(
77           CALLED_FN => 'fa_standard_pkg.create_sequence',
78           CALLING_FN => X_Calling_Fn
79           ,p_log_level_rec => p_log_level_rec);
80    when others then
81 	fa_standard_pkg.raise_error(
82 	  CALLED_FN => 'fa_standard_pkg.create_sequence',
83 	  CALLING_FN => X_Calling_Fn
84 	  ,p_log_level_rec => p_log_level_rec);
85   end create_sequence;
86 
87 END FA_STANDARD_DDL_PKG;