SELECT dv.vendor_id C_VENDOR_ID,
dv.vendor_site_id C_OLD_VENDOR_SITE_ID,
duplicate_vendor_id C_DUP_VENDOR_ID,
duplicate_vendor_site_id C_DUP_VENDOR_SITE_ID,
entry_id C_ENTRY_ID,
dv.org_id C_ORG_ID,
a.vendor_name C_VENDOR_NAME,
b.vendor_name C_DUP_VENDOR_NAME,
c.vendor_site_code C_VENDOR_SITE_CODE,
d.vendor_site_code C_DUP_VENDOR_SITE_CODE,
dv.keep_site_flag C_KEEP_SITE_FLAG,
dv.paid_invoices_flag C_PAID_INVOICES_FLAG,
a.segment1 C_NEW_VENDOR_NUMBER,
b.segment1 C_OLD_VENDOR_NUMBER,
a.party_id c_party_id,
dv.keep_site_flag C_KEEP_SITE_FLAG,
d.party_site_id C_DUP_PARTY_SITE_ID,
c.party_site_id C_PARTY_SITE_ID,
AP_APXINUPD_XMLP_PKG.c_operating_unitformula(dv.org_id) C_OPERATING_UNIT,
AP_APXINUPD_XMLP_PKG.c_vendor_site_idformula(dv.org_id, dv.keep_site_flag, dv.vendor_id, c.vendor_site_code, dv.vendor_site_id) C_VENDOR_SITE_ID,
--AP_APXINUPD_XMLP_PKG.c_dup_recurring_countformula(duplicate_vendor_id, duplicate_vendor_site_id, dv.vendor_id, :C_VENDOR_SITE_ID) C_DUP_RECURRING_COUNT,
AP_APXINUPD_XMLP_PKG.c_dup_recurring_countformula(duplicate_vendor_id, duplicate_vendor_site_id, dv.vendor_id, AP_APXINUPD_XMLP_PKG.c_vendor_site_idformula(dv.org_id, dv.keep_site_flag, dv.vendor_id, c.vendor_site_code, dv.vendor_site_id)) C_DUP_RECURRING_COUNT,
AP_APXINUPD_XMLP_PKG.c_recurring_countformula(dv.vendor_id, AP_APXINUPD_XMLP_PKG.c_vendor_site_idformula(dv.org_id, dv.keep_site_flag, dv.vendor_id, c.vendor_site_code, dv.vendor_site_id), duplicate_vendor_id, duplicate_vendor_site_id) C_RECURRING_COUNT,
AP_APXINUPD_XMLP_PKG.c_update_dup_vendorformula(:C_DUP_INVOICE_COUNT, AP_APXINUPD_XMLP_PKG.c_dup_recurring_countformula(duplicate_vendor_id, duplicate_vendor_site_id, dv.vendor_id, AP_APXINUPD_XMLP_PKG.c_vendor_site_idformula(dv.org_id, dv.keep_site_flag, dv.vendor_id, c.vendor_site_code, dv.vendor_site_id)), dv.keep_site_flag, AP_APXINUPD_XMLP_PKG.c_vendor_site_idformula(dv.org_id, dv.keep_site_flag, dv.vendor_id, c.vendor_site_code, dv.vendor_site_id), entry_id, :C_PAID_INVOICE_COUNT_RESET, :C_UNPAID_INVOICE_COUNT_RESET, duplicate_vendor_id, duplicate_vendor_site_id, dv.vendor_id, c.party_site_id, d.party_site_id) C_UPDATE_DUP_VENDOR,
AP_APXINUPD_XMLP_PKG.c_invoice_countformula(:C_DUP_INVOICE_COUNT, :C_NON_DUP_INVOICE_COUNT, :C_DUP_RECURRING_COUNT, :C_RECURRING_COUNT) C_INVOICE_COUNT
FROM ap_duplicate_vendors_all dv,
ap_suppliers a,
ap_suppliers b,
ap_supplier_sites_all c,
ap_supplier_sites_all d
WHERE dv.process_flag='S'
AND a.vendor_id=dv.vendor_id
AND c.vendor_site_id=nvl(dv.vendor_site_id,duplicate_vendor_site_id)
AND b.vendor_id=dv.duplicate_vendor_id
AND d.vendor_site_id=dv.duplicate_vendor_site_id
AND d.org_id =dv.org_id
AND dv.process<>'P' |
