UPDATE APPS_NL.GEDIS_CONTACT 
SET LAST_UPDATE_DATE = TZ.local_to_db_date(TO_DATE('7/17/2013 11:20:19 AM','MM/dd/yyyy hh:mi:ss AM')), 
EMAIL = 'test@dell.com', 
SUBSCRIBE_TO_CFO = 'N' 
WHERE CONTACT_ID= 90005911;

ORA-00604: error occurred at recursive SQL level 1
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'SYNCRN'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


CREATE OR REPLACE procedure CTXSYS.syncrn (
  ownid IN binary_integer,
  oname IN varchar2,
  idxid IN binary_integer,
  ixpid IN binary_integer,
  rtabnm IN varchar2,
  srcflg IN binary_integer
)
  authid definer
  as external
  name "comt_cb"
  library dr$lib
  with context
  parameters(
    context,
    ownid  ub4,
    oname  OCISTRING,
    idxid  ub4,
    ixpid  ub4,
    rtabnm OCISTRING,
    srcflg ub1
);
/

SELECT saddr,sid,serial#,user#,username,lockwait,status,
schemaname,type,SQL_EXEC_START,PREV_EXEC_START,logon_time,BLOCKING_SESSION_STATUS FROM v$session
WHERE TRUNC(logon_time)= '17-JUL-2013'

ALTER SESSION SET CURRENT_SCHEMA = APPS_NL;

UPDATE APPS_NL.GEDIS_CONTACT  
SET LAST_UPDATE_DATE =TO_DATE('7/17/2013 11:20:19 AM','MM/dd/yyyy hh:mi:ss AM'),  
EMAIL = 'test@dell.com',  
SUBSCRIBE_TO_CFO = 'N'  
WHERE CONTACT_ID= 90005911;  
commit;

1 row updated.
commit
Error at line 6
ORA-00604: error occurred at recursive SQL level 1
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'SYNCRN'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

CREATE TABLE APPS_NL.GEDIS_CONTACT
(
  CONTACT_ID               NUMBER(20),
  LAST_UPDATE_DATE         DATE                 NOT NULL,
  LAST_UPDATED_BY          NUMBER(15)           NOT NULL,
  CREATION_DATE            DATE                 NOT NULL,
  CREATED_BY               NUMBER(15)           NOT NULL,
  ORIG_SYSTEM_CONTACT_REF  VARCHAR2(240 CHAR),
  ADDRESS_ID               NUMBER(20)           NOT NULL,
  TITLE                    VARCHAR2(25 CHAR),
  FIRST_NAME               VARCHAR2(40 CHAR),
  LAST_NAME                VARCHAR2(50 CHAR)    NOT NULL,
  EMAIL                    VARCHAR2(240 CHAR),
  DEPARTMENT               VARCHAR2(60 CHAR),
  INTERFACE_REQUIRED       VARCHAR2(1 CHAR)     DEFAULT 'N'                   NOT NULL,
  PRIMARY                  VARCHAR2(1 CHAR),
  UPPER_LAST_NAME          VARCHAR2(50 CHAR),
  MAIL_FLAG                VARCHAR2(1 CHAR),
  FAX_FLAG                 VARCHAR2(1 CHAR),
  EMAIL_FLAG               VARCHAR2(1 CHAR),
  MARKETING_LANGUAGE_CODE  VARCHAR2(20 CHAR),
  ITEM_LANGUAGE_CODE       VARCHAR2(20 CHAR),
  UPPER_FIRST_NAME         VARCHAR2(40 CHAR),
  INACTIVE                 VARCHAR2(1 CHAR)     DEFAULT 'N',
  CREATED_BY_NT            VARCHAR2(30 CHAR),
  LAST_UPDATED_BY_NT       VARCHAR2(30 CHAR),
  ORIG_SYSTEM              VARCHAR2(30 CHAR),
  LAST_UPDATED_BY_SYSTEM   VARCHAR2(30 CHAR),
  EN_FIRST_NAME            VARCHAR2(40 CHAR),
  EN_LAST_NAME             VARCHAR2(50 CHAR),
  LAST_UPDATE_DATE_UTC     DATE                 DEFAULT null,
  CREATION_DATE_UTC        DATE                 DEFAULT null,
  JP_FIRST_NAME            VARCHAR2(40 CHAR),
  JP_LAST_NAME             VARCHAR2(50 CHAR),
  PHONE_FLAG               VARCHAR2(1 CHAR),
  SUBSCRIBE_TO_CFO         VARCHAR2(1 CHAR)
);

