Page View count

Wednesday, September 14, 2016

Available to Transact/Reserve quantity API IN 11i

DECLARE
   x_return_status         VARCHAR2 (50);
   x_msg_count             VARCHAR2 (50);
   x_msg_data              VARCHAR2 (50);
   l_item_id               NUMBER;
   l_organization_id       NUMBER;
   l_qty_on_hand           NUMBER;
   l_res_qty_on_hand       NUMBER;
   l_avail_to_tnsct        NUMBER;
   l_avail_to_reserve      NUMBER;
   l_qty_reserved          NUMBER;
   l_qty_suggested         NUMBER;
   l_lot_control_code      BOOLEAN;
   l_serial_control_code   BOOLEAN;
BEGIN
   -- Set the org context
    FND_GLOBAL.apps_initialize ( user_id => 70028, resp_id => 53774, resp_appl_id => 660,security_group_id => 0);
   inv_quantity_tree_grp.clear_quantity_cache; -- Clear Quantity cache  
   -- Set the variable values
   l_item_id := 1188623;
   l_organization_id := 344;
   l_lot_control_code := FALSE;  --Only When Lot number is passed  TRUE else FALSE
   l_serial_control_code := FALSE;
   -- Call API
   inv_quantity_tree_pub.query_quantities
               (p_api_version_number       => 1.0
               ,p_init_msg_lst             => NULL
               ,x_return_status            => x_return_status
               ,x_msg_count                => x_msg_count
               ,x_msg_data                 => x_msg_data
               ,p_organization_id          => l_organization_id
               ,p_inventory_item_id        => l_item_id
               ,p_tree_mode                => apps.inv_quantity_tree_pub.g_transaction_mode
               ,p_is_revision_control      => FALSE
               ,p_is_lot_control           => l_lot_control_code-- is_lot_control,
               ,p_is_serial_control        => l_serial_control_code
               ,p_revision                 => NULL              -- p_revision,
               ,p_lot_number               => NULL              -- p_lot_number,
               ,p_lot_expiration_date      => SYSDATE
               ,p_subinventory_code        => NULL              -- p_subinventory_code,
               ,p_locator_id               => NULL              -- p_locator_id,
               --,p_cost_group_id            => NULL          
               --,p_onhand_source            => NULL
               ,x_qoh                      => l_qty_on_hand     -- Quantity on-hand
               ,x_rqoh                     => l_res_qty_on_hand --reservable quantity on-hand
               ,x_qr                       => l_qty_reserved
               ,x_qs                       => l_qty_suggested
               ,x_att                      => l_avail_to_tnsct  -- available to transact
               ,x_atr                      => l_avail_to_reserve-- available to reserve
               );
   DBMS_OUTPUT.put_line ('On-Hand Quantity: ' || l_qty_on_hand);
   DBMS_OUTPUT.put_line ('Quantity Reserved: ' || l_qty_reserved);
   DBMS_OUTPUT.put_line ('Quantity Suggested: ' || l_qty_suggested);
   DBMS_OUTPUT.put_line ('Available to Transact: ' || l_avail_to_tnsct);
   DBMS_OUTPUT.put_line ('Available to Reserve: ' || l_avail_to_reserve);
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('ERROR: ' || SQLERRM);
END;

Monday, September 12, 2016

Multi-Org or multiple organization access (MOAC) in R12

What is MOAC?

Multi-Org or multiple organization access (MOAC) is basically an ability to access multiple operating units from a single application responsibility.

Why it has been created?

Prior to R12, end users use to toggle / switch / change responsibilities in order to do transactions (like invoice / payment processing in AP) in different operating units. This is a very time consuming and inefficient way of recording transactions when you have 100s of operating units specially Internet based organizations who have worldwide operations in almost all the countries.
To address this, a new feature in R12 has been introduced in which user can switch between operating units within a responsibility something similar to “Change Organization” feature in inventory. Prior to R12, user would have to switch responsibilities in order to enter transactions in respective operating units (tagged to the responsibility).

What are its advantages?

  • Multi-Org Access Control (MOAC) enables companies that have implemented a Shared Services operating model to efficiently process business transactions by allowing them to access, process and report on data for an unlimited number of operating units within a single applications responsibility.
  • This increases the productivity of Shared Service Centers, as users no longer have to switch application responsibilities when processing transactions for multiple operating units at a time.
  • Ability to view data from multiple operating units from a single responsibility, gives users more information. This enables them to make better decisions.
The following SQL will dump out the Security Profiles and Operating Unit Names assigned to them.
SELECT   psp.SECURITY_PROFILE_NAME,
         psp.SECURITY_PROFILE_ID,
         hou.NAME,
         hou.ORGANIZATION_ID
FROM     PER_SECURITY_PROFILES psp,
         PER_SECURITY_ORGANIZATIONS pso,
         HR_OPERATING_UNITS hou
WHERE    pso.SECURITY_PROFILE_ID = psp.SECURITY_PROFILE_ID
         AND pso.ORGANIZATION_ID = hou.ORGANIZATION_ID;
