[Home] [Help]
PACKAGE BODY: APPS.ALR_ORACLE_MAIL_ACCOUNTS_PKG
Source
1 package body ALR_ORACLE_MAIL_ACCOUNTS_PKG as
2 /* $Header: ALROMLAB.pls 120.3.12010000.1 2008/07/27 06:58:48 appldev ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_APPLICATION_ID in NUMBER,
6 X_ORACLE_ID in NUMBER,
7 X_NAME in VARCHAR2,
8 X_ENCRYPTED_PASSWORD in VARCHAR2,
9 X_SENDMAIL_ACCOUNT in VARCHAR2,
10 X_DEFAULT_RESPONSE_ACCOUNT in VARCHAR2,
11 X_CREATION_DATE in DATE,
12 X_CREATED_BY in NUMBER,
13 X_LAST_UPDATE_DATE in DATE,
14 X_LAST_UPDATED_BY in NUMBER,
15 X_LAST_UPDATE_LOGIN in NUMBER
16 ) is
17 cursor C is select ROWID from ALR_ORACLE_MAIL_ACCOUNTS
18 where APPLICATION_ID = X_APPLICATION_ID
19 and ORACLE_ID = X_ORACLE_ID
20 ;
21 begin
22 insert into ALR_ORACLE_MAIL_ACCOUNTS (
23 LAST_UPDATE_LOGIN,
24 APPLICATION_ID,
25 ORACLE_ID,
26 NAME,
27 ENCRYPTED_PASSWORD,
28 SENDMAIL_ACCOUNT,
29 DEFAULT_RESPONSE_ACCOUNT,
30 LAST_UPDATE_DATE,
31 LAST_UPDATED_BY,
32 CREATION_DATE,
33 CREATED_BY
34 ) values (
35 X_LAST_UPDATE_LOGIN,
36 X_APPLICATION_ID,
37 X_ORACLE_ID,
38 X_NAME,
39 X_ENCRYPTED_PASSWORD,
40 X_SENDMAIL_ACCOUNT,
41 X_DEFAULT_RESPONSE_ACCOUNT,
42 X_LAST_UPDATE_DATE,
43 X_LAST_UPDATED_BY,
44 X_CREATION_DATE,
45 X_CREATED_BY
46 );
47
48 open c;
49 fetch c into X_ROWID;
50 if (c%notfound) then
51 close c;
52 raise no_data_found;
53 end if;
54 close c;
55
56 end INSERT_ROW;
57
58 procedure LOAD_ROW (
59 X_APPLICATION_SHORT_NAME in VARCHAR2,
60 X_ORACLE_USERNAME in VARCHAR2,
61 X_OWNER in VARCHAR2,
62 X_NAME in VARCHAR2,
63 X_ENCRYPTED_PASSWORD in VARCHAR2,
64 X_SENDMAIL_ACCOUNT in VARCHAR2,
65 X_DEFAULT_RESPONSE_ACCOUNT in VARCHAR2,
66 X_LAST_UPDATE_DATE in VARCHAR2,
67 X_CUSTOM_MODE in VARCHAR2
68 ) is
69 l_user_id number := 0;
70 l_app_id number := null;
71 l_oracle_id number := null;
72 l_row_id varchar2(64);
73
74 f_luby number; -- entity owner in file
75 f_ludate date; -- entity update date in file
76 db_luby number; -- entity owner in db
77 db_ludate date; -- entity update date in db
78
79 begin
80
81 -- Translate owner to file_last_updated_by
82 f_luby := fnd_load_util.owner_id(X_OWNER);
83
84 -- Translate char last_update_date to date
85 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
86
87 select APPLICATION_ID into l_app_id
88 from FND_APPLICATION
89 where APPLICATION_SHORT_NAME = X_APPLICATION_SHORT_NAME;
90
91 select ORACLE_ID into l_oracle_id
92 from FND_ORACLE_USERID
93 where ORACLE_USERNAME = X_ORACLE_USERNAME;
94
95 select last_updated_by, last_update_date
96 into db_luby, db_ludate
97 from ALR_ORACLE_MAIL_ACCOUNTS
98 where application_id = l_app_id
99 and oracle_id = l_oracle_id;
100
101 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,db_ludate,
102 x_custom_mode)) then
103
104 ALR_ORACLE_MAIL_ACCOUNTS_PKG.UPDATE_ROW(
105 X_APPLICATION_ID => l_app_id,
106 X_ORACLE_ID => l_oracle_id,
107 X_NAME => X_NAME,
108 X_ENCRYPTED_PASSWORD => X_ENCRYPTED_PASSWORD,
109 X_SENDMAIL_ACCOUNT => X_SENDMAIL_ACCOUNT,
110 X_DEFAULT_RESPONSE_ACCOUNT =>
111 X_DEFAULT_RESPONSE_ACCOUNT,
112 X_LAST_UPDATE_DATE => f_ludate,
113 X_LAST_UPDATED_BY => f_luby,
114 X_LAST_UPDATE_LOGIN => 0 );
115
116 end if;
117
118 exception
119 when no_data_found then
120 ALR_ORACLE_MAIL_ACCOUNTS_PKG.INSERT_ROW(
121 X_ROWID => l_row_id,
122 X_APPLICATION_ID => l_app_id,
123 X_ORACLE_ID => l_oracle_id,
124 X_NAME => X_NAME,
125 X_ENCRYPTED_PASSWORD => X_ENCRYPTED_PASSWORD,
126 X_SENDMAIL_ACCOUNT => X_SENDMAIL_ACCOUNT,
127 X_DEFAULT_RESPONSE_ACCOUNT =>
128 X_DEFAULT_RESPONSE_ACCOUNT,
129 X_CREATION_DATE => f_ludate,
130 X_CREATED_BY => f_luby,
131 X_LAST_UPDATE_DATE => f_ludate,
132 X_LAST_UPDATED_BY => f_luby,
133 X_LAST_UPDATE_LOGIN => 0 );
134
135 end LOAD_ROW;
136
137 procedure LOCK_ROW (
138 X_APPLICATION_ID in NUMBER,
139 X_ORACLE_ID in NUMBER,
140 X_NAME in VARCHAR2,
141 X_ENCRYPTED_PASSWORD in VARCHAR2,
142 X_SENDMAIL_ACCOUNT in VARCHAR2,
143 X_DEFAULT_RESPONSE_ACCOUNT in VARCHAR2
144 ) is
145 cursor c1 is select
146 NAME,
147 ENCRYPTED_PASSWORD,
148 SENDMAIL_ACCOUNT,
149 DEFAULT_RESPONSE_ACCOUNT,
150 APPLICATION_ID,
151 ORACLE_ID
152 from ALR_ORACLE_MAIL_ACCOUNTS
153 where APPLICATION_ID = X_APPLICATION_ID
154 and ORACLE_ID = X_ORACLE_ID
155 for update of APPLICATION_ID nowait;
156 begin
157 for recinfo in c1 loop
158 if ( (recinfo.APPLICATION_ID = X_APPLICATION_ID)
159 AND (recinfo.ORACLE_ID = X_ORACLE_ID)
160 AND ((recinfo.NAME = X_NAME)
161 OR ((recinfo.NAME is null) AND (X_NAME is null)))
162 AND ((recinfo.ENCRYPTED_PASSWORD = X_ENCRYPTED_PASSWORD)
163 OR ((recinfo.ENCRYPTED_PASSWORD is null) AND (X_ENCRYPTED_PASSWORD is null)))
164 AND ((recinfo.SENDMAIL_ACCOUNT = X_SENDMAIL_ACCOUNT)
165 OR ((recinfo.SENDMAIL_ACCOUNT is null) AND (X_SENDMAIL_ACCOUNT is null)))
166 AND ((recinfo.DEFAULT_RESPONSE_ACCOUNT = X_DEFAULT_RESPONSE_ACCOUNT)
167 OR ((recinfo.DEFAULT_RESPONSE_ACCOUNT is null) AND (X_DEFAULT_RESPONSE_ACCOUNT is null)))
168 ) then
169 null;
170 else
171 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
172 app_exception.raise_exception;
173 end if;
174 end loop;
175 return;
176 end LOCK_ROW;
177
178 procedure UPDATE_ROW (
179 X_APPLICATION_ID in NUMBER,
180 X_ORACLE_ID in NUMBER,
181 X_NAME in VARCHAR2,
182 X_ENCRYPTED_PASSWORD in VARCHAR2,
183 X_SENDMAIL_ACCOUNT in VARCHAR2,
184 X_DEFAULT_RESPONSE_ACCOUNT in VARCHAR2,
185 X_LAST_UPDATE_DATE in DATE,
186 X_LAST_UPDATED_BY in NUMBER,
187 X_LAST_UPDATE_LOGIN in NUMBER
188 ) is
189 begin
190 update ALR_ORACLE_MAIL_ACCOUNTS set
191 NAME = X_NAME,
192 ENCRYPTED_PASSWORD = X_ENCRYPTED_PASSWORD,
193 SENDMAIL_ACCOUNT = X_SENDMAIL_ACCOUNT,
194 DEFAULT_RESPONSE_ACCOUNT = X_DEFAULT_RESPONSE_ACCOUNT,
195 APPLICATION_ID = X_APPLICATION_ID,
196 ORACLE_ID = X_ORACLE_ID,
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 where APPLICATION_ID = X_APPLICATION_ID
201 and ORACLE_ID = X_ORACLE_ID;
202
203 if (sql%notfound) then
204 raise no_data_found;
205 end if;
206 end UPDATE_ROW;
207
208 procedure DELETE_ROW (
209 X_APPLICATION_ID in NUMBER,
210 X_ORACLE_ID in NUMBER
211 ) is
212 begin
213 delete from ALR_ORACLE_MAIL_ACCOUNTS
214 where APPLICATION_ID = X_APPLICATION_ID
215 and ORACLE_ID = X_ORACLE_ID;
216
217 if (sql%notfound) then
218 raise no_data_found;
219 end if;
220
221 end DELETE_ROW;
222
223 end ALR_ORACLE_MAIL_ACCOUNTS_PKG;