CREATE OR REPLACE PROCEDURE SP_DAYS_FUND_RESULT (

  pSLIP_DATE      IN        SLIP_M.SLIP_DATE%TYPE,            /* 전표일자        */
  pUSER_ID        IN        SLIP_M.CREATE_USER_ID%TYPE,       /* CREATEY_USER_ID */
  pIP_ADDR        IN        SLIP_M.PC_IP_ADDR%TYPE            /* PC_IP_ADDR      */
          
) IS

  vCURSOR_ID                INTEGER;
  vDUMMY                    INTEGER;
  vAMOUNT                   DAYS_FUND_PLAN.HEAD_AMT%TYPE;
  vHEAD_AMT                 DAYS_FUND_PLAN.HEAD_AMT%TYPE;           /* 본사자금금액 */
  vBRANCH_AMT               DAYS_FUND_PLAN.BRANCH_AMT%TYPE;         /* 지점자금금액 */

BEGIN

  --[ Cursor 선언 ]
  FOR cur IN ( SELECT FUND_CODE, FUND_DIST, RESULT_SQL
                 FROM FUND_CODE
                WHERE FUND_CODE > ' ' )
               
  LOOP
               

    --[ Cursor내의 Block ]
    BEGIN
   
      --[ 처리를 위해 Cursor를 Open ]
      vCURSOR_ID := DBMS_SQL.OPEN_CURSOR;
   
     
      --[ Query를 Parse한다 ]
      DBMS_SQL.PARSE(vCURSOR_ID, cur.RESULT_SQL, DBMS_SQL.NATIVE);
   
     
      --[ 입력변수를 묶는다 ]
      DBMS_SQL.BIND_VARIABLE(vCURSOR_ID, ':SLIP_DATE', pSLIP_DATE);
   
     
      --[ 출력변수를 정의한다 ]
      DBMS_SQL.DEFINE_COLUMN(vCURSOR_ID, 1, vAMOUNT );
      -- 출력변수 지정시 char이면 자리수도 지정해주어야 합니다.  

 
      
      --[ 문장을 실행한다 ]
      vDUMMY := DBMS_SQL.EXECUTE(vCURSOR_ID);
   
     
      --[ 인출 Loop ]
      LOOP
     
        --[ 행을 버퍼로 인출하고 Exit조건을 검사한다 ]
        IF DBMS_SQL.FETCH_ROWS(vCURSOR_ID) = 0 THEN
           EXIT;
        END IF;
   
       
        --[ 버퍼에서 PL/SQL 변수로 행들을 검색한다 ]
        DBMS_SQL.COLUMN_VALUE(vCURSOR_ID, 1, vAMOUNT );
   
     
        --[ 본사금액/지점금액을 구한다 ]
        SELECT DECODE(cur.FUND_DIST, '1', NVL(vAMOUNT,0), 0),
               DECODE(cur.FUND_DIST, '2', NVL(vAMOUNT,0), 0)
          INTO vHEAD_AMT,
               vBRANCH_AMT
          FROM DUAL;
    
                                       
        --[ 일 자금항목에 Insert ]
        INSERT INTO DAYS_FUND_PLAN                                   
                  ( PLAN_DATE,  PLAN_GUBUN, FUND_CODE,     HEAD_AMT,  BRANCH_AMT,  CREATE_USER_ID, CREATE_DATE, PC_IP_ADDR )       
            VALUES( pSLIP_DATE, 'R',        cur.FUND_CODE, vHEAD_AMT, vBRANCH_AMT, pUSER_ID,       SysDate,     pIP_ADDR   );
                    
      END LOOP;

      --[ Cursor를 닫는다 ]
      DBMS_SQL.CLOSE_CURSOR(vCURSOR_ID);

     
    --[ 예외 처리부 ]
    EXCEPTION

      WHEN OTHERS THEN
           DBMS_SQL.CLOSE_CURSOR(vCURSOR_ID);

          INSERT INTO DAYS_FUND_PLAN                                   
                    ( PLAN_DATE,  PLAN_GUBUN, FUND_CODE,     HEAD_AMT, BRANCH_AMT, CREATE_USER_ID, CREATE_DATE, PC_IP_ADDR )       
              VALUES( pSLIP_DATE, 'R',        cur.FUND_CODE, 0,        0,          pUSER_ID,       SysDate,     pIP_ADDR   );
                    
    END;  
 
 
  END LOOP;

 
  --[ 상위코드에 Update ]
  SP_FUND_UPLEVEL(SUBSTR(pSLIP_DATE,1,6),'R');
 

  --[ Commit ]
  COMMIT WORK;
                
                  
END SP_DAYS_FUND_RESULT;