Friday, October 10, 2014

Sample SQL to extract 1099 tax data from Oracle AP

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;

1 comment:

  1. Regards
    Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
    LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
    Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at training@oracleappstechnical.com | +91 - 9581017828.

    ReplyDelete