Payroll Analytics per GL Account

Owned by Sigrid Kviteberg, created
Last updated: Aug 03, 2022 by Daria Chebotaieva
9 min read

Summary

Payroll Analytics per GL Account for a specific year

Keywords

payroll, accounts

Description

This view gives the possibility to view and filter amounts per GL accounts.

Due to the amount of data, the view should be split in two where the last condition is set to fetch Balance Accounts in one view, and the Profit accounts in another view.

Selection

These views will extract all payroll transactions linked up to a GL account for the year 2021.

Make the following selection to view the data:

  • Drag and drop the Amount field in the “Drop Data Fields Here”

  • Drag and drop the fields for Account and EC Number and Text into the field “Drop Row Fields Here”.

  • Drag and drop the fields for Employer into the field “Drop Column Fields Here”.

  • Use the Period field to filter out selected Periods or you can drop it in the rows or column fields as wanted

  • More fields are available for filtering or for dragging and dropping as necessary

 

Field Specification

SELECT
P01.Pin ,
P01.Client Numorgid,
p01.Name,
P01.Employmentstartdate,
P01.Employmentenddate,
P01.Personalidno,
pyh.Periodeused as Period,
pyh.Entrycode as 'Entry Code',
c02.name as 'Rank',
txt.Text as 'Entry Code Name',
convert(varchar,pyh.Entrycode) + ' ' + txt.text as 'EC Number and Text',
ed.AGA as 'Employers Fee',
ed.ftrekk as 'Taxable',
pyh.Amount*Calcsignvalue as 'Amount',
pyh.Account,
da.Accounttext as 'Account Name',
pyh.Account + ' ' + da.Accounttext as 'Account Number and Name',
Calculatedamountfield as 'Count',
t.Ttext as 'EC Text',
v.Name as 'Vessel Name',
v.Orgcode + ' ' + v.Name as 'Vessel Code Name',
e.Name as 'Employer',
c.Name as 'Company'
from
(
select orgnumid, employer, pin, rank, periodeused, entrycode, accountdim2, accountdim3, accountdim4, accountdim5, accountdim6, calculatedamount1 amount, case when calcsign='-' then -1 else 1 end calcsignvalue, accountnoofcalcamount1 account, 1 calculatedamountfield, ((entrycode/1000)*100) + 1 textfield from pw001pyh where periodeused>202100 and periodeused < 202200 union all
select orgnumid, employer, pin, rank, periodeused, entrycode, accountdim2, accountdim3, accountdim4, accountdim5, accountdim6, calculatedamount2 amount, case when calcsign='-' then -1 else 1 end calcsignvalue, accountnoofcalcamount2 account, 2 calculatedamountfield, ((entrycode/1000)*100) + 2 textfield from pw001pyh where periodeused>202100 and periodeused < 202200 union all
select orgnumid, employer, pin, rank, periodeused, entrycode, accountdim2, accountdim3, accountdim4, accountdim5, accountdim6, calculatedamount3 amount, case when calcsign='-' then -1 else 1 end calcsignvalue, accountnoofcalcamount3 account, 3 calculatedamountfield, ((entrycode/1000)*100) + 3 textfield from pw001pyh where periodeused>202100 and periodeused < 202200 union all
select orgnumid, employer, pin, rank, periodeused, entrycode, accountdim2, accountdim3, accountdim4, accountdim5, accountdim6, calculatedamount4 amount, case when calcsign='-' then -1 else 1 end calcsignvalue, accountnoofcalcamount4 account, 4 calculatedamountfield, ((entrycode/1000)*100) + 4 textfield from pw001pyh where periodeused>202100 and periodeused < 202200 union all
select orgnumid, employer, pin, rank, periodeused, entrycode, accountdim2, accountdim3, accountdim4, accountdim5, accountdim6, calculatedamount5 amount, case when calcsign='-' then -1 else 1 end calcsignvalue, accountnoofcalcamount5 account, 5 calculatedamountfield, ((entrycode/1000)*100) + 5 textfield from pw001pyh where periodeused>202100 and periodeused < 202200 union all
select orgnumid, employer, pin, rank, periodeused, entrycode, accountdim2, accountdim3, accountdim4, accountdim5, accountdim6, calculatedamount6 amount, case when calcsign='-' then -1 else 1 end calcsignvalue, accountnoofcalcamount6 account, 6 calculatedamountfield, ((entrycode/1000)*100) + 6 textfield from pw001pyh where periodeused>202100 and periodeused < 202200 union all
select orgnumid, employer, pin, rank, periodeused, entrycode, accountdim2, accountdim3, accountdim4, accountdim5, accountdim6, calculatedamount7 amount, case when calcsign='-' then -1 else 1 end calcsignvalue, accountnoofcalcamount7 account, 7 calculatedamountfield, ((entrycode/1000)*100) + 7 textfield from pw001pyh where periodeused>202100 and periodeused < 202200 union all
select orgnumid, employer, pin, rank, periodeused, entrycode, accountdim2, accountdim3, accountdim4, accountdim5, accountdim6, calculatedamount8 amount, case when calcsign='-' then -1 else 1 end calcsignvalue, accountnoofcalcamount8 account, 8 calculatedamountfield, ((entrycode/1000)*100) + 8 textfield from pw001pyh where periodeused>202100 and periodeused < 202200 union all
select orgnumid, employer, pin, rank, periodeused, entrycode, accountdim2, accountdim3, accountdim4, accountdim5, accountdim6, calculatedamount9 amount, case when calcsign='-' then -1 else 1 end calcsignvalue, accountnoofcalcamount9 account, 9 calculatedamountfield, ((entrycode/1000)*100) + 9 textfield from pw001pyh where periodeused>202100 and periodeused < 202200 union all
select orgnumid, employer, pin, rank, periodeused, entrycode, accountdim2, accountdim3, accountdim4, accountdim5, accountdim6, calculatedamount10 amount, case when calcsign='-' then -1 else 1 end calcsignvalue, accountnoofcalcamount10 account, 10 calculatedamountfield, ((entrycode/1000)*100) + 10 textfield from pw001pyh where periodeused>202100 and periodeused < 202200 union all
select orgnumid, employer, pin, rank, periodeused, entrycode, accountdim2, accountdim3, accountdim4, accountdim5, accountdim6, calculatedamount11 amount, case when calcsign='-' then -1 else 1 end calcsignvalue, accountnoofcalcamount11 account, 11 calculatedamountfield, ((entrycode/1000)*100) + 11 textfield from pw001pyh where periodeused>202100 and periodeused < 202200 union all
select orgnumid, employer, pin, rank, periodeused, entrycode, accountdim2, accountdim3, accountdim4, accountdim5, accountdim6, calculatedamount1 amount, case when calcsign='-' then 1 else -1 end calcsignvalue, accountnoofcalcamountcredit1 account, 1 calculatedamountfield, ((entrycode/1000)*100) + 1 textfield from pw001pyh where periodeused>202100 and periodeused < 202200 union all
select orgnumid, employer, pin, rank, periodeused, entrycode, accountdim2, accountdim3, accountdim4, accountdim5, accountdim6, calculatedamount2 amount, case when calcsign='-' then 1 else -1 end calcsignvalue, accountnoofcalcamountcredit2 account, 2 calculatedamountfield, ((entrycode/1000)*100) + 2 textfield from pw001pyh where periodeused>202100 and periodeused < 202200 union all
select orgnumid, employer, pin, rank, periodeused, entrycode, accountdim2, accountdim3, accountdim4, accountdim5, accountdim6, calculatedamount3 amount, case when calcsign='-' then 1 else -1 end calcsignvalue, accountnoofcalcamountcredit3 account, 3 calculatedamountfield, ((entrycode/1000)*100) + 3 textfield from pw001pyh where periodeused>202100 and periodeused < 202200 union all
select orgnumid, employer, pin, rank, periodeused, entrycode, accountdim2, accountdim3, accountdim4, accountdim5, accountdim6, calculatedamount4 amount, case when calcsign='-' then 1 else -1 end calcsignvalue, accountnoofcalcamountcredit4 account, 4 calculatedamountfield, ((entrycode/1000)*100) + 4 textfield from pw001pyh where periodeused>202100 and periodeused < 202200 union all
select orgnumid, employer, pin, rank, periodeused, entrycode, accountdim2, accountdim3, accountdim4, accountdim5, accountdim6, calculatedamount5 amount, case when calcsign='-' then 1 else -1 end calcsignvalue, accountnoofcalcamountcredit5 account, 5 calculatedamountfield, ((entrycode/1000)*100) + 5 textfield from pw001pyh where periodeused>202100 and periodeused < 2016012 union all
select orgnumid, employer, pin, rank, periodeused, entrycode, accountdim2, accountdim3, accountdim4, accountdim5, accountdim6, calculatedamount6 amount, case when calcsign='-' then 1 else -1 end calcsignvalue, accountnoofcalcamountcredit6 account, 6 calculatedamountfield, ((entrycode/1000)*100) + 6 textfield from pw001pyh where periodeused>202100 and periodeused < 202200 union all
select orgnumid, employer, pin, rank, periodeused, entrycode, accountdim2, accountdim3, accountdim4, accountdim5, accountdim6, calculatedamount7 amount, case when calcsign='-' then 1 else -1 end calcsignvalue, accountnoofcalcamountcredit7 account, 7 calculatedamountfield, ((entrycode/1000)*100) + 7 textfield from pw001pyh where periodeused>202100 and periodeused < 202200 union all
select orgnumid, employer,pin, rank, periodeused, entrycode, accountdim2, accountdim3, accountdim4, accountdim5, accountdim6, calculatedamount8 amount, case when calcsign='-' then 1 else -1 end calcsignvalue, accountnoofcalcamountcredit8 account, 8 calculatedamountfield, ((entrycode/1000)*100) + 8 textfield from pw001pyh where periodeused>202100 and periodeused < 202200 union all
select orgnumid, employer, pin, rank, periodeused, entrycode, accountdim2, accountdim3, accountdim4, accountdim5, accountdim6, calculatedamount9 amount, case when calcsign='-' then 1 else -1 end calcsignvalue, accountnoofcalcamountcredit9 account, 9 calculatedamountfield, ((entrycode/1000)*100) + 9 textfield from pw001pyh where periodeused>202100 and periodeused < 202200 union all
select orgnumid, employer, pin, rank, periodeused, entrycode, accountdim2, accountdim3, accountdim4, accountdim5, accountdim6, calculatedamount10 amount, case when calcsign='-' then 1 else -1 end calcsignvalue, accountnoofcalcamountcredit10 account, 10 calculatedamountfield, ((entrycode/1000)*100) + 10 textfield from pw001pyh where periodeused>202100 and periodeused < 202200 union all
select orgnumid, employer, pin, rank, periodeused, entrycode, accountdim2, accountdim3, accountdim4, accountdim5, accountdim6, calculatedamount11 amount, case when calcsign='-' then 1 else -1 end calcsignvalue, accountnoofcalcamountcredit11 account, 11 calculatedamountfield, ((entrycode/1000)*100) + 11 textfield from pw001pyh where periodeused>202100 and periodeused < 202200
) pyh
left join pw001p01 p01 on p01.pin= pyh.pin
left join pyety001txt t on t.tid=0 and t.texttype='O' and pyh.calculatedamountfield=t.tnumber
left join py001acc da on pyh.account=da.accountno
left join pyety001 txt on pyh.entrycode=txt.entrycode
left join pworg e on pyh.employer=e.numorgid
left join pworg c on pyh.orgnumid=c.numorgid
left join pworg v on pyh.accountdim2=v.numorgid
left join pw001c02 c02 on pyh.rank=c02.code
left join pwedagdef ed on pyh.entrycode=ed.entrycode and ('L'+convert(varchar(3), (pyh.calculatedamountfield +60)) = ed.pfieldno)
where periodeused > 202100 and periodeused < 202200
and ((isnumeric(pyh.account)=1 and convert(integer, pyh.account) > 2999))

