Create or Assign New Temporary tablespace in Oracle Database

1.Create Temporary Tablespace TempNew

2.Make the default tablespace to TemNew

3.Drop temp tablespace TEMP

First Query To Check current temporary tablespace 

SELECT * FROM database_properties WHERE property_name like '%TABLESPACE'

1) Create another Temporary Tablespace TEMP2

 -CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE '/ebs/TOSC/db/data/temp2_new.dbf' SIZE 3g TABLESPACE GROUP TEMP;

2) Make Default Database temp tablespace  TEMP2

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP2;

3) drop tablespace temp1 INCLUDING CONTENTS AND DATAFILES;

How to Cancel Concurrent Requests from Backend Oracle Apps ( EBS )

select status_code,phase_code from fnd_concurrent_requests where  request_id=&1;

select fcr.CONCURRENT_PROGRAM_ID,fcp.CONCURRENT_PROGRAM_NAME from
fnd_concurrent_requests fcr , fnd_concurrent_programs fcp
where
1=1
and fcr.CONCURRENT_PROGRAM_ID=fcp.CONCURRENT_PROGRAM_ID
and fcp.CONCURRENT_PROGRAM_NAME like '%OSC%AR%'

select CONCURRENT_PROGRAM_NAME from fnd_concurrent_programs  where CONCURRENT_PROGRAM_NAME like '%OSC%AR%H%'
select * from fnd_concurrent_programs_tl where USER_CONCURRENT_PROGRAM_NAME like '%OSC%Dynomax%On%'

UPDATE fnd_concurrent_requests
SET phase_code = 'C', status_code = 'X'
WHERE status_code IN ('Q','I')
AND requested_start_date > SYSDATE
AND hold_flag = 'N'
AND CONCURRENT_PROGRAM_ID=90353

select request_id,phase_code , status_code from fnd_concurrent_requests
WHERE status_code IN ('Q','I')
AND requested_start_date > SYSDATE
AND hold_flag = 'N'
AND CONCURRENT_PROGRAM_ID=90353;

HOW TO SET THE OVERRIDE ADDRESS in WORKFLOW MAILER in Oracle Apps (EBS)

HOW TO SET THE OVERRIDE ADDRESS in WORKFLOW MAILER
------------------------------------------------------------------------------------------------
How can I change Workflow Notification Mailer parameters without login to OAM?
Solution
You can use the following script to change any Workflow Notification Mailer parameter or even any workflow agent listener parameter without login to the application

Do the following

1.Run the following script $FND_TOP/sql/afsvcpup.sql from sqlplus as apps user
2.For prompt Enter Component Id: enter id corresponds to Workflow Notification Mailer
3.For prompt Enter the Comp Param Id to update enter id corresponds to parameter you want to change
4.For prompt Enter a value for the parameter enter your parameter value

Provide steps to allow the Workflow Mailer to Override an Address.

To resolve the issue test the following steps in a development instance and then migrate accordingly:
1. Use the script $FND_TOP/sql/afsvcpup.sql.

This will enable to change any and all or your Mailer components.
A. Run the script
B. It will show you the components - pick the comp id for the Workflow Mailer - default is 10006
C. then it will show you the parameters and their values for the mailer
D. Pick the comp param id for the Override address (it will say Test Address ) - default is 10093

2. The script will display the following:
You have selected parameter : Test Address
Current value of parameter : NONE

Enter a value for the parameter : <Here you will put the email address you want >
3. Retest the Workflow mailer and confirm the override now works as expected.
--Check the workflow services using the below query.


select fsc.COMPONENT_NAME,fsc.STARTUP_MODE,fsc.COMPONENT_STATUS
from APPS.FND_CONCURRENT_QUEUES_VL fcq, fnd_svc_components fsc
where fsc.concurrent_queue_id = fcq.concurrent_queue_id(+)
order by COMPONENT_STATUS , STARTUP_MODE , COMPONENT_NAME;

--
To Check What is the Current Email Address Set

SQL> select fscpv.parameter_value
    from fnd_svc_comp_params_tl fscpt
    ,fnd_svc_comp_param_vals fscpv
    where fscpt.display_name = 'Test Address'
    and fscpt.parameter_id = fscpv.parameter_id;  2    3    4    5

PARAMETER_VALUE
--------------------------------------------------------------------------------
xyz.abc@domain.com

