IGI CIS2007 Subcontractor Verification Status

	SELECT pov.FIRST_NAME,
       pov.SECOND_NAME,
       pov.LAST_NAME,
       pov.SALUTATION,
       (decode(pov.SALUTATION, NULL, NULL, pov.SALUTATION || '. ') ||
       decode(pov.FIRST_NAME, NULL, NULL, pov.FIRST_NAME || ' ') ||
       pov.LAST_NAME) SUPPLIER_NAME,
       pov.VENDOR_NAME VENDOR_NAME,
       pov.COMPANY_REGISTRATION_NUMBER COMPANY_REGISTRATION_NUMBER,
       pov.SEGMENT1,
       pov.TRADING_NAME,
       (select meaning
          from fnd_lookup_values_vl
         where LOOKUP_TYPE = 'VENDOR TYPE'
           and LOOKUP_CODE = pov.VENDOR_TYPE_LOOKUP_CODE) VENDOR_TYPE,	   
	   (SELECT to_char(MAX(aip.ACCOUNTING_DATE),'DD-MON-YYYY')
            FROM AP_CHECKS ac, 
                 AP_INVOICE_PAYMENTS aip,
                 AP_INVOICES inv,
                 AP_SUPPLIER_SITES pvs 
	    WHERE ac.VENDOR_ID = pov.VENDOR_ID
	      AND ac.VOID_DATE IS NULL
	      AND aip.CHECK_ID = ac.CHECK_ID
              AND inv.INVOICE_ID = aip.invoice_id
              AND pvs.VENDOR_ID = inv.VENDOR_ID
              AND pvs.VENDOR_SITE_ID = inv.VENDOR_SITE_ID
              AND upper(pvs.ALLOW_AWT_FLAG) = 'Y') LAST_PAYMENT_DATE,	   
	   (select tax.TAX_RATE from dual
	   where pov.awt_group_id is null
	   or pov.pay_awt_group_id is null
	   or pov.awt_group_id = pov.pay_awt_group_id)TAX_RATE,
       decode(pov.VENDOR_TYPE_LOOKUP_CODE,
               'PARTNERSHIP',
               pov.PARTNERSHIP_UTR,
               pov.UNIQUE_TAX_REFERENCE_NUM) UNIQUE_TAX_REFERENCE_NUM,       
       pov.NATIONAL_INSURANCE_NUMBER,
       (select tax.TAX_NAME from dual
	    where pov.awt_group_id is null
	   or pov.pay_awt_group_id is null
	   or pov.awt_group_id = pov.pay_awt_group_id)TAX_NAME,
       (select meaning
          from IGI_LOOKUPS
         where LOOKUP_TYPE = 'IGI_CIS2007_MATCH_STATUS'
           and LOOKUP_CODE = pov.MATCH_STATUS_FLAG) MATCH_STATUS,
       to_char(pov.CIS_VERIFICATION_DATE,'DD-MON-YYYY') CIS_VERIFICATION_DATE,
       pov.VERIFICATION_NUMBER,
       (select grp.NAME from dual
	    where pov.awt_group_id is null
	   or pov.pay_awt_group_id is null
	   or pov.awt_group_id = pov.pay_awt_group_id)  TAX_GROUP_NAME,
	   IGI_CIS2007_IGIPSUPR_PKG.igi_cis_is_verify_required(pov.VENDOR_ID) VERIFY_STATUS
  FROM AP_SUPPLIERS          pov,
	   AP_AWT_GROUPS       grp,
       AP_AWT_GROUP_TAXES  grp_tax,
	   AP_TAX_CODES tax_codes,
	   AP_AWT_TAX_RATES    tax       
 WHERE &pwhereclause 
 &porderclause