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

AR Customers and Sites

➜  Back to Library
Description
Categories: Enginatics, Financials
Customer master data including address, profile information, sites and site uses 

select
x.ou,
x.party_type,
x.party_name,
x.url,
x.duns_number,
x.sic_code,
x.category,
x.account_number,
x.reference,
x.site_reference,
x.site_use_reference,
x.account_type,
x.classification,
x.sales_channel,
hcpc.name profile_class,
xxen_util.meaning(x.credit_classification,'AR_CMGT_CREDIT_CLASSIFICATION',222) credit_classification,
x.site_name,
x.site_number,
x.location,
x.address,
x.country,
x.identifying_address_flag,
x.site_use,
x.primary_flag,
jrret.resource_name primary_salesrep,
ottt.name order_type,
qlht.name price_list,
x.ship_partial,
xxen_util.meaning(x.fob_point,'FOB',222) fob,
x.tax_reference,
&column_trx_count
x.profile_level,
rtt.name payment_term,
xxen_util.meaning(x.send_statement,'YES_NO',0) send_statement,
ascl.name statement_cycle,
xxen_util.meaning(x.send_credit_balance,'YES_NO',0) send_credit_balance,
xxen_util.meaning(x.send_dunning_letters,'YES_NO',0) send_dunning_letters,
adls.name dunning_letter,
ac.name collector_name,
x.tax_code,
avtab.tax_rate,
xxen_util.meaning(x.freight_term,'FREIGHT_TERMS',660) freight_term,
xxen_util.meaning(x.ship_via,'SHIP_METHOD',3) carrier,
x.ship_sets,
x.demand_class,
haou2.name warehouse,
x.party_status,
x.account_status,
x.site_status,
x.site_use_status,
xxen_util.user_name(x.created_by) created_by,
xxen_util.client_time(x.creation_date) creation_date,
xxen_util.user_name(x.last_updated_by) last_updated_by,
xxen_util.client_time(x.last_update_date) last_update_date
from
(
select
hou.name ou,
initcap(hp.party_type) party_type,
hp.party_name,
hp.url,
hp.duns_number,
decode(hp.party_type,'ORGANIZATION',hp.sic_code) sic_code,
xxen_util.meaning(hcas.class_code,'CUSTOMER_CATEGORY',222) category,
hca.account_number,
hca.orig_system_reference reference,
hcasa.orig_system_reference site_reference,
hcsua.orig_system_reference site_use_reference,
decode(hca.customer_type,'R','External','I','Internal') account_type,
xxen_util.meaning(hca.customer_class_code,'CUSTOMER CLASS',222) classification,
xxen_util.meaning(hca.sales_channel_code,'SALES_CHANNEL',660) sales_channel,
hps.party_site_name site_name,
hps.party_site_number site_number,
hcsua.location,
hz_format_pub.format_address (hps.location_id,null,null,' , ') address,
ftt.territory_short_name country,
xxen_util.meaning(hps.identifying_address_flag,'YES_NO',0) identifying_address_flag,
xxen_util.meaning(hcsua.site_use_code,'SITE_USE_CODE',222) site_use,
xxen_util.meaning(hcsua.primary_flag,'YES_NO',0) primary_flag,
xxen_util.meaning(hcsua.ship_partial,'YES_NO',0) ship_partial,
hcsua.tax_reference,
nvl(hcsua.tax_code,hca.tax_code) tax_code,
xxen_util.meaning(hcsua.demand_class_code,'DEMAND_CLASS',3) demand_class,
xxen_util.meaning(hcsua.ship_sets_include_lines_flag,'YES_NO',0) ship_sets,
decode(hp.status,'A','Active','I','Inactive','D','Deleted') party_status,
decode(hca.status,'A','Active','I','Inactive','D','Deleted') account_status,
decode(hcasa.status,'A','Active','I','Inactive','D','Deleted') site_status,
decode(hcsua.status,'A','Active','I','Inactive','D','Deleted') site_use_status,
nvl(hcsua.fob_point,hca.fob_point) fob_point,
nvl(hcsua.freight_term,hca.freight_term) freight_term,
nvl(hcsua.ship_via,hca.ship_via) ship_via,
nvl(hcsua.order_type_id,hca.order_type_id) order_type_id,
nvl(hcsua.price_list_id,hca.price_list_id) price_list_id,
nvl(hcsua.primary_salesrep_id,hca.primary_salesrep_id) primary_salesrep_id,
nvl(hcsua.org_id,hcasa.org_id) org_id,
nvl2(hcp2.cust_account_profile_id,'site',nvl2(hcp1.cust_account_profile_id,'account',null)) profile_level,
nvl(hcp2.profile_class_id,hcp1.profile_class_id) profile_class_id,
nvl(hcp2.send_statements,hcp1.send_statements) send_statement,
nvl(hcp2.credit_balance_statements,hcp1.credit_balance_statements) send_credit_balance,
nvl(hcp2.statement_cycle_id,hcp1.statement_cycle_id) statement_cycle_id,
nvl(hcp2.dunning_letters,hcp1.dunning_letters) send_dunning_letters,
nvl(hcp2.dunning_letter_set_id,hcp1.dunning_letter_set_id) dunning_letter_set_id,
nvl(hcp2.standard_terms,nvl(hcsua.payment_term_id,nvl(hcp1.standard_terms,hca.payment_term_id))) payment_term_id,
nvl(hcp2.collector_id,hcp1.collector_id) collector_id,
nvl(hcp2.credit_classification,hcp1.credit_classification) credit_classification,
nvl(hcp2.cust_account_profile_id,hcp1.cust_account_profile_id) cust_account_profile_id,
nvl(hcsua.warehouse_id,hca.warehouse_id) warehouse_id,
hou.set_of_books_id,
hca.cust_account_id,
hcsua.site_use_id,
hp.created_by,
hp.creation_date,
hp.last_updated_by,
hp.last_update_date
from
hr_operating_units hou,
hz_parties hp,
hz_cust_accounts hca,
(select hcasa.* from hz_cust_acct_sites_all hcasa where :detail_level in ('Site','Site Use')) hcasa,
hz_party_sites hps,
hz_locations hl,
fnd_territories_tl ftt,
(select hcsua.* from hz_cust_site_uses_all hcsua where :detail_level in ('Site Use')) hcsua,
(select hcp.* from hz_customer_profiles hcp where hcp.site_use_id is null) hcp1,
(select hcp.* from hz_customer_profiles hcp where hcp.site_use_id is not null and :detail_level='Site Use') hcp2,
(select hcas.* from hz_code_assignments hcas where hcas.owner_table_name='HZ_PARTIES' and hcas.class_category='CUSTOMER_CATEGORY' and hcas.primary_flag='Y') hcas
where
1=1 and
hp.party_id=hca.party_id and
hca.cust_account_id=hcasa.cust_account_id(+) and
hcasa.org_id=hou.organization_id(+) and
hcasa.party_site_id=hps.party_site_id(+) and
hps.location_id=hl.location_id(+) and
hl.country=ftt.territory_code(+) and
ftt.language(+)=userenv('lang') and
hcasa.cust_acct_site_id=hcsua.cust_acct_site_id(+) and
hca.cust_account_id=hcp1.cust_account_id(+) and
hcsua.site_use_id=hcp2.site_use_id(+) and
hp.party_id=hcas.owner_table_id(+)
) x,
qp_list_headers_tl qlht,
jtf_rs_salesreps jrs,
(select jrret.* from jtf_rs_resource_extns_tl jrret where jrret.language=userenv('lang') and jrret.category in ('EMPLOYEE','OTHER','PARTY','PARTNER','SUPPLIER_CONTACT')) jrret,
hz_cust_profile_classes hcpc,
oe_transaction_types_tl ottt,
hr_all_organization_units haou2,
(select x.* from (select max(avtab.vat_tax_id) over (partition by avtab.set_of_books_id, avtab.tax_code) max_vat_tax_id, avtab.* from ar_vat_tax_all_b avtab where sysdate between avtab.start_date and nvl(avtab.end_date,sysdate)) x where x.vat_tax_id=x.max_vat_tax_id) avtab,
ra_terms_tl rtt,
ar_dunning_letter_sets adls,
ar_statement_cycles ascl,
ar_collectors ac
where
x.price_list_id=qlht.list_header_id(+) and
qlht.language(+)=userenv('lang') and
x.primary_salesrep_id=jrs.salesrep_id(+) and
x.org_id=jrs.org_id(+) and
jrs.resource_id=jrret.resource_id(+) and
x.profile_class_id=hcpc.profile_class_id(+) and
x.order_type_id=ottt.transaction_type_id(+) and
ottt.language(+)=userenv('lang') and
x.warehouse_id=haou2.organization_id(+) and
x.tax_code=avtab.tax_code(+) and
x.set_of_books_id=avtab.set_of_books_id(+) and
x.payment_term_id=rtt.term_id(+) and
rtt.language(+)=userenv('lang') and
x.dunning_letter_set_id=adls.dunning_letter_set_id(+) and
x.statement_cycle_id=ascl.statement_cycle_id(+) and
x.collector_id=ac.collector_id(+)
order by
x.party_name,
x.ou,
x.country,
x.address,
x.site_use

