View: IGF_AP_MIS_ITMS_LTR_V

Product:	IGF - Financial Aid
Description:	This view is used to return the missing items (missing documents) which are required for Financial Aid processing
Implementation/DBA Data:	APPS.IGF_AP_MIS_ITMS_LTR_V

View Text

SELECT HZ.PARTY_ID PERSON_ID
, HZ.PARTY_NAME PARTY_NAME
, HZ.PARTY_NUMBER PERSON_NUMBER
, HZ.PARTY_TYPE PARTY_TYPE
, HZ.ADDRESS1 ADDRESS_LINE1
, HZ.ADDRESS2 ADDRESS_LINE2
, HZ.ADDRESS3 ADDRESS_LINE3
, HZ.ADDRESS4 ADDRESS_LINE4
, HZ.ANALYSIS_FY ANALYSIS_FY
, HZ.CATEGORY_CODE CATEGORY_CODE
, HZ.CITY CITY
, HZ.COMPETITOR_FLAG COMPETITOR_FLAG
, HZ.COUNTRY COUNTRY
, HZ.COUNTY COUNTY
, IGS_CO_GEN_003.GET_PER_ADDR_FOR_CORR(HZ.PARTY_ID) PERSON_NAME_ADDRESS
, HZ.CURR_FY_POTENTIAL_REVENUE CURR_FY_POTENTIAL_REVENUE
, HZ.CUSTOMER_KEY CUSTOMER_KEY
, HZ.DO_NOT_MAIL_FLAG DO_NOT_MAIL_FLAG
, HZ.DUNS_NUMBER DUNS_NUMBER
, HZ.EMAIL_ADDRESS EMAIL_ADDRESS
, HZ.EMPLOYEES_TOTAL EMPLOYEES_TOTAL
, HZ.FISCAL_YEAREND_MONTH FISCAL_YEAREND_MONTH
, HZ.GROUP_TYPE GROUP_TYPE
, HZ.GSA_INDICATOR_FLAG GSA_INDICATOR_FLAG
, HZ.HQ_BRANCH_IND HQ_BRANCH_IND
, HZ.JGZZ_FISCAL_CODE JGZZ_FISCAL_CODE
, HZ.KNOWN_AS PREFERRED_GIVEN_NAME
, HZ.KNOWN_AS2 KNOWN_AS2
, HZ.KNOWN_AS3 KNOWN_AS3
, HZ.KNOWN_AS4 KNOWN_AS4
, HZ.KNOWN_AS5 KNOWN_AS5
, HZ.LANGUAGE_NAME LANGUAGE_NAME
, HZ.LAST_ORDERED_DATE LAST_ORDERED_DATE
, HZ.MISSION_STATEMENT MISSION_STATEMENT
, HZ.NEXT_FY_POTENTIAL_REVENUE NEXT_FY_POTENTIAL_REVENUE
, HZ.ORGANIZATION_NAME_PHONETIC ORGANIZATION_NAME_PHONETIC
, HZ.ORIG_SYSTEM_REFERENCE ORIG_SYSTEM_REFERENCE
, HZ.PERSON_ACADEMIC_TITLE PERSON_ACADEMIC_TITLE
, HZ.PERSON_FIRST_NAME GIVEN_NAMES
, HZ.PERSON_FIRST_NAME_PHONETIC PERSON_FIRST_NAME_PHONETIC
, HZ.PERSON_IDEN_TYPE PERSON_IDEN_TYPE
, HZ.PERSON_IDENTIFIER PERSON_IDENTIFIER
, HZ.PERSON_LAST_NAME SURNAME
, HZ.PERSON_LAST_NAME_PHONETIC PERSON_LAST_NAME_PHONETIC
, HZ.PERSON_MIDDLE_NAME MIDDLE_NAME
, HZ.PERSON_NAME_SUFFIX SUFFIX
, HZ.PERSON_PRE_NAME_ADJUNCT PREFIX
, HZ.PERSON_PREVIOUS_LAST_NAME PERSON_PREVIOUS_LAST_NAME
, HZ.PERSON_TITLE TITLE
, HZ.POSTAL_CODE POSTAL_CODE
, HZ.PROVINCE PROVINCE
, HZ.REFERENCE_USE_FLAG REFERENCE_USE_FLAG
, HZ.SALUTATION SALUTATION
, HZ.SIC_CODE SIC_CODE
, HZ.SIC_CODE_TYPE SIC_CODE_TYPE
, HZ.STATE STATE
, HZ.STATUS STATUS
, HZ.TAX_NAME TAX_NAME
, HZ.TAX_REFERENCE TAX_REFERENCE
, HZ.THIRD_PARTY_FLAG THIRD_PARTY_FLAG
, HZ.TOTAL_NUM_OF_ORDERS TOTAL_NUM_OF_ORDERS
, HZ.TOTAL_ORDERED_AMOUNT TOTAL_ORDERED_AMOUNT
, HZ.URL URL
, HZ.VALIDATED_FLAG VALIDATED_FLAG
, HZ.WH_UPDATE_DATE WH_UPDATE_DATE
, HZ.YEAR_ESTABLISHED YEAR_ESTABLISHED
, PIT.API_PERSON_ID PERSON_SSN
, TO_CHAR (SYSDATE
, 'MONTHFMDD
, RRRR') TODAYS_DATE
, IGF_GR_GEN.GET_CALENDAR_DESC(FBASE.CI_CAL_TYPE
, FBASE.CI_SEQUENCE_NUMBER) AWARD_YEAR
, 'MI/'||PIT.API_PERSON_ID|| TO_CHAR (SYSDATE
, 'MM/DD/YYYY') LETTER_ID
, TDM.ITEM_CODE ITEM_CODE
, TDM.CORSP_MESG CORSP_MESG
, IGF_AW_GEN_004.GET_CORR_CUST_TEXT(PERSON_ID) CUSTOM_TEXT
, FBASE.CI_CAL_TYPE CI_CAL_TYPE
, FBASE.CI_SEQUENCE_NUMBER CI_SEQUENCE_NUMBER
FROM HZ_PARTIES HZ
, IGF_AP_TD_ITEM_INST_ALL TDII
, IGF_AP_ST_CORR_TEXT CTEXT
, IGS_PE_HZ_PARTIES PE
, ( SELECT API_2.API_PERSON_ID
, API_2.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
, IGF_AP_TD_ITEM_MST_ALL TDM
, IGF_AP_FA_BASE_REC_ALL FBASE
WHERE TDII.STATUS IN ('INC'
, 'REC'
, 'REQ')
AND HZ.PARTY_ID = FBASE.PERSON_ID
AND PE.PARTY_ID = FBASE.PERSON_ID
AND PE.PARTY_ID = PIT.PE_PERSON_ID (+)
AND TDM.TODO_NUMBER = TDII.ITEM_SEQUENCE_NUMBER
AND FBASE.BASE_ID = TDII.BASE_ID
AND FBASE.BASE_ID = CTEXT.BASE_ID(+)
AND NVL (TDII.INACTIVE_FLAG
, 'Y') = 'N'
AND NVL(PE.DECEASED_IND
, 'N') <> 'Y'

Columns