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:

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)