Versions Compared

Version Old Version 1 New Version 2
Changes made by

Sigrid Kviteberg

Sigrid Kviteberg

Saved on

Key

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

...

Description

This view extract selected data from historical payroll periods

Summary

This view is created specifically for Norwegian customers that has Employers Fee, Leave Pay and Pension calculated.

It can be modified to change the name of the fields or to remove fields not needed

Keywords

File / Script Link

Expand
titleSQL Statement

SELECT
P.PIN,
1 AS COUNT,
P.NAME,
PAY.PERIODEUSED as 'PERIOD',
PAY.ENTRYCODE 'EC NUMBER',
CONVERT(VARCHAR,pay.ENTRYCODE)+' '+EC.TEXT AS 'ENTRY CODE',
PAY.CALCULATEDAMOUNT1 AS AMOUNT,
PAY.CALCULATEDAMOUNT2 as 'EMPLOYERS FEE',
PAY.CALCULATEDAMOUNT3 as 'LEAVE PAY',
PAY.CALCULATEDAMOUNT4 as 'EMP. FEE LP',
PAY.CALCULATEDAMOUNT6 as 'PENSION FEE',
Pay.PAID,
PAY.DATE_OF_PAYMENT,
PAY.TEXT,
convert(float, case when entryfield2='' then '0' else replace(entryfield2, ',', '.') end) as QUANTITY,
ORG.NAME AS VESSEL,
P.CLIENT NUMORGID,
P.EMPLOYMENTSTARTDATE,
P.EMPLOYMENTENDDATE,
CMP.NAME as COMPANY,
EMP.NAME as EMPOLOYER,
CURCMP.NAME as 'CURRENT COMPANY LINK'

FROM PW001P01 P
LEFT JOIN PW001PYH PAY ON PAY.PIN=P.PIN
LEFT JOIN PWORG ORG ON ORG.NUMORGID=PAY.ACCOUNTDIM2
LEFT JOIN PWORG CMP ON CMP.NUMORGID=pAY.ORGNUMID
LEFT JOIN PWORG EMP ON EMP.NUMORGID=pAY.EMPLOYER
LEFT JOIN PWORG CURCMP ON CURCMP.NUMORGID=p.CLIENT
LEFT JOIN PYETY001 EC on EC.ENTRYCODE=PAY.ENTRYCODE
WHERE PAY.PERIODEUSED>202100 AND PAY.PERIODEUSED<202200

Compatibility APM Version

Compatibility SQL Version

...

SELECT
P.PIN,
1 AS COUNT,
P.NAME,
PAY.PERIODEUSED as 'PERIOD',
PAY.ENTRYCODE 'EC NUMBER',
CONVERT(VARCHAR,pay.ENTRYCODE)+' '+EC.TEXT AS 'ENTRY CODE',
PAY.CALCULATEDAMOUNT1 AS AMOUNT,
PAY.CALCULATEDAMOUNT2 as 'EMPLOYERS FEE',
PAY.CALCULATEDAMOUNT3 as 'LEAVE PAY',
PAY.CALCULATEDAMOUNT4 as 'EMP. FEE LP',
PAY.CALCULATEDAMOUNT6 as 'PENSION FEE',
Pay.PAID,
PAY.DATE_OF_PAYMENT,
PAY.TEXT,
convert(float, case when entryfield2='' then '0' else replace(entryfield2, ',', '.') end) as QUANTITY,
ORG.NAME AS VESSEL,
P.CLIENT NUMORGID,
P.EMPLOYMENTSTARTDATE,
P.EMPLOYMENTENDDATE,
CMP.NAME as COMPANY,
EMP.NAME as EMPOLOYEREMPLOYER,
CURCMP.NAME as 'CURRENT COMPANY LINK'

...