Tuesday, October 5, 2010

How to convert a report into excel sheet

PROCEDURE U_1ButtonAction is
CURSOR cur is
  SELECT A.EMP_CODE,A.EMP_TITLE ||' '||A.EMP_FNAME ||' '||A.EMP_LNAME EMP_NAME,A.EMP_DEPT,A.EMP_TYPE,EMP_DOJ
FROM BPAYT030 A WHERE A.EMP_TYPE = DECODE(:P_EMPTYPE,'A',A.EMP_TYPE,:P_EMPTYPE)
AND A.EMP_DEPT = DECODE(:P_EMPDEPT,'A',A.EMP_DEPT,:P_EMPDEPT)  AND EMP_STATUS ='Y';
--AND A.JOB_TITLE  = DECODE(:P_JOB,'A',A.EMP_DEPT,:P_JOB)
V_OUTFILE TEXT_IO.FILE_TYPE;
V_OUTSTRING    VARCHAR2(1000);
L_FILENAME     VARCHAR2(100);
L_SR           NUMBER:=0;
L_DESC         VARCHAR2(200);
L_DESC1        VARCHAR2(200);
BEGIN
L_FILENAME := 'C:'||'EMPLOYEE LIST'||TO_CHAR(SYSDATE,'DDMMRR')||'.CSV';
 V_outfile := text_io.fopen(L_FILENAME,'W');   
 V_outstring := ','||','||'EMPLOYEE TYPE/DEPARTMENT WISE DETAILS';
TEXT_IO.PUT_LINE(V_OUTFILE, V_OUTSTRING);
V_outstring :='';
TEXT_IO.PUT_LINE(V_OUTFILE, V_OUTSTRING);
V_outstring := :P_COMP;
TEXT_IO.PUT_LINE(V_OUTFILE, V_OUTSTRING);
  --EXCEL FILE HEADS
 V_outstring :='EMPLOYEE TYPE'||','||'DEPARTMENT'||','||'CODE'||','||'DOJ' ||','||'EMPLOYEE NAME';
TEXT_IO.PUT_LINE(V_OUTFILE, V_OUTSTRING);
FOR REC IN CUR LOOP
SELECT CODE_DESC INTO L_DESC FROM BCOMT011 WHERE TYPE_CODE  = REC.EMP_TYPE;
SELECT CODE_DESC INTO L_DESC1 FROM BCOMT011 WHERE TYPE_CODE  = REC.EMP_DEPT;
--DATA IN EXCEL
V_outstring :=  L_DESC||','||L_DESC1||','||REC.EMP_CODE||','||EC.EMP_DOJ||','||REC.EMP_NAME;
TEXT_IO.PUT_LINE(V_OUTFILE, V_OUTSTRING);
END LOOP;
text_io.fclose(v_outfile);
SRW.MESSAGE(100,'File '||L_FILENAME|| ' Generated Successfully....');
EXCEPTION      
WHEN OTHERS THEN
SRW.MESSAGE('1',SQLERRM);
text_io.fclose(v_outfile);
END;

No comments:

Post a Comment