--一、资产新增
PROCEDURE do_addition IS
l_trans_rec fa_api_types.trans_rec_type;
l_dist_trans_rec fa_api_types.trans_rec_type;
l_asset_hdr_rec fa_api_types.asset_hdr_rec_type;
l_asset_desc_rec fa_api_types.asset_desc_rec_type;
l_asset_cat_rec fa_api_types.asset_cat_rec_type;
l_asset_type_rec fa_api_types.asset_type_rec_type;
l_asset_hierarchy_rec fa_api_types.asset_hierarchy_rec_type;
l_asset_fin_rec fa_api_types.asset_fin_rec_type;
l_asset_deprn_rec fa_api_types.asset_deprn_rec_type;
l_asset_dist_rec fa_api_types.asset_dist_rec_type;
l_asset_dist_tbl fa_api_types.asset_dist_tbl_type;
l_inv_tbl fa_api_types.inv_tbl_type;
l_inv_rate_tbl fa_api_types.inv_rate_tbl_type;
l_inv_rec fa_api_types.inv_rec_type;
l_return_status VARCHAR2(1);
l_mesg_count NUMBER := 0;
l_mesg_len NUMBER;
l_mesg VARCHAR2(4000);
BEGIN
--初始化
fnd_profile.put(‘PRINT_DEBUG‘, ‘Y‘);
dbms_output.enable(10000000);
fa_srvr_msg.init_server_message;
fa_debug_pkg.initialize;
--资产描述信息
l_asset_desc_rec.description := ‘电脑‘;
l_asset_desc_rec.tag_number := ‘BZ20120223003‘;
l_asset_desc_rec.asset_key_ccid := 1001; --2;
l_asset_cat_rec.category_id := 1001;
l_asset_type_rec.asset_type := ‘CAPITALIZED‘;
--l_asset_desc_rec.asset_number :=
--l_asset_desc_rec.property_type_code :=
--l_asset_desc_rec.property_1245_1250_code :=
--l_asset_desc_rec.in_use_flag :=
--l_asset_desc_rec.owned_leased :=
--l_asset_desc_rec.new_used :=
--l_asset_desc_rec.inventorial :=
--l_asset_desc_rec.manufacturer_name :=
--l_asset_desc_rec.serial_number :=
--l_asset_desc_rec.model_number :=
--l_asset_desc_rec.tag_number :=
--l_asset_desc_rec.parent_asset_id :=
--l_asset_desc_rec.warranty_id :=
--l_asset_desc_rec.lease_id :=
--资产发票信息
/*
-- invoice info
-- l_inv_rec.fixed_assets_cost := 2500;
-- l_inv_rec.deleted_flag := ‘NO‘;
l_inv_rec.description := l_asset_desc_rec.description;
-- l_inv_rec.unrevalued_cost := 5555;
-- l_inv_rec.create_batch_id := 1000;
--l_inv_rec.payables_code_combination_id := 13528;
l_inv_rec.feeder_system_name := ‘BZ‘;
-- l_inv_rec.payables_cost := 5555;
l_inv_rec.payables_units := 1;
l_inv_rec.po_vendor_id := 1;
--l_inv_rec.inv_indicator := 1;
*/
l_inv_tbl(1) := l_inv_rec;
--发票汇率信息
/*
l_inv_rate_tbl(1).set_of_books_id := 658; -- first reporting book
l_inv_rate_tbl(1).exchange_rate := .532;
l_inv_rate_tbl(2).inv_indicator := 1;
l_inv_rate_tbl(2).set_of_books_id := 659; -- second reporting book
l_inv_rate_tbl(2).exchange_rate := .233;
*/
--资产财务信息
l_asset_fin_rec.date_placed_in_service := to_date(‘2012-01-31‘,
‘yyyy-mm-dd‘); --‘DPIS‘;
l_asset_fin_rec.depreciate_flag := ‘YES‘;
l_asset_fin_rec.cost := 6000;
--资产折旧信息
l_asset_deprn_rec.ytd_deprn := 0;
l_asset_deprn_rec.deprn_reserve := 0;
l_asset_deprn_rec.bonus_ytd_deprn := 0;
l_asset_deprn_rec.bonus_deprn_reserve := 0;
--资产转移信息
l_asset_hdr_rec.book_type_code := ‘ZZ_FA_BOOK‘; --‘book‘;
l_trans_rec.transaction_date_entered := l_asset_fin_rec.date_placed_in_service;
l_trans_rec.who_info.last_updated_by := 1130; --FND_GLOBAL.USER_ID;
/* distribution info */
-- l_asset_dist_tbl := fa_API_TYPES.asset_dist_tbl_type (null);
l_asset_dist_rec.units_assigned := 1;
l_asset_dist_rec.expense_ccid := 20005; --12975;
l_asset_dist_rec.location_ccid := 6001; --2;
l_asset_dist_rec.assigned_to := NULL; --81
l_asset_dist_rec.transaction_units := l_asset_dist_rec.units_assigned;
l_asset_dist_tbl(1) := l_asset_dist_rec;
--调用API
fa_addition_pub .do_addition(1.0,
fnd_api.g_false,
fnd_api.g_false,
fnd_api.g_valid_level_full,
l_return_status,
l_mesg_count,
l_mesg,
NULL,
--
l_trans_rec,
l_dist_trans_rec,
--
l_asset_hdr_rec,
l_asset_desc_rec,
l_asset_type_rec,
l_asset_cat_rec,
l_asset_hierarchy_rec,
l_asset_fin_rec,
l_asset_deprn_rec,
l_asset_dist_tbl,
l_inv_tbl);
dbms_output.put_line(l_return_status);
IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
dbms_output.put_line(‘FAILED‘);
-- dbms_output.put_line(to_char(sqlerr));
dbms_output.put_line(SQLERRM);
l_mesg_count := fnd_msg_pub.count_msg;
IF l_mesg_count > 0 THEN
l_mesg := chr(10) || substr(fnd_msg_pub.get(fnd_msg_pub.g_first,
fnd_api.g_false),
1,
512);
FOR i IN 1 .. 2 LOOP
-- (l_mesg_count - 1) loop
l_mesg := l_mesg || chr(10) || substr(fnd_msg_pub.get(fnd_msg_pub.g_next,
fnd_api.g_false),
1,
512);
END LOOP;
fnd_msg_pub.delete_msg();
l_mesg_len := length(l_mesg);
FOR i IN 1 .. ceil(l_mesg_len / 255) LOOP
dbms_output.put_line(substr(l_mesg, ((i * 255) - 254), 255));
END LOOP;
END IF;
ELSE
dbms_output.put_line(‘SUCCESS‘);
dbms_output.put_line(‘THID‘ ||
to_char(l_trans_rec.transaction_header_id));
dbms_output.put_line(‘ASSET_ID‘ || to_char(l_asset_hdr_rec.asset_id));
dbms_output.put_line(‘ASSET_NUMBER‘ || l_asset_desc_rec.asset_number);
END IF;
END;
--二、资产描述信息调整
PROCEDURE update_desc IS
l_count NUMBER;
l_return_status VARCHAR2(100);
l_msg_count NUMBER := 0;
l_msg_data VARCHAR2(4000);
l_request_id NUMBER;
reclass_err EXCEPTION;
i NUMBER := 0;
l_api_version CONSTANT NUMBER := 1.0;
l_msg_list VARCHAR2(5) := fnd_api.g_false;
l_commit_flag VARCHAR2(5) := fnd_api.g_false;
l_validation_level VARCHAR2(5) := fnd_api.g_valid_level_full;
l_debug_flag VARCHAR2(5) := fnd_api.g_false;
l_calling_fn VARCHAR2(50) := ‘Update Asset Desc Script‘;
l_trans_rec fa_api_types.trans_rec_type;
l_asset_hdr_rec fa_api_types.asset_hdr_rec_type;
l_asset_desc_rec fa_api_types.asset_desc_rec_type;
l_asset_type_rec fa_api_types.asset_type_rec_type;
l_asset_cat_rec fa_api_types.asset_cat_rec_type;
l_asset_id NUMBER(15) := 64;
l_description VARCHAR2(30) := substr(‘Description‘, 1, 30);
l_serial_number VARCHAR2(10) := substr(‘Serial_number‘,
1,
10);
l_category_id NUMBER(15);
l_transaction_type_code VARCHAR2(20) := NULL;
l_transaction_date_entered DATE := NULL;
l_temp_str VARCHAR2(640);
BEGIN
--初始化
fnd_profile.put(‘PRINT_DEBUG‘, ‘Y‘);
dbms_output.enable(10000000);
fa_srvr_msg.init_server_message;
fa_debug_pkg.initialize;
l_trans_rec.who_info.last_updated_by := 1001;
l_trans_rec.who_info.last_update_login := 1001;
l_trans_rec.who_info.last_update_date := SYSDATE;
l_trans_rec.who_info.creation_date := l_trans_rec.who_info.last_update_date;
l_trans_rec.who_info.created_by := l_trans_rec.who_info.last_updated_by;
l_asset_hdr_rec.asset_id := 64;
l_asset_desc_rec.description := l_description;
l_asset_desc_rec.serial_number := l_serial_number;
l_asset_cat_rec.desc_flex.attribute1 := ‘Test Thru API‘;
l_asset_cat_rec.desc_flex.attribute2 := ‘Test2 Thru API‘;
l_return_status := NULL;
dbms_output.put_line(‘calling FA_ASSET_DESC_PUB.update_desc‘);
fa_asset_desc_pub.update_desc(
-- std parameters
p_api_version => l_api_version,
p_init_msg_list => l_msg_list,
p_commit => l_commit_flag,
p_validation_level => l_validation_level,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_calling_fn => l_calling_fn,
px_trans_rec => l_trans_rec,
px_asset_hdr_rec => l_asset_hdr_rec,
px_asset_desc_rec_new => l_asset_desc_rec,
px_asset_cat_rec_new => l_asset_cat_rec);
-- rollback;
dbms_output.put_line(‘l_return_status ‘ || l_return_status);
IF l_return_status = ‘E‘ THEN
l_msg_count := fnd_msg_pub.count_msg;
IF (l_msg_count > 0) THEN
dbms_output.put_line(‘l_msg_count ‘ || to_char(l_msg_count));
l_temp_str := substr(fnd_msg_pub.get(fnd_msg_pub.g_first,
fnd_api.g_false),
1,
230);
dbms_output.put_line(l_temp_str);
l_temp_str := NULL;
FOR i IN 1 .. (l_msg_count - 1) LOOP
l_temp_str := fnd_msg_pub.get(fnd_msg_pub.g_next, fnd_api.g_false);
dbms_output.put_line(l_temp_str);
END LOOP;
ELSE
dbms_output.put_line(‘dump: NO MESSAGE !‘);
END IF;
RETURN;
END IF;
fa_srvr_msg.add_message(calling_fn => ‘reclass.sql‘,
NAME => ‘FA_SHARED_END_SUCCESS‘,
token1 => ‘PROGRAM‘,
value1 => ‘FA_ASSET_DESC_PUB.update_desc‘);
EXCEPTION
WHEN reclass_err THEN
ROLLBACK WORK;
fa_srvr_msg.add_message(calling_fn => ‘reclass.sql‘,
NAME => ‘FA_SHARED_PROGRAM_FAILED‘,
token1 => ‘PROGRAM‘,
value1 => ‘FA_ASSET_DESC_PUB.update_desc‘);
l_msg_count := fnd_msg_pub.count_msg;
IF (l_msg_count > 0) THEN
l_temp_str := fnd_msg_pub.get(fnd_msg_pub.g_first, fnd_api.g_false);
dbms_output.put_line(‘dump: ‘ || l_temp_str);
FOR i IN 1 .. (l_msg_count - 1) LOOP
l_temp_str := fnd_msg_pub.get(fnd_msg_pub.g_next, fnd_api.g_false);
dbms_output.put_line(‘dump: ‘ || l_temp_str);
END LOOP;
ELSE
dbms_output.put_line(‘dump: NO MESSAGE !‘);
END IF;
WHEN OTHERS THEN
ROLLBACK WORK;
fa_srvr_msg.add_sql_error(calling_fn => ‘fa_asset_desc_pub..update_desc‘);
RAISE fnd_api.g_exc_unexpected_error;
END;
--三、资产交易信息及财务信息调整
PROCEDURE do_adjustment IS
l_trans_rec fa_api_types.trans_rec_type;
l_asset_hdr_rec fa_api_types.asset_hdr_rec_type;
l_asset_fin_rec_adj fa_api_types.asset_fin_rec_type;
l_asset_fin_rec_new fa_api_types.asset_fin_rec_type;
l_asset_fin_mrc_tbl_new fa_api_types.asset_fin_tbl_type;
l_inv_trans_rec fa_api_types.inv_trans_rec_type;
l_inv_tbl fa_api_types.inv_tbl_type;
l_inv_rate_tbl fa_api_types.inv_rate_tbl_type;
l_asset_deprn_rec_adj fa_api_types.asset_deprn_rec_type;
l_asset_deprn_rec_new fa_api_types.asset_deprn_rec_type;
l_asset_deprn_mrc_tbl_new fa_api_types.asset_deprn_tbl_type;
l_inv_rec fa_api_types.inv_rec_type;
l_group_reclass_options_rec fa_api_types.group_reclass_options_rec_type;
l_return_status VARCHAR2(1);
l_mesg_count NUMBER := 0;
l_mesg_len NUMBER;
l_mesg VARCHAR2(512);
BEGIN
--初始化
fnd_profile.put(‘PRINT_DEBUG‘, ‘Y‘);
dbms_output.enable(10000000);
fa_srvr_msg.init_server_message;
fa_debug_pkg.initialize;
l_asset_hdr_rec.asset_id := 64; --asset_id;
l_asset_hdr_rec.book_type_code := ‘ZZ_FA_BOOK‘;
-- invoice trans
l_inv_trans_rec.transaction_type := ‘INVOICE ADDITION‘;
-- invoice info
l_inv_rec.fixed_assets_cost := 3000;
l_inv_rec.deleted_flag := ‘NO‘;
l_inv_rec.description := ‘TEST NIV‘;
l_inv_rec.unrevalued_cost := 0;
l_inv_rec.create_batch_id := 1000;
--l_inv_rec.payables_code_combination_id := 13528;
l_inv_rec.payables_cost := 2500;
l_inv_rec.payables_units := 1;
l_inv_rec.inv_indicator := 1;
-- rate info for mrc
/*
l_inv_rate_tbl(1).inv_indicator := 1;
l_inv_rate_tbl(1).set_of_books_id := 425;
l_inv_rate_tbl(1).exchange_rate := 50;
*/
-- set up the invoice table
l_inv_tbl(1) := l_inv_rec;
fa_adjustment_pub.do_adjustment(1.0,
fnd_api.g_false,
fnd_api.g_false,
fnd_api.g_valid_level_full,
NULL,
l_return_status,
l_mesg_count,
l_mesg,
l_trans_rec,
l_asset_hdr_rec,
l_asset_fin_rec_adj,
l_asset_fin_rec_new,
l_asset_fin_mrc_tbl_new,
l_inv_trans_rec,
l_inv_tbl,
l_asset_deprn_rec_adj,
l_asset_deprn_rec_new,
l_asset_deprn_mrc_tbl_new,
l_group_reclass_options_rec);
dbms_output.put_line(l_return_status);
IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
fa_debug_pkg.dump_debug_messages(max_mesgs => 0);
l_mesg_count := fnd_msg_pub.count_msg;
IF l_mesg_count > 0 THEN
l_mesg := substr(fnd_msg_pub.get(fnd_msg_pub.g_first,
fnd_api.g_false),
1,
512);
dbms_output.put_line(substr(l_mesg, 1, 255));
FOR i IN 1 .. l_mesg_count - 1 LOOP
l_mesg := substr(fnd_msg_pub.get(fnd_msg_pub.g_next,
fnd_api.g_false),
1,
512);
dbms_output.put_line(substr(l_mesg, 1, 255));
END LOOP;
fnd_msg_pub.delete_msg();
END IF;
ELSE
dbms_output.put_line(‘SUCCESS‘);
dbms_output.put_line(‘THID‘ ||
to_char(l_trans_rec.transaction_header_id));
END IF;
END;
--四、资产删除（限刚创建，无折旧，无报废等资产）
PROCEDURE do_delete IS
l_asset_hdr_rec fa_api_types.asset_hdr_rec_type;
l_return_status VARCHAR2(1);
l_mesg_count NUMBER := 0;
l_mesg_len NUMBER;
l_mesg VARCHAR2(4000);
BEGIN
dbms_output.enable(1000000);
fa_srvr_msg.init_server_message;
-- asset header info
l_asset_hdr_rec.asset_id := 64;
l_asset_hdr_rec.book_type_code := ‘ZZ_FA_BOOK‘;
fa_deletion_pub.do_delete(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_false,
p_validation_level => fnd_api.g_valid_level_full,
x_return_status => l_return_status,
x_msg_count => l_mesg_count,
x_msg_data => l_mesg,
p_calling_fn => NULL,
px_asset_hdr_rec => l_asset_hdr_rec);
l_mesg_count := fnd_msg_pub.count_msg;
IF l_mesg_count > 0 THEN
l_mesg := chr(10) ||
substr(fnd_msg_pub.get(fnd_msg_pub.g_first, fnd_api.g_false),
1,
250);
dbms_output.put_line(l_mesg);
FOR i IN 1 .. (l_mesg_count - 1) LOOP
l_mesg := substr(fnd_msg_pub.get(fnd_msg_pub.g_next,
fnd_api.g_false),
1,
250);
dbms_output.put_line(l_mesg);
END LOOP;
fnd_msg_pub.delete_msg();
END IF;
IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
dbms_output.put_line(‘FAILURE‘);
ELSE
dbms_output.put_line(‘SUCCESS‘);
dbms_output.put_line(‘ASSET_ID‘ || to_char(l_asset_hdr_rec.asset_id));
dbms_output.put_line(‘BOOK: ‘ || l_asset_hdr_rec.book_type_code);
END IF;
END;
--五、资产分配(转移)
PROCEDURE do_transfer IS
l_return_status VARCHAR2(1);
l_msg_count NUMBER := 0;
l_msg_data VARCHAR2(4000);
l_trans_rec fa_api_types.trans_rec_type;
l_asset_hdr_rec fa_api_types.asset_hdr_rec_type;
l_asset_dist_tbl fa_api_types.asset_dist_tbl_type;
l_temp_str VARCHAR2(512);
BEGIN
fnd_profile.put(‘PRINT_DEBUG‘, ‘Y‘);
dbms_output.enable(1000000);
fa_srvr_msg.init_server_message;
fa_debug_pkg.initialize;
-- fill in asset information
l_asset_hdr_rec.asset_id := 49;
l_asset_hdr_rec.book_type_code := ‘ZZ_FA_BOOK‘;
-- transaction date must be filled in if performing
-- prior period transfer
--l_trans_rec.transaction_date_entered := to_date(‘01-JAN-1999 10:54:22‘, ‘dd-mon-yyyy hh24:mi:ss‘);
l_asset_dist_tbl.delete;
/*
fill in distribution data for existing distribution lines
affected by this transfer txn. Note: You need to fill in
only affected distribution lines.
For source distribution, you must fill in either existing
distribution id or 2 columns(expense_ccid,location_ccid) or
3-tuple columns(assigned_to,expense_ccid,and location_ccid)
depending on the makeup of the particular distribution
of the asset.
*/
l_asset_dist_tbl(1).distribution_id := 2007;
l_asset_dist_tbl(1).transaction_units := -1;
/*
either above 2 lines or below 4 lines must be provided
for source distribution:
l_asset_dist_tbl(1).transaction_units := -2;
l_asset_dist_tbl(1).assigned_to := 11;
l_asset_dist_tbl(1).expense_ccid :=15338;
l_asset_dist_tbl(1).location_ccid := 3;
*/
-- fill in dist info for destination distribution
l_asset_dist_tbl(2).transaction_units := 1;
l_asset_dist_tbl(2).assigned_to := 81;
l_asset_dist_tbl(2).expense_ccid := 1013; --15338;
l_asset_dist_tbl(2).location_ccid := 1; --3;
l_trans_rec.who_info.last_updated_by := 1130; --FND_GLOBAL.USER_ID;
l_trans_rec.who_info.last_update_login := 10001; --FND_GLOBAL.LOGIN_ID;
fa_transfer_pub .do_transfer(1.0,
fnd_api.g_false,
fnd_api.g_false,
fnd_api.g_valid_level_full,
NULL,
l_return_status,
l_msg_count,
l_msg_data,
l_trans_rec,
l_asset_hdr_rec,
l_asset_dist_tbl);
IF (l_return_status != fnd_api.g_ret_sts_success) THEN
dbms_output.put_line(‘TRANSFER failed!.‘);
l_msg_count := fnd_msg_pub.count_msg;
IF (l_msg_count > 0) THEN
l_temp_str := substr(fnd_msg_pub.get(fnd_msg_pub.g_first,
fnd_api.g_false),
1,
512);
dbms_output.put_line(‘Error: ‘ || l_temp_str);
FOR i IN 1 .. (l_msg_count - 1) LOOP
l_temp_str := substr(fnd_msg_pub.get(fnd_msg_pub.g_next,
fnd_api.g_false),
1,
512);
dbms_output.put_line(‘Error: ‘ || l_temp_str);
END LOOP;
END IF;
ELSE
dbms_output.put_line(‘TRANSFER completed successfully!‘);
dbms_output.put_line(‘THID = ‘ ||
to_char(l_trans_rec.transaction_header_id));
END IF;
fnd_msg_pub.delete_msg();
END;
--六、资产报废
PROCEDURE do_retirement IS
api_error EXCEPTION;
/* Test asset info */
l_asset_id NUMBER;
l_book_type_code VARCHAR2(15) := ‘ZZ_FA_BOOK‘;
l_user_id NUMBER := 1130; -- USER_ID must properly be set to run calc gain/loss
--/ define local record types /
l_trans_rec fa_api_types.trans_rec_type;
l_dist_trans_rec fa_api_types.trans_rec_type;
l_asset_hdr_rec fa_api_types.asset_hdr_rec_type;
l_asset_retire_rec fa_api_types.asset_retire_rec_type;
l_asset_dist_tbl fa_api_types.asset_dist_tbl_type;
l_subcomp_tbl fa_api_types.subcomp_tbl_type;
l_inv_tbl fa_api_types.inv_tbl_type;
--/ misc info /
l_api_version NUMBER := 1;
l_init_msg_list VARCHAR2(1) := fnd_api.g_false;
l_commit VARCHAR2(1) := fnd_api.g_false;
l_validation_level NUMBER := fnd_api.g_valid_level_full;
l_calling_fn VARCHAR2(80) := ‘Retirement test wrapper‘;
l_return_status VARCHAR2(1) := fnd_api.g_false;
l_msg_count NUMBER := 0;
l_msg_data VARCHAR2(512);
l_count NUMBER;
l_request_id NUMBER;
i NUMBER := 0;
l_temp_str VARCHAR2(512);
l_mesg_count NUMBER;
BEGIN
dbms_output.disable;
dbms_output.enable(1000000);
dbms_output.put_line(‘begin‘);
fa_srvr_msg.init_server_message;
fa_debug_pkg.set_debug_flag(debug_flag => ‘YES‘);
-- Get standard who info
l_request_id := fnd_global.conc_request_id;
l_asset_id := 100014573;
--A530000000104
l_trans_rec.who_info.last_updated_by := 1130;
l_trans_rec.who_info.last_update_login := -1;
l_trans_rec.who_info.last_update_date := SYSDATE;
l_trans_rec.who_info.creation_date := l_trans_rec.who_info.last_update_date;
l_trans_rec.who_info.created_by := l_trans_rec.who_info.last_updated_by;
l_trans_rec.transaction_type_code := NULL; -- this will be determined inside API
l_trans_rec.transaction_date_entered := NULL;
l_asset_hdr_rec.asset_id := l_asset_id;
l_asset_hdr_rec.book_type_code := l_book_type_code;
l_asset_hdr_rec.period_of_addition := NULL;
l_asset_retire_rec.retirement_prorate_convention := NULL; --‘STL‘;
l_asset_retire_rec.date_retired := NULL; -- will be current period by default
l_asset_retire_rec.units_retired := 1;
l_asset_retire_rec.cost_retired := 6266806.97;
-- l_asset_retire_rec.proceeds_of_sale := 0;
-- l_asset_retire_rec.cost_of_removal := 0;
-- l_asset_retire_rec.retirement_type_code := ‘SALE‘;
l_asset_retire_rec.trade_in_asset_id := NULL;
l_asset_retire_rec.calculate_gain_loss := fnd_api.g_true; --特别注意，如果此处为TRUE，则资产只能进行重建，如果FALSE，则报废可以进行UNDO操作
fnd_profile.put(‘USER_ID‘, l_user_id);
l_asset_dist_tbl.delete;
--l_asset_dist_tbl(1).distribution_id := 4003;
fa_retirement_pub.do_retirement(p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
p_commit => l_commit,
p_validation_level => l_validation_level,
p_calling_fn => l_calling_fn,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
px_trans_rec => l_trans_rec,
px_dist_trans_rec => l_dist_trans_rec,
px_asset_hdr_rec => l_asset_hdr_rec,
px_asset_retire_rec => l_asset_retire_rec,
p_asset_dist_tbl => l_asset_dist_tbl,
p_subcomp_tbl => l_subcomp_tbl,
p_inv_tbl => l_inv_tbl);
IF l_return_status = fnd_api.g_false THEN
RAISE api_error;
END IF;
dbms_output.put_line(‘test wrapper: retirement_id: ‘ ||
l_asset_retire_rec.retirement_id);
--commit;
-- Dump Debug messages when run in debug mode to log file
IF (fa_debug_pkg.print_debug) THEN
fa_debug_pkg.write_debug_log;
END IF;
fa_srvr_msg.add_message(calling_fn => l_calling_fn,
NAME => ‘FA_SHARED_END_SUCCESS‘,
token1 => ‘PROGRAM‘,
value1 => ‘RETIREMENT_API‘);
l_mesg_count := fnd_msg_pub.count_msg;
IF (l_mesg_count > 0) THEN
l_temp_str := fnd_msg_pub.get(fnd_msg_pub.g_first, fnd_api.g_false);
dbms_output.put_line(‘dump: ‘ || l_temp_str);
FOR i IN 1 .. (l_mesg_count - 1) LOOP
l_temp_str := fnd_msg_pub.get(fnd_msg_pub.g_next, fnd_api.g_false);
dbms_output.put_line(‘dump: ‘ || l_temp_str);
END LOOP;
ELSE
dbms_output.put_line(‘dump: NO MESSAGE !‘);
END IF;
EXCEPTION
WHEN api_error THEN
ROLLBACK WORK;
fa_srvr_msg.add_message(calling_fn => l_calling_fn,
NAME => ‘FA_SHARED_PROGRAM_FAILED‘,
token1 => ‘PROGRAM‘,
value1 => l_calling_fn);
l_mesg_count := fnd_msg_pub.count_msg;
IF (l_mesg_count > 0) THEN
l_temp_str := fnd_msg_pub.get(fnd_msg_pub.g_first, fnd_api.g_false);
dbms_output.put_line(‘dump: ‘ || l_temp_str);
FOR i IN 1 .. (l_mesg_count - 1) LOOP
l_temp_str := fnd_msg_pub.get(fnd_msg_pub.g_next, fnd_api.g_false);
dbms_output.put_line(‘dump: ‘ || l_temp_str);
END LOOP;
ELSE
dbms_output.put_line(‘dump: NO MESSAGE !‘);
END IF;
END;
--七、撤消资产报废（只能撤消未进行“计算损益”的报废）
PROCEDURE undo_retirement IS
api_error EXCEPTION;
l_retirement_id NUMBER := 2013;
--l_asset_id number := 101027;
--l_book_type_code varchar2(15) := ‘ZBOOK‘;
l_user_id NUMBER := 1130; -- USER_ID must properly be set to run calc gain/loss
--/ define local record types /
l_trans_rec fa_api_types.trans_rec_type;
l_asset_hdr_rec fa_api_types.asset_hdr_rec_type;
l_asset_retire_rec fa_api_types.asset_retire_rec_type;
l_asset_dist_tbl fa_api_types.asset_dist_tbl_type;
l_subcomp_tbl fa_api_types.subcomp_tbl_type;
l_inv_tbl fa_api_types.inv_tbl_type;
-- / misc info /
l_api_version NUMBER := 1;
l_init_msg_list VARCHAR2(1) := fnd_api.g_false;
l_commit VARCHAR2(1) := fnd_api.g_true;
l_validation_level NUMBER := fnd_api.g_valid_level_full;
l_calling_fn VARCHAR2(80) := ‘Retirement test wrapper‘;
l_return_status VARCHAR2(1) := fnd_api.g_false;
l_msg_count NUMBER := 0;
l_msg_data VARCHAR2(512);
l_count NUMBER;
l_request_id NUMBER;
i NUMBER := 0;
l_temp_str VARCHAR2(512);
l_mesg_count NUMBER;
BEGIN
dbms_output.disable;
dbms_output.enable(1000000);
dbms_output.put_line(‘begin‘);
fa_srvr_msg.init_server_message;
fa_debug_pkg.set_debug_flag(debug_flag => ‘YES‘);
-- Get standard who info
l_request_id := fnd_global.conc_request_id;
l_trans_rec.who_info.last_updated_by := -1;
l_trans_rec.who_info.last_update_login := -1;
l_trans_rec.who_info.last_update_date := SYSDATE;
l_trans_rec.who_info.creation_date := l_trans_rec.who_info.last_update_date;
l_trans_rec.who_info.created_by := l_trans_rec.who_info.last_updated_by;
l_trans_rec.transaction_type_code := NULL; -- this will be determined inside API
l_trans_rec.transaction_date_entered := NULL;
-- l_asset_hdr_rec.asset_id := l_asset_id;
-- l_asset_hdr_rec.book_type_code := l_book_type_code;
-- l_asset_hdr_rec.period_of_addition := NULL;
l_asset_retire_rec.retirement_id := l_retirement_id;
fnd_profile.put(‘USER_ID‘, l_user_id);
fa_retirement_pub.undo_retirement(p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
p_commit => l_commit,
p_validation_level => l_validation_level,
p_calling_fn => l_calling_fn,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
,
px_trans_rec => l_trans_rec,
px_asset_hdr_rec => l_asset_hdr_rec,
px_asset_retire_rec => l_asset_retire_rec);
IF l_return_status = fnd_api.g_false THEN
RAISE api_error;
END IF;
COMMIT;
-- Dump Debug messages when run in debug mode to log file
IF (fa_debug_pkg.print_debug) THEN
fa_debug_pkg.write_debug_log;
END IF;
fa_srvr_msg.add_message(calling_fn => l_calling_fn,
NAME => ‘FA_SHARED_END_SUCCESS‘,
token1 => ‘PROGRAM‘,
value1 => ‘RETIREMENT_API‘);
l_mesg_count := fnd_msg_pub.count_msg;
IF (l_mesg_count > 0) THEN
l_temp_str := fnd_msg_pub.get(fnd_msg_pub.g_first, fnd_api.g_false);
dbms_output.put_line(‘dump: ‘ || l_temp_str);
FOR i IN 1 .. (l_mesg_count - 1) LOOP
l_temp_str := fnd_msg_pub.get(fnd_msg_pub.g_next, fnd_api.g_false);
dbms_output.put_line(‘dump: ‘ || l_temp_str);
END LOOP;
ELSE
dbms_output.put_line(‘dump: NO MESSAGE !‘);
END IF;
EXCEPTION
WHEN api_error THEN
ROLLBACK WORK;
fa_srvr_msg.add_message(calling_fn => l_calling_fn,
NAME => ‘FA_SHARED_PROGRAM_FAILED‘,
token1 => ‘PROGRAM‘,
value1 => l_calling_fn);
l_mesg_count := fnd_msg_pub.count_msg;
IF (l_mesg_count > 0) THEN
l_temp_str := fnd_msg_pub.get(fnd_msg_pub.g_first, fnd_api.g_false);
dbms_output.put_line(‘dump: ‘ || l_temp_str);
FOR i IN 1 .. (l_mesg_count - 1) LOOP
l_temp_str := fnd_msg_pub.get(fnd_msg_pub.g_next, fnd_api.g_false);
dbms_output.put_line(‘dump: ‘ || l_temp_str);
END LOOP;
ELSE
dbms_output.put_line(‘dump: NO MESSAGE !‘);
END IF;
END;
--八、报废资产重建
PROCEDURE do_reinstatement IS
api_error EXCEPTION;
--/ Test asset info /
l_retirement_id NUMBER := 2012;
--l_asset_id number := 100837;
--l_book_type_code varchar2(15) := ‘ZBOOK1‘;
l_user_id NUMBER := 1130; -- USER_ID must properly be set to run calc gain/loss
--/ define local record types /
l_trans_rec fa_api_types.trans_rec_type;
l_asset_hdr_rec fa_api_types.asset_hdr_rec_type;
l_asset_retire_rec fa_api_types.asset_retire_rec_type;
l_asset_dist_tbl fa_api_types.asset_dist_tbl_type;
l_subcomp_tbl fa_api_types.subcomp_tbl_type;
l_inv_tbl fa_api_types.inv_tbl_type;
--/ misc info /
l_api_version NUMBER := 1;
l_init_msg_list VARCHAR2(1) := fnd_api.g_false;
l_commit VARCHAR2(1) := fnd_api.g_false;
l_validation_level NUMBER := fnd_api.g_valid_level_full;
l_calling_fn VARCHAR2(80) := ‘Retirement test wrapper‘;
l_return_status VARCHAR2(1) := fnd_api.g_false;
l_msg_count NUMBER := 0;
l_msg_data VARCHAR2(512);
l_count NUMBER;
l_request_id NUMBER;
i NUMBER := 0;
l_temp_str VARCHAR2(512);
l_mesg_count NUMBER;
BEGIN
dbms_output.disable;
dbms_output.enable(1000000);
dbms_output.put_line(‘begin‘);
fa_srvr_msg.init_server_message;
fa_debug_pkg.set_debug_flag(debug_flag => ‘YES‘);
-- Get standard who info
l_request_id := fnd_global.conc_request_id;
l_trans_rec.who_info.last_updated_by := -1;
l_trans_rec.who_info.last_update_login := -1;
l_trans_rec.who_info.last_update_date := SYSDATE;
l_trans_rec.who_info.creation_date := l_trans_rec.who_info.last_update_date;
l_trans_rec.who_info.created_by := l_trans_rec.who_info.last_updated_by;
l_trans_rec.transaction_type_code := NULL; -- this will be determined inside API
l_trans_rec.transaction_date_entered := NULL;
--l_asset_hdr_rec.asset_id := l_asset_id;
--l_asset_hdr_rec.book_type_code := l_book_type_code;
--l_asset_hdr_rec.period_of_addition := NULL;
--l_asset_retire_rec.date_retired := NULL; -- will be current period by default
--l_asset_retire_rec.units_retired := NULL;
l_asset_retire_rec.calculate_gain_loss := fnd_api.g_true; --特别注意，如果此处为TRUE，则资产只能进行重新报废，如果FALSE，则重建可以进行UNDO操作
l_asset_retire_rec.retirement_id := l_retirement_id;
fnd_profile.put(‘USER_ID‘, l_user_id);
l_asset_dist_tbl.delete;
--l_asset_dist_tbl(1).distribution_id := 975;
--l_asset_dist_tbl(1).transaction_units := -1;
--l_asset_dist_tbl(1).units_assigned := null;
--l_asset_dist_tbl(1).assigned_to := null;
--l_asset_dist_tbl(1).expense_ccid := null;
--l_asset_dist_tbl(1).location_ccid := null;
fa_retirement_pub.do_reinstatement(p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
p_commit => l_commit,
p_validation_level => l_validation_level,
p_calling_fn => l_calling_fn,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
,
px_trans_rec => l_trans_rec,
px_asset_hdr_rec => l_asset_hdr_rec,
px_asset_retire_rec => l_asset_retire_rec,
p_asset_dist_tbl => l_asset_dist_tbl,
p_subcomp_tbl => l_subcomp_tbl,
p_inv_tbl => l_inv_tbl);
IF l_return_status = fnd_api.g_false THEN
RAISE api_error;
END IF;
COMMIT;
-- Dump Debug messages when run in debug mode to log file
IF (fa_debug_pkg.print_debug) THEN
fa_debug_pkg.write_debug_log;
END IF;
fa_srvr_msg.add_message(calling_fn => l_calling_fn,
NAME => ‘FA_SHARED_END_SUCCESS‘,
token1 => ‘PROGRAM‘,
value1 => ‘RETIREMENT_API‘);
l_mesg_count := fnd_msg_pub.count_msg;
IF (l_mesg_count > 0) THEN
l_temp_str := fnd_msg_pub.get(fnd_msg_pub.g_first, fnd_api.g_false);
dbms_output.put_line(‘dump: ‘ || l_temp_str);
FOR i IN 1 .. (l_mesg_count - 1) LOOP
l_temp_str := fnd_msg_pub.get(fnd_msg_pub.g_next, fnd_api.g_false);
dbms_output.put_line(‘dump: ‘ || l_temp_str);
END LOOP;
ELSE
dbms_output.put_line(‘dump: NO MESSAGE !‘);
END IF;
EXCEPTION
WHEN api_error THEN
ROLLBACK WORK;
fa_srvr_msg.add_message(calling_fn => l_calling_fn,
NAME => ‘FA_SHARED_PROGRAM_FAILED‘,
token1 => ‘PROGRAM‘,
value1 => l_calling_fn);
l_mesg_count := fnd_msg_pub.count_msg;
IF (l_mesg_count > 0) THEN
l_temp_str := fnd_msg_pub.get(fnd_msg_pub.g_first, fnd_api.g_false);
dbms_output.put_line(‘dump: ‘ || l_temp_str);
FOR i IN 1 .. (l_mesg_count - 1) LOOP
l_temp_str := fnd_msg_pub.get(fnd_msg_pub.g_next, fnd_api.g_false);
dbms_output.put_line(‘dump: ‘ || l_temp_str);
END LOOP;
ELSE
dbms_output.put_line(‘dump: NO MESSAGE !‘);
END IF;
END;
--九、撤消资产重建（限于未进行“计算损益”的重建资产）
PROCEDURE undo_reinstatement IS
api_error EXCEPTION;
/* Test asset info */
l_retirement_id NUMBER := 2011;
--l_asset_id number := 100837;
--l_book_type_code varchar2(15) := ‘ZBOOK1‘;
l_user_id NUMBER := 1001; -- USER_ID must properly be set to run calc gain/loss
/* define local record types */
l_trans_rec fa_api_types.trans_rec_type;
l_asset_hdr_rec fa_api_types.asset_hdr_rec_type;
l_asset_retire_rec fa_api_types.asset_retire_rec_type;
l_asset_dist_tbl fa_api_types.asset_dist_tbl_type;
l_subcomp_tbl fa_api_types.subcomp_tbl_type;
l_inv_tbl fa_api_types.inv_tbl_type;
/* misc info */
l_api_version NUMBER := 1;
l_init_msg_list VARCHAR2(1) := fnd_api.g_false;
l_commit VARCHAR2(1) := fnd_api.g_true;
l_validation_level NUMBER := fnd_api.g_valid_level_full;
l_calling_fn VARCHAR2(80) := ‘Retirement test wrapper‘;
l_return_status VARCHAR2(1) := fnd_api.g_false;
l_msg_count NUMBER := 0;
l_msg_data VARCHAR2(512);
l_count NUMBER;
l_request_id NUMBER;
i NUMBER := 0;
l_temp_str VARCHAR2(512);
l_mesg_count NUMBER;
BEGIN
dbms_output.disable;
dbms_output.enable(1000000);
dbms_output.put_line(‘begin‘);
fa_srvr_msg.init_server_message;
fa_debug_pkg.set_debug_flag(debug_flag => ‘YES‘);
-- Set rollback segment if profile option is set
/*
fnd_profile.get(‘FA_LARGE_ROLLBACK_SEGMENT‘, rbs_name);
IF (rbs_name is not NULL) THEN
sql_stmt := ‘SET TRANSACTION USE ROLLBACK SEGMENT ‘ || rbs_name;
execute immediate sql_stmt;
END IF;
*/
-- Get standard who info
l_request_id := fnd_global.conc_request_id;
l_trans_rec.who_info.last_updated_by := -1;
l_trans_rec.who_info.last_update_login := -1;
l_trans_rec.who_info.last_update_date := SYSDATE;
l_trans_rec.who_info.creation_date := l_trans_rec.who_info.last_update_date;
l_trans_rec.who_info.created_by := l_trans_rec.who_info.last_updated_by;
l_trans_rec.transaction_type_code := NULL; -- this will be determined inside API
l_trans_rec.transaction_date_entered := NULL;
-- l_asset_hdr_rec.asset_id := l_asset_id;
-- l_asset_hdr_rec.book_type_code := l_book_type_code;
-- l_asset_hdr_rec.period_of_addition := NULL;
l_asset_retire_rec.retirement_id := l_retirement_id;
fnd_profile.put(‘USER_ID‘, l_user_id);
fa_retirement_pub.undo_reinstatement(p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
p_commit => l_commit,
p_validation_level => l_validation_level,
p_calling_fn => l_calling_fn,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
px_trans_rec => l_trans_rec,
px_asset_hdr_rec => l_asset_hdr_rec,
px_asset_retire_rec => l_asset_retire_rec);
IF l_return_status = fnd_api.g_false THEN
RAISE api_error;
END IF;
COMMIT;
-- Dump Debug messages when run in debug mode to log file
IF (fa_debug_pkg.print_debug) THEN
fa_debug_pkg.write_debug_log;
END IF;
fa_srvr_msg.add_message(calling_fn => l_calling_fn,
NAME => ‘FA_SHARED_END_SUCCESS‘,
token1 => ‘PROGRAM‘,
value1 => ‘RETIREMENT_API‘);
l_mesg_count := fnd_msg_pub.count_msg;
IF (l_mesg_count > 0) THEN
l_temp_str := fnd_msg_pub.get(fnd_msg_pub.g_first, fnd_api.g_false);
dbms_output.put_line(‘dump: ‘ || l_temp_str);
FOR i IN 1 .. (l_mesg_count - 1) LOOP
l_temp_str := fnd_msg_pub.get(fnd_msg_pub.g_next, fnd_api.g_false);
dbms_output.put_line(‘dump: ‘ || l_temp_str);
END LOOP;
ELSE
dbms_output.put_line(‘dump: NO MESSAGE !‘);
END IF;
EXCEPTION
WHEN api_error THEN
ROLLBACK WORK;
fa_srvr_msg.add_message(calling_fn => l_calling_fn,
NAME => ‘FA_SHARED_PROGRAM_FAILED‘,
token1 => ‘PROGRAM‘,
value1 => l_calling_fn);
l_mesg_count := fnd_msg_pub.count_msg;
IF (l_mesg_count > 0) THEN
l_temp_str := fnd_msg_pub.get(fnd_msg_pub.g_first, fnd_api.g_false);
dbms_output.put_line(‘dump: ‘ || l_temp_str);
FOR i IN 1 .. (l_mesg_count - 1) LOOP
l_temp_str := fnd_msg_pub.get(fnd_msg_pub.g_next, fnd_api.g_false);
dbms_output.put_line(‘dump: ‘ || l_temp_str);
END LOOP;
ELSE
dbms_output.put_line(‘dump: NO MESSAGE !‘);
END IF;
END;
--十、资产重分类
PROCEDURE do_reclass IS
l_trans_rec fa_api_types.trans_rec_type;
l_asset_hdr_rec fa_api_types.asset_hdr_rec_type;
l_asset_cat_rec_new fa_api_types.asset_cat_rec_type;
l_recl_opt_rec fa_api_types.reclass_options_rec_type;
l_return_status VARCHAR2(1);
l_mesg_count NUMBER;
l_mesg VARCHAR2(512);
BEGIN
dbms_output.enable(1000000);
fa_srvr_msg.init_server_message;
l_asset_hdr_rec.asset_id := 1;
l_asset_cat_rec_new.category_id := 1;
l_recl_opt_rec.copy_cat_desc_flag := upper(substr(‘©_category_desc_YES_NO‘,
1,
3));
l_recl_opt_rec.redefault_flag := upper(substr(‘&redefault_deprn_rules_YES_NO‘,
1,
3));
fa_reclass_pub.do_reclass(
-- std parameters
p_api_version => 1.0,
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_false,
p_validation_level => fnd_api.g_valid_level_full,
p_calling_fn => NULL,
x_return_status => l_return_status,
x_msg_count => l_mesg_count,
x_msg_data => l_mesg,
-- api parameters
px_trans_rec => l_trans_rec,
px_asset_hdr_rec => l_asset_hdr_rec,
px_asset_cat_rec_new => l_asset_cat_rec_new,
p_recl_opt_rec => l_recl_opt_rec);
--dump messages
l_mesg_count := fnd_msg_pub.count_msg;
IF l_mesg_count > 0 THEN
l_mesg := chr(10) ||
substr(fnd_msg_pub.get(fnd_msg_pub.g_first, fnd_api.g_false),
1,
250);
dbms_output.put_line(l_mesg);
FOR i IN 1 .. (l_mesg_count - 1) LOOP
l_mesg := substr(fnd_msg_pub.get(fnd_msg_pub.g_next,
fnd_api.g_false),
1,
250);
dbms_output.put_line(l_mesg);
END LOOP;
fnd_msg_pub.delete_msg();
END IF;
IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
dbms_output.put_line(‘FAILURE‘);
ELSE
dbms_output.put_line(‘SUCCESS‘);
dbms_output.put_line(‘THID‘ ||
to_char(l_trans_rec.transaction_header_id));
END IF;
END;
END cux_fa_do_pkg;转载请注明出处:
未经允许不得转载：lxfamn » FA模块的10个API范例
ERP系统配置
相关概念
PLSQL配置连接以及客户端