CREATE INDEX APPS_NL.GEDIS_CONTACT_N1 ON APPS_NL.GEDIS_CONTACT(ADDRESS_ID);
CREATE INDEX APPS_NL.GEDIS_CONTACT_N2 ON APPS_NL.GEDIS_CONTACT(LAST_NAME);
CREATE INDEX APPS_NL.GEDIS_CONTACT_N3 ON APPS_NL.GEDIS_CONTACT(UPPER_LAST_NAME);
CREATE INDEX APPS_NL.GEDIS_CONTACT_N4 ON APPS_NL.GEDIS_CONTACT(ORIG_SYSTEM_CONTACT_REF);
CREATE INDEX APPS_NL.GEDIS_CONTACT_N5 ON APPS_NL.GEDIS_CONTACT(LAST_UPDATE_DATE);
CREATE INDEX APPS_NL.GEDIS_CONTACT_N6 ON APPS_NL.GEDIS_CONTACT(UPPER_FIRST_NAME);
CREATE UNIQUE INDEX APPS_NL.PK_GEDIS_CONTACT ON APPS_NL.GEDIS_CONTACT(CONTACT_ID);


CREATE OR REPLACE TRIGGER APPS_NL."TR_GEDIS_CONTACT_DEL" 
before DELETE
ON GEDIS_contact
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
tmpVar NUMBER;
BEGIN
  delete from gedis_customer_summary
    where contact_id = :old.contact_id;
END ;
/


CREATE OR REPLACE TRIGGER APPS_NL."GEDIS_CONTACT_AUI" 
  AFTER INSERT OR UPDATE
  ON APPS_NL.GEDIS_CONTACT   FOR EACH ROW
DECLARE
  v_type VARCHAR2(6);
  v_save BOOLEAN;
