AP 1099 Payments Report- Not Supported: Reserved For Future Use

➜ Back to Library
Description
Categories: BI Publisher
Imported from BI Publisher
Description: 1099 Payments Report
Application: Payables
Source: 1099 Payments Report (XML) - Not Supported: Reserved For Future Use
Short Name: APXTRRVT_XML
DB package: APXT7RVT

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  P.vendor_name Vendor_name,
	 P.segment1 Vendor_Number,
                     null Tax_Reporting_site,
	ID.type_1099 Income_Tax_Type,
                 decode(replace(replace(P.num_1099,'-',''),' ',''),'000000000','',P.num_1099) Tax_ID_Number, 
                round(sum(decode(I.invoice_amount,0,decode(I.cancelled_amount,
                         null,0,decode(greatest(ID.accounting_date,:p_end_date+1),
                         ID.accounting_date,0,decode(least(ID.accounting_date,
                         :p_start_date-1),ID.accounting_date,0,ABS(ID.amount)))),ID.amount)/
                    decode(I.invoice_amount,0,decode(I.cancelled_amount,
                         null,1,0,1,I.cancelled_amount),
                         decode(AP_UTILITIES_PKG.Net_Invoice_Amount(I.invoice_id),0,1,
                                      AP_UTILITIES_PKG.Net_Invoice_Amount(I.invoice_id)))
                        *IP.amount),2) payment_total
FROM     ap_reporting_entities RE,
               ap_reporting_entity_lines REL,
               ap_1099_tape_data TD,
               po_vendors P,
               gl_code_combinations CC,
               ap_invoices I,
               ap_checks C,
               ap_bank_accounts ABA,
               ap_invoice_distributions ID,
               ap_invoice_payments IP  
WHERE  not exists
               (select pvs.vendor_site_code
                 from   po_vendor_sites pvs
                 where p.vendor_id = pvs.vendor_id)
AND       (:p_input_region_short_name is null OR
                ID.income_tax_region = :p_input_region_short_name)
AND        P.vendor_id=I.vendor_id
and        c.void_date is null  
AND        P.vendor_id=TD.vendor_id 
AND      ((:p_fed_reportable = 'Y' and P.federal_reportable_flag = 'Y')
               OR (:p_fed_reportable = 'N' or :p_fed_reportable is null))
AND      I.invoice_id=IP.invoice_id
AND      I.invoice_id=ID.invoice_id
AND     C.check_id = IP.check_id
AND     C.bank_account_id = ABA.bank_account_id
AND      IP.accounting_date BETWEEN :p_start_date
AND      :p_end_date
AND      ID.type_1099 is not null
AND      ID.type_1099 <> 'MISC4'
AND     decode(:P_query_driver, 'INV', ID.dist_code_combination_id,
                       ABA.asset_code_combination_id) = CC.code_combination_id
AND      RE.tax_entity_id = :p_tax_entity_id
AND      RE.tax_entity_id = REL.tax_entity_id
AND      CC.chart_of_accounts_id = :C_CHART_ACCTS_ID
&C_DYNAMIC_SQL
GROUP BY P.segment1,P.vendor_name,ID.type_1099,
                 decode(replace(replace(P.num_1099,'-',''),' ',''),'000000000','',P.num_1099) 
UNION
 SELECT P.vendor_name Vendor_name,
	 P.segment1 Vendor_Number,
                    PVS.vendor_site_code Tax_Reporting_site,
                 ID.type_1099 Income_Tax_Type,
                P.num_1099 Tax_id_Number,
                round(sum(decode(I.invoice_amount,0,decode(I.cancelled_amount,
                         null,0,decode(greatest(ID.accounting_date,:p_end_date+1),
                         ID.accounting_date,0,decode(least(ID.accounting_date,
                         :p_start_date-1),ID.accounting_date,0,ABS(ID.amount)))),ID.amount)/
                    decode(I.invoice_amount,0,decode(I.cancelled_amount,
                         null,1,0,1,I.cancelled_amount),
                         decode(AP_UTILITIES_PKG.Net_Invoice_Amount(I.invoice_id),0,1,
                                      AP_UTILITIES_PKG.Net_Invoice_Amount(I.invoice_id)))
                        *IP.amount),2) payment_total
FROM    ap_income_tax_regions ITR,
               ap_reporting_entities RE,
               ap_reporting_entity_lines REL,
               ap_1099_tape_data TD,
               po_vendors P,
                po_vendor_sites pvs,
               gl_code_combinations CC,
               ap_invoices I,
               ap_checks C,
               ap_bank_accounts ABA,
               ap_invoice_distributions ID,
               ap_invoice_payments IP  
WHERE   p.vendor_id = pvs.vendor_id
AND       ( PVS.tax_reporting_site_flag  = 'Y'    OR
                  ( not exists
		( select   	' No Tax_Report_Flag Setting'
		   from        po_vendor_sites pvs2
		   where     pvs2.tax_reporting_site_flag = 'Y'
                                           and     pvs2.vendor_id = p.vendor_id )
                   AND
                   pvs.vendor_site_code = ( select     min(pvs2.vendor_site_code)
			           from        po_vendor_sites pvs2 
 			           where      pvs2.vendor_id = p.vendor_id ))
                )
AND       ID.income_tax_region = ITR.region_short_name (+)
and        c.void_date is null  
AND       (:p_input_region_short_name is null OR
                ID.income_tax_region = :p_input_region_short_name)
