ZCH_LINKED_PARTIES

Details

Schema: FUSION
Object owner: ZCH
Object type: VIEW

Columns

Name
DATA_SET_ID DATA_SET_UUID LINK_TYPE LINK_TYPE_ID LINK_ID LINK_NUMBER MAIN_LINK_FLAG CONTEXT_PARTY_ID CONTEXT_PARTY_NUMBER LINKED_OBJECT_CODE LINKED_OBJECT_ID LINKED_OBJECT_USAGES LINKED_PARTY_ID LINKED_PARTY_NUMBER LINKED_PARTY_NAME LINKED_PARTY_TYPE CREATION_DATE CREATED_BY LAST_UPDATE_DATE LAST_UPDATED_BY LAST_UPDATE_LOGIN OBJECT_VERSION_NUMBER

Name

DATA_SET_ID

DATA_SET_UUID

LINK_TYPE

LINK_TYPE_ID

LINK_ID

LINK_NUMBER

MAIN_LINK_FLAG

CONTEXT_PARTY_ID

CONTEXT_PARTY_NUMBER

LINKED_OBJECT_CODE

LINKED_OBJECT_ID

LINKED_OBJECT_USAGES

LINKED_PARTY_ID

LINKED_PARTY_NUMBER

LINKED_PARTY_NAME

LINKED_PARTY_TYPE

CREATION_DATE

CREATED_BY

LAST_UPDATE_DATE

LAST_UPDATED_BY

LAST_UPDATE_LOGIN

OBJECT_VERSION_NUMBER

Query

