API To Add Responsibilty To User

/* Grant Particular Responsibility to a User */
SET serveroutput on
DECLARE
   v_user_name                                       VARCHAR2 (30) := UPPER ('Oracle FND User');
   v_resp                                            VARCHAR2 (255) := 'Application Administrator';
   v_resp_key                                        VARCHAR2 (30);
   v_app_short_name                                  VARCHAR2 (50);
BEGIN
   SELECT r.responsibility_key
        , a.application_short_name
     INTO v_resp_key
        , v_app_short_name
     FROM apps.fnd_responsibility_vl r
        , apps.fnd_application_vl a
    WHERE r.application_id = a.application_id
      AND UPPER (r.responsibility_name) = UPPER (v_resp);
   apps.fnd_user_pkg.addresp (username                      => v_user_name
                            , resp_app                      => v_app_short_name
                            , resp_key                      => v_resp_key
                            , security_group                => 'STANDARD'
                            , description                   => NULL
                            , start_date                    => SYSDATE
                            , end_date                      => NULL
                             );
   COMMIT;
   DBMS_OUTPUT.put_line ('Responsibility:' || v_resp || ' ' || 'is added to the User:' || v_user_name);
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Unable to add the responsibility due to' || SQLCODE || ' ' || SUBSTR (SQLERRM, 1, 100));
      ROLLBACK;
END;
/

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