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