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 (
CASE WHEN a.DATEFROM > periode.PStart THEN a.DATEFROM
WHEN (ELSE periode.PStart
END,
SELECT MIN(DATEFROM) CASE
WHEN ISNULL(a.DATETO, a.TODATEESTIMATED) < periode.PEnd THEN FROMISNULL(a.DATETO, a.TODATEESTIMATED)
pw001p03 pa ELSE periode.PEnd
WHERE pa.PIN = p.PIN END
) + 1 AS 'ACTIVITY DAYS',
AND pa.datefrom < (
SELECT NAME
FROM pworg
DATEADD( WHERE NUMORGID = a.VESSEL
) 'ACTIVITY VESSEL',
MM, (
SELECT NAME
FROM pworg
1, WHERE orgtype = 4
AND numorgid = (
CAST( SELECT NUMORGIDABOVE
CASE FROM pworg
WHERE orgtype = 5
WHEN RIGHT(CAST(company.PERIODE AS VARCHAR), 2) = AND numorgid = a.NUMORGID
)
'00' THEN LEFT(CAST(company.PERIODE AS VARCHAR), 4)) 'ACTIVITY DEPARTMENT',
(
SELECT NAME
FROM pworg
+ '01' WHERE orgtype = 5
AND numorgid = a.NUMORGID
WHEN CONVERT(INT, RIGHT(CAST(company.PERIODE AS VARCHAR), 2)) ) '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,
12 THEN LEFT(CAST(company.PERIODE AS VARCHAR), 4) p20.CONTRACTKIND DESC
) AS ROW#,
CASE
+ '12' WHEN p20.CONTRACTKIND = 0 THEN 'Main Contract'
WHEN p20.CONTRACTKIND = 1 THEN 'Sub Contract'
ELSE CAST(company.PERIODE AS VARCHAR(6)) WHEN p20.CONTRACTKIND = 2 THEN 'Ammendment'
ELSE ''
END END +AS '01Contract Type',
AS DATETIME C32CONT.Text 'Contract Name',
C02CONT.NAME 'Contract Rank',
p20.DATESTART )'Contract Start',
p20.DATEEND 'Contract End',
p20.PAYSCALETABLE 'CONTRACT PAYSCALE TABLE',
) -1 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',
AND ISNULL(pa.dateto, pa.TODATEESTIMATED) >= ( CASE
WHEN P.Transferbalance = '0' THEN 'Carry Forward To Next Month'
CAST( WHEN P.Transferbalance = '1' THEN 'Transfer To Bank Account'
WHEN P.Transferbalance = '2' THEN 'Interpay'
CASE WHEN P.Transferbalance = '3' THEN 'Alpha Credit Bank'
WHEN P.Transferbalance = '4' THEN 'Cash'
WHEN RIGHT(CAST(company.PERIODE AS VARCHAR), 2)WHEN P.Transferbalance = '005' THEN 'Citibank Direct Deposit'
WHEN P.Transferbalance = '6' THEN 'Citibank'
WHEN P.Transferbalance = '7' THEN 'Chase Manhattan'
LEFT(CAST(company.PERIODE AS VARCHAR), 4) WHEN P.Transferbalance = '8' THEN 'Hellenic Bank'
WHEN P.Transferbalance = '9' THEN 'Isabel Domestic'
+ '01' WHEN P.Transferbalance = 'A' THEN 'Isabel International'
WHEN P.Transferbalance = 'B' THEN 'Bank Transfer'
WHEN CONVERT(INT, RIGHT(CAST(company.PERIODE AS VARCHAR), 2))
P.Transferbalance = 'C' THEN 'Swedish Domestic'
WHEN P.Transferbalance = 'D' THEN 'Swedish International'
WHEN P.Transferbalance = 'E' THEN 'Philippine Banking'
> 12 THEN LEFT(CAST(company.PERIODE AS VARCHAR), 4) WHEN P.Transferbalance = 'F' THEN 'Manual Bank'
WHEN P.Transferbalance = 'G' THEN 'Zagrebancka'
WHEN P.Transferbalance += '12H' THEN 'ING Bank'
WHEN P.Transferbalance = 'I' THEN 'Direct Deposit E-Monee'
ELSE CAST(company.PERIODE AS VARCHAR(6))
WHEN P.Transferbalance = 'J' THEN 'Deutsche Bank'
WHEN P.Transferbalance = 'K' THEN 'SACS'
WHEN P.Transferbalance = 'L' ENDTHEN +'Hellenic '01Cyprus'
AS DATETIME 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'
CAST( WHEN P.Transferbalance = 'T' THEN 'Chase Insight'
WHEN P.Transferbalance = 'U' THEN 'Ocean Pay'
CASE WHEN P.Transferbalance = 'V' THEN 'Banco de Oro'
WHEN RIGHT(CAST(company.PERIODE AS VARCHAR), 2)P.Transferbalance = '00W' THEN LEFT(CAST(company.PERIODE AS VARCHAR), 4)'Bank of Philippine Island'
WHEN P.Transferbalance = 'X' THEN 'CitiDirect (Onboard)'
WHEN P.Transferbalance += '01Y' THEN 'Metrobank Direct (PH)'
WHEN P.Transferbalance = 'Z' THEN 'Elektron'
WHEN CONVERT(INT, RIGHT(CAST(company.PERIODE AS VARCHAR), 2)) > 12 THEN WHEN P.Transferbalance = 'A1' THEN 'E-Banking (Maramut)'
WHEN P.Transferbalance = 'A2' THEN 'RBS Direct Access'
WHEN LEFT(CAST(company.PERIODE AS VARCHAR), 4)
P.Transferbalance = 'A3' THEN 'Brazilian Banks'
WHEN P.Transferbalance = 'A4' THEN 'NETS'
WHEN P.Transferbalance += '12A5' THEN 'NONE'
WHEN P.Transferbalance = 'A6' THEN 'JDP Morgan'
ELSE CAST(company.PERIODE AS VARCHAR(6)) WHEN P.Transferbalance = 'A7' THEN 'J. P. Morgan Access'
WHEN P.Transferbalance = 'A8' THEN 'Spar Nord Domestic'
END + '01' AS DATETIME WHEN P.Transferbalance = 'A9' THEN 'Spar Nord International'
) WHEN P.Transferbalance = 'B1' THEN 'Rabobank'
WHEN )P.Transferbalance THEN CAST(
= 'B2' THEN 'Deutsche Bank'
WHEN P.Transferbalance = 'B4' THEN 'ISO20022'
CASE WHEN P.Transferbalance = 'B5' THEN 'Brightwell'
END WHEN RIGHT(CAST(company.PERIODE AS VARCHAR), 2) = '00' THEN LEFT(CAST(company.PERIODE AS VARCHAR), 4)'PAYMENT METHOD',
CASE
WHEN ISNULL(
+ '01' ISNULL(pay.CURRENCYFORPAYMENT, p.CURRENCYFORPAYMENT),
WHEN CONVERT(INT, RIGHT(CAST(company.PERIODE AS VARCHAR), 2)) > 12 THEN p.CURRENCY
) = '' THEN company.CURRENCYCODE
LEFT(CAST(company.PERIODE AS VARCHAR), 4) ELSE ISNULL(
ISNULL(pay.CURRENCYFORPAYMENT, p.CURRENCYFORPAYMENT),
+ '12' p.CURRENCY
ELSE CAST(company.PERIODE AS VARCHAR(6))
END AS 'PAYMENT CURRENCY',
END + '01' AS DATETIME CASE
WHEN p.EMAILPAYSLIP = 'F' THEN 'Off'
) WHEN p.EMAILPAYSLIP = 'T' THEN 'On'
ELSE ( END 'DISTRIBURE PAYSLIP OPTION',
email.TELENO 'EMAIL',
SELECT MIN(DATEFROM) CASE
WHEN P.EMPLOYMENTSTARTDATE IS FROMNULL THEN 'Employment pw001p03Start paDate missing'
ELSE ''
WHERE pa.PIN = p.PINEND Issue1,
CASE
AND pa.datefrom < ( WHEN P.EMPLOYMENTSTARTDATE > a.DATEFROM THEN
'Employment Start Date greater than Activity Start Date'
DATEADD( ELSE ''
END Issue2,
MM, CASE
WHEN A.DATEFROM < CAST(GETDATE() AS DATE)
1, AND ISNULL(A.PLANNED, 'N') = 'Y'
OR A.TODATEESTIMATED < CAST(GETDATE() AS DATE)
CAST( AND A.DATETO IS NULL THEN 'Activity not confirmed'
ELSE ''
CASE END Issue3,
CASE
WHEN RIGHTNULLIF(CAST(company.PERIODE AS VARCHAR), 2) =
A.PAYSCALETABLE, '') IS NOT NULL
AND NULLIF(A.PAYSCALE, '') IS NULL THEN 'Activity Payscale Code missing'
ELSE '00'
THEN LEFT(CAST(company.PERIODE AS VARCHAR), 4) END Issue4,
CASE
+ '01' WHEN (
NULLIF(a.PAYSCALETABLE, '') IS NOT NULL
WHEN CONVERT(INT, RIGHT(CAST(company.PERIODE AS VARCHAR), 2)) AND NULLIF(a.PAYSCALE, '') IS NOT NULL
)
AND (
> (p20.PAYSCALECODE <> a.PAYSCALE)
12 THENOR LEFT(CAST(companyp20.PERIODEPAYSCALETABLE AS<> VARCHARa.PAYSCALETABLE),
4) ) THEN 'Payscale mismatch between Contract and Activity'
ELSE ''
+ '12' END AS Issue5,
CASE
ELSE CAST(company.PERIODE AS VARCHAR(6)) WHEN p20.DATESTART > periode.PEnd THEN 'Invalid Contract Start Date'
ELSE ''
END END + '01' AS DATETIMEIssue6,
CASE
WHEN p20.CONTRACTKIND IS NULL THEN 'No valid contract )for this period'
ELSE ''
END ) -1 Issue7,
CASE
) WHEN (
a.CODE IN (SELECT CODE
AND ISNULL(pa.dateto, pa.TODATEESTIMATED) >= ( FROM PW001C12
CAST( WHERE OPTIONS LIKE '%S%')
CASE )
AND (
WHEN RIGHT(CAST(company.PERIODE AS VARCHAR), 2) = '00' THEN a.DATEFROM < (
CASE
LEFT(CAST(company.PERIODE AS VARCHAR), 4) WHEN p20.DATESTART > maincon.DATESTART THEN maincon.DATESTART
+ '01' ELSE p20.DATESTART
END
WHEN CONVERT(INT, RIGHT(CAST(company.PERIODE AS VARCHAR), 2)) )
) THEN 'No contract starting from ' + CONVERT(VARCHAR, a.DATEFROM, 1) + ' to ' + CONVERT(VARCHAR, p20.DATESTART, 1)
> 12 THEN LEFT(CAST(company.PERIODE AS VARCHAR), 4) ELSE ''
END Issue8,
+ '12'CASE
WHEN (a.PAYSCALETABLE IS NULL OR a.PAYSCALETABLE = '')
ELSEAND CAST(company.PERIODE AS VARCHAR(6))
(a.PAYSCALE IS NULL OR a.PAYSCALE = '') THEN
'Activity Payscale Code and Activity Payscale Table are missing.'
END + '01' AS DATETIME 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 END Issue9
FROM pw001p01 ),p
JOIN (
CASE SELECT NUMORGID,
WHEN ( PERIODE,
SELECT MAX(ISNULL(pa.DATETO, pa.TODATEESTIMATED)) CAST(
CASE
FROM pw001p03 pa WHEN RIGHT(CAST(PERIODE WHEREAS pa.PINVARCHAR), 2) = p.PIN
'00' THEN LEFT(CAST(PERIODE AS VARCHAR), 4)
AND pa.datefrom < ( +
DATEADD( '01'
WHEN CONVERT(INT, RIGHT(CAST(PERIODE AS VARCHAR), 2)) > 12 THEN LEFT(CAST(PERIODE AS MMVARCHAR), 4)
+ '12'
1, ELSE CAST(PERIODE AS VARCHAR(6))
CAST( END + '01' AS DATETIME
CASE ) AS 'PStart',
DATEADD(
WHEN RIGHT(CAST(company.PERIODE AS VARCHAR), 2) = MM,
1,
'00' THEN LEFT(CAST(company.PERIODE AS VARCHAR), 4) CAST(
+ '01'
CASE
WHEN CONVERT(INT, RIGHT(CAST(company.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(company.PERIODE AS VARCHAR), 4)
+ '12'
ELSE CAST(PERIODE 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
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 ''
END Issue4,
CASE
WHEN (
NULLIF(a.PAYSCALETABLE, '') IS NOT NULL
AND NULLIF(a.PAYSCALE, '') IS NOT NULL
)
AND (
(p20.PAYSCALECODE <> a.PAYSCALE)
OR (p20.PAYSCALETABLE <> a.PAYSCALETABLE)
) THEN 'Payscale mismatch between Contract and Activity'
ELSE ''
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>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)AS VARCHAR(6))
-- ELSE '' --END + '01' AS DATETIME
Issue9 FROM pw001p01 p )
JOIN ( SELECT NUMORGID, ) -1 AS 'PEnd'
PERIODE, FROM PWORGCMP
CAST( WHERE PAYROLLENABLED = 'Y'
CASEUNION ALL
SELECT NUMORGID,
WHEN RIGHT(CAST(PERIODE AS VARCHAR), 2) = '00' THEN LEFT(CAST(PERIODE AS VARCHAR), 4) PERIODE,
+ CAST(
CASE
'01' WHEN CONVERT(INT, RIGHT(CAST(PERIODE AS VARCHAR), 2)) >= 12'00' THEN LEFT(CAST(PERIODE AS VARCHAR), 4)), 4)
+
+ '1201'
WHEN ELSE CONVERT(INT, RIGHT(CAST(PERIODE AS VARCHAR(6))), 2)) > 12 THEN LEFT(CAST(PERIODE AS VARCHAR), 4)
END + '0112'
AS DATETIME ) AS 'PStart', ELSE CAST(PERIODE AS VARCHAR(6))
DATEADD( END + '01' AS DATETIME
MM, ) AS 'PStart',
1, DATEADD(
CAST( MM,
CASE 1,
WHEN RIGHT(CAST(PERIODE AS VARCHAR), 2) = '00' THEN LEFT(CAST(PERIODE AS VARCHAR), 4) CAST(
CASE
+ '01' WHEN CONVERT(INT, RIGHT(CAST(PERIODE AS VARCHAR), 2)) >= 12'00' THEN LEFT(CAST(PERIODE AS VARCHAR), 4)
4) + '1201'
ELSE WHEN CONVERT(INT, RIGHT(CAST(PERIODE AS VARCHAR(6), 2)) > 12 THEN LEFT(CAST(PERIODE AS VARCHAR), 4)
END + '01' AS DATETIME + '12'
) ) -1 AS 'PEnd'ELSE CAST(PERIODE AS VARCHAR(6))
FROM PWORGCMP END WHERE PAYROLLENABLED = 'Y'
+ '01' AS DATETIME
UNION ALL )
SELECT NUMORGID, ) -1 AS 'PEnd'
PERIODE, FROM PWORGVES
CAST( WHERE PAYROLLENABLED = 'Y'
CASE ) periode
WHEN RIGHT(CAST(PERIODE AS VARCHAR), 2) = '00' THEN LEFT(CAST(PERIODE AS VARCHAR), 4) ON p.CLIENT = periode.NUMORGID
JOIN PWORG ORGAN
+ ON p.CLIENT = ORGAN.NUMORGID
JOIN PWORGCMP company
'01' ON p.CLIENT = company.NUMORGID
LEFT JOIN PW001P0P p0p
WHEN CONVERT(INT, RIGHT(CAST(PERIODE AS VARCHAR), 2)) > 12 THEN LEFT(CAST(PERIODE AS VARCHAR), 4) ON p0p.PIN = p.PIN
AND p0p.PNUMBER = 'A'
+ '12' AND p0p.PAYSCALECODE IS NOT NULL
ELSE CAST(PERIODE AS VARCHAR(6)) AND p0p.PAYSCALECODE <> ''
LEFT JOIN PW001PAY pay
END + '01' AS DATETIME ON pay.PIN = p.PIN
) AS 'PStart', AND pay.CalculateTime IS NOT NULL
DATEADD( AND pay.PERIODEUSED = company.PERIODE
AND NOT EXISTS(
MM, SELECT 1
1, FROM PW001PAY PAY2
CAST( WHERE PAY.PIN = PAY2.PIN
CASE AND PAY2.PERIODEUSED = company.PERIODE
WHEN RIGHT(CAST(PERIODE AS VARCHAR), 2) = '00' THEN LEFT(CAST(PERIODE AS VARCHAR), 4) AND (
+ '01' PAY2.CALCULATETIME > PAY.CALCULATETIME
WHEN CONVERT(INT, RIGHT(CAST(PERIODE AS VARCHAR), 2)) > 12 THEN LEFT(CAST(PERIODE AS VARCHAR), 4) OR (
+ '12' PAY2.CALCULATETIME = PAY.CALCULATETIME
ELSE CAST(PERIODE AS VARCHAR(6)) --AND PAY2.UUID > PAY.UUID
END + '01' AS DATETIME )
) )
) -1 AS 'PEnd' )
FROM PWORGVES LEFT JOIN PW001P1R p1r5
WHERE PAYROLLENABLED = 'Y' ON p1r5.PIN = p.PIN
) periode AND p1r5.RATENO = 5
ON p.CLIENT = periode.NUMORGID LEFT JOIN pw001c02 c02
JOIN PWORG ORGAN ON p.CLIENTrank = ORGAN.NUMORGIDc02.code
LEFT JOIN PW001P0Y P0Y
PWORGCMP company ON p.CLIENTPIN = company.NUMORGIDP0Y.PIN
LEFT JOIN PW001P0T email
PW001P0P p0p ON p0pemail.PIN = p.PIN
AND p0p.PNUMBER = 'A'
AND p0pemail.PAYSCALECODETELETYPE IS= NOT6
NULL AND p0p.PAYSCALECODE <>AND ''NOT EXISTS (
LEFT JOIN PW001PAY pay SELECT 1
ON pay.PIN = p.PIN FROM AND pay.CalculateTime ISpw001p0t NOTt
NULL AND pay.PERIODEUSED = company.PERIODE WHERE t.PIN = email.PIN
AND NOT EXISTS( AND t.TELETYPE = email.TELETYPE
SELECT 1 FROM PW001PAY PAY2 AND (
WHERE PAY.PIN = PAY2.PIN t.TELEPRIORITY < email.TELEPRIORITY
AND PAY2.PERIODEUSED = company.PERIODE OR (
AND ( PAY2t.CALCULATETIMETELEPRIORITY >= PAY.CALCULATETIMEemail.TELEPRIORITY
OR ( AND t.SEQUENCENO > email.SEQUENCENO
PAY2.CALCULATETIME = PAY.CALCULATETIME )
)
--AND PAY2.UUID > PAY.UUID )
) --Activity matching payroll period
JOIN PW001P03 a
) ON a.PIN = p.PIN
) LEFT JOIN PW001P1RAND p1r5a.CODE IN (SELECT CODE
ON p1r5.PIN = p.PIN FROM PW001C12
AND p1r5.RATENO = 5 LEFT JOIN pw001c02 c02 WHERE TRANSACTIONCODE <> '')
ON p.rank = c02.code AND a.datefrom <= LEFTperiode.PEnd
JOIN PW001P0Y P0Y ON p.PIN = P0Y.PINAND ISNULL(a.dateto, a.TODATEESTIMATED) >= periode.PStart
LEFT JOIN PW001p20 PW001P0Tp20sub
email ON emailp20sub.PIN = p.PIN
--AND emailp20.TELETYPEHISTORICAL = 'F'
6 AND NOT EXISTS (
p20sub.DATESTART <= ISNULL(A.DATETO, A.TODATEESTIMATED)
AND p20sub.CONTRACTIDABOVE IS NOT NULL
SELECT 1 AND p20sub.DATESTART = A.DATEFROM
FROM pw001p0t t AND COALESCE(
WHERE t.PIN = email.PIN p20sub.DATEEND,
AND t.TELETYPE = email.TELETYPE (
AND ( SELECT MIN(t.DATESTART) -1
FROM PW001p20 t.TELEPRIORITY
< email.TELEPRIORITY WHERE t.pin = p20sub.pin
OR ( AND t.DATESTART > p20sub.DATESTART
t.TELEPRIORITY = email.TELEPRIORITY ),
AND t.SEQUENCENO > emaila.SEQUENCENOdatefrom
) >= A.DATEFROM
) --AND ISNULL(p20.DATEEND, ISNULL(A.DATETO, A.TODATEESTIMATED)) >= periode.PStart
) --AND NOT EXISTS (
) -- SELECT 1 FROM PW001p20. z
--Activity matching payroll period JOIN PW001P03 a
-- WHERE z.PIN = p20.PIN AND p20.DATESTART <= ISNULL(A.DATETO, A.TODATEESTIMATED)
ON a.PIN = p.PIN -- AND COALESCE(p20.DATEEND,(SELECT MIN(t.DATESTART) -1 FROM PW001p20. t WHERE t.pin = p20.pin AND t.DATESTART > ANDp20.DATESTART), a.CODEDATEFROM) IN (SELECT CODE>= A.DATEFROM
-- AND z.DATESTART > p20.DATESTART)
FROM PW001C12 --AND (p20.dateend IS NULL OR ISNULL(p20.DATEEND,GETDATE()) >= periode.PStart)
WHERE TRANSACTIONCODE <> '') --AND ISNULL(p20.DATEEND,GETDATE()) >= periode.PStart
AND a.datefrom <= periode.PEnd AND ISNULL(a.dateto,
a.TODATEESTIMATED) >= periode.PStart LEFT JOIN PW001P20PW001p20 maincon
p20 ON p20maincon.PIN = p.PIN
--AND p20maincon.HISTORICALCONTRACTKIND = 'F' 0
AND P20maincon.DATESTART <= ISNULL(A.DATETO, A.TODATEESTIMATED)CONTRACTIDABOVE IS NULL
AND COALESCE( maincon.DATESTART <= A.DATEFROM--ISNULL(a.DATETO, a.TODATEESTIMATED)
AND (maincon.DATEEND IS NULL OR P20maincon.DATEEND, >= ISNULL(a.DATETO, a.TODATEESTIMATED))--IS NULL
LEFT JOIN PW001p20 p20
( ON p20.SEQUENCENO = ISNULL(p20sub.SEQUENCENO, maincon.SEQUENCENO)
SELECT MIN(t.DATESTART) -1 LEFT JOIN PW001C02 C02CONT
FROM PW001P20 t ON p20.RANK = C02CONT.CODE
LEFT JOIN PW001C32 C32CONT
WHERE t.pin = p20.pin ON p20.CONTRACTTYPE = C32CONT.code
AND t.DATESTART >LEFT p20.DATESTARTJOIN PWPSC000 PT
ON ISNULL(a.PAYSCALETABLE, p20.PAYSCALETABLE), = pt.REGULATIVECODE
AND (
a.datefrom ) >= A.DATEFROM PT.INCLUDEPLANNEDACTIVITY = 'Y'
LEFT JOIN PW001P20 prevcon OR (PT.INCLUDEPLANNEDACTIVITY <> 'Y' AND a.PLANNED <> ON'Y')
prevcon.SEQUENCENO = p20.CONTRACTIDABOVE LEFT JOIN PW001C02 C02CONT )
ON p20.RANK =LEFT C02CONT.CODEJOIN PWPSC001 PS
LEFT JOIN PW001C32 C32CONT ON ISNULL(a.PAYSCALE, p20.PAYSCALECODE) = PS.PAYSCALECODE
ON p20.CONTRACTTYPE = C32CONT.code AND PS.SEQNO = pt.SEQNO
LEFT JOIN PWPSC000 PT LEFT JOIN PWORGVES v
ON ISNULL(a.PAYSCALETABLE, p20.PAYSCALETABLE) = pt.REGULATIVECODE ON a.VESSEL = v.NUMORGID
AND ( LEFT JOIN PW001C43 c43
PT.INCLUDEPLANNEDACTIVITY = 'Y' ON c43.CODE = P.COSTPLACE
OR (PT.INCLUDEPLANNEDACTIVITY <> 'Y' AND a.PLANNED <> 'Y')
LEFT JOIN (
) SELECT C0.REGULATIVECODE AS 'Table_Code',
LEFT JOIN PWPSC001 PS ONC0.REGULATIVENAME ISNULL(a.PAYSCALE, p20.PAYSCALECODE) = PS.PAYSCALECODEAS 'Table_Name',
AND PS.SEQNO = pt.SEQNO C1.PAYSCALECODE AS 'Payscale_Code',
LEFT JOIN PWORGVES v ON a.VESSEL = v.NUMORGIDC1.PAYSCALENAME AS 'Payscale_Name'
LEFT JOIN PW001C43 c43 FROM PWPSC000 C0
ON c43.CODE = P.COSTPLACE LEFT JOIN (PWPSC001 C1
SELECT C0.REGULATIVECODE AS 'Table_Code', ON C1.SEQNO = C0.SEQNO
C0.REGULATIVENAME AS 'Table_Name', ) payscale
C1.PAYSCALECODEON AS 'Payscalepayscale.Table_Code', = p20.PAYSCALETABLE
AND payscale.Payscale_Code = C1.PAYSCALENAME AS 'Payscale_Name'p20.PAYSCALECODE
LEFT JOIN (
FROM PWPSC000 C0 SELECT OLE.PIN,
LEFT JOIN PWPSC001 C1 DOCS.DOCTYPE,
ON C1.SEQNO = C0.SEQNO DOCS.[DESCRIPTION],
) payscale ON payscale.Table_Code = p20DOCS.PAYSCALETABLESOURCEDOC,
AND payscale.Payscale_Code = p20.PAYSCALECODE --RIGHT(CAST(DOCS.[DESCRIPTION] AS VARCHAR), 6) AS PAYPERIOD,
LEFT JOIN ( SELECT OLE.PIN, CASE
DOCS.DOCTYPE, WHEN ISNUMERIC(LEFT(RIGHT(CAST(DOCS.SOURCEDOC AS VARCHAR), 11), 6)) = 1 THEN
DOCS.[DESCRIPTION], LEFT(RIGHT(CAST(DOCS.SOURCEDOC, AS VARCHAR), 11), 6)
--RIGHT(CAST(DOCS.[DESCRIPTION] AS VARCHAR), 6) AS PAYPERIOD, ELSE 0
CASE WHEN ISNUMERIC(RIGHT(CAST(DOCS.[DESCRIPTION] AS VARCHAR), 6)) = 1 THEN RIGHT(CAST(DOCS.[DESCRIPTION] AS VARCHAR), 6) ELSE 0 END 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
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 (
(
(
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
--WHERE Main.NUMORGID NOT IN (10012602) |
