EBS R12 应付发票混合发票导入

PROCEDURE create_invoices( x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,p_attribute10 IN VARCHAR2
,x_ap_invoice_id OUT NUMBER) IS
l_api_name CONSTANT VARCHAR2(30) := 'create_invoices';
l_savepoint_name CONSTANT VARCHAR2(30) := 'create_invoices_sp';
l_iface_rec ap_invoices_interface%ROWTYPE;
l_iface_lines_rec ap_invoice_lines_interface%ROWTYPE;
lx_holds_count NUMBER;
lx_approval_status VARCHAR2(240);
lv_funds_return_code VARCHAR2(240);
l_success BOOLEAN;
--
v_batch_error_flag VARCHAR2(30);
v_invoices_fetched NUMBER;
v_invoices_created NUMBER;
v_total_invoice_amount NUMBER; -- bug 989221
v_print_batch_flag VARCHAR2(30);
l_input_batch_number VARCHAR2(80);
l_amount_sum NUMBER := 0;
l_num NUMBER;
l_flag VARCHAR2(1);
l_inv_flag VARCHAR2(1);
-- l_count NUMBER;
-- l_sum_amount NUMBER := 0;
-- l_diff_dist_account VARCHAR2(80);
l_invoice_type_lookup_code VARCHAR2(30);
CURSOR cur_l_data(t_attribute10 IN VARCHAR2) IS

SELECT l.iface_id
,l.line_number
,l.line_type_lookup_code
,decode(l.po_match_id
,NULL
,l.line_amount
,l.quantity_invoiced * l.unit_price) line_amount

,l.tax_amount
,l.po_match_id
,l.quantity_invoiced
,l.unit_price
,l.line_dis_account_id
,l.attribute21
,l.attribute22
,l.attribute23
,l.attribute24
,l.attribute25
,l.attribute26
,l.attribute27
,l.attribute28
,l.attribute29
,l.attribute30
,l.source_header_id
FROM cux_imp_ap_invoice_iface l
WHERE 1 = 1
AND l.request_id = g_request_id
AND l.imp_status = 'R'
AND l.attribute10 = t_attribute10
ORDER BY l.line_amount DESC;

CURSOR cur_po_data(t_iface_id IN NUMBER
,t_po_match_id IN VARCHAR2) IS
SELECT tmp.iface_id
,tmp.consigned_flag
,tmp.po_header_id
,tmp.po_line_id
,tmp.line_location_id
,tmp.po_release_id
,tmp.quantity
,tmp.transaction_id
,tmp.item_id
,tmp.unit_of_measure
,tmp.mark_id
FROM cux.cux_ap_po_order_tmp tmp
WHERE 1 = 1
AND tmp.iface_id = t_iface_id
AND tmp.po_match_id = t_po_match_id
ORDER BY tmp.quantity DESC;

-- l_max_line_num NUMBER;
l_old_num NUMBER;
l_org_id NUMBER;
l_org_name VARCHAR2(240);
l_vendor_id NUMBER;
l_vendor_site_id NUMBER;
l_terms_date DATE;
l_exchange_date DATE;
l_exchange_rate NUMBER;
l_terms_id NUMBER;
l_payment_method_code VARCHAR2(80);
l_invoice_date DATE;
l_invoice_num VARCHAR2(240);
l_invoice_currency_code VARCHAR2(80);
l_invoice_amount NUMBER;
l_tax_amount NUMBER;
l_diif_tax_amount NUMBER;
l_description VARCHAR2(240);
l_gl_date DATE;
l_liability_account_id NUMBER;
l_tax_classification_code VARCHAR2(80);
l_source_code VARCHAR2(80);
l_attribute1 VARCHAR2(150);
l_attribute2 VARCHAR2(150);
l_attribute3 VARCHAR2(150);
l_attribute4 VARCHAR2(150);
l_attribute5 VARCHAR2(150);
l_attribute6 VARCHAR2(150);
l_attribute7 VARCHAR2(150);
l_attribute8 VARCHAR2(150);
l_attribute9 VARCHAR2(150);
l_attribute10 VARCHAR2(150);
l_apply_term VARCHAR2(150);

BEGIN
x_return_status := cux_api.start_activity(p_pkg_name => g_pkg_name
,p_api_name => l_api_name
,p_savepoint_name => l_savepoint_name
,p_init_msg_list => p_init_msg_list);
raise_exception(x_return_status);

