PACKAGE BODY ES IS
    /* OPEN EXCEL SHEET */
PROCEDURE OPEN_SHEET(FILENAME IN VARCHAR2,SHEET IN VARCHAR2,VISIBLE VARCHAR2) IS
	V_PATH VARCHAR2(50);
BEGIN
	SELECT EXCEL_FILE_PATH INTO :KEY_BLK.CTL_PATH FROM TBMF_SERVER_PATH;
	ES.FILE_NAME := :KEY_BLK.CTL_PATH||'WI_QUERY_REG.xls';
---	MESSAGE('FILE NAME : '||ES.FILE_NAME,ACKNOWLEDGE);
---	MESSAGE('FILE NAME : '||ES.FILE_NAME,ACKNOWLEDGE);
	IF OPEN_FLAG = 'N' THEN
       IF ES.FILE_NAME IS NULL THEN
          MESSAGE('No File Name specified');
          MESSAGE(' ',NO_ACKNOWLEDGE);  
          RAISE FORM_TRIGGER_FAILURE;
       END IF;
       IF SHEET IS NULL THEN
          MESSAGE('No WorkSheets specified');
          MESSAGE(' ',NO_ACKNOWLEDGE);  
          RAISE FORM_TRIGGER_FAILURE;
       END IF;

       APPLICATION:=OLE2.CREATE_OBJ('Excel.Application');
       OLE2.SET_PROPERTY(APPLICATION,'Visible',VISIBLE);
       WORKBOOKS:=OLE2.GET_OBJ_PROPERTY(APPLICATION,'Workbooks');
   	BEGIN
       ARGS:= OLE2.CREATE_ARGLIST;
       OLE2.ADD_ARG(ARGS,FILENAME);
       WORKBOOK:=OLE2.GET_OBJ_PROPERTY(WORKBOOKS,'Open',args);
       WORKSHEETS:=OLE2.GET_OBJ_PROPERTY(WORKBOOK,'Worksheets');
       OLE2.DESTROY_ARGLIST(ARGS);
   	EXCEPTION
   	WHEN OTHERS THEN
       IF SQLCODE = '-305500' THEN
          OLE2.INVOKE(APPLICATION, 'Quit');
          OLE2.RELEASE_OBJ(WORKSHEETS);
          OLE2.RELEASE_OBJ(WORKBOOK);
          OLE2.RELEASE_OBJ(WORKBOOKS);
          OLE2.RELEASE_OBJ(APPLICATION);
          MESSAGE('File Not Found');
          MESSAGE(' ',NO_ACKNOWLEDGE);
          RAISE FORM_TRIGGER_FAILURE;
       END IF;  
    END;   
   	BEGIN    
       ARGS:= OLE2.CREATE_ARGLIST;
       OLE2.ADD_ARG(ARGS,SHEET);
       WORKSHEET:=OLE2.GET_OBJ_PROPERTY(WORKBOOK,'Worksheets',ARGS);
       OLE2.DESTROY_ARGLIST(ARGS);
   	EXCEPTION
   	WHEN OTHERS THEN
       IF SQLCODE = '-305500' THEN
          CLOSE_SHEET;
          MESSAGE('Cannot Open Worksheet');
          MESSAGE(' ',NO_ACKNOWLEDGE);
          RAISE FORM_TRIGGER_FAILURE;
       END IF; 
    END; 
   	OPEN_FLAG := 'Y';
	END IF;

END;

/* READ DATA (NUMERIC) FROM CELL */
FUNCTION READ_NUM(ROW IN NUMBER,COL IN VARCHAR2) RETURN NUMBER IS
DATA NUMBER;
BEGIN
    ARGS:=OLE2.CREATE_ARGLIST;
    OLE2.ADD_ARG(ARGS,ROW); /* ROW REF*/
    OLE2.ADD_ARG(ARGS,COL); /* COL REF*/
    CELL:=OLE2.GET_OBJ_PROPERTY(WORKSHEET,'Cells',ARGS);
    DATA:=OLE2.GET_NUM_PROPERTY(CELL,'Value');
    OLE2.DESTROY_ARGLIST(ARGS);
    OLE2.RELEASE_OBJ(CELL);
    RETURN(DATA);
END;

/* READ DATE FROM CELL */
FUNCTION READ_DATE(ROW IN NUMBER,COL IN VARCHAR2) RETURN DATE IS
DATA NUMBER;
DT DATE;
BEGIN
    ARGS:=OLE2.CREATE_ARGLIST;
    OLE2.ADD_ARG(ARGS,ROW); /* ROW REF*/
    OLE2.ADD_ARG(ARGS,COL); /* COL REF*/
    CELL:=OLE2.GET_OBJ_PROPERTY(WORKSHEET,'Cells',ARGS);
    DATA:=OLE2.GET_NUM_PROPERTY(CELL,'Value');
    OLE2.DESTROY_ARGLIST(ARGS);
    OLE2.RELEASE_OBJ(CELL);
    DT := TO_DATE(TO_CHAR(DATA+2415019),'J');  
    RETURN(DT);
END;

