[Home] [Help]
PACKAGE BODY: APPS.AMW_OBJECT_ASSESSMENTS_PVT
Source
1 PACKAGE BODY AMW_OBJECT_ASSESSMENTS_PVT AS
2 /* $Header: amwobassb.pls 120.0 2005/05/31 22:21:42 appldev noship $ */
3
4 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
5 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
6
7 G_PKG_NAME CONSTANT VARCHAR2 (30) := 'AMW_OBJECT_ASSESSMENTS_PVT';
8 G_FILE_NAME CONSTANT VARCHAR2 (15) := 'amwobassb.pls';
9
10
11 FUNCTION check_object_assess_exists
12 (
13 p_assessment_id IN NUMBER,
14 p_object_type IN VARCHAR2
15 )
16 RETURN VARCHAR2 IS
17
18 l_dummy NUMBER;
19
20 BEGIN
21 SELECT 1 INTO l_dummy
22 FROM amw_object_assessments
23 WHERE assessment_id = p_assessment_id
24 AND object_type = p_object_type;
25
26 RETURN 'Y';
27
28 EXCEPTION
29 WHEN NO_DATA_FOUND THEN
30 RETURN 'N';
31
32 WHEN TOO_MANY_ROWS THEN
33 RETURN 'Y';
34 END check_object_assess_exists;
35
36 PROCEDURE create_object_assessment
37 (
38 p_api_version_number IN NUMBER := 1.0,
39 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
40 p_commit IN VARCHAR2 := FND_API.g_false,
41 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
42 p_object_type IN VARCHAR2,
43 p_assessment_id IN NUMBER,
44 p_certification_id IN NUMBER,
45 p_org_id IN NUMBER,
46 p_process_id IN NUMBER,
47 x_return_status OUT nocopy VARCHAR2,
48 x_msg_count OUT nocopy NUMBER,
49 x_msg_data OUT nocopy VARCHAR2
50 )
51 IS
52
53 l_api_name CONSTANT VARCHAR2(30) := 'create_object_assessment';
54 l_api_version_number CONSTANT NUMBER := 1.0;
55
56 l_return_status VARCHAR2(32767);
57 l_msg_count NUMBER;
58 l_msg_data VARCHAR2(32767);
59
60 BEGIN
61
62 SAVEPOINT CREATE_OBJECT;
63
64 -- Standard call to check for call compatibility.
65 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
66 p_api_version_number,
67 l_api_name,
68 G_PKG_NAME)
69 THEN
70 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
71 END IF;
72
73 -- Initialize message list if p_init_msg_list is set to TRUE.
74 IF FND_API.to_Boolean( p_init_msg_list )
75 THEN
76 FND_MSG_PUB.initialize;
77 END IF;
78
79 -- Initialize API return status to SUCCESS
80 x_return_status := FND_API.G_RET_STS_SUCCESS;
81
82 IF p_object_type = 'PROCESS'
83 THEN
84
85 UPDATE amw_object_assessments apa
86 SET apa.assessment_id = p_assessment_id
87 WHERE apa.pk1 = p_certification_id
88 AND apa.pk2 = p_org_id
89 AND apa.pk3 = p_process_id
90 AND apa.object_type = p_object_type;
91
92 INSERT INTO amw_object_assessments (
93 object_assessment_id,
94 assessment_id,
95 object_type,
96 pk1,
97 pk2,
98 pk3,
99 last_update_date,
100 last_updated_by,
101 creation_date,
102 created_by,
103 last_update_login,
104 object_version_number)
105 SELECT amw_object_assessments_s.nextval,
106 p_assessment_id,
107 p_object_type,
108 p_certification_id,
109 p_org_id,
110 p_process_id,
111 sysdate,
112 fnd_global.user_id,
113 sysdate,
114 fnd_global.user_id,
115 fnd_global.user_id,
116 1
117 FROM dual
118 WHERE NOT EXISTS
119 (SELECT 'Y' FROM amw_object_assessments
120 WHERE pk1 = p_certification_id
121 AND pk2 = p_org_id
122 AND pk3 = p_process_id
123 AND object_type = 'PROCESS'
124 );
125 END IF;
126
127 EXCEPTION WHEN OTHERS THEN
128 ROLLBACK TO CREATE_OBJECT;
129 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
130
131 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name);
132 FND_MSG_PUB.Count_And_Get(
133 p_encoded => FND_API.G_FALSE,
134 p_count => x_msg_count,
135 p_data => x_msg_data);
136
137
138 END create_object_assessment;
139
140
141 PROCEDURE update_object_assessment
142 (
143 p_api_version_number IN NUMBER := 1.0,
144 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
145 p_commit IN VARCHAR2 := FND_API.g_false,
146 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
147 p_object_type IN VARCHAR2,
148 p_assessment_id IN NUMBER,
149 p_certification_id IN NUMBER,
150 p_org_id IN NUMBER,
151 p_process_id IN NUMBER,
152 x_return_status OUT nocopy VARCHAR2,
153 x_msg_count OUT nocopy NUMBER,
154 x_msg_data OUT nocopy VARCHAR2
155 )
156 IS
157
158 l_api_name CONSTANT VARCHAR2(30) := 'update_object_assessment';
159 l_api_version_number CONSTANT NUMBER := 1.0;
160
161 l_return_status VARCHAR2(32767);
162 l_msg_count NUMBER;
163 l_msg_data VARCHAR2(32767);
164
165 BEGIN
166
167 SAVEPOINT UPDATE_OBJECT;
168
169 -- Standard call to check for call compatibility.
170 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
171 p_api_version_number,
172 l_api_name,
173 G_PKG_NAME)
174 THEN
175 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
176 END IF;
177
178 -- Initialize message list if p_init_msg_list is set to TRUE.
179 IF FND_API.to_Boolean( p_init_msg_list )
180 THEN
181 FND_MSG_PUB.initialize;
182 END IF;
183
184 -- Initialize API return status to SUCCESS
185 x_return_status := FND_API.G_RET_STS_SUCCESS;
186
187 IF p_object_type = 'PROCESS'
188 THEN
189 UPDATE amw_object_assessments apa
190 SET apa.assessment_id = p_assessment_id
191 WHERE apa.pk1 = p_certification_id
192 AND apa.pk2 = p_org_id
193 AND apa.pk3 = p_process_id
194 AND apa.object_type = p_object_type;
195 END IF;
196
197 EXCEPTION WHEN OTHERS THEN
198 ROLLBACK TO UPDATE_OBJECT;
199 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
200
201 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name);
202 FND_MSG_PUB.Count_And_Get(
203 p_encoded => FND_API.G_FALSE,
204 p_count => x_msg_count,
205 p_data => x_msg_data);
206
207
208 END update_object_assessment;
209
210 PROCEDURE remove_object_assessment
211 (
212 p_api_version_number IN NUMBER := 1.0,
213 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
214 p_commit IN VARCHAR2 := FND_API.g_false,
215 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
216 p_object_type IN VARCHAR2,
217 p_assessment_id IN NUMBER,
218 p_certification_id IN NUMBER,
219 p_org_id IN NUMBER,
220 p_process_id IN NUMBER,
221 x_return_status OUT nocopy VARCHAR2,
222 x_msg_count OUT nocopy NUMBER,
223 x_msg_data OUT nocopy VARCHAR2
224 )
225 IS
226
227 l_api_name CONSTANT VARCHAR2(30) := 'remove_object_assessment';
228 l_api_version_number CONSTANT NUMBER := 1.0;
229
230 l_return_status VARCHAR2(32767);
231 l_msg_count NUMBER;
232 l_msg_data VARCHAR2(32767);
233
234 BEGIN
235 SAVEPOINT REMOVE_OBJECT;
236
237 -- Standard call to check for call compatibility.
238 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
239 p_api_version_number,
240 l_api_name,
241 G_PKG_NAME)
242 THEN
243 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
244 END IF;
245
246 -- Initialize message list if p_init_msg_list is set to TRUE.
247 IF FND_API.to_Boolean( p_init_msg_list )
248 THEN
249 FND_MSG_PUB.initialize;
250 END IF;
251
252 -- Initialize API return status to SUCCESS
253 x_return_status := FND_API.G_RET_STS_SUCCESS;
254
255 IF p_object_type = 'PROCESS'
256 THEN
257 DELETE FROM amw_object_assessments apa
258 WHERE apa.pk1 = p_certification_id
259 AND apa.pk2 = p_org_id
260 AND apa.pk3 = p_process_id
261 AND apa.object_type = p_object_type;
262 END IF;
263
264 EXCEPTION WHEN OTHERS THEN
265 ROLLBACK TO REMOVE_OBJECT;
266 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
267
268 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name);
269 FND_MSG_PUB.Count_And_Get(
270 p_encoded => FND_API.G_FALSE,
271 p_count => x_msg_count,
272 p_data => x_msg_data);
273
274 END remove_object_assessment;
275
276 END AMW_OBJECT_ASSESSMENTS_PVT;
277
278