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