[Home] [Help]
PACKAGE BODY: APPS.CSC_PROF_BLOCKS_PKG
Source
1 PACKAGE BODY CSC_PROF_BLOCKS_PKG as
2 /* $Header: csctpvab.pls 120.4 2005/09/18 23:15:07 vshastry ship $ */
3 -- Start of Comments
4 -- Package name : CSC_PROF_BLOCKS_PKG
5 -- Purpose :
6 -- History :
7 -- 03 Nov 00 axsubram Added Translate_row,load_row (# 1487860)
8 -- 03 Nov 00 axsubram File name constant corrected to csctpvab.pls
9 -- 6 Nov 2002 jamose 1159 Upgrade changes for table handlers
10 -- Added seed condition and CSC_CORE_UTILS dependency removed
11 -- 18 Nov 02 jamose made changes for the NOCOPY and FND_API.G_MISS*
12 -- 19-07-2005 tpalaniv Modified the translate_row and load_row APIs to fetch last_updated_by using FND API
13 -- 19-09-2005 vshastry Bug 4596220. Added condition in insert row
14 -- NOTE :
15 -- End of Comments
16
17
18 G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSC_PROF_BLOCKS_PKG';
19 G_FILE_NAME CONSTANT VARCHAR2(12) := 'csctpvab.pls';
20
21 -- jamose
22 G_MISS_CHAR VARCHAR2(1) := FND_API.G_MISS_CHAR;
23 G_MISS_NUM NUMBER := FND_API.G_MISS_NUM;
24 G_MISS_DATE DATE := FND_API.G_MISS_DATE;
25
26 PROCEDURE Insert_Row(
27 px_BLOCK_ID IN OUT NOCOPY NUMBER,
28 p_CREATED_BY NUMBER,
29 p_CREATION_DATE DATE,
30 p_LAST_UPDATED_BY NUMBER,
31 p_LAST_UPDATE_DATE DATE,
32 p_LAST_UPDATE_LOGIN NUMBER,
33 p_BLOCK_NAME VARCHAR2,
34 p_DESCRIPTION VARCHAR2,
35 p_START_DATE_ACTIVE DATE,
36 p_END_DATE_ACTIVE DATE,
37 p_SEEDED_FLAG VARCHAR2,
38 p_BLOCK_NAME_CODE VARCHAR2,
39 p_OBJECT_CODE VARCHAR2,
40 p_SQL_STMNT_FOR_DRILLDOWN VARCHAR2,
41 p_SQL_STMNT VARCHAR2,
42 p_BATCH_SQL_STMNT VARCHAR2,
43 p_SELECT_CLAUSE VARCHAR2,
44 p_CURRENCY_CODE VARCHAR2,
45 p_FROM_CLAUSE VARCHAR2,
46 p_WHERE_CLAUSE VARCHAR2,
47 p_OTHER_CLAUSE VARCHAR2,
48 p_BLOCK_LEVEL VARCHAR2,
49 x_OBJECT_VERSION_NUMBER OUT NOCOPY NUMBER,
50 p_APPLICATION_ID NUMBER)
51 IS
52 Cursor new_seq_csr IS Select csc_prof_blocks_s.nextval
53 from dual;
54
55 l_object_version_number NUMBER := 1;
56 ps_seeded_flag Varchar2(3);
57 BEGIN
58
59 /* added the below 2 lines for bug 4596220 */
60 ps_seeded_flag := p_seeded_flag;
61 IF NVL(p_seeded_flag, 'N') <> 'Y' THEN
62 /* Added This If Condition for Bug 1944040*/
63 If (p_Created_by=1) then
64 ps_seeded_flag:='Y';
65 Else
66 ps_seeded_flag:='N';
67 End If;
68 END IF;
69
70 If (px_BLOCK_ID IS NULL) OR (px_BLOCK_ID = G_MISS_NUM) then
71 Open new_seq_csr;
72 Fetch new_seq_csr into px_block_id;
73 Close new_seq_csr;
74 End If;
75 -- to_date(NULL) added to include timestamp during creation
76 INSERT INTO CSC_PROF_BLOCKS_B(
77 BLOCK_ID,
78 CREATED_BY,
79 CREATION_DATE,
80 LAST_UPDATED_BY,
81 LAST_UPDATE_DATE,
82 LAST_UPDATE_LOGIN,
83 -- BLOCK_NAME,
84 -- DESCRIPTION,
85 START_DATE_ACTIVE,
86 END_DATE_ACTIVE,
87 SEEDED_FLAG,
88 BLOCK_NAME_CODE,
89 OBJECT_CODE,
90 SQL_STMNT_FOR_DRILLDOWN,
91 SQL_STMNT,
92 BATCH_SQL_STMNT,
93 SELECT_CLAUSE,
94 CURRENCY_CODE,
95 FROM_CLAUSE,
96 WHERE_CLAUSE,
97 OTHER_CLAUSE,
98 BLOCK_LEVEL,
99 OBJECT_VERSION_NUMBER,
100 APPLICATION_ID)
101 VALUES (
102 px_BLOCK_ID,
103 decode( p_CREATED_BY, G_MISS_NUM, NULL, p_CREATED_BY),
104 decode( p_CREATION_DATE, G_MISS_DATE, to_date(NULL), p_CREATION_DATE),
105 decode( p_LAST_UPDATED_BY, G_MISS_NUM, NULL, p_LAST_UPDATED_BY),
106 decode( p_LAST_UPDATE_DATE,G_MISS_DATE,to_date(NULL), p_LAST_UPDATE_DATE),
107 decode( p_LAST_UPDATE_LOGIN,G_MISS_NUM, NULL, p_LAST_UPDATE_LOGIN),
108 -- decode( p_BLOCK_NAME, CSC_CORE_UTILS_PVT.G_MISS_CHAR, NULL, p_BLOCK_NAME),
109 -- decode( p_DESCRIPTION, CSC_CORE_UTILS_PVT.G_MISS_CHAR, NULL, p_DESCRIPTION),
110 decode( p_START_DATE_ACTIVE, G_MISS_DATE,to_date(NULL), p_START_DATE_ACTIVE),
111 decode( p_END_DATE_ACTIVE, G_MISS_DATE,to_date(NULL), p_END_DATE_ACTIVE),
112 decode( p_SEEDED_FLAG, G_MISS_CHAR, NULL, ps_SEEDED_FLAG),
113 decode( p_BLOCK_NAME_CODE, G_MISS_CHAR, NULL, p_BLOCK_NAME_CODE),
114 decode( p_OBJECT_CODE, G_MISS_CHAR, NULL, p_OBJECT_CODE),
115 decode( p_SQL_STMNT_FOR_DRILLDOWN, G_MISS_CHAR, NULL, p_SQL_STMNT_FOR_DRILLDOWN),
116 decode( p_SQL_STMNT, G_MISS_CHAR, NULL, p_SQL_STMNT),
117 decode( p_BATCH_SQL_STMNT, G_MISS_CHAR, NULL, p_BATCH_SQL_STMNT),
118 decode( p_SELECT_CLAUSE, G_MISS_CHAR, NULL, p_SELECT_CLAUSE),
119 decode( p_CURRENCY_CODE, G_MISS_CHAR, NULL, p_CURRENCY_CODE),
120 decode( p_FROM_CLAUSE, G_MISS_CHAR, NULL, p_FROM_CLAUSE),
121 decode( p_WHERE_CLAUSE, G_MISS_CHAR, NULL, p_WHERE_CLAUSE),
122 decode( p_OTHER_CLAUSE, G_MISS_CHAR, NULL, p_Other_CLAUSE),
123 decode( p_BLOCK_LEVEL, G_MISS_CHAR, NULL, p_BLOCK_LEVEL),
124 l_object_version_number,
125 decode( p_application_id, G_MISS_NUM, NULL, p_application_id)
126 );
127
128 -- assigning the out parameters
129 x_object_version_number := l_object_version_number;
130
131 INSERT INTO CSC_PROF_BLOCKS_TL (
132 BLOCK_ID,
133 BLOCK_NAME,
134 DESCRIPTION,
135 CREATED_BY,
136 CREATION_DATE,
137 LAST_UPDATED_BY,
138 LAST_UPDATE_DATE,
139 LAST_UPDATE_LOGIN,
140 LANGUAGE,
141 SOURCE_LANG )
142 SELECT
143 px_BLOCK_ID,
144 decode( p_BLOCK_NAME, G_MISS_CHAR, NULL, p_BLOCK_NAME),
145 decode( p_DESCRIPTION,G_MISS_CHAR, NULL, p_DESCRIPTION),
146 p_CREATED_BY,
147 p_CREATION_DATE,
148 p_LAST_UPDATED_BY,
149 p_LAST_UPDATE_DATE,
150 p_LAST_UPDATE_LOGIN,
151 L.LANGUAGE_CODE,
152 userenv('LANG')
153 FROM FND_LANGUAGES L
154 WHERE L.INSTALLED_FLAG in ('I', 'B')
155 AND not exists
156 (SELECT NULL
157 FROM CSC_PROF_BLOCKS_TL T
158 WHERE T.BLOCK_ID = PX_BLOCK_ID
159 AND T.LANGUAGE = L.LANGUAGE_CODE);
160
161 End Insert_Row;
162
163 PROCEDURE Update_Row(
164 p_BLOCK_ID NUMBER,
165 p_LAST_UPDATED_BY NUMBER,
166 p_LAST_UPDATE_DATE DATE,
167 p_LAST_UPDATE_LOGIN NUMBER,
168 p_BLOCK_NAME VARCHAR2,
169 p_DESCRIPTION VARCHAR2,
170 p_START_DATE_ACTIVE DATE,
171 p_END_DATE_ACTIVE DATE,
172 p_SEEDED_FLAG VARCHAR2,
173 p_BLOCK_NAME_CODE VARCHAR2,
174 p_OBJECT_CODE VARCHAR2,
175 p_SQL_STMNT_FOR_DRILLDOWN VARCHAR2,
176 p_SQL_STMNT VARCHAR2,
177 p_BATCH_SQL_STMNT VARCHAR2,
178 p_SELECT_CLAUSE VARCHAR2,
179 p_CURRENCY_CODE VARCHAR2,
180 p_FROM_CLAUSE VARCHAR2,
181 p_WHERE_CLAUSE VARCHAR2,
182 p_OTHER_CLAUSE VARCHAR2,
183 p_BLOCK_LEVEL VARCHAR2,
184 px_OBJECT_VERSION_NUMBER IN OUT NOCOPY NUMBER,
185 p_APPLICATION_ID NUMBER)
186 IS
187 BEGIN
188 /* Though we do not have any default null, for update case we need to preserve the
189 the old values so an nvl has been used. By doing this we can avoid an excess null
190 checking on the private api which are not really required for validation and no
191 impact even if the api is called from not through priviate api -jamose
192
193 */
194 UPDATE CSC_PROF_BLOCKS_B
195 SET
196 LAST_UPDATED_BY = p_LAST_UPDATED_BY,
197 LAST_UPDATE_DATE = p_LAST_UPDATE_DATE,
198 LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN,
199 -- BLOCK_NAME = decode( p_BLOCK_NAME, G_MISS_CHAR, BLOCK_NAME, p_BLOCK_NAME),
200 -- DESCRIPTION = decode( p_DESCRIPTION, G_MISS_CHAR, DESCRIPTION, p_DESCRIPTION),
201 START_DATE_ACTIVE = p_START_DATE_ACTIVE,
202 END_DATE_ACTIVE = p_END_DATE_ACTIVE,
203 SEEDED_FLAG = p_SEEDED_FLAG,
204 BLOCK_NAME_CODE = p_BLOCK_NAME_CODE,
205 OBJECT_CODE = p_OBJECT_CODE,
206 SQL_STMNT_FOR_DRILLDOWN = p_SQL_STMNT_FOR_DRILLDOWN,
207 SQL_STMNT = p_SQL_STMNT,
208 BATCH_SQL_STMNT = p_BATCH_SQL_STMNT,
209 SELECT_CLAUSE = p_SELECT_CLAUSE,
210 CURRENCY_CODE = p_CURRENCY_CODE,
211 FROM_CLAUSE = p_FROM_CLAUSE,
212 WHERE_CLAUSE = p_WHERE_CLAUSE,
213 OTHER_CLAUSE =p_OTHER_CLAUSE,
214 BLOCK_LEVEL = p_BLOCK_LEVEL,
215 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
216 APPLICATION_ID = p_application_id
217 where BLOCK_ID = p_BLOCK_ID
218 RETURNING OBJECT_VERSION_NUMBER INTO px_OBJECT_VERSION_NUMBER;
219 UPDATE CSC_PROF_BLOCKS_TL
220 SET BLOCK_NAME = p_BLOCK_NAME,
221 DESCRIPTION = p_DESCRIPTION,
222 LAST_UPDATE_DATE = p_LAST_UPDATE_DATE,
223 LAST_UPDATED_BY = p_LAST_UPDATED_BY,
224 LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN,
225 SOURCE_LANG = userenv('LANG')
226 WHERE BLOCK_ID = P_BLOCK_ID
227 AND userenv('LANG') in (LANGUAGE, SOURCE_LANG);
228
229 if (sql%rowcount = 0) then
230 raise no_Data_found;
231 end if;
232
233 END Update_Row;
234
235 procedure LOCK_ROW (
236 P_BLOCK_ID in NUMBER,
237 P_OBJECT_VERSION_NUMBER in NUMBER
238 ) is
239 cursor c is select 'X'
240 from CSC_PROF_BLOCKS_B
241 where BLOCK_ID = P_BLOCK_ID
242 and object_version_number = P_object_version_number
243 for update of BLOCK_ID nowait;
244 recinfo c%rowtype;
245
246 cursor c1 is select
247 BLOCK_NAME,
248 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
249 from CSC_PROF_BLOCKS_TL
250 where BLOCK_ID = P_BLOCK_ID
251 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
252 for update of BLOCK_ID nowait;
253 begin
254 open c;
255 fetch c into recinfo;
256 if (c%notfound) then
257 close c;
258 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
259 app_exception.raise_exception;
260 end if;
261 close c;
262 return;
263 end LOCK_ROW;
264
265 procedure DELETE_ROW (
266 P_BLOCK_ID NUMBER,
267 P_OBJECT_VERSION_NUMBER NUMBER
268 ) is
269 begin
270 delete from CSC_PROF_BLOCKS_TL
271 where BLOCK_ID = P_BLOCK_ID;
272
273 if (sql%notfound) then
274 raise no_data_found;
275 end if;
276
277 delete from CSC_PROF_BLOCKS_B
278 where BLOCK_ID = P_BLOCK_ID
279 and OBJECT_VERSION_NUMBER = p_OBJECT_VERSION_NUMBER;
280
281 if (sql%notfound) then
282 raise no_data_found;
283 end if;
284 end DELETE_ROW;
285
286 procedure ADD_LANGUAGE
287 is
288 begin
289 delete from CSC_PROF_BLOCKS_TL T
290 where not exists
291 (select NULL
292 from CSC_PROF_BLOCKS_B B
293 where B.BLOCK_ID = T.BLOCK_ID
294 );
295
296 update CSC_PROF_BLOCKS_TL T set (
297 BLOCK_NAME,
298 DESCRIPTION
299 ) = (select
300 B.BLOCK_NAME,
301 B.DESCRIPTION
302 from CSC_PROF_BLOCKS_TL B
303 where B.BLOCK_ID = T.BLOCK_ID
304 and B.LANGUAGE = T.SOURCE_LANG)
305 where (
306 T.BLOCK_ID,
307 T.LANGUAGE
308 ) in (select
309 SUBT.BLOCK_ID,
310 SUBT.LANGUAGE
311 from CSC_PROF_BLOCKS_TL SUBB, CSC_PROF_BLOCKS_TL SUBT
312 where SUBB.BLOCK_ID = SUBT.BLOCK_ID
313 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
314 and (SUBB.BLOCK_NAME <> SUBT.BLOCK_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 insert into CSC_PROF_BLOCKS_TL (
321 BLOCK_ID,
322 BLOCK_NAME,
323 DESCRIPTION,
324 CREATED_BY,
325 CREATION_DATE,
326 LAST_UPDATED_BY,
327 LAST_UPDATE_DATE,
328 LAST_UPDATE_LOGIN,
329 LANGUAGE,
330 SOURCE_LANG
331 ) select
332 B.BLOCK_ID,
333 B.BLOCK_NAME,
334 B.DESCRIPTION,
335 B.CREATED_BY,
336 B.CREATION_DATE,
337 B.LAST_UPDATED_BY,
338 B.LAST_UPDATE_DATE,
339 B.LAST_UPDATE_LOGIN,
340 L.LANGUAGE_CODE,
341 B.SOURCE_LANG
342 from CSC_PROF_BLOCKS_TL B, FND_LANGUAGES L
343 where L.INSTALLED_FLAG in ('I', 'B')
344 and B.LANGUAGE = userenv('LANG')
345 and not exists
346 (select NULL
347 from CSC_PROF_BLOCKS_TL T
348 where T.BLOCK_ID = B.BLOCK_ID
349 and T.LANGUAGE = L.LANGUAGE_CODE);
350 end ADD_LANGUAGE;
351
352 PROCEDURE Translate_Row(
353 p_block_id NUMBER,
354 p_block_name VARCHAR2,
355 p_description VARCHAR2,
356 p_owner VARCHAR2)
357 IS
358 Begin
359 Update Csc_Prof_Blocks_TL set
360 block_name = nvl(p_block_name,block_name),
361 description = nvl(p_description,description),
362 last_update_date = sysdate,
363 last_updated_by = fnd_load_util.owner_id(p_owner),
364 last_update_login = 0,
365 source_lang = userenv('LANG')
366 Where block_id = p_block_id
370
367 and userenv('LANG') in (language, source_lang);
368
369 End Translate_Row;
371 PROCEDURE Load_Row(
372 p_BLOCK_ID NUMBER,
373 p_LAST_UPDATED_BY NUMBER,
374 p_LAST_UPDATE_DATE DATE,
375 p_LAST_UPDATE_LOGIN NUMBER,
376 p_BLOCK_NAME VARCHAR2,
377 p_DESCRIPTION VARCHAR2,
378 p_START_DATE_ACTIVE DATE,
379 p_END_DATE_ACTIVE DATE,
380 p_SEEDED_FLAG VARCHAR2,
381 p_BLOCK_NAME_CODE VARCHAR2,
382 p_OBJECT_CODE VARCHAR2,
383 p_SQL_STMNT_FOR_DRILLDOWN VARCHAR2,
384 p_SQL_STMNT VARCHAR2,
385 p_BATCH_SQL_STMNT VARCHAR2,
386 p_SELECT_CLAUSE VARCHAR2,
387 p_CURRENCY_CODE VARCHAR2,
388 p_FROM_CLAUSE VARCHAR2,
389 p_WHERE_CLAUSE VARCHAR2,
390 p_OTHER_CLAUSE VARCHAR2,
391 p_BLOCK_LEVEL VARCHAR2,
392 px_OBJECT_VERSION_NUMBER IN OUT NOCOPY NUMBER,
393 p_APPLICATION_ID NUMBER,
394 p_owner VARCHAR2)
395 IS
396
397 l_user_id number := 0;
398 l_block_id number := G_MISS_NUM;
399 l_object_version_number number := 0;
400
401 /** This is mainly for loading seed data . That is the
402 reason, that l_check_id is being declared here, The check_id
403 returned from insert_row is not used.
404
405 2. Object_version_number is not passed . It is assumed that
406 seed data would be run when other users are not using
407 the system
408 **/
409 BEGIN
410
411 /* commented for R12 ATG Project
412 if (p_owner = 'SEED') then
413 l_user_id := 1;
414 end if;
415 */
416
417 l_block_id := p_block_id ;
418
419 Csc_Prof_Blocks_Pkg.Update_Row(
420 p_BLOCK_ID => p_block_id,
421 p_LAST_UPDATED_BY => p_last_updated_by,
422 p_LAST_UPDATE_DATE => p_last_update_date,
423 p_LAST_UPDATE_LOGIN => 0,
424 p_BLOCK_NAME => p_block_name,
425 p_DESCRIPTION => p_description,
426 p_START_DATE_ACTIVE => to_date(p_start_date_active,'YYYY/MM/DD'),
427 p_END_DATE_ACTIVE => to_date(p_end_date_active,'YYYY/MM/DD'),
428 p_SEEDED_FLAG => p_seeded_flag,
429 p_BLOCK_NAME_CODE => p_block_name_code,
430 p_OBJECT_CODE => p_object_code,
431 p_SQL_STMNT_FOR_DRILLDOWN => p_sql_stmnt_for_drilldown,
432 p_SQL_STMNT => p_sql_stmnt,
433 p_BATCH_SQL_STMNT => p_batch_sql_stmnt,
434 p_SELECT_CLAUSE => p_select_clause,
435 p_CURRENCY_CODE => p_currency_code,
436 p_FROM_CLAUSE => p_from_clause,
437 p_WHERE_CLAUSE => p_where_clause,
438 p_OTHER_CLAUSE => p_other_clause,
439 p_BLOCK_LEVEL => p_block_level,
440 px_OBJECT_VERSION_NUMBER => l_object_version_number,
441 p_APPLICATION_ID => p_application_id);
442
443 EXCEPTION
444 WHEN NO_DATA_FOUND THEN
445 Csc_Prof_Blocks_Pkg.Insert_Row(
446 px_BLOCK_ID => l_block_id,
447 p_CREATED_BY => p_last_updated_by,
448 p_CREATION_DATE => p_last_update_date,
449 p_LAST_UPDATED_BY => p_last_updated_by,
450 p_LAST_UPDATE_DATE => p_last_update_date,
451 p_LAST_UPDATE_LOGIN => 0,
452 p_BLOCK_NAME => p_block_name,
453 p_DESCRIPTION => p_description,
454 p_START_DATE_ACTIVE => to_date(p_start_date_active,'YYYY/MM/DD'),
455 p_END_DATE_ACTIVE => to_date(p_end_date_active,'YYYY/MM/DD'),
456 p_SEEDED_FLAG => p_seeded_flag,
457 p_BLOCK_NAME_CODE => p_block_name_code,
458 p_OBJECT_CODE => p_object_code,
459 p_SQL_STMNT_FOR_DRILLDOWN => p_sql_stmnt_for_drilldown,
460 p_SQL_STMNT => p_sql_stmnt,
461 p_BATCH_SQL_STMNT => p_batch_sql_stmnt,
462 p_SELECT_CLAUSE => p_select_clause,
463 p_CURRENCY_CODE => p_currency_code,
464 p_FROM_CLAUSE => p_from_clause,
465 p_WHERE_CLAUSE => p_where_clause,
466 p_OTHER_CLAUSE => p_other_clause,
467 p_BLOCK_LEVEL => p_block_level,
468 x_OBJECT_VERSION_NUMBER => l_object_version_number,
469 p_APPLICATION_ID =>p_application_id);
470
471 END Load_Row;
472
473 End CSC_PROF_BLOCKS_PKG;