SQL to pull 1099 data for year
In the United States, you must report to the Internal Revenue Service certain types of payments you make to 1099 reportable suppliers. In the supplier window, you can designate suppliers as federally
reportable. When you enter invoices for the supplier, you classify invoice distributions by 1099 MISC type using the Income Tax Type field. At year end, you can then report, in standard formats, your accumulated 1099 payment information to the Internal Revenue Service, other tax agencies, and your suppliers. For each reporting entity, you assign one or more balancing segment values. When you submit 1099 reports for a tax entity, Payables sums up the paid invoice distributions that have these company balancing segment values in their accounts.
In below SQL pass year details in ddmmyyyy format to pull 1099 tax data of that year-
In below sql designed to pull data for 2012 year. So for different year, replce highlited date as per ddmmyyyy format.
select * from (
SELECT TO_CHAR(To_Date('31122012','ddmmyyyy'),'yyyy') TAX_YEAR,
P.vendor_id,
Rpad(Upper(Nvl(p.Tax_Reporting_Name, p.Vendor_Name)),40) Vendor_Name,
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,
pvs.address_line1||' '||pvs.address_line2||' '||pvs.address_line3 payee_address,
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,
ITR.region_code,
round(sum(decode(ID.type_1099, 'MISC1' ,
(decode(I.invoice_amount,0,decode(I.cancelled_amount,
null,0,decode(greatest(ID.accounting_date, TRUNC(To_Date('31122012','ddmmyyyy'))+1 ),
ID.accounting_date,0,decode(least(ID.accounting_date, TRUNC(To_Date('01012012','ddmmyyyy'))-1 ),
ID.accounting_date,0,ID.amount))),ID.amount)/
decode(I.invoice_amount,0,decode(I.cancelled_amount,
null,1,0,1,I.cancelled_amount),
decode( INV_NET_AMT.netamount ,0,1,
INV_NET_AMT.netamount ))
*IP.amount),0)),2) MISC1,
round(sum(decode(ID.type_1099,'MISC2',
(decode(I.invoice_amount,0,decode(I.cancelled_amount,
null,0,decode(greatest(ID.accounting_date, TRUNC(To_Date('31122012','ddmmyyyy'))+1 ),
ID.accounting_date,0,decode(least(ID.accounting_date, TRUNC(To_Date('01012012','ddmmyyyy'))-1 ),
ID.accounting_date,0,ID.amount))),ID.amount)/
decode(I.invoice_amount,0,decode(I.cancelled_amount,
null,1,0,1,I.cancelled_amount),
decode( INV_NET_AMT.netamount ,0,1,
INV_NET_AMT.netamount ))
*IP.amount),0)),2) MISC2,
round(sum(decode(ID.type_1099, 'MISC3' ,
(decode(I.invoice_amount,0,decode(I.cancelled_amount,
null,0,decode(greatest(ID.accounting_date, TRUNC(To_Date('31122012','ddmmyyyy'))+1 ),
ID.accounting_date,0,decode(least(ID.accounting_date, TRUNC(To_Date('01012012','ddmmyyyy'))-1 ),
ID.accounting_date,0,ID.amount))),ID.amount)/
decode(I.invoice_amount,0,decode(I.cancelled_amount,
null,1,0,1,I.cancelled_amount),
decode( INV_NET_AMT.netamount ,0,1,
INV_NET_AMT.netamount ))
*IP.amount),0)),2) MISC3,
-1*(round(sum(decode(ID.type_1099, 'MISC4' ,
(decode(I.invoice_amount,0,decode(I.cancelled_amount,
null,0,decode(greatest(ID.accounting_date, TRUNC(To_Date('31122012','ddmmyyyy'))+1 ),
ID.accounting_date,0,decode(least(ID.accounting_date, TRUNC(To_Date('01012012','ddmmyyyy'))-1 ),
ID.accounting_date,0,ID.amount))),ID.amount)/
decode(I.invoice_amount,0,decode(I.cancelled_amount,
null,1,0,1,I.cancelled_amount),
decode( INV_NET_AMT.netamount ,0,1,
INV_NET_AMT.netamount ))
*IP.amount),0)),2)) MISC4,
round(sum(decode(ID.type_1099, 'MISC5' ,
(decode(I.invoice_amount,0,decode(I.cancelled_amount,
null,0,decode(greatest(ID.accounting_date, TRUNC(To_Date('31122012','ddmmyyyy'))+1 ),
ID.accounting_date,0,decode(least(ID.accounting_date, TRUNC(To_Date('01012012','ddmmyyyy'))-1 ),
ID.accounting_date,0,ID.amount))),ID.amount)/
decode(I.invoice_amount,0,decode(I.cancelled_amount,
null,1,0,1,I.cancelled_amount),
decode( INV_NET_AMT.netamount ,0,1,
INV_NET_AMT.netamount ))
*IP.amount),0)),2) MISC5,
round(sum(decode(ID.type_1099, 'MISC6' ,
(decode(I.invoice_amount,0,decode(I.cancelled_amount,
null,0,decode(greatest(ID.accounting_date, TRUNC(To_Date('31122012','ddmmyyyy'))+1 ),
ID.accounting_date,0,decode(least(ID.accounting_date, TRUNC(To_Date('01012012','ddmmyyyy'))-1 ),
ID.accounting_date,0,ID.amount))),ID.amount)/
decode(I.invoice_amount,0,decode(I.cancelled_amount,
null,1,0,1,I.cancelled_amount),
decode( INV_NET_AMT.netamount ,0,1,
INV_NET_AMT.netamount ))
*IP.amount),0)),2) MISC6,
round(sum(decode(ID.type_1099, 'MISC7' ,
(decode(I.invoice_amount,0,decode(I.cancelled_amount,
null,0,decode(greatest(ID.accounting_date, TRUNC(To_Date('31122012','ddmmyyyy'))+1 ),
ID.accounting_date,0,decode(least(ID.accounting_date, TRUNC(To_Date('01012012','ddmmyyyy'))-1 ),
ID.accounting_date,0,ID.amount))),ID.amount)/
decode(I.invoice_amount,0,decode(I.cancelled_amount,
null,1,0,1,I.cancelled_amount),
decode( INV_NET_AMT.netamount ,0,1,
INV_NET_AMT.netamount ))
*IP.amount),0)),2) MISC7,
round(sum(decode(ID.type_1099, 'MISC8' ,
(decode(I.invoice_amount,0,decode(I.cancelled_amount,
null,0,decode(greatest(ID.accounting_date, TRUNC(To_Date('31122012','ddmmyyyy'))+1 ),
ID.accounting_date,0,decode(least(ID.accounting_date, TRUNC(To_Date('01012012','ddmmyyyy'))-1 ),
ID.accounting_date,0,ID.amount))),ID.amount)/
decode(I.invoice_amount,0,decode(I.cancelled_amount,
null,1,0,1,I.cancelled_amount),
decode( INV_NET_AMT.netamount ,0,1,
INV_NET_AMT.netamount ))
*IP.amount),0)),2) MISC8,
round(sum(decode(ID.type_1099, 'MISC9' ,
(decode(I.invoice_amount,0,decode(I.cancelled_amount,
null,0,decode(greatest(ID.accounting_date, TRUNC(To_Date('31122012','ddmmyyyy'))+1 ),
ID.accounting_date,0,decode(least(ID.accounting_date, TRUNC(To_Date('01012012','ddmmyyyy'))-1 ),
ID.accounting_date,0,ID.amount))),ID.amount)/
decode(I.invoice_amount,0,decode(I.cancelled_amount,
null,1,0,1,I.cancelled_amount),
decode( INV_NET_AMT.netamount ,0,1,
INV_NET_AMT.netamount ))
*IP.amount),0)),2) MISC9,
round(sum(decode(ID.type_1099, 'MISC10' ,
(decode(I.invoice_amount,0,decode(I.cancelled_amount,
null,0,decode(greatest(ID.accounting_date, TRUNC(To_Date('31122012','ddmmyyyy'))+1 ),
ID.accounting_date,0,decode(least(ID.accounting_date, TRUNC(To_Date('01012012','ddmmyyyy'))-1 ),
ID.accounting_date,0,ID.amount))),ID.amount)/
decode(I.invoice_amount,0,decode(I.cancelled_amount,
null,1,0,1,I.cancelled_amount),
decode( INV_NET_AMT.netamount ,0,1,
INV_NET_AMT.netamount ))
*IP.amount),0)),2) MISC10,
round(sum(decode(ID.type_1099, 'MISC13' ,
(decode(I.invoice_amount,0,decode(I.cancelled_amount,
null,0,decode(greatest(ID.accounting_date, TRUNC(To_Date('31122012','ddmmyyyy'))+1 ),
ID.accounting_date,0,decode(least(ID.accounting_date, TRUNC(To_Date('01012012','ddmmyyyy'))-1 ),
ID.accounting_date,0,ID.amount))),ID.amount)/
decode(I.invoice_amount,0,decode(I.cancelled_amount,
null,1,0,1,I.cancelled_amount),
decode( INV_NET_AMT.netamount ,0,1,
INV_NET_AMT.netamount ))
*IP.amount),0)),2) MISC13,
round(sum(decode(ID.type_1099, 'MISC14' ,
(decode(I.invoice_amount,0,decode(I.cancelled_amount,
null,0,decode(greatest(ID.accounting_date, TRUNC(To_Date('31122012','ddmmyyyy'))+1 ),
ID.accounting_date,0,decode(least(ID.accounting_date, TRUNC(To_Date('01012012','ddmmyyyy'))-1 ),
ID.accounting_date,0,ID.amount))),ID.amount)/
decode(I.invoice_amount,0,decode(I.cancelled_amount,
null,1,0,1,I.cancelled_amount),
decode( INV_NET_AMT.netamount ,0,1,
INV_NET_AMT.netamount ))
*IP.amount),0)),2) MISC14,
round(sum(decode(ID.type_1099, 'MISC15a NT' ,
(decode(I.invoice_amount,0,decode(I.cancelled_amount,
null,0,decode(greatest(ID.accounting_date, TRUNC(To_Date('31122012','ddmmyyyy'))+1 ),
ID.accounting_date,0,decode(least(ID.accounting_date, TRUNC(To_Date('01012012','ddmmyyyy'))-1 ),
ID.accounting_date,0,ID.amount))),ID.amount)/
decode(I.invoice_amount,0,decode(I.cancelled_amount,
null,1,0,1,I.cancelled_amount),
decode( INV_NET_AMT.netamount ,0,1,
INV_NET_AMT.netamount ))
*IP.amount),0)),2) misc15ant,
round(sum(decode(ID.type_1099, 'MISC15a T' ,
(decode(I.invoice_amount,0,decode(I.cancelled_amount,
null,0,decode(greatest(ID.accounting_date, TRUNC(To_Date('31122012','ddmmyyyy'))+1 ),
ID.accounting_date,0,decode(least(ID.accounting_date, TRUNC(To_Date('01012012','ddmmyyyy'))-1 ),
ID.accounting_date,0,ID.amount))),ID.amount)/
decode(I.invoice_amount,0,decode(I.cancelled_amount,
null,1,0,1,I.cancelled_amount),
decode( INV_NET_AMT.netamount ,0,1,
INV_NET_AMT.netamount ))
*IP.amount),0)),2) misc15aT,
round(sum(decode(ID.type_1099, 'MISC15b' ,
(decode(I.invoice_amount,0,decode(I.cancelled_amount,
null,0,decode(greatest(ID.accounting_date, TRUNC(To_Date('31122012','ddmmyyyy'))+1 ),
ID.accounting_date,0,decode(least(ID.accounting_date, TRUNC(To_Date('01012012','ddmmyyyy'))-1 ),
ID.accounting_date,0,ID.amount))),ID.amount)/
decode(I.invoice_amount,0,decode(I.cancelled_amount,
null,1,0,1,I.cancelled_amount),
decode( INV_NET_AMT.netamount ,0,1,
INV_NET_AMT.netamount ))
*IP.amount),0)),2) MISC15b
FROM ap.ap_income_tax_regions ITR,
ap.ap_reporting_entity_lines_all REL,
po.po_vendors P,
ap.ap_bank_accounts_all ABA,
ap.ap_checks_all AC,
ap.ap_invoices_all I,
gl.gl_code_combinations CC,
ap.ap_invoice_distributions_all ID,
ap.ap_invoice_payments_all IP ,
( select AI.invoice_id , nvl(AID1.amount+AI.invoice_amount , AI.invoice_amount) as netamount
from ap.ap_invoices_all AI, ( select AID.invoice_id, SUM(AID.amount) as amount
From Ap.Ap_Invoice_Distributions_All Aid
Where ( Nvl(Aid.Invoice_Includes_Prepay_Flag, 'N' ) = 'N'
And (Aid.Line_Type_Lookup_Code = 'PREPAY'
OR (AID.line_type_lookup_code = 'TAX'
And Aid.Prepay_Tax_Parent_Id Is Not Null)))
OR AID.line_type_lookup_code = 'AWT'
GROUP by AID.invoice_id ) AID1
where AI.invoice_id = AID1.invoice_id (+) ) INV_NET_AMT ,
Po.Po_Vendor_Sites_All Pvs,
apps.Fnd_Territories_Vl Ft,
Apps.Per_All_People_F Papf
Where Id.Income_Tax_Region = Itr.Region_Short_Name (+)
AND P.vendor_id=I.vendor_id
AND pvs.vendor_id = P.vendor_id
AND nvl(P.employee_id,-99) = papf.person_id (+)
AND sysdate between papf.effective_start_date(+) and papf.effective_end_date(+) -- Bug 7419661
AND (AC.void_date is null
OR NOT AC.void_date BETWEEN TRUNC(To_Date('01012012','ddmmyyyy')) AND TRUNC(To_Date('31122012','ddmmyyyy')) )
AND I.invoice_id=IP.invoice_id
AND I.invoice_id=ID.invoice_id
AND INV_NET_AMT.invoice_id = I.invoice_id
AND IP.accounting_date BETWEEN TRUNC(To_Date('01012012','ddmmyyyy')) AND TRUNC(To_Date('31122012','ddmmyyyy'))
-- AND ID.accounting_date BETWEEN p_start_date AND p_end_date --Bug10412971: --Bug13344168
AND ID.type_1099 is not null
AND AC.bank_account_id = ABA.bank_account_id
And Ip.Check_Id = Ac.Check_Id
And Rel.Tax_Entity_Id = 10000
And Cc.Chart_Of_Accounts_Id = 101
And P.Federal_Reportable_Flag = 'Y'
And Aba.Asset_Code_Combination_Id = Cc.Code_Combination_Id
And Cc.Segment1 = Rel.Balancing_Segment_Value
AND ft.territory_code(+) = pvs.country
AND (pvs.tax_reporting_site_flag = 'Y'
OR
(pvs.vendor_site_code=
(select min(vendor_site_code)
from po.po_vendor_sites_all 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.po_vendor_sites_all pvs3
where pvs3.vendor_id = pvs.vendor_id)
)
AND not exists
(SELECT 'X' from po.po_vendor_sites_all pvs4
WHERE pvs4.tax_reporting_site_flag = 'Y'
AND pvs4.vendor_id = pvs.vendor_id)))
Group By Itr.Region_Code,P.vendor_name, 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 vendor_id asc;
Can you please share the same for oracle r12 as well?
ReplyDeleteextraordinary information. Get services of form 1099 online
ReplyDelete