ORACLEEBSR12应收模块收款API研究

1、R12应收模块收款API研究撰写人:杜春阳撰写日期:2012年1月10日最后修改日期:2012年1月10日版本: 1.0文档控制更改记录日期姓名版本变更参考2011-01-10杜春阳1.0无前期版本审阅姓名职位分发编号.名称地点1212目录文档控制2更改记录2审阅2分发2目录3文档说明4使用对象4参考文档4相关业务汇总5相关业务汇总5代码案例详解7创建收款（AR_RECEIPT_API_PUB.CREATE_CASH）7核销收款（AR_RECEIPT_API_PUB.APPLY）9创建并核销收款（AR_RECEIPT_API_PUB.CREATE_AND_APPLY）11撤销核销收款（AR_R

2、ECEIPT_API_PUB.CREATE_AND_APPLY）13冲销收款（）14文档说明该文档主要介绍应收模块的收款功能相关API。本文仅介绍了部分主要业务的API，对于其他业务请参阅下方ORACLE官方文档。使用对象开发人员参考文档l MetalinkOracle Receivables: Receipts API Overview, Setup, Sample Scripts and Troubleshooting ID 1361075.1相关业务汇总相关业务汇总包名：AR_RECEIPT_API_PUB。黄色为本文介绍编号业务过程名称英文解释1.创建收款CREATE_CASHCrea

3、tes a single cash receipt, as in the case of manually created cash receipts2.核销应收发票APPLYApplies a cash receipt to a particular installment of a debit item. The application can also be a cross currency application.3.创建并同时核销发票CREATE_AND_APPLYCreates a cash receipt and applies it to a specified install

4、ment of a debit item in one pass. Application will fail if the receipt cannot be created.This API is a super-set of CREATE_CASH and APPLY. It provides more input parameters which allow the user to have more control over the type of receipt and application to create. For example, this API takes in a

5、value for p_factor_discount_amount, whereas APPLY API above does not.4.取消核销发票UNAPPLYUnapplies the application of a particular installment of a debit item against the specified cash receipt.5.冲销收款REVERSEReverses the specified receipt.6核销账户APPLY_ON_ACCOUNTCreates an on-account application for a cash r

6、eceipt7撤销核销账户UNAPPLY_ON_ACCOUNTUnapplies the on-account application on the specified receipt.8核销活动ACTIVITY_APPLICATIONApplies to an activity, such as Receipt Write-off or Refund.9创建杂项收款CREATE_MISCCreates a single miscellaneous receipt.10核销其他账户活动APPLY_OTHER_ACCOUNTApplies to other account activities,

7、 such as Claim Investigation (for Trade Management customers only).11撤销核销其他账户UNAPPLY_OTHER_ACCOUNTUnapplies from other account activities, such as Claim Investigation.12不懂APPLY_OPEN_RECEIPTCreates a receipt-to-receipt application (payment netting).13不懂UNAPPLY_OPEN_RECEIPTUnapplies a receipt-to-recei

8、pt application.14不懂CREATE_APPLY_ON_ACCCreates a cash receipt and an on-account application in one pass. Application will fail if the receipt cannot be created.15核销到发票行明细APPLY_IN_DETAILApply a receipt at invoice line level.代码案例详解创建收款（AR_RECEIPT_API_PUB.CREATE_CASH）API描述这个API一次可以创建一个收款，创建成功后，状态为未核销。 该

9、API一次只能创建一个收款，无法创建收款批。API代码详解 PROCEDURE Create_cash( - Standard API parameters. p_api_version IN NUMBER, p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE, p_commit IN VARCHAR2 := FND_API.G_FALSE, x_return_status OUT NOCOPY VARCHAR2, x_msg_count OUT NOCOPY NUMBER, x_msg_data OUT NOCOPY VARCHAR2, - Rece

10、ipt info. parameters p_currency_code IN VARCHAR2 DEFAULT NULL, p_amount IN NUMBER DEFAULT NULL, p_receipt_number IN VARCHAR2 DEFAULT NULL, p_receipt_date IN DATE DEFAULT NULL, p_gl_date IN DATE DEFAULT NULL, p_customer_number IN VARCHAR2 DEFAULT NULL, p_receipt_method_id IN NUMBER DEFAULT NULL, p_cr

11、_id OUT NOCOPY NUMBER )以上并非所有参数，仅包含主要参数，其他参数请自行研究。1. x_return_status API返回状态2. x_msg_count API返回信息数量3. x_msg_data API返回消息内容4. p_currency_code 币种值来源 select currency_code from fnd_currencies;5. p_receipt_number 收款编号6. p_receipt_date 收款日期7. p_gl_date 入账日期8. p_customer_number 客户编号值来源select b.account_num

