已经完全付款的发票仍然可以选择并进行零金额的付款

背景:

 

 最近用户反映,一些已经完全付款的发票仍然可以选择并进行零金额的付款,目前我们没有开启这样的功能(enable the Allow Zero Payments option in the Payables Options region of the Bank Accounts window.)

 

原因:

 

   个人认为可能是bug, 导致没有同步更新flag

 

措施:

 

1>找出相应的发票;

SELECT DISTINCT ai.invoice_id, ai.invoice_num invoice_num,

                pv.segment1 vendor_num, pv.vendor_name vendor_name,

                'The fully Paid invoice should be closed!' status

           FROM ap.ap_invoices_all ai,

                        po.po_vendors pv,

                       ap.ap_payment_schedules_all aps

          WHERE ai.invoice_id = aps.invoice_id

        AND ai.set_of_books_id = &sob

                 AND ai.invoice_amount = ai.amount_paid           

                 AND ai.cancelled_date IS NULL

                 AND ai.vendor_id = pv.vendor_id

         AND (   ai.payment_status_flag <> 'Y'

                 OR aps.payment_status_flag <> 'Y'

                )

2>更新相应flag以符合实际业务;

  update ap_invoices_all

     set payment_status_flag='Y'

   where invoice_id=&invoice_id;

 

   update ap_payment_schedules_all

        set payment_status_flag='Y'

    where invoice_id=&invoice_id;

 

结论: 测试发现

 

    1>两表中的payment_status_flag任一一个为Y,另一个取值N或P,在付款工作台会选择不到发票;

    2>两表中的payment_status_flag均不为Y,两者不管取N或P,在付款工作台上都能选择到发票.

   

 

Metalink反馈[SR:7571908.994]

 

CAUSE DETERMINATION
====================
The cause of the issue is invalid / incorrect data in AP_PAYMENT_SCHEDULES_ALL and AP_Invoices_All tables
Extra row(s) with AMOUNT_REMAINING, BATCH_ID, DUE_DATE ,GROSS_AMOUNT fields should not be null in

AP_PAYMENT_SCHEDULES_ALL table PAYMENT_STATUS_ FLAG should not be P in AP_Invoices_All table

 

SOLUTION / ACTION PLAN
======================

To implement the solution, please execute the following steps:

1. Ensure that you have taken a backup of your system before applying the recommended s
olution.

2. Run the following scripts in a TEST environment first:

Create table TAR_7571908_994_APSA as
Select * from ap_payment_schedules_all
Where amount_remaining is null and
batch_id is null and
due_date is null and
gross_amount is null and
payment_status_flag='P';

Create table TAR_7571908_994_AIA as
Select * from AP_Invoices_All
Where INVOICE_ID in (485237, 492065) and
PAYMENT_STATUS_FLAG = 'P';

Delete from ap_payment_schedules_all
Where amount_remaining is null and
batch_id is null and
due_date is null and
gross_amount is null and
payment_status_flag='P';

--This should delete all extra row(s) rows with null values fields in ap_payment_schedules_all

Update AP_Invoices_All
Set PAYMENT_STATUS_FLAG = 'Y'
Where INVOICE_ID in (485237, 492065);

--This should update PAYMENT_STATUS_FLAG field in the invoices specified