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