1 PACKAGE AHL_UMP_UNITMAINT_PVT AUTHID CURRENT_USER AS
2 /* $Header: AHLVUMXS.pls 120.3.12020000.3 2013/01/10 01:20:58 saguo ship $ */
3
4 ---------------------------------------------------------------------
5 -- Define Record Types for record structures needed by the APIs --
6 ---------------------------------------------------------------------
7 TYPE Unit_Effectivity_Rec_Type IS RECORD (
8 UNIT_EFFECTIVITY_ID NUMBER,
9 OBJECT_VERSION_NUMBER NUMBER,
10 LAST_UPDATE_DATE DATE,
11 LAST_UPDATED_BY NUMBER,
12 CREATION_DATE DATE,
13 CREATED_BY NUMBER,
14 LAST_UPDATE_LOGIN NUMBER,
15 CSI_ITEM_INSTANCE_ID NUMBER,
16 -- Maps to CSI_ITEM_INSTANCE_ID
17 CSI_INSTANCE_NUMBER VARCHAR2(30),
18 MR_INTERVAL_ID NUMBER,
19 MR_EFFECTIVITY_ID NUMBER,
20 MR_ID NUMBER,
21 -- Following two map to MR_ID
22 MR_TITLE VARCHAR2(80),
23 MR_VERSION_NUMBER NUMBER,
24 STATUS_CODE VARCHAR2(30),
25 -- Maps to STATUS_CODE
26 STATUS VARCHAR2(80),
27 DUE_DATE DATE,
28 DUE_COUNTER_VALUE NUMBER,
29 FORECAST_SEQUENCE NUMBER,
30 REPETITIVE_MR_FLAG VARCHAR2(1),
31 TOLERANCE_FLAG VARCHAR2(1),
32 MESSAGE_CODE VARCHAR2(30),
33 PRECEDING_UE_ID NUMBER,
34 REMARKS VARCHAR2(4000),
35 DATE_RUN DATE,
36 SET_DUE_DATE DATE,
37 ACCOMPLISHED_DATE DATE,
38 -- Added for 11.5.10 Enhancements.
39 QA_COLLECTION_ID NUMBER,
40 UNIT_DEFERRAL_ID NUMBER,
41 UNIT_DEFERRAL_OBJECT_VERSION NUMBER,
42 ATTRIBUTE_CATEGORY VARCHAR2(30),
43 ATTRIBUTE1 VARCHAR2(150),
44 ATTRIBUTE2 VARCHAR2(150),
45 ATTRIBUTE3 VARCHAR2(150),
46 ATTRIBUTE4 VARCHAR2(150),
47 ATTRIBUTE5 VARCHAR2(150),
48 ATTRIBUTE6 VARCHAR2(150),
49 ATTRIBUTE7 VARCHAR2(150),
50 ATTRIBUTE8 VARCHAR2(150),
51 ATTRIBUTE9 VARCHAR2(150),
52 ATTRIBUTE10 VARCHAR2(150),
53 ATTRIBUTE11 VARCHAR2(150),
54 ATTRIBUTE12 VARCHAR2(150),
55 ATTRIBUTE13 VARCHAR2(150),
56 ATTRIBUTE14 VARCHAR2(150),
57 ATTRIBUTE15 VARCHAR2(150)
58 );
59
60 TYPE Unit_Threshold_Rec_Type IS RECORD (
61 UNIT_THRESHOLD_ID NUMBER,
62 OBJECT_VERSION_NUMBER NUMBER,
63 LAST_UPDATE_DATE DATE,
64 LAST_UPDATED_BY NUMBER,
65 CREATION_DATE DATE,
66 CREATED_BY NUMBER,
67 LAST_UPDATE_LOGIN NUMBER,
68 UNIT_EFFECTIVITY_ID NUMBER,
69 COUNTER_ID NUMBER,
70 -- Maps to COUNTER_ID
71 COUNTER_NAME VARCHAR2(80),
72 COUNTER_VALUE NUMBER,
73 OPERATION_FLAG VARCHAR2(1),
74 ATTRIBUTE_CATEGORY VARCHAR2(30),
75 ATTRIBUTE1 VARCHAR2(150),
76 ATTRIBUTE2 VARCHAR2(150),
77 ATTRIBUTE3 VARCHAR2(150),
78 ATTRIBUTE4 VARCHAR2(150),
79 ATTRIBUTE5 VARCHAR2(150),
80 ATTRIBUTE6 VARCHAR2(150),
81 ATTRIBUTE7 VARCHAR2(150),
82 ATTRIBUTE8 VARCHAR2(150),
83 ATTRIBUTE9 VARCHAR2(150),
84 ATTRIBUTE10 VARCHAR2(150),
85 ATTRIBUTE11 VARCHAR2(150),
86 ATTRIBUTE12 VARCHAR2(150),
87 ATTRIBUTE13 VARCHAR2(150),
88 ATTRIBUTE14 VARCHAR2(150),
89 ATTRIBUTE15 VARCHAR2(150),
90 -- Added for 11.5.10 Enhancements.
91 UNIT_DEFERRAL_ID NUMBER
92 );
93
94 TYPE Unit_Accomplish_Rec_Type IS RECORD (
95 UNIT_ACCOMPLISH_ID NUMBER,
96 OBJECT_VERSION_NUMBER NUMBER,
97 LAST_UPDATE_DATE DATE,
98 LAST_UPDATED_BY NUMBER,
99 CREATION_DATE DATE,
100 CREATED_BY NUMBER,
101 LAST_UPDATE_LOGIN NUMBER,
102 UNIT_EFFECTIVITY_ID NUMBER,
103 COUNTER_ID NUMBER,
104 -- Maps to COUNTER_ID
105 COUNTER_NAME VARCHAR2(80),
106 COUNTER_VALUE NUMBER,
107 OPERATION_FLAG VARCHAR2(1),
108 ATTRIBUTE_CATEGORY VARCHAR2(30),
109 ATTRIBUTE1 VARCHAR2(150),
110 ATTRIBUTE2 VARCHAR2(150),
111 ATTRIBUTE3 VARCHAR2(150),
112 ATTRIBUTE4 VARCHAR2(150),
113 ATTRIBUTE5 VARCHAR2(150),
114 ATTRIBUTE6 VARCHAR2(150),
115 ATTRIBUTE7 VARCHAR2(150),
116 ATTRIBUTE8 VARCHAR2(150),
117 ATTRIBUTE9 VARCHAR2(150),
118 ATTRIBUTE10 VARCHAR2(150),
119 ATTRIBUTE11 VARCHAR2(150),
120 ATTRIBUTE12 VARCHAR2(150),
121 ATTRIBUTE13 VARCHAR2(150),
122 ATTRIBUTE14 VARCHAR2(150),
123 ATTRIBUTE15 VARCHAR2(150)
124 );
125
126 ----------------------------------------------
127 -- Define Table Type for records structures --
128 ----------------------------------------------
129 TYPE Unit_Effectivity_Tbl_Type IS TABLE OF Unit_Effectivity_Rec_Type INDEX BY BINARY_INTEGER;
130
131 TYPE Unit_Threshold_Tbl_Type IS TABLE OF Unit_Threshold_Rec_Type INDEX BY BINARY_INTEGER;
132
133 TYPE Unit_Accomplish_Tbl_Type IS TABLE OF Unit_Accomplish_Rec_Type INDEX BY BINARY_INTEGER;
134
135 ------------------------
136 -- Declare Procedures --
137 ------------------------
138
139 -- Start of Comments --
140 -- Procedure name : Process_UnitEffectivity
141 -- Type : Private
142 -- Function : Manages Create/Modify/Delete operations of applicable maintenance
143 -- requirements on a unit.
144 -- Pre-reqs :
145 -- Parameters :
146 --
147 -- Standard IN Parameters :
148 -- p_api_version IN NUMBER Required
149 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
150 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
151 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
152 -- p_default IN VARCHAR2 Default FND_API.G_TRUE
153 -- Based on this flag, the API will set the default attributes.
154 -- p_module_type In VARCHAR2 Default NULL
155 -- This will be null.
156 -- Standard OUT Parameters :
157 -- x_return_status OUT VARCHAR2 Required
158 -- x_msg_count OUT NUMBER Required
159 -- x_msg_data OUT VARCHAR2 Required
160 --
161 -- Process_UnitEffectivity Parameters :
162 -- If no input parameters are passed, then effectivity will be built for all units.
163 -- If either p_mr_header_id OR p_mr_title and p_mr_version_number are passed, then effectivity
164 -- will be built for all units having this maintenance requirement; p_mr_header_id being the unique
165 -- identifier of a maintenance requirement.
166 -- If either p_csi_item_instance_id OR p_csi_instance_number are passed, then effectivity
167 -- will be built for the unit this item instance belongs to.
168 -- If either p_unit_name OR p_unit_config_header_id are passed, then effectivity will be
169 -- built for the unit configuration.
170 --
171
172 PROCEDURE Process_UnitEffectivity (
173 p_api_version IN NUMBER,
174 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
175 p_commit IN VARCHAR2 := FND_API.G_FALSE,
176 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
177 p_default IN VARCHAR2 := FND_API.G_TRUE,
178 p_module_type IN VARCHAR2 := NULL,
179 x_return_status OUT NOCOPY VARCHAR2,
180 x_msg_count OUT NOCOPY NUMBER,
181 x_msg_data OUT NOCOPY VARCHAR2,
182 p_mr_header_id IN NUMBER := NULL,
183 p_mr_title IN VARCHAR2 := NULL,
184 p_mr_version_number IN NUMBER := NULL,
185 p_unit_config_header_id IN NUMBER := NULL,
186 p_unit_name IN VARCHAR2 := NULL,
187 p_csi_item_instance_id IN NUMBER := NULL,
188 p_csi_instance_number IN VARCHAR2 := NULL
189
190 );
191
192
193 -- Start of Comments --
194 -- Procedure name : Build_UnitEffectivity
195 -- Type : Private
196 -- Function : This procedure will build unit and item effectivity and commit. Build_UnitEffectivity will commit at a unit level. If the
197 -- unit has any errors, then rollback will be performed for that unit only.
198 --
199 -- Pre-reqs :
200 -- Parameters :
201 --
202 -- Standard OUT Parameters :
203 -- x_return_status OUT VARCHAR2 Required
204 -- x_msg_count OUT NUMBER Required
205 -- x_msg_data OUT VARCHAR2 Required
206 --
207 -- Parameters:
208 -- If no input parameters are passed, then effectivity will be built for all units.
209 -- If either p_mr_header_id OR p_mr_title and p_mr_version_number are passed, then effectivity
210 -- will be built for all units having this maintenance requirement; p_mr_header_id being
211 -- the unique
212 -- identifier of a maintenance requirement.
213 -- If either p_csi_item_instance_id OR p_csi_instance_number are passed, then effectivity
214 -- will be built for the unit this item instance belongs to.
215 -- If either p_unit_name OR p_unit_config_header_id are passed, then effectivity will be
216 -- built for the unit configuration.
217 --
218 -- Details:
219 -- p_mr_header_id is the mr_header_id.
220 -- p_mr_title and p_mr_version_number will be used to resolve VALUE to ID conversion
221 -- for p_mr_header_id.
222 -- p_unit_config_header_id is the unit configuration ID.
223 -- p_unit_name will be used to resolve VALUE to ID conversion for p_unit_config_header_id.
224 -- p_csi_item_instance_id is the instance_id from csi_item_instances.
225 -- p_csi_instance_number will be used to resolve VALUE to ID conversion for
226 -- p_csi_item_instance_id.
227 -- p_commit will always be true; so the caller need not do an explicit commit.
228 -- Build_UnitEffectivity will commit at a unit level. If the unit has any errors,
229 -- then rollback will be performed for that unit only.
230 -- p_concurrent_flag will be 'Y' if this procedure is called by the concurrent program else it
231 -- will be N. Default is N.
232 --
233 PROCEDURE Build_UnitEffectivity (
234 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
235 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
236 p_default IN VARCHAR2 := FND_API.G_TRUE,
237 p_module_type IN VARCHAR2 := NULL,
238 x_return_status OUT NOCOPY VARCHAR2,
239 x_msg_count OUT NOCOPY NUMBER,
240 x_msg_data OUT NOCOPY VARCHAR2,
241 p_mr_header_id IN NUMBER := NULL,
242 p_mr_title IN VARCHAR2 := NULL,
243 p_mr_version_number IN NUMBER := NULL,
244 p_unit_config_header_id IN NUMBER := NULL,
245 p_unit_name IN VARCHAR2 := NULL,
246 p_csi_item_instance_id IN NUMBER := NULL,
247 p_csi_instance_number IN VARCHAR2 := NULL,
248 -- Tamal: Bug #4207212, #4114368 Begin
249 p_contract_number IN VARCHAR2 := NULL,
250 p_contract_modifier IN VARCHAR2 := NULL,
251 -- Tamal: Bug #4207212, #4114368 End
252 p_concurrent_flag IN VARCHAR2 := 'N',
253 -- sracha: Added parameter for number of workers to fix perf issue bug# 6893404
254 p_num_of_workers IN NUMBER := 1,
255 p_mtl_category_id IN NUMBER := NULL,
256 p_process_option IN VARCHAR2 := NULL,
257 -- JKJain, NR Analysis and Forecasting
258 p_simulation_plan_id IN NUMBER := NULL
259 );
260
261 -- JKJain, NR Analysis and Forecasting
262 -- Start of Comments --
263 -- Procedure name : Build_SimulationPlan_UE
264 -- Type : Private
265 -- Function : This procedure will build simulation plan's unit and commit.
266 --
267 PROCEDURE Build_SimulationPlan_UE (
268 errbuf OUT NOCOPY VARCHAR2,
269 retcode OUT NOCOPY NUMBER,
270 p_simulation_plan_id IN NUMBER,
271 p_unit_config_header_id IN NUMBER := NULL,
272 p_num_of_workers IN NUMBER := 1
273 );
274
275 -- Start of Comments --
276 -- Procedure name : Capture_MR_Updates
277 -- Type : Private
278 -- Function : For a given set of instances, will record their statuses with either
279 -- accomplishment date or deferred-next due date or termination date with
280 -- their corresponding counter and counter values.
281 -- Pre-reqs :
282 -- Parameters :
283 --
284 -- Standard IN Parameters :
285 -- p_api_version IN NUMBER Required
286 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
287 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
288 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
289 -- p_default IN VARCHAR2 Default FND_API.G_TRUE
293 -- x_return_status OUT VARCHAR2 Required
290 -- p_module_type IN VARCHAR2 Default NULL.
291 --
292 -- Standard OUT Parameters :
294 -- x_msg_count OUT NUMBER Required
295 -- x_msg_data OUT VARCHAR2 Required
296 --
297 -- Capture MR Update Parameters:
298 -- p_unit_Effectivity_tbl IN Unit_Effectivity_tbl_type Required
299 -- List of all unit effectivities whose status, due or accomplished dates
300 -- and counter values need to be captured
301 -- p_x_Unit_Threshold_tbl IN OUT Unit_Threshold_tbl_type Required
302 -- List of all thresholds (counters and counter values) when a MR becomes due
303 -- p_x_Unit_Accomplish_tbl IN OUT Unit_Accomplish_tbl_type Required
304 -- List of all counters and corresponding counter values when the MR was last accomplished
305 --
306 --
307 -- Version :
308 -- Initial Version 1.0
309 --
310 -- End of Comments.
311
312 PROCEDURE Capture_MR_Updates
313 (
314 p_api_version IN NUMBER,
315 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
316 p_commit IN VARCHAR2 := FND_API.G_FALSE,
317 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
318 p_default IN VARCHAR2 := FND_API.G_TRUE,
319 p_module_type IN VARCHAR2 := NULL,
320 p_unit_Effectivity_tbl IN AHL_UMP_UNITMAINT_PVT.Unit_Effectivity_tbl_type,
321 p_x_unit_threshold_tbl IN OUT NOCOPY AHL_UMP_UNITMAINT_PVT.Unit_Threshold_tbl_type,
322 p_x_unit_accomplish_tbl IN OUT NOCOPY AHL_UMP_UNITMAINT_PVT.Unit_Accomplish_tbl_type,
323 x_return_status OUT NOCOPY VARCHAR2,
324 x_msg_count OUT NOCOPY NUMBER,
325 x_msg_data OUT NOCOPY VARCHAR2
326 );
327
328 -- Start of Comments --
329 -- Procedure name : Validate_For_Initialize
330 -- Type : Private
331 -- Function : For a given unit effectivity id, determined if it can be initialized.
332 -- Pre-reqs :
333 -- Parameters :
334 --
335 -- Standard IN Parameters :
336 -- p_api_version IN NUMBER Required
337 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
338 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
339 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
340 -- p_default IN VARCHAR2 Default FND_API.G_TRUE
341 -- p_module_type IN VARCHAR2 Default NULL.
342 --
343 -- Standard OUT Parameters :
344 -- x_return_status OUT VARCHAR2 Required
345 -- x_msg_count OUT NUMBER Required
346 -- x_msg_data OUT VARCHAR2 Required
347 --
348 -- Validate_For_Initialize Parameters:
349 -- p_unit_effectivity_id IN Id of Unit Effectivity to be initialized Required
350 --
351 -- Version :
352 -- Initial Version 1.0
353 --
354 -- End of Comments.
355
356 PROCEDURE Validate_For_Initialize
357 (
358 p_api_version IN NUMBER,
359 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
360 -- This parameter does not make any sense in this method. Added for standard compliance
361 p_commit IN VARCHAR2 := FND_API.G_FALSE,
362 -- This parameter does not make any sense in this method. Added for standard compliance
363 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
364 -- This parameter does not make any sense in this method. Added for standard compliance
365 p_default IN VARCHAR2 := FND_API.G_TRUE,
366 -- This parameter does not make any sense in this method. Added for standard compliance
367 p_module_type IN VARCHAR2 := NULL,
368 p_unit_effectivity_id IN NUMBER,
369 x_return_status OUT NOCOPY VARCHAR2,
370 x_msg_count OUT NOCOPY NUMBER,
371 x_msg_data OUT NOCOPY VARCHAR2
372 );
373
374
375
376 ----------------------------------------
377 -- Declare Procedures for Terminate MR Instances --
378 ----------------------------------------
379 -- Start of Comments --
380 -- Procedure name : Terminate_MR_Instances
381 -- Type : Public
382 -- Function : Terminate MR Instances
383 -- Pre-reqs :
384 -- Parameters :
385 --
386 -- Standard IN Parameters :
387 -- p_api_version IN NUMBER Required
388 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
389 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
390 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
391 -- Standard OUT Parameters :
392 -- x_return_status OUT VARCHAR2 Required
393 -- x_msg_count OUT NUMBER Required
394 -- x_msg_data OUT VARCHAR2 Required
395 --
396 -- p_module_type IN VARCHAR2 Required.
397 -- This parameter indicates the front-end form interface. The default value is null. If the value
398 -- is JSP, then this API clears out all id columns and validations are done using the values;based
399 -- on which the Id's are populated.
400 --
401 -- Terminate_MR_Instances Parameters :
402 -- p_old_mr_header_id IN NUMBER,
403 -- p_old_mr_title IN VARCHAR2,
404 -- p_old_version_number IN NUMBER,
405 -- p_new_mr_header_id IN NUMBER,
406 -- p_new_mr_title IN VARCHAR2,
407 -- p_new_version_number IN NUMBER,
408
409 --
410 -- Version :
411 -- Initial Version 1.0
412 --
413 -- End of Comments.
414 PROCEDURE Terminate_MR_Instances(
415 p_api_version IN NUMBER := 1.0,
416 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
417 p_commit IN VARCHAR2 := FND_API.G_FALSE,
418 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
419 p_default IN VARCHAR2 := FND_API.G_TRUE,
420 p_module_type IN VARCHAR2 := NULL,
421 p_old_mr_header_id IN NUMBER,
422 p_old_mr_title IN VARCHAR2,
423 p_old_version_number IN NUMBER,
424 p_new_mr_header_id IN NUMBER := NULL,
425 p_new_mr_title IN VARCHAR2 := NULL,
426 p_new_version_number IN NUMBER := NULL,
427 x_return_status OUT NOCOPY VARCHAR2,
428 x_msg_count OUT NOCOPY NUMBER,
429 x_msg_data OUT NOCOPY VARCHAR2 );
430
431 -- Tamal: Bug #4207212, #4114368 Begin
432 PROCEDURE Building_PM_Unit_Effectivities (
433 errbuf OUT NOCOPY VARCHAR2,
434 retcode OUT NOCOPY NUMBER,
435 p_api_version IN NUMBER,
436 p_mr_header_id IN NUMBER := NULL,
437 p_csi_item_instance_id IN NUMBER := NULL,
438 p_contract_number IN VARCHAR2 := NULL,
439 p_contract_modifier IN VARCHAR2 := NULL,
440 p_num_of_workers IN NUMBER := 1
441 );
442 -- Tamal: Bug #4207212, #4114368 End
443
444 -- Sally's Changes!!!!!!! Bug #: 10137623 Begin
445 PROCEDURE Building_AHL_Unit_Effectivity (
446 errbuf OUT NOCOPY VARCHAR2,
447 retcode OUT NOCOPY NUMBER,
448 p_mr_id IN NUMBER := NULL,
449 p_unit_id IN NUMBER := NULL,
450 p_csi_instance_id IN NUMBER := NULL,
451 p_mtl_category_id IN NUMBER := NULL,
452 p_process_option IN VARCHAR2 := NULL,
453 p_num_of_workers IN NUMBER := 1
454 );
455 -- Sally's Changes!!!!! End
456
457 -- SATHAPLI::Bug# 6504069, 26-Mar-2008
458 -- API to build the unit effectivities for all the attached units for a given PC.
459 -- The API is configured as the concurrent program AHLPCUEFF.
460 PROCEDURE Building_PC_Unit_Effectivities (
461 errbuf OUT NOCOPY VARCHAR2,
462 retcode OUT NOCOPY NUMBER,
463 p_api_version IN NUMBER,
464 p_pc_header_id IN NUMBER
465 );
466
467 ------------------------------------------------------------------------------------------------
468 -- API added for the concurrent program "Process Terminated Maintenance Requirements".
469 -- This API is to be used with Concurrent program.
470 -- Bug # 8570734
471 ------------------------------------------------------------------------------------------------
472 PROCEDURE process_terminated_MRs (
473 errbuf OUT NOCOPY VARCHAR2,
474 retcode OUT NOCOPY NUMBER,
475 p_api_version IN NUMBER
476 );
477
478 End AHL_UMP_UNITMAINT_PVT;
[Home] [Help]