[Home] [Help]
PACKAGE BODY: APPS.GMD_CUSTOMER_TESTS_PVT
Source
1 package body GMD_CUSTOMER_TESTS_PVT as
2 /* $Header: GMDVTCUB.pls 115.5 2002/12/03 17:10:05 cnagarba noship $*/
3
4 procedure INSERT_ROW (
5 X_ROWID in out nocopy ROWID,
6 X_TEST_ID in NUMBER,
7 X_CUST_ID in NUMBER,
8 X_REPORT_PRECISION in NUMBER,
9 X_TEXT_CODE in NUMBER,
10 X_CUST_TEST_DISPLAY in VARCHAR2,
11 X_CREATION_DATE in DATE,
12 X_CREATED_BY in NUMBER,
13 X_LAST_UPDATE_DATE in DATE,
14 X_LAST_UPDATED_BY in NUMBER,
15 X_LAST_UPDATE_LOGIN in NUMBER
16 ) is
17 cursor C is select ROWID from GMD_CUSTOMER_TESTS_B
18 where TEST_ID = X_TEST_ID
19 and CUST_ID = X_CUST_ID
20 ;
21 begin
22
23 insert into GMD_CUSTOMER_TESTS_B (
24 TEST_ID,
25 CUST_ID,
26 REPORT_PRECISION,
27 TEXT_CODE,
28 CREATION_DATE,
29 CREATED_BY,
30 LAST_UPDATE_DATE,
31 LAST_UPDATED_BY,
32 LAST_UPDATE_LOGIN
33 ) values (
34 X_TEST_ID,
35 X_CUST_ID,
36 X_REPORT_PRECISION,
37 X_TEXT_CODE,
38 NVL(X_CREATION_DATE,SYSDATE),
39 NVL(X_CREATED_BY,FND_GLOBAL.USER_ID),
40 NVL(X_LAST_UPDATE_DATE,SYSDATE),
41 NVL(X_LAST_UPDATED_BY,FND_GLOBAL.USER_ID),
42 NVL(X_LAST_UPDATE_LOGIN,FND_GLOBAL.LOGIN_ID)
43 );
44
45 insert into GMD_CUSTOMER_TESTS_TL (
46 TEST_ID,
47 CUST_ID,
48 CUST_TEST_DISPLAY,
49 CREATION_DATE,
50 CREATED_BY,
51 LAST_UPDATE_DATE,
52 LAST_UPDATED_BY,
53 LAST_UPDATE_LOGIN,
54 LANGUAGE,
55 SOURCE_LANG
56 ) select
57 X_TEST_ID,
58 X_CUST_ID,
59 X_CUST_TEST_DISPLAY,
60 NVL(X_CREATION_DATE,SYSDATE),
61 NVL(X_CREATED_BY,FND_GLOBAL.USER_ID),
62 NVL(X_LAST_UPDATE_DATE,SYSDATE),
63 NVL(X_LAST_UPDATED_BY,FND_GLOBAL.USER_ID),
64 NVL(X_LAST_UPDATE_LOGIN,FND_GLOBAL.LOGIN_ID),
65 L.LANGUAGE_CODE,
66 userenv('LANG')
67 from FND_LANGUAGES L
68 where L.INSTALLED_FLAG in ('I', 'B')
69 and not exists
70 (select NULL
71 from GMD_CUSTOMER_TESTS_TL T
72 where T.TEST_ID = X_TEST_ID
73 and T.CUST_ID = X_CUST_ID
74 and T.LANGUAGE = L.LANGUAGE_CODE);
75
76 open c;
77 fetch c into X_ROWID;
78 if (c%notfound) then
79 close c;
80 raise no_data_found;
81 end if;
82 close c;
83
84 end INSERT_ROW;
85
86
87 FUNCTION INSERT_ROW(p_customer_tests_rec IN GMD_CUSTOMER_TESTS%ROWTYPE) RETURN BOOLEAN IS
88 l_rowid ROWID;
89 BEGIN
90 GMD_CUSTOMER_TESTS_PVT.INSERT_ROW(
91 X_ROWID => l_rowid,
92 X_TEST_ID => p_customer_tests_rec.TEST_ID,
93 X_CUST_ID => p_customer_tests_rec.CUST_ID,
94 X_REPORT_PRECISION => p_customer_tests_rec.REPORT_PRECISION,
95 X_TEXT_CODE => p_customer_tests_rec.TEXT_CODE,
96 X_CUST_TEST_DISPLAY => p_customer_tests_rec.CUST_TEST_DISPLAY,
97 X_CREATION_DATE => p_customer_tests_rec.CREATION_DATE,
98 X_CREATED_BY => p_customer_tests_rec.CREATED_BY,
99 X_LAST_UPDATE_DATE => p_customer_tests_rec.LAST_UPDATE_DATE,
100 X_LAST_UPDATED_BY => p_customer_tests_rec.LAST_UPDATED_BY,
101 X_LAST_UPDATE_LOGIN => p_customer_tests_rec.LAST_UPDATE_LOGIN);
102
103 RETURN TRUE;
104
105 EXCEPTION WHEN OTHERS THEN
106 FND_MESSAGE.Set_Name('GMD','GMD_API_ERROR');
107 FND_MESSAGE.Set_Token('PACKAGE','GMD_CUSTOMER_TESTS_PVT.INSERT_ROW');
108 FND_MESSAGE.Set_Token('ERROR', SUBSTR(SQLERRM,1,100));
109 FND_MESSAGE.Set_Token('POSITION','010' );
110 FND_MSG_PUB.ADD;
111 RETURN FALSE;
112 END INSERT_ROW;
113
114 procedure LOCK_ROW (
115 X_TEST_ID in NUMBER,
116 X_CUST_ID in NUMBER,
117 X_REPORT_PRECISION in NUMBER,
118 X_TEXT_CODE in NUMBER,
119 X_CUST_TEST_DISPLAY in VARCHAR2
120 ) is
121 cursor c is select
122 REPORT_PRECISION,
123 TEXT_CODE
124 from GMD_CUSTOMER_TESTS_B
125 where TEST_ID = X_TEST_ID
126 and CUST_ID = X_CUST_ID
127 for update of TEST_ID nowait;
128 recinfo c%rowtype;
129
130 cursor c1 is select
131 CUST_TEST_DISPLAY,
132 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
133 from GMD_CUSTOMER_TESTS_TL
134 where TEST_ID = X_TEST_ID
135 and CUST_ID = X_CUST_ID
136 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
137 for update of TEST_ID nowait;
138 begin
139 open c;
140 fetch c into recinfo;
141 if (c%notfound) then
142 close c;
143 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
144 app_exception.raise_exception;
145 end if;
146 close c;
147 if ( ((recinfo.REPORT_PRECISION = X_REPORT_PRECISION)
148 OR ((recinfo.REPORT_PRECISION is null) AND (X_REPORT_PRECISION is null)))
149 AND ((recinfo.TEXT_CODE = X_TEXT_CODE)
150 OR ((recinfo.TEXT_CODE is null) AND (X_TEXT_CODE is null)))
151 ) then
152 null;
153 else
154 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
155 app_exception.raise_exception;
156 end if;
157
158 for tlinfo in c1 loop
159 if (tlinfo.BASELANG = 'Y') then
160 if ( (tlinfo.CUST_TEST_DISPLAY = X_CUST_TEST_DISPLAY)
161 ) then
162 null;
163 else
164 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
165 app_exception.raise_exception;
166 end if;
167 end if;
168 end loop;
169 return;
170 end LOCK_ROW;
171
172 procedure UPDATE_ROW (
173 X_TEST_ID in NUMBER,
174 X_CUST_ID in NUMBER,
175 X_REPORT_PRECISION in NUMBER,
176 X_TEXT_CODE in NUMBER,
177 X_CUST_TEST_DISPLAY in VARCHAR2,
178 X_LAST_UPDATE_DATE in DATE,
179 X_LAST_UPDATED_BY in NUMBER,
180 X_LAST_UPDATE_LOGIN in NUMBER
181 ) is
182 begin
183 update GMD_CUSTOMER_TESTS_B set
184 REPORT_PRECISION = X_REPORT_PRECISION,
185 TEXT_CODE = X_TEXT_CODE,
186 LAST_UPDATE_DATE = NVL(X_LAST_UPDATE_DATE,SYSDATE),
187 LAST_UPDATED_BY = NVL(X_LAST_UPDATED_BY,FND_GLOBAL.USER_ID),
188 LAST_UPDATE_LOGIN = NVL(X_LAST_UPDATE_LOGIN,FND_GLOBAL.LOGIN_ID)
189 where TEST_ID = X_TEST_ID
190 and CUST_ID = X_CUST_ID;
191
192 if (sql%notfound) then
193 raise no_data_found;
194 end if;
195
196 update GMD_CUSTOMER_TESTS_TL set
197 CUST_TEST_DISPLAY = X_CUST_TEST_DISPLAY,
198 LAST_UPDATE_DATE = NVL(X_LAST_UPDATE_DATE,SYSDATE),
199 LAST_UPDATED_BY = NVL(X_LAST_UPDATED_BY,FND_GLOBAL.USER_ID),
200 LAST_UPDATE_LOGIN = NVL(X_LAST_UPDATE_LOGIN,FND_GLOBAL.LOGIN_ID),
201 SOURCE_LANG = userenv('LANG')
202 where TEST_ID = X_TEST_ID
203 and CUST_ID = X_CUST_ID
204 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
205
206 if (sql%notfound) then
207 raise no_data_found;
208 end if;
209 end UPDATE_ROW;
210
211 FUNCTION DELETE_ROW (
212 P_TEST_ID IN NUMBER,
213 P_CUST_ID IN NUMBER) RETURN BOOLEAN IS
214 begin
215 IF P_TEST_ID IS NOT NULL AND P_CUST_ID IS NOT NULL THEN
216 delete from GMD_CUSTOMER_TESTS_TL
217 where TEST_ID = P_TEST_ID
218 and CUST_ID = P_CUST_ID;
219
220 if (sql%notfound) then
221 raise no_data_found;
222 end if;
223
224 delete from GMD_CUSTOMER_TESTS_B
225 where TEST_ID = P_TEST_ID
226 and CUST_ID = P_CUST_ID;
227
228 if (sql%notfound) then
229 raise no_data_found;
230 end if;
231 ELSE
232 gmd_api_pub.log_message('GMD_NO_KEYS','TABLE_NAME', 'GMD_CUSTOMER_TESTS');
233 RETURN FALSE;
234 END IF;
235
236 RETURN TRUE;
237
238 EXCEPTION
239 WHEN NO_DATA_FOUND
240 THEN
241 gmd_api_pub.log_message('GMD_NO_DATA_FOUND','TABLE_NAME', 'GMD_CUSTOMER_TESTS');
242 RETURN FALSE;
243 WHEN OTHERS THEN
244 gmd_api_pub.log_message('GMD_API_ERROR','PACKAGE','GMD_CUSTOMER_TESTS_PVT.DELETE_ROW','ERROR',SUBSTR(SQLERRM,1,100),'POSITION','010');
245 RETURN FALSE;
246 end DELETE_ROW;
247
248 procedure ADD_LANGUAGE
249 is
250 begin
251 delete from GMD_CUSTOMER_TESTS_TL T
252 where not exists
253 (select NULL
254 from GMD_CUSTOMER_TESTS_B B
255 where B.TEST_ID = T.TEST_ID
256 and B.CUST_ID = T.CUST_ID
257 );
258
259 update GMD_CUSTOMER_TESTS_TL T set (
260 CUST_TEST_DISPLAY
261 ) = (select
262 B.CUST_TEST_DISPLAY
263 from GMD_CUSTOMER_TESTS_TL B
264 where B.TEST_ID = T.TEST_ID
265 and B.CUST_ID = T.CUST_ID
266 and B.LANGUAGE = T.SOURCE_LANG)
267 where (
268 T.TEST_ID,
269 T.CUST_ID,
270 T.LANGUAGE
271 ) in (select
272 SUBT.TEST_ID,
273 SUBT.CUST_ID,
274 SUBT.LANGUAGE
275 from GMD_CUSTOMER_TESTS_TL SUBB, GMD_CUSTOMER_TESTS_TL SUBT
276 where SUBB.TEST_ID = SUBT.TEST_ID
277 and SUBB.CUST_ID = SUBT.CUST_ID
278 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
279 and (SUBB.CUST_TEST_DISPLAY <> SUBT.CUST_TEST_DISPLAY
280 ));
281
282 insert into GMD_CUSTOMER_TESTS_TL (
283 TEST_ID,
284 CUST_ID,
285 CUST_TEST_DISPLAY,
286 CREATION_DATE,
287 CREATED_BY,
288 LAST_UPDATED_BY,
289 LAST_UPDATE_DATE,
290 LAST_UPDATE_LOGIN,
291 LANGUAGE,
292 SOURCE_LANG
293 ) select
294 B.TEST_ID,
295 B.CUST_ID,
296 B.CUST_TEST_DISPLAY,
297 B.CREATION_DATE,
298 B.CREATED_BY,
299 B.LAST_UPDATED_BY,
300 B.LAST_UPDATE_DATE,
301 B.LAST_UPDATE_LOGIN,
302 L.LANGUAGE_CODE,
303 B.SOURCE_LANG
304 from GMD_CUSTOMER_TESTS_TL B, FND_LANGUAGES L
305 where L.INSTALLED_FLAG in ('I', 'B')
306 and B.LANGUAGE = userenv('LANG')
307 and not exists
308 (select NULL
309 from GMD_CUSTOMER_TESTS_TL T
310 where T.TEST_ID = B.TEST_ID
311 and T.CUST_ID = B.CUST_ID
312 and T.LANGUAGE = L.LANGUAGE_CODE);
313 end ADD_LANGUAGE;
314
315 FUNCTION lock_row (
316 p_test_id IN NUMBER,
317 p_cust_id IN NUMBER)
318 RETURN BOOLEAN
319 IS
320 dummy NUMBER;
321 BEGIN
322
323 IF P_TEST_ID IS NOT NULL AND P_CUST_ID IS NOT NULL THEN
324 SELECT test_id
325 INTO dummy
326 FROM gmd_customer_tests_b
327 WHERE test_id = p_test_id
328 AND cust_id = p_cust_id
329 FOR UPDATE OF test_id NOWAIT ;
330 ELSE
331 gmd_api_pub.log_message('GMD_NO_KEYS','TABLE_NAME', 'GMD_CUSTOMER_TESTS');
332 RETURN FALSE;
333 END IF;
334
335 RETURN TRUE;
336
337 EXCEPTION
338 WHEN NO_DATA_FOUND
339 THEN
340 gmd_api_pub.log_message('GMD_NO_DATA_FOUND','TABLE_NAME', 'GMD_CUSTOMER_TESTS');
341 RETURN FALSE;
342 WHEN OTHERS
343 THEN
344 gmd_api_pub.log_message('GMD_API_ERROR','PACKAGE','GMD_CUSTOMER_TESTS_PVT.LOCK_ROW','ERROR',SUBSTR(SQLERRM,1,100),'POSITION','010');
345 RETURN FALSE;
346 END lock_row;
347
348 end GMD_CUSTOMER_TESTS_PVT;