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