Monthly Archives: February 2015

Oracle R12 Script to Update FND/HR/IBY/AP/PER Tables to divert email messages

BEGIN
/* Workflow needs to be running in order for cloned instance to be usable
Need to prevent workflow from sending emails or faxes automatically.
Any testing that requires emails or faxes to be sent will require destination
fax numbers/email addresses to be set manually

Start with email addresses (emails with Oracle.MailBox@OraGurus.com in
‘To:’ get dropped by sendmail)
*/

UPDATE fnd_user fu1
SET fu1.EMAIL_ADDRESS = ‘Oracle.MailBox@OraGurus.com’
WHERE fu1.EMAIL_ADDRESS IS NOT NULL;

/* FND User */
UPDATE fnd_user fu1
SET fu1.fax = NULL
WHERE fu1.fax IS NOT NULL;

/* HR Person */
UPDATE per_all_people_f pp1
SET pp1.EMAIL_ADDRESS = ‘Oracle.MailBox@OraGurus.com’
WHERE pp1.EMAIL_ADDRESS IS NOT NULL;
/* Now nullify all the email addresses and FAX numbers to prevent
Suppliers set up for these from getting them. If we set the
fax fields to dummy values the faxes get bounced by the right-fax
server to the CPU’s fax machine, and if we leave nulls in the fax
number fields for suppliers set to FAX the workflow procedure
fails, so we null the fax fields AND convert suppliers set up for
fax to be emailing to Oracle.MailBox@OraGurus.com
*/

/* Vendor Communication */
UPDATE AP_SUPPLIER_SITES_ALL pvs1
SET pvs1.fax = NULL,
pvs1.fax_area_code = NULL
WHERE (pvs1.fax IS NOT NULL OR pvs1.fax_area_code IS NOT NULL);
UPDATE AP_SUPPLIER_SITES_ALL pvs1
SET pvs1.EMAIL_ADDRESS = ‘Oracle.MailBox@OraGurus.com’,
pvs1.REMITTANCE_EMAIL = ‘Oracle.MailBox@OraGurus.com’
WHERE (pvs1.EMAIL_ADDRESS IS NOT NULL OR pvs1.REMITTANCE_EMAIL IS NOT NULL);
UPDATE AP_SUPPLIER_SITES_ALL pvs1
SET pvs1.supplier_notif_method = ‘EMAIL’,
pvs1.EMAIL_ADDRESS = ‘Oracle.MailBox@OraGurus.com’,
pvs1.REMITTANCE_EMAIL = ‘Oracle.MailBox@OraGurus.com’
WHERE pvs1.supplier_notif_method = ‘FAX’;

/* In R12, Seperate Remittance Advice and xdodelivery.cfg setup bypasses
the WF override email set and sends the emails directly to supplier
remit email addresses
*/

/* Vendor Remittance */
UPDATE IBY_EXTERNAL_PAYEES_ALL iepa
SET REMIT_ADVICE_DELIVERY_METHOD = ‘EMAIL’,
REMIT_ADVICE_EMAIL = ‘Oracle.MailBox@OraGurus.com’
WHERE REMIT_ADVICE_DELIVERY_METHOD = ‘FAX’;

UPDATE IBY_EXTERNAL_PAYEES_ALL iepa
SET REMIT_ADVICE_EMAIL = ‘Oracle.MailBox@OraGurus.com’
WHERE REMIT_ADVICE_DELIVERY_METHOD = ‘EMAIL’;
/* This one only necessary if you haven’t run the Concurrent Job to
sync the local workflow tables, which is where we’ll be under
normal cloning circumstances
*/

UPDATE WF_LOCAL_ROLES wflr1
SET wflr1.EMAIL_ADDRESS = ‘Oracle.MailBox@OraGurus.com’ WHERE wflr1.EMAIL_ADDRESS IS NOT NULL;

UPDATE WF_LOCAL_ROLES wflr1
SET wflr1.FAX = NULL WHERE wflr1.FAX IS NOT NULL;
/* To update the wf_notifications table if the status is still pending to be ‘SENT’
*/

UPDATE WF_NOTIFICATIONS
SET mail_status = ‘SENT’,
due_date = null
WHERE mail_status is null;
/* Some of the email addresses are in the profile options, in this case for
the option “PO: Secondary Email address”
This statement gets them whether set at system or whatever level
*/

update fnd_profile_option_values pov1
set pov1.PROFILE_OPTION_VALUE = ‘Oracle.MailBox@OraGurus.com’
where pov1.application_id||pov1.profile_Option_id = (
select po1.application_id||po1.profile_option_id
from fnd_profile_options po1
where profile_option_name = ‘PO_SECONDRY_EMAIL_ADD’);
/* The above does its job, but in testing some users get left with this
profile option set to ‘Oracle.MailBox@OraGurus.com’ and this is easily neglected, so we get rid of
all non-system level settings of this profile option
*/

delete from fnd_profile_option_values pov1
where pov1.application_id||pov1.profile_Option_id = (
select po1.application_id||po1.profile_option_id
from fnd_profile_options po1
where profile_option_name = ‘PO_SECONDRY_EMAIL_ADD’
and level_id <> 10001);
/* Customer Contacts (party/site) */
update hz_parties
set email_address=’Oracle.MailBox@OraGurus.com’
where email_address is not null;

update hz_contact_points
set EMAIL_ADDRESS = ‘Oracle.MailBox@OraGurus.com’
where email_address is not null;

COMMIT;

EXCEPTION
when others then
dbms_output.put_line(‘Error while resetting the workflow email address: ‘ || SQLCODE || substr(SQLERRM, 1, 200));
rollback;
END;
/
EXIT;