1 Description | 2 Word Documents | 3 Setup of Document | 4 SQL Statement

Summary	This is a sample of a B1B2 Visa Application
Keywords	Visa Application, B1B2

Read more about how to work with the Merge documents https://adonishr.atlassian.net/l/c/refaMVwF

Description

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

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.

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

Adonis HR Reports, Views and Similar

B1B2 Visa Application