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