GL INTERFACE CODE

             GL INTERFACE CODE                                                        
                                                                     
                                                                     
                                             
CREATE OR REPLACE PROCEDURE  GE_Pro(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 VENKAT_GL_INBOUND_INTERFACE;
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);
BEGIN
 DELETE FROM  gl_interface;
 COMMIT;
FOR rec_cur IN gl_cur LOOP
     l_flag:='A';
     l_err_flag:='A';
--This  PL/SQL Block will do the currency validation
 --end of the currency validation
--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_category:=NULL;
            l_flag:='E';
            l_error_msg:='Category does not exist ';
 END;
Fnd_File.put_line (Fnd_File.LOG,'Inserting data into the Interface TABLE'||L_FLAG);
 --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_userid:=NULL;
            l_flag:='E';
            l_error_msg:='User ID does not exist ';
 END;
 Fnd_File.put_line (Fnd_File.LOG,'Inserting data into the Interface TABLE'||L_FLAG);
 --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_set_of_books_id:=NULL;
            l_flag:='E';
            l_error_msg:='set of Books ID does not exist ';
 END;
 Fnd_File.put_line (Fnd_File.LOG,'Inserting data into the Interface TABLE'||L_FLAG);
-- End Set of  books Validation
 --Status Column validation
/* BEGIN
 IF rec_cur.status = 'NEW' THEN
 l_flag:= 'A';
 ELSE
 l_flag:= 'E';
 Fnd_File.put_line (Fnd_File.LOG,'Status column has got invalid data');
 END IF;
 END;*/
 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_currencycode:=NULL;
            l_flag:='E';
            l_error_msg:='currency code does not exists';
 END;
Fnd_File.put_line (Fnd_File.LOG,'Inserting data into the Interface TABLE'||L_FLAG);
Fnd_File.put_line (Fnd_File.LOG,'Inserting data into the Interface TABLE'||L_FLAG);
--End of Actual Flag Column validation
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_Pro;
/