[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
cai.start_dt,
cai.alternate_code,
cai.cal_type,
cai.sequence_number,
sum(NVL (disb_gross_amt, 0)) load_total_offered_amt,
count(distinct awd.award_id) award_count,
fmast.fund_id fund_id,
fmast.description fund_name
FROM
igf_aw_award_all awd,
igf_aw_fund_mast fmast,
igs_ca_inst_all cai,
igf_aw_awd_disb disb
WHERE
fmast.ci_cal_type = cp_fa_cal_type AND
fmast.ci_sequence_number = cp_fa_sequence_number AND
awd.base_id = igf_aw_gen_004.get_base_id_for_person (cp_person_id, cp_fa_cal_type, cp_fa_sequence_number) AND
awd.fund_id = fmast.fund_id AND
awd.award_status IN ('ACCEPTED', 'OFFERED') AND
-- awd.notification_status_code IN ('R', 'F') AND
awd.award_id = disb.award_id AND
cai.cal_type = disb.ld_cal_type AND
cai.sequence_number = disb.ld_sequence_number AND
disb.ld_cal_type = NVL(cp_ld_cal_type, disb.ld_cal_type) AND
disb.ld_sequence_number = NVL(cp_ld_sequence_number, disb.ld_sequence_number) AND
fmast.fund_id = NVL(cp_fund_id, fmast.fund_id)
AND
NOT EXISTS
(SELECT disb.ld_cal_type, disb.ld_sequence_number
FROM igf_aw_awd_disb disb
WHERE
disb.award_id = awd.award_id
MINUS
SELECT ld_cal_type, ld_sequence_number
FROM igf_aw_awd_prd_term apt
WHERE apt.ci_cal_type = cp_fa_cal_type AND
apt.ci_sequence_number = cp_fa_sequence_number AND
apt.award_prd_cd = NVL(cp_award_prd_cd, award_prd_cd))
GROUP BY
cai.start_dt,
cai.alternate_code,
cai.cal_type,
cai.sequence_number,
fmast.fund_id,
fmast.description
order by cai.start_dt, fmast.description;
SELECT
DISTINCT
cai.start_dt,
cai.alternate_code,
cai.cal_type,
cai.sequence_number
FROM
igf_aw_award_all awd,
igf_aw_fund_mast fmast,
igs_ca_inst_all cai,
igf_aw_awd_disb disb
WHERE
fmast.ci_cal_type = cp_fa_cal_type AND
fmast.ci_sequence_number = cp_fa_sequence_number AND
awd.base_id = igf_aw_gen_004.get_base_id_for_person (cp_person_id, cp_fa_cal_type, cp_fa_sequence_number) AND
awd.fund_id = fmast.fund_id AND
awd.award_status IN ('ACCEPTED', 'OFFERED') AND
-- awd.notification_status_code IN ('R', 'F') AND
awd.award_id = disb.award_id AND
cai.cal_type = disb.ld_cal_type AND
cai.sequence_number = disb.ld_sequence_number
AND
NOT EXISTS
(SELECT disb.ld_cal_type, disb.ld_sequence_number
FROM igf_aw_awd_disb disb
WHERE
disb.award_id = awd.award_id
MINUS
SELECT ld_cal_type, ld_sequence_number
FROM igf_aw_awd_prd_term apt
WHERE apt.ci_cal_type = cp_fa_cal_type AND
apt.ci_sequence_number = cp_fa_sequence_number AND
apt.award_prd_cd = NVL(cp_award_prd_cd, award_prd_cd))
order by cai.start_dt;
SELECT
base.base_id
FROM igf_ap_fa_base_rec_all base
WHERE
person_id = cp_person_id
AND base.ci_cal_type = cp_fa_cal_type
AND base.ci_sequence_number = cp_fa_sequence_number;
p_select_type IN VARCHAR2,
p_sql_stmt OUT NOCOPY VARCHAR2
) IS
l_award_year igf_ap_mis_itms_ltr_v.award_year%TYPE;
system letter code and select type as input parameter
and return the select clause to the main procedure
Know limitations, enhancements or remarks
Change History
Who When What
pkpatel 5-May-2003 Bug 2941138
Modified to use Bind variable
kumma 7-JUN-2003 2853531, Modified for adding the dynamic and static group functionality for financial aid system letter
pkpatel 19-AUG-2003 Bug 3104422 passed the Award year as per the system letter, since the underlying views have been modified.
bvisvana 22-May-2006 Bug 3724328 - For Code refactoring (Issue with huge person id groups).
Removed the p_person_id parameter from the build_sql_stm call and
procedure since the person id are stored in PLSQL table (and treated as PIPELINED function)
(reverse chronological order - newest change first)
***************************************************************/
fnd_dsql.init;
fnd_dsql.add_text(' SELECT distinct email_address,person_id,award_year FROM ');
log_to_fnd('build_sql_stmt','debug','IT IS LETTER CODE of type '||p_sys_ltr_code||' with select type as '||p_select_type||' and award year = '||l_award_year);
IF p_select_type IN ('S','G','A','L') THEN
IF p_sys_ltr_code='FAAWARD' THEN
fnd_dsql.add_text(' IGF_AW_PER_LIST_V WHERE person_id IN (select column_value from table(igf_aw_gen_004.get_person_id())');
fnd_dsql.add_text(' IGF_AP_MIS_ITMS_LTR_V WHERE person_id IN (select column_value from table(igf_aw_gen_004.get_person_id())');
fnd_dsql.add_text(' IGF_SL_DISB_LTR_V WHERE person_id IN (select column_value from table(igf_aw_gen_004.get_person_id())');
SELECT coa_duration_num,
coa_duration_efc_amt
FROM igf_ap_css_profile_all
WHERE active_profile = 'Y'
AND base_id = cp_base_id;
adhawan 11-feb-2003 Select the efc from the Active isir instead of the payment isir
2758804 Modified the c_efc for it .
--rasahoo 05-Aug-2003 #3024112 Changed the parameters in call igf_ap_efc_calc.get_efc_no_of_months
--
(reverse chronological order - newest change first)
***************************************************************/
CURSOR c_efc(cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
cp_months NUMBER)
IS
SELECT DECODE(f.award_fmly_contribution_type,
2, DECODE(cp_months, 1 ,isir.sec_alternate_month_1,
2 , isir.sec_alternate_month_2,
3 , isir.sec_alternate_month_3,
4 , isir.sec_alternate_month_4,
5 , isir.sec_alternate_month_5,
6 , isir.sec_alternate_month_6,
7 , isir.sec_alternate_month_7,
8 , isir.sec_alternate_month_8,
9 , isir.secondary_efc,
10, isir.sec_alternate_month_10,
11, isir.sec_alternate_month_11,
12, isir.sec_alternate_month_12),
DECODE(cp_months, 1 , isir.primary_alternate_month_1,
2 , isir.primary_alternate_month_2,
3 , isir.primary_alternate_month_3,
4 , isir.primary_alternate_month_4,
5 , isir.primary_alternate_month_5,
6 , isir.primary_alternate_month_6,
7 , isir.primary_alternate_month_7,
8 , isir.primary_alternate_month_8,
9 , isir.primary_efc,
10, isir.primary_alternate_month_10,
11, isir.primary_alternate_month_11,
12, isir.primary_alternate_month_12)
) efc,
isir.primary_efc primary_efc,
NVL(isir.auto_zero_efc,'N') auto_zero_efc
FROM igf_ap_isir_matched isir,
igf_ap_fa_base_rec_all f
WHERE isir.base_id = cp_base_id
AND isir.base_id = f.base_id
AND isir.active_isir='Y';
SELECT num_days_divisor,
roundoff_fact
FROM igf_ap_efc_v efc,
igf_ap_fa_base_rec_all fabase
WHERE efc.ci_cal_type = fabase.ci_cal_type
AND efc.ci_sequence_number = fabase.ci_sequence_number
AND fabase.base_id = cp_base_id;
Purpose : This function is used to insert the transaction
records(disbursement records for the different funds
in an award year to the given person. It checks if
p_flag='Y' implies automatic population then it
gets all the disbursement records for all the terms
in an given award year.Else if 'N' then it
generates the records for the given award year
for different fund codes to the person
Know limitations, enhancements or remarks
Change History
Who When What
(reverse chronological order - newest change first)
***************************************************************/
l_fund_found_for_load BOOLEAN;
which is inserted INTO the table. As the functions award_data
wrap has out NOCOPY parameter too thus this wrapper is created to
remove the out NOCOPY parameter so that it can be used in the
select clause
Know limitations, enhancements or remarks
Change History
Who When What
(reverse chronological order - newest change first)
***************************************************************/
l_awd_tot NUMBER;
select clause thus a wrapper is created without out NOCOPY
clause
Know limitations, enhancements or remarks
Change History
Who When What
(reverse chronological order - newest change first)
***************************************************************/
l_ret_data VARCHAR2(32000);
and it inserts the records in temp table for
the person and award year depending on the values
in particular format
Know limitations, enhancements or remarks
Change History
Who When What
(reverse chronological order - newest change first)
***************************************************************/
l_awd_tot NUMBER DEFAULT 0;
SELECT NULL fund_code,-1 fund_id,
'Award Type ' data1,
igf_aw_gen_004.get_headings (
p_person_id,
p_param1,
p_param2,
p_param3,
p_param4,
p_param5,
p_param6,
p_param7,
p_flag
) data2,
1 seq
FROM DUAL
UNION
SELECT DISTINCT
fmast.fund_code, fmast.fund_id, '' || fmast.description || ' ' data1,
get_award_data (
p_person_id,
fmast.fund_id,
p_param1,
p_param2,
p_param3,
p_param4,
p_param5,
p_param6,
p_param7,
p_flag
) data2,
2 seq
FROM
igf_aw_award_all awd,
igf_aw_fund_mast fmast,
igs_ca_inst_all cai,
igf_aw_awd_disb disb
WHERE
fmast.ci_cal_type = RTRIM (SUBSTR (p_param1, 1, 10))
AND fmast.ci_sequence_number = TO_NUMBER (RTRIM (SUBSTR (p_param1, 11)))
AND awd.base_id = igf_aw_gen_004.get_base_id_for_person (p_person_id, RTRIM (SUBSTR (p_param1, 1, 10)), TO_NUMBER (RTRIM (SUBSTR (p_param1, 11))))
AND awd.fund_id = fmast.fund_id
AND awd.award_status IN ('ACCEPTED', 'OFFERED')
-- AND awd.notification_status_code IN ('R', 'F')
AND awd.award_id = disb.award_id
AND cai.cal_type = disb.ld_cal_type
AND cai.sequence_number = disb.ld_sequence_number
AND NVL (awd.offered_amt, 0) > 0
AND
NOT EXISTS
(SELECT disb.ld_cal_type, disb.ld_sequence_number
FROM igf_aw_awd_disb disb
WHERE
disb.award_id = awd.award_id
MINUS
SELECT ld_cal_type, ld_sequence_number
FROM igf_aw_awd_prd_term apt
WHERE apt.ci_cal_type = RTRIM (SUBSTR (p_param1, 1, 10))
AND apt.ci_sequence_number = TO_NUMBER (RTRIM (SUBSTR (p_param1, 11)))
AND apt.award_prd_cd = NVL(p_param2, award_prd_cd))
UNION
SELECT NULL fund_code,-1 fund_id,
'Term Total ' data1,
igf_aw_gen_004.get_term_total (
p_person_id,
p_param1,
p_param2,
p_param3,
p_param4,
p_param5,
p_param6,
p_param7,
p_flag
) data2,
3 seq
FROM DUAL
ORDER BY seq;
SELECT lttmp.rowid row_id FROM
igf_aw_awd_ltr_tmp lttmp
WHERE
person_id = p_person_id AND
ci_cal_type = RTRIM (SUBSTR (p_param1, 1, 10)) AND
ci_sequence_number = TO_NUMBER (RTRIM (SUBSTR (p_param1, 11)));
igf_aw_awd_ltr_tmp_pkg.delete_row (
x_rowid => l_get_pers_del.row_id
);
igf_aw_awd_ltr_tmp_pkg.insert_row (
x_rowid => l_rowid,
x_line_id => get_awd_data%ROWCOUNT,
x_person_id => p_person_id,
x_fund_code => get_award_data_rec.fund_code,
x_fund_description => get_award_data_rec.data1,
x_award_name => get_award_data_rec.data1,
x_ci_cal_type => RTRIM (SUBSTR (p_param1,1,10)),
x_ci_sequence_number => TO_NUMBER (RTRIM (SUBSTR (p_param1,11))),
x_award_total => l_awd_tot,
x_term_amount_text => get_award_data_rec.data2,
x_mode => 'R'
);
p_select_type IN VARCHAR2 ,
p_sys_ltr_code IN VARCHAR2 ,
p_person_id IN NUMBER ,
p_list_id IN NUMBER ,
p_letter_type IN VARCHAR2 ,
p_parameter_1 IN VARCHAR2 ,
p_parameter_2 IN VARCHAR2 ,
p_parameter_3 IN VARCHAR2 ,
p_parameter_4 IN VARCHAR2 ,
p_parameter_5 IN VARCHAR2 ,
p_parameter_6 IN VARCHAR2 ,
p_parameter_7 IN VARCHAR2 ,
p_parameter_8 IN VARCHAR2 ,
p_parameter_9 IN VARCHAR2 ,
p_flag IN VARCHAR2 ,
p_sql_stmt OUT NOCOPY VARCHAR2,
p_exception OUT NOCOPY VARCHAR2
) IS
/*************************************************************
Created By :Prajeesh
Date Created on : 05-Feb-2002
Purpose : This Procedure is the main procedure for pre processing
for both missing items letter and award processing.
It gets the valid persons after pre processing and
generates the sql statement with the valid persons and
this select statement is sent to the main concurrent
manager called procedure
Know limitations, enhancements or remarks
Change History
Who When What
ridas 07-Feb-2006 Bug #5021084. Replaced function IGS_GET_DYNAMIC_SQL with GET_DYNAMIC_SQL.
rajagupt 05-Oct-2005 Bug#4644213 - Award Notification Letter. Return if p_person_id is NULL and p_select_type is 'S'
and if p_list_id is NULL and p_select_type is "G".
bvisvana 04-Sep-2005 FA 157 - Bug # 4382371 - Award Notification Letter.
Make a return without forming the sql stmt if the person_id = '-9999999'
veramach 15-Apr-2004 bug 3543089
Changed sizes of variables to allow more person_ids to be processed.
Also added a error message in the EXCEPTION section
masehgal 14-Jun-2002 # 2413695 Changed message to
'IGF','IGF_AW_NO_LIST'
kumma 7-JUN-2003 2853531, Modified for adding the dynamic and static group functionality for financial aid system letter
Removed the cursor c_query that was fetching query from jtf_fm_queries_all , instead make a call to IGS_CO_API.get_list_query
asbala 19-AUG-2003 3098262:Added check to select only active members for static person_id group
(reverse chronological order - newest change first)
***************************************************************/
CURSOR c_map IS
SELECT document_id,name
FROM igs_co_mapping_v
WHERE map_id=p_list_id;
SELECT attach_fid
FROM ibc_citems_v
WHERE CITEM_ID = cp_itm_id;
SELECT file_name
FROM igs_pe_persid_group_all
WHERE group_id = p_list_id; --Here p_List id is representing group_id for Financial Aid System Letter
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_004.corp_pre_process.debug','Selection Criteria '|| NVL(p_select_type , 'NULL'));
IF p_select_type = 'S' THEN -- Select Type - S-Person/G-Group/A-Auto select
IF p_person_id IS NULL THEN
fnd_message.set_name('IGF','IGF_AW_NOTI_LTR_NO_PERS_NUM');
OPEN l_query_desc FOR select DISTINCT p_person_id person_id FROM DUAL;
ELSIF p_select_type = 'G' THEN -- Select Type - S-Person/G-Group/A-Auto select
IF p_list_id IS NULL THEN
fnd_message.set_name('IGF','IGF_AW_NOTI_LTR_NO_PERS_GRP');
ELSIF p_select_type = 'A' THEN -- Select Type - S-Person/G-Group/A-Auto select
-- select all students who have any award in ready to send state.
OPEN l_query_desc FOR
SELECT
DISTINCT base.person_id
FROM
igf_aw_award_all awd,
igf_aw_fund_mast fmast,
igs_ca_inst_all cai,
igf_aw_awd_disb disb,
igf_ap_fa_base_rec_all base
WHERE
fmast.ci_cal_type = LTRIM(RTRIM(SUBSTR(p_parameter_1, 1, 10))) AND
fmast.ci_sequence_number = TO_NUMBER(LTRIM(RTRIM(SUBSTR(p_parameter_1, 11)))) AND
awd.base_id = base.base_id AND
awd.fund_id = fmast.fund_id AND
awd.award_status IN ('ACCEPTED', 'OFFERED') AND
awd.notification_status_code IN ('R', 'F') AND
awd.award_id = disb.award_id AND
cai.cal_type = disb.ld_cal_type AND
cai.sequence_number = disb.ld_sequence_number
AND
NOT EXISTS
(SELECT disb.ld_cal_type, disb.ld_sequence_number
FROM igf_aw_awd_disb disb
WHERE
disb.award_id = awd.award_id
MINUS
SELECT ld_cal_type, ld_sequence_number
FROM igf_aw_awd_prd_term apt
WHERE apt.ci_cal_type = LTRIM(RTRIM(SUBSTR(p_parameter_1, 1, 10))) AND
apt.ci_sequence_number = TO_NUMBER(LTRIM(RTRIM(SUBSTR(p_parameter_1, 11)))) AND
apt.award_prd_cd = NVL(p_parameter_2, award_prd_cd));
END IF; -- END Select Type - S-Person/G-Group/A-Auto select
LOOP -- Process all selected students for award letter creation and update their state as well.
FETCH l_query_desc INTO l_person_id;
END LOOP; -- END Process all selected students for award letter creation and update their state as well.
p_select_type,
p_sql_stmt
);
IF p_select_type = 'S'
THEN
IF p_sys_ltr_code = 'FAMISTM'
THEN
missing_items (p_person_id, p_parameter_1, l_return_status);
p_select_type,
p_sql_stmt
);
p_select_type,
p_sql_stmt
);
ELSIF p_select_type = 'L' THEN
OPEN c_map;
l_query_str := 'SELECT distinct person_id FROM '
|| '('
|| l_query_text
|| ')';
log_to_fnd('corp_pre_process','debug','select type = L ..Calling build_sql_stmt');
p_select_type,
p_sql_stmt
);
ELSIF p_select_type = 'G' THEN
IF p_list_id IS NULL OR p_person_id IS NOT NULL THEN
FND_MESSAGE.SET_NAME('IGF','IGF_AW_WRNG_PRAM_PG_ID');
l_query_str := ' SELECT distinct person_id FROM igs_pe_prsid_grp_mem_all WHERE group_id = :l_group_id AND sysdate BETWEEN start_date AND NVL(end_date,sysdate)';
log_to_fnd('corp_pre_process','debug','select type = G ..Calling build_sql_stmt');
p_select_type,
p_sql_stmt
);
SELECT tdii.base_id,
tdii.item_sequence_number,
tdii.add_date,
tdii.status_date,
tdii.corsp_date,
tdii.corsp_count,
tdii.inactive_flag,
tdii.freq_attempt,
tdii.max_attempt
FROM igf_ap_td_item_inst tdii
WHERE NVL(tdii.inactive_flag,'N') = 'N'
AND tdii.status IN ('INC', 'REQ')
AND tdii.base_id = l_base_id;
SELECT fabase.base_id
FROM
igf_ap_fa_base_rec_all fabase
WHERE person_id = p_person_id AND
fabase.ci_cal_type = RTRIM (SUBSTR (p_param1, 1, 10)) AND
fabase.ci_sequence_number = TO_NUMBER (RTRIM (SUBSTR (p_param1, 11)));
PROCEDURE loan_disbursement_update (
p_person_id IN NUMBER,
p_award_year IN VARCHAR2
) IS
/*************************************************************
Created By :Prajeesh
Date Created on : 05-Feb-2002
Purpose : This Procedure is the Post processing Procedure
for loan disbursement. AFter sending a mail.
It updates the notification date with current
date for each disbursement record for the person
in an award year
Know limitations, enhancements or remarks
Change History
Who When What
(reverse chronological order - newest change first)
***************************************************************/
/*Cursor to get the disbursement records for the person in an award year*/
CURSOR cur_loan_disb_rec IS
SELECT disb.rowid row_id,disb.*
FROM
igf_db_awd_disb_dtl_all disb
WHERE award_id IN (SELECT award_id
FROM igf_aw_award_all aw,
igf_ap_fa_base_rec_all fbase
WHERE fbase.base_id = aw.base_id AND
fbase.person_id = p_person_id AND
fbase.ci_cal_type = RTRIM(SUBSTR(p_award_year,1,10)) AND
fbase.ci_sequence_number = TO_NUMBER(RTRIM(SUBSTR(p_award_year,11))));
/* Update the notification date as sysdate for the disbursement records*/
OPEN cur_loan_disb_rec;
igf_db_awd_disb_dtl_pkg.update_row (
X_Mode => 'R',
x_rowid => l_cur_loan_disb_rec.row_id,
x_award_id => l_cur_loan_disb_rec.award_id,
x_disb_num => l_cur_loan_disb_rec.disb_num,
x_disb_seq_num => l_cur_loan_disb_rec.disb_seq_num,
x_disb_gross_amt => l_cur_loan_disb_rec.disb_gross_amt,
x_fee_1 => l_cur_loan_disb_rec.fee_1,
x_fee_2 => l_cur_loan_disb_rec.fee_2,
x_disb_net_amt => l_cur_loan_disb_rec.disb_net_amt,
x_disb_adj_amt => l_cur_loan_disb_rec.disb_adj_amt,
x_disb_date => l_cur_loan_disb_rec.disb_date,
x_fee_paid_1 => l_cur_loan_disb_rec.fee_paid_1,
x_fee_paid_2 => l_cur_loan_disb_rec.fee_paid_2,
x_disb_activity => l_cur_loan_disb_rec.disb_activity,
x_disb_batch_id => l_cur_loan_disb_rec.disb_batch_id,
x_disb_ack_date => l_cur_loan_disb_rec.disb_ack_date,
x_booking_batch_id => l_cur_loan_disb_rec.booking_batch_id,
x_booked_date => l_cur_loan_disb_rec.booked_date,
x_disb_status => l_cur_loan_disb_rec.disb_status,
x_disb_status_date => l_cur_loan_disb_rec.disb_status_date,
x_sf_status => l_cur_loan_disb_rec.sf_status, -- Accepted
x_sf_status_date => l_cur_loan_disb_rec.sf_status_date,
x_sf_invoice_num => l_cur_loan_disb_rec.sf_invoice_num,
x_spnsr_credit_id => l_cur_loan_disb_rec.spnsr_credit_id,
x_spnsr_charge_id => l_cur_loan_disb_rec.spnsr_charge_id,
x_sf_credit_id => l_cur_loan_disb_rec.sf_credit_id,
x_error_desc => l_cur_loan_disb_rec.error_desc,
x_notification_date => TRUNC(SYSDATE),
x_interest_rebate_amt => l_cur_loan_disb_rec.interest_rebate_amt,
x_ld_cal_type => l_cur_loan_disb_rec.ld_cal_type,
x_ld_sequence_number => l_cur_loan_disb_rec.ld_sequence_number
);
FND_MESSAGE.SET_TOKEN('NAME','igf_aw_gen_004.loan_disbursement_update');
END loan_disbursement_update;
PROCEDURE missing_items_update (
p_person_id IN NUMBER,
p_award_year IN VARCHAR2
) IS
/*************************************************************
Created By :Prajeesh
Date Created on : 05-Feb-2002
Purpose : This Procedure is for the post processing. It updates
the correspondence date and count and also the
correspondence text is made active Y
Know limitations, enhancements or remarks
Change History
Who When What
--bkkumar 04-jun-2003 Bug #2858504
-- Added legacy_record_flag
-- in the table handler calls for igf_ap_td_item_inst_pkg.update_row
(reverse chronological order - newest change first)
***************************************************************/
/*Cursor to get the incomplete to items for the person in an award year*/
CURSOR cur_incomp_items
IS
SELECT tdii.rowid row_id,tdii.*
FROM
igf_ap_td_item_inst_all tdii,
igf_ap_fa_base_rec_all facon
WHERE
facon.base_id = tdii.base_id AND
NVL(tdii.inactive_flag,'N')='N' AND
tdii.status IN ('INC','REQ') AND
facon.person_id = p_person_id AND
facon.ci_cal_type = RTRIM(SUBSTR(p_award_year,1,10)) AND
facon.ci_sequence_number = TO_NUMBER(RTRIM(SUBSTR(p_award_year,11)));
SELECT ctext.rowid row_id,ctext.*
FROM
igf_ap_st_corr_text ctext
WHERE
ctext.active = 'N' AND
ctext.base_id IN (SELECT base_id
FROM
igf_ap_fa_base_rec_all where person_id=p_person_id);
/*Update the correspondence text for the person as active Y thus it cant be changed again*/
OPEN cur_corr_text;
igf_ap_st_corr_text_pkg.update_row (
x_mode => 'R',
x_rowid => l_cur_corr_text.row_id,
x_corsp_id => l_cur_corr_text.corsp_id,
x_base_id => l_cur_corr_text.base_id,
x_custom_text => l_cur_corr_text.custom_text,
x_run_date => TRUNC(SYSDATE),
x_active => 'Y'
);
update the correspondence date with sysdate*/
OPEN cur_incomp_items;
igf_ap_td_item_inst_pkg.update_row (
x_rowid => l_cur_incomp_items.row_id,
x_base_id => l_cur_incomp_items.base_id,
x_item_sequence_number => l_cur_incomp_items.item_sequence_number,
x_status => l_cur_incomp_items.status,
x_status_date => l_cur_incomp_items.status_date,
x_add_date => l_cur_incomp_items.add_date,
x_corsp_date => TRUNC(SYSDATE),
x_corsp_count => NVL(l_cur_incomp_items.corsp_count,0) + 1,
x_inactive_flag => l_cur_incomp_items.inactive_flag,
x_required_for_application => l_cur_incomp_items.required_for_application,
x_freq_attempt => l_cur_incomp_items.freq_attempt,
x_max_attempt => l_cur_incomp_items.max_attempt,
x_mode => 'R',
x_legacy_record_flag => l_cur_incomp_items.legacy_record_flag,
x_clprl_id => l_cur_incomp_items.clprl_id
);
FND_MESSAGE.SET_TOKEN('NAME','IGF_AW_GEN_004.mising_items_update');
END missing_items_update;
SELECT DECODE (lt.fund_description,
'Award Type', '' || lt.fund_description || ' Award Message
',
'' || NVL (lt.fund_description, '-') || ' ' || NVL (fmast.awd_notice_txt, '-') || '
PROCEDURE award_letter_update (
p_person_id IN NUMBER,
p_award_year IN VARCHAR2,
p_award_prd_cd IN VARCHAR
) IS
/*************************************************************
Created By :Prajeesh
Date Created on : 05-Feb-2002
Purpose : This Procedure is post processing one. It updates
the notification status and status date after
sending the letter. Status is made sent
Know limitations, enhancements or remarks
Change History
Who When What
rasahoo 18-NOV-2003 FA 128 - ISIR update 2004-05
added new parameter award_fmly_contribution_type to
igf_ap_fa_base_rec_pkg.update_row
ugummall 25-SEP-2003 FA 126 Multiple FA Offices
added new parameter assoc_org_num to
igf_ap_fa_base_rec_pkg.update_row call.
masehgal 11-Nov-2002 FA 101 - SAP Obsoletion
removed packaging hold
masehgal 25-Sep-2002 FA 104 - To Do Enhancements
Added manual_disb_hold in FA Base update
(reverse chronological order - newest change first)
***************************************************************/
BEGIN
-- Update award notification status to s = Sent.
IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_004.award_letter_update.debug', 'p_person_id - ' || p_person_id);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_004.award_letter_update.debug', 'p_award_year - ' || p_award_year);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_004.award_letter_update.debug', 'p_award_prd_cd - ' || p_award_prd_cd);
igf_aw_gen.update_notification_status (
p_cal_type => RTRIM (SUBSTR (p_award_year,1,10)),
p_seq_num => TO_NUMBER (RTRIM (SUBSTR (p_award_year, 11))),
p_awarding_period => p_award_prd_cd,
p_base_id => igf_aw_gen_004.get_base_id_for_person (p_person_id, RTRIM (SUBSTR (p_award_year, 1, 10)), TO_NUMBER (RTRIM (SUBSTR (p_award_year, 11)))),
p_notification_status_code => 'S',
p_notification_status_date => TRUNC(SYSDATE),
p_called_from => 'IGFAW13B'
) ;
FND_MESSAGE.SET_TOKEN('NAME','IGF_AW_GEN_004.AWARD_LETTER_UPDATE');
END award_letter_update ;
SELECT corr.custom_text
FROM
igf_ap_st_corr_text corr,
igf_ap_fa_base_rec_all fbase
WHERE
fbase.base_id = corr.base_id AND
fbase.person_id = p_person_id AND
corr.active = 'Y';