Hi Cherie,
Using pl/sql tables and bulk binding will increase the speed dramatically with both insert and cursor fetching. I've done it myself many times with runaway success.
Also, lookup tables can be cached in the procedure to avoid going to disk over and over again. I don't remember the size of acct_pay_type but that lookup for the warehouse dates is a candidate.
However it hasn't been over a db link. Do you think the db link is the problem?
Gotta love the select distinct from ACH_TRAN. That's one of the huge tables, right?
Gosh, good luck. Looks like the way the team writes code up there hasn't changed. Seriously the developers need to take a step forward and look at advanced features in pl/sql instead of banging out a bunch of code and sending it to you and saying, "What's the problem? Fix the database."
Bottom line is, a dba can't compensate for a botched design. It will bite over and over...
Lisa Koivu
Oracle Database Administrator
Fairfield Resorts, Inc.
5259 Coconut Creek Parkway
Ft. Lauderdale, FL, USA 33063
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, January 07, 2003 12:26 PM
To: Multiple recipients of list ORACLE-L
Subject: Long-running PL/SQL function (long)
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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
