EBS R12 应付发票混合发票导入(实例DEMO)

declare
l_iface_rec ap_invoices_interface%ROWTYPE;
l_iface_lines_rec ap_invoice_lines_interface%ROWTYPE;
l_org_id NUMBER;
v_gdts number;
l_vendor_id number;
l_vendor_site_id number;

p_counts number;

zx_rates_b number;

v_period_name varchar2(100);

p_crr_count number;

v_ou_code number;

l_success BOOLEAN;

p_rejec_info varchar2(2000);

p_rejec_info varchar2(4000);

v_code_combination_id NUMBER;

p_ou_code varchar2(200); --公司段

p_pra_code varchar2(200); --负债科目
p_crr_code varchar2(200); --行负债科目

p_pra_code_id number; --负债科目
p_crr_code_id number; --行负债科目

p_dr_count number;

l_item_num number;
--
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_promised_date date;

x_ap_invoice_id number;

l_holds_count number;

l_Approve_Status varchar2(2000);

l_Funds_Code varchar2(100);

v_vendor_site number;

v_currs number;

p_zx_rates number;

rx_return_status varchar2(240);
rx_return_mesgs varchar2(2000);

l_error_message varchar2(4000);

v_counts number;
v_venodrs number;

v_vendor_count number;

v_term_ids number;

l_head_inter_id number;

l_TW_CRR_ACCOUNT_id number;

l_TW_DR_ACCOUNT_id number;

p_next_flag varchar2(240);

p_user_id number;

p_ap_user_id number;

vrx_return_status varchar2(100);
vrx_return_mesgs varchar2(1000);

v_return_status varchar2(100);
v_return_mesgs varchar2(1000);

pp_rejec_info varchar2(2000);

t_crr_code varchar2(1000);

t_crr_code_id number;

x_dr_code varchar2(100);

v_amount number := -1000;

l_inv_flag varchar2(10);

BEGIN

p_next_flag := 'S';

l_error_message := null;

v_ou_code := null;

select fus.USER_ID
into p_ap_user_id
from fnd_user fus
where fus.USER_NAME = '15795'; --c_head.ap_user_num;

cux_common_pkg.init_application(p_org_code => '101', --c_head.ou_code,
p_moudle => 'AP',
p_user_id => p_ap_user_id,
x_return_status => vrx_return_status,
x_return_mesg => vrx_return_mesgs);

mo_global.init('M');

l_iface_rec := null;

select org.OPERATING_UNIT
into l_org_id
from org_organization_definitions org
where org.ORGANIZATION_CODE = '101'; --c_head.ou_code;

select x.attribute1
into p_ou_code
from HR_ORGANIZATION_UNITS_V x
where x.organization_id = l_org_id;

l_iface_rec.ATTRIBUTE2 := '20240426001'; --c_head.fssc_boe_no;

--发票头最后更新人
l_iface_rec.last_updated_by := fnd_global.user_id;
--发票头创建时间
l_iface_rec.creation_date := SYSDATE;
--发票头创建人
l_iface_rec.created_by := fnd_global.user_id;
--发票头最后登录人
l_iface_rec.last_update_login := fnd_global.LOGIN_ID;
--发票行最后更新时间
l_iface_lines_rec.last_update_date := SYSDATE;
--发票行最后更新人
l_iface_lines_rec.last_updated_by := fnd_global.user_id;
--发票行创建时间
l_iface_lines_rec.creation_date := SYSDATE;
--发票行的创建人
l_iface_lines_rec.created_by := fnd_global.user_id;
--发票行最后更新人
l_iface_lines_rec.last_update_login := fnd_global.LOGIN_ID;

--发票头
--获取发票invoice id
SELECT ap_invoices_interface_s.NEXTVAL INTO l_head_inter_id FROM dual;

l_iface_rec.invoice_id := l_head_inter_id;

-- 发票金额为正数，发票类型为标准：S+供应商编码+yyyymm+两位流水号，
-- 发票金额为负数，发票类型为贷项通知单：C+供应商编码+yyyymm+两位流水号

if /*c_head.INVOICE_AMOUNT*/
v_amount > 0 then

--发票编号
l_iface_rec.invoice_num := 'S-G0502-' || to_char(sysdate, 'YYYYMM') ||
substr(cux.cux_ap_check_all_s.nextval, -4);

--发票类型
l_iface_rec.invoice_type_lookup_code := 'STANDARD';

--发票金额
l_iface_rec.invoice_amount := v_amount; --c_head.invoice_amount;

else
--发票编号
l_iface_rec.invoice_num := 'C-G0502-' || to_char(sysdate, 'YYYYMM') ||
substr(cux.cux_ap_check_all_s.nextval, -4);

--发票金额
l_iface_rec.invoice_amount := abs(v_amount) * -1; --c_head.invoice_amount;

--发票类型
l_iface_rec.invoice_type_lookup_code := 'CREDIT'; --'DEBIT';

/*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;*/

end if;

--发票日期
l_iface_rec.invoice_date := to_date('2024-04-26', 'YYYY-MM-DD');

