Sample SQL for generating external keys

Sample SQL statements for generating external keys for most data types that can have external keys are included below. The SQL must be used as a guide only. These SQLs may not always be used to generate external keys. A field in the external system may exist that can be used as the external key. The SQL is database platform dependent, and is Oracle specific. Moreover, depending on customer data, some modifications may be required.

• Business Rule Instance

  
  insert into alex_external_key (bus_obj_type_id, bob_id, new_external_key) select 111000,
  a.br_instance_id, ltrim(rtrim(b.bob_name)) || '*' || rtrim(ltrim(c.event_name)) || '*' ||
  ltrim(rtrim(d.rule_title)) from albr_instance a, almt_bob_type b, albr_event c, albr_defn d where
  a.bus_obj_type_id = b.bus_obj_type_id and a.br_event_id = c.br_event_id and a.rule_defn_id =
  d.rule_defn_id;

• Credit Card

  
  insert into alex_external_key (bus_obj_type_id, bob_id, new_external_key) select 50000,
  ccard_id, ltrim(rtrim(ccard_account)) from alcc_card;

• Credit Card Processor

  
  insert into alex_external_key (bus_obj_type_id, bob_id, new_external_key) select 53000,
  cc_processor_id, ltrim(rtrim(cc_processor_name)) from alcc_processor;

• Credit Card SIC code

  
  insert into alex_external_key (bus_obj_type_id, bob_id, new_external_key) select 52000,
  sic_code_id, ltrim(rtrim(sic_code)) from alcc_sic_code where sic_code is not null;
  
  insert into alex_external_key (bus_obj_type_id, bob_id, new_external_key) select 52000,
  sic_code_id, 'UNKNOWN' from alcc_sic_code where sic_code is null;

• Accounting Calendar

  
  insert into alex_external_key (bus_obj_type_id, bob_id, new_external_key) select 999058300, 
  accounting_calendar_id, ltrim(rtrim(accounting_calendar_name)) from alco_accounting_calendar;
  

• Address

  
  insert into alex_external_key (bus_obj_type_id, bob_id, new_external_key) select 135450,
  address_id, ltrim(rtrim(address_name)) from alco_address;
  

• Charge Role

  
  insert into alex_external_key (bus_obj_type_id, bob_id, new_external_key) select 55900,
  charge_role_id, ltrim(rtrim(charge_role_name)) from alco_charge_role;
  

• Charge Type

  
  insert into alex_external_key (bus_obj_type_id, bob_id, new_external_key) select 55800,
  charge_type_id, ltrim(rtrim(charge_name)) from alco_charge_types;
  

• Client

  
  insert into alex_external_key (bus_obj_type_id, bob_id, new_external_key) select 29600,
  client_id, ltrim(rtrim(client_code)) from alco_clients;
  

• Company

  
  insert into alex_external_key (bus_obj_type_id, bob_id, new_external_key) select 28000,
   company_id, ltrim(rtrim(company_code)) from alco_company;

• Cost Center

  
  insert into alex_external_key (bus_obj_type_id, bob_id, new_external_key) select 29500,
  a.cost_center_id, ltrim(rtrim(b.company_code)) || '*' || rtrim(ltrim(c.division_code)) || '*' ||
  rtrim(ltrim(d.department_code)) || '*' || ltrim(rtrim(a.cost_center_code)) from alco_cost_center a, alco_company b, alco_division c, alco_department d where a.department_id = d.department_id and d.division_id = c.division_id and c.company_id = b.company_id;
  

• Country

  
  insert into alex_external_key (bus_obj_type_id, bob_id, new_external_key) select 21000,
  country_id, ltrim(rtrim(country_code_iso)) from alco_country;
  

• County

  
  insert into alex_external_key (bus_obj_type_id, bob_id, new_external_key) select 23400,
  a.county_id, ltrim(rtrim(c.country_code_iso)) || '*' || ltrim(rtrim(b.state_name)) || '*' ||
  ltrim(rtrim(a.county_name)) from alco_county a, alco_state b, alco_country c where a.state_id =
  b.state_id and b.country_id = c.country_id;
  

