Company: Link Technologies
Case No: L12728. Project: 14.00: LinkSOFT Version 14.00
Logged By: Vineet (Link Business Solutions) on 25 May 2022 07:03AM
Priority: High
Product: Payroll & HR
Group: Software Defect
Time Taken: 75.50 (Weight: 76.50)
Version: 14.00
Assigned To: Development
Circulation: Aarti Pooja Gayaneshwar, Development, Sanjay, Vineet
Resolve By: Tuesday, 31 May 2022 12:00 AM [694 days since logged date]
Status: Closed
Subject: Gross and Taxable Allowance Between Pay Analysis, Pay Accumulator, Levy, Employee Payment History and Tax Employer Monthly Schedule Not Reconciling
Summary:    

Hi Vineet,

Further to the discussion, when comparing our Pay Day Reports Taxable components to that of Pay Analysis, PayCode Accumulator and Pay History, there are big variances. This is happening for most months. Below is an analysis that I did for one month


 

PayCode Accumulator (Jan 2022)

Pay Analysis (Jan 2022)

Payment History (Jan 2022)

Employer Tax Monthly Schedule (Jan 2022)

Gross

 $                             2,249,540.49

 $             2,249,540.49

 $                     2,200,180.74

 

Tax Allowance

 $                                   66,750.41

 $                  66,750.41

 $                          64,572.07

 

Total

 $                             2,316,290.90

 $             2,316,290.90

 $                    2,264,752.81

2,343,427.30

 

We need to lodge our Levy which is based on Gross submitted to FRCS and we really need to know what is correct. Seek your teams urgent assistance.

Audit Notes:Edited by sanjay on 02/06/22 14:57. Edited by sanjay on 31/05/22 11:56. Edited by sanjay on 30/05/22 14:13. Edited by sanjay on 26/05/22 17:43. Edited by sanjay on 26/05/22 11:16. Edited by sanjay on 26/05/22 10:38. Edited by vineet on 25/05/22 07:03. 
24 May 202208:00AM Comment 1 by Vineet (Link Business Solutions) Assigned To: Vineet (Link Business Solutions) Followup Date: 25-05-2022 07:30 AM Time Taken: 3.00 Notes: Edited by sanjay on 30/05/22 14:16. 

We have done the following checks from our end:

  1. Verified the Classification of all components that have been tagged Normal and Bonus. These are the only 2 columns in the report giving the sum.
  2. Verified the pay classification for each pay code
  3. Levy report gross is also showing Gross payable for levy as   2,316,290.90. However, the levy is based on gross submitted to FRCS hence needs to identify this discrepancy in total.

The difference between Gross in Pay Analysis, Pay Code Accumulator, Levy Report, and Gross submitted to FRCS is overstated by  $27,136.40. This total is not a direct sum of any component.

We will diagnose this with our development team and let you know by this afternoon

Regards
Vineet Ram
222 0085


25 May 202207:02AM Comment 2 by Vineet (Link Business Solutions) Assigned To: Sanjay (Link Technologies) Followup Date: 25-05-2022 12:34 PM Time Taken: 1.00 Notes: Edited by sanjay on 30/05/22 14:21. Edited by sanjay on 30/05/22 14:16. Edited by vineet on 26/05/22 10:47. Edited by vineet on 26/05/22 10:11. Edited by sanjay on 25/05/22 13:52. 

Bula Sanjay

The setup details for this as below:

URL:             http://10.0.0.18/XXX/
Credentials: Default admin credentials
Version:      13.40
Server: QA8
Database: LINKSOFT-XXX-Training