/* READ DATA (ALPHANUMERIC) FROM CELL */
FUNCTION READ_CHAR (ROW IN NUMBER,COL IN VARCHAR2) RETURN CHAR IS
DATA VARCHAR2(80);
BEGIN
    ARGS:=OLE2.CREATE_ARGLIST;
    OLE2.ADD_ARG(ARGS,ROW); /* ROW REF*/
    OLE2.ADD_ARG(ARGS,COL); /* COL REF*/
    CELL:=OLE2.GET_OBJ_PROPERTY(WORKSHEET,'Cells',ARGS);
    DATA:=OLE2.GET_CHAR_PROPERTY(CELL,'Value');
    OLE2.DESTROY_ARGLIST(ARGS);
    OLE2.RELEASE_OBJ(CELL);
    RETURN(DATA); 
END;


/* PUT DATA (NUMERIC) IN CELL */
PROCEDURE PUT_NUM (ROW IN NUMBER,COL IN VARCHAR2,DATA IN NUMBER) IS
BEGIN
	  IF DATA IS NOT NULL THEN
       ARGS:=OLE2.CREATE_ARGLIST;
       OLE2.ADD_ARG(ARGS,ROW); /* ROW REF*/
       OLE2.ADD_ARG(ARGS,COL); /* COL REF*/
       CELL:=OLE2.GET_OBJ_PROPERTY(WORKSHEET,'Cells',ARGS);
       OLE2.DESTROY_ARGLIST(ARGS);
       OLE2.SET_PROPERTY(CELL,'Value',DATA); /* PUT VALUE IN CELL */
       OLE2.RELEASE_OBJ(CELL);
    END IF;
END;

/* PUT DATA (ALPHANUMERIC) IN CELL */
PROCEDURE PUT_CHAR (ROW IN NUMBER,COL IN VARCHAR2,DATA IN VARCHAR2) IS
BEGIN
	  IF DATA IS NOT NULL THEN
       ARGS:=OLE2.CREATE_ARGLIST;
       OLE2.ADD_ARG(ARGS,ROW); /* ROW REF*/
       OLE2.ADD_ARG(ARGS,COL); /* COL REF*/
       CELL:=OLE2.GET_OBJ_PROPERTY(WORKSHEET,'Cells',ARGS);
       OLE2.DESTROY_ARGLIST(ARGS);
       OLE2.SET_PROPERTY(CELL,'Value',DATA); /* PUT VALUE IN CELL */
       OLE2.RELEASE_OBJ(CELL);
    END IF;
END;

/* SAVE AND EXIT EXCEL */
PROCEDURE SAVE_SHEET IS
BEGIN
    IF OPEN_FLAG = 'Y' THEN
       ARGS := OLE2.CREATE_ARGLIST; 
       OLE2.ADD_ARG(ARGS,false); 
       OLE2.INVOKE(WORKBOOK, 'save',ARGS); 
       OLE2.DESTROY_ARGLIST(ARGS); 
       OLE2.INVOKE(APPLICATION, 'Quit');
       OLE2.RELEASE_OBJ(WORKSHEET);
       OLE2.RELEASE_OBJ(WORKSHEETS);
       OLE2.RELEASE_OBJ(WORKBOOK);
       OLE2.RELEASE_OBJ(WORKBOOKS);
       OLE2.RELEASE_OBJ(APPLICATION);
  END IF;
END;
/* SAVE THE SHEET IN ANOTHER FILENAME AND EXIT EXCEL */
PROCEDURE SAVEAS_SHEET(FILENAME IN VARCHAR2) IS
BEGIN
    IF OPEN_FLAG = 'Y' THEN
       ARGS := OLE2.CREATE_ARGLIST; 
       OLE2.ADD_ARG(ARGS,FILENAME); 
       OLE2.INVOKE(WORKBOOK, 'SaveAs',ARGS); 
       OLE2.DESTROY_ARGLIST(ARGS); 
       OLE2.INVOKE(APPLICATION, 'Quit');
       OLE2.RELEASE_OBJ(WORKSHEET);
       OLE2.RELEASE_OBJ(WORKSHEETS);
       OLE2.RELEASE_OBJ(WORKBOOK);
       OLE2.RELEASE_OBJ(WORKBOOKS);
       OLE2.RELEASE_OBJ(APPLICATION);
  END IF;
END;
/* EXIT EXCEL SHEET*/
PROCEDURE CLOSE_SHEET IS
BEGIN 
  IF OPEN_FLAG = 'Y' THEN
     ARGS := OLE2.CREATE_ARGLIST; 
     OLE2.ADD_ARG(ARGS,false); 
     OLE2.INVOKE(WORKBOOK, 'close',ARGS); 
     OLE2.DESTROY_ARGLIST(ARGS); 
     OLE2.INVOKE(APPLICATION, 'Quit');
     OLE2.RELEASE_OBJ(WORKSHEET);
     OLE2.RELEASE_OBJ(WORKSHEETS);
     OLE2.RELEASE_OBJ(WORKBOOK);
     OLE2.RELEASE_OBJ(WORKBOOKS);
     OLE2.RELEASE_OBJ(APPLICATION);
  END IF;
  OPEN_FLAG:='N';
END;

/* EXIT EXCEL SHEET*/
PROCEDURE CLOSE_APPS IS
BEGIN 
  IF OPEN_FLAG = 'Y' THEN
     OLE2.RELEASE_OBJ(WORKSHEET);
     OLE2.RELEASE_OBJ(WORKSHEETS);
     OLE2.RELEASE_OBJ(WORKBOOK);
     OLE2.RELEASE_OBJ(WORKBOOKS);
     OLE2.RELEASE_OBJ(APPLICATION);
  END IF;
  OPEN_FLAG:='N';
END;


END;