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