Validations On Concurrent Program Parameters

Validating the Parameters in Concurrent Programs can be approached by using the Special type LOV.

Scenario:

There are two parameters From Invoice Date and To Invoice Date. The To Invoice Date should not be greater than the 90 days from the From Invoice Date.

Solution:

1. Take the From Invoice Date Parameter with FND_STANDARD_DATE Value Set.
2. Create a Special Type Value Set "XX_DATE_SPECIAL" with Action Validate.

3. Add the Below code to the code-section

FND PLSQL "DECLARE
l_frm_dte DATE:=to_date(':$FLEX$.P_FROM_INV_DATE','YYYY/MM/DD HH24:MI:SS');
l_to_dte DATE:= :!VALUE;
BEGIN
IF l_to_dte > l_frm_dte + 90 THEN
FND_MESSAGE.SET_NAME('FND','FND_GENERIC_MESSAGE');
FND_MESSAGE.SET_TOKEN('MESSAGE','Please enter date range less than 90 days.');
FND_MESSAGE.RAISE_ERROR;
END IF;
END;"

4. Create a Parameter To Invoice Date and attach the Special Value set "XX_VALIDATE_DATE90"

Inventory Item Conversions

We can import new items or update the existing items using Item import program.

Pre-requisites:

1. Master Organization should be defined
2. Child Organization should be defined
3. Code Combinations should be defined
4. Item Templates (If using item templates in interface table)
5. Defining Item Status Codes
6. Item Types should be defined
7. Items should be loaded into Master organization before assigning to child organizations

Steps Involved:

For any conversion program, we receive the CV40 (Functional Design Document) which details about the purpose and scope of the conversion, required functional setups, Interface/APIs to be used and other details.

We need to develop the CV60 (Technical Design Document) document based on CV40. CV60 document details about the approach we are following, table access details, package names, conversion program details, import program details (if applicable) etc.

Data files:

We get the legacy data in a  flat file from the customer.Flat file can be of .csv, .txt, .xls, .xml extensions

Custom Objects:

Staging Table:
Create a staging table to store the legacy data based on the flat file structure. Make sure the staging table has status column to store the record status (status column) at each stage, transaction id to identify each record uniquely and error message column to store the validation errors.

Package:

Create a package to validate the legacy data and load the data into Interface tables or to base tables if using APIs to load the data. Modify the record status after validation and load.

Different statuses a record can have:

• N - New
• V - Validation Successful
• VE - Validation Error 
• L - Load Successful
• LE - Load Error

SQL Load:

Create the control file to transfer the data from flat file to staging table. To know more about sql loader click here

SQL Loader command: sqlldr control = <ctl file name>


Validate the legacy data:

Register a concurrent program using the custom package we created. Submit the concurrent program using SRS window to validate the data. Make sure the program does all the necessary validations.

Mandatory validations for Item conversions are:

1. Item number not null
2. Description not null
3. Organization not null
4. Organization should exist in Oracle
5. Duplicate Item and Organization combination validation with in the staging table and w.r.t Oracle
6. Item Template should exist in Oracle
7. Code combination IDs should exist in Oracle
8. UOM should exist in Oracle

Update the status column in the staging table accordingly (with V or VE) at the end of validation process.


Load the data:

Pick all the successfully validated items and load them into interface table MTL_SYSTEM_ITEMS_INTERFACE. Populate the transaction_type as 'CREATE' and process_flag as '1'. If you want to submit the import program in batches, populate set_of_process_id with different numbers for each batch of import.

Update the status column in the staging table accordingly (with L or LE) at the end of load process.

Possible values for transaction_type are 'CREATE', 'UPDATE'
Possible values for process_flag are 1= Pending, 2= Assign Complete, 3= Assign/Validation Failed, 4= Validation succeeded; Import failed, 5 = Import in Process, 7 = Import succeeded


Run the Import program:

Go to SRS window and submit 'Import Items' program with appropriate parameters to load the items from Interface table to base tables.

Interface tables:
MTL_SYSTEM_ITEMS_INTERFACE
MTL_ITEM_REVISIONS_INTERFACE (If importing revisions)
MTL_ITEM_CATEGORIES_INTERFACE (If importing categories)

Base tables:
MTL_SYSTEM_ITEMS_B
MTL_ITEM_REVISIONS_B
MTL_CATEGORIES_B
MTL_CATEGORY_SETS_B
MTL_ITEM_STATUS
MTL_ITEM_TEMPLATES

Error table:
MTL_INTERFACE_ERRORS


Common Issues:

1. We cannot load master and child items in a single import program. Import the master items first and then import the child items
2. Item revision numbers should be in chronological order. For ex: AA, BB, CC... or 1, 2, 3... is valid but AA, 1, BB, 2 etc.is not valid
3. Master-Child conflicts. This happen when the master controlled attributes are different between master and child org items

