[Home] [Help]
PACKAGE BODY: APPS.FRM_DELIVERY_FTP_SERVERS_PKG
Source
1 package body FRM_DELIVERY_FTP_SERVERS_PKG as
2 /* $Header: frmdelftpb.pls 120.0 2012/01/24 11:47:20 dhvenkat noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_SERVER_ID in NUMBER,
6 X_HOST in VARCHAR2,
7 X_PORT in NUMBER,
8 X_AUTH_TYPE in VARCHAR2,
9 X_USERNAME in VARCHAR2,
10 X_TYPE in VARCHAR2,
11 X_FILE_PERMISSION in NUMBER,
12 X_OBJECT_VERSION_NUMBER in NUMBER,
13 X_SERVER_NAME in VARCHAR2,
14 X_CREATION_DATE in DATE,
15 X_CREATED_BY in NUMBER,
16 X_LAST_UPDATE_DATE in DATE,
17 X_LAST_UPDATED_BY in NUMBER,
18 X_LAST_UPDATE_LOGIN in NUMBER
19 ) is
20 cursor C is select ROWID from FRM_DELIVERY_FTP_SERVERS_B
21 where SERVER_ID = X_SERVER_ID
22 ;
23 begin
24 insert into FRM_DELIVERY_FTP_SERVERS_B (
25 SERVER_ID,
26 HOST,
27 PORT,
28 AUTH_TYPE,
29 USERNAME,
30 TYPE,
31 FILE_PERMISSION,
32 OBJECT_VERSION_NUMBER,
33 CREATION_DATE,
34 CREATED_BY,
35 LAST_UPDATE_DATE,
36 LAST_UPDATED_BY,
37 LAST_UPDATE_LOGIN
38 ) values (
39 X_SERVER_ID,
40 X_HOST,
41 X_PORT,
42 X_AUTH_TYPE,
43 X_USERNAME,
44 X_TYPE,
45 X_FILE_PERMISSION,
46 X_OBJECT_VERSION_NUMBER,
47 X_CREATION_DATE,
48 X_CREATED_BY,
49 X_LAST_UPDATE_DATE,
50 X_LAST_UPDATED_BY,
51 X_LAST_UPDATE_LOGIN
52 );
53
54 insert into FRM_DELIVERY_FTP_SERVERS_TL (
55 SERVER_ID,
56 SERVER_NAME,
57 CREATION_DATE,
58 CREATED_BY,
59 LAST_UPDATED_BY,
60 LAST_UPDATE_LOGIN,
61 LAST_UPDATE_DATE,
62 LANGUAGE,
63 SOURCE_LANG
64 ) select
65 X_SERVER_ID,
66 X_SERVER_NAME,
67 X_CREATION_DATE,
68 X_CREATED_BY,
69 X_LAST_UPDATED_BY,
70 X_LAST_UPDATE_LOGIN,
71 X_LAST_UPDATE_DATE,
72 L.LANGUAGE_CODE,
73 userenv('LANG')
74 from FND_LANGUAGES L
75 where L.INSTALLED_FLAG in ('I', 'B')
76 and not exists
77 (select NULL
78 from FRM_DELIVERY_FTP_SERVERS_TL T
79 where T.SERVER_ID = X_SERVER_ID
80 and T.LANGUAGE = L.LANGUAGE_CODE);
81
82 open c;
83 fetch c into X_ROWID;
84 if (c%notfound) then
85 close c;
86 raise no_data_found;
87 end if;
88 close c;
89
90 end INSERT_ROW;
91
92 procedure LOCK_ROW (
93 X_SERVER_ID in NUMBER,
94 X_HOST in VARCHAR2,
95 X_PORT in NUMBER,
96 X_AUTH_TYPE in VARCHAR2,
97 X_USERNAME in VARCHAR2,
98 X_TYPE in VARCHAR2,
99 X_FILE_PERMISSION in NUMBER,
100 X_OBJECT_VERSION_NUMBER in NUMBER,
101 X_SERVER_NAME in VARCHAR2
102 ) is
103 cursor c is select
104 HOST,
105 PORT,
106 AUTH_TYPE,
107 USERNAME,
108 TYPE,
109 FILE_PERMISSION,
110 OBJECT_VERSION_NUMBER
111 from FRM_DELIVERY_FTP_SERVERS_B
112 where SERVER_ID = X_SERVER_ID
113 for update of SERVER_ID nowait;
114 recinfo c%rowtype;
115
116 cursor c1 is select
117 SERVER_NAME,
118 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
119 from FRM_DELIVERY_FTP_SERVERS_TL
120 where SERVER_ID = X_SERVER_ID
121 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
122 for update of SERVER_ID nowait;
123 begin
124 open c;
125 fetch c into recinfo;
126 if (c%notfound) then
127 close c;
128 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
129 app_exception.raise_exception;
130 end if;
131 close c;
132 if ( (recinfo.HOST = X_HOST)
133 AND ((recinfo.PORT = X_PORT)
134 OR ((recinfo.PORT is null) AND (X_PORT is null)))
135 AND (recinfo.AUTH_TYPE = X_AUTH_TYPE)
136 AND ((recinfo.USERNAME = X_USERNAME)
137 OR ((recinfo.USERNAME is null) AND (X_USERNAME is null)))
138 AND (recinfo.TYPE = X_TYPE)
139 AND ((recinfo.FILE_PERMISSION = X_FILE_PERMISSION)
140 OR ((recinfo.FILE_PERMISSION is null) AND (X_FILE_PERMISSION is null)))
141 AND (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
142 ) then
143 null;
144 else
145 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
146 app_exception.raise_exception;
147 end if;
148
149 for tlinfo in c1 loop
150 if (tlinfo.BASELANG = 'Y') then
151 if ( (tlinfo.SERVER_NAME = X_SERVER_NAME)
152 ) then
153 null;
154 else
155 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
156 app_exception.raise_exception;
157 end if;
158 end if;
159 end loop;
160 return;
161 end LOCK_ROW;
162
163 procedure UPDATE_ROW (
164 X_SERVER_ID in NUMBER,
165 X_HOST in VARCHAR2,
166 X_PORT in NUMBER,
167 X_AUTH_TYPE in VARCHAR2,
168 X_USERNAME in VARCHAR2,
169 X_TYPE in VARCHAR2,
170 X_FILE_PERMISSION in NUMBER,
171 X_OBJECT_VERSION_NUMBER in NUMBER,
172 X_SERVER_NAME in VARCHAR2,
173 X_LAST_UPDATE_DATE in DATE,
174 X_LAST_UPDATED_BY in NUMBER,
175 X_LAST_UPDATE_LOGIN in NUMBER
176 ) is
177 begin
178 update FRM_DELIVERY_FTP_SERVERS_B set
179 HOST = X_HOST,
180 PORT = X_PORT,
181 AUTH_TYPE = X_AUTH_TYPE,
182 USERNAME = X_USERNAME,
183 TYPE = X_TYPE,
184 FILE_PERMISSION = X_FILE_PERMISSION,
185 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
186 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
187 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
188 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
189 where SERVER_ID = X_SERVER_ID;
190
191 if (sql%notfound) then
192 raise no_data_found;
193 end if;
194
195 update FRM_DELIVERY_FTP_SERVERS_TL set
196 SERVER_NAME = X_SERVER_NAME,
197 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
198 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
199 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
200 SOURCE_LANG = userenv('LANG')
201 where SERVER_ID = X_SERVER_ID
202 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
203
204 if (sql%notfound) then
205 raise no_data_found;
206 end if;
207 end UPDATE_ROW;
208
209 procedure DELETE_ROW (
210 X_SERVER_ID in NUMBER
211 ) is
212 begin
213 delete from FRM_DELIVERY_FTP_SERVERS_TL
214 where SERVER_ID = X_SERVER_ID;
215
216 if (sql%notfound) then
217 raise no_data_found;
218 end if;
219
220 delete from FRM_DELIVERY_FTP_SERVERS_B
221 where SERVER_ID = X_SERVER_ID;
222
223 if (sql%notfound) then
224 raise no_data_found;
225 end if;
226 end DELETE_ROW;
227
228 procedure ADD_LANGUAGE
229 is
230 begin
231 delete from FRM_DELIVERY_FTP_SERVERS_TL T
232 where not exists
233 (select NULL
234 from FRM_DELIVERY_FTP_SERVERS_B B
235 where B.SERVER_ID = T.SERVER_ID
236 );
237
238 update FRM_DELIVERY_FTP_SERVERS_TL T set (
239 SERVER_NAME
240 ) = (select
241 B.SERVER_NAME
242 from FRM_DELIVERY_FTP_SERVERS_TL B
243 where B.SERVER_ID = T.SERVER_ID
244 and B.LANGUAGE = T.SOURCE_LANG)
245 where (
246 T.SERVER_ID,
247 T.LANGUAGE
248 ) in (select
249 SUBT.SERVER_ID,
250 SUBT.LANGUAGE
251 from FRM_DELIVERY_FTP_SERVERS_TL SUBB, FRM_DELIVERY_FTP_SERVERS_TL SUBT
252 where SUBB.SERVER_ID = SUBT.SERVER_ID
253 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
254 and (SUBB.SERVER_NAME <> SUBT.SERVER_NAME
255 ));
256
257 insert into FRM_DELIVERY_FTP_SERVERS_TL (
258 SERVER_ID,
259 SERVER_NAME,
260 CREATION_DATE,
261 CREATED_BY,
262 LAST_UPDATED_BY,
263 LAST_UPDATE_LOGIN,
264 LAST_UPDATE_DATE,
265 LANGUAGE,
266 SOURCE_LANG
267 ) select /*+ ORDERED */
268 B.SERVER_ID,
269 B.SERVER_NAME,
270 B.CREATION_DATE,
271 B.CREATED_BY,
272 B.LAST_UPDATED_BY,
273 B.LAST_UPDATE_LOGIN,
274 B.LAST_UPDATE_DATE,
275 L.LANGUAGE_CODE,
276 B.SOURCE_LANG
277 from FRM_DELIVERY_FTP_SERVERS_TL B, FND_LANGUAGES L
278 where L.INSTALLED_FLAG in ('I', 'B')
279 and B.LANGUAGE = userenv('LANG')
280 and not exists
281 (select NULL
282 from FRM_DELIVERY_FTP_SERVERS_TL T
283 where T.SERVER_ID = B.SERVER_ID
284 and T.LANGUAGE = L.LANGUAGE_CODE);
285 end ADD_LANGUAGE;
286
287 end FRM_DELIVERY_FTP_SERVERS_PKG;