Script to Delete Concurrent Programs from Oracle E-Business Suite 11i and R12

There might be instances wherein one needs to remove an invalid Concurrent Program setup in Oracle E-Business Suite, including its Concurrent Executable setup. This cannot be done from the front-end application, as it only allows you to disable the Concurrent Program.

Fortunately, we have the option to do this from the back-end:

declare

    cursor conc_cur
    is
    SELECT  appl.application_short_name application
          , CONC.USER_CONCURRENT_PROGRAM_NAME
          , EXEC.EXECUTABLE_NAME
          , CONC.CONCURRENT_PROGRAM_NAME
          , lookup.meaning execution_method_code
          , EXEC.EXECUTION_FILE_NAME      
    FROM    FND_CONCURRENT_PROGRAMS_VL CONC
          , FND_EXECUTABLES EXEC
          , FND_APPLICATION_VL APPL
          , fnd_lookup_values lookup
    WHERE   EXEC.EXECUTABLE_ID = CONC.EXECUTABLE_ID
    AND     EXEC.EXECUTION_FILE_NAME = :pkg_name.procedure_name -- change this to the Concurrent Program Executable
    AND     APPL.APPLICATION_ID = EXEC.APPLICATION_ID
    AND     LOOKUP.LOOKUP_CODE = EXEC.EXECUTION_METHOD_CODE
    and     LOOKUP.lookup_type='CP_EXECUTION_METHOD_CODE';

Begin

    for c1 in conc_cur loop

        fnd_program.delete_program(c1.CONCURRENT_PROGRAM_NAME, c1.application);
        fnd_program.delete_executable(c1.EXECUTABLE_NAME, c1.application);
    
    end loop;
    
    commit;
    
End;

Please note that you would need to have access to the APPS schema or a user with similar or higher privileges.

Update: June 28, 2019: For EBS 12.2.5, the above query might not return any values because of the Views. Use the following code instead:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32

declare cursor conc_cur is SELECT appl.application_short_name application --, CONC.USER_CONCURRENT_PROGRAM_NAME , EXEC.EXECUTABLE_NAME , CONC.CONCURRENT_PROGRAM_NAME , lookup.meaning execution_method_code , EXEC.EXECUTION_FILE_NAME FROM FND_CONCURRENT_PROGRAMS CONC , FND_EXECUTABLES EXEC , FND_APPLICATION APPL , fnd_lookup_values lookup WHERE EXEC.EXECUTABLE_ID = CONC.EXECUTABLE_ID AND EXEC.EXECUTION_FILE_NAME = 'GLMSUMMACCNEW' AND APPL.APPLICATION_ID = EXEC.APPLICATION_ID AND LOOKUP.LOOKUP_CODE = EXEC.EXECUTION_METHOD_CODE and LOOKUP.lookup_type='CP_EXECUTION_METHOD_CODE'; Begin for c1 in conc_cur loop fnd_program.delete_program(c1.CONCURRENT_PROGRAM_NAME, c1.application); fnd_program.delete_executable(c1.EXECUTABLE_NAME, c1.application); end loop; commit; End;