• Currency

  
  insert into alex_external_key (bus_obj_type_id, bob_id, new_external_key) select 35000,
  a.currency_fmt_defn_id, ltrim(rtrim(a.currency_code_iso)) from alco_currency_format a;
  

• Department

  
  insert into alex_external_key (bus_obj_type_id, bob_id, new_external_key) select 29000,
  a.department_id, ltrim(rtrim(b.company_code)) || '*' || rtrim(ltrim(c.division_code)) || '*' ||
  rtrim(ltrim(a.department_code)) from alco_department a, alco_company b, alco_division c 
  where a.division_id = c.division_id and c.company_id = b.company_id;

• Division

  
  insert into alex_external_key (bus_obj_type_id, bob_id, new_external_key) select 28500,
  a.division_id, ltrim(rtrim(b.company_code)) || '*' || rtrim(ltrim(a.division_code)) 
  from alco_division a, alco_company b where a.company_id = b.company_id;

• Employment Status

  
  insert into alex_external_key (bus_obj_type_id, bob_id, new_external_key) select 55700,
  emp_status_id, ltrim(rtrim(emp_status_name)) from alco_employment_status;
  

• Expense Type

  
  insert into alex_external_key (bus_obj_type_id, bob_id, new_external_key) select 800,
  expense_type_id, ltrim(rtrim(expense_type_label)) from alco_expense_type;
  

• Field Defaults

  
  insert into alex_external_key (bus_obj_type_id, bob_id, new_external_key) select 999072500,
  a.field_defaults_id, ltrim(rtrim(b.bob_name)) || '*' || ltrim(rtrim(a.bob_element))
  from alco_field_defaults a, almt_bob_type b where a.bob_type = b.bus_obj_type_id;
  

• Group Definition

  
  insert into alex_external_key (bus_obj_type_id, bob_id, new_external_key) select 147000,
  group_id, ltrim(rtrim(group_name)) from alco_group_defn;
  

• Holiday Schedule

  
  insert into alex_external_key (bus_obj_type_id, bob_id, new_external_key) select 55200,
  holiday_sched_name_id, ltrim(rtrim(holiday_sched_name)) from alco_holiday_sched_name;
  

• Holidays

  
  insert into alex_external_key (bus_obj_type_id, bob_id, new_external_key) select 55300,
  a.holiday_id, ltrim(rtrim(b.holiday_sched_name)) || '*' || to_char(a.holiday_date,
  'mm-dd-yyyy hh24:mi:ss') from alco_holidays a, alco_holiday_sched_name b where 
  a.holiday_sched_name_id = b.holiday_sched_name_id;
  

• Job Classification

  
  insert into alex_external_key (bus_obj_type_id, bob_id, new_external_key) select 56800,
  job_class_id, ltrim(rtrim(code)) from alco_job_classification;
  

• Labor Status

  
  insert into alex_external_key (bus_obj_type_id, bob_id, new_external_key) select 55600,
  labor_status_id, ltrim(rtrim(code)) from alco_labor_status;
  

• Location

  
  insert into alex_external_key (bus_obj_type_id, bob_id, new_external_key) select 19000,
  a.location_id, ltrim(rtrim(c.country_code_iso)) || '*' || ltrim(rtrim(b.state_name)) || '*' || 
  ltrim(rtrim(a.city_name)) || '*' || ltrim(rtrim(a.suburb_name)) || '*' || ltrim(rtrim(a.street_name))
  from alco_location a, alco_state b, alco_country c where a.state_id = b.state_id and a.country_id = 
  c.country_id and a.state_id is not null;
  
  insert into alex_external_key (bus_obj_type_id, bob_id, new_external_key) select 19000,
  a.location_id, ltrim(rtrim(b.country_code_iso)) || '*' || ltrim(rtrim(a.city_name)) || '*' || 
  ltrim(rtrim(a.suburb_name)) || '*' || ltrim(rtrim(a.street_name)) from alco_location a, 
  alco_country b where a.country_id = b.country_id and a.state_id is null;
  

