Re: Long-running PL/SQL function (long)

I think that you can try 2 things:

1. Run the function in the source db. Selects across links does funny stuff.
2. Write CSV file on the source system and sql loader on the target using direct.

Yechiel Adar
Mehish
----- Original Message -----
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Sent: Tuesday, January 07, 2003 7:25 PM

>
> Our developers sent me a function which is running quite long to see if I
> could give them any advice. It is written in PL/SQL for version 9.2.0.1
> of Oracle on Sun Solaris. It is going across a database link. It reads
> tables in one database and loads a new table in a datamart table on
another
> box. It looks like it will currently run for four or five days to load a
> 140 million-row table, which is longer than our available window.
>
> I am wondering if anyone can look at the big picture and see if there are
> any obvious places for improvement of this overall design. I am open to
> any suggestions that I can relay back to the developers.
>
> My gratitude to anyone who can wade through this and recommend
> improvements.
>
> Cherie Machler
> Oracle DBA
> Gelco Information Network
>
>
>
>
> FUNCTION exp_rpt_sts_load (
> in_src_proc_no NUMBER,
> in_stt_dt DATE,
> in_stop_dt DATE,
> in_commit_interval NUMBER,
> in_err_threshold VARCHAR2,
> in_debugging BOOLEAN )
> RETURN BOOLEAN IS
>
> TYPE list_array IS VARRAY(200) OF VARCHAR2(2);
>
> TYPE no_array IS VARRAY(200) OF NUMBER(10);
>
> lv_pay_sts_array list_array :=
> list_array(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
>
> NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
>
> NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
>
> NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
>
> NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
>
> NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
>
> NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
>
> lv_sts_cnfr_no no_array :=
> no_array(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
>
> NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
>
> NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
>
> NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
>
> NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
>
> NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
>
> NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
>
> CURSOR c_exp_rpt_hdr IS
> SELECT a.acct_no,
> a.cnfr_no,
> a.arrv_dt_tm,
> b.pay_type,
> b.status,
> b.wh_mod_dt_tm upd_dt_tm,
> b.wh_date_key,
> b.wh_time_key
> FROM exp_rpt_hdr a,
> exp_rpt_amt_type b
> WHERE b.wh_mod_dt_tm BETWEEN in_stt_dt AND in_stop_dt
> AND trans_type = 'R'
> AND a.cnfr_no = b.cnfr_no
> order by acct_no, cnfr_no;
>
> -- c_exp_rpt_hdr storage values
> lv_cnfr_no exp_rpt_hdr.cnfr_no%TYPE;
> lv_acct_no acct_pay_type.acct_no%TYPE;
> lv_pay_type acct_pay_type.pay_type%TYPE;
>
> CURSOR c_exp_rpt_dtls IS
> SELECT DISTINCT a.cnfr_no,
> b.line_seq_no,
> NVL(b.dtl_seq_no,0) dtl_seq_no,
> NVL(c.alloc_seq_no,0) alloc_seq_no,
> d.descr
> FROM exp_rpt_line_item_hdr a,
> exp_rpt_line_item_dtl b,
> exp_rpt_alloc c,
> acct_pay_type d
> WHERE a.cnfr_no = lv_cnfr_no
> AND b.pay_type = lv_pay_type
> AND a.cnfr_no = b.cnfr_no
> AND b.cnfr_no = c.cnfr_no(+)
> AND b.line_seq_no = c.line_seq_no(+)
> AND b.dtl_seq_no = c.dtl_seq_no(+)
> AND d.acct_no = lv_acct_no
> AND b.pay_type = d.pay_type
> ORDER BY b.line_seq_no, dtl_seq_no, alloc_seq_no;
>
> -- c_exp_rpt_dtls storage values
> lv_line_seq_no exp_rpt_line_item_dtl.line_seq_no%TYPE;
> lv_dtl_seq_no exp_rpt_line_item_dtl.dtl_seq_no%TYPE;
> lv_82_descr acct_pay_type.descr%TYPE;
>
> -- Row definitions
> r_exp_rpt_hdr c_exp_rpt_hdr%ROWTYPE;
> r_exp_rpt_dtls c_exp_rpt_dtls%ROWTYPE;
>
> -- Miscellaneous local variables
> lv_mgr_global_user_no acct_user.global_user_no%TYPE;
> lv_eff_dt_in DATE;
> lv_sql_code NUMBER;
> lv_sql_msg VARCHAR2(256);
> lv_step_txt VARCHAR2(160);
> lv_err_txt VARCHAR2(320);
> lv_sysdate DATE;
> lv_char_SYSDATE VARCHAR2(20);
> lv_handle UTL_FILE.FILE_TYPE;
> lv_status BOOLEAN := TRUE;
> lv_in_cnt NUMBER :=0;
> lv_row_cnt NUMBER :=0;
> lv_err_cnt NUMBER :=0;
> lv_run_log_no INTEGER :=0;
> lv_in_loop BOOLEAN;
> lv_82 BOOLEAN := FALSE;
> lv_pay_meth r_exp_rpt_hdr.pay_type%TYPE;
> lv_pay_sts r_exp_rpt_hdr.status%TYPE;
> lv_no_alloc_rec BOOLEAN;
> lv_ach_amt NUMBER := 0;
> loop_ctr NUMBER;
> lv_chng_dt DATE;
> lv_arrv_dt DATE;
> lv_loop NUMBER := 0;
> lv_tran_dt DATE;
>
> -- Constants
> c_proc_nm VARCHAR2(80) := 'load_edm_exp_rpt_sts';
>
> BEGIN
> -- File Control
> lv_handle := WHSE_DEBUG_PKG.open_debug_log_file(c_proc_nm);
> lv_err_txt := 'Process '
> || c_proc_nm
> || ', '
> || 'Runtime '
> || SYSDATE;
>
> lv_status := WHSE_DEBUG_PKG.write_debug_log_file(lv_err_txt,
> lv_handle);
>
> --Run Log Start
> lv_run_log_no := GlobalError.RunLogStart2(in_src_proc_no);
>
> SELECT SYSDATE, TO_CHAR(SYSDATE, 'yyyymmddhh24miss')
> INTO lv_sysdate, lv_char_SYSDATE
> FROM dual;
>
> -- Debug Control
> IF in_debugging
> THEN
> lv_err_txt := 'Time '
> || SYSDATE
> || lv_step_txt;
> lv_status := WHSE_DEBUG_PKG.write_debug_log_file(lv_err_txt,
> lv_handle);
> END IF;
>
> lv_acct_no := 0;
> lv_cnfr_no := 0;
> lv_line_seq_no := 0;
>
> OPEN c_exp_rpt_hdr;
>
> LOOP
>
> FETCH c_exp_rpt_hdr
> INTO r_exp_rpt_hdr;
> EXIT WHEN c_exp_rpt_hdr%NOTFOUND;
>
> IF lv_acct_no <> r_exp_rpt_hdr.acct_no THEN
> BEGIN
>
> SELECT descr
> INTO lv_82_descr
> FROM acct_pay_type
> WHERE pay_type = 82
> AND acct_no = lv_acct_no
> AND wh_mod_dt_tm = ( SELECT max(wh_mod_dt_tm)
> FROM acct_pay_type
> WHERE acct_no = lv_acct_no
> AND pay_type = 82)
> AND wh_row_eff_dt_tm = ( SELECT max(wh_row_eff_dt_tm)
> FROM acct_pay_type
> WHERE acct_no = lv_acct_no
> AND pay_type = 82);
>
> EXCEPTION
> WHEN OTHERS THEN
> lv_82_descr := 'COMPANY PAID';
>
> END;
> END IF;
>
> IF lv_cnfr_no <> r_exp_rpt_hdr.cnfr_no THEN
>
> IF lv_82 THEN
>
> loop_ctr := 0;
>
> LOOP
>
> loop_ctr := loop_ctr +1;
>
> IF lv_pay_sts_array(loop_ctr) IS NULL THEN
> EXIT;
> END IF;
>
> IF ((lv_pay_sts_array(loop_ctr) IS NOT NULL)
> AND (lv_sts_cnfr_no(loop_ctr) = lv_cnfr_no))
> THEN
> BEGIN
>
> INSERT INTO edm_exp_rpt_sts(
> cnfr_no,
> pay_meth,
> line_seq_no,
> dtl_seq_no,
> alloc_seq_no,
> pay_sts,
> pay_meth_descr,
> acct_no,
> sts_chng_dt,
> arrival_dt,
> src_proc_no,
> dm_load_dt,
> ach_amt,
> tran_dt)
> VALUES(
> lv_cnfr_no,
> 82,
> lv_line_seq_no+1,
> 1,
> 0,
> lv_pay_sts_array(loop_ctr),
> lv_82_descr,
> lv_acct_no,
> lv_chng_dt,
> lv_arrv_dt,
> in_src_proc_no,
> lv_sysdate,
> lv_ach_amt,
> lv_tran_dt );
>
> lv_row_cnt := lv_row_cnt + 1;
> IF MOD(lv_row_cnt,in_commit_interval) = 0 THEN
> COMMIT;
> END IF;
>
> EXCEPTION
> WHEN OTHERS THEN NULL;
> END;
> END IF;
> lv_pay_sts_array(loop_ctr) := NULL;
> lv_sts_cnfr_no(loop_ctr) := NULL;
> END LOOP;
>
> loop_ctr := 1;
>
> lv_82 := FALSE;
>
> END IF;
>
> lv_line_seq_no := 0;
> END IF;
>
> lv_cnfr_no := r_exp_rpt_hdr.cnfr_no;
> lv_acct_no := r_exp_rpt_hdr.acct_no;
> lv_pay_type := r_exp_rpt_hdr.pay_type;
> lv_pay_sts := r_exp_rpt_hdr.status;
> lv_chng_dt := r_exp_rpt_hdr.upd_dt_tm;
> lv_arrv_dt := r_exp_rpt_hdr.arrv_dt_tm;
> lv_pay_meth := r_exp_rpt_hdr.pay_type;
>
> loop_ctr := 1;
>
> LOOP
> IF lv_pay_sts_array(loop_ctr) IS NULL THEN
> lv_pay_sts_array(loop_ctr) := lv_pay_sts;
> lv_sts_cnfr_no(loop_ctr) := lv_cnfr_no;
> EXIT;
> ELSIF lv_pay_sts_array(loop_ctr) = lv_pay_sts THEN
> EXIT;
> ELSE
> loop_ctr := loop_ctr +1;
> END IF;
> END LOOP;
>
> lv_step_txt := 'Working on exp_rpt_hdr Acct_no :'
> || r_exp_rpt_hdr.acct_no;
>
> IF lv_pay_type = 82 THEN
> lv_82 := TRUE;
> END IF;
>
> -- Debug Control
> IF in_debugging THEN
> lv_err_txt := 'Time '
> || SYSDATE
> || lv_step_txt;
> lv_status :=
> WHSE_DEBUG_PKG.write_debug_log_file(lv_err_txt,
> lv_handle);
> END IF;
>
> OPEN c_exp_rpt_dtls;
> LOOP
>
> FETCH c_exp_rpt_dtls
> INTO r_exp_rpt_dtls;
> EXIT WHEN c_exp_rpt_dtls%NOTFOUND;
>
> IF r_exp_rpt_dtls.line_seq_no > lv_line_seq_no THEN
> lv_line_seq_no := r_exp_rpt_dtls.line_seq_no;
> END IF;
>
> lv_step_txt := 'Working on exp_rpt_dtls cnfr_no :'
> || r_exp_rpt_dtls.cnfr_no;
>
> -- Debug Control
> IF in_debugging THEN
> lv_err_txt := 'Time '
> || SYSDATE
> || lv_step_txt;
> lv_status :=
> WHSE_DEBUG_PKG.write_debug_log_file(lv_err_txt,
> lv_handle);
> END IF;
>
> BEGIN
> SELECT distinct ach_amt,
> tran_dt
> INTO lv_ach_amt,
> lv_tran_dt
> FROM ach_tran
> WHERE cnfr_no = lv_cnfr_no
> AND splt_pay_type = lv_pay_meth
> AND status = lv_pay_sts;
>
> EXCEPTION
> WHEN NO_DATA_FOUND THEN
> lv_ach_amt := NULL;
> lv_tran_dt := NULL;
>
> WHEN OTHERS THEN
> lv_sql_code := SQLCODE;
> lv_sql_msg := SQLERRM(lv_sql_code);
> lv_step_txt := 'Exception Thrown '
> || lv_sql_msg;
>
> -- Debug Control
> IF in_debugging THEN
>
> lv_err_txt := 'Time '
> || TO_CHAR(SYSDATE,'MM:DD:YYYY HH24:MI:SS')
> || lv_step_txt;
> lv_status :=
> WHSE_DEBUG_PKG.write_debug_log_file(lv_err_txt,
> lv_handle);
>
> END IF;
>
> --Log error
> GlobalError.LogSQLError(in_src_proc_no,
> lv_sql_code,
> lv_sql_msg,
> lv_step_txt);
>
> lv_err_cnt := lv_err_cnt + 1;
>
> IF MOD(lv_err_cnt,in_err_threshold) = 0 THEN
>
> lv_step_txt := c_proc_nm
> || ' failed';
> lv_err_txt := 'Time '
> || TO_CHAR(SYSDATE,'MM:DD:YYYY HH24:MI:SS')
> || lv_step_txt;
> lv_status :=
> WHSE_DEBUG_PKG.write_debug_log_file(lv_err_txt,
> lv_handle);
>
> --Run Log Stop
> GlobalError.RunLogStop2(in_src_proc_no,
> lv_run_log_no);
> lv_status :=
> WHSE_DEBUG_PKG.close_debug_log_file(lv_handle);
>
> RETURN FALSE;
> END IF;
> END;
> BEGIN
>
> INSERT INTO edm_exp_rpt_sts
> (cnfr_no,
> pay_meth,
> line_seq_no,
> dtl_seq_no,
> alloc_seq_no,
> pay_sts,
> pay_meth_descr,
> acct_no,
> sts_chng_dt,
> arrival_dt,
> src_proc_no,
> dm_load_dt,
> ach_amt,
> tran_dt)
> VALUES( r_exp_rpt_hdr.cnfr_no,
> r_exp_rpt_hdr.pay_type,
> r_exp_rpt_dtls.line_seq_no,
> r_exp_rpt_dtls.dtl_seq_no,
> r_exp_rpt_dtls.alloc_seq_no,
> r_exp_rpt_hdr.status,
> r_exp_rpt_dtls.descr,
> r_exp_rpt_hdr.acct_no,
> r_exp_rpt_hdr.upd_dt_tm,
> r_exp_rpt_hdr.arrv_dt_tm,
> in_src_proc_no,
> lv_sysdate,
> lv_ach_amt,
> lv_tran_dt);
>
> lv_row_cnt := lv_row_cnt + 1;
>
> IF MOD(lv_row_cnt,
> in_commit_interval) = 0 THEN
>
> COMMIT;
>
> END IF;
>
> EXCEPTION
> WHEN DUP_VAL_ON_INDEX THEN
> NULL;
> /*
> COMMIT;
>
> UPDATE edm_exp_rpt_sts
> SET ach_amt = lv_ach_amt,
> src_proc_no = in_src_proc_no,
> dm_load_dt = lv_sysdate,
> tran_dt = lv_tran_dt
> WHERE cnfr_no = lv_cnfr_no
> AND pay_sts = lv_pay_sts
> AND pay_meth = lv_pay_meth;
>
> COMMIT;*/
>
> WHEN OTHERS THEN
>
> lv_sql_code := SQLCODE;
> lv_sql_msg := SQLERRM(lv_sql_code);
>
> lv_step_txt :=
> 'Exception Thrown Exception report sts '
> || lv_sql_msg;
>
> --Log error
> GlobalError.LogSQLError(in_src_proc_no,
> lv_sql_code,
> lv_sql_msg,
> lv_step_txt);
>
> lv_err_cnt := lv_err_cnt + 1;
>
> IF MOD(lv_err_cnt,in_err_threshold) = 0 THEN
> lv_step_txt := c_proc_nm|| ' failed';
> lv_err_txt := 'Time '
> || SYSDATE
> || lv_step_txt;
> lv_status :=
>
> WHSE_DEBUG_PKG.write_debug_log_file(lv_err_txt,
>
> lv_handle);
>
> --Run Log Stop
> GlobalError.RunLogStop2(in_src_proc_no,
> lv_run_log_no);
> lv_status :=
>
> WHSE_DEBUG_PKG.close_debug_log_file(lv_handle);
>
> RETURN FALSE;
> END IF;
>
> -- Debug Control
> IF in_debugging THEN
> lv_err_txt := 'Time '
> || SYSDATE
> || lv_step_txt;
> lv_status :=
>
> WHSE_DEBUG_PKG.write_debug_log_file(lv_err_txt,
>
> lv_handle);
> END IF;
> END;
> END LOOP;
>
> CLOSE c_exp_rpt_dtls;
>
> END LOOP;
>
> close c_exp_rpt_hdr;
>
> IF lv_82 THEN
>
> loop_ctr := 0;
>
> LOOP
>
> loop_ctr := loop_ctr +1;
>
> IF lv_pay_sts_array(loop_ctr) IS NULL THEN
> EXIT;
> END IF;
>
> IF ((lv_pay_sts_array(loop_ctr) IS NOT NULL)
> AND (lv_sts_cnfr_no(loop_ctr) = lv_cnfr_no))
> THEN
> BEGIN
>
> INSERT INTO edm_exp_rpt_sts(
> cnfr_no,
> pay_meth,
> line_seq_no,
> dtl_seq_no,
> alloc_seq_no,
> pay_sts,
> pay_meth_descr,
> acct_no,
> sts_chng_dt,
> arrival_dt,
> src_proc_no,
> dm_load_dt,
> ach_amt,
> tran_dt)
> VALUES( r_exp_rpt_hdr.cnfr_no,
> 82,
> lv_line_seq_no+1,
> 1,
> 0,
> lv_pay_sts_array(loop_ctr),
> lv_82_descr,
> r_exp_rpt_hdr.acct_no,
> r_exp_rpt_hdr.upd_dt_tm,
> r_exp_rpt_hdr.arrv_dt_tm,
> in_src_proc_no,
> lv_sysdate,
> lv_ach_amt,
> lv_tran_dt );
>
> lv_row_cnt := lv_row_cnt + 1;
> IF MOD(lv_row_cnt,in_commit_interval) = 0 THEN
> COMMIT;
> END IF;
>
> EXCEPTION
> WHEN OTHERS THEN NULL;
> END;
> END IF;
> END LOOP;
> END IF;
>
> lv_step_txt := c_proc_nm
> || ' completed successfully';
> lv_err_txt := 'Time '
> || TO_CHAR(SYSDATE,'MM:DD:YYYY HH24:MI:SS')
> || lv_step_txt;
> lv_status :=
> WHSE_DEBUG_PKG.write_debug_log_file(lv_err_txt,
> lv_handle);
>
> --On success, lets log our results
> GlobalError.RunLogUnits2(in_src_proc_no,
> lv_run_log_no,
> lv_in_cnt,
> lv_row_cnt,
> lv_err_cnt);
>
> --Run Log Stop - Now pass back the run log id and indicate
completion
> GlobalError.RunLogStop2(in_src_proc_no,
> lv_run_log_no);
> lv_status :=
> WHSE_DEBUG_PKG.close_debug_log_file(lv_handle);
>
> --Success, let's tell that to who called us
> RETURN TRUE;
>
> EXCEPTION
> WHEN OTHERS THEN
> lv_sql_code := SQLCODE;
> lv_sql_msg := SQLERRM(lv_sql_code);
> lv_step_txt := 'Exception Thrown '
> || lv_sql_msg;
>
> -- Debug Control
> IF in_debugging THEN
>
> lv_err_txt := 'Time '
> || TO_CHAR(SYSDATE,'MM:DD:YYYY HH24:MI:SS')
> || lv_step_txt;
> lv_status :=
> WHSE_DEBUG_PKG.write_debug_log_file(lv_err_txt,
> lv_handle);
>
> END IF;
>
> --Log error
> GlobalError.LogSQLError(in_src_proc_no,
> lv_sql_code,
> lv_sql_msg,
> lv_step_txt);
>
> lv_err_cnt := lv_err_cnt + 1;
>
> lv_step_txt := c_proc_nm
> || ' failed';
> lv_err_txt := 'Time '
> || TO_CHAR(SYSDATE,'MM:DD:YYYY HH24:MI:SS')
> || lv_step_txt;
> lv_status :=
> WHSE_DEBUG_PKG.write_debug_log_file(lv_err_txt,
> lv_handle);
>
> --Run Log Stop
> GlobalError.RunLogStop2(in_src_proc_no,
> lv_run_log_no);
> lv_status :=
> WHSE_DEBUG_PKG.close_debug_log_file(lv_handle);
>
> RETURN FALSE;
>
> END exp_rpt_sts_load;
>
>
>
>
>
>
> SQL> desc exp_rpt_hdr
> Name Null? Type
> ----------------------------------------- --------
> ----------------------------
> CNFR_NO NOT NULL NUMBER(20)
> WH_DATE_KEY NOT NULL NUMBER(5)
> WH_MOD_DT_TM NOT NULL DATE
> WH_ROW_STS NOT NULL VARCHAR2(1)
> WH_ROW_EFF_DT_TM NOT NULL DATE
> MOD_GLOBAL_USER_NO NOT NULL NUMBER(15)
> TGT_CNFR_NO NOT NULL NUMBER(20)
> EXP_RPT_NO NUMBER(6)
> TRANS_TYPE NOT NULL VARCHAR2(1)
> TRANS_ID NOT NULL NUMBER(38)
> ACCT_NO NOT NULL NUMBER(9)
> GLOBAL_USER_NO NOT NULL NUMBER(15)
> INTL_REP_NO NOT NULL NUMBER(5)
> SUBMIT_DT_TM NOT NULL DATE
> START_DT NOT NULL DATE
> END_DT NOT NULL DATE
> EXP_RPT_STATUS NOT NULL VARCHAR2(1)
> ACCT_REV_NO NOT NULL NUMBER(18)
> UPD_DT_TM NOT NULL DATE
> ARRV_DT_TM NOT NULL DATE
> VER_NO VARCHAR2(7)
> EXE_VER_NO NUMBER(4,2)
> TITLE VARCHAR2(40)
> PURPOSE VARCHAR2(65)
> MSG_IND VARCHAR2(1)
> PRXY_CRTN_ID NUMBER(5)
> PRXY_SUBM_ID NUMBER(5)
> EXTL_ER_ID VARCHAR2(40)
> EXTL_TITLE VARCHAR2(40)
> EXTL_APPL_NAME VARCHAR2(40)
> EXTL_APPL_VER_ID VARCHAR2(40)
> DFLT_ORG_LVL1 VARCHAR2(20)
> DFLT_ORG_LVL2 VARCHAR2(20)
> DFLT_ORG_LVL3 VARCHAR2(20)
> DFLT_ORG_LVL4 VARCHAR2(20)
> REMARKS VARCHAR2(4000)
> IMAGED_IND VARCHAR2(1)
> AUDIT_TYPE VARCHAR2(1)
>
>
>
>
> SQL> desc exp_rpt_amt_type
> Name Null? Type
> ----------------------------------------- --------
> ----------------------------
> CNFR_NO NOT NULL NUMBER(20)
> PAY_TYPE NOT NULL NUMBER(3)
> WH_DATE_KEY NOT NULL NUMBER(5)
> WH_TIME_KEY NOT NULL NUMBER(5)
> WH_MOD_DT_TM NOT NULL DATE
> MOD_GLOBAL_USER_NO NOT NULL NUMBER(15)
> AMOUNT NOT NULL NUMBER(20,2)
> STATUS NOT NULL VARCHAR2(1)
> UPD_DT_TM NOT NULL DATE
>
>
>
>
> SQL> desc exp_rpt_alloc
> Name Null? Type
> ----------------------------------------- --------
> ----------------------------
> CNFR_NO NOT NULL NUMBER(20)
> LINE_SEQ_NO NOT NULL NUMBER(6)
> DTL_SEQ_NO NOT NULL NUMBER(6)
> ALLOC_SEQ_NO NOT NULL NUMBER(6)
> WH_DATE_KEY NOT NULL NUMBER(5)
> WH_MOD_DT_TM NOT NULL DATE
> MOD_GLOBAL_USER_NO NOT NULL NUMBER(15)
> UPD_DT_TM NOT NULL DATE
> ALLOC_AMT NOT NULL NUMBER(20,2)
> PROJ_NO VARCHAR2(40)
> ORG_LVL1 VARCHAR2(20)
> ORG_LVL2 VARCHAR2(20)
> ORG_LVL3 VARCHAR2(20)
> ORG_LVL4 VARCHAR2(20)
> ORG_LVL5 VARCHAR2(20)
> ORG_LVL6 VARCHAR2(20)
> ORG_LVL7 VARCHAR2(20)
> ORG_LVL8 VARCHAR2(20)
>
>
>
> SQL> desc acct_pay_type
> Name Null? Type
> ----------------------------------------- --------
> ----------------------------
> ACCT_NO NOT NULL NUMBER(9)
> REV_NO NOT NULL NUMBER(20)
> PAY_TYPE NOT NULL NUMBER(3)
> WH_DATE_KEY NOT NULL NUMBER(5)
> WH_TIME_KEY NOT NULL NUMBER(5)
> WH_MOD_DT_TM NOT NULL DATE
> WH_ROW_STS NOT NULL VARCHAR2(1)
> WH_ROW_EFF_DT_TM NOT NULL DATE
> MOD_GLOBAL_USER_NO NOT NULL NUMBER(15)
> UPD_DT_TM NOT NULL DATE
> DESCR NOT NULL VARCHAR2(20)
> STATUS NOT NULL VARCHAR2(1)
> GL_CODE VARCHAR2(12)
> REIMBURSE_IND VARCHAR2(1)
>
>
>
>
> SQL> desc exp_rpt_line_item_dtl
> Name Null? Type
> ----------------------------------------- --------
> ----------------------------
> CNFR_NO NOT NULL NUMBER(20)
> LINE_SEQ_NO NOT NULL NUMBER(6)
> DTL_SEQ_NO NOT NULL NUMBER(6)
> WH_DATE_KEY NOT NULL NUMBER(5)
> WH_MOD_DT_TM NOT NULL DATE
> MOD_GLOBAL_USER_NO NOT NULL NUMBER(15)
> EXP_CAT NOT NULL NUMBER(4)
> PAY_TYPE NOT NULL NUMBER(3)
> LINE_ITEM_DT NOT NULL DATE
> LINE_ITEM_AMT NOT NULL NUMBER(20,2)
> UPD_DT_TM NOT NULL DATE
> CURR_RATE NUMBER(17,8)
> CURR_AMT NUMBER(20,2)
> VENDOR VARCHAR2(30)
> LOCATION VARCHAR2(30)
> PURPOSE VARCHAR2(30)
> EXTL_LINE_SEQ_ID VARCHAR2(40)
> LINE_ITEM_REMARKS VARCHAR2(4000)
>
>
>
> SQL> desc ach_tran
> Name Null? Type
> ----------------------------------------- --------
> ----------------------------
> CNFR_NO NOT NULL NUMBER(20)
> SPLT_PAY_TYPE NOT NULL VARCHAR2(3)
> STATUS NOT NULL VARCHAR2(1)
> TRACE_NO NOT NULL NUMBER(10)
> WH_DATE_KEY NOT NULL NUMBER(5)
> WH_MOD_DT_TM NOT NULL DATE
> WH_ROW_STS NOT NULL VARCHAR2(1)
> WH_ROW_EFF_DT_TM NOT NULL DATE
> PROC_STATUS NOT NULL VARCHAR2(1)
> ACCT_NO NOT NULL NUMBER(9)
> INTL_REP_NO NOT NULL NUMBER(5)
> TRAN_DT NOT NULL DATE
> TRAN_TYPE NOT NULL VARCHAR2(1)
> STLMNT_DT NOT NULL DATE
> UPD_DT_TM NOT NULL DATE
> APP_NO NUMBER(10)
> ACH_AMT NUMBER(20,2)
> RET_REAS_CD VARCHAR2(3)
> BNK_ABA_ROUT_NO NUMBER(10)
> BNK_ACCT_NO VARCHAR2(17)
> BNK_TRAN_CODE VARCHAR2(2)
> ACH_CURR_TYPE VARCHAR2(1)
> PAYEE_NAME VARCHAR2(35)
> API_STATUS VARCHAR2(3)
> API_SUB_STATUS VARCHAR2(1)
> ENTRY_CLASS_IND VARCHAR2(1)
>
>
>
> SQL> desc edm_exp_rpt_sts
> Name Null? Type
> ----------------------------------------- --------
> ----------------------------
> CNFR_NO NOT NULL NUMBER(18)
> PAY_METH NOT NULL VARCHAR2(3)
> LINE_SEQ_NO NOT NULL NUMBER(6)
> DTL_SEQ_NO NOT NULL NUMBER(6)
> ALLOC_SEQ_NO NOT NULL NUMBER(6)
> PAY_STS NOT NULL VARCHAR2(1)
> PAY_METH_DESCR NOT NULL VARCHAR2(25)
> ACCT_NO NOT NULL NUMBER(9)
> STS_CHNG_DT NOT NULL DATE
> ARRIVAL_DT NOT NULL DATE
> SRC_PROC_NO NOT NULL NUMBER(10)
> DM_LOAD_DT NOT NULL DATE
> ACH_AMT NUMBER(20,2)
> TRAN_DT DATE
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author:
> INET: Cherie_Machler_at_gelco.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Yechiel Adar
  INET: adar76_at_inter.net.il

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).