0% found this document useful (0 votes)
48 views2 pages

Customer Profile Query Results

The document contains a SQL query that retrieves distinct customer account information, including various attributes such as original system, business unit, customer name, account number, and contact details. It joins multiple tables related to customer accounts, parties, sites, and profiles to compile a comprehensive dataset. The query also includes filtering conditions for specific business units and created by parameters.
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
0% found this document useful (0 votes)
48 views2 pages

Customer Profile Query Results

The document contains a SQL query that retrieves distinct customer account information, including various attributes such as original system, business unit, customer name, account number, and contact details. It joins multiple tables related to customer accounts, parties, sites, and profiles to compile a comprehensive dataset. The query also includes filtering conditions for specific business units and created by parameters.
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

SELECT distinct (select distinct ORIG_SYSTEM from HZ_ORIG_SYS_REFERENCES where

orig_system_reference=hzp.orig_system_reference and rownum=1) ORIG_SYSTEM


, hzp.orig_system_reference party_orig_system_reference
, busineesunit.bu_name businees_unit
,hzp.party_name customer_name
,hca.account_number accountnumber
,party_site_number
,[Link] site_status
,hcsu.site_use_code addresstype
, location
, a5.address1
, a5.address2
, a5.address3
, a5.address4
, [Link]
, a5.postal_code
, [Link]
, [Link]
--,primary_flag
, [Link] profile_class_name
, [Link] collector_name
, cp.credit_balance_statements
, cp.credit_hold
, cp.dunning_letters send_dunning_letters
, cp.send_statements send_statements
, [Link] standard_terms
, cp.credit_currency_code
, hzp1.person_first_name contactname
,hcp.email_address
,hcp_phone.phone_number
FROM hz_cust_accounts hca
,hz_parties hzp
,hz_party_sites hzs
,hz_cust_acct_sites_all hcas
,hz_cust_account_roles hcar
,hz_party_site_uses hps1
,hz_relationships hr
,hz_parties hzp1
,hz_contact_points hcp
,hz_cust_site_uses_all hcsu,
hz_contact_points hcp_phone,
(
SELECT DISTINCT
b.bu_name,a.set_id
FROM fnd_setid_assignments a
INNER JOIN fun_all_business_units_v b
ON a.determinant_value = b.bu_id
WHERE a.reference_group_name = 'HZ_CUSTOMER_ACCOUNT_SITE'
) busineesunit,
hz_locations a5
, hz_cust_profile_classes cpc
, hz_customer_profiles_f cp
, ar_collectors col
, ra_terms term
, ar_statement_cycles cyc

-- hz_parties hp2
WHERE hzp.party_id = hca.party_id
AND hzp.party_id = hzs.party_id
AND hzs.party_id = hca.party_id
AND hcas.party_site_id = hzs.party_site_id
AND hcas.cust_account_id = hca.cust_account_id
AND hps1.party_site_id = hzs.party_site_id
AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
AND hcar.cust_acct_site_id = hcas.cust_acct_site_id
AND hcar.relationship_id = hr.relationship_id
--AND hzp.PREFERRED_CONTACT_PERSON_ID(+) = hp2.party_id
--AND CONTACT_POINT_PURPOSE = 'BUSINESS'
--AND hca.account_number='MH100055'
AND hzp.party_id = hr.subject_id
AND hr.object_id = hzp1.party_id
AND hcp.owner_table_id (+)= hzp1.party_id
AND hr.relationship_id = hcp.relationship_id(+)
and hcp.contact_point_type(+) ='EMAIL'
and [Link](+)='A'
AND hcp_phone.owner_table_id (+)= hzp1.party_id
AND hr.relationship_id = hcp_phone.relationship_id(+)
and hcp_phone.contact_point_type(+) ='PHONE'
and hcp_phone.STATUS(+)='A'
and busineesunit.set_id=hcas.set_id
AND hzs.location_id = a5.location_id
AND cp.profile_class_id = cpc.profile_class_id (+)
AND hca.cust_account_id = cp.cust_account_id
AND cp.collector_id = col.collector_id
AND cp.standard_terms = term.term_id (+)
AND cp.statement_cycle_id = cyc.statement_cycle_id (+)
AND cp.site_use_id IS NULL
and hzp.created_by =nvl(:p_created_by, hzp.created_by)
and busineesunit.bu_name =nvl(:p_Business_Unit,busineesunit.bu_name)
--[Link].57AND NVL(hcp.PRIMARY_FLAG,'Y')='Y'
--and hca.account_number = '[Link]-10'
--and hzp.party_name ='ELI LILLY (PHILIPPINES) Inc..PHLAT'

