How to set Workflow Mailer Override Address

Option 1 :

How to set Workflow Mailer Override Address from Backend ? (Doc ID 1533596.1)

Please use the following 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

It will show you

You have selected parameter : Test Address
Current value of parameter : NONE

Enter a value for the parameter :

Option 2

FND_SVC_COMP_PARAM_VALS_PKG.LOAD_ROW 

( x_component_name => ‘Workflow Notification Mailer’,
x_parameter_name => ‘TEST_ADDRESS’,
x_parameter_value => ‘test.mail@mail.com’
x_customization_level => ‘L’
x_object_version_number => -1,
x_owner => ‘ORACLE’
);

Commit;

Option 3

How to set "Set Override Address"?

1. Log into Oracle Applications Manager [OAM]
2. Navigate: Site Map / Administration tab / Workflow section / Notification Mailer / View Details / Set Override Address Button
3. Give a valid Email Address / Submit

[OR]

1. System Administration Responsibility
2. Navigate: Workflow Manager / Notification Mailer / View Details / Set Override Address Button
3. Give a valid Email Address / Submit
4. Mailer will send an email to the new Override Address that has been entered in OAM, just to verify the email Address is valid.
5. The email includes a Verification Code, that must be entered in OAM to confirm the new Override Address

How to clear "Set Override Address"?

Log into OAM.

Navigate: Site Map / Administration tab / Workflow section / Notification Mailer / View Details / Set Override Address Button

Click on button "Clear Override Address"

Option 4

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;

update
fnd_svc_comp_param_vals fscpv
set
fscpv.PARAMETER_VALUE = ''
where
fscpv.parameter_id in (
select
fscpt.parameter_id
from
fnd_svc_comp_params_tl fscpt
where
fscpt.display_name = 'Test Address');

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;

commit;

Setting the tnsnames.ora in Oracle SQL Developer

Setting the tnsnames.ora in Oracle SQL Developer


Tools --> Preferences --> Database --> Advanced --> TNS Names Directory

Oracle SQL MERGE statement

Use the MERGE statement to select rows from one or more sources for update or insertion into a table or view. You can specify conditions to determine whether to update or insert into the target table or view.

This statement is a convenient way to combine multiple operations. It lets you avoid multiple INSERT, UPDATE, and DELETE DML statements.

MERGE is a deterministic statement. You cannot update the same row of the target table multiple times in the same MERGE statement.


Example
CREATE TABLE bonuses (employee_id NUMBER, bonus NUMBER DEFAULT 100);

INSERT INTO bonuses(employee_id)
   (SELECT e.employee_id FROM employees e, orders o
   WHERE e.employee_id = o.sales_rep_id
   GROUP BY e.employee_id);

SELECT * FROM bonuses ORDER BY employee_id;

EMPLOYEE_ID      BONUS
----------- ----------
        153        100
        154        100
        155        100
        156        100
        158        100
        159        100
        160        100
        161        100
        163        100

MERGE INTO bonuses D
   USING (SELECT employee_id, salary, department_id FROM employees
   WHERE department_id = 80) S
   ON (D.employee_id = S.employee_id)
   WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary*.01
     DELETE WHERE (S.salary > 8000)
   WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus)
     VALUES (S.employee_id, S.salary*.01)
     WHERE (S.salary <= 8000);

SELECT * FROM bonuses ORDER BY employee_id;

EMPLOYEE_ID      BONUS
----------- ----------
        153        180
        154        175
        155        170
        159        180
        160        175
        161        170
        179        620
        173        610
        165        680
        166        640
        164        720
        172        730
        167        620
        171        740