[Home] [Help]
PACKAGE BODY: APPS.QA_DBLINK_PKG
Source
1 PACKAGE BODY qa_dblink_pkg AS
2 /* $Header: qadblinkb.pls 120.5.12010000.1 2008/07/25 09:19:16 appldev ship $ */
3
4 err_logon_denied CONSTANT VARCHAR2(100) := fnd_message.get_string('QA','QA_ERR_LOGON_DENIED');
5 err_invalid_host CONSTANT VARCHAR2(100) := fnd_message.get_string('QA','QA_ERR_INVALID_HOST');
6 err_dblink_exists CONSTANT VARCHAR2(100) := fnd_message.get_string('QA','QA_ERR_DBLINK_EXISTS');
7 err_fetching_link CONSTANT VARCHAR2(100) := fnd_message.get_string('QA','QA_ERR_FETCHING_LINK');
8 err_dblink_creation CONSTANT VARCHAR2(100) := fnd_message.get_string('QA','QA_ERR_DBLINK_CREATION');
9 err_view_creation CONSTANT VARCHAR2(100) := fnd_message.get_string('QA','QA_ERR_VIEW_CREATION');
10 successful_completion CONSTANT VARCHAR2(100) := 'SUCCESS';
11 p_quote CONSTANT VARCHAR2(4) := '''';
12
13 --
14 -- Apps schema info
15 --
16 g_dummy BOOLEAN;
17 g_fnd CONSTANT VARCHAR2(3) := 'FND';
18 g_status VARCHAR2(1);
19 g_industry VARCHAR2(10);
20 g_schema VARCHAR2(30);
21
22 PROCEDURE drop_dblink(
23 p_dblink_name VARCHAR2) IS
24 --
25 -- Local procedure to drop the database link
26 --
27 BEGIN
28 EXECUTE IMMEDIATE 'drop database link ' || p_dblink_name;
29 END drop_dblink;
30
31 FUNCTION create_view(
32 p_dblink_name VARCHAR2)
33 RETURN VARCHAR2 IS
34 --
35 -- Local function to regenerate the view qa_device_data_values_v on edg_event_vw@dblink
36 --
37 l_stmt VARCHAR2(1000);
38 BEGIN
39 l_stmt := 'create or replace view qa_device_data_values_v ' ||
40 'as select event_time, event_data, event_tag_id device_source, ' ||
41 'device_name, 192 quality_code from edg_event_vw@';
42 l_stmt := l_stmt || p_dblink_name;
43 --EXECUTE IMMEDIATE l_stmt || p_dblink_name;
44 ad_ddl.do_ddl(g_schema, 'QA', ad_ddl.create_view, l_stmt,
45 'qa_device_data_values_v');
46
47 RETURN successful_completion;
48 EXCEPTION
49 WHEN OTHERS THEN
50 l_stmt := 'create or replace view qa_device_data_values_v as ' ||
51 'select systimestamp event_time, ' ||
52 'null event_data, ' ||
53 'null device_source, ' ||
54 'null device_name from dual';
55 --EXECUTE IMMEDIATE l_stmt;
56 ad_ddl.do_ddl(g_schema, 'QA', ad_ddl.create_view, l_stmt,
57 'qa_device_data_values_v');
58
59
60 RETURN err_view_creation || substr(sqlerrm, 1, 100);
61 END create_view;
62
63 FUNCTION create_dblink(
64 p_dblink_name VARCHAR2,
65 p_user_name VARCHAR2,
66 p_pwd VARCHAR2,
67 p_connect_str VARCHAR2)
68 RETURN VARCHAR2 IS
69 --
70 -- Main location function to create dblink.
71 -- If the dblink already exists, then
72 -- an error needs to be raised .
73 -- If the db link is created
74 -- Return 0 if successful, a negative error code if not.
75 -- Because this is a DDL, by definition a commit is
76 -- performed inherently.
77 --
78 l_chk NUMBER := 0;
79 return_status VARCHAR2(200);
80
81 INVALID_HOST EXCEPTION;
82 PRAGMA EXCEPTION_INIT(INVALID_HOST, -12154);
83
84 BEGIN
85
86 EXECUTE IMMEDIATE 'create database link ' || p_dblink_name ||
87 ' connect to ' || p_user_name ||
88 ' identified by ' || p_pwd ||
89 ' using ' || p_quote || p_connect_str || p_quote;
90 BEGIN
91 EXECUTE IMMEDIATE 'select 1 from edg_event_vw@' || p_dblink_name || ' where rownum = 1' INTO l_chk;
92
93 return_status := create_view(p_dblink_name);
94
95 IF return_status <> successful_completion THEN
96 drop_dblink(p_dblink_name);
97 RETURN return_status;
98 END IF;
99 EXCEPTION
100 WHEN LOGIN_DENIED THEN
101 drop_dblink(p_dblink_name);
102 RETURN err_logon_denied;
103 WHEN INVALID_HOST THEN
104 drop_dblink(p_dblink_name);
105 RETURN err_invalid_host;
106 WHEN NO_DATA_FOUND THEN
107 null;
108 WHEN OTHERS THEN
109 drop_dblink(p_dblink_name);
110 RETURN err_fetching_link || substr(sqlerrm, 1, 100);
111 END;
112 RETURN successful_completion;
113 EXCEPTION
114 WHEN OTHERS THEN
115 RETURN err_dblink_creation || substr(sqlerrm, 1, 100);
116 END create_dblink;
117
118 --
119 -- Local procedure to regenerate the view qa_device_data_values_v on qa_device_data_values table
120 --
121 PROCEDURE create_opc_view IS
122
123 l_stmt VARCHAR2(1000);
124 BEGIN
125 l_stmt := 'CREATE OR REPLACE VIEW qa_device_data_values_v AS ' ||
126 ' SELECT TO_TIMESTAMP_TZ(event_time) event_time, ' ||
127 ' event_data, ' ||
128 ' device_source, ' ||
129 ' device_name, ' ||
130 ' quality_code ' ||
131 'FROM qa_device_data_values';
132 --EXECUTE IMMEDIATE l_stmt;
133 ad_ddl.do_ddl(g_schema, 'QA', ad_ddl.create_view, l_stmt,
134 'qa_device_data_values_v');
135 EXCEPTION
136 WHEN OTHERS THEN
137 l_stmt := 'create or replace view qa_device_data_values_v as ' ||
138 'select systimestamp event_time, ' ||
139 'null event_data, ' ||
140 'null device_source, ' ||
141 'null device_name from dual';
142 --EXECUTE IMMEDIATE l_stmt;
143 ad_ddl.do_ddl(g_schema, 'QA', ad_ddl.create_view, l_stmt,
144 'qa_device_data_values_v');
145
146 END create_opc_view;
147
148 PROCEDURE wrapper(
149 errbuf OUT NOCOPY VARCHAR2,
150 retcode OUT NOCOPY NUMBER,
151 argument1 VARCHAR2,
152 dummy NUMBER,
153 argument2 VARCHAR2,
154 argument3 VARCHAR2,
155 argument4 VARCHAR2,
156 argument5 VARCHAR2) IS
157
158 --
159 -- Wrapper procedure to create or drop the index.
160 -- This procedure is the entry point for this package
161 -- through the concurrent program 'Manage Collection
162 -- element indexes'. This wrapper procedure is attached
163 -- to the QADBLINK executable.
164 -- argument1 -> Server Type : Server Type for Device Integration. 1 - Sensor Edge Server, 2- OPC Server (Third Party)
165 -- dummy -> Dummy Parameter : To handle Enabling/Disabling of SDR specific fields based on Server Type.
166 -- argument2 -> SDR DB Link Name : 'Create a dblink using this name. If already existant then raise an error'.
167 -- argument3 -> User Name for connecting to SDR database.
168 -- argument4 -> Password for connecting to SDR database for the user name specified in argument2.
169 -- argument5 -> Connection Descriptor (The entire TNS Entry of the SDR Database instance).
170 --
171
172 l_return VARCHAR2(2000);
173 l_db_link_name VARCHAR2(128);
174 l_err_mes_license VARCHAR2(2000);
175 BEGIN
176
177 fnd_file.put_line(fnd_file.log, 'qa_dblink_pkg: entered the wrapper');
178
179 -- APPS schema params.
180 g_dummy := fnd_installation.get_app_info(g_fnd, g_status,
181 g_industry, g_schema);
182
183 IF FND_PROFILE.VALUE('WIP_MES_OPS_FLAG') <> 1 THEN
184 l_err_mes_license := fnd_message.get_string('WIP','WIP_WS_NO_LICENSE');
185 fnd_file.put_line(fnd_file.log, 'ERROR: ' || substr(l_err_mes_license, 1, 200));
186 errbuf := 'ERROR: ' || substr(l_err_mes_license, 1, 200);
187 retcode := 2;
188 ELSIF trim(argument1) = '1' THEN
189 fnd_file.put_line(fnd_file.log, 'Create the DB Link');
190 l_db_link_name := UPPER(trim(argument2));
191
192
193 l_return := create_dblink(p_dblink_name => l_db_link_name,
194 p_user_name => trim(argument3),
195 p_pwd => trim(argument4),
196 p_connect_str => trim(argument5));
197
198 IF (l_return = successful_completion) THEN
199 fnd_file.put_line(fnd_file.log, 'DB Link successfully created');
200 errbuf := '';
201 retcode := 0;
202 ELSE
203 fnd_file.put_line(fnd_file.log, 'DB Link creation failed : ' || substr(l_return, 1, 200));
204 errbuf := substr(l_return, 1, 200);
205 retcode := 2;
206 END IF;
207 ELSE
208 -- If third party OPC server is selected, revert the view to its original state.
209 create_opc_view;
210 END IF;
211 fnd_file.put_line(fnd_file.log, 'qa_dblink_pkg: exiting the wrapper');
212
213 END wrapper;
214
215 END qa_dblink_pkg;