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',
CASE-- p0p.PAYSCALETABLE 'PD PAYSCALE TABLE',
WHEN p20.CONTRACTKIND = 0 THEN-- 'Main Contract' p0p.PAYSCALECODE 'PD PAYSCALE',
WHEN p20.CONTRACTKIND = 1 THEN 'Sub Contract' ROW_NUMBER() OVER(
PARTITION BY p20.PIN ORDER WHENBY p20.CONTRACTKINDPIN,
= 2 THEN 'Ammendment' p20.CONTRACTKIND DESC
ELSE '' ) END AS ROW#,
AS 'Contract Type', CASE
C32CONT.Text 'Contract Name', WHEN C02CONT.NAMEp20.CONTRACTKIND = 0 THEN 'Main Contract Rank',
p20.DATESTART 'Contract Start', WHEN p20.CONTRACTKIND = 1 THEN 'Sub Contract'
p20.DATEEND 'Contract End', WHEN p20.CONTRACTKIND = 2 THEN 'Ammendment'
p20.PAYSCALETABLE 'CONTRACT PAYSCALE TABLE', ELSE ''
p20.PAYSCALECODE 'CONTRACT PAYSCALE', END payscale.Table_NameAS 'CONTRACT PAYSCALE TABLE NAMEContract Type',
payscaleC32CONT.Payscale_NameText 'CONTRACTContract PAYSCALE NAMEName',
payC02CONT.CALCULATEDBYNAME 'CALCULATEDContract BYRank',
payp20.CalculateTimeDATESTART 'CALCULATEContract TIMEStart',
CASEp20.DATEEND 'Contract End',
p20.PAYSCALETABLE 'CONTRACT PAYSCALE TABLE',
WHEN P.Transferbalance = '0' THEN 'Carry Forward To Next Month' p20.PAYSCALECODE 'CONTRACT PAYSCALE',
WHEN P.Transferbalance = '1' THEN 'Transfer To Bank Account' payscale.Table_Name 'CONTRACT PAYSCALE TABLE NAME',
payscale.Payscale_Name 'CONTRACT PAYSCALE NAME',
WHEN P.Transferbalance = '2' THEN 'Interpay' pay.CALCULATEDBY 'CALCULATED BY',
WHEN P.Transferbalance = '3' THEN 'Alpha Credit Bank' pay.CalculateTime 'CALCULATE TIME',
WHEN P.Transferbalance = '4' THENCASE 'Cash'
WHEN P.Transferbalance = '50' THEN 'Citibank Direct DepositCarry Forward To Next Month'
WHEN P.Transferbalance = '61' THEN 'CitibankTransfer To Bank Account'
WHEN P.Transferbalance = '72' THEN 'Chase ManhattanInterpay'
WHEN P.Transferbalance = '83' THEN 'HellenicAlpha Credit Bank'
WHEN P.Transferbalance = '94' THEN 'Isabel DomesticCash'
WHEN P.Transferbalance = 'A5' THEN 'IsabelCitibank Direct InternationalDeposit'
WHEN P.Transferbalance = 'B6' THEN 'Bank TransferCitibank'
WHEN P.Transferbalance = 'C7' THEN 'SwedishChase DomesticManhattan'
WHEN P.Transferbalance = 'D8' THEN 'SwedishHellenic InternationalBank'
WHEN P.Transferbalance = 'E9' THEN 'PhilippineIsabel BankingDomestic'
WHEN P.Transferbalance = 'FA' THEN 'ManualIsabel BankInternational'
WHEN P.Transferbalance = 'GB' THEN 'ZagrebanckaBank Transfer'
WHEN P.Transferbalance = 'HC' THEN 'INGSwedish BankDomestic'
WHEN P.Transferbalance = 'ID' THEN 'Direct Deposit E-MoneeSwedish International'
WHEN P.Transferbalance = 'JE' THEN 'DeutschePhilippine BankBanking'
WHEN P.Transferbalance = 'KF' THEN 'SACSManual Bank'
WHEN P.Transferbalance = 'LG' THEN 'Hellenic CyprusZagrebancka'
WHEN P.Transferbalance = 'MH' THEN 'MTING 100Bank'
WHEN P.Transferbalance = 'NI' THEN 'Citibank (PL)Direct Deposit E-Monee'
WHEN P.Transferbalance = '0J' THEN 'AgentDeutsche .RBank'
WHEN P.Transferbalance = 'PK' THEN 'German BankSACS'
WHEN P.Transferbalance = 'QL' THEN 'TridentHellenic TrustCyprus'
WHEN P.Transferbalance = 'RM' THEN 'CitibankMT (Asia)100'
WHEN P.Transferbalance = 'SN' THEN 'PNCCitibank Bank(PL)'
WHEN P.Transferbalance = 'T0' THEN 'ChaseAgent Insight.R'
WHEN P.Transferbalance = 'UP' THEN 'OceanGerman PayBank'
WHEN P.Transferbalance = 'VQ' THEN 'BancoTrident de OroTrust'
WHEN P.Transferbalance = 'WR' THEN 'Bank of Philippine IslandCitibank (Asia)'
WHEN P.Transferbalance = 'XS' THEN 'CitiDirectPNC (Onboard)Bank'
WHEN P.Transferbalance = 'YT' THEN 'Metrobank Direct (PH)Chase Insight'
WHEN P.Transferbalance = 'ZU' THEN 'ElektronOcean Pay'
WHEN P.Transferbalance = 'A1V' THEN 'E-Banking (Maramut)Banco de Oro'
WHEN P.Transferbalance = 'A2W' THEN 'RBSBank of DirectPhilippine AccessIsland'
WHEN P.Transferbalance = 'A3X' THEN 'BrazilianCitiDirect Banks(Onboard)'
WHEN P.Transferbalance = 'A4Y' THEN 'NETSMetrobank Direct (PH)'
WHEN P.Transferbalance = 'A5Z' THEN 'NONEElektron'
WHEN P.Transferbalance = 'A6A1' THEN 'JDP MorganE-Banking (Maramut)'
WHEN P.Transferbalance = 'A7A2' THEN 'J.RBS P.Direct Morgan Access'
WHEN P.Transferbalance = 'A8A3' THEN 'SparBrazilian Nord DomesticBanks'
WHEN P.Transferbalance = 'A9A4' THEN 'Spar Nord InternationalNETS'
WHEN P.Transferbalance = 'B1A5' THEN 'RabobankNONE'
WHEN P.Transferbalance = 'B2A6' THEN 'DeutscheJDP BankMorgan'
WHEN P.Transferbalance = 'B4A7' THEN 'ISO20022J. P. Morgan Access'
WHEN P.Transferbalance = 'B5A8' THEN 'BrightwellSpar Nord Domestic'
END WHEN P.Transferbalance = 'A9' THEN 'Spar ASNord International'PAYMENT METHOD',
WHEN payP.CURRENCYFORPAYMENTTransferbalance = 'B1'PAYMENT THEN CURRENCY','Rabobank'
CASE WHEN P.Transferbalance = 'B2' THEN 'Deutsche Bank'
WHEN pP.EMAILPAYSLIPTransferbalance = 'FB4' THEN 'OffISO20022'
WHEN pP.EMAILPAYSLIPTransferbalance = 'TB5' THEN 'OnBrightwell'
END AS 'DISTRIBUREPAYMENT PAYSLIP OPTIONMETHOD',
emailpay.TELENOCURRENCYFORPAYMENT 'EMAILPAYMENT CURRENCY',
CASE
WHEN Pp.EMPLOYMENTSTARTDATEEMAILPAYSLIP IS= NULL'F' THEN 'EmploymentOff'
Start Date missing' WHEN p.EMAILPAYSLIP = 'T' ELSETHEN 'On'
END 'DISTRIBURE PAYSLIP OPTION',
Issue1, email.TELENO 'EMAIL',
CASE CASE
WHEN P.EMPLOYMENTSTARTDATE > a.DATEFROM THEN WHEN P.EMPLOYMENTSTARTDATE IS NULL THEN 'Employment Start Date greater than Activity Start Datemissing'
ELSE ''
END Issue2Issue1,
CASE
WHEN AP.DATEFROMEMPLOYMENTSTARTDATE < CAST(GETDATE() AS DATE)> a.DATEFROM THEN
AND ISNULL(A.PLANNED, 'N') = 'Y'
'Employment Start Date greater than Activity Start Date'
ELSE ''
OR A.TODATEESTIMATED < CAST(GETDATE() AS DATE) END Issue2,
AND A.DATETO IS NULL THEN 'Activity not confirmed' CASE
ELSE '' WHEN A.DATEFROM < CAST(GETDATE() AS DATE)
END Issue3, AND ISNULL(A.PLANNED, 'N') = 'Y'
CASE WHENOR NULLIF(A.PAYSCALETABLE, ''TODATEESTIMATED < CAST(GETDATE() IS NOT NULLAS DATE)
AND NULLIF(A.PAYSCALE, '') DATETO IS NULL THEN 'Activity Payscale Code missing'not confirmed'
ELSE ''
END Issue3,
Issue4, CASE
WHEN NULLIF(A.PAYSCALETABLE, '') IS NOT NULL
AND NULLIF(aA.PAYSCALETABLEPAYSCALE, '') IS NOT NULL THEN 'Activity Payscale Code missing'
AND NULLIF(a.PAYSCALE, ELSE '')
IS NOT NULL END )Issue4,
CASE
AND ( WHEN (
(p20.PAYSCALECODE <> a.PAYSCALE) NULLIF(a.PAYSCALETABLE, '') IS NOT NULL
OR (p20.PAYSCALETABLE <> a.PAYSCALETABLE) ) THEN 'Payscale mismatch between Contract and Activity'AND NULLIF(a.PAYSCALE, '') IS NOT NULL
ELSE '' )
END AS Issue5, AND (
CASE (p20.PAYSCALECODE <> a.PAYSCALE)
WHEN p20.DATESTART > periode.PEnd THEN 'Invalid Contract Start Date' OR (p20.PAYSCALETABLE <> a.PAYSCALETABLE)
ELSE '' END AS) Issue6,THEN 'Payscale mismatch between Contract and Activity'
CASE ELSE ''
WHEN p20.CONTRACTKIND IS NULL THEN 'No valid contract for this period' 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 > maincon.DATESTART THEN maincon.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)
ELSE '' END + '12'
Issue7, CASE WHEN (a.CODE IN (SELECT CODE FROM PW001C12 WHERE OPTIONS LIKE '%S%')) ANDELSE CAST(a.DATEFROMPERIODE <AS (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)VARCHAR(6))
END ELSE+ '01' AS DATETIME
END ) AS Issue8,'PStart',
CASE WHEN (a.CODE IN (SELECT CODE FROM PW001C12 WHERE OPTIONS LIKE '%S%')) AND (a.DATEFROM > p20.DATESTART) THEN 'Contract started on ' + CONVERT(VARCHAR, p20.DATESTART, 1) + ' instead of ' + CONVERT(VARCHAR, a.DATEFROM, 1)DATEADD(
MM,
ELSE '' END Issue9 1,
FROM pw001p01 p JOIN CAST(
SELECT NUMORGID, CASE
PERIODE, WHEN RIGHT(CAST(PERIODE AS VARCHAR), 2) = '00' THEN LEFT(CAST(PERIODE AS 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
+ LEFT(CAST(PERIODE AS VARCHAR), 4)
'01' + '12'
WHEN CONVERT(INT, RIGHT(CAST(PERIODE AS VARCHAR), 2)) > 12 THEN ELSE CAST(PERIODE AS VARCHAR(6))
LEFT(CAST(PERIODE AS VARCHAR), 4) END + '01' AS DATETIME
+ '12' )
ELSE CAST(PERIODE AS VARCHAR(6)) ) -1 AS 'PEnd'
END + '01' AS DATETIME FROM PWORGCMP
) AS 'PStart', WHERE PAYROLLENABLED = 'Y'
DATEADD( UNION ALL
SELECT MMNUMORGID,
1PERIODE,
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 + ELSE CAST(PERIODE AS VARCHAR(6))'01' AS DATETIME
) AS 'PStart',
END + '01' AS DATETIME DATEADD(
) MM,
) -1 AS 'PEnd' FROM PWORGCMP 1,
WHERE PAYROLLENABLED = 'Y' CAST(
UNION ALL CASE
SELECT NUMORGID, PERIODE, WHEN RIGHT(CAST(PERIODE AS VARCHAR), 2) = '00' THEN LEFT(CAST(PERIODE AS VARCHAR), 4)
CAST( + '01'
CASE WHEN CONVERT(INT, RIGHT(CAST(PERIODE AS VARCHAR), 2)) => '00'12 THEN LEFT(CAST(PERIODE AS VARCHAR), 4)
LEFT(CAST(PERIODE AS VARCHAR), 4)
+ + '0112'
WHEN CONVERT(INT, RIGHT( ELSE CAST(PERIODE AS VARCHAR), 2)) > 12 THEN(6))
END + '01' AS DATETIME
LEFT(CAST(PERIODE AS VARCHAR), 4) )
+ '12' ) -1 AS 'PEnd'
ELSE CAST(PERIODE AS VARCHAR(6)) FROM PWORGVES
WHERE ENDPAYROLLENABLED += '01Y'
AS DATETIME ) periode
) AS 'PStart', ON p.CLIENT = periode.NUMORGID
DATEADD( JOIN PWORG ORGAN
MM, ON p.CLIENT = ORGAN.NUMORGID
JOIN PWORGCMP company
1, ON p.CLIENT = company.NUMORGID
CAST( LEFT JOIN PW001P0P p0p
CASE ON p0p.PIN = p.PIN
AND p0p.PNUMBER = 'A'
WHEN RIGHT(CAST(PERIODE AS VARCHAR), 2) = '00' THEN LEFT(CAST(PERIODE AS VARCHAR), 4) AND p0p.PAYSCALECODE IS NOT NULL
AND p0p.PAYSCALECODE +<> '01'
LEFT JOIN PW001PAY pay
WHEN CONVERT(INT, RIGHT(CAST(PERIODE AS VARCHAR), 2)) > 12 THEN ON pay.PIN = p.PIN
AND pay.CalculateTime IS NOT NULL
LEFT(CAST(PERIODE AS VARCHAR), 4) AND pay.PERIODEUSED = company.PERIODE
AND NOT EXISTS(
+ '12' SELECT 1
ELSE CAST(PERIODE AS VARCHAR(6)) FROM PW001PAY PAY2
END + '01' AS DATETIME WHERE PAY.PIN = PAY2.PIN
) AND PAY2.PERIODEUSED = company.PERIODE
) -1 AS 'PEnd' AND (
FROM PWORGVES WHERE PAYROLLENABLED = 'Y' PAY2.CALCULATETIME > PAY.CALCULATETIME
) periode ON p.CLIENT = periode.NUMORGIDOR (
JOIN PWORG ORGAN ON pPAY2.CLIENTCALCULATETIME = ORGANPAY.NUMORGIDCALCULATETIME
JOIN PWORGCMP company ON --AND pPAY2.CLIENTUUID => company.NUMORGIDPAY.UUID
LEFT JOIN PW001P0P p0p )
ON p0p.PIN = p.PIN AND p0p.PNUMBER = 'A' )
AND p0p.PAYSCALECODE IS NOT NULL )
AND p0p.PAYSCALECODE <> '' LEFT JOIN PW001P1R p1r5
LEFT JOIN PW001PAY pay ON p1r5.PIN = p.PIN
ON pay.PIN = p.PIN AND p1r5.RATENO = 5
AND pay.CalculateTime IS NOT NULL LEFT JOIN pw001c02 c02
AND pay.PERIODEUSED = company.PERIODE ON p.rank = c02.code
AND NOT EXISTS( LEFT JOIN PW001P0Y P0Y
SELECT 1 ON p.PIN = P0Y.PIN
FROM PW001PAY PAY2 LEFT JOIN PW001P0T email
WHEREON PAYemail.PIN = PAY2p.PIN
AND email.TELETYPE = 6
AND PAY2.PERIODEUSED = company.PERIODE AND NOT EXISTS (
AND ( SELECT 1
PAY2.CALCULATETIME > PAY.CALCULATETIME FROM pw001p0t t
OR ( WHERE t.PIN = email.PIN
AND PAY2t.CALCULATETIMETELETYPE = PAYemail.CALCULATETIMETELETYPE
AND (
) t.TELEPRIORITY )< email.TELEPRIORITY
) OR (
LEFT JOIN PW001P1R p1r5 ON p1r5.PIN = p.PIN t.TELEPRIORITY = email.TELEPRIORITY
AND p1r5.RATENO = 5 LEFT JOIN pw001c02 c02 AND t.SEQUENCENO > email.SEQUENCENO
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 --Activity matching payroll period
AND NOT EXISTS ( JOIN PW001P03 a
SELECT 1ON a.PIN = p.PIN
AND FROMa.CODE IN (SELECT pw001p0tCODE
t WHERE t.PIN = email.PIN FROM PW001C12
AND t.TELETYPE = email.TELETYPE WHERE TRANSACTIONCODE <> '')
AND (a.datefrom <= periode.PEnd
AND ISNULL(a.dateto, a.TODATEESTIMATED) >= periode.PStart
t.TELEPRIORITY < email.TELEPRIORITY LEFT JOIN PW001p20 p20sub
ORON ( p20sub.PIN = p.PIN
--AND p20.HISTORICAL = 'F'
t.TELEPRIORITY = email.TELEPRIORITY AND p20sub.DATESTART <= ISNULL(A.DATETO, A.TODATEESTIMATED)
AND COALESCE(
AND t.SEQUENCENO > email.SEQUENCENO p20sub.DATEEND,
) (
) SELECT MIN(t.DATESTART) -1
) FROM PW001p20 t
--Activity matching payroll period JOIN PW001P03 a WHERE t.pin = p20sub.pin
ON a.PIN = p.PIN AND at.CODEDATESTART IN (SELECT CODE> p20sub.DATESTART
),
FROM PW001C12 a.datefrom
WHERE TRANSACTIONCODE <> '') ) >= A.DATEFROM
AND a.datefrom <= periode.PEnd --AND AND ISNULL(p20.DATEEND, ISNULL(aA.datetoDATETO, aA.TODATEESTIMATED)) >= periode.PStart
LEFT JOIN PW001P20 p20 --AND NOT EXISTS (
ON p20.PIN = p.PIN -- SELECT 1 FROM PW001p20. z
AND P20.DATESTART <= ISNULL(A.DATETO, A.TODATEESTIMATED) -- WHERE z.PIN = p20.PIN AND p20.DATESTART AND<= COALESCEISNULL(A.DATETO, A.TODATEESTIMATED)
P20-- 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)
SELECT MIN(t.DATESTART) -1 --AND (p20.dateend IS NULL OR ISNULL(p20.DATEEND,GETDATE()) >= periode.PStart)
FROM PW001P20 t --AND ISNULL(p20.DATEEND,GETDATE()) >= periode.PStart
WHERE t.pin = p20.pin
LEFT JOIN PW001p20 ANDmaincon
t.DATESTART > p20.DATESTART ON maincon.PIN = p.PIN
), AND maincon.CONTRACTKIND = 0
a.datefrom AND maincon.CONTRACTIDABOVE IS NULL
) >= A.DATEFROM LEFT JOIN PW001p20 LEFTp20
JOIN PW001P20 prevcon ON prevconp20.SEQUENCENO = p20.CONTRACTIDABOVEISNULL(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',
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 DOCS.[DESCRIPTION] IS NOT NULL THEN 'Y'
CASE ELSE 'N'
WHEN DOCS.[DESCRIPTION] IS NOT NULL THEN 'Y' END AS PAYSLIPSTATUS
ELSE 'N' FROM PW001P01OLE--left join PW001OLEDOCS PAYSL on PAYSL.pin= pin and DOCTYPE='payslip' and SOURCEDOC like '%periodeused%'
END AS PAYSLIPSTATUS OLE
FROM PW001P01OLE LEFT JOIN PW001OLEDOCS DOCS
OLE ON OLE.DOCNO = DOCS.DOCNO
LEFT JOIN PW001OLEDOCS DOCS AND DOCS.DOCTYPE = 'payslip'
ON OLE.DOCNO = DOCS.DOCNO --AND DOCS.DOCTYPEDESCRIPTION =LIKE 'payslip%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
) T
WHERE t.issues <> ''
--AND t.NUMORGID NOT IN (10012602)
|
