Description

The view shows the crew that have unpaid Direct Deposit (ACH) payment Entry codes in the current Payroll period: EC 505 Allotment ACH, EC 794 Advances ACH, EC 799 Sign Off ACH

Selection

CREATE VIEW dbo.PW001SRV412 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],
CONVERT(MONEY, ROUND(PYH.CALCULATEDAMOUNT1, 2), 0) as Amount,
case when pyh.entrycode in (505) THEN 'Allotment'
when pyh.entrycode in (794) then 'Advance'
when pyh.entrycode in (799) then 'Sign Off'
End
as Type,
PYH.entryfield5 AS 'Payment Date',
pai.PAYNAME [Recipient Name],
pai.RCODERECEIVER RoutingNo,
pai.BANKACCOUNTTYPE [Acc Type],
pai.ACCOUNTNO,
per.periodname as [Voyage]
from pw001pay pyh
left join pw001p01 p on p.pin=pyh.pin
join (select
SEQUENCENO,
PIN,
RANK,
DATEFROM,
DATETO,
TODATEESTIMATED,
PLANNED from PW001P03)
p03 on p03.SEQUENCENO=pyh.ACTIVITYSEQ
or (pyh.ACTIVITYSEQ is null and p03.PIN = pyh.PIN
and pyh.entryfield5 between p03.DATEFROM and isnull(p03.DATETO,p03.TODATEESTIMATED)
)
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 (505, 794, 799) and pyh.CALCULATEDAMOUNT1<>0 and Isnull(pyh.PAID,'N') != 'Y'