Versions Compared

Version Old Version 1 New Version Current
Changes made by

Sigrid Kviteberg

Daria Chebotaieva

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Table of Contents
minLevel1
maxLevel1
typeflat
separatorpipe
Page Properties

Summary

A-melding vs. A-melding Reconsiliation

...

Summary

...

Keywords

...

payroll,

...

a-melding,

...

File / Script Link

...

Compatibility APM Version

...

reconsiliation, accounting voucher

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

Expand
titleSQL Statement

SELECTSelect
P01p01.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 Name',
pyh.Periodeused as 'Period',
convert(varchar,pyh.Entrycode) + ' ' + txt.text as 'EC Number and Text',
case when pyh.entrycode>349 and pyh.entrycode<400 THEN 0.00
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' and skatteogavgiftsregel<>'svalbard')
then pyh.CALCULATEDAMOUNT1
else 0.00
end +
case when pyh.entrycode in (select ENTRYCODE from PWEDAGDEF where PFIELDNO='L66' and AGA = 'Y' and skatteogavgiftsregel<>'svalbard')
then pyh.CALCULATEDAMOUNT6
else 0.00
end +
case when pyh.entrycode in (select ENTRYCODE from PWEDAGDEF where PFIELDNO='L67' and AGA = 'Y' and skatteogavgiftsregel<>'svalbard')
then pyh.CALCULATEDAMOUNT7
else 0.00
end +
case when pyh.entrycode in (select ENTRYCODE from PWEDAGDEF where PFIELDNO='L68' and AGA = 'Y' and skatteogavgiftsregel<>'svalbard')
then pyh.CALCULATEDAMOUNT8
else 0.00
end +
case when pyh.entrycode in (select ENTRYCODE from PWEDAGDEF where PFIELDNO='L69' and AGA = 'Y' and skatteogavgiftsregel<>'svalbard')
then pyh.CALCULATEDAMOUNT9
else 0.00
end +
case when pyh.entrycode in (select ENTRYCODE from PWEDAGDEF where PFIELDNO='L70' and AGA = 'Y' and skatteogavgiftsregel<>'svalbard')
then pyh.CALCULATEDAMOUNT10
else 0.00
end +
case when pyh.entrycode in (select ENTRYCODE from PWEDAGDEF where PFIELDNO='L71' and AGA = 'Y' and skatteogavgiftsregel<>'svalbard')
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 skatteogavgiftsregel<>'svalbard') 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 skatteogavgiftsregel<>'svalbard') 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 skatteogavgiftsregel<>'svalbard') 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 skatteogavgiftsregel<>'svalbard') 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 skatteogavgiftsregel<>'svalbard') 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 skatteogavgiftsregel<>'svalbard') 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 skatteogavgiftsregel<>'svalbard') 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

Expand
titleSQL 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')

from pw001pyh pyh
left join pw001p01 p01 on p01.pin= pyh.pinthen 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:

  1. Drop the fields “Basis”, “Basis from A-melding” and “A-melding diff” into the “Drop Data fields here”.

  2. 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.

...

Other