SELECT
P01.Pin ,
P01.Client Numorgid,
p01.Name,
P01.Employmentstartdate,
P01.Employmentenddate,
P01.Personalidno,
pyh.Periodeused as Period,
pyh.Entrycode as 'Entry Code',
c02.name as 'Rank',
txt.Text as 'Entry Code Name',
convert(varchar,pyh.Entrycode) + ' ' + txt.text as 'EC Number and Text',
ed.AGA as 'Employers Fee',
ed.ftrekk as 'Taxable',
pyh.Amount*Calcsignvalue as 'Amount',
pyh.Account,
da.Accounttext as 'Account Name',
pyh.Account + ' ' + da.Accounttext as 'Account Number and Name',
Calculatedamountfield as 'Count',
t.Ttext as 'EC Text',
v.Name as 'Vessel Name',
v.Orgcode + ' ' + v.Name as 'Vessel Code Name',
e.Name as 'Employer',
c.Name as 'Company'
from
(
select orgnumid, employer, pin, rank, periodeused, entrycode, accountdim2, accountdim3, accountdim4, accountdim5, accountdim6, calculatedamount1 amount, case when calcsign='-' then -1 else 1 end calcsignvalue, accountnoofcalcamount1 account, 1 calculatedamountfield, ((entrycode/1000)*100) + 1 textfield from pw001pyh where periodeused>202100 and periodeused < 202200 union all
select orgnumid, employer, pin, rank, periodeused, entrycode, accountdim2, accountdim3, accountdim4, accountdim5, accountdim6, calculatedamount2 amount, case when calcsign='-' then -1 else 1 end calcsignvalue, accountnoofcalcamount2 account, 2 calculatedamountfield, ((entrycode/1000)*100) + 2 textfield from pw001pyh where periodeused>202100 and periodeused < 202200 union all
select orgnumid, employer, pin, rank, periodeused, entrycode, accountdim2, accountdim3, accountdim4, accountdim5, accountdim6, calculatedamount3 amount, case when calcsign='-' then -1 else 1 end calcsignvalue, accountnoofcalcamount3 account, 3 calculatedamountfield, ((entrycode/1000)*100) + 3 textfield from pw001pyh where periodeused>202100 and periodeused < 202200 union all
select orgnumid, employer, pin, rank, periodeused, entrycode, accountdim2, accountdim3, accountdim4, accountdim5, accountdim6, calculatedamount4 amount, case when calcsign='-' then -1 else 1 end calcsignvalue, accountnoofcalcamount4 account, 4 calculatedamountfield, ((entrycode/1000)*100) + 4 textfield from pw001pyh where periodeused>202100 and periodeused < 202200 union all
select orgnumid, employer, pin, rank, periodeused, entrycode, accountdim2, accountdim3, accountdim4, accountdim5, accountdim6, calculatedamount5 amount, case when calcsign='-' then -1 else 1 end calcsignvalue, accountnoofcalcamount5 account, 5 calculatedamountfield, ((entrycode/1000)*100) + 5 textfield from pw001pyh where periodeused>202100 and periodeused < 202200 union all
select orgnumid, employer, pin, rank, periodeused, entrycode, accountdim2, accountdim3, accountdim4, accountdim5, accountdim6, calculatedamount6 amount, case when calcsign='-' then -1 else 1 end calcsignvalue, accountnoofcalcamount6 account, 6 calculatedamountfield, ((entrycode/1000)*100) + 6 textfield from pw001pyh where periodeused>202100 and periodeused < 202200 union all
select orgnumid, employer, pin, rank, periodeused, entrycode, accountdim2, accountdim3, accountdim4, accountdim5, accountdim6, calculatedamount7 amount, case when calcsign='-' then -1 else 1 end calcsignvalue, accountnoofcalcamount7 account, 7 calculatedamountfield, ((entrycode/1000)*100) + 7 textfield from pw001pyh where periodeused>202100 and periodeused < 202200 union all
select orgnumid, employer, pin, rank, periodeused, entrycode, accountdim2, accountdim3, accountdim4, accountdim5, accountdim6, calculatedamount8 amount, case when calcsign='-' then -1 else 1 end calcsignvalue, accountnoofcalcamount8 account, 8 calculatedamountfield, ((entrycode/1000)*100) + 8 textfield from pw001pyh where periodeused>202100 and periodeused < 202200 union all
select orgnumid, employer, pin, rank, periodeused, entrycode, accountdim2, accountdim3, accountdim4, accountdim5, accountdim6, calculatedamount9 amount, case when calcsign='-' then -1 else 1 end calcsignvalue, accountnoofcalcamount9 account, 9 calculatedamountfield, ((entrycode/1000)*100) + 9 textfield from pw001pyh where periodeused>202100 and periodeused < 202200 union all
select orgnumid, employer, pin, rank, periodeused, entrycode, accountdim2, accountdim3, accountdim4, accountdim5, accountdim6, calculatedamount10 amount, case when calcsign='-' then -1 else 1 end calcsignvalue, accountnoofcalcamount10 account, 10 calculatedamountfield, ((entrycode/1000)*100) + 10 textfield from pw001pyh where periodeused>202100 and periodeused < 202200 union all
select orgnumid, employer, pin, rank, periodeused, entrycode, accountdim2, accountdim3, accountdim4, accountdim5, accountdim6, calculatedamount11 amount, case when calcsign='-' then -1 else 1 end calcsignvalue, accountnoofcalcamount11 account, 11 calculatedamountfield, ((entrycode/1000)*100) + 11 textfield from pw001pyh where periodeused>202100 and periodeused < 202200 union all
select orgnumid, employer, pin, rank, periodeused, entrycode, accountdim2, accountdim3, accountdim4, accountdim5, accountdim6, calculatedamount1 amount, case when calcsign='-' then 1 else -1 end calcsignvalue, accountnoofcalcamountcredit1 account, 1 calculatedamountfield, ((entrycode/1000)*100) + 1 textfield from pw001pyh where periodeused>202100 and periodeused < 202200 union all
select orgnumid, employer, pin, rank, periodeused, entrycode, accountdim2, accountdim3, accountdim4, accountdim5, accountdim6, calculatedamount2 amount, case when calcsign='-' then 1 else -1 end calcsignvalue, accountnoofcalcamountcredit2 account, 2 calculatedamountfield, ((entrycode/1000)*100) + 2 textfield from pw001pyh where periodeused>202100 and periodeused < 202200 union all
select orgnumid, employer, pin, rank, periodeused, entrycode, accountdim2, accountdim3, accountdim4, accountdim5, accountdim6, calculatedamount3 amount, case when calcsign='-' then 1 else -1 end calcsignvalue, accountnoofcalcamountcredit3 account, 3 calculatedamountfield, ((entrycode/1000)*100) + 3 textfield from pw001pyh where periodeused>202100 and periodeused < 202200 union all
select orgnumid, employer, pin, rank, periodeused, entrycode, accountdim2, accountdim3, accountdim4, accountdim5, accountdim6, calculatedamount4 amount, case when calcsign='-' then 1 else -1 end calcsignvalue, accountnoofcalcamountcredit4 account, 4 calculatedamountfield, ((entrycode/1000)*100) + 4 textfield from pw001pyh where periodeused>202100 and periodeused < 202200 union all
select orgnumid, employer, pin, rank, periodeused, entrycode, accountdim2, accountdim3, accountdim4, accountdim5, accountdim6, calculatedamount5 amount, case when calcsign='-' then 1 else -1 end calcsignvalue, accountnoofcalcamountcredit5 account, 5 calculatedamountfield, ((entrycode/1000)*100) + 5 textfield from pw001pyh where periodeused>202100 and periodeused < 2016012 union all
select orgnumid, employer, pin, rank, periodeused, entrycode, accountdim2, accountdim3, accountdim4, accountdim5, accountdim6, calculatedamount6 amount, case when calcsign='-' then 1 else -1 end calcsignvalue, accountnoofcalcamountcredit6 account, 6 calculatedamountfield, ((entrycode/1000)*100) + 6 textfield from pw001pyh where periodeused>202100 and periodeused < 202200 union all
select orgnumid, employer, pin, rank, periodeused, entrycode, accountdim2, accountdim3, accountdim4, accountdim5, accountdim6, calculatedamount7 amount, case when calcsign='-' then 1 else -1 end calcsignvalue, accountnoofcalcamountcredit7 account, 7 calculatedamountfield, ((entrycode/1000)*100) + 7 textfield from pw001pyh where periodeused>202100 and periodeused < 202200 union all
select orgnumid, employer,pin, rank, periodeused, entrycode, accountdim2, accountdim3, accountdim4, accountdim5, accountdim6, calculatedamount8 amount, case when calcsign='-' then 1 else -1 end calcsignvalue, accountnoofcalcamountcredit8 account, 8 calculatedamountfield, ((entrycode/1000)*100) + 8 textfield from pw001pyh where periodeused>202100 and periodeused < 202200 union all
select orgnumid, employer, pin, rank, periodeused, entrycode, accountdim2, accountdim3, accountdim4, accountdim5, accountdim6, calculatedamount9 amount, case when calcsign='-' then 1 else -1 end calcsignvalue, accountnoofcalcamountcredit9 account, 9 calculatedamountfield, ((entrycode/1000)*100) + 9 textfield from pw001pyh where periodeused>202100 and periodeused < 202200 union all
select orgnumid, employer, pin, rank, periodeused, entrycode, accountdim2, accountdim3, accountdim4, accountdim5, accountdim6, calculatedamount10 amount, case when calcsign='-' then 1 else -1 end calcsignvalue, accountnoofcalcamountcredit10 account, 10 calculatedamountfield, ((entrycode/1000)*100) + 10 textfield from pw001pyh where periodeused>202100 and periodeused < 202200 union all
select orgnumid, employer, pin, rank, periodeused, entrycode, accountdim2, accountdim3, accountdim4, accountdim5, accountdim6, calculatedamount11 amount, case when calcsign='-' then 1 else -1 end calcsignvalue, accountnoofcalcamountcredit11 account, 11 calculatedamountfield, ((entrycode/1000)*100) + 11 textfield from pw001pyh where periodeused>202100 and periodeused < 202200
) pyh
left join pw001p01 p01 on p01.pin= pyh.pin
left join pyety001txt t on t.tid=0 and t.texttype='O' and pyh.calculatedamountfield=t.tnumber
left join py001acc da on pyh.account=da.accountno
left join pyety001 txt on pyh.entrycode=txt.entrycode
left join pworg e on pyh.employer=e.numorgid
left join pworg c on pyh.orgnumid=c.numorgid
left join pworg v on pyh.accountdim2=v.numorgid
left join pw001c02 c02 on pyh.rank=c02.code
left join pwedagdef ed on pyh.entrycode=ed.entrycode and ('L'+convert(varchar(3), (pyh.calculatedamountfield +60)) = ed.pfieldno)
where periodeused > 202100 and periodeused < 202200
and ((isnumeric(pyh.account)=1 and convert(integer, pyh.account) < 3000))

Other