Unconfirmed Activities (ALL)

Owned by Julius Marck Velasco, created
Last updated: Apr 04, 2024 by Daria Chebotaieva
4 min read
1 Description | 2 View Sample | 3 Main Data Selection | 4 SQL statement | 5 Columns Specification

Summary

Shows all the crews’ unconfirmed activities

Keywords

Activities, Unconfirmed

Category

Crew List View

Description

The view shows all the crews’ unconfirmed activities.

View Sample

image-20240304-120115.png

 

Main Data Selection

All crew with unconfirmed activities. An activity is considered unconfirmed if the status is current and the estimated end date is less than today’s date, or if the status is planned and the date from is less than today’s date.

SQL statement

CREATE VIEW dbo.VIEWID AS SELECT P01.PIN AS PIN, P01.ALTERNATIVEPIN AS 'ALTERNATIVE PIN', P01.NAME AS 'Full Name', P01.FIRSTNAME AS 'First Name', P01.LASTNAME AS 'Last Name', P01.MIDDLENAME 'Middle Name', ORG.NAME AS 'Organization', C02.[NAME] AS 'Rank', c12plan.TEXT 'Activity', CASE WHEN ((p03.DAteto = '' OR Dateto IS NULL) AND p03.PLANNED = 'Y') THEN 'Planned' WHEN ((p03.DAteto = '' OR Dateto IS NULL) AND p03.PLANNED = 'N') THEN 'Current' END AS 'Activity status', CASE WHEN ((P03.DATEFROM <= GETDATE()) AND p03.PLANNED = 'Y') THEN 'Unconfirmed Date From' WHEN ( (P03.todateestimated <= GETDATE()) AND (p03.DAteto = '' OR Dateto IS NULL) ) THEN 'Unconfirmed Date To' END AS 'Issue', p03.DATEFROM 'Start Date', p03.TODATEESTIMATED 'Estimated End Date', plan_ves.NUMORGID 'Vessel_NUMORGID', plan_ves.NAME 'Vessel', plan_dep.NAME 'Department', plan_pos.NAME 'Position', P01.client NUMORGID, p01.employmentstartdate, p01.employmentenddate, p01.COSTPLACE AS 'Department/Cost Place Code', c43.[NAME] AS 'Department/Cost Place' FROM PW001P01 p01 JOIN PW001P03 p03 ON p03.PIN = p01.PIN LEFT JOIN PW001C02 C02 ON C02.CODE = P01.[RANK] LEFT JOIN PW001C12 c12plan ON c12plan.CODE = p03.CODE LEFT JOIN PWORG plan_ves ON plan_ves.NUMORGID = dbo.ad_scanorgtree(p03.NUMORGID, 3) LEFT JOIN PWORG plan_dep ON plan_dep.NUMORGID = dbo.ad_scanorgtree(p03.NUMORGID, 4) LEFT JOIN PWORG plan_pos ON plan_pos.NUMORGID = dbo.ad_scanorgtree(p03.NUMORGID, 5) LEFT JOIN PWORG ORG ON ORG.NUMORGID = P01.CLIENT LEFT JOIN PW001C43 c43 ON c43.CODE = p01.COSTPLACE WHERE (p03.PLANNED = 'Y' AND p03.datefrom <= GETDATE()) OR ( p03.datefrom <= GETDATE() AND p03.dateto IS NULL AND p03.todateestimated <= GETDATE() )

Columns Specification

Column

Description/ Location in APM

Column

Description/ Location in APM

PIN

Personal Details > Personal > PIN

Alternative PIN

Personal Details > Personal > Alternative PIN

Full Name

Personal Details > Personal > Full Name

Firstname

Personal Details > Personal > Firstname

Lastname

Personal Details > Personal > Lastname

Middlename

Personal Details > Personal > Middlename

Organization

Personal Details > Employment > Organization/Company

Rank

Personal Details > Employment > Rank

Activity

Activitiy name

Activity Status

Current/Planned

Issue

Unconfirmed Date From/ Unconfirmed Date To

Start Date

Datagroups > Activities > Date From

Estimated End Date

Datagroups > Activities > Estimated End Date

Vessel

Datagroups > Activities > Vessel name

Department

Datagroups > Activities > Department name

Position

Datagroups > Activities > Position name

Department/Cost Place Code

Personal Details > Employment > Payroll: Cost Place code

Department/Cost Place

Personal Details > Employment > Payroll: Cost Place name