, , , ,

How to Launch Workflow?

Following APIs used to Launch Workflow

declare
cursor c1 is
select incident_no,CREATED_BY_USER_ID from org_sr_summary a
where trunc(create_dt) = trunc(sysdate) and incident_no = ‘1203950’
and not exists ( select 1 from org_incident_history b where a.incident_no = b.incident_id);

v_return_status varchar2(150);
v_msg_count number;
v_msg_data varchar2(150);
v_itemkey varchar2(150);
v_initiator_user_id number;
v_count number := 0;
v_null number:=0;

begin

For i in c1
Loop

begin
select workflow_process_id into v_null
from cs_incidents_all_b
where incident_number = TO_CHAR(i.incident_no)
and workflow_process_id is null ;
exception
when no_data_found then dbms_output.put_line(‘Exception’);
end;

if v_null is null then

CS_WORKFLOW_PUB.LAUNCH_SERVEREQ_WORKFLOW(p_api_version => 1
,p_init_msg_list => FND_API.G_FALSE
,p_commit => FND_API.G_FALSE
,p_return_status => v_return_status
,p_msg_count => v_msg_count
,p_msg_data => v_msg_data
,p_request_number => i.incident_no
,p_initiator_user_id => i.CREATED_BY_USER_ID
,p_initiator_resp_id => NULL
,p_initiator_resp_appl_id => NULL
,p_itemkey => v_itemkey
, p_nowait => FND_API.G_FALSE);

v_count := v_count + 1;
end if;

END LOOP;
dbms_output.put_line(‘total count = ‘||v_count);
commit;
end;

SELECT b.process_name, b.instance_label, a.activity_result_code,
a.activity_status, a.item_key
FROM wf_item_activity_statuses a, wf_process_activities b
WHERE a.process_activity = b.instance_id
AND a.item_type = ‘SERVEREQ’
AND a.item_key LIKE ‘1203968%’
AND a.activity_status = ‘DEFERRED’

begin
wf_engine.handleerror(itemtype => ‘SERVEREQ’,
itemkey => ‘1203968-757581’,
activity => ‘XX_WF:STANDARDWAIT-1’ ,
command => ‘RETRY’);
commit;
end;

declare
 cursor c1 is
 select incident_no,CREATED_BY_USER_ID from org_sr_summary a
 where trunc(create_dt) = trunc(sysdate) and incident_no = 1199236;
 –and not exists ( select 1 from org_incident_history b where a.incident_no = b.incident_id);
  v_return_status        varchar2(150);
  v_msg_count            number;
  v_msg_data            varchar2(150);
  v_itemkey                varchar2(150);
  v_initiator_user_id   number;
  v_count                number := 0;
  v_null                number:=0;
begin
     For i in c1
     Loop
      begin
      select workflow_process_id into v_null
      from      cs_incidents_all_b
      where  incident_number = TO_CHAR(i.incident_no)
      and  workflow_process_id is null ;
      exception
      when no_data_found then null;
      end;
      if v_null is null then
      CS_WORKFLOW_PUB.LAUNCH_SERVEREQ_WORKFLOW(p_api_version                    => 1
                                             ,p_init_msg_list                 => FND_API.G_FALSE
                                             ,p_commit                       => FND_API.G_FALSE
                                             ,p_return_status               => v_return_status
                                             ,p_msg_count                   => v_msg_count  
                                             ,p_msg_data                       => v_msg_data
                                             ,p_request_number               => i.incident_no
                                             ,p_initiator_user_id              => i.CREATED_BY_USER_ID
                                             ,p_initiator_resp_id              => NULL
                                             ,p_initiator_resp_appl_id        => NULL
                                             ,p_itemkey                       => v_itemkey
                                             , p_nowait                           => FND_API.G_FALSE);

    v_count := v_count + 1;
     IF v_msg_count > 0 THEN

        FOR I IN 1..v_msg_count LOOP

        dbms_output.put_line(‘I.’|| SUBSTR (FND_MSG_PUB.Get(p_encoded =>FND_API.G_FALSE ), 1, 255));

        END LOOP;

    END IF;
    end if;
   END LOOP;  
    dbms_output.put_line(‘total count = ‘||v_count);
    dbms_output.put_line(‘return status = ‘||v_return_status);
        dbms_output.put_line(‘ v_msg_data = ‘|| v_msg_data);
    dbms_output.put_line(‘  v_itemkey = ‘|| v_itemkey);
