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'

Oracle Change/Modify PO Print Logo

Changing the Logo on PO Print is a little tricky. I say tricky purely because even though the PO Print program is a XML Publisher based report the layout part of the report is XSL-FO type. So all the details are in XML.

--------------------------------------------------------
Note: For doing this we need to have XML Publisher template builder installed. Pls follow the below instructions

Download the patch from Metalink. Latest version as of today is Patch 12395372: UPDATE FOR BI PUBLISHER DESKTOP 10.1.3.2.1 (5.6.3) size = 95.9 MB Product=BI Publisher (formerly XML Publisher)

Unzip this patch and then run BIPublisherDesktop.exe to install in a directory
--------------------------------------------------------
So lets get to the task.

Step 1: Create an RTF Document and embed the image into the document. I create a MSWord document and 'Save As' RTF document. And in Word (Menu)Insert -> Picture -> From File -> Select the image.

Step 2: Update the size of image to approx the size it appears in the document.

Step 3: Once you have template builder installed, the plug-in adds new menu options in Word. Tools -> Export -> XSL-FO Style Sheet. Screenshot attached

Step 4: An XML file is generated, Open the file and search for tag 'fo:instream-foreign-object' tag which is of our interest.

Step 5: Copy the tag contents from the beginning to the end of the tag 'fo:instream-foreign-object'. So this is our image xdofo:uid

Step 6: Open XML Publisher, Search for Template 'Standard Purchase Order Stylesheet', Duplicate the template entering required details. Download PO_STANDARD_XSLFO.xsl and rename to XXC_PO_STANDARD_XSLFO.xsl

Step 7: Open XXC_PO_STANDARD_XSLFO.xsl and search for 'fo:inline' which contains the Logo info. Uncomment the inline tags and replace the 'fo:external-graphic content' with the tags contents we copied in step 5.

The file should look like this.

Step 8: Save XXC_PO_STANDARD_XSLFO.xsl and attach the file to the template we created in Step 6. Also update the xsl:style sheet definition(at the beginning of the document) to refer to xdo namespace as follows.

'xsl:stylesheet version="1.0" xmlns:fo="http://www.w3.org/1999/XSL/Format" xmlns:ora="http://www.oracle.com/XSL/Transform/java/" xmlns:xdofo="http://xmlns.oracle.com/oxp/fo/extensions" xmlns:xdoxliff="urn:oasis:names:tc:xliff:document:1.1" xmlns:xdoxslt="http://www.oracle.com/XSL/Transform/java/oracle.apps.xdo.template.rtf.XSLTFunctions" xmlns:xlink="http://www.w3.org/1999/xlink" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"'

Step 9: Attach the new template to PO Document Types. (Document Type Layout), so the new template will be picked for the PO document creation

Step 10: For this article purpose lets simply the process and see if we can get the PO Document with the new Logo, by using the preview button in XML Publisher(for the new template we created in Step 6).Here we have it.

Leave me a comment if you have any questions.

Oracle Open Multiple Forms

In Oracle we have an option under Tools -> Close Other Forms which when unchecked allows us to open multiple forms.

Sometimes this option is not update-able. This is controlled by a function.

If it is not in a certain responsibility we need to add an menu exclusion in the resp definition.

Add Function -> Navigator: Disable Multiform

Oracle Database Version

Ever wondered what Oracle database version you are running on, use the following query

Oracle Version :
-----
select * from v$version
where banner like 'Oracle%';

v$version also have versions for PL/SQL version and TNS version etc.

Oracle Workflow Miscellaneous

Some miscellaneous workflow things I encountered.

Workflow can be started by two different ways
1. Itemtype (launch workflow)
2. wf_event.RAISE

So to search for the workflow in the code you have to keep in mind the above.

Deferred Events:

To find if you event or workflow is struck in the deferred Q, use the following query.
SELECT a.user_data.event_name event, a.msg_state status,a.user_data.send_date
FROM applsys.aq$wf_deferred a
WHERE a.user_data.event_name like 'oracle%write%'

Unix Commands.

Some commands which I came across during my experience.

To Search for some piece of code or message text in Oracle_TOP or any directory in UNIX

find . | xargs grep -s 'XXC_MESSAGE'

Unix Command History: /bin/bash

Zip/Unzip in UNIX : zipinfo datazip.zip

Using TAR
---------
To ZIP : tar czf itemdata1.tar.gz datafiles
to Unzip : tar xvzf itemdata.tar.gz

Filesize in MB: ls -lh (in MB)

Count files/entries in a directory: ls -1 | wc -l

