[Home] [Help]
PACKAGE BODY: APPS.WIP_DISCRETE_WS_MOVE
Source
1 PACKAGE BODY wip_discrete_ws_move AS
2 /* $Header: wipdsmvb.pls 120.2 2006/09/19 06:31:24 paho noship $ */
3
4 procedure explodeComponents(p_jobID in number,
5 p_orgID in number,
6 p_moveQty in number,
7 p_fromOp in number,
8 p_fromStep in number,
9 p_toOp in number,
10 p_toStep in number,
11 p_txnType in number,
12 x_moveTxnID out nocopy number,
13 x_cplTxnID out nocopy number,
14 x_txnHeaderID out nocopy number,
15 x_compHeaderID out nocopy number,
16 x_batchID out nocopy number,
17 x_lotEntryType out nocopy number,
18 x_compInfo out nocopy system.wip_lot_serial_obj_t,
19 x_mtlMode out nocopy number,
20 x_periodID out nocopy number,
21 x_returnStatus out nocopy varchar2,
22 x_errMessage out nocopy varchar2) is
23 openPastPeriod boolean;
24 periodID number;
25 begin
26 select wip_transactions_s.nextval into x_moveTxnID from dual;
27 select mtl_material_transactions_s.nextval into x_cplTxnID from dual;
28 select mtl_material_transactions_s.nextval into x_batchID from dual;
29 select mtl_material_transactions_s.nextval into x_txnHeaderID from dual;
30
31 openPastPeriod := false;
32 x_mtlMode := nvl(to_number(fnd_profile.value('TRANSACTION_PROCESS_MODE')),
33 WIP_CONSTANTS.ONLINE);
34 if ( x_mtlMode = WIP_CONSTANTS.FORM_LEVEL ) then
35 x_mtlMode := nvl(to_number(fnd_profile.value('WIP_SHOP_FLOOR_MTL_TRANSACTION')),
36 WIP_CONSTANTS.ONLINE);
37 end if;
38
39 if ( x_mtlMode <> WIP_CONSTANTS.ONLINE ) then
40 select mtl_material_transactions_s.nextval into x_compHeaderID from dual;
41 else
42 x_compHeaderID := x_txnHeaderID;
43 end if;
44
45 -- derive the accounting period stuff by calling inv routine
46 invttmtx.tdatechk(
47 org_id => p_orgID,
48 transaction_date => sysdate,
49 period_id => periodID,
50 open_past_period => openPastPeriod);
51
52 if (periodID = -1 or periodID = 0) then
53 fnd_message.set_name(
54 application => 'INV',
55 name => 'INV_NO_OPEN_PERIOD');
56 x_returnStatus := fnd_api.g_ret_sts_error;
57 x_errMessage := fnd_message.get;
58 return;
59 end if;
60
61 x_periodID := periodID;
62
63 wma_move.backflush(p_jobID => p_jobID,
64 p_orgID => p_orgID,
65 p_childMoveID => -1,
66 p_moveID => x_moveTxnID,
67 p_ocQty => 0,
68 p_moveQty => p_moveQty,
69 p_txnDate => sysdate,
70 p_txnHdrID => x_txnHeaderID,
71 p_fm_op => p_fromOp,
72 p_fm_step => p_fromStep,
73 p_to_op => p_toOp,
74 p_to_step => p_toStep,
75 p_cmpTxnID => x_cplTxnID,
76 p_txnType => p_txnType,
77 p_objectID => -1,
78 x_lotEntryType => x_lotEntryType,
79 x_compInfo => x_compInfo,
80 x_returnStatus => x_returnStatus,
81 x_errMessage => x_errMessage);
82
83 end explodeComponents;
84
85
86 procedure processMove(moveData in MoveData,
87 x_returnStatus out nocopy varchar2,
88 x_errMessage out nocopy varchar2) is
89 qaCollectionID number;
90 processStatus number;
91 groupID number;
92 primaryItemID number;
93 lineID number;
94
95 fmOpCode varchar2(5);
96 fmDeptID number;
97 fmDeptCode varchar2(11);
98 fmPrevOpSeq number;
99 fmNextOpSeq number;
100 fmOpExists boolean;
101 toOpCode varchar2(5);
102 toDeptID number;
103 toDeptCode varchar2(11);
104 toPrevOpSeq number;
105 toNextOpSeq number;
106 toOpExists boolean;
107
108 l_totalNum number;
109 l_returnStatus VARCHAR2(1);
110 l_logLevel NUMBER;
111 l_params wip_logger.param_tbl_t;
112 begin
113 x_returnStatus := fnd_api.g_ret_sts_success;
114 l_totalNum := 0;
115 l_logLevel := to_number(fnd_log.g_current_runtime_level);
116 savepoint dsmove1;
117
118 if ( l_logLevel <= wip_constants.trace_logging) then
119 l_params(1).paramName := 'not printing params';
120 l_params(1).paramValue := ' ';
121 wip_logger.entryPoint(p_procName => 'wip_discrete_ws_move.insertMoveRecord',
122 p_params => l_params,
123 x_returnStatus => l_returnStatus);
124 end if;
125
126 qaCollectionID := moveData.qaCollectionID;
127 if ( moveData.qaCollectionID is not null ) then
128 select count(*) into l_totalNum
129 from qa_results
130 where collection_id = moveData.qaCollectionID;
131 if ( l_totalNum = 0 ) then
132 qaCollectionID := null;
133 end if;
134 end if;
135
136 if ( moveData.txnMode = 1 ) then
137 groupID := moveData.txnID;
138 processStatus := 2; -- running
139 else
140 groupID := null;
141 processStatus := 1;
142 end if;
143
144 select primary_item_id, line_id
145 into primaryItemID, lineID
146 from wip_discrete_jobs
147 where organization_id = moveData.orgID
148 and wip_entity_id = moveData.wipEntityID;
149
150 wip_operations_info.derive_info(
151 p_org_id => moveData.orgID,
152 p_wip_entity_id => moveData.wipEntityID,
153 p_first_schedule_id => null,
154 p_operation_seq_num => moveData.fmOp,
155 p_operation_code => fmOpCode,
156 p_department_id => fmDeptID,
157 p_department_code => fmDeptCode,
158 p_prev_op_seq_num => fmPrevOpSeq,
159 p_next_op_seq_num => fmNextOpSeq,
160 p_operation_exists => fmOpExists);
161
162 wip_operations_info.derive_info(
163 p_org_id => moveData.orgID,
164 p_wip_entity_id => moveData.wipEntityID,
165 p_first_schedule_id => null,
166 p_operation_seq_num => moveData.toOp,
167 p_operation_code => toOpCode,
168 p_department_id => toDeptID,
169 p_department_code => toDeptCode,
170 p_prev_op_seq_num => toPrevOpSeq,
171 p_next_op_seq_num => toNextOpSeq,
172 p_operation_exists => toOpExists);
173
174
175 insert into wip_move_txn_interface
176 (transaction_id,
177 group_id,
178 source_code,
179 last_update_date,
180 last_updated_by,
181 creation_date,
182 created_by,
183 process_phase,
184 process_status,
185 transaction_type,
186 organization_id,
187 wip_entity_id,
188 entity_type,
189 transaction_date,
190 acct_period_id,
191 fm_operation_seq_num,
192 fm_intraoperation_step_type,
193 to_operation_seq_num,
194 to_intraoperation_step_type,
195 transaction_quantity,
196 transaction_uom,
197 scrap_account_id,
198 qa_collection_id,
199 primary_item_id,
200 line_id,
201 fm_operation_code,
202 fm_department_id,
203 fm_department_code,
204 to_operation_code,
205 to_department_id,
206 to_department_code,
207 primary_quantity,
208 primary_uom)
209 values(moveData.txnID,
210 groupID,
211 'Discrete Station Move',
212 sysdate,
213 fnd_global.user_id,
214 sysdate,
215 fnd_global.user_id,
216 2, -- move processing
217 processStatus,
218 moveData.txnType,
219 moveData.orgID,
220 moveData.wipEntityID,
221 1,
222 sysdate,
223 moveData.periodID,
224 moveData.fmOp,
225 moveData.fmStep,
226 moveData.toOp,
227 moveData.toStep,
228 moveData.txnQty,
229 moveData.txnUOM,
230 moveData.scrapAcctID,
231 qaCollectionID,
232 primaryItemID,
233 lineID,
234 fmOpCode,
235 fmDeptID,
236 fmDeptCode,
237 toOpCode,
238 toDeptID,
239 toDeptCode,
240 moveData.txnQty,
241 moveData.txnUOM
242 );
243
244 if ( moveData.txnMode = WIP_CONSTANTS.BACKGROUND ) then
245 return;
246 end if;
247
248 -- process online move transactions
249 if ( moveData.compHeaderID is not null ) then
250 wip_mtlTempProc_priv.validateInterfaceTxns(
251 p_txnHdrID => moveData.compHeaderID,
252 p_addMsgToStack => fnd_api.g_true,
253 p_rollbackOnErr => fnd_api.g_true,
254 x_returnStatus => l_returnStatus);
255 end if;
256
257 if ( l_returnStatus <> fnd_api.g_ret_sts_success) then
258 x_returnStatus := fnd_api.g_ret_sts_error;
259 wip_utilities.get_message_stack(p_msg => x_errMessage);
260 return;
261 end if;
262
263 if ( moveData.assyHeaderID is not null AND moveData.assyHeaderID <> moveData.compHeaderID ) then
264 wip_mtlTempProc_priv.validateInterfaceTxns(
265 p_txnHdrID => moveData.assyHeaderID,
266 p_addMsgToStack => fnd_api.g_true,
267 p_rollbackOnErr => fnd_api.g_true,
268 x_returnStatus => l_returnStatus);
269 if ( l_returnStatus <> fnd_api.g_ret_sts_success) then
270 x_returnStatus := fnd_api.g_ret_sts_error;
271 wip_utilities.get_message_stack(p_msg => x_errMessage);
272 return;
273 end if;
274 end if;
275
276 wip_movProc_priv.processIntf(
277 p_group_id => groupID,
278 p_proc_phase => WIP_CONSTANTS.MOVE_PROC,
279 p_time_out => 0,
280 p_move_mode => WIP_CONSTANTS.ONLINE,
281 p_bf_mode => WIP_CONSTANTS.ONLINE,
282 p_mtl_mode => moveData.mtlMode,
283 p_endDebug => fnd_api.g_true,
284 p_initMsgList => fnd_api.g_true,
285 p_insertAssy => fnd_api.g_false,
286 p_do_backflush => fnd_api.g_false,
287 p_assy_header_id => moveData.assyHeaderID,
288 p_mtl_header_id => moveData.compHeaderID,
289 x_returnStatus => x_returnStatus);
290 if ( x_returnStatus <> fnd_api.g_ret_sts_success ) then
291 wip_utilities.get_message_stack(p_msg => x_errMessage);
292 rollback to dsmove1;
293 end if;
294
295 exception
296 when fnd_api.g_exc_unexpected_error THEN
297 rollback to dsmove1;
298 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
299 wip_utilities.get_message_stack(p_msg => x_errMessage);
300 if (l_logLevel <= wip_constants.trace_logging) then
301 wip_logger.exitPoint(p_procName => 'wip_discrete_ws_move.insertMoveRecord',
302 p_procReturnStatus => x_returnStatus,
303 p_msg => x_errMessage,
304 x_returnStatus => l_returnStatus);
305 end if;
306
307 when others then
308 rollback to dsmove1;
309 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
310 fnd_message.set_name ('WIP', 'GENERIC_ERROR');
311 fnd_message.set_token ('PROCEDURE', 'wip_discrete_ws_move.processMove');
312 fnd_message.set_token ('ERROR', SQLCODE || ' ' || SQLERRM);
313 x_errMessage := fnd_message.get;
314 if (l_logLevel <= wip_constants.trace_logging) then
315 wip_logger.exitPoint(p_procName => 'wip_discrete_ws_move.processMove',
316 p_procReturnStatus => x_returnStatus,
317 p_msg => x_errMessage,
318 x_returnStatus => l_returnStatus);
319 end if;
320 end processMove;
321
322
323 procedure createLocator(p_orgID in number,
324 p_locatorName in varchar2,
325 p_subinv in varchar2,
326 x_locatorID out nocopy number,
327 x_returnStatus out nocopy varchar2,
328 x_errMessage out nocopy varchar2) is
329 l_msgCount number;
330 l_locExists varchar2(1);
331 begin
332 inv_loc_wms_pub.create_locator(
333 x_return_status => x_returnStatus,
334 x_msg_count => l_msgCount,
335 x_msg_data => x_errMessage,
336 x_inventory_location_id => x_locatorID,
337 x_locator_exists => l_locExists,
338 p_organization_id => p_orgID,
339 p_organization_code => null,
340 p_concatenated_segments => p_locatorName,
341 p_description => null,
342 p_inventory_location_type => 3, --storage locator
343 p_picking_order => null,
344 p_location_maximum_units => null,
345 p_subinventory_code => p_subinv,
346 p_location_weight_uom_code => null,
347 p_max_weight => null,
348 p_volume_uom_code => null,
349 p_max_cubic_area => null,
350 p_x_coordinate => null,
351 p_y_coordinate => null,
352 p_z_coordinate => null,
353 p_physical_location_id => null,
354 p_pick_uom_code => null,
355 p_dimension_uom_code => null,
356 p_length => null,
357 p_width => null,
358 p_height => null,
359 p_status_id => null,
360 p_dropping_order => null);
361 end createLocator;
362
363
364 procedure checkOvershipment(p_orgID in number,
365 p_itemID in number,
366 p_orderLineID in number,
367 p_primaryQty in number,
368 p_primaryUOM in varchar2,
369 x_returnStatus out nocopy varchar2,
370 x_errMessage out nocopy varchar2) is
371 l_wsh_minmax_in_rec wsh_integration.minmaxinrectype;
372 l_wsh_minmax_out_rec wsh_integration.minmaxoutrectype;
373 l_wsh_minmax_inout_rec wsh_integration.minmaxinoutrectype;
374 l_msg_count number;
375 l_msg_data varchar2(2000);
376
377 l_max_rem_primary_qty number;
378 l_inv_primary_rsv_quantity number;
379 begin
380 x_returnStatus := fnd_api.g_ret_sts_success;
381 l_wsh_minmax_in_rec.api_version_number := 1.0;
382 l_wsh_minmax_in_rec.source_code := 'OE';--Fix for Bug 4635597
383 l_wsh_minmax_in_rec.line_id := p_orderLineID;
384
385 wsh_integration.get_min_max_tolerance_quantity(
386 p_in_attributes => l_wsh_minmax_in_rec,
387 p_out_attributes => l_wsh_minmax_out_rec,
388 p_inout_attributes => l_wsh_minmax_inout_rec,
389 x_return_status => x_returnStatus,
390 x_msg_count => l_msg_count,
391 x_msg_data => l_msg_data);
392
393 if ( x_returnStatus <> fnd_api.g_ret_sts_success ) then
394 fnd_message.set_name(application => 'WIP',
395 name => 'WIP_WSH_MINMAX_API_FAILURE');
396 fnd_message.set_token(token => 'ENTITY1',
397 value => substr(l_msg_data, 1, 250),
398 translate => false);
399 x_errMessage := fnd_message.get;
400 return;
401 end if;
402
403 l_max_rem_primary_qty := inv_convert.inv_um_convert(
404 item_id => p_itemID,
405 precision => null,
406 from_quantity => l_wsh_minmax_out_rec.max_remaining_quantity,
407 from_unit => l_wsh_minmax_out_rec.quantity_uom,
408 to_unit => p_primaryUOM,
409 from_name => null,
410 to_name => null);
411
412 select nvl(sum(primary_reservation_quantity), 0)
413 into l_inv_primary_rsv_quantity
414 from mtl_reservations
415 where demand_source_line_id = p_orderLineID
416 and organization_id = p_orgID
417 and supply_source_type_id = 13;
418
419 if ( p_primaryQty > l_max_rem_primary_qty - l_inv_primary_rsv_quantity ) then
420 x_returnStatus := fnd_api.g_ret_sts_error;
421 fnd_message.set_name('WIP', 'WIP_OSHP_TOLERANCE_FAIL');
422 x_errMessage := fnd_message.get;
423 end if;
424
425 end checkOvershipment;
426
427
428 function clientToServerDate(p_date in date) return date is
429 l_dateval varchar2(100);
430 l_date date;
431 begin
432 if ( p_date is null ) then
433 return null;
434 end if;
435 l_dateval := to_char(p_date, fnd_date.outputDT_mask);
436 l_date := fnd_date.displayDT_to_date(l_dateval);
437 return l_date;
438 end clientToServerDate;
439
440
441 function serverToClientDate(p_date in date) return date is
442 l_dateval varchar2(100);
443 l_date date;
444 begin
445 if ( p_date is null ) then
446 return null;
447 end if;
448 l_dateval := fnd_date.date_to_displayDT(p_date);
449 l_date := to_date(l_dateval, fnd_date.outputDT_mask);
450 return l_date;
451 end serverToClientDate;
452
453
454 procedure initTimezone is
455 begin
456 fnd_date.timezones_enabled := true;
457 fnd_date.server_timezone_code := fnd_timezones.get_server_timezone_code;
458 fnd_date.client_timezone_code := fnd_timezones.get_client_timezone_code;
459 if ( fnd_timezones.timezones_enabled = 'N' ) then
460 fnd_date.timezones_enabled := false;
461 end if;
462 end initTimezone;
463
464
465 /* Fix for bug 4568517: New procedure get_prj_loc_lov added.
466 * ==========================================================
467 * Procedure returns a ref cursor containing LOV statement to
468 * be used by discrete workstation code completion locator.
469 * For PJM enabled orgs, the locator will show project number
470 * and task number, and if project/task are passed, the
471 * restriction would be applied.
472 *===========================================================
473 */
474 PROCEDURE get_prj_loc_lov(
475 x_locators OUT NOCOPY t_genref
476 , p_organization_id IN NUMBER
477 , p_subinventory_code IN VARCHAR2
478 , p_restrict_locators_code IN NUMBER
479 , p_inventory_item_id IN NUMBER
480 , p_concatenated_segments IN VARCHAR2
481 , p_transaction_type_id IN NUMBER
482 , p_wms_installed IN VARCHAR2
483 , p_project_id IN NUMBER
484 , p_task_id IN NUMBER
485 ) IS
486
487 l_ispjm_org VARCHAR2(1);
488 l_sub_type NUMBER;
489 BEGIN
490 BEGIN
491 SELECT nvl(PROJECT_REFERENCE_ENABLED,'N')
492 INTO l_ispjm_org
493 FROM pjm_org_parameters
494 WHERE organization_id=p_organization_id;
495 EXCEPTION
496 WHEN NO_DATA_FOUND THEN
497 l_ispjm_org:='N';
498 END;
499
500 BEGIN
501 SELECT Nvl(subinventory_type,1)
502 INTO l_sub_type
503 FROM mtl_secondary_inventories
504 WHERE secondary_inventory_name = p_subinventory_code
505 AND organization_id = p_organization_id;
506 EXCEPTION
507 WHEN OTHERS THEN
508 l_sub_type := 1;
509 END;
510
511 IF l_ispjm_org='N' THEN /*Non PJM Org*/
512 IF p_Restrict_Locators_Code = 1 AND l_sub_type = 1 THEN --Locators restricted to predefined list
513 OPEN x_Locators FOR
514 SELECT a.inventory_location_id,
515 a.concatenated_segments,
516 nvl( a.description, -1)
517 FROM mtl_item_locations_kfv a,mtl_secondary_locators b
518 WHERE b.organization_id = p_Organization_Id
519 AND b.inventory_item_id = p_Inventory_Item_Id
520 AND nvl(a.disable_date, trunc(sysdate+1)) > trunc(sysdate)
521 AND b.subinventory_code = p_Subinventory_Code
522 AND a.inventory_location_id = b.secondary_locator
523 AND a.concatenated_segments LIKE (p_concatenated_segments)
524 AND inv_material_status_grp.is_status_applicable
525 ( p_wms_installed,
526 NULL,
527 p_transaction_type_id,
528 NULL,
529 NULL,
530 p_Organization_Id,
531 p_Inventory_Item_Id,
532 p_Subinventory_Code,
533 a.inventory_location_id,
534 NULL,
535 NULL,
536 'L') = 'Y'
537 ORDER BY 2;
538
539 ELSE --Locators not restricted
540 OPEN x_Locators FOR
541 SELECT inventory_location_id,
542 concatenated_segments,
543 description
544 FROM mtl_item_locations_kfv
545 WHERE organization_id = p_Organization_Id
546 AND subinventory_code = p_Subinventory_Code
547 AND nvl(disable_date, trunc(sysdate+1)) > trunc(sysdate)
548 AND concatenated_segments LIKE (p_concatenated_segments )
549 AND inv_material_status_grp.is_status_applicable
550 ( p_wms_installed,
551 NULL,
552 p_transaction_type_id,
553 NULL,
554 NULL,
555 p_Organization_Id,
556 p_Inventory_Item_Id,
557 p_Subinventory_Code,
558 inventory_location_id,
559 NULL,
560 NULL,
561 'L') = 'Y'
562 ORDER BY 2;
563 END IF;
564 ELSE /*PJM org*/
565 IF p_Restrict_Locators_Code = 1 AND l_sub_type = 1 THEN --Locators restricted to predefined list
566 OPEN x_Locators FOR
567 SELECT a.inventory_location_id,
568 inv_project.get_locator(a.inventory_location_id,
569 a.organization_id) concatenated_segments,
570 nvl( a.description, -1)
571 FROM mtl_item_locations_kfv a,mtl_secondary_locators b
572 WHERE b.organization_id = p_Organization_Id
573 AND b.inventory_item_id = p_Inventory_Item_Id
574 AND nvl(a.disable_date, trunc(sysdate+1)) > trunc(sysdate)
575 AND b.subinventory_code = p_Subinventory_Code
576 AND a.inventory_location_id = b.secondary_locator
577 AND a.concatenated_segments like (p_concatenated_segments )
578 AND nvl(a.project_id,-1) = nvl(p_project_id, -1)
579 AND nvl(a.task_id, -1) = nvl(p_task_id, -1)
580 AND inv_material_status_grp.is_status_applicable
581 ( p_wms_installed,
582 NULL,
583 p_transaction_type_id,
584 NULL,
585 NULL,
586 p_Organization_Id,
587 p_Inventory_Item_Id,
588 p_Subinventory_Code,
589 a.inventory_location_id,
590 NULL,
591 NULL,
592 'L') = 'Y'
593 ORDER BY 2;
594 ELSE --Locators not restricted
595 OPEN x_Locators FOR
596 SELECT inventory_location_id,
597 inv_project.get_locator(inventory_location_id,
598 organization_id) concatenated_segments,
599 description
600 FROM mtl_item_locations_kfv
601 WHERE organization_id = p_Organization_Id
602 AND subinventory_code = p_Subinventory_Code
603 AND nvl(disable_date, trunc(sysdate+1)) > trunc(sysdate)
604 AND concatenated_segments LIKE (p_concatenated_segments )
605 AND nvl(project_id,-1) = nvl(p_project_id, -1)
606 AND nvl(task_id, -1) = nvl(p_task_id, -1)
607 AND inv_material_status_grp.is_status_applicable
608 ( p_wms_installed,
609 NULL,
610 p_transaction_type_id,
611 NULL,
612 NULL,
613 p_Organization_Id,
614 p_Inventory_Item_Id,
615 p_Subinventory_Code,
616 inventory_location_id,
617 NULL,
618 NULL,
619 'L') = 'Y'
620 ORDER BY 2;
621 END IF;
622 END IF;
623 END get_prj_loc_lov;
624
625 END wip_discrete_ws_move;