当前位置: 首页 > 工具软件 > invoice > 使用案例 >

详解EBS接口开发之应收INVOICE导入

黄啸
2023-12-01

(一)应收INVOICE常用标准表简介

1.1   常用标准表

如下表中列出了与应收INVOICE导入相关的表和说明:

表名

说明

其他信息

RA_BATCH_SOURCES_ALL

AR事务处理来源表

 

 

 

RA_BATCHES_ALL

AR事务处理批表

 

 

对应视图

RA_CUST_TRX_TYPES_ALL

AR事务处理类型

 

 

对应视图

RA_CUSTOMER_TRX_ALL

AR事务处理题头表

 

 

对应视图

RA_CUSTOMER_TRX_LINES_ALL

AR事务处理明细表

 

 

对应视图

RA_CUST_TRX_LINE_GL_DIST_ALL

AR事务处理分配表

 

 

对应视图

RA_CUST_TRX_LINE_SALESREPS_ALL

AR事务处理销售业绩表

 

 

对应视图

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

1.2  接口表大体介绍

如下表中列出了与应收INVOICE导入相关的接口表和说明:

表名

说明

其他信息

ra_interface_lines_all

 

 

 

 

ra_interface_salescredits_all

 

 

 

 

ra_interface_distributions

 

 

 

 

ra_interface_errors_all

错误信息表

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

1.3   说明

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

 

(二)应收INVOICE导入组成

应收INVOICE导入主要包括:INVOICE头信息,INVOICE行信息,INVOICE行税信息,INVOICE行分配信息

 

2.1   应收INVOICE导入组成

INVOICE头信息导入:导入INVOICE头信息

INVOICE行信息导入:导入INVOICE行具体信息

INVOICE行税信息导入:导入INVOICE行税信息

INVOICE行分配信息导入:INVOICE行分配信息

(三)应收INVOICE导入顺序及流程

3.1   应收INVOICE导入顺序

由于数据的先后关联依赖关系,同一供应收INVOICE导入必须按照一定的顺序进行:先导头信息,再行信息,接着导入行发运信息。最后发运的分配等信息。

 

3.2   应收INVOICE导入流程

应收INVOICE导入流程:

第一步:创建需要导入应收INVOICE的中间表,字段上提供供应收INVOICE相关信息。

第二步:向中间表中插入需要导入的应收INVOICE信息,验证需要插入的数据是否满足应收INVOICE信息规则,去除不合规范的数据。

第三步:对在第二部中验证及转化翻译的数据插入到接口中。

第四步:提交并发请求导入应收INVOICE信息。

 

(四)应收INVOICE导入API研究

3.1   应收INVOICE导入api

AR_INVOICE_API_PUB.create_invoice(
    p_api_version           IN      	NUMBER,
    p_init_msg_list         IN      	VARCHAR2 := FND_API.G_FALSE,
    p_commit                IN      	VARCHAR2 := FND_API.G_FALSE,
    p_batch_source_rec      IN      	batch_source_rec_type DEFAULT NULL,
    p_trx_header_tbl        IN      	trx_header_tbl_type,
    p_trx_lines_tbl         IN      	trx_line_tbl_type,
    p_trx_dist_tbl          IN          trx_dist_tbl_type,
    p_trx_salescredits_tbl  IN          trx_salescredits_tbl_type,
    x_return_status         OUT NOCOPY  VARCHAR2,
    x_msg_count             OUT NOCOPY  NUMBER,
    x_msg_data              OUT NOCOPY  VARCHAR2)