SQL_Statement
select case lm.data_set_uuid when '47343de0-77e4-36b5-9697-dd51a77826c2' then 1 when '8e8ffdf7-0e40-34b3-a1f7-9179c9215f21' then 2 end data_set_id, lm.data_set_uuid, lm.dedup_link_type link_type, case lm.dedup_link_type when 'B2F_EQUIV' then 1 when 'DQ_IDENTITY' then 2 when 'ORA_ORG_LINK' then 3 end link_type_id, lm.dedup_link_id link_id, dl.dedup_link_number link_number, case lm.dedup_link_type when 'ORA_ORG_LINK' then null else lm.master_flag end main_link_flag, cp.party_id context_party_id, cp.party_number context_party_number, lm.object_code linked_object_code, case lm.object_code when 'Customer' then 1 when 'Partner' then 2 when 'Supplier' then 3 else -1 end linked_object_id, lm.object_usages linked_object_usages, lm.record_id linked_party_id, lp.party_number linked_party_number, lp.party_name linked_party_name, lp.party_type linked_party_type, lm.creation_date, lm.created_by, lm.last_update_date, lm.last_updated_by, lm.last_update_login, lm.object_version_number from zch_dedup_links_b dl, zch_dedup_link_members lm, hz_parties cp, hz_parties lp where exists ( select 1 from zch_dedup_link_members cm 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 (cm.dedup_link_type = 'ORA_ORG_LINK' or 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 dl.dedup_link_status = 'A' and lm.data_set_uuid = dl.data_set_uuid and lm.dedup_link_type = dl.dedup_link_type and lm.dedup_link_id = dl.dedup_link_id and (lm.dedup_link_type = 'ORA_ORG_LINK' or lm.master_flag = 'N') and lm.effective_start_date <= trunc(SYSDATE + 1) and (lm.effective_end_date >= trunc(SYSDATE) or lm.effective_end_date is null) and lm.restore_flag = 'N' and lm.record_id = lp.party_id and lp.party_id != cp.party_id and lp.status = 'A' union all select case lm.data_set_uuid when '47343de0-77e4-36b5-9697-dd51a77826c2' then 1 when '8e8ffdf7-0e40-34b3-a1f7-9179c9215f21' then 2 end data_set_id, lm.data_set_uuid, lm.dedup_link_type link_type, case lm.dedup_link_type when 'B2F_EQUIV' then 1 when 'DQ_IDENTITY' then 2 when 'ORA_ORG_LINK' then 3 end link_type_id, lm.dedup_link_id link_id, dl.dedup_link_number link_number, case lm.dedup_link_type when 'ORA_ORG_LINK' then null else lm.master_flag end main_link_flag, cp.party_id context_party_id, cp.party_number context_party_number, lm.object_code linked_object_code, case lm.object_code when 'Customer' then 1 when 'Partner' then 2 when 'Supplier' then 3 else -1 end linked_object_id, lm.object_usages linked_object_usages, lm.record_id linked_party_id, lp.party_number linked_party_number, lp.party_name linked_party_name, lp.party_type linked_party_type, lm.creation_date, lm.created_by, lm.last_update_date, lm.last_updated_by, lm.last_update_login, lm.object_version_number from zch_dedup_links_b dl, zch_dedup_link_members lm, hz_parties cp, hz_parties lp where exists ( select 1 from zch_dedup_link_members cm 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 (cm.dedup_link_type != 'ORA_ORG_LINK' 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 dl.dedup_link_status = 'A' and lm.data_set_uuid = dl.data_set_uuid and lm.dedup_link_type = dl.dedup_link_type and lm.dedup_link_id = dl.dedup_link_id and (lm.dedup_link_type != 'ORA_ORG_LINK' and lm.master_flag = 'Y') and lm.effective_start_date <= trunc(SYSDATE + 1) and (lm.effective_end_date >= trunc(SYSDATE) or lm.effective_end_date is null) and lm.restore_flag = 'N' and lm.record_id = lp.party_id and lp.party_id != cp.party_id and lp.status = 'A'

SQL_Statement

select

case lm.data_set_uuid

when '47343de0-77e4-36b5-9697-dd51a77826c2' then 1

when '8e8ffdf7-0e40-34b3-a1f7-9179c9215f21' then 2

end data_set_id,

lm.data_set_uuid,

lm.dedup_link_type link_type,

case lm.dedup_link_type

when 'B2F_EQUIV' then 1

when 'DQ_IDENTITY' then 2

when 'ORA_ORG_LINK' then 3

end link_type_id,

lm.dedup_link_id link_id,

dl.dedup_link_number link_number,

case lm.dedup_link_type

when 'ORA_ORG_LINK' then null

else lm.master_flag

end main_link_flag,

cp.party_id context_party_id,

cp.party_number context_party_number,

lm.object_code linked_object_code,

case lm.object_code

when 'Customer' then 1

when 'Partner' then 2

when 'Supplier' then 3

else -1

end linked_object_id,

lm.object_usages linked_object_usages,

lm.record_id linked_party_id,

lp.party_number linked_party_number,

lp.party_name linked_party_name,

lp.party_type linked_party_type,

lm.creation_date,

lm.created_by,

lm.last_update_date,

lm.last_updated_by,

lm.last_update_login,

lm.object_version_number

from

zch_dedup_links_b dl,

zch_dedup_link_members lm,

hz_parties cp,

hz_parties lp

where exists (

select 1 from zch_dedup_link_members cm

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 (cm.dedup_link_type = 'ORA_ORG_LINK' or 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 dl.dedup_link_status = 'A'

and lm.data_set_uuid = dl.data_set_uuid

and lm.dedup_link_type = dl.dedup_link_type

and lm.dedup_link_id = dl.dedup_link_id

and (lm.dedup_link_type = 'ORA_ORG_LINK' or lm.master_flag = 'N')

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

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

and lm.restore_flag = 'N'

and lm.record_id = lp.party_id

and lp.party_id != cp.party_id

and lp.status = 'A'

union all

select

case lm.data_set_uuid

when '47343de0-77e4-36b5-9697-dd51a77826c2' then 1

when '8e8ffdf7-0e40-34b3-a1f7-9179c9215f21' then 2

end data_set_id,

lm.data_set_uuid,

lm.dedup_link_type link_type,

case lm.dedup_link_type

when 'B2F_EQUIV' then 1

when 'DQ_IDENTITY' then 2

when 'ORA_ORG_LINK' then 3

end link_type_id,

lm.dedup_link_id link_id,

dl.dedup_link_number link_number,

case lm.dedup_link_type

when 'ORA_ORG_LINK' then null

else lm.master_flag

end main_link_flag,

cp.party_id context_party_id,

cp.party_number context_party_number,

lm.object_code linked_object_code,

case lm.object_code

when 'Customer' then 1

when 'Partner' then 2

when 'Supplier' then 3

else -1

end linked_object_id,

lm.object_usages linked_object_usages,

lm.record_id linked_party_id,

lp.party_number linked_party_number,

lp.party_name linked_party_name,

lp.party_type linked_party_type,

lm.creation_date,

lm.created_by,

lm.last_update_date,

lm.last_updated_by,

lm.last_update_login,

lm.object_version_number

from

zch_dedup_links_b dl,

zch_dedup_link_members lm,

hz_parties cp,

hz_parties lp

where exists (

select 1 from zch_dedup_link_members cm

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 (cm.dedup_link_type != 'ORA_ORG_LINK' 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 dl.dedup_link_status = 'A'

and lm.data_set_uuid = dl.data_set_uuid

and lm.dedup_link_type = dl.dedup_link_type

and lm.dedup_link_id = dl.dedup_link_id

and (lm.dedup_link_type != 'ORA_ORG_LINK' and lm.master_flag = 'Y')

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

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

and lm.restore_flag = 'N'

and lm.record_id = lp.party_id

and lp.party_id != cp.party_id

and lp.status = 'A'