BEGIN
  IF INSERTING THEN
    v_type := 'INSERT';
    v_save := TRUE;
  ELSE
    v_type := 'UPDATE';

    IF  --(:NEW.LAST_UPDATE_DATE - :OLD.LAST_UPDATE_DATE < 1/86400)
    /*OR*/ (:NEW.CREATED_BY_NT = :OLD.CREATED_BY_NT OR (:NEW.CREATED_BY_NT IS NULL AND :OLD.CREATED_BY_NT IS NULL))
    AND (:NEW.LAST_UPDATED_BY_NT = :OLD.LAST_UPDATED_BY_NT OR (:NEW.LAST_UPDATED_BY_NT IS NULL AND :OLD.LAST_UPDATED_BY_NT IS NULL))
    AND (:NEW.ORIG_SYSTEM = :OLD.ORIG_SYSTEM OR (:NEW.ORIG_SYSTEM IS NULL AND :OLD.ORIG_SYSTEM IS NULL))
    AND (:NEW.LAST_UPDATED_BY_SYSTEM = :OLD.LAST_UPDATED_BY_SYSTEM OR (:NEW.LAST_UPDATED_BY_SYSTEM IS NULL AND :OLD.LAST_UPDATED_BY_SYSTEM IS NULL))
    AND (:NEW.EN_FIRST_NAME = :OLD.EN_FIRST_NAME OR (:NEW.EN_FIRST_NAME IS NULL AND :OLD.EN_FIRST_NAME IS NULL))
    AND (:NEW.EN_LAST_NAME = :OLD.EN_LAST_NAME OR (:NEW.EN_LAST_NAME IS NULL AND :OLD.EN_LAST_NAME IS NULL))
    AND (:NEW.CONTACT_ID = :OLD.CONTACT_ID OR (:NEW.CONTACT_ID IS NULL AND :OLD.CONTACT_ID IS NULL))
    AND (:NEW.LAST_UPDATED_BY = :OLD.LAST_UPDATED_BY OR (:NEW.LAST_UPDATED_BY IS NULL AND :OLD.LAST_UPDATED_BY IS NULL))
    AND (:NEW.CREATION_DATE = :OLD.CREATION_DATE OR (:NEW.CREATION_DATE IS NULL AND :OLD.CREATION_DATE IS NULL))
    AND (:NEW.CREATED_BY = :OLD.CREATED_BY OR (:NEW.CREATED_BY IS NULL AND :OLD.CREATED_BY IS NULL))
    AND (:NEW.ORIG_SYSTEM_CONTACT_REF = :OLD.ORIG_SYSTEM_CONTACT_REF OR (:NEW.ORIG_SYSTEM_CONTACT_REF IS NULL AND :OLD.ORIG_SYSTEM_CONTACT_REF IS NULL))
    AND (:NEW.ADDRESS_ID = :OLD.ADDRESS_ID OR (:NEW.ADDRESS_ID IS NULL AND :OLD.ADDRESS_ID IS NULL))
    AND (:NEW.TITLE = :OLD.TITLE OR (:NEW.TITLE IS NULL AND :OLD.TITLE IS NULL))
    AND (:NEW.FIRST_NAME = :OLD.FIRST_NAME OR (:NEW.FIRST_NAME IS NULL AND :OLD.FIRST_NAME IS NULL))
    AND (:NEW.LAST_NAME = :OLD.LAST_NAME OR (:NEW.LAST_NAME IS NULL AND :OLD.LAST_NAME IS NULL))
    AND (:NEW.EMAIL = :OLD.EMAIL OR (:NEW.EMAIL IS NULL AND :OLD.EMAIL IS NULL))
    AND (:NEW.DEPARTMENT = :OLD.DEPARTMENT OR (:NEW.DEPARTMENT IS NULL AND :OLD.DEPARTMENT IS NULL))
    AND (:NEW.INTERFACE_REQUIRED = :OLD.INTERFACE_REQUIRED OR (:NEW.INTERFACE_REQUIRED IS NULL AND :OLD.INTERFACE_REQUIRED IS NULL))
    AND (:NEW.PRIMARY = :OLD.PRIMARY OR (:NEW.PRIMARY IS NULL AND :OLD.PRIMARY IS NULL))
    AND (:NEW.UPPER_LAST_NAME = :OLD.UPPER_LAST_NAME OR (:NEW.UPPER_LAST_NAME IS NULL AND :OLD.UPPER_LAST_NAME IS NULL))
    AND (:NEW.MAIL_FLAG = :OLD.MAIL_FLAG OR (:NEW.MAIL_FLAG IS NULL AND :OLD.MAIL_FLAG IS NULL))
    AND (:NEW.FAX_FLAG = :OLD.FAX_FLAG OR (:NEW.FAX_FLAG IS NULL AND :OLD.FAX_FLAG IS NULL))
    AND (:NEW.EMAIL_FLAG = :OLD.EMAIL_FLAG OR (:NEW.EMAIL_FLAG IS NULL AND :OLD.EMAIL_FLAG IS NULL))
    AND (:NEW.MARKETING_LANGUAGE_CODE = :OLD.MARKETING_LANGUAGE_CODE OR (:NEW.MARKETING_LANGUAGE_CODE IS NULL AND :OLD.MARKETING_LANGUAGE_CODE IS NULL))
    AND (:NEW.ITEM_LANGUAGE_CODE = :OLD.ITEM_LANGUAGE_CODE OR (:NEW.ITEM_LANGUAGE_CODE IS NULL AND :OLD.ITEM_LANGUAGE_CODE IS NULL))
    AND (:NEW.UPPER_FIRST_NAME = :OLD.UPPER_FIRST_NAME OR (:NEW.UPPER_FIRST_NAME IS NULL AND :OLD.UPPER_FIRST_NAME IS NULL))
    AND (:NEW.INACTIVE = :OLD.INACTIVE OR (:NEW.INACTIVE IS NULL AND :OLD.INACTIVE IS NULL)) THEN
      v_save := FALSE;
    ELSE
      v_save := TRUE;
    END IF;
  END IF;

  IF v_save THEN
    xxeom_order_queue_buf.ddw_insert_row('CONTACT'
                                       , :NEW.CONTACT_ID
                                       , /*:NEW.LAST_UPDATE_DATE D-14925*/ SYSDATE
                                       , :NEW.LAST_UPDATED_BY
                                       , v_type);
  END IF;
END;
/


CREATE OR REPLACE TRIGGER APPS_NL.GEDIS_CONTACT_ARU_GDAS
    after update ON APPS_NL.GEDIS_CONTACT for each row
