AP Withholding Tax by Invoice

select
       &P_Tax_Authority_Name              Tax_Authority_Name
,      &P_Tax_Authority_Site_Code         Tax_Authority_Site_Code
,      &P_TA_Address_Line1                TA_Address_Line1
,      &P_TA_Address_Line2                TA_Address_Line2
,      &P_TA_Address_Line3                TA_Address_Line3
,      &P_TA_City                         TA_City
,      &P_TA_State                        TA_State
,      &P_TA_Zip                          TA_Zip
,      &P_TA_Province                     TA_Province
,      &P_TA_Country                      TA_Country
,      decode(:P_AWT_Report
             ,'AWT4'
             ,t.tax_name
             ,null
             )                            Withholding_Tax_Name
,      decode(:P_AWT_Report
             ,'AWT4'
             ,n.description
             ,null
             )                            Withholding_Tax_Description
,      decode(:P_AWT_Report
             ,'AWT4'
             ,t.tax_rate
             ,null
             )                            Withholding_Tax_Rate
,      supplier.vendor_id                 Supplier_Id
,      supplier.vendor_name               Supplier_Name
,      supplier.segment1                  Supplier_Number
,      supplier.vat_registration_num      Supplier_Tax_File_Number
,      supplier.vat_registration_num      Supplier_Fiscal_Code
,      supplier.num_1099                  Supplier_NIF
,      &P_Supplier_Surname                Supplier_Surname
,      &P_Supplier_First_Name             Supplier_First_Name
,      &P_Supplier_Birthdate              Supplier_Birthdate
,      &P_Supplier_TOB                    Supplier_TOB
,      &P_Supplier_DOB                    Supplier_DOB
,      &P_Supplier_Sex                    Supplier_Sex
,      &P_Supplier_FCC                    Supplier_FCC
,      &P_Supplier_FCR                    Supplier_FCR
,      &P_Supplier_FIFC                   Supplier_FIFC
,      supplier_site.vendor_site_id       Site_Id
,      supplier_site.vendor_site_code     Site_Code
,      supplier_site.vat_registration_num Site_Fiscal_Code
,      supplier_site.address_line1        Site_Address_Line1
,      supplier_site.address_line2        Site_Address_Line2
,      supplier_site.address_line3        Site_Address_Line3
,      supplier_site.city                 Site_City
,      supplier_site.state                Site_State
,      supplier_site.zip                  Site_Zip
,      supplier_site.province             Site_Province
,      supplier_site.country              Site_Country
,      decode(:P_AWT_Report
             ,'AWT6'
             ,t.certificate_type
             ,null
             )                            Certificate_Type
,      decode(:P_AWT_Report
             ,'AWT6'
             ,t.certificate_number
             ,null
             )                            Certificate_Number
,      decode(:P_AWT_Report
             ,'AWT6'
             ,t.tax_rate
             ,null
             )                            Certificate_Tax_Rate
,      decode(:P_AWT_Report
             ,'AWT6'
             ,t.rate_type
             ,null
             )                            Certificate_Rate_Type_Test
,      decode(:P_AWT_Report
             ,'AWT6'
             ,t.start_date
             ,null
             )                            Certificate_Start_Date
,      decode(:P_AWT_Report
             ,'AWT6'
             ,t.end_date
             ,null
             )                            Certificate_End_Date
,      decode(:P_AWT_Report
             ,'AWT6'
             ,t.priority
             ,null
             )                            Certificate_Priority
,      decode(:P_REPORT_CURRENCY_V
             ,'ORIGINAL'
             ,i.invoice_currency_code
             ,:C_BASE_CURRENCY_CODE
             )                            Actual_Currency_Code
,      decode(:P_AWT_Report
             ,'AWT5'
             ,t.tax_name
             ,null
             )                            Inv_Tax_Name
,      decode(:P_AWT_Report
             ,'AWT5'
             ,n.description
             ,null
             )                            Inv_Tax_Description
