AR Aggregate VAT Statement (1) Copy link

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 TO_DATE(:P_PERIOD_FROM,'RRRR/MM/DD HH24:MI:SS') AND TO_DATE(:P_PERIOD_TO,'RRRR/MM/DD HH24:MI:SS')
  AND NVL(rct_prev.trx_date,TO_DATE(:P_PERIOD_TO,'RRRR/MM/DD HH24:MI:SS')) BETWEEN TO_DATE(:P_PERIOD_FROM,'RRRR/MM/DD HH24:MI:SS') AND TO_DATE(:P_PERIOD_TO,'RRRR/MM/DD HH24:MI:SS')
  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