1611: -- Parameters : itemtype, itemkey, actid, funcmode, resultout, p_user_id,
1612: -- x_name, x_description
1613: -- Version : 1.1
1614: -- MDOKAL : Bug 2902588, changed code to check for fnd_user info 1st
1615: -- and then WF_ROLES instead of WF_USERS.
1616: -- : 19-NOV-03 MDOKAL Bug 3262184 - changed order in which
1617: -- wf users are retrieved.
1618: -- End of comments
1619: PROCEDURE GET_NOTIFICATION_AGENT(
1624: , p_user_id IN NUMBER
1625: , x_name OUT NOCOPY VARCHAR2
1626: , x_description OUT NOCOPY VARCHAR2 ) IS
1627:
1628: CURSOR wf_roles_csr(c_emp_id NUMBER, c_system VARCHAR2)
1629: IS
1630: SELECT NAME, DISPLAY_NAME
1631: FROM WF_ROLES
1632: WHERE orig_system_id = c_emp_id
1627:
1628: CURSOR wf_roles_csr(c_emp_id NUMBER, c_system VARCHAR2)
1629: IS
1630: SELECT NAME, DISPLAY_NAME
1631: FROM WF_ROLES
1632: WHERE orig_system_id = c_emp_id
1633: AND orig_system = c_system;
1634:
1635: CURSOR fnd_users_csr(c_user_id NUMBER)
1670: OPEN fnd_users_csr(p_user_id);
1671: FETCH fnd_users_csr INTO l_user, l_desc, l_emp;
1672: IF fnd_users_csr%notfound THEN
1673: -- 2nd check if id passed belongs to an employee rather then fnd user
1674: OPEN wf_roles_csr(p_user_id, 'PER');
1675: FETCH wf_roles_csr INTO l_user, l_desc;
1676: IF wf_roles_csr%notfound THEN
1677: CLOSE wf_roles_csr;
1678: -- Maybe a HR user not attached to FND user
1671: FETCH fnd_users_csr INTO l_user, l_desc, l_emp;
1672: IF fnd_users_csr%notfound THEN
1673: -- 2nd check if id passed belongs to an employee rather then fnd user
1674: OPEN wf_roles_csr(p_user_id, 'PER');
1675: FETCH wf_roles_csr INTO l_user, l_desc;
1676: IF wf_roles_csr%notfound THEN
1677: CLOSE wf_roles_csr;
1678: -- Maybe a HR user not attached to FND user
1679: OPEN wf_roles_csr(p_user_id, 'HZ_PARTY');
1672: IF fnd_users_csr%notfound THEN
1673: -- 2nd check if id passed belongs to an employee rather then fnd user
1674: OPEN wf_roles_csr(p_user_id, 'PER');
1675: FETCH wf_roles_csr INTO l_user, l_desc;
1676: IF wf_roles_csr%notfound THEN
1677: CLOSE wf_roles_csr;
1678: -- Maybe a HR user not attached to FND user
1679: OPEN wf_roles_csr(p_user_id, 'HZ_PARTY');
1680: FETCH wf_roles_csr INTO l_user, l_desc;
1673: -- 2nd check if id passed belongs to an employee rather then fnd user
1674: OPEN wf_roles_csr(p_user_id, 'PER');
1675: FETCH wf_roles_csr INTO l_user, l_desc;
1676: IF wf_roles_csr%notfound THEN
1677: CLOSE wf_roles_csr;
1678: -- Maybe a HR user not attached to FND user
1679: OPEN wf_roles_csr(p_user_id, 'HZ_PARTY');
1680: FETCH wf_roles_csr INTO l_user, l_desc;
1681: CLOSE wf_roles_csr;
1675: FETCH wf_roles_csr INTO l_user, l_desc;
1676: IF wf_roles_csr%notfound THEN
1677: CLOSE wf_roles_csr;
1678: -- Maybe a HR user not attached to FND user
1679: OPEN wf_roles_csr(p_user_id, 'HZ_PARTY');
1680: FETCH wf_roles_csr INTO l_user, l_desc;
1681: CLOSE wf_roles_csr;
1682: ELSE
1683: CLOSE wf_roles_csr;
1676: IF wf_roles_csr%notfound THEN
1677: CLOSE wf_roles_csr;
1678: -- Maybe a HR user not attached to FND user
1679: OPEN wf_roles_csr(p_user_id, 'HZ_PARTY');
1680: FETCH wf_roles_csr INTO l_user, l_desc;
1681: CLOSE wf_roles_csr;
1682: ELSE
1683: CLOSE wf_roles_csr;
1684: END IF;
1677: CLOSE wf_roles_csr;
1678: -- Maybe a HR user not attached to FND user
1679: OPEN wf_roles_csr(p_user_id, 'HZ_PARTY');
1680: FETCH wf_roles_csr INTO l_user, l_desc;
1681: CLOSE wf_roles_csr;
1682: ELSE
1683: CLOSE wf_roles_csr;
1684: END IF;
1685: END IF;
1679: OPEN wf_roles_csr(p_user_id, 'HZ_PARTY');
1680: FETCH wf_roles_csr INTO l_user, l_desc;
1681: CLOSE wf_roles_csr;
1682: ELSE
1683: CLOSE wf_roles_csr;
1684: END IF;
1685: END IF;
1686: CLOSE fnd_users_csr;
1687:
1687:
1688: -- if l_emp is not null then the user is attached to an employee
1689: IF l_emp IS NOT NULL THEN
1690:
1691: OPEN wf_roles_csr(l_emp, 'PER');
1692: FETCH wf_roles_csr INTO l_user, l_desc;
1693: CLOSE wf_roles_csr;
1694:
1695: END IF;
1688: -- if l_emp is not null then the user is attached to an employee
1689: IF l_emp IS NOT NULL THEN
1690:
1691: OPEN wf_roles_csr(l_emp, 'PER');
1692: FETCH wf_roles_csr INTO l_user, l_desc;
1693: CLOSE wf_roles_csr;
1694:
1695: END IF;
1696:
1689: IF l_emp IS NOT NULL THEN
1690:
1691: OPEN wf_roles_csr(l_emp, 'PER');
1692: FETCH wf_roles_csr INTO l_user, l_desc;
1693: CLOSE wf_roles_csr;
1694:
1695: END IF;
1696:
1697: -- if l_user is still null, no user info was found