I am working on a complex update where I need to update the data in one table based on the output from another table.
Scenario:
Table 1: HZ_CUST_ACCOUNTS (FK PARTY_ID FROM HZ_PARTIES)
| PARTY_ID | CUST_ACCOUNT_ID |
|---|---|
| 123 | 567 |
Table 2 HZ_PARTIES
| PARTY_ID | NAME |
|---|---|
| 123 | XYZ |
Table 3 HZ_CONTACT_POINTS (FK PARTY_ID FROM HZ_PARTIES)
| OWNER_TABLE_ID | OWNER_TABLE_NAME | |
|---|---|---|
| 123 | XYZ@GMAIL | HZ_PARTIES |
| 123 | ABC@GMAIL | HZ_PARTY_SITES |
Table 4: Customer_dimension_table
| cust_account_id | customer_id |
|---|---|
| 567 | 879 |
Table 5: Contact_dimension_table(target table)
| customer_id | name | |
|---|---|---|
| 879 | XYZ | NULL |
| 999 | XYZ | NULL |
| 879 | ABC | NULL |
I need your expertise in filling up the email address for the XYZ based on the above source table. Is this transformation possible? I am trying to fetch the contacts in oracle ebs but in the main query it is giving me the wrong email address for some of the parties as some of the parties have party_id(hz_parties) matching with the party_site_id(in HZ_party_sites). Hence I wanted to make an update so that it can update the correct email_address but update approach seems not to be working.
Here is the problematic query:
SELECT hcar.cust_account_role_id,
hcar.current_role_state,
hcar.role_type,
hcar.status,
hcar.cust_account_id,
hcar.cust_acct_site_id/* , ( SELECT hp.party_name
FROM hz_parties hp
WHERE 1=1
AND hp.party_id = hcar.party_id
) contact_name */
,
(SELECT hp.party_name
FROM hz_parties hp, hz_relationships hr
WHERE 1 = 1
AND hr.party_id = hcar.party_id
AND hp.party_id = hr.object_id
AND hr.object_type = 'PERSON'
AND hr.relationship_code IN
('CONTACT', 'EMPLOYER_OF'))
contact_name,
hcp.contact_point_id,
hcp.phone_country_code,
NVL(hcp.phone_area_code, (SELECT hcp.phone_area_code
FROM hz_contact_points hcp
WHERE 1 = 1
AND hcp.owner_table_id = hcar.party_id
AND hcp.phone_line_type(+) = 'GEN'
AND hcp.phone_area_code IS NOT NULL
--AND hcp.primary_flag(+) = 'Y'
AND hcp.contact_point_id =
(SELECT MAX (hcp2.contact_point_id)
FROM hz_contact_points hcp2
WHERE 1 = 1
AND hcp2.phone_line_type(+) = 'GEN'
AND hcp2.primary_flag(+) = 'Y'
AND hcp2.owner_table_id =
hcp.owner_table_id))) phone_area_code,
NVL(hcp.phone_number, (SELECT hcp.phone_number
FROM hz_contact_points hcp
WHERE 1 = 1
AND hcp.owner_table_id = hcar.party_id
AND hcp.phone_line_type(+) = 'GEN'
--AND hcp.primary_flag(+) = 'Y'
AND hcp.phone_number IS NOT NULL
AND hcp.contact_point_id =
(SELECT MAX (hcp2.contact_point_id)
FROM hz_contact_points hcp2
WHERE 1 = 1
AND hcp2.phone_line_type(+) = 'GEN'
--AND hcp2.primary_flag(+) = 'Y'
AND hcp2.owner_table_id =
hcp.owner_table_id))) phone_number,
NVL(hcp.phone_extension, (SELECT hcp.phone_extension
FROM hz_contact_points hcp
WHERE 1 = 1
AND hcp.owner_table_id = hcar.party_id
AND hcp.phone_line_type(+) = 'GEN'
-- AND hcp.primary_flag(+) = 'Y'
AND hcp.phone_extension IS NOT NULL
AND hcp.contact_point_id =
(SELECT MAX (hcp2.contact_point_id)
FROM hz_contact_points hcp2
WHERE 1 = 1
AND hcp2.phone_line_type(+) = 'GEN'
--AND hcp2.primary_flag(+) = 'Y'
AND hcp2.owner_table_id =
hcp.owner_table_id))) phone_extension ,
NVL(hcp.email_address,
NVL(( SELECT MAX(SUBSTR (hcp.email_address, 1, 50))
FROM hz_contact_points hcp
WHERE 1 = 1
AND hcp.owner_table_id IN (SELECT hr3.party_id
FROM hz_parties hp3,
hz_relationships hr3,
hz_cust_account_roles hcar3
WHERE 1 = 1
AND hr3.party_id = hcar3.party_id
AND hp3.party_id = hr3.object_id
AND hr3.object_type = 'PERSON'
AND hr3.relationship_code IN
('CONTACT', 'EMPLOYER_OF')
and hcar.cust_account_id=hcar3.cust_account_id
and hp3.party_name = (SELECT hp1.party_name
FROM hz_parties hp1, hz_relationships hr1
WHERE 1 = 1
AND hr1.party_id= hcar.party_id
AND hp1.party_id = hr1.object_id
AND hr1.object_type = 'PERSON'
AND hr1.relationship_code IN
('CONTACT', 'EMPLOYER_OF')) )
--= hcar.party_id
AND hcp.contact_point_type = 'EMAIL'
AND hcp.email_address IS NOT NULL
),
( (SELECT hp.email_address
FROM hz_parties hp
WHERE 1 = 1
AND hp.party_id = hcar.party_id ))
)
) email_address,
(SELECT DECODE (hcp2.contact_point_type,
'TLX', hcp2.telex_number,
hcp2.phone_number)
FROM hz_contact_points hcp2, fnd_lookup_values flv
WHERE 1 = 1
AND hcp2.contact_point_id = hcp.contact_point_id
AND hcp2.contact_point_type NOT IN ('EDI')
AND hcp2.owner_table_id = hcar.party_id
AND hcp2.primary_flag = 'Y'
AND NVL (hcp2.phone_line_type,
hcp2.contact_point_type) = 'FAX'
AND NVL (hcp2.phone_line_type,
hcp2.contact_point_type) = flv.lookup_code
AND ( ( flv.lookup_type = 'COMMUNICATION_TYPE'
AND flv.lookup_code IN
('PHONE', 'TLX', 'EMAIL', 'WEB'))
OR (flv.lookup_type = 'PHONE_LINE_TYPE'))
AND flv.language = USERENV ('LANG')
AND flv.view_application_id = 222
AND flv.security_group_id = 0)
fax,
(SELECT SUBSTR (NVL (hoc.job_title, hoc.job_title_code),
1,
250)
FROM hz_org_contacts hoc, hz_relationships hr
WHERE 1 = 1
AND hoc.party_relationship_id = hr.relationship_id
AND hr.party_id = hcar.party_id
AND hr.object_type = 'ORGANIZATION'
AND hr.directional_flag = 'F')
job_title,
'ORACLE' data_source,
hcp.primary_flag
FROM hz_cust_account_roles hcar, hz_contact_points hcp
WHERE 1 = 1
-- AND hcar.current_role_state = 'A'
AND hcp.owner_table_id(+) = hcar.party_id
AND hcp.primary_flag(+) = 'Y'
AND hcar.party_id IN
(SELECT hr.party_id
FROM hz_org_contacts hoc, hz_relationships hr
WHERE 1 = 1
AND hoc.party_relationship_id =
hr.relationship_id
AND hr.object_type = 'ORGANIZATION'
AND hr.directional_flag = 'F'
AND hoc.last_update_date > SYSDATE -5
UNION
SELECT party_id
FROM hz_cust_account_roles
WHERE last_update_date > SYSDATE - 5
)
