1 PACKAGE BODY per_org_man_count_name_pkg AS
2 /* $Header: pewspor1.pkb 120.1.12010000.1 2008/07/28 06:08:05 appldev 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 */
43 --
44 PROCEDURE MANAGER_COUNT_NAME(P_ORGANIZATION_ID IN NUMBER,
45 P_BUSINESS_GROUP_ID IN NUMBER,
46 P_SESSION_DATE IN DATE,
47 P_MANAGER IN OUT NOCOPY VARCHAR2 ) IS
48 MANAGER_NAME PER_ALL_PEOPLE_F.FULL_NAME%type;
49 NO_OF_MANAGERS NUMBER(4); --FIX FOR BUG 6219536
50 BEGIN
51 SELECT COUNT(DISTINCT E.PERSON_ID),
52 MAX(E.FULL_NAME)
53 INTO
54 NO_OF_MANAGERS,
55 MANAGER_NAME
56 FROM
57 PER_ALL_PEOPLE_F E,
58 PER_ALL_ASSIGNMENTS_F A
59 WHERE (E.CURRENT_EMPLOYEE_FLAG = 'Y' OR
60 E.CURRENT_NPW_FLAG = 'Y')
61 AND A.PERSON_ID = E.PERSON_ID
62 AND A.ORGANIZATION_ID = P_ORGANIZATION_ID
63 AND ((A.ASSIGNMENT_TYPE = 'E' AND
64 A.MANAGER_FLAG = 'Y') OR
65 (A.ASSIGNMENT_TYPE = 'C' AND
66 A.MANAGER_FLAG = 'Y'))
67 AND P_SESSION_DATE BETWEEN
68 E.EFFECTIVE_START_DATE AND E.EFFECTIVE_END_DATE
69 AND P_SESSION_DATE BETWEEN
70 A.EFFECTIVE_START_DATE AND A.EFFECTIVE_END_DATE;
71
72 --
73 --If there is only one manager put the mnager's name
74 --
75
76 IF NO_OF_MANAGERS = 0 THEN
77 fnd_message.set_name('PER', 'HR_NO_CURRENT_MANAGERS');
78 P_MANAGER := fnd_message.get; --'** No Current Managers **';
79 ELSIF NO_OF_MANAGERS = 1 THEN
80 P_MANAGER := MANAGER_NAME;
81 ELSE
82 fnd_message.set_name('PER', 'HR_NUM_CURRENT_MANAGERS');
83 fnd_message.set_token('number', to_char(NO_OF_MANAGERS)); --'** num Current Managers **';
84 P_MANAGER := fnd_message.get;
85 END IF;
86 END;
87 --
88 --Count the number of organizations that the employee is in
89 --
90 PROCEDURE ORGANIZATION_COUNT_NAME
91 (P_ORGANIZATION IN OUT NOCOPY VARCHAR2,
92 P_MANAGER_FLAG_DESC IN OUT NOCOPY VARCHAR2,
93 P_MANAGER_FLAG IN OUT NOCOPY VARCHAR2,
94 P_PERSON_ID IN NUMBER,
95 P_SESSION_DATE IN DATE,
96 P_ORGANIZATION_ID IN NUMBER,
97 P_ORGANIZATION_STRUCTURE_ID IN NUMBER,
98 P_BUSINESS_GROUP_ID IN NUMBER,
99 P_VERSION_ID IN NUMBER,
100 P_USER_PERSON_TYPE OUT NOCOPY VARCHAR2) IS
101 --
102 NO_OF_ORGS NUMBER(3);
103 ORGANIZATION_NAME HR_ALL_ORGANIZATION_UNITS.NAME%type;
104 MANAGER_FLAG_DESC VARCHAR2(80);
105 MANAGER_FLAG VARCHAR2(30);
106 --
107 BEGIN
108 SELECT
109 COUNT(DISTINCT(TO_CHAR(A.ORGANIZATION_ID)||NVL(MANAGER_FLAG,'N'))),
110 MAX(OTL.NAME),
111 MAX(L.MEANING),
112 MAX(NVL(A.MANAGER_FLAG,'N'))
113 INTO NO_OF_ORGS,
114 ORGANIZATION_NAME,
115 MANAGER_FLAG_DESC,
116 MANAGER_FLAG
117 FROM PER_ALL_ASSIGNMENTS_F A,
118 HR_ALL_ORGANIZATION_UNITS O,
119 HR_ALL_ORGANIZATION_UNITS_TL OTL,
120 FND_LOOKUPS L
121 WHERE A.PERSON_ID = P_PERSON_ID
122 AND (A.ASSIGNMENT_TYPE = 'E' OR A.ASSIGNMENT_TYPE = 'C')
123 AND P_SESSION_DATE BETWEEN
124 A.EFFECTIVE_START_DATE AND A.EFFECTIVE_END_DATE
125 AND O.ORGANIZATION_ID = A.ORGANIZATION_ID
126 AND O.ORGANIZATION_ID = OTL.ORGANIZATION_ID
127 AND OTL.LANGUAGE = USERENV('LANG')
128 AND ((O.ORGANIZATION_ID = P_ORGANIZATION_ID AND
129 P_ORGANIZATION_STRUCTURE_ID IS NULL)
130 OR (P_ORGANIZATION_STRUCTURE_ID IS NOT NULL AND
131 EXISTS
132 (SELECT 1
133 FROM PER_ORG_STRUCTURE_ELEMENTS E
134 WHERE O.ORGANIZATION_ID
135 IN (E.ORGANIZATION_ID_CHILD,E.ORGANIZATION_ID_PARENT)
136 AND E.ORG_STRUCTURE_VERSION_ID = P_VERSION_ID)))
137 AND L.LOOKUP_CODE = NVL(A.MANAGER_FLAG,'N')
138 AND L.LOOKUP_TYPE = 'YES_NO';
139
140 --
141 --If there is only one organization then put the organization name
142 --
143
144 IF NO_OF_ORGS = 0 THEN
145 fnd_message.set_name('PER', 'HR_NO_ORGANIZATIONS');
146 P_ORGANIZATION := fnd_message.get; --'** No Organizations **'
147 P_MANAGER_FLAG_DESC := NULL;
148 P_MANAGER_FLAG := NULL;
149 ELSIF NO_OF_ORGS = 1 THEN
150 --
151 P_ORGANIZATION := ORGANIZATION_NAME;
152 P_MANAGER_FLAG_DESC := MANAGER_FLAG_DESC;
153 P_MANAGER_FLAG := MANAGER_FLAG;
154 --
155 p_user_person_type := hr_person_type_usage_info.get_user_person_type
156 (p_effective_date => p_session_date
157 ,p_person_id => p_person_id);
158 --
159 ELSE
160 fnd_message.set_name('PER', 'HR_NUM_ORGANIZATIONS');
161 fnd_message.set_token('number', to_char(NO_OF_ORGS));
162 P_ORGANIZATION := fnd_message.get; --'** num Organizations **'
163 P_MANAGER_FLAG_DESC := NULL;
164 P_MANAGER_FLAG := NULL;
165 END IF;
166 END;
167 end per_org_man_count_name_pkg;
[Home] [Help]