应收票据\收款的追溯研究

hzsumking

应收模块总结：
主要类型:1.
'Sales Invoices' [销售发票]
, 'Credit Memos' [贷项通知单]
, 'Debit Memos' [借项通知单]
, 'Chargebacks' [拖欠款项]
references字段对应规则：gl_import_references
/*
reference_1：ar_posting_control.posting_control_id
reference_2：ra_customer_trx_all.customer_trx_id
reference_3：ra_cust_trx_line_gl_dist_all.cust_trx_line_gl_dist_id
reference_4：ra_customer_trx_all.trx_number
reference_5： ra_hcustomers.CUSTOMER_NUMBER
reference_6：'CUSTOMER'
reference_7：ra_customer_trx_all.bill_to_customer_id
reference_8：CM
reference_9：CM_REC
reference_10：RA_CUST_TRX_LINE_GL_DIST
*/
举例1：
--'Credit Memos' [贷项通知单]
--凭证头
select * from gl_je_headers gjh
where gjh.je_category='Credit Memos'
and gjh.je_source='Receivables'
and gjh.je_header_id=172348
;
--批
select * from gl_je_batches gjb where gjb.je_batch_id=276278
;
--凭证行 3行
select * from gl_je_lines gjl
where gjl.je_header_id=172348
;
--导入总帐过程记录表 4行
select * from gl_import_references gir
where gir.je_header_id=172348
;
--应收票据分配
select * from ra_cust_trx_line_gl_dist_all rctlg
where rctlg.cust_trx_line_gl_dist_id in (89394,89395,89396)
;
--应收票据头
select * from ra_customer_trx_all rct
where rct.customer_trx_id=15121
;
--应收票据行
select * from ra_customer_trx_lines_all rct
where rct.customer_trx_id=15121
;
--客户
select * from ra_customers rc
where rc.customer_id=1071
;
--导入控制项
select * from ar_posting_control apc
where apc.posting_control_id=97014
;
-----------------------------------------------------
举例2：
--'Sales Invoices' [销售发票]
--凭证头
select * from gl_je_headers gjh
where gjh.je_category='Sales Invoices'
and gjh.je_source='Receivables'
and gjh.je_header_id=172192
;
--批
select * from gl_je_batches gjb where gjb.je_batch_id=276180;
--凭证行 2行
select * from gl_je_lines gjl where gjl.je_header_id=172192;
--导入总帐过程记录表 2行
select * from gl_import_references gir
where gir.je_header_id=172192
;
--应收票据
select * from ra_cust_trx_line_gl_dist_all rctlg
where rctlg.cust_trx_line_gl_dist_id in (90951,90952)
;
--应收票据头
select * from ra_customer_trx_all rct
where rct.customer_trx_id=15174
;
--应收票据行
select * from ra_customer_trx_lines_all rct
where rct.customer_trx_id=15174
;
--客户
select * from ra_customers rc
where rc.customer_id=1090
;
--导入控制项
select * from ar_posting_control apc
where apc.posting_control_id=96019
;
------------------------------------------------------------------------------------------

主要类型:2.
'Credit Memo Applications' [贷项通知单核销]

references字段对应规则：gl_import_references
/*
reference_1：ar_posting_control.posting_control_id
reference_2：AR_RECEIVABLE_APPLICATIONS_ALL.RECEIVABLE_APPLICATION_ID
reference_3：ar_distributions_all.line_id
reference_4：ra_customer_trx_all.trx_number 对应调整的
reference_5：ra_customer_trx_all.trx_number
reference_6：'INV'
reference_7：ra_customer_trx_all.bill_to_customer_id
reference_8：CMAPP
reference_9：CMAPP_REC
reference_10：AR_RECEIVABLE_APPLICATIONS
*/
举例如下：
--'Credit Memo Applications' [贷项通知单核销]
--凭证头
select * from gl_je_headers gjh
where gjh.je_category='Credit Memo Applications'
and gjh.je_source='Receivables'
and gjh.je_header_id=172740
;
--批
select * from gl_je_batches gjb where gjb.je_batch_id=276392
;

--凭证行 2行
select * from gl_je_lines gjl
where gjl.je_header_id=172740
;
--导入总帐过程记录表  4行
select * from gl_import_references gir
where gir.je_header_id=172740
;
--分配 source_id=193552,193553
select * from ar_distributions_all ad
where ad.line_id in (268278,268280,268277,268279);
--核销 payment_schedule_id,customer_trx_id in (15149,15156)
--applied_customer_trx_id in (15003,15004)
--
SELECT ara.*
  FROM AR_RECEIVABLE_APPLICATIONS_ALL ARA
WHERE ARA.RECEIVABLE_APPLICATION_ID in (193552,193553)
;

