/******************************************************************************** * * Program Name: lawson_hr_725_803.sql * * Program Description: SQL Script to extract HR information for upload * * Author: John Eisenschmidt * * Date: 2003-12-24 - Created * 2003-12-26 - Fixed problem in WHERE clauses * ********************************************************************************/ /* HR511 - Employee PA512 - Emergency Contact HR515 - User Field PR512 - Employee Direct Deposit PR514 - Employee Deduction Codes HR507 - Supervisor Information TA570 Time Accrual Balances TR500 - Training History */ /* EMPLOYEE INFORMATION (HR511) */ SELECT EMPLOYEE.COMPANY, EMPLOYEE.EMPLOYEE, EMPLOYEE.LAST_NAME, EMPLOYEE.FIRST_NAME, EMPLOYEE.MIDDLE_INIT, EMPLOYEE.MIDDLE_NAME, EMPLOYEE.NICK_NAME, EMPLOYEE.ADDR1, EMPLOYEE.ADDR2, EMPLOYEE.ADDR3, EMPLOYEE.ADDR4, EMPLOYEE.CITY, EMPLOYEE.STATE, EMPLOYEE.ZIP, EMPLOYEE.COUNTRY, EMPLOYEE.EMP_STATUS, EMPLOYEE.FICA_NBR, EMPLOYEE.PROCESS_LEVEL, EMPLOYEE.DEPARTMENT, EMPLOYEE.HM_DIST_CO, EMPLOYEE.HM_ACCT_UNIT, EMPLOYEE.HM_ACCOUNT, EMPLOYEE.HM_SUB_ACCT, EMPLOYEE.SUPERVISOR, EMPLOYEE.ACTIVITY, EMPLOYEE.UNION_CODE, EMPLOYEE.USER_LEVEL, EMPLOYEE.DATE_HIRED, EMPLOYEE.ADJ_HIRE_DATE, EMPLOYEE.TERM_DATE, EMPLOYEE.JOB_CODE, EMPLOYEE.NBR_FTE, EMPLOYEE.SALARY_CLASS, EMPLOYEE.PAY_RATE, EMPLOYEE.PAY_FREQUENCY, EMPLOYEE.STAND_HOURS, EMPLOYEE.STAND_AMT, EMPLOYEE.WARN_FLAG, EMPLOYEE.ADD_ALLOW_PER, EMPLOYEE.ADD_ALLOW_HRS, EMPLOYEE.ADD_ALLOW_AMT, EMPLOYEE.AUTO_TIME_REC, EMPLOYEE.AUTO_DEPOSIT, EMPLOYEE.CREATION_DATE, EMPLOYEE.PAY_STEP, EMPLOYEE.PAY_GRADE, EMPLOYEE.SCHEDULE, EMPLOYEE.EXEMPT_EMP, EMPLOYEE.SHIFT, EMPLOYEE.EIC_STATUS, EMPLOYEE.ANNIVERS_DATE, EMPLOYEE.PENSION_PLAN, EMPLOYEE.RPT_INS_COST, EMPLOYEE.EBE_AMOUNT, EMPLOYEE.OT_PLAN_CODE, EMPLOYEE.DECEASED, EMPLOYEE.BSI_GROUP, EMPLOYEE.WC_STATE, EMPLOYEE.MOVING_EXP, EMPLOYEE.SICK_PAY, PAEMPLOYEE.SUPP_ADDR1, PAEMPLOYEE.SUPP_ADDR2, PAEMPLOYEE.SUPP_ADDR3, PAEMPLOYEE.SUPP_ADDR4, PAEMPLOYEE.SUPP_CITY, PAEMPLOYEE.SUPP_STATE, PAEMPLOYEE.SUPP_ZIP, PAEMPLOYEE.SUPP_COUNTRY, PAEMPLOYEE.SUPP_CNTRY_CD, PAEMPLOYEE.HM_PHONE_CNTRY, PAEMPLOYEE.HM_PHONE_NBR, PAEMPLOYEE.WK_PHONE_CNTRY, PAEMPLOYEE.WK_PHONE_NBR, PAEMPLOYEE.WK_PHONE_EXT, PAEMPLOYEE.EEO_CLASS, PAEMPLOYEE.SEX, PAEMPLOYEE.HANDICAP_ID, PAEMPLOYEE.COMP_CODE, PAEMPLOYEE.COMP_NBR, PAEMPLOYEE.VETERAN, PAEMPLOYEE.BIRTHDATE, PAEMPLOYEE.BIRTH_CITY, PAEMPLOYEE.BIRTH_STATE, PAEMPLOYEE.BIRTH_CNTRY_CD, PAEMPLOYEE.SENIOR_DATE, PAEMPLOYEE.MAIDEN_LST_NM, PAEMPLOYEE.MAIDEN_FST_NM, PAEMPLOYEE.MAIDEN_MI, PAEMPLOYEE.FORMER_LST_NM, PAEMPLOYEE.FORMER_FST_NM, PAEMPLOYEE.FORMER_MI, PAEMPLOYEE.LOCAT_CODE, PAEMPLOYEE.HIRE_SOURCE, PAEMPLOYEE.SECURITY_CODE, PAEMPLOYEE.SECURITY_NBR, PAEMPLOYEE.BARGAIN_UNIT, PAEMPLOYEE.CLOCK_NBR, PAEMPLOYEE.MAIL_GROUP, PAEMPLOYEE.MB_NBR, PAEMPLOYEE.DRAFT_STATUS, PAEMPLOYEE.FINAL_RANK, PAEMPLOYEE.CUR_STATUS, PAEMPLOYEE.REV_SCHEDULE, PAEMPLOYEE.NEXT_REV_CODE, PAEMPLOYEE.FNCTN_GROUP, PAEMPLOYEE.EXCLUDE_FLAG, PAEMPLOYEE.NBR_HL_DEP, PAEMPLOYEE.NBR_DN_DEP, PAEMPLOYEE.HL_COV_PROOF, PAEMPLOYEE.DN_COV_PROOF, PAEMPLOYEE.HL_VERIFY_DT, PAEMPLOYEE.DN_VERIFY_DT, PAEMPLOYEE.SPOUSE_EMP, PAEMPLOYEE.SP_EMP_ADDR1, PAEMPLOYEE.SP_EMP_ADDR2, PAEMPLOYEE.SP_EMP_ADDR3, PAEMPLOYEE.SP_EMP_ADDR4, PAEMPLOYEE.SP_EMP_CITY, PAEMPLOYEE.SP_EMP_STATE, PAEMPLOYEE.SP_EMP_ZIP, PAEMPLOYEE.SP_EMP_COUNTRY, PAEMPLOYEE.SP_EMP_PH_CNTR, PAEMPLOYEE.SP_EMP_PH_NBR, PAEMPLOYEE.OWNER_FLAG, PAEMPLOYEE.KEY_EMP_FLAG, PAEMPLOYEE.OFFICER, PAEMPLOYEE.HIGH_COMP, PAEMPLOYEE.SMOKER, PAEMPLOYEE.I9_STATUS, PAEMPLOYEE.I9_ALIEN_NBR, PAEMPLOYEE.I9_ADMIT_NBR, PAEMPLOYEE.I9_STA_EXP_DT, PAEMPLOYEE.I9_DOC_NBR_01, PAEMPLOYEE.I9_DOC_DESC_01, PAEMPLOYEE.I9_DOC_TYPE_01, PAEMPLOYEE.I9_DOC_EXP_DT_01, PAEMPLOYEE.I9_DOC_NBR_02, PAEMPLOYEE.I9_DOC_DESC_02, PAEMPLOYEE.I9_DOC_TYPE_02, PAEMPLOYEE.I9_DOC_EXP_DT_02, PAEMPLOYEE.I9_AUTHORIZE, PAEMPLOYEE.TRUE_MAR_STAT, PAEMPLOYEE.PRIMARY_CARE, PAEMPLOYEE.FAMILY_AGG, PAEMPPOS.R_POSITION, EMPLOYEE.TIPPED, PAEMPLOYEE.WORK_ZIP, EMPLOYEE.ACCT_CATEGORY, EMPLOYEE.SEC_LVL, EMPLOYEE.SEC_LOCATION, PAEMPLOYEE.BEN_SALARY_1, PAEMPLOYEE.BEN_SALARY_2, PAEMPLOYEE.BEN_SALARY_3, PAEMPLOYEE.BEN_SALARY_4, PAEMPLOYEE.BEN_SALARY_5, PAEMPLOYEE.BEN_DATE_1, PAEMPLOYEE.BEN_DATE_2, PAEMPLOYEE.BEN_DATE_3, PAEMPLOYEE.BEN_DATE_4, PAEMPLOYEE.BEN_DATE_5, EMPLOYEE.SUPERVISOR_IND, EMPLOYEE.WORK_SCHED, EMPLOYEE.ANNUAL_HOURS, EMPLOYEE.NEW_HIRE_DATE, PAEMPLOYEE.LANGUAGE_CODE, PAEMPLOYEE.RELIGION, PAEMPLOYEE.DISABILITY, PAEMPLOYEE.SUPP_PHONE_CNT, PAEMPLOYEE.SUPP_PHONE_NBR, EMPLOYEE.CURRENCY_CODE FROM dbo.EMPLOYEE LEFT OUTER /* INNER */ JOIN dbo.PAEMPLOYEE ON dbo.PAEMPLOYEE.EMPLOYEE = dbo.EMPLOYEE.EMPLOYEE LEFT OUTER /* INNER */ JOIN dbo.PAEMPPOS ON dbo.PAEMPPOS.EMPLOYEE = dbo.EMPLOYEE.EMPLOYEE WHERE EMPLOYEE.EMP_STATUS='AP' OR --active, part time EMPLOYEE.EMP_STATUS='AF' OR --active, full time EMPLOYEE.EMP_STATUS='BN' OR --benefits only EMPLOYEE.EMP_STATUS='CO' OR --contractor EMPLOYEE.EMP_STATUS='DI' OR --disability EMPLOYEE.EMP_STATUS='FL' OR --FMLA EMPLOYEE.EMP_STATUS='FM' OR --FMLA, why are there two? EMPLOYEE.EMP_STATUS='GA' OR --garnishment EMPLOYEE.EMP_STATUS='LA' OR --leave of absence EMPLOYEE.EMP_STATUS='LO' OR --leave of absence, two of these too? EMPLOYEE.EMP_STATUS='NN' OR --needed new emp# due to garnishment EMPLOYEE.EMP_STATUS='RT' OR --retirement EMPLOYEE.EMP_STATUS='TE' OR --temp over 30hrs EMPLOYEE.EMP_STATUS='TM' OR --temp under 30 hrs EMPLOYEE.EMP_STATUS='TP' OR --terminated, pay pending EMPLOYEE.EMP_STATUS='TR' OR --transferred process levels EMPLOYEE.EMP_STATUS='WC' OR --workers comp ((EMPLOYEE.TERM_DATE > {ts '2003-10-01 00:00:00'}) AND (EMPLOYEE.EMP_STATUS='T')); ------------------------------------------------------------------------------------------- -- (EMPLOYEE.EMP_STATUS='AP') /* AND (EMPLOYEE.COMPANY=700) */ OR --active, part time -- (EMPLOYEE.EMP_STATUS='AF') /* AND (EMPLOYEE.COMPANY=700) */ OR --active, full time -- (EMPLOYEE.EMP_STATUS='BN') /* AND (EMPLOYEE.COMPANY=700) */ OR --benefits only -- ( -- (EMPLOYEE.TERM_DATE > {ts '2003-10-01 00:00:00'}) AND -- ( -- (EMPLOYEE.EMP_STATUS='TP') /* AND (EMPLOYEE.COMPANY=700) */ OR -- (EMPLOYEE.EMP_STATUS='TM') /* AND (EMPLOYEE.COMPANY=700) */ OR -- (EMPLOYEE.EMP_STATUS='TE') /* AND (EMPLOYEE.COMPANY=700) */ -- ) -- ) ------------------------------------------------------------------------------------------- /* EMERGENCY CONTACT INFORMATION (PA512) */ SELECT 'A' AS 'FC', PAEMERGCNT.COMPANY, PAEMERGCNT.EMPLOYEE, PAEMERGCNT.SEQ_NBR, PAEMERGCNT.LAST_NAME, PAEMERGCNT.FIRST_NAME, PAEMERGCNT.MIDDLE_INIT, PAEMERGCNT.ADDR1, PAEMERGCNT.ADDR2, PAEMERGCNT.ADDR3, PAEMERGCNT.ADDR4, PAEMERGCNT.CITY, PAEMERGCNT.STATE, PAEMERGCNT.ZIP, PAEMERGCNT.RELATIONSHIP, PAEMERGCNT.HM_PHONE_CNTRY, PAEMERGCNT.HM_PHONE_NBR, PAEMERGCNT.WK_PHONE_CNTRY, PAEMERGCNT.WK_PHONE_NBR, PAEMERGCNT.WK_PHONE_EXT, PAEMERGCNT.COUNTRY_CODE '' AS 'DATE_STAMP', '' AS 'TIME_STAMP', '' AS 'USER_ID' FROM dbo.PAEMERGCNT INNER JOIN dbo.EMPLOYEE ON dbo.PAEMERGCNT.EMPLOYEE = dbo.EMPLOYEE.EMPLOYEE WHERE EMPLOYEE.EMP_STATUS='AP' OR --active, part time EMPLOYEE.EMP_STATUS='AF' OR --active, full time EMPLOYEE.EMP_STATUS='BN' OR --benefits only EMPLOYEE.EMP_STATUS='CO' OR --contractor EMPLOYEE.EMP_STATUS='DI' OR --disability EMPLOYEE.EMP_STATUS='FL' OR --FMLA EMPLOYEE.EMP_STATUS='FM' OR --FMLA, why are there two? EMPLOYEE.EMP_STATUS='GA' OR --garnishment EMPLOYEE.EMP_STATUS='LA' OR --leave of absence EMPLOYEE.EMP_STATUS='LO' OR --leave of absence, two of these too? EMPLOYEE.EMP_STATUS='NN' OR --needed new emp# due to garnishment EMPLOYEE.EMP_STATUS='RT' OR --retirement EMPLOYEE.EMP_STATUS='TE' OR --temp over 30hrs EMPLOYEE.EMP_STATUS='TM' OR --temp under 30 hrs EMPLOYEE.EMP_STATUS='TP' OR --terminated, pay pending EMPLOYEE.EMP_STATUS='TR' OR --transferred process levels EMPLOYEE.EMP_STATUS='WC' OR --workers comp ((EMPLOYEE.TERM_DATE > {ts '2003-10-01 00:00:00'}) AND (EMPLOYEE.EMP_STATUS='T')); /* USER FIELD (HR515) */ SELECT 'A' AS 'FC', HREMPUSF.COMPANY, --THERE IS A DISCREPENCY BETWEEN THE DATABASE AND THE INTERFACE FILE --THE DEVELOPER WHO DID THIS *MUST* BE PUNISHED CASE EMP_APP WHEN '0' THEN '1' --EMPLOYEE WHEN '1' THEN '2' --APPLICANT ELSE '?' --BOSTON FERN END AS 'REC_TYPE', HREMPUSF.EMPLOYEE, '' AS 'APPLICANT', '' AS 'POSITION', FIELD_NAME, A_FIELD, N_FIELD, D_FIELD, '' AS 'EFFECT_DATE', '' AS 'END_DATE', '' AS 'DATE_STAMP', '' AS 'TIME_STAMP', '' AS 'USER_ID', '' AS 'CURRENCY_CODE', '' AS 'BASE_CURRENCY', '' AS 'BASE_AMOUNT' FROM dbo.HREMPUSF INNER JOIN dbo.EMPLOYEE ON dbo.HREMPUSF.EMPLOYEE = dbo.EMPLOYEE.EMPLOYEE INNER JOIN dbo.HRUSERFLDS ON dbo.HREMPUSF.FIELD_KEY = dbo.HRUSERFLDS.FIELD_KEY WHERE EMPLOYEE.EMP_STATUS='AP' OR --active, part time EMPLOYEE.EMP_STATUS='AF' OR --active, full time EMPLOYEE.EMP_STATUS='BN' OR --benefits only EMPLOYEE.EMP_STATUS='CO' OR --contractor EMPLOYEE.EMP_STATUS='DI' OR --disability EMPLOYEE.EMP_STATUS='FL' OR --FMLA EMPLOYEE.EMP_STATUS='FM' OR --FMLA, why are there two? EMPLOYEE.EMP_STATUS='GA' OR --garnishment EMPLOYEE.EMP_STATUS='LA' OR --leave of absence EMPLOYEE.EMP_STATUS='LO' OR --leave of absence, two of these too? EMPLOYEE.EMP_STATUS='NN' OR --needed new emp# due to garnishment EMPLOYEE.EMP_STATUS='RT' OR --retirement EMPLOYEE.EMP_STATUS='TE' OR --temp over 30hrs EMPLOYEE.EMP_STATUS='TM' OR --temp under 30 hrs EMPLOYEE.EMP_STATUS='TP' OR --terminated, pay pending EMPLOYEE.EMP_STATUS='TR' OR --transferred process levels EMPLOYEE.EMP_STATUS='WC' OR --workers comp ((EMPLOYEE.TERM_DATE > {ts '2003-10-01 00:00:00'}) AND (EMPLOYEE.EMP_STATUS='T')); /* EMPLOYEE DIRECT DEPOSIT (ACH) DISTRIBUTION (PR512) */ SELECT 'A' AS 'FC', EMACHDEPST.COMPANY, EMACHDEPST.EMPLOYEE, EMACHDEPST.ACH_DIST_NBR, EMACHDEPST.OCCURANCES, EMACHDEPST.REMAIN_OCCUR, EMACHDEPST.EBANK_ID, EMACHDEPST.EBNK_ACCT_NBR, EMACHDEPST.ACCOUNT_TYPE, '' AS 'BANK_ROLL_NO', EMACHDEPST.ACH_PRENOTIFY, EMACHDEPST.NET_PERCENT, EMACHDEPST.DEPOSIT_AMT, EMACHDEPST.DEFAULT_FLAG, EMACHDEPST.DESCRIPTION, EMACHDEPST.CHECK_DESC, EMACHDEPST.BEG_DATE, EMACHDEPST.END_DATE, EMACHDEPST.DED_CYCLE_01, EMACHDEPST.DED_CYCLE_02, EMACHDEPST.DED_CYCLE_03, EMACHDEPST.DED_CYCLE_04, EMACHDEPST.DED_CYCLE_05, EMACHDEPST.DED_CYCLE_06, EMACHDEPST.DED_CYCLE_07, EMACHDEPST.DED_CYCLE_08, EMACHDEPST.DED_CYCLE_09, -- more Canadian goodness '' AS 'CA-INST-NBR', '' AS 'CA-TRANSIT-NBR', '' AS 'PAYABLE_TO' FROM dbo.EMACHDEPST INNER JOIN dbo.EMPLOYEE ON dbo.EMACHDEPST.EMPLOYEE = dbo.EMPLOYEE.EMPLOYEE WHERE EMPLOYEE.EMP_STATUS='AP' OR --active, part time EMPLOYEE.EMP_STATUS='AF' OR --active, full time EMPLOYEE.EMP_STATUS='BN' OR --benefits only EMPLOYEE.EMP_STATUS='CO' OR --contractor EMPLOYEE.EMP_STATUS='DI' OR --disability EMPLOYEE.EMP_STATUS='FL' OR --FMLA EMPLOYEE.EMP_STATUS='FM' OR --FMLA, why are there two? EMPLOYEE.EMP_STATUS='GA' OR --garnishment EMPLOYEE.EMP_STATUS='LA' OR --leave of absence EMPLOYEE.EMP_STATUS='LO' OR --leave of absence, two of these too? EMPLOYEE.EMP_STATUS='NN' OR --needed new emp# due to garnishment EMPLOYEE.EMP_STATUS='RT' OR --retirement EMPLOYEE.EMP_STATUS='TE' OR --temp over 30hrs EMPLOYEE.EMP_STATUS='TM' OR --temp under 30 hrs EMPLOYEE.EMP_STATUS='TP' OR --terminated, pay pending EMPLOYEE.EMP_STATUS='TR' OR --transferred process levels EMPLOYEE.EMP_STATUS='WC' OR --workers comp ((EMPLOYEE.TERM_DATE > {ts '2003-10-01 00:00:00'}) AND (EMPLOYEE.EMP_STATUS='T')); /* EMPLOYEE DEDUCTION MASTER (PR514) */ SELECT 'A' AS 'FC', EMDEDMASTR.COMPANY, EMDEDMASTR.EMPLOYEE, '' AS 'PEG_CASE_NUMBER', '' AS 'PEG_FILE_NUMBER', '' AS 'PEG_DESC', EMDEDMASTR.DED_CODE, EMDEDMASTR.SEQ_NBR, '' AS 'PEG_ARR_DED_CODE', '' AS 'PEG_ARR_SEQ_NBR', '' AS 'PEG_FEES_DED_CODE', '' AS 'PEG_FEES_SEQ_NBR', '' AS 'PEG_GARN_OBJ_ID', EMDEDMASTR.NEXT_AMOUNT, EMDEDMASTR.AMOUNT_2, EMDEDMASTR.BALANCE_TYPE, EMDEDMASTR.BALANCE_AMT, EMDEDMASTR.DED_PRIORITY, EMDEDMASTR.EFFECT_DATE, EMDEDMASTR.END_DATE, EMDEDMASTR.ARREARS, EMDEDMASTR.PCT_MATCHED, EMDEDMASTR.MONTHLY_LIMIT, EMDEDMASTR.PAY_PRD_LIMIT, EMDEDMASTR.RES_CODE, EMDEDMASTR.MARITAL_STATUS, EMDEDMASTR.EXEMPTIONS, EMDEDMASTR.EXEMPT_AMOUNT, EMDEDMASTR.CERT_CODE, EMDEDMASTR.PERS_EXEMPTS, EMDEDMASTR.DEPEND_EXEMPTS, EMDEDMASTR.ADDL_EXEMPTS, EMDEDMASTR.ADDL_EXEMP_AMT, EMDEDMASTR.ADDL_TAX_CODE, EMDEDMASTR.ADDL_RATE, EMDEDMASTR.ADDL_AMOUNT, EMDEDMASTR.ADDL_STD_DED, EMDEDMASTR.EXP_DIST_CO, EMDEDMASTR.EXP_ACCT_UNIT, EMDEDMASTR.EXP_ACCOUNT, EMDEDMASTR.EXP_SUB_ACCT, EMDEDMASTR.ACR_DIST_CO, EMDEDMASTR.ACR_ACCT_UNIT, EMDEDMASTR.ACR_ACCOUNT, EMDEDMASTR.ACR_SUB_ACCT, EMDEDMASTR.DED_CYCLE_01, EMDEDMASTR.DED_CYCLE_02, EMDEDMASTR.DED_CYCLE_03, EMDEDMASTR.DED_CYCLE_04, EMDEDMASTR.DED_CYCLE_05, EMDEDMASTR.DED_CYCLE_06, EMDEDMASTR.DED_CYCLE_07, EMDEDMASTR.DED_CYCLE_08, EMDEDMASTR.DED_CYCLE_09, EMDEDMASTR.DED_ORDER, EMDEDMASTR.FORMULA_NUMBER, EMDEDMASTR.FILING_STATUS, EMDEDMASTR.LEVY_EXEMPTS, EMDEDMASTR.LEVY_YEAR, EMDEDMASTR.SUB_PRIORITY, --PEG holds garnishment information, not an issue here '' AS 'PEG_TAX_ID_CODE', '' AS 'PEG_TYPE', '' AS 'PEG_VENDOR', '' AS 'PEG_ORIG_BALANCE', '' AS 'PEG_NUM_OF_DEP', '' AS 'PEG_HEAD_OF_FAM', '' AS 'PEG_ARR_ORIG_BAL', '' AS 'PEG_OVER12_WEEKS', '' AS 'PEG_ARR_ADD_PCT', '' AS 'PEG_ARR_NEXT_AMT', '' AS 'PEG_ARR_AMOUNT2', '' AS 'PEG_APP_BAL_AMT', '' AS 'PEG_ARR_BAL_TYPE', '' AS 'PEG_FEES_OVERRIDE', '' AS 'PEG_FEES_NEXT_AMT', '' AS 'PEG_EXEM_FORM', '' AS 'PEG_RATE_X_FED_MIN', '' AS 'PEG_FACTOR1', '' AS 'PEG_FACTOR2', '' AS 'PEG_FACTOR3', '' AS 'PEG_FACTOR4', '' AS 'PEG_SND_FAMILY', '' AS 'PEG_SND_FAM_YES', '' AS 'PEG_SND_FAM_NO', '' AS 'PEG_PCT_PER_DEP', '' AS 'PEG_CALC_TYPE', '' AS 'PEG_EXCL_PAY_CODE', '' AS 'PEG_EXCEL_DED_CODE', --the rest of these columns only seem to affect Canada '' AS 'EDM_NET_CLAIM_AMT', '' AS 'EDM_PRESCRIBEDAREA', '' AS 'EDM_STUDENT_PEN_EX', '' AS 'EDM_TD1_FILING_DAT', '' AS 'EDM_TAX_EXEMPT_FLG', '' AS 'EDM_AUTH_TAX_CRED', '' AS 'EDM_AUTH_TAX_DED', '' AS 'EDM_MARRIED_EQUIV', '' AS 'EDM_AGE_65_OVER', '' AS 'EDM_EMP_DISABILITY', '' AS 'EDM_DEP_UNDER_18', '' AS 'EDM_DEP_OVR_17', '' AS 'EDM_APPROVED_STOCK', '' AS 'RGP_APPROVED_STOCK_PR', '' AS 'EDM_TAX_CODE', '' AS 'EDM_TAX_BASIS', '' AS 'EDM_NI_CODE', '' AS 'EDM_DIRECTOR', '' AS 'EDM_CONTRACTED_OUT', '' AS 'PEG_DED_ARRS_TD', '' AS 'PEG_EXEMPT_ARRS_TD', '' AS 'PEG_REMIT_TO_CODE' FROM dbo.EMDEDMASTR INNER JOIN dbo.EMPLOYEE ON dbo.EMDEDMASTR.EMPLOYEE = dbo.EMPLOYEE.EMPLOYEE WHERE EMPLOYEE.EMP_STATUS='AP' OR --active, part time EMPLOYEE.EMP_STATUS='AF' OR --active, full time EMPLOYEE.EMP_STATUS='BN' OR --benefits only EMPLOYEE.EMP_STATUS='CO' OR --contractor EMPLOYEE.EMP_STATUS='DI' OR --disability EMPLOYEE.EMP_STATUS='FL' OR --FMLA EMPLOYEE.EMP_STATUS='FM' OR --FMLA, why are there two? EMPLOYEE.EMP_STATUS='GA' OR --garnishment EMPLOYEE.EMP_STATUS='LA' OR --leave of absence EMPLOYEE.EMP_STATUS='LO' OR --leave of absence, two of these too? EMPLOYEE.EMP_STATUS='NN' OR --needed new emp# due to garnishment EMPLOYEE.EMP_STATUS='RT' OR --retirement EMPLOYEE.EMP_STATUS='TE' OR --temp over 30hrs EMPLOYEE.EMP_STATUS='TM' OR --temp under 30 hrs EMPLOYEE.EMP_STATUS='TP' OR --terminated, pay pending EMPLOYEE.EMP_STATUS='TR' OR --transferred process levels EMPLOYEE.EMP_STATUS='WC' OR --workers comp ((EMPLOYEE.TERM_DATE > {ts '2003-10-01 00:00:00'}) AND (EMPLOYEE.EMP_STATUS='T')); /* Supervisor Information (HR507) */ SELECT 'A' AS 'FC', HRSUPER.COMPANY, HRSUPER.CODE, HRSUPER.DESCRIPTION, HRSUPER.EMPLOYEE, HRSUPER.EFFECT_DATE, HRSUPER.ACTIVE_FLAG, HRSUPER.SUPER_RPTS_TO, HRSUPER.USER1, HRSUPER.USER2, HRSUPER.USER3, HRSUPER.USER4, HRSUPER.USER5 FROM dbo.HRSUPER INNER JOIN dbo.EMPLOYEE ON dbo.HRSUPER.EMPLOYEE = dbo.EMPLOYEE.EMPLOYEE WHERE EMPLOYEE.EMP_STATUS='AP' OR --active, part time EMPLOYEE.EMP_STATUS='AF' OR --active, full time EMPLOYEE.EMP_STATUS='BN' OR --benefits only EMPLOYEE.EMP_STATUS='CO' OR --contractor EMPLOYEE.EMP_STATUS='DI' OR --disability EMPLOYEE.EMP_STATUS='FL' OR --FMLA EMPLOYEE.EMP_STATUS='FM' OR --FMLA, why are there two? EMPLOYEE.EMP_STATUS='GA' OR --garnishment EMPLOYEE.EMP_STATUS='LA' OR --leave of absence EMPLOYEE.EMP_STATUS='LO' OR --leave of absence, two of these too? EMPLOYEE.EMP_STATUS='NN' OR --needed new emp# due to garnishment EMPLOYEE.EMP_STATUS='RT' OR --retirement EMPLOYEE.EMP_STATUS='TE' OR --temp over 30hrs EMPLOYEE.EMP_STATUS='TM' OR --temp under 30 hrs EMPLOYEE.EMP_STATUS='TP' OR --terminated, pay pending EMPLOYEE.EMP_STATUS='TR' OR --transferred process levels EMPLOYEE.EMP_STATUS='WC' OR --workers comp ((EMPLOYEE.TERM_DATE > {ts '2003-10-01 00:00:00'}) AND (EMPLOYEE.EMP_STATUS='T')); /* Time Accrual Balances (TA570) */ SELECT EMTAMASTR.COMPANY, EMTAMASTR.PLAN_NAME, EMTAMASTR.EMPLOYEE, '' AS 'TRANS_DATE', --fed from TA570 job EMTAMASTR.ACR_BALANCE, EMTAMASTR.ELIG_BALANCE FROM dbo.EMTAMASTR INNER JOIN dbo.EMPLOYEE ON dbo.EMTAMASTR.EMPLOYEE = dbo.EMPLOYEE.EMPLOYEE WHERE EMPLOYEE.EMP_STATUS='AP' OR --active, part time EMPLOYEE.EMP_STATUS='AF' OR --active, full time EMPLOYEE.EMP_STATUS='BN' OR --benefits only EMPLOYEE.EMP_STATUS='CO' OR --contractor EMPLOYEE.EMP_STATUS='DI' OR --disability EMPLOYEE.EMP_STATUS='FL' OR --FMLA EMPLOYEE.EMP_STATUS='FM' OR --FMLA, why are there two? EMPLOYEE.EMP_STATUS='GA' OR --garnishment EMPLOYEE.EMP_STATUS='LA' OR --leave of absence EMPLOYEE.EMP_STATUS='LO' OR --leave of absence, two of these too? EMPLOYEE.EMP_STATUS='NN' OR --needed new emp# due to garnishment EMPLOYEE.EMP_STATUS='RT' OR --retirement EMPLOYEE.EMP_STATUS='TE' OR --temp over 30hrs EMPLOYEE.EMP_STATUS='TM' OR --temp under 30 hrs EMPLOYEE.EMP_STATUS='TP' OR --terminated, pay pending EMPLOYEE.EMP_STATUS='TR' OR --transferred process levels EMPLOYEE.EMP_STATUS='WC' OR --workers comp ((EMPLOYEE.TERM_DATE > {ts '2003-10-01 00:00:00'}) AND (EMPLOYEE.EMP_STATUS='T')); /* Training History (TR500) */ SELECT PATRNHIST.COMPANY, PATRNHIST.EMPLOYEE, PATRNHIST.COURSE, PATRNHIST.DATE_COMPLETED, PATRNHIST.CEU_AWARDED FROM dbo.PATRNHIST INNER JOIN dbo.EMPLOYEE ON dbo.PATRNHIST.EMPLOYEE = dbo.EMPLOYEE.EMPLOYEE WHERE EMPLOYEE.EMP_STATUS='AP' OR --active, part time EMPLOYEE.EMP_STATUS='AF' OR --active, full time EMPLOYEE.EMP_STATUS='BN' OR --benefits only EMPLOYEE.EMP_STATUS='CO' OR --contractor EMPLOYEE.EMP_STATUS='DI' OR --disability EMPLOYEE.EMP_STATUS='FL' OR --FMLA EMPLOYEE.EMP_STATUS='FM' OR --FMLA, why are there two? EMPLOYEE.EMP_STATUS='GA' OR --garnishment EMPLOYEE.EMP_STATUS='LA' OR --leave of absence EMPLOYEE.EMP_STATUS='LO' OR --leave of absence, two of these too? EMPLOYEE.EMP_STATUS='NN' OR --needed new emp# due to garnishment EMPLOYEE.EMP_STATUS='RT' OR --retirement EMPLOYEE.EMP_STATUS='TE' OR --temp over 30hrs EMPLOYEE.EMP_STATUS='TM' OR --temp under 30 hrs EMPLOYEE.EMP_STATUS='TP' OR --terminated, pay pending EMPLOYEE.EMP_STATUS='TR' OR --transferred process levels EMPLOYEE.EMP_STATUS='WC' OR --workers comp ((EMPLOYEE.TERM_DATE > {ts '2003-10-01 00:00:00'}) AND (EMPLOYEE.EMP_STATUS='T'));