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(&P_TA_City, &P_TA_State, &P_TA_Zip , &P_TA_Address_Line1 , &P_TA_Address_Line2 ,&P_TA_Address_Line3) 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.awt_gross_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
&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 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
&P_Tax_Authority_Joins
and d.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 |
