AP 1099 Electronic Media - draft

Description
Categories: BI Publisher, Financials, Procurement
Application: Payables
Source: 1099 Electronic Media (XML) - Not Supported: Reserved For Future Use
Short Name: APTCMT_XML
DB package: AP_APXT7CMT_XMLP_PKG
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 NameSQL textValidation
Ledger
 
Federal Reporting Limit
 
Number
Electronic File Name
 
Replacement Alpha Character
 
Original File
 
LOV Oracle
File Indicator
 
LOV Oracle
Contact E-mail
 
Contact Name
 
Contact Telephone Number
 
Number
Query Driver
 
Payer Name Source
 
LOV Oracle
Last Year Filing
 
LOV Oracle
Foreign Corporation
 
LOV Oracle
Test Submission
 
LOV Oracle
Media Type
 
LOV Oracle
Tax Reporting Entity
 
LOV Oracle
To Payment Date
 
Date
From Payment Date
 
Date
Control Code
 
Control Name