1 package body BSC_MIGREATION_UI AS
2 /*$Header: BSCMGUIB.pls 120.3 2005/12/16 01:42 amitgupt noship $*/
3
4 procedure createDbLink( p_dblink_sql IN varchar2,
5 p_dblink_name IN varchar2,
6 p_create_status OUT NOCOPY NUMBER) IS
7
8 l_sql varchar2(1000);
9 l_stmt varchar2(1000);
10 l_stmt_remote varchar2(1000);
11 l_sid varchar2(100);
12 l_sid_remote varchar2(100);
13
14 BEGIN
15
16 Execute immediate p_dblink_sql;
17
18 l_sql := 'Select sysdate from dual@' ||p_dblink_name;
19
20 execute immediate l_sql;
21
22 p_create_status := 0;
23
24 -- See if db link is created to the same database (loop back dblink)
25 --bug 4400763
26 l_stmt := 'select name from v$database';
27 l_stmt_remote := 'select name from v$database@'||p_dblink_name;
28 execute immediate l_stmt into l_sid;
29 execute immediate l_stmt_remote into l_sid_remote;
30
31 if (l_sid =l_sid_remote) then
32 p_create_status := -4;
33 dropDbLink(p_dblink_name);
34 end if;
35
36
37 EXCEPTION
38 WHEN OTHERS THEN
39 IF sqlcode = -02019 or sqlcode = -12545 THEN -- wrong connection information
40 p_create_status := -1;
41 ELSE
42 IF sqlcode = -01017 THEN --wrong username/password
43 p_create_status := -2;
44 ELSE IF sqlcode = -02011 THEN --duplicate db link name
45 p_create_status := -3;
46 return;
47 ELSE
48 p_create_status := -100; --any other error
49 END IF;
50
51 END IF;
52 END IF;
53
54 dropDbLink(p_dblink_name);
55
56 END createDbLink;
57
58 procedure dropDbLink(p_dblink_name IN varchar2) IS
59
60 l_sql varchar2(1000);
61 l_dummy date;
62 BEGIN
63 ---if(p_dblink_name == 'BSC_SRC_DBLINK_UI')
64 select sysdate into l_dummy from dual;
65 --rollback; -- we have to see if we can remove this roll back
66
67 begin
68 EXECUTE IMMEDIATE 'ALTER SESSION CLOSE DATABASE LINK '|| p_dblink_name;
69 exception
70 when others then
71 null;
72 end;
73
74 l_sql := 'drop database link ' ||p_dblink_name;
75
76 execute immediate l_sql;
77
78 EXCEPTION
79 WHEN OTHERS THEN
80 raise;
81 END;
82
83 procedure initRespTmpTable(p_process_id IN varchar2,
84 p_dblink_name IN varchar2,
85 num_rows OUT NOCOPY Number) IS
86
87 defRespTab RespMapTable;
88
89 TYPE CurTyp IS REF CURSOR;
90 cv CurTyp;
91 l_stmt varchar2(2000);
92
93 l_src_resp_id BSC_RESPONSIBILITY_VL.RESPONSIBILITY_ID%TYPE;
94 l_src_resp_name BSC_RESPONSIBILITY_VL.RESPONSIBILITY_NAME%TYPE;
95 l_tar_resp_id BSC_RESPONSIBILITY_VL.RESPONSIBILITY_ID%TYPE;
96 l_tar_resp_name BSC_RESPONSIBILITY_VL.RESPONSIBILITY_NAME%TYPE;
97
98 BEGIN
99
100 l_stmt:= null;
101 -- truncate the table first to remove any data for the same session
102 execute immediate 'delete BSC_PMA_MIG_TMP_RESP_MAP';
103
104 if(p_process_id is not null) then
105 --this means there is some process on hold
106
107 l_stmt:= 'SELECT
108 SRC_RLIST.RESPONSIBILITY_ID,
109 SRC_RLIST.RESPONSIBILITY_NAME,
110 TAR_RLIST.RESPONSIBILITY_ID,
111 TAR_RLIST.RESPONSIBILITY_NAME
112 FROM
113 (SELECT INPUT_TABLE_NAME,
114 substr(INPUT_TABLE_NAME,3,INSTR(INPUT_TABLE_NAME,''_'',1,1)-3) ROW_COUNT,
115 RESPONSIBILITY_ID,
116 RESPONSIBILITY_NAME
117 FROM bsc_db_loader_control,BSC_RESPONSIBILITY_VL@'||p_dblink_name||
118 ' SRC_RESP WHERE PROCESS_ID = :1 AND INPUT_TABLE_NAME LIKE ''SR%''
119 AND SRC_RESP.RESPONSIBILITY_ID = substr(INPUT_TABLE_NAME,INSTR(INPUT_TABLE_NAME,''_'',1,1)+1)) SRC_RLIST,
120 (SELECT INPUT_TABLE_NAME,
121 substr(INPUT_TABLE_NAME,3,INSTR(INPUT_TABLE_NAME,''_'',1,1)-3) ROW_COUNT,
122 RESPONSIBILITY_ID,
123 RESPONSIBILITY_NAME
124 FROM bsc_db_loader_control,BSC_RESPONSIBILITY_VL TAR_RESP
125 WHERE PROCESS_ID =:2 AND INPUT_TABLE_NAME LIKE ''TR%''
126 AND TAR_RESP.RESPONSIBILITY_ID = substr(INPUT_TABLE_NAME,INSTR(INPUT_TABLE_NAME,''_'',1,1)+1)) TAR_RLIST
127 WHERE
128 TAR_RLIST.ROW_COUNT =SRC_RLIST.ROW_COUNT';
129
130 -- get the configuration from bsc_db_loader_process
131 OPEN cv for l_stmt using p_process_id,p_process_id;
132 FETCH cv BULK COLLECT INTO defRespTab;
133 CLOSE cv;
134
135 --now put the data in the temp table
136 FORALL i IN defRespTab.FIRST..defRespTab.LAST
137 INSERT INTO BSC_PMA_MIG_TMP_RESP_MAP VALUES defRespTab(i);
138
139 num_rows := defRespTab.LAST;
140 else
141 --this means there is no process on hold
142 -- fetch all the responsibilities that map by default
143 l_stmt:= 'SELECT SRC_RESP.RESPONSIBILITY_ID, SRC_RESP.RESPONSIBILITY_NAME,
144 TAR_RESP.RESPONSIBILITY_ID,TAR_RESP.RESPONSIBILITY_NAME
145 FROM BSC_RESPONSIBILITY_VL@'||p_dblink_name||
146 ' SRC_RESP, FND_RESPONSIBILITY@'||p_dblink_name||
147 ' SRC_FND_RESP, BSC_RESPONSIBILITY_VL TAR_RESP,
148 FND_RESPONSIBILITY TAR_FND_RESP
149 WHERE SRC_RESP.RESPONSIBILITY_ID IN
150 (SELECT RESPONSIBILITY_ID FROM BSC_USER_KPI_ACCESS@'||p_dblink_name||
151 ' UNION SELECT RESPONSIBILITY_ID FROM BSC_USER_TAB_ACCESS@'||p_dblink_name||
152 ' ) AND
153 SRC_RESP.RESPONSIBILITY_ID = SRC_FND_RESP.RESPONSIBILITY_ID
154 AND
155 TAR_RESP.RESPONSIBILITY_ID = TAR_FND_RESP.RESPONSIBILITY_ID
156 AND
157 SRC_FND_RESP.RESPONSIBILITY_KEY = TAR_FND_RESP.RESPONSIBILITY_KEY';
158
159 OPEN cv for l_stmt;
160 FETCH cv BULK COLLECT INTO defRespTab;
161 CLOSE cv;
162
163 --now put the data in the temp table
164 FORALL i IN defRespTab.FIRST..defRespTab.LAST
165 INSERT INTO BSC_PMA_MIG_TMP_RESP_MAP VALUES defRespTab(i);
166
167 num_rows := defRespTab.LAST;
168 end if;
169
170 EXCEPTION
171 WHEN OTHERS THEN
172 raise;
173 END;
174
175 -- FetchMode =='1' means selected Indicators
176 -- FetchMode =='2' means selected Tabs
177 PROCEDURE initTmpObjTable(p_process_id IN varchar2,
178 p_dblink_name IN varchar2,
179 pFetchMode IN varchar2,
180 pRespList IN varchar2) IS
181 l_obj_list objectList;
182 TYPE CurTyp IS REF CURSOR;
183 cv CurTyp;
184 l_stmt varchar2(2000);
185
186 BEGIN
187 -- truncate the table first to remove any data for the same session
188 execute immediate 'delete from BSC_PMA_MIG_TMP_OBJ_LIST';
189
190 if(p_process_id is not null and pFetchMode = '1') then
191 -- get the configuration from bsc_db_loader_process
192 -- need to fetch indicators
193 l_stmt:= 'Select DISTINCT K.INDICATOR, K.NAME
194 FROM bsc_db_loader_control,BSC_KPIS_VL@'||p_dblink_name||
195 ' K ,BSC_USER_KPI_ACCESS@'||p_dblink_name|| ' RK
196 WHERE PROCESS_ID = :1
197 AND INPUT_TABLE_NAME LIKE ''KF%''
198 AND K.INDICATOR = substr(INPUT_TABLE_NAME,INSTR(INPUT_TABLE_NAME,''_'',1,1)+1) AND
199 RK.INDICATOR = K.INDICATOR AND INSTR(:2,RK.RESPONSIBILITY_ID) >0';
200
201 OPEN cv for l_stmt using p_process_id,pRespList;
202 FETCH cv BULK COLLECT INTO l_obj_list;
203 CLOSE cv;
204
205 --now put the data in the temp table
206 FORALL i IN l_obj_list.FIRST..l_obj_list.LAST
207 INSERT INTO BSC_PMA_MIG_TMP_OBJ_LIST VALUES l_obj_list(i);
208 elsif(p_process_id is not null and pFetchMode = '2') then
209 -- get the configuration from bsc_db_loader_process
210 -- need to fetch tabs
211 l_stmt:= 'Select DISTINCT K.TAB_ID, K.NAME
212 FROM bsc_db_loader_control,BSC_TABS_VL@'||p_dblink_name||
213 ' K , BSC_USER_TAB_ACCESS@'||p_dblink_name|| ' RT
214 WHERE PROCESS_ID = :1
215 AND INPUT_TABLE_NAME LIKE ''TF%''
216 AND K.TAB_ID = substr(INPUT_TABLE_NAME,INSTR(INPUT_TABLE_NAME,''_'',1,1)+1) AND
217 RT.TAB_ID = K.TAB_ID AND INSTR(:2,RT.RESPONSIBILITY_ID) >0';
218
219 OPEN cv for l_stmt using p_process_id,pRespList;
220 FETCH cv BULK COLLECT INTO l_obj_list;
221 CLOSE cv;
222
223 --now put the data in the temp table
224 FORALL i IN l_obj_list.FIRST..l_obj_list.LAST
225 INSERT INTO BSC_PMA_MIG_TMP_OBJ_LIST VALUES l_obj_list(i);
226 end if;
227
228 END;
229
230 END BSC_MIGREATION_UI;
[Home] [Help]