[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT cainst.alternate_code, cainst.cal_type, cainst.sequence_number
FROM igs_ca_inst cainst, igs_ca_type catyp
WHERE catyp.s_cal_cat = cp_cal_cat
AND cainst.cal_type = catyp.cal_type
AND cainst.ALTERNATE_CODE = cp_alternate_code
AND ROWNUM = 1;
SELECT 'X' val
FROM igs_ca_inst_rel rel,
igs_ca_inst ca
WHERE rel.sup_cal_type = ca.cal_type
AND rel.sup_ci_sequence_number = ca.sequence_number
AND ca.alternate_code = cp_alternate_code
AND sub_cal_type = cp_ld_cal_type
AND sub_ci_sequence_number = cp_ld_sequence_number
AND ROWNUM = 1;
SELECT 'X' val
FROM igs_ca_load_to_teach_v
WHERE load_alternate_code = cp_alternate_code_load
AND teach_alternate_code = cp_alternate_code_teach
AND ROWNUM = 1;
SELECT hz.party_id person_id
FROM igs_pe_hz_parties hz,
hz_parties hz1
WHERE hz1.party_number = cp_person_number
AND hz.party_id = hz1.party_id;
SELECT base_id fa_base_id
FROM igf_ap_fa_base_rec_all
WHERE person_id = cp_person_id
AND ci_cal_type = p_ci_cal_type
AND ci_sequence_number = p_ci_sequence_number ;
SELECT
lookup_type,
lookup_code,
meaning
FROM
igf_lookups_view
WHERE
lookup_type = lv_lookup_type
AND enabled_flag='Y';
SELECT
lookup_type,
lookup_code,
meaning
FROM
igf_aw_lookups_view
WHERE
lookup_type = lv_lookup_type and
sys_award_year = lv_sys_award_year
AND enabled_flag='Y';
SELECT 'x'
FROM igf_ap_li_bat_ints
WHERE batch_num = cp_batch_num
AND batch_type = cp_batch_type
AND rownum = 1;
lv_stmt := 'SELECT '||p_sar_field_name ||' FROM igf_ap_isir_matched_all WHERE payment_isir = ''Y'' AND system_record_type = ''ORIGINAL'' AND base_id = '||to_char(p_base_id);
|| Build ISIR update 2004 - 05
*/
CURSOR c_cum_efc(
cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
cp_isir_id igf_ap_isir_matched_all.isir_id%TYPE
) IS
SELECT distinct ca.alternate_code,
ca.start_dt ld_start_dt,
ca.end_dt ld_end_dt,
igf_ap_gen.get_individual_coa_amt(ca.start_dt,fa.base_id) coa,
ca.cal_type ld_cal_type, ca.sequence_number ld_sequence_number,
DECODE(fa.AWARD_FMLY_CONTRIBUTION_TYPE, '2',
DECODE ( igf_ap_efc_calc.get_efc_no_of_months(ca.end_dt, coa.base_id ),
1 , SEC_ALTERNATE_MONTH_1,
2 , SEC_ALTERNATE_MONTH_2,
3 , SEC_ALTERNATE_MONTH_3,
4 , SEC_ALTERNATE_MONTH_4,
5 , SEC_ALTERNATE_MONTH_5,
6 , SEC_ALTERNATE_MONTH_6,
7 , SEC_ALTERNATE_MONTH_7,
8 , SEC_ALTERNATE_MONTH_8,
9 , SECONDARY_EFC,
10, SEC_ALTERNATE_MONTH_10,
11, SEC_ALTERNATE_MONTH_11,
12, SEC_ALTERNATE_MONTH_12 ) ,
DECODE ( igf_ap_efc_calc.get_efc_no_of_months(ca.end_dt, coa.base_id ),
1 , PRIMARY_ALTERNATE_MONTH_1,
2 , PRIMARY_ALTERNATE_MONTH_2,
3 , PRIMARY_ALTERNATE_MONTH_3,
4 , PRIMARY_ALTERNATE_MONTH_4,
5 , PRIMARY_ALTERNATE_MONTH_5,
6 , PRIMARY_ALTERNATE_MONTH_6,
7 , PRIMARY_ALTERNATE_MONTH_7,
8 , PRIMARY_ALTERNATE_MONTH_8,
9 , PRIMARY_EFC,
10, PRIMARY_ALTERNATE_MONTH_10,
11, PRIMARY_ALTERNATE_MONTH_11,
12, PRIMARY_ALTERNATE_MONTH_12 ) ) efc
FROM igf_ap_isir_matched_all isir,
igf_ap_fa_base_rec_all fa,
igs_ca_inst_all ca,
igf_aw_coa_itm_terms coa
WHERE coa.base_id = fa.base_id
AND coa.base_id = isir.base_id
AND fa.base_id = cp_base_id
AND isir.isir_id = cp_isir_id
AND coa.ld_sequence_number = ca.sequence_number
AND coa.ld_cal_type = ca.cal_type
ORDER BY ca.start_dt;
SELECT SUM(coait.amount) coa
FROM igf_aw_coa_itm_terms coait,
igs_ca_inst ca
WHERE ca.cal_type = coait.ld_cal_type
AND ca.sequence_number = coait.ld_sequence_number
AND ca.start_dt <= cp_ld_start_dt
AND coait.base_id = cp_base_id;
SELECT SUM(coait.amount) coa
FROM igf_aw_coa_itm_terms coait,
igs_ca_inst ca
WHERE ca.cal_type = coait.ld_cal_type
AND ca.sequence_number = coait.ld_sequence_number
AND ca.start_dt = cp_ld_start_dt
AND coait.base_id = cp_base_id;
PROCEDURE update_preflend_todo_status ( p_person_id IN igf_ap_fa_base_rec_all.person_id%TYPE,
p_return_status OUT NOCOPY VARCHAR2
) IS
------------------------------------------------------------------
--Created by : bvisvana, Oracle India
--Date created: 09-Dec-2005
--
--Purpose: Bug 4773795 - To update the PREFLEND todo status when user assigns a preferred lender through the Manage Preferred Lender page
--
--Known limitations/enhancements and/or remarks:
--
--Change History:
--Who When What
-------------------------------------------------------------------
CURSOR get_active_preflend(cp_person_id igf_ap_fa_base_rec_all.person_id%TYPE) IS
SELECT clprl_id FROM igf_sl_cl_pref_lenders WHERE
person_id = cp_person_id AND TRUNC(SYSDATE) BETWEEN start_date AND NVL(end_date,TRUNC(SYSDATE));
SELECT clprl_id,base_id,item_sequence_number,status FROM igf_ap_td_item_inst_v WHERE
person_id = cp_person_id AND system_todo_type_code='PREFLEND';
l_update BOOLEAN := FALSE;
l_update := TRUE;
l_update := TRUE;
IF l_update THEN
update_td_status( p_base_id => preflend_todo_item_rec.base_id,
p_item_sequence_number => preflend_todo_item_rec.item_sequence_number,
p_status => l_status,
p_clprl_id => preflend_todo_item_rec.clprl_id,
p_return_status => l_return_status
);
fnd_message.set_token('NAME','IGF_AP_GEN.UPDATE_PREFLEND_TODO_STATUS'||SQLERRM);
END update_preflend_todo_status;
PROCEDURE update_td_status(
p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
p_item_sequence_number IN igf_ap_td_item_inst_all.item_sequence_number%TYPE,
p_status IN igf_ap_td_item_inst_all.status%TYPE,
p_clprl_id IN igf_sl_cl_pref_lenders.clprl_id%TYPE DEFAULT NULL,
p_return_status OUT NOCOPY VARCHAR2
) AS
------------------------------------------------------------------
--Created by : veramach, Oracle India
--Date created:
--
--Purpose:
--
--
--Known limitations/enhancements and/or remarks:
--
--Change History:
--Who When What
-------------------------------------------------------------------
-- Get the item
CURSOR c_inst(
cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
cp_item_sequence_number igf_ap_td_item_inst_all.item_sequence_number%TYPE
) IS
SELECT td.ROWID row_id,
td.*
FROM igf_ap_td_item_inst_all td
WHERE base_id = cp_base_id
AND item_sequence_number = cp_item_sequence_number;
SELECT system_todo_type_code
FROM igf_ap_td_item_mst_all
WHERE todo_number = cp_todo_number;
SELECT hz.party_number
FROM hz_parties hz,
igf_ap_fa_base_rec_all fa
WHERE fa.person_id = hz.party_id
AND fa.base_id = cp_base_id;
SELECT description
FROM igf_ap_td_item_mst_all
WHERE todo_number = cp_item_sequence_number;
SELECT ca.alternate_code
FROM igs_ca_inst_all ca,
igf_ap_fa_base_rec_all fa
WHERE fa.base_id = cp_base_id
AND fa.ci_cal_type = ca.cal_type
AND fa.ci_sequence_number = ca.sequence_number;
igf_ap_td_item_inst_pkg.update_row(
x_rowid => l_inst.row_id,
x_base_id => l_inst.base_id,
x_item_sequence_number => l_inst.item_sequence_number,
x_status => p_status,
x_status_date => TRUNC(SYSDATE),
x_add_date => l_inst.add_date,
x_corsp_date => l_inst.corsp_date,
x_corsp_count => l_inst.corsp_count,
x_inactive_flag => l_inst.inactive_flag,
x_freq_attempt => l_inst.freq_attempt,
x_max_attempt => l_inst.max_attempt,
x_required_for_application => l_inst.required_for_application,
x_mode => 'R',
x_legacy_record_flag => l_inst.legacy_record_flag,
x_clprl_id => l_inst.clprl_id
);
SELECT igs_pe_res_chg_s.nextval INTO l_seq_val FROM DUAL;
fnd_message.set_token('NAME','IGF_AP_GEN.UPDATE_TD_STATUS'||SQLERRM);
END update_td_status;