1 PACKAGE BODY AMW_risk_type_PVT AS
2 /* $Header: amwvmrtb.pls 120.0 2005/05/31 21:25:10 appldev noship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- AMW_RISK_TYPE_PVT
7 -- End of Comments
8 -- ===============================================================
9 g_pkg_name CONSTANT VARCHAR2 (30) := 'AMW_RISK_TYPE_PVT';
10 g_file_name CONSTANT VARCHAR2 (12) := 'amwvrtpb.pls';
11 g_user_id NUMBER := fnd_global.user_id;
12 g_login_id NUMBER := fnd_global.conc_login_id;
13 --------------------- BEGIN: Declaring internal Procedures ----------------------
14 -- ==============================================================================
15 -- Start of Comments
16 -- ==============================================================================
17 -- API Name
18 -- Insert_Delete_Risk_Type
19 -- Type
20 -- Public
21 -- Pre-Req
22 --
23 -- Parameters
24 --
25 -- IN
26 -- p_riskrev_id IN NUMBER Optional Default = null
27 -- p_risk_type_code IN VARCHAR2 Required
28 -- p_select_flag IN VARCHAR2 Required
29 -- p_commit IN VARCHAR2 Required Default = FND_API_G_FALSE
30 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
31 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
32 --
33 -- OUT
34 -- x_return_status OUT VARCHAR2
35 -- x_msg_count OUT NUMBER
36 -- x_msg_data OUT VARCHAR2
37 -- Version : Current version 1.0
38 -- Note:
39 --
40 -- End of Comments
41 -- ==============================================================================
42 --
43 PROCEDURE insert_delete_risk_type (
44 p_risk_rev_id IN NUMBER := NULL,
45 p_risk_type_code IN VARCHAR2 := NULL,
46 p_select_flag IN VARCHAR2 := NULL,
47 p_commit IN VARCHAR2 := fnd_api.g_false,
48 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
49 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
50 p_api_version_number IN NUMBER,
51 x_return_status OUT NOCOPY VARCHAR2,
52 x_msg_count OUT NOCOPY NUMBER,
53 x_msg_data OUT NOCOPY VARCHAR2
54 ) IS
55
56 l_api_name CONSTANT VARCHAR2 (30) := 'increase_delete_risk_type';
57 l_api_version_number CONSTANT NUMBER := 1.0;
58 l_risk_type_id NUMBER := 0;
59 l_risk_type_row_count NUMBER := 0;
60 l_creation_date date;
61 l_created_by number;
62 l_last_update_date date;
63 l_last_updated_by number;
64 l_last_update_login number;
65
66 --- for risk association to a process, we are passed risk_id and process_id
67 --- foll. cursor traverses the process hierarchy tree to get all parent processes
68 --- for this process_id
69 CURSOR C1 is
70 (select count(*) from amw_risk_type
71 where risk_rev_id = p_risk_rev_id
72 and risk_type_code = p_risk_type_code);
73
74 BEGIN
75
76 SAVEPOINT amw_risk_type_pvt;
77 x_return_status := fnd_api.g_ret_sts_success;
78 -- Standard call to check for call compatibility.
79 IF NOT fnd_api.compatible_api_call (l_api_version_number,
80 p_api_version_number,
81 l_api_name,
82 g_pkg_name
83 ) THEN
84 RAISE fnd_api.g_exc_unexpected_error;
85 END IF;
86 -- Initialize message list if p_init_msg_list is set to TRUE.
87 IF fnd_api.to_boolean (p_init_msg_list) THEN
88 fnd_msg_pub.initialize;
89 END IF;
90 -- Debug Message
91 amw_utility_pvt.debug_message ('Private API: ' || l_api_name || 'start');
92 -- Initialize API return status to SUCCESS
93 x_return_status := fnd_api.g_ret_sts_success;
94 /* Temporarily commenting out the validata session code ..... */
95 -- =========================================================================
96 -- Validate Environment
97 -- =========================================================================
98 IF fnd_global.user_id IS NULL THEN
99 amw_utility_pvt.error_message
100 (p_message_name => 'USER_PROFILE_MISSING');
101 RAISE fnd_api.g_exc_error;
102 END IF;
103
104
105 IF (p_select_flag = 'N') then
106 OPEN C1;
107 FETCH C1 into l_risk_type_row_count;
108 IF (l_risk_type_row_count = 1) then
109 delete from amw_risk_type
110 where risk_rev_id = p_risk_rev_id
111 and risk_type_code = p_risk_type_code;
112 END IF;
113 CLOSE C1;
114 END IF;
115
116
117 IF (p_select_flag = 'Y') then
118 OPEN C1;
119 FETCH C1 into l_risk_type_row_count;
120
121 IF (l_risk_type_row_count = 0) then
122 select amw_risk_type_s.nextval into l_risk_type_id from dual;
123 l_creation_date := SYSDATE;
124 l_created_by := FND_GLOBAL.USER_ID;
125 l_last_update_date := SYSDATE;
126 l_last_updated_by := FND_GLOBAL.USER_ID;
127 l_last_update_login := FND_GLOBAL.USER_ID;
128
129 insert into amw_risk_type (risk_type_id,
130 risk_rev_id,
131 risk_type_code,
132 creation_date,
133 created_by,
134 last_update_date,
135 last_updated_by,
136 last_update_login,
137 object_version_number)
138
139 values (l_risk_type_id,
140 p_risk_rev_id,
141 p_risk_type_code,
142 l_creation_date,
143 l_created_by,
144 l_last_update_date,
145 l_last_updated_by,
146 l_last_update_login,
147 1);
148 END IF;
149 END IF;
150
151
152 -- =========================================================================
153 -- End Validate Environment
154 -- =========================================================================
155 -- End commenting the session validation code ....
156 IF x_return_status <> fnd_api.g_ret_sts_success THEN
157 RAISE fnd_api.g_exc_error;
158 END IF;
159 -- Standard check for p_commit
160 IF fnd_api.to_boolean (p_commit) THEN
161 COMMIT WORK;
162 END IF;
163 --Debug Message
164 amw_utility_pvt.debug_message ('Private API: ' || l_api_name || 'end');
165 -- Standard call to get message count and if count is 1, get message info.
166 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
167 EXCEPTION
168 WHEN fnd_api.g_exc_error THEN
169 ROLLBACK TO amw_risk_type_pvt;
170 x_return_status := fnd_api.g_ret_sts_error;
171 -- Standard call to get message count and if count=1, get the message
172 fnd_msg_pub.count_and_get (p_encoded => fnd_api.g_false,
173 p_count => x_msg_count,
174 p_data => x_msg_data
175 );
176 WHEN fnd_api.g_exc_unexpected_error THEN
177 ROLLBACK TO amw_risk_type_pvt;
178 x_return_status := fnd_api.g_ret_sts_unexp_error;
179 -- Standard call to get message count and if count=1, get the message
180 fnd_msg_pub.count_and_get (p_encoded => fnd_api.g_false,
181 p_count => x_msg_count,
182 p_data => x_msg_data
183 );
184 WHEN OTHERS THEN
185 ROLLBACK TO amw_risk_type_pvt;
186 x_return_status := fnd_api.g_ret_sts_unexp_error;
187 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error) THEN
188 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
189 END IF;
190 -- Standard call to get message count and if count=1, get the message
191 fnd_msg_pub.count_and_get (p_encoded => fnd_api.g_false,
192 p_count => x_msg_count,
193 p_data => x_msg_data
194 );
195 END insert_delete_risk_type;
196
197
198 -- ===============================================================
199 -- Procedure name
200 -- Revise_Risk_Type
201 -- Purpose
202 -- revise risk type from old RiskRevId to new RiskRevId
203 -- ===============================================================
204 PROCEDURE Revise_Risk_Type(
205 p_old_risk_rev_id IN NUMBER,
206 p_risk_rev_id IN NUMBER,
207 p_commit IN VARCHAR2 := fnd_api.g_false,
208 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
209 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
210 p_api_version_number IN NUMBER,
211 x_return_status OUT NOCOPY VARCHAR2,
212 x_msg_count OUT NOCOPY NUMBER,
213 x_msg_data OUT NOCOPY VARCHAR2
214 ) IS
215 l_api_name CONSTANT VARCHAR2 (30) := 'Revise_Risk_Type';
216 l_api_version_number CONSTANT NUMBER := 1.0;
217 BEGIN
218 SAVEPOINT amw_risk_type_revise;
219 -- Standard call to check for call compatibility.
220 IF NOT fnd_api.compatible_api_call (l_api_version_number,
221 p_api_version_number,
222 l_api_name,
223 g_pkg_name
224 ) THEN
225 RAISE fnd_api.g_exc_unexpected_error;
226 END IF;
227 -- Initialize message list if p_init_msg_list is set to TRUE.
228 IF fnd_api.to_boolean (p_init_msg_list) THEN
229 fnd_msg_pub.initialize;
230 END IF;
231 -- Debug Message
232 amw_utility_pvt.debug_message ('Private API: ' || l_api_name || 'start');
233 -- Initialize API return status to SUCCESS
234 x_return_status := fnd_api.g_ret_sts_success;
235 IF fnd_global.user_id IS NULL THEN
236 amw_utility_pvt.error_message
237 (p_message_name => 'USER_PROFILE_MISSING');
238 RAISE fnd_api.g_exc_error;
239 END IF;
240
241 -- carry over Risk Type Associations when Revising the Risk
242 IF p_old_risk_rev_id is not null AND p_risk_rev_id is not null THEN
243 insert into AMW_RISK_TYPE (
244 risk_type_id
245 ,risk_rev_id
246 ,risk_type_code
247 ,creation_date
248 ,created_by
249 ,last_update_date
250 ,last_updated_by
251 ,last_update_login
252 ,object_version_number
253 )
254 select
255 amw_risk_type_s.nextval
256 ,p_risk_rev_id
257 ,old.RISK_TYPE_CODE
258 ,sysdate
259 ,FND_GLOBAL.USER_ID
260 ,sysdate
261 ,FND_GLOBAL.USER_ID
262 ,FND_GLOBAL.USER_ID
263 ,1
264 from AMW_RISK_TYPE old
265 where old.RISK_REV_ID = p_old_risk_rev_id;
266 END IF;
267
268 IF x_return_status <> fnd_api.g_ret_sts_success THEN
269 RAISE fnd_api.g_exc_error;
270 END IF;
271 -- Standard check for p_commit
272 IF fnd_api.to_boolean (p_commit) THEN
273 COMMIT WORK;
274 END IF;
275 --Debug Message
276 amw_utility_pvt.debug_message ('API: ' || l_api_name || 'end');
277 -- Standard call to get message count and if count is 1, get message info.
278 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
279 EXCEPTION
280 WHEN fnd_api.g_exc_error THEN
281 ROLLBACK TO amw_risk_type_revise;
282 x_return_status := fnd_api.g_ret_sts_error;
283 -- Standard call to get message count and if count=1, get the message
284 fnd_msg_pub.count_and_get (p_encoded => fnd_api.g_false,
285 p_count => x_msg_count,
286 p_data => x_msg_data
287 );
288 WHEN fnd_api.g_exc_unexpected_error THEN
289 ROLLBACK TO amw_risk_type_revise;
290 x_return_status := fnd_api.g_ret_sts_unexp_error;
291 -- Standard call to get message count and if count=1, get the message
292 fnd_msg_pub.count_and_get (p_encoded => fnd_api.g_false,
293 p_count => x_msg_count,
294 p_data => x_msg_data
295 );
296 WHEN OTHERS THEN
297 ROLLBACK TO amw_risk_type_revise;
298 x_return_status := fnd_api.g_ret_sts_unexp_error;
299 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error) THEN
300 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
301 END IF;
302 -- Standard call to get message count and if count=1, get the message
303 fnd_msg_pub.count_and_get (p_encoded => fnd_api.g_false,
304 p_count => x_msg_count,
305 p_data => x_msg_data
306 );
307
308 END Revise_Risk_Type;
309
310
311 END amw_risk_type_pvt;
[Home] [Help]