[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_temp_merror_tbl.delete;
l_temp_merror_tbl.DELETE(i);
l_temp_merror_tbl.DELETE(j);
PROCEDURE update_error_status (
p_error_tbl IN csi_mass_edit_pub.Mass_Upd_Rep_Error_Tbl,
p_txn_line_id IN NUMBER,
p_entry_id IN NUMBER
) is
l_instance_id NUMBER;
UPDATE csi_mass_edit_entries_b
SET status_code = 'FAILED'
WHERE entry_id = p_entry_id;
UPDATE csi_t_txn_line_details
SET processing_status = 'ERROR',
ERROR_CODE = 'E'
WHERE transaction_line_id = p_txn_line_id
AND instance_id = l_instance_id;
UPDATE csi_t_txn_line_details
SET processing_status = 'WARNING',
ERROR_CODE = 'W'
WHERE transaction_line_id = p_txn_line_id
AND instance_id = l_instance_id;
End update_error_status;
PROCEDURE UPDATE_MUSYS_ERR_STATUS (
p_entry_id IN NUMBER
) IS
PRAGMA AUTONOMOUS_TRANSACTION;
UPDATE csi_mass_edit_entries_b
SET status_code = 'FAILED'
WHERE entry_id = p_entry_id;
END UPDATE_MUSYS_ERR_STATUS;
SELECT cii.location_id location_id
,cii.location_type_code location_type_code
,cii.install_location_id install_location_id
,cii.install_location_type_code install_location_type_code
,cip.party_id party_id
,cip.party_source_table party_source_table
FROM csi_item_instances cii
,csi_i_parties cip
WHERE cii.instance_id = p_instance_id
AND cip.instance_id = cii.instance_id
AND cip.relationship_type_code = 'OWNER';
SELECT cil.location_id location_id
,cil.location_type_code location_type_code
,cil.install_location_id install_location_id
,cil.install_location_type_code install_location_type_code
,cid.party_source_id party_id
,cid.party_source_table party_source_table
FROM csi_t_txn_line_details cil
,csi_t_party_details cid
WHERE cil.transaction_line_id = p_txn_line_id
AND cil.instance_id = p_instance_id
AND cid.txn_line_detail_id = cil.txn_line_detail_id
AND cid.relationship_type_code = 'OWNER';
SELECT entry_id, batch_type, name, schedule_date
FROM csi_mass_edit_entries_vl
WHERE status_code = 'SCHEDULED'
AND schedule_date <= SYSDATE
AND entry_id = nvl(p_entry_id,entry_id);
Select 'Y' INTO l_dummy from dual where
EXISTS( select 'Y'
from csi_mass_edit_entries_vl cmeev, CSI_WEBADI_STAGING cws
WHERE cmeev.ENTRY_ID = cws.BATCH_ID
AND cmeev.STATUS_CODE IN ('SUCCESSFUL', 'FAILED')
AND cmeev.BATCH_TYPE = 'WBADI'
AND nvl(cws.PROCESSING_STATUS, 'N') <> 'P'
AND cmeev.ENTRY_ID = p_entry_id);
debug('The Mass update batch being processed is a re-uploaded Web ADI batch.');
debug_out('The Mass update batch being processed is a re-uploaded Web ADI batch.');
UPDATE csi_mass_edit_entries_b
SET status_code = 'SCHEDULED',
SCHEDULE_DATE = SYSDATE
WHERE BATCH_TYPE = 'WBADI'
AND status_code = 'CREATED';
CURSOR selected_instance_csr (p_txn_line_id IN NUMBER) IS
SELECT ctld.instance_id
,ctld.transaction_line_id
,nvl(ctld.location_id, FND_API.G_MISS_NUM) location_id
,nvl(ctld.location_type_code, FND_API.G_MISS_CHAR) location_type_code
,nvl(ctld.install_location_id, FND_API.G_MISS_NUM) install_location_id
,nvl(ctld.install_location_type_code, FND_API.G_MISS_CHAR) install_location_type_code
,cii.object_version_number
,cii.instance_usage_code
FROM csi_t_txn_line_details ctld,
csi_item_instances cii
WHERE ctld.transaction_line_id = p_txn_line_id
AND ctld.INSTANCE_ID is not null
AND ctld.preserve_detail_flag = 'Y'
AND cii.instance_id = ctld.instance_id;
SELECT cip.instance_party_id pty_instance_party_id,
cip.instance_id pty_instance_id,
cip.party_source_table pty_party_source_table,
cip.party_id pty_party_id,
cip.relationship_type_code pty_rel_type_code,
cip.object_version_number pty_obj_version_number
,cia.ip_account_id pty_acc_ip_account_id,
cia.instance_party_id pty_acc_instance_party_id,
cia.party_account_id pty_acc_party_account_id,
cia.relationship_type_code pty_acct_rel_type_code,
cia.object_version_number pty_acct_obj_version_number
FROM csi_i_parties cip,
csi_ip_accounts cia,
csi_t_txn_line_details ctld
WHERE cip.instance_id = ctld.instance_id
AND cip.contact_flag ='N'
AND cip.relationship_type_code = 'OWNER'
AND ctld.transaction_line_id = p_txn_line_id
AND ctld.instance_exists_flag = 'Y'
AND ctld.preserve_detail_flag = 'Y'
AND cia.instance_party_id = cip.instance_party_id
AND cia.relationship_type_code = 'OWNER'
UNION
SELECT cip.instance_party_id pty_instance_party_id,
cip.instance_id pty_instance_id,
cip.party_source_table pty_party_source_table,
cip.party_id pty_party_id,
cip.relationship_type_code pty_rel_type_code,
cip.object_version_number pty_obj_version_number,
null pty_acc_ip_account_id,
null pty_acc_instance_party_id,
null pty_acc_party_account_id,
null pty_acct_rel_type_code,
null pty_acct_obj_version_number
FROM csi_i_parties cip,
csi_t_txn_line_details ctld
WHERE cip.instance_id = ctld.instance_id
AND cip.contact_flag ='N'
AND cip.relationship_type_code = 'OWNER'
AND ctld.transaction_line_id = p_txn_line_id
AND ctld.instance_exists_flag = 'Y'
AND ctld.preserve_detail_flag = 'Y'
AND cip.party_id in (SELECT INTERNAL_PARTY_ID FROM csi_install_parameters)
AND NOT EXISTS (SELECT 'X' FROM csi_i_parties cip, csi_ip_accounts cia
WHERE cip.INSTANCE_PARTY_ID = cia.INSTANCE_PARTY_ID
AND cip.instance_id = ctld.instance_id
AND cip.contact_flag = 'N'
AND cip.relationship_type_code = 'OWNER');
SELECT cip.instance_party_id pty_instance_party_id,
cip.instance_id pty_instance_id,
cip.party_source_table pty_party_source_table,
cip.party_id pty_party_id,
cip.relationship_type_code pty_rel_type_code,
cip.object_version_number pty_obj_version_number
FROM csi_i_parties cip
WHERE cip.instance_id = p_instance_id
AND cip.contact_flag = 'N'
AND nvl(cip.active_end_date,sysdate) >= sysdate;
SELECT ciev.attribute_value_id
,ciev.attribute_id
,ctld.instance_id -- Bug 7613909
,ciev.attribute_value
,ciev.object_version_number
FROM csi_iea_values ciev,
csi_t_txn_line_details ctld
WHERE ciev.instance_id (+) = ctld.instance_id -- Bug 7613909
AND ctld.transaction_line_id = p_txn_line_id
AND ctld.instance_id is not null;
SELECT a.txn_line_id,
a.batch_type,
b.meaning,
a.description,
a.name,
a.schedule_date,
a.system_cascade
INTO l_txn_line_id,
l_batch_type,
l_batch_meaning,
l_batch_desc,
l_name,
l_batch_sch_date,
l_system_cascade
FROM csi_mass_edit_entries_vl a,
csi_lookups b
WHERE a.entry_id = p_entry_id
AND a.status_code = 'SCHEDULED'
AND a.schedule_date <= SYSDATE
AND b.lookup_type = 'CSI_IB_TXN_TYPE_CODE'
AND b.lookup_code = a.batch_type;
SELECT ib_txn_type_code
INTO l_found_batch
FROM csi_txn_sub_types
WHERE transaction_type_id = 3
AND ib_txn_type_code = l_batch_type;
UPDATE csi_mass_edit_entries_b
SET status_code = 'PROCESSING',
request_id = FND_GLOBAL.CONC_REQUEST_ID
WHERE entry_id = p_entry_id;
l_temp_merror_tbl.delete;
update_error_status(l_temp_merror_tbl,l_txn_line_id,p_entry_id);
FOR l_instance_csr IN selected_instance_csr(l_txn_line_id)
LOOP
n_inst_ind := n_inst_ind + 1;
csi_item_instance_grp.update_item_instance (
p_api_version => 1.0
,p_commit => fnd_api.g_false
,p_init_msg_list => fnd_api.g_true
,p_validation_level => fnd_api.g_valid_level_full
,p_instance_tbl => n_instance_tbl
,p_ext_attrib_values_tbl => n_ext_attrib_values_tbl
,p_party_tbl => n_party_tbl
,p_account_tbl => n_party_account_tbl
,p_pricing_attrib_tbl => n_price_tbl
,p_org_assignments_tbl => n_org_assignments_tbl
,p_asset_assignment_tbl => n_asset_assignment_tbl
,p_txn_rec => n_txn_rec
,x_instance_id_lst => n_instance_id_lst
,p_grp_upd_error_tbl => n_grp_error_tbl
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
update_error_status(l_temp_merror_tbl,l_txn_line_id,p_entry_id);
l_temp_merror_tbl.delete;
p_last_update_date => SYSDATE,
p_last_updated_by => FND_GLOBAL.USER_ID,
p_creation_date => SYSDATE,
p_created_by => FND_GLOBAL.USER_ID
);
Debug('Checking for system mass update');
Debug_out('Checking for system mass update');
UPDATE csi_t_transaction_lines
SET processing_status = 'PROCESSED'
WHERE transaction_line_id = l_txn_line_id;
UPDATE csi_t_txn_line_details
SET error_explanation = '',
processing_status = 'PROCESSED',
error_code = 'P'
WHERE transaction_line_id = l_txn_line_id
AND instance_id is not null;
UPDATE csi_mass_edit_entries_b
SET status_code = 'SUCCESSFUL'
WHERE entry_id = p_entry_id;
update_error_status(l_temp_merror_tbl,l_txn_line_id,p_entry_id);
SELECT 'x' INTO l_new_owner
FROM CSI_T_PARTY_DETAILS WHERE TXN_LINE_DETAIL_ID IN(
SELECT TXN_LINE_DETAIL_ID FROM CSI_T_TXN_LINE_DETAILS CTLD, CSI_MASS_EDIT_ENTRIES_VL CMEE
WHERE CTLD.TRANSACTION_LINE_ID = CMEE.TXN_LINE_ID
AND CMEE.ENTRY_ID = p_entry_id
AND INSTANCE_ID IS NULL)
AND PARTY_SOURCE_TABLE = 'HZ_PARTIES'
AND RELATIONSHIP_TYPE_CODE = 'OWNER'
AND ROWNUM = 1;
SELECT 'x' INTO l_old_owner
FROM CSI_T_PARTY_DETAILS WHERE TXN_LINE_DETAIL_ID IN(
SELECT TXN_LINE_DETAIL_ID FROM CSI_T_TXN_LINE_DETAILS CTLD, CSI_MASS_EDIT_ENTRIES_VL CMEE
WHERE CTLD.TRANSACTION_LINE_ID = CMEE.TXN_LINE_ID
AND CMEE.ENTRY_ID = p_entry_id
AND INSTANCE_ID IS NOT NULL)
AND PARTY_SOURCE_TABLE = 'HZ_PARTIES'
AND RELATIONSHIP_TYPE_CODE = 'OWNER'
AND ROWNUM = 1;
debug('The Batch doesn not qualify for system mass update
as the owner type is not HZ_PARTIES');
PROCESS_SYSTEM_MASS_UPDATE (
p_api_version => 1.0
,p_commit => fnd_api.g_false
,p_Entry_id => p_Entry_id
,p_instance_tbl => n_instance_tbl
,p_ext_attrib_values_tbl => n_ext_attrib_values_tbl -- Not used, retained for future enhancements
,p_party_tbl => n_party_tbl
,p_account_tbl => n_party_account_tbl
,p_txn_rec => n_txn_rec
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
UPDATE csi_t_transaction_lines
SET processing_status = 'PROCESSED'
WHERE transaction_line_id = l_txn_line_id;
UPDATE csi_t_txn_line_details
SET error_explanation = '',
processing_status = 'PROCESSED',
error_code = 'P'
WHERE transaction_line_id = l_txn_line_id
AND instance_id is not null;
UPDATE csi_mass_edit_entries_b
SET status_code = 'SUCCESSFUL'
WHERE entry_id = p_entry_id;
update_error_status(l_temp_merror_tbl,l_txn_line_id,p_entry_id);
UPDATE_MUSYS_ERR_STATUS(p_entry_id);
CURSOR selected_instance_csr (p_txn_line_id IN NUMBER) IS
select cii.instance_id,
cii.instance_number
from csi_instance_search_v cii,
csi_t_txn_line_details tld
where tld.transaction_line_id = p_txn_line_id and
tld.instance_id is not null and
tld.instance_id = cii.instance_id and
nvl(tld.active_end_date(+),sysdate+1) > sysdate;
FOR l_instance_csr IN selected_instance_csr(p_txn_line_id)
LOOP
if px_mass_edit_inst_tbl.count > 0 then
for i in px_mass_edit_inst_tbl.first..px_mass_edit_inst_tbl.last
loop
IF l_instance_csr.instance_id = px_mass_edit_inst_tbl(i).instance_id THEN
IF x_output IS NOT NULL THEN
x_output := x_output || ',' ;
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 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 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_PUB';
SAVEPOINT update_mass_edit_pub;
csi_t_gen_utility_pvt.add('API Being Executed : UPDATE_MASS_EDIT_BATCH_PUB');
csi_mass_edit_pvt.update_mass_edit_batch
(
p_api_version => l_api_version,
p_commit => p_commit,
p_init_msg_list => p_init_msg_list,
p_validation_level => p_validation_level,
px_mass_edit_rec => px_mass_edit_rec,
px_txn_line_rec => px_txn_line_rec,
px_mass_edit_inst_tbl => px_mass_edit_inst_tbl,
px_txn_line_detail_rec => px_txn_line_detail_rec,
px_txn_party_detail_tbl => px_txn_party_detail_tbl,
px_txn_pty_acct_detail_tbl => px_txn_pty_acct_detail_tbl,
px_txn_ext_attrib_vals_tbl => px_txn_ext_attrib_vals_tbl,
x_mass_edit_error_tbl => x_mass_edit_error_tbl,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
ROLLBACK TO update_mass_edit_pub;
ROLLBACK TO update_mass_edit_pub;
ROLLBACK TO update_mass_edit_pub;
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 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;
csi_t_gen_utility_pvt.add('API Being Executed : DELETE_MASS_EDIT_BATCH');
csi_mass_edit_pvt.delete_mass_edit_batch (
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_mass_edit_rec => p_mass_edit_rec,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
csi_t_gen_utility_pvt.add('API Executed : Delete Mass Edit Batch');
ROLLBACK TO Delete_Mass_Edit_Batch;
ROLLBACK TO Delete_Mass_Edit_Batch;
ROLLBACK TO Delete_Mass_Edit_Batch;
END Delete_Mass_Edit_Batch;
/* This is the wrapper API to handle multiple batch deletes. Calls the DELETE_MASS_EDIT_BATCH */
PROCEDURE DELETE_MASS_EDIT_BATCHES
(
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_tbl IN mass_edit_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) := 'DELETE_MASS_EDIT_BATCHES';
SAVEPOINT delete_mass_edit_batches;
csi_t_gen_utility_pvt.add('API Being Executed : DELETE_MASS_EDIT_BATCHES');
csi_mass_edit_pvt.delete_mass_edit_batch (
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_mass_edit_rec => p_mass_edit_tbl(m_ind),
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
csi_t_gen_utility_pvt.add('API Executed : Delete Mass Edit Batches');
ROLLBACK TO Delete_Mass_Edit_Batches;
ROLLBACK TO Delete_Mass_Edit_Batches;
ROLLBACK TO Delete_Mass_Edit_Batches;
END Delete_Mass_Edit_Batches;
/* Procedure name: PROCESS_SYSTEM_MASS_UPDATE */
/* Description : procedure used to update System in */
/* mass update batch */
/*----------------------------------------------------*/
PROCEDURE PROCESS_SYSTEM_MASS_UPDATE
(
p_api_version IN NUMBER
,p_commit IN VARCHAR2 := fnd_api.g_false
,p_entry_id IN NUMBER
,p_instance_tbl IN OUT NOCOPY csi_datastructures_pub.instance_tbl
,p_ext_attrib_values_tbl IN OUT NOCOPY csi_datastructures_pub.extend_attrib_values_tbl
,p_party_tbl IN OUT NOCOPY csi_datastructures_pub.party_tbl
,p_account_tbl IN OUT NOCOPY 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 mu_new_party_csr(p_transaction_line_id NUMBER) IS
SELECT tp.party_source_table PartySourceTable,
tp.party_source_id PartyId,
ta.account_id AccountId,
party.party_number PartyNumber
FROM csi_t_txn_line_details tld,
csi_t_party_details tp,
csi_t_party_accounts ta,
hz_parties party,
hz_cust_accounts account
WHERE tld.txn_line_detail_id = tp.txn_line_detail_id
AND tld.transaction_line_id = p_transaction_line_id
AND tld.INSTANCE_ID IS NULL
AND tp.txn_party_detail_id = ta.txn_party_detail_id
AND tp.party_source_id = party.party_id
AND ta.account_id = account.cust_account_id
AND tp.relationship_type_code = 'OWNER'
AND tp.party_source_table = 'HZ_PARTIES';
SELECT SYSTEM_TYPE_CODE,
SYSTEM_NUMBER,
PARENT_SYSTEM_ID,
COTERMINATE_DAY_MONTH,
START_DATE_ACTIVE,
END_DATE_ACTIVE,
CONTEXT,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
OBJECT_VERSION_NUMBER,
OPERATING_UNIT_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID
FROM CSI_SYSTEMS_B
WHERE SYSTEM_ID = p_system_id;
SELECT NAME,
DESCRIPTION
FROM CSI_SYSTEMS_TL
WHERE SYSTEM_ID = p_system_id;
l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_SYSTEM_MASS_UPDATE';
SAVEPOINT PROCESS_SYSTEM_MASS_UPDATE;
debug('Inside PROCESS_SYSTEM_MASS_UPDATE');
csi_gen_utility_pvt.put_line( 'PROCESS_SYSTEM_MASS_UPDATE');
SELECT cmee.txn_line_id
INTO l_txn_line_id
FROM csi_mass_edit_entries_vl cmee,
csi_lookups clkps
WHERE cmee.entry_id = p_entry_id
--AND cmee.status_code = 'SCHEDULED'
AND cmee.schedule_date <= SYSDATE
AND clkps.lookup_type = 'CSI_IB_TXN_TYPE_CODE'
AND clkps.lookup_code = cmee.batch_type;
IDENTIFY_SYSTEM_FOR_UPDATE (
p_txn_line_id => l_txn_line_id
,p_upd_system_tbl => upd_system_tbl
,x_return_status => l_return_status);
debug('Calling UPDATE_SYSTEM');
CSI_SYSTEMS_PVT.UPDATE_SYSTEM(
p_api_version => 1.0,
p_commit => FND_API.G_FALSE,
p_init_msg_list => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_system_rec => l_txn_sys_rec,
p_txn_rec => p_txn_rec,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
debug('Error updating systems in PROCESS_SYSTEM_MASS_UPDATE - System ID - ' || upd_system_tbl(system_rec_ind).SYSTEM_ID);
ROLLBACK TO PROCESS_SYSTEM_MASS_UPDATE;
ROLLBACK TO PROCESS_SYSTEM_MASS_UPDATE;
ROLLBACK TO PROCESS_SYSTEM_MASS_UPDATE;
END PROCESS_SYSTEM_MASS_UPDATE; -- PROCESS_SYSTEM_MASS_UPDATE
/* Procedure name: IDENTIFY_SYSTEM_FOR_UPDATE */
/* Description : procedure used to identifies System for */
/* mass update batch */
/*----------------------------------------------------*/
PROCEDURE IDENTIFY_SYSTEM_FOR_UPDATE
(
p_txn_line_id IN NUMBER
,p_upd_system_tbl OUT NOCOPY csi_datastructures_pub.mu_systems_tbl
,x_return_status OUT NOCOPY VARCHAR2
)
IS
-- Cursors
CURSOR distinct_system_cur (txn_line_id IN NUMBER) IS
SELECT distinct cii.system_id system_id
FROM csi_t_txn_line_details ctld,
csi_item_instances cii
WHERE ctld.transaction_line_id = txn_line_id
AND ctld.INSTANCE_ID is not null
AND ctld.preserve_detail_flag = 'Y'
AND cii.instance_id = ctld.instance_id;
debug('Inside IDENTIFY_SYSTEM_FOR_UPDATE');
SELECT COUNT(1)
INTO l_active_instance_count
FROM csi_item_instances cisv,
CSI_SYSTEMS_B csb
WHERE cisv.SYSTEM_ID = l_mu_systems_csr.system_id
AND cisv.system_id = csb.system_id
AND NVL(cisv.ACTIVE_END_DATE,sysdate+1) > sysdate
AND NVL(csb.END_DATE_ACTIVE,sysdate +1) > sysdate;
SELECT COUNT(1)
INTO l_mu_instance_count
FROM csi_t_txn_line_details ctld,
csi_item_instances cii
WHERE ctld.transaction_line_id = p_txn_line_id
AND cii.system_id = l_mu_systems_csr.system_id
AND ctld.INSTANCE_ID IS NOT NULL
AND ctld.preserve_detail_flag = 'Y'
AND cii.instance_id = ctld.instance_id;
debug('System Qualifying for Mass Update - ' || l_mu_systems_csr.system_id);
debug_out('System Qualifying for Mass Update - ' || l_mu_systems_csr.system_id);
debug('Total Number of Systems to be updated - ' || p_upd_system_tbl.COUNT);
debug_out('Total Number of Systems to be updated - ' || p_upd_system_tbl.COUNT);
debug('End IDENTIFY_SYSTEM_FOR_UPDATE');
debug('Exception in IDENTIFY_SYSTEM_FOR_UPDATE');
END IDENTIFY_SYSTEM_FOR_UPDATE; -- IDENTIFY_SYSTEM_FOR_UPDATE
/* before mass update */
/*----------------------------------------------------*/
PROCEDURE VALIDATE_SYSTEM_BATCH
(
p_entry_id IN NUMBER
,p_txn_line_id IN NUMBER
,p_upd_system_tbl IN csi_datastructures_pub.mu_systems_tbl
,x_return_status OUT NOCOPY VARCHAR2
)
IS
CURSOR SYS_ACCOUNT_CSR (p_entry_id IN NUMBER) IS
SELECT TA.ACCOUNT_ID ACCOUNT_ID,
TP.PARTY_SOURCE_ID PARTY_ID
FROM CSI_T_PARTY_DETAILS TP,
CSI_T_PARTY_ACCOUNTS TA ,
CSI_T_TXN_LINE_DETAILS CTLD ,
CSI_MASS_EDIT_ENTRIES_VL CMEE
WHERE CMEE.ENTRY_ID = p_entry_id
AND TA.TXN_PARTY_DETAIL_ID = TP.TXN_PARTY_DETAIL_ID
AND TP.TXN_LINE_DETAIL_ID = CTLD.TXN_LINE_DETAIL_ID
AND CTLD.TRANSACTION_LINE_ID = CMEE.TXN_LINE_ID
AND CTLD.INSTANCE_ID IS NOT NULL
AND ROWNUM = 1;
debug('Total Number of Systems qualifying for mass update - ' || p_upd_system_tbl.COUNT);
UPDATE CSI_WEBADI_STAGING
SET PROCESSING_STATUS = 'P'
WHERE BATCH_ID = p_entry_id;
UPDATE csi_mass_edit_entries_b
SET status_code = 'PROCESSING',
request_id = FND_GLOBAL.CONC_REQUEST_ID
WHERE entry_id = p_entry_id;
SELECT Count(*) INTO l_crt_count
FROM CSI_WEBADI_STAGING
where BATCH_ID = p_entry_id
and INSTANCE_ID is null;
SELECT Count(*) INTO l_updt_count
FROM CSI_WEBADI_STAGING
where BATCH_ID = p_entry_id
and INSTANCE_ID is NOT null;
debug_out('No.of records submitted for update : ' || l_updt_count);
--debug_out('No.of records successfully updated : ' || (l_updt_count - l_updt_fld_count));
--debug_out('No.of update records failed : ' || l_update_fld_count);
Process_webadi_update(errbuf, retcode, p_Entry_id, px_csi_txn_rec);
UPDATE csi_mass_edit_entries_b
SET status_code = 'FAILED'
WHERE entry_id = p_entry_id;
UPDATE csi_mass_edit_entries_b
SET status_code = 'SUCCESSFUL'
WHERE entry_id = p_entry_id;
SELECT
ITEM_ID,
ITEM_NUMBER ,
INSTANCE_ID,
PRODUCT_NUMBER,
INSTANCE_DESCRIPTION,
ITEM_REV,
UOM,
QUANTITY,
SERIAL_NUMBER ,
LOT_NUMBER ,
ORGANIZATION,
LAST_VLD_ORGANIZATION_ID,
INSTANCE_STATUS_ID,
EXTERNAL_REF ,
OPERATIONAL_STATUS_CODE ,
INTERNALLY_OWNED,
OWNER_PARTY_SOURCE_TABLE ,
OWNER_PARTY_TYPE ,
OWNER_PARTY_NUM,
OWNER_NAME,
OWNER_ACCOUNT_NUMBER ,
OWNER_ID,
OWNER_ACCOUNT_ID,
OBJECT_VERSION_NUMBER,
PRODUCT_TYPE_CODE ,
INSTALL_DATE,
VERSION_LABEL ,
CREATION_DATE,
ACTIVE_START_DATE,
EXPIRATION_DATE ,
CONTRACT_NUMBER ,
PARENT_INSTANCE ,
ASSOCIATE_BILL_TO_PARTY_TYPE,
ASSOCIATE_BILL_TO_PARTY_NUMBER,
ASSOCIATE_BILL_TO_PARTY_NAME,
ASSOCIATE_BILL_TO_PARTY_ID,
ASSOCIATE_BILL_TO_ACCT_NUM,
ASSOCIATE_BILL_TO_ACCT_ID,
ASSOCIATE_SHIP_TO_PARTY_TYPE,
ASSOCIATE_SHIP_TO_PARTY_NUMBER,
ASSOCIATE_SHIP_TO_PARTY_NAME,
ASSOCIATE_SHIP_TO_PARTY_ID,
ASSOCIATE_SHIP_TO_ACCT_NUM,
ASSOCIATE_SHIP_TO_ACCT_ID,
BILL_TO_LOC_ID,
SHIP_TO_LOC_ID ,
LOCATION_ID,
LOCATION_TYPE_CODE ,
INSTALL_LOCATION_ID,
INSTALL_LOCATION_TYPE_CODE ,
ASSOCIATE_CONTRACT_ID,
ASSOCIATE_CONTRACT_NUMBER,
ASSOCIATE_CONTRACT_MODIFIER,
ASSOCIATE_SERVICE_LINE,
ASSOCIATE_SERVICE_LINE_ID,
CREATE_CONTRACT,
SYSTEM_ID,
SYSTEM_NAME,
Pricing_context,
Pricing_attribute1,
Pricing_attribute2,
Pricing_attribute3,
Pricing_attribute4,
Pricing_attribute5,
Pricing_attribute6,
Pricing_attribute7,
Pricing_attribute8,
Pricing_attribute9,
Pricing_attribute10,
Pricing_attribute11,
Pricing_attribute12,
Pricing_attribute13,
Pricing_attribute14,
Pricing_attribute15,
Pricing_attribute16,
Pricing_attribute17,
Pricing_attribute18,
Pricing_attribute19,
Pricing_attribute20,
Pricing_attribute21,
Pricing_attribute22,
Pricing_attribute23,
Pricing_attribute24,
Pricing_attribute25,
Pricing_attribute26,
Pricing_attribute27,
Pricing_attribute28,
Pricing_attribute29,
Pricing_attribute30,
Pricing_attribute31,
Pricing_attribute32,
Pricing_attribute33,
Pricing_attribute34,
Pricing_attribute35,
Pricing_attribute36,
Pricing_attribute37,
Pricing_attribute38,
Pricing_attribute39,
Pricing_attribute40,
Pricing_attribute41,
Pricing_attribute42,
Pricing_attribute43,
Pricing_attribute44,
Pricing_attribute45,
Pricing_attribute46,
Pricing_attribute47,
Pricing_attribute48,
Pricing_attribute49,
Pricing_attribute50,
Pricing_attribute51,
Pricing_attribute52,
Pricing_attribute53,
Pricing_attribute54,
Pricing_attribute55,
Pricing_attribute56,
Pricing_attribute57,
Pricing_attribute58,
Pricing_attribute59,
Pricing_attribute60,
Pricing_attribute61,
Pricing_attribute62,
Pricing_attribute63,
Pricing_attribute64,
Pricing_attribute65,
Pricing_attribute66,
Pricing_attribute67,
Pricing_attribute68,
Pricing_attribute69,
Pricing_attribute70,
Pricing_attribute71,
Pricing_attribute72,
Pricing_attribute73,
Pricing_attribute74,
Pricing_attribute75,
Pricing_attribute76,
Pricing_attribute77,
Pricing_attribute78,
Pricing_attribute79,
Pricing_attribute80,
Pricing_attribute81,
Pricing_attribute82,
Pricing_attribute83,
Pricing_attribute84,
Pricing_attribute85,
Pricing_attribute86,
Pricing_attribute87,
Pricing_attribute88,
Pricing_attribute89,
Pricing_attribute90,
Pricing_attribute91,
Pricing_attribute92,
Pricing_attribute93,
Pricing_attribute94,
Pricing_attribute95,
Pricing_attribute96,
Pricing_attribute97,
Pricing_attribute98,
Pricing_attribute99,
Pricing_attribute100,
BATCH_ID,
CONTEXT,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE16,
ATTRIBUTE17,
ATTRIBUTE18,
ATTRIBUTE19,
ATTRIBUTE20,
ATTRIBUTE21,
ATTRIBUTE22,
ATTRIBUTE23,
ATTRIBUTE24,
ATTRIBUTE25,
ATTRIBUTE26,
ATTRIBUTE27,
ATTRIBUTE28,
ATTRIBUTE29,
ATTRIBUTE30
FROM CSI_WEBADI_STAGING
where BATCH_ID = p_entry_id
and INSTANCE_ID is null;
Select DESCRIPTION
into l_batch_desc
FROM csi_mass_edit_entries_vl
where ENTRY_ID = p_Entry_id;
debug_out('The Mass update batch is not found. So the create records are not processed.');
debug_out('In the uploaded update record #' || xls_idx ||
'. For Employee/Vendor party type, Invalid Value(s) or invaid' ||
' combination of values for: ' ||
'Associate Ship To Party Number, Associate Ship To Party Name.');
debug_out('In the uploaded update record #' || xls_idx ||
'. For Employee/Vendor party type, value not entered for' ||
' column: Associate Ship To Party Number.');
debug_out('In the uploaded update record #' || xls_idx ||
'. For Employee/Vendor party type, value not entered for ' ||
'column: Associate Ship To Party Name.');
debug_out('In the uploaded update record #' || xls_idx ||
'. Invalid value entered for column: Associate Ship To Party Number.');
SELECT instance_id INTO l_par_instance_id
FROM csi_item_instances
WHERE instance_number = l_instance.PARENT_INSTANCE;
SELECT INTERNAL_PARTY_ID
INTO l_internal_party_id
FROM csi_install_parameters;
SELECT Count(*) INTO l_crt_count
FROM CSI_WEBADI_STAGING
where BATCH_ID = p_entry_id
and INSTANCE_ID is null;
p_last_update_date => SYSDATE,
p_last_updated_by => FND_GLOBAL.USER_ID,
p_creation_date => SYSDATE,
p_created_by => FND_GLOBAL.USER_ID
);
PROCEDURE Process_webadi_update
( errbuf OUT NOCOPY VARCHAR2
,retcode OUT NOCOPY NUMBER
,p_Entry_id IN NUMBER
,p_csi_txn_rec IN OUT NOCOPY csi_datastructures_pub.transaction_rec
) IS
CURSOR instances_for_update_csr(p_entry_id IN NUMBER) IS
SELECT
ITEM_ID,
ORGANIZATION,
ITEM_NUMBER ,
INSTANCE_ID,
PRODUCT_NUMBER ,
INSTANCE_DESCRIPTION,
ITEM_REV,
UOM,
QUANTITY,
LAST_VLD_ORGANIZATION_ID,
SERIAL_NUMBER ,
LOT_NUMBER ,
INSTANCE_STATUS_ID,
EXTERNAL_REF ,
OPERATIONAL_STATUS_CODE ,
OWNER_PARTY_SOURCE_TABLE ,
OWNER_PARTY_TYPE ,
OWNER_PARTY_NUM,
OWNER_NAME,
OWNER_ACCOUNT_NUMBER ,
OWNER_ID,
OWNER_ACCOUNT_ID,
OBJECT_VERSION_NUMBER,
PRODUCT_TYPE_CODE ,
INSTALL_DATE,
VERSION_LABEL ,
CREATION_DATE,
ACTIVE_START_DATE,
EXPIRATION_DATE ,
CONTRACT_NUMBER ,
PARENT_INSTANCE ,
OLD_PARENT_INSTANCE,
SPLIT_QTY,
ASSOCIATE_BILL_TO_PARTY_TYPE,
ASSOCIATE_BILL_TO_PARTY_NUMBER,
ASSOCIATE_BILL_TO_PARTY_NAME,
ASSOCIATE_BILL_TO_PARTY_ID,
ASSOCIATE_BILL_TO_ACCT_NUM,
ASSOCIATE_BILL_TO_ACCT_ID,
ASSOCIATE_SHIP_TO_PARTY_TYPE,
ASSOCIATE_SHIP_TO_PARTY_NUMBER,
ASSOCIATE_SHIP_TO_PARTY_NAME,
ASSOCIATE_SHIP_TO_PARTY_ID,
ASSOCIATE_SHIP_TO_ACCT_NUM,
ASSOCIATE_SHIP_TO_ACCT_ID,
BILL_TO_LOC_ID,
SHIP_TO_LOC_ID ,
LOCATION_ID,
LOCATION_TYPE_CODE ,
INSTALL_LOCATION_ID,
INSTALL_LOCATION_TYPE_CODE ,
ASSOCIATE_CONTRACT_ID,
ASSOCIATE_CONTRACT_NUMBER,
ASSOCIATE_CONTRACT_MODIFIER,
ASSOCIATE_SERVICE_LINE,
ASSOCIATE_SERVICE_LINE_ID,
SYSTEM_ID,
SYSTEM_NAME,
Pricing_context,
Pricing_attribute1,
Pricing_attribute2,
Pricing_attribute3,
Pricing_attribute4,
Pricing_attribute5,
Pricing_attribute6,
Pricing_attribute7,
Pricing_attribute8,
Pricing_attribute9,
Pricing_attribute10,
Pricing_attribute11,
Pricing_attribute12,
Pricing_attribute13,
Pricing_attribute14,
Pricing_attribute15,
Pricing_attribute16,
Pricing_attribute17,
Pricing_attribute18,
Pricing_attribute19,
Pricing_attribute20,
Pricing_attribute21,
Pricing_attribute22,
Pricing_attribute23,
Pricing_attribute24,
Pricing_attribute25,
Pricing_attribute26,
Pricing_attribute27,
Pricing_attribute28,
Pricing_attribute29,
Pricing_attribute30,
Pricing_attribute31,
Pricing_attribute32,
Pricing_attribute33,
Pricing_attribute34,
Pricing_attribute35,
Pricing_attribute36,
Pricing_attribute37,
Pricing_attribute38,
Pricing_attribute39,
Pricing_attribute40,
Pricing_attribute41,
Pricing_attribute42,
Pricing_attribute43,
Pricing_attribute44,
Pricing_attribute45,
Pricing_attribute46,
Pricing_attribute47,
Pricing_attribute48,
Pricing_attribute49,
Pricing_attribute50,
Pricing_attribute51,
Pricing_attribute52,
Pricing_attribute53,
Pricing_attribute54,
Pricing_attribute55,
Pricing_attribute56,
Pricing_attribute57,
Pricing_attribute58,
Pricing_attribute59,
Pricing_attribute60,
Pricing_attribute61,
Pricing_attribute62,
Pricing_attribute63,
Pricing_attribute64,
Pricing_attribute65,
Pricing_attribute66,
Pricing_attribute67,
Pricing_attribute68,
Pricing_attribute69,
Pricing_attribute70,
Pricing_attribute71,
Pricing_attribute72,
Pricing_attribute73,
Pricing_attribute74,
Pricing_attribute75,
Pricing_attribute76,
Pricing_attribute77,
Pricing_attribute78,
Pricing_attribute79,
Pricing_attribute80,
Pricing_attribute81,
Pricing_attribute82,
Pricing_attribute83,
Pricing_attribute84,
Pricing_attribute85,
Pricing_attribute86,
Pricing_attribute87,
Pricing_attribute88,
Pricing_attribute89,
Pricing_attribute90,
Pricing_attribute91,
Pricing_attribute92,
Pricing_attribute93,
Pricing_attribute94,
Pricing_attribute95,
Pricing_attribute96,
Pricing_attribute97,
Pricing_attribute98,
Pricing_attribute99,
Pricing_attribute100,
BATCH_ID,
CASCADE_UPDATE,
CONTEXT,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE16,
ATTRIBUTE17,
ATTRIBUTE18,
ATTRIBUTE19,
ATTRIBUTE20,
ATTRIBUTE21,
ATTRIBUTE22,
ATTRIBUTE23,
ATTRIBUTE24,
ATTRIBUTE25,
ATTRIBUTE26,
ATTRIBUTE27,
ATTRIBUTE28,
ATTRIBUTE29,
ATTRIBUTE30
FROM CSI_WEBADI_STAGING
where BATCH_ID = p_entry_id
and INSTANCE_ID is not null;
l_instance instances_for_update_csr%ROWTYPE;
debug('Start procedure: Process_webadi_update');
--UPDATE PROCESSING STARTS
--Retrieving batch description
BEGIN
Select DESCRIPTION
into l_batch_desc
FROM csi_mass_edit_entries_vl
where ENTRY_ID = p_Entry_id;
debug_out('The Mass update batch is not found. So the update records are not processed.');
--Traversing through the cursor and processing for each instance to update
FOR l_instance IN instances_for_update_csr(p_entry_id)
LOOP
--Validations
isRecValid := true;
debug_out('In the uploaded update record #' || xls_idx ||
'. For Employee/Vendor party type, invalid Value(s) or invaid combination of' ||
' values for columns: Owner Party Num, Owner Name.');
debug_out('In the uploaded update record #' || xls_idx ||
'. For Employee/Vendor type, value not entered for column: Owner Party Num.');
debug_out('In the uploaded update record #' || xls_idx ||
'. For Employee/Vendor type, value not entered for column: Owner Name.');
debug_out('In the uploaded update record #' || xls_idx ||
'. Value not entered for column: Owner Party Num.');
debug_out('In the uploaded update record #' || xls_idx ||
'. Invalid value entered for column: Owner Party Num.');
debug_out('In the uploaded update record #' || xls_idx ||
'. Invalid value for column: Owner Account Number');
debug_out('In the uploaded update record #' || xls_idx ||
'. The column, Owner Account Number, cannot be used without a valid' ||
' value in the columns: Owner Party Num, Owner Name.');
debug_out('In the uploaded update record #' || xls_idx ||
'. Value not entered for Mandatory column: Location Type Code.');
debug_out('In the uploaded update record #' || xls_idx ||
'. Value not entered for Mandatory column: Location Id.');
debug_out('In the uploaded update record #' || xls_idx ||
'. For Employee/Vendor party type, invalid Value(s) ' ||
'or invaid combination of values for: ' ||
'Associate Bill To Party Number, Associate Bill To Party Name.');
debug_out('In the uploaded update record #' || xls_idx ||
'. For Employee/Vendor party type, value not entered for' ||
' column: Associate Bill To Party Number.');
debug_out('In the uploaded update record #' || xls_idx ||
'. For Employee/Vendor party type, value not entered for ' ||
'column: Associate Bill To Party Name.');
debug_out('In the uploaded update record #' || xls_idx ||
'. Invalid value entered for column: Associate Bill To Party Number.');
debug_out('In the uploaded update record #' || xls_idx ||
'. Invalid value for column: Associate Bill To Acct Num');
debug_out('In the uploaded update record #' || xls_idx ||
'. The column, Associate Bill To Acct Num, cannot be used without a valid' ||
' value in the columns: Associate Bill To Party Number, Associate Bill To Party Name');
debug_out('In the uploaded update record #' || xls_idx ||
'. For Employee/Vendor party type, Invalid Value(s) or invaid' ||
' combination of values for: ' ||
'Associate Ship To Party Number, Associate Ship To Party Name.');
debug_out('In the uploaded update record #' || xls_idx ||
'. For Employee/Vendor party type, value not entered for' ||
' column: Associate Ship To Party Number.');
debug_out('In the uploaded update record #' || xls_idx ||
'. For Employee/Vendor party type, value not entered for ' ||
'column: Associate Ship To Party Name.');
debug_out('In the uploaded update record #' || xls_idx ||
'. Invalid value entered for column: Associate Ship To Party Number.');
debug_out('In the uploaded update record #' || xls_idx ||
'. Invalid value for column: Associate Ship To Acct Num');
debug_out('In the uploaded update record #' || xls_idx ||
'. The column, Associate Ship To Acct Num, cannot be used without a valid' ||
' value in the columns: Associate Ship To Party Number, Associate Ship To Party Name');
debug_out('In the uploaded update record #' || xls_idx ||
'. Invalid value/combination of values for columns: Associate Contract Number, Associate Contract Modifier');
debug_out('In the uploaded update record #' || xls_idx ||
'. Invalid value for column: Associate Service Line');
debug_out('In the uploaded update record #' || xls_idx ||
'. The column, Associate Service Line, cannot be used without a valid' ||
' value in the columns: Associate Contract Number, Associate Contract Modifier');
debug_out('In the uploaded update record #' || xls_idx ||
'. The column, Associate Service Line should be provided ' ||
' for the contract, specified with Associate Contract Number and Associate Contract Modifier');
debug_out('In the uploaded update record #' || xls_idx ||
'. Invalid value for column: System Name');
SELECT system_id
INTO l_cust_sys_id
FROM
( SELECT sys.system_id
FROM csi_systems_vl sys, HZ_PARTIES hzp, HZ_CUST_ACCOUNTS acct,
CSI_LOOKUPS LK
WHERE sys.CUSTOMER_ID = acct.CUST_ACCOUNT_ID
and acct.PARTY_ID = hzp.PARTY_ID
and acct.account_number = l_instance.OWNER_ACCOUNT_NUMBER
and sys.SYSTEM_TYPE_CODE = LK.LOOKUP_CODE(+)
and LK.LOOKUP_TYPE (+) = 'CSI_SYSTEM_TYPE'
and sys.name = l_instance.SYSTEM_NAME
UNION
SELECT sys.system_id
FROM csi_systems_vl sys, HZ_PARTIES hzp, HZ_CUST_ACCOUNTS acct,
HZ_CUST_ACCT_RELATE_ALL acctall, CSI_LOOKUPS LK
WHERE sys.CUSTOMER_ID = acctall.RELATED_CUST_ACCOUNT_ID
and sys.CUSTOMER_ID = acct.CUST_ACCOUNT_ID
and acct.PARTY_ID = hzp.PARTY_ID
and acctall.CUST_ACCOUNT_ID = acct.CUST_ACCOUNT_ID
and acct.account_number = l_instance.OWNER_ACCOUNT_NUMBER
and sys.SYSTEM_TYPE_CODE = LK.LOOKUP_CODE(+)
and LK.LOOKUP_TYPE (+) = 'CSI_SYSTEM_TYPE'
and sys.name = l_instance.SYSTEM_NAME
);
debug_out('In the uploaded update record #' || xls_idx ||
'. WARNING: The Owner account of the System is neither ' ||
'same nor a related account of the Item Instance Owner.');
Select OBJECT_VERSION_NUMBER, LAST_VLD_ORGANIZATION_ID, LOCATION_TYPE_CODE
into l_object_version_number, l_last_vld_organization_id, l_location_type_code
from csi_item_instances
where instance_id = l_instance.INSTANCE_ID;
debug_out('In the uploaded update record #' || xls_idx ||
' the Instance ' || l_instance.PRODUCT_NUMBER ||
' is not found');
debug('In the uploaded update record #' || xls_idx ||
' the Instance ' || l_instance.PRODUCT_NUMBER ||
' is not found, while retrieving OBJECT_VERSION_NUMBER, LAST_VLD_ORGANIZATION_ID');
SELECT SERIAL_NUMBER_CONTROL_CODE
INTO l_serial_number_control_code
FROM mtl_system_items_b
WHERE INVENTORY_ITEM_ID = l_instance.ITEM_ID
AND ORGANIZATION_ID = l_last_vld_organization_id;
debug_out('In the uploaded update record #' || xls_idx ||
' It is an invalid Item for the Organization chosen.');
Select cip.INSTANCE_PARTY_ID, cip.PARTY_ID, cia.PARTY_ACCOUNT_ID, cia.IP_ACCOUNT_ID,
CIP.OBJECT_VERSION_NUMBER, CIA.OBJECT_VERSION_NUMBER
into l_instance_party_id, l_curr_partyid, l_curr_accountid, l_ip_account_id,
l_party_obj_ver, l_act_obj_ver
from CSI_I_PARTIES cip, csi_ip_accounts cia
where cip.INSTANCE_PARTY_ID = cia.INSTANCE_PARTY_ID
AND cip.RELATIONSHIP_TYPE_CODE = 'OWNER' AND cip.CONTACT_FLAG = 'N'
AND Nvl(cip.ACTIVE_START_DATE, SYSDATE - 1) < SYSDATE
AND Nvl(cip.ACTIVE_END_DATE, SYSDATE + 1) > SYSDATE
AND Nvl(cia.ACTIVE_START_DATE, SYSDATE - 1) < SYSDATE
AND Nvl(cia.ACTIVE_END_DATE, SYSDATE + 1) > SYSDATE
AND cip.INSTANCE_ID = l_instance.INSTANCE_ID;
Select cip.INSTANCE_PARTY_ID, cip.PARTY_ID, CIP.OBJECT_VERSION_NUMBER
into l_instance_party_id, l_curr_partyid, l_party_obj_ver
from CSI_I_PARTIES cip
where cip.RELATIONSHIP_TYPE_CODE = 'OWNER' AND cip.CONTACT_FLAG = 'N'
AND Nvl(cip.ACTIVE_START_DATE, SYSDATE - 1) < SYSDATE
AND Nvl(cip.ACTIVE_END_DATE, SYSDATE + 1) > SYSDATE
AND cip.INSTANCE_ID = l_instance.INSTANCE_ID;
debug_out('In the uploaded update record #' || xls_idx ||
' for instance ' || l_instance.PRODUCT_NUMBER ||
' current Owner details are not found.');
SELECT instance_id INTO l_par_instance_id
FROM csi_item_instances
WHERE instance_number = l_instance.PARENT_INSTANCE;
debug_out('In the uploaded update record #' || xls_idx ||
'. Invalid value for column: Parent Instance');
SELECT instance_id INTO l_old_par_instance_id
FROM csi_item_instances
WHERE instance_number = l_instance.OLD_PARENT_INSTANCE;
debug_out('In the uploaded update record #' || xls_idx ||
'. Invalid value for hidden column: Old Parent Instance.');
debug_out('In the uploaded update record #' || xls_idx ||
'. Update of Inventory instance is not allowed.');
SELECT operational_status_code INTO l_operational_status_code
FROM csi_item_instances
WHERE instance_id = l_instance.INSTANCE_ID;
debug_out('In the uploaded update record #' || xls_idx ||
'. Invalid value for hidden column: Instance Id.');
debug_out('In the uploaded update record #' || xls_idx ||
'. Change of value is not supported for the column: Operational Status Code');
debug_out('The uploaded update record #' || xls_idx ||
' is not processed due to the failure of the above mentioned validation(s).');
' for Update. Instance Number: ' || l_instance.PRODUCT_NUMBER);
u_instance_tbl(inst_idx).CASCADE_OWNERSHIP_FLAG := l_instance.CASCADE_UPDATE;
SELECT cil1.VERSION_LABEL
INTO l_old_version_label
FROM csi_i_version_labels cil1
WHERE cil1.instance_id = l_instance.INSTANCE_ID
AND nvl(cil1.DATE_TIME_STAMP, sysdate) = nvl ( (SELECT max(cil2.DATE_TIME_STAMP)
FROM csi_i_version_labels cil2
WHERE cil2.instance_id = cil1.instance_id ) , sysdate );
--Checking for serial control code, to allow quantity update only for non serial
IF(l_serial_number_control_code = 1) THEN
u_instance_tbl(inst_idx).QUANTITY := l_instance.QUANTITY;
SELECT pricing_attribute_id, object_version_number
INTO l_pricing_attribute_id, l_pa_obj_version_num
FROM csi_i_pricing_attribs
WHERE Nvl(active_end_date, SYSDATE + 1) > SYSDATE
AND instance_id = l_instance.instance_id;
debug_out('Could not create/update Pricing attributes for Instance: ' ||
l_instance.PRODUCT_NUMBER ||
'. Please check the request log, for further error details');
SELECT INTERNAL_PARTY_ID
INTO l_internal_party_id
FROM csi_install_parameters;
u_party_tbl(party_idx).CASCADE_OWNERSHIP_FLAG := l_instance.CASCADE_UPDATE;
u_account_tbl(account_idx).CASCADE_OWNERSHIP_FLAG := l_instance.CASCADE_UPDATE;
Select 'X'
into l_dummy_var
from CSI_I_PARTIES cip
where cip.RELATIONSHIP_TYPE_CODE = 'BILL_TO' AND cip.CONTACT_FLAG = 'N'
AND Nvl(cip.ACTIVE_END_DATE, SYSDATE + 1) > SYSDATE
AND cip.INSTANCE_ID = l_instance.INSTANCE_ID
AND cip.PARTY_ID = l_instance.ASSOCIATE_BILL_TO_PARTY_ID;
Select 'X'
into l_dummy_var
from CSI_I_PARTIES cip
where cip.RELATIONSHIP_TYPE_CODE = 'SHIP_TO' AND cip.CONTACT_FLAG = 'N'
AND Nvl(cip.ACTIVE_END_DATE, SYSDATE + 1) > SYSDATE
AND cip.INSTANCE_ID = l_instance.INSTANCE_ID
AND cip.PARTY_ID = l_instance.ASSOCIATE_SHIP_TO_PARTY_ID;
SELECT RELATIONSHIP_ID, OBJECT_VERSION_NUMBER
INTO l_relationship_id, l_rel_obj_ver
FROM csi_ii_relationships
WHERE object_id = l_old_par_instance_id
AND subject_id = l_instance.INSTANCE_ID
AND nvl(ACTIVE_END_DATE, sysdate + 1) > sysdate;
--Call to Update API
debug('Number of records populated to call Update API: ' || u_instance_tbl.count);
csi_item_instance_grp.update_item_instance (
p_api_version => 1.0,
p_commit => fnd_api.g_false,
p_init_msg_list => fnd_api.g_true,
p_validation_level => fnd_api.g_valid_level_full,
p_instance_tbl => u_instance_tbl,
p_ext_attrib_values_tbl => u_ext_attrib_values_tbl,
p_party_tbl => u_party_tbl,
p_account_tbl => u_account_tbl,
p_pricing_attrib_tbl => u_pricing_attrib_tbl,
p_org_assignments_tbl => u_org_assignments_tbl,
p_asset_assignment_tbl => u_instance_asset_tbl,
p_txn_rec => p_csi_txn_rec,
x_instance_id_lst => l_instance_id_lst,
p_grp_upd_error_tbl => u_grp_upd_error_tbl,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
debug('Call to update_item_instance not successful');
debug_out('Update of the instances not successful, please check the request log for further details.');
--Creating notes for all the updated instances
IF(l_batch_desc IS NOT NULL) THEN
IF(u_instance_tbl.count >0) THEN
FOR inst_tab_row IN u_instance_tbl.FIRST .. u_instance_tbl.LAST
LOOP
IF u_instance_tbl.EXISTS(inst_tab_row) THEN
BEGIN
SELECT instance_number
INTO l_instance_number
FROM csi_item_instances
WHERE instance_id = u_instance_tbl(inst_tab_row).Instance_id; EXCEPTION
debug('Calling JTF_NOTES_PUB.CREATE_NOTE for update record Instance: '
|| l_instance_number);
p_last_update_date => SYSDATE,
p_last_updated_by => FND_GLOBAL.USER_ID,
p_creation_date => SYSDATE,
p_created_by => FND_GLOBAL.USER_ID
);
'update record Instance: ' || l_instance_number);
'update record Instance: ' || l_instance_number);
SELECT instance_number
INTO l_instance_number
FROM csi_item_instances
WHERE instance_id = u_grp_upd_error_tbl(inst_tab_row).Instance_id;
debug_out('Update of Instance ' || l_instance_number || ' : Failed with the error ' ||
u_grp_upd_error_tbl(inst_tab_row).Entity_Name || '- ' || u_grp_upd_error_tbl(inst_tab_row).Error_Message );
debug('Update of Instance ' || l_instance_number || ' : Failed with the error ' ||
u_grp_upd_error_tbl(inst_tab_row).Entity_Name || '- ' || u_grp_upd_error_tbl(inst_tab_row).Error_Message );
SELECT Count(*) INTO l_updt_count
FROM CSI_WEBADI_STAGING
where BATCH_ID = p_entry_id
and INSTANCE_ID is NOT null;
debug_out('Total no. of Item Instances Updated successfully: ' || (l_updt_count - l_falied_count));
debug_out('Total no. of update records failed: ' || l_falied_count);
debug('Total no. of Item Instances Updated successfully: ' || (l_updt_count - l_falied_count));
debug('Total no. of update records failed: ' || l_falied_count);
--Split Instance code, actually have to be done only if update for the instance is a success
--split_idx := 0;
--UPDATE PROCESSING STARTS
--Traversing through the cursor and processing for each instance to update
debug('Number of records populated to call Split Instance API: ' || l_instance_split_tbl.count);
Select QUANTITY, SPLIT_QTY
INTO l_quantity, l_splitqty
from CSI_WEBADI_STAGING
where BATCH_ID = p_Entry_id
and INSTANCE_ID = l_instance_split_tbl(inst_tab_row).instance_id;
SELECT instance_number
INTO l_instance_number
FROM csi_item_instances
WHERE instance_id = c_pricing_attribs_tbl(tab_row).instance_id; EXCEPTION
debug('Number of records populated to call update Pricing attributes API: ' ||
u_pricing_attribs_tbl.count);
csi_pricing_attribs_pvt.update_pricing_attribs(
p_api_version => 1.0,
p_commit => fnd_api.g_false,
p_init_msg_list => fnd_api.g_true,
p_validation_level => fnd_api.g_valid_level_full,
p_pricing_attribs_rec => u_pricing_attribs_tbl(tab_row),
p_txn_rec => p_csi_txn_rec,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
SELECT instance_number
INTO l_instance_number
FROM csi_item_instances
WHERE instance_id = u_pricing_attribs_tbl(tab_row).instance_id; EXCEPTION
debug('Instance not found, while debug for update_pricing_attribs' ||
' for ID: ' || u_pricing_attribs_tbl(tab_row).instance_id);
debug('Error Occured in call to update_pricing_attribs for the Instance: ' ||
l_instance_number);
debug('End procedure: Process_webadi_update');
debug_out('Encountered unexpected error while update. Please check the request log for error details');
debug('Encountered unexpected exception in Process_webadi_update');
END Process_webadi_update;
SELECT name,
description
INTO l_old_batch_name,
l_old_batch_desc
FROM csi_mass_edit_entries_vl
WHERE entry_id = p_entry_id;
select CSI_MU_REUPLOAD_S.NEXTVAL INTO l_new_batch_seq from DUAL;
UPDATE CSI_WEBADI_STAGING
SET BATCH_ID = p_reupld_batch_id,
BATCH_NAME = l_mass_edit_rec.name
WHERE nvl(PROCESSING_STATUS, 'N') <> 'P'
AND BATCH_ID = p_entry_id;
UPDATE csi_webadi_staging cws SET LAST_VLD_ORGANIZATION_ID =
(
SELECT O.ORGANIZATION_ID
FROM HR_ALL_ORGANIZATION_UNITS O, HR_ALL_ORGANIZATION_UNITS_TL OTL
WHERE O.ORGANIZATION_ID = OTL.ORGANIZATION_ID
AND OTL.LANGUAGE = userenv('LANG')
AND OTL.NAME = cws.ORGANIZATION
)
WHERE cws.batch_id = p_Entry_id;
UPDATE csi_webadi_staging cws SET INSTANCE_ID =
(
SELECT INSTANCE_ID FROM csi_item_instances cii
WHERE cii.instance_number = cws.PRODUCT_NUMBER
)
WHERE cws.batch_id = p_Entry_id;
UPDATE csi_webadi_staging cws SET ITEM_ID =
(
SELECT m2.inventory_item_id
FROM mtl_system_items_kfv m2,
mtl_system_items m1,
fnd_lookup_values fcl,
mtl_parameters m3
WHERE m1.COMMS_NL_TRACKABLE_FLAG = 'Y'
and sysdate between nvl(m1.START_DATE_ACTIVE,sysdate)
and nvl(m1.END_DATE_ACTIVE ,sysdate)
and m2.inventory_item_id = m1.inventory_item_id
and fcl.lookup_type(+) = 'ITEM_TYPE'
and fcl.lookup_code(+) = m2.item_type
and sysdate between nvl(m2.START_DATE_ACTIVE,sysdate)
and nvl(m2.END_DATE_ACTIVE ,sysdate)
and fcl.LANGUAGE(+) = userenv('LANG')
and m3.organization_id = m2.organization_id
and m3.organization_id = m1.organization_id
and m2.inventory_item_status_code not in ('Inactive')
AND m2.concatenated_segments = cws.ITEM_NUMBER
AND m2.organization_id = cws.LAST_VLD_ORGANIZATION_ID
)
WHERE cws.batch_id = p_Entry_id;
UPDATE csi_webadi_staging cws SET OWNER_ID =
(
decode(cws.OWNER_PARTY_TYPE,
'HZ_PARTIES',
(SELECT party_id FROM hz_parties WHERE party_number = cws.OWNER_PARTY_NUM),
'EMPLOYEE',
(SELECT employee_id FROM per_employees_current_x
WHERE employee_num = cws.OWNER_PARTY_NUM
AND full_name = cws.OWNER_NAME),
'PO_VENDORS',
(SELECT vendor_id FROM po_vendors WHERE segment1 = cws.OWNER_PARTY_NUM
AND vendor_name = cws.OWNER_NAME
AND nvl(start_date_active,sysdate) <= sysdate AND nvl(end_date_Active, sysdate+1) > SYSDATE
AND enabled_flag = 'Y'),
null)
)
WHERE cws.batch_id = p_Entry_id;
UPDATE csi_webadi_staging cws SET ASSOCIATE_BILL_TO_PARTY_ID =
(
decode(cws.ASSOCIATE_BILL_TO_PARTY_TYPE,
'HZ_PARTIES',
(SELECT party_id FROM hz_parties
WHERE party_number = cws.ASSOCIATE_BILL_TO_PARTY_NUMBER),
'EMPLOYEE',
(SELECT employee_id FROM per_employees_current_x
WHERE employee_num = cws.ASSOCIATE_BILL_TO_PARTY_NUMBER
AND full_name = cws.ASSOCIATE_BILL_TO_PARTY_NAME),
'PO_VENDORS',
(SELECT vendor_id FROM po_vendors
WHERE segment1 = cws.ASSOCIATE_BILL_TO_PARTY_NUMBER
AND vendor_name = cws.ASSOCIATE_BILL_TO_PARTY_NAME
AND nvl(start_date_active,sysdate) <= sysdate AND nvl(end_date_Active, sysdate+1) > SYSDATE
AND enabled_flag = 'Y'),
null)
)
WHERE cws.batch_id = p_Entry_id;
UPDATE csi_webadi_staging cws SET ASSOCIATE_SHIP_TO_PARTY_ID =
(
decode(cws.ASSOCIATE_SHIP_TO_PARTY_TYPE,
'HZ_PARTIES',
(SELECT party_id FROM hz_parties
WHERE party_number = cws.ASSOCIATE_SHIP_TO_PARTY_NUMBER),
'EMPLOYEE',
(SELECT employee_id FROM per_employees_current_x
WHERE employee_num = cws.ASSOCIATE_SHIP_TO_PARTY_NUMBER
AND full_name = cws.ASSOCIATE_SHIP_TO_PARTY_NAME),
'PO_VENDORS',
(SELECT vendor_id FROM po_vendors
WHERE segment1 = cws.ASSOCIATE_SHIP_TO_PARTY_NUMBER
AND vendor_name = cws.ASSOCIATE_SHIP_TO_PARTY_NAME
AND nvl(start_date_active,sysdate) <= sysdate AND nvl(end_date_Active, sysdate+1) > SYSDATE
AND enabled_flag = 'Y'),
null)
)
WHERE cws.batch_id = p_Entry_id;
UPDATE csi_webadi_staging cws SET OWNER_ACCOUNT_ID =
(
SELECT CUST_ACCOUNT_ID FROM hz_cust_accounts
WHERE status = 'A'
AND sysdate between nvl(ACCOUNT_ACTIVATION_DATE,sysdate) and nvl(ACCOUNT_TERMINATION_DATE ,sysdate)
AND PARTY_ID = cws.OWNER_ID
AND ACCOUNT_NUMBER = cws.OWNER_ACCOUNT_NUMBER
)
WHERE cws.batch_id = p_Entry_id;
UPDATE csi_webadi_staging cws SET ASSOCIATE_BILL_TO_ACCT_ID =
(
SELECT CUST_ACCOUNT_ID FROM hz_cust_accounts
WHERE status = 'A'
AND sysdate between nvl(ACCOUNT_ACTIVATION_DATE,sysdate) and nvl(ACCOUNT_TERMINATION_DATE ,sysdate)
AND PARTY_ID = cws.ASSOCIATE_BILL_TO_PARTY_ID
AND ACCOUNT_NUMBER = cws.ASSOCIATE_BILL_TO_ACCT_NUM
)
WHERE cws.batch_id = p_Entry_id;
UPDATE csi_webadi_staging cws SET ASSOCIATE_SHIP_TO_ACCT_ID =
(
SELECT CUST_ACCOUNT_ID FROM hz_cust_accounts
WHERE status = 'A'
AND sysdate between nvl(ACCOUNT_ACTIVATION_DATE,sysdate) and nvl(ACCOUNT_TERMINATION_DATE ,sysdate)
AND PARTY_ID = cws.ASSOCIATE_SHIP_TO_PARTY_ID
AND ACCOUNT_NUMBER = cws.ASSOCIATE_SHIP_TO_ACCT_NUM
)
WHERE cws.batch_id = p_Entry_id;
UPDATE csi_webadi_staging cws SET ASSOCIATE_CONTRACT_ID =
(
SELECT okch.id ContractId
FROM okc_k_headers_all_b okch, okc_statuses_b sts, okc_k_party_roles_b k_party
WHERE okch.sts_code = sts.code
AND okch.scs_code IN ('SERVICE','WARRANTY') AND sts.ste_code = 'ENTERED'
AND okch.id = k_party.chr_id AND k_party.jtot_object1_code IN ('OKX_PARTY')
AND contract_number = cws.ASSOCIATE_CONTRACT_NUMBER
AND Nvl(contract_number_modifier, fnd_api.g_miss_char)
= Nvl(cws.ASSOCIATE_CONTRACT_MODIFIER, fnd_api.g_miss_char)
AND EXISTS (SELECT 1 FROM HZ_CUST_ACCOUNTS CA1
WHERE CA1.party_id = k_party.object1_id1 AND CA1.status = 'A'
AND CA1.account_number = cws.OWNER_ACCOUNT_NUMBER
UNION ALL
SELECT 1 FROM HZ_CUST_ACCOUNTS CA2, HZ_CUST_ACCT_RELATE_ALL REL
WHERE CA2.party_id = k_party.object1_id1
AND REL.cust_account_id = CA2.CUST_ACCOUNT_ID
AND REL.org_id = okch.AUTHORING_ORG_ID AND REL.status = 'A'
AND CA2.status = 'A'
AND REL.related_cust_account_id = CA2.CUST_ACCOUNT_ID
and CA2.account_number = cws.OWNER_ACCOUNT_NUMBER)
)
WHERE cws.batch_id = p_Entry_id;
UPDATE csi_webadi_staging cws SET ASSOCIATE_SERVICE_LINE_ID =
(
SELECT okl.id
FROM okc_k_lines_b okl, okc_k_items oki,
mtl_system_items_kfv mtl, okc_k_headers_all_b okh
WHERE oki.cle_id = okl.id
AND mtl.inventory_item_id = oki.object1_id1
AND mtl.organization_id = oki.object1_id2
AND oki.jtot_object1_code = 'OKX_SERVICE'
AND okl.chr_id = okh.id AND okl.lse_id IN (1,19)
AND okh.contract_number= cws.ASSOCIATE_CONTRACT_NUMBER
AND nvl(okh.contract_number_modifier, fnd_api.g_miss_char)
= nvl(cws.ASSOCIATE_CONTRACT_MODIFIER, fnd_api.g_miss_char)
AND okl.line_number = cws.ASSOCIATE_SERVICE_LINE
)
WHERE cws.batch_id = p_Entry_id;
UPDATE csi_webadi_staging cws SET OLD_PARENT_INSTANCE =
(
select cii.instance_number
from csi_ii_relationships cir, csi_item_instances cii
where cir.subject_id = cws.INSTANCE_ID
and cir.object_id = cii.instance_id
and Nvl(cir.ACTIVE_END_DATE, SYSDATE + 1) > sysdate
)
WHERE cws.batch_id = p_Entry_id;
UPDATE csi_webadi_staging cws SET SYSTEM_ID =
(
select s.SYSTEM_ID
FROM CSI_LOOKUPS LK, csi_systems_v s
WHERE s.SYSTEM_TYPE_CODE = LK.LOOKUP_CODE(+)
AND LK.LOOKUP_TYPE (+) = 'CSI_SYSTEM_TYPE'
AND s.NAME = cws.SYSTEM_NAME
)
WHERE cws.batch_id = p_Entry_id;