1 PACKAGE BODY AMW_PUBLIC_PKG AS
2 /* $Header: amwpbpkb.pls 115.0 2004/04/28 19:50:33 abedajna noship $ */
3
4 FUNCTION get_proc_org_opinion_status(p_process_id in number, p_org_id in number, p_mode in varchar2) return varchar2 is
5
6 l_last_audit_status varchar2(240);
7
8 begin
9
10 select audit_result
11 into l_last_audit_status
12 from amw_opinions_v
13 where pk1_value = p_process_id and pk3_value = p_org_id
14 and object_opinion_type_id =
15 (select object_opinion_type_id from AMW_OBJECT_OPINION_TYPES
16 where opinion_type_id = (select opinion_type_id from amw_opinion_types_b where opinion_type_code = p_mode)
17 and object_id = (select object_id from fnd_objects where obj_name = 'AMW_ORG_PROCESS') )
18 and last_update_date =
19 (select max(last_update_date) from amw_opinions_v
20 where pk1_value = p_process_id and pk3_value = p_org_id
21 and object_opinion_type_id =
22 (select object_opinion_type_id from AMW_OBJECT_OPINION_TYPES
23 where opinion_type_id = (select opinion_type_id from amw_opinion_types_b where opinion_type_code = p_mode)
24 and object_id = (select object_id from fnd_objects where obj_name = 'AMW_ORG_PROCESS') ) );
25
26 return l_last_audit_status;
27
28 exception
29 when no_data_found then
30 return null;
31 when others then
32 return null;
33
34 end get_proc_org_opinion_status;
35
36
37
38 FUNCTION get_proc_org_opinion_date(p_process_id in number, p_org_id in number, p_mode in varchar2) return varchar2 is
39
40 l_last_update_date date;
41
42 begin
43
44 select max(last_update_date) into l_last_update_date from amw_opinions_v
45 where pk1_value = p_process_id and pk3_value = p_org_id
46 and object_opinion_type_id =
47 (select object_opinion_type_id from AMW_OBJECT_OPINION_TYPES
48 where opinion_type_id = (select opinion_type_id from amw_opinion_types_b where opinion_type_code = p_mode)
49 and object_id = (select object_id from fnd_objects where obj_name = 'AMW_ORG_PROCESS') );
50
51 return l_last_update_date;
52
53 exception
54 when no_data_found then
55 return null;
56 when others then
57 return null;
58
59 end get_proc_org_opinion_date;
60
61
62 END AMW_PUBLIC_PKG;
[Home] [Help]