What is SQL* Loader?

What is SQL*Loader?
SQL*loader is one of the Oracle tool which will be used to transfer the data from Flat-File to oracle Database table.

Which files in SQL*loader?
1. Flat or Data File
2. Control File
3. Bad File
4. Discard File
5. Log File

What is Flat Or Data File: This file contains the records in a special format; these records will be fetching for other legacy. The extension of these files might be .dat, .txt, or .csv (comma separated view).

What is Control File: This is SQL loader execution file, which will be used to transfer the date from file to table. In side of these control file, we will mention the Data file path, table name, column mapping. The extension of control file is .ctl


Control File Creation:

Load data
INFILE ‘Data File Path’
INSERT INTO ‘Table Name’
FIELD TERMINATED BY ‘,’
WHERE deptno = 10
TRAILING NULL COLS
(column1 , empno
column2, ename
column3, deptno)

Once we develop the control file we will execute this by using fallowing command
C:\> sqlldr user/passward @ Database Control = name of control file (with extension .ctl)
This command will start the control file execution, and it will try to read the data and inserting into table. After completion of this execution, automatically three files will gets created
Bad file
Discard file
Log file

Bad File: Bad file contain the records, which are rejected by the SQL*loader. SQL*loader will reject the records, when ever the Flat file format is not correct or if any internal error occurs it will rejected. The extension of bad file is .bad

Discard File: Discard file contains the records which are rejected by the control file, control file reject the records, if record is not satisfying the conditions, which we have mentioned inside of control files the extension of discard file is .dis

Logfile: It contains the complete info of the process, like no of records successfully loaded in to the table
No of records successfully loaded in to the bad file & discard file.
And where the bad, discard file gets created and time taken to complete the process.
Taking the complete log.
SQL* Loader Modes:
INSERT
APPEND
REPLACE
We can replaced the data in to the table by using any one of the allowing method

INSERT: When we are using this statement, table should be empty. SQL * loader will insert the new data form the file.

APPEND: This mode will be use to attach the new record to the existing records.

REPLACE: This will replace the existing records with new records.
C:\> sqlldr userid/passward@Database control=text1.ctl path=direct


SQL* Loader Paths: We can execution SQL* loader in two paths or nodes
Direct
Conventional
By default SQL*loader will be running in conventional mode, if we want to run in direct mode will use the fallowing syntax
C:\> sqlldr userid/passward@Database control=text1.ctl path=direct
Direct mode will disable the table and column constrains and it will insert the data.
Conventional path will check every constrains, if it is satisfied it will insert the record
Conventional path is just like ‘insert statement’
SQL Commands Limitations:
to_date, to_char, upper, lower, Initcap, string, decode, nvl
when clause
sequence_name.next_value, Ref-Cursor
sysdate, ltrim, rtrim, constant

How to to make a PL/SQL procedure as a Concurrent Program?

PL/SQL Stored Procedures :
If you want to make a PL/SQL procedure as a Concurrent Program, then we will define that procedure by using fallowing syntax

Syntax:
CREATE OR REPLACE PROCEDURE Procedure_Name
(errbuf OUT VARCHAR2,
recoded IN VARCHAR2,
x IN NUMBER,
y IN NUMBER) AS
BEGIN
PL/SQL statements;
Fnd_file.put_line (fnd_file.output, ’message’variables);
Fnd_file.put.line (fnd_file.log, ’message’variables);
END ;

ERRBUF: Used to get the error messages in to the log file if any errors occur in side of procedure.
RETCODE: Used to get the status of Concurrent Program

The Status can be either 0 – for success
1 – for warning
2 – for error

Inside of procedure body we can use all valid PL/SQL statements except DBMS_OTUPUT.PUT_LINE Instead of this we will use fallowing to API’S (Application Programming Interface).
API is nothing but a package.
• Fnd_file.put_line(fnd_file.output,’message’variables); - is write for the output file.
• Fnd_file.put.line(fnd_file.log,’message’variables); - is used for log file.

Steps for Developing the Procedure:
1. Develop the procedure as per client requirement.
2. Create an executable with execution method as PL/SQL stored procedure
3. Define the Concurrent Program at as
• EXECUTION
• PARAMETER
• INCOMPATIBILITIES PROGRAM
4. Attach the Concurrent Program to the request group.
5. Attach the request group to the responsibility.
6. Attach the responsibility to user.
7. User will submit program from SRW window


Example for ErrorCode and retCode:

