...
Description | A-melding vs. A-melding Reconsiliation |
|---|
Summary | This view can be used to locate PINs causing a difference between what is reported in A-melding, and what is appearing in the Accounting Voucher |
|---|
Keywords | Payroll, A-melding, Accounting Voucher, Reconsiliation, Norwegian Payroll |
|---|
File / Script Link | |
|---|
Compatibility APM Version | |
|---|
Compatibility SQL Version | |
|---|
...
Selection
| Expand |
|---|
|
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.calculatedamount2100/14.1)/1.033
when pyh.calculatedamount6<>0 and pyh.entrycode in (163,168) THEN (pyh.calculatedamount2100/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',
P01.Client Numorgid,
P01.Employmentstartdate,
P01.Employmentenddate
from pw001pyh pyh
left join pw001p01 p01 on p01.pin= pyh.pin
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 |
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.
...
Other