1 PACKAGE BODY AHL_UMP_SR_GRP AS
2 /* $Header: AHLGUSRB.pls 115.0 2003/09/17 22:40:58 jaramana noship $ */
3
4 -----------------------
5 -- Declare Constants --
6 -----------------------
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AHL_UMP_SR_GRP';
8
9 G_LOG_PREFIX CONSTANT VARCHAR2(100) := 'ahl.plsql.AHL_UMP_SR_GRP';
10
11 -----------------------------------------
12 -- Public Procedure Definitions follow --
13 -----------------------------------------
14 -- Start of Comments --
15 -- Procedure name : Create_SR_Unit_Effectivity
16 -- Type : Public
17 -- Function : Group Hook API to create a SR type unit effectivity.
18 -- Called by the CMRO type Service Request as Post Insert Internal hook.
19 -- Uses CS_SERVICEREQUEST_PVT.USER_HOOKS_REC to get SR Details.
20 -- Pre-reqs :
21 -- Parameters :
22 --
23 -- x_return_status OUT VARCHAR2 Required
24 --
25 -- Version :
26 -- Initial Version 1.0
27 --
28 -- End of Comments.
29
30 PROCEDURE Create_SR_Unit_Effectivity
31 (
32 x_return_status OUT NOCOPY VARCHAR2) IS
33
34 l_api_name CONSTANT VARCHAR2(30) := 'Create_SR_Unit_Effectivity';
35 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Create_SR_Unit_Effectivity';
36
37 BEGIN
38
39 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
40 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
41 END IF;
42
43 -- Initialize API return status to success
44 x_return_status := FND_API.G_RET_STS_SUCCESS;
45
46 -- Begin Processing
47 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
48 FND_LOG.STRING(FND_LOG.LEVEL_EVENT, L_DEBUG_KEY, 'About to call AHL_UMP_SR_PVT.Create_SR_Unit_Effectivity');
49 END IF;
50
51 AHL_UMP_SR_PVT.Create_SR_Unit_Effectivity(
52 x_return_status => x_return_status
53 );
54
55 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
56 FND_LOG.STRING(FND_LOG.LEVEL_EVENT, L_DEBUG_KEY, 'Returned from call to AHL_UMP_SR_PVT.Create_SR_Unit_Effectivity. x_return_status = ' || x_return_status);
57 END IF;
58
59 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
60 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
61 END IF;
62
63 EXCEPTION
64 WHEN FND_API.G_EXC_ERROR THEN
65 x_return_status := FND_API.G_RET_STS_ERROR;
66
67 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
68 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
69
70 WHEN OTHERS THEN
71 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
72 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
73 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
74 p_procedure_name => 'Create_SR_Unit_Effectivity',
75 p_error_text => SUBSTR(SQLERRM,1,240));
76 END IF;
77
78 END Create_SR_Unit_Effectivity;
79
80 ----------------------------------------
81
82 -- Start of Comments --
83 -- Procedure name : Process_SR_Updates
84 -- Type : Public
85 -- Function : Group Hook API to process updates to a (current or former) CMRO type
86 -- SR by adding, removing or updating SR type unit effectivities.
87 -- Called by the Service Request as Post Update internal hook.
88 -- Uses CS_SERVICEREQUEST_PVT.USER_HOOKS_REC to get SR Details.
89 -- Pre-reqs :
90 -- Parameters :
91 --
92 -- x_return_status OUT VARCHAR2 Required
93 --
94 -- Version :
95 -- Initial Version 1.0
96 --
97 -- End of Comments.
98
99 PROCEDURE Process_SR_Updates
100 (
101 x_return_status OUT NOCOPY VARCHAR2) IS
102
103 l_api_name CONSTANT VARCHAR2(30) := 'Process_SR_Updates';
104 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Process_SR_Updates';
105
106 BEGIN
107 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
108 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
109 END IF;
110
111 -- Initialize API return status to success
112 x_return_status := FND_API.G_RET_STS_SUCCESS;
113
114 -- Begin Processing
115 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
116 FND_LOG.STRING(FND_LOG.LEVEL_EVENT, L_DEBUG_KEY, 'About to call AHL_UMP_SR_PVT.Process_SR_Updates');
117 END IF;
118
119 AHL_UMP_SR_PVT.Process_SR_Updates(
120 x_return_status => x_return_status
121 );
122
123 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
124 FND_LOG.STRING(FND_LOG.LEVEL_EVENT, L_DEBUG_KEY, 'Returned from call to AHL_UMP_SR_PVT.Process_SR_Updates. x_return_status = ' || x_return_status);
125 END IF;
126
127 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
128 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
129 END IF;
130
131 EXCEPTION
132 WHEN FND_API.G_EXC_ERROR THEN
133 x_return_status := FND_API.G_RET_STS_ERROR;
134
135 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
136 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
137
138 WHEN OTHERS THEN
139 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
140 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
141 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
142 p_procedure_name => 'Process_SR_Updates',
143 p_error_text => SUBSTR(SQLERRM,1,240));
144 END IF;
145
146 END Process_SR_Updates;
147
148 ----------------------------------------
149
150 -- Start of Comments --
151 -- Procedure name : Process_SR_MR_Associations
152 -- Type : Public
153 -- Function : Processes new and removed MR associations with a CMRO type SR.
154 -- This API will be called by the Service Request module whenever new MRs
155 -- are associated to or existing MRs are disassociated from a CMRO type SR.
156 -- .
157 -- Pre-reqs :
158 -- Parameters :
159 --
160 -- Standard IN Parameters :
161 -- p_api_version IN NUMBER Required
162 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
163 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
164 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
165 --
166 -- Standard OUT Parameters :
167 -- x_return_status OUT VARCHAR2 Required
168 -- x_msg_count OUT NUMBER Required
169 -- x_msg_data OUT VARCHAR2 Required
170 --
171 -- Process_SR_MR_Associations Parameters:
172 -- p_user_id IN NUMBER Required
173 -- The Id of the user calling this API
174 -- p_login_id IN NUMBER Required
175 -- The Login Id of the user calling this API
176 -- p_request_id IN NUMBER Required
177 -- The Id of the Service Request
178 -- p_object_version_number IN NUMBER Required
179 -- The object version number of the Service Request
180 -- p_request_number IN NUMBER Required
181 -- The request number of the Service Request
182 -- p_x_sr_mr_association_tbl IN OUT NOCOPY AHL_UMP_SR_PVT.SR_MR_Association_Tbl_Type Required
183 -- The Table of records containing the details about the associations and disassociations
184 --
185 -- Version :
186 -- Initial Version 1.0
187 --
188 -- End of Comments.
189
190 PROCEDURE Process_SR_MR_Associations
191 (
192 p_api_version IN NUMBER,
193 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
194 p_commit IN VARCHAR2 := FND_API.G_FALSE,
195 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
196 x_return_status OUT NOCOPY VARCHAR2,
197 x_msg_count OUT NOCOPY NUMBER,
198 x_msg_data OUT NOCOPY VARCHAR2,
199 p_user_id IN NUMBER,
200 p_login_id IN NUMBER,
201 p_request_id IN NUMBER,
202 p_object_version_number IN NUMBER,
203 p_request_number IN VARCHAR2,
204 p_x_sr_mr_association_tbl IN OUT NOCOPY AHL_UMP_SR_PVT.SR_MR_Association_Tbl_Type) IS
205
206 l_api_version CONSTANT NUMBER := 1.0;
207 l_api_name CONSTANT VARCHAR2(30) := 'Process_SR_MR_Associations';
208 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Process_SR_MR_Associations';
209
210 BEGIN
211 -- Standard start of API savepoint
212 SAVEPOINT Process_SR_MR_Associations_pvt;
213
214 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
215 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
216 END IF;
217
218 -- Standard call to check for call compatibility
219 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
220 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
221 END IF;
222
223 -- Initialize API return status to success
224 x_return_status := FND_API.G_RET_STS_SUCCESS;
225
226 -- Begin Processing
227 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
228 FND_LOG.STRING(FND_LOG.LEVEL_EVENT, L_DEBUG_KEY, 'About to call AHL_UMP_SR_PVT.Process_SR_MR_Associations');
229 END IF;
230
231 AHL_UMP_SR_PVT.Process_SR_MR_Associations(
232 p_api_version => p_api_version,
233 p_init_msg_list => p_init_msg_list,
234 p_commit => FND_API.G_FALSE,
235 p_validation_level => p_validation_level,
236 x_return_status => x_return_status,
237 x_msg_count => x_msg_count,
238 x_msg_data => x_msg_data,
239 p_user_id => p_user_id,
240 p_login_id => p_login_id,
241 p_request_id => p_request_id,
242 p_object_version_number => p_object_version_number,
243 p_request_number => p_request_number,
244 p_x_sr_mr_association_tbl => p_x_sr_mr_association_tbl
245 );
246
247 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
248 FND_LOG.STRING(FND_LOG.LEVEL_EVENT, L_DEBUG_KEY, 'Returned from call to AHL_UMP_SR_PVT.Process_SR_MR_Associations. x_return_status = ' || x_return_status);
249 END IF;
250
251 -- Standard check of p_commit
252 IF FND_API.TO_BOOLEAN(p_commit) THEN
253 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
254 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'About to Commit.');
255 END IF;
256 COMMIT WORK;
257 END IF;
258
259 -- Standard call to get message count and if count is 1, get message info
260 FND_MSG_PUB.Count_And_Get
261 ( p_count => x_msg_count,
262 p_data => x_msg_data,
263 p_encoded => fnd_api.g_false
264 );
265
266 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
267 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
268 END IF;
269
270 EXCEPTION
271 WHEN FND_API.G_EXC_ERROR THEN
272 ROLLBACK TO Process_SR_MR_Associations_pvt;
273 x_return_status := FND_API.G_RET_STS_ERROR;
274 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
275 p_data => x_msg_data,
276 p_encoded => fnd_api.g_false);
277 --AHL_UTIL_PKG.Err_Mesg_To_Table(x_err_mesg_tbl);
278
279 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
280 ROLLBACK TO Process_SR_MR_Associations_pvt;
281 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
282 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
283 p_data => x_msg_data,
284 p_encoded => fnd_api.g_false);
285 --AHL_UTIL_PKG.Err_Mesg_To_Table(x_err_mesg_tbl);
286
287 WHEN OTHERS THEN
288 ROLLBACK TO Process_SR_MR_Associations_pvt;
289 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
290 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
291 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
292 p_procedure_name => 'Process_SR_MR_Associations',
293 p_error_text => SUBSTR(SQLERRM,1,240));
294 END IF;
295 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
296 p_data => x_msg_data,
297 p_encoded => fnd_api.g_false);
298 --AHL_UTIL_PKG.Err_Mesg_To_Table(x_err_mesg_tbl);
299
300 END Process_SR_MR_Associations;
301
302 ----------------------------------------
303
304 --------------------------------------
305 -- End Public Procedure Definitions --
306 --------------------------------------
307
308 END AHL_UMP_SR_GRP;