12、berfrom hz_parties a,hz_cust_accounts bwhere a.party_name = '&Customer_name'and a.party_id = b.party_id9. p_receipt_method_id 收款方法值来源select receipt_method_id from ar_receipt_methods;10. p_cr_id 返回的收款ID11. API成功后，将会把数据提交到AR_RECEIVABLE_APPLICATIONS_ALL中API代码实例DECLARE l_return_status VARCHA

13、R2(1); l_msg_count NUMBER; l_msg_data VARCHAR2(240); l_cash_receipt_id NUMBER; p_count number := 0;BEGIN - 1) Set the applications context mo_global.init('AR');-初始化MOAC mo_global.set_policy_context('S', '261');-261是OU的ID fnd_global.apps_initialize(2125, 51260, 280, 0);-2125是U

14、SER_ID,51260是responsibility_id,280是application_id - 2) Call the API AR_RECEIPT_API_PUB.CREATE_CASH ( p_api_version => 1.0, p_init_msg_list => FND_API.G_TRUE, p_commit => FND_API.G_TRUE, p_validation_level => FND_API.G_VALID_LEVEL_FULL, x_return_status => l_return_status, x_msg_count =

15、> l_msg_count, x_msg_data => l_msg_data, p_currency_code => 'CNY', p_amount => 10000, p_receipt_number => 'TEST20120120', p_receipt_date => '20-01-2012', p_gl_date => '20-01-2012', p_customer_number => 1945, p_receipt_method_id => 2004, p_cr

16、_id => l_cash_receipt_id ); - 3) Review the API output dbms_output.put_line('Status ' | l_return_status); dbms_output.put_line('Cash Receipt id ' | l_cash_receipt_id ); dbms_output.put_line('Message count ' | l_msg_count); if l_msg_count = 1 Then dbms_output.put_line('

17、l_msg_data '|l_msg_data); elsif l_msg_count > 1 Then loop p_count := p_count + 1; l_msg_data := FND_MSG_PUB.Get(FND_MSG_PUB.G_NEXT,FND_API.G_FALSE); if l_msg_data is NULL then exit; end if; dbms_output.put_line('Message ' | p_count |'. '|l_msg_data); end loop; end if; END;核销收款

18、（AR_RECEIPT_API_PUB.APPLY）API描述改API一次可以核销一张发票，如果需要核销多张发票，可以循环调用。API代码详解AR_RECEIPT_API_PUB.APPLY ( p_api_version IN NUMBER, p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE, p_commit IN VARCHAR2 := FND_API.G_FALSE, p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL, p_cash_receipt_id IN ar_cash

19、_receipts.cash_receipt_id%TYPE DEFAULT NULL, p_customer_trx_id IN ra_customer_trx.customer_trx_id%TYPE DEFAULT NULL, p_amount_applied IN ar_receivable_applications.amount_applied%TYPE DEFAULT NULL, x_return_status OUT NOCOPY VARCHAR2, x_msg_count OUT NOCOPY NUMBER,x_msg_data => OUT NOCOPY VARCHAR

20、2); 以上并非所有参数，仅包含主要参数，其他参数请自行研究。1. p_cash_receipt_id 收款ID值来源select cash_receipt_id from ar_cash_receipts_all where org_id = &org_id;2. p_customer_trx_id 应收发票ID值来源select customer_trx_idfrom ra_customer_trx_allwhere org_id = &org_id;3. p_amount_applied 核销金额，非必填，不填系统默认全部或者可核销金额，可不使用这个参数。4. x_ret

21、urn_status API返回状态5. x_msg_count API返回信息数量6. x_msg_data API返回消息内容7. API成功后，将会把数据提交到AR_RECEIVABLE_APPLICATIONS_ALL中API代码实例DECLARE l_return_status varchar2(1); l_msg_count number; l_msg_data varchar2(240); p_count number :=0;BEGIN - 1) Set the applications context mo_global.init('AR'); mo_glob

