Friday, January 13, 2012

Oracle EBS: Cloning Script To Clear Email Addresses

We use the following cloning script to clear down email addresses on each of the cloned systems we create from our production EBS system. In exchange we have setup a mailbox which all the test systems feed into and each tiem we create a new clone we add an email address to the mailbox so that it receives emails from the new clone (this way you can filter the messages in the mailbox to get the ones you're interested in for the clone system you are looking at).

The script itself sends an email with what it's done to the mail box (so we know when the system was last cloned), it also includes a check to make sure it's not being run against production (PROD) by accident!

The actual script (which was written against Oracle version 12.1.3) is below;

declare
  c_EMAIL constant varchar2(60) := '%DATABASE%_APPSTEST@<your domain>';

  v_Email    PO_VENDOR_SITES_ALL.EMAIL_ADDRESS%TYPE;
  v_Database v$Database.NAME%TYPE;
  v_Body     CLOB;
  procedure addToBody(p_Text in varchar2) as
  begin
    dbms_lob.writeappend(v_Body, length(p_Text) + 4, p_Text || '<br>');
    dbms_output.put_line(p_Text);
  end;
begin
  dbms_lob.createtemporary(lob_loc => v_Body, cache => False);

  addToBody('0010 Checking database instance ... ');
  select name into v_Database from v$Database;
  dbms_output.put_line('020 Database instance = "' || v_Database || '"');
  if v_Database = 'PROD' then
    raise_application_error(-20005,
                            'This script must NOT be run against PROD.');
  end if;

  v_Email := replace(c_EMAIL, '%DATABASE%', v_Database);
  addToBody('0100 All emails will be set to "' || v_Email || '"');

  addToBody('0110 Updating AP supplier contacts (AP_SUPPLIER_CONTACTS) records ... ');
  update AP_SUPPLIER_CONTACTS
     set EMAIL_ADDRESS = v_Email
   where EMAIL_ADDRESS is not null;
  addToBody(' ... ' || to_char(SQL%ROWCOUNT) || ' records updated');

  addToBody('0120 Updating AP supplier site details (AP_SUPPLIER_SITES_ALL) records ... ');
  update AP_SUPPLIER_SITES_ALL
     set EMAIL_ADDRESS = v_Email
   where EMAIL_ADDRESS is not null;
  addToBody(' ... ' || to_char(SQL%ROWCOUNT) ||
            ' records updated (EMAIL_ADDRESS)');
  update AP_SUPPLIER_SITES_ALL
     set REMITTANCE_EMAIL = v_Email
   where REMITTANCE_EMAIL is not null;
  addToBody(' ... ' || to_char(SQL%ROWCOUNT) ||
            ' records updated (REMITTANCE_EMAIL)');

  addToBody('0130 Updating User (FND_USER) records ... ');
  update fnd_user
     set email_address = v_Email
   where email_address is not null;
  addToBody(' ... ' || to_char(SQL%ROWCOUNT) || ' records updated');

  addToBody('0140 Updating Vendor Contact Points (HZ_CONTACT_POINTS) records ... ');
  update AR.HZ_CONTACT_POINTS
     set EMAIL_ADDRESS = v_Email
   where EMAIL_ADDRESS is not null;
  addToBody(' ... ' || to_char(SQL%ROWCOUNT) || ' records updated');

  addToBody('0150 Updating Parties (HZ_PARTIES) records ... ');
  update HZ_PARTIES
     set email_address = v_Email
   where email_address is not null;
  addToBody(' ... ' || to_char(SQL%ROWCOUNT) || ' records updated');

  addToBody('0160 Updating external payees (IBY_EXTERNAL_PAYEES_ALL) records ... ');
  update IBY.IBY_EXTERNAL_PAYEES_ALL
     set remit_advice_email = v_Email
   where remit_advice_email is not null;
  addToBody(' ... ' || to_char(SQL%ROWCOUNT) || ' records updated');

  addToBody('0170 Updating Employee (PER_PEOPLE_X) records ... ');
  update PER_PEOPLE_X
     set email_address = v_Email
   where email_address is not null;
  addToBody(' ... ' || to_char(SQL%ROWCOUNT) || ' records updated');

  addToBody('0180 Updating points (RA_CONTACT_PHONES_INT_ALL) records ... ');
  update RA_CONTACT_PHONES_INT_ALL
     set EMAIL_ADDRESS = v_Email
   where EMAIL_ADDRESS is not null;
  addToBody(' ... ' || to_char(SQL%ROWCOUNT) || ' records updated');

  addToBody('0190 Updating Supplier (PO_VENDOR_SITES_ALL) records ... ');
  update PO_VENDOR_SITES_ALL
     set email_address = v_Email
   where email_address is not null;
  addToBody(' ... ' || to_char(SQL%ROWCOUNT) || ' records updated');

  commit;

  -- Send an e-mail to the mailbox so you know when it was last refreshed.
  napp_emailmanager.addEmail(p_From    => v_Email,
                             p_To      => v_Email,
                             p_Subject => v_Database || ' refreshed on ' ||
                                          to_char(SYSDATE, 'DD-MON-YYYY'),
                             p_Body    => v_Body);
  napp_emailmanager.processMails;
end;


Have I missed anything?



Post a Comment