Supplier Conversion in Oracle Apps 11i / r12

If you can solve a problem with single SQL query instead of writing long PL/SQL procedure, you will sound as cool as Apple fans sound when criticizing Microsoft.

I enjoy challenge of solving problems with single SQL statements. Personally, I find code written using lots of cursor, loops, IF condition and exceptions too difficult to maintain.

Here is a Supplier conversion program that I wrote recently using mostly SQL statements.
Data File Validations
Doing null validation for staging table fields using single DML statement

One boring way to do validation is by using PL/SQL code. Open staging table cursor, check for each field value and update error message field.

But same thing can be achieved using single update statement.

UPDATE xxxav_sup_stg_tab xsst

SET vendor_error =

(SELECT DECODE (

a.err_msg,

NULL, NULL,

SUBSTR (a.err_msg, 1, LENGTH (a.err_msg) - 1)

|| ' SHOULD NOT BE NULL')

error_msg

FROM (SELECT DECODE (vendor_name,

NULL, ' Vendor Name,',

NULL)

|| DECODE (vendor_type_lookup_code,

NULL, ' Vendor Type lookup,',

NULL)

|| DECODE (vendor_site_code,

NULL, ' Vendor Site Code,',

NULL)

|| DECODE (vendor_type_dff,

NULL, ' Vendor Type DFF,',

NULL)

|| DECODE (lgcy_vendor_ref2,

NULL, 'Legacy Vendor REF2,',

NULL)

err_msg,

ROWID line_no

FROM xxxav_sup_stg_tab) a

WHERE a.line_no = xsst.ROWID);

The innermost SQL subquery will check each column for null value and if value is null it will concatenate all error messages and updates error_message column.

Duplicate Record Validation on staging table

If unique id is assigned to all rows in staging table, we can join

UPDATE xxxav_sup_stg_tab out

SET site_error =

site_error

|| ' Duplicate Line for vendor and site combination'

WHERE EXISTS

(  SELECT vendor_name, vendor_site_code

FROM xxxav_sup_stg_tab inn

WHERE inn.vendor_name = out.vendor_name

AND inn.vendor_site_code = out.vendor_site_code

and inn.record_id <> out.record_id);

Staging Table Validations

Populate Derived Columns in Staging table

For almost all values that are inserted in interface tables, I like to keep one derived column in staging table like for vendor type  value coming in file, I will create one corresponding derived column o_vendor_type_lkp_code in staging table.
This ensure that we are inserting all validated column values in interface table and makes code easy to debug. This also helps to separate value derivation from error message updates.

Populate derived Column values using Single SQL statement

UPDATE xxxav_sup_stg_tab xsst

SET o_vendor_type_lookup =

DECODE (

xsst.vendor_type_lookup_code,

NULL, NULL,

(SELECT lookup_code

FROM po_lookup_codes

WHERE     xsst.vendor_type_lookup_code IS NOT NULL

AND lookup_type = 'VENDOR TYPE'

AND (displayed_field) = xsst.vendor_type_lookup_code)),

o_tax_type =

DECODE (xsst.awt_group_name,

NULL, NULL,

(SELECT income_tax_type

FROM ap_income_tax_types aitt

WHERE     1 = 1

AND xsst.awt_group_name IS NOT NULL

AND xsst.awt_group_name) = (aitt.income_tax_type)),

o_inspection_flag =

DECODE (UPPER (xsst.match_option),

NULL, 'N',

'2-WAY', 'N',

'3-WAY', 'N',

'4-WAY', 'Y',

NULL),

o_receipt_required_flag =

DECODE (UPPER (xsst.match_option),

NULL, 'Y',

'2-WAY', 'N',

'3-WAY', 'Y',

'4-WAY', 'Y',

NULL)

WHERE o_vendor_id IS NULL;

 Update All validation Errors

UPDATE xxxav_sup_stg_tab xsst

-- oracle Name

SET vendor_error =

vendor_error

