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

Description
Categories: BI Publisher
Imported from BI Publisher
Description: 1099 Electronic Media
Application: Payables
Source: 1099 Electronic Media (XML) - Not Supported: Reserved For Future Use
Short Name: APTCMT_XML
DB package: AP_APXT7CMT_XMLP_PKG

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Contact us to schedule a demo or if you need help with the installation

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
Parameter Name SQL text Validation
Operating Unit
 
LOV
Control Name
 
Char
Control Code
 
Char
From Payment Date
 
Date
To Payment Date
 
Date
Tax Reporting Entity
 
LOV Oracle
Media Type
 
LOV Oracle
Test Submission
 
LOV Oracle
Foreign Corporation
 
LOV Oracle
Last Year Filing
 
LOV Oracle
Payer Name Source
 
LOV Oracle
Query Driver
 
Char
Contact Telephone Number
 
Number
Contact Name
 
Char
Contact E-mail
 
Char
File Indicator
 
LOV Oracle
Original File
 
LOV Oracle
Replacement Alpha Character
 
Char
Electronic File Name
 
Char
Federal Reporting Limit
 
Number
Download
 
Blitz Report™

Blitz Report™ provides multiple benefits: