Happy New Year 2009

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

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

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

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

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.

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

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
)

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 '!'

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

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

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

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.

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

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

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

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.

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.

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

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).

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.

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.

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.

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.

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

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

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.

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

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.

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.

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.

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

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.

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.

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

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.

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.

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
_ALL	Contains 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.
_V	View 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.

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.

Proposed Reverse Life Cycle

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

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.