To datafix AR DATE

http://www.cnblogs.com/benio/archive/2012/07/07/2580203.html

AR transactions should be created on 6-JUL-2010,in fact they were generated on 25-JUN-2010. Because the SHIP_DATE_ACTUAL is on 25-JUN-2010.

 

 

--------------------------------------------------------------------------------
--Original date: TRX_DATE=25-JUN-2010,GL_DATE=25-JUN-2010,DUE_DATE=25-JUL-2010
--------------------------------------------------------------------------------

SELECT * FROM AR.RA_CUSTOMER_TRX_ALL WHERE TRX_NUMBER IN ('905911','905912') AND SET_OF_BOOKS_ID=89
--TRX_DATE,SHIP_DATE_ACTUAL

SELECT * FROM AR.RA_CUSTOMER_TRX_LINES_ALL WHERE CUSTOMER_TRX_ID IN (9849489,9849490)

SELECT * FROM AR.RA_CUST_TRX_LINE_GL_DIST_ALL WHERE CUSTOMER_TRX_ID IN (9849489,9849490)

--GL_DATE

SELECT * FROM AR.AR_PAYMENT_SCHEDULES_ALL WHERE CUSTOMER_TRX_ID IN (9849489,9849490)
--DUE_DATE

 

Solution:

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

UPDATE AR.RA_CUSTOMER_TRX_ALL
   SET TRX_DATE = TRUNC(SYSDATE - 2)
 WHERE TRX_NUMBER IN ('905911','905912')

   AND SET_OF_BOOKS_ID = 89;

 

ALTER TRIGGER  ra_cust_trx_line_gl_dist_bri DISABLE;

UPDATE AR.RA_CUST_TRX_LINE_GL_DIST_ALL
   SET GL_DATE = TRUNC(SYSDATE - 2)
 WHERE CUSTOMER_TRX_ID IN (9849489,9849490);
 
ALTER TRIGGER  ra_cust_trx_line_gl_dist_bri ENABLE;

UPDATE AR.AR_PAYMENT_SCHEDULES_ALL
   SET TRX_DATE = TRUNC(SYSDATE - 2),
       GL_DATE  = TRUNC(SYSDATE - 2),
       DUE_DATE = TRUNC(SYSDATE + 28)
 WHERE CUSTOMER_TRX_ID IN (9849489,9849490);

 

 

 

新增收款

 

ar_cash_receipts_all  状态为UNAPP --表示未核销

 

ar_cash_receipt_history_all  状态为CLEARED, current_record_flag 为Y --表示结清, 

 

ar_payment_schedules_all  amount_due_original 为收款额的负数 --表示原始收款额

ar_payment_schedules_all  amount_due_remaining 为收款额的负数 --表示未核销额

 

 

ar_payment_schedules_all  状态为OP --表示为有效的计划

 

 

 

 

冲销

 

--停止付款

ar_cash_receipts_all 状态变为STOP

 

ar_cash_receipt_history_all 新增一条记录，状态为REVERSED， 并且current_record_flag 置为Y. 原来记录的current_record_flag 置为空

 

ar_payment_schedules_all 状态变为CL,并且amount_due_remaining 变为0

 

 

 

--冲销收款

ar_cash_receipts_all 状态变为REV

 

ar_cash_receipt_history_all 新增一条记录，状态为REVERSED， 并且current_record_flag 置为Y. 原来记录的current_record_flag 置为空

 

 

ar_payment_schedules_all 状态变为CL,并且amount_due_remaining 变为0

 

 

核销

 

--收款注销

ar_cash_receipts_all  无变化

ar_cash_receipt_history_all 无变化

 

ar_payment_schedules_all  amount_due_remaining 变为 原剩余金额 - 注销金额 的负数

 

ar_receivable_applications_v  核销视图中 增加一行trx_number 为收款注销的记录，amount_applied 为注销金额, 状态为OP.

 

--退款

ar_cash_receipts_all  无变化

ar_cash_receipt_history_all 无变化

ar_payment_schedules_all  amount_due_remaining 变为 原剩余金额 - 注销金额 的负数

 

ar_receivable_applications_v  核销视图中 增加一行trx_number 为退款的记录，amount_applied 为注销金额, 状态为OP.

 

 

--取销 注销或退款

ar_payment_schedules_all  的 amount_due_remaining 会还原

ar_receivable_applications_v 的原注销或退款记录会消失

 

如果注销或退款为原始收款额的话

 

ar_cash_receipts_all 状态为变为APP

 

ar_payment_schedules_all 状态变为CL,并且amount_due_remaining 变为0

 

 

--这个视图还是挺有用的，可直接取到收款的各项信息

AR_CASH_RECEIPTS_V

 

 

吐糟一下，R12 的收款 BUG实在太多了...