1 PACKAGE EAM_PMDef_Pub AUTHID CURRENT_USER AS
2 /* $Header: EAMPPMDS.pls 120.8.12020000.2 2013/01/10 09:09:51 srkotika 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 PLANNER_MAINTENANCE NUMBER /*WO defaults ER*/
70 );
71
72 TYPE pm_activities_grp_rec_type is RECORD
73 (
74 PM_SCHEDULE_ID NUMBER,
75 ACTIVITY_ASSOCIATION_ID NUMBER,
76 INTERVAL_MULTIPLE NUMBER,
77 ALLOW_REPEAT_IN_CYCLE VARCHAR2(1),
78 DAY_TOLERANCE NUMBER,
79 NEXT_SERVICE_START_DATE DATE,
80 NEXT_SERVICE_END_DATE DATE
81 );
82
83 TYPE pm_activities_grp_tbl_type IS TABLE OF pm_activities_grp_rec_type index by binary_integer;
84
85 TYPE pm_rule_rec_type is RECORD
86 (
87 rule_id number,
88 PM_SCHEDULE_ID NUMBER,
89 RULE_TYPE NUMBER,
90 DAY_INTERVAL NUMBER,
91 METER_ID NUMBER,
92 RUNTIME_INTERVAL NUMBER,
93 LAST_SERVICE_READING NUMBER,
94 EFFECTIVE_READING_FROM NUMBER,
95 EFFECTIVE_READING_TO NUMBER,
96 EFFECTIVE_DATE_FROM DATE,
97 EFFECTIVE_DATE_TO DATE,
98 LIST_DATE DATE,
99 LIST_DATE_DESC VARCHAR2(50)
100
101 );
102
103
104 TYPE pm_rule_tbl_type IS TABLE OF pm_rule_rec_type index by binary_integer;
105
106 TYPE pm_date_rec_type is RECORD
107 (
108 index1 number,
109 date1 date,
110 other number
111 );
112
113
114
115 TYPE pm_date_tbl_type is TABLE of pm_date_rec_type index by binary_integer;
116
117 TYPE pm_num_rec_type is RECORD
118 (
119 index1 number,
120 num1 number,
121 other number
122 );
123
124
125 TYPE pm_num_tbl_type is TABLE of pm_num_rec_type index by binary_integer;
126
127
128 /* This method copies an existing PM definition, including the header and all the rules. */
129
130 PROCEDURE instantiate_PM_def
131 (
132 p_pm_schedule_id IN NUMBER,
133 p_activity_assoc_id IN NUMBER,
134 x_new_pm_schedule_id OUT NOCOPY NUMBER, -- this is the pm_schedule_id of the newly copied pm schedule
135 x_return_status OUT NOCOPY VARCHAR2,
136 x_msg_count OUT NOCOPY NUMBER,
137 x_msg_data OUT NOCOPY VARCHAR2
138 );
139
140
141 /* This procedure instantiates a set of PM definitions for all asset_association_id's in the activity_assoc_id_tbl table. */
142
143 PROCEDURE instantiate_PM_Defs
144 (
145 p_api_version IN NUMBER ,
146 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
147 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
148 p_validation_level IN NUMBER :=
149 FND_API.G_VALID_LEVEL_FULL ,
150 x_return_status OUT NOCOPY VARCHAR2 ,
151 x_msg_count OUT NOCOPY NUMBER ,
152 x_msg_data OUT NOCOPY VARCHAR2 ,
153 p_activity_assoc_id_tbl IN EAM_ObjectInstantiation_PUB.Association_Id_Tbl_Type
154
155 );
156
157
158
159 /* This procedure creates a new PM definition, including the header and all rules. */
160 /*#
161 * This procedure is used to insert records in EAM_PM_SCHEDULINGS
162 * It is used to create Preventive Maintenance Schedule.
163 * @param p_api_version Version of the API
164 * @param p_init_msg_list Flag to indicate initialization of message list
165 * @param p_commit Flag to indicate whether API should commit changes
166 * @param p_validation_level Validation Level of the API
167 * @param x_return_status Return status of the procedure call
168 * @param x_msg_count Count of the return messages that API returns
169 * @param x_msg_data The collection of the messages.
170 * @param p_pm_schedule_rec This is a PL QL record type, which holds the master level data of Preventive Maintenance schedule definition
171 * @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
172 * @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
173 * @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
174 * @param x_new_pm_schedule_id The unique identifier of newly created Preventive Maintenance Schedule
175 * @rep:scope public
176 * @rep:displayname Create Preventive Maintenance Schedule
177 */
178
179
180 PROCEDURE create_PM_def
181 ( p_api_version IN NUMBER ,
182 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
183 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
184 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
185 x_return_status OUT NOCOPY VARCHAR2 ,
186 x_msg_count OUT NOCOPY NUMBER ,
187 x_msg_data OUT NOCOPY VARCHAR2 ,
188 p_pm_schedule_rec IN pm_scheduling_rec_type,
189 p_pm_activities_tbl IN pm_activities_grp_tbl_type,
190 p_pm_day_interval_rules_tbl IN pm_rule_tbl_type ,
191 p_pm_runtime_rules_tbl IN pm_rule_tbl_type ,
192 p_pm_list_date_rules_tbl IN pm_rule_tbl_type ,
193 x_new_pm_schedule_id OUT NOCOPY NUMBER -- this is the pm_schedule_id of the newly created pm schedule
194 );
195
196
197
198 /*#
199 * This procedure is used to update the existing records in EAM_PM_SCHEDULINGS.
200 * It is used to update Preventive Maintenance Schedule.
201 * @param p_api_version Version of the API
202 * @param p_init_msg_list Flag to indicate initialization of message list
203 * @param p_commit Flag to indicate whether API should commit changes
204 * @param p_validation_level Validation Level of the API
205 * @param x_return_status Return status of the procedure call
206 * @param x_msg_count Count of the return messages that API returns
207 * @param x_msg_data The collection of the messages.
208 * @param p_pm_schedule_rec This is a PL QL record type, which holds the master level data of Preventive Maintenance schedule definition
209 * @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
210 * @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
211 * @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
212 * @rep:scope public
213 * @rep:displayname Update Preventive Maintenance Schedule
214 */
215
216 procedure update_pm_def
217 ( p_api_version IN NUMBER ,
218 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
219 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
220 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
221 x_return_status OUT NOCOPY VARCHAR2 ,
222 x_msg_count OUT NOCOPY NUMBER ,
223 x_msg_data OUT NOCOPY VARCHAR2 ,
224 --p_pm_schedule_id IN NUMBER,
225 p_pm_schedule_rec IN pm_scheduling_rec_type:=null,
226 p_pm_activities_tbl IN pm_activities_grp_tbl_type,
227 p_pm_day_interval_rules_tbl IN pm_rule_tbl_type,
228 p_pm_runtime_rules_tbl IN pm_rule_tbl_type,
229 p_pm_list_date_rules_tbl IN pm_rule_tbl_type
230 );
231
232 function validate_pm_header
233 (
234 p_pm_schedule_rec IN pm_scheduling_rec_type,
235 x_reason_failed OUT NOCOPY varchar2
236 ) return BOOLEAN;
237
238
239 function validate_pm_day_interval_rule
240 (
241 p_pm_rule_rec IN pm_rule_rec_type,
242 x_reason_failed OUT NOCOPY varchar2
243 ) return BOOLEAN;
244
245
246 function validate_pm_runtime_rule
247 (
248 p_pm_rule_rec IN pm_rule_rec_type,
249 x_reason_failed OUT NOCOPY varchar2
250 ) return BOOLEAN;
251
252
253 function validate_pm_list_date
254 (
255 p_pm_rule_rec IN pm_rule_rec_type,
256 x_reason_failed OUT NOCOPY varchar2
257 ) return BOOLEAN;
258
259
260 function validate_pm_day_interval_rules
261 (
262 p_pm_rules_tbl IN pm_rule_tbl_type,
263 x_reason_failed OUT NOCOPY varchar2
264 ) return BOOLEAN;
265
266
267 function validate_pm_runtime_rules
268 (
269 p_pm_rules_tbl IN pm_rule_tbl_type,
270 x_reason_failed OUT NOCOPY varchar2
271 ) return BOOLEAN;
272
273
274 function validate_pm_list_date_rules
275 (
276 p_pm_rules_tbl IN pm_rule_tbl_type,
277 x_reason_failed OUT NOCOPY varchar2
278 ) return BOOLEAN;
279
280
281 function validate_pm_header_and_rules
282 (
283 p_pm_schedule_rec IN pm_scheduling_rec_type,
284 p_pm_day_interval_rules_tbl IN pm_rule_tbl_type,
285 p_pm_runtime_rules_tbl IN pm_rule_tbl_type,
286 p_pm_list_date_rules_tbl IN pm_rule_tbl_type,
287 x_reason_failed OUT NOCOPY varchar2
288 ) return BOOLEAN;
289
290 function validate_pm_activity
291 (
292 p_pm_activity_grp_rec IN pm_activities_grp_rec_type,
293 p_pm_runtime_rules_tbl IN pm_rule_tbl_type,
294 p_pm_schedule_rec IN PM_Scheduling_Rec_Type,
295 x_reason_failed OUT NOCOPY varchar2,
296 x_message OUT NOCOPY varchar2,
297 x_activities OUT NOCOPY varchar2
298
299 ) return BOOLEAN;
300
301 function validate_pm_activity
302 (
303 p_pm_activity_grp_rec IN pm_activities_grp_rec_type,
304 p_pm_schedule_rec IN PM_Scheduling_Rec_Type,
305 x_reason_failed OUT NOCOPY varchar2
306 ) return BOOLEAN;
307
308 function validate_pm_activities
309 (
310 p_pm_activities_grp_tbl IN pm_activities_grp_tbl_type,
311 p_pm_runtime_rules_tbl IN pm_rule_tbl_type,
312 p_pm_schedule_rec IN pm_scheduling_rec_type,
313 x_reason_failed OUT NOCOPY varchar2,
314 x_message OUT NOCOPY varchar2,
315 x_activities OUT NOCOPY varchar2
316 ) return BOOLEAN;
317
318 procedure sort_table_by_date
319 (
320 p_date_table IN pm_date_tbl_type,
321 p_num_rows IN number,
322 x_sorted_date_table OUT NOCOPY pm_date_tbl_type
323 );
324
325
326 procedure sort_table_by_number
327 (
328 p_num_table IN pm_num_tbl_type,
329 p_num_rows IN number,
330 x_sorted_num_table OUT NOCOPY pm_num_tbl_type
331 );
332
333
334 procedure merge_rules
335 (p_rules_tbl1 IN pm_rule_tbl_type,
336 p_rules_tbl2 IN pm_rule_tbl_type,
337 x_merged_rules_tbl OUT NOCOPY pm_rule_tbl_type);
338
339 procedure get_pm_last_activity
340 ( p_pm_schedule_id IN NUMBER,
341 p_activity_association_id OUT NOCOPY NUMBER,
342 x_return_status OUT NOCOPY VARCHAR2,
343 x_msg_count OUT NOCOPY NUMBER ,
344 x_msg_data OUT NOCOPY VARCHAR2
345 );
346
347 /*#
348 * 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
349 * @param p_api_version Version of the API
350 * @param p_init_msg_list Flag to indicate initialization of message list
351 * @param p_commit Flag to indicate whether API should commit changes
352 * @param p_validation_level Validation Level of the API
353 * @param x_return_status Return status of the procedure call
354 * @param x_msg_count Count of the return messages that API returns
355 * @param x_msg_data The collection of the messages.
356 * @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
357 * @rep:scope public
358 * @rep:displayname Update Preventive Maintenance Schedule
359 */
360
361 procedure update_pm_last_cyclic_act
362 ( p_api_version IN NUMBER ,
363 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
364 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
365 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
366 x_return_status OUT NOCOPY VARCHAR2 ,
367 x_msg_count OUT NOCOPY NUMBER ,
368 x_msg_data OUT NOCOPY VARCHAR2 ,
369 p_pm_schedule_id IN NUMBER
370 );
371
372 /*#
373 * 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.
374 * This procedure is called from PM Schedule form and from Work Order completion
375 * @param p_api_version Version of the API
376 * @param p_init_msg_list Flag to indicate initialization of message list
377 * @param p_commit Flag to indicate whether API should commit changes
378 * @param p_validation_level Validation Level of the API
379 * @param x_return_status Return status of the procedure call
380 * @param x_msg_count Count of the return messages that API returns
381 * @param x_msg_data The collection of the messages.
382 * @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
383 * @rep:scope public
384 * @rep:displayname Update Preventive Maintenance Schedule
385 */
386
387 procedure update_pm_last_service_reading
388 ( p_api_version IN NUMBER ,
389 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
390 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
391 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
392 x_return_status OUT NOCOPY VARCHAR2 ,
393 x_msg_count OUT NOCOPY NUMBER ,
394 x_msg_data OUT NOCOPY VARCHAR2 ,
395 p_pm_schedule_id IN NUMBER
396 );
397
398 END;
399
[Home] [Help]