/
Assign Persons in CSB based on Competence
Assign Persons in CSB based on Competence
- Tracy Mercado
Owned by Tracy Mercado, created
1 Description | 2 Selection | 3 SQL statement | 4 Field Specification | 5 Start Page | 6 Other
Summary | Crew List view to assign persons in Crew Station Bill Based on Competence. |
|---|---|
Keywords | Crew Station Bill |
Description
This CrewList view was created to determine and assign persons in Crew Station Bill based on competence.
Selection
SQL statement
CREATE VIEW dbo.[PW001SRV14] AS
WITH dummy AS
(
SELECT P1.PIN,
P1.NUMORGID,
P1.EMPLOYMENTSTARTDATE,
P1.EMPLOYMENTENDDATE
FROM (
SELECT NULL PIN,
NULL NUMORGID,
NULL EMPLOYMENTSTARTDATE,
NULL EMPLOYMENTENDDATE
) AS P1
),
cteStationRequiredDocs AS (
SELECT STATIONSEQNO,
CERTIFICATECODE,
'Competence' AS CertType
FROM WEB_CP_CSB_REQ_CERTIFICATION
UNION ALL
SELECT STATIONSEQNO,
CERTIFICATECODE,
'Medical' AS CertType
FROM WEB_CP_CSB_REQ_MEDICAL AS wccrm
),
cteStationReqDocsCount AS (
SELECT csrdCount.STATIONSEQNO,
COUNT(csrdCount.CERTIFICATECODE)StationReqDocCount
FROM cteStationRequiredDocs AS csrdCount
GROUP BY
csrdCount.STATIONSEQNO
),
cteCompetence AS (
SELECT *
FROM (
SELECT tp05.PIN,
tp05.CODE,
tp05.EXPIRYDATE,
ROW_NUMBER() OVER(
PARTITION BY PIN,
CODE ORDER BY ISNULL(tp05.EXPIRYDATE, '99991231') DESC,
ISNULL(tp05.DATEFROM, '99991231') DESC
) AS Nr
FROM pw001p05 tp05
WHERE code IN (SELECT DISTINCT csrd1.CERTIFICATECODE
FROM cteStationRequiredDocs AS csrd1)
) p05
WHERE p05.Nr = 1
),
cteMedical AS (
SELECT *
FROM (
SELECT PIN,
tp07.CODE,
tp07.EXPIRYDATE,
ROW_NUMBER() OVER(
PARTITION BY PIN,
CODE ORDER BY ISNULL(tp07.EXPIRYDATE, '99991231') DESC,
ISNULL(tp07.DATEISSUED, '99991231') DESC
) AS Nr
FROM pw001p07 tp07
WHERE tp07.code IN (SELECT DISTINCT csrd2.CERTIFICATECODE
FROM cteStationRequiredDocs AS csrd2)
) p07
WHERE p07.Nr = 1
),
cteCrewDocs AS (
SELECT csrd.STATIONSEQNO,
heldDocs.PIN,
COUNT(heldDocs.Code) AcquiredDocs
FROM cteStationRequiredDocs AS csrd
LEFT JOIN (
SELECT *
FROM cteCompetence p05
UNION ALL
SELECT *
FROM cteMedical P07
) heldDocs
ON csrd.CERTIFICATECODE = heldDocs.code
GROUP BY
csrd.STATIONSEQNO,
heldDocs.PIN
)
SELECT ves.NUMORGID AS VesselID,
ves.NAME AS VesselName,
wccs.SEQNO AS StationSeqno,
safePlan.[DESCRIPTION] AS SafetyPlan,
wccs.code AS StationCode,
wccs.NAME AS StationName,
STUFF(
(
SELECT (
' || ' + 'MaxPax:' + CONVERT(VARCHAR, wccsl.MAXPAX) + '- ' + wccsl.NAME
)
FROM WEB_CP_CSB_STN_SFTYLVL AS wccss
LEFT JOIN WEB_CP_CSB_SAFETY_LEVELS AS wccsl
ON wccsl.SEQNO = wccss.SAFETY_LEVEL
WHERE wccs.seqno = wccss.station
FOR XML PATH('')
),
1,
3,
''
) AS StationSafetyLevels,
si.SAFETYID,
si.RANK AS SafetyID_Rank,
si.TITLE AS SafetyID_Title,
STUFF(
(
SELECT (' | ' + reQcert.CERTIFICATECODE)
FROM cteStationRequiredDocs reqCert
WHERE wccs.SEQNO = reqCert.STATIONSEQNO
FOR XML PATH('')
),
1,
3,
''
) AS StationRequiredCertificates,
rdc.StationReqDocCount AS StationRequiredCertificateCount,
COALESCE(crewDocCount.AcquiredDocs, 0) AS CrewMemberDocs,
P03.PIN,
P01.EMPLOYMENTSTARTDATE,
P01.EMPLOYMENTENDDATE,
P01.CLIENT NUMORGID,
p03.ActivityRank,
p03.ActivityCode,
P03.IsPlannedActivity,
p03.DATEFROM AS ActivityStartDate,
P03.TODATEESTIMATED ActivityEndDate
FROM WEB_CP_CSB_STATIONS AS wccs
JOIN WEB_CP_CSB_SAFETYPLAN AS safePlan
ON wccs.REVNR = safePlan.REVISIONNR
AND safePlan.IS_ACTIVE = 1
AND (NOT (safePlan.[IS_PASSIVE] = 1))
LEFT JOIN cteStationReqDocsCount rdc
ON wccs.SEQNO = rdc.STATIONSEQNO
LEFT JOIN WEB_CP_CSB_STN_SFTYID AS wccss
ON wccs.SEQNO = wccss.STATION
LEFT JOIN WEB_CP_CSB_SAFETYID AS SI
ON wccss.SAFETYID = SI.SEQNO
LEFT JOIN pworg ves
ON ves.NUMORGID = wccs.NUMORGID
LEFT JOIN (
SELECT p3.PIN,
p3.CODE AS ActivityCode,
p3.[RANK] AS ActivityRank,
p3.DATEFROM,
P3.DATETO,
P3.TODATEESTIMATED,
dbo.ad_scanorgtree(P3.NUMORGID, 3) AS ActivityVesselID,
ISNULL(P3.PLANNED, 'N')IsPlannedActivity
FROM PW001P03 AS P3
WHERE P3.DATETO IS NULL
AND EXISTS(
SELECT 1
FROM PW001C12 C12
WHERE C12.CODE = P3.CODE
AND C12.OPTIONS LIKE '%S%'
)
) P03
ON P03.ActivityVesselID = ves.NUMORGID
AND p03.datefrom = (
SELECT MIN(tp3.DATEFROM)
FROM PW001P03 tp3
WHERE tp3.pin = p03.pin
AND tp3.dateto IS NULL
AND dbo.ad_scanorgtree(tp3.NUMORGID, 3) = P03.ACTIVITYVESSELID
AND EXISTS(
SELECT 1
FROM PW001C12 tc12
WHERE tc12.CODE = tp3.CODE
AND tc12.OPTIONS LIKE '%S%'
)
)
AND CASE
WHEN si.[RANK] IS NOT NULL THEN si.[RANK]
ELSE P03.ActivityRank
END = P03.ActivityRank
LEFT JOIN cteCrewDocs crewDocCount
ON p03.PIN = crewDocCount.PIN
AND crewDocCount.STATIONSEQNO = wccs.SEQNO
LEFT JOIN PW001P01 P01
ON P01.PIN = P03.PIN
WHERE (NOT (wccs.[IS_PASSIVE] = 1))
AND (
COALESCE(crewDocCount.AcquiredDocs, 0) = COALESCE(rdc.StationReqDocCount, 0)
)Field Specification
Start Page
Link to the documentation on the related Start Page Tile.
Other