[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT *
FROM csi_item_instances
WHERE instance_id = p_ins_id;
CSI_MASS_EDIT_ENTRIES_B_PKG.Insert_Row(
px_ENTRY_ID => px_mass_edit_rec.ENTRY_ID,
px_TXN_LINE_ID => px_mass_edit_rec.TXN_LINE_ID,
px_TXN_LINE_DETAIL_ID => px_mass_edit_rec.TXN_LINE_DETAIL_ID,
p_STATUS_CODE => px_mass_edit_rec.STATUS_CODE,
p_SCHEDULE_DATE => px_mass_edit_rec.SCHEDULE_DATE,
p_START_DATE => px_mass_edit_rec.START_DATE,
p_END_DATE => px_mass_edit_rec.END_DATE,
p_NAME => px_mass_edit_rec.NAME,
p_BATCH_TYPE => px_mass_edit_rec.BATCH_TYPE,
p_DESCRIPTION => px_mass_edit_rec.DESCRIPTION,
p_CREATED_BY => csi_mass_edit_pub.g_user_id,
p_CREATION_DATE => sysdate,
p_LAST_UPDATED_BY => csi_mass_edit_pub.g_user_id,
p_LAST_UPDATE_DATE => sysdate,
p_LAST_UPDATE_LOGIN => csi_mass_edit_pub.g_login_id,
p_OBJECT_VERSION_NUMBER => 1.0,
p_SYSTEM_CASCADE => px_mass_edit_rec.SYSTEM_CASCADE
);
SELECT INSTANCE_PARTY_ID
INTO l_instance_party_id
FROM CSI_I_PARTIES
WHERE INSTANCE_ID = l_instance_csr.instance_id and
PARTY_ID = l_instance_csr.owner_party_id and
PARTY_SOURCE_TABLE = l_instance_csr.owner_party_source_table and
RELATIONSHIP_TYPE_CODE = 'OWNER';
SELECT IP_ACCOUNT_ID
INTO l_ip_account_id
FROM CSI_IP_ACCOUNTS
WHERE INSTANCE_PARTY_ID = l_instance_party_id AND
RELATIONSHIP_TYPE_CODE = 'OWNER' AND
PARTY_ACCOUNT_ID = l_instance_csr.owner_party_account_id;
csi_t_txn_details_grp.update_transaction_dtls(
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_txn_line_rec => l_txn_line_rec,
px_txn_line_detail_tbl => l_txn_line_detail_tbl,
px_txn_ii_rltns_tbl => l_txn_ii_rltns_tbl,
px_txn_party_detail_tbl => l_txn_party_detail_tbl,
px_txn_pty_acct_detail_tbl => l_txn_pty_acct_detail_tbl,
px_txn_org_assgn_tbl => l_txn_org_assgn_tbl,
px_txn_ext_attrib_vals_tbl => l_txn_ext_attrib_vals_tbl,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
SELECT 1
FROM csi_mass_edit_entries_tl
WHERE entry_id <> p_batch_id
AND name = p_batch_name;
SELECT *
INTO l_mass_edit_rec
FROM csi_mass_edit_entries_vl
WHERE entry_id = p_batch_id;
SELECT entry_id
,name
,txn_line_id
,batch_type
,status_code
,schedule_date
INTO l_mass_edit_rec.entry_id
,l_mass_edit_rec.name
,l_mass_edit_rec.txn_line_id
,l_mass_edit_rec.batch_type
,l_mass_edit_rec.status_code
,l_mass_edit_rec.schedule_date
FROM csi_mass_edit_entries_vl
WHERE name = p_batch_name;
Select txn_line_detail_id
Into x_mass_edit_rec.txn_line_detail_id
From csi_t_txn_line_details
Where transaction_line_id = l_mass_edit_rec.txn_line_id
And instance_id is null; -- there can be ONLY one record with no instance ID(dummy...)
SELECT 'X' INTO l_found
FROM csi_mass_edit_entries_vl
WHERE name = p_batch_name;
SELECT sub_type_id
INTO l_sub_type_id
FROM CSI_TXN_SUB_TYPES
WHERE transaction_type_id = 3
AND IB_TXN_TYPE_CODE = p_batch_type;
SELECT status_code
INTO l_status
FROM csi_mass_edit_entries_b cmee,
csi_lookups cl
WHERE cmee.entry_id = p_batch_id
AND cmee.status_code = cl.lookup_code
AND cl.lookup_type = 'CSI_MU_BATCH_STATUSES';
PROCEDURE UPDATE_MASS_EDIT_BATCH (
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,
px_mass_edit_rec IN OUT NOCOPY csi_mass_edit_pub.mass_edit_rec,
px_txn_line_rec IN OUT NOCOPY csi_t_datastructures_grp.txn_line_rec,
px_mass_edit_inst_tbl IN OUT NOCOPY csi_mass_edit_pub.mass_edit_inst_tbl,
px_txn_line_detail_rec IN OUT NOCOPY csi_t_datastructures_grp.txn_line_detail_rec,
px_txn_party_detail_tbl IN OUT NOCOPY 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,
px_txn_ext_attrib_vals_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_ext_attrib_vals_tbl,
x_mass_edit_error_tbl OUT NOCOPY csi_mass_edit_pub.mass_edit_error_tbl,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2) IS
l_api_version NUMBER := 1.0;
l_api_name VARCHAR2(30) := 'UPDATE_MASS_EDIT_BATCH_PVT';
SELECT *
FROM csi_item_instances
WHERE instance_id = p_ins_id;
SAVEPOINT UPDATE_MASS_EDIT_PVT;
p_api_name => 'UPDATE_MASS_EDIT_BATCH',
x_sub_type_id => l_sub_type_id,
x_mass_edit_error_tbl => l_mass_edit_error_tbl);
'UPD', -- Update
l_mass_edit_error_tbl,
l_return_status);
SELECT INSTANCE_PARTY_ID
INTO l_instance_party_id
FROM CSI_I_PARTIES
WHERE INSTANCE_ID = l_instance_csr.instance_id and
PARTY_ID = l_instance_csr.owner_party_id and
PARTY_SOURCE_TABLE = l_instance_csr.owner_party_source_table and
RELATIONSHIP_TYPE_CODE = 'OWNER';
SELECT IP_ACCOUNT_ID
INTO l_ip_account_id
FROM CSI_IP_ACCOUNTS
WHERE INSTANCE_PARTY_ID = l_instance_party_id AND
RELATIONSHIP_TYPE_CODE = 'OWNER' AND
PARTY_ACCOUNT_ID = l_instance_csr.owner_party_account_id;
debug('Setting the active and date and object version number and incrementing index for deleted rows - PL/SQL Table Row: '||tld_idx);
debug('Calling csi_t_txn_details_grp.update_transaction_dtls API');
csi_t_txn_details_grp.update_transaction_dtls(
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_txn_line_rec => l_txn_line_rec,
px_txn_line_detail_tbl => l_txn_line_detail_tbl,
px_txn_ii_rltns_tbl => l_txn_ii_rltns_tbl,
px_txn_party_detail_tbl => l_txn_party_detail_tbl,
px_txn_pty_acct_detail_tbl => l_txn_pty_acct_detail_tbl,
px_txn_org_assgn_tbl => l_txn_org_assgn_tbl,
px_txn_ext_attrib_vals_tbl => l_txn_ext_attrib_vals_tbl,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
'UPD', -- Update
l_mass_edit_error_tbl,
l_return_status);
CSI_MASS_EDIT_ENTRIES_B_PKG.update_Row(
p_ENTRY_ID => px_mass_edit_rec.ENTRY_ID,
p_TXN_LINE_ID => px_mass_edit_rec.TXN_LINE_ID,
p_STATUS_CODE => l_mass_edit_rec.STATUS_CODE,
p_SCHEDULE_DATE => l_mass_edit_rec.SCHEDULE_DATE,
p_START_DATE => l_mass_edit_rec.START_DATE,
p_END_DATE => l_mass_edit_rec.END_DATE,
p_NAME => l_mass_edit_rec.NAME,
p_CREATED_BY => fnd_api.g_miss_num,
p_CREATION_DATE => fnd_api.g_miss_date,
p_LAST_UPDATED_BY => csi_mass_edit_pub.g_user_id,
p_LAST_UPDATE_DATE => sysdate,
p_LAST_UPDATE_LOGIN => csi_mass_edit_pub.g_login_id,
p_OBJECT_VERSION_NUMBER => l_mass_edit_rec.OBJECT_VERSION_NUMBER,
p_DESCRIPTION => l_mass_edit_rec.DESCRIPTION,
p_BATCH_TYPE => px_mass_edit_rec.BATCH_TYPE,
p_SYSTEM_CASCADE => l_mass_edit_rec.SYSTEM_CASCADE
);
ROLLBACK TO update_mass_edit_pvt;
ROLLBACK TO update_mass_edit_pvt;
ROLLBACK TO update_mass_edit_pvt;
END UPDATE_MASS_EDIT_BATCH;
PROCEDURE DELETE_MASS_EDIT_BATCH
(
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_mass_edit_rec IN csi_mass_edit_pub.mass_edit_rec,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_version NUMBER := 1.0;
l_api_name VARCHAR2(30) := 'DELETE_MASS_EDIT_BATCH';
SAVEPOINT delete_mass_edit_batch_pvt;
debug('Cannot Delete a Batch that is being Processed: '||l_mass_edit_rec.status_code);
csi_t_txn_details_grp.delete_transaction_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_transaction_line_id => l_mass_edit_rec.txn_line_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
csi_mass_edit_entries_b_pkg.delete_row(
p_entry_id => l_mass_edit_rec.entry_id);
OKS_IBINT_PUB.DELETE_BATCH(
p_api_version => 1.0,
p_init_msg_list => 'F',
p_batch_id => l_mass_edit_rec.entry_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
ROLLBACK TO Delete_Mass_Edit_Batch_pvt;
ROLLBACK TO Delete_Mass_Edit_Batch_pvt;
ROLLBACK TO Delete_Mass_Edit_Batch_pvt;
END Delete_Mass_Edit_Batch;
SELECT 1
FROM csi_item_instances
WHERE instance_id = pc_instance_id
AND (active_end_date is NULL OR
nvl(active_end_date, sysdate+1) > sysdate);
SELECT cil.transaction_line_id transaction_line_id
,cid.party_source_id party_id
,cid.party_source_table party_source_table
,cil.txn_line_detail_id txn_line_detail_id
,cil.instance_id instance_id
,cid.txn_party_detail_id txn_party_detail_id
FROM csi_t_txn_line_details cil
,csi_t_party_details cid
WHERE cil.transaction_line_id = pc_txn_line_id
AND cil.instance_id IS NOT NULL
AND cid.txn_line_detail_id = cil.txn_line_detail_id
AND cid.relationship_type_code = 'OWNER';
SELECT cia.account_id
FROM csi_t_txn_line_details cil
,csi_t_party_details cid
,csi_t_party_accounts cia
WHERE cil.transaction_line_id = pc_txn_line_id
AND cil.instance_id IS NOT NULL
AND cid.txn_line_detail_id = cil.txn_line_detail_id
AND cid.relationship_type_code = 'OWNER'
AND cid.txn_party_detail_id = cia.txn_party_detail_id
AND cia.txn_party_detail_id = pc_txn_party_detail_id;
SELECT cil.transaction_line_id transaction_line_id
,cii.owner_party_source_table owner_party_source_table
,cii.owner_party_id owner_party_id
,cii.owner_party_account_id owner_party_account_id
,cil.txn_line_detail_id txn_line_detail_id
,cii.instance_id instance_id
FROM csi_t_txn_line_details cil
,csi_item_instances cii
WHERE cil.transaction_line_id = pc_txn_line_id
AND cil.instance_id IS NOT NULL
AND cil.instance_id = cii.instance_id;
SELECT ctpd.txn_party_detail_id,
ctpd.txn_line_detail_id,
ctpd.party_source_id,
ctpd.party_source_table
FROM csi_t_party_details ctpd
WHERE ctpd.txn_line_detail_id = pc_txn_line_id;
SELECT ctpa.txn_account_detail_id,
ctpa.txn_party_detail_id,
ctpa.account_id
FROM csi_t_party_accounts ctpa
WHERE ctpa.txn_party_detail_id = pc_txn_party_detail_id;
SELECT cil.location_id location_id
,cil.instance_status_id instance_status_id
,cil.install_location_id install_location_id
,cil.txn_line_detail_id txn_line_detail_id
FROM csi_t_txn_line_details cil
WHERE cil.transaction_line_id = pc_txn_line_id
AND cil.instance_id IS NULL;
SELECT terminated_flag
FROM csi_instance_statuses
WHERE instance_status_id = pc_instance_status_id;
SELECT cil.active_end_date active_end_date
,cil.txn_line_detail_id txn_line_detail_id
FROM csi_t_txn_line_details cil
WHERE cil.transaction_line_id = pc_txn_line_id
AND cil.instance_id IS NULL;
SELECT cid.active_start_date active_start_date
,cid.txn_line_detail_id txn_line_detail_id
FROM csi_t_txn_line_details cil
,csi_t_party_details cid
WHERE cil.transaction_line_id = pc_txn_line_id
AND cil.instance_id IS NULL
AND cid.txn_line_detail_id = cil.txn_line_detail_id
AND cid.relationship_type_code = 'OWNER';
SELECT cil.install_location_id install_location_id
,cil.location_id location_id
,cil.instance_status_id instance_status_id
,cil.external_reference external_reference
,cil.installation_date installation_date
,cil.csi_system_id csi_system_id
FROM csi_t_txn_line_details cil
WHERE cil.txn_line_detail_id = pc_txn_line_detail_id
AND cil.instance_id IS NOT NULL;
SELECT cil.transaction_line_id transaction_line_id
,cii.owner_party_source_table owner_party_source_table
,cii.owner_party_id owner_party_id
,cii.owner_party_account_id owner_party_account_id
,cil.txn_line_detail_id txn_line_detail_id
,cii.location_id location_id
,cii.install_location_id install_location_id
,cii.instance_status_id instance_status_id
,cii.external_reference external_reference
,cii.system_id system_id
,cii.location_type_code location_type_code
,cii.instance_usage_code instance_usage_code
,cii.instance_id instance_id
,cii.install_date install_date
FROM csi_t_txn_line_details cil
,csi_item_instances cii
WHERE cil.transaction_line_id = pc_txn_line_id
AND cil.instance_id IS NOT NULL
AND cil.instance_id = cii.instance_id;
SELECT 1
FROM CSI_SYSTEMS_B
WHERE SYSTEM_ID = p_system_id
AND (end_date_active is NULL OR
nvl(end_date_active, sysdate+1) > sysdate);
SELECT CUSTOMER_ID
FROM CSI_SYSTEMS_B
WHERE SYSTEM_ID = p_system_id;