PROCEDURE Load_Cust_Item(ERRBUF OUT VARCHAR2,
                  RETCODE OUT VARCHAR2,
                  ARGUMENT1 IN VARCHAR2,
                  ARGUMENT2 IN VARCHAR2) IS

    L_Retcode Number;
        CONC_STATUS BOOLEAN;
BEGIN

    L_Retcode := Load_Cust_Items_Iface(argument1,
                               argument2);

    if L_Retcode = 0 then
        RETCODE := 'Success';
                CONC_STATUS := FND_CONCURRENT.SET_COMPLETION_STATUS('NORMAL',Current_Error_Code);
    elsif L_Retcode = 1 then
        RETCODE := 'Warning';
                CONC_STATUS := FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING',Current_Error_Code);
    elsif L_Retcode = 2 then
        RETCODE := 'Error';
                CONC_STATUS := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',Current_Error_Code);
    end if;

END Load_Cust_Item;

How to Run the SQL script from the Concurrent Program?

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

What Are Standards for Report Developments in Oracler Apps?

Oracle Applications Standards for New Report Developments...
For developing a Report in Oracle Applications we should follow three standards.
1. Creation of Bind Variable - P_CONC_REQUEST_ID:
We must create a Bind Variable called “P_CONC_REQUEST_ID” (We can’t change this name. It is standard name.). If we run Conc. Prgm. from SRS window, it will give a Request ID. It will get store in ‘P_CONC_REQUEST_ID” automatically. This Bind Variable is useful, when we call another Conc. Prgm. with in a Conc. Prgm.

2. FND SRWINIT in Before Report Trigger:
We call the USER_EXIT (‘FND SRWINIT’) form Before Report Trigger. Syntax is
SRW.USER_EXXIT(‘FND SRWINIT’):
This USER_EXIT is initializing the user profiles in the report trigger i.e., before getting the date from the Database.
Note:
While executing the Conc. Prgm. the system allocate memory for the program which contains all details of user. In above syntax,
SRW.USER_EXIT refers to D2K and purpose of this is, when we want to transfer the control from execution of report to other 3rd generation language and again transfer the control to report execution.
FND SRWINIT refers to Oracle Applications. Purpose of this is to get the “User Profile”.

3. FND SRWEXIT in After Report Trigger:
We call the USER_EXIT (‘FND SRWEXIT’) form After Report Trigger. Syntax is
SRW.USER_EXXIT (‘FND SRWEXIT’):
This USER_EXIT is frees the memory which is occupied by user profiles.

Navigation Path for Value Set Creation?

Navigation Path for Value Set Creation:
Application-> Validation -> Set.
Once we create Independent & Dependent valueset then we can attach values to the valueset by using the following Navigation.
Application ->
-> Validation
-> Values (To create values for value set)
NOTE: Once we attach any value to Independent & Dependent we can’t delete that value, but we can disable that value. Duplicate values are not allowed in list of values.

Develop a Report using Query and by creating valueset :
Select USER_ID, USERNAME From FND_USER Where
USER_ID Between :X AND :Y
:$FLEX$ - It is One of the Oracle applications Key word which we use to get the prevents parameter value in current list of values “WHERE Clause”. We can be use Table Values in the “Where Clause Box”.
Query Using: $FLEX$
Select
VENDOR_SITE_ID
From
PO_VENDOR_SITE_ALL
Where
VENODR_ID = :$FLEX$.VEN_TABLE
Note: We can give Where Clause Condition in creation of Second Value Set.
Practical:
Query:
Select *
From ORF_ORGANIZATION_DEFINATIONS
Where ORGANIZATION_ID = :P_ORG_ID
And BUSINESS_GROUP_ID = :P_BUSINESS_GROUP_ID

In Where Clause write the statement as
Where
BUSINESS_GROUP_ID = :$FLEX$.BUISINESS_GROUP

Range:
When ever we have to restrict the user with in the given values we use Range. For example when ever our parameter is having “From Date and To Date” we have to use Range option to restrict the user to enter the values between Low and High.
Note:
Pre defined value set for date is “FND_DATE” and its default format is “DD-MON-YY”.Alias name is mandatory when we are specifying ‘:$FLEX$’ and Column Name in ‘Additional Column’.

What is Value Sets? What are Types Of Value Sets?

