1 PACKAGE BODY PER_ORG_BGT_PKG AS
2 /* $Header: pebgt02t.pkb 115.0 99/07/17 18:47:10 porting ship $ */
3 --
4 -- PROCEDURE GET_MANAGERS: Populate No of managers, manager name and
5 -- managers employee number within the control block.
6 -- If no managers then manager name reflects this.
7 -- If more than one then manager name also reflects this.
8 -- else if only one manager then the name and emp no is provided.
9 --
10 procedure get_managers(X_ORGANIZATION_ID Number,
11 X_BUSINESS_GROUP_ID Number,
12 X_NO_OF_MANAGERS IN OUT VARCHAR2,
13 X_MANAGER_NAME IN OUT VARCHAR2,
14 X_MANAGER_EMP_NO IN OUT VARCHAR2) is
15 --
16 l_real_manager_name varchar2 (240);
17 --
18 begin
19 --
20 hr_utility.set_message('801','HR_ALL_MANAGERS');
21 --
22 -- Assume more than one manager so set string to "Managers"
23 --
24 X_MANAGER_NAME := hr_utility.get_message;
25 --
26 -- Obtain number of managers and concatenate with "Managers"
27 -- Get all the data in a single cursor - we have to use group functions for
28 -- the manager's name and number, so we use MAX. If the count = 1, the name
29 -- and number we get must be the correct ones; if count != 1 we're not
30 -- interested in which name and number we'll get, as we discard them anyway.
31 -- This approach removes the need for a second cursor to get the name and
32 -- number separately. RMF 15.11.94.
33 --
34 SELECT COUNT(E.PERSON_ID),
35 '** ' || COUNT(E.PERSON_ID) ||' '|| X_MANAGER_NAME,
36 MAX(E.FULL_NAME),
37 MAX(E.EMPLOYEE_NUMBER)
38 INTO X_NO_OF_MANAGERS,
39 X_MANAGER_NAME,
40 l_real_manager_name,
41 X_MANAGER_EMP_NO
42 FROM PER_ALL_PEOPLE E
43 , PER_ALL_ASSIGNMENTS A
44 WHERE E.CURRENT_EMPLOYEE_FLAG = 'Y'
45 AND A.PERSON_ID = E.PERSON_ID
46 AND A.ORGANIZATION_ID = X_ORGANIZATION_ID
47 AND A.BUSINESS_GROUP_ID = X_BUSINESS_GROUP_ID
48 AND A.ASSIGNMENT_TYPE = 'E'
49 AND A.MANAGER_FLAG = 'Y';
50 --
51 if X_NO_OF_MANAGERS = 0 then
52 -- Set manager name to "No Managers"
53 --
54 hr_utility.set_message('801','HR_ALL_NO_MANAGERS');
55 X_MANAGER_NAME := hr_utility.get_message;
56 X_MANAGER_EMP_NO := NULL;
57 --
58 elsif X_NO_OF_MANAGERS = 1 then
59 -- set X_MANAGER_NAME to the manager name retrieved
60 --
61 X_MANAGER_NAME := l_real_manager_name;
62 --
63 else
64 -- more than one manager, so clear the manager emp no. We've already set
65 -- the manager name to "** n Managers **".
66 X_MANAGER_EMP_NO := NULL;
67 end if;
68 --
69 end get_managers;
70 --
71 END PER_ORG_BGT_PKG;
[Home] [Help]