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