WHEN (
not(
         (old.CONTACT_ID    = new.CONTACT_ID    or (old.CONTACT_ID    is NULL and new.CONTACT_ID    is NULL))
     and (old.TITLE         = new.TITLE         or (old.TITLE         is NULL and new.TITLE         is NULL))
     and (old.FIRST_NAME    = new.FIRST_NAME    or (old.FIRST_NAME    is NULL and new.FIRST_NAME    is NULL))
     and (old.LAST_NAME     = new.LAST_NAME     or (old.LAST_NAME     is NULL and new.LAST_NAME     is NULL))
     and (old.EN_FIRST_NAME = new.EN_FIRST_NAME or (old.EN_FIRST_NAME is NULL and new.EN_FIRST_NAME is NULL))
     and (old.EN_LAST_NAME  = new.EN_LAST_NAME  or (old.EN_LAST_NAME  is NULL and new.EN_LAST_NAME  is NULL))
     and (old.PRIMARY       = new.PRIMARY       or (old.PRIMARY       is NULL and new.PRIMARY       is NULL))
     and (old.EMAIL         = new.EMAIL         or (old.EMAIL         is NULL and new.EMAIL         is NULL))
     and (old.INACTIVE      = new.INACTIVE      or (old.INACTIVE      is NULL and new.INACTIVE      is NULL))
    )
      )
begin
    update GEDIS_CUSTOMER u
       set u.LAST_PUBLISH_TIME = systimestamp at time zone 'GMT'
     where u.FIRST_BOOKED_ORDER_DATE is not NULL
       and u.CUSTOMER_ID in (select a.CUSTOMER_ID
                               from GEDIS_ADDRESS a
                              where a.ADDRESS_ID = :new.ADDRESS_ID)
    ;
end;
/


CREATE OR REPLACE TRIGGER APPS_NL.GEDIS_CONTACT_CHUB_AUI
  AFTER UPDATE ON APPS_NL.GEDIS_CONTACT   FOR EACH ROW
DECLARE
   V_PHONE_ID NUMBER;
BEGIN
   -- Check is the Customer HUB Interface enabled
   IF XXEOM_ORDER_QUEUE_BUF.GET_GEDIS_BUCONFIG_PARAMETER('ENABLE_CUSTOMER_HUB') = 'Y' THEN
      NULL;
   ELSE
      RETURN;
   END IF;
   -- Check Inactive changes
   IF  UPPER ( nvl( :NEW.INACTIVE, '-11111' ) ) !=
       UPPER ( nvl( :OLD.INACTIVE, '-11111' ) )
   THEN
     NULL;
   ELSE
     RETURN;
   END IF;
   BEGIN
        -- Get Phone Identifier for INT1/2 queue type
        SELECT    PHONE_ID
           INTO   V_PHONE_ID
           FROM   GEDIS_TELEPHONE
          WHERE
                  CONTACT_ID = :NEW.CONTACT_ID
              AND PRIMARY    = 'Y';
             -- Message for Customer Hub
             XXEOM_ORDER_QUEUE_BUF.ADD_MESSAGE
                       (
                          P_MESSAGE_TYPE => 'CHUB',
                          P_PHONE_ID     => V_PHONE_ID
                       );
   EXCEPTION
         WHEN OTHERS THEN
           xxeom_Order_queue_buf.Fix_Error_In_Audit(
               p_Interface => audit_interface.Interface_Customer,
               p_Consumer_name => 'INT1_CHUB',
               p_Error_Text =>
               'Contact_id: '||:NEW.CONTACT_ID||
               ',p_phone_id: '||V_PHONE_ID||'. Stage: '||
               'TRIGGER GEDIS_CONTACT_CHUB_AUI'||'. Error: '||SQLERRM);
   END;
END GEDIS_CONTACT_CHUB_AUI;
/


CREATE OR REPLACE TRIGGER APPS_NL.TR_GEDIS_CONTACT_UPD
AFTER UPDATE
OF ORIG_SYSTEM_CONTACT_REF
  ,FIRST_NAME
  ,LAST_NAME
  ,UPPER_LAST_NAME
  ,UPPER_FIRST_NAME
  ,INACTIVE
  ,EN_FIRST_NAME
  ,EN_LAST_NAME
  ,EMAIL
  ,JP_FIRST_NAME
  ,JP_LAST_NAME
