CREATE VIEW dbo.PW001SRV501
AS
SELECT P01.PIN PIN,
P01.ALTERNATIVEPIN AS 'ALTERNATIVE PIN',
P01.NAME AS 'Full Name',
P01.TITLENAME AS 'Title Name',
P01.FIRSTNAME AS 'First Name',
P01.MIDDLENAME AS 'Middle Name',
P01.LASTNAME AS 'Last Name',
P01.MAIDENNAME AS 'Maiden Name',
CASE
WHEN p01.SEX = 'M' THEN 'Male'
WHEN p01.SEX = 'F' THEN 'Female'
ELSE 'Undefined'
END AS Gender,
p01Rank.[NAME] AS [Rank],
ORG.NAME AS 'Organization',
C02.NAME 'Current Rank',
P01.CLIENT Numorgid,
P01.EmploymentStartDate,
P01.EmploymentEndDate,
nat.NATIONALITY AS Nationality,
P01.BIRTHDATE 'Birth date',
P01.PERSONALIDNO AS 'Personal ID',
PASS.TDNUMBER 'Passport No',
PASS.DATETO AS 'Passport Expiry',
passc.COUNTRYNAME AS 'Passport Country of Issue',
P01.ADDRESS1 + ' ' + P01.ADDRESS2 AS 'Address',
P01.POSTCODE 'Post Code',
P01.POSTPLACE 'Post Place',
PC.COUNTRYNAME AS 'Address Country',
P01.HOMEAIRPORT 'Home Airport',
email.TELENO AS 'E-MAIL',
mob.TELENO AS 'Mobile Phone',
PL.DOCUMENT PPICTURE,
CASE
WHEN CONTRACTKIND = 0 THEN 'Main Contract'
WHEN CONTRACTKIND = 1 THEN 'Sub Contract'
WHEN CONTRACTKIND = 2 THEN 'Ammendment'
ELSE ''
END AS 'Contract Type',
CASE
WHEN CONTRACTKIND = 2 THEN p20.DATESTART
ELSE NULL
END AS 'Amendment Date Start',
CASE
WHEN CONTRACTKIND = 2 THEN p20.DATEEND
ELSE NULL
END AS 'Amendment Date End',
C32CONT.Text 'Contract Name',
C02CONT.NAME 'Contract Rank',
p20.DATESTART 'Contract Start',
p20.DATEEND 'Contract End',
p20.DURATION 'Trial period duration',
p20.TRIALPERIODEND 'Trial period end',
p20.PAYSCALETABLE 'Contract Payscale Table',
payscaleinfo.Table_Name 'Contract Pasyscale Table Name',
p20.PAYSCALECODE 'Contract Payscale Code',
p20.DATESTART,
payscaleinfo.Payscale_Name 'Contract Payscale Name',
ISNULLCONVERT(
dbo.AD_RETURNPAYSCALEAMOUNT(p20.PAYSCALECODE, p20.PAYSCALETABLE, 0, 1),
0
) VARCHAR, CAST(ROUND(p20r.RATEN, 2) AS DECIMAL(10, 2))) 'Merit by Individual',
CASE
WHEN ISNULL(p0y.PAYROLLPERIODLOCK, 'N') = '' THEN 'N'
ELSE ISNULL(p0y.PAYROLLPERIODLOCK, 'N')
END AS 'Paroll Period Lock',
CASE
WHEN p01.Transferbalance = '0' THEN 'Carry Forward To Next Month'
WHEN p01.Transferbalance = '1' THEN 'Transfer To Bank Account'
WHEN p01.Transferbalance = '2' THEN 'Interpay'
WHEN p01.Transferbalance = '3' THEN 'Alpha Credit Bank'
WHEN p01.Transferbalance = '4' THEN 'Cash'
WHEN p01.Transferbalance = '5' THEN 'Credit/Debit Card'
WHEN p01.Transferbalance = '6' THEN 'Citibank'
WHEN p01.Transferbalance = '7' THEN 'Chase Manhattan'
WHEN p01.Transferbalance = '8' THEN 'Hellenic Bank'
WHEN p01.Transferbalance = '9' THEN 'Isabel Domestic'
WHEN p01.Transferbalance = 'A' THEN 'Isabel International'
WHEN p01.Transferbalance = 'B' THEN 'Bank Transfer'
WHEN p01.Transferbalance = 'C' THEN 'Swedish Domestic'
WHEN p01.Transferbalance = 'D' THEN 'Swedish International'
WHEN p01.Transferbalance = 'E' THEN 'Philippine Banking'
WHEN p01.Transferbalance = 'F' THEN 'Manual Bank'
WHEN p01.Transferbalance = 'G' THEN 'Zagrebancka'
WHEN p01.Transferbalance = 'H' THEN 'ING Bank'
WHEN p01.Transferbalance = 'I' THEN 'Direct Deposit E-Monee'
WHEN p01.Transferbalance = 'J' THEN 'Deutsche Bank'
WHEN p01.Transferbalance = 'K' THEN 'SACS'
WHEN p01.Transferbalance = 'L' THEN 'Hellenic Cyprus'
WHEN p01.Transferbalance = 'M' THEN 'MT 100'
WHEN p01.Transferbalance = 'N' THEN 'Citibank (PL)'
WHEN p01.Transferbalance = '0' THEN 'Agent .R'
WHEN p01.Transferbalance = 'P' THEN 'German Bank'
WHEN p01.Transferbalance = 'Q' THEN 'Trident Trust'
WHEN p01.Transferbalance = 'R' THEN 'Citibank (Asia)'
WHEN p01.Transferbalance = 'S' THEN 'PNC Bank'
WHEN p01.Transferbalance = 'T' THEN 'Chase Insight'
WHEN p01.Transferbalance = 'U' THEN 'Ocean Pay'
WHEN p01.Transferbalance = 'V' THEN 'Banco de Oro'
WHEN p01.Transferbalance = 'W' THEN 'Bank of Philippine Island'
WHEN p01.Transferbalance = 'X' THEN 'CitiDirect (Onboard)'
WHEN p01.Transferbalance = 'Y' THEN 'Metrobank Direct (PH)'
WHEN p01.Transferbalance = 'Z' THEN 'Elektron'
WHEN p01.Transferbalance = 'A1' THEN 'E-Banking (Maramut)'
WHEN p01.Transferbalance = 'A2' THEN 'RBS Direct Access'
WHEN p01.Transferbalance = 'A3' THEN 'Brazilian Banks'
WHEN p01.Transferbalance = 'A4' THEN 'NETS'
WHEN p01.Transferbalance = 'A5' THEN 'NONE'
WHEN p01.Transferbalance = 'A6' THEN 'JDP Morgan'
WHEN p01.Transferbalance = 'A7' THEN 'J. P. Morgan Access'
WHEN p01.Transferbalance = 'A8' THEN 'Spar Nord Domestic'
WHEN p01.Transferbalance = 'A9' THEN 'Spar Nord International'
WHEN p01.Transferbalance = 'B1' THEN 'Rabobank'
WHEN p01.Transferbalance = 'B2' THEN 'Deutsche Bank'
WHEN p01.Transferbalance = 'B4' THEN 'ISO20022'
WHEN p01.Transferbalance = 'B5' THEN 'Brightwell'
END AS 'Payment Method',
CASE
WHEN GETDATE() BETWEEN p03cur.DATEFROM AND ISNULL(p03cur.DATETO, p03cur.TODATEESTIMATED) THEN C12.[TEXT]--p03cur.CODE
ELSE 'Available'
END AS 'Status',
CASE
WHEN p03cur.PIN IS NOT NULL THEN 'Currently Onboard'
WHEN p03plan.PIN IS NOT NULL THEN CONVERT(VARCHAR, p03plan.DATEFROM, 110)
WHEN p03cur.PIN IS NULL AND p03plan.PIN IS NULL THEN 'Not planned'
END 'Nearest Embarkation Date',
(
CONVERT(
NUMERIC(6, 2),
CONVERT(REAL, DATEDIFF(DAY, P01.EMPLOYMENTSTARTDATE, GETDATE())) / 365
)
) 'Employment Seniority',
ISNULL(
(
CONVERT(
NUMERIC(6, 2),
CONVERT(
REAL,
DATEDIFF(DAY, ISNULL(p03cur.DATEFROM, p03plan.DATEFROM), GETDATE())
) / 365
)
),
0
) 'Activity Seniority',
dbo.ad_PayrollSeniority(
CMP.PERIODE,
PRD.COMPLETIONDATE,
P20.PAYROL_SENIORITY_START,
P20.PAYROL_SENIORITY_END,
REPLACE(P20.PAYROLL_SENIORITY_YEARS, ',', '.')
) 'Payroll Seniority',
p01.COSTPLACE AS 'Department/Cost Place Code',
c43.[NAME] AS 'Department/Cost Place'
FROM PW001P01 P01
LEFT JOIN PW001P08 pass
ON pass.PIN = p01.PIN
AND pass.CODE = 'P'
AND NOT EXISTS (
SELECT 1
FROM PW001P08 t
WHERE t.PIN = pass.PIN
AND t.CODE = pass.CODE
AND (
t.DATETO < pass.DATETO
OR (t.DATETO = pass.DATETO AND t.SEQUENCENO > pass.SEQUENCENO)
)
)
LEFT JOIN dbo.PW001P03 P03cur
ON P01.PIN = P03cur.PIN
--AND (P03cur.CODE IN (SELECT c12.CODE
-- FROM PW001C12 c12
-- WHERE c12.OPTIONS LIKE '%S%') OR p03cur.CODE = 'SHORE')
AND (P03cur.DATETO IS NULL OR P03cur.DATETO >= GETDATE())
AND P03cur.DATEFROM <= GETDATE()
AND P03cur.PLANNED <> 'Y'
LEFT JOIN PW001P03 p03plan
ON p03plan.PIN = p01.PIN
AND p03plan.PLANNED = 'Y'
AND p03plan.CODE IN (SELECT t.CODE
FROM pw001c12 t
WHERE t.OPTIONS LIKE '%S%')
AND NOT EXISTS (
SELECT 1
FROM PW001P03 p03t
WHERE p03t.PIN = p01.PIN
AND p03t.PLANNED = 'Y'
AND p03t.CODE IN (SELECT t.CODE
FROM PW001C12 t
WHERE t.OPTIONS LIKE '%S%')
AND p03t.DATEFROM < p03plan.DateFrom
)
LEFT JOIN (
SELECT NUMORGID,
PERIODE
FROM PWORGCMP
UNION ALL
SELECT NUMORGID,
PERIODE
FROM PWORGVES
) CMP
ON (CMP.NUMORGID = dbo.ad_ScanOrgTreePayroll(p01.client))
LEFT JOIN PWCMPPAY PRD
ON (COMPANYID = CMP.NUMORGID)
AND ((PTYPE <> 'T') OR (PTYPE IS NULL))
AND (PRD.PERIOD = CMP.PERIODE)
LEFT JOIN PWCOUNTRY passc
ON passc.COUNTRYCODE = pass.ISSUE_COUNTRY
LEFT JOIN PW001P0T email
ON email.PIN = p01.PIN
AND email.TELETYPE = 6
AND email.TELEPRIORITY IS NOT NULL
AND NOT EXISTS (
SELECT 1
FROM PW001P0T t
WHERE t.PIN = email.PIN
AND t.TELETYPE = email.TELETYPE
AND t.TELEPRIORITY IS NOT NULL
AND (
t.TELEPRIORITY < email.TELEPRIORITY
OR t.TELEPRIORITY = email.TELEPRIORITY
AND t.SEQUENCENO < email.SEQUENCENO
)
)
LEFT JOIN PW001P0T mob
ON mob.PIN = p01.PIN
AND mob.TELETYPE = 3
AND mob.TELEPRIORITY IS NOT NULL
AND NOT EXISTS (
SELECT 1
FROM PW001P0T t
WHERE t.PIN = mob.PIN
AND t.TELETYPE = mob.TELETYPE
AND t.TELEPRIORITY IS NOT NULL
AND (
t.TELEPRIORITY < mob.TELEPRIORITY
OR t.TELEPRIORITY = mob.TELEPRIORITY
AND t.SEQUENCENO < mob.SEQUENCENO
)
)
LEFT JOIN PWCOUNTRY nat
ON nat.COUNTRYCODE = p01.NATIONALITY
LEFT JOIN PW001P0P P0P
ON ((P01.PIN = P0P.PIN) AND (P0P.PNUMBER = 'A'))
LEFT JOIN PW001C02 p01Rank
ON p01Rank.CODE = p01.[RANK]
LEFT JOIN PW001C02 C02
ON P0P.POSITIONID = C02.CODE
LEFT JOIN PWORG ORG
ON P01.CLIENT = ORG.NUMORGID
LEFT JOIN PWCOUNTRY PC
ON P01.ADDRESS_COUNTRY = PC.COUNTRYCODE
LEFT JOIN PW001P01PICT PL
ON P01.PIN = PL.PIN
LEFT JOIN PW001P20 p20
ON p20.PIN = p01.PIN
AND p20.HISTORICAL = 'F'
AND NOT EXISTS (
SELECT 1
FROM PW001P20 t
WHERE t.PIN = p20.PIN
AND t.HISTORICAL = 'F'
AND t.DATESTART > p20.DATESTART
)
LEFT JOIN PW001C02 C02CONT
ON p20.RANK = C02CONT.CODE
LEFT JOIN PW001C32 C32CONT
ON p20.CONTRACTTYPE = C32CONT.code
LEFT JOIN PW001P0Y p0y
ON p0y.PIN = p01.PIN
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
) payscaleinfo
ON payscaleinfo.Table_Code = p20.PAYSCALETABLE
AND payscaleinfo.Payscale_Code = p20.PAYSCALECODE
LEFT JOIN pw001c12 c12
ON c12.CODE = p03cur.CODE
LEFT JOIN PW001C43 c43
ON c43.CODE = p01.COSTPLACE
LEFT JOIN PW001P20R p20r
ON p20r.CONTRACT_SEQNO = p20.SEQUENCENO |
