SQL to convert GMT to EST with Daylight Savings

SELECT to_date(
           to_char(
             to_timestamp_tz(
               to_char(to_timestamp('2015-11-01 06:00:00','yyyy-mm-dd hh24:mi:ss'),'YYYYMMDDHH24:MI:SS')||' '||'GMT'
                            ,'YYYYMMDDHH24:MI:SS TZR') at time zone 'America/New_York'
                   ,'YYYYMMDDHH24:MI:SS')
                 ,'YYYYMMDDHH24:MI:SS') datelight_GMT_EST
  FROM DUAL

Supplier Remittance Advice

For remittance advice Oracle fetches the email address from 'iby_external_payees_all' table.

When testing any changes to the remittance advice make sure to update the email before running the concurrent program.

SELECT   aps.segment1 "Vendor Number"
        ,aps.vendor_name "Vendor Name"
        ,iepa.remit_advice_email "Remittance Advice Email"
  FROM  ap_suppliers aps
       ,iby_external_payees_all iepa
 WHERE  iepa.payee_party_id = aps.party_id
   AND  aps.vendor_name= '<>';


You can also see the same at Payment Details -> Supplier / Supplier Site Level. Navigate to “Separate Remittance Advice Delivery” Tab.

Update Workflow Administrator Role in Oracle Applications 11i/R12 from backend

For looking at workflow details that are owned by other users or status diagram in Oracle Applications use the following update statement to update.

By default (in 11i & R12) this role is set to user sysadmin (In old versions 11.5.8 or prior, it used to set to *)

UPDATE wf_resources
   SET text = ’ * ’
 WHERE NAME = ’wf_admin_role’;