Versions Compared

Version Old Version 3 New Version 4
Changes made by

Daria Chebotaieva

Helen Ustych

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Expand
Code Block
languagesql
CREATE VIEW dbo.PW001SRV131 AS
SELECT p01.PIN,
       P01.client            NUMORGID,
       p01.EMPLOYMENTSTARTDATE,
       p01.EMPLOYMENTENDDATE,
       p01.NAME,
       p01.FIRSTNAME,
       p01.LASTNAME,
       p01.SEX               GENDER,
       plan_pos.NAME         POSITION,
       PLAN_DEP.NAME         DEPARTMENT,
       plan_ves.NAME         VESSEL,
       plan_ves.NUMORGID     vessel_numorgid,
       PLAN_DEP.numorgid     department_numorgid,
       ORG.NAME              ORGANIZATION,
       p01.BIRTHDATE      AS 'DATE OF BIRTH',
       P01.PLACEOFBIRTH 'PLACE OF BIRTH',
       CASE 
            WHEN c20manning.NAME IS NULL THEN 'NOT ASSIGNED'
            ELSE C20MANNING.NAME
       END                AS 'MANNING POOL',
       nat.NATIONALITY    AS 'NATIONALITY ',
       p01.NATIONALITY 'NATIONALITY CODE',
       nat.ISO3166_ALPHA3 'NATIONALOTY CODE ISO',
       P08.TDNUMBER 'PASS NUMBER',
       p08.datefrom 'PASS ISSUE',
       p08.dateto 'PASS EXPIRY',
       P081.TDNUMBER         SBOOK,
       P081.DATEFROM 'SB ISSUE DATE',
       p081.issuedby 'SB ISSUE BY',
       P081.DATETO 'SBOOK EXPIRY',
       p08v.TDNUMBER 'VISA',
       p08v.datefrom 'VISA ISSUE',
       p08v.dateto 'VISA EXPIRY',
       --c79d.[TEXT] 'Disembarkation Reason',
       c79e.[TEXT] 'Embarkation Reson',
       p03plan.DATEFROM 'EMBARKATION DATE',
       ISNULL(portOn.portcode, portEst.portcode) 'EMBARKATION PORT CODE',
       ISNULL(portOn.NAME, portEst.NAME) 'EMBARKATION PORT',
       p03plan.TODATEESTIMATED 'DISEMBARKATION DATE',
       ISNULL(portOff.portcode, portEstOff.portcode) 'DISEMBARKATION PORT CODE',
       ISNULL(portOff.NAME, portEstOff.NAME) 'DISEMBARKATION PORT',
       fb.GDSLOCATOR      AS 'GDS Locator',
       STUFF(
           (
               SELECT ',' + CHAR(10) + al.AIRLINELOCATOR
               FROM   PWCCMAIRSEGMENTS al
               WHERE  al.seqno = fb.SEQNO
               ORDER BY
                      al.SEGNO
                      FOR XML PATH('')
           ),
           1,
           2,
           ''
       )                  AS 'Airline Locator',
       Stuff((Select ','+Char(10)
			+Case when len(rd.CARRIER+convert(varchar,rd.FLIGHTNUM))<7 then Space(7-len(rd.CARRIER+convert(varchar,rd.FLIGHTNUM)))+rd.CARRIER+convert(varchar,rd.FLIGHTNUM) else rd.CARRIER+convert(varchar,rd.FLIGHTNUM) end +Space(1)
			+case when left(convert(varchar,rd.DEPT_DATE,106),1) = '0'
				then Replace(Replace(Upper(Left(convert(varchar,rd.DEPT_DATE,106),6)),' ',''),'0',' ')
				else Replace(Upper(Left(convert(varchar,rd.DEPT_DATE,106),6)),' ','')
			end+Space(1)
			+Isnull((select Case when isnull(charindex('-',LTRIM(RTRIM(Replace(Replace(Replace(Replace([name],'Int`l',''),'Int''l',''),'Apt.',''),'Airport','')))),0)=0 then Replace(Replace(Replace(Replace([name],'Int`l',''),'Int''l',''),'Apt.',''),'Airport','')
						else RTRIM(Left(LTRIM(RTRIM(Replace(Replace(Replace(Replace([name],'Int`l',''),'Int''l',''),'Apt.',''),'Airport',''))),charindex('-',LTRIM(RTRIM(Replace(Replace(Replace(Replace([name],'Int`l',''),'Int''l',''),'Apt.',''),'Airport',''))))-1))
						End as airport from pw001c48 where code  = rd.DEPT_AIRPORT),'')+Char(9)+
			+Isnull((select Case when isnull(charindex('-',LTRIM(RTRIM(Replace(Replace(Replace(Replace([name],'Int`l',''),'Int''l',''),'Apt.',''),'Airport','')))),0)=0 then Replace(Replace(Replace(Replace([name],'Int`l',''),'Int''l',''),'Apt.',''),'Airport','')
						else RTRIM(Left(LTRIM(RTRIM(Replace(Replace(Replace(Replace([name],'Int`l',''),'Int''l',''),'Apt.',''),'Airport',''))),charindex('-',LTRIM(RTRIM(Replace(Replace(Replace(Replace([name],'Int`l',''),'Int''l',''),'Apt.',''),'Airport',''))))-1))
						End as airport from pw001c48 where code  = rd.ARRV_AIRPORT),'')+Char(9)+
			+Isnull(case when rd.DEPT_TIME<100 then '00' when rd.DEPT_TIME<1000 then '0' else '' end+Convert(varchar(4),rd.DEPT_TIME)+Space(1),'    '+Space(1))
			+Isnull(case when rd.ARRV_TIME<100 then '00' when rd.ARRV_TIME<1000 then '0' else '' end+Convert(varchar(4),rd.ARRV_TIME)+Space(1),'    '+Space(1))
	   From PWCCMAIRSEGMENTS rd where rd.seqno = fb.SEQNO
	   order by rd.SEGNO
	   for xml path('')),1,2,'') as 'Route Details',
       STUFF(
           (
               SELECT ',' + CHAR(10) + fn.CARRIER + SPACE(1) + CONVERT(VARCHAR, fn.FLIGHTNUM)
               FROM   PWCCMAIRSEGMENTS fn
               WHERE  fn.seqno = fb.SEQNO
               ORDER BY
                      fn.SEGNO
                      FOR XML PATH('')
           ),
           1,
           2,
           ''
       )                  AS 'Flight No',
       fb.ARVLDATE        AS 'Arrival Date',
       LEFT(fbat.ETA, 2) + ':' + RIGHT(fbat.ETA, 2) AS 'ETA',
       STUFF(
           (
               SELECT DISTINCT ',' + CHAR(10) + tn.TICKETCODE
               FROM   PWCCMAIRSEGMENTS tn
               WHERE  tn.seqno = fb.SEQNO
                      FOR XML PATH('')
           ),
           1,
           2,
           ''
       )                  AS 'Ticket Number',
       CASE 
            WHEN P0N.NOTES IS NULL THEN 'N'
            ELSE 'Y'
       END                AS NOTES
