Skip to Main Content

E-Business Suite

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

IB UPDATE_ITEM_INSTANCE ERROR - doesn't allow ACTIVE_START_DATE to change

336471Sep 15 2008 — edited Mar 5 2009
All,

I need to update active_start_date of IB instance. I have written a test program to update active_start_date using
csi_item_instance_pub.update_item_instance API. After running the script, I am
getting following error:

Msg1: Installed Base doesn't allow ACTIVE_START_DATE to be changed
x_return_status = E
x_msg_count = 1
x_msg_data = Installed Base doesn't allow ACTIVE_START_DATE to be changed

Question:
Is ACTIVE_START_DATE really is not allowed to be updated using update_item_instance API ? What's the
reason ?
Is there any program code issue in the script that is causing this error ?
What are other alternatives ?

Thanks

-- this is the sample code I have used --
BEGIN
x_instance_rec.instance_id := 10028 ;
x_instance_rec.object_version_number := 1;

p_txn_rec.transaction_id := FND_API.G_MISS_NUM;
p_txn_rec.transaction_date := SYSDATE;
p_txn_rec.source_transaction_date := SYSDATE;
p_txn_rec.transaction_type_id := 1;

x_instance_rec.active_start_date := to_date('5/4/1998','mm/dd/yyyy') ; --SYSDATE;
x_instance_rec.install_date := SYSDATE ; to_date('5/4/1998','mm/dd/yyyy') ;
--P_ext_attrib_values(0).instance_id := 70146;
--P_ext_attrib_values(0).attribute_id := 10165;
--P_ext_attrib_values(0).attribute_value := 'TEST';

csi_item_instance_pub.update_item_instance(
1.0,
p_commit,
p_init_msg_lst,
1,
x_instance_rec,
p_ext_attrib_values,
p_party_tbl,
p_account_tbl,
p_pricing_attrib_tbl,
p_org_assignments_tbl,
p_asset_assignment_tbl,
p_txn_rec,
x_instance_id_lst,
x_return_status,
x_msg_count,
x_msg_data);

commit;
-- Output the results

if x_msg_count > 0 then
for j in 1 .. x_msg_count
loop

fnd_msg_pub.get ( j , FND_API.G_FALSE , x_msg_data , t_msg_dummy );
t_output := ( 'Msg' || To_Char ( j ) || ': ' || x_msg_data );

dbms_output.put_line ( SubStr ( t_output , 1 , 255 ) );
end loop;
end if;

dbms_output.put_line('x_return_status = '||x_return_status);
dbms_output.put_line('x_msg_count = '||TO_CHAR(x_msg_count));
dbms_output.put_line('x_msg_data = '||x_msg_data);

COMMIT;

END;

Comments

628428
rrb,
Is ACTIVE_START_DATE really is not allowed to be updated using update_item_instance API ? What's the
reason ?
Yes. The reasons I can quote are these: Same start date is used in party record creation in csi_i_parties, csi_ip_accounts. Also for the version labeling (history) this same date is used. I am assuming that this instance is at customer and not internal. Hence if there is warranty, this same date is used (in the absence of install date for manually created IB). But these reasons are not sufficient to justify the fact that you cannot update the date. Someone from Development team can point out.
Is there any program code issue in the script that is causing this error ?
What are other alternatives ?

I see none. Alternatives, hard update is the only way.

But again, can you tell us why you are trying to update this date (business reasons)?

Thanks
Nagamohan
336471
Nagmohan

Thanks. The reason is dates are wrong when we did the initial conversion, causing users to think IB records are new but some are very old.

Assuming we need to update directly, do we need to insert into history table ? also into any other tables ? I see that when I use API to update install date it inserts a new records into history table.

Also, what is the use of p_txn_rec when passing to API ? does these id, date, corresponds to some columns in IB tables ?


p_txn_rec.transaction_id := FND_API.G_MISS_NUM;
p_txn_rec.transaction_date := SYSDATE;
p_txn_rec.source_transaction_date := SYSDATE;
p_txn_rec.transaction_type_id := 1;

Thanks
628428
Instead why don't you update the installed date using API? If you are worried about the contracts getting terminated or changed, you can set the profile CSI: Contracts Enabled as No for that user where you are running the update.

If you want to hard update, it is always better to have history, otherwise there is no way you can find that it has been changed (unless you want to use the extended attributes to store the old one).

