Travel Expenses waiting for Approval - per total count (multiple level)
- Daria Chebotaieva
Summary | The crew list view shows the list of approvers/ coordinators that have some travel expenses waiting their approval. |
|---|---|
Keywords | Travel Expense, Approver |
Description
The crew list view shows the list of approvers/ coordinators that have some travel expenses waiting for their approval and states the number of documents waiting for approval.
The view works only with the multiple-level chain setup with any level set up (Rank/ Office, PIN/ Name, or Position/ Vessel)
Selection
All approvers that have some documents waiting for approval with an active account in the Employee Portal.
SQL Statement
CREATE VIEW dbo.PW001SRV56
AS
SELECT
P01.PIN,
P01.CLIENT NUMORGID,
P01.EMPLOYMENTSTARTDATE,
P01.EMPLOYMENTENDDATE,
P01.[NAME] AS MANAGER_NAME,
P01.[RANK] AS MANAGER_RANK,
main.DOCUMENTS_TO_APPROVE
FROM PW001P01 P01
RIGHT JOIN (
SELECT (
CASE
WHEN coord.PIN IS NOT NULL THEN coord.PIN
WHEN appr.PIN IS NOT NULL THEN appr.PIN
ELSE ''
END
) PIN,
COUNT(wf.DOCUMENT_ID) AS DOCUMENTS_TO_APPROVE
FROM WEB_CP_DOCUMENTS doc
LEFT JOIN WEB_CP_TRAVEL_BILLS tb
ON doc.DOCUMENT_ID = tb.DOCUMENT_ID
LEFT JOIN PWORG docves
ON tb.VESSEL = docves.NUMORGID
LEFT JOIN WEB_CP_WORKFLOW wf
ON doc.DOCUMENT_ID = wf.DOCUMENT_ID
LEFT JOIN WEB_CP_COA2_NODE_DETAILS nd
ON wf.NEXT_NODE = nd.NODE_SEQUENCENO
LEFT JOIN WEB_CP_COA_APPROVER_VESSELS av
ON av.NODE_SEQUENCENO = nd.SEQUENCENO
LEFT JOIN PWORG apprves
ON av.VESSEL_NUMORGID = apprves.NUMORGID
LEFT JOIN PW001P01 coord
ON coord.PIN = nd.PIN
LEFT JOIN PW001P01 appr
ON appr.rank = nd.rank
RIGHT JOIN WEB_CP_ACCOUNT_EMPLOYEE acc on acc.pin = appr.pin
LEFT JOIN PW001P0T email
ON email.PIN IN (coord.PIN, appr.PIN)
AND email.TELETYPE = 6
AND email.TELEPRIORITY IS NOT NULL
AND NOT EXISTS (
SELECT 1
FROM PW001P0T t
WHERE t.PIN = email.PIN
AND t.TELETYPE = email.TELETYPE
AND t.TELEPRIORITY IS NOT NULL
AND (
t.TELEPRIORITY < email.TELEPRIORITY
OR t.TELEPRIORITY = email.TELEPRIORITY
AND t.SEQUENCENO < email.SEQUENCENO
)
)
WHERE wf.step = (
SELECT MAX(t.STEP)
FROM WEB_CP_WORKFLOW t
WHERE t.DOCUMENT_ID = wf.DOCUMENT_ID
GROUP BY
t.DOCUMENT_ID
)
AND wf.state = 1
AND tb.VESSEL = av.VESSEL_NUMORGID
GROUP BY
coord.NAME,
appr.NAME,
wf.ORIGINATOR_PIN,
coord.rank,
appr.rank,
coord.PIN,
appr.PIN,
email.TELENO,
coord.CLIENT,
appr.CLIENT,
coord.EMPLOYMENTSTARTDATE,
appr.EMPLOYMENTSTARTDATE,
coord.EMPLOYMENTENDDATE,
appr.EMPLOYMENTENDDATE
) main
ON main.PIN = p01.PINField Specification
PIN - ID of the approver/ coordinator.
MANAGER_NAME - full name of the approver/ coordinator.
MANAGER_RANK - rank of the approver/ coordinator.
DOCUMENTS_TO_APPROVE - count of the documents waiting for approval;