BAL_ADJ_TAX - Excel by vpi57995

VIEWS: 94 PAGES: 24

									                                            BAL_ADJ_TAX1042



FIELD NAME       TYPE Start Position RAW LENGTH MASK FORMAT   DESCRIPTION
RECORD_NAME      Char               1          8    8 Upper   Record Name 'BALADJTX'
SSN              Char               9          9    9         Social Security Number
EMPLID           Char              18         11   11 Upper   EmplID
COMPANY          Char              29          3    3 Upper   Company
BALANCE_ID       Char              32          2    2 Upper   Balance ID
BALANCE_YEAR     Nbr               34          4    4         Balances for Year
STATE            Char              38          6    6 Upper   State
LOCALITY         Char              44         10   10 Upper   Locality
TAX_CLASS        Char              54          1    1         Tax Class
BAL_ADJ_SEQ      Nbr               55          3    3         Balance Adjust Sequence Number
DT_ENTERED       Date              58         10   10         Date Entered
BALANCE_QTR      Nbr               68          3    3         Balances for Quarter
BALANCE_PERIOD   Nbr               71          3    3         Balances for Period
TAX_YTD          Sign              74         12  9.2         Tax YTD
TAX_QTD          Sign              86         12  9.2         Tax QTD
TAX_MTD          Sign              98         12  9.2         Tax MTD
TAX_YTD_ADJ      Sign             110         12  9.2         Tax YTD Adjusted
TAX_QTD_ADJ      Sign             122         12  9.2         Tax QTD Adjusted
TAX_MTD_ADJ      Sign             134         12  9.2         Tax MTD Adjusted
TAX_ADJ          Sign             146         12  9.2         Tax Adjustment
TXGRS_YTD        Sign             158         12  9.2         Taxable Gross YTD
TXGRS_QTD        Sign             170         12  9.2         Taxable Gross QTD
TXGRS_MTD        Sign             182         12  9.2         Taxable Gross MTD
TXGRS_YTD_ADJ    Sign             194         12  9.2         Taxable Grs YTD Adjusted
TXGRS_QTD_ADJ    Sign             206         12  9.2         Taxable Grs QTD Adjusted
TXGRS_MTD_ADJ    Sign             218         12  9.2         Taxable Grs MTD Adjusted
TXGRS_ADJ        Sign             230         12  9.2         Taxable Gross Adjustment
NLGRS_YTD        Sign             242         12  9.2         No Limit Gross YTD
NLGRS_QTD        Sign             254         12  9.2         No Limit Gross QTD
NLGRS_MTD        Sign             266         12  9.2         No Limit Gross MTD
NLGRS_YTD_ADJ    Sign             278         12  9.2         No Limit Gross YTD Adjusted
NLGRS_QTD_ADJ    Sign             290         12  9.2         No Limit Gross QTD Adjusted
NLGRS_MTD_ADJ    Sign             302         12  9.2         No Limit Gross MTD Adjusted
NLGRS_ADJ        Sign             314         12  9.2         No Limit Gross Adjustment
TIPS_MTD         Sign             326         12  9.2         MTD Tips
TIPS_MTD_ADJ     Sign             338         12  9.2         MTD Tips Adjusted
TIPS_ADJ         Sign             350         12  9.2         Tips Adjusted
ADJ_REASON       Char             362         50   50 Mixed   Adjustment Reason
FILLER           Char             412          6              Spaces

                      Total Length        417
FIELD NAME        TYPE START POS RAW LENGTH MASK FORMAT   DESCRIPTION
RECORD_NAME       Char          1          8    8 Upper   Record Name
SSN               Char          9          9    9         Social Security Number
EMPLID            Char         18         11   11 Upper   EmplID
COMPANY           Char         29          3    3 Upper   Company
BALANCE_ID        Char         32          2    2 Upper   Balance ID
BALANCE_YEAR      Nbr          34          4    4         Balances for Year
BALANCE_QTR       Nbr          38          3    3         Balances for Quarter
BALANCE_PERIOD    Nbr          41          3    3         Balances for Period
TOTAL_GROSS_YTD   Sign         44         12  9.2         Total Gross YTD
TOTAL_TAXES_YTD   Sign         56         12  9.2         Total Taxes YTD
TOTAL_DEDNS_YTD   Sign         68         12  9.2         Total Deductions YTD
NET_PAY_YTD       Sign         80         12  9.2         Net Pay YTD
FILLER            Char         92        326              Spaces

                      Total Length      417
