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.AMW_EVALUATIONS_PKG

Source


1 PACKAGE BODY AMW_EVALUATIONS_PKG as
2 /*$Header: amwevalb.pls 115.8 2004/01/27 02:05:13 kosriniv noship $*/
3 
4 
5 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AMW_EVALUATIONS_PKG';
6 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amwevalb.pls';
7 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
8 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
9 
10 
11 procedure insert_row (
12  p_EVALUATION_SET_ID               IN NUMBER,
13  p_EVALUATION_OBJECT_NAME          IN VARCHAR2,
14  p_EVALUATION_CONTEXT              IN VARCHAR2,
15  p_EVALUATION_TYPE                 IN VARCHAR2,
16  -- 12.31.2003 tsho: for bug 3326347, don't convert varchar2 to date
17  -- p_DATE_EVALUATED                  IN VARCHAR2,
18  p_DATE_EVALUATED                  IN DATE,
19  p_PK1_VALUE                       IN VARCHAR2,
20  p_PK2_VALUE                       IN VARCHAR2,
21  p_PK3_VALUE                       IN VARCHAR2,
22  p_PK4_VALUE                       IN VARCHAR2,
23  p_PK5_VALUE                       IN VARCHAR2,
24  p_ENTERED_BY_ID                   IN NUMBER,
25  p_EXECUTED_BY_ID                  IN NUMBER,
26  p_COMMENTS			   IN VARCHAR2,
27  p_DES_EFF			   IN VARCHAR2,
28  p_OP_EFF			   IN VARCHAR2,
29  p_OV_EFF			   IN VARCHAR2,
30  p_PGMODE			   IN VARCHAR2,
31  p_EVALUATION_ID		   IN NUMBER,
32  p_commit		           in varchar2 := FND_API.G_FALSE,
33  p_validation_level		   IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
34  p_init_msg_list		   IN VARCHAR2 := FND_API.G_FALSE,
35  x_return_status		   out nocopy varchar2,
36  x_msg_count			   out nocopy number,
37  x_msg_data			   out nocopy varchar2,
38  p_EVALUATION_SET_STATUS_CODE          IN VARCHAR2
39 ) is
40 
41   L_API_NAME CONSTANT VARCHAR2(30) := 'insert_row';
42   l_evaluation_id number;
43 
44   begin
45   x_return_status := FND_API.G_RET_STS_SUCCESS;
46   IF FND_API.to_Boolean( p_init_msg_list )  THEN
47      FND_MSG_PUB.initialize;
48   END IF;
49   IF FND_GLOBAL.User_Id IS NULL THEN
50     AMW_Utility_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
51     RAISE FND_API.G_EXC_ERROR;
52   END IF;
53 
54 
55 if p_PGMODE = 'CR' then
56 
57 /*****************************************************************************/
58 /********************************CODE FOR CREATE******************************/
59 /*****************************************************************************/
60 
61 insert into amw_evaluations_b (
62  EVALUATION_ID,
63  EVALUATION_SET_ID,
64  EVALUATION_OBJECT_NAME,
65  EVALUATION_CONTEXT,
66  EVALUATION_TYPE,
67  DATE_EVALUATED,
68  PK1_VALUE,
69  PK2_VALUE,
70  ENTERED_BY_ID,
71  EXECUTED_BY_ID,
72  LAST_UPDATE_DATE,
73  LAST_UPDATED_BY,
74  CREATION_DATE,
75  CREATED_BY,
76  LAST_UPDATE_LOGIN,
77  OBJECT_VERSION_NUMBER,
78  EVALUATION_SET_STATUS_CODE
79 )
80 values
81 (
82 AMW_EVALUATION_ID_S.nextval,
83 p_EVALUATION_SET_ID,
84 p_EVALUATION_OBJECT_NAME,
85 p_EVALUATION_CONTEXT,
86 p_EVALUATION_TYPE,
87 -- to_date(p_DATE_EVALUATED, 'DD-MON-YYYY HH24:MI:SS'),
88 -- to_date(p_DATE_EVALUATED),
89 -- 12.31.2003 tsho: for bug 3326347, don't convert varchar2 to date
90 --to_date(p_DATE_EVALUATED, 'DD/MM/YYYY'),
91 p_DATE_EVALUATED,
92 p_PK1_VALUE,
93 p_PK2_VALUE,
94 p_ENTERED_BY_ID,
95 p_EXECUTED_BY_ID,
96 sysdate,
97 G_USER_ID,
98 sysdate,
99 G_USER_ID,
100 G_LOGIN_ID,
101 1,
102 p_EVALUATION_SET_STATUS_CODE
103 )returning EVALUATION_ID into l_evaluation_id;
104 
105 
106  insert into AMW_EVALUATIONS_TL (
107  EVALUATION_ID,
108  COMMENTS,
109  LANGUAGE,
110  SOURCE_LANG,
111  LAST_UPDATE_DATE,
112  LAST_UPDATED_BY,
113  CREATION_DATE,
114  CREATED_BY,
115  LAST_UPDATE_LOGIN,
116  OBJECT_VERSION_NUMBER
117  )	select
118 	l_evaluation_id,
119 	p_comments,
120 	L.LANGUAGE_CODE,
121 	userenv('LANG'),
122 	sysdate,
123 	G_USER_ID,
124 	sysdate,
125 	G_USER_ID,
126 	G_LOGIN_ID,
127 	1
128 	from FND_LANGUAGES L
129 	where L.INSTALLED_FLAG in ('I', 'B')
130 	and not exists
131 		(select NULL
132 		 from AMW_EVALUATIONS_TL T
133 		 where T.EVALUATION_ID = l_evaluation_id
134 		 and T.LANGUAGE = L.LANGUAGE_CODE);
135 
136 
137 if p_EVALUATION_OBJECT_NAME = 'PROCEDURE_CONTROL' then
138 	insert into amw_evaluations_details (
139 	 EVALUATION_ID,
140 	 EVALUATION_COMPONENT,
144 	 CREATED_BY,
141 	 LAST_UPDATE_DATE,
142 	 LAST_UPDATED_BY,
143 	 CREATION_DATE,
145 	 LAST_UPDATE_LOGIN,
146 	 EVALUATION_CONCLUSION )
147 	 values
148 	 (
149 	 l_evaluation_id,
150 	 'DESIGN_EFFECTIVENESS',
151 	 sysdate,
152 	 G_USER_ID,
153 	 sysdate,
154 	 G_USER_ID,
155 	 G_LOGIN_ID,
156 	 p_DES_EFF
157 	 );
158 	insert into amw_evaluations_details (
159 	 EVALUATION_ID,
160 	 EVALUATION_COMPONENT,
161 	 LAST_UPDATE_DATE,
162 	 LAST_UPDATED_BY,
163 	 CREATION_DATE,
164 	 CREATED_BY,
165 	 LAST_UPDATE_LOGIN,
166 	 EVALUATION_CONCLUSION )
167 	 values
168 	 (
169 	 l_evaluation_id,
173 	 sysdate,
170 	 'OPERATING_EFFECTIVENESS',
171 	 sysdate,
172 	 G_USER_ID,
174 	 G_USER_ID,
175 	 G_LOGIN_ID,
176 	 p_OP_EFF
177 	 );
178 end if;
182 	 EVALUATION_COMPONENT,
179 if p_EVALUATION_OBJECT_NAME = 'PROCEDURE' then
180 	insert into amw_evaluations_details (
181 	 EVALUATION_ID,
183 	 LAST_UPDATE_DATE,
184 	 LAST_UPDATED_BY,
185 	 CREATION_DATE,
186 	 CREATED_BY,
187 	 LAST_UPDATE_LOGIN,
188 	 EVALUATION_CONCLUSION )
189 	 values
190 	 (
191 	 l_evaluation_id,
192 	 'OVERALL_EFFECTIVENESS',
193 	 sysdate,
194 	 G_USER_ID,
195 	 sysdate,
196 	 G_USER_ID,
197 	 G_LOGIN_ID,
198 	 p_OV_EFF
199 	 );
200 end if;
201 if p_EVALUATION_OBJECT_NAME = 'ASSESSMENT_COMPONENT' OR p_EVALUATION_OBJECT_NAME = 'ASSESSMENT' then
202 	insert into amw_evaluations_details (
203 	 EVALUATION_ID,
204 	 EVALUATION_COMPONENT,
205 	 LAST_UPDATE_DATE,
206 	 LAST_UPDATED_BY,
207 	 CREATION_DATE,
208 	 CREATED_BY,
209 	 LAST_UPDATE_LOGIN,
210 	 EVALUATION_CONCLUSION )
211 	 values
215 	 sysdate,
212 	 (
213 	 l_evaluation_id,
214 	 'CONCLUSION',
216 	 G_USER_ID,
217 	 sysdate,
218 	 G_USER_ID,
219 	 G_LOGIN_ID,
220 	 p_OV_EFF
221 	 );
222 end if;
223 
224 else -- p_PGMODE = 'CR'
225 
226 /*****************************************************************************/
227 /********************************CODE FOR UPDATE******************************/
228 /*****************************************************************************/
229 
230 update amw_evaluations_b set
231 -- DATE_EVALUATED = to_date(p_DATE_EVALUATED, 'DD-MON-YYYY HH24:MI:SS'),
232 -- DATE_EVALUATED = to_date(p_DATE_EVALUATED),
233 -- 12.31.2003 tsho: for bug 3326347, don't convert varchar2 to date
234 -- DATE_EVALUATED = to_date(p_DATE_EVALUATED,'DD/MM/YYYY'),
235  DATE_EVALUATED = p_DATE_EVALUATED,
236  ENTERED_BY_ID = p_ENTERED_BY_ID,
237  EXECUTED_BY_ID = p_EXECUTED_BY_ID,
238  LAST_UPDATE_DATE = sysdate,
239  LAST_UPDATED_BY = G_USER_ID,
240  CREATION_DATE = sysdate,
241  CREATED_BY = G_USER_ID,
242  LAST_UPDATE_LOGIN = G_LOGIN_ID,
243  OBJECT_VERSION_NUMBER = (OBJECT_VERSION_NUMBER + 1),
244  EVALUATION_SET_STATUS_CODE = p_EVALUATION_SET_STATUS_CODE
245  where EVALUATION_ID = p_EVALUATION_ID;
246 
247 
248 update AMW_EVALUATIONS_TL set
249  COMMENTS = p_comments,
250  LAST_UPDATE_DATE = sysdate,
251  LAST_UPDATED_BY = G_USER_ID,
252  CREATION_DATE = sysdate,
253  CREATED_BY = G_USER_ID,
254  LAST_UPDATE_LOGIN = G_LOGIN_ID,
255  OBJECT_VERSION_NUMBER = (OBJECT_VERSION_NUMBER + 1)
256  where evaluation_id = p_EVALUATION_ID;
257 
258 
259 if p_EVALUATION_OBJECT_NAME = 'PROCEDURE_CONTROL' then
260 
261 	update amw_evaluations_details set
262 	 EVALUATION_CONCLUSION = p_DES_EFF,
263 	 LAST_UPDATE_DATE = sysdate,
264 	 LAST_UPDATED_BY = G_USER_ID,
265 	 CREATION_DATE = sysdate,
266 	 CREATED_BY = G_USER_ID,
267 	 LAST_UPDATE_LOGIN = G_LOGIN_ID
268 	 where evaluation_id = p_evaluation_id
269 	 and EVALUATION_COMPONENT = 'DESIGN_EFFECTIVENESS';
270 
271 
272 	update amw_evaluations_details set
273 	 EVALUATION_CONCLUSION = p_OP_EFF,
274 	 LAST_UPDATE_DATE = sysdate,
275 	 LAST_UPDATED_BY = G_USER_ID,
276 	 CREATION_DATE = sysdate,
277 	 CREATED_BY = G_USER_ID,
278 	 LAST_UPDATE_LOGIN = G_LOGIN_ID
279 	 where evaluation_id = p_evaluation_id
280 	 and EVALUATION_COMPONENT = 'OPERATING_EFFECTIVENESS';
281 end if;
282 
283 if p_EVALUATION_OBJECT_NAME = 'PROCEDURE' then
284 
285 	update amw_evaluations_details set
286 	 EVALUATION_CONCLUSION = p_OV_EFF,
287 	 LAST_UPDATE_DATE = sysdate,
288 	 LAST_UPDATED_BY = G_USER_ID,
289 	 CREATION_DATE = sysdate,
290 	 CREATED_BY = G_USER_ID,
291 	 LAST_UPDATE_LOGIN = G_LOGIN_ID
292 	 where evaluation_id = p_evaluation_id
293 	 and EVALUATION_COMPONENT = 'OVERALL_EFFECTIVENESS';
294 
295 end if;
296 
297 
298 if p_EVALUATION_OBJECT_NAME = 'ASSESSMENT_COMPONENT' OR p_EVALUATION_OBJECT_NAME = 'ASSESSMENT' then
299 
300 	update amw_evaluations_details set
301 	 EVALUATION_CONCLUSION = p_OV_EFF,
302 	 LAST_UPDATE_DATE = sysdate,
303 	 LAST_UPDATED_BY = G_USER_ID,
304 	 CREATION_DATE = sysdate,
305 	 CREATED_BY = G_USER_ID,
306 	 LAST_UPDATE_LOGIN = G_LOGIN_ID
307 	 where evaluation_id = p_evaluation_id
308 	 and EVALUATION_COMPONENT = 'CONCLUSION';
309 
310 end if;
311 
312 end if; -- p_PGMODE = 'UP'
313 
314 
315 exception
316   WHEN FND_API.G_EXC_ERROR THEN
317      ROLLBACK;
318      x_return_status := FND_API.G_RET_STS_ERROR;
319      FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
320 
321   WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
322      ROLLBACK;
323      x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
324      FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
325 
326   WHEN OTHERS THEN
327      ROLLBACK;
328      x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
329      IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
330      THEN
331         FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name);
332      END IF;
333      FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data  => x_msg_data);
334 
335 end insert_row;
336 
337 
338 function get_op_effectiveness(p_evaluation_id IN NUMBER) return varchar2 IS
339 l_ev_conl varchar2(1);
340 begin
341 	select evaluation_conclusion
342 	into l_ev_conl
343 	from amw_evaluations_details
344 	where evaluation_id = p_evaluation_id
345 	and evaluation_component = 'OPERATING_EFFECTIVENESS';
346 
347 	RETURN AMW_Utility_PVT.get_lookup_meaning('AMW_PASS_FAIL', l_ev_conl);
348 end get_op_effectiveness;
349 
350 
351 function get_des_effectiveness(p_evaluation_id IN NUMBER) return varchar2 IS
352 l_ev_conl varchar2(1);
353 begin
354 	select evaluation_conclusion
355 	into l_ev_conl
356 	from amw_evaluations_details
357 	where evaluation_id = p_evaluation_id
358 	and evaluation_component = 'DESIGN_EFFECTIVENESS';
359 
360 	RETURN AMW_Utility_PVT.get_lookup_meaning('AMW_PASS_FAIL', l_ev_conl);
361 end get_des_effectiveness;
362 
363 function get_op_effectiveness_code(p_evaluation_id IN NUMBER) return varchar2 IS
364 l_ev_conl varchar2(1);
365 begin
366 	select evaluation_conclusion
367 	into l_ev_conl
368 	from amw_evaluations_details
369 	where evaluation_id = p_evaluation_id
370 	and evaluation_component = 'OPERATING_EFFECTIVENESS';
371 
372 	RETURN l_ev_conl;
373 end get_op_effectiveness_code;
374 
375 
379 	select evaluation_conclusion
376 function get_des_effectiveness_code(p_evaluation_id IN NUMBER) return varchar2 IS
377 l_ev_conl varchar2(1);
378 begin
380 	into l_ev_conl
381 	from amw_evaluations_details
382 	where evaluation_id = p_evaluation_id
383 	and evaluation_component = 'DESIGN_EFFECTIVENESS';
384 
385 	RETURN l_ev_conl;
386 end get_des_effectiveness_code;
387 
388 function get_line_conclusion(p_evaluation_id IN NUMBER) return varchar2 IS
389 l_ev_conl varchar2(1);
390 begin
391 	select evaluation_conclusion
392 	into l_ev_conl
393 	from amw_evaluations_details
394 	where evaluation_id = p_evaluation_id
395 	and evaluation_component = 'LINE_CONCLUSION';
396 
397 	RETURN AMW_Utility_PVT.get_lookup_meaning('AMW_EVALUATION_CONCLUSION', l_ev_conl);
398 end get_line_conclusion;
399 
400 function get_line_conclusion_code(p_evaluation_id IN NUMBER) return varchar2 IS
401 l_ev_conl varchar2(1);
402 begin
403 	select evaluation_conclusion
404 	into l_ev_conl
405 	from amw_evaluations_details
406 	where evaluation_id = p_evaluation_id
407 	and evaluation_component = 'LINE_COMPONENT';
408 
409 	RETURN l_ev_conl;
410 end get_line_conclusion_code;
411 
412 
413 function isEvalOwnerOrExecutor(p_evaluation_id IN NUMBER, p_user_id IN NUMBER) return varchar2 IS
414 n     number;
415 BEGIN
416    select count(*)
417    into n
418    from  amw_evaluations_vl
419    where evaluation_id = p_evaluation_id
420    and   (executed_by_id = p_user_id or entered_by_id = p_user_id);
421 
422    if n > 0 then
423        return 'Y';
424    else
425        return 'N';
426    end if;
427 end isEvalOwnerOrExecutor;
428 
429 
430 function isEvalExecutorOfAssessment(p_assessment_id IN NUMBER, p_user_id IN NUMBER, p_eval_context IN VARCHAR2) return varchar2 IS
431 n     number;
432 BEGIN
433    select count(*)
434    into n
435    from  amw_evaluations_vl
436    where executed_by_id = p_user_id
437     and evaluation_object_name = 'ASSESSMENT'
438     and evaluation_type = '1'
439     and evaluation_context = p_eval_context
440     and pk1_value = p_assessment_id;
441 
442    if n > 0 then
443        return 'Y';
444    else
445        return 'N';
446    end if;
447 end isEvalExecutorOfAssessment;
448 
449 procedure ADD_LANGUAGE
450 is
451 begin
452   delete from AMW_EVALUATIONS_TL T
453   where not exists
454     (select NULL
455     from AMW_EVALUATIONS_B B
456     where B.EVALUATION_ID = T.EVALUATION_ID
457     );
458 
459   update AMW_EVALUATIONS_TL T set (
460       COMMENTS
461     ) = (select
462       B.COMMENTS
463     from AMW_EVALUATIONS_TL B
464     where B.EVALUATION_ID = T.EVALUATION_ID
465     and B.LANGUAGE = T.SOURCE_LANG)
466   where (
467       T.EVALUATION_ID,
468       T.LANGUAGE
469   ) in (select
470       SUBT.EVALUATION_ID,
471       SUBT.LANGUAGE
472     from AMW_EVALUATIONS_TL SUBB, AMW_EVALUATIONS_TL SUBT
473     where SUBB.EVALUATION_ID = SUBT.EVALUATION_ID
474     and SUBB.LANGUAGE = SUBT.SOURCE_LANG
475     and (SUBB.COMMENTS <> SUBT.COMMENTS
476       or (SUBB.COMMENTS is null and SUBT.COMMENTS is not null)
477       or (SUBB.COMMENTS is not null and SUBT.COMMENTS is null)
478   ));
479 
480   insert into AMW_EVALUATIONS_TL (
481     EVALUATION_ID,
482     COMMENTS,
483     LAST_UPDATE_DATE,
484     LAST_UPDATED_BY,
485     CREATION_DATE,
486     CREATED_BY,
487     LAST_UPDATE_LOGIN,
488     SECURITY_GROUP_ID,
489     OBJECT_VERSION_NUMBER,
490     LANGUAGE,
491     SOURCE_LANG
492   ) select
493     B.EVALUATION_ID,
494     B.COMMENTS,
495     B.LAST_UPDATE_DATE,
496     B.LAST_UPDATED_BY,
497     B.CREATION_DATE,
498     B.CREATED_BY,
499     B.LAST_UPDATE_LOGIN,
500     B.SECURITY_GROUP_ID,
501     B.OBJECT_VERSION_NUMBER,
502     L.LANGUAGE_CODE,
503     B.SOURCE_LANG
504   from AMW_EVALUATIONS_TL B, FND_LANGUAGES L
505   where L.INSTALLED_FLAG in ('I', 'B')
506   and B.LANGUAGE = userenv('LANG')
507   and not exists
508     (select NULL
509     from AMW_EVALUATIONS_TL T
510     where T.EVALUATION_ID = B.EVALUATION_ID
511     and T.LANGUAGE = L.LANGUAGE_CODE);
512 end ADD_LANGUAGE;
513 
514 
515 end AMW_EVALUATIONS_PKG;