Wiki: P 2 K_CF_GET_DAPR

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;

Notes #

Click to create a new notes page