Decode
1.It’s a function
2.can compare only discrete vaules
3.Pl/Sql standards
4.cannot process null

Case
1.It’s an Expression
2.can hande range values
3.ANSI Standards
4.Processing time is faster when compared to Decode
5.can process null

Decode
The default operator for comparison is ‘=’,
for e.g select deptno,decode
(deptno,10,’dept10′,20,dept20,default)

so if the deptno=10 then the value is replaced by dept10
and so on,

whereas in Case:

we can use any operator other than ‘=’,
for e.g
select deptno,
case when deptno=10 and sal>1000 then ‘dept10’
else
‘dept20’

Decode: using for reporting purpose. and it is implemented
by if stmt.
Ex: select decode(job,’CLERK’,’executive’,job) from emp;

Case : implemented by if & elsif condition. it is using
pl/sql equality conditionality.
Ex : select case job=’CLERK’ then ‘executive’ else ‘no’ end
a from emp;

it is also used for multipul colms & multi conditions.

in above stmt a is reffered alias name.

There is one more Important difference between CASE and DECODE

DECODE can be used Only inside SQL statement….
But CASE can be used any where even as a paramtre of a function/procedure

Eg:-

Code: [Select all] [Show/ hide]SQL> create or replace procedure pro_01(n number) is
2 begin
3 dbms_output.put_line(‘ The number = ‘||n);
4 End;
5 /

Procedure created.

SQL> set serverout on
SQL> var a varchar2(5);
SQL> Begin
2 :a := ‘ONE’;
3 End;
4 /

PL/SQL procedure successfully completed.

SQL> Begin
2 pro_01(Decode(:a,’ONE’,1,0));
3 End;
4 /
pro_01(Decode(:a,’ONE’,1,0));
*
ERROR at line 2:
ORA-06550: line 2, column 9:
PLS-00204: function or pseudo-column ‘DECODE’ may be used inside a SQL
statement only
ORA-06550: line 2, column 2:
PL/SQL: Statement ignored

SQL> Begin
2 pro_01(case :a when ‘ONE’ then 1 else 0 end);
3 End;
4 /
The number = 1

PL/SQL procedure successfully completed.

If we want to run the SQL script from the Concurrent Program window then we should follow the below steps.
1. Develop the SQL*plus (.SQL)
2. Transfer the SQL script file (.sql) file from local machine to the server in to respectable path CUST_TOP/11.5.0/SQL/.SQL
Then we have to follow the steps to implement (to run from Concurrent Program window) in Oracle Applications.
1. Create executable by executable method SQL*plus
2. Define Concurrent Program, attach executable, parameters, incompatibilities
3. Attach Concurrent Program to request group.
4. Attach request to responsibility
5. Attach responsibility to user
6. User will submit from SRS window.

Simple SQL Script:
Column user_id format 99999999
Column user_name format a50
Column ucreation_date format a11

Prompt *****************************
Prompt This is SQL* Plus Script
Prompt *****************************

SELECT user_id, user_name,creation_date
FROM end_user;

SQL Script with Parameter:
Inside of SQL script we can receive the parameter value by using &1, &2, &3 and so on. First Concurrent Program parameter value will come in the place, where we have mention &1. Second Concurrent Program parameter in to &2 and so on…
Note:
We can define max of 100 PARAMETERS for a Concurrent Program.
The Format Type of Concurrent Program output should be ‘TEXT’.

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

We all know SLA is rule driven to derive accounts for create accounting process. If you are not using standard (seeded) accounting rules, but have created a new one with a new application accounting method for a specific application, how create accounting program uses these rules to derive correct accounting?
We talked about SLA earlier here. If you follow the earlier article, we are talking about using application accounting method (custom) to derive an account for a specific journal line type (debit or credit). To achieve custom accounting you assign this accounting derivation rule to this journal line type. These journal line types roll into event classes (invoices or deposits) and entities (AR transactions) to form Application Specific Accounting Method. This accounting method is assigned to an application of interest where this desired accounting is expected. This accounting method has to be validated (Validation Program) for this to be used.
This validation process actually creates a database package where all these rules are coded and maintained. To identify the package you can use this simple script which gives you the package name. Parameters to this package have to be sourced from the table XLA_PRODUCT_RULES_B. This tables stores the application accounting definitions that we talked about earlier. For example you want to get the custom application accounting method you have created for Receivables, you can simply use this SQL to get that.
Pass the values from the above SQL to this simple function get that package. If you want to see standard rules, change product rule type code from C to S. If you have more than one product rule code (application accounting method), you should know which one you are looking for. If you open this database package your technical eyes can see what is happening in create accounting program.