AND        P.vendor_id=I.vendor_id
AND	P.vendor_id=TD.vendor_id 
AND      ((:p_fed_reportable = 'Y' and P.federal_reportable_flag = 'Y')
               OR (:p_fed_reportable = 'N' or :p_fed_reportable is null))
AND      I.invoice_id=IP.invoice_id
AND      I.invoice_id=ID.invoice_id
AND     C.check_id = IP.check_id
AND     C.bank_account_id = ABA.bank_account_id
AND      IP.accounting_date BETWEEN :p_start_date
AND      :p_end_date
AND      ID.type_1099 is not null
AND      ID.type_1099  <> 'MISC4'
AND     decode(:P_query_driver, 'INV', ID.dist_code_combination_id,
                       ABA.asset_code_combination_id) = CC.code_combination_id
AND      RE.tax_entity_id = :p_tax_entity_id
AND      RE.tax_entity_id = REL.tax_entity_id
AND      CC.chart_of_accounts_id = :C_CHART_ACCTS_ID
&C_DYNAMIC_SQL
GROUP BY P.vendor_name,P.segment1, P.num_1099, PVS.vendor_site_code,ID.type_1099
) 
 and P.segment1=:Vendor_Number4 
 and ID.type_1099=:Income_Tax_Type2
order by 1
SELECT P.vendor_name Vendor_name,
	 P.segment1 Vendor_Number,
                    PVS.vendor_site_code Tax_Reporting_site,
                 ID.type_1099 Income_Tax_Type,
                P.num_1099 Tax_id_Number,
                round(sum(decode(I.invoice_amount,0,decode(I.cancelled_amount,
                         null,0,decode(greatest(ID.accounting_date,:p_end_date+1),
                         ID.accounting_date,0,decode(least(ID.accounting_date,
                         :p_start_date-1),ID.accounting_date,0,ABS(ID.amount)))),ID.amount)/
                    decode(I.invoice_amount,0,decode(I.cancelled_amount,
                         null,1,0,1,I.cancelled_amount),
                         decode(AP_UTILITIES_PKG.Net_Invoice_Amount(I.invoice_id),0,1,
                                      AP_UTILITIES_PKG.Net_Invoice_Amount(I.invoice_id)))
                        *IP.amount),2) payment_total
FROM    ap_income_tax_regions ITR,
               ap_reporting_entities RE,
               ap_reporting_entity_lines REL,
               ap_1099_tape_data TD,
               po_vendors P,
                po_vendor_sites pvs,
               gl_code_combinations CC,
               ap_invoices I,
               ap_checks C,
               ap_bank_accounts ABA,
               ap_invoice_distributions ID,
               ap_invoice_payments IP  
WHERE   p.vendor_id = pvs.vendor_id
AND       ( PVS.tax_reporting_site_flag  = 'Y'    OR
                  ( not exists
		( select   	' No Tax_Report_Flag Setting'
		   from        po_vendor_sites pvs2
		   where     pvs2.tax_reporting_site_flag = 'Y'
                                           and     pvs2.vendor_id = p.vendor_id )
                   AND
                   pvs.vendor_site_code = ( select     min(pvs2.vendor_site_code)
			           from        po_vendor_sites pvs2 
 			           where      pvs2.vendor_id = p.vendor_id ))
                )
AND       ID.income_tax_region = ITR.region_short_name (+)
and        c.void_date is null  
AND       (:p_input_region_short_name is null OR
                ID.income_tax_region = :p_input_region_short_name)
AND        P.vendor_id=I.vendor_id
AND	P.vendor_id=TD.vendor_id 
AND      ((:p_fed_reportable = 'Y' and P.federal_reportable_flag = 'Y')
               OR (:p_fed_reportable = 'N' or :p_fed_reportable is null))
AND      I.invoice_id=IP.invoice_id
AND      I.invoice_id=ID.invoice_id
AND     C.check_id = IP.check_id
AND     C.bank_account_id = ABA.bank_account_id
AND      IP.accounting_date BETWEEN :p_start_date
AND      :p_end_date
AND      ID.type_1099 is not null
AND      ID.type_1099  <> 'MISC4'
AND     decode(:P_query_driver, 'INV', ID.dist_code_combination_id,
                       ABA.asset_code_combination_id) = CC.code_combination_id
AND      RE.tax_entity_id = :p_tax_entity_id
AND      RE.tax_entity_id = REL.tax_entity_id
AND      CC.chart_of_accounts_id = :C_CHART_ACCTS_ID
&C_DYNAMIC_SQL
GROUP BY P.vendor_name,P.segment1, P.num_1099, PVS.vendor_site_code,ID.type_1099
) 
 and P.segment1=:Vendor_Number4 
 and ID.type_1099=:Income_Tax_Type2
order by 1
Parameter Name SQL text Validation
Operating Unit 
 
 LOV
Group By 
 
 LOV Oracle
Reporting Option 
 
 LOV Oracle
From Payment Date 
 
 Date
To Payment Date 
 
 Date
Supplier Name 
 
 LOV Oracle
Income Tax Region 
 
 LOV Oracle
Tax Reporting Entity 
 
 LOV Oracle
Federal Reportable Only 
 
 LOV Oracle
Meeting Minimum Levels Only 
 
 LOV Oracle
Query Driver 
 
 Char
Download
 
Blitz Report™

Blitz Report™ provides multiple benefits: