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