Sorting the Time Card List in OTL

Recently I got a requirement to change the order in which the time cards are displayed in the Time Card list for the employees. The requirement was to show the latest time card on the top. We can achieve this using Self Service Personalization.

Before we start make sure that the following profile options are set. You can set this at Responsibility Level for development purpose.

Personalize Self-Service Defn
Set the value to Yes to allow ‘Personalize’ link to appear at the top of the page.

FND : Personalization Region Link Enabled
Set the value to Yes to display all the personalization links above each OAF page region.

Disable Self-Service Personal
Setting the value to Yes will disable all OAF personalization on all pages at all levels (Function, Site, Responsibility, User).

These profile options allow us to Personalize the web page.

Follow the below screen shots to see how we can achieve this change using Personalization.

Navigation:-  OTL Responsibility > Time Entry

Click on “Personalize Recent Timecard List” link as shown in the below image:

Once you click the link, you will get the below page. Click on the region indicated. Here you select the region / item that we are going to personalize.  In our scenario, we have to personalize the time card list.

Once you click the Personalize link for the item, it will show you various options that can be personalized. You should get the below page now.

Click on the Edit link against the Query option at Responsibility Level. See the above image to see where exactly you need to click. In the next page, you will be able to see the options for “Sorting”.

You have an option of sorting the time card list. For our requirement I have selected two options. You can select all the three options or only one of them based on your requirements.

Once you select the sort options, click “Apply”. Bingo. We are done.

Note:- There is NO NEED to bounce the server for this change.

Hope this helped some of you out there.

Starting with Oracle APEX?

If you are starting with Oracle APEX development and would like to know the installation procedures, please refer to the following links. 

http://asksipho.blogspot.com/2013/01/installing-oracle-xe-database.html

http://asksipho.blogspot.com/2013/01/oracle-xe-installation-on-windows-8.html

The first link explains how to install the Oracle XE Database on Windows 8 OS and the second one talks about Oracle APEX. I found these two links helpful.

I will be posting more on APEX development in the future posts.

OTL API Error

I was trying to update a previously submitted time card using the OTL API (hxc_timestore_deposit.execute_deposit_process). I was getting a very strange error as described below:

ORA-20020: Pa_Otc_Api :::  ->Validate_Otc_Data->Validate_Process->DetermineProcessingFlags :: Only Ovn has changed.  Update the orig_transaction_reference of the ei. : ORA-01403: no data found

I searched a lot and did not find any help in Google or Metalink. Finally I decided to debug the API myself. After spending quite a few hours on the daunting task, I found out the issue.

The API hxc_timestore_deposit.execute_deposit_process, inturn calls another API and a function – Pa_Otc_Api.determineprocessflags(). In this function there is a select statement that retrieves the data from pa_expenditure_items table.

select project_id , task_id, expenditure_type
into l_Proj_Id , l_task_id , l_Exp_Type
from Pa_Expenditure_Items
where RowId = l_RowId;

The above select statement was not retrieving any records and that is the reason why the API was failing. And now the interesting facts:

1. The time card data was not transferred to Oracle Projects and there were no records in pa_expenditure_items table. 

2. Since you are calling the API, please make sure that you set org context as API is using MO Tables

Hope this helps someone who is working on the API. I have used the below APIs in the order given below to update the time card hours:

• hxc_timestore_deposit.update_building_block
• hxc_timestore_deposit.execute_deposit_process

Useful Oracle Apps Queries

Below are a list of SQL Queries that will be useful during your day to day activities.

1. Concurrent Programs Assigned to a Request Group

SELECT fcpv.concurrent_program_name SHORT_NAME
,fcpv.user_concurrent_program_name CONC_PROG_NAME
,frg.request_group_name REQ_GROUP_NAME
FROM fnd_concurrent_programs_vl fcpv
,fnd_request_groups frg
,fnd_request_group_units frgu
WHERE fcpv.concurrent_program_id = frgu.request_unit_id
AND fcpv.enabled_flag = ‘Y’
AND frgu.request_group_id = frg.request_group_id
AND frg.request_group_name = ‘&request_group_name’;

2. Check if the concurrent program is assigned to a given responsibility

SELECT fcpv.concurrent_program_name SHORT_NAME
,fcpv.user_concurrent_program_name CONC_PROG_NAME
,frg.request_group_name REQ_GROUP_NAME
,frv.responsibility_name
FROM fnd_concurrent_programs_vl fcpv
,fnd_request_groups frg
,fnd_request_group_units frgu
,fnd_responsibility_vl frv
WHERE fcpv.concurrent_program_id = frgu.request_unit_id
AND fcpv.enabled_flag = ‘Y’
AND frgu.request_group_id = frg.request_group_id
AND frg.request_group_id = frv.request_group_id
AND frv.responsibility_name = ‘&resp_name’
AND fcpv.user_concurrent_program_name = ‘&con_prg_name’;

3. List of Menus Excluded from a given Responsibility

