[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select 1
from csi_item_instances
where owner_party_id = p_from_fk_id
and owner_party_source_table = v_owner_party_source_table
for update nowait;
select 1
from csi_item_instances
where ( install_location_type_code = v_install_location_type_code and install_location_id = p_from_fk_id ) or
( location_type_code = v_location_type_code and location_id = p_from_fk_id )
for update nowait;
select internal_party_id
into v_internal_party_id
from csi_install_parameters;
select merge_reason_code
into l_merge_reason_code
from hz_merge_batch
where batch_id = p_batch_id;
update csi_item_instances
set owner_party_id = p_to_fk_id,
last_update_date = SYSDATE,
last_updated_by = G_USER_ID,
last_update_login = G_LOGIN_ID
where owner_party_id = p_from_fk_id
and owner_party_source_table = v_owner_party_source_table;
arp_message.set_name('AR', 'AR_ROWS_UPDATED');
/* insert record into transaction table */
v_no_of_rows := 0;
SELECT transaction_type_id
INTO v_transaction_type_id
FROM csi_txn_types
WHERE source_transaction_type = v_source_transaction_type;
SELECT transaction_id
INTO v_transaction_id
FROM csi_transactions
WHERE source_line_ref_id = p_batch_id AND
transaction_type_id = v_transaction_type_id;
SELECT CSI_TRANSACTIONS_S.nextval
INTO v_transaction_id
FROM dual;
csi_t_gen_utility_pvt.ADD('Insert record to csi_transactions');
INSERT INTO csi_transactions(
transaction_id
,transaction_date
,source_transaction_date
,transaction_type_id
,source_line_ref_id
,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
,p_batch_id
,fnd_global.user_id
,sysdate
,fnd_global.user_id
,sysdate
,fnd_global.user_id
,1
);
/* insert record into history table */
csi_t_gen_utility_pvt.ADD('Insert record to history.');
INSERT INTO CSI_ITEM_INSTANCES_H
(
INSTANCE_HISTORY_ID
,INSTANCE_ID
,TRANSACTION_ID
,OLD_LOCATION_ID
,NEW_LOCATION_ID
,FULL_DUMP_FLAG
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,OBJECT_VERSION_NUMBER
,OLD_INST_LOC_ID
,NEW_INST_LOC_ID
)
SELECT
CSI_ITEM_INSTANCES_H_S.nextval
,cii.INSTANCE_ID
,v_transaction_id
,decode( cii.location_id, p_from_fk_id, cii.location_id, null )
,decode( cii.location_id, p_from_fk_id, p_to_fk_id, null )
,'N'
,fnd_global.user_id
,sysdate
,fnd_global.user_id
,sysdate
,fnd_global.user_id
,1
,decode( cii.install_location_id, p_from_fk_id, cii.install_location_id, null )
,decode( cii.install_location_id, p_from_fk_id, p_to_fk_id, null )
FROM csi_item_instances cii
WHERE ( install_location_type_code = v_install_location_type_code and
install_location_id = p_from_fk_id ) or
( location_type_code = v_location_type_code and
location_id = p_from_fk_id );
arp_message.set_Name('CSI', 'CSI_ROWS_INSERTED');
v_error_message := 'Done with the insert of item instance history';
csi_t_gen_utility_pvt.ADD('Done with the insert of item instance history ');
csi_t_gen_utility_pvt.ADD('Num of history row updated: '||v_no_of_rows);
UPDATE csi_item_instances_h cih
SET cih.new_location_id = decode(cih.new_location_id, p_from_fk_id, p_to_fk_id),
cih.new_inst_loc_id = decode(cih.new_inst_loc_id, p_from_fk_id, p_to_fk_id),
last_update_date = sysdate
WHERE cih.transaction_id = v_transaction_id
AND cih.instance_history_id IN
(SELECT cii2.instance_history_id
FROM csi_item_instances_h cii2
WHERE(cii2.new_location_id = p_from_fk_id OR cii2.new_inst_loc_id = p_from_fk_id)
AND cii2.transaction_id = v_transaction_id);
After inserting into the history tables for the location(s) update,
now update the install_location_id and location_id, if applicable
*/
arp_message.set_name('AR', 'AR_LOCKING_TABLE');
update csi_item_instances
set install_location_id = decode( install_location_id, p_from_fk_id, p_to_fk_id, install_location_id ),
location_id = decode( location_id, p_from_fk_id, p_to_fk_id, location_id ),
last_update_date = SYSDATE,
last_updated_by = G_USER_ID,
last_update_login = G_LOGIN_ID
where ( install_location_type_code = v_install_location_type_code and
install_location_id = p_from_fk_id ) or
( location_type_code = v_install_location_type_code and
location_id = p_from_fk_id );
arp_message.set_name('AR', 'AR_ROWS_UPDATED');
csi_t_gen_utility_pvt.ADD('Num of instance row updated: '||v_no_of_rows);
select 1
from csi_i_parties
where party_id = p_from_fk_id
and party_source_table = v_party_source_table
for update nowait;
select internal_party_id
into v_internal_party_id
from csi_install_parameters;
select merge_reason_code
into l_merge_reason_code
from hz_merge_batch
where batch_id = p_batch_id;
/* insert record into transaction table */
v_no_of_rows := 0;
SELECT transaction_type_id
INTO v_transaction_type_id
FROM csi_txn_types
WHERE source_transaction_type = v_source_transaction_type;
SELECT transaction_id
INTO v_transaction_id
FROM csi_transactions
WHERE source_line_ref_id = p_batch_id
AND transaction_type_id = v_transaction_type_id;
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
,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
,p_batch_id
,fnd_global.user_id
,sysdate
,fnd_global.user_id
,sysdate
,fnd_global.user_id
,1
);
/* insert record into history table */
INSERT INTO csi_i_parties_h
(
instance_party_history_id,
instance_party_id,
transaction_id,
old_party_source_table,
new_party_source_table,
old_party_id,
new_party_id,
full_dump_flag,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
object_version_number
)
SELECT csi_i_parties_h_s.nextval,
cip.instance_party_id,
v_transaction_id,
v_party_source_table,
v_party_source_table,
p_from_fk_id,
p_to_fk_id,
'N',
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
1
FROM csi_i_parties cip
WHERE cip.party_source_table = v_party_source_table
AND cip.party_id = p_from_fk_id;
arp_message.set_Name('CSI', 'CSI_ROWS_INSERTED');
v_error_message := 'Done with the insert of party history';
csi_t_gen_utility_pvt.ADD('Party history row updated'|| v_no_of_rows);
update csi_i_parties
set party_id = p_to_fk_id,
last_update_date = SYSDATE,
last_updated_by = G_USER_ID,
last_update_login = G_LOGIN_ID
where party_id = p_from_fk_id
and party_source_table = v_party_source_table;
csi_t_gen_utility_pvt.ADD('Party row updated'||l_count);
arp_message.set_name('AR', 'AR_ROWS_UPDATED');
select 1
from csi_systems_b
where install_site_use_id = p_from_fk_id
for update nowait;
select 1
from csi_systems_b
where ship_to_contact_id = p_from_fk_id
or bill_to_contact_id = p_from_fk_id
or technical_contact_id = p_from_fk_id
or service_admin_contact_id = p_from_fk_id
for update nowait;
select internal_party_id
into v_internal_party_id
from csi_install_parameters;
select merge_reason_code
into l_merge_reason_code
from hz_merge_batch
where batch_id = p_batch_id;
SELECT transaction_type_id
INTO v_transaction_type_id
FROM csi_txn_types
WHERE source_transaction_type = v_source_transaction_type;
SELECT transaction_id
INTO v_transaction_id
FROM csi_transactions
WHERE source_line_ref_id = p_batch_id
AND transaction_type_id = v_transaction_type_id;
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
,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
,p_batch_id
,fnd_global.user_id
,sysdate
,fnd_global.user_id
,sysdate
,fnd_global.user_id
,1
);
INSERT INTO csi_systems_h
(system_history_id,
system_id,
transaction_id,
old_install_site_use_id,
new_install_site_use_id,
full_dump_flag,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
object_version_number
)
SELECT csi_systems_h_s.nextval,
csb.system_id,
v_transaction_id,
p_from_fk_id,
p_to_fk_id,
'N',
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
1
FROM csi_systems_b csb
WHERE csb.install_site_use_id = p_from_fk_id;
arp_message.set_Name('CSI', 'CSI_ROWS_INSERTED');
v_error_message := 'Done with the insert of systems history';
update csi_systems_b
set install_site_use_id = p_to_fk_id,
last_update_date = SYSDATE,
last_updated_by = G_USER_ID,
last_update_login = G_LOGIN_ID
where install_site_use_id = p_from_fk_id;
arp_message.set_name('AR', 'AR_ROWS_UPDATED');
INSERT INTO csi_systems_h
(
system_history_id,
system_id,
transaction_id,
old_ship_to_contact_id,
new_ship_to_contact_id,
old_bill_to_contact_id,
new_bill_to_contact_id,
old_technical_contact_id,
new_technical_contact_id,
old_service_admin_contact_id,
new_service_admin_contact_id,
full_dump_flag,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
object_version_number
)
SELECT csi_systems_h_s.nextval,
csb.system_id,
v_transaction_id,
decode( csb.ship_to_contact_id, p_from_fk_id, p_from_fk_id, null ),
decode( csb.ship_to_contact_id, p_from_fk_id, p_to_fk_id, null ),
decode( csb.bill_to_contact_id, p_from_fk_id, p_from_fk_id, null ),
decode( csb.bill_to_contact_id, p_from_fk_id, p_to_fk_id, null ),
decode( csb.technical_contact_id, p_from_fk_id, p_from_fk_id, null ),
decode( csb.technical_contact_id, p_from_fk_id, p_to_fk_id, null ),
decode( csb.service_admin_contact_id, p_from_fk_id, p_from_fk_id, null ),
decode( csb.service_admin_contact_id, p_from_fk_id, p_to_fk_id, null ),
'N',
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
1
FROM csi_systems_b csb
WHERE ship_to_contact_id = p_from_fk_id
OR bill_to_contact_id = p_from_fk_id
OR technical_contact_id = p_from_fk_id
OR service_admin_contact_id = p_from_fk_id ;
arp_message.set_Name('CSI', 'CSI_ROWS_INSERTED');
v_error_message := 'Done with the insert of systems history';
update csi_systems_b
set ship_to_contact_id = decode( ship_to_contact_id, p_from_fk_id, p_to_fk_id, ship_to_contact_id ),
bill_to_contact_id = decode( bill_to_contact_id, p_from_fk_id, p_to_fk_id, bill_to_contact_id ),
technical_contact_id = decode( technical_contact_id, p_from_fk_id, p_to_fk_id, technical_contact_id ),
service_admin_contact_id = decode( service_admin_contact_id, p_from_fk_id, p_to_fk_id, service_admin_contact_id ),
last_update_date = SYSDATE,
last_updated_by = G_USER_ID,
last_update_login = G_LOGIN_ID
where ship_to_contact_id = p_from_fk_id
or bill_to_contact_id = p_from_fk_id
or technical_contact_id = p_from_fk_id
or service_admin_contact_id = p_from_fk_id ;
arp_message.set_name('AR', 'AR_ROWS_UPDATED');
select 1
from csi_t_txn_systems
where install_site_use_id = p_from_fk_id
for update nowait;
select 1
from csi_t_txn_systems
where ship_to_contact_id = p_from_fk_id
or bill_to_contact_id = p_from_fk_id
or technical_contact_id = p_from_fk_id
or service_admin_contact_id = p_from_fk_id
for update nowait;
select internal_party_id
into v_internal_party_id
from csi_install_parameters;
select merge_reason_code
into l_merge_reason_code
from hz_merge_batch
where batch_id = p_batch_id;
update csi_t_txn_systems
set install_site_use_id = p_to_fk_id,
last_update_date = SYSDATE,
last_updated_by = G_USER_ID,
last_update_login = G_LOGIN_ID
where install_site_use_id = p_from_fk_id;
arp_message.set_name('AR', 'AR_ROWS_UPDATED');
update csi_t_txn_systems
set bill_to_contact_id = decode(bill_to_contact_id, p_from_fk_id, p_to_fk_id, bill_to_contact_id ),
ship_to_contact_id = decode(ship_to_contact_id, p_from_fk_id, p_to_fk_id, ship_to_contact_id ),
technical_contact_id = decode(technical_contact_id, p_from_fk_id, p_to_fk_id, technical_contact_id ),
service_admin_contact_id = decode(service_admin_contact_id,
p_from_fk_id, p_to_fk_id, service_admin_contact_id ),
last_update_date = SYSDATE,
last_updated_by = G_USER_ID,
last_update_login = G_LOGIN_ID
where ship_to_contact_id = p_from_fk_id
or bill_to_contact_id = p_from_fk_id
or technical_contact_id = p_from_fk_id
or service_admin_contact_id = p_from_fk_id;
arp_message.set_name('AR', 'AR_ROWS_UPDATED');
select 1
from csi_t_party_details
where party_source_id = p_from_fk_id
and party_source_table = v_party_source_table
for update nowait;
select internal_party_id
into v_internal_party_id
from csi_install_parameters;
select merge_reason_code
into l_merge_reason_code
from hz_merge_batch
where batch_id = p_batch_id;
update csi_t_party_details
set party_source_id = p_to_fk_id,
last_update_date = SYSDATE,
last_updated_by = G_USER_ID,
last_update_login = G_LOGIN_ID
where party_source_id = p_from_fk_id
and party_source_table = v_party_source_table;
arp_message.set_name('AR', 'AR_ROWS_UPDATED');
select 1
from csi_t_txn_line_details
where ( location_id = p_from_fk_id and location_type_code = v_location_type_code )
or ( install_location_id = p_from_fk_id and install_location_type_code = v_location_type_code )
for update nowait;
select merge_reason_code
into l_merge_reason_code
from hz_merge_batch
where batch_id = p_batch_id;
update csi_t_txn_line_details
set location_id = decode( location_id, p_from_fk_id, p_to_fk_id, location_id ),
install_location_id = decode( install_location_id, p_from_fk_id, p_to_fk_id, install_location_id ),
last_update_date = SYSDATE,
last_updated_by = G_USER_ID,
last_update_login = G_LOGIN_ID
where ( location_id = p_from_fk_id and location_type_code = v_location_type_code )
or ( install_location_id = p_from_fk_id and install_location_type_code = v_location_type_code );
arp_message.set_name('AR', 'AR_ROWS_UPDATED');