There are three Profile Options you need to be aware of related to Multi-Org that should be set at the Responsibility Level.
  • MO: Security Profile– Always evaluated first.
  • MO: Operating Unit– Secondary priority being evaluated after ‘MO: Security Profile’
  • MO: Default Operating Unit– Sets the default Operating Unit for transactions when running under a Security Profile.

How it is done in R12?

In Release 12, one creates a Security Profile and assigns as many operating units as you required. One can tie that security profile to a single responsibility using a profile option called MO: Security Profile. For example, you could assign the security profile to the EMEA Payables responsibility to allow that responsibility to process invoices across all operating units.
In Release 12, define a security profile in HR using the Security profile form or the Global Security profile form, and assign all of the operating units that one would want a responsibility to access. The one needs to run a concurrent request called “Run Security List Maintenance” from HR which will make those security profile available and allow one to assign them to a responsibility via a profile option called MO: Security Profile.
One can define another profile option called MO: Default Operating Unit which is optional and allows one to specify a default operating unit that will be the default when you open different subledger application forms.

Know your Concurrent Program’s Performance


Know your Concurrent Program’s Performance

The below query will give you the time taken to execute the concurrent Programs with the latest concurrent programs with least execution time comes first.
select
      f.request_id ,
      pt.user_concurrent_program_name user_conc_program_name,
      f.actual_start_date start_on,
      f.actual_completion_date end_on,
      floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)
        || ' HOURS ' ||
        floor((((f.actual_completion_date-f.actual_start_date)
        *24*60*60) -
        floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)*3600)/60)
        || ' MINUTES ' ||
        round((((f.actual_completion_date-f.actual_start_date)
        *24*60*60) -
        floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)*3600 -
        (floor((((f.actual_completion_date-f.actual_start_date)
        *24*60*60) -
        floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)*3600)/60)*60) ))
        || ' SECS ' time_difference,
      p.concurrent_program_name concurrent_program_name,
      decode(f.phase_code,'R','Running','C','Complete',f.phase_code) Phase,
      f.status_code
from  apps.fnd_concurrent_programs p,
      apps.fnd_concurrent_programs_tl pt,
      apps.fnd_concurrent_requests f
where f.concurrent_program_id = p.concurrent_program_id
      and f.program_application_id = p.application_id
      and f.concurrent_program_id = pt.concurrent_program_id
      and f.program_application_id = pt.application_id
      AND pt.language = USERENV('Lang')
      and f.actual_start_date is not null
order by
      f.actual_start_date desc,
      f.actual_completion_date-f.actual_start_date ;

Wednesday, September 07, 2016

API scripts to Create/Update/delete the Category Set/Category Assignment for a Item

API scripts to Create/Update/delete the Category Set/Category Assignment for a Item
/*****************************************************************************
 Script to Create the Category for an item
*****************************************************************************/

DECLARE
   l_msg_index_out                                   NUMBER;
   l_error_message                                   VARCHAR2 (2000);
   x_return_status                                   VARCHAR2 (80);
   x_error_code                                      NUMBER;
   x_msg_count                                       NUMBER;
   x_msg_data                                        VARCHAR2 (250);
   l_category_id                                     NUMBER;
   l_category_set_id                                 NUMBER;
   l_inventory_item_id                               NUMBER;
   l_organization_id                                 NUMBER;
BEGIN
   SELECT mcs_tl.category_set_id
     INTO l_category_set_id
     FROM mtl_category_sets_tl mcs_tl
    WHERE mcs_tl.category_set_name = '<category_set_name>';

   SELECT mcb.category_id
     INTO l_category_id
     FROM mtl_categories_b mcb
    WHERE mcb.segment1 = '<category_name>'
      AND mcb.structure_id = (SELECT mcs.structure_id
                                FROM mtl_category_sets_b mcs
                               WHERE mcs.category_set_id = l_category_set_id);

   SELECT organization_id
     INTO l_organization_id
     FROM mtl_parameters
    WHERE organization_code = '<organization_name>';

   SELECT inventory_item_id
     INTO l_inventory_item_id
     FROM mtl_system_items_b
    WHERE segment1 = '<Item_name>'
      AND organization_id = l_organization_id;

   inv_item_category_pub.create_category_assignment (p_api_version                 => 1.0
                                                   , p_init_msg_list               => fnd_api.g_true
                                                   , p_commit                      => fnd_api.g_true
                                                   , x_return_status               => x_return_status
                                                   , x_errorcode                   => x_error_code
                                                   , x_msg_count                   => x_msg_count
                                                   , x_msg_data                    => x_msg_data
                                                   , p_category_id                 => l_category_id
                                                   , p_category_set_id             => l_category_set_id
                                                   , p_inventory_item_id           => l_inventory_item_id
                                                   , p_organization_id             => l_organization_id
                                                    );

   IF x_return_status <> fnd_api.g_ret_sts_success
   THEN
      FOR i IN 1 .. x_msg_count
      LOOP
         apps.fnd_msg_pub.get (p_msg_index                   => i
                             , p_encoded                     => fnd_api.g_false
                             , p_data                        => x_msg_data
                             , p_msg_index_out               => l_msg_index_out
                              );

         IF l_error_message IS NULL
         THEN
            l_error_message                                          := SUBSTR (x_msg_data, 1, 250);
         ELSE
            l_error_message                                          :=
                                                                      l_error_message || ' /' || SUBSTR (x_msg_data, 1, 250);
         END IF;
      END LOOP;

      DBMS_OUTPUT.put_line ('*****************************************');
      DBMS_OUTPUT.put_line ('API Error : ' || l_error_message);
      DBMS_OUTPUT.put_line ('*****************************************');
   ELSE
      DBMS_OUTPUT.put_line ('*****************************************');
      DBMS_OUTPUT.put_line ('Created Category Assiginment from Item id : ' || l_inventory_item_id || ' Successfully');
      DBMS_OUTPUT.put_line ('*****************************************');
   END IF;