For the purpose of this case, we will generate: 

  1. Pay Analysis
    1. Company: 1001
    2. DateFrom: 01/01/2022
    3. DateTo: 31/01/2022
    4. ReportLevel: Hide Employees
    5. Gross+Taxable Allowance = 2316290.90
  2. PayCode Accumulator Levy
    1. Company: 1001
    2. DateFrom: 01/01/2022
    3. DateTo: 31/01/2022
    4. ShowEmployee: N
    5. ShowDetails: N
    6. Gross+Taxable Allowance = 2316290.90
  3. Pay History
    1. Company: 1001
    2. DateFrom: 01/01/2022
    3. DateTo: 31/01/2022
    4. Gross+Taxable Allowance = 2264752.91
  4. Tax Employer Monthly Schedule
    1. Company: 1001
    2. DateFrom: 01/01/2022
    3. DateTo: 31/01/2022
    4. ConsolidateAllCompany: No
    5. TaxableGross: 2,343,427.30

For the period 01/01/2022 to 31/01/2022 and compare the Taxable Gross Figures in each of these reports.

Definition of Taxable Gross is (Gross+Taxable Allowance+ Cash Benefit + Excess Super Paid by the Employer in access of 10%). 

Problem: Gross submitted for Fiji Revenue and Customer Services as PAYE lodgement does not reconcile with Taxable Gross in Pay Analysis, PayCode Accumulator, and Levy Report. When Levy is lodged we use the Levy Report from the system, however, the Department of Levy calculates employers levy based on Gross submitted to FRCS. These 2 need to reconcile with each other for correct lodgement to both authorities.

Regards
Vineet Ram
222 0085


25 May 202202:08PM Comment 3 by Sanjay (Link Technologies) Assigned To: Vineet (Link Business Solutions) Followup Date: 25-05-2022 07:52 PM Time Taken: 3.00 Notes: Edited by sanjay on 30/05/22 14:21. 

Hi Vineet,

I have tidied up comment 2 so it is easier to identify the reports and the issue. Which company is this for?.

Also, I checked the Gross calculations in DEMO and our calculations are correct. You have not stated the report parameters and expected values so I cannot verify this on CLIENT database yet.

Can you bring this to a meeting so we can discuss what the expected values are and what needs to be done next?

We can meet this afternoon, or send a meeting request for tomorrow morning at 10 am AEST or 2 PM aest whichever one suits you.


26 May 202211:19AM Comment 4 by Sanjay (Link Technologies) Assigned To: Development Followup Date: 26-05-2022 05:16 PM Time Taken: 2.00
Vineet and Sanjay - Discussion Notes:
  1. We are unable to replicate the issue in DEMO

Next Steps:

  1. Provide details on how Gross and Taxable Allowance is calculated in the reports "Pay Analysis" and "Pay History" and why there is a difference - Sanjay
  2. Provide a list of Components in this database that contributes to Gross and Taxable Allowance - Sanjay
  3. Do a pivot analysis on "Pay Analysis" and "Pay History" to identify which employees have incorrect amounts - Vineet
  4. Identify which amount is correct based on 3. above. - Vineet

Once we have the above 4 items we can work out the next steps.


26 May 202203:10PM Comment 5 by Sanjay (Link Technologies) Assigned To: Development Followup Date: 26-05-2022 08:08 PM Time Taken: 4.00

Hi Vineet, in response to items 1 and 2 of comment 4, here is my response:

  1. Both reports use the exact same functions to compute Gross and Taxable Allowances
  2. The report uses User access to Employees, and I noticed that the "Admin" user on the report does not have access to all Pay Teams. This could be the reason for the difference

Can you give all Pay Teams access to the Admin user and review the report? 


26 May 202205:42PM Comment 6 by Sanjay (Link Technologies) Assigned To: Development Followup Date: 26-05-2022 11:42 PM Time Taken: 1.00

Hi Vineet,

Here is a list of Pay Teams the ADMIN user needs access to:

  1. CAS
  2. CAS1
  3. CREST
  4. SAL
  5. SALNU
  6. SALO
  7. TUCK
  8. WAGEO

26 May 202205:43PM Comment 7 by Sanjay (Link Technologies) ETC was changed from 24/05/2022 to 31/05/2022
27 May 202202:05PM Comment 8 by Sanjay (Link Technologies) Assigned To: Development Followup Date: 27-05-2022 03:48 PM Time Taken: 8.00 Notes: Edited by sanjay on 30/05/22 14:19. 
*** Skype conversation ***
Sanjay:  I am looking at this query and found something unusual. Can you give me a use case where an employee will have multiple Tax File numbers?

