The view shows persons having net payment entry code 994 in different periods. The amounts are split by payment currencies.
Keywords
Payroll
Category
Analytical View
Description
The view shows persons having net payment entry code 994 in different periods. The amounts are split by payment currencies.
View Sample
Main Data Selection
All crews having entry code 994 in all payroll periods (current/historical).
SQL 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',
--CONVERT(VARCHAR, PAY.CALCULATEDAMOUNT1) AS AMOUNT,
pay.CURRENCYFORPAYMENT,
CASE
WHEN pay.CURRENCYFORPAYMENT = 'NOK' THEN CALCULATEDAMOUNT1
ELSE dbo.ad_StrToFloat(ENTRYFIELD1) * CURRENCYRATEFORPAYMENT
END AS AMOUNT,
PAY.CALCULATEDAMOUNT2 AS 'EMPLOYERS FEE',
PAY.CALCULATEDAMOUNT3 AS 'LEAVE PAY',
PAY.CALCULATEDAMOUNT4 AS 'EMP. FEE LP',
PAY.CALCULATEDAMOUNT6 AS 'PENSION FEE',
PAY.CURRENCYCODECALC AS 'PAYROLL CURRENCY',
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 EMPLOYER,
CURCMP.NAME AS 'CURRENT COMPANY LINK'
FROM PW001P01 P
LEFT JOIN
(
SELECT pay.PIN,
pay.ACCOUNTDIM2,
pay.ORGNUMID,
pay.EMPLOYER,
pay.ENTRYCODE,
pay.CURRENCYFORPAYMENT,
pay.CALCULATEDAMOUNT1,
pay.ENTRYFIELD1,
pay.CURRENCYRATEFORPAYMENT,
pay.CALCULATEDAMOUNT2,
pay.PERIODEUSED,
pay.CALCULATEDAMOUNT3,
pay.CALCULATEDAMOUNT4,
pay.CALCULATEDAMOUNT6,
pay.CURRENCYCODECALC,
pay.PAID,
pay.[TEXT],
pay.ENTRYFIELD2,
pay.DATE_OF_PAYMENT
FROM PW001PAY AS pay
UNION ALL
SELECT pyh.PIN,
pyh.ACCOUNTDIM2,
pyh.ORGNUMID,
pyh.EMPLOYER,
pyh.ENTRYCODE,
pyh.CURRENCYFORPAYMENT,
pyh.CALCULATEDAMOUNT1,
pyh.ENTRYFIELD1,
pyh.CURRENCYRATEFORPAYMENT,
pyh.CALCULATEDAMOUNT2,
pyh.PERIODEUSED,
pyh.CALCULATEDAMOUNT3,
pyh.CALCULATEDAMOUNT4,
pyh.CALCULATEDAMOUNT6,
pyh.CURRENCYCODECALC,
pyh.PAID,
pyh.[TEXT],
pyh.ENTRYFIELD2,
pyh.DATE_OF_PAYMENT
FROM PW001PYH AS pyh
)
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.ENTRYCODE = 994
Rows/ Columns Specification
Row/Column
Description/ Location in APM
Row/Column
Description/ Location in APM
Period
Payroll period.
Company
Crew member’s employment organization set in Personal Details.
