AP Withholding Tax by Invoice

Description
Categories: BI Publisher, Financials, Procurement
Application: Payables
Source: Withholding Tax by Invoice Report (XML)
Short Name: APXWTINV_XML
DB package: AP_APXWTGNR_XMLP_PKG
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
Parameter Name SQL text Validation
Withholding Date Basis
 
LOV Oracle
Currency
 
LOV Oracle
Supplier Number Range To
 
LOV Oracle
Supplier Number Range From
 
LOV Oracle
Supplier Name Range To
 
LOV Oracle
Supplier Name Range From
 
LOV Oracle
Supplier Name
 
LOV Oracle
To
 
Date
From
 
Date
Withholding Tax Name
 
LOV Oracle
Tax Authority Site
 
LOV Oracle
Tax Authority Name
 
LOV Oracle