Oracle Customer Relationship Query in Oracle apps

Oracle Customer Relationship Query in Oracle apps

In this post , I will share the sql query helps to extract the Customer Relationship Information's in Oracle apps. This query will share parent and the child customers information's in the relationship. You will also get the information about Relationship type (One Way or Reciprocal) with the help of this Sql Query.

 

Customer Relationship in Oracle Apps helps to control their Payments between them. With the Help of Customer Relationship we can Settle the payments received from one Customer against multiple customers Invoices. We can create relationships between any customers and relationship can also be one-way or reciprocal. In One-way Relationship , we can apply the payment or receipt of the Parents Customer with all their related customers invoices which we have created in the Relationship.

 

In Reciprocal Relationship , we can apply the payment or receipt of both Parent and child Customers with all their related customer invoices which we have created in the Relationship in two way.

 

Tables Involved in Oracle Customer Relationship Query

1.hz_cust_acct_relate_all

2.hz_parties

 

 

Detail Oracle Customer Relationship Query in Oracle apps

SELECT hzpuicustactrelateeo.cust_account_id,

       hzpuicustactrelateeo.related_cust_account_id,

       hzpuicustactrelateeo.last_update_date,

       hzpuicustactrelateeo.last_updated_by,

       hzpuicustactrelateeo.creation_date, hzpuicustactrelateeo.created_by,

       hzpuicustactrelateeo.last_update_login,

       hzpuicustactrelateeo.relationship_type, hzpuicustactrelateeo.comments,

       hzpuicustactrelateeo.attribute_category,

       hzpuicustactrelateeo.request_id,

       hzpuicustactrelateeo.program_application_id,

       hzpuicustactrelateeo.program_id,

       hzpuicustactrelateeo.program_update_date,

       hzpuicustactrelateeo.customer_reciprocal_flag,

       hzpuicustactrelateeo.status,

       hzpuicustactrelateeo.org_id, hzpuicustactrelateeo.bill_to_flag,

       hzpuicustactrelateeo.ship_to_flag,

       hca.account_name AS rel_act_desc, hp.party_name AS rel_customer,

       hca.account_number AS rel_act_number, 'Y' AS lov_mode, hp.party_id

  FROM hz_cust_acct_relate_all hzpuicustactrelateeo,

       hz_cust_accounts_all hca,

       hz_parties hp

 WHERE 1=1

   AND hca.cust_account_id = hzpuicustactrelateeo.related_cust_account_id

   AND hca.party_id = hp.party_id

   and hp.party_number=:PARTY_NUMBER