[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'x' FROM igf_lookups_view
WHERE lookup_type = 'IGF_SL_DL_STAFFORD'
AND lookup_code = UPPER(p_fed_fund_code)
AND enabled_flag = 'Y';
SELECT 'x' FROM igf_lookups_view
WHERE lookup_type = 'IGF_SL_DL_PLUS'
AND lookup_code = UPPER(p_fed_fund_code)
AND enabled_flag = 'Y';
SELECT 'x' FROM igf_lookups_view
WHERE lookup_type = 'IGF_SL_CL_GPLUS'
AND lookup_code = UPPER(p_fed_fund_code)
AND enabled_flag = 'Y';
SELECT 'x' FROM igf_lookups_view
WHERE lookup_type = 'IGF_SL_CL_STAFFORD'
AND lookup_code = UPPER(p_fed_fund_code)
AND enabled_flag = 'Y';
SELECT 'x' FROM igf_lookups_view
WHERE lookup_type = 'IGF_SL_CL_PLUS'
AND lookup_code = UPPER(p_fed_fund_code)
AND enabled_flag = 'Y';
SELECT dl_version FROM igf_sl_dl_setup_all
WHERE ci_cal_type = p_ci_cal_type
AND ci_sequence_number = p_ci_seq_num;
SELECT cl_version
FROM igf_sl_cl_setup_all
WHERE ci_cal_type = p_ci_cal_type
AND ci_sequence_number = p_ci_seq_num
AND relationship_cd = p_relationship_cd
AND NVL(PARTY_ID, -99) = NVL(p_party_id, -99);
SELECT message_class, batch_type, cycle_year, rec_length FROM igf_sl_dl_file_type
WHERE dl_version = p_dl_version
AND dl_file_type = p_dl_file_type
AND dl_loan_catg = p_dl_loan_catg;
SELECT dl_version, dl_file_type, dl_loan_catg FROM igf_sl_dl_file_type
WHERE message_class = p_message_class
AND batch_type = p_batch_type;
SELECT file_ident_code, file_ident_name FROM igf_sl_cl_file_type
WHERE cl_version = p_cl_version
AND cl_file_type = p_cl_file_type;
SELECT cl_version, cl_file_type FROM igf_sl_cl_file_type
WHERE file_ident_code = p_file_ident_code
AND file_ident_name = p_file_ident_name;
/* Declared the CURSOR to select the disbursement date for the particular loan ID and Disbursement Number */
CURSOR c_disb_date IS
SELECT iaad.disb_date
FROM igf_sl_loans isl, igf_aw_awd_disb iaad
WHERE isl.loan_id = p_loan_id
AND iaad.disb_num = p_disb_num
AND isl.award_id = iaad.award_id;
SELECT
phone_area_code,
phone_number
FROM igs_pe_contacts_v
WHERE owner_table_id = p_person_id
AND primary_flag = 'Y'
AND status = 'A'
AND contact_point_type = 'PHONE';
SELECT count(*) cnt
FROM igf_sl_cl_pref_lenders
WHERE person_id = cp_person_id
AND end_date IS NULL;
SELECT count(*) cnt
FROM igf_sl_cl_pref_lenders
WHERE person_id = cp_person_id
AND ( ( cp_start_date BETWEEN start_date AND NVL(end_date,TO_DATE('4712/12/31','YYYY/MM/DD')) )
OR ( NVL(cp_end_date,TO_DATE('4712/12/31','YYYY/MM/DD')) BETWEEN start_date AND NVL(end_date,TO_DATE('4712/12/31','YYYY/MM/DD')))
OR ( cp_start_date < start_date AND NVL(cp_end_date,TO_DATE('4712/12/31','YYYY/MM/DD')) > NVL(end_date,TO_DATE('4712/12/31','YYYY/MM/DD'))));
Modified c_get_paddr cursor to select active records only and to select
start date and end date from igs_pe_hz_pty_sites.
(reverse chronological order - newest change first)
**************************************************************/
CURSOR c_get_ssn (
cp_person_id igf_sl_cl_pref_lenders.person_id%TYPE,
cp_person_id_type igs_pe_person_id_typ.s_person_id_type%TYPE
)
IS
SELECT api.api_person_id_uf ssn,
api.person_id_type,
api.start_dt,
api.end_dt
FROM igs_pe_alt_pers_id api,
igs_pe_person_id_typ pid
WHERE api.pe_person_id = cp_person_id
AND api.person_id_type = pid.person_id_type
AND pid.s_person_id_type = cp_person_id_type --
AND SYSDATE BETWEEN api.start_dt AND NVL(api.end_dt,SYSDATE);
SELECT person_id,
person_number,
pre_name_adjunct,
first_name,
middle_name,
last_name,
title,
full_name,
suffix,
birth_date,
gender
FROM igs_pe_person_base_v
WHERE person_id = cp_person_id;
SELECT email_address
FROM igs_pe_contacts_v
WHERE owner_table_id = cp_person_id
AND primary_flag = cp_primary_flag
AND status = cp_status
AND contact_point_type = cp_contact_point_type ;
SELECT ps.party_id,
ps.identifying_address_flag,
l.address1,
l.address2,
l.address3,
l.address4,
l.city,
l.state,
l.province,
l.county,
l.country,
l.postal_code,
l.last_update_date
FROM hz_party_sites ps,
hz_locations l ,
igs_pe_hz_pty_sites ihps
WHERE ps.location_id = l.location_id
AND ps.party_site_id = ihps.party_site_id(+)
AND ps.identifying_address_flag = cp_identifying_address_flag
AND ( ps.status = 'A' AND SYSDATE BETWEEN NVL(ihps.start_date,SYSDATE)
AND NVL(ihps.end_date,SYSDATE))
AND ps.party_id = cp_person_id;
SELECT api.api_person_id,
api.region_cd,
api.person_id_type,
api.start_dt,
api.end_dt,
pid.s_person_id_type
FROM igs_pe_alt_pers_id api,
igs_pe_person_id_typ pid
WHERE api.pe_person_id = cp_person_id
AND api.person_id_type = pid.person_id_type
AND pid.s_person_id_type = cp_person_id_type
AND SYSDATE BETWEEN api.start_dt AND NVL(api.end_dt,SYSDATE);
SELECT ps.party_id,
psu.site_use_type,
ps.identifying_address_flag,
l.address1,
l.address2,
l.address3,
l.address4,
l.city,
l.state,
l.province,
l.county,
l.country,
l.postal_code,
l.last_update_date
FROM hz_party_sites ps,
hz_locations l,
hz_party_site_uses psu
WHERE ps.location_id = l.location_id
AND ps.party_site_id = psu.party_site_id
AND psu.status = cp_status
AND psu.site_use_type = cp_site_use_type
AND SYSDATE BETWEEN NVL(ps.start_date_active,SYSDATE) AND NVL(ps.end_date_active,SYSDATE)
AND ps.party_id = cp_person_id
ORDER BY ps.start_date_active DESC;
SELECT document_num
FROM igs_pe_eit_perm_res_v
WHERE perm_res_cntry = cp_perm_res_cntry
AND person_id = cp_person_id
AND SYSDATE BETWEEN start_date AND NVL(end_date,SYSDATE);
SELECT pei_information1 state_code,
start_date
FROM igs_pe_eit
WHERE person_id = cp_person_id
AND information_type = cp_information_type
AND SYSDATE BETWEEN start_date AND NVL(end_date,SYSDATE);
SELECT lkup.tag,
pct.restatus_code
FROM igs_lookup_values lkup,
igs_pe_eit_restatus_v pct
WHERE lkup.lookup_type = cp_lookup_type
AND lkup.lookup_code = pct.restatus_code
AND pct.person_id = cp_person_id
AND SYSDATE BETWEEN start_date AND NVL(end_date,SYSDATE)
AND lkup.tag IN ('1','2','3');
SELECT l_get_reg_num,
l_get_citzn_status.tag,
l_get_name.birth_date,
l_get_emailaddr.email_address,
l_get_name.first_name,
l_get_name.full_name,
l_get_name.last_name,
l_get_res_state.start_date,
l_get_lic_num.api_person_id,
l_get_lic_num.region_cd,
l_get_name.middle_name,
l_get_paddr.address1,
l_get_paddr.address2,
l_get_paddr.city,
l_get_paddr.state,
l_get_paddr.postal_code,
igf_gr_gen.get_ssn_digits(NVL(l_get_ssn.ssn,'')),
l_get_res_state.state_code,
l_get_paddr.province,
l_get_paddr.county,
l_get_paddr.country,
l_get_addr.address1,
l_get_addr.address2,
l_get_addr.city,
l_get_addr.state,
l_get_addr.postal_code
FROM DUAL;
bkkumar 10-apr-04 FACR116 - Added the check to select
the rel_code from igf_aw_fund_cat_all
(reverse chronological order - newest change first)
**************************************************************/
CURSOR c_chk_rel_code1 (
cp_rel_code igf_sl_cl_setup_all.relationship_cd%TYPE
)
IS
SELECT relationship_cd
FROM igf_sl_cl_setup_all
WHERE relationship_cd = cp_rel_code
AND ROWNUM = 1;
SELECT relationship_cd
FROM igf_sl_cl_pref_lenders
WHERE relationship_cd = cp_rel_code
AND ROWNUM = 1;
SELECT alt_rel_code
FROM igf_aw_fund_cat_all
WHERE alt_rel_code = cp_rel_code
AND ROWNUM = 1;
SELECT ps.responsible_org_unit_cd org_unit_cd,
hz.party_id
FROM igs_ps_ver_all ps,
hz_parties hz
WHERE hz.party_number = ps.responsible_org_unit_cd
AND ps.course_cd = cp_course_cd
AND ps.version_number = cp_version_number;
SELECT count(ADMISSION_APPL_NUMBER) num_of_appls
FROM IGS_AD_APPL_ALL appl,
IGS_AD_APPL_STAT_V stat
WHERE appl.ADM_APPL_STATUS = stat.ADM_APPL_STATUS
AND stat.S_ADM_APPL_STATUS <> 'WITHDRAWN'
AND PERSON_ID = p_person_id;
SELECT count(*) num_of_records
FROM IGS_AD_APPL_ALL appl,
IGS_AD_PS_APPL_INST applinst,
IGS_AD_OU_STAT_V igsl2
WHERE appl.person_id = applinst.person_id
AND appl.admission_appl_number = applinst.admission_appl_number
AND igsl2.adm_outcome_status = applinst.adm_outcome_status
AND igsl2.s_adm_outcome_status NOT IN ('CANCELLED', 'NO-QUOTA', 'REJECTED', 'SUSPEND', 'VOIDED', 'WITHDRAWN')
AND applinst.person_id = p_person_id;
SELECT ps.responsible_org_unit_cd,
hz.party_name,
hz.party_id,
count(*) NUM_OF_RECORDS
FROM igs_ps_ver_all ps,
hz_parties hz,
igs_ad_appl_all appl,
igs_ad_ps_appl_inst applinst,
igs_ad_appl_stat_v igsl1,
igs_ad_ou_stat_v igsl2
WHERE ps.responsible_org_unit_cd = hz.party_number
AND appl.person_id = applinst.person_id
AND appl.admission_appl_number = applinst.admission_appl_number
AND applinst.course_cd = ps.course_cd
AND applinst.crv_version_number = ps.version_number
AND igsl1.adm_appl_status = appl.adm_appl_status
AND igsl1.s_adm_appl_status <> 'WITHDRAWN'
AND igsl2.adm_outcome_status = applinst.adm_outcome_status
AND igsl2.s_adm_outcome_status not in ('CANCELLED','NO-QUOTA', 'REJECTED', 'SUSPEND', 'VOIDED', 'WITHDRAWN')
AND applinst.person_id = p_person_id
GROUP BY
responsible_org_unit_cd,
party_name,
party_id;
SELECT fa.assoc_org_num,
hz.party_number,
hz.party_name,
hz.party_id
FROM igf_ap_fa_base_rec_all fa,
hz_parties hz
WHERE fa.base_id = p_base_id
AND fa.assoc_org_num = hz.party_id;
SELECT org.org_alternate_id office_cd
FROM igs_or_org_alt_ids org,
igs_or_org_alt_idtyp idt
WHERE org.org_structure_id = p_org_unit_cd
AND org.org_alternate_id_type = idt.org_alternate_id_type
AND SYSDATE BETWEEN org.start_date AND NVL(org.end_date, SYSDATE)
AND idt.system_id_type = p_office_type;
SELECT fund_id
FROM igf_aw_award_all
WHERE award_id = cp_n_award_id;
SELECT fcat.fed_fund_code
FROM igf_aw_fund_mast_all fmast
,igf_aw_fund_cat_all fcat
WHERE fmast.fund_code = fcat.fund_code
AND fmast.fund_id = cp_n_fund_id;
SELECT loans.loan_number
FROM igf_sl_lor_all lor
,igf_sl_loans_all loans
,igf_aw_award_all awd
,igf_aw_fund_mast_all fmast
WHERE lor.relationship_cd = cp_v_relationship_cd
AND loans.loan_id = lor.loan_id
AND (loans.loan_status = 'S' OR loans.loan_chg_status = 'S')
AND awd.award_id = loans.award_id
AND fmast.fund_id = awd.fund_id
AND fmast.ci_cal_type = cp_v_cal_type
AND fmast.ci_sequence_number = cp_n_sequence_number;
SELECT loans.loan_number
FROM igf_sl_lor_all lor
,igf_sl_loans_all loans
,igf_aw_award_all awd
,igf_aw_fund_mast_all fmast
WHERE lor.relationship_cd = cp_v_relationship_cd
AND loans.loan_id = lor.loan_id
AND awd.award_id = loans.award_id
AND fmast.fund_id = awd.fund_id
AND fmast.ci_cal_type = cp_v_cal_type
AND fmast.ci_sequence_number = cp_n_sequence_number;
PROCEDURE update_cl_chg_status(p_v_loan_number IN igf_sl_loans_all.loan_number%TYPE) IS
------------------------------------------------------------------
--Created by : svuppala, Oracle IDC
--Date created: 20-Oct-2004
--
-- Purpose : Update Loan Change Status
-- Invoked :
--
-- Parameters : p_loan_id : IN parameter. Required.
--
--
--Known limitations/enhancements and/or remarks:
--
--Change History:
--Who When What
------------------------------------------------------------------
--l_loan_chg_status igf_sl_loans_all.loan_chg_status%TYPE;
SELECT igfsla.* ,igfsla.ROWID ROW_ID
FROM igf_sl_loans_all igfsla
WHERE loan_number = cp_loan_number_txt;
SELECT 'x'
FROM igf_sl_clchsn_dtls
WHERE loan_number_txt = cp_loan_number_txt
AND status_code = 'S';
SELECT 'x'
FROM igf_sl_clchsn_dtls
WHERE loan_number_txt = cp_loan_number_txt
AND status_code = 'A'
AND response_status_code = 'R';
SELECT 'x'
FROM igf_sl_clchsn_dtls
WHERE loan_number_txt = cp_loan_number_txt
AND status_code = 'A'
AND (response_status_code = 'F' OR response_status_code = 'P') ;
SELECT 'x'
FROM igf_sl_clchsn_dtls
WHERE loan_number_txt = cp_loan_number_txt
AND status_code = 'A'
AND response_status_code = 'A';
SELECT 'x'
FROM igf_sl_clchsn_dtls
WHERE loan_number_txt = cp_loan_number_txt
AND status_code = 'R';
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_sl_gen.update_cl_chg_status.debug','Loan Change Status = ' || lv_chg_status);
igf_sl_loans_pkg.update_row(
x_rowid => rec_c_sl_loans.row_id,
x_loan_id => rec_c_sl_loans.loan_id,
x_award_id => rec_c_sl_loans.award_id,
x_seq_num => rec_c_sl_loans.seq_num,
x_loan_number => rec_c_sl_loans.loan_number,
x_loan_per_begin_date => rec_c_sl_loans.loan_per_begin_date,
x_loan_per_end_date => rec_c_sl_loans.loan_per_end_date,
x_loan_status => rec_c_sl_loans.loan_status,
x_loan_status_date => rec_c_sl_loans.loan_status_date,
x_loan_chg_status => lv_chg_status,
x_loan_chg_status_date => rec_c_sl_loans.loan_chg_status_date,
x_active => rec_c_sl_loans.active,
x_active_date => rec_c_sl_loans.active_date,
x_borw_detrm_code => rec_c_sl_loans.borw_detrm_code,
x_legacy_record_flag => rec_c_sl_loans.legacy_record_flag,
x_external_loan_id_txt => rec_c_sl_loans.external_loan_id_txt
);
END update_cl_chg_status;
SELECT fant.*
FROM igf_ap_fa_ant_data fant
WHERE fant.base_id = cp_base_id;
SELECT ant_data.*
FROM
igf_aw_awd_ld_cal_v awd_year_terms,
igf_ap_fa_ant_data ant_data
WHERE
ant_data.ld_cal_type = awd_year_terms.ld_cal_type AND
ant_data.ld_sequence_number = awd_year_terms.ld_sequence_number AND
ant_data.base_id = cp_base_id AND
ant_data.org_unit_cd IS NOT NULL
ORDER BY
igf_aw_packaging.get_term_start_date(cp_base_id, awd_year_terms.ld_cal_type, awd_year_terms.ld_sequence_number) ASC;
SELECT prog.*
FROM
igf_aw_awd_ld_cal_v awd_year_terms,
igf_ap_fa_ant_data ant_data,
igs_ps_ver_all prog
WHERE
ant_data.ld_cal_type = awd_year_terms.ld_cal_type AND
ant_data.ld_sequence_number = awd_year_terms.ld_sequence_number AND
ant_data.base_id = cp_base_id and
ant_data.program_cd = prog.course_cd AND
prog.course_status = 'ACTIVE' AND
ant_data.program_cd IS NOT NULL AND
prog.responsible_org_unit_cd IS NOT NULL
ORDER BY
igf_aw_packaging.get_term_start_date(cp_base_id, awd_year_terms.ld_cal_type, awd_year_terms.ld_sequence_number) ASC,
prog.version_number DESC;
SELECT org.org_alternate_id office_cd
FROM igs_or_org_alt_ids org,
igs_or_org_alt_idtyp idt
WHERE org.org_structure_id = p_org_unit_cd
AND org.org_alternate_id_type = idt.org_alternate_id_type
AND SYSDATE BETWEEN org.start_date AND NVL(org.end_date, SYSDATE)
AND idt.system_id_type = p_office_type;