Follow us: Connect on YouTube Connect on YouTube Connect on YouTube

Pages

Thursday, 21 February 2019

Supplier Site Inactive Date Audit Query




By the following query you can find initial supplier site inactive date and new supplier site inactive date 
SELECT vendor_site_id,
       AUDIT_TIMESTAMP,
       Modified_Field_name,
       A.INACTIVE_DATE Initial_Value,
       DECODE (
          PRXL_AP_SUPPLIER_AUDIT_PKG.
           PRXL_AP_CHECK_NULL_CASE_FUC (A.vendor_site_id,
                                        A.AUDIT_TIMESTAMP,
                                        'INACTIVE_DATE'),
          'Y', NULL,
          NVL (
             LAG (
                A.INACTIVE_DATE,
                1)
             OVER (PARTITION BY A.vendor_site_id
                   ORDER BY A.AUDIT_TIMESTAMP DESC),
             A.inact_date))
          New_value,
       a.AUDIT_USER_NAME Modified_by
  FROM (SELECT 'Supplier Site Inactive Date' Modified_Field_name,
               assaa.AUDIT_TIMESTAMP,
               assa.VENDOR_ID,
               assa.vendor_site_id,
               TO_CHAR (assaa.INACTIVE_DATE, 'DD-MON-YYYY') INACTIVE_DATE,
               TO_CHAR (assa.INACTIVE_DATE, 'DD-MON-YYYY') inact_date,
               assa.creation_date,
               assaa.AUDIT_USER_NAME
          FROM ap_supplier_sites_all assa, ap_supplier_sites_all_a assaa
         WHERE assa.vendor_site_id = assaa.vendor_site_id
               AND assaa.INACTIVE_DATE IS NOT NULL) A

Share this article :

0 comments:

Post a Comment