END;
/

/*****************************************************************************
 Script to Update the Category for an item
*****************************************************************************/

DECLARE
   l_msg_index_out                                   NUMBER;
   l_error_message                                   VARCHAR2 (2000);
   x_return_status                                   VARCHAR2 (80);
   x_error_code                                      NUMBER;
   x_msg_count                                       NUMBER;
   x_msg_data                                        VARCHAR2 (250);
   l_category_id                                     NUMBER;
   l_category_set_id                                 NUMBER;
   l_inventory_item_id                               NUMBER;
   l_organization_id                                 NUMBER;
   l_old_category_id                                 NUMBER;
BEGIN
   SELECT mcs_tl.category_set_id
     INTO l_category_set_id
     FROM mtl_category_sets_tl mcs_tl
    WHERE mcs_tl.category_set_name = '<category_set_name>';

   SELECT mcb.category_id
     INTO l_category_id
     FROM mtl_categories_b mcb
    WHERE mcb.segment1 = '<category_name>'
      AND mcb.structure_id = (SELECT mcs_b.structure_id
                                FROM mtl_category_sets_b mcs_b
                               WHERE mcs_b.category_set_id = l_category_set_id);

   SELECT organization_id
     INTO l_organization_id
     FROM mtl_parameters
    WHERE organization_code = '<organization_id>';

   SELECT inventory_item_id
     INTO l_inventory_item_id
     FROM mtl_system_items_b
    WHERE segment1 = '<Item_name>'
      AND organization_id = l_organization_id;

   SELECT mcb.category_id
     INTO l_old_category_id
     FROM mtl_system_items_b msi
        , mtl_item_categories mic
        , mtl_categories_b mcb
        , mtl_category_sets mcs
    WHERE 1 = 1
      AND mic.inventory_item_id = msi.inventory_item_id
      AND mic.organization_id = msi.organization_id
      AND mic.category_id = mcb.category_id
      AND mic.category_set_id = mcs.category_set_id
      AND mcb.structure_id = mcs.structure_id
      AND msi.inventory_item_id = c_inventory_item_id
      AND msi.organization_id = c_orgnaization_id
      AND mcs.category_set_id = c_category_set_id;

   inv_item_category_pub.update_category_assignment (p_api_version                 => 1.0
                                                   , p_init_msg_list               => fnd_api.g_false
                                                   , p_commit                      => fnd_api.g_true
                                                   , x_return_status               => l_return_status
                                                   , x_errorcode                   => l_error_code
                                                   , x_msg_count                   => l_msg_count
                                                   , x_msg_data                    => l_msg_data
                                                   , p_category_id                 => l_category_id
                                                   , p_category_set_id             => l_category_set_id
                                                   , p_inventory_item_id           => l_inventory_item_id
                                                   , p_organization_id             => l_organization_id
                                                   , p_old_category_id             => l_old_category_id
                                                    );

   IF x_return_status <> fnd_api.g_ret_sts_success
   THEN
      FOR i IN 1 .. x_msg_count
      LOOP
         apps.fnd_msg_pub.get (p_msg_index                   => i
                             , p_encoded                     => fnd_api.g_false
                             , p_data                        => x_msg_data
                             , p_msg_index_out               => l_msg_index_out
                              );

         IF l_error_message IS NULL
         THEN
            l_error_message                                          := SUBSTR (x_msg_data, 1, 250);
         ELSE
            l_error_message                                          :=
                                                                      l_error_message || ' /' || SUBSTR (x_msg_data, 1, 250);
         END IF;
      END LOOP;

      DBMS_OUTPUT.put_line ('*****************************************');
      DBMS_OUTPUT.put_line ('API Error : ' || l_error_message);
      DBMS_OUTPUT.put_line ('*****************************************');
   ELSE
      DBMS_OUTPUT.put_line ('*****************************************');
      DBMS_OUTPUT.put_line ('Created Category Assiginment from Item id : ' || l_inventory_item_id || ' Successfully');
      DBMS_OUTPUT.put_line ('*****************************************');
   END IF;
END;
/

