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%' ;