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. A workflow must be set up on IMWA for the email to be sent (example - EE_LV_DECLINE). A "Char" variable should be set up on the usercalc (example - DAPR_COMMENT).
| 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 Sample IMWA Message Section | ||
| 99999 | EXIT |
Sample IMWA message#
This is a sample of the message that should be added in the usercalc via the workflow action (ACT). This message should be found in the "NOTES" section in the usercalc
<<EID.FIRST_NAME>> <<EID.LAST_NAME>>,
Your leave request for Vacation starting <<AAL.START_DATE>> has been declined.
Reason: <<DAPR_COMMENT>>Function Logic#
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;
