Introduction:
This SQL query is used to fetching the data of Customer contact details like Customer Name, Contact Name, Email Address Contact Number and Dunning Letters etc… In this based on customer number we can get all the customer contact details will be displayed.
Cause of the issue:
Business wants a report that contains Customer Name, Contact Name, Email Address Contact Number and Dunning Letters of the customer from Fusion FSCM module. So, business wants to create a custom report to pull the required data.
How do we solve:
Create a report in BI publisher using below SQL query to extract the Customer details.
SQL Query:
SELECT (select distinct account_number from hz_cust_Accounts a1 where Party_id = p.PARTY_ID and substr(account_number ,1,1) <> ‘X’
and exists (select 1 from hz_cust_acct_sites_all where cust_account_id = a1.cust_account_id and
set_id in (300000003829025,300000003829050))) Customer_Acct_number,
hcpf.SEND_STATEMENTS,hcpf.DUNNING_LETTERS,
hps.party_site_number,
p.PARTY_NAME Customer_Name,
c.PARTY_NAME Contact_name,
c.STATUS Party_status,
EmailPEO.EMAIL_ADDRESS,
OrganizationContactPEO.ORG_CONTACT_ID,
EmailPEO.CONTACT_POINT_TYPE,
EmailPEO.CONTACT_POINT_PURPOSE,
OrganizationContactPEO.CONTACT_NUMBER,
hrr.RESPONSIBILITY_TYPE
FROM
HZ_RELATIONSHIPS r,
HZ_PARTIES p,
HZ_PARTIES c,
HZ_PARTY_SITES hps,
HZ_ORG_CONTACTS OrganizationContactPEO,
HZ_CONTACT_POINTS EmailPEO,
hz_cust_account_roles hcar,
HZ_ROLE_RESPONSIBILITY hrr,
hz_customer_profiles_f hcpf
WHERE p.party_id=r.object_id(+)
and p.party_id= hps.party_id and
c.party_id=r.subject_id(+)
and p.party_id=hcpf.party_id
and hcpf.PROGRAM_APPLICATION_ID = 222 and
r.object_type IN (‘ORGANIZATION’ ,’PERSON’) and
r.relationship_code(+)=’CONTACT_OF’ and
r.subject_type=’PERSON’ and
r.directional_flag = ‘F’ and
r.RELATIONSHIP_ID = OrganizationContactPEO.PARTY_RELATIONSHIP_ID(+) AND
EmailPEO.owner_table_id(+) = r.subject_id AND
EmailPEO.RELATIONSHIP_ID(+) = r.RELATIONSHIP_ID AND
EmailPEO.owner_table_name(+) = ‘HZ_PARTIES’ AND
EmailPEO.contact_point_type(+) =’EMAIL’ AND
r.RELATIONSHIP_ID = hcar.RELATIONSHIP_ID and
hcar.CUST_ACCOUNT_ROLE_ID = hrr.CUST_ACCOUNT_ROLE_ID(+) and
p.PARTY_ID in (select party_id from hz_cust_Accounts a where 1=1
and exists (select 1 from hz_cust_acct_sites_all where cust_account_id = a.cust_account_id and
set_id in (300000003829025,300000003829050)))
order by 1
