1 Description | 2 Attachment

Summary	The export fetches the information that can be used for direct export to SafeSeaNet - Maritime Single Window Advanced Search - EMSA - European Maritime Safety Agency (europa.eu)
Keywords	Customsreporting, Norwegian Customs

Description

The export fetches the information that can be used for direct export to SafeSeaNet, you can read more here

Advanced Search - EMSA - European Maritime Safety Agency (europa.eu)

A series of custom exports for filling this Excel spreadsheet:

SSN spreadsheet information:

Previous version:

Cruise Itinerary

Parameter Name	Parameter Type	Parameter Description

Parameter Name	Parameter Type	Parameter Description
Vessel	Drop-down	Vessel list
Cruise	Text	Cruise/voyage code

It returns an XLS file with the list of all port calls for a selected cruise/voyage.

Select 
	i.PLACEFROM as 'PORT',
	Convert(varchar,i.ETA,104) as 'ETA (date)',
	Convert(varchar,i.ETA,8) as 'ETA (time)'
From PWORGVESACT i
Left Join PWPORT p on i.PLACEFROM = p.PORTCODE
Join PWORG v on i.NUMORGID = v.NUMORGID
Join PWORGVES vn on i.NUMORGID = vn.NUMORGID
Where i.VOYAGE is not null

Crew Effects

Parameter Name	Parameter Type	Parameter Description

Parameter Name	Parameter Type	Parameter Description
Vessel	Drop-down	Vessel list

It returns an XLS file with the list of crew effects declarations for the crew on board or joining in the next port.

Required settings:

Norwegian Maritime Reporting XREF Interface with code 'NM';
Effects' Codes ineligible for relief from customs duties and taxes or subject to prohibitions or restrictions (e.u. wines, spirits, cigarettes, tobacco, etc.) must be linked to NM XREF codes ('Cigarettes', 'Spirits', 'Wine', 'Other').

Declare @V nvarchar(25);

SELECT 
	CREW.LASTNAME [Family name],
	CREW.FIRSTNAME [Given name(s)],
	R.[NAME] [Rank or rating],
	STUFF(
	(SELECT '; '+
		CASE WHEN LEN(LTRIM(RTRIM(SMOKE.BRAND)))=0 THEN '' ELSE '"'+LTRIM(RTRIM(SMOKE.BRAND))+'" ' END +
		SCODE.[TEXT]+
		Isnull(Space(1)+Convert(varchar,SMOKE.ITEMS)+Space(1)+Isnull((SELECT [NAME] FROM PW001C86 WHERE CODE = SMOKE.MEASUREMENT),''),'')
	FROM PW001P15 SMOKE 
	JOIN PW001C84 SCODE ON SMOKE.CODE = SCODE.CODE
		AND SCODE.CODE IN (SELECT T.CODE FROM PW001C000_XREF T
						WHERE INTERFACE_XREF = 'NM'
							AND T.TABLE_REF = 84
							AND T.XREF_CODE = 'Cigarettes')
	WHERE SMOKE.PIN = CREW.PIN
	FOR XML PATH ('')
	),1,2,'') [Cigarettes],
	STUFF(
	(SELECT '; '+
		CASE WHEN LEN(LTRIM(RTRIM(DRINK.BRAND)))=0 THEN '' ELSE '"'+LTRIM(RTRIM(DRINK.BRAND))+'" ' END +
		SCODE.[TEXT]+
		Isnull(Space(1)+Convert(varchar,DRINK.ITEMS)+Space(1)+Isnull((SELECT [NAME] FROM PW001C86 WHERE CODE = DRINK.MEASUREMENT),''),'')
	FROM PW001P15 DRINK 
	JOIN PW001C84 SCODE ON DRINK.CODE = SCODE.CODE
		AND SCODE.CODE IN (SELECT T.CODE FROM PW001C000_XREF T
						WHERE INTERFACE_XREF = 'NM'
							AND T.TABLE_REF = 84
							AND T.XREF_CODE = 'Spirits')
	WHERE DRINK.PIN = CREW.PIN
	FOR XML PATH ('')
	),1,2,'') [Spirits],
	STUFF(
	(SELECT '; '+
		CASE WHEN LEN(LTRIM(RTRIM(BOOZ.BRAND)))=0 THEN '' ELSE '"'+LTRIM(RTRIM(BOOZ.BRAND))+'" ' END +
		SCODE.[TEXT]+
		Isnull(Space(1)+Convert(varchar,BOOZ.ITEMS)+Space(1)+Isnull((SELECT [NAME] FROM PW001C86 WHERE CODE = BOOZ.MEASUREMENT),''),'')
	FROM PW001P15 BOOZ 
	JOIN PW001C84 SCODE ON BOOZ.CODE = SCODE.CODE
		AND SCODE.CODE IN (SELECT T.CODE FROM PW001C000_XREF T
						WHERE INTERFACE_XREF = 'NM'
							AND T.TABLE_REF = 84
							AND T.XREF_CODE = 'Wine')
	WHERE BOOZ.PIN = CREW.PIN
	FOR XML PATH ('')
	),1,2,'') [Wine],
	STUFF(
	(SELECT '; '+
		CASE WHEN LEN(LTRIM(RTRIM(OTHER.BRAND)))=0 THEN '' ELSE '"'+LTRIM(RTRIM(OTHER.BRAND))+'" ' END +
		SCODE.[TEXT]+
		Isnull(Space(1)+Convert(varchar,OTHER.ITEMS)+Space(1)+Isnull((SELECT [NAME] FROM PW001C86 WHERE CODE = OTHER.MEASUREMENT),''),'')
	FROM PW001P15 OTHER 
	JOIN PW001C84 SCODE ON OTHER.CODE = SCODE.CODE
		AND SCODE.CODE IN (SELECT T.CODE FROM PW001C000_XREF T
						WHERE INTERFACE_XREF = 'NM'
							AND T.TABLE_REF = 84
							AND T.XREF_CODE = 'Other')
	WHERE OTHER.PIN = CREW.PIN
	FOR XML PATH ('')
	),1,2,'') [Other]

