Flat File - Data Migration

How about a design pattern like this?

CREATE OR REPLACE DIRECTORY c_external_csv_tab_dir AS 'c:\some_folder_on_database_server\';

CREATE TABLE c_external_migration_tab
(
  a1   VARCHAR2(256),
  a2   VARCHAR2(256),
  a3   VARCHAR2(256),
  a4   VARCHAR2(256),
  a5   VARCHAR2(256),
  a6   VARCHAR2(256),
  a7   VARCHAR2(256),
  a8   VARCHAR2(256),
  a9   VARCHAR2(256),
  a10  VARCHAR2(256)
)
ORGANIZATION EXTERNAL
(
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY c_external_csv_tab_dir
  ACCESS PARAMETERS 
  (
    RECORDS DELIMITED BY NEWLINE
    CHARACTERSET AL32UTF8
    STRING SIZES ARE IN CHARACTERS
     BADFILE 'c_external_migration_tab.bad'
     DISCARDFILE 'c_external_migration_tab.dis'
    LOGFILE 'c_external_migration_tab.log'
     SKIP 1
     FIELDS TERMINATED BY ","  OPTIONALLY ENCLOSED BY '"'
     LTRIM
        MISSING FIELD VALUES ARE NULL
     (
      a1   CHAR(256),
      a2   CHAR(256),
      a3   CHAR(256),
      a4   CHAR(256),
      a5   CHAR(256),
      a6   CHAR(256),
      a7   CHAR(256),
      a8   CHAR(256),
      a9   CHAR(256),
      a10  CHAR(256)
     )
  )
  location (c_external_csv_tab_dir:'c_external_migration_tab.csv')
)
REJECT LIMIT UNLIMITED;

GRANT SELECT ON c_external_migration_tab TO ifsinfo WITH GRANT OPTION;
GRANT SELECT ON c_external_migration_tab TO ifssys;
GRANT SELECT ON c_external_migration_tab TO fnd_admin;


DECLARE
   info_       VARCHAR2(32767);
   objid_      VARCHAR2(32767);
   objversion_ VARCHAR2(32767);
   attr_       VARCHAR2(32767);
BEGIN
   FOR header_rec_ IN (
      SELECT e.a1 AS wo,
             e.a2 AS description,
             e.a3 AS maint_org
        FROM c_external_csv_tab_dir
    GROUP BY e.a1, e.a2, e.a3
    ORDER BY 1, 2, 3
   ) LOOP
      client_sys.clear_attr(attr_);
      client_sys.add_to_attr('WO',          header_rec_.wo,          attr_);
      client_sys.add_to_attr('DESCRIPTION', header_rec_.description, attr_);
      client_sys.add_to_attr('MAINT_ORG',   header_rec_.maint_org,   attr_);
      header_lu_api.new__(info_, objid_, objversion_, attr_, 'DO');
      FOR line_rec_ IN (
         SELECT e.a4 AS line,
                e.a5 AS description,
                e.a6 AS duration,
                e.a7 AS plannedmen
           FROM c_external_csv_tab_dir
          WHERE e.a1 = header_rec_.wo
            AND e.a2 = header_rec_.description
            AND e.a3 = header_rec_.maint_org
       ORDER BY 1, 2, 3, 4
      ) LOOP
         client_sys.clear_attr(attr_);
         client_sys.add_to_attr('LINE',        line_rec_.line,        attr_);
         client_sys.add_to_attr('DESCRIPTION', line_rec_.description, attr_);
         client_sys.add_to_attr('DURATION',    line_rec_.duration,    attr_);
         client_sys.add_to_attr('PLANNEDMEN',  line_rec_.plannedmen,  attr_);
         line_lu_api.new__(info_, objid_, objversion_, attr_, 'DO');
      END LOOP;
   END LOOP;
END;
/

Thanks Kevin - I hadn't considered approaching the issue like this but it makes sense to do so.  Your code has given me some good ideas on where to take the solution outside of the migration job. 

1. Create a job to load file into a temporary table by using “CREATE_TABLE_FROM_FILE”.
2. Create a job with “MIGRATE_SOURCE_DATA” In Method List  and use table for job 1 as source. You can add first the master and then the detail view in the method list with “Action” “In Loop” and then both will be executed for one line. If you only specify “New” no update will be done on master when you have a work order with more than one line.

Using generic column names like that allows you to reuse that external table for other purposes, and I never write migration jobs. (The version of this I personally use has 120 columns. If you need more than that, you’re doing something wrong.)