PSC_BL_CURRENT_CONTACT_V

Details

• Schema: FUSION

• Object owner: PSC_BL

• Object type: VIEW

Columns

Name
LNP_RECORD_KEY LNP_RECORD_ID ATTRIBUTE_CATEGORY SUBCLASSIFICATION LEVEL1_TRANS LEVEL2_TRANS TRANS_LEVEL CONTACT_TYPE PRIMARY_FLAG APPLICANT_FLAG OWNER_FLAG CONTACT_ACCESS PARTY_ID EMAIL_ADDRESS1 BUSINESS_ENTITY_KEY CONTACT_KEY

Query

SQL_Statement

SELECT ACT.LNP_RECORD_KEY, ACT.LNP_RECORD_ID, ACT.ATTRIBUTE_CATEGORY, TYP.SUBCLASSIFICATION, LEVEL1_TRANS, LEVEL2_TRANS, NVL(LEVEL1_TRANS, 0) || '.' || NVL(LEVEL2_TRANS, 0) AS TRANS_LEVEL, CNT.CONTACT_TYPE, CNT.PRIMARY_FLAG, CNT.APPLICANT_FLAG, CNT.OWNER_FLAG, CNT.CONTACT_ACCESS, PRT.PARTY_ID, CNT.EMAIL_ADDRESS1, ACT.BUSINESS_ENTITY_KEY, CNT.CONTACT_KEY FROM PSC_BL_RECORD ACT, PSC_LNP_CONTACT_LIST CNT, HZ_PARTIES PRT, PSC_LNP_RECORD_TYPE_B TYP WHERE ACT.CLASSIFICATION = 'LIC' AND TYP.RECORD_TYPE_ID = ACT.ATTRIBUTE_CATEGORY AND CNT.LNP_RECORD_KEY = ACT.LNP_RECORD_KEY AND CNT.CONTACT_ACTIVE = 'Y' AND ((UPPER(PRT.EMAIL_ADDRESS) = UPPER(CNT.EMAIL_ADDRESS1) AND CNT.APPLICANT_FLAG = 'N') OR (CNT.APPLICANT_FLAG = 'Y' AND CNT.CONTACT_PARTY_ID = PRT.PARTY_ID)) AND ( ( TYP.SUBCLASSIFICATION IN ('ORA_NA','ORA_CNTR') AND ACT.SYSTEM_STATUS NOT IN ( 'PND', 'VOD', 'WTH', 'DNI' ) AND TO_NUMBER(NVL(LEVEL1_TRANS, 0) || '.' || NVL(LEVEL2_TRANS, 0)) = (SELECT MAX(TO_NUMBER(NVL(LEVEL1_TRANS, 0) || '.' || NVL(LEVEL2_TRANS, 0))) FROM PSC_BL_RECORD L_ACT WHERE L_ACT.LNP_RECORD_ID = ACT.LNP_RECORD_ID AND L_ACT.CLASSIFICATION = 'LIC' AND L_ACT.ATTRIBUTE_CATEGORY = ACT.ATTRIBUTE_CATEGORY AND L_ACT.SYSTEM_STATUS NOT IN ( 'PND', 'VOD', 'WTH', 'DNI' ) ) ) OR ( TYP.SUBCLASSIFICATION = 'ORA_CON' AND SYSTEM_STATUS NOT IN ( 'PND', 'VOD', 'WTH' ) ) )