SELECT frv.responsibility_name
,fmv.user_menu_name
FROM fnd_resp_functions frf
,fnd_menus_vl fmv
,fnd_responsibility_vl frv
WHERE frf.rule_type =’M’
AND frf.action_id = fmv.menu_id
AND frf.responsibility_id = frv.responsibility_id
AND frv.responsibility_name = ‘&resp_name’;

4. List of Functions Excluded from a given responsibility

SELECT frv.responsibility_name
,fffv.user_function_name
FROM fnd_resp_functions frf
,fnd_form_functions_vl fffv
,fnd_responsibility_vl frv
WHERE frf.rule_type =’F’
AND frf.action_id = fffv.function_id
AND frf.responsibility_id = frv.responsibility_id
AND frv.responsibility_name = ‘&resp_name’;

5. Concurrent Program Name, Responsibility Name and User Name for a concurrent request id

SELECT fcr.request_id
,frv.responsibility_name
,fcpv.concurrent_program_name PROG_SHORT_NAME
,fcpv.user_concurrent_program_name CON_PROG_NAME
,fu.user_name REQUESTED_BY
FROM fnd_concurrent_requests fcr
,fnd_concurrent_programs_vl fcpv
,fnd_user fu
,fnd_responsibility_vl frv
WHERE fcpv.concurrent_program_id = fcr.concurrent_program_id
AND fu.user_id = fcr.requested_by
AND frv.responsibility_id = fcr.responsibility_id
AND fcr.request_id = &req_id;

Journal Approval Worfklow Error

I want to share a very strange error that I had encountered while working on the Journal Approval Process.

The process :- Enter a new journal and save it. After it is saved, the “Approve” button is enabled. When I clicked on the approve button, I was getting the below error:

“APPROVE_BATCH.start_approval raised un handled exception”.
When you go to details you get the “Exact fetch returned more than one row” error.

Possible Suspects : I did the below primary checks:
Form compilation Issues – Checked whether the form is compiled correctly or not. For that i followed the below mentioned steps:

1. cd $AU_TOP/forms/US
2. strings -a GLXJEENT.fmb | grep ‘$Header’
3. Saved the fmb version
4. cd $GL_TOP/forms/US
5. strings -a GLXJEENT.fmx | grep ‘$Header’
6. Saved the fmb version
7. Compared the fmb version in Step 3 and Step 6. Well there were no differences.

So it was not because of any form compilation issues.

Next step waa to check if the DB version change had caused any issues. At times if the DB is upgraded then calls to DB from forms can give problems. So I copied the code snippet that calls the workflow from the form and ran it from sql plus. Well, I was getting the below error:

“ORA-01422: Exact fetch returns more than one row”. That is the same error that i get from the form. So I concluded that it is not a DB Upgrade Issue.

Now it was the hard part. To go through the standard workflow code. And guess what I was able to debug the issue by going through the standard Journal Approval WF package. I executed the code snippets one by one to see where I was getting the error. Finally, I got error for the below code snippet:

SELECT WA.ITEM_TYPE, WA.NAME, WA.VERSION, WA.TYPE, WA.RERUN,
WA.EXPAND_ROLE, WA.COST, WA.ERROR_ITEM_TYPE,
WA.ERROR_PROCESS, WA.FUNCTION, WA.FUNCTION_TYPE, WA.EVENT_NAME,
WA.MESSAGE, WA.BEGIN_DATE, WA.END_DATE, WA.DIRECTION
INTO   WF_CACHE.Activities(waIND).ITEM_TYPE,
WF_CACHE.Activities(waIND).NAME,
WF_CACHE.Activities(waIND).VERSION,
WF_CACHE.Activities(waIND).TYPE,
WF_CACHE.Activities(waIND).RERUN,
WF_CACHE.Activities(waIND).EXPAND_ROLE,
WF_CACHE.Activities(waIND).COST,
WF_CACHE.Activities(waIND).ERROR_ITEM_TYPE,
WF_CACHE.Activities(waIND).ERROR_PROCESS,
WF_CACHE.Activities(waIND).FUNCTION,
WF_CACHE.Activities(waIND).FUNCTION_TYPE,
WF_CACHE.Activities(waIND).EVENT_NAME,
WF_CACHE.Activities(waIND).MESSAGE,
WF_CACHE.Activities(waIND).BEGIN_DATE,
WF_CACHE.Activities(waIND).END_DATE,
WF_CACHE.Activities(waIND).DIRECTION
FROM WF_ACTIVITIES WA
WHERE WA.ITEM_TYPE = itemtype /*G;BATCH*/
AND  WA.NAME = root /*GL_JE_APPROVAL_PROCESS*/
AND  SYSDATE >= WA.BEGIN_DATE
AND  SYSDATE < nvl(WA.END_DATE, SYSDATE +1)