---GL日期--
l_iface_rec.gl_date := to_date('2024-04-26', 'YYYY-MM-DD');

l_iface_rec.payment_method_code := 'TW-PAYMENT';

select pvs.VENDOR_ID
into l_vendor_id
from po_vendors pvs
where pvs.SEGMENT1 = 'G0502'; --c_head.vendor_number;

--供应商ID
l_iface_rec.vendor_id := l_vendor_id; --1134;

select pvsa.VENDOR_SITE_ID
into l_vendor_site_id
from po_vendor_sites_all pvsa
where pvsa.VENDOR_ID = l_vendor_id
and pvsa.VENDOR_SITE_CODE = '费用采购' --c_head.vendor_site_code
and pvsa.ORG_ID = l_org_id;

--供应商地点
l_iface_rec.vendor_site_id := l_vendor_site_id; --1134;

--发票币别
l_iface_rec.invoice_currency_code := 'CNY'; --c_head.invoice_currency_code;
--汇率类型
l_iface_rec.exchange_rate_type := 'Corporate'; --c_head.exchange_rate_type;
--汇率日期
l_iface_rec.exchange_date := NULL;
--汇率
l_iface_rec.exchange_rate := NULL;
--来源
l_iface_rec.SOURCE := 'INV IMPORT';

l_iface_rec.DESCRIPTION := '混合发票测试'; --substr(c_head.invoice_desc, 0, 80);

l_input_batch_number := '101' || to_date('2024-04-26', 'yyyy-mm-dd') ||
'15795-费用采购';

--Lookup type SOURCE
l_iface_rec.group_id := l_head_inter_id;
l_iface_rec.org_id := l_org_id;

p_pra_code := p_ou_code || '.00.00.' || '220202' ||
'.00000000.0000.0000000.000.0';

p_pra_code_id := cux_ws_ap_ce_invoice_pub.get_code_combination_id(l_segment1 => p_pra_code);

/*if p_pra_code_id = 0 then
l_error_message := nvl(l_error_message, '') || '业务大类科目段=' || p_pra_code ||
'获取会计科目失败';
end if;*/

--负责科目
l_iface_rec.ACCTS_PAY_CODE_COMBINATION_ID := p_pra_code_id;

--把数据插入到发票头接口表

INSERT INTO ap_invoices_interface VALUES l_iface_rec;

l_item_num := 0;

--发票行
l_item_num := 1;
SELECT ap_invoice_lines_interface_s.NEXTVAL
INTO l_iface_lines_rec.invoice_line_id
FROM dual;

--OU
l_iface_lines_rec.org_id := l_iface_rec.org_id;
--发票ID
l_iface_lines_rec.invoice_id := l_iface_rec.invoice_id;

--发票行日期
l_iface_lines_rec.accounting_date := to_date('2024-04-26', 'YYYY-MM-DD');

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

--发票行类型
l_iface_lines_rec.line_type_lookup_code := 'ITEM';

--发票行金额
l_iface_lines_rec.amount := -1500; --c_l_line.invoice_notax_amount;

--发票行账户

/* if c_l_line.tw_dr_account = '999902' then
x_dr_code := '0000000';
else
x_dr_code := c_l_line.attribute1;
end if;*/

x_dr_code := '0000000';

p_crr_code := p_ou_code || '.00.00.' || '999902' || '.00000000.0000.' ||
x_dr_code || '.000.0';

p_crr_code_id := cux_ws_ap_ce_invoice_pub.get_code_combination_id(l_segment1 => p_crr_code);

/*if p_crr_code_id = 0 then
l_error_message := nvl(l_error_message, '') || '业务小类科目段=' || p_crr_code ||
'项目段:' || c_l_line.attribute1 || '获取会计科目失败';
end if;*/

l_iface_lines_rec.dist_code_combination_id := p_crr_code_id; --p_crr_code_id;

-- l_iface_lines_rec.tax_code := '0%';
/*if c_l_line.tax_code is not null then
l_iface_lines_rec.tax_classification_code := '0%';
else
l_iface_lines_rec.tax_classification_code := '0%';
end if;*/

l_iface_lines_rec.tax_classification_code := '13%外加'; --c_l_line.tax_code;

--把数据插入发票行接口表
INSERT INTO ap_invoice_lines_interface VALUES l_iface_lines_rec;

l_item_num := 2;
SELECT ap_invoice_lines_interface_s.NEXTVAL
INTO l_iface_lines_rec.invoice_line_id
FROM dual;

--OU
l_iface_lines_rec.org_id := l_iface_rec.org_id;
--发票ID
l_iface_lines_rec.invoice_id := l_iface_rec.invoice_id;

--发票行日期
l_iface_lines_rec.accounting_date := to_date('2024-04-26', 'YYYY-MM-DD');

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

--发票行类型
l_iface_lines_rec.line_type_lookup_code := 'ITEM';

--发票行金额
l_iface_lines_rec.amount := 500; --c_l_line.invoice_notax_amount;

--发票行账户

