CREATE OR REPLACE PROCEDURE QALOCAL.GL_PROC
AS
   CURSOR C1
   IS
      SELECT T5.BU_ID,
             T5.BU_NM,
             T3.CRNCY_ID,
             T3.CRNCY_CD,
             T6.REF_DESC AS GROUP_GL,
             P.SEG_DESC AS LEDGER,
             C.SEG_DESC AS SUB_LEDGER,
             T2.PERIOD_END_FG,
             T2.QUARTER_END_FG,
             T2.YEAR_END_FG,
             BAL_DT AS PERIOD,
             NVL (T2.LEDGER_BAL, 0) AS BALANCE
        FROM GL_ACCOUNT_SUMMARY T1,
             GL_DAILY_BALANCE_HISTORY T2,
             CURRENCY T3,
             GL_ACCOUNT T4,
             BUSINESS_UNIT T5,
             GL_CHART_OF_ACCOUNT P,
             GL_CHART_OF_ACCOUNT C,
             GL_ACCOUNT_CATEGORY_REF T6
       WHERE     T1.GL_ACCT_SUMMARY_ID = T2.GL_ACCT_SUMMARY_ID
             AND T4.GL_ACCT_ID = T2.GL_ACCT_ID
             AND T3.CRNCY_ID = T1.CRNCY_ID
             AND T4.GL_ACCT_ID = T1.GL_ACCT_ID
             AND T6.REF_KEY = T4.GL_ACCT_CAT_CD
             AND T4.BU_ID = T5.BU_ID
             AND C.CHART_OF_ACCT_ID = T4.CHART_OF_ACCT_ID
             AND P.CHART_OF_ACCT_ID = C.PARENT_ID
             AND T1.REC_ST = 'A'
             AND T3.REC_ST = 'A'
             AND T4.REC_ST = 'A'
             AND P.REC_ST = 'A'
             AND T5.REC_ST = 'A'
             AND T6.REF_DESC <> 'Capital' 
             AND T2.BAL_DT =TO_DATE('24-04-2007','DD-MM-YYYY');  /*Without Capital*/
type c1_type  is table of c1%rowtype;
rec1 c1_type;
   LOC_CRNCY        NUMBER;
   STD_RATE_SHEET   NUMBER;
   Report_RATE         NUMBER;
   BU_BAL           NUMBER;
   V_CONVERT_BAl    NUMBER;
BEGIN
   SELECT TO_NUMBER (PARAM_VALUE)
     INTO LOC_CRNCY
     FROM CTRL_PARAMETER
    WHERE PARAM_CD = 'S17';                                /*Local  Currency*/

   SELECT TO_NUMBER (PARAM_VALUE)
     INTO STD_RATE_SHEET
     FROM CTRL_PARAMETER
    WHERE PARAM_CD = 'S1009';                            /*Standard Rate Sheet*/

   SELECT TO_NUMBER (PARAM_VALUE)
     INTO Report_RATE
     FROM CTRL_PARAMETER
    WHERE PARAM_CD = 'S1010';                                      /*Report-Rate*/
open c1;
loop
fetch c1 bulk collect into rec1 limit 500;
   FOR i IN 1..rec1.count loop
         IF (i.CRNCY_ID = LOC_CRNCY)THEN
         INSERT INTO BI_GL_HISTORY
              VALUES (BI_GL_SEQ.NEXTVAL,
                      i.BU_ID,
                      i.BU_NM,
                      i.CRNCY_CD,
                      i.GROUP_GL,
                      i.Ledger,
                      i.SUB_Ledger,
                      i.PERIOD_END_FG,
                      i.QUARTER_END_FG,
                      i.YEAR_END_FG,
                      i.PERIOD,
                      i.BALANCE,
                      SYSTIMESTAMP);

         COMMIT;
      ELSE
         V_CONVERT_BAl :=
      /*package-Function call*/      CRNCY_CONVERSION_PKG.CONVERT (i.CRNCY_ID,
                                          LOC_CRNCY,
                                          STD_RATE_SHEET,
                                          STD_RATE_SHEET,
                                          Report_RATE,
                                          Report_RATE,
                                          TO_CHAR (i.BALANCE),
                                          i.BU_ID);

         INSERT INTO BI_GL_HISTORY
              VALUES (BI_GL_SEQ.NEXTVAL,
                      i.BU_ID,
                      i.BU_NM,
                      i.CRNCY_CD,
                      i.GROUP_GL,
                      i.Ledger,
                      i.SUB_Ledger,
                      i.PERIOD_END_FG,
                      i.QUARTER_END_FG,
                      i.YEAR_END_FG,
                      i.PERIOD,
                      V_CONVERT_BAL,
                      SYSTIMESTAMP);
         END IF;
         END LOOP;
   exit when c1%notfound;
   end loop;
end

 i.BU_ID to BU_ID(i) 

CREATE OR REPLACE PROCEDURE GL_PROC
AS
   CURSOR C1
   IS
      SELECT ename
        FROM emp;
         
    TYPE c1_type  IS TABLE OF c1%ROWTYPE;
    rec1   c1_type;  
          
BEGIN
open c1;
loop
fetch c1 bulk collect into rec1 limit 500;
   FOR i IN 1..rec1.count loop
         INSERT INTO emp1(ename) VALUES (rec1(i).ename);
         END LOOP;
  exit when c1%notfound;
   end loop;
close c1;
end;
/