[Home] [Help]
PACKAGE BODY: APPS.AMW_COMPONENTS_PKG
Source
1 PACKAGE BODY AMW_COMPONENTS_PKG as
2 /* $Header: amwvascb.pls 115.6 2004/04/02 01:08:23 npanandi noship $ */
3
4 FUNCTION COMPONENTS_PRESENT (
5 P_OBJECT_ID IN NUMBER,
6 P_OBJECT_TYPE IN VARCHAR2,
7 P_COMPONENT_CODE IN VARCHAR2
8 ) RETURN VARCHAR2 IS
9
10 n number;
11 BEGIN
12 -- 11.25.2003: use object_id and object_type instead
13 /*
14 select count(*)
15 into n
16 from amw_assessment_components
17 where assessment_id = P_ASSESSMENT_ID
18 and component_code = P_COMPONENT_CODE;
19 */
20 select count(*)
21 into n
22 from amw_assessment_components
23 where object_id = P_OBJECT_ID
24 and object_type = P_OBJECT_TYPE
25 and component_code = P_COMPONENT_CODE;
26
27
28 if n > 0 then
29 return 'Y';
30 else
31 return 'N';
32 end if;
33 END COMPONENTS_PRESENT;
34
35
36 FUNCTION NEW_COMPONENTS_PRESENT (
37 P_OBJECT_ID IN NUMBER,
38 P_OBJECT_TYPE IN VARCHAR2,
39 P_COMPONENT_CODE IN VARCHAR2
40 ) RETURN VARCHAR2 IS
41
42 n number;
43 yes varchar2(80);
44 no varchar2(80);
45 BEGIN
46 -- 11.25.2003: use object_id and object_type instead
47 /*
48 select count(*)
49 into n
50 from amw_assessment_components
51 where assessment_id = P_ASSESSMENT_ID
52 and component_code = P_COMPONENT_CODE;
53 */
54 select count(*)
55 into n
56 from amw_assessment_components
57 where object_id = P_OBJECT_ID
58 and object_type = P_OBJECT_TYPE
59 and component_code = P_COMPONENT_CODE;
60
61 select meaning
62 into yes
63 from fnd_lookups
64 where lookup_type='YES_NO'
65 and lookup_code='Y';
66
67 select meaning
68 into no
69 from fnd_lookups
70 where lookup_type='YES_NO'
71 and lookup_code='N';
72
73 if n > 0 then
74 ---return 'Y';
75 return yes;
76 else
77 ---return 'N';
78 return no;
79 end if;
80 END NEW_COMPONENTS_PRESENT;
81
82
83 PROCEDURE PROCESS_COMPONENTS (
84 p_init_msg_list IN VARCHAR2,
85 p_commit IN VARCHAR2,
86 p_validate_only IN VARCHAR2,
87 p_select_flag IN VARCHAR2,
88 -- p_assessment_id IN NUMBER, -- 11.25.2003 tsho: obseleted, use object_id, object_type instead
89 p_object_id IN NUMBER, -- 11.25.2003 tsho: combined with object_type will replace assessment_id
90 p_object_type IN VARCHAR2, -- 11.25.2003 tsho: combined with obejct_id will replace assessment_id
91 p_component_code IN VARCHAR2,
92 x_return_status OUT NOCOPY VARCHAR2,
93 x_msg_count OUT NOCOPY NUMBER,
94 x_msg_data OUT NOCOPY VARCHAR2,
95 p_other_component_value IN VARCHAR2
96 ) IS
97
98 l_creation_date date;
99 l_created_by number;
100 l_last_update_date date;
101 l_last_updated_by number;
102 l_last_update_login number;
103 l_assessment_component_id number;
104 l_object_version_number number;
105
106 BEGIN
107
108 -- create savepoint if p_commit is true
109 IF p_commit = FND_API.G_TRUE THEN
110 SAVEPOINT process_component_save;
111 END IF;
112
113 -- initialize message list if p_init_msg_list is set to true
114 if FND_API.to_boolean(nvl(p_init_msg_list, FND_API.G_FALSE)) then
115 fnd_msg_pub.initialize;
116 end if;
117
118 -- initialize return status to success
119 x_return_status := fnd_api.g_ret_sts_success;
120
121 -- 11.25.2003: use object_id and object_type instead
122 /*
123 delete from amw_assessment_components
124 where assessment_id = p_assessment_id
125 and component_code = p_component_code;
126 */
127 delete from amw_assessment_components
128 where object_id = p_object_id
129 and object_type = p_object_type
130 and component_code = p_component_code;
131
132
133 if (p_select_flag = 'Y') then
134
135 l_creation_date := SYSDATE;
136 l_created_by := FND_GLOBAL.USER_ID;
137 l_last_update_date := SYSDATE;
138 l_last_updated_by := FND_GLOBAL.USER_ID;
139 l_last_update_login := FND_GLOBAL.USER_ID;
140 l_object_version_number := 1;
141
142 select amw_assessment_components_s.nextval into l_assessment_component_id from dual;
143
144 insert into amw_assessment_components (assessment_component_id,
145 -- assessment_id, -- 11.25.2003 tsho: obseleted, use object_id and object_type instead
146 object_type, -- 11.25.2003: combined with object_id will replace assessment_id
147 object_id, -- 11.25.2003: combined with object_type will replace assessment_id
148 component_code,
149 other_component_value,
150 creation_date,
151 created_by,
152 last_update_date,
153 last_updated_by,
154 last_update_login,
155 object_version_number)
156 values (l_assessment_component_id,
157 -- p_assessment_id, -- 11.25.2003 tsho: obseleted, use object_id and object_type instead
158 p_object_type,
159 p_object_id,
160 p_component_code,
161 p_other_component_value,
162 l_creation_date,
163 l_created_by,
164 l_last_update_date,
165 l_last_updated_by,
166 l_last_update_login,
167 l_object_version_number);
168
169 end if;
170 EXCEPTION
171 WHEN FND_API.G_EXC_ERROR THEN
172 IF p_commit = FND_API.G_TRUE THEN
173 ROLLBACK TO assessment_component_save;
174 END IF;
175
176 x_return_status := FND_API.G_RET_STS_ERROR;
177
178 fnd_msg_pub.count_and_get(p_count => x_msg_count,
179 p_data => x_msg_data);
180
181 WHEN OTHERS THEN
182 IF p_commit = FND_API.G_TRUE THEN
183 ROLLBACK TO create_prop_person_support;
184 END IF;
185
186 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
187
188 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AMW_COMPONENTS_PKG',
189 p_procedure_name => 'PROCESS_COMPONENTS',
190 p_error_text => SUBSTRB(SQLERRM,1,240));
191
192 fnd_msg_pub.count_and_get(p_count => x_msg_count,
193 p_data => x_msg_data);
194
195 END PROCESS_COMPONENTS;
196
197 END AMW_COMPONENTS_PKG;