[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_veto_reason VARCHAR2(255) := 'During Account Merge, old Account Id exists in Installed Base History tables. This table stores the history of all the accounts for an item instance and hence cannot be deleted.';
SELECT DISTINCT
m.CUSTOMER_MERGE_HEADER_ID ,
yt.IP_ACCOUNT_ID ,
yt.PARTY_ACCOUNT_ID ,
yt.BILL_TO_ADDRESS ,
yt.SHIP_TO_ADDRESS ,
cip.INSTANCE_PARTY_ID ,
cip.INSTANCE_ID
FROM CSI_IP_ACCOUNTS yt,
CSI_I_PARTIES cip,
CSI_ITEM_INSTANCES cii,
RA_CUSTOMER_MERGES m
WHERE ( yt.PARTY_ACCOUNT_ID = m.DUPLICATE_ID OR
yt.BILL_TO_ADDRESS = m.DUPLICATE_SITE_ID OR
yt.SHIP_TO_ADDRESS = m.DUPLICATE_SITE_ID )
AND yt.instance_party_id = cip.instance_party_id
AND ( yt.active_end_date is null OR
yt.active_end_date > sysdate )
AND cip.instance_id = cii.instance_id
AND ( cii.active_end_date is null OR
cii.active_end_date > sysdate )
-- TODO : the next line needs to be uncommented
AND m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num;
l_owner_update VARCHAR2(1);
vetoed_list.delete;
SELECT CUSTOMER_SITE_ID INTO l_install_customer_site_id
FROM RA_CUSTOMER_MERGES
WHERE CUSTOMER_MERGE_HEADER_ID=MERGE_HEADER_ID_LIST(I)
AND CUSTOMER_SITE_CODE='INSTALL_AT' ;
SELECT INSTALL_LOCATION_TYPE_CODE,INSTALL_LOCATION_ID,LOCATION_TYPE_CODE,LOCATION_ID
INTO l_install_location_type_code,NUM_COL5_ORIG_LIST(I),l_location_type_code,NUM_COL4_ORIG_LIST(I)
FROM CSI_ITEM_INSTANCES WHERE INSTANCE_ID=INSTANCE_ID_LIST(I);
SELECT CUST_ACCT_SITE_ID
INTO NUM_COL6_NEW_LIST(I) FROM hz_cust_site_uses_all
WHERE SITE_USE_ID= INSTALL_CUSTOMER_SITE_ID_LIST(I);
SELECT PARTY_SITE_ID INTO NUM_COL5_NEW_LIST(I)
FROM HZ_CUST_ACCT_SITES_ALL
WHERE CUST_ACCT_SITE_ID= NUM_COL6_NEW_LIST(I);
SELECT LOCATION_ID INTO NUM_COL4_NEW_LIST(I)
FROM HZ_PARTY_SITES
WHERE PARTY_SITE_ID = NUM_COL5_NEW_LIST(I);
SELECT CUST_ACCT_SITE_ID
INTO NUM_COL6_NEW_LIST(I) FROM hz_cust_site_uses_all
WHERE SITE_USE_ID= INSTALL_CUSTOMER_SITE_ID_LIST(I);
/* SELECT PARTY_ID
INTO l_party_id FROM HZ_CUST_ACCOUNTS
WHERE CUST_ACCOUNT_ID= NUM_COL1_ORIG_LIST(I);
SELECT PARTY_SITE_ID INTO l_party_site_id
FROM HZ_PARTY_SITES
WHERE PARTY_ID=l_party_id AND location_id=NUM_COL5_ORIG_LIST(I);
SELECT CUST_ACCT_SITE_ID
INTO NUM_COL6_ORIG_LIST(I) FROM HZ_CUST_ACCT_SITES_ALL
WHERE PARTY_SITE_ID= l_party_site_id
AND CUST_ACCOUNT_ID= NUM_COL1_ORIG_LIST(I);
SELECT PARTY_SITE_ID INTO l_party_site_id
FROM HZ_CUST_ACCT_SITES_ALL
WHERE CUST_ACCT_SITE_ID= NUM_COL6_NEW_LIST(I);
SELECT LOCATION_ID INTO NUM_COL5_NEW_LIST(I)
FROM HZ_PARTY_SITES
WHERE PARTY_SITE_ID = l_party_site_id;
SELECT LOCATION_ID,LOCATION_ID
INTO NUM_COL5_NEW_LIST(I), NUM_COL4_NEW_LIST(I)
FROM HZ_PARTY_SITES
WHERE PARTY_SITE_ID = l_party_site_id;
INSERT INTO HZ_CUSTOMER_MERGE_LOG
(
MERGE_LOG_ID ,
TABLE_NAME ,
MERGE_HEADER_ID ,
PRIMARY_KEY_ID ,
NUM_COL1_ORIG ,
NUM_COL1_NEW ,
NUM_COL2_ORIG ,
NUM_COL2_NEW ,
NUM_COL3_ORIG ,
NUM_COL3_NEW ,
ACTION_FLAG ,
REQUEST_ID ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATE_LOGIN ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY
)
VALUES
(
HZ_CUSTOMER_MERGE_LOG_s.nextval ,
'CSI_IP_ACCOUNTS' ,
MERGE_HEADER_ID_LIST(I) ,
PRIMARY_KEY_ID_LIST(I) ,
NUM_COL1_ORIG_LIST(I) ,
NUM_COL1_NEW_LIST(I) ,
NUM_COL2_ORIG_LIST(I ),
NUM_COL2_NEW_LIST(I) ,
NUM_COL3_ORIG_LIST(I) ,
NUM_COL3_NEW_LIST(I) ,
'U' ,
req_id ,
hz_utility_pub.CREATED_BY ,
hz_utility_pub.CREATION_DATE ,
hz_utility_pub.LAST_UPDATE_LOGIN ,
hz_utility_pub.LAST_UPDATE_DATE ,
hz_utility_pub.LAST_UPDATED_BY
);
SELECT transaction_id
INTO v_transaction_id
FROM csi_transactions
WHERE source_line_ref_id = req_id
AND source_line_ref = set_num;
SELECT transaction_type_id
INTO v_transaction_type_id
FROM csi_txn_types
WHERE source_transaction_type = v_source_transaction_type;
SELECT party_account_id, bill_to_address, ship_to_address
INTO l_party_account_id, l_bill_to_address, l_ship_to_address
FROM csi_ip_accounts
WHERE ip_account_id = PRIMARY_KEY_ID_LIST(I);
p_u_ext_attrib_values_tbl.delete;
p_u_party_tbl.delete;
p_u_party_account_tbl.delete;
p_u_pricing_attrib_tbl.delete;
p_u_org_assignments_tbl.delete;
p_u_asset_assignment_tbl.delete;
SELECT party_id
INTO l_to_party_id
FROM hz_cust_accounts
WHERE cust_account_id = NUM_COL1_NEW_LIST(I);
l_owner_update := 'N';
l_owner_update := 'Y';
l_owner_update ;
IF l_owner_update = 'Y' THEN -- { l_owner_update is 'Y'
IF l_pty_with_diff_accts = 'Y' THEN -- { l_pty_with_diff_accts 1
-- move owner party record with the l_to_party_id
-- create a new party record with previous party_id
-- loop
-- if account is to be changed
-- set the instance_party_id from the l_g_ph record
-- else
-- set the instance_party_id to the newly created one
-- end if
-- move the account record
-- end loop
-- move the party record with l_to_party_id
error_str := '016';
l_g_pah_tbl(K).ship_to_address = NUM_COL3_ORIG_LIST(I) ) THEN -- { if the record needs update
p_u_party_account_tbl(l_acct_idx).parent_tbl_index := l_pty_idx-1;
ELSE -- } if record needs update { if record needs no update
p_u_party_account_tbl(l_acct_idx).parent_tbl_index := l_pty_idx;
END IF; -- } if the record needs no update
ELSE -- } l_owner_update = 'Y' { l_owner_update = 'N'
--
-- if l_pty_with_diff_accts then
-- move pty record as is
-- create new party record with l_to_party
-- loop
-- if account is to be updated
-- use the new party record index
-- else
-- use the old party record index
-- end if
-- move acct record
-- end loop
-- else
-- move pty record with new l_to_party
-- loop
-- move acct records with updated account_ids
-- end loop
-- end if;
l_g_pah_tbl(K).ship_to_address = NUM_COL3_ORIG_LIST(I) ) THEN -- { if the record needs update
p_u_party_account_tbl(l_acct_idx).parent_tbl_index := l_pty_idx;
ELSE -- } if record needs update { if record needs no update
p_u_party_account_tbl(l_acct_idx).parent_tbl_index := l_pty_idx-1;
END IF; -- } if the record needs no update
END IF; -- } l_owner_update = 'N'
write_to_cr_log( 'CSI:Party and Account Records for update call : ' ||
to_char(p_u_party_tbl.count) || ' : ' ||
to_char(p_u_party_account_tbl.count) );
csi_item_instance_pub.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_rec => p_u_instance_rec,
p_ext_attrib_values_tbl => p_u_ext_attrib_values_tbl,
p_party_tbl => p_u_party_tbl,
p_account_tbl => p_u_party_account_tbl,
p_pricing_attrib_tbl => p_u_pricing_attrib_tbl,
p_org_assignments_tbl => p_u_org_assignments_tbl,
p_asset_assignment_tbl => p_u_asset_assignment_tbl,
p_txn_rec => p_txn_rec,
x_instance_id_lst => x_instance_id_lst,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
/* ***commented veto delete for bug 5897064**
--
-- Veto the delete as the history information still exists.
--
l_from_customer_id := num_col1_orig_list(I);
ARP_CMERGE_MASTER.veto_delete
(
req_id => req_id,
set_num => set_num,
from_customer_id => l_from_customer_id,
veto_reason => l_veto_reason
);
END IF; -- } Party account id retrieved is not already updated
arp_message.set_name('CSI','CSI_ROWS_UPDATED');
arp_message.set_line( 'Done with the update of CSI_IP_ACCOUNTS' );
SELECT distinct
CUSTOMER_MERGE_HEADER_ID ,
yt.SYSTEM_ID ,
yt.CUSTOMER_ID ,
yt.BILL_TO_SITE_USE_ID ,
yt.SHIP_TO_SITE_USE_ID
FROM CSI_SYSTEMS_B yt,
RA_CUSTOMER_MERGES m
WHERE ( yt.CUSTOMER_ID = m.DUPLICATE_ID OR
yt.BILL_TO_SITE_USE_ID = m.DUPLICATE_SITE_ID OR
yt.SHIP_TO_SITE_USE_ID = m.DUPLICATE_SITE_ID )
AND m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num;
INSERT INTO HZ_CUSTOMER_MERGE_LOG
(
MERGE_LOG_ID ,
TABLE_NAME ,
MERGE_HEADER_ID ,
PRIMARY_KEY_ID ,
NUM_COL1_ORIG ,
NUM_COL1_NEW ,
NUM_COL2_ORIG ,
NUM_COL2_NEW ,
NUM_COL3_ORIG ,
NUM_COL3_NEW ,
ACTION_FLAG ,
REQUEST_ID ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATE_LOGIN ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY
)
VALUES
(
HZ_CUSTOMER_MERGE_LOG_s.nextval ,
'CSI_SYSTEMS_B' ,
MERGE_HEADER_ID_LIST(I) ,
PRIMARY_KEY_ID_LIST(I) ,
NUM_COL1_ORIG_LIST(I) ,
NUM_COL1_NEW_LIST(I) ,
NUM_COL2_ORIG_LIST(I ),
NUM_COL2_NEW_LIST(I) ,
NUM_COL3_ORIG_LIST(I) ,
NUM_COL3_NEW_LIST(I) ,
'U' ,
req_id ,
hz_utility_pub.CREATED_BY ,
hz_utility_pub.CREATION_DATE ,
hz_utility_pub.LAST_UPDATE_LOGIN ,
hz_utility_pub.LAST_UPDATE_DATE ,
hz_utility_pub.LAST_UPDATED_BY
);
write_to_cr_log( 'CSI : Before Update Loop check ');
UPDATE CSI_SYSTEMS_B yt
SET CUSTOMER_ID = NUM_COL1_NEW_LIST(I) ,
BILL_TO_SITE_USE_ID = NUM_COL2_NEW_LIST(I) ,
SHIP_TO_SITE_USE_ID = NUM_COL3_NEW_LIST(I) ,
LAST_UPDATE_DATE = SYSDATE ,
last_updated_by = arp_standard.profile.user_id ,
last_update_login = arp_standard.profile.last_update_login ,
REQUEST_ID = req_id ,
PROGRAM_APPLICATION_ID = arp_standard.profile.program_application_id ,
PROGRAM_ID = arp_standard.profile.program_id ,
PROGRAM_UPDATE_DATE = SYSDATE
WHERE SYSTEM_ID = PRIMARY_KEY_ID_LIST(I);
If transaction_inserted_flag = 'N' then
1.1 if a transaction does not exist
create a transaction
1.2 set the transaction_inserted_flag = 'Y'
------*/
SELECT count(*)
INTO v_transaction_cnt
FROM csi_transactions
WHERE source_line_ref_id = req_id
AND source_line_ref = set_num;
BEGIN -- { select csi_transaction
error_str := '005';
SELECT transaction_id
INTO v_transaction_id
FROM csi_transactions
WHERE source_line_ref_id = req_id
AND source_line_ref = set_num;
BEGIN -- { insert csi_transaction
error_str := '006';
SELECT count(*)
INTO v_transaction_cnt
FROM csi_txn_types
WHERE source_transaction_type = v_source_transaction_type;
SELECT transaction_type_id
INTO v_transaction_type_id
FROM csi_txn_types
WHERE source_transaction_type = v_source_transaction_type;
SELECT csi_transactions_s.nextval
INTO v_transaction_id
FROM dual;
INSERT INTO csi_transactions
(
transaction_id ,
transaction_date ,
source_transaction_date ,
transaction_type_id ,
source_line_ref_id ,
source_line_ref ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date ,
last_update_login ,
object_version_number
)
VALUES
(
v_transaction_id ,
sysdate ,
sysdate ,
v_transaction_type_id ,
req_id ,
set_num ,
arp_standard.profile.user_id ,
sysdate ,
arp_standard.profile.user_id ,
sysdate ,
arp_standard.profile.last_update_login ,
1
);
END; -- } end insert csi_transaction
END; -- } end select csi_transaction
BEGIN -- { Update systems history
SELECT count(*)
INTO v_transaction_cnt
FROM csi_systems_h
WHERE transaction_id = v_transaction_id
AND system_id = PRIMARY_KEY_ID_LIST(I);
SELECT system_history_id
INTO v_system_history_id
FROM csi_systems_h
WHERE transaction_id = v_transaction_id
AND system_id = PRIMARY_KEY_ID_LIST(I);
UPDATE csi_systems_h
SET old_customer_id = NUM_COL1_ORIG_LIST(I) ,
new_customer_id = NUM_COL1_NEW_LIST(I) ,
old_bill_to_site_use_id = NUM_COL2_ORIG_LIST(I) ,
new_bill_to_site_use_id = NUM_COL2_NEW_LIST(I) ,
old_ship_to_site_use_id = NUM_COL3_ORIG_LIST(I) ,
new_ship_to_site_use_id = NUM_COL3_NEW_LIST(I) ,
object_version_number = object_version_number + 1
WHERE system_history_id = v_system_history_id ;
write_to_cr_log( 'CSI : updated system history record ' );
BEGIN --{ Insert systems history
write_to_cr_log( 'CSI : No system history record found ' );
INSERT INTO csi_systems_h
(
system_history_id ,
system_id ,
transaction_id ,
old_customer_id ,
new_customer_id ,
old_bill_to_site_use_id ,
new_bill_to_site_use_id ,
old_ship_to_site_use_id ,
new_ship_to_site_use_id ,
full_dump_flag ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date ,
last_update_login ,
object_version_number
)
VALUES
(
csi_systems_h_s.nextval ,
PRIMARY_KEY_ID_LIST(I) ,
v_transaction_id ,
NUM_COL1_ORIG_LIST(I) ,
NUM_COL1_NEW_LIST(I) ,
NUM_COL2_ORIG_LIST(I) ,
NUM_COL2_NEW_LIST(I) ,
NUM_COL3_ORIG_LIST(I) ,
NUM_COL3_NEW_LIST(I) ,
'N',
arp_standard.profile.user_id ,
sysdate ,
arp_standard.profile.user_id ,
sysdate ,
arp_standard.profile.last_update_login ,
1
);
write_to_cr_log( 'CSI : inserted system history record ' );
END; -- } Insert systems history
END; -- } Update Systems history
arp_message.set_line( 'Done with the insert of systems history' );
SELECT distinct CUSTOMER_MERGE_HEADER_ID ,
TXN_ACCOUNT_DETAIL_ID ,
ACCOUNT_ID ,
BILL_TO_ADDRESS_ID ,
SHIP_TO_ADDRESS_ID
FROM CSI_T_PARTY_ACCOUNTS yt,
RA_CUSTOMER_MERGES m
WHERE ( yt.ACCOUNT_ID = m.DUPLICATE_ID OR
yt.BILL_TO_ADDRESS_ID = m.DUPLICATE_SITE_ID OR
yt.SHIP_TO_ADDRESS_ID = m.DUPLICATE_SITE_ID )
AND m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num;
write_to_cr_log( 'Starting to update the csi_t_party_accounts' );
INSERT INTO HZ_CUSTOMER_MERGE_LOG
(
MERGE_LOG_ID ,
TABLE_NAME ,
MERGE_HEADER_ID ,
PRIMARY_KEY_ID ,
NUM_COL1_ORIG ,
NUM_COL1_NEW ,
NUM_COL2_ORIG ,
NUM_COL2_NEW ,
NUM_COL3_ORIG ,
NUM_COL3_NEW ,
ACTION_FLAG ,
REQUEST_ID ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATE_LOGIN ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY
)
VALUES
(
HZ_CUSTOMER_MERGE_LOG_s.nextval ,
'CSI_T_PARTY_ACCOUNTS' ,
MERGE_HEADER_ID_LIST(I) ,
PRIMARY_KEY_ID_LIST(I) ,
NUM_COL1_ORIG_LIST(I) ,
NUM_COL1_NEW_LIST(I) ,
NUM_COL2_ORIG_LIST(I) ,
NUM_COL2_NEW_LIST(I) ,
NUM_COL3_ORIG_LIST(I) ,
NUM_COL3_NEW_LIST(I) ,
'U' ,
req_id ,
hz_utility_pub.CREATED_BY ,
hz_utility_pub.CREATION_DATE ,
hz_utility_pub.LAST_UPDATE_LOGIN ,
hz_utility_pub.LAST_UPDATE_DATE ,
hz_utility_pub.LAST_UPDATED_BY
);
UPDATE CSI_T_PARTY_ACCOUNTS yt
SET ACCOUNT_ID = NUM_COL1_NEW_LIST(I) ,
BILL_TO_ADDRESS_ID = NUM_COL2_NEW_LIST(I) ,
SHIP_TO_ADDRESS_ID = NUM_COL3_NEW_LIST(I) ,
LAST_UPDATE_DATE = SYSDATE ,
last_updated_by = arp_standard.profile.user_id ,
last_update_login = arp_standard.profile.last_update_login ,
REQUEST_ID = req_id ,
PROGRAM_APPLICATION_ID = arp_standard.profile.program_application_id ,
PROGRAM_ID = arp_standard.profile.program_id ,
PROGRAM_UPDATE_DATE = SYSDATE
WHERE TXN_ACCOUNT_DETAIL_ID = PRIMARY_KEY_ID_LIST(I);
arp_message.set_name('AR','AR_ROWS_UPDATED');
arp_message.set_line( 'Done with the update of csi_t_party_accounts' );
SELECT distinct CUSTOMER_MERGE_HEADER_ID ,
yt.TRANSACTION_SYSTEM_ID ,
yt.CUSTOMER_ID ,
yt.BILL_TO_SITE_USE_ID ,
yt.SHIP_TO_SITE_USE_ID ,
yt.INSTALL_SITE_USE_ID
FROM CSI_T_TXN_SYSTEMS yt,
RA_CUSTOMER_MERGES m
WHERE ( yt.CUSTOMER_ID = m.DUPLICATE_ID OR
yt.BILL_TO_SITE_USE_ID = m.DUPLICATE_SITE_ID OR
yt.SHIP_TO_SITE_USE_ID = m.DUPLICATE_SITE_ID OR
yt.INSTALL_SITE_USE_ID = m.DUPLICATE_SITE_ID )
AND m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num;
INSERT INTO HZ_CUSTOMER_MERGE_LOG (
MERGE_LOG_ID,
TABLE_NAME,
MERGE_HEADER_ID,
PRIMARY_KEY_ID,
NUM_COL1_ORIG,
NUM_COL1_NEW,
NUM_COL2_ORIG,
NUM_COL2_NEW,
NUM_COL3_ORIG,
NUM_COL3_NEW,
NUM_COL4_ORIG,
NUM_COL4_NEW,
ACTION_FLAG,
REQUEST_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY)
VALUES (
HZ_CUSTOMER_MERGE_LOG_s.nextval,
'CSI_T_TXN_SYSTEMS',
MERGE_HEADER_ID_LIST(I),
PRIMARY_KEY_ID_LIST(I),
NUM_COL1_ORIG_LIST(I),
NUM_COL1_NEW_LIST(I),
NUM_COL2_ORIG_LIST(I),
NUM_COL2_NEW_LIST(I),
NUM_COL3_ORIG_LIST(I),
NUM_COL3_NEW_LIST(I),
NUM_COL4_ORIG_LIST(I),
NUM_COL4_NEW_LIST(I),
'U',
req_id,
hz_utility_pub.CREATED_BY,
hz_utility_pub.CREATION_DATE,
hz_utility_pub.LAST_UPDATE_LOGIN,
hz_utility_pub.LAST_UPDATE_DATE,
hz_utility_pub.LAST_UPDATED_BY );
UPDATE CSI_T_TXN_SYSTEMS yt
SET CUSTOMER_ID = NUM_COL1_NEW_LIST(I) ,
BILL_TO_SITE_USE_ID = NUM_COL2_NEW_LIST(I) ,
SHIP_TO_SITE_USE_ID = NUM_COL3_NEW_LIST(I) ,
INSTALL_SITE_USE_ID = NUM_COL4_NEW_LIST(I) ,
LAST_UPDATE_DATE = SYSDATE ,
last_updated_by = arp_standard.profile.user_id ,
last_update_login = arp_standard.profile.last_update_login ,
REQUEST_ID = req_id ,
PROGRAM_APPLICATION_ID = arp_standard.profile.program_application_id ,
PROGRAM_ID = arp_standard.profile.program_id ,
PROGRAM_UPDATE_DATE = SYSDATE
WHERE TRANSACTION_SYSTEM_ID = PRIMARY_KEY_ID_LIST(I);
arp_message.set_name('AR','AR_ROWS_UPDATED');
arp_message.set_line( 'Done with the update of CSI_T_TXN_SYSTEMS' );