Option 2 or  Second approach  to update over ride address
======================
Also one alternate is there to set it from back-end using the below query
------------------------------------------------------------------------------------------
update fnd_svc_comp_param_vals
set    parameter_value = '&EnterEmailID'
where  parameter_id =
( select parameter_id
 from   fnd_svc_comp_params_tl
 where  display_name = 'Test Address');

How to create a trigger to send Email notifications in Oracle Database

 CREATE OR REPLACE TRIGGER APPS.xx_send_mail_trigger

AFTER insert

ON dept

REFERENCING NEW AS NEW OLD AS OLD

   FOR EACH ROW

  6  DECLARE

PRAGMA AUTONOMOUS_TRANSACTION;

tmpVar NUMBER;

   v_sqlerrm varchar2(500);



      var1 varchar2(250);

      var2 varchar2(250);

   BEGIN

      tmpVar := 0;

      v_sqlerrm :=NULL;

   UTL_MAIL.send(sender     =>  'xyz@gmail.com',

               recipients =>  'xyz@gmail.com',

                   cc         =>  'xyz@gmail.com',

                   bcc        =>  'xyz@gmail.com',

               subject    => 'UTL_MAIL Test',

                   message    => 'If you get this message it worked!');

       COMMIT;



   END xx_send_mail_trigger;

   /



Trigger created.



CREATE or REPLACE TRIGGER TR_TEST

AFTER INSERT ON dept_test

REFERENCING NEW AS NEW OLD AS OLD

FOR EACH ROW

DECLARE

PRAGMA AUTONOMOUS_TRANSACTION;

tmpVar NUMBER;

a NUMBER;

v_sqlerrm varchar2(500);



   var1 varchar2(250);

   var2 varchar2(250);

BEGIN

   tmpVar := 0;

   v_sqlerrm :=NULL;

SELECT deptno INTO a FROM dept_test WHERE deptno=:NEW.deptno;

DBMS_OUTPUT.PUT_LINE('This is to inform that following deliveries has been shipped in Oracle' || ',' || a);



UTL_MAIL.send(sender     =>  'xyz@gmail.com',

                recipients =>  'xyz@gmail.com',

                cc         =>  'xyz@gmail.com',

                bcc        =>  'xyz@gmail.com',

                subject    => 'This is to inform that following deliveries has been shipped in Oracle',

                message    => 'This is to inform that following deliveries has been shipped in Oracle');

    COMMIT;

END TR_TEST;

/


Trigger created.

How to debug SMTP or IMAP server network connections for Workflow mailer in Oracle Apps (EBS)

Below command will give you the debug log for SMTP Network Connections for Workflow mailer
in Oracle Apps
(please substitute the values as per your environment  in the below comand)

$AFJVAPRG -classpath $AF_CLASSPATH -Dprotocol=smtp \
-Ddbcfile=$FND_SECURE/PROD.dbc \
-Dport=25 -Dssl=Y \
-Dtruststore=/home/applprod/.keystore \
-Dserver=mail.oscdubai.com \
-Daccount=xyz@gmail.com -Dpassword=pasword \
-Dconnect_timeout=120 -Ddebug=Y \
-DdebugMailSession=Y oracle.apps.fnd.wf.mailer.Mailer



Below command will give you the debug log for IMAP  Network Connections for Workflow mailer in Oracle Apps


$AFJVAPRG -classpath $AF_CLASSPATH -Dprotocol=imap \
-Ddbcfile=$FND_SECURE/PROD.dbc \
-Dport=25 -Dssl=Y \
-Dtruststore=/home/applprod/.keystore \
-Dserver=mail.oscdubai.com \
-Daccount=xyz@gmail.com -Dpassword=pasword \
-Dconnect_timeout=120 -Ddebug=Y \
-DdebugMailSession=Y oracle.apps.fnd.wf.mailer.Mailer




However to Debug the flow you can pick up a particular notification id in WF_NOTIFICATIONS table which is having issue and run the below sql located in $FND_TOP/sql

Workflow debugging scripts wfmlrdbg.sql and wfbesdbg.sql

Query to find the Workflow Log file location (R12.2.x) 

=========================================

select fl.meaning,fcp.process_status_code,

decode(fcq.concurrent_queue_name,'WFMLRSVC','maile r container','WFALSNRSVC','listener container',fcq.concurrent_queue_name),

fcp.concurrent_process_id,os_process_id, fcp.logfile_name

