Enabling trace in PL/SQL block for HR APIs

Sometimes for debugging conversion scripts for HRMS enabling trace could be useful for debugging purpose… The following code could be used:

 

exec  APPS.HR_UTILITY.TRACE_ON;
exec APPS.HR_UTILITY.SET_TRACE_OPTIONS(UPPER(‘TRACE_DEST:DBMS_OUTPUT’));

 

Before this DBMS_OUTPUT needs to be enabled.

Advertisements

Escalation mechanism in Oracle workflow

For setting up escalation mechanism in workflow the following method could be followed.

Scenario: Notification goes for Approval to an approver. If the notification is not approved/rejected for 3 days, the notification is sent to the next level of approver.

1

SR reminder Approval Notification is the Notification sent for approval. Escalate if the function that handles the time out / escalation mechanism.

2

3

Following is the code for it.

PROCEDURE ESCALATE(ITEMTYPE  IN VARCHAR2,
ITEMKEY   IN VARCHAR2,
ACTID     IN NUMBER,
FUNCMODE  IN VARCHAR2,
RESULTOUT OUT VARCHAR2) is

p_shipment_header_id NUMBER;
l_reminder_count NUMBER;
p_req_header_id NUMBER;
p_approver_id NUMBER;
p_approver_name varchar2(50);

BEGIN

p_shipment_header_id := wf_engINe.getitemattrNUMBER(itemtype => itemtype,
itemkey  => itemkey,
aname    => ‘SHIPMENT_HEADER_ID’);

l_reminder_count := wf_engine.getitemattrNUMBER(itemtype => itemtype,
itemkey  => itemkey,
aname    => ‘REMINDER_COUNT’);

l_reminder_count := l_reminder_count + 1;

wf_engine.setitemattrnumber(ITEMTYPE,
ITEMKEY,
‘REMINDER_COUNT’,
l_reminder_count);

if l_reminder_count > 3 then

SELECT prl.requisition_header_id
INTO p_req_header_id
FROM rcv_transactions_interface rti,
po_distributions_all pod,
PO_REQ_DISTRIBUTIONS_ALL prd,
po_requisition_lines_all prl
WHERE rti.po_distribution_id = pod.po_distribution_id
AND pod.req_distribution_id  = prd.distribution_id
AND prd.REQUISITION_LINE_ID  = prl.REQUISITION_LINE_ID
AND rti.shipment_header_id   = p_shipment_header_id
AND rownum                   =1;

p_approver_id := MPL_PR_DH_GROUP_PKG.MPL_PR_DH_GROUP_APPROVER (p_req_header_id);

SELECT user_name INTO p_approver_name
from fnd_user where employee_id = p_approver_id;

wf_engine.setitemattrtext(ITEMTYPE,
ITEMKEY,
‘ESCALATION_ROLE’,
p_approver_name);

resultout := wf_engINe.eng_completed || ‘:’ || ‘Y’;

else
resultout := wf_engINe.eng_completed || ‘:’ || ‘N’;
END IF;

END ESCALATE;