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

Convert LONG datatypes to LOB's or Query on the Long Data type Columns

CREATE TABLE XX_LOG_STG(C1 NUMBER, C2 LONG)
/
INSERT INTO  XX_LOG_STG VALUES (1, 'LONG data to convert to CLOB')
/
-- Insert LONG into LOB column
CREATE TABLE XX_LOB_STG(C1 NUMBER, C2 CLOB);

-- Use TO_LOB function to convert LONG to LOB...
INSERT INTO  XX_LOB_STG
       SELECT C1, TO_LOB(C2) FROM XX_LOG_STG;

CREATE TABLE XX_DBA_TRIGGERS AS SELECT TRIGGER_NAME, TO_LOB(TRIGGER_BODY) AS TRIGGER_BODY

FROM DBA_TRIGGERS ;

-- Query on the Long Data type Column i.e. Trigger Body as below




SELECT * FROM XX_DBA_TRIGGERS WHERE UPPER(TRIGGER_BODY) LIKE '%FND_REQUEST%' ;

Working with Nested Objects

DECLARE
   l_return_status                                   VARCHAR2 (2000);
   l_msg_count                                       NUMBER;
   l_msg_data                                        VARCHAR2 (2000);
   l_party_merge_obj                                 hz_party_merge_obj;
   l_stage                                           NUMBER;
