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 FROM THEN ISNULL(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 12 THEN LEFT(CAST(company.PERIODE AS VARCHAR), 4)BY p20.PIN,
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 + '01' AS DATETIME AS 'Contract Type',
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 AND ISNULL(pa.dateto, pa.TODATEESTIMATED) >= ('CALCULATE TIME',
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 RIGHT(CAST(company.PERIODE AS VARCHAR), 2)P.Transferbalance = '004' THEN 'Cash'
WHEN P.Transferbalance = '5' THEN 'Citibank Direct Deposit'
WHEN P.Transferbalance = '6' THEN 'Citibank'
LEFT(CAST(company.PERIODE AS VARCHAR), 4) WHEN P.Transferbalance = '7' THEN 'Chase Manhattan'
WHEN P.Transferbalance = '8' THEN 'Hellenic Bank'
+ '01'
WHEN P.Transferbalance = '9' THEN 'Isabel Domestic'
WHEN P.Transferbalance = 'A' THEN 'Isabel International'
WHEN CONVERT(INT, RIGHT(CAST(company.PERIODE AS VARCHAR), 2))
P.Transferbalance = 'B' THEN 'Bank Transfer'
WHEN P.Transferbalance = 'C' THEN 'Swedish Domestic'
WHEN P.Transferbalance = 'D' THEN 'Swedish International'
> 12 THEN LEFT(CAST(company.PERIODE AS VARCHAR), 4) WHEN P.Transferbalance = 'E' THEN 'Philippine Banking'
WHEN P.Transferbalance = 'F' THEN 'Manual Bank'
WHEN P.Transferbalance += '12G' THEN 'Zagrebancka'
WHEN P.Transferbalance = 'H' 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'
END + '01' AS DATETIME 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)'
CAST(WHEN P.Transferbalance = 'S' THEN 'PNC Bank'
WHEN P.Transferbalance = 'T' THEN 'Chase Insight'
CASE WHEN P.Transferbalance = 'U' THEN 'Ocean Pay'
WHEN P.Transferbalance = 'V' THEN 'Banco de WHEN RIGHT(CAST(company.PERIODE AS VARCHAR), 2) = '00' THEN LEFT(CAST(company.PERIODE AS VARCHAR), 4)Oro'
WHEN P.Transferbalance = 'W' THEN 'Bank of Philippine Island'
WHEN P.Transferbalance = 'X' THEN 'CitiDirect (Onboard)'
+ '01' WHEN P.Transferbalance = 'Y' THEN 'Metrobank Direct (PH)'
WHEN CONVERT(INT, RIGHT(CAST(company.PERIODE AS VARCHAR), 2)) > 12 THENP.Transferbalance = 'Z' THEN 'Elektron'
WHEN P.Transferbalance = 'A1' THEN 'E-Banking (Maramut)'
WHEN P.Transferbalance = 'A2' LEFT(CAST(company.PERIODE AS VARCHAR), 4)THEN 'RBS Direct Access'
WHEN P.Transferbalance = 'A3' THEN 'Brazilian Banks'
WHEN P.Transferbalance += '12A4' THEN 'NETS'
WHEN P.Transferbalance = 'A5' THEN 'NONE'
ELSE CAST(company.PERIODE AS VARCHAR(6)) WHEN P.Transferbalance = 'A6' THEN 'JDP Morgan'
WHEN P.Transferbalance = 'A7' THEN 'J. P. Morgan Access'
END + '01' AS DATETIME WHEN P.Transferbalance = 'A8' THEN 'Spar Nord Domestic'
) WHEN P.Transferbalance = 'A9' THEN 'Spar Nord International'
WHEN )P.Transferbalance THEN CAST(
= 'B1' THEN 'Rabobank'
WHEN P.Transferbalance = 'B2' THEN 'Deutsche Bank'
CASE WHEN P.Transferbalance = 'B4' THEN 'ISO20022'
WHEN RIGHT(CAST(company.PERIODE AS VARCHAR), 2)P.Transferbalance = '00B5' THEN LEFT(CAST(company.PERIODE AS VARCHAR), 4)'Brightwell'
END AS 'PAYMENT METHOD',
CASE
+ '01' WHEN ISNULL(
WHEN CONVERT(INT, RIGHT(CAST(company.PERIODE AS VARCHAR), 2)) > 12 THEN ISNULL(pay.CURRENCYFORPAYMENT, p.CURRENCYFORPAYMENT),
p.CURRENCY
LEFT(CAST(company.PERIODE AS VARCHAR), 4) ) = '' THEN company.CURRENCYCODE
ELSE ISNULL(
+ '12' ISNULL(pay.CURRENCYFORPAYMENT, p.CURRENCYFORPAYMENT),
ELSE CAST(company.PERIODE AS VARCHAR(6)) p.CURRENCY
)
END + '01' AS DATETIME END AS 'PAYMENT CURRENCY',
)CASE
WHEN p.EMAILPAYSLIP = 'F' THEN 'Off'
ELSE ( WHEN p.EMAILPAYSLIP = 'T' THEN 'On'
SELECT MIN(DATEFROM) END 'DISTRIBURE PAYSLIP OPTION',
email.TELENO 'EMAIL',
FROM pw001p03 paCASE
WHEN P.EMPLOYMENTSTARTDATE IS NULL THEN 'Employment Start Date missing'
WHERE pa.PIN = p.PIN ELSE ''
END Issue1,
AND pa.datefrom < ( CASE
WHEN P.EMPLOYMENTSTARTDATE > a.DATEFROM THEN
DATEADD( 'Employment Start Date greater than Activity Start Date'
ELSE ''
MM, END Issue2,
CASE
1, WHEN A.DATEFROM < CAST(GETDATE() AS DATE)
AND ISNULL(A.PLANNED, 'N') = 'Y'
CAST( OR A.TODATEESTIMATED < CAST(GETDATE() AS DATE)
AND A.DATETO IS NULL THEN 'Activity not confirmed'
CASE ELSE ''
END Issue3,
WHEN RIGHT(CAST(company.PERIODE AS VARCHAR), 2) = CASE
WHEN NULLIF(A.PAYSCALETABLE, '') IS NOT NULL
AND NULLIF(A.PAYSCALE, '') IS NULL THEN 'Activity Payscale Code missing'
'00' THEN LEFT(CAST(company.PERIODE AS VARCHAR), 4) ELSE ''
END Issue4,
CASE +
'01' WHEN (
NULLIF(a.PAYSCALETABLE, '') IS NOT WHENNULL
CONVERT(INT, RIGHT(CAST(company.PERIODE AS VARCHAR), 2)) AND NULLIF(a.PAYSCALE, '') IS NOT NULL
)
> AND (
(p20.PAYSCALECODE <> a.PAYSCALE)
12 THEN LEFT(CAST(company.PERIODE AS VARCHAR), 4) OR (p20.PAYSCALETABLE <> a.PAYSCALETABLE)
) THEN 'Payscale mismatch between Contract and Activity'
+ '12' ELSE ''
END AS Issue5,
ELSE CAST(company.PERIODE ASCASE VARCHAR(6))
WHEN p20.DATESTART > periode.PEnd THEN 'Invalid Contract Start Date'
ENDELSE + '01'
AS DATETIME END AS Issue6,
CASE
) WHEN p20.CONTRACTKIND IS NULL THEN 'No valid contract for this period'
) -1 ELSE ''
END Issue7,
) CASE
WHEN (
AND ISNULL(pa.dateto, pa.TODATEESTIMATED) >= ( a.CODE IN (SELECT CODE
CAST( FROM PW001C12
CASE WHERE OPTIONS LIKE '%S%')
)
WHEN RIGHT(CAST(company.PERIODE AS VARCHAR), 2) = '00'AND THEN(
a.DATEFROM < (
LEFT(CAST(company.PERIODE AS VARCHAR), 4) CASE
WHEN p20.DATESTART > maincon.DATESTART THEN maincon.DATESTART
+ '01' ELSE p20.DATESTART
WHEN CONVERT(INT, RIGHT(CAST(company.PERIODE AS VARCHAR), 2)) END
)
> 12 THEN LEFT(CAST(company.PERIODE AS VARCHAR), 4)
) THEN 'No contract starting from ' + CONVERT(VARCHAR, a.DATEFROM, 1) + ' to ' + CONVERT(VARCHAR, p20.DATESTART, 1)
ELSE ''
END + '12' Issue8,
CASE
WHEN (a.PAYSCALETABLE IS NULL ELSE CAST(company.PERIODE AS VARCHAR(6))OR a.PAYSCALETABLE = '')
AND (a.PAYSCALE IS NULL OR a.PAYSCALE = '') THEN
END + '01' AS DATETIME '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'
END ELSE ''
), END Issue9
( FROM pw001p01 p
CASE JOIN (
WHEN ( SELECT NUMORGID,
SELECT MAX(ISNULL(pa.DATETO, pa.TODATEESTIMATED)) PERIODE,
CAST(
FROM pw001p03 pa CASE
WHERE pa.PIN = p.PIN WHEN RIGHT(CAST(PERIODE AS VARCHAR), 2) = '00' THEN LEFT(CAST(PERIODE AS VARCHAR), 4) AND
pa.datefrom < ( +
DATEADD( '01'
MM, WHEN CONVERT(INT, RIGHT(CAST(PERIODE AS VARCHAR), 2)) > 12 THEN LEFT(CAST(PERIODE AS VARCHAR), 4)
1, + '12'
CAST( ELSE CAST(PERIODE AS VARCHAR(6))
CASE END + '01' AS DATETIME
) AS 'PStart',
WHEN RIGHT(CAST(company.PERIODE AS VARCHAR), 2) = DATEADD(
MM,
'00' THEN LEFT(CAST(company.PERIODE AS VARCHAR), 4) 1,
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(PERIODE AS VARCHAR), 4)
12 THEN LEFT(CAST(company.PERIODE AS VARCHAR), 4) + '12'
+ '12' ELSE CAST(PERIODE AS VARCHAR(6))
END + '01' AS DATETIME
ELSE CAST(company.PERIODE AS VARCHAR(6)) )
END + '01') -1 AS DATETIME'PEnd'
FROM PWORGCMP
) WHERE PAYROLLENABLED = 'Y'
UNION ALL
) -1 SELECT NUMORGID,
) PERIODE,
AND ISNULL(pa.dateto, pa.TODATEESTIMATED) >= (CAST(
CASE
CAST( WHEN RIGHT(CAST(PERIODE AS VARCHAR), 2) = '00' THEN LEFT(CAST(PERIODE AS VARCHAR), 4)
CASE +
WHEN RIGHT(CAST(company.PERIODE AS VARCHAR), 2) = '00' THEN '01'
WHEN CONVERT(INT, RIGHT(CAST(PERIODE AS VARCHAR), 2)) > 12 THEN LEFT(CAST(company.PERIODE AS VARCHAR), 4)
+ '12'
+ '01' ELSE CAST(PERIODE AS VARCHAR(6))
WHEN CONVERT(INT, RIGHT(CAST(company.PERIODE AS VARCHAR), 2)) END + '01' AS DATETIME
) AS 'PStart',
> 12 THEN LEFT(CAST(company.PERIODE AS VARCHAR), 4) DATEADD(
MM,
+ '12' 1,
ELSE CAST(company.PERIODE
AS VARCHAR(6)) CASE
END + '01' AS DATETIME 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'
DATEADD( MM, ELSE CAST(PERIODE AS VARCHAR(6))
1, END + '01' AS DATETIME
CAST( )
) -1 AS CASE'PEnd'
FROM PWORGVES
WHEN RIGHT(CAST(company.PERIODE AS VARCHAR), 2) = '00' THEN LEFT(CAST(company.PERIODE AS VARCHAR),WHERE 4) PAYROLLENABLED = 'Y'
) periode
ON + '01'
p.CLIENT = periode.NUMORGID
JOIN PWORG ORGAN
ON WHEN CONVERT(INT, RIGHT(CAST(company.PERIODE AS VARCHAR), 2)) > 12 THEN p.CLIENT = ORGAN.NUMORGID
JOIN PWORGCMP company
ON p.CLIENT = company.NUMORGID
LEFT(CAST(company.PERIODE AS VARCHAR), 4) LEFT JOIN PW001P0P p0p
ON p0p.PIN = p.PIN
+ '12' AND p0p.PNUMBER = 'A'
AND p0p.PAYSCALECODE IS NOT NULL
ELSE CAST(company.PERIODE AS VARCHAR(6)) AND p0p.PAYSCALECODE <> ''
LEFT JOIN PW001PAY pay
END + '01' AS DATETIME ON pay.PIN = p.PIN
) AND pay.CalculateTime IS NOT NULL
AND pay.PERIODEUSED = company.PERIODE
) -1 AND NOT EXISTS(
) THEN ( SELECT 1
DATEADD( FROM PW001PAY PAY2
MM, WHERE PAY.PIN = PAY2.PIN
1, AND PAY2.PERIODEUSED = company.PERIODE
CAST( AND (
CASE PAY2.CALCULATETIME > PAY.CALCULATETIME
WHEN RIGHT(CAST(company.PERIODE AS VARCHAR), 2) = '00' THEN LEFT(CAST(company.PERIODE AS VARCHAR), 4)OR (
PAY2.CALCULATETIME = PAY.CALCULATETIME
+ '01' WHEN CONVERT(INT, RIGHT(CAST(company.PERIODE AS VARCHAR), 2)) > 12 THEN--AND PAY2.UUID > PAY.UUID
)
LEFT(CAST(company.PERIODE AS VARCHAR), 4) )
+)
'12' LEFT JOIN PW001P1R p1r5
ON p1r5.PIN ELSE= CAST(company.PERIODE AS VARCHAR(6))p.PIN
AND p1r5.RATENO = 5
ENDLEFT +JOIN '01'pw001c02 ASc02
DATETIME ON p.rank = c02.code
) LEFT JOIN PW001P0Y P0Y
ON p.PIN = )P0Y.PIN
-1 LEFT JOIN PW001P0T email
) ON email.PIN = p.PIN
ELSE ( AND email.TELETYPE = 6
SELECT MAX(ISNULL(pa.DATETO, pa.TODATEESTIMATED)) AND NOT EXISTS (
SELECT 1
FROM pw001p03 pa FROM pw001p0t t
WHERE pa.PIN = p.PIN WHERE t.PIN = email.PIN
AND pa.datefrom < ( AND t.TELETYPE = email.TELETYPE
DATEADD( AND (
t.TELEPRIORITY < email.TELEPRIORITY
MM, OR (
1, t.TELEPRIORITY = email.TELEPRIORITY
CAST( AND t.SEQUENCENO > email.SEQUENCENO
CASE )
WHEN RIGHT(CAST(company.PERIODE AS VARCHAR), 2) = )
)
--Activity matching payroll period
'00' THEN LEFT(CAST(company.PERIODE AS VARCHAR), 4) JOIN PW001P03 a
ON a.PIN = p.PIN
AND a.CODE + '01'
IN (SELECT CODE
FROM PW001C12
WHEN CONVERT(INT, RIGHT(CAST(company.PERIODE AS VARCHAR), 2)) WHERE TRANSACTIONCODE <> '')
AND a.datefrom <= periode.PEnd
>
AND ISNULL(a.dateto, a.TODATEESTIMATED) >= periode.PStart
LEFT JOIN PW001p20 p20sub
ON p20sub.PIN = p.PIN
12 THEN LEFT(CAST(company.PERIODE AS VARCHAR), 4) --AND p20.HISTORICAL = 'F'
AND p20sub.DATESTART <= ISNULL(A.DATETO, A.TODATEESTIMATED)
AND + '12'
p20sub.CONTRACTIDABOVE IS NOT NULL
AND p20sub.DATESTART = A.DATEFROM
AND COALESCE(
ELSE CAST(company.PERIODE AS VARCHAR(6)) p20sub.DATEEND,
(
END + '01' AS DATETIME SELECT MIN(t.DATESTART) -1
) FROM PW001p20 t
WHERE t.pin ) -1= p20sub.pin
AND t.DATESTART > p20sub.DATESTART
) ),
AND ISNULL(pa.dateto, pa.TODATEESTIMATED) >= ( a.datefrom
) >= A.DATEFROM
CAST( --AND ISNULL(p20.DATEEND, ISNULL(A.DATETO, A.TODATEESTIMATED)) >= periode.PStart
--AND CASENOT EXISTS (
-- SELECT 1 FROM PW001p20. z
WHEN RIGHT(CAST(company.PERIODE AS VARCHAR), 2) -- WHERE z.PIN = '00' THEN
p20.PIN AND p20.DATESTART <= ISNULL(A.DATETO, A.TODATEESTIMATED)
-- AND COALESCE(p20.DATEEND,(SELECT 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',
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.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)
+ '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'
UNION ALL
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 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 (MIN(t.DATESTART) -1 FROM PW001p20. t WHERE t.pin = p20.pin AND t.DATESTART > p20.DATESTART), a.DATEFROM) >= A.DATEFROM
SELECT 1 -- AND z.DATESTART > p20.DATESTART)
FROM pw001p0t t --AND (p20.dateend IS NULL OR ISNULL(p20.DATEEND,GETDATE()) >= periode.PStart)
WHERE t.PIN = email.PIN --AND t.TELETYPE = email.TELETYPEISNULL(p20.DATEEND,GETDATE()) >= periode.PStart
AND (
LEFT JOIN PW001p20 maincon
t.TELEPRIORITY < email.TELEPRIORITY ON maincon.PIN = p.PIN
OR ( AND maincon.CONTRACTKIND = 0
t.TELEPRIORITY =AND emailmaincon.TELEPRIORITYCONTRACTIDABOVE IS NULL
AND maincon.DATESTART <= A.DATEFROM--ISNULL(a.DATETO, a.TODATEESTIMATED)
AND t.SEQUENCENO > email.SEQUENCENO AND (maincon.DATEEND IS NULL OR maincon.DATEEND >= ISNULL(a.DATETO, a.TODATEESTIMATED))--IS NULL
) LEFT JOIN PW001p20 p20
) ON p20.SEQUENCENO = ISNULL(p20sub.SEQUENCENO, maincon.SEQUENCENO)
--Activity matching payroll period LEFT JOIN PW001C02 C02CONT
JOIN PW001P03 a ON a p20.PINRANK = p.PINC02CONT.CODE
AND a.CODE IN (SELECT CODE LEFT JOIN PW001C32 C32CONT
FROM PW001C12 ON p20.CONTRACTTYPE = C32CONT.code
WHERELEFT JOIN TRANSACTIONCODEPWPSC000 <>PT
'') AND a.datefrom <= periode.PEnd ON AND ISNULL(a.datetoPAYSCALETABLE, ap20.TODATEESTIMATEDPAYSCALETABLE) >= periodept.PStartREGULATIVECODE
LEFT JOIN PW001P20 p20 AND (
ON p20.PIN = p.PIN
--AND p20.HISTORICAL = 'F' AND P20.DATESTART <= ISNULL(A.DATETO, A.TODATEESTIMATED) PT.INCLUDEPLANNEDACTIVITY = 'Y'
AND COALESCE( P20.DATEEND, OR (PT.INCLUDEPLANNEDACTIVITY <> 'Y' AND a.PLANNED <> 'Y')
( SELECT MIN(t.DATESTART)
-1 LEFT JOIN PWPSC001 FROMPS
PW001P20 t ON WHERE t.pinISNULL(a.PAYSCALE, p20.PAYSCALECODE) = p20.pin
PS.PAYSCALECODE
AND tPS.DATESTARTSEQNO >= p20pt.DATESTARTSEQNO
), LEFT JOIN PWORGVES v
a.datefrom ON ) >a.VESSEL = Av.DATEFROMNUMORGID
LEFT JOIN PW001P20 prevcon LEFT JOIN PW001C43 c43
ON prevcon.SEQUENCENO = p20.CONTRACTIDABOVE ON LEFTc43.CODE JOIN= PW001C02P.COSTPLACE
C02CONT ON p20.RANK = C02CONT.CODE LEFT JOIN (
LEFT JOIN PW001C32 C32CONT ONSELECT p20.CONTRACTTYPE = C32CONT.codeC0.REGULATIVECODE AS 'Table_Code',
LEFT JOIN PWPSC000 PT ON ISNULL(a.PAYSCALETABLE, p20.PAYSCALETABLE) = pt.REGULATIVECODEC0.REGULATIVENAME AS 'Table_Name',
AND ( PTC1.INCLUDEPLANNEDACTIVITYPAYSCALECODE =AS 'YPayscale_Code',
OR (PT.INCLUDEPLANNEDACTIVITY <> 'Y' AND a.PLANNED <> 'Y') C1.PAYSCALENAME AS 'Payscale_Name'
) LEFT JOIN PWPSC001 PS FROM PWPSC000 C0
ON ISNULL(a.PAYSCALE, p20.PAYSCALECODE) = PS.PAYSCALECODE AND PS.SEQNO = pt.SEQNO LEFT JOIN PWORGVES v PWPSC001 C1
ON a.VESSEL = v.NUMORGID LEFTON JOIN PW001C43C1.SEQNO c43= C0.SEQNO
ON c43.CODE = P.COSTPLACE ) payscale
LEFT JOIN ( ON SELECT C0payscale.REGULATIVECODE AS 'Table_Code', = p20.PAYSCALETABLE
AND C0payscale.REGULATIVENAMEPayscale_Code AS 'Table_Name',= p20.PAYSCALECODE
LEFT JOIN (
C1.PAYSCALECODE AS 'Payscale_Code', SELECT C1.PAYSCALENAME AS 'Payscale_Name'OLE.PIN,
FROM PWPSC000 C0 DOCS.DOCTYPE,
LEFT JOIN PWPSC001 C1 DOCS.[DESCRIPTION],
ON C1.SEQNO = C0.SEQNO ) payscale DOCS.SOURCEDOC,
ON payscale.Table_Code = p20.PAYSCALETABLE AND payscale.Payscale_Code = p20.PAYSCALECODE --RIGHT(CAST(DOCS.[DESCRIPTION] AS VARCHAR), 6) AS PAYPERIOD,
LEFT JOIN ( SELECT OLE.PIN, CASE
DOCS.DOCTYPE, WHEN DOCS.[DESCRIPTION],
ISNUMERIC(LEFT(RIGHT(CAST(DOCS.SOURCEDOC AS VARCHAR), 11), 6)) = 1 THEN
DOCS.SOURCEDOC, --LEFT(RIGHT(CAST(DOCS.[DESCRIPTION]SOURCEDOC AS VARCHAR), 11), 6) AS PAYPERIOD,
CASE ELSE 0
WHEN ISNUMERIC(LEFT(RIGHT(CAST(DOCS.SOURCEDOC AS VARCHAR), 11), 6)) = 1 THEN LEFT(RIGHT(CAST(DOCS.SOURCEDOC AS VARCHAR), 11), 6) END AS PAYPERIOD,
ELSE 0 --),
END AS PAYPERIOD, CASE
--), CASEWHEN DOCS.[DESCRIPTION] IS NOT NULL THEN 'Y'
WHEN DOCS.[DESCRIPTION] IS NOT NULL THEN 'Y' ELSE 'N'
ELSE 'N' END AS PAYSLIPSTATUS
END 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.DOCTYPE = 'payslip'DOCNO
--AND DOCS.DESCRIPTIONDOCTYPE LIKE= '%PAYSLIP_202308%payslip'
) PayslipStatus ON PayslipStatus.PIN = p.PIN --AND PayslipStatusDOCS.PAYPERIODDESCRIPTION = company.PERIODELIKE '%PAYSLIP_202308%'
WHERE ( ) PayslipStatus
( ON PayslipStatus.PIN = p.PIN
( AND PayslipStatus.PAYPERIOD = CHARINDEX(a.CODE, pt.ACTIVITYCODES, 0) > 0company.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 WHEN main.ROW# = 1OR THENps.ACTIVITYCODES 1IS NULL
WHEN main.[Contract Start] = main.[ACTIVITY START] AND main.[Contract End] = main.[Contract End] THEN 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 IN (10012602) = 1 |
