ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 103C731B0912442AE0633CC4730A52EDY 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 XXEOS XXEOSARYVTSLD_XML US XXEOS ZM-Bericht AR (1) Imported from BI Publisher Description: ZM-Bericht AR Application: Customizations EOS Source: EOS: ZM-Bericht AR (XML) Short Name: XXEOSARYVTSLD_XML DB package: XXEOSARYVTSLD RDF 1=1 :c_addr_id :c_branch :c_city :c_country :c_group_by :c_group_by_adj :c_industry_code :c_line_no :c_postal_code :c_reptype :c_sales_title :c_sobcurr :c_sobname :c_street :c_tdr_vat_no :lp_where :p_chart_of_accounts_id :p_conc_request_id :p_enddate :p_remit_to_address :p_reptype :p_set_of_books_id :p_site_use_code :p_startdate :p_tax_rate_code 1 -20 :p_set_of_books_id LOV Oracle AR_SRS_SET_OF_BOOKS_ID Y select set_of_books_id id, set_of_books_id value, null description from ar_system_parameters order by value,description SELECT set_of_books_id FROM ar_system_parameters Y D Buchkennung Buchkennung 2 -10 :p_chart_of_accounts_id LOV Oracle AR_CHART_OF_ACCOUNTS Y select sb.chart_of_accounts_id id, sb.chart_of_accounts_id value, null description from gl_sets_of_books sb,ar_system_parameters sp where sb.set_of_books_id=sp.set_of_books_id order by value,description select sb.chart_of_accounts_id from gl_sets_of_books sb, ar_system_parameters sp where sb.set_of_books_id = sp.set_of_books_id Y D Kontenplankennung Kontenplankennung 3 10 :p_reptype LOV Oracle AR_REPORT_TYPE Y select lookup_code id, meaning value, null description from ar_lookups where lookup_type='REPORT_TYPE_SD' and enabled_flag='Y' and sysdate between nvl(start_date_active,sysdate) and nvl(end_date_active,sysdate) order by meaning,lookup_code Y D Details/Übersicht Details/Übersicht 4 20 :p_startdate Date Y D Von Datum Von Datum 5 30 :p_enddate Date Y D Bis Bis 6 40 :p_remit_to_address LOV Oracle AR_SRS_BRANCH_ID Y select acct_site.cust_acct_site_id id, acct_site.cust_acct_site_id value, loc.address1 description from hz_cust_acct_sites acct_site,hz_party_sites ps,hz_locations loc where acct_site.party_site_id=ps.party_site_id and loc.location_id=ps.location_id and acct_site.cust_account_id=-1 order by loc.address1 Y D (überwiesen an) Zweigstelle Kennung Zweigstelle Zahlungsempfänger 7 50 :p_site_use_code LOV Oracle OE_SITE_USE_CODES Y select lookup_code id, meaning value, null description from ar_lookups where lookup_type='SITE_USE_CODE' and lookup_code in ('BILL_TO','SHIP_TO') order by meaning select meaning from ar_lookups where lookup_type = 'SITE_USE_CODE' and lookup_code = 'BILL_TO' Y D Ausgewertete Adresse Ausgewertete Adresse 8 60 :p_tax_rate_code Char U EU (UST-ID) Y D relevanter Steuersatz Tax Rate Code