Common questions

Powered by AI

Dunning letters and statement cycles facilitate account receivable processes by providing systematic and consistent communication mechanisms to remind customers of outstanding payments. Dunning letters help prioritize collections activities and improve cash flow by prompting timely payments. Statement cycles provide regular updates, ensuring transparency in account activities and allowing both parties to reconcile their records accurately, thus reducing disputes .

The inclusion of detailed address information in a customer account management system enhances its functionality by allowing precise identification and communication with customers. It supports logistics and delivery operations, ensures compliance with regional regulations, and improves personalization and segmentation in marketing strategies. Additionally, accurate address data reduces errors in billing and shipping, leading to improved customer satisfaction and operational efficiency .

Standard terms impact the management of customer accounts by defining the conditions under which transactions occur, such as payment terms, delivery conditions, and returns policies. They ensure that there is a clear contractual framework governing business transactions, helping to prevent disputes and improve compliance with company policies. Having standard terms facilitates automation in billing and reduces administrative overhead .

Business units influence the management of customer relationships by providing a framework for organizing accounts according to the specific needs and strategies of each unit. They allow for differentiated customer relationship strategies, ensuring that resources and efforts are aligned with each business unit's objectives. In this system, business units are linked to customer accounts through set IDs, which help enforce consistency and separation of management processes across units .

Associating each customer with a dedicated account collector benefits the financial management process by ensuring that responsibility for managing customer accounts is clearly assigned. Collectors can build stronger relationships and understand specific customer behaviors, enhancing the effectiveness of collections and negotiations. This specialization allows for targeted strategies to address delinquent payments, improves communication, and potentially increases customer retention due to personalized service .

Primary contact flags in customer information systems play a crucial role by identifying the main point of contact for each customer. This identification is essential for directing important communications and ensuring timely responses to queries or issues. Such flags streamline communication workflows, reduce the complexity of managing multiple contact methods, and enhance the likelihood of successfully reaching the intended recipient, thus improving customer service and satisfaction .

Having distinct ORIG_SYSTEM values is significant because it ensures that customer accounts can be accurately traced back to their original systems. This traceability is crucial for maintaining data integrity, facilitating accurate reporting, and ensuring that changes or updates in customer information remain consistent across different systems .

Omitting location details can significantly impact customer relationships by limiting the ability to provide location-specific services and solutions. It could lead to logistical challenges, such as incorrect deliveries or service availabilities, reducing customer satisfaction. Moreover, it impedes targeted marketing efforts and compliance with local guidelines, which could potentially result in legal and operational penalties .

Profile classes play a critical role in managing customer credit profiles by categorizing customers based on their credit status and risk levels. Such classification allows for tailored credit terms, balances, and dunning letters, optimizing risk management and credit decision-making. Profile classes ensure consistency in how credit policies are applied and help streamline processes such as collections and credit evaluations .

Designing a system to include multiple contact points, such as email and phone numbers, for each customer is crucial for ensuring effective and flexible communication. Different situations may require different modes of contact, such as email for detailed information and phone calls for immediate issues. This design enhances customer service, supports diverse customer preferences, and ensures redundancy in communication channels, reducing the risk of losing contact with customers .

You might also like