CREATE OR REPLACE Package acr_invoice_pkg Is
-- Author : DJ
-- Created : 2013/7/5 15:00:02
-- Purpose : 应收发票创建
-- Version : 1.30
Procedure tmp_delete_session(p_session_id Number);
Procedure split_cashflow(p_cashflow_id Number,
p_session_id Number,
p_bp_id Number,
p_bp_name Varchar2,
p_invoice_title Varchar2,
p_invoice_bp_address_phone_num Varchar2,
p_invoice_bp_bank_account Varchar2,
p_invoice_bp_tax_registry_num Varchar2,
p_head_description Varchar2,
p_currency Varchar2,
p_exchange_rate_type Varchar2,
p_exchange_rate_quotation Varchar2,
p_exchange_rate Number,
p_user_id Number);
Procedure split_penalty_cashflow(p_contract_id Number,
p_journal_header_id Number,
p_journal_line_id Number,
p_billing_amount Number,
p_session_id Number,
p_bp_id Number,
p_bp_name Varchar2,
p_invoice_title Varchar2,
p_invoice_bp_address_phone_num Varchar2,
p_invoice_bp_bank_account Varchar2,
p_invoice_bp_tax_registry_num Varchar2,
p_head_description Varchar2,
p_currency Varchar2,
p_exchange_rate_type Varchar2,
p_exchange_rate_quotation Varchar2,
p_exchange_rate Number,
p_user_id Number);
Procedure invoicetmp_update(p_record_id Number,
p_invoice_kind Varchar2,
p_billing_amount Number,
p_tax_amount Number Default Null,
p_product_name Varchar2,
p_user_id Number);
--创建发票
Procedure create_invoice(p_session_id Number,
p_company_id Number,
p_group_billing_method Varchar2,
p_invoice_date Date,
p_accounting_date Date,
p_user_id Number,
p_role_id Number);
--宏菱建机罚息开票
Procedure create_penalty_invoice(p_session_id Number,
p_company_id Number,
p_group_billing_method Varchar2,
p_invoice_date Date,
p_accounting_date Date,
p_user_id Number,
p_role_id Number);
--删除发票
Procedure delete_invoice(p_invoice_hd_id Number, p_user_id Number);
--确认发票
Procedure confirm_invoice(p_invoice_hd_id Number, p_user_id Number);
--反冲发票
Procedure reverse_invoice(p_invoice_hd_id Number,
p_reverse_date Date,
p_vat_red_notice_num Varchar2,
p_user_id Number,
p_role_id Number);
--发票行合并
--add by Yenick 宏菱项目 增加发票行合并
Procedure invoice_row_merge(p_invoice_hd_id In Number,
p_user_id Number,
p_merge_flag Varchar2);
--发票提交
Procedure acr_invoice_submit(p_batch_id Number, p_user_id Number);
--发票审批通过
Procedure acr_invoice_approved(p_batch_id Number, p_user_id Number);
-- function ss(p_batch_id out number) return number;
--发票审批拒绝
Procedure acr_invoice_reject(p_batch_id Number, p_user_id Number);
-- add by shen
Procedure acr_invoice_status_change(p_invoice_hd Number,
p_want_status In Varchar2,
p_user_id In Number);
Procedure get_invoice_number(p_document_type In Out Varchar2,
p_transaction_date In Date,
p_company_id In Number,
p_user_id In Number,
p_document_number Out Varchar2,
p_business_type Out Varchar2,
p_ln_step_length Out Number);
--增值税台账 数据处理
Procedure vat_account_data_process(p_base_period Varchar2,
p_session_id Number,
p_user_id Number);
End acr_invoice_pkg;
/
CREATE OR REPLACE Package Body acr_invoice_pkg Is
g_round_n Number := 2;
g_status_new Constant Varchar2(30) := 'NEW';
g_status_confirm Constant Varchar2(30) := 'CONFIRM';
g_status_confirming Constant Varchar2(30) := 'CONFIRMING';
g_status_post Constant Varchar2(30) := 'POST';
e_get_invoice_number_err Exception;
e_billing_amt_over_err Exception;
e_billing_amt_split_err Exception;
e_invoice_limit_not_found Exception;
e_period_not_found Exception;
e_htjs_tolerance_error Exception;
e_lock_error Exception;
Pragma Exception_Init(e_lock_error, -54);
Type invoice_hd_tmp_type Is Record(
company_id Number,
spv_company_id Number,
contract_id Number,
project_id Number,
invoice_document_type Varchar2(100),
bp_id Number,
bp_name Varchar2(2000),
bp_tax_registry_num Varchar2(2000),
bp_address_phone_num Varchar2(2000),
bp_bank_account Varchar2(2000),
description Varchar2(2000),
invoice_kind Varchar2(100),
total_amount Number,
currency Varchar2(100),
exchange_rate_type Varchar2(100),
exchange_rate_quotation Varchar2(100),
exchange_rate Number,
tax_type_id Number,
tax_type_rate Number,
times Number,
record_id number);
Type invoice_ln_tmp_type Is Record(
contract_id Number,
cashflow_id Number,
cf_item Number,
cf_type Number,
product_name Varchar2(2000),
billing_amount Number);
Type invoice_penalty_tmp_type Is Record(
contract_id Number,
cf_item Number,
cf_type Number,
product_name Varchar2(2000),
billing_amount Number,
tax_amount Number,
journal_header_id Number,
journal_line_id Number,
due_amount number);
g_package_name Varchar2(255) := upper('ACR_INVOICE_PKG');
--拆分类型
--与syscode ACR510_BILL_DETAIL_METHOD 保持一致
g_divide_t_b_equipment Varchar2(30) := 'BILLING_EQUIPMENT';
g_divide_t_b_rental Varchar2(30) := 'BILLING_RENTAL';
g_divide_t_b_principal Varchar2(30) := 'BILLING_PRINCIPAL';
g_divide_t_b_interest Varchar2(30) := 'BILLING_INTEREST';
g_divide_t_r_principal Varchar2(30) := 'RECEIPT_PRINCIPAL';
g_divide_t_others Varchar2(30) := 'OTHERS';
g_bill_status_full Varchar2(30) := 'FULL';
g_bill_status_partial Varchar2(30) := 'PARTIAL';
g_bill_status_not Varchar2(30) := 'NOT';
g_business_t_leaseback Varchar2(30) := 'LEASEBACK';
g_business_t_lease Varchar2(30) := 'LEASE';
g_document_category Constant Varchar2(30) := 'AR_INVOICE';
g_htjs_tolerance Number := 0.0625; --金税头税额与行税额合计的容差
Function cashflow_get_record(p_cashflow_id Number)
Return con_contract_cashflow%Rowtype Is
v_record con_contract_cashflow%Rowtype;
Begin
Select *
Into v_record
From con_contract_cashflow c
Where c.cashflow_id = p_cashflow_id;
Return v_record;
End;
Procedure invoicetmp_insert(p_record acr_invoice_create_tmp%Rowtype) Is
Begin
Insert Into acr_invoice_create_tmp Values p_record;
End;
Procedure tmp_delete_session(p_session_id Number) Is
Begin
Delete From acr_invoice_create_tmp t Where t.session_id = p_session_id;
End;
Function invoice_get_id Return Number Is
v_acr_invoice_create_tmp_id Number;
Begin
Select acr_invoice_create_tmp_s.nextval
Into v_acr_invoice_create_tmp_id
From dual;
Return v_acr_invoice_create_tmp_id;
End;
Function contract_get(p_contract_id Number) Return con_contract%Rowtype Is
v_record con_contract%Rowtype;
Begin
Select *
Into v_record
From con_contract c
Where c.contract_id = p_contract_id;
Return v_record;
End;
Function get_billmethod_rec(p_billing_method Varchar2)
Return hls_billing_method%Rowtype Is
r_rec hls_billing_method%Rowtype;
Begin
Select *
Into r_rec
From hls_billing_method
Where billing_method = p_billing_method;
Return r_rec;
Exception
When no_data_found Then
Return Null;
End;
Function get_invoicekind_by_taxpayer(p_contract_id Number) Return Varchar2 Is
v_taxpayer_type Varchar2(255);
v_vat_flag Varchar2(1);
Begin
Select m.taxpayer_type, c.vat_flag
Into v_taxpayer_type, v_vat_flag
From hls_bp_master m, con_contract c
Where c.contract_id = p_contract_id
And m.bp_id = c.bp_id_tenant;
If v_vat_flag = 'Y' Then
If v_taxpayer_type = 'GENERAL_TAXPAYER' Then
--普通纳税人：专票
Return '0';
Elsif v_taxpayer_type = 'SMALL_SCALE_TAXPAYER' Then
--小规模纳税人：普票
Return '2';
Else
--其余算入 小规模纳税人：普票
Return '2';
End If;
Else
Return 'SALES_TAX';
End If;
End;
--现金流处理之后
Procedure cf_type_after_handle(p_tmp acr_invoice_create_tmp%Rowtype,
p_cashflow con_contract_cashflow%Rowtype,
p_billmethodprop hls_billing_method%Rowtype) Is
v_tmp acr_invoice_create_tmp%Rowtype;
v_contract con_contract%Rowtype;
v_invoice_kind_by_taxpayer Varchar2(255);
v_cf_billing_rule Varchar2(255);
Begin
v_tmp := p_tmp;
v_contract := contract_get(p_cashflow.contract_id);
Begin
Select billing_rule
Into v_cf_billing_rule
From con_contract_cf_item
Where contract_id = p_cashflow.contract_id
And cf_item = p_cashflow.cf_item;
Exception
When no_data_found Then
v_cf_billing_rule := '';
End;
If v_cf_billing_rule = 'BILLING_FIRST' Then
v_tmp.billing_amount := nvl(v_tmp.due_amount, 0) -
v_tmp.cf_billing_amount;
Else
--modify by shen
/*v_tmp.billing_amount := nvl(v_tmp.received_amount, 0) -
v_tmp.cf_billing_amount;*/
--开票金额改为应收减已开票，无论是否收到钱
v_tmp.billing_amount := nvl(v_tmp.due_amount, 0) -
v_tmp.cf_billing_amount;
End If;
If nvl(v_tmp.billing_amount, 0) > 0 Then
--发票金额为0，则不生成
v_tmp.cashflow_id := p_cashflow.cashflow_id;
v_tmp.record_id := invoice_get_id;
v_tmp.contract_id := p_cashflow.contract_id;
--v_tmp.billing_object := p_divide_type;
v_tmp.times := p_cashflow.times;
invoicetmp_insert(v_tmp);
End If;
End;
--现金流处理之前
Procedure cf_type_before_handle(p_tmp In Out acr_invoice_create_tmp%Rowtype,
p_cashflow con_contract_cashflow%Rowtype,
p_billmethodprop hls_billing_method%Rowtype) Is
Begin
p_tmp.cf_type := p_cashflow.cf_type;
p_tmp.cf_item := p_cashflow.cf_item;
p_tmp.received_amount := nvl(p_cashflow.received_amount, 0);
End;
--设备款处理cf_type=109
Procedure cf_type_109_handle(p_tmp acr_invoice_create_tmp%Rowtype,
p_cashflow con_contract_cashflow%Rowtype,
p_contract con_contract%Rowtype,
p_billmethodprop hls_billing_method%Rowtype) Is
v_tmp acr_invoice_create_tmp%Rowtype;
Begin
v_tmp := p_tmp;
cf_type_before_handle(p_tmp => v_tmp,
p_cashflow => p_cashflow,
p_billmethodprop => p_billmethodprop);
If p_billmethodprop.equipment_billing_flag = 'Y' Then
v_tmp.due_amount := nvl(p_cashflow.due_amount, 0);
v_tmp.cf_billing_amount := nvl(p_cashflow.billing_amount, 0);
--获取 invoice kind
If p_billmethodprop.equipment_invoice_kind_tp = '10' Then
v_tmp.invoice_kind := get_invoicekind_by_taxpayer(p_contract_id => p_contract.contract_id);
Elsif p_billmethodprop.equipment_invoice_kind_tp = '20' Then
v_tmp.invoice_kind := p_billmethodprop.equipment_invoice_kind;
End If;
--获取 tax rate
If p_billmethodprop.equipment_tax_rate_tp = '10' Then
If p_cashflow.tax_type_id Is Not Null Or
p_cashflow.tax_type_rate Is Not Null Then
v_tmp.tax_type_id := p_cashflow.tax_type_id;
v_tmp.tax_type_rate := p_cashflow.tax_type_rate;
Else
v_tmp.tax_type_id := p_contract.tax_type_id;
End If;
Elsif p_billmethodprop.equipment_tax_rate_tp = '20' Then
v_tmp.tax_type_id := p_billmethodprop.equipment_tax_rate;
End If;
If v_tmp.tax_type_id Is Not Null And v_tmp.tax_type_rate Is Null Then
Select tax_type_rate
Into v_tmp.tax_type_rate
From fnd_tax_type_codes
Where tax_type_id = v_tmp.tax_type_id;
End If;
cf_type_after_handle(p_tmp => v_tmp,
p_cashflow => p_cashflow,
p_billmethodprop => p_billmethodprop);
End If;
End;
--利息处理cf_type=101
Procedure cf_type_101_handle(p_tmp acr_invoice_create_tmp%Rowtype,
p_cashflow con_contract_cashflow%Rowtype,
p_contract con_contract%Rowtype,
p_billmethodprop hls_billing_method%Rowtype) Is
v_tmp acr_invoice_create_tmp%Rowtype;
Begin
v_tmp := p_tmp;
cf_type_before_handle(p_tmp => v_tmp,
p_cashflow => p_cashflow,
p_billmethodprop => p_billmethodprop);
If p_billmethodprop.interest_billing_flag = 'Y' Then
v_tmp.due_amount := nvl(p_cashflow.interest, 0);
v_tmp.cf_billing_amount := nvl(p_cashflow.billing_interest, 0);
v_tmp.received_amount := nvl(p_cashflow.received_interest, 0);
v_tmp.cf_type := 101;
v_tmp.cf_item := 101;
--获取 invoice kind
If p_billmethodprop.interest_invoice_kind_tp = '10' Then
v_tmp.invoice_kind := get_invoicekind_by_taxpayer(p_contract_id => p_contract.contract_id);
Elsif p_billmethodprop.interest_invoice_kind_tp = '20' Then
v_tmp.invoice_kind := p_billmethodprop.interest_invoice_kind;
End If;
--获取 tax rate
If p_billmethodprop.interest_tax_rate_tp = '10' Then
If p_cashflow.tax_type_id Is Not Null Or
p_cashflow.tax_type_rate Is Not Null Then
v_tmp.tax_type_id := p_cashflow.tax_type_id;
v_tmp.tax_type_rate := p_cashflow.tax_type_rate;
Else
v_tmp.tax_type_id := p_contract.tax_type_id;
End If;
Elsif p_billmethodprop.interest_tax_rate_tp = '20' Then
v_tmp.tax_type_id := p_billmethodprop.interest_tax_rate;
End If;
If v_tmp.tax_type_id Is Not Null And v_tmp.tax_type_rate Is Null Then
Select tax_type_rate
Into v_tmp.tax_type_rate
From fnd_tax_type_codes
Where tax_type_id = v_tmp.tax_type_id;
End If;
cf_type_after_handle(p_tmp => v_tmp,
p_cashflow => p_cashflow,
p_billmethodprop => p_billmethodprop);
End If;
End;
--本金处理cf_type=100
Procedure cf_type_100_handle(p_tmp acr_invoice_create_tmp%Rowtype,
p_cashflow con_contract_cashflow%Rowtype,
p_contract con_contract%Rowtype,
p_billmethodprop hls_billing_method%Rowtype) Is
v_tmp acr_invoice_create_tmp%Rowtype;
Begin
v_tmp := p_tmp;
cf_type_before_handle(p_tmp => v_tmp,
p_cashflow => p_cashflow,
p_billmethodprop => p_billmethodprop);
If p_billmethodprop.principal_billing_flag = 'Y' Then
v_tmp.due_amount := nvl(p_cashflow.principal, 0);
v_tmp.cf_billing_amount := nvl(p_cashflow.billing_principal, 0);
v_tmp.received_amount := nvl(p_cashflow.received_principal, 0);
v_tmp.cf_type := 100;
v_tmp.cf_item := 100;
--获取 invoice kind
If p_billmethodprop.principal_invoice_kind_tp = '10' Then
v_tmp.invoice_kind := get_invoicekind_by_taxpayer(p_contract_id => p_contract.contract_id);
Elsif p_billmethodprop.principal_invoice_kind_tp = '20' Then
v_tmp.invoice_kind := p_billmethodprop.principal_invoice_kind;
End If;
--获取 tax rate
If p_billmethodprop.principal_tax_rate_tp = '10' Then
If p_cashflow.tax_type_id Is Not Null Or
p_cashflow.tax_type_rate Is Not Null Then
v_tmp.tax_type_id := p_cashflow.tax_type_id;
v_tmp.tax_type_rate := p_cashflow.tax_type_rate;
Else
v_tmp.tax_type_id := p_contract.tax_type_id;
End If;
Elsif p_billmethodprop.principal_tax_rate_tp = '20' Then
v_tmp.tax_type_id := p_billmethodprop.principal_tax_rate;
End If;
If v_tmp.tax_type_id Is Not Null And v_tmp.tax_type_rate Is Null Then
Select tax_type_rate
Into v_tmp.tax_type_rate
From fnd_tax_type_codes
Where tax_type_id = v_tmp.tax_type_id;
End If;
cf_type_after_handle(p_tmp => v_tmp,
p_cashflow => p_cashflow,
p_billmethodprop => p_billmethodprop);
End If;
End;
--租金处理cf_type=1
Procedure cf_type_1_handle(p_tmp acr_invoice_create_tmp%Rowtype,
p_cashflow con_contract_cashflow%Rowtype,
p_contract con_contract%Rowtype,
p_billmethodprop hls_billing_method%Rowtype) Is
v_tmp acr_invoice_create_tmp%Rowtype;
r_hls_bp_master hls_bp_master%Rowtype;
Begin
v_tmp := p_tmp;
If p_billmethodprop.rental_billing_flag = 'Y' Then
-- 按租金开票
cf_type_before_handle(p_tmp => v_tmp,
p_cashflow => p_cashflow,
p_billmethodprop => p_billmethodprop);
v_tmp.due_amount := nvl(p_cashflow.due_amount, 0);
v_tmp.received_amount := nvl(p_cashflow.received_amount, 0);
v_tmp.cf_billing_amount := nvl(p_cashflow.billing_amount, 0);
--获取 invoice kind
Select *
Into r_hls_bp_master
From hls_bp_master m
Where m.bp_id = v_tmp.bp_id;
If nvl(r_hls_bp_master.taxpayer_type, 'SMALL_SCALE_TAXPAYER') =
'GENERAL_TAXPAYER' Then
--普通纳税人：专票
v_tmp.invoice_kind := '0';
Elsif nvl(r_hls_bp_master.taxpayer_type, 'SMALL_SCALE_TAXPAYER') =
'SMALL_SCALE_TAXPAYER' Then
--小规模纳税人：普票
v_tmp.invoice_kind := '2';
End If;
/* if p_billmethodprop.rental_invoice_kind_tp = '10' then
v_tmp.invoice_kind := get_invoicekind_by_taxpayer(p_contract_id => p_contract.contract_id);
elsif p_billmethodprop.rental_invoice_kind_tp = '20' then
v_tmp.invoice_kind := p_billmethodprop.rental_invoice_kind;
end if;*/
--获取 tax rate
If p_billmethodprop.rental_tax_rate_tp = '10' Then
If p_cashflow.tax_type_id Is Not Null Or
p_cashflow.tax_type_rate Is Not Null Then
v_tmp.tax_type_id := p_cashflow.tax_type_id;
v_tmp.tax_type_rate := p_cashflow.tax_type_rate;
Else
v_tmp.tax_type_id := p_contract.tax_type_id;
End If;
Elsif p_billmethodprop.rental_tax_rate_tp = '20' Then
v_tmp.tax_type_id := p_billmethodprop.rental_tax_rate;
End If;
If v_tmp.tax_type_id Is Not Null And v_tmp.tax_type_rate Is Null Then
Select tax_type_rate
Into v_tmp.tax_type_rate
From fnd_tax_type_codes
Where tax_type_id = v_tmp.tax_type_id;
End If;
cf_type_after_handle(p_tmp => v_tmp,
p_cashflow => p_cashflow,
p_billmethodprop => p_billmethodprop);
Else
--本金开票
If p_billmethodprop.principal_billing_flag = 'Y' Then
cf_type_100_handle(p_tmp, p_cashflow, p_contract, p_billmethodprop);
End If;
--利息开票
If p_billmethodprop.interest_billing_flag = 'Y' Then
cf_type_101_handle(p_tmp, p_cashflow, p_contract, p_billmethodprop);
End If;
End If;
End;
--利息处理cf_type=101 addby wuts
Procedure cf_item_301_handle(p_tmp acr_invoice_create_tmp%Rowtype,
p_cashflow con_contract_cashflow%Rowtype,
p_contract con_contract%Rowtype,
p_billmethodprop hls_billing_method%Rowtype) Is
v_tmp acr_invoice_create_tmp%Rowtype;
Begin
v_tmp := p_tmp;
cf_type_before_handle(p_tmp => v_tmp,
p_cashflow => p_cashflow,
p_billmethodprop => p_billmethodprop);
If p_billmethodprop.interest_billing_flag = 'Y' Then
v_tmp.due_amount := nvl(p_cashflow.due_amount, 0);
v_tmp.cf_billing_amount := nvl(p_cashflow.billing_amount, 0);
v_tmp.received_amount := nvl(p_cashflow.received_amount, 0);
v_tmp.cf_type := 301;
v_tmp.cf_item := 301;
--获取 invoice kind
/* if p_billmethodprop.interest_invoice_kind_tp = '10' then
v_tmp.invoice_kind := get_invoicekind_by_taxpayer(p_contract_id => p_contract.contract_id);
elsif p_billmethodprop.interest_invoice_kind_tp = '20' then
v_tmp.invoice_kind := p_billmethodprop.interest_invoice_kind;
end if;*/
--管理费 专用发票 modify by wuts
v_tmp.invoice_kind := '0';
--获取 tax rate
If p_billmethodprop.interest_tax_rate_tp = '10' Then
If p_cashflow.tax_type_id Is Not Null Or
p_cashflow.tax_type_rate Is Not Null Then
v_tmp.tax_type_id := p_cashflow.tax_type_id;
v_tmp.tax_type_rate := p_cashflow.tax_type_rate;
Else
v_tmp.tax_type_id := p_contract.tax_type_id;
End If;
Elsif p_billmethodprop.interest_tax_rate_tp = '20' Then
v_tmp.tax_type_id := p_billmethodprop.interest_tax_rate;
End If;
If v_tmp.tax_type_id Is Not Null And v_tmp.tax_type_rate Is Null Then
Select tax_type_rate
Into v_tmp.tax_type_rate
From fnd_tax_type_codes
Where tax_type_id = v_tmp.tax_type_id;
End If;
cf_type_after_handle(p_tmp => v_tmp,
p_cashflow => p_cashflow,
p_billmethodprop => p_billmethodprop);
End If;
End;
--租金处理cf_type=301
Procedure cf_type_301_handle(p_tmp acr_invoice_create_tmp%Rowtype,
p_cashflow con_contract_cashflow%Rowtype,
p_contract con_contract%Rowtype,
p_billmethodprop hls_billing_method%Rowtype) Is
v_tmp acr_invoice_create_tmp%Rowtype;
Begin
v_tmp := p_tmp;
If p_billmethodprop.rental_billing_flag = 'Y' Then
-- 按租金开票
cf_type_before_handle(p_tmp => v_tmp,
p_cashflow => p_cashflow,
p_billmethodprop => p_billmethodprop);
v_tmp.due_amount := nvl(p_cashflow.due_amount, 0);
v_tmp.received_amount := nvl(p_cashflow.received_amount, 0);
v_tmp.cf_billing_amount := nvl(p_cashflow.billing_amount, 0);
--获取 invoice kind
/*if p_billmethodprop.rental_invoice_kind_tp = '10' then
v_tmp.invoice_kind := get_invoicekind_by_taxpayer(p_contract_id => p_contract.contract_id);
elsif p_billmethodprop.rental_invoice_kind_tp = '20' then
v_tmp.invoice_kind := p_billmethodprop.rental_invoice_kind;
end if;
*/
--管理费 专用发票 modify by wuts
v_tmp.invoice_kind := '0';
--获取 tax rate
If p_billmethodprop.rental_tax_rate_tp = '10' Then
If p_cashflow.tax_type_id Is Not Null Or
p_cashflow.tax_type_rate Is Not Null Then
v_tmp.tax_type_id := p_cashflow.tax_type_id;
v_tmp.tax_type_rate := p_cashflow.tax_type_rate;
Else
v_tmp.tax_type_id := p_contract.tax_type_id;
End If;
Elsif p_billmethodprop.rental_tax_rate_tp = '20' Then
v_tmp.tax_type_id := p_billmethodprop.rental_tax_rate;
End If;
If v_tmp.tax_type_id Is Not Null And v_tmp.tax_type_rate Is Null Then
Select tax_type_rate
Into v_tmp.tax_type_rate
From fnd_tax_type_codes
Where tax_type_id = v_tmp.tax_type_id;
End If;
cf_type_after_handle(p_tmp => v_tmp,
p_cashflow => p_cashflow,
p_billmethodprop => p_billmethodprop);
Else
--利息开票
If p_billmethodprop.interest_billing_flag = 'Y' Then
cf_item_301_handle(p_tmp, p_cashflow, p_contract, p_billmethodprop);
End If;
End If;
End;
--end wuts
--其他处理
Procedure cf_type_others_handle(p_tmp acr_invoice_create_tmp%Rowtype,
p_cashflow con_contract_cashflow%Rowtype,
p_contract con_contract%Rowtype,
p_billmethodprop hls_billing_method%Rowtype) Is
v_tmp acr_invoice_create_tmp%Rowtype;
v_cf_method hls_billing_method_cf%Rowtype;
Begin
v_tmp := p_tmp;
cf_type_before_handle(p_tmp => v_tmp,
p_cashflow => p_cashflow,
p_billmethodprop => p_billmethodprop);
v_tmp.due_amount := nvl(p_cashflow.due_amount, 0);
v_tmp.cf_billing_amount := nvl(p_cashflow.billing_amount, 0);
Begin
Select *
Into v_cf_method
From hls_billing_method_cf
Where billing_method = p_billmethodprop.billing_method
And cf_item = p_cashflow.cf_item;
If v_cf_method.enabled_flag = 'Y' Then
--获取 invoice kind
If v_cf_method.invoice_kind_tp = '10' Then
v_tmp.invoice_kind := get_invoicekind_by_taxpayer(p_contract_id => p_contract.contract_id);
Elsif v_cf_method.invoice_kind_tp = '20' Then
v_tmp.invoice_kind := v_cf_method.invoice_kind;
End If;
--获取 tax rate
If v_cf_method.tax_rate_tp = '10' Then
If p_cashflow.tax_type_id Is Not Null Or
p_cashflow.tax_type_rate Is Not Null Then
v_tmp.tax_type_id := p_cashflow.tax_type_id;
v_tmp.tax_type_rate := p_cashflow.tax_type_rate;
Else
v_tmp.tax_type_id := p_contract.tax_type_id;
End If;
Elsif v_cf_method.tax_rate_tp = '20' Then
v_tmp.tax_type_id := v_cf_method.tax_rate;
End If;
If v_tmp.tax_type_id Is Not Null And v_tmp.tax_type_rate Is Null Then
Select tax_type_rate
Into v_tmp.tax_type_rate
From fnd_tax_type_codes
Where tax_type_id = v_tmp.tax_type_id;
End If;
cf_type_after_handle(p_tmp => v_tmp,
p_cashflow => p_cashflow,
p_billmethodprop => p_billmethodprop);
End If;
Exception
When no_data_found Then
If p_billmethodprop.other_billing_flag = 'Y' Then
--获取 invoice kind
If p_billmethodprop.other_invoice_kind_tp = '10' Then
v_tmp.invoice_kind := get_invoicekind_by_taxpayer(p_contract_id => p_contract.contract_id);
Elsif p_billmethodprop.other_invoice_kind_tp = '20' Then
v_tmp.invoice_kind := p_billmethodprop.other_invoice_kind;
End If;
--获取 tax rate
If p_billmethodprop.other_tax_rate_tp = '10' Then
If p_cashflow.tax_type_id Is Not Null Or
p_cashflow.tax_type_rate Is Not Null Then
v_tmp.tax_type_id := p_cashflow.tax_type_id;
v_tmp.tax_type_rate := p_cashflow.tax_type_rate;
Else
v_tmp.tax_type_id := p_contract.tax_type_id;
End If;
Elsif p_billmethodprop.other_tax_rate_tp = '20' Then
v_tmp.tax_type_id := p_billmethodprop.other_tax_rate;
End If;
If v_tmp.tax_type_id Is Not Null And v_tmp.tax_type_rate Is Null Then
Select tax_type_rate
Into v_tmp.tax_type_rate
From fnd_tax_type_codes
Where tax_type_id = v_tmp.tax_type_id;
End If;
cf_type_after_handle(p_tmp => v_tmp,
p_cashflow => p_cashflow,
p_billmethodprop => p_billmethodprop);
End If;
End;
End;
Procedure cf_type_9_handle(p_tmp acr_invoice_create_tmp%Rowtype,
p_contract con_contract%Rowtype,
p_billmethodprop hls_billing_method%Rowtype) Is
v_tmp acr_invoice_create_tmp%Rowtype;
v_cf_method hls_billing_method_cf%Rowtype;
Begin
v_tmp := p_tmp;
v_tmp.record_id := invoice_get_id;
Begin
Select *
Into v_cf_method
From hls_billing_method_cf
Where billing_method = p_billmethodprop.billing_method
And cf_item = 9;
If v_cf_method.enabled_flag = 'Y' Then
--获取 invoice kind
If v_cf_method.invoice_kind_tp = '10' Then
v_tmp.invoice_kind := get_invoicekind_by_taxpayer(p_contract_id => p_contract.contract_id);
Elsif v_cf_method.invoice_kind_tp = '20' Then
v_tmp.invoice_kind := v_cf_method.invoice_kind;
End If;
--获取 tax rate
If v_cf_method.tax_rate_tp = '10' Then
v_tmp.tax_type_id := p_contract.tax_type_id;
Elsif v_cf_method.tax_rate_tp = '20' Then
v_tmp.tax_type_id := v_cf_method.tax_rate;
End If;
If v_tmp.tax_type_id Is Not Null And v_tmp.tax_type_rate Is Null Then
Select tax_type_rate
Into v_tmp.tax_type_rate
From fnd_tax_type_codes
Where tax_type_id = v_tmp.tax_type_id;
End If;
invoicetmp_insert(v_tmp);
End If;
Exception
When no_data_found Then
If p_billmethodprop.other_billing_flag = 'Y' Then
--获取 invoice kind
If p_billmethodprop.other_invoice_kind_tp = '10' Then
v_tmp.invoice_kind := get_invoicekind_by_taxpayer(p_contract_id => p_contract.contract_id);
Elsif p_billmethodprop.other_invoice_kind_tp = '20' Then
v_tmp.invoice_kind := p_billmethodprop.other_invoice_kind;
End If;
--获取 tax rate
If p_billmethodprop.other_tax_rate_tp = '10' Then
v_tmp.tax_type_id := p_contract.tax_type_id;
Elsif p_billmethodprop.other_tax_rate_tp = '20' Then
v_tmp.tax_type_id := p_billmethodprop.other_tax_rate;
End If;
If v_tmp.tax_type_id Is Not Null And v_tmp.tax_type_rate Is Null Then
Select tax_type_rate
Into v_tmp.tax_type_rate
From fnd_tax_type_codes
Where tax_type_id = v_tmp.tax_type_id;
End If;
invoicetmp_insert(v_tmp);
End If;
End;
End;
-- add by niminmin
--罚息开票 不按现金流开票
Procedure split_penalty_cashflow(p_contract_id Number,
p_journal_header_id Number,
p_journal_line_id Number,
p_billing_amount Number,
p_session_id Number,
p_bp_id Number,
p_bp_name Varchar2,
p_invoice_title Varchar2,
p_invoice_bp_address_phone_num Varchar2,
p_invoice_bp_bank_account Varchar2,
p_invoice_bp_tax_registry_num Varchar2,
p_head_description Varchar2,
p_currency Varchar2,
p_exchange_rate_type Varchar2,
p_exchange_rate_quotation Varchar2,
p_exchange_rate Number,
p_user_id Number) Is
v_contract con_contract%Rowtype;
v_billmethodprop hls_billing_method%Rowtype;
v_paras acr_invoice_create_tmp%Rowtype;
v_account_code Varchar2(200);
e_bill_status Exception;
v_programe_name Varchar2(255) := upper('split_penalty_cashflow');
Begin
v_contract := contract_get(p_contract_id);
Begin
Select hl.amount_cr
Into v_paras.tax_amount
From gld_accounts_vl v, hls_journal_detail hl, con_contract ct
Where hl.account_id = v.account_id
And hl.journal_header_id = p_journal_header_id
And v.account_code = '2221001020'
And ct.contract_id = p_contract_id
And ct.contract_number = hl.reference3;
Exception
When no_data_found Then
Raise e_bill_status;
End;
v_billmethodprop := get_billmethod_rec(v_contract.billing_method);
v_paras.creation_date := Sysdate;
v_paras.created_by := p_user_id;
v_paras.last_update_date := Sysdate;
v_paras.last_updated_by := p_user_id;
v_paras.invoice_title := p_invoice_title;
v_paras.bp_id := p_bp_id;
v_paras.bp_name := p_bp_name;
v_paras.invoice_bp_address_phone_num := p_invoice_bp_address_phone_num;
v_paras.invoice_bp_bank_account := p_invoice_bp_bank_account;
v_paras.head_description := p_head_description;
v_paras.session_id := p_session_id;
v_paras.checked_flag := 'N';
v_paras.invoice_bp_tax_registry_num := p_invoice_bp_tax_registry_num;
v_paras.currency := p_currency;
v_paras.exchange_rate_type := p_exchange_rate_type;
v_paras.exchange_rate_quotation := p_exchange_rate_quotation;
v_paras.exchange_rate := p_exchange_rate;
v_paras.journal_header_id := p_journal_header_id;
v_paras.journal_line_id := p_journal_line_id;
v_paras.due_amount := p_billing_amount;
v_paras.cf_billing_amount := p_billing_amount;
v_paras.contract_id := p_contract_id;
v_paras.cf_item := 9;
v_paras.cf_type := 9;
cf_type_9_handle(v_paras, v_contract, v_billmethodprop);
Exception
When e_bill_status Then
sys_raise_app_error_pkg.raise_sys_others_error(p_message => '未找到对应销项税，请联系管理员！',
p_created_by => p_user_id,
p_package_name => g_package_name,
p_procedure_function_name => v_programe_name);
raise_application_error(sys_raise_app_error_pkg.c_error_number,
sys_raise_app_error_pkg.g_err_line_id);
When Others Then
sys_raise_app_error_pkg.raise_sys_others_error(p_message => dbms_utility.format_error_backtrace || ' ' ||
Sqlerrm,
p_created_by => p_user_id,
p_package_name => g_package_name,
p_procedure_function_name => v_programe_name);
raise_application_error(sys_raise_app_error_pkg.c_error_number,
sys_raise_app_error_pkg.g_err_line_id);
End;
Procedure split_cashflow(p_cashflow_id Number,
p_session_id Number,
p_bp_id Number,
p_bp_name Varchar2,
p_invoice_title Varchar2,
p_invoice_bp_address_phone_num Varchar2,
p_invoice_bp_bank_account Varchar2,
p_invoice_bp_tax_registry_num Varchar2,
p_head_description Varchar2,
p_currency Varchar2,
p_exchange_rate_type Varchar2,
p_exchange_rate_quotation Varchar2,
p_exchange_rate Number,
p_user_id Number) Is
v_cashflow con_contract_cashflow%Rowtype;
v_contract con_contract%Rowtype;
v_billmethodprop hls_billing_method%Rowtype;
v_paras acr_invoice_create_tmp%Rowtype;
e_bill_status Exception;
v_programe_name Varchar2(255) := upper('split_cashflow');
Begin
v_cashflow := cashflow_get_record(p_cashflow_id);
v_contract := contract_get(v_cashflow.contract_id);
If v_cashflow.billing_status = g_bill_status_full Then
Raise e_bill_status;
End If;
v_billmethodprop := get_billmethod_rec(v_contract.billing_method);
v_paras.creation_date := Sysdate;
v_paras.created_by := p_user_id;
v_paras.last_update_date := Sysdate;
v_paras.last_updated_by := p_user_id;
v_paras.invoice_title := p_invoice_title;
v_paras.bp_id := p_bp_id;
v_paras.bp_name := p_bp_name;
v_paras.invoice_bp_address_phone_num := p_invoice_bp_address_phone_num;
v_paras.invoice_bp_bank_account := p_invoice_bp_bank_account;
v_paras.head_description := p_head_description;
v_paras.session_id := p_session_id;
v_paras.checked_flag := 'N';
v_paras.invoice_bp_tax_registry_num := p_invoice_bp_tax_registry_num;
v_paras.currency := p_currency;
v_paras.exchange_rate_type := p_exchange_rate_type;
v_paras.exchange_rate_quotation := p_exchange_rate_quotation;
v_paras.exchange_rate := p_exchange_rate;
If v_contract.currency != 'CNY' Then
v_cashflow.due_amount := v_cashflow.due_amount_cny;
v_cashflow.principal := v_cashflow.principal_cny;
v_cashflow.interest := v_cashflow.interest_cny;
v_cashflow.received_amount := v_cashflow.received_amount_cny;
v_cashflow.received_principal := v_cashflow.received_principal_cny;
v_cashflow.received_interest := v_cashflow.received_interest_cny;
End If;
--管理费
If v_cashflow.cf_type = 301 Then
cf_type_301_handle(v_paras, v_cashflow, v_contract, v_billmethodprop);
--租金
Elsif v_cashflow.cf_type = 1 Then
cf_type_1_handle(v_paras, v_cashflow, v_contract, v_billmethodprop);
Elsif v_cashflow.cf_type = 100 Then
cf_type_100_handle(v_paras, v_cashflow, v_contract, v_billmethodprop);
Elsif v_cashflow.cf_type = 101 Then
cf_type_101_handle(v_paras, v_cashflow, v_contract, v_billmethodprop);
Elsif v_cashflow.cf_type = 109 Then
cf_type_109_handle(v_paras, v_cashflow, v_contract, v_billmethodprop);
Else
cf_type_others_handle(v_paras,
v_cashflow,
v_contract,
v_billmethodprop);
End If;
Exception
When e_bill_status Then
Null;
When Others Then
sys_raise_app_error_pkg.raise_sys_others_error(p_message => dbms_utility.format_error_backtrace || ' ' ||
Sqlerrm,
p_created_by => p_user_id,
p_package_name => g_package_name,
p_procedure_function_name => v_programe_name);
raise_application_error(sys_raise_app_error_pkg.c_error_number,
sys_raise_app_error_pkg.g_err_line_id);
End;
Procedure invoicetmp_update(p_record_id Number,
p_invoice_kind Varchar2,
p_billing_amount Number,
p_tax_amount Number Default Null,
p_product_name Varchar2,
p_user_id Number) Is
Begin
Update acr_invoice_create_tmp t
Set t.invoice_kind = p_invoice_kind,
t.billing_amount = p_billing_amount,
t.product_name = p_product_name,
t.tax_amount = p_tax_amount,
t.checked_flag = 'Y',
t.last_updated_by = p_user_id,
t.last_update_date = Sysdate
Where t.record_id = p_record_id;
End;
Procedure create_invoice_check(p_session_id In Number,
p_company_id In Number,
p_user_id In Number) Is
v_exists Varchar2(1) := 'N';
e_leaseback_invoice_kind_err Exception;
e_receipt_cf_type_err Exception;
e_bp_class_taxpayer_err Exception;
e_vat_flag_err Exception;
Begin
/*begin
select 'Y'
into v_exists
from dual
where exists (select 1
from acr_invoice_create_tmp t, con_contract c
where t.session_id = p_session_id
and t.checked_flag = 'Y'
and t.contract_id = c.contract_id
and c.business_type = 'LEASEBACK'
and t.invoice_kind = '0'
and t.cf_type in (100, 2));
raise e_leaseback_invoice_kind_err;--回租业务本金部分不能开具增值税专用发票
exception
when no_data_found then
null;
end;*/
Begin
Select 'Y'
Into v_exists
From dual
Where Exists (Select 1
From acr_invoice_create_tmp t
Where t.session_id = p_session_id
And t.checked_flag = 'Y'
And t.invoice_kind <> 'RECEIPT'
And t.cf_type = 5);
Raise e_receipt_cf_type_err; --保证金只能开收据
Exception
When no_data_found Then
Null;
End;
Begin
Select 'Y'
Into v_exists
From dual
Where Exists
(Select 1
From acr_invoice_create_tmp t, hls_bp_master b
Where t.session_id = p_session_id
And t.checked_flag = 'Y'
And t.bp_id = b.bp_id
And t.invoice_kind = '0'
And (b.bp_class = 'NP' Or
b.taxpayer_type = 'SMALL_SCALE_TAXPAYER'));
Raise e_bp_class_taxpayer_err;
--合同BP为自然人hls_bp_master.bp_class=NP、或纳税人类型为小规模纳税人hls_bp_master. taxpayer_type=SMALL_SCALE_TAXPAYER，不能开增值税专用发票
Exception
When no_data_found Then
Null;
End;
Begin
Select 'Y'
Into v_exists
From dual
Where Exists (Select 1
From acr_invoice_create_tmp t,
con_contract c,
fnd_tax_type_codes tt
Where t.session_id = p_session_id
And t.checked_flag = 'Y'
And t.contract_id = c.contract_id
And c.tax_type_id = tt.tax_type_id
And nvl(tt.vat_flag, 'N') = 'N'
And t.invoice_kind In ('0', '2'));
Raise e_vat_flag_err;
--合同税种不为增值税VAT_FLAG=N，不能开增值税发票（InvKind≠0，2），只能开营业税发票或收据
Exception
When no_data_found Then
Null;
End;
Exception
When e_leaseback_invoice_kind_err Then
sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'ACR510_LEASEBACK_INVOICE_KIND_ERROR',
p_created_by => p_user_id,
p_package_name => 'ACR_INVOICE_PKG',
p_procedure_function_name => 'create_invoice_check');
raise_application_error(sys_raise_app_error_pkg.c_error_number,
sys_raise_app_error_pkg.g_err_line_id);
When e_receipt_cf_type_err Then
sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'ACR510_RECEIPT_CF_TYPE_ERROR',
p_created_by => p_user_id,
p_package_name => 'ACR_INVOICE_PKG',
p_procedure_function_name => 'create_invoice_check');
raise_application_error(sys_raise_app_error_pkg.c_error_number,
sys_raise_app_error_pkg.g_err_line_id);
When e_bp_class_taxpayer_err Then
sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'ACR510_CREATE_INVOICE_BP_CLASS_TAXPAYER_ERROR',
p_created_by => p_user_id,
p_package_name => 'ACR_INVOICE_PKG',
p_procedure_function_name => 'create_invoice_check');
raise_application_error(sys_raise_app_error_pkg.c_error_number,
sys_raise_app_error_pkg.g_err_line_id);
When e_vat_flag_err Then
sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'ACR510_CREATE_INVOICE_VAT_FLAG_ERROR',
p_created_by => p_user_id,
p_package_name => 'ACR_INVOICE_PKG',
p_procedure_function_name => 'create_invoice_check');
raise_application_error(sys_raise_app_error_pkg.c_error_number,
sys_raise_app_error_pkg.g_err_line_id);
End;
Procedure check_invoice_date(p_company_id In Number,
p_invoice_data In Date,
p_accounting_date In Date,
p_period_name Out Varchar,
p_internal_period_num Out Number) Is
Begin
Select a.period_name, a.internal_period_num
Into p_period_name, p_internal_period_num
From gld_period_status a, gld_periods b
Where a.company_id = p_company_id
And a.period_set_code = b.period_set_code
And a.period_name = b.period_name
And p_accounting_date Between b.start_date And b.end_date
And b.adjustment_flag = 'N';
-- and a.period_status_code = 'O';
Exception
When no_data_found Then
Raise e_period_not_found;
End;
--航天金税 税额检查
Procedure check_htjs_tolerance(p_invoice_hd_rec acr_invoice_hd%Rowtype,
p_tax_type_rate Number) Is
v_hd_tax_amt Number;
Begin
Null;
/*v_hd_tax_amt := round(p_invoice_hd_rec.total_amount /
(1 + p_tax_type_rate) * p_tax_type_rate,
2);
if abs(v_hd_tax_amt - p_invoice_hd_rec.tax_amount) > g_htjs_tolerance then
raise e_htjs_tolerance_error;
end if;*/
End;
--更新发票头上的4个组织字段
Procedure update_org_info(p_invoice_hd_rec In Out acr_invoice_hd%Rowtype) Is
v_penalty_count Number;
Begin
Select Count(1)
Into v_penalty_count
From acr_invoice_ln
Where invoice_hd_id = p_invoice_hd_rec.invoice_hd_id
And cf_item = 9;
If v_penalty_count < 1 Then
Begin
Select c.billing_method
Into p_invoice_hd_rec.billing_method
From con_contract_cashflow cf, con_contract c, acr_invoice_ln l
Where cf.cashflow_id = l.cashflow_id
And cf.contract_id = c.contract_id
And l.invoice_hd_id = p_invoice_hd_rec.invoice_hd_id
Group By c.billing_method;
Exception
When too_many_rows Then
p_invoice_hd_rec.billing_method := Null;
End;
Begin
Select c.lease_organization
Into p_invoice_hd_rec.lease_organization
From con_contract_cashflow cf, con_contract c, acr_invoice_ln l
Where cf.cashflow_id = l.cashflow_id
And cf.contract_id = c.contract_id
And l.invoice_hd_id = p_invoice_hd_rec.invoice_hd_id
Group By c.lease_organization;
Exception
When too_many_rows Then
p_invoice_hd_rec.lease_organization := Null;
End;
Begin
Select c.lease_channel
Into p_invoice_hd_rec.lease_channel
From con_contract_cashflow cf, con_contract c, acr_invoice_ln l
Where cf.cashflow_id = l.cashflow_id
And cf.contract_id = c.contract_id
And l.invoice_hd_id = p_invoice_hd_rec.invoice_hd_id
Group By c.lease_channel;
Exception
When too_many_rows Then
p_invoice_hd_rec.lease_channel := Null;
End;
Begin
Select c.division
Into p_invoice_hd_rec.division
From con_contract_cashflow cf, con_contract c, acr_invoice_ln l
Where cf.cashflow_id = l.cashflow_id
And cf.contract_id = c.contract_id
And l.invoice_hd_id = p_invoice_hd_rec.invoice_hd_id
Group By c.division;
Exception
When too_many_rows Then
p_invoice_hd_rec.division := Null;
End;
Begin
Select c.project_id
Into p_invoice_hd_rec.project_id
From con_contract_cashflow cf, con_contract c, acr_invoice_ln l
Where cf.cashflow_id = l.cashflow_id
And cf.contract_id = c.contract_id
And l.invoice_hd_id = p_invoice_hd_rec.invoice_hd_id
Group By c.project_id;
Exception
When too_many_rows Then
p_invoice_hd_rec.project_id := Null;
End;
Begin
Select cf.contract_id
Into p_invoice_hd_rec.contract_id
From con_contract_cashflow cf, acr_invoice_ln l
Where cf.cashflow_id = l.cashflow_id
And l.invoice_hd_id = p_invoice_hd_rec.invoice_hd_id
Group By cf.contract_id;
Exception
When too_many_rows Then
p_invoice_hd_rec.contract_id := Null;
End;
End If;
End;
--更新发票头上的字段
Procedure update_hd_info(p_invoice_hd_rec In Out Nocopy acr_invoice_hd%Rowtype) Is
v_total_ln_tax_amt Number;
v_total_ln_amt Number;
v_tax_type_rate Number;
Begin
Select Sum(total_amount), Sum(tax_amount), Min(tax_type_rate)
Into v_total_ln_amt, v_total_ln_tax_amt, v_tax_type_rate
From acr_invoice_ln
Where invoice_hd_id = p_invoice_hd_rec.invoice_hd_id;
p_invoice_hd_rec.tax_amount := v_total_ln_tax_amt;
p_invoice_hd_rec.total_amount := v_total_ln_amt;
If p_invoice_hd_rec.description Is Null Then
p_invoice_hd_rec.description := acr_invoice_desc_custom_pkg.get_acr_invoice_head_desc(p_invoice_hd_id => p_invoice_hd_rec.invoice_hd_id,
p_company_id => p_invoice_hd_rec.company_id);
End If;
check_htjs_tolerance(p_invoice_hd_rec => p_invoice_hd_rec,
p_tax_type_rate => v_tax_type_rate);
update_org_info(p_invoice_hd_rec => p_invoice_hd_rec);
End;
--插入单据流
Procedure insert_doc_flow(p_invoice_hd_rec In Out acr_invoice_hd%Rowtype) Is
Begin
For c_ln In (Select a.invoice_ln_id,
c.document_category,
c.contract_number,
cf.cashflow_id,
c.contract_id
From acr_invoice_ln a,
con_contract_cashflow cf,
con_contract c
Where a.invoice_hd_id = p_invoice_hd_rec.invoice_hd_id
And a.cashflow_id = cf.cashflow_id
And cf.contract_id = c.contract_id) Loop
hls_document_flow_pkg.insert_document_flow(p_doc_category => p_invoice_hd_rec.document_category,
p_doc_id => p_invoice_hd_rec.invoice_hd_id,
p_doc_line_id => c_ln.invoice_ln_id,
p_doc_number => p_invoice_hd_rec.invoice_number,
p_source_doc_category => c_ln.document_category,
p_source_doc_id => c_ln.contract_id,
p_source_doc_line_id => c_ln.cashflow_id,
p_source_doc_number => c_ln.contract_number,
p_user_id => p_invoice_hd_rec.created_by);
End Loop;
End;
Procedure insert_trx_user_authority(p_invoice_hd_rec In Out acr_invoice_hd%Rowtype,
p_user_id In Number) Is
Begin
aut_document_authority_pkg.insert_trx_user_authority(p_company_id => p_invoice_hd_rec.company_id,
p_owner_user_id => p_invoice_hd_rec.owner_user_id,
p_start_date => trunc(Sysdate),
p_end_date => to_date('30000101',
'yyyymmdd'),
p_user_id => p_user_id,
p_trx_category => p_invoice_hd_rec.document_category,
p_trx_id => p_invoice_hd_rec.invoice_hd_id);
End;
--创建完的发票，整张检查
Procedure created_invoice_check(p_invoice_hd_rec In Out acr_invoice_hd%Rowtype,
p_user_id In Number,
p_role_id In Number) Is
Begin
update_hd_info(p_invoice_hd_rec => p_invoice_hd_rec);
Update acr_invoice_hd
Set Row = p_invoice_hd_rec
Where invoice_hd_id = p_invoice_hd_rec.invoice_hd_id;
insert_doc_flow(p_invoice_hd_rec => p_invoice_hd_rec);
insert_trx_user_authority(p_invoice_hd_rec => p_invoice_hd_rec,
p_user_id => p_user_id);
End;
Procedure insert_invoice_hd(p_invoice_hd_rec acr_invoice_hd%Rowtype) Is
Begin
Insert Into acr_invoice_hd Values p_invoice_hd_rec;
End;
Procedure insert_invoice_ln(p_invoice_ln_rec acr_invoice_ln%Rowtype) Is
Begin
Insert Into acr_invoice_ln Values p_invoice_ln_rec;
End;
Function get_invoice_number(p_document_type Varchar2,
p_transaction_date Date,
p_company_id Number,
p_user_id Number) Return Varchar2 Is
v_no Varchar2(100);
Begin
/* haha_test_pkg.log(p_document_type);
haha_test_pkg.log(p_company_id);*/
v_no := fnd_code_rule_pkg.get_rule_next_auto_num(p_document_category => g_document_category,
p_document_type => p_document_type,
p_company_id => p_company_id,
p_operation_unit_id => Null,
p_operation_date => p_transaction_date,
p_created_by => p_user_id);
If v_no = fnd_code_rule_pkg.c_error Then
Raise e_get_invoice_number_err;
End If;
Return v_no;
End;
Procedure get_invoice_number(p_document_type In Out Varchar2,
p_transaction_date In Date,
p_company_id In Number,
p_user_id In Number,
p_document_number Out Varchar2,
p_business_type Out Varchar2,
p_ln_step_length Out Number) Is
v_document_type Varchar2(30);
Begin
If p_document_type Is Null Then
v_document_type := 'STD';
Else
v_document_type := p_document_type;
End If;
Select business_type, ln_step_length
Into p_business_type, p_ln_step_length
From hls_document_type
Where document_category = g_document_category
And document_type = v_document_type;
p_document_number := get_invoice_number(p_document_type => v_document_type,
p_transaction_date => p_transaction_date,
p_company_id => p_company_id,
p_user_id => p_user_id);
p_document_type := v_document_type;
End;
Procedure update_cashflow_ln(p_invoice_hd_rec acr_invoice_hd%Rowtype,
p_invoice_ln_rec acr_invoice_ln%Rowtype,
p_user_id Number) Is
r_cashflow_rec con_contract_cashflow%Rowtype;
v_contract_currency con_contract.currency%Type;
r_billing_method hls_billing_method%Rowtype;
v_billing_amount con_contract_cashflow.billing_amount%Type;
v_billing_principal con_contract_cashflow.billing_principal%Type;
v_billing_interest con_contract_cashflow.billing_interest%Type;
v_billing_status con_contract_cashflow.billing_status%Type;
Begin
If p_invoice_ln_rec.cashflow_id Is Null Then
Return;
End If;
Begin
Select *
Into r_billing_method
From hls_billing_method
Where billing_method =
(Select billing_method
From con_contract c, con_contract_cashflow ccc
Where ccc.cashflow_id = p_invoice_ln_rec.cashflow_id
And ccc.contract_id = c.contract_id);
Exception
When no_data_found Then
Return;
End;
Select *
Into r_cashflow_rec
From con_contract_cashflow
Where cashflow_id = p_invoice_ln_rec.cashflow_id
For Update Nowait;
Select currency
Into v_contract_currency
From con_contract
Where contract_id = r_cashflow_rec.contract_id;
If p_invoice_hd_rec.currency = 'CNY' And v_contract_currency <> 'CNY' Then
r_cashflow_rec.due_amount := r_cashflow_rec.due_amount_cny;
r_cashflow_rec.principal := r_cashflow_rec.principal_cny;
r_cashflow_rec.interest := r_cashflow_rec.interest_cny;
End If;
If p_invoice_ln_rec.cf_type = 100 Then
--本金cf
v_billing_principal := nvl(r_cashflow_rec.billing_principal, 0) +
nvl(p_invoice_ln_rec.total_amount, 0);
v_billing_interest := r_cashflow_rec.billing_interest;
v_billing_amount := nvl(r_cashflow_rec.billing_amount, 0) +
nvl(p_invoice_ln_rec.total_amount, 0);
If v_billing_principal > r_cashflow_rec.principal Then
Raise e_billing_amt_over_err;
End If;
If r_billing_method.rental_billing_flag = 'Y' Or
(r_billing_method.principal_billing_flag = 'Y' And
r_billing_method.interest_billing_flag = 'Y') Then
--本+利 or 租金
If v_billing_amount = r_cashflow_rec.due_amount Then
v_billing_status := g_bill_status_full;
Elsif v_billing_amount = 0 Then
v_billing_status := g_bill_status_not;
Else
v_billing_status := g_bill_status_partial;
End If;
Elsif r_billing_method.principal_billing_flag = 'Y' And
nvl(r_billing_method.interest_billing_flag, 'N') = 'N' Then
--本
If v_billing_principal = r_cashflow_rec.principal Then
v_billing_status := g_bill_status_full;
Elsif v_billing_principal = 0 Then
v_billing_status := g_bill_status_not;
Else
v_billing_status := g_bill_status_partial;
End If;
Elsif nvl(r_billing_method.principal_billing_flag, 'N') = 'N' And
r_billing_method.interest_billing_flag = 'Y' Then
--利
If v_billing_interest = r_cashflow_rec.interest Then
v_billing_status := g_bill_status_full;
Elsif v_billing_interest = 0 Then
v_billing_status := g_bill_status_not;
Else
v_billing_status := g_bill_status_partial;
End If;
End If;
Elsif p_invoice_ln_rec.cf_type = 101 Then
--利息cf
v_billing_principal := r_cashflow_rec.billing_principal;
v_billing_interest := nvl(r_cashflow_rec.billing_interest, 0) +
nvl(p_invoice_ln_rec.total_amount, 0);
v_billing_amount := nvl(r_cashflow_rec.billing_amount, 0) +
nvl(p_invoice_ln_rec.total_amount, 0);
If v_billing_interest > r_cashflow_rec.interest Then
Raise e_billing_amt_over_err;
End If;
If r_billing_method.rental_billing_flag = 'Y' Or
(r_billing_method.principal_billing_flag = 'Y' And
r_billing_method.interest_billing_flag = 'Y') Then
--本+利
If v_billing_amount = r_cashflow_rec.due_amount Then
v_billing_status := g_bill_status_full;
Elsif v_billing_amount = 0 Then
v_billing_status := g_bill_status_not;
Else
v_billing_status := g_bill_status_partial;
End If;
Elsif r_billing_method.principal_billing_flag = 'Y' And
nvl(r_billing_method.interest_billing_flag, 'N') = 'N' Then
--本
If v_billing_principal = r_cashflow_rec.principal Then
v_billing_status := g_bill_status_full;
Elsif v_billing_principal = 0 Then
v_billing_status := g_bill_status_not;
Else
v_billing_status := g_bill_status_partial;
End If;
Elsif nvl(r_billing_method.principal_billing_flag, 'N') = 'N' And
r_billing_method.interest_billing_flag = 'Y' Then
--利
If v_billing_interest = r_cashflow_rec.interest Then
v_billing_status := g_bill_status_full;
Elsif v_billing_interest = 0 Then
v_billing_status := g_bill_status_not;
Else
v_billing_status := g_bill_status_partial;
End If;
End If;
Else
--其他类型cf
v_billing_principal := r_cashflow_rec.billing_principal;
v_billing_interest := r_cashflow_rec.billing_interest;
v_billing_amount := nvl(r_cashflow_rec.billing_amount, 0) +
nvl(p_invoice_ln_rec.total_amount, 0);
If v_billing_amount > r_cashflow_rec.due_amount Then
Raise e_billing_amt_over_err;
End If;
If v_billing_amount = r_cashflow_rec.due_amount Then
v_billing_status := g_bill_status_full;
Elsif v_billing_amount = 0 Then
v_billing_status := g_bill_status_not;
Else
v_billing_status := g_bill_status_partial;
End If;
End If;
Update con_contract_cashflow
Set billing_status = v_billing_status,
billing_amount = v_billing_amount,
billing_principal = v_billing_principal,
billing_interest = v_billing_interest,
last_updated_by = p_user_id,
last_update_date = Sysdate
Where cashflow_id = p_invoice_ln_rec.cashflow_id;
End;
Procedure split_invoice_hd(p_invoice_tmp_hd_rec In invoice_hd_tmp_type,
p_invoice_hd_rec In Out acr_invoice_hd%Rowtype,
p_ln_step_length In Number,
p_session_id In Number,
p_user_id In Number,
p_role_id In Number) Is
v_total_net_amount Number;
v_total_amount Number;
v_invoice_limit Number;
v_avg_split_qty Number;
v_avg_split_amt Number;
v_avg_split_tax_amt Number;
v_avg_split_net_amt Number;
v_remain_split_total_amt Number; --剩余拆分金额
v_remain_split_tax_amt Number; --剩余拆分税额
v_remain_split_net_amt Number; --剩余拆分不含税金额
v_invoice_line_limit hls_billing_method.invoice_line_limit%Type;
v_bill_of_sale hls_billing_method.bill_of_sale%Type;
v_average_split hls_billing_method.average_split%Type;
v_invoice_split_limit hls_billing_method.invoice_split_limit%Type;
v_split_rounding hls_billing_method.split_rounding%Type;
r_invoice_hd_new_rec acr_invoice_hd%Rowtype;
r_invoice_ln_new_rec acr_invoice_ln%Rowtype;
r_invoice_ln_rec acr_invoice_ln%Rowtype;
n Number := 0;
v_ln_count Number;
v_acr_invoice_hd_id Number;
v_acr_invoice_ln_id Number;
v_cash_total_amount Number; --应收金额总额
v_cash_total_vat_amount Number; -- 税额总额
Procedure amt_split Is
Begin
--金额拆分
Select Sum(net_amount), Sum(total_amount)
Into v_total_net_amount, v_total_amount
From acr_invoice_ln
Where invoice_hd_id = p_invoice_hd_rec.invoice_hd_id;
-- @Description:现金流需开票总金额, 开票总税额 @Date:20/6.16 @Modified:Liyuan.Chen
Select Sum(t.due_amount), Sum(t.vat_due_amount)
Into v_cash_total_amount, v_cash_total_vat_amount
From acr_invoice_ln l, con_contract_cashflow t
Where l.invoice_hd_id = p_invoice_hd_rec.invoice_hd_id
And l.cashflow_id = t.cashflow_id;
If v_average_split = 'Y' Then
If v_total_net_amount <= v_invoice_limit Then
created_invoice_check(p_invoice_hd_rec => p_invoice_hd_rec,
p_user_id => p_user_id,
p_role_id => p_role_id);
Return;
End If;
If v_invoice_limit Is Null Then
Raise e_invoice_limit_not_found;
End If;
If v_ln_count > 1 Then
Raise e_billing_amt_split_err;
End If;
Select *
Into r_invoice_ln_rec
From acr_invoice_ln
Where invoice_hd_id = p_invoice_hd_rec.invoice_hd_id;
------------------平均拆分---------------------
--拆分发票的数量
v_avg_split_qty := floor(v_total_net_amount / v_invoice_limit) + 1;
--拆分发票的平均金额
If r_invoice_ln_rec.tax_included_flag = 'Y' Then
v_avg_split_amt := trunc(v_total_amount / v_avg_split_qty,
v_split_rounding);
v_avg_split_tax_amt := round(v_avg_split_amt /
(1 + r_invoice_ln_rec.tax_type_rate) *
r_invoice_ln_rec.tax_type_rate,
g_round_n);
v_avg_split_net_amt := v_avg_split_amt - v_avg_split_tax_amt;
--检查最后一张拆分发票的剩余金额是否还超过限额
v_remain_split_total_amt := v_total_amount -
(v_avg_split_amt *
(v_avg_split_qty - 1));
v_remain_split_tax_amt := v_remain_split_total_amt /
(1 + r_invoice_ln_rec.tax_type_rate) *
r_invoice_ln_rec.tax_type_rate;
v_remain_split_net_amt := v_remain_split_total_amt -
v_remain_split_tax_amt;
Else
v_avg_split_net_amt := trunc(v_total_net_amount / v_avg_split_qty,
v_split_rounding);
v_avg_split_tax_amt := round(v_avg_split_net_amt *
r_invoice_ln_rec.tax_type_rate,
g_round_n);
v_avg_split_amt := v_avg_split_net_amt + v_avg_split_tax_amt;
--检查最后一张拆分发票的剩余金额是否还超过限额
v_remain_split_net_amt := v_total_net_amount -
(v_avg_split_net_amt *
(v_avg_split_qty - 1));
v_remain_split_tax_amt := round(v_remain_split_net_amt *
r_invoice_ln_rec.tax_type_rate,
g_round_n);
v_remain_split_total_amt := v_remain_split_net_amt +
v_remain_split_tax_amt;
End If;
If v_avg_split_net_amt > v_invoice_limit Or
v_remain_split_net_amt > v_invoice_limit Then
v_avg_split_qty := v_avg_split_qty + 1;
If r_invoice_ln_rec.tax_included_flag = 'Y' Then
v_avg_split_amt := trunc(v_total_amount / v_avg_split_qty,
v_split_rounding);
v_avg_split_tax_amt := round(v_avg_split_amt / (1 +
r_invoice_ln_rec.tax_type_rate) *
r_invoice_ln_rec.tax_type_rate,
g_round_n);
v_avg_split_net_amt := v_avg_split_amt - v_avg_split_tax_amt;
--最后一张拆分发票的剩余金额
v_remain_split_total_amt := v_total_amount -
(v_avg_split_amt *
(v_avg_split_qty - 1));
v_remain_split_tax_amt := v_remain_split_total_amt /
(1 + r_invoice_ln_rec.tax_type_rate) *
r_invoice_ln_rec.tax_type_rate;
v_remain_split_net_amt := v_remain_split_total_amt -
v_remain_split_tax_amt;
Else
v_avg_split_net_amt := trunc(v_total_net_amount /
v_avg_split_qty,
v_split_rounding);
v_avg_split_tax_amt := round(v_avg_split_net_amt *
r_invoice_ln_rec.tax_type_rate,
g_round_n);
v_avg_split_amt := v_avg_split_net_amt + v_avg_split_tax_amt;
--最后一张拆分发票的剩余金额
v_remain_split_net_amt := v_total_net_amount -
(v_avg_split_net_amt *
(v_avg_split_qty - 1));
v_remain_split_tax_amt := round(v_remain_split_net_amt *
r_invoice_ln_rec.tax_type_rate,
g_round_n);
v_remain_split_total_amt := v_remain_split_net_amt +
v_remain_split_tax_amt;
End If;
End If;
Else
--最大限额拆分
If v_invoice_split_limit Is Null Then
created_invoice_check(p_invoice_hd_rec => p_invoice_hd_rec,
p_user_id => p_user_id,
p_role_id => p_role_id);
Return;
End If;
If v_total_net_amount <= v_invoice_split_limit Then
created_invoice_check(p_invoice_hd_rec => p_invoice_hd_rec,
p_user_id => p_user_id,
p_role_id => p_role_id);
Return;
End If;
If v_ln_count > 1 Then
Raise e_billing_amt_split_err;
End If;
Select *
Into r_invoice_ln_rec
From acr_invoice_ln
Where invoice_hd_id = p_invoice_hd_rec.invoice_hd_id;
If r_invoice_ln_rec.tax_included_flag = 'Y' Then
v_avg_split_qty := ceil(v_total_net_amount /
v_invoice_split_limit);
v_avg_split_net_amt := v_invoice_split_limit;
v_avg_split_tax_amt := round(v_avg_split_net_amt *
r_invoice_ln_rec.tax_type_rate,
g_round_n);
v_avg_split_amt := v_avg_split_net_amt + v_avg_split_tax_amt;
--检查最后一张拆分发票的剩余金额是否还超过限额
/*v_remain_split_net_amt := v_total_net_amount -
((v_avg_split_qty - 1) *
v_avg_split_net_amt);
v_remain_split_tax_amt := (v_total_amount - v_total_net_amount) -
((v_avg_split_qty - 1) *
v_avg_split_tax_amt);*/
-- @Description:应业务要求, 用现金流需开票总金额相减得最后一张发票金额 @Date:20/6.16 @Modified:Liyuan.Chen
v_remain_split_net_amt := v_cash_total_amount -
v_cash_total_vat_amount -
((v_avg_split_qty - 1) *
v_avg_split_net_amt);
v_remain_split_tax_amt := v_cash_total_vat_amount -
((v_avg_split_qty - 1) *
v_avg_split_tax_amt);
v_remain_split_total_amt := v_remain_split_net_amt +
v_remain_split_tax_amt;
Else
v_avg_split_qty := ceil(v_total_net_amount /
v_invoice_split_limit);
v_avg_split_net_amt := v_invoice_split_limit;
v_avg_split_tax_amt := round(v_avg_split_net_amt *
r_invoice_ln_rec.tax_type_rate,
g_round_n);
v_avg_split_amt := v_avg_split_net_amt + v_avg_split_tax_amt;
--检查最后一张拆分发票的剩余金额是否还超过限额
/*v_remain_split_net_amt := v_total_net_amount -
(v_avg_split_net_amt *
(v_avg_split_qty - 1));
v_remain_split_tax_amt := (v_total_amount - v_total_net_amount) -
((v_avg_split_qty - 1) *
v_avg_split_tax_amt);*/
-- @Description:应业务要求, 用现金流需开票总金额相减得最后一张发票金额 @Date:20/6.16 @Modified:Liyuan.Chen
v_remain_split_net_amt := v_cash_total_amount -
v_cash_total_vat_amount -
((v_avg_split_qty - 1) *
v_avg_split_net_amt);
v_remain_split_tax_amt := v_cash_total_vat_amount -
((v_avg_split_qty - 1) *
v_avg_split_tax_amt);
v_remain_split_total_amt := v_remain_split_net_amt +
v_remain_split_tax_amt;
End If;
End If;
For i In 2 .. v_avg_split_qty Loop
Select acr_invoice_hd_s.nextval Into v_acr_invoice_hd_id From dual;
Select acr_invoice_ln_s.nextval Into v_acr_invoice_ln_id From dual;
r_invoice_hd_new_rec := p_invoice_hd_rec;
r_invoice_hd_new_rec.invoice_hd_id := v_acr_invoice_hd_id;
r_invoice_hd_new_rec.document_number := get_invoice_number(p_document_type => p_invoice_hd_rec.document_type,
p_transaction_date => p_invoice_hd_rec.accounting_date,
p_company_id => p_invoice_hd_rec.company_id,
p_user_id => p_invoice_hd_rec.created_by);
r_invoice_hd_new_rec.total_amount := v_avg_split_amt;
r_invoice_hd_new_rec.tax_amount := v_avg_split_tax_amt;
--创建新的发票行记录
r_invoice_ln_new_rec := r_invoice_ln_rec;
r_invoice_ln_new_rec.invoice_ln_id := v_acr_invoice_ln_id;
r_invoice_ln_new_rec.invoice_hd_id := r_invoice_hd_new_rec.invoice_hd_id;
r_invoice_ln_new_rec.price := v_avg_split_amt;
r_invoice_ln_new_rec.total_amount := v_avg_split_amt;
r_invoice_ln_new_rec.tax_amount := v_avg_split_tax_amt;
r_invoice_ln_new_rec.net_amount := v_avg_split_net_amt;
r_invoice_ln_new_rec.net_price := v_avg_split_net_amt;
--插入新的发票头
insert_invoice_hd(r_invoice_hd_new_rec);
--插入新的发票行
insert_invoice_ln(r_invoice_ln_new_rec);
created_invoice_check(p_invoice_hd_rec => r_invoice_hd_new_rec,
p_user_id => p_user_id,
p_role_id => p_role_id);
End Loop;
--重算原（第1张）发票头（行）的金额字段
p_invoice_hd_rec.total_amount := v_remain_split_total_amt;
p_invoice_hd_rec.tax_amount := v_remain_split_tax_amt;
r_invoice_ln_rec.price := v_remain_split_total_amt;
r_invoice_ln_rec.total_amount := v_remain_split_total_amt;
r_invoice_ln_rec.tax_amount := v_remain_split_tax_amt;
r_invoice_ln_rec.net_amount := v_remain_split_net_amt;
r_invoice_ln_rec.net_price := v_remain_split_net_amt;
Update acr_invoice_hd
Set Row = p_invoice_hd_rec
Where invoice_hd_id = p_invoice_hd_rec.invoice_hd_id;
Update acr_invoice_ln
Set Row = r_invoice_ln_rec
Where invoice_ln_id = r_invoice_ln_rec.invoice_ln_id;
created_invoice_check(p_invoice_hd_rec => p_invoice_hd_rec,
p_user_id => p_user_id,
p_role_id => p_role_id);
/*else
------------------最大金额拆分 -----------------------
loop
exit when v_total_amount <= v_invoice_split_limit;
--创建新的发票头记录
r_invoice_hd_new_rec := p_invoice_hd_rec;
r_invoice_hd_new_rec.invoice_hd_id := acr_invoice_hd_s.nextval;
r_invoice_hd_new_rec.document_number := get_invoice_number(p_document_type => p_invoice_hd_rec.document_type,
p_transaction_date => p_invoice_hd_rec.accounting_date,
p_company_id => p_invoice_hd_rec.company_id,
p_user_id => p_invoice_hd_rec.created_by);
r_invoice_hd_new_rec.total_amount := v_invoice_split_limit;
--创建新的发票行记录
r_invoice_ln_new_rec := r_invoice_ln_rec;
r_invoice_ln_new_rec.invoice_ln_id := acr_invoice_ln_s.nextval;
r_invoice_ln_new_rec.invoice_hd_id := r_invoice_hd_new_rec.invoice_hd_id;
r_invoice_ln_new_rec.price := r_invoice_hd_new_rec.total_amount;
r_invoice_ln_new_rec.total_amount := r_invoice_hd_new_rec.total_amount;
r_invoice_ln_new_rec.tax_amount := round(r_invoice_ln_new_rec.total_amount / (1 +
r_invoice_ln_new_rec.tax_type_rate) *
r_invoice_ln_new_rec.tax_type_rate,
g_round_n);
r_invoice_ln_new_rec.net_amount := r_invoice_ln_new_rec.total_amount -
r_invoice_ln_new_rec.net_amount;
r_invoice_ln_new_rec.net_price := r_invoice_ln_new_rec.net_amount;
--插入新的发票头
insert_invoice_hd(r_invoice_hd_new_rec);
--插入新的发票行
insert_invoice_ln(r_invoice_ln_new_rec);
created_invoice_check(p_invoice_hd_rec => r_invoice_hd_new_rec,
p_user_id => p_user_id,
p_role_id => p_role_id);
--重算原（第1张）发票头（行）的金额字段
r_invoice_ln_rec.total_amount := r_invoice_ln_rec.total_amount - r_invoice_ln_new_rec.total_amount;
r_invoice_ln_rec.net_amount := r_invoice_ln_rec.net_amount - r_invoice_ln_new_rec.net_amount;
r_invoice_ln_rec.tax_amount := r_invoice_ln_rec.tax_amount - r_invoice_ln_new_rec.tax_amount;
r_invoice_ln_rec.price := r_invoice_ln_rec.total_amount;
r_invoice_ln_rec.net_price := r_invoice_ln_rec.net_amount;
end loop;
update acr_invoice_hd
set row = p_invoice_hd_rec
where invoice_hd_id = p_invoice_hd_rec.invoice_hd_id;
update acr_invoice_ln
set row = r_invoice_ln_rec
where invoice_ln_id = r_invoice_ln_rec.invoice_ln_id;
created_invoice_check(p_invoice_hd_rec => p_invoice_hd_rec,
p_user_id => p_user_id,
p_role_id => p_role_id);
end if;
end if;*/
End;
Procedure qty_split Is
Begin
If v_bill_of_sale = 'N' And v_ln_count > v_invoice_line_limit Then
For i In 2 .. ceil(v_ln_count / v_invoice_line_limit) Loop
Select acr_invoice_hd_s.nextval
Into v_acr_invoice_hd_id
From dual;
r_invoice_hd_new_rec := p_invoice_hd_rec;
r_invoice_hd_new_rec.invoice_hd_id := v_acr_invoice_hd_id;
r_invoice_hd_new_rec.document_number := get_invoice_number(p_document_type => p_invoice_hd_rec.document_type,
p_transaction_date => p_invoice_hd_rec.accounting_date,
p_company_id => p_invoice_hd_rec.company_id,
p_user_id => p_invoice_hd_rec.created_by);
--得到第i张发票的总金额
Select Sum(total_amount)
Into r_invoice_hd_new_rec.total_amount
From acr_invoice_ln
Where invoice_hd_id = p_invoice_hd_rec.invoice_hd_id
And line_number >
p_ln_step_length * v_invoice_line_limit * (i - 1)
And line_number <= p_ln_step_length * v_invoice_line_limit * i;
--将新发票的发票头id，以及发票行号 更新
Update acr_invoice_ln
Set invoice_hd_id = r_invoice_hd_new_rec.invoice_hd_id,
line_number = line_number -
(v_invoice_line_limit * p_ln_step_length)
Where invoice_hd_id = p_invoice_hd_rec.invoice_hd_id
And line_number >
p_ln_step_length * v_invoice_line_limit * (i - 1)
And line_number <= p_ln_step_length * v_invoice_line_limit * i;
--查询新发票头
insert_invoice_hd(r_invoice_hd_new_rec);
created_invoice_check(p_invoice_hd_rec => r_invoice_hd_new_rec,
p_user_id => p_user_id,
p_role_id => p_role_id);
--将第1张发票头总金额重新设置
Select Sum(total_amount)
Into p_invoice_hd_rec.total_amount
From acr_invoice_ln
Where invoice_hd_id = p_invoice_hd_rec.invoice_hd_id;
Update acr_invoice_hd
Set Row = p_invoice_hd_rec
Where invoice_hd_id = p_invoice_hd_rec.invoice_hd_id;
created_invoice_check(p_invoice_hd_rec => p_invoice_hd_rec,
p_user_id => p_user_id,
p_role_id => p_role_id);
End Loop;
Else
created_invoice_check(p_invoice_hd_rec => p_invoice_hd_rec,
p_user_id => p_user_id,
p_role_id => p_role_id);
End If;
End;
Begin
--收据 不用拆分
If p_invoice_hd_rec.invoice_kind = 'RECEIPT' Then
created_invoice_check(p_invoice_hd_rec => p_invoice_hd_rec,
p_user_id => p_user_id,
p_role_id => p_role_id);
Return;
End If;
--总发票行数
Select Count(1)
Into v_ln_count
From acr_invoice_ln
Where invoice_hd_id = p_invoice_hd_rec.invoice_hd_id;
If p_invoice_tmp_hd_rec.contract_id Is Not Null Then
Select invoice_line_limit,
bill_of_sale,
average_split,
decode(p_invoice_hd_rec.invoice_kind,
'0',
vat_invoice_split_limit,
'2',
invoice_split_limit,
'SALES_TAX',
sales_tax_invoice_split_limit,
Null),
split_rounding
Into v_invoice_line_limit,
v_bill_of_sale,
v_average_split,
v_invoice_split_limit,
v_split_rounding
From hls_billing_method
Where billing_method =
(Select billing_method
From con_contract
Where contract_id = p_invoice_tmp_hd_rec.contract_id);
Elsif v_ln_count = 1 Then
Select invoice_line_limit,
bill_of_sale,
average_split,
decode(p_invoice_hd_rec.invoice_kind,
'0',
vat_invoice_split_limit,
'2',
invoice_split_limit,
'SALES_TAX',
sales_tax_invoice_split_limit,
Null),
split_rounding
Into v_invoice_line_limit,
v_bill_of_sale,
v_average_split,
v_invoice_split_limit,
v_split_rounding
From hls_billing_method
Where billing_method =
(Select c.billing_method
From acr_invoice_ln a,
con_contract_cashflow b,
con_contract c
Where a.invoice_hd_id = p_invoice_hd_rec.invoice_hd_id
And a.cashflow_id = b.cashflow_id
And b.contract_id = c.contract_id);
Else
Select invoice_line_limit,
bill_of_sale,
average_split,
decode(p_invoice_hd_rec.invoice_kind,
'0',
vat_invoice_split_limit,
'2',
invoice_split_limit,
'SALES_TAX',
sales_tax_invoice_split_limit,
Null),
split_rounding
Into v_invoice_line_limit,
v_bill_of_sale,
v_average_split,
v_invoice_split_limit,
v_split_rounding
From hls_billing_method
Where billing_method =
(Select c.billing_method
From acr_invoice_ln a,
con_contract_cashflow b,
con_contract c
Where a.invoice_hd_id = p_invoice_hd_rec.invoice_hd_id
And a.cashflow_id = b.cashflow_id
And b.contract_id = c.contract_id
And rownum = 1)
And rownum = 1;
End If;
If v_invoice_split_limit Is Null Then
Select decode(p_invoice_hd_rec.invoice_kind,
'0',
vat_invoice_limit,
'2',
invoice_limit,
'SALES_TAX',
sales_tax_invoice_limit,
Null)
Into v_invoice_split_limit
From fnd_companies
Where company_id = nvl(p_invoice_hd_rec.spv_company_id,
p_invoice_hd_rec.company_id);
End If;
v_invoice_limit := v_invoice_split_limit;
------------------------金额拆分 ---------------------------------
amt_split;
------------------------行数拆分 ---------------------------------
qty_split;
End;
Procedure create_invoice_hd(p_invoice_tmp_hd_rec In invoice_hd_tmp_type,
p_invoice_date In Date,
p_accounting_date In Date,
p_period_name In Varchar2,
p_internal_period_num In Number,
p_invoice_hd_rec Out acr_invoice_hd%Rowtype,
p_ln_step_length Out Number,
p_user_id In Number,
p_role_id In Number) Is
r_invoice_hd acr_invoice_hd%Rowtype;
v_acr_invoice_hd_id Number;
Begin
Select acr_invoice_hd_s.nextval Into v_acr_invoice_hd_id From dual;
r_invoice_hd.invoice_hd_id := v_acr_invoice_hd_id;
r_invoice_hd.company_id := p_invoice_tmp_hd_rec.company_id;
r_invoice_hd.spv_company_id := p_invoice_tmp_hd_rec.spv_company_id;
r_invoice_hd.document_type := p_invoice_tmp_hd_rec.invoice_document_type;
r_invoice_hd.document_category := g_document_category;
get_invoice_number(p_document_type => r_invoice_hd.document_type,
p_transaction_date => p_accounting_date,
p_company_id => p_invoice_tmp_hd_rec.company_id,
p_user_id => p_user_id,
p_document_number => r_invoice_hd.document_number,
p_ln_step_length => p_ln_step_length,
p_business_type => r_invoice_hd.business_type);
r_invoice_hd.manual_invoice_flag := 'N';
r_invoice_hd.bp_id := p_invoice_tmp_hd_rec.bp_id;
r_invoice_hd.bp_name := p_invoice_tmp_hd_rec.bp_name;
r_invoice_hd.bp_tax_registry_num := p_invoice_tmp_hd_rec.bp_tax_registry_num;
r_invoice_hd.bp_address_phone_num := p_invoice_tmp_hd_rec.bp_address_phone_num;
r_invoice_hd.bp_bank_account := p_invoice_tmp_hd_rec.bp_bank_account;
r_invoice_hd.description := p_invoice_tmp_hd_rec.description;
r_invoice_hd.currency := p_invoice_tmp_hd_rec.currency;
r_invoice_hd.exchange_rate_type := p_invoice_tmp_hd_rec.exchange_rate_type;
r_invoice_hd.exchange_rate_quotation := p_invoice_tmp_hd_rec.exchange_rate_quotation;
r_invoice_hd.exchange_rate := p_invoice_tmp_hd_rec.exchange_rate;
r_invoice_hd.distribution_set_id := Null;
r_invoice_hd.internal_period_num := p_internal_period_num;
r_invoice_hd.period_name := p_period_name;
r_invoice_hd.accounting_date := p_accounting_date;
r_invoice_hd.invoice_date := p_invoice_date;
r_invoice_hd.invoice_number := '';
r_invoice_hd.invoice_status := 'NEW';
r_invoice_hd.project_id := p_invoice_tmp_hd_rec.project_id;
r_invoice_hd.contract_id := p_invoice_tmp_hd_rec.contract_id;
r_invoice_hd.reversed_flag := 'N';
r_invoice_hd.source_invoice_header_id := Null;
r_invoice_hd.create_je_flag := 'N';
r_invoice_hd.gld_interface_flag := 'N';
r_invoice_hd.confirmed_date := Null;
r_invoice_hd.confirmed_by := Null;
r_invoice_hd.posted_date := Null;
r_invoice_hd.posted_by := Null;
r_invoice_hd.invoice_kind := p_invoice_tmp_hd_rec.invoice_kind;
r_invoice_hd.vat_interface_status := 'UNTRANSFERED';
r_invoice_hd.vat_invoice_code := Null;
r_invoice_hd.vat_invoice_status := Null;
r_invoice_hd.vat_red_notice_num := Null;
r_invoice_hd.ref_vat_invoice_code := Null;
r_invoice_hd.ref_invoice_number := Null;
r_invoice_hd.owner_user_id := p_user_id;
r_invoice_hd.created_by := p_user_id;
r_invoice_hd.creation_date := Sysdate;
r_invoice_hd.last_updated_by := p_user_id;
r_invoice_hd.last_update_date := Sysdate;
insert_invoice_hd(r_invoice_hd);
p_invoice_hd_rec := r_invoice_hd;
hls_doc_operate_history_pkg.insert_doc_operate_history(p_document_category => r_invoice_hd.document_category,
p_document_id => r_invoice_hd.invoice_hd_id,
p_operation_code => hls_doc_operate_history_pkg.c_generate,
p_user_id => p_user_id,
p_operation_time => Sysdate,
p_description => Null);
End;
--add by niminmin
--罚息开票不记录现金流,取凭证维度
Procedure create_invoice_ln_penalty(p_invoice_tmp_hd_rec In invoice_hd_tmp_type,
p_invoice_hd_rec In acr_invoice_hd%Rowtype,
p_ln_step_length In Number,
p_session_id In Number,
p_user_id In Number) Is
r_invoice_tmp_ln invoice_penalty_tmp_type;
r_invoice_ln acr_invoice_ln%Rowtype;
v_tax_amount Number;
v_net_amount Number;
v_invoice_hd_id Number;
billing_amount_error Exception;
v_billlingmethod hls_billing_method%Rowtype;
v_contract con_contract%Rowtype;
v_round_n Number;
v_old_sum_total_amount number; --本次开票之前已开票总额
v_old_sum_tax_amount number; --本次开票之前已开票税额
v_old_tax_amount number; --凭证上税额
n Number := 0;
v_acr_invoice_ln_id Number;
Cursor cur_invoice_tmp_ln Is
Select c.contract_id,
t.cf_item,
t.cf_type,
t.product_name,
t.billing_amount,
t.tax_amount,
t.journal_header_id,
t.journal_line_id,
t.due_amount
From acr_invoice_create_tmp t,
con_contract c,
hls_document_type dt,
fnd_tax_type_codes tt
Where t.session_id = p_session_id
And t.contract_id = c.contract_id
And c.document_type = dt.document_type
And dt.document_category = 'CONTRACT'
--and c.tax_type_id = tt.tax_type_id
And t.tax_type_id = tt.tax_type_id
And t.checked_flag = 'Y'
And c.company_id = p_invoice_tmp_hd_rec.company_id
And (c.spv_company_id = p_invoice_tmp_hd_rec.spv_company_id Or
(c.spv_company_id Is Null And
p_invoice_tmp_hd_rec.spv_company_id Is Null))
And t.invoice_kind = p_invoice_tmp_hd_rec.invoice_kind
And t.currency = p_invoice_tmp_hd_rec.currency
And t.bp_id = p_invoice_tmp_hd_rec.bp_id
And nvl(t.exchange_rate_type, 'MANUAL') =
p_invoice_tmp_hd_rec.exchange_rate_type
And nvl(t.exchange_rate_quotation, 'DIRECT QUOTATION') =
p_invoice_tmp_hd_rec.exchange_rate_quotation
And nvl(t.exchange_rate, 1) = p_invoice_tmp_hd_rec.exchange_rate
--以下三个字段条件： 原始表上字段不会为空的
And (c.contract_id = p_invoice_tmp_hd_rec.contract_id Or
p_invoice_tmp_hd_rec.contract_id Is Null)
And (c.project_id = p_invoice_tmp_hd_rec.project_id Or
p_invoice_tmp_hd_rec.project_id Is Null)
And (t.times = p_invoice_tmp_hd_rec.times Or
(p_invoice_tmp_hd_rec.times Is Null))
--以下字段条件：等号两边都有可能为空
And (t.invoice_title = p_invoice_tmp_hd_rec.bp_name Or
(t.invoice_title Is Null And
p_invoice_tmp_hd_rec.bp_name Is Null))
And (t.invoice_bp_address_phone_num =
p_invoice_tmp_hd_rec.bp_address_phone_num Or
(t.invoice_bp_address_phone_num Is Null And
p_invoice_tmp_hd_rec.bp_address_phone_num Is Null))
And (t.invoice_bp_tax_registry_num =
p_invoice_tmp_hd_rec.bp_tax_registry_num Or
(t.invoice_bp_tax_registry_num Is Null And
p_invoice_tmp_hd_rec.bp_tax_registry_num Is Null))
And (t.invoice_bp_bank_account =
p_invoice_tmp_hd_rec.bp_bank_account Or
(t.invoice_bp_bank_account Is Null And
p_invoice_tmp_hd_rec.bp_bank_account Is Null))
And (t.head_description = p_invoice_tmp_hd_rec.description Or
(t.head_description Is Null And
p_invoice_tmp_hd_rec.description Is Null))
And (dt.ref_document_type =
p_invoice_tmp_hd_rec.invoice_document_type Or
(dt.ref_document_type Is Null Or
p_invoice_tmp_hd_rec.invoice_document_type Is Null))
--and c.tax_type_id = p_invoice_tmp_hd_rec.tax_type_id
And t.tax_type_id = p_invoice_tmp_hd_rec.tax_type_id
And tt.tax_type_rate = p_invoice_tmp_hd_rec.tax_type_rate
Order By c.contract_number
For Update Nowait;
Begin
v_invoice_hd_id := p_invoice_hd_rec.invoice_hd_id;
If p_invoice_hd_rec.contract_id Is Not Null Then
Select *
Into v_contract
From con_contract t
Where t.contract_id = p_invoice_hd_rec.contract_id;
v_billlingmethod := get_billmethod_rec(v_contract.billing_method);
End If;
Open cur_invoice_tmp_ln;
Loop
Fetch cur_invoice_tmp_ln
Into r_invoice_tmp_ln;
Exit When cur_invoice_tmp_ln%Notfound;
n := n + 1;
Begin
Select currency_precision
Into v_round_n
From con_contract a
Where a.contract_id = r_invoice_tmp_ln.contract_id;
Exception
When no_data_found Then
v_round_n := Null;
End;
v_round_n := nvl(v_round_n, g_round_n);
Select acr_invoice_ln_s.nextval Into v_acr_invoice_ln_id From dual;
r_invoice_ln.invoice_ln_id := v_acr_invoice_ln_id;
r_invoice_ln.invoice_hd_id := v_invoice_hd_id;
r_invoice_ln.line_type := 'SERVICE';
r_invoice_ln.line_number := n * p_ln_step_length;
r_invoice_ln.cashflow_id := Null;
r_invoice_ln.cf_item := r_invoice_tmp_ln.cf_item;
r_invoice_ln.cf_type := r_invoice_tmp_ln.cf_type;
r_invoice_ln.product_id := '';
r_invoice_ln.product_name := r_invoice_tmp_ln.product_name; --from tmp
r_invoice_ln.specification := ''; --from tmp
r_invoice_ln.quantity := 1;
r_invoice_ln.uom := '';
r_invoice_ln.price_quantity := 1;
r_invoice_ln.price_quantity_uom := '';
r_invoice_ln.tax_type_id := p_invoice_tmp_hd_rec.tax_type_id;
r_invoice_ln.tax_type_rate := p_invoice_tmp_hd_rec.tax_type_rate;
r_invoice_ln.tax_included_flag := 'Y';
r_invoice_ln.total_amount := r_invoice_tmp_ln.billing_amount;
r_invoice_ln.tax_amount := r_invoice_tmp_ln.tax_amount;
r_invoice_ln.net_amount := r_invoice_tmp_ln.billing_amount -
r_invoice_tmp_ln.tax_amount;
r_invoice_ln.net_price := r_invoice_ln.net_amount;
r_invoice_ln.price := r_invoice_tmp_ln.billing_amount;
r_invoice_ln.responsibility_center_id := '';
r_invoice_ln.account_id := '';
r_invoice_ln.description := '';
r_invoice_ln.created_by := p_user_id;
r_invoice_ln.creation_date := Sysdate;
r_invoice_ln.last_updated_by := p_user_id;
r_invoice_ln.last_update_date := Sysdate;
r_invoice_ln.ref_n03 := r_invoice_tmp_ln.journal_header_id;
r_invoice_ln.ref_n04 := r_invoice_tmp_ln.journal_line_id;
insert_invoice_ln(r_invoice_ln);
-- update_cashflow_ln(p_invoice_hd_rec => p_invoice_hd_rec,
-- p_invoice_ln_rec => r_invoice_ln,
-- p_user_id => p_user_id);
End Loop;
Close cur_invoice_tmp_ln;
Exception
When billing_amount_error Then
sys_raise_app_error_pkg.raise_user_define_error(p_message_code => '开票金额有误！',
p_created_by => p_user_id,
p_package_name => 'ACR_INVOICE_PKG',
p_procedure_function_name => 'create_invoice_ln');
raise_application_error(sys_raise_app_error_pkg.c_error_number,
sys_raise_app_error_pkg.g_err_line_id);
When Others Then
If cur_invoice_tmp_ln%Isopen Then
Close cur_invoice_tmp_ln;
End If;
Raise;
End;
--modify by Spencer 3893 20161012 税率取根据规则获取的acr_invoice_create_tmp，不取合同表
Procedure create_invoice_ln(p_invoice_tmp_hd_rec In invoice_hd_tmp_type,
p_invoice_hd_rec In acr_invoice_hd%Rowtype,
p_ln_step_length In Number,
p_session_id In Number,
p_user_id In Number) Is
r_invoice_tmp_ln invoice_ln_tmp_type;
r_invoice_ln acr_invoice_ln%Rowtype;
r_con_contract_cashflow con_contract_cashflow%Rowtype;
v_tax_amount Number;
v_net_amount Number;
v_invoice_hd_id Number;
billing_amount_error Exception;
v_billlingmethod hls_billing_method%Rowtype;
v_contract con_contract%Rowtype;
v_round_n Number;
n Number := 0;
v_acr_invoice_ln_id Number;
Cursor cur_invoice_tmp_ln Is
Select c.contract_id,
t.cashflow_id,
t.cf_item,
t.cf_type,
t.product_name,
t.billing_amount
From acr_invoice_create_tmp t,
con_contract c,
con_contract_cashflow cf,
hls_document_type dt,
fnd_tax_type_codes tt
Where t.session_id = p_session_id
And t.contract_id = c.contract_id
And t.cashflow_id = cf.cashflow_id
And c.document_type = dt.document_type
And dt.document_category = 'CONTRACT'
--and c.tax_type_id = tt.tax_type_id
And t.tax_type_id = tt.tax_type_id
And t.checked_flag = 'Y'
And c.company_id = p_invoice_tmp_hd_rec.company_id
And (c.spv_company_id = p_invoice_tmp_hd_rec.spv_company_id Or
(c.spv_company_id Is Null And
p_invoice_tmp_hd_rec.spv_company_id Is Null))
And t.invoice_kind = p_invoice_tmp_hd_rec.invoice_kind
And t.currency = p_invoice_tmp_hd_rec.currency
And t.bp_id = p_invoice_tmp_hd_rec.bp_id
And nvl(t.exchange_rate_type, 'MANUAL') =
p_invoice_tmp_hd_rec.exchange_rate_type
And nvl(t.exchange_rate_quotation, 'DIRECT QUOTATION') =
p_invoice_tmp_hd_rec.exchange_rate_quotation
And nvl(t.exchange_rate, 1) = p_invoice_tmp_hd_rec.exchange_rate
--以下三个字段条件： 原始表上字段不会为空的
And (c.contract_id = p_invoice_tmp_hd_rec.contract_id Or
p_invoice_tmp_hd_rec.contract_id Is Null)
And (c.project_id = p_invoice_tmp_hd_rec.project_id Or
p_invoice_tmp_hd_rec.project_id Is Null)
And (t.times = p_invoice_tmp_hd_rec.times Or
(p_invoice_tmp_hd_rec.times Is Null))
--以下字段条件：等号两边都有可能为空
And (t.invoice_title = p_invoice_tmp_hd_rec.bp_name Or
(t.invoice_title Is Null And
p_invoice_tmp_hd_rec.bp_name Is Null))
And (t.invoice_bp_address_phone_num =
p_invoice_tmp_hd_rec.bp_address_phone_num Or
(t.invoice_bp_address_phone_num Is Null And
p_invoice_tmp_hd_rec.bp_address_phone_num Is Null))
And (t.invoice_bp_tax_registry_num =
p_invoice_tmp_hd_rec.bp_tax_registry_num Or
(t.invoice_bp_tax_registry_num Is Null And
p_invoice_tmp_hd_rec.bp_tax_registry_num Is Null))
And (t.invoice_bp_bank_account =
p_invoice_tmp_hd_rec.bp_bank_account Or
(t.invoice_bp_bank_account Is Null And
p_invoice_tmp_hd_rec.bp_bank_account Is Null))
And (t.head_description = p_invoice_tmp_hd_rec.description Or
(t.head_description Is Null And
p_invoice_tmp_hd_rec.description Is Null))
And (dt.ref_document_type =
p_invoice_tmp_hd_rec.invoice_document_type Or
(dt.ref_document_type Is Null Or
p_invoice_tmp_hd_rec.invoice_document_type Is Null))
--and c.tax_type_id = p_invoice_tmp_hd_rec.tax_type_id
And t.tax_type_id = p_invoice_tmp_hd_rec.tax_type_id
And tt.tax_type_rate = p_invoice_tmp_hd_rec.tax_type_rate
Order By c.contract_number, t.times, cf.due_date
For Update Nowait;
Begin
v_invoice_hd_id := p_invoice_hd_rec.invoice_hd_id;
If p_invoice_hd_rec.contract_id Is Not Null Then
Select *
Into v_contract
From con_contract t
Where t.contract_id = p_invoice_hd_rec.contract_id;
v_billlingmethod := get_billmethod_rec(v_contract.billing_method);
End If;
Open cur_invoice_tmp_ln;
Loop
Fetch cur_invoice_tmp_ln
Into r_invoice_tmp_ln;
Exit When cur_invoice_tmp_ln%Notfound;
n := n + 1;
Begin
Select currency_precision
Into v_round_n
From con_contract a, con_contract_cashflow b
Where a.contract_id = b.contract_id
And b.cashflow_id = r_invoice_tmp_ln.cashflow_id;
Exception
When no_data_found Then
v_round_n := Null;
End;
v_round_n := nvl(v_round_n, g_round_n);
Select acr_invoice_ln_s.nextval Into v_acr_invoice_ln_id From dual;
r_invoice_ln.invoice_ln_id := v_acr_invoice_ln_id;
r_invoice_ln.invoice_hd_id := v_invoice_hd_id;
r_invoice_ln.line_type := 'SERVICE';
r_invoice_ln.line_number := n * p_ln_step_length;
r_invoice_ln.cashflow_id := r_invoice_tmp_ln.cashflow_id;
r_invoice_ln.cf_item := r_invoice_tmp_ln.cf_item;
r_invoice_ln.cf_type := r_invoice_tmp_ln.cf_type;
r_invoice_ln.product_id := '';
r_invoice_ln.product_name := r_invoice_tmp_ln.product_name; --from tmp
r_invoice_ln.specification := ''; --from tmp
r_invoice_ln.quantity := 1;
r_invoice_ln.uom := '';
r_invoice_ln.price_quantity := 1;
r_invoice_ln.price_quantity_uom := '';
r_invoice_ln.tax_type_id := p_invoice_tmp_hd_rec.tax_type_id;
If p_invoice_hd_rec.invoice_kind = 'SALES_TAX' Then
r_invoice_ln.tax_type_rate := 0;
r_invoice_ln.tax_included_flag := 'N';
r_invoice_ln.net_amount := r_invoice_tmp_ln.billing_amount;
r_invoice_ln.tax_amount := round(r_invoice_tmp_ln.billing_amount *
r_invoice_ln.tax_type_rate,
v_round_n);
r_invoice_ln.total_amount := nvl(r_invoice_ln.net_amount, 0) +
nvl(r_invoice_ln.tax_amount, 0);
r_invoice_ln.net_price := r_invoice_ln.net_amount;
r_invoice_ln.price := r_invoice_ln.total_amount;
Else
r_invoice_ln.tax_type_rate := p_invoice_tmp_hd_rec.tax_type_rate;
r_invoice_ln.tax_included_flag := 'Y';
r_invoice_ln.total_amount := r_invoice_tmp_ln.billing_amount;
--modify by shen 税额取现金流表上对应的税额
Select *
Into r_con_contract_cashflow
From con_contract_cashflow ccc
Where ccc.cashflow_id = r_invoice_ln.cashflow_id;
If (r_con_contract_cashflow.cf_item = 1 And
r_con_contract_cashflow.cf_type = 1) Or
(r_con_contract_cashflow.cf_item = 2 And
r_con_contract_cashflow.cf_type = 2) Or
(r_con_contract_cashflow.cf_item = 8 And
r_con_contract_cashflow.cf_type = 8) Or
(r_con_contract_cashflow.cf_item = 11 And
r_con_contract_cashflow.cf_type = 11) Or
(r_con_contract_cashflow.cf_item = 200 And
r_con_contract_cashflow.cf_type = 1) Or
(r_con_contract_cashflow.cf_item = 250 And
r_con_contract_cashflow.cf_type = 1) Then
If r_invoice_tmp_ln.billing_amount =
r_con_contract_cashflow.principal Then
v_tax_amount := nvl(r_con_contract_cashflow.vat_principal, 0);
v_net_amount := nvl(r_con_contract_cashflow.net_principal, 0);
--modify by shen 利息税等于租金税减去本金税，利息不含税等于利息-利息税
Elsif r_invoice_tmp_ln.billing_amount =
r_con_contract_cashflow.interest Then
v_tax_amount := nvl(r_con_contract_cashflow.vat_due_amount, 0) -
nvl(r_con_contract_cashflow.vat_principal, 0);
v_net_amount := nvl(r_con_contract_cashflow.interest, 0) -
v_tax_amount;
Elsif r_invoice_tmp_ln.billing_amount =
r_con_contract_cashflow.due_amount Then
v_tax_amount := nvl(r_con_contract_cashflow.vat_due_amount, 0);
v_net_amount := nvl(r_con_contract_cashflow.net_due_amount, 0);
Else
Raise billing_amount_error;
End If;
r_invoice_ln.tax_amount := v_tax_amount;
r_invoice_ln.net_amount := v_net_amount;
Elsif r_con_contract_cashflow.cf_item = 9 And
r_con_contract_cashflow.cf_type = 9 Then
Select round(Sum(coo.write_off_due_amount) / 1.13, 2),
round(Sum(coo.write_off_due_amount) -
Sum(coo.write_off_due_amount) / 1.13,
2)
Into v_net_amount, v_tax_amount
From csh_write_off coo
Where coo.cashflow_id = r_con_contract_cashflow.cashflow_id;
r_invoice_ln.tax_amount := v_tax_amount;
r_invoice_ln.net_amount := v_net_amount;
Else
r_invoice_ln.tax_amount := round(r_invoice_ln.total_amount / (1 +
p_invoice_tmp_hd_rec.tax_type_rate) *
p_invoice_tmp_hd_rec.tax_type_rate,
v_round_n);
r_invoice_ln.net_amount := r_invoice_ln.total_amount -
r_invoice_ln.tax_amount;
End If;
r_invoice_ln.net_price := r_invoice_ln.net_amount;
r_invoice_ln.price := r_invoice_tmp_ln.billing_amount;
End If;
r_invoice_ln.responsibility_center_id := '';
r_invoice_ln.account_id := '';
r_invoice_ln.description := '';
r_invoice_ln.created_by := p_user_id;
r_invoice_ln.creation_date := Sysdate;
r_invoice_ln.last_updated_by := p_user_id;
r_invoice_ln.last_update_date := Sysdate;
insert_invoice_ln(r_invoice_ln);
update_cashflow_ln(p_invoice_hd_rec => p_invoice_hd_rec,
p_invoice_ln_rec => r_invoice_ln,
p_user_id => p_user_id);
End Loop;
Close cur_invoice_tmp_ln;
Exception
When billing_amount_error Then
sys_raise_app_error_pkg.raise_user_define_error(p_message_code => '开票金额有误！',
p_created_by => p_user_id,
p_package_name => 'ACR_INVOICE_PKG',
p_procedure_function_name => 'create_invoice_ln');
raise_application_error(sys_raise_app_error_pkg.c_error_number,
sys_raise_app_error_pkg.g_err_line_id);
When Others Then
If cur_invoice_tmp_ln%Isopen Then
Close cur_invoice_tmp_ln;
End If;
Raise;
End;
Procedure save_acr_invoice_batch(p_invoice_hd_rec In acr_invoice_hd%Rowtype,
p_user_id Number) Is
v_id Number;
Begin
For acr_invoice_ln_rec In (Select *
From acr_invoice_ln t
Where t.invoice_hd_id =
p_invoice_hd_rec.invoice_hd_id) Loop
Select acr_invoice_batch_ln_s.nextval Into v_id From dual;
--单据复制
hls_document_transfer_pkg.doc_to_doc(p_from_doc_table => 'ACR_INVOICE_LN',
p_from_doc_pk => acr_invoice_ln_rec.invoice_ln_id,
p_to_doc_table => 'ACR_INVOICE_BATCH_LN',
p_to_doc_pk => v_id,
p_copy_method => 'DOC_TO_HISTORY',
p_user_id => p_user_id);
End Loop;
--null;
End;
--modify by Spencer 3893 20161012 税率取根据规则获取的acr_invoice_create_tmp，不取合同表
Procedure create_invoice_group_by_times(p_session_id Number,
p_invoice_date Date,
p_accounting_date Date,
p_period_name Varchar2,
p_internal_period_num Number,
p_user_id Number,
p_role_id Number) Is
r_invoice_tmp_hd invoice_hd_tmp_type;
r_invoice_hd acr_invoice_hd%Rowtype;
v_ln_step_length Number;
Cursor cur_group_by_times_hd Is
Select c.company_id,
c.spv_company_id,
t.contract_id,
Null As project_id,
dt.ref_document_type As invoice_document_type,
t.bp_id,
t.invoice_title As bp_name,
hbm.tax_registry_num As bp_tax_registry_num,
t.invoice_bp_address_phone_num,
t.invoice_bp_bank_account,
t.head_description,
t.invoice_kind,
Sum(t.billing_amount) As total_amount,
t.currency,
nvl(t.exchange_rate_type, 'MANUAL') As exchange_rate_type,
nvl(t.exchange_rate_quotation, 'DIRECT QUOTATION') As exchange_rate_quotation,
nvl(t.exchange_rate, 1) As exchange_rate,
t.tax_type_id, --c.tax_type_id,
tt.tax_type_rate,
t.times
From acr_invoice_create_tmp t,
con_contract c,
hls_bp_master hbm,
hls_document_type dt,
fnd_tax_type_codes tt
Where t.contract_id = c.contract_id
And c.data_class = 'NORMAL'
And t.bp_id = hbm.bp_id
And c.document_type = dt.document_type
And dt.document_category = 'CONTRACT'
And t.tax_type_id = tt.tax_type_id
--and c.tax_type_id = tt.tax_type_id
And t.session_id = p_session_id
And t.checked_flag = 'Y'
Group By c.company_id,
c.spv_company_id,
t.invoice_kind,
t.contract_id,
t.bp_id,
dt.ref_document_type,
t.invoice_title,
hbm.tax_registry_num,
t.invoice_bp_address_phone_num,
t.invoice_bp_bank_account,
t.head_description,
t.currency,
nvl(t.exchange_rate_type, 'MANUAL'),
nvl(t.exchange_rate_quotation, 'DIRECT QUOTATION'),
nvl(t.exchange_rate, 1),
--c.tax_type_id,
t.tax_type_id,
tt.tax_type_rate,
t.times;
Begin
Open cur_group_by_times_hd;
Loop
Fetch cur_group_by_times_hd
Into r_invoice_tmp_hd;
Exit When cur_group_by_times_hd%Notfound;
create_invoice_hd(p_invoice_tmp_hd_rec => r_invoice_tmp_hd,
p_invoice_date => p_invoice_date,
p_accounting_date => p_accounting_date,
p_period_name => p_period_name,
p_internal_period_num => p_internal_period_num,
p_invoice_hd_rec => r_invoice_hd,
p_ln_step_length => v_ln_step_length,
p_user_id => p_user_id,
p_role_id => p_role_id);
create_invoice_ln(p_invoice_tmp_hd_rec => r_invoice_tmp_hd,
p_invoice_hd_rec => r_invoice_hd,
p_ln_step_length => v_ln_step_length,
p_session_id => p_session_id,
p_user_id => p_user_id);
split_invoice_hd(p_invoice_tmp_hd_rec => r_invoice_tmp_hd,
p_invoice_hd_rec => r_invoice_hd,
p_ln_step_length => v_ln_step_length,
p_session_id => p_session_id,
p_user_id => p_user_id,
p_role_id => p_role_id);
--插入发票行合并表
save_acr_invoice_batch(p_invoice_hd_rec => r_invoice_hd,
p_user_id => p_user_id);
End Loop;
Close cur_group_by_times_hd;
End;
Procedure create_penalty_no_group_by(p_session_id Number,
p_invoice_date Date,
p_accounting_date Date,
p_period_name Varchar2,
p_internal_period_num Number,
p_user_id Number,
p_role_id Number) Is
r_invoice_tmp_hd invoice_hd_tmp_type;
r_invoice_hd acr_invoice_hd%Rowtype;
v_ln_step_length Number;
Cursor cur_group_by_contract_hd Is
Select c.company_id,
c.spv_company_id,
t.contract_id,
Null As project_id,
dt.ref_document_type As invoice_document_type,
t.bp_id,
t.invoice_title As bp_name,
hbm.tax_registry_num As bp_tax_registry_num,
t.invoice_bp_address_phone_num,
t.invoice_bp_bank_account,
t.head_description,
t.invoice_kind,
t.billing_amount As total_amount,
t.currency,
nvl(t.exchange_rate_type, 'MANUAL') As exchange_rate_type,
nvl(t.exchange_rate_quotation, 'DIRECT QUOTATION') As exchange_rate_quotation,
nvl(t.exchange_rate, 1) As exchange_rate,
t.tax_type_id,
tt.tax_type_rate,
Null As times
From acr_invoice_create_tmp t,
con_contract c,
hls_bp_master hbm,
hls_document_type dt,
fnd_tax_type_codes tt
Where t.contract_id = c.contract_id
And c.data_class = 'NORMAL'
And t.bp_id = hbm.bp_id
And c.document_type = dt.document_type
And dt.document_category = 'CONTRACT'
And t.tax_type_id = tt.tax_type_id
And t.session_id = p_session_id
And t.checked_flag = 'Y';
Begin
Open cur_group_by_contract_hd;
Loop
Fetch cur_group_by_contract_hd
Into r_invoice_tmp_hd;
Exit When cur_group_by_contract_hd%Notfound;
create_invoice_hd(p_invoice_tmp_hd_rec => r_invoice_tmp_hd,
p_invoice_date => p_invoice_date,
p_accounting_date => p_accounting_date,
p_period_name => p_period_name,
p_internal_period_num => p_internal_period_num,
p_invoice_hd_rec => r_invoice_hd,
p_ln_step_length => v_ln_step_length,
p_user_id => p_user_id,
p_role_id => p_role_id);
create_invoice_ln_penalty(p_invoice_tmp_hd_rec => r_invoice_tmp_hd,
p_invoice_hd_rec => r_invoice_hd,
p_ln_step_length => v_ln_step_length,
p_session_id => p_session_id,
p_user_id => p_user_id);
split_invoice_hd(p_invoice_tmp_hd_rec => r_invoice_tmp_hd,
p_invoice_hd_rec => r_invoice_hd,
p_ln_step_length => v_ln_step_length,
p_session_id => p_session_id,
p_user_id => p_user_id,
p_role_id => p_role_id);
--插入发票行合并表
save_acr_invoice_batch(p_invoice_hd_rec => r_invoice_hd,
p_user_id => p_user_id);
End Loop;
Close cur_group_by_contract_hd;
End;
Procedure create_penalty_group_by_con(p_session_id Number,
p_invoice_date Date,
p_accounting_date Date,
p_period_name Varchar2,
p_internal_period_num Number,
p_user_id Number,
p_role_id Number) Is
r_invoice_tmp_hd invoice_hd_tmp_type;
r_invoice_hd acr_invoice_hd%Rowtype;
v_ln_step_length Number;
Cursor cur_group_by_contract_hd Is
Select c.company_id,
c.spv_company_id,
t.contract_id,
Null As project_id,
dt.ref_document_type As invoice_document_type,
t.bp_id,
t.invoice_title As bp_name,
hbm.tax_registry_num As bp_tax_registry_num,
t.invoice_bp_address_phone_num,
t.invoice_bp_bank_account,
t.head_description,
t.invoice_kind,
Sum(t.billing_amount) As total_amount,
t.currency,
nvl(t.exchange_rate_type, 'MANUAL') As exchange_rate_type,
nvl(t.exchange_rate_quotation, 'DIRECT QUOTATION') As exchange_rate_quotation,
nvl(t.exchange_rate, 1) As exchange_rate,
t.tax_type_id,
tt.tax_type_rate,
Null As times
From acr_invoice_create_tmp t,
con_contract c,
hls_bp_master hbm,
hls_document_type dt,
fnd_tax_type_codes tt
Where t.contract_id = c.contract_id
And c.data_class = 'NORMAL'
And t.bp_id = hbm.bp_id
And c.document_type = dt.document_type
And dt.document_category = 'CONTRACT'
And t.tax_type_id = tt.tax_type_id
And t.session_id = p_session_id
And t.checked_flag = 'Y'
Group By c.company_id,
c.spv_company_id,
t.invoice_kind,
t.contract_id,
t.bp_id,
dt.ref_document_type,
t.invoice_title,
hbm.tax_registry_num,
t.invoice_bp_address_phone_num,
t.invoice_bp_bank_account,
t.head_description,
t.currency,
nvl(t.exchange_rate_type, 'MANUAL'),
nvl(t.exchange_rate_quotation, 'DIRECT QUOTATION'),
nvl(t.exchange_rate, 1),
t.tax_type_id,
tt.tax_type_rate,
Null;
Begin
Open cur_group_by_contract_hd;
Loop
Fetch cur_group_by_contract_hd
Into r_invoice_tmp_hd;
Exit When cur_group_by_contract_hd%Notfound;
create_invoice_hd(p_invoice_tmp_hd_rec => r_invoice_tmp_hd,
p_invoice_date => p_invoice_date,
p_accounting_date => p_accounting_date,
p_period_name => p_period_name,
p_internal_period_num => p_internal_period_num,
p_invoice_hd_rec => r_invoice_hd,
p_ln_step_length => v_ln_step_length,
p_user_id => p_user_id,
p_role_id => p_role_id);
create_invoice_ln_penalty(p_invoice_tmp_hd_rec => r_invoice_tmp_hd,
p_invoice_hd_rec => r_invoice_hd,
p_ln_step_length => v_ln_step_length,
p_session_id => p_session_id,
p_user_id => p_user_id);
split_invoice_hd(p_invoice_tmp_hd_rec => r_invoice_tmp_hd,
p_invoice_hd_rec => r_invoice_hd,
p_ln_step_length => v_ln_step_length,
p_session_id => p_session_id,
p_user_id => p_user_id,
p_role_id => p_role_id);
--插入发票行合并表
save_acr_invoice_batch(p_invoice_hd_rec => r_invoice_hd,
p_user_id => p_user_id);
End Loop;
Close cur_group_by_contract_hd;
End;
Procedure create_invoice_group_by_con(p_session_id Number,
p_invoice_date Date,
p_accounting_date Date,
p_period_name Varchar2,
p_internal_period_num Number,
p_user_id Number,
p_role_id Number) Is
r_invoice_tmp_hd invoice_hd_tmp_type;
r_invoice_hd acr_invoice_hd%Rowtype;
v_ln_step_length Number;
Cursor cur_group_by_contract_hd Is
Select c.company_id,
c.spv_company_id,
t.contract_id,
Null As project_id,
dt.ref_document_type As invoice_document_type,
t.bp_id,
t.invoice_title As bp_name,
hbm.tax_registry_num As bp_tax_registry_num,
t.invoice_bp_address_phone_num,
t.invoice_bp_bank_account,
t.head_description,
t.invoice_kind,
Sum(t.billing_amount) As total_amount,
t.currency,
nvl(t.exchange_rate_type, 'MANUAL') As exchange_rate_type,
nvl(t.exchange_rate_quotation, 'DIRECT QUOTATION') As exchange_rate_quotation,
nvl(t.exchange_rate, 1) As exchange_rate,
t.tax_type_id,
tt.tax_type_rate,
Null As times
From acr_invoice_create_tmp t,
con_contract c,
hls_bp_master hbm,
hls_document_type dt,
fnd_tax_type_codes tt
Where t.contract_id = c.contract_id
And c.data_class = 'NORMAL'
And t.bp_id = hbm.bp_id
And c.document_type = dt.document_type
And dt.document_category = 'CONTRACT'
And t.tax_type_id = tt.tax_type_id
And t.session_id = p_session_id
And t.checked_flag = 'Y'
Group By c.company_id,
c.spv_company_id,
t.invoice_kind,
t.contract_id,
t.bp_id,
dt.ref_document_type,
t.invoice_title,
hbm.tax_registry_num,
t.invoice_bp_address_phone_num,
t.invoice_bp_bank_account,
t.head_description,
t.currency,
nvl(t.exchange_rate_type, 'MANUAL'),
nvl(t.exchange_rate_quotation, 'DIRECT QUOTATION'),
nvl(t.exchange_rate, 1),
t.tax_type_id,
tt.tax_type_rate,
Null;
Begin
Open cur_group_by_contract_hd;
Loop
Fetch cur_group_by_contract_hd
Into r_invoice_tmp_hd;
Exit When cur_group_by_contract_hd%Notfound;
create_invoice_hd(p_invoice_tmp_hd_rec => r_invoice_tmp_hd,
p_invoice_date => p_invoice_date,
p_accounting_date => p_accounting_date,
p_period_name => p_period_name,
p_internal_period_num => p_internal_period_num,
p_invoice_hd_rec => r_invoice_hd,
p_ln_step_length => v_ln_step_length,
p_user_id => p_user_id,
p_role_id => p_role_id);
create_invoice_ln(p_invoice_tmp_hd_rec => r_invoice_tmp_hd,
p_invoice_hd_rec => r_invoice_hd,
p_ln_step_length => v_ln_step_length,
p_session_id => p_session_id,
p_user_id => p_user_id);
split_invoice_hd(p_invoice_tmp_hd_rec => r_invoice_tmp_hd,
p_invoice_hd_rec => r_invoice_hd,
p_ln_step_length => v_ln_step_length,
p_session_id => p_session_id,
p_user_id => p_user_id,
p_role_id => p_role_id);
--插入发票行合并表
save_acr_invoice_batch(p_invoice_hd_rec => r_invoice_hd,
p_user_id => p_user_id);
End Loop;
Close cur_group_by_contract_hd;
End;
Procedure create_invoice_group_by_prj(p_session_id Number,
p_invoice_date Date,
p_accounting_date Date,
p_period_name Varchar2,
p_internal_period_num Number,
p_user_id Number,
p_role_id Number) Is
r_invoice_tmp_hd invoice_hd_tmp_type;
r_invoice_hd acr_invoice_hd%Rowtype;
v_ln_step_length Number;
Cursor cur_group_by_project_hd Is
Select c.company_id,
c.spv_company_id,
Null As contract_id,
c.project_id,
dt.ref_document_type As invoice_document_type,
t.bp_id,
t.invoice_title As bp_name,
hbm.tax_registry_num As bp_tax_registry_num,
t.invoice_bp_address_phone_num,
t.invoice_bp_bank_account,
t.head_description,
t.invoice_kind,
Sum(t.billing_amount) As total_amount,
t.currency,
nvl(t.exchange_rate_type, 'MANUAL') As exchange_rate_type,
nvl(t.exchange_rate_quotation, 'DIRECT QUOTATION') As exchange_rate_quotation,
nvl(t.exchange_rate, 1) As exchange_rate,
c.tax_type_id,
tt.tax_type_rate,
Null As times
From acr_invoice_create_tmp t,
con_contract c,
hls_bp_master hbm,
hls_document_type dt,
fnd_tax_type_codes tt
Where t.contract_id = c.contract_id
And c.data_class = 'NORMAL'
And t.bp_id = hbm.bp_id
And c.document_type = dt.document_type
And dt.document_category = 'CONTRACT'
And c.tax_type_id = tt.tax_type_id
And t.session_id = p_session_id
And t.checked_flag = 'Y'
Group By c.company_id,
c.spv_company_id,
t.invoice_kind,
c.project_id,
t.bp_id,
dt.ref_document_type,
t.invoice_title,
hbm.tax_registry_num,
t.invoice_bp_address_phone_num,
t.invoice_bp_bank_account,
t.head_description,
t.currency,
nvl(t.exchange_rate_type, 'MANUAL'),
nvl(t.exchange_rate_quotation, 'DIRECT QUOTATION'),
nvl(t.exchange_rate, 1),
c.tax_type_id,
tt.tax_type_rate,
Null;
Begin
Open cur_group_by_project_hd;
Loop
Fetch cur_group_by_project_hd
Into r_invoice_tmp_hd;
Exit When cur_group_by_project_hd%Notfound;
create_invoice_hd(p_invoice_tmp_hd_rec => r_invoice_tmp_hd,
p_invoice_date => p_invoice_date,
p_accounting_date => p_accounting_date,
p_period_name => p_period_name,
p_internal_period_num => p_internal_period_num,
p_invoice_hd_rec => r_invoice_hd,
p_ln_step_length => v_ln_step_length,
p_user_id => p_user_id,
p_role_id => p_role_id);
create_invoice_ln(p_invoice_tmp_hd_rec => r_invoice_tmp_hd,
p_invoice_hd_rec => r_invoice_hd,
p_ln_step_length => v_ln_step_length,
p_session_id => p_session_id,
p_user_id => p_user_id);
split_invoice_hd(p_invoice_tmp_hd_rec => r_invoice_tmp_hd,
p_invoice_hd_rec => r_invoice_hd,
p_ln_step_length => v_ln_step_length,
p_session_id => p_session_id,
p_user_id => p_user_id,
p_role_id => p_role_id);
--插入发票行合并表
save_acr_invoice_batch(p_invoice_hd_rec => r_invoice_hd,
p_user_id => p_user_id);
End Loop;
Close cur_group_by_project_hd;
End;
Procedure create_invoice_group_by_bp(p_session_id Number,
p_invoice_date Date,
p_accounting_date Date,
p_period_name Varchar2,
p_internal_period_num Number,
p_user_id Number,
p_role_id Number) Is
r_invoice_tmp_hd invoice_hd_tmp_type;
r_invoice_hd acr_invoice_hd%Rowtype;
v_ln_step_length Number;
Cursor cur_group_by_tenant_hd Is
Select c.company_id,
c.spv_company_id,
Null As contract_id,
Null As project_id,
dt.ref_document_type As invoice_document_type,
t.bp_id,
t.invoice_title As bp_name,
hbm.tax_registry_num As bp_tax_registry_num,
t.invoice_bp_address_phone_num,
t.invoice_bp_bank_account,
t.head_description,
t.invoice_kind,
Sum(t.billing_amount) As total_amount,
t.currency,
nvl(t.exchange_rate_type, 'MANUAL') As exchange_rate_type,
nvl(t.exchange_rate_quotation, 'DIRECT QUOTATION') As exchange_rate_quotation,
nvl(t.exchange_rate, 1) As exchange_rate,
t.tax_type_id,
tt.tax_type_rate,
Null As times
From acr_invoice_create_tmp t,
con_contract c,
hls_bp_master hbm,
hls_document_type dt,
fnd_tax_type_codes tt
Where t.contract_id = c.contract_id
And c.data_class = 'NORMAL'
And t.bp_id = hbm.bp_id
And c.document_type = dt.document_type
And dt.document_category = 'CONTRACT'
And t.tax_type_id = tt.tax_type_id
And t.session_id = p_session_id
And t.checked_flag = 'Y'
Group By c.company_id,
c.spv_company_id,
t.invoice_kind,
t.bp_id,
dt.ref_document_type,
t.invoice_title,
hbm.tax_registry_num,
t.invoice_bp_address_phone_num,
t.invoice_bp_bank_account,
t.head_description,
t.currency,
nvl(t.exchange_rate_type, 'MANUAL'),
nvl(t.exchange_rate_quotation, 'DIRECT QUOTATION'),
nvl(t.exchange_rate, 1),
t.tax_type_id,
tt.tax_type_rate;
Begin
Open cur_group_by_tenant_hd;
Loop
Fetch cur_group_by_tenant_hd
Into r_invoice_tmp_hd;
Exit When cur_group_by_tenant_hd%Notfound;
create_invoice_hd(p_invoice_tmp_hd_rec => r_invoice_tmp_hd,
p_invoice_date => p_invoice_date,
p_accounting_date => p_accounting_date,
p_period_name => p_period_name,
p_internal_period_num => p_internal_period_num,
p_invoice_hd_rec => r_invoice_hd,
p_ln_step_length => v_ln_step_length,
p_user_id => p_user_id,
p_role_id => p_role_id);
create_invoice_ln(p_invoice_tmp_hd_rec => r_invoice_tmp_hd,
p_invoice_hd_rec => r_invoice_hd,
p_ln_step_length => v_ln_step_length,
p_session_id => p_session_id,
p_user_id => p_user_id);
split_invoice_hd(p_invoice_tmp_hd_rec => r_invoice_tmp_hd,
p_invoice_hd_rec => r_invoice_hd,
p_ln_step_length => v_ln_step_length,
p_session_id => p_session_id,
p_user_id => p_user_id,
p_role_id => p_role_id);
--插入发票行合并表
save_acr_invoice_batch(p_invoice_hd_rec => r_invoice_hd,
p_user_id => p_user_id);
End Loop;
Close cur_group_by_tenant_hd;
End;
--宏菱建机罚息开票
Procedure create_penalty_invoice(p_session_id Number,
p_company_id Number,
p_group_billing_method Varchar2,
p_invoice_date Date,
p_accounting_date Date,
p_user_id Number,
p_role_id Number) Is
v_period_name gld_periods.period_name%Type;
v_internal_period_num gld_periods.internal_period_num%Type;
Begin
create_invoice_check(p_session_id => p_session_id,
p_company_id => p_company_id,
p_user_id => p_user_id);
check_invoice_date(p_company_id => p_company_id,
p_invoice_data => p_invoice_date,
p_accounting_date => p_accounting_date,
p_period_name => v_period_name,
p_internal_period_num => v_internal_period_num);
If p_group_billing_method = 'GROUP_BY_CONTRACT' Then
create_penalty_group_by_con(p_session_id => p_session_id,
p_invoice_date => p_invoice_date,
p_accounting_date => p_accounting_date,
p_period_name => v_period_name,
p_internal_period_num => v_internal_period_num,
p_user_id => p_user_id,
p_role_id => p_role_id);
End If;
If p_group_billing_method = 'NO_GROUP' Then
create_penalty_no_group_by(p_session_id => p_session_id,
p_invoice_date => p_invoice_date,
p_accounting_date => p_accounting_date,
p_period_name => v_period_name,
p_internal_period_num => v_internal_period_num,
p_user_id => p_user_id,
p_role_id => p_role_id);
End If;
Delete From acr_invoice_create_tmp Where session_id = p_session_id;
Exception
When Others Then
sys_raise_app_error_pkg.raise_sys_others_error(p_message => dbms_utility.format_error_backtrace || ' ' ||
Sqlerrm,
p_created_by => p_user_id,
p_package_name => 'ACR_INVOICE_PKG',
p_procedure_function_name => 'create_invoice');
raise_application_error(sys_raise_app_error_pkg.c_error_number,
sys_raise_app_error_pkg.g_err_line_id);
End;
Procedure create_invoice(p_session_id Number,
p_company_id Number,
p_group_billing_method Varchar2,
p_invoice_date Date,
p_accounting_date Date,
p_user_id Number,
p_role_id Number) Is
v_period_name gld_periods.period_name%Type;
v_internal_period_num gld_periods.internal_period_num%Type;
Begin
create_invoice_check(p_session_id => p_session_id,
p_company_id => p_company_id,
p_user_id => p_user_id);
check_invoice_date(p_company_id => p_company_id,
p_invoice_data => p_invoice_date,
p_accounting_date => p_accounting_date,
p_period_name => v_period_name,
p_internal_period_num => v_internal_period_num);
If p_group_billing_method = 'GROUP_BY_TIMES' Then
create_invoice_group_by_times(p_session_id => p_session_id,
p_invoice_date => p_invoice_date,
p_accounting_date => p_accounting_date,
p_period_name => v_period_name,
p_internal_period_num => v_internal_period_num,
p_user_id => p_user_id,
p_role_id => p_role_id);
End If;
If p_group_billing_method = 'GROUP_BY_CONTRACT' Then
create_invoice_group_by_con(p_session_id => p_session_id,
p_invoice_date => p_invoice_date,
p_accounting_date => p_accounting_date,
p_period_name => v_period_name,
p_internal_period_num => v_internal_period_num,
p_user_id => p_user_id,
p_role_id => p_role_id);
End If;
If p_group_billing_method = 'GROUP_BY_PROJECT' Then
create_invoice_group_by_prj(p_session_id => p_session_id,
p_invoice_date => p_invoice_date,
p_accounting_date => p_accounting_date,
p_period_name => v_period_name,
p_internal_period_num => v_internal_period_num,
p_user_id => p_user_id,
p_role_id => p_role_id);
End If;
If p_group_billing_method = 'GROUP_BY_TENANT' Then
create_invoice_group_by_bp(p_session_id => p_session_id,
p_invoice_date => p_invoice_date,
p_accounting_date => p_accounting_date,
p_period_name => v_period_name,
p_internal_period_num => v_internal_period_num,
p_user_id => p_user_id,
p_role_id => p_role_id);
End If;
Delete From acr_invoice_create_tmp Where session_id = p_session_id;
Exception
When e_period_not_found Then
sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'ACR510_PERIOD_NOT_FOUND_ERROR',
p_created_by => p_user_id,
p_package_name => 'ACR_INVOICE_PKG',
p_procedure_function_name => 'create_invoice');
raise_application_error(sys_raise_app_error_pkg.c_error_number,
sys_raise_app_error_pkg.g_err_line_id);
When e_get_invoice_number_err Then
sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'ACR510_INVOICE_NUMBER_CODING_RULE_ERROR',
p_created_by => p_user_id,
p_package_name => 'ACR_INVOICE_PKG',
p_procedure_function_name => 'create_invoice');
raise_application_error(sys_raise_app_error_pkg.c_error_number,
sys_raise_app_error_pkg.g_err_line_id);
When e_billing_amt_over_err Then
sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'ACR510_BILLING_AMOUNT_OVER_AMT_ERROR',
p_created_by => p_user_id,
p_package_name => 'ACR_INVOICE_PKG',
p_procedure_function_name => 'create_invoice');
raise_application_error(sys_raise_app_error_pkg.c_error_number,
sys_raise_app_error_pkg.g_err_line_id);
When e_billing_amt_split_err Then
sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'ACR510_BILLING_AMOUNT_SPLIT_ERROR',
p_created_by => p_user_id,
p_package_name => 'ACR_INVOICE_PKG',
p_procedure_function_name => 'create_invoice');
raise_application_error(sys_raise_app_error_pkg.c_error_number,
sys_raise_app_error_pkg.g_err_line_id);
When e_invoice_limit_not_found Then
sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'ACR510_COMPANY_INVOICE_LIMIT_ERROR',
p_created_by => p_user_id,
p_package_name => 'ACR_INVOICE_PKG',
p_procedure_function_name => 'create_invoice');
raise_application_error(sys_raise_app_error_pkg.c_error_number,
sys_raise_app_error_pkg.g_err_line_id);
When e_htjs_tolerance_error Then
sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'ACR510_HTJS_TOLERANCE_ERROR',
p_created_by => p_user_id,
p_package_name => 'ACR_INVOICE_PKG',
p_procedure_function_name => 'create_invoice');
raise_application_error(sys_raise_app_error_pkg.c_error_number,
sys_raise_app_error_pkg.g_err_line_id);
When Others Then
sys_raise_app_error_pkg.raise_sys_others_error(p_message => dbms_utility.format_error_backtrace || ' ' ||
Sqlerrm,
p_created_by => p_user_id,
p_package_name => 'ACR_INVOICE_PKG',
p_procedure_function_name => 'create_invoice');
raise_application_error(sys_raise_app_error_pkg.c_error_number,
sys_raise_app_error_pkg.g_err_line_id);
End;
Function lock_acr_invoice(p_invoice_hd_id Number)
Return acr_invoice_hd%Rowtype Is
r_acr_invoice_hd_rec acr_invoice_hd%Rowtype;
Begin
Select *
Into r_acr_invoice_hd_rec
From acr_invoice_hd
Where invoice_hd_id = p_invoice_hd_id
For Update Nowait;
Return r_acr_invoice_hd_rec;
End;
Procedure delete_invoice(p_invoice_hd_id Number, p_user_id Number) Is
r_acr_invoice_hd_rec acr_invoice_hd%Rowtype;
e_invoice_delete_status_err Exception;
Begin
--锁表
r_acr_invoice_hd_rec := lock_acr_invoice(p_invoice_hd_id => p_invoice_hd_id);
--状态检查
If r_acr_invoice_hd_rec.invoice_status != g_status_new And
r_acr_invoice_hd_rec.invoice_status != 'REJECT' Then
Raise e_invoice_delete_status_err;
End If;
For c_acr_invoice_ln In (Select *
From acr_invoice_ln
Where invoice_hd_id = p_invoice_hd_id) Loop
--以负数 重新更新cashflow行状态
c_acr_invoice_ln.total_amount := -c_acr_invoice_ln.total_amount;
update_cashflow_ln(p_invoice_hd_rec => r_acr_invoice_hd_rec,
p_invoice_ln_rec => c_acr_invoice_ln,
p_user_id => p_user_id);
--删除发票行
Delete From acr_invoice_ln
Where invoice_ln_id = c_acr_invoice_ln.invoice_ln_id;
End Loop;
--删除发票头
Delete From acr_invoice_hd Where invoice_hd_id = p_invoice_hd_id;
--删除单据流
hls_document_flow_pkg.delete_document_flow(p_doc_category => 'AR_INVOICE',
p_doc_id => p_invoice_hd_id,
p_doc_line_id => Null);
--删除权限表
aut_document_authority_pkg.delete_trx_authority(p_document_category => 'AR_INVOICE',
p_document_id => p_invoice_hd_id);
--删除操作表
hls_doc_operate_history_pkg.delete_doc_operate_history_all(p_document_category => 'AR_INVOICE',
p_document_id => p_invoice_hd_id);
Exception
When e_invoice_delete_status_err Then
sys_raise_app_error_pkg.raise_user_define_error(p_message_code => '复核中的单据不能删除',
p_created_by => p_user_id,
p_package_name => 'ACR_INVOICE_PKG',
p_procedure_function_name => 'delete_invoice');
raise_application_error(sys_raise_app_error_pkg.c_error_number,
sys_raise_app_error_pkg.g_err_line_id);
When e_lock_error Then
sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'ACR510_INVOICE_LOCK_ERROR',
p_created_by => p_user_id,
p_package_name => 'ACR_INVOICE_PKG',
p_procedure_function_name => 'delete_invoice');
raise_application_error(sys_raise_app_error_pkg.c_error_number,
sys_raise_app_error_pkg.g_err_line_id);
When Others Then
sys_raise_app_error_pkg.raise_sys_others_error(p_message => dbms_utility.format_error_backtrace || ' ' ||
Sqlerrm,
p_created_by => p_user_id,
p_package_name => 'ACR_INVOICE_PKG',
p_procedure_function_name => 'delete_invoice');
raise_application_error(sys_raise_app_error_pkg.c_error_number,
sys_raise_app_error_pkg.g_err_line_id);
End;
Procedure confirm_invoice(p_invoice_hd_id Number, p_user_id Number) Is
r_acr_invoice_hd_rec acr_invoice_hd%Rowtype;
e_invoice_confirm_status_err Exception;
Begin
--锁表
r_acr_invoice_hd_rec := lock_acr_invoice(p_invoice_hd_id => p_invoice_hd_id);
--状态检查
If Not r_acr_invoice_hd_rec.invoice_status = g_status_confirming Then
Raise e_invoice_confirm_status_err;
End If;
--更新状态
Update acr_invoice_hd
Set invoice_status = g_status_confirm,
confirmed_date = Sysdate,
confirmed_by = p_user_id
Where invoice_hd_id = p_invoice_hd_id;
--生成凭证
--按照吉利要求，回传之后才生成凭证
/*acr_invoice_je_pkg.create_acr_invoice_je(p_invoice_hd_id => p_invoice_hd_id,
p_user_id => p_user_id);*/
hls_doc_operate_history_pkg.insert_doc_operate_history(p_document_category => r_acr_invoice_hd_rec.document_category,
p_document_id => p_invoice_hd_id,
p_operation_code => hls_doc_operate_history_pkg.c_confrim,
p_user_id => p_user_id,
p_operation_time => Sysdate,
p_description => Null);
Exception
When e_invoice_confirm_status_err Then
sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'ACR510_CONFIRM_INVOICE_STATUS_ERROR',
p_created_by => p_user_id,
p_package_name => 'ACR_INVOICE_PKG',
p_procedure_function_name => 'confirm_invoice');
raise_application_error(sys_raise_app_error_pkg.c_error_number,
sys_raise_app_error_pkg.g_err_line_id);
When e_lock_error Then
sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'ACR510_INVOICE_LOCK_ERROR',
p_created_by => p_user_id,
p_package_name => 'ACR_INVOICE_PKG',
p_procedure_function_name => 'confirm_invoice');
raise_application_error(sys_raise_app_error_pkg.c_error_number,
sys_raise_app_error_pkg.g_err_line_id);
When Others Then
sys_raise_app_error_pkg.raise_sys_others_error(p_message => dbms_utility.format_error_backtrace || ' ' ||
Sqlerrm,
p_created_by => p_user_id,
p_package_name => 'ACR_INVOICE_PKG',
p_procedure_function_name => 'confirm_invoice');
raise_application_error(sys_raise_app_error_pkg.c_error_number,
sys_raise_app_error_pkg.g_err_line_id);
End;
Procedure reverse_invoice(p_invoice_hd_id Number,
p_reverse_date Date,
p_vat_red_notice_num Varchar2,
p_user_id Number,
p_role_id Number) Is
v_period_name gld_periods.period_name%Type;
v_internal_period_num gld_periods.internal_period_num%Type;
v_acr_invoice_hd_id Number;
v_acr_invoice_ln_id Number;
r_acr_invoice_hd_rec acr_invoice_hd%Rowtype;
r_acr_invoice_hd_r_rec acr_invoice_hd%Rowtype; --反冲的发票头记录
e_reverse_date_error Exception;
e_invoice_reverse_status_err Exception;
e_invoice_reverse_flag_err Exception;
Begin
--锁表
r_acr_invoice_hd_rec := lock_acr_invoice(p_invoice_hd_id => p_invoice_hd_id);
If p_reverse_date < r_acr_invoice_hd_rec.accounting_date Then
Raise e_reverse_date_error;
End If;
check_invoice_date(p_company_id => r_acr_invoice_hd_rec.company_id,
p_invoice_data => p_reverse_date,
p_accounting_date => p_reverse_date,
p_period_name => v_period_name,
p_internal_period_num => v_internal_period_num);
--状态检查
If Not r_acr_invoice_hd_rec.invoice_status = g_status_confirm Then
Raise e_invoice_reverse_status_err;
End If;
If Not nvl(r_acr_invoice_hd_rec.reversed_flag, 'N') = 'N' Then
Raise e_invoice_reverse_flag_err;
End If;
Select acr_invoice_hd_s.nextval Into v_acr_invoice_hd_id From dual;
--插入反冲的发票头和行记录
r_acr_invoice_hd_r_rec := r_acr_invoice_hd_rec;
r_acr_invoice_hd_r_rec.invoice_hd_id := v_acr_invoice_hd_id;
r_acr_invoice_hd_r_rec.document_number := get_invoice_number(p_document_type => r_acr_invoice_hd_r_rec.document_type,
p_transaction_date => r_acr_invoice_hd_r_rec.accounting_date,
p_company_id => r_acr_invoice_hd_r_rec.company_id,
p_user_id => p_user_id);
r_acr_invoice_hd_r_rec.invoice_number := Null;
r_acr_invoice_hd_r_rec.total_amount := -r_acr_invoice_hd_r_rec.total_amount;
r_acr_invoice_hd_r_rec.tax_amount := -r_acr_invoice_hd_r_rec.tax_amount;
r_acr_invoice_hd_r_rec.invoice_date := r_acr_invoice_hd_rec.invoice_date;
r_acr_invoice_hd_r_rec.accounting_date := p_reverse_date;
r_acr_invoice_hd_r_rec.period_name := v_period_name;
r_acr_invoice_hd_r_rec.internal_period_num := v_internal_period_num;
r_acr_invoice_hd_r_rec.create_je_flag := 'N';
r_acr_invoice_hd_r_rec.gld_interface_flag := 'N';
r_acr_invoice_hd_r_rec.reversed_flag := 'R';
r_acr_invoice_hd_r_rec.source_invoice_header_id := p_invoice_hd_id;
r_acr_invoice_hd_r_rec.vat_interface_status := 'UNTRANSFERED';
r_acr_invoice_hd_r_rec.vat_invoice_code := Null;
r_acr_invoice_hd_r_rec.vat_invoice_status := Null;
r_acr_invoice_hd_r_rec.vat_red_notice_num := p_vat_red_notice_num;
r_acr_invoice_hd_r_rec.ref_vat_invoice_code := r_acr_invoice_hd_rec.vat_invoice_code;
r_acr_invoice_hd_r_rec.ref_invoice_number := r_acr_invoice_hd_rec.invoice_number;
r_acr_invoice_hd_r_rec.created_by := p_user_id;
r_acr_invoice_hd_r_rec.creation_date := Sysdate;
r_acr_invoice_hd_r_rec.last_updated_by := p_user_id;
r_acr_invoice_hd_r_rec.last_update_date := Sysdate;
insert_invoice_hd(r_acr_invoice_hd_r_rec);
For c_acr_invoice_ln_r_rec In (Select *
From acr_invoice_ln
Where invoice_hd_id = p_invoice_hd_id) Loop
Select acr_invoice_ln_s.nextval Into v_acr_invoice_ln_id From dual;
c_acr_invoice_ln_r_rec.invoice_hd_id := r_acr_invoice_hd_r_rec.invoice_hd_id;
c_acr_invoice_ln_r_rec.invoice_ln_id := v_acr_invoice_ln_id;
c_acr_invoice_ln_r_rec.quantity := -c_acr_invoice_ln_r_rec.quantity;
c_acr_invoice_ln_r_rec.net_price := -c_acr_invoice_ln_r_rec.net_price;
c_acr_invoice_ln_r_rec.tax_amount := -c_acr_invoice_ln_r_rec.tax_amount;
c_acr_invoice_ln_r_rec.net_amount := -c_acr_invoice_ln_r_rec.net_amount;
c_acr_invoice_ln_r_rec.total_amount := -c_acr_invoice_ln_r_rec.total_amount;
c_acr_invoice_ln_r_rec.created_by := p_user_id;
c_acr_invoice_ln_r_rec.creation_date := Sysdate;
c_acr_invoice_ln_r_rec.last_updated_by := p_user_id;
c_acr_invoice_ln_r_rec.last_update_date := Sysdate;
insert_invoice_ln(c_acr_invoice_ln_r_rec);
update_cashflow_ln(p_invoice_hd_rec => r_acr_invoice_hd_r_rec,
p_invoice_ln_rec => c_acr_invoice_ln_r_rec,
p_user_id => p_user_id);
End Loop;
insert_doc_flow(p_invoice_hd_rec => r_acr_invoice_hd_r_rec);
insert_trx_user_authority(p_invoice_hd_rec => r_acr_invoice_hd_r_rec,
p_user_id => p_user_id);
--更新状态
Update acr_invoice_hd
Set reversed_flag = 'W',
reverse_date = p_reverse_date,
source_invoice_header_id = r_acr_invoice_hd_r_rec.invoice_hd_id,
last_updated_by = p_user_id,
last_update_date = Sysdate
Where invoice_hd_id = p_invoice_hd_id;
--生成反冲凭证
acr_invoice_je_pkg.create_acr_invoice_reverse_je(p_invoice_hd_id => r_acr_invoice_hd_r_rec.invoice_hd_id,
p_user_id => p_user_id);
hls_doc_operate_history_pkg.insert_doc_operate_history(p_document_category => r_acr_invoice_hd_rec.document_category,
p_document_id => p_invoice_hd_id,
p_operation_code => hls_doc_operate_history_pkg.c_reverse,
p_user_id => p_user_id,
p_operation_time => Sysdate,
p_description => Null);
Exception
When e_period_not_found Then
sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'ACR510_PERIOD_NOT_FOUND_ERROR',
p_created_by => p_user_id,
p_package_name => 'ACR_INVOICE_PKG',
p_procedure_function_name => 'reverse_invoice');
raise_application_error(sys_raise_app_error_pkg.c_error_number,
sys_raise_app_error_pkg.g_err_line_id);
When e_reverse_date_error Then
sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'ACR510_REVERSE_DATE_ERROR',
p_created_by => p_user_id,
p_package_name => 'ACR_INVOICE_PKG',
p_procedure_function_name => 'reverse_invoice');
raise_application_error(sys_raise_app_error_pkg.c_error_number,
sys_raise_app_error_pkg.g_err_line_id);
When e_invoice_reverse_status_err Then
sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'ACR510_REVERSE_INVOICE_STATUS_ERROR',
p_created_by => p_user_id,
p_package_name => 'ACR_INVOICE_PKG',
p_procedure_function_name => 'reverse_invoice');
raise_application_error(sys_raise_app_error_pkg.c_error_number,
sys_raise_app_error_pkg.g_err_line_id);
When e_invoice_reverse_flag_err Then
sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'ACR510_REVERSE_INVOICE_REVERSE_FLAG_ERROR',
p_created_by => p_user_id,
p_package_name => 'ACR_INVOICE_PKG',
p_procedure_function_name => 'reverse_invoice');
raise_application_error(sys_raise_app_error_pkg.c_error_number,
sys_raise_app_error_pkg.g_err_line_id);
When e_lock_error Then
sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'ACR510_INVOICE_LOCK_ERROR',
p_created_by => p_user_id,
p_package_name => 'ACR_INVOICE_PKG',
p_procedure_function_name => 'reverse_invoice');
raise_application_error(sys_raise_app_error_pkg.c_error_number,
sys_raise_app_error_pkg.g_err_line_id);
When Others Then
sys_raise_app_error_pkg.raise_sys_others_error(p_message => dbms_utility.format_error_backtrace || ' ' ||
Sqlerrm,
p_created_by => p_user_id,
p_package_name => 'ACR_INVOICE_PKG',
p_procedure_function_name => 'reverse_invoice');
raise_application_error(sys_raise_app_error_pkg.c_error_number,
sys_raise_app_error_pkg.g_err_line_id);
End;
--add by Yenick 宏菱项目 增加发票行合并
Procedure invoice_row_merge(p_invoice_hd_id In Number,
p_user_id Number,
p_merge_flag Varchar2) Is
r_acr_invoice_batch_ln acr_invoice_batch_ln%Rowtype;
r_acr_invoice_ln acr_invoice_ln%Rowtype;
r_acr_invoice_hd acr_invoice_hd%Rowtype;
v_price Number;
v_net_price Number;
v_tax_type_id Number;
v_tax_type_rate Number;
v_tax_included_flag Varchar2(100);
v_tax_amount Number;
v_net_amount Number;
v_total_amount Number;
Begin
Select t.*
Into r_acr_invoice_hd
From acr_invoice_hd t
Where t.invoice_hd_id = p_invoice_hd_id;
If r_acr_invoice_hd.ref_merge_flag = p_merge_flag Then
Return;
End If;
Update acr_invoice_hd t
Set t.ref_merge_flag = t.merge_flag
Where t.invoice_hd_id = p_invoice_hd_id;
Select t.*
Into r_acr_invoice_ln
From acr_invoice_ln t
Where t.invoice_hd_id = p_invoice_hd_id
And rownum = 1;
--删除行合并表中的行明细记录
Delete From acr_invoice_batch_ln t
Where t.invoice_hd_id = p_invoice_hd_id;
If r_acr_invoice_hd.merge_flag = 'Y' Then
Select Sum(t.price),
Sum(t.net_price),
Sum(t.tax_amount),
Sum(t.net_amount),
Sum(t.total_amount)
Into v_price,
v_net_price,
v_tax_amount,
v_net_amount,
v_total_amount
From acr_invoice_ln t
Where t.invoice_hd_id = p_invoice_hd_id;
r_acr_invoice_batch_ln.invoice_batch_ln_id := acr_invoice_batch_ln_s.nextval;
r_acr_invoice_batch_ln.invoice_hd_id := p_invoice_hd_id;
r_acr_invoice_batch_ln.line_type := 'SERVICE';
r_acr_invoice_batch_ln.line_number := 10;
r_acr_invoice_batch_ln.quantity := 1;
r_acr_invoice_batch_ln.price := v_price;
r_acr_invoice_batch_ln.price_quantity := 1;
r_acr_invoice_batch_ln.net_price := v_net_price;
r_acr_invoice_batch_ln.tax_type_id := r_acr_invoice_ln.tax_type_id;
r_acr_invoice_batch_ln.tax_type_rate := r_acr_invoice_ln.tax_type_rate;
r_acr_invoice_batch_ln.tax_included_flag := r_acr_invoice_ln.tax_included_flag;
r_acr_invoice_batch_ln.product_name := r_acr_invoice_ln.product_name;
r_acr_invoice_batch_ln.tax_amount := v_tax_amount;
r_acr_invoice_batch_ln.net_amount := v_net_amount;
r_acr_invoice_batch_ln.total_amount := v_total_amount;
r_acr_invoice_batch_ln.created_by := p_user_id;
r_acr_invoice_batch_ln.creation_date := Sysdate;
r_acr_invoice_batch_ln.last_updated_by := p_user_id;
r_acr_invoice_batch_ln.last_update_date := Sysdate;
Insert Into acr_invoice_batch_ln Values r_acr_invoice_batch_ln;
Else
/*update acr_invoice_hd t
set t.merge_flag = 'N',
t.last_updated_by = p_user_id,
t.last_update_date = sysdate
where t.invoice_hd_id = p_invoice_hd_id;*/
For rec_acr_invoice_ln In (Select *
From acr_invoice_ln t
Where t.invoice_hd_id = p_invoice_hd_id) Loop
r_acr_invoice_batch_ln.invoice_batch_ln_id := acr_invoice_batch_ln_s.nextval;
r_acr_invoice_batch_ln.invoice_hd_id := p_invoice_hd_id;
r_acr_invoice_batch_ln.line_type := 'SERVICE';
r_acr_invoice_batch_ln.line_number := rec_acr_invoice_ln.line_number;
r_acr_invoice_batch_ln.quantity := 1;
r_acr_invoice_batch_ln.price := rec_acr_invoice_ln.price;
r_acr_invoice_batch_ln.price_quantity := 1;
r_acr_invoice_batch_ln.net_price := rec_acr_invoice_ln.net_price;
r_acr_invoice_batch_ln.tax_type_id := r_acr_invoice_ln.tax_type_id;
r_acr_invoice_batch_ln.tax_type_rate := r_acr_invoice_ln.tax_type_rate;
r_acr_invoice_batch_ln.tax_included_flag := r_acr_invoice_ln.tax_included_flag;
r_acr_invoice_batch_ln.product_name := rec_acr_invoice_ln.product_name;
r_acr_invoice_batch_ln.tax_amount := rec_acr_invoice_ln.tax_amount;
r_acr_invoice_batch_ln.net_amount := rec_acr_invoice_ln.net_amount;
r_acr_invoice_batch_ln.total_amount := rec_acr_invoice_ln.total_amount;
r_acr_invoice_batch_ln.created_by := p_user_id;
r_acr_invoice_batch_ln.creation_date := Sysdate;
r_acr_invoice_batch_ln.last_updated_by := p_user_id;
r_acr_invoice_batch_ln.last_update_date := Sysdate;
Insert Into acr_invoice_batch_ln Values r_acr_invoice_batch_ln;
End Loop;
End If;
Exception
When Others Then
sys_raise_app_error_pkg.raise_sys_others_error(p_message => dbms_utility.format_error_backtrace || ' ' ||
Sqlerrm,
p_created_by => p_user_id,
p_package_name => 'ACR_INVOICE_PKG',
p_procedure_function_name => 'invoice_row_merge');
raise_application_error(sys_raise_app_error_pkg.c_error_number,
sys_raise_app_error_pkg.g_err_line_id);
End invoice_row_merge;
--发票提交
Procedure acr_invoice_submit(p_batch_id Number, p_user_id Number) Is
r_acr_invoice_hd acr_invoice_hd%Rowtype;
v_instance_id Number;
v_document_info Varchar2(1000);
v_count Number;
v_bp_name Varchar2(100);
Begin
Update acr_invoice_hd t
Set t.invoice_status = 'APPROVING'
Where t.batch_id = p_batch_id;
Select Count(1)
Into v_count
From acr_invoice_hd t
Where t.batch_id = p_batch_id;
Select t.invoice_bp_name
Into v_bp_name
From acr_invoice_hd_v t
Where t.batch_id = p_batch_id
And rownum = 1;
If v_count = 1 Then
v_document_info := v_bp_name || '提交发票审批';
Elsif v_count > 1 Then
v_document_info := v_bp_name || '等提交发票审批';
End If;
--提交审批
hls_workflow_pkg.workflow_start(p_instance_id => v_instance_id,
p_document_category => 'AR_INVOICE',
p_document_type => 'STD',
p_company_id => 1,
p_user_id => p_user_id,
p_parameter_1 => 'USER_ID',
p_parameter_1_value => p_user_id,
p_parameter_2 => 'DOCUMENT_INFO',
p_parameter_2_value => v_document_info,
p_parameter_3 => 'BATCH_ID',
p_parameter_3_value => p_batch_id,
p_parameter_4 => 'SUBMIT_DATE',
p_parameter_4_value => to_char(Sysdate,
'yyyy"年"mm"月"dd"日"'));
End acr_invoice_submit;
--更新为同一批次
Procedure acr_invoice_update_batch(p_user_id Number,
p_batch_id Number,
p_invoice_hd_id In Number) Is
-- r_acr_invoice_hd_batch acr_invoice_hd_batch%rowtype;
Begin
Update acr_invoice_hd t
Set t.batch_id = p_batch_id
Where t.invoice_hd_id = p_invoice_hd_id;
End;
/* function ss(p_batch_id out number) return number is
v_id number;
begin
select ACR_INVOICE_HD_BATCH_S.NEXTVAL INTO v_id from dual;
p_batch_id := v_id;
return p_batch_id;
end;*/
--发票审批通过
Procedure acr_invoice_approved(p_batch_id Number, p_user_id Number) Is
Begin
Update acr_invoice_hd t
Set t.invoice_status = 'CONFIRM',
t.last_update_date = Sysdate,
t.last_updated_by = p_user_id
Where t.batch_id = p_batch_id;
End;
--发票审批拒绝
Procedure acr_invoice_reject(p_batch_id Number, p_user_id Number) Is
Begin
Update acr_invoice_hd t
Set t.invoice_status = 'REJECT',
t.last_update_date = Sysdate,
t.last_updated_by = p_user_id
Where t.batch_id = p_batch_id;
End;
Procedure acr_invoice_status_change(p_invoice_hd Number,
p_want_status In Varchar2,
p_user_id In Number) Is
v_count Number;
e_exists_err Exception;
e_status_err Exception;
Begin
If p_want_status Is Null Then
Raise e_status_err;
End If;
Select Count(1)
Into v_count
From acr_invoice_batch_ln bn
Where bn.invoice_hd_id = p_invoice_hd;
If v_count < 1 Then
Raise e_exists_err;
End If;
Update acr_invoice_hd t
Set t.invoice_status = p_want_status
Where t.invoice_hd_id = p_invoice_hd;
Exception
When e_exists_err Then
sys_raise_app_error_pkg.raise_sys_others_error(p_message => '发票明细行有误，请检查！',
p_created_by => p_user_id,
p_package_name => 'ACR_INVOICE_PKG',
p_procedure_function_name => 'acr_invoice_status_change');
raise_application_error(sys_raise_app_error_pkg.c_error_number,
sys_raise_app_error_pkg.g_err_line_id);
When e_status_err Then
sys_raise_app_error_pkg.raise_sys_others_error(p_message => '发票状态有误！',
p_created_by => p_user_id,
p_package_name => 'ACR_INVOICE_PKG',
p_procedure_function_name => 'acr_invoice_status_change');
raise_application_error(sys_raise_app_error_pkg.c_error_number,
sys_raise_app_error_pkg.g_err_line_id);
End;
Procedure vat_account_data_process(p_base_period Varchar2,
p_session_id Number,
p_user_id Number) Is
vat_account_tmp_cur vat_account_tmp%Rowtype;
v_exist_flag Number;
v_purchase_amount_flag Number;
v_penalty_amount_flag Number;
Begin
Select Count(1)
Into v_exist_flag
From vat_account_tmp t
Where t.session_id = p_session_id;
If v_exist_flag > 0 Then
Delete From vat_account_tmp t Where t.session_id = p_session_id;
End If;
For cur_temp In (Select ah.invoice_kind,
to_char(ah.invoice_date, 'yyyymm') invoice_month,
ah.invoice_date,
ah.invoice_hd_id,
al.invoice_ln_id,
ah.invoice_number,
ah.total_amount,
Case
When al.cf_item = 2 Then
Case
When (Select to_char(h.journal_date, 'yyyymm')
From hls_journal_header h,
hls_journal_detail l
Where h.journal_header_id =
l.journal_header_id
And h.je_transaction_code =
'LEASE_INCEPT'
And l.je_template_ln_id = 8797
And l.source_doc_id = ah.contract_id
And rownum = 1) >= p_base_period Then
'N'
Else
'Y'
End
When al.cf_item <> 9 Then
Case
When (Select to_char(h.journal_date, 'yyyymm')
From hls_journal_header h,
hls_journal_detail l
Where h.journal_header_id =
l.journal_header_id
And h.je_transaction_code =
'HL_SALES_TAX_ACT_NEW'
And l.cashflow_id = al.cashflow_id
And rownum = 1) >= p_base_period Then
'N'
Else
'Y'
End
Else
Case
When (Select to_char(h.journal_date, 'yyyymm')
From hls_journal_header h,
hls_journal_detail l
Where h.journal_header_id =
l.journal_header_id
And h.je_transaction_code =
'HL_CSH_CONSOLIDATION_DALAY'
And l.journal_line_id = al.ref_n04
And rownum = 1) >= p_base_period Then
'N'
Else
'Y'
End
End for_invoice_flag,
ah.bp_name,
nvl(al.contract_number,
(Select l.reference3
From hls_journal_header h,
hls_journal_detail l
Where h.journal_header_id =
l.journal_header_id
And h.je_transaction_code =
'HL_CSH_CONSOLIDATION_DALAY'
And l.cashflow_id = al.ref_n04)) contract_number,
ah.return_tax_amount,
Case
When al.cf_item = 2 Then
(Select to_char(h.journal_date, 'yyyymm')
From hls_journal_header h,
hls_journal_detail l
Where h.journal_header_id =
l.journal_header_id
And h.je_transaction_code = 'LEASE_INCEPT'
And l.je_template_ln_id = 8797
And l.source_doc_id = ah.contract_id
And rownum = 1)
When al.cf_item <> 9 Then
(Select to_char(h.journal_date, 'yyyymm')
From hls_journal_header h,
hls_journal_detail l
Where h.journal_header_id =
l.journal_header_id
And h.je_transaction_code =
'HL_SALES_TAX_ACT_NEW'
And l.cashflow_id = al.cashflow_id
And rownum = 1)
Else
(Select to_char(h.journal_date, 'yyyymm')
From hls_journal_header h,
hls_journal_detail l
Where h.journal_header_id =
l.journal_header_id
And h.je_transaction_code =
'HL_CSH_CONSOLIDATION_DALAY'
And l.journal_line_id = al.ref_n04
And rownum = 1)
End je_month,
Case
When al.cf_item <> 9 Then
(Select vat_due_amount
From con_contract_cashflow
Where cashflow_id = al.cashflow_id)
Else
(Select l.amount_cr
From hls_journal_header h,
hls_journal_detail l,
con_contract t
Where h.journal_header_id =
l.journal_header_id
And h.je_transaction_code =
'HL_CSH_CONSOLIDATION_DALAY'
And t.contract_id = ah.contract_id
And t.contract_number = l.reference3
And h.journal_header_id = al.ref_n03
And l.reference6 = 'A5'
And rownum = 1)
End amount_cr,
Case
When al.cf_item = 2 Then
(Select h.journal_date
From hls_journal_header h,
hls_journal_detail l
Where h.journal_header_id =
l.journal_header_id
And h.je_transaction_code = 'LEASE_INCEPT'
And l.je_template_ln_id = 8797
And l.source_doc_id = ah.contract_id
And rownum = 1)
When al.cf_item <> 9 Then
(Select h.journal_date
From hls_journal_header h,
hls_journal_detail l
Where h.journal_header_id =
l.journal_header_id
And h.je_transaction_code =
'HL_SALES_TAX_ACT_NEW'
And l.cashflow_id = al.cashflow_id
And rownum = 1)
Else
(Select h.journal_date
From hls_journal_header h,
hls_journal_detail l
Where h.journal_header_id =
l.journal_header_id
And h.je_transaction_code =
'HL_CSH_CONSOLIDATION_DALAY'
And l.journal_line_id = al.ref_n04
And rownum = 1)
End je_date,
Case
When al.ref_n04 Is Not Null Then
(Select h.sbo_num
From hls_journal_header h,
hls_journal_detail l
Where h.journal_header_id =
l.journal_header_id
And h.je_transaction_code =
'HL_CSH_CONSOLIDATION_DALAY'
And l.journal_line_id = al.ref_n04)
Else
decode(al.cf_item,
2,
(Select h.sbo_num
From hls_journal_header h,
hls_journal_detail l
Where h.journal_header_id =
l.journal_header_id
And h.je_transaction_code =
'LEASE_INCEPT'
And l.je_template_ln_id = 8797
And l.source_doc_id = ah.contract_id
And ((trunc(h.journal_date) <=
trunc(to_date('2020-01-31',
'yyyy-mm-dd')) And
h.description = 'MANUAL_IMPORT') Or
(trunc(h.journal_date) >
trunc(to_date('2020-01-31',
'yyyy-mm-dd'))))),
(Select h.sbo_num
From hls_journal_header h,
hls_journal_detail l
Where h.journal_header_id =
l.journal_header_id
And h.je_transaction_code =
'HL_SALES_TAX_ACT_NEW'
And l.cashflow_id = al.cashflow_id
And ((trunc(h.journal_date) <=
trunc(to_date('2020-01-31',
'yyyy-mm-dd')) And
h.description = 'MANUAL_IMPORT') Or
(trunc(h.journal_date) >
trunc(to_date('2020-01-31',
'yyyy-mm-dd'))))
And rownum = 1))
End journal_num,
decode(al.cf_item,
1,
to_char(al.times),
2,
'首付款',
8,
'留购金',
9,
'违约金') times,
al.cashflow_id,
al.cf_item,
Case
When al.cf_item <> 9 Then
(Select due_date
From con_contract_cashflow
Where cashflow_id = al.cashflow_id)
Else
(Select h.journal_date
From hls_journal_header h,
hls_journal_detail l
Where h.journal_header_id =
l.journal_header_id
And h.je_transaction_code =
'HL_CSH_CONSOLIDATION_DALAY'
And l.journal_line_id = al.ref_n04)
End due_date,
Case
When al.cf_item <> 9 Then
(Select due_amount
From con_contract_cashflow
Where cashflow_id = al.cashflow_id)
Else
(Select l.amount_dr
From hls_journal_header h,
hls_journal_detail l
Where h.journal_header_id =
l.journal_header_id
And h.je_transaction_code =
'HL_CSH_CONSOLIDATION_DALAY'
And l.journal_line_id = al.ref_n04)
End due_amount,
decode(cf_item,
2,
(Select due_amount
From con_contract_cashflow
Where cashflow_id = al.cashflow_id),
(Select principal
From con_contract_cashflow
Where cashflow_id = al.cashflow_id)) principal,
decode(cf_item,
8,
(Select due_amount
From con_contract_cashflow
Where cashflow_id = al.cashflow_id),
9,
(Select l.amount_dr
From hls_journal_header h,
hls_journal_detail l
Where h.journal_header_id =
l.journal_header_id
And h.je_transaction_code =
'HL_CSH_CONSOLIDATION_DALAY'
And l.journal_line_id = al.ref_n04),
(Select interest
From con_contract_cashflow
Where cashflow_id = al.cashflow_id)) interest,
Case
When al.cf_item <> 9 Then
(Select vat_due_amount
From con_contract_cashflow
Where cashflow_id = al.cashflow_id)
Else
(Select l.amount_cr
From hls_journal_header h,
hls_journal_detail l,
con_contract t
Where h.journal_header_id =
l.journal_header_id
And h.je_transaction_code =
'HL_CSH_CONSOLIDATION_DALAY'
And t.contract_id = ah.contract_id
And t.contract_number = l.reference3
And h.journal_header_id = al.ref_n03
And l.reference6 = 'A5'
And rownum = 1)
End vat_due_amount
From acr_invoice_hd ah, acr_invoice_ln_lv al
Where ah.invoice_hd_id = al.invoice_hd_id
And ah.vat_interface_status = 'BACK'
And ah.reversed_flag = 'N'
And to_char(ah.invoice_date, 'yyyymm') =
p_base_period
And (Exists
(Select 1
From hls_journal_header hh,
hls_journal_detail hl
Where hh.journal_header_id =
hl.journal_header_id
And hh.sbo_num Is Not Null
And ((hh.je_transaction_code =
'HL_SALES_TAX_ACT_NEW' And
hl.cashflow_id = al.cashflow_id And
((trunc(hh.journal_date) <=
trunc(to_date('2020-01-31',
'yyyy-mm-dd')) And
hh.description = 'MANUAL_IMPORT') Or
(trunc(hh.journal_date) >
trunc(to_date('2020-01-31',
'yyyy-mm-dd'))))
) Or
(hh.je_transaction_code = 'LEASE_INCEPT' And
hl.je_template_ln_id = 8797 And
hl.source_doc_id = ah.contract_id And
al.cf_item = 2 And
((trunc(hh.journal_date) <=
trunc(to_date('2020-01-31',
'yyyy-mm-dd')) And
hh.description = 'MANUAL_IMPORT') Or
(trunc(hh.journal_date) >
trunc(to_date('2020-01-31',
'yyyy-mm-dd'))))))
Or (al.ref_n04 Is Not Null And
al.ref_n04 = hl.journal_line_id And
(hh.je_transaction_code =
'HL_CSH_CONSOLIDATION_DALAY' And
((trunc(hh.journal_date) <=
trunc(to_date('2020-01-31',
'yyyy-mm-dd')) And
hh.description = 'MANUAL_IMPORT') Or
(trunc(hh.journal_date) >
trunc(to_date('2020-01-31',
'yyyy-mm-dd'))))))))) Loop
vat_account_tmp_cur.vct_id := vat_account_tmp_s.nextval;
vat_account_tmp_cur.session_id := p_session_id;
vat_account_tmp_cur.invoice_hd_id := cur_temp.invoice_hd_id;
vat_account_tmp_cur.invoice_line_id := cur_temp.invoice_ln_id;
vat_account_tmp_cur.contract_id := Null;
vat_account_tmp_cur.cashflow_id := cur_temp.cashflow_id;
vat_account_tmp_cur.invoice_kind := cur_temp.invoice_kind;
vat_account_tmp_cur.tax_apply_month := cur_temp.invoice_month;
vat_account_tmp_cur.invoice_date := cur_temp.invoice_date;
vat_account_tmp_cur.invoice_number := cur_temp.invoice_number;
--含税金额
vat_account_tmp_cur.invoice_tax_amount := cur_temp.total_amount;
vat_account_tmp_cur.apply_flag := cur_temp.for_invoice_flag;
vat_account_tmp_cur.tenant_name := cur_temp.bp_name;
vat_account_tmp_cur.contract_number := cur_temp.contract_number;
vat_account_tmp_cur.acr_tax_amount := cur_temp.return_tax_amount;
vat_account_tmp_cur.journal_tax_amount := cur_temp.amount_cr;
vat_account_tmp_cur.journal_month := cur_temp.je_month;
vat_account_tmp_cur.journal_date := cur_temp.je_date;
vat_account_tmp_cur.journal_num := cur_temp.journal_num;
vat_account_tmp_cur.times := cur_temp.times;
vat_account_tmp_cur.cf_item := cur_temp.cf_item;
vat_account_tmp_cur.due_date := cur_temp.due_date;
vat_account_tmp_cur.due_amount := cur_temp.due_amount;
vat_account_tmp_cur.principal := cur_temp.principal;
vat_account_tmp_cur.interest := cur_temp.interest;
vat_account_tmp_cur.tax_amount := cur_temp.vat_due_amount;
vat_account_tmp_cur.created_by := p_user_id;
vat_account_tmp_cur.creation_date := Sysdate;
vat_account_tmp_cur.last_updated_by := p_user_id;
vat_account_tmp_cur.last_update_date := Sysdate;
Insert Into vat_account_tmp Values vat_account_tmp_cur;
End Loop;
--插入调差凭证
For cur_distance_je In (Select l.amount_cr,
ach.invoice_hd_id,
to_char(h.journal_date, 'yyyymm') je_month,
h.journal_date,
h.sbo_num
From hls_journal_header h,
hls_journal_detail l,
acr_invoice_hd ach
Where l.journal_header_id = h.journal_header_id
And h.source_id = ach.invoice_hd_id
And h.sbo_num Is Not Null
And ach.invoice_hd_id In
(Select Distinct t.invoice_hd_id
From vat_account_tmp t
Where t.session_id = p_session_id)
And l.reference6 = 'A5') Loop
Insert Into vat_account_tmp
(vct_id,
session_id,
invoice_hd_id,
journal_tax_amount,
journal_month,
journal_date,
journal_num,
description,
created_by,
creation_date,
last_updated_by,
last_update_date)
Values
(vat_account_tmp_s.nextval,
p_session_id,
cur_distance_je.invoice_hd_id,
cur_distance_je.amount_cr,
cur_distance_je.je_month,
cur_distance_je.journal_date,
cur_distance_je.sbo_num,
--调差凭证标志
'ADJUST_JOURNAL',
p_user_id,
Sysdate,
p_user_id,
Sysdate);
End Loop;
--更新表中的数据
--统计不是本金利益分开开票的情况
For acr_invoice_ln In (Select trunc(Min(t.due_date)) min_due_date,
t.invoice_hd_id
From vat_account_tmp t,
acr_invoice_ln l,
con_contract_cashflow ccc
Where t.session_id = p_session_id
And t.cashflow_id = ccc.cashflow_id
And l.invoice_ln_id = t.invoice_line_id
And l.total_amount = ccc.due_amount
Group By t.invoice_hd_id) Loop
Update vat_account_tmp vat
Set vat.invoice_kind = '',
vat.apply_flag = '',
vat.invoice_date = '',
vat.invoice_number = '',
vat.invoice_tax_amount = '',
vat.tax_apply_month = '',
vat.tenant_name = '',
vat.contract_number = '',
vat.acr_tax_amount = ''
Where vat.invoice_hd_id = acr_invoice_ln.invoice_hd_id
And vat.session_id = p_session_id
And trunc(vat.due_date) > trunc(acr_invoice_ln.min_due_date);
--单独判断最后一期租金和留购金的情况，删除留购金的数据
Select Count(1)
Into v_purchase_amount_flag
From vat_account_tmp t
Where t.session_id = p_session_id
And t.invoice_hd_id = acr_invoice_ln.invoice_hd_id
And trunc(t.due_date) = trunc(acr_invoice_ln.min_due_date);
If v_purchase_amount_flag > 1 Then
Update vat_account_tmp vat
Set vat.invoice_kind = '',
vat.apply_flag = '',
vat.invoice_date = '',
vat.invoice_number = '',
vat.invoice_tax_amount = '',
vat.tax_apply_month = '',
vat.tenant_name = '',
vat.contract_number = '',
vat.acr_tax_amount = ''
Where vat.invoice_hd_id = acr_invoice_ln.invoice_hd_id
And vat.session_id = p_session_id
And trunc(vat.due_date) = trunc(acr_invoice_ln.min_due_date)
And vat.cf_item = 8;
End If;
End Loop;
--本利拆开
For acr_invoice_ln_dif In (Select t.invoice_line_id,
t.invoice_tax_amount,
ccc.interest,
ccc.principal
From vat_account_tmp t,
con_contract_cashflow ccc,
acr_invoice_ln l
Where t.session_id = p_session_id
And t.cashflow_id = ccc.cashflow_id
And l.invoice_ln_id = t.invoice_line_id
And l.total_amount <> ccc.due_amount) Loop
--将利息置空
If acr_invoice_ln_dif.invoice_tax_amount =
acr_invoice_ln_dif.interest Then
Update vat_account_tmp vat
Set vat.journal_tax_amount = '',
vat.journal_month = '',
vat.journal_date = '',
vat.journal_num = '',
vat.times = '',
vat.due_date = '',
vat.due_amount = '',
vat.principal = '',
vat.interest = '',
vat.tax_amount = ''
Where vat.invoice_line_id = acr_invoice_ln_dif.invoice_line_id;
End If;
End Loop;
--罚息的数据单独处理
For acr_invoice_ln In (Select trunc(Min(t.due_date)) min_due_date,
t.invoice_hd_id,
Min(t.invoice_line_id) min_invoice_ln_id
From vat_account_tmp t
Where t.session_id = p_session_id
And t.cf_item = 9
Group By t.invoice_hd_id) Loop
Update vat_account_tmp vat
Set vat.invoice_kind = '',
vat.apply_flag = '',
vat.invoice_date = '',
vat.invoice_number = '',
vat.invoice_tax_amount = '',
vat.tax_apply_month = '',
vat.tenant_name = '',
vat.contract_number = '',
vat.acr_tax_amount = ''
Where vat.invoice_hd_id = acr_invoice_ln.invoice_hd_id
And vat.session_id = p_session_id
And trunc(vat.due_date) > trunc(acr_invoice_ln.min_due_date)
And vat.cf_item = 9;
--如果罚息是同一天的，也需要单独处理
Select Count(1)
Into v_penalty_amount_flag
From vat_account_tmp vat
Where vat.invoice_hd_id = acr_invoice_ln.invoice_hd_id
And vat.session_id = p_session_id
And trunc(vat.due_date) = trunc(acr_invoice_ln.min_due_date);
If v_penalty_amount_flag > 1 Then
Update vat_account_tmp vat
Set vat.invoice_kind = '',
vat.apply_flag = '',
vat.invoice_date = '',
vat.invoice_number = '',
vat.invoice_tax_amount = '',
vat.tax_apply_month = '',
vat.tenant_name = '',
vat.contract_number = '',
vat.acr_tax_amount = ''
Where vat.invoice_hd_id = acr_invoice_ln.invoice_hd_id
And vat.session_id = p_session_id
And trunc(vat.due_date) = trunc(acr_invoice_ln.min_due_date)
And vat.invoice_line_id > acr_invoice_ln.min_invoice_ln_id;
End If;
End Loop;
End;
End acr_invoice_pkg;
/