[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE gen_select(
p_system_query_rec IN csi_datastructures_pub.system_query_rec,
x_select_cl OUT NOCOPY VARCHAR2
)
IS
BEGIN
x_select_cl := 'SELECT distinct system_id,customer_id,system_type_code,system_number,
parent_system_id,ship_to_contact_id,bill_to_contact_id,technical_contact_id,
service_admin_contact_id,ship_to_site_use_id,bill_to_site_use_id,
install_site_use_id,coterminate_day_month,autocreated_from_system_id,
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
FROM csi_systems_b ';
END gen_select;
SELECT instr(p_rec_item, '%', 1, 1)
FROM dual;
SELECT instr(p_rec_item, '_', 1, 1)
FROM dual;
SELECT max(min(to_char(a.transaction_date,'dd-mon-rr hh24:mi:ss')))
INTO l_f_date
FROM csi_transactions a, csi_systems_b b,csi_systems_h c
WHERE b.system_id = c.system_id
AND c.transaction_id = a.transaction_id
AND c.full_dump_flag = 'Y'
AND a.transaction_date <=p_time_stamp
AND c.system_id = p_system_id
GROUP BY to_char(a.transaction_date,'dd-mon-rr hh24:mi:ss');
SELECT max(min(to_char(a.transaction_date,'dd-mon-rr hh24:mi:ss')))
INTO l_t_date
FROM csi_transactions a, csi_systems_b b,csi_systems_h c
WHERE b.system_id = c.system_id
AND c.transaction_id = a.transaction_id
AND a.transaction_date <=p_time_stamp
AND c.system_id = p_system_id
GROUP BY to_char(a.transaction_date,'dd-mon-rr hh24:mi:ss');
SELECT c.system_id
,c.old_customer_id
,c.new_customer_id
,c.old_system_type_code
,c.new_system_type_code
,c.old_system_number
,c.new_system_number
,c.old_parent_system_id
,c.new_parent_system_id
,c.old_ship_to_contact_id
,c.new_ship_to_contact_id
,c.old_bill_to_contact_id
,c.new_bill_to_contact_id
,c.old_technical_contact_id
,c.new_technical_contact_id
,c.old_service_admin_contact_id
,c.new_service_admin_contact_id
,c.old_ship_to_site_use_id
,c.new_ship_to_site_use_id
,c.old_install_site_use_id
,c.new_install_site_use_id
,c.old_bill_to_site_use_id
,c.new_bill_to_site_use_id
,c.old_coterminate_day_month
,c.new_coterminate_day_month
,c.old_start_date_active
,c.new_start_date_active
,c.old_end_date_active
,c.new_end_date_active
,c.old_autocreated_from_system
,c.new_autocreated_from_system
,c.old_config_system_type
,c.new_config_system_type
,c.old_context
,c.new_context
,c.old_attribute1
,c.new_attribute1
,c.old_attribute2
,c.new_attribute2
,c.old_attribute3
,c.new_attribute3
,c.old_attribute4
,c.new_attribute4
,c.old_attribute5
,c.new_attribute5
,c.old_attribute6
,c.new_attribute6
,c.old_attribute7
,c.new_attribute7
,c.old_attribute8
,c.new_attribute8
,c.old_attribute9
,c.new_attribute9
,c.old_attribute10
,c.new_attribute10
,c.old_attribute11
,c.new_attribute11
,c.old_attribute12
,c.new_attribute12
,c.old_attribute13
,c.new_attribute13
,c.old_attribute14
,c.new_attribute14
,c.old_attribute15
,c.new_attribute15
,c.full_dump_flag
,c.old_operating_unit_id
,c.new_operating_unit_id
FROM csi_transactions a,csi_systems_b b,csi_systems_h c
WHERE b.system_id = c.system_id
AND c.transaction_id = a.transaction_id
AND c.system_id = p_system_id
AND a.transaction_date BETWEEN to_date(p_f_time_stamp,'dd/mm/yyyy hh24:mi:ss')
AND to_date(p_t_time_stamp,'dd/mm/yyyy hh24:mi:ss')
ORDER BY to_char(a.transaction_date,'dd/mm/yyyy hh24:mi:ss') ;
SELECT max(to_char(a.transaction_date,'dd/mm/yyyy hh24:mi:ss'))
INTO l_to_date
FROM csi_transactions a,csi_systems_h b
WHERE a.transaction_id=b.transaction_id
AND b.system_id=p_sys_rec.system_id;
l_select_cl VARCHAR2(2000) := '';
gen_select(l_crit_systems_rec,l_select_cl);
dbms_sql.parse(l_cur_get_systems, l_select_cl||l_systems_where , dbms_sql.native);
SELECT last_purge_date
INTO l_last_purge_date
FROM CSI_ITEM_INSTANCES
WHERE rownum < 2;
SELECT 'x'
FROM csi_systems_vl
WHERE name = p_Name
AND customer_id = p_Customer_ID
--AND system_number = nvl(p_System_number,system_number)
AND (system_number IS NULL OR
system_number = p_System_number)
AND (p_System_id IS NULL OR
System_id <> p_System_id);
SELECT 'x'
FROM csi_systems_b
WHERE system_id = x_parent_system_id
START WITH parent_system_id = x_system_id
CONNECT BY parent_system_id = prior system_id;
SELECT *
FROM csi_systems_h
WHERE csi_systems_h.system_history_id = p_sys_hist_id
FOR UPDATE OF object_version_number;
SELECT system_history_id
INTO l_sys_hist_id
FROM csi_systems_h h
WHERE h.transaction_id = p_transaction_id
AND h.system_id = p_old_systems_rec.system_id;
csi_systems_h_pkg.update_row(
p_system_history_id => l_sys_hist_id,
p_system_id => fnd_api.g_miss_num,
p_transaction_id => fnd_api.g_miss_num,
p_old_customer_id => fnd_api.g_miss_num,
p_new_customer_id => l_new_systems_rec.customer_id,
p_old_system_type_code => fnd_api.g_miss_char,
p_new_system_type_code => l_new_systems_rec.system_type_code,
p_old_system_number => fnd_api.g_miss_char,
p_new_system_number => l_new_systems_rec.system_number,
p_old_parent_system_id => fnd_api.g_miss_num,
p_new_parent_system_id => l_new_systems_rec.parent_system_id,
p_old_ship_to_contact_id => fnd_api.g_miss_num,
p_new_ship_to_contact_id => l_new_systems_rec.ship_to_contact_id,
p_old_bill_to_contact_id => fnd_api.g_miss_num,
p_new_bill_to_contact_id => l_new_systems_rec.bill_to_contact_id,
p_old_technical_contact_id => fnd_api.g_miss_num,
p_new_technical_contact_id => l_new_systems_rec.technical_contact_id,
p_old_service_admin_contact_id => fnd_api.g_miss_num,
p_new_service_admin_contact_id => l_new_systems_rec.service_admin_contact_id,
p_old_ship_to_site_use_id => fnd_api.g_miss_num,
p_new_ship_to_site_use_id => l_new_systems_rec.ship_to_site_use_id,
p_old_install_site_use_id => fnd_api.g_miss_num,
p_new_install_site_use_id => l_new_systems_rec.install_site_use_id,
p_old_bill_to_site_use_id => fnd_api.g_miss_num,
p_new_bill_to_site_use_id => l_new_systems_rec.bill_to_site_use_id,
p_old_coterminate_day_month => fnd_api.g_miss_char,
p_new_coterminate_day_month => l_new_systems_rec.coterminate_day_month,
p_old_start_date_active => fnd_api.g_miss_date,
p_new_start_date_active => l_new_systems_rec.start_date_active,
p_old_end_date_active => fnd_api.g_miss_date,
p_new_end_date_active => l_new_systems_rec.end_date_active,
p_old_autocreated_from_system => fnd_api.g_miss_num,
p_new_autocreated_from_system => l_new_systems_rec.autocreated_from_system_id,
p_old_config_system_type => fnd_api.g_miss_char,
p_new_config_system_type => l_new_systems_rec.config_system_type,
p_old_context => fnd_api.g_miss_char,
p_new_context => l_new_systems_rec.context,
p_old_attribute1 => fnd_api.g_miss_char,
p_new_attribute1 => l_new_systems_rec.attribute1,
p_old_attribute2 => fnd_api.g_miss_char,
p_new_attribute2 => l_new_systems_rec.attribute2,
p_old_attribute3 => fnd_api.g_miss_char,
p_new_attribute3 => l_new_systems_rec.attribute3,
p_old_attribute4 => fnd_api.g_miss_char,
p_new_attribute4 => l_new_systems_rec.attribute4,
p_old_attribute5 => fnd_api.g_miss_char,
p_new_attribute5 => l_new_systems_rec.attribute5,
p_old_attribute6 => fnd_api.g_miss_char,
p_new_attribute6 => l_new_systems_rec.attribute6,
p_old_attribute7 => fnd_api.g_miss_char,
p_new_attribute7 => l_new_systems_rec.attribute7,
p_old_attribute8 => fnd_api.g_miss_char,
p_new_attribute8 => l_new_systems_rec.attribute8,
p_old_attribute9 => fnd_api.g_miss_char,
p_new_attribute9 => l_new_systems_rec.attribute9,
p_old_attribute10 => fnd_api.g_miss_char,
p_new_attribute10 => l_new_systems_rec.attribute10,
p_old_attribute11 => fnd_api.g_miss_char,
p_new_attribute11 => l_new_systems_rec.attribute11,
p_old_attribute12 => fnd_api.g_miss_char,
p_new_attribute12 => l_new_systems_rec.attribute12,
p_old_attribute13 => fnd_api.g_miss_char,
p_new_attribute13 => l_new_systems_rec.attribute13,
p_old_attribute14 => fnd_api.g_miss_char,
p_new_attribute14 => l_new_systems_rec.attribute14,
p_old_attribute15 => fnd_api.g_miss_char,
p_new_attribute15 => l_new_systems_rec.attribute15,
p_full_dump_flag => fnd_api.g_miss_char,
p_created_by => fnd_api.g_miss_num, -- fnd_global.user_id,
p_creation_date => fnd_api.g_miss_date,
p_last_updated_by => fnd_global.user_id,
p_last_update_date => SYSDATE,
p_last_update_login => fnd_global.conc_login_id,
p_object_version_number => fnd_api.g_miss_num,
p_old_name => fnd_api.g_miss_char,
p_new_name => l_new_systems_rec.name,
p_old_description => fnd_api.g_miss_char,
p_new_description => l_new_systems_rec.description,
p_old_operating_unit_id => fnd_api.g_miss_num,
p_new_operating_unit_id => l_new_systems_rec.operating_unit_id
);
csi_systems_h_pkg.update_row(
p_system_history_id => l_sys_hist_id,
p_system_id => fnd_api.g_miss_num,
p_transaction_id => fnd_api.g_miss_num,
p_old_customer_id => l_sys_hist_csr.old_customer_id,
p_new_customer_id => l_sys_hist_csr.new_customer_id,
p_old_system_type_code => l_sys_hist_csr.old_system_type_code,
p_new_system_type_code => l_sys_hist_csr.new_system_type_code,
p_old_system_number => l_sys_hist_csr.old_system_number,
p_new_system_number => l_sys_hist_csr.new_system_number,
p_old_parent_system_id => l_sys_hist_csr.old_parent_system_id,
p_new_parent_system_id => l_sys_hist_csr.new_parent_system_id,
p_old_ship_to_contact_id => l_sys_hist_csr.old_ship_to_contact_id,
p_new_ship_to_contact_id => l_sys_hist_csr.new_ship_to_contact_id,
p_old_bill_to_contact_id => l_sys_hist_csr.old_bill_to_contact_id,
p_new_bill_to_contact_id => l_sys_hist_csr.new_bill_to_contact_id,
p_old_technical_contact_id => l_sys_hist_csr.old_technical_contact_id,
p_new_technical_contact_id => l_sys_hist_csr.new_technical_contact_id,
p_old_service_admin_contact_id => l_sys_hist_csr.old_service_admin_contact_id,
p_new_service_admin_contact_id => l_sys_hist_csr.new_service_admin_contact_id,
p_old_ship_to_site_use_id => l_sys_hist_csr.old_ship_to_site_use_id,
p_new_ship_to_site_use_id => l_sys_hist_csr.new_ship_to_site_use_id,
p_old_install_site_use_id => l_sys_hist_csr.old_install_site_use_id,
p_new_install_site_use_id => l_sys_hist_csr.new_install_site_use_id,
p_old_bill_to_site_use_id => l_sys_hist_csr.old_bill_to_site_use_id,
p_new_bill_to_site_use_id => l_sys_hist_csr.new_bill_to_site_use_id,
p_old_coterminate_day_month => l_sys_hist_csr.old_coterminate_day_month,
p_new_coterminate_day_month => l_sys_hist_csr.new_coterminate_day_month,
p_old_start_date_active => l_sys_hist_csr.old_start_date_active,
p_new_start_date_active => l_sys_hist_csr.new_start_date_active,
p_old_end_date_active => l_sys_hist_csr.old_end_date_active,
p_new_end_date_active => l_sys_hist_csr.new_end_date_active,
p_old_autocreated_from_system => l_sys_hist_csr.old_autocreated_from_system,
p_new_autocreated_from_system => l_sys_hist_csr.new_autocreated_from_system,
p_old_config_system_type => l_sys_hist_csr.old_config_system_type,
p_new_config_system_type => l_sys_hist_csr.new_config_system_type,
p_old_context => l_sys_hist_csr.old_context,
p_new_context => l_sys_hist_csr.new_context,
p_old_attribute1 => l_sys_hist_csr.old_attribute1,
p_new_attribute1 => l_sys_hist_csr.new_attribute1,
p_old_attribute2 => l_sys_hist_csr.old_attribute2,
p_new_attribute2 => l_sys_hist_csr.new_attribute2,
p_old_attribute3 => l_sys_hist_csr.old_attribute3,
p_new_attribute3 => l_sys_hist_csr.new_attribute3,
p_old_attribute4 => l_sys_hist_csr.old_attribute4,
p_new_attribute4 => l_sys_hist_csr.new_attribute4,
p_old_attribute5 => l_sys_hist_csr.old_attribute5,
p_new_attribute5 => l_sys_hist_csr.new_attribute5,
p_old_attribute6 => l_sys_hist_csr.old_attribute6,
p_new_attribute6 => l_sys_hist_csr.new_attribute6,
p_old_attribute7 => l_sys_hist_csr.old_attribute7,
p_new_attribute7 => l_sys_hist_csr.new_attribute7,
p_old_attribute8 => l_sys_hist_csr.old_attribute8,
p_new_attribute8 => l_sys_hist_csr.new_attribute8,
p_old_attribute9 => l_sys_hist_csr.old_attribute9,
p_new_attribute9 => l_sys_hist_csr.new_attribute9,
p_old_attribute10 => l_sys_hist_csr.old_attribute10,
p_new_attribute10 => l_sys_hist_csr.new_attribute10,
p_old_attribute11 => l_sys_hist_csr.old_attribute11,
p_new_attribute11 => l_sys_hist_csr.new_attribute11,
p_old_attribute12 => l_sys_hist_csr.old_attribute12,
p_new_attribute12 => l_sys_hist_csr.new_attribute12,
p_old_attribute13 => l_sys_hist_csr.old_attribute13,
p_new_attribute13 => l_sys_hist_csr.new_attribute13,
p_old_attribute14 => l_sys_hist_csr.old_attribute14,
p_new_attribute14 => l_sys_hist_csr.new_attribute14,
p_old_attribute15 => l_sys_hist_csr.old_attribute15,
p_new_attribute15 => l_sys_hist_csr.new_attribute15,
p_full_dump_flag => fnd_api.g_miss_char,
p_created_by => fnd_api.g_miss_num,
p_creation_date => fnd_api.g_miss_date,
p_last_updated_by => fnd_global.user_id,
p_last_update_date => SYSDATE,
p_last_update_login => fnd_global.conc_login_id,
p_object_version_number => fnd_api.g_miss_num,
p_old_name => l_sys_hist_csr.old_name,
p_new_name => l_sys_hist_csr.new_name,
p_old_description => l_sys_hist_csr.old_description,
p_new_description => l_sys_hist_csr.new_description,
p_old_operating_unit_id => l_sys_hist_csr.old_operating_unit_id,
p_new_operating_unit_id => l_sys_hist_csr.new_operating_unit_id
);
csi_systems_h_pkg.insert_row(
px_system_history_id => l_systems_hist_rec.system_history_id,
p_system_id => l_old_systems_rec.system_id,
p_transaction_id => l_transaction_id,
p_old_customer_id => l_old_systems_rec.customer_id,
p_new_customer_id => l_new_systems_rec.customer_id,
p_old_system_type_code => l_old_systems_rec.system_type_code,
p_new_system_type_code => l_new_systems_rec.system_type_code,
p_old_system_number => l_old_systems_rec.system_number,
p_new_system_number => l_new_systems_rec.system_number,
p_old_parent_system_id => l_old_systems_rec.parent_system_id,
p_new_parent_system_id => l_new_systems_rec.parent_system_id,
p_old_ship_to_contact_id => l_old_systems_rec.ship_to_contact_id,
p_new_ship_to_contact_id => l_new_systems_rec.ship_to_contact_id,
p_old_bill_to_contact_id => l_old_systems_rec.bill_to_contact_id,
p_new_bill_to_contact_id => l_new_systems_rec.bill_to_contact_id,
p_old_technical_contact_id => l_old_systems_rec.technical_contact_id,
p_new_technical_contact_id => l_new_systems_rec.technical_contact_id,
p_old_service_admin_contact_id => l_old_systems_rec.service_admin_contact_id,
p_new_service_admin_contact_id => l_new_systems_rec.service_admin_contact_id,
p_old_ship_to_site_use_id => l_old_systems_rec.ship_to_site_use_id,
p_new_ship_to_site_use_id => l_new_systems_rec.ship_to_site_use_id,
p_old_install_site_use_id => l_old_systems_rec.install_site_use_id,
p_new_install_site_use_id => l_new_systems_rec.install_site_use_id,
p_old_bill_to_site_use_id => l_old_systems_rec.bill_to_site_use_id,
p_new_bill_to_site_use_id => l_new_systems_rec.bill_to_site_use_id,
p_old_coterminate_day_month => l_old_systems_rec.coterminate_day_month,
p_new_coterminate_day_month => l_new_systems_rec.coterminate_day_month,
p_old_start_date_active => l_old_systems_rec.start_date_active,
p_new_start_date_active => l_new_systems_rec.start_date_active,
p_old_end_date_active => l_old_systems_rec.end_date_active,
p_new_end_date_active => l_new_systems_rec.end_date_active,
p_old_autocreated_from_system => l_old_systems_rec.autocreated_from_system_id,
p_new_autocreated_from_system => l_new_systems_rec.autocreated_from_system_id,
p_old_config_system_type => l_old_systems_rec.config_system_type,
p_new_config_system_type => l_new_systems_rec.config_system_type,
p_old_context => l_old_systems_rec.context,
p_new_context => l_new_systems_rec.context,
p_old_attribute1 => l_old_systems_rec.attribute1,
p_new_attribute1 => l_new_systems_rec.attribute1,
p_old_attribute2 => l_old_systems_rec.attribute2,
p_new_attribute2 => l_new_systems_rec.attribute2,
p_old_attribute3 => l_old_systems_rec.attribute3,
p_new_attribute3 => l_new_systems_rec.attribute3,
p_old_attribute4 => l_old_systems_rec.attribute4,
p_new_attribute4 => l_new_systems_rec.attribute4,
p_old_attribute5 => l_old_systems_rec.attribute5,
p_new_attribute5 => l_new_systems_rec.attribute5,
p_old_attribute6 => l_old_systems_rec.attribute6,
p_new_attribute6 => l_new_systems_rec.attribute6,
p_old_attribute7 => l_old_systems_rec.attribute7,
p_new_attribute7 => l_new_systems_rec.attribute7,
p_old_attribute8 => l_old_systems_rec.attribute8,
p_new_attribute8 => l_new_systems_rec.attribute8,
p_old_attribute9 => l_old_systems_rec.attribute9,
p_new_attribute9 => l_new_systems_rec.attribute9,
p_old_attribute10 => l_old_systems_rec.attribute10,
p_new_attribute10 => l_new_systems_rec.attribute10,
p_old_attribute11 => l_old_systems_rec.attribute11,
p_new_attribute11 => l_new_systems_rec.attribute11,
p_old_attribute12 => l_old_systems_rec.attribute12,
p_new_attribute12 => l_new_systems_rec.attribute12,
p_old_attribute13 => l_old_systems_rec.attribute13,
p_new_attribute13 => l_new_systems_rec.attribute13,
p_old_attribute14 => l_old_systems_rec.attribute14,
p_new_attribute14 => l_new_systems_rec.attribute14,
p_old_attribute15 => l_old_systems_rec.attribute15,
p_new_attribute15 => l_new_systems_rec.attribute15,
p_full_dump_flag => 'Y',
p_created_by => fnd_global.user_id,
p_creation_date => SYSDATE,
p_last_updated_by => fnd_global.user_id,
p_last_update_date => SYSDATE,
p_last_update_login => fnd_global.conc_login_id,
p_object_version_number => 1,
p_old_name => l_old_systems_rec.name,
p_new_name => l_new_systems_rec.name,
p_old_description => l_old_systems_rec.description,
p_new_description => l_new_systems_rec.description,
p_old_operating_unit_id => l_old_systems_rec.operating_unit_id,
p_new_operating_unit_id => l_new_systems_rec.operating_unit_id
);
csi_systems_h_pkg.insert_row(
px_system_history_id => l_systems_hist_rec.system_history_id,
p_system_id => l_old_systems_rec.system_id,
p_transaction_id => l_transaction_id,
p_old_customer_id => l_systems_hist_rec.old_customer_id,
p_new_customer_id => l_systems_hist_rec.new_customer_id,
p_old_system_type_code => l_systems_hist_rec.old_system_type_code,
p_new_system_type_code => l_systems_hist_rec.new_system_type_code,
p_old_system_number => l_systems_hist_rec.old_system_number,
p_new_system_number => l_systems_hist_rec.new_system_number,
p_old_parent_system_id => l_systems_hist_rec.old_parent_system_id,
p_new_parent_system_id => l_systems_hist_rec.new_parent_system_id,
p_old_ship_to_contact_id => l_systems_hist_rec.old_ship_to_contact_id,
p_new_ship_to_contact_id => l_systems_hist_rec.new_ship_to_contact_id,
p_old_bill_to_contact_id => l_systems_hist_rec.old_bill_to_contact_id,
p_new_bill_to_contact_id => l_systems_hist_rec.new_bill_to_contact_id,
p_old_technical_contact_id => l_systems_hist_rec.old_technical_contact_id,
p_new_technical_contact_id => l_systems_hist_rec.new_technical_contact_id,
p_old_service_admin_contact_id => l_systems_hist_rec.old_service_admin_contact_id,
p_new_service_admin_contact_id => l_systems_hist_rec.new_service_admin_contact_id,
p_old_ship_to_site_use_id => l_systems_hist_rec.old_ship_to_site_use_id,
p_new_ship_to_site_use_id => l_systems_hist_rec.new_ship_to_site_use_id,
p_old_install_site_use_id => l_systems_hist_rec.old_install_site_use_id,
p_new_install_site_use_id => l_systems_hist_rec.new_install_site_use_id,
p_old_bill_to_site_use_id => l_systems_hist_rec.old_bill_to_site_use_id,
p_new_bill_to_site_use_id => l_systems_hist_rec.new_bill_to_site_use_id,
p_old_coterminate_day_month => l_systems_hist_rec.old_coterminate_day_month,
p_new_coterminate_day_month => l_systems_hist_rec.new_coterminate_day_month,
p_old_start_date_active => l_systems_hist_rec.old_start_date_active,
p_new_start_date_active => l_systems_hist_rec.new_start_date_active,
p_old_end_date_active => l_systems_hist_rec.old_end_date_active,
p_new_end_date_active => l_systems_hist_rec.new_end_date_active,
p_old_autocreated_from_system => l_systems_hist_rec.old_autocreated_from_system,
p_new_autocreated_from_system => l_systems_hist_rec.new_autocreated_from_system,
p_old_config_system_type => l_systems_hist_rec.old_config_system_type,
p_new_config_system_type => l_systems_hist_rec.new_config_system_type,
p_old_context => l_systems_hist_rec.old_context,
p_new_context => l_systems_hist_rec.new_context,
p_old_attribute1 => l_systems_hist_rec.old_attribute1,
p_new_attribute1 => l_systems_hist_rec.new_attribute1,
p_old_attribute2 => l_systems_hist_rec.old_attribute2,
p_new_attribute2 => l_systems_hist_rec.new_attribute2,
p_old_attribute3 => l_systems_hist_rec.old_attribute3,
p_new_attribute3 => l_systems_hist_rec.new_attribute3,
p_old_attribute4 => l_systems_hist_rec.old_attribute4,
p_new_attribute4 => l_systems_hist_rec.new_attribute4,
p_old_attribute5 => l_systems_hist_rec.old_attribute5,
p_new_attribute5 => l_systems_hist_rec.new_attribute5,
p_old_attribute6 => l_systems_hist_rec.old_attribute6,
p_new_attribute6 => l_systems_hist_rec.new_attribute6,
p_old_attribute7 => l_systems_hist_rec.old_attribute7,
p_new_attribute7 => l_systems_hist_rec.new_attribute7,
p_old_attribute8 => l_systems_hist_rec.old_attribute8,
p_new_attribute8 => l_systems_hist_rec.new_attribute8,
p_old_attribute9 => l_systems_hist_rec.old_attribute9,
p_new_attribute9 => l_systems_hist_rec.new_attribute9,
p_old_attribute10 => l_systems_hist_rec.old_attribute10,
p_new_attribute10 => l_systems_hist_rec.new_attribute10,
p_old_attribute11 => l_systems_hist_rec.old_attribute11,
p_new_attribute11 => l_systems_hist_rec.new_attribute11,
p_old_attribute12 => l_systems_hist_rec.old_attribute12,
p_new_attribute12 => l_systems_hist_rec.new_attribute12,
p_old_attribute13 => l_systems_hist_rec.old_attribute13,
p_new_attribute13 => l_systems_hist_rec.new_attribute13,
p_old_attribute14 => l_systems_hist_rec.old_attribute14,
p_new_attribute14 => l_systems_hist_rec.new_attribute14,
p_old_attribute15 => l_systems_hist_rec.old_attribute15,
p_new_attribute15 => l_systems_hist_rec.new_attribute15,
p_full_dump_flag => 'N',
p_created_by => fnd_global.user_id,
p_creation_date => SYSDATE,
p_last_updated_by => fnd_global.user_id,
p_last_update_date => SYSDATE,
p_last_update_login => fnd_global.conc_login_id,
p_object_version_number => 1,
p_old_name => l_systems_hist_rec.old_name,
p_new_name => l_systems_hist_rec.new_name,
p_old_description => l_systems_hist_rec.old_description,
p_new_description => l_systems_hist_rec.new_description,
p_old_operating_unit_id => l_systems_hist_rec.old_operating_unit_id,
p_new_operating_unit_id => l_systems_hist_rec.new_operating_unit_id
);
SELECT csi_systems_s.NEXTVAL
INTO x_system_id
FROM sys.dual;
csi_systems_b_pkg.insert_row(
px_system_id => x_system_id,
p_customer_id => p_system_rec.customer_id,
p_system_type_code => p_system_rec.system_type_code,
p_system_number => p_system_rec.system_number,
p_parent_system_id => p_system_rec.parent_system_id,
p_ship_to_contact_id => p_system_rec.ship_to_contact_id,
p_bill_to_contact_id => p_system_rec.bill_to_contact_id,
p_technical_contact_id => p_system_rec.technical_contact_id,
p_service_admin_contact_id => p_system_rec.service_admin_contact_id,
p_ship_to_site_use_id => p_system_rec.ship_to_site_use_id,
p_bill_to_site_use_id => p_system_rec.bill_to_site_use_id,
p_install_site_use_id => p_system_rec.install_site_use_id,
p_coterminate_day_month => p_system_rec.coterminate_day_month,
p_autocreated_from_system_id => p_system_rec.autocreated_from_system_id,
p_config_system_type => p_system_rec.config_system_type,
p_start_date_active => l_start_date,
p_end_date_active => p_system_rec.end_date_active,
p_context => p_system_rec.context,
p_attribute1 => p_system_rec.attribute1,
p_attribute2 => p_system_rec.attribute2,
p_attribute3 => p_system_rec.attribute3,
p_attribute4 => p_system_rec.attribute4,
p_attribute5 => p_system_rec.attribute5,
p_attribute6 => p_system_rec.attribute6,
p_attribute7 => p_system_rec.attribute7,
p_attribute8 => p_system_rec.attribute8,
p_attribute9 => p_system_rec.attribute9,
p_attribute10 => p_system_rec.attribute10,
p_attribute11 => p_system_rec.attribute11,
p_attribute12 => p_system_rec.attribute12,
p_attribute13 => p_system_rec.attribute13,
p_attribute14 => p_system_rec.attribute14,
p_attribute15 => p_system_rec.attribute15,
p_created_by => fnd_global.user_id,
p_creation_date => SYSDATE,
p_last_updated_by => fnd_global.user_id,
p_last_update_date => SYSDATE,
p_last_update_login => fnd_global.conc_login_id,
p_object_version_number => 1,
p_name => l_name,--p_system_rec.name,
p_description => p_system_rec.description,
p_operating_unit_id => p_system_rec.operating_unit_id,
p_request_id => p_system_rec.request_id,
p_program_application_id => p_system_rec.program_application_id,
p_program_id => p_system_rec.program_id,
p_program_update_date => p_system_rec.program_update_date);
csi_systems_h_pkg.insert_row(
px_system_history_id => l_system_history_id,
p_system_id => l_system_id,
p_transaction_id => p_txn_rec.transaction_id,
p_old_customer_id => NULL,
p_new_customer_id => p_system_rec.customer_id,
p_old_system_type_code => NULL,
p_new_system_type_code => p_system_rec.system_type_code,
p_old_system_number => NULL,
p_new_system_number => p_system_rec.system_number,
p_old_parent_system_id => NULL,
p_new_parent_system_id => p_system_rec.parent_system_id,
p_old_ship_to_contact_id => NULL,
p_new_ship_to_contact_id => p_system_rec.ship_to_contact_id,
p_old_bill_to_contact_id => NULL,
p_new_bill_to_contact_id => p_system_rec.bill_to_contact_id,
p_old_technical_contact_id => NULL,
p_new_technical_contact_id => p_system_rec.technical_contact_id,
p_old_service_admin_contact_id => NULL,
p_new_service_admin_contact_id => p_system_rec.service_admin_contact_id,
p_old_ship_to_site_use_id => NULL,
p_new_ship_to_site_use_id => p_system_rec.ship_to_site_use_id,
p_old_install_site_use_id => NULL,
p_new_install_site_use_id => p_system_rec.install_site_use_id,
p_old_bill_to_site_use_id => NULL,
p_new_bill_to_site_use_id => p_system_rec.bill_to_site_use_id,
p_old_coterminate_day_month => NULL,
p_new_coterminate_day_month => p_system_rec.coterminate_day_month,
p_old_start_date_active => NULL,
p_new_start_date_active => l_start_date,
p_old_end_date_active => NULL,
p_new_end_date_active => p_system_rec.end_date_active,
p_old_autocreated_from_system => NULL,
p_new_autocreated_from_system => p_system_rec.autocreated_from_system_id,
p_old_config_system_type => NULL,
p_new_config_system_type => p_system_rec.config_system_type,
p_old_context => NULL,
p_new_context => p_system_rec.context,
p_old_attribute1 => NULL,
p_new_attribute1 => p_system_rec.attribute1,
p_old_attribute2 => NULL,
p_new_attribute2 => p_system_rec.attribute2,
p_old_attribute3 => NULL,
p_new_attribute3 => p_system_rec.attribute3,
p_old_attribute4 => NULL,
p_new_attribute4 => p_system_rec.attribute4,
p_old_attribute5 => NULL,
p_new_attribute5 => p_system_rec.attribute5,
p_old_attribute6 => NULL,
p_new_attribute6 => p_system_rec.attribute6,
p_old_attribute7 => NULL,
p_new_attribute7 => p_system_rec.attribute7,
p_old_attribute8 => NULL,
p_new_attribute8 => p_system_rec.attribute8,
p_old_attribute9 => NULL,
p_new_attribute9 => p_system_rec.attribute9,
p_old_attribute10 => NULL,
p_new_attribute10 => p_system_rec.attribute10,
p_old_attribute11 => NULL,
p_new_attribute11 => p_system_rec.attribute11,
p_old_attribute12 => NULL,
p_new_attribute12 => p_system_rec.attribute12,
p_old_attribute13 => NULL,
p_new_attribute13 => p_system_rec.attribute13,
p_old_attribute14 => NULL,
p_new_attribute14 => p_system_rec.attribute14,
p_old_attribute15 => NULL,
p_new_attribute15 => p_system_rec.attribute15,
p_full_dump_flag => 'Y',
p_created_by => fnd_global.user_id,
p_creation_date => SYSDATE,
p_last_updated_by => fnd_global.user_id,
p_last_update_date => SYSDATE,
p_last_update_login => fnd_global.conc_login_id,
p_object_version_number => 1,
p_old_name => NULL,
p_new_name => l_name, --p_system_rec.name,
p_old_description => NULL,
p_new_description => p_system_rec.description,
p_old_operating_unit_id => NULL,
p_new_operating_unit_id => p_system_rec.operating_unit_id) ;
PROCEDURE update_system(
p_api_version IN NUMBER,
p_commit IN VARCHAR2,
p_init_msg_list IN VARCHAR2,
p_validation_level IN NUMBER,
p_system_rec IN csi_datastructures_pub.system_rec,
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 systems_csr (sys_id NUMBER) IS
SELECT system_id,
customer_id,
system_type_code,
system_number,
parent_system_id,
ship_to_contact_id,
bill_to_contact_id,
technical_contact_id,
service_admin_contact_id,
ship_to_site_use_id,
bill_to_site_use_id,
install_site_use_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
FROM csi_systems_b
WHERE system_id=sys_id
FOR UPDATE OF object_version_number ;
SELECT name,
description
FROM csi_systems_tl
WHERE system_id=sys_id
FOR UPDATE OF system_id ;
SELECT party_id
FROM csi_i_parties cip, csi_item_instances cii
WHERE cip.instance_id=cii.instance_id
AND cii.system_id=p_system_rec.system_id
AND cip.relationship_type_code='OWNER'
AND sysdate BETWEEN NVL(cii.active_start_date,sysdate)
AND NVL(cii.active_end_date,sysdate);
SELECT ip_account_id
FROM csi_item_instances cii,
csi_i_parties cip,
csi_ip_accounts cia
WHERE cii.instance_id=cip.instance_id
AND cii.system_id=p_system_rec.system_id
AND cip.instance_party_id=cia.instance_party_id
AND cia.relationship_type_code='OWNER'
AND sysdate BETWEEN NVL(cii.active_start_date,sysdate)
AND NVL(cii.active_end_date,sysdate);
SELECT cip.instance_id instance_id,cip.instance_party_id instance_party_id,
cip.object_version_number party_obj_version_number
,cia.ip_account_id ip_account_id,cia.object_version_number account_obj_version_number
FROM CSI_ITEM_INSTANCES cii,
CSI_I_PARTIES cip,
CSI_IP_ACCOUNTS cia
WHERE cii.system_id = p_system_rec.system_id
AND cip.instance_id = cii.instance_id
AND cip.relationship_type_code='OWNER'
AND cip.instance_party_id=cia.instance_party_id
AND cia.relationship_type_code='OWNER'
AND sysdate BETWEEN NVL(cii.active_start_date,sysdate) AND NVL(cii.active_end_date,sysdate)
AND sysdate BETWEEN NVL(cip.active_start_date,sysdate) AND NVL(cip.active_end_date,sysdate)
AND sysdate BETWEEN NVL(cia.active_start_date,sysdate) AND NVL(cia.active_end_date,sysdate);
SELECT cia.ip_account_id ip_account_id,cia.object_version_number object_version_number
,cia.bill_to_address,cia.ship_to_address
FROM CSI_ITEM_INSTANCES cii,
CSI_I_PARTIES cip,
CSI_IP_ACCOUNTS cia
WHERE cii.system_id = p_system_rec.system_id
AND cip.instance_id = cii.instance_id
AND cip.relationship_type_code='OWNER'
AND cip.instance_party_id=cia.instance_party_id
AND cia.relationship_type_code='OWNER'
AND sysdate BETWEEN NVL(cii.active_start_date,sysdate) AND NVL(cii.active_end_date,sysdate)
AND sysdate BETWEEN NVL(cip.active_start_date,sysdate) AND NVL(cip.active_end_date,sysdate)
AND sysdate BETWEEN NVL(cia.active_start_date,sysdate) AND NVL(cia.active_end_date,sysdate)
AND ((NVL(cia.bill_to_address,-999) = NVL(p_bill_to,-999)) OR
(NVL(cia.ship_to_address,-999) = NVL(p_ship_to,-999))) ;
SELECT cip.instance_party_id instance_party_id
FROM CSI_ITEM_INSTANCES cii,
CSI_I_PARTIES cip
WHERE cii.system_id = p_system_rec.system_id
AND cip.instance_id = cii.instance_id
AND cip.relationship_type_code = 'OWNER'
AND sysdate BETWEEN NVL(cii.active_start_date,sysdate) AND NVL(cii.active_end_date,sysdate)
AND sysdate BETWEEN NVL(cip.active_start_date,sysdate) AND NVL(cip.active_end_date,sysdate);
SELECT instance_party_id,object_version_number,party_id,relationship_type_code
FROM CSI_I_PARTIES
WHERE contact_ip_id = p_contact_ip_id
AND contact_flag = 'Y'
AND party_source_table = 'HZ_PARTIES'
AND sysdate BETWEEN NVL(active_start_date,sysdate) AND NVL(active_end_date,sysdate);
SELECT instance_id, install_location_id, object_version_number
FROM CSI_ITEM_INSTANCES
WHERE sysdate BETWEEN NVL(active_start_date,sysdate) AND NVL(active_end_date,sysdate)
AND system_id = p_sys_id;
l_api_name CONSTANT VARCHAR2(30) := 'update_system';
SAVEPOINT update_system_pvt;
csi_gen_utility_pvt.put_line( 'update_system');
p_validation_mode => 'UPDATE',
p_system_id => p_system_rec.system_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
p_validation_mode => 'UPDATE',
p_object_version_number => p_system_rec.object_version_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
p_validation_mode => 'UPDATE',
p_system_rec => p_system_rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
p_validation_mode => 'UPDATE',
p_system_id => p_system_rec.system_id,
p_start_date => p_system_rec.start_date_active,
p_end_date => p_system_rec.end_date_active,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
SELECT MAX(t.transaction_date)
INTO l_transaction_date
FROM csi_systems_h s,
csi_transactions t
WHERE s.system_id=p_system_rec.system_id
AND s.transaction_id=t.transaction_id;
csi_systems_b_pkg.update_row_for_mu(
p_system_id => p_system_rec.system_id,
p_customer_id => p_system_rec.customer_id,
p_system_type_code => p_system_rec.system_type_code,
p_system_number => p_system_rec.system_number,
p_parent_system_id => p_system_rec.parent_system_id,
p_ship_to_contact_id => p_system_rec.ship_to_contact_id,
p_bill_to_contact_id => p_system_rec.bill_to_contact_id,
p_technical_contact_id => p_system_rec.technical_contact_id,
p_service_admin_contact_id => p_system_rec.service_admin_contact_id,
p_ship_to_site_use_id => p_system_rec.ship_to_site_use_id,
p_bill_to_site_use_id => p_system_rec.bill_to_site_use_id,
p_install_site_use_id => p_system_rec.install_site_use_id,
p_coterminate_day_month => p_system_rec.coterminate_day_month,
p_autocreated_from_system_id => p_system_rec.autocreated_from_system_id,
p_start_date_active => p_system_rec.start_date_active,
p_end_date_active => p_system_rec.end_date_active,
p_context => p_system_rec.context,
p_attribute1 => p_system_rec.attribute1,
p_attribute2 => p_system_rec.attribute2,
p_attribute3 => p_system_rec.attribute3,
p_attribute4 => p_system_rec.attribute4,
p_attribute5 => p_system_rec.attribute5,
p_attribute6 => p_system_rec.attribute6,
p_attribute7 => p_system_rec.attribute7,
p_attribute8 => p_system_rec.attribute8,
p_attribute9 => p_system_rec.attribute9,
p_attribute10 => p_system_rec.attribute10,
p_attribute11 => p_system_rec.attribute11,
p_attribute12 => p_system_rec.attribute12,
p_attribute13 => p_system_rec.attribute13,
p_attribute14 => p_system_rec.attribute14,
p_attribute15 => p_system_rec.attribute15,
p_created_by => fnd_api.g_miss_num,
p_creation_date => fnd_api.g_miss_date,
p_last_updated_by => fnd_global.user_id,
p_last_update_date => SYSDATE,
p_last_update_login => fnd_global.conc_login_id,
p_object_version_number => p_system_rec.object_version_number,
p_name => p_system_rec.name,
p_description => p_system_rec.description,
p_operating_unit_id => p_system_rec.operating_unit_id,
p_request_id => p_system_rec.request_id,
p_program_application_id => p_system_rec.program_application_id,
p_program_id => p_system_rec.program_id,
p_program_update_date => p_system_rec.program_update_date);
csi_systems_b_pkg.update_row(
p_system_id => p_system_rec.system_id,
p_customer_id => p_system_rec.customer_id,
p_system_type_code => p_system_rec.system_type_code,
p_system_number => p_system_rec.system_number,
p_parent_system_id => p_system_rec.parent_system_id,
p_ship_to_contact_id => p_system_rec.ship_to_contact_id,
p_bill_to_contact_id => p_system_rec.bill_to_contact_id,
p_technical_contact_id => p_system_rec.technical_contact_id,
p_service_admin_contact_id => p_system_rec.service_admin_contact_id,
p_ship_to_site_use_id => p_system_rec.ship_to_site_use_id,
p_bill_to_site_use_id => p_system_rec.bill_to_site_use_id,
p_install_site_use_id => p_system_rec.install_site_use_id,
p_coterminate_day_month => p_system_rec.coterminate_day_month,
p_autocreated_from_system_id => p_system_rec.autocreated_from_system_id,
p_start_date_active => p_system_rec.start_date_active,
p_end_date_active => p_system_rec.end_date_active,
p_context => p_system_rec.context,
p_attribute1 => p_system_rec.attribute1,
p_attribute2 => p_system_rec.attribute2,
p_attribute3 => p_system_rec.attribute3,
p_attribute4 => p_system_rec.attribute4,
p_attribute5 => p_system_rec.attribute5,
p_attribute6 => p_system_rec.attribute6,
p_attribute7 => p_system_rec.attribute7,
p_attribute8 => p_system_rec.attribute8,
p_attribute9 => p_system_rec.attribute9,
p_attribute10 => p_system_rec.attribute10,
p_attribute11 => p_system_rec.attribute11,
p_attribute12 => p_system_rec.attribute12,
p_attribute13 => p_system_rec.attribute13,
p_attribute14 => p_system_rec.attribute14,
p_attribute15 => p_system_rec.attribute15,
p_created_by => fnd_api.g_miss_num,
p_creation_date => fnd_api.g_miss_date,
p_last_updated_by => fnd_global.user_id,
p_last_update_date => SYSDATE,
p_last_update_login => fnd_global.conc_login_id,
p_object_version_number => p_system_rec.object_version_number,
p_name => p_system_rec.name,
p_description => p_system_rec.description,
p_operating_unit_id => p_system_rec.operating_unit_id,
p_request_id => p_system_rec.request_id,
p_program_application_id => p_system_rec.program_application_id,
p_program_id => p_system_rec.program_id,
p_program_update_date => p_system_rec.program_update_date);
/************* COMMENTED SINCE API SHOULD BE CALLED RATHER THAN DIRECT UPDATE
check version 115.54 for the old code
*********************** END OF COMMENT ****************/
END IF;
select party_id
into l_party_id
from HZ_CUST_ACCOUNTS
where cust_account_id = p_system_rec.customer_id;
select 'Y'
into l_exists
from CSI_ITEM_INSTANCES
where instance_id = v_rec.instance_id
and owner_party_id = l_party_id
and nvl(owner_party_account_id,-999) = p_system_rec.customer_id;
csi_party_relationships_pub.update_inst_party_relationship
(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_party_tbl => l_party_tbl
,p_party_account_tbl=> l_party_account_tbl
,p_txn_rec => p_txn_rec
,p_oks_txn_inst_tbl => px_oks_txn_inst_tbl
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
UPDATE CSI_TRANSACTIONS
set contracts_invoked = 'Y'
where transaction_id = p_txn_rec.transaction_id;
select 'Y'
into l_exists
from CSI_ITEM_INSTANCES
where instance_id = v_install_rec.instance_id
and nvl(install_location_id,-999) = l_install_to;
csi_item_instance_pvt.update_Item_Instance
(
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_instance_rec => l_instance_rec
,p_txn_rec => p_txn_rec
,x_instance_id_lst => l_instance_id_lst
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_item_attribute_tbl => l_item_attribute_tbl
,p_location_tbl => l_location_tbl
,p_generic_id_tbl => l_generic_id_tbl
,p_lookup_tbl => l_lookup_tbl
,p_ins_count_rec => l_ins_count_rec
,p_oks_txn_inst_tbl => px_oks_txn_inst_tbl
,p_child_inst_tbl => px_child_inst_tbl
);
csi_gen_utility_pvt.put_line('Error from csi_item_instance_pvt.update_item_instance while updating Install Location.');
-- Call Update_Inst_party_account
csi_party_relationships_pvt.update_inst_party_account
( 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_party_account_rec => l_party_account_tbl(1)
,p_txn_rec => p_txn_rec
,p_oks_txn_inst_tbl => px_oks_txn_inst_tbl
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
csi_party_relationships_pvt.update_inst_party_relationship
(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_party_rec => l_party_tbl(1)
,p_txn_rec => p_txn_rec
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
ROLLBACK TO update_system_pvt;
ROLLBACK TO update_system_pvt;
ROLLBACK TO update_system_pvt;
END update_system;
SELECT system_id,
customer_id,
system_type_code,
system_number,
parent_system_id,
ship_to_contact_id,
bill_to_contact_id,
technical_contact_id,
service_admin_contact_id,
ship_to_site_use_id,
bill_to_site_use_id,
install_site_use_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
FROM csi_systems_b
WHERE system_id=p_system_id
FOR UPDATE OF object_version_number ;
SELECT name,
description
FROM csi_systems_tl
WHERE system_id=sys_id
FOR UPDATE OF system_id;
SELECT *
FROM csi_item_instances
WHERE system_id=p_system_id
AND sysdate BETWEEN NVL(active_start_date,(sysdate -1))
AND NVL(active_end_date,(sysdate +1));
-- <= sysdate. Changed this to call Update_Item_Instance which takes care of changing the
-- status to EXPIRED when the active_end_Date is <= sysdate. For active_end_date > sysdate,
-- only the date component should change for the instances and status should remain as it is.
--
IF NVL(fnd_profile.value('CSI_CASCADE_SYS_TERMINATE'),'N')='Y' THEN
FOR expire_csr IN expire_instance_csr(p_system_rec.system_id)
LOOP
Begin
l_exists := 'N';
select 'Y'
into l_exists
from CSI_ITEM_INSTANCES
where instance_id = expire_csr.instance_id
and nvl(active_end_date,(sysdate+1)) <= sysdate;
l_ext_attrib_values_tbl.DELETE;
l_party_tbl.DELETE;
l_account_tbl.DELETE;
l_pricing_attrib_tbl.DELETE;
l_org_assignments_tbl.DELETE;
l_asset_assignment_tbl.DELETE;
csi_gen_utility_pvt.put_line('Calling Update for instance_id '||to_char(l_instance_rec.instance_id));
csi_item_instance_pub.update_item_instance
(
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_instance_rec => l_instance_rec
,p_ext_attrib_values_tbl => l_ext_attrib_values_tbl
,p_party_tbl => l_party_tbl
,p_account_tbl => l_account_tbl
,p_pricing_attrib_tbl => l_pricing_attrib_tbl
,p_org_assignments_tbl => l_org_assignments_tbl
,p_asset_assignment_tbl => l_asset_assignment_tbl
,p_txn_rec => p_txn_rec
,x_instance_id_lst => x_instance_id_lst
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
csi_systems_b_pkg.update_row(
p_system_id => p_system_rec.system_id,
p_customer_id => p_system_rec.customer_id,
p_system_type_code => p_system_rec.system_type_code,
p_system_number => p_system_rec.system_number,
p_parent_system_id => p_system_rec.parent_system_id,
p_ship_to_contact_id => p_system_rec.ship_to_contact_id,
p_bill_to_contact_id => p_system_rec.bill_to_contact_id,
p_technical_contact_id => p_system_rec.technical_contact_id,
p_service_admin_contact_id => p_system_rec.service_admin_contact_id,
p_ship_to_site_use_id => p_system_rec.ship_to_site_use_id,
p_bill_to_site_use_id => p_system_rec.bill_to_site_use_id,
p_install_site_use_id => p_system_rec.install_site_use_id,
p_coterminate_day_month => p_system_rec.coterminate_day_month,
p_autocreated_from_system_id => p_system_rec.autocreated_from_system_id,
p_start_date_active => p_system_rec.start_date_active,
p_end_date_active => l_sysdate,
p_context => p_system_rec.context,
p_attribute1 => p_system_rec.attribute1,
p_attribute2 => p_system_rec.attribute2,
p_attribute3 => p_system_rec.attribute3,
p_attribute4 => p_system_rec.attribute4,
p_attribute5 => p_system_rec.attribute5,
p_attribute6 => p_system_rec.attribute6,
p_attribute7 => p_system_rec.attribute7,
p_attribute8 => p_system_rec.attribute8,
p_attribute9 => p_system_rec.attribute9,
p_attribute10 => p_system_rec.attribute10,
p_attribute11 => p_system_rec.attribute11,
p_attribute12 => p_system_rec.attribute12,
p_attribute13 => p_system_rec.attribute13,
p_attribute14 => p_system_rec.attribute14,
p_attribute15 => p_system_rec.attribute15,
p_created_by => fnd_api.g_miss_num,
p_creation_date => fnd_api.g_miss_date,
p_last_updated_by => fnd_global.user_id,
p_last_update_date => SYSDATE,
p_last_update_login => fnd_global.conc_login_id,
p_object_version_number => fnd_api.g_miss_num,
p_name => p_system_rec.name,
p_description => p_system_rec.description,
p_operating_unit_id => p_system_rec.operating_unit_id,
p_request_id => p_system_rec.request_id,
p_program_application_id => p_system_rec.program_application_id,
p_program_id => p_system_rec.program_id,
p_program_update_date => p_system_rec.program_update_date);
SELECT 'x'
INTO l_dummy
FROM csi_systems_b
WHERE system_id=p_system_id;
ELSIF p_validation_mode='UPDATE' OR p_validation_mode='EXPIRE' THEN
IF ( (p_system_id IS NOT NULL) AND (p_system_id<>fnd_api.g_miss_num) )
THEN
BEGIN
SELECT 'x'
INTO l_dummy
FROM csi_systems_b
WHERE system_id=p_system_id;
SELECT 'x'
INTO l_dummy
FROM hz_cust_accounts hzc
,hz_parties hz
WHERE hzc.cust_account_id = p_customer_id
AND hzc.party_id=hz.party_id;
/*SELECT 'x'
INTO l_dummy
FROM hz_parties
WHERE party_id=p_customer_id; */
ELSIF p_validation_mode='UPDATE' THEN
IF ( (p_customer_id IS NOT NULL) AND (p_customer_id<>fnd_api.g_miss_num) )
THEN
BEGIN
SELECT 'x'
INTO l_dummy
FROM hz_cust_accounts hzc
,hz_parties hz
WHERE hzc.cust_account_id = p_customer_id
AND hzc.party_id=hz.party_id;
/* SELECT 'x'
INTO l_dummy
FROM hz_parties
WHERE party_id=p_customer_id; */
SELECT 'x'
INTO l_dummy
FROM csi_lookups
WHERE lookup_type=l_sys_lookup_type
AND lookup_code=p_system_type_code;
ELSIF p_validation_mode='UPDATE' THEN
IF ( (p_system_type_code IS NOT NULL) AND (p_system_type_code<>fnd_api.g_miss_char) )
THEN
BEGIN
SELECT 'x'
INTO l_dummy
FROM csi_lookups
WHERE lookup_type=l_sys_lookup_type
AND lookup_code=p_system_type_code;
SELECT 'x'
INTO l_dummy
FROM csi_systems_b
WHERE system_id=p_parent_system_id;
ELSIF p_validation_mode='UPDATE' THEN
IF ( (p_parent_system_id IS NOT NULL) AND (p_parent_system_id<>fnd_api.g_miss_num) )
THEN
BEGIN
SELECT 'x'
INTO l_dummy
FROM csi_systems_b
WHERE system_id=p_parent_system_id;
SELECT 'x'
INTO l_dummy
FROM hz_parties
WHERE party_id=p_ship_to_contact_id;
ELSIF p_validation_mode='UPDATE' THEN
IF ( (p_ship_to_contact_id IS NOT NULL) AND (p_ship_to_contact_id<>fnd_api.g_miss_num) )
THEN
BEGIN
SELECT 'x'
INTO l_dummy
FROM hz_parties
WHERE party_id=p_ship_to_contact_id;
SELECT 'x'
INTO l_dummy
FROM hz_parties
WHERE party_id=p_bill_to_contact_id;
ELSIF p_validation_mode='UPDATE' THEN
IF ( (p_bill_to_contact_id IS NOT NULL) AND (p_bill_to_contact_id<>fnd_api.g_miss_num) )
THEN
BEGIN
SELECT 'x'
INTO l_dummy
FROM hz_parties
WHERE party_id=p_bill_to_contact_id;
SELECT 'x'
INTO l_dummy
FROM hz_parties
WHERE party_id=p_technical_contact_id;
ELSIF p_validation_mode='UPDATE' THEN
IF ( (p_technical_contact_id IS NOT NULL) AND (p_technical_contact_id<>fnd_api.g_miss_num) )
THEN
BEGIN
SELECT 'x'
INTO l_dummy
FROM hz_parties
WHERE party_id=p_technical_contact_id;
SELECT 'x'
INTO l_dummy
FROM hz_parties
WHERE party_id=p_service_admin_contact_id;
ELSIF p_validation_mode='UPDATE' THEN
IF ( (p_service_admin_contact_id IS NOT NULL) AND (p_service_admin_contact_id<>fnd_api.g_miss_num) )
THEN
BEGIN
SELECT 'x'
INTO l_dummy
FROM hz_parties
WHERE party_id=p_service_admin_contact_id;
SELECT 'x'
INTO l_dummy
FROM hz_cust_site_uses_all--hz_party_sites
WHERE site_use_id = p_ship_to_site_use_id
AND site_use_code = 'SHIP_TO';
ELSIF p_validation_mode='UPDATE' THEN
IF ( (p_ship_to_site_use_id IS NOT NULL) AND (p_ship_to_site_use_id<>fnd_api.g_miss_num) )
THEN
BEGIN
SELECT 'x'
INTO l_dummy
FROM hz_cust_site_uses_all --hz_party_sites
WHERE site_use_id = p_ship_to_site_use_id
AND site_use_code = 'SHIP_TO';
SELECT 'x'
INTO l_dummy
FROM hz_cust_site_uses_all --hz_cust_acct_sites_all
WHERE site_use_id = p_bill_to_site_use_id
AND site_use_code = 'BILL_TO';
ELSIF p_validation_mode='UPDATE' THEN
IF ( (p_bill_to_site_use_id IS NOT NULL) AND (p_bill_to_site_use_id<>fnd_api.g_miss_num) )
THEN
BEGIN
SELECT 'x'
INTO l_dummy
FROM hz_cust_site_uses_all --hz_cust_acct_sites_all
WHERE site_use_id = p_bill_to_site_use_id
AND site_use_code = 'BILL_TO';
SELECT 'x'
INTO l_dummy
FROM hz_party_sites
WHERE party_site_id=p_install_site_use_id;
ELSIF p_validation_mode='UPDATE' THEN
IF ( (p_install_site_use_id IS NOT NULL) AND (p_install_site_use_id<>fnd_api.g_miss_num) )
THEN
BEGIN
SELECT 'x'
INTO l_dummy
FROM hz_party_sites
WHERE party_site_id=p_install_site_use_id;
SELECT 'x'
INTO l_dummy
FROM csi_systems_b
WHERE system_id=p_auto_sys_id;
IF p_validation_mode='UPDATE' THEN
IF ( (p_start_date IS NOT NULL) AND (p_start_date<>fnd_api.g_miss_date) )
AND ( (p_end_date IS NOT NULL) AND (p_end_date<>fnd_api.g_miss_date) )
THEN
IF (p_start_date > p_end_date)
THEN
fnd_message.set_name('CSI', 'CSI_START_DATE_GREATER');
SELECT start_date_active
INTO l_start_date_active
FROM csi_systems_b
WHERE system_id = p_system_id;
IF p_validation_mode='UPDATE' THEN
IF ( (p_name IS NULL) OR (p_name=fnd_api.g_miss_char) ) THEN
fnd_message.set_name('CSI', 'CSI_SYS_NAME_NOT_PASSED');
IF ( (p_validation_mode = 'UPDATE') OR (p_validation_mode = 'EXPIRE') ) THEN
IF ( (p_object_version_number IS NULL) OR (p_object_version_number = fnd_api.g_miss_num) ) THEN
fnd_message.set_name('CSI', 'CSI_MISSING_OBJ_VER_NUM');
l_select_cl VARCHAR2(2000) := '';
gen_select(l_crit_systems_rec,l_select_cl);
dbms_sql.parse(l_cur_get_systems, l_select_cl||l_systems_where , dbms_sql.native);
SELECT last_purge_date
INTO l_last_purge_date
FROM CSI_ITEM_INSTANCES
WHERE rownum < 2;
SELECT name
,description
INTO p_system_header_tbl(sys_row).name
,p_system_header_tbl(sys_row).description
FROM CSI_SYSTEMS_VL
WHERE system_id = p_system_header_tbl(sys_row).system_id;
SELECT hza.account_number
,hza.account_name
,hza.party_id
,hzp.party_number
,hzp.party_name
INTO p_system_header_tbl(sys_row).customer_number
,p_system_header_tbl(sys_row).customer_name
,p_system_header_tbl(sys_row).party_id
,p_system_header_tbl(sys_row).customer_party_number
,p_system_header_tbl(sys_row).party_name
FROM HZ_CUST_ACCOUNTS hza
,HZ_PARTIES hzp
WHERE hza.cust_account_id = p_system_header_tbl(sys_row).customer_id
AND hza.party_id = hzp.party_id;
SELECT hzp.party_id
,hzp.party_number
,hzp.party_name
,hzp.party_type
,hls.address1
,hls.address2
,hls.address3
,hls.address4
,hls.city
,hls.state
,hls.postal_code
,hls.country
,hls.description
,hls.location_id
,hps.party_site_number
INTO p_system_header_tbl(sys_row).ship_to_customer_id
,p_system_header_tbl(sys_row).ship_to_customer_number
,p_system_header_tbl(sys_row).ship_to_customer
,p_system_header_tbl(sys_row).ship_party_type
,p_system_header_tbl(sys_row).ship_to_address1
,p_system_header_tbl(sys_row).ship_to_address2
,p_system_header_tbl(sys_row).ship_to_address3
,p_system_header_tbl(sys_row).ship_to_address4
,p_system_header_tbl(sys_row).ship_to_location
,p_system_header_tbl(sys_row).ship_state
,p_system_header_tbl(sys_row).ship_postal_code
,p_system_header_tbl(sys_row).ship_country
,p_system_header_tbl(sys_row).ship_description
,p_system_header_tbl(sys_row).ship_to_location_id
,p_system_header_tbl(sys_row).ship_to_site_number
FROM HZ_CUST_SITE_USES_ALL hzsu
,HZ_CUST_ACCT_SITES_ALL hzca
,HZ_PARTY_SITES hps
,HZ_PARTIES hzp
,HZ_LOCATIONS hls
WHERE hzsu.site_use_id = p_system_header_tbl(sys_row).ship_to_site_use_id
AND hzca.cust_acct_site_id = hzsu.cust_acct_site_id
AND hzca.party_site_id = hps.party_site_id
AND hps.party_id = hzp.party_id
AND hps.location_id = hls.location_id;
SELECT hzp.party_id
,hzp.party_number
,hzp.party_name
,hzp.party_type
,hls.address1
,hls.address2
,hls.address3
,hls.address4
,hls.city
,hls.state
,hls.postal_code
,hls.country
,hls.description
,hls.location_id
,hps.party_site_number
INTO p_system_header_tbl(sys_row).bill_to_customer_id
,p_system_header_tbl(sys_row).bill_to_customer_number
,p_system_header_tbl(sys_row).bill_to_customer
,p_system_header_tbl(sys_row).bill_party_type
,p_system_header_tbl(sys_row).bill_to_address1
,p_system_header_tbl(sys_row).bill_to_address2
,p_system_header_tbl(sys_row).bill_to_address3
,p_system_header_tbl(sys_row).bill_to_address4
,p_system_header_tbl(sys_row).bill_to_location
,p_system_header_tbl(sys_row).bill_state
,p_system_header_tbl(sys_row).bill_postal_code
,p_system_header_tbl(sys_row).bill_country
,p_system_header_tbl(sys_row).bill_description
,p_system_header_tbl(sys_row).bill_to_location_id
,p_system_header_tbl(sys_row).bill_to_site_number
FROM HZ_CUST_SITE_USES_ALL hzsu
,HZ_CUST_ACCT_SITES_ALL hzca
,HZ_PARTY_SITES hps
,HZ_PARTIES hzp
,HZ_LOCATIONS hls
WHERE hzsu.site_use_id = p_system_header_tbl(sys_row).bill_to_site_use_id
AND hzca.cust_acct_site_id = hzsu.cust_acct_site_id
AND hzca.party_site_id = hps.party_site_id
AND hps.party_id = hzp.party_id
AND hps.location_id = hls.location_id;
SELECT hzp.party_id
,hzp.party_number
,hzp.party_name
,hzp.party_type
,hls.address1
,hls.address2
,hls.address3
,hls.address4
,hls.city
,hls.state
,hls.postal_code
,hls.country
,hls.description
,hls.location_id
,hps.party_site_number
INTO p_system_header_tbl(sys_row).install_customer_id
,p_system_header_tbl(sys_row).install_customer_number
,p_system_header_tbl(sys_row).install_customer
,p_system_header_tbl(sys_row).install_party_type
,p_system_header_tbl(sys_row).install_address1
,p_system_header_tbl(sys_row).install_address2
,p_system_header_tbl(sys_row).install_address3
,p_system_header_tbl(sys_row).install_address4
,p_system_header_tbl(sys_row).install_location
,p_system_header_tbl(sys_row).install_state
,p_system_header_tbl(sys_row).install_postal_code
,p_system_header_tbl(sys_row).install_country
,p_system_header_tbl(sys_row).install_description
,p_system_header_tbl(sys_row).install_location_id
,p_system_header_tbl(sys_row).install_site_number
FROM HZ_PARTY_SITES hps
,HZ_PARTIES hzp
,HZ_LOCATIONS hls
WHERE hps.party_site_id = p_system_header_tbl(sys_row).install_site_use_id
AND hps.party_id = hzp.party_id
AND hps.location_id = hls.location_id;
SELECT hzp.party_number
,hzp.party_name
INTO p_system_header_tbl(sys_row).technical_contact_number
,p_system_header_tbl(sys_row).technical_contact
FROM HZ_PARTIES hzp
WHERE hzp.party_id = p_system_header_tbl(sys_row).technical_contact_id;
SELECT hzp.party_number
,hzp.party_name
INTO p_system_header_tbl(sys_row).service_admin_contact_number
,p_system_header_tbl(sys_row).service_admin_contact
FROM HZ_PARTIES hzp
WHERE hzp.party_id = p_system_header_tbl(sys_row).service_admin_contact_id;
SELECT hzp.party_number
,hzp.party_name
INTO p_system_header_tbl(sys_row).bill_to_contact_number
,p_system_header_tbl(sys_row).bill_to_contact
FROM HZ_PARTIES hzp
WHERE hzp.party_id = p_system_header_tbl(sys_row).bill_to_contact_id;
SELECT hzp.party_number
,hzp.party_name
INTO p_system_header_tbl(sys_row).ship_to_contact_number
,p_system_header_tbl(sys_row).ship_to_contact
FROM HZ_PARTIES hzp
WHERE hzp.party_id = p_system_header_tbl(sys_row).ship_to_contact_id;
SELECT name
INTO p_system_header_tbl(sys_row).operating_unit_name
FROM HR_OPERATING_UNITS
WHERE organization_id = p_system_header_tbl(sys_row).operating_unit_id;
SELECT name
,description
INTO p_system_header_tbl(sys_row).parent_name
,p_system_header_tbl(sys_row).parent_description
FROM CSI_SYSTEMS_VL
WHERE system_id = p_system_header_tbl(sys_row).parent_system_id;
SELECT meaning
INTO p_system_header_tbl(sys_row).system_type
FROM CSI_LOOKUPS
WHERE lookup_type = l_sys_type
AND lookup_code = p_system_header_tbl(sys_row).system_type_code;
SELECT *
FROM CSI_SYSTEMS_H
WHERE transaction_id = p_txn_id
AND system_id = p_sys_id;