那年,因为财务每日的收款数据越来越大,加密箱的处理速度越来越慢,领导下死命令,必须独立开发一个类似的功能,实现收款的快速导入和核销!当时还是小白的我颤抖着接下了任务。
经过不断测试,先搞清楚了收款创建涉及的表:
ar_cash_receipts_all:收款明细表
ar_cash_receipt_history_all:写入
ar_payment_schedules_all:写入
ar_receivable_applications_all:写入
ar_distributions_all:写入
ar_distributions_all:写入
xla_transaction_entities:写入
xla_events :写入
收款核销相关的表:
ar_receivable_applications_all: 写入两次
ar_distributions_all:写入和更新
ra_customer_trx_all:更新
ar_payment_schedules_all:更新
ar_cash_receipts_all : 更新
先介绍收款创建的实现逻辑:
1:先向接口准备数据,接口结构如下,
组织ID(org_id)
收款编号(receipt_number)
gl日期(gl_date)
客户编号(cusotmer_number)
收款金额(rec_amount)
币种(currency_code)
收款方法(rec_method)
处理状态(process_status)
处理信息(process_message);
....
收款 导入的实现逻辑如下:
2:接口数据校验(检验接口的关键字段不能为空切符合要求)
1.1:通过表【gl_period_statuses】检查GL日期所在期间是否开启
1.2:检查收款金额是否小于0;
1.3:通过表【hr_operating_units】检查ORG_ID是否有效;
1.4:通过表【hz_cust_accounts】检查客户编号是否有效
1.5:检查该收款是否重复导入;
3:表【ar_cash_receipts_all】数据写入逻辑
cash_receipt_id, -- 取自 ar_cash_receipts_s.nextval
amount, -- 取值 接口的收款金额字段
set_of_books_id, -- 通过org_id 关联 ,取值 hr_operating_units.set_of_books_id
currency_code, -- 取值接口的币种字段
pay_from_customer, -- 通过客户编号关联,取值hz_cust_accounts 表的cust_account_id字段;具体逻辑再下面
status, --固定值:UNAPP
TYPE, -- 固定值:CASH
receipt_number, -- 取自 接口表的收款编号字段
receipt_date, -- 取值接口表的GL日期字段
confirmed_flag, -- 固定值:Y
customer_site_use_id, -- 通过客户表和组织关联,取自hz_cust_site_uses_all表的site_use_id字段;具体方法再下面
deposit_date, -- 取值接口表的GL日期字段
receipt_method_id, --取自ar_receipt_methods表的receipt_method_id字段,具体逻辑在下面
factor_discount_amount, -- 固定值:0
override_remit_account_flag, -- 固定值:N
org_id, -- 接口表的组织ID
unique_reference, -- 固定值:-1
rec_version_number, -- 固定值:1
remit_bank_acct_use_id, -- 取自表ar_receipt_method_accounts_all的remit_bank_acct_use_id字段,具体逻辑如下
legal_entity_id, -- 取自表hr_operating_units的default_legal_context_id字段
autoapply_flag, -- 固定值:N
-- 收款客户相关字段的获取逻辑
select c.cust_account_id, -- 收款客户ID
ua.site_use_id -- 获得地点ID
into l_cust_account_id, l_site_user_id
from hz_cust_accounts c,
hz_customer_profiles cp,
hz_parties party,
hz_cust_acct_sites_all sa,
hz_cust_site_uses_all ua
where sa.cust_acct_site_id = ua.cust_acct_site_id
and sa.status = ua.status
and sa.status = 'A' --已启用
and ua.site_use_code = 'BILL_TO'
and sa.cust_account_id = c.cust_account_id
and c.cust_account_id = cp.cust_account_id(+)
and cp.site_use_id is null
and sa.bill_to_flag = 'P'
and ua.primary_flag = 'Y'
and c.party_id = party.party_id
and ua.org_id = l_org_id
and c.account_number = l_account_number;
收款客户及相关会计的获取方法:
select m.receipt_method_id,
a.remit_bank_acct_use_id,
cba.account_owner_org_id,
a.remittance_ccid,
a.unapplied_ccid
into l_receipt_method_id,
l_remit_bank_acct_use_id,
l_account_owner_org_id,
l_remittance_ccid,
l_unapplied_ccid
from ar_receipt_method_accounts_all a,
ar_receivables_trx_all t,
ar_receipt_methods m,
ce_bank_accounts cba,
ce_bank_acct_uses_all cbau
where cba.bank_account_id = cbau.bank_account_id
and cba.currency_code = 'CNY'
and cbau.bank_acct_use_id = a.remit_bank_acct_use_id
and t.receivables_trx_id = a.edisc_receivables_trx_id
and a.receipt_method_id = m.receipt_method_id
and sysdate between a.start_date and nvl(a.end_date, sysdate)
and m.name = v_receipt_method
and t.org_id = l_org_id;
4:表【ar_cash_receipt_history_all】数据写入逻辑
cash_receipt_history_id, -- 取自 ar_cash_receipt_history_s.nextval
cash_receipt_id, -- 取自 ar_cash_recepts_all.cash_receipt_id
status, -- 'REMITTED'
trx_date, -- 接口的GL_DATE
amount, -- 接口的AMOUNT
first_posted_record_flag, -- 'Y'
postable_flag, -- 'Y'
factor_flag, -- 'N'
gl_date, -- 接口的GL_DATE
current_record_flag, -- 'Y'
account_code_combination_id, -- ar_receipt_method_accounts_all.remittance_ccid(具体逻辑如如下所示)
posting_control_id, -- '-3' (未过账的默认值)
last_update_login, -- '-1',
acctd_amount, -- 本币金额,如果当前币种不是本币,需要通过汇率与收款金额换算成本币金额
acctd_factor_discount_amount, -- '0'
created_from, -- 'ARRERGW',
org_id, -- ar_cash_receipts_all.org_id
event_id, -- xla_events_s.nextval
---- 获得账套账户ID,获得账户ID 逻辑
select m.receipt_method_id,
a.remit_bank_acct_use_id,
cba.account_owner_org_id,
a.remittance_ccid,
a.unapplied_ccid
into l_receipt_method_id,
l_remit_bank_acct_use_id,
l_account_owner_org_id,
l_remittance_ccid,
l_unapplied_ccid
from ar_receipt_method_accounts_all a,
ar_receivables_trx_all t,
ar_receipt_methods m,
ce_bank_accounts cba,
ce_bank_acct_uses_all cbau
where cba.bank_account_id = cbau.bank_account_id
and cba.currency_code = 'CNY'
and cbau.bank_acct_use_id = a.remit_bank_acct_use_id
and t.receivables_trx_id = a.edisc_receivables_trx_id
and a.receipt_method_id = m.receipt_method_id
and sysdate between a.start_date and nvl(a.end_date, sysdate)
and m.name = l_receipt_method (变量)
and t.org_id = l_org_id;(变量)
5:表【xla_events】数据写入逻辑
event_id, -- xla_events_s.nextval
application_id, -- 222(ar) /200(ap) ...
event_type_code, -- RECP_CREATE (收款创建代码,此外核销,冲销都有不同的代码)
event_date, -- 接口的GL日期
entity_id, -- xla_transaction_entities_s.nextval
event_status_code, -- 'U'(待生成科目、已生成科目:P、生成科目失败:I)
process_status_code, -- 'U'(待生成科目、已生成科目:P、生成科目失败:I)
event_number, -- 1( 初始值)
on_hold_flag, -- 'N'
transaction_date, -- GL日期
budgetary_control_flag, -- 'N'
6:表【ar_payment_schedules_all】数据写入逻辑
payment_schedule_id, -- ar_payment_schedules_s.nextval
due_date, -- 收款到期日,计算逻辑如下
amount_due_original, -- 接口的收款金额
amount_due_remaining, -- 接口的收款金额取反
number_of_due_dates, -- ‘1’ 默认值
status, -- 'OP'(初始值,如果全额核销更新CL)
invoice_currency_code,-- 接口的币种
CLASS, -- 'PMT'
customer_id, -- 客户ID(逻辑如上)
customer_site_use_id, -- 客户地点ID(逻辑如上)
cash_receipt_id, -- ar_cash_receipts_all.cash_receipt_id
associated_cash_receipt_id, -- ar_cash_receipts_all.cash_receipt_id
gl_date_closed, -- 接口GL日期的最后一天
actual_date_closed, -- 接口GL日期的最后一天
trx_number, -- 收款编号
trx_date, -- 接口的GL日期
gl_date, -- 接口GL日期
acctd_amount_due_remaining, -- 接口收款金额取反
org_id, -- ar_cash_receipts_all.org_id
收款到账日期的计算逻辑:
SELECT to_date(l_gl_date, 'YYYY-MM-DD') + rtl.due_days
INTO l_due_days
FROM hz_customer_profiles hcp
JOIN ra_terms_lines rtl
ON hcp.standard_terms = rtl.term_id
JOIN hz_cust_accounts hca
ON hca.cust_account_id = hcp.cust_account_id
WHERE hcp.status = 'A'
AND hcp.site_use_id IS NULL
AND hca.account_number = to_char(l_account_number); --'1032'
7:表【ar_receivable_applications_all】数据写入逻辑
receivable_application_id, -- ar_receivable_applications_s.nextval
amount_applied, -- 接口收款金额
gl_date, -- 接口GL日期
code_combination_id, -- 如上 l_unapplied_ccid 的取值逻辑
set_of_books_id, -- ar_cash_receipts_all.set_of_books_id
display, -- 'N'(未核销或取消核销,如果核销记录Y)
apply_date, -- 接口GL日期
application_type, -- 'CASH'(初始值)
status, -- 'UNAPP'(初始值)
payment_schedule_id, -- ar_payment_schedules_all.payment_schedule_id
cash_receipt_id, -- ar_cash_receipts_all.cash_receipt_id
application_rule, -- '60.2'
posting_control_id, -- '-3'
acctd_amount_applied_from, -- 接口收款金额
cash_receipt_history_id, -- ar_cash_receipt_history_all.cash_receipt_history_id
org_id, -- ar_cash_receipts_all.org_id
event_id, -- xla_events.event_id
upgrade_method -- 'R12'
8:表【ar_distributions_all】数据写入逻辑 (此表需要写入两条记录)
记录1:
line_id, -- ar_distributions_s.nextval
source_id, -- ar_cash_receipt_history_all.cash_receipt_history_id
source_table, -- 'CRH',
source_type, -- 'REMITTANCE',
code_combination_id, -- 如上 l_remittance_ccid 的取值逻辑
amount_dr, -- 接口收款金额
acctd_amount_dr, -- 接口收款本币金额
org_id, --
currency_code, --
third_party_id, -- 如上 l_cust_account_id 的取值逻辑
third_party_sub_id, -- 如上 l_site_user_id 的取值逻辑
记录2:
line_id, -- ar_distributions_s.nextval
source_id, -- ar_receivable_applications_all.receivable_application_id
source_table, -- 'RA'
source_type, -- 'UNAPP',
code_combination_id, -- 如上 l_unapplied_ccid 的取值逻辑
amount_cr, -- 接口收款金额
acctd_amount_cr -- 接口收款本币金额
org_id, -- 组织ID
currency_code, -- 币种
third_party_id, -- 如上 l_cust_account_id 的取值逻辑
third_party_sub_id, -- 如上 l_site_user_id 的取值逻辑
9:表【xla_transaction_entites】数据写入逻辑 (此表需要写入两条记录)
entity_id, -- xla_transaction_entities_s.nextval
application_id, -- 222
legal_entity_id, -- 如上 l_account_owner_org_id 的取值逻辑
entity_code, -- ‘RECEIPTS’
source_id_int_1, -- ar_cash_receipts_all.cash_receipt_id
security_id_int_1, -- l_org_id
transaction_number, -- l_receipt_number
ledger_id, -- sets_of_books_is
source_application_id -- 222
如果收款存在可核销对象,此时需要做核销处理:
ar_receivable_applications_all:写入2条记录
ar_distributions_all : 写入多条记录和更新
先介绍一下核销实现逻辑:
EBS的收款与订单核销,是个非常严谨的逻辑;
如果一个收款与一个事务处理等额未核销;要用收款与这个事务处理的所有不含税行明细和税明细来生成具体的核销记录;所以在余额里面,有事务处理核销余额,不含税行明细核销余额,税核销余额;
核销的有限逻辑:
1:最高优先级 - 先与事务处理的的税行明细余额核销;
2:次等优先级 - 与事务处理的不含税行明细金额核销(所有税额行已全部核销完毕),