[Home] [Help]
PACKAGE BODY: APPS.WIP_SCHED_RELATION_GRP
Source
1 PACKAGE BODY WIP_SCHED_RELATION_GRP AS
2 /* $Header: wipgwlkb.pls 115.3 2003/10/14 15:26:44 amgarg noship $ */
3 /*==========================================================================+
4 | Copyright (c) 1997 Oracle Corporation Redwood Shores, California, USA |
5 | All rights reserved. |
6 +===========================================================================+
7 | |
8 | File Name : wipgwlkb.pls |
9 | |
10 | DESCRIPTION : This package, is a Group API, which contains functions |
11 | to Create and Delete relationships for Work Order Scheduling.|
12 | |
13 | Coders : Amit Garg |
14 +===========================================================================*/
15
16
17 /************************************************************************
18 * PACKAGE VARIABLES *
19 ************************************************************************/
20
21
22 /******************************************************************************
23 * PROCEDURE INSERTROW *
24 * This procedure is used to validate AND create Relationships to be *
25 * inserted in WIP_SCHED_RELATIONSHIPS Table *
26 * The input parameters for this procedure are: *
27 * p_parentObjectID : Parent Object Idetifier *
28 * p_parentObjectTypeID : Parent Object type Idetifier *
29 * p_childObjectID : Child Object Idetifier *
30 * p_childObjectTypeID : Child Object type Idetifier *
31 * p_relationshipType : Type of relationship between parent and child *
32 * p_relationshipStatus : Relationship status, *
33 * pending : 0 *
34 * processing : 1 *
35 * valid : 2 *
36 * invalid : 3 *
37 * x_return_status : out parameter to indicate success, failure or *
38 * error for this procedure *
39 * x_msg_count : out parameter indicating number of messages in *
40 * msg list *
41 * x_msg_data : message in encoded form is returned *
42 * p_api_version : parameter indicating api version, to check for *
43 * valid API version *
44 * p_init_msg_list : Parameter to indicate whether public msg list *
45 * is required to be initialised *
46 * p_commit : Parameter to indicate if commit is required *
47 * by this proc *
48 ******************************************************************************/
49 PROCEDURE insertRow(p_parentObjectID IN NUMBER,
50 p_parentObjectTypeID IN NUMBER,
51 p_childObjectID IN NUMBER,
52 p_childObjectTypeID IN NUMBER,
53 p_relationshipType IN NUMBER,
54 p_relationshipStatus IN NUMBER,
55 x_return_status OUT NOCOPY VARCHAR2,
56 x_msg_count OUT NOCOPY NUMBER,
57 x_msg_data OUT NOCOPY VARCHAR2,
58 p_api_version IN NUMBER,
59 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
60 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE)
61 IS
62
63 l_api_name CONSTANT VARCHAR2(30) := 'WIP_SCHED_RELATION_GRP';
64 l_api_version CONSTANT NUMBER := 1.0;
65
66 CURSOR top_level_object_cur IS
67 SELECT top_level_object_id,
68 top_level_object_type_id
69 FROM wip_sched_relationships
70 WHERE child_object_id = p_parentObjectID
71 AND relationship_type = 1;
72
73 CURSOR top_id_rel2_parent_cur IS
74 SELECT top_level_object_id,
75 top_level_object_type_id
76 FROM wip_sched_relationships
77 WHERE child_object_id = p_parentObjectID
78 AND relationship_type = 1;
79
80 CURSOR top_id_rel2_child_cur IS
81 SELECT top_level_object_id,
82 top_level_object_type_id
83 FROM wip_sched_relationships
84 WHERE child_object_id = p_childObjectID
85 AND relationship_type = 1;
86
87 l_top_level_object_id NUMBER;
88 l_top_level_object_id_tmp1 NUMBER := NULL;
89 l_top_level_object_id_tmp2 NUMBER := NULL;
90 l_top_level_object_type_id NUMBER;
91 l_Creation_Date DATE;
92 l_Created_By NUMBER;
93 l_last_UPDATE_date DATE;
94 l_last_UPDATEd_by NUMBER;
95 l_last_UPDATE_login NUMBER;
96
97 l_count_a Number := 0;
98 l_count_b Number := 0;
99
100
101 INVALID_OBJ_TYPE_EXCEPTION EXCEPTION;
102 INVALID_REL_TYPE_EXCEPTION EXCEPTION;
103 DEP_REL_EXIST_EXCEPTION EXCEPTION;
104 CONST_REL_EXIST_EXCEPTION EXCEPTION;
105 DUPLICATE_PARENT_EXCEPTION EXCEPTION;
106 LOOP_FOUND_EXCEPTION EXCEPTION;
107 INSERT_FAIL_EXCEPTION EXCEPTION;
108 PARENT_CHILD_SAME_EXCEPTION EXCEPTION;
109 BAD_REL_STATUS_EXCEPTION EXCEPTION;
110
111 BEGIN
112
113 /* Standard begin of API savepoint */
114 SAVEPOINT sp_wip_wol_grp;
115 /* Standard call to check for call compatibility. */
116 IF NOT FND_API.Compatible_API_Call (l_api_version,
117 P_API_VERSION,
118 l_api_name,
119 G_PKG_NAME)
120
121 THEN
122 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
123 END IF;
124
125 /* Check p_init_msg_list */
126 IF FND_API.to_Boolean( p_init_msg_list ) THEN
127 FND_MSG_PUB.initialize;
128 END IF;
129 /* Initialize API return status to success */
130 x_return_status := FND_API.G_RET_STS_SUCCESS;
131
132
133 /* Object type can be WO only */
134 IF p_parentObjectTypeID <> WIP_CONSTANTS.G_Obj_TYPE_WO
135 OR p_childObjectTypeID <> WIP_CONSTANTS.G_Obj_TYPE_WO
136 THEN
137 raise INVALID_OBJ_TYPE_EXCEPTION;
138 END IF;
139
140
141 /* Relationship type can be Type 1(Constrained) or Type 2(Dependent) only */
142 IF p_relationshipType <> WIP_CONSTANTS.G_REL_TYPE_CONSTRAINED
143 AND p_relationshipType <> WIP_CONSTANTS.G_REL_TYPE_DEPENDENT
144 THEN
145 raise INVALID_REL_TYPE_EXCEPTION;
146 END IF;
147
148
149 /* Check IF ParentID NOT same as ChildID */
150 IF p_parentObjectID = p_childObjectID then
151 raise PARENT_CHILD_SAME_EXCEPTION;
152 END IF;
153
154
155 /* Check if Relationship Status is 0,1,2 and 3 only */
156 IF p_relationshipStatus <> WIP_CONSTANTS.G_REL_Status_Pending
157 AND p_relationshipStatus <> WIP_CONSTANTS.G_REL_Status_Processing
158 AND p_relationshipStatus <> WIP_CONSTANTS.G_REL_Status_Valid
159 AND p_relationshipStatus <> WIP_CONSTANTS.G_REL_Status_Invalid
160 then
161 raise BAD_REL_STATUS_EXCEPTION;
162 END IF;
163
164
165 /*----------------------------+
166 | VALIDATE FOR Rel Type 1 |
167 +----------------------------*/
168 IF P_RELATIONSHIPTYPE = WIP_CONSTANTS.G_REL_TYPE_CONSTRAINED
169 THEN
170
171 /* Check Parent AND Child don't have Dependent Relationship */
172
173 /* Check that the Child doesn't already have an existing Parent */
174 SELECT count(*)
175 INTO l_count_a
176 FROM wip_sched_relationships
177 WHERE relationship_type = WIP_CONSTANTS.G_REL_TYPE_CONSTRAINED
178 AND child_object_id = p_childObjectID;
179
180 IF l_count_a <>0 then
181 raise DUPLICATE_PARENT_EXCEPTION;
182 END IF;
183
184
185 l_count_a := 0;
186 /* Check IF Child doesn't lie in Parent hierarchy */
187 SELECT count(*)
188 INTO l_count_a
189 FROM wip_sched_relationships
190 WHERE relationship_type = WIP_CONSTANTS.G_REL_TYPE_DEPENDENT
191 AND child_object_id = p_childObjectID
192 START WITH parent_object_id = p_parentObjectID
193 AND relationship_type = WIP_CONSTANTS.G_REL_TYPE_DEPENDENT
194 CONNECT BY PRIOR child_object_id = parent_object_id
195 AND relationship_type = WIP_CONSTANTS.G_REL_TYPE_DEPENDENT;
196
197
198 /* Check IF Parent doesn't lie in Child hierarchy */
199 SELECT count(*) INTO l_count_b
200 FROM wip_sched_relationships
201 WHERE relationship_type = WIP_CONSTANTS.G_REL_TYPE_DEPENDENT
202 AND child_object_id = p_parentObjectID
203 START WITH parent_object_id = p_childObjectID
204 AND relationship_type = WIP_CONSTANTS.G_REL_TYPE_DEPENDENT
205 CONNECT BY PRIOR child_object_id = parent_object_id
206 AND relationship_type = WIP_CONSTANTS.G_REL_TYPE_DEPENDENT;
207
208
209 /* both l_count_a AND l_count_b should be ZERO. */
210 IF l_count_a <>0 OR l_count_b <>0 then
211 raise DEP_REL_EXIST_EXCEPTION;
212 END IF;
213
214 l_count_a := 0;
215 l_count_b := 0;
216
217
218 /* Populate TOP_LEVEL_OBJECT_ID for REL TYPE 1 */
219
220 /* Check if Parent is CHILD in any existing Relationships */
221 OPEN top_level_object_cur;
222 FETCH top_level_object_cur
223 INTO l_top_level_object_id,
224 l_top_level_object_type_id;
225 IF top_level_object_cur%NOTFOUND then
226 /* if parent node is root node */
227 l_top_level_object_id := p_parentObjectID;
228 l_top_level_object_type_id := p_parentObjectTypeID;
229 END IF;
230 CLOSE top_level_object_cur;
231
232
233 /* For Rel Type 1, UPDATE TOP_LEVEL_OBJECT_ID for all records whose parent is CHILD */
234 UPDATE wip_sched_relationships
235 SET top_level_object_id = l_top_level_object_id,
236 top_level_object_type_id = l_top_level_object_type_id
237 WHERE top_level_object_id = p_childObjectID
238 AND relationship_type = WIP_CONSTANTS.G_REL_TYPE_CONSTRAINED;
239
240 END IF;
241 /*----------------------------+
242 | END Relationship TYPE 1 |
243 +----------------------------*/
244
245 l_count_a := 0;
246 l_count_b := 0;
247
248
249 /*----------------------------+
250 | VALIDATE FOR Rel Type 2 |
251 +----------------------------*/
252 IF P_RELATIONSHIPTYPE = WIP_CONSTANTS.G_REL_TYPE_DEPENDENT
253 THEN
254
255
256 /* Check Parent AND Child don't have CONSTRAINED Relationship */
257
258 /* Check if Child doesn't lie in Parent hierarchy */
259 SELECT count(*) INTO l_count_a
260 FROM wip_sched_relationships
261 WHERE relationship_type = WIP_CONSTANTS.G_REL_TYPE_CONSTRAINED
262 AND child_object_id = p_childObjectID
263 START WITH parent_object_id = p_parentObjectID
264 AND relationship_type = WIP_CONSTANTS.G_REL_TYPE_CONSTRAINED
265 CONNECT BY PRIOR child_object_id = parent_object_id
266 AND relationship_type = WIP_CONSTANTS.G_REL_TYPE_CONSTRAINED;
267
268 /* Check if Parent doesn't lie in Child hierarchy */
269 SELECT count(*) INTO l_count_b
270 FROM wip_sched_relationships
271 WHERE relationship_type = WIP_CONSTANTS.G_REL_TYPE_CONSTRAINED
272 AND child_object_id = p_parentObjectID
273 START WITH parent_object_id = p_childObjectID
274 AND relationship_type = WIP_CONSTANTS.G_REL_TYPE_CONSTRAINED
275 CONNECT BY PRIOR child_object_id = parent_object_id
276 AND relationship_type = WIP_CONSTANTS.G_REL_TYPE_CONSTRAINED;
277
278 /* both l_count_a AND l_count_b should be ZERO. */
279 IF l_count_a <>0 OR l_count_b <>0 then
280 raise CONST_REL_EXIST_EXCEPTION;
281 END IF;
282
283 l_count_a := 0;
284 l_count_b := 0;
285
286
287 /*------------------------------------------------------+
288 | CHECK FOR LOOP IN CASE of DEPENDENT Relationships |
289 +------------------------------------------------------*/
290 /* Check if Child doesn't lie in Parent hierarchy for Rel Type 2 */
291 SELECT count(*) INTO l_count_a
292 FROM wip_sched_relationships
293 WHERE relationship_type = WIP_CONSTANTS.G_REL_TYPE_DEPENDENT
294 AND child_object_id = p_childObjectID
295 START WITH parent_object_id = p_parentObjectID
296 AND relationship_type = WIP_CONSTANTS.G_REL_TYPE_DEPENDENT
297 CONNECT BY PRIOR child_object_id = parent_object_id
298 AND relationship_type = WIP_CONSTANTS.G_REL_TYPE_DEPENDENT;
299
300 /* Check if Parent doesn't lie in Child hierarchy for Rel Type 2 */
301 SELECT count(*) INTO l_count_b
302 FROM wip_sched_relationships
303 WHERE relationship_type = WIP_CONSTANTS.G_REL_TYPE_DEPENDENT
304 AND child_object_id = p_parentObjectID
305 START WITH parent_object_id = p_childObjectID
306 AND relationship_type = WIP_CONSTANTS.G_REL_TYPE_DEPENDENT
307 CONNECT BY PRIOR child_object_id = parent_object_id
308 AND relationship_type = WIP_CONSTANTS.G_REL_TYPE_DEPENDENT;
309
310 /* both l_count_a AND l_count_b should be ZERO. */
311 IF l_count_a <>0 OR l_count_b <>0 then
312 raise LOOP_FOUND_EXCEPTION;
313 END IF;
314
315 /*--------------------------------------------------+
316 | Populate TOP_LEVEL_OBJECT_ID for Rel Type 2 |
317 +--------------------------------------------------*/
318 /* Populate TOP_LEVEL_OBJECT_ID for REL TYPE 2 */
319
320 /* Check if Parent is CHILD in any existing Relationships */
321 OPEN top_id_rel2_parent_cur;
322 FETCH top_id_rel2_parent_cur
323 INTO l_top_level_object_id_tmp1,
324 l_top_level_object_type_id;
325 CLOSE top_id_rel2_parent_cur;
326
327 /* Check if Child is CHILD in any existing Relationships */
328 OPEN top_id_rel2_child_cur;
329 FETCH top_id_rel2_child_cur
330 INTO l_top_level_object_id_tmp2,
331 l_top_level_object_type_id;
332 CLOSE top_id_rel2_child_cur;
333
334 /* Check if Both TOP_LEVEL_OBJECT_IDs, if they exits, are same */
335 /* Populate NULL, if different, otherwise, populate this ID*/
336 IF l_top_level_object_id_tmp1 = l_top_level_object_id_tmp2
337 THEN
338 l_top_level_object_id := l_top_level_object_id_tmp2;
339 ELSE
340 l_top_level_object_id := NULL;
341 l_top_level_object_type_id := NULL;
342 END IF;
343
344 END IF;
345 /*--------------------------------+
346 | END for Relationship TYPE 2 |
347 +--------------------------------*/
348
349
350 /* If NO EXCEPTIONS, INSERT THE ROW */
351 l_Creation_Date := SYSDATE;
352 l_Created_By := FND_GLOBAL.USER_ID;
353 l_last_UPDATE_date := SYSDATE;
354 l_last_UPDATEd_by := FND_GLOBAL.USER_ID;
355 l_last_UPDATE_login := FND_GLOBAL.LOGIN_ID;
356
357 INSERT INTO WIP_SCHED_RELATIONSHIPS(
358 SCHED_RELATIONSHIP_ID,
359 PARENT_OBJECT_ID,
360 PARENT_OBJECT_TYPE_ID,
361 CHILD_OBJECT_ID,
362 CHILD_OBJECT_TYPE_ID,
363 RELATIONSHIP_TYPE,
364 RELATIONSHIP_STATUS,
365 TOP_LEVEL_OBJECT_ID,
366 TOP_LEVEL_OBJECT_TYPE_ID,
367 CREATED_BY,
368 CREATION_DATE,
369 LAST_UPDATED_BY,
370 LAST_UPDATE_DATE,
371 Last_UPDATE_Login)
372 VALUES(
373 WIP_SCHED_RELATIONSHIPS_S.NEXTVAL,
374 p_parentObjectID,
375 p_parentObjectTypeID,
376 p_childObjectID,
377 p_childObjectTypeID,
378 p_relationshipType,
379 p_relationshipStatus,
380 l_top_level_object_id,
381 l_top_level_object_type_id,
382 l_created_by,
383 l_creation_date,
384 l_last_UPDATEd_by,
385 l_last_UPDATE_date,
386 l_last_UPDATE_login);
387
388 IF SQL%NOTFOUND THEN
389 RAISE INSERT_FAIL_EXCEPTION;
390 END IF;
391
392 /* Standard check of p_commit */
393
394 /* Commit work if p_commit flag true */
395 IF FND_API.To_Boolean( p_commit ) THEN
396 COMMIT WORK;
397 END IF;
398
399 /* Standard call to get message count AND IF count is 1, get message info. */
400 FND_MSG_PUB.Count_AND_Get
401 ( p_count => x_msg_count,
402 p_data => x_msg_data
403 );
404
405 Exception
406
407 WHEN FND_API.G_EXC_ERROR THEN
408 ROLLBACK TO sp_wip_wol_GRP;
409 x_return_status := FND_API.G_RET_STS_ERROR ;
410 FND_MSG_PUB.Count_AND_Get
411 ( p_count => x_msg_count,
412 p_data => x_msg_data
413 );
414
415 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
416 ROLLBACK TO sp_wip_wol_grp;
417 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
418 FND_MSG_PUB.Count_AND_Get
419 ( p_count => x_msg_count,
420 p_data => x_msg_data
421 );
422
423 /* Take Appropriate Action, AND message */
424 WHEN INVALID_OBJ_TYPE_EXCEPTION
425 THEN
426 ROLLBACK to sp_wip_wol_grp;
427 x_return_status := FND_API.G_RET_STS_ERROR;
428 FND_MESSAGE.SET_name('WIP', 'WIP_WOL_INVALID_OBJ_TYPE');
429 FND_MSG_PUB.Add;
430 FND_MSG_PUB.Count_AND_Get
431 ( p_count => x_msg_count,
432 p_data => x_msg_data
433 );
434
435 WHEN INVALID_REL_TYPE_EXCEPTION
436 THEN
437 ROLLBACK to sp_wip_wol_grp;
438 x_return_status := FND_API.G_RET_STS_ERROR;
439 FND_MESSAGE.SET_name('WIP', 'WIP_WOL_INVALID_REL_TYPE');
440 FND_MSG_PUB.Add;
441 FND_MSG_PUB.Count_AND_Get
442 ( p_count => x_msg_count,
443 p_data => x_msg_data
444 );
445
446 WHEN PARENT_CHILD_SAME_EXCEPTION
447 THEN
448 ROLLBACK to sp_wip_wol_grp;
449 x_return_status := FND_API.G_RET_STS_ERROR;
450 FND_MESSAGE.SET_name('WIP', 'WIP_WOL_PARENT_CHILD_SAME');
451 FND_MSG_PUB.Add;
452 FND_MSG_PUB.Count_AND_Get
453 ( p_count => x_msg_count,
454 p_data => x_msg_data
455 );
456
457 WHEN BAD_REL_STATUS_EXCEPTION
458 THEN
459 ROLLBACK to sp_wip_wol_grp;
460 x_return_status := FND_API.G_RET_STS_ERROR;
461 FND_MESSAGE.SET_name('WIP', 'WIP_WOL_BAD_REL_STATUS');
462 FND_MSG_PUB.Add;
463 FND_MSG_PUB.Count_AND_Get
464 ( p_count => x_msg_count,
465 p_data => x_msg_data
466 );
467
468 WHEN DEP_REL_EXIST_EXCEPTION
469 THEN
470 ROLLBACK to sp_wip_wol_grp;
471 x_return_status := FND_API.G_RET_STS_ERROR;
472 FND_MESSAGE.SET_name('WIP', 'WIP_WOL_DEPENDENT_REL_EXIST');
473 FND_MSG_PUB.Add;
474 FND_MSG_PUB.Count_AND_Get
475 ( p_count => x_msg_count,
476 p_data => x_msg_data
477 );
478
479 WHEN CONST_REL_EXIST_EXCEPTION
480 THEN
481 ROLLBACK to sp_wip_wol_grp;
482 x_return_status := FND_API.G_RET_STS_ERROR;
483 FND_MESSAGE.SET_name('WIP', 'WIP_WOL_CONSTRAINED_REL_EXIST');
484 FND_MSG_PUB.Add;
485 FND_MSG_PUB.Count_AND_Get
486 ( p_count => x_msg_count,
487 p_data => x_msg_data
488 );
489
490 WHEN DUPLICATE_PARENT_EXCEPTION
491 THEN
492 ROLLBACK to sp_wip_wol_grp;
493 x_return_status := FND_API.G_RET_STS_ERROR;
494 FND_MESSAGE.SET_name('WIP', 'WIP_WOL_PARENT_EXIST');
495 FND_MSG_PUB.Add;
496 FND_MSG_PUB.Count_AND_Get
497 ( p_count => x_msg_count,
498 p_data => x_msg_data
499 );
500
501 WHEN LOOP_FOUND_EXCEPTION
502 THEN
503 ROLLBACK to sp_wip_wol_grp;
504 x_return_status := FND_API.G_RET_STS_ERROR;
505 FND_MESSAGE.SET_name('WIP', 'WIP_WOL_LOOP_FOUND');
506 FND_MSG_PUB.Add;
507 FND_MSG_PUB.Count_AND_Get
508 ( p_count => x_msg_count,
509 p_data => x_msg_data
510 );
511
512 WHEN INSERT_FAIL_EXCEPTION
513 THEN
514 ROLLBACK to sp_wip_wol_grp;
515 x_return_status := FND_API.G_RET_STS_ERROR;
516 FND_MESSAGE.SET_name('WIP', 'WIP_WOL_INSERT_FAIL');
517 FND_MSG_PUB.Add;
518 FND_MSG_PUB.Count_AND_Get
519 ( p_count => x_msg_count,
520 p_data => x_msg_data
521 );
522
523 WHEN OTHERS THEN
524 ROLLBACK TO sp_wip_wol_grp;
525 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
526 IF FND_MSG_PUB.Check_Msg_Level
527 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
528 THEN
529 FND_MSG_PUB.Add_Exc_Msg
530 ( G_PKG_NAME,
531 l_api_name
532 );
533 END IF;
534 FND_MSG_PUB.Count_AND_Get
535 ( p_count => x_msg_count,
536 p_data => x_msg_data
537 );
538
539 END insertRow;
540
541
542
543
544 /************************************************************************
545 * PROCEDURE DELETEROW *
546 * This procedure is used to validate AND DELETE Relationships FROM *
547 * WIP_SCHED_RELATIONSHIPS Table *
548 * The input parameters for this procedure are: *
549 * p_relationshipID : Relationship idetifier to be deleted *
550 * x_return_status : To indicate procedure success, failure, error *
551 * x_msg_count : To indicate number of msgs in msg list *
552 * x_msg_data : Return message in encoded form *
553 * p_api_version : To validate API version to be used *
554 * p_init_msg_list : Whether to intialize public msg list *
555 * p_commit : Whether to commit transaction *
556 ************************************************************************/
557 PROCEDURE deleteRow(p_relationshipID IN NUMBER,
558 x_return_status OUT NOCOPY VARCHAR2,
559 x_msg_count OUT NOCOPY NUMBER,
560 x_msg_data OUT NOCOPY VARCHAR2,
561 p_api_version IN NUMBER,
562 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
563 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE)
564 IS
565
566 l_api_name CONSTANT VARCHAR2(30) := 'WIP_SCHED_RELATION_GRP';
567 l_api_version CONSTANT NUMBER := 1.0;
568
569 l_count_a NUMBER := 0;
570 l_child_object_id NUMBER := 0;
571 l_parent_object_id NUMBER := 0;
572 l_relationship_type NUMBER := 0;
573 l_relationship_id_tmp NUMBER := NULL;
574 l_top_level_object_id_tmp1 NUMBER := NULL;
575 l_top_level_object_id_tmp2 NUMBER := NULL;
576 l_top_level_object_id NUMBER;
577 l_top_level_object_type_id NUMBER;
578
579
580 DEP_REL_EXIST_EXCEPTION EXCEPTION;
581 NO_SUCH_RELID_EXCEPTION EXCEPTION;
582 DELETE_FAIL_EXCEPTION EXCEPTION;
583
584 CURSOR dependent_rels_cur IS
585 SELECT distinct sched_relationship_id
586 FROM wip_sched_relationships
587 WHERE relationship_type = WIP_CONSTANTS.G_REL_TYPE_DEPENDENT
588 AND sched_relationship_id
589 IN
590 (SELECT SCHED_RELATIONSHIP_ID
591 FROM wip_sched_relationships
592 START WITH parent_object_id = l_parent_object_id
593 CONNECT BY PRIOR child_object_id = parent_object_id);
594
595 CURSOR top_id_rel2_parent_cur IS
596 SELECT top_level_object_id,
597 top_level_object_type_id
598 FROM wip_sched_relationships
599 WHERE relationship_type = 1
600 AND child_object_id =
601 (SELECT parent_object_id
602 FROM wip_sched_relationships
603 WHERE sched_relationship_id =
604 l_relationship_id_tmp);
605
606 CURSOR top_id_rel2_child_cur IS
607 SELECT top_level_object_id,
608 top_level_object_type_id
609 FROM wip_sched_relationships
610 WHERE relationship_type = 1
611 AND child_object_id =
612 (SELECT child_object_id
613 FROM wip_sched_relationships
614 WHERE sched_relationship_id =
615 l_relationship_id_tmp);
616
617 BEGIN
618
619 /* Standard begin of API savepoint */
620 SAVEPOINT sp_wip_wol_grp;
621 /* Standard call to check for call compatibility. */
622 IF NOT FND_API.Compatible_API_Call (l_api_version,
623 P_API_VERSION,
624 l_api_name,
625 G_PKG_NAME)
626
627 THEN
628 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
629 END IF;
630
631 /* Check p_init_msg_list */
632 IF FND_API.to_Boolean( p_init_msg_list ) THEN
633 FND_MSG_PUB.initialize;
634 END IF;
635 /* Initialize API return status to success */
636 x_return_status := FND_API.G_RET_STS_SUCCESS;
637
638
639 /* Check IF One Row exists for RelationshipID */
640 BEGIN
641 SELECT child_object_id,
642 parent_object_id,
643 relationship_type
644 INTO l_child_object_id,
645 l_parent_object_id,
646 l_relationship_type
647 FROM WIP_SCHED_RELATIONSHIPS
648 WHERE sched_relationship_ID = p_relationshipID;
649 EXCEPTION
650 When NO_DATA_FOUND then
651 raise NO_SUCH_RELID_EXCEPTION;
652 END;
653
654 /* IF relationship ID exists */
655
656 /*-------------------+
657 | FOR Rel Type 1 |
658 +-------------------*/
659 IF l_relationship_type = WIP_CONSTANTS.G_REL_TYPE_CONSTRAINED
660 then
661
662 /* Check CHILD not involved in any rel type 2 */
663 SELECT count(*) INTO l_count_a
664 FROM wip_sched_relationships
665 WHERE relationship_type = WIP_CONSTANTS.G_REL_TYPE_DEPENDENT
666 AND (parent_object_id = l_child_object_id
667 OR child_object_id = l_child_object_id);
668
669 IF l_count_a <> 0
670 then
671 Raise DEP_REL_EXIST_EXCEPTION;
672 END IF;
673
674
675 /* UPDATE the TOP_LEVEL_OBJECT_ID of the subtree rooted at CHILD to be CHILD */
676 UPDATE wip_sched_relationships
677 SET top_level_object_id = l_child_object_id
678 WHERE SCHED_RELATIONSHIP_ID
679 IN
680 (SELECT SCHED_RELATIONSHIP_ID FROM wip_sched_relationships
681 WHERE relationship_type = WIP_CONSTANTS.G_REL_TYPE_CONSTRAINED
682 START WITH parent_object_id = l_child_object_id
683 AND relationship_type = WIP_CONSTANTS.G_REL_TYPE_CONSTRAINED
684 CONNECT BY PRIOR child_object_id = parent_object_id
685 AND relationship_type = WIP_CONSTANTS.G_REL_TYPE_CONSTRAINED);
686
687
688 /* UPDATE Top_level_object_id for all Rel type 2 relationships under Parent_object */
689 FOR dependent_rel_cur_rec IN dependent_rels_cur
690 LOOP
691 l_relationship_id_tmp := dependent_rel_cur_rec.sched_relationship_id;
692
693 OPEN top_id_rel2_parent_cur;
694 FETCH top_id_rel2_parent_cur INTO l_top_level_object_id_tmp1, l_top_level_object_type_id;
695 OPEN top_id_rel2_child_cur;
696 FETCH top_id_rel2_child_cur INTO l_top_level_object_id_tmp2, l_top_level_object_type_id;
697
698 /*if top_level_object_id of parent Or Child node in Rel type 2 is either NULL or Not equal */
699 /* UPDATE it to NULL*/
700 IF l_top_level_object_id_tmp1 <> l_top_level_object_id_tmp2
701 then
702 UPDATE wip_sched_relationships
703 SET top_level_object_id = NULL
704 WHERE sched_relationship_id = l_relationship_id_tmp;
705 END IF;
706
707 CLOSE top_id_rel2_parent_cur;
708 CLOSE top_id_rel2_child_cur;
709
710 END LOOP;
711
712 END IF;
713 /*---------------------+
714 | END IF for Rel type 1|
715 +---------------------*/
716
717 /* Delete the row NOW */
718 DELETE FROM WIP_SCHED_RELATIONSHIPS
719 WHERE SCHED_RELATIONSHIP_ID = p_relationshipID ;
720
721 /* Check IF Delete fails */
722 IF SQL%NOTFOUND THEN
723 RAISE DELETE_FAIL_EXCEPTION;
724 END IF;
725
726
727 /* Standard check of p_commit */
728 /* Commit work IF p_commit flag true */
729 IF FND_API.To_Boolean( p_commit ) THEN
730 COMMIT WORK;
731 END IF;
732
733 /* Standard call to get message count AND IF count is 1, get message info. */
734 FND_MSG_PUB.Count_AND_Get
735 ( p_count => x_msg_count,
736 p_data => x_msg_data
737 );
738
739 EXCEPTION
740
741 WHEN FND_API.G_EXC_ERROR THEN
742 ROLLBACK TO sp_wip_wol_GRP;
743 x_return_status := FND_API.G_RET_STS_ERROR ;
744 FND_MSG_PUB.Count_AND_Get
745 ( p_count => x_msg_count,
746 p_data => x_msg_data
747 );
748
749 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
750 ROLLBACK TO sp_wip_wol_grp;
751 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
752 FND_MSG_PUB.Count_AND_Get
753 ( p_count => x_msg_count,
754 p_data => x_msg_data
755 );
756
757 WHEN DEP_REL_EXIST_EXCEPTION THEN
758 ROLLBACK to sp_wip_wol_grp;
759 x_return_status := FND_API.G_RET_STS_ERROR;
760 FND_MESSAGE.SET_name('WIP', 'WIP_WOL_CHILD_DEP_REL_EXIST');
761 FND_MSG_PUB.Add;
762 FND_MSG_PUB.Count_AND_Get
763 ( p_count => x_msg_count,
764 p_data => x_msg_data
765 );
766
767 WHEN NO_SUCH_RELID_EXCEPTION THEN
768 ROLLBACK to sp_wip_wol_grp;
769 x_return_status := FND_API.G_RET_STS_ERROR;
770 FND_MESSAGE.SET_name('WIP', 'WIP_WOL_NO_SUCH_REL_ID');
771 FND_MSG_PUB.Add;
772 FND_MSG_PUB.Count_AND_Get
773 ( p_count => x_msg_count,
774 p_data => x_msg_data
775 );
776
777 WHEN DELETE_FAIL_EXCEPTION
778 THEN
779 ROLLBACK to sp_wip_wol_grp;
780 x_return_status := FND_API.G_RET_STS_ERROR;
781 FND_MESSAGE.SET_name('WIP', 'WIP_WOL_DELETE_FAIL');
782 FND_MSG_PUB.Add;
783 FND_MSG_PUB.Count_AND_Get
784 ( p_count => x_msg_count,
785 p_data => x_msg_data
786 );
787
788 WHEN OTHERS THEN
789 ROLLBACK TO sp_wip_wol_grp;
790 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
791 IF FND_MSG_PUB.Check_Msg_Level
792 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
793 THEN
794 FND_MSG_PUB.Add_Exc_Msg
795 ( G_PKG_NAME,
796 l_api_name
797 );
798 END IF;
799 FND_MSG_PUB.Count_AND_Get
800 ( p_count => x_msg_count,
801 p_data => x_msg_data
802 );
803
804 END deleteRow;
805
806
807 END wip_sched_relation_grp;