AP 1099 Electronic Media - draft

Description
Categories: BI Publisher
Columns: C Rownum, Vendor Name, Tax Reporting Name, Payee Name Control, Vendor Line, Vendor City, Vendor State, Vendor Zip, Error Text, Misc1 ...
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 Name SQL text Validation
Control Name
 
Control Code
 
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
 
Contact Telephone Number
 
Number
Contact Name
 
Contact E-mail
 
File Indicator
 
LOV Oracle
Original File
 
LOV Oracle
Replacement Alpha Character
 
Electronic File Name
 
Federal Reporting Limit
 
Number
Ledger