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', WHEN ISNULL(
CASE WHENISNULL(pay.CURRENCYFORPAYMENT, p.EMAILPAYSLIP = 'F' THEN 'Off'CURRENCYFORPAYMENT),
WHEN p.EMAILPAYSLIPCURRENCY
= 'T' THEN 'On' END 'DISTRIBURE PAYSLIP OPTION', ) = '' THEN company.CURRENCYCODE
email.TELENO 'EMAIL', ELSE ISNULL(
CASE WHEN P.EMPLOYMENTSTARTDATE IS NULL THEN 'Employment Start Date missing'ISNULL(pay.CURRENCYFORPAYMENT, p.CURRENCYFORPAYMENT),
ELSE '' p.CURRENCY
END )
Issue1, END CASE AS 'PAYMENT CURRENCY',
WHEN P.EMPLOYMENTSTARTDATE > a.DATEFROM THEN CASE
WHEN p.EMAILPAYSLIP = 'F'Employment StartTHEN Date'Off'
greater than Activity Start Date' WHEN p.EMAILPAYSLIP = 'T' ELSETHEN 'On'
END 'DISTRIBURE PAYSLIP OPTION',
Issue2 email.TELENO 'EMAIL',
CASE
WHEN AP.DATEFROMEMPLOYMENTSTARTDATE < CAST(GETDATE() AS DATE)
IS NULL THEN 'Employment Start Date missing'
AND ISNULL(A.PLANNED,ELSE 'N')
= 'Y' END OR A.TODATEESTIMATEDIssue1,
< CAST(GETDATE() AS DATE) CASE
AND A.DATETO IS NULL THEN 'Activity not confirmed' WHEN P.EMPLOYMENTSTARTDATE > a.DATEFROM THEN
ELSE '' 'Employment Start Date greater than Activity ENDStart Date'
Issue3, ELSE ''
CASE END WHEN NULLIF(A.PAYSCALETABLEIssue2,
'') IS NOT NULL CASE
AND NULLIF(A.PAYSCALE, '') IS NULL THEN 'Activity Payscale Code missing' WHEN A.DATEFROM < CAST(GETDATE() AS DATE)
AND ISNULL(A.PLANNED, ELSE'N') = 'Y'
OR A.TODATEESTIMATED < CAST(GETDATE() AS DATE)
END AND A.DATETO IS Issue4,NULL THEN 'Activity not confirmed'
CASE ELSE ''
END Issue3,
WHEN ( CASE
WHEN NULLIF(aA.PAYSCALETABLE, '') IS NOT NULL
AND NULLIF(aA.PAYSCALE, '') IS NOT NULL THEN 'Activity Payscale Code missing'
ELSE ''
) END Issue4,
CASE
AND ( WHEN (
(p20.PAYSCALECODE <> a.PAYSCALE)
NULLIF(a.PAYSCALETABLE, '') IS NOT NULL
AND NULLIF(a.PAYSCALE, '') IS OR (p20.PAYSCALETABLE <> a.PAYSCALETABLE)NOT NULL
)
) THEN 'Payscale mismatchAND between(
Contract and Activity' (p20.PAYSCALECODE <> a.PAYSCALE)
ELSE '' OR (p20.PAYSCALETABLE <> a.PAYSCALETABLE)
END AS Issue5, ) THEN 'Payscale mismatch between Contract and Activity'
CASE ELSE ''
END AS WHENIssue5,
p20.DATESTART > periode.PEnd THEN 'Invalid Contract Start Date' CASE
WHEN p20.DATESTART ELSE ''
> periode.PEnd THEN 'Invalid Contract Start Date'
END ELSE ''
AS Issue6, END AS Issue6,
CASE
WHEN p20.CONTRACTKIND IS NULL THEN 'No valid contract for this period'
ELSE ''
END Issue7,
Issue7,CASE
WHEN 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) FROM PW001C12
ELSE '' WHERE OPTIONS ENDLIKE '%S%')
Issue8 )
--CASE AND (
-- WHEN (a.CODEDATEFROM IN< (SELECT
CODE FROM PW001C12 WHERE OPTIONS LIKE '%S%')) AND (a.DATEFROM > p20.DATESTART) THEN 'Contract started on ' + CONVERT(VARCHAR, p20.DATESTART, 1) + ' instead ofCASE '
+ CONVERT(VARCHAR, a.DATEFROM, 1) -- ELSE ''
WHEN p20.DATESTART > prevcon.DATESTART THEN prevcon.DATESTART
--END Issue9 ELSE p20.DATESTART
FROM pw001p01 p END
JOIN ( )
) THEN SELECT'No NUMORGID,contract starting from ' + CONVERT(VARCHAR, a.DATEFROM, 1) + ' to ' + CONVERT(VARCHAR, p20.DATESTART, 1)
ELSE ''
PERIODE, END Issue8,
CASE
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
(a.PAYSCALETABLE IS NULL OR a.PAYSCALETABLE = '')
AND (a.PAYSCALE IS NULL OR a.PAYSCALE = '') THEN
WHEN RIGHT(CAST(PERIODE AS VARCHAR), 2) = '00' THEN LEFT(CAST(PERIODE AS VARCHAR), 4)
'Activity Payscale Code and Activity Payscale Table are missing.'
WHEN a.PAYSCALETABLE IS NULL
OR a.PAYSCALETABLE = '' THEN 'Activity Payscale Table + '01'is missing'
WHEN a.PAYSCALE IS NULL
OR a.PAYSCALE = '' THEN 'Activity Payscale Code is missing'
WHEN CONVERT(INT, RIGHT(CAST(PERIODE AS VARCHAR), 2)) > 12 THEN LEFT(CAST(PERIODE AS VARCHAR), 4)ELSE ''
END Issue9
FROM pw001p01 p
JOIN (
+ '12' SELECT NUMORGID,
PERIODE,
ELSE CAST(PERIODE AS VARCHAR(6)) CAST(
ENDCASE +
'01' AS DATETIME WHEN RIGHT(CAST(PERIODE AS VARCHAR), 2) = '00' )THEN LEFT(CAST(PERIODE AS VARCHAR), 4)
) -1 AS 'PEnd' +
FROM PWORGCMP '01'
WHERE PAYROLLENABLED = 'Y' WHEN CONVERT(INT, RIGHT(CAST(PERIODE AS VARCHAR), 2)) > 12 THEN LEFT(CAST(PERIODE UNIONAS ALLVARCHAR), 4)
SELECT NUMORGID, + '12'
PERIODE, ELSE CAST(PERIODE AS VARCHAR(6))
CAST( END + '01' AS DATETIME
CASE ) AS 'PStart',
DATEADD(
WHEN RIGHT(CAST(PERIODE AS VARCHAR), 2) = '00' THEN LEFT(CAST(PERIODE AS VARCHAR), 4) MM,
1,
+ CAST(
'01' CASE
WHEN CONVERT(INT, RIGHT(CAST(PERIODE AS VARCHAR), 2)) >= 12'00' THEN LEFT(CAST(PERIODE AS VARCHAR), 4)
+ '1201'
WHEN ELSE CONVERT(INT, RIGHT(CAST(PERIODE AS VARCHAR(6), 2)) > 12 THEN LEFT(CAST(PERIODE AS VARCHAR), 4)
END + '01' AS DATETIME ) AS 'PStart',+ '12'
DATEADD( ELSE CAST(PERIODE AS VARCHAR(6))
MM, END + '01' AS DATETIME
)
1, ) -1 AS 'PEnd'
CAST( FROM PWORGCMP
WHERE PAYROLLENABLED = 'Y'
CASE UNION ALL
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'00' THEN 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 LEFT(CAST(PERIODE AS VARCHAR), 4)
END + '01' AS DATETIME + '12'
) ELSE CAST(PERIODE AS VARCHAR(6))
-1 AS 'PEnd' END + FROM'01' AS DATETIME
PWORGVES ) WHERE PAYROLLENABLEDAS = 'YPStart',
) periode DATEADD(
ON p.CLIENT = periode.NUMORGID MM,
JOIN PWORG ORGAN 1,
ON p.CLIENT = ORGAN.NUMORGID CAST(
JOIN PWORGCMP company ON p.CLIENT =CASE company.NUMORGID
LEFT JOIN PW001P0P p0p WHEN RIGHT(CAST(PERIODE AS VARCHAR), 2) = '00' ONTHEN p0p.PIN = p.PINLEFT(CAST(PERIODE AS VARCHAR), 4)
AND p0p.PNUMBER = 'A' + '01'
AND p0p.PAYSCALECODE IS NOT NULL AND p0p.PAYSCALECODE <> '' WHEN CONVERT(INT, RIGHT(CAST(PERIODE AS VARCHAR), 2)) > 12 THEN LEFT(CAST(PERIODE AS VARCHAR), 4)
LEFT JOIN PW001PAY pay ON pay.PIN = p.PIN + '12'
AND pay.CalculateTime IS NOT NULL ELSE CAST(PERIODE AS VARCHAR(6))
AND pay.PERIODEUSED = company.PERIODE AND NOT EXISTS( END + '01' AS DATETIME
SELECT 1 )
) FROM-1 AS PW001PAY'PEnd'
PAY2 FROM PWORGVES
WHERE PAY.PIN = PAY2.PIN WHERE PAYROLLENABLED = 'Y'
) periode
ANDON PAY2p.PERIODEUSEDCLIENT = companyperiode.PERIODENUMORGID
JOIN PWORG ORGAN
ON p.CLIENT = ORGAN.NUMORGID
AND ( JOIN PWORGCMP company
ON p.CLIENT = company.NUMORGID
LEFT JOIN PAY2.CALCULATETIMEPW001P0P >p0p
PAY.CALCULATETIME ON p0p.PIN = p.PIN
AND p0p.PNUMBER = 'A'
AND p0p.PAYSCALECODE IS ORNOT (NULL
AND p0p.PAYSCALECODE <> ''
LEFT JOIN PW001PAY pay
ON pay.PIN = PAY2.CALCULATETIME = PAY.CALCULATETIMEp.PIN
AND pay.CalculateTime IS NOT NULL
AND pay.PERIODEUSED = company.PERIODE
AND NOT EXISTS(
--AND PAY2.UUID > PAY.UUID SELECT 1
FROM PW001PAY PAY2
WHERE ) PAY.PIN = PAY2.PIN
AND PAY2.PERIODEUSED = company.PERIODE
) AND (
) LEFT JOIN PW001P1R p1r5 PAY2.CALCULATETIME > PAY.CALCULATETIME
ON p1r5.PIN = p.PIN OR (
AND p1r5.RATENO = 5 LEFT JOIN pw001c02 c02 PAY2.CALCULATETIME = PAY.CALCULATETIME
ON p.rank = c02.code LEFT JOIN PW001P0Y P0Y
--AND PAY2.UUID > PAY.UUID
ON p.PIN = P0Y.PIN )
LEFT JOIN PW001P0T email )
ON email.PIN = p.PIN )
AND email.TELETYPE =LEFT 6JOIN PW001P1R p1r5
ON p1r5.PIN = p.PIN
AND NOT EXISTS ( AND p1r5.RATENO = 5
LEFT JOIN pw001c02 c02
SELECT 1 ON p.rank = c02.code
LEFT JOIN FROMPW001P0Y P0Y
pw001p0t t ON p.PIN = P0Y.PIN
WHERELEFT JOIN t.PIN =PW001P0T email.PIN
ON email.PIN = p.PIN
AND email.TELETYPE = 6
AND t.TELETYPE = email.TELETYPE AND NOT EXISTS (
SELECT 1
AND ( FROM pw001p0t t
WHERE t.PIN = email.PIN
t.TELEPRIORITY < email.TELEPRIORITY AND t.TELETYPE = email.TELETYPE
AND (
OR ( t.TELEPRIORITY < email.TELEPRIORITY
t.TELEPRIORITY = email.TELEPRIORITY OR (
AND t.SEQUENCENOTELEPRIORITY >= email.SEQUENCENOTELEPRIORITY
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
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
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 FROM PWPSC000 C0
LEFT JOIN PWPSC001 C1
ON C1.SEQNO = C0.SEQNO
) payscale
) payscale ON payscale.Table_Code = p20.PAYSCALETABLE
ON AND payscale.TablePayscale_Code = p20.PAYSCALETABLEPAYSCALECODE
LEFT JOIN (
AND payscale.Payscale_Code = p20.PAYSCALECODE SELECT OLE.PIN,
LEFT JOIN ( DOCS.DOCTYPE,
SELECT OLE.PIN, DOCS.[DESCRIPTION],
DOCS.DOCTYPE, DOCS.SOURCEDOC,
--RIGHT(CAST(DOCS.[DESCRIPTION], AS VARCHAR), 6) AS PAYPERIOD,
CASE
DOCS.SOURCEDOC, --WHEN ISNUMERIC(LEFT(RIGHT(CAST(DOCS.[DESCRIPTION]SOURCEDOC AS VARCHAR), 611) AS PAYPERIOD, 6)) = 1 THEN LEFT(RIGHT(CAST(DOCS.SOURCEDOC AS VARCHAR), 11), 6)
CASE WHEN ISNUMERIC(RIGHT(CAST(DOCS.[DESCRIPTION] AS VARCHAR), 6)) = 1 THEN RIGHT(CAST(DOCS.[DESCRIPTION] AS VARCHAR), 6) ELSE 0
END AS PAYPERIOD, END AS PAYPERIOD,
--), --),
CASE CASE
WHEN DOCS.[DESCRIPTION] IS NOT NULL THEN 'Y'
ELSE 'N'
ENDEND AS PAYSLIPSTATUS
FROM PW001P01OLE--left join PW001OLEDOCS PAYSL on PAYSL.pin= pin and DOCTYPE='payslip' and SOURCEDOC like '%periodeused%'
OLE
OLE LEFT JOIN PW001OLEDOCS DOCS
LEFT JOIN PW001OLEDOCS DOCS ON OLE.DOCNO = DOCS.DOCNO
ON OLE.DOCNO =AND DOCS.DOCNODOCTYPE = 'payslip'
--AND DOCS.DOCTYPEDESCRIPTION =LIKE 'payslip%PAYSLIP_202308%'
) PayslipStatus
ON PayslipStatus.PIN = p.PIN
--AND DOCS.DESCRIPTION LIKE '%PAYSLIP_202308%' PayslipStatus.PAYPERIOD = company.PERIODE
WHERE (
(
) PayslipStatus (
ON PayslipStatus.PIN = p.PIN CHARINDEX(a.CODE, pt.ACTIVITYCODES, 0) > 0
AND PayslipStatus.PAYPERIOD = company.PERIODE WHERE (OR pt.ACTIVITYCODES IS NULL
( )
)
( OR (
CHARINDEX(a.CODE, pt.ACTIVITYCODES, 0) > 0 (
OR pt.ACTIVITYCODES IS NULL CHARINDEX(a.CODE, PS.ACTIVITYCODES, 0) > 0
) OR ps.ACTIVITYCODES IS NULL
) )
OR ( )
( )
) Main
WHERE Main.NUMORGID NOT IN (10012602)
AND CHARINDEX(a.CODE,
PS.ACTIVITYCODES, 0) > 0 CASE
ORWHEN psmain.ACTIVITYCODESROW# IS= NULL1 THEN 1
WHEN main.[Contract End] IS NULL
) AND main.[Contract Type] LIKE '%Main%' THEN 1
) ELSE 0 END
) ) = Main1 |