RECORD_NAME      Char   1    8     8 Upper Record Name               TABLE
SSN              Char   9    9     9       Social Security Number




EMPLID           Char   18   11   11 Upper EmplID                   APSEM

COMPANY          Char   29   3       Upper Company                  APSEMAG
BALANCE_ID       Char   32   2     2 Upper Balance ID

BALANCE_YEAR     Nbr    34   4     4       Balances for Year


BALANCE_QTR      Nbr    38   3     3       Balances for Quarter

BALANCE_PERIOD   Nbr    41   3     3       Balances for Period

BENEFIT_RCD_NBR Nbr     44   3     3       Benefit Record Number




PLAN_TYPE        Char   47   2     2 Upper Plan Type                APSEMDP
                                     Value Description
                                       0 General Deduction
                                            10   Medical
                                            11   Dental
                                            14   Vision
                                            1Y   Dental - Dependents
                                            1Z   Medical - Dependents
                                            20   Life
                                            21   Supplemental Life
                                            25   Dependent Life
                                            27   Supplemental Life - 1st 20000
                                            2X   Supp Life > $50,000
                                            30   Short-Term Disability
                                            47   Nonelective Contributions
                                            48   Employer Only
                                            49   Section 457
                                            4Y   OPERS
                                            4Z   Administrative Fee
                                            50   Sick
                                            51   Vacation
                                            60   Flex Spending Health - U.S.
                                            61   Flex Spending Dependent Care
                                            70   PERS
                                            7Y   PERS 3
                                            7Z   PERS 2
                                            82   Pension Plan 1 - U.S.




BENEFIT_PLAN   Char            49   6    6 Upper Benefit Plan
DEDCD          Char            55   6    6 Upper Deduction Code                  APSEMDD
DED_CLASS      Char            61   1    1 Upper Deduction Classification
                                           Value Description
                                             A After-Tax
                                             B Before-Tax
                                             L QC Taxable Benefit
                                             N Nontaxable Benefit
                                             P Nontaxable Btax Benefit
                                             T Taxable Benefit

DED_YTD        Sign            62 12 9.2         Deduction Balance YTD
DED_QTD        Sign            74 12 9.2         Deduction Balance QTD
DED_MTD        Sign            86 12 9.2         Deduction Balance MTD
FILLER         Char            98 320            Spaces

                      Total Length 417
 FIELD     TYPE   LENGTH CONVERSION RULE                     ISSUES / Comments

                                                           Load PS Earnings_Bal before loading
                                                           PS Deduction_Bal so PS
                           PS Load program is looking up Deduction_Bal can set
                           SSN on PERS_NID to obtain Benefit_Rcd_Nbr =
                           EmplID.                         Earnings_Bal.Empl_Rcd.
                           Extract file will exclude any
                           check detail with object code   Legacy File needs to be sorted by SSN,
                           112700.                         then Agency, then Plan_Type. Legacy
EMXX-R-                    Extract Load. This extract is file also needs to include DeptID and
SSN     Numeric          9 created for Fiscal Year totals. JobCode.
EMAG-C-                    Exract Load will furnish         PS Load will validate Agency # by
AGCY    Numeric          3 Agency # = Company.             comparing to Company Table.
                           PS Load will default to "FY'
                           PS Load will populate with
                           Current FY.
                           PS Load will populate based
                           on Balance Period furnished
                           (below) by Extract Load..
                           Extract Load will furnish the   January = "7", February = "8", March =
                           Period number.                  "9", etc.
                           PS Load will default to Empl    Based on Compnay, DeptID, and Job
                           Rcd Nbr of Job.                 Code furnished by Extract Load.

                           Extract Load will first check     The "RET TRS SS" field is EMDP-A-
                           the legacy payroll field "RET     RET-TRS-SS.
                           TRS SS" and if the amount for
                           this field is greater than 0.00   The "ST PD TRS" field is EMDP-A-ST-
                           use the detail to capture the     PAID-TRS.
                           "ST PD TRS" and "RET DEF"
                           information as noted below. If    The "RET DEF" field is EMDP-A-RET-
                           the "RET TRS SS" field is         OTHER-DEF.
                           equal to 0.00, then skip the
                           check detail and go to the next
                           check detail for this employee.

                        If the above is true then check
