The Oracle Prodigy: 2018

Get the XML Extract of Payments or Separate Remittance Advise from Oracle Fusion Applications

When an organization is issuing Payments to Suppliers and sending Remittance Advise,

there would be instances where issues would arise (Incorrect Amounts, Invalid Supplier Details, etc.) and there is a need to review the XML data fusion is sending out.

However, getting the XML Extract of Payments or Remittance Advises in Fusion can be quite difficult as compared to other Fusion Reports.

In comparison, getting XML data for AR Invoices/Credit Memos are particularly easy, as the option to download its XML is already included when running its ESS job ("Print Receivables Transactions").

On the other hand, Payments or Remittance Advises don't have this capability.

Luckily, there are two workarounds Oracle has identified and I have personally tested both.

Listed below are the workarounds, including their advantages and disadvantages:

Use BI Publisher's ad-hoc querying capabilities.

The advantage of this option is there is no need for the actual process to be re-run, particularly useful if the issue has occurred in Production.
The downside of this option is sufficient BI Administrator Privileges is needed to create the ad-hoc Data Model that will retrieve the XML.

Modify the Report's Output Format to XML and re-run the process.

Unfortunately, I don't actually see an advantage with regards to this option.
Because actual process needs to be re-run, this option would be risky if done in the Production Environment, as the application might re-send the data to suppliers or the Bank, creating confusion.

For this post, we will focus on the Option # 1. The steps to do this are as follows:

Login to Oracle Fusion Applications using a Username that has the sufficient Custom BI Role (inherited BI Admin or BI Author role)
Go to BI Publisher by appending "xmlpserver" on the browser's URL: (i.e. https://xxxx.xx.xxx.oraclecloud.com/xmlpserver)
Create a New Data Model

Create a New SQL Data Set:

Use the Below Query with the appropriate Payment_File_Number

select document 
from  iby_trxn_documents 
where  payment_instruction_id = &Payment_File_Number
and  doctype = 100

Run this Data Set and Click on "Export".

Open this output in either Notepad or Notepad++ and re-save it as an XML file.

The resulting XML file would be the actual XML data used by the Payment File and Remittance Advise.

Using Simplified IF-ELSE condition in BI Publisher

In Oracle BI Publisher allows the use of Conditional Statements in RTF Templates. It uses similar logical conditions in PL/SQL such as IF-THEN, CHOOSE-WHEN, etc. More details and examples can be found from Fusion Middleware Report Designer's Guide for Oracle Business Intelligence Publisher.

However, the documentation provided only shows a scenario wherein you have just one condition. Example is below:

<?if:VENDOR_NAME='COMPANY A'?>This is the Vendor A<?end if?>

However, the code becomes a bit too troublesome and long if you need to have an ELSE condition:

<?if:VENDOR_NAME='COMPANY A'?>This is Vendor A<?end if?>
<?if:VENDOR_NAME!='COMPANY A'?>This is NOT Vendor A<?end if?>

To simplify this, you may use the XSLT function IFELSE that addresses this requirement:

<?xdoxslt:ifelse(VENDOR_NAME='COMPANY A',This is Vendor A,This is NOT Vendor A)?>

It works similar to the DECODE function in PL/SQL.
If the given condition is Satisfied, it will return the first parameter, if not, then it will return the second parameter.

Seems simple enough, eh?
Let me know in the comments if there's any issues!

Deactivate the seeded Separate Remittance Advise Layout in Oracle Fusion Applications

Even if the seeded Separate Remittance Advise (SRA) Layout has already been deactivated from BI Publisher, , it may still appear on the LOV of the SRA Screen.

Take the sample below. the Custom SRA Layout is already selected as the default and the Seeded layout is already Inactive:

However, the seeded layout can still be selected when we go into Payments > Task List > Send Separate Remittance Advise:

To suppress this, go into Setup and Maintenance > Task List > Search and key in "Manage Formats" and search for "Disbursement Separate Remittance Advise Formats":

Open the seeded layout and end-date it:

This will effectively suppress the layout from being selected from the SRA screen:

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:

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.

Identify what DFF attribute holds the old transaction number (ex. INTERFACE_LINE_ATTRIBUTE1). Make sure this is properly defined and deployed before proceeding.
Login to Oracle Fusion Applications and go to the "Setup and Maintenance" screen
Search for the Task "Manage Transaction Sources" and query for the Transaction Source to be used.
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.

Save your changes and close the task.
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.
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:

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.
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:

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":

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

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:

Identify under what group / pillar the report belongs to (Ex. Financials, HCM, Procurement)
Identify the report's functionality (Ex. Invoice Report, Payroll, Purchase Order)
Identify if the report is being called from the application screen via a function button, a menu, etc (more on this later).
Familiarity with the expected report output and its data.
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;

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:

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

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

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:

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

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

In the Document Type Details, select the desired Purchase Order layout.
Click on Save and Close
Test the new Purchase Order Layout by going to the Procurement menu > Purchase Orders, then click on the Task Pane and Select "Manage Orders"
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.