SELECT org_id
,org_name
,invoice_type_lookup_code
,vendor_id
,vendor_site_id
,terms_date
,exchange_date
,exchange_rate
,terms_id
,payment_method_code
,invoice_date
,invoice_num
,invoice_currency_code
,SUM(invoice_amount) invoice_amount
,SUM(tax_amount) tax_amount
,SUM(diif_tax_amount) diif_tax_amount
,description
,gl_date
,liability_account_id
,tax_classification_code
,source_code
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,apply_term
INTO l_org_id
,l_org_name
,l_invoice_type_lookup_code
,l_vendor_id
,l_vendor_site_id
,l_terms_date
,l_exchange_date
,l_exchange_rate
,l_terms_id
,l_payment_method_code
,l_invoice_date
,l_invoice_num
,l_invoice_currency_code
,l_invoice_amount
,l_tax_amount
,l_diif_tax_amount
,l_description
,l_gl_date
,l_liability_account_id
,l_tax_classification_code
,l_source_code
,l_attribute1
,l_attribute2
,l_attribute3
,l_attribute4
,l_attribute5
,l_attribute6
,l_attribute7
,l_attribute8
,l_attribute9
,l_attribute10
,l_apply_term
FROM (SELECT t.org_id
,t.org_name
,t.invoice_type_lookup_code
,t.vendor_id
,t.vendor_site_id
,cux_imp_ap_invoice_utl.verify_date(p_date => t.terms_date
,p_format => g_date_formate) terms_date
,cux_imp_ap_invoice_utl.verify_date(p_date => t.exchange_date
,p_format => g_date_formate) exchange_date
,t.exchange_rate
,t.terms_id
,t.payment_method_code
,cux_imp_ap_invoice_utl.verify_date(p_date => t.invoice_date
,p_format => g_date_formate) invoice_date
,t.invoice_num
,t.invoice_currency_code
,nvl(t.line_amount
,0) + nvl(t.tax_amount
,0) invoice_amount
,t.tax_amount tax_amount
,t.tax_amount diif_tax_amount
,t.description
,cux_imp_ap_invoice_utl.verify_date(p_date => t.gl_date
,p_format => g_date_formate) gl_date
,t.liability_account_id
,t.tax_classification_code
,t.source_code
,t.attribute1
,t.attribute2
,t.attribute3
,t.attribute4
,t.attribute5 --合同号
,t.attribute6
,t.attribute7
,t.attribute8
,t.attribute9
,t.attribute10
,t.apply_term --核销条件
FROM cux_imp_ap_invoice_iface t
WHERE 1 = 1
AND t.attribute10 = p_attribute10
AND t.request_id = g_request_id
AND t.imp_status = 'R')
GROUP BY org_id
,org_name
,invoice_type_lookup_code
,vendor_id
,vendor_site_id
,vendor_site_id
,terms_date
,exchange_date
,exchange_rate
,terms_id
,payment_method_code
,invoice_date
,invoice_num
,invoice_currency_code
,description
,gl_date
,liability_account_id
,tax_classification_code
,source_code
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,apply_term;


l_iface_rec := NULL;
l_iface_rec.last_updated_by := g_user_id;
l_iface_rec.creation_date := SYSDATE;
l_iface_rec.created_by := g_user_id;
l_iface_rec.last_update_login := g_login_id;
l_iface_rec.invoice_id := ap_invoices_interface_s.nextval;
l_iface_rec.group_id := l_iface_rec.invoice_id;

--
--发票头
--获取发票invoice id

l_iface_rec.org_id := l_org_id;
l_iface_rec.invoice_num := l_invoice_num; --发票编号
l_iface_rec.vendor_id := l_vendor_id; --供应商ID
l_iface_rec.vendor_site_id := l_vendor_site_id; --供应商地点
l_iface_rec.terms_date := l_terms_date; --条件日期
l_iface_rec.invoice_currency_code := l_invoice_currency_code; --发票币别

IF l_iface_rec.invoice_currency_code <> 'CNY' THEN
l_iface_rec.exchange_rate_type := 'User'; --汇率类型
l_iface_rec.exchange_date := l_exchange_date; --汇率日期
l_iface_rec.exchange_rate := l_exchange_rate; --汇率
END IF;

l_iface_rec.terms_id := l_terms_id; --条件
l_iface_rec.payment_method_code := l_payment_method_code; --付款方法
l_iface_rec.invoice_date := l_invoice_date; --发票日期