AR_INVOICE_API_PUB.CREATE_SINGLE_INVOICE(
    p_api_version           IN  NUMBER,
    p_init_msg_list         IN  VARCHAR2 := FND_API.G_FALSE,
    p_commit                IN  VARCHAR2 := FND_API.G_FALSE,
    p_batch_source_rec      IN  batch_source_rec_type DEFAULT NULL,
    p_trx_header_tbl        IN  trx_header_tbl_type,
    p_trx_lines_tbl         IN  trx_line_tbl_type,
    p_trx_dist_tbl          IN  trx_dist_tbl_type,
    p_trx_salescredits_tbl  IN  trx_salescredits_tbl_type,
    p_trx_contingencies_tbl IN  trx_contingencies_tbl_type,
    x_customer_trx_id       OUT NOCOPY  NUMBER,
    x_return_status         OUT NOCOPY  VARCHAR2,
    x_msg_count             OUT NOCOPY  NUMBER,
    x_msg_data              OUT NOCOPY  VARCHAR2);


 

3.2   应收INVOICE导入api说明

create_invoice

Use the CREATE_INVOICE procedure to createmultiple invoices in a batch. The

procedure returns a global record typestructure which contains the batch_id to

retrieve the necessary data from thetransaction tables. The structure is defined in

the package specification of ar_invoice_api_pub.Please refer to Example for

Creating Multiple Invoices in a Batch, page6-17for usage.

CREATE_SINGLE_INVOICE

Use the CREATE_SINGLE_INVOICE procedure tocreate a single invoice. The

procedure returns customer_trx_id as an outparameter. Please refer to Example for

Creating a Single Invoice, page 6-19forusage

 

3.3   应收INVOICE导入api简单使用范例

Creating MultipleInvoices

1.  DECLARE
l_return_status varchar2(1);
l_msg_count number;
l_msg_data varchar2(2000);
l_batch_id number; 
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;
CURSOR cBatch IS
select customer_trx_id 
from ra_customer_trx_all
where batch_id = l_batch_id;
CURSOR cValidTxn IS
SELECT trx_header_id
From ar_trx_header_gt
WHERE trx_header_id not in (
SELECT trx_header_id
FROM ar_trx_errors_gt);
2.  BEGIN
1.  Set applications context if not already set. 
fnd_global.apps_initialize(1318, 50559, 222,0);
2.  Populate header information.
l_trx_header_tbl(1).trx_header_id := 101;
l_trx_header_tbl(1).trx_number := 'Test Invoice API';
l_trx_header_tbl(1).bill_to_customer_id := 1006;
l_trx_header_tbl(1).cust_trx_type_id := 2376; 
3.  Populate batch source information.
l_batch_source_rec.batch_source_id := 1188; 
4.  Populate line 1 information.
l_trx_lines_tbl(1).trx_header_id := 101;
l_trx_lines_tbl(1).trx_line_id := 101;
l_trx_lines_tbl(1).line_number := 1;
l_trx_lines_tbl(1).memo_line_id := 8;
l_trx_lines_tbl(1).quantity_invoiced := 10;
l_trx_lines_tbl(1).unit_selling_price := 12;
l_trx_lines_tbl(1).line_type := 'LINE';
6-18   Oracle ReceivablesReference Guide
5.  Populate line 2 information.
l_trx_lines_tbl(2).trx_header_id := 101;
l_trx_lines_tbl(2).trx_line_id := 102;
l_trx_lines_tbl(2).line_number := 2;
l_trx_lines_tbl(2).description := 'Test';
l_trx_lines_tbl(2).quantity_invoiced := 12;
l_trx_lines_tbl(2).unit_selling_price := 12;
l_trx_lines_tbl(2).line_type := 'LINE';
6.  Populate freight information and link it to line 1.
l_trx_lines_tbl(3).trx_header_id := 101;
l_trx_lines_tbl(3).trx_line_id := 103;
l_trx_lines_tbl(3).link_to_trx_line_id := 101;
l_trx_lines_tbl(3).line_number := 1;
l_trx_lines_tbl(3).line_type := 'FREIGHT';
l_trx_lines_tbl(3). amount := 25; 
7.  Call the invoice api to create multiple invoices in a batch.
AR_INVOICE_API_PUB.create_invoice(
p_api_version => 1.0,
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_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data); 
IF l_return_status = fnd_api.g_ret_sts_error OR 
l_return_status = fnd_api.g_ret_sts_unexp_error THEN
dbms_output.put_line('unexpected errors found!');
ELSE
8.  Check if there are record exist in error table. If no records exist for a 
trx_header_id, then only Invoice will create in the system; otherwise not.
For cValidTxnRec IN cvalidTxn
loop
IF (ar_invoice_api_pub.g_api_outputs.batch_id IS NOT NULL)
THEN
dbms_output.put_line('Invoice(s) suceessfully created!') ;
dbms_output.put_line('Batch ID: ' || 
ar_invoice_api_pub.g_api_outputs.batch_id);
l_batch_id := ar_invoice_api_pub.g_api_outputs.batch_id;
9.  To see all customer_trx_id for this batch:
Invoice Creation API User Notes    6-19
for cBatchRec in cBatch
loop
dbms_output.put_line ( 'Cust Trx Id '|| 
cBatchRec.customer_trx_id );
end loop; 
ELSE
dbms_output.put_line('Errors found!');
END IF;
End loop;
END IF;
END;
/
10. See all the validation errors.
SET LINESIZE 200
COLUMN trx_header_id HEADING 'Header ID'
COLUMN trx_line_id HEADING 'Line ID'
COLUMN error_message HEADING 'Message'
COLUMN invalid_value HEADING 'Invalid Value'
COLUMN trx_header_id FORMAT 9999999
COLUMN trx_line_id FORMAT 9999999
COLUMN error_message FORMAT a30
COLUMN invalid_value FORMAT a20
SELECT trx_header_id, trx_line_id, error_message, invalid_value
FROM ar_trx_errors_gt;


 