Vineet, 1:42 PM
What they are doing is when an employee moves from Casual to Permanent they create a new profile entirely. The Tax Number remains on both the Active and Inactive Profile

Sanjay: 1:43 PM
ok

Vineet, 1:43 PM
By the way with all pay team access is given diff is now 1k

Sanjay:
In the database, there are no scenarios where two employee ID's have the same Tax ID. Can you give me an example?
I think they are removing the TaxID from the inactive employee as there are 3000 employees with empty TaxID
The Pay analysis report uses user access to pay teams. The Payment History doesn't do this. We should make it consistent

Vineet, 1:56 PM
I updated it to NULL when trying to compare as employee's names were coming incorrectly

You will see the social security number with the tax Id for these inactive employees

Sanjay 1:56 PM
ok, its the Nulls and Empty values in the TaxID that's causing the performance issue
Also, you didn't say you have been updating data on the database in your helpdesk case

Vineet, 1:57 PM
Oh ok.I was trying to compare the name of employees using vlookup. Levy and Tax Employer had different names because tax employer get name via tin, first name it gets

So with multiple employees on the same TIN I couldn't compare by name. It's only here not on their production

1:58 PM
Can I restore the database and try again?

27 May 202202:23PM Comment 9 by Vineet (Link Business Solutions) Assigned To: Development Followup Date: 27-05-2022 07:48 PM Time Taken: 0.50 Notes: Edited by sanjay on 30/05/22 14:19. Edited by vineet on 27/05/22 15:28. 

Bula Sanjay

With all pay teams' access provided to role administrators below is the analysis:

Levy Report Gross Total: 2,342,325.63
Tax Employer Monthly Schedule Report Total: 2,343,427.30
Difference: $1101.67

Regards
Vineet Ram


30 May 202209:40AM Comment 10 by Vineet (Link Business Solutions) Assigned To: Development Followup Date: 30-05-2022 01:33 PM Time Taken: 0.25

Hi Sanjay

Please disregard the Employee by Employee Analysis in comment 9. We did a lookup using the employee's name between the Levy report and Tax Employer Monthly Schedule however, there are many employees with the exact same name. This Analysis would be difficult for now unless we add Employee ID in the data grid view. However, there is a total difference of  $1101.67 between the 2 reports.

Regards
Vineet Ram
222 0085


30 May 202201:57PM Comment 11 by Sanjay (Link Technologies) Assigned To: Development Followup Date: 30-05-2022 06:06 PM Time Taken: 6.00 Notes: Edited by sanjay on 30/05/22 14:00. 

Hi Vineet, The issue you are having is related to the Employees Tax ID. For testing purposes, I have updated the TAXID of the employees with the EMPLOYEE code. This now makes all reports line up,

These Tax Reports rely on the TIN No to be accurate and completed, therefore it is important to check this diligently.

We can do a team session to see what the issue is with TIN numbers and how we can make it easier to reconcile.


31 May 202211:56AM Comment 12 by Sanjay (Link Technologies) Case L12728 added to project 14.00
31 May 202212:18PM Comment 13 by Sanjay (Link Technologies) Assigned To: Development Followup Date: 31-05-2022 05:56 PM Time Taken: 14.00
PART A - Development work for this case has been completed.

1. The change will be available in version:14.00 and 13.40.0531

2. The following changes were made(Include Database object names, Program classes, and any other relevant information):

  1. Changed the report "Tax Employer Monthly Schedule" to allow the same Employee TIN No within one company and across multiple companies.

3. Affected Areas:

  1. Report: Tax Employer Monthly Schedule 

4. The issue was caused by:

  1. The function did not account for the same Employee TIN in Multiple companies with the same Company FTN

5. Other Relevant Notes
6. Next Step
(Review and System Test (Developer) -> UAT (Quality) -> Documentation): UAT and Documentation


LinkSOFT 13.40.0531 PATCH is available at this link.