Oracle Set application Context

Below are tiny SQL's which can be used to set the Apps context.

BEGIN
dbms_application_info.set_client_info('103');
END;

BEGIN
FND_GLOBAL.Apps_Initialize (user_id => 5992 ,resp_id => 51498 ,resp_appl_id => 660 );
END;

In SQL developer sometimes the views don't fetch any data. Set the NLS Lang using the following SQL.

alter session set nls_language = 'AMERICAN';

Debug Oracle Applications.

Below is the piece of code I have been using for quite a while for creating a good debug process in Oracle Applications development environment. It is good enough to be in PROD if we can convince the team too.


Steps:
1.Create a sequence
2.Create a debug table
3.Create a procedure
---
CREATE SEQUENCE APPS.XXC_SEQ
  START WITH 1
  MAXVALUE 99999999
  MINVALUE 1
  NOCYCLE
  CACHE 2
  NOORDER;
/

CREATE TABLE APPS.XXC_DEBUG_TBL(
  SEQ    NUMBER,  MESG   VARCHAR2(1000 BYTE),  DTIME  DATE)

/

CREATE OR REPLACE procedure APPS.XXC_debug(mesg1 in XXC_debug_tbl.mesg%type)
is
    PRAGMA AUTONOMOUS_TRANSACTION;
begin

    insert into XXC_debug_tbl(Seq,mesg,dtime)
    values(XXC_seq.nextval,mesg1,sysdate);
    commit;
      
end;



To call the procedure : xxc_debug('Test message')

And all debug messages are available in xxc_debug_tbl

Oracle Inventory - Get Item Cost

Below is the Oracle Standard API to fetch the Item cost.
Having a look at API We can see that the derivation is in various stages. Based on the Cost Method.

Standard cost method id is 1
Average cost method id is 2

-----
If the costing method is 1 the costs come from cst_item_costs
If not the costs come from cst_quantity_layers which will be based on the cost group for the item.

Pls download the attached code for more info.
Download File

PA Patchset level.

To find the current PA Patch set level you are on use the attached script.
Download File

Oracle SQL - GREATEST and LEAST

To select max from 2 columns
select greatest(4,2) from dual

To select min from 2 columns
select least(1,3) from dual

Search for Quote character in SQL

Ever wanted to find strings which contain Quote character. Using CHR(39) is the way.

Ex:
select table_name||CHR(39) from all_tables where table_name like '%'||CHR(39)||'%' and rownum < 5;

I am here trying to find table names which contain a quote char.

Fetch Environment variables in PL/SQL

How to get the value of environment variables in PL/SQL code
-------------------------------------------------------------
1.For eBusiness Suite, you can also look at fnd_env_context table in your pl/sql code to get the path to the custom top variables as well.


2.A well coded program avoids hard-coding of paths and uses variables. If you want to get the value of unix environment variables in your programs, you can use the DBMS_SYSTEM.GET_ENV procedure. For applying it in E-Business Suite, you would need to make sure that the variables are defined in RDBMS $ORACLE_HOME/custom$CONTEXT_NAME.env

For example, I defined environment variable CUSTOM_TOP in a new file $ORACLE_HOME/custom$CONTEXT_NAME.env. This file is called automatically by your database environment file $CONTEXT_NAME.env.

To test, whether the value appears simply do this in an sql session:
SQL> set autoprint on
SQL> var CUSTOM_TOP varchar2(255)
SQL> exec dbms_system.get_env('CUSTOM_TOP',:CUSTOM_TOP);

PL/SQL procedure successfully completed.

XML Publisher/Template Builder compile error

Symptoms:
When building a template or loading an BI Publisher, (XML Publisher), sample file, the system throws error:

"Compile error in hidden module: Module_starter"
Cause:
This error is due to the installation of the Microsoft Security Update KB936021. Solution

To workaround this issue, follow these steps:

1. Go to the Start Menu on the affected machine: Program Files > Oracle> XML Publisher Desktop > Template Builder for Word -Bin.
2. Double click on the file "ChangeUILang.exe".
3. Select the appropriate language and click ok. This will register the User Interface language.
4. Test.
5. Migrate to other machines as needed.

Enable Help -> Diagnostics in Oracle Applications

We just need to set a profile to enable Help - Diagnostics in Oracle Applications. From this we can Examine forms value, personalizations etc.Pls see the below screenshot.

Set the following profile options.
1. Hide Diagnostics menu entry - No
2. FND: Diagnostics - Yes
3. Utilities:Diagnostics - Yes