ORACLE APPS CONVERSIONS AND INTERFACES: 2011

Monday 11 April 2011

ORACLE PROCEDURES AND FUNCTIONS

What are Procedure and Functions?

A Procedure or Function is a logically grouped set of SQL and PL/SQL statements that perform a specific task. A stored Procedure or Function is a named PL/SQL code block that has been complied and stored in one of the Oracle engine’s system tales.

Procedures or Functions are made up of :-

Declarative Part.
Executable Part.
Optional Exception –handling part.

Advantages of using a Procedures or Functions

Security :- Stored Procedure and Function can help enforce data security.

Performance:- Amount of information sent over a network is less. No compilation step is required to execute the code.

Memory Allocation:- The stored Procedure or Function having shared memory capabilities. Only on copy of procedure or Function needs to be loaded for execution by multiple users.

Productivity:- By writing Procedure or Function redundant coding can be avoided.

Integrity:- A Procedure or Function needs to be tested only once to guarantee that it returns an accurate result.

Procedure and Function Differences?

The procedure may or may not return a value. It will return the values by its own name and function must and should return a value and it will return values with function name.

Function is executed in select statement. Procedure is called using

Exec.

A procedure is capable of performing DML operations. Where as

A function is not capable of doing DML operations except SELECT.

A Procedure reduce the code redundancy using out parameter concept where as It cannot be avoided in a Function.

A Procedure is a complete executable statement of PL/SQL block where as A Function is a part of statement of PL/SQL block.

ORACLE PLSQL EXCEPTIONS

EXCEPTIONS:-

What is Exception?

Exception is a error handing program. Which abnormally terminates

Named Exceptions or Pre-defined Exceptions:-

1. DUP_VAL_ON_INDEX ------ ORA-00001

2. LOGIN_DENIED ----- ORA-01017

3. NO_DATA_FOUND ----- ORA-01403

4. NOT_LOGGED_ON ----- ORA-01012

5. PROGRAM_ERROR ----- ORA-06501

6. TOO_MANY_ROWS ----- ORA-01422

7. VALUE_ERROR ----- ORA-06502

8. ZERO-DIVIDE ----- ORA-01476

9. INVALID CURSOR ----- ORA-01001

10. CURSOR ALREADY OPEN --- ORA-06511

What is Pragma?

Pragma is a precompiler directive, which directs the compiler before compilation of the program.

Pragma Autonomous Transaction?

Used to split the whole transaction into two parts parent and child, which run parallely and both have the relation ship

Ex: ITEM ATTCHMENTS CONVERSION

Pragma Exception Init?

Used to associate a user-defined exception with oracle-defined error.

Ex: Bank Account Min Balance

Mutating Error, Mutating table?

It will occur whenever we try to do DML operations on a table, which is effected by a trigger. The table, which is affected by trigger, is known as Mutating Error.

RAISE STATEMENT:- Raise an exception only when an error makes it undesirable or impossible to finish processing. we can code a Raise statement for a given exception any where in the scope to that exception.

Ex:- DECLARE

OUT_OF _STOCK EXCEPTION;

NUMBER_ON_HANDS NUMBER(4);

BEGIN

IF NUMBER_ON_HAND < 1 THEN

RAISE OUT_OF _STOCK;

(WE CAN RAISE THE PRE-DEFINED EXCEPTION EXPLICITLY)

END IF;

EXCEPTION

WHEN OUT_OF_STOCK THEN

HANDLE THE ERROR

END;

PLSQL TRIGGERS

TRIGGER’S:-

What is a Trigger ?

A piece of logic that is executed at or triggered by a SQL *forms event.

Types of Triggers?

1.   Row Triggers:- A row Trigger is fired each time a row in the table is affected by the triggering statement.

2.   Statement Triggers:- It is fired once on behalf of the triggering statement, independent of the number of rows the triggering statement affects.

3.   Before Triggers:-  1. Before row Trigger  2. Before statement Trigger.

4.   After Triggers:- 1. After row Trigger 2. After statement Trigger.

Database triggers are event based objects which means, execution dependson the event attached to them. These events are categorized into 4 groups :

DML EVENTS.........INSERT, UPDATE, DELETE [DML TRIGGERS]

               These triggers are used in response  to  the

               Insert, Delete, Update statements on a table.

Syntax:

CREATE OR REPLACE TRIGGER <Trig.Name>

<execution time>

<event list>

ON <table name>

[FOR EACH ROW]      .... Trigger type

[WHEN <condition>]  ....   ,, condition

<plsql anonymous block> ;

These triggers always are executed before the event and

are row level triggers.

DDL EVENTS.........CREATE, ALTER, DROP    [DDL TRIGGERS]

               These triggers allow you to write a code that

               is invoked in response to the DDL statements.

