1 PACKAGE EAM_PMDef_Pub AS
2 /* $Header: EAMPPMDS.pls 120.8 2007/11/26 12:11:24 rnandyal ship $*/
3 /*#
4 * This package is used for the INSERT / UPDATE of PM Schedules.
5 * It defines 2 key procedures create_pm_def, update_pm_def
6 * which first validates and massages the IN parameters
7 * and then carries out the respective operations.
8 * @rep:scope public
9 * @rep:product EAM
10 * @rep:lifecycle active
11 * @rep:displayname Preventive Maintenance Schedule
12 * @rep:category BUSINESS_ENTITY EAM_PM_SCHEDULE
13 */
14
15
16 Type PM_Scheduling_Rec_Type is RECORD
17 (
18 PM_SCHEDULE_ID NUMBER,
19 ACTIVITY_ASSOCIATION_ID NUMBER,
20 NON_SCHEDULED_FLAG VARCHAR2(1),
21 FROM_EFFECTIVE_DATE DATE,
22 TO_EFFECTIVE_DATE DATE,
23 RESCHEDULING_POINT NUMBER,
24 LEAD_TIME NUMBER,
25 ATTRIBUTE_CATEGORY VARCHAR2(30),
26 ATTRIBUTE1 VARCHAR2(150),
27 ATTRIBUTE2 VARCHAR2(150),
28 ATTRIBUTE3 VARCHAR2(150),
29 ATTRIBUTE4 VARCHAR2(150),
30 ATTRIBUTE5 VARCHAR2(150),
31 ATTRIBUTE6 VARCHAR2(150),
32 ATTRIBUTE7 VARCHAR2(150),
33 ATTRIBUTE8 VARCHAR2(150),
34 ATTRIBUTE9 VARCHAR2(150),
35 ATTRIBUTE10 VARCHAR2(150),
36 ATTRIBUTE11 VARCHAR2(150),
37 ATTRIBUTE12 VARCHAR2(150),
38 ATTRIBUTE13 VARCHAR2(150),
39 ATTRIBUTE14 VARCHAR2(150),
40 ATTRIBUTE15 VARCHAR2(150),
41 DAY_TOLERANCE NUMBER,
42 SOURCE_CODE VARCHAR2(30),
43 SOURCE_LINE VARCHAR2(30),
44 DEFAULT_IMPLEMENT VARCHAR2(1),
45 WHICHEVER_FIRST VARCHAR2(1),
46 INCLUDE_MANUAL VARCHAR2(1),
47 SET_NAME_ID NUMBER,
48 SCHEDULING_METHOD_CODE NUMBER ,
49 TYPE_CODE NUMBER,
50 NEXT_SERVICE_START_DATE date,
51 NEXT_SERVICE_END_DATE date,
52 SOURCE_TMPL_ID NUMBER,
53 AUTO_INSTANTIATION_FLAG VARCHAR2(1),
54 NAME VARCHAR2(50),
55 TMPL_FLAG VARCHAR2(1),
56 GENERATE_WO_STATUS NUMBER,
57 INTERVAL_PER_CYCLE NUMBER,
58 CURRENT_CYCLE NUMBER,
59 CURRENT_SEQ NUMBER,
60 CURRENT_WO_SEQ NUMBER,
61 BASE_DATE DATE,
62 BASE_READING NUMBER,
63 EAM_LAST_CYCLIC_ACT NUMBER,
64 MAINTENANCE_OBJECT_ID NUMBER,
65 MAINTENANCE_OBJECT_TYPE NUMBER,
66 LAST_REVIEWED_DATE Date,
67 Last_reviewed_by NUMBER,
68 GENERATE_NEXT_WORK_ORDER VARCHAR2(1)
69 );
70
71 TYPE pm_activities_grp_rec_type is RECORD
72 (
73 PM_SCHEDULE_ID NUMBER,
74 ACTIVITY_ASSOCIATION_ID NUMBER,
75 INTERVAL_MULTIPLE NUMBER,
76 ALLOW_REPEAT_IN_CYCLE VARCHAR2(1),
77 DAY_TOLERANCE NUMBER,
78 NEXT_SERVICE_START_DATE DATE,
79 NEXT_SERVICE_END_DATE DATE
80 );
81
82 TYPE pm_activities_grp_tbl_type IS TABLE OF pm_activities_grp_rec_type index by binary_integer;
83
84 TYPE pm_rule_rec_type is RECORD
85 (
86 rule_id number,
87 PM_SCHEDULE_ID NUMBER,
88 RULE_TYPE NUMBER,
89 DAY_INTERVAL NUMBER,
90 METER_ID NUMBER,
91 RUNTIME_INTERVAL NUMBER,
92 LAST_SERVICE_READING NUMBER,
93 EFFECTIVE_READING_FROM NUMBER,
94 EFFECTIVE_READING_TO NUMBER,
95 EFFECTIVE_DATE_FROM DATE,
96 EFFECTIVE_DATE_TO DATE,
97 LIST_DATE DATE,
98 LIST_DATE_DESC VARCHAR2(50)
99
100 );
101
102
103 TYPE pm_rule_tbl_type IS TABLE OF pm_rule_rec_type index by binary_integer;
104
105 TYPE pm_date_rec_type is RECORD
106 (
107 index1 number,
108 date1 date,
109 other number
110 );
111
112
113
114 TYPE pm_date_tbl_type is TABLE of pm_date_rec_type index by binary_integer;
115
116 TYPE pm_num_rec_type is RECORD
117 (
118 index1 number,
119 num1 number,
120 other number
121 );
122
123
124 TYPE pm_num_tbl_type is TABLE of pm_num_rec_type index by binary_integer;
125
126
127 /* This method copies an existing PM definition, including the header and all the rules. */
128
129 PROCEDURE instantiate_PM_def
130 (
131 p_pm_schedule_id IN NUMBER,
132 p_activity_assoc_id IN NUMBER,
133 x_new_pm_schedule_id OUT NOCOPY NUMBER, -- this is the pm_schedule_id of the newly copied pm schedule
134 x_return_status OUT NOCOPY VARCHAR2,
135 x_msg_count OUT NOCOPY NUMBER,
136 x_msg_data OUT NOCOPY VARCHAR2
137 );
138
139
140 /* This procedure instantiates a set of PM definitions for all asset_association_id's in the activity_assoc_id_tbl table. */
141
142 PROCEDURE instantiate_PM_Defs
143 (
144 p_api_version IN NUMBER ,
145 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
146 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
147 p_validation_level IN NUMBER :=
148 FND_API.G_VALID_LEVEL_FULL ,
149 x_return_status OUT NOCOPY VARCHAR2 ,
150 x_msg_count OUT NOCOPY NUMBER ,
151 x_msg_data OUT NOCOPY VARCHAR2 ,
152 p_activity_assoc_id_tbl IN EAM_ObjectInstantiation_PUB.Association_Id_Tbl_Type
153
154 );
155
156
157
158 /* This procedure creates a new PM definition, including the header and all rules. */
159 /*#
160 * This procedure is used to insert records in EAM_PM_SCHEDULINGS
161 * It is used to create Preventive Maintenance Schedule.
162 * @param p_api_version Version of the API
163 * @param p_init_msg_list Flag to indicate initialization of message list
164 * @param p_commit Flag to indicate whether API should commit changes
165 * @param p_validation_level Validation Level of the API
166 * @param x_return_status Return status of the procedure call
167 * @param x_msg_count Count of the return messages that API returns
168 * @param x_msg_data The collection of the messages.
169 * @param p_pm_schedule_rec This is a PL QL record type, which holds the master level data of Preventive Maintenance schedule definition
170 * @param p_pm_day_interval_rules_tbl This is PL SQL record type, which holds the child level data of Day Interval Rules of a Preventive Maintenance schedule definition
171 * @param p_pm_runtime_rules_tbl This is PL SQL record type, which holds the child level data of Runtime Interval Rules of a Preventive Maintenance schedule definition
172 * @param p_pm_list_date_rules_tbl This is PL SQL record type, which holds the child level data of the simple list date based rules of a Preventive Maintenance schedule definition
173 * @param x_new_pm_schedule_id The unique identifier of newly created Preventive Maintenance Schedule
174 * @rep:scope public
175 * @rep:displayname Create Preventive Maintenance Schedule
176 */
177
178
179 PROCEDURE create_PM_def
180 ( p_api_version IN NUMBER ,
181 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
182 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
183 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
184 x_return_status OUT NOCOPY VARCHAR2 ,
185 x_msg_count OUT NOCOPY NUMBER ,
186 x_msg_data OUT NOCOPY VARCHAR2 ,
187 p_pm_schedule_rec IN pm_scheduling_rec_type,
188 p_pm_activities_tbl IN pm_activities_grp_tbl_type,
189 p_pm_day_interval_rules_tbl IN pm_rule_tbl_type ,
190 p_pm_runtime_rules_tbl IN pm_rule_tbl_type ,
191 p_pm_list_date_rules_tbl IN pm_rule_tbl_type ,
192 x_new_pm_schedule_id OUT NOCOPY NUMBER -- this is the pm_schedule_id of the newly created pm schedule
193 );
194
195
196
197 /*#
198 * This procedure is used to update the existing records in EAM_PM_SCHEDULINGS.
199 * It is used to update Preventive Maintenance Schedule.
200 * @param p_api_version Version of the API
201 * @param p_init_msg_list Flag to indicate initialization of message list
202 * @param p_commit Flag to indicate whether API should commit changes
203 * @param p_validation_level Validation Level of the API
204 * @param x_return_status Return status of the procedure call
205 * @param x_msg_count Count of the return messages that API returns
206 * @param x_msg_data The collection of the messages.
207 * @param p_pm_schedule_rec This is a PL QL record type, which holds the master level data of Preventive Maintenance schedule definition
208 * @param p_pm_day_interval_rules_tbl This is PL SQL record type, which holds the child level data of Day Interval Rules of a Preventive Maintenance schedule definition
209 * @param p_pm_runtime_rules_tbl This is PL SQL record type, which holds the child level data of Runtime Interval Rules of a Preventive Maintenance schedule definition
210 * @param p_pm_list_date_rules_tbl This is PL SQL record type, which holds the child level data of the simple list date based rules of a Preventive Maintenance schedule definition
211 * @rep:scope public
212 * @rep:displayname Update Preventive Maintenance Schedule
213 */
214
215 procedure update_pm_def
216 ( p_api_version IN NUMBER ,
217 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
218 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
219 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
220 x_return_status OUT NOCOPY VARCHAR2 ,
221 x_msg_count OUT NOCOPY NUMBER ,
222 x_msg_data OUT NOCOPY VARCHAR2 ,
223 --p_pm_schedule_id IN NUMBER,
224 p_pm_schedule_rec IN pm_scheduling_rec_type:=null,
225 p_pm_activities_tbl IN pm_activities_grp_tbl_type,
226 p_pm_day_interval_rules_tbl IN pm_rule_tbl_type,
227 p_pm_runtime_rules_tbl IN pm_rule_tbl_type,
228 p_pm_list_date_rules_tbl IN pm_rule_tbl_type
229 );
230
231 function validate_pm_header
232 (
233 p_pm_schedule_rec IN pm_scheduling_rec_type,
234 x_reason_failed OUT NOCOPY varchar2
235 ) return BOOLEAN;
236
237
238 function validate_pm_day_interval_rule
239 (
240 p_pm_rule_rec IN pm_rule_rec_type,
241 x_reason_failed OUT NOCOPY varchar2
242 ) return BOOLEAN;
243
244
245 function validate_pm_runtime_rule
246 (
247 p_pm_rule_rec IN pm_rule_rec_type,
248 x_reason_failed OUT NOCOPY varchar2
249 ) return BOOLEAN;
250
251
252 function validate_pm_list_date
253 (
254 p_pm_rule_rec IN pm_rule_rec_type,
255 x_reason_failed OUT NOCOPY varchar2
256 ) return BOOLEAN;
257
258
259 function validate_pm_day_interval_rules
260 (
261 p_pm_rules_tbl IN pm_rule_tbl_type,
262 x_reason_failed OUT NOCOPY varchar2
263 ) return BOOLEAN;
264
265
266 function validate_pm_runtime_rules
267 (
268 p_pm_rules_tbl IN pm_rule_tbl_type,
269 x_reason_failed OUT NOCOPY varchar2
270 ) return BOOLEAN;
271
272
273 function validate_pm_list_date_rules
274 (
275 p_pm_rules_tbl IN pm_rule_tbl_type,
276 x_reason_failed OUT NOCOPY varchar2
277 ) return BOOLEAN;
278
279
280 function validate_pm_header_and_rules
281 (
282 p_pm_schedule_rec IN pm_scheduling_rec_type,
283 p_pm_day_interval_rules_tbl IN pm_rule_tbl_type,
284 p_pm_runtime_rules_tbl IN pm_rule_tbl_type,
285 p_pm_list_date_rules_tbl IN pm_rule_tbl_type,
286 x_reason_failed OUT NOCOPY varchar2
287 ) return BOOLEAN;
288
289 function validate_pm_activity
290 (
291 p_pm_activity_grp_rec IN pm_activities_grp_rec_type,
292 p_pm_runtime_rules_tbl IN pm_rule_tbl_type,
293 p_pm_schedule_rec IN PM_Scheduling_Rec_Type,
294 x_reason_failed OUT NOCOPY varchar2,
295 x_message OUT NOCOPY varchar2,
296 x_activities OUT NOCOPY varchar2
297
298 ) return BOOLEAN;
299
300 function validate_pm_activity
301 (
302 p_pm_activity_grp_rec IN pm_activities_grp_rec_type,
303 p_pm_schedule_rec IN PM_Scheduling_Rec_Type,
304 x_reason_failed OUT NOCOPY varchar2
305 ) return BOOLEAN;
306
307 function validate_pm_activities
308 (
309 p_pm_activities_grp_tbl IN pm_activities_grp_tbl_type,
310 p_pm_runtime_rules_tbl IN pm_rule_tbl_type,
311 p_pm_schedule_rec IN pm_scheduling_rec_type,
312 x_reason_failed OUT NOCOPY varchar2,
313 x_message OUT NOCOPY varchar2,
314 x_activities OUT NOCOPY varchar2
315 ) return BOOLEAN;
316
317 procedure sort_table_by_date
318 (
319 p_date_table IN pm_date_tbl_type,
320 p_num_rows IN number,
321 x_sorted_date_table OUT NOCOPY pm_date_tbl_type
322 );
323
324
325 procedure sort_table_by_number
326 (
327 p_num_table IN pm_num_tbl_type,
328 p_num_rows IN number,
329 x_sorted_num_table OUT NOCOPY pm_num_tbl_type
330 );
331
332
333 procedure merge_rules
334 (p_rules_tbl1 IN pm_rule_tbl_type,
335 p_rules_tbl2 IN pm_rule_tbl_type,
336 x_merged_rules_tbl OUT NOCOPY pm_rule_tbl_type);
337
338 procedure get_pm_last_activity
339 ( p_pm_schedule_id IN NUMBER,
340 p_activity_association_id OUT NOCOPY NUMBER,
341 x_return_status OUT NOCOPY VARCHAR2,
342 x_msg_count OUT NOCOPY NUMBER ,
343 x_msg_data OUT NOCOPY VARCHAR2
344 );
345
346 /*#
347 * This procedure is used to update the eam_last_cyclic_act in the eam_pm_schedulings table. This is called from PM schedule form and Work Order completion
348 * @param p_api_version Version of the API
349 * @param p_init_msg_list Flag to indicate initialization of message list
350 * @param p_commit Flag to indicate whether API should commit changes
351 * @param p_validation_level Validation Level of the API
352 * @param x_return_status Return status of the procedure call
353 * @param x_msg_count Count of the return messages that API returns
354 * @param x_msg_data The collection of the messages.
355 * @param p_pm_schedule_id This is a number parameter, which is the pm schedule id for which the eam_last_cyclic_act should be updated
356 * @rep:scope public
357 * @rep:displayname Update Preventive Maintenance Schedule
358 */
359
360 procedure update_pm_last_cyclic_act
361 ( p_api_version IN NUMBER ,
362 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
363 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
364 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
365 x_return_status OUT NOCOPY VARCHAR2 ,
366 x_msg_count OUT NOCOPY NUMBER ,
367 x_msg_data OUT NOCOPY VARCHAR2 ,
368 p_pm_schedule_id IN NUMBER
369 );
370
371 /*#
372 * This procedure is used to update the last service reading in eam_pm_scheduling_rules table. The last service reading is of the last cyclic activity.
373 * This procedure is called from PM Schedule form and from Work Order completion
374 * @param p_api_version Version of the API
375 * @param p_init_msg_list Flag to indicate initialization of message list
376 * @param p_commit Flag to indicate whether API should commit changes
377 * @param p_validation_level Validation Level of the API
378 * @param x_return_status Return status of the procedure call
379 * @param x_msg_count Count of the return messages that API returns
380 * @param x_msg_data The collection of the messages.
381 * @param p_pm_schedule_id This is a number parameter, which is the pm schedule id for which the last service reading should be updated
382 * @rep:scope public
383 * @rep:displayname Update Preventive Maintenance Schedule
384 */
385
386 procedure update_pm_last_service_reading
387 ( p_api_version IN NUMBER ,
388 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
389 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
390 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
391 x_return_status OUT NOCOPY VARCHAR2 ,
392 x_msg_count OUT NOCOPY NUMBER ,
393 x_msg_data OUT NOCOPY VARCHAR2 ,
394 p_pm_schedule_id IN NUMBER
395 );
396
397 END;
398
[Home] [Help]