06 Jun 202212:22PM Comment 14 by Vineet (Link Business Solutions) Assigned To: Sanjay (Link Technologies) Followup Date: 08-06-2022 06:08 PM Time Taken: 4.00 Notes: Edited by vineet on 07/06/22 14:06. 

Bula Sanjay

I have done the test after applying the updates. Below is the result for the Year 2021. It reconciles for all months except March 2021 where the Tax Employer Monthly schedule is now understated by $947.36. The Taxable Gross for Pay Analysis, Levy, and Employee Payment History is reconciling now.

Figure 1.1 Analysis of the 4 Reports

 

Pay Analysis

 

 

Tax Employer Monthly Schedule

Levy

Employee Payment History

 

 

Gross

Taxable Allowance

Taxable Gross

Taxable Gross

Taxable Gross

Gross

Taxable Allowance

Taxable Gross

Jan-21

2,316,923.40

77,550.59

2,394,473.99

2,394,473.99

2,394,473.99

2,316,923.40

77,550.59

2,394,473.99

Feb-21

1,590,442.42

66,057.94

1,656,500.36

1,656,500.36

1,656,500.36

1,590,442.42

66,057.94

1,656,500.36

Mar-21

1,635,648.71

129,916.31

1,765,565.02

1,764,617.66

1,765,565.02

1,635,648.71

129,916.31

1,765,565.02

Apr-21

1,709,682.14

83,870.10

1,793,552.24

1,793,552.24

1,793,552.24

1,709,682.14

83,870.10

1,793,552.24

May-21

1897141.10

79,695.80

1,976,836.90

1,976,836.90

1,976,836.90

1,897,141.10

79,695.80

1,976,836.90

Jun-21

1562082.22

61067.10

1,623,149.32

1,623,149.32

1,623,149.32

1,562,082.22

61,067.10

1,623,149.32

Jul-21

1650724.31

57371.93

1,708,096.24

1,708,096.24

1,708,096.24

1,650,724.31

57,371.93

1,708,096.24

Aug-21

1773671.09

69596.80

1,843,267.89

1,843,267.89

1,843,267.89

1,773,671.09

69,596.80

1,843,267.89

Sep-21

1576186.84

50132.20

1,626,319.04

1,626,319.04

1,626,319.04

1,576,186.84

50,132.20

1,626,319.04

Oct-21

1869038.67

65154.75

1,934,193.42

1,934,193.42

1,934,193.42

1,869,038.67

65,154.75

1,934,193.42

Nov-21

1690941.65

56560.09

1,747,501.74

1,747,501.74

1,747,501.74

1,690,941.65

56,560.09

1,747,501.74

Dec-21

1,721,446.84

102,871.00

1,824,317.84

1,824,317.84

1,824,317.84

1,721,446.84

102,871.00

1,824,317.84

 

The Filters used for the 4 rep

  1. Pay Analysis
    1. Company: 1001
    2. DateFrom: 01/03/2021
    3. DateTo: 31/03/2021
    4. ReportLevel: Hide Employees
    5. Gross+Taxable Allowance = 1,765,565.02
  2. Levy Report
    1. Company: 1001
    2. DateFrom: 01/03/2021
    3. DateTo: 31/03/2021
    4. Gross+Taxable Allowance = 1,765,565.02
  3. Employee Payment History
    1. Company: 1001
    2. DateFrom: 01/03/2021
    3. DateTo: 31/03/2021
    4. Group By: Pay Calendar Year
    5. Gross+Taxable Allowance = 1,765,565.02
  4. Tax Employer Monthly Schedule (Fiji)
    1. Company: 1001
    2. DateFrom: 01/03/2021
    3. DateTo: 31/03/2021
    4. ConsolidateAllCompany: No
    5. TaxableGross: 1,764,617.66

Please review what could have caused this and let me know so we can discuss this further

Regards
Vineet Ram
222 0085



