oracle完全支付,Oracle EBS AP 已经完全付款的发票仍然可以选择并进行零金额的付款...

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