This demo I found from apps expert Mr. Anil Passi's blog. His contribution to the apps community is awesome. We are deeply thankful to him. Great job Anil.
Go to below link
http://apps2fusion.com/training_demo/anilpassi/claf-blaf-custom-skin-in-oaf/claf_step_by_step_demo.html
Go to below link for color selection:
http://w3schools.com/HTML/html_colors.asp
Showing posts with label General ERP. Show all posts
Showing posts with label General ERP. Show all posts
Monday, March 15, 2010
Wednesday, January 20, 2010
‘File ->Export’ Function is troubling
‘File ->Export’ Function is troubling
Posted on November 13th, 2008 by Sanjit Anand | Print This Post | Email This Post
Hard Time this week for me ,struggling with Finance user from another entity to see unexpected things reported , Out of the box File -> export function was not working properly, some of them reported Browser crashing some of them reported data not exported 100% from form, some of them reported longer duration to export for nearly 500 records from Invoice Inquiry or Invoice Entry form.
There are few cases which user can experience
File - Export does not bring up a Save As prompt
File -Export may leads to Browser crashes
File - Export takes long time to export into Excel
File - Export not exporting 100% data from form
File - Export creates zero byte file
File -Export on selecting "Continue to End" causes system to hang
More less, these problems are encountered by some additional setup requirement in IE . Here are few troubleshooting points, if you are using IE 6 or 7.
1. Make sure your browser will have setup completed for these 2 categories:
ActiveX controls
Downloads
You should follow these Steps:
Go to IE Tools menu - Internet Options - Security - Custom Level
Adjust all the settings for ActiveX Controls and Downloads to be either 'enable' or prompt for zones Internet, Local Intranet, and Trusted Zones
Restart the browser and now test if the export process works
2. Make sure if users desktop are using a proxy server to access the web via MSIE. If you set "Bypass proxy server for local addresses" in Internet Options -> Connection -> LAN Settings check if the File > Export works without error.
3. Make sure you have set System Profile Options 'Export: Mime type' to 'text/tab-separated-values' or or 'application/vnd.ms-excel'
4.Delete Browser Cache > Tools > Internet Options > zone=temporary internet files : delete files {checked offline content}
5. You should also delete C:program files/oracle/jinitiator/jcache/*
6. Make sure you disable Pop-up Blocker
7. If you have still an issue , try this based out of note :338545.1
Make sure you have checked step 1,2,3.
Login to AP responsibility and try exporting from the data from Invoice workbench after search for invoice batch.
Are you able to export?
If not ,Go to (Tool Bar) Help -> Diagnostics -> Examine
Set Block = ENVIRONMENT
Set Field = FND_EXPORT_DEBUG
Set Value = TRUE
Export and observe the messages that are generated during the export process.
What is the last pop up message ?
Try to run the following piece of code in SQLPLUS*
declare
db_file number;
mime_type varchar2(255) :='text/plain' ;
out_string varchar2(32767) :='Just some plain text that is stored' ;
web_server_prefix varchar2(500);
url varchar2(500);
begin
fnd_global.apps_initialize(user_id =>,resp_id => ,resp_appl_id => );
DBMS_SESSION.SET_NLS('NLS_LANGUAGE','AMERICAN');
db_file :=fnd_gfm.file_create(content_type =>mime_type,program_name=> 'export');
fnd_gfm.file_write_line(db_file,out_string);
db_file :=fnd_gfm.file_close(db_file);
url:=fnd_gfm.construct_download_url(fnd_web_config.gfm_agent,db_file,TRUE);
dbms_output.put_line(url);
end;
If still not able to fix, ask Oracle for further resolution.
8.If you are not able to 100% export in excel do a quick check
Run this query immediatly after Export get completed
select count(*) from fnd_lobs where program_name='export';
If counts not matches then you need to recreate the index using script $FND_TOP/sql/aflobbld.sql. This is because the File/Export functionality of EBS uses system LOB columns to store temp data before it is outputed to a browser. If that temp data becomes large enough as the result of large export query set, then the max_extents limitation of the column is reached thereby causing the export to fail.
Most of my user experinces these issues with WIN2K or XP SP1 and SP2, but not with SP3.No clue for me with service pack.
Is there any linkage with window XP or 2000 .Net Framework installation and Service pack in client desktop for this feature? Do share some thoughts if anyone got such kind of issue in past.
Posted on November 13th, 2008 by Sanjit Anand | Print This Post | Email This Post
Hard Time this week for me ,struggling with Finance user from another entity to see unexpected things reported , Out of the box File -> export function was not working properly, some of them reported Browser crashing some of them reported data not exported 100% from form, some of them reported longer duration to export for nearly 500 records from Invoice Inquiry or Invoice Entry form.
There are few cases which user can experience
File - Export does not bring up a Save As prompt
File -Export may leads to Browser crashes
File - Export takes long time to export into Excel
File - Export not exporting 100% data from form
File - Export creates zero byte file
File -Export on selecting "Continue to End" causes system to hang
More less, these problems are encountered by some additional setup requirement in IE . Here are few troubleshooting points, if you are using IE 6 or 7.
1. Make sure your browser will have setup completed for these 2 categories:
ActiveX controls
Downloads
You should follow these Steps:
Go to IE Tools menu - Internet Options - Security - Custom Level
Adjust all the settings for ActiveX Controls and Downloads to be either 'enable' or prompt for zones Internet, Local Intranet, and Trusted Zones
Restart the browser and now test if the export process works
2. Make sure if users desktop are using a proxy server to access the web via MSIE. If you set "Bypass proxy server for local addresses" in Internet Options -> Connection -> LAN Settings check if the File > Export works without error.
3. Make sure you have set System Profile Options 'Export: Mime type' to 'text/tab-separated-values' or or 'application/vnd.ms-excel'
4.Delete Browser Cache > Tools > Internet Options > zone=temporary internet files : delete files {checked offline content}
5. You should also delete C:program files/oracle/jinitiator
6. Make sure you disable Pop-up Blocker
7. If you have still an issue , try this based out of note :338545.1
Make sure you have checked step 1,2,3.
Login to AP responsibility and try exporting from the data from Invoice workbench after search for invoice batch.
Are you able to export?
If not ,Go to (Tool Bar) Help -> Diagnostics -> Examine
Set Block = ENVIRONMENT
Set Field = FND_EXPORT_DEBUG
Set Value = TRUE
Export and observe the messages that are generated during the export process.
What is the last pop up message ?
Try to run the following piece of code in SQLPLUS*
declare
db_file number;
mime_type varchar2(255) :='text/plain' ;
out_string varchar2(32767) :='Just some plain text that is stored' ;
web_server_prefix varchar2(500);
url varchar2(500);
begin
fnd_global.apps_initialize(user_id =>
DBMS_SESSION.SET_NLS('NLS_LANGUAGE','AMERICAN');
db_file :=fnd_gfm.file_create(content_type =>mime_type,program_name=> 'export');
fnd_gfm.file_write_line(db_file,out_string);
db_file :=fnd_gfm.file_close(db_file);
url:=fnd_gfm.construct_download_url(fnd_web_config.gfm_agent,db_file,TRUE);
dbms_output.put_line(url);
end;
If still not able to fix, ask Oracle for further resolution.
8.If you are not able to 100% export in excel do a quick check
Run this query immediatly after Export get completed
select count(*) from fnd_lobs where program_name='export';
If counts not matches then you need to recreate the index using script $FND_TOP/sql/aflobbld.sql. This is because the File/Export functionality of EBS uses system LOB columns to store temp data before it is outputed to a browser. If that temp data becomes large enough as the result of large export query set, then the max_extents limitation of the column is reached thereby causing the export to fail.
Most of my user experinces these issues with WIN2K or XP SP1 and SP2, but not with SP3.No clue for me with service pack.
Is there any linkage with window XP or 2000 .Net Framework installation and Service pack in client desktop for this feature? Do share some thoughts if anyone got such kind of issue in past.
Sunday, January 3, 2010
Canceling Long Running Queries in Oracle Applications 11i
Oracle strongly recommends that customers wishing to use the Cancel Query feature upgrade to Developer 6i patch 14 (Forms version 6.0.8.23.x) or later.
Customers using Developer 6i patch 13 (Forms version 6.0.8.22.x) must apply patch 2974236 to use all the cancel query features outlined in this document.
Oracle Applications does not support the Cancel Query feature for releases 10SC, 10.7NCA and 11.0 .
For information on the Cancel Query feature with earlier patch set releases, please see Metalink Note 241012.1 titled 'Canceling Long Running Queries in Earlier Versions of Oracle Applications 11i'
Canceling Long-Running Queries (Overview)
On occasion, a user may start a long-running query, grow tired of waiting, and close the browser. This section considers the implications of such an action, and describes a new feature that can help in this situation.
The middle-tier Forms Server process is basically single-threaded, and queries are normally executed in Blocking mode. If the client is killed, the Forms Server process still waits for the query to complete. This means that the Forms Server process will only time out when control has been returned from the database, at which point it will terminate when it can no longer communicate with the PC client. Cleanup will also take place for the database session.
The same principle applies in the case of a long-running transaction that calls a stored procedure from client-side PL/SQL. The key point is that in all such cases the query must run to completion before the middle tier process terminates and cleanup takes place.
If the query takes longer than about 5 seconds, a dialog box will appear that allows the user to cancel the query by clicking a Cancel button. This Cancel Query feature (sometimes called Non-blocking mode, as it is based on SQL*Net non-blocking functionality) is the only way to terminate a query without killing the Forms session.
This capability only applies to queries issued by form blocks or LOVs. It will not work for transactions such as stored procedure calls or queries executed from PL/SQL.
If you are using Oracle Applications 11i Release 7 (11.5.7) or later, or are using FND mini-pack D or later you must set the profile option FND:ENABLE_CANCEL_QUERY to Yes in order to enable this feature. This can be set at site, application, responsibility or user level. Setting this profile to Yes also adds a call to set_form_property in the standard APPCORE when-new-form-instance event, which sets the INTERACTION_MODE property to NON_BLOCKING.
If you are using Oracle Applications 11i prior to Release 7 (11.5.7) and FND mini-pack prior to D, you must apply patch 1675290, and follow the instructions in its readme file to be able use this feature.
Actions that will cause the dialog to appear:
For any block populated via a query issued by Oracle Forms directly, the dialog will appear if the time to fetch the records for the query exceeds the threshold.
When a query is run against a block that is part of a master-detail relation, the dialog may appear and disappear several times if multiple detail blocks are involved.
For any LOV which is based on a SQL statement, if the LOV is explicitly invoked by the user with an action such as Edit List of Values, the dialog will appear if the time to execute and fetch all records for the query exceeds the threshold.
With an operation that fetches many records, such as Go-Last Record or Action-Export, the records are fetched in batches of multiple rows at a time. The dialog will appear if the time to fetch all the records exceeds the threshold.
Queries issued from Find Windows will cause the dialog to appear if the threshold time is exceeded.
Actions that will not cause the dialog to appear:
Spreadtable blocks that are predominantly used by the CRM suite of products will not activate the Cancel Query feature. Java blocks, Java Beans and PLSQL queries that are not activated by forms will not activate this feature.
For a query run against a block that is part of a master-detail relation, the dialog will only appear if the time to populate a particular block exceeds the threshold.
LOVs that open automatically as a result of validation errors or non-unique entries.
Any LOV that has been coded as a user exit, including items such as concurrent request parameter LOVs or Flexfield segment LOVs. Oracle Applications user-exits call private Forms API’s to display an array built by the user-exit in a Forms LOV window. As the query is executed by the user-exit, Forms runtime has no control over it, so is unable to cancel it.
Any form processing involved with creating, committing, updating, deleting a record.
--------------------------------------------------------------------------------
Tuning Cancel Query
You must be on Forms 6i patchset 14 (6.0.8.23.x) or Forms 6i patchset 13 (6.0.8.22.x) with patch 2974236 applied, for all the options described below to be available. As cancel query introduces additional overheads you may wish to tune it to minimize any additional resource requirements.
As Cancel Query is enabled in Oracle Applications 11i by setting the profile FND_ENABLE_CANCEL_QUERY at the appropriate level, some customers may wish to enable cancel query for power users only due to the additional overheads. In this case the profile should be set at User level for these users only.
Cancel Query makes use of OCI level non-blocking queries. The middle tier Forms server process starts the query in non-blocking mode, then unlike a normal blocking query, control is returned to the forms code.
While the query is running, Forms polls the OCI layer until the query completes and records are returned. As well as polling to see if the database has finished executing the query, the forms server process also polls the Forms Java client to initially display the cancel query dialog, and then detect if the cancel button has been pressed. If the cancel button has been pressed by the end user, Forms will call the OCI function to terminate the query. At the time the query is terminated, some records may have already been fetched, so an incomplete record set may be returned in some cases.
While the query is running in non-blocking mode, the user is unable to do anything in the current forms UI except cancel the query. They cannot work in another form if they have multiple forms open.
Cancel query obviously imposes an additional overhead, as it needs to poll the database to see if the query has completed, and also poll the client to see if the user has cancelled it. You may experience the following issues -
Extra round trips between the forms server and the client. The Forms server polls the client once every second after the first 2 seconds by default. System Administrators can set environment variables to change the defaults. See below.
User Wait Time (i.e. the time the user has to wait for the query to complete and control returned to the user) may increase, particularly for queries which fetch a lot of records. Note that by default queries on Forms blocks only fetch enough records to display the first screen full, and fetch additional records as you scroll through. Therefore a query which potentially returns a million rows will not cause a problem because forms is smart enough to only fetch the first few rows. An LOV always fetches all records selected by the query.
CPU on the middle tier will increase. CPU will also increase on the client but that isn't normally a problem, as the client PC is not normally running anywhere near capacity. Middle tier CPU is more important. In cancel query we have to strike a balance between CPU usage on the middle tier and User Wait Time. Polling the database more often increases CPU usage, but reduces user wait time, particularly for queries which fetch a lot of records. The database polling interval is configurable - see The Database Polling Interval section below.
The following environment variables can be set to tune Cancel Query. Guidance is provided, but in some cases the optimal value is yet to be established, hence the reason for making everything configurable;
--------------------------------------------------------------------------------
Tuning round trips between the Middle Tier and the Client
Changes to the environment variables below will only affect Forms Server Listener implementations. Forms Servlet Listener users should go to the Forms Servlet Listener section below.
Minimizing network round trips between the middle tier and client is important, particularly if the bandwidth is limited, or the end user in on a WAN. Three environment variables have been introduced to tune the client round trips. Despite the names of the variables, they now control the round trips for all queries. The environment variables need to be set when the Forms server is started. They apply to all users with cancel query enabled. The defaults are set slightly differently for Oracle Applications users of the Forms runtime.
Initial Delay (FORMS60_LOV_INITIAL):
Specify the time in milliseconds
Default 1000 (2000 for Oracle Applications)
Minimum Polling Period (FORMS60_LOV_MINIMUM):
Specify the time in milliseconds.
Default 500 (1000 for Oracle Applications)
Network Latency Weighting (FORMS60_LOV_WEIGHT):
Specify the time in milliseconds.
Default 0 (16 for Oracle Applications)
FORMS60_LOV_INITIAL
This can be set between 1000 and 32000, and is the value in Milliseconds before the cancel query dialog initially appears. By setting this to 32000, it means the cancel query dialog will not appear until after 32 seconds have passed, and more importantly, no round trips will be generated between the Forms server and the client until after 32 seconds. If you set it to 32000, in most cases no extra network traffic will be generated by cancel query, except in the case of queries which really are candidates for being cancelled. range is 1000 to 32000. 5000 or 10000 would be a good starting point. Set to 32000 if your network is slow or heavily loaded.
The default value for Oracle Applications is set to 5000 milliseconds.
Autoconfig Users
Autoconfig users with TXK (FND) Autoconfig Template Roll Up M ( Patch 4709948 ) or later, should update this value through Autoconfig. If you are using an earlier version, you may also apply patch 3563654 to obtain this functionality.
You may alter the value for the FORMS60_LOV_INTIAL parameter as follows;
Login to 'Oracle Applications Manager'
Select 'Site Map' -> 'Monitoring' Tab -> 'System Configuration' -> 'Applications Context'
Click on 'Edit Parameters' for the 'Applications' tier.
Select 'OA_VAR' from the 'Search' drop down menu, enter 's_f60lov' in the adjacent field and press the 'Go' button.
Alter the value for the 'forms 60 LOV initial' parameter and press the 'Save' button.
Non-Autoconfig Users
To alter the value for this environment variable please follow the steps below:-
On Unix:
Stop the Forms Server process.
Update the 'Oracle Forms 6.0 environment variables' section of your '$APPL_TOP/< SID >.env' file with; (Using 2000 milliseconds as an example)
FORMS60_LOV_INITIAL="10000"
export FORMS60_LOV_INITIAL
Source the environment, then restart the Forms Server process.
On Windows:
Stop the Forms Server process.
Update the 'Oracle Forms 6.0 environment variables' section of your '$APPL_TOP/< SID >.cmd' file with; (Using 10 seconds (10000 milliseconds) as an example)
FORMS60_LOV_INITIAL=10000
Source the environment, then restart the Forms Server process.
FORMS60_LOV_MINIMUM
This environment variable can be set to anything between 1000 and 32000 and is the value in Milliseconds between subsequent polling of the client from the middle tier. The more often you poll, the faster the query will be cancelled when you press the button, but at the expense of more network round trips. Although you can only set one value for all users, but you can set FORMS60_LOV_WEIGHT to automatically vary the time between polling based on network latency. The suggested range for FORMS60_LOV_MINIMUM is 1000 to 5000.
The default value for Oracle Applications is set to 1000 milliseconds.
Autoconfig Users
Autoconfig users with TXK (FND) Autoconfig Template Roll Up M ( Patch 4709948 ) or later, should update this value through Autoconfig. If you are using an earlier version, you may also apply patch 3563654 to obtain this functionality.
You may alter the value for the FORMS60_LOV_MINIMUM parameter as follows;
Login to 'Oracle Applications Manager'
Select 'Site Map' -> 'Monitoring' Tab -> 'System Configuration' -> 'Applications Context'
Click on 'Edit Parameters' for the 'Applications' tier.
Select 'OA_VAR' from the 'Search' drop down menu, enter 's_f60lovminimum' in the adjacent field and press the 'Go' button.
Alter the value for the 'forms 60 LOV minimum' parameter and press the 'Save' button.
Non-Autoconfig Users
To alter the value for this environment variable please follow the steps below:-
On Unix:
Stop the Forms Server process.
Update the 'Oracle Forms 6.0 environment variables' section of your '$APPL_TOP/< SID >.env' file with; (Using 2000 milliseconds as an example)
FORMS60_LOV_MINIMUM="2000"
export FORMS60_LOV_MINIMUM
Source the environment, then restart the Forms Server process.
On Windows:
Stop the Forms Server process.
Update the 'Oracle Forms 6.0 environment variables' section of your '$APPL_TOP/< SID >.cmd' file with; (Using 2000 milliseconds as an example)
FORMS60_LOV_MINIMUM=2000
Source the environment, then restart the Forms Server process.
FORMS60_LOV_WEIGHT
This environment variable can be set between 0 and 32000. It defines a weighting factor, which takes account of the actual network latency to automatically adjust the polling period. You may have some users on the LAN with low latency, and other users on the WAN with high latency. All users may want to use cancel query, but the extra round trips will be more of a problem for the WAN, so this allows you to automatically decrease the frequency of the network round trips for users on slow networks and is utilized in the following equation;
x = minimum period (from FORMS60_LOV_MINIMUM) + weighting factor (from FORMS60_LOV_WEIGHT) * avg roundtrip time
Initially, there will be a certain amount of trial and error involved in finding the optimal value for this variable. We would suggest starting with the default value of 16, then increase/decrease by small amounts until the desired performance is obtained. If you do not wish to alter the interval then this value should be set to 0 (zero).
The default value for Oracle Applications is set to 16.
Autoconfig Users
Autoconfig users with TXK (FND) Autoconfig Template Roll Up M ( Patch 4709948 ) or later, should update this value through Autoconfig. If you are using an earlier version, you may also apply patch 3563654 to obtain this functionality.
You may alter the value for the FORMS60_LOV_WEIGHT parameter as follows;
Login to 'Oracle Applications Manager'
Select 'Site Map' -> 'Monitoring' Tab -> 'System Configuration' -> 'Applications Context'
Click on 'Edit Parameters' for the 'Applications' tier.
Select 'OA_VAR' from the 'Search' drop down menu, enter 's_f60lovweight' in the adjacent field and press the 'Go' button.
Alter the value for the 'forms 60 LOV weight' parameter and press the 'Save' button.
Non-Autoconfig Users
To alter the value for this environment variable please follow the steps below:-
On Unix:
Stop the Forms Server process.
Update the 'Oracle Forms 6.0 environment variables' section of your '$APPL_TOP/< SID >.env' file with; (Using 0 as an example)
FORMS60_LOV_WEIGHT="0"
export FORMS60_LOV_WEIGHT
Source the environment, then restart the Forms Server process.
On Windows:
Stop the Forms Server process.
Update the 'Oracle Forms 6.0 environment variables' section of your '$APPL_TOP/< SID >.cmd' file with; (Using 0 as an example)
FORMS60_LOV_WEIGHT=0
Source the environment, then restart the Forms Server process.
--------------------------------------------------------------------------------
Cancel Query with Long List LOV's
Longlist LOV's are a special case and do not use OCI non-blocking calls - it actually uses blocking calls at OCI level. Cancel query functionality is implemented between fetches, therefore it is only possible to cancel when a fetch completes and control is returned to the forms runtime internal code by the OCI layer. You cannot cancel if the initial execute and fetch takes an excessive time, or a subsequent fetch takes an excessive time.
In general longlist LOV queries should be tuned to perform adequately by Oracle development and the only time you need to cancel is when you enter inappropriate reduction criteria resulting in a long delay due to too many rows getting returned. In this case cancel query should work well.
FORMS60_BLOCKING_LONGLIST
This environment variable can be used to turn the cancel query feature on or off for Longlist LOV's. The default value is ‘FALSE’, which means cancel query is enabled for Longlist LOV's.
There is little reason to turn off cancel query for longlist LOV's. As the queries are always executed in blocking mode you will not save middle tier CPU. If network bandwidth is a real problem, but you still want cancel query for block queries you could turn it off.
The default value for Oracle Applications is set to FALSE.
Autoconfig Users
Autoconfig users with TXK (FND) Autoconfig Template Roll Up M ( Patch 4709948 ) or later, should update this value through Autoconfig. If you are using an earlier version, you may also apply patch 3563654 to obtain this functionality.
You may disable the cancel query functionality for Longlist LOV's as follows;
Login to 'Oracle Applications Manager'
Select 'Site Map' -> 'Monitoring' Tab -> 'System Configuration' -> 'Applications Context'
Click on 'Edit Parameters' for the 'Applications' tier.
Select 'OA_VAR' from the 'Search' drop down menu, enter 's_f60blocklist' in the adjacent field and press the 'Go' button.
Alter the value for the 'form 60 blocking longlist' parameter and press the 'Save' button.
Non-Autoconfig Users
To disable cancel query for Longlist LOV's, please follow the steps below:-
On Unix:
Stop the Forms Server process.
Update the 'Oracle Forms 6.0 environment variables' section of your '$APPL_TOP/< SID >.env' file with;
FORMS60_BLOCKING_LONGLIST="TRUE"
export FORMS60_BLOCKING_LONGLIST
Source the environment, then restart the Forms Server process.
On Windows:
Stop the Forms Server process.
Update the 'Oracle Forms 6.0 environment variables' section of your '$APPL_TOP/< SID >.cmd' file with;
FORMS60_BLOCKING_LONGLIST=TRUE
Source the environment, then restart the Forms Server process.
--------------------------------------------------------------------------------
Cancel Query with Standard LOV's
In theory cancel query should never need to be activated for standard LOV's. As there is no way to modify the query and re-execute it, any long running query is a performance problem, which needs to be fixed by Oracle Applications Development. Cancel query will not solve the underlying problem. Therefore from Forms patchset 14 we run all standard LOV's in blocking mode and offset some of the increase in middle tier CPU due to cancel query, with minimal loss of functionality. It will also reduce network round trips, but in general standard LOV queries will be quick, and will have completed before any client process round trips are generated.
FORMS60_ENABLE_LOV_CANCEL_QUERY
This environment variable is set to FALSE, which means cancel query is disabled for Standard LOV's (all standard LOV's will run in blocking mode). This parameter should not be changed.
--------------------------------------------------------------------------------
The Database Polling Interval
A new database polling algorithm is introduced in Forms patchset14, which should help to reduce the additional middle tier CPU used by cancel query. The new polling algorithm should use less CPU than all previous implementations.
By default we now poll every 100 milliseconds to check whether the query has completed, with sleeps in between. The more often we poll, the more CPU it uses. However if we don’t poll often enough, we will experience increases in user wait time, and in particular queries which fetch large numbers of rows, or forms which repopulate multiple detail blocks.
This is the reason we decided to disable cancel query for standard LOV’s by default (see above), as standard LOV’s which returned 1000+ records were affected most when we increased the polling interval. As we couldn't really see a need for cancel query on a standard LOV, it made more sense to disable it so we could run with a higher polling interval and reduce the middle tier CPU overhead.
FORMS60_NONBLOCKING_SLEEP
This variable allows you to alter the sleep period as required. For example, if this is set to "200" you will get a sleep period of 200 milliseconds between polling. This should be set as high as possible without impacting user-wait time. It’s not clear what the optimal value is at this time, hence the reason we made it configurable, but either 100 milliseconds (the default) or 200 milliseconds would be a good starting point. The optimal value probably be somewhere between 100 milliseconds and 400 milliseconds - any higher and it would probably start to impact user wait time.
The default value for Oracle Applications is set at 100 milliseconds
Autoconfig Users
Autoconfig users with TXK (FND) Autoconfig Template Roll Up M ( Patch 4709948 ) or later, should update this value through Autoconfig. If you are using an earlier version, you may also apply patch 3563654 to obtain this functionality.
You may disable the cancel query functionality for Longlist LOV's as follows;
Login to 'Oracle Applications Manager'
Select 'Site Map' -> 'Monitoring' Tab -> 'System Configuration' -> 'Applications Context'
Click on 'Edit Parameters' for the 'Applications' tier.
Select 'OA_VAR' from the 'Search' drop down menu, enter ' s_f60nonblockingsleep' in the adjacent field and press the 'Go' button.
Alter the value for the 'forms 60 non-blocking sleep' parameter and press the 'Save' button.
Non-Autoconfig Users
To alter the value for this environment variable please follow the steps below:-
On Unix:
Stop the Forms Server process.
Update the 'Oracle Forms 6.0 environment variables' section of your '$APPL_TOP/< SID >.env' file with; (Using 200 milliseconds as an example)
FORMS60_NONBLOCKING_SLEEP="200"
export FORMS60_NONBLOCKING_SLEEP
Source the environment, then restart the Forms Server process.
On Windows:
Stop the Forms Server process.
Update the 'Oracle Forms 6.0 environment variables' section of your '$APPL_TOP/< SID >.cmd' file with; (Using 200 milliseconds as an example)
FORMS60_NONBLOCKING_SLEEP=200
Source the environment, then restart the Forms Server process.
--------------------------------------------------------------------------------
Forms Listener Servlet
If you are using the Forms Listener Servlet, then MaxBlockTime defined in formservlet.properties must be set to a value larger than the maximum cancel query polling interval. For example if;
FORMS60_LOV_INITIAL=10000
FORMS60_LOV_MINIMUM=20000
FORMS60_LOV_WEIGHT=0
Then set MaxBlockTime=25000 or higher.
If you set FORMS60_LOV_WEIGHT to a non-zero value, you will have to calculate the maximum polling interval based on the highest network latency. Set MaxBlockTime at least 5000ms higher than the maximum polling interval.
Failure to set MaxBlockTime to a value larger than the maximum polling interval will result in a loss of performance. If it's undefined (as in older versions of the configuration files), it defaults to 1000ms.
For the Forms Listener Servlet, the FORMS60_* environment variables are defined in the configuration file formservlet.ini
For further information on Forms Listener Servlet, see MetaLink Note id 201340.1 titled, 'Using Forms Listener Servlet with Oracle Applications 11i'.
--------------------------------------------------------------------------------
Open Enhancements (For Consideration)
Bug 823698 - Customize text/size of Cancel Query dialog and error message when query is cancelled
Bug 974350 - Mouse pointer flickers during non-blocking query on a Block/List of Values
Customers using Developer 6i patch 13 (Forms version 6.0.8.22.x) must apply patch 2974236 to use all the cancel query features outlined in this document.
Oracle Applications does not support the Cancel Query feature for releases 10SC, 10.7NCA and 11.0 .
For information on the Cancel Query feature with earlier patch set releases, please see Metalink Note 241012.1 titled 'Canceling Long Running Queries in Earlier Versions of Oracle Applications 11i'
Canceling Long-Running Queries (Overview)
On occasion, a user may start a long-running query, grow tired of waiting, and close the browser. This section considers the implications of such an action, and describes a new feature that can help in this situation.
The middle-tier Forms Server process is basically single-threaded, and queries are normally executed in Blocking mode. If the client is killed, the Forms Server process still waits for the query to complete. This means that the Forms Server process will only time out when control has been returned from the database, at which point it will terminate when it can no longer communicate with the PC client. Cleanup will also take place for the database session.
The same principle applies in the case of a long-running transaction that calls a stored procedure from client-side PL/SQL. The key point is that in all such cases the query must run to completion before the middle tier process terminates and cleanup takes place.
If the query takes longer than about 5 seconds, a dialog box will appear that allows the user to cancel the query by clicking a Cancel button. This Cancel Query feature (sometimes called Non-blocking mode, as it is based on SQL*Net non-blocking functionality) is the only way to terminate a query without killing the Forms session.
This capability only applies to queries issued by form blocks or LOVs. It will not work for transactions such as stored procedure calls or queries executed from PL/SQL.
If you are using Oracle Applications 11i Release 7 (11.5.7) or later, or are using FND mini-pack D or later you must set the profile option FND:ENABLE_CANCEL_QUERY to Yes in order to enable this feature. This can be set at site, application, responsibility or user level. Setting this profile to Yes also adds a call to set_form_property in the standard APPCORE when-new-form-instance event, which sets the INTERACTION_MODE property to NON_BLOCKING.
If you are using Oracle Applications 11i prior to Release 7 (11.5.7) and FND mini-pack prior to D, you must apply patch 1675290, and follow the instructions in its readme file to be able use this feature.
Actions that will cause the dialog to appear:
For any block populated via a query issued by Oracle Forms directly, the dialog will appear if the time to fetch the records for the query exceeds the threshold.
When a query is run against a block that is part of a master-detail relation, the dialog may appear and disappear several times if multiple detail blocks are involved.
For any LOV which is based on a SQL statement, if the LOV is explicitly invoked by the user with an action such as Edit List of Values, the dialog will appear if the time to execute and fetch all records for the query exceeds the threshold.
With an operation that fetches many records, such as Go-Last Record or Action-Export, the records are fetched in batches of multiple rows at a time. The dialog will appear if the time to fetch all the records exceeds the threshold.
Queries issued from Find Windows will cause the dialog to appear if the threshold time is exceeded.
Actions that will not cause the dialog to appear:
Spreadtable blocks that are predominantly used by the CRM suite of products will not activate the Cancel Query feature. Java blocks, Java Beans and PLSQL queries that are not activated by forms will not activate this feature.
For a query run against a block that is part of a master-detail relation, the dialog will only appear if the time to populate a particular block exceeds the threshold.
LOVs that open automatically as a result of validation errors or non-unique entries.
Any LOV that has been coded as a user exit, including items such as concurrent request parameter LOVs or Flexfield segment LOVs. Oracle Applications user-exits call private Forms API’s to display an array built by the user-exit in a Forms LOV window. As the query is executed by the user-exit, Forms runtime has no control over it, so is unable to cancel it.
Any form processing involved with creating, committing, updating, deleting a record.
--------------------------------------------------------------------------------
Tuning Cancel Query
You must be on Forms 6i patchset 14 (6.0.8.23.x) or Forms 6i patchset 13 (6.0.8.22.x) with patch 2974236 applied, for all the options described below to be available. As cancel query introduces additional overheads you may wish to tune it to minimize any additional resource requirements.
As Cancel Query is enabled in Oracle Applications 11i by setting the profile FND_ENABLE_CANCEL_QUERY at the appropriate level, some customers may wish to enable cancel query for power users only due to the additional overheads. In this case the profile should be set at User level for these users only.
Cancel Query makes use of OCI level non-blocking queries. The middle tier Forms server process starts the query in non-blocking mode, then unlike a normal blocking query, control is returned to the forms code.
While the query is running, Forms polls the OCI layer until the query completes and records are returned. As well as polling to see if the database has finished executing the query, the forms server process also polls the Forms Java client to initially display the cancel query dialog, and then detect if the cancel button has been pressed. If the cancel button has been pressed by the end user, Forms will call the OCI function to terminate the query. At the time the query is terminated, some records may have already been fetched, so an incomplete record set may be returned in some cases.
While the query is running in non-blocking mode, the user is unable to do anything in the current forms UI except cancel the query. They cannot work in another form if they have multiple forms open.
Cancel query obviously imposes an additional overhead, as it needs to poll the database to see if the query has completed, and also poll the client to see if the user has cancelled it. You may experience the following issues -
Extra round trips between the forms server and the client. The Forms server polls the client once every second after the first 2 seconds by default. System Administrators can set environment variables to change the defaults. See below.
User Wait Time (i.e. the time the user has to wait for the query to complete and control returned to the user) may increase, particularly for queries which fetch a lot of records. Note that by default queries on Forms blocks only fetch enough records to display the first screen full, and fetch additional records as you scroll through. Therefore a query which potentially returns a million rows will not cause a problem because forms is smart enough to only fetch the first few rows. An LOV always fetches all records selected by the query.
CPU on the middle tier will increase. CPU will also increase on the client but that isn't normally a problem, as the client PC is not normally running anywhere near capacity. Middle tier CPU is more important. In cancel query we have to strike a balance between CPU usage on the middle tier and User Wait Time. Polling the database more often increases CPU usage, but reduces user wait time, particularly for queries which fetch a lot of records. The database polling interval is configurable - see The Database Polling Interval section below.
The following environment variables can be set to tune Cancel Query. Guidance is provided, but in some cases the optimal value is yet to be established, hence the reason for making everything configurable;
--------------------------------------------------------------------------------
Tuning round trips between the Middle Tier and the Client
Changes to the environment variables below will only affect Forms Server Listener implementations. Forms Servlet Listener users should go to the Forms Servlet Listener section below.
Minimizing network round trips between the middle tier and client is important, particularly if the bandwidth is limited, or the end user in on a WAN. Three environment variables have been introduced to tune the client round trips. Despite the names of the variables, they now control the round trips for all queries. The environment variables need to be set when the Forms server is started. They apply to all users with cancel query enabled. The defaults are set slightly differently for Oracle Applications users of the Forms runtime.
Initial Delay (FORMS60_LOV_INITIAL):
Specify the time in milliseconds
Default 1000 (2000 for Oracle Applications)
Minimum Polling Period (FORMS60_LOV_MINIMUM):
Specify the time in milliseconds.
Default 500 (1000 for Oracle Applications)
Network Latency Weighting (FORMS60_LOV_WEIGHT):
Specify the time in milliseconds.
Default 0 (16 for Oracle Applications)
FORMS60_LOV_INITIAL
This can be set between 1000 and 32000, and is the value in Milliseconds before the cancel query dialog initially appears. By setting this to 32000, it means the cancel query dialog will not appear until after 32 seconds have passed, and more importantly, no round trips will be generated between the Forms server and the client until after 32 seconds. If you set it to 32000, in most cases no extra network traffic will be generated by cancel query, except in the case of queries which really are candidates for being cancelled. range is 1000 to 32000. 5000 or 10000 would be a good starting point. Set to 32000 if your network is slow or heavily loaded.
The default value for Oracle Applications is set to 5000 milliseconds.
Autoconfig Users
Autoconfig users with TXK (FND) Autoconfig Template Roll Up M ( Patch 4709948 ) or later, should update this value through Autoconfig. If you are using an earlier version, you may also apply patch 3563654 to obtain this functionality.
You may alter the value for the FORMS60_LOV_INTIAL parameter as follows;
Login to 'Oracle Applications Manager'
Select 'Site Map' -> 'Monitoring' Tab -> 'System Configuration' -> 'Applications Context'
Click on 'Edit Parameters' for the 'Applications' tier.
Select 'OA_VAR' from the 'Search' drop down menu, enter 's_f60lov' in the adjacent field and press the 'Go' button.
Alter the value for the 'forms 60 LOV initial' parameter and press the 'Save' button.
Non-Autoconfig Users
To alter the value for this environment variable please follow the steps below:-
On Unix:
Stop the Forms Server process.
Update the 'Oracle Forms 6.0 environment variables' section of your '$APPL_TOP/< SID >.env' file with; (Using 2000 milliseconds as an example)
FORMS60_LOV_INITIAL="10000"
export FORMS60_LOV_INITIAL
Source the environment, then restart the Forms Server process.
On Windows:
Stop the Forms Server process.
Update the 'Oracle Forms 6.0 environment variables' section of your '$APPL_TOP/< SID >.cmd' file with; (Using 10 seconds (10000 milliseconds) as an example)
FORMS60_LOV_INITIAL=10000
Source the environment, then restart the Forms Server process.
FORMS60_LOV_MINIMUM
This environment variable can be set to anything between 1000 and 32000 and is the value in Milliseconds between subsequent polling of the client from the middle tier. The more often you poll, the faster the query will be cancelled when you press the button, but at the expense of more network round trips. Although you can only set one value for all users, but you can set FORMS60_LOV_WEIGHT to automatically vary the time between polling based on network latency. The suggested range for FORMS60_LOV_MINIMUM is 1000 to 5000.
The default value for Oracle Applications is set to 1000 milliseconds.
Autoconfig Users
Autoconfig users with TXK (FND) Autoconfig Template Roll Up M ( Patch 4709948 ) or later, should update this value through Autoconfig. If you are using an earlier version, you may also apply patch 3563654 to obtain this functionality.
You may alter the value for the FORMS60_LOV_MINIMUM parameter as follows;
Login to 'Oracle Applications Manager'
Select 'Site Map' -> 'Monitoring' Tab -> 'System Configuration' -> 'Applications Context'
Click on 'Edit Parameters' for the 'Applications' tier.
Select 'OA_VAR' from the 'Search' drop down menu, enter 's_f60lovminimum' in the adjacent field and press the 'Go' button.
Alter the value for the 'forms 60 LOV minimum' parameter and press the 'Save' button.
Non-Autoconfig Users
To alter the value for this environment variable please follow the steps below:-
On Unix:
Stop the Forms Server process.
Update the 'Oracle Forms 6.0 environment variables' section of your '$APPL_TOP/< SID >.env' file with; (Using 2000 milliseconds as an example)
FORMS60_LOV_MINIMUM="2000"
export FORMS60_LOV_MINIMUM
Source the environment, then restart the Forms Server process.
On Windows:
Stop the Forms Server process.
Update the 'Oracle Forms 6.0 environment variables' section of your '$APPL_TOP/< SID >.cmd' file with; (Using 2000 milliseconds as an example)
FORMS60_LOV_MINIMUM=2000
Source the environment, then restart the Forms Server process.
FORMS60_LOV_WEIGHT
This environment variable can be set between 0 and 32000. It defines a weighting factor, which takes account of the actual network latency to automatically adjust the polling period. You may have some users on the LAN with low latency, and other users on the WAN with high latency. All users may want to use cancel query, but the extra round trips will be more of a problem for the WAN, so this allows you to automatically decrease the frequency of the network round trips for users on slow networks and is utilized in the following equation;
x = minimum period (from FORMS60_LOV_MINIMUM) + weighting factor (from FORMS60_LOV_WEIGHT) * avg roundtrip time
Initially, there will be a certain amount of trial and error involved in finding the optimal value for this variable. We would suggest starting with the default value of 16, then increase/decrease by small amounts until the desired performance is obtained. If you do not wish to alter the interval then this value should be set to 0 (zero).
The default value for Oracle Applications is set to 16.
Autoconfig Users
Autoconfig users with TXK (FND) Autoconfig Template Roll Up M ( Patch 4709948 ) or later, should update this value through Autoconfig. If you are using an earlier version, you may also apply patch 3563654 to obtain this functionality.
You may alter the value for the FORMS60_LOV_WEIGHT parameter as follows;
Login to 'Oracle Applications Manager'
Select 'Site Map' -> 'Monitoring' Tab -> 'System Configuration' -> 'Applications Context'
Click on 'Edit Parameters' for the 'Applications' tier.
Select 'OA_VAR' from the 'Search' drop down menu, enter 's_f60lovweight' in the adjacent field and press the 'Go' button.
Alter the value for the 'forms 60 LOV weight' parameter and press the 'Save' button.
Non-Autoconfig Users
To alter the value for this environment variable please follow the steps below:-
On Unix:
Stop the Forms Server process.
Update the 'Oracle Forms 6.0 environment variables' section of your '$APPL_TOP/< SID >.env' file with; (Using 0 as an example)
FORMS60_LOV_WEIGHT="0"
export FORMS60_LOV_WEIGHT
Source the environment, then restart the Forms Server process.
On Windows:
Stop the Forms Server process.
Update the 'Oracle Forms 6.0 environment variables' section of your '$APPL_TOP/< SID >.cmd' file with; (Using 0 as an example)
FORMS60_LOV_WEIGHT=0
Source the environment, then restart the Forms Server process.
--------------------------------------------------------------------------------
Cancel Query with Long List LOV's
Longlist LOV's are a special case and do not use OCI non-blocking calls - it actually uses blocking calls at OCI level. Cancel query functionality is implemented between fetches, therefore it is only possible to cancel when a fetch completes and control is returned to the forms runtime internal code by the OCI layer. You cannot cancel if the initial execute and fetch takes an excessive time, or a subsequent fetch takes an excessive time.
In general longlist LOV queries should be tuned to perform adequately by Oracle development and the only time you need to cancel is when you enter inappropriate reduction criteria resulting in a long delay due to too many rows getting returned. In this case cancel query should work well.
FORMS60_BLOCKING_LONGLIST
This environment variable can be used to turn the cancel query feature on or off for Longlist LOV's. The default value is ‘FALSE’, which means cancel query is enabled for Longlist LOV's.
There is little reason to turn off cancel query for longlist LOV's. As the queries are always executed in blocking mode you will not save middle tier CPU. If network bandwidth is a real problem, but you still want cancel query for block queries you could turn it off.
The default value for Oracle Applications is set to FALSE.
Autoconfig Users
Autoconfig users with TXK (FND) Autoconfig Template Roll Up M ( Patch 4709948 ) or later, should update this value through Autoconfig. If you are using an earlier version, you may also apply patch 3563654 to obtain this functionality.
You may disable the cancel query functionality for Longlist LOV's as follows;
Login to 'Oracle Applications Manager'
Select 'Site Map' -> 'Monitoring' Tab -> 'System Configuration' -> 'Applications Context'
Click on 'Edit Parameters' for the 'Applications' tier.
Select 'OA_VAR' from the 'Search' drop down menu, enter 's_f60blocklist' in the adjacent field and press the 'Go' button.
Alter the value for the 'form 60 blocking longlist' parameter and press the 'Save' button.
Non-Autoconfig Users
To disable cancel query for Longlist LOV's, please follow the steps below:-
On Unix:
Stop the Forms Server process.
Update the 'Oracle Forms 6.0 environment variables' section of your '$APPL_TOP/< SID >.env' file with;
FORMS60_BLOCKING_LONGLIST="TRUE"
export FORMS60_BLOCKING_LONGLIST
Source the environment, then restart the Forms Server process.
On Windows:
Stop the Forms Server process.
Update the 'Oracle Forms 6.0 environment variables' section of your '$APPL_TOP/< SID >.cmd' file with;
FORMS60_BLOCKING_LONGLIST=TRUE
Source the environment, then restart the Forms Server process.
--------------------------------------------------------------------------------
Cancel Query with Standard LOV's
In theory cancel query should never need to be activated for standard LOV's. As there is no way to modify the query and re-execute it, any long running query is a performance problem, which needs to be fixed by Oracle Applications Development. Cancel query will not solve the underlying problem. Therefore from Forms patchset 14 we run all standard LOV's in blocking mode and offset some of the increase in middle tier CPU due to cancel query, with minimal loss of functionality. It will also reduce network round trips, but in general standard LOV queries will be quick, and will have completed before any client process round trips are generated.
FORMS60_ENABLE_LOV_CANCEL_QUERY
This environment variable is set to FALSE, which means cancel query is disabled for Standard LOV's (all standard LOV's will run in blocking mode). This parameter should not be changed.
--------------------------------------------------------------------------------
The Database Polling Interval
A new database polling algorithm is introduced in Forms patchset14, which should help to reduce the additional middle tier CPU used by cancel query. The new polling algorithm should use less CPU than all previous implementations.
By default we now poll every 100 milliseconds to check whether the query has completed, with sleeps in between. The more often we poll, the more CPU it uses. However if we don’t poll often enough, we will experience increases in user wait time, and in particular queries which fetch large numbers of rows, or forms which repopulate multiple detail blocks.
This is the reason we decided to disable cancel query for standard LOV’s by default (see above), as standard LOV’s which returned 1000+ records were affected most when we increased the polling interval. As we couldn't really see a need for cancel query on a standard LOV, it made more sense to disable it so we could run with a higher polling interval and reduce the middle tier CPU overhead.
FORMS60_NONBLOCKING_SLEEP
This variable allows you to alter the sleep period as required. For example, if this is set to "200" you will get a sleep period of 200 milliseconds between polling. This should be set as high as possible without impacting user-wait time. It’s not clear what the optimal value is at this time, hence the reason we made it configurable, but either 100 milliseconds (the default) or 200 milliseconds would be a good starting point. The optimal value probably be somewhere between 100 milliseconds and 400 milliseconds - any higher and it would probably start to impact user wait time.
The default value for Oracle Applications is set at 100 milliseconds
Autoconfig Users
Autoconfig users with TXK (FND) Autoconfig Template Roll Up M ( Patch 4709948 ) or later, should update this value through Autoconfig. If you are using an earlier version, you may also apply patch 3563654 to obtain this functionality.
You may disable the cancel query functionality for Longlist LOV's as follows;
Login to 'Oracle Applications Manager'
Select 'Site Map' -> 'Monitoring' Tab -> 'System Configuration' -> 'Applications Context'
Click on 'Edit Parameters' for the 'Applications' tier.
Select 'OA_VAR' from the 'Search' drop down menu, enter ' s_f60nonblockingsleep' in the adjacent field and press the 'Go' button.
Alter the value for the 'forms 60 non-blocking sleep' parameter and press the 'Save' button.
Non-Autoconfig Users
To alter the value for this environment variable please follow the steps below:-
On Unix:
Stop the Forms Server process.
Update the 'Oracle Forms 6.0 environment variables' section of your '$APPL_TOP/< SID >.env' file with; (Using 200 milliseconds as an example)
FORMS60_NONBLOCKING_SLEEP="200"
export FORMS60_NONBLOCKING_SLEEP
Source the environment, then restart the Forms Server process.
On Windows:
Stop the Forms Server process.
Update the 'Oracle Forms 6.0 environment variables' section of your '$APPL_TOP/< SID >.cmd' file with; (Using 200 milliseconds as an example)
FORMS60_NONBLOCKING_SLEEP=200
Source the environment, then restart the Forms Server process.
--------------------------------------------------------------------------------
Forms Listener Servlet
If you are using the Forms Listener Servlet, then MaxBlockTime defined in formservlet.properties must be set to a value larger than the maximum cancel query polling interval. For example if;
FORMS60_LOV_INITIAL=10000
FORMS60_LOV_MINIMUM=20000
FORMS60_LOV_WEIGHT=0
Then set MaxBlockTime=25000 or higher.
If you set FORMS60_LOV_WEIGHT to a non-zero value, you will have to calculate the maximum polling interval based on the highest network latency. Set MaxBlockTime at least 5000ms higher than the maximum polling interval.
Failure to set MaxBlockTime to a value larger than the maximum polling interval will result in a loss of performance. If it's undefined (as in older versions of the configuration files), it defaults to 1000ms.
For the Forms Listener Servlet, the FORMS60_* environment variables are defined in the configuration file formservlet.ini
For further information on Forms Listener Servlet, see MetaLink Note id 201340.1 titled, 'Using Forms Listener Servlet with Oracle Applications 11i'.
--------------------------------------------------------------------------------
Open Enhancements (For Consideration)
Bug 823698 - Customize text/size of Cancel Query dialog and error message when query is cancelled
Bug 974350 - Mouse pointer flickers during non-blocking query on a Block/List of Values
Wednesday, December 30, 2009
7 Flows to be considered in an ERP implementation
Introduction
A functional consultant going to implement an ERP solution need to have a clear and structured idea of the various aspects of the business. An ERP implementation can be successful only if it is done top down. This means that the consultant has to identify the business issues and constraint before he gets down to the task of designing the solution. One way to do this is to start with the key reports that the organization is presently using and analyse as to the kind of information that is key to this organization.
To understand the business in a structured way, one of the methods is to divide the business into various flows. There are 7 important flows that a consultant need to understand thoroughly and integrate the same in the ERP to ensure a successful ERP implementation.
1. Business Flow:
Logically business flow encompasses all the processes being presently followed by the organization. However, from the perspective of ERP implementation, the consultant has to focus on certain key questions to understand the business thoroughly. Some the questions which should be used in this analysis are;
Where does organization procures materials? What are the key specs. What are the top three raw materials? How many supplier per item on an average? What is the relationship dynamics between supplier and organization? who is more powerful ? What are the three key issues in procurement where you can add value.
What is the organizations conversion process? How efficient is internal processes? How is the production planning done? How is inventory management done? What is the inventory turnover ratio? What is the level of obsolete inventory in the organization? How is the quality measured? How is the material being managed? what is the costing method used? Why are they using that costing method? How complex is the routing? Do they use subassemblies in manufacturing? Are their local taxes? how is excise handled?
How does organization get the customer orders? How are the customer entering the organization? Are there many one time customers? How does order gets registered? How does orgn handle available to promise issues? how is the material movement handled? What is the cost of items? How general are the items? can we divide the items into distinct categories? How is the material issue taking place? How does the costing gets done? How does the organization measure the profitability? Is it on a per order basis or a per customer basis? Do they follow significant customer approach? Do they have separate priority for category 1 customers and another for the other types of customers? What is the criteria for deciding a customer as a category 1 customer? How frequent is the reviewWhat is the size of inventory master? How are items coded? How is costing done? What is the costing method used? Why?What is the nature of the organization? Is it distribution intensive? Does it have many depots? Franchises? Is it operations intensive? Is it purchase intensive?
What is the power structure in the organization? Who is the project champion? Who could be a potential risk? what is the age profile? What is the change culture of the organization? Are they comfortable with technology? Are they going to ERP for clearly identified business benefits or are they going because competitors have moved into ERP? Is business leading the ERP implementation or is it considered as just another IT project in the organization?
At the end of business flow understanding, you should have a clear idea of top 3 business reasons why the organization is going for and ERP and the key reasons for chosing this particular ERP package. This would give you a clear idea of what is expected out of this implementation. Answer to the last question could give you a clear idea of the risks involved in this implementation.
2. Proces Flow:
Here we are talking of various business cycles. You need to know the P2P, Production and O2C cycles with some level of detail with the accounting impact. This will decide some of the key setups
3. Material Flow:
How does the material come into the organization? How is it accepted into inventory? What is the role of inspection? What is the matching process? How is the material moving in to production? Is it in bulk or based on each production order? How does the material come back into Raw material warehouse? How does it come into FG stores? How is it picked for shipping? How it it packed? Shipped?This will give you a clear picture of the complexity in inventory handling process.
4. Document Flow:
For each of the flows discussed in step 2 and 3, you need to know the associated document flows. Some of these documents are internal to the organization (inspection report, GRN etc) where data accuracy is of primary importance, while others are external to organization (PO, AR Invoice etc) where data accuracy as well as formatting are equally important. The consultant should focus more on external reports without spending too much time on the look and feel of internal reports.
For example, in a P2P (Procure to Pay) process, the document flow could be indent (requisition) --> Quotation --> PO --> Inspection report -->GRN (Goods Received Note) --> Delivery Challan --> Invoice --> Payment Voucher
5. Accounting Flow:
For each of the processes discussed in point 2, you need to know how the Accounting entries are generated and how they impact the profit / profitability of the organization. Please note that for every inventory transaction ERP creates an accounting entry (Perpetual inventory valuation). you need to be clear of the accounting steps. The inventory accounting has a tendency of getting out of hand. One of the key constraints in ERP is the understanding of local tax accounting flows and mapping the same. The consultant need to allocate some time for this activity.
6. Report Flow:
You need to know the key reports in the ERP package which shows that all the above flows are functioning correctly. Some of the reports key reports are Inventory valuation report, Open invoices report in both AR and AP, Supplier advances in AP, Customer Advances in AR, Assets Register, Depreciation / Accumulated Depreciation report, Supplier listing, Customer listing, Open POs, Open SOs, Trial Balance etc.
The above 6 Flows are what is defined as 'Above the Surface' Flows. A functional consultant need to be clear of the above flows.
7. Data Flow:
How the data flows through the database based on your transactions. What are the key tables? What are their linkages? How does the data flows from one process to another?
A consultant should at the beginning of the project strive to attain a clear understanding of the above flows. This will help him to talk the language of the organization which is very important from the perspective of the end user, to tailor his implementation to the business requirements of the organization and have a clear assessment of the implementation risks.
A functional consultant going to implement an ERP solution need to have a clear and structured idea of the various aspects of the business. An ERP implementation can be successful only if it is done top down. This means that the consultant has to identify the business issues and constraint before he gets down to the task of designing the solution. One way to do this is to start with the key reports that the organization is presently using and analyse as to the kind of information that is key to this organization.
To understand the business in a structured way, one of the methods is to divide the business into various flows. There are 7 important flows that a consultant need to understand thoroughly and integrate the same in the ERP to ensure a successful ERP implementation.
1. Business Flow:
Logically business flow encompasses all the processes being presently followed by the organization. However, from the perspective of ERP implementation, the consultant has to focus on certain key questions to understand the business thoroughly. Some the questions which should be used in this analysis are;
Where does organization procures materials? What are the key specs. What are the top three raw materials? How many supplier per item on an average? What is the relationship dynamics between supplier and organization? who is more powerful ? What are the three key issues in procurement where you can add value.
What is the organizations conversion process? How efficient is internal processes? How is the production planning done? How is inventory management done? What is the inventory turnover ratio? What is the level of obsolete inventory in the organization? How is the quality measured? How is the material being managed? what is the costing method used? Why are they using that costing method? How complex is the routing? Do they use subassemblies in manufacturing? Are their local taxes? how is excise handled?
How does organization get the customer orders? How are the customer entering the organization? Are there many one time customers? How does order gets registered? How does orgn handle available to promise issues? how is the material movement handled? What is the cost of items? How general are the items? can we divide the items into distinct categories? How is the material issue taking place? How does the costing gets done? How does the organization measure the profitability? Is it on a per order basis or a per customer basis? Do they follow significant customer approach? Do they have separate priority for category 1 customers and another for the other types of customers? What is the criteria for deciding a customer as a category 1 customer? How frequent is the reviewWhat is the size of inventory master? How are items coded? How is costing done? What is the costing method used? Why?What is the nature of the organization? Is it distribution intensive? Does it have many depots? Franchises? Is it operations intensive? Is it purchase intensive?
What is the power structure in the organization? Who is the project champion? Who could be a potential risk? what is the age profile? What is the change culture of the organization? Are they comfortable with technology? Are they going to ERP for clearly identified business benefits or are they going because competitors have moved into ERP? Is business leading the ERP implementation or is it considered as just another IT project in the organization?
At the end of business flow understanding, you should have a clear idea of top 3 business reasons why the organization is going for and ERP and the key reasons for chosing this particular ERP package. This would give you a clear idea of what is expected out of this implementation. Answer to the last question could give you a clear idea of the risks involved in this implementation.
2. Proces Flow:
Here we are talking of various business cycles. You need to know the P2P, Production and O2C cycles with some level of detail with the accounting impact. This will decide some of the key setups
3. Material Flow:
How does the material come into the organization? How is it accepted into inventory? What is the role of inspection? What is the matching process? How is the material moving in to production? Is it in bulk or based on each production order? How does the material come back into Raw material warehouse? How does it come into FG stores? How is it picked for shipping? How it it packed? Shipped?This will give you a clear picture of the complexity in inventory handling process.
4. Document Flow:
For each of the flows discussed in step 2 and 3, you need to know the associated document flows. Some of these documents are internal to the organization (inspection report, GRN etc) where data accuracy is of primary importance, while others are external to organization (PO, AR Invoice etc) where data accuracy as well as formatting are equally important. The consultant should focus more on external reports without spending too much time on the look and feel of internal reports.
For example, in a P2P (Procure to Pay) process, the document flow could be indent (requisition) --> Quotation --> PO --> Inspection report -->GRN (Goods Received Note) --> Delivery Challan --> Invoice --> Payment Voucher
5. Accounting Flow:
For each of the processes discussed in point 2, you need to know how the Accounting entries are generated and how they impact the profit / profitability of the organization. Please note that for every inventory transaction ERP creates an accounting entry (Perpetual inventory valuation). you need to be clear of the accounting steps. The inventory accounting has a tendency of getting out of hand. One of the key constraints in ERP is the understanding of local tax accounting flows and mapping the same. The consultant need to allocate some time for this activity.
6. Report Flow:
You need to know the key reports in the ERP package which shows that all the above flows are functioning correctly. Some of the reports key reports are Inventory valuation report, Open invoices report in both AR and AP, Supplier advances in AP, Customer Advances in AR, Assets Register, Depreciation / Accumulated Depreciation report, Supplier listing, Customer listing, Open POs, Open SOs, Trial Balance etc.
The above 6 Flows are what is defined as 'Above the Surface' Flows. A functional consultant need to be clear of the above flows.
7. Data Flow:
How the data flows through the database based on your transactions. What are the key tables? What are their linkages? How does the data flows from one process to another?
A consultant should at the beginning of the project strive to attain a clear understanding of the above flows. This will help him to talk the language of the organization which is very important from the perspective of the end user, to tailor his implementation to the business requirements of the organization and have a clear assessment of the implementation risks.
How to Make Close Other Forms Option Updatable
The Feature
When using certain responsibilities the ‘Close Other Forms’ option in the Navigator window, ‘Tools’ menu is protected against an update.
Solution
The function ‘Navigator: Disable Multiform’ needs to be included in menu exclusions of the given responsibility. As System Administrator:
o Navigate to Security > Responsibility > Define.
o Query the responsibility in which you want to allow to control the Close Other Forms option.
o In the Menu Exclusions, enter a function Name: Navigator: Disable Multiform
o Save changes.
The values are stored in FND_USER_PREFERENCES table by user. To see whether the checkbox is checked or not:
select * from fnd_preferences
where preference_name = ‘NEW_WINDOW_FLAG’
‘N’ means defaults to checked, ‘R’ means defaults to not-checked. We have had some inconsistencies with this checkbox. You would un-check it and exit the applications, and the next time when you log in, it’s checked again.
When using certain responsibilities the ‘Close Other Forms’ option in the Navigator window, ‘Tools’ menu is protected against an update.
Solution
The function ‘Navigator: Disable Multiform’ needs to be included in menu exclusions of the given responsibility. As System Administrator:
o Navigate to Security > Responsibility > Define.
o Query the responsibility in which you want to allow to control the Close Other Forms option.
o In the Menu Exclusions, enter a function Name: Navigator: Disable Multiform
o Save changes.
The values are stored in FND_USER_PREFERENCES table by user. To see whether the checkbox is checked or not:
select * from fnd_preferences
where preference_name = ‘NEW_WINDOW_FLAG’
‘N’ means defaults to checked, ‘R’ means defaults to not-checked. We have had some inconsistencies with this checkbox. You would un-check it and exit the applications, and the next time when you log in, it’s checked again.
Saturday, October 25, 2008
Good training articles
Good training articles
Good training articles are available on below site. We thank and appreciate the person involved in this work.
http://getappstraining.blogspot.com
Happy learning and knowledge sharing.
Good training articles are available on below site. We thank and appreciate the person involved in this work.
http://getappstraining.blogspot.com
Happy learning and knowledge sharing.
Difference between Discrete-Process and Flow Manufacturing
Difference between Discrete, Process and Flow Manufacturing:
This post contains links to where you can find explanations on these types of manufacturing which will enable you to determine the difference between them.
ITtoolbox has a good article entitled Difference Between Discrete and Flow Manufacturing which is worth taking a look at. Oracle’s Manufacturing Page explains how each of these modules works and contains product data sheets you can download.
A brief outline of each of the modules capabilities per the site is outlined below:
Discrete Manufacturing
Oracle Discrete Manufacturing helps you manage the entire product lifecycle for discrete manufacturing processes, from initial design and engineering through work-in-process to cost and quality management. Minimize costs and cycle times while supporting efficient mass customization as well as build-to-order and project-based manufacturing methodologies.
Flow Manufacturing
Oracle Flow Manufacturing supports the entire build-to-order manufacturing process, including make-to-stock, configure-to-order, discrete-repetitive, assemble-to-order, and engineer-to-order. Initiate schedules as soon as customer orders are complete, and ensure shipment as soon as build is complete. Results include shorter cycle times, balanced production, reduced inventory costs, and improved product quality.
Process Manufacturing
Oracle Process Manufacturing automates the entire product lifecycle for recipe-based manufacturing, from new product development, recipe management and production, to cost, quality, and regulatory management. It enables you to formulate products to individual customer specifications, manage variability, optimize capacity, and drive continuous process improvement.
Thanks for reading.
This post contains links to where you can find explanations on these types of manufacturing which will enable you to determine the difference between them.
ITtoolbox has a good article entitled Difference Between Discrete and Flow Manufacturing which is worth taking a look at. Oracle’s Manufacturing Page explains how each of these modules works and contains product data sheets you can download.
A brief outline of each of the modules capabilities per the site is outlined below:
Discrete Manufacturing
Oracle Discrete Manufacturing helps you manage the entire product lifecycle for discrete manufacturing processes, from initial design and engineering through work-in-process to cost and quality management. Minimize costs and cycle times while supporting efficient mass customization as well as build-to-order and project-based manufacturing methodologies.
Flow Manufacturing
Oracle Flow Manufacturing supports the entire build-to-order manufacturing process, including make-to-stock, configure-to-order, discrete-repetitive, assemble-to-order, and engineer-to-order. Initiate schedules as soon as customer orders are complete, and ensure shipment as soon as build is complete. Results include shorter cycle times, balanced production, reduced inventory costs, and improved product quality.
Process Manufacturing
Oracle Process Manufacturing automates the entire product lifecycle for recipe-based manufacturing, from new product development, recipe management and production, to cost, quality, and regulatory management. It enables you to formulate products to individual customer specifications, manage variability, optimize capacity, and drive continuous process improvement.
Thanks for reading.
Thursday, August 28, 2008
Understanding ERP
Understanding ERP
Suppose you are running a small grocery shop. So the typical operation as a shop owner is you basically buy groceries from some big seller and stock it in your shop. Now people come to your shop for day-to-day needs and buy stuff from your shop at a slightly higher price than what you originally bought and stocked it in your shop.
Ocassionally you may not be carrying items or run out of stock that people ask for so you make a note of it and promise the person to come back tomorrow and they will get their item. So far so good, now lets name some entities before we proceed and things get complicated. The big seller from whom you buy stock is called as Vendor, the people who come to your shop to buy things are known as customers, the stock in your shop is known as inventory.
So far we have identified few entities that play an active role in your day-to-day operations. As time goes by, your business expands and now you take orders over the phone and provide service to deliver the items to your customers, so you hire people to help you out in maintaining the inventory, do the delivery part and all the necessary stuff to keep the business running smoothly. The people you hire are known as employees.
So in this small shop, you typically manage the bookkeeping activities by hand using a notepad or something similar. Now imagine the same setup on a larger scale where you have more than 10,000 customers, have more than 1000 vendors, have more than 1000employees and have a huge warehouse to maintain your inventory. Do you think you can manage all that information using pen and paper? Absolutely not possible! Agree?
To facilitate big businesses, companies like Oracle Corporation have created huge software known in the category of ERP (Enterprise Resource Planning) as Oracle Applications. Now coming to think of it, Oracle Applications is not one huge software, instead it is a collection of software known as modules that are integrated and talk to each other.
Now what is meant by integrated? First let us identify the modules by entities. For e.g Purchasing and Account Payables deal with the vendors since you typically purchase from vendors and eventually have to pay the dues. Oracle Purchasing handles all the requisitions and purchase orders to the vendors whereas Oracle Accounts Payables handles all the payments to the vendors.
Similarly Oracle Inventory deals with the items you maintain in stock, warehouse etc. Dealing with customers is handled collectively with the help of Oracle Receivables and Oracle Order Management. Order Management helps you collect all the information that your customer is ordering over the phone or webstore etc whereas Receivables help you collect the money for the orders that are delivered to the customers.
Now who maintains the paychecks, benefits of the 1000 employees? It is managed by Oracle Human Resources. So by now you might have got an idea - for each logical function there is a separate module that helps to execute and maintain that function.
So all the individual functions are being taken care but how do I know if I am making profit or loss? That’s where integration comes into play. There is another module known as Oracle General Ledger. This module receives information from all the different transaction modules and summarizes them in order to help you create profit and loss statements, reports for paying Taxes etc.
To simplify, when you pay your employees that payment is reported back to General Ledgers as cost i.e money going out, when you purchase inventory items the information is transferred to GL as money going out, and so is the case when you pay your vendors. Similarly when you receive items in your inventory it is transferred to GL as money (i.e. a form of money) coming in, when your customer sends payment it is transfered to GL as money coming in. So all the different transaction modules report to GL (General Ledger) as either “money going in” or “money going out”, the net result will tell you if you are making a profit or loss.
All the equipment, shops, warehouses, computers can be termed as Assets and they are managed by Oracle Fixed Assets. Initially Oracle Applications started as bunch of modules and as time passed by they added new modules for different and new functions growing to meet the needs of today's global business corporations
(Source:from internet)
Suppose you are running a small grocery shop. So the typical operation as a shop owner is you basically buy groceries from some big seller and stock it in your shop. Now people come to your shop for day-to-day needs and buy stuff from your shop at a slightly higher price than what you originally bought and stocked it in your shop.
Ocassionally you may not be carrying items or run out of stock that people ask for so you make a note of it and promise the person to come back tomorrow and they will get their item. So far so good, now lets name some entities before we proceed and things get complicated. The big seller from whom you buy stock is called as Vendor, the people who come to your shop to buy things are known as customers, the stock in your shop is known as inventory.
So far we have identified few entities that play an active role in your day-to-day operations. As time goes by, your business expands and now you take orders over the phone and provide service to deliver the items to your customers, so you hire people to help you out in maintaining the inventory, do the delivery part and all the necessary stuff to keep the business running smoothly. The people you hire are known as employees.
So in this small shop, you typically manage the bookkeeping activities by hand using a notepad or something similar. Now imagine the same setup on a larger scale where you have more than 10,000 customers, have more than 1000 vendors, have more than 1000employees and have a huge warehouse to maintain your inventory. Do you think you can manage all that information using pen and paper? Absolutely not possible! Agree?
To facilitate big businesses, companies like Oracle Corporation have created huge software known in the category of ERP (Enterprise Resource Planning) as Oracle Applications. Now coming to think of it, Oracle Applications is not one huge software, instead it is a collection of software known as modules that are integrated and talk to each other.
Now what is meant by integrated? First let us identify the modules by entities. For e.g Purchasing and Account Payables deal with the vendors since you typically purchase from vendors and eventually have to pay the dues. Oracle Purchasing handles all the requisitions and purchase orders to the vendors whereas Oracle Accounts Payables handles all the payments to the vendors.
Similarly Oracle Inventory deals with the items you maintain in stock, warehouse etc. Dealing with customers is handled collectively with the help of Oracle Receivables and Oracle Order Management. Order Management helps you collect all the information that your customer is ordering over the phone or webstore etc whereas Receivables help you collect the money for the orders that are delivered to the customers.
Now who maintains the paychecks, benefits of the 1000 employees? It is managed by Oracle Human Resources. So by now you might have got an idea - for each logical function there is a separate module that helps to execute and maintain that function.
So all the individual functions are being taken care but how do I know if I am making profit or loss? That’s where integration comes into play. There is another module known as Oracle General Ledger. This module receives information from all the different transaction modules and summarizes them in order to help you create profit and loss statements, reports for paying Taxes etc.
To simplify, when you pay your employees that payment is reported back to General Ledgers as cost i.e money going out, when you purchase inventory items the information is transferred to GL as money going out, and so is the case when you pay your vendors. Similarly when you receive items in your inventory it is transferred to GL as money (i.e. a form of money) coming in, when your customer sends payment it is transfered to GL as money coming in. So all the different transaction modules report to GL (General Ledger) as either “money going in” or “money going out”, the net result will tell you if you are making a profit or loss.
All the equipment, shops, warehouses, computers can be termed as Assets and they are managed by Oracle Fixed Assets. Initially Oracle Applications started as bunch of modules and as time passed by they added new modules for different and new functions growing to meet the needs of today's global business corporations
(Source:from internet)
Subscribe to:
Posts (Atom)