The above code checks active records (for an item type and name) in the WF_ACTIVITIES table for the date range. In my case, i was returning mutliple rows. So i figured out that there were two records with end_date as NULL for item_type = ‘GLBATCH’ and name = ‘GEL_JE_APPROVAL_PROCESS’. Ideally there should be only one active record for a given date range. Since the end_date was NULL for two records, it was retuning me multiple rows for the date range.

Fix – I populated the end_date column for one of the records and then tried the workflow and it worked without any issues!!!!

Root Cause – There was a Data Migration activity that had happened from an old instance to the new instance. They wanted to retain all the historic WF data in the new instance. When they copied the WF data, they copied even the WF definitions as well. The new instance had active records in WF_ACTIVITIES table for the item_type and name. The DM copied over more active records from the old instance to the new instance. And because of which we have multiple active records in the new instance for the item_type and name.

Things to remember – We should not copy the WF definitions from an old instance to a new instance through a Data Migration activity.

Hope this was informative. Please send your comments.

XML Report from XML Data Template

I used to wonder on how i can develop a xml publisher report without using rdf or sql or pl/sql concurrent program. The traditional way is to

1. Create a RDF or SQL or PL/SQL procedure
2. Register the RDF or SQL or PL/SQL report as a concurrent program and set the output to XML
3. Run the above report and get the output and save it as a XML file
4. Use the XML data in building the Template using Word.

This seemed to be a little cumbersome for me. So I was searching for alternatives. Then i stumbled upon the following scenario. And I hope this helps all of  you. This is part one of a series of blogs. Hope you enjoy this and come up with questions and suggestions.

The XML data template is an XML document that consists of four basic sections:

1. define parameters
2. define triggers
3. define data query
4. define data structure.

This structure is shown in the following graphic:

Step by Step Guide to create a sample XML Data Template:

Decide on the SQL Query

In the example, we are trying to get the following data from the AP_INVOICES_ALL Table

1. INVOICE_NUM
2. INVOICE_CURRENCY_CODE
3. INVOICE_AMOUNT

The query for the same will be like below:

SELECT  invoice_num

,invoice_currency_code

,invoice_amount

FROM    ap_invoices_all

Decide on the parameters

We will be using two parameters to limit the data that is being retrieved:

1. ORG_ID
2. VENDOR_ID

The resulting query will be like:

SELECT  invoice_num

,invoice_currency_code

,invoice_amount

FROM    ap_invoices_all

WHERE org_id = :p_OrgId

AND       vendor_id = :p_VendorId

That’s all we need to construct the XML Data Template.

<?xml version=”1.0″ encoding=”WINDOWS-1252″ ?>

<dataTemplate description=”Invoice Data” Version=”1.0″>

<parameters>

<parameter name=”p_OrgId” dataType=”number” />

<parameter name=”p_VendorId” dataType=”number” />

</parameters>

<dataQuery>

<sqlStatement name=”Q1″>

<![CDATA[

SELECT invoice_num,invoice_currency_code,invoice_amount

FROM ap_invoices_all

WHERE org_id = :p_OrgId

AND vendor_id = :p_VendorId

]]>

</sqlStatement>

</dataQuery>

<dataStructure>

<group name=”G_INV” source=”Q1″>

<element name=”INV_NUMBER” value=”invoice_num” />

<element value=”invoice_currency_code” />

<element name=”AMOUNT” value=”invoice_amount”/>

</group>

</dataStructure>

</dataTemplate>

If you see the above XML Data Template, we have specified the parameters that are being used and also the SQL Query that will be fetching the data. The SQL Query is using the parameters that are being defined under the Parameters Section.

Save the same as “.xml” file in your local system.

Open Word. Go to Add-Ins. You will be able to see the BI Publisher Menu. Under the Menu, Select Data > Load XML Schema. Select the xml file that we just created.

If the data is loaded successfully, you will get the below message:

Click OK and to Continue

Select Insert > Table/Form

You will get the below screen:

Drag and Drop G Inv element from Data Source to the Template Region as shown below

Select Drop All Nodes from the choices. We will be getting the below screen:

Press OK. We will get the below template in the Word Document. Save the same as XXINV_DETAILS.rtf.

Inv Number	Currency	Amount
F INV_NUMBER	CURRENCY	AMOUNT E

Go to XML Publisher Administrator responsibility and create a new Data Definition. Give the following for the fields:

Name – XXINV_DETAILS

Code – XXINV_DETAILS

Application – Provisioning (or your custom application)

Start Date – Automatically Populated

Press “Add File” next to Data Template. Browse and upload the XML Data Template that we have created here.

Go to Data Templates and create a new Data Template. Give the following details:

Select the Data Definition that we had created prior to this.

Select Language as US English and upload the RTF File that we have created (XXINV_DETAILS.rtf).

Navigate to System Administrator > Concurrent > Program > Define.

Create a new Concurrent Program and give the following details.

The Executable should always be XDODTEXE.

Click on Parameters and give the following details:

Assign the program to the request group and run the program.

You can download the sample output file HERE.

I will be including few complex reports in the next series of posts. Give me your suggestions.

rAj