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;
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:
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
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
Post a Comment