*************************************************************************
** SAMPLE - Create and load temporary table with SQL Loader **
** This sample is a fixed column format with header, trailer **
** and detail records **
*************************************************************************
--DROP TABLE RZ9_MI_MERIT_HEADER;
CREATE TABLE RZ9_MI_MERIT_HEADER
(RECORD_TYPE VARCHAR2(11),
TITLE VARCHAR2(64),
SCHOOL_ID VARCHAR2(4),
SCHOOL_NAME VARCHAR2(64),
AID_YEAR VARCHAR2(7),
FILE_DT VARCHAR2(10));
--DROP TABLE RZ9_MI_MERIT_DETAIL;
CREATE TABLE RZ9_MI_MERIT_DETAIL
(RECORD_TYPE VARCHAR2(11),
START_YEAR VARCHAR2(4),
SSN VARCHAR2(9),
LAST_NAME VARCHAR2(25),
FIRST_NAME VARCHAR2(16),
MI VARCHAR2(1),
BIRTH_DT VARCHAR2(8),
AMOUNT_USED VARCHAR2(7),
AMOUNT_ELIG VARCHAR2(7),
CODE1 VARCHAR2(2),
AWARD_ID VARCHAR2(9),
Certified VARCHAR(21),
MESSAGE VARCHAR2(21));
--DROP TABLE RZ9_MI_MERIT_TRAILER;
CREATE TABLE RZ9_MI_MERIT_TRAILER
(RECORD_TYPE VARCHAR2(11),
FILE_DT VARCHAR2(10),
TOTAL_RECS VARCHAR2(64),
TOTAL_PMT VARCHAR2(64));
Load data
Infile 'EXTAWARDS.txt'
INSERT
INTO TABLE RZ9_MI_MERIT_HEADER
WHEN RECORD_TYPE = 'EVR_HEADER'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(RECORD_TYPE,
TITLE,
SCHOOL_ID,
SCHOOL_NAME,
AID_YEAR,
FILE_DT)
Load data
Infile 'EXTAWARDS.txt'
INSERT
INTO TABLE RZ9_MI_MERIT_DETAIL
WHEN RECORD_TYPE = 'EVR_DETAIL'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(RECORD_TYPE,
START_YEAR,
SSN,
LAST_NAME,
FIRST_NAME,
MI,
BIRTH_DT,
AMOUNT_USED,
AMOUNT_ELIG,
CODE1,
AWARD_ID,
CERTIFIED,
MESSAGE)
Load data
Infile 'EXTAWARDS.txt'
INSERT
INTO TABLE RZ9_MI_MERIT_TRAILER
WHEN RECORD_TYPE = 'EVR_TRAILER'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(RECORD_TYPE,
FILE_DT,
TOTAL_RECS,
TOTAL_PMT)
*************************************************************************
** SAMPLE - Create and load temporary table with external awards **
** This sample is loading awards from a spreadsheet **
*************************************************************************
--create directory
create or replace directory external_awards as '/export/home/extawards';
--must move or FTP rzratmp.csv into above directory
drop table RZRATMP;
create table rzratmp
(rzratmp_id varchar2(9),
rzratmp_fund_code varchar2(6),
rzratmp_amount varchar2(8),
rzratmp_aprd_code varchar2(6))
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER
DEFAULT DIRECTORY external_awards
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ','
MISSING FIELD VALUES ARE NULL)
LOCATION ('rzratmp.csv')
)
REJECT LIMIT UNLIMITED;
*************************************************************************
** SAMPLE - Read the temporary table and load awards from a **
** spreadsheet using the RP_AWARD API. **
** Illustrates creating your own term and disb schedules **
** using APIs but RP_AWARD will do that for you if you check **
** auto scheduling on RFRMGMT. **
*************************************************************************
create or replace
PROCEDURE PROC_LOAD_AWARDS (p_aidy varchar2) AS
-- Set up cursor for Detail records
cursor import_awards is
SELECT
RZRATMP_ID,
RZRATMP_FUND_CODE,
RZRATMP_AMOUNT,
RZRATMP_APRD_CODE
FROM RZRATMP;
cursor award_terms is
select
rfrdefa_term_code,
rfrdefa_aprd_code,
rfrdefa_award_pct
from rfrdefa
where rfrdefa_aidy_code = p_aidy;
cursor award_disbursements is
select
rfrdefd_term_code,
rfrdefd_aprd_code,
rfrdefd_disburse_date,
rfrdefd_disburse_pct
from rfrdefd
where rfrdefd_aidy_code = p_aidy;
v_id varchar2(9);
v_fund_code varchar2(6);
v_amount_var varchar2(8);
v_amount number;
v_aprd_code varchar2(6);
rv_rzralog_rec_count integer;
rv_max_jobid integer;
rv_jobid integer;
rv_dup_count integer;
rv_pidm number;
rv_award_count integer;
rv_aprd varchar2(6);
rv_term varchar2(6);
rv_amount number;
rv_term_offer_amount number;
rv_disburse_date date;
rv_rowid_out rowid;
rv_rfrdefa_aprd_exists varchar2(1);
rv_rfrdefd_aprd_exists varchar2(1);
rv_counter_rprawrd integer default 0;
rv_counter_rpratrm integer default 0;
rv_counter_rpradsb integer default 0;
rv_err_message varchar2(1000);
BEGIN
------
--AUDIT** Get Job ID - unique one up number for each run
------
rv_rzralog_rec_count := 0;
select count(*)
into rv_rzralog_rec_count
from rzralog;
select max(rzralog_jobid)
into rv_max_jobid
from rzralog;
if rv_rzralog_rec_count = 0 then
rv_jobid := 1;
else
rv_jobid := rv_max_jobid + 1;
end if;
------
--AUDIT** Load all RZRATMP records into RZRALOG
------
insert into RZRALOG
select
A.*,null,rv_jobid,trunc(sysdate),null,null,null,null,null,null
from RZRATMP A;
gb_common.p_commit;
------
--Loop through cursor
------
for import_awards_rec in import_awards loop
rv_err_message := null;
------
--Put incoming fields into variables
------
v_id := import_awards_rec.rzratmp_id;
v_fund_code := import_awards_rec.rzratmp_fund_code;
v_amount_var := import_awards_rec.rzratmp_amount;
v_aprd_code := import_awards_rec.rzratmp_aprd_code;
if LENGTH(TRIM(TRANSLATE(v_amount_var, ' +-.0123456789', ' '))) is null then
v_amount := to_number(v_amount_var);
else
rv_err_message := 'Non numeric amount';
goto update_log;
end if;
------
--AUDIT** Check to see if this record is already in the RZRALOG file and the award
--is 'L'oaded
--
--If it is then this record has already been processed so notify as dup
--and go to the next record
------
rv_dup_count := 0;
select count(*)
into rv_dup_count
from RZRALOG
where v_id = rzralog_id
and v_fund_code = rzralog_fund_code
and rzralog_status = 'L'
and rv_jobid > rzralog_jobid;
if rv_dup_count > 0 then
rv_err_message := 'Log file says award for ID already loaded';
goto update_log;
end if;
------
--Get the PIDM
------
begin
select spriden_pidm
into rv_pidm
from spriden
where v_id = spriden_id
and spriden_change_ind is null;
exception
when no_data_found then
rv_err_message := 'ID not found';
goto update_log;
when too_many_rows then
rv_err_message := 'More than one PIDM found for ID';
goto update_log;
end;
------
--Check to see if the award is already in RPRAWRD
------
begin
select count(*)
into rv_award_count
from rprawrd
where rv_pidm = rprawrd_pidm
and p_aidy = rprawrd_aidy_code
and v_fund_code = rprawrd_fund_code;
exception
when no_data_found then
rv_err_message := 'RPRAWRD already has this fund for aid year';
goto update_log;
end;
------
--Check to see if the aprd code exists in both RFRDEFA and RFRDEFD
------
begin
select 'Y'
into rv_rfrdefa_aprd_exists
from rfrdefa
where rfrdefa_aidy_code = p_aidy
and rfrdefa_aprd_code = v_aprd_code;
exception
when no_data_found then
rv_err_message := 'APRD code not in RFRDEFA';
goto update_log;
end;
begin
select 'Y'
into rv_rfrdefd_aprd_exists
from rfrdefd
where rfrdefd_aidy_code = p_aidy
and rfrdefd_aprd_code = v_aprd_code;
exception
when no_data_found then
rv_err_message := 'APRD code not in RFRDEFD';
goto update_log;
end;
------
--Load the award into RPRAWRD
--Don't commit until both RPRAWRD and RPRATRM loaded
------
BEGIN
rp_award.p_create(
p_aidy_code => p_aidy,
p_pidm => rv_pidm,
p_fund_code => v_fund_code,
p_awst_code => 'ACPT',
p_awst_date => NULL,
p_sys_ind => NULL,
p_lock_ind => NULL,
p_unmet_need_ovrde_ind => NULL,
p_replace_tfc_ovrde_ind => NULL,
p_treq_ovrde_ind => NULL,
p_fed_limit_ovrde_ind => NULL,
p_fund_limit_ovrde_ind => NULL,
p_offer_exp_date => NULL,
p_accept_amt => v_amount,
p_accept_date => NULL,
p_offer_amt => v_amount,
p_offer_date => NULL,
p_info_access_ind => NULL,
p_override_yr_in_coll => NULL,
p_data_origin => 'PROC_LOAD_AWARDS',
p_override_no_pell => NULL);
rv_counter_rprawrd := rv_counter_rprawrd + 1;
/*
dbms_output.put_line('rp_award.p_create, '
|| 'ID is ' || v_id
|| ' Aid Year is ' || p_aidy
|| ' PIDM is ' || rv_pidm
|| ' Amount is ' || v_amount
|| ' SQLERRM is ' || SQLERRM);
*/
EXCEPTION
WHEN OTHERS THEN
gb_common.p_rollback;
rv_err_message := SQLERRM;
/*
dbms_output.put_line('Error on rp_award.p_create, '
|| 'ID is ' || v_id
|| ' Aid Year is ' || p_aidy
-- || ' LN is ' || import_awards_rec.LAST_NAME
|| ' PIDM is ' || rv_pidm
|| ' Amount is ' || v_amount
|| ' SQLERRM is ' || rv_err_message);
*/
goto update_log;
END;
------
--Create the RPRATRM entry for each term defined for aprd in RFRDEFA
------
for award_terms_rec in award_terms loop
if award_terms_rec.rfrdefa_aprd_code > v_aprd_code then
goto end_award_terms_loop;
end if;
rv_term := award_terms_rec.rfrdefa_term_code;
rv_amount := v_amount * award_terms_rec.rfrdefa_award_pct / 100;
BEGIN
rp_award_schedule.p_create(
p_aidy_code => p_aidy,
p_pidm => rv_pidm,
p_fund_code => v_fund_code,
p_term_code => award_terms_rec.rfrdefa_term_code,
p_offer_amt => rv_amount,
p_offer_date => NULL,
p_memo_exp_date => NULL,
p_accept_amt => rv_amount);
--
-- p_accept_date => NULL,
-- p_pckg_load_ind => NULL,
-- p_nslds_ovrd_ind => NULL,
-- p_pell_awrd_load_opt => NULL,
-- p_lock_ind => NULL,
-- p_cip_override_code => NULL);
rv_counter_rpratrm := rv_counter_rpratrm + 1;
/*
dbms_output.put_line('rp_award_schedule.p_create, '
|| 'ID is ' || v_id
|| ' Aid Year is ' || p_aidy
-- || ' LN is ' || import_awards_rec.LAST_NAME
|| ' PIDM is ' || rv_pidm
|| ' Term is ' || rv_term
|| ' Amount is ' || rv_amount
|| ' SQLERRM is ' || SQLERRM);
*/
EXCEPTION
WHEN OTHERS THEN
gb_common.p_rollback;
rv_err_message := rv_err_message || '::' || SQLERRM;
/*
dbms_output.put_line('Error on rp_award_schedule.p_create, '
|| 'ID is ' || v_id
|| ' Aid Year is ' || p_aidy
-- || ' LN is ' || import_awards_rec.LAST_NAME
|| ' PIDM is ' || rv_pidm
|| ' Term is ' || rv_term
|| ' Amount is ' || rv_amount
|| ' SQLERRM is ' || rv_err_message);
*/
goto update_log;
END;
<end_award_terms_loop>
null;
end loop;
------
--Create the Disbursement entry for each disbursement defined in RFRDEFD
------
for award_disbursements_rec in award_disbursements loop
if award_disbursements_rec.rfrdefd_aprd_code > v_aprd_code then
goto end_award_disbursements_loop;
end if;
rv_term := award_disbursements_rec.rfrdefd_term_code;
rv_disburse_date := award_disbursements_rec.rfrdefd_disburse_date;
begin
select rpratrm_offer_amt
into rv_term_offer_amount
from rpratrm
where rpratrm_pidm = rv_pidm
and rpratrm_aidy_code = p_aidy
and rpratrm_fund_code = v_fund_code
and rpratrm_term_code = rv_term;
exception
when no_data_found then
rv_amount := 0;
end;
rv_amount := rv_term_offer_amount * award_disbursements_rec.rfrdefd_disburse_pct / 100;
BEGIN
rb_award_disbursement.p_create(
p_aidy_code => p_aidy,
p_pidm => rv_pidm,
p_schedule_date => rv_disburse_date,
p_fund_code => v_fund_code,
p_term_code => rv_term,
p_schedule_amt => rv_amount,
p_disburse_date => null,
p_disburse_amt => null,
p_tran_number => null,
p_disburse_pct => null,
p_disburse_load => null,
p_disburse_load_opt => null,
p_lms_feed_ind => null,
p_pell_calc_disb_amt => null,
p_pellc_disb_load => null,
p_pellc_disb_load_opt => null,
p_disb_batch_no => null,
p_disb_ref_no => null,
p_debit_cr_ind => null,
p_disb_amt => null,
p_disb_action_code => null,
p_disb_ack_ref_no => null,
p_disb_acpt_debit_cr_ind => null,
p_disb_acpt_amt => null,
p_disb_acpt_date => null,
p_ytd_amt_paid => null,
p_edit_rej_01 => null,
p_edit_rej_02 => null,
p_edit_rej_03 => null,
p_edit_rej_04 => null,
p_edit_rej_05 => null,
p_edit_rej_06 => null,
p_edit_rej_07 => null,
p_edit_rej_08 => null,
p_edit_rej_09 => null,
p_edit_rej_10 => null,
p_edit_rej_11 => null,
p_edit_rej_12 => null,
p_edit_rej_13 => null,
p_edit_rej_14 => null,
p_edit_rej_15 => null,
p_edit_rej_16 => null,
p_edit_rej_17 => null,
p_edit_rej_18 => null,
p_edit_rej_19 => null,
p_edit_rej_20 => null,
p_edit_rej_21 => null,
p_edit_rej_22 => null,
p_edit_rej_23 => null,
p_edit_rej_24 => null,
p_edit_rej_25 => null,
p_shared_sar_id_flag => null,
p_verif_w_status_flag => null,
p_concur_enroll_flag => null,
p_potential_overpmt_flag => null,
p_orig_id => null,
p_inst_xref => null,
p_disb_date_sent => null,
p_disb_date => null,
p_pay_pd_start_date => null,
p_acpt_pay_pd_start_date => null,
p_disb_lock_ind => null,
p_alternate_pell_ind => null,
p_user_id => gb_common.f_sct_user,
p_disb_seq_no => null,
p_acpt_disb_seq_no => null,
p_document_id => null,
p_disb_net_amt => null,
p_acpt_prev_disb_seq_no => null,
p_disb_sched_no_days => null,
p_sys_ind => null,
p_data_origin => null,
p_cps_ver_ind => null,
p_highest_cps_tran_no => null,
p_excl_from_net_amt => null,
p_edit_field_1 => null,
p_edit_value_1 => null,
p_edit_field_2 => null,
p_edit_value_2 => null,
p_edit_field_3 => null,
p_edit_value_3 => null,
p_edit_field_4 => null,
p_edit_value_4 => null,
p_edit_field_5 => null,
p_edit_value_5 => null,
p_cip_code => null,
p_percent_elig_used => null,
p_rowid_out => rv_rowid_out);
rv_counter_rpradsb := rv_counter_rpradsb + 1;
/*
dbms_output.put_line('rp_award_disbursement.p_create, '
|| 'ID is ' || v_id
|| ' Aid Year is ' || p_aidy
-- || ' LN is ' || import_awards_rec.LAST_NAME
|| ' PIDM is ' || rv_pidm
|| ' Aprd is ' || rv_aprd
|| ' Term is ' || rv_term
|| ' Amount is ' || rv_amount
|| ' SQLERRM is ' || SQLERRM);
*/
EXCEPTION
WHEN OTHERS THEN
gb_common.p_rollback;
rv_err_message := rv_err_message || '::' || SQLERRM;
/*
dbms_output.put_line('Error on rp_award_disbursement.p_create, '
|| 'ID is ' || v_id
|| ' Aid Year is ' || p_aidy
-- || ' LN is ' || import_awards_rec.LAST_NAME
|| ' PIDM is ' || rv_pidm
|| ' Aprd is ' || rv_aprd
|| ' Term is ' || rv_term
|| ' Amount is ' || rv_amount
|| ' SQLERRM is ' || rv_err_message);
*/
goto update_log;
END;
<end_award_disbursements_loop>
null;
end loop;
------
--AUDIT** Update the RZRALOG table to log everything
------
<update_log>
if rv_err_message is not null then
gb_common.p_rollback;
UPDATE RZRALOG
set rzralog_status = 'E',
rzralog_pidm = rv_pidm,
rzralog_aidy_code = p_aidy,
rzralog_term = rv_term,
rzralog_amount_calc = rv_amount,
rzralog_disburse_date = rv_disburse_date,
rzralog_message = rv_err_message
where v_id = rzralog_id
and v_fund_code = rzralog_fund_code
and v_amount_var = rzralog_amount
and v_aprd_code = rzralog_aprd_code
and rv_jobid = rzralog_jobid;
gb_common.p_commit;
else
gb_common.p_commit;
UPDATE RZRALOG
SET rzralog_status = 'L',
rzralog_pidm = rv_pidm,
rzralog_aidy_code = p_aidy,
rzralog_term = rv_term,
rzralog_amount_calc = rv_amount,
rzralog_disburse_date = rv_disburse_date
where v_id = rzralog_id
and v_fund_code = rzralog_fund_code
and v_amount_var = rzralog_amount
and v_aprd_code = rzralog_aprd_code
and rv_jobid = rzralog_jobid;
gb_common.p_commit;
end if;
end loop;
dbms_output.put_line('PROC_LOAD_AWARDS completed, RPRAWRD records added '
|| rv_counter_rprawrd
|| ' RPRATRM records added '
|| rv_counter_rpratrm
|| ' RPRADSB records added '
|| rv_counter_rpradsb);
END PROC_LOAD_AWARDS;
*************************************************************************
** SAMPLE - Custom audit trail table with fields from the external **
** award plus any derived field plus message **
*************************************************************************
DROP TABLE RZRALOG;
CREATE TABLE RZRALOG
(RZRALOG_ID VARCHAR2(9),
RZRALOG_FUND_CODE VARCHAR2(6),
RZRALOG_AMOUNT VARCHAR2(8),
RZRALOG_APRD_CODE VARCHAR2(6),
RZRALOG_PIDM NUMBER,
RZRALOG_JOBID INTEGER,
RZRALOG_ACTIVITY_DATE DATE,
RZRALOG_AIDY_CODE VARCHAR2(4),
RZRALOG_TERM VARCHAR2(6),
RZRALOG_AMOUNT_CALC NUMBER,
RZRALOG_DISBURSE_DATE DATE,
RZRALOG_STATUS VARCHAR2(1),
RZRALOG_MESSAGE VARCHAR2(1000));