Page Comparison

...
Expand
title	SQL 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.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 ACCOUNTNOOFCALCAMOUNT1 <> '' THEN 'DEBIT'
            WHEN 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 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, ELSE                     '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 '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)

...
Version	Old Version 4	New Version Current
Changes made by	Daria Chebotaieva	Daria Chebotaieva
Saved on	Feb 06, 2024	Nov 13, 2024
Versions Compared

Key

Fields Definition

View Configuration (Column/Raw/Data Fields)
