[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT decode(nvl(p_txn_party_detail_rec.preserve_detail_flag,fnd_api.g_miss_char),
fnd_api.g_miss_char, 'Y', p_txn_party_detail_rec.preserve_detail_flag)
INTO l_preserve_detail_flag
FROM sys.dual;
p_api_name => 'insert_row',
p_pkg_name => 'csi_t_party_detailss_pkg');
csi_t_party_details_pkg.insert_row(
px_txn_party_detail_id => l_txn_party_detail_id,
p_txn_line_detail_id => p_txn_party_detail_rec.txn_line_detail_id,
p_party_source_table => p_txn_party_detail_rec.party_source_table,
p_party_source_id => p_txn_party_detail_rec.party_source_id,
p_relationship_type_code => p_txn_party_detail_rec.relationship_type_code,
p_contact_flag => p_txn_party_detail_rec.contact_flag,
p_active_start_date => p_txn_party_detail_rec.active_start_date,
p_active_end_date => p_txn_party_detail_rec.active_end_date,
p_preserve_detail_flag => l_preserve_detail_flag,
p_instance_party_id => p_txn_party_detail_rec.instance_party_id,
p_attribute1 => p_txn_party_detail_rec.attribute1,
p_attribute2 => p_txn_party_detail_rec.attribute2,
p_attribute3 => p_txn_party_detail_rec.attribute3,
p_attribute4 => p_txn_party_detail_rec.attribute4,
p_attribute5 => p_txn_party_detail_rec.attribute5,
p_attribute6 => p_txn_party_detail_rec.attribute6,
p_attribute7 => p_txn_party_detail_rec.attribute7,
p_attribute8 => p_txn_party_detail_rec.attribute8,
p_attribute9 => p_txn_party_detail_rec.attribute9,
p_attribute10 => p_txn_party_detail_rec.attribute10,
p_attribute11 => p_txn_party_detail_rec.attribute11,
p_attribute12 => p_txn_party_detail_rec.attribute12,
p_attribute13 => p_txn_party_detail_rec.attribute13,
p_attribute14 => p_txn_party_detail_rec.attribute14,
p_attribute15 => p_txn_party_detail_rec.attribute15,
p_created_by => g_user_id,
p_creation_date => sysdate,
p_last_updated_by => g_user_id,
p_last_update_date => sysdate,
p_last_update_login => g_login_id,
p_object_version_number => 1.0,
p_context => p_txn_party_detail_rec.context,
p_contact_party_id => null,
p_primary_flag => p_txn_party_detail_rec.primary_flag,
p_preferred_flag => p_txn_party_detail_rec.preferred_flag);
'csi_t_party_details_pkg.insert_row Failed. '||substr(sqlerrm,1,200));
SELECT decode(nvl(p_txn_pty_acct_detail_rec.preserve_detail_flag,fnd_api.g_miss_char),
fnd_api.g_miss_char, 'Y', p_txn_pty_acct_detail_rec.preserve_detail_flag)
INTO l_preserve_detail_flag
FROM sys.dual;
p_api_name => 'insert_row',
p_pkg_name => 'csi_t_party_accounts_pkg');
csi_t_party_accounts_pkg.insert_row(
px_txn_account_detail_id => l_txn_account_detail_id,
p_txn_party_detail_id => p_txn_pty_acct_detail_rec.txn_party_detail_id,
p_ip_account_id => p_txn_pty_acct_detail_rec.ip_account_id,
p_account_id => p_txn_pty_acct_detail_rec.account_id,
p_relationship_type_code => p_txn_pty_acct_detail_rec.relationship_type_code,
p_bill_to_address_id => p_txn_pty_acct_detail_rec.bill_to_address_id,
p_ship_to_address_id => p_txn_pty_acct_detail_rec.ship_to_address_id,
p_active_start_date => p_txn_pty_acct_detail_rec.active_start_date,
p_active_end_date => p_txn_pty_acct_detail_rec.active_end_date,
p_preserve_detail_flag => l_preserve_detail_flag,
p_attribute1 => p_txn_pty_acct_detail_rec.attribute1,
p_attribute2 => p_txn_pty_acct_detail_rec.attribute2,
p_attribute3 => p_txn_pty_acct_detail_rec.attribute3,
p_attribute4 => p_txn_pty_acct_detail_rec.attribute4,
p_attribute5 => p_txn_pty_acct_detail_rec.attribute5,
p_attribute6 => p_txn_pty_acct_detail_rec.attribute6,
p_attribute7 => p_txn_pty_acct_detail_rec.attribute7,
p_attribute8 => p_txn_pty_acct_detail_rec.attribute8,
p_attribute9 => p_txn_pty_acct_detail_rec.attribute9,
p_attribute10 => p_txn_pty_acct_detail_rec.attribute10,
p_attribute11 => p_txn_pty_acct_detail_rec.attribute11,
p_attribute12 => p_txn_pty_acct_detail_rec.attribute12,
p_attribute13 => p_txn_pty_acct_detail_rec.attribute13,
p_attribute14 => p_txn_pty_acct_detail_rec.attribute14,
p_attribute15 => p_txn_pty_acct_detail_rec.attribute15,
p_created_by => g_user_id,
p_creation_date => sysdate,
p_last_updated_by => g_user_id,
p_last_update_date => sysdate,
p_last_update_login => g_login_id,
p_object_version_number => 1.0,
p_context => p_txn_pty_acct_detail_rec.context);
'csi_t_party_accounts_pkg.insert_row Failed. '||substr(sqlerrm,1,200));
PROCEDURE update_txn_party_dtls(
p_api_version IN NUMBER,
p_commit IN VARCHAR2 := fnd_api.g_false,
p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
p_txn_party_detail_tbl IN csi_t_datastructures_grp.txn_party_detail_tbl,
px_txn_pty_acct_detail_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_pty_acct_detail_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_txn_party_dtls';
SELECT *
FROM csi_t_party_details
WHERE txn_party_detail_id = p_pty_dtl_id;
SELECT *
FROM csi_t_party_accounts
WHERE txn_party_detail_id = p_pty_dtl_id;
SELECT txn_party_detail_id, preserve_detail_flag
FROM csi_t_party_details
WHERE contact_party_id = p_txn_party_detail_id
AND contact_flag = 'Y';
SAVEPOINT update_txn_party_dtls;
l_pty_rec.last_updated_by := g_user_id;
l_pty_rec.last_update_date := sysdate;
l_pty_rec.last_update_login := g_login_id;
csi_t_txn_parties_pvt.delete_txn_pty_acct_dtls(
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_txn_pty_acct_ids_tbl => l_del_pa_ids_tbl,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
p_api_name => 'delete_row',
p_pkg_name => 'csi_t_party_details_pkg');
csi_t_party_details_pkg.delete_row(
p_txn_party_detail_id => cont_pty_rec.txn_party_detail_id);
p_api_name => 'update_row',
p_pkg_name => 'csi_t_party_details_pkg');
csi_t_party_details_pkg.update_row(
p_txn_party_detail_id => l_pty_rec.txn_party_detail_id,
p_txn_line_detail_id => l_pty_rec.txn_line_detail_id,
p_party_source_table => l_pty_rec.party_source_table,
p_party_source_id => l_pty_rec.party_source_id,
p_relationship_type_code => l_pty_rec.relationship_type_code,
p_contact_flag => l_pty_rec.contact_flag,
p_active_start_date => l_pty_rec.active_start_date,
p_active_end_date => l_pty_rec.active_end_date,
p_preserve_detail_flag => l_pty_rec.preserve_detail_flag,
p_instance_party_id => l_pty_rec.instance_party_id,
p_attribute1 => l_pty_rec.attribute1,
p_attribute2 => l_pty_rec.attribute2,
p_attribute3 => l_pty_rec.attribute3,
p_attribute4 => l_pty_rec.attribute4,
p_attribute5 => l_pty_rec.attribute5,
p_attribute6 => l_pty_rec.attribute6,
p_attribute7 => l_pty_rec.attribute7,
p_attribute8 => l_pty_rec.attribute8,
p_attribute9 => l_pty_rec.attribute9,
p_attribute10 => l_pty_rec.attribute10,
p_attribute11 => l_pty_rec.attribute11,
p_attribute12 => l_pty_rec.attribute12,
p_attribute13 => l_pty_rec.attribute13,
p_attribute14 => l_pty_rec.attribute14,
p_attribute15 => l_pty_rec.attribute15,
p_created_by => l_pty_rec.created_by,
p_creation_date => l_pty_rec.creation_date,
p_last_updated_by => l_pty_rec.last_updated_by,
p_last_update_date => l_pty_rec.last_update_date,
p_last_update_login => l_pty_rec.last_update_login,
p_object_version_number => l_pty_rec.object_version_number,
p_context => l_pty_rec.context,
p_contact_party_id => l_pty_rec.contact_party_id,
p_primary_flag => l_pty_rec.primary_flag,
p_preferred_flag => l_pty_rec.preferred_flag);
'csi_t_party_details_pkg.update_row Failed. '||substr(sqlerrm,1,200));
fnd_api.g_miss_num THEN -- it is a update account
-- Build the acct tbl to call it at the end...
l_u_pty_acct_tbl(l_u_acct_ind).txn_account_detail_id :=
px_txn_pty_acct_detail_tbl(l_pa_ind).txn_account_detail_id;
csi_t_txn_parties_pvt.update_txn_pty_acct_dtls(
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_txn_pty_acct_detail_tbl => l_u_pty_acct_tbl,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
ROLLBACK TO update_txn_party_dtls;
ROLLBACK TO update_txn_party_dtls;
ROLLBACK TO update_txn_party_dtls;
END update_txn_party_dtls;
PROCEDURE update_txn_pty_acct_dtls (
p_api_version IN NUMBER
,p_commit IN VARCHAR2 := fnd_api.g_false
,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
,p_txn_pty_acct_detail_tbl IN csi_t_datastructures_grp.txn_pty_acct_detail_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_txn_pty_acct_dtls';
SELECT *
FROM csi_t_party_accounts
where txn_account_detail_id = p_acct_dtl_id;
SAVEPOINT update_txn_pty_acct_dtls;
l_pa_rec.last_updated_by := g_user_id;
l_pa_rec.last_update_date := sysdate;
l_pa_rec.last_update_login := g_login_id;
p_api_name => 'update_row',
p_pkg_name => 'csi_t_party_details_pkg');
csi_t_party_accounts_pkg.update_row (
p_txn_account_detail_id => l_pa_rec.txn_account_detail_id,
p_txn_party_detail_id => l_pa_rec.txn_party_detail_id,
p_ip_account_id => l_pa_rec.ip_account_id,
p_account_id => l_pa_rec.account_id,
p_relationship_type_code => l_pa_rec.relationship_type_code,
p_bill_to_address_id => l_pa_rec.bill_to_address_id,
p_ship_to_address_id => l_pa_rec.ship_to_address_id,
p_active_start_date => l_pa_rec.active_start_date,
p_active_end_date => l_pa_rec.active_end_date,
p_preserve_detail_flag => l_pa_rec.preserve_detail_flag,
p_attribute1 => l_pa_rec.attribute1,
p_attribute2 => l_pa_rec.attribute2,
p_attribute3 => l_pa_rec.attribute3,
p_attribute4 => l_pa_rec.attribute4,
p_attribute5 => l_pa_rec.attribute5,
p_attribute6 => l_pa_rec.attribute6,
p_attribute7 => l_pa_rec.attribute7,
p_attribute8 => l_pa_rec.attribute8,
p_attribute9 => l_pa_rec.attribute9,
p_attribute10 => l_pa_rec.attribute10,
p_attribute11 => l_pa_rec.attribute11,
p_attribute12 => l_pa_rec.attribute12,
p_attribute13 => l_pa_rec.attribute13,
p_attribute14 => l_pa_rec.attribute14,
p_attribute15 => l_pa_rec.attribute15,
p_created_by => l_pa_rec.created_by,
p_creation_date => l_pa_rec.creation_date,
p_last_updated_by => l_pa_rec.last_updated_by,
p_last_update_date => l_pa_rec.last_update_date,
p_last_update_login => l_pa_rec.last_update_login,
p_object_version_number => l_pa_rec.object_version_number,
p_context => l_pa_rec.context);
'csi_t_party_accounts_pkg.update_row Failed. '||substr(sqlerrm,1,200));
ROLLBACK TO update_txn_pty_acct_dtls;
ROLLBACK TO update_txn_pty_acct_dtls;
ROLLBACK TO update_txn_pty_acct_dtls;
END update_txn_pty_acct_dtls;
PROCEDURE delete_txn_party_dtls(
p_api_version IN NUMBER
,p_commit IN VARCHAR2 := fnd_api.g_false
,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
,p_txn_party_ids_tbl IN csi_t_datastructures_grp.txn_party_ids_tbl
,x_txn_pty_acct_ids_tbl OUT NOCOPY csi_t_datastructures_grp.txn_pty_acct_ids_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) := 'delete_txn_party_dtls';
SELECT txn_account_detail_id
FROM csi_t_party_accounts
WHERE txn_party_detail_id = p_txn_party_detail_id;
SELECT txn_party_detail_id
FROM csi_t_party_details
WHERE txn_line_detail_id = p_line_dtl_id;
SELECT txn_party_detail_id
FROM csi_t_party_details
WHERE contact_party_id = p_txn_party_detail_id
AND contact_flag = 'Y';
SAVEPOINT delete_txn_party_dtls;
csi_t_txn_parties_pvt.delete_txn_pty_acct_dtls(
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_txn_pty_acct_ids_tbl => x_txn_pty_acct_ids_tbl,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
p_api_name => 'delete_row',
p_pkg_name => 'csi_t_party_details_pkg');
csi_t_party_details_pkg.delete_row(
p_txn_party_detail_id => cont_pty_rec.txn_party_detail_id);
p_api_name => 'delete_row',
p_pkg_name => 'csi_t_party_details_pkg');
csi_t_party_details_pkg.delete_row(
p_txn_party_detail_id => l_pty_dtl_id);
csi_t_txn_parties_pvt.delete_txn_pty_acct_dtls(
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_txn_pty_acct_ids_tbl => x_txn_pty_acct_ids_tbl,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
p_api_name => 'delete_row',
p_pkg_name => 'csi_t_party_details_pkg');
csi_t_party_details_pkg.delete_row(
p_txn_party_detail_id => cont_pty_rec.txn_party_detail_id);
p_api_name => 'delete_row',
p_pkg_name => 'csi_t_party_details_pkg');
csi_t_party_details_pkg.delete_row(
p_txn_party_detail_id => l_pty_dtl_id);
ROLLBACK TO delete_txn_party_dtls;
ROLLBACK TO delete_txn_party_dtls;
ROLLBACK TO delete_txn_party_dtls;
END delete_txn_party_dtls;
/* deletes the party accounts based on the ids passed */
PROCEDURE delete_txn_pty_acct_dtls(
p_api_version IN NUMBER
,p_commit IN VARCHAR2 := fnd_api.g_false
,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
,p_txn_pty_acct_ids_tbl IN csi_t_datastructures_grp.txn_pty_acct_ids_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) := 'delete_txn_pty_acct_dtls';
SELECT txn_account_detail_id
FROM csi_t_party_accounts
WHERE txn_party_detail_id = p_pty_dtl_id;
SAVEPOINT delete_txn_pty_acct_dtls;
p_api_name => 'delete_row',
p_pkg_name => 'csi_t_party_accounts_pkg');
csi_t_party_accounts_pkg.delete_row(
p_txn_account_detail_id => l_acct_dtl_id);
p_api_name => 'delete_row',
p_pkg_name => 'csi_t_party_accounts_pkg');
csi_t_party_accounts_pkg.delete_row(
p_txn_account_detail_id => pty_acc_rec.txn_account_detail_id);
ROLLBACK TO delete_txn_pty_acct_dtls;
ROLLBACK TO delete_txn_pty_acct_dtls;
ROLLBACK TO delete_txn_pty_acct_dtls;
END delete_txn_pty_acct_dtls;
l_select_stmt varchar2(2000);
l_select_stmt :=
'select txn_account_detail_id, txn_party_detail_id, ip_account_id, '||
' account_id, relationship_type_code, bill_to_address_id, ship_to_address_id, '||
' active_start_date, active_end_date, preserve_detail_flag, context, '||
' attribute1, attribute2, attribute3, attribute4, attribute5, attribute6, '||
' attribute7, attribute8, attribute9, attribute10, attribute11, attribute12, '||
' attribute13, attribute14, attribute15, object_version_number '||
'from csi_t_party_accounts '||
'where txn_party_detail_id = :party_dtl_id';
dbms_sql.parse(l_pty_acct_cur_id, l_select_stmt , dbms_sql.native);
l_select_stmt varchar2(2000);
l_select_stmt :=
'select txn_party_detail_id, txn_line_detail_id, instance_party_id, '||
' party_source_table, party_source_id, relationship_type_code, contact_flag, '||
' contact_party_id, active_start_date, active_end_date, preserve_detail_flag, '||
' 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_t_party_details '||
'where txn_line_detail_id = :line_dtl_id';
dbms_sql.parse(l_pty_cur_id, l_select_stmt , dbms_sql.native);
l_tmp_party_detail_tbl.delete;
l_tmp_pa_tbl.delete;