[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_select_stmt VARCHAR2(20000) := ' SELECT instance_party_id, instance_id, party_source_table, '||
' party_id, relationship_type_code,contact_flag ,contact_ip_id, active_start_date, '||
' active_end_date, context,attribute1,attribute2,attribute3, attribute4,attribute5, '||
' attribute6, attribute7, attribute8, attribute9, attribute10 ,attribute11, '||
' attribute12,attribute13,attribute14,attribute15 ,object_version_number, '||
' primary_flag, preferred_flag'||
' FROM CSI_I_PARTIES ';
l_select_stmt := l_select_stmt || ' where '||l_where_clause;
dbms_sql.parse(l_get_party_cursor_id, l_select_stmt , dbms_sql.native);
SELECT pf.employee_number
,cl.meaning --party_type
INTO x_party_header_tbl(i).party_number
,x_party_header_tbl(i).party_type
FROM per_all_people_f pf
,csi_lookups cl
,csi_item_instances cii
WHERE pf.person_id = x_party_header_tbl(i).party_id
AND cl.lookup_type=l_pty_lookup_type
AND cl.lookup_code=l_party_source_tbl
AND cii.instance_id=x_party_header_tbl(i).instance_id
AND pf.effective_end_date > SYSDATE
AND ROWNUM = 1 ;
SELECT hz.party_number
,cl.meaning --party_type
,hz.party_name
INTO x_party_header_tbl(i).party_number
,x_party_header_tbl(i).party_type
,l_pty_name
FROM hz_parties hz
,csi_lookups cl
,csi_item_instances cii
WHERE party_id = x_party_header_tbl(i).party_id
AND cl.lookup_type=l_pty_lookup_type
AND cl.lookup_code=l_party_source_tbl
AND cii.instance_id=x_party_header_tbl(i).instance_id;
SELECT po.segment1
,cl.meaning --party_type
INTO x_party_header_tbl(i).party_number
,x_party_header_tbl(i).party_type
FROM csi_lookups cl
,csi_item_instances cii
,po_vendors po
WHERE cl.lookup_type=l_pty_lookup_type
AND cl.lookup_code=l_party_source_tbl
AND cii.instance_id=x_party_header_tbl(i).instance_id
AND po.vendor_id = x_party_header_tbl(i).party_id;
SELECT jt.team_number
,cl.meaning --party_type
INTO x_party_header_tbl(i).party_number
,x_party_header_tbl(i).party_type
FROM jtf_rs_teams_vl jt
,csi_lookups cl
,csi_item_instances cii
WHERE jt.team_id = x_party_header_tbl(i).party_id
and cl.lookup_type=l_pty_lookup_type
and cl.lookup_code=l_party_source_tbl
and cii.instance_id=x_party_header_tbl(i).instance_id;
SELECT jg.group_number
,cl.meaning --party_type
INTO x_party_header_tbl(i).party_number
,x_party_header_tbl(i).party_type
FROM jtf_rs_groups_vl jg
,csi_lookups cl
,csi_item_instances cii
WHERE jg.group_id = x_party_header_tbl(i).party_id
and cl.lookup_type=l_pty_lookup_type
and cl.lookup_code=l_party_source_tbl
and cii.instance_id=x_party_header_tbl(i).instance_id;
SELECT 'x'
INTO l_account_found
FROM csi_ip_accounts
WHERE ip_account_id =p_party_account_tbl(l_acct_row).ip_account_id;
csi_party_relationships_pvt.update_inst_party_account
( 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_party_account_rec => p_party_account_tbl(l_acct_row)
,p_txn_rec => p_txn_rec
,p_oks_txn_inst_tbl => p_oks_txn_inst_tbl
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
/* Procedure name: Update_inst_party_relationship */
/* Description : Procedure used to update the existing */
/* instance -party relationships */
/*-------------------------------------------------------------*/
PROCEDURE update_inst_party_relationship
( p_api_version IN NUMBER
,p_commit IN VARCHAR2
,p_init_msg_list IN VARCHAR2
,p_validation_level IN NUMBER
,p_party_tbl IN csi_datastructures_pub.party_tbl
,p_party_account_tbl IN OUT NOCOPY csi_datastructures_pub.party_account_tbl
,p_txn_rec IN OUT NOCOPY csi_datastructures_pub.transaction_rec
,p_oks_txn_inst_tbl IN OUT NOCOPY oks_ibint_pub.txn_instance_tbl
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_INST_PARTY_RELATIONSHIP';
SELECT acct.party_account_id
,acct.active_end_date
,pty.instance_id
FROM csi_ip_accounts acct
,csi_i_parties pty
WHERE acct.instance_party_id = p_ins_pty_id
AND acct.relationship_type_code = 'OWNER'
AND ((acct.active_end_date IS NULL) OR (acct.active_end_date>SYSDATE))
AND pty.instance_party_id= acct.instance_party_id;
SELECT instance_id subject_id -- added by sguthiva for 2608706
FROM csi_item_instances
WHERE instance_id IN(
SELECT subject_id
FROM csi_ii_relationships
WHERE relationship_type_code = 'COMPONENT-OF'
START WITH object_id = p_object_id
CONNECT BY object_id = PRIOR subject_id)
AND (active_end_date IS NULL OR active_end_date> SYSDATE); */
SELECT instance_party_id,
party_id
FROM csi_i_parties
WHERE instance_party_id = p_ins_pty_id
AND relationship_type_code = 'OWNER'
AND (active_end_date IS NULL OR active_end_date > sysdate);
SELECT instance_party_id,
party_id,
relationship_type_code,
object_version_number,
active_end_date -- Added for bug 7333900
FROM csi_i_parties
WHERE instance_id = p_ins_id
AND relationship_type_code<>'OWNER'
AND (active_end_date IS NULL
OR (trunc(active_end_date,'MI') > trunc(sysdate,'MI'))); -- Modified for bug 7333900 -- changed to trunc for bug 12564475
SELECT ip_account_id,
relationship_type_code,
object_version_number,
active_end_date -- Added for bug 7333900
FROM csi_ip_accounts
WHERE instance_party_id=p_inst_party_id
AND relationship_type_code <>'OWNER'
AND nvl(active_end_date, sysdate+1) >= sysdate;
SAVEPOINT update_inst_party_rel_pub ;
csi_gen_utility_pvt.put_line( 'update_inst_party_relationship ');
csi_gen_utility_pvt.put_line( 'update_inst_party_relationship:' ||
p_api_version ||'-'||
p_commit ||'-'||
p_init_msg_list ||'-'||
p_validation_level );
SELECT party_account_id
INTO l_old_parent_owner_pty_acct_id
FROM csi_ip_accounts
WHERE instance_party_id = l_party_rec.instance_party_id
AND relationship_type_code = 'OWNER';
SELECT acct.object_version_number
INTO l_obj_ver_number
FROM csi_ip_accounts acct
WHERE acct.ip_account_id= p_party_account_tbl(l_row).ip_account_id;
csi_party_relationships_pvt.update_inst_party_relationship
( 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_party_rec => l_party_rec
,p_txn_rec => p_txn_rec
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data ) ;
SELECT acct.object_version_number
INTO p_party_account_tbl(l_arow).object_version_number
FROM csi_ip_accounts acct
WHERE acct.ip_account_id= p_party_account_tbl(l_arow).ip_account_id;
SELECT acct.object_version_number,acct.active_end_date
INTO p_party_account_tbl(l_acct_row).object_version_number,l_end_date
FROM csi_ip_accounts acct
WHERE acct.ip_account_id= p_party_account_tbl(l_acct_row).ip_account_id;
csi_party_relationships_pvt.update_inst_party_account
( 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_party_account_rec => p_party_account_tbl(l_acct_row)
,p_txn_rec => p_txn_rec
,p_oks_txn_inst_tbl => p_oks_txn_inst_tbl
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
csi_party_relationships_pvt.update_inst_party_account
( 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_party_account_rec => l_exp_acct_rec
,p_txn_rec => p_txn_rec
,p_oks_txn_inst_tbl => p_oks_txn_inst_tbl
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
csi_party_relationships_pvt.update_inst_party_relationship
( 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_party_rec => l_exp_party_rec
,p_txn_rec => p_txn_rec
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data ) ;
SELECT ip_account_id,
relationship_type_code,
object_version_number
INTO l_exp_acct_rec.ip_account_id,
l_exp_acct_rec.relationship_type_code,
l_exp_acct_rec.object_version_number
FROM csi_ip_accounts
WHERE instance_party_id=p_party_tbl(p_row).instance_party_id
AND relationship_type_code ='OWNER'
AND nvl(active_end_date, sysdate+1) >= sysdate;
csi_party_relationships_pvt.update_inst_party_account
( 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_party_account_rec => l_exp_acct_rec
,p_txn_rec => p_txn_rec
,p_oks_txn_inst_tbl => p_oks_txn_inst_tbl
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
SELECT instance_party_id
,party_account_id
,relationship_type_code
,bill_to_address
,ship_to_address
,active_start_date
,active_end_date
,context
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
INTO l_ip_acct_rec.instance_party_id
,l_ip_acct_rec.party_account_id
,l_ip_acct_rec.relationship_type_code
,l_ip_acct_rec.bill_to_address
,l_ip_acct_rec.ship_to_address
,l_ip_acct_rec.active_start_date
,l_ip_acct_rec.active_end_date
,l_ip_acct_rec.context
,l_ip_acct_rec.attribute1
,l_ip_acct_rec.attribute2
,l_ip_acct_rec.attribute3
,l_ip_acct_rec.attribute4
,l_ip_acct_rec.attribute5
,l_ip_acct_rec.attribute6
,l_ip_acct_rec.attribute7
,l_ip_acct_rec.attribute8
,l_ip_acct_rec.attribute9
,l_ip_acct_rec.attribute10
,l_ip_acct_rec.attribute11
,l_ip_acct_rec.attribute12
,l_ip_acct_rec.attribute13
,l_ip_acct_rec.attribute14
,l_ip_acct_rec.attribute15
FROM csi_ip_accounts
WHERE instance_party_id = l_old_party_tbl(l_old_party_row).instance_party_id
AND relationship_type_code = 'OWNER'
AND SYSDATE BETWEEN nvl(active_start_date, SYSDATE-1)
AND nvl(active_end_date, SYSDATE+1);
SELECT instance_id
INTO l_old_party_tbl(l_old_party_row).instance_id
FROM csi_i_parties
WHERE instance_party_id=l_old_party_tbl(l_old_party_row).instance_party_id;
SELECT instance_party_id,
instance_id,
party_id,
object_version_number
INTO l_cld_party_rec.instance_party_id,
l_cld_party_rec.instance_id,
l_cld_party_id,
l_cld_party_rec.object_version_number
FROM csi_i_parties
WHERE instance_id = l_rel_tbl(j).subject_id
AND relationship_type_code = 'OWNER'
AND (active_end_date IS NULL OR active_end_date > SYSDATE);
SELECT party_account_id
INTO l_cld_party_acct_id
FROM csi_ip_accounts
WHERE instance_party_id = l_cld_party_rec.instance_party_id
AND relationship_type_code = 'OWNER'
AND SYSDATE BETWEEN nvl(active_start_date, sysdate-1)
AND nvl(active_end_date, sysdate+1);
SELECT party_account_id
INTO l_old_child_owner_pty_acct_id
FROM csi_ip_accounts
WHERE instance_party_id = l_cld_party_rec.instance_party_id
AND relationship_type_code = 'OWNER';
csi_party_relationships_pvt.update_inst_party_relationship
( 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_party_rec => l_cld_party_rec
,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_party_relationships_pvt.update_inst_party_relationship
( 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_party_rec => l_exp_party_rec
,p_txn_rec => p_txn_rec
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data ) ;
SELECT ip_account_id,
relationship_type_code,
object_version_number
INTO l_exp_acct_rec.ip_account_id,
l_exp_acct_rec.relationship_type_code,
l_exp_acct_rec.object_version_number
FROM csi_ip_accounts
WHERE instance_party_id=l_cld_party_rec.instance_party_id
AND relationship_type_code ='OWNER';
csi_party_relationships_pvt.update_inst_party_account
( 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_party_account_rec => l_exp_acct_rec
,p_txn_rec => p_txn_rec
,p_oks_txn_inst_tbl => p_oks_txn_inst_tbl
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
select system_id
into l_ip_acct_rec.system_id
from csi_item_instances
where instance_id = l_rel_tbl(j).subject_id
and nvl(system_id,fnd_api.g_miss_num) = p_party_account_tbl(1).system_id;
ROLLBACK TO update_inst_party_rel_pub;
ROLLBACK TO update_inst_party_rel_pub;
ROLLBACK TO update_inst_party_rel_pub;
END update_inst_party_relationship ;
l_select_stmt VARCHAR2(2000) := ' SELECT ip_account_id , instance_party_id, party_account_id, '||
' relationship_type_code, active_start_date, active_end_date,context , attribute1, '||
' attribute2, attribute3, attribute4, attribute5, attribute6, attribute7,attribute8, '||
' attribute9, attribute10,attribute11, attribute12,attribute13,attribute14,attribute15, '||
' object_version_number, bill_to_address, ship_to_address from csi_ip_accounts ';
l_select_stmt := l_select_stmt || ' where '||l_where_clause;
dbms_sql.parse(l_get_acct_cursor_id, l_select_stmt , dbms_sql.native);
/* Procedure name: Update_inst_party_account */
/* Description : Procedure used to update the existing */
/* instance-party account relationships */
/*------------------------------------------------------------*/
PROCEDURE update_inst_party_account
( p_api_version IN NUMBER
,p_commit IN VARCHAR2
,p_init_msg_list IN VARCHAR2
,p_validation_level IN NUMBER
,p_party_account_tbl IN csi_datastructures_pub.party_account_tbl
,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 party_account_csr (p_ins_pty_id IN NUMBER) IS
SELECT subject_id
FROM csi_ii_relationships
WHERE relationship_type_code = 'COMPONENT-OF'
START WITH object_id = (SELECT instance_id+0
FROM csi_i_parties
WHERE instance_party_id = p_ins_pty_id
AND relationship_type_code = 'OWNER'
AND (active_end_date IS NULL OR active_end_date > sysdate)
)
CONNECT BY object_id = PRIOR subject_id; ***/
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_INST_PARTY_ACCOUNT';
SAVEPOINT update_inst_party_acct_pub;
csi_gen_utility_pvt.put_line( 'update_inst_party_account');
csi_gen_utility_pvt.put_line( 'update_inst_party_account:'||
p_api_version ||'-'||
p_commit ||'-'||
p_init_msg_list );
SELECT acct.ip_account_id,
acct.party_account_id,
pty.party_id
INTO l_temp_account_tbl(l_acct_row).ip_account_id,
l_temp_account_tbl(l_acct_row).party_account_id,
l_temp_account_tbl(l_acct_row).attribute1
FROM csi_ip_accounts acct,
csi_i_parties pty
WHERE acct.ip_account_id = p_party_account_tbl(l_count).ip_account_id
AND acct.instance_party_id = pty.instance_party_id;
csi_party_relationships_pvt.update_inst_party_account
( 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_party_account_rec => p_party_account_tbl(l_count)
,p_txn_rec => p_txn_rec
,p_oks_txn_inst_tbl => px_oks_txn_inst_tbl
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
select instance_id
into l_object_id
from CSI_I_PARTIES
where instance_party_id = l_party_account_tbl(l_old_party_row).instance_party_id
and relationship_type_code = 'OWNER'
and (active_end_date IS NULL OR active_end_date > sysdate);
SELECT acct.ip_account_id,
acct.object_version_number,
pty.party_id
INTO l_party_account_tbl(l_old_party_row).ip_account_id,
l_party_account_tbl(l_old_party_row).object_version_number,
l_party_id
FROM csi_ip_accounts acct,
csi_i_parties pty
WHERE pty.instance_party_id = acct.instance_party_id
AND pty.instance_id = l_rel_tbl(j).subject_id
AND acct.relationship_type_code = 'OWNER'
AND (acct.active_end_date IS NULL OR
acct.active_end_date > SYSDATE);
csi_party_relationships_pvt.update_inst_party_account
( 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_party_account_rec => l_party_account_tbl(l_old_party_row)
,p_txn_rec => p_txn_rec
,p_oks_txn_inst_tbl => px_oks_txn_inst_tbl
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
ROLLBACK TO update_inst_party_acct_pub;
ROLLBACK TO update_inst_party_acct_pub;
ROLLBACK TO update_inst_party_acct_pub;
END update_inst_party_account ;