Using the below query we can track the DML operations made on Supplier’s.It is useful script for Auditing and Tracking purposes.
select DISTINCT
a.segment1 vendor_number,A.VENDOR_NAME,c.name org_name,a.creation_date supplier_creation_date,
(select user_name from apps.fnd_user where user_id=a.CREATED_BY)created_by,
(select user_name from apps.fnd_user where user_id=a.LAST_UPDATED_BY)updated_by,a.last_update_date supplier_updated_date,
a.end_date_active,
CASE
WHEN trunc(a.creation_date)>=’01-JAN-20′ THEN ‘Creation’
WHEN (trunc(a.LAST_UPDATE_DATE)>=’01-JAN-20’and a.end_date_active is not null) THEN ‘Deletion’
WHEN trunc(a.LAST_UPDATE_DATE)>=’01-JAN-20′ then ‘Updated’
— WHEN end_date_active is not null THEN ‘Detletd’
ELSE ‘The owner is another value’
END request_type
from apps.ap_suppliers a ,
apps.ap_supplier_sites_all b,apps.hr_operating_units c
where
a.vendor_id=b.vendor_id
and B.ORG_ID=c.organization_id
AND b.org_id=3931
–a.segment1 in (25880,29970,28129)
AND (TRUNC(A.LAST_UPDATE_DATE)>=’01-JAN-20′ or TRUNC(A.creation_DATE)>=’01-JAN-20′)
order by a.segment1
