IGI CIS2007 Subcontractor Statement of Payment and Deductions

	SELECT aprea.ENTITY_NAME CONTRACTOR_NAME,
           aprea.TAX_OFFICE_NUMBER,
           aprea.PAYE_REFERENCE,
		 --  rownum RECORD_NUM,
           hrl.address_line_1 ADDRESS_LINE_1,
           hrl.address_line_2 ADDRESS_LINE_2,
           hrl.address_line_3 ADDRESS_LINE_3,
           hrl.POSTAL_CODE        CONTRACTOR_POSTAL_CODE,
           hrl.REGION_1           CONTRACTOR_REGION_1,
           hrl.REGION_2           CONTRACTOR_REGION_2,
           hrl.REGION_3           CONTRACTOR_REGION_3,
           hrl.TELEPHONE_NUMBER_1 CONTRACTOR_TELEPHONE_NUMBER_1,
           hrl.TELEPHONE_NUMBER_2 CONTRACTOR_TELEPHONE_NUMBER_2,
           hrl.TELEPHONE_NUMBER_3 CONTRACTOR_TELEPHONE_NUMBER_3,
           hrl.TOWN_OR_CITY       CONTRACTOR_TOWN_OR_CITY,
           hrl.COUNTRY            CONTRACTOR_COUNTRY_SHORT_NAME,
           hrl.LOCATION_CODE      CONTRACTOR_LOCATION_CODE,
           hrl.DESCRIPTION        CONTRACTOR_DESCRIPTION,
           hrl.TAX_NAME           CONTRACTOR_TAX_NAME,
           hdr.UNIQUE_TAX_REFERENCE_NUM CONTRACTOR_UTR,
           lines.vendor_name SUBCONTRACTOR_NAME,
		 --  lag(lines.vendor_name,1) over (order by lines.vendor_name ) PREVIOUS_SUBCONT_NAME,
		   lines.SALUTATION SALUTATION,
           lines.FIRST_NAME FIRST_NAME,
           lines.LAST_NAME LAST_NAME,
           lines.trading_name TRADING_NAME,
		   pvs.address_line1 SUB_ADDRESS_LINE1,
           pvs.address_line2 SUB_ADDRESS_LINE2,
           pvs.address_line3 SUB_ADDRESS_LINE3,
           pvs.city CITY,
           pvs.state STATE,
           pvs.country COUNTRY,
           pvs.ZIP            SUB_ZIP,
           pvs.PROVINCE       SUB_PROVINCE,
           pvs.AREA_CODE      SUB_AREA_CODE,
           pvs.PHONE          SUB_PHONE,
           pvs.FAX            SUB_FAX,
           pvs.FAX_AREA_CODE  SUB_FAX_AREA_CODE,
           pvs.TELEX          SUB_TELEX,
		   pvs.ADDRESS_LINE4  SUB_ADDRESS_LINE4,
		   pvs.COUNTY         SUB_COUNTY,
		   pvs.EMAIL_ADDRESS  SUB_EMAIL_ADDRESS,
           lines.VERIFICATION_NUMBER VERIFICATION_NUMBER,
		   lines.UNIQUE_TAX_REFERENCE_NUM SUBCONTRACTOR_UTR
		   &P_SELECT_CLAUSE
	FROM igi_cis_mth_ret_hdr_h   hdr,
         igi_cis_mth_ret_lines_h lines,
         ap_reporting_entities   aprea,
		 ap_supplier_sites pvs
		 &P_FROM_CLAUSE
	WHERE hdr.HEADER_ID = lines.HEADER_ID
		AND hdr.REQUEST_STATUS_CODE = 'C' 
		AND aprea.unique_tax_reference_num = hdr.unique_tax_reference_num
		AND pvs.vendor_id = lines.vendor_id
        AND ((pvs.PRIMARY_PAY_SITE_FLAG = 'Y' AND NVL(pvs.INACTIVE_DATE,sysdate) >= sysdate) OR
 	               pvs.VENDOR_SITE_ID = (select min(site.VENDOR_SITE_ID) from AP_SUPPLIER_SITES site where pvs.VENDOR_ID = site.VENDOR_ID               and NVL(site.INACTIVE_DATE,sysdate) >= sysdate AND site.PAY_SITE_FLAG = 'Y' and NOT EXISTS(select 1 from AP_SUPPLIER_SITES vsite where              vsite.VENDOR_ID = site.VENDOR_ID and vsite.PRIMARY_PAY_SITE_FLAG = 'Y' and NVL(vsite.INACTIVE_DATE,sysdate) >= sysdate )))
	    &P_WHERE_CLAUSE