[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_coa_updated VARCHAR2(1) ;
FUNCTION coa_needs_update(
p_item_code igf_aw_coa_items.item_code%TYPE,
p_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
p_amount igf_aw_coa_items.amount%TYPE,
p_pell_coa_amount igf_aw_coa_items.pell_coa_amount%TYPE,
p_alt_pell_amount igf_aw_coa_items.alt_pell_amount%TYPE,
p_fixed_cost igf_aw_coa_items.fixed_cost%TYPE
) RETURN BOOLEAN AS
------------------------------------------------------------------
--Created by : veramach, Oracle India
--Date created: 07-JAN-2004
--
--Purpose:
--
--
--Known limitations/enhancements and/or remarks:
--
--Change History:
--Who When What
--ridas 09-09-2005 Bug #4226096. Added a new CURSOR 'c_get_lock_flg'
-- to fetch lock flag.
-------------------------------------------------------------------
-- check if the student's COA item needs update or not
CURSOR c_coa_item(
cp_item_code igf_aw_coa_items.item_code%TYPE,
cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
cp_amount igf_aw_coa_items.amount%TYPE,
cp_pell_coa_amount igf_aw_coa_items.pell_coa_amount%TYPE,
cp_alt_pell_amount igf_aw_coa_items.alt_pell_amount%TYPE,
cp_fixed_cost igf_aw_coa_items.fixed_cost%TYPE
) IS
SELECT lock_flag
FROM igf_aw_coa_items
WHERE item_code = cp_item_code
AND base_id = cp_base_id
AND amount = cp_amount
AND NVL(pell_coa_amount,-1) = NVL(cp_pell_coa_amount,-1)
AND NVL(alt_pell_amount,-1) = NVL(cp_alt_pell_amount,-1)
AND NVL(fixed_cost,'*') = NVL(cp_fixed_cost,'*');
SELECT lock_flag
FROM igf_aw_coa_items
WHERE item_code = cp_item_code
AND base_id = cp_base_id;
END coa_needs_update;
SELECT DISTINCT ld_cal_type,ld_sequence_number
FROM igf_aw_coa_itm_terms
WHERE base_id = c_base_id
MINUS
SELECT DISTINCT ld_cal_type,ld_sequence_number
FROM igf_aw_award_t
WHERE process_id = c_process_id;
SELECT DISTINCT ld_cal_type,ld_sequence_number
FROM igf_aw_award_t
WHERE process_id = c_process_id
MINUS
SELECT DISTINCT ld_cal_type,ld_sequence_number
FROM igf_aw_coa_itm_terms
WHERE base_id = c_base_id;
SELECT rowid row_id,
item_code,
lock_flag
FROM igf_aw_coa_items
WHERE base_id = cp_base_id;
SELECT rowid row_id,
lock_flag
FROM igf_aw_coa_itm_terms
WHERE base_id = cp_base_id
AND item_code = cp_item_code;
PROCEDURE delete_coa(
p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE
) AS
------------------------------------------------------------------
--Created by : veramach, Oracle India
--Date created: 26-Dec-2003
--
--Purpose: Delete COA item and terms associated with a base_id
--
--
--Known limitations/enhancements and/or remarks:
--
--Change History:
--Who When What
-------------------------------------------------------------------
CURSOR c_coa(
cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE
) IS
SELECT rowid row_id,
item_code
FROM igf_aw_coa_items
WHERE base_id = cp_base_id;
SELECT rowid row_id
FROM igf_aw_coa_itm_terms
WHERE base_id = cp_base_id
AND item_code = cp_item_code;
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_calc.delete_coa.debug','Starting delete_coa with base_id:'||p_base_id);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_calc.delete_coa.debug','deleting item '||coa_rec.item_code);
igf_aw_coa_itm_terms_pkg.delete_row(
x_rowid => coa_terms_rec.row_id
);
igf_aw_coa_items_pkg.delete_row(
x_rowid => coa_rec.row_id
);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_calc.delete_coa.debug','delete_coa done');
fnd_message.set_token('NAME','IGF_AW_COA_CALC.DELETE_COA' || ' '|| SQLERRM);
fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_aw_coa_calc.delete_coa.exception','sql error: '||SQLERRM);
END delete_coa;
SELECT grp.item_code,
grp.fixed_cost,
grp.default_value item_amount,
grp.pell_amount,
grp.pell_alternate_amt,
grp.lock_flag,
def.ld_cal_type,
def.ld_sequence_number,
def.ld_perct
FROM igf_aw_coa_grp_item grp,
igf_aw_coa_ld def
WHERE grp.coa_code = c_coa_code
AND grp.ci_cal_type = c_ci_cal_type
AND grp.ci_sequence_number = c_ci_sequence_number
AND grp.coa_code = def.coa_code
AND grp.ci_cal_type = def.ci_cal_type
AND grp.ci_sequence_number = def.ci_sequence_number
AND grp.active = 'Y'
AND grp.item_dist = 'N'
UNION ALL
SELECT ovrd.item_code,
grp.fixed_cost,
grp.default_value item_amount,
grp.pell_amount,
grp.pell_alternate_amt,
grp.lock_flag,
ovrd.ld_cal_type,
ovrd.ld_sequence_number,
ovrd.ld_perct
FROM igf_aw_cit_ld_overide ovrd ,
igf_aw_coa_grp_item grp
WHERE grp.coa_code = c_coa_code
AND grp.ci_cal_type = c_ci_cal_type
AND grp.ci_sequence_number = c_ci_sequence_number
AND grp.coa_code = ovrd.coa_code
AND grp.ci_cal_type = ovrd.ci_cal_type
AND grp.ci_sequence_number = ovrd.ci_sequence_number
AND grp.item_code = ovrd.item_code
AND grp.active = 'Y'
AND grp.item_dist = 'Y' ;
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_calc.populate_setup_table.debug','inserting item->'||items_rec.item_code||
'item_term_amount->'||item_term_amount||
'ld_cal->'||items_rec.ld_cal_type||
'ld_seq->'||items_rec.ld_sequence_number);
igf_aw_award_t_pkg.insert_row(
x_rowid => l_rowid,
x_process_id => l_process_id,
x_sl_number => l_sl_number,
x_fund_id => NULL,
x_base_id => NULL,
x_offered_amt => items_rec.item_amount, --this is the item amount
x_accepted_amt => item_term_amount, --this is the term amount
x_paid_amt => items_rec.pell_amount, --this is the pell amount
x_need_reduction_amt => items_rec.pell_alternate_amt, --this is the pell alternate amount
x_flag => 'GR',
x_temp_num_val1 => NULL,
x_temp_num_val2 => NULL,
x_temp_char_val1 => items_rec.item_code, --item code
x_tp_cal_type => items_rec.fixed_cost,
x_tp_sequence_number => NULL,
x_ld_cal_type => items_rec.ld_cal_type,
x_ld_sequence_number => items_rec.ld_sequence_number,
x_mode => 'R',
x_adplans_id => NULL,
x_app_trans_num_txt => NULL,
x_award_id => NULL,
x_lock_award_flag => items_rec.lock_flag,
x_temp_val3_num => NULL,
x_temp_val4_num => NULL,
x_temp_char2_txt => NULL,
x_temp_char3_txt => NULL
);
SELECT DISTINCT temp_char_val1 item_code,
offered_amt item_amount,
paid_amt pell_amount,
need_reduction_amt pell_alternate_amount,
tp_cal_type fixed_cost,
lock_award_flag
FROM igf_aw_award_t
WHERE process_id = c_process_id
ORDER BY temp_char_val1;
SELECT temp_char_val1 item_code,
accepted_amt item_term_amount,
paid_amt pell_amount,
need_reduction_amt pell_alternate_amount,
tp_cal_type fixed_cost,
ld_cal_type,
ld_sequence_number,
lock_award_flag
FROM igf_aw_award_t
WHERE temp_char_val1 = c_item_code
AND process_id=c_process_id;
SELECT temp_char_val1 item_code,
accepted_amt item_term_amount,
paid_amt pell_amount,
need_reduction_amt pell_alternate_amount,
tp_cal_type fixed_cost,
ld_cal_type,
ld_sequence_number,
lock_award_flag
FROM igf_aw_award_t
WHERE temp_char_val1 = c_item_code
AND process_id = c_process_id;
SELECT DISTINCT coa_group_items.new_item,
coa_group_items.item_amount,
coa_group_items.pell_amt,
coa_group_items.pell_alt_amt,
coa_group_items.fixed_cost,
coa_group_items.lock_award_flag,
assigned_coa.existing_item,
assigned_coa.row_id
FROM
(
SELECT DISTINCT temp_char_val1 new_item,
offered_amt item_amount,
paid_amt pell_amt,
need_reduction_amt pell_alt_amt,
tp_cal_type fixed_cost,
lock_award_flag
FROM igf_aw_award_t
WHERE process_id = c_process_id
) coa_group_items,
(
SELECT rowid row_id,
item_code existing_item
FROM igf_aw_coa_items
WHERE base_id = c_base_id
) assigned_coa
WHERE coa_group_items.new_item = assigned_coa.existing_item(+);
SELECT rowid row_id,
lock_flag
FROM igf_aw_coa_itm_terms
WHERE base_id = cp_base_id
AND item_code = cp_item_code
AND ld_cal_type = cp_ld_cal_type
AND ld_sequence_number = cp_ld_sequence_number;
SELECT item.rowid row_id,
item.*
FROM igf_aw_coa_items item
WHERE base_id = c_base_id
AND item_code = c_item_code;
SELECT item_code,
SUM(NVL(amount,0)) amount
FROM igf_aw_coa_itm_terms term
WHERE base_id = c_base_id
GROUP BY item_code;
lv_terms_updated VARCHAR2(1);
lv_terms_updated := 'N';
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_calc.add_coa_items.debug','calling items.insert with base_id/item_code -> ' ||
p_base_id || ' / '|| first_coa_rec.item_code);
g_coa_updated := 'Y' ;
igf_aw_coa_items_pkg.insert_row(
x_rowid => l_rowid,
x_base_id => p_base_id,
x_item_code => first_coa_rec.item_code,
x_amount => NVL(first_coa_rec.item_amount,0),
x_pell_coa_amount => first_coa_rec.pell_amount,
x_alt_pell_amount => first_coa_rec.pell_alternate_amount,
x_fixed_cost => first_coa_rec.fixed_cost,
x_legacy_record_flag => NULL,
x_mode => 'R',
x_lock_flag => first_coa_rec.lock_award_flag
);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_calc.add_coa_items.debug','calling items.insert with base_id/item_code/ld_cal/ld_seq -> ' ||
p_base_id || ' / '|| first_itm_term_rec.item_code ||
' / ' || first_itm_term_rec.ld_cal_type || ' / ' || first_itm_term_rec.ld_sequence_number);
IF igf_aw_coa_update.is_attrib_matching(
p_base_id => p_base_id,
p_base_details => l_base_details,
p_ci_cal_type => g_cal_type,
p_ci_sequence_number => g_sequence_number,
p_ld_cal_type => first_itm_term_rec.ld_cal_type,
p_ld_sequence_number => first_itm_term_rec.ld_sequence_number,
p_item_code => first_itm_term_rec.item_code,
p_amount => ln_amount,
p_rate_order_num => ln_rate_order
) THEN
IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_calc.add_coa_items.debug','is_attrib_matching found');
g_coa_updated := 'Y';
lv_terms_updated := 'Y';
igf_aw_coa_itm_terms_pkg.insert_row(
x_rowid => l_rowid,
x_base_id => p_base_id,
x_item_code => first_itm_term_rec.item_code,
x_amount => ln_amount,
x_ld_cal_type => first_itm_term_rec.ld_cal_type,
x_ld_sequence_number => first_itm_term_rec.ld_sequence_number,
x_mode => 'R',
x_lock_flag => first_itm_term_rec.lock_award_flag
);
g_coa_updated := 'Y';
lv_terms_updated := 'Y';
igf_aw_coa_itm_terms_pkg.insert_row(
x_rowid => l_rowid,
x_base_id => p_base_id,
x_item_code => first_itm_term_rec.item_code,
x_amount => first_itm_term_rec.item_term_amount,
x_ld_cal_type => first_itm_term_rec.ld_cal_type,
x_ld_sequence_number => first_itm_term_rec.ld_sequence_number,
x_mode => 'R',
x_lock_flag => first_itm_term_rec.lock_award_flag
);
IF lv_terms_updated = 'N' THEN
RAISE E_SKIP_STD_NO_ITEMS;
IF g_update_coa = 'N' THEN
IF does_term_mismatch(p_base_id,l_process_id) THEN
fnd_message.set_name('IGF','IGF_AW_COA_INCONSTENT_TERMS');
g_coa_updated := 'Y' ;
igf_aw_coa_items_pkg.insert_row(
x_rowid => l_rowid,
x_base_id => p_base_id,
x_item_code => chk_item_match_rec.new_item,
x_amount => NVL(chk_item_match_rec.item_amount,0),
x_pell_coa_amount => chk_item_match_rec.pell_amt,
x_alt_pell_amount => chk_item_match_rec.pell_alt_amt,
x_fixed_cost => chk_item_match_rec.fixed_cost,
x_mode => 'R',
x_lock_flag => chk_item_match_rec.lock_award_flag
);
IF igf_aw_coa_update.is_attrib_matching(
p_base_id => p_base_id,
p_base_details => l_base_details,
p_ci_cal_type => g_cal_type,
p_ci_sequence_number => g_sequence_number,
p_ld_cal_type => sec_itm_term_rec.ld_cal_type,
p_ld_sequence_number => sec_itm_term_rec.ld_sequence_number,
p_item_code => sec_itm_term_rec.item_code,
p_amount => ln_amount,
p_rate_order_num => ln_rate_order
) THEN
IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_calc.add_coa_items.debug','is_attrib_matching found');
g_coa_updated := 'Y';
lv_terms_updated := 'Y';
igf_aw_coa_itm_terms_pkg.insert_row(
x_rowid => l_rowid,
x_base_id => p_base_id,
x_item_code => sec_itm_term_rec.item_code,
x_amount => ln_amount,
x_ld_cal_type => sec_itm_term_rec.ld_cal_type,
x_ld_sequence_number => sec_itm_term_rec.ld_sequence_number,
x_mode => 'R',
x_lock_flag => sec_itm_term_rec.lock_award_flag
);
g_coa_updated := 'Y';
lv_terms_updated := 'Y';
igf_aw_coa_itm_terms_pkg.insert_row(
x_rowid => l_rowid,
x_base_id => p_base_id,
x_item_code => sec_itm_term_rec.item_code,
x_amount => sec_itm_term_rec.item_term_amount,
x_ld_cal_type => sec_itm_term_rec.ld_cal_type,
x_ld_sequence_number => sec_itm_term_rec.ld_sequence_number,
x_mode => 'R',
x_lock_flag => sec_itm_term_rec.lock_award_flag
);
IF lv_terms_updated = 'N' THEN
RAISE E_SKIP_STD_NO_ITEMS;
ELSIF g_update_coa = 'Y' THEN
IF g_update_method = 'SKIP' THEN
IF does_term_mismatch(p_base_id,l_process_id) THEN
--log an error message
IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_calc.add_coa_items.debug','g_override_inconsistent_terms = N!so erroring out');
g_coa_updated := 'Y' ;
igf_aw_coa_items_pkg.insert_row(
x_rowid => l_rowid,
x_base_id => p_base_id,
x_item_code => chk_item_match_rec.new_item,
x_amount => NVL(chk_item_match_rec.item_amount,0),
x_pell_coa_amount => chk_item_match_rec.pell_amt,
x_alt_pell_amount => chk_item_match_rec.pell_alt_amt,
x_fixed_cost => chk_item_match_rec.fixed_cost,
x_mode => 'R',
x_lock_flag => chk_item_match_rec.lock_award_flag
);
IF igf_aw_coa_update.is_attrib_matching(
p_base_id => p_base_id,
p_base_details => l_base_details,
p_ci_cal_type => g_cal_type,
p_ci_sequence_number => g_sequence_number,
p_ld_cal_type => sec_itm_term_rec.ld_cal_type,
p_ld_sequence_number => sec_itm_term_rec.ld_sequence_number,
p_item_code => sec_itm_term_rec.item_code,
p_amount => ln_amount,
p_rate_order_num => ln_rate_order
) THEN
IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_calc.add_coa_items.debug','is_attrib_matching found');
g_coa_updated := 'Y';
lv_terms_updated := 'Y';
igf_aw_coa_itm_terms_pkg.insert_row(
x_rowid => l_rowid,
x_base_id => p_base_id,
x_item_code => sec_itm_term_rec.item_code,
x_amount => ln_amount,
x_ld_cal_type => sec_itm_term_rec.ld_cal_type,
x_ld_sequence_number => sec_itm_term_rec.ld_sequence_number,
x_mode => 'R',
x_lock_flag => sec_itm_term_rec.lock_award_flag
);
g_coa_updated := 'Y';
lv_terms_updated := 'Y';
igf_aw_coa_itm_terms_pkg.insert_row(
x_rowid => l_rowid,
x_base_id => p_base_id,
x_item_code => sec_itm_term_rec.item_code,
x_amount => sec_itm_term_rec.item_term_amount,
x_ld_cal_type => sec_itm_term_rec.ld_cal_type,
x_ld_sequence_number => sec_itm_term_rec.ld_sequence_number,
x_mode => 'R',
x_lock_flag => sec_itm_term_rec.lock_award_flag
);
IF coa_needs_update(chk_item_match_rec.new_item,p_base_id,chk_item_match_rec.item_amount,chk_item_match_rec.pell_amt,chk_item_match_rec.pell_alt_amt,chk_item_match_rec.fixed_cost) THEN
IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_calc.add_coa_items.debug','updating item '||chk_item_match_rec.new_item);
igf_aw_coa_items_pkg.update_row(
x_rowid => chk_item_match_rec.row_id,
x_base_id => p_base_id,
x_item_code => chk_item_match_rec.new_item,
x_amount => NVL(chk_item_match_rec.item_amount,0),
x_pell_coa_amount => chk_item_match_rec.pell_amt,
x_alt_pell_amount => chk_item_match_rec.pell_alt_amt,
x_fixed_cost => chk_item_match_rec.fixed_cost,
x_mode => 'R',
x_lock_flag => chk_item_match_rec.lock_award_flag
);
g_coa_updated := 'Y' ;
IF igf_aw_coa_update.is_attrib_matching(
p_base_id => p_base_id,
p_base_details => l_base_details,
p_ci_cal_type => g_cal_type,
p_ci_sequence_number => g_sequence_number,
p_ld_cal_type => sec_itm_term_rec.ld_cal_type,
p_ld_sequence_number => sec_itm_term_rec.ld_sequence_number,
p_item_code => sec_itm_term_rec.item_code,
p_amount => ln_amount,
p_rate_order_num => ln_rate_order
) THEN
IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_calc.add_coa_items.debug','is_attrib_matching found');
g_coa_updated := 'Y';
lv_terms_updated := 'Y';
igf_aw_coa_itm_terms_pkg.update_row(
x_rowid => l_item_term.row_id,
x_base_id => p_base_id,
x_item_code => sec_itm_term_rec.item_code,
x_amount => ln_amount,
x_ld_cal_type => sec_itm_term_rec.ld_cal_type,
x_ld_sequence_number => sec_itm_term_rec.ld_sequence_number,
x_mode => 'R',
x_lock_flag => sec_itm_term_rec.lock_award_flag
);
igf_aw_coa_itm_terms_pkg.update_row(
x_rowid => l_item_term.row_id,
x_base_id => p_base_id,
x_item_code => sec_itm_term_rec.item_code,
x_amount => sec_itm_term_rec.item_term_amount,
x_ld_cal_type => sec_itm_term_rec.ld_cal_type,
x_ld_sequence_number => sec_itm_term_rec.ld_sequence_number,
x_mode => 'R',
x_lock_flag => sec_itm_term_rec.lock_award_flag
);
g_coa_updated := 'Y';
lv_terms_updated := 'Y';
IF lv_terms_updated = 'N' THEN
RAISE E_SKIP_STD_NO_ITEMS;
ELSIF g_update_method = 'OVERWRITE' THEN
IF does_term_mismatch(p_base_id,l_process_id) THEN
--delete and recreate COA
IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_calc.add_coa_items.debug','calling delete_coa');
delete_coa(p_base_id);
g_coa_updated := 'Y' ;
igf_aw_coa_items_pkg.insert_row(
x_rowid => l_rowid,
x_base_id => p_base_id,
x_item_code => chk_item_match_rec.new_item,
x_amount => NVL(chk_item_match_rec.item_amount,0),
x_pell_coa_amount => chk_item_match_rec.pell_amt,
x_alt_pell_amount => chk_item_match_rec.pell_alt_amt,
x_fixed_cost => chk_item_match_rec.fixed_cost,
x_mode => 'R',
x_lock_flag => chk_item_match_rec.lock_award_flag
);
IF igf_aw_coa_update.is_attrib_matching(
p_base_id => p_base_id,
p_base_details => l_base_details,
p_ci_cal_type => g_cal_type,
p_ci_sequence_number => g_sequence_number,
p_ld_cal_type => sec_itm_term_rec.ld_cal_type,
p_ld_sequence_number => sec_itm_term_rec.ld_sequence_number,
p_item_code => sec_itm_term_rec.item_code,
p_amount => ln_amount,
p_rate_order_num => ln_rate_order
) THEN
IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_calc.add_coa_items.debug','is_attrib_matching found');
g_coa_updated := 'Y';
lv_terms_updated := 'Y';
igf_aw_coa_itm_terms_pkg.insert_row(
x_rowid => l_rowid,
x_base_id => p_base_id,
x_item_code => sec_itm_term_rec.item_code,
x_amount => ln_amount,
x_ld_cal_type => sec_itm_term_rec.ld_cal_type,
x_ld_sequence_number => sec_itm_term_rec.ld_sequence_number,
x_mode => 'R',
x_lock_flag => sec_itm_term_rec.lock_award_flag
);
g_coa_updated := 'Y';
lv_terms_updated := 'Y';
igf_aw_coa_itm_terms_pkg.insert_row(
x_rowid => l_rowid,
x_base_id => p_base_id,
x_item_code => sec_itm_term_rec.item_code,
x_amount => sec_itm_term_rec.item_term_amount,
x_ld_cal_type => sec_itm_term_rec.ld_cal_type,
x_ld_sequence_number => sec_itm_term_rec.ld_sequence_number,
x_mode => 'R',
x_lock_flag => sec_itm_term_rec.lock_award_flag
);
IF coa_needs_update(chk_item_match_rec.new_item,p_base_id,chk_item_match_rec.item_amount,chk_item_match_rec.pell_amt,chk_item_match_rec.pell_alt_amt,chk_item_match_rec.fixed_cost) THEN
igf_aw_coa_items_pkg.update_row(
x_rowid => chk_item_match_rec.row_id,
x_base_id => p_base_id,
x_item_code => chk_item_match_rec.new_item,
x_amount => NVL(chk_item_match_rec.item_amount,0),
x_pell_coa_amount => chk_item_match_rec.pell_amt,
x_alt_pell_amount => chk_item_match_rec.pell_alt_amt,
x_fixed_cost => chk_item_match_rec.fixed_cost,
x_mode => 'R',
x_lock_flag => chk_item_match_rec.lock_award_flag
);
g_coa_updated := 'Y' ;
IF igf_aw_coa_update.is_attrib_matching(
p_base_id => p_base_id,
p_base_details => l_base_details,
p_ci_cal_type => g_cal_type,
p_ci_sequence_number => g_sequence_number,
p_ld_cal_type => sec_itm_term_rec.ld_cal_type,
p_ld_sequence_number => sec_itm_term_rec.ld_sequence_number,
p_item_code => sec_itm_term_rec.item_code,
p_amount => ln_amount,
p_rate_order_num => ln_rate_order
) THEN
IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_calc.add_coa_items.debug','is_attrib_matching found');
g_coa_updated := 'Y';
lv_terms_updated := 'Y';
igf_aw_coa_itm_terms_pkg.update_row(
x_rowid => l_item_term.row_id,
x_base_id => p_base_id,
x_item_code => sec_itm_term_rec.item_code,
x_amount => ln_amount,
x_ld_cal_type => sec_itm_term_rec.ld_cal_type,
x_ld_sequence_number => sec_itm_term_rec.ld_sequence_number,
x_mode => 'R',
x_lock_flag => sec_itm_term_rec.lock_award_flag
);
igf_aw_coa_itm_terms_pkg.update_row(
x_rowid => l_item_term.row_id,
x_base_id => p_base_id,
x_item_code => sec_itm_term_rec.item_code,
x_amount => sec_itm_term_rec.item_term_amount,
x_ld_cal_type => sec_itm_term_rec.ld_cal_type,
x_ld_sequence_number => sec_itm_term_rec.ld_sequence_number,
x_mode => 'R',
x_lock_flag => sec_itm_term_rec.lock_award_flag
);
g_coa_updated := 'Y';
lv_terms_updated := 'Y';
IF lv_terms_updated = 'N' THEN
RAISE E_SKIP_STD_NO_ITEMS;
igf_aw_coa_items_pkg.update_row(
x_rowid => l_items.row_id,
x_base_id => l_items.base_id,
x_item_code => l_items.item_code,
x_amount => l_terms.amount,
x_pell_coa_amount => l_items.pell_coa_amount,
x_alt_pell_amount => l_items.alt_pell_amount,
x_fixed_cost => l_items.fixed_cost,
x_legacy_record_flag => l_items.legacy_record_flag,
x_mode => 'R',
x_lock_flag => l_items.lock_flag
);
SELECT ca.alternate_code term,
SUM(NVL(terms.amount,0)) amount
FROM igf_aw_coa_itm_terms terms,
igs_ca_inst ca
WHERE ca.cal_type = terms.ld_cal_type
AND ca.sequence_number = terms.ld_sequence_number
AND terms.base_id = c_base_id
GROUP BY base_id,
alternate_code
ORDER BY 1;
SELECT coa_f total
FROM igf_ap_fa_base_rec
WHERE base_id = c_base_id;
SELECT 'x'
FROM igf_aw_coa_itm_terms coa
WHERE coa.base_id = p_base_id
AND rownum = 1;
SELECT SUM(NVL(pell_coa_amount,0)) pell_coa,
SUM(NVL(alt_pell_amount,0)) alt_pell_coa
FROM igf_aw_coa_items
WHERE base_id = cp_base_id;
SELECT 'x'
FROM igf_aw_fund_cat_all fcat,
igf_aw_fund_mast_all fmast,
igf_aw_award_all awd
WHERE fcat.fed_fund_code = 'PELL'
AND fcat.fund_code = fmast.fund_code
AND fmast.fund_id = awd.fund_id
AND awd.award_status IN ('ACCEPTED','OFFERED')
AND awd.base_id = cp_base_id;
SELECT NVL(fab.lock_coa_flag,'N') lock_coa_flag
FROM igf_ap_fa_base_rec fab
WHERE fab.base_id = c_base_id;
g_coa_updated := 'N' ;
IF lv_result = 'Y' AND g_coa_updated = 'Y' THEN
IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_calc.process_student.debug','calling print_output_file, and checking overaward');
IF g_coa_updated = 'N' THEN
fnd_message.set_name('IGF','IGF_AW_NO_CHNG');
p_update_coa IN VARCHAR2,
p_update_method IN VARCHAR2,
l_run_type IN VARCHAR2,
p_pergrp_id IN igs_pe_prsid_grp_mem_all.group_id%TYPE,
p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE
) IS
--------------------------------------------------------------------------------
-- this procedure is called from concurrent manager.
-- if the parameters passed are not correct then procedure exits
-- giving reasons for errors.
-- Created By : cdcruz
-- Modified By : gmuralid
--Change History:
--Who When What
--ridas 08-Feb-2006 Bug #5021084. Added new parameter 'lv_group_type' in call to igf_ap_ss_pkg.get_pid
--tsailaja 13/Jan/2006 Bug 4947880 Added invocation of igf_aw_gen.set_org_id(NULL);
SELECT fa.base_id
FROM igf_ap_fa_base_rec_all fa
WHERE fa.ci_cal_type = c_ci_cal_type
AND fa.ci_sequence_number = c_ci_sequence_number;
SELECT row_id rid
FROM igf_aw_award_t
WHERE process_id = c_process_id;
SELECT group_cd
FROM igs_pe_persid_group_all
WHERE group_id = c_grp_id;
g_update_coa := p_update_coa;
g_update_method := p_update_method;
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_calc.run.debug','p_update_coa:'||p_update_coa);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_calc.run.debug','p_update_method:'||p_update_method);
fnd_file.put_line(fnd_file.log,RPAD(igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','UPDATE_COA'),60) || p_update_coa);
fnd_file.put_line(fnd_file.log,RPAD(igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','UPDATE_METHOD'),60) || igf_aw_gen.lookup_desc('IGF_AW_COA_UPD_MTHD',p_update_method));
ELSIF p_update_coa = 'Y' AND p_update_method IS NULL THEN
fnd_message.set_name('IGF','IGF_AW_COA_PARAM_UPD');
'SELECT party_id person_id,
party_number person_number
FROM hz_parties
WHERE party_id IN ('||lv_sql_stmt||') 'USING p_pergrp_id;
'SELECT party_id person_id,
party_number person_number
FROM hz_parties
WHERE party_id IN ('||lv_sql_stmt||')';
SELECT igf_aw_process_s.nextval INTO l_process_id FROM dual ;
SELECT igf_aw_process_s.nextval INTO l_process_id FROM dual ;
SELECT igf_aw_process_s.nextval INTO l_process_id FROM dual ;
igf_aw_award_t_pkg.delete_row(temp_del_rec.rid);