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).
