11i VS R12 Differences in Finance

Procedure for GL Interface

CREATE OR REPLACE PROCEDURE GE_Pro30(Errbuf  OUT VARCHAR2,
                                      Retcode OUT VARCHAR2) IS
  -- cursor declaration
  CURSOR gl_cur IS
    SELECT
        status    ,
        set_of_books_id   ,
        accounting_date   ,
        currency   ,
        date_created   ,
        created_by    ,
        actual_flag    ,
        category       ,
        source     ,
        curr_conversion     ,
          segment1     ,
          segment2        ,
          segment3        ,
          segment4        ,
          segment5        ,
          entered_dr      ,
          entered_cr      ,
          accounted_dr    ,
          accounted_cr    ,
          group_id
          FROM XX_TEMP;
l_currencycode    VARCHAR2(25);
l_set_of_books_id NUMBER(3);
l_flag            VARCHAR2(2);
l_error_msg       VARCHAR2(100);
l_err_flag        VARCHAR2(10);
l_category        VARCHAR2(100);
L_USERID          NUMBER(10);
l_count           NUMBER(9) default 0;
BEGIN
 DELETE FROM  gl_interface;
 COMMIT;
FOR rec_cur IN gl_cur LOOP
l_count:=l_count+1;
l_flag    :='A';
--Category Column Validation
BEGIN
 SELECT USER_JE_CATEGORY_NAME
 INTO   l_CATEGORY
 FROM   GL_JE_CATEGORIES
 WHERE  USER_JE_CATEGORY_NAME = REC_CUR.Category;
 EXCEPTION
     WHEN OTHERS THEN
            l_flag:='E';
            l_error_msg:='Category does not exist ';
Fnd_File.put_line (Fnd_File.LOG,'Inserting data into the Interface TABLE'||'-'||l_count||' '||l_error_msg);
END;
 --End Category Column Validation
 --User ID column validation
BEGIN
SELECT USER_ID
INTO   L_USERID
FROM   FND_USER
WHERE  USER_ID = REC_CUR.created_by;
EXCEPTION
WHEN OTHERS THEN
            l_flag:='E';
            l_error_msg:='User ID does not exist ';
Fnd_File.put_line (Fnd_File.LOG,'Inserting data into the Interface TABLE'||'-'||l_count||' '||l_error_msg);
END;
 --End of Created_by OR UserID column Validation
 --Set of  books Validation
 BEGIN
     SELECT set_of_books_id
     INTO   l_set_of_books_id
     FROM   GL_SETS_OF_BOOKS
     WHERE set_of_books_id=rec_cur.set_of_books_id;
     EXCEPTION
     WHEN OTHERS THEN
            l_flag:='E';
            l_error_msg:='set of Books ID does not exist ';
Fnd_File.put_line (Fnd_File.LOG,'Inserting data into the Interface TABLE'||'-'||l_count||' '||l_error_msg);
 END;
--Cuurency Code Validation
 BEGIN
     SELECT currency_code
     INTO   l_currencycode
     FROM   fnd_currencies
     WHERE currency_code=rec_cur.currency
     AND currency_code='USD';
     EXCEPTION
      WHEN OTHERS THEN
            l_flag:='E';
            l_error_msg:='currency code does not exists';
Fnd_File.put_line (Fnd_File.LOG,'Inserting data into the Interface TABLE'||'-'||l_count||' '||l_error_msg);
 END;
IF   l_flag!='E' THEN
Fnd_File.put_line (Fnd_File.LOG,'Inserting data into the Interface TABLE');
INSERT INTO gl_interface(status,
      set_of_books_id,
      accounting_date,
      currency_code,
      date_created,
      created_by,
      actual_flag,
      user_je_category_name,
      user_je_source_name,
      user_currency_conversion_type,
      segment1,
      segment2,
      segment3,
      segment4,
      segment5,
      entered_dr,
      entered_cr,
      accounted_dr,
      accounted_cr,
      group_id)
      VALUES
      (rec_cur.status    ,
       rec_cur.set_of_books_id   ,
       rec_cur.accounting_date   ,
       rec_cur.currency    ,
       rec_cur.date_created   ,
       rec_cur.created_by    ,
       rec_cur.actual_flag    ,
       rec_cur.category       ,
       rec_cur.source     ,
       rec_cur.curr_conversion                ,
       rec_cur.segment1  ,
       rec_cur.segment2  ,
       rec_cur.segment3  ,
       rec_cur.segment4  ,
       rec_cur.segment5  ,
       rec_cur.entered_dr   ,
       rec_cur.entered_cr   ,
       rec_cur.accounted_dr  ,
       rec_cur.accounted_cr  ,
       rec_cur.group_id);
 END IF;
 l_flag:=NULL;
 l_error_msg:=NULL;
  END LOOP;
COMMIT;
END  GE_Pro30;
/