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