GL_PROCEDURE CODING & PROCESS STEPS

CREATE OR REPLACE PROCEDURE GL_PAT123(Errbuf  OUT VARCHAR2,
                                          Retcode OUT VARCHAR2) IS
  -- cursor declaration
  CURSOR gl_cur IS
    SELECT
        status    ,
        set_of_books_name  ,
        accounting_date   ,
        currency   ,
        date_created ,
        created_by   ,
        actual_flag  ,
        source ,
        category     ,
          segment1   ,
          segment2   ,
          segment3   ,
          segment4   ,
          segment5   ,
          xx_entered_dr ,
          xx_entered_cr    
          FROM GLINT ;
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_source_name,
      user_je_category_name ,
      segment1,
      segment2,
      segment3,
      segment4,
      segment5,
      entered_dr,
      entered_cr,
       )
      VALUES
      (rec_cur.status ,
       rec_cur.set_of_books_name  ,
       rec_cur.accounting_date  ,
       rec_cur.currency   ,
       rec_cur.date_created   ,
       rec_cur.created_by    ,
       rec_cur.actual_flag    ,
       rec_cur.source  ,
       rec_cur.category  ,
       rec_cur.segment1  ,
       rec_cur.segment2  ,
       rec_cur.segment3  ,
       rec_cur.segment4  ,
       rec_cur.segment5  ,
       rec_cur.xx_entered_dr   ,
       rec_cur.xx_entered_cr   ,
       );
 END IF;
 l_flag:=NULL;
 l_error_msg:=NULL;
 END LOOP;
COMMIT;
END GL_PAT123;


1)we have received flat file from client

2)We have created Staging table as per flat file structre

3)developed Control file and uploaded data

4)Developed PL/SQL Program to upload the data from stage into interface table
   1)declare Cursor
   2)open cursor
   3)Validate each record
   4)If no invalid record then insert into interface table.

5)Run the journal import from GL => Journal => Import => Run
   Give the two parameters 1)Source
          2)Group ID

6) Open the Output if status is 'SUCEESS' then take Request ID.

7)open Journal Enter screen Query the records based on the %requestid% As batch Name
  Select Review journal button we can see the journal detailed transaction

8) If we want correct the journals we can correct from Journal=>Import=>Correct

9) If we want delete the journals we can delete from Journal=>Import=>Delete