详解EBS接口开发之应收款处理

时间：2023-03-09 04:57:02
详解EBS接口开发之应收款处理

参考实例参考：杜春阳 R12应收模块收款API研究

（一）应收款常用标准表简介

1.1   常用标准表

如下表中列出了与应收款处理相关的表和说明：

表名

说明

其他信息

AR_BATCHES_ALL

AR收款批表

AR_BATCH_SOURCES_ALL

AR收款类型表

对应视图

AR_CASH_RECEIPTS_ALL

AR收款表

对应视图

AR_CASH_RECEIPT_HISTORY_ALL

AR收款历史表

对应视图

AR_MISC_CASH_DISTRIBUTIONS_ALL

AR杂项收款分配表

对应视图

AP_BANK_ACCOUNTS_ALL

AR汇款银行

对应视图

AR_RECEIPT_METHODS

AR收款分类

对应视图

1.3 表中字段的大体介绍

1.3   说明

这里只列出了应收款处理相关的大多数常用表。还有一些不经常用到的没有涉及，具体可以参考oracle网站上的

二）应收款处理API

2.1   简介

创建收款

AR_RECEIPT_API_PUB.CREATE_CASH

核销应收INVOICE

AR_RECEIPT_API_PUB.APPLY

创建并同时核销INVOICE

AR_RECEIPT_API_PUB.CREATE_AND_APPLY

取消核销INVOICE

AR_RECEIPT_API_PUB. UNAPPLY

冲销收款

AR_RECEIPT_API_PUB. REVERSE

核销账户

AR_RECEIPT_API_PUB. APPLY_ON_ACCOUNT

撤销核销账户

AR_RECEIPT_API_PUB. UNAPPLY_ON_ACCOUNT

核销活动

AR_RECEIPT_API_PUB. ACTIVITY_APPLICATION

创建杂项收款

AR_RECEIPT_API_PUB. CREATE_MISC

核销其他账户活动

AR_RECEIPT_API_PUB. APPLY_OTHER_ACCOUNT

撤销核销其他账户

AR_RECEIPT_API_PUB. UNAPPLY_OTHER_ACCOUNT

核销现金收款冲销其他收款

AR_RECEIPT_API_PUB. APPLY_OPEN_RECEIPT

取消核销现金收款冲销其他收款

AR_RECEIPT_API_PUB. UNAPPLY_OPEN_RECEIPT

AR_RECEIPT_API_PUB. CREATE_APPLY_ON_ACC

核销到INVOICE行明细

AR_RECEIPT_API_PUB. APPLY_IN_DETAIL

3.1   AR_RECEIPT_API_PUB. Create_cash

这个API一次可以创建一个收款，创建成功后，状态为未核销。

此API一次只能创建一个收款，无法创建收款批。

AR_RECEIPT_API_PUB. 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,

                 -- Receipt 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_id		  OUT NOCOPY NUMBER

                  )

仅包含主要参数

x_return_status    API返回状态

x_msg_count        API返回信息数量

x_msg_data         API返回消息内容

p_currency_code    币种

值来源

select currency_code from fnd_currencies;

p_receipt_number    收款编号

p_receipt_date      收款日期

p_gl_date           入账日期

p_customer_number   客户编号

值来源

select b.account_number

from hz_parties a,

hz_cust_accounts b

where a.party_name = '&Customer_name'

and a.party_id = b.party_id

p_receipt_method_id  收款方法

值来源

select receipt_method_id from ar_receipt_methods;

p_cr_id 返回的收款ID

API成功后，将会把数据提交到AR_RECEIVABLE_APPLICATIONS_ALL中

API代码实例

DECLARE

   l_return_status VARCHAR2(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是USER_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 => 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_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('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;

3.2   AR_RECEIPT_API_PUB.APPLY

API描述

改API一次可以核销一张INVOICE，如果需要核销多张INVOICE，可以循环调用。

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_receipts.cash_receipt_id%TYPEDEFAULT NULL,,

     p_customer_trx_id INra_customer_trx.customer_trx_id%TYPE DEFAULT NULL,

     p_amount_applied  INar_receivable_applications.amount_applied%TYPE DEFAULT NULL,

     x_return_status OUT NOCOPY VARCHAR2,

     x_msg_count OUT NOCOPY NUMBER,

x_msg_data => OUTNOCOPY VARCHAR2);












仅包含主要参数




p_cash_receipt_id  收款ID

值来源

select cash_receipt_id 

fromar_cash_receipts_all 

where org_id =&org_id;

p_customer_trx_id  应收INVOICEID

值来源

select customer_trx_id

fromra_customer_trx_all

where org_id =&org_id;

p_amount_applied   核销金额，非必填，不填系统默认全部或者可核销金额，可不使用这个参数。

x_return_status    API返回状态

x_msg_count        API返回信息数量

x_msg_data         API返回消息内容

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_global.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_receipt_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);

   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 ||'. '||l_msg_data);

      end loop;

   end if;

end;








3.3   AR_RECEIPT_API_PUB.CREATE_AND_APPLY






API描述




此API可以同时创建收款并且核销。相当于同时调用了上面2个过程。但是一次只能创建一个收款和核销第一个应收INVOICE。如果要核销多个应收INVOICE，还是要循环调用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.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_receipts.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%TYPE 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实例




DECLARE

   l_return_status VARCHAR2(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');

   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 => 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-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('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,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;





3.4   AR_RECEIPT_API_PUB.CREATE_AND_APPLY






API描述




该API可以撤销已经核销的应收INVOICE。




API详解


AR_RECEIPT_API_PUB.UNAPPLY

   ( 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 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 NULL,

     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

   );



并非所有参数


p_applied_payment_schedule_id 已核销的应收INVOICE

值来源

select applied_payment_schedule_id

from ar_receivable_applications_all

where 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');

   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_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_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 ||'. '||l_msg_data);

       end loop;

    end if;

END;





3.5   AR_RECEIPT_API.PUB.REVERSE






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 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 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);

并非所有参数

p_cash_receipt_id 未冲销的收款ID

值来源

select cash_receipt_id

from ar_cash_receipts_all

where org_id = &org_id

and status <> 'REV';

p_reversal_category_code 冲销类别

值来源

select lookup_code

from fnd_lookup_values

where lookup_type = 'REVERSAL_CATEGORY_TYPE';

p_reversal_reason_code 冲销原因代码

select lookup_code

from fnd_lookup_values

where lookup_type = 'CKAJST_REASON';





API实例


DECLARE

   l_return_status VARCHAR2(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');

   mo_global.set_policy_context('S', '261');

   fnd_global.apps_initialize(2125, 51260, 280, 0);

   AR_RECEIPT_API_PUB.reverse

   ( 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_msg_data => l_msg_data,

     p_cash_receipt_id => 2565,

     p_reversal_category_code => 'STOP',

     p_reversal_reason_code => 'WRONG INVOICE');

    -- 3) Review the API output

    dbms_output.put_line('Status ' || l_return_status);

    dbms_output.put_line('Message count ' || l_msg_count);

    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 ||'. '||l_msg_data);

       end loop;

    end if;

END;






































posted on

2013-12-09 19:43 
王小航 

阅读(...) 

评论(...) 
编辑 
收藏


相关文章