UTL_FILE used in apps

CREATE OR REPLACE PROCEDURE XXPOFLAT (
P_ERRBUF OUT VARCHAR2
,P_RETCODE OUT NUMBER
,P_PO_HEADER_ID IN NUMBER
,P_ORGID IN NUMBER) AS
CURSOR PO_data IS
SELECT '01'"TRANSFER_ID",pha.ORG_ID,msib.ORGANIZATION_ID
,MSIB.SEGMENT1"PART_NO"
,'LT'"PLANT_CODE"
,PHA.SEGMENT1"PURCHASE_ORDER_NUMBER"
,PLA.QUANTITY"QUANTITY"
,'YN'"CURRENCY"
,ROUND(PLA.UNIT_PRICE)"UNIT_PRICE"
,POV.VENDOR_NAME
,DECODE(PLLA.CANCEL_FLAG,'N',NULL,PLLA.NEED_BY_DATE)"PREVIOUS_DUE_DATE"
,DECODE(PLLA.CANCEL_FLAG,'N',NULL,PLLA.CREATION_DATE)"PREVIOUS_ISSUE_DATE"
,TO_CHAR(PLLA.APPROVED_DATE,'DD-MON-YYYY')"APPROVED_DATE"
-- ,(CASE WHEN PHA.CANCEL_FLAG='N'THEN PHA.REVISION_NUM ELSE 01 END)"REVISION_NUM"
,DECODE(PHA.CANCEL_FLAG,'N',PHA.REVISION_NUM,'01')"REVISION_LEVEL"
,PHA.ATTRIBUTE8"CLASSIFICATION_CODE"
,' ' Reason_code
,' ' Cancel_code
,DECODE(PLLA.CANCEL_FLAG,'N',NULL,PLLA.QUANTITY_CANCELLED)"QUANTITY_CANCELLED"
,' ' Fax_Reference
,MSIB.DESCRIPTION"PART_NAME"
FROM
PO_HEADERS_ALL PHA
,PO_LINES_ALL PLA
,MTL_SYSTEM_ITEMS_B MSIB
,PO_VENDORS POV
,PO_LINE_LOCATIONS_ALL PLLA
WHERE PHA.PO_HEADER_ID = P_PO_HEADER_ID
AND PHA.PO_HEADER_ID = PLA.PO_HEADER_ID
AND PLA.ITEM_ID = MSIB.INVENTORY_ITEM_ID
AND MSIB.ORGANIZATION_ID = P_ORGID
AND PHA.VENDOR_ID = POV.VENDOR_ID
AND PHA.PO_HEADER_ID = PLLA.PO_HEADER_ID
AND PLA.PO_LINE_ID = PLLA.PO_LINE_ID
AND PLLA.SHIP_TO_ORGANIZATION_ID = MSIB.ORGANIZATION_ID
AND PHA.APPROVED_FLAG ='Y' ;

v_file UTL_FILE.FILE_TYPE;

BEGIN
v_file := UTL_FILE.FOPEN(location => '/usr/tmp',
filename => 'PO_FLAT.txt',
open_mode => 'w',
max_linesize => 32767);
FOR cur_rec IN PO_data LOOP
UTL_FILE.PUT_LINE(v_file,
cur_rec.TRANSFER_ID || chr(9) ||
cur_rec.PART_NO || chr(9) ||
cur_rec.PLANT_CODE || chr(9) ||
cur_rec.PURCHASE_ORDER_NUMBER || chr(9) ||
cur_rec.QUANTITY || chr(9) ||
cur_rec.CURRENCY || chr(9) ||
cur_rec.UNIT_PRICE || chr(9) ||
cur_rec.VENDOR_NAME || chr(9) ||
cur_rec.PREVIOUS_DUE_DATE || chr(9) ||
cur_rec.PREVIOUS_ISSUE_DATE || chr(9) ||
cur_rec.APPROVED_DATE || chr(9) ||
cur_rec.REVISION_LEVEL || chr(9) ||
cur_rec.CLASSIFICATION_CODE || chr(9) ||
cur_rec.REASON_CODE || chr(9) ||
cur_rec.CANCEL_CODE || chr(9) ||
cur_rec.QUANTITY_CANCELLED || chr(9) ||
cur_rec.FAX_REFERENCE || chr(9) ||
cur_rec.PART_NAME);
END LOOP;
UTL_FILE.FCLOSE(v_file);

