[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_delete_flag IN VARCHAR2 ) IS
/*
|| Created By : masehgal
|| Created On : 28-May-2003
|| Purpose : Logs all the Input Parameters
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
*/
-- cursor to get batch desc for the batch id from igf_ap_li_bat_ints
CURSOR c_batch_desc(cp_batch_num igf_aw_li_coa_ints.batch_num%TYPE ) IS
SELECT batch_desc, batch_type
FROM igf_ap_li_bat_ints
WHERE batch_num = cp_batch_num ;
l_yes_no := igf_ap_gen.get_lookup_meaning('IGF_AP_YES_NO',p_delete_flag);
SELECT DISTINCT(NVL(import_record_type,'*')) types
FROM igf_aw_li_coa_ints
WHERE ci_alternate_code = cp_alternate_code
AND batch_num = cp_batch_num
AND person_number = cp_person_number
AND import_status_type IN ('R','U') ;
l_update VARCHAR2(1) ;
l_update := NULL ;
l_update := 'U' ;
IF l_update is NOT NULL and l_others is not null THEN
p_rec_type := 'E';
ELSIF l_update is NOT NULL and l_others is null THEN
p_rec_type := 'U';
ELSIF l_others is not null and l_update is null THEN
p_rec_type := 'I';
ELSIF l_others is null and l_update is null THEN
p_rec_type := 'E';
SELECT DISTINCT item_code
FROM igf_aw_coa_items
WHERE base_id = cp_base_id ;
SELECT COUNT(DISTINCT (ld_sequence_number)) person_terms
FROM igf_aw_coa_itm_terms
WHERE base_id = cp_base_id ;
SELECT COUNT(DISTINCT(ld_sequence_number)) coa_terms
FROM igf_aw_coa_itm_terms
WHERE base_id = cp_base_id
AND item_code = cp_item_code ;
SELECT 1
FROM igf_aw_coa_items
WHERE base_id = cp_base_id
AND item_code = cp_item_code ;
SELECT 1
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_seq_num ;
PROCEDURE delete_coa_terms ( p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE) IS
/*
|| Created By : masehgal
|| Created On : 28-May-2003
|| Purpose : deletion of COA Terms
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
*/
CURSOR del_coa_terms( cp_base_id igf_aw_coa_itm_terms.base_id%TYPE) IS
SELECT rowid
FROM igf_aw_coa_itm_terms
WHERE base_id = cp_base_id ;
igf_aw_coa_itm_terms_pkg.delete_row( x_rowid => lv_rowid.rowid);
fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_lg_coa_imp.delete_coa_terms.exception','Unhandled exception in Procedure delete_coa_terms'||SQLERRM);
fnd_message.set_token('NAME','IGF_AP_LG_COA_IMP.DELETE_COA_TERMS');
END delete_coa_terms ;
PROCEDURE delete_coa_items ( p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE) IS
/*
|| Created By : masehgal
|| Created On : 28-May-2003
|| Purpose : deletion of COA Items
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
*/
CURSOR del_coa_items( cp_base_id igf_aw_coa_itm_terms.base_id%TYPE) IS --,
SELECT rowid
FROM igf_aw_coa_items
WHERE base_id = cp_base_id ;
igf_aw_coa_items_pkg.delete_row( x_rowid => lv_rowid.rowid);
fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_lg_coa_imp.delete_coa_items.exception','Unhandled exception in Procedure delete_coa_items'||SQLERRM);
fnd_message.set_token('NAME','IGF_AP_LG_COA_IMP.DELETE_COA_ITEMS');
END delete_coa_items ;
p_delete_flag IN VARCHAR2 ) IS
/*
|| Created By : masehgal
|| Created On : 28-May-2003
|| Purpose : Main - called from submitted request
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| tsailaja 13/Jan/2006 Bug 4947880 Added invocation of igf_aw_gen.set_org_id(NULL);
g_skip_item_insert BOOLEAN := FALSE ;
l_recs_deleted BOOLEAN ;
SELECT award_year_status_code, sys_award_year
FROM igf_ap_batch_aw_map map
WHERE map.ci_cal_type = g_ci_cal_type
AND map.ci_sequence_number = g_ci_sequence_number ;
SELECT batch_num,
coaint_id,
ci_alternate_code,
person_number,
item_code,
pell_coa_amt,
alt_pell_expense_amt,
NVL(fixed_cost_flag,'N') fixed_cost_flag,
ld_alternate_code,
term_amt,
import_status_type,
import_record_type
FROM igf_aw_li_coa_ints
WHERE ci_alternate_code = cp_alternate_code
AND batch_num = cp_batch_num
AND import_status_type IN ('R','U')
ORDER BY person_number , item_code, ld_alternate_code;
SELECT alternate_code
FROM igs_ca_inst
WHERE cal_type = cp_ci_cal_type
AND sequence_number = cp_ci_sequence_number ;
SELECT 1
FROM igf_aw_item
WHERE item_code = NVL(p_item_code, item_code)
AND rownum = 1;
SELECT items.rowid,items.*
FROM igf_aw_coa_items items
WHERE base_id = cp_base_id
AND item_code = cp_item_code ;
SELECT cal_type, sequence_number
FROM igs_ca_inst
WHERE alternate_code = cp_alternate_code ;
log_input_params( p_batch_num, l_alternate_code , p_delete_flag);
IF l_per_item_count > 0 THEN -- only if some inserts have happened for the person
-- masehgal latest ...
-- as soon as 1 record for a person is marked as error record, we need to skip the whole person
-- using person_all_skip flag for the same
-- from here ....
IF g_person_all_skip THEN
ROLLBACK TO coa_person_recs ;
UPDATE igf_aw_li_coa_ints
SET import_status_type = 'E'
WHERE batch_num = p_batch_num
AND person_number = l_last_person_number ;
UPDATE igf_aw_li_coa_ints
SET import_status_type = 'E'
WHERE batch_num = p_batch_num
AND person_number = l_last_person_number ;
l_recs_deleted := FALSE ;
UPDATE igf_aw_li_coa_ints
SET import_status_type = 'E'
WHERE batch_num = p_batch_num
AND person_number = person_rec.person_number ;
g_skip_item_insert := TRUE ;
g_skip_item_insert := TRUE ;
g_skip_item_insert := TRUE ;
g_skip_item_insert := FALSE ;
IF (NOT l_dup_coa_found) AND (NOT l_recs_deleted) AND l_rec_type = 'U' THEN
-- log a message for duplicate
FND_MESSAGE.SET_NAME('IGF','IGF_AP_ORIG_REC_NOT_FOUND');
g_skip_item_insert := TRUE ;
IF (NOT l_dup_item_found) AND (NOT l_recs_deleted) AND l_rec_type = 'U' THEN
-- log a message for duplicate
FND_MESSAGE.SET_NAME('IGF','IGF_AP_ORIG_REC_NOT_FOUND');
UPDATE igf_aw_li_coa_ints
SET import_status_type = 'E'
WHERE coaint_id = person_rec.coaint_id ;
IF l_rec_type = 'U' AND (NOT l_recs_deleted) THEN
-- records exist
-- have to be deleted
delete_coa_terms ( l_fa_base_id );
delete_coa_items ( l_fa_base_id );
l_recs_deleted := TRUE ;
IF NOT g_skip_item_insert THEN
-- new item ... add item and then add terms
l_item_amount := 0 ;
igf_aw_coa_items_pkg.insert_row(
x_rowid => lv_rowid,
x_base_id => l_fa_base_id,
x_item_code => person_rec.item_code,
x_amount => l_item_amount,
x_pell_coa_amount => person_rec.pell_coa_amt,
x_alt_pell_amount => person_rec.alt_pell_expense_amt,
x_fixed_cost => person_rec.fixed_cost_flag,
x_legacy_record_flag => 'Y',
x_lock_flag => 'N',
x_mode => 'R');
END IF ; -- item insertion skip check
igf_aw_coa_itm_terms_pkg.insert_row(
x_rowid => lv_term_rowid,
x_base_id => l_fa_base_id,
x_item_code => person_rec.item_code,
x_amount => person_rec.term_amt,
x_ld_cal_type => l_load_cal_type,
x_ld_sequence_number => l_load_seq_num,
x_lock_flag => 'N',
x_mode => 'R');
igf_aw_coa_items_pkg.update_row (
x_rowid => l_item_rec.rowid,
x_base_id => l_fa_base_id,
x_item_code => l_item_rec.item_code,
x_amount => l_item_amount,
x_pell_coa_amount => l_item_rec.pell_coa_amount,
x_alt_pell_amount => l_item_rec.alt_pell_amount,
x_fixed_cost => l_item_rec.fixed_cost,
x_legacy_record_flag => 'Y',
x_lock_flag => 'N',
x_mode => 'R' );
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_lg_coa_imp.main.debug','Inserted COA record in Procedure main');
IF p_delete_flag = 'Y' THEN
DELETE FROM igf_aw_li_coa_ints
WHERE coaint_id = person_rec.coaint_id ;
UPDATE igf_aw_li_coa_ints
SET import_status_type = 'I'
WHERE coaint_id = person_rec.coaint_id ;
END LOOP ; -- person selection loop
IF l_per_item_count > 0 THEN -- only if some inserts have happened for the person
-- masehgal latest ...
-- as soon as 1 record for a person is marked as error record, we need to skip the whole person
-- using person_all_skip flag for the same
-- from here ....
IF g_person_all_skip THEN
ROLLBACK TO coa_person_recs ;
UPDATE igf_aw_li_coa_ints
SET import_status_type = 'E'
WHERE batch_num = p_batch_num
AND person_number = l_last_person_number ;
UPDATE igf_aw_li_coa_ints
SET import_status_type = 'E'
WHERE batch_num = p_batch_num
AND person_number = l_last_person_number ;