eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.CSC_PROF_CHECKS_PKG

Source


1 PACKAGE BODY CSC_PROF_CHECKS_PKG as
2 /* $Header: csctpckb.pls 120.3 2005/09/18 23:14:52 vshastry ship $ */
3 -- Start of Comments
4 -- Package name     : CSC_PROF_CHECKS_PKG
5 -- Purpose          :
6 -- History          :
7 --	03 Nov 00   axsubram  Added  Translate_row,load_row (# 1487864)
8 --	03 Nov 00	axsubram	File name constant corrected to csctpckb.pls
9 -- 07 Nov 02   jamose Upgrade table handler changes
10 -- 25 Nov 02   jamose Fnd_Api_G_MISS* changes to improve the performance
11 -- 19-07-2005 tpalaniv Modified the translate_row and load_row APIs to fetch last_updated_by using FND API
12 -- 19-09-2005 vshastry Bug 4596220. Added condition in insert row
13 -- NOTE             :
14 -- End of Comments
15 
16 
17 G_PKG_NAME CONSTANT VARCHAR2(30)  := 'CSC_PROF_CHECKS_PKG';
18 G_FILE_NAME CONSTANT VARCHAR2(12) := 'csctpckb.pls';
19 
20 G_MISS_CHAR VARCHAR2(1) := FND_API.G_MISS_CHAR;
21 G_MISS_NUM NUMBER := FND_API.G_MISS_NUM;
22 G_MISS_DATE DATE := FND_API.G_MISS_DATE;
23 
24 PROCEDURE Insert_Row(
25           px_CHECK_ID   IN OUT NOCOPY NUMBER,
26           p_CHECK_NAME    VARCHAR2,
27           p_CHECK_NAME_CODE    VARCHAR2,
28           p_DESCRIPTION    VARCHAR2,
29           p_START_DATE_ACTIVE    DATE,
30           p_END_DATE_ACTIVE    DATE,
31           p_SEEDED_FLAG    VARCHAR2,
32           p_SELECT_TYPE    VARCHAR2,
33           p_SELECT_BLOCK_ID    NUMBER,
34           p_DATA_TYPE    VARCHAR2,
35           p_FORMAT_MASK    VARCHAR2,
36           p_THRESHOLD_GRADE    VARCHAR2,
37           p_THRESHOLD_RATING_CODE    VARCHAR2,
38           p_CHECK_UPPER_LOWER_FLAG    VARCHAR2,
39           p_THRESHOLD_COLOR_CODE    VARCHAR2,
40           p_CHECK_LEVEL             VARCHAR2,
41           p_CREATED_BY    NUMBER,
42           p_CREATION_DATE    DATE,
43           p_LAST_UPDATED_BY    NUMBER,
44           p_LAST_UPDATE_DATE    DATE,
45           p_LAST_UPDATE_LOGIN    NUMBER,
46 	    x_OBJECT_VERSION_NUMBER OUT NOCOPY NUMBER,
47           p_APPLICATION_ID     NUMBER
48 	)
49  IS
50    CURSOR C2 IS SELECT CSC_PROF_CHECKS_S.nextval FROM sys.dual;
51 l_object_version_number number := 1;
52 ps_SEEDED_FLAG    Varchar2(3);
53 BEGIN
54 
55    /* added the below 2 lines for bug 4596220 */
56    ps_seeded_flag := p_seeded_flag;
57    IF NVL(p_seeded_flag, 'N') <> 'Y' THEN
58 
59    /* Added This If Condition for Bug 1944040 */
60       If p_Created_by=1 then
61            ps_seeded_flag:='Y';
62       Else
63            ps_seeded_flag:='N';
64       End If;
65    END IF;
66 
67    If (px_CHECK_ID IS NULL) OR (px_CHECK_ID = G_MISS_NUM) then
68        OPEN C2;
69        FETCH C2 INTO px_CHECK_ID;
70        CLOSE C2;
71    End If;
72 
73   -- to_date(NULL) added to include timestamp during creation
74    INSERT INTO CSC_PROF_CHECKS_b(
75            CHECK_ID,
76            CHECK_NAME_CODE,
77            START_DATE_ACTIVE,
78            END_DATE_ACTIVE,
79            SEEDED_FLAG,
80            SELECT_TYPE,
81            SELECT_BLOCK_ID,
82            DATA_TYPE,
83            FORMAT_MASK,
84            THRESHOLD_GRADE,
85            THRESHOLD_RATING_CODE,
86            CHECK_UPPER_LOWER_FLAG,
87            THRESHOLD_COLOR_CODE,
88            CHECK_LEVEL,
89            CREATED_BY,
90            CREATION_DATE,
91            LAST_UPDATED_BY,
92            LAST_UPDATE_DATE,
93            LAST_UPDATE_LOGIN,
94 	   OBJECT_VERSION_NUMBER,
95            APPLICATION_ID
96           ) VALUES (
97            px_CHECK_ID,
98            decode( p_CHECK_NAME_CODE, G_MISS_CHAR, NULL, p_CHECK_NAME_CODE),
99            decode( p_START_DATE_ACTIVE,G_MISS_DATE, to_date(NULL), p_START_DATE_ACTIVE),
100            decode( p_END_DATE_ACTIVE, G_MISS_DATE,to_date(NULL), p_END_DATE_ACTIVE),
101            decode( p_SEEDED_FLAG, G_MISS_CHAR, NULL, ps_SEEDED_FLAG),
102            decode( p_SELECT_TYPE, G_MISS_CHAR, NULL, p_SELECT_TYPE),
103            decode( p_SELECT_BLOCK_ID, G_MISS_NUM, NULL, p_SELECT_BLOCK_ID),
104            decode( p_DATA_TYPE, G_MISS_CHAR, NULL, p_DATA_TYPE),
105            decode( p_FORMAT_MASK, G_MISS_CHAR, NULL, p_FORMAT_MASK),
106            decode( p_THRESHOLD_GRADE, G_MISS_CHAR, NULL, p_THRESHOLD_GRADE),
107            decode( p_THRESHOLD_RATING_CODE, G_MISS_CHAR, NULL, p_THRESHOLD_RATING_CODE),
108            decode( p_CHECK_UPPER_LOWER_FLAG, G_MISS_CHAR, NULL, p_CHECK_UPPER_LOWER_FLAG),
109            decode( p_THRESHOLD_COLOR_CODE, G_MISS_CHAR, NULL, p_THRESHOLD_COLOR_CODE),
110            decode( p_CHECK_LEVEL, G_MISS_CHAR, NULL, p_CHECK_LEVEL),
111            decode( p_CREATED_BY, G_MISS_NUM, NULL, p_CREATED_BY),
112            decode( p_CREATION_DATE, G_MISS_DATE,to_date(NULL), p_CREATION_DATE),
113            decode( p_LAST_UPDATED_BY, G_MISS_NUM, NULL, p_LAST_UPDATED_BY),
114            decode( p_LAST_UPDATE_DATE, G_MISS_DATE,to_date(NULL), p_LAST_UPDATE_DATE),
115            decode( p_LAST_UPDATE_LOGIN, G_MISS_NUM, NULL, p_LAST_UPDATE_LOGIN),
116 	     l_OBJECT_VERSION_NUMBER,
117            decode( p_APPLICATION_ID,G_MISS_NUM, NULL, p_APPLICATION_ID) );
118 
119 
120    INSERT INTO CSC_PROF_CHECKS_TL(
121 	   CHECK_ID,
122            CHECK_NAME,
123            DESCRIPTION,
124            CREATED_BY,
125            CREATION_DATE,
126            LAST_UPDATED_BY,
127            LAST_UPDATE_DATE,
128            LAST_UPDATE_LOGIN,
129            LANGUAGE,
130            SOURCE_LANG
131           ) select
132            Px_CHECK_ID,
133            decode( p_CHECK_NAME, G_MISS_CHAR, NULL, p_CHECK_NAME),
134            decode( p_DESCRIPTION,G_MISS_CHAR, NULL, p_DESCRIPTION),
135            decode( p_CREATED_BY, G_MISS_NUM, NULL, p_CREATED_BY),
136            decode( p_CREATION_DATE, G_MISS_DATE,to_date(NULL), p_CREATION_DATE),
137            decode( p_LAST_UPDATED_BY, G_MISS_NUM, NULL, p_LAST_UPDATED_BY),
138            decode( p_LAST_UPDATE_DATE, G_MISS_DATE,to_date(NULL), p_LAST_UPDATE_DATE),
139            decode( p_LAST_UPDATE_LOGIN, G_MISS_NUM, NULL, p_LAST_UPDATE_LOGIN),
140            L.LANGUAGE_CODE,
141            userenv('LANG')
142      FROM FND_LANGUAGES L
143     WHERE L.INSTALLED_FLAG in ('I', 'B')
144     AND not exists
145       (select NULL
146        from CSC_PROF_CHECKS_TL T
147        where T.CHECK_ID = Px_CHECK_ID
148        and T.LANGUAGE = L.LANGUAGE_CODE);
149 
150     --set out parameters
151     x_object_version_number := l_object_version_number;
152 End Insert_Row;
153 
154 PROCEDURE Update_Row(
155           p_CHECK_ID    NUMBER,
156           p_CHECK_NAME    VARCHAR2,
157           p_CHECK_NAME_CODE    VARCHAR2,
158           p_DESCRIPTION    VARCHAR2,
159           p_START_DATE_ACTIVE    DATE,
160           p_END_DATE_ACTIVE    DATE,
161           p_SEEDED_FLAG    VARCHAR2,
162           p_SELECT_TYPE    VARCHAR2,
163           p_SELECT_BLOCK_ID    NUMBER,
164           p_DATA_TYPE    VARCHAR2,
165           p_FORMAT_MASK    VARCHAR2,
166           p_THRESHOLD_GRADE    VARCHAR2,
167           p_THRESHOLD_RATING_CODE    VARCHAR2,
168           p_CHECK_UPPER_LOWER_FLAG    VARCHAR2,
169           p_THRESHOLD_COLOR_CODE    VARCHAR2,
170           p_CHECK_LEVEL             VARCHAR2,
171           p_LAST_UPDATED_BY    NUMBER,
172           p_LAST_UPDATE_DATE    DATE,
173           p_LAST_UPDATE_LOGIN    NUMBER,
174 	       px_OBJECT_VERSION_NUMBER IN OUT NOCOPY NUMBER,
175           p_APPLICATION_ID  NUMBER)
176 
177  IS
178  BEGIN
179     Update CSC_PROF_CHECKS_B
180     SET
181        CHECK_NAME_CODE = p_CHECK_NAME_CODE,
182        START_DATE_ACTIVE = p_START_DATE_ACTIVE,
183        END_DATE_ACTIVE = p_END_DATE_ACTIVE,
184        SEEDED_FLAG = p_SEEDED_FLAG,
185        SELECT_TYPE = p_SELECT_TYPE,
186        SELECT_BLOCK_ID = p_SELECT_BLOCK_ID,
187        DATA_TYPE = p_DATA_TYPE,
188        FORMAT_MASK = p_FORMAT_MASK,
189        THRESHOLD_GRADE = p_THRESHOLD_GRADE,
190        THRESHOLD_RATING_CODE = p_THRESHOLD_RATING_CODE,
191        CHECK_UPPER_LOWER_FLAG = p_CHECK_UPPER_LOWER_FLAG,
192        THRESHOLD_COLOR_CODE = p_THRESHOLD_COLOR_CODE,
193        CHECK_LEVEL = p_CHECK_LEVEL,
194        LAST_UPDATED_BY = p_LAST_UPDATED_BY,
195        LAST_UPDATE_DATE = p_LAST_UPDATE_DATE,
196        LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN,
197 	    OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
198        APPLICATION_ID= p_APPLICATION_ID
199     WHERE CHECK_ID = p_CHECK_ID
200     RETURNING OBJECT_VERSION_NUMBER INTO px_object_version_number;
201 
202     UPDATE CSC_PROF_CHECKS_TL SET
203         CHECK_NAME =  p_CHECK_NAME,
204         DESCRIPTION = p_DESCRIPTION,
205         LAST_UPDATED_BY = p_LAST_UPDATED_BY,
206         LAST_UPDATE_DATE = p_LAST_UPDATE_DATE,
207         LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN,
208     	  SOURCE_LANG = userenv('LANG')
209     WHERE CHECK_ID = P_CHECK_ID
210     AND userenv('LANG') in (LANGUAGE, SOURCE_LANG);
211     If (SQL%NOTFOUND) then
212         RAISE NO_DATA_FOUND;
213     End If;
214 END Update_Row;
215 
216 
217 procedure LOCK_ROW (
218   P_CHECK_ID  NUMBER,
219   P_OBJECT_VERSION_NUMBER NUMBER
220 ) is
221   cursor c is select
222       OBJECT_VERSION_NUMBER,
223       CHECK_NAME_CODE,
224       START_DATE_ACTIVE,
225       END_DATE_ACTIVE,
226       SEEDED_FLAG,
227       SELECT_TYPE,
228       SELECT_BLOCK_ID,
229       DATA_TYPE,
230       FORMAT_MASK,
231       THRESHOLD_GRADE,
232       THRESHOLD_RATING_CODE,
233       THRESHOLD_COLOR_CODE,
234       CHECK_LEVEL,
235       CHECK_UPPER_LOWER_FLAG
236     from CSC_PROF_CHECKS_B
237     where CHECK_ID = P_CHECK_ID
238     and object_version_number = P_OBJECT_VERSION_NUMBER
239     for update of CHECK_ID nowait;
240   recinfo c%rowtype;
241 
242   cursor c1 is select
243       CHECK_NAME,
244       decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
245     from CSC_PROF_CHECKS_TL
246     where CHECK_ID = P_CHECK_ID
247     and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
248     for update of CHECK_ID nowait;
249 begin
250   open c;
251   fetch c into recinfo;
252   if (c%notfound) then
253     close c;
254     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
255     app_exception.raise_exception;
256   end if;
257   close c;
258   return;
259 end LOCK_ROW;
260 
261 procedure DELETE_ROW (
262   P_CHECK_ID  NUMBER,
263   P_OBJECT_VERSION_NUMBER NUMBER
264 ) is
265 begin
266   delete from CSC_PROF_CHECKS_TL
267   where CHECK_ID = P_CHECK_ID;
268 
269   if (sql%notfound) then
270     raise no_data_found;
271   end if;
272 
273   delete from CSC_PROF_CHECKS_B
274   where CHECK_ID = P_CHECK_ID
275   and OBJECT_VERSION_NUMBER = P_OBJECT_VERSION_NUMBER;
276 
277   if (sql%notfound) then
278     raise no_data_found;
279   end if;
280 end DELETE_ROW;
281 
282 procedure ADD_LANGUAGE
283 is
284 begin
285   delete from CSC_PROF_CHECKS_TL T
286   where not exists
287     (select NULL
288     from CSC_PROF_CHECKS_B B
289     where B.CHECK_ID = T.CHECK_ID
290     );
291 
292   update CSC_PROF_CHECKS_TL T set (
293       CHECK_NAME,
294       DESCRIPTION
295     ) = (select
296       B.CHECK_NAME,
297       B.DESCRIPTION
298     from CSC_PROF_CHECKS_TL B
299     where B.CHECK_ID = T.CHECK_ID
300     and B.LANGUAGE = T.SOURCE_LANG)
301   where (
302       T.CHECK_ID,
303       T.LANGUAGE
304   ) in (select
305       SUBT.CHECK_ID,
306       SUBT.LANGUAGE
307     from CSC_PROF_CHECKS_TL SUBB, CSC_PROF_CHECKS_TL SUBT
308     where SUBB.CHECK_ID = SUBT.CHECK_ID
309     and SUBB.LANGUAGE = SUBT.SOURCE_LANG
310     and (SUBB.CHECK_NAME <> SUBT.CHECK_NAME
311       or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
312       or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
313       or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
314   ));
315 
316   insert into CSC_PROF_CHECKS_TL (
317     CHECK_ID,
318     CHECK_NAME,
319     DESCRIPTION,
320     CREATED_BY,
321     CREATION_DATE,
322     LAST_UPDATED_BY,
323     LAST_UPDATE_DATE,
324     LAST_UPDATE_LOGIN,
325     LANGUAGE,
326     SOURCE_LANG
327   ) select
328     B.CHECK_ID,
329     B.CHECK_NAME,
330     B.DESCRIPTION,
331     B.CREATED_BY,
332     B.CREATION_DATE,
333     B.LAST_UPDATED_BY,
334     B.LAST_UPDATE_DATE,
335     B.LAST_UPDATE_LOGIN,
336     L.LANGUAGE_CODE,
337     B.SOURCE_LANG
338   from CSC_PROF_CHECKS_TL B, FND_LANGUAGES L
339   where L.INSTALLED_FLAG in ('I', 'B')
340   and B.LANGUAGE = userenv('LANG')
341   and not exists
342     (select NULL
343     from CSC_PROF_CHECKS_TL T
344     where T.CHECK_ID = B.CHECK_ID
345     and T.LANGUAGE = L.LANGUAGE_CODE);
346 end ADD_LANGUAGE;
347 
348 procedure TRANSLATE_ROW (
349   P_CHECK_ID        NUMBER,
350   p_CHECK_NAME      VARCHAR2,
351   p_DESCRIPTION    	VARCHAR2,
352   p_owner 		varchar2)
353   IS
354   BEGIN
355 	Update Csc_Prof_Checks_TL set
356 	     check_name        = p_check_name,
357 	     description       = nvl(p_description,description),
358 	     last_update_date  = sysdate,
359 	     last_updated_by   = fnd_load_util.owner_id(p_owner),
360 	     last_update_login = 0,
361 	     source_lang       = userenv('LANG')
362 	     Where check_id    = p_check_id
363 	    and  userenv('LANG') in (language, source_lang);
364 
365 end TRANSLATE_ROW;
366 
367 PROCEDURE Load_Row(
368           p_CHECK_ID    		NUMBER,
369           p_CHECK_NAME    	VARCHAR2,
370           p_CHECK_NAME_CODE   VARCHAR2,
371           p_DESCRIPTION    	VARCHAR2,
372           p_START_DATE_ACTIVE DATE,
373           p_END_DATE_ACTIVE   DATE,
374           p_SEEDED_FLAG    	VARCHAR2,
375           p_SELECT_TYPE    	VARCHAR2,
376           p_SELECT_BLOCK_ID   NUMBER,
377           p_DATA_TYPE    	VARCHAR2,
378           p_FORMAT_MASK    	VARCHAR2,
379           p_THRESHOLD_GRADE   VARCHAR2,
380           p_THRESHOLD_RATING_CODE    VARCHAR2,
381           p_CHECK_UPPER_LOWER_FLAG   VARCHAR2,
382           p_THRESHOLD_COLOR_CODE     VARCHAR2,
383           p_CHECK_LEVEL              VARCHAR2,
384           p_LAST_UPDATED_BY     NUMBER,
385           p_LAST_UPDATE_DATE    DATE,
386           p_LAST_UPDATE_LOGIN   NUMBER,
387 	  px_OBJECT_VERSION_NUMBER IN OUT NOCOPY NUMBER ,
388           p_APPLICATION_ID      NUMBER,
389        	  P_OWNER	VARCHAR2)
390 IS
391 	     l_user_id	number := 0;
392 	     l_check_id	number := G_MISS_NUM;
393 	     l_object_version_number	number := 0;
394 
395 		/** This is mainly for loading seed data . That is the
396 		reason, that l_check_id is being declared here, The check_id
397 		returned from insert_row is not used.
398 
399 		2. Object_version_number is not passed . It is assumed that
400 			seed data would be run when other users are not using
401 			the system
402 		**/
403     BEGIN
404 		/*if (p_owner = 'SEED') then
405 			l_user_id := 1;
406 		end if; */
407 
408         l_check_id := p_check_id;
409 
410         Csc_Prof_Checks_Pkg.Update_Row(
411  			p_CHECK_ID    		     => p_check_id,
412  			p_CHECK_NAME    	     => p_check_name,
413  			p_CHECK_NAME_CODE  	     => p_check_name_code,
414  			p_DESCRIPTION    	     => p_description,
415  			p_START_DATE_ACTIVE  => to_date(p_start_date_active,'YYYY/MM/DD'),
416  			p_END_DATE_ACTIVE    => to_date(p_end_date_active,'YYYY/MM/DD'),
417  			p_SEEDED_FLAG    	     => 'Y',
418  			p_SELECT_TYPE    	     => p_select_type,
419  			p_SELECT_BLOCK_ID        => p_select_block_id,
420  			p_DATA_TYPE    	     => p_data_type,
421  			p_FORMAT_MASK    	     => p_format_mask,
422  			p_THRESHOLD_GRADE        => p_threshold_grade,
423  			p_THRESHOLD_RATING_CODE  => p_threshold_rating_code,
424  			p_CHECK_UPPER_LOWER_FLAG => p_check_upper_lower_flag,
425  			p_THRESHOLD_COLOR_CODE   => p_threshold_color_code,
426                         p_CHECK_LEVEL            => p_check_level,
427  			p_LAST_UPDATED_BY    	=> p_LAST_UPDATED_BY,
428  			p_LAST_UPDATE_DATE    	=> p_LAST_UPDATE_DATE,
429  			p_LAST_UPDATE_LOGIN    	=> 0,
430  		        px_OBJECT_VERSION_NUMBER => l_object_version_number,
431                         p_APPLICATION_ID          => p_application_id );
432 
433 
434    EXCEPTION
435       WHEN NO_DATA_FOUND THEN
436 
437 	    csc_prof_checks_pkg.insert_row(
438  		px_CHECK_ID   	          => l_check_id ,
439  		p_CHECK_NAME             => p_check_name,
440  		p_CHECK_NAME_CODE        => p_check_name_code,
441  		p_DESCRIPTION            => p_description,
442  		p_START_DATE_ACTIVE  => to_date(p_start_date_active,'YYYY/MM/DD'),
443  		p_END_DATE_ACTIVE    => to_date(p_end_date_active,'YYYY/MM/DD'),
444  		p_SEEDED_FLAG            => 'Y',
445  		p_SELECT_TYPE            => p_select_type,
446  		p_SELECT_BLOCK_ID        => p_select_block_id,
447  		p_DATA_TYPE    	     => p_data_type,
448  		p_FORMAT_MASK    	     => p_format_mask,
449  		p_THRESHOLD_GRADE        => p_threshold_grade,
450  		p_THRESHOLD_RATING_CODE  => p_threshold_rating_code,
451  		p_CHECK_UPPER_LOWER_FLAG => p_check_upper_lower_flag,
452  		p_THRESHOLD_COLOR_CODE   => p_threshold_color_code,
453                 p_CHECK_LEVEL            => p_check_level,
454  		p_CREATED_BY             => p_LAST_UPDATED_BY,
455  		p_CREATION_DATE          => p_LAST_UPDATE_DATE,
456  		p_LAST_UPDATED_BY        => p_LAST_UPDATED_BY,
457  		p_LAST_UPDATE_DATE       => p_LAST_UPDATE_DATE,
458  		p_LAST_UPDATE_LOGIN      => 0,
459  		x_OBJECT_VERSION_NUMBER  => l_object_version_number,
460                 p_APPLICATION_ID          => p_application_id );
461 
462 End Load_ROW;
463 
464 End CSC_PROF_CHECKS_PKG;