|| (SELECT ' Mismatch Vendor number and Name'

FROM po_vendors pv

WHERE     pv.vendor_id = xsst.o_vendor_id

AND pv.segment1 <> xsst.oracle_vendor_number

AND xsst.o_vendor_id IS NOT NULL)

|| DECODE (

xsst.vendor_type_dff,

NULL, NULL,

DECODE (o_vendor_type,

NULL, ' Invalid Vendor Type',

NULL))

|| DECODE (

xsst.vendor_type_lookup_code,

NULL, NULL,

DECODE (o_vendor_type_lookup,

NULL, 'Invalid Vendor Type lookup code',

NULL))

|| DECODE (

xsst.awt_group_name,

NULL, NULL,

DECODE (xsst.o_tax_type,

NULL, ' Invalid AWT Group name',

NULL))

|| (SELECT ' Invalid Fedral Flag'

FROM DUAL

WHERE UPPER (xsst.federal_reportable_flag) NOT IN

('Y', 'N')

AND federal_reportable_flag IS NOT NULL)

|| (SELECT ' Invalid State Report Flag'

FROM DUAL

WHERE UPPER (xsst.state_reportable_flag) NOT IN

('Y', 'N')

AND state_reportable_flag IS NOT NULL)

|| DECODE (

UPPER (xsst.federal_reportable_flag),

'Y', DECODE (xsst.awt_group_name,

NULL, ' Missing Awt group name',

NULL),

NULL)

|| DECODE (

xsst.match_option,

NULL, NULL,

(SELECT ' Invalid Match Approval Level'

FROM DUAL

WHERE UPPER (xsst.match_option) NOT IN

('2-WAY', '3-WAY', '4-WAY')))

WHERE o_vendor_id IS NULL;

Keep Log function separate instead of hard coding fnd_file.put_line(fnd_file.LOG,v_message); or dbms_output.put_line();

PROCEDURE LOG (MESSAGE VARCHAR2)

AS

BEGIN

--      DBMS_OUTPUT.put_line (MESSAGE);

fnd_file.put_line (fnd_file.LOG, MESSAGE);

END;

Performance Tuning Oracle Reports and Programs

Here are some of the performance tuning tips and tricks that I have learned over time. Most of them will work some of the time and  some of them will work most of the time.

People often ask me - Have you tried using temporary table for Oracle reports which has performance issue or have I analysed trace file of reports. But the truth is that most of the time analysing current query in toad proves enough to find bottleneck of the program. I submit the Oracle report, and in toad window I monitor corresponding process to check for running SQL query. I watch the queries which are executing and the plan which is being used for the query. Queries which test your patience will be the queries which needs your attention. I start from these SQL statements and I try to Optimize them.

1) Do not use truncate function on database Date column instead use timestamp or explicit expression for date comparisons.

problems with date comparisons

Like  transaction_date of rcv_transactions is an indexed column, so if we use truncate on transaction_date, index will not be used.

Instead of trunc(transaction_date) = '12-JUL-2013' use transaction_date between to_date('12-JUL-2013 00:00:00','DD-MON-YYYY HH24:MI:SS') and to_date('12-JUL-2013 23:59:59','DD-MON-YYYY HH24:MI:SS')
similarly for creation_date of rcv_shipment_headers

2) Use same data type as index column type in expressions

If indexed column is number and expression uses string, index is used
if indexed column is string and number is used in expression , index is skipped

3) Use lexical parameters to build query, this will improve performance most of the time.

4) Using Index is not always good. People would often say index will lead to performance improvement. But using index of on large table instead of performing full table scan of small table may some time prove otherwise.
Modify query to use  full table scan of small table instead of using index on big table and check run time.

If temporary table is being used in report and some table join is used with large tables, do not use large table indexes instead force full table scan of temporary table.

5) When checking for existing values, like if  purchase order exists for a vendor
Use rownum in query. Instead of

select count(*) from po_headers_all where vendor_id = <>

use select 1 from po_headers_all where vendor_id = <> and rownum <2

6) Mention all column joins explicitly in SQL query.

