ZCA_BI_ACCOUNT_HIERARCHIES

Details

Schema: FUSION
Object owner: ZCA
Object type: VIEW

Columns

Name
TREE_STRUCTURE_CODE TREE_CODE TREE_VERSION_ID CF_TREE_NODE_ID DISTANCE CREATION_DATE LAST_UPDATE_DATE EFFECTIVE_START_DATE EFFECTIVE_END_DATE DEP0_PK1_VALUE DEP1_PK1_VALUE DEP2_PK1_VALUE DEP3_PK1_VALUE DEP4_PK1_VALUE DEP5_PK1_VALUE DEP6_PK1_VALUE DEP7_PK1_VALUE DEP8_PK1_VALUE DEP9_PK1_VALUE DEP9_PARTY_ID DEP9_PARTY_NAME DEP9_PARTY_UNIQUE_NAME

Name

TREE_STRUCTURE_CODE

TREE_CODE

TREE_VERSION_ID

CF_TREE_NODE_ID

DISTANCE

CREATION_DATE

LAST_UPDATE_DATE

EFFECTIVE_START_DATE

EFFECTIVE_END_DATE

DEP0_PK1_VALUE

DEP1_PK1_VALUE

DEP2_PK1_VALUE

DEP3_PK1_VALUE

DEP4_PK1_VALUE

DEP5_PK1_VALUE

DEP6_PK1_VALUE

DEP7_PK1_VALUE

DEP8_PK1_VALUE

DEP9_PK1_VALUE

DEP9_PARTY_ID

DEP9_PARTY_NAME

DEP9_PARTY_UNIQUE_NAME

Query

SQL_Statement
SELECT /+ qb_name(custDep0) / 'HZ_CUSTOMER_HIERARCHY' TREE_STRUCTURE_CODE, 'STANDALONE' TREE_CODE, '0' TREE_VERSION_ID, to_char(PARTY_ID) cf_tree_node_id, 0 DISTANCE, CAST(NULL AS DATE) CREATION_DATE, CAST(NULL AS DATE) LAST_UPDATE_DATE, CAST(NULL AS DATE) EFFECTIVE_START_DATE, CAST(NULL AS DATE) EFFECTIVE_END_DATE, PARTY_ID DEP0_PK1_VALUE, TO_CHAR(PARTY_ID) DEP1_PK1_VALUE, TO_CHAR(PARTY_ID) DEP2_PK1_VALUE, TO_CHAR(PARTY_ID) DEP3_PK1_VALUE, TO_CHAR(PARTY_ID) DEP4_PK1_VALUE, TO_CHAR(PARTY_ID) DEP5_PK1_VALUE, TO_CHAR(PARTY_ID) DEP6_PK1_VALUE, TO_CHAR(PARTY_ID) DEP7_PK1_VALUE, TO_CHAR(PARTY_ID) DEP8_PK1_VALUE, TO_CHAR(PARTY_ID) DEP9_PK1_VALUE, PARTY_ID Dep9_Party_Id, PARTY_NAME Dep9_Party_Name, PARTY_UNIQUE_NAME Dep9_Party_Unique_Name FROM HZ_PARTIES AA WHERE NOT EXISTS (SELECT /+ qb_name(custDepNotIn) / 1 FROM FND_TREE_NODE_CF BB, FND_TREE_VERSION CC WHERE BB.TREE_STRUCTURE_CODE = 'HZ_CUSTOMER_HIERARCHY' AND BB.TREE_STRUCTURE_CODE = CC.TREE_STRUCTURE_CODE AND BB.TREE_CODE = CC.TREE_CODE AND BB.TREE_VERSION_ID = CC.TREE_VERSION_ID AND (trunc(SYSDATE) BETWEEN CC.EFFECTIVE_START_DATE AND CC.EFFECTIVE_END_DATE) AND CC.STATUS = 'ACTIVE' AND BB.DEP0_PK1_VALUE = TO_CHAR(AA.PARTY_ID) ) UNION ALL SELECT /+ qb_name(custDepAll) / CFT.TREE_STRUCTURE_CODE, CFT.TREE_CODE, CFT.TREE_VERSION_ID, CFT.CF_TREE_NODE_ID, CFT.DISTANCE, CFT.CREATION_DATE, CFT.LAST_UPDATE_DATE, TREEVER.EFFECTIVE_START_DATE, TREEVER.EFFECTIVE_END_DATE, DSDEP0.PARTY_ID DEP0_PK1_VALUE, CFT.DEP1_PK1_VALUE DEP1_PK1_VALUE, CFT.DEP2_PK1_VALUE DEP2_PK1_VALUE, CFT.DEP3_PK1_VALUE DEP3_PK1_VALUE, CFT.DEP4_PK1_VALUE DEP4_PK1_VALUE, CFT.DEP5_PK1_VALUE DEP5_PK1_VALUE, CFT.DEP6_PK1_VALUE DEP6_PK1_VALUE, CFT.DEP7_PK1_VALUE DEP7_PK1_VALUE, CFT.DEP8_PK1_VALUE DEP8_PK1_VALUE, CFT.DEP9_PK1_VALUE DEP9_PK1_VALUE, DSDEP9.PARTY_ID Dep9_Party_Id, DSDEP9.PARTY_NAME Dep9_Party_Name, DSDEP9.PARTY_UNIQUE_NAME Dep9_Party_Unique_Name FROM FND_TREE_NODE_CF CFT, HZ_PARTIES DSDEP0, HZ_PARTIES DSDEP9, FND_TREE_VERSION TREEVER WHERE CFT.TREE_STRUCTURE_CODE = 'HZ_CUSTOMER_HIERARCHY' AND CFT.DEP0_PK1_VALUE = TO_CHAR(DSDEP0.PARTY_ID) AND DSDEP0.PARTY_TYPE = 'ORGANIZATION' AND CFT.DEP9_PK1_VALUE = TO_CHAR(DSDEP9.PARTY_ID) AND DSDEP9.PARTY_TYPE = 'ORGANIZATION' AND CFT.TREE_STRUCTURE_CODE = TREEVER.TREE_STRUCTURE_CODE AND CFT.TREE_CODE = TREEVER.TREE_CODE AND CFT.TREE_VERSION_ID = TREEVER.TREE_VERSION_ID AND ( trunc(SYSDATE) BETWEEN TREEVER.EFFECTIVE_START_DATE AND TREEVER.EFFECTIVE_END_DATE) AND TREEVER.STATUS = 'ACTIVE'

