AP 1099 Supplier Exceptions Report- Not Supported: Reserved For Future Use (1)

SELECT      pv.vendor_id vendor_id1, lc.displayed_field  exception4, lc.lookup_code exception_code1
FROM        po_vendors pv, ap_lookup_codes lc
  WHERE ( nvl ( pv.federal_reportable_flag , 'N' ) = 'Y' AND exists ( SELECT /*+ NO_UNNEST */ 'site exists' FROM po_vendor_sites WHERE vendor_id = pv.vendor_id ) AND not exists ( SELECT  /*+ NO_UNNEST */ 'no 1099 site' FROM po_vendor_sites WHERE nvl ( tax_reporting_site_flag , 'N' ) = 'Y' AND vendor_id = pv.vendor_id ) AND lc.lookup_type = '1099 VENDOR EXCEPTION' AND lc.lookup_code = 'NO 1099 SITE' ) AND ( :vendor_id = pv.vendor_id)  UNION SELECT      pv.vendor_id vendor_id1,  lc.displayed_field  exception4, lc.lookup_code exception_code1
FROM        po_vendors pv, ap_lookup_codes lc,
	(SELECT distinct person_id
                  ,national_identifier
                  FROM PER_ALL_PEOPLE_F) papf --bug6050423
  WHERE ( ( pv.num_1099 is null OR replace ( replace ( nvl ( papf.national_identifier , nvl ( Pv.individual_1099 , Pv.num_1099 ) ) , '-' , '' ) , ' ' , '' ) is null OR replace ( replace ( nvl ( papf.national_identifier , nvl ( Pv.individual_1099 , Pv.num_1099 ) ) , '-' , '' ) , ' ' , '' ) = '000000000' ) AND nvl ( pv.federal_reportable_flag , 'N' ) = 'Y' AND lc.lookup_type = '1099 VENDOR EXCEPTION' AND lc.lookup_code = 'NULL TIN' AND nvl ( Pv.employee_id , - 99 ) = papf.person_id (+) ) AND ( :vendor_id = pv.vendor_id)  UNION SELECT      pv.vendor_id vendor_id1, lc.displayed_field  exception4, lc.lookup_code exception_code1
FROM        po_vendors pv, ap_lookup_codes lc
  WHERE ( pv.organization_type_lookup_code IS NULL AND nvl ( pv.federal_reportable_flag , 'N' ) = 'Y' AND lc.lookup_type = '1099 VENDOR EXCEPTION' AND lc.lookup_code = 'NULL ORGANIZATION TYPE' ) AND ( :vendor_id = pv.vendor_id)  UNION SELECT   pv.vendor_id vendor_id1, lc.displayed_field  exception4, lc.lookup_code exception_code1
