VIEW: APPS.IGF_AP_EFC_DET_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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