VIEW: APPS.IGS_AD_RESI_LETTER_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 , 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 , TO_DATE(null) wh_update_date , hz.year_established , rdv.resident_details_id , rdv.residency_class_desc , rdv.residency_class residency_class_cd, rdv.residency_status_desc , rdv.residency_status residency_status_cd, rdv.evaluation_date , rdv.evaluator , rdv.start_dt , rdv.end_dt , rdv.cal_type res_cal_type, rdv.sequence_number res_sequence_number, rdv.calendar_desc res_calendar_desc, rdv.comments , aa.acad_cal_type , aa.acad_ci_sequence_number, NVL (ci.DESCRIPTION, ci. ALTERNATE_CODE) , Igs_co_gen_003.get_residency_dff_values (rdv.resident_details_id) , Igs_co_gen_003.get_per_addr_for_corr(hz.party_id) , TO_CHAR (SYSDATE, 'FMDD Month RRRR') , Persid.group_id FROM hz_parties hz, igs_ad_appl aa, igs_ca_inst ci, igs_pe_hz_parties pe, igs_pe_res_dtls_v rdv, igs_pe_prsid_grp_mem_all persid WHERE hz.party_id = pe.party_id AND persid.person_id(+)=pe.party_id AND NVL(pe.deceased_ind,'N') <> 'Y' AND rdv.person_id = hz.party_id AND aa.person_id = rdv.person_id AND aa.acad_cal_type = ci.cal_type AND aa.acad_ci_sequence_number = ci.sequence_number

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
, 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
, TO_DATE(NULL) WH_UPDATE_DATE
, HZ.YEAR_ESTABLISHED
, RDV.RESIDENT_DETAILS_ID
, RDV.RESIDENCY_CLASS_DESC
, RDV.RESIDENCY_CLASS RESIDENCY_CLASS_CD
, RDV.RESIDENCY_STATUS_DESC
, RDV.RESIDENCY_STATUS RESIDENCY_STATUS_CD
, RDV.EVALUATION_DATE
, RDV.EVALUATOR
, RDV.START_DT
, RDV.END_DT
, RDV.CAL_TYPE RES_CAL_TYPE
, RDV.SEQUENCE_NUMBER RES_SEQUENCE_NUMBER
, RDV.CALENDAR_DESC RES_CALENDAR_DESC
, RDV.COMMENTS
, AA.ACAD_CAL_TYPE
, AA.ACAD_CI_SEQUENCE_NUMBER
, NVL (CI.DESCRIPTION
, CI. ALTERNATE_CODE)
, IGS_CO_GEN_003.GET_RESIDENCY_DFF_VALUES (RDV.RESIDENT_DETAILS_ID)
, IGS_CO_GEN_003.GET_PER_ADDR_FOR_CORR(HZ.PARTY_ID)
, TO_CHAR (SYSDATE
, 'FMDD MONTH RRRR')
, PERSID.GROUP_ID
FROM HZ_PARTIES HZ
, IGS_AD_APPL AA
, IGS_CA_INST CI
, IGS_PE_HZ_PARTIES PE
, IGS_PE_RES_DTLS_V RDV
, IGS_PE_PRSID_GRP_MEM_ALL PERSID
WHERE HZ.PARTY_ID = PE.PARTY_ID
AND PERSID.PERSON_ID(+)=PE.PARTY_ID
AND NVL(PE.DECEASED_IND
, 'N') <> 'Y'
AND RDV.PERSON_ID = HZ.PARTY_ID
AND AA.PERSON_ID = RDV.PERSON_ID
AND AA.ACAD_CAL_TYPE = CI.CAL_TYPE
AND AA.ACAD_CI_SEQUENCE_NUMBER = CI.SEQUENCE_NUMBER