Parameter Name SQL text Validation
Account Number 
hca.account_number=:account_number
 LOV
Country 
hl.country in (select ftt.territory_code from fnd_territories_tl ftt where ftt.territory_short_name=:country and ftt.language=userenv('lang'))
 LOV
Customer Name not like 
upper(hp.party_name) not like upper(:customer_name)
 LOV
Customer Name 
upper(hp.party_name) like upper(:customer_name)
 LOV
Show active only 
hp.status='A' and
hca.status='A' and
nvl(hcasa.status,'A')='A' and
nvl(hcsua.status,'A')='A'
 LOV
Show identifying Addresses only 
hps.identifying_address_flag='Y'
 LOV
Operating Unit 
hou.name=:operating_unit
 LOV
Level 
 
 LOV
Show trx count within x days 
(select count(*) from ra_customer_trx_all rcta where rcta.trx_date>=sysdate-:trx_count_days and x.cust_account_id=rcta.bill_to_customer_id and x.site_use_id=rcta.bill_to_site_use_id) trx_count,
 Number

Download
 

Related Blitz Reports
FND User Login History
AR Transactions and Lines
XLE Legal Entities
PER Organizations
PER Organization Hierarchies
AR Customer Credit Limits
FND Descriptive Flexfields
AR Miscellaneous Receipts
AR Incomplete Transactions
AR Payment Terms

© 2019 Copyright by Enginatics GmbH

By continuing to use the site, you agree to the use of cookies. Accept