Workflow Builder ” Error Accessing the System Registry”.

Unable to launch Workflow Builder. Gives the following error : ” Error Accessing the System Registry”.

Solution : Right click on the icon and “Run as Administrator”.

This resolves the issue.

Advertisements

Unable To View workflow Status Diagram

Unable To View workflow Status Diagram

When pressed on the click Here link the below error message displays:
oracle.apps.fnd.framework.OAException: java.lang.NullPointerException ( and it displays lot of lines with the exception message)

Solution:

Set the profile option Server Timezone at Site level (should be set to the value of database timezone) and Bounce the Apache Server

Add PQH Roles and add users to it

Complete procedure to add PQH Roles and add users to it

PROCEDURE mpl_cse_create_roles(
errbuf OUT NOCOPY  VARCHAR2,
RETCODE OUT NOCOPY VARCHAR2)
IS
/*************************************************************************************
**  Name       : MPL_CSE_CREATE_ROLES
**  DESCRIPTION: This procedure used to define roles for AME
**************************************************************************************/
l_num_role_id      pqh_roles.role_id%TYPE;
l_num_object_version_number   pqh_roles.object_version_number%type;
l_num_person_extra_info_id   per_people_extra_info.person_extra_info_id%type;
l_num_ovn_pie      per_people_extra_info.object_version_number%type;
l_num_person_id      per_people_extra_info.person_id%type;
l_num_loop_count        NUMBER;
l_var_user              VARCHAR2(1000);
l_var_err               VARCHAR2(1000);
l_num_addusrrole_id     NUMBER;
l_var_process           VARCHAR2(10);
CURSOR cur_role
IS
SELECT *
FROM mpl_cse_role_conv_stg
WHERE process_flag IS NULL;
BEGIN
l_num_object_version_number := NULL;
l_num_role_id               := NULL;
l_num_person_id             := NULL;
l_num_person_extra_info_id  := NULL;
l_num_ovn_pie               := NULL;
FOR cur_role_rec IN cur_role
LOOP
BEGIN
pqh_roles_api.create_role
( p_validate          => FALSE ,
p_role_id           => l_num_role_id ,
p_role_name         => cur_role_rec.role_name ,
p_business_group_id   => 0 ,
p_role_type_cd    => ‘EMP’ ,
p_enable_flag    => ‘Y’ ,
p_object_version_number  => l_num_object_version_number ,
p_effective_date    => SYSDATE ,
p_information_category  => cur_role_rec.location
);
COMMIT;
UPDATE mpl_cse_role_conv_stg
SET process_flag = ‘S’
WHERE role_name  = cur_role_rec.role_name;
EXCEPTION
WHEN OTHERS THEN
l_var_err     := SQLERRM;
l_var_process :=’N’;
UPDATE mpl_cse_role_conv_stg
SET process_flag = ‘E’,
error_message  = ‘Role Could not be  created ‘
|| l_var_err
WHERE role_name = cur_role_rec.role_name;
FND_FILE.PUT_LINE(FND_FILE.LOG,’Role Could not be  created’);
END;
/*adding users to role*******/
/*checking number of user in a role*/
BEGIN
SELECT DECODE(cur_role_rec.member_1, NULL, 0, 1) + DECODE(cur_role_rec.member_2, NULL, 0, 1) + DECODE(cur_role_rec.member_3, NULL, 0, 1)
INTO l_num_loop_count
FROM dual;
EXCEPTION
WHEN OTHERS THEN
l_num_loop_count := 0;
l_var_process    :=’N’;
END;
IF cur_role_rec.member_1 IS NOT NULL OR cur_role_rec.member_2 IS NOT NULL OR cur_role_rec.member_3 IS NOT NULL THEN
FOR i IN 1..l_num_loop_count
LOOP
l_var_user          := NULL;
l_num_person_id     := NULL;
l_num_addusrrole_id := NULL;
l_var_process       := NULL;
IF i                 =1 THEN
l_var_user        := cur_role_rec.member_1_emp_num;
ELSIF i              =2 THEN
l_var_user        := cur_role_rec.member_2_emp_num;
ELSIF i              =3 THEN
l_var_user        := cur_role_rec.member_3_emp_num;
END IF;
BEGIN
SELECT person_id
INTO l_num_person_id
FROM per_all_people_f
WHERE employee_number =l_var_user
AND SYSDATE BETWEEN effective_start_date AND effective_end_date;
EXCEPTION
WHEN OTHERS THEN
l_var_process :=’N’;
l_var_user    := NULL;
dbms_output.put_line(‘person_id:’||SQLERRM);
UPDATE mpl_cse_role_conv_stg
SET process_flag = ‘E’,
error_message  = error_message
||’ User could not be added to Role. Employee Number: ‘
||cur_role_rec.member_1_emp_num
WHERE role_name = cur_role_rec.role_name;
END;
BEGIN
SELECT role_id
INTO l_num_addusrrole_id
FROM pqh_roles
WHERE role_name = cur_role_rec.role_name;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(‘role_id:’||SQLERRM);
l_var_process       :=’N’;
l_num_addusrrole_id := NULL;
UPDATE mpl_cse_role_conv_stg
SET process_flag = ‘E’,
error_message  = error_message
||’ Role not valid’
WHERE role_name = cur_role_rec.role_name;
END;
IF NVL(l_var_process,’Y’) !=’N’ THEN
BEGIN
hr_person_extra_info_api.create_person_extra_info
( p_validate       => FALSE ,
p_person_id      => l_num_person_id ,
p_information_type     => ‘PQH_ROLE_USERS’ ,
p_pei_information_category   => ‘PQH_ROLE_USERS’ ,
p_pei_information3     => l_num_addusrrole_id ,
p_pei_information4     => ‘N’ ,
p_pei_information5     => ‘Y’ ,
p_pei_information9     => ‘N’ ,
p_person_extra_info_id    =>l_num_person_extra_info_id ,
p_object_version_number   =>l_num_ovn_pie
);
IF l_num_person_extra_info_id IS NOT NULL THEN
UPDATE mpl_cse_role_conv_stg
SET process_flag = ‘S’
WHERE role_name  = cur_role_rec.role_name;
ELSE
DBMS_OUTPUT.PUT_LINE(‘USER NOT LOADED :’||l_num_person_id);
UPDATE mpl_cse_role_conv_stg
SET process_flag = ‘E’,
error_message  = error_message
|| ‘Seeded api ERROR hr_person_extra_info_api.create_person_extra_info ‘
WHERE role_name = cur_role_rec.role_name;
END IF;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line(fnd_file.log,’user Could not be  be added to role’);
dbms_output.put_line(‘user Could not be  be added to role’||SQLERRM);
l_var_err := SUBSTR(SQLERRM,1000);
UPDATE mpl_cse_role_conv_stg
SET process_flag = ‘E’,
error_message  = error_message
|| ‘user could not be adde for perosn_id’
||l_num_person_id
WHERE role_name = cur_role_rec.role_name;
END;
END IF;
END LOOP;
END IF ;
COMMIT;
END LOOP;
END mpl_cse_create_roles;

