Versions Compared

Version Old Version 4 New Version 5
Changes made by

Daria Chebotaieva

Arvin John Salandanan

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Expand
titleSQL statement
Code Block
selectSELECT 
	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 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 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 'AS 'Vessel Name',
       CASE 
            WHEN pyh.ACCOUNTDIM3 IS NULL OR pyh.ACCOUNTDIM3 = '' THEN '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 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 CodeCategory',
	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'calculatedamount1 'Amount',
       pyh.periodeused       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,AS Period,
       CONVERT(VARCHAR, pyh.ENTRYCODE) + ' ' + EC.TEXT AS 'Entry Code',
	case when ACC.ACCOUNTNO=pyh.ACCOUNTNOOFCALCAMOUNT4 THEN pyh.ACCOUNTNOOFCALCAMOUNT4 + ' -CASE '
+ 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 WHEN pyh.ACCOUNTNOOFCALCAMOUNT1 <> '' THEN pyh.ACCOUNTNOOFCALCAMOUNT1 + ' - ' + acc.ACCOUNTTEXT
            WHEN pyh.ACCOUNTNOOFCALCAMOUNTCREDIT1 <> '' THEN pyh.ACCOUNTNOOFCALCAMOUNTCREDIT1 + ' - ' + acc.ACCOUNTTEXT
		 when acc.ACCOUNTNO=pyh.ACCOUNTNOOFCALCAMOUNTCREDIT5 THEN pyh.ACCOUNTNOOFCALCAMOUNTCREDIT5 + ' - ' + acc.ACCOUNTTEXT
    end END 'Account No',
	case when ACC.ACCOUNTNO=pyh.ACCOUNTNOOFCALCAMOUNT5 THEN 'DEBIT' 		 when acc.ACCOUNTNO=pyh.ACCOUNTNOOFCALCAMOUNTCREDIT5 THEN 'CREDIT'
		 else 'N/A' CASE 
        end 'Account type', 	pyh.rank asWHEN Rank,
	pyh.currencyforpaymentACCOUNTNOOFCALCAMOUNT1 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 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 '<> '0' THEN 'DEBIT'
            WHEN pyh.ACCOUNTNOOFCALCAMOUNTCREDIT1 <> '0' 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.ACCOUNTNOOFCALCAMOUNTCREDIT8 + ' - ' + 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,
	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.ACCOUNTNOOFCALCAMOUNTCREDIT9 + ' - ' + acc.ACCOUNTTEXT
    end 'Account No',
	case when ACC.ACCOUNTNO=pyh.ACCOUNTNOOFCALCAMOUNT9 THEN 'DEBIT'
		 when acc.ACCOUNTNO=pyh.ACCOUNTNOOFCALCAMOUNTCREDIT9 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.ACCOUNTNOOFCALCAMOUNT9 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.client                    numorgid,
       p01.employmentstartdate,
       p01.employmentenddate
FROM   pw001pyh pyh
       LEFT JOIN pw001p01 p01
            ON  p01.pin = pyh.pin
       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

Fields Definition

...

View Configuration (Column/Raw/Data Fields)

...