select
pay.PIN,
p01.Name,
persorg.name as Organization,
pay.accountdim2 as VesOrgID,
isnull(ves.name, 'N/A') as 'Vessel Name',
case when pay.ACCOUNTDIM3 is NULL or pay.ACCOUNTDIM3='' then 'N/A'
else pay.ACCOUNTDIM3
end 'Department',
isnull(CAT.NAME, 'Other') as 'Category',
pay.CALCULATEDAMOUNT1 as 'Amount',
pay.periodeused as Period,
CONVERT(VARCHAR,PAY.ENTRYCODE) + ' ' + EC.TEXT AS 'Entry Code',
case when pay.ACCOUNTNOOFCALCAMOUNT1<>'' THEN pay.ACCOUNTNOOFCALCAMOUNT1 + ' ACC.ACCOUNTNO=pay.ACCOUNTNOOFCALCAMOUNT1 THEN pay.ACCOUNTNOOFCALCAMOUNT1 + ' - ' + acc.ACCOUNTTEXT
when acc.ACCOUNTNO=pay.ACCOUNTNOOFCALCAMOUNTCREDIT1 THEN pay.ACCOUNTNOOFCALCAMOUNTCREDIT1 + ' - ' + acc.ACCOUNTTEXT
end 'Account No',
case when ACC.ACCOUNTNO=pay.ACCOUNTNOOFCALCAMOUNT1 THEN 'DEBIT'
when acc.ACCOUNTNO=pay.ACCOUNTNOOFCALCAMOUNTCREDIT1 THEN 'CREDIT'
else 'N/A'
end 'Account type',
pay.rank as Rank,
pay.currencyforpayment as 'Payment Currency ',
p01.client numorgid,
p01.employmentstartdate,
p01.employmentenddate
from
pw001pay pay
left join pw001p01 p01 on p01.pin=pay.pin and CALCULATEDAMOUNT1<>0
left join pworg ves on ves.numorgid=pay.accountdim2
left join pyety001 ec on ec.entrycode=pay.entrycode
left join pworg persorg on persorg.numorgid=p01.client
left join PW001C117 CAT on ec.CATEGORY=CAT.SEQUENCENO and pay.ENTRYCODE=ec.ENTRYCODE
left join PY001ACC ACC on ACC.ACCOUNTNO=pay.ACCOUNTNOOFCALCAMOUNT1 or acc.ACCOUNTNO=pay.ACCOUNTNOOFCALCAMOUNTCREDIT1
where CALCULATEDAMOUNT1<>0
UNION ALL
select
pay.PIN,
p01.Name,
persorg.name as Organization,
pay.accountdim2 as VesOrgID,
isnull(ves.name, 'N/A') as 'Vessel Name',
case when pay.ACCOUNTDIM3 is NULL or pay.ACCOUNTDIM3='' then 'N/A'
else pay.ACCOUNTDIM3
end 'Department',
isnull(CAT.NAME, 'Other') as 'Category',
pay.CALCULATEDAMOUNT2 as 'Amount',
pay.periodeused as Period,
CONVERT(VARCHAR,PAY.ENTRYCODE) + ' ' + EC.TEXT AS 'Entry Code',
case when ACC.ACCOUNTNO=pay.ACCOUNTNOOFCALCAMOUNT2 THEN pay.ACCOUNTNOOFCALCAMOUNT2 + ' - ' + acc.ACCOUNTTEXT
when pay.ACCOUNTNOOFCALCAMOUNTCREDIT1<>'' THEN pay.ACCOUNTNOOFCALCAMOUNTCREDIT1acc.ACCOUNTNO=pay.ACCOUNTNOOFCALCAMOUNTCREDIT2 THEN pay.ACCOUNTNOOFCALCAMOUNTCREDIT2 + ' - ' + acc.ACCOUNTTEXT
end 'Account No',
case when ACC.ACCOUNTNO=pay.ACCOUNTNOOFCALCAMOUNT2 THEN 'DEBIT'
when acc.ACCOUNTNO=pay.ACCOUNTNOOFCALCAMOUNTCREDIT2 THEN 'CREDIT'
else 'N/A'
end 'Account type',
pay.rank as Rank,
pay.currencyforpayment as 'Payment Currency ',
p01.client numorgid,
p01.employmentstartdate,
p01.employmentenddate
from
pw001pay pay
left join pw001p01 p01 on p01.pin=pay.pin and CALCULATEDAMOUNT2<>0
left join pworg ves on ves.numorgid=pay.accountdim2
left join pyety001 ec on ec.entrycode=pay.entrycode
left join pworg persorg on persorg.numorgid=p01.client
left join PW001C117 CAT on ec.CATEGORY=CAT.SEQUENCENO and pay.ENTRYCODE=ec.ENTRYCODE
left join PY001ACC ACC on ACC.ACCOUNTNO=pay.ACCOUNTNOOFCALCAMOUNT2 or acc.ACCOUNTNO=pay.ACCOUNTNOOFCALCAMOUNTCREDIT2
where CALCULATEDAMOUNT2<>0
UNION ALL
select
pay.PIN,
p01.Name,
persorg.name as Organization,
pay.accountdim2 as VesOrgID,
isnull(ves.name, 'N/A') as 'Vessel Name',
case when pay.ACCOUNTDIM3 is NULL or pay.ACCOUNTDIM3='' then 'N/A'
else pay.ACCOUNTDIM3
end 'Department',
isnull(CAT.NAME, 'Other') as 'Category',
pay.CALCULATEDAMOUNT3 as 'Amount',
pay.periodeused as Period,
CONVERT(VARCHAR,PAY.ENTRYCODE) + ' ' + EC.TEXT AS 'Entry Code',
case when ACC.ACCOUNTNO=pay.ACCOUNTNOOFCALCAMOUNT3 THEN pay.ACCOUNTNOOFCALCAMOUNT3 + ' - ' + acc.ACCOUNTTEXT
when acc.ACCOUNTNO=pay.ACCOUNTNOOFCALCAMOUNTCREDIT3 THEN pay.ACCOUNTNOOFCALCAMOUNTCREDIT3 + ' - ' + acc.ACCOUNTTEXT
end 'Account No',
case when ACC.ACCOUNTNO=pay.ACCOUNTNOOFCALCAMOUNT3 THEN 'DEBIT'
when acc.ACCOUNTNO=pay.ACCOUNTNOOFCALCAMOUNTCREDIT3 THEN 'CREDIT'
else 'N/A'
end 'Account type',
pay.rank as Rank,
pay.currencyforpayment as 'Payment Currency ',
p01.client numorgid,
p01.employmentstartdate,
p01.employmentenddate
from
pw001pay pay
left join pw001p01 p01 on p01.pin=pay.pin and CALCULATEDAMOUNT3<>0
left join pworg ves on ves.numorgid=pay.accountdim2
left join pyety001 ec on ec.entrycode=pay.entrycode
left join pworg persorg on persorg.numorgid=p01.client
left join PW001C117 CAT on ec.CATEGORY=CAT.SEQUENCENO and pay.ENTRYCODE=ec.ENTRYCODE
left join PY001ACC ACC on ACC.ACCOUNTNO=pay.ACCOUNTNOOFCALCAMOUNT3 or acc.ACCOUNTNO=pay.ACCOUNTNOOFCALCAMOUNTCREDIT3
where CALCULATEDAMOUNT3<>0
UNION ALL
select
pay.PIN,
p01.Name,
persorg.name as Organization,
pay.accountdim2 as VesOrgID,
isnull(ves.name, 'N/A') as 'Vessel Name',
case when pay.ACCOUNTDIM3 is NULL or pay.ACCOUNTDIM3='' then 'N/A'
else pay.ACCOUNTDIM3
end 'Department',
isnull(CAT.NAME, 'Other') as 'Category',
pay.CALCULATEDAMOUNT4 as 'Amount',
pay.periodeused as Period,
CONVERT(VARCHAR,PAY.ENTRYCODE) + ' ' + EC.TEXT AS 'Entry Code',
case when ACC.ACCOUNTNO=pay.ACCOUNTNOOFCALCAMOUNT4 THEN pay.ACCOUNTNOOFCALCAMOUNT4 + ' - ' + acc.ACCOUNTTEXT
when acc.ACCOUNTNO=pay.ACCOUNTNOOFCALCAMOUNTCREDIT4 THEN pay.ACCOUNTNOOFCALCAMOUNTCREDIT4 + ' - ' + acc.ACCOUNTTEXT
end 'Account No',
case when ACC.ACCOUNTNO=pay.ACCOUNTNOOFCALCAMOUNT4 THEN 'DEBIT'
when acc.ACCOUNTNO=pay.ACCOUNTNOOFCALCAMOUNTCREDIT4 THEN 'CREDIT'
else 'N/A'
end 'Account type',
pay.rank as Rank,
pay.currencyforpayment as 'Payment Currency ',
p01.client numorgid,
p01.employmentstartdate,
p01.employmentenddate
from
pw001pay pay
left join pw001p01 p01 on p01.pin=pay.pin and CALCULATEDAMOUNT4<>0
left join pworg ves on ves.numorgid=pay.accountdim2
left join pyety001 ec on ec.entrycode=pay.entrycode
left join pworg persorg on persorg.numorgid=p01.client
left join PW001C117 CAT on ec.CATEGORY=CAT.SEQUENCENO and pay.ENTRYCODE=ec.ENTRYCODE
left join PY001ACC ACC on ACC.ACCOUNTNO=pay.ACCOUNTNOOFCALCAMOUNT4 or acc.ACCOUNTNO=pay.ACCOUNTNOOFCALCAMOUNTCREDIT4
where CALCULATEDAMOUNT4<>0
UNION ALL
select
pay.PIN,
p01.Name,
persorg.name as Organization,
pay.accountdim2 as VesOrgID,
isnull(ves.name, 'N/A') as 'Vessel Name',
case when pay.ACCOUNTDIM3 is NULL or pay.ACCOUNTDIM3='' then 'N/A'
else pay.ACCOUNTDIM3
end 'Department',
isnull(CAT.NAME, 'Other') as 'Category',
pay.CALCULATEDAMOUNT5 as 'Amount',
pay.periodeused as Period,
CONVERT(VARCHAR,PAY.ENTRYCODE) + ' ' + EC.TEXT AS 'Entry Code',
case when ACC.ACCOUNTNO=pay.ACCOUNTNOOFCALCAMOUNT5 THEN pay.ACCOUNTNOOFCALCAMOUNT5 + ' - ' + acc.ACCOUNTTEXT
when acc.ACCOUNTNO=pay.ACCOUNTNOOFCALCAMOUNTCREDIT5 THEN pay.ACCOUNTNOOFCALCAMOUNTCREDIT5 + ' - ' + acc.ACCOUNTTEXT
end 'Account No',
case when ACC.ACCOUNTNO=pay.ACCOUNTNOOFCALCAMOUNT5 THEN 'DEBIT'
when acc.ACCOUNTNO=pay.ACCOUNTNOOFCALCAMOUNTCREDIT5 THEN 'CREDIT'
else 'N/A'
end 'Account type',
pay.rank as Rank,
pay.currencyforpayment as 'Payment Currency ',
p01.client numorgid,
p01.employmentstartdate,
p01.employmentenddate
from
pw001pay pay
left join pw001p01 p01 on p01.pin=pay.pin and CALCULATEDAMOUNT5<>0
left join pworg ves on ves.numorgid=pay.accountdim2
left join pyety001 ec on ec.entrycode=pay.entrycode
left join pworg persorg on persorg.numorgid=p01.client
left join PW001C117 CAT on ec.CATEGORY=CAT.SEQUENCENO and pay.ENTRYCODE=ec.ENTRYCODE
left join PY001ACC ACC on ACC.ACCOUNTNO=pay.ACCOUNTNOOFCALCAMOUNT5 or acc.ACCOUNTNO=pay.ACCOUNTNOOFCALCAMOUNTCREDIT5
where CALCULATEDAMOUNT5<>0
UNION ALL
select
pay.PIN,
p01.Name,
persorg.name as Organization,
pay.accountdim2 as VesOrgID,
isnull(ves.name, 'N/A') as 'Vessel Name',
case when pay.ACCOUNTDIM3 is NULL or pay.ACCOUNTDIM3='' then 'N/A'
else pay.ACCOUNTDIM3
end 'Department',
isnull(CAT.NAME, 'Other') as 'Category',
pay.CALCULATEDAMOUNT6 as 'Amount',
pay.periodeused as Period,
CONVERT(VARCHAR,PAY.ENTRYCODE) + ' ' + EC.TEXT AS 'Entry Code',
case when ACC.ACCOUNTNO=pay.ACCOUNTNOOFCALCAMOUNT6 THEN pay.ACCOUNTNOOFCALCAMOUNT6 + ' - ' + acc.ACCOUNTTEXT
when acc.ACCOUNTNO=pay.ACCOUNTNOOFCALCAMOUNTCREDIT6 THEN pay.ACCOUNTNOOFCALCAMOUNTCREDIT6 + ' - ' + acc.ACCOUNTTEXT
end 'Account No',
case when ACC.ACCOUNTNO=pay.ACCOUNTNOOFCALCAMOUNT6 THEN 'DEBIT'
when acc.ACCOUNTNO=pay.ACCOUNTNOOFCALCAMOUNTCREDIT6 THEN 'CREDIT'
else 'N/A'
end 'Account type',
pay.rank as Rank,
pay.currencyforpayment as 'Payment Currency ',
p01.client numorgid,
p01.employmentstartdate,
p01.employmentenddate
from
pw001pay pay
left join pw001p01 p01 on p01.pin=pay.pin and CALCULATEDAMOUNT6<>0
left join pworg ves on ves.numorgid=pay.accountdim2
left join pyety001 ec on ec.entrycode=pay.entrycode
left join pworg persorg on persorg.numorgid=p01.client
left join PW001C117 CAT on ec.CATEGORY=CAT.SEQUENCENO and pay.ENTRYCODE=ec.ENTRYCODE
left join PY001ACC ACC on ACC.ACCOUNTNO=pay.ACCOUNTNOOFCALCAMOUNT6 or acc.ACCOUNTNO=pay.ACCOUNTNOOFCALCAMOUNTCREDIT6
where CALCULATEDAMOUNT6<>0
UNION ALL
select
pay.PIN,
p01.Name,
persorg.name as Organization,
pay.accountdim2 as VesOrgID,
isnull(ves.name, 'N/A') as 'Vessel Name',
case when pay.ACCOUNTDIM3 is NULL or pay.ACCOUNTDIM3='' then 'N/A'
else pay.ACCOUNTDIM3
end 'Department',
isnull(CAT.NAME, 'Other') as 'Category',
pay.CALCULATEDAMOUNT7 as 'Amount',
pay.periodeused as Period,
CONVERT(VARCHAR,PAY.ENTRYCODE) + ' ' + EC.TEXT AS 'Entry Code',
case when ACC.ACCOUNTNO=pay.ACCOUNTNOOFCALCAMOUNT7 THEN pay.ACCOUNTNOOFCALCAMOUNT7 + ' - ' + acc.ACCOUNTTEXT
when acc.ACCOUNTNO=pay.ACCOUNTNOOFCALCAMOUNTCREDIT7 THEN pay.ACCOUNTNOOFCALCAMOUNTCREDIT7 + ' - ' + acc.ACCOUNTTEXT
end 'Account No',
case when ACC.ACCOUNTNO=pay.ACCOUNTNOOFCALCAMOUNT7 THEN 'DEBIT'
when acc.ACCOUNTNO=pay.ACCOUNTNOOFCALCAMOUNTCREDIT7 THEN 'CREDIT'
else 'N/A'
end 'Account type',
pay.rank as Rank,
pay.currencyforpayment as 'Payment Currency ',
p01.client numorgid,
p01.employmentstartdate,
p01.employmentenddate
from
pw001pay pay
left join pw001p01 p01 on p01.pin=pay.pin and CALCULATEDAMOUNT7<>0
left join pworg ves on ves.numorgid=pay.accountdim2
left join pyety001 ec on ec.entrycode=pay.entrycode
left join pworg persorg on persorg.numorgid=p01.client
left join PW001C117 CAT on ec.CATEGORY=CAT.SEQUENCENO and pay.ENTRYCODE=ec.ENTRYCODE
left join PY001ACC ACC on ACC.ACCOUNTNO=pay.ACCOUNTNOOFCALCAMOUNT7 or acc.ACCOUNTNO=pay.ACCOUNTNOOFCALCAMOUNTCREDIT7
where CALCULATEDAMOUNT7<>0
UNION ALL
select
pay.PIN,
p01.Name,
persorg.name as Organization,
pay.accountdim2 as VesOrgID,
isnull(ves.name, 'N/A') as 'Vessel Name',
case when pay.ACCOUNTDIM3 is NULL or pay.ACCOUNTDIM3='' then 'N/A'
else pay.ACCOUNTDIM3
end 'Department',
isnull(CAT.NAME, 'Other') as 'Category',
pay.CALCULATEDAMOUNT8 as 'Amount',
pay.periodeused as Period,
CONVERT(VARCHAR,PAY.ENTRYCODE) + ' ' + EC.TEXT AS 'Entry Code',
case when ACC.ACCOUNTNO=pay.ACCOUNTNOOFCALCAMOUNT8 THEN pay.ACCOUNTNOOFCALCAMOUNT8 + ' - ' + acc.ACCOUNTTEXT
when acc.ACCOUNTNO=pay.ACCOUNTNOOFCALCAMOUNTCREDIT8 THEN pay.ACCOUNTNOOFCALCAMOUNTCREDIT8 + ' - ' + acc.ACCOUNTTEXT
end 'Account No',
case when ACC.ACCOUNTNO=pay.ACCOUNTNOOFCALCAMOUNT8 THEN 'DEBIT'
when acc.ACCOUNTNO=pay.ACCOUNTNOOFCALCAMOUNTCREDIT8 THEN 'CREDIT'
else 'N/A'
end 'Account type',
pay.rank as Rank,
pay.currencyforpayment as 'Payment Currency ',
p01.client numorgid,
p01.employmentstartdate,
p01.employmentenddate
from
pw001pay pay
left join pw001p01 p01 on p01.pin=pay.pin and CALCULATEDAMOUNT8<>0
left join pworg ves on ves.numorgid=pay.accountdim2
left join pyety001 ec on ec.entrycode=pay.entrycode
left join pworg persorg on persorg.numorgid=p01.client
left join PW001C117 CAT on ec.CATEGORY=CAT.SEQUENCENO and pay.ENTRYCODE=ec.ENTRYCODE
left join PY001ACC ACC on ACC.ACCOUNTNO=pay.ACCOUNTNOOFCALCAMOUNT8 or acc.ACCOUNTNO=pay.ACCOUNTNOOFCALCAMOUNTCREDIT8
where CALCULATEDAMOUNT8<>0
UNION ALL
select
pay.PIN,
p01.Name,
persorg.name as Organization,
pay.accountdim2 as VesOrgID,
isnull(ves.name, 'N/A') as 'Vessel Name',
case when pay.ACCOUNTDIM3 is NULL or pay.ACCOUNTDIM3='' then 'N/A'
else pay.ACCOUNTDIM3
end 'Department',
isnull(CAT.NAME, 'Other') as 'Category',
pay.CALCULATEDAMOUNT9 as 'Amount',
pay.periodeused as Period,
CONVERT(VARCHAR,PAY.ENTRYCODE) + ' ' + EC.TEXT AS 'Entry Code',
case when ACC.ACCOUNTNO=pay.ACCOUNTNOOFCALCAMOUNT9 THEN pay.ACCOUNTNOOFCALCAMOUNT9 + ' - ' + acc.ACCOUNTTEXT
when acc.ACCOUNTNO=pay.ACCOUNTNOOFCALCAMOUNTCREDIT9 THEN pay.ACCOUNTNOOFCALCAMOUNTCREDIT9 + ' - ' + acc.ACCOUNTTEXT
end 'Account No',
case when ACC.ACCOUNTNO=pay.ACCOUNTNOOFCALCAMOUNT1<>0ACCOUNTNOOFCALCAMOUNT9 THEN 'DEBIT'
when acc.ACCOUNTNO=pay.ACCOUNTNOOFCALCAMOUNTCREDIT1<>0ACCOUNTNOOFCALCAMOUNTCREDIT9 THEN 'CREDIT'
else 'N/A'
end 'Account type',
pay.rank as Rank,
pay.currencyforpayment as 'Payment Currency ',
p01.client numorgid,
p01.employmentstartdate,
p01.employmentenddate
from
pw001pay pay
left join pw001p01 p01 on p01.pin=pay.pin and CALCULATEDAMOUNT1<>0
left join pworg ves on ves.numorgid=pay.accountdim2
left join pyety001 ec on ec.entrycode=pay.entrycode
left join pworg persorg on persorg.numorgid=p01.client
left join PW001C117 CAT on ec.CATEGORY=CAT.SEQUENCENO and pay.ENTRYCODE=ec.ENTRYCODE
left join PY001ACC ACC on ACC.ACCOUNTNO=pay.ACCOUNTNOOFCALCAMOUNT9 or acc.ACCOUNTNO=pay.ACCOUNTNOOFCALCAMOUNT1 or acc.ACCOUNTNO=pay.ACCOUNTNOOFCALCAMOUNTCREDIT1
where CALCULATEDAMOUNT1<>0pay.ACCOUNTNOOFCALCAMOUNTCREDIT9
where CALCULATEDAMOUNT9<>0
UNION ALL
select
pay.PIN,
p01.Name,
persorg.name as Organization,
pay.accountdim2 as VesOrgID,
isnull(ves.name, 'N/A') as 'Vessel Name',
case when pay.ACCOUNTDIM3 is NULL or pay.ACCOUNTDIM3='' then 'N/A'
else pay.ACCOUNTDIM3
end 'Department',
isnull(CAT.NAME, 'Other') as 'Category',
pay.CALCULATEDAMOUNT10 as 'Amount',
pay.periodeused as Period,
CONVERT(VARCHAR,PAY.ENTRYCODE) + ' ' + EC.TEXT AS 'Entry Code',
case when ACC.ACCOUNTNO=pay.ACCOUNTNOOFCALCAMOUNT10 THEN pay.ACCOUNTNOOFCALCAMOUNT10 + ' - ' + acc.ACCOUNTTEXT
when acc.ACCOUNTNO=pay.ACCOUNTNOOFCALCAMOUNTCREDIT10 THEN pay.ACCOUNTNOOFCALCAMOUNTCREDIT10 + ' - ' + acc.ACCOUNTTEXT
end 'Account No',
case when ACC.ACCOUNTNO=pay.ACCOUNTNOOFCALCAMOUNT10 THEN 'DEBIT'
when acc.ACCOUNTNO=pay.ACCOUNTNOOFCALCAMOUNTCREDIT10 THEN 'CREDIT'
else 'N/A'
end 'Account type',
pay.rank as Rank,
pay.currencyforpayment as 'Payment Currency ',
p01.client numorgid,
p01.employmentstartdate,
p01.employmentenddate
from
pw001pay pay
left join pw001p01 p01 on p01.pin=pay.pin and CALCULATEDAMOUNT1<>0
left join pworg ves on ves.numorgid=pay.accountdim2
left join pyety001 ec on ec.entrycode=pay.entrycode
left join pworg persorg on persorg.numorgid=p01.client
left join PW001C117 CAT on ec.CATEGORY=CAT.SEQUENCENO and pay.ENTRYCODE=ec.ENTRYCODE
left join PY001ACC ACC on ACC.ACCOUNTNO=pay.ACCOUNTNOOFCALCAMOUNT10 or acc.ACCOUNTNO=pay.ACCOUNTNOOFCALCAMOUNTCREDIT10
where CALCULATEDAMOUNT10<>0
UNION ALL
select
pay.PIN,
p01.Name,
persorg.name as Organization,
pay.accountdim2 as VesOrgID,
isnull(ves.name, 'N/A') as 'Vessel Name',
case when pay.ACCOUNTDIM3 is NULL or pay.ACCOUNTDIM3='' then 'N/A'
else pay.ACCOUNTDIM3
end 'Department',
isnull(CAT.NAME, 'Other') as 'Category',
pay.CALCULATEDAMOUNT11 as 'Amount',
pay.periodeused as Period,
CONVERT(VARCHAR,PAY.ENTRYCODE) + ' ' + EC.TEXT AS 'Entry Code',
case when ACC.ACCOUNTNO=pay.ACCOUNTNOOFCALCAMOUNT11 THEN pay.ACCOUNTNOOFCALCAMOUNT11 + ' - ' + acc.ACCOUNTTEXT
when acc.ACCOUNTNO=pay.ACCOUNTNOOFCALCAMOUNTCREDIT11 THEN pay.ACCOUNTNOOFCALCAMOUNTCREDIT11 + ' - ' + acc.ACCOUNTTEXT
end 'Account No',
case when ACC.ACCOUNTNO=pay.ACCOUNTNOOFCALCAMOUNT11 THEN 'DEBIT'
when acc.ACCOUNTNO=pay.ACCOUNTNOOFCALCAMOUNTCREDIT11 THEN 'CREDIT'
else 'N/A'
end 'Account type',
pay.rank as Rank,
pay.currencyforpayment as 'Payment Currency ',
p01.client numorgid,
p01.employmentstartdate,
p01.employmentenddate
from
pw001pay pay
left join pw001p01 p01 on p01.pin=pay.pin and CALCULATEDAMOUNT1<>0
left join pworg ves on ves.numorgid=pay.accountdim2
left join pyety001 ec on ec.entrycode=pay.entrycode
left join pworg persorg on persorg.numorgid=p01.client
left join PW001C117 CAT on ec.CATEGORY=CAT.SEQUENCENO and pay.ENTRYCODE=ec.ENTRYCODE
left join PY001ACC ACC on ACC.ACCOUNTNO=pay.ACCOUNTNOOFCALCAMOUNT11 or acc.ACCOUNTNO=pay.ACCOUNTNOOFCALCAMOUNTCREDIT11
where CALCULATEDAMOUNT11<>0 |