EMDP-A- Signed S9(5)v99 the legacy payroll field "ST PD
RET-TRS- Decimal        TRS", if the amount for this
SS                      field is greater than 0.00 then
                        the Plan Type = 70
EMDP-A- Signed S9(5)v99
ST-PAID- Decimal        If the amount in the "RET TRS        Legacy Payroll
TRS                     SS" field is greater than 0.00
                        check to see if the "RET DEF"
EMDP-A- Signed S9(5)v99 field is greater than 0.00, if       Value Descr
RET-     Decimal        "RET DEF" is greater than 0.00       70      PERS
OTHER-                  add a row and set the Plan           7Z       Retirement - TSP 1%
DEF                     Type = 7Z.
AWF 1/21/04:
Extract Load will furnish
using crosswalk.

PS Load will populate based
on DEDCD and DED_CLASS
furnished by Extract Load.
FIELD NAME                                 MASK
               TYPE Start Position RAW LENGTH        FORMAT
RECORD_NAME    Char               1      8         8 Upper
SSN            Char               9      9         9




EMPLID         Char           18       11         11 Upper


COMPANY        Char           29        3          3 Upper
BALANCE_ID     Char           32        2          2 Upper

BALANCE_YEAR   Nbr            34        4          4

BALANCE_QTR    Nbr            38        3          3


BALANCE_PERIOD Nbr            41        3          3




EMPL_RCD       Nbr            44        3          3
SPCL_BALANCE   Char           47        1          1 Upper

ERNCD          Char           48        3           3 Upper
HRS_YTD        Sign           51        8         5.2
HRS_QTD        Sign           59        8         5.2
HRS_MTD        Sign           67        8         5.2
GRS_YTD   Sign               75    12   9.2
GRS_QTD   Sign               87    12   9.2
GRS_MTD   Sign               99    12   9.2
FILLER    Char              111   307

                 Total Length     417
DESCRIPTION                                   LEGACY
Record Name                      TABLE      FIELD       TYPE     LENGTH
Social Security Number




EmplID                          APSEM    EMXX-R-SSN    Numeric        9


Company                         APSEMAG EMAG-C-AGCY Numeric           3
Balance ID

Balances for Year

Balances for Quarter


Balances for Period




Empl Rcd Nbr
Spl Accumulator Bal/Earn Code

Earnings Code
Hours YTD
Hours QTD
Hours Mtd
                             EMDP-A-RET-
                             TRS-SS

                             TOTAL GROSS
                             EMDP-A-GRS
                             EMDP-A-ANNU- Signed S9(5)V99
                             ED             Decimal
                             EMDP-A-ANNU-
                             ST             "       S9(5)V99
                                                    S9(5)V99
                             INS ST GRP             S9(5)V99
                             EMDP-A-INS-ST-         S9(5)V99
                             SS
                             EMDP-A-        "       S9(5)V99
                             ADJUSTED-              S9(5)V99
                             BENEFIT                S9(5)V99

                             EXCESS BEN
                             EMDP-A-       "        S9(5)V99
                             EXCESS-
Gross Earnings YTD   APSEMDP BENEFIT
Gross Earnings QTD
Gross Earnings MTD
Spaces
CONVERSION RULE                 ISSUES / Comments


PS Load program is looking
up SSN on PERS_NID to
obtain EmplID.                  Extract file will be sorted by SSN,
Extract file will exclude any   Agency, JobCode, and DeptID after file
check detail with object code   is created and before it is given to PS
112700.                         Load.
Extract Load. This extract
is created for Fiscal Year      This extract is only being created for
totals only.                    employees with Teachers' Retirement.
                                PS Load will use agency # to assign
