set termout off
/*
||  FILE:   ap_vend.sql
||
||  Import vendor records from the legacy system to Oracle
||  Purchasing/Payables
||
||  Make sure the directory where the source flat file will
||  be located is entered in the INIT.ORA file.  This directory
||  must be on the same operating platform as the INIT.ORA file.
||  Also, in a PC environment, it must be on the same drive.
||
||  The fields of the flat file must be tilde (~) delimited
||  with one record per line.  The information should be
||  structured as follows:
||
||          1~2~3~4~5~6~7~8~9~10
||
||  where:  1  = vendor number
||          2  = vendor name
||          3  = address line 1
||          4  = address line 2
||          5  = city
||          6  = state
||          7  = zip
||          8  = attention line
||          9  = tax ID
||          10 = 1099 flag
||
||
*/
set termout on

set serveroutput on size 100000
set verify off


/* Get user parameters */
accept S_FILENAME char prompt   'Enter the flat file name..'
accept S_FILEDIR char prompt    'Enter file directory......'
accept N_DEBUG_LEVEL number prompt 'DEBUG level (0,1,2).......'


/* Declare variables */
declare
  /* Flat file management variables */
  F_FILEHANDLE    utl_file.file_type;
  S_STRING        varchar2(1000);
  type T_READFILE is table of varchar2(100) index by binary_integer;
  T_FIELD         T_READFILE;
  N_FIELD_COUNT   number      := 10;    --Field count
  C_SEP           varchar2(1) := '~';   --Field separator
  C_ENC           varchar2(1) := NULL;  --Field enclosure
  N_LINE_COUNT    number      := 0;

  /* Working variables */
  N_TEMP_COUNT    number;
  N_AP_CCID       number;
  N_SOB_ID        number;
  N_TERM_ID       number;
  N_VEND_ID       number;
  N_VEND_NUM      number;
  S_LOC_CODE      varchar2(20);

  /* Exception handlers */
  FATAL_ERROR     exception;
  END_OF_FILE     exception;
  SKIP_LINE       exception;


