Search This Blog

Wednesday, December 31, 2008

Happy New Year 2009

Wish you all a very happy New Year 2009. May all the wishes and dreams come true.

Wednesday, December 24, 2008

Enhancing 11i/12 Homepage Menu via Firefox and Greasemonkey

Source: Gareth Robert Blog
Here is something I found very interesting and useful and thought of sharing it with others.

In oracle E-Business suite the Oracle homepage menus is not in an organized manner and hence many times we have to make use of favourite option and for easy access save frequently used ones there.

By Default the home page is displayed as


After the enhancement the menu will be displayed as


Now lets discuss how this can be achieved.
Following things should be installed
1) Firefox
2) GreaseMonkey (Firefox Addon)
3) Install the Script

Once the Add-On is installed a Monkey icon will be displayed in status bar on the right side of firefox. Right click on that and click on Manage User Scripts and add the applications URL.

Many thanks to the Author for sharing this with us.

Wish you all a Merry Christmas and Happy new year

Monday, December 8, 2008

List of Tables for Forms Personalization

Below is the list of tables that are populated when any Forms Personalization is done


FND_FORM_CUSTOM_RULES
FND_FORM_CUSTOM_SCOPES
FND_FORM_CUSTOM_ACTIONS
FND_FORM_CUSTOM_PARAMS
FND_FORM_CUSTOM_PROP_VALUES
FND_FORM_CUSTOM_PROP_LIST


Click here For FNDLOAD on forms personalization

Saturday, November 22, 2008

Convert Date into Week Rage

Below is an example of how to display dates into week Range in Oracle
For E.g. 22-Nov-08 is in the date range 17-Nov-08 to 23-Nov-08

Firstly lets see how to get week of the year. Following query can be used to get this

 SELECT to_char(sysdate,'WW') FROM Dual; 


Now lets get the week range

SELECT TO_CHAR (TRUNC (SYSDATE, 'IYYY') + ((TO_CHAR (SYSDATE, 'WW') - 1) * 7), 'DD-MON-RR')
|| ' to '
|| TO_CHAR (TRUNC (SYSDATE, 'IYYY') + ((TO_CHAR (SYSDATE, 'WW')) * 7)-1, 'DD-MON-RR')
FROM Dual;

The output of above query for date 29-Nov-2008 is 24-NOV-08 to 30-NOV-08

Wednesday, November 12, 2008

API to Create Sales Order(SO) Reservations

Below is the code that can be used to create SO reservations


DECLARE
p_rsv inv_reservation_global.mtl_reservation_rec_type;
p_dummy_sn inv_reservation_global.serial_number_tbl_type;
x_msg_count NUMBER;
x_msg_data VARCHAR2(240);
x_rsv_id NUMBER;
x_dummy_sn inv_reservation_global.serial_number_tbl_type;
x_status VARCHAR2(1);
x_qty NUMBER;
BEGIN
-- fnd_global.APPS_Initialize(28270,53073,660);
dbms_application_info.set_client_info(5283);
--p_user_id, p_resp_id, p_resp_appl_id
--p_rsv.reservation_id := NULL; -- cannot know
p_rsv.requirement_date := Sysdate+2;
p_rsv.organization_id := 5343; --mtl_parameters.organization id
p_rsv.inventory_item_id := 949729;--mtl_system_items.Inventory_item_id;
p_rsv.demand_source_type_id := inv_reservation_global.g_source_type_oe; -- which is 2
p_rsv.demand_source_name := NULL;
p_rsv.demand_source_header_id := 1334166 ; --mtl_sales_orders.sales_order_id
p_rsv.demand_source_line_id := 4912468 ; -- oe_order_lines.line_id
p_rsv.primary_uom_code := 'EA';
p_rsv.primary_uom_id := NULL;
p_rsv.reservation_uom_code := 'EA';
p_rsv.reservation_uom_id := NULL;
p_rsv.reservation_quantity := 10;
p_rsv.primary_reservation_quantity := 10;
p_rsv.supply_source_type_id := inv_reservation_global.g_source_type_inv;

inv_reservation_pub.create_reservation
(
p_api_version_number => 1.0
, x_return_status => x_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_rsv_rec => p_rsv
, p_serial_number => p_dummy_sn
, x_serial_number => x_dummy_sn
, x_quantity_reserved => x_qty
, x_reservation_id => x_rsv_id
);
dbms_output.put_line('Return status = '||x_status);
dbms_output.put_line('msg count = '||to_char(x_msg_count));
dbms_output.put_line('msg data = '||x_msg_data);
dbms_output.put_line('Quantity reserved = '||to_char(x_qty));
dbms_output.put_line('Reservation id = '||to_char(x_rsv_id));
IF x_msg_count >=1 THEN
FOR I IN 1..x_msg_count
LOOP
dbms_output.put_line(I||'. '||SUBSTR(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ),1, 255));
fnd_file.put_line(fnd_file.log,I||'. '||SUBSTR(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ),1, 255));
END LOOP;

END IF;
COMMIT;
END;
/

Similary we have update_reservations, relieve_reservations, delete_reservations API to respectively update, relieve or delete reservations.

Monday, October 13, 2008

Store SQLPLUS output to a variable in Unix Shell Script

In response to one of the question asked, here is the solution of how to call sqlplus and store the output in a variable.
In the script below I am connecting to sqlplus through unix and querying fnd_lookup_values table and storing the output of query in a unix variable. This variable can then be later referred in the code using $.


#Suresh Vaishya:The script connects to sqlplus and stores output of a query into a unix variable.
#The output of query is seperated by '~' to identify different record values.

login='apps/apps'
code=`
sqlplus -s $login <set heading off
set feedback off
SELECT lookup_code||'~' FROM fnd_lookup_values WHERE lookup_type ='AGREEMENT LINES' and enabled_flag = 'Y' and Nvl(end_Date_ac
tive,sysdate+1) > sysdate and language='US';
exit
EOF`
echo 'Output of SQL Query is '
echo $code
echo 'End of Script'

The script output is

Output of SQL Query is
BLANKET~ ITEM~ MAIN~ NOTES~ PRICE~ QUOTATION~
End of Script


Related Post
Calling SQLPLUS from unix

Monday, October 6, 2008

API to create Item Category

The item categories can be created using item import when Items are created.
They can also be created using APIs discussed below
Following are the steps to Check and/or Create Item Categories
1) Create Category Segment combinations
2) If the enforce list is checked for category then insert category combinations in the enforce list
3) Create/Update/Delete Category Assignments on Item

In Detail
Create Category Segment combinations
Following APIs can be used to create/update/delete category combinations.


INV_ITEM_CATEGORY_PUB.CREATE_CATEGORY(
P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
X_RETURN_STATUS OUT VARCHAR2,
X_ERRORCODE OUT NUMBER,
X_MSG_COUNT OUT NUMBER,
X_MSG_DATA OUT VARCHAR2,
P_CATEGORY_REC IN INV_ITEM_CATEGORY_PUB.CATEGORY_REC_TYPE,
X_CATEGORY_ID OUT NUMBER
)

