1 PACKAGE EAM_WORKORDER_PKG AUTHID CURRENT_USER as
2 /* $Header: EAMWOTHS.pls 120.4.12020000.2 2013/01/18 10:16:57 fqu ship $ */
3
4 /************************************************
5 * Default Values: *
6 * Many columns are not used in EAM. As we *
7 * decided, we put the pre-determined default *
8 * value into them. *
9 * These are just indicative values. Actual *
10 * default values are in procedure spec. *
11 ************************************************/
12 Default_Job_Type NUMBER := 3; -- Treat EAM Job as Non-Standard Job
13 Default_Wip_Supply_Type NUMBER := 7; -- Based On Bill
14 Default_Start_Quantity NUMBER := 1; -- Not Used
15 Default_Ovcomp_Tol_Type NUMBER := null; -- Not Used
16 Default_Ovcomp_Tol_Value NUMBER := null; --Not Used
17 Default_Quantity_Completed NUMBER := 0; -- Not Used
18 Default_Quantity_Scrapped NUMBER := 0; -- Not Used
19 Default_Net_Quantity NUMBER := 1; -- Not Used
20 Default_Schedule_Group_Id NUMBER := null; -- Not Used
21 Default_Build_Sequence NUMBER := null; -- Not Used
22 Default_Line_Id NUMBER := null; -- Not Used
23 Default_End_Item_Unit_Number VARCHAR2(10) := null; -- Not Used
24 Default_Po_Creation_Time NUMBER := 1; -- Not Used
25 Default_Due_Date DATE := null; -- Not Used
26
27
28 /* The Insert and Update table handlers for Define EAM WorkOrders */
29
30 /*cboppana --Added this function to create a work order.This calls the Work Order api */
31 PROCEDURE Create_Workorder(X_Rowid IN OUT NOCOPY VARCHAR2,
32 X_wip_entity_id IN OUT NOCOPY NUMBER,
33 X_organization_id NUMBER,
34 X_last_update_date DATE,
35 X_last_updated_by NUMBER,
36 X_creation_date DATE,
37 X_created_by NUMBER,
38 X_last_update_login NUMBER,
39 X_description IN OUT NOCOPY VARCHAR2,
40 X_status_type IN OUT NOCOPY NUMBER,
41 X_user_defined_status_id IN OUT NOCOPY NUMBER,
42 X_pending_flag IN OUT NOCOPY VARCHAR2,
43 X_workflow_type IN OUT NOCOPY NUMBER ,
44 X_warranty_claim_status IN OUT NOCOPY NUMBER,
45 X_material_shortage_flag IN OUT NOCOPY NUMBER,
46 X_material_shortage_check_date IN OUT NOCOPY DATE,
47
48 X_primary_item_id NUMBER,
49 X_parent_wip_entity_id NUMBER,
50 X_asset_number VARCHAR2,
51 X_asset_group_id NUMBER,
52 X_pm_schedule_id NUMBER,
53 X_rebuild_item_id NUMBER,
54 X_rebuild_serial_number VARCHAR2,
55 X_manual_rebuild_flag IN OUT NOCOPY VARCHAR2,
56 X_shutdown_type IN OUT NOCOPY VARCHAR2,
57 X_tagout_required IN OUT NOCOPY VARCHAR2,
58 X_plan_maintenance IN OUT NOCOPY VARCHAR2,
59 X_estimation_status VARCHAR2,
60 X_requested_start_date IN OUT NOCOPY DATE,
61 X_requested_due_date IN OUT NOCOPY DATE,
62 X_notification_required IN OUT NOCOPY VARCHAR2,
63 X_work_order_type IN OUT NOCOPY VARCHAR2,
64 X_owning_department IN OUT NOCOPY NUMBER,
65 X_planner_maintenance IN OUT NOCOPY NUMBER,
66 X_activity_type IN OUT NOCOPY VARCHAR2,
67 X_activity_cause IN OUT NOCOPY VARCHAR2,
68 X_firm_planned_flag IN OUT NOCOPY NUMBER,
69 X_job_type NUMBER default 3,
70 X_wip_supply_type NUMBER default 7,
71 X_class_code IN OUT NOCOPY VARCHAR2,
72 X_material_account IN OUT NOCOPY NUMBER,
73 X_material_overhead_account IN OUT NOCOPY NUMBER,
74 X_resource_account IN OUT NOCOPY NUMBER,
75 X_outside_processing_account IN OUT NOCOPY NUMBER,
76 X_material_variance_account IN OUT NOCOPY NUMBER,
77 X_resource_variance_account IN OUT NOCOPY NUMBER,
78 X_outside_proc_var_account IN OUT NOCOPY NUMBER,
79 X_std_cost_adjustment_account IN OUT NOCOPY NUMBER,
80 X_overhead_account IN OUT NOCOPY NUMBER,
81 X_overhead_variance_account IN OUT NOCOPY NUMBER,
82 X_scheduled_start_date IN OUT NOCOPY DATE,
83 X_date_released DATE,
84 X_scheduled_completion_date IN OUT NOCOPY DATE,
85 X_date_completed DATE,
86 X_date_closed DATE,
87 X_start_quantity NUMBER default 1,
88 X_overcompletion_toleran_type NUMBER default null,
89 X_overcompletion_toleran_value NUMBER default null,
90 X_quantity_completed NUMBER default 0,
91 X_quantity_scrapped NUMBER default 0,
92 X_net_quantity NUMBER default 1,
93 X_bom_reference_id NUMBER,
94 X_routing_reference_id NUMBER,
95 X_common_bom_sequence_id IN OUT NOCOPY NUMBER,
96 X_common_routing_sequence_id IN OUT NOCOPY NUMBER,
97 X_bom_revision IN OUT NOCOPY VARCHAR2,
98 X_routing_revision IN OUT NOCOPY VARCHAR2,
99 X_bom_revision_date IN OUT NOCOPY DATE,
100 X_routing_revision_date IN OUT NOCOPY DATE,
101 X_lot_number VARCHAR2,
102 X_alternate_bom_designator IN OUT NOCOPY VARCHAR2,
103 X_alternate_routing_designator IN OUT NOCOPY VARCHAR2,
104 X_completion_subinventory VARCHAR2,
105 X_completion_locator_id NUMBER,
106 X_demand_class VARCHAR2,
107 X_attribute_category IN OUT NOCOPY VARCHAR2,
108 X_attribute1 IN OUT NOCOPY VARCHAR2,
109 X_attribute2 IN OUT NOCOPY VARCHAR2,
110 X_attribute3 IN OUT NOCOPY VARCHAR2,
111 X_attribute4 IN OUT NOCOPY VARCHAR2,
112 X_attribute5 IN OUT NOCOPY VARCHAR2,
113 X_attribute6 IN OUT NOCOPY VARCHAR2,
114 X_attribute7 IN OUT NOCOPY VARCHAR2,
115 X_attribute8 IN OUT NOCOPY VARCHAR2,
116 X_attribute9 IN OUT NOCOPY VARCHAR2,
117 X_attribute10 IN OUT NOCOPY VARCHAR2,
118 X_attribute11 IN OUT NOCOPY VARCHAR2,
119 X_attribute12 IN OUT NOCOPY VARCHAR2,
120 X_attribute13 IN OUT NOCOPY VARCHAR2,
121 X_attribute14 IN OUT NOCOPY VARCHAR2,
122 X_attribute15 IN OUT NOCOPY VARCHAR2,
123 X_We_Rowid IN OUT NOCOPY VARCHAR2,
124 X_Entity_Type NUMBER,
125 X_Wip_Entity_Name VARCHAR2,
126 X_Schedule_Group_Id NUMBER default null,
127 X_Build_Sequence NUMBER default null,
128 X_Line_Id NUMBER default null,
129 X_Project_Id NUMBER,
130 X_Task_Id NUMBER,
131 X_end_item_unit_number VARCHAR2 default null,
132 X_po_creation_time NUMBER default 1,
133 X_priority IN OUT NOCOPY NUMBER ,
134 X_due_date DATE default null,
135 X_maintenance_object_id NUMBER,
136 X_maintenance_object_source NUMBER,
137 X_maintenance_object_type NUMBER,
138 X_material_issue_by_mo IN OUT NOCOPY VARCHAR2,
139 X_activity_source IN OUT NOCOPY VARCHAR2,
140 X_Parent_Wip_Name VARCHAR2 default null,
141 X_Relationship_Type NUMBER default null,
142 X_Relation_Status IN OUT NOCOPY VARCHAR2,
143 x_failure_code_required IN OUT NOCOPY VARCHAR2,
144 x_eam_failure_entry_record IN OUT NOCOPY eam_process_failure_entry_pub.eam_failure_entry_record_typ ,
145 x_eam_failure_codes_tbl IN OUT NOCOPY eam_process_failure_entry_pub.eam_failure_codes_tbl_typ);
146
147
148 /*cboppana ----Added this function to update a work order.This calls the Work Order api */
149 PROCEDURE Update_Workorder( X_Rowid VARCHAR2,
150 X_wip_entity_id NUMBER,
151 X_organization_id NUMBER,
152 X_last_update_date DATE,
153 X_last_updated_by NUMBER,
154 X_creation_date DATE,
155 X_created_by NUMBER,
156 X_last_update_login NUMBER,
157 X_description IN OUT NOCOPY VARCHAR2,
158 X_status_type IN OUT NOCOPY NUMBER,
159 X_user_defined_status_id IN OUT NOCOPY NUMBER,
160 X_pending_flag IN OUT NOCOPY VARCHAR2,
161 X_workflow_type IN OUT NOCOPY NUMBER ,
162 X_warranty_claim_status IN OUT NOCOPY NUMBER,
163 X_material_shortage_flag IN OUT NOCOPY NUMBER,
164 X_material_shortage_check_date IN OUT NOCOPY DATE,
165 X_primary_item_id NUMBER,
166 X_parent_wip_entity_id NUMBER,
167 X_asset_number VARCHAR2,
168 X_asset_group_id NUMBER,
169 X_pm_schedule_id NUMBER,
170 X_rebuild_item_id NUMBER,
171 X_rebuild_serial_number VARCHAR2,
172 X_manual_rebuild_flag IN OUT NOCOPY VARCHAR2,
173 X_shutdown_type IN OUT NOCOPY VARCHAR2,
174 X_tagout_required IN OUT NOCOPY VARCHAR2,
175 X_plan_maintenance IN OUT NOCOPY VARCHAR2,
176 X_estimation_status VARCHAR2,
177 X_requested_start_date IN OUT NOCOPY DATE,
178 X_requested_due_date IN OUT NOCOPY DATE,
179 X_notification_required IN OUT NOCOPY VARCHAR2,
180 X_work_order_type IN OUT NOCOPY VARCHAR2,
181 X_owning_department IN OUT NOCOPY NUMBER,
182 X_planner_maintenance IN OUT NOCOPY NUMBER,
183 X_activity_type IN OUT NOCOPY VARCHAR2,
184 X_activity_cause IN OUT NOCOPY VARCHAR2,
185 X_firm_planned_flag IN OUT NOCOPY NUMBER,
186 X_class_code IN OUT NOCOPY VARCHAR2,
187 X_material_account IN OUT NOCOPY NUMBER,
188 X_material_overhead_account IN OUT NOCOPY NUMBER,
189 X_resource_account IN OUT NOCOPY NUMBER,
190 X_outside_processing_account IN OUT NOCOPY NUMBER,
191 X_material_variance_account IN OUT NOCOPY NUMBER,
192 X_resource_variance_account IN OUT NOCOPY NUMBER,
193 X_outside_proc_var_account IN OUT NOCOPY NUMBER,
194 X_std_cost_adjustment_account IN OUT NOCOPY NUMBER,
195 X_overhead_account IN OUT NOCOPY NUMBER,
196 X_overhead_variance_account IN OUT NOCOPY NUMBER,
197 X_scheduled_start_date IN OUT NOCOPY DATE,
198 X_date_released DATE,
199 X_scheduled_completion_date IN OUT NOCOPY DATE,
200 X_date_completed DATE,
201 X_date_closed DATE,
202 X_bom_reference_id NUMBER,
203 X_routing_reference_id NUMBER,
204 X_common_bom_sequence_id IN OUT NOCOPY NUMBER,
205 X_common_routing_sequence_id IN OUT NOCOPY NUMBER,
206 X_bom_revision IN OUT NOCOPY VARCHAR2,
207 X_routing_revision IN OUT NOCOPY VARCHAR2,
208 X_bom_revision_date IN OUT NOCOPY DATE,
209 X_routing_revision_date IN OUT NOCOPY DATE,
210 X_lot_number VARCHAR2,
211 X_alternate_bom_designator IN OUT NOCOPY VARCHAR2,
212 X_alternate_routing_designator IN OUT NOCOPY VARCHAR2,
213 X_completion_subinventory VARCHAR2,
214 X_completion_locator_id NUMBER,
215 X_demand_class VARCHAR2,
216 X_attribute_category IN OUT NOCOPY VARCHAR2,
217 X_attribute1 IN OUT NOCOPY VARCHAR2,
218 X_attribute2 IN OUT NOCOPY VARCHAR2,
219 X_attribute3 IN OUT NOCOPY VARCHAR2,
220 X_attribute4 IN OUT NOCOPY VARCHAR2,
221 X_attribute5 IN OUT NOCOPY VARCHAR2,
222 X_attribute6 IN OUT NOCOPY VARCHAR2,
223 X_attribute7 IN OUT NOCOPY VARCHAR2,
224 X_attribute8 IN OUT NOCOPY VARCHAR2,
225 X_attribute9 IN OUT NOCOPY VARCHAR2,
226 X_attribute10 IN OUT NOCOPY VARCHAR2,
227 X_attribute11 IN OUT NOCOPY VARCHAR2,
228 X_attribute12 IN OUT NOCOPY VARCHAR2,
229 X_attribute13 IN OUT NOCOPY VARCHAR2,
230 X_attribute14 IN OUT NOCOPY VARCHAR2,
231 X_attribute15 IN OUT NOCOPY VARCHAR2,
232 X_We_Rowid IN OUT NOCOPY VARCHAR2,
233 X_Entity_Type NUMBER,
234 X_Wip_Entity_Name VARCHAR2,
235 X_Update_Wip_Entities VARCHAR2,
236 X_Schedule_Group_Id NUMBER,
237 X_Project_Id NUMBER,
238 X_Task_Id NUMBER,
239 X_priority IN OUT NOCOPY NUMBER,
240 X_maintenance_object_id NUMBER,
241 X_maintenance_object_source NUMBER,
242 X_maintenance_object_type NUMBER,
243 X_material_issue_by_mo IN OUT NOCOPY VARCHAR2,
244 X_activity_source IN OUT NOCOPY VARCHAR2,
245 X_old_rebuild_source NUMBER default NULL,
246 x_failure_code_required IN OUT NOCOPY VARCHAR2,
247 x_eam_failure_entry_record IN OUT NOCOPY eam_process_failure_entry_pub.eam_failure_entry_record_typ,
248 x_eam_failure_codes_tbl IN OUT NOCOPY eam_process_failure_entry_pub.eam_failure_codes_tbl_typ,
249 x_return_status IN OUT NOCOPY VARCHAR2 /*added for bug 7003588*/);
250
251
252 PROCEDURE Lock_Row( X_Rowid VARCHAR2,
253 X_wip_entity_id NUMBER,
254 X_organization_id NUMBER,
255 X_description VARCHAR2,
256 X_status_type NUMBER,
257 X_user_defined_status_id NUMBER,
258 X_pending_flag VARCHAR2,
259 X_workflow_type NUMBER ,
260 X_warranty_claim_status NUMBER,
261 X_material_shortage_flag NUMBER,
262 X_material_shortage_check_date DATE,
263 X_primary_item_id NUMBER,
264 X_parent_wip_entity_id NUMBER,
265 X_asset_number VARCHAR2,
266 X_asset_group_id NUMBER,
267 X_pm_schedule_id NUMBER,
268 X_rebuild_item_id NUMBER,
269 X_rebuild_serial_number VARCHAR2,
270 X_manual_rebuild_flag VARCHAR2,
271 X_shutdown_type VARCHAR2,
272 X_tagout_required VARCHAR2,
273 X_plan_maintenance VARCHAR2,
274 X_estimation_status VARCHAR2,
275 X_requested_start_date DATE,
276 X_requested_due_date DATE,
277 X_notification_required VARCHAR2,
278 X_work_order_type VARCHAR2,
279 X_owning_department NUMBER,
280 X_activity_type VARCHAR2,
281 X_activity_cause VARCHAR2,
282 X_firm_planned_flag NUMBER,
283 X_class_code VARCHAR2,
284 X_material_account NUMBER,
285 X_material_overhead_account NUMBER,
286 X_resource_account NUMBER,
287 X_outside_processing_account NUMBER,
288 X_material_variance_account NUMBER,
289 X_resource_variance_account NUMBER,
290 X_outside_proc_var_account NUMBER,
291 X_std_cost_adjustment_account NUMBER,
292 X_overhead_account NUMBER,
293 X_overhead_variance_account NUMBER,
294 X_scheduled_start_date DATE,
295 X_date_released DATE,
296 X_scheduled_completion_date DATE,
297 X_date_completed DATE,
298 X_date_closed DATE,
299 X_bom_reference_id NUMBER,
300 X_routing_reference_id NUMBER,
301 X_common_bom_sequence_id NUMBER,
302 X_common_routing_sequence_id NUMBER,
303 X_bom_revision VARCHAR2,
304 X_routing_revision VARCHAR2,
305 X_bom_revision_date DATE,
306 X_routing_revision_date DATE,
307 X_lot_number VARCHAR2,
308 X_alternate_bom_designator VARCHAR2,
309 X_alternate_routing_designator VARCHAR2,
310 X_completion_subinventory VARCHAR2,
311 X_completion_locator_id NUMBER,
312 X_demand_class VARCHAR2,
313 X_attribute_category VARCHAR2,
314 X_attribute1 VARCHAR2,
315 X_attribute2 VARCHAR2,
316 X_attribute3 VARCHAR2,
317 X_attribute4 VARCHAR2,
318 X_attribute5 VARCHAR2,
319 X_attribute6 VARCHAR2,
320 X_attribute7 VARCHAR2,
321 X_attribute8 VARCHAR2,
322 X_attribute9 VARCHAR2,
323 X_attribute10 VARCHAR2,
324 X_attribute11 VARCHAR2,
325 X_attribute12 VARCHAR2,
326 X_attribute13 VARCHAR2,
327 X_attribute14 VARCHAR2,
328 X_attribute15 VARCHAR2,
329 X_We_Rowid VARCHAR2,
330 X_Entity_Type NUMBER,
331 X_Wip_Entity_Name VARCHAR2,
332 X_Update_Wip_Entities VARCHAR2,
333 X_Schedule_Group_Id NUMBER,
334 X_Project_Id NUMBER,
335 X_Task_Id NUMBER,
336 X_priority NUMBER,
337 X_maintenance_object_id NUMBER,
338 X_maintenance_object_source NUMBER,
339 X_maintenance_object_type NUMBER,
340 X_material_issue_by_mo VARCHAR2,
341 X_activity_source VARCHAR2);
342
343
344
345
346 PROCEDURE Update_Genealogy( X_wip_entity_id IN NUMBER,
347 X_organization_id IN NUMBER,
348 X_parent_wip_entity_id IN NUMBER,
349 X_rebuild_item_id IN NUMBER,
350 X_rebuild_serial_number IN VARCHAR2,
351 X_manual_rebuild_flag IN VARCHAR2,
352 x_maintenance_object_type IN NUMBER,
353 x_maintenance_object_id IN NUMBER ) ;
354
355 PROCEDURE get_all_mesg(mesg IN OUT NOCOPY VARCHAR2);
356
357 END EAM_WORKORDER_PKG;
358
359
360