FROM PW001P01 CREW
LEFT JOIN PWORGVESACT NI on NI.NUMORGID = @V
		AND NI.ETA = (SELECT MIN(T.ETA) FROM PWORGVESACT T
					  WHERE T.NUMORGID = @V AND T.ETD>GETDATE()
					  )
LEFT JOIN PW001P03 A ON CREW.PIN = A.PIN AND A.PLANNED!='Y'
		AND A.CODE IN (SELECT CODE FROM PW001C12 WHERE OPTIONS LIKE '%S%')
		AND CONVERT(DATE,ISNULL(A.DATETO,GETDATE()))>=CONVERT(DATE,GETDATE())
		AND dbo.ad_scanorgtree(A.NUMORGID,3) = @V
LEFT JOIN PW001P03 PA ON CREW.PIN = PA.PIN AND PA.PLANNED='Y' AND A.SEQUENCENO IS NULL
		AND PA.CODE IN (SELECT CODE FROM PW001C12 WHERE OPTIONS LIKE '%S%')
		AND PA.SEQUENCENO IN (SELECT CC.NEXTACT FROM PWCCMCHANGECREWLIST CC
							  WHERE CC.SIGNON  = 1
								AND CC.SEQNO = NI.SEQNO
								)
JOIN PW001C02 R ON R.CODE = ISNULL(ISNULL(A.[RANK],PA.[RANK]),CREW.[RANK]) and R.OPTIONS NOT LIKE '%S%'

WHERE ISNULL(A.SEQUENCENO,PA.SEQUENCENO) is not null
	AND CREW.PIN IN (SELECT PIN FROM PW001P15 WHERE CODE IN (SELECT CODE FROM PW001C000_XREF WHERE INTERFACE_XREF = 'NM' AND TABLE_REF=84 AND XREF_CODE IN ('Cigarettes','Spirits','Wine','Other')))
ORDER BY CREW.PIN

Current Crew List

Parameter Name	Parameter Type	Parameter Description

Parameter Name	Parameter Type	Parameter Description
Vessel	Drop-down	Vessel list

It returns an XLS file with the list of crew onboard or joining in the next port.

Declare @V INT = <:V>; /*Vessel NUMORGID*/

