Oracle zdlar,Oracle 让我爱你不容易,AR收款问题

不明白为什么不公开，那我贴2个SQL吧，当时顾问给的，看是否适用：

1.列出所有的客户编号不正确的收款单：

SELECT cr.receipt_number,cr.cash_receipt_id,

cr.pay_from_customer, cr.customer_site_use_id,

ard.third_party_id,ard.third_party_sub_id

FROM ar_cash_receipts_all cr,ar_cash_receipt_history_all crh,

ar_distributions_all ard

WHERE cr.cash_receipt_id = crh.cash_receipt_id

AND crh.cash_receipt_history_id = ard.source_id

AND cr.pay_from_customer != ard.third_party_id

AND cr.customer_site_use_id != ard.third_party_sub_id

AND ard.source_table ='CRH';

SELECT cr.receipt_number,cr.cash_receipt_id,

cr.pay_from_customer, cr.customer_site_use_id,

ard.third_party_id,ard.third_party_sub_id

FROM ar_cash_receipts_all cr,ar_receivable_applications_all ra,

ar_distributions_all ard

WHERE cr.cash_receipt_id = ra.cash_receipt_id

AND ra.receivable_application_id = ard.source_id

AND cr.pay_from_customer != ard.third_party_id

AND cr.customer_site_use_id != ard.third_party_sub_id

AND ard.source_table = 'RA'

AND ard.third_party_id IS NOT NULL

AND ard.third_party_sub_id IS NOT NULL;

2.据修复脚本：

EXEC fnd_client_info.set_org_context(&org_id);

DECLARE

CURSOR correct_ra IS

SELECT ard.line_id line_id,ard.source_id ra_id

FROM ar_cash_receipts cr,ar_receivable_applications ra,

ar_distributions ard

WHERE cr.cash_receipt_id = ra.cash_receipt_id

AND ra.receivable_application_id = ard.source_id

AND cr.pay_from_customer != ard.third_party_id

AND cr.customer_site_use_id != ard.third_party_sub_id

AND ard.source_table = 'RA'

AND ard.third_party_id IS NOT NULL

AND ard.third_party_sub_id IS NOT NULL;

CURSOR correct_crh IS

SELECT ard.line_id line_id,ard.source_id crh_id

FROM ar_cash_receipts cr,ar_cash_receipt_history crh,

ar_distributions ard

WHERE cr.cash_receipt_id = crh.cash_receipt_id

AND crh.cash_receipt_history_id = ard.source_id

AND cr.pay_from_customer != ard.third_party_id

AND cr.customer_site_use_id != ard.third_party_sub_id

AND ard.source_table = 'CRH';

l_customer_id number;

l_site_use_id number;

BEGIN

FOR ra_records IN correct_ra LOOP

BEGIN

SELECT pay_from_customer, customer_site_use_id

INTO l_customer_id, l_site_use_id

FROM ar_cash_receipts cr, ar_receivable_applications ra

WHERE ra.receivable_application_id = ra_records.ra_id

AND cr.cash_receipt_id = ra.cash_receipt_id;

UPDATE ar_distributions

SET third_party_id = l_customer_id,

third_party_sub_id = l_site_use_id

WHERE line_id = ra_records.line_id;

EXCEPTION WHEN NO_DATA_FOUND THEN

NULL;

END;

END LOOP;

FOR crh_records IN correct_crh LOOP

BEGIN

SELECT pay_from_customer, customer_site_use_id

INTO l_customer_id, l_site_use_id

FROM ar_cash_receipts cr, ar_cash_receipt_history crh

WHERE crh.cash_receipt_history_id = crh_records.crh_id

AND cr.cash_receipt_id = crh.cash_receipt_id;

UPDATE ar_distributions

SET third_party_id = l_customer_id,

third_party_sub_id = l_site_use_id

WHERE line_id = crh_records.line_id;

EXCEPTION WHEN NO_DATA_FOUND THEN

NULL;

END;

END LOOP;

END;

/

COMMIT;

不过为了保险，最好提个TAR问问。