AP 1099 Electronic Media- Not Supported: Reserved For Future Use

SELECT  rownum C_rownum, P.vendor_name vendor_name,
                P.tax_reporting_name tax_reporting_name,
                rpad(P.name_control, 4) payee_name_control,
                pvs.address_line1||' '||pvs.address_line2||' '||pvs.address_line3 vendor_line,
                decode(substr(p.organization_type_lookup_code,1,7), 'FOREIGN',
                  substr(pvs.city,1,21)||' '||substr(pvs.state,1,9)||' '||substr(ft.territory_short_name,1,10),
                  rpad(substr(pvs.city,1,40),40)) vendor_city,
                rpad(substr(pvs.state,1,2),2) vendor_state,
                rpad(substr(replace(replace(pvs.zip,'-',''),' ',''),1,9),9) vendor_zip,
                decode(pvs.address_line1,'','No Address Line 1. ')||
                     decode(substr(p.organization_type_lookup_code,1,7),
                        'FOREIGN',
                     decode(pvs.country,'','No country. '),
                     decode(length(replace(replace(nvl(papf.national_identifier,nvl(P.individual_1099,P.num_1099)),
                        '-',''), ' ','')),9,'','Bad EIN/SSN. ')||
                     decode(pvs.city,'','No city. ')||
                     decode(nvl(pvs.province,pvs.state),'','No state. ')||
                     decode(replace(replace(pvs.zip,'-',''),' ',''),
                                                '','No postal code. ')) error_text,
                sum(misc1) misc1, sum(misc2) misc2, sum(misc3) misc3, sum(misc4) misc4, sum(misc5) misc5,
                sum(misc6) misc6, sum(misc7+misc15aT+misc15b) misc7, sum(misc8) misc8, sum(misc9) misc9, 
	sum(misc10) misc10, sum(misc13) misc13 ,sum(misc14) misc14, sum(misc15aT+misc15aNT) misc15a, 
	sum(misc15aT+misc15b) misc15b,
                decode(substr(P.organization_type_lookup_code,1,7), 'FOREIGN', '1', ' ') foreign_payee_flag,
                decode(replace(P.organization_type_lookup_code,'FOREIGN '),
                        'CORPORATION','1','INDIVIDUAL','2',' ') tin_type,
               decode(replace(replace(nvl(papf.national_identifier,nvl(P.individual_1099,P.num_1099)),'-',''),' ',''), '000000000',
                              '',replace(replace(nvl(papf.national_identifier,nvl(P.individual_1099,P.num_1099)),'-',''),' ','')) EIN,
                p.segment1 vendor_num,
                lpad(substr(TD.region_code,1,2),2,'0') region_code,
                p.vendor_id, 
	AP_APXT7CMT_XMLP_PKG.c_error_dummyformula(decode ( pvs.address_line1 , '' , 'No Address Line 1. ' ) || decode ( substr ( p.organization_type_lookup_code , 1 , 7 ) , 'FOREIGN' , decode ( pvs.country , '' , 'No country. ' ) , decode ( length ( replace ( replace ( nvl ( papf.national_identifier , nvl ( P.individual_1099 , P.num_1099 ) ) , '-' , '' ) , ' ' , '' ) ) , 9 , '' , 'Bad EIN/SSN. ' ) || decode ( pvs.city , '' , 'No city. ' ) || decode ( nvl ( pvs.province , pvs.state ) , '' , 'No state. ' ) || decode ( replace ( replace ( pvs.zip , '-' , '' ) , ' ' , '' ) , '' , 'No postal code. ' ) ), P.vendor_name) C_error_dummy, 
	AP_APXT7CMT_XMLP_PKG.cf_negative_miscformula(sum ( misc2 ), sum ( misc3 ), sum ( misc4 ), sum ( misc5 ), sum ( misc6 ), sum ( misc7 + misc15aT + misc15b ), sum ( misc8 ), sum ( misc9 ), sum ( misc10 ), sum ( misc13 ), sum ( misc14 ), sum ( misc15aT + misc15aNT ), sum ( misc15aT + misc15b ), decode ( pvs.address_line1 , '' , 'No Address Line 1. ' ) || decode ( substr ( p.organization_type_lookup_code , 1 , 7 ) , 'FOREIGN' , decode ( pvs.country , '' , 'No country. ' ) , decode ( length ( replace ( replace ( nvl ( papf.national_identifier , nvl ( P.individual_1099 , P.num_1099 ) ) , '-' , '' ) , ' ' , '' ) ) , 9 , '' , 'Bad EIN/SSN. ' ) || decode ( pvs.city , '' , 'No city. ' ) || decode ( nvl ( pvs.province , pvs.state ) , '' , 'No state. ' ) || decode ( replace ( replace ( pvs.zip , '-' , '' ) , ' ' , '' ) , '' , 'No postal code. ' ) ), P.vendor_name) CF_NEGATIVE_MISC, 
	AP_APXT7CMT_XMLP_PKG.c_b_recordformula(rpad ( P.name_control , 4 ), decode ( replace ( P.organization_type_lookup_code , 'FOREIGN ' ) , 'CORPORATION' , '1' , 'INDIVIDUAL' , '2' , ' ' ), decode ( replace ( replace ( nvl ( papf.national_identifier , nvl ( P.individual_1099 , P.num_1099 ) ) , '-' , '' ) , ' ' , '' ) , '000000000' , '' , replace ( replace ( nvl ( papf.national_identifier , nvl ( P.individual_1099 , P.num_1099 ) ) , '-' , '' ) , ' ' , '' ) ), p.vendor_id, sum ( misc1 ), sum ( misc2 ), sum ( misc3 ), sum ( misc4 ), sum ( misc5 ), sum ( misc6 ), sum ( misc7 + misc15aT + misc15b ), sum ( misc8 ), sum ( misc10 ), sum ( misc13 ), sum ( misc14 ), sum ( misc15aT + misc15aNT ), sum ( misc15aT + misc15b ), decode ( substr ( P.organization_type_lookup_code , 1 , 7 ) , 'FOREIGN' , '1' , ' ' ), P.tax_reporting_name, P.vendor_name, pvs.address_line1 || ' ' || pvs.address_line2 || ' ' || pvs.address_line3, decode ( substr ( p.organization_type_lookup_code , 1 , 7 ) , 'FOREIGN' , substr ( pvs.city , 1 , 21 ) || ' ' || substr ( pvs.state , 1 , 9 ) || ' ' || substr ( ft.territory_short_name , 1 , 10 ) , rpad ( substr ( pvs.city , 1 , 40 ) , 40 ) ), rpad ( substr ( pvs.state , 1 , 2 ) , 2 ), rpad ( substr ( replace ( replace ( pvs.zip , '-' , '' ) , ' ' , '' ) , 1 , 9 ) , 9 ), lpad ( substr ( TD.region_code , 1 , 2 ) , 2 , '0' ), sum ( misc9 )) C_B_record