Syntax:

------

 CREATE OR REPLACE TRIGGER <Trig.name>

 BEFORE | AFTER

 CREATE OR ALTER OR DROP ON {DATABASE | <Schema.SCHEMA>}

 <pl/sql block>;

INSTEAD-OF EVENTS..VIEW      [INSTEADOF TRIGGERS]

          These triggers allow you to write a code that

                is invoked when a user tries to insert, update

                or delete through a view.

Syntax:

CREATE OR REPLACE TRIGGER <Trig.Name>

INSTEAD OF <DML events> ON <view name>

FOR EACH ROW

[WHEN <condition>]

<pl/sql block>;

DATABASE EVENTS....STARTUP, SHUTDOWN, LOGON,

                                      LOGOUT,  SERVER ERROR

                                      [DATABASE EVENT TRIGGERS]

                    These triggers allow you to write a code that

          is invoked in response to database events such

          as startup, shutdown etc.

RAISE_APPLICATION_ERROR Procedure?

Oracle engine provides a procedure name RAISE_APPLICATION_ERROR that

Allows programmers to issue user-defined error message.

Syntax:- RAISE_APPLICATION_ERROR(<error number>, <message>);

Where error number is a negative integer in the range -20000 to

-20999. and message is a character string upto 2048 bytes.

RAISE_APPLICATION_ERROR is used in Data base Triggers.

PLSQL CURSORS

CURSORS:-

Cursor is a method to access more than one record from database. It will allocate an area of memory known as context area. It contains information necessary information including the no of rows to be processed.

Types of CURSOR’S :-

Implicit Cursor:- It is used for all SQL Statements (DML). It is opened and closed by the PL/SQL Engine Implicitly.

Explicit Cursor :- It has to be define Explicitly we have to do

1. Declare the Cursor.

2. Open the Cursor for a Query.

3. Fetch the records into the PL/SQL variables.

4. Close the Cursor.

STEPS REQUIRED FOR CURSOR MANAGEMENT

1) Declaration Of Cursor.

Cursor name and select statement for the cursor

to be provided in DECLARE division. During this

plsql keeps information about cursor only.

CURSOR <cursor name> IS <query>;

2) Opening of Cursor.

During this step, plsql performs the following:

- allocation of memory for cursor.

- retrieval of records into cursor from db.

- Positioning record pointer at 1st record.

OPEN <cursor name>;

3) Fetching record values from cursor into plsql

variables.

Values from current record are retrieved from

cursor into plsql variables and record pointer

is moved to next record.

FETCH <cursor name> INTO <variable list>;

4) Closing of Cursor.

During this, plsql removes memory reserved for

cursor so thelink between db & cursor is gone.

CLOSE <cursor name>;

CURSOR ATTRIBUTES

These are system variables provided by plsql to

keep the cursor status. These are :

1. %FOUND :- Keeps boolean value that indicate whether

fetch is successful or failure. If TRUE

then success and if FALSE failure.

2. %NOTFOUND :- reverse of the above.

3. %ROWCOUNT :- Keeps the number value that indicate how

many records have been fetched. In other

words current record number which always

start from 1.

4. %ISOPEN :- Keeps boolean value to indicate whether

cursor is open or close. True for Open &

False for Not Open.

These are used with cursor name. The syntax is:

<cursor name><attribute name>;

Ex: C1%Found;

LIMITATIONS:-

1) CURSOR is READ only. That means DML operations

are not allowed using CURSOR. Only records can

be retrieved.

2) CURSOR is FORWARD only. i.e. record pointer in

cursor can not move back to previous records.

Thursday 24 March 2011

HR CREATE NEW EMPLOYEE

