[Home] [Help]
PACKAGE BODY: APPS.CAC_SR_PERIOD_DTL_TYPES_PKG
Source
1 package body CAC_SR_PERIOD_DTL_TYPES_PKG as
2 /* $Header: cacsrprddtltypb.pls 120.1 2006/03/01 02:04:20 sbarat noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_PERIOD_DTL_TYPE_ID in NUMBER,
6 X_DISPLAY_COLOR in VARCHAR2,
7 X_PERIOD_DTL_TYPE_NAME in VARCHAR2,
8 X_PERIOD_DTL_TYPE_DESC 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 CAC_SR_PERIOD_DTL_TYPES_B
16 where PERIOD_DTL_TYPE_ID = X_PERIOD_DTL_TYPE_ID
17 ;
18 begin
19 insert into CAC_SR_PERIOD_DTL_TYPES_B (
20 PERIOD_DTL_TYPE_ID,
21 DISPLAY_COLOR,
22 CREATION_DATE,
23 CREATED_BY,
24 LAST_UPDATE_DATE,
25 LAST_UPDATED_BY,
26 LAST_UPDATE_LOGIN
27 ) values (
28 X_PERIOD_DTL_TYPE_ID,
29 X_DISPLAY_COLOR,
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 CAC_SR_PERIOD_DTL_TYPES_TL (
38 PERIOD_DTL_TYPE_ID,
39 PERIOD_DTL_TYPE_NAME,
40 PERIOD_DTL_TYPE_DESC,
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_PERIOD_DTL_TYPE_ID,
50 X_PERIOD_DTL_TYPE_NAME,
51 X_PERIOD_DTL_TYPE_DESC,
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 CAC_SR_PERIOD_DTL_TYPES_TL T
64 where T.PERIOD_DTL_TYPE_ID = X_PERIOD_DTL_TYPE_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_PERIOD_DTL_TYPE_ID in NUMBER,
79 X_DISPLAY_COLOR in VARCHAR2,
80 X_PERIOD_DTL_TYPE_NAME in VARCHAR2,
81 X_PERIOD_DTL_TYPE_DESC in VARCHAR2
82 ) is
83 cursor c is select
84 DISPLAY_COLOR
85 from CAC_SR_PERIOD_DTL_TYPES_B
86 where PERIOD_DTL_TYPE_ID = X_PERIOD_DTL_TYPE_ID
87 for update of PERIOD_DTL_TYPE_ID nowait;
88 recinfo c%rowtype;
89
90 cursor c1 is select
91 PERIOD_DTL_TYPE_NAME,
92 PERIOD_DTL_TYPE_DESC,
93 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
94 from CAC_SR_PERIOD_DTL_TYPES_TL
95 where PERIOD_DTL_TYPE_ID = X_PERIOD_DTL_TYPE_ID
96 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
97 for update of PERIOD_DTL_TYPE_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.DISPLAY_COLOR = X_DISPLAY_COLOR)
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.PERIOD_DTL_TYPE_NAME = X_PERIOD_DTL_TYPE_NAME)
118 AND ((tlinfo.PERIOD_DTL_TYPE_DESC = X_PERIOD_DTL_TYPE_DESC)
119 OR ((tlinfo.PERIOD_DTL_TYPE_DESC is null) AND (X_PERIOD_DTL_TYPE_DESC 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_PERIOD_DTL_TYPE_ID in NUMBER,
133 X_DISPLAY_COLOR in VARCHAR2,
134 X_PERIOD_DTL_TYPE_NAME in VARCHAR2,
135 X_PERIOD_DTL_TYPE_DESC 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 CAC_SR_PERIOD_DTL_TYPES_B set
142 DISPLAY_COLOR = X_DISPLAY_COLOR,
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 PERIOD_DTL_TYPE_ID = X_PERIOD_DTL_TYPE_ID;
147
148 if (sql%notfound) then
149 raise no_data_found;
150 end if;
151
152 update CAC_SR_PERIOD_DTL_TYPES_TL set
153 PERIOD_DTL_TYPE_NAME = X_PERIOD_DTL_TYPE_NAME,
154 PERIOD_DTL_TYPE_DESC = X_PERIOD_DTL_TYPE_DESC,
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 PERIOD_DTL_TYPE_ID = X_PERIOD_DTL_TYPE_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_PERIOD_DTL_TYPE_ID in NUMBER
169 ) is
170 begin
171 delete from CAC_SR_PERIOD_DTL_TYPES_TL
172 where PERIOD_DTL_TYPE_ID = X_PERIOD_DTL_TYPE_ID;
173
174 if (sql%notfound) then
175 raise no_data_found;
176 end if;
177
178 delete from CAC_SR_PERIOD_DTL_TYPES_B
179 where PERIOD_DTL_TYPE_ID = X_PERIOD_DTL_TYPE_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 CAC_SR_PERIOD_DTL_TYPES_TL T
190 where not exists
191 (select NULL
192 from CAC_SR_PERIOD_DTL_TYPES_B B
193 where B.PERIOD_DTL_TYPE_ID = T.PERIOD_DTL_TYPE_ID
194 );
195
196 update CAC_SR_PERIOD_DTL_TYPES_TL T set (
197 PERIOD_DTL_TYPE_NAME,
198 PERIOD_DTL_TYPE_DESC
199 ) = (select
200 B.PERIOD_DTL_TYPE_NAME,
201 B.PERIOD_DTL_TYPE_DESC
202 from CAC_SR_PERIOD_DTL_TYPES_TL B
203 where B.PERIOD_DTL_TYPE_ID = T.PERIOD_DTL_TYPE_ID
204 and B.LANGUAGE = T.SOURCE_LANG)
205 where (
206 T.PERIOD_DTL_TYPE_ID,
207 T.LANGUAGE
208 ) in (select
209 SUBT.PERIOD_DTL_TYPE_ID,
210 SUBT.LANGUAGE
211 from CAC_SR_PERIOD_DTL_TYPES_TL SUBB, CAC_SR_PERIOD_DTL_TYPES_TL SUBT
212 where SUBB.PERIOD_DTL_TYPE_ID = SUBT.PERIOD_DTL_TYPE_ID
213 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
214 and (SUBB.PERIOD_DTL_TYPE_NAME <> SUBT.PERIOD_DTL_TYPE_NAME
215 or SUBB.PERIOD_DTL_TYPE_DESC <> SUBT.PERIOD_DTL_TYPE_DESC
216 or (SUBB.PERIOD_DTL_TYPE_DESC is null and SUBT.PERIOD_DTL_TYPE_DESC is not null)
217 or (SUBB.PERIOD_DTL_TYPE_DESC is not null and SUBT.PERIOD_DTL_TYPE_DESC is null)
218 ));
219
220 insert into CAC_SR_PERIOD_DTL_TYPES_TL (
221 PERIOD_DTL_TYPE_ID,
222 PERIOD_DTL_TYPE_NAME,
223 PERIOD_DTL_TYPE_DESC,
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 /*+ ORDERED */
232 B.PERIOD_DTL_TYPE_ID,
233 B.PERIOD_DTL_TYPE_NAME,
234 B.PERIOD_DTL_TYPE_DESC,
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 CAC_SR_PERIOD_DTL_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 CAC_SR_PERIOD_DTL_TYPES_TL T
248 where T.PERIOD_DTL_TYPE_ID = B.PERIOD_DTL_TYPE_ID
249 and T.LANGUAGE = L.LANGUAGE_CODE);
250 end ADD_LANGUAGE;
251
252 /************ Start of addition by SBARAT on 01/03/2006 for bug# 5031486 ***********/
253 PROCEDURE TRANSLATE_ROW(
254 X_PERIOD_DTL_TYPE_ID IN NUMBER,
255 X_PERIOD_DTL_TYPE_NAME IN VARCHAR2,
256 X_PERIOD_DTL_TYPE_DESC IN VARCHAR2,
257 X_OWNER IN VARCHAR2)
258 IS
259 l_user_id NUMBER := 0;
260 BEGIN
261 IF x_owner = 'SEED'
262 THEN
263 l_user_id := 1;
264 END IF;
265
266 UPDATE cac_sr_period_dtl_types_tl
267 SET period_dtl_type_name = NVL(X_PERIOD_DTL_TYPE_NAME, period_dtl_type_name ) ,
268 period_dtl_type_desc = NVL(X_PERIOD_DTL_TYPE_DESC, period_dtl_type_desc),
269 last_updated_by = l_user_id,
270 last_update_date = sysdate,
271 last_update_login = 0,
272 source_lang = USERENV ('LANG')
273 WHERE period_dtl_type_id = x_period_dtl_type_id
274 And USERENV('LANG') In (LANGUAGE, SOURCE_LANG);
275
276 IF (SQL%NOTFOUND)
277 THEN
278 Raise NO_DATA_FOUND;
279 END IF;
280
281 END TRANSLATE_ROW;
282
283
284 PROCEDURE LOAD_ROW (
285 X_PERIOD_DTL_TYPE_ID IN NUMBER,
286 X_DISPLAY_COLOR IN VARCHAR2,
287 X_PERIOD_DTL_TYPE_NAME IN VARCHAR2,
288 X_PERIOD_DTL_TYPE_DESC IN VARCHAR2,
289 X_OWNER IN VARCHAR2
290 )
291 IS
292 l_user_id NUMBER := 0;
293 l_period_dtl_type_id NUMBER;
294 l_rowid ROWID;
295 BEGIN
296
297 IF x_owner = 'SEED'
298 THEN
299 l_user_id := 1;
300 END IF;
301
302
303 SELECT period_dtl_type_id
304 INTO l_period_dtl_type_id
305 FROM cac_sr_period_dtl_types_b
306 WHERE period_dtl_type_id = x_period_dtl_type_id;
307
308
309 UPDATE cac_sr_period_dtl_types_b
310 SET display_color = x_display_color,
311 last_updated_by = l_user_id,
312 last_update_date = sysdate,
313 last_update_login = 0
314 WHERE period_dtl_type_id = l_period_dtl_type_id;
315
316 UPDATE cac_sr_period_dtl_types_tl
317 SET period_dtl_type_name = x_period_dtl_type_name,
318 period_dtl_type_desc = x_period_dtl_type_desc,
319 last_updated_by = l_user_id,
320 last_update_date = sysdate,
321 last_update_login = 0,
322 source_lang = USERENV ('LANG')
323 WHERE period_dtl_type_id = l_period_dtl_type_id
324 And USERENV ('LANG') In (LANGUAGE, SOURCE_LANG);
325
326 EXCEPTION
327 WHEN no_data_found THEN
328 CAC_SR_PERIOD_DTL_TYPES_PKG.INSERT_ROW
329 (
330 x_rowid => l_rowid ,
331 x_period_dtl_type_id => x_period_dtl_type_id,
332 x_display_color => x_display_color,
333 x_period_dtl_type_name => x_period_dtl_type_name,
334 x_period_dtl_type_desc => x_period_dtl_type_desc,
335 x_creation_date => SYSDATE,
336 x_created_by => l_user_id,
337 x_last_update_date => SYSDATE,
338 x_last_updated_by => l_user_id,
339 x_last_update_login => 0
340 );
341
342 END LOAD_ROW ;
343 /************ End of addition by SBARAT on 01/03/2006 for bug# 5031486 ***********/
344
345 end CAC_SR_PERIOD_DTL_TYPES_PKG;