IF l_invoice_type_lookup_code = 'MIXED' THEN
l_iface_rec.invoice_type_lookup_code := 'CREDIT'; --发票类型
l_iface_rec.invoice_amount := abs(l_invoice_amount) * -1; --发票金额
ELSE
l_iface_rec.invoice_type_lookup_code := l_invoice_type_lookup_code; --发票类型
l_iface_rec.invoice_amount := l_invoice_amount; --发票金额
END IF;

l_iface_rec.description := l_description;
l_iface_rec.gl_date := l_gl_date;
l_iface_rec.accts_pay_code_combination_id := l_liability_account_id;
l_iface_rec.source := g_source; --来源
l_iface_rec.attribute1 := l_attribute1;
l_iface_rec.attribute2 := l_attribute2;
l_iface_rec.attribute3 := l_attribute3;
l_iface_rec.attribute4 := l_attribute4;
l_iface_rec.attribute5 := l_attribute5; --合同号
l_iface_rec.attribute6 := l_attribute6;
l_iface_rec.attribute7 := l_attribute7;
l_iface_rec.attribute8 := l_attribute8;
l_iface_rec.attribute9 := l_attribute9;
l_iface_rec.attribute10 := l_attribute10;
l_iface_rec.attribute11 := l_apply_term; --核销条件
--把数据插入到发票头接口表
IF l_invoice_amount > 0 THEN
l_flag := 'P'; --正数
ELSE
l_flag := 'M'; --负数
END IF;
l_iface_rec.attribute13 := l_flag;
INSERT INTO ap_invoices_interface VALUES l_iface_rec;
l_amount_sum := 0;
l_num := 0;

FOR rec_l IN cur_l_data(l_attribute10) LOOP

IF rec_l.po_match_id IS NOT NULL THEN
get_po_data(x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_iface_id => rec_l.iface_id
,p_po_match_id => rec_l.po_match_id
,p_invoiced_quantity => rec_l.quantity_invoiced);

raise_exception(x_return_status);
l_old_num := 0;
FOR rec_po IN cur_po_data(t_iface_id => rec_l.iface_id
,t_po_match_id => rec_l.po_match_id) LOOP
l_old_num := l_old_num + 1;
l_num := l_num + 1;
l_iface_lines_rec := NULL;
l_iface_lines_rec.last_update_date := SYSDATE;
l_iface_lines_rec.last_updated_by := g_user_id;
l_iface_lines_rec.creation_date := SYSDATE;
l_iface_lines_rec.created_by := g_user_id;
l_iface_lines_rec.last_update_login := g_login_id;
l_iface_lines_rec.attribute1 := rec_l.attribute21;
l_iface_lines_rec.attribute2 := rec_l.attribute22;
l_iface_lines_rec.attribute3 := rec_l.attribute23;
l_iface_lines_rec.attribute4 := rec_l.attribute24;
l_iface_lines_rec.attribute5 := rec_l.attribute25;
l_iface_lines_rec.attribute6 := rec_l.attribute26;
l_iface_lines_rec.attribute7 := rec_l.attribute27;
l_iface_lines_rec.attribute8 := rec_l.attribute28;
l_iface_lines_rec.attribute9 := rec_l.attribute29;
l_iface_lines_rec.attribute10 := rec_l.attribute30;
l_iface_lines_rec.attribute12 := rec_l.source_header_id;
l_iface_lines_rec.attribute14 := rec_po.mark_id; --标记表ID
l_iface_lines_rec.org_id := l_iface_rec.org_id; --OU
l_iface_lines_rec.invoice_id := l_iface_rec.invoice_id; --发票ID
l_iface_lines_rec.accounting_date := l_iface_rec.gl_date; --发票行日期
l_iface_lines_rec.line_type_lookup_code := rec_l
.line_type_lookup_code; --发票行类型

IF rec_po.consigned_flag = 'N' THEN
--采购订单匹配行
l_iface_lines_rec.invoice_line_id := ap_invoice_lines_interface_s.nextval;

l_iface_lines_rec.line_number := l_num;
l_iface_lines_rec.po_header_id := rec_po.po_header_id;
l_iface_lines_rec.po_line_id := rec_po.po_line_id;
l_iface_lines_rec.po_line_location_id := rec_po.line_location_id;

