AP 1099 Payments - draft

Description
Categories: BI Publisher, Financials, Procurement
Application: Payables
Source: 1099 Payments Report (XML) - Not Supported: Reserved For Future Use
Short Name: APXTRRVT_XML
DB package: APXT7RVT
( 
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
Ledger
 
Query Driver
 
Meeting Minimum Levels Only
 
LOV Oracle
Federal Reportable Only
 
LOV Oracle
Tax Reporting Entity
 
LOV Oracle
Income Tax Region
 
LOV Oracle
Supplier Name
 
LOV Oracle
To Payment Date
 
Date
From Payment Date
 
Date
Reporting Option
 
LOV Oracle
Group By
 
LOV Oracle
Ask a question