How to upload data to the EBS forms data block

Create a temporary table: XXX_TEMP_FILES
The table creation statement is:
Create table XXX_TEMP_FILES (
FILE_ID NUMBER,
Seq number,
TEXT VARCHAR2 (2000 ),
CREATION_DATE DATE
)
The purpose of creating this table is to extract the data from the file_data field of the table FND_LOBS through the program, because the data in this table is of the lobs type, therefore, the entire file is placed in this field. We use this temporary table to split the data file into a row and a record. then, each row is located based on the score.
2. Create an upload package: XXX_FILE_IO
XXX_FILE_IO contains three subprograms:
INS_TEMP_FILES: obtains the uploaded data from the fnd_lobs table, splits it by row, and inserts it into the: XXX_TEMP_FILES table.
SEL_TEMP_FILES: extract data from XXX_TEMP_FILES
DEL_TEMP_FILES: the data has been uploaded to the Form interface. You need to delete the file.
The main statement is:
PROCEDURE INS_TEMP_FILES (p_file_id in number) IS
W_integer INTEGER;
W_blob BLOB;

W_raw RAW (10 );
W_buff VARCHAR2 (30000 );
W_line VARCHAR2 (30000 );
W_len INTEGER;
Eofsw BOOLEAN: = FALSE;
Offset INTEGER;
W_ B _len NUMBER: = 0;
W_num NUMBER;
W_seq NUMBER: = 0;

BEGIN
SELECT file_data INTO w_blob FROM fnd_lobs WHERE file_id = p_file_id;

Offset: = 1;
LOOP
Exit when eofsw;
W_seq: = w_seq + 1;
W_raw: = utl_raw.cast_to_raw (chr (10 ));
W_num: = dbms_lob.instr (w_blob, w_raw, offset, 1 );
W_len: = w_num-w_ B _len;
W_ B _len: = w_num;

IF w_num = 0
THEN
W_len := 20000;
Eofsw: = TRUE;
End if;

BEGIN
DBMS_LOB.READ (w_blob, w_len, offset, w_buff );
EXCEPTION
WHEN no_data_found THEN
EXIT;
WHEN OTHERS THEN
RAISE;
END;

W_line: = utl_raw.cast_to_varchar2 (w_buff );

Select replace (w_line, chr (10), NULL) INTO w_line FROM dual;
Select replace (w_line, chr (13), NULL) INTO w_line FROM dual;

Insert into XXX_TEMP_FILES
(FILE_ID, SEQ, TEXT, CREATION_DATE)
VALUES
(P_file_id, w_seq, w_line, SYSDATE );

Offset: = offset + w_len;
End loop;

END;
3. Run the. XXX_UPLOAD package in the Program Unit in the Form file.
It is best to make this file into a PLL and pack it together with other customized and useful programs. Uploading to form is a useful and Common Program of the system.
This tool uses FND_GFM to upload files to FND_LOBS and split the data in the package created in the second step:
P_file_id: = NULL;
Access_id: = FND_GFM.AUTHORIZE (NULL );
FND_PROFILE.GET ('apps _ WEB_AGENT ', l_server_url );
Rochelle URL: = rtrim (l_server_url, '/') |
'/Fnd_file_upload.displayGFMform? Access_id = '|
To_char (access_id) |
Chr (38) |
'L _ server_url = '|
Rochelle server_url;
If (l_url is NULL) then
Raise form_trigger_failure;
Return NULL;
End if;
FND_UTILITIES.OPEN_URL (l_url );
FND_MESSAGE.SET_NAME ('fnd', 'atchmt-FILE-UPLOAD-COMPLETE ');
Button_choice: = FND_MESSAGE.QUESTION (
Button1 => 'yes ',
Button2 => null,
Button3 => 'no ',
Default_btn => 1,
Cancel_btn => 3,
Icon => 'Question'
);
If button_choice = 1 then

P_file_id: = FND_GFM.GET_FILE_ID (access_id );
XXX_FILE_IO.INS_TEMP_FILES (p_file_id );
-- Pcm_dbms_lob.UPOPEN (p_file_id );
Else
Return NULL;
End if;

Return p_file_id;
1. Create a Program in the Program Unit in the Form file, for example, UPLOAD_XXX_XXX (X is converted according to actual needs)
The file UPLOAD_XXX_XXX needs to be modified in the actual application, and the actual field modification program to be imported is used. the main function is to read data from XXX_TEMP_FILES and then map each data to the form Domain Based on the delimiter.

 

Author: "Soy Milk"