1 PACKAGE BODY per_org_man_count_name_pkg AS
2 /* $Header: pewspor1.pkb 120.2 2010/02/24 09:45:47 sgundoju ship $ */
3 --
4 /*
5 +======================================================================+
6 | Copyright (c) 1993 Oracle Corporation |
7 | Redwood Shores, California, USA |
8 | All rights reserved. |
9 +======================================================================+
10
11 Name
12 Richard Metcalf
13 Purpose
14 Count the number of managers in the organization and
15 if there is one put the managers name
16 count the number of organizations the employee is in
17 and if there is only puts the organization name
18 History
19 28-OCT-93 R Metcalf created
20 04-OCT-94 R Fine Renamed package to conform to naming standards.
21 31-JUL-96 J Alloun Added error handling.
22 20-JUN-97 70.7 LMA 1) Fixed bug 507611
23 NLSP16.1 FRM-40735 WHEN-VALIDATE-ITEM
24 TRIGGER RAISED UNHANDLED EXCEPTION VALUE_E
25 by change MANAGER_NAME from varchar2(60) to
26 PER_ALL_PEOPLE_F.E.FULL_NAME%type and
27 ORGANIZATION_NAME from varchar2(60) to
28 HR_ALL_ORGANIZATION_UNITS.NAME%type
29 2) Fixed hard coded messages
30 24-Jun-97 70.8 teyres Changed as to is on create or replace line
31 25-Jun-97 110.1/70.9 teyres 110.1 and 70.9 are the same
32 03-DEC-97 110.2 mbocutt Add missing token substitution in message
33 HR_NUM_ORGANIZATIONS.
34 04-DEC-98 115.1 tfilippi Changed ORGANIZATION_COUNT_NAME to support MLS
35 18-JUN-1999 714621 115.2 asahay added p_manager_flag to
36 organization_count_name procedure
37 30-DEC-99 115.3 ccarter Bug 1123545, changed length of manager_desc_flex
38 to 80 and manager_flag to 30.
39 14-SEP-00 115.4 jpbard Added support for global org hierarchies
40 17-JUL-07 115.6 sathkris Changed the length of the variable NO_OF_MANAGERS
41 FROM 3 TO 4 for bug 6219536
42 24-FEB-10 115.8 sgundoju Added a new condition in procedure
43 ORGANIZATION_COUNT_NAME
44 */
45 --
46 PROCEDURE MANAGER_COUNT_NAME(P_ORGANIZATION_ID IN NUMBER,
47 P_BUSINESS_GROUP_ID IN NUMBER,
48 P_SESSION_DATE IN DATE,
49 P_MANAGER IN OUT NOCOPY VARCHAR2 ) IS
50 MANAGER_NAME PER_ALL_PEOPLE_F.FULL_NAME%type;
51 NO_OF_MANAGERS NUMBER(4); --FIX FOR BUG 6219536
52 BEGIN
53 SELECT COUNT(DISTINCT E.PERSON_ID),
54 MAX(E.FULL_NAME)
55 INTO
56 NO_OF_MANAGERS,
57 MANAGER_NAME
58 FROM
59 PER_ALL_PEOPLE_F E,
60 PER_ALL_ASSIGNMENTS_F A
61 WHERE (E.CURRENT_EMPLOYEE_FLAG = 'Y' OR
62 E.CURRENT_NPW_FLAG = 'Y')
63 AND A.PERSON_ID = E.PERSON_ID
64 AND A.ORGANIZATION_ID = P_ORGANIZATION_ID
65 AND ((A.ASSIGNMENT_TYPE = 'E' AND
66 A.MANAGER_FLAG = 'Y') OR
67 (A.ASSIGNMENT_TYPE = 'C' AND
68 A.MANAGER_FLAG = 'Y'))
69 AND P_SESSION_DATE BETWEEN
70 E.EFFECTIVE_START_DATE AND E.EFFECTIVE_END_DATE
71 AND P_SESSION_DATE BETWEEN
72 A.EFFECTIVE_START_DATE AND A.EFFECTIVE_END_DATE;
73
74 --
75 --If there is only one manager put the mnager's name
76 --
77
78 IF NO_OF_MANAGERS = 0 THEN
79 fnd_message.set_name('PER', 'HR_NO_CURRENT_MANAGERS');
80 P_MANAGER := fnd_message.get; --'** No Current Managers **';
81 ELSIF NO_OF_MANAGERS = 1 THEN
82 P_MANAGER := MANAGER_NAME;
83 ELSE
84 fnd_message.set_name('PER', 'HR_NUM_CURRENT_MANAGERS');
85 fnd_message.set_token('number', to_char(NO_OF_MANAGERS)); --'** num Current Managers **';
86 P_MANAGER := fnd_message.get;
87 END IF;
88 END;
89 --
90 --Count the number of organizations that the employee is in
91 --
92 PROCEDURE ORGANIZATION_COUNT_NAME
93 (P_ORGANIZATION IN OUT NOCOPY VARCHAR2,
94 P_MANAGER_FLAG_DESC IN OUT NOCOPY VARCHAR2,
95 P_MANAGER_FLAG IN OUT NOCOPY VARCHAR2,
96 P_PERSON_ID IN NUMBER,
97 P_SESSION_DATE IN DATE,
98 P_ORGANIZATION_ID IN NUMBER,
99 P_ORGANIZATION_STRUCTURE_ID IN NUMBER,
100 P_BUSINESS_GROUP_ID IN NUMBER,
101 P_VERSION_ID IN NUMBER,
102 P_USER_PERSON_TYPE OUT NOCOPY VARCHAR2) IS
103 --
104 NO_OF_ORGS NUMBER(3);
105 ORGANIZATION_NAME HR_ALL_ORGANIZATION_UNITS.NAME%type;
106 MANAGER_FLAG_DESC VARCHAR2(80);
107 MANAGER_FLAG VARCHAR2(30);
108 --
109 BEGIN
110 SELECT
111 COUNT(DISTINCT(TO_CHAR(A.ORGANIZATION_ID)||NVL(MANAGER_FLAG,'N'))),
112 MAX(OTL.NAME),
113 MAX(L.MEANING),
114 MAX(NVL(A.MANAGER_FLAG,'N'))
115 INTO NO_OF_ORGS,
116 ORGANIZATION_NAME,
117 MANAGER_FLAG_DESC,
118 MANAGER_FLAG
119 FROM PER_ALL_ASSIGNMENTS_F A,
120 HR_ALL_ORGANIZATION_UNITS O,
121 HR_ALL_ORGANIZATION_UNITS_TL OTL,
122 FND_LOOKUPS L
123 WHERE A.PERSON_ID = P_PERSON_ID
124 AND (A.ASSIGNMENT_TYPE = 'E' OR A.ASSIGNMENT_TYPE = 'C')
125 --Added for Bug 6010637
126 AND (SELECT PER_SYSTEM_STATUS FROM PER_ASSIGNMENT_STATUS_TYPES PAST
127 WHERE A.ASSIGNMENT_STATUS_TYPE_ID = PAST.ASSIGNMENT_STATUS_TYPE_ID
128 AND A.ASSIGNMENT_TYPE IN ('E','C')) IN ('ACTIVE_ASSIGN','ACTIVE_CWK')
129 --
130 AND P_SESSION_DATE BETWEEN
131 A.EFFECTIVE_START_DATE AND A.EFFECTIVE_END_DATE
132 AND O.ORGANIZATION_ID = A.ORGANIZATION_ID
133 AND O.ORGANIZATION_ID = OTL.ORGANIZATION_ID
134 AND OTL.LANGUAGE = USERENV('LANG')
135 AND ((O.ORGANIZATION_ID = P_ORGANIZATION_ID AND
136 P_ORGANIZATION_STRUCTURE_ID IS NULL)
137 OR (P_ORGANIZATION_STRUCTURE_ID IS NOT NULL AND
138 EXISTS
139 (SELECT 1
140 FROM PER_ORG_STRUCTURE_ELEMENTS E
141 WHERE O.ORGANIZATION_ID
142 IN (E.ORGANIZATION_ID_CHILD,E.ORGANIZATION_ID_PARENT)
143 AND E.ORG_STRUCTURE_VERSION_ID = P_VERSION_ID)))
144 AND L.LOOKUP_CODE = NVL(A.MANAGER_FLAG,'N')
145 AND L.LOOKUP_TYPE = 'YES_NO';
146
147 --
148 --If there is only one organization then put the organization name
149 --
150
151 IF NO_OF_ORGS = 0 THEN
152 fnd_message.set_name('PER', 'HR_NO_ORGANIZATIONS');
153 P_ORGANIZATION := fnd_message.get; --'** No Organizations **'
154 P_MANAGER_FLAG_DESC := NULL;
155 P_MANAGER_FLAG := NULL;
156 ELSIF NO_OF_ORGS = 1 THEN
157 --
158 P_ORGANIZATION := ORGANIZATION_NAME;
159 P_MANAGER_FLAG_DESC := MANAGER_FLAG_DESC;
160 P_MANAGER_FLAG := MANAGER_FLAG;
161 --
162 p_user_person_type := hr_person_type_usage_info.get_user_person_type
163 (p_effective_date => p_session_date
164 ,p_person_id => p_person_id);
165 --
166 ELSE
167 fnd_message.set_name('PER', 'HR_NUM_ORGANIZATIONS');
168 fnd_message.set_token('number', to_char(NO_OF_ORGS));
169 P_ORGANIZATION := fnd_message.get; --'** num Organizations **'
170 P_MANAGER_FLAG_DESC := NULL;
171 P_MANAGER_FLAG := NULL;
172 END IF;
173 END;
174 end per_org_man_count_name_pkg;
[Home] [Help]