/*****************************************************************************
 Script to delete the categroy assignment for a item
*****************************************************************************/

DECLARE
   l_msg_index_out                                   NUMBER;
   l_error_message                                   VARCHAR2 (2000);
   x_return_status                                   VARCHAR2 (80);
   x_error_code                                      NUMBER;
   x_msg_count                                       NUMBER;
   x_msg_data                                        VARCHAR2 (250);

   CURSOR c_get_item_categories
   IS
      SELECT msi.segment1
           , msi.inventory_item_id
           , mcs.category_set_id
           , mcb.category_id
           , msi.organization_id
        FROM mtl_system_items_b msi
           , mtl_item_categories mic
           , mtl_categories_b mcb
           , mtl_category_sets mcs
       WHERE 1 = 1
         AND mic.inventory_item_id = msi.inventory_item_id
         AND mic.organization_id = msi.organization_id
         AND mic.category_id = mcb.category_id
         AND mic.category_set_id = mcs.category_set_id
         AND mcb.structure_id = mcs.structure_id
         AND msi.inventory_item_id = c_inventory_item_id
         AND msi.organization_id = c_orgnaization_id;
BEGIN
   FOR c_get_item_categories_rec IN c_get_item_categories
   LOOP
      inv_item_category_pub.delete_category_assignment (p_api_version                 => 1.0
                                                      , p_init_msg_list               => fnd_api.g_true
                                                      , p_commit                      => fnd_api.g_true
                                                      , x_return_status               => x_return_status
                                                      , x_errorcode                   => x_error_code
                                                      , x_msg_count                   => x_msg_count
                                                      , x_msg_data                    => x_msg_data
                                                      , p_category_id                 => c_get_item_categories_rec.category_id
                                                      , p_category_set_id             => c_get_item_categories_rec.category_set_id
                                                      , p_inventory_item_id           => c_get_item_categories_rec.inventory_item_id
                                                      , p_organization_id             => c_get_item_categories_rec.organization_id
                                                       );

      IF x_return_status <> fnd_api.g_ret_sts_success
      THEN
         FOR i IN 1 .. x_msg_count
         LOOP
            apps.fnd_msg_pub.get (p_msg_index                   => i
                                , p_encoded                     => fnd_api.g_false
                                , p_data                        => x_msg_data
                                , p_msg_index_out               => l_msg_index_out
                                 );

            IF l_error_message IS NULL
            THEN
               l_error_message                                          := SUBSTR (x_msg_data, 1, 250);
            ELSE
               l_error_message                                          :=
                                                                      l_error_message || ' /' || SUBSTR (x_msg_data, 1, 250);
            END IF;
         END LOOP;

         DBMS_OUTPUT.put_line ('*****************************************');
         DBMS_OUTPUT.put_line ('API Error : ' || l_error_message);
         DBMS_OUTPUT.put_line ('*****************************************');
      ELSE
         DBMS_OUTPUT.put_line ('*****************************************');
         DBMS_OUTPUT.put_line (   'Removed Category Assiginment from Item : '
                               || c_get_item_categories_rec.segment1
                               || ' Successfully'
                              );
         DBMS_OUTPUT.put_line ('*****************************************');
      END IF;
   END LOOP;
END;
/
********************************************************************
 Bulk Update script to Update the Category on Item
********************************************************************
DECLARE
   CURSOR c_category
   IS
      SELECT mcb.category_id
           , mcs.category_set_id
           , msi.organization_id
           , msi.inventory_item_id
           , msi.segment1
        FROM mtl_system_items_b msi
           , mtl_item_categories mic
           , mtl_categories_b mcb
           , mtl_category_sets mcs
       WHERE 1 = 1
         AND mic.inventory_item_id = msi.inventory_item_id
         AND mic.organization_id = msi.organization_id
         AND mic.category_id = mcb.category_id
         AND mic.category_set_id = mcs.category_set_id
         AND mcb.structure_id = mcs.structure_id
         AND msi.segment1 = :p_segment1
          OR msi.inventory_item_id = :p_inventory_item_id
         AND msi.organization_id = :p_organization_id
         AND mcs.category_set_id = :p_category_set_id
         AND mcb.category_id = :p_category_id;

   l_msg_index_out                              NUMBER;
   l_error_message                              VARCHAR2(2000);
   x_return_status                              VARCHAR2(80);
   x_error_code                                 NUMBER;
   x_msg_count                                  NUMBER;
   x_msg_data                                   VARCHAR2(250);
   l_category_id                                NUMBER;
   l_category_set_id                            NUMBER;
   l_inventory_item_id                          NUMBER;
   l_organization_id                            NUMBER;
   l_old_category_id                            NUMBER;