l_iface_lines_rec.rcv_transaction_id := rec_po.transaction_id;
l_iface_lines_rec.inventory_item_id := rec_po.item_id;
l_iface_lines_rec.po_unit_of_measure := rec_po.unit_of_measure;
l_iface_lines_rec.quantity_invoiced := rec_po.quantity;
l_iface_lines_rec.unit_price := rec_l.unit_price;

l_iface_lines_rec.amount := round(rec_po.quantity *
rec_l.unit_price
,2);
l_iface_lines_rec.match_option := 'R';

INSERT INTO ap_invoice_lines_interface
VALUES l_iface_lines_rec;
--寄售
ELSIF rec_po.consigned_flag = 'Y' THEN
--采购订单匹配行
l_iface_lines_rec.invoice_line_id := ap_invoice_lines_interface_s.nextval;

l_iface_lines_rec.line_number := l_num;
l_iface_lines_rec.po_header_id := rec_po.po_header_id;
l_iface_lines_rec.po_release_id := rec_po.po_release_id;
l_iface_lines_rec.po_line_id := rec_po.po_line_id;
l_iface_lines_rec.po_line_location_id := rec_po.line_location_id;
l_iface_lines_rec.inventory_item_id := rec_po.item_id;
l_iface_lines_rec.po_unit_of_measure := rec_po.unit_of_measure;

l_iface_lines_rec.quantity_invoiced := rec_po.quantity;
l_iface_lines_rec.unit_price := rec_l.unit_price;

l_iface_lines_rec.amount := round(rec_po.quantity *
rec_l.unit_price
,2);
l_iface_lines_rec.match_option := 'P';
INSERT INTO ap_invoice_lines_interface
VALUES l_iface_lines_rec;
END IF;

l_amount_sum := l_amount_sum +
round(rec_po.quantity * rec_l.unit_price
,2);

END LOOP;
ELSE

l_num := l_num + 1;
l_iface_lines_rec := NULL;
l_iface_lines_rec.last_update_date := SYSDATE;
l_iface_lines_rec.last_updated_by := g_user_id;
l_iface_lines_rec.creation_date := SYSDATE;
l_iface_lines_rec.created_by := g_user_id;
l_iface_lines_rec.last_update_login := g_login_id;
l_iface_lines_rec.attribute1 := rec_l.attribute21;
l_iface_lines_rec.attribute2 := rec_l.attribute22;
l_iface_lines_rec.attribute3 := rec_l.attribute23;
l_iface_lines_rec.attribute4 := rec_l.attribute24;
l_iface_lines_rec.attribute5 := rec_l.attribute25;
l_iface_lines_rec.attribute6 := rec_l.attribute26;
l_iface_lines_rec.attribute7 := rec_l.attribute27;
l_iface_lines_rec.attribute8 := rec_l.attribute28;
l_iface_lines_rec.attribute9 := rec_l.attribute29;
l_iface_lines_rec.attribute10 := rec_l.attribute30;
l_iface_lines_rec.attribute12 := rec_l.source_header_id;
l_iface_lines_rec.org_id := l_iface_rec.org_id; --OU
l_iface_lines_rec.invoice_id := l_iface_rec.invoice_id; --发票ID
l_iface_lines_rec.accounting_date := l_iface_rec.gl_date; --发票行日期
l_iface_lines_rec.line_type_lookup_code := rec_l.line_type_lookup_code; --发票行类型

--非采购订单匹配发票
l_iface_lines_rec.invoice_line_id := ap_invoice_lines_interface_s.nextval;

l_iface_lines_rec.line_number := l_num; --发票行号

l_iface_lines_rec.amount := rec_l.line_amount; --发票行金额

l_iface_lines_rec.dist_code_combination_id := rec_l.line_dis_account_id;

l_amount_sum := l_amount_sum + rec_l.line_amount;

INSERT INTO ap_invoice_lines_interface VALUES l_iface_lines_rec;
END IF;
END LOOP;

l_num := l_num + 1;
--税
l_iface_lines_rec := NULL;
l_iface_lines_rec.last_update_date := SYSDATE;
l_iface_lines_rec.last_updated_by := g_user_id;
l_iface_lines_rec.creation_date := SYSDATE;
l_iface_lines_rec.created_by := g_user_id;
l_iface_lines_rec.last_update_login := g_login_id;
l_iface_lines_rec.org_id := l_iface_rec.org_id; --OU
l_iface_lines_rec.invoice_id := l_iface_rec.invoice_id; --发票ID
l_iface_lines_rec.accounting_date := l_iface_rec.gl_date; --发票行日期

