2 Replies Latest reply on Aug 3, 2019 5:59 PM by Robeen

    keycols question

    Robeen

      Oracle Database 12.2.0 (target)

      Oracle Database 12.1.0 (source)

       

      Hello Team,

       

      From the extract report file, I am getting warning

       

      2019-07-31 14:23:25  INFO    OGG-06509  Using the following key columns for source table CBS_CORE.CB_SUB_AR_AP:

      TRANS_NUM_V, SERIAL_NUM_N, ACCOUNT_LINK_CODE_N, SUBSIDIARY_CODE_V

       

      But application team says the fields should be used as unique key index (keycols)

      TRANS_TYPE_V,TRANS_NUM_V,SERIAL_NUM_N,ACCOUNT_LINK_CODE_N

       

      Please advise if I use the keycols fields provided by application team in the replicat parameter file for that table.

       

      Regards,

       

      Joe

        • 1. Re: keycols question
          ORASCN

          Hi Joe,

           

          Please share the below command output from the Source,

           

          ggsci >dblogin.....

           

          ggsci >info trandata CBS_CORE.CB_SUB_AR_AP

           

          Need the Create DDL of the above table as well.

           

          Regards,

          Veera

          • 2. Re: keycols question
            Robeen

            Hi Veera,

             

            please find below the output

             

            1. Trandata output

             

            GGSCI (RH-DATWHSE-DEV.mtg.local) 3> dblogin useridalias oggscb_dwh

            Successfully logged into database.

             

            GGSCI (RH-DATWHSE-DEV.mtg.local as extractogg@cbs) 4> info trandata CBS_CORE.CB_SUB_AR_AP

             

            Logging of supplemental redo log data is enabled for table CBS_CORE.CB_SUB_AR_AP.

             

            Columns supplementally logged for table CBS_CORE.CB_SUB_AR_AP: "ACCOUNT_LINK_CODE_N", "SERIAL_NUM_N", "SUBSIDIARY_CODE_V", "TRANS_NUM_V".

             

            Prepared CSN for table CBS_CORE.CB_SUB_AR_AP: 256740701697

             

            2. DDL output:

             

            DROP TABLE CBS_CORE.CB_SUB_AR_AP CASCADE CONSTRAINTS;

             

             

            CREATE TABLE CBS_CORE.CB_SUB_AR_AP

            (

              TRANS_NUM_V               VARCHAR2(20 BYTE),

              SERIAL_NUM_N              NUMBER(2)           DEFAULT 0,

              TRANS_DATE_D              TIMESTAMP(6) WITH TIME ZONE,

              ACCOUNT_LINK_CODE_N       NUMBER(10),

              MAIN_ACCOUNT_LINK_CODE_N  NUMBER(10),

              SUBSIDIARY_CODE_V         VARCHAR2(10 BYTE),

              DB_CR_V                   VARCHAR2(1 BYTE),

              OVERALL_AMT_N             NUMBER(14)          DEFAULT 0,

              CLEARED_AMT_N             NUMBER(14)          DEFAULT 0,

              TEMP_CLR_AMT_N            NUMBER(14)          DEFAULT 0,

              OLD_YR_ADJUST_AMT_N       NUMBER(14)          DEFAULT 0,

              BILL_CLEARED_FLG_V        VARCHAR2(1 BYTE),

              DUE_DATE_D                TIMESTAMP(6) WITH TIME ZONE,

              TRANSFERRED_AMT_N         NUMBER(14),

              SALE_TYPE_V               VARCHAR2(1 BYTE),

              LAST_MODIFIED_DATE_D      TIMESTAMP(6) WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,

              DEBTORS_GL_CODE_N         NUMBER(10),

              SUPPLEMENTAL LOG GROUP GGS_129571 (TRANS_NUM_V,SERIAL_NUM_N,ACCOUNT_LINK_CODE_N,SUBSIDIARY_CODE_V) ALWAYS,

              SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS,

              SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS,

              SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS

            )

            TABLESPACE CBS

            PCTUSED    0

            PCTFREE    10

            INITRANS   1

            MAXTRANS   255

            STORAGE    (

                        INITIAL          80K

                        NEXT             1M

                        MINEXTENTS       1

                        MAXEXTENTS       UNLIMITED

                        PCTINCREASE      0

                        BUFFER_POOL      DEFAULT

                       )

            LOGGING

            NOCOMPRESS

            NOCACHE

            NOPARALLEL

            MONITORING;

             

             

            COMMENT ON COLUMN CBS_CORE.CB_SUB_AR_AP.MAIN_ACCOUNT_LINK_CODE_N IS 'ACCOUNT LINK NUMBER OF CB_ACCOUNT RECORD';

             

             

            COMMENT ON COLUMN CBS_CORE.CB_SUB_AR_AP.TEMP_CLR_AMT_N IS 'AMOUNT HELD BY UNAUTHORISED TRANS';

             

             

            COMMENT ON COLUMN CBS_CORE.CB_SUB_AR_AP.TRANSFERRED_AMT_N IS 'HOW MUCH AMOUNT IS TRANSFERRED TO OTHER ACCOUNT';

             

             

            COMMENT ON COLUMN CBS_CORE.CB_SUB_AR_AP.DEBTORS_GL_CODE_N IS 'DEBTORS GL CODE';

             

             

             

             

             

             

            CREATE INDEX CBS_CORE.CB_SUB_AR_AP#IND2 ON CBS_CORE.CB_SUB_AR_AP

            (ACCOUNT_LINK_CODE_N)

            LOGGING

            TABLESPACE CBS_TAB

            PCTFREE    10

            INITRANS   2

            MAXTRANS   255

            STORAGE    (

                        INITIAL          64K

                        NEXT             1M

                        MINEXTENTS       1

                        MAXEXTENTS       UNLIMITED

                        PCTINCREASE      0

                        BUFFER_POOL      DEFAULT

                       )

            NOPARALLEL;

             

             

             

             

            CREATE UNIQUE INDEX CBS_CORE.CB_SUB_AR_AP$1 ON CBS_CORE.CB_SUB_AR_AP

            (TRANS_NUM_V, SERIAL_NUM_N, SUBSIDIARY_CODE_V, ACCOUNT_LINK_CODE_N)

            LOGGING

            TABLESPACE CBS_TAB

            PCTFREE    10

            INITRANS   2

            MAXTRANS   255

            STORAGE    (

                        INITIAL          64K

                        NEXT             1M

                        MINEXTENTS       1

                        MAXEXTENTS       UNLIMITED

                        PCTINCREASE      0

                        BUFFER_POOL      DEFAULT

                       )

            PARALLEL ( DEGREE 8 INSTANCES 1 );

             

             

             

             

            CREATE INDEX CBS_CORE.CB_SUB_AR_AP$2 ON CBS_CORE.CB_SUB_AR_AP

            (ACCOUNT_LINK_CODE_N, SYS_EXTRACT_UTC("TRANS_DATE_D"))

            LOGGING

            TABLESPACE CBS_TAB

            PCTFREE    10

            INITRANS   2

            MAXTRANS   255

            STORAGE    (

                        INITIAL          64K

                        NEXT             1M

                        MINEXTENTS       1

                        MAXEXTENTS       UNLIMITED

                        PCTINCREASE      0

                        BUFFER_POOL      DEFAULT

                       )

            NOPARALLEL;

             

             

             

             

            CREATE INDEX CBS_CORE.CB_SUB_AR_AP$3 ON CBS_CORE.CB_SUB_AR_AP

            (MAIN_ACCOUNT_LINK_CODE_N, SYS_EXTRACT_UTC("TRANS_DATE_D"))

            LOGGING

            TABLESPACE CBS_TAB

            PCTFREE    10

            INITRANS   2

            MAXTRANS   255

            STORAGE    (

                        INITIAL          64K

                        NEXT             1M

                        MINEXTENTS       1

                        MAXEXTENTS       UNLIMITED

                        PCTINCREASE      0

                        BUFFER_POOL      DEFAULT

                       )

            NOPARALLEL;

             

             

             

             

            CREATE INDEX CBS_CORE.IDX_SUB_AR_AP_ACC_TRAN_SN ON CBS_CORE.CB_SUB_AR_AP

            (MAIN_ACCOUNT_LINK_CODE_N, TRANS_NUM_V, SERIAL_NUM_N)

            LOGGING

            TABLESPACE CBS_TAB

            PCTFREE    10

            INITRANS   2

            MAXTRANS   255

            STORAGE    (

                        INITIAL          64K

                        NEXT             1M

                        MINEXTENTS       1

                        MAXEXTENTS       UNLIMITED

                        PCTINCREASE      0

                        BUFFER_POOL      DEFAULT

                       )

            NOPARALLEL;

             

             

             

             

            CREATE OR REPLACE TRIGGER CBS_CORE.UPDT_SRV_TRANS_TO_INTERFACE

            BEFORE INSERT ON CBS_CORE.CB_SUB_AR_AP FOR EACH ROW

            WHEN (

            SUBSTR(NEW.TRANS_NUM_V, 1,1) != 'F' OR SUBSTR(OLD.TRANS_NUM_V, 1,1) != 'F'

                  )

            DECLARE

                 /*

                 This process will post the credit/debit amounts  to CS5 through EMA

                 */

             

             

                 l_err_code_v                 VARCHAR(15);

                 l_err_mesg_v                 VARCHAR2(200);

                 l_success_flag_n             NUMBER(1) := 1;

                 l_account_code_n             NUMBER(10);

                 l_account_type_v             VARCHAR2(3);

                 l_service_code_v             VARCHAR2(3);

                 l_preferred_curr_code_v      VARCHAR2(5);

                 l_entity_type_v              VARCHAR2(3);

                 l_main_account_link_code_n   NUMBER(10);

                 l_account_link_code_n        NUMBER(10);

                 l_service_info_v             VARCHAR2(50);

                 l_trans_type_v               VARCHAR2(1);

                 l_mul_applicable_flg_v       VARCHAR2(1):= 'N';

                 l_string_v                   VARCHAR2(7);

                 l_shrd_indvl_flag_v          VARCHAR2(1);

                 l_adv_trans_cnt_n            NUMBER;

                 l_status_code_v              VARCHAR2(2);

                 l_posting_system_v           VARCHAR2(255);

                 l_inv_srv_optn_v             VARCHAR2(1);

                 l_update_uc2_v               VARCHAR2(1) := 'N';

                 l_set_uc_required_v          VARCHAR2(1) := CHECK_FEATURE_REQUIRED('FIN', 'MUL_UC_SET_FLAG');

                 l_trans_num_v                VARCHAR2(20);

                 l_xml_ip                     XMLTYPE;

                 l_quit_e                     EXCEPTION;

               --  l_sim_num_v                  gsm_service_mast.sim_num_v%TYPE; -- added by Babu #IR188641

                -- l_imsi_num_n                 gsm_sims_master.imsi_num_n%TYPE; -- added by Babu #IR188641

             

             

            FUNCTION IS_SERVICE_LIVE (ip_service_code_v VARCHAR2)

            RETURN VARCHAR2

            AS

                l_live_flag_v    VARCHAR2(1);

             

             

            BEGIN

             

             

                SELECT NVL(LIVE_FLAG_V, 'N')

                INTO   l_live_flag_v

                FROM   CB_SERVICES

                WHERE  SERVICE_CODE_V = ip_service_code_v ;

             

             

                RETURN l_live_flag_v;

             

             

            END;

            /***************************************************************************************************

                Module       : Finance

                Author       :

                Purpose      : This process will post the credit/debit amounts  to CS5 through EMA

                Created on   :

                Created by   :

             

             

                REVISIONS:

                Version    |     Date       |     Author            |      Description

                ---------  | ----------     |   ---------------     |  ----------------------------

                  1.0      |                |    Satheesha DB       |     Initial version   

                  1.1      |  17-Jan-2013   |    Babu Prasad        |     Code Allignment        

            /****************************************************************************************************/   

            BEGIN

             

             

                BEGIN

             

             

                    SELECT KEY_VALUE_CHAR_V

                    INTO   l_posting_system_v

                    FROM   CB_CONTROL_KEYS

                    WHERE  KEY_CODE_V = 'POST_FM_TRANS';

             

             

                EXCEPTION

                    WHEN NO_DATA_FOUND THEN

                         l_posting_system_v := 'NONE';

             

             

                END;                          

               

                IF l_posting_system_v = 'CS5' THEN

             

             

                    IF INSERTING THEN

                   

                       l_trans_type_v := SUBSTR(:NEW.TRANS_NUM_V, 1, 1);

                       l_main_account_link_code_n := :NEW.MAIN_ACCOUNT_LINK_CODE_N;

                       l_account_link_code_n := :NEW.ACCOUNT_LINK_CODE_N;

                       l_trans_num_v         := :NEW.TRANS_NUM_V;

                      

                    ELSE 

                   

                        l_trans_type_v := SUBSTR(:OLD.TRANS_NUM_V, 1, 1);

                        l_main_account_link_code_n := :OLD.MAIN_ACCOUNT_LINK_CODE_N;

                        l_account_link_code_n := :OLD.ACCOUNT_LINK_CODE_N;

                        l_trans_num_v := :OLD.TRANS_NUM_V;

                   

                    END IF; 

                   

                    l_err_code_v := 'bal_upd:01';

                    l_err_mesg_v := 'Error in getting entity_type_v from CB_ACC_BALNC';

             

             

                    SELECT ENTITY_TYPE_V

                    INTO   l_entity_type_v

                    FROM   CB_ACC_BALNC

                    WHERE  ACCOUNT_LINK_CODE_N = l_main_account_link_code_n;

             

             

                    IF l_entity_type_v != 'SUB' THEN

             

             

                       GOTO END_PROCESS;

             

             

                    END IF;                 

             

             

                    l_err_code_v := 'bal_upd:02';

                    l_err_mesg_v := 'Error in getting unbilled usage from CB_SUBS_INFORMATION_MASTER';

             

             

                    SELECT a.SERVICE_INFO_V, a.ACCOUNT_CODE_N, a.SERVICE_CODE_V,

                           (SELECT NVL(MUL_APPLICABLE_FLG_V, 'N')||NVL(IND_SHARED_FLAG_V,'S')||PREFERRED_CURR_CODE_V FROM CB_ACCOUNT_MASTER WHERE ACCOUNT_CODE_N = a.ACCOUNT_CODE_N)STRING_V,

                           (SELECT ACCOUNT_TYPE_V FROM CB_ACCOUNT_MASTER WHERE ACCOUNT_CODE_N = a.ACCOUNT_CODE_N)ACCOUNT_TYPE_V,

                           a.STATUS_CODE_V

                    INTO   l_service_info_v, l_account_code_n, l_service_code_v, l_string_v, l_account_type_v, l_status_code_v

                    FROM   CB_ACCOUNT_SERVICE_LIST a

                    WHERE  a.ACCOUNT_LINK_CODE_N = l_account_link_code_n;

             

             

                    l_mul_applicable_flg_v  := SUBSTR(l_string_v,1,1);

                    l_shrd_indvl_flag_v     := SUBSTR(l_string_v,2,1);

                    l_preferred_curr_code_v := SUBSTR(l_string_v,3);                                          

                   

                    IF l_set_uc_required_v  = 'Y'  AND l_mul_applicable_flg_v= 'Y' THEN  

                   

                       l_err_code_v := 'bal_upd:03';

                       l_err_mesg_v := 'Unable to Insert into CB_PROCS_CR_SCHDL';

                   

                       INSERT INTO CB_PROCS_CR_SCHDL(PROCS_DATE_D, ACCOUNT_LINK_CODE_N, SERVICE_CODE_V, STATUS_OPTN_V)

                       VALUES(CURRENT_TIMESTAMP, l_account_link_code_n, l_service_code_v, 'I');         

                   

                    ELSE

                   

                        IF l_service_code_v  = 'CB' OR l_status_code_v in ('ER', 'PR') OR l_mul_applicable_flg_v='Y' THEN

             

             

                           GOTO END_PROCESS;

             

             

                        END IF;                       

             

             

                        IF INSERTING THEN

             

             

                           BEGIN

                               SELECT 1

                               INTO   l_adv_trans_cnt_n

                               FROM   CB_ADV_SET_OFF_TRANS

                               WHERE  CREDIT_NOTE_NO_V = :NEW.TRANS_NUM_V;

                           EXCEPTION

                                WHEN NO_DATA_FOUND THEN

                                    l_adv_trans_cnt_n := 0;

                                WHEN TOO_MANY_ROWS THEN

                                    l_adv_trans_cnt_n := 0;

                                WHEN OTHERS THEN

                                    l_adv_trans_cnt_n := 0;

                           END;

             

             

                           IF l_shrd_indvl_flag_v = 'S' THEN      

                          

                               IF l_adv_trans_cnt_n > 0 THEN

             

             

                                  GOTO END_PROCESS;

             

             

                               END IF;

             

             

                           ELSIF l_shrd_indvl_flag_v = 'I' THEN

                          

                                 IF l_adv_trans_cnt_n > 0 THEN

                                 

                                    SELECT INV_SRV_OPTN_V

                                    INTO   l_inv_srv_optn_v

                                    FROM   CB_SUBS_PAID_ADV_DTLS a, CB_ADV_SET_OFF_TRANS b

                                    WHERE  a.MAIN_ACCOUNT_LINK_CODE_N = b.MAIN_ACCOUNT_LINK_cODE_N

                                    AND    a.RECEIPT_NO_V = b.RECEIPT_NO_V

                                    AND    b.CREDIT_NOTE_NO_V = :NEW.TRANS_NUM_V;

                                   

                                    IF l_inv_srv_optn_v = 'S' THEN

                                   

                                       GOTO END_PROCESS;

                                   

                                    END IF;            

                                   

                                 END IF;   

                                                        

                           END IF;                  

                       

                        END IF;  

                          

                        IF IS_SERVICE_LIVE(l_account_type_v) = 'Y' AND IS_SERVICE_LIVE(l_service_code_v) = 'Y' THEN

             

             

                            l_err_code_v := 'bal_upd:05';

                            l_err_mesg_v := 'Unable to Insert into CB_PROCS_CR_SCHDL '||l_trans_num_v;

             

             

                              

                            INSERT INTO TRANS_TO_3RD_PARTY(ACCOUNT_CODE_N

                                                           , MAIN_ACC_LINK_CODE_N

                                                           , SERV_ACC_LINK_CODE_N

                                                           , SERVICE_ID_V

                                                           , TRANS_NUM_V

                                                           , POST_STATUS_V

                                                           , POSTED_DATE_D

                                                          )

                                                   VALUES (l_account_code_n

                                                           , l_main_account_link_code_n

                                                           , l_account_link_code_n

                                                           , l_service_info_v

                                                           , l_trans_num_v

                                                           , 'Q'

                                                           , CURRENT_TIMESTAMP

                                                          );

             

             

                        END IF;

             

             

                    END IF;

                   

                END IF;

             

             

                <<END_PROCESS>>

                l_success_flag_n := 1;

             

             

            EXCEPTION

             

             

                WHEN l_quit_e THEN

                    l_err_code_v := SUBSTR(l_err_code_v||':'||SQLCODE,1,15);

                    l_err_mesg_v := RTRIM(l_err_mesg_v)||' '||SUBSTR(SQLERRM,1,200);

                    RAISE_APPLICATION_ERROR(-20001, l_err_code_v||' : '||l_err_mesg_v);

             

             

                WHEN NO_DATA_FOUND THEN                    

                    l_err_code_v := SUBSTR(l_err_code_v||':'||SQLCODE,1,15);

                    l_err_mesg_v := RTRIM(l_err_mesg_v)||' '||SUBSTR(SQLERRM,1,200);

                   

                    GNL_ERROR_LOG_PRC(l_err_code_v, l_err_mesg_v, $$PLSQL_UNIT);

                   

                    RAISE_APPLICATION_ERROR(-20001, 'No Data Found..!'||l_account_link_code_n );

             

             

                WHEN OTHERS THEN         

                    l_err_code_v := SUBSTR(l_err_code_v||':'||SQLCODE,1,15);

                    l_err_mesg_v := RTRIM(l_err_mesg_v)||' '||SUBSTR(SQLERRM,1,200);

                   

                    GNL_ERROR_LOG_PRC(l_err_code_v, l_err_mesg_v, $$PLSQL_UNIT);

                   

                    RAISE_APPLICATION_ERROR(-20001, SQLERRM);

             

             

            END;

            /

             

            CREATE OR REPLACE TRIGGER CBS_CORE.UPD_AMT_SUB_ARAP_FINC_BAL AFTER

              UPDATE OF OVERALL_AMT_N ON CBS_CORE.CB_SUB_AR_AP FOR EACH ROW

            DECLARE l_acc_balnc_rowid ROWID;

              l_entity_type_v VARCHAR2(3);

              BEGIN

                SELECT ROWID,

                  ENTITY_TYPE_V

                INTO l_acc_balnc_rowid,

                  l_entity_type_v

                FROM CB_ACC_BALNC

                WHERE ACCOUNT_LINK_CODE_N = :OLD.ACCOUNT_LINK_CODE_N;

                IF l_entity_type_v        = 'SUB' THEN

                  IF :OLD.DB_CR_V         = 'D' THEN

                    UPDATE CB_ACC_BALNC

                    SET YTD_DB_AMT_N          = YTD_DB_AMT_N - :OLD.OVERALL_AMT_N + :NEW.OVERALL_AMT_N

                    WHERE ACCOUNT_LINK_CODE_N = :OLD.ACCOUNT_LINK_CODE_N;

                    UPDATE CB_MONTHLY_DB_CR_TOTALS

                    SET MTH_DB_AMT_N          = NVL(MTH_DB_AMT_N,0) - :OLD.OVERALL_AMT_N + :NEW.OVERALL_AMT_N

                    WHERE ACCOUNT_LINK_CODE_N = :OLD.ACCOUNT_LINK_CODE_N

                    AND YR_MTH_N              = TO_NUMBER(TO_CHAR(TRUNC(:OLD.TRANS_DATE_D),'YYYYMM'));

                  ELSE -- IF CREDIT

                    UPDATE CB_ACC_BALNC

                    SET YTD_CR_AMT_N          = YTD_CR_AMT_N - :OLD.OVERALL_AMT_N + :NEW.OVERALL_AMT_N

                    WHERE ACCOUNT_LINK_CODE_N = :OLD.ACCOUNT_LINK_CODE_N;

                    UPDATE CB_MONTHLY_DB_CR_TOTALS

                    SET MTH_CR_AMT_N          = MTH_CR_AMT_N - :OLD.OVERALL_AMT_N + :NEW.OVERALL_AMT_N

                    WHERE ACCOUNT_LINK_CODE_N = :OLD.ACCOUNT_LINK_CODE_N

                    AND YR_MTH_N              = TO_NUMBER(TO_CHAR(TRUNC(:OLD.TRANS_DATE_D),'YYYYMM'));

                  END IF; --:OLD.DB_CR_V = 'D'

                END IF;   --l_entity_type_v = 'SUB'

              EXCEPTION

              WHEN NO_DATA_FOUND THEN

                dbms_output.put_line('test'||:OLD.ACCOUNT_LINK_CODE_n);

                RAISE_APPLICATION_ERROR(-20001,'No SUB AR/AP Data Found..!' );

              WHEN OTHERS THEN

                RAISE_APPLICATION_ERROR(-20001,SQLERRM);

              END;

            /

             

            CREATE OR REPLACE TRIGGER CBS_CORE.CB_SUB_AR_AP_UPD_TRIG BEFORE

              UPDATE ON CBS_CORE.CB_SUB_AR_AP FOR EACH ROW

            BEGIN :NEW.LAST_MODIFIED_DATE_D := CURRENT_TIMESTAMP;

            END;

            /

             

            CREATE OR REPLACE TRIGGER CBS_CORE.UPDT_SUB_ARAP_FINC_BALNC AFTER

              INSERT OR

              DELETE ON CBS_CORE.CB_SUB_AR_AP FOR EACH ROW

            DECLARE l_acc_balnc_rowid ROWID;

              l_entity_type_v VARCHAR2(3);

            BEGIN

              IF INSERTING THEN

                SELECT ROWID,

                  ENTITY_TYPE_V

                INTO l_acc_balnc_rowid,

                  l_entity_type_v

                FROM CB_ACC_BALNC

                WHERE ACCOUNT_LINK_CODE_N = :NEW.ACCOUNT_LINK_CODE_N;

                IF l_entity_type_v        = 'SUB' THEN

                  IF :NEW.DB_CR_V         = 'D' THEN

                    UPDATE CB_ACC_BALNC

                    SET YTD_DB_AMT_N = NVL(YTD_DB_AMT_N,0) + :NEW.OVERALL_AMT_N

                    WHERE ROWID      = l_acc_balnc_rowid;

                    BEGIN

                      UPDATE CB_MONTHLY_DB_CR_TOTALS

                      SET MTH_DB_AMT_N          = NVL(MTH_DB_AMT_N,0) + :NEW.OVERALL_AMT_N

                      WHERE ACCOUNT_LINK_CODE_N = :NEW.ACCOUNT_LINK_CODE_N

                      AND YR_MTH_N              = TO_NUMBER(TO_CHAR(TRUNC(:NEW.TRANS_DATE_D),'YYYYMM'));

                      IF SQL%NOTFOUND THEN -- CREATING MONTH BAL RECORD FOR NEW MONTH

                        INSERT

                        INTO CB_MONTHLY_DB_CR_TOTALS

                          (

                            ACCOUNT_LINK_CODE_N,

                            YR_MTH_N,

                            MTH_DB_AMT_N

                          )

                          VALUES

                          (

                            :NEW.ACCOUNT_LINK_CODE_N,

                            TO_NUMBER(TO_CHAR(TRUNC(:NEW.TRANS_DATE_D),'YYYYMM')),

                            :NEW.OVERALL_AMT_N

                          );

                      END IF;

                    END;

                  ELSE -- IF CREDIT

                    UPDATE CB_ACC_BALNC

                    SET YTD_CR_AMT_N = NVL(YTD_CR_AMT_N,0) + :NEW.OVERALL_AMT_N

                    WHERE ROWID      = l_acc_balnc_rowid;

                    BEGIN

                      UPDATE CB_MONTHLY_DB_CR_TOTALS

                      SET MTH_CR_AMT_N          = NVL(MTH_CR_AMT_N,0) + :NEW.OVERALL_AMT_N

                      WHERE ACCOUNT_LINK_CODE_N = :NEW.ACCOUNT_LINK_CODE_N

                      AND YR_MTH_N              = TO_NUMBER(TO_CHAR(TRUNC(:NEW.TRANS_DATE_D),'YYYYMM'));

                      IF SQL%NOTFOUND THEN -- CREATING MONTH BAL RECORD FOR NEW MONTH

                        INSERT

                        INTO CB_MONTHLY_DB_CR_TOTALS

                          (

                            ACCOUNT_LINK_CODE_N,

                            YR_MTH_N,

                            MTH_CR_AMT_N

                          )

                          VALUES

                          (

                            :NEW.ACCOUNT_LINK_CODE_N,

                            TO_NUMBER(TO_CHAR(TRUNC(:NEW.TRANS_DATE_D),'YYYYMM')) ,

                            :NEW.OVERALL_AMT_N

                          );

                      END IF;

                    END;

                  END IF; --NEW.DB_CR_V = 'D'

                END IF;   --l_entity_type_v = 'SUB'

              ELSIF DELETING THEN

                SELECT ROWID,

                  ENTITY_TYPE_V

                INTO l_acc_balnc_rowid,

                  l_entity_type_v

                FROM CB_ACC_BALNC

                WHERE ACCOUNT_LINK_CODE_N = :OLD.ACCOUNT_LINK_CODE_N;

                IF l_entity_type_v        = 'SUB' THEN

                  IF :OLD.DB_CR_V         = 'D' THEN

                    UPDATE CB_ACC_BALNC

                    SET YTD_DB_AMT_N          = YTD_DB_AMT_N - :OLD.OVERALL_AMT_N

                    WHERE ACCOUNT_LINK_CODE_N = :OLD.ACCOUNT_LINK_CODE_N;

                    UPDATE CB_MONTHLY_DB_CR_TOTALS

                    SET MTH_DB_AMT_N          = NVL(MTH_DB_AMT_N,0) - :OLD.OVERALL_AMT_N

                    WHERE ACCOUNT_LINK_CODE_N = :OLD.ACCOUNT_LINK_CODE_N

                    AND YR_MTH_N              = TO_NUMBER(TO_CHAR(TRUNC(:OLD.TRANS_DATE_D),'YYYYMM'));

                  ELSE -- IF CREDIT

                    UPDATE CB_ACC_BALNC

                    SET YTD_CR_AMT_N          = YTD_CR_AMT_N - :OLD.OVERALL_AMT_N

                    WHERE ACCOUNT_LINK_CODE_N = :OLD.ACCOUNT_LINK_CODE_N;

                    UPDATE CB_MONTHLY_DB_CR_TOTALS

                    SET MTH_CR_AMT_N          = MTH_CR_AMT_N - :OLD.OVERALL_AMT_N

                    WHERE ACCOUNT_LINK_CODE_N = :OLD.ACCOUNT_LINK_CODE_N

                    AND YR_MTH_N              = TO_NUMBER(TO_CHAR(TRUNC(:OLD.TRANS_DATE_D),'YYYYMM'));

                  END IF; --:OLD.DB_CR_V = 'D'

                END IF;   --l_entity_type_v = 'SUB'

              END IF;     -- DELETING

            EXCEPTION

            WHEN NO_DATA_FOUND THEN

              dbms_output.put_line('test'||:NEW.ACCOUNT_LINK_CODE_n);

              RAISE_APPLICATION_ERROR(-20001,'No SUB AR/AP Data Found..!' );

            WHEN OTHERS THEN

              RAISE_APPLICATION_ERROR(-20001,SQLERRM);

            END;

            /

             

            DROP SYNONYM CBS_APPS.CB_SUB_AR_AP;

             

            CREATE OR REPLACE SYNONYM CBS_APPS.CB_SUB_AR_AP FOR CBS_CORE.CB_SUB_AR_AP;

            DROP SYNONYM CBS_VIEW_INT.CB_SUB_AR_AP;

             

            CREATE OR REPLACE SYNONYM CBS_VIEW_INT.CB_SUB_AR_AP FOR CBS_CORE.CB_SUB_AR_AP;

            DROP SYNONYM CBS_CUST.CB_SUB_AR_AP;

             

            CREATE OR REPLACE SYNONYM CBS_CUST.CB_SUB_AR_AP FOR CBS_CORE.CB_SUB_AR_AP;

             

            DROP SYNONYM MT_PROD_STG.CB_SUB_AR_AP;

             

            CREATE OR REPLACE SYNONYM MT_PROD_STG.CB_SUB_AR_AP FOR CBS_CORE.CB_SUB_AR_AP;

             

            DROP SYNONYM CBS_VIEW.CB_SUB_AR_AP;

             

            CREATE OR REPLACE SYNONYM CBS_VIEW.CB_SUB_AR_AP FOR CBS_CORE.CB_SUB_AR_AP;

             

            DROP SYNONYM TT_MSO.CB_SUB_AR_AP;

             

            CREATE OR REPLACE SYNONYM TT_MSO.CB_SUB_AR_AP FOR CBS_CORE.CB_SUB_AR_AP;

            DROP SYNONYM FINANCE_APP.CB_SUB_AR_AP;

             

            CREATE OR REPLACE SYNONYM FINANCE_APP.CB_SUB_AR_AP FOR CBS_CORE.CB_SUB_AR_AP;

             

            ALTER TABLE CBS_CORE.CB_SUB_AR_AP ADD (

              CONSTRAINT SERV_ARAP#TRANS_DATET$NN

              CHECK ("TRANS_DATE_D" IS NOT NULL),

              CONSTRAINT SUB_ARAP#AMT$CK

              CHECK (OVERALL_AMT_N > 0)  DISABLE,

              CONSTRAINT SUB_ARAP#AMT$NN

              CHECK ("OVERALL_AMT_N" IS NOT NULL),

              CONSTRAINT SUB_ARAP#DB_CR$CK

              CHECK (DB_CR_V IN('D','C')),

              CONSTRAINT SUB_ARAP#DB_CR$NN

              CHECK ("DB_CR_V" IS NOT NULL),

              CONSTRAINT SUB_ARAP#MAIN_AC_LNK_CD$NN

              CHECK ("MAIN_ACCOUNT_LINK_CODE_N" IS NOT NULL));

             

            GRANT ALTER, DELETE, INDEX, INSERT, REFERENCES, SELECT, UPDATE, ON COMMIT REFRESH, QUERY REWRITE, READ, DEBUG, FLASHBACK ON CBS_CORE.CB_SUB_AR_AP TO CBS_APPS;

             

            GRANT ALTER, DELETE, INSERT, SELECT, UPDATE ON CBS_CORE.CB_SUB_AR_AP TO CBS_CUST;

             

            GRANT READ ON CBS_CORE.CB_SUB_AR_AP TO CBS_VIEW;

             

            GRANT SELECT ON CBS_CORE.CB_SUB_AR_AP TO CBS_VIEW_INT;

             

            GRANT SELECT ON CBS_CORE.CB_SUB_AR_AP TO FINANCE_APP;

             

            GRANT INSERT, SELECT, UPDATE ON CBS_CORE.CB_SUB_AR_AP TO MT_PROD_STG;

             

            GRANT READ ON CBS_CORE.CB_SUB_AR_AP TO TT_MSO;

             

            Regards,

             

            Joe