[Home] [Help]
PACKAGE BODY: APPS.AHL_OSP_SERV_ITEM_RELS_PVT
Source
1 PACKAGE BODY AHL_OSP_SERV_ITEM_RELS_PVT AS
2 /* $Header: AHLVOSRB.pls 120.6 2006/07/26 06:21:27 mpothuku noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AHL_OSP_SERV_ITEM_RELS_PVT';
5 G_APP_NAME CONSTANT VARCHAR2(3) := 'AHL';
6
7
8 /*#
9 * This package Contains Record type and private procedures to process Service Item relationship with Inv Item.
10 * @rep:scope public
11 * @rep:product AHL
12 * @rep:displayname Process OSP Inv Itm Service Itm Relations
13 * @rep:lifecycle active
14 * @rep:category BUSINESS_ENTITY AHL_OSP_ORDER
15 */
16
17
18 ------------------------
19 -- Declare Procedures --
20 ------------------------
21
22 -- Start of Comments --
23 -- Procedure name : PROCESS_SERV_ITM_RELS
24 -- Type : Public
25 -- Function : For creating/updating relationship between Inv Item and Service Item.
26 -- Pre-reqs :
27 -- Parameters :
28 --
29 -- Standard IN Parameters :
30 -- p_api_version IN NUMBER Default 1.0
31 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_TRUE
32 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
33 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
34 -- Standard OUT Parameters :
35 -- x_return_status OUT NOCOPY VARCHAR2 Required
36 -- x_msg_count OUT NOCOPY NUMBER Required
37 -- x_msg_data OUT NOCOPY VARCHAR2 Required
38 --
39 -- Process Order Parameters:
40 -- p_x_Inv_serv_item_rec IN OUT NOCOPY Inv_Serv_Item_Rels_Rec_Type Required
41 -- All parameters for Inv Item Service Item relationship
42 --
43 --
44 -- Version :
45 -- Initial Version 1.0
46 --
47 -- End of Comments.
48 /*#
49 * This procedure is used to process a Shipment order related to an OSP Order.
50 * @param p_api_version API Version Number
51 * @param p_init_msg_list Initialize the message stack, default value FND_API.G_TRUE
52 * @param p_commit to decide whether to commit the transaction or not, default value FND_API.G_FALSE
53 * @param p_validation_level validation level, default value FND_API.G_VALID_LEVEL_FULL
54 * @param p_module_type Module type of the caller
55 * @param p_x_header_rec Contains the attributes of the Shipment header, of type AHL_OSP_SHIPMENT_PUB.Ship_Header_Rec_Type
56 * @param x_return_status return status
57 * @param x_msg_count return message count
58 * @param x_msg_data return message data
59 * @rep:scope public
60 * @rep:lifecycle active
61 * @rep:displayname Process Inv Item Service Item Relations
62 */
63 PROCEDURE PROCESS_SERV_ITM_RELS (
64 p_api_version IN NUMBER := 1.0,
65 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
66 p_commit IN VARCHAR2 := FND_API.G_FALSE,
67 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
68 p_module_type IN VARCHAR2 := NULL,
69 p_x_Inv_serv_item_rec IN OUT NOCOPY AHL_OSP_SERV_ITEM_RELS_PVT.Inv_Serv_Item_Rels_Rec_Type,
70 x_return_status OUT NOCOPY VARCHAR2,
71 x_msg_count OUT NOCOPY NUMBER,
72 x_msg_data OUT NOCOPY VARCHAR2)
73 IS
74 l_api_name CONSTANT VARCHAR2(30) := 'process_serv_itm_rels';
75 l_api_version CONSTANT NUMBER := 1.0;
76 L_DEBUG_KEY CONSTANT VARCHAR2(150) := 'ahl.plsql.ahl_osp_serv_item_rels_pvt.process_serv_itm_rels';
77
78
79 CURSOR get_Item_info_csr(p_org_name VARCHAR,
80 p_item_name VARCHAR,
81 p_item_flag VARCHAR)
82 IS
83 /*
84 SELECT
85 inventory_item_id ,
86 inventory_org_id
87 FROM ahl_mtl_items_ou_v
88 WHERE organization_name = p_org_name
89 AND concatenated_segments = p_item_name
90 AND inventory_item_flag = p_item_flag
91 AND NVL(start_date_active, sysdate) <= sysdate
92 AND NVL(end_date_active, sysdate + 1) > sysdate
93 AND DECODE(p_item_flag,'N',purchasing_enabled_flag,'Y') = 'Y'
94 AND DECODE(p_item_flag,'N',NVL(outside_operation_flag, 'N'),'N') = 'N';
95 */
96 --Changed by mpothuku for fixing the Performance Bug# 4919315
97
98 SELECT
99 mtl.inventory_item_id,
100 mtl.organization_id inventory_org_id
101 FROM mtl_system_items_kfv mtl, inv_organization_info_v org, hr_all_organization_units hou
102 WHERE hou.name = p_org_name
103 AND mtl.concatenated_segments = p_item_name
104 AND mtl.organization_id = org.organization_id
105 AND hou.organization_id = org.organization_id
106 AND NVL (org.operating_unit, mo_global.get_current_org_id ()) = mo_global.get_current_org_id()
107 AND mtl.inventory_item_flag = p_item_flag
108 AND NVL(mtl.start_date_active, sysdate) <= sysdate
109 AND NVL(mtl.end_date_active, sysdate + 1) > sysdate
110 AND DECODE(p_item_flag,'N',mtl.purchasing_enabled_flag,'Y') = 'Y'
111 AND DECODE(p_item_flag,'N',NVL(mtl.outside_operation_flag, 'N'),'N') = 'N';
112
113 CURSOR get_Item_info_id_csr(p_org_id VARCHAR,
114 p_item_id VARCHAR,
115 p_item_flag VARCHAR)
116 IS
117 /*
118 SELECT
119 inventory_item_id ,
120 inventory_org_id
121 FROM ahl_mtl_items_ou_v
122 WHERE inventory_org_id = p_org_id
123 AND inventory_item_id = p_item_id
124 AND inventory_item_flag = p_item_flag
125 AND NVL(start_date_active, sysdate) <= sysdate
126 AND NVL(end_date_active, sysdate + 1) > sysdate
127 AND DECODE(p_item_flag,'N',purchasing_enabled_flag,'Y') = 'Y'
128 AND DECODE(p_item_flag,'N',NVL(outside_operation_flag, 'N'),'N') = 'N';
129 */
130 --Changed by mpothuku for fixing the Performance Bug# 4919315
131 SELECT
132 mtl.inventory_item_id,
133 mtl.organization_id inventory_org_id
134 FROM mtl_system_items_b mtl, inv_organization_info_v org
135 WHERE mtl.organization_id = p_org_id
136 AND mtl.inventory_item_id = p_item_id
137 AND mtl.organization_id = org.organization_id
138 AND NVL (org.operating_unit, mo_global.get_current_org_id ()) = mo_global.get_current_org_id()
139 AND mtl.inventory_item_flag = p_item_flag
140 AND NVL(mtl.start_date_active, sysdate) <= sysdate
141 AND NVL(mtl.end_date_active, sysdate + 1) > sysdate
142 AND DECODE(p_item_flag,'N',mtl.purchasing_enabled_flag,'Y') = 'Y'
143 AND DECODE(p_item_flag,'N',NVL(mtl.outside_operation_flag, 'N'),'N') = 'N';
144
145 CURSOR Item_Ser_rel_det_csr(p_ser_item_rel_id NUMBER)
146 IS
147 SELECT
148 inv_service_item_rel_id ,
149 object_version_number ,
150 inv_item_id ,
151 inv_org_id ,
152 service_item_id ,
153 rank ,
154 active_start_date ,
155 active_end_date
156 FROM ahl_inv_service_item_rels
157 WHERE inv_service_item_rel_id = p_ser_item_rel_id
158 FOR UPDATE;
159
160
161 CURSOR Item_Ser_rel_exists_csr(P_inv_org_id NUMBER,p_inv_item_id NUMBER,p_service_item_id NUMBER)
162 IS
163 SELECT 'X'
164 FROM ahl_inv_service_item_rels
165 WHERE inv_org_id = p_inv_org_id
166 AND inv_item_id = p_inv_item_id
167 AND service_item_id = p_service_item_id;
168
169
170 CURSOR Item_rank_exists_csr(P_inv_org_id NUMBER,p_inv_item_id NUMBER,p_rank NUMBER,p_serv_rel_id NUMBER)
171 IS
172 SELECT 'X'
173 FROM ahl_inv_service_item_rels
174 WHERE inv_org_id = p_inv_org_id
175 AND inv_item_id = p_inv_item_id
176 AND rank = p_rank
177 AND INV_SERVICE_ITEM_REL_ID <> NVL(p_serv_rel_id,-99);
178
179 CURSOR org_item_rank_exists_csr(p_inv_item_id NUMBER, p_service_item_id NUMBER, p_rank NUMBER) IS
180 SELECT
181 isirv.inv_org_id,
182 isirv.inv_org_name,
183 isirv.inv_item_number,
184 isirv.service_item_number,
185 isirv.rank
186 /* Removed the OU filtering here, as the view ahl_inv_service_item_rels_v itself is OU filtered with the Bug
187 fix 5350882, mpothuku 26-Jul-06 */
188 FROM ahl_inv_service_item_rels_v isirv,
189 mtl_system_items_b mtl
190 /*
191 inv_organization_info_v org
192 */
193 WHERE isirv.inv_item_id = p_inv_item_id
194 AND isirv.service_item_id <> p_service_item_id
195 /*
196 If p_service_item_id is not assigned to the Org thats being considered
197 there is no need to throw the validation, the creation simply escapes it anyway if the
198 service item does not belong to the Org thats being considered
199 */
200 AND mtl.inventory_item_id = p_service_item_id
201 AND mtl.organization_id = isirv.inv_org_id
202 AND isirv.rank = p_rank;
203 /* Fix for the Bug# 5167378 by mpothuku on 17-Apr-06 */
204 /*
205 AND NVL(org.operating_unit, mo_global.get_current_org_id())= mo_global.get_current_org_id();
206 */
207
208 l_Item_Ser_rel_det Item_Ser_rel_det_csr%ROWTYPE;
209 l_org_item_rank_exists_csr org_item_rank_exists_csr%ROWTYPE;
210 l_dummy VARCHAR(1);
211 l_count NUMBER;
212
213 BEGIN
214
215
216
217 -- Standard start of API savepoint
218 SAVEPOINT process_serv_items_pvt;
219
220 -- Initialize API return status to success
221 x_return_status := FND_API.G_RET_STS_SUCCESS;
222
223 -- Standard call to check for call compatibility
224 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
225 G_PKG_NAME) THEN
226 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
227 END IF;
228
229 -- Initialize message list if p_init_msg_list is set to TRUE
230 IF FND_API.To_Boolean(p_init_msg_list) THEN
231 FND_MSG_PUB.Initialize;
232 END IF;
233
234 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
235 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
236 END IF;
237
238 IF p_x_Inv_serv_item_rec.operation_flag IS NULL THEN
239 FND_MESSAGE.set_name('AHL', 'AHL_OSP_OPER_FLAG_NULL');
240 FND_MSG_PUB.ADD;
241 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
242 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED, L_DEBUG_KEY, FALSE);
243 END IF;
244 RAISE Fnd_Api.g_exc_error;
245 END IF;
246
247 IF p_x_Inv_serv_item_rec.operation_flag = 'C' THEN
248
249 IF (p_x_Inv_serv_item_rec.inv_org_id IS NULL OR
250 p_x_Inv_serv_item_rec.inv_org_id = FND_API.G_MISS_NUM) AND
251 (p_x_Inv_serv_item_rec.inv_org_name IS NULL OR
252 p_x_Inv_serv_item_rec.inv_org_name = FND_API.G_MISS_CHAR) THEN
253 FND_MESSAGE.set_name('AHL', 'AHL_OSP_ORG_NAME_NULL');
254 FND_MSG_PUB.ADD;
255 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
259
256 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED, L_DEBUG_KEY, FALSE);
257 END IF;
258 END IF;
260 IF (p_x_Inv_serv_item_rec.inv_item_id IS NULL OR
261 p_x_Inv_serv_item_rec.inv_item_id = FND_API.G_MISS_NUM) AND
262 (p_x_Inv_serv_item_rec.inv_item_name IS NULL OR
263 p_x_Inv_serv_item_rec.inv_item_name = FND_API.G_MISS_CHAR) THEN
264 FND_MESSAGE.set_name('AHL', 'AHL_OSP_INV_ITEM_NULL');
265 FND_MSG_PUB.ADD;
266 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
267 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED, L_DEBUG_KEY, FALSE);
268 END IF;
269 END IF;
270
271 IF (p_x_Inv_serv_item_rec.service_item_id IS NULL OR
272 p_x_Inv_serv_item_rec.service_item_id = FND_API.G_MISS_NUM) AND
273 (p_x_Inv_serv_item_rec.service_item_name IS NULL OR
274 p_x_Inv_serv_item_rec.service_item_name = FND_API.G_MISS_CHAR) THEN
275 FND_MESSAGE.set_name('AHL', 'AHL_OSP_SER_ITEM_NULL');
276 FND_MSG_PUB.ADD;
277 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
278 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED, L_DEBUG_KEY, FALSE);
279 END IF;
280 END IF;
281
282 IF p_x_Inv_serv_item_rec.inv_org_name IS NOT NULL
283 AND p_x_Inv_serv_item_rec.inv_item_name IS NOT NULL THEN
284
285 OPEN get_Item_info_csr(p_x_Inv_serv_item_rec.inv_org_name,
286 p_x_Inv_serv_item_rec.inv_item_name,'Y');
287 FETCH get_Item_info_csr INTO p_x_Inv_serv_item_rec.inv_item_id,
288 p_x_Inv_serv_item_rec.inv_org_id;
289 IF get_Item_info_csr%NOTFOUND THEN
290 FND_MESSAGE.set_name('AHL', 'AHL_OSP_INVALID_INV_ITEM');
291 FND_MSG_PUB.ADD;
292 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
293 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED, L_DEBUG_KEY, FALSE);
294 END IF;
295 CLOSE get_Item_info_csr;
296 RAISE Fnd_Api.g_exc_error;
297 END IF;
298 CLOSE get_Item_info_csr;
299 ELSE
300 OPEN get_Item_info_id_csr(p_x_Inv_serv_item_rec.inv_org_id,
301 p_x_Inv_serv_item_rec.inv_item_id,'Y');
302 FETCH get_Item_info_id_csr INTO p_x_Inv_serv_item_rec.inv_item_id,
303 p_x_Inv_serv_item_rec.inv_org_id;
304 IF get_Item_info_id_csr%NOTFOUND THEN
305 FND_MESSAGE.set_name('AHL', 'AHL_OSP_INVALID_INV_ITEM');
306 FND_MSG_PUB.ADD;
307 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
308 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED, L_DEBUG_KEY, FALSE);
309 END IF;
310 CLOSE get_Item_info_id_csr;
311 RAISE Fnd_Api.g_exc_error;
312 END IF;
313 CLOSE get_Item_info_id_csr;
314
315 END IF;
316
317 IF TRUNC(p_x_Inv_serv_item_rec.active_start_date) < TRUNC(SYSDATE) THEN
318 FND_MESSAGE.set_name('AHL', 'AHL_VENDOR_START_DATE_PAST');
319 FND_MSG_PUB.ADD;
320 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
321 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED, L_DEBUG_KEY, FALSE);
322 END IF;
323 END IF;
324
325 IF p_x_Inv_serv_item_rec.service_item_name IS NOT NULL
326 AND p_x_Inv_serv_item_rec.inv_item_name IS NOT NULL THEN
327
328 OPEN get_Item_info_csr(p_x_Inv_serv_item_rec.inv_org_name,
329 p_x_Inv_serv_item_rec.service_item_name,'N');
330 FETCH get_Item_info_csr INTO p_x_Inv_serv_item_rec.service_item_id,
331 p_x_Inv_serv_item_rec.inv_org_id;
332 IF get_Item_info_csr%NOTFOUND THEN
333 FND_MESSAGE.set_name('AHL', 'AHL_OSP_INVALID_SERV_ITEM');
334 FND_MSG_PUB.ADD;
335 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
336 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED, L_DEBUG_KEY, FALSE);
337 END IF;
338 CLOSE get_Item_info_csr;
339 RAISE Fnd_Api.g_exc_error;
340 END IF;
341 CLOSE get_Item_info_csr;
342 ELSE
343 OPEN get_Item_info_id_csr(p_x_Inv_serv_item_rec.inv_org_id,
344 p_x_Inv_serv_item_rec.service_item_id,'N');
345 FETCH get_Item_info_id_csr INTO p_x_Inv_serv_item_rec.service_item_id,
346 p_x_Inv_serv_item_rec.inv_org_id;
347 IF get_Item_info_id_csr%NOTFOUND THEN
348 FND_MESSAGE.set_name('AHL', 'AHL_OSP_INVALID_SERV_ITEM');
349 FND_MSG_PUB.ADD;
350 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
351 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED, L_DEBUG_KEY, FALSE);
352 END IF;
353 CLOSE get_Item_info_id_csr;
354 RAISE Fnd_Api.g_exc_error;
355 END IF;
356 CLOSE get_Item_info_id_csr;
357
358 END IF;
359
360
361 OPEN Item_Ser_rel_exists_csr(p_x_Inv_serv_item_rec.inv_org_id,
362 p_x_Inv_serv_item_rec.inv_item_id,
363 p_x_Inv_serv_item_rec.service_item_id);
364 FETCH Item_Ser_rel_exists_csr INTO l_dummy;
365 IF Item_Ser_rel_exists_csr%FOUND THEN
366 FND_MESSAGE.set_name('AHL', 'AHL_OSP_RELATION_EXISTS');
367 FND_MSG_PUB.ADD;
368 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
369 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED, L_DEBUG_KEY, FALSE);
370 END IF;
371 CLOSE Item_Ser_rel_exists_csr;
372 RAISE Fnd_Api.g_exc_error;
373 END IF;
374 CLOSE Item_Ser_rel_exists_csr;
375
376
377 END IF; -- Operations flag = C
378
379
380
384 p_x_Inv_serv_item_rec.rank = FND_API.G_MISS_NUM) THEN
381 IF p_x_Inv_serv_item_rec.operation_flag <> 'D' THEN
382
383 IF (p_x_Inv_serv_item_rec.rank IS NULL OR
385 FND_MESSAGE.set_name('AHL', 'AHL_OSP_RANK_NULL');
386 FND_MSG_PUB.ADD;
387 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
388 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED, L_DEBUG_KEY, FALSE);
389 END IF;
390 RAISE Fnd_Api.g_exc_error;
391 ELSIF ( p_x_Inv_serv_item_rec.rank < 1 OR
392 FLOOR(p_x_Inv_serv_item_rec.rank) <> p_x_Inv_serv_item_rec.rank) THEN
393 FND_MESSAGE.set_name('AHL', 'AHL_OSP_RANK_INVALID_NUM');
394 FND_MSG_PUB.ADD;
395 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
396 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED, L_DEBUG_KEY, FALSE);
397 END IF;
398 RAISE Fnd_Api.g_exc_error;
399 END IF;
400
401
402 IF (p_x_Inv_serv_item_rec.active_start_date IS NULL OR
403 p_x_Inv_serv_item_rec.active_start_date = FND_API.G_MISS_DATE) THEN
404 FND_MESSAGE.set_name('AHL', 'AHL_OSP_START_DATE_NULL');
405 FND_MSG_PUB.ADD;
406 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
407 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED, L_DEBUG_KEY, FALSE);
408 END IF;
409 ELSIF p_x_Inv_serv_item_rec.active_start_date > NVL(p_x_Inv_serv_item_rec.active_end_date,
410 p_x_Inv_serv_item_rec.active_start_date) THEN
411 FND_MESSAGE.set_name('AHL', 'AHL_OSP_START_DATE_GT');
412 FND_MSG_PUB.ADD;
413 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
414 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED, L_DEBUG_KEY, FALSE);
415 END IF;
416 END IF;
417
418 IF p_x_Inv_serv_item_rec.operation_flag = 'C' THEN
419
420 OPEN Item_rank_exists_csr(p_x_Inv_serv_item_rec.inv_org_id,
421 p_x_Inv_serv_item_rec.inv_item_id,
422 p_x_Inv_serv_item_rec.rank,
423 NULL);
424 ELSE
425 OPEN Item_rank_exists_csr(p_x_Inv_serv_item_rec.inv_org_id,
426 p_x_Inv_serv_item_rec.inv_item_id,
427 p_x_Inv_serv_item_rec.rank,
428 p_x_Inv_serv_item_rec.inv_ser_item_rel_id);
429 END IF;
430 FETCH Item_rank_exists_csr INTO l_dummy;
431 IF Item_rank_exists_csr%FOUND THEN
432 FND_MESSAGE.set_name('AHL', 'AHL_OSP_RANK_DUP');
433 FND_MSG_PUB.ADD;
434 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
435 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED, L_DEBUG_KEY, FALSE);
436 END IF;
437 CLOSE Item_rank_exists_csr;
438 RAISE Fnd_Api.g_exc_error;
439 END IF;
440 CLOSE Item_rank_exists_csr;
441
442
443 END IF;-- Operations flag <> D
444
445
446 IF p_x_Inv_serv_item_rec.operation_flag IN ('D','U') THEN
447
448 IF p_x_Inv_serv_item_rec.inv_ser_item_rel_id IS NULL THEN
449 FND_MESSAGE.set_name('AHL', 'AHL_OSP_REL_ID_NULL');
450 FND_MSG_PUB.ADD;
451 RAISE Fnd_Api.g_exc_error;
452 END IF;
453
454 OPEN Item_Ser_rel_det_csr(p_x_Inv_serv_item_rec.inv_ser_item_rel_id);
455 FETCH Item_Ser_rel_det_csr INTO l_Item_Ser_rel_det;
456 IF Item_Ser_rel_det_csr%NOTFOUND THEN
457 FND_MESSAGE.set_name('AHL', 'AHL_OSP_REL_NOTFOUND');
458 FND_MSG_PUB.ADD;
459 CLOSE Item_Ser_rel_det_csr;
460 RAISE Fnd_Api.g_exc_error;
461 END IF;
462 CLOSE Item_Ser_rel_det_csr;
463
464 IF NVL(l_Item_Ser_rel_det.object_version_number,-9) <> p_x_Inv_serv_item_rec.obj_ver_num THEN
465 FND_MESSAGE.set_name('AHL', 'AHL_COM_RECORD_MODIFIED');
466 FND_MSG_PUB.ADD;
467 RAISE Fnd_Api.g_exc_error;
468 END IF;
469
470 END IF;
471
472 -- Check Error Message stack.
473 x_msg_count := FND_MSG_PUB.count_msg;
474 IF x_msg_count > 0 THEN
475 RAISE FND_API.G_EXC_ERROR;
476 END IF;
477
478 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
479 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'p_x_Inv_serv_item_rec.operation_flag: ' || p_x_Inv_serv_item_rec.operation_flag );
480 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'p_x_Inv_serv_item_rec.for_all_org_flag: ' || p_x_Inv_serv_item_rec.for_all_org_flag );
481 END IF;
482
483 IF p_x_Inv_serv_item_rec.operation_flag = 'C' THEN
484 IF p_x_Inv_serv_item_rec.for_all_org_flag = 'Y' THEN
485
486 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
487 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Inserting Relationships across Multiple Orgs');
488 END IF;
489 /* Commented by mpothuku on 25-jul-2005 to create the relationships in the Orgs where the relationship is
490 not already defined. The relationship if already existing is left intact.
491 */
492 /*
493 DELETE FROM AHL_INV_SERVICE_ITEM_RELS
494 WHERE INV_ITEM_ID = p_x_Inv_serv_item_rec.inv_item_id
495 AND SERVICE_ITEM_ID = p_x_Inv_serv_item_rec.service_item_id;
496 */
497
498 --Validation to check if there already exists a service item with the same Rank for the given inventory
499 --item in any of the orgs in which the relationship is being created.
500 l_count := 0;
501 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
502 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Before Validation for Org, Inventory, Rank Check');
503 END IF;
507 FETCH org_item_rank_exists_csr INTO l_org_item_rank_exists_csr;
504
505 OPEN org_item_rank_exists_csr(p_x_Inv_serv_item_rec.inv_item_id, p_x_Inv_serv_item_rec.service_item_id, p_x_Inv_serv_item_rec.rank);
506 LOOP
508 EXIT WHEN org_item_rank_exists_csr%NOTFOUND;
509 FND_MESSAGE.set_name('AHL', 'AHL_OSP_INV_RANK_EXISTS');
510 FND_MESSAGE.set_token('INV_ORG_NAME', l_org_item_rank_exists_csr.inv_org_name);
511 FND_MESSAGE.set_token('INV_ITEM_NUMBER', l_org_item_rank_exists_csr.inv_item_number);
512 FND_MESSAGE.set_token('SVC_ITEM_NUMBER', l_org_item_rank_exists_csr.service_item_number);
513 FND_MESSAGE.set_token('RANK', l_org_item_rank_exists_csr.rank);
514 FND_MSG_PUB.ADD;
515 l_count := l_count + 1;
516 END LOOP;
517 CLOSE org_item_rank_exists_csr;
518
519 IF(l_count > 0) THEN
520 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
521 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Validation for Org, Inventory, Rank Check failed');
522 END IF;
523 RAISE Fnd_Api.g_exc_error;
524 END IF;
525
526 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
527 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Validation for Org, Inventory, Rank Check sucessful');
528 END IF;
529
530
531 INSERT INTO AHL_INV_SERVICE_ITEM_RELS
532 (INV_SERVICE_ITEM_REL_ID,
533 OBJECT_VERSION_NUMBER,
534 LAST_UPDATE_DATE ,
535 LAST_UPDATED_BY ,
536 CREATION_DATE ,
537 CREATED_BY ,
538 LAST_UPDATE_LOGIN ,
539 INV_ITEM_ID ,
540 INV_ORG_ID ,
541 SERVICE_ITEM_ID ,
542 RANK ,
543 ACTIVE_START_DATE ,
544 ACTIVE_END_DATE ,
545 SECURITY_GROUP_ID ,
546 ATTRIBUTE_CATEGORY ,
547 ATTRIBUTE1 ,
548 ATTRIBUTE2 ,
549 ATTRIBUTE3 ,
550 ATTRIBUTE4 ,
551 ATTRIBUTE5 ,
552 ATTRIBUTE6 ,
553 ATTRIBUTE7 ,
554 ATTRIBUTE8 ,
555 ATTRIBUTE9 ,
556 ATTRIBUTE10 ,
557 ATTRIBUTE11 ,
558 ATTRIBUTE12 ,
559 ATTRIBUTE13 ,
560 ATTRIBUTE14 ,
561 ATTRIBUTE15 )
562 SELECT AHL_INV_SERVICE_ITEM_RELS_S.NEXTVAL,
563 1,
564 sysdate,
565 Fnd_Global.USER_ID,
566 sysdate,
567 Fnd_Global.USER_ID,
568 Fnd_Global.LOGIN_ID,
569 Inv.INVENTORY_ITEM_ID,
570 Inv.ORGANIZATION_ID,
571 Serv.INVENTORY_ITEM_ID,
572 p_x_Inv_serv_item_rec.rank,
573 p_x_Inv_serv_item_rec.active_start_date,
574 p_x_Inv_serv_item_rec.active_end_date,
575 NULL ,
576 NULL ,
577 NULL ,
578 NULL ,
579 NULL ,
580 NULL ,
581 NULL ,
582 NULL ,
583 NULL ,
584 NULL ,
585 NULL ,
586 NULL ,
587 NULL ,
588 NULL ,
589 NULL ,
590 NULL ,
591 NULL
592 FROM mtl_system_items_kfv Inv,
593 mtl_system_items_kfv Serv,
594 --Modified by mpothuku on 17-Jan-05 to fix the performance Bug 4919315
595 inv_organization_info_v org
596 WHERE Inv.ORGANIZATION_ID = Serv.ORGANIZATION_ID
597 AND org.organization_id = Inv.ORGANIZATION_ID
598 AND Inv.inventory_item_flag = 'Y'
599 AND Serv.inventory_item_flag = 'N'
600 AND Inv.inventory_item_id = p_x_Inv_serv_item_rec.inv_item_id
601 AND Serv.inventory_item_id = p_x_Inv_serv_item_rec.service_item_id
602 -- mpothuku start
603 AND org.organization_id not in
604 (select INV_ORG_ID from AHL_INV_SERVICE_ITEM_RELS
605 where inv_item_id = p_x_Inv_serv_item_rec.inv_item_id
606 AND service_item_id = p_x_Inv_serv_item_rec.service_item_id)
607 -- mpothuku end
608 AND NVL(org.operating_unit, mo_global.get_current_org_id()) =
609 mo_global.get_current_org_id();
610
611 ELSE
612
613 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
614 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Inserting Relationship in Single Org');
615 END IF;
616
617 INSERT INTO AHL_INV_SERVICE_ITEM_RELS
618 (INV_SERVICE_ITEM_REL_ID,
619 OBJECT_VERSION_NUMBER,
620 LAST_UPDATE_DATE ,
621 LAST_UPDATED_BY ,
622 CREATION_DATE ,
623 CREATED_BY ,
624 LAST_UPDATE_LOGIN ,
625 INV_ITEM_ID ,
626 INV_ORG_ID ,
627 SERVICE_ITEM_ID ,
628 RANK ,
629 ACTIVE_START_DATE ,
630 ACTIVE_END_DATE ,
631 SECURITY_GROUP_ID ,
632 ATTRIBUTE_CATEGORY ,
633 ATTRIBUTE1 ,
634 ATTRIBUTE2 ,
635 ATTRIBUTE3 ,
636 ATTRIBUTE4 ,
637 ATTRIBUTE5 ,
638 ATTRIBUTE6 ,
639 ATTRIBUTE7 ,
643 ATTRIBUTE11 ,
640 ATTRIBUTE8 ,
641 ATTRIBUTE9 ,
642 ATTRIBUTE10 ,
644 ATTRIBUTE12 ,
645 ATTRIBUTE13 ,
646 ATTRIBUTE14 ,
647 ATTRIBUTE15 )
648 VALUES
649 (AHL_INV_SERVICE_ITEM_RELS_S.NEXTVAL,
650 1,
651 sysdate,
652 Fnd_Global.USER_ID,
653 sysdate,
654 Fnd_Global.USER_ID,
655 Fnd_Global.LOGIN_ID,
656 p_x_Inv_serv_item_rec.inv_item_id,
657 p_x_Inv_serv_item_rec.inv_org_id,
658 p_x_Inv_serv_item_rec.service_item_id,
659 p_x_Inv_serv_item_rec.rank,
660 p_x_Inv_serv_item_rec.active_start_date,
661 p_x_Inv_serv_item_rec.active_end_date,
662 NULL ,
663 NULL ,
664 NULL ,
665 NULL ,
666 NULL ,
667 NULL ,
668 NULL ,
669 NULL ,
670 NULL ,
671 NULL ,
672 NULL ,
673 NULL ,
674 NULL ,
675 NULL ,
676 NULL ,
677 NULL ,
678 NULL ) RETURN INV_SERVICE_ITEM_REL_ID INTO p_x_Inv_serv_item_rec.inv_ser_item_rel_id;
679
680 END IF; -- for all org
681
682 ELSIF p_x_Inv_serv_item_rec.operation_flag = 'U' THEN
683
684 --Validate that end_date is not in past during updation of the record
685 --Added by mpothuku on 25-Aug-05 to fix the bug #4552227
686 IF TRUNC(p_x_Inv_serv_item_rec.active_end_date) < TRUNC(SYSDATE) THEN
687 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
688 fnd_log.string(fnd_log.level_statement, L_DEBUG_KEY, 'End Date is in Past');
689 END IF;
690 FND_MESSAGE.set_name('AHL', 'AHL_VENDOR_CER_END_DATE_PAST');
691 FND_MSG_PUB.ADD;
692 RAISE Fnd_Api.g_exc_error;
693 END IF;
694 -- mpothuku End
695
696 UPDATE ahl_inv_service_item_rels
697 SET active_start_date = p_x_inv_serv_item_rec.active_start_date,
698 active_end_date = p_x_inv_serv_item_rec.active_end_date,
699 rank = p_x_inv_serv_item_rec.rank,
700 object_version_number = object_version_number +1
701 WHERE inv_service_item_rel_id = p_x_Inv_serv_item_rec.inv_ser_item_rel_id
702 AND object_version_number = l_Item_Ser_rel_det.object_version_number;
703
704 IF SQL%ROWCOUNT = 0 THEN
705 FND_MESSAGE.set_name('AHL', 'AHL_COM_RECORD_MODIFIED');
706 FND_MSG_PUB.ADD;
707 RAISE Fnd_Api.g_exc_error;
708 END IF;
709
710
711 ELSIF p_x_Inv_serv_item_rec.operation_flag = 'D' THEN
712
713 DELETE FROM ahl_inv_service_item_rels
714 WHERE inv_service_item_rel_id = p_x_Inv_serv_item_rec.inv_ser_item_rel_id
715 AND object_version_number = l_Item_Ser_rel_det.object_version_number;
716
717 IF SQL%ROWCOUNT = 0 THEN
718 FND_MESSAGE.set_name('AHL', 'AHL_COM_RECORD_MODIFIED');
719 FND_MSG_PUB.ADD;
720 RAISE Fnd_Api.g_exc_error;
721 END IF;
722
723 DELETE FROM ahl_item_vendor_rels
724 WHERE inv_service_item_rel_id = p_x_Inv_serv_item_rec.inv_ser_item_rel_id;
725
726
727 END IF; -- operation flag C
728
729
730 -- Standard check of p_commit
731 IF FND_API.To_Boolean(p_commit) THEN
732 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
733 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'About to commit work');
734 END IF;
735 COMMIT WORK;
736 END IF;
737
738
739 -- Standard call to get message count and if count is 1, get message
740 FND_MSG_PUB.Count_And_Get
741 ( p_count => x_msg_count,
742 p_data => x_msg_data,
743 p_encoded => fnd_api.g_false);
744 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
745 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
746 END IF;
747
748 EXCEPTION
749 WHEN FND_API.G_EXC_ERROR THEN
750 x_return_status := FND_API.G_RET_STS_ERROR;
751 Rollback to process_serv_items_pvt;
752 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
753 p_data => x_msg_data,
754 p_encoded => fnd_api.g_false);
755
756 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
757 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
758 Rollback to process_serv_items_pvt;
759 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
760 p_data => x_msg_data,
761 p_encoded => fnd_api.g_false);
762 WHEN OTHERS THEN
763 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
764 Rollback to process_serv_items_pvt;
765 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
766 p_procedure_name => l_api_name,
767 p_error_text => SQLERRM);
768
769 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
770 p_data => x_msg_data,
771 p_encoded => fnd_api.g_false);
772
773 END PROCESS_SERV_ITM_RELS;
774
775
776
777
778 End AHL_OSP_SERV_ITEM_RELS_PVT;