AR Aggregate VAT Statement

➜ Back to Library
Description
Categories: BI Publisher
Application: Receivables
Source: AR Aggregate VAT Statement
Short Name: ARAGGVATSTMT
DB package: AR_AGG_VAT_STMT_PKG

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Contact us to schedule a demo or if you need help with the installation

SELECT ft.territory_code      country_code
      ,hpar.tax_reference     vat_number
      ,hpar.party_name        customer_name
      ,rct.attribute1         attribute1
      ,rct.attribute2         attribute2
      ,rct.attribute3         attribute3
      ,rct.attribute4         attribute4
      ,rct.attribute5         attribute5
      ,rct.attribute6         attribute6
      ,rct.attribute7         attribute7
      ,rct.attribute8         attribute8
      ,rct.attribute9         attribute9
      ,rct.attribute10        attribute10
      ,rct.attribute11        attribute11
      ,rct.attribute12        attribute12
      ,rct.attribute13        third_party
      ,rct.attribute14        attribute14
      ,rct.attribute15        attribute15
      ,rct.trx_number         invoice_number
      ,rct.trx_date           invoice_date
      ,rctd.gl_date           hk_date
      ,rct.ship_date_actual   ship_date
      ,zl.tax_rate_code       tax_code
      ,zl.tax_date            tax_date
      ,SUM(NVL(zl.taxable_amt_funcl_curr
	         ,zl.taxable_amt * NVL(zl.currency_conversion_rate,NVL(rct.exchange_rate,1)))) taxable_amount
      ,zldf.tax_invoice_date tax_invoice_date	 
FROM  ra_customer_trx_all           rct
    , ra_customer_trx_all           rct_prev
    , ra_customer_trx_lines_all     rctl
    , ra_cust_trx_types_all         rctt
    , ra_cust_trx_line_gl_dist_all  rctd
    , hz_cust_site_uses_all         hcsu
    , hz_cust_acct_sites_all        hcas
    , hz_party_sites                hps
    , hz_parties                    hpar
    , hz_locations                  hl
    , fnd_territories_vl            ft
    , zx_lines                      zl
    , zx_taxes_b                    zt
    , mtl_country_assignments       mca
    , zx_lines_det_factors          zldf
WHERE rct.customer_trx_id          = rctl.customer_trx_id
  AND rct.customer_trx_id          = rctd.customer_trx_id
  AND rct.cust_trx_type_id         = rctt.cust_trx_type_id
  AND rct.org_id                   = rctt.org_id
  AND rct.previous_customer_trx_id = rct_prev.customer_trx_id(+)
  AND rctl.customer_trx_line_id    = zl.trx_line_id
  AND rctt.type                    IN ('INV','DM','CM')
  AND rct.complete_flag            = 'Y'
  AND zl.application_id            = 222
  AND zl.entity_code               = 'TRANSACTIONS'
  AND zt.tax_type_code             = 'VAT'
  AND rctd.account_class           = 'REC'
  AND rctd.latest_rec_flag         = 'Y'
  AND rctl.line_type               = 'LINE'
  AND zl.tax_id                    = zt.tax_id
  AND NVL(rct.ship_to_site_use_id,rct.bill_to_site_use_id) = hcsu.site_use_id
  AND hcsu.cust_acct_site_id       = hcas.cust_acct_site_id
  AND hcas.party_site_id           = hps.party_site_id
  AND hps.party_id                 = hpar.party_id
  AND hps.location_id              = hl.location_id
  AND hl.country                   = ft.territory_code
  AND rct.trx_date                 BETWEEN :P_PERIOD_FROM AND :P_PERIOD_TO
  AND NVL(rct_prev.trx_date,:P_PERIOD_TO) BETWEEN :P_PERIOD_FROM AND :P_PERIOD_TO
  AND rct.org_id                   = :P_ORG_ID
  AND rct.set_of_books_id          = :P_LEDGER_ID
  AND mca.zone_code                = 'EC'
  AND mca.territory_code           != 'SK'
  AND ft.territory_code            = mca.territory_code
  AND zldf.application_id          = 222
  AND zldf.entity_code             = zl.entity_code
  AND zldf.event_class_code        = zl.event_class_code
  AND zldf.trx_id                  = zl.trx_id
  AND zldf.trx_line_id             = zl.trx_line_id
  AND zldf.trx_level_type          = zl.trx_level_type 
GROUP BY ft.territory_code
      ,hpar.tax_reference
      ,hpar.party_name
      ,rct.attribute1
      ,rct.attribute2
      ,rct.attribute3
      ,rct.attribute4
      ,rct.attribute5
      ,rct.attribute6
      ,rct.attribute7
      ,rct.attribute8
      ,rct.attribute9
      ,rct.attribute10
      ,rct.attribute11
      ,rct.attribute12
      ,rct.attribute13
      ,rct.attribute14
      ,rct.attribute15
      ,rct.trx_number
      ,rct.trx_date
      ,rctd.gl_date
      ,rct.ship_date_actual
      ,zl.tax_rate_code
	  ,zl.tax_date
      ,zldf.tax_invoice_date
ORDER BY 1,2
Parameter Name SQL text Validation
Ledger 
 
 LOV Oracle
Legal Entity First Party 
 
 LOV Oracle
From Period 
 
 Date
To Period 
 
 Date
Report Type 
 
 LOV Oracle
Download
 
Related Blitz Reports
JG Regional Invoice Format Program- Not Supported: Reserved For Future Use AR Transactions and Lines ONT Orders and Lines GL Account Analysis (Distributions) ONT Orders RK: AR Transactions and Lines AR Transaction Detail Avalara VAT LinesData Avalara VAT listing
Blitz Report™

Blitz Report™ provides multiple benefits: