CREATE OR REPLACE PROCEDURE EMP_CSV AS CURSOR c_data IS SELECT empno, ename, job, mgr, TO_CHAR(hiredate,'DD-MON-YYYY') AS hiredate, sal, comm, deptno FROM emp ORDER BY ename; v_file UTL_FILE.FILE_TYPE; BEGIN v_file := UTL_FILE.FOPEN(location => 'EXTRACT_DIR', filename => 'emp_csv.txt', open_mode => 'w', max_linesize => 32767); FOR cur_rec IN c_data LOOP UTL_FILE.PUT_LINE(v_file, cur_rec.empno || ',' || cur_rec.ename || ',' || cur_rec.job || ',' || cur_rec.mgr || ',' || cur_rec.hiredate || ',' || cur_rec.empno || ',' || cur_rec.sal || ',' || cur_rec.comm || ',' || cur_rec.deptno); END LOOP; UTL_FILE.FCLOSE(v_file); EXCEPTION WHEN OTHERS THEN UTL_FILE.FCLOSE(v_file); RAISE; END; /
--NewHireRehire_AAP_DM SELECT DISTINCT PER.PERSON_NUMBER, ASSIGNMENT_SEQUENCE, NAME.DISPLAY_NAME AS NAME, case when ASS.REASON_CODE in ('REHIRE') then ' ' else TO_CHAR((SELECT MIN(effective_START_date) FROM PER_ALL_ASSIGNMENTS_M WHERE PERSON_ID = ASS.PERSON_ID and ASSIGNMENT_TYPE in ('E') AND ACTION_CODE in ('HIRE','ADD_AN_EMPLOYEE','HIRE_ADD_WORK_RELATION') AND EFFECTIVE_START_DATE = ASS.EFFECTIVE_START_DATE ),'MM/DD/YYYY') end as HIRE_DATE, case when ASS.REASON_CODE in ('REHIRE') then to_char((SELECT MAX(effective_START_date) FROM PER_ALL_ASSIGNMENTS_M WHERE PERSON_ID = ASS.PERSON_ID AND (reason_CODE IN ('REHIRE','REHIRE_WKR') or (ACTION_CODE in ('REHIRE'))) AND EFFECTIVE_START_DATE = ASS.EFFECTIVE_START_DATE),'MM/DD...
Comments
Post a Comment