• Mileage rate

  
  insert into alex_external_key (bus_obj_type_id, bob_id, new_external_key) select 26000,
  a.mileage_rate_id, ltrim(rtrim(b.expense_type_label)) || '*' || to_char(a.take_effect_date, 'mm-dd-yyyy hh24:mi:ss')
  from alco_mileage_rate a, alco_expense_type b where a.expense_type_id = b.expense_type_id;

• Notice Definition

  
  insert into alex_external_key (bus_obj_type_id, bob_id, new_external_key) select 999072000,
  notice_definition_id, ltrim(rtrim(notice_identifier)) from alco_notice_definition;
  

• Office

  
  insert into alex_external_key (bus_obj_type_id, bob_id, new_external_key) select 70000,
  office_id, ltrim(rtrim(office_code)) from alco_office;
  

• Payment Term

  
  insert into alex_external_key (bus_obj_type_id, bob_id, new_external_key) select 150010,
  payment_term_id, ltrim(rtrim(payment_term_code)) from alco_payment_term;
  

• Payment Type

  
  insert into alex_external_key (bus_obj_type_id, bob_id, new_external_key) select 700,
  payment_type_id, ltrim(rtrim(payment_type_label)) from alco_payment_type;
  

• Project Activity Type

  
  insert into alex_external_key (bus_obj_type_id, bob_id, new_external_key) select 999059800,
  project_activity_type_id, ltrim(rtrim(code)) from alco_project_activity_type;
  

• Project Code

  
  insert into alex_external_key (bus_obj_type_id, bob_id, new_external_key) select 80000,
  project_number_id, ltrim(rtrim(project_charge_code)) from alco_project_number;
  

• Purpose

  
  insert into alex_external_key (bus_obj_type_id, bob_id, new_external_key) select 17000,
  purpose_id, ltrim(rtrim(purpose_string)) from alco_purpose;
  

• Set Of Books

  
  insert into alex_external_key (bus_obj_type_id, bob_id, new_external_key) select 999057900,
  set_of_books_id, ltrim(rtrim(set_of_books_name)) from alco_set_of_books;
  

• Ship Method

  
  insert into alex_external_key (bus_obj_type_id, bob_id, new_external_key) select 135750,
  ship_method_id, ltrim(rtrim(ship_method)) from alco_ship_method;
  

• State

  
  insert into alex_external_key (bus_obj_type_id, bob_id, new_external_key) select 135700,
  a.state_id, ltrim(rtrim(b.country_code_iso)) || '*' || ltrim(rtrim(a.state_name)) 
  from alco_state a, alco_country b where a.country_id = b.country_id;
  

• Unit Of Measure

  
  insert into alex_external_key (bus_obj_type_id, bob_id, new_external_key) select 135500,
  unit_of_measure_id, ltrim(rtrim(uom_code)) from alco_unit_of_measure;
  

• User

  
  insert into alex_external_key (bus_obj_type_id, bob_id, new_external_key) select 30000,
  user_id, ltrim(rtrim(user_full_name)) from alco_user;
  

• Vendor

  
  insert into alex_external_key (bus_obj_type_id, bob_id, new_external_key) select 31000,
  vendor_id, ltrim(rtrim(vendor_name)) from alco_vendor;
  

• Deduction Type

  
  insert into alex_external_key (bus_obj_type_id, bob_id, new_external_key) select 999055800, 
  deduction_type_id, ltrim(rtrim(deduction_type_name)) from alpd_deduction_type;
  

• Duration

  
  insert into alex_external_key (bus_obj_type_id, bob_id, new_external_key) select 23000,
  duration_id, ltrim(rtrim(duration_label)) from alpd_duration;
  

• PR Category

  
  insert into alex_external_key (bus_obj_type_id, bob_id, new_external_key) select 135300, 
  pr_category_id, ltrim(rtrim(category_string)) from alpr_category;
  