Creating a SingleInvoice

1.  DECLARE
l_return_status varchar2(1);
l_msg_count number;
l_msg_data varchar2(2000);
l_batch_id number; 
l_cnt number := 0;
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.
l_customer_trx_id number;
2.  BEGIN
1.  Set applications context if not already set. 
fnd_global.apps_initialize(1318, 50559, 222,0);
2.  Populate header information.
6-20   Oracle ReceivablesReference Guide
l_trx_header_tbl(1).trx_header_id := 101;
l_trx_header_tbl(1).trx_number := 'Test Invoice API';
l_trx_header_tbl(1).bill_to_customer_id := 1006;
l_trx_header_tbl(1).cust_trx_type_id := 2376; 
3.  Populate batch source information.
l_batch_source_rec.batch_source_id := 1188; 
4.  Populate line 1 information.
l_trx_lines_tbl(1).trx_header_id := 101;
l_trx_lines_tbl(1).trx_line_id := 101;
l_trx_lines_tbl(1).line_number := 1;
l_trx_lines_tbl(1).memo_line_id := 8;
l_trx_lines_tbl(1).quantity_invoiced := 10;
l_trx_lines_tbl(1).unit_selling_price := 12;
l_trx_lines_tbl(1).line_type := 'LINE';
5.  Populate line 2 information.
l_trx_lines_tbl(2).trx_header_id := 101;
l_trx_lines_tbl(2).trx_line_id := 102;
l_trx_lines_tbl(2).line_number := 2;
l_trx_lines_tbl(2).description := 'Test';
l_trx_lines_tbl(2).quantity_invoiced := 12;
l_trx_lines_tbl(2).unit_selling_price := 12;
l_trx_lines_tbl(2).line_type := 'LINE';
6.  Populate freight information and link it to line 1.
l_trx_lines_tbl(3).trx_header_id := 101;
l_trx_lines_tbl(3).trx_line_id := 103;
l_trx_lines_tbl(3).link_to_trx_line_id := 101;
l_trx_lines_tbl(3).line_number := 1;
l_trx_lines_tbl(3).line_type := 'FREIGHT';
l_trx_lines_tbl(3). amount := 25; 
7.  Call the invoice api to create multiple invoices in a batch.
AR_INVOICE_API_PUB.create_single_invoice(
p_api_version => 1.0,
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 => l_customer_trx_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data); 
IF l_return_status = fnd_api.g_ret_sts_error OR 
l_return_status = fnd_api.g_ret_sts_unexp_error THEN
dbms_output.put_line('unexpected errors found!');
ELSE
8.  Check whether any record exist in error table
Invoice Creation API User Notes    6-21
SELECT count(*)
Into cnt
From ar_trx_errors_gt;
IF cnt = 0
THEN
dbms_output.put_line ( 'Customer Trx id '|| l_customer_trx_id);
ELSE
dbms_output.put_line ( 'Transaction not Created, Please check 
ar_trx_errors_gt table'); 
END IF;
END;
/
9.  See all the validation errors. 
SET LINESIZE 200
COLUMN trx_header_id HEADING 'Header ID'
COLUMN trx_line_id HEADING 'Line ID'
COLUMN error_message HEADING 'Message'
COLUMN invalid_value HEADING 'Invalid Value'
COLUMN trx_header_id FORMAT 9999999
COLUMN trx_line_id FORMAT 9999999
COLUMN error_message FORMAT a30
COLUMN invalid_value FORMAT a20
SELECT trx_header_id, trx_line_id, error_message, invalid_value
FROM ar_trx_errors_gt;


 

3.4   应收INVOICE导入api使用说明

对于API使用参数必输参数参考上面例子就可以知道,应收INVOICE行类型比较特殊有LINE类型,TAX类型等当行类型不同时各个字段的要求并不相同,具体使用参照120arrg.pdf。插入税行的时候行类型为TAX

在将中间表中数据向标准表中导入时,需要检验这条应收INVOICE信息是否已经在系统中存在,这些检验要在程序中进行,并根据检验的结果进行导入或者更新

4.1   INVOICE号信息检验

SELECT COUNT(*)

        INTO v_count_sys

        FROM ra_customer_trx_all a

       WHERE a.trx_number =rec_oa_ar_invoice.apply_number;

      IF v_count_sys <> 0 THEN

        cux_conc_utl.log_msg('此INVOICE号系统中已存在');

        h_error_msg  := '此INVOICE号系统中已存在!';

        h_error_flag := 'Y';

        GOTO validate_header_end;

      END IF;


 

 

六)参考实例程序