INV_ITEM_CATEGORY_PUB.UPDATE_CATEGORY(
P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
X_RETURN_STATUS OUT VARCHAR2,
X_ERRORCODE OUT NUMBER,
X_MSG_COUNT OUT NUMBER,
X_MSG_DATA OUT VARCHAR2,
P_CATEGORY_REC IN INV_ITEM_CATEGORY_PUB.CATEGORY_REC_TYPE
)

INV_ITEM_CATEGORY_PUB.DELETE_CATEGORY(
P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
X_RETURN_STATUS OUT VARCHAR2,
X_ERRORCODE OUT NUMBER,
X_MSG_COUNT OUT NUMBER,
X_MSG_DATA OUT VARCHAR2,
P_CATEGORY_ID IN NUMBER
)


Use following API for assigning a category to a category set. A category will be available in the list of valid categoies for a category set only if it is assigned to the category set. This is a required step if for categories enforce list is checked on.

INV_ITEM_CATEGORY_PUB.CREATE_VALID_CATEGORY(
P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
P_CATEGORY_SET_ID IN NUMBER,
P_CATEGORY_ID IN NUMBER,
P_PARENT_CATEGORY_ID IN NUMBER,
X_RETURN_STATUS OUT VARCHAR2,
X_ERRORCODE OUT NUMBER,
X_MSG_COUNT OUT NUMBER,
X_MSG_DATA OUT VARCHAR2
)


Following APIs can be used to create/update/delete Item category assignments.

INV_ITEM_CATEGORY_PUB.CREATE_CATEGORY_ASSIGNMENT(
P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
X_RETURN_STATUS OUT VARCHAR2,
X_ERRORCODE OUT NUMBER,
X_MSG_COUNT OUT NUMBER,
X_MSG_DATA OUT VARCHAR2,
P_CATEGORY_ID IN NUMBER,
P_CATEGORY_SET_ID IN NUMBER,
P_INVENTORY_ITEM_ID IN NUMBER,
P_ORGANIZATION_ID IN NUMBER
)

INV_ITEM_CATEGORY_PUB.UPDATE_CATEGORY_DESCRIPTION(
P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
X_RETURN_STATUS OUT VARCHAR2,
X_ERRORCODE OUT NUMBER,
X_MSG_COUNT OUT NUMBER,
X_MSG_DATA OUT VARCHAR2,
P_CATEGORY_ID IN NUMBER,
P_DESCRIPTION IN VARCHAR2
)

INV_ITEM_CATEGORY_PUB.DELETE_CATEGORY_ASSIGNMENT(
P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
X_RETURN_STATUS OUT VARCHAR2,
X_ERRORCODE OUT NUMBER,
X_MSG_COUNT OUT NUMBER,
X_MSG_DATA OUT VARCHAR2,
P_CATEGORY_ID IN NUMBER,
P_CATEGORY_SET_ID IN NUMBER,
P_INVENTORY_ITEM_ID IN NUMBER,
P_ORGANIZATION_ID IN NUMBER
)

Thursday, October 2, 2008

Item Import (Item Conversion)

The Item import Interface(IOI) reads data from following tables for importing items and item details. The MTL_SYSTEMS_ITEM_INTERFACE table is used for new item numbers
and all item attributes. This is the main item interface table, and can be
the only table used to import items. MTL_ITEM_REVISIONS_INTERFACE is used if Item revisions history is also loaded with items. Item categories can be imported using MTL_ITEM_CATEGORIES_INTERFACE.
The import error can be tracked using MTL_INTERFACE_ERRORS table. The transaction_id and request_id populated by the import program can be used to link interface table and error table.

Required columns in MTL_SYSTEM_ITEMS_INTERFACE
PROCESS_FLAG = 1 (The column is used to identify status of record)
TRANSACTION_TYPE = 'CREATE' or 'UPDATE'
SET_PROCESS_ID = any numeric value (This is not a required column but for performance it is advised to use this column and then run import program for the value entered here)
ORGANIZATION_ID/ORGANIZATION_CODE = Master/Child Org.
DESCRIPTION = 'Description of the item'
ITEM_NUMBER and/or SEGMENT(n) = If using item_number then each segment value should be entered concatenated by segment seperator. If Item revisions history is also being loaded then Item_number should be populated.
LIST_PRICE_PER_UNIT = If material cost is to be populated for an item along with item import .

Required columns in MTL_ITEM_REVISIONS_INTERFACE table. The table is only used if Item revision is to be loaded in the same run with IOI. If this table is not used then items are created with the default revision setup for an organization.
PROCESS_FLAG = 1
TRANSACTION_TYPE = 'CREATE'
SET_PROCESS_ID = any numeric value(Should be same for the item in MTL_SYSTEM_ITEMS_INTERFACE table)
ORGANIZATION_ID/ORGANIZATION_CODE = Master/Child Org.
REVISION
EFFECTIVITY_DATE
IMPLEMENTATION_DATE
ITEM_NUMBER = Same as item_number in mtl_system_items_interface table.
Each row in the mtl_item_revisions_interface table must have the REVISION and
EFFECTIVITY_DATE in alphabetical (ASCII sort) and chronological order.

Required columns for MTL_ITEM_CATEGORIES_INTERFACE table.
TRANSACTION_TYPE = 'CREATE'
SET_PROCESS_ID = any numeric value(Should be same for the item in MTL_SYSTEM_ITEMS_INTERFACE table)
ORGANIZATION_ID/ORGANIZATION_CODE = Master/Child Org
ITEM_NUMBER/INVENTORY_ITEM_ID or both
CATEGORY_SET_NAME or CATEGORY_SET_NAME or both
CATEGORY_ID or CATEGORY_NAME or both


For performance purpose, it is advised to batch set of records using set_process_id column and then run import program for that set_process_id. The item import(IOI) program can be run in parallel if seperate set_process_ids are passed while submitting. The IOI automatically separates Master records from Child, and
processes Master records first. However, as one IOI process is not aware of
other IOI processes running in parallel, do not split a given item's separate
Organization records into two different SET_PROCESS_IDs that are being run in
parallel.

Item import program can be run in 2 modes INSERT & UPDATE.
The method to update Item attribute columns to NULL is to use the following values:
· for Numeric fields: insert -999999
· for Character fields: insert '!'

Tuesday, September 23, 2008

FNDLOAD using Shell Script

Whenever we use FNDLOAD at times it becomes difficult to remember the .lct name.
Here I have tried to create a shell script which can be used to DOWNLOAD or UPLOAD concurrent program definitions using FNDLOAD. The shell script prompts for necessary parameter needed to perform specific action.

Shell Script Code
-----------------
Download concurrent program definition using FNDLOAD. Create a shell script FNDCPDOWN using following code.


echo "Enter APPS Password: "
stty -echo #Turns echo off
read appspwd
stty echo #Turns echo on

echo "Enter .ldt Name: "
read ldtname

echo "Enter Application Short Name: "
read applname

echo "Enter Concurrent Program Short Name: "
read cpname

FNDLOAD apps/$appspwd O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct $ldtname PROGRAM APPLICATION_SHORT_NAME="$applname"
CONCURRENT_PROGRAM_NAME="$cpname"

