Summary	The view shows all the crew that are active/working this month and are supposed to receive payments according to the activity and contract.
Keywords	Payroll, Payments
Category	Crew List View

Description

The view shows all the crew that are active/working this month and are supposed to receive payments according to the activity and contract (from Contracts Datagroup only).

The view is used for various purposes:

to locate the active/working crew and check their payroll;
to locate possible problems with crew data, as it has a column called “Issues“ that considers possible user mistakes that our customers make from time to time;
to check contract and activity details on the active/working crew;
to check the payment details on the active/working crew;
to check if the payslip output is activated on all the crew and use Common Entry to activate it for the newcomers;
to check if the payslips were successfully distributed for all the crew this month before the payroll period closure;
etc.

Usually, there should be only one line per person. But it will be split if the person has several activities or contracts within the current period.

View Sample

Main Data Selection

The view shows all the crew that are active/working this month and are supposed to receive payments according to the activity and contract.

SQL statement

CREATE VIEW [dbo].[PW001SRV202]
AS

SELECT  main.PIN,
       main.EMPLOYMENTSTARTDATE,
       main.EMPLOYMENTENDDATE,
       main.NUMORGID,
       main.[PAYSLIP STATUS],
       main.[PAYMENT PERIOD],
       main.[PERIOD START],
       main.[PERIOD END],
       main.NAME,
       main.ORGANIZATION,
       main.[RANK],
       main.[RANK CODE],
       main.[NATIONALITY CODE],
       main.ACTIVITY,
       main.[ACTIVITY START],
       main.[ACTIVITY END],
       main.[ACTIVITY ESTIMATED END],
       main.[ACTIVITY DAYS],
       main.[ACTIVITY VESSEL],
       main.[ACTIVITY DEPARTMENT],
       main.[ACTIVITY POSITION],
       main.[ACTIVITY PAYSCALE TABLE],
       main.[ACTIVITY PAYSCALE CODE],
       --main.[Row#],
       main.[Contract Type],
       main.[Contract Name],
       main.[Contract Rank],
       main.[Contract Start],
       main.[Contract End],
       main.[CONTRACT PAYSCALE TABLE],
       main.[CONTRACT PAYSCALE],
       main.[CONTRACT PAYSCALE TABLE NAME],
       main.[CONTRACT PAYSCALE NAME],
       main.[CALCULATED BY],
       main.[CALCULATE TIME],
       main.[PAYMENT METHOD],
       main.[PAYMENT CURRENCY],
       --main.[DISTRIBURE PAYSLIP OPTION] as [DISTRIBUTE PAYSLIP OPTION],
       main.EMAIL,
       ISNULL(
           STUFF(
               ISNULL(NULLIF(', ' + main.issue1, ', '), '') +
               ISNULL(NULLIF(', ' + main.issue2, ', '), '') +
               ISNULL(NULLIF(', ' + main.issue3, ', '), '') +
               ISNULL(NULLIF(', ' + main.issue4, ', '), '') +
               ISNULL(NULLIF(', ' + main.issue5, ', '), '') +
               ISNULL(NULLIF(', ' + main.issue6, ', '), '') +
               ISNULL(NULLIF(', ' + main.issue7, ', '), '') +
               ISNULL(NULLIF(', ' + main.issue8, ', '), '') +
               ISNULL(NULLIF(', ' + main.issue9, ', '), ''),
               1,
               1,
               ''
           ),
           ''
       )  AS [ISSUES],
       main.[Department/Cost Place Code],
       main.[Department/Cost Place]
FROM   (
           SELECT p.PIN,
                  p.EMPLOYMENTSTARTDATE,
                  p.EMPLOYMENTENDDATE,
                  p.CLIENT        NUMORGID,
                  CASE 
                       WHEN PayslipStatus.PAYSLIPSTATUS = 'Y' THEN 'Distributed'
                       ELSE       'Not Distributed'
                  END 'PAYSLIP STATUS',
                  company.PERIODE 'PAYMENT PERIOD',
                  periode.PStart 'PERIOD START',
                  periode.PEnd 'PERIOD END',
                  p.NAME,
                  p.COSTPLACE  AS 'Department/Cost Place Code',
                  c43.NAME     AS 'Department/Cost Place',
                  ORGAN.NAME 'ORGANIZATION',
                  c02.NAME 'RANK',
                  dbo.ad_orgPosC02Code(
                      (
                          SELECT ORGCODE
                          FROM   pworg
                          WHERE  orgtype = 5
                                 AND numorgid = a.NUMORGID
                      )
                  )'RANK CODE',
                  p.NATIONALITY 'NATIONALITY CODE',
                  (
                      SELECT TEXT
                      FROM   pw001c12
                      WHERE  code = a.code
                  ) 'ACTIVITY',
                  a.DATEFROM 'ACTIVITY START',
                  a.DATETO 'ACTIVITY END',
                  a.TODATEESTIMATED 'ACTIVITY ESTIMATED END',
                  DATEDIFF(
                      DAY,
                      CASE 
                           WHEN a.DATEFROM > periode.PStart THEN a.DATEFROM
                           ELSE periode.PStart
                      END,
                      CASE 
                           WHEN ISNULL(a.DATETO, a.TODATEESTIMATED) < periode.PEnd THEN ISNULL(a.DATETO, a.TODATEESTIMATED)
                           ELSE periode.PEnd
                      END
                  ) + 1        AS 'ACTIVITY DAYS',
                  (
                      SELECT NAME
                      FROM   pworg
                      WHERE  NUMORGID = a.VESSEL
                  ) 'ACTIVITY VESSEL',
                  (
                      SELECT NAME
                      FROM   pworg
                      WHERE  orgtype = 4
                             AND numorgid = (
                                     SELECT NUMORGIDABOVE
                                     FROM   pworg
                                     WHERE  orgtype = 5
                                            AND numorgid = a.NUMORGID
                                 )
                  ) 'ACTIVITY DEPARTMENT',
                  (
                      SELECT NAME
                      FROM   pworg
                      WHERE  orgtype = 5
                             AND numorgid = a.NUMORGID
                  ) 'ACTIVITY POSITION',
                  a.PAYSCALETABLE 'ACTIVITY PAYSCALE TABLE',
                  a.PAYSCALE 'ACTIVITY PAYSCALE CODE',
                  --       p0p.PAYSCALETABLE 'PD PAYSCALE TABLE',
                  --       p0p.PAYSCALECODE 'PD PAYSCALE',
                  ROW_NUMBER() OVER(
                      PARTITION BY p20.PIN ORDER BY p20.PIN,
                      p20.CONTRACTKIND DESC
                  )            AS ROW#,
                  CASE 
                       WHEN p20.CONTRACTKIND = 0 THEN 'Main Contract'
                       WHEN p20.CONTRACTKIND = 1 THEN 'Sub Contract'
                       WHEN p20.CONTRACTKIND = 2 THEN 'Ammendment'
                       ELSE ''
                  END          AS 'Contract Type',
                  C32CONT.Text 'Contract Name',
                  C02CONT.NAME 'Contract Rank',
                  p20.DATESTART 'Contract Start',
                  p20.DATEEND 'Contract End',
                  p20.PAYSCALETABLE 'CONTRACT PAYSCALE TABLE',
                  p20.PAYSCALECODE 'CONTRACT PAYSCALE',
                  payscale.Table_Name 'CONTRACT PAYSCALE TABLE NAME',
                  payscale.Payscale_Name 'CONTRACT PAYSCALE NAME',
                  pay.CALCULATEDBY 'CALCULATED BY',
                  pay.CalculateTime 'CALCULATE TIME',
                  CASE 
                       WHEN P.Transferbalance = '0' THEN 'Carry Forward To Next Month'
                       WHEN P.Transferbalance = '1' THEN 'Transfer To Bank Account'
                       WHEN P.Transferbalance = '2' THEN 'Interpay'
                       WHEN P.Transferbalance = '3' THEN 'Alpha Credit Bank'
                       WHEN P.Transferbalance = '4' THEN 'Cash'
                       WHEN P.Transferbalance = '5' THEN 'Citibank Direct Deposit'
                       WHEN P.Transferbalance = '6' THEN 'Citibank'
                       WHEN P.Transferbalance = '7' THEN 'Chase Manhattan'
                       WHEN P.Transferbalance = '8' THEN 'Hellenic Bank'
                       WHEN P.Transferbalance = '9' THEN 'Isabel Domestic'
                       WHEN P.Transferbalance = 'A' THEN 'Isabel International'
                       WHEN P.Transferbalance = 'B' THEN 'Bank Transfer'
                       WHEN P.Transferbalance = 'C' THEN 'Swedish Domestic'
                       WHEN P.Transferbalance = 'D' THEN 'Swedish International'
                       WHEN P.Transferbalance = 'E' THEN 'Philippine Banking'
                       WHEN P.Transferbalance = 'F' THEN 'Manual Bank'
                       WHEN P.Transferbalance = 'G' THEN 'Zagrebancka'
                       WHEN P.Transferbalance = 'H' THEN 'ING Bank'
                       WHEN P.Transferbalance = 'I' THEN 'Direct Deposit E-Monee'
                       WHEN P.Transferbalance = 'J' THEN 'Deutsche Bank'
                       WHEN P.Transferbalance = 'K' THEN 'SACS'
                       WHEN P.Transferbalance = 'L' THEN 'Hellenic Cyprus'
                       WHEN P.Transferbalance = 'M' THEN 'MT 100'
                       WHEN P.Transferbalance = 'N' THEN 'Citibank (PL)'
                       WHEN P.Transferbalance = '0' THEN 'Agent .R'
                       WHEN P.Transferbalance = 'P' THEN 'German Bank'
                       WHEN P.Transferbalance = 'Q' THEN 'Trident Trust'
                       WHEN P.Transferbalance = 'R' THEN 'Citibank (Asia)'
                       WHEN P.Transferbalance = 'S' THEN 'PNC Bank'
                       WHEN P.Transferbalance = 'T' THEN 'Chase Insight'
                       WHEN P.Transferbalance = 'U' THEN 'Ocean Pay'
                       WHEN P.Transferbalance = 'V' THEN 'Banco de Oro'
                       WHEN P.Transferbalance = 'W' THEN 'Bank of Philippine Island'
                       WHEN P.Transferbalance = 'X' THEN 'CitiDirect (Onboard)'
                       WHEN P.Transferbalance = 'Y' THEN 'Metrobank Direct (PH)'
                       WHEN P.Transferbalance = 'Z' THEN 'Elektron'
                       WHEN P.Transferbalance = 'A1' THEN 'E-Banking (Maramut)'
                       WHEN P.Transferbalance = 'A2' THEN 'RBS Direct Access'
                       WHEN P.Transferbalance = 'A3' THEN 'Brazilian Banks'
                       WHEN P.Transferbalance = 'A4' THEN 'NETS'
                       WHEN P.Transferbalance = 'A5' THEN 'NONE'
                       WHEN P.Transferbalance = 'A6' THEN 'JDP Morgan'
                       WHEN P.Transferbalance = 'A7' THEN 'J. P. Morgan Access'
                       WHEN P.Transferbalance = 'A8' THEN 'Spar Nord Domestic'
                       WHEN P.Transferbalance = 'A9' THEN 'Spar Nord International'
                       WHEN P.Transferbalance = 'B1' THEN 'Rabobank'
                       WHEN P.Transferbalance = 'B2' THEN 'Deutsche Bank'
                       WHEN P.Transferbalance = 'B4' THEN 'ISO20022'
                       WHEN P.Transferbalance = 'B5' THEN 'Brightwell'
                  END          AS 'PAYMENT METHOD',
                  CASE 
                       WHEN ISNULL(
                                ISNULL(pay.CURRENCYFORPAYMENT, p.CURRENCYFORPAYMENT),
                                p.CURRENCY
                            ) = '' THEN company.CURRENCYCODE
                       ELSE ISNULL(
                                ISNULL(pay.CURRENCYFORPAYMENT, p.CURRENCYFORPAYMENT),
                                p.CURRENCY
                            )
                  END          AS 'PAYMENT CURRENCY',
                  CASE 
                       WHEN p.EMAILPAYSLIP = 'F' THEN 'Off'
                       WHEN p.EMAILPAYSLIP = 'T' THEN 'On'
                  END 'DISTRIBURE PAYSLIP OPTION',
                  email.TELENO 'EMAIL',
                  CASE 
                       WHEN P.EMPLOYMENTSTARTDATE IS NULL THEN 'Employment Start Date missing'
                       ELSE ''
                  END             Issue1,
                  CASE 
                       WHEN P.EMPLOYMENTSTARTDATE > a.DATEFROM THEN 
                            'Employment Start Date greater than Activity Start Date'
                       ELSE ''
                  END             Issue2,
                  CASE 
                       WHEN A.DATEFROM < CAST(GETDATE() AS DATE)
                            AND ISNULL(A.PLANNED, 'N') = 'Y'
                            OR A.TODATEESTIMATED < CAST(GETDATE() AS DATE)
                            AND A.DATETO IS NULL THEN 'Activity not confirmed'
                           ELSE ''
                      END         Issue3,
                      CASE 
                           WHEN NULLIF(A.PAYSCALETABLE, '') IS NOT NULL
                            AND NULLIF(A.PAYSCALE, '') IS NULL THEN 'Activity Payscale Code missing'
                           ELSE ''
                      END         Issue4,
                      CASE 
                           WHEN (
                                    NULLIF(a.PAYSCALETABLE, '') IS NOT NULL
                                    AND NULLIF(a.PAYSCALE, '') IS NOT NULL
                                )
                            AND (
                                    (p20.PAYSCALECODE <> a.PAYSCALE)
                                    OR (p20.PAYSCALETABLE <> a.PAYSCALETABLE)
                                ) THEN 'Payscale mismatch between Contract and Activity'
                           ELSE ''
                      END      AS Issue5,
                      CASE 
                           WHEN p20.DATESTART > periode.PEnd THEN 'Invalid Contract Start Date'
                           ELSE ''
                      END      AS Issue6,
                      CASE 
                           WHEN p20.CONTRACTKIND IS NULL THEN 'No valid contract for this period'
                           ELSE ''
                      END         Issue7,
                      CASE 
                           WHEN (
                                    a.CODE IN (SELECT CODE
                                               FROM   PW001C12
                                               WHERE  OPTIONS LIKE '%S%')
                                )
                            AND (
                                    a.DATEFROM < (
                                        CASE 
                                             WHEN p20.DATESTART > maincon.DATESTART THEN maincon.DATESTART
                                             ELSE p20.DATESTART
                                        END
                                    )
                                ) THEN 'No contract starting from ' + CONVERT(VARCHAR, a.DATEFROM, 1) + ' to ' + CONVERT(VARCHAR, p20.DATESTART, 1)
                           ELSE ''
                      END         Issue8,
                      CASE 
                           WHEN (a.PAYSCALETABLE IS NULL OR a.PAYSCALETABLE = '')
                            AND (a.PAYSCALE IS NULL OR a.PAYSCALE = '') THEN 
                                'Activity Payscale Code and Activity Payscale Table are missing.'
                           WHEN a.PAYSCALETABLE IS NULL
                            OR a.PAYSCALETABLE = '' THEN 'Activity Payscale Table is missing'
                          WHEN a.PAYSCALE IS NULL
                            OR a.PAYSCALE = '' THEN 'Activity Payscale Code is missing'
                          ELSE ''
                     END          Issue9
              FROM   pw001p01 p
                     JOIN (
                              SELECT NUMORGID,
                                     PERIODE,
                                     CAST(
                                         CASE 
                                              WHEN RIGHT(CAST(PERIODE AS VARCHAR), 2) = '00' THEN LEFT(CAST(PERIODE AS VARCHAR), 4) 
                                                   +
                                                   '01'
                                              WHEN CONVERT(INT, RIGHT(CAST(PERIODE AS VARCHAR), 2)) > 12 THEN LEFT(CAST(PERIODE AS VARCHAR), 4)
                                                   + '12'
                                              ELSE CAST(PERIODE AS VARCHAR(6))
                                         END + '01' AS DATETIME
                                     ) AS 'PStart',
                                     DATEADD(
                                         MM,
                                         1,
                                         CAST(
                                             CASE 
                                                  WHEN RIGHT(CAST(PERIODE AS VARCHAR), 2) = '00' THEN LEFT(CAST(PERIODE AS VARCHAR), 4)
                                                       + '01'
                                                  WHEN CONVERT(INT, RIGHT(CAST(PERIODE AS VARCHAR), 2)) > 12 THEN LEFT(CAST(PERIODE AS VARCHAR), 4)
                                                       + '12'
                                                  ELSE CAST(PERIODE AS VARCHAR(6))
                                             END + '01' AS DATETIME
                                         )
                                     ) -1 AS 'PEnd'
                              FROM   PWORGCMP
                              WHERE  PAYROLLENABLED = 'Y'
                              UNION ALL
                              SELECT NUMORGID,
                                     PERIODE,
                                     CAST(
                                         CASE 
                                              WHEN RIGHT(CAST(PERIODE AS VARCHAR), 2) = '00' THEN LEFT(CAST(PERIODE AS VARCHAR), 4) 
                                                   +
                                                   '01'
                                              WHEN CONVERT(INT, RIGHT(CAST(PERIODE AS VARCHAR), 2)) > 12 THEN LEFT(CAST(PERIODE AS VARCHAR), 4)
                                                   + '12'
                                              ELSE CAST(PERIODE AS VARCHAR(6))
                                         END + '01' AS DATETIME
                                     ) AS 'PStart',
                                     DATEADD(
                                         MM,
                                         1,
                                         CAST(
                                             CASE 
                                                  WHEN RIGHT(CAST(PERIODE AS VARCHAR), 2) = '00' THEN LEFT(CAST(PERIODE AS VARCHAR), 4)
                                                       + '01'
                                                  WHEN CONVERT(INT, RIGHT(CAST(PERIODE AS VARCHAR), 2)) > 12 THEN LEFT(CAST(PERIODE AS VARCHAR), 4)
                                                       + '12'
                                                  ELSE CAST(PERIODE AS VARCHAR(6))
                                             END + '01' AS DATETIME
                                         )
                                     ) -1 AS 'PEnd'
                              FROM   PWORGVES
                              WHERE  PAYROLLENABLED = 'Y'
                          ) periode
                          ON  p.CLIENT = periode.NUMORGID
                     JOIN PWORG ORGAN
                          ON  p.CLIENT = ORGAN.NUMORGID
                     JOIN PWORGCMP company
                          ON  p.CLIENT = company.NUMORGID
                     LEFT JOIN PW001P0P p0p
                          ON  p0p.PIN = p.PIN
                          AND p0p.PNUMBER = 'A'
                          AND p0p.PAYSCALECODE IS NOT NULL
                          AND p0p.PAYSCALECODE <> ''
                     LEFT JOIN PW001PAY pay
                          ON  pay.PIN = p.PIN
                          AND pay.CalculateTime IS NOT NULL
                          AND pay.PERIODEUSED = company.PERIODE
                          AND NOT EXISTS(
                                  SELECT 1
                                  FROM   PW001PAY PAY2
                                  WHERE  PAY.PIN = PAY2.PIN
                                         AND PAY2.PERIODEUSED = company.PERIODE
                                         AND (
                                                 PAY2.CALCULATETIME > PAY.CALCULATETIME
                                                 OR (
                                                        PAY2.CALCULATETIME = PAY.CALCULATETIME
                                                        --AND PAY2.UUID > PAY.UUID
                                                    )
                                             )
                              )
                     LEFT JOIN PW001P1R p1r5
                          ON  p1r5.PIN = p.PIN
                          AND p1r5.RATENO = 5
                     LEFT JOIN pw001c02 c02
                          ON  p.rank = c02.code
                     LEFT JOIN PW001P0Y P0Y
                          ON  p.PIN = P0Y.PIN
                     LEFT JOIN PW001P0T email
                          ON  email.PIN = p.PIN
                          AND email.TELETYPE = 6
                          AND NOT EXISTS (
                                  SELECT 1
                                  FROM   pw001p0t t
                                  WHERE  t.PIN = email.PIN
                                         AND t.TELETYPE = email.TELETYPE
                                         AND (
                                                 t.TELEPRIORITY < email.TELEPRIORITY
                                                 OR (
                                                        t.TELEPRIORITY = email.TELEPRIORITY
                                                        AND t.SEQUENCENO > email.SEQUENCENO
                                                    )
                                             )
                              )
                              --Activity matching payroll period
                     JOIN PW001P03 a
                          ON  a.PIN = p.PIN
                          AND a.CODE IN (SELECT CODE
                                         FROM   PW001C12
                                         WHERE  TRANSACTIONCODE <> '')
                          AND a.datefrom <= periode.PEnd
                          AND ISNULL(a.dateto, a.TODATEESTIMATED) >= periode.PStart
                     LEFT JOIN PW001p20 p20sub
                          ON  p20sub.PIN = p.PIN
                              --AND p20.HISTORICAL = 'F'
                          AND p20sub.DATESTART <= ISNULL(A.DATETO, A.TODATEESTIMATED) 
                          AND p20sub.CONTRACTIDABOVE IS NOT NULL
                          AND p20sub.DATESTART = A.DATEFROM
                          AND COALESCE(
                                  p20sub.DATEEND,
                                  (
                                      SELECT MIN(t.DATESTART) -1
                                      FROM   PW001p20 t
                                      WHERE  t.pin = p20sub.pin
                                             AND t.DATESTART > p20sub.DATESTART
                                  ),
                                  a.datefrom
                              ) >= A.DATEFROM
                              --AND ISNULL(p20.DATEEND, ISNULL(A.DATETO, A.TODATEESTIMATED)) >= periode.PStart
                              --AND NOT EXISTS (
                              --	SELECT 1  FROM PW001p20. z
                              --	WHERE z.PIN = p20.PIN AND p20.DATESTART <= ISNULL(A.DATETO, A.TODATEESTIMATED)
                              --		AND COALESCE(p20.DATEEND,(SELECT MIN(t.DATESTART) -1 FROM PW001p20. t WHERE  t.pin = p20.pin AND t.DATESTART > p20.DATESTART), a.DATEFROM) >= A.DATEFROM
                              --		AND z.DATESTART > p20.DATESTART)
                              --AND (p20.dateend IS NULL OR ISNULL(p20.DATEEND,GETDATE()) >= periode.PStart)
                              --AND ISNULL(p20.DATEEND,GETDATE()) >= periode.PStart
                              
                     LEFT JOIN PW001p20 maincon
                          ON  maincon.PIN = p.PIN
                          AND maincon.CONTRACTKIND = 0
                          AND maincon.CONTRACTIDABOVE IS NULL
                          AND maincon.DATESTART <= A.DATEFROM--ISNULL(a.DATETO, a.TODATEESTIMATED)
                          AND (maincon.DATEEND IS NULL OR maincon.DATEEND >= ISNULL(a.DATETO, a.TODATEESTIMATED))--IS NULL
                     LEFT JOIN PW001p20 p20
                          ON  p20.SEQUENCENO = ISNULL(p20sub.SEQUENCENO, maincon.SEQUENCENO)
                     LEFT JOIN PW001C02 C02CONT
                          ON  p20.RANK = C02CONT.CODE
                     LEFT JOIN PW001C32 C32CONT
                          ON  p20.CONTRACTTYPE = C32CONT.code
                     LEFT JOIN PWPSC000 PT
                          ON  ISNULL(a.PAYSCALETABLE, p20.PAYSCALETABLE) = pt.REGULATIVECODE
                          AND (
                                  PT.INCLUDEPLANNEDACTIVITY = 'Y'
                                  OR (PT.INCLUDEPLANNEDACTIVITY <> 'Y' AND a.PLANNED <> 'Y')
                              )
                     LEFT JOIN PWPSC001 PS
                          ON  ISNULL(a.PAYSCALE, p20.PAYSCALECODE) = PS.PAYSCALECODE
                          AND PS.SEQNO = pt.SEQNO
                     LEFT JOIN PWORGVES v
                          ON  a.VESSEL = v.NUMORGID
                     LEFT JOIN PW001C43 c43
                          ON  c43.CODE = P.COSTPLACE
                     LEFT JOIN (
                              SELECT C0.REGULATIVECODE AS 'Table_Code',
                                     C0.REGULATIVENAME AS 'Table_Name',
                                     C1.PAYSCALECODE AS 'Payscale_Code',
                                     C1.PAYSCALENAME AS 'Payscale_Name'
                              FROM   PWPSC000 C0
                                     LEFT JOIN PWPSC001 C1
                                          ON  C1.SEQNO = C0.SEQNO
                          ) payscale
                          ON  payscale.Table_Code = p20.PAYSCALETABLE
                          AND payscale.Payscale_Code = p20.PAYSCALECODE
                     LEFT JOIN (
                              SELECT OLE.PIN,
                                     DOCS.DOCTYPE,
                                     DOCS.[DESCRIPTION],
                                     DOCS.SOURCEDOC,
                                     --RIGHT(CAST(DOCS.[DESCRIPTION] AS VARCHAR), 6) AS PAYPERIOD,
                                     CASE 
                                          WHEN ISNUMERIC(LEFT(RIGHT(CAST(DOCS.SOURCEDOC AS VARCHAR), 11), 6)) = 1 THEN 
                                               LEFT(RIGHT(CAST(DOCS.SOURCEDOC AS VARCHAR), 11), 6)
                                          ELSE 0
                                     END  AS PAYPERIOD,
                                     --),
                                     CASE 
                                          WHEN DOCS.[DESCRIPTION] IS NOT NULL THEN 'Y'
                                          ELSE 'N'
                                     END  AS PAYSLIPSTATUS
                              FROM   PW001P01OLE--left join PW001OLEDOCS PAYSL on PAYSL.pin= pin and DOCTYPE='payslip' and SOURCEDOC like '%periodeused%'
                                     OLE
                                     LEFT JOIN PW001OLEDOCS DOCS
                                          ON  OLE.DOCNO = DOCS.DOCNO
                                          AND DOCS.DOCTYPE = 'payslip'
                                              --AND DOCS.DESCRIPTION LIKE '%PAYSLIP_202308%'
                          ) PayslipStatus
                          ON  PayslipStatus.PIN = p.PIN
                          AND PayslipStatus.PAYPERIOD = company.PERIODE
              WHERE  (
                         (
                             (
                                 CHARINDEX(a.CODE, pt.ACTIVITYCODES, 0) > 0
                                 OR pt.ACTIVITYCODES IS NULL
                             )
                         )
                         OR (
                                (
                                    CHARINDEX(a.CODE, PS.ACTIVITYCODES, 0) > 0
                                    OR ps.ACTIVITYCODES IS NULL
                                )
                            )
                     )
       )     Main