commit;
end;

, , , , ,

Tips of Oracle SQL Performance Tuning

These are the tips that needs to be followed to performance tune your SQL scripts;

·    Never do a calculation on an indexed column (e.g., WHERE salary*5 > :myvalue)

·    Whenever possible, use the UNION statement instead of OR conditions

·    Avoid the use of NOT IN or HAVING in the WHERE clause. Instead, use the NOT EXISTS clause

·    Always specify numeric values in numeric form and character values in character form (e.g., WHERE emp_number = 565, WHERE emp_name = ?Jones?)

·    Avoid specifying NULL in an indexed column

·    Avoid the LIKE parameter if = will suffice. Using any Oracle function will invalidate the index, causing a full-table scan

·    Never mix data types in Oracle queries, as it will invalidate the index. If the column is numeric, remember not to use quotes (e.g., salary = 50000). For char index columns, always use single quotes (e.g., name = ?BURLESON?)

·    Remember that Oracle’s rule-based optimizer looks at the order of table names in the FROM clause to determine the driving table. Always make sure that the last table specified in the FROM clause is the table that will return the smallest number of rows. In other words, specify multiple tables with the largest result set table specified first in the FROM clause

·    Avoid using sub-queries when a JOIN will do the job

·    Use the Oracle decode function to minimize the number of times a table has to be selected

·    To turn off an index you do not want to use (only with a cost-based optimizer), concatenate a null string to the index column name (e.g., name||’) or add zero to a numeric column name (e.g., salary+0). With the rule-based optimizer, this allows you to manually choose the most selective index to service your query

·    If your query will return more than 20 percent of the rows in the table, use a full-table scan rather than an index scan

·    Always use table aliases when referencing columns
·    Understand the data. Look around table structures and data. Get a feel for the data model and how to navigate it.
·    If a view joins 3 extra tables to retrieve data that you do not need, don’t use the view!
·    When joining 2 views that themselves select from other views, check that the 2 views that you are using do not join the same tables!
·    Avoid multiple layers of view. For example, look for queries based on views that are themselves views. It may be desirable to encapsulate from a development point of view. But from a performance point of view, you loose control and understanding of exactly how much task loading your query will generate for the system.
·    Look for tables/views that add no value to the query. Try to remove table joins by getting the data from another table in the join.
·    WHERE EXISTS sub-queries can be better than join if can you reduce drastically the number of records in driver query. Otherwise, join is better.
·    WHERE EXISTS can be better than join when driving from parent records and want to make sure that at least on child exists. Optimizer knows to bail out as soon as finds one record. Join would get all records and then distinct them!
·    In reports, most of the time fewer queries will work faster. Each query results in a cursor that Reports has to open and fetch. See Reports Ref Manual for exceptions.
·    Avoid NOT in or NOT = on indexed columns. They prevent the optimizer from using indexes. Use where amount > 0 instead of where amount != 0.
·    Avoid writing where project_category is not null. Nulls can prevent the optimizer from using an index.
·    Consider using IN or UNION in place of OR on indexed columns. OR’s on indexed columns causes the optimizer to perform a full table scan.
·    Avoid calculations on indexed columns. Write WHERE approved_amt > 26000/3 instead of WHERE approved_amt/3 > 26000.
·    Avoid this: SUBSTR(haou.attribute1,1,LENGTH(‘:p_otc’)) = :p_otc). Consider this: WHERE  haou.attribute1 like :p_otc||’%’
·    Talk to your DBA. If you think that a column used in a WHERE clause should have an index, don’t assume that an index was defined. Check and talk to your DBA if you don’t find any.
·    Consider replacing outer joins on indexed columns with UNION. A nested loop outer takes more time than a nested loop un-joined with another table access by index.
·    Consider adding small frequently accessed columns (not frequently updated) to an existing index. This will enable some queries to work only with the index, not the table.
·    Consider NOT EXISTS instead of NOT IN.
·    If a query is going to read most of the records in a table (more than 60%), use a full table scan.
·    Try to group multiple sub queries into one.
·    If you want to actually understand what you are doing, here are a few things that you need to start playing with:
·    Get into EXPLAIN_PLAN. There are multiple way of doing this. The less user friendly is to simply issue this in SQL*Plus: explain plan set statement_id = ‘HDD1’ for ;
·    Look at the trace from Oracle Reports. It tells you how much time it spends on each query.
·    Use the SQL Trace by issuing an alter session set sql_trace=true; then look at it with TKPROF .trc
·    Do not use functions on indexed columns in WHERE clauses Unless specifically using a function based index.