Adding Users to PQH_ROLES

For adding user to PQH_ROLES

Table : per_people_extra_info  — Role ID would be stored in an attribute as per setup

begin
hr_person_extra_info_api.create_person_extra_info
(p_validate                   => false
,p_person_id                  => 25915
,p_information_type           => ‘PQH_ROLE_USERS’
,p_pei_information_category   => ‘PQH_ROLE_USERS’
,p_pei_information3           => 4001–l_num_role_id
,p_pei_information4           => ‘N’
,p_pei_information5           => ‘Y’
,p_pei_information9           => ‘N’
,p_person_extra_info_id       =>l_num_person_extra_info_id
,p_object_version_number      =>l_num_ovn_pie
);

Create Position Control roles(pqh_roles)

The following script can be used for creating Position Control roles used for AME.

Table : PQH_ROLES

Navigation : Global HRMS Manger –> Ctrl + L –> Maintain Roles

 

Capture

 

DECLARE
L_NUM_ROLE_ID  pqh_roles.role_id%TYPE;
l_num_object_version_number  pqh_roles.object_version_number%TYPE;
BEGIN
pqh_roles_api.create_role
(
P_VALIDATE                      => FALSE
,P_ROLE_ID                       => L_NUM_ROLE_ID
,P_ROLE_NAME                    => ‘DEMO_ROLE’
,P_ROLE_TYPE_CD                  =>’EMP’
,P_ENABLE_FLAG                   => ‘Y’
,P_OBJECT_VERSION_NUMBER         =>L_NUM_OBJECT_VERSION_NUMBER
— ,p_business_group_id              in  number    default null
,P_EFFECTIVE_DATE                =>SYSDATE
, P_INFORMATION_CATEGORY        =>NULL
,p_information4=> ‘IN-BAN19’
);
COMMIT;
END;

/***********************END********************************/

 

 

 

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;