[Home] [Help]
PACKAGE BODY: APPS.WIP_CPLPROC_PRIV
Source
1 package body wip_cplProc_priv as
2 /* $Header: wipcplpb.pls 120.12 2007/09/17 19:53:45 vjambhek ship $ */
3
4 ---------------
5 --private types
6 ---------------
7 type num_tbl_t is table of number;
8 type char_tbl_t is table of varchar2(3);
9 type rowid_tbl_t is table of varchar2(18);
10 type date_tbl_t is table of date;
11
12 type schedule_rec_t is record(repSchedID NUMBER,
13 bomRev VARCHAR2(3),
14 startQty NUMBER,
15 toMoveQty NUMBER);
16
17 ----------------------
18 --forward declarations
19 ----------------------
20 procedure fillCplParamTbl(p_cplRec IN completion_rec_t,
21 x_params OUT NOCOPY wip_logger.param_tbl_t);
22
23 procedure processRepetitive(p_cplRec IN completion_rec_t,
24 p_txnTmpID IN NUMBER,
25 x_returnStatus OUT NOCOPY VARCHAR2);
26
27 procedure processDiscrete(p_cplRec IN completion_rec_t,
28 p_txnTmpID IN NUMBER,
29 x_serialStartOp OUT NOCOPY NUMBER,
30 x_returnStatus OUT NOCOPY VARCHAR2);
31
32 ---------------------------
33 --public/private procedures
34 ---------------------------
35 procedure processTemp(p_txnTmpID IN NUMBER,
36 p_initMsgList IN VARCHAR2,
37 p_endDebug IN VARCHAR2,
38 x_returnStatus OUT NOCOPY VARCHAR2) is
39
40 l_cplRec completion_rec_t;
41 l_wipEntityType NUMBER;
42 l_params wip_logger.param_tbl_t;
43 l_returnStatus VARCHAR2(1);
44 l_retValue VARCHAR2(10);
45 l_msgCount NUMBER;
46 l_errMsg VARCHAR2(240);
47 l_msgData VARCHAR2(4000);
48 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
49 l_serialStartOp NUMBER;
50
51 /* Bug 5708242 */
52 l_primaryCostMethod NUMBER;
53 l_errNum NUMBER;
54 l_cstRetVal NUMBER;
55 begin
56 savepoint wipcplpb20;
57 x_returnStatus := fnd_api.g_ret_sts_success;
58 if(fnd_api.to_boolean(p_initMsgList)) then
59 fnd_msg_pub.initialize;
60 end if;
61
62 if (l_logLevel <= wip_constants.trace_logging) then
63 l_params(1).paramName := 'p_txnTmpID';
64 l_params(1).paramValue := p_txnTmpID;
65 wip_logger.entryPoint(p_procName => 'wip_cplProc_priv.processTemp',
66 p_params => l_params,
67 x_returnStatus => x_returnStatus);
68
69 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
70 raise fnd_api.g_exc_unexpected_error;
71 end if;
72 end if;
73
74 select wip_entity_type,
75 organization_id,
76 transaction_source_id,
77 repetitive_line_id,
78 inventory_item_id, --5
79 transaction_action_id,
80 primary_quantity, --qty is relative to inv, but is also relative to wip in this case (completions are positive)
81 transaction_quantity,
82 transaction_date,
83 completion_transaction_id, --10
84 kanban_card_id,
85 qa_collection_id,
86 operation_seq_num,
87 revision, --14
88 transaction_header_id,
89 transaction_status,
90 overcompletion_transaction_id,
91 overcompletion_primary_qty,
92 last_updated_by,
93 created_by,
94 nvl(content_lpn_id, lpn_id),
95 transaction_mode,
96 move_transaction_id,
97 material_allocation_temp_id
98 into l_cplRec.wipEntityType,
99 l_cplRec.orgID,
100 l_cplRec.wipEntityID,
101 l_cplRec.repLineID,
102 l_cplRec.itemID, --5
103 l_cplRec.txnActionID,
104 l_cplRec.priQty,
105 l_cplRec.txnQty,
106 l_cplRec.txnDate,
107 l_cplRec.cplTxnID, --10
108 l_cplRec.kanbanCardID,
109 l_cplRec.qaCollectionID,
110 l_cplRec.lastOpSeq,
111 l_cplRec.revision,--14
112 l_cplRec.txnHdrID,
113 l_cplRec.txnStatus,
114 l_cplRec.overCplTxnID,
115 l_cplRec.overCplPriQty,
116 l_cplRec.lastUpdBy,
117 l_cplRec.createdBy,
118 l_cplRec.lpnID,
119 l_cplRec.txnMode,
120 l_cplRec.movTxnID,
121 l_cplRec.mtlAlcTmpID
122 from mtl_material_transactions_temp
123 where transaction_temp_id = p_txnTmpID;
124
125 if(l_cplRec.qaCollectionID is not null) then
126 qa_result_grp.enable(p_api_version => 1.0,
127 p_validation_level => 0,
128 p_collection_id => l_cplRec.qaCollectionID,
129 p_return_status => l_returnStatus,
130 p_msg_count => l_msgCount,
131 p_msg_data => l_errMsg);
132 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
133 l_errMsg := 'QA error: ' || l_errMsg;
134 raise fnd_api.g_exc_unexpected_error;
135 end if;
136 end if;
137
138 if(nvl(l_cplRec.overCplPriQty, 0) <> 0) then
139 --ensure the overcompletion txn id is populated
140 if(l_cplRec.overCplTxnID is null) then
141 update mtl_material_transactions_temp
142 set overcompletion_transaction_id = wip_transactions_s.nextval
143 where transaction_temp_id = p_txnTmpID returning overcompletion_transaction_id into l_cplRec.overCplTxnID;
144
145 end if;
146
147 --if the txn mode is online, the form does the over-cpl move as it must do one move for all the
148 --cpl records in the multi-row block. In all other cases, the move is done here.
149 if(l_cplRec.txnMode <> wip_constants.online) then
150 processOverCpl(p_cplRec => l_cplRec,
151 x_returnStatus => x_returnStatus);
152 end if;
153
154 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
155 l_errMsg := 'overcompletion processing errored';
156 raise fnd_api.g_exc_unexpected_error;
157 end if;
158 end if;
159
160 if(l_cplRec.wipEntityType = wip_constants.repetitive) then
161 processRepetitive(p_cplRec => l_cplRec,
162 p_txnTmpID => p_txnTmpID,
163 x_returnStatus => x_returnStatus);
164
165 else
166 processDiscrete(p_cplRec => l_cplRec,
167 p_txnTmpID => p_txnTmpID,
168 x_serialStartOp => l_serialStartOp,
169 x_returnStatus => x_returnStatus);
170
171 end if;
172 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
173 l_errMsg := 'entity specific processing failed';
174 raise fnd_api.g_exc_unexpected_error;
175 end if;
176
177 /* Fix for bug 5708242: Moved the call to cstpacms.validate_move_snap_to_temp() from
178 wipmtivb.pls to this place, to avoid intermittent commits, and to facilitate proper
179 rollback into CST_COMP_SNAP_INTERFACE if exception occurs */
180
181 select primary_cost_method
182 into l_primaryCostMethod
183 from mtl_parameters
184 where organization_id = l_cplRec.orgID;
185
186 if (l_primaryCostMethod in (2,5,6)) then
187 l_cstRetVal := 1;
188 l_cstRetVal := cstpacms.validate_move_snap_to_temp(
189 p_txnTmpID,
190 p_txnTmpID,
191 1, -- for Inventory interface
192 l_cplRec.priQty,
193 l_errNum,
194 l_retValue,
195 l_errMsg);
196 if(l_cstRetVal <> 1) then
197 /* Error message will be populated by the procedure. Just raise exception. */
198 raise fnd_api.g_exc_unexpected_error;
199 end if;
200 end if;
201
202 if(l_logLevel <= wip_constants.trace_logging) then
203 wip_logger.exitPoint(p_procName => 'wip_cplProc_priv.processTemp',
204 p_procReturnStatus => x_returnStatus,
205 p_msg => 'procedure success.',
206 x_returnStatus => l_returnStatus); --discard logging return status
207 end if;
208 if(fnd_api.to_boolean(p_endDebug)) then
209 wip_logger.cleanUp(x_returnStatus => l_returnStatus); --discard logging return status
210 end if;
211 exception
212 when no_data_found then
213 rollback to wipcplpb20;
214 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
215 if (l_logLevel <= wip_constants.trace_logging) then
216 wip_logger.exitPoint(p_procName => 'wip_cplProc_priv.processTemp',
217 p_procReturnStatus => x_returnStatus,
218 p_msg => 'no data found',
219 x_returnStatus => l_returnStatus); --discard logging return status
220 end if;
221 if(fnd_api.to_boolean(p_endDebug)) then
222 wip_logger.cleanUp(x_returnStatus => l_returnStatus); --discard logging return status
223 end if;
224 fnd_message.set_name('WIP', 'INVALID_MMTT_TEMP_ID');
225 fnd_msg_pub.add;
226 if (l_logLevel <= wip_constants.full_logging) then
227 wip_logger.log(SQLERRM, l_returnStatus);
228 end if;
229 when fnd_api.g_exc_error then --could not derive all lot/serial info for components
230 --do *not* rollback. leave the component records in mmtt/mtlt for the caller to query/complete
231 --when the record is processed again, only the material processing and inv txn will occur
232 if (l_logLevel <= wip_constants.trace_logging) then
233 wip_logger.exitPoint(p_procName => 'wip_cplProc_priv.processTemp',
234 p_procReturnStatus => x_returnStatus,
235 p_msg => 'need to collect l/s info',
236 x_returnStatus => l_returnStatus); --discard logging return status
237 end if;
238 if(fnd_api.to_boolean(p_endDebug)) then
239 wip_logger.cleanUp(x_returnStatus => l_returnStatus); --discard logging return status
240 end if;
241 when fnd_api.g_exc_unexpected_error then
242 rollback to wipcplpb20;
243 if (l_logLevel <= wip_constants.trace_logging) then
244 wip_logger.exitPoint(p_procName => 'wip_cplProc_priv.processTemp',
245 p_procReturnStatus => x_returnStatus,
246 p_msg => l_errMsg,
247 x_returnStatus => l_returnStatus); --discard logging return status
248 end if;
249 if(fnd_api.to_boolean(p_endDebug)) then
250 wip_logger.cleanUp(x_returnStatus => l_returnStatus); --discard logging return status
251 end if;
252 when others then
253 rollback to wipcplpb20;
254 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
255 fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_cplProc_priv',
256 p_procedure_name => 'processTemp',
257 p_error_text => SQLERRM);
258 if (l_logLevel <= wip_constants.trace_logging) then
259 wip_logger.exitPoint(p_procName => 'wip_cplProc_priv.processTemp',
260 p_procReturnStatus => x_returnStatus,
261 p_msg => 'unexpected error: ' || SQLERRM,
262 x_returnStatus => l_returnStatus); --discard logging return status
263 end if;
264 if(fnd_api.to_boolean(p_endDebug)) then
265 wip_logger.cleanUp(x_returnStatus => l_returnStatus); --discard logging return status
266 end if;
267 end processTemp;
268
269 procedure processDiscrete(p_cplRec IN completion_rec_t,
270 p_txnTmpID IN NUMBER,
271 x_serialStartOp OUT NOCOPY NUMBER,
272 x_returnStatus OUT NOCOPY VARCHAR2) is
273 l_rowid ROWID;
274 l_qtyCompleted NUMBER;
275 l_jobStatus NUMBER;
276 l_cplDate DATE;
277 l_toMoveQty NUMBER;
278 l_msgCount NUMBER;
279 l_paramCount NUMBER;
280 l_params wip_logger.param_tbl_t;
281 l_errMsg VARCHAR2(240);
282 l_msgTxt VARCHAR2(2000);
283 l_returnStatus VARCHAR2(1);
284 l_qtyAvailToComplete NUMBER;
285 l_nullObj system.wip_component_tbl_t := null;
286 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
287 l_docType NUMBER;
288 oc_primary_qty NUMBER; -- Fix BUG 4869979 (FP 5107900)
289 -- Fixed bug 3678776. We should allow user to overreturn assembly back
290 -- to non-standard job.
291 l_jobType NUMBER;
292 begin
293 if (l_logLevel <= wip_constants.trace_logging) then
294 fillCplParamTbl(p_cplRec => p_cplRec,
295 x_params => l_params);
296 l_paramCount := l_params.count;
297 l_params(l_paramCount + 1).paramName := 'p_txnTmpID';
298 l_params(l_paramCount + 1).paramValue := p_txnTmpID;
299
300 wip_logger.entryPoint(p_procName => 'wip_cplProc_priv.processDiscrete',
301 p_params => l_params,
302 x_returnStatus => x_returnStatus);
303 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
304 raise fnd_api.g_exc_unexpected_error;
305 end if;
306 end if;
307
308 select rowid,
309 quantity_completed,
310 start_quantity - quantity_scrapped - quantity_completed, --remaining qty to complete
311 serialization_start_op,
312 job_type,
313 date_completed
314 into l_rowid,
315 l_qtyCompleted,
316 l_qtyAvailToComplete,
317 x_serialStartOp,
318 l_jobType,
319 l_cplDate
320 from wip_discrete_jobs
321 where wip_entity_id = p_cplRec.wipEntityID
322 for update of quantity_completed nowait;
323
324 if(p_cplRec.txnActionID = wip_constants.cplassy_action) then
325 if(p_cplRec.priQty >= l_qtyAvailToComplete) then
326 l_jobStatus := wip_constants.comp_chrg;
327 l_cplDate := nvl(l_cplDate, p_cplRec.txnDate); --Bug 4864403 Only change date_completed if it is null
328 end if;
329 --allocate completions to sales orders
330 l_errMsg := 'SO allocation failed.'; --set message in case it fails
331 x_returnStatus := fnd_api.g_ret_sts_success;
332 if(p_cplRec.lpnID is null) then
333 wip_so_reservations.allocate_completion_to_so(
334 p_organization_id => p_cplRec.orgID,
335 p_wip_entity_id => p_cplRec.wipEntityID,
336 p_inventory_item_id => p_cplRec.itemID,
337 p_transaction_header_id => p_cplRec.cplTxnID,
338 p_txn_temp_id => p_txnTmpID,
339 x_return_status => x_returnStatus,
340 x_msg_count => l_msgCount,
341 x_msg_data => l_errMsg);
342 end if;
343
344 --if so allocation went ok, then update the kanban card if it exists
345 if(x_returnStatus = fnd_api.g_ret_sts_success and
346 p_cplRec.kanbanCardID is not null) then
347 l_errMsg := 'Kanban update failed.'; --set message in case it fails
348 if(p_cplRec.wipEntityType = wip_constants.lotbased) then
349 l_docType := 8;
350 else
351 l_docType := inv_kanban_pvt.g_doc_type_discrete_job;
352 end if;
353
354 inv_kanban_pvt.UPDATE_CARD_SUPPLY_STATUS
355 (p_kanban_card_id => p_cplRec.kanbanCardID,
356 p_supply_status => inv_kanban_pvt.g_supply_status_full,
357 p_document_type => l_docType,
358 p_document_header_id => p_cplRec.wipEntityID,
359 p_document_detail_id => null,
360 p_replenish_quantity => p_cplRec.priQty,
361 x_return_status => x_returnStatus);
362 end if;
363 else --a return
364 /* Bug 4864403 Change Status and date_completed when quantity_completed is less than start_quantity*/
365 if(l_qtyAvailToComplete > p_cplRec.priQty) then
366 l_jobStatus := wip_constants.released; --make sure the job status gets flipped back to released
367 l_cplDate := NULL;
368 end if;
369 -- Fixed bug 3678776. We should allow user to overreturn assembly back
370 -- to non-standard job.
371 if(abs(p_cplRec.priQty) > l_qtyCompleted AND
372 l_jobType = WIP_CONSTANTS.STANDARD) then
373 fnd_message.set_name('WIP', 'WIP_LESS_OR_EQUAL');
374 fnd_message.set_token('ENTITY1', 'total txn qty-cap', true);
375 fnd_message.set_token('ENTITY2', 'job complete quantity', true);
376 fnd_msg_pub.add;
377 l_errMsg := 'not enough quantity to return';
378 raise fnd_api.g_exc_unexpected_error;
379 end if;
380 --return the so reservation to wip
381 l_errMsg := 'SO return reservation failed'; --set message in case SO return failed
382 wip_so_reservations.return_reservation_to_wip(
383 p_organization_id => p_cplRec.orgID,
384 p_wip_entity_id => p_cplRec.wipEntityID,
385 p_inventory_item_id => p_cplRec.itemID,
386 p_transaction_header_id => p_cplRec.cplTxnID,
387 p_txn_temp_id => p_txnTmpID,
388 x_return_status => x_returnStatus,
389 x_msg_count => l_msgCount,
390 x_msg_data => l_errMsg);
391 end if;
392 --if any of the above failed, make sure the error status is unexpected error and fail
393 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
394 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
395 raise fnd_api.g_exc_unexpected_error;
396 end if;
397
398 update wip_discrete_jobs --increase the qty completed
399 set quantity_completed = l_qtyCompleted + p_cplRec.priQty, --remember txn qty is negative for returns
400 date_completed = l_cplDate,
401 status_type = nvl(l_jobStatus, status_type),
402 last_updated_by = fnd_global.user_id,
403 last_update_date = sysdate,
404 request_id = fnd_global.conc_request_id,
405 program_application_id = fnd_global.prog_appl_id,
406 program_id = fnd_global.conc_program_id,
407 program_update_date = sysdate
408 where rowid = l_rowid;
409
410 --if there exists a routing, lock the last operation
411 if(p_cplRec.lastOpSeq > 0) then
412 select quantity_waiting_to_move, rowid
413 into l_toMoveQty, l_rowid
414 from wip_operations
415 where wip_entity_id = p_cplRec.wipEntityID
416 and operation_seq_num = p_cplRec.lastOpSeq
417 for update of quantity_waiting_to_move nowait;
418
419 if(l_toMoveQty - p_cplRec.priQty < 0) then
420 fnd_message.set_name('WIP', 'WIP_LESS_QTY');
421 fnd_msg_pub.add;
422 l_errMsg := 'not enough qty in to move of last op';
423 raise fnd_api.g_exc_unexpected_error;
424 end if;
425 update wip_operations --lower the quantity available to complete
426 set quantity_waiting_to_move = quantity_waiting_to_move - p_cplRec.priQty,
427 date_last_moved = decode(p_cplRec.txnActionID, wip_constants.cplassy_action, p_cplRec.txnDate, date_last_moved),
428 last_updated_by = fnd_global.user_id,
429 last_update_date = sysdate,
430 request_id = fnd_global.conc_request_id,
431 program_application_id = fnd_global.prog_appl_id,
432 program_id = fnd_global.conc_program_id,
433 program_update_date = sysdate
434 where rowid = l_rowid;
435 else -- Fix BUG 4869979 (FP 5107900)
436 -- If routing does not exist, validate if quantity is available on the job for completion
437
438 if (p_cplRec.overCplPriQty is null or p_cplRec.overCplPriQty < 0) then
439 oc_primary_qty:= 0;
440 else
441 oc_primary_qty := p_cplRec.overCplPriQty;
442 end if;
443 if p_cplRec.priQty - abs(l_qtyAvailToComplete) - oc_primary_qty > 0 then
444 fnd_message.set_name('WIP', 'WIP_LESS_QTY');
445 fnd_msg_pub.add;
446 l_errMsg := 'Quantity required to complete this transaction no longer available';
447 raise fnd_api.g_exc_unexpected_error;
448
449 end if;
450 -- end of BUG 4869979 (FP 5107900)
451
452 end if;
453
454 if (l_logLevel <= wip_constants.trace_logging) then
455 wip_logger.exitPoint(p_procName => 'wip_cplProc_priv.processDiscrete',
456 p_procReturnStatus => x_returnStatus,
457 p_msg => 'procedure success.',
458 x_returnStatus => l_returnStatus); --discard logging return status
459 end if;
460 exception
461 when fnd_api.g_exc_unexpected_error then
462 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
463 if (l_logLevel <= wip_constants.trace_logging) then
464 wip_logger.exitPoint(p_procName => 'wip_cplProc_priv.processDiscrete',
465 p_procReturnStatus => x_returnStatus,
466 p_msg => l_errMsg,
467 x_returnStatus => l_returnStatus); --discard logging return status
468 end if;
469 when wip_constants.records_locked then
470 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
471 if (l_logLevel <= wip_constants.trace_logging) then
472 wip_logger.exitPoint(p_procName => 'wip_cplProc_priv.processDiscrete',
473 p_procReturnStatus => x_returnStatus,
474 p_msg => 'records were locked',
475 x_returnStatus => l_returnStatus); --discard logging return status
476 end if;
477 fnd_message.set_name('INV', 'INV_WIP_WORK_ORDER_LOCKED');
478 fnd_msg_pub.add;
479 when others then
480 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
481 fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_cplProc_priv',
482 p_procedure_name => 'processDiscrete',
483 p_error_text => SQLERRM);
484 if (l_logLevel <= wip_constants.trace_logging) then
485 wip_logger.exitPoint(p_procName => 'wip_cplProc_priv.processDiscrete',
486 p_procReturnStatus => x_returnStatus,
487 p_msg => 'unexpected error: ' || SQLERRM,
488 x_returnStatus => l_returnStatus); --discard logging return status
489 end if;
490 end processDiscrete;
491
492
493
494 procedure processRepetitive(p_cplRec IN completion_rec_t,
495 p_txnTmpID IN NUMBER,
496 x_returnStatus OUT NOCOPY VARCHAR2) is
497
498 l_lastOpSeq NUMBER;
499 l_firstSchedID NUMBER;
500 l_lastSchedID NUMBER;
501 l_schedRec schedule_rec_t;
502 l_status NUMBER;
503 l_params wip_logger.param_tbl_t;
504 l_paramCount NUMBER;
505 l_returnStatus VARCHAR2(1);
506 l_errMsg VARCHAR2(240);
507 l_nullObj system.wip_component_tbl_t := null;
508 --the following field is used to store values for a final update to MMTA if not enough open qty can be found
509 --in the existing schedules and we need to allocate more qty to the first/last schedule after the main loop is done
510 l_finalRepSchedID NUMBER;
511 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
512 l_scrapQty NUMBER;
513
514 --type for below cursor
515 type schedule_recTbl_t is record(schedID num_tbl_t,
516 bomRev char_tbl_t,
517 startQty num_tbl_t,
518 cpldQty num_tbl_t, --completed qty
519 toMoveQty num_tbl_t,
520 preAlcQty num_tbl_t,
521 wrsRowID rowid_tbl_t,
522 woRowID rowid_tbl_t);
523
524 cursor c_preAllocs(v_lastOpSeq NUMBER) is
525 select wrs.repetitive_schedule_id,
526 wrs.bom_revision,
527 wrs.daily_production_rate * wrs.processing_work_days,
528 wrs.quantity_completed,
529 wo.quantity_waiting_to_move,
530 wmat.primary_quantity,
531 rowidtochar(wrs.rowid),
532 rowidtochar(wo.rowid)
533 from wip_operations wo,
534 wip_repetitive_schedules wrs,
535 wip_mtl_allocations_temp wmat
536 where wrs.wip_entity_id = p_cplRec.wipEntityID
537 and wrs.line_id = p_cplRec.repLineID
538 and wrs.status_type in (wip_constants.released, wip_constants.comp_chrg)
539 and wrs.repetitive_schedule_id = wo.repetitive_schedule_id (+)
540 and wrs.wip_entity_id = wo.wip_entity_id (+)
541 and v_lastOpSeq = wo.operation_seq_num (+)
542 and wrs.repetitive_schedule_id = wmat.repetitive_schedule_id
543 and wmat.transaction_temp_id = p_txnTmpID
544 order by wrs.first_unit_start_date
545 for update of wo.quantity_completed, wrs.quantity_completed nowait;
546
547 l_schedRecTbl schedule_recTbl_t;
548 l_newSchedQty NUMBER;
549 l_cplStatus NUMBER;
550 l_rollFwdSuccess NUMBER;
551 begin
552 x_returnStatus := fnd_api.g_ret_sts_success;
553 if (l_logLevel <= wip_constants.trace_logging) then
554 fillCplParamTbl(p_cplRec => p_cplRec,
555 x_params => l_params);
556 l_paramCount := l_params.count;
557 l_params(l_paramCount + 1).paramName := 'p_txnTmpID';
558 l_params(l_paramCount + 1).paramValue := p_txnTmpID;
559
560 wip_logger.entryPoint(p_procName => 'wip_cplProc_priv.processRepetitive',
561 p_params => l_params,
562 x_returnStatus => x_returnStatus);
563
564 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
565 raise fnd_api.g_exc_unexpected_error;
566 end if;
567 end if;
568
569
570 if(p_cplRec.txnActionID = wip_constants.cplassy_action AND
571 p_cplRec.kanbanCardID is not null) then
572 inv_kanban_pvt.update_card_supply_status
573 (p_kanban_card_id => p_cplRec.kanbanCardID,
574 p_supply_status => inv_kanban_pvt.g_supply_status_full,
575 p_document_type => inv_kanban_pvt.g_doc_type_rep_schedule,
576 p_document_header_id => p_cplRec.wipEntityID,
577 p_document_detail_id => null,
578 p_replenish_quantity => p_cplRec.priQty,
579 x_return_status => x_returnStatus);
580
581 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
582 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
583 l_errMsg := 'Kanban update failed.'; --set message in case it fails
584 raise fnd_api.g_exc_unexpected_error;
585 end if;
586 end if;
587
588 if(p_cplRec.lastOpSeq is null or p_cplRec.lastOpSeq < 0) then
589 l_lastOpSeq := 1; --if no rtg, components have op_seq = 1
590 else
591 l_lastOpSeq := p_cplRec.lastOpSeq;
592 end if;
593 open c_preAllocs(v_lastOpSeq => l_lastOpSeq);
594 fetch c_preAllocs
595 bulk collect into l_schedRecTbl.schedID,
596 l_schedRecTbl.bomRev,
597 l_schedRecTbl.startQty,
598 l_schedRecTbl.cpldQty,
599 l_schedRecTbl.toMoveQty,
600 l_schedRecTbl.preAlcQty,
601 l_schedRecTbl.wrsRowID,
602 l_schedRecTbl.woRowID;
603 close c_preAllocs;
604
605 for i in 1..l_schedRecTbl.schedID.count loop
606 select nvl(sum(quantity_scrapped), 0)
607 into l_scrapQty
608 from wip_operations
609 where repetitive_schedule_id = l_schedRecTbl.schedID(i);
610
611 if(l_schedRecTbl.woRowID(i) is not null) then
612 update wip_operations
613 set quantity_waiting_to_move = quantity_waiting_to_move - l_schedRecTbl.preAlcQty(i),
614 date_last_moved = sysdate,
615 last_updated_by = fnd_global.user_id,
616 last_update_date = sysdate,
617 request_id = fnd_global.conc_request_id,
618 program_application_id = fnd_global.prog_appl_id,
619 program_id = fnd_global.conc_program_id,
620 program_update_date = sysdate
621 where rowid = chartorowid(l_schedRecTbl.woRowID(i))
622 returning quantity_waiting_to_move into l_newSchedQty;
623
624 if(l_newSchedQty < 0) then
625 --add check for pending txns here
626 fnd_message.set_name('WIP', 'WIP_LESS_QTY');
627 fnd_msg_pub.add;
628 l_errMsg := 'not enough qty in to move of last op';
629 raise fnd_api.g_exc_unexpected_error;
630 end if;
631 end if;
632 if (l_logLevel <= wip_constants.full_logging) then
633 wip_logger.log('schedID' || l_schedRecTbl.schedID(i), l_returnStatus);
634 wip_logger.log('startQty ' || l_schedRecTbl.startQty(i), l_returnStatus);
635 wip_logger.log('cpldQty ' || l_schedRecTbl.cpldQty(i), l_returnStatus);
636 wip_logger.log('scrapQty ' || l_scrapQty, l_returnStatus);
637 wip_logger.log('preAlcQty ' || l_schedRecTbl.preAlcQty(i), l_returnStatus);
638 end if;
639
640 if(l_schedRecTbl.startQty(i) - l_schedRecTbl.cpldQty(i) - l_scrapQty <= l_schedRecTbl.preAlcQty(i) AND
641 p_cplRec.txnActionID = wip_constants.cplassy_action) then
642 l_status := wip_constants.comp_chrg;
643 else
644 l_status := wip_constants.released;
645 end if;
646
647 update wip_repetitive_schedules
648 set quantity_completed = quantity_completed + l_schedRecTbl.preAlcQty(i),
649 status_type = l_status,
650 last_updated_by = fnd_global.user_id,
651 last_update_date = sysdate,
652 request_id = fnd_global.conc_request_id,
653 program_application_id = fnd_global.prog_appl_id,
654 program_id = fnd_global.conc_program_id,
655 program_update_date = sysdate
656 where rowid = chartorowid(l_schedRecTbl.wrsRowID(i));
657
658 if(l_status = wip_constants.comp_chrg) then
659 wip_repetitive_utilities.roll_forward_cover(p_closed_sched_id => l_schedRecTbl.schedID(i),
660 p_rollfwd_sched_id => null, --doesn't seem to be in use
661 p_rollfwd_type => wip_constants.roll_complete,
662 p_org_id => p_cplRec.orgID,
663 p_update_status => wip_constants.yes,
664 p_success_flag => l_rollFwdSuccess,
665 p_error_msg => l_errMsg);
666 if(l_rollFwdSuccess <> wip_constants.yes) then
667 -- fnd_msg_pub.add; --assume error message is still current
668 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
669 l_errMsg := 'roll forward failed for schedule ' || l_schedRec.repSchedID;
670 end if;
671 end if;
672 end loop;
673 insert into mtl_material_txn_allocations(transaction_id,
674 repetitive_schedule_id,
675 organization_id,
676 last_update_date,
677 last_updated_by,
678 creation_date,
679 created_by,
680 last_update_login,
681 primary_quantity,
682 transaction_quantity,
683 request_id,
684 program_application_id,
685 program_id,
686 transaction_date)
687 select p_cplRec.mtlAlcTmpID,
688 wmat.repetitive_schedule_id,
689 wmat.organization_id,
690 sysdate,
691 fnd_global.user_id,
692 sysdate,
693 fnd_global.user_id,
694 fnd_global.login_id,
695 wmat.primary_quantity,
696 wmat.transaction_quantity,
697 fnd_global.conc_request_id,
698 fnd_global.prog_appl_id,
699 fnd_global.conc_program_id,
700 wmat.transaction_date
701 from wip_mtl_allocations_temp wmat
702 where wmat.transaction_temp_id = p_txnTmpID;
703
704 if (l_logLevel <= wip_constants.full_logging) then
705 wip_logger.log('inserted ' || SQL%ROWCOUNT || ' rows into MMTA', l_returnStatus);
706 end if;
707 delete wip_mtl_allocations_temp
708 where transaction_temp_id = p_txnTmpID;
709
710 if (l_logLevel <= wip_constants.full_logging) then
711 wip_logger.log('deleted ' || SQL%ROWCOUNT || ' rows from WMAT', l_returnStatus);
712 end if;
713
714 if (l_logLevel <= wip_constants.trace_logging) then
715 wip_logger.exitPoint(p_procName => 'wip_cplProc_priv.processRepetitive',
716 p_procReturnStatus => x_returnStatus,
717 p_msg => 'procedure success.',
718 x_returnStatus => l_returnStatus); --discard logging return status
719 end if;
720
721 exception
722 when fnd_api.g_exc_unexpected_error then
723 if (l_logLevel <= wip_constants.trace_logging) then
724 wip_logger.exitPoint(p_procName => 'wip_cplProc_priv.processRepetitive',
725 p_procReturnStatus => x_returnStatus,
726 p_msg => l_errMsg,
727 x_returnStatus => l_returnStatus); --discard logging return status
728 end if;
729 when others then
730 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
731 fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_cplProc_priv',
732 p_procedure_name => 'processRepetitive',
733 p_error_text => SQLERRM);
734 if (l_logLevel <= wip_constants.trace_logging) then
735 wip_logger.exitPoint(p_procName => 'wip_cplProc_priv.processRepetitive',
736 p_procReturnStatus => x_returnStatus,
737 p_msg => 'unexpected error: ' || SQLERRM,
738 x_returnStatus => l_returnStatus); --discard logging return status
739 end if;
740 end processRepetitive;
741
742 --This procedure:
743 -- + checks tolerances
744 -- + performs a move txn if the job/sched has a routing
745 -- + updates wro if the job/sched does *not* have a routing (not for repetitive yet)
746 --
747 -- The end result is that after this procedure, the completion code should be able
748 -- to process normally
749 -- The one caveat is that the overcompletion quantity must match the remaining quantity
750 -- after completing all repetitive schedules. This check is done in processRepetitive()
751 procedure processOverCpl(p_cplRec IN OUT NOCOPY completion_rec_t,
752 x_returnStatus OUT NOCOPY VARCHAR2) is
753
754 l_errMsg VARCHAR2(240);
755 l_params wip_logger.param_tbl_t;
756 l_returnStatus VARCHAR2(1);
757 l_firstSchedID NUMBER;
758 l_lastSchedID NUMBER;
759 l_firstOpSeq NUMBER;
760 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
761 --use the cursors only to lock the relavent wro rows
762 cursor c_repRows(v_schedID NUMBER) is
763 select required_quantity
764 from wip_requirement_operations
765 where repetitive_schedule_id = v_schedID
766 for update of required_quantity nowait;
767
768 cursor c_discRows(v_wipEntityID NUMBER) is
769 select required_quantity
770 from wip_requirement_operations
771 where wip_entity_id = v_wipEntityID
772 for update of required_quantity nowait;
773
774 begin
775 if (l_logLevel <= wip_constants.trace_logging) then
776 fillCplParamTbl(p_cplRec => p_cplRec,
777 x_params => l_params);
778 wip_logger.entryPoint(p_procName => 'wip_cplProc_priv.processOverCpl',
779 p_params => l_params,
780 x_returnStatus => x_returnStatus);
781
782 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
783 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
784 raise fnd_api.g_exc_unexpected_error;
785 end if;
786 end if;
787
788 if(p_cplRec.wipEntityType = wip_constants.repetitive) then
789 wip_repetitive_utilities.get_first_last_sched(p_wip_entity_id => p_cplRec.wipEntityID,
790 p_org_id => p_cplRec.orgID,
791 p_line_id => p_cplRec.repLineID,
792 x_first_sched_id => l_firstSchedID,
793 x_last_sched_id => l_lastSchedID,
794 x_error_mesg => l_errMsg);
795 if(l_errMsg <> null) then
796 fnd_msg_pub.add; --assume prev fn used the fnd_message pkg
797 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
798 l_errMsg := 'wip_repetitive_utilities.get_first_last_sched: ' || l_errMsg;
799 raise fnd_api.g_exc_unexpected_error;
800 end if;
801 end if;
802
803 wip_overcompletion.check_tolerance(p_organization_id => p_cplRec.orgID,
804 p_wip_entity_id => p_cplRec.wipEntityID,
805 p_repetitive_schedule_id => l_lastSchedID,
806 p_primary_quantity => p_cplRec.overCplPriQty,
807 p_result => x_returnStatus);
808
809
810 if(x_returnStatus = wip_constants.no) then
811 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
812 fnd_message.set_name('WIP', 'WIP_OC_TOLERANCE_FAIL');
813 fnd_msg_pub.add;
814 l_errMsg := 'cpl exceeded tolerances';
815 raise fnd_api.g_exc_unexpected_error;
816 else
817 x_returnStatus := fnd_api.g_ret_sts_success;
818 end if;
819
820
821 --if a routing exists, insert a move record and perform the move
822 if(p_cplRec.lastOpSeq > 0) then
823
824 wip_overcompletion.insert_oc_move_txn( p_primary_quantity => p_cplRec.overCplPriQty,
825 p_cpl_profile => wip_constants.online,
826 p_oc_txn_id => p_cplRec.overCplTxnID,
827 p_parent_cpl_txn_id => p_cplRec.cplTxnID,
828 p_first_schedule_id => l_firstSchedID,
829 p_user_id => p_cplRec.lastUpdBy, --fnd_global.user_id,
830 p_login_id => fnd_global.conc_login_id,
831 p_req_id => fnd_global.conc_request_id,
832 p_appl_id => fnd_global.prog_appl_id,
833 p_prog_id => fnd_global.conc_program_id,
834 p_child_txn_id => p_cplrec.movTxnID,
835 p_first_operation_seq_num => l_firstOpSeq,
836 p_err_mesg => l_errMsg);
837
838 --if insert failed
839 if(l_errMsg is not null) then
840 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
841 fnd_msg_pub.add;
842 l_errMsg := 'wip_overcompletion.insert_oc_move_txn: ' || l_errMsg;
843 raise fnd_api.g_exc_unexpected_error;
844 end if;
845
846 --we must process material requirements in background mode so any assy pull components in MMTT
847 --do not get processed. Assy pull components must remain through the discrete job processing so
848 --the backflush procedure does not insert new requirements.
849
850 wip_movProc_priv.processIntf(p_group_id => p_cplRec.movTxnID,
851 p_child_txn_id => -1,
852 p_mtl_header_id => p_cplRec.txnHdrID,
853 p_proc_phase => wip_constants.move_proc,
854 p_time_out => 0,
855 p_move_mode => wip_constants.online,
856 p_bf_mode => wip_constants.online, --ignored
857 p_mtl_mode => wip_constants.no_processing,--do not call inv TM at all
858 p_endDebug => fnd_api.g_false,
859 p_initMsgList => fnd_api.g_false,
860 p_insertAssy => fnd_api.g_true,
861 p_do_backflush => fnd_api.g_false,--backflush was already done
862 p_cmp_txn_id => null,
863 x_returnStatus => x_returnStatus);
864
865 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
866 --depend on move to set the message
867 raise fnd_api.g_exc_unexpected_error;
868 end if;
869 --otherwise we just have to increase the component requirements in wro
870 else
871 if(p_cplRec.wipEntityType = wip_constants.repetitive) then
872 open c_repRows(v_schedID => l_lastSchedID);
873 update wip_requirement_operations
874 set required_quantity = round(required_quantity + p_cplRec.overCplPriQty * quantity_per_assembly, wip_constants.inv_max_precision)
875 where repetitive_schedule_id = l_lastSchedID;
876 close c_repRows;
877 else
878 open c_discRows(v_wipEntityID => p_cplRec.wipEntityID);
879 update wip_requirement_operations
880 set required_quantity = round(required_quantity + p_cplRec.overCplPriQty * quantity_per_assembly, wip_constants.inv_max_precision)
881 where wip_entity_id = p_cplRec.wipEntityID
882 AND nvl(basis_type,1) <> WIP_CONSTANTS.LOT_BASED_MTL; /* LBM Project */
883 close c_discRows;
884 end if;
885 end if;
886
887 if (l_logLevel <= wip_constants.trace_logging) then
888 wip_logger.exitPoint(p_procName => 'wip_cplProc_priv.processOverCpl',
889 p_procReturnStatus => x_returnStatus,
890 p_msg => 'procedure success.',
891 x_returnStatus => l_returnStatus); --discard logging return status
892 end if;
893 exception
894 when fnd_api.g_exc_unexpected_error then
895 if (l_logLevel <= wip_constants.trace_logging) then
896 wip_logger.exitPoint(p_procName => 'wip_cplProc_priv.processOverCpl',
897 p_procReturnStatus => x_returnStatus,
898 p_msg => l_errMsg,
899 x_returnStatus => l_returnStatus); --discard logging return status
900 end if;
901 when others then
902 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
903 fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_cplProc_priv',
904 p_procedure_name => 'processOverCpl',
905 p_error_text => SQLERRM);
906 if (l_logLevel <= wip_constants.trace_logging) then
907 wip_logger.exitPoint(p_procName => 'wip_cplProc_priv.processOverCpl',
908 p_procReturnStatus => x_returnStatus,
909 p_msg => 'unexpected error: ' || SQLERRM,
910 x_returnStatus => l_returnStatus); --discard logging return status
911 end if;
912 end processOverCpl;
913
914 procedure preAllocateSchedules(p_txnHdrID IN NUMBER,
915 p_cplTxnID IN NUMBER,
916 p_txnActionID IN NUMBER,
917 p_wipEntityID IN NUMBER,
918 p_repLineID IN NUMBER,
919 p_tblName IN VARCHAR2,
920 p_endDebug IN VARCHAR2,
921 x_returnStatus OUT NOCOPY VARCHAR2) IS
922
923 /* Fix for bug 5373061: Added date_released condition to allocate back-dated transactions correctly */
924 cursor c_repCplScheds(v_lastOpSeq NUMBER, v_wipEntityID NUMBER, v_repLineID NUMBER, v_txnDate DATE) is
925 select wrs.repetitive_schedule_id repSchedID,
926 wrs.bom_revision bomRev,
927 nvl(wo.quantity_waiting_to_move,
928 ((wrs.daily_production_rate * wrs.processing_work_days) - wrs.quantity_completed)) availQty,
929 nvl(sum(wmat.primary_quantity), 0) tempQty
930 from wip_operations wo,
931 wip_repetitive_schedules wrs,
932 wip_mtl_allocations_temp wmat
933 where wrs.wip_entity_id = v_wipEntityID
934 and wrs.line_id = v_repLineID
935 and wrs.date_released < v_txnDate
936 and wrs.status_type in (wip_constants.released, wip_constants.comp_chrg)
937 and wrs.repetitive_schedule_id = wo.repetitive_schedule_id (+)
938 and wrs.wip_entity_id = wo.wip_entity_id (+)
939 and v_lastOpSeq = wo.operation_seq_num (+)
940 and wrs.repetitive_schedule_id = wmat.repetitive_schedule_id (+)
941 group by wrs.repetitive_schedule_id,
942 wrs.bom_revision,
943 wo.quantity_waiting_to_move,
944 wrs.daily_production_rate,
945 wrs.processing_work_days,
946 wrs.quantity_completed,
947 wrs.first_unit_start_date
948 order by wrs.first_unit_start_date;
949
950 /* Fix for bug 5373061: Added date_released condition to allocate back-dated transactions correctly */
951 cursor c_repRetScheds(v_lastOpSeq NUMBER, v_wipEntityID NUMBER, v_repLineID NUMBER, v_txnDate DATE) is
952 select wrs.repetitive_schedule_id repSchedID,
953 wrs.bom_revision bomRev,
954 wrs.quantity_completed availQty,
955 nvl(sum(wmat.primary_quantity), 0) tempQty
956 from wip_repetitive_schedules wrs,
957 wip_mtl_allocations_temp wmat
958 where wrs.wip_entity_id = v_wipEntityID
959 and wrs.line_id = v_repLineID
960 and wrs.date_released < v_txnDate
961 and wrs.status_type in (wip_constants.released, wip_constants.comp_chrg)
962 and wrs.repetitive_schedule_id = wmat.repetitive_schedule_id (+)
963 group by wrs.repetitive_schedule_id,
964 wrs.bom_revision,
965 wrs.daily_production_rate,
966 wrs.processing_work_days,
967 wrs.quantity_completed,
968 wrs.first_unit_start_date
969 order by wrs.first_unit_start_date desc;
970
971 cursor c_mmttTxns is
972 select transaction_temp_id txnTmpID,
973 operation_seq_num lastOpSeq,
974 revision,
975 transaction_date txnDate,
976 primary_quantity priQty,
977 transaction_quantity txnQty,
978 overcompletion_primary_qty,
979 organization_id
980 from mtl_material_transactions_temp
981 where completion_transaction_id = p_cplTxnID
982 and transaction_header_id = p_txnHdrID
983 and transaction_source_id = p_wipEntityID
984 and transaction_source_type_id = 5
985 and transaction_action_id in (wip_constants.cplassy_action, wip_constants.retassy_action);
986
987 cursor c_mtiTxns is
988 select transaction_interface_id,
989 operation_seq_num lastOpSeq,
990 revision,
991 transaction_date txnDate,
992 primary_quantity priQty,
993 transaction_quantity txnQty,
994 overcompletion_primary_qty,
995 organization_id
996 from mtl_transactions_interface
997 where completion_transaction_id = p_cplTxnID
998 and transaction_header_id = p_txnHdrID
999 and transaction_source_id = p_wipEntityID
1000 and transaction_source_type_id = 5
1001 and transaction_action_id in (wip_constants.cplassy_action, wip_constants.retassy_action);
1002
1003 type schedule_recTbl_t is record(repSchedID num_tbl_t,
1004 bomRev char_tbl_t,
1005 availQty num_tbl_t,
1006 tempQty num_tbl_t);
1007
1008 type txn_alloc_recTbl_t is record(txnID num_tbl_t,
1009 lastOpSeq num_tbl_t,
1010 revision char_tbl_t,
1011 txnDate date_tbl_t,
1012 priQty num_tbl_t,
1013 txnQty num_tbl_t,
1014 overCplQty num_tbl_t,
1015 orgID num_tbl_t);
1016 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
1017 l_params wip_logger.param_tbl_t;
1018 l_lastOpSeq NUMBER;
1019 l_schedQty NUMBER;
1020 l_returnStatus VARCHAR2(1);
1021 l_errMsg VARCHAR2(2000);
1022 l_remainingQty NUMBER;
1023 l_lastSchedID NUMBER;
1024 l_txnRecTbl txn_alloc_recTbl_t;
1025 l_schedRecTbl schedule_recTbl_t;
1026 l_startQty NUMBER;
1027 begin
1028 x_returnStatus := fnd_api.g_ret_sts_success;
1029
1030 savepoint wipcplpb40;
1031
1032 if (l_logLevel <= wip_constants.trace_logging) then
1033 l_params(1).paramName := 'p_txnHdrID';
1034 l_params(1).paramValue := p_txnHdrID;
1035 l_params(2).paramName := 'p_cplTxnID';
1036 l_params(2).paramValue := p_cplTxnID;
1037 l_params(3).paramName := 'p_txnActionID';
1038 l_params(3).paramValue := p_txnActionID;
1039 l_params(4).paramName := 'p_wipEntityID';
1040 l_params(4).paramValue := p_wipEntityID;
1041 l_params(5).paramName := 'p_repLineID';
1042 l_params(5).paramValue := p_repLineID;
1043 l_params(6).paramName := 'p_tblName';
1044 l_params(6).paramValue := p_tblName;
1045
1046 wip_logger.entryPoint(p_procName => 'wip_cplProc_priv.preAllocateSchedules',
1047 p_params => l_params,
1048 x_returnStatus => x_returnStatus);
1049
1050 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
1051 raise fnd_api.g_exc_unexpected_error;
1052 end if;
1053 end if;
1054
1055 if(p_tblName = wip_constants.MTI_TBL) then
1056 if (l_logLevel <= wip_constants.full_logging) then
1057 wip_logger.log('.25', l_returnStatus);
1058 end if;
1059 open c_mtiTxns;
1060 fetch c_mtiTxns
1061 bulk collect into l_txnRecTbl.txnID,
1062 l_txnRecTbl.lastOpSeq,
1063 l_txnRecTbl.revision,
1064 l_txnRecTbl.txnDate,
1065 l_txnRecTbl.priQty,
1066 l_txnRecTbl.txnQty,
1067 l_txnRecTbl.overCplQty,
1068 l_txnRecTbl.orgID;
1069 close c_mtiTxns;
1070 if (l_logLevel <= wip_constants.full_logging) then
1071 wip_logger.log('mti row count is ' || l_txnRecTbl.txnID.count, l_returnStatus);
1072 end if;
1073 else
1074 if (l_logLevel <= wip_constants.full_logging) then
1075 wip_logger.log('1', l_returnStatus);
1076 end if;
1077 open c_mmttTxns;
1078 fetch c_mmttTxns
1079 bulk collect into l_txnRecTbl.txnID,
1080 l_txnRecTbl.lastOpSeq,
1081 l_txnRecTbl.revision,
1082 l_txnRecTbl.txnDate,
1083 l_txnRecTbl.priQty,
1084 l_txnRecTbl.txnQty,
1085 l_txnRecTbl.overCplQty,
1086 l_txnRecTbl.orgID;
1087 close c_mmttTxns;
1088 if (l_logLevel <= wip_constants.full_logging) then
1089 wip_logger.log('2', l_returnStatus);
1090 end if;
1091 end if;
1092
1093 for i in 1..l_txnRecTbl.txnID.count loop
1094 if (l_logLevel <= wip_constants.full_logging) then
1095 wip_logger.log('processing cpl tmpID:' || l_txnRecTbl.txnID(i) || '; qty:' || l_txnRecTbl.priQty(i), l_returnStatus);
1096 end if;
1097
1098 if(l_txnRecTbl.lastOpSeq(i) is null or l_txnRecTbl.lastOpSeq(i) < 0) then
1099 l_lastOpSeq := 1; --if no rtg, components have op_seq = 1
1100 else
1101 l_lastOpSeq := l_txnRecTbl.lastOpSeq(i);
1102 end if;
1103
1104 if (l_logLevel <= wip_constants.full_logging) then
1105 wip_logger.log('last op seq is ' || l_lastOpSeq, l_returnStatus);
1106 wip_logger.log('action is ' || l_lastOpSeq, l_returnStatus);
1107 end if;
1108 l_remainingQty := l_txnRecTbl.priQty(i);
1109
1110 if(p_txnActionID = wip_constants.cplassy_action) then
1111 /* Fix for bug 5373061: Pass txnDate to cursor to fetch
1112 only valid schedules as on transaction date */
1113 open c_repCplScheds(v_lastOpSeq => l_txnRecTbl.lastOpSeq(i),
1114 v_wipEntityID => p_wipEntityID,
1115 v_repLineID => p_repLineID,
1116 v_txnDate => l_txnRecTbl.txnDate(i));
1117
1118 fetch c_repCplScheds
1119 bulk collect into l_schedRecTbl.repSchedID,
1120 l_schedRecTbl.bomRev,
1121 l_schedRecTbl.availQty,
1122 l_schedRecTbl.tempQty;
1123 close c_repCplScheds;
1124 else
1125 /* Fix for bug 5373061: Pass txnDate to cursor to fetch
1126 only valid schedules as on transaction date */
1127 open c_repRetScheds(v_lastOpSeq => l_txnRecTbl.lastOpSeq(i),
1128 v_wipEntityID => p_wipEntityID,
1129 v_repLineID => p_repLineID,
1130 v_txnDate => l_txnRecTbl.txnDate(i));
1131
1132 fetch c_repRetScheds
1133 bulk collect into l_schedRecTbl.repSchedID,
1134 l_schedRecTbl.bomRev,
1135 l_schedRecTbl.availQty,
1136 l_schedRecTbl.tempQty;
1137 close c_repRetScheds;
1138 end if;
1139 for j in 1..l_schedRecTbl.repSchedID.count loop
1140
1141 if(p_txnActionID = wip_constants.cplassy_action) then
1142 if(l_schedRecTbl.availQty(j) is null) then
1143 select ((wrs.daily_production_rate * wrs.processing_work_days) - wrs.quantity_completed) - nvl(sum(wo.quantity_scrapped), 0)
1144 into l_schedRecTbl.availQty(j)
1145 from wip_repetitive_schedules wrs,
1146 wip_operations wo
1147 where wrs.wip_entity_id = p_wipEntityID
1148 and wrs.repetitive_schedule_id = l_schedRecTbl.repSchedID(j)
1149 and wrs.repetitive_schedule_id = wo.repetitive_schedule_id (+)
1150 and wrs.wip_entity_id = wo.wip_entity_id (+)
1151 group by wrs.daily_production_rate,
1152 wrs.processing_work_days,
1153 wrs.quantity_completed;
1154 end if;
1155 l_schedQty := greatest(0, least(l_schedRecTbl.availQty(j) - nvl(l_schedRecTbl.tempQty(j), 0), l_remainingQty));
1156 else
1157 l_schedQty := -1 * greatest(0, least(l_schedRecTbl.availQty(j) + nvl(l_schedRecTbl.tempQty(j), 0), abs(l_remainingQty)));
1158 end if;
1159
1160
1161 l_lastSchedID := l_schedRecTbl.repSchedID(j);--the last schedule fetched is the last one open for completions
1162
1163 if(l_logLevel <= wip_constants.full_logging) then
1164 wip_logger.log('processing sched ' || l_schedRecTbl.repSchedID(j) || ' w/open qty of ' || l_schedQty, l_returnStatus);
1165 wip_logger.log('availQty: ' || l_schedRecTbl.availQty(j), l_returnStatus);
1166 wip_logger.log('startQty: ' || l_startQty, l_returnStatus);
1167 wip_logger.log('tempQty: ' || l_schedRecTbl.tempQty(j), l_returnStatus);
1168 wip_logger.log('remainQty: ' || l_remainingQty, l_returnStatus);
1169 end if;
1170
1171 --if the revisions don't match...
1172 if(l_schedRecTbl.bomRev(j) <> l_txnRecTbl.revision(i) and
1173 (l_schedRecTbl.bomRev(j) is not null or l_txnRecTbl.revision(i) is not null)) then
1174 fnd_message.set_name('WIP', 'WIP_SCHED_MULTIPLE_BILL_REV');
1175 fnd_msg_pub.add;
1176 l_errMsg := 'Schedules have different bill revisions.'; --set message in case it fails
1177 raise fnd_api.g_exc_unexpected_error;
1178 end if;
1179
1180 --complete the lesser of the open quantity and the remaining transaction qty
1181
1182 if(l_schedQty <> 0) then
1183 if (l_logLevel <= wip_constants.full_logging) then
1184 wip_logger.log('inserting into temp table; sched:' || l_schedRecTbl.repSchedID(j) || '; qty:' || l_schedQty, l_returnStatus);
1185 end if;
1186 insert into wip_mtl_allocations_temp
1187 (transaction_temp_id,
1188 completion_transaction_id,
1189 repetitive_schedule_id,
1190 organization_id,
1191 last_update_date,
1192 last_updated_by,
1193 creation_date,
1194 created_by,
1195 last_update_login,
1196 request_id,
1197 program_application_id,
1198 program_id,
1199 program_update_date,
1200 transaction_quantity,
1201 primary_quantity,
1202 transaction_date)
1203 values
1204 (l_txnRecTbl.txnID(i),
1205 p_cplTxnID,
1206 l_schedRecTbl.repSchedID(j),
1207 l_txnRecTbl.orgID(i),
1208 sysdate,
1209 fnd_global.user_id,
1210 sysdate,
1211 fnd_global.user_id,
1212 fnd_global.login_id,
1213 fnd_global.conc_request_id,
1214 fnd_global.prog_appl_id,
1215 fnd_global.conc_program_id,
1216 sysdate,
1217 round(l_txnRecTbl.txnQty(i) * l_schedQty/l_txnRecTbl.priQty(i), wip_constants.inv_max_precision), --% of qty completed * txn Qty
1218 l_schedQty,
1219 l_txnRecTbl.txnDate(i));
1220
1221 l_remainingQty := l_remainingQty - l_schedQty;
1222 if(l_remainingQty = 0) then
1223 exit;
1224 end if;
1225 end if;
1226 end loop;
1227 if(l_remainingQty <> 0) then -- over-completion/return
1228 if(l_lastSchedID is null) then
1229 /* Fix for bug 5373061: Passed missing token */
1230 fnd_message.set_name('WIP', 'WIP_INT_ERROR_NO_SCHED');
1231 fnd_message.set_token('ROUTINE', 'wip_cplProc_priv.preAllocateSchedules');
1232 fnd_msg_pub.add;
1233 l_errMsg := 'did not find any schedules.';
1234 raise fnd_api.g_exc_unexpected_error; -- couldn't find any open schedules
1235 end if;
1236
1237 if (l_logLevel <= wip_constants.full_logging) then
1238 wip_logger.log('txnID' || l_txnRecTbl.txnID(i), l_returnStatus);
1239 wip_logger.log('rmnQty' || l_remainingQty, l_returnStatus);
1240 wip_logger.log('schedID' || l_lastSchedID, l_returnStatus);
1241 end if;
1242
1243 --in below stmt, transaction_quantity = old_qty + (conversion ratio * new primary_qty)
1244 update wip_mtl_allocations_temp
1245 set transaction_quantity = transaction_quantity +
1246 (transaction_quantity/primary_quantity * l_remainingQty),
1247 primary_quantity = primary_quantity + l_remainingQty
1248 where transaction_temp_id = l_txnRecTbl.txnID(i)
1249 and repetitive_schedule_id = l_lastSchedID;
1250
1251 if(SQL%ROWCOUNT = 0) then
1252 wip_logger.log('l_txnRecTbl.overCplQty = '||to_char(l_txnRecTbl.overCplQty(i)), l_returnStatus);
1253
1254 /* Fix for bug 5373061: We will reach here even if no overcompletion is involved. Back-dated
1255 transactions can pick up older schedules which do not have open quantity. Do not allocate
1256 to the older schedule. Throw error */
1257
1258 /* Fix for Bug#6018877 - FP of bug#6004763. Added the if condition check not to consider Return txn */
1259 if(p_txnActionID <> wip_constants.retassy_action) then
1260 if(l_txnRecTbl.overCplQty(i) IS NULL) then
1261 fnd_message.set_name('WIP', 'WIP_INT_ERROR_NO_SCHED');
1262 fnd_message.set_token('ROUTINE', 'wip_cplProc_priv.preAllocateSchedules');
1263 fnd_msg_pub.add;
1264 l_errMsg := 'did not find any schedules.';
1265 raise fnd_api.g_exc_unexpected_error; -- couldn't find any open schedules
1266 end if;
1267 end if; /*Fix for Bug#6018877 - FP of bug#6004763*/
1268
1269 if (l_logLevel <= wip_constants.full_logging) then
1270 wip_logger.log('update failed. inserting new row', l_returnStatus);
1271 end if;
1272 /* Fixed bug 3698513. Completion_transaction_id is a not null column
1273 * , so we have to insert a value into this column. This bug only
1274 * occur when overcompletion and available quantity is zero.
1275 */
1276 insert into wip_mtl_allocations_temp
1277 (transaction_temp_id,
1278 completion_transaction_id,
1279 repetitive_schedule_id,
1280 organization_id,
1281 last_update_date,
1282 last_updated_by,
1283 creation_date,
1284 created_by,
1285 last_update_login,
1286 request_id,
1287 program_application_id,
1288 program_id,
1289 program_update_date,
1290 transaction_quantity,
1291 primary_quantity,
1292 transaction_date)
1293 values
1294 (l_txnRecTbl.txnID(i),
1295 p_cplTxnID,
1296 l_lastSchedID,
1297 l_txnRecTbl.orgID(i),
1298 sysdate,
1299 fnd_global.user_id,
1300 sysdate,
1301 fnd_global.user_id,
1302 fnd_global.login_id,
1303 fnd_global.conc_request_id,
1304 fnd_global.prog_appl_id,
1305 fnd_global.conc_program_id,
1306 sysdate,
1307 l_txnRecTbl.txnQty(i),
1308 l_txnRecTbl.priQty(i),
1309 l_txnRecTbl.txnDate(i));
1310 end if;
1311 end if;
1312 end loop;
1313
1314
1315 if (l_logLevel <= wip_constants.trace_logging) then
1316 wip_logger.exitPoint(p_procName => 'wip_cplProc_priv.preAllocateSchedules',
1317 p_procReturnStatus => x_returnStatus,
1318 p_msg => 'procedure success.',
1319 x_returnStatus => l_returnStatus); --discard logging return status
1320 end if;
1321 exception
1322 when fnd_api.g_exc_unexpected_error then
1323 rollback to wipcplpb40;
1324 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
1325 if (l_logLevel <= wip_constants.trace_logging) then
1326 wip_logger.exitPoint(p_procName => 'wip_cplProc_priv.preAllocateSchedules',
1327 p_procReturnStatus => x_returnStatus,
1328 p_msg => l_errMsg,
1329 x_returnStatus => l_returnStatus); --discard logging return status
1330 end if;
1331 when others then
1332 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
1333 fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_cplProc_priv',
1334 p_procedure_name => 'preAllocateSchedules',
1335 p_error_text => SQLERRM);
1336 if (l_logLevel <= wip_constants.trace_logging) then
1337 wip_logger.exitPoint(p_procName => 'wip_cplProc_priv.preAllocateSchedules',
1338 p_procReturnStatus => x_returnStatus,
1339 p_msg => 'unexpected error: ' || SQLERRM,
1340 x_returnStatus => l_returnStatus); --discard logging return status
1341 end if;
1342 end preAllocateSchedules;
1343 /*
1344 procedure preAllocateRets(p_txnHdrID IN NUMBER,
1345 p_cplTxnID IN NUMBER,
1346 p_wipEntityID IN NUMBER,
1347 p_repLineID IN NUMBER,
1348 p_tblName IN VARCHAR2,
1349 x_returnStatus OUT NOCOPY VARCHAR2) IS
1350
1351 cursor c_openScheds is
1352 select wrs.repetitive_schedule_id,
1353 ((wrs.daily_production_rate * wrs.processing_work_days) - wrs.quantity_completed) startQty,
1354 wrs.quantity_completed cplQty,
1355 sum(wmat.primary_quantity) tempQty
1356 from wip_repetitive_schedules wrs,
1357 wip_mtl_allocations_temp wmat
1358 where wrs.wip_entity_id = p_wipEntityID
1359 and wrs.line_id = p_repLineID
1360 and wrs.status_type in (wip_constants.released, wip_constants.comp_chrg)
1361 and wrs.repetitive_schedule_id = wmat.repetitive_schedule_id (+)
1362 group by wrs.repetitive_schedule_id,
1363 wrs.bom_revision,
1364 wrs.daily_production_rate,
1365 wrs.processing_work_days,
1366 wrs.quantity_completed,
1367 wrs.first_unit_start_date
1368 order by wrs.first_unit_start_date;
1369
1370 l_schedID NUMBER;
1371 l_errMsg VARCHAR2(2000);
1372 l_returnStatus VARCHAR2(1);
1373 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
1374 l_params wip_logger.param_tbl_t;
1375 begin
1376 --need to find the first open schedule. if we have pre-allocated completions, we have to see
1377 --which ones will be completed by the time this return is processed.
1378 if (l_logLevel <= wip_constants.trace_logging) then
1379 l_params(1).paramName := 'p_txnHdrID';
1380 l_params(1).paramValue := p_txnHdrID;
1381 l_params(2).paramName := 'p_cplTxnID';
1382 l_params(2).paramValue := p_cplTxnID;
1383 l_params(3).paramName := 'p_wipEntityID';
1384 l_params(3).paramValue := p_wipEntityID;
1385 l_params(4).paramName := 'p_repLineID';
1386 l_params(4).paramValue := p_repLineID;
1387 l_params(5).paramName := 'p_tblName';
1388 l_params(5).paramValue := p_tblName;
1389
1390 wip_logger.entryPoint(p_procName => 'wip_cplProc_priv.preAllocateRets',
1391 p_params => l_params,
1392 x_returnStatus => x_returnStatus);
1393
1394 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
1395 raise fnd_api.g_exc_unexpected_error;
1396 end if;
1397 end if;
1398
1399 for l_schedRec in c_openScheds loop
1400 l_schedID := l_schedRec.repetitive_schedule_id;
1401
1402 if(l_logLevel <= wip_constants.full_logging) then
1403 wip_logger.log('l_schedRec.cplQty' || l_schedRec.cplQty, l_returnStatus);
1404 wip_logger.log('l_schedRec.tempQty' || l_schedRec.tempQty, l_returnStatus);
1405 wip_logger.log('l_schedRec.startQty' || l_schedRec.startQty, l_returnStatus);
1406 end if;
1407
1408 if(greatest(l_schedRec.cplQty, 0) + nvl(l_schedRec.tempQty, 0) < l_schedRec.startQty) then
1409 if (l_logLevel <= wip_constants.full_logging) then
1410 wip_logger.log('found open schedule' || l_schedID, l_returnStatus);
1411 end if;
1412 exit;
1413 end if;
1414 end loop;
1415 if (l_logLevel <= wip_constants.full_logging) then
1416 wip_logger.log('returning to schedule' || l_schedID, l_returnStatus);
1417 end if;
1418
1419 if(p_tblName = wip_constants.MMTT_TBL) then
1420 insert into wip_mtl_allocations_temp
1421 (transaction_temp_id,
1422 repetitive_schedule_id,
1423 organization_id,
1424 last_update_date,
1425 last_updated_by,
1426 creation_date,
1427 created_by,
1428 last_update_login,
1429 request_id,
1430 program_application_id,
1431 program_id,
1432 program_update_date,
1433 transaction_quantity,
1434 primary_quantity,
1435 transaction_date)
1436 select mmtt.transaction_temp_id,
1437 l_schedID,
1438 mmtt.organization_id,
1439 sysdate,
1440 fnd_global.user_id,
1441 sysdate,
1442 fnd_global.user_id,
1443 fnd_global.login_id,
1444 fnd_global.conc_request_id,
1445 fnd_global.prog_appl_id,
1446 fnd_global.conc_program_id,
1447 sysdate,
1448 mmtt.transaction_quantity,
1449 mmtt.primary_quantity,
1450 mmtt.transaction_date
1451 from mtl_material_transactions_temp mmtt
1452 where mmtt.transaction_header_id = p_txnHdrID
1453 and mmtt.completion_transaction_id = p_cplTxnID
1454 and mmtt.transaction_action_id = wip_constants.retassy_action;
1455 elsif(p_tblName = wip_constants.MTI_TBL) then
1456 insert into wip_mtl_allocations_temp
1457 (transaction_temp_id,
1458 repetitive_schedule_id,
1459 organization_id,
1460 last_update_date,
1461 last_updated_by,
1462 creation_date,
1463 created_by,
1464 last_update_login,
1465 request_id,
1466 program_application_id,
1467 program_id,
1468 program_update_date,
1469 transaction_quantity,
1470 primary_quantity,
1471 transaction_date)
1472 select mti.transaction_interface_id,
1473 l_schedID,
1474 mti.organization_id,
1475 sysdate,
1476 fnd_global.user_id,
1477 sysdate,
1478 fnd_global.user_id,
1479 fnd_global.login_id,
1480 fnd_global.conc_request_id,
1481 fnd_global.prog_appl_id,
1482 fnd_global.conc_program_id,
1483 sysdate,
1484 mti.transaction_quantity,
1485 mti.primary_quantity,
1486 mti.transaction_date
1487 from mtl_transactions_interface mti
1488 where mti.transaction_header_id = p_txnHdrID
1489 and mti.completion_transaction_id = p_cplTxnID
1490 and mti.transaction_action_id = wip_constants.retassy_action;
1491 end if;
1492
1493 if (l_logLevel <= wip_constants.full_logging) then
1494 wip_logger.log('inserted ' || SQL%ROWCOUNT || ' rows', l_returnStatus);
1495 end if;
1496
1497 x_returnStatus := fnd_api.g_ret_sts_success;
1498
1499 if (l_logLevel <= wip_constants.trace_logging) then
1500 wip_logger.exitPoint(p_procName => 'wip_cplProc_priv.preAllocateRets',
1501 p_procReturnStatus => x_returnStatus,
1502 p_msg => 'success',
1503 x_returnStatus => l_returnStatus); --discard logging return status
1504 end if;
1505
1506 exception
1507 when others then
1508 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
1509 if (l_logLevel <= wip_constants.trace_logging) then
1510 wip_logger.exitPoint(p_procName => 'wip_cplProc_priv.preAllocateRets',
1511 p_procReturnStatus => x_returnStatus,
1512 p_msg => 'unexpected error: ' || SQLERRM,
1513 x_returnStatus => l_returnStatus); --discard logging return status
1514 end if;
1515 end preAllocateRets;
1516
1517 procedure preAllocateSchedules(p_txnHdrID IN NUMBER,
1518 p_cplTxnID IN NUMBER,
1519 p_txnActionID IN NUMBER,
1520 p_wipEntityID IN NUMBER,
1521 p_repLineID IN NUMBER,
1522 p_tblName IN VARCHAR2,
1523 p_endDebug IN VARCHAR2,
1524 x_returnStatus OUT NOCOPY VARCHAR2) IS
1525
1526 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
1527 l_params wip_logger.param_tbl_t;
1528 l_returnStatus VARCHAR2(1);
1529 l_errMsg VARCHAR2(2000);
1530 begin
1531 savepoint wipcplpb40;
1532
1533 if (l_logLevel <= wip_constants.trace_logging) then
1534 l_params(1).paramName := 'p_cplTxnID';
1535 l_params(1).paramValue := p_cplTxnID;
1536
1537 wip_logger.entryPoint(p_procName => 'wip_cplProc_priv.preAllocateSchedules',
1538 p_params => l_params,
1539 x_returnStatus => x_returnStatus);
1540
1541 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
1542 raise fnd_api.g_exc_unexpected_error;
1543 end if;
1544 end if;
1545
1546 if(p_txnActionID = wip_constants.cplassy_action) then
1547 preAllocateCpls(p_txnHdrID => p_txnHdrID,
1548 p_cplTxnID => p_cplTxnID,
1549 p_wipEntityID => p_wipEntityID,
1550 p_repLineID => p_repLineID,
1551 p_tblName => p_tblName,
1552 x_returnStatus => x_returnStatus);
1553 else
1554 preAllocateRets(p_txnHdrID => p_txnHdrID,
1555 p_cplTxnID => p_cplTxnID,
1556 p_wipEntityID => p_wipEntityID,
1557 p_repLineID => p_repLineID,
1558 p_tblName => p_tblName,
1559 x_returnStatus => x_returnStatus);
1560 end if;
1561
1562 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
1563 raise fnd_api.g_exc_unexpected_error;
1564 end if;
1565
1566 if (l_logLevel <= wip_constants.trace_logging) then
1567 wip_logger.exitPoint(p_procName => 'wip_cplProc_priv.preAllocateSchedules',
1568 p_procReturnStatus => x_returnStatus,
1569 p_msg => 'procedure success.',
1570 x_returnStatus => l_returnStatus); --discard logging return status
1571 if(fnd_api.to_boolean(p_endDebug)) then
1572 wip_logger.cleanUp(x_returnStatus => l_returnStatus); --discard logging return status
1573 end if;
1574 end if;
1575
1576 exception
1577 when fnd_api.g_exc_unexpected_error then
1578 rollback to wipcplpb40;
1579 if (l_logLevel <= wip_constants.trace_logging) then
1580 wip_logger.exitPoint(p_procName => 'wip_cplProc_priv.preAllocateSchedules',
1581 p_procReturnStatus => x_returnStatus,
1582 p_msg => l_errMsg,
1583 x_returnStatus => l_returnStatus); --discard logging return status
1584 if(fnd_api.to_boolean(p_endDebug)) then
1585 wip_logger.cleanUp(x_returnStatus => l_returnStatus); --discard logging return status
1586 end if;
1587 end if;
1588 when others then
1589 rollback to wipcplpb40;
1590 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
1591 fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_cplProc_priv',
1592 p_procedure_name => 'preAllocateSchedules',
1593 p_error_text => SQLERRM);
1594 if (l_logLevel <= wip_constants.trace_logging) then
1595 wip_logger.exitPoint(p_procName => 'wip_cplProc_priv.preAllocateSchedules',
1596 p_procReturnStatus => x_returnStatus,
1597 p_msg => 'unexpected error: ' || SQLERRM,
1598 x_returnStatus => l_returnStatus); --discard logging return status
1599 if(fnd_api.to_boolean(p_endDebug)) then
1600 wip_logger.cleanUp(x_returnStatus => l_returnStatus); --discard logging return status
1601 end if;
1602 end if;
1603 end preAllocateSchedules;
1604 */
1605 procedure fillCplParamTbl(p_cplRec IN completion_rec_t,
1606 x_params OUT NOCOPY wip_logger.param_tbl_t) is
1607 begin
1608 x_params(1).paramName := 'p_cplRec.wipEntityType';
1609 x_params(1).paramValue := p_cplRec.wipEntityType;
1610 x_params(2).paramName := 'p_cplRec.wipEntityID';
1611 x_params(2).paramValue := p_cplRec.wipEntityID;
1612 x_params(3).paramName := 'p_cplRec.orgID';
1613 x_params(3).paramValue := p_cplRec.orgID;
1614 x_params(4).paramName := 'p_cplRec.repLineID';
1615 x_params(4).paramValue := p_cplRec.repLineID;
1616 x_params(5).paramName := 'p_cplRec.itemID';
1617 x_params(5).paramValue := p_cplRec.itemID;
1618 x_params(6).paramName := 'p_cplRec.txnActionID';
1619 x_params(6).paramValue := p_cplRec.txnActionID;
1620 x_params(7).paramName := 'p_cplRec.priQty';
1621 x_params(7).paramValue := p_cplRec.priQty;
1622 x_params(8).paramName := 'p_cplRec.txnQty';
1623 x_params(8).paramValue := p_cplRec.txnQty;
1624 x_params(9).paramName := 'p_cplRec.txnDate';
1625 x_params(9).paramValue := p_cplRec.txnDate;
1626 x_params(10).paramName := 'p_cplRec.cplTxnID';
1627 x_params(10).paramValue := p_cplRec.cplTxnID;
1628 x_params(11).paramName := 'p_cplRec.kanbanCardID';
1629 x_params(11).paramValue := p_cplRec.kanbanCardID;
1630 x_params(12).paramName := 'p_cplRec.qaCollectionID';
1631 x_params(12).paramValue := p_cplRec.qaCollectionID;
1632 x_params(13).paramName := 'p_cplRec.lastOpSeq';
1633 x_params(13).paramValue := p_cplRec.lastOpSeq;
1634 x_params(14).paramName := 'p_cplRec.revision';
1635 x_params(14).paramValue := p_cplRec.revision;
1636 x_params(15).paramName := 'p_cplRec.mtlAlcTmpID';
1637 x_params(15).paramValue := p_cplRec.mtlAlcTmpID;
1638 x_params(16).paramName := 'p_cplRec.txnHdrID';
1639 x_params(16).paramValue := p_cplRec.txnHdrID;
1640 x_params(17).paramName := 'p_cplRec.txnStatus';
1641 x_params(17).paramValue := p_cplRec.txnStatus;
1642 x_params(18).paramName := 'p_cplRec.overCplPriQty';
1643 x_params(18).paramValue := p_cplRec.overCplPriQty;
1644 x_params(19).paramName := 'p_cplRec.overCplTxnID';
1645 x_params(19).paramValue := p_cplRec.overCplTxnID;
1646 x_params(20).paramName := 'p_cplRec.lastUpdBy';
1647 x_params(20).paramValue := p_cplRec.lastUpdBy;
1648 x_params(21).paramName := 'p_cplRec.createdBy';
1649 x_params(21).paramValue := p_cplRec.createdBy;
1650 x_params(22).paramName := 'p_cplRec.lpnID';
1651 x_params(22).paramValue := p_cplRec.lpnID;
1652 x_params(23).paramName := 'p_cplRec.movTxnID';
1653 x_params(23).paramValue := p_cplRec.movTxnID;
1654 end fillCplParamTbl;
1655
1656 PROCEDURE processOATxn(p_org_id IN NUMBER,
1657 p_interface_id IN NUMBER,
1658 p_mtl_header_id IN NUMBER,
1659 p_oc_primary_qty IN NUMBER,
1660 p_assySerial IN VARCHAR2:= NULL,
1661 p_print_label IN NUMBER default null, /*VJ Label Printing*/
1662 x_returnStatus OUT NOCOPY VARCHAR2) IS
1663 CURSOR c_cmp_txn IS
1664 SELECT transaction_source_id wip_entity_id,
1665 organization_id org_id,
1666 inventory_item_id item_id,
1667 transaction_action_id action_id,
1668 primary_quantity primary_qty,
1669 transaction_quantity txn_qty,
1670 transaction_date txn_date,
1671 completion_transaction_id cmp_txn_id,
1672 overcompletion_transaction_id oc_txn_id,
1673 kanban_card_id,
1674 qa_collection_id,
1675 operation_seq_num op_seq_num,
1676 revision,
1677 overcompletion_primary_qty oc_primary_qty,
1678 last_updated_by,
1679 created_by
1680 FROM mtl_material_transactions_temp
1681 WHERE transaction_temp_id = p_interface_id;
1682
1683
1684 l_cmp_txn c_cmp_txn%ROWTYPE;
1685 l_log_level NUMBER := fnd_log.g_current_runtime_level;
1686 l_cost_method NUMBER;
1687 l_err_num NUMBER;
1688 l_msg_count NUMBER;
1689 l_ret_value NUMBER;
1690 l_error_msg VARCHAR2(1000);
1691 l_label_status VARCHAR2(1);
1692 l_msg_stack VARCHAR2(2000);
1693 l_process_phase VARCHAR2(3);
1694 l_return_status VARCHAR(1);
1695 l_params wip_logger.param_tbl_t;
1696 l_oc_rec wip_cplProc_priv.completion_rec_t;
1697 -- new variables for serialization
1698 l_op_seq NUMBER;
1699 l_step NUMBER;
1700 BEGIN
1701 l_process_phase := '1';
1702 IF (l_log_level <= wip_constants.trace_logging) THEN
1703 l_params(1).paramName := 'p_org_id';
1704 l_params(1).paramValue := p_org_id;
1705 l_params(2).paramName := 'p_interface_id';
1706 l_params(2).paramValue := p_interface_id;
1707 l_params(3).paramName := 'p_mtl_header_id';
1708 l_params(3).paramValue := p_mtl_header_id;
1709 l_params(4).paramName := 'p_oc_primary_qty';
1710 l_params(4).paramValue := p_oc_primary_qty;
1711 wip_logger.entryPoint(p_procName => 'wip_cplProc_priv.processOATxn',
1712 p_params => l_params,
1713 x_returnStatus => l_return_status);
1714 END IF;
1715 l_process_phase := '2';
1716 SAVEPOINT s_oa_txn_proc;
1717 -- Validate and move records from MTI to MMTT.
1718 wip_mtlTempProc_priv.validateInterfaceTxns(
1719 p_txnHdrID => p_mtl_header_id,
1720 p_addMsgToStack => fnd_api.g_true, -- So that we can display to user
1721 p_rollbackOnErr => fnd_api.g_false,
1722 x_returnStatus => x_returnStatus);
1723
1724 IF(x_returnStatus <> fnd_api.g_ret_sts_success) THEN
1725 raise fnd_api.g_exc_unexpected_error;
1726 END IF;
1727
1728 l_process_phase := '3';
1729 SELECT primary_cost_method
1730 INTO l_cost_method
1731 FROM mtl_parameters
1732 WHERE organization_id = p_org_id;
1733
1734 l_process_phase := '4';
1735 --insert a row into cst_comp_snap_temp
1736 IF(l_cost_method IN (wip_constants.cost_avg,wip_constants.cost_fifo,
1737 wip_constants.cost_lifo)) THEN
1738 l_ret_value := CSTACOSN.op_snapshot(i_txn_temp_id => p_interface_id,
1739 err_num => l_err_num,
1740 err_code => l_error_msg,
1741 err_msg => l_error_msg);
1742 IF(l_ret_value <> 1) THEN
1743 fnd_message.set_name(application => 'CST',
1744 name => 'CST_SNAPSHOT_FAILED');
1745 fnd_msg_pub.add;
1746 raise fnd_api.g_exc_unexpected_error;
1747 END IF;
1748 END IF;
1749
1750 l_process_phase := '5';
1751 IF (p_print_label = 1) THEN /* VJ Label Printing */
1752 wip_utilities.print_label(p_txn_id => p_mtl_header_id,
1753 p_table_type => 2, --MMTT
1754 p_ret_status => x_returnStatus,
1755 p_msg_count => l_msg_count,
1756 p_msg_data => l_error_msg,
1757 p_label_status => l_label_status,
1758 p_business_flow_code => 26); -- discrete business flow code
1759 -- do not error out if label printing, only put warning message in log
1760 IF(x_returnStatus <> fnd_api.g_ret_sts_success) THEN
1761 wip_utilities.get_message_stack(p_msg => l_msg_stack);
1762 IF (l_log_level <= wip_constants.full_logging) then
1763 wip_logger.log(p_msg => 'An error has occurred with label printing.\n' ||
1764 'The following error has occurred during ' ||
1765 'printing: ' || l_msg_stack || '\n' ||
1766 'Please check the Inventory log file for more ' ||
1767 'information.',
1768 x_returnStatus =>l_return_status);
1769 END IF;
1770 END IF;
1771 END IF; /* VJ Label Printing */
1772
1773 l_process_phase := '6';
1774
1775 OPEN c_cmp_txn;
1776 -- This cursor will return only one record.
1777 FETCH c_cmp_txn INTO l_cmp_txn;
1778
1779 IF(p_oc_primary_qty IS NOT NULL AND p_oc_primary_qty > 0) THEN
1780 -- Initialize l_oc_rec
1781 l_oc_rec.wipEntityType := WIP_CONSTANTS.DISCRETE;
1782 l_oc_rec.wipEntityID := l_cmp_txn.wip_entity_id;
1783 l_oc_rec.orgID := l_cmp_txn.org_id;
1784 l_oc_rec.repLineID := null; -- only used for repetitive
1785 l_oc_rec.itemID := l_cmp_txn.item_id;
1786 l_oc_rec.txnActionID := l_cmp_txn.action_id;
1787 l_oc_rec.priQty := l_cmp_txn.primary_qty;
1788 l_oc_rec.txnQty := l_cmp_txn.txn_qty;
1789 l_oc_rec.txnDate := l_cmp_txn.txn_date;
1790 l_oc_rec.cplTxnID := l_cmp_txn.cmp_txn_id;
1791 l_oc_rec.movTxnID := l_cmp_txn.oc_txn_id;
1792 l_oc_rec.kanbanCardID := l_cmp_txn.kanban_card_id;
1793 l_oc_rec.qaCollectionID := l_cmp_txn.qa_collection_id;
1794 l_oc_rec.lastOpSeq := l_cmp_txn.op_seq_num;
1795 l_oc_rec.revision := l_cmp_txn.revision;
1796 l_oc_rec.mtlAlcTmpID := null; -- only used for repetitive
1797 l_oc_rec.txnHdrID := p_mtl_header_id;
1798 l_oc_rec.txnStatus := null;
1799 l_oc_rec.overCplPriQty := p_oc_primary_qty;
1800 l_oc_rec.lastUpdBy := l_cmp_txn.last_updated_by;
1801 l_oc_rec.createdBy := l_cmp_txn.created_by;
1802 l_oc_rec.lpnID := null; -- only used for LPN
1803 l_oc_rec.txnMode := WIP_CONSTANTS.ONLINE;
1804 l_oc_rec.overCplTxnID := l_cmp_txn.oc_txn_id;
1805
1806 wip_cplProc_priv.processOverCpl(p_cplRec => l_oc_rec,
1807 x_returnStatus => x_returnStatus);
1808
1809 IF(x_returnStatus <> fnd_api.g_ret_sts_success) THEN
1810 RAISE fnd_api.g_exc_unexpected_error;
1811 END IF;
1812 END IF; -- Overcompletion
1813
1814 l_process_phase := '7';
1815 wip_mtlTempProc_priv.processTemp
1816 (p_initMsgList => fnd_api.g_true,
1817 p_txnHdrID => p_mtl_header_id,
1818 p_txnMode => WIP_CONSTANTS.ONLINE,
1819 x_returnStatus => x_returnStatus,
1820 x_errorMsg => l_error_msg);
1821
1822 IF(x_returnStatus <> fnd_api.g_ret_sts_success) THEN
1823 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
1824 fnd_message.set_token('MESSAGE', l_error_msg);
1825 fnd_msg_pub.add;
1826 raise fnd_api.g_exc_unexpected_error;
1827 END IF;
1828
1829 l_process_phase := '8';
1830 -- Update to MSN must take place after inventory processing as they always
1831 -- clear the group mark id when processing a serial. Here we have to
1832 -- repopulate the group_mark_id, wip_entity_id, op_seq, and
1833 -- intra_op columns.
1834 IF (p_assySerial IS NOT NULL AND
1835 l_cmp_txn.action_id = WIP_CONSTANTS.RETASSY_ACTION) THEN
1836 -- Check whether the job has routing or not.
1837 IF(l_cmp_txn.op_seq_num = -1) THEN
1838 -- No routing
1839 l_op_seq := null;
1840 l_step := null;
1841 ELSE
1842 l_op_seq := l_cmp_txn.op_seq_num;
1843 l_step := WIP_CONSTANTS.TOMOVE;
1844 END IF;
1845
1846 wip_utilities.update_serial(p_serial_number => p_assySerial,
1847 p_inventory_item_id => l_cmp_txn.item_id,
1848 p_organization_id => l_cmp_txn.org_id,
1849 p_wip_entity_id => l_cmp_txn.wip_entity_id,
1850 p_operation_seq_num => l_op_seq,
1851 p_intraoperation_step_type => l_step,
1852 x_return_status => x_returnStatus);
1853
1854 IF(x_returnStatus <> fnd_api.g_ret_sts_success) THEN
1855 RAISE fnd_api.g_exc_unexpected_error;
1856 END IF;
1857 END IF; -- Serialized transaction
1858
1859 l_process_phase := '9';
1860 IF(c_cmp_txn%ISOPEN) THEN
1861 CLOSE c_cmp_txn;
1862 END IF;
1863
1864 x_returnStatus := fnd_api.g_ret_sts_success;
1865 -- write to the log file
1866 IF (l_log_level <= wip_constants.trace_logging) THEN
1867 wip_logger.exitPoint(p_procName => 'wip_cplProc_priv.processOATxn',
1868 p_procReturnStatus => x_returnStatus,
1869 p_msg => 'procedure complete',
1870 x_returnStatus => l_return_status);
1871 END IF;
1872 -- close log file
1873 wip_logger.cleanUp(x_returnStatus => l_return_status);
1874
1875 EXCEPTION
1876 WHEN fnd_api.g_exc_unexpected_error THEN
1877 IF(c_cmp_txn%ISOPEN) THEN
1878 CLOSE c_cmp_txn;
1879 END IF;
1880 ROLLBACK TO SAVEPOINT s_oa_txn_proc;
1881 x_returnStatus := fnd_api.g_ret_sts_error;
1882
1883 IF (l_log_level <= wip_constants.trace_logging) THEN
1884 wip_logger.exitPoint(p_procName => 'wip_cplProc_priv.processOATxn',
1885 p_procReturnStatus => x_returnStatus,
1886 p_msg => 'wip_cplProc_priv.processOATxn failed : '
1887 || l_process_phase,
1888 x_returnStatus => l_return_status);
1889 END IF;
1890 -- close log file
1891 wip_logger.cleanUp(x_returnStatus => l_return_status);
1892 WHEN others THEN
1893 IF(c_cmp_txn%ISOPEN) THEN
1894 CLOSE c_cmp_txn;
1895 END IF;
1896 ROLLBACK TO SAVEPOINT s_oa_txn_proc;
1897 x_returnStatus := fnd_api.g_ret_sts_error;
1898 l_error_msg := ' unexpected error: ' || SQLERRM || 'SQLCODE = ' ||
1899 SQLCODE;
1900
1901 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
1902 fnd_message.set_token('MESSAGE', l_error_msg);
1903 fnd_msg_pub.add;
1904
1905 IF (l_log_level <= wip_constants.trace_logging) THEN
1906 wip_logger.exitPoint(p_procName => 'wip_cplProc_priv.processOATxn',
1907 p_procReturnStatus => x_returnStatus,
1908 p_msg => l_error_msg || ' : ' || l_process_phase,
1909 x_returnStatus => l_return_status);
1910 END IF;
1911 -- close log file
1912 wip_logger.cleanUp(x_returnStatus => l_return_status);
1913 END processOATxn;
1914 end wip_cplProc_priv;