select application_id,
product_rule_code,
product_rule_type_code,product_rule_hash_id from xla_product_rules_b
where application_id=222 –Receivables
and product_rule_type_code=’C’;


DECLARE
c_package_name CONSTANT VARCHAR2 (30) := ‘XLA_$id1$_AAD_$id2$_$id3$_PKG’;
l_package_name VARCHAR2 (1000);

FUNCTION getpackagename (
p_application_id IN NUMBER,
p_product_rule_type_code IN VARCHAR2,
p_product_rule_hash_id IN NUMBER
)
RETURN VARCHAR2
IS
l_name VARCHAR2 (30);
l_hashapplication VARCHAR2 (30);
l_hashrulecode VARCHAR2 (30);
l_log_module VARCHAR2 (240);

BEGIN
l_hashapplication := LPAD (SUBSTR (TO_CHAR (ABS (p_application_id)), 1, 5), 5, ’0′);
l_hashrulecode := LPAD (SUBSTR (TO_CHAR (p_product_rule_hash_id), 1, 6), 6, ’0′);
l_name := c_package_name;
l_name := REPLACE (l_name, ‘$id1$’, l_hashapplication);
l_name := REPLACE (l_name, ‘$id2$’, p_product_rule_type_code);
l_name := REPLACE (l_name, ‘$id3$’, l_hashrulecode);
RETURN l_name;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END getpackagename;
BEGIN
l_package_name :=
getpackagename (p_application_id => 222,–application ID
p_product_rule_type_code => ‘C’, –Rule type
p_product_rule_hash_id => 18 –Hash value
);
DBMS_OUTPUT.put_line (l_package_name);
END;

Use Of Oe_Order_Pub.Process_Order To Create Sale Order
    Process Order API is a PL/SQL packaged procedure which can be used to manipulate the sales order data by performing Insert, update or delete operation on the following sales Order business object entities.
Analogous to other public API’s, Process Order API also validates the data before inserting them into the application tables.
Though Process Order API has packaged procedures which will insert, update, delete data into the tables, they can not be run on their own. Either they need to be called from another package procedure or can be executed as PL/SQL block via the sql*plus.

Related Data Table Names

Order Header===> OE_ORDER_HEADERS_ALL
Order Line===> OE_ORDER_LINES_ALL
Order Price Adjustments===> OE_PRICE_ADJUSTMENTS
Order Sales Credits===> OE_SALES_CREDITS
Order Pricing Attributes===> OE_ORDER_PRICE_ATTRIBS
Order Adjustment Attributes===> OE_PRICE_ADJ_ATTRIBS
Order Adjustment Associations===> OE_PRICE_ADJ_ASSOCS
Line Sales Credits===> OE_SALES_CREDITS
Line Price Adjustments> OE_PRICE_ADJUSTMENTS
Line Pricing Attributes===> OE_ORDER_PRICE_ATTRIBS
Line Adjustment Attributes===> OE_PRICE_ADJ_ATTRIBS
Line Adjustment Associations===> OE_PRICE_ADJ_ASSOCS
Lot Serial Numbers ===>OE_LOT_SERIAL_NUMBERS

R12 – SAMPLE SCRIPT TO CREATE SALES ORDER USING OE_ORDER_PUB.PROCESS_ORDER

