[Home] [Help]
PACKAGE BODY: APPS.IEX_SCORE_COMP_TYPES_PKG
Source
1 PACKAGE BODY IEX_SCORE_COMP_TYPES_PKG AS
2 /* $Header: iextsctb.pls 120.3 2004/11/05 21:35:06 jypark ship $ */
3 PG_DEBUG NUMBER(2) ;
4
5 PROCEDURE INSERT_ROW (
6 X_ROWID IN OUT NOCOPY VARCHAR2,
7 P_SCORE_COMP_TYPE_ID IN NUMBER,
8 P_OBJECT_VERSION_NUMBER IN NUMBER,
9 P_SCORE_COMP_VALUE IN VARCHAR2,
10 P_ACTIVE_FLAG IN VARCHAR2,
11 P_SCORE_COMP_NAME IN VARCHAR2,
12 P_DESCRIPTION IN VARCHAR2,
13 P_CREATION_DATE IN DATE,
14 P_CREATED_BY IN NUMBER,
15 P_LAST_UPDATE_DATE IN DATE,
16 P_LAST_UPDATED_BY IN NUMBER,
17 P_LAST_UPDATE_LOGIN IN NUMBER,
18 P_JTF_OBJECT_CODE IN VARCHAR2,
19 P_FUNCTION_FLAG IN VARCHAR2,
20 P_METRIC_FLAG IN VARCHAR2,
21 P_DISPLAY_ORDER IN NUMBER)
22 IS
23 CURSOR C IS
24 SELECT ROWID
25 FROM IEX_SCORE_COMP_TYPES_B
26 WHERE SCORE_COMP_TYPE_ID = P_SCORE_COMP_TYPE_ID;
27 l_function_flag VARCHAR2(1);
28 BEGIN
29
30 if p_function_Flag is null then
31 l_function_flag := 'N';
32 else
33 l_function_flag := p_function_flag;
34 end if;
35
36
37 INSERT INTO IEX_SCORE_COMP_TYPES_B (
38 SCORE_COMP_TYPE_ID,
39 OBJECT_VERSION_NUMBER,
40 SCORE_COMP_VALUE,
41 ACTIVE_FLAG,
42 CREATION_DATE,
43 CREATED_BY,
44 LAST_UPDATE_DATE,
45 LAST_UPDATED_BY,
46 LAST_UPDATE_LOGIN,
47 JTF_OBJECT_CODE,
48 FUNCTION_FLAG,
49 METRIC_FLAG,
50 DISPLAY_ORDER)
51 VALUES (
52 P_SCORE_COMP_TYPE_ID,
53 P_OBJECT_VERSION_NUMBER,
54 P_SCORE_COMP_VALUE,
55 P_ACTIVE_FLAG,
56 P_CREATION_DATE,
57 P_CREATED_BY,
58 P_LAST_UPDATE_DATE,
59 P_LAST_UPDATED_BY,
60 P_LAST_UPDATE_LOGIN,
61 P_JTF_OBJECT_CODE,
62 l_function_flag,
63 p_metric_flag,
64 p_display_order);
65
66 INSERT INTO IEX_SCORE_COMP_TYPES_TL (
67 SCORE_COMP_TYPE_ID,
68 OBJECT_VERSION_NUMBER,
69 LAST_UPDATE_DATE,
70 LAST_UPDATED_BY,
71 LAST_UPDATE_LOGIN,
72 CREATION_DATE,
73 CREATED_BY,
74 SCORE_COMP_NAME,
75 DESCRIPTION,
76 LANGUAGE,
77 SOURCE_LANG)
78 SELECT
79 P_SCORE_COMP_TYPE_ID,
80 P_OBJECT_VERSION_NUMBER,
81 P_LAST_UPDATE_DATE,
82 P_LAST_UPDATED_BY,
83 P_LAST_UPDATE_LOGIN,
84 P_CREATION_DATE,
85 P_CREATED_BY,
86 P_SCORE_COMP_NAME,
87 P_DESCRIPTION,
88 L.LANGUAGE_CODE,
89 userenv('LANG')
90 FROM FND_LANGUAGES L
91 WHERE L.INSTALLED_FLAG IN ('I', 'B')
92 AND NOT EXISTS
93 (SELECT NULL
94 FROM IEX_SCORE_COMP_TYPES_TL T
95 WHERE T.SCORE_COMP_TYPE_ID = P_SCORE_COMP_TYPE_ID
96 AND T.LANGUAGE = L.LANGUAGE_CODE);
97
98 OPEN c;
99 FETCH c INTO X_ROWID;
100 IF (c%NOTFOUND) THEN
101 CLOSE c;
102 RAISE NO_DATA_FOUND;
103 END IF;
104 CLOSE c;
105
106 END INSERT_ROW;
107
108 PROCEDURE LOCK_ROW (
109 P_SCORE_COMP_TYPE_ID IN NUMBER,
110 P_OBJECT_VERSION_NUMBER IN NUMBER,
111 P_SCORE_COMP_VALUE IN VARCHAR2,
112 P_ACTIVE_FLAG IN VARCHAR2,
113 P_SCORE_COMP_NAME IN VARCHAR2) IS
114
115 CURSOR c IS SELECT
116 OBJECT_VERSION_NUMBER,
117 SCORE_COMP_VALUE,
118 ACTIVE_FLAG
119 FROM IEX_SCORE_COMP_TYPES_B
120 WHERE SCORE_COMP_TYPE_ID = P_SCORE_COMP_TYPE_ID
121 FOR UPDATE of SCORE_COMP_TYPE_ID NOWAIT;
122 recinfo c%rowtype;
123
124 CURSOR c1 IS SELECT
125 SCORE_COMP_NAME,
126 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
127 FROM IEX_SCORE_COMP_TYPES_TL
128 WHERE SCORE_COMP_TYPE_ID = P_SCORE_COMP_TYPE_ID
129 AND userenv('LANG') IN (LANGUAGE, SOURCE_LANG)
130 FOR UPDATE of SCORE_COMP_TYPE_ID nowait;
131
132 BEGIN
133 OPEN c;
134 FETCH c INTO recinfo;
135 IF (c%notfound) THEN
136 CLOSE c;
137 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
138 app_exception.raise_exception;
139 END IF;
140 CLOSE c;
141 IF ( (recinfo.OBJECT_VERSION_NUMBER = P_OBJECT_VERSION_NUMBER)
142 AND ((recinfo.SCORE_COMP_VALUE = P_SCORE_COMP_VALUE)
143 OR ((recinfo.SCORE_COMP_VALUE IS NULL) AND (P_SCORE_COMP_VALUE IS NULL)))
144 AND ((recinfo.ACTIVE_FLAG = P_ACTIVE_FLAG)
145 OR ((recinfo.ACTIVE_FLAG IS NULL) AND (P_ACTIVE_FLAG IS NULL)))
146 ) THEN
147 NULL;
148 ELSE
149 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
150 app_exception.raise_exception;
151 END IF;
152
153 FOR tlinfo IN c1 LOOP
154 IF (tlinfo.BASELANG = 'Y') THEN
155 IF ( ((tlinfo.SCORE_COMP_NAME = P_SCORE_COMP_NAME)
156 OR ((tlinfo.SCORE_COMP_NAME IS NULL) AND (P_SCORE_COMP_NAME IS NULL)))
157 ) THEN
158 NULL;
159 ELSE
160 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
161 app_exception.raise_exception;
162 END IF;
163 END IF;
164 END LOOP;
165 RETURN;
166 END LOCK_ROW;
167
168 PROCEDURE UPDATE_ROW (
169 P_SCORE_COMP_TYPE_ID IN NUMBER,
170 P_OBJECT_VERSION_NUMBER IN NUMBER,
171 P_SCORE_COMP_VALUE IN VARCHAR2,
172 P_ACTIVE_FLAG IN VARCHAR2,
173 P_SCORE_COMP_NAME IN VARCHAR2,
174 P_LAST_UPDATE_DATE IN DATE,
175 P_LAST_UPDATED_BY IN NUMBER,
176 P_LAST_UPDATE_LOGIN IN NUMBER,
177 P_DESCRIPTION IN VARCHAR2,
178 P_JTF_OBJECT_CODE IN VARCHAR2,
179 P_FUNCTION_FLAG IN VARCHAR2,
180 P_METRIC_FLAG IN VARCHAR2,
181 P_DISPLAY_ORDER IN NUMBER) IS
182 l_function_flag VARCHAR2(1);
183 BEGIN
184
185 if p_function_Flag is null then
186 l_function_flag := 'N';
187 else
188 l_function_flag := p_function_flag;
189 end if;
190
191 UPDATE IEX_SCORE_COMP_TYPES_B SET
192 OBJECT_VERSION_NUMBER = P_OBJECT_VERSION_NUMBER,
193 SCORE_COMP_VALUE = P_SCORE_COMP_VALUE,
194 ACTIVE_FLAG = P_ACTIVE_FLAG,
195 LAST_UPDATE_DATE = P_LAST_UPDATE_DATE,
196 LAST_UPDATED_BY = P_LAST_UPDATED_BY,
197 LAST_UPDATE_LOGIN = P_LAST_UPDATE_LOGIN,
198 JTF_OBJECT_CODE = P_JTF_OBJECT_CODE,
199 FUNCTION_FLAG = l_FUNCTION_FLAG,
200 METRIC_FLAG = P_METRIC_FLAG,
201 DISPLAY_ORDER = P_DISPLAY_ORDER
202 WHERE SCORE_COMP_TYPE_ID = P_SCORE_COMP_TYPE_ID;
203
204 IF (sql%notfound) THEN
205 RAISE no_data_found;
206 END IF;
207
208 UPDATE IEX_SCORE_COMP_TYPES_TL SET
209 SCORE_COMP_NAME = P_SCORE_COMP_NAME,
210 DESCRIPTION = P_DESCRIPTION,
211 LAST_UPDATE_DATE = P_LAST_UPDATE_DATE,
212 LAST_UPDATED_BY = P_LAST_UPDATED_BY,
213 LAST_UPDATE_LOGIN = P_LAST_UPDATE_LOGIN,
214 SOURCE_LANG = userenv('LANG')
215 WHERE SCORE_COMP_TYPE_ID = P_SCORE_COMP_TYPE_ID
216 AND userenv('LANG') IN (LANGUAGE, SOURCE_LANG);
217
218 IF (sql%notfound) THEN
219 RAISE no_data_found;
220 END IF;
221 END UPDATE_ROW;
222
223 PROCEDURE DELETE_ROW (
224 P_SCORE_COMP_TYPE_ID IN NUMBER
225 ) IS
226 BEGIN
227 DELETE FROM IEX_SCORE_COMP_TYPES_TL
228 WHERE SCORE_COMP_TYPE_ID = P_SCORE_COMP_TYPE_ID;
229
230 IF (sql%notfound) THEN
231 RAISE no_data_found;
232 END IF;
233
234 DELETE FROM IEX_SCORE_COMP_TYPES_B
235 WHERE SCORE_COMP_TYPE_ID = P_SCORE_COMP_TYPE_ID;
236
237 IF (sql%notfound) THEN
238 RAISE no_data_found;
239 END IF;
240 END DELETE_ROW;
241
242 PROCEDURE ADD_LANGUAGE
243 IS
244 BEGIN
245 DELETE FROM IEX_SCORE_COMP_TYPES_TL T
246 WHERE NOT EXISTS
247 (SELECT NULL
248 FROM IEX_SCORE_COMP_TYPES_B B
249 WHERE B.SCORE_COMP_TYPE_ID = T.SCORE_COMP_TYPE_ID
250 );
251
252 UPDATE IEX_SCORE_COMP_TYPES_TL T SET
253 (SCORE_COMP_NAME ) =
254 (SELECT
255 B.SCORE_COMP_NAME
256 FROM IEX_SCORE_COMP_TYPES_TL B
257 WHERE B.SCORE_COMP_TYPE_ID = T.SCORE_COMP_TYPE_ID
258 AND B.LANGUAGE = T.SOURCE_LANG)
259 WHERE (T.SCORE_COMP_TYPE_ID,
260 T.LANGUAGE) IN
261 (SELECT
262 SUBT.SCORE_COMP_TYPE_ID,
263 SUBT.LANGUAGE
264 FROM IEX_SCORE_COMP_TYPES_TL SUBB, IEX_SCORE_COMP_TYPES_TL SUBT
265 WHERE SUBB.SCORE_COMP_TYPE_ID = SUBT.SCORE_COMP_TYPE_ID
266 AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
267 AND (SUBB.SCORE_COMP_NAME <> SUBT.SCORE_COMP_NAME)
268 OR (SUBB.SCORE_COMP_NAME IS NULL AND SUBT.SCORE_COMP_NAME IS NOT NULL)
269 OR (SUBB.SCORE_COMP_NAME IS NOT NULL AND SUBT.SCORE_COMP_NAME IS NULL)
270 );
271
272 INSERT INTO IEX_SCORE_COMP_TYPES_TL (
273 SCORE_COMP_TYPE_ID,
274 OBJECT_VERSION_NUMBER,
275 PROGRAM_ID,
276 LAST_UPDATE_DATE,
277 LAST_UPDATED_BY,
278 LAST_UPDATE_LOGIN,
279 CREATION_DATE,
280 CREATED_BY,
281 SCORE_COMP_NAME,
282 DESCRIPTION,
283 LANGUAGE,
284 SOURCE_LANG
285 ) SELECT
286 B.SCORE_COMP_TYPE_ID,
287 B.OBJECT_VERSION_NUMBER,
288 B.PROGRAM_ID,
289 B.LAST_UPDATE_DATE,
290 B.LAST_UPDATED_BY,
291 B.LAST_UPDATE_LOGIN,
292 B.CREATION_DATE,
293 B.CREATED_BY,
294 B.SCORE_COMP_NAME,
295 B.DESCRIPTION,
296 L.LANGUAGE_CODE,
297 B.SOURCE_LANG
298 FROM IEX_SCORE_COMP_TYPES_TL B, FND_LANGUAGES L
299 WHERE L.INSTALLED_FLAG IN ('I', 'B')
300 AND B.LANGUAGE = userenv('LANG')
301 AND NOT EXISTS
302 (SELECT NULL
303 FROM IEX_SCORE_COMP_TYPES_TL T
304 WHERE T.SCORE_COMP_TYPE_ID = B.SCORE_COMP_TYPE_ID
305 AND T.LANGUAGE = L.LANGUAGE_CODE);
306 END ADD_LANGUAGE;
307
308 BEGIN
309 PG_DEBUG := TO_NUMBER(NVL(FND_PROFILE.value('IEX_DEBUG_LEVEL'), '20'));
310
311 END IEX_SCORE_COMP_TYPES_PKG;