FROM   PW001P01 p01
       LEFT JOIN PWORG ORG
            ON  P01.CLIENT = ORG.NUMORGID
       JOIN PW001P03 p03plan
            ON  p03plan.PIN = p01.PIN
            AND p03plan.PLANNED = 'Y'
            AND NOT EXISTS (
                    SELECT 1
                    FROM   PW001P03 t
                           JOIN PW001C12 c12t
                                ON  c12t.CODE = t.CODE
                                AND c12t.OPTIONS LIKE '%S%'
                    WHERE  t.PIN = p03plan.Pin
                           AND dbo.ad_scanorgtree(t.NUMORGID, 3) = dbo.ad_scanorgtree(p03plan.NUMORGID, 3)
                           AND t.Numorgid <> p03plan.Numorgid
                           AND t.TODATEESTIMATED = p03plan.DATEFROM - 1
                )
                AND P03PLAN.DATEFROM <= (GETDATE()+14)
       JOIN PW001C12 c12plan
            ON  c12plan.CODE = p03plan.CODE
            AND c12plan.OPTIONS LIKE '%S%'
       LEFT JOIN PWROT_SHIFT_ACTIVITIES psa
            ON  psa.ACTIVITIES = p03plan.SEQUENCENO
       LEFT JOIN PWROT_ACTIVE_SHIFT pas
            ON  pas.SEQUENCENO = psa.SEQUENCENO
       LEFT JOIN PWORG plan_ves
            ON  plan_ves.NUMORGID = dbo.ad_scanorgtree(p03plan.NUMORGID, 3)
       LEFT JOIN PWORG PLAN_DEP
            ON  PLAN_DEP.NUMORGID = dbo.ad_scanorgtree(P03PLAN.NUMORGID, 4)
       LEFT JOIN PWORG plan_pos
            ON  plan_pos.NUMORGID = p03plan.NUMORGID
            AND plan_pos.ORGTYPE = 5
       LEFT JOIN PW001C02 C02
            ON  C02.code = dbo.ad_orgPosC02Code(PLAN_POS.ORGCODE)
       LEFT JOIN PWCOUNTRY nat
            ON  nat.COUNTRYCODE = p01.NATIONALITY
       LEFT JOIN PW001P08 P08
            ON  P08.PIN = P01.PIN
            AND P08.CODE = 'PP'
       LEFT JOIN PW001P08 P081
            ON  P081.PIN = P01.PIN
            AND P081.CODE = 'SBBAH'
       LEFT JOIN PW001P08 P08v
            ON  P08v.PIN = P01.PIN
            AND P08v.CODE = 'VISASH'
       LEFT JOIN PWCCMCHANGECREWLIST cclOn
            ON  cclOn.NEXTACT = p03plan.SEQUENCENO
       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 = p03plan.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
       LEFT JOIN PW001P0N P0N
            ON  P0N.PIN = P01.PIN
       LEFT JOIN PW001C20 c20manning
            ON  c20manning.CODE = MANAGENTCODE
       LEFT JOIN PWCCMFLIGHTARRANGEMENTS fb
            ON  fb.pin = p01.pin
            AND fb.SEQNOM = ISNULL(cclOn.SEQNO, ccEst.SEQNO)
            AND fb.SIGNON = 1
            AND fb.BOOKING_STATUS IN ('ISSUED', 'APPROVED')
       LEFT JOIN (
                SELECT SEQNOM,
                       PIN,
                       CASE 
                            WHEN SCHEDULED_ARRIVAL_TIME < 100 THEN '00'
                            WHEN SCHEDULED_ARRIVAL_TIME < 1000 THEN '0'
                            ELSE ''
                       END + CONVERT(VARCHAR(4), SCHEDULED_ARRIVAL_TIME) AS 'ETA'
                FROM   PWCCMFLIGHTARRANGEMENTS
                WHERE  signon = 1
                       AND BOOKING_STATUS IN ('ISSUED', 'APPROVED')
            ) fbat
            ON  fbat.pin = p01.pin
            AND fbat.SEQNOM = ISNULL(cclOn.SEQNO, ccEst.SEQNO)
       --LEFT JOIN PW001C79 c79d
            --ON  c79d.CODE = p03plan.DISEMBARKATION
       LEFT JOIN PW001C79 c79e
            ON  c79e.CODE = p03plan.EMBARKATION