DECLARE
x_emp_num VARCHAR2(200) := null ;
x_business_group_id NUMBER:=202;
-- x_person_type_id    NUMBER:=13;
x_validate_mode     BOOLEAN := FALSE;
x_person_id NUMBER ;
x_assignment_id NUMBER ;
x_per_object_version_number NUMBER;
x_asg_object_version_number NUMBER;
x_per_effective_start_date DATE;
x_per_effective_end_date    DATE;
x_full_name                 VARCHAR2(300);
x_per_comment_id            NUMBER;
x_assignment_sequence       NUMBER;
x_assignment_number         VARCHAR2(10);
x_name_combination_warning BOOLEAN := FALSE;
x_assign_payroll_warning    BOOLEAN := FALSE;
x_orig_hire_warning         BOOLEAN := FALSE;
BEGIN
/*SELECT business_group_id
INTO x_business_group_id
FROM per_business_groups
WHERE NAME = 'Vision Corporation';
SELECT ppt.person_type_id
INTO x_person_type_id
FROM per_person_types ppt
WHERE ppt.business_group_id = x_business_group_id
AND ppt.user_person_type = 'Employee';*/
DBMS_OUTPUT.PUT_LINE('*** BEFORE CALLING ***');
hr_employee_api.create_gb_employee(p_validate                  => x_validate_mode
                                    ,p_hire_date                 => SYSDATE -- In this case
                                    ,p_business_group_id         => x_business_group_id
                                    ,p_last_name                 => 'Suresh'
                                    ,p_sex                       => 'M'
                                   -- ,p_person_type_id            => x_person_type_id
                                    ,p_date_of_birth             => '12-JAN-83'
                                    ,p_employee_number           => x_emp_num
                                    ,p_first_name                => 'AQEELUDDIN'
                                    ,p_known_as                  => ''
                                    ,p_marital_status            => ''
                                    ,p_middle_names              => ''
                                    ,p_ni_number                 => '958643218'
                                    ,p_previous_last_name        => ''
                                    ,p_title                     => 'MR.'
                                    ,p_original_date_of_hire     => SYSDATE
                                    ,p_person_id                 => x_person_id
                                    ,p_assignment_id             => x_assignment_id
                                    ,p_per_object_version_number => x_per_object_version_number
                                    ,p_asg_object_version_number => x_asg_object_version_number
                                    ,p_per_effective_start_date => x_per_effective_start_date
                                    ,p_per_effective_end_date    => x_per_effective_end_date
                                    ,p_full_name                 => x_full_name
                                    ,p_per_comment_id            => x_per_comment_id
                                    ,p_assignment_sequence       => x_assignment_sequence
                                    ,p_assignment_number         => x_assignment_number
                                    ,p_name_combination_warning => x_name_combination_warning
                                    ,p_assign_payroll_warning    => x_assign_payroll_warning
                                    ,p_orig_hire_warning         => x_orig_hire_warning
                                    );
COMMIT ;
IF(x_person_id=NULL) THEN
DBMS_OUTPUT.PUT_LINE('*** GOT ERROR ***');
END IF;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR '||SQLERRM);
END;

OKC PROCESSING DEFINATIONS

DECLARE
    rec1 okc_pdp_pvt.pdpv_rec_type;
    rec2 okc_pdp_pvt.pdpv_rec_type;


    x_api_version                  NUMBER       :=1.0;
    p_init_msg_list                VARCHAR2(200):=null;
    x_return_status                VARCHAR2(200):=null;
    x_msg_count                    NUMBER       :=null;
    x_msg_data                     VARCHAR2(200):=null;

    x_user_id                       NUMBER      :=Fnd_Profile.VALUE('USER_ID');--profile value
    x_resp_id                       NUMBER      :=fnd_global.resp_id;
    x_appl_id                       NUMBER      :=fnd_global.resp_appl_id;
    x_org_id                        NUMBER      :=fnd_global.org_id;
    x_login_id                      NUMBER      :=Fnd_Profile.VALUE('LOGIN_ID');

    BEGIN
    rec1                                        :=null;
    rec1.id                                     :=null;
    rec1.object_version_number                  :=null;
    rec1.sfwt_flag                              :='Y';
    rec1.pdf_id                                 :=null;
    rec1.name                                   :='Test3';
    rec1.user_name                              :=null;
    rec1.data_type                              :='CHAR';
    rec1.default_value                          :=null;
    rec1.required_yn                            :='Y';
    rec1.description                            :='Creating test1';
    rec1.application_id                         :=515;
    rec1.seeded_flag                            :='N';
    rec1.created_by                             :=1318;
    rec1.creation_date                          :=sysdate;
    rec1.last_updated_by                        :=1318;
    rec1.last_update_date                       :=sysdate;
    rec1.last_update_login                      :=5500646;
    rec1.jtot_object_code                       :=null;
    rec1.NAME_COLUMN                            :=null;
    rec1.description_column                     :=null;


    fnd_global.apps_initialize(x_user_id, x_resp_id, x_appl_id,x_login_id);
    DBMS_OUTPUT.PUT_LINE('API Execution Started');

              FND_MSG_PUB.INITIALIZE;

    OKC_PDP_PVT.INSERT_ROW(
    p_api_version                  =>x_api_version
    ,p_init_msg_list               =>p_init_msg_list
    ,x_return_status               =>x_return_status
    ,x_msg_count                   => x_msg_count
    ,x_msg_data                    =>x_msg_data
    ,p_pdpv_rec                    =>rec1
    ,x_pdpv_rec                    =>rec2);

    COMMIT;
    IF (x_return_Status ='S') THEN
    DBMS_OUTPUT.PUT_LINE('API Executed Successfully');
    DBMS_OUTPUT.PUT_LINE(x_return_status);
    DBMS_OUTPUT.PUT_LINE(x_msg_count);
    DBMS_OUTPUT.PUT_LINE(x_msg_data);
    ELSE
                 IF x_msg_count > 0 THEN
                FOR l_index in 1..x_msg_count LOOP
                   dbms_output.put_line(l_index || '.' || SUBSTR(fnd_msg_pub.get(p_encoded => fnd_api.g_false), 1, 255));
                END LOOP;
             END IF;
    DBMS_OUTPUT.PUT_LINE(x_return_status);
    DBMS_OUTPUT.PUT_LINE(x_msg_count);
   DBMS_OUTPUT.PUT_LINE(x_msg_data);
   DBMS_OUTPUT.PUT_LINE('API Not Executed Successfully ');
   END IF;
   EXCEPTION
   WHEN OTHERS THEN
   DBMS_OUTPUT.PUT_LINE('ERROR='||sqlerrm);
   END;