l_iface_lines_rec.invoice_line_id := ap_invoice_lines_interface_s.nextval;

l_iface_lines_rec.line_number := l_num; --发票行号

l_iface_lines_rec.amount := l_tax_amount; -- 税额

l_iface_lines_rec.tax_code := l_tax_classification_code;
l_iface_lines_rec.tax_classification_code := l_tax_classification_code;
l_iface_lines_rec.line_type_lookup_code := 'TAX';
l_amount_sum := l_amount_sum +
l_diif_tax_amount;
INSERT INTO ap_invoice_lines_interface VALUES l_iface_lines_rec;

--判断是否需要导入差异行
cux_conc_utl.log_msg(' 发票总额------------------------->' ||
l_invoice_amount);
cux_conc_utl.log_msg('发票行单价*数量汇总额： --------->' || l_amount_sum);
IF l_invoice_amount <> l_amount_sum THEN

l_num := l_num + 1;
l_iface_lines_rec := NULL;
l_iface_lines_rec.last_update_date := SYSDATE;
l_iface_lines_rec.last_updated_by := g_user_id;
l_iface_lines_rec.creation_date := SYSDATE;
l_iface_lines_rec.created_by := g_user_id;
l_iface_lines_rec.last_update_login := g_login_id;

l_iface_lines_rec.org_id := l_iface_rec.org_id; --OU
l_iface_lines_rec.invoice_id := l_iface_rec.invoice_id; --发票ID
l_iface_lines_rec.accounting_date := l_iface_rec.gl_date; --发票行日期
l_iface_lines_rec.line_type_lookup_code := 'ITEM'; --发票行类型
--非采购订单匹配发票
l_iface_lines_rec.invoice_line_id := ap_invoice_lines_interface_s.nextval;
l_iface_lines_rec.line_number := l_num; --发票行号
l_iface_lines_rec.amount := l_invoice_amount - l_amount_sum; --发票行金额
cux_conc_utl.log_msg(' 差异行金额------------------>' ||
l_iface_lines_rec.amount);
l_iface_lines_rec.dist_code_combination_id := cux_imp_ap_invoice_utl.get_diff_dist_acct_id(p_org_code => l_org_name
,p_org_id => l_org_id
,p_account_num => g_account_num
,p_segment_delimiter => g_segment_delimiter);
cux_conc_utl.log_msg('dist_code_combination_id--------->' ||
l_iface_lines_rec.dist_code_combination_id);
INSERT INTO ap_invoice_lines_interface VALUES l_iface_lines_rec;
END IF;

-- END LOOP;
cux_conc_utl.log_msg('发票导入api开始--------->' ||
to_char(SYSDATE
,'YYYY-MM-DD hh24:mi:ss'));
l_success := ap_import_invoices_pkg.import_invoices(p_batch_name => l_input_batch_number
,p_gl_date => SYSDATE
,p_hold_code => NULL
,p_hold_reason => NULL
,p_commit_cycles => NULL
,p_source => l_iface_rec.source
,p_group_id => l_iface_rec.invoice_id
,p_conc_request_id => -1
,p_debug_switch => 'N'
,p_org_id => l_iface_lines_rec.org_id
,p_batch_error_flag => v_batch_error_flag
,p_invoices_fetched => v_invoices_fetched
,p_invoices_created => v_invoices_created
,p_total_invoice_amount => v_total_invoice_amount
, -- bug 989221
p_print_batch => v_print_batch_flag
,p_calling_sequence => 'invoice_import'
,p_commit => 'N');
cux_conc_utl.log_msg('发票导入api结束--------->' ||
to_char(SYSDATE
,'YYYY-MM-DD hh24:mi:ss'));
IF NOT l_success
OR v_invoices_created = 0 THEN
FOR rec IN (SELECT t.displayed_field || t.reject_lookup_code ||
t.parent_table msg
FROM ap_interface_rejections_v t
WHERE 1 = 1
/* AND rownum < 5*/
AND t.invoice_id = l_iface_rec.invoice_id) LOOP
x_msg_data := substr(x_msg_data || rec.msg
,0
,2000);
END LOOP;
cux_conc_utl.log_msg('错误信息查询结束--------->' ||
to_char(SYSDATE
,'YYYY-MM-DD hh24:mi:ss'));


