PACKAGE BODY: APPS.CS_SR_ADDR_SYNC_INDEX_PKG

Source

1 PACKAGE BODY CS_SR_ADDR_SYNC_INDEX_PKG AS
2 /* $Header: csadsyib.pls 115.2 2004/02/10 20:37:43 aneemuch noship $ */
3 
4    -- errbuf = err messages
5    -- retcode = 0 success, 1 = warning, 2=error
6 
7    -- bmode: S = sync  OFAST=optimize fast, OFULL = optimize full
8 
9 PROCEDURE Sync_All_Index  (
10    ERRBUF         OUT NOCOPY  VARCHAR2,
11    RETCODE        OUT NOCOPY  NUMBER,
12    BMODE          IN          VARCHAR2 DEFAULT NULL )
13 IS
14    l_errbuf varchar2(2000);
15    l_retcode number;
16    l_mode varchar2(5);
17 BEGIN
18    l_mode := bmode;
19 
20    if(bmode is null) then
21       l_mode := 'S';
22    elsif( bmode not in ('S','OFAST', 'OFULL')) then
23       errbuf := 'Invalid mode specified';
24       begin
25          --3..FND_FILE.PUT_LINE(3..FND_FILE.LOG, errbuf);
26          FND_FILE.PUT_LINE(FND_FILE.LOG, errbuf);
27       exception
28          when others then
29          null;
30       end;
31 
32       retcode := 2;
33       return;
34    end if;
35 
36    Sync_Address_Index (l_errbuf, l_retcode, l_mode);
37 
38    -- If not success, return error
39    if( l_retcode <> 0 ) then
40       errbuf  := l_errbuf;
41       retcode := l_retcode;
42    end if;
43 
44    -- Return successfully
45    errbuf  := 'Success';
46    retcode := 0;
47 
48 END SYNC_ALL_INDEX;
49 
50 PROCEDURE Sync_Address_Index  (
51    ERRBUF         OUT NOCOPY  VARCHAR2,
52    RETCODE        OUT NOCOPY  NUMBER,
53    BMODE          IN          VARCHAR2)
54 
55 IS
56 -- To fix bug 3431755 added owner to the cursor
57    -- cursor to get the owner of the CTX_SUMMARY_INDEX
58    cursor get_ind_owner is
59    select owner
60    from   all_indexes
61    where  index_name  = 'ADDRESS_CTX_INDEX'
62    and    index_type  = 'DOMAIN'
63    and    owner       = 'CS';
64 
65 -- end of changes , bug fix 3431755
66 
67    l_ind_owner       VARCHAR2(90);
68    sql_stmt1         VARCHAR2(250);
69 
70 BEGIN
71 
72    if(bmode is null or bmode not in ('S', 'OFAST', 'OFULL')) then
73       errbuf := 'Invalid mode specified';
74 
75       begin
76          --3..FND_FILE.PUT_LINE(3..FND_FILE.LOG, errbuf);
77          FND_FILE.PUT_LINE(FND_FILE.LOG, errbuf);
78       exception
79          when others then
80          null;
81       end;
82 
83       retcode := 2;
84       return;
85    end if;
86 
87    open  get_ind_owner;
88    fetch get_ind_owner into l_ind_owner;
89 
90    if ( get_ind_owner%NOTFOUND ) then
91       close get_ind_owner;
92 
93       errbuf := 'Index ADDRESS_CTX_INDEX is not found. Please create the domain index ' ||
94 		'before executing this concurrent program.';
95       begin
96          FND_FILE.PUT_LINE(FND_FILE.LOG, errbuf);
97       exception
98          when others then
99             null;
100       end;
101       retcode := 2;
102       return;
103    end if;
104 
105    close get_ind_owner;
106 
107    sql_stmt1 := 'alter index ' || l_ind_owner || '.address_ctx_index REBUILD ONLINE';
108 
109    if(bmode = 'S') then
110       sql_stmt1 := sql_stmt1 || ' parameters (''SYNC'') ';
111    elsif(bmode = 'OFAST') then
112       sql_stmt1 := sql_stmt1 || ' parameters (''OPTIMIZE FAST'') ';
113    elsif(bmode = 'OFULL') then
114       sql_stmt1 := sql_stmt1 || ' parameters (''OPTIMIZE FULL'') ';
115    end if;
116 
117    if (bmode = 'S') then
118     --ctx_ddl.sync_index( '1..address_ctx_index' );
119      ad_ctx_ddl.sync_index( l_ind_owner ||'.address_ctx_index' );
120    else
121       EXECUTE IMMEDIATE sql_stmt1;
122    end if;
123 
124    -- Return successfully
125    errbuf := 'Success';
126    retcode := 0;
127 
128 EXCEPTION
129    WHEN OTHERS THEN
130       -- Return error
131       errbuf := 'Unexpected error while attempting to sync domain index ADDRESS_CTX_INDEX.'
132 		|| ' Error : '|| SQLERRM;
133 
134       begin
135          FND_FILE.PUT_LINE(FND_FILE.LOG, errbuf);
136       exception
137          when others then
138             null;
139       end;
140 
141       retcode := 2;
142 
143 END Sync_Address_Index;
144 
145 END CS_SR_ADDR_SYNC_INDEX_PKG;