VIEW: APPS.IGF_AW_AWD_LTR_V

Source

View Text - Preformatted

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(fcon.ci_cal_type, fcon.ci_sequence_number) award_year, lt.ci_cal_type, lt.ci_sequence_number, 'AW/' || pit.api_person_id || TO_CHAR (SYSDATE, 'mm/dd/yyyy') letter_id, lt.award_name award_name, lt.line_id line_id, lt.term_amount_text term_amount_text, igf_aw_gen_004.get_award_desc ( lt.person_id, lt.ci_cal_type, lt.ci_sequence_number ) award_description, lt.fund_description award_message, fcon.coa_f coa, igf_aw_gen_004.efc_f(fcon.base_id) efc_fm, igf_aw_gen_004.efc_i(fcon.base_id) efc_im, igf_aw_gen_004.need_i(fcon.base_id) need_im, igf_aw_gen_004.need_f(fcon.base_id) need_fm, igf_aw_gen_004.unmetneed_i(fcon.base_id) remain_im, igf_aw_gen_004.unmetneed_f(fcon.base_id) remain_fm, acad.institution_name institution_name, res.residency_state FROM hz_parties hz, 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, ( SELECT EIT.PEI_INFORMATION1 RESIDENCY_STATE, EIT.PERSON_ID PERSON_ID FROM IGS_PE_EIT EIT WHERE EIT.INFORMATION_TYPE = 'PE_STAT_RES_STATE' AND SYSDATE BETWEEN EIT.START_DATE AND NVL(EIT.END_DATE,SYSDATE) ) RES, igf_ap_fa_base_rec_all fcon, igf_aw_awd_ltr_tmp lt, igf_aw_fund_cat_all fcat, igs_ad_acad_history_v acad, igf_aw_fund_mast_all fmast WHERE lt.fund_code = fmast.fund_code(+) AND lt.ci_cal_type = fmast.ci_cal_type(+) AND lt.ci_sequence_number = fmast.ci_sequence_number(+) AND lt.person_id = fcon.person_id AND lt.ci_cal_type = fcon.ci_cal_type AND lt.ci_sequence_number = fcon.ci_sequence_number AND lt.person_id = res.person_id(+) AND lt.person_id = acad.person_id(+) AND acad.current_inst (+) = 'Y' AND fcon.person_id = hz.party_id AND fcon.person_id = pe.party_id AND pe.party_id = pit.pe_person_id(+) AND lt.fund_code = fcat.fund_code (+) AND NVL(pe.deceased_ind, 'N') <> 'Y'

View Text - HTML Formatted

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(FCON.CI_CAL_TYPE
, FCON.CI_SEQUENCE_NUMBER) AWARD_YEAR
, LT.CI_CAL_TYPE
, LT.CI_SEQUENCE_NUMBER
, 'AW/' || PIT.API_PERSON_ID || TO_CHAR (SYSDATE
, 'MM/DD/YYYY') LETTER_ID
, LT.AWARD_NAME AWARD_NAME
, LT.LINE_ID LINE_ID
, LT.TERM_AMOUNT_TEXT TERM_AMOUNT_TEXT
, IGF_AW_GEN_004.GET_AWARD_DESC ( LT.PERSON_ID
, LT.CI_CAL_TYPE
, LT.CI_SEQUENCE_NUMBER ) AWARD_DESCRIPTION
, LT.FUND_DESCRIPTION AWARD_MESSAGE
, FCON.COA_F COA
, IGF_AW_GEN_004.EFC_F(FCON.BASE_ID) EFC_FM
, IGF_AW_GEN_004.EFC_I(FCON.BASE_ID) EFC_IM
, IGF_AW_GEN_004.NEED_I(FCON.BASE_ID) NEED_IM
, IGF_AW_GEN_004.NEED_F(FCON.BASE_ID) NEED_FM
, IGF_AW_GEN_004.UNMETNEED_I(FCON.BASE_ID) REMAIN_IM
, IGF_AW_GEN_004.UNMETNEED_F(FCON.BASE_ID) REMAIN_FM
, ACAD.INSTITUTION_NAME INSTITUTION_NAME
, RES.RESIDENCY_STATE
FROM HZ_PARTIES HZ
, 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
, ( SELECT EIT.PEI_INFORMATION1 RESIDENCY_STATE
, EIT.PERSON_ID PERSON_ID
FROM IGS_PE_EIT EIT
WHERE EIT.INFORMATION_TYPE = 'PE_STAT_RES_STATE'
AND SYSDATE BETWEEN EIT.START_DATE
AND NVL(EIT.END_DATE
, SYSDATE) ) RES
, IGF_AP_FA_BASE_REC_ALL FCON
, IGF_AW_AWD_LTR_TMP LT
, IGF_AW_FUND_CAT_ALL FCAT
, IGS_AD_ACAD_HISTORY_V ACAD
, IGF_AW_FUND_MAST_ALL FMAST
WHERE LT.FUND_CODE = FMAST.FUND_CODE(+)
AND LT.CI_CAL_TYPE = FMAST.CI_CAL_TYPE(+)
AND LT.CI_SEQUENCE_NUMBER = FMAST.CI_SEQUENCE_NUMBER(+)
AND LT.PERSON_ID = FCON.PERSON_ID
AND LT.CI_CAL_TYPE = FCON.CI_CAL_TYPE
AND LT.CI_SEQUENCE_NUMBER = FCON.CI_SEQUENCE_NUMBER
AND LT.PERSON_ID = RES.PERSON_ID(+)
AND LT.PERSON_ID = ACAD.PERSON_ID(+)
AND ACAD.CURRENT_INST (+) = 'Y'
AND FCON.PERSON_ID = HZ.PARTY_ID
AND FCON.PERSON_ID = PE.PARTY_ID
AND PE.PARTY_ID = PIT.PE_PERSON_ID(+)
AND LT.FUND_CODE = FCAT.FUND_CODE (+)
AND NVL(PE.DECEASED_IND
, 'N') <> 'Y'