Introduction: AP-query to get List of Suppliers which have not made payments since 2 years in Oracle EBS either invoice or payment.
Query:
select distinct a.vendor_id,
b.ORG_ID,
a.segment1 SUPPLIER_NUMBER,
a.VENDOR_TYPE_LOOKUP_CODE SUPPLIER_TYPE,
a.VENDOR_NAME SUPPLIER_NAME,
a.VENDOR_NAME_ALT SUPPLIER_NAME_ALT,
a.num_1099 TAXPAYER_ID,
a.vat_registration_num TAX_REGISTRATION_NUM,
a.allow_awt_flag SUPPLIER_ALLOW_WITHHOLDING_TAX,
a.CREATION_DATE SUPPLIER_CREATION_DATE,
(select user_name from apps.fnd_user where user_id=a.CREATED_BY) SUPPLIER_CREATED_BY,
a.LAST_UPDATE_DATE SUPPLIER_LAST_UPDATE_DATE,
decode(a.END_DATE_ACTIVE,null,’ACTIVE’,’IN ACTIVE’) “Supplier active code “,
(select user_name from apps.fnd_user where user_id=a.LAST_UPDATED_BY) SUPPLIER_LAST_UPDATED_BY,
b.VENDOR_SITE_ID,
b.INACTIVE_DATE,
b.vendor_site_code SUPPLIER_SITE_CODE,
b.vendor_SITE_CODE_ALT SITE_CODE_ALT,
b.allow_awt_flag SITE_ALLOW_WITHHOLDING_TAX
,b.ADDRESS_LINE1
,b.ADDRESS_LINE2
,b.ADDRESS_LINE3
,b.ADDRESS_LINE4
,b.CITY
,b.STATE
,b.ZIP POST_CODE
,b.CREATION_DATE SUPPLIER_SITE_CREATION_DATE
,b.CREATED_BY SUPPLIER_SITE_CREATED_BY
,b.LAST_UPDATE_DATE SUPPLIER_SITE_LAST_UPDATE_DATE
,b.LAST_UPDATED_BY SUPPLIER_SITE_LAST_UPDATED_BY
,b.SUPPLIER_NOTIF_METHOD SITE_PO_NOTIF_METHOD
,b.EMAIL_ADDRESS SITE_PO_EMAIL
,a.PAY_GROUP_LOOKUP_CODE
from apps.ap_suppliers a,
apps.ap_supplier_sites_all b
where 1=1
and a.vendor_id=b.vendor_id
and b.inactive_date IS NULL
and a.end_date_active is null
and b.org_id=31834
and a.vendor_id not in (
select distinct vendor_id from apps.ap_checks_all where CHECK_DATE between ’01-MAY-18′ and ’31-MAY-21′–few scenarios should use the ap_payment_schedules_all table.
and vendor_id in
(select distinct a.vendor_id
from apps.ap_suppliers a,
apps.ap_supplier_sites_all b
where 1=1
and a.vendor_id=b.vendor_id
and b.inactive_date IS NULL
and a.end_date_active is null
and b.org_id=31834))
order by vendor_id
