selectSELECT
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 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 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 acc.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 AS 'Vessel Name',
CASE
WHEN pay.ACCOUNTDIM3 IS NULL OR pay.ACCOUNTDIM3 = '' THEN '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',
isnullELSE 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 CodeCategory',
case when ACC.ACCOUNTNO=pay.ACCOUNTNOOFCALCAMOUNT3 THEN pay.ACCOUNTNOOFCALCAMOUNT3 + ' - ' + acc.ACCOUNTTEXT when acc.ACCOUNTNO=pay.ACCOUNTNOOFCALCAMOUNTCREDIT3CALCULATEDAMOUNT1 THEN pay.ACCOUNTNOOFCALCAMOUNTCREDIT3 + ' - ' + acc.ACCOUNTTEXT
end 'Account No AS 'Amount',
case when ACC.ACCOUNTNO=pay.ACCOUNTNOOFCALCAMOUNT3 THEN 'DEBIT' when acc.ACCOUNTNO=pay.ACCOUNTNOOFCALCAMOUNTCREDIT3 THEN 'CREDIT'
else 'N/A'periodeused 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,AS Period,
CONVERT(VARCHAR, PAY.ENTRYCODE) + ' ' + EC.TEXT AS 'Entry Code',
case when ACC.ACCOUNTNO=pay.ACCOUNTNOOFCALCAMOUNT4 THEN pay.ACCOUNTNOOFCALCAMOUNT4 + ' -CASE '
+ 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 WHEN pay.ACCOUNTNOOFCALCAMOUNT1 <> '' THEN pay.ACCOUNTNOOFCALCAMOUNT1 + ' - ' + acc.ACCOUNTTEXT
WHEN pay.ACCOUNTNOOFCALCAMOUNTCREDIT1 <> '' THEN pay.ACCOUNTNOOFCALCAMOUNTCREDIT1 + ' - ' + acc.ACCOUNTTEXT
when acc.ACCOUNTNO=pay.ACCOUNTNOOFCALCAMOUNTCREDIT5 THEN pay.ACCOUNTNOOFCALCAMOUNTCREDIT5 + ' - ' + acc.ACCOUNTTEXT
end END 'Account No',
case when ACC.ACCOUNTNO=pay.ACCOUNTNOOFCALCAMOUNT5 THEN 'DEBIT' when acc.ACCOUNTNO=pay.ACCOUNTNOOFCALCAMOUNTCREDIT5 THEN 'CREDIT'
else 'N/A' CASE
end 'Account type', pay.rank as Rank,WHEN pay.currencyforpaymentACCOUNTNOOFCALCAMOUNT1 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.ACCOUNTTEXT0' THEN 'DEBIT'
end 'Account No',
case when ACC.ACCOUNTNO=WHEN pay.ACCOUNTNOOFCALCAMOUNT6ACCOUNTNOOFCALCAMOUNTCREDIT1 THEN<> 'DEBIT0'
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, ELSE '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 '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.pinclient 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<>0numorgid,
UNION ALL
select pay.PIN, p01.Nameemploymentstartdate,
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.ACCOUNTNOOFCALCAMOUNT9 THEN 'DEBIT'
when acc.ACCOUNTNO=pay.ACCOUNTNOOFCALCAMOUNTCREDIT9 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.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 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 |