VIEW: APPS.IGF_SL_DISB_LTR_V

Source

View Text - Preformatted

SELECT hz.party_id , hz.party_name , hz.party_number , hz.party_type , hz.address1 , hz.address2 , hz.address3 , hz.address4 , hz.analysis_fy , hz.category_code , hz.city , hz.competitor_flag , hz.country , hz.county , igs_co_gen_003.get_per_addr_for_corr(hz.party_id) , hz.curr_fy_potential_revenue , hz.customer_key , hz.do_not_mail_flag , hz.duns_number , hz.email_address , hz.employees_total , hz.fiscal_yearend_month , hz.group_type , hz.gsa_indicator_flag , hz.hq_branch_ind , hz.jgzz_fiscal_code , hz.known_as , hz.known_as2 , hz.known_as3 , hz.known_as4 , hz.known_as5 , hz.language_name , hz.last_ordered_date , hz.mission_statement , hz.next_fy_potential_revenue , hz.organization_name_phonetic , hz.orig_system_reference , hz.person_academic_title , hz.person_first_name , hz.person_first_name_phonetic , hz.person_iden_type , hz.person_identifier , hz.person_last_name , hz.person_last_name_phonetic , hz.person_middle_name , hz.person_name_suffix , hz.person_pre_name_adjunct , hz.person_previous_last_name , hz.person_title , hz.postal_code , hz.province , hz.reference_use_flag , hz.salutation , hz.sic_code , hz.sic_code_type , hz.state , hz.status , hz.tax_name , hz.tax_reference , hz.third_party_flag , hz.total_num_of_orders , hz.total_ordered_amount , hz.url , hz.validated_flag , hz.wh_update_date , hz.year_established , pit.api_person_id , TO_CHAR (SYSDATE,'MonthFMDD,RRRR') , igf_gr_gen.get_calendar_desc(fbase.ci_cal_type, fbase.ci_sequence_number) award_year , fbase.ci_cal_type , fbase.ci_sequence_number , ci.alternate_code , 'DB/'||pit.api_person_id|| TO_CHAR(SYSDATE,'/mm/dd/yyyy') , fmast.description , SUM(disb.disb_gross_amt) , TO_CHAR(disb.disb_date,'DD-Mon-YYYY') FROM hz_parties hz, igs_pe_hz_parties pe, ( 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, igf_aw_award_all aw, igf_aw_fund_mast_all fmast, igf_aw_fund_cat_all fcat, igf_ap_fa_base_rec_all fbase, igf_db_awd_disb_dtl_all disb, igs_ca_inst_all ci WHERE hz.party_id = pe.party_id AND pe.party_id = pit.pe_person_id (+) AND NVL(pe.deceased_ind, 'N') <> 'Y' AND pe.party_id = fbase.person_id AND fbase.base_id = aw.base_id AND aw.fund_id = fmast.fund_id AND fmast.fund_code = fcat.fund_code AND fcat.sys_fund_type = 'LOAN' AND aw.award_id = disb.award_id AND disb.sf_credit_id IS NOT NULL AND disb.notification_date IS NULL AND ci.cal_type = fbase.ci_cal_type AND ci.sequence_number = fbase.ci_sequence_number GROUP BY hz.party_id , hz.party_name , hz.party_number , hz.party_type , hz.address1 , hz.address2 , hz.address3 , hz.address4, hz.analysis_fy, hz.category_code , hz.city , hz.competitor_flag , hz.country , hz.county , igs_co_gen_003.get_per_addr_for_corr(hz.party_id) , hz.curr_fy_potential_revenue , hz.customer_key , hz.do_not_mail_flag, hz.duns_number , hz.email_address , hz.employees_total , hz.fiscal_yearend_month , hz.group_type , hz.gsa_indicator_flag , hz.hq_branch_ind , hz.jgzz_fiscal_code , hz.known_as , hz.known_as2 , hz.known_as3 , hz.known_as4 , hz.known_as5 , hz.language_name , hz.last_ordered_date , hz.mission_statement , hz.next_fy_potential_revenue , hz.organization_name_phonetic , hz.orig_system_reference , hz.person_academic_title , hz.person_first_name , hz.person_first_name_phonetic , hz.person_iden_type , hz.person_identifier , hz.person_last_name , hz.person_last_name_phonetic , hz.person_middle_name , hz.person_name_suffix , hz.person_pre_name_adjunct , hz.person_previous_last_name , hz.person_title , hz.postal_code , hz.province , hz.reference_use_flag, hz.salutation , hz.sic_code , hz.sic_code_type , hz.state , hz.status , hz.tax_name , hz.tax_reference , hz.third_party_flag , hz.total_num_of_orders , hz.total_ordered_amount , hz.url , hz.validated_flag , hz.wh_update_date , hz.year_established , pit.api_person_id , TO_CHAR (SYSDATE,'monthfmdd,rrrr') , igf_gr_gen.get_calendar_desc(fbase.ci_cal_type, fbase.ci_sequence_number) , fbase.ci_cal_type, fbase.ci_sequence_number, ci.alternate_code, 'DB/'||pit.api_person_id|| TO_CHAR(SYSDATE, '/mm/dd/yyyy') , fmast.description, TO_CHAR(disb.disb_date,'DD-Mon-YYYY'), disb.disb_date

View Text - HTML Formatted

SELECT HZ.PARTY_ID
, HZ.PARTY_NAME
, HZ.PARTY_NUMBER
, HZ.PARTY_TYPE
, HZ.ADDRESS1
, HZ.ADDRESS2
, HZ.ADDRESS3
, HZ.ADDRESS4
, HZ.ANALYSIS_FY
, HZ.CATEGORY_CODE
, HZ.CITY
, HZ.COMPETITOR_FLAG
, HZ.COUNTRY
, HZ.COUNTY
, IGS_CO_GEN_003.GET_PER_ADDR_FOR_CORR(HZ.PARTY_ID)
, HZ.CURR_FY_POTENTIAL_REVENUE
, HZ.CUSTOMER_KEY
, HZ.DO_NOT_MAIL_FLAG
, HZ.DUNS_NUMBER
, HZ.EMAIL_ADDRESS
, HZ.EMPLOYEES_TOTAL
, HZ.FISCAL_YEAREND_MONTH
, HZ.GROUP_TYPE
, HZ.GSA_INDICATOR_FLAG
, HZ.HQ_BRANCH_IND
, HZ.JGZZ_FISCAL_CODE
, HZ.KNOWN_AS
, HZ.KNOWN_AS2
, HZ.KNOWN_AS3
, HZ.KNOWN_AS4
, HZ.KNOWN_AS5
, HZ.LANGUAGE_NAME
, HZ.LAST_ORDERED_DATE
, HZ.MISSION_STATEMENT
, HZ.NEXT_FY_POTENTIAL_REVENUE
, HZ.ORGANIZATION_NAME_PHONETIC
, HZ.ORIG_SYSTEM_REFERENCE
, HZ.PERSON_ACADEMIC_TITLE
, HZ.PERSON_FIRST_NAME
, HZ.PERSON_FIRST_NAME_PHONETIC
, HZ.PERSON_IDEN_TYPE
, HZ.PERSON_IDENTIFIER
, HZ.PERSON_LAST_NAME
, HZ.PERSON_LAST_NAME_PHONETIC
, HZ.PERSON_MIDDLE_NAME
, HZ.PERSON_NAME_SUFFIX
, HZ.PERSON_PRE_NAME_ADJUNCT
, HZ.PERSON_PREVIOUS_LAST_NAME
, HZ.PERSON_TITLE
, HZ.POSTAL_CODE
, HZ.PROVINCE
, HZ.REFERENCE_USE_FLAG
, HZ.SALUTATION
, HZ.SIC_CODE
, HZ.SIC_CODE_TYPE
, HZ.STATE
, HZ.STATUS
, HZ.TAX_NAME
, HZ.TAX_REFERENCE
, HZ.THIRD_PARTY_FLAG
, HZ.TOTAL_NUM_OF_ORDERS
, HZ.TOTAL_ORDERED_AMOUNT
, HZ.URL
, HZ.VALIDATED_FLAG
, HZ.WH_UPDATE_DATE
, HZ.YEAR_ESTABLISHED
, PIT.API_PERSON_ID
, TO_CHAR (SYSDATE
, 'MONTHFMDD
, RRRR')
, IGF_GR_GEN.GET_CALENDAR_DESC(FBASE.CI_CAL_TYPE
, FBASE.CI_SEQUENCE_NUMBER) AWARD_YEAR
, FBASE.CI_CAL_TYPE
, FBASE.CI_SEQUENCE_NUMBER
, CI.ALTERNATE_CODE
, 'DB/'||PIT.API_PERSON_ID|| TO_CHAR(SYSDATE
, '/MM/DD/YYYY')
, FMAST.DESCRIPTION
, SUM(DISB.DISB_GROSS_AMT)
, TO_CHAR(DISB.DISB_DATE
, 'DD-MON-YYYY')
FROM HZ_PARTIES HZ
, IGS_PE_HZ_PARTIES PE
, ( 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
, IGF_AW_AWARD_ALL AW
, IGF_AW_FUND_MAST_ALL FMAST
, IGF_AW_FUND_CAT_ALL FCAT
, IGF_AP_FA_BASE_REC_ALL FBASE
, IGF_DB_AWD_DISB_DTL_ALL DISB
, IGS_CA_INST_ALL CI
WHERE HZ.PARTY_ID = PE.PARTY_ID
AND PE.PARTY_ID = PIT.PE_PERSON_ID (+)
AND NVL(PE.DECEASED_IND
, 'N') <> 'Y'
AND PE.PARTY_ID = FBASE.PERSON_ID
AND FBASE.BASE_ID = AW.BASE_ID
AND AW.FUND_ID = FMAST.FUND_ID
AND FMAST.FUND_CODE = FCAT.FUND_CODE
AND FCAT.SYS_FUND_TYPE = 'LOAN'
AND AW.AWARD_ID = DISB.AWARD_ID
AND DISB.SF_CREDIT_ID IS NOT NULL
AND DISB.NOTIFICATION_DATE IS NULL
AND CI.CAL_TYPE = FBASE.CI_CAL_TYPE
AND CI.SEQUENCE_NUMBER = FBASE.CI_SEQUENCE_NUMBER GROUP BY HZ.PARTY_ID
, HZ.PARTY_NAME
, HZ.PARTY_NUMBER
, HZ.PARTY_TYPE
, HZ.ADDRESS1
, HZ.ADDRESS2
, HZ.ADDRESS3
, HZ.ADDRESS4
, HZ.ANALYSIS_FY
, HZ.CATEGORY_CODE
, HZ.CITY
, HZ.COMPETITOR_FLAG
, HZ.COUNTRY
, HZ.COUNTY
, IGS_CO_GEN_003.GET_PER_ADDR_FOR_CORR(HZ.PARTY_ID)
, HZ.CURR_FY_POTENTIAL_REVENUE
, HZ.CUSTOMER_KEY
, HZ.DO_NOT_MAIL_FLAG
, HZ.DUNS_NUMBER
, HZ.EMAIL_ADDRESS
, HZ.EMPLOYEES_TOTAL
, HZ.FISCAL_YEAREND_MONTH
, HZ.GROUP_TYPE
, HZ.GSA_INDICATOR_FLAG
, HZ.HQ_BRANCH_IND
, HZ.JGZZ_FISCAL_CODE
, HZ.KNOWN_AS
, HZ.KNOWN_AS2
, HZ.KNOWN_AS3
, HZ.KNOWN_AS4
, HZ.KNOWN_AS5
, HZ.LANGUAGE_NAME
, HZ.LAST_ORDERED_DATE
, HZ.MISSION_STATEMENT
, HZ.NEXT_FY_POTENTIAL_REVENUE
, HZ.ORGANIZATION_NAME_PHONETIC
, HZ.ORIG_SYSTEM_REFERENCE
, HZ.PERSON_ACADEMIC_TITLE
, HZ.PERSON_FIRST_NAME
, HZ.PERSON_FIRST_NAME_PHONETIC
, HZ.PERSON_IDEN_TYPE
, HZ.PERSON_IDENTIFIER
, HZ.PERSON_LAST_NAME
, HZ.PERSON_LAST_NAME_PHONETIC
, HZ.PERSON_MIDDLE_NAME
, HZ.PERSON_NAME_SUFFIX
, HZ.PERSON_PRE_NAME_ADJUNCT
, HZ.PERSON_PREVIOUS_LAST_NAME
, HZ.PERSON_TITLE
, HZ.POSTAL_CODE
, HZ.PROVINCE
, HZ.REFERENCE_USE_FLAG
, HZ.SALUTATION
, HZ.SIC_CODE
, HZ.SIC_CODE_TYPE
, HZ.STATE
, HZ.STATUS
, HZ.TAX_NAME
, HZ.TAX_REFERENCE
, HZ.THIRD_PARTY_FLAG
, HZ.TOTAL_NUM_OF_ORDERS
, HZ.TOTAL_ORDERED_AMOUNT
, HZ.URL
, HZ.VALIDATED_FLAG
, HZ.WH_UPDATE_DATE
, HZ.YEAR_ESTABLISHED
, PIT.API_PERSON_ID
, TO_CHAR (SYSDATE
, 'MONTHFMDD
, RRRR')
, IGF_GR_GEN.GET_CALENDAR_DESC(FBASE.CI_CAL_TYPE
, FBASE.CI_SEQUENCE_NUMBER)
, FBASE.CI_CAL_TYPE
, FBASE.CI_SEQUENCE_NUMBER
, CI.ALTERNATE_CODE
, 'DB/'||PIT.API_PERSON_ID|| TO_CHAR(SYSDATE
, '/MM/DD/YYYY')
, FMAST.DESCRIPTION
, TO_CHAR(DISB.DISB_DATE
, 'DD-MON-YYYY')
, DISB.DISB_DATE