[Home] [Help]
PACKAGE BODY: APPS.ALR_PROFILE_OPTIONS_PKG
Source
1 package body ALR_PROFILE_OPTIONS_PKG as
2 /* $Header: ALRPOPTB.pls 120.3.12010000.1 2008/07/27 06:58:51 appldev ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_PROFILE_OPTION_NAME in VARCHAR2,
6 X_PROFILE_OPTION_VALUE in VARCHAR2,
7 X_PROFILE_OPTION_LONG in LONG,
8 X_DESCRIPTION in VARCHAR2,
9 X_LONG_FLAG in VARCHAR2,
10 X_ENCRYPTED_PASSWORD in VARCHAR2,
11 X_LOOKUP_TYPE in VARCHAR2,
12 X_CREATION_DATE in DATE,
13 X_CREATED_BY in NUMBER,
14 X_LAST_UPDATE_DATE in DATE,
15 X_LAST_UPDATED_BY in NUMBER,
16 X_LAST_UPDATE_LOGIN in NUMBER
17 ) is
18 cursor C is select ROWID from ALR_PROFILE_OPTIONS
19 where PROFILE_OPTION_NAME = X_PROFILE_OPTION_NAME
20 ;
21 begin
22 insert into ALR_PROFILE_OPTIONS (
23 PROFILE_OPTION_NAME,
24 PROFILE_OPTION_VALUE,
25 PROFILE_OPTION_LONG,
26 DESCRIPTION,
27 LONG_FLAG,
28 ENCRYPTED_PASSWORD,
29 LAST_UPDATE_DATE,
30 LAST_UPDATED_BY,
31 CREATION_DATE,
32 CREATED_BY,
33 LAST_UPDATE_LOGIN,
34 LOOKUP_TYPE
35 ) values (
36 X_PROFILE_OPTION_NAME,
37 X_PROFILE_OPTION_VALUE,
38 X_PROFILE_OPTION_LONG,
39 X_DESCRIPTION,
40 X_LONG_FLAG,
41 X_ENCRYPTED_PASSWORD,
42 X_LAST_UPDATE_DATE,
43 X_LAST_UPDATED_BY,
44 X_CREATION_DATE,
45 X_CREATED_BY,
46 X_LAST_UPDATE_LOGIN,
47 X_LOOKUP_TYPE);
48
49 open c;
50 fetch c into X_ROWID;
51 if (c%notfound) then
52 close c;
53 raise no_data_found;
54 end if;
55 close c;
56
57 end INSERT_ROW;
58
59 procedure LOAD_ROW (
60 X_PROFILE_OPTION_NAME in VARCHAR2,
61 X_OWNER in VARCHAR2,
62 X_PROFILE_OPTION_VALUE in VARCHAR2,
63 X_PROFILE_OPTION_LONG in VARCHAR2,
64 X_DESCRIPTION in VARCHAR2,
65 X_LONG_FLAG in VARCHAR2,
66 X_ENCRYPTED_PASSWORD in VARCHAR2,
67 X_LOOKUP_TYPE in VARCHAR2,
68 X_LAST_UPDATE_DATE in VARCHAR2,
69 X_CUSTOM_MODE in VARCHAR2
70 ) is
71 l_user_id number := 0;
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 last_updated_by, last_update_date
88 into db_luby, db_ludate
89 from ALR_PROFILE_OPTIONS
90 where profile_option_name = X_PROFILE_OPTION_NAME;
91
92 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,db_ludate,
93 x_custom_mode)) then
94
95 ALR_PROFILE_OPTIONS_PKG.UPDATE_ROW(
96 X_PROFILE_OPTION_NAME => X_PROFILE_OPTION_NAME,
97 X_PROFILE_OPTION_VALUE => X_PROFILE_OPTION_VALUE,
98 X_PROFILE_OPTION_LONG => X_PROFILE_OPTION_LONG,
99 X_DESCRIPTION => X_DESCRIPTION,
100 X_LONG_FLAG => X_LONG_FLAG,
101 X_ENCRYPTED_PASSWORD => X_ENCRYPTED_PASSWORD,
102 X_LOOKUP_TYPE => X_LOOKUP_TYPE,
103 X_LAST_UPDATE_DATE => f_ludate,
104 X_LAST_UPDATED_BY => f_luby,
105 X_LAST_UPDATE_LOGIN => 0 );
106
107 end if;
108
109 exception
110 when NO_DATA_FOUND then
111 ALR_PROFILE_OPTIONS_PKG.INSERT_ROW(
112 X_ROWID => l_row_id,
113 X_PROFILE_OPTION_NAME => X_PROFILE_OPTION_NAME,
114 X_PROFILE_OPTION_VALUE => X_PROFILE_OPTION_VALUE,
115 X_PROFILE_OPTION_LONG => X_PROFILE_OPTION_LONG,
116 X_DESCRIPTION => X_DESCRIPTION,
117 X_LONG_FLAG => X_LONG_FLAG,
118 X_ENCRYPTED_PASSWORD => X_ENCRYPTED_PASSWORD,
119 X_LOOKUP_TYPE => X_LOOKUP_TYPE,
120 X_CREATION_DATE => f_ludate,
121 X_CREATED_BY => f_luby,
122 X_LAST_UPDATE_DATE => f_ludate,
123 X_LAST_UPDATED_BY => f_luby,
124 X_LAST_UPDATE_LOGIN => 0 );
125
126 end LOAD_ROW;
127
128 procedure LOCK_ROW (
129 X_PROFILE_OPTION_NAME in VARCHAR2,
130 X_PROFILE_OPTION_VALUE in VARCHAR2,
131 X_PROFILE_OPTION_LONG in LONG,
132 X_DESCRIPTION in VARCHAR2,
133 X_LONG_FLAG in VARCHAR2,
134 X_ENCRYPTED_PASSWORD in VARCHAR2,
135 X_LOOKUP_TYPE in VARCHAR2
136 ) is
137 cursor c1 is select
138 PROFILE_OPTION_VALUE,
139 PROFILE_OPTION_LONG,
140 DESCRIPTION,
141 LONG_FLAG,
142 ENCRYPTED_PASSWORD,
143 LOOKUP_TYPE,
144 PROFILE_OPTION_NAME
145 from ALR_PROFILE_OPTIONS
146 where PROFILE_OPTION_NAME = X_PROFILE_OPTION_NAME
147 for update of PROFILE_OPTION_NAME nowait;
148 begin
149 for recinfo in c1 loop
150 if ( (recinfo.PROFILE_OPTION_NAME = X_PROFILE_OPTION_NAME)
151 AND ((recinfo.PROFILE_OPTION_VALUE = X_PROFILE_OPTION_VALUE)
152 OR ((recinfo.PROFILE_OPTION_VALUE is null) AND (X_PROFILE_OPTION_VALUE is null)))
153 AND ((recinfo.PROFILE_OPTION_LONG = X_PROFILE_OPTION_LONG)
154 OR ((recinfo.PROFILE_OPTION_LONG is null) AND (X_PROFILE_OPTION_LONG is null)))
155 AND ((recinfo.DESCRIPTION = X_DESCRIPTION)
156 OR ((recinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
157 AND (recinfo.LONG_FLAG = X_LONG_FLAG)
158 AND ((recinfo.ENCRYPTED_PASSWORD = X_ENCRYPTED_PASSWORD)
159 OR ((recinfo.ENCRYPTED_PASSWORD is null) AND (X_ENCRYPTED_PASSWORD is null)))
160 AND ((recinfo.LOOKUP_TYPE = X_LOOKUP_TYPE)
161 OR ((recinfo.LOOKUP_TYPE is null) AND (X_LOOKUP_TYPE is null)))
162 ) then
163 null;
164 else
165 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
166 app_exception.raise_exception;
167 end if;
168 end loop;
169 return;
170 end LOCK_ROW;
171
172 procedure UPDATE_ROW (
173 X_PROFILE_OPTION_NAME in VARCHAR2,
174 X_PROFILE_OPTION_VALUE in VARCHAR2,
175 X_PROFILE_OPTION_LONG in LONG,
176 X_DESCRIPTION in VARCHAR2,
177 X_LONG_FLAG in VARCHAR2,
178 X_ENCRYPTED_PASSWORD in VARCHAR2,
179 X_LOOKUP_TYPE in VARCHAR2,
180 X_LAST_UPDATE_DATE in DATE,
181 X_LAST_UPDATED_BY in NUMBER,
182 X_LAST_UPDATE_LOGIN in NUMBER
183 ) is
184 begin
185 update ALR_PROFILE_OPTIONS set
186 PROFILE_OPTION_VALUE = X_PROFILE_OPTION_VALUE,
187 PROFILE_OPTION_LONG = X_PROFILE_OPTION_LONG,
188 DESCRIPTION = X_DESCRIPTION,
189 LONG_FLAG = X_LONG_FLAG,
190 ENCRYPTED_PASSWORD = X_ENCRYPTED_PASSWORD,
191 LOOKUP_TYPE = X_LOOKUP_TYPE,
192 PROFILE_OPTION_NAME = X_PROFILE_OPTION_NAME,
193 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
194 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
195 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
196 where PROFILE_OPTION_NAME = X_PROFILE_OPTION_NAME;
197
198 if (sql%notfound) then
199 raise no_data_found;
200 end if;
201 end UPDATE_ROW;
202
203 procedure DELETE_ROW (
204 X_PROFILE_OPTION_NAME in VARCHAR2
205 ) is
206 begin
207 delete from ALR_PROFILE_OPTIONS
208 where PROFILE_OPTION_NAME = X_PROFILE_OPTION_NAME;
209
210 if (sql%notfound) then
211 raise no_data_found;
212 end if;
213
214 end DELETE_ROW;
215
216 end ALR_PROFILE_OPTIONS_PKG;