1 PACKAGE AHL_UMP_UNITMAINT_PVT AS
2 /* $Header: AHLVUMXS.pls 120.1.12010000.2 2008/12/27 00:54:32 sracha 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(30),
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(30),
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 );
258
259 -- Start of Comments --
260 -- Procedure name : Capture_MR_Updates
261 -- Type : Private
262 -- Function : For a given set of instances, will record their statuses with either
263 -- accomplishment date or deferred-next due date or termination date with
264 -- their corresponding counter and counter values.
265 -- Pre-reqs :
266 -- Parameters :
267 --
268 -- Standard IN Parameters :
269 -- p_api_version IN NUMBER Required
270 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
271 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
272 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
273 -- p_default IN VARCHAR2 Default FND_API.G_TRUE
274 -- p_module_type IN VARCHAR2 Default NULL.
275 --
276 -- Standard OUT Parameters :
277 -- x_return_status OUT VARCHAR2 Required
278 -- x_msg_count OUT NUMBER Required
279 -- x_msg_data OUT VARCHAR2 Required
280 --
281 -- Capture MR Update Parameters:
282 -- p_unit_Effectivity_tbl IN Unit_Effectivity_tbl_type Required
283 -- List of all unit effectivities whose status, due or accomplished dates
284 -- and counter values need to be captured
285 -- p_x_Unit_Threshold_tbl IN OUT Unit_Threshold_tbl_type Required
286 -- List of all thresholds (counters and counter values) when a MR becomes due
287 -- p_x_Unit_Accomplish_tbl IN OUT Unit_Accomplish_tbl_type Required
288 -- List of all counters and corresponding counter values when the MR was last accomplished
289 --
290 --
291 -- Version :
292 -- Initial Version 1.0
293 --
294 -- End of Comments.
295
296 PROCEDURE Capture_MR_Updates
297 (
298 p_api_version IN NUMBER,
299 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
300 p_commit IN VARCHAR2 := FND_API.G_FALSE,
301 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
302 p_default IN VARCHAR2 := FND_API.G_TRUE,
303 p_module_type IN VARCHAR2 := NULL,
304 p_unit_Effectivity_tbl IN AHL_UMP_UNITMAINT_PVT.Unit_Effectivity_tbl_type,
305 p_x_unit_threshold_tbl IN OUT NOCOPY AHL_UMP_UNITMAINT_PVT.Unit_Threshold_tbl_type,
306 p_x_unit_accomplish_tbl IN OUT NOCOPY AHL_UMP_UNITMAINT_PVT.Unit_Accomplish_tbl_type,
307 x_return_status OUT NOCOPY VARCHAR2,
308 x_msg_count OUT NOCOPY NUMBER,
309 x_msg_data OUT NOCOPY VARCHAR2
310 );
311
312 -- Start of Comments --
313 -- Procedure name : Validate_For_Initialize
314 -- Type : Private
315 -- Function : For a given unit effectivity id, determined if it can be initialized.
316 -- Pre-reqs :
317 -- Parameters :
318 --
319 -- Standard IN Parameters :
323 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
320 -- p_api_version IN NUMBER Required
321 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
322 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
324 -- p_default IN VARCHAR2 Default FND_API.G_TRUE
325 -- p_module_type IN VARCHAR2 Default NULL.
326 --
327 -- Standard OUT Parameters :
328 -- x_return_status OUT VARCHAR2 Required
329 -- x_msg_count OUT NUMBER Required
330 -- x_msg_data OUT VARCHAR2 Required
331 --
332 -- Validate_For_Initialize Parameters:
333 -- p_unit_effectivity_id IN Id of Unit Effectivity to be initialized Required
334 --
335 -- Version :
336 -- Initial Version 1.0
337 --
338 -- End of Comments.
339
340 PROCEDURE Validate_For_Initialize
341 (
342 p_api_version IN NUMBER,
343 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
344 -- This parameter does not make any sense in this method. Added for standard compliance
345 p_commit IN VARCHAR2 := FND_API.G_FALSE,
346 -- This parameter does not make any sense in this method. Added for standard compliance
347 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
348 -- This parameter does not make any sense in this method. Added for standard compliance
349 p_default IN VARCHAR2 := FND_API.G_TRUE,
350 -- This parameter does not make any sense in this method. Added for standard compliance
351 p_module_type IN VARCHAR2 := NULL,
352 p_unit_effectivity_id IN NUMBER,
353 x_return_status OUT NOCOPY VARCHAR2,
354 x_msg_count OUT NOCOPY NUMBER,
355 x_msg_data OUT NOCOPY VARCHAR2
356 );
357
358
359
360 ----------------------------------------
361 -- Declare Procedures for Terminate MR Instances --
362 ----------------------------------------
363 -- Start of Comments --
364 -- Procedure name : Terminate_MR_Instances
365 -- Type : Public
366 -- Function : Terminate MR Instances
367 -- Pre-reqs :
368 -- Parameters :
369 --
370 -- Standard IN Parameters :
371 -- p_api_version IN NUMBER Required
372 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
373 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
374 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
375 -- Standard OUT Parameters :
376 -- x_return_status OUT VARCHAR2 Required
377 -- x_msg_count OUT NUMBER Required
378 -- x_msg_data OUT VARCHAR2 Required
379 --
380 -- p_module_type IN VARCHAR2 Required.
381 -- This parameter indicates the front-end form interface. The default value is null. If the value
382 -- is JSP, then this API clears out all id columns and validations are done using the values;based
383 -- on which the Id's are populated.
384 --
385 -- Terminate_MR_Instances Parameters :
386 -- p_old_mr_header_id IN NUMBER,
387 -- p_old_mr_title IN VARCHAR2,
388 -- p_old_version_number IN NUMBER,
389 -- p_new_mr_header_id IN NUMBER,
390 -- p_new_mr_title IN VARCHAR2,
391 -- p_new_version_number IN NUMBER,
392
393 --
394 -- Version :
395 -- Initial Version 1.0
396 --
397 -- End of Comments.
398 PROCEDURE Terminate_MR_Instances(
399 p_api_version IN NUMBER := 1.0,
400 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
401 p_commit IN VARCHAR2 := FND_API.G_FALSE,
402 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
403 p_default IN VARCHAR2 := FND_API.G_TRUE,
404 p_module_type IN VARCHAR2 := NULL,
405 p_old_mr_header_id IN NUMBER,
406 p_old_mr_title IN VARCHAR2,
407 p_old_version_number IN NUMBER,
408 p_new_mr_header_id IN NUMBER := NULL,
409 p_new_mr_title IN VARCHAR2 := NULL,
410 p_new_version_number IN NUMBER := NULL,
411 x_return_status OUT NOCOPY VARCHAR2,
412 x_msg_count OUT NOCOPY NUMBER,
413 x_msg_data OUT NOCOPY VARCHAR2 );
414
415 -- Tamal: Bug #4207212, #4114368 Begin
416 PROCEDURE Building_PM_Unit_Effectivities (
417 errbuf OUT NOCOPY VARCHAR2,
418 retcode OUT NOCOPY NUMBER,
419 p_api_version IN NUMBER,
420 p_mr_header_id IN NUMBER := NULL,
421 p_csi_item_instance_id IN NUMBER := NULL,
422 p_contract_number IN VARCHAR2 := NULL,
423 p_contract_modifier IN VARCHAR2 := NULL,
424 p_num_of_workers IN NUMBER := 1
425 );
426 -- Tamal: Bug #4207212, #4114368 End
427
428 -- SATHAPLI::Bug# 6504069, 26-Mar-2008
429 -- API to build the unit effectivities for all the attached units for a given PC.
430 -- The API is configured as the concurrent program AHLPCUEFF.
431 PROCEDURE Building_PC_Unit_Effectivities (
432 errbuf OUT NOCOPY VARCHAR2,
433 retcode OUT NOCOPY NUMBER,
434 p_api_version IN NUMBER,
435 p_pc_header_id IN NUMBER
436 );
437
438 End AHL_UMP_UNITMAINT_PVT;
[Home] [Help]