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 |