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