/* if c_l_line.tw_dr_account = '999902' then
x_dr_code := '0000000';
else
x_dr_code := c_l_line.attribute1;
end if;*/

x_dr_code := '0000000';

p_crr_code := p_ou_code || '.00.00.' || '999902' || '.00000000.0000.' ||
x_dr_code || '.000.0';

p_crr_code_id := cux_ws_ap_ce_invoice_pub.get_code_combination_id(l_segment1 => p_crr_code);

/*if p_crr_code_id = 0 then
l_error_message := nvl(l_error_message, '') || '业务小类科目段=' || p_crr_code ||
'项目段:' || c_l_line.attribute1 || '获取会计科目失败';
end if;*/

l_iface_lines_rec.dist_code_combination_id := p_crr_code_id; --p_crr_code_id;

l_iface_lines_rec.tax_classification_code := '13%外加'; --c_l_line.tax_code;

--把数据插入发票行接口表
INSERT INTO ap_invoice_lines_interface VALUES l_iface_lines_rec;

----插入税行------
-- l_iface_lines_rec := NULL;

/* l_item_num := l_item_num + 1;

SELECT ap_invoice_lines_interface_s.NEXTVAL
INTO l_iface_lines_rec.invoice_line_id
FROM dual;

--OU
l_iface_lines_rec.org_id := l_iface_rec.org_id;
--发票ID
l_iface_lines_rec.invoice_id := l_iface_rec.invoice_id;

--发票行日期
l_iface_lines_rec.accounting_date := to_date('2024-04-26', 'YYYY-MM-DD');

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

--发票行类型
l_iface_lines_rec.line_type_lookup_code := 'ITEM';

--发票行金额
l_iface_lines_rec.amount := 1000; --c_l_line.TAX_AMOUNT;

--发票行账户

t_crr_code := p_ou_code || '.00.00.22210101' ||
'.00000000.0000.0000000.000.0';

t_crr_code_id := get_code_combination_id(l_segment1 => t_crr_code);
l_iface_lines_rec.dist_code_combination_id := t_crr_code_id; --p_crr_code_id;
l_iface_lines_rec.tax_classification_code := '13%外加'; --c_l_line.tax_code;

--把数据插入发票行接口表
INSERT INTO ap_invoice_lines_interface VALUES l_iface_lines_rec;*/

--提交事务处理
COMMIT;
--调用api把发票导入

l_success := ap_import_invoices_pkg.import_invoices(p_batch_name => l_input_batch_number,
p_gl_date => to_date('2024-04-26',
'YYYY-MM-DD'),
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');

IF NOT l_success THEN
-- dbms_output.put_line('错误提示:发票导入失败。错误信息：' || SQLERRM || v_batch_error_flag);
l_error_message := nvl(l_error_message, '') || '错误提示:发票导入失败。错误信息：' ||
SQLERRM || v_batch_error_flag;
--把接口表的数据删除

/* DELETE ap_invoices_interface aii
WHERE aii.invoice_id = l_iface_rec.invoice_id;

DELETE ap_invoice_lines_interface aili
WHERE aili.invoice_id = l_iface_rec.invoice_id;
--提交事务处理

p_ret_message := l_error_message;*/

COMMIT;
ELSE

begin

SELECT ai.invoice_id
INTO x_ap_invoice_id
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
p_next_flag := 'E';

begin
select listagg(air.description, ',') within group(order by air.description) info
into pp_rejec_info
from ap_interface_rejections_v air
where --air.PARENT_ID = l_iface_rec.invoice_id
/* exists (select 1
from ap_invoice_lines_interface ail
where ail.INVOICE_ID = air.PARENT_ID)
and */
air.PARENT_ID = l_iface_rec.invoice_id
group by air.PARENT_ID;
exception
when others then
pp_rejec_info := null;
end;

if pp_rejec_info is null or length(nvl(pp_rejec_info, '1')) <= 1 then

begin
select listagg(air.REJECT_LOOKUP_CODE, ',') within group(order by air.REJECT_LOOKUP_CODE) info
into pp_rejec_info
from ap_interface_rejections air
where --air.PARENT_ID = l_iface_rec.invoice_id
exists (select 1
from ap_invoice_lines_interface ail
where ail.INVOICE_ID = air.PARENT_ID)
and air.PARENT_ID = l_iface_rec.invoice_id
group by air.PARENT_ID;
exception
when others then
pp_rejec_info := null;
end;

end if;

end;

l_inv_flag := 'M';

IF p_next_flag = 'S' 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;

UPDATE ap_invoice_lines_all aia
SET AIA.tax_classification_code = NULL
where aia.INVOICE_ID = x_ap_invoice_id;

dbms_output.put_line('invoice_num=' || l_iface_rec.invoice_num);

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 => l_holds_count,
p_approval_status => l_Approve_Status,
p_funds_return_code => l_Funds_Code,
p_calling_mode => 'APPROVE',
p_calling_sequence => 'CUXAPAUTOAPPLY',
p_debug_switch => 'N',
p_budget_control => 'Y',
p_commit => 'N');

END IF;
end if;
end;