SELECT hcasa.org_id,
role_acct.account_number,
hcasa.orig_system_reference,
rel.subject_id,rel.object_id
,party.party_id party_id,
rel_party.party_id rel_party_id,
acct_role.cust_account_id ,
acct_role.cust_acct_site_id ,
party.person_pre_name_adjunct contact_prefix,
substr(party.person_first_name, 1, 40) contact_first_name,
substr(party.person_middle_name, 1, 40) contact_middle_name,
substr(party.person_last_name, 1, 50) contact_last_name,
party.person_name_suffix contact_suffix,
acct_role.status, org_cont.job_title contact_job_title,
org_cont.job_title_code contact_job_title_code,
rel_party.address1 contact_address1,
rel_party.address2 contact_address2,
rel_party.address3 contact_address3,
rel_party.address4 contact_address4,
rel_party.country contact_country,
rel_party.state contact_state,
rel_party.city contact_city,
rel_party.county contact_county,
rel_party.postal_code contact_postal_code
FROM hz_contact_points cont_point,
hz_cust_account_roles acct_role,
hz_parties party,
hz_parties rel_party,
hz_relationships rel,
hz_org_contacts org_cont,
hz_cust_accounts role_acct,
hz_contact_restrictions cont_res,
hz_person_language per_lang,
hz_cust_acct_sites_all hcasa
WHERE acct_role.party_id = rel.party_id
and acct_role.role_type = 'CONTACT'
and org_cont.party_relationship_id = rel.relationship_id
and rel.subject_id = party.party_id
and rel_party.party_id = rel.party_id
and cont_point.owner_table_id(+) = rel_party.party_id
and cont_point.contact_point_type(+) = 'EMAIL'
and cont_point.primary_flag(+) = 'Y'
and acct_role.cust_account_id = role_acct.cust_account_id
and role_acct.party_id = rel.object_id
and party.party_id = per_lang.party_id(+)
and per_lang.native_language(+) = 'Y'
and party.party_id = cont_res.subject_id(+)
and cont_res.subject_table(+) = 'HZ_PARTIES'
and role_acct.cust_account_id = hcasa.cust_account_id
and hcasa.cust_acct_site_id = acct_role.cust_acct_site_id
-- and acct_role.cust_account_id = p_customer_id --3177
-- and acct_role.cust_acct_site_id = p_address_id
-- and hcasa.org_id = 90
Thanks a lot. Of great help.
ReplyDelete-------------------CUSTOMER ACCOUNT CONTACT INFO
ReplyDeleteselect hp.party_name
,hp1.party_name Contact_name
,hoc.contact_number
,hcar.cust_acct_site_id
,hca.cust_account_id
from apps.hz_parties hp
,apps.hz_cust_accounts hca
,apps.hz_org_contacts hoc
,apps.hz_cust_account_roles hcar
,apps.hz_parties hp1
,apps.hz_relationships hr
where 1 = 1
and hca.party_id = hp.party_id
AND hr.subject_id = hp1.party_id
AND hr.object_id = hp.party_id
AND hcar.party_id = hr.party_id
AND hoc.party_relationship_id = hr.relationship_id
AND hcar.cust_acct_site_id is null
AND hcar.STATUS like 'A';
-------------------CUSTOMER ACCOUNT SITE CONTACT INFO
select hp.party_name
,hp1.party_name Contact_name
,hoc.contact_number
,hps.party_site_id
,hps.PARTY_SITE_NUMBER
from apps.hz_parties hp
,apps.hz_party_sites hps
,apps.hz_cust_accounts hca
,apps.hz_cust_acct_sites_all hcas
,apps.hz_org_contacts hoc
,apps.hz_cust_account_roles hcar
,apps.hz_parties hp1
,apps.hz_relationships hr
where 1 = 1
and hp.party_id = hps.party_id
and hca.party_id = hp.party_id
and hca.cust_account_id = hcas.cust_account_id
and hcas.party_site_id = hps.party_site_id
and hcar.cust_acct_site_id(+) = hcas.cust_acct_site_id
AND hr.subject_id = hp1.party_id
AND hr.object_id = hp.party_id
AND hcar.party_id = hr.party_id
AND hoc.party_relationship_id = hr.relationship_id
AND hcar.STATUS like 'A'
I'M NOW FULFILL FINANCIALLY BECAUSE OF THE LOAN I GOT FROM LFDS .I would like to bring this to the notice of the public about how i came in contact with LFDS after i lost my job and being denied loan by my bank and other financial institution due to my credit score. I could not pay my children's fees. I was behind on bills, about to be thrown out of the house due to my inability to pay my rent, It was during this period my kids were taken from me by foster care. Then i set out to seek for funds online where i lost $3,670 that i borrowed from friends which i was rip off by two online loan companies. Until i read about:Le_Meridian Funding Service (lfdsloans@outlook.com / lfdsloans@lemeridianfds.com) somewhere on the internet, Still wasn't convince because of what i have been through until a relative of mine who is a clergy also told me about the ongoing loan scheme of LFDS at a very low interest rate of 1.9%% and lovely repayment terms without penalty for default of payment. I have no choice than to also contact them which i did through text +1-989-394-3740 and Mr Benjamin responded back to me That day was the I'M best and greatest day of my life which can never be forgotten when i receive a credit alert of $400,000.00 Usd loan amount i applied for. I utilized the loan effectively to pay up my debts and to start up a business and today i and my kids are so happy and fulfill. You can as well contact them through email: (lfdsloans@outlook.com / lfdsloans@lemeridianfds.com) WhatsApptext helpline: +1-989-394-3740 Why am i doing this? I am doing this to save as many that are in need of a loan not to be victim of scams on the internet. Thanks and God bless you all, I'm Oleksander Artem from Horizon Park BC , Ukrain.
ReplyDelete