ON APPS_NL.GEDIS_CONTACT REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
BEGIN
  UPDATE GEDIS_CUSTOMER_SUMMARY
     SET ORIG_SYSTEM_CONTACT_REF = :new.ORIG_SYSTEM_CONTACT_REF
        ,FIRST_NAME = :new.FIRST_NAME
        ,LAST_NAME = :new.LAST_NAME
        ,UPPER_LAST_NAME = :new.UPPER_LAST_NAME
        ,UPPER_FIRST_NAME = :new.UPPER_FIRST_NAME
        ,CONTACT_INACTIVE = DECODE(:new.INACTIVE, 'Y', 'Y', 'N')
        ,EN_FIRST_NAME = :new.EN_FIRST_NAME
        ,EN_LAST_NAME = :new.EN_LAST_NAME
        ,UPPER_EN_FIRST_NAME = UPPER(:new.EN_FIRST_NAME)
        ,UPPER_EN_LAST_NAME = UPPER(:new.EN_LAST_NAME)
      --,OT_DUMMY = null
        ,EMAIL = :new.EMAIL
        ,UPPER_EMAIL = UPPER(:new.EMAIL)
        ,JP_FIRST_NAME = :new.JP_FIRST_NAME
        ,JP_LAST_NAME = :new.JP_LAST_NAME
   WHERE CONTACT_ID = :old.CONTACT_ID;
END ;
/


ALTER TABLE APPS_NL.GEDIS_CONTACT ADD (
  CONSTRAINT PK_GEDIS_CONTACT
 PRIMARY KEY
 (CONTACT_ID)
    USING INDEX 
    TABLESPACE XXDELL_TS_TX_IDX
    PCTFREE    10
    INITRANS   11
    MAXTRANS   255
    STORAGE    (
                INITIAL          4M
                NEXT             1M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
               ));

AUDIT DELETE ON APPS_NL.GEDIS_CONTACT BY SESSION WHENEVER SUCCESSFUL;
AUDIT DELETE ON APPS_NL.GEDIS_CONTACT BY SESSION WHENEVER NOT SUCCESSFUL;
AUDIT UPDATE ON APPS_NL.GEDIS_CONTACT BY SESSION WHENEVER SUCCESSFUL;
AUDIT UPDATE ON APPS_NL.GEDIS_CONTACT BY SESSION WHENEVER NOT SUCCESSFUL;

GRANT SELECT ON APPS_NL.GEDIS_CONTACT TO ALL_BU_READ_ONLY;

GRANT ALTER, DELETE, INDEX, INSERT, REFERENCES, SELECT, UPDATE, ON COMMIT REFRESH, QUERY REWRITE, DEBUG, FLASHBACK ON APPS_NL.GEDIS_CONTACT TO APPS_GLOBAL;

GRANT DELETE, INSERT, SELECT, UPDATE ON APPS_NL.GEDIS_CONTACT TO GDAS_USER;

GRANT SELECT ON APPS_NL.GEDIS_CONTACT TO GEDIS_READ_ONLY;

GRANT INSERT, SELECT, UPDATE ON APPS_NL.GEDIS_CONTACT TO GSL_USER;

GRANT SELECT ON APPS_NL.GEDIS_CONTACT TO SQL_USER;

SELECT * FROM dba_dependencies WHERE name='SYNCRN'

OWNER      NAME   TYPE         REFERENCED_OWNER    REFERENCED_NAME               REFERENCED_TYPE    REFERENCED_LINK_NAME   DEPENDENCY_TYPE
-------------- ------------------------------ ------------------ ----------------------------------------- -------------------------------- 
CTXSYS    SYNCRN    PROCEDURE   SYS                 STANDARD                         PACKAGE                                HARD
CTXSYS    SYNCRN    PROCEDURE   SYS                 SYS_STUB_FOR_PURITY_ANALYSIS     PACKAGE                                HARD
CTXSYS    SYNCRN    PROCEDURE   CTXSYS              DR$LIB                           LIBRARY                                HARD

CREATE OR REPLACE TRIGGER APPS_NL.TR_GEDIS_CONTACT_UPD
AFTER UPDATE
OF ORIG_SYSTEM_CONTACT_REF
  ,FIRST_NAME
  ,LAST_NAME
  ,UPPER_LAST_NAME
  ,UPPER_FIRST_NAME
  ,INACTIVE
  ,EN_FIRST_NAME
  ,EN_LAST_NAME
  ,EMAIL
  ,JP_FIRST_NAME
  ,JP_LAST_NAME