·    Beware of implicit datatype conversions occuring on indexed columns (e.g. comparing a character column with a number expression will invoke an implicit
TO_NUMBER operation on the indexed column, preventing use of the index – in this case it would be better to us an explicit TO_CHAR operation around thenumber expression being compared against

·    Make sure that you have the correct indexes on the tables:-
·    Ensure that the first column of the index is selective ( e.g. more than around 15 distinct values with a near uniform distribution
·    Avoid using insufficiently selective indexes (i.e. each index value applies to at least 15% of the rows).  In this case, a full table scan will be more efficient.
·    Aim to have no more than 4 indexes per table. ( each insert into the table has to also modify data in the indexes )

·    To use compound indexes ensure that the WHERE clause includes conditions on  the leading column(s) of the index

·    NEVER use SELECT * FROM. in application code

·    Try to avoid use of the DISTINCT clause as it always requires a sort operation.  Excessive use of DISTINCT clause may point to an underlying data model problem (e.g a missing table).

, , ,

What is Item Replenishment ?

Item Replenishment Overview:

One can use the replenishment counting system to plan your inventories. This may be ideal for replenishing free stock items that you store on the production floor, or office supplies kept in departmental cabinets. Oracle Inventory provides an open interface for loading replenishment count data collected from some other system. In some situations, you may want to do your own planning and use Oracle Inventory to create your internal requisitions. As items are issued from a sub-inventory without formal issuing procedures, it is necessary to take inventory of non–tracked sub-inventories to record item usage and determine reorder necessity.
Business Scenario
In order to determine when and how much to replenish, periodical physical count of the items in non–tracked sub-inventories can be entered through the Replenishment Counts window, or via a batch load transaction such as a bar code reader. Oracle Inventory permits on–hand quantity as an additional replenishment information type for non–tracked sub-inventories. If an on–hand quantity is entered, Oracle Inventory uses min–max planning to determine whether an order should be placed. Oracle Inventory provides an external interface for loading replenishment count data collected electronically, such as by a bar code reader. Once you have populated the replenishment interface tables, you can run the Validate Replenishment Interface program to validate the counts and put the information into the appropriate tables.
               
Type of Interface: OPEN Interface
Technical Overview
MTL_REPLENISH_HEADERS, MTL_REPLENISH_LINES are the main tables. MTL_REPLENISH_HEADERS _INT, MTL_REPLENISH_LINES_INT are the Interface tables
The production tables are MTL_REPLENISH_HEADERS and MTL_REPLENISH_LINES. For each record in the MTL_REPLENISH_HEADERS there should be at least one record in MTL_REPLENISH_LINES. The corresponding interface tables are  MTL_REPLENISH_HEADERS_INT and MTL_REPLENISH_LINES_INT.                                
One should be very careful about many things like the Count type (On-Hand Qty, Order Qty, Order Maximum) depend on the item such that On-Hand Qty is applicable to non-tracked items, Order Qty is applicable to Reorder Level planned items while Order Maximum is applicable to items that are Min-Max planned. The replenishment count is mandatory in the first two conditions while it is not needed in Min-Max Planning. With the On-hand Qty, count qty is the on-hand balance while with Order Qty it is the reorder qty.             
Required fields for MTL_REPLENISH_HEADERS_INT are
ORGANIZATION_ID/ORGANIZATION_CODE, PROCESS_MODE, PROCESS_STATUS, REPLENISH_HEADER_ID, REPLENISH_COUNT_NAME, SUBINVENTORY_CODE,
PROCESS_MODE:
 2 – Concurrent
3 – Background
The replenishment validator polling process will pick up interface replenishment counts marked for Background processing. The validator will pick up and process all replenishment counts with a process mode of Background each time it runs.
You use Concurrent processing mode if you want to launch a dedicated replenishment validator process to explicitly process a single  replenishment count, identified as a parameter to the program, from the interface table.
PROCESS_STATUS
1 – Hold
2 – Pending
3 – Processing
4 – Error
5 – Completed
Required fields for MTL_REPLENISH_LINES_INT are INVENTORY_ITEM_ID, ORGANIZATION_ID, COUNT_TYPE_CODE, COUNT_QUANTITY, and COUNT_UOM_CODE
COUNT_TYPE_CODE
Enter the type of the replenishment count entry. The valid count types are:
1 – On-hand Quantity: Use On-hand Quantity to identify counts that are the result of stock-takes of sub-inventories in which you do not track on-hand quantities.
2 – Order Quantity: Use Order Quantity when you want to specify the quantity to be ordered. This count type may be used with either tracked or non-tracked sub-inventories.
3 – Order Maximum: Use Order Maximum when you want to place an order for the min-max maximum quantity specified for item in the sub-inventory specified. This count type may be used with either tracked or non-tracked sub-inventories.
Setting Up the Replenishment Interface
Access the Replenishment Interface through the Oracle Inventory menu (Counting/Replenishment Counts/Process Interface). Select the type of request by choosing Single Request. In the Request Name field, select Validate Replenishment Interface. In the Parameters window, select Concurrent or Background as the Processing Mode and select the Count Name for processing. Select Submit Request to begin processing. You can also use the Schedule button to specify resubmission parameters that will control how frequently the Replenishment Validator polls for records in the interface tables.
Validations Performed
The value of REPLENISH_HEADER_ID must be unique among existing replenishment counts
§         The value of REPLENISH_COUNT_NAME must be unique among existing count headers
§         The value of LAST_UPDATED_BY must be a valid user name
§         ORGANIZATION_ID must be a valid identifier of an organization
§         SUBINVENTORY_CODE must refer to an existing sub-inventory
§         DELIVERY_LOCATION_ID must be a valid identifier of a location associated with the organization generating the replenishment
§         There must be at least one line per header
§         The ORGANIZATION_ID at the header level must be the same as that at the line level
§         COUNT_TYPE_CODE must be either 1, 2, or 3 and must be consistent with whether the sub-inventory is tracked or non-tracked
§         The value of COUNT_QUANTITY must be consistent with COUNT_TYPE_CODE and must be greater than zero INVENTORY_ITEM_ID must refer to a transactable item in the organization specified
§         The item must exist in the sub-inventory and must be min-max planned in that sub-inventory
§         The COUNT_UOM_CODE must be valid and conversions to primary UOM must exist
§         Each line must correspond to a header
Error Messages: (Values for the ERROR_FLAG)
Possible values for the ERROR_FLAG column in the MTL_REPLENISH_HEADERS_INT table are:
1 – Non-unique replenishment header id
2 – Non-unique replenishment count name
3 – Invalid user name
4 – Invalid organization identifier
5 – Invalid subinventory
7 – Header with no corresponding replenishment lines
10 – Header failed because line failed
18 – Delivery location is not valid
Possible values for the ERROR_FLAG column in the MTL_REPLENISH_LINES_INT table are:
1 – No corresponding header id
3 – Invalid user name
8 – Invalid item identifier or item isn’t transactable
9 – Invalid unit of measure or no conversion to primary unit of measure exists
11 – No item specified in either identifier or segments
12 – Invalid count type
13 – On-hand count type used for tracked sub inventory