SELECT --CREW.PIN,ISNULL(a.datefrom,pa.DATEFROM),CREW.NATIONALITY,
	ROW_NUMBER() OVER(ORDER BY CREW.PIN ASC) [No],
	CREW.LASTNAME [Family name],
	CREW.FIRSTNAME [Given name(s)],
	N.ISO3166_ALPHA3 [Nationality],
	CONVERT(VARCHAR,CREW.BIRTHDATE,104) [Date of birth],
	CREW.PLACEOFBIRTH,
	DOCTYPE.VISATYPE [Nature of identity document],
	ISNULL(ID.TDNUMBER,SB.TDNUMBER) [Number of identity document],
	ISNULL(ID.ISSUEDWHERE,SB.ISSUEDWHERE) [Identity document issued in],
	CASE
		WHEN A.SEQUENCENO IS NOT NULL
			AND A.SEQUENCENO IN (SELECT CC.CURRENTACT FROM PWCCMCHANGECREWLIST CC
							  WHERE CC.SIGNON  = 0
								AND CC.SEQNO = NI.SEQNO
								)
			THEN 'Dismbarking at arrival' 
		WHEN A.SEQUENCENO IS NOT NULL
			THEN 'Onboard' 
		WHEN PA.SEQUENCENO IS NOT NULL
			THEN 'Embarking at arrival' 
	END [Muster Status],
	NULL [Disembarking itinerary identifier],
	NULL [Exemp from mandatory travel insurance],
	R.[NAME] [Rank or rating],
	/*CASE
		WHEN CREW.SEX = 'M' Then 'Mail'
		WHEN CREW.SEX = 'F' Then 'Femail'
		WHEN CREW.SEX = 'U' Then 'Non-binary'
		ELSE CREW.SEX
	END*/ 
	CREW.SEX [Gender],
	VISA.TDNUMBER [Visa/Rsidence Permit Number],
	VISA.ISSUEDWHERE [Visa issued in],
	STUFF(
	(SELECT '; '+
		CASE WHEN LEN(LTRIM(RTRIM(CE.BRAND)))=0 THEN '' ELSE '"'+LTRIM(RTRIM(CE.BRAND))+'" ' END +
		SCODE.[TEXT]+
		Isnull(Space(1)+Convert(varchar,CE.ITEMS)+Space(1)+Isnull((SELECT [NAME] FROM PW001C86 WHERE CODE = CE.MEASUREMENT),''),'')
	FROM PW001P15 CE 
	JOIN PW001C84 SCODE ON CE.CODE = SCODE.CODE
		AND SCODE.CODE IN (SELECT T.CODE FROM PW001C000_XREF T
						   WHERE INTERFACE_XREF = 'NM'
							AND T.TABLE_REF = 84
							AND T.XREF_CODE IN ('Cigarettes','Spirits','Wine','Other')
							)
	WHERE CE.PIN = CREW.PIN
	FOR XML PATH ('')
	),1,2,'') [Effects],
	SB.TDNUMBER [Seamans Book Number],
	SB.ISSUEDWHERE [Seamans Book Issued in],
	(SELECT ISO3166_ALPHA3 FROM PWCOUNTRY WHERE COUNTRYCODE=ISNULL(ID.ISSUE_COUNTRY,SB.ISSUE_COUNTRY)) ISCOUNTRY
FROM PW001P01 CREW
LEFT JOIN PWORGVESACT NI on NI.NUMORGID = @V
		AND NI.ETA = (SELECT MIN(T.ETA) FROM PWORGVESACT T
					  WHERE T.NUMORGID = @V AND T.ETD>GETDATE()
					  )
LEFT JOIN PW001P03 A ON CREW.PIN = A.PIN AND A.PLANNED!='Y'
		AND A.CODE IN (SELECT CODE FROM PW001C12 WHERE OPTIONS LIKE '%S%')
		AND CONVERT(DATE,ISNULL(A.DATETO,GETDATE()))>=CONVERT(DATE,GETDATE())
		AND dbo.ad_scanorgtree(A.NUMORGID,3) = @V
