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 VESSEL],
t.[ACTIVITY DEPARTMENT],
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 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],
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]
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#, PARTITION BY p20.PIN ORDER BY CASEp20.PIN,
WHEN p20.CONTRACTKIND =DESC
0 THEN 'Main Contract' ) WHEN p20.CONTRACTKIND = 1AS THENROW#,
'Sub Contract' CASE
WHEN p20.CONTRACTKIND = 2 THEN 'Ammendment' 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 END Issue3,
CASE
WHEN NULLIF(A.PAYSCALETABLE, '') IS NOT NULL
AND NULLIF(A.PAYSCALE, '') IS NULL THEN 'Activity Payscale Code missing'
ELSE ''
END 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 END AS Issue5,
CASE
WHEN p20.DATESTART > periode.PEnd THEN 'Invalid Contract Start Date'
ELSE ''
END END AS Issue6,
CASE
WHEN p20.CONTRACTKIND IS NULL THEN 'No valid contract for this period'
ELSE ''
END END Issue7,
CASE 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 ' +WHEN CONVERT(VARCHAR, a.DATEFROM, 1) + ' to ' + CONVERT(VARCHAR, p20.DATESTART, 1)
ELSE '' a.CODE IN (SELECT CODE
END Issue8, CASE FROM PW001C12
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 'ActivityWHERE Payscale TableOPTIONS isLIKE missing'%S%')
WHEN a.PAYSCALE IS NULL OR a.PAYSCALE = '' THEN 'Activity Payscale Code is missing' )
ELSE '' END AND (
Issue9 FROM pw001p01 p a.DATEFROM < (
JOIN ( CASE
SELECT NUMORGID, PERIODE, WHEN p20.DATESTART > maincon.DATESTART THEN maincon.DATESTART
CAST( ELSE p20.DATESTART
CASE END
WHEN RIGHT(CAST(PERIODE AS VARCHAR), 2) = '00' THEN LEFT(CAST(PERIODE AS VARCHAR), 4) )
) THEN 'No contract starting from ' + CONVERT(VARCHAR, a.DATEFROM, 1) +
' to ' + CONVERT(VARCHAR, p20.DATESTART, 1)
'01' ELSE ''
WHEN CONVERT(INT, RIGHT(CAST(PERIODE AS VARCHAR), 2)) > 12 THEN END Issue8,
CASE
LEFT(CAST(PERIODE AS VARCHAR), 4) WHEN (a.PAYSCALETABLE IS NULL OR a.PAYSCALETABLE = '')
+ '12' AND (a.PAYSCALE IS NULL OR a.PAYSCALE = '') THEN
ELSE CAST(PERIODE AS VARCHAR(6)) 'Activity Payscale Code and Activity Payscale Table are missing.'
WHEN ENDa.PAYSCALETABLE +IS '01'NULL
AS DATETIME OR a.PAYSCALETABLE = '' THEN 'Activity Payscale Table ) AS 'PStart',is missing'
WHEN a.PAYSCALE IS NULL
DATEADD( OR a.PAYSCALE = '' THEN 'Activity Payscale Code is missing'
MM, ELSE ''
1, END Issue9
CAST( FROM pw001p01 p
JOIN (
CASE SELECT NUMORGID,
WHEN RIGHT(CAST(PERIODE AS VARCHAR), 2) = '00' THEN LEFT(CAST(PERIODE AS VARCHAR), 4) PERIODE,
CAST(
+ '01' CASE
WHEN CONVERT(INT, RIGHT(CAST(PERIODE AS VARCHAR), 2)) > 12 THEN WHEN RIGHT(CAST(PERIODE AS VARCHAR), 2) = '00' THEN LEFT(CAST(PERIODE AS VARCHAR), 4)
LEFT(CAST(PERIODE AS VARCHAR), 4) +
+ '12' '01'
ELSE CAST(PERIODE AS VARCHAR(6)) WHEN CONVERT(INT, RIGHT(CAST(PERIODE AS VARCHAR), 2)) > 12 THEN
END + '01' AS DATETIME LEFT(CAST(PERIODE AS VARCHAR), 4)
) -1 AS 'PEnd' + '12'
FROM PWORGCMP ELSE CAST(PERIODE AS VARCHAR(6))
WHERE PAYROLLENABLED = 'Y' END + '01' AS DATETIME
UNION ALL ) SELECTAS NUMORGID'PStart',
PERIODE, DATEADD(
CAST( MM,
CASE 1,
WHEN RIGHT(CAST(PERIODE AS VARCHAR), 2) = '00' THEN LEFT(CAST(PERIODE
AS VARCHAR), 4) CASE
+ WHEN RIGHT(CAST(PERIODE AS VARCHAR), 2) = '01'
00' THEN LEFT(CAST(PERIODE AS VARCHAR), 4)
WHEN CONVERT(INT, RIGHT(CAST(PERIODE AS VARCHAR), 2)) > 12 THEN + '01'
WHEN CONVERT(INT, LEFTRIGHT(CAST(PERIODE AS VARCHAR), 4)2)) > 12 THEN
+ '12' LEFT(CAST(PERIODE AS VARCHAR), 4)
ELSE CAST(PERIODE AS VARCHAR(6)) + '12'
END + '01' AS DATETIME ELSE CAST(PERIODE AS VARCHAR(6))
) AS 'PStart', END + '01' AS DATETIME
DATEADD( MM,)
) 1,-1 AS 'PEnd'
FROM PWORGCMP
CAST( WHERE PAYROLLENABLED = 'Y'
CASE UNION ALL
WHEN RIGHT(CAST(PERIODE AS VARCHAR), 2) = '00' THEN LEFT(CAST(PERIODE ASSELECT VARCHAR)NUMORGID,
4) PERIODE,
+ '01' CAST(
WHEN CONVERT(INT, RIGHT(CAST(PERIODE AS VARCHAR), 2)) > 12 THEN CASE
WHEN RIGHT(CAST(PERIODE AS VARCHAR), 2) = '00' THEN LEFT(CAST(PERIODE AS VARCHAR), 4)
+
'12' '01'
ELSE CAST(PERIODE AS VARCHAR(6)) END + '01'WHEN CONVERT(INT, RIGHT(CAST(PERIODE AS DATETIME
VARCHAR), 2)) > 12 THEN
) LEFT(CAST(PERIODE AS VARCHAR), 4)
) -1 AS 'PEnd' + '12'
FROM PWORGVES WHERE PAYROLLENABLED = 'Y' ELSE CAST(PERIODE AS VARCHAR(6))
) periode END + '01' AS DATETIME
ON p.CLIENT = periode.NUMORGID JOIN PWORG ORGAN ) AS 'PStart',
ON p.CLIENT = ORGAN.NUMORGID DATEADD(
JOIN PWORGCMP company ON p.CLIENT = company.NUMORGID MM,
LEFT JOIN PW001P0P p0p 1,
ON p0p.PIN = p.PIN AND p0p.PNUMBER = 'A' CAST(
AND p0p.PAYSCALECODE IS NOT NULL CASE
AND p0p.PAYSCALECODE <> '' LEFT JOIN PW001PAY pay WHEN RIGHT(CAST(PERIODE AS VARCHAR), 2) = '00' THEN LEFT(CAST(PERIODE AS VARCHAR), 4)
ON pay.PIN = p.PIN AND pay.CalculateTime IS NOT NULL + '01'
AND pay.PERIODEUSED = company.PERIODE AND NOTWHEN EXISTSCONVERT(INT, RIGHT(CAST(PERIODE AS VARCHAR), 2)) > 12 THEN
SELECT 1 LEFT(CAST(PERIODE AS VARCHAR), 4)
FROM PW001PAY PAY2 WHERE PAY.PIN = PAY2.PIN + '12'
AND PAY2.PERIODEUSED = company.PERIODE ELSE CAST(PERIODE AS VARCHAR(6))
AND ( END + '01' AS DATETIME
PAY2.CALCULATETIME > PAY.CALCULATETIME )
OR ( ) -1 AS 'PEnd'
FROM PWORGVES
PAY2.CALCULATETIME = PAY.CALCULATETIME WHERE PAYROLLENABLED = 'Y'
--AND PAY2.UUID > PAY.UUID ) periode
ON p.CLIENT = periode.NUMORGID
) JOIN PWORG ORGAN
) ON p.CLIENT = ORGAN.NUMORGID
) JOIN PWORGCMP company
LEFT JOIN PW001P1R p1r5 ON p.CLIENT = company.NUMORGID
ON p1r5.PIN = p.PIN LEFT JOIN PW001P0P p0p
AND p1r5.RATENO = 5 ON p0p.PIN = p.PIN
LEFT JOIN pw001c02 c02 AND p0p.PNUMBER = 'A'
ON p.rank = c02.code AND p0p.PAYSCALECODE IS LEFTNOT JOINNULL
PW001P0Y P0Y ON AND p.PIN = P0Y.PINp0p.PAYSCALECODE <> ''
LEFT JOIN PW001P0TPW001PAY pay
email ON emailpay.PIN = p.PIN
AND emailpay.TELETYPECalculateTime =IS 6NOT NULL
AND NOT EXISTS ( AND pay.PERIODEUSED = company.PERIODE
AND SELECTNOT 1EXISTS(
FROM pw001p0tSELECT t1
WHERE t.PIN =FROM email.PIN PW001PAY PAY2
WHERE AND tPAY.TELETYPEPIN = emailPAY2.TELETYPEPIN
AND ( AND PAY2.PERIODEUSED = company.PERIODE
t.TELEPRIORITYAND <(
email.TELEPRIORITY OR ( PAY2.CALCULATETIME > PAY.CALCULATETIME
OR t.TELEPRIORITY(
= email.TELEPRIORITY AND tPAY2.SEQUENCENOCALCULATETIME >= email.SEQUENCENOPAY.CALCULATETIME
)
--AND PAY2.UUID > PAY.UUID
) )
) )
--Activity matching payroll period )
JOIN PW001P03 a LEFT JOIN PW001P1R p1r5
ON a.PIN = p.PIN ON p1r5.PIN AND= a.CODE IN (SELECT CODEp.PIN
AND p1r5.RATENO = 5
FROM PW001C12 LEFT JOIN pw001c02 c02
WHERE ON TRANSACTIONCODEp.rank <> '')= c02.code
LEFT JOIN AND a.datefrom <= periode.PEndPW001P0Y P0Y
AND ISNULL(a.dateto, a.TODATEESTIMATED) >= periode.PStartON p.PIN = P0Y.PIN
LEFT JOIN PW001P0T email
PW001P20 p20 ON p20email.PIN = p.PIN
--AND p20email.HISTORICALTELETYPE = 'F'6
AND P20.DATESTART <= ISNULL(A.DATETO, A.TODATEESTIMATED)NOT EXISTS (
AND COALESCE( SELECT 1
P20.DATEEND, FROM pw001p0t t
( WHERE t.PIN = email.PIN
SELECT MIN(t.DATESTART) -1 AND t.TELETYPE = email.TELETYPE
FROM PW001P20 t AND (
WHERE t.pin = p20.pin t.TELEPRIORITY < email.TELEPRIORITY
AND t.DATESTART > p20.DATESTART ), OR (
a.datefrom t.TELEPRIORITY = email.TELEPRIORITY
) >= A.DATEFROM LEFT JOIN PW001P20 prevcon AND t.SEQUENCENO > email.SEQUENCENO
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 --Activity matching payroll period
ON ISNULL(a.PAYSCALETABLE, p20.PAYSCALETABLE) = pt.REGULATIVECODE JOIN PW001P03 a
AND ( ON a.PIN = p.PIN
PT.INCLUDEPLANNEDACTIVITY = 'Y' AND a.CODE IN (SELECT CODE
OR (PT.INCLUDEPLANNEDACTIVITY <> 'Y' AND a.PLANNED <> 'Y') FROM PW001C12
) WHERE TRANSACTIONCODE LEFT<> JOIN'')
PWPSC001 PS ON AND ISNULL(a.PAYSCALE, p20.PAYSCALECODE) = PS.PAYSCALECODEdatefrom <= periode.PEnd
AND PS.SEQNO = pt.SEQNOISNULL(a.dateto, a.TODATEESTIMATED) >= periode.PStart
LEFT JOIN PWORGVESPW001p20 p20sub
v ON ap20sub.VESSELPIN = v.NUMORGIDp.PIN
LEFT JOIN PW001C43 c43--AND p20.HISTORICAL = 'F'
ON AND c43p20sub.CODEDATESTART <= P.COSTPLACEISNULL(A.DATETO, A.TODATEESTIMATED)
LEFT JOINAND COALESCE(
SELECT C0p20sub.REGULATIVECODEDATEEND,
AS 'Table_Code', (
C0.REGULATIVENAME AS 'Table_Name', SELECT C1.PAYSCALECODE AS 'Payscale_Code',MIN(t.DATESTART) -1
C1.PAYSCALENAME AS 'Payscale_Name' 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
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',
FROM PWPSC000 C0 C0.REGULATIVENAME AS 'Table_Name',
LEFT JOIN PWPSC001 C1 C1.PAYSCALECODE AS 'Payscale_Code',
ON C1.SEQNO = C0.SEQNO C1.PAYSCALENAME AS 'Payscale_Name'
) payscale FROM PWPSC000 C0
ON payscale.Table_Code = p20.PAYSCALETABLE LEFT JOIN PWPSC001 C1
AND payscale.Payscale_Code = p20.PAYSCALECODE LEFT JOIN ( ON C1.SEQNO = C0.SEQNO
SELECT OLE.PIN, ) payscale
DOCS.DOCTYPE, ON payscale.Table_Code = p20.PAYSCALETABLE
DOCS.[DESCRIPTION], AND payscale.Payscale_Code = p20.PAYSCALECODE
LEFT JOIN (
DOCS.SOURCEDOC, SELECT OLE.PIN,
RIGHT(CAST(DOCS.[DESCRIPTION] AS VARCHAR), 6) AS PAYPERIOD, DOCS.DOCTYPE,
--), DOCS.[DESCRIPTION],
CASE DOCS.SOURCEDOC,
WHEN DOCS.[DESCRIPTION] IS NOT NULL THEN 'Y' RIGHT(CAST(DOCS.[DESCRIPTION] AS VARCHAR), 6) AS PAYPERIOD,
ELSE 'N' --),
END AS PAYSLIPSTATUS CASE
FROM PW001P01OLE--left join PW001OLEDOCS PAYSL on PAYSL.pin= pin and DOCTYPE='payslip' and SOURCEDOC like '%periodeused%' WHEN DOCS.[DESCRIPTION] IS NOT NULL THEN 'Y'
OLE LEFT JOIN PW001OLEDOCS DOCS ELSE 'N'
ON END OLE.DOCNOAS =PAYSLIPSTATUS
DOCS.DOCNO FROM PW001P01OLE--left join PW001OLEDOCS PAYSL on PAYSL.pin= pin ANDand DOCS.DOCTYPE = 'payslip' and SOURCEDOC like '%periodeused%'
OLE
--AND DOCS.DESCRIPTION LIKE '%PAYSLIP_202308%' ) PayslipStatus LEFT JOIN PW001OLEDOCS DOCS
ON PayslipStatus.PIN = p.PIN ON OLE.DOCNO = DOCS.DOCNO
AND PayslipStatus.PAYPERIOD = company.PERIODE WHERE ( AND DOCS.DOCTYPE = 'payslip'
( ( --AND DOCS.DESCRIPTION LIKE '%PAYSLIP_202308%'
CHARINDEX(a.CODE, pt.ACTIVITYCODES, 0) > 0 ) PayslipStatus
ON OR ptPayslipStatus.ACTIVITYCODES ISPIN NULL= p.PIN
) AND PayslipStatus.PAYPERIOD = company.PERIODE
WHERE (
) OR (
(
CHARINDEX(a.CODE, PSpt.ACTIVITYCODES, 0) > 0
OR pspt.ACTIVITYCODES IS NULL
)
)
) OR (
) Main WHERE Main.ROW# = 1 (
AND ( CASE CHARINDEX(a.CODE, PS.ACTIVITYCODES, 0) > 0
WHEN main.ROW# = 1 THEN 1 WHENOR main.[Contract Start] = main.[ACTIVITY START] AND main.[Contract End] = main.[Contract End] THEN 1ps.ACTIVITYCODES IS NULL
)
WHEN main.[Contract End] IS NULL )
AND main.[Contract Type] LIKE '%Main%' THEN 1 ELSE)
0 END ) = 1Main
) T
WHERE t.issues <> ''
--AND t.NUMORGID NOT IN (10012602)
|