5.1  接口表一些重要字段

1.参数定义
--调用系统api需要的标准参数(这是single的Multiple的还需要多加一个)
    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; --提交并发请求需要 


2.处理程序
FOR rec_trx_header IN cur_trx_header LOOP
      --头循环
      cux_conc_utl.log_msg('进入pvt,调用pvt。do_arFOR rec_trx_header IN cur_trx_header LOOP:');
      cux_log.debug('rec_trx_header loop begin:');
      dbms_output.put_line('rec_trx_header loop begin:');
      l_trx_count := 0; --初始化
      --按照客户和币种进行分组
      -- FOR rec_ar_group IN cur_ar_group(x_header_id => rec_trx_header.header_id) LOOP
      /*IF l_debug = 'Y' THEN
        cux_log.debug('rec_ar_group loop begin:');
        dbms_output.put_line('rec_ar_group loop begin:');
      END IF; --IF l_debug ='Y' THEN*/
      l_header_count := l_header_count + 1; --需要创建成功的INVOICE计数
      l_trx_count    := l_trx_count + 1;
      --参数初始化
      l_return_status    := NULL;
      l_msg_count        := NULL;
      l_msg_data         := NULL;
      l_batch_id         := NULL;
      l_cnt              := 0;
      l_batch_source_rec := NULL;
      l_trx_header_tbl.delete; --清空
      l_trx_lines_tbl.delete; --清空
      l_trx_dist_tbl.delete; --清空
      l_trx_salescredits_tbl.delete; --清空
      x_customer_trx_id := NULL;
    
      l_line_count := 0;
      -- l_header_count      :=0;
      x_cust_site_use_id  := NULL;
      x_cust_acct_site_id := NULL;
      l_project_num       := NULL;
      l_line_num          := 0;
      x_payment_term_id   := NULL; --付款条件
    
      --设置多组织访问控制
      mo_global.init('AR');
      mo_global.set_policy_context('S', rec_trx_header.org_id);
      cux_conc_utl.log_msg('进入pvt,调用pvt。rec_trx_header.org_id ' ||
                           rec_trx_header.org_id);
      IF rec_trx_header.source_code = 'INV' THEN
        v_batch_name := 'INV INTERCOMPANY';
      ELSIF rec_trx_header.source_code IN ('OA', 'OMS') THEN
        v_batch_name := rec_trx_header.source_code;
      END IF;
      l_batch_source_rec.batch_source_id := get_batch_id(p_batch_name => v_batch_name,
                                                         p_org_id     => rec_trx_header.org_id); --redo
    
      --给参数l_trx_header_tbl赋值 这里写死了应该把1换成count
      l_trx_header_tbl(l_header_count).trx_header_id := rec_trx_header.header_id; --头id
      l_trx_header_tbl(l_header_count).org_id := rec_trx_header.org_id; --OU id 
      /*IF  rec_trx_header.source_code IN ('OA', 'OMS') THEN --当 v_batch_name := 'Intercompany';时系统自动填充事务处理编号
         l_trx_header_tbl(l_header_count).trx_number := rec_trx_header.trx_number; --(事务处理编号?)
      END IF;*/
      l_trx_header_tbl(l_header_count).trx_number := rec_trx_header.trx_number; --(事务处理编号?)
      l_trx_header_tbl(l_header_count).bill_to_customer_id := rec_trx_header.bill_to_customer_id; --收单客户id
      l_trx_header_tbl(l_header_count).bill_to_site_use_id := rec_trx_header.bill_to_site_use_id; --主要手单方地点用途
      l_trx_header_tbl(l_header_count).bill_to_address_id := rec_trx_header.bill_to_address_id; --收单地点id--不需要?
      --cux_log.debug('rec_ar_group.customer_id:' ||rec_ar_group.customer_id);
      l_trx_header_tbl(l_header_count).sold_to_customer_id := rec_trx_header.sold_to_customer_id; --采购方
     
      l_trx_header_tbl(l_header_count).cust_trx_type_id := rec_trx_header.cust_trx_type_id; --事务处理类型id
      
      l_trx_header_tbl(l_header_count).term_id := rec_trx_header.term_id; --付款条件id
      --事务处理日期
      --INVOICE          
