Hi all.
I would appreciate some assistance on where to start and even a recommended solution as I am stumped. I am also learning. I am trying to build a database which fills the gaps between several major systems which deal with rostering and payroll and ultimately the general ledger. These systems leave me with massive variances to correct via journal so I'm hoping to build a database which will turn days of work into hours or even minutes. ?
I have started to build a database which imports the ledger transactions for each pay run, retains the establishment/position list and the default staff pay location and relevant percentage.
I have the added following tables:
Employees (EmployeeID)
EmployeePosition (a Many to Many relationship between EmployeeID and PositionID)
Establishment (PositionID)
DefaultChargeCode (m2m to PositionID - which permits allocation of multiple charge codes to distribute a percentage of employee costs to different business areas)
GeneralLedger (GLI PK, PosID)
At the moment I am running a make table query to extract correct and incorrect charges based on the ChargeCode table. Basically the correct charges stay the same and incorrect charges are altered only to move debit charges to the credit column to reverse the transaction.
So, the current (probably not ideal design) is for correct entries to be saved into another GLICorrect(pk) table and incorrect entries into a GLIIncorrect(pk) table (all transactions retain the original primary key number from the GeneralLedger table to avoid duplications)
I have a FinanceJournal table which I need to house the GLIIncorrect entries and summarised corrections but because our primary systems are so inaccurate there are usually several incorrect charge lines to every correct line, so I need to leave the incorrect line as it is so the journal reverses the original charge and then sum the total reversal based on a up to six table columns which creates the account string to apply the employee charge to the correct allocated position charge code.
The final blue sky dream outcome would be to be able in some instances to apply position based percentage splits for those roles which are shared and paid for by different areas of the business.
Eg. for role 2877 the payroll charges will be applied to different business area charge strings.
ChargeCodeID PosID EntID FundID CostCtrID ActivityID ProjectID InitiativeID Percentage 573 2877 11 210 3660 1012 00000 000 25 574 2877 11 210 3662 1012 00000 000 25 575 2877 11 210 3664 1012 00000 000 25 576 2877 11 210 3666 1012 00000 000 25
The table leaves out the account code which will potentially add tens of additional lines to the charges to be distributed to those individual lines.
Hopefully the above is clear as I am never sure if I have provided too little info or too much.
Thanks :-)