echo "LDT File $ldtname created"


Upload Concurrent Program using FNDLOAD. Create a shell script FNDCPUP using following code.

echo "Enter APPS Password"
stty -echo #Turns echo off
read appspwd
stty echo #Turns echo on
echo 'Enter .ldt file name to upload'
read ldt_name

FNDLOAD apps/$appspwd O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct $ldt_name


Test the above created shell script.
For downloading the script will prompt for APPS password, .ldt file name, application name and Concurrent program short name.
For uploading the script will prompt for APPS password and .ldt file name.

Related Post
Create Concurrent Programs using FNDLOAD

Over 10,000 visits in less than 7 months

Hurrrrrray .. There has been over 10,000 hits from around the world in less than 7 months. Thanks for your regular visit and comments.
Keep visiting. Please feel free to share with me any information/content/topic and I can post that with your name in this blog.

Thanks again.
Suresh

Wednesday, September 3, 2008

Call Concurrent Program from responsibility Menu

The concurrent programs can be called in one of the following 2 ways
1) From standard concurrent request by attaching the concurrent program to a request group. The request group name can be found from the responsibility. Query for the request group and attach concurrent program to that request group. Now the program will be available from that responsibility.
2) The other option is to call request directly from the Menu.

To assign a concurrent program to a menu follow the steps. I have taken Import Bills and Routings and import items program as an example.
a) Create a new function of form type and name it as your concurrent program
Bills of Material:


Import Items:


b) In the parameter field enter the request group name(if all programs assigned to the request is to be available) or enter concurrent program name(if only one concurrent program should be available).
Bills of Material:


Import Items:

c) Assign this function to a responsibility menu from which you want to run this concurrent program.

Now go to that responsibility and click on the function. It will directly launch the concurrent program

Monday, September 1, 2008

Completed 50 Posts

Just noticed that I have posted 50 articles in the blog. Thanks for visiting and appreciating it by your responses/comments. That does gives a lot of encouragement and boost in posting more topic, so keep peeking the site as there is lot more to come.

Any ideas or suggestions are more than welcome. If you have any article that needs to be shared, please feel free to share with me and I can post them in the blog with your name/photo.

Thanks,
Suresh

Wednesday, August 13, 2008

Increase Session Timeout Time

The session when kept inactive for certain period gets inactive and hence we need to relogin again.
It sometimes become frustating when working on a test instance and makes us login again and again. This can be avoided by setting a profile option.

Profile "ICX_SESSION_TIMEOUT" can be used to increase the session timeout time.

Friday, July 11, 2008

Query to get Concurrent program name and its parameter

Below query can be used to get concurrent program name and its parameter


SELECT fcpl.user_concurrent_program_name
, fcp.concurrent_program_name
, par.end_user_column_name
, par.form_left_prompt prompt
, par.enabled_flag
, par.required_flag
, par.display_flag
FROM fnd_concurrent_programs fcp
, fnd_concurrent_programs_tl fcpl
, fnd_descr_flex_col_usage_vl par
WHERE fcp.concurrent_program_id = fcpl.concurrent_program_id
AND fcpl.user_concurrent_program_name = &conc_prg_name
AND fcpl.LANGUAGE = 'US'
AND par.descriptive_flexfield_name = '$SRS$.' || fcp.concurrent_program_name

Monday, June 30, 2008

FNDLOAD in a nutshell

In the following links I have discussed on how to migrate
1) Concurrent Programs
2) Value sets
from one instance to another using FNDLOAD.

Now I will try to cover several other objects that can be migrated using FNDLOAD.
The syntax for moving any objects using FNDLOAD is almost the same except few changes. Following is the list of .lct files that are used for different objects
1) Concurrent Program --> afcpprog.lct
2) Value Sets --> afffload.lct
3) Menus --> afsload.lct

Download
FNDLOAD apps/APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt MENU MENU_NAME='XXXX'
Upload
FNDLOAD apps/APPS_PWD O Y UPLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt
4) Lookups --> aflvmlu.lct
Download
FNDLOAD apps/APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct file_name.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME='XXXX' LOOKUP_TYPE='XXXX'
Upload
FNDLOAD apps/APPS_PWD O Y UPLOAD $FND_TOP/patch/115/import/aflvmlu.lct file_name.ldt
5) Flexfield --> afffload.lct
Descriptive Flexfield
Download
FNDLOAD apps/APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt DESC_FLEX APPLICATION_SHORT_NAME='XXXX' DESCRIPTIVE_FLEXFIELD_NAME='XXXX'
Upload
FNDLOAD apps/APPS_PWD 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt
Key Flexfield
Download
FNDLOAD apps/APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt KEY_FLEX APPLICATION_SHORT_NAME='XXXX' DESCRIPTIVE_FLEXFIELD_NAME='XXXX'
Upload
FNDLOAD apps/APPS_PWD 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt
6) Profile Options --> afscprof.lct
Download
FNDLOAD apps/APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct file_name.ldt PROFILE PROFILE_NAME='XXXX' APPLICATION_SHORT_NAME='XXXX'
Upload
FNDLOAD apps/APPS_PWD O Y UPLOAD $FND_TOP/patch/115/import/afscprof.lct file_name.ldt
7) Responsibility --> afscursp.lct
FNDLOAD apps/APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct file_name.ldt FND_RESPONSIBILITY RESP_KEY='XXXX'
Upload
FNDLOAD apps/APPS_PWD O Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct file_name.ldt
8) Request Groups --> afcpreqg.lct
Download
FNDLOAD apps/APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct file_name.ldt REQUEST_GROUP REQUEST_GROUP_NAME='XXXX' APPLICATION_SHORT_NAME='XXXX' REQUEST_GROUP_UNIT UNIT_NAME='XXXX'
Upload
FNDLOAD apps/APPS_PWD O Y UPLOAD $FND_TOP/patch/115/import/afcpreqg.lct file_name.ldt
9) Menus --> afsload.lct
Download
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt MENU MENU_NAME='XXXX'
Upload
FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt
10) Forms Personalization --> affrmcus.lct
Download
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct file_name.ldt FND_FORM_CUSTOM_RULES FUNCTION_NAME='XXXX'
Upload
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct file_name.ldt

Friday, June 27, 2008

FNDLOAD to create value sets

Download a Value Set:


FNDLOAD apps/APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct VALUE_SET_XXXX.ldt VALUE_SET FLEX_VALUE_SET_NAME='XXXX'

Download Value Set Values:

FNDLOAD apps/APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct VALUE_SET_XXXX.ldt VALUE_SET_VALUE FLEX_VALUE_SET_NAME='XXXX'

Upload a Value Set:

FNDLOAD apps/APPS_PWD 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct VALUE_SET_XXXX.ldt


Related Post: Create Concurrent Program using FNDLOAD

Monday, June 23, 2008

FNDLOAD to create concurrent programs

The FNDLOAD can be used to create several oracle objects like Menu, Concurrent program, request sets, DFF, Responsibility, Messages, forms personaliztion etc. from one instance to another.
Here I will discuss how to create concurrent program using FNDLOADS

