**************** EXPLAIN_PLAN *************** SQL> EXPLAIN PLAN for select count(*) from USER_LEDGER_TRANS; Explained. SQL> select operation, options, object_name, id, parent_id 2 from plan_table; OPERATION OPTIONS OBJECT_NAME ID PARENT_ID ------------------------------ ------------ ------------------------ ---------- ---------- SELECT STATEMENT 0 SORT UNIQUE 1 0 NESTED LOOPS 2 1 NESTED LOOPS 3 2 HASH JOIN 4 3 NESTED LOOPS 5 4 INDEX UNIQUE SCAN REG_TTYPE_PK 6 5 TABLE ACCESS FULL TAS_RETURN_PERIODS 7 5 TABLE ACCESS FULL CBR_COLL_TRANS_DETAILS 8 4 INDEX RANGE SCAN CBR_COLLECTION_MODES_PK 9 3 TABLE ACCESS FULL USER_LEDGER_TRANS 10 2 MERGE JOIN OUTER 1 0 SORT JOIN 2 1 VIEW 3 2 SORT GROUP BY 4 3 TABLE ACCESS FULL J$CBR_COLL_TRANS_DETAILS 5 4 SORT JOIN 6 1 TABLE ACCESS BY ROWID CBR_COLL_TRANS_DETAILS 7 6 INDEX FULL SCAN CBR_COLLTRANS_PK 8 7 SELECT STATEMENT 0 MERGE JOIN OUTER 1 0 SORT JOIN 2 1 SELECT STATEMENT 0 VIEW 3 2 SORT GROUP BY 4 3 TABLE ACCESS FULL J$CBR_COLL_TRANS_DETAILS 5 4 SORT JOIN 6 1 TABLE ACCESS BY ROWID CBR_COLL_TRANS_DETAILS 7 6 INDEX FULL SCAN CBR_COLLTRANS_PK 8 7 SELECT STATEMENT 0 MERGE JOIN OUTER 1 0 SORT JOIN 2 1 VIEW 3 2 SORT GROUP BY 4 3 TABLE ACCESS FULL J$CBR_COLL_TRANS_DETAILS 5 4 SORT JOIN 6 1 TABLE ACCESS FULL CBR_COLL_TRANS_DETAILS 7 6 SELECT STATEMENT 0 MERGE JOIN OUTER 1 0 SORT JOIN 2 1 VIEW 3 2 SORT GROUP BY 4 3 TABLE ACCESS FULL J$CBR_COLL_TRANS_DETAILS 5 4 SORT JOIN 6 1 TABLE ACCESS FULL CBR_COLL_TRANS_DETAILS 7 6 SELECT STATEMENT 0 MERGE JOIN OUTER 1 0 SORT JOIN 2 1 VIEW 3 2 SORT GROUP BY 4 3 TABLE ACCESS FULL J$CBR_COLL_TRANS_DETAILS 5 4 SORT JOIN 6 1 TABLE ACCESS BY ROWID CBR_COLL_TRANS_DETAILS 7 6 INDEX FULL SCAN CBR_COLLTRANS_PK 8 7 SELECT STATEMENT 0 MERGE JOIN OUTER 1 0 SORT JOIN 2 1 VIEW 3 2 SORT GROUP BY 4 3 TABLE ACCESS FULL J$CBR_COLL_TRANS_DETAILS 5 4 SORT JOIN 6 1 TABLE ACCESS FULL CBR_COLL_TRANS_DETAILS 7 6 SELECT STATEMENT 0 MERGE JOIN OUTER 1 0 SORT JOIN 2 1 VIEW 3 2 SORT GROUP BY 4 3 TABLE ACCESS FULL J$CBR_COLL_TRANS_DETAILS 5 4 SORT JOIN 6 1 TABLE ACCESS FULL CBR_COLL_TRANS_DETAILS 7 6 SELECT STATEMENT 0 MERGE JOIN OUTER 1 0 SORT JOIN 2 1 VIEW 3 2 SORT GROUP BY 4 3 TABLE ACCESS FULL J$CBR_COLL_TRANS_DETAILS 5 4 SORT JOIN 6 1 TABLE ACCESS BY ROWID CBR_COLL_TRANS_DETAILS 7 6 INDEX FULL SCAN CBR_COLLTRANS_PK 8 7 SELECT STATEMENT 0 MERGE JOIN OUTER 1 0 SORT JOIN 2 1 VIEW 3 2 SORT GROUP BY 4 3 TABLE ACCESS FULL J$CBR_COLL_TRANS_DETAILS 5 4 SORT JOIN 6 1 TABLE ACCESS BY ROWID CBR_COLL_TRANS_DETAILS 7 6 INDEX FULL SCAN CBR_COLLTRANS_PK 8 7 SELECT STATEMENT 0 MERGE JOIN OUTER 1 0 SORT JOIN 2 1 VIEW 3 2 SORT GROUP BY 4 3 TABLE ACCESS FULL J$CBR_COLL_TRANS_DETAILS 5 4 SORT JOIN 6 1 TABLE ACCESS FULL CBR_COLL_TRANS_DETAILS 7 6 SELECT STATEMENT 0 MERGE JOIN OUTER 1 0 SORT JOIN 2 1 VIEW 3 2 SORT GROUP BY 4 3 TABLE ACCESS FULL J$CBR_COLL_TRANS_DETAILS 5 4 SORT JOIN 6 1 TABLE ACCESS FULL CBR_COLL_TRANS_DETAILS 7 6 SELECT STATEMENT 0 MERGE JOIN OUTER 1 0 SORT JOIN 2 1 VIEW 3 2 SORT GROUP BY 4 3 TABLE ACCESS FULL J$CBR_COLL_TRANS_DETAILS 5 4 SORT JOIN 6 1 TABLE ACCESS FULL CBR_COLL_TRANS_DETAILS 7 6 SELECT STATEMENT 0 MERGE JOIN OUTER 1 0 SORT JOIN 2 1 VIEW 3 2 SORT GROUP BY 4 3 TABLE ACCESS FULL J$CBR_COLL_TRANS_DETAILS 5 4 SORT JOIN 6 1 TABLE ACCESS FULL CBR_COLL_TRANS_DETAILS 7 6 SELECT STATEMENT 0 NESTED LOOPS OUTER 1 0 VIEW 2 1 SORT GROUP BY 3 2 TABLE ACCESS FULL J$CBR_COLL_TRANS_DETAILS 4 3 TABLE ACCESS BY ROWID CBR_COLL_TRANS_DETAILS 5 1 INDEX UNIQUE SCAN CBR_COLLTRANS_PK 6 5 SELECT STATEMENT 0 NESTED LOOPS OUTER 1 0 VIEW 2 1 SORT GROUP BY 3 2 TABLE ACCESS FULL J$CBR_COLL_TRANS_DETAILS 4 3 TABLE ACCESS BY ROWID CBR_COLL_TRANS_DETAILS 5 1 INDEX UNIQUE SCAN CBR_COLLTRANS_PK 6 5 SELECT STATEMENT 0 SORT AGGREGATE 1 0 TABLE ACCESS FULL TAS_TP_LEDGER_TRANS 2 1 SELECT STATEMENT 0 TABLE ACCESS FULL TAS_TP_LEDGER_TRANS 1 0 SELECT STATEMENT 0 SORT AGGREGATE 1 0 TABLE ACCESS FULL USER_LEDGER_TRANS 2 1 142 rows selected. SQL> SQL> ALTER SESSION SET SQL_TRACE = TRUE; Session altered. SQL> select count(*) from USER_LEDGER_TRANS; COUNT(*) ---------- 141704779 SQL> SQL> ==================== "abc_ora_13060.trc" [Read only] 57 lines, 2847 characters /appl1/home/oracle734/admin/abc/udump/abc_ora_13060.trc Oracle7 Server Release 7.3.4.5.0 - Production With the distributed and parallel query options PL/SQL Release 2.3.4.5.0 - Production ORACLE_HOME = /appl1/home/oracle734 System name: SunOS Node name: bir-ceb Release: 5.8 Version: Generic_Virtual Machine: sun4v Instance name: cebu Redo thread mounted by this instance: 1 Oracle process number: 567 Unix process pid: 13060, image: oraclecebu *** 2019-04-08 14:30:48.000 *** SESSION ID:(1072.305) 2019-04-08 14:30:48.000 ===================== PARSING IN CURSOR #1 len=34 dep=0 uid=0 oct=42 lid=0 tim=32777740 hv=2929342487 ad='89010300' ALTER SESSION SET SQL_TRACE = TRUE END OF STMT EXEC #1:c=0,e=0,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=32777740 ===================== PARSING IN CURSOR #2 len=87 dep=0 uid=0 oct=3 lid=0 tim=32777740 hv=1878801623 ad='8115e000' select substr(value,1,2) from v$nls_parameters where parameter='NLS_NUMERIC_CHARACTERS' END OF STMT PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=32777740 EXEC #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=32777740 FETCH #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=4,tim=32777740 STAT #2 id=1 cnt=5 pid=0 pos=0 obj=0 op='FIXED TABLE FULL X$NLS_PARAMETERS ' *** 2019-04-08 14:31:24.000 ===================== PARSING IN CURSOR #2 len=205 dep=1 uid=0 oct=3 lid=0 tim=32781343 hv=2855787161 ad='8abd8e34' select obj#,type,ctime,mtime,stime,status from obj$ where owner#=:1 and name=:2 and namespace=:3 and(remoteowner=:4 or remoteowner is null and :4 is null)and(linkname=:5 or linkname is null and :5 is null) END OF STMT PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=32781343 EXEC #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=32781343 FETCH #2:c=0,e=0,p=1,cr=2,cu=0,mis=0,r=0,dep=1,og=4,tim=32781343 ===================== PARSING IN CURSOR #3 len=124 dep=1 uid=0 oct=3 lid=0 tim=32781343 hv=3411629771 ad='8ab8794c' select privilege#,nvl(col#,0),max(nvl(option$,0))from objauth$ where obj#=:1 and grantee#=:2 group by privilege#,nvl(col#,0) END OF STMT PARSE #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=32781343 EXEC #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=32781343 FETCH #3:c=0,e=0,p=1,cr=3,cu=0,mis=0,r=0,dep=1,og=4,tim=32781343 ===================== PARSING IN CURSOR #1 len=40 dep=0 uid=0 oct=3 lid=0 tim=32781343 hv=2588922089 ad='849eedcc' select count(*) from USER_LEDGER_TRANS END OF STMT PARSE #1:c=0,e=0,p=2,cr=5,cu=0,mis=1,r=0,dep=0,og=4,tim=32781343 EXEC #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=32781344 *** 2019-04-08 14:32:12.000 FETCH #1:c=104,e=4775,p=30337,cr=30330,cu=2,mis=0,r=0,dep=0,og=4,tim=32786119 *** 2019-04-08 14:32:28.000 XCTEND rlbk=0, rd_only=1 STAT #1 id=1 cnt=0 pid=0 pos=0 obj=0 op='SORT AGGREGATE ' STAT #1 id=2 cnt=1848542 pid=1 pos=1 obj=17211 op='TABLE ACCESS FULL USER_LEDGER_TRANS ' ============================== host/appl1/home/oracle734> tkprof /appl1/home/oracle734/admin/abc/udump/abc_ora_13060.trc abc_tas_tp_ledger.log explain= / table=its.TAS_TP_LEDGER_TRANS TKPROF: Release 7.3.4.0.0 - Production on Mon Apr 8 15:32:19 2019 Copyright (c) Oracle Corporation 1979, 1996. All rights reserved. Trace file: /appl1/home/oracle734/admin/abc/udump/abc_ora_13060.trc Sort options: default ******************************************************************************** count = number of times OCI procedure was executed cpu = cpu time in seconds executing elapsed = elapsed time in seconds executing disk = number of physical reads of buffers from disk query = number of buffers gotten for consistent read current = number of buffers gotten in current mode (usually for update) rows = number of rows processed by the fetch or execute call ******************************************************************************** EXPLAIN PLAN option disabled. ******************************************************************************** ALTER SESSION SET SQL_TRACE = TRUE call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 0 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 1 0.00 0.00 0 0 0 0 Misses in library cache during parse: 0 Misses in library cache during execute: 1 Optimizer goal: CHOOSE Parsing user id: SYS ******************************************************************************** select substr(value,1,2) from v$nls_parameters where parameter='NLS_NUMERIC_CHARACTERS' call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.00 0.00 0 0 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.00 0 0 0 1 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: SYS ******************************************************************************** select obj#,type,ctime,mtime,stime,status from obj$ where owner#=:1 and name=:2 and namespace=:3 and(remoteowner=:4 or remoteowner is null and :4 is null)and(linkname=:5 or linkname is null and :5 is null) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.00 0.00 1 2 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.00 1 2 0 0 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 1) ******************************************************************************** select privilege#,nvl(col#,0),max(nvl(option$,0)) from objauth$ where obj#=:1 and grantee#=:2 group by privilege#,nvl(col#,0) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.00 0.00 1 3 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.00 1 3 0 0 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 1) ******************************************************************************** select count(*) from USER_LEDGER_TRANS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 1.04 47.75 30337 30330 2 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 1.04 47.75 30337 30330 2 0 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: SYS ******************************************************************************** OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.00 0.00 0 0 0 0 Execute 3 0.00 0.00 0 0 0 0 Fetch 2 1.04 47.75 30337 30330 2 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 7 1.04 47.75 30337 30330 2 1 Misses in library cache during parse: 2 Misses in library cache during execute: 1 OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.00 0.00 0 0 0 0 Execute 2 0.00 0.00 0 0 0 0 Fetch 2 0.00 0.00 2 5 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 6 0.00 0.00 2 5 0 0 Misses in library cache during parse: 0 3 user SQL statements in session. 2 internal SQL statements in session. 5 SQL statements in session. 0 statements EXPLAINed in this session. ******************************************************************************** Trace file: /appl1/home/oracle734/admin/abc/udump/abc_ora_13060.trc Trace file compatibility: 7.03.02 Sort options: default 1 session in tracefile. 3 user SQL statements in trace file. 2 internal SQL statements in trace file. 5 SQL statements in trace file. 5 unique SQL statements in trace file. 57 lines in trace file. *************** DDLs ***************** CREATE TABLE abc.USER_LEDGER_TRANS ( TIN VARCHAR2(9) NOT NULL, BRANCH_CODE VARCHAR2(3) NOT NULL, TAX_CODE VARCHAR2(2) NOT NULL, RETRN_PERIOD DATE NOT NULL, RETRN_PERIOD_SEQ_NUM NUMBER(3) NOT NULL, TRANS_SEQ_NO NUMBER(4) NOT NULL, TRANS_CODE VARCHAR2(3) NOT NULL, TRANS_DATE DATE NOT NULL, TRANS_AMOUNT NUMBER(14,2) NOT NULL, STATUS_FLAG VARCHAR2(1) NOT NULL, REASON_CODE VARCHAR2(3) NOT NULL, REF_NO VARCHAR2(16) NOT NULL, POSTED_BY VARCHAR2(12) NOT NULL, POSTING_DATE DATE NOT NULL, RETRN_SEQ_NUM NUMBER(15), RECEIVING_RDO VARCHAR2(3), COLL_AGENT_TYPE VARCHAR2(1), COLL_AGENT_CODE VARCHAR2(6), COLL_DATE DATE, COLL_MUN_CODE VARCHAR2(5), MP_CODE VARCHAR2(1), ROR_NUMBER VARCHAR2(15), PENALTY_CODE VARCHAR2(2), PENALTY_TYPE VARCHAR2(1), DC_TRANS_SEQ_NO NUMBER(4), DC_PAYMENT_SEQ_NO NUMBER(4), DC_DATE_REDEEMED DATE, CLAIM_SEQ_NO NUMBER(2), MODIFIED_BY VARCHAR2(12), DATE_MODIFIED DATE ) TABLESPACE USR_DATA_LRG PCTUSED 60 PCTFREE 20 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 2000M NEXT 20M MINEXTENTS 1 MAXEXTENTS 1200 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 ) NOCACHE PARALLEL ( DEGREE 1 INSTANCES 1 ); COMMENT ON TABLE abc.USER_LEDGER_TRANS IS 'Created from Entity USER LEDGER TRANSACTION by JOJON on 25-MAY-95'; CREATE INDEX abc.USER_LEDGER_TRANS_N2 ON abc.USER_LEDGER_TRANS (TRANS_DATE) TABLESPACE USR_IDX_LRG PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 2000M NEXT 10M MINEXTENTS 1 MAXEXTENTS 500 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 ) PARALLEL ( DEGREE 1 INSTANCES 1 ); CREATE UNIQUE INDEX abc.USER_LEDGER_TRANS_PK ON abc.USER_LEDGER_TRANS (TIN, BRANCH_CODE, TAX_CODE, RETRN_PERIOD, RETRN_PERIOD_SEQ_NUM, TRANS_SEQ_NO) TABLESPACE USR_IDX_LRG PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 2000M NEXT 10M MINEXTENTS 1 MAXEXTENTS 900 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 ) PARALLEL ( DEGREE 1 INSTANCES 1 ); CREATE INDEX abc.USER_LEDGER_TRANS_N1 ON abc.USER_LEDGER_TRANS (TRANS_CODE) TABLESPACE USR_IDX_LRG PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 1280M NEXT 10M MINEXTENTS 1 MAXEXTENTS 500 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 ) PARALLEL ( DEGREE 1 INSTANCES 1 ); CREATE OR REPLACE TRIGGER abc.USER_LEDGER_TRANS_RAMTRG AFTER INSERT OR UPDATE OR DELETE ON USER_LEDGER_TRANS REFERENCING OLD AS OLD FOR EACH ROW DECLARE IO_RETCODE NUMBER; TIME_NOW VARCHAR2(30); DMLTYPE CHAR; KEY_VALUE VARCHAR2(250); FLAG CHAR; V_FILLER LONG; USER_ID VARCHAR2(30); V_HOME_RDC VARCHAR2(3); RDC_VAL VARCHAR2(3); /* Declare constant */ FAIL_CODE CONSTANT NUMBER := 100; BEGIN FLAG := 'N'; V_HOME_RDC := NULL; select TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')||LPAD(TEC_TIME_SEQ.NEXTVAL,8,0) into TIME_NOW from SYS.DUAL; select USER into USER_ID from SYS.DUAL; select VALUE into RDC_VAL from TEC_SYSTEM_PARMS where PARAMETER = 'OFFICE CODE'; IF USER_ID = 'TASCLNUSER' THEN IF RDC_VAL = '001' THEN V_HOME_RDC := '101'; ELSIF RDC_VAL = '002' THEN V_HOME_RDC := '102'; ELSIF RDC_VAL = '003' THEN V_HOME_RDC := '103'; ELSIF RDC_VAL = '008' THEN V_HOME_RDC := '104'; ELSIF RDC_VAL = '009' THEN V_HOME_RDC := '105'; END IF; END IF; IF INSERTING THEN ROLL_UP_TRANS_TOTALS_PROC ( :NEW.TIN, :NEW.BRANCH_CODE, :NEW.TAX_CODE, :NEW.RETRN_PERIOD, :NEW.RETRN_PERIOD_SEQ_NUM, :NEW.TRANS_CODE, :NEW.REASON_CODE, :NEW.PENALTY_TYPE, :NEW.TRANS_AMOUNT, :NEW.TRANS_DATE, IO_RETCODE); KEY_VALUE := '~'|| :NEW.TIN||'~'|| :NEW.BRANCH_CODE||'~'|| :NEW.TAX_CODE||'~'|| :NEW.RETRN_PERIOD||'~'|| :NEW.RETRN_PERIOD_SEQ_NUM||'~'|| :NEW.TRANS_SEQ_NO||'~'; DMLTYPE := 'I'; FLAG := 'Y'; V_FILLER := '~'|| :NEW.TIN||'~'|| :NEW.BRANCH_CODE||'~'|| :NEW.TAX_CODE||'~'|| :NEW.RETRN_PERIOD||'~'|| :NEW.RETRN_PERIOD_SEQ_NUM||'~'|| :NEW.TRANS_SEQ_NO||'~'|| :NEW.TRANS_CODE||'~'|| :NEW.TRANS_DATE||'~'|| :NEW.TRANS_AMOUNT||'~'|| :NEW.STATUS_FLAG||'~'|| :NEW.REASON_CODE||'~'|| :NEW.REF_NO||'~'|| :NEW.POSTED_BY||'~'|| :NEW.POSTING_DATE||'~'|| :NEW.RETRN_SEQ_NUM||'~'|| :NEW.RECEIVING_RDO||'~'|| :NEW.COLL_AGENT_TYPE||'~'|| :NEW.COLL_AGENT_CODE||'~'|| :NEW.COLL_DATE||'~'|| :NEW.COLL_MUN_CODE||'~'|| :NEW.MP_CODE||'~'|| :NEW.ROR_NUMBER||'~'|| :NEW.PENALTY_CODE||'~'|| :NEW.PENALTY_TYPE||'~'|| :NEW.DC_TRANS_SEQ_NO||'~'|| :NEW.DC_PAYMENT_SEQ_NO||'~'|| :NEW.DC_DATE_REDEEMED||'~'|| :NEW.CLAIM_SEQ_NO||'~'|| :NEW.MODIFIED_BY||'~'|| :NEW.DATE_MODIFIED||'~'; -- SIR#4881 start IF IO_RETCODE = FAIL_CODE THEN RAISE_APPLICATION_ERROR(-20225, 'No Data found'); END IF; -- SIR#4881 end ELSIF DELETING THEN KEY_VALUE := '~'|| :OLD.TIN||'~'|| :OLD.BRANCH_CODE||'~'|| :OLD.TAX_CODE||'~'|| :OLD.RETRN_PERIOD||'~'|| :OLD.RETRN_PERIOD_SEQ_NUM||'~'|| :OLD.TRANS_SEQ_NO||'~'; DMLTYPE := 'D'; FLAG := 'Y'; ELSIF UPDATING THEN KEY_VALUE := '~'|| :OLD.TIN||'~'|| :OLD.BRANCH_CODE||'~'|| :OLD.TAX_CODE||'~'|| :OLD.RETRN_PERIOD||'~'|| :OLD.RETRN_PERIOD_SEQ_NUM||'~'|| :OLD.TRANS_SEQ_NO||'~'; DMLTYPE := 'U'; V_FILLER := '~'|| :NEW.TIN||'~'|| :NEW.BRANCH_CODE||'~'|| :NEW.TAX_CODE||'~'|| :NEW.RETRN_PERIOD||'~'|| :NEW.RETRN_PERIOD_SEQ_NUM||'~'|| :NEW.TRANS_SEQ_NO||'~'|| :NEW.TRANS_CODE||'~'|| :NEW.TRANS_DATE||'~'|| :NEW.TRANS_AMOUNT||'~'|| :NEW.STATUS_FLAG||'~'|| :NEW.REASON_CODE||'~'|| :NEW.REF_NO||'~'|| :NEW.POSTED_BY||'~'|| :NEW.POSTING_DATE||'~'|| :NEW.RETRN_SEQ_NUM||'~'|| :NEW.RECEIVING_RDO||'~'|| :NEW.COLL_AGENT_TYPE||'~'|| :NEW.COLL_AGENT_CODE||'~'|| :NEW.COLL_DATE||'~'|| :NEW.COLL_MUN_CODE||'~'|| :NEW.MP_CODE||'~'|| :NEW.ROR_NUMBER||'~'|| :NEW.PENALTY_CODE||'~'|| :NEW.PENALTY_TYPE||'~'|| :NEW.DC_TRANS_SEQ_NO||'~'|| :NEW.DC_PAYMENT_SEQ_NO||'~'|| :NEW.DC_DATE_REDEEMED||'~'|| :NEW.CLAIM_SEQ_NO||'~'|| :NEW.MODIFIED_BY||'~'|| :NEW.DATE_MODIFIED||'~'; FLAG := 'Y'; END IF; IF FLAG = 'Y' THEN insert into TEC_DT_UNLOAD values ('USER_LEDGER_TRANS', '~TIN~BRANCH_CODE~TAX_CODE~RETRN_PERIOD~RETRN_PERIOD_SEQ_NUM~TRANS_SEQ_NO~', KEY_VALUE, DMLTYPE, V_HOME_RDC, TIME_NOW, V_FILLER); END IF; EXCEPTION WHEN OTHERS THEN dbms_output.put_line('SQLCODE: '||SQLCODE); END; /************************ * end of trigger script* ************************/ / CREATE OR REPLACE TRIGGER abc.USER_LEDGER_TRANS_RBMTRG BEFORE INSERT OR UPDATE OR DELETE ON abc.USER_LEDGER_TRANS FOR EACH ROW DECLARE IP_ADDR VARCHAR2(20); MOD_ID VARCHAR2(8); KEY_VALUES VARCHAR2(2000); DMLTYPE VARCHAR2(1); BEGIN IP_ADDR := TEC_GET_IP_ADDRESS; MOD_ID := TEC_GET_MOD_ID; IF UPDATING THEN :NEW.MODIFIED_BY := USER; :NEW.DATE_MODIFIED := TEC_SYS_DATE; DMLTYPE := 'U'; KEY_VALUES := :OLD.TIN || ',' || :OLD.BRANCH_CODE || ',' || :OLD.TAX_CODE || ',' || :OLD.RETRN_PERIOD || ',' || :OLD.RETRN_PERIOD_SEQ_NUM || ',' || :OLD.TRANS_SEQ_NO || ',' || :OLD.TRANS_AMOUNT || ',' || :NEW.TRANS_AMOUNT ; ELSIF DELETING THEN DMLTYPE := 'D'; KEY_VALUES := :OLD.TIN || ',' || :OLD.BRANCH_CODE || ',' || :OLD.TAX_CODE || ',' || :OLD.RETRN_PERIOD || ',' || :OLD.RETRN_PERIOD_SEQ_NUM || ',' || :OLD.TRANS_SEQ_NO || ',' || :OLD.TRANS_AMOUNT ; ELSIF INSERTING THEN DMLTYPE := 'I'; KEY_VALUES := :NEW.TIN || ',' || :NEW.BRANCH_CODE || ',' || :NEW.TAX_CODE || ',' || :NEW.RETRN_PERIOD || ',' || :NEW.RETRN_PERIOD_SEQ_NUM || ',' || :NEW.TRANS_SEQ_NO || ',' || :NEW.TRANS_AMOUNT ; END IF; IF TEC_GLOBAL.AUDIT_MODE IS NULL OR TEC_GLOBAL.AUDIT_MODE != 'N' THEN insert into TEC_AUDIT_TRAILS (USER_ID, TERMINAL_ID, DATE_CREATED, TRANSACTION_TYPE, MODULE_ID, TABLE_NAME, KEY_VALUES) values (USER, IP_ADDR, TEC_SYS_DATE, DMLTYPE, MOD_ID, 'USER_LEDGER_TRANS', KEY_VALUES); END IF; END; / CREATE PUBLIC SYNONYM USER_LEDGER_TRANS FOR abc.USER_LEDGER_TRANS; ALTER TABLE abc.USER_LEDGER_TRANS ADD ( CONSTRAINT USER_LEDGER_TRANS_PK PRIMARY KEY (TIN, BRANCH_CODE, TAX_CODE, RETRN_PERIOD, RETRN_PERIOD_SEQ_NUM, TRANS_SEQ_NO) USING INDEX TABLESPACE USR_IDX_LRG PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 2000M NEXT 10M MINEXTENTS 1 MAXEXTENTS 900 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 )); ALTER TABLE abc.USER_LEDGER_TRANS ADD ( CONSTRAINT USER_LEDGER_TRANS_FK FOREIGN KEY (TIN, BRANCH_CODE, TAX_CODE, RETRN_PERIOD, RETRN_PERIOD_SEQ_NUM) REFERENCES abc.TAS_RETURN_PERIODS (TIN,BRANCH_CODE,TAX_CODE,RETRN_PERIOD,RETRN_PERIOD_SEQ_NUM), CONSTRAINT USER_LEDGER_TRANS_FK2 FOREIGN KEY (REASON_CODE) REFERENCES CODES.TAS_REASON_TYPES (REASON_CODE), CONSTRAINT USER_LEDGER_TRANS_FK3 FOREIGN KEY (TRANS_CODE) REFERENCES CODES.TAS_TRANSACTION_TYPES (TRANS_CODE), CONSTRAINT USER_LEDGER_TRANS_FK4 FOREIGN KEY (PENALTY_TYPE, PENALTY_CODE) REFERENCES CODES.TAS_PENALTY_TYPES (PENALTY_TYPE,PENALTY_CODE), CONSTRAINT USER_LEDGER_TRANS_FK6 FOREIGN KEY (COLL_AGENT_TYPE, COLL_AGENT_CODE, RECEIVING_RDO, COLL_MUN_CODE) REFERENCES CODES.CBR_COLL_AGENT_ASSIGNS (COLL_AGENT_TYPE,COLL_AGENT_CODE,RDO_CODE,BIR_MUN_CODE)); GRANT DELETE, INSERT, SELECT, UPDATE ON abc.USER_LEDGER_TRANS TO ARCHITECTURE; GRANT REFERENCES, SELECT ON abc.USER_LEDGER_TRANS TO PUBLIC WITH GRANT OPTION; GRANT DELETE, INSERT, SELECT, UPDATE ON abc.USER_LEDGER_TRANS TO UPDATEALLOWED;