Oracle Application Object Library uses values; value sets and validation tables as important components of key FLEXFIELDs, descriptive FLEXFIELDs, and Standard Request Submission. This section helps you understand, use and change values, value sets, and validation tables. When you first define your FLEXFIELDs, you choose how many segments you want to use and what order you want them to appear. You also choose how you want to validate each of your segments. The decisions you make affect how you define your value sets and your values. You define your value sets first, either before or while you define your FLEXFIELD segment structures. You typically define your individual values only after your FLEXFIELD has been completely defined (and frozen and compiled). Depending on what type of value set you use, you may not need to predefine individual values at all before you can use your FLEXFIELD.
You can share value sets among segments in different FLEXFIELDs, segments in different structures of the same FLEXFIELD, and even segments within the same FLEXFIELD structure. You can share value sets across key and descriptive FLEXFIELDs. You can also use value sets for report parameters for your reports that use the Standard Request Submission feature.
Because the conditions you specify for your value sets determine what values you can use with them, you should plan both your values and your value sets at the same time. For example, if your values are 01, 02 instead of 1, 2, you would define the value set with Right–Justify Zero–fill set to Yes.
Value set is nothing but List of Values with validations. We can use the Value Sets when ever the Concurrent Program has parameters and while defining the Flex Fields. We have to attach the value sets to the Concurrent Program. Validations are depending on Client Requirement.

Value sets are of 8 types.
There are several validation types that affect the way users enter and use segment or parameter values:
1. None (not validated at all)
2. Independent
3. Dependent
4. Table
5. Special (advanced)
6. Pair (advanced)
7. Translatable Independent
8. Translatable Dependent

You cannot change the validation type of an existing value set, since your changes affect all FLEXFIELDs and report parameters that use the same value set.

None: You use a None type value set when you want to allow users to enter any value so long as that value meets the value set formatting rules. That is, the value must not exceed the maximum length you define for your value set, and it must meet any format requirements for that value set. For example, if the value set does not allow alphabetic characters, your user could not enter the value ABC, but could enter the value 456 (for a value set with maximum length of three). The values of the segment using this value set are not otherwise validated, and they do not have descriptions. Because a NONE value set is not validated, a segment that uses this value set does not provide a list of values for your users. A segment that uses this value set (that is, a non–validated segment) cannot use FLEXFIELD value security rules to restrict the values a user can enter.

Independent > An Independent value set provides a predefined list of values for a segment. These values can have an associated description. For example, the value 01 could have a description of ‘Company 01’. The meaning of a value in this value set does not depend on the value of any other segment. Independent values are stored in an Oracle Application Object Library table. You define independent values using an Oracle Applications window, Segment Values.

Table > A table–validated value set provides a predefined list of values like an independent set, but its values are stored in an application table. You define which table you want to use, along with a WHERE cause to limit the values you want to use for your set. Typically, you use a table–validated set when you have a table whose values are already maintained in an application table (for example, a table of vendor names maintained by a Define Vendors form). Table validation also provides some advanced features such as allowing a segment to depend upon multiple prior segments in the same structure.

Dependent > A dependent value set is similar to an independent value set, except that the available values in the list and the meaning of a given value depend on which independent value was selected in a prior segment of the FLEXFIELD structure. You can think of a dependent value set as a collection of little value sets, with one little set for each independent value in the corresponding independent value set. You must define your independent value set before you define the dependent value set that depends on it. You define dependent values in the Segment Values windows, and your values are stored in an Oracle Application Object Library table.

Special and Pair Value Sets:
Special and pair value sets provide a mechanism to allow a”FLEXFIELD–within–a–FLEXFIELD”. These value sets are primarily used for Standard Request Submission parameters. You do not generally use these value sets for normal FLEXFIELD segments. Special and Pair value sets use special validation routines you define. For example, you can define validation routines to provide another FLEXFIELD as a value set for a single segment or to provide a range FLEXFIELD as a value set for a pair of segments.

Translatable Independent and Translatable Dependent :
A Translatable Independent value set is similar to Independent value set in that it provides a predefined list of values for a segment. However, a translated value can be used. A Translatable Dependent value set is similar to Dependent value set in that the available values in the list and the meaning of a given value depend on which independent value was selected in a prior segment of the FLEXFIELD structure. However, a translated value can be used. FLEXFIELD Value Security cannot be used with Translatable Independent or Translatable Dependent value sets. For format validation, translatable value sets must use the format type Char. The maximum size must be no greater than 150. The Number Only option and the Right–justify and Zero–Fill Numbers option cannot be used with translatable value sets. Range FLEXFIELDs cannot use Translatable Independent or Translatable Dependent value sets.

How to Develope Report with out Parameters?

Developing Report with out Parameters:
To develop any report in Report-6i tool, select “Build a new Report Manually” option.

Select SQL query object and write the Select Statement.

It will ask the Database connection. Provide User id, Password and Connection string (DB host) – APPS/APPS@ORCL.

