Summary	The view is used for accounting purposes. It gives an overview of the payments for historical periods split by accounts debit and credit.
Keywords	Payroll, Accounting
Category	Analytical View

Description

The view is used for accounting purposes. It gives an overview of the payments for historical periods split by accounts debit and credit.

Main Data Selection

All the crew members have some payments in history. Only calculatedamount1 is considered by this view.

Setup and Configuration

SQL statement

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 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 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.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

Fields Definition

View Configuration (Column/Raw/Data Fields)

Rows/ Columns Specification

Row/Column	Description/ Location in APM
PIN	The PIN from the Personal Details.
Name	The Full Name from the Personal Details.
Organization	The Employment Organization from the Personal Details.
VesOrgID	The ID of the vessel linked to the payment entry in Account Dim 2.
Vessel Name	The Name of the vessel linked to the payment entry Account Dim 2.
Department	The Code of the department linked to the payment entry Account Dim 3.
Category	The category of the payment entry.
Amount	The Amount (Entry Field 1) value of the payment entry.
Period	The Period of the payment entry.
Entry Code	The Code of payment entry.
Account No	The account number of calculatedamount1 on the payment entry.
Account Type	The account type of calculatedamount1 on the - either debit or credit.
Rank	The Name of the rank linked to the payment entry.
Payment Currency	The payment currency of the payment entry.
numorgid	The emplyment organization ID from the Personal Details.
employmentstratdate	The emplyment start date from the Personal Details.
employmentenddate	The emplyment end date from the Personal Details.

Other

N/A

Accounting Summary (historical period)