p_txn_rec is the only link between the csi_item_instances and csi_item_instance_h. This information goes into the csi_transctions table where the transaction_id in that table is linked via csi_item_instance_h to csi_item_instances. It is a way to tell that there is a specific transaction of specific type happened on this instance from a specific source.

Thanks
Nagamohan
336471
Nagmohan

We have to fix the install dates anyways for some IB.

The reason to update is I guess business is confused and creating warranty tickets instead of charge tickets by looking at IB start dates. So, we have to fix the active dates to either to correct ones or to very old dates.

It's good suggestion that to set the profile.

<<
If you want to hard update, it is always better to have history, otherwise there is no way you can find that it has been changed (unless you want to use the extended attributes to store the old one). >>

Do you mean to insert into csi_item_instance_h table through script + into csi_transactions manually ?

What happens if we don't that ?

Thanks
628428
Do you mean to insert into csi_item_instance_h table through script + into csi_transactions manually ?
Yes. You can use csi_item_instances_h_pkg and csi_transactions_pvt to do that.

Usually we create a new custom transaction type for this like say 'Active Date Correction' and use that transaction type to seperate from the manual updates we do from the screen (transaction_type_id=1). And use that.

For this transaction, you will old active_start_date and new_active_start_date in the history table.

If you have this you will be able to see that in the transactions page of the IB when you are looking at an instance.

If you do not want go in this route, other option is use a DFF or extended attribute and dump old value there and use new value to update. Gives you some comfort if someone questions later.

Both options give you ability to see but the later one may have issues as you have to control people from updating it.

Thanks
Nagamohan
336471
Hi Nagmohan

I agree with you that clean way to do is to insert records into transaction and history table.

Now, is there any test script that you have to share with me on how to use csi_item_instances_h_pkg and csi_transactions_pvt to do this ? I don't have any.

Also, when I did use the API to update the installl date, it worked, and, on install base page, transaction tab shows user as "ANONYMOUS". can we set this to specific user in the script ? if so, how and where ?

Thanks
628428
DECLARE
l_txn_id NUMBER;
l_instance_id NUMBER;
l_curr_active_start_date DATE;
l_txn_rec csi_datastructures_pub.transaction_rec;
x_return_status VARCHAR2 (1);
x_msg_count NUMBER;
x_msg_data VARCHAR (4000);
l_txn_hist_id NUMBER;
BEGIN
fnd_global.apps_initialize (1005902, 64152, 542);

SELECT active_start_date
INTO l_curr_active_start_date
FROM csi_item_instances
WHERE instance_id = 46;

UPDATE csi_item_instances
SET active_start_date = active_start_date - 30
, object_version_number = object_version_number + 1
, last_updated_by=1005902
,last_update_date=SYSDATE
WHERE instance_id = 46;

SELECT csi_transactions_s.NEXTVAL
INTO l_txn_id
FROM SYS.DUAL;

SELECT csi_item_instances_h_s.NEXTVAL
INTO l_txn_hist_id
FROM SYS.DUAL;