View Sample

image-20241025-080807.pngImage Added

Columns Specification

Column

Description/ Location in APM

PIN

Personal Details > Personal > PIN

ALTERNATIVEPIN

Personal Details > Personal > Alternative PIN

Start Page

Link to the documentation on the related Start Page Tile.

...

NUMORGID

Personal Details > Employment > Organization’s NUMORGID

EMPLOYMENTSTARTDATE

Personal Details > Employment > Employment Start Date

EMPLOYMENTENDDATE

Personal Details > Employment > Employment End Date

NAME

Personal Details > Personal > Full Name

NATIVE NAME

Personal Details > Personal > Native Name

FIRST NAME

Personal Details > Personal > First Name

MIDDLE NAME

Personal Details > Personal > Middle Name

LAST NAME

Personal Details > Personal > Last Name

Gender

Personal Details > Personal > Gender

Address

Personal Details > Personal > Address1 + Address 2

Post Code

Personal Details > Personal > Post Code

Post Place

Personal Details > Personal > Post Place

Personal ID

Personal Details > Personal > Personal ID

Position

Datagroups > Activity > Position

Department

Datagroups > Activity > Department

Vessel Name

Datagroups > Activity > Vessel

vessel_numorgid

Datagroups > Activity > Vessel NUMORGID

