1 PACKAGE eam_completion AS
2 /* $Header: EAMWCMPS.pls 120.1 2005/12/05 14:46:58 baroy noship $*/
3 /*#
4 * This is the public API for maintenance work order completion/uncompletion
5 * @rep:scope public
6 * @rep:product EAM
7 * @rep:displayname Maintenance Work Completion
8 * @rep:lifecycle active
9 * @rep:category BUSINESS_ENTITY EAM_COMPLETE_WO_OPERATION
10 */
11 -- Version Initial version 1.0 Kaweesak Boonyapornnad
12
13 INVENTORY_ITEM_NULL EAM_WorkOrderTransactions_PUB.Inventory_Item_Tbl_Type;
14
15 TYPE Lot_Serial_Rec_Type is RECORD
16 (
17 lot_number VARCHAR2(80),
18 serial_number VARCHAR2(30),
19 quantity NUMBER
20 );
21
22 TYPE Lot_Serial_Tbl_Type is TABLE OF Lot_Serial_Rec_Type INDEX BY BINARY_INTEGER;
23
24 /***************************************************************************
25 *
26 * This package will be used to complete and uncomplete EAM work order
27 *
28 * PARAMETER:
29 *
30 * x_wip_entity_id Work Order ID
31 * x_rebuild_jobs A flag used to determine work order type
32 * (N:Regular EAM work order/ Y:Rebuild work order)
33 * x_transaction_type The type of transaction (Complete(1) / Uncomplete(2))
34 * x_transaction_date The date of transaction
35 * x_user_id User ID
36 * x_request_id, For concurrent processing
37 * x_appplication_id, For concurrent processing
38 * x_program_id For concurrent processing
39 * x_reconcil_code This parameter was predefined in FND_LOOKUP_VALUES
40 * where lookup_type = 'WIP_EAM_RECONCILIATION_CODE'
41 * x_subinventory For rebuild work order with material issue only
42 * x_locator_id For rebuild work order with material issue only
43 * x_lot_number For rebuild work order with material issue only
44 * x_serial_number For rebuild work order with material issue only
45 * x_reference For regular EAM work order only
46 * x_qa_collection_id For regular EAM work order only
47 * (null if the the work order is not under QA control)
48 * x_shutdown_start_date Shutdown information for regular EAM
49 * x_shutdown_end_date Shutdown information for regular EAM
50 * x_quantity Number of items for inventory
51 * x_update_meter default to fnd_api.g_true
52 * whether to update meters
53 * x_commit default to fnd_api.g_true
54 * whether to commit the changes to DB
55 * x_attribute_category For descriptive flex field
56 * x_attribute1-15 For descriptive flex field
57 * errCode OUT 0 if procedure success, 1 otherwise
58 * errMsg OUT NOCOPY The informative error message
59 *
60 *
61 * This procedure will insert all the required information to
62 * EAM_JOB_COMPLETION_TXNS table for tracking purpose (history), and updated
63 * some information in WIP_DISCRETE_JOBS table(status_type, last_update_date,
64 * last_updated_by).
65 *
66 * Complete transaction:
67 *
68 * We can complete all the jobs, but the one that has status
69 * 'Pending Routing Load'(10), 'Failed Routing Load'(11), 'Complete'(4),
70 * 'Pending Bill Load'(8), and 'Failed Bill Load'(9). At the end of procudure,
71 * we just change the STATUS_TYPE in WIP_DISCRETE_JOBS to be 'Complete'(4).
72 *
73 * <Regular EAM Work Order>
74 *
75 * We need to check whether the child job(rebuild job) already closed or not.
76 * We cannot complete regular EAM work order if the coresponding
77 * rebuild work order did not complete and there is no material issue. On the
78 * other hand, if the corresponding rebuild work order is a manual job with
79 * material issue, we can complete the corresponding parent job without
80 * completing all the child jobs. Call eam_pm_utils.update_pm_when_complete
81 * (p_org_id,p_wip_entity_id, p_completion_date) to update meter.
82 * p_completion_date is equal to x_actual_end_date. If x_qa_colletion_id is
83 * not null, call QA_RESULT_GRP.ENABLE to process QA data. If
84 * x_shutdown_start_date and x_shutdown_end_date are not null, insert shutdown
85 * history to EAM_ASSET_STATUS_HISTORY.
86 *
87 * <Rebuild Work Order>
88 *
89 * If there is material issue , this procedure will return the completed
90 * rebuildable item to inventory (updating the information in
91 * MTL_MATERIAL_TRANSACTIONS_TEMP, MTL_SERIAL_NUMBERS_TEMP, and
92 * MTL_TRANSACTION_LOTS_TEMP table by using the completion location
93 * information provided by the user(Subinventory, Locator, Lot Number,
94 * Serial Number)and call inventory processor to process assembly completion).
95 *
96 * Uncomplete transaction:
97 *
98 * We can uncomplete only the jobs that has status 'Complete'(4). At the end
99 * of this procudure, we just change the STATUS_TYPE in WIP_DISCRETE_JOBS to
100 * be 'Released(3)'.
101 *
102 * <Regular EAM Work Order>
103 *
104 * We don't have to check much for Regular EAM Work Order, but we need to call
105 * eam_pm_utils.update_pm_when_uncomplete (p_org_id,p_wip_entity_id,
106 * p_completion_date) to update meter. p_completion_date is equal to
107 * x_actual_end_date. If x_qa_colletion_id is not null, call
108 * QA_RESULT_GRP.ENABLE to process QA data. If x_shutdown_start_date and
109 * x_shutdown_end_date are not null, insert shutdown history to
110 * EAM_ASSET_STATUS_HISTORY.
111 *
112 * <Rebuild Work Order>
113 *
114 * If there is material issue, we need to deal with Inventory Assembly Return
115 * to get the item back from Inventory. If there is no material issue, we need
116 * to check whether the corresponding parent job(regular EAM) already completed
117 * or not. If the parent job already complete, return informative error. We
118 * cannot use recursive to uncomplete the parent jobs.
119 *
120 * Required Arguments: (For both Completion and Uncomplete)
121 *
122 * x_wip_entity_id
123 * x_rebuild_jobs
124 * x_transaction_type
125 * x_transaction_date
126 * x_actual_start_date
127 * x_actual_end_date
128 * x_actual_duration
129 *
130 * Arguments: (For regular EAM work order only)
131 * x_reference
132 * x_qa_collection_id
133 * x_shutdown_start_date
134 * x_shutdown_end_date
135 *
136 * Arguments: (For rebuild work order with material issue only)
137 * x_subinventory
138 * x_locator_id
139 * x_lot_number
140 * x_serial_number
141 *
142 * Arguments: (For concurrent processing)
143 * x_request_id
144 * x_application_id
145 * x_program_id
146 *
147 ***************************************************************************/
148 /*#
149 * Complete/UnComplete maintenance work order.
150 * The maintenance work order can be a regular work order or a rebuild work order.
151 * While completing the regular work order user can provide the Asset Shutdown history information.
152 * While completing the rebuild work order with material issue ,user can specify return location by specifying subinventory/locator/lot/serial information.
153 * This API can not be use to enter the collection plan results and meter readings while completing the work order.
154 * @param x_wip_entity_id Work order identifier
155 * @param x_rebuild_jobs Type of Work order 'N'(Normal) / 'Y' (Rebuild)
156 * @param x_transaction_type Type of transaction '1'(complete) / '2' (uncomplete)
157 * @param x_transaction_date Date of the complete or uncomplete transaction
158 * @param x_user_id User Id
159 * @param x_request_id Standard Who Column used for concurrent process
160 * @param x_application_id Standard Who Column used for concurrent process
161 * @param x_program_id Standard Who Column used for concurrent process
162 * @param x_reconcil_code Reconciliation code used to store the result of a performed maintenance task
163 * @param x_actual_start_date Actual start date of work order
164 * @param x_actual_end_date Actual end date of work order
165 * @param x_actual_duration Duration of work order
166 * @param x_inventory_item_info A table of EAM_WorkOrderTransactions_PUB.Inventory_Item_Tbl_Type Required for rebuild work order with material issue (Contains subinventory,locator,lot,serial information)
167 * @param x_reference Reference Required for regular EAM work order only
168 * @param x_qa_collection_id Quality Collection Plan Identifier Required for regular EAM work order only
169 * @param x_shutdown_start_date Shutdown start date Required for regular EAM work order only
170 * @param x_shutdown_end_date Shutdown end date Required for regular EAM work order only
171 * @param x_commit Parameter to indicate whether to commit the changes to Database 'T'(true) / 'F'(false)
172 * @param x_attribute_category Descriptive flexfield structure defining column
173 * @param x_attribute1 Descriptive Flexfield Segment
174 * @param x_attribute2 Descriptive Flexfield Segment
175 * @param x_attribute3 Descriptive Flexfield Segment
176 * @param x_attribute4 Descriptive Flexfield Segment
177 * @param x_attribute5 Descriptive Flexfield Segment
178 * @param x_attribute6 Descriptive Flexfield Segment
179 * @param x_attribute7 Descriptive Flexfield Segment
180 * @param x_attribute8 Descriptive Flexfield Segment
181 * @param x_attribute9 Descriptive Flexfield Segment
182 * @param x_attribute10 Descriptive Flexfield Segment
183 * @param x_attribute11 Descriptive Flexfield Segment
184 * @param x_attribute12 Descriptive Flexfield Segment
185 * @param x_attribute13 Descriptive Flexfield Segment
186 * @param x_attribute14 Descriptive Flexfield Segment
187 * @param x_attribute15 Descriptive Flexfield Segment
188 * @param errCode 0 if procedure success, 1 otherwise
189 * @param errMsg The informative error message
190 * @rep:scope public
191 * @rep:lifecycle active
192 * @rep:displayname Maintenance Work Completion
193 */
194
195 PROCEDURE complete_work_order_generic(
196 x_wip_entity_id IN NUMBER,
197 x_rebuild_jobs IN VARCHAR2,
198 x_transaction_type IN NUMBER,
199 x_transaction_date IN DATE,
200 x_user_id IN NUMBER := fnd_global.user_id,
201 x_request_id IN NUMBER := null,
202 x_application_id IN NUMBER := null,
203 x_program_id IN NUMBER := null,
204 x_reconcil_code IN VARCHAR2 := null,
205 x_actual_start_date IN DATE,
206 x_actual_end_date IN DATE,
207 x_actual_duration IN NUMBER,
208 x_inventory_item_info IN EAM_WorkOrderTransactions_PUB.Inventory_Item_Tbl_Type := INVENTORY_ITEM_NULL,
209 x_reference IN VARCHAR2 := null,
210 x_qa_collection_id IN NUMBER := null,
211 x_shutdown_start_date IN DATE := null,
212 x_shutdown_end_date IN DATE := null,
213 x_commit IN VARCHAR2 := fnd_api.g_false,
214 x_attribute_category IN VARCHAR2 := null,
215 x_attribute1 IN VARCHAR2 := null,
216 x_attribute2 IN VARCHAR2 := null,
217 x_attribute3 IN VARCHAR2 := null,
218 x_attribute4 IN VARCHAR2 := null,
219 x_attribute5 IN VARCHAR2 := null,
220 x_attribute6 IN VARCHAR2 := null,
221 x_attribute7 IN VARCHAR2 := null,
222 x_attribute8 IN VARCHAR2 := null,
223 x_attribute9 IN VARCHAR2 := null,
224 x_attribute10 IN VARCHAR2 := null,
225 x_attribute11 IN VARCHAR2 := null,
226 x_attribute12 IN VARCHAR2 := null,
227 x_attribute13 IN VARCHAR2 := null,
228 x_attribute14 IN VARCHAR2 := null,
229 x_attribute15 IN VARCHAR2 := null,
230 errCode OUT NOCOPY NUMBER,
231 errMsg OUT NOCOPY VARCHAR2) ;
232 -- x_statement OUT NUMBER ;
233
234 PROCEDURE complete_work_order(
235 x_wip_entity_id IN NUMBER,
236 x_rebuild_jobs IN VARCHAR2,
237 x_transaction_type IN NUMBER,
238 x_transaction_date IN DATE,
239 x_user_id IN NUMBER := fnd_global.user_id,
240 x_request_id IN NUMBER := null,
241 x_application_id IN NUMBER := null,
242 x_program_id IN NUMBER := null,
243 x_reconcil_code IN VARCHAR2 := null,
244 x_actual_start_date IN DATE,
245 x_actual_end_date IN DATE,
246 x_actual_duration IN NUMBER,
247 x_subinventory IN VARCHAR2 := null,
248 x_locator_id IN NUMBER := null,
249 x_lot_number IN VARCHAR2 := null,
250 x_serial_number IN VARCHAR2 := null,
251 x_reference IN VARCHAR2 := null,
252 x_qa_collection_id IN NUMBER := null,
253 x_shutdown_start_date IN DATE := null,
254 x_shutdown_end_date IN DATE := null,
255 x_commit IN VARCHAR2 := fnd_api.g_false,
256 x_attribute_category IN VARCHAR2 := null,
257 x_attribute1 IN VARCHAR2 := null,
258 x_attribute2 IN VARCHAR2 := null,
259 x_attribute3 IN VARCHAR2 := null,
260 x_attribute4 IN VARCHAR2 := null,
261 x_attribute5 IN VARCHAR2 := null,
262 x_attribute6 IN VARCHAR2 := null,
263 x_attribute7 IN VARCHAR2 := null,
264 x_attribute8 IN VARCHAR2 := null,
265 x_attribute9 IN VARCHAR2 := null,
266 x_attribute10 IN VARCHAR2 := null,
267 x_attribute11 IN VARCHAR2 := null,
268 x_attribute12 IN VARCHAR2 := null,
269 x_attribute13 IN VARCHAR2 := null,
270 x_attribute14 IN VARCHAR2 := null,
271 x_attribute15 IN VARCHAR2 := null,
272 errCode OUT NOCOPY NUMBER,
273 errMsg OUT NOCOPY VARCHAR2) ;
274 -- x_statement OUT NUMBER ;
275
276
277
278
279 -- This method will be called via Forms
280
281 PROCEDURE complete_work_order_form(
282 x_wip_entity_id IN NUMBER,
283 x_rebuild_jobs IN VARCHAR2,
284 x_transaction_type IN NUMBER,
285 x_transaction_date IN DATE,
286 x_user_id IN NUMBER := fnd_global.user_id,
287 x_request_id IN NUMBER := null,
288 x_application_id IN NUMBER := null,
289 x_program_id IN NUMBER := null,
290 x_reconcil_code IN VARCHAR2 := null,
291 x_actual_start_date IN DATE,
292 x_actual_end_date IN DATE,
293 x_actual_duration IN NUMBER,
294 x_subinventory IN VARCHAR2 := null,
295 x_locator_id IN NUMBER := null,
296 x_lot_number IN VARCHAR2 := null,
297 x_serial_number IN VARCHAR2 := null,
298 x_reference IN VARCHAR2 := null,
299 x_qa_collection_id IN NUMBER := null,
300 x_shutdown_start_date IN DATE := null,
301 x_shutdown_end_date IN DATE := null,
302 x_attribute_category IN VARCHAR2 := null,
303 x_attribute1 IN VARCHAR2 := null,
304 x_attribute2 IN VARCHAR2 := null,
305 x_attribute3 IN VARCHAR2 := null,
306 x_attribute4 IN VARCHAR2 := null,
307 x_attribute5 IN VARCHAR2 := null,
308 x_attribute6 IN VARCHAR2 := null,
309 x_attribute7 IN VARCHAR2 := null,
310 x_attribute8 IN VARCHAR2 := null,
311 x_attribute9 IN VARCHAR2 := null,
312 x_attribute10 IN VARCHAR2 := null,
313 x_attribute11 IN VARCHAR2 := null,
314 x_attribute12 IN VARCHAR2 := null,
315 x_attribute13 IN VARCHAR2 := null,
316 x_attribute14 IN VARCHAR2 := null,
317 x_attribute15 IN VARCHAR2 := null,
318 errCode OUT NOCOPY NUMBER,
319 errMsg OUT NOCOPY VARCHAR2 );
323 PROCEDURE complete_work_order_commit(
320 -- x_statement OUT NUMBER ;
321
322 /* Added for bug# 3238163 */
324 x_wip_entity_id IN NUMBER,
325 x_rebuild_jobs IN VARCHAR2,
326 x_transaction_type IN NUMBER,
327 x_transaction_date IN DATE,
328 x_user_id IN NUMBER := fnd_global.user_id,
329 x_request_id IN NUMBER := null,
330 x_application_id IN NUMBER := null,
331 x_program_id IN NUMBER := null,
332 x_reconcil_code IN VARCHAR2 := null,
333 x_commit IN VARCHAR2 := fnd_api.g_false,
334 x_actual_start_date IN DATE,
335 x_actual_end_date IN DATE,
336 x_actual_duration IN NUMBER,
337 x_subinventory IN VARCHAR2 := null,
338 x_locator_id IN NUMBER := null,
339 x_lot_number IN VARCHAR2 := null,
340 x_serial_number IN VARCHAR2 := null,
341 x_reference IN VARCHAR2 := null,
342 x_qa_collection_id IN NUMBER := null,
343 x_shutdown_start_date IN DATE := null,
344 x_shutdown_end_date IN DATE := null,
345 x_attribute_category IN VARCHAR2 := null,
346 x_attribute1 IN VARCHAR2 := null,
347 x_attribute2 IN VARCHAR2 := null,
348 x_attribute3 IN VARCHAR2 := null,
349 x_attribute4 IN VARCHAR2 := null,
350 x_attribute5 IN VARCHAR2 := null,
351 x_attribute6 IN VARCHAR2 := null,
352 x_attribute7 IN VARCHAR2 := null,
353 x_attribute8 IN VARCHAR2 := null,
354 x_attribute9 IN VARCHAR2 := null,
355 x_attribute10 IN VARCHAR2 := null,
356 x_attribute11 IN VARCHAR2 := null,
357 x_attribute12 IN VARCHAR2 := null,
358 x_attribute13 IN VARCHAR2 := null,
359 x_attribute14 IN VARCHAR2 := null,
360 x_attribute15 IN VARCHAR2 := null,
361 errCode OUT NOCOPY NUMBER,
362 errMsg OUT NOCOPY VARCHAR2 );
363
364 PROCEDURE lock_row(
365 p_wip_entity_id IN NUMBER,
366 p_organization_id IN NUMBER,
367 p_rebuild_item_id IN NUMBER,
368 p_parent_wip_entity_id IN NUMBER,
369 p_asset_number IN VARCHAR2,
370 p_asset_group_id IN NUMBER,
371 p_manual_rebuild_flag IN VARCHAR2,
372 p_asset_activity_id IN NUMBER,
373 p_status_type IN NUMBER,
374 x_return_status OUT NOCOPY NUMBER,
375 x_msg_count OUT NOCOPY NUMBER,
376 x_msg_data OUT NOCOPY VARCHAR2);
377
378 END eam_completion;