[Home] [Help]
PACKAGE BODY: APPS.WIP_MTLPROC_PRIV
Source
1 package body wip_mtlProc_priv as
2 /* $Header: wipmtlpb.pls 120.11.12000000.3 2007/05/07 11:25:53 akbhatia ship $ */
3
4 /* History
5 *
6 * Bug Fix By Description
7 * --------- ---------- -----------
8 *
9 * 5356098 mraman Added 2 local variables and
10 * intialized them to null to avoid
11 * ORA-6531 in procedure populateRepScheds
12 *
13 */
14 ----------------------
15 --private package types
16 -----------------------
17 type num_tbl_t is table of number;
18
19 -----------------
20 --package globals
21 -----------------
22 g_extendAmount constant number := 20; --for nested tables, the amount to extend each time more rows are needed.
23
24 ----------------------
25 --forward declarations
26 ----------------------
27 -- procedure setMtlTxnID(p_mtlTxnID IN NUMBER);
28 -- procedure resetMtlTxnID;
29 -- procedure setTxnTmpID(p_txnTmpID IN NUMBER);
30 procedure writeError(p_txnTmpID IN NUMBER);
31
32 procedure processTxn(p_issueRec IN wip_mtlTempProc_grp.comp_rec_t,
33 p_issueQty IN NUMBER,
34 p_repSchedID IN NUMBER,
35 x_returnStatus OUT NOCOPY VARCHAR2);
36
37 procedure populateRepScheds(p_issueRec IN wip_mtlTempProc_grp.comp_rec_t,
38 x_schedTbl OUT NOCOPY num_tbl_t,
39 x_qtyTbl OUT NOCOPY num_tbl_t,
40 x_returnStatus OUT NOCOPY VARCHAR2);
41
42 procedure processRepetitive(p_issueRec IN OUT nocopy wip_mtlTempProc_grp.comp_rec_t,
43 x_returnStatus OUT NOCOPY VARCHAR2);
44
45 ---------------------------
46 --public/private procedures
47 ---------------------------
48 procedure processTemp(p_initMsgList IN VARCHAR2,
49 p_endDebug IN VARCHAR2,
50 p_txnTmpID IN NUMBER,
51 x_returnStatus OUT NOCOPY VARCHAR2) is
52 l_issueRec wip_mtlTempProc_grp.comp_rec_t;
53 l_returnStatus VARCHAR2(1);
54 l_invStatus NUMBER;
55 l_errMsg VARCHAR2(2400);
56 l_params wip_logger.param_tbl_t;
57 l_msgCount NUMBER;
58 l_jobStatus NUMBER;
59 l_jobStatusCode VARCHAR2(240);
60 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
61 begin
62 savepoint wipmtlpb_SP1;
63
64 if(fnd_api.to_boolean(p_initMsgList)) then
65 fnd_msg_pub.initialize;
66 end if;
67
68 if (l_logLevel <= wip_constants.trace_logging) then
69 l_params(1).paramName := 'p_txnTmpID';
70 l_params(1).paramValue := p_txnTmpID;
71 wip_logger.entryPoint(p_procName => 'wip_mtlProc_priv.processTemp',
72 p_params => l_params,
73 x_returnStatus => x_returnStatus);
74 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
75 raise fnd_api.g_exc_unexpected_error;
76 end if;
77 end if;
78
79 select p_txnTmpID,
80 mmtt.material_allocation_temp_id,
81 mmtt.transaction_source_id,
82 mmtt.wip_entity_type,
83 mmtt.organization_id, --5
84 mmtt.repetitive_line_id,
85 mmtt.inventory_item_id,
86 mmtt.operation_seq_num,
87 -1 * mmtt.primary_quantity, --qty is relative to inv, make it relative to wip
88 -1 * mmtt.transaction_quantity, --10
89 mmtt.negative_req_flag,
90 mmtt.wip_supply_type,
91 msi.wip_supply_subinventory, /* Bug 5918149 : Pick subinventory from msi instead of mmtt. FP for bug 5895215 */
92 msi.wip_supply_locator_id, /* Bug 5918149 : Pick locator from msi instead of mmtt. FP for bug 5895215 */
93 mmtt.transaction_date, --15
94 mmtt.transaction_header_id,
95 mmtt.move_transaction_id,
96 mmtt.completion_transaction_id,
97 mmtt.qa_collection_id,
98 mmtt.department_id,
99 mmtt.transaction_action_id,
100 msi.serial_number_control_code,
101 msi.lot_control_code,
102 msi.eam_item_type,
103 mmtt.rebuild_item_id,
104 mmtt.rebuild_job_name,
105 mmtt.rebuild_activity_id,
106 mmtt.rebuild_serial_number
107 into l_issueRec.txnTmpID,
108 l_issueRec.mtlTxnID,
109 l_issueRec.wipEntityID,
110 l_issueRec.wipEntityType,
111 l_issueRec.orgID,
112 l_issueRec.repLineID,--5
113 l_issueRec.itemID,
114 l_issueRec.opSeqNum,
115 l_issueRec.primaryQty,
116 l_issueRec.txnQty,
117 l_issueRec.negReqFlag, --10
118 l_issueRec.wipSupplyType,
119 l_issueRec.supplySub,
120 l_issueRec.supplyLocID,
121 l_issueRec.txnDate,
122 l_issueRec.txnHdrID, --15
123 l_issueRec.movTxnID,
124 l_issueRec.cplTxnID,
125 l_issueRec.qaCollectionID,
126 l_issueRec.deptID,
127 l_issueRec.txnActionID,
128 l_issueRec.serialControlCode,
129 l_issueRec.lotControlCode,
130 l_issueRec.eamItemType,
131 l_issueRec.rebuildItemID,
132 l_issueRec.rebuildJobName,
133 l_issueRec.rebuildActivityID,
134 l_issueRec.rebuildSerialNumber
135 from mtl_material_transactions_temp mmtt, mtl_system_items_b msi
136 where transaction_temp_id = p_txnTmpID
137 and mmtt.inventory_item_id = msi.inventory_item_id
138 and mmtt.organization_id = msi.organization_id
139 and nvl(flow_schedule, 'N') <> 'Y';
140
141 --in 11.5.9 forms don't always insert wip supply type. from 11.5.10 on, all forms and interfaces should
142 --for now, use ids to default supplytype if not populated:
143 if(l_issueRec.wipSupplyType is null) then
144 if(l_issueRec.movTxnID is not null) then
145 l_issueRec.wipSupplyType := wip_constants.op_pull;
146 elsif(l_issueRec.cplTxnID is not null) then
147 l_issueRec.wipSupplyType := wip_constants.assy_pull;
148 else
149 l_issueRec.wipSupplyType := wip_constants.push;
150 end if;
151 end if;
152
153 if(l_issueRec.wipEntityType in (wip_constants.discrete,
154 wip_constants.lotbased,
155 wip_constants.eam)) then
156 /* commented out to prevent stuck transactions in MMTT
157 select status_type
158 into l_jobStatus
159 from wip_discrete_jobs
160 where wip_entity_id = l_issueRec.wipEntityID;
161
162 if (l_logLevel <= wip_constants.full_logging) then
163 wip_logger.log('selected job status:' || l_jobStatus, l_returnStatus);
164 end if;
165
166 if(l_jobStatus not in (wip_constants.released, wip_constants.comp_chrg)) then
167 begin
168 select meaning
169 into l_JobStatus
170 from mfg_lookups
171 where lookup_type = 'WIP_JOB_STATUS'
172 and lookup_code = l_jobStatus;
173 exception
174 when others then
175 l_jobStatusCode := l_jobStatus;
176 end;
177 fnd_message.set_name('WIP', 'WIP_PICKING_STATUS_ERROR');
178 fnd_message.set_token('STATUS', l_jobStatus);
179 fnd_msg_pub.add;
180 raise fnd_api.g_exc_unexpected_error;
181 end if;
182 */
183
184 processTxn(p_issueRec => l_issueRec,
185 p_issueQty => null, --since not processing rep schedules
186 p_repSchedID => null, --since not processing rep schedules
187 x_returnStatus => x_returnStatus);
188 if ( x_returnStatus <> fnd_api.g_ret_sts_success ) then
189 raise fnd_api.g_exc_unexpected_error;
190 end if;
191
192 -------------------
193 --eam issue code
194 -------------------
195 if(l_issueRec.wipEntityType = wip_constants.eam) then
196 --if error occurs, processRebuildable should put an err msg on the stack
197 if (l_logLevel <= wip_constants.full_logging) then
198 wip_logger.log('about to call EAM processor ', l_returnStatus);
199 end if;
200 wip_eamMtlProc_priv.processCompTxn(p_compRec => l_issueRec,
201 x_returnStatus => x_returnStatus);
202 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
203 l_errMsg := 'EAM logic failed';
204 raise fnd_api.g_exc_unexpected_error;
205 end if;
206 end if;
207
208 elsif(l_issueRec.wipEntityType = wip_constants.repetitive) then
209 --may be multiple schedule transactions per single issue
210 processRepetitive(p_issueRec => l_issueRec,
211 x_returnStatus => x_returnStatus);
212
213 else
214 fnd_message.set_name('WIP', 'OPERATION_PROCESSING_ERROR');
215 l_errMsg := 'Invalid WIP Entity Type:' || l_issueRec.wipEntityType;
216 fnd_msg_pub.add;
217 raise fnd_api.g_exc_unexpected_error;
218 end if;
219
220 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
221 l_errMsg := 'processing failed';
222 raise fnd_api.g_exc_unexpected_error;
223 end if;
224 if(l_issueRec.qaCollectionID is not null and
225 l_issueRec.movTxnID is null) then --if movTxnID is present, move would have enabled results.
226 qa_result_grp.enable(p_api_version => 1.0,
227 p_validation_level => 0,
228 p_collection_id => l_issueRec.qaCollectionID,
229 p_return_status => x_returnStatus,
230 p_msg_count => l_msgCount,
231 p_msg_data => l_errMsg);
232 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
233 l_errMsg := 'QA Failed. Collection ID:' || l_issueRec.qaCollectionID;
234 raise fnd_api.g_exc_unexpected_error;
235 end if;
236 end if;
237
238 if (l_logLevel <= wip_constants.trace_logging) then
239 wip_logger.exitPoint(p_procName => 'wip_mtlProc_priv.processTemp',
240 p_procReturnStatus => x_returnStatus,
241 p_msg => 'Transaction Succeeded',
242 x_returnStatus => l_returnStatus); --discard logging return status
243 end if;
244 if(fnd_api.to_boolean(p_endDebug)) then
245 wip_logger.cleanUp(x_returnStatus => l_returnStatus); --discard logging return status
246 end if;
247 exception
248 --processing error. return status and error msg should have already been set.
249 when no_data_found then
250 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
251 rollback to wipmtlpb_SP1;
252 if (l_logLevel <= wip_constants.trace_logging) then
253 wip_logger.exitPoint(p_procName => 'wip_mtlProc_priv.processTemp',
254 p_procReturnStatus => x_returnStatus,
255 p_msg => 'no data found exception. tmpID:' || p_txnTmpID,
256 x_returnStatus => l_returnStatus); --discard logging return status
257 end if;
258 if(fnd_api.to_boolean(p_endDebug)) then
259 wip_logger.cleanUp(x_returnStatus => l_returnStatus); --discard logging return status
260 end if;
261 fnd_message.set_name('WIP', 'INVALID_MMTT_TEMP_ID');
262 fnd_msg_pub.add;
263 writeError(p_txnTmpID);
264
265 when fnd_api.g_exc_unexpected_error then
266 rollback to wipmtlpb_SP1;
267
268 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
269
270 writeError(p_txnTmpID); --update the MMTT line to error for wip failures
271 if (l_logLevel <= wip_constants.trace_logging) then
272 wip_logger.exitPoint(p_procName => 'wip_mtlProc_priv.processTemp',
273 p_procReturnStatus => x_returnStatus,
274 p_msg => l_errMsg,
275 x_returnStatus => l_returnStatus); --discard logging return status
276 end if;
277 if(fnd_api.to_boolean(p_endDebug)) then
278 wip_logger.cleanUp(x_returnStatus => l_returnStatus); --discard logging return status
279 end if;
280 when others then
281 rollback to wipmtlpb_SP1;
282 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
283 fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_mtlProc_priv',
284 p_procedure_name => 'processTemp',
285 p_error_text => SQLERRM);
286 writeError(p_txnTmpID);
287 if (l_logLevel <= wip_constants.trace_logging) then
288 wip_logger.exitPoint(p_procName => 'wip_mtlProc_priv.processTemp',
289 p_procReturnStatus => x_returnStatus,
290 p_msg => 'item' || l_issueRec.itemID || ' unexpected error: ' || SQLERRM,
291 x_returnStatus => l_returnStatus); --discard logging return status
292 end if;
293 if(fnd_api.to_boolean(p_endDebug)) then
294 wip_logger.cleanUp(x_returnStatus => l_returnStatus); --discard logging return status
295 end if;
296 end processTemp;
297
298 procedure fillIssueParamTbl(p_issueRec IN wip_mtlTempProc_grp.comp_rec_t,
299 x_params OUT NOCOPY wip_logger.param_tbl_t)
300 is begin
301
302 x_params(1).paramName := 'p_issueRec.txnTmpID';
303 x_params(1).paramValue := p_issueRec.txnTmpID;
304 x_params(2).paramName := 'p_issueRec.mtlTxnID';
305 x_params(2).paramValue := p_issueRec.mtlTxnID;
306 x_params(3).paramName := 'p_issueRec.wipEntityID';
307 x_params(3).paramValue := p_issueRec.wipEntityID;
308 x_params(4).paramName := 'p_issueRec.repLineID';
309 x_params(4).paramValue := p_issueRec.repLineID;
310 x_params(5).paramName := 'p_issueRec.orgID';
311 x_params(5).paramValue := p_issueRec.orgID;
312 x_params(6).paramName := 'p_issueRec.itemID';
313 x_params(6).paramValue := p_issueRec.itemID;
314 x_params(7).paramName := 'p_issueRec.opSeqNum';
315 x_params(7).paramValue := p_issueRec.opSeqNum;
316 x_params(8).paramName := 'p_issueRec.primaryQty';
317 x_params(8).paramValue := p_issueRec.primaryQty;
318 x_params(9).paramName := 'p_issueRec.txnQty';
319 x_params(9).paramValue := p_issueRec.txnQty;
320 x_params(10).paramName := 'p_issueRec.negReqFlag';
321 x_params(10).paramValue := p_issueRec.negReqFlag;
322 x_params(11).paramName := 'p_issueRec.wipSupplyType';
323 x_params(11).paramValue := p_issueRec.wipSupplyType;
324 x_params(12).paramName := 'p_issueRec.wipEntityType';
325 x_params(12).paramValue := p_issueRec.wipEntityType;
326 x_params(13).paramName := 'p_issueRec.supplySub';
327 x_params(13).paramValue := p_issueRec.supplySub;
328 x_params(14).paramName := 'p_issueRec.supplyLocID';
329 x_params(14).paramValue := p_issueRec.supplyLocID;
330 x_params(15).paramName := 'p_issueRec.txnDate';
331 x_params(15).paramValue := p_issueRec.txnDate;
332 x_params(16).paramName := 'p_issueRec.txnHdrID';
333 x_params(16).paramValue := p_issueRec.txnHdrID;
334 x_params(17).paramName := 'p_issueRec.movTxnID';
335 x_params(17).paramValue := p_issueRec.movTxnID;
336 x_params(18).paramName := 'p_issueRec.cplTxnID';
337 x_params(18).paramValue := p_issueRec.cplTxnID;
338 x_params(19).paramName := 'p_issueRec.qaCollectionID';
339 x_params(19).paramValue := p_issueRec.qaCollectionID;
340 x_params(20).paramName := 'p_issueRec.deptID';
341 x_params(20).paramValue := p_issueRec.deptID;
342 x_params(21).paramName := 'p_issueRec.txnActionID';
343 x_params(21).paramValue := p_issueRec.txnActionID;
344 x_params(22).paramName := 'p_issueRec.serialControlCode';
345 x_params(22).paramValue := p_issueRec.serialControlCode;
346 x_params(23).paramName := 'p_issueRec.lotControlCode';
347 x_params(23).paramValue := p_issueRec.lotControlCode;
348 x_params(24).paramName := 'p_issueRec.eamItemType';
349 x_params(24).paramValue := p_issueRec.eamItemType;
350 x_params(25).paramName := 'p_issueRec.rebuildItemID';
351 x_params(25).paramValue := p_issueRec.rebuildItemID;
352 x_params(26).paramName := 'p_issueRec.rebuildJobName';
353 x_params(26).paramValue := p_issueRec.rebuildJobName;
354 x_params(27).paramName := 'p_issueRec.rebuildActivityID';
355 x_params(27).paramValue := p_issueRec.rebuildActivityID;
356 x_params(28).paramName := 'p_issueRec.rebuildSerialNumber';
357 x_params(28).paramValue := p_issueRec.rebuildSerialNumber;
358
359 end fillIssueParamTbl;
360
361 procedure processTxn(p_issueRec IN wip_mtlTempProc_grp.comp_rec_t,
362 p_issueQty IN NUMBER,
363 p_repSchedID IN NUMBER,
364 x_returnStatus OUT NOCOPY VARCHAR2) IS
365 l_rowid ROWID;
366 l_doUpdate boolean := true;
367 l_params wip_logger.param_tbl_t;
368 l_paramCount NUMBER;
369 l_errMsg VARCHAR2(240);
370 l_returnStatus VARCHAR2(1);
371 l_newRequiredQty NUMBER;
372 l_newIssuedQty NUMBER;
373 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
374 l_dept_id NUMBER := null; /* Bugfix 5401362 */
375 begin
376 if (l_logLevel <= wip_constants.full_logging) then
377 wip_logger.log('processTxn: p_issueQty: ' || p_issueQty, l_returnStatus);
378 end if;
379 --just logs the issue record. moved to a helper just to keep procedure small
380
381 if (l_logLevel <= wip_constants.trace_logging) then
382 fillIssueParamTbl(p_issueRec => p_issueRec,
383 x_params => l_params);
384 l_paramCount := l_params.count;
385 l_params(l_paramCount + 1).paramName := 'p_issueQty';
386 l_params(l_paramCount + 1).paramValue := p_issueQty;
387 l_params(l_paramCount + 2).paramName := 'p_repSchedID';
388 l_params(l_paramCount + 2).paramValue := p_repSchedID;
389 wip_logger.entryPoint(p_procName => 'wip_mtlProc_priv.processTxn',
390 p_params => l_params,
391 x_returnStatus => x_returnStatus);
392 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
393 raise fnd_api.g_exc_unexpected_error;
394 end if;
395 end if;
396
397 if(p_repSchedID is null) then
398 begin
399 select rowid
400 into l_rowid
401 from wip_requirement_operations
402 where inventory_item_id = p_issueRec.itemID
403 and wip_entity_id = p_issueRec.wipEntityID
404 and operation_seq_num = p_issueRec.opSeqNum
405 for update of quantity_issued, quantity_allocated nowait;
406 exception
407 when no_data_found then
408 l_doUpdate := false;
409 end;
410 if(l_doUpdate) then
411 if (l_logLevel <= wip_constants.full_logging) then
412 wip_logger.log('do update is true', l_returnStatus);
413 end if;
414 --below, quantity_allocated must be >= 0. At the same time, it must never increase via a return, negative issue.
415 --only the component picking process should increase the quantity_allocated column
416 update wip_requirement_operations --try to update an existing requirement
417 set quantity_issued = quantity_issued + nvl(p_issueQty, p_issueRec.primaryQty),
418 quantity_allocated = greatest(0, least(quantity_allocated, quantity_allocated - nvl(p_issueQty, p_issueRec.primaryQty))),
419 last_update_date = sysdate,
420 last_updated_by = fnd_global.user_id,
421 last_update_login = fnd_global.conc_login_id,
422 request_id = fnd_global.conc_request_id,
423 program_id = fnd_global.conc_program_id,
424 program_application_id = fnd_global.prog_appl_id
425 where rowid = l_rowid returning quantity_issued, required_quantity into l_newIssuedQty, l_newRequiredQty;
426
427 end if;
428 else
429 begin --try to find an existing requirement
430 select rowid
431 into l_rowid
432 from wip_requirement_operations
433 where inventory_item_id = p_issueRec.itemID
434 and wip_entity_id = p_issueRec.wipEntityID
435 and repetitive_schedule_id = p_repSchedID
436 and operation_seq_num = p_issueRec.opSeqNum
437 for update of quantity_issued, quantity_allocated nowait;
438 exception
439 when no_data_found then --no existing requirement, will have to insert one
440 l_doUpdate := false;
441 end;
442 if(l_doUpdate) then
443 --below, quantity_allocated must be >= 0. At the same time, it must never increase via a return, negative issue.
444 --only the component picking process should increase the quantity_allocated column
445 update wip_requirement_operations --try to update an existing requirement
446 set quantity_issued = quantity_issued + nvl(p_issueQty, p_issueRec.primaryQty),
447 quantity_allocated = greatest(0, least(quantity_allocated, quantity_allocated - nvl(p_issueQty, p_issueRec.primaryQty))),
448 last_update_date = sysdate,
449 last_updated_by = fnd_global.user_id,
450 last_update_login = fnd_global.conc_login_id,
451 request_id = fnd_global.conc_request_id,
452 program_id = fnd_global.conc_program_id,
453 program_application_id = fnd_global.prog_appl_id
454 where rowid = l_rowid returning quantity_issued, required_quantity into l_newIssuedQty, l_newRequiredQty;
455
456 end if;
457 end if;
458
459 if(not l_doUpdate) then --create the requirement since we could not find an existing one
460 if (l_logLevel <= wip_constants.full_logging) then
461 wip_logger.log('about to do insert', l_returnStatus);
462 end if;
463
464 /* Bugfix 5401362. Default department_id when null */
465 if (p_issueRec.deptID is null) then
466 begin
467 select department_id
468 into l_dept_id
469 from wip_operations wo
470 where wip_entity_id = p_issueRec.wipEntityID
471 and operation_seq_num = p_issueRec.opSeqNum;
472 exception
473 when others then
474 null;
475 end;
476 end if;
477 /* End bugfix 5401362 */
478
479 insert into wip_requirement_operations
480 (inventory_item_id,
481 organization_id,
482 wip_entity_id,
483 operation_seq_num,
484 repetitive_schedule_id, --5
485 creation_date,
486 created_by,
487 last_update_login,
488 last_update_date,
489 last_updated_by, --10
490 department_id,
491 date_required,
492 required_quantity,
493 quantity_issued,
494 quantity_per_assembly, --15
495 wip_supply_type,
496 mrp_net_flag,
497 request_id,
498 program_application_id,
499 program_id, --20
500 program_update_date,
501 supply_subinventory,
502 supply_locator_id,
503 mps_date_required,
504 mps_required_quantity, --25
505 segment1,
506 segment2,
507 segment3,
508 segment4,
509 segment5, --30
510 segment6,
511 segment7,
512 segment8,
513 segment9,
514 segment10, --35
515 segment11,
516 segment12,
517 segment13,
518 segment14,
519 segment15, --40
520 segment16,
521 segment17,
522 segment18,
523 segment19,
524 segment20,
525 component_yield_factor -- Added for bug 4703470
526 )
527 select p_issueRec.itemID,
528 p_issueRec.orgID,
529 p_issueRec.wipEntityID,
530 p_issueRec.opSeqNum,
531 p_repSchedID, --5
532 sysdate,
533 fnd_global.user_id,
534 fnd_global.login_id,
535 sysdate,
536 fnd_global.user_id, --10
537 nvl(p_issueRec.deptID, l_dept_id), /* Bugfix 5401362 l_dept_id if null */
538 p_issueRec.txnDate,
539 0, --required_quantity
540 nvl(p_issueQty, p_issueRec.primaryQty),
541 0, --quantity_per_assembly 15
542 nvl(p_issueRec.wipSupplyType, wip_constants.push),
543 wip_constants.yes,
544 fnd_global.conc_request_id,
545 fnd_global.prog_appl_id,
546 fnd_global.conc_program_id, --20
547 sysdate,
548 p_issueRec.supplySub,
549 p_issueRec.supplyLocID,
550 p_issueRec.txnDate,
551 0, --mps_required_quantity??? 25
552 SEGMENT1,
553 SEGMENT2,
554 SEGMENT3,
555 SEGMENT4,
556 SEGMENT5, --30
557 SEGMENT6,
558 SEGMENT7,
559 SEGMENT8,
560 SEGMENT9,
561 SEGMENT10, --35
562 SEGMENT11,
563 SEGMENT12,
564 SEGMENT13,
565 SEGMENT14,
566 SEGMENT15, --40
567 SEGMENT16,
568 SEGMENT17,
569 SEGMENT18,
570 SEGMENT19,
571 SEGMENT20,
572 1 -- Added for Bug 4703470
573 FROM MTL_SYSTEM_ITEMS
574 WHERE ORGANIZATION_ID = p_issueRec.orgID
575 AND INVENTORY_ITEM_ID = p_issueRec.itemID;
576
577 if (l_logLevel <= wip_constants.full_logging) then
578 wip_logger.log('inserted ' || SQL %ROWCOUNT, l_returnStatus);
579 end if;
580 end if;
581
582 x_returnStatus := fnd_api.g_ret_sts_success;
583
584 if (l_logLevel <= wip_constants.trace_logging) then
585 wip_logger.exitPoint(p_procName => 'wip_mtlProc_priv.processTxn',
586 p_procReturnStatus => x_returnStatus,
587 p_msg => 'procedure success',
588 x_returnStatus => l_returnStatus); --discard logging return status
589 end if;
590 exception
591 when fnd_api.g_exc_unexpected_error then
592 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
593 if (l_logLevel <= wip_constants.trace_logging) then
594 wip_logger.exitPoint(p_procName => 'wip_mtlProc_priv.processTxn',
595 p_procReturnStatus => x_returnStatus,
596 p_msg => l_errMsg,
597 x_returnStatus => l_returnStatus); --discard logging return status
598 end if;
599 fnd_message.set_name('WIP', 'OPERATION_PROCESSING_ERROR');
600 fnd_msg_pub.add;
601 --need to add a message to the stack and count it
602 when wip_constants.records_locked then
603 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
604 if (l_logLevel <= wip_constants.trace_logging) then
605 wip_logger.exitPoint(p_procName => 'wip_mtlProc_priv.processTxn',
606 p_procReturnStatus => x_returnStatus,
607 p_msg => 'records locked',
608 x_returnStatus => l_returnStatus);
609 end if;
610 fnd_message.set_name('INV', 'INV_WIP_WORK_ORDER_LOCKED');
611 fnd_msg_pub.add;
612 when others then
613 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
614 fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_mtlProc_priv',
615 p_procedure_name => 'processTxn',
616 p_error_text => SQLERRM);
617 if (l_logLevel <= wip_constants.trace_logging) then
618 wip_logger.exitPoint(p_procName => 'wip_mtlProc_priv.processTxn',
619 p_procReturnStatus => x_returnStatus,
620 p_msg => 'unexpected error: ' || SQLERRM,
621 x_returnStatus => l_returnStatus); --discard logging return status
622 end if;
623 fnd_message.set_name('WIP', 'OPERATION_PROCESSING_ERROR');
624 fnd_msg_pub.add;
625 end processTxn;
626
627 --This procedure fetches the repetitive schedules and how much of the component
628 --quantity can be allocated to that schedule. As different cursors are used for
629 --issues and returns as well as by supply type, this helper method helps reduce
630 --the size of processRepetitive() below
631 procedure populateRepScheds(p_issueRec IN wip_mtlTempProc_grp.comp_rec_t,
632 x_schedTbl OUT NOCOPY num_tbl_t,
633 x_qtyTbl OUT NOCOPY num_tbl_t,
634 x_returnStatus OUT NOCOPY VARCHAR2) is
635 l_mmttCount NUMBER;
636 l_include_yield NUMBER ; -- added for bug 5491202
637 /* Bug Num 5356098 */
638 l_schedTbl num_tbl_t := num_tbl_t();
639 l_qtyTbl num_tbl_t := num_tbl_t();
640 begin
641 x_schedTbl := l_schedTbl ;
642 x_qtyTbl := l_qtyTbl ;
643 --return status is set to success at the last line of the procedure.
644 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
645
646 if(p_issueRec.wipSupplyType = wip_constants.push) then
647 /* Fix for bug 5373061: Added date_released condition */
648 if(p_issueRec.txnActionID in (wip_constants.isscomp_action, wip_constants.issnegc_action)) then
649 select wro.repetitive_schedule_id,
650 wro.required_quantity - wro.quantity_issued
651 bulk collect into x_schedTbl, x_qtyTbl
652 from wip_requirement_operations wro,
653 wip_repetitive_schedules wrs
654 where wro.wip_entity_id = p_issueRec.wipEntityID
655 and wro.inventory_item_id = p_issueRec.itemID
656 and wro.operation_seq_num = p_issueRec.opSeqNum
657 and sign(wro.quantity_per_assembly) = sign(p_issueRec.negReqFlag)
658 and wrs.repetitive_schedule_id = wro.repetitive_schedule_id
659 and wrs.line_id = p_issueRec.repLineID
660 and sign(wro.required_quantity) = p_issueRec.negReqFlag
661 and wrs.status_type in (3,4) /* bug3338344*/
662 and wrs.date_released < p_issueRec.txnDate
663 order by wrs.first_unit_start_date;
664 else -- a return transaction
665 select wro.repetitive_schedule_id,--same as issue cursor above except for order by
666 wro.quantity_issued
667 bulk collect into x_schedTbl, x_qtyTbl
668 from wip_requirement_operations wro,
669 wip_repetitive_schedules wrs
670 where wro.wip_entity_id = p_issueRec.wipEntityID
671 and wro.inventory_item_id = p_issueRec.itemID
672 and wro.operation_seq_num = p_issueRec.opSeqNum
673 and sign(wro.quantity_per_assembly) = sign(p_issueRec.negReqFlag)
674 and wrs.repetitive_schedule_id = wro.repetitive_schedule_id
675 and wrs.line_id = p_issueRec.repLineID
676 and sign(wro.required_quantity) = p_issueRec.negReqFlag
677 and wrs.status_type in (3,4) /* bug3338344*/
678 and wrs.date_released < p_issueRec.txnDate
679 order by wrs.first_unit_start_date desc;
680 end if;
681 elsif(p_issueRec.movTxnID is not null) then
682
683 -- bug 5491202 added the following sql to find org level parameter
684 -- for including or excluding component_yield_factor
685
686 select nvl(include_component_yield,1)
687 into l_include_yield
688 from wip_parameters
689 where organization_id = p_issueRec.orgID ;
690
691 -- bug 5491202 end changes
692
693 if(p_issueRec.txnActionID in (wip_constants.isscomp_action, wip_constants.issnegc_action)) then
694 select wro.repetitive_schedule_id,
695 -- bug 5491202 changed the next line to include component yield
696 -- wro.quantity_per_assembly * wmta.primary_quantity
697 round( wro.quantity_per_assembly * wmta.primary_quantity
698 / decode(l_include_yield,2,1,nvl(wro.component_yield_factor,1)),
699 wip_constants.inv_max_precision)
700 bulk collect into x_schedTbl, x_qtyTbl
701 from wip_repetitive_schedules wrs,
702 wip_requirement_operations wro,
703 wip_move_txn_allocations wmta
704 where wmta.transaction_id = p_issueRec.movTxnID
705 and wro.repetitive_schedule_id = wmta.repetitive_schedule_id
706 and wro.repetitive_schedule_id = wrs.repetitive_schedule_id
707 and wro.wip_entity_id = p_issueRec.wipEntityID
708 and wro.inventory_item_id = p_issueRec.itemID
709 and wro.operation_seq_num = p_issueRec.opSeqNum
710 and wro.wip_supply_type = p_issueRec.wipSupplyType
711 and wro.quantity_per_assembly <> 0
712 and sign(wro.required_quantity) = p_issueRec.negReqFlag
713 /* and wrs.status_type in (3,4) */ /* bug3338344 removed for bug5137228 (fp5015515) */
714 order by wrs.first_unit_start_date;
715 else --return txn
716 select wro.repetitive_schedule_id,
717 -- bug 5491202 changed the next line to include component yield
718 -- wro.quantity_per_assembly * wmta.primary_quantity schedQty
719 round( wro.quantity_per_assembly * wmta.primary_quantity
720 / decode(l_include_yield,2,1,nvl(wro.component_yield_factor,1)),
721 wip_constants.inv_max_precision)
722 bulk collect into x_schedTbl, x_qtyTbl
723 from wip_repetitive_schedules wrs,
724 wip_requirement_operations wro,
725 wip_move_txn_allocations wmta
726 where wmta.transaction_id = p_issueRec.movTxnID
727 and wro.repetitive_schedule_id = wmta.repetitive_schedule_id
728 and wro.repetitive_schedule_id = wrs.repetitive_schedule_id
729 and wro.wip_entity_id = p_issueRec.wipEntityID
730 and wro.inventory_item_id = p_issueRec.itemID
731 and wro.operation_seq_num = p_issueRec.opSeqNum
732 and wro.wip_supply_type = p_issueRec.wipSupplyType
733 and wro.quantity_per_assembly <> 0
734 and sign(wro.required_quantity) = p_issueRec.negReqFlag
735 /* and wrs.status_type in (3,4) */ /* bug3338344 removed for bug5137228 (fp5015515) */
736 order by wrs.first_unit_start_date desc;
737 end if;
738
739 elsif(p_issueRec.cplTxnID is not null) then
740 --the completion transaction could either be in mmtt or mmt...
741 select count(*)
742 into l_mmttCount
743 from mtl_material_transactions_temp
744 where completion_transaction_id = p_issueRec.cplTxnID
745 and transaction_action_id in (wip_constants.cplassy_action, wip_constants.retassy_action);
746
747 if(l_mmttCount > 0) then
748 if(p_issueRec.txnActionID in (wip_constants.isscomp_action, wip_constants.issnegc_action)) then
749 select wro.repetitive_schedule_id,
750 wro.quantity_per_assembly * sum(mmta.primary_quantity) schedQty
751 bulk collect into x_schedTbl, x_qtyTbl
752 from wip_repetitive_schedules wrs,
753 wip_requirement_operations wro,
754 mtl_material_txn_allocations mmta,
755 mtl_material_transactions_temp mmtt --the MMTT row(s) are the assy rows
756 where mmta.transaction_id = mmtt.material_allocation_temp_id
757 and mmtt.completion_transaction_id = p_issueRec.cplTxnID
758 and mmtt.transaction_action_id in (wip_constants.cplassy_action, wip_constants.retassy_action)
759 and wro.wip_entity_id = p_issueRec.wipEntityID
760 and wro.repetitive_schedule_id = mmta.repetitive_schedule_id
761 and wro.repetitive_schedule_id = wrs.repetitive_schedule_id
762 and wro.inventory_item_id = p_issueRec.itemID
763 and wro.operation_seq_num = p_issueRec.opSeqNum
764 and wro.wip_supply_type = p_issueRec.wipSupplyType
765 and wro.quantity_per_assembly <> 0
766 and sign(wro.required_quantity) = p_issueRec.negReqFlag
767 /* and wrs.status_type in (3,4) */ /* bug3338344 removed for bug5137228 (fp5015515) */
768 group by wro.repetitive_schedule_id, wro.quantity_per_assembly, wrs.first_unit_start_date
769 order by wrs.first_unit_start_date;
770
771 /* Fix for Bug#5030360 (fp5201404). Check into wip_mtl_allocations_temp as
772 Completion record may not be processed yet
773 to be present in mtl_material_txn_allocations
774 */
775
776 if sql%NOTFOUND then
777 select wro.repetitive_schedule_id,
778 wro.quantity_per_assembly * sum(wmat.primary_quantity) schedQty
779 bulk collect into x_schedTbl, x_qtyTbl
780 from wip_repetitive_schedules wrs,
781 wip_requirement_operations wro,
782 wip_mtl_allocations_temp wmat,
783 mtl_material_transactions_temp mmtt --the MMTT row(s) are the assy rows
784 where wmat.transaction_temp_id = mmtt.transaction_temp_id
785 and mmtt.completion_transaction_id = p_issueRec.cplTxnID
786 and mmtt.transaction_action_id in (wip_constants.cplassy_action, wip_constants.retassy_action)
787 and wro.wip_entity_id = p_issueRec.wipEntityID
788 and wro.repetitive_schedule_id = wmat.repetitive_schedule_id
789 and wro.repetitive_schedule_id = wrs.repetitive_schedule_id
790 and wro.inventory_item_id = p_issueRec.itemID
791 and wro.operation_seq_num = p_issueRec.opSeqNum
792 and wro.wip_supply_type = p_issueRec.wipSupplyType
793 and wro.quantity_per_assembly <> 0
794 and sign(wro.required_quantity) = p_issueRec.negReqFlag
795 group by wro.repetitive_schedule_id, wro.quantity_per_assembly, wrs.first_unit_start_date
796 order by wrs.first_unit_start_date;
797 end if ;
798 /* End for Bug#5030360 (fp52014040) */
799
800 else --return txn
801 select wro.repetitive_schedule_id,
802 wro.quantity_per_assembly * sum(mmta.primary_quantity) schedQty
803 bulk collect into x_schedTbl, x_qtyTbl
804 from wip_repetitive_schedules wrs,
805 wip_requirement_operations wro,
806 mtl_material_txn_allocations mmta,
807 mtl_material_transactions_temp mmtt
808 where mmta.transaction_id = mmtt.material_allocation_temp_id
809 and mmtt.completion_transaction_id = p_issueRec.cplTxnID
810 and mmtt.transaction_action_id in (wip_constants.cplassy_action, wip_constants.retassy_action)
811 and wro.wip_entity_id = p_issueRec.wipEntityID
812 and wro.repetitive_schedule_id = mmta.repetitive_schedule_id
813 and wro.repetitive_schedule_id = wrs.repetitive_schedule_id
814 and wro.inventory_item_id = p_issueRec.itemID
815 and wro.operation_seq_num = p_issueRec.opSeqNum
816 and wro.wip_supply_type = p_issueRec.wipSupplyType
817 and wro.quantity_per_assembly <> 0
818 and sign(wro.required_quantity) = p_issueRec.negReqFlag
819 /* and wrs.status_type in (3,4) */ /* bug3338344 removed for bug5137228 (fp5015515) */
820 group by wro.repetitive_schedule_id, wro.quantity_per_assembly, wrs.first_unit_start_date
821 order by wrs.first_unit_start_date desc;
822
823 /* Fix for Bug#5030360 (fp5201404). Check into wip_mtl_allocations_temp as
824 Completion record may not be processed yet
825 to be present in mtl_material_txn_allocations
826 */
827 if sql%NOTFOUND then
828 select wro.repetitive_schedule_id,
829 wro.quantity_per_assembly * sum(wmat.primary_quantity) schedQty
830 bulk collect into x_schedTbl, x_qtyTbl
831 from wip_repetitive_schedules wrs,
832 wip_requirement_operations wro,
833 wip_mtl_allocations_temp wmat,
834 mtl_material_transactions_temp mmtt --the MMTT row(s) are the assy rows
835 where wmat.transaction_temp_id = mmtt.transaction_temp_id
836 and mmtt.completion_transaction_id = p_issueRec.cplTxnID
837 and mmtt.transaction_action_id in (wip_constants.cplassy_action, wip_constants.retassy_action)
838 and wro.wip_entity_id = p_issueRec.wipEntityID
839 and wro.repetitive_schedule_id = wmat.repetitive_schedule_id
840 and wro.repetitive_schedule_id = wrs.repetitive_schedule_id
841 and wro.inventory_item_id = p_issueRec.itemID
842 and wro.operation_seq_num = p_issueRec.opSeqNum
843 and wro.wip_supply_type = p_issueRec.wipSupplyType
844 and wro.quantity_per_assembly <> 0
845 and sign(wro.required_quantity) = p_issueRec.negReqFlag
846 group by wro.repetitive_schedule_id, wro.quantity_per_assembly, wrs.first_unit_start_date
847 order by wrs.first_unit_start_date desc;
848 end if ;
849 /* End for Bug#5030360 (fp5201404) */
850 end if;--issue return stmt
851 --This case occurs when the assembly is processed online and the components background. This
852 --can happen through the desktop form since there are 2 profiles, one governing assy txn mode
853 --and the other governing component txn mode if the assy mode is online...
854 else --l_mmttCount == 0 => assy row is in MMT
855 if(p_issueRec.txnActionID in (wip_constants.isscomp_action, wip_constants.issnegc_action)) then
856 select wro.repetitive_schedule_id,
857 wro.quantity_per_assembly * sum(mmta.primary_quantity) schedQty
858 bulk collect into x_schedTbl, x_qtyTbl
859 from wip_repetitive_schedules wrs,
860 wip_requirement_operations wro,
861 mtl_material_txn_allocations mmta,
862 mtl_material_transactions mmt
863 where mmta.transaction_id = mmt.transaction_id
864 and mmt.completion_transaction_id = p_issueRec.cplTxnID
865 and mmt.transaction_action_id in (wip_constants.cplassy_action, wip_constants.retassy_action)
866 and wro.wip_entity_id = p_issueRec.wipEntityID
867 and wro.repetitive_schedule_id = mmta.repetitive_schedule_id
868 and wro.repetitive_schedule_id = wrs.repetitive_schedule_id
869 and wro.inventory_item_id = p_issueRec.itemID
870 and wro.operation_seq_num = p_issueRec.opSeqNum
871 and wro.wip_supply_type = p_issueRec.wipSupplyType
872 and wro.quantity_per_assembly <> 0
873 and sign(wro.required_quantity) = p_issueRec.negReqFlag
874 /* and wrs.status_type in (3,4) */ /* bug3338344 removed for bug5137228 (fp5015515) */
875 group by wro.repetitive_schedule_id, wro.quantity_per_assembly, wrs.first_unit_start_date
876 order by wrs.first_unit_start_date;
877 else --return txn
878 select wro.repetitive_schedule_id,
879 wro.quantity_per_assembly * sum(mmta.primary_quantity) schedQty
880 bulk collect into x_schedTbl, x_qtyTbl
881 from wip_repetitive_schedules wrs,
882 wip_requirement_operations wro,
883 mtl_material_txn_allocations mmta,
884 mtl_material_transactions mmt
885 where mmta.transaction_id = mmt.transaction_id
886 and mmt.completion_transaction_id = p_issueRec.cplTxnID
887 and mmt.transaction_action_id in (wip_constants.cplassy_action, wip_constants.retassy_action)
888 and wro.wip_entity_id = p_issueRec.wipEntityID
889 and wro.repetitive_schedule_id = mmta.repetitive_schedule_id
890 and wro.repetitive_schedule_id = wrs.repetitive_schedule_id
891 and wro.inventory_item_id = p_issueRec.itemID
892 and wro.operation_seq_num = p_issueRec.opSeqNum
893 and wro.wip_supply_type = p_issueRec.wipSupplyType
894 and wro.quantity_per_assembly <> 0
895 and sign(wro.required_quantity) = p_issueRec.negReqFlag
896 /* and wrs.status_type in (3,4)*/ /* bug3338344 removed for bug5137228 (fp5015515) */
897 group by wro.repetitive_schedule_id, wro.quantity_per_assembly, wrs.first_unit_start_date
898 order by wrs.first_unit_start_date desc;
899 end if;--issue/return stmt
900 end if; --MMTT/MMT stmt
901 end if; --supply type stmt
902 x_returnStatus := fnd_api.g_ret_sts_success;
903 --only errors that can occur are sql exceptions. let those fall through to the calling fn
904 end populateRepScheds;
905
906 procedure processRepetitive(p_issueRec IN OUT NOCOPY wip_mtlTempProc_grp.comp_rec_t,
907 x_returnStatus OUT NOCOPY VARCHAR2) IS
908 /* Fix for bug 4390097: Rounding l_remQty to prevent wrong quantities in MMTA */
909 -- l_remQty NUMBER := round(abs(p_issueRec.primaryQty),5);
910 /*Fix for bug 5374443: Need not round l_remQty. It contains MMTT primary quantity which is already rounded to 6 decimals. */
911 l_remQty NUMBER := abs(p_issueRec.primaryQty);
912 l_schedID NUMBER;
913 l_excessQtySchedID NUMBER;--if not enough open qty for txn, excess qty goes to this sched
914 l_schedQty NUMBER;
915 l_issueQty NUMBER;
916 l_params wip_logger.param_tbl_t;
917 l_errMsg VARCHAR2(240);
918 l_returnStatus VARCHAR2(1);
919 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
920 l_schedTbl num_tbl_t;--TABLE OF NUMBER;
921 l_qtyTbl num_tbl_t;--TABLE OF NUMBER;
922 l_mmta_schedIdTbl num_tbl_t := num_tbl_t();
923 l_mmta_priQtyTbl num_tbl_t := num_tbl_t();
924 l_mmta_txnQtyTbl num_tbl_t := num_tbl_t();
925 l_mmtaRowCount NUMBER := 0;
926
927 begin
928 if (l_logLevel <= wip_constants.trace_logging) then
929 fillIssueParamTbl(p_issueRec => p_issueRec,
930 x_params => l_params);
931 wip_logger.entryPoint(p_procName => 'wip_mtlProc_priv.processRepetitive',
932 p_params => l_params,
933 x_returnStatus => x_returnStatus);
934 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
935 raise fnd_api.g_exc_unexpected_error;
936 end if;
937 end if;
938
939 if (l_logLevel <= wip_constants.full_logging) then
940 wip_logger.log('remaining qty:' || l_remQty, l_returnStatus);
941 end if;
942
943 populateRepScheds(p_issueRec => p_issueRec,
944 x_schedTbl => l_schedTbl,
945 x_qtyTbl => l_qtyTbl,
946 x_returnStatus => x_returnStatus);
947
948 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
949 raise fnd_api.g_exc_unexpected_error;
950 end if;
951
952 if(p_issueRec.mtlTxnID is null) then
953 update mtl_material_transactions_temp
954 set material_allocation_temp_id = mtl_material_transactions_s.nextval
955 where transaction_temp_id = p_issueRec.txnTmpID returning material_allocation_temp_id into p_issueRec.mtlTxnID;
956 end if;
957
958 --if over-issuing or returning, apply excess qty to the last schedule
959 if(l_schedTbl.count > 0) then
960 l_excessQtySchedID := l_schedTbl(l_schedTbl.count);
961 end if;
962
963 for i in 1..l_schedTbl.count LOOP
964 exit when l_remQty = 0;
965
966 -- Fix for Bug#4390097. Round l_qtyTbl(i) as corresponding MMTT qty is
967 -- already rounded to 5 decimal precision.
968 --l_qtyTbl(i) := round(l_qtyTbl(i), 5) ;
969
970 -- Fix for Bug#5374443 : Round l_qtyTbl(i) to 6 decimals as corresponding MMTT qty is rounded to 6 decimal precision.
971 l_qtyTbl(i) := round(l_qtyTbl(i),6);
972
973 if (l_logLevel <= wip_constants.full_logging) then
974 wip_logger.log('start loop', l_returnStatus);
975 wip_logger.log(' sched_id:' || l_schedTbl(i), l_returnStatus);
976 wip_logger.log(' sched_qty:' || l_qtyTbl(i), l_returnStatus);
977 end if;
978
979 if(round(l_qtyTbl(i), wip_constants.inv_max_precision) = 0) then
980 goto END_OF_LOOP;
981 end if;
982 if(l_remQty > abs(l_qtyTbl(i))) then
983 l_remQty := l_remQty - abs(l_qtyTbl(i));
984 l_issueQty := sign(p_issueRec.primaryQty) * abs(l_qtyTbl(i));
985 else
986 l_issueQty := l_remQty * sign(p_issueRec.primaryQty);
987 l_remQty := 0;
988 end if;
989 if (l_logLevel <= wip_constants.full_logging) then
990 wip_logger.log(' l_remQty:' || l_remQty, l_returnStatus);
991 wip_logger.log(' l_issQty:' || l_issueQty, l_returnStatus);
992 wip_logger.log(' sign(priQty):' || sign(p_issueRec.primaryQty), l_returnStatus);
993 end if;
994
995 if(mod(l_mmtaRowCount, g_extendAmount) = 0) then
996 l_mmta_schedIdTbl.extend(g_extendAmount);
997 l_mmta_priQtyTbl.extend(g_extendAmount);
998 l_mmta_txnQtyTbl.extend(g_extendAmount);
999 end if;
1000 l_mmtaRowCount := l_mmtaRowCount + 1;
1001 l_mmta_schedIdTbl(l_mmtaRowCount) := l_schedTbl(i);
1002 l_mmta_priQtyTbl(l_mmtaRowCount) := -1 * l_issueQty; --make qty relative to inv for quantities
1003 l_mmta_txnQtyTbl(l_mmtaRowCount) := round(-1 * (l_issueQty/p_issueRec.primaryQty) * p_issueRec.txnQty, wip_constants.inv_max_precision);
1004
1005 if (l_logLevel <= wip_constants.full_logging) then
1006 wip_logger.log('inserted sched:' || l_schedTbl(i) || '; qty:' || l_mmta_priQtyTbl(l_mmtaRowCount), l_returnStatus);
1007 end if;
1008
1009 processTxn(p_issueRec => p_issueRec,
1010 p_issueQty => l_issueQty, --override p_issueRec.primaryQty
1011 p_repSchedID => l_schedTbl(i),
1012 x_returnStatus => x_returnStatus);
1013
1014 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
1015 l_errMsg := 'processTxn for schedule ' || l_schedTbl(i) || ' failed.';
1016 raise fnd_api.g_exc_unexpected_error;
1017 end if;
1018 <<END_OF_LOOP>>
1019 null;
1020 end loop;
1021 if (l_logLevel <= wip_constants.full_logging) then
1022 wip_logger.log('remQty:' || l_remQty, l_returnStatus);
1023 end if;
1024
1025 if(l_remQty > 0) then --should only happen for push txns
1026 if(l_excessQtySchedID is null) then
1027 /* Fix for bug 5373061: Added status_type and date_released checks to pick valid schedules only */
1028 select repetitive_schedule_id --the requirement doesn't exist. Find the earliest open schedule
1029 into l_excessQtySchedID
1030 from wip_repetitive_schedules wrs
1031 where wrs.wip_entity_id = p_issueRec.wipEntityID
1032 and wrs.line_id = p_issueRec.repLineID
1033 and wrs.status_type in (WIP_CONSTANTS.RELEASED, WIP_CONSTANTS.COMP_CHRG)
1034 and wrs.date_released < p_issueRec.txnDate
1035 and first_unit_start_date = (select min(first_unit_start_date)
1036 from wip_repetitive_schedules
1037 where wip_entity_id = p_issueRec.wipEntityID
1038 and line_id = p_issueRec.repLineID
1039 and status_type in (WIP_CONSTANTS.RELEASED, WIP_CONSTANTS.COMP_CHRG)
1040 and date_released < p_issueRec.txnDate)
1041
1042 order by wrs.first_unit_start_date;
1043 end if;
1044
1045 if(l_mmtaRowCount = 0) then
1046 l_mmta_schedIdTbl.extend(g_extendAmount);
1047 l_mmta_priQtyTbl.extend(g_extendAmount);
1048 l_mmta_txnQtyTbl.extend(g_extendAmount);
1049 l_mmtaRowCount := l_mmtaRowCount + 1;
1050 end if;
1051
1052 l_mmta_schedIdTbl(l_mmtaRowCount) := l_excessQtySchedID;
1053 l_issueQty := l_remQty * sign(p_issueRec.primaryQty);--first get the sign correct
1054
1055 /* Fix for bug 5729726: Add excess quantity to the existing allocation quantity, if any, instead of over-writing the
1056 existing allocation quantity with the excess quantity. This will prevent incorrect allocation and MMT-MMTA Mismatch */
1057 l_mmta_priQtyTbl(l_mmtaRowCount) := nvl(l_mmta_priQtyTbl(l_mmtaRowCount),0) - l_issueQty;--make qty relative to inventory
1058 l_mmta_txnQtyTbl(l_mmtaRowCount) := nvl(l_mmta_txnQtyTbl(l_mmtaRowCount),0) + round(-1 * (l_issueQty/p_issueRec.primaryQty) * p_issueRec.txnQty, wip_constants.inv_max_precision);
1059
1060 -- l_mmta_priQtyTbl(l_mmtaRowCount) := -1 * l_issueQty;--make qty relative to inventory
1061 -- l_mmta_txnQtyTbl(l_mmtaRowCount) := round(-1 * (l_issueQty/p_issueRec.primaryQty) * p_issueRec.txnQty, wip_constants.inv_max_precision);
1062 /*End of fix 5729726:*/
1063
1064 processTxn(p_issueRec => p_issueRec,
1065 p_issueQty => l_issueQty,
1066 p_repSchedID => l_excessQtySchedID,
1067 x_returnStatus => x_returnStatus);
1068 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
1069 l_errMsg := 'processTxn for the first schedule ' || l_excessQtySchedID || ' failed.';
1070 raise fnd_api.g_exc_unexpected_error;
1071 end if;
1072 end if;
1073
1074 if (l_logLevel <= wip_constants.full_logging) then
1075 wip_logger.log('rowcount ' || l_mmtaRowCount || ' rows.', l_returnStatus);
1076 end if;
1077
1078 --now trim unused rows
1079 l_mmta_schedIdTbl.trim(g_extendAmount - mod(l_mmtaRowCount, g_extendAmount));
1080 l_mmta_priQtyTbl.trim(g_extendAmount - mod(l_mmtaRowCount, g_extendAmount));
1081 l_mmta_txnQtyTbl.trim(g_extendAmount - mod(l_mmtaRowCount, g_extendAmount));
1082
1083
1084 forall i in 1..l_mmta_schedIdTbl.count
1085 insert into mtl_material_txn_allocations
1086 (transaction_id,
1087 repetitive_schedule_id,
1088 organization_id,
1089 last_update_date,
1090 last_updated_by,
1091 creation_date,
1092 created_by,
1093 last_update_login,
1094 request_id,
1095 program_application_id,
1096 program_id,
1097 program_update_date,
1098 primary_quantity,
1099 transaction_quantity,
1100 transaction_date)
1101 values
1102 (p_issueRec.mtlTxnID,
1103 l_mmta_schedIdTbl(i),
1104 p_issueRec.orgID,
1105 sysdate,
1106 fnd_global.user_id,
1107 sysdate,
1108 fnd_global.user_id,
1109 fnd_global.conc_login_id,
1110 fnd_global.conc_request_id,
1111 fnd_global.prog_appl_id,
1112 fnd_global.conc_program_id,
1113 sysdate,
1114 l_mmta_priQtyTbl(i),
1115 l_mmta_txnQtyTbl(i),
1116 p_issueRec.txnDate);
1117
1118 if (l_logLevel <= wip_constants.full_logging) then
1119 wip_logger.log(SQL%ROWCOUNT || ' row inserted into MMTA', l_returnStatus);
1120 wip_logger.log('txn id' || p_issueRec.mtlTxnID, l_returnStatus);
1121 end if;
1122
1123 if (l_logLevel <= wip_constants.trace_logging) then
1124 wip_logger.exitPoint(p_procName => 'wip_mtlProc_priv.processRepetitive',
1125 p_procReturnStatus => x_returnStatus,
1126 p_msg => 'procedure success.',
1127 x_returnStatus => l_returnStatus); --discard logging return status
1128 end if;
1129 exception
1130 when fnd_api.g_exc_unexpected_error then
1131 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
1132 if (l_logLevel <= wip_constants.trace_logging) then
1133 wip_logger.exitPoint(p_procName => 'wip_mtlProc_priv.processRepetitive',
1134 p_procReturnStatus => x_returnStatus,
1135 p_msg => l_errMsg,
1136 x_returnStatus => l_returnStatus); --discard logging return status
1137 end if;
1138 when others then
1139 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
1140 fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_mtlProc_priv',
1141 p_procedure_name => 'processRepetitive',
1142 p_error_text => SQLERRM);
1143 if (l_logLevel <= wip_constants.trace_logging) then
1144 wip_logger.exitPoint(p_procName => 'wip_mtlProc_priv.processRepetitive',
1145 p_procReturnStatus => x_returnStatus,
1146 p_msg => 'unexpected error: ' || SQLERRM,
1147 x_returnStatus => l_returnStatus); --discard logging return status
1148 end if;
1149 end processRepetitive;
1150
1151
1152 procedure writeError(p_txnTmpID IN NUMBER) is
1153 l_errCode VARCHAR2(2000);
1154 l_errExpl VARCHAR2(2000);
1155 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
1156 l_returnStatus VARCHAR(1);
1157 begin
1158 wip_utilities.get_message_stack(p_delete_stack => fnd_api.g_false,
1159 p_msg => l_errExpl);
1160 fnd_message.set_name('WIP', 'MTL_PROC_FAIL');
1161 l_errCode := fnd_message.get;
1162 if (l_logLevel <= wip_constants.full_logging) then
1163 wip_logger.log('writeError: tempID is ' || p_txnTmpID, l_returnStatus);
1164 wip_logger.log('writeError: errCode is ' ||l_errCode, l_returnStatus);
1165 wip_logger.log('writeError: errExpl is ' ||l_errExpl, l_returnStatus);
1166 end if;
1167 update mtl_material_transactions_temp
1168 set error_code = substr(l_errCode, 1, 240),
1169 error_explanation = substr(l_errExpl, 1, 240),
1170 process_flag = 'E'
1171 where transaction_temp_id = p_txnTmpID;
1172 exception
1173 when others then
1174 null;
1175 end writeError;
1176
1177 PROCEDURE processOATxn(p_mtl_header_id IN NUMBER,
1178 x_returnStatus OUT NOCOPY VARCHAR2) IS
1179
1180 l_log_level NUMBER := fnd_log.g_current_runtime_level;
1181 l_ret_value NUMBER;
1182 l_error_msg VARCHAR2(1000);
1183 l_process_phase VARCHAR2(3);
1184 l_return_status VARCHAR(1);
1185 l_params wip_logger.param_tbl_t;
1186 BEGIN
1187 l_process_phase := '1';
1188 IF (l_log_level <= wip_constants.trace_logging) THEN
1189 l_params(1).paramName := 'p_mtl_header_id';
1190 l_params(1).paramValue := p_mtl_header_id;
1191 wip_logger.entryPoint(p_procName => 'wip_mtlProc_priv.processOATxn',
1192 p_params => l_params,
1193 x_returnStatus => l_return_status);
1194 END IF;
1195 l_process_phase := '2';
1196 SAVEPOINT s_oa_txn_proc;
1197 -- Validate and move records from MTI to MMTT.
1198 wip_mtlTempProc_priv.validateInterfaceTxns(
1199 p_txnHdrID => p_mtl_header_id,
1200 p_addMsgToStack => fnd_api.g_true, -- So that we can display to user
1201 p_rollbackOnErr => fnd_api.g_false,
1202 x_returnStatus => x_returnStatus);
1203
1204 IF(x_returnStatus <> fnd_api.g_ret_sts_success) THEN
1205 raise fnd_api.g_exc_unexpected_error;
1206 END IF;
1207
1208 l_process_phase := '3';
1209 wip_mtlTempProc_priv.processTemp
1210 (p_initMsgList => fnd_api.g_true,
1211 p_txnHdrID => p_mtl_header_id,
1212 p_txnMode => WIP_CONSTANTS.ONLINE,
1213 x_returnStatus => x_returnStatus,
1214 x_errorMsg => l_error_msg);
1215
1216 IF(x_returnStatus <> fnd_api.g_ret_sts_success) THEN
1217 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
1218 fnd_message.set_token('MESSAGE', l_error_msg);
1219 fnd_msg_pub.add;
1220 raise fnd_api.g_exc_unexpected_error;
1221 END IF;
1222 l_process_phase := '4';
1223 x_returnStatus := fnd_api.g_ret_sts_success;
1224
1225 -- write to the log file
1226 IF (l_log_level <= wip_constants.trace_logging) THEN
1227 wip_logger.exitPoint(p_procName => 'wip_mtlProc_priv.processOATxn',
1228 p_procReturnStatus => x_returnStatus,
1229 p_msg => 'procedure complete',
1230 x_returnStatus => l_return_status);
1231 END IF;
1232 -- close log file
1233 wip_logger.cleanUp(x_returnStatus => l_return_status);
1234 EXCEPTION
1235 WHEN fnd_api.g_exc_unexpected_error THEN
1236 ROLLBACK TO SAVEPOINT s_oa_txn_proc;
1237 x_returnStatus := fnd_api.g_ret_sts_error;
1238
1239 IF (l_log_level <= wip_constants.trace_logging) THEN
1240 wip_logger.exitPoint(p_procName => 'wip_mtlProc_priv.processOATxn',
1241 p_procReturnStatus => x_returnStatus,
1242 p_msg => 'wip_cplProc_priv.processOATxn failed : '
1243 || l_process_phase,
1244 x_returnStatus => l_return_status);
1245 END IF;
1246 -- close log file
1247 wip_logger.cleanUp(x_returnStatus => l_return_status);
1248 WHEN others THEN
1249 ROLLBACK TO SAVEPOINT s_oa_txn_proc;
1250 x_returnStatus := fnd_api.g_ret_sts_error;
1251 l_error_msg := ' unexpected error: ' || SQLERRM || 'SQLCODE = ' ||
1252 SQLCODE;
1253
1254 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
1255 fnd_message.set_token('MESSAGE', l_error_msg);
1256 fnd_msg_pub.add;
1257
1258 IF (l_log_level <= wip_constants.trace_logging) THEN
1259 wip_logger.exitPoint(p_procName => 'wip_cplProc_priv.processOATxn',
1260 p_procReturnStatus => x_returnStatus,
1261 p_msg => l_error_msg || ' : ' || l_process_phase,
1262 x_returnStatus => l_return_status);
1263 END IF;
1264 -- close log file
1265 wip_logger.cleanUp(x_returnStatus => l_return_status);
1266 END processOATxn;
1267
1268 end wip_mtlProc_priv;