[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
7. insert_into_cod_tables() (IGF_GR_COD_DTLS and IGF_AW_DB_COD_DTLS)
8. submit_xml_event()
9. XML Gateway Standard to create xml
10. store_xml()
11. This will insert xml file into IGF_SL_COD_DOC_DTLS
12. Launch print xml sub process IGFGRJ14
13. Log input parameters
14. igf_sl_dl_gen_xml.edit_clob()
Edit the clob file and update it in IGF_SL_COD_DOC_DTLS table.
15. igf_sl_dl_gen_xml.print_out_xml()
Print XML file in out file.
/*************************************************************/
/*************************************************************
Created By : ugummall
Date Created On : 2004/10/04
Purpose :
Know limitations, enhancements or remarks
Change History
Who When What
(reverse chronological order - newest change first)
***************************************************************/
gv_document_id_txt VARCHAR2(30);
SELECT group_cd
FROM IGS_PE_PERSID_GROUP_ALL
WHERE group_id = p_per_grp_id;
SELECT base_id
FROM IGF_AP_FA_BASE_REC_ALL
WHERE base_id = cp_base_id
AND ci_cal_type = cp_cal_type
AND ci_sequence_number = cp_seq_number;
SELECT person_number
FROM IGS_PE_PERSON_BASE_V
WHERE person_id = p_person_id;
SELECT base_id
FROM IGF_AP_FA_BASE_REC_ALL
WHERE person_id = p_person_id
AND ci_cal_type = p_cal_type
AND ci_sequence_number = p_sequence_number;
SELECT group_cd group_name
FROM igs_pe_persid_group_all
WHERE group_id = cp_persid_grp;
SELECT bam.award_year_status_code,
bam.pell_participant_code,
bam.sys_award_year
FROM IGF_AP_BATCH_AW_MAP bam
WHERE bam.ci_cal_type = cp_cal_type
AND bam.ci_sequence_number = cp_sequence_number;
SELECT 'x'
FROM IGF_GR_REPORT_PELL rep
WHERE rep.ci_cal_type = cp_cal_type
AND rep.ci_sequence_number = cp_sequence_number
AND rep.rep_entity_id_txt = cp_source_entity_id;
SELECT rfms.origination_id
FROM IGF_GR_RFMS rfms
WHERE rfms.ci_cal_type = cp_cal_type
AND rfms.ci_sequence_number = cp_sequence_number
AND rfms.rep_entity_id_txt = cp_report_entity_id;
SELECT rfms.origination_id
FROM IGF_GR_RFMS rfms
WHERE rfms.ci_cal_type = cp_cal_type
AND rfms.ci_sequence_number = cp_sequence_number
AND rfms.rep_entity_id_txt = cp_report_entity_id
AND rfms.atd_entity_id_txt = cp_attend_entity_id;
SELECT fabase.base_id
FROM IGF_AP_FA_BASE_REC_ALL fabase
WHERE fabase.base_id = cp_base_id;
SELECT 'x'
FROM IGF_GR_RFMS rfms
WHERE rfms.ci_cal_type = cp_cal_type
AND rfms.ci_sequence_number = cp_sequence_number
AND rfms.base_id = cp_base_id;
SELECT 'x'
FROM IGS_PE_PERSID_GROUP_ALL pers
WHERE pers.group_id = cp_persid_grp
AND pers.closed_ind = 'N';
SELECT award_id,
disb_num,
disb_seq_num,
disb_accepted_amt,
disb_date
FROM IGF_AW_DB_CHG_DTLS dbchgdtls
WHERE dbchgdtls.award_id = cp_award_id;
SELECT payment_isir,transaction_num,dependency_status,
date_of_birth,current_ssn,last_name,middle_initial
FROM igf_ap_isir_matched_all
WHERE base_id = p_base_id
AND payment_isir = 'Y'
AND system_record_type = 'ORIGINAL';
IF g_ver_num = '2006-2007' THEN -- check made as part of FA162(COD Reg Updates R12 porting)
IF ( (student_dtl_rec.p_date_of_birth < TO_DATE('01011907', 'DDMMYYYY')) OR (student_dtl_rec.p_date_of_birth > TO_DATE('31121998', 'DDMMYYYY')) ) THEN
lv_complete := FALSE;
IF g_ver_num = '2006-2007' THEN -- check made as part of FA162(COD Reg Updates R12 porting)
IF ( (p_rfms_rec.enrollment_dt < TO_DATE('01012006', 'DDMMYYYY')) OR (p_rfms_rec.enrollment_dt > TO_DATE('30062007', 'DDMMYYYY')) ) THEN
lv_complete := FALSE;
IF g_ver_num = '2006-2007' THEN -- check made as part of FA162(COD Reg Updates R12 porting)
IF ( (rec_disb_chg_dtls.disb_date < TO_DATE('01072006', 'DDMMYYYY')) OR (rec_disb_chg_dtls.disb_date > TO_DATE('27102009', 'DDMMYYYY')) ) THEN
lv_complete := FALSE;
PROCEDURE insert_into_cod_tables(p_rfms_rec cur_rfms%ROWTYPE, p_source_entity_id VARCHAR2)
IS
/*************************************************************
Created By : ugummall
Date Created On : 2004/10/04
Purpose :
Know limitations, enhancements or remarks
Change History
Who When What
(reverse chronological order - newest change first)
***************************************************************/
-- Cursor to get disbursement data from IGF_AW_DB_CHG_DTLS table.
CURSOR cur_disb_chg_dtls ( cp_award_id igf_aw_db_chg_dtls.award_id%TYPE) IS
SELECT award_id,
disb_num,
disb_seq_num,
disb_accepted_amt,
disb_date
FROM IGF_AW_DB_CHG_DTLS dbchgdtls
WHERE dbchgdtls.award_id = cp_award_id;
SELECT s_ssn, s_last_name, s_date_of_birth
FROM IGF_GR_COD_DTLS
WHERE origination_id = cp_origination_id;
SELECT chg.*
FROM igf_aw_db_chg_dtls chg
WHERE award_id = cp_award_id
AND disb_status = 'G'; -- Ready to Send (introduced this predicate again bcz of bug #4390096)
SELECT payment_isir,transaction_num,dependency_status,
date_of_birth,current_ssn,last_name,middle_initial
FROM igf_ap_isir_matched_all
WHERE base_id = p_base_id
AND payment_isir = 'Y'
AND system_record_type = 'ORIGINAL';
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_gen_xml.insert_into_cod_tables.debug','after inserting cod db dtls seq num, disb num, award id' || rec.disb_seq_num || ' , ' || rec.disb_num || ' , ' || rec.award_id);
fnd_message.set_token('NAME','IGF_GR_GEN_XML.INSERT_INTO_COD_TABLES');
END insert_into_cod_tables;
SELECT 'Y'
FROM IGF_GR_REPORT_PELL rep, IGF_GR_ATTEND_PELL atd
WHERE rep.rcampus_id = atd.rcampus_id
AND rep.ci_cal_type = cp_ci_cal_type
AND rep.ci_sequence_number = cp_ci_sequence_number
AND rep.rep_entity_id_txt = cp_rep_entity_id_txt
AND atd.atd_entity_id_txt = cp_atd_entity_id_txt;
SELECT isir.transaction_num
FROM IGF_AP_ISIR_MATCHED_ALL isir
WHERE isir.base_id = cp_base_id
AND isir.payment_isir = 'Y'
AND isir.system_record_type = 'ORIGINAL';
SELECT sum(a.disb_accepted_amt) disb_amt_tot
FROM IGF_AW_DB_CHG_DTLS a
WHERE a.award_id = cp_award_id
AND NVL(a.disb_activity, 'x') <> 'Q'
AND a.disb_seq_num = ( SELECT max(b.disb_seq_num)
FROM IGF_AW_DB_CHG_DTLS b
WHERE b.award_id = cp_award_id
AND NVL(b.disb_activity, 'x') <> 'Q'
AND b.disb_num = a.disb_num);
SELECT count(*)
FROM IGF_AW_AWD_DISB disb
WHERE disb.award_id = cp_award_id
AND UPPER(disb.hold_rel_ind) = 'TRUE';
insert_into_cod_tables(p_rfms_rec, p_source_entity_id);
fnd_message.set_name('IGF','IGF_GR_XML_INSERT_EXC');
CURSOR cur_sequence IS SELECT IGF_GR_PELL_GEN_XML_S.NEXTVAL FROM DUAL;
SELECT document_id_txt
FROM IGF_GR_COD_DTLS
WHERE document_id_txt = cp_document_id;
SELECT response_option_code
FROM IGF_GR_PELL_SETUP_ALL
WHERE ci_cal_type = cp_cal_type
AND ci_sequence_number = cp_sequence_number
AND rep_entity_id_txt = cp_source_entity_id;
OPEN cur_per_grp FOR ' SELECT PARTY_ID FROM HZ_PARTIES WHERE PARTY_ID IN (' || l_list || ') ' USING p_persid_grp;
OPEN cur_per_grp FOR ' SELECT PARTY_ID FROM HZ_PARTIES WHERE PARTY_ID IN (' || l_list || ') ';
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_gen_xml.store_xml.debug',' insert into doc dtls ');
igf_sl_cod_doc_dtls_pkg.insert_row(
x_rowid => lv_rowid,
x_document_id_txt => lv_document_id,
x_outbound_doc => l_clob,
x_inbound_doc => NULL,
x_send_date => NULL,
x_ack_date => NULL,
x_doc_status => 'R',
x_doc_type => 'PELL',
x_full_resp_code => NULL,
x_mode => 'R');
PROCEDURE update_status(p_document_id_txt VARCHAR2)
IS
/*************************************************************
Created By : ugummall
Date Created On : 2004/10/04
Purpose :
Know limitations, enhancements or remarks
Change History
Who When What
(reverse chronological order - newest change first)
***************************************************************/
CURSOR cur_cod_orig ( cp_document_id_txt VARCHAR2) IS
SELECT coddtls.origination_id
FROM IGF_GR_COD_DTLS coddtls
WHERE coddtls.document_id_txt = cp_document_id_txt;
SELECT disb.award_id, disb.disb_num, disb.disb_seq_num
FROM IGF_AW_DB_COD_DTLS disb
WHERE disb.document_id_txt = cp_document_id_txt;
SELECT rfms.*
FROM IGF_GR_RFMS rfms
WHERE rfms.origination_id = cp_origination_id;
SELECT disb.rowid row_id,disb.*
FROM IGF_AW_DB_CHG_DTLS disb
WHERE disb.award_id = cp_award_id
AND disb.disb_num = cp_disb_num
AND disb.disb_seq_num = cp_disb_seq;
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_gen_xml.update_status.debug','First doc id ' || p_document_id_txt);
igf_gr_rfms_pkg.update_row ( x_rowid => rfms_rec.row_id,
x_origination_id => rfms_rec.origination_id,
x_ci_cal_type => rfms_rec.ci_cal_type,
x_ci_sequence_number => rfms_rec.ci_sequence_number,
x_base_id => rfms_rec.base_id,
x_award_id => rfms_rec.award_id ,
x_rfmb_id => rfms_rec.rfmb_id ,
x_sys_orig_ssn => rfms_rec.sys_orig_ssn ,
x_sys_orig_name_cd => rfms_rec.sys_orig_name_cd ,
x_transaction_num => rfms_rec.transaction_num ,
x_efc => rfms_rec.efc,
x_ver_status_code => rfms_rec.ver_status_code ,
x_secondary_efc => rfms_rec.secondary_efc ,
x_secondary_efc_cd => rfms_rec.secondary_efc_cd ,
x_pell_amount => rfms_rec.pell_amount ,
x_pell_profile => rfms_rec.pell_profile ,
x_enrollment_status => rfms_rec.enrollment_status ,
x_enrollment_dt => rfms_rec.enrollment_dt ,
x_coa_amount => rfms_rec.coa_amount ,
x_academic_calendar => rfms_rec.academic_calendar ,
x_payment_method => rfms_rec.payment_method ,
x_total_pymt_prds => rfms_rec.total_pymt_prds ,
x_incrcd_fed_pell_rcp_cd => rfms_rec.incrcd_fed_pell_rcp_cd ,
x_attending_campus_id => rfms_rec.attending_campus_id ,
x_est_disb_dt1 => rfms_rec.est_disb_dt1 ,
x_orig_action_code => rfms_rec.orig_action_code ,
x_orig_status_dt => rfms_rec.orig_status_dt ,
x_orig_ed_use_flags => rfms_rec.orig_ed_use_flags ,
x_ft_pell_amount => rfms_rec.ft_pell_amount ,
x_prev_accpt_efc => rfms_rec.prev_accpt_efc ,
x_prev_accpt_tran_no => rfms_rec.prev_accpt_tran_no ,
x_prev_accpt_sec_efc_cd => rfms_rec.prev_accpt_sec_efc_cd ,
x_prev_accpt_coa => rfms_rec.prev_accpt_coa ,
x_orig_reject_code => rfms_rec.orig_reject_code ,
x_wk_inst_time_calc_pymt => rfms_rec.wk_inst_time_calc_pymt ,
x_wk_int_time_prg_def_yr => rfms_rec.wk_int_time_prg_def_yr ,
x_cr_clk_hrs_prds_sch_yr => rfms_rec.cr_clk_hrs_prds_sch_yr ,
x_cr_clk_hrs_acad_yr => rfms_rec.cr_clk_hrs_acad_yr ,
x_inst_cross_ref_cd => rfms_rec.inst_cross_ref_cd ,
x_low_tution_fee => rfms_rec.low_tution_fee ,
x_rec_source => rfms_rec.rec_source ,
x_pending_amount => rfms_rec.pending_amount ,
x_mode => 'R' ,
x_birth_dt => rfms_rec.birth_dt ,
x_last_name => rfms_rec.last_name ,
x_first_name => rfms_rec.first_name ,
x_middle_name => rfms_rec.middle_name ,
x_current_ssn => rfms_rec.current_ssn ,
x_legacy_record_flag => rfms_rec.legacy_record_flag ,
x_reporting_pell_cd => rfms_rec.rep_pell_id ,
x_rep_entity_id_txt => rfms_rec.rep_entity_id_txt ,
x_atd_entity_id_txt => rfms_rec.atd_entity_id_txt ,
x_note_message => rfms_rec.note_message ,
x_full_resp_code => rfms_rec.full_resp_code ,
x_document_id_txt => p_document_id_txt
);
igf_aw_db_chg_dtls_pkg.update_row ( x_rowid => sys_rec.row_id,
x_award_id => sys_rec.award_id,
x_disb_num => sys_rec.disb_num,
x_disb_seq_num => sys_rec.disb_seq_num,
x_disb_accepted_amt => sys_rec.disb_accepted_amt,
x_orig_fee_amt => sys_rec.orig_fee_amt,
x_disb_net_amt => sys_rec.disb_net_amt,
x_disb_date => sys_rec.disb_date,
x_disb_activity => sys_rec.disb_activity,
x_disb_status => sys_rec.disb_status,
x_disb_status_date => sys_rec.disb_status_date,
x_disb_rel_flag => sys_rec.disb_rel_flag,
x_first_disb_flag => sys_rec.first_disb_flag,
x_interest_rebate_amt => sys_rec.interest_rebate_amt,
x_disb_conf_flag => sys_rec.disb_conf_flag,
x_pymnt_prd_start_date => sys_rec.pymnt_prd_start_date,
x_note_message => sys_rec.note_message,
x_batch_id_txt => sys_rec.batch_id_txt,
x_ack_date => sys_rec.ack_date,
x_booking_id_txt => sys_rec.booking_id_txt,
x_booking_date => sys_rec.booking_date,
x_mode => 'R'
);
fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_gr_gen_xml.update_status.exception','Exception:'||SQLERRM);
fnd_message.set_token('NAME','IGF_GR_GEN_XML.UPDATE_STATUS');
END update_status;
SELECT meaning, lookup_code
FROM IGF_LOOKUPS_VIEW
WHERE lookup_type = 'IGF_GE_PARAMETERS'
AND lookup_code IN ('PARAMETER_PASS','DOCUMENT_ID');
igf_sl_cod_doc_dtls_pkg.update_row ( x_rowid => lv_rowid,
x_document_id_txt => p_document_id,
x_outbound_doc => lc_newxmldoc,
x_inbound_doc => NULL,
x_send_date => TRUNC(SYSDATE),
x_ack_date => NULL,
x_doc_status => 'S',
x_doc_type => 'PELL',
x_full_resp_code => NULL,
x_mode => 'R');
update_status(p_document_id);