GL CODE COMBINATION

Declare
l_request_id number(10);
G_USER_NAME         VARCHAR2(100) := 'OPERATIONS';
   G_RESP_KEY          VARCHAR2(100):= 'GENERAL_LEDGER_OPERATIONS';
   l_user_id           NUMBER;
   l_resp_id           NUMBER;
   l_resp_appl_id      NUMBER;
   l_login_id         number;

   V_BOL BOOLEAN:=NULL;

   BEGIN

    BEGIN
      SELECT user_id
      INTO   l_user_id
      FROM   fnd_user
      WHERE user_name=G_USER_NAME;
   EXCEPTION WHEN OTHERS THEN
      null;
   END;
BEGIN
      SELECT responsibility_id
            ,application_id
      INTO   l_resp_id
            ,l_resp_appl_id
      FROM   fnd_responsibility_vl
      WHERE responsibility_key=G_RESP_KEY;
   EXCEPTION WHEN OTHERS THEN
      null;
   END;

   FND_GLOBAL.APPS_INITIALIZE(user_id=>l_user_id,
                              resp_id=>l_resp_id,
                              resp_appl_id=>l_resp_appl_id);

V_BOL:= fnd_flex_keyval.validate_segs(operation=>'CREATE_COMBINATION',
appl_short_name=> 'SQLGL',
key_flex_code =>'GL#',
structure_number =>101,
concat_segments => '01-730-1216-0000-000',
values_or_ids    => 'V',
validation_date =>sysdate,
displayable      => 'ALL',
data_set         => NULL,
vrule             =>NULL,
where_clause      => NULL,
get_columns       =>NULL,
allow_nulls       =>FALSE,
allow_orphans     =>FALSE);
--validation_date => sysdate);
IF V_BOL=TRUE THEN
DBMS_OUTPUT.PUT_LINE('CREATED');
ELSE
DBMS_OUTPUT.PUT_LINE('NOT CREATED');
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR='||sqlerrm);
END;

QA CHECK LIST

DECLARE
    rec1 OKC_QA_CHECK_LIST_PVT.qclv_rec_type;
    rec2 OKC_QA_CHECK_LIST_PVT.qclv_rec_type;
    p_init_msg_list                 VARCHAR2(100):= null; -- OKC_API.G_FALSE;
    x_return_status                 VARCHAR2(100):=null;
    x_msg_count                     NUMBER:=null;
    x_msg_data                      VARCHAR2(100):=null;
    x_id                            number:=null;

    x_user_id                       NUMBER :=Fnd_Profile.VALUE('USER_ID');--profile value
    x_resp_id                       NUMBER :=fnd_global.resp_id;
    x_appl_id                       NUMBER :=fnd_global.resp_appl_id;
    x_org_id                        NUMBER :=fnd_global.org_id;
    x_login_id                      NUMBER :=Fnd_Profile.VALUE('LOGIN_ID');

    BEGIN

    rec1:=null;
    rec1.id                     :=null;
    rec1.object_version_number :=null;
    rec1.name                   :='Check List Rule4';
    rec1.short_description      :='This is new checklist rule';
    rec1.begin_date             :=sysdate;
    rec1.end_date               :=null;
    rec1.sfwt_flag              :='N';
    rec1.created_by             :=1318;
    rec1.creation_date          :=sysdate;
    rec1.last_updated_by        :=1318;
    rec1.last_update_date       :=sysdate;
    rec1.last_update_login      :=5500222;
    rec1.application_id         :=515;
    rec1.default_yn             :='N';