--应收票据: 2221qj060504001DJ,2221qj060504002DJ
select * from ra_customer_trx_all rct
where rct.customer_trx_id in (15149,15156)
;
--应收票据: 2221qj060504001,2221qj060504002
select * from ra_customer_trx_all rct
where rct.customer_trx_id in (15003,15004)
;
--应收票据行
select * from ra_customer_trx_lines_all rctl
where rctl.customer_trx_id in (15149,15156)
;
--营业款结算-其他－财务费用－银行手续费
select * from ra_customer_trx_lines_all rctl
where rctl.customer_trx_id in (15003,15004)
;
--分配
select * from ra_cust_trx_line_gl_dist_all rctlg
where rctlg.customer_trx_line_id in (
        select rctl.customer_trx_line_id
        from ra_customer_trx_lines_all rctl
        where rctl.customer_trx_id in (15149,15156)
        )
;
--分配2
select * from ra_cust_trx_line_gl_dist_all rctlg
where rctlg.customer_trx_line_id in (
         select rctl.customer_trx_line_id
         from ra_customer_trx_lines_all rctl
         where rctl.customer_trx_id in (15003,15004)
         )
;

--付款计划（调整单) 对应 customer_trx_id in (15149,15156)
select * from ar_payment_schedules_all aps
where aps.payment_schedule_id in (78176,78177)
;

--付款计划 对应customer_trx_id in (15003,15004)
select * from ar_payment_schedules_all aps
where aps.payment_schedule_id in (76941,76942)
;

--客户
select * from ra_customers rc
where rc.customer_id=1047
;

--导入控制项
select * from ar_posting_control apc
where apc.posting_control_id=97026
;

主要类型:3.
'Misc Receipts' [杂项收款]
references字段对应规则：gl_import_references
/*
reference_1：ar_posting_control.posting_control_id
reference_2：ar_cash_receipts_all.cash_receipt_id
reference_3：ar_distributions_all.line_id
reference_4：ar_cash_receipts_all.cash_receipt_number
reference_5：当 reference_9='MISC_CASH'  或 reference_10='AR_CASH_RECEIPT_HISTORY'时，
             关联  ar_cash_receipt_history_all.cash_receipt_history_id
             当 reference_9='MISC_CASH' 或 reference_10='AR_MISC_CASH_DISTRIBUTIONS'时，
             关联  ar_misc_cash_distributions_all.misc_cash_distribution_id
reference_6：
reference_7：
reference_8：MISC
reference_9：MISC_CASH 或  MISC_MISCCASH
reference_10：AR_CASH_RECEIPT_HISTORY 或 AR_MISC_CASH_DISTRIBUTIONS
*/
举例如下：
--'Misc Receipts' [杂项收款]

--凭证头
select * from gl_je_headers gjh
where gjh.je_category='Misc Receipts'
and gjh.je_source='Receivables'
and gjh.je_header_id=172736
;
--凭证批
select * from gl_je_batches gjb where gjb.je_batch_id=276390;
--凭证行 4行
select * from gl_je_lines gjl
where gjl.je_header_id=172736
;
--导入总帐过程记录表 4行
select * from gl_import_references gir
where gir.je_header_id=172736
;
--分配表  
/*注意:
当source_Table为CRH 或 source_type 为 CASH 时，source_id关联表 ar_cash_receipt_history_all.cash_receipt_history_id
当source_table为MCD 或 source_type 为 MISCCASH 时，source_id关联表 ar_misc_cash_distributions_all.misc_cash_distribution_id
*/
select * from ar_distributions_all ad
where ad.line_id in (268274,268272,268276,268273)
and ad.source_table='CRH'
;

--取得收款id,注意这里有两部分组成
select  acrh.cash_receipt_id
from ar_cash_receipt_history_all acrh
where acrh.cash_receipt_history_id in (
                      select ad.source_id from ar_distributions_all ad
                      where ad.line_id in (268274,268272,268276,268273)
                      and ad.source_table='CRH'
                      )
union
select amcd.cash_receipt_id
from ar_misc_cash_distributions_all amcd
where amcd.misc_cash_distribution_id in (
                       select ad.source_id from ar_distributions_all ad
                       where ad.line_id in (268274,268272,268276,268273)
                       and ad.source_table='MCD'
                       )
;
--再根据上面的cash_receipt_id 得到杂项收款明细
select * from ar_cash_receipts_all acr
where acr.type='MISC' and
acr.cash_receipt_id in (75317,61828)
;
--------------------------------
--导入控制表
select * from ar_posting_control apc
where apc.posting_control_id=97025
;

------------------------------------------------------------------------

其它：
'Receipts'       [收款]
'Trade Receipts' [商业收据]
'Bank Receipts'  [银行收款]
'Adjustment' [应收发票调整]

但愿对新人有一定帮助！

hzsumking

自己先坐沙发，顶一下