|
|
SQL Human Resources / Employees
API AHEM
AHEM - Employees Table
Bank Codes ODSC SQL Table
Business Place OBPL SQL Table
Branches OUBR SQL Table
Countries OCRY SQL Table
Cost Center OPRC SQL Table
Departments OUDP SQL Table
Employee ID Type OIDT SQL Table
Employees OHEM SQL Table
Employee Position OHPS SQL Table
Sales Employee OSLP SQL Table
Employee Status OHST SQL Table
Termination Reason OHTR SQL Table
Employee Types OHTY SQL Table
Users OUSR SQL Table
SBO SQL database table details
| Field | Description | Type | Constraints | Size | Related | Default | Primary_Key | Unique | empID KEY UNIQUE | Employee No. | Int | 11 | PRIMARY | Yes |
|---|---|---|---|---|---|---|---|---|
| lastName | Last Name | nVarChar | 50 | |||||
| firstName | First Name | nVarChar | 50 | |||||
| middleName | Middle Name | nVarChar | 50 | |||||
| sex | Gender | VarChar | F) Female M) Male | 1 | M | |||
| jobTitle | Job Title | nVarChar | 20 | |||||
| type | Employee Type | Int | 11 | OHTY Employee Types | ||||
| dept | Department | Int | 6 | OUDP Departments | ||||
| branch | Branch | Int | 6 | OUBR Branches | ||||
| workStreet | Work Street | nVarChar | 100 | |||||
| workBlock | Work Block | nVarChar | 100 | |||||
| workZip | Work Zip Code | nVarChar | 20 | |||||
| workCity | Work City | nVarChar | 100 | |||||
| workCounty | Work County | nVarChar | 100 | |||||
| workCountr | Work Country | nVarChar | 3 | OCRY Countries | ||||
| workState | Work State | nVarChar | 3 | |||||
| manager | Manager | Int | 11 | OHEM Employees | ||||
| userId | User ID | Int | 11 | OUSR Users | ||||
| salesPrson | Sales Employee | Int | 11 | OSLP Sales Employee | ||||
| officeTel | Office Phone | nVarChar | 20 | |||||
| officeExt | Office Ext. | nVarChar | 20 | |||||
| mobile | Mobile Phone | nVarChar | 20 | |||||
| pager | Pager | nVarChar | 20 | |||||
| homeTel | Home Phone | nVarChar | 20 | |||||
| fax | Fax | nVarChar | 20 | |||||
| nVarChar | 100 | |||||||
| startDate | Start Date | Date | 8 | |||||
| status | Status | Int | 11 | OHST Employee Status | ||||
| salary | Salary | Num | 20 | |||||
| salaryUnit | Salary Unit | VarChar | H) Hour D) Day W) Week M) Month Y) Year S) Semimonthly B) Biweekly | 1 | M | |||
| emplCost | Employee Costs | Num | 20 | |||||
| empCostUnt | Employee Cost Unit | VarChar | H) Hour D) Day W) Week M) Month Y) Year | 1 | M | |||
| termDate | Termination Date | Date | 8 | |||||
| termReason | Termination Reason | Int | 11 | OHTR Termination Reason | ||||
| bankCode | Bank Code | nVarChar | 30 | ODSC Bank Codes | ||||
| bankBranch | Bank Branch | nVarChar | 100 | |||||
| bankBranNo | Bank Branch No. | nVarChar | 30 | |||||
| bankAcount | Bank Account | nVarChar | 100 | |||||
| homeStreet | Home Street | nVarChar | 100 | |||||
| homeBlock | Home Block | nVarChar | 100 | |||||
| homeZip | Home Zip Code | nVarChar | 20 | |||||
| homeCity | Home City | nVarChar | 100 | |||||
| homeCounty | Home County | nVarChar | 100 | |||||
| homeCountr | Home Country | nVarChar | 3 | OCRY Countries | ||||
| homeState | Home State | nVarChar | 3 | |||||
| birthDate | Date of Birth | Date | 8 | |||||
| brthCountr | Country of Birth | nVarChar | 3 | OCRY Countries | ||||
| martStatus | Marital Status | VarChar | S) Single M) Married D) Divorced W) Widowed | 1 | S | |||
| nChildren | No. of Children | Int | 6 | |||||
| govID | ID Issued by Authorities | nVarChar | 64 | |||||
| citizenshp | Citizenship | nVarChar | 3 | OCRY Countries | ||||
| passportNo | Passport No. | nVarChar | 64 | |||||
| passportEx | Passport Expiration Date | Date | 8 | |||||
| picture | Picture | nVarChar | 127 | |||||
| remark | Remarks | Text | 16 | |||||
| attachment | Attachments | Text | 16 | |||||
| salaryCurr | Salary Currency | nVarChar | 3 | |||||
| empCostCur | Employee Costs Currency | nVarChar | 3 | |||||
| WorkBuild | Work Building/Floor/Room | Text | 16 | |||||
| HomeBuild | Home Building/Floor/Room | Text | 16 | |||||
| position | Position | Int | 11 | OHPS Employee Position | ||||
| AtcEntry | Attachment Entry | Int | 11 | |||||
| AddrTypeW | Work Address Type | nVarChar | 100 | |||||
| AddrTypeH | Home Address Type | nVarChar | 100 | |||||
| StreetNoW | Work Street No. | nVarChar | 100 | |||||
| StreetNoH | Home Street No. | nVarChar | 100 | |||||
| DispMidNam | Display Middle Name | VarChar | N) No Y) Yes | 1 | N | |||
| NamePos | Name Positioning Set | VarChar | 1) Last Name, First Name 2) First Name, Last Name | 1 | 1 | |||
| DispComma | Display Comma | VarChar | N) No Y) Yes | 1 | N | |||
| CostCenter | Cost Center | nVarChar | 8 | OPRC Cost Center | ||||
| CompanyNum | Company Number | nVarChar | 20 | |||||
| VacPreYear | Vacation: Previous Year | Int | 11 | |||||
| VacCurYear | Vacation: Current Year | Int | 11 | |||||
| MunKey | Municipality Key | nVarChar | 20 | |||||
| TaxClass | Tax Class | nVarChar | 0 1) Tax Class I 2) Tax Class II 3) Tax Class III 4) Tax Class IV 5) Tax Class V 6) Tax Class VI | 2 | 0 | |||
| InTaxLiabi | Income Tax Liability | nVarChar | 0 1) On Tax Card 2) Flat-Rate Tax 3) Cross-Border Employee 4) Not Liable | 2 | 0 | |||
| EmTaxCCode | Religion | nVarChar | 0 --) No Church Tax Liability AK) (AK) Old Catholic EV) (EV) Protestant FA) (FA) Non-Denomination Alzey FB) (FB) Non-Denominational Regional Congregation Baden FG) (FG) Non-Denominational Regional Congregation Palatinate FM) (FM) Non-Denominational Congregation Mainz FR) (FR) French-Reformed FS) (FS) Non-Denominational Congregation Offenbach/Mainz IB) (IB) Israelite Religious Community Baden IL) (IL) Israelite Rural IS) (IS) Israelite IW) (IW) Israelite Religious Community Wuerttemberg JD) (JD) Jewish Religion Tax JH) (JH) Jewish Religion Tax JS) (JS) Jewish Religion Tax LT) (LT) Lutheran RF) (RF) Reformed RK) (RK) Roman Catholic | 9 | 0 | |||
| RelPartner | Religion of Partner | nVarChar | 0 --) No Church Tax Liability AK) (AK) Old Catholic EV) (EV) Protestant FA) (FA) Non-Denomination Alzey FB) (FB) Non-Denominational Regional Congregation Baden FG) (FG) Non-Denominational Regional Congregation Palatinate FM) (FM) Non-Denominational Congregation Mainz FR) (FR) French-Reformed FS) (FS) Non-Denominational Congregation Offenbach/Mainz IB) (IB) Israelite Religious Community Baden IL) (IL) Israelite Rural IS) (IS) Israelite IW) (IW) Israelite Religious Community Wuerttemberg JD) (JD) Jewish Religion Tax JH) (JH) Jewish Religion Tax JS) (JS) Jewish Religion Tax LT) (LT) Lutheran RF) (RF) Reformed RK) (RK) Roman Catholic | 9 | 0 | |||
| ExemptAmnt | Exemption Amount | Num | 20 | |||||
| ExemptUnit | Exemption Amount Period | nVarChar | 0 1) Yearly 2) Monthly 3) Weekly 4) Daily | 20 | 0 | |||
| AddiAmnt | Additional Amount | Num | 20 | |||||
| AddiUnit | Additional Amount Period | nVarChar | 0 1) Yearly 2) Monthly 3) Weekly 4) Daily | 20 | 0 | |||
| TaxOName | Tax Office Name | nVarChar | 50 | |||||
| TaxONum | Tax Office Number | nVarChar | 20 | |||||
| HeaInsName | Health Insurance Company Name | nVarChar | 50 | |||||
| HeaInsCode | Health Insurance Code | nVarChar | 50 | |||||
| HeaInsType | Type of Health Insurance | nVarChar | ' ') AOK) (AOK) IKK) (IKK) EKK) (EKK) BKK) (BKK) BKS) (BKS) LKK) (LKK) | 20 | ' ' | |||
| SInsurNum | Social Insurance Number | nVarChar | 20 | |||||
| StatusOfP | Professional Status | nVarChar | -1) 0 1) Worker 2) Skilled Worker 3) Supervisor/Foreman 4) Office Worker 5) Youth Help/Sheltered Workshop 6) On Career Advancement Training 7) Homeworker 8) Part Time <18 hrs 9) Part Time > 18 Hrs | 2 | -1 | |||
| StatusOfE | Educational Status | nVarChar | 0 1) W/o Professional Qualification 2) W. Professional Qualification 3) High School w/o Prof. Qualif. 4) High School w. Prof. Qualific. 5) Vocational Qualification 6) University 7) Not Possible to Specify | 2 | 0 | |||
| BCodeDateV | Bank Code for DATEV | nVarChar | 20 | |||||
| DevBAOwner | Deviating Bank Account Owner | VarChar | Y) Yes N) No | 1 | N | |||
| FNameSP | First Name of Spouse | nVarChar | 50 | |||||
| SurnameSP | Last Name of Spouse | nVarChar | 50 | |||||
| LogInstanc | Log Instance | Int | 11 | 0 | ||||
| UserSign | User Signature | Int | 6 | OUSR Users | ||||
| UserSign2 | Updating User | Int | 6 | OUSR Users | ||||
| UpdateDate | Update Date | Date | 8 | |||||
| PersGroup | Person Group | nVarChar | -1) 101) Subject to Social Insurance 102) Apprentice 104) Home Worker 105) Trainee 106) Student 108) Early Retirement 109) Part-Time Employee 110) Short-Term Employee 112) Family Member: Agriculture 113) Addnl Income: Agriculture 114) Addnl Income: Seasonal Agriculture 116) Receiving Compensation Pay 118) Irregularly Employed 119) Pensioner 997) Not Specified | 5 | -1 | |||
| JTCode | Job Title Code | nVarChar | 5 | |||||
| ExtEmpNo | Ext. Employee No. | nVarChar | 20 | |||||
| BirthPlace | Place of Birth | nVarChar | 100 | |||||
| PymMeth | Payment Method | nVarChar | -1) 05) Bank Transfer | 2 | 05 | |||
| ExemptCurr | Exemption Amount Currency | nVarChar | 3 | |||||
| AddiCurr | Additional Amount Currency | nVarChar | 3 | |||||
| STDCode | STD Code | Int | 11 | |||||
| FatherName | Father's Name | nVarChar | 127 | |||||
| CPF | Personal Fiscal ID | nVarChar | 100 | |||||
| CRC | CRC Number | nVarChar | 20 | |||||
| ContResp | Accountant Responsible | VarChar | Y) Yes N) No | 1 | N | |||
| RepLegal | Legal Representative | VarChar | Y) Yes N) No | 1 | N | |||
| DirfDeclar | DIRF Responsible | VarChar | Y) Yes N) No | 1 | N | |||
| UF_CRC | CRC State | nVarChar | 3 | |||||
| IDType | ID Type | nVarChar | 30 | OIDT Employee ID Type | ||||
| Active | Employee Status | VarChar | Y) Active N) Inactive | 1 | Y | |||
| BPLId | Branch | Int | 11 | OBPL Business Place | ||||
| ManualNUM | Manual EMP No. | nVarChar | 60 | |||||
| PassIssue | Passport Issue Date | Date | 8 | |||||
| PassIssuer | Passport Issuer | nVarChar | 127 | |||||
| QualCode | Qualification Code | nVarChar | 000) n/a 203) Diretor 204) Conselheiro de Administração 205) Administrador 206) Administrador do Grupo 207) Administrador de Sociedade Filiada 220) Administrador Judicial - Pessoa Fisica 222) Administrador Judicial - Pessoa Juridica - profissional responsável 223) Administrador Judicial / Gestor 226) Gestor Judicial 309) Procurador 312) Inventariante 313) Liquidante 315) Interventor 801) Empresário 900) Contador 999) Outros | 3 | 000 | |||
| PRWebAccss | Enable Access to PR from Web | VarChar | Y) Yes N) No | 1 | N | |||
| PrePRWeb | Previous Stats of PR Web Access | VarChar | Y) Yes N) No | 1 | N |
Table relationship map
SELECT statement
SELECT
AHEM.empID AS 'Employee No.',
AHEM.lastName AS 'Last Name',
AHEM.firstName AS 'First Name',
AHEM.middleName AS 'Middle Name',
AHEM.sex AS 'Gender',
AHEM.jobTitle AS 'Job Title',
AHEM.type AS 'Employee Type',
AHEM.dept AS 'Department',
AHEM.branch AS 'Branch',
AHEM.workStreet AS 'Work Street',
AHEM.workBlock AS 'Work Block',
AHEM.workZip AS 'Work Zip Code',
AHEM.workCity AS 'Work City',
AHEM.workCounty AS 'Work County',
AHEM.workCountr AS 'Work Country',
AHEM.workState AS 'Work State',
AHEM.manager AS 'Manager',
AHEM.userId AS 'User ID',
AHEM.salesPrson AS 'Sales Employee',
AHEM.officeTel AS 'Office Phone',
AHEM.officeExt AS 'Office Ext.',
AHEM.mobile AS 'Mobile Phone',
AHEM.pager AS 'Pager',
AHEM.homeTel AS 'Home Phone',
AHEM.fax AS 'Fax',
AHEM.email AS 'E-Mail',
AHEM.startDate AS 'Start Date',
AHEM.status AS 'Status',
AHEM.salary AS 'Salary',
AHEM.salaryUnit AS 'Salary Unit',
AHEM.emplCost AS 'Employee Costs',
AHEM.empCostUnt AS 'Employee Cost Unit',
AHEM.termDate AS 'Termination Date',
AHEM.termReason AS 'Termination Reason',
AHEM.bankCode AS 'Bank Code',
AHEM.bankBranch AS 'Bank Branch',
AHEM.bankBranNo AS 'Bank Branch No.',
AHEM.bankAcount AS 'Bank Account',
AHEM.homeStreet AS 'Home Street',
AHEM.homeBlock AS 'Home Block',
AHEM.homeZip AS 'Home Zip Code',
AHEM.homeCity AS 'Home City',
AHEM.homeCounty AS 'Home County',
AHEM.homeCountr AS 'Home Country',
AHEM.homeState AS 'Home State',
AHEM.birthDate AS 'Date of Birth',
AHEM.brthCountr AS 'Country of Birth',
AHEM.martStatus AS 'Marital Status',
AHEM.nChildren AS 'No. of Children',
AHEM.govID AS 'ID Issued by Authorities',
AHEM.citizenshp AS 'Citizenship',
AHEM.passportNo AS 'Passport No.',
AHEM.passportEx AS 'Passport Expiration Date',
AHEM.picture AS 'Picture',
AHEM.remark AS 'Remarks',
AHEM.attachment AS 'Attachments',
AHEM.salaryCurr AS 'Salary Currency',
AHEM.empCostCur AS 'Employee Costs Currency',
AHEM.WorkBuild AS 'Work Building/Floor/Room',
AHEM.HomeBuild AS 'Home Building/Floor/Room',
AHEM.position AS 'Position',
AHEM.AtcEntry AS 'Attachment Entry',
AHEM.AddrTypeW AS 'Work Address Type',
AHEM.AddrTypeH AS 'Home Address Type',
AHEM.StreetNoW AS 'Work Street No.',
AHEM.StreetNoH AS 'Home Street No.',
AHEM.DispMidNam AS 'Display Middle Name',
AHEM.NamePos AS 'Name Positioning Set',
AHEM.DispComma AS 'Display Comma',
AHEM.CostCenter AS 'Cost Center',
AHEM.CompanyNum AS 'Company Number',
AHEM.VacPreYear AS 'Vacation: Previous Year',
AHEM.VacCurYear AS 'Vacation: Current Year',
AHEM.MunKey AS 'Municipality Key',
AHEM.TaxClass AS 'Tax Class',
AHEM.InTaxLiabi AS 'Income Tax Liability',
AHEM.EmTaxCCode AS 'Religion',
AHEM.RelPartner AS 'Religion of Partner',
AHEM.ExemptAmnt AS 'Exemption Amount',
AHEM.ExemptUnit AS 'Exemption Amount Period',
AHEM.AddiAmnt AS 'Additional Amount',
AHEM.AddiUnit AS 'Additional Amount Period',
AHEM.TaxOName AS 'Tax Office Name',
AHEM.TaxONum AS 'Tax Office Number',
AHEM.HeaInsName AS 'Health Insurance Company Name',
AHEM.HeaInsCode AS 'Health Insurance Code',
AHEM.HeaInsType AS 'Type of Health Insurance',
AHEM.SInsurNum AS 'Social Insurance Number',
AHEM.StatusOfP AS 'Professional Status',
AHEM.StatusOfE AS 'Educational Status',
AHEM.BCodeDateV AS 'Bank Code for DATEV',
AHEM.DevBAOwner AS 'Deviating Bank Account Owner',
AHEM.FNameSP AS 'First Name of Spouse',
AHEM.SurnameSP AS 'Last Name of Spouse',
AHEM.LogInstanc AS 'Log Instance',
AHEM.UserSign AS 'User Signature',
AHEM.UserSign2 AS 'Updating User',
AHEM.UpdateDate AS 'Update Date',
AHEM.PersGroup AS 'Person Group',
AHEM.JTCode AS 'Job Title Code',
AHEM.ExtEmpNo AS 'Ext. Employee No.',
AHEM.BirthPlace AS 'Place of Birth',
AHEM.PymMeth AS 'Payment Method',
AHEM.ExemptCurr AS 'Exemption Amount Currency',
AHEM.AddiCurr AS 'Additional Amount Currency',
AHEM.STDCode AS 'STD Code',
AHEM.FatherName AS 'Father's Name',
AHEM.CPF AS 'Personal Fiscal ID',
AHEM.CRC AS 'CRC Number',
AHEM.ContResp AS 'Accountant Responsible',
AHEM.RepLegal AS 'Legal Representative',
AHEM.DirfDeclar AS 'DIRF Responsible',
AHEM.UF_CRC AS 'CRC State',
AHEM.IDType AS 'ID Type',
AHEM.Active AS 'Employee Status',
AHEM.BPLId AS 'Branch',
AHEM.ManualNUM AS 'Manual EMP No.',
AHEM.PassIssue AS 'Passport Issue Date',
AHEM.PassIssuer AS 'Passport Issuer',
AHEM.QualCode AS 'Qualification Code',
AHEM.PRWebAccss AS 'Enable Access to PR from Web',
AHEM.PrePRWeb AS 'Previous Stats of PR Web Access'
FROM AHEM AS AHEM
Related information
Bank Codes ODSC SQL Table
Business Place OBPL SQL Table
Branches OUBR SQL Table
Countries OCRY SQL Table
Cost Center OPRC SQL Table
Departments OUDP SQL Table
Employee ID Type OIDT SQL Table
Employees OHEM SQL Table
Employee Position OHPS SQL Table
Sales Employee OSLP SQL Table
Employee Status OHST SQL Table
Termination Reason OHTR SQL Table
Employee Types OHTY SQL Table
Users OUSR SQL Table
This website is not affiliated with, maintained, authorized, endorsed or sponsored by SAP AG or any of its affiliates