-- MO_GLOBAL.SET_POLICY_CONTEXT('S', 204);
    fnd_global.apps_initialize(x_user_id, x_resp_id, x_appl_id,x_login_id);

    DBMS_OUTPUT.PUT_LINE('API Execution Started');

    OKC_QA_CHECK_LIST_PUB.CREATE_QA_CHECK_LIST(

    p_api_version                  =>1.0
    ,p_init_msg_list               =>null
    ,x_return_status               =>x_return_status
    ,x_msg_count                   => x_msg_count
    ,x_msg_data                    =>x_msg_data
    ,p_qclv_rec                    =>rec1
    ,x_qclv_rec                    =>rec2);

    COMMIT;

    IF (x_return_Status ='S') THEN
    DBMS_OUTPUT.PUT_LINE('QA Check Rule Created Successfully');
    DBMS_OUTPUT.PUT_LINE(x_return_status);
    DBMS_OUTPUT.PUT_LINE(x_msg_count);
    DBMS_OUTPUT.PUT_LINE(x_msg_data);
    ELSE
     IF x_msg_count > 0 THEN
                FOR l_index in 1..x_msg_count LOOP
                   dbms_output.put_line(l_index || '.' || SUBSTR(fnd_msg_pub.get(p_encoded => fnd_api.g_false), 1, 255));
                END LOOP;
             END IF;
    DBMS_OUTPUT.PUT_LINE(x_return_status);
    DBMS_OUTPUT.PUT_LINE(x_msg_count);
   DBMS_OUTPUT.PUT_LINE(x_msg_data);
   DBMS_OUTPUT.PUT_LINE('QA Check Rule Not Created Successfully ');
   END IF;
   EXCEPTION
   WHEN OTHERS THEN
   DBMS_OUTPUT.PUT_LINE('ERROR='||sqlerrm);
   END;

AR TRANSACTION TYPES

DECLARE
--apps intilization
   G_USER_NAME                          VARCHAR2(100)    := 'SYSADMIN';
   G_RESP_KEY                           VARCHAR2(100)    := 'APPLICATION_DEVELOPER';
   l_user_id                            NUMBER;
   l_resp_id                            NUMBER;
   l_resp_appl_id                       NUMBER;