7) Less cost doesn't always ensures improved performance

parent_transaction_id = -1 on rcv_transaction shows very less cost but takes lot of time similarly, queries with rownum <2 will show very less cost.

8) For excel reports, if post processing time is more, then generating delimited file using PL SQL package will improve performance.
To check for how much time  a program has spent in post processing,  check  fnd_concurrent_requests.PP_START_DATE column

9) Remember if report is run with trace enabled, queries will always perform hard bind.

We had a strange issue wherein after enabling trace, the program was completing faster. We could not find out the reason as to why this is happening, but I think it was related to hard bind performed each time.
The report was using bind variable and there are some known problem of bind variable peeking.

11) Using sub query in FROM clause is expensive Instead try using sub query in select clause with where condition if you want to select only few values in main select clause.

12) Do not user formula column which are header dependent at line level. header level details should be fetched only once.

13) Distinct clause is little tricky. Because even if query is wrong sometimes, it conceals the problem and presents correct output. If you have missed some join in a query with distinct clause, then it might give correct output, but performance is highly impacted.

I worked on one performance tuning of one report recently. It was taking huge time for one particular Org. I identified the query which was taking long time. When I analyzed the query I found that instead of using org dependent view, query was using _all table with no where condition for org_id. The output of report was coming as expected but in background the query was killing performance.

 

How to get more Facebook Likes and Followers

This lazy weekend, while browsing for random stuff, I came across this website which allowed users to exchange likes, follow, comments for some popular social networking websites.

So, I decided to try it. To see if it actually works, I created an account on like4like.org and created one Pinterest account to verify it.

On this website, you have to give your profile link which you want others to follow, or to like. I provided my Pinterest page url, and then started following other users on Pinterest through this website.

In 2-3 minutes, I followed some 5-7 users and keenly refreshed my Pinterest page. To my surprise, I got my first follower in 4 min time. Now, I wanted to see how fast can I get followers so, I started following more users and within some 1 hour I had around 15 followers. Not Bad!

Exchanging back-links has been there for long time to improve Google page rank. And it was a successful method until one day, when Google decided to give negative score for black-hat SEO method. Well, for now this method works.

Html5 Game Programming Tutorial for Beginners Part 2 - Keyboard and Bullets

In this part, we will load an image object, control its movement using keyboard, and then we will arm our hero with a loaded gun.

So, time to bring our hero in picture. I will load the image saved in my local folder.

Load Image

[code language="javascript"]
//create init function to initialize image loading
var img = new Image();
var imgLoaded = false;
img.onload = function() {
imgLoaded=true;
}
img.src = 'images/hero.png';
[/code]

After loading the image, we need to draw it on canvas

[code language="javascript"]context.drawImage(img,x,y);[/code]

Create Hero Object

To keep code sane, we will create hero object that will hold all properties and methods of hero. If you are new to Objects in javascript refer Working with objects

[code language="javascript"]
var hero = {
x: 50,
y: 50,
width:img.width,
height:img.height,
draw: function() {
if(imgLoaded){
context.drawImage(img,this.x,this.y);
}
}
};

[/code]

Html5 Game Programming Tutorial for Beginners Part 1 - Drawing on Canvas

I will document my learning of game programming here. I don't like writing long paragraphs so, I will just note necesary things here . Let's roll

Creating canvas 

Canvas is a rectangular space on page, which is blank until you draw something on it. We can draw stuff on canvas using JS.  

Now, lets go ahead and create a canvas and draw some text on it.

We can define canvas in  html page or we can create it using JS function.
In HTML

[code language="html"]

<canvas id="<span class=">myCanvas" width="500" height ="300">Canvas not supported</canvas></canvas>

[/code]

and save the canvas object in JS variable

[code language="javascript"]

canvas= document.getElementById("myCanvas");

[/code]

In JS

[code language="javascript"]

var canvas = document.createElement('canvas');
canvas.id = 'myCanvas';
canvas.width = 1224;
canvas.height = 768;

[/code]