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 (
                                        SELECT MIN(DATEFROM)
                                        FROM   pw001p03 pa
                                        WHERE  pa.PIN = p.PIN
                                               AND pa.datefrom < (
                                                       DATEADD(
                                                           MM,
                                                           1,
                                                           CAST(
                                                               CASE 
                                                                    WHEN RIGHT(CAST(company.PERIODE AS VARCHAR), 2) =
                                                                         '00' THEN LEFT(CAST(company.PERIODE AS VARCHAR), 4) 
                                                                         + '01'
                                                                    WHEN CONVERT(INT, RIGHT(CAST(company.PERIODE AS VARCHAR), 2)) 
                                                                         >
                                                                         12 THEN LEFT(CAST(company.PERIODE AS VARCHAR), 4) 
                                                                         + '12'
                                                                    ELSE CAST(company.PERIODE AS VARCHAR(6))
                                                               END + '01' AS DATETIME
                                                           )
                                                       ) -1
                                                   )
                                               AND ISNULL(pa.dateto, pa.TODATEESTIMATED) >= (
                                                       CAST(
                                                           CASE 
                                                                WHEN RIGHT(CAST(company.PERIODE AS VARCHAR), 2) = '00' THEN 
                                                                     LEFT(CAST(company.PERIODE AS VARCHAR), 4)
                                                                     + '01'
                                                                WHEN CONVERT(INT, RIGHT(CAST(company.PERIODE AS VARCHAR), 2)) 
                                                                     > 12 THEN LEFT(CAST(company.PERIODE AS VARCHAR), 4) 
                                                                     + '12'
                                                                ELSE CAST(company.PERIODE AS VARCHAR(6))
                                                           END + '01' AS DATETIME
                                                       )
                                                   )
                                    ) 
                                    < (
                                        CAST(
                                            CASE 
                                                 WHEN RIGHT(CAST(company.PERIODE AS VARCHAR), 2) = '00' THEN LEFT(CAST(company.PERIODE AS VARCHAR), 4)
                                                      + '01'
                                                 WHEN CONVERT(INT, RIGHT(CAST(company.PERIODE AS VARCHAR), 2)) > 12 THEN 
                                                      LEFT(CAST(company.PERIODE AS VARCHAR), 4)
                                                      + '12'
                                                 ELSE CAST(company.PERIODE AS VARCHAR(6))
                                            END + '01' AS DATETIME
                                        )
                                    ) THEN CAST(
                                        CASE 
                                             WHEN RIGHT(CAST(company.PERIODE AS VARCHAR), 2) = '00' THEN LEFT(CAST(company.PERIODE AS VARCHAR), 4)
                                                  + '01'
                                             WHEN CONVERT(INT, RIGHT(CAST(company.PERIODE AS VARCHAR), 2)) > 12 THEN 
                                                  LEFT(CAST(company.PERIODE AS VARCHAR), 4)
                                                  + '12'
                                             ELSE CAST(company.PERIODE AS VARCHAR(6))
                                        END + '01' AS DATETIME
                                    )
                               ELSE (
                                        SELECT MIN(DATEFROM)
                                        FROM   pw001p03 pa
                                        WHERE  pa.PIN = p.PIN
                                               AND pa.datefrom < (
                                                       DATEADD(
                                                           MM,
                                                           1,
                                                           CAST(
                                                               CASE 
                                                                    WHEN RIGHT(CAST(company.PERIODE AS VARCHAR), 2) =
                                                                         '00' THEN LEFT(CAST(company.PERIODE AS VARCHAR), 4) 
                                                                         + '01'
                                                                    WHEN CONVERT(INT, RIGHT(CAST(company.PERIODE AS VARCHAR), 2)) 
                                                                         >
                                                                         12 THEN LEFT(CAST(company.PERIODE AS VARCHAR), 4) 
                                                                         + '12'
                                                                    ELSE CAST(company.PERIODE AS VARCHAR(6))
                                                               END + '01' AS DATETIME
                                                           )
                                                       ) -1
                                                   )
                                               AND ISNULL(pa.dateto, pa.TODATEESTIMATED) >= (
                                                       CAST(
                                                           CASE 
                                                                WHEN RIGHT(CAST(company.PERIODE AS VARCHAR), 2) = '00' THEN 
                                                                     LEFT(CAST(company.PERIODE AS VARCHAR), 4)
                                                                     + '01'
                                                                WHEN CONVERT(INT, RIGHT(CAST(company.PERIODE AS VARCHAR), 2)) 
                                                                     > 12 THEN LEFT(CAST(company.PERIODE AS VARCHAR), 4) 
                                                                     + '12'
                                                                ELSE CAST(company.PERIODE AS VARCHAR(6))
                                                           END + '01' AS DATETIME
                                                       )
                                                   )
                                    )
                          END
                      ),
                      (
                          CASE 
                               WHEN (
                                        SELECT MAX(ISNULL(pa.DATETO, pa.TODATEESTIMATED))
                                        FROM   pw001p03 pa
                                        WHERE  pa.PIN = p.PIN
                                               AND pa.datefrom < (
                                                       DATEADD(
                                                           MM,
                                                           1,
                                                           CAST(
                                                               CASE 
                                                                    WHEN RIGHT(CAST(company.PERIODE AS VARCHAR), 2) =
                                                                         '00' THEN LEFT(CAST(company.PERIODE AS VARCHAR), 4) 
                                                                         + '01'
                                                                    WHEN CONVERT(INT, RIGHT(CAST(company.PERIODE AS VARCHAR), 2)) 
                                                                         >
                                                                         12 THEN LEFT(CAST(company.PERIODE AS VARCHAR), 4) 
                                                                         + '12'
                                                                    ELSE CAST(company.PERIODE AS VARCHAR(6))
                                                               END + '01' AS DATETIME
                                                           )
                                                       ) -1
                                                   )
                                               AND ISNULL(pa.dateto, pa.TODATEESTIMATED) >= (
                                                       CAST(
                                                           CASE 
                                                                WHEN RIGHT(CAST(company.PERIODE AS VARCHAR), 2) = '00' THEN 
                                                                     LEFT(CAST(company.PERIODE AS VARCHAR), 4)
                                                                     + '01'
                                                                WHEN CONVERT(INT, RIGHT(CAST(company.PERIODE AS VARCHAR), 2)) 
                                                                     > 12 THEN LEFT(CAST(company.PERIODE AS VARCHAR), 4) 
                                                                     + '12'
                                                                ELSE CAST(company.PERIODE AS VARCHAR(6))
                                                           END + '01' AS DATETIME
                                                       )
                                                   )
                                    )
                                    > (
                                        DATEADD(
                                            MM,
                                            1,
                                            CAST(
                                                CASE 
                                                     WHEN RIGHT(CAST(company.PERIODE AS VARCHAR), 2) = '00' THEN LEFT(CAST(company.PERIODE AS VARCHAR), 4)
                                                          + '01'
                                                     WHEN CONVERT(INT, RIGHT(CAST(company.PERIODE AS VARCHAR), 2)) > 12 THEN 
                                                          LEFT(CAST(company.PERIODE AS VARCHAR), 4)
                                                          + '12'
                                                     ELSE CAST(company.PERIODE AS VARCHAR(6))
                                                END + '01' AS DATETIME
                                            )
                                        ) -1
                                    ) THEN (
                                        DATEADD(
                                            MM,
                                            1,
                                            CAST(
                                                CASE 
                                                     WHEN RIGHT(CAST(company.PERIODE AS VARCHAR), 2) = '00' THEN LEFT(CAST(company.PERIODE AS VARCHAR), 4)
                                                          + '01'
                                                     WHEN CONVERT(INT, RIGHT(CAST(company.PERIODE AS VARCHAR), 2)) > 12 THEN 
                                                          LEFT(CAST(company.PERIODE AS VARCHAR), 4)
                                                          + '12'
                                                     ELSE CAST(company.PERIODE AS VARCHAR(6))
                                                END + '01' AS DATETIME
                                            )
                                        ) -1
                                    )
                               ELSE (
                                        SELECT MAX(ISNULL(pa.DATETO, pa.TODATEESTIMATED))
                                        FROM   pw001p03 pa
                                        WHERE  pa.PIN = p.PIN
                                               AND pa.datefrom < (
                                                       DATEADD(
                                                           MM,
                                                           1,
                                                           CAST(
                                                               CASE 
                                                                    WHEN RIGHT(CAST(company.PERIODE AS VARCHAR), 2) =
                                                                         '00' THEN LEFT(CAST(company.PERIODE AS VARCHAR), 4) 
                                                                         + '01'
                                                                    WHEN CONVERT(INT, RIGHT(CAST(company.PERIODE AS VARCHAR), 2)) 
                                                                         >
                                                                         12 THEN LEFT(CAST(company.PERIODE AS VARCHAR), 4) 
                                                                         + '12'
                                                                    ELSE CAST(company.PERIODE AS VARCHAR(6))
                                                               END + '01' AS DATETIME
                                                           )
                                                       ) -1
                                                   )
                                               AND ISNULL(pa.dateto, pa.TODATEESTIMATED) >= (
                                                       CAST(
                                                           CASE 
                                                                WHEN RIGHT(CAST(company.PERIODE AS VARCHAR), 2) = '00' THEN 
                                                                     LEFT(CAST(company.PERIODE AS VARCHAR), 4)
                                                                     + '01'
                                                                WHEN CONVERT(INT, RIGHT(CAST(company.PERIODE AS VARCHAR), 2)) 
                                                                     > 12 THEN LEFT(CAST(company.PERIODE AS VARCHAR), 4) 
                                                                     + '12'
                                                                ELSE CAST(company.PERIODE AS VARCHAR(6))
                                                           END + '01' AS DATETIME
                                                       )
                                                   )
                                    )
                          END
                      )
                  ) + 1 '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 > prevcon.DATESTART THEN prevcon.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 p20
               ON p20.PIN = p.PIN
               --AND p20.HISTORICAL = 'F'
           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
               LEFT JOIN PW001P20 prevcon
               ON prevcon.SEQUENCENO = p20.CONTRACTIDABOVE
               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)
       AND (
               CASE 
                    WHEN main.ROW# = 1 THEN 1
                    WHEN main.[Contract Start] = main.[ACTIVITY START] AND main.[Contract End] = main.[Contract End] THEN 1
                    WHEN main.[Contract End] IS NULL AND main.[Contract Type] LIKE '%Main%' AND main.ROW#  < 3 THEN 1 
                   ELSE 0 END
           ) = 1

Columns Specification

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

Active Crew Payroll (this month)