/* Main conversion processing routine */
begin

  /* Declare local procedure */
  declare
    procedure parse_line(S_TEXT in varchar2)
    is
      N_START_POS   number      := 1;     --Field pointer
    begin

      /* Field read loop */
      for N_INDEX in 1..N_FIELD_COUNT loop

        /* Read the current field and record it in T_FIELD(x) */
        select  rtrim(substr(S_TEXT,N_START_POS,
                  decode(instr(S_TEXT,C_ENC||C_SEP,1,N_INDEX),
                    0, (length(S_TEXT) - N_START_POS + 1),
                    instr(S_TEXT,C_ENC||C_SEP,1,N_INDEX) -
                      N_START_POS + decode(C_ENC,NULL,0,1))))
        into    T_FIELD(N_INDEX)
        from    dual;

        /* Strip enclosing character */
        if (C_ENC is not NULL)
        then
          T_FIELD(N_INDEX) := substr(T_FIELD(N_INDEX),2,
            length(T_FIELD(N_INDEX))-2);
        end if;

        /* Move field pointer to next field */
        select  decode(instr(S_TEXT,C_ENC||C_SEP,1,N_INDEX),
                  0, length(S_TEXT) + 1,
                  instr(S_TEXT,C_ENC||C_SEP,1,N_INDEX) +
                    decode(C_ENC,NULL,1,2))
        into    N_START_POS
        from    dual;

      end loop;  --End of field read loop
    end parse_line;


  /* MAIN */
  begin

    /* Open data file for reading */
    dbms_output.put_line('Reading data flat file');
    begin
      F_FILEHANDLE := utl_file.fopen('&&S_FILEDIR', '&&S_FILENAME', 'r');
    exception
      when utl_file.INVALID_PATH then
        dbms_output.put_line(
          'ERROR: Directory &&S_FILEDIR not accessible');
        raise FATAL_ERROR;

      when utl_file.INVALID_OPERATION then
        dbms_output.put_line(
          'ERROR: File &&S_FILENAME not found');
        raise FATAL_ERROR;

      when OTHERS then
        dbms_output.put_line(
          'Unhandled error when opening source file');
        raise FATAL_ERROR;
    end;

    /* Get Accounts Payable GL account */
    begin
      select  code_combination_id
      into    N_AP_CCID
      from    gl.gl_code_combinations
      where   segment1 = '01'
      and     segment2 = '0101'
      and     segment3 = '30110'
      and     segment4 = '0000';
    exception
      when NO_DATA_FOUND
      then
        dbms_output.put_line('Invalid GL account');
        raise;
    end;

    /* Validate currency code */
    select  count(*)
    into    N_TEMP_COUNT
    from    fnd_currencies
    where   currency_code = 'USD';
    if (N_TEMP_COUNT != 1)
    then
      dbms_output.put_line('Invalid currency code');
      raise NO_DATA_FOUND;
    end if;

    /* Validate organization type */
    select  count(*)
    into    N_TEMP_COUNT
    from    po.po_lookup_codes
    where   lookup_type = 'ORGANIZATION TYPE'
    and     lookup_code = 'CORPORATION';
    if (N_TEMP_COUNT != 1)
    then
      dbms_output.put_line('Invalid currency code');
      raise NO_DATA_FOUND;
    end if;

    /* Validate pay data basis */
    select  count(*)
    into    N_TEMP_COUNT
    from    po_lookup_codes
    where   lookup_type = 'PAY DATE BASIS'
    and     lookup_code = 'DISCOUNT';
    if (N_TEMP_COUNT != 1)
    then
      dbms_output.put_line('Invalid pay data basis');
      raise NO_DATA_FOUND;
    end if;

    /* Get set of books ID (there should only be one) */
    begin
      select  set_of_books_id
      into    N_SOB_ID
      from    gl.gl_sets_of_books;
    exception
      when NO_DATA_FOUND
      then
        dbms_output.put_line('No set of books set up');
        raise;
    end;

    /* Get terms ID (all vendors will get the same terms) */
    begin
      select  term_id
      into    N_TERM_ID
      from    ap.AP_TERMS
      where   name = 'Net 45';
    exception
      when NO_DATA_FOUND
      then
        dbms_output.put_line('NET 45 payment terms not set up');
        raise;
    end;

    /* Get last conversion vendor number used */
    select  nvl(max(to_number(segment1)),0)
    into    N_VEND_NUM
    from    po_vendors
    where   to_number(segment1) < 20000;
    dbms_output.put_line('Starting vendor numbering at '||N_VEND_NUM);

    /* Line read loop */
    N_LINE_COUNT := 0;
    begin
      loop
      begin
        /* Get next line from file */
        begin
          utl_file.get_line(F_FILEHANDLE, S_STRING);
        exception
          when NO_DATA_FOUND then
            raise END_OF_FILE;
        end;
        N_LINE_COUNT := N_LINE_COUNT + 1;

        /* Skip blank lines */
        if (rtrim(ltrim(S_STRING)) IS NULL) then
          raise SKIP_LINE;
        end if;

        parse_line(S_STRING);

        /* Process line */
        if (&&N_DEBUG_LEVEL not in (1,2))
        then

          /* Print field contents */
          dbms_output.put_line('Field A: '||T_FIELD(1));
          dbms_output.put_line('Field B: '||T_FIELD(2));
          dbms_output.put_line('Field C: '||T_FIELD(3));
          dbms_output.put_line('Field D: '||T_FIELD(4));
          dbms_output.put_line('Field E: '||T_FIELD(5));
          dbms_output.put_line('Field F: '||T_FIELD(6));
          dbms_output.put_line('Field G: '||T_FIELD(7));
          dbms_output.put_line('Field H: '||T_FIELD(8));
          dbms_output.put_line('Field I: '||T_FIELD(9));
          dbms_output.put_line('Field J: '||T_FIELD(10));

        else

          /* Insert new vendor record if not already created */
          begin
            select  vendor_id
            into    N_VEND_ID
            from    po_vendors
            where   vendor_name = T_FIELD(2);
          exception
            when NO_DATA_FOUND
            then
            
              select  po_vendors_s.nextval
              into    N_VEND_ID
              from    dual;
              N_VEND_NUM := N_VEND_NUM + 1;
              if (&&N_DEBUG_LEVEL = 1)
              then
                dbms_output.put_line('Vendor Number: '||N_VEND_NUM);
              end if;

              insert
              into    po_vendors
              (       accts_pay_code_combination_id,
                      allow_substitute_receipts_flag,
                      allow_unordered_receipts_flag,
                      always_take_disc_flag,
                      amount_includes_tax_flag,
                      auto_calculate_interest_flag,
                      auto_tax_calc_flag,
                      auto_tax_calc_override,
                      created_by,
                      creation_date,
                      enabled_flag,
                      exclude_freight_from_discount,
                      exclusive_payment_flag,
                      hold_all_payments_flag,
                      hold_flag,
                      hold_future_payments_flag,
                      hold_unmatched_invoices_flag,
                      inspection_required_flag,
                      invoice_currency_code,
                      last_update_date,
                      last_update_login,
                      last_updated_by,
                      min_order_amount,
                      num_1099,
                      one_time_flag,
                      pay_date_basis_lookup_code,
                      pay_group_lookup_code,
                      payment_currency_code,
                      payment_method_lookup_code,
                      payment_priority,
                      segment1,
                      set_of_books_id,
                      summary_flag,
                      terms_date_basis,
                      terms_id,
                      type_1099,
                      vendor_id,
                      vendor_name,
                      vendor_type_lookup_code,
                      withholding_status_lookup_code
              )
              values
              (       N_AP_CCID,
                      'N',
                      'N',
                      'N',
                      'N',
                      'N',
                      'N',
                      'N',
                      1,
                      sysdate,
                      'Y',
                      'Y',
                      'N',  --??? exclusive payment flag
                      'N',
                      'N',
                      'N',
                      'N',
                      'N',
                      'USD',
                      sysdate,
                      1,
                      1,
                      0,
                      T_FIELD(9),
                      'N',
                      'DISCOUNT',
                      'VENDOR',
                      'USD',
                      'CHECK',
                      50,
                      rtrim(ltrim(to_char(N_VEND_NUM))),
                      N_SOB_ID,
                      'N',
                      'Invoice',
                      N_TERM_ID,
                      '1099_MISC',
                      N_VEND_ID,
                      T_FIELD(2),
                      'VENDOR',
                      'EXEMPT'
              );
          end;

          /* Insert new site record if not already created */
          select  count(*)
          into    N_TEMP_COUNT
          from    po_vendor_sites_all
          where   vendor_id = N_VEND_ID
          and     attribute1 = T_FIELD(1);
          if (N_TEMP_COUNT = 0)
          then

            /* Assign the site code */
            S_LOC_CODE := NULL;
            select  max(vendor_site_code)
            into    S_LOC_CODE
            from    po_vendor_sites_all
            where   vendor_id = N_VEND_ID
            and     substr(vendor_site_code,1,13) =
                      substr(T_FIELD(5),1,13);
            if (S_LOC_CODE is not NULL)
            then
              S_LOC_CODE := substr(S_LOC_CODE,1,13)||
                ltrim(to_char(to_number(nvl(substr(
                  S_LOC_CODE,14),'0'))+1,'09'));
            else
              S_LOC_CODE := substr(T_FIELD(5),1,13);
            end if;
            if (&&N_DEBUG_LEVEL = 1)
            then
              dbms_output.put_line('Site Code:     '||S_LOC_CODE);
            end if;

            /* Insert site record */
            insert
            into    po_vendor_sites_all
            (       accts_pay_code_combination_id,
                    address_line1,
                    address_line2,
                    address_line3,
                    always_take_disc_flag,
                    amount_includes_tax_flag,
                    attention_ar_flag,
                    attribute1,
                    auto_tax_calc_flag,
                    auto_tax_calc_override,
                    city,
                    created_by,
                    creation_date,
                    edi_id_number,
                    exclude_freight_from_discount,
                    exclusive_payment_flag,
                    hold_all_payments_flag,
                    hold_future_payments_flag,
                    hold_unmatched_invoices_flag,
                    invoice_currency_code,
                    language,
                    last_update_date,
                    last_update_login,
                    last_updated_by,
                    org_id,
                    pay_date_basis_lookup_code,
                    pay_group_lookup_code,
                    pay_site_flag,
                    payment_currency_code,
                    payment_method_lookup_code,
                    payment_priority,
                    purchasing_site_flag,
                    rfq_only_site_flag,
                    state,
                    tax_reporting_site_flag,
                    terms_date_basis,
                    terms_id,
                    vendor_id,
                    vendor_site_code,
                    vendor_site_id,
                    zip
            )
            select  N_AP_CCID,
                    T_FIELD(3),
                    T_FIELD(4),
                    T_FIELD(8),
                    'N',
                    'N',
                    'N',
                    T_FIELD(1),
                    'N',
                    'N',
                    T_FIELD(5),
                    1,
                    sysdate,
                    0,
                    'Y',
                    'N',
                    'N',
                    'N',
                    'N',
                    'USD',
                    'US',
                    sysdate,
                    1,
                    1,
                    NULL, --??? org_id,
                    'DISCOUNT',
                    NULL,
                    'Y',
                    'USD',
                    'CHECK',
                    50,
                    'Y',
                    'N',
                    T_FIELD(6),
                    decode(upper(T_FIELD(10)),
                      NULL,'N', 'Y','Y', 'N'),
                    'Invoice',
                    N_TERM_ID,
                    N_VEND_ID,
                    S_LOC_CODE,
                    po_vendor_sites_s.nextval,
                    T_FIELD(7)
            from    dual;
          end if;

          /* Save work */
          if (&&N_DEBUG_LEVEL = 3)
          then
            commit;
          end if;
        end if;

      exception
        when SKIP_LINE then
          NULL;
      end;
      end loop;
    exception
      when END_OF_FILE then
        dbms_output.put_line(N_LINE_COUNT||' lines read');
    end; --End of line read loop

    /* Close file */
    utl_file.fclose(F_FILEHANDLE);

    if (&&N_DEBUG_LEVEL != 3)
    then
      rollback;
    end if;
  end;
exception
    when FATAL_ERROR then
        rollback;
        utl_file.fclose(F_FILEHANDLE);
        dbms_output.put_line('Conversion process terminated');

    when OTHERS then
        dbms_output.put_line('Error at line '||N_LINE_COUNT);
        raise;
end;
/

set verify on
set serveroutput off
