...
Name
...
| Table of Contents | ||||||||
|---|---|---|---|---|---|---|---|---|
|
| Page Properties | ||||
|---|---|---|---|---|
|
Description
The view shows the crew members that have unpaid Direct Deposit (ACH) payment Entry codes in the current Payroll period.
...
The view shows the crew with the EC 505 - Allotment ACH, EC 794 - Advances ACH, EC 799 - Sign Off ACH in the current Payroll payroll period that have not been processed and locked.
Field Specification
| Code Block |
|---|
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' |