from fnd_concurrent_queues fcq, fnd_concurrent_processes fcp , fnd_lookups fl

where fcq.concurrent_queue_id=fcp.concurrent_queue_id and fcp.process_status_code='A'

and fl.lookup_type='CP_PROCESS_STATUS_CODE' and

fl.lookup_code=fcp.process_status_code

and concurrent_queue_name in('WFMLRSVC')

order by fcp.logfile_name;

How to use GMAIL ( IMAP / SMTP ) for your Workflow Notification mailer

Using GMAIL's IMAP & SMTP Server for  Configuring the Oracle Workflow Mailer 
===========================================


Doc ID 2052059.1
===============

1. Reference the following https://blogs.oracle.com/oracleworkflow/entry/using_gmail_smtp_and_imap

2. Create a New Account in GMAIL:

Located at https://www.gmail.com/intl/en/mail/help/about.html

Create an Account


Name = emailwork  charlery

Choose the username:

emailwork.charlery  <@gmail.com>

ie: emailwork.charlery@gmail.com

Password:

imapacct16



3. Once signed onto the new GMAIL account, go to the Left Menu.

-        Hover the mouse over the <More> menu, go down to the “Create New Label> Link.

-        Create a PROCESS, then a DISCARD Folder/Label.



 4. Record the GMAIL Server IMAP and SMTP Settings:



SMTP Server



HOST NAME

smtp.gmail.com

SSL Port

465

TLS/SSL required

Yes

User Name

emailwork.charlery@gmail.com

Password

imapacct16

IMAP Server



Host Name

imap.gmail.com

SSL Port

993

TLS/SSL Required

Yes

User Name

emailwork.charlery@gmail.com

Password

imapacct16



5. Install GMAIL SSL Certificates into the Concurrent Manager Tier:

  a) Copy the following GMAIL SSL Certificate into a file called gmail.cer



-----BEGIN CERTIFICATE-----

MIIDWzCCAsSgAwIBAgIKaNPuGwADAAAisjANBgkqhkiG9w0BAQUFADBGMQswCQYD

VQQGEwJVUzETMBEGA1UEChMKR29vZ2xlIEluYzEiMCAGA1UEAxMZR29vZ2xlIElu

dGVybmV0IEF1dGhvcml0eTAeFw0xMTAyMTYwNDQzMDRaFw0xMjAyMTYwNDUzMDRa

MGgxCzAJBgNVBAYTAlVTMRMwEQYDVQQIEwpDYWxpZm9ybmlhMRYwFAYDVQQHEw1N

b3VudGFpbiBWaWV3MRMwEQYDVQQKEwpHb29nbGUgSW5jMRcwFQYDVQQDEw5pbWFw

LmdtYWlsLmNvbTCBnzANBgkqhkiG9w0BAQEFAAOBjQAwgYkCgYEAqfPyPSEHpfzv

Xx+9zGUxoxcOXFrGKCbZ8bfUd8JonC7rfId32t0gyAoLCgM6eU4lN05VenNZUoCh

L/nrX+ApdMQv9UFV58aYSBMU/pMmK5GXansbXlpHao09Mc8eur2xV+4cnEtxUvzp

co/OaG15HDXcr46c6hN6P4EEFRcb0ccCAwEAAaOCASwwggEoMB0GA1UdDgQWBBQj

27IIOfeIMyk1hDRzfALz4WpRtzAfBgNVHSMEGDAWgBS/wDDr9UMRPme6npH7/Gra

42sSJDBbBgNVHR8EVDBSMFCgTqBMhkpodHRwOi8vd3d3LmdzdGF0aWMuY29tL0dv

b2dsZUludGVybmV0QXV0aG9yaXR5L0dvb2dsZUludGVybmV0QXV0aG9yaXR5LmNy

bDBmBggrBgEFBQcBAQRaMFgwVgYIKwYBBQUHMAKGSmh0dHA6Ly93d3cuZ3N0YXRp

Yy5jb20vR29vZ2xlSW50ZXJuZXRBdXRob3JpdHkvR29vZ2xlSW50ZXJuZXRBdXRo

b3JpdHkuY3J0MCEGCSsGAQQBgjcUAgQUHhIAVwBlAGIAUwBlAHIAdgBlAHIwDQYJ

