Oracle Workflow - Standard Concurrent Manager Activities

Oracle workflow provides some standard activities for invoking concurrent programs from workflow. The follow gives a sneak peak into the features related to this.

Execute Concurrent Program Activity -> Calls FND_WF_STANDARD.EXECUTECONCPROGRAM
It submits an Oracle Applications concurrent program from your workflow process and waits for it to complete
Submit Concurrent Program Activity -> Calls FND_WF_STANDARD.SUBMITCONCPROGRAM
It submits an Oracle Applications concurrent program from your workflow process, but does not wait for it to execute or complete.
Wait for Concurrent Program Activity -> Calls FND_WF_STANDARD.WAITFORCONCPROGRAM
If you submit a concurrent program from your workflow process, you can use the Wait for Concurrent Program activity as a means of blocking the process from further execution until the concurrent program completes.

Oracle Cash Managment - Bank Statement Reconcilation Tables

Bank reconciliation is one of the important process in Oracle Cash Management.

Below are the list of some tables and views which can help in generating reports.

Statement Interface Tables:
1. CE_STATEMENT_HEADERS_INT_ALL - Statement Headers
2. CE_STATEMENT_LINES_INTERFACE - Statement Lines

Statement Data Tables

AP_BANK_ACCOUNTS - Contains Bank account details

CE_STATEMENT_HEADERS_ALL - Contains bank statement headers

CE_STATEMENT_LINES - Contains bank statement lines, The status column shows if the line is reconciled or not

CE_STATEMENT_RECONCILS_ALL - Tables linking Statement Tables and various entities ( Receipts/Payments) Columns Reference Types(PAYMENT/RECEIPT/JE_LINE etc) and Reference ID can be used for linkage.

CE_RECONCILED_TRANSACTIONS_V - All reconciled Transactions
CE_101_RECONCILED_V/CE_200_RECONCILED_V/CE_222_RECONCILED_V has the reconciled transactions from GL/AP/AR respectively.

CE_AVAILABLE_TRANSACTIONS_V - All available transactions
Unreconciled Payments - CE_200_TRANSACTIONS_V
Unreconciled Receipts(Type=CASH) - CE_222_TRANSACTIONS_V
Miscellaneous(Type=MISC) - CE_222_TRANSACTIONS_V
Journal - CE_101_TRANSACTIONS_V
Batches - CE_AVAILABLE_BATCHES_V

Package CE_AUTO_BANK_MATCH controls most of the views.procedures like set_all will set the application ids to yes for the views to pick the data.

CE: Bank Account Security Access controls the data which can be viewed

Oracle View all Workflows.

In most cases users can see only workflows which below to the particular user.

Use the following query to find out who can see all workflows.

SELECT TEXT FROM WF_RESOURCES
WHERE name like 'WF_ADMIN_ROLE%';

You will find text like 'FND_RESP|FND|FNDWF_ADMIN_WEB|STANDARD' or SYSADMIN in the output. So a System Admin or Workflow Admin can see all the workflows across users.

Incase you want to update the role to some specific responsiblity use the following SQL.

UPDATE WF_RESOURCES
SET TEXT='FND_RESP|FND|FNDWF_ADMIN|STANDARD'
WHERE NAME = 'WF_ADMIN_ROLE'

--12345 is the responsibility id.
UPDATE WF_RESOURCES
SET TEXT='FND_RESP0:12345'
WHERE NAME = 'WF_ADMIN_ROLE';

View output for concurrent programs run by other users

Its pretty common being developers we would want to see the output of programs run by other users. Set the profile 'Concurrent:Report Access Level' -> Responsibility at user level.Navigate to the responsibility where the program is run and you should be able to see the output files.

Use the below query to get the responsibility the program is run.

select frt.responsibility_name from fnd_concurrent_requests fcr, FND_RESPONSIBILITY_TL frt where frt.responsibility_id= fcr.responsibility_id and request_id = x_req_id ;

Oracle Debugging/Tracing a Concurrent Program

Oracle provides a neat way to Log/Trace and debug Concurrent programs. Enable the profile option 'Concurrent: Allow Debugging' preferably at the user level so that you don't end up with many many debug files.

Once the profile is enabled we have the Debug Options button on concurrent program submission window enabled. I am sure many of us never bothered noticing the button before.

While submitting the program click on Debug Options we new page opens with the various options. Enable tracing or FND Log(given the programs uses logging) and a log file is created something like *username_*request_id*.* in your DB udump directory.

AR Transaction Type Extension

I have collated various notes which are helpful when dealing with AR Transaction Type Extension.

This extension is called from PRC: Interface Invoices to Receivables which interfaces PA Draft Invoice to AR (Receivables).

The AR Transaction Type Extension enables you to determine the AR transaction type when you interface invoices to Oracle Receivables.

Oracle Projects provides a template package and procedure that you use as the basis of your AR transaction type extension procedures. The name of the template package is pa_client_extn_inv_transfer. The name of the procedure is get_ar_trx_type. The package file is PAXITRXB.pls

Before we develop the extension we need to define the custom AR Transaction Types. Invoice class and Credit Memo class and we customize the above procedure to select the right transaction type based on custom criteria.

Note: 'Invoice' Type should always have an associated 'Credit Memo' Type,else you might encounter the following error.

Rejection Code = !NO_INV_TYPE! which is 'No Invoice Type found using Invoice Organization'