07 Jun 202202:04PM Comment 15 by Sanjay (Link Technologies) Assigned To: Vineet (Link Business Solutions) Followup Date: 07-06-2022 07:59 PM Time Taken: 1.00
Hi Vineet, Case L12728 Comment 14 shows report parameters for March 2022, but the results table shows data from March 2021. Can you correct comment 14? Thanks.

07 Jun 202202:11PM Comment 16 by Vineet (Link Business Solutions) Assigned To: Sanjay (Link Technologies) Followup Date: 07-06-2022 08:06 PM Time Taken: 0.25

Bula Sanjay

Comment 14 has been corrected with the dates of March 2021.

Thanks
Vineet


08 Jun 202210:53AM Comment 17 by Sanjay (Link Technologies) Assigned To: Vineet (Link Business Solutions) Followup Date: 08-06-2022 04:40 PM Time Taken: 8.00

Hi Vineet, the difference is caused by TAX_NO "185958204"

Since this report groups by TIN No, when having employees with the same TAX_NO, they need to be exactly identical. 

I used this script to correct the data: 

-- UPDATE HR_EMPLOYEE SET TAX_ID = '185958204' WHERE EMPLOYEE_ID IN ('S0384','C0055', 'W0816')


08 Jun 202202:24PM Comment 18 by Vineet (Link Business Solutions) Assigned To: Sanjay (Link Technologies) Followup Date: 09-06-2022 08:20 PM Time Taken: 0.50

Hi Sanjay

After this change, the reports for the Year 2021 are in reconciliation. If there are such data issues in the Year 2022, how can we handle them? Please do share so we can document and get the support team to handle these in the future

Regards
Vineet Ram
222 0085


15 Jun 202212:29PM Comment 19 by Sanjay (Link Technologies) Assigned To: Development Followup Date: 15-06-2022 06:24 PM Time Taken: 12.00
PART A - Development work for this case has been completed.

1. The change will be available in version: 14.00

2. The following changes were made(Include Database object names, Program classes, and any other relevant information):

  1. Added validation on TAX No to enforce AlphaNumeric characters only
  2. On Save, any spaces before, after or in-between Tax number is removed
  3. These changes have been made in the following areas:
    1. Employee Maintenance
    2. Applicant Maintenance
    3. Dependant Maintenance

3. Affected Areas:

  1. Employee Maintenance
  2. Applicant Maintenance
  3. Dependant Maintenance

4. The issue was caused by:

  1. Validation on TAX No required as Tax reports group by Tax No

5. Other Relevant Notes
6. Next Step
(Review and System Test (Developer) -> UAT (Quality) -> Documentation): UAT


Notes: You can use this script to correct any spaces on existing clients.   

UPDATE HR_Employee SET TAX_ID = [dbo].[LT_SYS_RemoveSpecialCharacters_fx](TAX_ID)


19 Jul 202203:33PM Comment 20 by Sanjay (Link Technologies) Assigned To: Development Followup Date: 19-07-2022 09:27 PM Time Taken: 7.00
The following tests were performed:

Table 1 - Test Results
NoTest CaseExpected ResultPass/FailComments
1Edit employee tax Number on employee profile and try and add space 
The system should save removing any spaces before, after or in-between
Pass

2

Edit TFN on the following menus:

  1. Employee Maintenance
  2. Applicant Maintenance
  3. Dependant Maintenance
Same as 1. above
Pass

3Generate reports "Pay Analysis", "Levy Report", "Employee Payment History" and "Tax Employer Monthly Schedule" and verify
Gross, Taxable Allowance should match
Pass

4



5



Environment Details

  1. OS version: Win11
  2. Application version: 14.00
  3. Setup on:
    1. Server: LinkQA4
    2. Database: LinkSOFT
    3. LinkSOFT URL: HTTP://LinkQA4/LinkSOFT
  4. Login Details: Standard username and password for user "admin"

Next Step: Closure


If you have any queries regarding this support incident, please email admin@linktechnologies.com.au and include the Case No: L12728 in the subject line of all emails regarding this issue.

Document size: 72.7 KB
For call complaints, please contact the Managing Director of the company using this form