CREATE OR REPLACE PACKAGE BODY cux_fa_do_pkg IS –一、资产新增 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 := –资产发票信息 l_inv_tbl(1) := l_inv_rec; –发票汇率信息 –资产财务信息 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; — 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 — 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; l_asset_dist_tbl(1).distribution_id := 2007; l_asset_dist_tbl(1).transaction_units := -1; — 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; 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; 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 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; 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 — 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;
![【Access数据库】关于带参数的交叉表查询[图]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)