Create Layout manually according to the given query.
Compile the report with out errors.
Save the report in Local Machine.

Copy the Report and paste it in respective path (Respective TOP).
Path - APPS/ D / Oracle / PROD_APPL / PO / 11.5.0 / Reports / US / .rdf
Implementing Report in Oracle Applications:
Connect to APPS Server.
Create Executable for created report.
Navigation: System Administrator
->Responsibility
-> Concurrent Program
->Executable
Mandatory Fields in this window are: Executable, Short Name, Application, Execution File Name
Note: From here we can refer the Report (Executable) with Short Name;

Create the Concurrent Program for this Executable.
Navigation: System Administrator
->Concurrent
-> Program
-> Define
Give the required information like Program, Short Name, Application, Executable (Name, Method), Output (Format…) etc.

Create Request Group.
Navigation: Security
-> Responsibility
-> Request
Here we have to give information like Group (RG Name), Application (E.g. Oracle Purchasing)
Add the Concurrent Program as Request to Request Group which is created.

Create Responsibility.
Navigation: Security
-> Responsibility
->Define
While creating Responsibility we need to mention three things.
· Data Group – Required. It is nothing but Oracle User ID
· Menu – Required. Collection of Forms, which are related to specific business
· Request Group – Optional. Collection of Concurrent Programs and Reports which are related to specific business
Note: If we create Responsibility with Request Group, no need to add RG again to Responsibility.

Create User.
Navigation: Security
-> User
-> Define
Attach the Responsibility to User.
Run the Concurrent Program from SRS window.
Note: All the Concurrent Programs should run from SRS (Standard Request Submission) window.

How to go to SRS window:
Select relative Responsibility (FILE à Switch Responsibility)
Go to View à Requests à Submit New Request à Select the Report à Click Submit
After Submission it will give Request ID. Here 2 fields View Report, View LOG File will give information of the Resultant file created by Concurrent Program.
Note:
· We can find all O/P and LOG files by the following query from the Database.
Select LOGFILE_NAME, OUTFILE_NAME
From FND_CONCURRENT_REQUESTS
Where REQUEST_ID = ‘XXX’;
· We can’t delete Concurrent Program but we can disable it.
· Columns and Rows fields will be used to mention the O/P file Columns and Rows.
· Save, This Check Box can use to save the O/P file in the server.
· Use in SRS, if we uncheck this Check Box, then this we can’t run this program from SRS window.
· Copy To, We can create new for the existing Report.
Scheduling the Concurrent Program:
We can schedule the Concurrent Program in 4 ways.
· As Soon As Possible (Default)
· Once
· Periodically
· On Specific Days
We can save the Schedule and can apply this Schedule to any other Concurrent Program.
We can run with a message in the LOG File by using SRW.MESSAGE() function.
Develop Report with Parameters:
There are 2 types of Parameters.
1. Bind parameters
2. Lexical Parameters
Bind Parameter: Bind parameter is a variable which we will use to pass the value. We should use ‘:’ before any variable in a query.
Lexical parameter: Lexical parameter is a parameter which we will use inside of a query. By using this parameter we can replace any clause or any where inside of the statement like Select, From, Where, Order By clauses.
If our report has parameters then we should define parameters from the report while creating Concurrent Program. While defining the parameters we should mention following fields.
It is a list of values with validations. We have to use value set for a parameter. By using this we can restrict the invalid entries by the end-user.

Prompt: This field will use to display the string while submitting the Concurrent Program in the Parameter Form.

Token: It is a field which is use to give the link between Concurrent Program Parameter and report Bind Variable. When we create Bind Variables in report those may or may not be in the same sequence. So we can map these bind Variable with the Token fields.
Required Check Box: If we uncheck this, the parameter is optional, otherwise it is mandatory.
Default Types: There are 4 default types.
· Constant
· Profile
· SQL Statement
· Segment
Constant: We can select this default type if we have to pass the constant value to parameter of the Concurrent Program. We can mention the value in the “Default Value Field” at right side.
Profile:
SQL Statement: If we have to set the default value as SQL Query result set then we should select default value type is SQL Statement and we have to enter the SQL Query in the ”Default Value field”.
Segment: If we want to get previous parameter value as default to the next parameter, we have to select the default type as Segment and we have to give the previous parameter name in “Default Value Field”.
Note: The query which we have to enter in default value field should give only one value
Using the format trigger we can hide or display the “Layout Object”. This layout object can be a Field or Text.
We can display input parameter values in the first page (or any other pages) by using Bind Variables.