CREATE VIEW [dbo].[PW001SRV203] AS
SELECT t.PIN,
t.EMPLOYMENTSTARTDATE,
t.EMPLOYMENTENDDATE,
t.NUMORGID,
t.NAME,
t.ORGANIZATION,
t.[RANK],
t.[RANK CODE],
t.ACTIVITY,
t.[ACTIVITY START],
t.[ACTIVITY END],
t.[ACTIVITY ESTIMATED END],
t.[ACTIVITY PAYSCALE TABLE],
t.[ACTIVITY PAYSCALE CODE],
t.[Contract Type],
t.[Contract Name],
t.[Contract Rank],
t.[Contract Start],
t.[Contract End],
t.[CONTRACT PAYSCALE TABLE],
t.[CONTRACT PAYSCALE],
t.[CONTRACT PAYSCALE TABLE NAME],
t.[CONTRACT PAYSCALE NAME],
t.ISSUES
FROM (
SELECT main.PIN,
main.EMPLOYMENTSTARTDATE,
main.EMPLOYMENTENDDATE,
main.NUMORGID,
main.NAME,
main.ORGANIZATION,
main.[RANK],
main.[RANK CODE],
main.ACTIVITY,
main.[ACTIVITY START],
main.[ACTIVITY END],
main.[ACTIVITY ESTIMATED END],
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],
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, ', '), ''), +
1,
ISNULL(NULLIF(', ' + main.issue8, ', '), '') +
1 ISNULL(NULLIF(', ' + main.issue9, ', '), ''),
'' 1,
), 1,
'' ''
) AS [ISSUES] FROM (),
SELECT p.PIN,''
) AS [ISSUES]
p.EMPLOYMENTSTARTDATE, FROM (
SELECT p.EMPLOYMENTENDDATEPIN,
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',
pay.CURRENCYFORPAYMENT '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, END Issue3,
CASE 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 ( AND (
(p20.PAYSCALECODE <> a.PAYSCALE) OR (p20.PAYSCALETABLEPAYSCALECODE <> a.PAYSCALETABLEPAYSCALE)
) THEN 'Payscale mismatch between Contract and Activity' OR (p20.PAYSCALETABLE <> a.PAYSCALETABLE)
ELSE '' ) THEN END'Payscale ASmismatch Issue5,between Contract and Activity'
CASE ELSE ''
WHEN p20.DATESTART > periode.PEnd THEN 'Invalid Contract Start Date' END AS Issue5,
ELSE '' CASE
END AS Issue6, 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)
WHEN p20.CONTRACTKIND IS NULL THEN 'No valid contract for this period' ELSE+ '12'
END Issue7 ELSE CAST(PERIODE AS VARCHAR(6))
FROM pw001p01 p JOIN ( END + '01' AS DATETIME
SELECT NUMORGID, ) AS PERIODE,'PStart',
CAST DATEADD(
CASE MM,
WHEN RIGHT(CAST(PERIODE AS VARCHAR), 2) = '00' THEN LEFT(CAST(PERIODE AS VARCHAR), 4)1,
CAST(
+ CASE
'01' WHEN CONVERT(INT, RIGHT(CAST(PERIODE AS VARCHAR), 2)) >= 12'00' THEN LEFT(CAST(PERIODE AS VARCHAR), 4) 4)
+ '1201'
ELSE CAST(PERIODE AS VARCHAR(6)) WHEN CONVERT(INT, RIGHT(CAST(PERIODE AS VARCHAR), 2)) > 12 THEN
END + '01' AS DATETIME ) AS 'PStart', LEFT(CAST(PERIODE AS VARCHAR), 4)
DATEADD( + MM,'12'
1, ELSE CAST(PERIODE AS VARCHAR(6))
CAST( END + CASE'01' AS DATETIME
WHEN RIGHT(CAST(PERIODE AS VARCHAR), 2)
= '00' THEN LEFT(CAST(PERIODE AS VARCHAR), 4) ) -1 AS 'PEnd'
+ '01' FROM PWORGVES
WHEN CONVERT(INT, RIGHT(CAST(PERIODE AS VARCHAR), 2)) > 12 THEN LEFT(CAST(PERIODE AS VARCHAR), 4) WHERE PAYROLLENABLED = 'Y'
+) '12'periode
ON p.CLIENT = periode.NUMORGID
ELSE CAST(PERIODE AS VARCHAR(6)) JOIN PWORG ORGAN
END + '01' AS DATETIME ON p.CLIENT = ORGAN.NUMORGID
) JOIN PWORGCMP company
) -1 AS 'PEnd' ON p.CLIENT = company.NUMORGID
FROM PWORGCMP LEFT JOIN PW001P0P p0p
WHERE PAYROLLENABLED = 'Y' ON p0p.PIN = p.PIN
UNION ALL SELECT NUMORGID,
AND p0p.PNUMBER = 'A'
PERIODE, AND p0p.PAYSCALECODE IS NOT NULL
CAST( AND p0p.PAYSCALECODE <> ''
CASE LEFT JOIN PW001PAY pay
ON pay.PIN = p.PIN
WHEN RIGHT(CAST(PERIODE AS VARCHAR), 2) = '00' THEN LEFT(CAST(PERIODE AS VARCHAR), 4) AND pay.CalculateTime IS NOT NULL
+ AND pay.PERIODEUSED = company.PERIODE
'01' AND NOT EXISTS(
WHEN CONVERT(INT, RIGHT(CAST(PERIODE AS VARCHAR), 2)) > 12 THEN LEFT(CAST(PERIODE AS VARCHAR), 4) SELECT 1
FROM PW001PAY PAY2
+ '12' WHERE PAY.PIN = PAY2.PIN
ELSE CAST(PERIODE AS VARCHAR(6)) END + '01' AS DATETIME AND PAY2.PERIODEUSED = company.PERIODE
) AS 'PStart', AND (
DATEADD( MM, PAY2.CALCULATETIME > PAY.CALCULATETIME
1, OR (
CAST( CASE PAY2.CALCULATETIME = PAY.CALCULATETIME
WHEN RIGHT(CAST(PERIODE AS VARCHAR), 2) = '00' THEN LEFT(CAST(PERIODE AS VARCHAR), 4) --AND PAY2.UUID > PAY.UUID
+ '01' )
WHEN CONVERT(INT, RIGHT(CAST(PERIODE AS VARCHAR), 2)) > 12 THEN LEFT(CAST(PERIODE AS VARCHAR), 4) )
+ '12' )
LEFT JOIN PW001P1R p1r5
ELSE CAST(PERIODE AS VARCHAR(6)) ON p1r5.PIN = p.PIN
END + '01' AS DATETIME AND p1r5.RATENO = 5
) LEFT JOIN pw001c02 c02
) -1 AS 'PEnd' ON p.rank = c02.code
FROM PWORGVES LEFT JOIN PW001P0Y P0Y
WHERE PAYROLLENABLED = 'Y' ) periode ON p.PIN = P0Y.PIN
ON p.CLIENT = periode.NUMORGID LEFT JOIN PW001P0T email
JOIN PWORG ORGAN ON pemail.CLIENTPIN = ORGANp.NUMORGIDPIN
JOIN PWORGCMP company AND email.TELETYPE = 6
ON p.CLIENT = company.NUMORGID AND LEFTNOT JOINEXISTS PW001P0P(
p0p ON p0p.PIN = p.PIN SELECT 1
AND p0p.PNUMBER = 'A' AND p0p.PAYSCALECODE IS NOT NULL FROM pw001p0t t
AND p0p.PAYSCALECODE <> '' LEFT JOINWHERE PW001PAY payt.PIN = email.PIN
ON pay.PIN = p.PIN AND payt.CalculateTimeTELETYPE IS= NOTemail.TELETYPE
NULL AND pay.PERIODEUSED = company.PERIODE AND NOT EXISTS((
SELECT 1 t.TELEPRIORITY < email.TELEPRIORITY
FROM PW001PAY PAY2 WHERE PAY.PIN = PAY2.PIN OR (
AND PAY2.PERIODEUSED = company.PERIODE t.TELEPRIORITY = email.TELEPRIORITY
AND ( PAY2.CALCULATETIME > PAY.CALCULATETIME AND t.SEQUENCENO > email.SEQUENCENO
OR ( )
PAY2.CALCULATETIME = PAY.CALCULATETIME )
--AND PAY2.UUID > PAY.UUID )
--Activity )matching payroll period
JOIN PW001P03 a
) )ON a.PIN = p.PIN
LEFT JOIN PW001P1R p1r5 AND a.CODE IN (SELECT CODE
ON p1r5.PIN = p.PIN AND p1r5.RATENO = 5 FROM PW001C12
LEFT JOIN pw001c02 c02 ON p.rank = c02.codeWHERE TRANSACTIONCODE <> '')
LEFT JOIN PW001P0Y P0Y AND a.datefrom <= periode.PEnd
ON p.PIN = P0Y.PIN AND ISNULL(a.dateto, a.TODATEESTIMATED) >= periode.PStart
LEFT JOIN PW001P0T email LEFT JOIN PW001P20 p20
ON email.PIN = p.PIN AND email.TELETYPE = 6 ON p20.PIN = p.PIN
AND NOT EXISTS ( --AND p20.HISTORICAL = 'F'
SELECT 1 AND P20.DATESTART <= ISNULL(A.DATETO, A.TODATEESTIMATED)
FROM pw001p0t t AND WHERECOALESCE(
t.PIN = email.PIN AND t.TELETYPE = email.TELETYPEP20.DATEEND,
AND (
SELECT MIN(t.TELEPRIORITYDATESTART) <-1
email.TELEPRIORITY ORFROM ( PW001P20 t
WHERE t.TELEPRIORITYpin = emailp20.TELEPRIORITYpin
AND t.SEQUENCENODATESTART > email.SEQUENCENOp20.DATESTART
),
) a.datefrom
) ) >= A.DATEFROM
--Activity matching payroll periodLEFT JOIN PW001P20 prevcon
JOIN PW001P03 a ON prevcon.SEQUENCENO = p20.CONTRACTIDABOVE
ON a.PIN = p.PIN LEFT JOIN PW001C02 C02CONT
AND a.CODE IN (SELECT CODE ON p20.RANK = C02CONT.CODE
FROM PW001C12 LEFT JOIN PW001C32 C32CONT
WHERE TRANSACTIONCODE <> '') ON p20.CONTRACTTYPE = C32CONT.code
AND a.datefrom <= periode.PEnd LEFT JOIN PWPSC000 PT
AND ISNULL(a.dateto, a.TODATEESTIMATED) >= periode.PStart LEFTON JOIN PW001P20 ISNULL(a.PAYSCALETABLE, p20.PAYSCALETABLE) = pt.REGULATIVECODE
ON p20.PIN = p.PIN AND (
--AND p20.HISTORICAL = 'F' AND P20PT.DATESTARTINCLUDEPLANNEDACTIVITY <= ISNULL(A.DATETO, A.TODATEESTIMATED)'Y'
AND COALESCE( OR (PT.INCLUDEPLANNEDACTIVITY <> 'Y' AND a.PLANNED <> 'Y')
P20.DATEEND, ( )
LEFT JOIN PWPSC001 SELECT MIN(t.DATESTART) -1PS
ON FROM PW001P20 tISNULL(a.PAYSCALE, p20.PAYSCALECODE) = PS.PAYSCALECODE
AND WHERE t.pinPS.SEQNO = p20pt.pinSEQNO
LEFT JOIN PWORGVES v
AND t.DATESTART > p20.DATESTART ON a.VESSEL = ),v.NUMORGID
LEFT JOIN a.datefromPW001C43 c43
) >= A.DATEFROM ON c43.CODE = P.COSTPLACE
LEFT JOIN PW001C02 C02CONT LEFT JOIN (
ON p20.RANK = C02CONT.CODE SELECT C0.REGULATIVECODE AS 'Table_Code',
LEFT JOIN PW001C32 C32CONT ON p20.CONTRACTTYPE = C32CONT.code C0.REGULATIVENAME AS 'Table_Name',
LEFT JOIN PWPSC000 PT ON ISNULL(a.PAYSCALETABLE, p20.PAYSCALETABLE) = pt.REGULATIVECODE C1.PAYSCALECODE AS 'Payscale_Code',
AND ( C1.PAYSCALENAME AS 'Payscale_Name'
PT.INCLUDEPLANNEDACTIVITY = 'Y' FROM OR (PT.INCLUDEPLANNEDACTIVITY <>PWPSC000 'Y'C0
AND a.PLANNED <> 'Y') ) LEFT JOIN PWPSC001 C1
LEFT JOIN PWPSC001 PS ON ISNULL(a.PAYSCALE, p20.PAYSCALECODE) = PS.PAYSCALECODE ON C1.SEQNO = C0.SEQNO
AND PS.SEQNO = pt.SEQNO ) payscale
LEFT JOIN PWORGVES v ON a.VESSEL payscale.Table_Code = vp20.NUMORGIDPAYSCALETABLE
LEFT JOIN PW001C43 c43 AND payscale.Payscale_Code = p20.PAYSCALECODE
ON c43.CODE = P.COSTPLACE LEFT JOIN (
LEFT JOIN ( SELECT OLE.PIN,
SELECT C0.REGULATIVECODE AS 'Table_Code', DOCS.DOCTYPE,
C0.REGULATIVENAME AS 'Table_Name', C1.PAYSCALECODE AS 'Payscale_Code'DOCS.[DESCRIPTION],
C1.PAYSCALENAME AS 'Payscale_Name' DOCS.SOURCEDOC,
FROM PWPSC000 C0 RIGHT(CAST(DOCS.[DESCRIPTION] AS VARCHAR), 6) AS PAYPERIOD,
LEFT JOIN PWPSC001 C1 --),
ON C1.SEQNO = C0.SEQNO CASE )
payscale ON payscale.Table_Code = p20.PAYSCALETABLE WHEN AND payscale.Payscale_Code = p20.PAYSCALECODE
DOCS.[DESCRIPTION] IS NOT NULL THEN 'Y'
LEFT JOIN ( ELSE 'N'
SELECT OLE.PIN, DOCS.DOCTYPE, END AS PAYSLIPSTATUS
DOCS.[DESCRIPTION], FROM PW001P01OLE--left join PW001OLEDOCS PAYSL on PAYSL.pin= pin and DOCTYPE='payslip' and SOURCEDOC like '%periodeused%'
DOCS.SOURCEDOC, OLE
RIGHT(CAST(DOCS.[DESCRIPTION] AS VARCHAR), 6) AS PAYPERIOD, LEFT JOIN PW001OLEDOCS DOCS
--), CASE ON OLE.DOCNO = DOCS.DOCNO
WHEN DOCS.[DESCRIPTION] IS NOT NULL THEN 'Y' AND DOCS.DOCTYPE = 'payslip'
ELSE 'N' --AND DOCS.DESCRIPTION LIKE '%PAYSLIP_202308%'
END AS PAYSLIPSTATUS ) PayslipStatus
FROM PW001P01OLE--left join PW001OLEDOCS PAYSL on PAYSL.pin= pin and DOCTYPE='payslip' and SOURCEDOC like '%periodeused%' ON PayslipStatus.PIN = p.PIN
OLE AND PayslipStatus.PAYPERIOD = company.PERIODE
LEFT JOIN PW001OLEDOCS DOCS WHERE (
(
ON OLE.DOCNO = DOCS.DOCNO (
AND DOCS.DOCTYPE = 'payslip' CHARINDEX(a.CODE, pt.ACTIVITYCODES, 0) > 0
--AND DOCS.DESCRIPTION LIKE '%PAYSLIP_202308%' OR pt.ACTIVITYCODES IS NULL
) PayslipStatus ON PayslipStatus.PIN = p.PIN )
AND PayslipStatus.PAYPERIOD = company.PERIODE )
WHERE ( OR (
( (
( CHARINDEX(a.CODE, ptPS.ACTIVITYCODES, 0) > 0
OR ptps.ACTIVITYCODES IS NULL
)
)
)
OR ( ) Main
WHERE Main.ROW# = 1 (
AND (
CASE
CHARINDEX(a.CODE, PS.ACTIVITYCODES, 0) > 0 WHEN main.ROW# = 1 THEN 1
OR ps.ACTIVITYCODES IS NULL 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%' THEN 1
) ELSE 0 END
) ) = Main1
) T
WHERE t.issues <> '' AND t.NUMORGID NOT IN (10012602) |