22、al.set_policy_context('S', '261'); fnd_global.apps_initialize(2125, 51260, 280, 0); - 2) Call the API AR_RECEIPT_API_PUB.APPLY ( p_api_version => 1.0, p_init_msg_list => FND_API.G_TRUE, p_commit => FND_API.G_TRUE, p_validation_level => FND_API.G_VALID_LEVEL_FULL, p_cash_r

23、eceipt_id => 2565, p_customer_trx_id => 5237, x_return_status => l_return_status, x_msg_count => l_msg_count, x_msg_data => l_msg_data); - 3) Review the API output dbms_output.put_line('Status ' | l_return_status); dbms_output.put_line('Message count ' | l_msg_count);

24、if l_msg_count = 1 Then dbms_output.put_line('l_msg_data '| l_msg_data); elsif l_msg_count > 1 Then loop p_count := p_count + 1; l_msg_data := FND_MSG_PUB.Get(FND_MSG_PUB.G_NEXT,FND_API.G_FALSE); if l_msg_data is NULL Then exit; end if; dbms_output.put_line('Message ' | p_count |&

25、#39;. '|l_msg_data); end loop; end if;end;创建并核销收款（AR_RECEIPT_API_PUB.CREATE_AND_APPLY）API描述此API可以同时创建收款并且核销。相当于同时调用了上面2个过程。但是一次只能创建一个收款和核销第一个应收发票。如果要核销多个应收发票，还是要循环调用AR_RECEIPT_API_PUB.APPLY。API详解AR_RECEIPT_API_PUB.create_and_apply ( p_api_version IN NUMBER, p_init_msg_list IN VARCHAR2 := FND_API

26、.G_FALSE, p_commit IN VARCHAR2 := FND_API.G_FALSE, p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL, x_return_status OUT NOCOPY VARCHAR2, x_msg_count OUT NOCOPY NUMBER, x_msg_data OUT NOCOPY VARCHAR2, p_amount IN ar_cash_receipts.amount%TYPE DEFAULT NULL, p_receipt_number IN ar_cash_receip

27、ts.receipt_number%TYPE DEFAULT NULL, p_receipt_date IN ar_cash_receipts.receipt_date%TYPE DEFAULT NULL, p_gl_date IN ar_cash_receipt_history.gl_date%TYPE DEFAULT NULL, p_customer_number IN hz_cust_accounts.account_number%TYPE DEFAULT NULL, p_receipt_method_id IN ar_cash_receipts.receipt_method_id%TY

28、PE DEFAULT NULL, p_customer_trx_id IN ra_customer_trx.customer_trx_id%TYPE DEFAULT NULL, p_cr_id => OUT NOCOPY ar_cash_receipts.cash_receipt_id%TYPE);以上并非所有参数，仅包含主要参数，其他参数请自行研究。该API的参数就是上两个参数的和。这里不做解释。API实例DECLARE l_return_status VARCHAR2(1); l_msg_count NUMBER; l_msg_data VARCHAR2(240); l_cash_r