For more full-detailed Tutorials and Tips, check out #TheOracleProdigy at https://lifeofanoracleprodigy.blogspot.com/
Follow The Oracle Prodigy on Facebook (https://www.facebook.com/theOracleProdigy/) and Twitter (https://twitter.com/D_OracleProdigy)

Use imported transaction number to search for Invoices in Oracle Fusion Receivables

Usually, when an invoice is imported into Oracle Fusion Receivables via AutoInvoice, the third-party application's miscellaneous details are stored in Descriptive Flexfields (DFFs). These details are often exclusive only to that application and isn't suitable to be mapped to seeded oracle fields, such as internal transaction numbers, descriptors, etc. 

In the example below, the details "Booking Number" and "Line Number" from a third-party application is stored in a DFF:

However, these DFFs are not available on the search screen and cannot be used to reference the newly-imported transactions:

To resolve this, we can use the "Reference" field to link the newly-imported transactions to their old transaction numbers. Below are the steps to set this up.

1. Identify what DFF attribute holds the old transaction number (ex. INTERFACE_LINE_ATTRIBUTE1). Make sure this is properly defined and deployed before proceeding.
2. Login to Oracle Fusion Applications and go to the "Setup and Maintenance" screen
3. Search for the Task "Manage Transaction Sources" and query for the Transaction Source to be used.
4. Under the "Source Defaults" section, inspect the LOV field "Reference Field Default Value" and choose the corresponding Header Attribute. For example, if your old transaction number is stored in INTERFACE_LINE_ATTRIBUTE1, then choose INTERFACE_HEADER_ATTRIBUTE1.

1. Save your changes and close the task.
2. Proceed to import a new sale transaction into Oracle Fusion Receivables using AutoInvoice. For example, the transaction number from the third-party application is "REFINV1" and is stored in INTERFACE_LINE_ATTRIBUTE1.
3. Once AutoInvoice has successfully completed and imported the transaction, the reference value "REFINV1" will be stored in RA_CUSTOMER_TRX_ALL.CT_REFERENCE and RA_CUST_TRX_LINES_ALL.ATTRIBUTE1 DFF:

1. It will be visible from the application in the "Cross Reference" field at the header Level and at also visible at the Line-level DFFs.
2. You may now use this to search for the imported transactions:

Please note that this will only take effect to transactions that have been imported after the Transaction Source setup.

Correctly display images in Separate Remittance Advise report in Oracle Fusion Applications

Currently, there is a requirement to modify the seeded Separate Remittance Advise (SRA) layout to add the Organization's specific branding such as logos, icons, etc.

To do this, just follow the same procedure in customizing seeded layouts in Oracle BI Publisher Enterprise and upload the custom layout. 

However, the SRA report has some limits on how it can be delivered. Depending on the setup of the Supplier site or the Payment Process Profile, we can either have it either "Printed" (save the PDF output and print or email it to Suppliers) or delivered automatically to the suppliers via Email (as an HTML).

However, one issue with delivering it via email is that when using layouts that contain pictures, the pictures explode because its not formatted correctly. Take the layout below as an example:

The sample layout above uses a Header in the RTF Template. Although this looks normal when generated manually (as a PDF), this layout's pictures will explode when delivered as an HTML Email:

Above is a screenshot of the report delivered via email, Zoomed at 20%. The text isn't even visible anymore due to the company logo eating up all the space. 

Follow the simple steps below resolves this issue:

1. Open the RTF layout in Word. Click on the picture to be re-sized. From the ribbon, under the  Picture Tools / Format tab, click on "Compress Pictures":

1. Change the photo's resolution to "Web/Screen" to that the resolution would be decreased to 96 dpi. Save and re-test:

1. Now the report output delivered is readable from the Email message body.

Follow my other Social Media Accounts!
Facebook Page: https://www.facebook.com/theOracleProdigy/
Twitter: https://twitter.com/D_OracleProdigy

Customize the layouts of seeded Oracle Fusion Applications reports

Oftentimes, an organization will require Oracle Fusion's seeded reports to be modified to add specific logos, icons for Branding and Marketing purposes. The best way to do this is to use BI Publisher's "Customize" function.

Before you can actually modify the report, you have to first know the following:

1. Identify under what group / pillar the report belongs to (Ex. Financials, HCM, Procurement)
2. Identify the report's functionality (Ex. Invoice Report, Payroll, Purchase Order)
3. Identify if the report is being called from the application screen via a function button, a menu, etc (more on this later).
4. Familiarity with the expected report output and its data.
5. Have the sufficient roles assigned such as BI Author and BI Administrator

For this example, we will modify the Purchase Order report and add a Custom logo.
To begin customizing a seeded Oracle Fusion Report, you may execute the following steps:

• Login to Oracle Fusion Applications using your Username and Password
• Click on the browser's address bar and change the URL to below to go to the BI Publisher Enterprise console

• Once inside BI Publisher Enterprise console, go to "Catalog" to access the Reports Catalog

• On the Folders pane, Expand "Shared Folders", then select the application that contains the Report to be modified (ex. "Procurement"), then open the folder of the report's functionality (ex. "Purchasing")

• Once inside the folder, choose the correct report and click on "More", then choose "Customize"

• This will effectively copy the said report and folder structure under the "Custom" folder.

• Going back to the report, you should now see the reports dashboard. It is recommended that you download the current layouts and use it as a guide for your custom Layout. Click on "Edit" to download desired seeded layout and use Microsoft Word to edit the RTF.

• Once you've finalized your custom layout, you may upload it by clicking on "View a List" (highlighted in Green). DO NOT use the "Add New Layout" button as this will prompt you to have a saved sample data in BI Publisher's metadata.
• You will then see a new page that will show the each report's properties (i.e. available Outputs Formats, Default Format, Active/Inactive, etc). Click on the (+) icon to add a new Layout.

• You again be shown a new page that will allow you to upload layouts. Click on the "Upload" icon to upload the customized RTF file. The other options are not recommended as they have limited formatting capabilities and may not be suitable for Production use. 

• Once the upload has been completed, the custom layout will be visible in the reports dashboard. Don't forget to select it as the default layout if this will be the layout to be used by your organization.

Depending on the report, there might be additional configurations or setups required to run these reports from the application screens. Reports such as Purchase Orders, Customer Statements need to have some configurations changed. See the links in the said reports for more information on how to configure them.

Check out the step-by-step video demonstration below for more information:

For more information, you may check out the Oracle Documentation on BI Reports Customization

Follow The Oracle Prodigy on Facebook (https://www.facebook.com/theOracleProdigy/) and Twitter (https://twitter.com/D_OracleProdigy)

Script to assign a responsibility to an application user in Oracle E-Business Suite

Use the Script below to assign a certain responsibility to an application user in Oracle E-Business Suite, applicable to both 11i and R12:

DECLARE 

    cursor add_resp is
 SELECT  DISTINCT 
         APPL.APPLICATION_SHORT_NAME
       , RESP.RESPONSIBILITY_KEY       
       , SEC.SECURITY_GROUP_KEY
 FROM    FND_RESPONSIBILITY_VL RESP
       , FND_APPLICATION_VL APPL
       , FND_SECURITY_GROUPS_VL SEC
       , FND_USER_RESP_GROUPS RESPG
 WHERE   RESP.RESPONSIBILITY_NAME = 'System Administrator'
 AND     RESP.APPLICATION_ID = APPL.APPLICATION_ID
 AND     SEC.SECURITY_GROUP_ID = RESPG.SECURITY_GROUP_ID
 AND     RESPG.RESPONSIBILITY_APPLICATION_ID = RESP.APPLICATION_ID
 AND     RESP.RESPONSIBILITY_ID = RESPG.RESPONSIBILITY_ID;

 lc_user_name varchar2(100) := 'ISIPJ85';
 
BEGIN 

 FOR i in add_resp loop

      fnd_user_pkg.addresp 
      (  
       username             => lc_user_name, 
         resp_app             => i.APPLICATION_SHORT_NAME
     ,resp_key             => i.RESPONSIBILITY_KEY
     ,security_group    => i.SECURITY_GROUP_KEY
     ,description          => NULL
     ,start_date           => SYSDATE
     ,end_date             => NULL 
     );
    
    end loop;

 COMMIT; 

EXCEPTION 
 WHEN OTHERS THEN 
    ROLLBACK; 
    DBMS_OUTPUT.PUT_LINE('Error Encountered: '||SQLERRM); 
END; 
/

SHOW ERR;

Take note that you would need enough database privilege to the APPS schema to run this script.

Script to Copy Oracle EBS Responsibilities from One User to Another

Use the Script below to copy an application user's responsibilities to another in Oracle E-Business Suite, applicable to both 11i and R12:

DECLARE 

    cursor add_resp is
 SELECT  DISTINCT 
         APPL.APPLICATION_SHORT_NAME
       , RESP.RESPONSIBILITY_KEY       
       , SEC.SECURITY_GROUP_KEY
 FROM    FND_RESPONSIBILITY_VL RESP
       , FND_APPLICATION_VL APPL
       , FND_SECURITY_GROUPS_VL SEC
       , FND_USER_RESP_GROUPS_DIRECT RESPG
    , fnd_user fu
 WHERE   RESP.APPLICATION_ID = APPL.APPLICATION_ID
 AND     SEC.SECURITY_GROUP_ID = RESPG.SECURITY_GROUP_ID
 AND     RESPG.RESPONSIBILITY_APPLICATION_ID = RESP.APPLICATION_ID
 AND     RESP.RESPONSIBILITY_ID = RESPG.RESPONSIBILITY_ID
 and  FU.USER_ID = RESPG.USER_ID 
 AND  fu.user_name = 'SYSADMIN'; -- Change the username with the one you want to copy from

 lc_user_name varchar2(100) := 'JISIP';
 -- Change the username with the one you want to assign the responsibilies to
 
BEGIN 

 FOR i in add_resp loop

  fnd_user_pkg.addresp 
  (  
   username        => lc_user_name, 
   resp_app        => i.APPLICATION_SHORT_NAME
   ,resp_key       => i.RESPONSIBILITY_KEY
   ,security_group => i.SECURITY_GROUP_KEY
   ,description    => NULL
   ,start_date     => SYSDATE
   ,end_date       => NULL 
  );
  
 end loop;  

 COMMIT; 

EXCEPTION 
 WHEN OTHERS THEN 
    ROLLBACK; 
    DBMS_OUTPUT.PUT_LINE('Error Encountered: '||SQLERRM); 
END; 
/

SHOW ERR;

Take note that you would need enough database privilege to the APPS schema to run this script.

Update! May 23, 2019:

I tried the same script for E-Business Suite 12.2.5 but it didn't work. I made a few changes and this did the trick:

set SERVEROUTPUT ON;
DECLARE 

    cursor add_resp is
 SELECT  DISTINCT 
   APPL.APPLICATION_SHORT_NAME
    , RESP2.RESPONSIBILITY_KEY       
    , SEC.SECURITY_GROUP_KEY
 FROM    FND_RESPONSIBILITY_tl RESP
    , FND_APPLICATION APPL
    , FND_SECURITY_GROUPS SEC
    , FND_USER_RESP_GROUPS RESPG
    , FND_RESPONSIBILITY RESP2
 WHERE   RESP.RESPONSIBILITY_NAME = 'System Administrator'
 and  RESP2.RESPONSIBILITY_id = RESP.RESPONSIBILITY_id
 AND     RESP.APPLICATION_ID = APPL.APPLICATION_ID
 AND     SEC.SECURITY_GROUP_ID = RESPG.SECURITY_GROUP_ID
 AND     RESPG.RESPONSIBILITY_APPLICATION_ID = RESP.APPLICATION_ID
 AND     RESP.RESPONSIBILITY_ID = RESPG.RESPONSIBILITY_ID;

 lc_user_name varchar2(100) := 'XXSAMPLE';
 
 v_count number := 0;
 
BEGIN 

 FOR i in add_resp loop

      fnd_user_pkg.addresp 
      (  
       username             => lc_user_name, 
         resp_app             => i.APPLICATION_SHORT_NAME
     ,resp_key             => i.RESPONSIBILITY_KEY
     ,security_group    => i.SECURITY_GROUP_KEY
     ,description          => NULL
     ,start_date           => SYSDATE
     ,end_date             => NULL 
     );
  
  v_count := v_count + 1;
  
    end loop;
 
 DBMS_OUTPUT.PUT_LINE('Responsibilities Added to '|| lc_user_name ||':'|| v_count ); 

 COMMIT; 

EXCEPTION 
 WHEN OTHERS THEN 
    ROLLBACK; 
    DBMS_OUTPUT.PUT_LINE('Error Encountered: '||SQLERRM); 
END; 
/

SHOW ERR;

For more full-detailed Tutorials and Tips, check out #TheOracleProdigy at https://lifeofanoracleprodigy.blogspot.com/
Follow The Oracle Prodigy on Facebook (https://www.facebook.com/theOracleProdigy/) and Twitter (https://twitter.com/D_OracleProdigy)

Script to create an Application User in Oracle E-Business Suite

Use the Script below to create an Application User in Oracle E-Business Suite, applicable to both 11i and R12:

DECLARE 

   lc_user_name                          VARCHAR2(100)     := 'ISIPJ85'; 
   lc_user_password                    VARCHAR2(100)     := 'Oracle123'; 
   ld_user_start_date                    DATE                := TO_DATE(SYSDATE); 
   ld_user_end_date                     VARCHAR2(100)    := NULL; 
   ld_password_date                    VARCHAR2(100)    := TO_DATE(SYSDATE); 
   ld_password_lifespan_days     NUMBER              := 90; 
   ln_person_id                          NUMBER              := NULL; 
   lc_email_address                      VARCHAR2(100)    := 'migs.isip.23@gmail.com';

BEGIN

  fnd_user_pkg.createuser 
  (  x_user_name                             => lc_user_name, 
     x_owner                                    => NULL, 
     x_unencrypted_password         => lc_user_password, 
     x_start_date                               => ld_user_start_date, 
     x_end_date                                 => ld_user_end_date, 
     x_password_date                      => ld_password_date, 
     x_password_lifespan_days       => ld_password_lifespan_days, 
     x_employee_id                          => ln_person_id, 
     x_email_address                       => lc_email_address 
 ); 
  
 COMMIT;


EXCEPTION 
 WHEN OTHERS THEN 
    ROLLBACK; 
    DBMS_OUTPUT.PUT_LINE(SQLERRM); 
END; 
/

SHOW ERR;

Take note that you would need enough database privilege to the APPS schema to run this script.

Creating a Custom Payment Process Profile (PPP) in Oracle Fusion Applications

A Payment Process Profile (PPP) is basically a profile when creating payments. It links all of the Payment setups together. Steps are listed below on how to create a custom PPP:

Navigation: Setup and Maintenance > Search for " Manage Payment Process Profiles" then click on the "+" Icon to add a new PPP

Populate the fields below as Required. 

Item	Remarks
Payment File Format	The File Format that will be used when the Payment is Created
Processing Type	Choose Electronic if this will be sent over SFTP or Email, Printed if it will be physically printed to Paper (i.e. Cheques)
Payment System	Choose the Payment System you're going to use and the Accounts you're going to be using with it
Payment System Accounts	Input a Configuration Profile Name, including the Transmission Protocols (where the file will be sent)

Creating a Custom Payment System in Oracle Fusion Applications

Navigation: Setup and Maintenance > Search for "Manage Payment Systems", then click on Create:

Put in the required values in the Name, Code, Processing Model and Bank fields. In the Format fields, you can add the Payment Formats that this Payment System may use.

Add the Transmission Protocols you’re going to use for this Payment System. You can use a custom Transmission Protocol if you're connecting to a third-party server.

Note: this can be used for both Inbound and Outbound transmission. 

For more full-detailed Tutorials and Tips, check out #TheOracleProdigy at https://lifeofanoracleprodigy.blogspot.com/

Follow The Oracle Prodigy on Facebook (https://www.facebook.com/theOracleProdigy/) and Twitter (https://twitter.com/D_OracleProdigy)

Creating a Custom Payment File Format in Oracle Fusion Applications

Depending on the Country, there are instances where a Bank requires their own payment file format. An example would be MT9, which is exclusive to New Zealand banks. Below are the steps to define a new Payment File Format:

1. Upload a custom RTF File into BI Publisher as "eText" and save.
2. Navigate to: Setup and Maintenance > Task Pane > Search > search for the task “Manage Format”
3. Select the correct format type (in this case, "Disbursement Payment File Formats"), and click on create:

1. Input a meaningful name to the Payment format, and choose the custom BI Publisher template you've uploaded.

1. This can now be used as a valid Payment Format to Banks.

Changing the Purchase Order Layout in Oracle Fusion Applications

Often times there is a requirement to change the Purchase Order PDF report during implementation to add a company's logo or branding, or add or remove information from the layout itself.

Below are the steps for you to modify a Purchase Order Report:

1. Upload a custom Purchase Order Template to BI Publisher. (see the link for the steps)
2. Navigate to Setup and Maintenance
3. Click on the Task Pane then click on Search

1. Search for the Task "Configure Procurement Business Function"
2. Select a Business Unit
3. Click on the "Document Types" Tab and choose "Purchase Order"

1. In the Document Type Details, select the desired Purchase Order layout.
2. Click on Save and Close
3. Test the new Purchase Order Layout by going to the Procurement menu > Purchase Orders, then click on the Task Pane and Select "Manage Orders"
4. Select a Purchase Order and then "View PDF"

For more information, you may check out the Oracle Documentation on BI Reports Customization

Script to Reset Password in Oracle E-Business Suite 11i and R12

Use the Script below to reset a User's password in Oracle E-Business Suite, applicable to both 11i and R12:

declare

 v_user_name  varchar2(100) := 'SYSADMIN';
 v_unencrypted_password varchar2(100) := 'oracle123';

begin

 apps.nd_user_pkg.updateuser
 (
     x_user_name   => v_user_name
   , x_owner    => null
   , x_unencrypted_password  => v_unencrypted_password
   , x_password_date   => sysdate
 );
  
 commit;
 
end;
/

Take note that you would need enough database privilege to the APPS schema to run this script.