1 package body FND_CONC_RELEASE_STATES_PKG as
2 /* $Header: AFCPSC2B.pls 120.2 2005/08/19 20:50:41 jtoruno ship $ */
3
4 procedure INSERT_ROW (
5 X_ROWID in out nocopy VARCHAR2,
6 X_CONCURRENT_STATE_ID in NUMBER,
7 X_APPLICATION_ID in NUMBER,
8 X_CONCURRENT_STATE_NAME in VARCHAR2,
9 X_ENABLED_FLAG in VARCHAR2,
10 X_START_DATE_ACTIVE in DATE,
11 X_END_DATE_ACTIVE in DATE,
12 X_STATUS in NUMBER,
13 X_LOOKUP_TYPE_ID in NUMBER,
14 X_UPDATED_FLAG in VARCHAR2,
15 X_USER_CONCURRENT_STATE_NAME in VARCHAR2,
16 X_DESCRIPTION in VARCHAR2,
17 X_CREATION_DATE in DATE,
18 X_CREATED_BY in NUMBER,
19 X_LAST_UPDATE_DATE in DATE,
20 X_LAST_UPDATED_BY in NUMBER,
21 X_LAST_UPDATE_LOGIN in NUMBER
22 ) is
23 cursor C is select ROWID from FND_CONC_RELEASE_STATES
24 where CONCURRENT_STATE_ID = X_CONCURRENT_STATE_ID
25 and APPLICATION_ID = X_APPLICATION_ID
26 ;
27 begin
28 insert into FND_CONC_RELEASE_STATES (
29 APPLICATION_ID,
30 CONCURRENT_STATE_ID,
31 CONCURRENT_STATE_NAME,
32 ENABLED_FLAG,
33 START_DATE_ACTIVE,
34 END_DATE_ACTIVE,
35 STATUS,
36 LOOKUP_TYPE_ID,
37 UPDATED_FLAG,
38 CREATION_DATE,
39 CREATED_BY,
40 LAST_UPDATE_DATE,
41 LAST_UPDATED_BY,
42 LAST_UPDATE_LOGIN
43 ) values (
44 X_APPLICATION_ID,
45 X_CONCURRENT_STATE_ID,
46 X_CONCURRENT_STATE_NAME,
47 X_ENABLED_FLAG,
48 X_START_DATE_ACTIVE,
49 X_END_DATE_ACTIVE,
50 X_STATUS,
51 X_LOOKUP_TYPE_ID,
52 X_UPDATED_FLAG,
53 X_CREATION_DATE,
54 X_CREATED_BY,
55 X_LAST_UPDATE_DATE,
56 X_LAST_UPDATED_BY,
57 X_LAST_UPDATE_LOGIN
58 );
59
60 insert into FND_CONC_RELEASE_STATES_TL (
61 APPLICATION_ID,
62 CONCURRENT_STATE_ID,
63 USER_CONCURRENT_STATE_NAME,
64 DESCRIPTION,
65 LAST_UPDATE_DATE,
66 LAST_UPDATED_BY,
67 LAST_UPDATE_LOGIN,
68 CREATION_DATE,
69 CREATED_BY,
70 LANGUAGE,
71 SOURCE_LANG
72 ) select
73 X_APPLICATION_ID,
74 X_CONCURRENT_STATE_ID,
75 X_USER_CONCURRENT_STATE_NAME,
76 X_DESCRIPTION,
77 X_LAST_UPDATE_DATE,
78 X_LAST_UPDATED_BY,
79 X_LAST_UPDATE_LOGIN,
80 X_CREATION_DATE,
81 X_CREATED_BY,
82 L.LANGUAGE_CODE,
83 userenv('LANG')
84 from FND_LANGUAGES L
85 where L.INSTALLED_FLAG in ('I', 'B')
86 and not exists
87 (select NULL
88 from FND_CONC_RELEASE_STATES_TL T
89 where T.CONCURRENT_STATE_ID = X_CONCURRENT_STATE_ID
90 and T.APPLICATION_ID = X_APPLICATION_ID
91 and T.LANGUAGE = L.LANGUAGE_CODE);
92
93 open c;
94 fetch c into X_ROWID;
95 if (c%notfound) then
96 close c;
97 raise no_data_found;
98 end if;
99 close c;
100
101 end INSERT_ROW;
102
103 procedure LOCK_ROW (
104 X_CONCURRENT_STATE_ID in NUMBER,
105 X_APPLICATION_ID in NUMBER,
106 X_CONCURRENT_STATE_NAME in VARCHAR2,
107 X_ENABLED_FLAG in VARCHAR2,
108 X_START_DATE_ACTIVE in DATE,
109 X_END_DATE_ACTIVE in DATE,
110 X_STATUS in NUMBER,
111 X_LOOKUP_TYPE_ID in NUMBER,
112 X_UPDATED_FLAG in VARCHAR2,
113 X_USER_CONCURRENT_STATE_NAME in VARCHAR2,
114 X_DESCRIPTION in VARCHAR2
115 ) is
116 cursor c is select
117 CONCURRENT_STATE_NAME,
118 ENABLED_FLAG,
119 START_DATE_ACTIVE,
120 END_DATE_ACTIVE,
121 STATUS,
122 LOOKUP_TYPE_ID,
123 UPDATED_FLAG
124 from FND_CONC_RELEASE_STATES
125 where CONCURRENT_STATE_ID = X_CONCURRENT_STATE_ID
126 and APPLICATION_ID = X_APPLICATION_ID
127 for update of CONCURRENT_STATE_ID nowait;
128 recinfo c%rowtype;
129
130 cursor c1 is select
131 USER_CONCURRENT_STATE_NAME,
132 DESCRIPTION
133 from FND_CONC_RELEASE_STATES_TL
134 where CONCURRENT_STATE_ID = X_CONCURRENT_STATE_ID
135 and APPLICATION_ID = X_APPLICATION_ID
136 and LANGUAGE = userenv('LANG')
137 for update of CONCURRENT_STATE_ID nowait;
138 tlinfo c1%rowtype;
139
140 begin
141 open c;
142 fetch c into recinfo;
143 if (c%notfound) then
144 close c;
145 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
146 app_exception.raise_exception;
147 end if;
148 close c;
149 if ( (recinfo.CONCURRENT_STATE_NAME = X_CONCURRENT_STATE_NAME)
150 AND (recinfo.ENABLED_FLAG = X_ENABLED_FLAG)
151 AND ((recinfo.START_DATE_ACTIVE = X_START_DATE_ACTIVE)
152 OR ((recinfo.START_DATE_ACTIVE is null) AND (X_START_DATE_ACTIVE is null)))
153 AND ((recinfo.END_DATE_ACTIVE = X_END_DATE_ACTIVE)
154 OR ((recinfo.END_DATE_ACTIVE is null) AND (X_END_DATE_ACTIVE is null)))
155 AND (recinfo.STATUS = X_STATUS)
156 AND (recinfo.LOOKUP_TYPE_ID = X_LOOKUP_TYPE_ID)
157 AND (recinfo.UPDATED_FLAG = X_UPDATED_FLAG)
158 ) then
159 null;
160 else
161 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
162 app_exception.raise_exception;
163 end if;
164
165 open c1;
166 fetch c1 into tlinfo;
167 if (c1%notfound) then
168 close c1;
169 return;
170 end if;
171 close c1;
172
173 if ( (tlinfo.USER_CONCURRENT_STATE_NAME = X_USER_CONCURRENT_STATE_NAME)
174 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
175 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
176 ) then
177 null;
178 else
179 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
180 app_exception.raise_exception;
181 end if;
182 return;
183 end LOCK_ROW;
184
185 procedure UPDATE_ROW (
186 X_CONCURRENT_STATE_ID in NUMBER,
187 X_APPLICATION_ID in NUMBER,
188 X_CONCURRENT_STATE_NAME in VARCHAR2,
189 X_ENABLED_FLAG in VARCHAR2,
190 X_START_DATE_ACTIVE in DATE,
191 X_END_DATE_ACTIVE in DATE,
192 X_STATUS in NUMBER,
193 X_LOOKUP_TYPE_ID in NUMBER,
194 X_UPDATED_FLAG in VARCHAR2,
195 X_USER_CONCURRENT_STATE_NAME in VARCHAR2,
196 X_DESCRIPTION in VARCHAR2,
197 X_LAST_UPDATE_DATE in DATE,
198 X_LAST_UPDATED_BY in NUMBER,
199 X_LAST_UPDATE_LOGIN in NUMBER
200 ) is
201 begin
202 update FND_CONC_RELEASE_STATES set
203 CONCURRENT_STATE_NAME = X_CONCURRENT_STATE_NAME,
204 ENABLED_FLAG = X_ENABLED_FLAG,
205 START_DATE_ACTIVE = X_START_DATE_ACTIVE,
206 END_DATE_ACTIVE = X_END_DATE_ACTIVE,
207 STATUS = X_STATUS,
208 LOOKUP_TYPE_ID = X_LOOKUP_TYPE_ID,
209 UPDATED_FLAG = X_UPDATED_FLAG,
210 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
211 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
212 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
213 where CONCURRENT_STATE_ID = X_CONCURRENT_STATE_ID
214 and APPLICATION_ID = X_APPLICATION_ID;
215
216 if (sql%notfound) then
217 raise no_data_found;
218 end if;
219
220 update FND_CONC_RELEASE_STATES_TL set
221 USER_CONCURRENT_STATE_NAME = X_USER_CONCURRENT_STATE_NAME,
222 DESCRIPTION = X_DESCRIPTION,
223 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
224 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
225 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
226 SOURCE_LANG = userenv('LANG')
227 where CONCURRENT_STATE_ID = X_CONCURRENT_STATE_ID
228 and APPLICATION_ID = X_APPLICATION_ID
229 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
230
231 if (sql%notfound) then
232 raise no_data_found;
233 end if;
234 end UPDATE_ROW;
235
236 procedure DELETE_ROW (
237 X_CONCURRENT_STATE_ID in NUMBER,
238 X_APPLICATION_ID in NUMBER
239 ) is
240 begin
241 delete from FND_CONC_RELEASE_STATES
242 where CONCURRENT_STATE_ID = X_CONCURRENT_STATE_ID
243 and APPLICATION_ID = X_APPLICATION_ID;
244
245 if (sql%notfound) then
246 raise no_data_found;
247 end if;
248
249 delete from FND_CONC_RELEASE_STATES_TL
250 where CONCURRENT_STATE_ID = X_CONCURRENT_STATE_ID
251 and APPLICATION_ID = X_APPLICATION_ID;
252
253 if (sql%notfound) then
254 raise no_data_found;
255 end if;
256 end DELETE_ROW;
257
258 procedure ADD_LANGUAGE
259 is
260 begin
261
262 /* Mar/19/03 requested by Ric Ginsberg */
263 /* The following delete and update statements are commented out */
264 /* as a quick workaround to fix the time-consuming table handler issue */
265 /* Eventually we'll need to turn them into a separate fix_language procedure */
266 /*
267 delete from FND_CONC_RELEASE_STATES_TL T
268 where not exists
269 (select NULL
270 from FND_CONC_RELEASE_STATES B
271 where B.CONCURRENT_STATE_ID = T.CONCURRENT_STATE_ID
272 and B.APPLICATION_ID = T.APPLICATION_ID
273 );
274
275 update FND_CONC_RELEASE_STATES_TL T set (
276 USER_CONCURRENT_STATE_NAME,
277 DESCRIPTION
278 ) = (select
279 B.USER_CONCURRENT_STATE_NAME,
280 B.DESCRIPTION
281 from FND_CONC_RELEASE_STATES_TL B
282 where B.CONCURRENT_STATE_ID = T.CONCURRENT_STATE_ID
283 and B.APPLICATION_ID = T.APPLICATION_ID
284 and B.LANGUAGE = T.SOURCE_LANG)
285 where (
286 T.CONCURRENT_STATE_ID,
287 T.APPLICATION_ID,
288 T.LANGUAGE
289 ) in (select
290 SUBT.CONCURRENT_STATE_ID,
291 SUBT.APPLICATION_ID,
292 SUBT.LANGUAGE
293 from FND_CONC_RELEASE_STATES_TL SUBB, FND_CONC_RELEASE_STATES_TL SUBT
294 where SUBB.CONCURRENT_STATE_ID = SUBT.CONCURRENT_STATE_ID
295 and SUBB.APPLICATION_ID = SUBT.APPLICATION_ID
296 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
297 and (SUBB.USER_CONCURRENT_STATE_NAME <> SUBT.USER_CONCURRENT_STATE_NAME
298 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
299 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
300 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
301 ));
302 */
303
304 insert into FND_CONC_RELEASE_STATES_TL (
305 APPLICATION_ID,
306 CONCURRENT_STATE_ID,
307 USER_CONCURRENT_STATE_NAME,
308 DESCRIPTION,
309 LAST_UPDATE_DATE,
310 LAST_UPDATED_BY,
311 LAST_UPDATE_LOGIN,
312 CREATION_DATE,
313 CREATED_BY,
314 LANGUAGE,
315 SOURCE_LANG
316 ) select
317 B.APPLICATION_ID,
318 B.CONCURRENT_STATE_ID,
319 B.USER_CONCURRENT_STATE_NAME,
320 B.DESCRIPTION,
321 B.LAST_UPDATE_DATE,
322 B.LAST_UPDATED_BY,
323 B.LAST_UPDATE_LOGIN,
324 B.CREATION_DATE,
325 B.CREATED_BY,
326 L.LANGUAGE_CODE,
327 B.SOURCE_LANG
328 from FND_CONC_RELEASE_STATES_TL B, FND_LANGUAGES L
329 where L.INSTALLED_FLAG in ('I', 'B')
330 and B.LANGUAGE = userenv('LANG')
331 and not exists
332 (select NULL
333 from FND_CONC_RELEASE_STATES_TL T
334 where T.CONCURRENT_STATE_ID = B.CONCURRENT_STATE_ID
335 and T.APPLICATION_ID = B.APPLICATION_ID
336 and T.LANGUAGE = L.LANGUAGE_CODE);
337 end ADD_LANGUAGE;
338
339 end FND_CONC_RELEASE_STATES_PKG;
[Home] [Help]