View: IGF_AP_EFC_DET_V

View Text

SELECT FA.ROWID
, PE.PARTY_NUMBER
, FA.CI_CAL_TYPE
, FA.CI_SEQUENCE_NUMBER
, CA.ALTERNATE_CODE CI_ALTERNATE_CODE
, CA.START_DT START_DT
, CA.END_DT END_DT
, PIT.API_PERSON_ID SSN
, PE.PERSON_LAST_NAME||'
, '||PE.PERSON_FIRST_NAME FULL_NAME
, NULL
, NULL
, NULL
, NULL
, NULL
, DECODE(SYSTEM_RECORD_TYPE
, 'CORRECTION'
, ISIR.ORIGINAL_SSN||ISIR.ORIG_NAME_ID||ISIR.TRANSACTION_NUM||'C'
, ISIR.ORIGINAL_SSN||ISIR.ORIG_NAME_ID||ISIR.TRANSACTION_NUM )
, FA.BASE_ID
, FA.PERSON_ID
, SUBSTR(IGS_EN_GEN_003.ENRP_GET_ENCMBRD_IND (PE.PARTY_ID)
, 1
, 1) ENCUMBERED_IND
, HZP.DECEASED_IND
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, FA.CREATED_BY
, FA.CREATION_DATE
, FA.LAST_UPDATED_BY
, FA.LAST_UPDATE_DATE
, FA.LAST_UPDATE_LOGIN
FROM IGF_AP_FA_BASE_REC FA
, IGF_AP_ISIR_MATCHED ISIR
, HZ_PARTIES PE
, IGS_CA_INST CA
, (SELECT API_PERSON_ID
, PE_PERSON_ID
FROM IGS_PE_PERSON_ID_TYP PIT_2
, IGS_PE_ALT_PERS_ID API_2
WHERE API_2.PERSON_ID_TYPE = PIT_2.PERSON_ID_TYPE
AND PIT_2.S_PERSON_ID_TYPE = 'SSN'
AND SYSDATE BETWEEN API_2.START_DT
AND NVL(API_2.END_DT
, SYSDATE)) PIT
, IGS_PE_HZ_PARTIES HZP
WHERE NVL(FA.ORG_ID
, NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 1)
, ' '
, NULL
, SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 10)))
, -99)) = NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 1)
, ' '
, NULL
, SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 10)))
, -99)
AND FA.PERSON_ID = PE.PARTY_ID
AND FA.CI_CAL_TYPE = CA.CAL_TYPE AND FA.CI_SEQUENCE_NUMBER = CA.SEQUENCE_NUMBER
AND FA.BASE_ID=ISIR.BASE_ID(+) AND ISIR.ACTIVE_ISIR='Y'
AND PE.PARTY_ID =PIT.PE_PERSON_ID (+)
AND HZP.PARTY_ID = PE.PARTY_ID

Columns