SQL_Statement

SELECT /*+ qb_name(custDep0) */

'HZ_CUSTOMER_HIERARCHY' TREE_STRUCTURE_CODE,

'STANDALONE' TREE_CODE,

'0' TREE_VERSION_ID,

to_char(PARTY_ID) cf_tree_node_id,

0 DISTANCE,

CAST(NULL AS DATE) CREATION_DATE,

CAST(NULL AS DATE) LAST_UPDATE_DATE,

CAST(NULL AS DATE) EFFECTIVE_START_DATE,

CAST(NULL AS DATE) EFFECTIVE_END_DATE,

PARTY_ID DEP0_PK1_VALUE,

TO_CHAR(PARTY_ID) DEP1_PK1_VALUE,

TO_CHAR(PARTY_ID) DEP2_PK1_VALUE,

TO_CHAR(PARTY_ID) DEP3_PK1_VALUE,

TO_CHAR(PARTY_ID) DEP4_PK1_VALUE,

TO_CHAR(PARTY_ID) DEP5_PK1_VALUE,

TO_CHAR(PARTY_ID) DEP6_PK1_VALUE,

TO_CHAR(PARTY_ID) DEP7_PK1_VALUE,

TO_CHAR(PARTY_ID) DEP8_PK1_VALUE,

TO_CHAR(PARTY_ID) DEP9_PK1_VALUE,

PARTY_ID Dep9_Party_Id, PARTY_NAME Dep9_Party_Name, PARTY_UNIQUE_NAME Dep9_Party_Unique_Name

FROM HZ_PARTIES AA

WHERE NOT EXISTS

(SELECT /*+ qb_name(custDepNotIn) */

1

FROM FND_TREE_NODE_CF BB,

FND_TREE_VERSION CC

WHERE BB.TREE_STRUCTURE_CODE = 'HZ_CUSTOMER_HIERARCHY'

AND BB.TREE_STRUCTURE_CODE = CC.TREE_STRUCTURE_CODE

AND BB.TREE_CODE = CC.TREE_CODE

AND BB.TREE_VERSION_ID = CC.TREE_VERSION_ID

AND (trunc(SYSDATE) BETWEEN CC.EFFECTIVE_START_DATE AND CC.EFFECTIVE_END_DATE)

AND CC.STATUS = 'ACTIVE'

AND BB.DEP0_PK1_VALUE = TO_CHAR(AA.PARTY_ID)

)

UNION ALL

SELECT /*+ qb_name(custDepAll) */

CFT.TREE_STRUCTURE_CODE,

CFT.TREE_CODE,

CFT.TREE_VERSION_ID,

CFT.CF_TREE_NODE_ID,

CFT.DISTANCE,

CFT.CREATION_DATE,

CFT.LAST_UPDATE_DATE,

TREEVER.EFFECTIVE_START_DATE,

TREEVER.EFFECTIVE_END_DATE,

DSDEP0.PARTY_ID DEP0_PK1_VALUE,

CFT.DEP1_PK1_VALUE DEP1_PK1_VALUE,

CFT.DEP2_PK1_VALUE DEP2_PK1_VALUE,

CFT.DEP3_PK1_VALUE DEP3_PK1_VALUE,

CFT.DEP4_PK1_VALUE DEP4_PK1_VALUE,

CFT.DEP5_PK1_VALUE DEP5_PK1_VALUE,

CFT.DEP6_PK1_VALUE DEP6_PK1_VALUE,

CFT.DEP7_PK1_VALUE DEP7_PK1_VALUE,

CFT.DEP8_PK1_VALUE DEP8_PK1_VALUE,

CFT.DEP9_PK1_VALUE DEP9_PK1_VALUE,

DSDEP9.PARTY_ID Dep9_Party_Id, DSDEP9.PARTY_NAME Dep9_Party_Name, DSDEP9.PARTY_UNIQUE_NAME Dep9_Party_Unique_Name

FROM FND_TREE_NODE_CF CFT,

HZ_PARTIES DSDEP0,

HZ_PARTIES DSDEP9,

FND_TREE_VERSION TREEVER

WHERE

CFT.TREE_STRUCTURE_CODE = 'HZ_CUSTOMER_HIERARCHY'

AND CFT.DEP0_PK1_VALUE = TO_CHAR(DSDEP0.PARTY_ID)

AND DSDEP0.PARTY_TYPE = 'ORGANIZATION'

AND CFT.DEP9_PK1_VALUE = TO_CHAR(DSDEP9.PARTY_ID)

AND DSDEP9.PARTY_TYPE = 'ORGANIZATION'

AND CFT.TREE_STRUCTURE_CODE = TREEVER.TREE_STRUCTURE_CODE

AND CFT.TREE_CODE = TREEVER.TREE_CODE

AND CFT.TREE_VERSION_ID = TREEVER.TREE_VERSION_ID

AND ( trunc(SYSDATE) BETWEEN TREEVER.EFFECTIVE_START_DATE AND TREEVER.EFFECTIVE_END_DATE)

AND TREEVER.STATUS = 'ACTIVE'