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 a.DATEFROM > periode.PStart THEN a.DATEFROM
ELSE periode.PStart
END,
CASE
WHEN ISNULL(a.DATETO, a.TODATEESTIMATED) < periode.PEnd THEN ISNULL(a.DATETO, a.TODATEESTIMATED)
ELSE periode.PEnd
END
) + 1 AS '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',
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,
CASE
WHEN NULLIF(A.PAYSCALETABLE, '') IS NOT NULL
AND NULLIF(A.PAYSCALE, '') IS NULL THEN 'Activity Payscale Code missing'
ELSE '' ELSE ''
END Issue4, CASE END Issue4,
WHEN ( CASE
NULLIF(a.PAYSCALETABLE, '') IS NOT NULL WHEN (
AND NULLIF(a.PAYSCALE, '') IS NOT NULL ) NULLIF(a.PAYSCALETABLE, '') IS NOT NULL
AND ( (p20.PAYSCALECODE <> a.PAYSCALE) AND NULLIF(a.PAYSCALE, '') IS NOT NULL
OR (p20.PAYSCALETABLE <> a.PAYSCALETABLE) ) THEN 'Payscale mismatch between Contract and)
Activity' ELSE '' AND (
END AS Issue5, CASE WHEN (p20.DATESTARTPAYSCALECODE ><> periodea.PEndPAYSCALE)
THEN 'Invalid Contract Start Date' ELSE '' END AS Issue6,
OR (p20.PAYSCALETABLE <> a.PAYSCALETABLE)
CASE WHEN p20.CONTRACTKIND IS NULL) THEN 'NoPayscale validmismatch contractbetween forContract thisand periodActivity'
ELSE '' ELSE ''
END Issue7, CASE END AS Issue5,
WHEN ( CASE
a.CODE IN (SELECT CODE WHEN p20.DATESTART > periode.PEnd THEN 'Invalid Contract Start Date'
FROM PW001C12 ELSE ''
WHERE OPTIONS LIKEEND '%S%') AS Issue6,
) CASE
AND ( a.DATEFROM < ( WHEN p20.CONTRACTKIND IS NULL THEN 'No valid contract for this period'
CASE ELSE ''
WHEN p20.DATESTART > prevcon.DATESTART THEN prevcon.DATESTART END Issue7,
ELSE p20.DATESTART CASE
END WHEN (
) ) THEN 'No contract starting from ' + CONVERT(VARCHAR, a.DATEFROM, 1) + ' toa.CODE 'IN + CONVERT(VARCHAR, p20.DATESTART, 1)SELECT CODE
ELSE '' END Issue8, CASEFROM PW001C12
WHEN (a.PAYSCALETABLE IS NULL OR a.PAYSCALETABLE = '') AND (a.PAYSCALE IS NULL OR a.PAYSCALE = '') THEN WHERE OPTIONS LIKE '%S%')
'Activity Payscale Code and Activity Payscale Table are missing.' WHEN a.PAYSCALETABLE IS)
NULL OR a.PAYSCALETABLE = '' THEN 'Activity Payscale Table is missing' AND (
WHEN a.PAYSCALE IS NULL OR a.PAYSCALE = '' THEN 'Activity Payscale Code is missing' a.DATEFROM < (
ELSE '' END Issue9 CASE
FROM pw001p01 p JOIN ( SELECT NUMORGID, WHEN p20.DATESTART > maincon.DATESTART THEN maincon.DATESTART
PERIODE, 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 ''
END WHEN CONVERT(INT, RIGHT(CAST(PERIODE AS VARCHAR)Issue8,
2)) > 12 THEN LEFT(CAST(PERIODE AS VARCHAR), 4) CASE
+ '12' WHEN (a.PAYSCALETABLE IS NULL OR a.PAYSCALETABLE = '')
ELSE CAST(PERIODE ASAND VARCHAR(6))
(a.PAYSCALE IS NULL OR a.PAYSCALE = '') THEN
END + '01' AS DATETIME 'Activity Payscale Code and Activity Payscale Table are missing.'
) AS 'PStart', WHEN a.PAYSCALETABLE IS NULL
DATEADD( OR a.PAYSCALETABLE = '' THEN 'Activity Payscale Table is missing'
MM, WHEN a.PAYSCALE IS NULL
1, OR a.PAYSCALE = '' THEN CAST('Activity Payscale Code is missing'
ELSE ''
CASE END Issue9
WHEN RIGHT(CAST(PERIODE AS VARCHAR), 2) = '00' THEN LEFT(CAST(PERIODE AS VARCHAR), 4)FROM pw001p01 p
JOIN (
+ '01' SELECT NUMORGID,
WHEN CONVERT(INTPERIODE,
RIGHT(CAST(PERIODE AS VARCHAR), 2)) > 12 THEN LEFT(CAST(PERIODE AS VARCHAR), 4) CAST(
+ '12' CASE
ELSE CAST(PERIODE AS VARCHAR(6)) WHEN RIGHT(CAST(PERIODE AS VARCHAR), END2) += '0100' THEN LEFT(CAST(PERIODE AS DATETIME VARCHAR), 4)
) +
) -1 AS 'PEnd' FROM PWORGCMP WHERE PAYROLLENABLED = 'Y01'
UNION ALL SELECT NUMORGID, WHEN CONVERT(INT, RIGHT(CAST(PERIODE AS VARCHAR), 2)) > 12 THEN LEFT(CAST(PERIODE AS VARCHAR), 4)
PERIODE, CAST( + '12'
CASE WHENELSE RIGHT(CAST(PERIODE AS VARCHAR(6), 2)
= '00' THEN LEFT(CAST(PERIODE AS VARCHAR), 4) END + '01' AS DATETIME
+ ) AS '01PStart',
WHEN CONVERT(INT, RIGHTDATEADD(CAST(PERIODE
AS VARCHAR), 2)) > 12 THEN LEFT(CAST(PERIODE AS VARCHAR), 4) MM,
+ '12' 1,
ELSE CAST(PERIODE AS VARCHAR(6)) END + '01' AS DATETIME CAST(
) AS 'PStart', CASE
DATEADD( MM, WHEN RIGHT(CAST(PERIODE AS VARCHAR), 2) = '00' THEN LEFT(CAST(PERIODE AS VARCHAR), 4)
1, CAST( + '01'
CASE WHEN CONVERT(INT, WHEN RIGHT(CAST(PERIODE AS VARCHAR), 2)) => '00'12 THEN LEFT(CAST(PERIODE AS VARCHAR), 4)
+ '01' + '12'
WHEN CONVERT(INT, RIGHT(CAST(PERIODE AS VARCHAR), 2)) > 12 THEN LEFT(CAST(PERIODE AS VARCHAR), 4) ELSE CAST(PERIODE AS VARCHAR(6))
+ '12' END + '01' AS DATETIME
ELSE CAST(PERIODE AS VARCHAR(6)) )
END + '01' AS DATETIME ) -1 AS 'PEnd'
) -1 AS 'PEnd' FROM PWORGCMP
FROM PWORGVES WHERE PAYROLLENABLED = 'Y'
) periode ONUNION p.CLIENTALL
= periode.NUMORGID JOIN PWORG ORGAN SELECT NUMORGID,
ON p.CLIENT = ORGAN.NUMORGID JOIN PWORGCMP company ON p.CLIENTPERIODE,
= company.NUMORGID LEFT JOIN PW001P0P p0p ON p0p.PIN = p.PINCAST(
AND p0p.PNUMBER = 'A' AND p0p.PAYSCALECODE IS NOT NULL CASE AND
p0p.PAYSCALECODE <> '' LEFT JOIN PW001PAY pay ON pay.PIN = p.PIN WHEN RIGHT(CAST(PERIODE AS VARCHAR), 2) = AND pay.CalculateTime IS NOT NULL
'00' THEN LEFT(CAST(PERIODE AS VARCHAR), 4)
AND pay.PERIODEUSED = company.PERIODE AND NOT EXISTS( SELECT 1 +
FROM PW001PAY PAY2 WHERE PAY.PIN = PAY2.PIN '01'
AND PAY2.PERIODEUSED = company.PERIODE WHEN AND (
CONVERT(INT, RIGHT(CAST(PERIODE AS VARCHAR), 2)) > 12 THEN LEFT(CAST(PERIODE AS VARCHAR), 4)
PAY2.CALCULATETIME > PAY.CALCULATETIME + '12'
OR ( ELSE CAST(PERIODE AS VARCHAR(6))
PAY2.CALCULATETIME = PAY.CALCULATETIME END + '01' AS --AND PAY2.UUID > PAY.UUID
DATETIME
) AS 'PStart',
) DATEADD(
) LEFT JOIN PW001P1R p1r5 ONMM,
p1r5.PIN = p.PIN AND p1r5.RATENO = 5 LEFT JOIN pw001c02 c02 1,
ON p.rank = c02.code LEFT JOIN PW001P0Y P0Y CAST(
ON p.PIN = P0Y.PIN LEFT JOIN PW001P0T email ON email.PINCASE =
p.PIN AND email.TELETYPE = 6 AND NOT EXISTS ( SELECT 1
WHEN RIGHT(CAST(PERIODE AS VARCHAR), 2) = '00' THEN LEFT(CAST(PERIODE AS VARCHAR), 4)
FROM pw001p0t t WHERE t.PIN = email.PIN + '01'
AND t.TELETYPE = email.TELETYPE ANDWHEN CONVERT(INT, RIGHT(CAST(PERIODE AS VARCHAR), 2)) > 12 THEN LEFT(CAST(PERIODE AS VARCHAR), 4)
t.TELEPRIORITY < email.TELEPRIORITY + '12'
OR ( 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 p20sub.CONTRACTIDABOVE IS NOT NULL
AND p20sub.DATESTART = A.DATEFROM
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)
t.TELEPRIORITY = email.TELEPRIORITY--AND ISNULL(p20.DATEEND,GETDATE()) >= periode.PStart
AND t.SEQUENCENO > email.SEQUENCENO LEFT JOIN PW001p20 maincon
)ON maincon.PIN = p.PIN
)AND maincon.CONTRACTKIND = 0
) AND --Activity matching payroll periodmaincon.CONTRACTIDABOVE IS NULL
JOIN PW001P03 a AND maincon.DATESTART <= ON a.PIN = p.PINA.DATEFROM--ISNULL(a.DATETO, a.TODATEESTIMATED)
AND a.CODE IN (SELECT CODE AND (maincon.DATEEND IS NULL OR maincon.DATEEND >= ISNULL(a.DATETO, a.TODATEESTIMATED))--IS NULL
FROM PW001C12 LEFT JOIN PW001p20 p20
WHERE TRANSACTIONCODE <> '') ON AND ap20.datefromSEQUENCENO <= periode.PEndISNULL(p20sub.SEQUENCENO, maincon.SEQUENCENO)
AND ISNULL(a.dateto, a.TODATEESTIMATED) >= periode.PStart LEFT JOIN PW001C02 C02CONT
LEFT JOIN PW001P20 p20 ON p20.PINRANK = p.PINC02CONT.CODE
LEFT --AND p20.HISTORICAL = 'F'JOIN PW001C32 C32CONT
AND P20.DATESTART <= ISNULL(A.DATETO, A.TODATEESTIMATED) ON p20.CONTRACTTYPE = ANDC32CONT.code
COALESCE( P20.DATEEND, LEFT JOIN PWPSC000 PT
( ON ISNULL(a.PAYSCALETABLE, p20.PAYSCALETABLE) = pt.REGULATIVECODE
SELECT MIN(t.DATESTART) -1 AND (
FROM PW001P20 t WHERE tPT.pinINCLUDEPLANNEDACTIVITY = p20.pin'Y'
AND t.DATESTART > p20.DATESTART OR (PT.INCLUDEPLANNEDACTIVITY <> 'Y' AND a.PLANNED <> 'Y')
), )
a.datefrom ) >= A.DATEFROM LEFT JOIN PWPSC001 PS
LEFT JOIN PW001P20 prevcon ON ON prevcon.SEQUENCENOISNULL(a.PAYSCALE, p20.PAYSCALECODE) = p20PS.CONTRACTIDABOVEPAYSCALECODE
LEFT JOIN PW001C02 C02CONT ON p20.RANKAND PS.SEQNO = C02CONTpt.CODESEQNO
LEFT JOIN PW001C32 C32CONT LEFT JOIN PWORGVES v
ON p20.CONTRACTTYPE = C32CONT.code ON LEFTa.VESSEL JOIN= PWPSC000v.NUMORGID
PT ON ISNULL(a.PAYSCALETABLE, p20.PAYSCALETABLE) = pt.REGULATIVECODE LEFT JOIN PW001C43 c43
AND ( ON PTc43.INCLUDEPLANNEDACTIVITYCODE = 'Y'P.COSTPLACE
OR (PT.INCLUDEPLANNEDACTIVITY <>LEFT 'Y'JOIN AND(
a.PLANNED <> 'Y') ) SELECT C0.REGULATIVECODE AS 'Table_Code',
LEFT JOIN PWPSC001 PS ON ISNULL(a.PAYSCALE, p20.PAYSCALECODE) = PS.PAYSCALECODE AND PSC0.SEQNOREGULATIVENAME = pt.SEQNOAS 'Table_Name',
LEFT JOIN PWORGVES v ON a.VESSEL = vC1.NUMORGIDPAYSCALECODE AS 'Payscale_Code',
LEFT JOIN PW001C43 c43 ON c43.CODE = P.COSTPLACE C1.PAYSCALENAME AS 'Payscale_Name'
LEFT JOIN ( FROM SELECTPWPSC000 C0.REGULATIVECODE
AS 'Table_Code',
C0.REGULATIVENAME AS 'Table_Name', LEFT JOIN PWPSC001 C1
C1.PAYSCALECODE AS 'Payscale_Code', ON C1.PAYSCALENAMESEQNO AS 'Payscale_Name'= C0.SEQNO
FROM PWPSC000 C0 ) payscale
LEFT JOIN PWPSC001 C1 ON payscale.Table_Code = p20.PAYSCALETABLE
ONAND C1.SEQNOpayscale.Payscale_Code = C0.SEQNOp20.PAYSCALECODE
LEFT )JOIN payscale(
ON payscale.Table_Code = p20.PAYSCALETABLE ANDSELECT payscale.Payscale_Code = p20.PAYSCALECODEOLE.PIN,
LEFT JOIN ( DOCS.DOCTYPE,
SELECT OLE.PIN, DOCS.DOCTYPE, DOCS.[DESCRIPTION],
DOCS.[DESCRIPTION], DOCS.SOURCEDOC,
DOCS.SOURCEDOC, --RIGHT(CAST(DOCS.[DESCRIPTION] AS VARCHAR), 6) AS PAYPERIOD,
CASE
WHEN ISNUMERIC(LEFT(RIGHT(CAST(DOCS.SOURCEDOC AS VARCHAR), 11), 6)) = 1 THEN
LEFT(RIGHT(CAST(DOCS.SOURCEDOC AS VARCHAR), 11), 6), 6)
ELSE 0
ELSE 0 END AS PAYPERIOD,
--),
CASE CASE
WHEN DOCS.[DESCRIPTION] IS NOT NULL THEN 'Y' WHEN DOCS.[DESCRIPTION] IS NOT NULL THEN 'Y'
ELSE 'N' END AS PAYSLIPSTATUSELSE '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 PAYSL on PAYSL.pin= pin and DOCTYPE='payslip' and SOURCEDOC like '%periodeused%'
LEFT JOIN PW001OLEDOCS DOCS ON OLE.DOCNO = DOCS.DOCNO
LEFT JOIN ANDPW001OLEDOCS DOCS.DOCTYPE
= 'payslip' --AND DOCS.DESCRIPTION LIKE '%PAYSLIP_202308%' ON OLE.DOCNO = DOCS.DOCNO
) PayslipStatus ON PayslipStatus.PIN = p.PIN AND PayslipStatusDOCS.PAYPERIODDOCTYPE = company.PERIODE'payslip'
WHERE ( ( --AND DOCS.DESCRIPTION LIKE '%PAYSLIP_202308%'
( ) PayslipStatus
CHARINDEX(a.CODE, pt.ACTIVITYCODES, 0) > 0 ON PayslipStatus.PIN = p.PIN
OR pt.ACTIVITYCODES IS NULL AND PayslipStatus.PAYPERIOD = company.PERIODE
) WHERE (
) (
OR ( (
( CHARINDEX(a.CODE, PSpt.ACTIVITYCODES, 0) > 0
OR pt.ACTIVITYCODES IS NULL
OR ps.ACTIVITYCODES IS NULL )
) )
) ) OR (
) Main WHERE Main.NUMORGID NOT IN (10012602) AND ( (
CASE WHEN main.ROW# = 1 THEN 1 CHARINDEX(a.CODE, PS.ACTIVITYCODES, 0) > 0
WHEN main.[Contract Start] = main.[ACTIVITY START] AND main.[Contract End] =OR mainps.[ContractACTIVITYCODES End]IS THENNULL
1 WHEN main.[Contract End] IS NULL )
AND main.[Contract Type] LIKE '%Main%' AND main.ROW#)
< 3 THEN 1 )
ELSE 0 END ) Main
--WHERE Main.NUMORGID )NOT = 1
IN (10012602) |