Download the program definition

FNDLOAD apps/APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct FNDLOAD_CONC_PRG_NAME.ldt PROGRAM APPLICATION_SHORT_NAME='XXXX' CONCURRENT_PROGRAM_NAME='CONC_PRG_NAME'


FNDLOAD_CONC_PRG_NAME.ldt is the file where concurrent program information is extracted. This file will be used to create same definition in any other instance.

Upload the Program definition
FNDLOAD apps/APPS_PWD O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct FNDLOAD_CONC_PRG_NAME.ldt


Note: Any value set that needs to be created along with concurrent program is downloaded and created automatically by FNDLOAD.

Tuesday, June 10, 2008

Date List of Value(LOV) for Concurrent Request Parameter

We are aware that it is not possible to display calendar window for concurrent request parameter. Here I am discussing on an alternative to create Date List of values.
This is addition to my earlier post Calender in Concurrent program.
Create a table value set as shown in the screenshot below.


click on Edit Information and Enter following in the table name field

(SELECT (TO_DATE (SYSDATE - 1 + LEVEL, 'DD-MON-RRRR')) date_range
, (TO_CHAR (SYSDATE - 1 + LEVEL, 'Month, DD RRRR')) date_word
FROM DUAL CONNECT BY LEVEL <= 1000)




This is how concurrent program looks when a value is attached to it.


Related Post: Calender in Concurrent program.

Monday, June 9, 2008

Basics of Value Sets in Oracle Applications

Explained below is the basics of Value Set.

What is a value set and where is it used?
Value set is primarily the List of Values(LOV) to restrict and mantain consistencies in entering or selecting the values. It is also the place holders to allow user enter a value. Oracle Application Object Library uses value sets as important components of key flexfields, descriptive flexfields, and Concurrent Request Submission.

What are the Format Types the value set have?
* Character
* Number
* Time
* Standard Date, Standard Date Time
* Date, Date Time
Note that Date and Date Time value set formats are obsolete and are provided for backward compatibility only. For new value sets, use the the format types Standard Date and Standard Date Time.

What are the validation types?

None:

* There is no validation done for this type of value set, hence allows user to enter any value.
Independent:

* It provides a list of pre-defined values. The predefined values are entered seperately.
Dependent:

* Same like Independent Value Set, except the List of Values shown to you will depends on which the Independent value you have selected in the Prior Segment.
* Must define your independent value set before you define the dependent value set that depends on it.
* Must create at least one dependent value for each independent value.
Table:

* The list of value is created based on database tables
* Allows to write simple queries, joins, order by etc
* The value, meaning and ID can be used to display a value, description to the value but return ID to the calling program or screen.
* Additional columns can also be displayed. The syntax is column "column title(size)",.... e.g. order_type "SO Order Type(40)"
* Can also create dependent values to filter LOV data based on parameter value selected earlier. This can be done using :$FLEX:.value_set_name in the where clause.
Special & Pair:
Pair validation value set allows to select a range of concatenated Flex field segments as parameters to the report. The special value set is used to perform special validation. This is used to enter the entire key flexfield segment in the single parameter of the report/calling entity.
Translatable Independent & Translatable Dependent:

* This is similar to Independent and Dependent value set except that translated values can be displayed to the user.

APIs to create value set
The FND_FLEX_VAL_API package can be used to create different types of value sets.
* VALUESET_EXISTS - To check if value set exists
* DELETE_VALUESET - To delete value set. The value set can only be deleted if it is not being referenced by any program or entity.
* CREATE_VALUESET_NONE
* CREATE_VALUESET_INDEPENDENT
* CREATE_VALUESET_DEPENDENT
* CREATE_VALUESET_TABLE
* CREATE_VALUESET_SPECIAL
* CREATE_VALUESET_PAIR

What are the oracle tables that store value set information?
* FND_FLEX_VALUE_SETS
* FND_ID_FLEX_SEGMENTS
* FND_FLEX_VALUE
* FND_FLEX_VALIDATION_EVENTS
* FND_FLEX_VALUE_RULE_LINES
* FND_FLEX_VALUE_RULE
* FND_FLEX_VALUE_RULE_USAGE
* FND_FLEX_VALIDATION_TABLES

Friday, June 6, 2008

Display Distinct Value in Value Set

Now there are 3 ways to create distinct values value set
1) Write distinct statement in the field where table name is entered, but this has a limitation to the number of characters that can be entered. so if query is long then you cannot make use of this feature
2) Create a view based on your query and use that view in the value set
3) Write your query in such a way that it gives you a distinct value. You can make use of ROWID to meet the requirement.


ex. SELECT ooh.order_number
FROM oe_order_lines_all ool, oe_order_headers_all ooh
WHERE ool.header_id = ooh.header_id
AND ool.ROWID = (SELECT MAX(ROWID)
FROM oe_order_lines_all oo11
WHERE oo11.header_id = ooh.header_id)


Note that the above query is just an example and needs to be changed as per your need. In the above query I purposely joined lines and header to display duplicate order_numbers and then supressed it by using MAX(ROWID).

Thursday, May 29, 2008

Enable/Disable Concurrent program parameter based on Other Parameter

Based on a request, here is the details on how parameter can be enabled/disabled based another parameter value.
Below is the requirement
There are 2 valuesets parameter. If for parameter 1 user selects Yes then the other paramter should be enabled whereas if No is selected then the parameter should remain disabled. This can be achieved by using a hidden parameter as explained below.

Step1: Need 3 value sets for 3 parameter.
Value set1 = BOM_SRS_YES_NO (Oracle Defined)
Value Set2 = AMS_SRS_CHAR1 (Oracle Defined)
Value Set3 = SV_DEPENDENT_VS (User Defined)




Step2: Create Concurrent program as displayed in the screenshot below
Parameter1: Main Parameter

Parameter2: Hidden PArameter

Parameter3: Dependent Parameter

Step3: Assign concurrent program to a request group and test your program.

Wednesday, May 21, 2008

The PL/SQL Wrap Utility - Hide source code in Oracle

A company has a code(Package, Procedure, Function etc) with all the proprietary information and logic in it. If this information is leaked out in the market then the competitors can take advantage of it and this can affect the business. One of the way to deal with this is to hide the code from others.
This can be achieved using oracle's WRAP utility. The advantage of WRAP utility is that this converts the source code into some language that is not understood but the code can still be compiled like any other source code.
Using Wrap is very simple. In the bin directory of Oracle Home, the wrap utility is installed. The file name could be WRAP.exe or WRAP80.exe depending on the oracle version installed.
Syntax

 C:\orant\BIN>wrap.exe iname=[inputfilename] oname=[outputfilename] 


e.g.
 C:\orant\BIN>wrap.exe iname=wrap_test.sql oname=wrap_test.plb 


An example of using WRAP

Create a sample procedure wrap_test using following code

CREATE OR REPLACE PROCEDURE wrap_test
IS
BEGIN
dbms_output.put_line('Wrap test complete');
END;
/
then call the wrap utility using following
wrap.exe iname=wrap_test.sql oname=wrap_test.plb

