XXEOS ZM-Bericht AR (1)

select
		ft.territory_short_name cty_name,
		ft.territory_code country_code,
		nvl (ptp1.rep_registration_number, tax_reg.registration_number ) vat_no,
		ract.invoice_currency_code currency_code,
		sum (nvl (racg.amount, 0)) line_tot,
		sum (nvl (racg.acctd_amount, 0)) line_acctd_tot,
		:p_reptype p_reptype,
	XXEOSARYVTSLD.C_LINE_NO_p C_LINE_NO
from
		hz_cust_acct_sites ra,
		hz_party_sites party_site,
		hz_locations loc,
		fnd_territories_vl ft,
		hz_cust_accounts racu,
		ra_customer_trx ract,
		ra_cust_trx_types ractt,
		ra_customer_trx_lines racl,
		ra_cust_trx_line_gl_dist racg,
		hz_cust_site_uses rasu,
		zx_party_tax_profile ptp1,
		gl_code_combinations gcc,
		(
			select
					*
				from
					(
						select
								party_tax_profile_id,
								registration_number,
								count (registration_number) over (partition by party_tax_profile_id) reg_count
							from
								zx_registrations
							where
								sysdate between effective_from and nvl (effective_to, sysdate + 1)
					)
					reg
				where
					reg.reg_count = 1
		)
		tax_reg
	where
		rasu.cust_acct_site_id = ra.cust_acct_site_id
	and ra.party_site_id    = party_site.party_site_id
	and loc.location_id     = party_site.location_id
					and loc.country                                                               = ft.territory_code
	and ft.alternate_territory_code                                              is not null
	and loc.country                                                              != :c_country
	and ractt.cust_trx_type_id                                                    = ract.cust_trx_type_id
	and decode (ractt.type, 'CM', :p_remit_to_address, 'INV', :p_remit_to_address, ract.remit_to_address_id ) =
		:p_remit_to_address
	and racl.customer_trx_id      = ract.customer_trx_id
	and racl.customer_trx_line_id = racg.customer_trx_line_id
	and racg.gl_date between :p_startdate and :p_enddate
	and ract.set_of_books_id      = :p_set_of_books_id
	and racl.line_type           != 'TAX'
	and ract.complete_flag        = 'Y'
	and ptp1.party_id             = party_site.party_site_id
	and ptp1.party_type_code      = 'THIRD_PARTY_SITE'
	and ptp1.party_tax_profile_id = tax_reg.party_tax_profile_id(+)
	and racg.code_combination_id  = gcc.code_combination_id
	and exists
		(
			select
					'X'
				from
					zx_lines_v
				where
					trx_id = ract.customer_trx_id
				and upper(tax_rate_code) = upper(:p_tax_rate_code)
		)
	and not exists
		(
			select
					'X'
				from
					fnd_lookup_values_vl flv
				where
					lookup_type                = 'XXEOS_ACCOUNT_EXCLUSION'
				and nvl (enabled_flag, 'Y') = 'Y'
				and flv.meaning             = gcc.segment2
		)
		&lp_where
	group by
		ft.territory_code,
		ft.territory_short_name,
		nvl (ptp1.rep_registration_number, tax_reg.registration_number),
		ract.invoice_currency_code
		&c_group_by
union
select
		ft.territory_short_name cty_name,
		ft.territory_code country_code,
		nvl (ptp1.rep_registration_number, tax_reg.registration_number ) vat_no,
		ract.invoice_currency_code currency_code,
		sum (nvl(ad.amount_cr, 0)            - nvl(ad.amount_dr, 0) ) line_tot ,
		sum (nvl(ad.acctd_amount_cr, 0) - nvl(ad.acctd_amount_dr, 0) ) line_acctd_tot,
		:p_reptype p_reptype,
	XXEOSARYVTSLD.C_LINE_NO_p C_LINE_NO