csi_item_instances_h_pkg.insert_row (px_instance_history_id => l_txn_hist_id
, p_instance_id => 46
, p_transaction_id => l_txn_id
, p_old_instance_number => fnd_api.g_miss_char
, p_new_instance_number => fnd_api.g_miss_char
, p_old_external_reference => fnd_api.g_miss_char
, p_new_external_reference => fnd_api.g_miss_char
, p_old_inventory_item_id => fnd_api.g_miss_num
, p_new_inventory_item_id => fnd_api.g_miss_num
, p_old_inventory_revision => fnd_api.g_miss_char
, p_new_inventory_revision => fnd_api.g_miss_char
, p_old_inv_master_orgzn_id => fnd_api.g_miss_num
, p_new_inv_master_orgzn_id => fnd_api.g_miss_num
, p_old_serial_number => fnd_api.g_miss_char
, p_new_serial_number => fnd_api.g_miss_char
, p_old_mfg_serial_number_flag => fnd_api.g_miss_char
, p_new_mfg_serial_number_flag => fnd_api.g_miss_char
, p_old_lot_number => fnd_api.g_miss_char
, p_new_lot_number => fnd_api.g_miss_char
, p_old_quantity => fnd_api.g_miss_num
, p_new_quantity => fnd_api.g_miss_num
, p_old_unit_of_measure => fnd_api.g_miss_char
, p_new_unit_of_measure => fnd_api.g_miss_char
, p_old_accounting_class_code => fnd_api.g_miss_char
, p_new_accounting_class_code => fnd_api.g_miss_char
, p_old_instance_condition_id => fnd_api.g_miss_num
, p_new_instance_condition_id => fnd_api.g_miss_num
, p_old_instance_status_id => fnd_api.g_miss_num
, p_new_instance_status_id => fnd_api.g_miss_num
, p_old_customer_view_flag => fnd_api.g_miss_char
, p_new_customer_view_flag => fnd_api.g_miss_char
, p_old_merchant_view_flag => fnd_api.g_miss_char
, p_new_merchant_view_flag => fnd_api.g_miss_char
, p_old_sellable_flag => fnd_api.g_miss_char
, p_new_sellable_flag => fnd_api.g_miss_char
, p_old_system_id => fnd_api.g_miss_num
, p_new_system_id => fnd_api.g_miss_num
, p_old_instance_type_code => fnd_api.g_miss_char
, p_new_instance_type_code => fnd_api.g_miss_char
, p_old_active_start_date => l_curr_active_start_date
, p_new_active_start_date => l_curr_active_start_date - 30
, p_old_active_end_date => fnd_api.g_miss_date
, p_new_active_end_date => fnd_api.g_miss_date
, p_old_location_type_code => fnd_api.g_miss_char
, p_new_location_type_code => fnd_api.g_miss_char
, p_old_location_id => fnd_api.g_miss_num
, p_new_location_id => fnd_api.g_miss_num
, p_old_inv_organization_id => fnd_api.g_miss_num
, p_new_inv_organization_id => fnd_api.g_miss_num
, p_old_inv_subinventory_name => fnd_api.g_miss_char
, p_new_inv_subinventory_name => fnd_api.g_miss_char
, p_old_inv_locator_id => fnd_api.g_miss_num
, p_new_inv_locator_id => fnd_api.g_miss_num
, p_old_pa_project_id => fnd_api.g_miss_num
, p_new_pa_project_id => fnd_api.g_miss_num
, p_old_pa_project_task_id => fnd_api.g_miss_num
, p_new_pa_project_task_id => fnd_api.g_miss_num
, p_old_in_transit_order_line_id => fnd_api.g_miss_num
, p_new_in_transit_order_line_id => fnd_api.g_miss_num
, p_old_wip_job_id => fnd_api.g_miss_num
, p_new_wip_job_id => fnd_api.g_miss_num
, p_old_po_order_line_id => fnd_api.g_miss_num
, p_new_po_order_line_id => fnd_api.g_miss_num
, p_old_completeness_flag => fnd_api.g_miss_char
, p_new_completeness_flag => fnd_api.g_miss_char
, p_full_dump_flag => fnd_api.g_miss_char
, p_old_context => fnd_api.g_miss_char
, p_new_context => fnd_api.g_miss_char
, p_old_attribute1 => fnd_api.g_miss_char
, p_new_attribute1 => fnd_api.g_miss_char
, p_old_attribute2 => fnd_api.g_miss_char
, p_new_attribute2 => fnd_api.g_miss_char
, p_old_attribute3 => fnd_api.g_miss_char
, p_new_attribute3 => fnd_api.g_miss_char
, p_old_attribute4 => fnd_api.g_miss_char
, p_new_attribute4 => fnd_api.g_miss_char
, p_old_attribute5 => fnd_api.g_miss_char
, p_new_attribute5 => fnd_api.g_miss_char
, p_old_attribute6 => fnd_api.g_miss_char
, p_new_attribute6 => fnd_api.g_miss_char
, p_old_attribute7 => fnd_api.g_miss_char
, p_new_attribute7 => fnd_api.g_miss_char
, p_old_attribute8 => fnd_api.g_miss_char
, p_new_attribute8 => fnd_api.g_miss_char
, p_old_attribute9 => fnd_api.g_miss_char
, p_new_attribute9 => fnd_api.g_miss_char
, p_old_attribute10 => fnd_api.g_miss_char
, p_new_attribute10 => fnd_api.g_miss_char
, p_old_attribute11 => fnd_api.g_miss_char
, p_new_attribute11 => fnd_api.g_miss_char
, p_old_attribute12 => fnd_api.g_miss_char
, p_new_attribute12 => fnd_api.g_miss_char
, p_old_attribute13 => fnd_api.g_miss_char
, p_new_attribute13 => fnd_api.g_miss_char
, p_old_attribute14 => fnd_api.g_miss_char
, p_new_attribute14 => fnd_api.g_miss_char
, p_old_attribute15 => fnd_api.g_miss_char
, p_new_attribute15 => fnd_api.g_miss_char
, p_created_by => 1005902
, p_creation_date => SYSDATE
, p_last_updated_by => 1005902
, p_last_update_date => SYSDATE
, p_last_update_login => fnd_api.g_miss_num
, p_object_version_number => 1
, p_old_inst_loc_type_code => fnd_api.g_miss_char
, p_new_inst_loc_type_code => fnd_api.g_miss_char
, p_old_inst_loc_id => fnd_api.g_miss_num
, p_new_inst_loc_id => fnd_api.g_miss_num
, p_old_inst_usage_code => fnd_api.g_miss_char
, p_new_inst_usage_code => fnd_api.g_miss_char
, p_old_last_vld_organization_id => fnd_api.g_miss_num
, p_new_last_vld_organization_id => fnd_api.g_miss_num
, p_old_config_inst_rev_num => fnd_api.g_miss_num
, p_new_config_inst_rev_num => fnd_api.g_miss_num
, p_old_config_valid_status => fnd_api.g_miss_char
, p_new_config_valid_status => fnd_api.g_miss_char
, p_old_instance_description => fnd_api.g_miss_char
, p_new_instance_description => fnd_api.g_miss_char
, p_old_install_date => fnd_api.g_miss_date
, p_new_install_date => fnd_api.g_miss_date
, p_old_return_by_date => fnd_api.g_miss_date
, p_new_return_by_date => fnd_api.g_miss_date
, p_old_actual_return_date => fnd_api.g_miss_date
, p_new_actual_return_date => fnd_api.g_miss_date
, p_old_last_oe_agreement_id => fnd_api.g_miss_num
, p_new_last_oe_agreement_id => fnd_api.g_miss_num
, p_old_last_oe_order_line_id => fnd_api.g_miss_num
, p_new_last_oe_order_line_id => fnd_api.g_miss_num
, p_old_last_oe_rma_line_id => fnd_api.g_miss_num
, p_new_last_oe_rma_line_id => fnd_api.g_miss_num
, p_old_last_wip_job_id => fnd_api.g_miss_num
, p_new_last_wip_job_id => fnd_api.g_miss_num
, p_old_last_po_po_line_id => fnd_api.g_miss_num
, p_new_last_po_po_line_id => fnd_api.g_miss_num
, p_old_last_pa_project_id => fnd_api.g_miss_num
, p_new_last_pa_project_id => fnd_api.g_miss_num
, p_old_last_pa_task_id => fnd_api.g_miss_num
, p_new_last_pa_task_id => fnd_api.g_miss_num
, p_old_last_txn_line_detail_id => fnd_api.g_miss_num
, p_new_last_txn_line_detail_id => fnd_api.g_miss_num
, p_old_last_oe_po_number => fnd_api.g_miss_char
, p_new_last_oe_po_number => fnd_api.g_miss_char
, p_old_network_asset_flag => fnd_api.g_miss_char
, p_new_network_asset_flag => fnd_api.g_miss_char
, p_old_maintainable_flag => fnd_api.g_miss_char
, p_new_maintainable_flag => fnd_api.g_miss_char
, p_old_pn_location_id => fnd_api.g_miss_num
, p_new_pn_location_id => fnd_api.g_miss_num
, p_old_asset_criticality_code => fnd_api.g_miss_char
, p_new_asset_criticality_code => fnd_api.g_miss_char
, p_old_category_id => fnd_api.g_miss_num
, p_new_category_id => fnd_api.g_miss_num
, p_old_equipment_gen_object_id => fnd_api.g_miss_num
, p_new_equipment_gen_object_id => fnd_api.g_miss_num
, p_old_instantiation_flag => fnd_api.g_miss_char
, p_new_instantiation_flag => fnd_api.g_miss_char
, p_old_linear_location_id => fnd_api.g_miss_num
, p_new_linear_location_id => fnd_api.g_miss_num
, p_old_operational_log_flag => fnd_api.g_miss_char
, p_new_operational_log_flag => fnd_api.g_miss_char
, p_old_checkin_status => fnd_api.g_miss_num
, p_new_checkin_status => fnd_api.g_miss_num
, p_old_sup_warranty_exp_date => fnd_api.g_miss_date
, p_new_sup_warranty_exp_date => fnd_api.g_miss_date
, p_old_attribute16 => fnd_api.g_miss_char
, p_new_attribute16 => fnd_api.g_miss_char
, p_old_attribute17 => fnd_api.g_miss_char
, p_new_attribute17 => fnd_api.g_miss_char
, p_old_attribute18 => fnd_api.g_miss_char
, p_new_attribute18 => fnd_api.g_miss_char
, p_old_attribute19 => fnd_api.g_miss_char
, p_new_attribute19 => fnd_api.g_miss_char
, p_old_attribute20 => fnd_api.g_miss_char
, p_new_attribute20 => fnd_api.g_miss_char
, p_old_attribute21 => fnd_api.g_miss_char
, p_new_attribute21 => fnd_api.g_miss_char
, p_old_attribute22 => fnd_api.g_miss_char
, p_new_attribute22 => fnd_api.g_miss_char
, p_old_attribute23 => fnd_api.g_miss_char
, p_new_attribute23 => fnd_api.g_miss_char
, p_old_attribute24 => fnd_api.g_miss_char
, p_new_attribute24 => fnd_api.g_miss_char
, p_old_attribute25 => fnd_api.g_miss_char
, p_new_attribute25 => fnd_api.g_miss_char
, p_old_attribute26 => fnd_api.g_miss_char
, p_new_attribute26 => fnd_api.g_miss_char
, p_old_attribute27 => fnd_api.g_miss_char
, p_new_attribute27 => fnd_api.g_miss_char
, p_old_attribute28 => fnd_api.g_miss_char
, p_new_attribute28 => fnd_api.g_miss_char
, p_old_attribute29 => fnd_api.g_miss_char
, p_new_attribute29 => fnd_api.g_miss_char
, p_old_attribute30 => fnd_api.g_miss_char
, p_new_attribute30 => fnd_api.g_miss_char
, p_old_payables_unit_price => fnd_api.g_miss_num
, p_new_payables_unit_price => fnd_api.g_miss_num
, p_old_payables_currency_code => fnd_api.g_miss_char
, p_new_payables_currency_code => fnd_api.g_miss_char
, p_old_purchase_unit_price => fnd_api.g_miss_num
, p_new_purchase_unit_price => fnd_api.g_miss_num
, p_old_purchase_currency_code => fnd_api.g_miss_char
, p_new_purchase_currency_code => fnd_api.g_miss_char
, p_old_sales_unit_price => fnd_api.g_miss_num
, p_new_sales_unit_price => fnd_api.g_miss_num
, p_old_sales_currency_code => fnd_api.g_miss_char
, p_new_sales_currency_code => fnd_api.g_miss_char
, p_old_operational_status_code => fnd_api.g_miss_char
, p_new_operational_status_code => fnd_api.g_miss_char
);
l_txn_rec.transaction_id := l_txn_id;
l_txn_rec.transaction_date := SYSDATE;
l_txn_rec.source_transaction_date := SYSDATE;
l_txn_rec.transaction_type_id := 1;