EXCEPTION
WHEN UTL_FILE.INVALID_PATH THEN
UTL_FILE.FCLOSE(v_file);
RAISE_APPLICATION_ERROR(-20000, 'File location is invalid.');

WHEN UTL_FILE.INVALID_MODE THEN
UTL_FILE.FCLOSE(v_file);
RAISE_APPLICATION_ERROR(-20001, 'The open_mode parameter in FOPEN is invalid.');

WHEN UTL_FILE.INVALID_FILEHANDLE THEN
UTL_FILE.FCLOSE(v_file);
RAISE_APPLICATION_ERROR(-20002, 'File handle is invalid.');

WHEN UTL_FILE.INVALID_OPERATION THEN
UTL_FILE.FCLOSE(v_file);
RAISE_APPLICATION_ERROR(-20003, 'File could not be opened or operated on as requested.');

WHEN UTL_FILE.READ_ERROR THEN
UTL_FILE.FCLOSE(v_file);
RAISE_APPLICATION_ERROR(-20004, 'Operating system error occurred during the read operation.');

WHEN UTL_FILE.WRITE_ERROR THEN
UTL_FILE.FCLOSE(v_file);
RAISE_APPLICATION_ERROR(-20005, 'Operating system error occurred during the write operation.');

WHEN UTL_FILE.INTERNAL_ERROR THEN
UTL_FILE.FCLOSE(v_file);
RAISE_APPLICATION_ERROR(-20006, 'Unspecified PL/SQL error.');

WHEN UTL_FILE.CHARSETMISMATCH THEN
UTL_FILE.FCLOSE(v_file);
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(v_file);
RAISE_APPLICATION_ERROR(-20008, 'The requested operation failed because the file is open.');

WHEN UTL_FILE.INVALID_MAXLINESIZE THEN
UTL_FILE.FCLOSE(v_file);
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(v_file);
RAISE_APPLICATION_ERROR(-20010, 'The filename parameter is invalid.');

WHEN UTL_FILE.ACCESS_DENIED THEN
UTL_FILE.FCLOSE(v_file);
RAISE_APPLICATION_ERROR(-20011, 'Permission to access to the file location is denied.');

WHEN UTL_FILE.INVALID_OFFSET THEN
UTL_FILE.FCLOSE(v_file);
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(v_file);
RAISE_APPLICATION_ERROR(-20013, 'The requested file delete operation failed.');

WHEN UTL_FILE.RENAME_FAILED THEN
UTL_FILE.FCLOSE(v_file);
RAISE_APPLICATION_ERROR(-20014, 'The requested file rename operation failed.');

WHEN OTHERS THEN
UTL_FILE.FCLOSE(v_file);
RAISE;
END;



SELECT * FROM USER_ERRORS WHERE NAME='XXPOFLAT'

DECLARE
ERR NUMBER;
ERRRETCODE VARCHAR2(1000);
BEGIN
XXPOFLAT(ERR,ERRRETCODE,6,84);
END;


SELECT * FROM USER_OBJECTS WHERE OBJECT_NAME='PO_CSV'


declare
V_chr varchar2(3);
ctr number;
begin
for ctr in 1..255 loop
select chr(ctr) into V_chr from dual;
-- dbms_output.put_line(V_chr);
dbms_output.put_line('Chr value '||ctr||' = '||V_chr||' ');
end loop;
end;


declare
V_chr varchar2(3);
ctr number;
begin
select chr(9) into V_chr from dual;
-- dbms_output.put_line(V_chr);
dbms_output.put_line('Chr value '||ctr||' = '||V_chr||' ');
end;