View: AST_LM_CKEY_ACCT_V

Product:	AST - TeleSales
Description:
Implementation/DBA Data:	APPS.AST_LM_CKEY_ACCT_V

View Text

SELECT ACCT.ACCOUNT_NUMBER
, ACCT.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
, ACCT.ACCOUNT_NAME
, TO_CHAR(ACCT.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.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'

Columns