ON APPS_NL.GEDIS_CONTACT REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DISABLE
DECLARE
BEGIN
  UPDATE GEDIS_CUSTOMER_SUMMARY
     SET ORIG_SYSTEM_CONTACT_REF = :new.ORIG_SYSTEM_CONTACT_REF
        ,FIRST_NAME = :new.FIRST_NAME
        ,LAST_NAME = :new.LAST_NAME
        ,UPPER_LAST_NAME = :new.UPPER_LAST_NAME
        ,UPPER_FIRST_NAME = :new.UPPER_FIRST_NAME
        ,CONTACT_INACTIVE = DECODE(:new.INACTIVE, 'Y', 'Y', 'N')
        ,EN_FIRST_NAME = :new.EN_FIRST_NAME
        ,EN_LAST_NAME = :new.EN_LAST_NAME
        ,UPPER_EN_FIRST_NAME = UPPER(:new.EN_FIRST_NAME)
        ,UPPER_EN_LAST_NAME = UPPER(:new.EN_LAST_NAME)
      --,OT_DUMMY = null
        ,EMAIL = :new.EMAIL
        ,UPPER_EMAIL = UPPER(:new.EMAIL)
        ,JP_FIRST_NAME = :new.JP_FIRST_NAME
        ,JP_LAST_NAME = :new.JP_LAST_NAME
   WHERE CONTACT_ID = :old.CONTACT_ID;
END ;
/

SELECT * FROM dba_dependencies WHERE REFERENCED_NAME='SYNCRN'

CREATE OR REPLACE TRIGGER APPS_NL.TR_OT_GCS_IDX_REFRESH
    before update of
        CUSTOMER_ID,
        OMEGA_CUSTOMER_NUMBER,
        CUSTOMER_NAME,
        ADDRESS1,
        ADDRESS2,
        FIRST_NAME,
        LAST_NAME,
        CITY,
        POSTAL_CODE,
        TELEPHONE,
        EN_CUSTOMER_NAME,
        EN_CUSTOMER_NAME_EXT,
        EN_CUSTOMER_NAME_EXT2,
        EN_ADDRESS1,
        EN_ADDRESS2,
        EN_COUNTRY,
        EN_COUNTY,
        EN_CITY,
        EN_FIRST_NAME,
        EN_LAST_NAME,
        EMAIL,
        CUSTOMER_NAME_EXT,
        JP_ADDRESS1,
        JP_ADDRESS2,
        JP_ADDRESS3,
        JP_FIRST_NAME,
        JP_LAST_NAME,
        ORIG_SYSTEM_CUSTOMER_REF,
        ORIG_SYSTEM_ADDRESS_REF
    ON APPS_NL.GEDIS_CUSTOMER_SUMMARY     for each row
