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;