--Declaring variables for ra_cust_trx_types_all
U_CUST_TRX_TYPE_ID         NUMBER(15) :=ra_cust_trx_types_s.nextval;
U_LAST_UPDATE_DATE               DATE       :=sysdate;
U_LAST_UPDATED_BY                NUMBER(15) :=null;
U_CREATION_DATE                  DATE       :=sysdate;
U_CREATED_BY                     NUMBER(15) :=null;
U_LAST_UPDATE_LOGIN                       NUMBER(15) :=null;
U_POST_TO_GL                     VARCHAR2(1):='Y';
U_ACCOUNTING_AFFECT_FLAG         VARCHAR2(1):='Y';
U_CREDIT_MEMO_TYPE_ID                     NUMBER(15) :=null;
U_STATUS                                  VARCHAR2(30):=null;
U_NAME                                    VARCHAR2(20):='Test2';
U_DESCRIPTION                             VARCHAR2(80):='This is sample transaction type';
U_TYPE                                    VARCHAR2(20):='INV';
U_DEFAULT_TERM                            NUMBER(15) :=null;
U_DEFAULT_PRINTING_OPTION         VARCHAR2(20) :='NOT';
U_DEFAULT_STATUS                 VARCHAR2(30) :='OP';
U_GL_ID_REV                               NUMBER(15)   :=null;
U_GL_ID_FREIGHT                           NUMBER(15)   :=null;
U_GL_ID_REC                               NUMBER(15)   :=null;
U_SUBSEQUENT_TRX_TYPE_ID                  NUMBER(15)   :=null;
U_SET_OF_BOOKS_ID                         NUMBER(15)   :=1;
U_ATTRIBUTE_CATEGORY                      VARCHAR2(30) :=null;
U_ATTRIBUTE1                              VARCHAR2(150):=null;
U_ATTRIBUTE2                              VARCHAR2(150):=null;
U_ATTRIBUTE3                              VARCHAR2(150):=null;
U_ATTRIBUTE4                              VARCHAR2(150):=null;
U_ATTRIBUTE5                              VARCHAR2(150):=null;
U_ATTRIBUTE6                              VARCHAR2(150):=null;
U_ATTRIBUTE7                              VARCHAR2(150):=null;
U_ATTRIBUTE8                              VARCHAR2(150):=null;
U_ATTRIBUTE9                              VARCHAR2(150):=null;
U_ATTRIBUTE10                             VARCHAR2(150):=null;
U_ALLOW_FREIGHT_FLAG             VARCHAR2(1) :='N';
U_ALLOW_OVERAPPLICATION_FLAG      VARCHAR2(10) :='N';
U_CREATION_SIGN                  VARCHAR2(30) :='A';
U_END_DATE                                DATE         :=null;
U_GL_ID_CLEARING                          NUMBER(15)   :=null;
U_GL_ID_TAX                               NUMBER(15)   :=null;
U_GL_ID_UNBILLED                          NUMBER(15)   :=null;
U_GL_ID_UNEARNED                          NUMBER(15)   :=null;
U_START_DATE                      DATE          :=sysdate;
U_TAX_CALCULATION_FLAG           VARCHAR2(1)   :='N';
U_ATTRIBUTE11                             VARCHAR2(150) :=null;
U_ATTRIBUTE12                             VARCHAR2(150) :=null;
U_ATTRIBUTE13                             VARCHAR2(150) :=null;
U_ATTRIBUTE14                             VARCHAR2(150) :=null;
U_ATTRIBUTE15                             VARCHAR2(150) :=null;
U_NATURAL_APP_ONLY_FLAG   VARCHAR2(10)          :='Y';
U_ORG_ID                                  NUMBER(15)    :=204;
U_GLOBAL_ATTRIBUTE1                       VARCHAR2(150) :=null;
U_GLOBAL_ATTRIBUTE2                       VARCHAR2(150) :=null;
U_GLOBAL_ATTRIBUTE3                       VARCHAR2(150) :=null;
U_GLOBAL_ATTRIBUTE4                       VARCHAR2(150) :=null;
U_GLOBAL_ATTRIBUTE5                       VARCHAR2(150) :=null;
U_GLOBAL_ATTRIBUTE6                       VARCHAR2(150) :=null;
U_GLOBAL_ATTRIBUTE7                       VARCHAR2(150) :=null;
U_GLOBAL_ATTRIBUTE8                       VARCHAR2(150) :=null;
U_GLOBAL_ATTRIBUTE9                       VARCHAR2(150) :=null;
U_GLOBAL_ATTRIBUTE10                      VARCHAR2(150) :=null;
U_GLOBAL_ATTRIBUTE11                      VARCHAR2(150) :=null;
U_GLOBAL_ATTRIBUTE12                      VARCHAR2(150) :=null;
U_GLOBAL_ATTRIBUTE13                      VARCHAR2(150) :=null;
U_GLOBAL_ATTRIBUTE14                      VARCHAR2(150) :=null;
U_GLOBAL_ATTRIBUTE15                      VARCHAR2(150) :=null;
U_GLOBAL_ATTRIBUTE16                      VARCHAR2(150) :=null;
U_GLOBAL_ATTRIBUTE17                      VARCHAR2(150) :=null;
U_GLOBAL_ATTRIBUTE18                      VARCHAR2(150) :=null;
U_GLOBAL_ATTRIBUTE19                      VARCHAR2(150) :=null;
U_GLOBAL_ATTRIBUTE20                      VARCHAR2(150) :=null;
U_GLOBAL_ATTRIBUTE_CATEGORY               VARCHAR2(30)   :=null;
U_RULE_SET_ID                             NUMBER         :=null;
U_SIGNED_FLAG                             VARCHAR2(1)    :='N';
U_DRAWEE_ISSUED_FLAG                      VARCHAR2(1)    :='N';
U_MAGNETIC_FORMAT_CODE                    VARCHAR2(30)   :=null;
U_FORMAT_PROGRAM_ID                       NUMBER(15)     :=null;
U_GL_ID_UNPAID_REC                        NUMBER(15)     :=null;
U_GL_ID_REMITTANCE                        NUMBER(15)     :=null;
U_GL_ID_FACTOR                            NUMBER(15)     :=null;
U_ALLOCATE_TAX_FREIGHT                    VARCHAR2(1)    :=null;
U_LEGAL_ENTITY_ID                         NUMBER(15)     :=null;
U_EXCLUDE_FROM_LATE_CHARGES               VARCHAR2(30)   :='N';
U_ADJ_POST_TO_GL                          VARCHAR2(1)    :='Y';
X_NAME                                    VARCHAR2(150);
X_COUNT                                   NUMBER         :=0;
BEGIN
BEGIN
select name into x_name
from ra_cust_trx_types_all
where name=u_name;
EXCEPTION WHEN NO_DATA_FOUND THEN
X_COUNT:=X_COUNT+1;
WHEN TOO_MANY_ROWS THEN
NUll;
END;
BEGIN
      SELECT user_id
      INTO   l_user_id
      FROM   fnd_user
      WHERE user_name=G_USER_NAME;
   EXCEPTION WHEN OTHERS THEN
      null;
   END;
   BEGIN
      SELECT responsibility_id
            ,application_id
      INTO   l_resp_id
            ,l_resp_appl_id
      FROM   fnd_responsibility_vl
      WHERE responsibility_key=G_RESP_KEY;
   EXCEPTION WHEN OTHERS THEN
      null;
   END;
   FND_GLOBAL.APPS_INITIALIZE(user_id=>l_user_id,
                              resp_id=>l_resp_id,
                              resp_appl_id=>l_resp_appl_id);

    U_LAST_UPDATED_BY :=l_user_id;
    U_CREATED_BY      :=l_user_id;
    U_LAST_UPDATE_LOGIN:=5590465;

    U_LAST_UPDATED_BY :=l_user_id;
    U_CREATED_BY       :=l_user_id;
    U_LAST_UPDATE_LOGIN :=5092465;

    IF (x_count !=1) THEN
