Summary	A-melding vs. A-melding Reconsiliation
Keywords	Payroll, A-melding, Accounting Voucher, Reconsiliation, Norwegian Payroll
Compatibility APM Version
Compatibility SQL Version

Description

This view will fetch all payroll transactions from specified historical periods that have Employers Fee calculated, or that have a setting for being included in the Employers Fee basis in A-melding/EDAG.

Selection

SQL Statement

Select
p01.Pin,
p01.Name,
z.Company,
z.Employer,
z.Vessel as 'Vessel name',
z.EC as 'EC Number and Text',
z.[Period] as 'Period',
z.Basis,
z.[Basis from A-melding],
z.Basis-z.[Basis from A-melding] as 'A-melding diff',
z.[Basis from A-melding included],
P01.Client Numorgid,
P01.Employmentstartdate,
P01.Employmentenddate
From
(SELECT
pyh.Pin ,
c.Name as 'Company',
e.Name as 'Employer',
v.Name as 'Vessel',
pyh.Periodeused as 'Period',
convert(varchar,pyh.Entrycode) + ' ' + txt.text as 'EC',
case when pyh.calculatedamount6<>0 and pyh.entrycode not in (163,168) THEN (pyh.calculatedamount2*100/14.1)/1.033
when pyh.calculatedamount6<>0 and pyh.entrycode in (163,168) THEN (pyh.calculatedamount2*100/14.1)
when pyh.calculatedamount6=0 then (pyh.calculatedamount2*100/14.1)
end as 'Basis',
case when pyh.entrycode in (select ENTRYCODE from PWEDAGDEF where PFIELDNO='L61' and AGA = 'Y')
then pyh.CALCULATEDAMOUNT1
else 0.00
end +
case when pyh.entrycode in (select ENTRYCODE from PWEDAGDEF where PFIELDNO='L66' and AGA = 'Y')
then pyh.CALCULATEDAMOUNT6
else 0.00
end +
case when pyh.entrycode in (select ENTRYCODE from PWEDAGDEF where PFIELDNO='L67' and AGA = 'Y')
then pyh.CALCULATEDAMOUNT7
else 0.00
end +
case when pyh.entrycode in (select ENTRYCODE from PWEDAGDEF where PFIELDNO='L68' and AGA = 'Y')
then pyh.CALCULATEDAMOUNT8
else 0.00
end +
case when pyh.entrycode in (select ENTRYCODE from PWEDAGDEF where PFIELDNO='L69' and AGA = 'Y')
then pyh.CALCULATEDAMOUNT9
else 0.00
end +
case when pyh.entrycode in (select ENTRYCODE from PWEDAGDEF where PFIELDNO='L70' and AGA = 'Y')
then pyh.CALCULATEDAMOUNT10
else 0.00
end +
case when pyh.entrycode in (select ENTRYCODE from PWEDAGDEF where PFIELDNO='L71' and AGA = 'Y')
then pyh.CALCULATEDAMOUNT11
else 0.00
end
as 'Basis from A-melding',
case when pyh.entrycode in (select ENTRYCODE from PWEDAGDEF where PFIELDNO='L61' and AGA = 'Y') and pyh.CALCULATEDAMOUNT1 != 0.00
then 'L61 '
else ''
end +
case when pyh.entrycode in (select ENTRYCODE from PWEDAGDEF where PFIELDNO='L66' and AGA = 'Y') and pyh.CALCULATEDAMOUNT6 != 0.00
then 'L66 '
else ''
end +
case when pyh.entrycode in (select ENTRYCODE from PWEDAGDEF where PFIELDNO='L67' and AGA = 'Y') and pyh.CALCULATEDAMOUNT7 != 0.00
then 'L67 '
else ''
end +
case when pyh.entrycode in (select ENTRYCODE from PWEDAGDEF where PFIELDNO='L68' and AGA = 'Y') and pyh.CALCULATEDAMOUNT8 != 0.00
then 'L68 '
else ''
end +
case when pyh.entrycode in (select ENTRYCODE from PWEDAGDEF where PFIELDNO='L69' and AGA = 'Y') and pyh.CALCULATEDAMOUNT9 != 0.00
then 'L69 '
else ''
end +
case when pyh.entrycode in (select ENTRYCODE from PWEDAGDEF where PFIELDNO='L70' and AGA = 'Y') and pyh.CALCULATEDAMOUNT10 != 0.00
then 'L70 '
else ''
end +
case when pyh.entrycode in (select ENTRYCODE from PWEDAGDEF where PFIELDNO='L71' and AGA = 'Y') and pyh.CALCULATEDAMOUNT11 != 0.00
then 'L71'
else ''
end
as 'Basis from A-melding included'
from pw001pyh pyh
left join pyety001 txt on pyh.entrycode=txt.entrycode
left join pworg c on pyh.orgnumid=c.numorgid
left join pworg e on pyh.employer=e.numorgid
left join pworg v on pyh.accountdim2=v.numorgid
left join pw001c02 c02 on pyh.rank=c02.code
--left join pw001pyh aga on aga.pin = pyh.pin and aga.periodeused = pyh.PERIODEUSED and aga.entrycode in (select ENTRYCODE from PWEDAGDEF where PFIELDNO='L61' and AGA = 'Y')
where pyh.entrycode not in (146) and pyh.calculatedamount2<>0
) Z
left join pw001p01 p01 on p01.pin= z.pin

SQL with check for year 2021 only

