1 PACKAGE EAM_WORKORDER_PKG as
2 /* $Header: EAMWOTHS.pls 120.3.12010000.2 2008/11/06 23:53:28 mashah 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_activity_type IN OUT NOCOPY VARCHAR2,
66 X_activity_cause IN OUT NOCOPY VARCHAR2,
67 X_firm_planned_flag IN OUT NOCOPY NUMBER,
68 X_job_type NUMBER default 3,
69 X_wip_supply_type NUMBER default 7,
70 X_class_code IN OUT NOCOPY VARCHAR2,
71 X_material_account IN OUT NOCOPY NUMBER,
72 X_material_overhead_account IN OUT NOCOPY NUMBER,
73 X_resource_account IN OUT NOCOPY NUMBER,
74 X_outside_processing_account IN OUT NOCOPY NUMBER,
75 X_material_variance_account IN OUT NOCOPY NUMBER,
76 X_resource_variance_account IN OUT NOCOPY NUMBER,
77 X_outside_proc_var_account IN OUT NOCOPY NUMBER,
78 X_std_cost_adjustment_account IN OUT NOCOPY NUMBER,
79 X_overhead_account IN OUT NOCOPY NUMBER,
80 X_overhead_variance_account IN OUT NOCOPY NUMBER,
81 X_scheduled_start_date IN OUT NOCOPY DATE,
82 X_date_released DATE,
83 X_scheduled_completion_date IN OUT NOCOPY DATE,
84 X_date_completed DATE,
85 X_date_closed DATE,
86 X_start_quantity NUMBER default 1,
87 X_overcompletion_toleran_type NUMBER default null,
88 X_overcompletion_toleran_value NUMBER default null,
89 X_quantity_completed NUMBER default 0,
90 X_quantity_scrapped NUMBER default 0,
91 X_net_quantity NUMBER default 1,
92 X_bom_reference_id NUMBER,
93 X_routing_reference_id NUMBER,
94 X_common_bom_sequence_id IN OUT NOCOPY NUMBER,
95 X_common_routing_sequence_id IN OUT NOCOPY NUMBER,
96 X_bom_revision IN OUT NOCOPY VARCHAR2,
97 X_routing_revision IN OUT NOCOPY VARCHAR2,
98 X_bom_revision_date IN OUT NOCOPY DATE,
99 X_routing_revision_date IN OUT NOCOPY DATE,
100 X_lot_number VARCHAR2,
101 X_alternate_bom_designator IN OUT NOCOPY VARCHAR2,
102 X_alternate_routing_designator IN OUT NOCOPY VARCHAR2,
103 X_completion_subinventory VARCHAR2,
104 X_completion_locator_id NUMBER,
105 X_demand_class VARCHAR2,
106 X_attribute_category IN OUT NOCOPY VARCHAR2,
107 X_attribute1 IN OUT NOCOPY VARCHAR2,
108 X_attribute2 IN OUT NOCOPY VARCHAR2,
109 X_attribute3 IN OUT NOCOPY VARCHAR2,
110 X_attribute4 IN OUT NOCOPY VARCHAR2,
111 X_attribute5 IN OUT NOCOPY VARCHAR2,
112 X_attribute6 IN OUT NOCOPY VARCHAR2,
113 X_attribute7 IN OUT NOCOPY VARCHAR2,
114 X_attribute8 IN OUT NOCOPY VARCHAR2,
115 X_attribute9 IN OUT NOCOPY VARCHAR2,
116 X_attribute10 IN OUT NOCOPY VARCHAR2,
117 X_attribute11 IN OUT NOCOPY VARCHAR2,
118 X_attribute12 IN OUT NOCOPY VARCHAR2,
119 X_attribute13 IN OUT NOCOPY VARCHAR2,
120 X_attribute14 IN OUT NOCOPY VARCHAR2,
121 X_attribute15 IN OUT NOCOPY VARCHAR2,
122 X_We_Rowid IN OUT NOCOPY VARCHAR2,
123 X_Entity_Type NUMBER,
124 X_Wip_Entity_Name VARCHAR2,
125 X_Schedule_Group_Id NUMBER default null,
126 X_Build_Sequence NUMBER default null,
127 X_Line_Id NUMBER default null,
128 X_Project_Id NUMBER,
129 X_Task_Id NUMBER,
130 X_end_item_unit_number VARCHAR2 default null,
131 X_po_creation_time NUMBER default 1,
132 X_priority IN OUT NOCOPY NUMBER ,
133 X_due_date DATE default null,
134 X_maintenance_object_id NUMBER,
135 X_maintenance_object_source NUMBER,
136 X_maintenance_object_type NUMBER,
137 X_material_issue_by_mo IN OUT NOCOPY VARCHAR2,
138 X_activity_source IN OUT NOCOPY VARCHAR2,
139 X_Parent_Wip_Name VARCHAR2 default null,
140 X_Relationship_Type NUMBER default null,
141 X_Relation_Status IN OUT NOCOPY VARCHAR2,
142 x_failure_code_required IN OUT NOCOPY VARCHAR2,
143 x_eam_failure_entry_record IN OUT NOCOPY eam_process_failure_entry_pub.eam_failure_entry_record_typ ,
144 x_eam_failure_codes_tbl IN OUT NOCOPY eam_process_failure_entry_pub.eam_failure_codes_tbl_typ);
145
146
147 /*cboppana ----Added this function to update a work order.This calls the Work Order api */
148 PROCEDURE Update_Workorder( X_Rowid VARCHAR2,
149 X_wip_entity_id NUMBER,
150 X_organization_id NUMBER,
151 X_last_update_date DATE,
152 X_last_updated_by NUMBER,
153 X_creation_date DATE,
154 X_created_by NUMBER,
155 X_last_update_login NUMBER,
156 X_description IN OUT NOCOPY VARCHAR2,
157 X_status_type IN OUT NOCOPY NUMBER,
158 X_user_defined_status_id IN OUT NOCOPY NUMBER,
159 X_pending_flag IN OUT NOCOPY VARCHAR2,
160 X_workflow_type IN OUT NOCOPY NUMBER ,
161 X_warranty_claim_status IN OUT NOCOPY NUMBER,
162 X_material_shortage_flag IN OUT NOCOPY NUMBER,
163 X_material_shortage_check_date IN OUT NOCOPY DATE,
164 X_primary_item_id NUMBER,
165 X_parent_wip_entity_id NUMBER,
166 X_asset_number VARCHAR2,
167 X_asset_group_id NUMBER,
168 X_pm_schedule_id NUMBER,
169 X_rebuild_item_id NUMBER,
170 X_rebuild_serial_number VARCHAR2,
171 X_manual_rebuild_flag IN OUT NOCOPY VARCHAR2,
172 X_shutdown_type IN OUT NOCOPY VARCHAR2,
173 X_tagout_required IN OUT NOCOPY VARCHAR2,
174 X_plan_maintenance IN OUT NOCOPY VARCHAR2,
175 X_estimation_status VARCHAR2,
176 X_requested_start_date IN OUT NOCOPY DATE,
177 X_requested_due_date IN OUT NOCOPY DATE,
178 X_notification_required IN OUT NOCOPY VARCHAR2,
179 X_work_order_type IN OUT NOCOPY VARCHAR2,
180 X_owning_department IN OUT NOCOPY NUMBER,
181 X_activity_type IN OUT NOCOPY VARCHAR2,
182 X_activity_cause IN OUT NOCOPY VARCHAR2,
183 X_firm_planned_flag IN OUT NOCOPY NUMBER,
184 X_class_code IN OUT NOCOPY VARCHAR2,
185 X_material_account IN OUT NOCOPY NUMBER,
186 X_material_overhead_account IN OUT NOCOPY NUMBER,
187 X_resource_account IN OUT NOCOPY NUMBER,
188 X_outside_processing_account IN OUT NOCOPY NUMBER,
189 X_material_variance_account IN OUT NOCOPY NUMBER,
190 X_resource_variance_account IN OUT NOCOPY NUMBER,
191 X_outside_proc_var_account IN OUT NOCOPY NUMBER,
192 X_std_cost_adjustment_account IN OUT NOCOPY NUMBER,
193 X_overhead_account IN OUT NOCOPY NUMBER,
194 X_overhead_variance_account IN OUT NOCOPY NUMBER,
195 X_scheduled_start_date IN OUT NOCOPY DATE,
196 X_date_released DATE,
197 X_scheduled_completion_date IN OUT NOCOPY DATE,
198 X_date_completed DATE,
199 X_date_closed DATE,
200 X_bom_reference_id NUMBER,
201 X_routing_reference_id NUMBER,
202 X_common_bom_sequence_id IN OUT NOCOPY NUMBER,
203 X_common_routing_sequence_id IN OUT NOCOPY NUMBER,
204 X_bom_revision IN OUT NOCOPY VARCHAR2,
205 X_routing_revision IN OUT NOCOPY VARCHAR2,
206 X_bom_revision_date IN OUT NOCOPY DATE,
207 X_routing_revision_date IN OUT NOCOPY DATE,
208 X_lot_number VARCHAR2,
209 X_alternate_bom_designator IN OUT NOCOPY VARCHAR2,
210 X_alternate_routing_designator IN OUT NOCOPY VARCHAR2,
211 X_completion_subinventory VARCHAR2,
212 X_completion_locator_id NUMBER,
213 X_demand_class VARCHAR2,
214 X_attribute_category IN OUT NOCOPY VARCHAR2,
215 X_attribute1 IN OUT NOCOPY VARCHAR2,
216 X_attribute2 IN OUT NOCOPY VARCHAR2,
217 X_attribute3 IN OUT NOCOPY VARCHAR2,
218 X_attribute4 IN OUT NOCOPY VARCHAR2,
219 X_attribute5 IN OUT NOCOPY VARCHAR2,
220 X_attribute6 IN OUT NOCOPY VARCHAR2,
221 X_attribute7 IN OUT NOCOPY VARCHAR2,
222 X_attribute8 IN OUT NOCOPY VARCHAR2,
223 X_attribute9 IN OUT NOCOPY VARCHAR2,
224 X_attribute10 IN OUT NOCOPY VARCHAR2,
225 X_attribute11 IN OUT NOCOPY VARCHAR2,
226 X_attribute12 IN OUT NOCOPY VARCHAR2,
227 X_attribute13 IN OUT NOCOPY VARCHAR2,
228 X_attribute14 IN OUT NOCOPY VARCHAR2,
229 X_attribute15 IN OUT NOCOPY VARCHAR2,
230 X_We_Rowid IN OUT NOCOPY VARCHAR2,
231 X_Entity_Type NUMBER,
232 X_Wip_Entity_Name VARCHAR2,
233 X_Update_Wip_Entities VARCHAR2,
234 X_Schedule_Group_Id NUMBER,
235 X_Project_Id NUMBER,
236 X_Task_Id NUMBER,
237 X_priority IN OUT NOCOPY NUMBER,
238 X_maintenance_object_id NUMBER,
239 X_maintenance_object_source NUMBER,
240 X_maintenance_object_type NUMBER,
241 X_material_issue_by_mo IN OUT NOCOPY VARCHAR2,
242 X_activity_source IN OUT NOCOPY VARCHAR2,
243 X_old_rebuild_source NUMBER default NULL,
244 x_failure_code_required IN OUT NOCOPY VARCHAR2,
245 x_eam_failure_entry_record IN OUT NOCOPY eam_process_failure_entry_pub.eam_failure_entry_record_typ,
246 x_eam_failure_codes_tbl IN OUT NOCOPY eam_process_failure_entry_pub.eam_failure_codes_tbl_typ,
247 x_return_status IN OUT NOCOPY VARCHAR2 /*added for bug 7003588*/);
248
249
250 PROCEDURE Lock_Row( X_Rowid VARCHAR2,
251 X_wip_entity_id NUMBER,
252 X_organization_id NUMBER,
253 X_description VARCHAR2,
254 X_status_type NUMBER,
255 X_user_defined_status_id NUMBER,
256 X_pending_flag VARCHAR2,
257 X_workflow_type NUMBER ,
258 X_warranty_claim_status NUMBER,
259 X_material_shortage_flag NUMBER,
260 X_material_shortage_check_date DATE,
261 X_primary_item_id NUMBER,
262 X_parent_wip_entity_id NUMBER,
263 X_asset_number VARCHAR2,
264 X_asset_group_id NUMBER,
265 X_pm_schedule_id NUMBER,
266 X_rebuild_item_id NUMBER,
267 X_rebuild_serial_number VARCHAR2,
268 X_manual_rebuild_flag VARCHAR2,
269 X_shutdown_type VARCHAR2,
270 X_tagout_required VARCHAR2,
271 X_plan_maintenance VARCHAR2,
272 X_estimation_status VARCHAR2,
273 X_requested_start_date DATE,
274 X_requested_due_date DATE,
275 X_notification_required VARCHAR2,
276 X_work_order_type VARCHAR2,
277 X_owning_department NUMBER,
278 X_activity_type VARCHAR2,
279 X_activity_cause VARCHAR2,
280 X_firm_planned_flag NUMBER,
281 X_class_code VARCHAR2,
282 X_material_account NUMBER,
283 X_material_overhead_account NUMBER,
284 X_resource_account NUMBER,
285 X_outside_processing_account NUMBER,
286 X_material_variance_account NUMBER,
287 X_resource_variance_account NUMBER,
288 X_outside_proc_var_account NUMBER,
289 X_std_cost_adjustment_account NUMBER,
290 X_overhead_account NUMBER,
291 X_overhead_variance_account NUMBER,
292 X_scheduled_start_date DATE,
293 X_date_released DATE,
294 X_scheduled_completion_date DATE,
295 X_date_completed DATE,
296 X_date_closed DATE,
297 X_bom_reference_id NUMBER,
298 X_routing_reference_id NUMBER,
299 X_common_bom_sequence_id NUMBER,
300 X_common_routing_sequence_id NUMBER,
301 X_bom_revision VARCHAR2,
302 X_routing_revision VARCHAR2,
303 X_bom_revision_date DATE,
304 X_routing_revision_date DATE,
305 X_lot_number VARCHAR2,
306 X_alternate_bom_designator VARCHAR2,
307 X_alternate_routing_designator VARCHAR2,
308 X_completion_subinventory VARCHAR2,
309 X_completion_locator_id NUMBER,
310 X_demand_class VARCHAR2,
311 X_attribute_category VARCHAR2,
312 X_attribute1 VARCHAR2,
313 X_attribute2 VARCHAR2,
314 X_attribute3 VARCHAR2,
315 X_attribute4 VARCHAR2,
316 X_attribute5 VARCHAR2,
317 X_attribute6 VARCHAR2,
318 X_attribute7 VARCHAR2,
319 X_attribute8 VARCHAR2,
320 X_attribute9 VARCHAR2,
321 X_attribute10 VARCHAR2,
322 X_attribute11 VARCHAR2,
323 X_attribute12 VARCHAR2,
324 X_attribute13 VARCHAR2,
325 X_attribute14 VARCHAR2,
326 X_attribute15 VARCHAR2,
327 X_We_Rowid VARCHAR2,
328 X_Entity_Type NUMBER,
329 X_Wip_Entity_Name VARCHAR2,
330 X_Update_Wip_Entities VARCHAR2,
331 X_Schedule_Group_Id NUMBER,
332 X_Project_Id NUMBER,
333 X_Task_Id NUMBER,
334 X_priority NUMBER,
335 X_maintenance_object_id NUMBER,
336 X_maintenance_object_source NUMBER,
337 X_maintenance_object_type NUMBER,
338 X_material_issue_by_mo VARCHAR2,
339 X_activity_source VARCHAR2);
340
341
342
343
344 PROCEDURE Update_Genealogy( X_wip_entity_id IN NUMBER,
345 X_organization_id IN NUMBER,
346 X_parent_wip_entity_id IN NUMBER,
347 X_rebuild_item_id IN NUMBER,
348 X_rebuild_serial_number IN VARCHAR2,
349 X_manual_rebuild_flag IN VARCHAR2,
350 x_maintenance_object_type IN NUMBER,
351 x_maintenance_object_id IN NUMBER ) ;
352
353 PROCEDURE get_all_mesg(mesg IN OUT NOCOPY VARCHAR2);
354
355 END EAM_WORKORDER_PKG;
356
357
358