LEFT JOIN PW001P03 PA ON CREW.PIN = PA.PIN AND PA.PLANNED='Y' AND A.SEQUENCENO IS NULL
		AND PA.CODE IN (SELECT CODE FROM PW001C12 WHERE OPTIONS LIKE '%S%')
		AND PA.SEQUENCENO IN (SELECT CC.NEXTACT FROM PWCCMCHANGECREWLIST CC
							  WHERE CC.SIGNON  = 1
								AND CC.SEQNO = NI.SEQNO
								)
LEFT JOIN PWCOUNTRY N ON N.COUNTRYCODE = CREW.NATIONALITY
LEFT JOIN PW001P08 ID ON ID.PIN = CREW.PIN
		AND ID.CODE in (SELECT CODE FROM PW001C23 where OPTIONS LIKE '%T%' and OPTIONS NOT LIKE '%P%')
		AND CONVERT(DATE,ISNULL(ID.DATETO,GETDATE()))>=CONVERT(DATE,GETDATE())
		AND ID.PASSPORTPRIORITY =
				(SELECT MIN(T.PASSPORTPRIORITY)
				FROM PW001P08 T
				WHERE T.PIN = CREW.PIN
				AND T.CODE in (SELECT CODE FROM PW001C23 where OPTIONS LIKE '%T%' and OPTIONS NOT LIKE '%P%')
				AND CONVERT(DATE,ISNULL(T.DATETO,GETDATE()))>=CONVERT(DATE,GETDATE())
				)
LEFT JOIN PW001P08 SB ON SB.PIN = CREW.PIN
		AND SB.CODE in (SELECT CODE FROM PW001C23 where OPTIONS LIKE '%S%' and OPTIONS NOT LIKE '%P%')
		AND CONVERT(DATE,ISNULL(SB.DATETO,GETDATE()))>=CONVERT(DATE,GETDATE())
		AND SB.PASSPORTPRIORITY =
				(SELECT MIN(T.PASSPORTPRIORITY)
				FROM PW001P08 T
				WHERE T.PIN = CREW.PIN
				AND T.CODE in (SELECT CODE FROM PW001C23 where OPTIONS LIKE '%S%' and OPTIONS NOT LIKE '%P%')
				AND CONVERT(DATE,ISNULL(T.DATETO,GETDATE()))>=CONVERT(DATE,GETDATE())
				)
LEFT JOIN PW001P08 VISA ON VISA.PIN = CREW.PIN
		AND VISA.CODE in (SELECT CODE FROM PW001C000_XREF WHERE INTERFACE_XREF = 'NM' AND TABLE_REF=23 AND XREF_CODE = 'VISA')
		AND CONVERT(DATE,ISNULL(VISA.DATETO,GETDATE()))>=CONVERT(DATE,GETDATE())
		AND VISA.PASSPORTPRIORITY =
				(SELECT MIN(T.PASSPORTPRIORITY)
				FROM PW001P08 T
				WHERE T.PIN = CREW.PIN
				AND T.CODE in (SELECT CODE FROM PW001C000_XREF WHERE INTERFACE_XREF = 'NM' AND TABLE_REF=23 AND XREF_CODE = 'VISA')
				AND CONVERT(DATE,ISNULL(T.DATETO,GETDATE()))>=CONVERT(DATE,GETDATE())
				)
LEFT JOIN PW001C23 DOCTYPE on DOCTYPE.CODE = ISNULL(ID.CODE,SB.CODE)
JOIN PW001C02 R ON R.CODE = ISNULL(ISNULL(A.[RANK],PA.[RANK]),CREW.[RANK]) and R.OPTIONS NOT LIKE '%S%'
WHERE ISNULL(A.SEQUENCENO,PA.SEQUENCENO) is not null

Current Passenger List

Parameter Name	Parameter Type	Parameter Description

Parameter Name	Parameter Type	Parameter Description
Vessel	Drop-down	Vessel list

It returns an XLS file with the list of passengers (crew with supernumerary rank) onboard or joining in the next port.

Reports, Views and Similar

SafeSeaNet Reporting - Maritime Single Window

Description

Cruise Itinerary

Crew Effects

Current Crew List

Current Passenger List

Attachment