department_numorgid

Datagroups > Activity > Department NUMORGID

Date of Birth

Personal Details > Personal > Date of Birth

Place of Birth

Personal Details > Personal > Place of Birth

Manning Pool

Personal Details > Employment > Manning Pool

Nationality

Personal Details > Personal > Nationality

Nationality Code

Personal Details > Personal > Nationaity Code

Natinality Code ISO

Personal Details > Personal > Nationality Code ISO

Passport Number

Datagroups > Travel Documents > Passport Number

Passport Issue

Datagroups > Travel Documents > Passport Issue Date

Passport Expiry

Datagroups > Travel Documents > Passport Expiry Date

Seamans Book Number

Datagroups > Travel Documents > Seamans Book number

Seamans Book Issued Date

Datagroups > Travel Documents > Seamans Book Issued Date

Seamans Book Issued By

Datagroups > Travel Documents > Seamans Book Issued By

Seamans Book Expiry

Datagroups > Travel Documents > Seamans Book Expiry

Visa Number

Datagroups > Travel Documents > VISA number

Visa Issued Date

Datagroups > Travel Documents > VISA Issued Date

Visa Expiry Date

Datagroups > Travel Documents > VISA Expiry Date

Embarkation Reason

Datagroups > Activities > Embarkation Reason

Embarkation Date

Datagroups > Activities > Embarkation Date From

Embarkation Port Code

Datagroups > Activities > Embarkation Port Code

Embarkation Port Name

Datagroups > Activities > Embarkation Port Name

Disembarkation Date

Datagroups > Activities > Disembarkation Date

Disembarkation Port Code

Datagroups > Activities > Disembarkation Port code

Disembarkation Port Name

Datagroups > Activities > Disembarkation Port Name

Flight GDS Locator

Datagroups > Flight Details > GDS Locator

Flight Airline Location

Datagroups > Flight Details > Airline Location

Flight Route Details

Datagroups > Flight Details > Route Details

Flight Number

Datagroups > Flight Details > Flight Number

Flight Arrival Date

Datagroups > Flight Details > Arrival Date

Flight ETA

Datagroups > Flight Details > ETA

Flight ETD

Datagroups > Flight Details > ETD

Flight Ticket Number

Datagroups > Flight Details > Ticket Number

NOTES

Personal Details > Others > Notes

Mobile

Personal Details > Personal > Telecommunication > Mobile

Email

Personal Details > Personal > Telecommunication > Email

Home Airport

Personal Details > Personal > Home Airport

Contract Type

Datagroups > Contracts > Current Contract > Contract Type

Contract Name

Datagroups > Contracts > Current Contract > Contract Name

Contract Rank

Datagroups > Contracts > Current Contract > Contract Rank

Contract Start

Datagroups > Contracts > Current Contract > Contract Start Date

Contract End

Datagroups > Contracts > Current Contract > Contract End Date

Trial period duration

Datagroups > Contracts > Current Contract > Trial period duration

Trial period end

Datagroups > Contracts > Current Contract > Trial period end

Contract Payscale Table

Datagroups > Contracts > Current Contract > linked Payscale Table

Contract Payscale Code

Datagroups > Contracts > Current Contract > linked Payscale Code

Employee Portal Acess From

Personal Details > Employee Portal > Customized Field

Duration of Employment

Datagroups > Contracts > Current Contract > Customized Field

Current Rank Code

Personal Details > Employment > Current Rank Code

Current Rank Name

Personal Details > Employment > Current Rank Name

Organization

Personal Details > Employment > linked Organization unit

Contract Scan Status

Datagroups > Contracts > Current > Linked scan status

Department/Cost Place Code

Personal Details > Personal > Payroll > Cost Place Code

Department/Cost Place

Personal Details > Personal > Payroll > Cost Place

Start Page

Embarking next 2 weeks