Content of new file wrap_test.plb
CREATE OR REPLACE PROCEDURE wrap_test wrapped
a000000
b2
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
7
4f 8d
LPjE3qKQyH/yQRCK4+efvSyST50wg5nnm7+fMr2ywFznKMB04yhSssvum3SLwMAy/tKGBvVS
m7JK/iiyveeysx0GMCyuJOqygaVR2+EC8XcG0wJd5GisbnfxwzIu9tHqJB/2OabWTW+0

/

It is very clear from this that the new code is not readable and so is completely hidden from others.
Drop your procedure(if already created) and recreate using the the new file wrap_test.plb which can be compiled as any other package. Important point here is that the source code will be hidden and cannot be read.
Another important point to remember is that once wrapped, a code cannot be unwrapped.

Thanks please let me know your feedback.

Tuesday, May 20, 2008

Query to get Customer Related information for a Sales Order

Here is another handy query to get Customer related information for a sales order.
The query will list SHIP TO and BILL TO Address for a customer.

SELECT ooh.order_number
, hp_bill.party_name
, hl_ship.address1 ||Decode(hl_ship.address2,NULL,'',chr(10))
||hl_ship.address2||Decode(hl_ship.address3,NULL,'',chr(10))
||hl_ship.address3||Decode(hl_ship.address4,NULL,'',chr(10))
||hl_ship.address4||Decode(hl_ship.city,NULL,'',chr(10))
||hl_ship.city ||Decode(hl_ship.state,NULL,'',',')
||hl_ship.state ||Decode(hl_ship.postal_code,'',',')
||hl_ship.postal_code ship_to_address
, hl_bill.address1 ||Decode(hl_bill.address2,NULL,'',chr(10))
||hl_bill.address2||Decode(hl_bill.address3,NULL,'',chr(10))
||hl_bill.address3||Decode(hl_bill.address4,NULL,'',chr(10))
||hl_bill.address4||Decode(hl_bill.city,NULL,'',chr(10))
||hl_bill.city ||Decode(hl_bill.state,NULL,'',',')
||hl_bill.state ||Decode(hl_bill.postal_code,'',',')
||hl_bill.postal_code bill_to_address
, ooh.transactional_curr_code currency_code
, mp.organization_code
, ooh.fob_point_code
, ooh.freight_terms_code
, ooh.cust_po_number
FROM oe_order_headers_all ooh
, hz_cust_site_uses_all hcs_ship
, hz_cust_acct_sites_all hca_ship
, hz_party_sites hps_ship
, hz_parties hp_ship
, hz_locations hl_ship
, hz_cust_site_uses_all hcs_bill
, hz_cust_acct_sites_all hca_bill
, hz_party_sites hps_bill
, hz_parties hp_bill
, hz_locations hl_bill
, mtl_parameters mp
WHERE 1 = 1
AND header_id = :p_header_id
AND ooh.ship_to_org_id = hcs_ship.site_use_id
AND hcs_ship.cust_acct_site_id = hca_ship.cust_acct_site_id
AND hca_ship.party_site_id = hps_ship.party_site_id
AND hps_ship.party_id = hp_ship.party_id
AND hps_ship.location_id = hl_ship.location_id
AND ooh.invoice_to_org_id = hcs_bill.site_use_id
AND hcs_bill.cust_acct_site_id = hca_bill.cust_acct_site_id
AND hca_bill.party_site_id = hps_bill.party_site_id
AND hps_bill.party_id = hp_bill.party_id
AND hps_bill.location_id = hl_bill.location_id
AND mp.organization_id(+) = ooh.ship_from_org_id


Please do let me know if this queries are helping and I will post more of such queries.

Note: The query is not tested completely. If any problem found with the above query please let me know and I will try to fix them.

Monday, May 12, 2008

Create List of Values (LOV) in Discoverer

The Item classes in Discoverer are treated as List of Values (LOVs). A List of value can be created and referenced by several fields of the folder.
Following are the steps to create LOV in discoverer. In the example below I am creating LOV on US States.
1) Connect to Discoverer using Admin and select the business area where folder/LOV needs to be created.
2) Create a custom folder for US States using following query

select * from ar_lookups
where lookup_type like 'STATE'

3) Select the field for which Item class is to be created and right click on that to select New Item Class. Click next on the wizard and finally click finish button to complete creation of Item class. In our example, the LOV is created on the lookup code field.

4) Now if any parameter is created based on Lookup Code column then the List of value will be attached to it.
5) This LOV can also be referenced by another column. For example there is another folder which has column Bill to State. Select that column and right click to go to the properties. Click on Item Class and select the Item class created in step 3.

Monday, May 5, 2008

Splitting String using Oracle SQL 9i

Run following query to split values seperated by comma(,)

 SELECT TRIM( SUBSTR ( txt
, INSTR (txt, ',', 1, level ) + 1
, INSTR (txt, ',', 1, level+1) - INSTR (txt, ',', 1, level) -1
)
)
AS token
FROM ( SELECT ','||:in_string||',' AS txt FROM dual )
CONNECT BY level <= LENGTH(txt)-LENGTH(REPLACE(txt,',',''))-1


Example
-------
If value of in_string is entered as 1234,2,3,45,6,7,7,88,9,346

Output is
TOKEN
-----
1234
2
3
45
6
7
7
88
9
346

Reference:
Tom Kyte's Blog

FAQ: Common Tracing Techniques for Oracle Apps

1) How to enable trace in the Oracle Application screens / forms?
Below is the navigation to enable trace for forms
Help Menu --> Diagnostics --> Trace(Select appropriate trace as needed)

Most commonly if debugging an error, you may select trace with binds.
When debugging a performance issue, you may consider using trace with binds and waits.

Remember to disable trace as this will keep generating the file.

2) How to enable trace for a concurrent program?
Refer to post Create Trace File for Concurrent Program

3) How to enable trace for a concurrent program INCLUDING bind variables and waits?
The above method can be used to generate a trace file, but this does not includes bind variables and wait times.
Download and review the script, bde_system_event_10046.sql, from Metalink Note 179848.1.
The above script is used to turn on the trace with binds and wait(Level 12).
Run Script and press enter when prompted.
Return to application and submit your concurrent program.
The moment status changes to running, switch to SQL*PLUS and press enter again to turn tracing off.
Important: Note that the tracing is set at global level and any program that runs during time when trace is kept on will be traced. Hence it is very important to immediately turn trace off after your programs status changes to running.

4) How to enable trace for all actions that occur for a user?
Use profile option Initialization SQL Statement - Custom
Set the value at user level