from
		hz_cust_acct_sites ra,
		hz_party_sites party_site,
		hz_locations loc,
		fnd_territories_vl ft,
		hz_cust_accounts racu,
		ra_customer_trx ract,
		ra_cust_trx_types ractt,
		ra_customer_trx_lines racl,
		ra_cust_trx_line_gl_dist racg,
		hz_cust_site_uses rasu,
		zx_party_tax_profile ptp1,
		gl_code_combinations gcc,
		(
			select
					*
				from
					(
						select
								party_tax_profile_id,
								registration_number,
								count (registration_number) over (partition by party_tax_profile_id) reg_count
							from
								zx_registrations
							where
								sysdate between effective_from and nvl (effective_to, sysdate + 1)
					)
					reg
				where
					reg.reg_count = 1
		)
		tax_reg
	where
		rasu.cust_acct_site_id = ra.cust_acct_site_id
	and ra.party_site_id    = party_site.party_site_id
	and loc.location_id     = party_site.location_id
					and loc.country                                                               = ft.territory_code
	and ft.alternate_territory_code                                              is not null
	and loc.country                                                              != :c_country
	and ractt.cust_trx_type_id                                                    = ract.cust_trx_type_id
	and decode (ractt.type, 'CM', :p_remit_to_address, 'INV', :p_remit_to_address, ract.remit_to_address_id ) =
		:p_remit_to_address
	and racl.customer_trx_id      = ract.customer_trx_id
	and racl.customer_trx_line_id = racg.customer_trx_line_id
	and racg.gl_date between :p_startdate and :p_enddate
	and ract.set_of_books_id      = :p_set_of_books_id
	and racl.line_type           != 'TAX'
	and ract.complete_flag        = 'Y'
	and ptp1.party_id             = party_site.party_site_id
	and ptp1.party_type_code      = 'THIRD_PARTY_SITE'
	and ptp1.party_tax_profile_id = tax_reg.party_tax_profile_id(+)
	and racg.code_combination_id  = gcc.code_combination_id
	and exists
		(
			select
					'X'
				from
					zx_lines_v
				where
					trx_id = ract.customer_trx_id
				and upper(tax_rate_code) = upper(:p_tax_rate_code)
		)
	and not exists
		(
			select
					'X'
				from
					fnd_lookup_values_vl flv
				where
					lookup_type                = 'XXEOS_ACCOUNT_EXCLUSION'
				and nvl (enabled_flag, 'Y') = 'Y'
				and flv.meaning             = gcc.segment2
		)
		&lp_where
	group by
		ft.territory_code,
		ft.territory_short_name,
		nvl (ptp1.rep_registration_number, tax_reg.registration_number),
		ract.invoice_currency_code
		&c_group_by
union all
select
		ft.territory_short_name cty_name,
		ft.territory_code country_code,
		nvl (ptp1.rep_registration_number, tax_reg.registration_number ) vat_no,
		ract.invoice_currency_code currency_code,
		sum (nvl(ad.amount_cr, 0)            - nvl(ad.amount_dr, 0) ) line_tot ,
		sum (nvl(ad.acctd_amount_cr, 0) - nvl(ad.acctd_amount_dr, 0) ) line_acctd_tot,
		:p_reptype p_reptype
	from
		ar_adjustments_v adj ,
		ar_distributions_all ad ,
		gl_code_combinations gcc,
		ra_customer_trx_partial_v ract,
		hz_cust_acct_sites ra,
		hz_party_sites party_site,
		hz_locations loc,
		fnd_territories_vl ft,
		hz_cust_accounts racu,
		hz_cust_site_uses rasu,
		zx_party_tax_profile ptp1,
		(
			select
					*
				from
					(
						select
								party_tax_profile_id,
								registration_number,
								count (registration_number) over (partition by party_tax_profile_id) reg_count
							from
								zx_registrations
							where
								sysdate between effective_from and nvl (effective_to, sysdate + 1)
					)
					reg
				where
					reg.reg_count = 1
		)
		tax_reg
	where
		ad.source_id              = adj.adjustment_id
	and ad.code_combination_id = gcc.code_combination_id
	and ad.source_table        = 'ADJ'
	and ad.source_type        <> 'TAX'
	and not exists
		(
			select
					'X'
				from
					fnd_lookup_values_vl flv
				where
					lookup_type                = 'XXEOS_ACCOUNT_EXCLUSION'
				and nvl (enabled_flag, 'Y') = 'Y'
				and flv.meaning             = gcc.segment2
		)
	and adj.gl_date between :p_startdate and :p_enddate
	and adj.customer_trx_id                                                           = ract.customer_trx_id
	and rasu.cust_acct_site_id                                                        = ra.cust_acct_site_id
	and ra.party_site_id                                                              = party_site.party_site_id
	and loc.location_id                                                               = party_site.location_id
	and loc.country                                                                   = ft.territory_code
	and ft.alternate_territory_code                                                  is not null
	and loc.country                                                                  != :c_country
	and decode (ract.ctt_class, 'CM', :p_remit_to_address, 'INV', :p_remit_to_address,ract.remit_to_address_id ) =
		:p_remit_to_address
	and adj.set_of_books_id       = :p_set_of_books_id
	and adj.gl_posted_date       is not null
	and ptp1.party_id             = party_site.party_site_id
	and ptp1.party_type_code      = 'THIRD_PARTY_SITE'
	and ptp1.party_tax_profile_id = tax_reg.party_tax_profile_id(+)
	and exists
		(
			select
					'X'
				from
					zx_lines_v
				where
					trx_id = ract.customer_trx_id
				and upper(tax_rate_code) = upper(:p_tax_rate_code)
		)
		&lp_where
	group by
		ft.territory_code,
		ft.territory_short_name,
		nvl (ptp1.rep_registration_number, tax_reg.registration_number),
		ract.invoice_currency_code
		&c_group_by_adj
	order by
		1,3