[Home] [Help]
PACKAGE: APPS.AHL_AMP_WORKBENCH_PVT
Source
1 PACKAGE AHL_AMP_WORKBENCH_PVT AUTHID CURRENT_USER AS
2 /* $Header: AHLVAMPS.pls 120.0.12020000.2 2012/12/11 03:06:52 prakkum noship $ */
3 --
4 ---------------------------------------------------------------------
5 -- Define Record Types for record structures needed by the APIs --
6 ---------------------------------------------------------------------
7 -- Record Type used for Org Search criteria
8 TYPE ORG_SCH_SEARCH_REC
9 IS
10 RECORD
11 (
12 ORG_ID NUMBER,
13 DEPARTMENT_ID NUMBER,
14 SPACE_ID NUMBER,
15 DEPARTMENT_NAME VARCHAR2(240),
16 SPACE_NAME VARCHAR2(240),
17 START_FROM_DATE DATE,
18 START_BEFORE_DATE DATE,
19 DISPLAY_WINDOW NUMBER,
20 RESULT_FILTER VARCHAR2(15) );
21
22 -- Record Type used for Fleet Search criteria
23 TYPE FLEET_SCH_SEARCH_REC
24 IS
25 RECORD
26 (
27 FLEET_ID NUMBER,
28 UNIT_ID NUMBER,
29 UNIT_NAME VARCHAR2(240),
30 MASTER_CONFIG VARCHAR2(240),
31 MINIMUM_DURATION NUMBER,
32 UOM VARCHAR2(15),
33 START_FROM_DATE DATE,
34 START_BEFORE_DATE DATE,
35 DISPLAY_WINDOW NUMBER );
36
37 -- Record Type used return data which will populate the VO
38 TYPE SCH_GRAPH_RESULTS_REC
39 IS
40 RECORD
41 (
42 ORG_ID NUMBER,
43 DEPARTMENT_ID NUMBER,
44 DEPARTMENT_DESC VARCHAR2(240),
45 SPACE_ID NUMBER,
46 SPACE_NAME VARCHAR2(30),
47 UNIT_ID NUMBER,
48 UNIT_NAME VARCHAR2(240),
49 SCHEDULE_TYPE_1 VARCHAR2(30),
50 VISIT_DATE_1 DATE,
51 VISIT_ID_1 NUMBER,
52 SCHEDULE_TYPE_2 VARCHAR2(30),
53 VISIT_DATE_2 DATE,
54 VISIT_ID_2 NUMBER,
55 SCHEDULE_TYPE_3 VARCHAR2(30),
56 VISIT_DATE_3 DATE,
57 VISIT_ID_3 NUMBER,
58 SCHEDULE_TYPE_4 VARCHAR2(30),
59 VISIT_DATE_4 DATE,
60 VISIT_ID_4 NUMBER,
61 SCHEDULE_TYPE_5 VARCHAR2(30),
62 VISIT_DATE_5 DATE,
63 VISIT_ID_5 NUMBER,
64 SCHEDULE_TYPE_6 VARCHAR2(30),
65 VISIT_DATE_6 DATE,
66 VISIT_ID_6 NUMBER,
67 SCHEDULE_TYPE_7 VARCHAR2(30),
68 VISIT_DATE_7 DATE,
69 VISIT_ID_7 NUMBER,
70 SCHEDULE_TYPE_8 VARCHAR2(30),
71 VISIT_DATE_8 DATE,
72 VISIT_ID_8 NUMBER,
73 SCHEDULE_TYPE_9 VARCHAR2(30),
74 VISIT_DATE_9 DATE,
75 VISIT_ID_9 NUMBER,
76 SCHEDULE_TYPE_10 VARCHAR2(30),
77 VISIT_DATE_10 DATE,
78 VISIT_ID_10 NUMBER,
79 SCHEDULE_TYPE_11 VARCHAR2(30),
80 VISIT_DATE_11 DATE,
81 VISIT_ID_11 NUMBER,
82 SCHEDULE_TYPE_12 VARCHAR2(30),
83 VISIT_DATE_12 DATE,
84 VISIT_ID_12 NUMBER,
85 SCHEDULE_TYPE_13 VARCHAR2(30),
86 VISIT_DATE_13 DATE,
87 VISIT_ID_13 NUMBER,
88 SCHEDULE_TYPE_14 VARCHAR2(30),
89 VISIT_DATE_14 DATE,
90 VISIT_ID_14 NUMBER,
91 SCHEDULE_TYPE_15 VARCHAR2(30),
92 VISIT_DATE_15 DATE,
93 VISIT_ID_15 NUMBER,
94 SCHEDULE_TYPE_16 VARCHAR2(30),
95 VISIT_DATE_16 DATE,
96 VISIT_ID_16 NUMBER,
97 SCHEDULE_TYPE_17 VARCHAR2(30),
98 VISIT_DATE_17 DATE,
99 VISIT_ID_17 NUMBER,
100 SCHEDULE_TYPE_18 VARCHAR2(30),
101 VISIT_DATE_18 DATE,
102 VISIT_ID_18 NUMBER,
103 SCHEDULE_TYPE_19 VARCHAR2(30),
104 VISIT_DATE_19 DATE,
105 VISIT_ID_19 NUMBER,
106 SCHEDULE_TYPE_20 VARCHAR2(30),
107 VISIT_DATE_20 DATE,
108 VISIT_ID_20 NUMBER,
109 SCHEDULE_TYPE_21 VARCHAR2(30),
110 VISIT_DATE_21 DATE,
111 VISIT_ID_21 NUMBER,
112 FILTER_REC BOOLEAN := FALSE );
113
114 -- Record Type used to returns the Visit Details
115 TYPE SCH_VISITS_REC
116 IS
117 RECORD
118 (
119 VISIT_ID NUMBER,
120 START_DATE DATE,
121 END_DATE DATE );
122
123 -- Maintenance Capacity Graph -Input record type
124 -- resource availability will be filled by API while processing
125
126 TYPE resource_input_rec_type
127 IS
128 RECORD
129 (
130 resource_id NUMBER,
131 resource_availability NUMBER); /* Sthilak removed the numeric precision */
132
133 -- Maintenance Capacity Graph -Output record type
134 TYPE resource_ouput_rec_type
135 IS
136 RECORD
137 (
138 on_date DATE,
139 cent_percent_capacity NUMBER, -- STHILAK Cent percent BAr
140 r1_capacity NUMBER,
141 r2_capacity NUMBER,
142 r3_capacity NUMBER,
143 r4_capacity NUMBER,
144 r5_capacity NUMBER ); /* Sthilak removed the numeric precision */
145
146 ----------------------------------------------
147 -- Define Table Type for records structures --
148 ----------------------------------------------
149 TYPE SCH_GRAPH_RESULTS_TBL
150 IS
151 TABLE OF SCH_GRAPH_RESULTS_REC INDEX BY BINARY_INTEGER;
152
153 TYPE SCH_VISITS_TBL
154 IS
155 TABLE OF SCH_VISITS_REC INDEX BY BINARY_INTEGER;
156
157 TYPE resource_input_tbl_type
158 IS
159 TABLE OF resource_input_rec_type INDEX BY BINARY_INTEGER;
160
161 TYPE resource_output_tbl_type
162 IS
163 TABLE OF resource_ouput_rec_type INDEX BY BINARY_INTEGER;
164
165 -----------------------------------------------
166 -- Define Procedures and Functions --
167 -----------------------------------------------
168 -- Procedure name : GET_ORG_SCH_GRAPH
169 -- Type : Public
170 -- Parameters :
171 --
172 -- Standard IN Parameters :
173 -- p_api_version NUMBER Required
174 -- p_init_msg_list VARCHAR2 Default FND_API.G_FALSE
175 -- p_validation_level NUMBER Default FND_API.G_VALID_LEVEL_FULL
176 --
177 -- Standard OUT Parameters :
178 -- x_return_status VARCHAR2 Required
179 -- x_msg_count NUMBER Required
180 -- x_msg_data VARCHAR2 Required
181 -- GET_ORG_SCH_GRAPH params
182 -- p_org_sch_search_rec ORG_SCH_SEARCH_REC Required
183 -- x_sch_graph_results_tbl SCH_GRAPH_RESULTS_TBL
184
185 PROCEDURE GET_ORG_SCH_GRAPH(
186 p_api_version IN NUMBER := 1.0,
187 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
188 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
189 x_return_status OUT NOCOPY VARCHAR2,
190 x_msg_count OUT NOCOPY NUMBER,
191 x_msg_data OUT NOCOPY VARCHAR2,
192 p_org_sch_search_rec IN ORG_SCH_SEARCH_REC,
193 x_sch_graph_results_tbl OUT NOCOPY SCH_GRAPH_RESULTS_TBL );
194
195 -- Procedure name : GET_VISITS_FOR_DATE
196 -- Type : Public
197 -- Parameters :
198 --
199 -- Standard IN Parameters :
200 -- p_api_version NUMBER Required
201 -- p_init_msg_list VARCHAR2 Default FND_API.G_FALSE
202 -- p_validation_level NUMBER Default FND_API.G_VALID_LEVEL_FULL
203 --
204 -- Standard OUT Parameters :
205 -- x_return_status VARCHAR2 Required
206 -- x_msg_count NUMBER Required
207 -- x_msg_data VARCHAR2 Required
208 -- GET_VISITS_FOR_DATE_ORG params
209 -- p_org_sch_search_rec ORG_SCH_SEARCH_REC Required
210 -- x_sch_graph_rec SCH_GRAPH_RESULTS_REC
211 -- x_sch_visits_tbl SCH_VISITS_TBL
212
213 PROCEDURE GET_VISITS_FOR_DATE_ORG(
214 p_api_version IN NUMBER := 1.0,
215 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
216 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
217 x_return_status OUT NOCOPY VARCHAR2,
218 x_msg_count OUT NOCOPY NUMBER,
219 x_msg_data OUT NOCOPY VARCHAR2,
220 p_org_sch_search_rec IN ORG_SCH_SEARCH_REC,
221 x_sch_graph_rec OUT NOCOPY SCH_GRAPH_RESULTS_REC,
222 x_sch_visits_tbl OUT NOCOPY SCH_VISITS_TBL );
223
224 -- Procedure name : GET_FLT_SCH_GRAPH
225 -- Type : Public
226 -- Parameters :
227 --
228 -- Standard IN Parameters :
229 -- p_api_version NUMBER Required
230 -- p_init_msg_list VARCHAR2 Default FND_API.G_FALSE
231 -- p_validation_level NUMBER Default FND_API.G_VALID_LEVEL_FULL
232 --
233 -- Standard OUT Parameters :
234 -- x_return_status VARCHAR2 Required
235 -- x_msg_count NUMBER Required
236 -- x_msg_data VARCHAR2 Required
237 -- GET_ORG_SCH_GRAPH params
238 -- p_flt_sch_search_rec FLEET_SCH_SEARCH_REC Required
239 -- x_sch_graph_results_tbl SCH_GRAPH_RESULTS_TBL
240
241 PROCEDURE GET_FLT_SCH_GRAPH(
242 p_api_version IN NUMBER := 1.0,
243 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
244 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
245 x_return_status OUT NOCOPY VARCHAR2,
246 x_msg_count OUT NOCOPY NUMBER,
247 x_msg_data OUT NOCOPY VARCHAR2,
248 p_flt_sch_search_rec IN FLEET_SCH_SEARCH_REC,
249 x_sch_graph_results_tbl OUT NOCOPY SCH_GRAPH_RESULTS_TBL );
250
251 -- Procedure name : GET_VISITS_FOR_DATE
252 -- Type : Public
253 -- Parameters :
254 --
255 -- Standard IN Parameters :
256 -- p_api_version NUMBER Required
257 -- p_init_msg_list VARCHAR2 Default FND_API.G_FALSE
258 -- p_validation_level NUMBER Default FND_API.G_VALID_LEVEL_FULL
259 --
260 -- Standard OUT Parameters :
261 -- x_return_status VARCHAR2 Required
262 -- x_msg_count NUMBER Required
263 -- x_msg_data VARCHAR2 Required
264 -- GET_VISITS_FOR_DATE params
265 -- p_flt_sch_search_rec FLEET_SCH_SEARCH_REC Required
266 -- x_sch_graph_rec SCH_GRAPH_RESULTS_REC
267 -- x_sch_visits_tbl SCH_VISITS_TBL
268
269 PROCEDURE GET_VISITS_FOR_DATE_FLT(
270 p_api_version IN NUMBER := 1.0,
271 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
272 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
273 x_return_status OUT NOCOPY VARCHAR2,
274 x_msg_count OUT NOCOPY NUMBER,
275 x_msg_data OUT NOCOPY VARCHAR2,
276 p_flt_sch_search_rec IN FLEET_SCH_SEARCH_REC,
277 x_sch_graph_rec OUT NOCOPY SCH_GRAPH_RESULTS_REC,
278 x_sch_visits_tbl OUT NOCOPY SCH_VISITS_TBL );
279
280 -- Procedure name : GET_MC_GRAPH_DATA
281 -- Type : Public
282 -- Pre-reqs :
283 -- Function :
284 -- Parameters :
285 --
286 -- Standard IN Parameters :
287 -- p_api_version NUMBER Required
288 -- p_init_msg_list VARCHAR2 Default FND_API.G_FALSE
289 -- p_commit VARCHAR2 Default FND_API.G_FALSE
290 -- p_validation_level NUMBER Default FND_API.G_VALID_LEVEL_FULL
291 -- p_default VARCHAR2 Default FND_API.G_TRUE
292 -- p_module_type VARCHAR2 Default NULL
293 --
294 -- Standard OUT Parameters :
295 -- x_return_status VARCHAR2 Required
296 -- x_msg_count NUMBER Required
297 -- x_msg_data VARCHAR2 Required
298 -- GET_MC_GRAPH_DATA params
299 -- p_organization_id - organization id
300 -- p_department_id - department id
301 -- p_start_date - start date from which the data for graph has to be calculated
302 -- p_no_of_days - for how many number of days - the data has to be calculated
303 -- p_x_resource_input - list of resource ids for which - the data has to be calculated
304 -- p_x_resource_output - calculated data for drawing graph
305 -- x_plan_date - the date when ASCP program ran last
306
307 PROCEDURE GET_MC_GRAPH_DATA(
308 p_api_version IN NUMBER := 1.0,
309 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
310 p_commit IN VARCHAR2 := FND_API.G_FALSE,
311 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
312 p_default IN VARCHAR2 := FND_API.G_FALSE,
313 p_module_type IN VARCHAR2 := NULL,
314 x_return_status OUT NOCOPY VARCHAR2,
315 x_msg_count OUT NOCOPY NUMBER,
316 x_msg_data OUT NOCOPY VARCHAR2,
317 p_organization_id IN NUMBER,
318 p_department_id IN NUMBER,
319 p_start_date IN DATE , -- Sthilak removed the defaulting ER1#3799535
320 p_no_of_days IN NUMBER,
321 p_max_range IN NUMBER, --Sthilak added to make the max range as parameterized
322 p_x_resource_input IN OUT NOCOPY resource_input_tbl_type,
323 p_x_resource_output IN OUT NOCOPY resource_output_tbl_type,
324 x_plan_date OUT NOCOPY DATE);
325 /*-------------------------------STHILAK ER1#3799535 STARTS------------------------*/
326
327 -- Function name : GET_NUMBER_OF_WORKING_DAYS
328 -- TO calculate the number of working days in given interval
329 FUNCTION GET_NUMBER_OF_WORKING_DAYS(
330 p_org_id NUMBER ,
331 p_dept_id NUMBER,
332 p_start_dt DATE ,
333 p_end_dt DATE)
334 RETURN NUMBER;
335
336 -- Function name : GET_NEXT_WORKING_DATE
337 -- TO calculate the nth working day from the given date
338 FUNCTION GET_NEXT_WORKING_DATE(
339 p_org_id NUMBER ,
340 p_dept_id NUMBER,
341 p_start_dt DATE ,
342 p_no_of_days NUMBER)
343 RETURN DATE;
344
345 -- Function name : GET_NEW_REQ_END_DATE
346 -- TO calculate the new requirement end date based on params
347 -- p_org_id NUMBER ,
348 -- p_start_dt DATE,
349 -- p_end_date DATE,
350 -- p_usage_units NUMBER,
351 -- p_applied_units NUMBER
352 FUNCTION GET_NEW_REQ_END_DATE(
353 p_org_id NUMBER ,
354 p_dept_id NUMBER,
355 p_start_dt DATE,
356 p_end_date DATE,
357 p_usage_units NUMBER,
358 p_applied_units NUMBER)
359 RETURN DATE;
360
361
362
363
364
365 -- Function name : GET_DAY_REQUIREMENT
366 -- TO calculate the requirement units on a date based on params
367 -- p_usage_units NUMBER,
368 -- p_applied_units NUMBER,
369 -- p_org_id NUMBER,
370 -- p_start_date DATE ,
371 -- p_end_date DATE,
372 -- p_cal_date DATE
373 FUNCTION GET_DAY_REQUIREMENT(
374 p_usage_units NUMBER,
375 p_applied_units NUMBER,
376 p_org_id NUMBER,
377 p_dept_id NUMBER,
378 p_start_date DATE ,
379 p_end_date DATE,
380 p_cal_date DATE)
381 RETURN NUMBER;
382
383 /*-------------------------------STHILAK ER1#3799535 ENDS------------------------*/
384
385 -- Function name : GET_FLEET_NAME
386 -- Type : Public
387 -- Parameters :
388 -- GET_FLEET_NAME params
389 -- p_item_instance_id NUMBER Required
390 -- p_visit_start_date DATE Required
391 -- p_visit_end_date DATE Required
392
393 FUNCTION GET_FLEET_NAME(
394 p_item_instance_id IN NUMBER,
395 p_visit_start_date IN DATE,
396 p_visit_end_date IN DATE)
397 RETURN VARCHAR2;
398
399 -- Function name : GET_FLEET_HEADER_ID
400 -- Type : Public
401 -- Parameters :
402 -- GET_FLEET_NAME params
403 -- p_item_instance_id NUMBER Required
404 -- p_visit_start_date DATE Required
408 p_item_instance_id IN NUMBER,
405 -- p_visit_end_date DATE Required
406
407 FUNCTION GET_FLEET_HEADER_ID(
409 p_visit_start_date IN DATE,
410 P_VISIT_END_DATE IN DATE)
411 RETURN NUMBER;
412
413 END AHL_AMP_WORKBENCH_PVT;