Oracle Applications

Where to find Libraries : fadolif.pll and FARSV.pll ?

2011-09-21T15:15:00.001-07:00

You might be struggling to find some libraries particularly the ones mentioned.

You can find them on $AU_TOP/plsql . I am not sure why someone will create libraries there.

Oracle Forms : Developing a new form.

2011-09-19T15:09:00.001-07:00

One small tip which might help while developing a new form.

1.Start development from TEMPLATE.fmb from $FND_TOP or $AU_TOP
2.Make sure the window is associated with the canvas and the code to close window is properly initialized or else you might see wierd calendar etc poping on the screens.

The place to check will be app_custom.close_window

Find the Current Tab in a canvas.

2011-09-19T15:07:00.000-07:00

The following piece of code can be used to find the tab in a given canvas.

===

declare
--call the serial capture api.
l_tab_name varchar2(100);
begin

--get the current tab
l_tab_name := GET_CANVAS_PROPERTY('CANVAS_NAME',topmost_tab_page);

if l_tab_name = 'TAB1' then
fnd_message.debug('TAB1');
elsif l_tab_name = 'TAB2' then
fnd_message.debug('TAB2');
else
fnd_message.debug('INVALID TAB');
end if;

exception
when others then
fnd_message.debug('Error in process:'||sqlerrm);
end;

Organization selection in Oracle Applications.

2011-09-15T15:14:00.001-07:00

Today I had to add Organization access to one of the forms. I found that is actually very easy to do.

Step 1: Add 'FND_ORG.CHOOSE_ORG; ' in the WHEN-NEW-FORM-INSTANCE trigger
Step 2: Define 4 parameters in the form; ORG_ID,ORG_CODE,ORG_NAME,CHART_OF_ACCOUNTS_ID

And you are all set, the list of Orgs appearing will be from the Organization Access defined for the current responsibility.

Receiving Transaction Errors (rcv_transactions_interface)

2011-08-30T17:59:00.000-07:00

We had some receiving errors and I learnt an interesting point that the detailed errors can be found in po_interface_errors.

select * from PO_INTERFACE_ERRORS where batch_id
in (select group_id from rcv_transactions_interface)

Oracle Shipping Exceptions.

2011-08-18T18:00:00.000-07:00

There might be scenarios when Pick Release is called you are not sure where to look into. WSH_EXCEPTIONS does the job of tracking the exceptions.

select * from WSH_EXCEPTIONS

Some More Date functions.

2011-08-18T14:40:00.000-07:00

What day is today in SQL?
select to_char(sysdate,'DAY') from dual;

Extending the above to other useful stuff:

First Day of the Month:
select to_char(trunc(sysdate,'MM'),'DAY' ) from dual;
Ex: WEDNESDAY

Last Day of the Month:
select to_char(last_day(sysdate),'DAY') from dual;
Ex: MONDAY

Last Date of the month :
select last_day(sysdate) from dual
Ex:31-Aug-2011

Add time to a given date:
select to_char(trunc(last_day(sysdate))+13/12,'DD-MON-RRRR HH24:MI:SS') from dual

Difference between two dates in hourss/minutes in Oracle.

2010-12-02T07:21:00.000-08:00

We might need to find the difference between two dates columns in hours/minutes in many cases. Like trying to find how long some program ran. One of the easiest way I found is

SELECT to_number( to_char(to_date('1','J') +
(date1 - date2), 'J') - 1) days,
to_char(to_date('00:00:00','HH24:MI:SS') +
(date1 - date2), 'HH24:MI:SS') time
FROM mytable;

Progress Order Workflow Automatically.

2010-12-02T07:10:00.000-08:00

In any implementation using Order Management Order Workflow errors are a common thing. And once you customize the workflow the pain is even more.

Sometimes the workflow gets struck of the errors might be momentary. Something like some service not available at that point of time.

Oracle provides a Automatic Retry program which does the job for us.

Program Name : Retry Activities in Error
Item Type : In most cases this happens to be 'OM Order Line' or else select accordingly.
Mode : Preview/Execute ,select Execute to commit/action the retry.

Get Timestamp value for Date column in SQL Developer.

2010-01-13T06:29:00.000-08:00

Most of the times when we look at Oracle tables from SQL Developer we find that the date column not showing the timestamp. A simple way to get the timestamp is using CAST function.

select cast(last_update_date as timestamp) from table_name.

Also there is a mroe easier way to see the timestamp in the settings of SQL Developer.

Tools -> Preferences -> Database -> NLS Parameters
Change Date Format to DD-MON-RRRR HH24:MI:SS

:)

SQL Loader Failing to load more than 255 characters.

2010-01-07T08:24:00.000-08:00

This was one of my observations.

We had a sample dataload file defined as :

