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