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 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 TABLE],
main.[ACTIVITY PAYSCALE CODE],
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 CASE'Sub Contract'
WHEN p20.CONTRACTKIND = 02 THEN 'Main ContractAmmendment'
WHEN p20.CONTRACTKIND = 1 THEN 'Sub Contract'ELSE ''
END WHEN p20.CONTRACTKIND = 2 THEN 'Ammendment'
ELSE ''
END AS 'Contract 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 Issue3, END Issue3,
CASE CASE
WHEN NULLIF(A.PAYSCALETABLE, '') IS NOT NULL ANDWHEN NULLIF(A.PAYSCALEPAYSCALETABLE, '') IS NOT NULL
THEN 'Activity Payscale Code missing' ELSE '' AND NULLIF(A.PAYSCALE, '') IS NULL THEN 'Activity Payscale Code missing'
END Issue4, ELSE ''
CASE WHENEND ( Issue4,
NULLIF(a.PAYSCALETABLE, '') IS NOT NULL CASE
ANDWHEN NULLIF(a.PAYSCALE,
'') IS NOT NULL ) NULLIF(a.PAYSCALETABLE, '') IS NOT NULL
AND ( AND NULLIF(p20.PAYSCALECODE <> a.PAYSCALE, '') IS NOT NULL
OR (p20.PAYSCALETABLE <> a.PAYSCALETABLE) )
) THEN 'Payscale mismatch between Contract and Activity' AND (
ELSE '' END AS Issue5, (p20.PAYSCALECODE <> a.PAYSCALE)
CASE WHENOR (p20.DATESTARTPAYSCALETABLE ><> periodea.PEndPAYSCALETABLE)
THEN 'Invalid Contract Start Date' ELSE '' ) THEN 'Payscale mismatch between Contract and Activity'
END AS Issue6, CASEELSE ''
WHEN p20.CONTRACTKIND ISEND NULL THEN 'No valid contract for this period'
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>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)
ELSE ''
END Issue8,
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)
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)
+ '12'
ELSE CAST(PERIODE AS VARCHAR(6))
END + '01' AS DATETIME
) AS 'PStart',
DATEADD(
MM,
1,
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
)
) -1 AS 'PEnd'
FROM PWORGCMP
WHERE PAYROLLENABLED = 'Y'
ELSE '' UNION ALL
END Issue7 FROM pw001p01 p SELECT NUMORGID,
JOIN ( SELECT NUMORGIDPERIODE,
PERIODE, CAST(
CAST( CASE
CASE WHEN RIGHT(CAST(PERIODE AS VARCHAR), 2) = '00' THEN LEFT(CAST(PERIODE AS VARCHAR), 4)
+ +
'01' '01'
WHEN CONVERT(INT, RIGHT(CAST(PERIODE AS VARCHAR), 2)) > 12 THEN LEFT(CAST(PERIODE AS VARCHAR), 4) WHEN CONVERT(INT, RIGHT(CAST(PERIODE AS VARCHAR), 2)) > 12 THEN
+ '12' ELSE LEFT(CAST(PERIODE AS VARCHAR(6), 4)
END + '01' AS DATETIME ) AS 'PStart',
DATEADD(+ '12'
MM, ELSE CAST(PERIODE AS VARCHAR(6))
1, END + '01' AS DATETIME
CAST( CASE ) AS 'PStart',
WHEN RIGHTDATEADD(CAST(PERIODE
AS VARCHAR), 2) = '00' THEN LEFT(CAST(PERIODE AS VARCHAR), 4) MM,
+ '01' 1,
WHEN CONVERT(INT, 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) += '01'00' THEN LEFT(CAST(PERIODE AS DATETIMEVARCHAR), 4)
) + '01'
) -1 AS 'PEnd' FROM PWORGCMP WHEN CONVERT(INT, RIGHT(CAST(PERIODE AS VARCHAR), 2)) > 12 WHERETHEN
PAYROLLENABLED = 'Y' UNION ALL SELECT NUMORGID, LEFT(CAST(PERIODE AS VARCHAR), 4)
PERIODE, CAST( + '12'
CASE ELSE CAST(PERIODE AS VARCHAR(6))
WHEN RIGHT(CAST(PERIODE AS VARCHAR), 2) = '00' THEN LEFT(CAST(PERIODE AS VARCHAR), 4) END + '01' AS DATETIME
+ )
'01' ) -1 AS 'PEnd'
WHEN CONVERT(INT, RIGHT(CAST(PERIODE AS VARCHAR), 2)) > 12 THEN LEFT(CAST(PERIODE AS VARCHAR), 4) FROM PWORGVES
+ '12' WHERE PAYROLLENABLED = 'Y'
ELSE CAST(PERIODE AS VARCHAR(6)) ) periode
END + '01' ASON DATETIME p.CLIENT = periode.NUMORGID
JOIN )PWORG ORGAN
AS 'PStart', ON p.CLIENT = DATEADD(ORGAN.NUMORGID
JOIN PWORGCMP company
MM, ON p.CLIENT = company.NUMORGID
1, LEFT JOIN PW001P0P p0p
CAST( ON p0p.PIN = p.PIN
CASE 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 LEFT(CAST(PERIODE AS VARCHAR),ON 4) pay.PIN = p.PIN
AND pay.CalculateTime IS NOT NULL
+ '12' AND pay.PERIODEUSED = company.PERIODE
ELSE CAST(PERIODE AS VARCHAR(6)) AND NOT EXISTS(
END + '01' AS DATETIME SELECT 1
) FROM PW001PAY PAY2
) -1 AS 'PEnd' WHERE FROM PAY.PIN = PWORGVESPAY2.PIN
WHERE PAYROLLENABLED = 'Y' AND PAY2.PERIODEUSED = company.PERIODE
) periode ON p.CLIENT = periode.NUMORGID AND JOIN(
PWORG ORGAN ON p.CLIENT = ORGAN.NUMORGID JOIN PWORGCMP company PAY2.CALCULATETIME > PAY.CALCULATETIME
ON p.CLIENT = company.NUMORGID LEFT JOIN PW001P0P p0p OR (
ON p0p.PIN = p.PIN AND p0p.PNUMBER = 'A' AND p0p.PAYSCALECODE IS NOT NULLPAY2.CALCULATETIME = PAY.CALCULATETIME
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( LEFT JOIN PW001P1R p1r5
SELECT 1 ON p1r5.PIN FROM= p.PIN
PW001PAY PAY2 WHERE AND PAYp1r5.PINRATENO = PAY2.PIN5
LEFT JOIN pw001c02 c02
AND PAY2.PERIODEUSED = company.PERIODE ON p.rank = c02.code
AND ( LEFT JOIN PW001P0Y P0Y
PAY2.CALCULATETIME > PAY.CALCULATETIME ON p.PIN = P0Y.PIN
LEFT JOIN ORPW001P0T (email
ON email.PIN = p.PIN
PAY2.CALCULATETIME = PAY.CALCULATETIME AND email.TELETYPE = 6
--AND PAY2.UUID > PAY.UUID AND NOT EXISTS (
SELECT 1
) )FROM pw001p0t t
) WHERE t.PIN = email.PIN
LEFT JOIN PW001P1R p1r5 ON p1r5.PIN = p.PIN AND t.TELETYPE = email.TELETYPE
AND p1r5.RATENO = 5 LEFT JOIN pw001c02 c02 AND (
ON p.rank = c02.code LEFT JOIN PW001P0Y P0Y t.TELEPRIORITY < email.TELEPRIORITY
ON p.PIN = P0Y.PIN LEFT JOIN PW001P0T email OR (
ON email.PIN = p.PIN AND email.TELETYPE = 6 t.TELEPRIORITY = ANDemail.TELEPRIORITY
NOT EXISTS ( SELECT 1 AND FROMt.SEQUENCENO > email.SEQUENCENO
pw001p0t t WHERE t.PIN = email.PIN )
AND t.TELETYPE = email.TELETYPE )
AND ( )
t.TELEPRIORITY < email.TELEPRIORITY --Activity matching payroll period
OR ( JOIN PW001P03 a
ON ta.TELEPRIORITYPIN = emailp.TELEPRIORITYPIN
AND a.CODE IN (SELECT CODE
AND t.SEQUENCENO > email.SEQUENCENO FROM PW001C12
) WHERE TRANSACTIONCODE <> '')
) ) AND a.datefrom <= periode.PEnd
--Activity matching payroll period AND ISNULL(a.dateto, a.TODATEESTIMATED) >= periode.PStart
JOIN PW001P03 a LEFT JOIN PW001P20 p20
ON a.PIN = p.PIN AND a.CODE IN (SELECTON CODE p20.PIN = p.PIN
FROMAND P20.DATESTART PW001C12
<= ISNULL(A.DATETO, A.TODATEESTIMATED)
AND COALESCE(
WHERE TRANSACTIONCODE <> '') AND a.datefrom <= periode.PEnd P20.DATEEND,
AND ISNULL(a.dateto, a.TODATEESTIMATED) >= periode.PStart LEFT JOIN(
PW001P20 p20 ON p20.PIN = p.PIN SELECT MIN(t.DATESTART) -1
--AND p20.HISTORICAL = 'F' AND P20.DATESTART <= ISNULL(A.DATETO, A.TODATEESTIMATED) FROM PW001P20 t
AND COALESCE( P20.DATEEND, WHERE t.pin = p20.pin
( SELECTAND MIN(t.DATESTART) -1> p20.DATESTART
FROM PW001P20 t ),
WHERE t.pin = p20.pin a.datefrom
AND t.DATESTART > p20.DATESTART ) >= A.DATEFROM
), LEFT JOIN PW001P20 prevcon
a.datefrom ON prevcon.SEQUENCENO = p20.CONTRACTIDABOVE
) >= A.DATEFROM LEFT JOIN PW001C02 C02CONT
LEFT JOIN PW001C02 C02CONT ON 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' = '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'
ELSE 'N'
END AS PAYSLIPSTATUS END AS PAYSLIPSTATUS
FROM PW001P01OLE--left join PW001OLEDOCS PAYSL on PAYSL.pin= pin and DOCTYPE='payslip' and SOURCEDOC likeFROM '%periodeused%' PW001P01OLE
OLE OLE
LEFT JOIN PW001OLEDOCS DOCS LEFT JOIN PW001OLEDOCS DOCS
ON OLE.DOCNO = DOCS.DOCNO ON OLE.DOCNO = DOCS.DOCNO
AND DOCS.DOCTYPE = 'payslip' AND DOCS.DOCTYPE = 'payslip'
--AND DOCS.DESCRIPTION LIKE '%PAYSLIP_202308%' ) PayslipStatus
) 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 <> '' |