,mesg_type CHAR "ltrim(rtrim(:mesg_type))"
,mesg_priority CHAR "ltrim(rtrim(:mesg_priority))"
,mesg_description CHAR "ltrim(rtrim(:mesg_description))"

The program was failing to load the data when the length of the field is more than 254 characters.

Issue:
NOTE: The default data type in SQL*Loader is CHAR(255). To load character fields longer than 255 characters, code the type and length in your control file. By doing this, Oracle will allocate a big enough buffer to hold the entire column, thus eliminating potential "Field in data file exceeds maximum length" errors. Example:

Fix:
,mesg_description CHAR(4000) "ltrim(rtrim(:mesg_description))"

FRM-40654 Record Has Been Updated. Requery Block To See Change

2009-10-13T01:58:00.000-07:00

One of the root causes which can cause this issue is the trailing spaces in the columns.

my case this was po_vendor_sites_all table.

Those who have Metalink can refer : 429469.1

and for the rest I am copying it here.

-----------
Symptoms

Unable to update fields on vendor sites form due to error:

FRM-40654: Record has been updated. Requery block to see change.

.
Cause
Leading or trailing spaces in various columns on the PO_VENDOR_SITES_ALL table

Leading or Trailing Spaces script available via Note 229407.1 confirmed there were leading or
trailing spaces in several columns in this table.

Solution

The update you need to run is as follows:

Update po_vendor_sites_all
set VENDOR_SITE_CODE = rtrim(VENDOR_SITE_CODE)
where VENDOR_SITE_CODE != rtrim(VENDOR_SITE_CODE);

Update po_vendor_sites_all
set VENDOR_SITE_CODE = ltrim(VENDOR_SITE_CODE)
where VENDOR_SITE_CODE !=ltrim(VENDOR_SITE_CODE);

Commit;

You might only get the ltrim portion updating rows for one column and the rtrim for others or some
of both. Don't worry about the numbers - it's best to run both ltrim and rtrim to be sure you have
caught all the offending spaces.

Then just repeat the script for the offending columns. In your case this would be:

ADDRESS_LINE1
ADDRESS_LINE2
ADDRESS_LINE3
ADDRESS_LINE4
AREA_CODE
PHONE
FAX
VAT_REGISTRATION_NUM
REMITTANCE_EMAIL
----------

An easier way to do the same is by running the DIAGNOSTIC TOOLS which for me is the best way rather than going around with all the columns which is cumbersome.

Steps:

To execute the test, do the following:

1. Login to Oracle E-Business Suite
2. Select the responsibility "Oracle Diagnostics Tool" (see Note 358831.1 for details)
3. Select application "Applications DBA" from the "Application" list of values
4. Click the "Advanced" tab
5. Scroll down to group "Data Collection"
6. Select test name "Trailing and Leading Spaces"

This will list the problematic data in the given Object which is straight forward than the previous approach.

Reference: Metalink : 229407.1

ORACLE FNDLOAD

2009-04-03T09:09:00.000-07:00

FNDLOAD sometimes becomes part and parcel of your usage everyday during implementations and here are the syntax of some of the commands we use generally.

1 -Download Oracle Printer Styles
FNDLOAD apps/apps@ 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcppstl.lct file_name.ldt STYLE PRINTER_STYLE_NAME="printer style name"

2 -Download Oracle Lookups
FNDLOAD apps/apps@ 0 Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct file_name.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME="prod"LOOKUP_TYPE="lookup name"

3 - Descriptive Flexfield with all of specific Contexts
FNDLOAD apps/apps@ 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt DESC_FLEX P_LEVEL=’COL_ALL:REF_ALL:CTX_ONE:SEG_ALL’ APPLICATION_SHORT_NAME="prod" DESCRIPTIVE_FLEXFIELD_NAME="desc flex name" P_CONTEXT_CODE="context name"

4 - Key Flexfield Structures
FNDLOAD apps/apps@ 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt KEY_FLEX P_LEVEL=’COL_ALL:FQL_ALL:SQL_ALL:STR_ONE:WFP_ALL:SHA_ALL:CVR_ALL:SEG_ALL’ APPLICATION_SHORT_NAME="prod" ID_FLEX_CODE="key flex code" P_STRUCTURE_CODE="structure name"

5 - Concurrent Programs
FNDLOAD apps/apps@ 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct file_name.ldt PROGRAM APPLICATION_SHORT_NAME="prod" CONCURRENT_PROGRAM_NAME="concurrent name"

6 - Value Sets
FNDLOAD apps/apps@ 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET FLEX_VALUE_SET_NAME="value set name"

7 - Value Sets with values
FNDLOAD apps/apps@ 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VSET_VALUE FLEX_VALUE ="flexfield segment value"

8 - Profile Options
FNDLOAD apps/apps@ 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct file_name.ldt PROFILE PROFILE_NAME="profile option" APPLICATION_SHORT_NAME="prod"