FROM   po_vendor_sites pvs, ap_lookup_codes lc, po_vendors pv
  WHERE ( pvs.vendor_id = pv.vendor_id AND nvl ( pv.federal_reportable_flag , 'N' ) = 'Y' AND lc.lookup_type = '1099 VENDOR EXCEPTION' AND ( nvl ( pvs.tax_reporting_site_flag , 'N' ) = 'Y' OR EXISTS ( SELECT /*+ NO_UNNEST */ 'Only One Site Exists' FROM po_vendor_sites pvs1 WHERE pvs1.vendor_id = pv.vendor_id GROUP BY pvs1.vendor_id HAVING count ( * ) = 1 ) ) AND ( ( pvs.state is null and substr ( nvl ( pv.organization_type_lookup_code , 1 ) , 1 , 7 ) != 'FOREIGN' and lc.lookup_code = 'NULL STATE' ) OR ( lc.lookup_code = 'NON-STANDARD STATE' and substr ( nvl ( pv.organization_type_lookup_code , 1 ) , 1 , 7 ) != 'FOREIGN' and not exists ( SELECT /*+ NO_UNNEST */ 'Match on state' FROM AP_Income_Tax_Regions WHERE pvs.state = region_short_name AND sysdate < nvl ( inactive_date , sysdate + 1 ) AND sysdate >= nvl ( active_date , sysdate ) ) and pvs.state is not null ) OR ( lc.lookup_code = 'NULL ADDRESS LINES' and ( ( pvs.address_line1 is NULL or pvs.city is NULL or pvs.zip is null ) and substr ( nvl ( pv.organization_type_lookup_code , 1 ) , 1 , 7 ) != 'FOREIGN' ) ) OR ( lc.lookup_code = 'NULL FOREIGN ADDRESS' and ( ( pvs.address_line1 is NULL or pvs.country is null ) and substr ( nvl ( pv.organization_type_lookup_code , 1 ) , 1 , 7 ) = 'FOREIGN' ) ) ) ) AND ( :vendor_id = pv.vendor_id)  UNION SELECT  pv.vendor_id vendor_id1,lc.displayed_field  exception4, lc.lookup_code exception_code1
        FROM    po_vendors pv, ap_lookup_codes lc
          WHERE ( nvl ( pv.federal_reportable_flag , 'N' ) = 'Y' AND substr ( nvl ( pv.organization_type_lookup_code , 1 ) , 1 , 7 ) = 'FOREIGN' AND lc.lookup_type = '1099 VENDOR EXCEPTION' AND lc.lookup_code = 'FOREIGN VENDOR' ) AND ( :vendor_id = pv.vendor_id)  UNION SELECT  pv.vendor_id vendor_id1,   lc.displayed_field exception4, lc.lookup_code exception_code1
        FROM    po_vendors pv,
                po_vendors pv2 ,
	ap_lookup_codes lc,
	(SELECT distinct person_id
                  ,national_identifier
                  FROM PER_ALL_PEOPLE_F) papf, --bug6050423
	(SELECT distinct person_id
                  ,national_identifier
                  FROM PER_ALL_PEOPLE_F) papf1--bug6050423
          WHERE ( nvl ( papf.national_identifier , nvl ( Pv.individual_1099 , Pv.num_1099 ) ) = nvl ( papf.national_identifier , nvl ( Pv.individual_1099 , Pv.num_1099 ) ) AND nvl ( pv.federal_reportable_flag , 'N' ) = 'Y' AND substr ( nvl ( pv.organization_type_lookup_code , 1 ) , 1 , 7 ) != 'FOREIGN' AND nvl ( pv2.federal_reportable_flag , 'N' ) = 'Y' AND pv2.vendor_id != pv.vendor_id AND substr ( nvl ( pv2.organization_type_lookup_code , 1 ) , 1 , 7 ) != 'FOREIGN' AND lc.lookup_type = '1099 VENDOR EXCEPTION' AND lc.lookup_code = 'DUPLICATE TIN' AND nvl ( Pv.employee_id , - 99 ) = papf.person_id (+) AND nvl ( Pv2.employee_id , - 99 ) = papf1.person_id (+) ) AND ( :vendor_id = pv.vendor_id)  UNION SELECT  pv.vendor_id vendor_id1,lc.displayed_field  exception4, lc.lookup_code exception_code1
        FROM    po_vendors pv , ap_lookup_codes lc,
	(SELECT distinct person_id
                  ,national_identifier
                  FROM PER_ALL_PEOPLE_F) papf --bug6050423
          WHERE ( nvl ( pv.federal_reportable_flag , 'N' ) = 'Y' AND substr ( nvl ( pv.organization_type_lookup_code , 1 ) , 1 , 7 ) != 'FOREIGN' AND ( length ( replace ( replace ( nvl ( papf.national_identifier , nvl ( Pv.individual_1099 , Pv.num_1099 ) ) , '-' , '' ) , ' ' , '' ) ) != 9 OR nvl ( length ( ltrim ( translate ( replace ( replace ( nvl ( papf.national_identifier , nvl ( Pv.individual_1099 , Pv.num_1099 ) ) , '-' , '' ) , ' ' , '' ) , '1234567890' , '1111111111' ) , '1' ) ) , 0 ) != 0 ) AND lc.lookup_type = '1099 VENDOR EXCEPTION' AND lc.lookup_code = 'NON-STANDARD TIN' AND nvl ( Pv.employee_id , - 99 ) = papf.person_id (+) ) AND ( :vendor_id = pv.vendor_id)