CREATE VIEW dbo.PW001SRV43PW001SRV53
AS
as
SELECT p01.pin as Pin,
p01.Name as                    AS Pin,
       --P01.ALTERNATIVEPIN         AS 'ALTERNATIVE PIN',
       p01.Name                   AS 'Name',
       p01.Firstname 'First Name',
       p01.MIDDLENAME 'Middle Name',
       p01.Lastname as               AS 'Last Name',
       P1P.NATIVENAME 'Native Name',
       p01.Nationality,
       COUNTRY.COUNTRYNAME        AS 'Nationality Name',
       p01.Sex as                   AS 'Gender',
case when
       C04.[TEXT]                 AS 'Marital Status',
       CASE 
            WHEN p01.sex = 'F' thenTHEN 'Female'
when
            WHEN p01.sex = 'M' thenTHEN 'Male' end as
       END                        AS 'Gender Name',
       p01.Birthdate as              AS 'DOB',
       p01.PLACEOFBIRTH as          AS 'Place of Birth',
       P01.PERSONALIDNO 'Personal ID',
       vsl.name  as Ship,                   AS Vessel,
       vsl.NUMORGID as Vessel                  vessel_numorgid,
       DEP.NAME as                  AS Department,
       POS.NAME as                   AS Position,
--
       P03.CabinID as               AS 'Cabin',
       dbo.ad_SignOnDate(p03.NUMORGID, p01.PIN, P03.DATEFROM) asAS 'Sign On Date',
       ISNULL(p03.DATETO, P03.TODATEESTIMATED) asAS 'Sign Off Date',
       --IMPLADC-568 06/27/2024
       /*
       ISNULL(portOn.portcode, portEst.portcode) 'EMBARKATION PORT CODE',
       ISNULL(portOn.NAME, portEst.NAME) 'EMBARKATION PORT',
       ISNULL(portOff.portcode, portEstOff.portcode) 'DISEMBARKATION PORT CODE',
       ISNULL(portOff.NAME, portEstOff.NAME) 'DISEMBARKATION PORT',
       */
       portOn.portcode 'EMBARKATION PORT CODE',
       portOn.NAME 'EMBARKATION PORT',
       portOff.portcode 'DISEMBARKATION PORT CODE',
       portOff.NAME 'DISEMBARKATION PORT',
       
       P1P.TDNUMBER as               AS 'Passport Number',
       P1P.DATEFROM as               AS 'Passport Issue Date',
       P1P.DATETO as                 AS 'Passport Expiry Date',
--C1D.TDNUMBER as 'C1D
       SBOOK.TDNUMBER             AS 'SB Number',
--C1D
       SBOOK.DATEFROM as            AS 'C1DSB Issue Date',
--C1D       SBOOK.DATETO as               AS 'C1DSB Expiry Date',
SBOOK
       --C1D.TDNUMBER as 'SBC1D Number',
SBOOK
       
--C1D.DATEFROM as 'SBC1D Issue Date',
SBOOK
       
--C1D.DATETO as 'SBC1D Expiry Date',
       p01.Client                    numorgid,
       p01.EmploymentStartDate,
       p01.EmploymentEnddate,
      /* CASE 
            WHEN CONTRACTKIND = 0 THEN 'Main Contract'
            WHEN CONTRACTKIND = 1 THEN 'Sub Contract'
            WHEN 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.DURATION 'Trial period duration',
       p20.TRIALPERIODEND 'Trial period end',
       p20.PAYSCALETABLE 'Contract Pasycale Table',
       p20.PAYSCALECODE 'Contract Pasycale Code',
       p0u.EMP_PORT 'Employee Portal Access From',
       p20u.[LENGTH] + ' months'  AS 'Duration Of Employment', */
       p01.[RANK]                 AS 'Current Rank Code',
       cRANK.[NAME]               AS 'Current Rank Name',
       cRANK.GROUPNO         AS 'Rank sort',
       C56.NAME                AS 'Rank Category',
       org.[NAME]                 AS 'Organization',
      /* CASE 
            WHEN p20.scanneddocno IS NULL THEN NCHAR(9940) + ' Missing'
            WHEN p20.scanvalidity IN (1, 0) AND p20.scanneddocno IS NOT NULL THEN NCHAR(10004) + ' Confirmed'
            ELSE 'N/A'
       END                        AS 'Contract Scan Status', */
       emb.[TEXT]                 AS 'Embarking Reason',
       dis.[TEXT]                 AS 'Disembarking Reason',
       p03.SEQUENCENO AS 'External ID'