dbms_output.put_line('Trying to insert duplicate value');
ELSE

    -- inserting values into ra_cust_trx_types_all

    insert into ra_cust_trx_types_all(
                                      CUST_TRX_TYPE_ID,
                                      LAST_UPDATE_DATE,
                                      LAST_UPDATED_BY ,
                                      CREATION_DATE ,
                                      CREATED_BY ,
                                      LAST_UPDATE_LOGIN ,
                                      POST_TO_GL ,
                                      ACCOUNTING_AFFECT_FLAG ,
                                      CREDIT_MEMO_TYPE_ID,
                                      STATUS ,
                                      NAME   ,
                                      DESCRIPTION   ,
                                      TYPE   ,
                                      DEFAULT_TERM ,
                                      DEFAULT_PRINTING_OPTION ,
                                      DEFAULT_STATUS ,
                                      GL_ID_REV ,
                                      GL_ID_FREIGHT ,
                                      GL_ID_REC   ,
                                      SUBSEQUENT_TRX_TYPE_ID ,
                                      SET_OF_BOOKS_ID   ,
                                      ATTRIBUTE_CATEGORY ,
                                      ATTRIBUTE1 ,
                                      ATTRIBUTE2 ,
                                      ATTRIBUTE3   ,
                                      ATTRIBUTE4   ,
                                      ATTRIBUTE5    ,
                                      ATTRIBUTE6 ,
                                      ATTRIBUTE7    ,
                                      ATTRIBUTE8 ,
                                      ATTRIBUTE9   ,
                                      ATTRIBUTE10   ,
                                      ALLOW_FREIGHT_FLAG ,
                                      ALLOW_OVERAPPLICATION_FLAG ,
                                      CREATION_SIGN ,
                                      END_DATE   ,
                                      GL_ID_CLEARING ,
                                      GL_ID_TAX     ,
                                      GL_ID_UNBILLED   ,
                                      GL_ID_UNEARNED   ,
                                      START_DATE       ,
                                      TAX_CALCULATION_FLAG   ,
                                      ATTRIBUTE11   ,
                                      ATTRIBUTE12   ,
                                      ATTRIBUTE13    ,
                                      ATTRIBUTE14   ,
                                      ATTRIBUTE15   ,
                                      NATURAL_APPLICATION_ONLY_FLAG,
                                      ORG_ID ,
                                      GLOBAL_ATTRIBUTE1 ,
                                      GLOBAL_ATTRIBUTE2 ,
                                      GLOBAL_ATTRIBUTE3 ,
                                      GLOBAL_ATTRIBUTE4 ,
                                      GLOBAL_ATTRIBUTE5 ,
                                      GLOBAL_ATTRIBUTE6 ,
                                      GLOBAL_ATTRIBUTE7 ,
                                      GLOBAL_ATTRIBUTE8 ,
                                      GLOBAL_ATTRIBUTE9 ,
                                      GLOBAL_ATTRIBUTE10 ,
                                      GLOBAL_ATTRIBUTE11 ,
                                      GLOBAL_ATTRIBUTE12 ,
                                      GLOBAL_ATTRIBUTE13 ,
                                      GLOBAL_ATTRIBUTE14 ,
                                      GLOBAL_ATTRIBUTE15 ,
                                      GLOBAL_ATTRIBUTE16 ,
                                      GLOBAL_ATTRIBUTE17 ,
                                      GLOBAL_ATTRIBUTE18 ,
                                      GLOBAL_ATTRIBUTE19 ,
                                      GLOBAL_ATTRIBUTE20 ,
                                      GLOBAL_ATTRIBUTE_CATEGORY   ,
                                      RULE_SET_ID       ,
                                      SIGNED_FLAG        ,
                                      DRAWEE_ISSUED_FLAG   ,
                                      MAGNETIC_FORMAT_CODE ,
                                      FORMAT_PROGRAM_ID    ,
                                      GL_ID_UNPAID_REC    ,
                                      GL_ID_REMITTANCE    ,
                                      GL_ID_FACTOR       ,
                                      ALLOCATE_TAX_FREIGHT ,
                                      LEGAL_ENTITY_ID       ,
                                      EXCLUDE_FROM_LATE_CHARGES   ,
                                      ADJ_POST_TO_GL    )

             VALUES         (
                                      U_CUST_TRX_TYPE_ID,
                                      U_LAST_UPDATE_DATE,
                                      U_LAST_UPDATED_BY ,
                                      U_CREATION_DATE ,
                                      U_CREATED_BY ,
                                      U_LAST_UPDATE_LOGIN ,
                                      U_POST_TO_GL ,
                                      U_ACCOUNTING_AFFECT_FLAG ,
                                      U_CREDIT_MEMO_TYPE_ID,
                                      U_STATUS ,
                                      U_NAME   ,
                                      U_DESCRIPTION   ,
                                      U_TYPE   ,
                                      U_DEFAULT_TERM ,
                                      U_DEFAULT_PRINTING_OPTION ,
                                      U_DEFAULT_STATUS ,
                                      U_GL_ID_REV ,
                                      U_GL_ID_FREIGHT ,
                                      U_GL_ID_REC   ,
                                      U_SUBSEQUENT_TRX_TYPE_ID ,
                                      U_SET_OF_BOOKS_ID   ,
                                      U_ATTRIBUTE_CATEGORY ,
                                      U_ATTRIBUTE1 ,
                                      U_ATTRIBUTE2 ,
                                      U_ATTRIBUTE3   ,
                                      U_ATTRIBUTE4   ,
                                      U_ATTRIBUTE5    ,
                                      U_ATTRIBUTE6 ,
                                      U_ATTRIBUTE7    ,
                                      U_ATTRIBUTE8 ,
                                      U_ATTRIBUTE9   ,
                                      U_ATTRIBUTE10   ,
                                      U_ALLOW_FREIGHT_FLAG ,
                                      U_ALLOW_OVERAPPLICATION_FLAG ,
                                      U_CREATION_SIGN ,
                                      U_END_DATE   ,
                                      U_GL_ID_CLEARING ,
                                      U_GL_ID_TAX     ,
                                      U_GL_ID_UNBILLED   ,
                                      U_GL_ID_UNEARNED   ,
                                      U_START_DATE       ,
                                      U_TAX_CALCULATION_FLAG   ,
                                      U_ATTRIBUTE11   ,
                                      U_ATTRIBUTE12   ,
                                      U_ATTRIBUTE13    ,
                                      U_ATTRIBUTE14   ,
                                      U_ATTRIBUTE15   ,
                                      U_NATURAL_APP_ONLY_FLAG,
                                      U_ORG_ID ,
                                      U_GLOBAL_ATTRIBUTE1 ,
                                      U_GLOBAL_ATTRIBUTE2 ,
                                      U_GLOBAL_ATTRIBUTE3 ,
                                      U_GLOBAL_ATTRIBUTE4 ,
                                      U_GLOBAL_ATTRIBUTE5 ,
                                      U_GLOBAL_ATTRIBUTE6 ,
                                      U_GLOBAL_ATTRIBUTE7 ,
                                      U_GLOBAL_ATTRIBUTE8 ,
                                      U_GLOBAL_ATTRIBUTE9 ,
                                      U_GLOBAL_ATTRIBUTE10 ,
                                      U_GLOBAL_ATTRIBUTE11 ,
                                      U_GLOBAL_ATTRIBUTE12 ,
                                      U_GLOBAL_ATTRIBUTE13 ,
                                      U_GLOBAL_ATTRIBUTE14 ,
                                      U_GLOBAL_ATTRIBUTE15 ,
                                      U_GLOBAL_ATTRIBUTE16 ,
                                      U_GLOBAL_ATTRIBUTE17 ,
                                      U_GLOBAL_ATTRIBUTE18 ,
                                      U_GLOBAL_ATTRIBUTE19 ,
                                      U_GLOBAL_ATTRIBUTE20 ,
                                      U_GLOBAL_ATTRIBUTE_CATEGORY   ,
                                      U_RULE_SET_ID       ,
                                      U_SIGNED_FLAG        ,
                                      U_DRAWEE_ISSUED_FLAG   ,
                                      U_MAGNETIC_FORMAT_CODE ,
                                      U_FORMAT_PROGRAM_ID    ,
                                      U_GL_ID_UNPAID_REC    ,
                                      U_GL_ID_REMITTANCE    ,
                                      U_GL_ID_FACTOR       ,
                                      U_ALLOCATE_TAX_FREIGHT ,
                                      U_LEGAL_ENTITY_ID       ,
                                      U_EXCLUDE_FROM_LATE_CHARGES   ,
                                      U_ADJ_POST_TO_GL );

   COMMIT;
DBMS_OUTPUT.PUT_LINE('Records inserted successfully');

   end if;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('The error is:'||SQLERRM);
END;