EBS_AR模块API导入总结

.Oracle ERP最佳技术实践E-Business SuiteOracle 技术总结之应收导入IFACE总结Author:罗正武E-Mail:Creation Date:2015.07.01Last Updated:2015.07.13Document Ref: Version:DRAFT 1A 、Version 2Approvals: Copy Number_Document ControlChange Record3DateAuthorVersionChange RDraft 1aNo Previous DocumentReviewersNamePositionDistributionCopy No.NameLocation1234Note To Holders:If you receive an electronic copy of this document and print it out, please write your name on the equivalent of the cover page, for document control purposes.If you receive a hard copy of this document, please write your name on the front cover, for document control purposes.ContentsDocument Controlii背景介绍21 应收基础简介31.1 模块简介31.2 主要功能和作用31.3 和其他模块的关系41.4 关键设置62 应收事务处理72.1 应收事务处理概述72.2 API创建应收事务处理/贷项通知单72.3 手工录入应收事务处理简介152.4 相关表/重点掌握API173 应收收款/收款注销183.1. 应收收款概述183.2. API创建应收收款/收款注销193.3. 应收收款录入/收款注销创建简介243.4. 相关表/重点掌握API254 应收核销/取消核销264.1. 应收收款核销简介264.2. API创建应收收款核销/取消核销264.3. 应收收款录入创建简介304.4. 相关表/重点掌握API325 应收调整335.1. 应收调整概述335.2. API创建调整335.3. 应收调整创建简介355.4. 相关表/重点掌握API366 常用函数387 参考文档401.Open and Closed Issues for this Deliverable41Open Issues41Closed Issues41. 背景介绍目前项目是中国邮政，现接触到一个应收的大接口,涉及到收款/收款注销,核销/取消核销,应收事务处理发票/贷项通知单,事务处理调整导入标准的.收获颇丰,写个文档记录一下. 1 应收基础简介1.1 模块简介应收模块基本简介1.2 主要功能和作用标准应收的主要功能和作用:1.3 和其他模块的关系 标准的应收和其他模块的关系:1.4 关键设置应收的关键设置作用:我们在调用标准api导入的时候,如果出现设置错误我们可以从以下设置来检查,典型的排错方式就是在标准界面录入导入数据检测问题应收关键设置列表:2 应收事务处理2.1 应收事务处理概述2.2 API创建应收事务处理/贷项通知单注:脚本中涉及到的所有id根据当前中邮项目的设置的映射规则取值.应收事务处理-简单导入发票应收事务处理测试脚本 id均为写死,项目上可以根据需求灵活运用-author bruce-date 20150701DECLARE g_pkg_name VARCHAR2(60) := cux_test_scripts; g_api_type VARCHAR2(60) := cux_test_scripts; l_api_name CONSTANT VARCHAR2(30) := transaction_prc; l_api_version CONSTANT NUMBER := 1.0; p_commit BOOLEAN := FALSE; l_batch_source_rec ar_invoice_api_pub.batch_source_rec_type; l_trx_header_tbl ar_invoice_api_pub.trx_header_tbl_type; l_trx_lines_tbl ar_invoice_api_pub.trx_line_tbl_type; l_trx_dist_tbl ar_invoice_api_pub.trx_dist_tbl_type; l_trx_salescredits_tbl ar_invoice_api_pub.trx_salescredits_tbl_type; x_return_status VARCHAR2(60); x_msg_count NUMBER; x_msg_data VARCHAR2(32676); x_customer_trx_id NUMBER; l_org_id NUMBER;BEGIN - start activity to create savepoint, check compatibility fnd_msg_pub.initialize; fnd_global.apps_initialize(user_id = 1291, resp_id = 20678, resp_appl_id = 222); l_org_id := 88; mo_global.set_policy_context(p_access_mode = S, p_org_id = l_org_id); l_batch_source_rec.batch_source_id := 6001; l_trx_header_tbl(1).cust_trx_type_id := 1221; l_trx_header_tbl(1).trx_header_id := 100; l_trx_header_tbl(1).trx_date := SYSDATE; l_trx_header_tbl(1).gl_date := SYSDATE; l_trx_header_tbl(1).trx_currency := CNY; l_trx_header_tbl(1).bill_to_customer_id := 39211; l_trx_header_tbl(1).term_id := 5; - l_trx_header_tbl(1).org_id := l_org_id; l_trx_lines_tbl(1).trx_header_id := 100; l_trx_lines_tbl(1).trx_line_id := 100; l_trx_lines_tbl(1).line_number := 1; l_trx_lines_tbl(1).description := nvl(NULL, 摘要); l_trx_lines_tbl(1).quantity_invoiced := 1; l_trx_lines_tbl(1).unit_selling_price := 100; l_trx_lines_tbl(1).line_type := LINE; l_trx_dist_tbl(1).trx_dist_id := 100; l_trx_dist_tbl(1).trx_header_id := 100; l_trx_dist_tbl(1).trx_line_id := 100; l_trx_dist_tbl(1).account_class := REV; l_trx_dist_tbl(1).amount := 100; l_trx_dist_tbl(1).acctd_amount := 100; l_trx_dist_tbl(1).percent := 100; l_trx_dist_tbl(1).code_combination_id := 3000; l_trx_dist_tbl(2).trx_dist_id := 100; l_trx_dist_tbl(2).trx_header_id := 100; -l_trx_dist_tbl(2).trx_line_id := 100; l_trx_dist_tbl(2).account_class := REC; l_trx_dist_tbl(2).amount := 100; l_trx_dist_tbl(2).acctd_amount := 100; l_trx_dist_tbl(2).percent := 100; l_trx_dist_tbl(2).code_combination_id := 3000; dbms_output.put_line(bf_x_customer_trx_id: | x_customer_trx_id); ar_invoice_api_pub.create_single_invoice(p_api_version = 1.0, p_init_msg_list = fnd_api.g_false, p_commit = fnd_api.g_false, p_batch_source_rec = l_batch_source_rec, p_trx_header_tbl = l_trx_header_tbl, p_trx_lines_tbl = l_trx_lines_tbl, p_trx_dist_tbl = l_trx_dist_tbl, p_trx_salescredits_tbl = l_trx_salescredits_tbl, x_customer_trx_id = x_customer_trx_id, x_return_status = x_return_status, x_msg_count = x_msg_count, x_msg_data = x_msg_data); dbms_output.put_line(af_x_customer_trx_id: | x_customer_trx_id | -x_return_status: | x_return_status); IF (x_return_status S) THEN RAISE fnd_api.g_exc_error; END IF; dbms_output.put_line(x_customer_trx_id: | x_customer_trx_id); IF x_customer_trx_id IS NULL THEN x_return_status := fnd_api.g_ret_sts_error; cux_api.set_message(p_app_name = CUX, p_msg_name = CUX_COMMON_MSG, p_token1 = TOKEN, p_token1_value = 出现未知错误，没有正确生成发票，请联系系统管理员); RAISE fnd_api.g_exc_error; END IF;EXCEPTION WHEN fnd_api.g_exc_error THEN dbms_output.put_line(g_exc_errorx:); dbms_output.put_line(FND_MSG_BUG: | fnd_msg_pub.get_detail(1, F); WHEN OTHERS THEN dbms_output.put_line(others_exception:); dbms_output.put_line(FND_MSG_BUG: | fnd_msg_pub.get_detail(1, F);END;贷项通知单-简单导入贷项通知单核销应收事务处理发票测试脚本 id均为写死,项目上可以根据需求灵活运用-author bruce-date 20150701DECLARE g_pkg_name VARCHAR2(60) := cux_test_scripts; g_api_type VARCHAR2(60) := cux_test_scripts; l_api_name CONSTANT VARCHAR2(30) := transaction_prc; l_api_version CONSTANT NUMBER := 1.0; p_commit BOOLEAN := FALSE; l_batch_source_rec ar_invoice_api_pub.batch_source_rec_type; l_trx_header_tbl ar_invoice_api_pub.trx_header_tbl_type; l_trx_lines_tbl ar_invoice_api_pub.trx_line_tbl_type; l_trx_dist_tbl ar_invoice_api_pub.trx_dist_tbl_type; l_trx_salescredits_tbl ar_invoice_api_pub.trx_salescredits_tbl_type; x_return_status VARCHAR2(60); x_msg_count NUMBER; x_msg_data VARCHAR2(32676); x_customer_trx_id NUMBER; l_org_id NUMBER; l_cm_app_rec ar_cm_api_pub.cm_app_rec_type; l_out_rec_application_id NUMBER; l_acctd_amount_applied_from NUMBER; l_acctd_amount_applied_to NUMBER;BEGIN - start activity to create savepoint, check compatibility fnd_msg_pub.initialize; fnd_global.apps_initialize(user_id = 1291, resp_id = 20678, resp_appl_id = 222); l_org_id := 88; mo_global.set_policy_context(p_access_mode = S, p_org_id = l_org_id); l_batch_source_rec.batch_source_id := 1004; l_trx_header_tbl(1).cust_trx_type_id := 1031; l_trx_header_tbl(1).trx_header_id := 100; l_trx_header_tbl(1).trx_date := SYSDATE; l_trx_header_tbl(1).gl_date := SYSDATE; l_trx_header_tbl(1).trx_currency := CNY; l_trx_header_tbl(1).bill_to_customer_id := 39211; l_trx_header_tbl(1).org_id := l_org_id; l_trx_lines_tbl(1).trx_header_id := 100; l_trx_lines_tbl(1).trx_line_id := 100; l_trx_lines_tbl(1).line_number := 1; l_trx_lines_tbl(1).description := nvl(NULL, 摘要); l_trx_lines_tbl(1).quantity_invoiced := 1; l_trx_lines_tbl(1).unit_selling_price := -100; l_trx_lines_tbl(1).line_type := LINE; l_trx_dist_tbl(1).trx_dist_id := 100; l_trx_dist_tbl(1).trx_header_id := 100; l_trx_dist_tbl(1).trx_line_id := 100; l_trx_dist_tbl(1).account_class := REV; l_trx_dist_tbl(1).amount := -100; l_trx_dist_tbl(1).acctd_amount := -100; l_trx_dist_tbl(1).percent := 100; l_trx_dist_tbl(1).code_combination_id := 3000; l_trx_dist_tbl(2).trx_dist_id := 100; l_trx_dist_tbl(2).trx_header_id := 100; -l_trx_dist_tbl(2).trx_line_id := 100; l_trx_dist_tbl(2).account_class := REC; l_trx_dist_tbl(2).amount := -100; l_trx_dist_tbl(2).acctd_amount := -100; l_trx_dist_tbl(2).percent := 100; l_trx_dist_tbl(2).code_combination_id := 3000; dbms_output.put_line(bf_x_customer_trx_id: | x_customer_trx_id); ar_invoice_api_pub.create_single_invoice(p_api_version = 1.0, p_init_msg_list = fnd_api.g_false, p_commit = fnd_api.g_false, p_batch_source_rec = l_batch_source_rec, p_trx_header_tbl = l_trx_header_tbl, p_trx_lines_tbl = l_trx_lines_tbl, p_trx_dist_tbl = l_trx_dist_tbl, p_trx_salescredits_tbl = l_trx_salescredits_tbl, x_customer_trx_id = x_customer_trx_id, x_return_status = x_return_status, x_msg_count = x_msg_count, x_msg_data = x_msg_data); dbms_output.put_line(af_x_customer_trx_id: | x_customer_trx_id | -x_return_status: | x_return_status); COMMIT; IF (x_return_status S) THEN RAISE fnd_api.g_exc_error; ELSE dbms_output.put_line(af_commit: | x_customer_trx_id); l_cm_app_rec.cm_customer_trx_id := x_customer_trx_id; l_cm_app_rec.inv_customer_trx_id := 67994; -红字发票10元 针对刚刚创建的发票 l_cm_app_rec.amount_applied := 100; ar_cm_api_pub.apply_on_account(p_api_version = 1, p_init_msg_list = fnd_api.g_true, p_commit = fnd_api.g_false, p_cm_app_rec = l_cm_app_rec, x_return_status = x_return_status, x_msg_count = x_msg_count, x_msg_data = x_msg_data, x_out_rec_application_id = l_out_rec_application_id, x_acctd_amount_applied_from = l_acctd_amount_applied_from, x_acctd_amount_applied_to = l_acctd_amount_applied_to, p_org_id = l_org_id); dbms_output.put_line(apply_on_account_x_return_status: | x_return_status); dbms_output.put_line(l_out_rec_application_id: | l_out_rec_application_id); IF (x_return_status S) THEN RAISE fnd_api.g_exc_error; END IF; END IF;EXCEPTION WHEN fnd_api.g_exc_error THEN dbms_output.put_line(g_exc_errorx:); dbms_output.put_line(FND_MSG_BUG: | fnd_msg_pub.get_detail(1, F); WHEN OTHERS THEN dbms_output.put_line(others_exception:); dbms_output.put_line(FND_MSG_BUG: | fnd_msg_pub.get_detail(1, F);END; 2.3 手工录入应收事务处理简介头信息:行信息:分配信息:2.4 相关表/重点掌握API应收事务处理表: ra_customer_trx_all重点掌握API: ar_invoice_api_pub.create_single_invoice/ ar_cm_api_pub.apply_on_account3 应收收款/收款注销3.1. 应收收款概述收款简介:收款业务流程:3.2. API创建应收收款/收款注销API创建收款:-简单导入收款测试脚本 id均为写死,项目上可以根据需求灵活运用-author bruce-date 20150701DECLARE l_api_name CONSTANT VARCHAR2(30) := receipt_prc; l_api_version CONSTANT NUMBER := 1.0; x_cash_receipt_id NUMBER; l_attribute_rec ar_receipt_api_pub.attribute_rec_type; l_org_id NUMBER; x_return_status VARCHAR2(60); x_msg_count NUMBER; g_pkg_name VARCHAR2(60) := cux_test_scripts; g_api_type VARCHAR2(60) := cux_test_scripts; x_msg_data VARCHAR2(32676);BEGIN fnd_msg_pub.initialize; l_org_id := 88; mo_global.set_policy_context(p_access_mode = S, p_org_id = l_org_id); fnd_global.apps_initialize(user_id = 1291, resp_id = 20678, resp_appl_id = 222); mo_global.set_policy_context(p_access_mode = S, p_org_id = l_org_id); ar_receipt_api_pub.create_cash(p_api_version = 1.0, p_init_msg_list = F, p_receipt_number = 20150714, p_receipt_method_id = 10001, p_currency_code = CNY, p_amount = 1000, p_receipt_date = SYSDATE, p_gl_date = SYSDATE, p_maturity_date = SYSDATE, p_customer_id = 8905, p_customer_site_use_id = 11117, p_remittance_bank_account_id = 10033, p_attribute_rec = l_attribute_rec, p_cr_id = x_cash_receipt_id, x_return_status = x_return_status, x_msg_count = x_msg_count, x_msg_data = x_msg_data); dbms_output.put_line(x_cash_receipt_id: | x_cash_receipt_id); IF (x_return_status S) THEN RAISE fnd_api.g_exc_error; END IF;EXCEPTION WHEN fnd_api.g_exc_error THEN dbms_output.put_line(g_exc_errorx:); dbms_output.put_line(FND_MSG_BUG: | fnd_msg_pub.get_detail(1, F); WHEN OTHERS THEN dbms_output.put_line(others_exception:); dbms_output.put_line(FND_MSG_BUG: | fnd_msg_pub.get_detail(1, F);END; API收款注销:-简单导入收款注销测试脚本 id均为写死,项目上可以根据需求灵活运用-author bruce-date 20150701DECLARE l_org_id NUMBER; x_return_status VARCHAR2(60); x_msg_count NUMBER; g_pkg_name VARCHAR2(60) := cux_test_scripts; g_api_type VARCHAR2(60) := cux_test_scripts; x_msg_data VARCHAR2(32676); l_api_name CONSTANT VARCHAR2(30) := act_app_prc; l_api_version CONSTANT NUMBER := 1.0; l_receivable_application_id NUMBER; l_attribute_rec ar_receipt_api_pub.attribute_rec_type; l_global_att_rec ar_receipt_api_pub.global_attribute_rec_type; l_application_ref_type ar_receivable_applications.application_ref_type%TYPE; l_application_ref_id ar_receivable_applications.application_ref_id%TYPE; l_application_ref_num ar_receivable_applications.application_ref_num%TYPE; l_secondary_application_ref_id ar_receivable_applications.secondary_application_ref_id%TYPE; CURSOR receipts_cur IS SELECT cr.cash_receipt_id, cr.currency_code, cux_ar_sd_daily_report_utl.get_cash_unapp_amount(cr.cash_receipt_id) cash_unapp_amount, _id, cr.pay_from_customer customer_id, cr.customer_site_use_id FROM ar_cash_receipts cr; l_amount NUMBER; l_app_amount NUMBER; l_cash_sum NUMBER;BEGIN fnd_msg_pub.initialize; l_org_id := 88; mo_global.set_policy_context(p_access_mode = S, p_org_id = l_org_id); fnd_global.apps_initialize(user_id = 1291, resp_id = 20678, resp_appl_id = 222); mo_global.set_policy_context(p_access_mode = S, p_org_id = l_org_id); l_amount := 1000; l_app_amount := 1000; ar_receipt_api_pub.activity_application(p_api_version = 1.0, p_commit = fnd_api.g_false, p_validation_level = fnd_api.g_valid_level_full, p_cash_receipt_id = 49145, -注销收款的收款id p_amount_applied = l_app_amount, p_applied_payment_schedule_id = -3, p_receivables_trx_id = 5001, p_apply_date = SYSDATE, p_apply_gl_date = SYSDATE, p_application_ref