Oracle Query to Replace Special Characters from a string

SELECT REGEXP_REPLACE('##$!%*~``$123&&!!__!','[^[:alnum:]'' '']', NULL) FROM dual

Oracle Queries/Commands to Kill Session

select * from v$access 

where object = 'XX_PACKAGE_NAME';

/* Pick the sids from above query and pass to the below query */

select * from v$session

where sid in (322,368);

Syntax: ALTER SYSTEM KILL SESSION 'SID,SERIAL#';

ALTER SYSTEM KILL SESSION '322,48848';

Query to Find Oracle Concurrent Request Trace File Location

/* In the below query pass request id value to bind variable &request_id*/
SELECT
req.request_id
,req.logfile_node_name node
,req.oracle_Process_id
,req.enable_trace
,dest.VALUE||'/'||LOWER(dbnm.VALUE)||'_ora_'||oracle_process_id||'.trc' trace_filename
,prog.user_concurrent_program_name
,execname.execution_file_name
,execname.subroutine_name
,phase_code
,status_code
,ses.SID
,ses.serial#
,ses.module
,ses.machine
FROM
fnd_concurrent_requests req
,v$session ses
,v$process proc
,v$parameter dest
,v$parameter dbnm
,fnd_concurrent_programs_vl prog
,fnd_executables execname
WHERE 1=1
AND req.request_id = &request_id
AND req.oracle_process_id=proc.spid(+)
AND proc.addr = ses.paddr(+)
AND dest.NAME='user_dump_dest'
AND dbnm.NAME='db_name'
AND req.concurrent_program_id = prog.concurrent_program_id
AND req.program_application_id = prog.application_id
AND prog.application_id = execname.application_id
AND prog.executable_id=execname.executable_id

Register a View as Table with Oracle Applications

/* In the below commands replace XXXX with the custom schema name where the view is created*/

CREATE OR REPLACE VIEW XXXX_Product_view AS (SELECT DISTINCT segment5 PRODUCT FROM gl_code_combinations);

CREATE OR REPLACE PUBLIC SYNONYM XXXX_PRODUCT FOR XXXX_Product_view;

EXEC ad_dd.register_table('XXXX','XXXX_PRODUCT','T');

EXEC ad_dd.register_column('XXXX','XXXX_PRODUCT','PRODUCT',1,'Varchar2',30,'N','N');

Script to Reset Oracle Application User Password from Backend

BEGIN
   IF fnd_user_pkg.changepassword ('&uname', '&pswd')
   THEN
      DBMS_OUTPUT.put_line ('Password changed successfully');
   ELSE
      DBMS_OUTPUT.put_line ('Failed changing password');
   END IF;
COMMIT;
END;

Query to retrieve Oracle open AR invoices by Batch Source

select b.trx_number,b.trx_date,x.name trx_type from apps.ra_customer_trx_all b
,apps.ar_payment_schedules_all a
,apps.ra_batch_sources_all d
,apps.ra_cust_trx_types_all x
where a.customer_trx_id=b.customer_trx_id
and d.batch_source_id=b.batch_source_id
and x.cust_trx_type_id=b.cust_trx_type_id
and amount_due_remaining<>0

Script to Add Responsibility to a Oracle Application User from Backend

/* Below script adds Sys Admin Responsibility to user FIRST.LAST*/
DECLARE
   lc_user_name              VARCHAR2 (100) := 'FIRST.LAST';
   lc_resp_appl_short_name   VARCHAR2 (100) := 'SYSADMIN';
   lc_responsibility_key     VARCHAR2 (100) := 'SYSTEM_ADMINISTRATOR';
   lc_security_group_key     VARCHAR2 (100) := 'STANDARD';
   ld_resp_start_date        DATE           := SYSDATE;
   ld_resp_end_date          DATE           := NULL;
BEGIN
   fnd_user_pkg.addresp (
                         username            => lc_user_name,
                         resp_app            => lc_resp_appl_short_name,
                         resp_key            => lc_responsibility_key,
                         security_group      => lc_security_group_key,
                         description         => NULL,
                         start_date          => ld_resp_start_date,
                         end_date            => ld_resp_end_date
                        );
   COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
      ROLLBACK;
      DBMS_OUTPUT.put_line (SQLERRM);
END;
/