BEGIN FND_CTL.FND_SESS_CTL('','', '', 'TRUE','','ALTER SESSION SET TRACEFILE_IDENTIFIER='||''''||'4269824.999' ||''''||' EVENTS ='||''''||' 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12 '||''''); END;


Reference: Metalink Note 296559.1

Related Post:
SQL Trace and TKPROF
Various options with TKPROF
Create Trace File for Concurrent Program

Friday, May 2, 2008

Various options with TKPROF

The post is in continuation to SQL Trace and TKPROF

Syntax of TKPROF

tkprof [filename1] [filename2] [WAITS] [SORT] [PRINT]
There are few more but these are more commonly used.

Argument Description
filename1 : The trace file name
filename2 : Filename to which TKPROF writes formatted output
WAITS : Flag to record summary for any wait events found in the trace file. Values are YES or NO
SORTS : Sorts traced SQL statements in descending order of specified sort option before listing them into the output file
PRSCNT Number of times parsed.
PRSCPU CPU time spent parsing.
PRSELA Elapsed time spent parsing.
PRSDSK Number of physical reads from disk during parse.
PRSQRY Number of consistent mode block reads during parse.
PRSCU Number of current mode block reads during parse.
PRSMIS Number of library cache misses during parse.
EXECNT Number of executes.
EXECPU CPU time spent executing.
EXEELA Elapsed time spent executing.
EXEDSK Number of physical reads from disk during execute.
EXEDSK Number of physical reads from disk during execute.
EXEQRY Number of consistent mode block reads during execute.
EXECU Number of current mode block reads during execute.
EXEROW Number of rows processed during execute.
EXEMIS Number of library cache misses during execute.
FCHCNT Number of fetches.
FCHCPU CPU time spent fetching.
FCHELA Elapsed time spent fetching.
FCHDSK Number of physical reads from disk during fetch.
FCHQRY Number of consistent mode block reads during fetch.
FCHCU Number of current mode block reads during fetch.
FCHROW Number of rows fetched.

PRINT : Number of SQL statements to be displayed

Example 1
If you are processing a large trace file using a combination of SORT parameters and the PRINT parameter, then you can produce a TKPROF output file containing only the highest resource-intensive statements. For example, the following statement prints the 10 statements in the trace file that have generated the most physical I/O:

tkprof abcd53269.trc abcd53269.txt SORT = (PRSDSK, EXEDSK, FCHDSK) PRINT = 10


Example 2
Print the SQL Query taking maximum CPU Usage
tkprof abcd53269.trc abcd53269.txt SORT = (EXECPU,FCHCPU) PRINT = 1

Wednesday, April 30, 2008

SQL Trace and TKPROF

Understanding SQL Trace
Times when program is performing poorly, creating and examining a trace file is one of the best way to find what is causing the problem for poor performance.
Following is the list of some of the SQL Trace statictics that are generated in the trace file.

Parse, execute, and fetch counts
CPU and elapsed times
Physical reads and logical reads
Number of rows processed
Commits and Rollback

There are several ways by which you can turn on/off SQL Trace

Turn on
DBMS_SESSION.SET_SQL_TRACE(TRUE);
ALTER SESSION SET SQL_TRACE = TRUE;


Turn off
DBMS_SESSION.SET_SQL_TRACE(FALSE);
ALTER SESSION SET SQL_TRACE = FALSE;

Click here to set SQL Trace on a concurrent program.
The trace file is created in the udump directory.

Understanding TKPROF
The TKPROF program can be used to format the contents of the trace file and convert it into a readable output file.
TKPROF can also be used to generate Explain Plan for the queries.
I will create a seperate post to discuss various options available with TKPROF.

Monday, April 21, 2008

Create Trace File for Concurrent Program

If your program is taking time to complete, then the best way to know what is causing the problem is by creating a trace file.

Navigation:
System Administrator(R) --> Concurrent --> Program --> Define

Query for the concurrent program and check enable trace button.


Now when the concurrent program is executed the trace file is created in the udump directory. The path of udump directory can be found by executing following query.

select * from v$parameter
where name like '%user_dump_dest%'


The trace file can be converted to a readable format by running a tkprof command over the trace file.
Syntax:
tkprof [trace_file_name] [new_file_name]


Click for more info on TKPROF

Friday, April 18, 2008

Oracle Table Rename Syntax

There is a direct command to rename table.
The syntax is as follows


alter table
table_name
rename to
new_table_name;


Note: When the table is renamed the referenced object like PL/SQL, applications etc are not updated. Hence this may make those invalid. However the indexes, constraints etc are updated.

Tuesday, April 8, 2008

Dependent Value Parameter in Discoverer

In Discover 4i there was no way to filter the list of second parameter based on the value selected in first parameter.
Discoverer Version 10g has overcome this problem and allows to filter the List of Values based on another parameter. This is very similar to dependent value set that we create in Oracle Apps.
In the paramter screen(shown in the screen shot below) Select Option "Filter the List of Values based on the selection conditions, then select the parameter based on which the value need to be filtered. Click OK and you are all set to filter the list based on another parameter.

Monday, April 7, 2008

Not Required Parameter in Discoverer

The old version of Discover4i forced to enter values for all the paramters. If the value needs to be passed as NULL, then user had to enter either NULL or '' as the parameter value.

Discover 10g has got rid of this problem. The parameter can now be made a non-required parameter by checking "Require User to Enter a Value" checkbox as shown in the screenshot below.

Friday, April 4, 2008

Password Policy and profile options

On apps there are a lot of profile options that are useful in making apps passwords difficult to guess, the profiles are

1-Signon password failure limit
2-Signon Password Length
3-Signon Password No Reuse
4-Signon Password Hard to Guess

For the first one it means how many time can I try to access the system using wrong password. It is recommended to change this value to 3. The default value is null.

The second one to allow minimum password length. The default value is 5, it is recommended to make it 6 or 7.

The 3rd profile is for not allowing using same password again for specified number of days.

The default value for 4th profile option is No. Following are the password rules if the value is set to Yes
1) The password contains at least one letter and at least one number.
2) The password does not contain the username.
3) The password does not contain consecutively repeating characters.

Reference: Metalink Note 362663.1

Tuesday, April 1, 2008

Basic VI Editor Commands

Cursor Movements

h, j, k, l

Move left, Down, Up, Right

0

Move to First Character of the Line

$

Move to end of Line

w

Move to next word

CTRL + D

Move Page Down

CTRL + U

Move Page Up

G

Move to the End of File

1G or :1

Move to the Top of File

nG

Move to Line number n. (replace n is any integer value)

CTRL + G

Display current Line Number


 

Find a Text

/text

Find a text in Forward Direction

?text

Find a text in Reverse Direction

n

Repeat the previous Search


Note: text needs to be replaced by the string that needs tobe searched.

 

Editing, Inserting orDeleting a Text

a

Append after Cursor

A

Append to the end of Line

i

Insert before cursor

I

Insert from the Beginning of the line

o

Insert a new Line in the edit mode

cw

Change word

cc

Change whole line

C

Change text to end of line

dd

Delete Current Line

5dd

Delete current line and next 4

D

Delete from Current Position to the end of line

u

Undo last change

U

Restore Current Line


 

Copy(yanking) and Paste

yy

Yank a copy of Current Line

p

Insert previously yanked Line


 

Saving and Exiting

ZZ or :wq

Save and exit file

:q!

Exit without Saving

:w filename1

Save edited file as filename1. Similar to Save as.

Saturday, March 29, 2008

Calling SqlPlus From Unix Shell Script

In this post we will discuss how we can call sqlplus from within a unix shell script. Here is a post on how to read concurrent program parameters from shell script and this is a continuation to that.
The syntax to call a SQLPLUS from shell script is


