Adonis APM  →  Shipmoney

Shipmoney uses the APP API and the sql view API_Shipmoney to substract the member information. The below sql statement, and the APP need to be installed on the shoreside. 

see How to consume Adonis API

Create view statement

CREATE VIEW [dbo].[API_Shipmoney]
as
SELECT
p01.pin,org.name AS VesselName,
p01.FIRSTNAME AS FirstName,
p01.LASTNAME AS LastName,
p01.[NAME] AS NAME,
NULL AS OtherSeafarerID,
p01.ADDRESS1 AS Address1,
p01.ADDRESS2 AS Address2,
p01.ADDRESS3 AS Address3,
p01.POSTCODE AS PostalCode,
CONVERT(VARCHAR,p01.birthdate,23) AS BirthDate,
p01.POSTPLACE AS City,
p01.NATIONALITY AS Citizenship,
p01.ADDRESS_COUNTRY AS CountryCode,
us.COUNTYNAME AS StateRegion,
c02.NAME AS Occupation,
'Passport' AS Passport,
p08.TDNUMBER AS IDValue,
CONVERT(VARCHAR,p08.DATETO,23) AS IDExpire,
scan.DOCNO AS IDImage,
phone.TELENO AS MobilePhoneNumber,
email.TELENO AS EmailAddress,
'RCYC' AS AgencyName,
p01.CURRENCYFORPAYMENT AS ContractId,
CONVERT(VARCHAR,dbo.ad_signondate(p03.numorgid,p03.pin,p03.datefrom),23) AS SignOn,
CONVERT(VARCHAR,p03.dateto,23) AS ConfirmedSignOff,
CONVERT(VARCHAR,p03.todateestimated,23) AS plannedSignOff
FROM pw001p01 p01
LEFT JOIN (SELECT * FROM (SELECT *,ROW_NUMBER() OVER(PARTITION BY p03.PIN ORDER BY p03.DATEFROM DESC) AS Nr FROM pw001p03 p03
WHERE EXISTS(SELECT 1 FROM pw001c12 c12 WHERE c12.CODE=p03.CODE AND c12.OPTIONS LIKE '%S%') AND ISNULL(p03.PLANNED,'N') <> 'Y') p03
WHERE p03.Nr = 1) p03 ON p03.PIN=p01.PIN
LEFT JOIN (SELECT NUMORGID,dbo.ad_scanorgtree(NUMORGID, 3) AS VesselID FROM pworg WHERE ORGTYPE IN ('3', '4', '5')) ou ON ou.NUMORGID=p03.NUMORGID
LEFT JOIN pw001c12 c12 ON c12.CODE=p03.CODE
LEFT JOIN pworg org ON org.NUMORGID=ou.VesselID
LEFT JOIN pw001p0p p0p ON p01.PIN=p0p.PIN AND p0p.PNUMBER='A'
LEFT JOIN pw001c02 c02 ON c02.CODE=p0p.POSITIONID
LEFT JOIN PW001P08 AS p08 ON p08.PIN=p01.PIN AND EXISTS(SELECT 1 FROM PW001C23 C23 WHERE C23.CODE=P08.CODE AND C23.OPTIONS LIKE '%T%')
AND NOT EXISTS (SELECT 1 FROM pw001p08 p08t JOIN PW001C23 C23t on c23t.code=p08t.code and c23t.options like '%T%' WHERE p01.PIN=p08t.PIN AND p08t.DATETO > p08.DATETO)
LEFT JOIN PW001OLEDOCS scan ON p08.scanneddocno=scan.DOCNO
LEFT JOIN (SELECT * FROM (SELECT *,ROW_NUMBER() OVER(PARTITION BY P0T.PIN ORDER BY P0T.REPL_MODIFIEDDATE DESC) AS Nr
FROM PW001P0T p0t WHERE P0T.TELETYPE IN (3)) p0t WHERE p0t.Nr=1) phone ON phone.PIN=p01.PIN
LEFT JOIN (SELECT * FROM (SELECT *,ROW_NUMBER() OVER(PARTITION BY P0T.PIN ORDER BY P0T.REPL_MODIFIEDDATE DESC) AS Nr
FROM PW001P0T p0t WHERE P0T.TELETYPE IN (6)) p0t WHERE p0t.Nr=1) email ON email.PIN=p01.PIN
LEFT JOIN pwpost post ON post.POSTCODE=p01.POSTCODE AND post.COUNTRYCODE=p01.ADDRESS_COUNTRY AND post.[NAME]=p01.POSTPLACE
LEFT JOIN PWCOUNTY us ON us.COUNTYCODE=post.COUNTYCODE AND us.COUNTRYCODE=post.COUNTRYCODE WHERE P03.DATEFROM IS NOT NULL
AND scan.docno is not null and p03.todateestimated is not null
GO

Drop view statement

If there is a need to rebuild the view the following statement can be run

Drop view [dbo].[API_Shipmoney] 

Afterwards the create statement can be sent again. 

Shipmoney → Adonis APM