DECLARE
l_api_version_number NUMBER := 1;
l_return_status VARCHAR2(2000);
l_msg_count NUMBER;
l_msg_data VARCHAR2(2000);
— PARAMETERS
l_debug_level number := 5; — OM DEBUG LEVEL (MAX 5)
l_org number := 308; — OPERATING UNIT
l_no_orders number := 1; — NO OF ORDERS
— INPUT VARIABLES FOR PROCESS_ORDER API
l_header_rec oe_order_pub.header_rec_type;
l_line_tbl oe_order_pub.line_tbl_type;
l_action_request_tbl oe_order_pub.Request_Tbl_Type;
— OUT VARIABLES FOR PROCESS_ORDER API
l_header_rec_out oe_order_pub.header_rec_type;
l_header_val_rec_out oe_order_pub.header_val_rec_type;
l_header_adj_tbl_out oe_order_pub.header_adj_tbl_type;
l_header_adj_val_tbl_out oe_order_pub.header_adj_val_tbl_type;
l_header_price_att_tbl_out oe_order_pub.header_price_att_tbl_type;
l_header_adj_att_tbl_out oe_order_pub.header_adj_att_tbl_type;
l_header_adj_assoc_tbl_out oe_order_pub.header_adj_assoc_tbl_type;
l_header_scredit_tbl_out oe_order_pub.header_scredit_tbl_type;
l_header_scredit_val_tbl_out oe_order_pub.header_scredit_val_tbl_type;
l_line_tbl_out oe_order_pub.line_tbl_type;
l_line_val_tbl_out oe_order_pub.line_val_tbl_type;
l_line_adj_tbl_out oe_order_pub.line_adj_tbl_type;
l_line_adj_val_tbl_out oe_order_pub.line_adj_val_tbl_type;
l_line_price_att_tbl_out oe_order_pub.line_price_att_tbl_type;
l_line_adj_att_tbl_out oe_order_pub.line_adj_att_tbl_type;
l_line_adj_assoc_tbl_out oe_order_pub.line_adj_assoc_tbl_type;
l_line_scredit_tbl_out oe_order_pub.line_scredit_tbl_type;
l_line_scredit_val_tbl_out oe_order_pub.line_scredit_val_tbl_type;
l_lot_serial_tbl_out oe_order_pub.lot_serial_tbl_type;
l_lot_serial_val_tbl_out oe_order_pub.lot_serial_val_tbl_type;
l_action_request_tbl_out oe_order_pub.request_tbl_type;
l_msg_index NUMBER;
l_data VARCHAR2(2000);
l_loop_count NUMBER;
l_debug_file VARCHAR2(200);
BEGIN
— INITIALIZATION REQUIRED FOR R12
mo_global.set_policy_context (‘S’, l_org);
mo_global.init(‘ONT’);
— INITIALIZE DEBUG INFO
IF (l_debug_level > 0) THEN
l_debug_file := OE_DEBUG_PUB.Set_Debug_Mode(‘FILE’);
oe_debug_pub.initialize;
oe_debug_pub.setdebuglevel(l_debug_level);
Oe_Msg_Pub.initialize;
END IF;
— INITIALIZE ENVIRONMENT
fnd_global.apps_initialize (user_id => 2083,
resp_id => 21623,
resp_appl_id => 660);

— INITIALIZE HEADER RECORD
l_header_rec := OE_ORDER_PUB.G_MISS_HEADER_REC;
— POPULATE REQUIRED ATTRIBUTES
l_header_rec.operation := OE_GLOBALS.G_OPR_CREATE;
l_header_rec.TRANSACTIONAL_CURR_CODE := ‘AUD’;
l_header_rec.pricing_date := SYSDATE;
l_header_rec.cust_po_number := ‘TSTPO30’;
l_header_rec.sold_to_org_id := 1006685;
l_header_rec.price_list_id := 33019;
l_header_rec.ordered_date := SYSDATE;
l_header_rec.shipping_method_code := ‘000001_Toll IPEC_T_2T5DGRD’;
l_header_rec.sold_from_org_id := 308;
l_header_rec.ship_from_org_id := 381;
l_header_rec.ship_to_org_id := 2005460;
l_header_rec.salesrep_id := 100000069;
l_header_rec.flow_status_code:=’ENTERED’;
l_header_rec.order_type_id := 5389;
— REQUIRED HEADER DFF INFORMATIONS
l_header_rec.attribute1 :=193; — Entering Branch
l_header_rec.attribute3 := ‘Y’; — Indexation applicable
l_header_rec.attribute5 := ‘2.5’; — Indexation Tolerance percentage
l_header_rec.attribute7 := 100000045; — Field Sales representative
l_header_rec.attribute11 := ‘100’; — Indexation Applicability
— INITIALIZE ACTION REQUEST RECORD
l_action_request_tbl(1) := OE_ORDER_PUB.G_MISS_REQUEST_REC;
— INITIALIZE LINE RECORD
l_line_tbl(1) := OE_ORDER_PUB.G_MISS_LINE_REC;
l_line_tbl(1).operation := OE_GLOBALS.G_OPR_CREATE; — Mandatory Operation to Pass
l_line_tbl(1).inventory_item_id := 102775;
l_line_tbl(1).ordered_quantity := 1;
l_line_tbl(1).ship_from_org_id := 381;
l_line_tbl(1).subinventory := ‘SELLABLE’;

