ZCH_DUPLICATE_PARTIES

Details

Schema: FUSION
Object owner: ZCH
Object type: VIEW

Columns

Name
DATA_SET_UUID CONTEXT_PARTY_ID CONTEXT_PARTY_NUMBER DUPLICATE_PARTY_ID DUPLICATE_PARTY_NUMBER DUPLICATE_PARTY_NAME DUPLICATE_PARTY_TYPE PROMOTED_FLAG CREATION_DATE CREATED_BY LAST_UPDATE_DATE LAST_UPDATED_BY LAST_UPDATE_LOGIN OBJECT_VERSION_NUMBER

Name

DATA_SET_UUID

CONTEXT_PARTY_ID

CONTEXT_PARTY_NUMBER

DUPLICATE_PARTY_ID

DUPLICATE_PARTY_NUMBER

DUPLICATE_PARTY_NAME

DUPLICATE_PARTY_TYPE

PROMOTED_FLAG

CREATION_DATE

CREATED_BY

LAST_UPDATE_DATE

LAST_UPDATED_BY

LAST_UPDATE_LOGIN

OBJECT_VERSION_NUMBER

Query

SQL_Statement
select dm.data_set_uuid, cm.record_id context_party_id, cp.party_number context_party_number, dm.record_id duplicate_party_id, dp.party_number duplicate_party_number, dp.party_name duplicate_party_name, dp.party_type duplicate_party_type, dm.master_flag promoted_flag, dm.creation_date, dm.created_by, dm.last_update_date, dm.last_updated_by, dm.last_update_login, dm.object_version_number from zch_dedup_links_b dl, zch_dedup_link_members cm, zch_dedup_link_members dm, hz_parties cp, hz_parties dp where cm.data_set_uuid = dl.data_set_uuid and cm.dedup_link_type = dl.dedup_link_type and cm.dedup_link_id = dl.dedup_link_id and dl.dedup_link_status = 'A' and cm.dedup_link_type = 'DQ_IDENTITY' and cm.master_flag = 'Y' and cm.effective_start_date <= trunc(SYSDATE + 1) and (cm.effective_end_date >= trunc(SYSDATE) or cm.effective_end_date is null) and cm.restore_flag = 'N' and cm.record_id = cp.party_id and dm.data_set_uuid = cm.data_set_uuid and dm.dedup_link_type = cm.dedup_link_type and dm.dedup_link_id = cm.dedup_link_id and dm.master_flag = 'N' and dm.effective_start_date <= trunc(SYSDATE + 1) and (dm.effective_end_date >= trunc(SYSDATE) or dm.effective_end_date is null) and dm.restore_flag = 'N' and dm.record_id = dp.party_id and dp.status = 'A' union all select dm.data_set_uuid, cm.record_id context_party_id, cp.party_number context_party_number, dm.record_id duplicate_party_id, dp.party_number duplicate_party_number, dp.party_name duplicate_party_name, dp.party_type duplicate_party_type, dm.master_flag promoted_flag, dm.creation_date, dm.created_by, dm.last_update_date, dm.last_updated_by, dm.last_update_login, dm.object_version_number from zch_dedup_links_b dl, zch_dedup_link_members cm, zch_dedup_link_members dm, hz_parties cp, hz_parties dp where cm.data_set_uuid = dl.data_set_uuid and cm.dedup_link_type = dl.dedup_link_type and cm.dedup_link_id = dl.dedup_link_id and dl.dedup_link_status = 'A' and cm.dedup_link_type = 'DQ_IDENTITY' and cm.master_flag = 'N' and cm.effective_start_date <= trunc(SYSDATE + 1) and (cm.effective_end_date >= trunc(SYSDATE) or cm.effective_end_date is null) and cm.restore_flag = 'N' and cm.record_id = cp.party_id and dm.data_set_uuid = cm.data_set_uuid and dm.dedup_link_type = cm.dedup_link_type and dm.dedup_link_id = cm.dedup_link_id and dm.master_flag = 'Y' and dm.effective_start_date <= trunc(SYSDATE + 1) and (dm.effective_end_date >= trunc(SYSDATE) or dm.effective_end_date is null) and dm.restore_flag = 'N' and dm.record_id = dp.party_id and dp.status = 'A'

SQL_Statement

select

dm.data_set_uuid,

cm.record_id context_party_id,

cp.party_number context_party_number,

dm.record_id duplicate_party_id,

dp.party_number duplicate_party_number,

dp.party_name duplicate_party_name,

dp.party_type duplicate_party_type,

dm.master_flag promoted_flag,

dm.creation_date,

dm.created_by,

dm.last_update_date,

dm.last_updated_by,

dm.last_update_login,

dm.object_version_number

from

zch_dedup_links_b dl,

zch_dedup_link_members cm,

zch_dedup_link_members dm,

hz_parties cp,

hz_parties dp

where cm.data_set_uuid = dl.data_set_uuid

and cm.dedup_link_type = dl.dedup_link_type

and cm.dedup_link_id = dl.dedup_link_id

and dl.dedup_link_status = 'A'

and cm.dedup_link_type = 'DQ_IDENTITY'

and cm.master_flag = 'Y'

and cm.effective_start_date <= trunc(SYSDATE + 1)

and (cm.effective_end_date >= trunc(SYSDATE) or cm.effective_end_date is null)

and cm.restore_flag = 'N'

and cm.record_id = cp.party_id

and dm.data_set_uuid = cm.data_set_uuid

and dm.dedup_link_type = cm.dedup_link_type

and dm.dedup_link_id = cm.dedup_link_id

and dm.master_flag = 'N'

and dm.effective_start_date <= trunc(SYSDATE + 1)

and (dm.effective_end_date >= trunc(SYSDATE) or dm.effective_end_date is null)

and dm.restore_flag = 'N'

and dm.record_id = dp.party_id

and dp.status = 'A'

union all