9 - Request Groups
FNDLOAD apps/apps@ 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct file_name.ldt REQUEST_GROUP REQUEST_GROUP_NAME="request group" APPLICATION_SHORT_NAME="prod"

10 -Download Request Sets (must be done on two stages. The set then the link)
FNDLOAD apps/apps@ 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct file_name.ldt REQ_SETAPPLICATION_SHORT_NAME="prod" REQUEST_SET_NAME="request set"
FNDLOAD apps/apps@ 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct file_name.ldt REQ_SET_LINKSAPPLICATION_SHORT_NAME="prod" REQUEST_SET_NAME="request set"

11 -Download Oracle Custom Responsibilities
FNDLOAD apps/apps@ 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct file_name.ldt FND_RESPONSIBILITY RESP_KEY="responsibility"

12 - Download Oracle Menus
FNDLOAD apps/apps@ 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt MENU MENU_NAME="menu_name"

13 -Download Oracle Messages
FNDLOAD apps/apps@ 0 Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct file_name.ldt FND_NEW_MESSAGES MESSAGE_NAME="message_name" APPLICATION_SHORT_NAME=”prod”

14. Download Oracle Alerts

FNDLOAD app_user_name/apps_password 0 Y DOWNLOAD
$ALR_TOP/patch/115/import/alr.lct .ldt ALR_ALERT
APPLICATION_SHORT_NAME= "prod" ALERT_NAME=Alert name to download

Example

FNDLOAD apps/apps 0 Y DOWNLOAD $ALR_TOP/patch/115/import/alr.lct
PUR16A_ALERT_REQ.ldt ALR_ALERTS APPLICATION_SHORT_NAME=PO
ALERT_NAME=XXC_PO_ITEMS_REVISION_MISMATCH_ALERT_REQ

15. Download Oracle forms Personalization

FNDLOAD / 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct FND_FORM_CUSTOM_RULES form_name=

Example
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct MFG208_PERSONALIZATION.ldt
FND_FORM_CUSTOM_RULES form_name=WIPTXSFM

I use this generally for my reference too ;)

Cheers.

How is add access for a oracle contract created by someone else.

2009-03-27T16:43:00.000-07:00

In general contracts are secured by user,to add access a contract follow the screenshot.

Return Requisition to Preparer.

2009-03-27T16:41:00.000-07:00

Operations that can be done after approving a requistion can be done by:

Purchasing User -> Auto Create -> Find ->Tools.

Check below screenshot for more info.

Approval Management (AME)

2009-03-27T16:17:00.000-07:00

Setup a AME for Oracle Invoice Approval:

1.Query for the particular transaction type. (In this case Payables Invoice Approval)
2.Click on Attributes, to open the attributes screen for that transaction type.

3.Lets create a new attribute using create:

4.Create a new condition based on the attribute by navigating to conditions Tab:

5.Now we have the condition we need to define actions what do you want to do?
Navigate to Approval group tab to define the group to be used in the action:

Dynamic approver:

6.Actions(Navigate to actions type)
we can see that the actions type is associated with the approval group or vice versa.

The Setup part is done now comes the real rules part of all.(linking conditions and actions)
7.
Step 1: enter the name

Step 2: Add your conditions:

Step 3:
Add your actions

Step 4:Review

If there is No Mathcing PO use this approval group for approving the invoice.

End.

Now we are done with the setup of AME.Easy isn't it?

To Get the cost groups and WAC associated with projects.

2009-03-27T16:16:00.000-07:00

1.To get Cost Group with a project:

PJM -> Project Definition ->project Parameters

2.To get the WAC from cost group :

INV -> Setup -> Costs -> Cost Groups-M

Sales Order Flow.

2009-03-27T16:14:00.001-07:00

1>create an order
2>add the line and release the sales order
3>ship confirm using transaction
4>and then order management srs or the trip id
5>see workflow

simple to see hard when trying to do it...doubts do ask me..

I will at some point come up with the screen shots.

Until then Cheers.

PL/SQL

2009-03-27T16:12:00.000-07:00

1.How to use a REF CURSOR?

->define TYPE (TYPE L_REF_CURSOR_TYPE IS REF CURSOR)
->define a variable of that type and open for some statement and done.

2.Standards when defining a concurrent program based on a PL/SQL package.

->The proc to be attached need to have first 2 parameters as OUT named(Errbuf OUT NOCOPY VARCHAR2,retCode OUT NOCOPY VARCHAR2)

3.Calling a CONC program from pl/sql->fnd_request.submit(applname,concname,null,null,FALSE,parameters);

Hello All.

2009-03-27T16:11:00.000-07:00

Hello to all the vistors of my blog.Here I am to put in my learning of Oracle Applications.If you have any doubts or anything you could reach me on mail.

Cheers.