csi_transactions_pvt.create_transaction (p_api_version => 1.0
, p_transaction_rec => l_txn_rec
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
DBMS_OUTPUT.put_line (x_return_status);
DBMS_OUTPUT.put_line (x_msg_count);
END;


ANONYMOUS appears as the user name because you might not have initiated apps using fnd_global.apps_initialize. Try initiating.

Thanks
Nagamohan
336471
Nagmohan
Great. I will try and let you know. By the way, analyst said it's sufficient to update the active_start_date.

Thanks
336471
Nagmohan

I was able to update the active_start_date as well insert history. I had to change script little bit to fit my environment.

Thanks for your help!
336471
Yes, I think this question answered to best of my knowledge.
336471
Nagmohan,

One more follow-up question on this.

I noticed some of the following tables are related to csi_item_instances, for example, parties, accounts, and therir history tables.

csi_item_instances_h
csi_transactions
csi_i_parties, csi_i_parties_h
csi_ip_accounts, csi_ip_accounts_h
csi_ii_relationships, csi_ii_relationships_h
csi_iea_values, csi_iea_values_h
csi_i_extended_attribs
csi_i_org_assignments, csi_i_org_assignments_h
csi_i_version_labels, csi_i_version_labels_h

Do you think to worry about these associated tables in anyway ? or if not what's the impact ? or just updating active_start_date alone would be sufficient?

Thanks much
1 - 11
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Apr 2 2009
Added on Sep 15 2008
11 comments
2,996 views