Capture Approval Comments#
This custom function can be called from a workflow to update a "Char" variable with the lastet "Comment" found on the approval detail record assosicated to the primary ID of the table in which theworkflow is being called.Usercalc Example: Send a notification to the employee stating the reason their leave request has been denied
The following UserCalc was written to capture the comments from the approval record if the LEAVE_APPROVAL is changed from "01-Awaiting Approval" to "99-Declined". The workflow product will determine the type of approval process the system will search for when triggered.
This is a 'Calculation' type UserCalc and will return the value of the wage rate plus premium rate to the appropriate benefit component.
LINE | CMD | TYPE 1 | OPERAND 1 | OPER | TYPE 2 | OPERAND 2 | TYPE 3 | OPERAND 3 | IF GO TO | ELSE GO TO | NOTES | |
---|---|---|---|---|---|---|---|---|---|---|---|---|
100 | IF | CF | UPDATING | EQ | B | TRUE | 200 | 99999 | ||||
200 | IF | NV | AAL.LEAVE_APPROVAL | NE | OV | AAL.LEAVE_APPROVAL | 300 | 99999 | ||||
300 | IF | NV | AAL.LEAVE_APPROVAL | EQ | A | 99 | 400 | 99999 | ||||
400 | LET | V | DAPR_COMMENT | EQ | CF | P2K_CF_GET_DAPR_COMMENT | 1000 | |||||
1000 | ACT | AC | EE_LV_DECLINE | LOG | $S | AS-OF-DATE | RT | Employee | 99999 | See IMWA Message Section | ||
99999 | EXIT |
create or replace FUNCTION "P2K_CF_GET_DAPR_COMMENT" RETURN VARCHAR2 AS CURSOR c_dapr1(pc_approval_type VARCHAR2, pc_reference_id NUMBER) IS SELECT dapr.comments FROM p2k_cm_approval_records dapr, p2k_cm_approval_processes dapp WHERE dapr.dapp_id = dapp.id AND (dapp.approval_type = pc_approval_type OR (pc_approval_type = 'BT' AND dapp.approval_type IN('02','03'))) AND dapr.reference_id = pc_reference_id AND dapr.approval_status NOT IN('01','20','80','95') --don't pick up Approval Cancelled, Future Approval, Approval Historic, or Declined Historic ORDER BY dapr.change_date DESC ; v_approval_type VARCHAR2(2); v_reference_id NUMBER; v_comments VARCHAR2(4000); BEGIN P2K_PMWRKFLW.SMERL('In P2K_CF_GET_DAPR_COMMENT - WF Table Alias: ' || p2k_pmwrkflw.ktable_alias); IF p2k_pmwrkflw.ktable_alias = 'PBT' THEN v_approval_type := 'BT'; v_reference_id := p2k_pmwrkflw.m_new_PBT.id; ELSIF p2k_pmwrkflw.ktable_alias = 'SRV' THEN v_approval_type := '04'; v_reference_id := p2k_pmwrkflw.m_new_SRV.id; ELSIF p2k_pmwrkflw.ktable_alias = 'SPA' THEN v_approval_type := '05'; v_reference_id := p2k_pmwrkflw.m_new_SPA.id; ELSIF p2k_pmwrkflw.ktable_alias = 'XXX' THEN v_approval_type := '06'; -- v_reference_id := p2k_pmwrkflw.m_new_XXX.id; ELSIF p2k_pmwrkflw.ktable_alias = 'RPO' THEN v_approval_type := '07'; v_reference_id := p2k_pmwrkflw.m_new_RPO.id; ELSIF p2k_pmwrkflw.ktable_alias = 'RAP' THEN v_approval_type := '08'; v_reference_id := p2k_pmwrkflw.m_new_RAP.id; ELSIF p2k_pmwrkflw.ktable_alias = 'CDP' THEN v_approval_type := '09'; -- v_reference_id := p2k_pmwrkflw.m_new_CDP.id; ELSIF p2k_pmwrkflw.ktable_alias = 'CDA' THEN v_approval_type := '10'; -- v_reference_id := p2k_pmwrkflw.m_new_CDA.id; ELSIF p2k_pmwrkflw.ktable_alias = 'CRS' THEN v_approval_type := '11'; -- v_reference_id := p2k_pmwrkflw.m_new_CRS.id; ELSIF p2k_pmwrkflw.ktable_alias = 'CRSC' THEN v_approval_type := '12'; -- v_reference_id := p2k_pmwrkflw.m_new_CRSC.id; ELSIF p2k_pmwrkflw.ktable_alias = 'CTR' THEN v_approval_type := '13'; -- v_reference_id := p2k_pmwrkflw.m_new_CTR.id; ELSIF p2k_pmwrkflw.ktable_alias = 'BEL' THEN v_approval_type := '14'; v_reference_id := p2k_pmwrkflw.m_new_BEL.id; ELSIF p2k_pmwrkflw.ktable_alias = 'RAS' THEN v_approval_type := '15'; -- v_reference_id := p2k_pmwrkflw.m_new_RAS.id; ELSIF p2k_pmwrkflw.ktable_alias = 'CRSE' THEN v_approval_type := '16'; v_reference_id := p2k_pmwrkflw.m_new_CRSE.id; ELSIF p2k_pmwrkflw.ktable_alias = 'PTS' THEN v_approval_type := '17'; v_reference_id := p2k_pmwrkflw.m_new_PTS.id; ELSIF p2k_pmwrkflw.ktable_alias = 'YYY' THEN v_approval_type := '18'; -- v_reference_id := p2k_pmwrkflw.m_new_YYY.id; ELSIF p2k_pmwrkflw.ktable_alias = 'AAL' THEN v_approval_type := '19'; v_reference_id := p2k_pmwrkflw.m_new_AAL.id; ELSIF p2k_pmwrkflw.ktable_alias = 'TCE' THEN v_approval_type := '20'; v_reference_id := p2k_pmwrkflw.m_new_TCE.id; ELSIF p2k_pmwrkflw.ktable_alias = 'RED' THEN v_approval_type := '21'; -- v_reference_id := p2k_pmwrkflw.m_new_RED.id; ELSIF p2k_pmwrkflw.ktable_alias = 'REF' THEN v_approval_type := '22'; -- v_reference_id := p2k_pmwrkflw.m_new_REF.id; ELSIF p2k_pmwrkflw.ktable_alias = 'RWH' THEN v_approval_type := '23'; -- v_reference_id := p2k_pmwrkflw.m_new_RWH.id; ELSIF p2k_pmwrkflw.ktable_alias = 'BOEE' THEN v_approval_type := '24'; v_reference_id := p2k_pmwrkflw.m_new_BOEE.id; ELSIF p2k_pmwrkflw.ktable_alias = 'DPS' THEN v_approval_type := '25'; -- v_reference_id := p2k_pmwrkflw.m_new_DPS.id; ELSIF p2k_pmwrkflw.ktable_alias = 'PTSE' THEN v_approval_type := '26'; -- v_reference_id := p2k_pmwrkflw.m_new_PTSE.id; END IF; P2K_PMWRKFLW.SMERL('In P2K_CF_GET_DAPR_COMMENT - Approval Type: ' || v_approval_type || ' / Reference Id: ' || v_reference_id); IF v_approval_type IS NOT NULL THEN OPEN c_dapr1(v_approval_type,v_reference_id); FETCH c_dapr1 INTO v_comments; CLOSE c_dapr1; END IF; RETURN v_comments; END P2K_CF_GET_DAPR_COMMENT;