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;