BEGIN
   fnd_global.apps_initialize( 12247, 20634, 401);
   COMMIT;

   FOR z IN c_category
   LOOP
      inv_item_category_pub.update_category_assignment(
         p_api_version                             => 1.0
       , p_init_msg_list                           => fnd_api.g_false
       , p_commit                                  => fnd_api.g_true
       , x_return_status                           => x_return_status
       , x_errorcode                               => x_error_code
       , x_msg_count                               => x_msg_count
       , x_msg_data                                => x_msg_data
       , p_category_id                             => :p_new_category_id                                                    --3372
       , p_category_set_id                         => z.category_set_id
       , p_inventory_item_id                       => z.inventory_item_id
       , p_organization_id                         => z.organization_id
       , p_old_category_id                         => z.category_id);

      IF x_return_status <> fnd_api.g_ret_sts_success
      THEN
         FOR i IN 1 .. x_msg_count
         LOOP
            apps.fnd_msg_pub.get(
               p_msg_index                               => i
             , p_encoded                                 => fnd_api.g_false
             , p_data                                    => x_msg_data
             , p_msg_index_out                           => l_msg_index_out);

            IF l_error_message IS NULL
            THEN
               l_error_message                                          := SUBSTR( x_msg_data, 1, 250);
            ELSE
               l_error_message                                          :=
                     l_error_message
                  || ' /'
                  || SUBSTR( x_msg_data, 1, 250);
            END IF;
         END LOOP;

         DBMS_OUTPUT.put_line( '*****************************************');
         DBMS_OUTPUT.put_line(
               'API Error : '
            || l_error_message);
         DBMS_OUTPUT.put_line( '*****************************************');
      ELSE
         DBMS_OUTPUT.put_line( '*****************************************');
         DBMS_OUTPUT.put_line(
               'Created Category Assiginment from Item id : '
            || l_inventory_item_id
            || ' Successfully');
         DBMS_OUTPUT.put_line( '*****************************************');
      END IF;
   END LOOP;

   COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line(
            'UNEXP_ERROR IN MAIN : '
         || SUBSTR( SQLERRM, 1, 250));
END;
/

Friday, August 26, 2016

Oracle SQL Performance Tuning Common Tips

1. Do not use the set operator UNION if the objective can be achieved through an UNION ALL. UNION incurs an extra sort operation which can be avoided.
2. Select ONLY those columns in a query which are required. Extra columns which are not actually used, incur more I/O on the database and increase network traffic.
3. Do not use the keyword DISTINCT if the objective can be achieved otherwise. DISTINCT incurs an extra sort operation and therefore slows your queries down.
4. If it is required to use a composite index, try to use the “Leading” column in the “WHERE” clause. Though Index skip scan is possible, it incurs extra cost in creating virtual indexes and may not be always possible depending on the cardinality of the leading columns.
5. There should not be any Cartesian product in the query unless there is a definite requirement to do so. I know this is a silly point but we all have done this mistake at one point 🙂
6. Wherever multiple tables are used, always refer to a column by either using an alias or using the fully qualified name. Do not leave the guess work for Oracle.
7. SQL statements should be formatted consistently (e.g the keywords should be in CAPS only) to aid readability. Now, this is not a performance tip really. However, it’s important and part of the practices.
8. If possible use bind variables instead of constant/literal values in the predicate filter conditions to reduce repeated parsing of the same statement.
9. Use meaningful aliases for tables/views
10. When writing sub-queries make use of the EXISTS operator where possible as Oracle knows that once a match has been found it can stop and avoid a full table scan (it does a SEMI JOIN).
11. If the selective predicate is in the sub query, then use IN.
12. If the selective predicate is in the parent query, then use EXISTS.
13. Do not modify indexed columns with functions such as RTRIM, TO_CHAR, UPPER, TRUNC as this will prevent the optimizer from identifying the index. If possible perform the modification on the constant side of the condition. If the indexed column is usually accessed through a function (e.g NVL), consider creating a function based index.
14. Try to use an index if less than 5% of the data needs to be accessed from a data set. The exception is a small table (a few hundred rows) which is usually best accessed through a FULL table scan irrespective of the percentage of data required.
15. Use equi-joins whenever possible, they improve SQL efficiency
16. Avoid the following kinds of complex expressions:
    • NVL (col1,-999) = ….
    • TO_DATE(), TO_NUMBER(), and so on