FROM    ap_1099_tape_data TD,
                po_vendors P,
                po_vendor_sites pvs,
                fnd_territories_vl ft,
	(SELECT distinct person_id
                  ,national_identifier
                  FROM PER_ALL_PEOPLE_F) papf 
        WHERE   P.vendor_id IN ( SELECT X.vendor_id FROM ap_1099_tape_data X
                                                     GROUP BY vendor_id
                                                     HAVING   sum(nvl(misc1,0)) +
                                                                      sum(nvl(misc3,0)) +
                                                                      sum(nvl(misc6,0)) +
                                                                      sum(nvl(misc7,0)) +
                                                                      sum(nvl(misc9,0)) +
                                                                      sum(nvl(misc10,0)) >= :P_FEDERAL_REPORTING_LIMIT
                                                          OR      sum(nvl(misc2,0)) >= 10
                                                          OR      sum(nvl(misc8,0)) >= 10
                                                          OR	     sum(nvl(misc15aT,0)) +
			 	     sum(nvl(misc15aNT,0)) >= :P_FEDERAL_REPORTING_LIMIT
                                                          OR      sum(nvl(misc13,0)) +
                                                                     sum(nvl(misc14,0)) +
                                                                     sum(nvl(misc5,0)) > 0
                                                          OR      sum(nvl(misc15b,0)) > 0
                                                     UNION
SELECT  Y.vendor_id	
FROM ap_1099_tape_data Y
                                                     WHERE   region_code is not null )
        AND     TD.vendor_id = P.vendor_id
        AND     pvs.vendor_id = P.vendor_id
        AND     nvl(misc1,0)+nvl(misc2,0)+nvl(misc3,0)+nvl(misc4,0)+nvl(misc5,0)
	+nvl(misc6,0)+nvl(misc7,0)+nvl(misc8,0)+nvl(misc9,0)+nvl(misc10,0)
	+nvl(misc13,0)+nvl(misc14,0)+nvl(misc15aT,0)+nvl(misc15aNT,0)+nvl(misc15b,0) > 0
        AND     ft.territory_code(+) = pvs.country
       AND      nvl(P.employee_id,-99)  = papf.person_id (+)
        AND     (pvs.tax_reporting_site_flag = 'Y'
                OR
                (pvs.vendor_site_code=
                       (select min(vendor_site_code)
                        from   po_vendor_sites pvs2
                        where  pvs2.vendor_id = pvs.vendor_id
                        and    nvl(inactive_date,sysdate+9000) =
                                (select max(decode(inactive_date,
                                                     '',sysdate+9000,
                                                        inactive_date))
                                 from   po_vendor_sites pvs3
                                 where  pvs3.vendor_id = pvs.vendor_id)
                       )
                 AND not exists
                 (SELECT 'X' from po_vendor_sites pvs4
                  WHERE  pvs4.tax_reporting_site_flag = 'Y'
                  AND    pvs4.vendor_id = pvs.vendor_id)))
       GROUP BY rownum, P.vendor_name,  TD.region_code, P.name_control, 
                 P.organization_type_lookup_code, nvl(papf.national_identifier,nvl(P.individual_1099,P.num_1099)), P.segment1, P.tax_reporting_name,
                 pvs.country, ft.territory_short_name,
                 pvs.address_line1, pvs.address_line2, pvs.address_line3,
                 pvs.city, pvs.province, pvs.state, pvs.zip, p.vendor_id
        ORDER BY rownum