ORA-29861: domain index is marked LOADING/FAILED/UNUSABLE

1. Run the Below Script

SELECT OWNER, INDEX_NAME, INDEX_TYPE, STATUS, DOMIDX_STATUS, DOMIDX_OPSTATUS ,TABLE_NAME FROM DBA_INDEXES
WHERE INDEX_TYPE='DOMAIN'
AND INDEX_NAME LIKE 'HZ%'

the result of the above query will be

OWNER    INDEX_NAME    INDEX_TYPE    STATUS    DOMIDX_STATUS    DOMIDX_OPSTATUS    TABLE_NAME

AR    HZ_STAGE_PARTIES_T1    DOMAIN    VALID    VALID    FAILED    HZ_STAGED_PARTIES

2. Drop the Invalid Index.

3. Create script as mentioned below

CREATE INDEX AR.HZ_STAGE_CONTACT_T1 ON AR.HZ_STAGED_CONTACTS
    (CONCAT_COL) INDEXTYPE IS CTXSYS.CONTEXT

It will resolve the ORA-29861

Script To Delete the Business Events and Subscriptions

SELECT we.guid event_guid
     , wes.guid subscription_guid
     , wes.rule_function
  FROM wf_events we
     , wf_event_subscriptions wes
 WHERE we.NAME = 'oracle.apps.ar.hz.CustAccount.create'
   AND wes.event_filter_guid = we.guid
   AND wes.status = 'ENABLED';

EXEC WF_EVENTS_PKG.DELETE_ROW(:event_guid);

EXEC WF_EVENT_SUBSCRIPTIONS_PKG.DELETE_ROW(:subscription_guid);

COMMIT;

Convert Number to Words

SELECT DECODE (SIGN (:p_number),  -1, 'Negative ',  0, 'Zero',  NULL)
       || DECODE (SIGN (ABS (:p_number)), +1, TO_CHAR ( TO_DATE ( ABS (:p_number), 'J'), 'Jsp'))
  FROM DUAL
/

Query to Build the Relationship Hierarchy (HZ_RELATIONSHIP) in Oracle Apps

SELECT DISTINCT LPAD (' ', LEVEL * 2) || LEVEL
              , CONNECT_BY_ROOT hp1.party_name AS ROOT
              , SYS_CONNECT_BY_PATH(hp1.party_name, ':') AS CHAIN            
              , hp1.party_name parent_party
              , hp1.party_id parent_party_id
              , hp1.party_number parent_party_number
              , hp.party_name child_party
              , hp.party_id child_party_id
              , hp.party_number child_party_number
           FROM hz_relationships hr
              , hz_parties hp
              , hz_parties hp1
          WHERE object_id = hp.party_id
            AND hp1.party_id = hr.subject_id
            AND hp.status = 'A'
            AND hr.status = 'A'
            AND hr.direction_code = 'P'
            AND relationship_type = :p_hierarchy_type 
     START WITH hp1.party_id = :p_parent_party_id 
            AND relationship_type = :p_hierarchy_type 
            AND hr.status = 'A'
     CONNECT BY NOCYCLE PRIOR hr.object_id = hr.subject_id
            AND hr.object_type = PRIOR hr.subject_type
            AND hr.object_table_name = PRIOR hr.subject_table_name
            AND hr.direction_code = 'P'
            AND hr.status = 'A'
       ORDER SIBLINGS BY hp.party_name

SQL to Delete Duplicate Rows in a Table

DELETE FROM xx_table 
 WHERE ROWID NOT IN (SELECT MIN(ROWID) 
                       FROM xx_table 
                      GROUP BY col1, col2....);