[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE do_update_link(
p_hierarchy_node_rec IN HIERARCHY_NODE_REC_TYPE,
x_return_status IN OUT NOCOPY VARCHAR2
);
PROCEDURE do_update_link_pvt(
p_hierarchy_node_rec IN HIERARCHY_NODE_REC_TYPE
);
SELECT *
FROM hz_temp_rel_gt
ORDER BY date1,date2;
insert into hz_temp_rel_gt(date1,date2,pc_flag) values(p_d1(i),p_d2(i),p_pc(i));
select effective_start_date,effective_end_date,'P' parent_child_flag
from hz_hierarchy_nodes
where parent_id=p_id
and parent_table_name=p_table_name
and parent_object_type=p_object_type
and hierarchy_type=p_hierarchy_type
and level_number=1
union
select effective_start_date,effective_end_date,'C' parent_child_flag
from hz_hierarchy_nodes
where child_id=p_id
and child_table_name=p_table_name
and child_object_type=p_object_type
and hierarchy_type=p_hierarchy_type
and level_number=1;
select to_date(to_char(effective_start_date,'dd/mm/yyyy')||'12:00:00 AM','dd/mm/yyyy hh:mi:ss AM'),
to_date(to_char(effective_end_date, 'dd/mm/yyyy')||'11:59:59 PM','dd/mm/yyyy hh:mi:ss PM'),
'P' parent_child_flag
from hz_hierarchy_nodes
where parent_id=p_id
and parent_table_name=p_table_name
and parent_object_type=p_object_type
and hierarchy_type=p_hierarchy_type
and level_number=1
union
select to_date(to_char(effective_start_date,'dd/mm/yyyy')||'00:00:00','dd/mm/yyyy hh24:mi:ss'),
to_date(to_char(effective_end_date, 'dd/mm/yyyy')||'23:59:59','dd/mm/yyyy hh24:mi:ss'),
'C' parent_child_flag
from hz_hierarchy_nodes
where child_id=p_id
and child_table_name=p_table_name
and child_object_type=p_object_type
and hierarchy_type=p_hierarchy_type
and level_number=1;
delete from hz_hierarchy_nodes
where parent_id=p_node_id
and parent_table_name=p_table_name
and parent_object_type=p_object_type
and hierarchy_type=p_hierarchy_type
and level_number=0;
hz_utility_v2pub.debug(p_message=>'HZ_HIERARCHY_NODES_PKG.insert_row (+) l_tp = '||l_tp||' l_lc = '||l_lc ,
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
HZ_HIERARCHY_NODES_PKG.Insert_Row(
X_ROWID => l_rowid,
X_HIERARCHY_TYPE => p_hierarchy_type,
X_PARENT_ID => p_node_id,
X_PARENT_TABLE_NAME => p_table_name,
X_PARENT_OBJECT_TYPE => p_object_type,
X_CHILD_ID => p_node_id,
X_CHILD_TABLE_NAME => p_table_name,
X_CHILD_OBJECT_TYPE => p_object_type,
X_LEVEL_NUMBER => 0,
X_TOP_PARENT_FLAG => l_tp,
X_LEAF_CHILD_FLAG => l_lc,
X_EFFECTIVE_START_DATE => l_start_date(1),
X_EFFECTIVE_END_DATE => l_end_date(1),
X_STATUS => NULL,
X_RELATIONSHIP_ID => NULL,
X_ACTUAL_CONTENT_SOURCE => p_actual_content_source
);
hz_utility_v2pub.debug(p_message=> 'HZ_HIERARCHY_NODES_PKG.insert_row (+) l_tp = '||l_tp||' l_lc = '||l_lc,
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
HZ_HIERARCHY_NODES_PKG.Insert_Row(
X_ROWID => l_rowid,
X_HIERARCHY_TYPE => p_hierarchy_type,
X_PARENT_ID => p_node_id,
X_PARENT_TABLE_NAME => p_table_name,
X_PARENT_OBJECT_TYPE => p_object_type,
X_CHILD_ID => p_node_id,
X_CHILD_TABLE_NAME => p_table_name,
X_CHILD_OBJECT_TYPE => p_object_type,
X_LEVEL_NUMBER => 0,
X_TOP_PARENT_FLAG => l_tp,
X_LEAF_CHILD_FLAG => l_lc,
X_EFFECTIVE_START_DATE => l_start_date(i)+l_const,
X_EFFECTIVE_END_DATE => l_end_date(i),
X_STATUS => NULL,
X_RELATIONSHIP_ID => NULL,
X_ACTUAL_CONTENT_SOURCE => p_actual_content_source
);
SELECT UNIQUE PARENT_ID,
PARENT_TABLE_NAME,
PARENT_OBJECT_TYPE,
LEVEL_NUMBER
/*
TOP_PARENT_FLAG,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE
*/
FROM HZ_HIERARCHY_NODES
WHERE CHILD_ID = p_hierarchy_node_rec.parent_id
AND CHILD_TABLE_NAME = p_hierarchy_node_rec.parent_table_name
AND CHILD_OBJECT_TYPE = p_hierarchy_node_rec.parent_object_type
AND HIERARCHY_TYPE = p_hierarchy_node_rec.hierarchy_type
AND (EFFECTIVE_START_DATE BETWEEN l_existing_start_date AND l_existing_end_date
OR
EFFECTIVE_END_DATE BETWEEN l_existing_start_date AND l_existing_end_date
OR
l_existing_start_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
OR
l_existing_end_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
)
AND NVL(status,'A') = 'A'
ORDER BY LEVEL_NUMBER ASC;
SELECT UNIQUE CHILD_ID,
CHILD_TABLE_NAME,
CHILD_OBJECT_TYPE,
LEVEL_NUMBER
/*
LEAF_CHILD_FLAG,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE
*/
FROM HZ_HIERARCHY_NODES
WHERE PARENT_ID = p_hierarchy_node_rec.child_id
AND PARENT_TABLE_NAME = p_hierarchy_node_rec.child_table_name
AND PARENT_OBJECT_TYPE = p_hierarchy_node_rec.child_object_type
AND HIERARCHY_TYPE = p_hierarchy_node_rec.hierarchy_type
AND (EFFECTIVE_START_DATE BETWEEN l_existing_start_date AND l_existing_end_date
OR
EFFECTIVE_END_DATE BETWEEN l_existing_start_date AND l_existing_end_date
OR
l_existing_start_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
OR
l_existing_end_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
)
AND NVL(status,'A') = 'A'
ORDER BY LEVEL_NUMBER ASC;
SELECT PARENT_ID,
PARENT_TABLE_NAME,
PARENT_OBJECT_TYPE,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE
FROM HZ_HIERARCHY_NODES a
WHERE CHILD_ID = p_child_id
AND CHILD_TABLE_NAME = p_child_table_name
AND CHILD_OBJECT_TYPE = p_child_object_type
AND HIERARCHY_TYPE = p_hierarchy_node_rec.hierarchy_type
AND (EFFECTIVE_START_DATE BETWEEN l_existing_start_date AND l_existing_end_date
OR
EFFECTIVE_END_DATE BETWEEN l_existing_start_date AND l_existing_end_date
OR
l_existing_start_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
OR
l_existing_end_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
)
AND NVL(status,'A') = 'A'
AND LEVEL_NUMBER = 1
-- Fix for Bug 5662272
-- Filter out end dated records, if there is a future record existing
AND NOT EXISTS (SELECT NULL FROM HZ_HIERARCHY_NODES b
WHERE b.PARENT_ID = a.PARENT_ID
AND b.PARENT_TABLE_NAME = a.PARENT_TABLE_NAME
AND b.PARENT_OBJECT_TYPE = a.PARENT_OBJECT_TYPE
AND b.CHILD_ID = a.CHILD_ID
AND b.CHILD_TABLE_NAME = a.CHILD_TABLE_NAME
AND b.CHILD_OBJECT_TYPE = a.CHILD_OBJECT_TYPE
AND b.HIERARCHY_TYPE = a.HIERARCHY_TYPE
AND b.LEVEL_NUMBER = a.LEVEL_NUMBER
AND NVL(b.status,'A') = 'A'
AND b.EFFECTIVE_END_DATE > a.EFFECTIVE_END_DATE
)
ORDER BY RELATIONSHIP_ID DESC;
SELECT LEVEL_NUMBER,
TOP_PARENT_FLAG,
LEAF_CHILD_FLAG,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE
FROM HZ_HIERARCHY_NODES a
WHERE PARENT_ID = p_parent_id
AND PARENT_TABLE_NAME = p_parent_table_name
AND PARENT_OBJECT_TYPE = p_parent_object_type
AND CHILD_ID = p_child_id
AND CHILD_TABLE_NAME = p_child_table_name
AND CHILD_OBJECT_TYPE = p_child_object_type
AND (EFFECTIVE_START_DATE BETWEEN l_existing_start_date AND l_existing_end_date
OR
EFFECTIVE_END_DATE BETWEEN l_existing_start_date AND l_existing_end_date
OR
l_existing_start_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
OR
l_existing_end_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
)
AND NVL(status,'A') = 'A'
-- Fix for Bug 5662272
-- For the same level, if there are 2 records, then take the latest one and filter out
-- record with lower end date. That record may have been end dated and a new one is created
AND NOT EXISTS (SELECT NULL FROM HZ_HIERARCHY_NODES b
WHERE b.PARENT_ID = a.PARENT_ID
AND b.PARENT_TABLE_NAME = a.PARENT_TABLE_NAME
AND b.PARENT_OBJECT_TYPE = a.PARENT_OBJECT_TYPE
AND b.CHILD_ID = a.CHILD_ID
AND b.CHILD_TABLE_NAME = a.CHILD_TABLE_NAME
AND b.CHILD_OBJECT_TYPE = a.CHILD_OBJECT_TYPE
AND b.HIERARCHY_TYPE = a.HIERARCHY_TYPE
AND b.LEVEL_NUMBER = a.LEVEL_NUMBER
AND NVL(b.status,'A') = 'A'
AND b.EFFECTIVE_END_DATE > a.EFFECTIVE_END_DATE
)
;
HZ_HIERARCHY_NODES_PKG.Insert_Row(
X_ROWID => l_direct_link_rowid,
X_HIERARCHY_TYPE => p_hierarchy_node_rec.hierarchy_type,
X_PARENT_ID => p_hierarchy_node_rec.parent_id,
X_PARENT_TABLE_NAME => p_hierarchy_node_rec.parent_table_name,
X_PARENT_OBJECT_TYPE => p_hierarchy_node_rec.parent_object_type,
X_CHILD_ID => p_hierarchy_node_rec.child_id,
X_CHILD_TABLE_NAME => p_hierarchy_node_rec.child_table_name,
X_CHILD_OBJECT_TYPE => p_hierarchy_node_rec.child_object_type,
X_LEVEL_NUMBER => 1,
X_TOP_PARENT_FLAG => NULL,
X_LEAF_CHILD_FLAG => NULL,
X_EFFECTIVE_START_DATE => p_hierarchy_node_rec.effective_start_date,
X_EFFECTIVE_END_DATE => p_hierarchy_node_rec.effective_end_date,
X_STATUS => p_hierarchy_node_rec.status,
X_RELATIONSHIP_ID => p_hierarchy_node_rec.relationship_id,
X_ACTUAL_CONTENT_SOURCE => p_hierarchy_node_rec.actual_content_source
);
SELECT EFFECTIVE_START_DATE, EFFECTIVE_END_DATE
INTO l_existing_start_date, l_existing_end_date
FROM HZ_HIERARCHY_NODES
WHERE PARENT_ID = p_hierarchy_node_rec.parent_id
AND CHILD_ID = p_hierarchy_node_rec.child_id
AND LEVEL_NUMBER = 1
AND RELATIONSHIP_ID = p_hierarchy_node_rec.relationship_id ;
HZ_HIERARCHY_NODES_PKG.Insert_Row(
X_ROWID => l_rowid,
X_HIERARCHY_TYPE => p_hierarchy_node_rec.hierarchy_type,
X_PARENT_ID => r_get_all_parents.parent_id,
X_PARENT_TABLE_NAME => r_get_all_parents.parent_table_name,
X_PARENT_OBJECT_TYPE => r_get_all_parents.parent_object_type,
X_CHILD_ID => r_get_all_children.child_id,
X_CHILD_TABLE_NAME => r_get_all_children.child_table_name,
X_CHILD_OBJECT_TYPE => r_get_all_children.child_object_type,
X_LEVEL_NUMBER => l_level_number,
X_TOP_PARENT_FLAG => NULL,
X_LEAF_CHILD_FLAG => NULL,
X_EFFECTIVE_START_DATE => l_effective_start_date,
X_EFFECTIVE_END_DATE => l_effective_end_date,
X_STATUS => NULL,
X_RELATIONSHIP_ID => NULL,
X_ACTUAL_CONTENT_SOURCE => p_hierarchy_node_rec.actual_content_source
);
Do_update_link procedure to traverse the tree recursively to figure out parents
at each step and then update the hierarchy nodes dates
------------------------------------------------------------------------------+*/
PROCEDURE do_update_link_pvt(
p_hierarchy_node_rec IN HIERARCHY_NODE_REC_TYPE
) IS
l_debug_prefix CONSTANT VARCHAR2(30) := '';
l_LAST_UPDATED_BY NUMBER;
l_LAST_UPDATE_DATE DATE;
l_LAST_UPDATE_LOGIN NUMBER;
SELECT CHILD_ID,
CHILD_OBJECT_TYPE,
CHILD_TABLE_NAME
FROM HZ_HIERARCHY_NODES
WHERE PARENT_ID = ll_child_id
AND PARENT_TABLE_NAME = ll_child_table
AND PARENT_OBJECT_TYPE = ll_child_object_type
AND HIERARCHY_TYPE = l_hierarchy_type
AND (EFFECTIVE_START_DATE BETWEEN l_existing_start_date AND l_existing_end_date
OR
EFFECTIVE_END_DATE BETWEEN l_existing_start_date AND l_existing_end_date
OR
l_existing_start_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
OR
l_existing_end_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
)
AND NVL(status,'A') = 'A'
ORDER BY LEVEL_NUMBER ASC;
SELECT PARENT_ID, PARENT_OBJECT_TYPE, CHILD_ID, CHILD_OBJECT_TYPE, rowid
FROM HZ_HIERARCHY_NODES a
WHERE PARENT_ID = l_parent_id
AND PARENT_TABLE_NAME = l_parent_table
AND PARENT_OBJECT_TYPE = l_parent_object_type
AND CHILD_ID = l_child_id
AND CHILD_TABLE_NAME = l_child_table
AND CHILD_OBJECT_TYPE = l_child_object_type
AND HIERARCHY_TYPE = l_hierarchy_type
AND (EFFECTIVE_START_DATE BETWEEN l_existing_start_date AND l_existing_end_date
OR
EFFECTIVE_END_DATE BETWEEN l_existing_start_date AND l_existing_end_date
OR
l_existing_start_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
OR
l_existing_end_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
)
AND NVL(status,'A') = 'A'
-- Fix for Bug 5662272
-- Here problem is, it will pick up any 1 record in random and update end date for that
-- This way it may inactivae end dated record and leave active record unchanged.
-- Change it pick up the records for which start date and end date for level 1 relationship
-- matches (most likely that will be the date for this link also.). If No such record
-- exists, then pick up the greatest effective_end_date rec (most linkely will be the active one)
-- and execute cursor only once
AND NOT EXISTS (SELECT NULL FROM HZ_HIERARCHY_NODES b
WHERE b.PARENT_ID = a.PARENT_ID
AND b.PARENT_TABLE_NAME = a.PARENT_TABLE_NAME
AND b.PARENT_OBJECT_TYPE = a.PARENT_OBJECT_TYPE
AND b.CHILD_ID = a.CHILD_ID
AND b.CHILD_TABLE_NAME = a.CHILD_TABLE_NAME
AND b.CHILD_OBJECT_TYPE = a.CHILD_OBJECT_TYPE
AND b.HIERARCHY_TYPE = a.HIERARCHY_TYPE
AND b.LEVEL_NUMBER = a.LEVEL_NUMBER
AND NVL(b.status,'A') = 'A'
AND b.EFFECTIVE_START_DATE = l_existing_start_date
AND b.EFFECTIVE_END_DATE = l_existing_end_date
AND a.ROWID <> b.ROWID
)
-- Move this condition inside cursor loop
--AND ROWNUM = 1
ORDER BY effective_end_date desc;
SELECT parent_id, parent_object_type, parent_table_name
FROM hz_hierarchy_nodes
WHERE hierarchy_type = l_hierarchy_type
AND child_table_name = l_child_table
AND child_object_type = l_child_object_type
AND child_id = l_child_id
AND level_number = 1
AND SYSDATE+0.0001 BETWEEN effective_start_date AND effective_end_date
AND NVL(status,'A') = 'A'
;
hz_utility_v2pub.debug(p_message=>'do_update_link_pvt (+)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
l_LAST_UPDATED_BY := HZ_UTILITY_V2PUB.last_updated_by;
l_LAST_UPDATE_DATE := HZ_UTILITY_V2PUB.last_update_date;
l_LAST_UPDATE_LOGIN := HZ_UTILITY_V2PUB.last_update_login;
SELECT EFFECTIVE_START_DATE,EFFECTIVE_END_DATE
INTO l_existing_start_date,l_existing_end_date
FROM HZ_HIERARCHY_NODES
WHERE PARENT_ID = p_hierarchy_node_rec.parent_id
AND CHILD_ID = p_hierarchy_node_rec.child_id
AND LEVEL_NUMBER = 1
AND RELATIONSHIP_ID = p_hierarchy_node_rec.relationship_id ;
UPDATE HZ_HIERARCHY_NODES
SET EFFECTIVE_START_DATE = NVL(p_hierarchy_node_rec.effective_start_date, EFFECTIVE_START_DATE),
EFFECTIVE_END_DATE = NVL(p_hierarchy_node_rec.effective_end_date, EFFECTIVE_END_DATE),
-- added for Bug 5662272 (This will update Status values for I which help in
-- eleminating deleted rows from all the cursors
STATUS = NVL(p_hierarchy_node_rec.status, STATUS),
LAST_UPDATED_BY = l_last_updated_by,
LAST_UPDATE_DATE = l_last_update_date,
LAST_UPDATE_LOGIN = l_last_update_login
WHERE ROWID = c_par_child_rec.ROWID;
hz_utility_v2pub.debug(p_message=>'do_update_link_pvt (-)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
END do_update_link_pvt;
| do_update_link
|
| DESCRIPTION
| updates hierarchial relationship between two nodes and corrosponding links.
|
| SCOPE - PRIVATE
|
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| ARGUMENTS : IN:
| OUT:
| IN/ OUT:
| p_hierarchy_node_rec
| x_return_status
|
| RETURNS : NONE
|
| NOTES
|
| MODIFICATION HISTORY
|
+===========================================================================*/
PROCEDURE do_update_link(
p_hierarchy_node_rec IN HIERARCHY_NODE_REC_TYPE,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
/* -- Moved below logic into procedure do_update_link_pvt for Bug 4662744 (Nishant 30-Mar-2006)
-- End dating of hierarchy in case of multiple parents was not correct
-- Bug 4902909.
-- we'll get the parents and children of the relationship,
-- based on the existing start dates.
-- This is because, if the new dates are used, the data will be picked up
-- in to the cursor only if there is some overlap between the old and new dates.
-- If the old and new date ranges are mutually exclusive, then the where
-- used for the cursors will prevent any data from being picked up.
-- Also the relationship API will not pass the existing dates.
-- these are picked up from the HZ_HIERARCHY_NODES table itself,
-- from the level 1 recordusing the parent_id, child_i and relationship_id
-- passed through the p_hierarchy_node_rec.
-- once obtained, these current dates will be used to pickup data in to the cursors.
l_existing_start_date DATE;
SELECT PARENT_ID,
PARENT_TABLE_NAME,
PARENT_OBJECT_TYPE,
LEVEL_NUMBER,
TOP_PARENT_FLAG,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE
FROM HZ_HIERARCHY_NODES
WHERE CHILD_ID = p_hierarchy_node_rec.parent_id
AND CHILD_TABLE_NAME = p_hierarchy_node_rec.parent_table_name
AND CHILD_OBJECT_TYPE = p_hierarchy_node_rec.parent_object_type
AND HIERARCHY_TYPE = p_hierarchy_node_rec.hierarchy_type
AND (EFFECTIVE_START_DATE BETWEEN l_existing_start_date AND l_existing_end_date
OR
EFFECTIVE_END_DATE BETWEEN l_existing_start_date AND l_existing_end_date
OR
l_existing_start_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
OR
l_existing_end_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
)
-- AND NVL(RELATIONSHIP_ID, p_hierarchy_node_rec.relationship_id) = p_hierarchy_node_rec.relationship_id
ORDER BY LEVEL_NUMBER ASC;
SELECT CHILD_ID,
CHILD_TABLE_NAME,
CHILD_OBJECT_TYPE,
LEVEL_NUMBER,
LEAF_CHILD_FLAG,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE
FROM HZ_HIERARCHY_NODES
WHERE PARENT_ID = p_hierarchy_node_rec.child_id
AND PARENT_TABLE_NAME = p_hierarchy_node_rec.child_table_name
AND PARENT_OBJECT_TYPE = p_hierarchy_node_rec.child_object_type
AND HIERARCHY_TYPE = p_hierarchy_node_rec.hierarchy_type
AND (EFFECTIVE_START_DATE BETWEEN l_existing_start_date AND l_existing_end_date
OR
EFFECTIVE_END_DATE BETWEEN l_existing_start_date AND l_existing_end_date
OR
l_existing_start_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
OR
l_existing_end_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
)
-- AND NVL(RELATIONSHIP_ID, p_hierarchy_node_rec.relationship_id) = p_hierarchy_node_rec.relationship_id
ORDER BY LEVEL_NUMBER ASC;
/* -- Moved below logic into procedure do_update_link_pvt for Bug 4662744 (Nishant 30-Mar-2006)
-- End dating of hierarchy in case of multiple parents was not correct
Select EFFECTIVE_START_DATE,EFFECTIVE_END_DATE into l_existing_start_date,l_existing_end_date
from HZ_HIERARCHY_NODES
where PARENT_ID=p_hierarchy_node_rec.parent_id
AND CHILD_ID=p_hierarchy_node_rec.child_id
AND LEVEL_NUMBER=1
AND RELATIONSHIP_ID=p_hierarchy_node_rec.relationship_id ;
UPDATE HZ_HIERARCHY_NODES
SET EFFECTIVE_START_DATE = NVL(p_hierarchy_node_rec.effective_start_date, EFFECTIVE_START_DATE),
EFFECTIVE_END_DATE = NVL(p_hierarchy_node_rec.effective_end_date, EFFECTIVE_END_DATE),
LAST_UPDATED_BY = HZ_UTILITY_V2PUB.last_updated_by,
LAST_UPDATE_DATE = HZ_UTILITY_V2PUB.last_update_date,
LAST_UPDATE_LOGIN = HZ_UTILITY_V2PUB.last_update_login
WHERE PARENT_ID = r_get_all_parents.parent_id
AND PARENT_TABLE_NAME = r_get_all_parents.parent_table_name
AND PARENT_OBJECT_TYPE = r_get_all_parents.parent_object_type
AND CHILD_ID = r_get_all_children.child_id
AND CHILD_TABLE_NAME = r_get_all_children.child_table_name
AND CHILD_OBJECT_TYPE = r_get_all_children.child_object_type
AND HIERARCHY_TYPE = p_hierarchy_node_rec.hierarchy_type
AND (EFFECTIVE_START_DATE BETWEEN l_existing_start_date AND l_existing_end_date
OR
EFFECTIVE_END_DATE BETWEEN l_existing_start_date AND l_existing_end_date
OR
l_existing_start_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
OR
l_existing_end_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
);
do_update_link_pvt(p_hierarchy_node_rec);
END do_update_link;
* PROCEDURE update_link
*
* DESCRIPTION
* Updates a hierarchial relationship between two nodes.
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
*
* ARGUMENTS
* IN:
* p_init_msg_list Initialize message stack if it is set to
* FND_API.G_TRUE. Default is FND_API.G_FALSE.
* p_hierarchy_node_rec Hierarchy node record.
* IN/OUT:
* OUT:
* x_return_status Return status after the call. The status can
* be FND_API.G_RET_STS_SUCCESS (success),
* FND_API.G_RET_STS_ERROR (error),
* FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
* x_msg_count Number of messages in message stack.
* x_msg_data Message text if x_msg_count is 1.
*
* NOTES
*
* MODIFICATION HISTORY
*
* 31-JAN-00 Indrajit Sen o Created
*
*/
PROCEDURE update_link(
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_hierarchy_node_rec IN HIERARCHY_NODE_REC_TYPE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_debug_prefix VARCHAR2(30) := '';
SAVEPOINT update_link;
hz_utility_v2pub.debug(p_message=>'update_link (+)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
do_update_link(p_hierarchy_node_rec,
x_return_status);
hz_utility_v2pub.debug(p_message=>'update_link (-)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
ROLLBACK TO update_link;
hz_utility_v2pub.debug(p_message=> 'l_line_number = '||l_line_number||' update_link (-)' ,
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
ROLLBACK TO update_link;
hz_utility_v2pub.debug(p_message=> 'l_line_number = '||l_line_number||' update_link (-)' ,
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
ROLLBACK TO update_link;
hz_utility_v2pub.debug(p_message=> 'l_line_number = '||l_line_number||' update_link (-)' ,
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
END update_link;
SELECT a.ROWID row_id, a.*
FROM HZ_RELATIONSHIPS a
WHERE RELATIONSHIP_TYPE = p_rel_type
ORDER BY RELATIONSHIP_ID;
SELECT *
FROM HZ_RELATIONSHIP_TYPES
WHERE RELATIONSHIP_TYPE = p_rel_type
AND FORWARD_REL_CODE = p_forward_rel_code
AND SUBJECT_TYPE = p_subject_type
AND OBJECT_TYPE = p_object_type;
SELECT SUBJECT_ID,
SUBJECT_TABLE_NAME,
SUBJECT_TYPE
FROM HZ_RELATIONSHIPS
WHERE OBJECT_ID = p_child_id
AND OBJECT_TABLE_NAME = p_child_table_name
AND OBJECT_TYPE = p_child_object_type
AND RELATIONSHIP_TYPE = p_rel_type
AND DIRECTION_CODE = 'P'
AND (START_DATE BETWEEN NVL(p_start_date, SYSDATE)
AND NVL(p_end_date, TO_DATE('31-12-4712 00:00:01', 'DD-MM-YYYY HH24:MI:SS'))
OR
END_DATE BETWEEN NVL(p_start_date, SYSDATE)
AND NVL(p_end_date, TO_DATE('31-12-4712 00:00:01', 'DD-MM-YYYY HH24:MI:SS'))
OR
NVL(p_start_date, SYSDATE) BETWEEN START_DATE AND END_DATE
OR
NVL(p_end_date, TO_DATE('31-12-4712 00:00:01', 'DD-MM-YYYY HH24:MI:SS')) BETWEEN START_DATE AND END_DATE
);
SELECT 1 INTO l_count
FROM HZ_RELATIONSHIPS
WHERE OBJECT_ID = l_child_id
AND OBJECT_TABLE_NAME = l_child_table_name
AND OBJECT_TYPE = l_child_object_type
AND RELATIONSHIP_TYPE = r1.relationship_type
AND DIRECTION_CODE = 'P'
AND (START_DATE BETWEEN NVL(r1.start_date, SYSDATE)
AND NVL(r1.end_date, TO_DATE('31-12-4712 00:00:01', 'DD-MM-YYYY HH24:MI:SS'))
OR
END_DATE BETWEEN NVL(r1.start_date, SYSDATE)
AND NVL(r1.end_date, TO_DATE('31-12-4712 00:00:01', 'DD-MM-YYYY HH24:MI:SS'))
OR
NVL(r1.start_date, SYSDATE) BETWEEN START_DATE AND END_DATE
OR
NVL(r1.end_date, TO_DATE('31-12-4712 00:00:01', 'DD-MM-YYYY HH24:MI:SS')) BETWEEN START_DATE AND END_DATE
);
UPDATE HZ_RELATIONSHIPS SET DIRECTION_CODE = 'P' WHERE ROWID = r1.row_id;
UPDATE HZ_RELATIONSHIPS SET DIRECTION_CODE = 'C' WHERE ROWID = r1.row_id;
UPDATE HZ_RELATIONSHIPS SET DIRECTION_CODE = 'C' WHERE ROWID = r1.row_id;
UPDATE HZ_RELATIONSHIPS SET DIRECTION_CODE = 'P' WHERE ROWID = r1.row_id;
UPDATE HZ_RELATIONSHIP_TYPES
SET HIERARCHICAL_FLAG = 'Y',
MULTIPLE_PARENT_ALLOWED = NVL(p_multi_parent_allowed, 'N'),
INCL_UNRELATED_ENTITIES = NVL(p_incl_unrelated_entities, 'N')
WHERE RELATIONSHIP_TYPE = p_rel_type;