best approach to import CSV file from FTP

We are on apps9. I would use a file data migration job to load the csv into the database and then use plsql procedure and api’s to update the relevant objects in IFS. You would need something to copy the file from FTP to the utl_file_dir.

In Apps 10 onwards you can directly call PLSQL method instead of BizApi from routing address. Therefore no need of transformer and BizApi development. CSV can be parsed and processed inside the PLSQL method.

Read More here: PL/SQL Methods (ifs.com)

Cheers!

Damith

I've found using Oracle Loader external tables is easier than using migration jobs. They're far easier to reuse for multiple purposes.

You'll need to edit this if you want more than 10 columns or more than 256 characters in your data. You'll also need to cast strings to numbers or dates as needed downstream.

Reading from a CSV file sitting in Oracle directory is a lot like reading from a view into that file.

1-- AS SYS:
2CREATE OR REPLACE DIRECTORY c_external_csv_dir AS '\\your\server\location';
3GRANT read, write, execute ON DIRECTORY c_external_csv_dir TO ifsapp;
4GRANT read, write, execute ON DIRECTORY c_external_csv_dir TO ifssys;
5
6-- AS IFSAPP:
7CREATE TABLE c_external_csv_tab
8(
9   a1   VARCHAR2(256),
10   a2   VARCHAR2(256),
11   a3   VARCHAR2(256),
12   a4   VARCHAR2(256),
13   a5   VARCHAR2(256),
14   a6   VARCHAR2(256),
15   a7   VARCHAR2(256),
16   a8   VARCHAR2(256),
17   a9   VARCHAR2(256),
18   a10  VARCHAR2(256)
19)
20ORGANIZATION EXTERNAL
21(
22   TYPE ORACLE_LOADER
23   DEFAULT DIRECTORY c_external_csv_dir
24   ACCESS PARAMETERS 
25   (
26      RECORDS DELIMITED BY newline
27      BADFILE 'c_external_csv_tab.bad'
28      DISCARDFILE 'c_external_csv_tab.dis'
29      LOGFILE 'c_external_csv_tab.log'
30      SKIP 1
31      FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
32      LTRIM
33      MISSING FIELD VALUES ARE NULL
34      (
35         a1   CHAR(256),
36         a2   CHAR(256),
37         a3   CHAR(256),
38         a4   CHAR(256),
39         a5   CHAR(256),
40         a6   CHAR(256),
41         a7   CHAR(256),
42         a8   CHAR(256),
43         a9   CHAR(256),
44         a10  CHAR(256)
45      )
46  )
47  LOCATION (c_external_csv_dir:'c_external_csv_tab.csv')
48)
49REJECT LIMIT UNLIMITED;
50
51GRANT SELECT ON c_external_csv_tab TO ifsinfo WITH GRANT OPTION;
52GRANT SELECT ON c_external_csv_tab TO ifssys WITH GRANT OPTION;
53GRANT SELECT ON c_external_csv_tab TO ifsprint;
54
55
56-- C_EXTERNAL_CSV_TAB now acts like a view to read directly from the CSV.
57-- Drop in a CSV file into that directory, then read from it like this:
58
59SELECT * FROM c_external_csv_tab;
60