Company = Agency # from         company code. PS Load will validate #
Extract Load.                   by comparing to Company Table.
PS Load will default to 'FY'
PS Load will default to         This could change depending on Go-
current 'FY'.                   Live date.
PS Load will default to         This could change depending on Go-
correct FY QTR.                 Live date.
 PS Load will default this to
what ever period is being       This could change depending on Go-
loaded.                         Live date.
Extract file to include         Note: Extract file is looking for first
Agency, Department and          active position and first active pay
Jobcode in extract file.        history to get jobcode (JFD/Class).
Using Company (Agency),
Department, and Jobcode         PS Department = Activity Code from
from extract, PS Load           Fund and Acct Line (OPM Payroll
program to perform lookup in    System) +000
PS_Job (Company,                PS Company = Agency (OPM Payroll
Department & Jobcode) for       System)
PS Load to determine            PS Jobcode = JFD/Class (OPM Payroll
Employee Record number.         System)
PS Load will default to 'Y'

PS Load will default to 'TRS'
PS Load will default to '0'
PS Load will default to '0'
PS Load will default to '0'
Extract file will first check
the legacy payroll field "RET
TRS SS" and if the amount Screen field "RET TRS SS" is actually
for this field is greater than EMDP-A-RET-TRS-SS.
0.00 use the detail to capture
the total gross and benefit
allowance earnings from the
"GROSS" (EMDP-A-GRS
plus EMDP-A-ANNU-ED
plus EMDP-A-ANNU-ST),          Legacy will sum the total gross and
"INS ST GRP" (EMDP-A-INS- total benefit allowance.
ST-SS + EMDP-A-
ADJUSTED-BENEFIT) and First check the legacy payroll field "RET
"EXCESS BEN" (EMDP-A- TRS SS" and if the amount for this field
EXCESS-BENEFIT) fields. is greater than 0.00 use the detail to
                               capture the total gross and benefit
Gross + INS ST GRP (State allowance earnings from the "GROSS",
$) + EXCESS BEN                "INS ST GRP" and "EXCESS BEN"
                               fields.
If the "RET TRS SS" field is
equal to 0.00, then skip the If the "RET TRS SS" field is equal to
check detail and go to the     0.00, then skip the check detail and go
next check for this            to the next check for this employee.
employee.
PS Load will default to '0'
PS Load will default to '0'
FIELD NAME     TYPE Start Position RAW LENGTH MASK FORMAT
RECORD_NAME    Char               1          8    8 Upper
SSN            Char               9          9    9




EMPLID         Char          18          11    11 Upper




COMPANY        Char          29           3     3 Upper
BALANCE_ID     Char          32           2     2 Upper

BALANCE_YEAR   Nbr           34           4     4

BALANCE_QTR    Nbr           38           3     3


BALANCE_PERIOD Nbr           41           3     3




STATE          Char          44           6     6 Upper


LOCALITY       Char          50          10    10 Upper

TAX_CLASS      Char          60           1     1 Upper
                                                  Value

                                                    A
                                                    B

                                                    C

                                                    D

                                                    E


                                                    F

                                                    G
                                   H


                                   J




                                   K


                                   L




                                   M
                                   N
                                   P


                                   Q
                                   R
                                   S
                                   T




                                   U



                                   V
                                   W
                                   X
                                   Z

NLGRS_YTD   Sign   61   12   9.2
NLGRS_QTD   Sign   73   12   9.2
NLGRS_MTD   Sign   85   12   9.2



TXGRS_YTD   Sign   97   12   9.2
TXGRS_QTD   Sign   109   12   9.2




TXGRS_MTD   Sign   121   12   9.2



TAX_YTD     Sign   133   12   9.2



TAX_QTD     Sign   145   12   9.2
TAX_MTD    Sign              157   12    9.2

TIPS_MTD   Sign              169    12   9.2
FILLER     Char              181   237

                  Total Length     417