• Manufacturer

  
  insert into alex_external_key (bus_obj_type_id, bob_id, new_external_key) select 135550, 
  manufacturer_id, ltrim(rtrim(manufacturer_name)) from alpr_manufacturer;
  

• PR Payment Type

  
  insert into alex_external_key (bus_obj_type_id, bob_id, new_external_key) select 150000, 
  pr_payment_type_id, ltrim(rtrim(pr_payment_type_label)) from alpr_payment_type;
  

• PR Purpose

  
  insert into alex_external_key (bus_obj_type_id, bob_id, new_external_key) select 135250,
  pr_purpose_id, ltrim(rtrim(purpose_string)) from alpr_purpose;
  

• Received Status

  
  insert into alex_external_key (bus_obj_type_id, bob_id, new_external_key) select 999059700, 
  received_status_id, ltrim(rtrim(status)) from alpr_received_status;
  

• Unspsc Code

  
  insert into alex_external_key (bus_obj_type_id, bob_id, new_external_key) select 999071900, 
  mapping_id, ltrim(rtrim(segment)) || '*' || ltrim(rtrim(family)) || '*' || ltrim(rtrim(class)) || '*' || 
  ltrim(rtrim(commodity)) from alpr_unspsccode;
  

• PR Vendor

  
  insert into alex_external_key (bus_obj_type_id, bob_id, new_external_key) select 135800, 
  vendor_type_id, ltrim(rtrim(vendor_type_name)) from alpr_vendor_type;
  

• Print Template

  
  insert into alex_external_key (bus_obj_type_id, bob_id, new_external_key) select 999071600, 
  template_id, ltrim(rtrim(template_name)) from alpt_template;
  

• Travel Category

  
  insert into alex_external_key (bus_obj_type_id, bob_id, new_external_key) select 999056700, 
  travel_category_id, ltrim(rtrim(travel_category_name)) from altr_travel_category;
  

• Travel Item

  
  insert into alex_external_key (bus_obj_type_id, bob_id, new_external_key) select 79000, 
  tr_item_type_id, ltrim(rtrim(tr_item_label)) from altr_travel_item;
  

• Attendance Overtime Calculation

  
  insert into alex_external_key (bus_obj_type_id, bob_id, new_external_key) select 46100, 
  attd_overtime_calc_id, ltrim(rtrim(calc_name)) from alts_attd_overtime_calc;
  

• Hourly Charge Type

  
  insert into alex_external_key (bus_obj_type_id, bob_id, new_external_key) select 56000, 
  hourly_charge_type_id, ltrim(rtrim(hourly_charge_type_name)) from alts_hourly_charge_types;
  

• Overtime Method

  
  insert into alex_external_key (bus_obj_type_id, bob_id, new_external_key) select 999072800, 
  overtime_method_id, ltrim(rtrim(code)) from alts_overtime_method;
  

• Report Period

  
  insert into alex_external_key (bus_obj_type_id, bob_id, new_external_key) select 55000, 
  report_period_id, ltrim(rtrim(report_period_name)) from alts_report_period;
  

• Timesheet Profile

  
  insert into alex_external_key (bus_obj_type_id, bob_id, new_external_key) select 56700, 
  ts_profile_id, ltrim(rtrim(code)) from alts_timesheet_profile;
  

• Work Schedule

  
  insert into alex_external_key (bus_obj_type_id, bob_id, new_external_key) select 55100, 
  work_sched_id, ltrim(rtrim(work_sched_name)) from alts_work_schedule;
  

• Application Parameter Override

  
  insert into alex_external_key (bus_obj_type_id, bob_id, new_external_key) select 88100, 
  a.parm_override_id, ltrim(rtrim(b.parm_name)) || '*' || ltrim(rtrim(a.parm_override_value)) 
  from alwf_appl_parm_override a, alwf_appl_parameters b where a.parm_id = b.parm_id;