cux_api.set_message(p_app_name => 'FND'
,p_msg_name => 'FND_GENERIC_MESSAGE'
,p_token1 => 'MESSAGE'
,p_token1_value => '错误提示:发票创建失败1。错误信息：' ||
x_msg_data ||
v_batch_error_flag);
x_return_status := fnd_api.g_ret_sts_error;
raise_exception(x_return_status);
ELSE

BEGIN
SELECT ai.invoice_id
,ai.attribute13
INTO x_ap_invoice_id
,l_inv_flag
FROM ap_invoices_all ai
WHERE ai.vendor_id = l_iface_rec.vendor_id
AND ai.org_id = l_iface_rec.org_id
AND ai.invoice_num = l_iface_rec.invoice_num;
EXCEPTION
WHEN OTHERS THEN
cux_api.set_message(p_app_name => 'FND'
,p_msg_name => 'FND_GENERIC_MESSAGE'
,p_token1 => 'MESSAGE'
,p_token1_value => '错误提示:发票创建失败3。错误信息：' ||
SQLERRM || x_msg_data);
x_return_status := fnd_api.g_ret_sts_error;
raise_exception(x_return_status);
END;
END IF;

cux_conc_utl.log_msg('l_invoice_type_lookup_code-------' ||
l_invoice_type_lookup_code);
cux_conc_utl.log_msg('l_inv_flag-------' || l_inv_flag);
IF l_invoice_type_lookup_code = 'MIXED' THEN

IF l_inv_flag = 'P' THEN
UPDATE ap_invoices_all a
SET a.invoice_type_lookup_code = 'MIXED' --混合发票
,a.invoice_amount = a.invoice_amount * -1
,a.pay_curr_invoice_amount = a.pay_curr_invoice_amount * -1
,a.amount_applicable_to_discount = a.amount_applicable_to_discount * -1
WHERE a.invoice_id = x_ap_invoice_id;
UPDATE ap_payment_schedules_all t
SET t.amount_remaining = t.amount_remaining * -1
,t.gross_amount = t.gross_amount * -1
,t.inv_curr_gross_amount = t.inv_curr_gross_amount * -1 --总额
WHERE 1 = 1
AND t.invoice_id = x_ap_invoice_id;

ELSE
UPDATE ap_invoices_all a
SET a.invoice_type_lookup_code = 'MIXED' --混合发票
WHERE a.invoice_id = x_ap_invoice_id;
END IF;

END IF;
cux_conc_utl.log_msg('发票金额更新结束--------->' ||
to_char(SYSDATE
,'YYYY-MM-DD hh24:mi:ss'));
ap_approval_pkg.approve(p_run_option => 'ALL'
,p_invoice_batch_id => NULL
,p_begin_invoice_date => NULL
,p_end_invoice_date => NULL
,p_vendor_id => l_iface_rec.vendor_id
,p_pay_group => NULL
,p_invoice_id => x_ap_invoice_id
,p_entered_by => NULL
,p_set_of_books_id => NULL
,p_trace_option => NULL
,p_conc_flag => NULL
,p_holds_count => lx_holds_count
,p_approval_status => lx_approval_status
,p_funds_return_code => lv_funds_return_code
,p_calling_mode => 'APPROVE'
,p_calling_sequence => 'CUXAPAUTOAPPLY'
,p_debug_switch => 'N'
,p_budget_control => 'Y'
,p_commit => 'N');
cux_conc_utl.log_msg('发票验证结束--------->' ||
to_char(SYSDATE
,'YYYY-MM-DD hh24:mi:ss'));
IF lx_approval_status <> 'APPROVED' THEN
SELECT MAX(listagg(t.hold_lookup_code || ':' || t.hold_reason
,'/') within GROUP(ORDER BY t.invoice_id))
INTO x_msg_data
FROM ap_holds_all t
WHERE 1 = 1
AND t.invoice_id = x_ap_invoice_id
GROUP BY t.invoice_id;

cux_api.set_message(p_app_name => 'FND'
,p_msg_name => 'FND_GENERIC_MESSAGE'
,p_token1 => 'MESSAGE'
,p_token1_value => '发票验证失败!' || x_msg_data);
x_return_status := fnd_api.g_ret_sts_error;
raise_exception(x_return_status);
END IF;
END create_invoices;