FROM   dbo.pw001p01 p01
       LEFT JOIN PWCOUNTRY COUNTRY
            ON  P01.NATIONALITY = COUNTRY.COUNTRYCODE
       JOIN dbo.PW001P03 P03
            ON  P01.PIN = P03.PIN
            AND P03.CODE inIN (SELECT c12.CODE
                             FROM   PW001C12 c12
                             WHERE  c12.OPTIONS LIKE '%S%')
            AND (P03.DATETO IS NULL OR P03.DATETO >= GETDATE())
            AND P03.DATEFROM <= GETDATE()
            AND P03.PLANNED <> 'Y'
       LEFT JOIN PWROT_SHIFT_ACTIVITIES psa
            ON  psa.ACTIVITIES = p03.SEQUENCENO
       LEFT JOIN PWROT_ACTIVE_SHIFT pas
            ON  pas.SEQUENCENO = psa.SEQUENCENO
       LEFT JOIN PWCCMCHANGECREWLIST cclOn
            ON  cclOn.NEXTACT = p03.SEQUENCENO
            AND cclOn.SIGNON = 1
       LEFT JOIN PWORGVESACT ccOn
            ON  ccOn.SEQNO = cclOn.SEQNO
       LEFT JOIN PWPORT portOn
            ON  portOn.PORTCODE = ccOn.PLACEFROM
       LEFT JOIN PWORGVESACT ccEst
            ON  ccEst.SEQNO = pas.SPD_CC_SIGNON
       LEFT JOIN PWPORT portEst
            ON  portEst.PORTCODE = ccEst.PLACEFROM
       LEFT JOIN PWCCMCHANGECREWLIST cclOff
            ON  cclOff.CURRENTACT = p03.SEQUENCENO
       LEFT JOIN PWORGVESACT ccOff
            ON  ccOff.SEQNO = cclOff.SEQNO
       LEFT JOIN PWPORT portOff
            ON  portOff.PORTCODE = ccOff.PLACEFROM
       LEFT JOIN PWORGVESACT ccEstOff
            ON  ccEstOff.SEQNO = pas.SPD_CC_SIGNON
       LEFT JOIN PWPORT portEstOff
            ON  portEstOff.PORTCODE = ccEstOff.PLACEFROM
       JOIN PWORG VSL
            ON  VSL.NUMORGID = dbo.ad_scanorgtree(P03.NUMORGID, 3)
       LEFT JOIN dbo.PWORG DEP
            ON  DEP.NUMORGID = dbo.ad_scanorgtree(P03.NUMORGID, 4)
       LEFT JOIN dbo.PWORG POS
            ON  POS.NUMORGID = dbo.ad_scanorgtree(P03.NUMORGID, 5)
       LEFT JOIN PW001P08 P1P
            ON  P01.PIN = P1P.PIN
            AND P1P.CODE = 'PP'
--LEFT JOIN PW001P08 C1D ON C1D.PIN=P01.PIN AND C1D.CODE='VISAC1D'
       LEFT JOIN PW001P08 SBOOK
            ON  SBOOK.PIN = P01.PIN
            AND SBOOK.CODE ='SB'

 'SBOOK'
            AND NOT EXISTS (
                    SELECT 1
                    FROM   PW001P08 t
                    WHERE  t.PIN = SBOOK.PIN
                           AND t.CODE = 'SBOOK'
                           AND t.DATETO > SBOOK.DATETO
                )
       LEFT JOIN PW001P20 p20
            ON  p20.PIN = p01.PIN
            AND p20.HISTORICAL = 'F'
                --AND p20.DATESTART BETWEEN p03plan.DATEFROM AND ISNULL(p03plan.DATETO, p03plan.TODATEESTIMATED)
            AND NOT EXISTS (
                    SELECT 1
                    FROM   PW001P20 t
                    WHERE  t.PIN = p20.PIN
                           AND t.HISTORICAL = 'F'
                           AND t.DATESTART > p20.DATESTART
                )
       LEFT JOIN PW001OLEDOCS sdoc
            ON  sdoc.DOCNO = p20.SCANNEDDOCNO
       LEFT JOIN PW001C02 C02CONT
            ON  p20.RANK = C02CONT.CODE
       LEFT JOIN PW001C32 C32CONT
            ON  p20.CONTRACTTYPE = C32CONT.code
       LEFT JOIN PW001P0U p0u
            ON  p0u.PIN = p01.PIN
       LEFT JOIN PW001P20U p20u
            ON  p20u.CONTRACT_SEQNO = p20.SEQUENCENO
       LEFT JOIN PW001C02 cRANK
            ON  cRank.CODE = p01.[RANK]
       LEFT JOIN PWORG org
            ON  org.NUMORGID = p01.CLIENT
       LEFT JOIN PW001C79 emb
            ON  emb.CODE = p03.Embarkation
       LEFT JOIN PW001C79 dis
            ON  dis.CODE = p03.Disembarkation
       LEFT JOIN PW001C43 c43
            ON  c43.CODE = p01.COSTPLACE
       LEFT JOIN PW001C04 c04
            ON  c04.CODE = p01.MARITALSTATUS

       /*LEFT JOIN PW001C02 c02
            ON  c02.CODE = p01.RANK*/

       LEFT JOIN PW001C56 c56
            ON  c56.CODE = cRANK.CODECATEGORY