PLSQL Query for Supplier Exemption Certificate Active/InActive - Oracle EBS R12

https://aporaclepayables.blogspot.com/2018/07/plsql-query-for-supplier-exemption.html

Supplier Exemption Certificate Active/InActive

select

 d.name,
 b.VENDOR_NAME,
 b.VENDOR_ID,
 b.SEGMENT1 "Supplier Number",
 c.VENDOR_SITE_CODE,
 a.TAX_NAME,
 a.CERTIFICATE_NUMBER,
 a.RATE_TYPE,
 a.PRIORITY,
 a.TAX_RATE,
 /* nvl(a.START_DATE,sysdate - 365000) st,
 nvl(a.END_DATE,sysdate + 365000) ed,*/
 case
   when trunc(nvl(a.END_DATE, sysdate + 365000)) < trunc(sysdate) then
    'N'
   when trunc(nvl(a.START_DATE, sysdate - 365000)) > trunc(sysdate) then
    'N'
   else
    'Y'
 end Status,
 to_char(a.START_DATE, 'DD-MON-YYYY') "FROM",
 a.START_DATE,
 to_char(a.END_DATE, 'DD-MON-YYYY') "TO",
 a.END_DATE,
 a.COMMENTS,
 a.ATTRIBUTE1 "Issuing Autority"

  from AP_AWT_TAX_RATES_all  a,
       ap_suppliers          b,
       ap_supplier_sites_all c,
       hr_operating_units    d
 where b.VENDOR_ID = c.VENDOR_ID
   and a.RATE_TYPE = 'CERTIFICATE'
   and b.VENDOR_ID = a.VENDOR_ID
   and a.VENDOR_SITE_ID = c.VENDOR_SITE_ID
   and a.ORG_ID = c.ORG_ID
   and a.ORG_ID = d.organization_id
   and a.ORG_ID = NVL(:P_ORG_ID, a.ORG_ID)
   and b.segment1 = NVL(:SEGMENT1, b.SEGMENT1)
   and c.VENDOR_SITE_CODE = NVL(:VENDOR_SITE_CODE, c.VENDOR_SITE_CODE)
   
      /*
      and ((( nvl(a.START_DATE,sysdate - 365000) between
      to_date(nvl(:P_FROM_DATE,
      to_char(sysdate - 365000, 'DD-MON-YYYY')   ),
      'DD-MON-YYYY') AND
      to_date(nvl(:P_TO_DATE,
      to_char(sysdate + 365000, 'DD-MON-YYYY')),
      'DD-MON-YYYY')) or
      (nvl(a.END_DATE,sysdate + 365000) between
      to_date(nvl(:P_FROM_DATE,
      to_char(sysdate - 365000, 'DD-MON-YYYY')),
      'DD-MON-YYYY') and
      to_date(nvl(:P_TO_DATE,
      to_char(sysdate + 365000, 'DD-MON-YYYY')),
      'DD-MON-YYYY'))) OR
      ((to_date(nvl(:P_FROM_DATE,
      to_char(sysdate - 365000, 'DD-MON-YYYY')),
      'DD-MON-YYYY') between nvl(a.START_DATE,sysdate - 365000) AND nvl(a.END_DATE,sysdate + 365000)) or
      (to_date(nvl(:P_TO_DATE,
      to_char(sysdate + 365000, 'DD-MON-YYYY')),
      'DD-MON-YYYY') between nvl(a.START_DATE,sysdate - 365000) and nvl(a.END_DATE,sysdate + 365000)))) 
      */
   
   and (
     
        ((nvl(a.START_DATE, sysdate - 365000) between
        nvl(:P_FROM_DATE, sysdate - 365000) AND
        nvl(:P_TO_DATE, sysdate + 365000)) or
        (nvl(a.END_DATE, sysdate + 365000) between
        nvl(:P_FROM_DATE, sysdate - 365000) and
        nvl(:P_TO_DATE, sysdate + 365000))) OR
        ((nvl(:P_FROM_DATE, sysdate - 365000) between
        nvl(a.START_DATE, sysdate - 365000) AND
        nvl(a.END_DATE, sysdate + 365000)) or
        (nvl(:P_TO_DATE, sysdate + 365000) between
        nvl(a.START_DATE, sysdate - 365000) and
        nvl(a.END_DATE, sysdate + 365000)))
     
       )
   
   and NVL(case
             when trunc(nvl(a.END_DATE, sysdate + 365000)) < trunc(sysdate) then
              'N'
             when trunc(nvl(a.START_DATE, sysdate - 365000)) > trunc(sysdate) then
              'N'
             else
              'Y'
           end,
           'NA') = NVL(:P_STATUS,
                       NVL(case
                             when trunc(nvl(a.END_DATE, sysdate + 365000)) < trunc(sysdate) then
                              'N'
                             when trunc(nvl(a.START_DATE, sysdate - 365000)) > trunc(sysdate) then
                              'N'
                             else
                              'Y'
                           end,
                           'NA'))
--  and a.START_DATE is null

 order by a.ORG_ID, b.VENDOR_NAME, c.VENDOR_SITE_CODE, a.START_DATE