/
Check Request Crew List View
Check Request Crew List View
- Helen Ustych
- Daria Chebotaieva
Owned by Helen Ustych, created
1 Description | 2 Selection | 3 SQL Statment
Summary | This view shows the crew members that have unpaid Office Check payment entry codes in the current Payroll period. |
|---|---|
Keywords | Check Request, Office Check |
Description
This view shows the crew members that have unpaid Office Check payment entry codes in the current Payroll period.
Selection
The view shows the crew members that have EC 502 Garnishment, EC 504 Allotment Office Check, EC 793 Advance Office check, EC 798 Sign Off Office check in the current Payroll period and that have not been processed and locked.
SQL Statment
CREATE VIEW
dbo.PW001SRV411 as
SELECT
P.PIN,
P.NAME,
p03.RANK,
P.CLIENT NUMORGID,
P.EMPLOYMENTSTARTDATE,
P.EMPLOYMENTENDDATE ,
'xxx-xx-' + RIGHT(p0y.SSN, 4) AS SSN,
ves.name [Vessel],
PYH.CALCULATEDAMOUNT1 AS AMOUNT,
case when pyh.entrycode in (502, 504) THEN 'Allotment'
when pyh.entrycode in (793) then 'Advance'
when pyh.entrycode in (798) then 'Sign Off'
End
as Type,
PYH.entryfield5 AS 'PAYMENT DATE',
pai.PAYNAME [Recipient Name],
per.periodname as [Voyage]
from pw001pay pyh
left join pw001p01 p on p.pin=pyh.pin
left join pw001p03 p03 on p03.SEQUENCENO=pyh.ACTIVITYSEQ
LEFT JOIN pyety001 ec
ON ec.entrycode = pyh.entrycode
left join PWORG AS VES ON pyh.ACCOUNTDIM2 = VES.NUMORGID
left join pw001p0y p0y on p0y.pin=pyh.pin
left join pw001pai pai on pai.seqno=pyh.seqno
left join PWCMPPAY per
on per.companyid=pyh.accountdim2
and per.period=pyh.periodeused
where pyh.ENTRYCODE in (502, 504, 793, 798) and pyh.CALCULATEDAMOUNT1<>0 and (pyh.paid is NULL or pyh.paid <>'Y')