BEGIN
   l_stage                                                  := 10;
   hz_extract_merge_event_pkg.get_party_merge_event_data (p_init_msg_list               => 'T'
                                                        , p_batch_id                    => p_merge_batch_id
                                                        , p_merge_to_party_id           => p_merge_to_party_id
                                                        , p_get_merge_detail_flag       => 'Y'
                                                        , x_party_merge_obj             => l_party_merge_obj
                                                        , x_return_status               => l_return_status
                                                        , x_msg_count                   => l_msg_count
                                                        , x_msg_data                    => l_msg_data
                                                         );
   l_stage                                                  := 20;
   DBMS_OUTPUT.put_line ('Table Type Count : ' || l_party_merge_obj.merge_detail_objs.COUNT);

   IF l_return_status = fnd_api.g_ret_sts_success
   THEN
      l_stage                                                  := 30;



      IF l_party_merge_obj.merge_from_party_objs.COUNT > 0
      THEN
         FOR j IN l_party_merge_obj.merge_from_party_objs.FIRST .. l_party_merge_obj.merge_from_party_objs.LAST
         LOOP
            IF l_party_merge_obj.merge_from_party_objs (j).orig_sys_objs.COUNT > 0
            THEN
               FOR k IN
                  l_party_merge_obj.merge_from_party_objs (j).orig_sys_objs.FIRST .. l_party_merge_obj.merge_from_party_objs
                                                                                                                         (j).orig_sys_objs.LAST
               LOOP
                  INSERT INTO xxcdh_orig_sys_ref_obj_tbl
                       VALUES (l_party_merge_obj.batch_id
                             , l_party_merge_obj.batch_name
                             , l_party_merge_obj.merge_type
                             , l_party_merge_obj.automerge_flag
                             , l_party_merge_obj.created_by
                             , l_party_merge_obj.creation_date
                             , l_party_merge_obj.last_update_login
                             , l_party_merge_obj.last_update_date
                             , l_party_merge_obj.last_updated_by
                             , l_party_merge_obj.merge_from_party_objs (j).party_id
                             , l_party_merge_obj.merge_from_party_objs (j).party_number
                             , l_party_merge_obj.merge_from_party_objs (j).party_name
                             , l_party_merge_obj.merge_from_party_objs (j).party_type
                             , l_party_merge_obj.merge_from_party_objs (j).orig_sys_objs (k).action_type
                             , l_party_merge_obj.merge_from_party_objs (j).orig_sys_objs (k).orig_system_ref_id
                             , l_party_merge_obj.merge_from_party_objs (j).orig_sys_objs (k).orig_system
                             , l_party_merge_obj.merge_from_party_objs (j).orig_sys_objs (k).orig_system_reference
                             , l_party_merge_obj.merge_from_party_objs (j).orig_sys_objs (k).object_type
                             , l_party_merge_obj.merge_from_party_objs (j).orig_sys_objs (k).object_id
                             , l_party_merge_obj.merge_from_party_objs (j).orig_sys_objs (k).status
                             , l_party_merge_obj.merge_from_party_objs (j).orig_sys_objs (k).reason_code
                             , l_party_merge_obj.merge_from_party_objs (j).orig_sys_objs (k).old_orig_system_reference
                             , l_party_merge_obj.merge_from_party_objs (j).orig_sys_objs (k).start_date_active
                             , l_party_merge_obj.merge_from_party_objs (j).orig_sys_objs (k).end_date_active
                             , l_party_merge_obj.merge_from_party_objs (j).orig_sys_objs (k).attribute_category
                             , l_party_merge_obj.merge_from_party_objs (j).orig_sys_objs (k).attribute1
                             , l_party_merge_obj.merge_from_party_objs (j).orig_sys_objs (k).attribute2
                             , l_party_merge_obj.merge_from_party_objs (j).orig_sys_objs (k).attribute3
                             , l_party_merge_obj.merge_from_party_objs (j).orig_sys_objs (k).attribute4
                             , l_party_merge_obj.merge_from_party_objs (j).orig_sys_objs (k).attribute5
                             , l_party_merge_obj.merge_from_party_objs (j).orig_sys_objs (k).attribute6
                             , l_party_merge_obj.merge_from_party_objs (j).orig_sys_objs (k).attribute7
                             , l_party_merge_obj.merge_from_party_objs (j).orig_sys_objs (k).attribute8
                             , l_party_merge_obj.merge_from_party_objs (j).orig_sys_objs (k).attribute9
                             , l_party_merge_obj.merge_from_party_objs (j).orig_sys_objs (k).attribute10
                             , l_party_merge_obj.merge_from_party_objs (j).orig_sys_objs (k).attribute11
                             , l_party_merge_obj.merge_from_party_objs (j).orig_sys_objs (k).attribute12
                             , l_party_merge_obj.merge_from_party_objs (j).orig_sys_objs (k).attribute13
                             , l_party_merge_obj.merge_from_party_objs (j).orig_sys_objs (k).attribute14
                             , l_party_merge_obj.merge_from_party_objs (j).orig_sys_objs (k).attribute15
                             , l_party_merge_obj.merge_from_party_objs (j).orig_sys_objs (k).attribute16
                             , l_party_merge_obj.merge_from_party_objs (j).orig_sys_objs (k).attribute17
                             , l_party_merge_obj.merge_from_party_objs (j).orig_sys_objs (k).attribute18
                             , l_party_merge_obj.merge_from_party_objs (j).orig_sys_objs (k).attribute19
                             , l_party_merge_obj.merge_from_party_objs (j).orig_sys_objs (k).attribute20
                              );
               END LOOP;
            END IF;
         END LOOP;
      END IF;

      IF l_party_merge_obj.merge_to_party_obj.orig_sys_objs.COUNT > 0
      THEN
         FOR i IN
            l_party_merge_obj.merge_to_party_obj.orig_sys_objs.FIRST .. l_party_merge_obj.merge_to_party_obj.orig_sys_objs.LAST
         LOOP
            INSERT INTO xxcdh_party_orig_sys_ref_obj
                 VALUES (l_party_merge_obj.batch_id
                       , l_party_merge_obj.batch_name
                       , l_party_merge_obj.merge_type
                       , l_party_merge_obj.automerge_flag
                       , l_party_merge_obj.created_by
                       , l_party_merge_obj.creation_date
                       , l_party_merge_obj.last_update_login
                       , l_party_merge_obj.last_update_date
                       , l_party_merge_obj.last_updated_by
                       , l_party_merge_obj.merge_to_party_obj.party_id
                       , l_party_merge_obj.merge_to_party_obj.party_number
                       , l_party_merge_obj.merge_to_party_obj.party_name
                       , l_party_merge_obj.merge_to_party_obj.party_type
                       , l_party_merge_obj.merge_to_party_obj.orig_sys_objs (i).action_type
                       , l_party_merge_obj.merge_to_party_obj.orig_sys_objs (i).orig_system_ref_id
                       , l_party_merge_obj.merge_to_party_obj.orig_sys_objs (i).orig_system
                       , l_party_merge_obj.merge_to_party_obj.orig_sys_objs (i).orig_system_reference
                       , l_party_merge_obj.merge_to_party_obj.orig_sys_objs (i).object_type
                       , l_party_merge_obj.merge_to_party_obj.orig_sys_objs (i).object_id
                       , l_party_merge_obj.merge_to_party_obj.orig_sys_objs (i).status
                       , l_party_merge_obj.merge_to_party_obj.orig_sys_objs (i).reason_code
                       , l_party_merge_obj.merge_to_party_obj.orig_sys_objs (i).old_orig_system_reference
                       , l_party_merge_obj.merge_to_party_obj.orig_sys_objs (i).start_date_active
                       , l_party_merge_obj.merge_to_party_obj.orig_sys_objs (i).end_date_active
                       , l_party_merge_obj.merge_to_party_obj.orig_sys_objs (i).attribute_category
                       , l_party_merge_obj.merge_to_party_obj.orig_sys_objs (i).attribute1
                       , l_party_merge_obj.merge_to_party_obj.orig_sys_objs (i).attribute2
                       , l_party_merge_obj.merge_to_party_obj.orig_sys_objs (i).attribute3
                       , l_party_merge_obj.merge_to_party_obj.orig_sys_objs (i).attribute4
                       , l_party_merge_obj.merge_to_party_obj.orig_sys_objs (i).attribute5
                       , l_party_merge_obj.merge_to_party_obj.orig_sys_objs (i).attribute6
                       , l_party_merge_obj.merge_to_party_obj.orig_sys_objs (i).attribute7
                       , l_party_merge_obj.merge_to_party_obj.orig_sys_objs (i).attribute8
                       , l_party_merge_obj.merge_to_party_obj.orig_sys_objs (i).attribute9
                       , l_party_merge_obj.merge_to_party_obj.orig_sys_objs (i).attribute10
                       , l_party_merge_obj.merge_to_party_obj.orig_sys_objs (i).attribute11
                       , l_party_merge_obj.merge_to_party_obj.orig_sys_objs (i).attribute12
                       , l_party_merge_obj.merge_to_party_obj.orig_sys_objs (i).attribute13
                       , l_party_merge_obj.merge_to_party_obj.orig_sys_objs (i).attribute14
                       , l_party_merge_obj.merge_to_party_obj.orig_sys_objs (i).attribute15
                       , l_party_merge_obj.merge_to_party_obj.orig_sys_objs (i).attribute16
                       , l_party_merge_obj.merge_to_party_obj.orig_sys_objs (i).attribute17
                       , l_party_merge_obj.merge_to_party_obj.orig_sys_objs (i).attribute18
                       , l_party_merge_obj.merge_to_party_obj.orig_sys_objs (i).attribute19
                       , l_party_merge_obj.merge_to_party_obj.orig_sys_objs (i).attribute20
                        );
         END LOOP;
      END IF;

      IF l_party_merge_obj.merge_detail_objs.COUNT > 0
      THEN
         FOR l IN l_party_merge_obj.merge_detail_objs.FIRST .. l_party_merge_obj.merge_detail_objs.LAST
         LOOP
            IF l_party_merge_obj.merge_detail_objs (l).from_object_sys_ref_objs.COUNT > 0
            THEN
               FOR m IN
                  l_party_merge_obj.merge_detail_objs (l).from_object_sys_ref_objs.FIRST .. l_party_merge_obj.merge_detail_objs
                                                                                                                         (l).from_object_sys_ref_objs.LAST
               LOOP
                  INSERT INTO xxcdh_party_merge_detail_obj1
                       VALUES (l_party_merge_obj.batch_id
                             , l_party_merge_obj.batch_name
                             , l_party_merge_obj.merge_type
                             , l_party_merge_obj.automerge_flag
                             , l_party_merge_obj.created_by
                             , l_party_merge_obj.creation_date
                             , l_party_merge_obj.last_update_login
                             , l_party_merge_obj.last_update_date
                             , l_party_merge_obj.last_updated_by
                             , l_party_merge_obj.merge_detail_objs (l).object_type
                             , l_party_merge_obj.merge_detail_objs (l).operation_type
                             , l_party_merge_obj.merge_detail_objs (l).from_object_id
                             , l_party_merge_obj.merge_detail_objs (l).from_object_sys_ref_objs (m).action_type
                             , l_party_merge_obj.merge_detail_objs (l).from_object_sys_ref_objs (m).orig_system_ref_id
                             , l_party_merge_obj.merge_detail_objs (l).from_object_sys_ref_objs (m).orig_system
                             , l_party_merge_obj.merge_detail_objs (l).from_object_sys_ref_objs (m).orig_system_reference
                             , l_party_merge_obj.merge_detail_objs (l).from_object_sys_ref_objs (m).object_type
                             , l_party_merge_obj.merge_detail_objs (l).from_object_sys_ref_objs (m).status
                             , l_party_merge_obj.merge_detail_objs (l).from_object_sys_ref_objs (m).reason_code
                             , l_party_merge_obj.merge_detail_objs (l).from_object_sys_ref_objs (m).old_orig_system_reference
                             , l_party_merge_obj.merge_detail_objs (l).from_object_sys_ref_objs (m).start_date_active
                             , l_party_merge_obj.merge_detail_objs (l).from_object_sys_ref_objs (m).end_date_active
                             , l_party_merge_obj.merge_detail_objs (l).from_object_sys_ref_objs (m).attribute_category
                             , l_party_merge_obj.merge_detail_objs (l).from_object_sys_ref_objs (m).attribute1
                             , l_party_merge_obj.merge_detail_objs (l).from_object_sys_ref_objs (m).attribute2
                             , l_party_merge_obj.merge_detail_objs (l).from_object_sys_ref_objs (m).attribute3
                             , l_party_merge_obj.merge_detail_objs (l).from_object_sys_ref_objs (m).attribute4
                             , l_party_merge_obj.merge_detail_objs (l).from_object_sys_ref_objs (m).attribute5
                             , l_party_merge_obj.merge_detail_objs (l).from_object_sys_ref_objs (m).attribute6
                             , l_party_merge_obj.merge_detail_objs (l).from_object_sys_ref_objs (m).attribute7
                             , l_party_merge_obj.merge_detail_objs (l).from_object_sys_ref_objs (m).attribute8
                             , l_party_merge_obj.merge_detail_objs (l).from_object_sys_ref_objs (m).attribute9
                             , l_party_merge_obj.merge_detail_objs (l).from_object_sys_ref_objs (m).attribute10
                             , l_party_merge_obj.merge_detail_objs (l).from_object_sys_ref_objs (m).attribute11
                             , l_party_merge_obj.merge_detail_objs (l).from_object_sys_ref_objs (m).attribute12
                             , l_party_merge_obj.merge_detail_objs (l).from_object_sys_ref_objs (m).attribute13
                             , l_party_merge_obj.merge_detail_objs (l).from_object_sys_ref_objs (m).attribute14
                             , l_party_merge_obj.merge_detail_objs (l).from_object_sys_ref_objs (m).attribute15
                             , l_party_merge_obj.merge_detail_objs (l).from_object_sys_ref_objs (m).attribute16
                             , l_party_merge_obj.merge_detail_objs (l).from_object_sys_ref_objs (m).attribute17
                             , l_party_merge_obj.merge_detail_objs (l).from_object_sys_ref_objs (m).attribute18
                             , l_party_merge_obj.merge_detail_objs (l).from_object_sys_ref_objs (m).attribute19
                             , l_party_merge_obj.merge_detail_objs (l).from_object_sys_ref_objs (m).attribute20
                              );
               END LOOP;
            END IF;
         END LOOP;
      END IF;

      IF l_party_merge_obj.merge_detail_objs.COUNT > 0
      THEN
         FOR l IN l_party_merge_obj.merge_detail_objs.FIRST .. l_party_merge_obj.merge_detail_objs.LAST
         LOOP
            IF l_party_merge_obj.merge_detail_objs (l).to_object_sys_ref_objs.COUNT > 0
            THEN
               FOR n IN
                  l_party_merge_obj.merge_detail_objs (l).to_object_sys_ref_objs.FIRST .. l_party_merge_obj.merge_detail_objs
                                                                                                                         (l).to_object_sys_ref_objs.LAST
               LOOP
                  INSERT INTO xxcdh_party_merge_detail_obj2
                       VALUES (l_party_merge_obj.batch_id
                             , l_party_merge_obj.batch_name
                             , l_party_merge_obj.merge_type
                             , l_party_merge_obj.automerge_flag
                             , l_party_merge_obj.created_by
                             , l_party_merge_obj.creation_date
                             , l_party_merge_obj.last_update_login
                             , l_party_merge_obj.last_update_date
                             , l_party_merge_obj.last_updated_by
                             , l_party_merge_obj.merge_detail_objs (l).object_type
                             , l_party_merge_obj.merge_detail_objs (l).operation_type
                             , l_party_merge_obj.merge_detail_objs (l).to_object_id
                             , l_party_merge_obj.merge_detail_objs (l).to_object_sys_ref_objs (n).action_type
                             , l_party_merge_obj.merge_detail_objs (l).to_object_sys_ref_objs (n).orig_system_ref_id
                             , l_party_merge_obj.merge_detail_objs (l).to_object_sys_ref_objs (n).orig_system
                             , l_party_merge_obj.merge_detail_objs (l).to_object_sys_ref_objs (n).orig_system_reference
                             , l_party_merge_obj.merge_detail_objs (l).to_object_sys_ref_objs (n).object_type
                             , l_party_merge_obj.merge_detail_objs (l).to_object_sys_ref_objs (n).status
                             , l_party_merge_obj.merge_detail_objs (l).to_object_sys_ref_objs (n).reason_code
                             , l_party_merge_obj.merge_detail_objs (l).to_object_sys_ref_objs (n).old_orig_system_reference
                             , l_party_merge_obj.merge_detail_objs (l).to_object_sys_ref_objs (n).start_date_active
                             , l_party_merge_obj.merge_detail_objs (l).to_object_sys_ref_objs (n).end_date_active
                             , l_party_merge_obj.merge_detail_objs (l).to_object_sys_ref_objs (n).attribute_category
                             , l_party_merge_obj.merge_detail_objs (l).to_object_sys_ref_objs (n).attribute1
                             , l_party_merge_obj.merge_detail_objs (l).to_object_sys_ref_objs (n).attribute2
                             , l_party_merge_obj.merge_detail_objs (l).to_object_sys_ref_objs (n).attribute3
                             , l_party_merge_obj.merge_detail_objs (l).to_object_sys_ref_objs (n).attribute4
                             , l_party_merge_obj.merge_detail_objs (l).to_object_sys_ref_objs (n).attribute5
                             , l_party_merge_obj.merge_detail_objs (l).to_object_sys_ref_objs (n).attribute6
                             , l_party_merge_obj.merge_detail_objs (l).to_object_sys_ref_objs (n).attribute7
                             , l_party_merge_obj.merge_detail_objs (l).to_object_sys_ref_objs (n).attribute8
                             , l_party_merge_obj.merge_detail_objs (l).to_object_sys_ref_objs (n).attribute9
                             , l_party_merge_obj.merge_detail_objs (l).to_object_sys_ref_objs (n).attribute10
                             , l_party_merge_obj.merge_detail_objs (l).to_object_sys_ref_objs (n).attribute11
                             , l_party_merge_obj.merge_detail_objs (l).to_object_sys_ref_objs (n).attribute12
                             , l_party_merge_obj.merge_detail_objs (l).to_object_sys_ref_objs (n).attribute13
                             , l_party_merge_obj.merge_detail_objs (l).to_object_sys_ref_objs (n).attribute14
                             , l_party_merge_obj.merge_detail_objs (l).to_object_sys_ref_objs (n).attribute15
                             , l_party_merge_obj.merge_detail_objs (l).to_object_sys_ref_objs (n).attribute16
                             , l_party_merge_obj.merge_detail_objs (l).to_object_sys_ref_objs (n).attribute17
                             , l_party_merge_obj.merge_detail_objs (l).to_object_sys_ref_objs (n).attribute18
                             , l_party_merge_obj.merge_detail_objs (l).to_object_sys_ref_objs (n).attribute19
                             , l_party_merge_obj.merge_detail_objs (l).to_object_sys_ref_objs (n).attribute20
                              );
               END LOOP;
            END IF;
         END LOOP;
      END IF;


      IF l_party_merge_obj.merge_detail_objs.COUNT > 0
      THEN
         FOR l IN l_party_merge_obj.merge_detail_objs.FIRST .. l_party_merge_obj.merge_detail_objs.LAST
         LOOP
            IF l_party_merge_obj.merge_detail_objs (l).from_parent_obj_sys_ref_objs.COUNT > 0
            THEN
               FOR o IN
                  l_party_merge_obj.merge_detail_objs (l).from_parent_obj_sys_ref_objs.FIRST .. l_party_merge_obj.merge_detail_objs
                                                                                                                         (l).from_parent_obj_sys_ref_objs.LAST
               LOOP
                  INSERT INTO xxcdh_party_merge_detail_obj3
                       VALUES (l_party_merge_obj.batch_id
                             , l_party_merge_obj.batch_name
                             , l_party_merge_obj.merge_type
                             , l_party_merge_obj.automerge_flag
                             , l_party_merge_obj.created_by
                             , l_party_merge_obj.creation_date
                             , l_party_merge_obj.last_update_login
                             , l_party_merge_obj.last_update_date
                             , l_party_merge_obj.last_updated_by
                             , l_party_merge_obj.merge_detail_objs (l).object_type
                             , l_party_merge_obj.merge_detail_objs (l).operation_type
                             , l_party_merge_obj.merge_detail_objs (l).from_parent_object_id
                             , l_party_merge_obj.merge_detail_objs (l).from_parent_obj_sys_ref_objs (o).action_type
                             , l_party_merge_obj.merge_detail_objs (l).from_parent_obj_sys_ref_objs (o).orig_system_ref_id
                             , l_party_merge_obj.merge_detail_objs (l).from_parent_obj_sys_ref_objs (o).orig_system
                             , l_party_merge_obj.merge_detail_objs (l).from_parent_obj_sys_ref_objs (o).orig_system_reference
                             , l_party_merge_obj.merge_detail_objs (l).from_parent_obj_sys_ref_objs (o).object_type
                             , l_party_merge_obj.merge_detail_objs (l).from_parent_obj_sys_ref_objs (o).status
                             , l_party_merge_obj.merge_detail_objs (l).from_parent_obj_sys_ref_objs (o).reason_code
                             , l_party_merge_obj.merge_detail_objs (l).from_parent_obj_sys_ref_objs (o).old_orig_system_reference
                             , l_party_merge_obj.merge_detail_objs (l).from_parent_obj_sys_ref_objs (o).start_date_active
                             , l_party_merge_obj.merge_detail_objs (l).from_parent_obj_sys_ref_objs (o).end_date_active
                             , l_party_merge_obj.merge_detail_objs (l).from_parent_obj_sys_ref_objs (o).attribute_category
                             , l_party_merge_obj.merge_detail_objs (l).from_parent_obj_sys_ref_objs (o).attribute1
                             , l_party_merge_obj.merge_detail_objs (l).from_parent_obj_sys_ref_objs (o).attribute2
                             , l_party_merge_obj.merge_detail_objs (l).from_parent_obj_sys_ref_objs (o).attribute3
                             , l_party_merge_obj.merge_detail_objs (l).from_parent_obj_sys_ref_objs (o).attribute4
                             , l_party_merge_obj.merge_detail_objs (l).from_parent_obj_sys_ref_objs (o).attribute5
                             , l_party_merge_obj.merge_detail_objs (l).from_parent_obj_sys_ref_objs (o).attribute6
                             , l_party_merge_obj.merge_detail_objs (l).from_parent_obj_sys_ref_objs (o).attribute7
                             , l_party_merge_obj.merge_detail_objs (l).from_parent_obj_sys_ref_objs (o).attribute8
                             , l_party_merge_obj.merge_detail_objs (l).from_parent_obj_sys_ref_objs (o).attribute9
                             , l_party_merge_obj.merge_detail_objs (l).from_parent_obj_sys_ref_objs (o).attribute10
                             , l_party_merge_obj.merge_detail_objs (l).from_parent_obj_sys_ref_objs (o).attribute11
                             , l_party_merge_obj.merge_detail_objs (l).from_parent_obj_sys_ref_objs (o).attribute12
                             , l_party_merge_obj.merge_detail_objs (l).from_parent_obj_sys_ref_objs (o).attribute13
                             , l_party_merge_obj.merge_detail_objs (l).from_parent_obj_sys_ref_objs (o).attribute14
                             , l_party_merge_obj.merge_detail_objs (l).from_parent_obj_sys_ref_objs (o).attribute15
                             , l_party_merge_obj.merge_detail_objs (l).from_parent_obj_sys_ref_objs (o).attribute16
                             , l_party_merge_obj.merge_detail_objs (l).from_parent_obj_sys_ref_objs (o).attribute17
                             , l_party_merge_obj.merge_detail_objs (l).from_parent_obj_sys_ref_objs (o).attribute18
                             , l_party_merge_obj.merge_detail_objs (l).from_parent_obj_sys_ref_objs (o).attribute19
                             , l_party_merge_obj.merge_detail_objs (l).from_parent_obj_sys_ref_objs (o).attribute20
                              );
               END LOOP;
            END IF;
         END LOOP;
      END IF;



      IF l_party_merge_obj.merge_detail_objs.COUNT > 0
      THEN
         FOR l IN l_party_merge_obj.merge_detail_objs.FIRST .. l_party_merge_obj.merge_detail_objs.LAST
         LOOP
            IF l_party_merge_obj.merge_detail_objs (l).to_parent_obj_sys_ref_objs.COUNT > 0
            THEN
               FOR p IN
                  l_party_merge_obj.merge_detail_objs (l).to_parent_obj_sys_ref_objs.FIRST .. l_party_merge_obj.merge_detail_objs
                                                                                                                         (l).to_parent_obj_sys_ref_objs.LAST
               LOOP
                  INSERT INTO xxcdh_party_merge_detail_obj4
                       VALUES (l_party_merge_obj.batch_id
                             , l_party_merge_obj.batch_name
                             , l_party_merge_obj.merge_type
                             , l_party_merge_obj.automerge_flag
                             , l_party_merge_obj.created_by
                             , l_party_merge_obj.creation_date
                             , l_party_merge_obj.last_update_login
                             , l_party_merge_obj.last_update_date
                             , l_party_merge_obj.last_updated_by
                             , l_party_merge_obj.merge_detail_objs (l).object_type
                             , l_party_merge_obj.merge_detail_objs (l).operation_type
                             , l_party_merge_obj.merge_detail_objs (l).to_parent_object_id
                             , l_party_merge_obj.merge_detail_objs (l).to_parent_obj_sys_ref_objs (p).action_type
                             , l_party_merge_obj.merge_detail_objs (l).to_parent_obj_sys_ref_objs (p).orig_system_ref_id
                             , l_party_merge_obj.merge_detail_objs (l).to_parent_obj_sys_ref_objs (p).orig_system
                             , l_party_merge_obj.merge_detail_objs (l).to_parent_obj_sys_ref_objs (p).orig_system_reference
                             , l_party_merge_obj.merge_detail_objs (l).to_parent_obj_sys_ref_objs (p).object_type
                             , l_party_merge_obj.merge_detail_objs (l).to_parent_obj_sys_ref_objs (p).status
                             , l_party_merge_obj.merge_detail_objs (l).to_parent_obj_sys_ref_objs (p).reason_code
                             , l_party_merge_obj.merge_detail_objs (l).to_parent_obj_sys_ref_objs (p).old_orig_system_reference
                             , l_party_merge_obj.merge_detail_objs (l).to_parent_obj_sys_ref_objs (p).start_date_active
                             , l_party_merge_obj.merge_detail_objs (l).to_parent_obj_sys_ref_objs (p).end_date_active
                             , l_party_merge_obj.merge_detail_objs (l).to_parent_obj_sys_ref_objs (p).attribute_category
                             , l_party_merge_obj.merge_detail_objs (l).to_parent_obj_sys_ref_objs (p).attribute1
                             , l_party_merge_obj.merge_detail_objs (l).to_parent_obj_sys_ref_objs (p).attribute2
                             , l_party_merge_obj.merge_detail_objs (l).to_parent_obj_sys_ref_objs (p).attribute3
                             , l_party_merge_obj.merge_detail_objs (l).to_parent_obj_sys_ref_objs (p).attribute4
                             , l_party_merge_obj.merge_detail_objs (l).to_parent_obj_sys_ref_objs (p).attribute5
                             , l_party_merge_obj.merge_detail_objs (l).to_parent_obj_sys_ref_objs (p).attribute6
                             , l_party_merge_obj.merge_detail_objs (l).to_parent_obj_sys_ref_objs (p).attribute7
                             , l_party_merge_obj.merge_detail_objs (l).to_parent_obj_sys_ref_objs (p).attribute8
                             , l_party_merge_obj.merge_detail_objs (l).to_parent_obj_sys_ref_objs (p).attribute9
                             , l_party_merge_obj.merge_detail_objs (l).to_parent_obj_sys_ref_objs (p).attribute10
                             , l_party_merge_obj.merge_detail_objs (l).to_parent_obj_sys_ref_objs (p).attribute11
                             , l_party_merge_obj.merge_detail_objs (l).to_parent_obj_sys_ref_objs (p).attribute12
                             , l_party_merge_obj.merge_detail_objs (l).to_parent_obj_sys_ref_objs (p).attribute13
                             , l_party_merge_obj.merge_detail_objs (l).to_parent_obj_sys_ref_objs (p).attribute14
                             , l_party_merge_obj.merge_detail_objs (l).to_parent_obj_sys_ref_objs (p).attribute15
                             , l_party_merge_obj.merge_detail_objs (l).to_parent_obj_sys_ref_objs (p).attribute16
                             , l_party_merge_obj.merge_detail_objs (l).to_parent_obj_sys_ref_objs (p).attribute17
                             , l_party_merge_obj.merge_detail_objs (l).to_parent_obj_sys_ref_objs (p).attribute18
                             , l_party_merge_obj.merge_detail_objs (l).to_parent_obj_sys_ref_objs (p).attribute19
                             , l_party_merge_obj.merge_detail_objs (l).to_parent_obj_sys_ref_objs (p).attribute20
                              );
               END LOOP;
            END IF;
         END LOOP;
      END IF;

      COMMIT;
   END IF;

   IF l_msg_count > 0
   THEN
      FOR i IN 1 .. l_msg_count
      LOOP
         l_msg_data                                               := l_msg_data || ' ' || fnd_msg_pub.get (i, 'F');
      END LOOP;

      DBMS_OUTPUT.put_line ('l_msg_data::' || l_msg_data);
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      -- null;
      DBMS_OUTPUT.put_line (l_stage || ' Error :' || SQLERRM);
END;
/