WHEN (
decode(new.CUSTOMER_ID             , old.CUSTOMER_ID             , 'N', 'Y') = 'Y'
       or decode(new.OMEGA_CUSTOMER_NUMBER   , old.OMEGA_CUSTOMER_NUMBER   , 'N', 'Y') = 'Y'
       or decode(new.CUSTOMER_NAME           , old.CUSTOMER_NAME           , 'N', 'Y') = 'Y'
       or decode(new.ADDRESS1                , old.ADDRESS1                , 'N', 'Y') = 'Y'
       or decode(new.ADDRESS2                , old.ADDRESS2                , 'N', 'Y') = 'Y'
       or decode(new.FIRST_NAME              , old.FIRST_NAME              , 'N', 'Y') = 'Y'
       or decode(new.LAST_NAME               , old.LAST_NAME               , 'N', 'Y') = 'Y'
       or decode(new.CITY                    , old.CITY                    , 'N', 'Y') = 'Y'
       or decode(new.POSTAL_CODE             , old.POSTAL_CODE             , 'N', 'Y') = 'Y'
       or decode(new.TELEPHONE               , old.TELEPHONE               , 'N', 'Y') = 'Y'
       or decode(new.EN_CUSTOMER_NAME        , old.EN_CUSTOMER_NAME        , 'N', 'Y') = 'Y'
       or decode(new.EN_CUSTOMER_NAME_EXT    , old.EN_CUSTOMER_NAME_EXT    , 'N', 'Y') = 'Y'
       or decode(new.EN_CUSTOMER_NAME_EXT2   , old.EN_CUSTOMER_NAME_EXT2   , 'N', 'Y') = 'Y'
       or decode(new.EN_ADDRESS1             , old.EN_ADDRESS1             , 'N', 'Y') = 'Y'
       or decode(new.EN_ADDRESS2             , old.EN_ADDRESS2             , 'N', 'Y') = 'Y'
       or decode(new.EN_COUNTRY              , old.EN_COUNTRY              , 'N', 'Y') = 'Y'
       or decode(new.EN_COUNTY               , old.EN_COUNTY               , 'N', 'Y') = 'Y'
       or decode(new.EN_CITY                 , old.EN_CITY                 , 'N', 'Y') = 'Y'
       or decode(new.EN_FIRST_NAME           , old.EN_FIRST_NAME           , 'N', 'Y') = 'Y'
       or decode(new.EN_LAST_NAME            , old.EN_LAST_NAME            , 'N', 'Y') = 'Y'
       or decode(new.EMAIL                   , old.EMAIL                   , 'N', 'Y') = 'Y'
       or decode(new.CUSTOMER_NAME_EXT       , old.CUSTOMER_NAME_EXT       , 'N', 'Y') = 'Y'
       or decode(new.JP_ADDRESS1             , old.JP_ADDRESS1             , 'N', 'Y') = 'Y'
       or decode(new.JP_ADDRESS2             , old.JP_ADDRESS2             , 'N', 'Y') = 'Y'
       or decode(new.JP_ADDRESS3             , old.JP_ADDRESS3             , 'N', 'Y') = 'Y'
       or decode(new.JP_FIRST_NAME           , old.JP_FIRST_NAME           , 'N', 'Y') = 'Y'
       or decode(new.JP_LAST_NAME            , old.JP_LAST_NAME            , 'N', 'Y') = 'Y'
       or decode(new.ORIG_SYSTEM_CUSTOMER_REF, old.ORIG_SYSTEM_CUSTOMER_REF, 'N', 'Y') = 'Y'
       or decode(new.ORIG_SYSTEM_ADDRESS_REF , old.ORIG_SYSTEM_ADDRESS_REF , 'N', 'Y') = 'Y'
      )
begin
    :new.OT_DUMMY := null;
end TR_OT_GCS_IDX_REFRESH;
/

CREATE INDEX APPS_NL.OT_CUSTOMER_NAME_EN_IDX ON APPS_NL.GEDIS_CUSTOMER_SUMMARY
(OT_CUSTOMER_NAME_EN)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS('datastore APPS_NL.OT_CUSTOMER_NAME_EN_STORE
                      lexer APPS_GLOBAL.GCS_DEFAULT_LEXER
                      stoplist APPS_GLOBAL.GCS_STOPLIST
                      sync (on commit)')
PARALLEL ( DEGREE 8 INSTANCES 1 );


CREATE INDEX APPS_NL.OT_CUSTOMER_NAME_IDX ON APPS_NL.GEDIS_CUSTOMER_SUMMARY
(OT_CUSTOMER_NAME)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS('datastore APPS_NL.OT_CUSTOMER_NAME_STORE
                      lexer APPS_GLOBAL.GCS_DEFAULT_LEXER
                      stoplist APPS_GLOBAL.GCS_STOPLIST
                      sync (on commit)')
PARALLEL ( DEGREE 8 INSTANCES 1 );


CREATE INDEX APPS_NL.OT_GCS_IDX ON APPS_NL.GEDIS_CUSTOMER_SUMMARY
(OT_DUMMY)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS('datastore APPS_NL.OT_CUSTOMER_SUMMARY_STORE
                      lexer APPS_GLOBAL.GCS_DEFAULT_LEXER
                      stoplist APPS_GLOBAL.GCS_STOPLIST
                      sync (on commit)')
PARALLEL ( DEGREE 8 INSTANCES 1 );

CREATE INDEX APPS_NL.OT_GCS_IDX ON APPS_NL.GEDIS_CUSTOMER_SUMMARY
(OT_DUMMY)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS('datastore APPS_NL.OT_CUSTOMER_SUMMARY_STORE
                      lexer APPS_GLOBAL.GCS_DEFAULT_LEXER
                      stoplist APPS_GLOBAL.GCS_STOPLIST
                      sync (on commit)')
PARALLEL ( DEGREE 8 INSTANCES 1 );

begin
    :new.OT_DUMMY := null;