[Home] [Help]
PACKAGE BODY: APPS.CSC_PROFILE_CHECK_RULES_PKG
Source
1 PACKAGE BODY CSC_PROFILE_CHECK_RULES_PKG as
2 /* $Header: csctpcrb.pls 120.3 2005/09/18 23:46:20 vshastry ship $ */
3 -- Start of Comments
4 -- Package name : CSC_PROFILE_CHECK_RULES_PKG
5 -- Purpose :
6 -- History :
7 -- 08-NOV-00 madhavan Added procedures translate_row and load_row. Fix to
8 -- bug # 1491205
9 -- 07 Nov 02 jamose Upgrade table handler changes
10 -- 18 Nov 02 jamose made changes for the NOCOPY and FND_API.G_MISS*
11 -- 19 july 2005 tpalaniv Deriving last_updated_by based on FND API for R12 ATG Project - Seed Data Versioning
12 -- 19-09-2005 vshastry Bug 4596220. Added condition in insert row
13 -- NOTE :
14 -- End of Comments
15
16 G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSC_PROFILE_CHECK_RULES_PKG';
17 G_FILE_NAME CONSTANT VARCHAR2(12) := 'csctucrb.pls';
18
19 G_MISS_CHAR VARCHAR2(1) := FND_API.G_MISS_CHAR;
20 G_MISS_NUM NUMBER := FND_API.G_MISS_NUM;
21 G_MISS_DATE DATE := FND_API.G_MISS_DATE;
22
23 PROCEDURE Insert_Row(
24 p_CHECK_ID IN NUMBER,
25 p_SEQUENCE NUMBER,
26 p_CREATED_BY NUMBER,
27 p_CREATION_DATE DATE,
28 p_LAST_UPDATED_BY NUMBER,
29 p_LAST_UPDATE_DATE DATE,
30 p_LAST_UPDATE_LOGIN NUMBER,
31 p_LOGICAL_OPERATOR VARCHAR2,
32 p_LEFT_PAREN VARCHAR2,
33 p_BLOCK_ID NUMBER,
34 p_COMPARISON_OPERATOR VARCHAR2,
35 p_EXPRESSION VARCHAR2,
36 p_EXPR_TO_BLOCK_ID NUMBER,
37 p_RIGHT_PAREN VARCHAR2,
38 p_SEEDED_FLAG VARCHAR2,
39 X_OBJECT_VERSION_NUMBER OUT NOCOPY NUMBER )
40
41 IS
42 -- CURSOR C2 IS SELECT CSC_PROF_CHECK_RULES_S.nextval FROM sys.dual;
43 ps_SEEDED_FLAG Varchar2(3);
44 BEGIN
45
46 /* added the below 2 lines for bug 4596220 */
47 ps_seeded_flag := p_seeded_flag;
48 IF NVL(p_seeded_flag, 'N') <> 'Y' THEN
49
50 /* Added This If Condition for Bug 1944040*/
51 If p_Created_by=1 then
52 ps_seeded_flag:='Y';
53 Else
54 ps_seeded_flag:='N';
55 End If;
56 END IF;
57
58 /*
59 If (px_CHECK_ID IS NULL) OR (px_CHECK_ID = CSC_CORE_UTILS_PVT.G_MISS_NUM) then
60 OPEN C2;
61 FETCH C2 INTO px_CHECK_ID;
62 CLOSE C2;
63 End If;
64 */
65 x_object_version_number := 1;
66
67 INSERT INTO CSC_PROF_CHECK_RULES_B(
68 CHECK_ID,
69 SEQUENCE,
70 CREATED_BY,
71 CREATION_DATE,
72 LAST_UPDATED_BY,
73 LAST_UPDATE_DATE,
74 LAST_UPDATE_LOGIN,
75 LOGICAL_OPERATOR,
76 LEFT_PAREN,
77 BLOCK_ID,
78 COMPARISON_OPERATOR,
79 --EXPRESSION1,
80 EXPR_TO_BLOCK_ID,
81 RIGHT_PAREN,
82 SEEDED_FLAG,
83 OBJECT_VERSION_NUMBER
84 ) VALUES (
85 p_CHECK_ID,
86 decode( p_SEQUENCE, G_MISS_NUM, NULL, p_SEQUENCE),
87 decode( p_CREATED_BY, G_MISS_NUM, NULL, p_CREATED_BY),
88 decode( p_CREATION_DATE, G_MISS_DATE, NULL, p_CREATION_DATE),
89 decode( p_LAST_UPDATED_BY, G_MISS_NUM, NULL, p_LAST_UPDATED_BY),
90 decode( p_LAST_UPDATE_DATE, G_MISS_DATE, NULL, p_LAST_UPDATE_DATE),
91 decode( p_LAST_UPDATE_LOGIN,G_MISS_NUM, NULL, p_LAST_UPDATE_LOGIN),
92 decode( p_LOGICAL_OPERATOR, G_MISS_CHAR, NULL, p_LOGICAL_OPERATOR),
93 decode( p_LEFT_PAREN, G_MISS_CHAR, NULL, p_LEFT_PAREN),
94 decode( p_BLOCK_ID, G_MISS_NUM, NULL, p_BLOCK_ID),
95 decode( p_COMPARISON_OPERATOR, G_MISS_CHAR, NULL, p_COMPARISON_OPERATOR),
96 --decode( p_EXPRESSION, CSC_CORE_UTILS_PVT.G_MISS_CHAR, NULL, p_EXPRESSION),
97 decode( p_EXPR_TO_BLOCK_ID, G_MISS_NUM, NULL, p_EXPR_TO_BLOCK_ID),
98 decode( p_RIGHT_PAREN, G_MISS_CHAR, NULL, p_RIGHT_PAREN),
99 decode( p_SEEDED_FLAG, G_MISS_CHAR, NULL, ps_SEEDED_FLAG),
100 x_object_version_number);
101
102 INSERT INTO CSC_PROF_CHECK_RULES_TL(
103 CHECK_ID,
104 SEQUENCE,
105 EXPRESSION,
106 CREATED_BY,
107 CREATION_DATE,
108 LAST_UPDATED_BY,
109 LAST_UPDATE_DATE,
110 LAST_UPDATE_LOGIN,
111 LANGUAGE,
112 SOURCE_LANG
113 )select
114 p_CHECK_ID,
115 decode( p_SEQUENCE, G_MISS_NUM, NULL, p_SEQUENCE),
116 decode( p_EXPRESSION, G_MISS_CHAR, NULL, p_EXPRESSION),
117 decode( p_CREATED_BY, G_MISS_NUM, NULL, p_CREATED_BY),
118 decode( p_CREATION_DATE, G_MISS_DATE, NULL, p_CREATION_DATE),
119 decode( p_LAST_UPDATED_BY, G_MISS_NUM, NULL, p_LAST_UPDATED_BY),
120 decode( p_LAST_UPDATE_DATE, G_MISS_DATE, NULL, p_LAST_UPDATE_DATE),
121 decode( p_LAST_UPDATE_LOGIN, G_MISS_NUM, NULL, p_LAST_UPDATE_LOGIN),
122 L.LANGUAGE_CODE,
123 userenv('LANG')
124 from FND_LANGUAGES L
125 where L.INSTALLED_FLAG in ('I', 'B')
126 and not exists
127 ( select NULL
128 from CSC_PROF_CHECK_RULES_TL T
129 where T.CHECK_ID = P_CHECK_ID
130 and t.sequence = p_sequence
131 and T.LANGUAGE = L.LANGUAGE_CODE );
132
133
134 End Insert_Row;
135
136 PROCEDURE Update_Row(
137 p_CHECK_ID NUMBER,
138 p_SEQUENCE NUMBER,
139 p_LAST_UPDATED_BY NUMBER,
140 p_LAST_UPDATE_DATE DATE,
141 p_LAST_UPDATE_LOGIN NUMBER,
142 p_LOGICAL_OPERATOR VARCHAR2,
143 p_LEFT_PAREN VARCHAR2,
144 p_BLOCK_ID NUMBER,
145 p_COMPARISON_OPERATOR VARCHAR2,
146 p_EXPRESSION VARCHAR2,
147 p_EXPR_TO_BLOCK_ID NUMBER,
148 p_RIGHT_PAREN VARCHAR2,
149 p_SEEDED_FLAG VARCHAR2,
150 px_OBJECT_VERSION_NUMBER IN OUT NOCOPY NUMBER)
151
152 IS
153 BEGIN
154 Update CSC_PROF_CHECK_RULES_B
155 SET
156 SEQUENCE = p_SEQUENCE,
157 LAST_UPDATED_BY = p_LAST_UPDATED_BY,
158 LAST_UPDATE_DATE =p_LAST_UPDATE_DATE,
159 LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN,
160 LOGICAL_OPERATOR = p_LOGICAL_OPERATOR,
161 LEFT_PAREN = p_LEFT_PAREN,
162 BLOCK_ID = p_BLOCK_ID,
163 COMPARISON_OPERATOR = p_COMPARISON_OPERATOR,
164 --EXPRESSION1 = decode( p_EXPRESSION1, CSC_CORE_UTILS_PVT.G_MISS_CHAR, EXPRESSION1, p_EXPRESSION1),
165 EXPR_TO_BLOCK_ID = p_EXPR_TO_BLOCK_ID,
166 RIGHT_PAREN = p_RIGHT_PAREN,
167 SEEDED_FLAG = p_SEEDED_FLAG,
168 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1
169 where CHECK_ID = p_CHECK_ID
170 and SEQUENCE = p_SEQUENCE
171 returning OBJECT_VERSION_NUMBER INTO px_OBJECT_VERSION_NUMBER;
172
173 update CSC_PROF_CHECK_RULES_TL set
174 EXPRESSION = p_EXPRESSION,
175 LAST_UPDATED_BY = p_LAST_UPDATED_BY,
176 LAST_UPDATE_DATE = p_LAST_UPDATE_DATE,
177 LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN,
178 SOURCE_LANG = userenv('LANG')
179 where CHECK_ID = P_CHECK_ID
180 and SEQUENCE = p_SEQUENCE
181 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
182
183 If (SQL%NOTFOUND) then
184 RAISE NO_DATA_FOUND;
185 End If;
186
187 END Update_Row;
188
189 procedure LOCK_ROW (
190 P_CHECK_ID in NUMBER,
191 P_SEQUENCE IN NUMBER,
192 P_OBJECT_VERSION_NUMBER in NUMBER
193 ) is
194 cursor c is select
195 SEQUENCE,
196 LOGICAL_OPERATOR,
197 LEFT_PAREN,
198 BLOCK_ID,
199 COMPARISON_OPERATOR,
200 EXPR_TO_BLOCK_ID,
201 RIGHT_PAREN,
202 OBJECT_VERSION_NUMBER
203 from CSC_PROF_CHECK_RULES_B
204 where CHECK_ID = P_CHECK_ID
205 and SEQUENCE = p_SEQUENCE
206 and object_version_number = P_OBJECT_VERSION_NUMBER
207 for update of CHECK_ID nowait;
208 recinfo c%rowtype;
209
210 cursor c1 is select
211 EXPRESSION,
212 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
213 from CSC_PROF_CHECK_RULES_TL
214 where CHECK_ID = P_CHECK_ID
215 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
216 and SEQUENCE = p_SEQUENCE
217 for update of CHECK_ID nowait;
218 begin
219 open c;
220 fetch c into recinfo;
221 if (c%notfound) then
222 close c;
223 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
224 app_exception.raise_exception;
225 end if;
226 close c;
227 return;
228 end LOCK_ROW;
229
230 procedure DELETE_ROW (
231 P_CHECK_ID NUMBER,
232 P_SEQUENCE NUMBER,
233 P_OBJECT_VERSION_NUMBER NUMBER
234 ) is
235 begin
236 delete from CSC_PROF_CHECK_RULES_TL
237 where CHECK_ID = P_CHECK_ID
241 raise no_data_found;
238 and SEQUENCE = p_SEQUENCE;
239
240 if (sql%notfound) then
242 end if;
243
244 delete from CSC_PROF_CHECK_RULES_B
245 where CHECK_ID = P_CHECK_ID
246 and SEQUENCE = P_SEQUENCE
247 and OBJECT_VERSION_NUMBER = P_OBJECT_VERSION_NUMBER;
248
249 if (sql%notfound) then
250 raise no_data_found;
251 end if;
252 end DELETE_ROW;
253
254
255 procedure ADD_LANGUAGE
256 is
257 begin
258 delete from CSC_PROF_CHECK_RULES_TL T
259 where not exists
260 (select NULL
261 from CSC_PROF_CHECK_RULES_B B
262 where B.CHECK_ID = T.CHECK_ID
263 and B.SEQUENCE = T.SEQUENCE
264 );
265
266 update CSC_PROF_CHECK_RULES_TL T set (
267 EXPRESSION
268 ) = (select
269 B.EXPRESSION
270 from CSC_PROF_CHECK_RULES_TL B
271 where B.CHECK_ID = T.CHECK_ID
272 and B.SEQUENCE = T.SEQUENCE
273 and B.LANGUAGE = T.SOURCE_LANG)
274 where (
275 T.CHECK_ID,
276 T.SEQUENCE,
277 T.LANGUAGE
278 ) in (select
279 SUBT.CHECK_ID,
280 SUBT.SEQUENCE,
281 SUBT.LANGUAGE
282 from CSC_PROF_CHECK_RULES_TL SUBB, CSC_PROF_CHECK_RULES_TL SUBT
283 where SUBB.CHECK_ID = SUBT.CHECK_ID
284 and SUBB.SEQUENCE = SUBT.SEQUENCE
285 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
286 and (SUBB.EXPRESSION <> SUBT.EXPRESSION
287 or (SUBB.EXPRESSION is null and SUBT.EXPRESSION is not null)
288 or (SUBB.EXPRESSION is not null and SUBT.EXPRESSION is null)
289 ));
290
291 insert into CSC_PROF_CHECK_RULES_TL (
292 CHECK_ID,
293 SEQUENCE,
294 EXPRESSION,
295 CREATED_BY,
296 CREATION_DATE,
297 LAST_UPDATED_BY,
298 LAST_UPDATE_DATE,
299 LAST_UPDATE_LOGIN,
300 LANGUAGE,
301 SOURCE_LANG
302 ) select
303 B.CHECK_ID,
304 B.SEQUENCE,
305 B.EXPRESSION,
306 B.CREATED_BY,
307 B.CREATION_DATE,
308 B.LAST_UPDATED_BY,
309 B.LAST_UPDATE_DATE,
310 B.LAST_UPDATE_LOGIN,
311 L.LANGUAGE_CODE,
312 B.SOURCE_LANG
313 from CSC_PROF_CHECK_RULES_TL B, FND_LANGUAGES L
314 where L.INSTALLED_FLAG in ('I', 'B')
315 and B.LANGUAGE = userenv('LANG')
316 and not exists
317 (select NULL
318 from CSC_PROF_CHECK_RULES_TL T
319 where T.CHECK_ID = B.CHECK_ID
320 and T.SEQUENCE = B.SEQUENCE
321 and T.LANGUAGE = L.LANGUAGE_CODE);
322 end ADD_LANGUAGE;
323
324 -- Fix for bug #1491205
325
326 procedure TRANSLATE_ROW (
327 p_check_id IN NUMBER,
328 p_sequence IN NUMBER,
329 p_expression IN VARCHAR2,
330 p_owner IN VARCHAR2)
331 IS
332 BEGIN
333 UPDATE csc_prof_check_rules_tl
334 SET expression = NVL(p_expression,expression),
335 last_update_date = sysdate,
336 last_updated_by = fnd_load_util.owner_id(p_owner), /* removed the decode logic for R12 mandate DECODE(p_owner, 'SEED', 1, 0), */
337 last_update_login = 0,
338 source_lang = userenv('LANG')
339 WHERE check_id = p_check_id
340 AND sequence = p_sequence
341 AND userenv('LANG') IN (language, source_lang) ;
342 end TRANSLATE_ROW ;
343
344
345 PROCEDURE LOAD_ROW (
346 p_CHECK_ID IN NUMBER,
347 p_SEQUENCE IN NUMBER,
348 p_LAST_UPDATED_BY IN NUMBER,
349 p_LAST_UPDATE_DATE IN DATE,
350 p_LAST_UPDATE_LOGIN IN NUMBER,
351 p_LOGICAL_OPERATOR IN VARCHAR2,
352 p_LEFT_PAREN IN VARCHAR2,
353 p_BLOCK_ID IN NUMBER,
354 p_COMPARISON_OPERATOR IN VARCHAR2,
355 p_EXPRESSION IN VARCHAR2,
356 p_EXPR_TO_BLOCK_ID IN NUMBER,
357 p_RIGHT_PAREN IN VARCHAR2,
358 p_SEEDED_FLAG IN VARCHAR2,
359 px_OBJECT_VERSION_NUMBER IN OUT NOCOPY NUMBER,
360 p_OWNER IN VARCHAR2)
361 IS
362 l_user_id NUMBER := 0;
363 l_object_version_number NUMBER := 0;
364 l_check_id NUMBER := p_check_id ;
365 l_sequence NUMBER := p_sequence ;
366
367 BEGIN
368
369 Csc_Profile_Check_Rules_Pkg.Update_Row(
370 p_CHECK_ID => p_check_id,
371 p_SEQUENCE => p_sequence,
372 p_LAST_UPDATED_BY => p_last_updated_by,
373 p_LAST_UPDATE_DATE => p_last_update_date,
374 p_LAST_UPDATE_LOGIN => 0,
375 p_LOGICAL_OPERATOR => p_logical_operator,
376 p_LEFT_PAREN => p_left_paren,
377 p_BLOCK_ID => p_block_id,
378 p_COMPARISON_OPERATOR => p_comparison_operator,
379 p_EXPRESSION => p_expression,
380 p_EXPR_TO_BLOCK_ID => p_expr_to_block_id,
381 p_RIGHT_PAREN => p_right_paren,
382 p_SEEDED_FLAG => p_seeded_flag,
383 px_OBJECT_VERSION_NUMBER => l_object_version_number );
384
385 exception when no_data_found then
386
387 Csc_Profile_Check_Rules_Pkg.Insert_Row(
388 p_CHECK_ID => l_check_id,
389 p_SEQUENCE => l_sequence,
390 p_CREATED_BY => p_last_updated_by,
391 p_CREATION_DATE => p_last_update_date,
392 p_LAST_UPDATED_BY => p_last_updated_by,
396 p_LEFT_PAREN => p_left_paren,
393 p_LAST_UPDATE_DATE => p_last_update_date,
394 p_LAST_UPDATE_LOGIN => 0,
395 p_LOGICAL_OPERATOR => p_logical_operator,
397 p_BLOCK_ID => p_block_id,
398 p_COMPARISON_OPERATOR => p_comparison_operator,
399 p_EXPRESSION => p_expression,
400 p_EXPR_TO_BLOCK_ID => p_expr_to_block_id,
401 p_RIGHT_PAREN => p_right_paren,
402 p_SEEDED_FLAG => p_seeded_flag,
403 X_OBJECT_VERSION_NUMBER => px_object_version_number);
404 END LOAD_ROW;
405
406 -- End of fix for bug #1491205
407
408 End CSC_PROFILE_CHECK_RULES_PKG;