,      i.invoice_date                     Invoice_Date
,      i.invoice_id                       Invoice_Id
,      i.invoice_type_lookup_code         Invoice_Type
,      i.invoice_currency_code            Invoice_Currency_Code
,      i.exchange_rate                    Invoice_Exchange_Rate
,      c.name                             Invoice_Currency_Name
,      i.invoice_num                      Invoice_Number
,      i.invoice_amount                   Invoice_Amount
,      i.base_amount                      Invoice_Base_Amount
,      i.description                      Invoice_Description
,      s.name                             Invoice_Doc_Sequence_Name
,      i.doc_sequence_value               Invoice_Doc_Sequence_Value
,      d.awt_invoice_payment_id           Break_AWT_Payment_Id
,      d.accrual_posted_flag              Accrual_Posted_Flag
,      d.cash_posted_flag                 Cash_Posted_Flag
,      d.accounting_date                  Accounting_Date
,     nvl(d.awt_gross_amount, i.invoice_amount)   Amount_Subject_To_Tax
,      d.amount                           Tax_Amount
,      d.base_amount                      Tax_Base_Amount
,      d.description                      Line_Description
,      d.awt_flag                         AWT_Flag
,      d.awt_invoice_payment_id           AWT_Invoice_Payment_Id
,      n.name                               Dist_Tax_Name
,      t.tax_rate                         Dist_Tax_Rate
,      t.rate_type                        Dist_Rate_Type
,      t.certificate_number               Dist_Certificate_Number
,      t.certificate_type                 Dist_Certificate_Type
,      t.start_date                       Dist_Cert_Start_Date
,      t.end_date                         Dist_Cert_End_Date
,      t.priority                         Dist_Cert_Priority,
	AP_APXWTGNR_XMLP_PKG.ctaaddress('No Tax Authority Info                                                                             ', 'No Tax Authority Info                                                                             ', 'No Tax Authority Info                                                                             ', 'No Tax Authority Info                                                                             ', 'No Tax Authority Info                                                                             ', 'No Tax Authority Info                                                                             ') C_TA_Address,
	AP_APXWTGNR_XMLP_PKG.coriginalinvtotal(:S1_Payment_Amount, :S1_Discount_Amount, :S0_Actual_Tax_Amount) C_Original_Inv_Total,
	AP_APXWTGNR_XMLP_PKG.csiteaddress(supplier_site.city, supplier_site.state, supplier_site.zip, supplier_site.address_line1, supplier_site.address_line2, supplier_site.address_line3) C_Site_Address,
	AP_APXWTGNR_XMLP_PKG.cinvoiceclass(d.awt_flag, i.invoice_date) C_Invoice_Class,
	AP_APXWTGNR_XMLP_PKG.cactualcurrencyname(c.name, :C_BASE_CURRENCY_NAME) C_Actual_Currency_Name,
	AP_APXWTGNR_XMLP_PKG.cinvoicefirstacctdate(i.invoice_id) C_Invoice_First_Acct_Date,
	AP_APXWTGNR_XMLP_PKG.cinvoiceactualamount(i.invoice_amount, i.invoice_currency_code, i.base_amount) C_Invoice_Actual_Amount,
	AP_APXWTGNR_XMLP_PKG.cinvoiceamountexempt(i.invoice_id, i.invoice_currency_code) C_Invoice_Amount_Exempt,
	AP_APXWTGNR_XMLP_PKG.cpaymentamount(i.invoice_id, d.awt_invoice_payment_id, decode ( :P_REPORT_CURRENCY_V , 'ORIGINAL' , i.invoice_currency_code , :C_BASE_CURRENCY_CODE ), i.invoice_currency_code) C_Payment_Amount,
	AP_APXWTGNR_XMLP_PKG.cdiscountamount(i.invoice_id, d.awt_invoice_payment_id, i.invoice_currency_code, i.exchange_rate) C_Discount_Amount,
	AP_APXWTGNR_XMLP_PKG.clastpaymentdoc(i.invoice_id, d.awt_invoice_payment_id) C_Last_Payment_Doc,
	AP_APXWTGNR_XMLP_PKG.clastpaymentdate(i.invoice_id, d.awt_invoice_payment_id) C_Last_Payment_Date,
	AP_APXWTGNR_XMLP_PKG.cgldistpostedstatus(d.accrual_posted_flag, d.cash_posted_flag) C_GL_Dist_Posted_Status,
	AP_APXWTGNR_XMLP_PKG.cactualamountsubject(nvl ( d_item.amount , i.invoice_amount ), decode ( :P_REPORT_CURRENCY_V , 'ORIGINAL' , i.invoice_currency_code , :C_BASE_CURRENCY_CODE ), i.invoice_currency_code, i.exchange_rate) C_Actual_Amount_Subject,
	AP_APXWTGNR_XMLP_PKG.cactualtaxamount(d.amount, decode ( :P_REPORT_CURRENCY_V , 'ORIGINAL' , i.invoice_currency_code , :C_BASE_CURRENCY_CODE ), i.invoice_currency_code, d.base_amount) C_Actual_Tax_Amount
from
       ap_invoice_distributions d
,      ap_invoices              i
,      fnd_currencies_vl        c
,      fnd_document_sequences   s
,      po_vendors               supplier
,      po_vendor_sites          supplier_site
,      ap_awt_tax_rates         t
,      ap_tax_codes             n
,      ap_invoice_distributions d_item
,      ap_checks               ac        --bug 20014337
,      ap_invoice_payments     aip       --bug 20014337
&P_Tax_Authority_Tables
where
       d.invoice_id            = i.invoice_id
and    i.vendor_id             = supplier_site.vendor_id
and    i.vendor_site_id        = supplier_site.vendor_site_id
and    i.vendor_id             = supplier.vendor_id
and    aip.invoice_payment_id(+) = d.awt_invoice_payment_id          --bug 20014337
and    ac.check_id(+)          = aip.check_id                        --bug 20014337
and    i.doc_sequence_id       = s.doc_sequence_id (+)
and    i.invoice_currency_code = c.currency_code
and    d.awt_tax_rate_id       = t.tax_rate_id (+)
and    d.withholding_tax_code_id          = n.tax_id (+) --bug16758675
&P_Tax_Authority_Joins
and    d.line_type_lookup_code = 'AWT'
and    d_item.invoice_id       = i.invoice_id  
and    d_item.invoice_distribution_id   =   d.awt_related_id
and    d_item.line_type_lookup_code <> 'AWT'
and    d.set_of_books_id       = :P_SET_OF_BOOKS_ID
&P_DATE_FILTER
&P_Select_Tax_Authority
&P_Restrict_To_Checkrun_Name
&P_Restrict_To_Paid_Dists
&P_Selected_Suppliers
&P_GL_Posted_Status
&P_Cert_Expiration_Range
&P_Restrict_Certificates
&P_Order_By