...

The view shows the crew that have unpaid Direct Deposit (ACH) payment Entry codes in the current Payroll period: .

Selection

The view shows the crew with EC 505 Allotment ACH, EC 794 Advances ACH, EC 799 Sign Off ACH

...

in the current Payroll period that have not been processed and locked.

Field Specification

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'