PACKAGE BODY: APPS.FA_STANDARD_DDL_PKG

Source

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