29、eceipt_id NUMBER; p_count number := 0;BEGIN - 1) Set the applications context mo_global.init('AR'); mo_global.set_policy_context('S', '261'); fnd_global.apps_initialize(2125, 51260, 280, 0); AR_RECEIPT_API_PUB.create_and_apply ( p_api_version => 1.0, p_init_msg_list =>

30、FND_API.G_TRUE, p_commit => FND_API.G_TRUE, p_validation_level => FND_API.G_VALID_LEVEL_FULL, x_return_status => l_return_status, x_msg_count => l_msg_count, x_msg_data => l_msg_data, p_amount => 9999.00, p_receipt_number => 'TEST20120120-1', p_receipt_date => '20

31、-01-2012', p_gl_date => '20-01-2012', p_customer_number => 1945, p_receipt_method_id => 2004, p_customer_trx_id => '5238', p_cr_id => l_cash_receipt_id ); - 3) Review the API output dbms_output.put_line('Status ' | l_return_status); dbms_output.put_line(

32、9;Message count ' | l_msg_count); dbms_output.put_line('Cash Receipt ID ' | l_cash_receipt_id ); if l_msg_count = 1 Then dbms_output.put_line('l_msg_data '| l_msg_data); elsif l_msg_count > 1 Then loop p_count := p_count + 1; l_msg_data := FND_MSG_PUB.Get(FND_MSG_PUB.G_NEXT,FN

33、D_API.G_FALSE); if l_msg_data is NULL Then exit; end if; dbms_output.put_line('Message ' | p_count |'. '|l_msg_data); end loop; end if;END; 撤销核销收款（AR_RECEIPT_API_PUB.CREATE_AND_APPLY）API描述该API可以撤销已经核销的应收发票。API详解AR_RECEIPT_API_PUB.UNAPPLY ( p_api_version IN NUMBER, p_init_msg_list IN

34、VARCHAR2 := FND_API.G_FALSE, p_commit IN VARCHAR2 := FND_API.G_FALSE, p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL, x_return_status OUT NOCOPY VARCHAR2, x_msg_count OUT NOCOPY NUMBER, x_msg_data OUT NOCOPY VARCHAR2, p_cash_receipt_id IN ar_cash_receipts.cash_receipt_id%TYPE DEFAULT NUL

35、L, p_applied_payment_schedule_id IN ar_payment_schedules.payment_schedule_id%TYPE DEFAULT NULL, p_reversal_gl_date IN ar_receivable_applications.reversal_gl_date%TYPE DEFAULT NULL );以上并非所有参数，仅包含主要参数，其他参数请自行研究。该API的参数仅介绍1. p_applied_payment_schedule_id 已核销的应收发票值来源select applied_payment_schedule_idfro

36、m ar_receivable_applications_allwhere cash_receipt_id = &cr_id;API实例DECLARE l_return_status VARCHAR2(1); l_msg_count NUMBER; l_msg_data VARCHAR2(240); p_count number := 0;BEGIN - 1) Set the applications context mo_global.init('AR'); mo_global.set_policy_context('S', '261'

37、); fnd_global.apps_initialize(2125, 51260, 280, 0); AR_RECEIPT_API_PUB.UNAPPLY ( p_api_version => 1.0, p_init_msg_list => FND_API.G_TRUE, p_commit => FND_API.G_TRUE, p_validation_level => FND_API.G_VALID_LEVEL_FULL, x_return_status => l_return_status, x_msg_count => l_msg_count, x_

38、msg_data => l_msg_data, p_cash_receipt_id => 2570, p_applied_payment_schedule_id => 4336, p_reversal_gl_date => '20-01-2012' ); - 3) Review the API output dbms_output.put_line('Status ' | l_return_status); dbms_output.put_line('Message count ' | l_msg_count); if l

39、_msg_count = 1 Then dbms_output.put_line('l_msg_data '| l_msg_data); elsif l_msg_count > 1 Then loop p_count := p_count + 1; l_msg_data := FND_MSG_PUB.Get(FND_MSG_PUB.G_NEXT,FND_API.G_FALSE); if l_msg_data is NULL Then exit; end if; dbms_output.put_line('Message ' | p_count |'

40、. '|l_msg_data); end loop; end if;END;冲销收款（）API描述该API可以冲销指定的收款。API详解AR_RECEIPT_API_PUB.reverse ( p_api_version IN NUMBER, p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE, p_commit IN VARCHAR2 := FND_API.G_FALSE, p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL, x_return_status OUT NOCOP

41、Y VARCHAR2, x_msg_count OUT NOCOPY NUMBER, x_msg_data OUT NOCOPY VARCHAR2, p_cash_receipt_id IN ar_cash_receipts.cash_receipt_id%TYPE DEFAULT NULL, p_reversal_category_code IN ar_cash_receipts.reversal_category%TYPE DEFAULT NULL,p_reversal_reason_code IN ar_cash_receipts.reversal_reason_code%TYPE DEFAULT NULL);以上并非所有参数，仅包含主要参数，其他参数请自行研究。该API的参数仅介绍1. p_cash_receipt_id 未冲销的收款ID值来源select cash_receipt_idfrom ar_cash_receipts_allwhere org_id = &org_idand status <> 'REV'2. p_reversal_category_code 冲销类别值来源select lookup_code from fnd