Select

p01.Pin,

p01.Name,

z.Company,

z.Employer,

z.Vessel as 'Vessel name',

z.EC as 'EC Number and Text',

z.[Period] as 'Period',

z.Basis,

z.[Basis from A-melding],

z.Basis-z.[Basis from A-melding] as 'A-melding diff',

z.[Basis from A-melding included],

P01.Client Numorgid,

P01.Employmentstartdate,

P01.Employmentenddate

From

(SELECT

pyh.Pin ,

c.Name as 'Company',

e.Name as 'Employer',

v.Name as 'Vessel',

pyh.Periodeused as 'Period',

convert(varchar,pyh.Entrycode) + ' ' + txt.text as 'EC',

case when pyh.calculatedamount6<>0 and pyh.entrycode not in (163,168) THEN (pyh.calculatedamount2*100/14.1)/1.033

when pyh.calculatedamount6<>0 and pyh.entrycode in (163,168) THEN (pyh.calculatedamount2*100/14.1)

when pyh.calculatedamount6=0 then (pyh.calculatedamount2*100/14.1)

end as 'Basis',

case when pyh.entrycode in (select ENTRYCODE from PWEDAGDEF where PFIELDNO='L61' and AGA = 'Y')

then pyh.CALCULATEDAMOUNT1

else 0.00

end +

case when pyh.entrycode in (select ENTRYCODE from PWEDAGDEF where PFIELDNO='L66' and AGA = 'Y')

then pyh.CALCULATEDAMOUNT6

else 0.00

end +

case when pyh.entrycode in (select ENTRYCODE from PWEDAGDEF where PFIELDNO='L67' and AGA = 'Y')

then pyh.CALCULATEDAMOUNT7

else 0.00

end +

case when pyh.entrycode in (select ENTRYCODE from PWEDAGDEF where PFIELDNO='L68' and AGA = 'Y')

then pyh.CALCULATEDAMOUNT8

else 0.00

end +

case when pyh.entrycode in (select ENTRYCODE from PWEDAGDEF where PFIELDNO='L69' and AGA = 'Y')

then pyh.CALCULATEDAMOUNT9

else 0.00

end +

case when pyh.entrycode in (select ENTRYCODE from PWEDAGDEF where PFIELDNO='L70' and AGA = 'Y')

then pyh.CALCULATEDAMOUNT10

else 0.00

end +

case when pyh.entrycode in (select ENTRYCODE from PWEDAGDEF where PFIELDNO='L71' and AGA = 'Y')

then pyh.CALCULATEDAMOUNT11

else 0.00

end

as 'Basis from A-melding',

case when pyh.entrycode in (select ENTRYCODE from PWEDAGDEF where PFIELDNO='L61' and AGA = 'Y') and pyh.CALCULATEDAMOUNT1 != 0.00

then 'L61 '

else ''

end +

case when pyh.entrycode in (select ENTRYCODE from PWEDAGDEF where PFIELDNO='L66' and AGA = 'Y') and pyh.CALCULATEDAMOUNT6 != 0.00

then 'L66 '

else ''

end +

case when pyh.entrycode in (select ENTRYCODE from PWEDAGDEF where PFIELDNO='L67' and AGA = 'Y') and pyh.CALCULATEDAMOUNT7 != 0.00

then 'L67 '

else ''

end +

case when pyh.entrycode in (select ENTRYCODE from PWEDAGDEF where PFIELDNO='L68' and AGA = 'Y') and pyh.CALCULATEDAMOUNT8 != 0.00

then 'L68 '

else ''

end +

case when pyh.entrycode in (select ENTRYCODE from PWEDAGDEF where PFIELDNO='L69' and AGA = 'Y') and pyh.CALCULATEDAMOUNT9 != 0.00

then 'L69 '

else ''

end +

case when pyh.entrycode in (select ENTRYCODE from PWEDAGDEF where PFIELDNO='L70' and AGA = 'Y') and pyh.CALCULATEDAMOUNT10 != 0.00

then 'L70 '

else ''

end +

case when pyh.entrycode in (select ENTRYCODE from PWEDAGDEF where PFIELDNO='L71' and AGA = 'Y') and pyh.CALCULATEDAMOUNT11 != 0.00

then 'L71'

else ''

end

as 'Basis from A-melding included'

from pw001pyh pyh

left join pyety001 txt on pyh.entrycode=txt.entrycode

left join pworg c on pyh.orgnumid=c.numorgid

left join pworg e on pyh.employer=e.numorgid

left join pworg v on pyh.accountdim2=v.numorgid

left join pw001c02 c02 on pyh.rank=c02.code

where pyh.entrycode not in (146) and pyh.calculatedamount2<>0 and pyh.PERIODEUSED between 202100 and 202200) Z

left join pw001p01 p01 on p01.pin= z.pin

Field Specification

The easiest way to find the differences is to drop the following fields into the following areas:

Drop the fields “Basis”, “Basis from A-melding” and “A-melding diff” into the “Drop Data fields here”.
Drop the field “Period”, “Company”, “EC Number and Text” and “PIN” into the “Drop Column fields here”

Afterward, you can use the “A-melding diff” field and filter away all 0 amounts so that you are left with only the amounts causing a difference.

You can also filter away any entry codes, like Svalbard entry codes since the employer fee there is 0 there and if not they will come up with a difference.

A-melding vs. A-melding Reconsiliation

Description

Selection

Field Specification

Other