Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Long-running PL/SQL function (long)
I think that you can try 2 things:
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).Received on Tue Jan 07 2003 - 12:29:17 CST
![]() |
![]() |