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