| Table of Contents | ||||||||
|---|---|---|---|---|---|---|---|---|
|
| Page Properties | ||||||
|---|---|---|---|---|---|---|
|
...
| Info |
|---|
Read more about how to work with the Merge documents https://adonishr.atlassian.net/l/c/refaMVwF |
...
This application can in most cases be used by all customers who need to apply for a B1B2 visa on behalf of their employees/crew.
Word Documents
| View file | ||
|---|---|---|
|
Setup of Document
Merge Properties
As the Select statement is added as a Crew List View, we are fetching the fields from this view.
select * from pw001srv402
...
Bookmark Setup Form
...
Certain fields are set as Editable so they can either be overwritten or if they don’t exist in the system they can be manually filled in via the dialogue window before merging.
...
SQL Statement
Adonis recommends adding the SQL Statement as a Crew List View so that all information can be checked before having to merge the document.
...
| Expand | title | SQL Statement
|---|
| Code Block |
CREATE VIEW dbo.PW001SRV402
AS
SELECT
p01.pin,
p01.Titlename TitleName,
p01.FIRSTNAME FirstName,
p01.middlename MiddleName,
p01.lastname LastName,
p01.Birthdate Birthdate,
p01.sex as Gender,
case when p01.sex='M' then 'him' when p01.sex='F' then 'her' End as Sex,
natcountry.countryname Nationality,
pass.tdnumber PassportNumber,
orgves.NAME AS VesselName,
ves.IMONUMBER as VesselIMONumber,
orgpos.NAME as Position,
p03.datefrom JoiningDate,
(p03.datefrom + 30) as valid,
countryOn.countryname Joining_Country,
'' as CourseDateFrom,
'' as CourseName,
'' as TrainingProvider,
--A bookmark can never be used more than one time, so we need to create the same fields with a different name
--for as many times as needed in the document
p01.Titlename TitleName2,
p01.FIRSTNAME FirstName2,
p01.middlename MiddleName2,
p01.lastname LastName2,
p01.Titlename TitleName3,
p01.FIRSTNAME FirstName3,
p01.middlename MiddleName3,
p01.lastname LastName3,
p01.Titlename TitleName4,
p01.FIRSTNAME FirstName4,
p01.middlename MiddleName4,
p01.lastname LastName4,
orgves.NAME AS VesselName1,
p01.Client as Numorgid,
p01.Employmentstartdate,
p01.Employmentenddate
FROM PW001P01 p01
JOIN PW001P03 p03
ON p03.PIN = p01.PIN
AND p03.DATEFROM > GETDATE()
AND p03.CODE IN (SELECT CODE
FROM PW001C12
WHERE OPTIONS LIKE '%S%')
AND NOT EXISTS (SELECT 1
FROM PW001P03 t
WHERE t.PIN = p01.Pin
AND t.DATEFROM > GETDATE()
AND t.CODE IN (SELECT CODE
FROM PW001C12
WHERE OPTIONS LIKE '%S%')
AND t.DATEFROM < p03.DateFrom
)
left join pw001p08 pass on pass.pin = p01.pin and pass.code = 'PP'
left join pwcountry natcountry on natcountry.countrycode = p01.nationality
LEFT JOIN PWORG orgves ON orgves.NUMORGID = dbo.ad_scanorgtree(p03.NUMORGID, 3)
LEFT JOIN PWORGVES VES ON orgves.NUMORGID = ves.NUMORGID
LEFT JOIN PWORG orgpos ON orgpos.NUMORGID = dbo.ad_scanorgtree(p03.NUMORGID, 5)
LEFT JOIN PWCCMCHANGECREWLIST ccl ON ccl.NEXTACT = p03.SEQUENCENO
LEFT JOIN PWORGVESACT vaOn ON vaOn.SEQNO = ccl.SEQNO
LEFT JOIN PWPORT portOn ON portOn.PORTCODE = vaOn.PLACEFROM
LEFT JOIN PWCOUNTRY countryOn ON countryOn.COUNTRYCODE = portOn.COUNTRYCODE |