DESCRIPTION                                   LEGACY
Record Name                      TABLE     FIELD    TYPE         LEN
Social Security Number




                                          EMXX-R-
EmplID                          APSEM     SSN     Numeric         9



                                          EMAG-C-
Company                         APSEMAG   AGCY    Numeric         3
Balance ID

Balances for Year

Balances for Quarter
                                          EMDP-D-
                                          ISSUE-
Balances for Period             APSEMDP   MM      Numeric         2




State                                                             2


Locality

Tax Balance Class
Description
                                          EMAG-C-
Non-resident alien (1042)       APSEMAG   FICA    Alpha           1
Local - Employer and Employee
                                          EMDP-A-
Earned Income Credit            APSEMDP   EIC       Signed Dec    7
                                          EMDP-A-   Signed
OASDI/Disability - EE           APSEMDP   FICA-ES   Dec           7
                                          EMDP-A-   Signed
OASDI/Disability - ER           APSEMDP   FICA-SS   Dec           7

                                          EMDP-A-   Signed
FICA Med Hospital Ins / EE      APSEMDP   MQFE-ES   Dec           7
                                          EMDP-A-   Signed
OASDI/EE - tips                 APSEMDP   FICA-ES   Dec           7
                                           EMDP-A- Signed
                                           FIT     Dec        7

                                           EMDP-A- Signed
Withholding                      APSEMDP   SIT     Dec        7

                                           EMDP-A-   Signed
OASDI/ER - tips                  APSEMDP   MQFE-ES   Dec      7
                                           EMDP-A-   Signed
                                           FIT       Dec      7

                                           EMDP-A- Signed
Excise                           APSEMDP   SIT     Dec        7

                                           EMDP-A- Signed
NJ Supl Workforce Admin Fund     APSEMDP   MQFE-SS Dec        7



                                           EMDP-C-
New Jersey WDPF                  APSEMDP   UNEMP Alpha        9
New Jersey HCSF
Occupational Privilege Tax

                                           EMDP-A- Signed
FICA Med Hospital Ins / ER       APSEMDP   MQFE-SS Dec        7
Local ER
Unemployment - Special
FICA Med Hospital Ins /EE-tips



                                           EMDP-C-
Unemployment ER                  APSEMDP   UNEMP Alpha        9



Unemployment EE
Voluntary Disability Plan EE
Voluntary Disability Plan ER
FICA Med Hospital Ins /ER-tips

No Limit Gross YTD
No Limit Gross QTD
No Limit Gross MTD



Taxable Gross YTD
Taxable Gross QTD             EMDP-A-
                              GRS

                              EMDP-A-
                              ANNU-ED

                              EMDP-A-
                              ANNU-ST

                              EMDP-A-
                              EXCESS-
                              BENEFIT

                              EMDP-A-
                              INS-CAFÉ-
                              ST-ES

                              EMDP-A-
                              INS-CAFÉ-
                              GRP-ES
                                                     All fields
                              EMDP-A-                 except
                              RET-                  EMDP-A-
                              OPERS-                EXCESS-
                              ES                    BENEFIT
                                                        are
                              EMDP-A-               S9(5)V99.
                              RET-
                              OLERS-                EMDP-A-
                              ES       All fields   EXCESS-
                                       are          BENEFIT
                              EMDP-A- Signed           is
Taxable Gross MTD   APSEMDP   RET-JUD- Decimal      S9(4)V99



Tax YTD



Tax QTD
                                EMDP-A- Signed
                                EIC     Dec

                                EMDP-A- Signed
                                FICA-ES Dec

                                EMDP-A- Signed
                                FICA-SS Dec
                   APSEMDP                                7
                                EMDP-A- Signed
                   APSEMDP      MQFE-ES Dec               7

                   APSEMDP      EMDP-A- Signed            7
                                FIT     Dec
                   APSEMDP                                7
                                EMDP-A- Signed
                   APSEMDP      SIT     Dec               7

                   APSEMDP      EMDP-A- Signed            7
                                MQFE-SS Dec
                   APSEMDP                                7
                                EMDP-A- Signed