These expressions prevent the optimizer from assigning valid cardinality or selectivity estimates and can in turn affect the overall plan and the join method
17. It is always better to write separate SQL statements for different tasks, but if you must use one SQL statement, then you can make a very complex statement slightly less complex by using the UNION ALL operator
18. Joins to complex views are not recommended, particularly joins from one complex view to another. Often this results in the entire view being instantiated, and then the query is run against the view data
19. Querying from a view requires all tables from the view to be accessed for the data to be returned. If that is not required, then do not use the view. Instead, use the base table(s), or if necessary, define a new view.
20. While querying on a partitioned table try to use the partition key in the “WHERE” clause if possible. This will ensure partition pruning.
21. Consider using the PARALLEL hint (only when additional resources can be allocated) while accessing large data sets.
22. Avoid doing an ORDER BY on a large data set especially if the response time is important.
23. Consider changing the OPTIMIZER MODE to FIRST_ROWS(n) if the response time is important. The default is ALL_ROWS which gives better throughput.
24. Use CASE statements instead of DECODE (especially where nested DECODEs are involved) because they increase the readability of the query immensely.
25. Do not use HINTS unless the performance gains clear.
26. Check if the statistics for the objects used in the query are up to date. If not, use the DBMS_STATS package to collect the same.
27. It is always good to understand the data both functionally and it’s diversity and volume in order to tune the query. Selectivity (predicate) and Cardinality (skew) factors have a big impact on query plan. Use of Statistics and Histograms can drive the query towards a better plan.
28. Read explain plan and try to make largest restriction (filter) as the driving site for the query, followed by the next largest, this will minimize the time spent on I/O and execution in subsequent phases of the plan.
29. If Query requires quick response rather than good throughput is the objective, try to avoid sorts (group by, order by, etc.). For good throughput, optimizer mode should be set to ALL ROWS.
30. Queries tend to perform worse as they age due to volume increase, structural changes in the database and application, upgrades etc. Use Automatic Workload Repository (AWR) and Automatic Database Diagnostic Monitor (ADDM) to better understand change in execution plan and throughput of top queries over a period of time.
31. SQL Tuning Advisor and SQL Access Advisor can be used for system advice on tuning specific SQL and their join and access paths, however, advice generated by these tools may not be always applicable (point 28).
32. SQL Access paths for joins are an component determining query execution time. Hash Joins are preferable when 2 large tables need to be joined. Nested loops make work better when a large table is joined with a small table.
Disclaimer: Points listed above are only pointers and may not work under every circumstance. This check list can be used as a reference while fixing performance problems in the Oracle Database.
Suggested further readings
  1. Materialized Views
  2. Advanced Replication
  3. Change Data Capture (Asynchronous)
  4. Automatic Workload Repository (AWR) and Automatic Database Diagnostic Monitor (ADDM).
  5. Partitioning strategies.
Now it’s turn if have any more tips which you have used then do add them in comment section… Your feedback is very valuable and it would be useful for other viewers too.

APPS FORM CREATION USING (TEMPLATE.fmb)

APPS FORM CREATION USING (TEMPLATE.fmb)


   ******* APPS FORM CREATION USING (TEMPLATE.fmb) ********

--> Befor going to create form we need to know some deffrens is there in between Narmal D2K Form & Apps Form
      t.e
          --> Narmal D2k Forms are Directly we can Create but we con't Register in Apps.
          --> But in Apps Form We can register in Oracle Apps by using the Bellow steps.( Using TEMPLATE.fmb)

-->1. First we need to create Custom (our own) Directory of Oracle Apps Forms.

      Ex: D:\FORM_10g_PLL

-->2. And then we need to Copy some files like (.FMB,.PLL) File and Paste in Custom (Our Own) Direcory of D:\FORM_10g_PLL
  
      That files are : AU--Top,  forms, Resources Folders

      Ex: D:\oracle\VIS\apps\apps_st\appl\au\12.0.0\forms\US

      Note: Hear we need to copy the files are    (TEMPLATE.fmb , APPSTAND.fmb)

---> And paste into the Custom (Our Own) directory of D:\FORM_10g_PLL

-->3. And then we need to Copy the file are pll (resource-All Folder)

      Ex: D:\oracle\VIS\apps\apps_st\appl\au\12.0.0\resource

      Note: Hear We need to copy the All Files to the Custom (Our Own) Directory of D:\FORM_10g_PLL

-->4.And the we need to set the Path of Custom (Our Own) Directory ,
      Note: Hear we need Add our custom directory to the Existing  directory.
       
        Ex Nov:- Got the RUN--> need to type as (regedit) and then click on ok

                                       --> Hear we need to Seclect HKEY_LOCAL_MEACHINE
                                       --> And then Select to open SOFTWARE
                           --> And then Select to open ORACLE
                                       --> And then Select to open HOME /HOME0, HOME1
                                       --> And then find to Openn the file name as FORM90_PATH ( D2k 10g version)
                                        FORM60_PATH ( D2k 6i version)
-->5.And then we need to Add our Custom Directory to the existing Directories

     EX: D:\develope\cgenf61\admin;D:\develope\forms90;D:\FORM_10g_PLL

--> So our path is Created

--> And we need Creat a form by using TEMPLATE.fmb ( in our Custo Directory of D:\FORM_10g_PLL)

APPS FORM CREATION USING WIZARD METHOD:
=============================

--> Open the Form Builder

 --> Hear we need creat form by selecting the Form Using Tempalte

       Ex: Nav:--> File-->New-->Form Using Tempalte

     Note: Hear we need to Brows to open  TEMPLATE.fmb ( in our Custo Directory of D:\FORM_10g_PLL)

Note: These Error means

         i am not currect file to copy the custom directory
        
         APSTAND.fmb( This is not our file)
         APPSTAND.fmb( This is our file)
   
    --> so i need to Replace this file then we can avoid the Error
                           
---> Now we Can Open the file TEMPLATE.fmb

Note: Now we Need To Delete the BLOCKS( Existing) in that Form