--WHERE  Main.NUMORGID NOT IN (10012602)

Columns Specification

Column	Description/ Location in APM

Column	Description/ Location in APM
PIN	The PIN from the Personal Details.
numorgid	The emplyment organization ID from the Personal Details.
employmentstratdate	The emplyment start date from the Personal Details.
employmentenddate	The emplyment end date from the Personal Details.
Payslip Status	The column shows in the payslip for the current payroll period hs been distributed to the enclosed of this person or not. The sql checks the stats based on the ecnlosed file name that should contain the current payroll period.
Payment Period	The current payroll period of the person based on the persons organization in Personal Details.
Period Start	The start date of the current payroll period of the person based on the persons organization in Personal Details.
Period End	The end date of the current payroll period of the person based on the persons organization in Personal Details.
Name	The Full Name from the Personal Details.
Organization	The Employment Organization from the Personal Details.
Rank	The Current Rank Name from the Personal Details.
Rank Code	The Current Rank Code from the Personal Details.
Nationality Code	The Nationality Code from the Personal Details.
Activity	The payable activity of the person that belongs to the current payroll period. The system checks activity code and payscale settings for autogeneration.
Activity Start	The start date of the selected activity.
Activity End	The end date of the selected activity.
Activity Estimated End	The estimated end date of the selected activity.
Activity Days	The number of days of the selected activity within the current payroll period.
Activity Vessel	The vessel of the selected activity.
Activity Department	The department of the selected activity.
Activity Position	The position of the selected activity.
Contract Type	From the contract of the person that covers selected activity dates.
Contract Name	From the contract of the person that covers selected activity dates.
Contract Rank	From the contract of the person that covers selected activity dates.
Contract Start	From the contract of the person that covers selected activity dates.
Contract End	From the contract of the person that covers selected activity dates.
Contract Payscale Table	From the contract of the person that covers selected activity dates.
Contract Payscale	From the contract of the person that covers selected activity dates.
Contract Payscale Table Name	From the contract of the person that covers selected activity dates.
Contract Payscale Name	From the contract of the person that covers selected activity dates.
Calculated By	The user name of the last person that calculated payroll of the selected crew member.
Calculate Time	The last time the selected crew member was calculated in payroll.
Payment Method	The Payment Action of the crew from the Payroll > Payment tab > New balance .
Payment Currency	The Payment Currency of the crew from the Payroll > Payment tab > New balance. If the currency is not declared for the person, it fetched from the employment company properties -calculation currency.
Distribute Payslip Option	The status of the E-mail Payslip Option from the Payroll > Payment tab > E-mail Payslip.
Email	The email from the Personal Details.
Issues	The column follows the following conditions: If the person doesn’t have an Employment Start Date, then the column says “Employment Start Date missing“. If the Employment Start Date of the person is greater than the Activity Start, then the column says “Employment Start Date greater than Activity Start Date“. If the activity was not confirmed in time - the Activity Start or Activity EED is less than today - then the column says “Activity not confirmed“. If the activity has a Payscale Table but doesn’t have a Payscale Code, then the column says “Activity Payscale Code missing“. If the Contract Payscale Table or Code is different than the Activity Payscale Table or Code then the column says “Payscale mismatch between Contract and Activity.“ But if both Activity Payscale Table and Code are empty, then this condition is ignored. If the Contract Start Date is greater than the end date of the current payroll period, then the column says “Invalid Contract Start Date“ “No valid contract for this period“ message is shown if contract is missing. All the above rules are checked together and listed through a comma if several are applicable.

Start Page

N/A

Adonis HR Reports, Views and Similar

Active Crew Payroll (this month)