Loyality Extraction Anonymous procedure to fetch the loyality data in a particular location in RESA (Region wise *.CSV file has been created at a particular location using UTL Package)

---/rms-s9c/rms/d01/install_dump/oradb10g/dbadmin/rms_admin/utlfile/loylity declare CURSOR CUR_VAT_REGION IS SELECT vat_region FROM vat_region vr WHERE EXISTS ( SELECT 1 FROM store s WHERE EXISTS ( SELECT 1 FROM sa_exported WHERE sa_exported.store = s.store AND sa_exported.store BETWEEN 999 and 9999 AND TRUNC(sa_exported.exp_datetime) = '28-May-12' AND sa_exported.status = 'P' AND sa_exported.system_code = 'RMS' ) AND s.VAT_REGION = vr.vat_region ) ; ---This package is to get the region code to create the CSV store wise firstly CURSOR CUR_LOYALITY_DATA(p_vat_region number) IS SELECT sth.store, ---"Store No", sth.register, --"POS Terminal No", sth.ref_no2, --"Transaction No." , sth.tran_no, --"Receipt NO", to_char(sth.tran_datetime,'dd/mm/yyyy') tran_datetime, --"Transaction Date" , sth.ref_no4, --"Loyalty Scheme", sth.ref_no3, --"Loyalty Card No.", stt.TENDER_TYPE_ID, --"Tender Code ", stt.tender_amt, --"Tender Amount ", sth.value ---" Bill Amount " from sa_exported sexp , sa_tran_head sth, sa_tran_tender stt,store s where sexp.store_day_seq_no = sth.store_day_seq_no and sexp.tran_seq_no = sth.TRAN_SEQ_NO and sexp.store = sth.store and sexp.day = sth.day and sth.TRAN_SEQ_NO = stt.tran_seq_no and sth.store = stt.store and sth.day = stt.day and sexp.store = s.store and trunc(sexp.exp_datetime) = '28-May-12' and sth.status = 'P' and sth.tran_type in ('SALE','RETURN') and ref_no3 is not null and stt.tender_type_id != 7002 and SYSTEM_CODE = 'RMS' and sexp.status = 'P' and s.VAT_REGION = p_vat_region; l_id utl_file.file_type; v_time_stamp varchar2(40); BEGIN SELECT TO_CHAR(SYSDATE, 'DDMMYYYYHH12MISS') INTO v_time_stamp FROM DUAL; FOR i IN CUR_VAT_REGION LOOP l_id := utl_file.fopen('/rms-s9c/rms/d01/install_dump/oradb10g/dbadmin/rms_admin/utlfile',i.vat_region||'Loyalty_SM_VATREGION_'||v_time_stamp||'.csv','W'); FOR j IN CUR_LOYALITY_DATA(i.vat_region) LOOP utl_file.put_line(l_id, j.store||','|| j.register||','|| --j.tran_seq_no||','|| j.ref_no2||','|| j.tran_no||','|| j.tran_datetime||','|| j.ref_no4||','|| j.ref_no3||','|| j.TENDER_TYPE_ID||','|| j.tender_amt||','|| j.value||REPLACE(chr(13),'') ); END LOOP; --vNewLine := null; utl_file.fclose(l_id); END LOOP; EXCEPTION WHEN UTL_FILE.INVALID_PATH THEN UTL_FILE.FCLOSE(l_id); RAISE_APPLICATION_ERROR(-20000, 'File location is invalid.'); WHEN UTL_FILE.INVALID_MODE THEN UTL_FILE.FCLOSE(l_id); RAISE_APPLICATION_ERROR(-20001, 'The open_mode parameter in FOPEN is invalid.'); WHEN UTL_FILE.INVALID_FILEHANDLE THEN UTL_FILE.FCLOSE(l_id); RAISE_APPLICATION_ERROR(-20002, 'File handle is invalid.'); WHEN UTL_FILE.INVALID_OPERATION THEN UTL_FILE.FCLOSE(l_id); RAISE_APPLICATION_ERROR(-20003, 'File could not be opened or operated on as requested.'); WHEN UTL_FILE.READ_ERROR THEN UTL_FILE.FCLOSE(l_id); RAISE_APPLICATION_ERROR(-20004, 'Operating system error occurred during the read operation.'); WHEN UTL_FILE.WRITE_ERROR THEN UTL_FILE.FCLOSE(l_id); RAISE_APPLICATION_ERROR(-20005, 'Operating system error occurred during the write operation.'); WHEN UTL_FILE.INTERNAL_ERROR THEN UTL_FILE.FCLOSE(l_id); RAISE_APPLICATION_ERROR(-20006, 'Unspecified PL/SQL error.'); WHEN UTL_FILE.CHARSETMISMATCH THEN UTL_FILE.FCLOSE(l_id); RAISE_APPLICATION_ERROR(-20007, 'A file is opened using FOPEN_NCHAR, but later I/O ' || 'operations use nonchar functions such as PUTF or GET_LINE.'); WHEN UTL_FILE.FILE_OPEN THEN UTL_FILE.FCLOSE(l_id); RAISE_APPLICATION_ERROR(-20008, 'The requested operation failed because the file is open.'); WHEN UTL_FILE.INVALID_MAXLINESIZE THEN UTL_FILE.FCLOSE(l_id); RAISE_APPLICATION_ERROR(-20009, 'The MAX_LINESIZE value for FOPEN() is invalid; it should ' || 'be within the range 1 to 32767.'); WHEN UTL_FILE.INVALID_FILENAME THEN UTL_FILE.FCLOSE(l_id); RAISE_APPLICATION_ERROR(-20010, 'The filename parameter is invalid.'); WHEN UTL_FILE.ACCESS_DENIED THEN UTL_FILE.FCLOSE(l_id); RAISE_APPLICATION_ERROR(-20011, 'Permission to access to the file location is denied.'); WHEN UTL_FILE.INVALID_OFFSET THEN UTL_FILE.FCLOSE(l_id); RAISE_APPLICATION_ERROR(-20012, 'The ABSOLUTE_OFFSET parameter for FSEEK() is invalid; ' || 'it should be greater than 0 and less than the total ' || 'number of bytes in the file.'); WHEN UTL_FILE.DELETE_FAILED THEN UTL_FILE.FCLOSE(l_id); RAISE_APPLICATION_ERROR(-20013, 'The requested file delete operation failed.'); WHEN UTL_FILE.RENAME_FAILED THEN UTL_FILE.FCLOSE(l_id); RAISE_APPLICATION_ERROR(-20014, 'The requested file rename operation failed.'); WHEN OTHERS THEN UTL_FILE.FCLOSE(l_id); RAISE; -- WHEN UTL_FILE.INVALID_PATH THEN -- utl_file.fclose(l_id); -- RAISE_APPLICATION_ERROR(-20000, 'gg File location is invalid.'); -- WHEN OTHERS THEN -- utl_file.fclose(l_id); -- RAISE; END; /