1 PACKAGE EAM_ASSIGN_EMP_PUB AUTHID CURRENT_USER AS
2 /* $Header: EAMPESHS.pls 120.0.12010000.3 2008/09/23 07:37:47 vmec ship $ */
3 /***************************************************************************
4 --
5 -- Copyright (c) 2002 Oracle Corporation, Redwood Shores, CA, USA
6 -- All rights reserved.
7 --
8 -- FILENAME
9 --
10 -- EAMPESHS.pls
11 --
12 -- DESCRIPTION
13 --
14 -- Package Interface for returning the various employees eligible for assignment
15 -- to a specific workorder -operation or workorder-operation-resource context.
16 -- NOTES
17 --
18 -- HISTORY
19 --
20 -- 11-Mar-05 Samir Jain Initial Creation
21 ***************************************************************************/
22
23 TYPE Emp_Search_Result_Rec_Type IS RECORD
24 (
25 person_id NUMBER,
26 employee_name VARCHAR2(240),
27 employee_number VARCHAR2(30),
28 instance_id NUMBER,
29 resource_id NUMBER,
30 department_id NUMBER,
31 resource_code VARCHAR2(10),
32 department_code VARCHAR2(10),
33 assign_unassign_enable VARCHAR2(30),
34 available_hours NUMBER,
35 assigned_hours NUMBER,
36 unassigned_hours NUMBER,
37 assigned_percentage NUMBER,
38 start_date DATE,
39 completion_date DATE,
40 duration NUMBER,
41 wo_firm_status VARCHAR2(1), --1 firm, 2 non-firm
42 uom VARCHAR2(3)
43 );
44
45 TYPE Emp_Search_Result_Tbl_Type IS TABLE OF Eam_Emp_Search_Result_Tbl%ROWTYPE;
46
47 TYPE Emp_Assignment_Rec_Type IS RECORD
48 (
49 wip_entity_id NUMBER,
50 wo_end_dt DATE,
51 wo_st_dt DATE,
52 WorkOrderName VARCHAR2(240),
53 Resource_code VARCHAR2(10),
54 Update_Switcher VARCHAR2(30),
55 usage NUMBER,
56 operation_seq_num NUMBER,
57 resource_seq_num NUMBER,
58 person_id NUMBER,
59 wo_assign_check CHAR(1),
60 Assign_Switcher VARCHAR2(30) ,
61 instance_id NUMBER,
62 organization_id NUMBER
63 );
64
65 TYPE Emp_Assignment_Tbl_Type IS TABLE OF Eam_Emp_Assignment_Details_Tbl%ROWTYPE;
66
67
68 --- Function and procedure signature to return the employee search results
69 /*
70 * This procedure is used to get all the employees who can be assigned to a workorder.
71 * Depending upon the search criteria entered, the eligible employees will be inserted into Eam_Emp_Search_Result_Tbl temporary table.
72 * The API calls the required procedures and function to calculate the assigned hour,available hour,assigned percentage for each eligible employee.
73 * The API requires you to enter atleast one of the following parameters: department id,resource id, person id,competence type, competence id.
74 * Also, enter the relevant horizon for which the result is to be fetched.
75 * In case of error ,API reports detailed and translatable error messages .
76 */
77
78
79 PROCEDURE Get_Emp_Search_Results_Pub (
80 p_horizon_start_date IN DATE ,
81 p_horizon_end_date IN DATE ,
82 p_organization_id IN NUMBER,
83 p_wip_entity_id IN NUMBER ,
84 p_competence_type IN VARCHAR2 ,
85 p_competence_id IN NUMBER ,
86 p_resource_id IN NUMBER ,
87 p_resource_seq_num IN NUMBER ,
88 p_operation_seq_num IN NUMBER ,
89 p_department_id IN NUMBER ,
90 p_person_id IN NUMBER ,
91 p_api_version IN NUMBER :=1.0 ,
92 p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE,
93 p_commit IN VARCHAR2:= FND_API.G_FALSE ,
94 p_validation_level IN NUMBER:= FND_API.G_VALID_LEVEL_FULL,
95 x_return_status OUT NOCOPY VARCHAR2 ,
96 x_msg_count OUT NOCOPY NUMBER ,
97 x_msg_data OUT NOCOPY VARCHAR2);
98
99 -- Function and procedure signature to return the assignment details of an employee
100 /*
101 * This procedure is used to get all the assignments for an employee.
102 * Depending on the horizon selected the eligible employees assignments will be inserted into Eam_Emp_Assignment_Details_Tbl temporary table.
103 * The API calls the required procedures and function to fetch the assignments within the given horizon.
104 * In case of error ,API reports detailed and translatable error messages .
105 */
106 PROCEDURE Get_Emp_Assignment_Details_Pub
107 (
108 p_person_id IN VARCHAR2,
109 p_horizon_start_date IN DATE,
110 p_horizon_end_date IN DATE,
111 p_organization_id IN NUMBER,
112 p_api_version IN NUMBER :=1.0 ,
113 p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE,
114 p_commit IN VARCHAR2:= FND_API.G_FALSE ,
115 p_validation_level IN NUMBER:= FND_API.G_VALID_LEVEL_FULL,
116 x_return_status OUT NOCOPY VARCHAR2 ,
117 x_msg_count OUT NOCOPY NUMBER ,
118 x_msg_data OUT NOCOPY VARCHAR2);
119
120
121
122 PROCEDURE Get_Emp_Search_Results_Pvt (
123 p_horizon_start_date IN DATE ,
124 p_horizon_end_date IN DATE ,
125 p_organization_id IN NUMBER,
126 p_wip_entity_id IN NUMBER ,
127 p_competence_type IN VARCHAR2 ,
128 p_competence_id IN NUMBER ,
129 p_resource_id IN NUMBER ,
130 p_resource_seq_num IN NUMBER ,
131 p_operation_seq_num IN NUMBER ,
132 p_department_id IN NUMBER ,
133 p_person_id IN NUMBER ,
134 p_api_version IN NUMBER :=1.0 ,
135 p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE,
136 p_commit IN VARCHAR2:= FND_API.G_FALSE ,
137 p_validation_level IN NUMBER:= FND_API.G_VALID_LEVEL_FULL,
138 x_return_status OUT NOCOPY VARCHAR2 ,
139 x_msg_count OUT NOCOPY NUMBER ,
140 x_msg_data OUT NOCOPY VARCHAR2);
141
142 -- Function and procedure signature to return the assignment details of an employee
143 PROCEDURE Get_Emp_Assignment_Details_Pvt
144 (
145 p_person_id IN VARCHAR2,
146 p_horizon_start_date IN DATE,
147 p_horizon_end_date IN DATE,
148 p_organization_id IN NUMBER,
149 p_api_version IN NUMBER :=1.0 ,
150 p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE,
151 p_commit IN VARCHAR2:= FND_API.G_FALSE ,
152 p_validation_level IN NUMBER:= FND_API.G_VALID_LEVEL_FULL,
153 x_return_status OUT NOCOPY VARCHAR2 ,
154 x_msg_count OUT NOCOPY NUMBER ,
155 x_msg_data OUT NOCOPY VARCHAR2);
156
157
158 -- Function to get the assignment status of a workordder.
159
160 FUNCTION Get_Emp_Assignment_Status
161 (
162 p_wip_entity_id IN NUMBER,
163 p_organization_id IN NUMBER
164 )
165 RETURN VARCHAR2;
166
167 --Helper Functions--------------------------
168
169 FUNCTION Competence_Type_Check
170 (
171 p_person_id IN NUMBER,
172 p_competence_type IN VARCHAR2
173 )
174 RETURN VARCHAR2;
175
176 FUNCTION Competence_Check
177 (
178 p_person_id IN NUMBER,
179 p_competence_id IN NUMBER
180 )
181 RETURN VARCHAR2;
182
183 FUNCTION Cal_Assigned_Hours
184 (p_wo_st_dt IN DATE,
185 p_wo_end_dt IN DATE,
186 p_horizon_start_date IN DATE,
187 p_horizon_end_date IN DATE
188 )
189 RETURN NUMBER;
190
191 FUNCTION Cal_Available_Hour(
192 p_resource_id IN NUMBER,
193 p_dept_id IN NUMBER,
194 p_calendar_code IN VARCHAR2,
195 p_horizon_start_date IN DATE,
196 p_horizon_end_date IN DATE
197 )
198 RETURN NUMBER;
199
200 FUNCTION Cal_Hr_Sys_Between_Horizon
201 (p_wo_st_dt IN DATE,
202 p_wo_end_dt IN DATE,
203 p_horizon_start_date IN DATE,
204 p_horizon_end_date IN DATE
205 )
206 RETURN NUMBER;
207
208 FUNCTION Cal_Hr_Sys_Before_Horizon
209 (p_wo_st_dt IN DATE,
210 p_wo_end_dt IN DATE,
211 p_horizon_start_date IN DATE,
212 p_horizon_end_date IN DATE
213 )
214 RETURN NUMBER;
215
216
217 FUNCTION Date_Exception
218 (
219 p_date IN DATE,
220 p_calendar_code IN VARCHAR2
221 )
222 RETURN CHAR;
223
224
225 PROCEDURE Cal_Extra_Hour_Start_Dt
226 (
227 l_start_date IN DATE,
228 l_previous IN BOOLEAN,
229 l_calendar_code IN VARCHAR2,
230 l_dept_id IN NUMBER,
231 l_resource_id IN NUMBER,
232 x_start_date OUT NOCOPY DATE,
233 x_extra_hour OUT NOCOPY NUMBER
234 );
235
236 PROCEDURE Cal_Extra_Hour_End_Dt
237 (
238 l_end_date IN DATE,
239 l_previous IN BOOLEAN,
240 l_calendar_code IN VARCHAR2,
241 l_dept_id IN NUMBER,
242 l_resource_id IN NUMBER,
243 x_end_date OUT NOCOPY DATE,
244 x_extra_hour OUT NOCOPY NUMBER
245 );
246
247 procedure cal_extra_24_hr_end_dt
248 (
249 p_end_date IN DATE,
250 p_calendar_code IN VARCHAR2,
251 x_end_date OUT NOCOPY DATE,
252 x_extra_hour OUT NOCOPY NUMBER
253 );
254 procedure cal_extra_24_hr_st_dt
255 (
256 p_start_date IN DATE,
257 p_calendar_code IN VARCHAR2,
258 x_end_date OUT NOCOPY DATE,
259 x_extra_hour OUT NOCOPY NUMBER
260 );
261
262 PROCEDURE Cal_Extra_Hour_Same_Dt
263 (
264 l_start_date IN DATE,
265 l_end_date IN DATE,
266 l_calendar_code IN VARCHAR2,
267 l_dept_id IN NUMBER,
268 l_resource_id IN NUMBER,
269 x_extra_hour OUT NOCOPY NUMBER
270 );
271
272 PROCEDURE Cal_Extra_Hour_Generic
273 (
274 l_start_date IN DATE,
275 l_end_date IN DATE,
276 l_calendar_code IN VARCHAR2,
277 l_dept_id IN NUMBER,
278 l_resource_id IN NUMBER,
279 x_extra_hour OUT NOCOPY NUMBER
280 );
281
282 FUNCTION Fetch_Details
283 (
284 p_op_res_end_dt IN DATE
285 )
286 RETURN VARCHAR2;
287
288
289
290 END EAM_ASSIGN_EMP_PUB;
[Home] [Help]