sqlplus -s $login <<-EOF
set feedback off
set serveroutput on
declare
-- variable declarations
....
....
begin
--sql statements..
....
....
end;
/
exit
EOF

In the syntax above,
$login is the unix variable which stores username/password.
EOF is the identifier to indicate start and end of sql statements.
UNIX variables can be referred inside sqlplus by using $ sign. Any dbms_output.put_line statements are printed in the log file if script is run through concurrent program.

Thursday, March 27, 2008

Hide a Button in the form using Forms Personlization

In one of the forum, there was a requirement to allow bookings only by certain user.
This can be achieved using forms personalization for users on 11.5.10 or higher.
Open the sales Order Form and click on Help->Diagnostics->Custom Code->Personalize to open the forms personlization screen.
(Click on the image to zoom it)






P.S.
In the example above I have hardcoded user name which is not the advisable way.
The better approach could be to create a custom profile and assign a value to the custom profile at user level. Based on the value entered the button can be enabled or disabled.

Note: The same result can also be achieved by using custom.pll

Tuesday, March 25, 2008

Custom Title Bar

Profile option "Site Name" can be used to display custom message in the title bar.
Often used in DEV/Test instance to track when the instance was refreshed/cloned.

Read Concurrent Parameters in UNIX shell script

Found this question in one of the forum and thought of creating a blog for same.
Often when concurrent program is created on a shell script there is a need to extract parameters passed from concurrent program in the shell script.
In Shell Script, $1 is the variable that stores following information
- Concurrent Program Short Name
- Request ID
- Login Information(APPS username and password)
- User Id
- User Name
- Printer Name
- Save output Flag
- Print number of Copies
- List of concurrent program parameters

Following code can be used to extract different values


requestid=`(echo $1 | cut -f2 -d' ' | cut -f2 -d= | tr -d '"' )` #request_id
login_usr_pwd=`(echo $1 | cut -f3 -d' ' | cut -f2 -d= | tr -d '"' )` #database username/password
conc_user_id=`(echo $1 | cut -f4 -d' ' | cut -f2 -d= | tr -d '"' | cut -c1-8)` #userid
conc_user_name=`(echo $1 | cut -f5 -d' ' | cut -f2 -d= | tr -d '"' | cut -c1-8)` #username
prog_param1=`(echo $1 | cut -f9 -d' ' | tr -d '"' )` #parameter 1
prog_param2=`(echo $1 | cut -f10 -d' ' | tr -d '"' )` #parameter 2
prog_param3=`(echo $1 | cut -f11 -d' ' | tr -d '"' )` #parameter 3
prog_param4=`(echo $1 | cut -f12 -d' ' | tr -d '"' )` #parameter 4
prog_param5=`(echo $1 | cut -f13 -d' ' | tr -d '"' )` #parameter 5
prog_param6=`(echo $1 | cut -f14 -d' ' | tr -d '"' )` #parameter 6


When connecting to SQLPLUS in shell script, it is always advisable to extract database username and password from parameter($login in our example above) and not hard-code the value in program.

Monday, March 17, 2008

Replace Keyboard with Mouse - Type using Mouse

Here is something interesting for you.
Replace Keyboard with Mouse and type letters using mouse.
Steps:
Click Start --> Select Run --> Type OSK --> Press OK

A Keyboard will Popup on the screen that can be used for typing. Isn't that interesting and useful sometimes.

Sunday, March 16, 2008

External Table in Oracle

From Version 9i Oracle has come with a new feature facilitating us to query directly from the FLAT File. This is know as external tables.
Here are the simple steps for creating external table
1) Create Directory

create or replace directory sv_ext_dir as '/home/svaishya/external_table'

2) Grant access to Directory
grant read, write on directory sv_ext_dir to apps

The user must have a read and write permission the directory('/home/svaishya/external_table' in this case).
3) Create External Table

CREATE TABLE sv_external_Table
(col1 VARCHAR2(50)
,col2 VARCHAR2(50)
)
ORGANIZATION EXTERNAL
( TYPE oracle_loader
DEFAULT DIRECTORY sv_ext_dir
ACCESS PARAMETERS
( FIELDS TERMINATED BY '|' )
LOCATION ('abc.txt')
)

abc.txt is the name of the file that will be located in the path mentioned in the directory sv_ext_dir

4) Place the file in the Directory Path
abc.txt (used in above create table command) is copied the directory.

5) Thats it, query table to extract data.
SELECT * FROM sv_external_table

Everytime the file is updated with new data the data in the table will be automatically refreshed.

Different Types of Table in Oracle Apps

In oracle applications there are tables that ends with similar suffixes. Here I am trying to list the meaning of those, please provide your suggestion and help me if I have missed anything.

_B The Main base tables
_ALLContains multi org data. There will be similar table without _ALL. Before querying this data the environment variable needs to be set. Dbms_application_info.set_client_info('org_id'), or apps_initialize can be used to set the environment variable.common column.
_VView created on base table. Mostly forms are created based on this views
_TL Tables that support multi language.
_VL View created on multi language tables. The view generally uses the base table and _tl table
_F This indicates that these are the date tracking tables. These tables are generally seen for HRMS and contain 2 common columns effective_start_date and effective_end_date
_S sequence related tables
_DFV /_KFV The DFF/KFF table created on the base table. This is the best way to get the concatenated value of DFF/KFF.
Also using this table the values can be queried based on the DFF/KFF name and not attributes column.

Saturday, March 15, 2008

See Parameters for the Submitted Request

A concurrent program is submitted which has many parameters. After the program is submitted I need to map the values with the parameter.
Click "Find Request" -> "View Details...", click on "Parameters" field, the parameters form will popup.




Sometimes you may feel that the forms have gone crazy and the parameters form does not pop up.This is because you are not in the correct responsibility that enable you to launch that request. The parameters form popup only from the responsibility where reports are registered.

View Report Output Submitted by others

Many times it happens that we need to see output submitted by others. Through system administrator View all concurrent program option we can search for the request but the problem is that the view output button is grayed off. How do we enable this button and see the output.
The answer is simple as profile does a magic here.

Change the Profile Option value for Concurrent:Report Access Level. Default value for the profile is "User", which means View Output is only accessible by the particular user that submitted the request. Set it to "Responsibility", now the View Output button is enabled from responsiblity where the report is registered.

Monday, March 10, 2008

Proposed Reverse Life Cycle

Found something interesting and thought of sharing with all of you. Hope you like it :-)

Saturday, March 8, 2008

Remove Control M (^M) Characters From Unix File

If you see control M characters(^M) in a file in UNIX, don't worry there is a very simple way to convert the file in UNIX format.
The command is


dos2unix filename [newfilename]


e.g. remove control characters from file abc.txt

dos2unix abc.txt abc.txt #this will replace abc.txt and remove control characters

dos2unix abc.txt xyz.txt #a new file xyz.txt will be created after removing control characters

So isn't that simple lovely command took off all the worries.

Friday, March 7, 2008

Find Running, Pending, On Hold and Scheduled Requests

