[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select to_char(sysdate,'YY-MON-DD HH:MI:SS')
into l_start_time
from dual ;
l_non_visited_tbl.DELETE(l_non_visited_index);
select to_char(sysdate,'YY-MON-DD HH:MI:SS')
into l_end_time
from dual ;
SELECT relationship_id ,
relationship_type_code ,
object_id ,
subject_id ,
position_reference ,
active_start_date ,
active_end_date ,
display_order ,
mandatory_flag ,
context ,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
object_version_number
FROM csi_ii_relationships
WHERE (subject_id = p_instance_id OR object_id = p_instance_id )
AND relationship_type_code = 'CONNECTED-TO'
AND nvl(active_start_date,creation_date) <= NVL(l_time_stamp,nvl(active_start_date,creation_date))
AND DECODE(p_active_relationship_only,FND_API.G_TRUE,NVL((active_end_date),c_sysdate+1),c_sysdate+1) > NVL((l_time_stamp),c_sysdate) ;
SELECT SYSDATE
INTO l_sysdate
FROM dual;
SELECT subject_id instance_id
FROM csi_ii_relationships
WHERE (subject_id = p_instance_id OR object_id = p_instance_id)
AND relationship_type_code = 'CONNECTED-TO'
AND nvl(active_start_date,creation_date) <= NVL(l_time_stamp,nvl(active_start_date,creation_date))
AND DECODE(p_active_relationship_only,FND_API.G_TRUE,NVL((active_end_date),c_sysdate+1),c_sysdate+1) > NVL((l_time_stamp),c_sysdate)
UNION ALL
SELECT object_id instance_id
FROM csi_ii_relationships
WHERE (subject_id = p_instance_id OR object_id = p_instance_id)
AND relationship_type_code = 'CONNECTED-TO'
AND nvl(active_start_date,creation_date) <= NVL(l_time_stamp,nvl(active_start_date,creation_date))
AND DECODE(p_active_relationship_only,FND_API.G_TRUE,NVL((active_end_date),c_sysdate+1),c_sysdate+1) > NVL((l_time_stamp),c_sysdate) ;
SELECT SYSDATE
INTO l_sysdate
FROM DUAL;
x_neighbor_inst_tbl.DELETE ;
PROCEDURE gen_select(
p_relship_query_rec IN csi_datastructures_pub.relationship_query_rec,
x_select_cl OUT NOCOPY VARCHAR2
)
IS
l_table_name VARCHAR2(30);
x_select_cl := 'SELECT relationship_id,relationship_type_code,object_id,subject_id'
||',nvl((select '||'''Y'''|| ' from csi_ii_relationships where object_id = ciir.subject_id and relationship_type_code = ciir.relationship_type_code and rownum=1), '||'''N'''||') subject_has_child,'
||'position_reference,active_start_date,active_end_date,display_order,mandatory_flag,context,attribute1,attribute2,attribute3,attribute4,attribute5,attribute6,attribute7,'
||'attribute8,attribute9,attribute10,attribute11,attribute12,attribute13,attribute14'
||',attribute15,object_version_number FROM csi_ii_relationships ciir';
END gen_select;
SELECT instr(p_rec_item, '%', 1, 1)
FROM dual;
SELECT instr(p_rec_item, '_', 1, 1)
FROM dual;
SELECT max(min(to_char(a.transaction_date,'dd-mon-rr hh24:mi:ss')))
INTO l_f_date
FROM csi_transactions a,csi_ii_relationships b,csi_ii_relationships_h c
WHERE b.relationship_id = c.relationship_id
AND c.transaction_id = a.transaction_id
AND c.full_dump_flag = 'Y'
AND a.transaction_date <= p_time_stamp
AND c.relationship_id = p_relationship_id
GROUP BY to_char(a.transaction_date,'dd-mon-rr hh24:mi:ss');
SELECT max(min(to_char(a.transaction_date,'dd-mon-rr hh24:mi:ss')))
INTO l_t_date
FROM csi_transactions a,csi_ii_relationships b,csi_ii_relationships_h c
WHERE b.relationship_id = c.relationship_id
AND c.transaction_id = a.transaction_id
AND a.transaction_date<= p_time_stamp
AND c.relationship_id = p_relationship_id
GROUP BY to_char(a.transaction_date,'dd-mon-rr hh24:mi:ss');
SELECT c.old_subject_id
,c.new_subject_id
,c.old_position_reference
,c.new_position_reference
,c.old_active_start_date
,c.new_active_start_date
,c.old_active_end_date
,c.new_active_end_date
,c.old_mandatory_flag
,c.new_mandatory_flag
,c.old_context
,c.new_context
,c.old_attribute1
,c.new_attribute1
,c.old_attribute2
,c.new_attribute2
,c.old_attribute3
,c.new_attribute3
,c.old_attribute4
,c.new_attribute4
,c.old_attribute5
,c.new_attribute5
,c.old_attribute6
,c.new_attribute6
,c.old_attribute7
,c.new_attribute7
,c.old_attribute8
,c.new_attribute8
,c.old_attribute9
,c.new_attribute9
,c.old_attribute10
,c.new_attribute10
,c.old_attribute11
,c.new_attribute11
,c.old_attribute12
,c.new_attribute12
,c.old_attribute13
,c.new_attribute13
,c.old_attribute14
,c.new_attribute14
,c.old_attribute15
,c.new_attribute15
,c.full_dump_flag
FROM csi_transactions a,csi_ii_relationships b,csi_ii_relationships_h c
WHERE b.relationship_id = c.relationship_id
AND c.transaction_id = a.transaction_id
AND c.relationship_id = p_relationship_id
-- AND a.transaction_date BETWEEN fnd_date.canonical_to_date(p_f_time_stamp) --to_date(p_f_time_stamp,'dd-mon-rr hh24:mi:ss')
-- AND fnd_date.canonical_to_date(p_t_time_stamp) --to_date(p_t_time_stamp,'dd-mon-rr hh24:mi:ss')
AND a.transaction_date BETWEEN to_date(p_f_time_stamp,'dd/mm/rr hh24:mi:ss')
AND to_date(p_t_time_stamp,'dd/mm/rr hh24:mi:ss')
ORDER BY to_char(a.transaction_date,'dd-mon-rr hh24:mi:ss') ;
SELECT max(to_char(a.transaction_date,'dd-mon-rr hh24:mi:ss'))
INTO l_to_date
FROM csi_transactions a,csi_ii_relationships_h b
WHERE a.transaction_id=b.transaction_id
AND b.relationship_id=p_rel_rec.relationship_id;
select relationship_id,relationship_type_code,object_id,subject_id,position_reference,
active_start_date,active_end_date,display_order,mandatory_flag,context,
attribute1,attribute2,attribute3,attribute4,attribute5,attribute6,attribute7,attribute8,
attribute9,attribute10,attribute11,attribute12,attribute13,attribute14,attribute15,
object_version_number
from CSI_II_RELATIONSHIPS cir
where cir.object_id = p_object_id;
l_rel_tbl_next_lvl.delete;
l_rel_tbl.DELETE;
select relationship_id,relationship_type_code,object_id,subject_id,position_reference,
active_start_date,active_end_date,display_order,mandatory_flag,context,
attribute1,attribute2,attribute3,attribute4,attribute5,attribute6,attribute7,attribute8,
attribute9,attribute10,attribute11,attribute12,attribute13,attribute14,attribute15,
object_version_number
from CSI_II_RELATIONSHIPS cir
where cir.object_id = p_object_id
and cir.relationship_type_code = p_rel_type_code
and DECODE(l_active_relationship_only,FND_API.G_TRUE,NVL((cir.active_end_date),c_sysdate+1),c_sysdate+1) > sysdate
and EXISTS (select 'x'
from CSI_ITEM_INSTANCES csi
where csi.instance_id = cir.subject_id
and DECODE(l_active_instances_only,FND_API.G_TRUE,NVL((csi.active_end_date),c_sysdate+1),c_sysdate+1) > sysdate);
select relationship_id,relationship_type_code,object_id,subject_id,position_reference,
active_start_date,active_end_date,display_order,mandatory_flag,context,
attribute1,attribute2,attribute3,attribute4,attribute5,attribute6,attribute7,attribute8,
attribute9,attribute10,attribute11,attribute12,attribute13,attribute14,attribute15,
object_version_number
from CSI_II_RELATIONSHIPS cir
where cir.object_id = p_object_id
and cir.relationship_type_code = p_rel_type_code
and DECODE(l_active_relationship_only,FND_API.G_TRUE,NVL((cir.active_end_date),c_sysdate+1),c_sysdate+1) > sysdate
and EXISTS (select 'x'
from CSI_ITEM_INSTANCES csi
where csi.instance_id = cir.subject_id
and csi.config_inst_hdr_id is not null
and csi.config_inst_item_id is not null
and csi.config_inst_rev_num is not null
and DECODE(l_active_instances_only,FND_API.G_TRUE,NVL((csi.active_end_date),c_sysdate+1),c_sysdate+1) > sysdate);
select relationship_id,relationship_type_code,object_id,subject_id,position_reference,
active_start_date,active_end_date,display_order,mandatory_flag,context,
attribute1,attribute2,attribute3,attribute4,attribute5,attribute6,attribute7,attribute8,
attribute9,attribute10,attribute11,attribute12,attribute13,attribute14,attribute15,
object_version_number
from CSI_II_RELATIONSHIPS cir
where cir.object_id = p_object_id
and DECODE(l_active_relationship_only,FND_API.G_TRUE,NVL((cir.active_end_date),c_sysdate+1),c_sysdate+1) > sysdate
and EXISTS (select 'x'
from CSI_ITEM_INSTANCES csi
where csi.instance_id = cir.subject_id
and DECODE(l_active_instances_only,FND_API.G_TRUE,NVL((csi.active_end_date),c_sysdate+1),c_sysdate+1) > sysdate);
select relationship_id,relationship_type_code,object_id,subject_id,position_reference,
active_start_date,active_end_date,display_order,mandatory_flag,context,
attribute1,attribute2,attribute3,attribute4,attribute5,attribute6,attribute7,attribute8,
attribute9,attribute10,attribute11,attribute12,attribute13,attribute14,attribute15,
object_version_number
from CSI_II_RELATIONSHIPS cir
where cir.subject_id = p_subject_id
and cir.relationship_type_code = p_rel_type_code
and DECODE(l_active_relationship_only,FND_API.G_TRUE,NVL((cir.active_end_date),c_sysdate+1),c_sysdate+1) > sysdate
and EXISTS (select 'x'
from CSI_ITEM_INSTANCES csi
where csi.instance_id = cir.subject_id
and DECODE(l_active_instances_only,FND_API.G_TRUE,NVL((csi.active_end_date),c_sysdate+1),c_sysdate+1) > sysdate);
select relationship_id,relationship_type_code,object_id,subject_id,position_reference,
active_start_date,active_end_date,display_order,mandatory_flag,context,
attribute1,attribute2,attribute3,attribute4,attribute5,attribute6,attribute7,attribute8,
attribute9,attribute10,attribute11,attribute12,attribute13,attribute14,attribute15,
object_version_number
from CSI_II_RELATIONSHIPS cir
where cir.relationship_id = p_relationship_id
and DECODE(l_active_relationship_only,FND_API.G_TRUE,NVL((cir.active_end_date),c_sysdate+1),c_sysdate+1) > sysdate
and EXISTS (select 'x'
from CSI_ITEM_INSTANCES csi
where csi.instance_id = cir.subject_id
and DECODE(l_active_instances_only,FND_API.G_TRUE,NVL((csi.active_end_date),c_sysdate+1),c_sysdate+1) > sysdate);
select relationship_id,relationship_type_code,object_id,subject_id,position_reference,
active_start_date,active_end_date,display_order,mandatory_flag,context,
attribute1,attribute2,attribute3,attribute4,attribute5,attribute6,attribute7,attribute8,
attribute9,attribute10,attribute11,attribute12,attribute13,attribute14,attribute15,
object_version_number
from CSI_II_RELATIONSHIPS cir
where cir.relationship_id = NVL(p_relationship_id,cir.relationship_id)
and cir.object_id = NVL(p_object_id,cir.object_id)
and cir.relationship_type_code = NVL(p_rel_type_code,cir.relationship_type_code)
and cir.subject_id = NVL(p_subject_id,cir.subject_id)
and DECODE(l_active_relationship_only,FND_API.G_TRUE,NVL((cir.active_end_date),c_sysdate+1),c_sysdate+1) > sysdate
and EXISTS (select 'x'
from CSI_ITEM_INSTANCES csi
where csi.instance_id = cir.subject_id
and DECODE(l_active_instances_only,FND_API.G_TRUE,NVL((csi.active_end_date),c_sysdate+1),c_sysdate+1) > sysdate);
select sysdate
into l_sysdate
from dual;
select 'Y'
into p_rel_tbl(l_ctr).subject_has_child
from CSI_II_RELATIONSHIPS
where object_id = rel.subject_id
and relationship_type_code = rel.relationship_type_code
and rownum = 1;
select 'Y'
into p_rel_tbl(l_ctr).subject_has_child
from CSI_II_RELATIONSHIPS
where object_id = rel.subject_id
and relationship_type_code = rel.relationship_type_code
and rownum = 1;
select 'Y'
into p_rel_tbl(l_ctr).subject_has_child
from CSI_II_RELATIONSHIPS
where object_id = rel.subject_id
and relationship_type_code = rel.relationship_type_code
and rownum = 1;
select 'Y'
into p_rel_tbl(l_ctr).subject_has_child
from CSI_II_RELATIONSHIPS
where object_id = rel.subject_id
and relationship_type_code = rel.relationship_type_code
and rownum = 1;
select 'Y'
into p_rel_tbl(l_ctr).subject_has_child
from CSI_II_RELATIONSHIPS
where object_id = rel.subject_id
and relationship_type_code = rel.relationship_type_code
and rownum = 1;
select 'Y'
into p_rel_tbl(l_ctr).subject_has_child
from CSI_II_RELATIONSHIPS
where object_id = rel.subject_id
and relationship_type_code = rel.relationship_type_code
and rownum = 1;
l_rel_tbl_next_lvl.delete;
l_rel_tbl.DELETE;
p_rel_tbl.DELETE;
l_rel_tbl_final.DELETE(1);
l_rel_tbl_final.DELETE(rel);
select object_id
into l_object_id
from CSI_II_RELATIONSHIPS
where subject_id = p_subject_id
and relationship_type_code = p_rel_type_code
and ((active_end_date is null) or (active_end_date > sysdate));
select object_id
into l_object_id
from CSI_II_RELATIONSHIPS
where subject_id = l_subject_id
and relationship_type_code = p_rel_type_code
and ((active_end_date is null) or (active_end_date > sysdate));
SELECT * FROM csi_ii_relationships
WHERE object_id=instance_id;
SELECT subject_id
from csi_ii_relationships
connect by prior subject_id = object_id
start with subject_id = p_instance_id;
SELECT relationship_id
,active_end_date
FROM csi_ii_relationships
WHERE relationship_type_code='COMPONENT-OF'
START WITH object_id = p_object_id
CONNECT BY object_id = PRIOR subject_id;
SELECT last_purge_date
INTO l_last_purge_date
FROM CSI_ITEM_INSTANCES
WHERE rownum < 2;
SELECT 'Y'
INTO l_relationship_tbl(i).subject_has_child
FROM CSI_II_RELATIONSHIPS
WHERE OBJECT_ID = l_relationship_tbl(i).subject_id
AND CREATION_DATE <= p_time_stamp
AND (ACTIVE_END_DATE IS NULL OR ACTIVE_END_DATE >= p_time_stamp)
AND ROWNUM < 2;
l_exp_inst_tbl.DELETE;
Select 'x'
into l_exists
from CSI_ITEM_INSTANCES
where instance_id = l_relationship_tbl(rel_row).subject_id
and nvl(active_end_date,(sysdate+1)) < sysdate;
dbms_sql.parse(l_cur_get_relations, l_select_cl||l_relations_where , dbms_sql.native);
SELECT last_purge_date
INTO l_last_purge_date
FROM CSI_ITEM_INSTANCES
WHERE rownum < 2;
SELECT 'x'
INTO l_found
FROM csi_ii_relationships_h
WHERE relationship_id=l_rel_tbl(p_time_csr).relationship_id;
SELECT 'Y'
INTO l_relationship_tbl(l_returned_rec_count).subject_has_child
FROM CSI_II_RELATIONSHIPS
WHERE OBJECT_ID = l_relationship_tbl(l_returned_rec_count).subject_id
AND CREATION_DATE <= p_time_stamp
AND (ACTIVE_END_DATE IS NULL OR ACTIVE_END_DATE >= p_time_stamp)
AND ROWNUM < 2;
l_exp_inst_tbl.DELETE;
Select 'x'
into l_exists
from CSI_ITEM_INSTANCES
where instance_id = l_relationship_tbl(rel_row).subject_id
and nvl(active_end_date,(sysdate+1)) < sysdate;
l_temp_relationship_tbl.DELETE;
l_exp_act_tbl.delete;
x_relationship_tbl.DELETE;
/* SELECT configurator_id
INTO l_configurator_id
FROM csi_ii_relationships
WHERE relationship_id=p_relationship_id;
SELECT 'x'
INTO l_dummy
FROM csi_ii_relationships
WHERE (( subject_id=p_object_id AND object_id=p_subject_id)
OR (subject_id=p_subject_id AND object_id=p_object_id))
AND relationship_type_code = p_relship_type_code
AND (active_end_date IS NULL OR active_end_date > SYSDATE)
AND ROWNUM = 1 ;
ELSIF p_mode='UPDATE'
THEN
SELECT 'x'
INTO l_dummy
FROM csi_ii_relationships
WHERE (( subject_id=p_object_id AND object_id=p_subject_id)
OR (subject_id=p_subject_id AND object_id=p_object_id))
AND relationship_type_code = p_relship_type_code
AND (active_end_date IS NULL OR active_end_date > SYSDATE)
AND relationship_id<>p_relationship_id
AND ROWNUM = 1 ;
SELECT COUNT(*)
INTO l_count
FROM csi_ii_relationships
WHERE (subject_id=p_instance_id OR object_id=p_instance_id)
AND relationship_type_code = 'CONNECTED-TO'
AND (active_end_date IS NULL OR active_end_date > SYSDATE);
ELSIF p_mode='UPDATE'
THEN
-- Code for update will check other than itself
-- and during unexpiring of an expired relationship.
SELECT COUNT(*)
INTO l_count
FROM csi_ii_relationships
WHERE (subject_id=p_instance_id OR object_id=p_instance_id)
AND relationship_type_code = 'CONNECTED-TO'
AND relationship_id <> p_relationship_id
AND (active_end_date IS NULL OR active_end_date > SYSDATE);
SELECT msi.ib_item_instance_class
INTO l_link
FROM csi_item_instances cii
,mtl_system_items_b msi
WHERE cii.instance_id = p_instance_id
AND msi.inventory_item_id=cii.inventory_item_id
AND msi.organization_id=cii.last_vld_organization_id
AND msi.ib_item_instance_class = l_class;
SELECT 'x'
INTO l_dummy
FROM csi_ii_relationships
WHERE subject_id=p_subject_id
AND relationship_type_code = p_relship_type_code
AND (active_end_date IS NULL OR active_end_date > SYSDATE)
--AND (SYSDATE BETWEEN NVL(active_start_date, SYSDATE) AND NVL(active_end_date, SYSDATE))
AND ROWNUM=1;
ELSIF p_mode='UPDATE'
THEN
BEGIN
SELECT 'x'
INTO l_dummy
FROM csi_ii_relationships
WHERE subject_id=p_subject_id
AND relationship_type_code = p_relship_type_code
AND relationship_id <> p_relationship_id
AND (active_end_date IS NULL OR active_end_date > SYSDATE)
--AND (SYSDATE BETWEEN NVL(active_start_date, SYSDATE) AND NVL(active_end_date, SYSDATE))
AND ROWNUM=1;
SELECT subject_id,relationship_type_code
INTO l_subject_id,l_relship_type_code
FROM csi_ii_relationships
WHERE relationship_id=p_relationship_id
AND subject_id =p_subject_id;
SELECT object_id,relationship_type_code
INTO l_object_id,l_relship_type_code
FROM csi_ii_relationships
WHERE relationship_id=p_relationship_id;
fnd_message.set_name('CSI','CSI_CANNOT_UPDATE');
SELECT 'x'
FROM csi_ii_relationships
WHERE subject_id = p_object_id
AND relationship_type_code = p_relationship_type_code
START WITH object_id = p_subject_id
CONNECT BY object_id = PRIOR subject_id;
SELECT relationship_history_id
,relationship_id
,transaction_id
,old_subject_id
,new_subject_id
,old_position_reference
,new_position_reference
,old_active_start_date
,new_active_start_date
,old_active_end_date
,new_active_end_date
,old_mandatory_flag
,new_mandatory_flag
,old_context
,new_context
,old_attribute1
,new_attribute1
,old_attribute2
,new_attribute2
,old_attribute3
,new_attribute3
,old_attribute4
,new_attribute4
,old_attribute5
,new_attribute5
,old_attribute6
,new_attribute6
,old_attribute7
,new_attribute7
,old_attribute8
,new_attribute8
,old_attribute9
,new_attribute9
,old_attribute10
,new_attribute10
,old_attribute11
,new_attribute11
,old_attribute12
,new_attribute12
,old_attribute13
,new_attribute13
,old_attribute14
,new_attribute14
,old_attribute15
,new_attribute15
,full_dump_flag
,object_version_number
FROM csi_ii_relationships_h
WHERE csi_ii_relationships_h.relationship_history_id = p_rel_hist_id
FOR UPDATE OF object_version_number;
SELECT relationship_history_id
INTO l_rel_hist_id
FROM csi_ii_relationships_h h
WHERE h.transaction_id = p_transaction_id
AND h.relationship_id = p_old_relship_rec.relationship_id;
csi_ii_relationships_h_pkg.update_row(
p_relationship_history_id => l_rel_hist_id ,
p_relationship_id => fnd_api.g_miss_num ,
p_transaction_id => fnd_api.g_miss_num ,
p_old_subject_id => fnd_api.g_miss_num ,
p_new_subject_id => l_new_relship_rec.subject_id ,
p_old_position_reference => fnd_api.g_miss_char ,
p_new_position_reference => l_new_relship_rec.position_reference,
p_old_active_start_date => fnd_api.g_miss_date ,
p_new_active_start_date => l_new_relship_rec.active_start_date ,
p_old_active_end_date => fnd_api.g_miss_date ,
p_new_active_end_date => l_new_relship_rec.active_end_date ,
p_old_mandatory_flag => fnd_api.g_miss_char ,
p_new_mandatory_flag => l_new_relship_rec.mandatory_flag ,
p_old_context => fnd_api.g_miss_char ,
p_new_context => l_new_relship_rec.context ,
p_old_attribute1 => fnd_api.g_miss_char ,
p_new_attribute1 => l_new_relship_rec.attribute1 ,
p_old_attribute2 => fnd_api.g_miss_char ,
p_new_attribute2 => l_new_relship_rec.attribute2 ,
p_old_attribute3 => fnd_api.g_miss_char ,
p_new_attribute3 => l_new_relship_rec.attribute3 ,
p_old_attribute4 => fnd_api.g_miss_char ,
p_new_attribute4 => l_new_relship_rec.attribute4 ,
p_old_attribute5 => fnd_api.g_miss_char ,
p_new_attribute5 => l_new_relship_rec.attribute5 ,
p_old_attribute6 => fnd_api.g_miss_char ,
p_new_attribute6 => l_new_relship_rec.attribute6 ,
p_old_attribute7 => fnd_api.g_miss_char ,
p_new_attribute7 => l_new_relship_rec.attribute7 ,
p_old_attribute8 => fnd_api.g_miss_char ,
p_new_attribute8 => l_new_relship_rec.attribute8 ,
p_old_attribute9 => fnd_api.g_miss_char ,
p_new_attribute9 => l_new_relship_rec.attribute9 ,
p_old_attribute10 => fnd_api.g_miss_char ,
p_new_attribute10 => l_new_relship_rec.attribute10 ,
p_old_attribute11 => fnd_api.g_miss_char ,
p_new_attribute11 => l_new_relship_rec.attribute11 ,
p_old_attribute12 => fnd_api.g_miss_char ,
p_new_attribute12 => l_new_relship_rec.attribute12 ,
p_old_attribute13 => fnd_api.g_miss_char ,
p_new_attribute13 => l_new_relship_rec.attribute13 ,
p_old_attribute14 => fnd_api.g_miss_char ,
p_new_attribute14 => l_new_relship_rec.attribute14 ,
p_old_attribute15 => fnd_api.g_miss_char ,
p_new_attribute15 => l_new_relship_rec.attribute15 ,
p_full_dump_flag => fnd_api.g_miss_char ,
p_created_by => fnd_api.g_miss_num, -- fnd_global.user_id,
p_creation_date => fnd_api.g_miss_date ,
p_last_updated_by => fnd_global.user_id ,
p_last_update_date => SYSDATE ,
p_last_update_login => fnd_global.conc_login_id ,
p_object_version_number => fnd_api.g_miss_num );
csi_ii_relationships_h_pkg.update_row(
p_relationship_history_id => l_rel_hist_id ,
p_relationship_id => fnd_api.g_miss_num ,
p_transaction_id => fnd_api.g_miss_num ,
p_old_subject_id => l_rel_hist_csr.old_subject_id ,
p_new_subject_id => l_rel_hist_csr.new_subject_id ,
p_old_position_reference => l_rel_hist_csr.old_position_reference,
p_new_position_reference => l_rel_hist_csr.new_position_reference,
p_old_active_start_date => l_rel_hist_csr.old_active_start_date ,
p_new_active_start_date => l_rel_hist_csr.new_active_start_date ,
p_old_active_end_date => l_rel_hist_csr.old_active_end_date ,
p_new_active_end_date => l_rel_hist_csr.new_active_end_date ,
p_old_mandatory_flag => l_rel_hist_csr.old_mandatory_flag ,
p_new_mandatory_flag => l_rel_hist_csr.new_mandatory_flag ,
p_old_context => l_rel_hist_csr.old_context ,
p_new_context => l_rel_hist_csr.new_context ,
p_old_attribute1 => l_rel_hist_csr.old_attribute1 ,
p_new_attribute1 => l_rel_hist_csr.new_attribute1 ,
p_old_attribute2 => l_rel_hist_csr.old_attribute2 ,
p_new_attribute2 => l_rel_hist_csr.new_attribute2 ,
p_old_attribute3 => l_rel_hist_csr.old_attribute3 ,
p_new_attribute3 => l_rel_hist_csr.new_attribute3 ,
p_old_attribute4 => l_rel_hist_csr.old_attribute4 ,
p_new_attribute4 => l_rel_hist_csr.new_attribute4 ,
p_old_attribute5 => l_rel_hist_csr.old_attribute5 ,
p_new_attribute5 => l_rel_hist_csr.new_attribute5 ,
p_old_attribute6 => l_rel_hist_csr.old_attribute6 ,
p_new_attribute6 => l_rel_hist_csr.new_attribute6 ,
p_old_attribute7 => l_rel_hist_csr.old_attribute7 ,
p_new_attribute7 => l_rel_hist_csr.new_attribute7 ,
p_old_attribute8 => l_rel_hist_csr.old_attribute8 ,
p_new_attribute8 => l_rel_hist_csr.new_attribute8 ,
p_old_attribute9 => l_rel_hist_csr.old_attribute9 ,
p_new_attribute9 => l_rel_hist_csr.new_attribute9 ,
p_old_attribute10 => l_rel_hist_csr.old_attribute10 ,
p_new_attribute10 => l_rel_hist_csr.new_attribute10 ,
p_old_attribute11 => l_rel_hist_csr.old_attribute11 ,
p_new_attribute11 => l_rel_hist_csr.new_attribute11 ,
p_old_attribute12 => l_rel_hist_csr.old_attribute12 ,
p_new_attribute12 => l_rel_hist_csr.new_attribute12 ,
p_old_attribute13 => l_rel_hist_csr.old_attribute13 ,
p_new_attribute13 => l_rel_hist_csr.new_attribute13 ,
p_old_attribute14 => l_rel_hist_csr.old_attribute14 ,
p_new_attribute14 => l_rel_hist_csr.new_attribute14 ,
p_old_attribute15 => l_rel_hist_csr.old_attribute15 ,
p_new_attribute15 => l_rel_hist_csr.new_attribute15 ,
p_full_dump_flag => fnd_api.g_miss_char ,
p_created_by => fnd_api.g_miss_num ,
p_creation_date => fnd_api.g_miss_date ,
p_last_updated_by => fnd_global.user_id ,
p_last_update_date => SYSDATE ,
p_last_update_login => fnd_global.conc_login_id ,
p_object_version_number => fnd_api.g_miss_num );
csi_ii_relationships_h_pkg.insert_row(
px_relationship_history_id => l_relship_hist_rec.relationship_history_id,
p_relationship_id => l_old_relship_rec.relationship_id,
p_transaction_id => l_transaction_id,
p_old_subject_id => l_old_relship_rec.subject_id,
p_new_subject_id => l_new_relship_rec.subject_id,
p_old_position_reference => l_old_relship_rec.position_reference,
p_new_position_reference => l_new_relship_rec.position_reference,
p_old_active_start_date => l_old_relship_rec.active_start_date,
p_new_active_start_date => l_new_relship_rec.active_start_date,
p_old_active_end_date => l_old_relship_rec.active_end_date,
p_new_active_end_date => l_new_relship_rec.active_end_date,
p_old_mandatory_flag => l_old_relship_rec.mandatory_flag,
p_new_mandatory_flag => l_new_relship_rec.mandatory_flag,
p_old_context => l_old_relship_rec.context,
p_new_context => l_new_relship_rec.context,
p_old_attribute1 => l_old_relship_rec.attribute1,
p_new_attribute1 => l_new_relship_rec.attribute1,
p_old_attribute2 => l_old_relship_rec.attribute2,
p_new_attribute2 => l_new_relship_rec.attribute2,
p_old_attribute3 => l_old_relship_rec.attribute3,
p_new_attribute3 => l_new_relship_rec.attribute3,
p_old_attribute4 => l_old_relship_rec.attribute4,
p_new_attribute4 => l_new_relship_rec.attribute4,
p_old_attribute5 => l_old_relship_rec.attribute5,
p_new_attribute5 => l_new_relship_rec.attribute5,
p_old_attribute6 => l_old_relship_rec.attribute6,
p_new_attribute6 => l_new_relship_rec.attribute6,
p_old_attribute7 => l_old_relship_rec.attribute7,
p_new_attribute7 => l_new_relship_rec.attribute7,
p_old_attribute8 => l_old_relship_rec.attribute8,
p_new_attribute8 => l_new_relship_rec.attribute8,
p_old_attribute9 => l_old_relship_rec.attribute9,
p_new_attribute9 => l_new_relship_rec.attribute9,
p_old_attribute10 => l_old_relship_rec.attribute10,
p_new_attribute10 => l_new_relship_rec.attribute10,
p_old_attribute11 => l_old_relship_rec.attribute11,
p_new_attribute11 => l_new_relship_rec.attribute11,
p_old_attribute12 => l_old_relship_rec.attribute12,
p_new_attribute12 => l_new_relship_rec.attribute12,
p_old_attribute13 => l_old_relship_rec.attribute13,
p_new_attribute13 => l_new_relship_rec.attribute13,
p_old_attribute14 => l_old_relship_rec.attribute14,
p_new_attribute14 => l_new_relship_rec.attribute14,
p_old_attribute15 => l_old_relship_rec.attribute15,
p_new_attribute15 => l_new_relship_rec.attribute15,
p_full_dump_flag => 'Y',
p_created_by => fnd_global.user_id,
p_creation_date => SYSDATE,
p_last_updated_by => fnd_global.user_id,
p_last_update_date => SYSDATE,
p_last_update_login => fnd_global.conc_login_id,
p_object_version_number => 1);
csi_ii_relationships_h_pkg.insert_row(
px_relationship_history_id => l_relship_hist_rec.relationship_history_id,
p_relationship_id => l_old_relship_rec.relationship_id,
p_transaction_id => l_transaction_id,
p_old_subject_id => l_relship_hist_rec.old_subject_id,
p_new_subject_id => l_relship_hist_rec.new_subject_id,
p_old_position_reference => l_relship_hist_rec.old_position_reference,
p_new_position_reference => l_relship_hist_rec.new_position_reference,
p_old_active_start_date => l_relship_hist_rec.old_active_start_date,
p_new_active_start_date => l_relship_hist_rec.new_active_start_date,
p_old_active_end_date => l_relship_hist_rec.old_active_end_date,
p_new_active_end_date => l_relship_hist_rec.new_active_end_date,
p_old_mandatory_flag => l_relship_hist_rec.old_mandatory_flag,
p_new_mandatory_flag => l_relship_hist_rec.new_mandatory_flag,
p_old_context => l_relship_hist_rec.old_context,
p_new_context => l_relship_hist_rec.new_context,
p_old_attribute1 => l_relship_hist_rec.old_attribute1,
p_new_attribute1 => l_relship_hist_rec.new_attribute1,
p_old_attribute2 => l_relship_hist_rec.old_attribute2,
p_new_attribute2 => l_relship_hist_rec.new_attribute2,
p_old_attribute3 => l_relship_hist_rec.old_attribute3,
p_new_attribute3 => l_relship_hist_rec.new_attribute3,
p_old_attribute4 => l_relship_hist_rec.old_attribute4,
p_new_attribute4 => l_relship_hist_rec.new_attribute4,
p_old_attribute5 => l_relship_hist_rec.old_attribute5,
p_new_attribute5 => l_relship_hist_rec.new_attribute5,
p_old_attribute6 => l_relship_hist_rec.old_attribute6,
p_new_attribute6 => l_relship_hist_rec.new_attribute6,
p_old_attribute7 => l_relship_hist_rec.old_attribute7,
p_new_attribute7 => l_relship_hist_rec.new_attribute7,
p_old_attribute8 => l_relship_hist_rec.old_attribute8,
p_new_attribute8 => l_relship_hist_rec.new_attribute8,
p_old_attribute9 => l_relship_hist_rec.old_attribute9,
p_new_attribute9 => l_relship_hist_rec.new_attribute9,
p_old_attribute10 => l_relship_hist_rec.old_attribute10,
p_new_attribute10 => l_relship_hist_rec.new_attribute10,
p_old_attribute11 => l_relship_hist_rec.old_attribute11,
p_new_attribute11 => l_relship_hist_rec.new_attribute11,
p_old_attribute12 => l_relship_hist_rec.old_attribute12,
p_new_attribute12 => l_relship_hist_rec.new_attribute12,
p_old_attribute13 => l_relship_hist_rec.old_attribute13,
p_new_attribute13 => l_relship_hist_rec.new_attribute13,
p_old_attribute14 => l_relship_hist_rec.old_attribute14,
p_new_attribute14 => l_relship_hist_rec.new_attribute14,
p_old_attribute15 => l_relship_hist_rec.old_attribute15,
p_new_attribute15 => l_relship_hist_rec.new_attribute15,
p_full_dump_flag => NULL,
p_created_by => fnd_global.user_id,
p_creation_date => SYSDATE,
p_last_updated_by => fnd_global.user_id,
p_last_update_date => SYSDATE,
p_last_update_login => fnd_global.conc_login_id,
p_object_version_number => 1);
| Case1. Create/Update Relationship |
| Only 'COMPONENT-OF' If subject_id not null and subject_id's creation_complete_flag |
| is 'Y' and relationship is mandatory then set object_id's completeness_flag to 'Y' |
| |
| Case2. Location attribute inheritance property. |
| Create/Update Relationship | |
| Only for 'COMPONENT-OF' relationship_type_code update location of subject_id and its children in |
| 'COMPONENT-OF' relationship with location of object_id. |
| |
| |
+------------------------------------------------------------------------------------------------------------*/
PROCEDURE update_instance
( p_api_version IN NUMBER,
p_commit IN VARCHAR2,
p_init_msg_list IN VARCHAR2,
p_validation_level IN NUMBER,
p_ii_relationship_rec IN csi_datastructures_pub.ii_relationship_rec,
p_txn_rec IN OUT NOCOPY csi_datastructures_pub.transaction_rec,
p_mode IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
/* CURSOR completeness_csr (p_subject_id IN NUMBER) IS
SELECT object_id
FROM csi_ii_relationships
START WITH subject_id = p_subject_id
CONNECT BY subject_id = PRIOR object_id; */
select subject_id
into l_ii_relationship_rec.subject_id
from CSI_II_RELATIONSHIPS
where relationship_id = l_ii_relationship_rec.relationship_id;
select object_id
into l_ii_relationship_rec.object_id
from CSI_II_RELATIONSHIPS
where relationship_id = l_ii_relationship_rec.relationship_id;
SELECT object_version_number,
config_inst_hdr_id,
config_inst_item_id,
config_inst_rev_num
INTO l_object_version1,
l_instance_rec.config_inst_hdr_id, -- added
l_instance_rec.config_inst_item_id, -- added
l_instance_rec.config_inst_rev_num -- added
FROM csi_item_instances
WHERE instance_id=l_ii_relationship_rec.subject_id;
IF p_mode='CREATE' OR p_mode='UPDATE'
THEN
l_instance_rec.instance_usage_code :='IN_RELATIONSHIP';
SELECT active_end_date,
location_type_code,
location_id,
inv_organization_id,
inv_subinventory_name,
inv_locator_id,
pa_project_id,
pa_project_task_id,
in_transit_order_line_id,
wip_job_id,
po_order_line_id,
operational_status_code,
install_location_id,
install_location_type_code
INTO l_instance_rec.active_end_date,
l_instance_rec.location_type_code,
l_instance_rec.location_id,
l_instance_rec.inv_organization_id,
l_instance_rec.inv_subinventory_name,
l_instance_rec.inv_locator_id,
l_instance_rec.pa_project_id,
l_instance_rec.pa_project_task_id,
l_instance_rec.in_transit_order_line_id,
l_instance_rec.wip_job_id,
l_instance_rec.po_order_line_id,
l_instance_rec.operational_status_code,
l_instance_rec.install_location_id,
l_instance_rec.install_location_type_code
FROM csi_item_instances
WHERE instance_id=l_ii_relationship_rec.object_id;
csi_item_instance_pub.update_item_instance
( p_api_version => p_api_version
,p_commit => p_commit
,p_init_msg_list => p_init_msg_list
,p_validation_level => p_validation_level
,p_instance_rec => l_instance_rec
,p_ext_attrib_values_tbl => p_ext_attrib_values_tbl
,p_party_tbl => p_party_tbl
,p_account_tbl => p_account_tbl
,p_pricing_attrib_tbl => p_pricing_attrib_tbl
,p_org_assignments_tbl => p_org_assignments_tbl
,p_asset_assignment_tbl => p_asset_assignment_tbl
,p_txn_rec => p_txn_rec
,x_instance_id_lst => l_instance_id_lst
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
/*csi_item_instance_pvt.update_item_instance
( p_api_version => p_api_version
,p_commit => p_commit
,p_init_msg_list => p_init_msg_list
,p_validation_level => p_validation_level
,p_instance_rec => l_instance_rec
,p_txn_rec => p_txn_rec
,x_instance_id_lst => l_instance_id_lst
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_item_attribute_tbl => l_item_attribute_tbl
,p_location_tbl => l_location_tbl
,p_generic_id_tbl => l_generic_id_tbl
,p_lookup_tbl => l_lookup_tbl
,p_ins_count_rec => l_ins_count_rec
,p_oks_txn_inst_tbl => px_oks_txn_inst_tbl
,p_child_inst_tbl => px_child_inst_tbl
);*/
SELECT subject_id
INTO l_subject_id
FROM csi_ii_relationships
WHERE object_id = l_rel_tbl(j).object_id
AND mandatory_flag = 'Y'
AND relationship_type_code='COMPONENT-OF'
AND ROWNUM=1;
SELECT COUNT(*)
INTO l_dummy
FROM csi_item_instances
WHERE instance_id IN ( SELECT subject_id
FROM csi_ii_relationships
WHERE object_id = l_rel_tbl(j).object_id
AND mandatory_flag = 'Y'
AND relationship_type_code='COMPONENT-OF'
)
AND creation_complete_flag = 'N';
SELECT object_version_number,
config_inst_hdr_id, --added
config_inst_item_id, --added
config_inst_rev_num
INTO l_object_version,
l_instance_rec1.config_inst_hdr_id, --added
l_instance_rec1.config_inst_item_id, --added
l_instance_rec1.config_inst_rev_num --added
FROM csi_item_instances
WHERE instance_id = l_rel_tbl(j).object_id;
csi_item_instance_pvt.update_item_instance
( p_api_version => p_api_version
,p_commit => p_commit
,p_init_msg_list => p_init_msg_list
,p_validation_level => p_validation_level
,p_instance_rec => l_instance_rec1
,p_txn_rec => p_txn_rec
,x_instance_id_lst => l_instance_id_lst
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_item_attribute_tbl => l_item_attribute_tbl
,p_location_tbl => l_location_tbl
,p_generic_id_tbl => l_generic_id_tbl
,p_lookup_tbl => l_lookup_tbl
,p_ins_count_rec => l_ins_count_rec
,p_oks_txn_inst_tbl => px_oks_txn_inst_tbl
,p_child_inst_tbl => px_child_inst_tbl
);
END update_instance;
PROCEDURE update_party_account
( p_api_version IN NUMBER,
p_commit IN VARCHAR2,
p_init_msg_list IN VARCHAR2,
p_validation_level IN NUMBER,
p_ii_relationship_rec IN csi_datastructures_pub.ii_relationship_rec,
p_txn_rec IN OUT NOCOPY csi_datastructures_pub.transaction_rec,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
l_ext_attrib_values_tbl csi_datastructures_pub.extend_attrib_values_tbl;
SELECT cp.party_id
,cp.party_source_table
,cp.contact_flag
,cp.relationship_type_code
,ca.party_account_id
,ca.relationship_type_code
INTO l_party_tbl(1).party_id
,l_party_tbl(1).party_source_table
,l_party_tbl(1).contact_flag
,l_party_tbl(1).relationship_type_code
,l_account_tbl(1).party_account_id
,l_account_tbl(1).relationship_type_code
FROM csi_i_parties cp,
csi_ip_accounts ca
WHERE cp.instance_id = p_ii_relationship_rec.object_id
AND cp.instance_party_id = ca.instance_party_id
AND cp.relationship_type_code = 'OWNER'
AND cp.relationship_type_code = ca.relationship_type_code
AND (cp.active_end_date IS NULL OR cp.active_end_date > SYSDATE)
AND (ca.active_end_date IS NULL OR ca.active_end_date > SYSDATE);
SELECT instance_party_id
,object_version_number
INTO l_party_tbl(1).instance_party_id
,l_party_tbl(1).object_version_number
FROM csi_i_parties
WHERE instance_id = p_ii_relationship_rec.subject_id
AND relationship_type_code = 'OWNER';
SELECT instance_id
,object_version_number
INTO l_instance_rec.instance_id
,l_instance_rec.object_version_number
FROM csi_item_instances
WHERE instance_id = p_ii_relationship_rec.subject_id;
csi_item_instance_pub.update_item_instance
( p_api_version => p_api_version
,p_commit => p_commit
,p_init_msg_list => p_init_msg_list
,p_validation_level => p_validation_level
,p_instance_rec => l_instance_rec
,p_ext_attrib_values_tbl => l_ext_attrib_values_tbl
,p_party_tbl => l_party_tbl
,p_account_tbl => l_account_tbl
,p_pricing_attrib_tbl => l_pricing_attrib_tbl
,p_org_assignments_tbl => l_org_assignments_tbl
,p_asset_assignment_tbl => l_asset_assignment_tbl
,p_txn_rec => p_txn_rec
,x_instance_id_lst => l_instance_id_lst
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
END update_party_account;
update_instance
( p_api_version => p_api_version,
p_commit => fnd_api.g_false,
p_init_msg_list => p_init_msg_list,
p_validation_level => p_validation_level,
p_ii_relationship_rec => p_relationship_tbl(l_count),
p_txn_rec => p_txn_rec,
p_mode => 'CREATE',
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
csi_gen_utility_pvt.put_line( ' Call to update_instance has errored ....');
csi_ii_relationships_pkg.insert_row(
px_relationship_id => p_relationship_tbl(l_count).relationship_id,
p_relationship_type_code => p_relationship_tbl(l_count).relationship_type_code,
p_object_id => p_relationship_tbl(l_count).object_id,
p_subject_id => p_relationship_tbl(l_count).subject_id,
p_position_reference => p_relationship_tbl(l_count).position_reference,
p_active_start_date => p_relationship_tbl(l_count).active_start_date,
p_active_end_date => p_relationship_tbl(l_count).active_end_date,
p_display_order => p_relationship_tbl(l_count).display_order,
p_mandatory_flag => p_relationship_tbl(l_count).mandatory_flag,
p_context => p_relationship_tbl(l_count).context,
p_attribute1 => p_relationship_tbl(l_count).attribute1,
p_attribute2 => p_relationship_tbl(l_count).attribute2,
p_attribute3 => p_relationship_tbl(l_count).attribute3,
p_attribute4 => p_relationship_tbl(l_count).attribute4,
p_attribute5 => p_relationship_tbl(l_count).attribute5,
p_attribute6 => p_relationship_tbl(l_count).attribute6,
p_attribute7 => p_relationship_tbl(l_count).attribute7,
p_attribute8 => p_relationship_tbl(l_count).attribute8,
p_attribute9 => p_relationship_tbl(l_count).attribute9,
p_attribute10 => p_relationship_tbl(l_count).attribute10,
p_attribute11 => p_relationship_tbl(l_count).attribute11,
p_attribute12 => p_relationship_tbl(l_count).attribute12,
p_attribute13 => p_relationship_tbl(l_count).attribute13,
p_attribute14 => p_relationship_tbl(l_count).attribute14,
p_attribute15 => p_relationship_tbl(l_count).attribute15,
p_created_by => fnd_global.user_id,
p_creation_date => SYSDATE,
p_last_updated_by => fnd_global.user_id,
p_last_update_date => SYSDATE,
p_last_update_login => fnd_global.conc_login_id,
p_object_version_number => 1);
update_party_account
( p_api_version => p_api_version,
p_commit => fnd_api.g_false,
p_init_msg_list => p_init_msg_list,
p_validation_level => p_validation_level,
p_ii_relationship_rec => p_relationship_tbl(l_count),
p_txn_rec => p_txn_rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
csi_ii_relationships_h_pkg.insert_row(
px_relationship_history_id => l_relship_history_id,
p_relationship_id => p_relationship_tbl(l_count).relationship_id,
p_transaction_id => p_txn_rec.transaction_id,
p_old_subject_id => NULL,
p_new_subject_id => p_relationship_tbl(l_count).subject_id,
p_old_position_reference => NULL,
p_new_position_reference => p_relationship_tbl(l_count).position_reference,
p_old_active_start_date => NULL,
p_new_active_start_date => p_relationship_tbl(l_count).active_start_date,
p_old_active_end_date => NULL,
p_new_active_end_date => p_relationship_tbl(l_count).active_end_date,
p_old_mandatory_flag => NULL,
p_new_mandatory_flag => p_relationship_tbl(l_count).mandatory_flag,
p_old_context => NULL,
p_new_context => p_relationship_tbl(l_count).context,
p_old_attribute1 => NULL,
p_new_attribute1 => p_relationship_tbl(l_count).attribute1,
p_old_attribute2 => NULL,
p_new_attribute2 => p_relationship_tbl(l_count).attribute2,
p_old_attribute3 => NULL,
p_new_attribute3 => p_relationship_tbl(l_count).attribute3,
p_old_attribute4 => NULL,
p_new_attribute4 => p_relationship_tbl(l_count).attribute4,
p_old_attribute5 => NULL,
p_new_attribute5 => p_relationship_tbl(l_count).attribute5,
p_old_attribute6 => NULL,
p_new_attribute6 => p_relationship_tbl(l_count).attribute6,
p_old_attribute7 => NULL,
p_new_attribute7 => p_relationship_tbl(l_count).attribute7,
p_old_attribute8 => NULL,
p_new_attribute8 => p_relationship_tbl(l_count).attribute8,
p_old_attribute9 => NULL,
p_new_attribute9 => p_relationship_tbl(l_count).attribute9,
p_old_attribute10 => NULL,
p_new_attribute10 => p_relationship_tbl(l_count).attribute10,
p_old_attribute11 => NULL,
p_new_attribute11 => p_relationship_tbl(l_count).attribute11,
p_old_attribute12 => NULL,
p_new_attribute12 => p_relationship_tbl(l_count).attribute12,
p_old_attribute13 => NULL,
p_new_attribute13 => p_relationship_tbl(l_count).attribute13,
p_old_attribute14 => NULL,
p_new_attribute14 => p_relationship_tbl(l_count).attribute14,
p_old_attribute15 => NULL,
p_new_attribute15 => p_relationship_tbl(l_count).attribute15,
p_full_dump_flag => 'Y',
p_created_by => fnd_global.user_id,
p_creation_date => SYSDATE,
p_last_updated_by => fnd_global.user_id,
p_last_update_date => SYSDATE,
p_last_update_login => fnd_global.conc_login_id,
p_object_version_number => 1);
l_ext_attrib_values_tbl.delete;
l_party_tbl.delete;
l_account_tbl.delete;
l_pricing_attrib_tbl.delete;
l_org_assignments_tbl.delete;
l_instance_id_lst.delete;
SELECT object_version_number,
'Y'
INTO l_instance_rec.object_version_number,
l_instance_rec.cascade_ownership_flag
FROM csi_item_instances
WHERE instance_id=l_instance_rec.instance_id
AND (active_end_date IS NULL OR active_end_date > sysdate);
csi_item_instance_pub.update_item_instance
( p_api_version => p_api_version
,p_commit => p_commit
,p_init_msg_list => p_init_msg_list
,p_validation_level => p_validation_level
,p_instance_rec => l_instance_rec
,p_ext_attrib_values_tbl => l_ext_attrib_values_tbl
,p_party_tbl => l_party_tbl
,p_account_tbl => l_account_tbl
,p_pricing_attrib_tbl => l_pricing_attrib_tbl
,p_org_assignments_tbl => l_org_assignments_tbl
,p_asset_assignment_tbl => l_asset_assignment_tbl
,p_txn_rec => p_txn_rec
,x_instance_id_lst => l_instance_id_lst
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
csi_gen_utility_pvt.put_line( 'Call to update_item_instance API for cascade ownership has errored');
PROCEDURE update_relationship
(
p_api_version IN NUMBER,
p_commit IN VARCHAR2,
p_init_msg_list IN VARCHAR2,
p_validation_level IN NUMBER,
p_relationship_tbl IN csi_datastructures_pub.ii_relationship_tbl,
p_replace_flag IN VARCHAR2,
p_txn_rec IN OUT NOCOPY csi_datastructures_pub.transaction_rec,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
CURSOR relship_csr (relship_id IN NUMBER) IS
SELECT relationship_id,
relationship_type_code,
object_id,
subject_id,
position_reference,
active_start_date,
active_end_date,
display_order,
mandatory_flag,
context,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
object_version_number
FROM csi_ii_relationships
WHERE relationship_id=relship_id
FOR UPDATE OF object_version_number ;
l_api_name CONSTANT VARCHAR2(30) := 'update_ii_relationships';
SAVEPOINT update_relationship_pvt;
CSI_gen_utility_pvt.put_line( 'update_relationship');
p_validation_mode => 'UPDATE',
p_ii_relationship_tbl => p_relationship_tbl,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
SELECT subject_id
INTO l_subject_lock
FROM csi_ii_relationships
WHERE relationship_id=p_relationship_tbl(l_count).relationship_id;
p_mode => 'UPDATE')
THEN
x_return_status:=fnd_api.g_ret_sts_success;
select 'x'
into l_exists
from csi_ii_relationships
where object_id = decode(p_relationship_tbl(l_count).subject_id,fnd_api.g_miss_num,l_relship_csr.subject_id,
p_relationship_tbl(l_count).subject_id)
and subject_id = decode(p_relationship_tbl(l_count).object_id,fnd_api.g_miss_num,l_relship_csr.object_id,
p_relationship_tbl(l_count).object_id)
and relationship_type_code = decode(p_relationship_tbl(l_count).relationship_type_code,fnd_api.g_miss_char,
l_relship_csr.relationship_type_code,
p_relationship_tbl(l_count).relationship_type_code)
and ((active_end_date is null) or (active_end_date > sysdate))
and relationship_id <> p_relationship_tbl(l_count).relationship_id;
SELECT object_id
INTO l_obj_id
FROM csi_ii_relationships
WHERE relationship_id=p_relationship_tbl(l_count).relationship_id;
SELECT subject_id
INTO l_sub_id
FROM csi_ii_relationships
WHERE relationship_id=p_relationship_tbl(l_count).relationship_id;
,p_mode => 'UPDATE'
,p_relationship_id => p_relationship_tbl(l_count).relationship_id )
THEN
fnd_message.set_name('CSI','CSI_LINK_EXISTS');
,p_mode => 'UPDATE'
,p_relationship_id => p_relationship_tbl(l_count).relationship_id )
THEN
fnd_message.set_name('CSI','CSI_LINK_EXISTS');
SELECT instance_usage_code
INTO l_ins_usage_code
FROM csi_item_instances
WHERE instance_id=l_object_id;
SELECT object_version_number,
config_inst_hdr_id, --added
config_inst_item_id, --added
config_inst_rev_num, --added
location_type_code,
inventory_item_id,
last_vld_organization_id
INTO l_obv_number,
l_instance_rec.config_inst_hdr_id, --added
l_instance_rec.config_inst_item_id, --added
l_instance_rec.config_inst_rev_num, --added
l_loc_type_code,
l_item_id,
l_vld_org
FROM csi_item_instances
WHERE instance_id = l_relship_csr.subject_id;
select serial_number_control_code
into l_srl_ctl
from MTL_SYSTEM_ITEMS_B
where inventory_item_id = l_item_id
and organization_id = l_vld_org;
'UPDATE',
p_relationship_tbl(l_count).relationship_id)
THEN
x_return_status:=fnd_api.g_ret_sts_success;
csi_gen_utility_pvt.put_line('Calling Update_Instance..');
update_instance
( p_api_version => p_api_version,
p_commit => fnd_api.g_false,--p_commit,
p_init_msg_list => p_init_msg_list,
p_validation_level => p_validation_level,
p_ii_relationship_rec => p_relationship_tbl(l_count),
p_txn_rec => p_txn_rec,
p_mode => 'UPDATE',
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
csi_gen_utility_pvt.put_line( ' Error from CSI_II_RELATIONSHIPS_PVT.UPDATE_RELATIONSHIP');
csi_gen_utility_pvt.put_line( ' Call to update_instance has errored ....');
csi_ii_relationships_pkg.update_row(
p_relationship_id => p_relationship_tbl(l_count).relationship_id,
p_relationship_type_code => p_relationship_tbl(l_count).relationship_type_code,
p_object_id => p_relationship_tbl(l_count).object_id,
p_subject_id => p_relationship_tbl(l_count).subject_id,
p_position_reference => p_relationship_tbl(l_count).position_reference,
p_active_start_date => fnd_api.g_miss_date, -- p_relationship_tbl(l_count).active_start_date,
p_active_end_date => p_relationship_tbl(l_count).active_end_date,
p_display_order => p_relationship_tbl(l_count).display_order,
p_mandatory_flag => p_relationship_tbl(l_count).mandatory_flag,
p_context => p_relationship_tbl(l_count).context,
p_attribute1 => p_relationship_tbl(l_count).attribute1,
p_attribute2 => p_relationship_tbl(l_count).attribute2,
p_attribute3 => p_relationship_tbl(l_count).attribute3,
p_attribute4 => p_relationship_tbl(l_count).attribute4,
p_attribute5 => p_relationship_tbl(l_count).attribute5,
p_attribute6 => p_relationship_tbl(l_count).attribute6,
p_attribute7 => p_relationship_tbl(l_count).attribute7,
p_attribute8 => p_relationship_tbl(l_count).attribute8,
p_attribute9 => p_relationship_tbl(l_count).attribute9,
p_attribute10 => p_relationship_tbl(l_count).attribute10,
p_attribute11 => p_relationship_tbl(l_count).attribute11,
p_attribute12 => p_relationship_tbl(l_count).attribute12,
p_attribute13 => p_relationship_tbl(l_count).attribute13,
p_attribute14 => p_relationship_tbl(l_count).attribute14,
p_attribute15 => p_relationship_tbl(l_count).attribute15,
p_created_by => fnd_api.g_miss_num,
p_creation_date => fnd_api.g_miss_date,
p_last_updated_by => fnd_global.user_id,
p_last_update_date => SYSDATE,
p_last_update_login => fnd_global.conc_login_id,
p_object_version_number => p_relationship_tbl(l_count).object_version_number);
csi_gen_utility_pvt.put_line('Calling update_item_instance..');
csi_item_instance_pvt.update_item_instance
(p_api_version => p_api_version
,p_commit => p_commit
,p_init_msg_list => p_init_msg_list
,p_validation_level => p_validation_level
,p_instance_rec => l_instance_rec
,p_txn_rec => p_txn_rec
,x_instance_id_lst => l_instance_id_lst
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_item_attribute_tbl => l_item_attribute_tbl
,p_location_tbl => l_location_tbl
,p_generic_id_tbl => l_generic_id_tbl
,p_lookup_tbl => l_lookup_tbl
,p_ins_count_rec => l_ins_count_rec
,p_called_from_rel => fnd_api.g_true
,p_oks_txn_inst_tbl => px_oks_txn_inst_tbl
,p_child_inst_tbl => px_child_inst_tbl
);
l_ext_attrib_values_tbl.delete;
l_party_tbl.delete;
l_account_tbl.delete;
l_pricing_attrib_tbl.delete;
l_org_assignments_tbl.delete;
l_inst_id_lst.delete;
SELECT object_id
INTO l_inst_rec.instance_id
FROM csi_ii_relationships
WHERE relationship_id=p_relationship_tbl(l_count).relationship_id;
csi_gen_utility_pvt.put_line( 'Error from update relationship API.');
SELECT object_version_number,
'Y'
INTO l_inst_rec.object_version_number,
l_inst_rec.cascade_ownership_flag
FROM csi_item_instances
WHERE instance_id=l_inst_rec.instance_id
AND (active_end_date IS NULL OR active_end_date > sysdate);
csi_gen_utility_pvt.put_line( 'Error from update relationship API.');
csi_item_instance_pub.update_item_instance
( p_api_version => p_api_version
,p_commit => p_commit
,p_init_msg_list => p_init_msg_list
,p_validation_level => p_validation_level
,p_instance_rec => l_inst_rec
,p_ext_attrib_values_tbl => l_ext_attrib_values_tbl
,p_party_tbl => l_party_tbl
,p_account_tbl => l_account_tbl
,p_pricing_attrib_tbl => l_pricing_attrib_tbl
,p_org_assignments_tbl => l_org_assignments_tbl
,p_asset_assignment_tbl => l_asset_assignment_tbl
,p_txn_rec => p_txn_rec
,x_instance_id_lst => l_inst_id_lst
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
csi_gen_utility_pvt.put_line( 'Error from update relationship API.');
csi_gen_utility_pvt.put_line( 'Call to update_item_instance API for cascade ownership has errored');
ROLLBACK TO update_relationship_pvt;
ROLLBACK TO update_relationship_pvt;
ROLLBACK TO update_relationship_pvt;
END update_relationship;
SELECT relationship_id,
relationship_type_code,
object_id,
subject_id,
position_reference,
active_start_date,
active_end_date,
display_order,
mandatory_flag,
context,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
object_version_number
FROM csi_ii_relationships
WHERE relationship_id=relship_id
FOR UPDATE OF object_version_number ;
SELECT instance_usage_code
INTO l_ins_usage_code
FROM csi_item_instances
WHERE instance_id=l_object_id;
SELECT object_version_number,
config_inst_hdr_id, --added
config_inst_item_id, --added
config_inst_rev_num,
inventory_item_id,
last_vld_organization_id,
location_type_code
INTO l_obv_number,
l_instance_rec.config_inst_hdr_id, --added
l_instance_rec.config_inst_item_id, --added
l_instance_rec.config_inst_rev_num,
l_item_id,
l_vld_org,
l_loc_type_code
FROM csi_item_instances
WHERE instance_id = l_relship_csr.subject_id;
select serial_number_control_code
into l_srl_ctl
from MTL_SYSTEM_ITEMS_B
where inventory_item_id = l_item_id
and organization_id = l_vld_org;
csi_ii_relationships_pkg.update_row(
p_relationship_id => p_relationship_rec.relationship_id,
p_relationship_type_code => fnd_api.g_miss_char,
p_object_id => fnd_api.g_miss_num,
p_subject_id => fnd_api.g_miss_num,
p_position_reference => fnd_api.g_miss_char,
p_active_start_date => fnd_api.g_miss_date,
p_active_end_date => l_sysdate,
p_display_order => fnd_api.g_miss_num,
p_mandatory_flag => fnd_api.g_miss_char,
p_context => fnd_api.g_miss_char,
p_attribute1 => fnd_api.g_miss_char,
p_attribute2 => fnd_api.g_miss_char,
p_attribute3 => fnd_api.g_miss_char,
p_attribute4 => fnd_api.g_miss_char,
p_attribute5 => fnd_api.g_miss_char,
p_attribute6 => fnd_api.g_miss_char,
p_attribute7 => fnd_api.g_miss_char,
p_attribute8 => fnd_api.g_miss_char,
p_attribute9 => fnd_api.g_miss_char,
p_attribute10 => fnd_api.g_miss_char,
p_attribute11 => fnd_api.g_miss_char,
p_attribute12 => fnd_api.g_miss_char,
p_attribute13 => fnd_api.g_miss_char,
p_attribute14 => fnd_api.g_miss_char,
p_attribute15 => fnd_api.g_miss_char,
p_created_by => fnd_api.g_miss_num,
p_creation_date => fnd_api.g_miss_date,
p_last_updated_by => fnd_global.user_id,
p_last_update_date => l_sysdate,
p_last_update_login => fnd_global.conc_login_id,
p_object_version_number => fnd_api.g_miss_num);
csi_gen_utility_pvt.put_line('Calling Update II from Exp Rel...');
csi_item_instance_pvt.update_item_instance
( p_api_version => p_api_version
,p_commit => p_commit
,p_init_msg_list => p_init_msg_list
,p_validation_level => p_validation_level
,p_instance_rec => l_instance_rec
,p_txn_rec => p_txn_rec
,x_instance_id_lst => l_instance_id_lst
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_item_attribute_tbl => l_item_attribute_tbl
,p_location_tbl => l_location_tbl
,p_generic_id_tbl => l_generic_id_tbl
,p_lookup_tbl => l_lookup_tbl
,p_ins_count_rec => l_ins_count_rec
,p_called_from_rel => fnd_api.g_true
,p_oks_txn_inst_tbl => px_oks_txn_inst_tbl
,p_child_inst_tbl => px_child_inst_tbl
);
SELECT 'x'
INTO l_dummy
FROM csi_ii_relationships
WHERE relationship_id=p_relationship_id
AND ROWNUM=1;
ELSIF ( (p_validation_mode = 'UPDATE') OR (p_validation_mode = 'EXPIRE') ) THEN
IF ( (p_relationship_id IS NOT NULL) AND (p_relationship_id <> fnd_api.g_miss_num) ) THEN
BEGIN
/* Added the condition 'AND ACTIVE_END_DATE IS NULL' to avoid updating expired relationship */
SELECT 'x'
INTO l_dummy
FROM csi_ii_relationships
WHERE relationship_id=p_relationship_id;
SELECT 'x'
INTO l_dummy
FROM csi_ii_relation_types
WHERE relationship_type_code=p_relationship_type_code;
ELSIF(p_validation_mode ='UPDATE')
THEN
IF ((p_relationship_type_code IS NOT NULL) AND (p_relationship_type_code <> fnd_api.g_miss_char)) THEN
BEGIN
SELECT 'x'
INTO l_dummy
FROM csi_ii_relation_types
WHERE relationship_type_code=p_relationship_type_code;
SELECT 'x'
INTO l_active
FROM csi_item_instances cii
WHERE cii.instance_id=p_object_id
AND (SYSDATE BETWEEN NVL(cii.active_start_date, SYSDATE) AND NVL(cii.active_end_date, SYSDATE));
SELECT instance_id
,quantity
,location_type_code
,inventory_item_id
,last_vld_organization_id
INTO l_instance_id
,l_quantity
,l_location_type_code
,l_inventory_item_id
,l_vld_org_id
FROM csi_item_instances
WHERE instance_id=p_object_id;
SELECT serial_number_control_code
,item_type
,bom_item_type
,pick_components_flag
,base_item_id -- Added by rk on 9-Apr
,replenish_to_order_flag -- for bug 2304221
INTO l_serial_code
,l_item_type
,l_bom_item_type
,l_pick_comp_flag
,l_base_item_id
,l_repl_order_flag
FROM mtl_system_items
WHERE inventory_item_id = l_inventory_item_id
AND organization_id = l_vld_org_id ;
ELSIF(p_validation_mode ='UPDATE')
THEN
IF ((p_object_id IS NOT NULL) AND (p_object_id <> fnd_api.g_miss_num)) THEN
BEGIN
-- Modified by sk for bug 2266166
SELECT instance_id
,quantity
,location_type_code
,inventory_item_id
,last_vld_organization_id
INTO l_instance_id
,l_quantity
,l_location_type_code
,l_inventory_item_id
,l_vld_org_id
FROM csi_item_instances
WHERE instance_id=p_object_id;
SELECT serial_number_control_code
,item_type
,bom_item_type
,pick_components_flag
,base_item_id
,replenish_to_order_flag
INTO l_serial_code
,l_item_type
,l_bom_item_type
,l_pick_comp_flag
,l_base_item_id -- fix for bug 2304221
,l_repl_order_flag
FROM mtl_system_items
WHERE inventory_item_id = l_inventory_item_id
AND organization_id = l_vld_org_id ;
SELECT 'x'
INTO l_active
FROM csi_item_instances cii
WHERE cii.instance_id=p_subject_id
AND (SYSDATE BETWEEN NVL(cii.active_start_date, SYSDATE) AND NVL(cii.active_end_date, SYSDATE));
SELECT instance_id,location_type_code
INTO l_instance_id,l_location_type_code
FROM csi_item_instances
WHERE instance_id=p_subject_id;
IF( (p_validation_mode = 'UPDATE') OR (p_validation_mode = 'EXPIRE') ) THEN
IF ( (p_object_version_number IS NULL) OR (p_object_version_number = fnd_api.g_miss_num) ) THEN
fnd_message.set_name('CSI', 'CSI_MISSING_OBJ_VER_NUM');
SELECT name
INTO p_rel_history_tbl(tab_row).relationship_type
FROM csi_ii_relation_types
WHERE relationship_type_code = p_rel_history_tbl(tab_row).relationship_type_code;
SELECT instance_number
INTO p_rel_history_tbl(tab_row).old_subject_number
FROM csi_item_instances
WHERE instance_id = p_rel_history_tbl(tab_row).old_subject_id;
SELECT instance_number
INTO p_rel_history_tbl(tab_row).new_subject_number
FROM csi_item_instances
WHERE instance_id = p_rel_history_tbl(tab_row).new_subject_id;
SELECT
cih.relationship_history_id ,
cih.relationship_id ,
cih.transaction_id ,
cih.old_subject_id ,
cih.new_subject_id ,
cih.old_position_reference ,
cih.new_position_reference ,
cih.old_active_start_date ,
cih.new_active_start_date ,
cih.old_active_end_date ,
cih.new_active_end_date ,
cih.old_mandatory_flag ,
cih.new_mandatory_flag ,
cih.old_context ,
cih.new_context ,
cih.old_attribute1 ,
cih.new_attribute1 ,
cih.old_attribute2 ,
cih.new_attribute2 ,
cih.old_attribute3 ,
cih.new_attribute3 ,
cih.old_attribute4 ,
cih.new_attribute4 ,
cih.old_attribute5 ,
cih.new_attribute5 ,
cih.old_attribute6 ,
cih.new_attribute6 ,
cih.old_attribute7 ,
cih.new_attribute7 ,
cih.old_attribute8 ,
cih.new_attribute8 ,
cih.old_attribute9 ,
cih.new_attribute9 ,
cih.old_attribute10 ,
cih.new_attribute10 ,
cih.old_attribute11 ,
cih.new_attribute11 ,
cih.old_attribute12 ,
cih.new_attribute12 ,
cih.old_attribute13 ,
cih.new_attribute13 ,
cih.old_attribute14 ,
cih.new_attribute14 ,
cih.old_attribute15 ,
cih.new_attribute15 ,
cih.full_dump_flag ,
cih.object_version_number ,
cir.relationship_type_code ,
cir.object_id ,
cih.creation_date
FROM csi_ii_relationships_h cih ,
csi_ii_relationships cir
WHERE cih.transaction_id = i_transaction_id
AND cih.relationship_id = cir.relationship_id;
l_rel_tbl_final.DELETE;
p_rel_tbl.DELETE;
l_rel_tbl_final.DELETE(rel); -- Pop the processed row
select object_id
into l_object_id
from CSI_II_RELATIONSHIPS
where subject_id = p_subject_id
and relationship_type_code = p_rel_type_code
and ((active_end_date is null) or (active_end_date > sysdate));