— REQUIRED LINE DFF INFORMATIONS
l_line_tbl(1).attribute2 := ‘20.99998’; — Gross Margin
l_line_tbl(1).attribute3 := ‘2.493288’; — Business Cost
l_line_tbl(1).attribute10 := ‘1000’; — Original Cust Requested Qty
l_line_tbl(1).attribute11 := ‘662.772’; — Baseline Margin
l_line_tbl(1).attribute16 := ‘DBP’; — Buy Price Basis
for i in 1..l_no_orders loop — BEGIN LOOP
— CALLTO PROCESS ORDER API
oe_order_pub.process_order(
p_org_id => l_org,
p_operating_unit => NULL,
p_api_version_number => l_api_version_number,
p_header_rec => l_header_rec,
p_line_tbl => l_line_tbl,
p_action_request_tbl => l_action_request_tbl,
— OUT variables
x_header_rec => l_header_rec_out,
x_header_val_rec => l_header_val_rec_out,
x_header_adj_tbl => l_header_adj_tbl_out,
x_header_adj_val_tbl => l_header_adj_val_tbl_out,
x_header_price_att_tbl => l_header_price_att_tbl_out,
x_header_adj_att_tbl => l_header_adj_att_tbl_out,
x_header_adj_assoc_tbl => l_header_adj_assoc_tbl_out,
x_header_scredit_tbl => l_header_scredit_tbl_out,
x_header_scredit_val_tbl => l_header_scredit_val_tbl_out,
x_line_tbl => l_line_tbl_out,
x_line_val_tbl => l_line_val_tbl_out,
x_line_adj_tbl => l_line_adj_tbl_out,
x_line_adj_val_tbl => l_line_adj_val_tbl_out,
x_line_price_att_tbl => l_line_price_att_tbl_out,
x_line_adj_att_tbl => l_line_adj_att_tbl_out,
x_line_adj_assoc_tbl => l_line_adj_assoc_tbl_out,
x_line_scredit_tbl => l_line_scredit_tbl_out,
x_line_scredit_val_tbl => l_line_scredit_val_tbl_out,
x_lot_serial_tbl => l_lot_serial_tbl_out,
x_lot_serial_val_tbl => l_lot_serial_val_tbl_out,
x_action_request_tbl => l_action_request_tbl_out,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
— CHECK RETURN STATUS
IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
IF (l_debug_level > 0) THEN
DBMS_OUTPUT.PUT_LINE(‘Sales Order Successfully Created’);
END IF;

COMMIT;
ELSE
IF (l_debug_level > 0) THEN
DBMS_OUTPUT.PUT_LINE(‘Failed to Create Sales Order’);
END IF;
ROLLBACK;
END IF;
END LOOP;
— DISPLAY RETURN STATUS FLAGS
if (l_debug_level > 0) then
DBMS_OUTPUT.PUT_LINE(‘Process Order Return Status is: ========>’ l_return_status);
DBMS_OUTPUT.PUT_LINE(‘Process Order msg data is: ===========>’ l_msg_data);
DBMS_OUTPUT.PUT_LINE(‘Process Order Message Count is:=======>’ l_msg_count);
DBMS_OUTPUT.PUT_LINE(‘Sales Order Created is:===============>’ to_char(l_header_rec_out.order_number));
DBMS_OUTPUT.PUT_LINE(‘Booked Flag for the Sales Order is:======>’ l_header_rec_out.booked_flag);
DBMS_OUTPUT.PUT_LINE(‘Header_id for the Sales Order is:========>’ l_header_rec_out.header_id);
DBMS_OUTPUT.PUT_LINE(‘Flow_Status_Code For the Sales Order is=>:’ l_header_rec_out.flow_status_code);
END IF;
— DISPLAY ERROR MSGS
IF (l_debug_level > 0) THEN
FOR i IN 1 .. l_msg_count LOOP
oe_msg_pub.get(
p_msg_index => i
,p_encoded => Fnd_Api.G_FALSE
,p_data => l_data
,p_msg_index_out => l_msg_index);
DBMS_OUTPUT.PUT_LINE(‘message is:’ l_data);
DBMS_OUTPUT.PUT_LINE(‘message index is:’ l_msg_index);
END LOOP;
END IF;
IF (l_debug_level > 0) THEN
DBMS_OUTPUT.PUT_LINE( ‘Debug = ‘ OE_DEBUG_PUB.G_DEBUG);
DBMS_OUTPUT.PUT_LINE( ‘Debug Level = ‘ to_char(OE_DEBUG_PUB.G_DEBUG_LEVEL));
DBMS_OUTPUT.PUT_LINE( ‘Debug File =’ OE_DEBUG_PUB.G_DIR’/’OE_DEBUG_PUB.G_FILE);
OE_DEBUG_PUB.DEBUG_OFF;
END IF;
END;