function CF_ATTACHFormula return Char is
V_ATTACH Varchar2(32000);
V_LONG_TXT LONG(32000);
begin
SELECT DLT.LONG_TEXT INTO V_LONG_TXT
/*AD.SEQ_NUM ,
DCT.USER_NAME ,
DAT.USER_NAME ,
AD.ATTACHED_DOCUMENT_ID ,
DET.USER_ENTITY_NAME ,
DAT.NAME ,
D.DOCUMENT_ID ,
AD.ENTITY_NAME ,
AD.PK1_VALUE ,
D.MEDIA_ID ,
D.URL ,
DT.TITLE ,
DLT.LONG_TEXT */
FROM FND_DOCUMENT_DATATYPES DAT,
FND_DOCUMENT_ENTITIES_TL DET ,
FND_DOCUMENTS_TL DT ,
FND_DOCUMENTS D ,
FND_DOCUMENT_CATEGORIES_TL DCT ,
FND_ATTACHED_DOCUMENTS AD ,
FND_DOCUMENTS_LONG_TEXT DLT ,
PO_HEADERS_ALL PHA
WHERE D.DOCUMENT_ID = AD.DOCUMENT_ID
AND DT.DOCUMENT_ID = D.DOCUMENT_ID
AND DCT.CATEGORY_ID = D.CATEGORY_ID
AND D.DATATYPE_ID = DAT.DATATYPE_ID
AND AD.ENTITY_NAME = DET.DATA_OBJECT_CODE
AND DLT.MEDIA_ID = D.MEDIA_ID
AND DAT.NAME = 'LONG_TEXT'
AND ENTITY_NAME = 'PO_HEADERS'
AND AD.PK1_VALUE = PHA.PO_HEADER_ID
AND PHA.SEGMENT1 = :PO_ORDER;
DECLARE
f_line VARCHAR2 (32000);
f UTL_FILE.file_type;
f_dir VARCHAR2 (250);
fname VARCHAR2 (50);
Comma1 VARCHAR (10);
Comma2 VARCHAR (10);
Comma3 VARCHAR (10);
Comma4 VARCHAR (10);
Comma5 VARCHAR (10);
BEGIN
f_dir := '/usr/tmp';
fname := 'TEST.txt';
f := UTL_FILE.fopen (f_dir, fname, 'r');
LOOP
BEGIN
UTL_FILE.get_line (f, f_line);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
EXIT;
END;
V_ATTACH := V_ATTACH || CHR(10) ||f_line;
SRW.MESSAGE(10001, V_LONG_TXT);
--- DBMS_OUTPUT.put_line( f_line );
END LOOP;
UTL_FILE.fclose (f);
COMMIT;
Return V_ATTACH;
END;
end;
V_ATTACH Varchar2(32000);
V_LONG_TXT LONG(32000);
begin
SELECT DLT.LONG_TEXT INTO V_LONG_TXT
/*AD.SEQ_NUM ,
DCT.USER_NAME ,
DAT.USER_NAME ,
AD.ATTACHED_DOCUMENT_ID ,
DET.USER_ENTITY_NAME ,
DAT.NAME ,
D.DOCUMENT_ID ,
AD.ENTITY_NAME ,
AD.PK1_VALUE ,
D.MEDIA_ID ,
D.URL ,
DT.TITLE ,
DLT.LONG_TEXT */
FROM FND_DOCUMENT_DATATYPES DAT,
FND_DOCUMENT_ENTITIES_TL DET ,
FND_DOCUMENTS_TL DT ,
FND_DOCUMENTS D ,
FND_DOCUMENT_CATEGORIES_TL DCT ,
FND_ATTACHED_DOCUMENTS AD ,
FND_DOCUMENTS_LONG_TEXT DLT ,
PO_HEADERS_ALL PHA
WHERE D.DOCUMENT_ID = AD.DOCUMENT_ID
AND DT.DOCUMENT_ID = D.DOCUMENT_ID
AND DCT.CATEGORY_ID = D.CATEGORY_ID
AND D.DATATYPE_ID = DAT.DATATYPE_ID
AND AD.ENTITY_NAME = DET.DATA_OBJECT_CODE
AND DLT.MEDIA_ID = D.MEDIA_ID
AND DAT.NAME = 'LONG_TEXT'
AND ENTITY_NAME = 'PO_HEADERS'
AND AD.PK1_VALUE = PHA.PO_HEADER_ID
AND PHA.SEGMENT1 = :PO_ORDER;
DECLARE
f_line VARCHAR2 (32000);
f UTL_FILE.file_type;
f_dir VARCHAR2 (250);
fname VARCHAR2 (50);
Comma1 VARCHAR (10);
Comma2 VARCHAR (10);
Comma3 VARCHAR (10);
Comma4 VARCHAR (10);
Comma5 VARCHAR (10);
BEGIN
f_dir := '/usr/tmp';
fname := 'TEST.txt';
f := UTL_FILE.fopen (f_dir, fname, 'r');
LOOP
BEGIN
UTL_FILE.get_line (f, f_line);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
EXIT;
END;
V_ATTACH := V_ATTACH || CHR(10) ||f_line;
SRW.MESSAGE(10001, V_LONG_TXT);
--- DBMS_OUTPUT.put_line( f_line );
END LOOP;
UTL_FILE.fclose (f);
COMMIT;
Return V_ATTACH;
END;
end;