--cux_log.debug('rec_trx_header.gl_date:' ||rec_trx_header.gl_date);
      l_trx_header_tbl(l_header_count).gl_date := rec_trx_header.gl_date; --GL日期
      l_trx_header_tbl(l_header_count).trx_date := rec_trx_header.trx_date; --事务处理日期
      --参考字段(此项目用于INVOICE编号)
      l_trx_header_tbl(l_header_count).reference_number := rec_trx_header.reference_number;
      --销售人员
      l_trx_header_tbl(l_header_count).primary_salesrep_id := -3; --rec_trx_header.primary_salesrep_id; --销售人员id (取销售订单头的销售员)
      --汇率
      l_trx_header_tbl(l_header_count).exchange_date := rec_trx_header.conversion_date;
      l_trx_header_tbl(l_header_count).exchange_rate_type := rec_trx_header.conversion_type;
      l_trx_header_tbl(l_header_count).exchange_rate := rec_trx_header.conversion_rate;
      
      --收入类型  
      --l_trx_header_tbl(l_header_count).interface_header_context := 'CUX_SERVICE_INVOICE'; --INVOICE行弹性域上下文:服务结算INVOICE                             
      l_trx_header_tbl(l_header_count).attribute1 := rec_trx_header.rev_type;
      --票种
      l_trx_header_tbl(l_header_count).attribute2 := rec_trx_header.tax_type;
      --INVOICE金额
      l_trx_header_tbl(l_header_count).attribute3 := rec_trx_header.invoice_amount;
      --摘要
      l_trx_header_tbl(l_header_count).attribute4 := rec_trx_header.remark; --interface_header_attribute1
      l_trx_header_tbl(l_header_count).interface_header_attribute1 := rec_trx_header.trx_number;
      
      --cux_conc_utl.log_msg('进入pvt,调用pvt。do_ar XXX '|| attribute3);
      -- cux_conc_utl.log_msg('进入pvt,调用pvt。do_ar XXXXXX '|| attribute3);
      --追溯字段(将TRX_HEADER_ID写入到RA_CUSTOMER_TRX_ALL)
      --头信息处理完毕,处理行信息(在分组循环内处理)
      --应收INVOICE(行)       
      FOR rec_trx_line IN cur_trx_line(x_header_id => rec_trx_header.header_id) LOOP
        --cux_log.debug('rec_trx_line loop begin:');
        cux_conc_utl.log_msg('进入pvt,调用pvt。do_ar FOR rec_trx_line IN cur_trx_line(x_header_id => rec_trx_header.header_id) LOOP:');
        l_line_count    := l_line_count + 1; --一个头多个行
        l_salesrep_id   := NULL; --销售人员id
        l_item_uom_code := NULL; --物料主单位
        l_tax_rate      := NULL;
        l_line_num      := l_line_num + 1;
        --INVOICE行信息
        l_trx_lines_tbl(l_line_count).trx_header_id := rec_trx_line.header_id; --行头id
        l_trx_lines_tbl(l_line_count).trx_line_id := rec_trx_line.line_id; --行id
        l_trx_lines_tbl(l_line_count).line_type := 'LINE'; --rec_trx_line.line_type; --行类型
        l_trx_lines_tbl(l_line_count).amount := rec_trx_line.aomunt; --收入金额
        -- l_trx_lines_tbl(l_line_count).memo_line_id :=1;
        l_trx_lines_tbl(l_line_count).line_number := l_line_num; --行号
        -- l_trx_lines_tbl(l_line_count).sales_order := rec_trx_line.SALES_ORDER; --销售订单编号
        -- l_trx_lines_tbl(l_line_count).sales_order_date := rec_trx_line.SALES_ORDER_DATE; --销售订单日期
        l_trx_lines_tbl(l_line_count).quantity_invoiced := rec_trx_line.quantity_invoiced; --数量为1
        --IF p_ar_type_code = 'INV' THEN
        --INVOICE
        l_trx_lines_tbl(l_line_count).unit_selling_price := rec_trx_line.unit_selling_price; --实际应收金额
        -- ELSIF p_ar_type_code = 'CM' THEN
        --贷项通知单
        --l_trx_lines_tbl(l_line_count).unit_selling_price := (-1) *rec_trx_line.actual_ar_amount; --实际应收金额
      
        -- END IF; --IF l_type_code = 'INV' THEN 
      
        -- l_trx_lines_tbl(l_line_count).inventory_item_id := rec_trx_line.INVENTORY_ITEM_ID;
        --l_trx_lines_tbl(l_line_count).warehouse_id := rec_trx_line.WAREHOUSE_ID;
        l_trx_lines_tbl(l_line_count).description := 'test'; --rec_trx_line.line_description;
        --主物料
        -- l_trx_lines_tbl(l_line_count).uom_code := rec_trx_line.uom_code; --单位
        --l_trx_lines_tbl(l_line_count).amount := ROUND(rec_trx_line.amount,2);
        l_trx_lines_tbl(l_line_count).link_to_trx_line_id := rec_trx_line.link_to_trx_line_id;
        
        --cux_conc_utl.log_msg('进入pvt,调用pvt。do_ar XXX '|| attribute3);
        l_trx_lines_tbl(l_line_count).interface_line_context := 'CUX_AR_INVOICE'; --INVOICE行弹性域上下文:服务结算INVOICE
        l_trx_lines_tbl(l_line_count).interface_line_attribute1 := rec_trx_line.header_id; --项目ID
        l_trx_lines_tbl(l_line_count).interface_line_attribute2 := rec_trx_line.line_id; --任务ID   
        l_trx_lines_tbl(l_line_count).interface_line_attribute3 := rec_trx_line.source_code; --服务结算头id
        l_trx_lines_tbl(l_line_count).interface_line_attribute4 := rec_trx_line.source_line_id; --服务结算行id                
        --插入分配行               
        l_trx_dist_tbl(l_line_count).trx_dist_id := rec_trx_line.line_id;
        l_trx_dist_tbl(l_line_count).trx_header_id := rec_trx_line.header_id;
        l_trx_dist_tbl(l_line_count).trx_line_id := rec_trx_line.line_id;
        l_trx_dist_tbl(l_line_count).account_class := 'REV'; --收入--必输字段REDO
        l_trx_dist_tbl(l_line_count).amount := l_trx_lines_tbl(l_line_count)
                                               .quantity_invoiced * l_trx_lines_tbl(l_line_count)
                                               .unit_selling_price;
        l_trx_dist_tbl(l_line_count).acctd_amount := l_trx_dist_tbl(l_line_count)
                                                     .amount;
        l_trx_dist_tbl(l_line_count).percent := 100;
        --得到应收事务处理类型的收入账户
        l_trx_dist_tbl(l_line_count).code_combination_id := rec_trx_line.code_combination_id; --账户ID 
      
        --插入销售人员信息(行的销售人员)         
        --得到销售人员下面的应收账款id             
        l_trx_salescredits_tbl(l_line_count).trx_salescredit_id := rec_trx_line.line_id; --GT表的id 
        l_trx_salescredits_tbl(l_line_count).trx_line_id := rec_trx_line.line_id; --行id           
        l_trx_salescredits_tbl(l_line_count).salesrep_id := -3; --销售人员id (取销售订单头的销售员)
        l_trx_salescredits_tbl(l_line_count).sales_credit_type_id := 1; --1:Quota Sales Credit 2:Non-quota Sales Credit  REDO
        l_trx_salescredits_tbl(l_line_count).salescredit_percent_split := 100; --REDO
        --added by cxy@2013-03-07 begin 
        ----(绕过自动计税)it is IMPORTANT to set the flag to N to flag that we want to bypass the tax engine         
        l_trx_lines_tbl(l_line_count).taxable_flag := 'N';
        --added by cxy@2013-03-07 end 
        cux_log.debug('rec_trx_line loop end:');
      END LOOP; --FOR rec_trx_line IN cur_trx_line ...
      --added by cxy@2013-03-15 begin 
      --增加税行
      FOR rec_trx_line IN cur_trx_line(x_header_id => rec_trx_header.header_id
                                       -- x_customer_id   => rec_ar_group.customer_id,
                                       -- x_currency_code => rec_ar_group.currency_code
                                       ) LOOP
        --cux_log.debug('rec_trx_line loop begin:');
        cux_conc_utl.log_msg('进入pvt,调用pvt。do_ar FOR rec_trx_line IN cur_trx_line(x_header_id => rec_trx_header.header_id:');
        l_tax_rate           := NULL;
        l_inclusive_tax_flag := NULL;
        l_line_count         := l_line_count + 1;
        l_line_num           := l_line_num + 1;
        -- LINE 2 : line_type = TAX
        -- this is the MANUAL tax line you are creating
        -- unique value within this trx_header_id
        SELECT cux_ar_invoice_line_s.nextval
          INTO l_trx_line_tax_id
          FROM dual;
        --得到税值:
        l_tax_rate := cux_ar_invoice_utl.get_tax_rate(p_tax_rate_code => rec_trx_line.tax_rate_code);
        cux_conc_utl.log_msg('进入pvt,调用pvt。do_ar  --得到税值:' || l_tax_rate);
        IF l_tax_rate IS NULL THEN
          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; --IF l_item_uom_code IS NULL THEN             
        l_inclusive_tax_flag := cux_ar_invoice_utl.get_inclusive_tax_flag(p_tax_rate_code => rec_trx_line.tax_rate_code);
        l_trx_lines_tbl(l_line_count).trx_line_id := l_trx_line_tax_id;
        -- this value needs to match the trx_header_id value provided in trx_header_tbl(1).trx_header_id
        l_trx_lines_tbl(l_line_count).trx_header_id := rec_trx_line.header_id;
        l_trx_lines_tbl(l_line_count).line_number := l_line_num; --行号都为1
        -- provide a description
        --l_trx_lines_tbl(l_line_count).description := 'Test Tax';
        -- pass in the tax amount and rate, these both have to be populated, if you pass only one of them
        -- you will get an error
        l_trx_lines_tbl(l_line_count).amount := rec_trx_line.tax_amount; --实际应收金额税值 redo primary_salesrep_id
        /* IF p_ar_type_code = 'INV' THEN
          --INVOICE
          l_trx_lines_tbl(l_line_count).amount := rec_trx_line.actual_ar_amount *
                                                  (l_tax_rate / 100); --实际应收金额税值
        ELSIF p_ar_type_code = 'CM' THEN
          --贷项通知单
          l_trx_lines_tbl(l_line_count).amount := (-1) *
                                                  rec_trx_line.actual_ar_amount *
                                                  (l_tax_rate / 100); --实际应收金额*税值
        END IF; --IF l_type_code = 'INV' THEN */
        -- pass in line_type = TAX
        l_trx_lines_tbl(l_line_count).line_type := 'TAX';
        -- pass in the value associated with the tax RATE
        l_trx_lines_tbl(l_line_count).tax_rate_code := rec_trx_line.tax_rate_code;
        cux_conc_utl.log_msg('进入pvt,rec_trx_line.TAX_RATE_code;' ||
                             rec_trx_line.tax_rate_code);
        --行上的金额是否含税
        l_trx_lines_tbl(l_line_count).amount_includes_tax_flag := nvl(l_inclusive_tax_flag,
                                                                      'N');
        -- pass in the value associated with the jurisdiction
        --l_trx_lines_tbl(l_line_count+1).tax_jurisdiction_code := 'CO-FL-DUVAL-11716';
        -- pass in the associated REGIME
        --l_trx_lines_tbl(l_line_count+1).tax_regime_code := 'US-SALES-TAX-101';
        -- pass in the associated TAX
        --l_trx_lines_tbl(l_line_count+1).tax := 'COUNTY';
        -- pass in the associated TAX STATUS
        --l_trx_lines_tbl(l_line_count+1).tax_status_code := 'STANDARD';
        -- link it to the line this tax is associated with          
        l_trx_lines_tbl(l_line_count).link_to_trx_line_id := rec_trx_line.line_id;
        --added by cxy@2013-03-07 end            
      END LOOP; --FOR rec_trx_line IN cur_trx_line...
      --added by cxy@2013-03-15 end 
      --调用系统标准的API创建INVOICE         
      cux_log.debug('ar_invoice_api_pub.create_single_invoice BEGIN');
      dbms_output.put_line('ar_invoice_api_pub.create_single_invoice BEGIN');
      ar_invoice_api_pub.create_single_invoice(p_api_version          => 1.0,
                                               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        => l_return_status,
                                               x_msg_count            => l_msg_count,
                                               x_msg_data             => l_msg_data);


 

转载于:https://www.cnblogs.com/wanghang/p/6299461.html

 类似资料: