| Table of Contents |
|---|
| minLevel | 1 |
|---|
| maxLevel | 1 |
|---|
| type | flat |
|---|
| separator | pipe |
|---|
|
| Page Properties |
|---|
Description Summary | Script for Merge Contract that shows Malta Social Security and Maternity Leave contribution amounts | Keywords | Contract, Malta _Social | Category | Contracts | Payroll |
|
Description
This script is used for creating a Mail Merge Contract that contains personal data, Passport, Seaman’s book details, joining data like Joining Port, Vessel, Position, and monthly salary amounts like Basic Wage, Guaranteed Overtime, Leave Payment, etc. It also contains amounts of Employee’s Malta Social Security contribution and Employer’s contribution - which is a total of Malta Social Security contribution and Malta Maternity Leave contribution (both calculated based on the conditions for EC 887 Malta Maternity leave and 889 Malta Social Security).
...
SQL Statement
| Code Block |
|---|
-- upd. 10.09.2021 |
...
...
...
...
...
...
...
...
...
P01.PLACEOFBIRTH BIRTHPLACE, |
...
...
DATEDIFF (MONTH, P03.DATEFROM, P03.TODATEESTIMATED +1) AS MONTHS, |
...
PASS.TDNUMBER PASSPORTNO, |
...
PASS.DATETO PASSPORT_EXPIRY, |
...
PASS.DATEFROM PASSPORT_ISSUE, |
...
CTRPASS.COUNTRYNAME ISSUED_BY, |
...
...
WHEN SB1.CODE IS NOT NULL THEN |
...
...
WHEN SB2.CODE IS NOT NULL THEN |
...
...
WHEN SB3.CODE IS NOT NULL THEN |
...
...
...
...
WHEN SB1.CODE IS NOT NULL THEN |
...
...
WHEN SB2.CODE IS NOT NULL THEN |
...
...
WHEN SB3.CODE IS NOT NULL THEN |
...
...
END AS SEAMAN_BOOK_EXPIRY, |
...
...
WHEN SB1.CODE IS NOT NULL THEN |
...
...
WHEN SB2.CODE IS NOT NULL THEN |
...
...
WHEN SB3.CODE IS NOT NULL THEN |
...
...
END AS SEAMAN_BOOK_ISSUE, |
...
...
WHEN SB1.CODE IS NOT NULL THEN |
...
...
WHEN SB2.CODE IS NOT NULL THEN |
...
...
WHEN SB3.CODE IS NOT NULL THEN |
...
...
END AS SEAMAN_BOOK_ISSUED_BY, |
...
...
...
ves.IMONUMBER as IMO_NUMBER, |
...
VES.REGISTERFLAG REGISTER_FLAG, |
...
VES.VESSELTYPE VESSEL_TYPE, |
...
PORTON.NAME ONSIGNING_PORT, |
...
PORTON.NAME ONSIGNING_PORT1, |
...
P01.CONTRACTSTARTDATE CONTRACT_START, |
...
FORMAT(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1), 'C') as BASIC, |
...
FORMAT(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 7), 'C') as GOT, |
...
FORMAT(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 11), 'C') as WEEKEND, |
...
FORMAT(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 14), 'C') as LP, |
...
FORMAT(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 5), 'C') as OT, |
...
FORMAT(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 20), 'C') as SUPPL1, |
...
FORMAT(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 21), 'C') as SUPPL2, |
...
FORMAT(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0,25), 'C') as TOTAL, |
...
FORMAT(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 19), 'C') as RESERVERD, |
...
-- Calculating Seafarer's part of Malta social security contribution |
...
FORMAT ((CASE WHEN --1. not EU address or No in PR 10 - 0 contribution |
...
...
...
...
...
P1R10.RATEN <> 'No') THEN '0' |
...
WHEN --2. Student <18 - contract type='10' |
...
P01.CONTRACTTYPE = '10' AND |
...
Datediff(yy,p01.birthdate,getdate())<18 AND |
...
((dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33)*CB33.RATE/100) > CB32.RATENO THEN |
...
cast(CB32.RATE as numeric(10,2))*4.33 --4.33 - Nr of weeks in a 30-days month |
...
WHEN --3. Student over 18 |
...
...
AND Datediff(yy,p01.birthdate,getdate())>=18 |
...
AND ((dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33/100)*CB33.RATE) > CB34.RATE |
...
...
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/100) * CB31.RATE |
...
WHEN --4. //Cat A - Person under 18 and earning not more then indicated in CB21 |
...
Datediff(yy,p01.birthdate,getdate())<18 AND |
...
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33) <=CB21.RATE |
...
...
cast(CB25.RATE as numeric(10,2)) * 4.33 |
...
WHEN -- 5. Cat - B Persons aged 18 and over earning not more then amount indicated in CB21 |
...
Datediff(yy,p01.birthdate,getdate())>=18 AND |
...
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33) <=CB21.RATE AND (P1R10.RATEN='MIN' OR P1R10.RATEN='min') |
...
...
cast(CB26.RATE as numeric(10,2)) * 4.33 |
...
WHEN --6. Cat - B Persons aged 18 and over earning not more then amount indicated in CB21 and with volunteer amount |
...
Datediff(yy,p01.birthdate,getdate())>=18 AND |
...
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33) <=CB21.RATE |
...
...
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/100)*CB27.RATE |
...
WHEN --7. Cat C - All persons whose basic weekly wage is equal to or exceeds CB21 and less then CB22 and born before 31.12.1961 |
...
P01.BIRTHDATE <='19611231' AND |
...
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33)>= CB21.RATE AND |
...
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33) <=CB22.RATE |
...
...
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/100)*CB28.RATE |
...
WHEN -- 8. Cat D - All persons whose basic weekly wage is equal to or exceeds CB22 and born before 31.12.1961 |
...
P01.BIRTHDATE <='19611231' AND |
...
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33)> CB22.RATE |
...
...
cast(CB29.RATE as numeric(10,2)) * 4.33 |
...
WHEN --9. Cat C - All persons whose basic weekly wage is equal to or exceeds CB21 and less then CB23 and born after 31.12.1961 |
...
P01.BIRTHDATE >'19611231' AND |
...
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33)> CB21.RATE AND |
...
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33) <=CB23.RATE |
...
THEN (CONVERT(MONEY, ROUND(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1), 2), 0)/100)*CB28.RATE |
...
WHEN --10. Cat D - All persons whose basic weekly wage is equal to or exceeds CB23 and born after 31.12.1961 |
...
P01.BIRTHDATE >'19611231' AND |
...
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33)> CB23.RATE |
...
...
cast(CB30.RATE as numeric(10,2))*4.33 |
...
END),'C') AS SSC_SEAFARER, |
...
-- Malta Maternity Leave - in case need to check separate amount for MLT Maternity leave used in calculation of SSC_EMPLOYER |
...
...
FORMAT ((CASE WHEN --1. not EU address or No in PR 10 - 0 contribution |
...
...
...
...
...
P1R10.RATEN <> 'No') THEN '0' |
...
WHEN --2. Student <18 - contract type='10' |
...
P01.CONTRACTTYPE = '10' AND |
...
Datediff(yy,p01.birthdate,getdate())<18 AND |
...
((dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33)*CB53.RATE/100) > CB52.RATENO THEN |
...
cast(CB52.RATE as numeric(10,2))*4.33 --4.33 - Nr of weeks in a 30-days month |
...
WHEN --3. Student over 18 |
...
...
AND Datediff(yy,p01.birthdate,getdate())>=18 |
...
AND ((dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33/100)*CB53.RATE) > CB54.RATE |
...
...
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/100) * CB51.RATE |
...
WHEN --4. //Cat A - Person under 18 and earning not more then indicated in CB21 |
...
Datediff(yy,p01.birthdate,getdate())<18 AND |
...
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33) <=CB41.RATE |
...
...
cast(CB45.RATE as numeric(10,2)) * 4.33 |
...
WHEN -- 5. Cat - B Persons aged 18 and over earning not more then amount indicated in CB21 |
...
Datediff(yy,p01.birthdate,getdate())>=18 AND |
...
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33) <=CB41.RATE AND (P1R10.RATEN='MIN' OR P1R10.RATEN='min') |
...
...
cast(CB46.RATE as numeric(10,2)) * 4.33 |
...
WHEN --6. Cat - B Persons aged 18 and over earning not more then amount indicated in CB21 and with volunteer amount |
...
Datediff(yy,p01.birthdate,getdate())>=18 AND |
...
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33) <=CB41.RATE |
...
...
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/100)*CB27.RATE |
...
WHEN --7. Cat C - All persons whose basic weekly wage is equal to or exceeds CB21 and less then CB22 and born before 31.12.1961 |
...
P01.BIRTHDATE <='19611231' AND |
...
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33)>= CB41.RATE AND |
...
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33) <=CB42.RATE |
...
...
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/100)*CB48.RATE |
...
WHEN -- 8. Cat D - All persons whose basic weekly wage is equal to or exceeds CB22 and born before 31.12.1961 |
...
P01.BIRTHDATE <='19611231' AND |
...
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33)> CB42.RATE |
...
...
cast(CB49.RATE as numeric(10,2)) * 4.33 |
...
WHEN --9. Cat C - All persons whose basic weekly wage is equal to or exceeds CB21 and less then CB23 and born after 31.12.1961 |
...
P01.BIRTHDATE >'19611231' AND |
...
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33)> CB41.RATE AND |
...
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33) <=CB43.RATE |
...
THEN (CONVERT(MONEY, ROUND(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1), 2), 0)/100)*CB48.RATE |
...
WHEN --10. Cat D - All persons whose basic weekly wage is equal to or exceeds CB23 and born after 31.12.1961 |
...
P01.BIRTHDATE >'19611231' AND |
...
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33)> CB43.RATE |
...
...
cast(CB50.RATE as numeric(10,2))4.33 |
...
END),'C') AS SSC_Materinity/ |
...
-- Calculating Employer's part of Malta contribution: Social Security+Maternity leave |
...
-- Malta Social Security: |
...
...
((CASE WHEN --1. not EU address or No in PR 10 - 0 contribution |
...
...
...
...
...
P1R10.RATEN <> 'No') THEN '0' |
...
WHEN --2. Student <18 - contract type='10' |
...
P01.CONTRACTTYPE = '10' AND |
...
Datediff(yy,p01.birthdate,getdate())<18 AND |
...
((dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33)*CB33.RATE/100) > CB32.RATENO THEN |
...
cast(CB32.RATE as numeric(10,2))*4. |
...
33 --4.33 - Nr of weeks in a 30-days month |
...
WHEN --3. Student over 18 |
...
...
AND Datediff(yy,p01.birthdate,getdate())>=18 |
...
AND ((dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33/100)*CB33.RATE) > CB34.RATE |
...
...
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/100) * CB31.RATE |
...
WHEN --4. //Cat A - Person under 18 and earning not more then indicated in CB21 |
...
Datediff(yy,p01.birthdate,getdate())<18 AND |
...
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33) <=CB21.RATE |
...
...
cast(CB25.RATE as numeric(10,2)) * 4.33 |
...
WHEN -- 5. Cat - B Persons aged 18 and over earning not more then amount indicated in CB21 |
...
Datediff(yy,p01.birthdate,getdate())>=18 AND |
...
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33) <=CB21.RATE AND (P1R10.RATEN='MIN' OR P1R10.RATEN='min') |
...
...
cast(CB26.RATE as numeric(10,2)) * 4.33 |
...
WHEN --6. Cat - B Persons aged 18 and over earning not more then amount indicated in CB21 and with volunteer amount |
...
Datediff(yy,p01.birthdate,getdate())>=18 AND |
...
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33) <=CB21.RATE |
...
...
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/100)*CB27.RATE |
...
WHEN --7. Cat C - All persons whose basic weekly wage is equal to or exceeds CB21 and less then CB22 and born before 31.12.1961 |
...
P01.BIRTHDATE <='19611231' AND |
...
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33)>= CB21.RATE AND |
...
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33) <=CB22.RATE |
...
...
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/100)*CB28.RATE |
...
WHEN -- 8. Cat D - All persons whose basic weekly wage is equal to or exceeds CB22 and born before 31.12.1961 |
...
P01.BIRTHDATE <='19611231' AND |
...
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33)> CB22.RATE |
...
...
cast(CB29.RATE as numeric(10,2)) * 4.33 |
...
WHEN --9. Cat C - All persons whose basic weekly wage is equal to or exceeds CB21 and less then CB23 and born after 31.12.1961 |
...
P01.BIRTHDATE >'19611231' AND |
...
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33)> CB21.RATE AND |
...
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33) <=CB23.RATE |
...
THEN (CONVERT(MONEY, ROUND(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1), 2), 0)/100)*CB28.RATE |
...
WHEN --10. Cat D - All persons whose basic weekly wage is equal to or exceeds CB23 and born after 31.12.1961 |
...
P01.BIRTHDATE >'19611231' AND |
...
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33)> CB23.RATE |
...
...
cast(CB30.RATE as numeric(10,2))*4.33 |
...
END) + -- + Malta Maternity leave |
...
(CASE WHEN --1. not EU address or No in PR 10 - 0 contribution |
...
...
...
...
...
P1R10.RATEN <> 'No') THEN '0' |
...
WHEN --2. Student <18 - contract type='10' |
...
P01.CONTRACTTYPE = '10' AND |
...
Datediff(yy,p01.birthdate,getdate())<18 AND |
...
((dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33)*CB53.RATE/100) > CB52.RATENO THEN |
...
cast(CB52.RATE as numeric(10,2))*4 |
...
.33 --4.33 - Nr of weeks in a 30-days month |
...
WHEN --3. Student over 18 |
...
...
AND Datediff(yy,p01.birthdate,getdate())>=18 |
...
AND ((dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33/100)*CB53.RATE) > CB54.RATE |
...
...
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/100) * CB51.RATE |
...
WHEN --4. //Cat A - Person under 18 and earning not more then indicated in CB21 |
...
Datediff(yy,p01.birthdate,getdate())<18 AND |
...
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33) <=CB41.RATE |
...
...
cast(CB45.RATE as numeric(10,2)) * 4.33 |
...
WHEN -- 5. Cat - B Persons aged 18 and over earning not more then amount indicated in CB21 |
...
Datediff(yy,p01.birthdate,getdate())>=18 AND |
...
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33) <=CB41.RATE AND (P1R10.RATEN='MIN' OR P1R10.RATEN='min') |
...
...
cast(CB46.RATE as numeric(10,2)) * 4.33 |
...
WHEN --6. Cat - B Persons aged 18 and over earning not more then amount indicated in CB21 and with volunteer amount |
...
Datediff(yy,p01.birthdate,getdate())>=18 AND |
...
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33) <=CB41.RATE |
...
...
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/100)*CB27.RATE |
...
WHEN --7. Cat C - All persons whose basic weekly wage is equal to or exceeds CB21 and less then CB22 and born before 31.12.1961 |
...
P01.BIRTHDATE <='19611231' AND |
...
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33)>= CB41.RATE AND |
...
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33) <=CB42.RATE |
...
...
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/100)*CB48.RATE |
...
WHEN -- 8. Cat D - All persons whose basic weekly wage is equal to or exceeds CB22 and born before 31.12.1961 |
...
P01.BIRTHDATE <='19611231' AND |
...
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33)> CB42.RATE |
...
...
cast(CB49.RATE as numeric(10,2)) * 4.33 |
...
WHEN --9. Cat C - All persons whose basic weekly wage is equal to or exceeds CB21 and less then CB23 and born after 31.12.1961 |
...
P01.BIRTHDATE >'19611231' AND |
...
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33)> CB41.RATE AND |
...
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33) <=CB43.RATE |
...
THEN (CONVERT(MONEY, ROUND(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1), 2), 0)/100)*CB48.RATE |
...
WHEN --10. Cat D - All persons whose basic weekly wage is equal to or exceeds CB23 and born after 31.12.1961 |
...
P01.BIRTHDATE >'19611231' AND |
...
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33)> CB43.RATE |
...
...
cast(CB50.RATE as numeric(10,2))*4.33 |
...
END),'C') AS SSC_EMPLOYER |
...
...
LEFT JOIN PWORG ORG ON ORG.NUMORGID=P01.CLIENT |
...
LEFT JOIN PW001P03 P03 ON P03.PIN=P01.PIN |
...
AND P03.Code IN (SELECT Code FROM PW001C12 WHERE OPTIONS LIKE '%S%') |
...
AND P03.DATEFROM >= GETDATE() |
...
...
...
...
...
AND p03t.CODE IN (SELECT Code FROM PW001C12 WHERE OPTIONS LIKE '%S%') |
...
AND p03t.DATEFROM >= GETDATE() |
...
...
AND p03t.DATEFROM < P03.Datefrom |
...
...
LEFT JOIN PW001P08 PASS ON P01.PIN=PASS.PIN |
...
...
LEFT JOIN PW001P08 SB1 ON SB1.PIN=P01.PIN and SB1.CODE='S005' |
...
LEFT JOIN PW001P08 SB2 ON SB2.PIN=P01.PIN and SB2.CODE='S006' |
...
LEFT JOIN PW001P08 SB3 ON SB3.PIN=P01.PIN and SB3.CODE='S007' |
...
left join pwcountry nat on nat.countrycode=p01.nationality |
...
...
ON orgves.NUMORGID = dbo.ad_scanorgtree(p03.NUMORGID, 3) |
...
...
ON orgpos.NUMORGID = dbo.ad_scanorgtree(p03.NUMORGID, 5) |
...
...
ON ves.NUMORGID = orgves.NUMORGID |
...
LEFT JOIN PWCCMCHANGECREWLIST sgnon |
...
ON sgnon.NEXTACT = p03.SEQUENCENO |
...
LEFT JOIN PWORGVESACT vpOn |
...
ON vpOn.SEQNO = sgnon.SEQNO |
...
...
ON portOn.PORTCODE = vpOn.PLACEFROM |
...
LEFT JOIN PWPSC000 PYS ON PYS.REGULATIVECODE = P03.PAYSCALETABLE |
...
...
...
...
...
ON CB21.NUMORGID = p01.CLIENT |
...
...
...
ON CB22.NUMORGID = p01.CLIENT |
...
...
...
ON CB23.NUMORGID = p01.CLIENT |
...
...
...
ON CB25.NUMORGID = p01.CLIENT |
...
...
...
ON CB26.NUMORGID = p01.CLIENT |
...
...
...
ON CB27.NUMORGID = p01.CLIENT |
...
...
...
ON CB28.NUMORGID = p01.CLIENT |
...
...
...
ON CB29.NUMORGID = p01.CLIENT |
...
...
...
ON CB30.NUMORGID = p01.CLIENT |
...
...
...
ON CB31.NUMORGID = p01.CLIENT |
...
...
...
ON CB32.NUMORGID = p01.CLIENT |
...
...
...
ON CB33.NUMORGID = p01.CLIENT |
...
...
...
ON CB34.NUMORGID = p01.CLIENT |
...
...
...
ON CB41.NUMORGID = p01.CLIENT |
...
...
...
ON CB42.NUMORGID = p01.CLIENT |
...
...
...
ON CB43.NUMORGID = p01.CLIENT |
...
...
...
ON CB45.NUMORGID = p01.CLIENT |
...
...
...
ON CB46.NUMORGID = p01.CLIENT |
...
...
...
ON CB47.NUMORGID = p01.CLIENT |
...
...
...
ON CB48.NUMORGID = p01.CLIENT |
...
...
...
ON CB49.NUMORGID = p01.CLIENT |
...
...
...
ON CB50.NUMORGID = p01.CLIENT |
...
...
...
ON CB51.NUMORGID = p01.CLIENT |
...
...
...
ON CB52.NUMORGID = p01.CLIENT |
...
...
...
ON CB53.NUMORGID = p01.CLIENT |
...
...
...
ON CB54.NUMORGID = p01.CLIENT |
...
...
LEFT JOIN PWCOUNTRY CTRPASS |
...
ON CTRPASS.COUNTRYCODE=PASS.ISSUE_COUNTRY |
...
LEFT JOIN PWCOUNTRY CTRSB1 |
...
ON CTRSB1.COUNTRYCODE=SB1.ISSUE_COUNTRY |
...
LEFT JOIN PWCOUNTRY CTRSB2 |
...
ON CTRSB2.COUNTRYCODE=SB2.ISSUE_COUNTRY |
...
LEFT JOIN PWCOUNTRY CTRSB3 |
...
ON CTRSB3.COUNTRYCODE=SB3.ISSUE_COUNTRY |
...
LEFT JOIN PWCOUNTRYPROP EUCOUNTRY |
...
ON EUCOUNTRY.COUNTRYCODE=P01.ADDRESS_COUNTRY |