Tuesday, January 22, 2013

Query to check Duplicates in iby_external_payees_all table

SELECT
 A.EXT_PAYEE_ID ,
A.PAYEE_PARTY_ID ,
A.PAYMENT_FUNCTION ,
A.EXCLUSIVE_PAYMENT_FLAG ,
 A.PARTY_SITE_ID ,
 A.SUPPLIER_SITE_ID ,
 A.ORG_ID ,
 A.ORG_TYPE ,
 A.DEFAULT_PAYMENT_METHOD_CODE ,
 A.ECE_TP_LOCATION_CODE ,
 A.BANK_CHARGE_BEARER ,
 A.BANK_INSTRUCTION1_CODE ,
 A.BANK_INSTRUCTION2_CODE ,
 A.BANK_INSTRUCTION_DETAILS ,
 A.PAYMENT_REASON_CODE ,
 A.PAYMENT_REASON_COMMENTS ,
 A.INACTIVE_DATE ,
 A.PAYMENT_TEXT_MESSAGE1 ,
 A.PAYMENT_TEXT_MESSAGE2 ,
 A.PAYMENT_TEXT_MESSAGE3 ,
 A.DELIVERY_CHANNEL_CODE ,
 A.PAYMENT_FORMAT_CODE ,
 A.SETTLEMENT_PRIORITY ,
 A.REMIT_ADVICE_DELIVERY_METHOD ,
 A.REMIT_ADVICE_EMAIL ,
 A.REMIT_ADVICE_FAX
FROM iby_external_payees_all a
 WHERE EXISTS (SELECT 'duplicates'
FROM iby_external_payees_all b
WHERE a.payee_party_id = b.payee_party_id
AND a.payment_function = b.payment_function
AND NVL(a.party_site_id, '0') = NVL(b.party_site_id, '0')
AND NVL(a.supplier_site_id, '0') = NVL(b.supplier_site_id, '0')
AND NVL(a.org_id, '0') = NVL(b.org_id, '0')
AND NVL(a.org_type, '0') = NVL(b.org_type, '0')
AND a.ext_payee_id <> b.ext_payee_id
 )
 ORDER BY a.PAYEE_PARTY_ID, a.last_update_date DESC;

2 comments:

JJ said...

How do we prevent duplicates in iby_external_payees_all?

And more importantly how do we fix it when we do get duplicates?

Thanks,
-JJ

Unknown said...


I simply want to say I am new to weblog and truly savored you’re website. More than likely I’m want to bookmark your blog . You actually have excellent writings. Thanks a bunch for sharing your web site.

Jorcel
www.imarksweb.org