Direct Deposit Request
- Helen Ustych
- Daria Chebotaieva
Owned by Helen Ustych, created
1 Description | 2 Selection | 3 Field Specification
Summary | The view shows the crew memebers that have unpaid Direct Deposit (ACH) payment Entry codes in the current Payroll period. |
|---|---|
Keywords | Direct Deposit, ACH |
Description
The view shows the crew members that have unpaid Direct Deposit (ACH) payment Entry codes in the current Payroll period.
Selection
The view shows the crew with the 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'