VIEW: APPS.IEX_LM_CKEY_ACCT_V

Source

View Text - Preformatted

SELECT acct.account_number ACCOUNT_NUMBER, acct.account_name ACCOUNT_NAME, to_char(acct.cust_account_id) cust_account_id , o.party_id o_party_id , 0 person_id , 0 relationship_id , o.party_name o_party_name , alk.meaning o_party_type , alk.lookup_code o_party_type_code , null role_type , null meaning , null r_party_id , null r_party_name , null r_party_type , o.status org_status , acct.status acct_status , null per_status from hz_cust_accounts acct, hz_parties o, ar_lookups alk WHERE acct.party_id = o.party_id and alk.lookup_type='PARTY_TYPE' and alk.lookup_code=o.party_type and acct.status(+)='A' UNION SELECT acct.account_number ACCOUNT_NUMBER, acct.account_name ACCOUNT_NAME, to_char(acct.cust_account_id) CUST_ACCOUNT_ID, o.party_id o_party_id , p.party_id person_id , relate.party_id relationship_id , o.party_name o_party_name , decode(relate.party_id,null,alk.meaning, (select meaning from ar_lookups where lookup_type='PARTY_TYPE' and lookup_code='PARTY_RELATIONSHIP' and enabled_flag='Y')) o_party_type , decode(relate.party_id,null,alk.lookup_code, (select lookup_code from ar_lookups where lookup_type='PARTY_TYPE' and lookup_code='PARTY_RELATIONSHIP' and enabled_flag='Y')) o_party_type_code , roles.role_type role_type, role_type.meaning meaning, to_char(p.party_id) r_party_id , p.party_name r_party_name , p.party_type r_party_type , o.status org_status , acct.status acct_status , p.status per_status from HZ_PARTIES p, HZ_RELATIONSHIPS relate, HZ_CUST_ACCOUNT_ROLES roles, HZ_CUST_ACCOUNTS acct, AR_LOOKUPS role_type, AR_LOOKUPS alk, hz_parties o WHERE relate.subject_id = p.party_id and roles.party_id = relate.party_id and roles.cust_account_id = acct.cust_account_id AND roles.role_type = role_type.lookup_code (+) AND role_type.lookup_type(+) = 'ACCT_ROLE_TYPE' and alk.lookup_type='PARTY_TYPE' and alk.lookup_code=o.party_type and alk.enabled_flag='Y' and relate.object_id = o.party_id and o.party_id = acct.party_id and acct.status(+)='A' and roles.current_role_state <> 'D'

View Text - HTML Formatted

SELECT ACCT.ACCOUNT_NUMBER ACCOUNT_NUMBER
, ACCT.ACCOUNT_NAME ACCOUNT_NAME
, TO_CHAR(ACCT.CUST_ACCOUNT_ID) CUST_ACCOUNT_ID
, O.PARTY_ID O_PARTY_ID
, 0 PERSON_ID
, 0 RELATIONSHIP_ID
, O.PARTY_NAME O_PARTY_NAME
, ALK.MEANING O_PARTY_TYPE
, ALK.LOOKUP_CODE O_PARTY_TYPE_CODE
, NULL ROLE_TYPE
, NULL MEANING
, NULL R_PARTY_ID
, NULL R_PARTY_NAME
, NULL R_PARTY_TYPE
, O.STATUS ORG_STATUS
, ACCT.STATUS ACCT_STATUS
, NULL PER_STATUS
FROM HZ_CUST_ACCOUNTS ACCT
, HZ_PARTIES O
, AR_LOOKUPS ALK
WHERE ACCT.PARTY_ID = O.PARTY_ID
AND ALK.LOOKUP_TYPE='PARTY_TYPE'
AND ALK.LOOKUP_CODE=O.PARTY_TYPE
AND ACCT.STATUS(+)='A' UNION SELECT ACCT.ACCOUNT_NUMBER ACCOUNT_NUMBER
, ACCT.ACCOUNT_NAME ACCOUNT_NAME
, TO_CHAR(ACCT.CUST_ACCOUNT_ID) CUST_ACCOUNT_ID
, O.PARTY_ID O_PARTY_ID
, P.PARTY_ID PERSON_ID
, RELATE.PARTY_ID RELATIONSHIP_ID
, O.PARTY_NAME O_PARTY_NAME
, DECODE(RELATE.PARTY_ID
, NULL
, ALK.MEANING
, (SELECT MEANING
FROM AR_LOOKUPS
WHERE LOOKUP_TYPE='PARTY_TYPE'
AND LOOKUP_CODE='PARTY_RELATIONSHIP'
AND ENABLED_FLAG='Y')) O_PARTY_TYPE
, DECODE(RELATE.PARTY_ID
, NULL
, ALK.LOOKUP_CODE
, (SELECT LOOKUP_CODE
FROM AR_LOOKUPS
WHERE LOOKUP_TYPE='PARTY_TYPE'
AND LOOKUP_CODE='PARTY_RELATIONSHIP'
AND ENABLED_FLAG='Y')) O_PARTY_TYPE_CODE
, ROLES.ROLE_TYPE ROLE_TYPE
, ROLE_TYPE.MEANING MEANING
, TO_CHAR(P.PARTY_ID) R_PARTY_ID
, P.PARTY_NAME R_PARTY_NAME
, P.PARTY_TYPE R_PARTY_TYPE
, O.STATUS ORG_STATUS
, ACCT.STATUS ACCT_STATUS
, P.STATUS PER_STATUS
FROM HZ_PARTIES P
, HZ_RELATIONSHIPS RELATE
, HZ_CUST_ACCOUNT_ROLES ROLES
, HZ_CUST_ACCOUNTS ACCT
, AR_LOOKUPS ROLE_TYPE
, AR_LOOKUPS ALK
, HZ_PARTIES O
WHERE RELATE.SUBJECT_ID = P.PARTY_ID
AND ROLES.PARTY_ID = RELATE.PARTY_ID
AND ROLES.CUST_ACCOUNT_ID = ACCT.CUST_ACCOUNT_ID
AND ROLES.ROLE_TYPE = ROLE_TYPE.LOOKUP_CODE (+)
AND ROLE_TYPE.LOOKUP_TYPE(+) = 'ACCT_ROLE_TYPE'
AND ALK.LOOKUP_TYPE='PARTY_TYPE'
AND ALK.LOOKUP_CODE=O.PARTY_TYPE
AND ALK.ENABLED_FLAG='Y'
AND RELATE.OBJECT_ID = O.PARTY_ID
AND O.PARTY_ID = ACCT.PARTY_ID
AND ACCT.STATUS(+)='A'
AND ROLES.CURRENT_ROLE_STATE <> 'D'