---> And Then We Need to Create (Data Block, Canvas, Window)

WINDOW CREATION:
=============

--> Create a new window by selectin Create button(+)
--> And Open property pallet(F4) for seting the properties

     Ex: General:
                      Name: APPS_WIZ_W
CANVAS CREATION:
=============
--> Create a new Canvas by selectin Create button(+)
--> And Open property pallet(F4) for seting the properties

Ex: General:
                      Name: APPS_WIZ_C

Note:  Attach WINDOWS to CANVAS & CANVAS to WINDOWS
=====================================

--> Go to The Property Palette(F4)  of CANVAS (APPS_WIZ_C)
    
       Physical:
                   Window: APPS_WIZ_W


--> Go to The Property Palette(F4)  of WINDOW (APPS_WIZ_W)
    
       Functional:
                   Primary Canvas: APPS_WIZ_C


DATA BLOCK CREATION:
===============
--> Create a new Data Block by selectin Create button(+)
--> Then Select tha Use The Data Block Wizard and Click on Ok Button
--> (Display the page next time) Click on Next-->(Table or View) Click on Next--> Type the Table         Name(Ex: PO_HEADERS_ALL) and Click on Refresh Button and Connect to The Data Bace (User Name: apps,         Password: apps, Database: vis)

--> Then Move The Available Columns to Database items And Click on Next-->Next-->(Hear we can edit the Data        Block Name) Click on Next-->Finish--> Click on Next-->
    (LAYOUT MODEL CREATION)--> Hear we can Select our Created CANVAS ( No need to        select , Defoult it will     Shows-->Next--> Hear we need to Move The Available items to Displayed        items-->Next-->Next-->Hear we     can select Form/Tabular (Form) -->Next--Hear we can give the Form Title-->Next-->Finish

--> Then we need to Save(.fmb) & Compile(Ctrl+T) the Form

       Ex: APPS_PO_WIZ_FORM.fmb

--> Note: When ever Compile the Form the (.fmx) will be Created automatically

--> Form is Created     

         

******* FORM REGISTRATION PROCESS ************


--> After Creation of the Form by using (TEMPLATE.fmb)

--> Then we need to Move (.fmb) File in " au-Top or po-Top "

       Ex: au-Top: D:\oracle\VIS\apps\apps_st\appl\au\12.0.0\forms\US

--> Then we need to Move (.fmx) File in " po-Top" (Perticular Resp) Only

       Ex: po-Top: D:\oracle\VIS\apps\apps_st\appl\po\12.0.0\forms\US


--> Login to Oracle Applications

                      operations
                      welcome

--> Form Registering:
      ===========
--> Nav: Application Developer (Responsibilty)--> Application-->Form

--> Hear we need to give

      Ex:   Form                                                    Application    User Form Name                                     
             ======                                              =======    ===========
             APPS_PO_WIZ_FORM ( File name(.fmb))      Purchasing    Apps Po Wiz Form  ( Display the Front End Apps)

--> Save it and Close Form

--> Function Registering:
      =============
--> Nav: Application Developer (Responsibilty)--> Application-->Function

      Ex: Function                        User Function Name  
           ======                       =============   
           APPS_PO_WIZ_FORM ( File name(.fmb))  APPS_PO_WIZ_FORM_FUNC (our wish)
       
           --> Go to the [Properties]  Tab in that same Function

           --> Hear we need to select Type asForm

                      Ex: Type: Form

           --> Go to the [Form] Tab in that same Function

           --> Hear we need to Brow & select our Form Name ( Display the Front End Apps)
  
                      Ex: Form: Apps Po Wiz Form (    User Form Name )

--> Save it and Close the Form Function

Attach "Function to Menu":
=================

--> Go to Nav: Application Developer (Responsibilty)--> Application--> Menu

Note: Hear we need to Get The ' Menu Name  ' of the PO( Perticular ) Responsibility from the Responsibility Window

       Nav: Switch to System Administrator (Responsibilty) -->Security-->Responsibility-->Define

             Hear to give our PO( Perticular ) Responsibility Name using F11 and Ctrl F11  or Find it

              Ex: Responsibility Name: Purchasing, Vision Communications (USA)

              --> And then we can get the Menu Name
 
              Ex: Menu: Purchasing SuperUser GUI

--> After geting the Menu name close the Responsibility Window & Swtch to the Application Developer(Responsibilty)
      --> Go to Nav: Application Developer (Responsibilty)--> Application--> Menu

      --> And enter to find the Menu name, using F11 and Ctrl F11  or Find it

                     Ex: Menu: Purchasing SuperUser GUI

      --> And the To add the (seq no , Prompt, Function Name) to end of the Existing Menus
         
            Ex: seq:702  Prompt: APPS_WIZ_PO_FORM Function: APPS_PO_WIZ_FORM_FUNC ( Brows our User Function                  Name)

--> Save it & Close the Menus form

--> And Switc to the Purchasing, Vision Communications (USA) Responsibilities

--> Go to End of the Navigator of PO Resp  And Click on to open the out put of our Form

***--> So the Error will come Becouse of we need set some properties in the Form Builder and Over Come This           Problem

Note: To set The 4 Tasks in Form Builder

         t.i.  1. Subclass information
               2. Change tha Properties of Form
               3. PRE-FORM
               4. APPS Custom Package Body
Note: 4the step is Common in every ware in form we need to provide.

1.Subclass information
===============
Note: 1.This Subclass information we need to set in (Data Block, Items , Convas, Window)
         2. When ever to set the Subclass information that file is Changed to Red color


--> Go to Set the bellow navigation And set Subclass information

Data Block Level:
---------------
-->  Go to Select-->Data Block-->Property palette of (F4) (PO_HEADERS_ALL)

        -->select the (*)Property Class
        -->Property Class Name: BLOCK
        -->Module: APPS_PO_WIZ_FORM ( Form Name)

--> Click on Ok

Items Level:
----------
--> Go to select--> Items( Like Item1)-->Property palette of (F4) of Item1

           Ex: PO_HEADER_ID

        -->select the (*)Property Class
        -->Property Class Name: Text_Item
        -->Module: APPS_PO_WIZ_FORM ( Form Name)

Note: Set the properties of All The Available Items like Above Ex.

Canvas Level:
------------
--> Go to select-->Canvas-->Property palette of (F4)(APPS_WIZ_C)

        -->select the (*)Property Class
        -->Property Class Name: Canvas
        -->Module: APPS_PO_WIZ_FORM ( Form Name)

Widow Level:
-----------
--> Go to select-->Windows-->Property palette of (F4)(APPS_WIZ_W)


        -->select the (*)Property Class
        -->Property Class Name: Window
        -->Module: APPS_PO_WIZ_FORM ( Form Name)

2. Change tha Properties of Form:
======================

--> Go to the Property Palette(F4)  of Form Leve

      Ex: FORM-->APPS_PO_WIZ_FORM(Form Name)

          Navigation:
        First Navigation Data Block: PO_HEADERS_ALL (Data Block Name)

3. PRE-FORM:
=========

--> Go to the PRE-FORM of Form Level Trigger

      NaV: Trigger( Under the Form)-->PRE-FORM--F11(To open the Pl/sql Editor) and set the bellow

Note:
===    FND_STANDARD.FORM_INFO('$Revision: 120.0                                                                                                                                              $', 'Template Form', 'FND',
                       '$Date: 2005/05/06 23:25  $', '$Author: appldev $');
          app_standard.event('PRE-FORM');
          app_window.set_window_position('BLOCKNAME', 'FIRST_WINDOW');----> Need To Change This Line Only

---> above code is Defoult , just we need to Replace the ('BLOCKNAME'(Data Block name)  , 'FIRST_WINDOW' Window         name )

---> 'FIRST_WINDOW'  Window name is Optional No Need to Replace

---> Need to Replace the BLOCKNAME to PO_HEADERS_ALL(Data Block name)

   Ex: app_window.set_window_position('BLOCKNAME', 'FIRST_WINDOW'); (Default)

        app_window.set_window_position('PO_HEADERS_ALL', 'FIRST_WINDOW');---Yes


--> Compile it ( Ctrl+T)

4. APPS Custom Package Body:
===================

-->Go to Select the " Program Units" in Form Builder
--> And then to  Select the APP_CUSTOM (Package Body)-->Go to the Pl/Sql Editor
--> Hear we need to set the Window name in if Condition

--> The bellow Code is Defoult in APP_CUSTOM (Package Body)

--> In bellow condition we need to replace as( your first window to APPS_WIZ_W(Window name) )

         if (wnd = '<your first window>') then ----> Need To Chang This Line Only
    app_window.close_first_window;
  elsif (wnd = '<another window>') then
    --defer relations
    --close related windows  
    null; 
  elsif (wnd = '<yet another window>') then
    --defer relations
    --close related windows  
    null;
  end if;

--> Then we need to Save & Compile (Ctrl+T) it

--> Then Login To the Oracle Application -->

--> And Switch to the Purchasing, Vision Communications (USA) Responsibilities

--> Go to End of the Navigator of PO Resp  And Click on to open the out put of our Form

Note: Form is Successfully Registered Here  we Can manupulate the DML Opertaions.

problem installing oracle developer Suite 10g on window 7 64 bit




Since last few days I was struggling to connect with Forms/Report 10g installed on my system, today I got the solution for this, so thought of to share with everyone, so that it might help everyone in future if anyone is installing developer 10g.


Issue  was :

My setup directory (Folder) was having space.
Like earlier my setup file was in

C:\A Stryker Work\Softwares\Oracle\Oracle Developer Suite 10g Working\ds_windows_x86_101202_disk1

So here “Oracle Developer Suite 10g Working”, this folder was containing few spaces.

Solutions:

I copied setup folder directly in C:/ Directory , and without spaces, then installed again, now I am able to connect successfully.


“C:\Oracle_dev_suite\ds_windows_x86_101202_disk1”