[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| to Create and Delete relationships for Work Order Scheduling.|
| |
| Coders : Amit Garg |
+===========================================================================*/
/************************************************************************
* PACKAGE VARIABLES *
************************************************************************/
/******************************************************************************
* PROCEDURE INSERTROW *
* This procedure is used to validate AND create Relationships to be *
* inserted in WIP_SCHED_RELATIONSHIPS Table *
* The input parameters for this procedure are: *
* p_parentObjectID : Parent Object Idetifier *
* p_parentObjectTypeID : Parent Object type Idetifier *
* p_childObjectID : Child Object Idetifier *
* p_childObjectTypeID : Child Object type Idetifier *
* p_relationshipType : Type of relationship between parent and child *
* p_relationshipStatus : Relationship status, *
* pending : 0 *
* processing : 1 *
* valid : 2 *
* invalid : 3 *
* x_return_status : out parameter to indicate success, failure or *
* error for this procedure *
* x_msg_count : out parameter indicating number of messages in *
* msg list *
* x_msg_data : message in encoded form is returned *
* p_api_version : parameter indicating api version, to check for *
* valid API version *
* p_init_msg_list : Parameter to indicate whether public msg list *
* is required to be initialised *
* p_commit : Parameter to indicate if commit is required *
* by this proc *
******************************************************************************/
PROCEDURE insertRow(p_parentObjectID IN NUMBER,
p_parentObjectTypeID IN NUMBER,
p_childObjectID IN NUMBER,
p_childObjectTypeID IN NUMBER,
p_relationshipType IN NUMBER,
p_relationshipStatus IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE)
IS
l_api_name CONSTANT VARCHAR2(30) := 'WIP_SCHED_RELATION_GRP';
SELECT top_level_object_id,
top_level_object_type_id
FROM wip_sched_relationships
WHERE child_object_id = p_parentObjectID
AND relationship_type = 1;
SELECT top_level_object_id,
top_level_object_type_id
FROM wip_sched_relationships
WHERE child_object_id = p_parentObjectID
AND relationship_type = 1;
SELECT top_level_object_id,
top_level_object_type_id
FROM wip_sched_relationships
WHERE child_object_id = p_childObjectID
AND relationship_type = 1;
l_last_UPDATE_date DATE;
l_last_UPDATEd_by NUMBER;
l_last_UPDATE_login NUMBER;
INSERT_FAIL_EXCEPTION EXCEPTION;
SELECT count(*)
INTO l_count_a
FROM wip_sched_relationships
WHERE relationship_type = WIP_CONSTANTS.G_REL_TYPE_CONSTRAINED
AND child_object_id = p_childObjectID;
SELECT count(*)
INTO l_count_a
FROM wip_sched_relationships
WHERE relationship_type = WIP_CONSTANTS.G_REL_TYPE_DEPENDENT
AND child_object_id = p_childObjectID
START WITH parent_object_id = p_parentObjectID
AND relationship_type = WIP_CONSTANTS.G_REL_TYPE_DEPENDENT
CONNECT BY PRIOR child_object_id = parent_object_id
AND relationship_type = WIP_CONSTANTS.G_REL_TYPE_DEPENDENT;
SELECT count(*) INTO l_count_b
FROM wip_sched_relationships
WHERE relationship_type = WIP_CONSTANTS.G_REL_TYPE_DEPENDENT
AND child_object_id = p_parentObjectID
START WITH parent_object_id = p_childObjectID
AND relationship_type = WIP_CONSTANTS.G_REL_TYPE_DEPENDENT
CONNECT BY PRIOR child_object_id = parent_object_id
AND relationship_type = WIP_CONSTANTS.G_REL_TYPE_DEPENDENT;
/* For Rel Type 1, UPDATE TOP_LEVEL_OBJECT_ID for all records whose parent is CHILD */
UPDATE wip_sched_relationships
SET top_level_object_id = l_top_level_object_id,
top_level_object_type_id = l_top_level_object_type_id
WHERE top_level_object_id = p_childObjectID
AND relationship_type = WIP_CONSTANTS.G_REL_TYPE_CONSTRAINED;
SELECT count(*) INTO l_count_a
FROM wip_sched_relationships
WHERE relationship_type = WIP_CONSTANTS.G_REL_TYPE_CONSTRAINED
AND child_object_id = p_childObjectID
START WITH parent_object_id = p_parentObjectID
AND relationship_type = WIP_CONSTANTS.G_REL_TYPE_CONSTRAINED
CONNECT BY PRIOR child_object_id = parent_object_id
AND relationship_type = WIP_CONSTANTS.G_REL_TYPE_CONSTRAINED;
SELECT count(*) INTO l_count_b
FROM wip_sched_relationships
WHERE relationship_type = WIP_CONSTANTS.G_REL_TYPE_CONSTRAINED
AND child_object_id = p_parentObjectID
START WITH parent_object_id = p_childObjectID
AND relationship_type = WIP_CONSTANTS.G_REL_TYPE_CONSTRAINED
CONNECT BY PRIOR child_object_id = parent_object_id
AND relationship_type = WIP_CONSTANTS.G_REL_TYPE_CONSTRAINED;
SELECT count(*) INTO l_count_a
FROM wip_sched_relationships
WHERE relationship_type = WIP_CONSTANTS.G_REL_TYPE_DEPENDENT
AND child_object_id = p_childObjectID
START WITH parent_object_id = p_parentObjectID
AND relationship_type = WIP_CONSTANTS.G_REL_TYPE_DEPENDENT
CONNECT BY PRIOR child_object_id = parent_object_id
AND relationship_type = WIP_CONSTANTS.G_REL_TYPE_DEPENDENT;
SELECT count(*) INTO l_count_b
FROM wip_sched_relationships
WHERE relationship_type = WIP_CONSTANTS.G_REL_TYPE_DEPENDENT
AND child_object_id = p_parentObjectID
START WITH parent_object_id = p_childObjectID
AND relationship_type = WIP_CONSTANTS.G_REL_TYPE_DEPENDENT
CONNECT BY PRIOR child_object_id = parent_object_id
AND relationship_type = WIP_CONSTANTS.G_REL_TYPE_DEPENDENT;
/* If NO EXCEPTIONS, INSERT THE ROW */
l_Creation_Date := SYSDATE;
l_last_UPDATE_date := SYSDATE;
l_last_UPDATEd_by := FND_GLOBAL.USER_ID;
l_last_UPDATE_login := FND_GLOBAL.LOGIN_ID;
INSERT INTO WIP_SCHED_RELATIONSHIPS(
SCHED_RELATIONSHIP_ID,
PARENT_OBJECT_ID,
PARENT_OBJECT_TYPE_ID,
CHILD_OBJECT_ID,
CHILD_OBJECT_TYPE_ID,
RELATIONSHIP_TYPE,
RELATIONSHIP_STATUS,
TOP_LEVEL_OBJECT_ID,
TOP_LEVEL_OBJECT_TYPE_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
Last_UPDATE_Login)
VALUES(
WIP_SCHED_RELATIONSHIPS_S.NEXTVAL,
p_parentObjectID,
p_parentObjectTypeID,
p_childObjectID,
p_childObjectTypeID,
p_relationshipType,
p_relationshipStatus,
l_top_level_object_id,
l_top_level_object_type_id,
l_created_by,
l_creation_date,
l_last_UPDATEd_by,
l_last_UPDATE_date,
l_last_UPDATE_login);
RAISE INSERT_FAIL_EXCEPTION;
WHEN INSERT_FAIL_EXCEPTION
THEN
ROLLBACK to sp_wip_wol_grp;
FND_MESSAGE.SET_name('WIP', 'WIP_WOL_INSERT_FAIL');
END insertRow;
* PROCEDURE DELETEROW *
* This procedure is used to validate AND DELETE Relationships FROM *
* WIP_SCHED_RELATIONSHIPS Table *
* The input parameters for this procedure are: *
* p_relationshipID : Relationship idetifier to be deleted *
* x_return_status : To indicate procedure success, failure, error *
* x_msg_count : To indicate number of msgs in msg list *
* x_msg_data : Return message in encoded form *
* p_api_version : To validate API version to be used *
* p_init_msg_list : Whether to intialize public msg list *
* p_commit : Whether to commit transaction *
************************************************************************/
PROCEDURE deleteRow(p_relationshipID IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE)
IS
l_api_name CONSTANT VARCHAR2(30) := 'WIP_SCHED_RELATION_GRP';
DELETE_FAIL_EXCEPTION EXCEPTION;
SELECT distinct sched_relationship_id
FROM wip_sched_relationships
WHERE relationship_type = WIP_CONSTANTS.G_REL_TYPE_DEPENDENT
AND sched_relationship_id
IN
(SELECT SCHED_RELATIONSHIP_ID
FROM wip_sched_relationships
START WITH parent_object_id = l_parent_object_id
CONNECT BY PRIOR child_object_id = parent_object_id);
SELECT top_level_object_id,
top_level_object_type_id
FROM wip_sched_relationships
WHERE relationship_type = 1
AND child_object_id =
(SELECT parent_object_id
FROM wip_sched_relationships
WHERE sched_relationship_id =
l_relationship_id_tmp);
SELECT top_level_object_id,
top_level_object_type_id
FROM wip_sched_relationships
WHERE relationship_type = 1
AND child_object_id =
(SELECT child_object_id
FROM wip_sched_relationships
WHERE sched_relationship_id =
l_relationship_id_tmp);
SELECT child_object_id,
parent_object_id,
relationship_type
INTO l_child_object_id,
l_parent_object_id,
l_relationship_type
FROM WIP_SCHED_RELATIONSHIPS
WHERE sched_relationship_ID = p_relationshipID;
SELECT count(*) INTO l_count_a
FROM wip_sched_relationships
WHERE relationship_type = WIP_CONSTANTS.G_REL_TYPE_DEPENDENT
AND (parent_object_id = l_child_object_id
OR child_object_id = l_child_object_id);
/* UPDATE the TOP_LEVEL_OBJECT_ID of the subtree rooted at CHILD to be CHILD */
UPDATE wip_sched_relationships
SET top_level_object_id = l_child_object_id
WHERE SCHED_RELATIONSHIP_ID
IN
(SELECT SCHED_RELATIONSHIP_ID FROM wip_sched_relationships
WHERE relationship_type = WIP_CONSTANTS.G_REL_TYPE_CONSTRAINED
START WITH parent_object_id = l_child_object_id
AND relationship_type = WIP_CONSTANTS.G_REL_TYPE_CONSTRAINED
CONNECT BY PRIOR child_object_id = parent_object_id
AND relationship_type = WIP_CONSTANTS.G_REL_TYPE_CONSTRAINED);
/* UPDATE Top_level_object_id for all Rel type 2 relationships under Parent_object */
FOR dependent_rel_cur_rec IN dependent_rels_cur
LOOP
l_relationship_id_tmp := dependent_rel_cur_rec.sched_relationship_id;
/* UPDATE it to NULL*/
IF l_top_level_object_id_tmp1 <> l_top_level_object_id_tmp2
then
UPDATE wip_sched_relationships
SET top_level_object_id = NULL
WHERE sched_relationship_id = l_relationship_id_tmp;
/* Delete the row NOW */
DELETE FROM WIP_SCHED_RELATIONSHIPS
WHERE SCHED_RELATIONSHIP_ID = p_relationshipID ;
/* Check IF Delete fails */
IF SQL%NOTFOUND THEN
RAISE DELETE_FAIL_EXCEPTION;
WHEN DELETE_FAIL_EXCEPTION
THEN
ROLLBACK to sp_wip_wol_grp;
FND_MESSAGE.SET_name('WIP', 'WIP_WOL_DELETE_FAIL');
END deleteRow;