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

Description
Categories: BI Publisher
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