Reports2017-11-18T12:27:27+00:00

AR European Sales Listing

Description
Categories: Enginatics, Financials
European Sales Listing (Elster ZM Meldung)

select distinct
nvl2(ftv.alternate_territory_code,'Y',null) eu_flag,
ftv.territory_short_name country,
ftv.territory_code country_code,
hcsua.tax_reference,
rcta.invoice_currency_code currency_code,
sum(nvl(rctlgda.amount,0)) over (partition by ftv.territory_short_name, ftv.territory_code, hcsua.tax_reference, rcta.invoice_currency_code) amount,
sum(nvl(rctlgda.acctd_amount,0)) over (partition by ftv.territory_short_name, ftv.territory_code, hcsua.tax_reference, rcta.invoice_currency_code) acctd_amount
from
gl_ledgers gl,
hr_operating_units hou,
ra_customer_trx_all rcta,
ra_cust_trx_types_all rctta,
ra_customer_trx_lines_all rctla,
ra_cust_trx_line_gl_dist_all rctlgda,
hz_cust_site_uses_all hcsua,
hz_cust_acct_sites_all hcasa,
hz_party_sites hps,
hz_locations hl,
fnd_territories_vl ftv
where
decode(rctta.type,'CM',:remit_to_address,rcta.remit_to_address_id)=:remit_to_address and
1=1 and
gl.ledger_id=hou.set_of_books_id and
hou.organization_id=rctlgda.org_id and
gl.ledger_id=rcta.set_of_books_id and
rcta.complete_flag='Y' and
rcta.org_id=rctta.org_id and
rcta.cust_trx_type_id=rctta.cust_trx_type_id and
rcta.customer_trx_id=rctla.customer_trx_id and
rctla.line_type!='TAX' and
rctla.customer_trx_line_id=rctlgda.customer_trx_line_id and
rctlgda.account_class in ('FREIGHT','REV','SUSPENSE','UNEARN','UNBILL') and
rctlgda.latest_rec_flag is null and
rcta.bill_to_site_use_id=hcsua.site_use_id and
hcsua.cust_acct_site_id=hcasa.cust_acct_site_id and
hcasa.party_site_id=hps.party_site_id and
hps.location_id=hl.location_id and
hl.country=ftv.territory_code
order by
nvl2(ftv.alternate_territory_code,'Y',null),
ftv.territory_short_name,
hcsua.tax_reference,
rcta.invoice_currency_code

Parameter Name SQL text Validation
Ledger
gl.name=:ledger_name
LOV
Branch (to which remitted)
 
LOV
To Date
rctlgda.gl_date<:date_to+1
Date
From Date
rctlgda.gl_date>=:date_from
Date