KoZIhvcNAQEFBQADgYEAxHVhW4aII3BPrKQGUdhOLMmdUyyr3TVmhJM9tPKhcKQ/

IcBYUev6gLsB7FH/n2bIJkkIilwZWIsj9jVJaQyJWP84Hjs3kus4fTpAOHKkLqrb

IZDYjwVueLmbOqr1U1bNe4E/LTyEf37+Y5hcveWBQduIZnHn1sDE2gA7LnUxvAU=

-----END CERTIFICATE-----



   b) Save the file gmail.cer into $ AF_JRE_TOP/lib/security/ directory

   c) Change permissions on gmail.cer to 755.

       $  chmod 755 gmail.cer

   d) Install the SSL Certificate into:

        $  keytool -import -trustcacerts -keystore $AF_JRE_TOP/lib/security/cacerts  -storepass changeit - alias gmail-lnx_chainnedcert -file gmail.cer

  One will see:

 Keytool_command_gmail

Answer: Yes

Trust this certificate? [no]:  Yes

Certificate was added to keystore



6. Run the Mailer command from the Concurrent Manager Tier to test the IMAP Connection:

$AFJVAPRG -classpath $AF_CLASSPATH -Dprotocol=imap -Ddbcfile=$FND_SECURE/$TWO_TASK.dbc -Dserver=imap.gmail.com -Dport=993 -Dssl=Y -Dtruststore=$AF_JRE_TOP/lib/security/cacerts -Daccount=<gmail username> -Dpassword=<password> -Dconnect_timeout=120 -Ddebug=Y -Dlogfile=GmailImapTest.log -DdebugMailSession=Y oracle.apps.fnd.wf.mailer.Mailer

ie:

$AFJVAPRG -classpath $AF_CLASSPATH -Dprotocol=imap -Ddbcfile=$FND_SECURE/VIS.dbc -Dport=993 -Dssl=Y -Dtruststore=$AF_JRE_TOP/lib/security/cacerts -Dserver=imap.gmail.com -Daccount=emailwork.charlery -Dpassword=imapacct16 -Dconnect_timeout=120 -Ddebug=Y -Dlogfile=GmailImapTest.log  -DdebugMailSession=Y oracle.apps.fnd.wf.mailer.Mailer



7. Run the Mailer command from the Concurrent Manager Tier to Test the SMTP Connection:

$AFJVAPRG -classpath $AF_CLASSPATH -Dprotocol=smtp -Ddbcfile=$FND_SECURE/$TWO_TASK.dbc -Dserver=smtp.gmail.com -Dport=465 -Dssl=Y -Dtruststore=$AF_JRE_TOP/lib/security/cacerts -Daccount=<gmail username> -Dpassword=<password> -Dconnect_timeout=120 -Ddebug=Y -Dlogfile=GmailSmtpTest.log -DdebugMailSession=Y oracle.apps.fnd.wf.mailer.Mailer

ie:

$AFJVAPRG -classpath $AF_CLASSPATH -Dprotocol=smtp -Ddbcfile=$FND_SECURE/VIS.dbc -Dserver=smtp.gmail.com -Dport=465 -Dssl=Y -Dtruststore=$AF_JRE_TOP/lib/security/gmail.cer -Daccount=emailwork.charlery -Dpassword=imapacct16 -Dconnect_timeout=120 -Ddebug=Y -Dlogfile=GmailSmtpTest.log -DdebugMailSession=Y oracle.apps.fnd.wf.mailer.Mailer

Query to Find responsibility associated for a Concurrent request

Query to Find responsibility associated for a Concurrent request
=============================================

 SELECT frt.responsibility_name, frg.request_group_name,
    frgu.request_unit_type,frgu.request_unit_id,
    fcpt.user_concurrent_program_name
    FROM fnd_Responsibility fr, fnd_responsibility_tl frt,
    fnd_request_groups frg, fnd_request_group_units frgu,
    fnd_concurrent_programs_tl fcpt
    WHERE frt.responsibility_id = fr.responsibility_id
    AND frg.request_group_id = fr.request_group_id
    AND frgu.request_group_id = frg.request_group_id
    AND fcpt.concurrent_program_id = frgu.request_unit_id
    AND frt.LANGUAGE = USERENV('LANG')
    AND fcpt.LANGUAGE = USERENV('LANG')
    AND fcpt.user_concurrent_program_name = :conc_prg_name
    ORDER BY 1,2,3,4