[Home] [Help]
PACKAGE BODY: APPS.XDP_SERVICE_WI_MAP_PKG
Source
1 package body XDP_SERVICE_WI_MAP_PKG as
2 /* $Header: XDPSWMPB.pls 120.1 2005/06/16 02:42:25 appldev $ */
3 procedure INSERT_ROW (
4 X_ROWID in OUT NOCOPY VARCHAR2,
5 X_SERVICE_WI_MAP_ID in NUMBER,
6 X_SERVICE_VAL_ACT_ID in NUMBER,
7 X_WORKITEM_ID in NUMBER,
8 X_PROVISION_SEQ in NUMBER,
9 X_MAPPING_TYPE in VARCHAR2,
10 X_DESCRIPTION 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 XDP_SERVICE_WI_MAP
18 where SERVICE_WI_MAP_ID = X_SERVICE_WI_MAP_ID
19 ;
20 begin
21 insert into XDP_SERVICE_WI_MAP (
22 SERVICE_WI_MAP_ID,
23 SERVICE_VAL_ACT_ID,
24 WORKITEM_ID,
25 PROVISION_SEQ,
26 MAPPING_TYPE,
27 CREATION_DATE,
28 CREATED_BY,
29 LAST_UPDATE_DATE,
30 LAST_UPDATED_BY,
31 LAST_UPDATE_LOGIN
32 ) values (
33 X_SERVICE_WI_MAP_ID,
34 X_SERVICE_VAL_ACT_ID,
35 X_WORKITEM_ID,
36 X_PROVISION_SEQ,
37 X_MAPPING_TYPE,
38 X_CREATION_DATE,
39 X_CREATED_BY,
40 X_LAST_UPDATE_DATE,
41 X_LAST_UPDATED_BY,
42 X_LAST_UPDATE_LOGIN
43 );
44
45 insert into XDP_SERVICE_WI_MAP_TL (
46 SERVICE_WI_MAP_ID,
47 DESCRIPTION,
48 CREATED_BY,
49 CREATION_DATE,
50 LAST_UPDATED_BY,
51 LAST_UPDATE_DATE,
52 LAST_UPDATE_LOGIN,
53 LANGUAGE,
54 SOURCE_LANG
55 ) select
56 X_SERVICE_WI_MAP_ID,
57 X_DESCRIPTION,
58 X_CREATED_BY,
59 X_CREATION_DATE,
60 X_LAST_UPDATED_BY,
61 X_LAST_UPDATE_DATE,
62 X_LAST_UPDATE_LOGIN,
63 L.LANGUAGE_CODE,
64 userenv('LANG')
65 from FND_LANGUAGES L
66 where L.INSTALLED_FLAG in ('I', 'B')
67 and not exists
68 (select NULL
69 from XDP_SERVICE_WI_MAP_TL T
70 where T.SERVICE_WI_MAP_ID = X_SERVICE_WI_MAP_ID
71 and T.LANGUAGE = L.LANGUAGE_CODE);
72
73 open c;
74 fetch c into X_ROWID;
75 if (c%notfound) then
76 close c;
77 raise no_data_found;
78 end if;
79 close c;
80
81 end INSERT_ROW;
82
83 procedure LOCK_ROW (
84 X_SERVICE_WI_MAP_ID in NUMBER,
85 X_SERVICE_VAL_ACT_ID in NUMBER,
86 X_WORKITEM_ID in NUMBER,
87 X_PROVISION_SEQ in NUMBER,
88 X_MAPPING_TYPE in VARCHAR2,
89 X_DESCRIPTION in VARCHAR2
90 ) is
91 cursor c is select
92 SERVICE_VAL_ACT_ID,
93 WORKITEM_ID,
94 PROVISION_SEQ,
95 MAPPING_TYPE
96 from XDP_SERVICE_WI_MAP
97 where SERVICE_WI_MAP_ID = X_SERVICE_WI_MAP_ID
98 for update of SERVICE_WI_MAP_ID nowait;
99 recinfo c%rowtype;
100
101 cursor c1 is select
102 DESCRIPTION,
103 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
104 from XDP_SERVICE_WI_MAP_TL
105 where SERVICE_WI_MAP_ID = X_SERVICE_WI_MAP_ID
106 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
107 for update of SERVICE_WI_MAP_ID nowait;
108 begin
109 open c;
110 fetch c into recinfo;
111 if (c%notfound) then
112 close c;
113 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
114 app_exception.raise_exception;
115 end if;
116 close c;
117 if ( (recinfo.SERVICE_VAL_ACT_ID = X_SERVICE_VAL_ACT_ID)
118 AND (recinfo.WORKITEM_ID = X_WORKITEM_ID)
119 AND (recinfo.PROVISION_SEQ = X_PROVISION_SEQ)
120 AND (recinfo.MAPPING_TYPE = X_MAPPING_TYPE)
121 ) then
122 null;
123 else
124 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
125 app_exception.raise_exception;
126 end if;
127
128 for tlinfo in c1 loop
129 if (tlinfo.BASELANG = 'Y') then
130 if ( ((tlinfo.DESCRIPTION = X_DESCRIPTION)
131 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
132 ) then
133 null;
134 else
135 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
136 app_exception.raise_exception;
137 end if;
138 end if;
139 end loop;
140 return;
141 end LOCK_ROW;
142
143 procedure UPDATE_ROW (
144 X_SERVICE_WI_MAP_ID in NUMBER,
145 X_SERVICE_VAL_ACT_ID in NUMBER,
146 X_WORKITEM_ID in NUMBER,
147 X_PROVISION_SEQ in NUMBER,
148 X_MAPPING_TYPE in VARCHAR2,
149 X_DESCRIPTION in VARCHAR2,
150 X_LAST_UPDATE_DATE in DATE,
151 X_LAST_UPDATED_BY in NUMBER,
152 X_LAST_UPDATE_LOGIN in NUMBER
153 ) is
154 begin
155 update XDP_SERVICE_WI_MAP set
156 SERVICE_VAL_ACT_ID = X_SERVICE_VAL_ACT_ID,
157 WORKITEM_ID = X_WORKITEM_ID,
158 PROVISION_SEQ = X_PROVISION_SEQ,
159 MAPPING_TYPE = X_MAPPING_TYPE,
160 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
161 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
162 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
163 where SERVICE_WI_MAP_ID = X_SERVICE_WI_MAP_ID;
164
165 if (sql%notfound) then
166 raise no_data_found;
167 end if;
168
169 update XDP_SERVICE_WI_MAP_TL set
170 DESCRIPTION = X_DESCRIPTION,
171 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
172 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
173 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
174 SOURCE_LANG = userenv('LANG')
175 where SERVICE_WI_MAP_ID = X_SERVICE_WI_MAP_ID
176 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
177
178 if (sql%notfound) then
179 raise no_data_found;
180 end if;
181 end UPDATE_ROW;
182
183 procedure DELETE_ROW (
184 X_SERVICE_WI_MAP_ID in NUMBER
185 ) is
186 begin
187 delete from XDP_SERVICE_WI_MAP_TL
188 where SERVICE_WI_MAP_ID = X_SERVICE_WI_MAP_ID;
189
190 if (sql%notfound) then
191 raise no_data_found;
192 end if;
193
194 delete from XDP_SERVICE_WI_MAP
195 where SERVICE_WI_MAP_ID = X_SERVICE_WI_MAP_ID;
196
197 if (sql%notfound) then
198 raise no_data_found;
199 end if;
200 end DELETE_ROW;
201
202 procedure ADD_LANGUAGE
203 is
204 begin
205 delete from XDP_SERVICE_WI_MAP_TL T
206 where not exists
207 (select NULL
208 from XDP_SERVICE_WI_MAP B
209 where B.SERVICE_WI_MAP_ID = T.SERVICE_WI_MAP_ID
210 );
211
212 update XDP_SERVICE_WI_MAP_TL T set (
213 DESCRIPTION
214 ) = (select
215 B.DESCRIPTION
216 from XDP_SERVICE_WI_MAP_TL B
217 where B.SERVICE_WI_MAP_ID = T.SERVICE_WI_MAP_ID
218 and B.LANGUAGE = T.SOURCE_LANG)
219 where (
220 T.SERVICE_WI_MAP_ID,
221 T.LANGUAGE
222 ) in (select
223 SUBT.SERVICE_WI_MAP_ID,
224 SUBT.LANGUAGE
225 from XDP_SERVICE_WI_MAP_TL SUBB, XDP_SERVICE_WI_MAP_TL SUBT
226 where SUBB.SERVICE_WI_MAP_ID = SUBT.SERVICE_WI_MAP_ID
227 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
228 and (SUBB.DESCRIPTION <> SUBT.DESCRIPTION
229 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
230 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
231 ));
232
233 insert into XDP_SERVICE_WI_MAP_TL (
234 SERVICE_WI_MAP_ID,
235 DESCRIPTION,
236 CREATED_BY,
237 CREATION_DATE,
238 LAST_UPDATED_BY,
239 LAST_UPDATE_DATE,
240 LAST_UPDATE_LOGIN,
241 LANGUAGE,
242 SOURCE_LANG
243 ) select
244 B.SERVICE_WI_MAP_ID,
245 B.DESCRIPTION,
246 B.CREATED_BY,
247 B.CREATION_DATE,
248 B.LAST_UPDATED_BY,
249 B.LAST_UPDATE_DATE,
250 B.LAST_UPDATE_LOGIN,
251 L.LANGUAGE_CODE,
252 B.SOURCE_LANG
253 from XDP_SERVICE_WI_MAP_TL B, FND_LANGUAGES L
254 where L.INSTALLED_FLAG in ('I', 'B')
255 and B.LANGUAGE = userenv('LANG')
256 and not exists
257 (select NULL
258 from XDP_SERVICE_WI_MAP_TL T
259 where T.SERVICE_WI_MAP_ID = B.SERVICE_WI_MAP_ID
260 and T.LANGUAGE = L.LANGUAGE_CODE);
261 end ADD_LANGUAGE;
262
263 procedure LOAD_ROW (
264 X_SERVICE_WI_MAP_ID in NUMBER,
265 X_SERVICE_VAL_ACT_ID in NUMBER,
266 X_WORKITEM_ID in NUMBER,
267 X_PROVISION_SEQ in NUMBER,
268 X_MAPPING_TYPE in VARCHAR2,
269 X_DESCRIPTION in VARCHAR2,
270 X_OWNER in VARCHAR2) IS
271 begin
272
273 declare
274 user_id number := 0;
275 row_id varchar2(64);
276
277 begin
278
279 if (X_OWNER = 'SEED') then
280 user_id := 1;
281 end if;
282
283 XDP_SERVICE_WI_MAP_PKG.UPDATE_ROW (
284 X_SERVICE_WI_MAP_ID => X_SERVICE_WI_MAP_ID,
285 X_SERVICE_VAL_ACT_ID => X_SERVICE_VAL_ACT_ID,
286 X_WORKITEM_ID => X_WORKITEM_ID,
287 X_PROVISION_SEQ => X_PROVISION_SEQ,
288 X_MAPPING_TYPE => X_MAPPING_TYPE,
289 X_DESCRIPTION => X_DESCRIPTION,
290 X_LAST_UPDATE_DATE => sysdate,
291 X_LAST_UPDATED_BY => user_id,
292 X_LAST_UPDATE_LOGIN => 0);
293
294 exception
295 when NO_DATA_FOUND then
296 XDP_SERVICE_WI_MAP_PKG.INSERT_ROW (
297 X_ROWID => row_id,
298 X_SERVICE_WI_MAP_ID => X_SERVICE_WI_MAP_ID,
299 X_SERVICE_VAL_ACT_ID => X_SERVICE_VAL_ACT_ID,
300 X_WORKITEM_ID => X_WORKITEM_ID,
301 X_PROVISION_SEQ => X_PROVISION_SEQ,
302 X_MAPPING_TYPE => X_MAPPING_TYPE,
303 X_DESCRIPTION => X_DESCRIPTION,
304 X_CREATION_DATE => sysdate,
305 X_CREATED_BY => user_id,
306 X_LAST_UPDATE_DATE => sysdate,
307 X_LAST_UPDATED_BY => user_id,
308 X_LAST_UPDATE_LOGIN => 0);
309 end;
310 end LOAD_ROW;
311
312 procedure TRANSLATE_ROW (
313 X_SERVICE_WI_MAP_ID in NUMBER,
314 X_DESCRIPTION in VARCHAR2,
315 X_OWNER in VARCHAR2) IS
316
317 begin
318
319 -- only update rows that have not been altered by user
320
321 update XDP_SERVICE_WI_MAP_TL
322 set description = X_DESCRIPTION,
323 source_lang = userenv('LANG'),
324 last_update_date = sysdate,
325 last_updated_by = decode(X_OWNER, 'SEED', 1, 0),
326 last_update_login = 0
327 where service_wi_map_id = X_SERVICE_WI_MAP_ID
328 and userenv('LANG') in (language, source_lang);
329
330 end TRANSLATE_ROW;
331
332
333
334 end XDP_SERVICE_WI_MAP_PKG;