There are several instances where we need to get the list of scheduled concurrent request. Below is another handy query that displays all the concurrent request that are in running, pending or scheduled status.


SELECT fcr.request_id,
DECODE (fcr.phase_code,
'P', DECODE (fcr.hold_flag, 'Y', 'Inactive', fl_pend.meaning),
fl_pend.meaning
) phase,
DECODE (fcr.phase_code,
'P', DECODE (fcr.hold_flag,
'Y', 'On Hold',
DECODE (SIGN (fcr.requested_start_date - SYSDATE),
1, 'Scheduled',
fl_stat.meaning
)
),
fl_stat.meaning
) status,
fcpt.user_concurrent_program_name, fcr.increment_dates,
fcr.resubmit_interval, fcr.resubmit_interval_unit_code,
fcr.resubmit_interval_type_code, parent_request_id,
fcr.requested_start_date, fu.user_name requested_by
FROM fnd_concurrent_requests fcr,
fnd_concurrent_programs_tl fcpt,
fnd_lookups fl_pend,
fnd_lookups fl_stat,
fnd_user fu
WHERE 1 = 1
AND fcpt.concurrent_program_id = fcr.concurrent_program_id
AND fcpt.LANGUAGE = USERENV ('LANG')
AND fcr.phase_code = fl_pend.lookup_code
AND fl_pend.lookup_type = 'CP_PHASE_CODE'
AND fcr.status_code = fl_stat.lookup_code
AND fl_stat.lookup_type = 'CP_STATUS_CODE'
AND fl_pend.meaning != 'Completed'
AND fu.user_id = fcr.requested_by
ORDER BY fcr.request_id DESC


PS: The query below is not fully tested, hence please provide your feedback incase you find any unexpected result.

Thursday, March 6, 2008

Display Numbers in Figures (words)

It happens very often that there is a requirement to display amounts in words, but in most of the forms we have amounts displayed in numbers. Here I will discuss on various options/methods to meet this.

1) Using Julian method


select to_char(to_date(11221873,'J'),'JSP') from dual -- For upper-case letters

select to_char(to_date(11221873,'J'),'Jsp') from dual -- For mixed-case letters

select to_char(to_date(11221873,'J'),'jsp') from dual -- For lower-case letters

The Julian Method is limited to display text only till million, so 9,999,999 is the maximum value that can be displayed in words.

2) Using Data and Time Suffixes. Listed is the option for Data and Time Suffix
TH - converts numbers to ordinal numbers
SP - converts numbers to words
SPTH - converts numbers to ordinal words

SELECT TO_CHAR(TO_TIMESTAMP(LPAD(99999999, 9, '0'), 'FF9'),'FFSP') AS amt_in_words
FROM DUAL -- For Upper Case Letters

SELECT TO_CHAR(TO_TIMESTAMP(LPAD(99999999, 9, '0'), 'FF9'),'Ffsp') AS amt_in_words
FROM DUAL -- For Mixed Case Letters

SELECT TO_CHAR(TO_TIMESTAMP(LPAD(99999999, 9, '0'), 'FF9'),'ffsp') AS amt_in_words
FROM DUAL -- For Lower Case Letters

The maximum value that can be displayed is 99,999,999. So we added another number as against the Julian function.

3) This is the best option but limited only to Oracle Application users.
Oracle Apps has provided an inbuilt function AP_AMOUNT_UTILITIES_PKG which can be used to achieve the result.

SELECT Upper(ap_amount_utilities_pkg.ap_convert_number (111234234324)) AS amt_in_words
FROM DUAL -- For Upper Case Letters

SELECT InitCap(ap_amount_utilities_pkg.ap_convert_number (111234234324)) AS amt_in_words
FROM DUAL -- For Mixed Case Letters

SELECT Lower(ap_amount_utilities_pkg.ap_convert_number (111234234324)) AS amt_in_words
FROM DUAL -- For Lower Case Letters

Well as there is an end to everything, even this function has a limitation but it can display big enough as it goes upto hundred billions. 999,999,999,999 is the maximum allowed value.

Tuesday, February 12, 2008

Tips and Tricks with SQL Loader

Following are some of the tips and tricks that can be used with SQL Loader

1) Load text for a column which is having more than 4000 bytes.
Use following syntax


LOAD DATA
APPEND INTO TABLE sv_test
FIELDS TERMINATED BY '|' TRAILING NULLCOLS
(
long_text_columns char(40000)
)


2) How to use oracle functions with SQL Loader

LOAD DATA
APPEND INTO TABLE sv_test
FIELDS TERMINATED BY '|' TRAILING NULLCOLS
(
long_text_columns char(40000) --- first 40000 characters
, column1 "trim(:column1)" --- Trims and loads
, column2 "replace(:column2,'\n',chr(10))" --- replace \n with new line
, column3 DATE "DD-MON-YYYY" --- defining date style
, column4 "upper(:column4)" --- changing to upper case
, column5 "lower(:column5)" --- changing to lower case
, column6 constant "FIXED" --- assigning a constant value
, column7 "sv_sequence.nextval" --- defaulting a value from sequence
)


Thats it for today .. will update with more commands later.

Sunday, February 10, 2008

Profile Value at any Level

Below is another handy query to get profile Values at all levels.
The query prompts for 2 parameters, the profile name and the level name that could be application/responsibility/username. Its a wild search so will retrieve data if any of parameter is passed a blank.

SELECT   distinct pot.user_profile_option_name Profile
     , DECODE (a.profile_option_value
             , '1', '1 (may be "Yes")'
             , '2', '2 (may be "No")'
             , a.profile_option_value
              ) Value
     , DECODE (a.level_id
             , 10001, 'Site'
             , 10002, 'Application'
             , 10003, 'Responsibility'
             , 10004, 'User'
             , '????'
              ) Level_identifier
     , DECODE (a.level_id
             , 10002, e.application_name
             , 10003, c.responsibility_name
             , 10004, d.user_name
             , '-'
              ) Level_Name
FROM     applsys.fnd_application_tl e
     , applsys.fnd_user d
     , applsys.fnd_responsibility_tl c
     , applsys.fnd_profile_option_values a
     , applsys.fnd_profile_options b
     , applsys.fnd_profile_options_tl pot
WHERE    1=1
AND      UPPER (pot.user_profile_option_name) LIKE UPPER ('%&v_profile%')
AND      pot.profile_option_name = b.profile_option_name
AND      b.application_id = a.application_id(+)
AND      b.profile_option_id = a.profile_option_id(+)
AND      a.level_value = c.responsibility_id(+)
AND      a.level_value = d.user_id(+)
AND      a.level_value = e.application_id(+)
AND      e.language(+) = userenv('lang')
AND      (   UPPER (e.application_name) LIKE UPPER ('%&appname_respname_username%')
        OR UPPER (c.responsibility_name) LIKE UPPER ('%&&appname_respname_username%')
        OR UPPER (d.user_name) LIKE UPPER ('%&&appname_respname_username%')
       )
ORDER BY Profile
     , Level_identifier
     , Level_name
     , Value


Do comment your feedback. Thanks.

Copyright (c) All rights reserved. Presented by Suresh Vaishya