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 PAYSCALE TABLEVESSEL],
main.[ACTIVITY PAYSCALE CODEDEPARTMENT],
main.[ContractACTIVITY TypePOSITION],
main.[ContractACTIVITY PAYSCALE NameTABLE],
main.[ContractACTIVITY PAYSCALE RankCODE],
main.[Contract Start]ROW#,
main.[Contract EndType],
main.[CONTRACTContract PAYSCALE TABLEName],
main.[CONTRACTContract PAYSCALERank],
main.[CONTRACT PAYSCALE TABLE NAMEContract Start],
main.[CONTRACTContract PAYSCALE NAMEEnd],
main.[CONTRACT PAYSCALE TABLE],
ISNULL( main.[CONTRACT PAYSCALE],
STUFF( main.[CONTRACT PAYSCALE TABLE NAME],
ISNULL( 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, ', 1,'), '') +
1ISNULL(NULLIF(', ' + main.issue9, ', '), ''),
'' 1,
), 1,
'' ''
) AS [ISSUES] FROM (),
SELECT p.PIN,''
) AS [ISSUES]
p.EMPLOYMENTSTARTDATE, FROM (
SELECT p.EMPLOYMENTENDDATEPIN,
p.CLIENTEMPLOYMENTSTARTDATE,
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',
CASE ROW_NUMBER() OVER(
PARTITION WHENBY p20.CONTRACTKINDPIN =ORDER 0 THEN 'Main Contract'
BY p20.PIN,
WHEN p20.CONTRACTKIND DESC
= 1 THEN 'Sub Contract' ) WHEN p20.CONTRACTKIND = 2 THEN 'Ammendment'AS ROW#,
CASE
ELSE '' WHEN p20.CONTRACTKIND = END0 THEN 'Main Contract'
AS 'Contract Type', WHEN p20.CONTRACTKIND = 1 C32CONT.TextTHEN 'Sub Contract Name','
WHEN C02CONT.NAME 'Contract Rank',p20.CONTRACTKIND = 2 THEN 'Ammendment'
p20.DATESTART 'Contract Start', 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 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, END Issue4,
CASE CASE
WHEN ( WHEN NULLIF(a.PAYSCALETABLE, '') IS NOT NULL(
AND NULLIF(a.PAYSCALEPAYSCALETABLE, '') IS NOT NULL
) AND NULLIF(a.PAYSCALE, '') IS NOT NULL
AND ( (p20.PAYSCALECODE <> a.PAYSCALE) )
OR (p20.PAYSCALETABLE <> a.PAYSCALETABLE) AND (
) THEN 'Payscale mismatch between Contract and Activity' ELSE '' (p20.PAYSCALECODE <> a.PAYSCALE)
END AS Issue5, OR (p20.PAYSCALETABLE <> a.PAYSCALETABLE)
CASE WHEN p20.DATESTART > periode.PEnd THEN 'Invalid Contract Start Date' ) THEN 'Payscale mismatch between Contract and Activity'
ELSE '' ENDELSE AS''
Issue6, CASE END AS Issue5,
WHEN p20.CONTRACTKIND IS NULL THEN 'No valid contract for this period' CASE
ELSE '' ENDWHEN Issue7p20.DATESTART > periode.PEnd THEN 'Invalid Contract Start Date'
FROM pw001p01 p ELSE ''
JOIN ( SELECT NUMORGID, END AS Issue6,
PERIODE, CASE
CAST( WHEN p20.CONTRACTKIND IS NULL THEN 'No valid contract for this period'
CASE ELSE ''
WHEN RIGHT(CAST(PERIODE AS VARCHAR), 2) = '00' THEN LEFT(CAST(PERIODE AS VARCHAR), 4) END Issue7,
+ CASE
WHEN (
'01' WHEN CONVERT(INT, RIGHT(CAST(PERIODE AS VARCHAR), 2)) > 12 THEN LEFT(CAST(PERIODE AS VARCHAR), 4)a.CODE IN (SELECT CODE
+ '12' FROM PW001C12
ELSE CAST(PERIODE AS VARCHAR(6)) WHERE OPTIONS LIKE '%S%')
END + '01' AS DATETIME )
) AS 'PStart', AND (
DATEADD( MM,a.DATEFROM < (
1, CASE
CAST( WHEN p20.DATESTART > maincon.DATESTART THEN maincon.DATESTART
CASE WHEN RIGHT(CAST(PERIODE AS VARCHAR), 2) = '00' THEN LEFT(CAST(PERIODE AS VARCHAR), 4)ELSE p20.DATESTART
END
+ '01' )
WHEN CONVERT(INT, RIGHT(CAST(PERIODE AS VARCHAR), 2)) > 12 THEN LEFT(CAST(PERIODE AS VARCHAR), 4) ) THEN 'No contract starting from ' + CONVERT(VARCHAR, a.DATEFROM, 1) +
+ '12' ' to ' + CONVERT(VARCHAR, p20.DATESTART, 1)
ELSE CAST(PERIODE AS VARCHAR(6)) ELSE ''
END + '01' AS DATETIME END Issue8,
) CASE
) -1 AS 'PEnd' WHEN (a.PAYSCALETABLE IS NULL OR FROMa.PAYSCALETABLE = '')
PWORGCMP WHERE PAYROLLENABLED = 'Y' AND (a.PAYSCALE IS NULL OR a.PAYSCALE = '') THEN
UNION ALL SELECT NUMORGID, 'Activity Payscale Code and Activity Payscale Table are missing.'
PERIODE, WHEN a.PAYSCALETABLE IS NULL
CAST( OR a.PAYSCALETABLE = '' THEN 'Activity Payscale Table is missing'
CASE WHEN a.PAYSCALE IS NULL
WHEN RIGHT(CAST(PERIODE AS VARCHAR), 2) = '00' THEN LEFT(CAST(PERIODE AS VARCHAR), 4) OR a.PAYSCALE = '' THEN 'Activity Payscale Code is missing'
+ ELSE ''
'01' END Issue9
WHEN CONVERT(INT, RIGHT(CAST(PERIODE AS VARCHAR), 2)) > 12 THEN LEFT(CAST(PERIODE AS VARCHAR), 4)FROM pw001p01 p
JOIN (
+ '12' SELECT NUMORGID,
ELSE CAST(PERIODE AS VARCHAR(6)) PERIODE,
END + '01' AS DATETIME CAST(
) AS 'PStart', DATEADD( CASE
MM, WHEN RIGHT(CAST(PERIODE AS VARCHAR), 2) = '00' THEN LEFT(CAST(PERIODE AS 1,VARCHAR), 4)
CAST( +
CASE '01'
WHEN RIGHT(CAST(PERIODE AS VARCHAR), 2) = '00' THEN LEFT(CAST(PERIODE AS VARCHAR), 4) WHEN CONVERT(INT, RIGHT(CAST(PERIODE AS VARCHAR), 2)) > 12 THEN
+ '01' WHEN CONVERT(INT, RIGHT(CAST(PERIODE AS VARCHAR), 2)) > 12 THEN LEFT(CAST(PERIODE AS VARCHAR), 4)
+ '12' + '12'
ELSE CAST(PERIODE AS VARCHAR(6)) ELSE CAST(PERIODE AS VARCHAR(6))
END + '01' AS DATETIME END + '01' AS DATETIME
) ) -1 AS 'PEnd' ) AS 'PStart',
FROM PWORGVES WHEREDATEADD(
PAYROLLENABLED = 'Y' ) periode ON p.CLIENT =MM,
periode.NUMORGID JOIN PWORG ORGAN ON p.CLIENT = ORGAN.NUMORGID 1,
JOIN PWORGCMP company ON p.CLIENT = company.NUMORGID CAST(
LEFT JOIN PW001P0P p0p ON p0p.PIN = p.PIN CASE
AND p0p.PNUMBER = 'A' AND p0p.PAYSCALECODE IS NOT NULL WHEN RIGHT(CAST(PERIODE AS AND p0p.PAYSCALECODE <>VARCHAR), 2) = '00' THEN LEFT(CAST(PERIODE AS VARCHAR), 4)
LEFT JOIN PW001PAY pay ON pay.PIN = p.PIN + '01'
AND pay.CalculateTime IS NOT NULL AND pay.PERIODEUSED = company.PERIODE WHEN AND NOT EXISTS(
CONVERT(INT, RIGHT(CAST(PERIODE AS VARCHAR), 2)) > 12 THEN
SELECT 1 FROM PW001PAY PAY2 LEFT(CAST(PERIODE AS VARCHAR), 4)
WHERE PAY.PIN = PAY2.PIN AND PAY2.PERIODEUSED = company.PERIODE + '12'
AND ( ELSE CAST(PERIODE AS VARCHAR(6))
PAY2.CALCULATETIME > PAY.CALCULATETIME END + '01' AS DATETIME
OR ( )
PAY2.CALCULATETIME = PAY.CALCULATETIME ) -1 AS 'PEnd'
--AND PAY2.UUID > PAY.UUID FROM PWORGCMP
) WHERE PAYROLLENABLED = 'Y'
) ) UNION ALL
LEFT JOIN PW001P1R p1r5 SELECT NUMORGID,
ON p1r5.PIN = p.PIN AND p1r5.RATENO = 5 PERIODE,
LEFT JOIN pw001c02 c02 ON p.rank =CAST(
c02.code LEFT JOIN PW001P0Y P0Y CASE
ON p.PIN = P0Y.PIN LEFT JOIN PW001P0T email WHEN RIGHT(CAST(PERIODE AS VARCHAR), 2) = ON email.PIN = p.PIN
'00' THEN LEFT(CAST(PERIODE AS VARCHAR), 4)
AND email.TELETYPE = 6 AND NOT EXISTS ( +
SELECT 1 FROM pw001p0t t '01'
WHERE t.PIN = email.PIN WHEN CONVERT(INT, RIGHT(CAST(PERIODE AS AND t.TELETYPE = email.TELETYPEVARCHAR), 2)) > 12 THEN
AND ( LEFT(CAST(PERIODE AS VARCHAR), 4)
t.TELEPRIORITY < email.TELEPRIORITY + '12'
OR ( t.TELEPRIORITY = email.TELEPRIORITY
ELSE CAST(PERIODE AS VARCHAR(6))
AND t.SEQUENCENO > email.SEQUENCENOEND + '01' AS DATETIME
) ) AS 'PStart',
) DATEADD(
) --Activity matching payroll period MM,
JOIN PW001P03 a ON a.PIN = p.PIN 1,
AND a.CODE IN (SELECT CODE CAST(
FROM PW001C12 WHERE TRANSACTIONCODE <> '') CASE
AND a.datefrom <= periode.PEnd AND ISNULL(a.dateto, a.TODATEESTIMATED) >= periode.PStart 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 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 p20 PW001p20 maincon
ON p20maincon.PIN = p.PIN
--AND p20maincon.HISTORICALCONTRACTKIND = 'F' 0
AND P20.DATESTART <= ISNULL(A.DATETO, A.TODATEESTIMATED)maincon.CONTRACTIDABOVE IS NULL
LEFT JOIN PW001p20 p20
ON p20.SEQUENCENO = ISNULL(p20sub.SEQUENCENO, maincon.SEQUENCENO)
AND COALESCE( LEFT JOIN PW001C02 C02CONT
P20.DATEEND, ON ( p20.RANK = C02CONT.CODE
SELECT MIN(t.DATESTART) -1
LEFT JOIN PW001C32 C32CONT
FROM PW001P20 t ON p20.CONTRACTTYPE = C32CONT.code
WHERE t.pin = p20.pinLEFT JOIN PWPSC000 PT
ON AND t.DATESTART >ISNULL(a.PAYSCALETABLE, p20.DATESTARTPAYSCALETABLE) = pt.REGULATIVECODE
), AND (
a.datefrom ) >= A.DATEFROM
PT.INCLUDEPLANNEDACTIVITY = 'Y'
LEFT JOIN PW001C02 C02CONT OR (PT.INCLUDEPLANNEDACTIVITY <> 'Y' ON p20.RANK = C02CONT.CODEAND a.PLANNED <> 'Y')
LEFT JOIN PW001C32 C32CONT )
ON p20.CONTRACTTYPE = C32CONT.code LEFT JOIN PWPSC001 PS
LEFT JOIN PWPSC000 PT ON ISNULL(a.PAYSCALETABLEPAYSCALE, p20.PAYSCALETABLEPAYSCALECODE) = pt.REGULATIVECODEPS.PAYSCALECODE
AND (PS.SEQNO = pt.SEQNO
PT.INCLUDEPLANNEDACTIVITY = 'Y' LEFT JOIN PWORGVES v
OR (PT.INCLUDEPLANNEDACTIVITY <> 'Y' AND a.PLANNED <> 'Y') ON a.VESSEL = v.NUMORGID
) LEFT JOIN PW001C43 c43
LEFT JOIN PWPSC001 PS ON ISNULL(a.PAYSCALE, p20.PAYSCALECODE) c43.CODE = PSP.PAYSCALECODECOSTPLACE
AND PS.SEQNO = pt.SEQNO LEFT JOIN (
LEFT JOIN PWORGVES v SELECT C0.REGULATIVECODE AS 'Table_Code',
ON a.VESSEL = v.NUMORGID LEFT JOIN PW001C43 c43 C0.REGULATIVENAME AS 'Table_Name',
ON c43.CODE = P.COSTPLACE LEFT JOIN ( C1.PAYSCALECODE AS 'Payscale_Code',
SELECT C0.REGULATIVECODE AS 'Table_Code', C1.PAYSCALENAME AS 'Payscale_Name'
C0.REGULATIVENAME AS 'Table_Name', FROM PWPSC000 C0
C1.PAYSCALECODE AS 'Payscale_Code', C1.PAYSCALENAMELEFT AS 'Payscale_Name'
JOIN PWPSC001 C1
FROM PWPSC000 C0 ON C1.SEQNO = C0.SEQNO
LEFT JOIN PWPSC001 C1 ) payscale
ON C1.SEQNO = C0.SEQNO ON payscale.Table_Code = p20.PAYSCALETABLE
) payscale ONAND payscale.TablePayscale_Code = p20.PAYSCALETABLEPAYSCALECODE
AND payscale.Payscale_Code = p20.PAYSCALECODE LEFT JOIN (
LEFT JOIN ( SELECT OLE.PIN,
SELECT OLE.PIN, DOCS.DOCTYPE,
DOCS.[DESCRIPTION],
DOCS.SOURCEDOC,
RIGHT(CAST(DOCS.[DESCRIPTION] AS VARCHAR), 6) 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
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 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
) T
WHERE t.issues <> ''
--AND t.NUMORGID NOT IN (10012602)
|