Tax MTD            APSEMDP      UNEMP Dec                 7

MTD Taxable Tips
Spaces

                                Green fields mean state needs to add criteria for furnishing the data.

                   NOTE: PS Load will create a record for every month and calculate QTD and YTD balances
                            to the Balance Period being loaded.
CONVERSION RULE                        ISSUES / Comments

PS Load program is looking up
SSN on PERS_NID to obtain
EmplID.

Extract Load will include data
for all employees based on the         Legacy File needs to be
conversion rule in the                 sorted by SSN, then
Development Request.                   Agency then Plan Type.
                                       PS Load will use agency #
                                       to assign company code.
                                       PS Load will validate # by
Company = Agency # from                comparing to Company
Extract Load.                          Table.
PS Load will default to "CY'
PS Load will populate with
Current Year.
PS Load will calc and populate
QTR.

                                       January = "1", February =
Extract Load will furnish period.      "2", March = "3", etc.

Extract Load will furnish based
on information pulled for
TAX_CLASS. When dealing with
a State tax, set this field to "OK"
for Oklahoma, or appropriate
State Code. When dealing with a
Federal tax, set this field to "$U".
                                       There are no Local Taxes
                                       being withheld in Legacy
PS Load will default to Blank.         System.
Extract Load will furnish based
on Crosswalk below.

FICA code must be "A", "B", "C",
or "T"


Extract Load will furnish.

Extract Load will furnish.

Extract Load will furnish.


Extract Load will furnish.

Extract Load will furnish.
The value of field STATE
determines whether Withholding
is referring to Federal Income Tax
or State Income Tax. See
previous comments.


Extract Load will furnish.
The value of field STATE
determines whether Withholding
is referring to Federal Income Tax
or State Income Tax. See
previous comments.



If EMDP-C-UNEMP = "C", create
a PAY_TAX record for
Unemployment; otherwise, do not
create a record for
Unemployment.
NA
NA




PS Load will calc and populate.
If EMDP-C-UNEMP = "C", create
a PAY_TAX record for
Unemployment; otherwise, do not
create a record for
Unemployment.
PS Load will calc and populate
based on period being loaded and
MTD total furnished below by
Extract Load.
NA
NA


PS Load will calc and populate.
PS Load will calc and populate.
PS Load will calc and populate.
PS Load will calc and populate
based on period being loaded and
MTD total furnished below by
Extract Load.
PS Load will calc and populate
based on period being loaded and
MTD total furnished below by
Extract Load.




                                    This is the Taxable Wage
                                    Base, not the FICA Wage
                                    Base.

                                    True Gross
                                    EMDP-A-GRS
                                    EMDP-A-ANNU-ED
                                    EMDP-A-ANNU-ST

Extract Load will furnish. In       Pre-Tax Deductions
order to calculate Taxable Gross,   EMDP-A-INS-CAFE-ST-
add the Excess Benefit Allowance    ES
amount to the True Gross, then      EMDP-A-INS-CAFE-GRP-
subtract out any deductions that    ES
were taken on a pre-tax basis       EMDP-A-ANNU-ED
(either FICA pre-tax or State and   EMDP-A-ANNU-ST
Federal pre-tax).                   EMDP-A-RET-OPERS-ES
                                    EMDP-A-RET-OLERS-ES
Taxable Gross = (True Gross +       EMDP-A-RET-JUD-ES
EMDP-A-EXCESS-BENEFIT) -            EMDP-A-RET-OTHER-
Pre-Tax Deductions                  DEF
PS Load will calc and populate
based on period being loaded and
MTD total furnished below by
Extract Load.
PS Load will calc and populate
based on period being loaded and
MTD total furnished below by
Extract Load.
            Extract Load will furnish by using Marsheila: Need to
            amount fields that correspond to knowLegacy field info.
            the given TAX_CLASS                Please fill in Green area.
                                               Legacy System does not
            PS Load will default to 0.         have this broken out.


tate needs to add criteria for furnishing the data.

d for every month and calculate QTD and YTD balances according

								
To top