Performance Tuning Tips

· Never Use the functions and calculations on Indexed Columns. 
· Sequence and Hard coded values can be at any place in 11g 
· Use the Indexed columns should be in Sequence
· Cost of the entire Query should be minimum
· Should not allow the Full Scan of a table 
· Use Exists/ Not Exists Instead of In and Not In 
· Use of Union All instead of Union 
· Use Bulk Collects for large volume of data for Inserting and Updating 
· Create Indexes for the custom tables 
· Gather Statistics 
· Performing Explain Plan, TK Prof

EMP Tables Structure and Data

CREATE TABLE EMP

(

   EMPNO      NUMBER (4) NOT NULL,

   ENAME      VARCHAR2 (10),

   JOB        VARCHAR2 (9),

   MGR        NUMBER (4),

   HIREDATE   DATE,

   SAL        NUMBER (7, 2),

   COMM       NUMBER (7, 2),

   DEPTNO     NUMBER (2)

);

INSERT INTO EMP

     VALUES (7369,

             'SMITH',

             'CLERK',

             7902,

             '17-DEC-1980',

             800,

             NULL,

             20);

INSERT INTO EMP

     VALUES (7499,

             'ALLEN',

             'SALESMAN',

             7698,

             '20-FEB-1981',

             1600,

             300,

             30);

INSERT INTO EMP

     VALUES (7521,

             'WARD',

             'SALESMAN',

             7698,

             '22-FEB-1981',

             1250,

             500,

             30);

INSERT INTO EMP

     VALUES (7566,

             'JONES',

             'MANAGER',

             7839,

             '2-APR-1981',

             2975,

             NULL,

             20);

INSERT INTO EMP

     VALUES (7654,

             'MARTIN',

             'SALESMAN',

             7698,

             '28-SEP-1981',

             1250,

             1400,

             30);

INSERT INTO EMP

     VALUES (7698,

             'BLAKE',

             'MANAGER',

             7839,

             '1-MAY-1981',

             2850,

             NULL,

             30);

INSERT INTO EMP

     VALUES (7782,

             'CLARK',

             'MANAGER',

             7839,

             '9-JUN-1981',

             2450,

             NULL,

             10);

INSERT INTO EMP

     VALUES (7788,

             'SCOTT',

             'ANALYST',

             7566,

             '09-DEC-1982',

             3000,

             NULL,

             20);

INSERT INTO EMP

     VALUES (7839,

             'KING',

             'PRESIDENT',

             NULL,

             '17-NOV-1981',

             5000,

             NULL,

             10);

INSERT INTO EMP

     VALUES (7844,

             'TURNER',

             'SALESMAN',

             7698,

             '8-SEP-1981',

             1500,

             0,

             30);

INSERT INTO EMP

     VALUES (7876,

             'ADAMS',

             'CLERK',

             7788,

             '12-JAN-1983',

             1100,

             NULL,

             20);

INSERT INTO EMP

     VALUES (7900,

             'JAMES',

             'CLERK',

             7698,

             '3-DEC-1981',

             950,

             NULL,

             30);

INSERT INTO EMP

     VALUES (7902,

             'FORD',

             'ANALYST',

             7566,

             '3-DEC-1981',

             3000,

             NULL,

             20);

INSERT INTO EMP

     VALUES (7934,

             'MILLER',

             'CLERK',

             7782,

             '23-JAN-1982',

             1300,

             NULL,

             10);

CREATE TABLE DEPT

(

   DEPTNO   NUMBER (2),

   DNAME    VARCHAR2 (14),

   LOC      VARCHAR2 (13)

);

INSERT INTO DEPT

     VALUES (10, 'ACCOUNTING', 'NEW YORK');

INSERT INTO DEPT

     VALUES (20, 'RESEARCH', 'DALLAS');

INSERT INTO DEPT

     VALUES (30, 'SALES', 'CHICAGO');

INSERT INTO DEPT

     VALUES (40, 'OPERATIONS', 'BOSTON');

CREATE TABLE BONUS

(

   ENAME   VARCHAR2 (10),

   JOB     VARCHAR2 (9),

   SAL     NUMBER,

   COMM    NUMBER

);

CREATE TABLE SALGRADE

(

   GRADE   NUMBER,

   LOSAL   NUMBER,

   HISAL   NUMBER

);

INSERT INTO SALGRADE

     VALUES (1, 700, 1200);

INSERT INTO SALGRADE

     VALUES (2, 1201, 1400);

INSERT INTO SALGRADE

     VALUES (3, 1401, 2000);

INSERT INTO SALGRADE

     VALUES (4, 2001, 3000);

INSERT INTO SALGRADE

     VALUES (5, 3001, 9999);