[Home] [Help]
PACKAGE BODY: APPS.WIP_FLOWUTIL_PRIV
Source
1 package body wip_flowUtil_priv as
2 /* $Header: wipfscmb.pls 120.21.12010000.2 2008/10/07 05:59:10 awongwai ship $ */
3
4 function checkSubstitution(p_parentID in number) return varchar2;
5
6 procedure mergeComponents(p_parentID in number,
7 x_returnStatus out NOCOPY varchar2);
8
9 procedure generateIssueLocator(p_parentID in number,
10 x_returnStatus out NOCOPY varchar2);
11
12 /**
13 * This function does the default for flow transactions.
14 */
15 function deriveCompletion(p_scheduledFlag in number,
16 p_orgID in number,
17 p_itemID in number,
18 p_txnSrcID in number,
19 p_txnDate in date,
20 p_txnActionID in number,
21 p_schedNum in out NOCOPY varchar2,
22 p_srcProjID in out NOCOPY number,
23 p_projID in out NOCOPY number,
24 p_srcTaskID in out NOCOPY number,
25 p_taskID in out NOCOPY number,
26 p_bomRev in out NOCOPY varchar2,
27 p_rev in out NOCOPY varchar2,
28 p_bomRevDate in out NOCOPY date,
29 p_altBomDes in out NOCOPY varchar2,
30 p_routRev in out NOCOPY varchar2,
31 p_routRevDate in out NOCOPY date,
32 p_altRtgDes in out NOCOPY varchar2,
33 p_cplSubinv in out NOCOPY varchar2,
34 p_cplLocID in out NOCOPY number,
35 p_classCode in out NOCOPY varchar2) return varchar2;
36
37
38 /**
39 * This is to validate the interface record for flow parent record.
40 */
41 function validateInterfaceCompletion(p_rowid in rowid) return varchar2;
42
43 /**
44 * This is to derive and validate the flow interface records for the given
45 * header id.
46 */
47 procedure processFlowInterfaceRecords(p_txnHeaderID in number) is
48 cursor flow_c is
49 select rowid,
50 transaction_interface_id,
51 primary_quantity,
52 operation_seq_num,
53 scheduled_flag,
54 organization_id,
55 inventory_item_id,
56 transaction_source_id,
57 transaction_date,
58 transaction_type_id,
59 transaction_action_id,
60 schedule_number,
61 source_project_id,
62 project_id,
63 source_task_id,
64 task_id,
65 bom_revision,
66 revision,
67 bom_revision_date,
68 alternate_bom_designator,
69 routing_revision,
70 routing_revision_date,
71 alternate_routing_designator,
72 subinventory_code,
73 locator_id,
74 accounting_class,
75 acct_period_id,
76 completion_transaction_id,
77 transaction_batch_id,
78 transaction_batch_seq
79 from mtl_transactions_interface
80 where transaction_header_id = p_txnHeaderID
81 and transaction_source_type_id = 5
82 and process_flag = 1
83 and upper(nvl(flow_schedule, 'N')) = 'Y'
84 and transaction_action_id in (WIP_CONSTANTS.SCRASSY_ACTION,
85 WIP_CONSTANTS.CPLASSY_ACTION,
86 WIP_CONSTANTS.RETASSY_ACTION);
87 l_returnStatus varchar2(1);
88 l_validationException exception;
89
90 l_params wip_logger.param_tbl_t;
91
92 l_fromUI NUMBER;--> 0 if the record originated from oracle user interfaces
93
94 l_logLevel NUMBER := to_number(fnd_log.g_current_runtime_level);
95 l_src_code VARCHAR2(2000);
96 l_errMsg VARCHAR2(50); /* Fix for Bug#5187500. Changed it to 50 */
97 l_bf_count NUMBER ; /* Fix for Bug#5187500 */
98 l_lot_ser_count NUMBER ; /* Fix for Bug#5187500 */
99 l_lot_entry_type NUMBER ; /* Fix for Bug#5187500 */
100 l_nontxn_excluded VARCHAR2(1); --added for fix 5630078
101 begin
102 l_lot_entry_type := 0 ; /* Fix for Bug#5187500 */
103
104 if (l_logLevel <= wip_constants.trace_logging) then
105 l_params(1).paramName := 'p_txnHeaderID';
106 l_params(1).paramValue := p_txnHeaderID;
107 wip_logger.entryPoint(p_procName => 'wip_flowutil_priv.processFlowInterfaceRecords',
108 p_params => l_params,
109 x_returnStatus => l_returnStatus);
110 if(l_returnStatus <> fnd_api.g_ret_sts_success) then
111 raise fnd_api.g_exc_unexpected_error;
112 end if;
113 end if;
114
115 for flow_rec in flow_c loop
116 begin
117 select count(*)
118 into l_fromUI
119 from mtl_transactions_interface
120 where parent_id is not null
121 and parent_id = flow_rec.transaction_interface_id
122 and substitution_type_id is null;
123
124 if(l_fromUI = 0) then
125 l_returnStatus := deriveCompletion
126 (
127 flow_rec.scheduled_flag,
128 flow_rec.organization_id,
129 flow_rec.inventory_item_id,
130 flow_rec.transaction_source_id,
131 flow_rec.transaction_date,
132 flow_rec.transaction_action_id,
133 flow_rec.schedule_number,
134 flow_rec.source_project_id,
135 flow_rec.project_id,
136 flow_rec.source_task_id,
137 flow_rec.task_id,
138 flow_rec.bom_revision,
139 flow_rec.revision,
140 flow_rec.bom_revision_date,
141 flow_rec.alternate_bom_designator,
142 flow_rec.routing_revision,
143 flow_rec.routing_revision_date,
144 flow_rec.alternate_routing_designator,
145 flow_rec.subinventory_code,
146 flow_rec.locator_id,
147 flow_rec.accounting_class);
148 if ( l_returnStatus = fnd_api.g_ret_sts_success ) then
149 update mtl_transactions_interface
150 set schedule_number = flow_rec.schedule_number,
151 source_project_id = flow_rec.source_project_id,
152 project_id = flow_rec.project_id,
153 source_task_id = flow_rec.source_task_id,
154 task_id = flow_rec.task_id,
155 bom_revision = flow_rec.bom_revision,
156 revision = flow_rec.revision,
157 bom_revision_date = flow_rec.bom_revision_date,
158 alternate_bom_designator = flow_rec.alternate_bom_designator,
159 routing_revision = flow_rec.routing_revision,
160 routing_revision_date = flow_rec.routing_revision_date,
161 alternate_routing_designator = flow_rec.alternate_routing_designator,
162 subinventory_code = flow_rec.subinventory_code,
163 locator_id = flow_rec.locator_id,
164 accounting_class = flow_rec.accounting_class
165 where rowid = flow_rec.rowid;
166 else
167 fnd_message.set_name('WIP', 'WIP_ERROR_FLOW_DEFAULTING');
168 fnd_message.set_token('ENTITY1',to_char(flow_rec.transaction_interface_id));
169 wip_mti_pub.setMtiError(p_txnInterfaceID => flow_rec.transaction_interface_id,
170 p_errCode => null,
171 p_msgData => fnd_message.get);
172 l_errMsg := 'error defaulting';
173 raise l_validationException;
174 end if;
175
176 if ( validateInterfaceCompletion(flow_rec.rowid) <>
177 fnd_api.g_ret_sts_success ) then
178 l_errMsg := 'error validating';
179 raise l_validationException;
180 end if;
181
182 -- now validate the substitution records
183 if ( checkSubstitution(flow_rec.transaction_interface_id) <>
184 fnd_api.g_ret_sts_success ) then
185 l_errMsg := 'check sub error';
186 raise l_validationException;
187 end if;
188 end if;
189
190 --for both UI and background, create a flow schedule, this stmt needs to be
191 --after deriveCompletion() in case of background.
192 createFlowSchedule(p_txnInterfaceID => flow_rec.transaction_interface_id,
193 x_returnStatus => l_returnStatus,
194 x_wipEntityID => flow_rec.transaction_source_id);
195 if(l_returnStatus <> fnd_api.g_ret_sts_success) then
196 l_errMsg := 'createFlowSchedule error';
197 raise l_validationException;
198 end if;
199
200 --Bug 5181899, for schedules submitted from oracle ui, if user has submitted the txn
201 --with no component, then we should not re-explode the bill
202 select source_code
203 into l_src_code
204 from mtl_transactions_interface
205 where rowid = flow_rec.rowid;
206
207 if(l_src_code = 'WIP_FLOW_SCHEDULES_OA' OR l_src_code = 'WIP_FLOW_SCHEDULES')
208 then l_fromUI := 1;
209 end if;
210
211 if(l_fromUI = 0) then
212 -- explode the bom
213 explodeRequirementsToMTI(p_txnHeaderID => p_txnHeaderID,
214 p_parentID => flow_rec.transaction_interface_id,
215 p_txnTypeID => flow_rec.transaction_type_id,
216 p_assyID => flow_rec.inventory_item_id,
217 p_orgID => flow_rec.organization_id,
218 p_qty => flow_rec.primary_quantity,
219 p_altBomDesig => flow_rec.alternate_bom_designator,
220 p_altOption => 2,
221 /* Fix for bug#3423629 */ p_bomRevDate => flow_rec.bom_revision_date,
222 p_txnDate => flow_rec.transaction_date,
223 p_projectID => flow_rec.project_id,
224 p_taskID => flow_rec.task_id,
225 p_toOpSeqNum => flow_rec.operation_seq_num,
226 p_altRoutDesig => flow_rec.alternate_routing_designator,
227 p_acctPeriodID => flow_rec.acct_period_id,
228 p_txnMode => wip_constants.background,
229 p_lockFlag => wip_constants.yes,
230 p_txnSourceID => flow_rec.transaction_source_id,
231 p_cplTxnID => flow_rec.completion_transaction_id,
232 p_txnBatchID => flow_rec.transaction_batch_id,
233 p_txnBatchSeq => flow_rec.transaction_batch_seq + 1,
234 p_defaultPushSubinv => 'Y', --bug#5262858
235 x_returnStatus => l_returnStatus,
236 x_nontxn_excluded =>l_nontxn_excluded); --added for fix 5630078
237 if ( l_returnStatus <> fnd_api.g_ret_sts_success ) then
238 l_errMsg := 'explosion error';
239 raise l_validationException;
240 end if;
241
242 -- now merge the exploded records with those substitution records.
243 mergeComponents(flow_rec.transaction_interface_id, l_returnStatus);
244 if ( l_returnStatus <> fnd_api.g_ret_sts_success ) then
245 l_errMsg := 'component merge error';
246 raise l_validationException;
247 end if;
248
249 -- Start for Fix for Bug#5187500. Default Lots
250
251 l_bf_count := 0 ;
252 l_lot_ser_count := 0 ;
253
254 SELECT COUNT(*)
255 INTO l_bf_count
256 FROM mtl_transactions_interface
257 WHERE transaction_header_id = p_txnHeaderID
258 AND completion_transaction_id = flow_rec.completion_transaction_id
259 AND transaction_action_id IN (WIP_CONSTANTS.ISSCOMP_ACTION,
260 WIP_CONSTANTS.RETCOMP_ACTION,
261 WIP_CONSTANTS.ISSNEGC_ACTION,
262 WIP_CONSTANTS.RETNEGC_ACTION);
263 SELECT COUNT(*)
264 INTO l_lot_ser_count
265 FROM mtl_transactions_interface mti,
266 mtl_system_items msi
267 WHERE mti.organization_id = msi.organization_id
268 AND mti.inventory_item_id = msi.inventory_item_id
269 AND (msi.lot_control_code = WIP_CONSTANTS.LOT
270 OR
271 msi.serial_number_control_code IN(WIP_CONSTANTS.FULL_SN,
272 WIP_CONSTANTS.DYN_RCV_SN))
273 AND transaction_header_id = p_txnHeaderID
274 AND completion_transaction_id = flow_rec.completion_transaction_id
275 AND transaction_action_id IN (WIP_CONSTANTS.ISSCOMP_ACTION,
276 WIP_CONSTANTS.RETCOMP_ACTION,
277 WIP_CONSTANTS.ISSNEGC_ACTION,
278 WIP_CONSTANTS.RETNEGC_ACTION);
279
280 if (l_lot_entry_type = 0 ) then -- Check only once
281 SELECT backflush_lot_entry_type
282 INTO l_lot_entry_type
283 FROM wip_parameters
284 WHERE organization_id = flow_rec.organization_id ;
285 end if ;
286
287 IF ((l_bf_count <> 0) and (l_lot_ser_count <> 0) and
288 (l_lot_entry_type <> WIP_CONSTANTS.MAN_ENTRY)) THEN
289 -- derive lot for Components
290 wip_autoLotProc_priv.deriveLotsFromMTI
291 (p_orgID => flow_rec.organization_id,
292 p_wipEntityID => flow_rec.transaction_source_id,
293 p_txnHdrID => p_txnHeaderID,
294 p_cplTxnID => flow_rec.completion_transaction_id,
295 p_movTxnID => null,
296 p_childMovTxnID => null,
297 p_initMsgList => fnd_api.g_false,
298 p_endDebug => fnd_api.g_false,
299 x_returnStatus => l_returnStatus);
300 IF ((l_returnStatus = fnd_api.g_ret_sts_unexp_error) or
301 (l_returnStatus = fnd_api.g_ret_sts_error)) THEN
302 l_errMsg := 'wip_autoLotProc_priv.deriveLotsFromMTI failed';
303 raise l_validationException ;
304 END IF;
305 END IF;
306
307 -- End for Fix for Bug#5187500. Default Lots
308
309 -- update the schedule number column of MTI to the schedule number of the parent record
310 -- this way, if it erorred out in MMTT, the user can query up all the records for a completion
311 -- transaction
312 update mtl_transactions_interface
313 set schedule_number = flow_rec.schedule_number
314 where completion_transaction_id = flow_rec.completion_transaction_id
315 and organization_id = flow_rec.organization_id --fix for bug 4890147, add more criteria so no FTS is done
316 and parent_id = flow_rec.transaction_interface_id;
317
318 -- generate issue locator
319 if ( flow_rec.source_project_id is not null ) then
320 generateIssueLocator(flow_rec.transaction_interface_id, l_returnStatus);
321 if ( l_returnStatus <> fnd_api.g_ret_sts_success ) then
322 l_errMsg := 'issue locator error';
323 raise l_validationException;
324 end if;
325 end if;
326 end if;
327 exception
328 when l_validationException then
329 if (l_logLevel <= wip_constants.full_logging) then
330 wip_logger.log(p_msg => 'Error defaulting/validating interface ' ||
331 to_char(flow_rec.transaction_interface_id) || ':' || l_errMsg,
332 x_returnStatus => l_returnStatus);
333 end if;
334 -- skip this one, validate next record. pl/sql doesn't have continue;
335 end;
336 end loop;
337
338 if (l_logLevel <= wip_constants.trace_logging) then
339 wip_logger.exitPoint(p_procName => 'wip_flowutil_priv.processFlowInterfaceRecords',
340 p_procReturnStatus => fnd_api.g_ret_sts_success,
341 p_msg => 'Finished processFlowInterfaceRecords',
342 x_returnStatus => l_returnStatus); --discard logging return status
343 end if;
344 end processFlowInterfaceRecords;
345
346
347
348 /**
349 * This function does the default for flow transactions.
350 * It doesn''t take the rowid so that everything can be selected from interface table.
351 * Instead, it takes all the parameter. The reason is that this might be called from
352 * the forms. It''s up to the caller to check the return value and set the error
353 * message. 1 means success and 0 means error.
354 */
355 function deriveCompletion(p_scheduledFlag in number,
356 p_orgID in number,
357 p_itemID in number,
358 p_txnSrcID in number,
359 p_txnDate in date,
360 p_txnActionID in number,
361 p_schedNum in out NOCOPY varchar2,
362 p_srcProjID in out NOCOPY number,
363 p_projID in out NOCOPY number,
364 p_srcTaskID in out NOCOPY number,
365 p_taskID in out NOCOPY number,
366 p_bomRev in out NOCOPY varchar2,
367 p_rev in out NOCOPY varchar2,
368 p_bomRevDate in out NOCOPY date,
369 p_altBomDes in out NOCOPY varchar2,
370 p_routRev in out NOCOPY varchar2,
371 p_routRevDate in out NOCOPY date,
372 p_altRtgDes in out NOCOPY varchar2,
373 p_cplSubinv in out NOCOPY varchar2,
374 p_cplLocID in out NOCOPY number,
375 p_classCode in out NOCOPY varchar2) return varchar2 is
376 l_errMsg varchar2(240);
377 l_dummy number;
378 begin
379 if ( p_scheduledFlag = 2 ) then
380 if ( (wip_flow_derive.schedule_number(
381 p_sched_num => p_schedNum) = 0 )
382 or (wip_flow_derive.src_project_id(
383 p_src_proj_id => p_srcProjID,
384 p_proj_id => p_projID) = 0 )
385 or (wip_flow_derive.src_task_id(
386 p_src_task_id => p_srcTaskID,
387 p_task_id => p_taskID) = 0 )
388 or (wip_flow_derive.bom_revision(
389 p_bom_rev => p_bomRev,
390 p_rev => p_rev,
391 p_bom_rev_date => p_bomRevDate,
392 p_item_id => p_itemID,
393 p_start_date => p_txnDate,
394 p_org_id => p_orgID) = 0 )
395 or (wip_flow_derive.routing_revision(
396 p_rout_rev => p_routRev,
397 p_rout_rev_date => p_routRevDate,
398 p_item_id => p_itemID,
399 p_start_date => p_txnDate,
400 p_org_id => p_orgID) = 0 )
401 or (p_txnActionID <> WIP_CONSTANTS.SCRASSY_ACTION and
402 wip_flow_derive.completion_sub(
403 p_comp_sub => p_cplSubinv,
404 p_item_id => p_itemID,
405 p_org_id => p_orgID,
406 p_alt_rtg_des => p_altRtgDes) = 0 )
407 or (p_txnActionID <> WIP_CONSTANTS.SCRASSY_ACTION and
408 wip_flow_derive.completion_locator_id(
409 p_comp_loc => p_cplLocID,
410 p_item_id => p_itemID,
411 p_org_id => p_orgID,
412 p_alt_rtg_des => p_altRtgDes,
413 p_proj_id => p_srcProjID,
414 p_task_id => p_taskID,
415 p_comp_sub => p_cplSubinv) = 0 )
416 or (wip_flow_derive.class_code(
417 p_class_code => p_classCode,
418 p_err_mesg => l_errMsg,
419 p_org_id => p_orgID,
420 p_item_id => p_itemID,
421 p_wip_entity_type => 4,
422 p_project_id => p_srcProjID) = 0 ) ) then
423 return fnd_api.g_ret_sts_error;
424 end if;
425 else -- else of p_scheduledFlag = 2
426 if ( wip_flow_derive.scheduled_flow_derivation(
427 p_txn_action_id => p_txnActionID,
428 p_item_id => p_itemID,
429 p_org_id => p_orgID,
430 p_txn_src_id => p_txnSrcID,
431 p_sched_num => p_schedNum,
432 p_src_proj_id => p_srcProjID,
433 p_proj_id => p_projID,
434 p_src_task_id => p_srcTaskID,
435 p_task_id => p_taskID,
436 p_bom_rev => p_bomRev,
437 p_rev => p_rev,
438 p_bom_rev_date => p_bomRevDate,
439 p_rout_rev => p_routRev,
440 p_rout_rev_date => p_routRevDate,
441 p_comp_sub => p_cplSubinv,
442 p_class_code => p_classCode,
443 p_wip_entity_type => l_dummy,
444 p_comp_loc => p_cplLocID,
445 p_alt_rtg_des => p_altRtgDes,
446 p_alt_bom_des => p_altBomDes) = 0 ) then
447 return fnd_api.g_ret_sts_error;
448 end if;
449 end if;
450 return fnd_api.g_ret_sts_success;
451 exception
452 when others then
453 return fnd_api.g_ret_sts_error;
454 end deriveCompletion;
455
456
457 /**
458 * This is to validate the interface record for flow parent record.
459 * This function is to be called for validating interface row for flow txns
460 * It sets the error for those records that errors out. It the return values
461 * is 0, then it means there is validation errors for the given row.
462 */
463 function validateInterfaceCompletion(p_rowid in rowid) return varchar2 is
464 l_dummy number;
465 l_scheduleNumber varchar2(30);
466 l_interfaceID number;
467 begin
468 select schedule_number,
469 transaction_interface_id
470 into l_scheduleNumber,
471 l_interfaceID
472 from mtl_transactions_interface
473 where rowid = p_rowid;
474
475 if ( wip_flow_validation.primary_item_id(p_rowid => p_rowid) = 0 ) then
476 fnd_message.set_name('WIP', 'WIP_CANNOT_BUILD_ITEM');
477
478 elsif (wip_flow_validation.class_code(p_rowid => p_rowid) = 0 ) then
479 fnd_message.set_name('WIP', 'WIP_INTERFACE_INVALID_CLASS');
480
481 elsif (wip_flow_validation.bom_rev_date(p_rowid => p_rowid) = 0 ) then
482 fnd_message.set_name('WIP', 'WIP_INVALID_BOM_REVISION_DATE');
483
484 elsif (wip_flow_validation.bom_revision(p_rowid => p_rowid) = 0 ) then
485 fnd_message.set_name('WIP', 'WIP_INVALID_BOM_REVISION');
486
487 elsif (wip_flow_validation.rout_rev_date(p_rowid => p_rowid) = 0 ) then
488 fnd_message.set_name('WIP', 'WIP_INVALID_ROUT_REVISION_DATE');
489
490 elsif (wip_flow_validation.routing_revision(p_rowid => p_rowid) = 0 ) then
491 fnd_message.set_name('WIP', 'WIP_INVALID_ROUT_REVISION');
492
493 elsif (wip_flow_validation.alt_bom_desg(p_rowid => p_rowid) = 0 ) then
494 fnd_message.set_name('WIP', 'WIP_ML_ALTERNATE_BOM');
495
496 elsif (wip_flow_validation.alt_rout_desg(p_rowid => p_rowid) = 0 ) then
497 fnd_message.set_name('WIP', 'WIP_ML_ALTERNATE_ROUTING');
498
499 elsif (wip_flow_validation.completion_sub(p_rowid => p_rowid) = 0 ) then
500 fnd_message.set_name('WIP', 'WIP_INVALID_COMPLETION_SUB');
501
502 elsif (wip_flow_validation.completion_locator_id(p_rowid => p_rowid) = 0 ) then
503 fnd_message.set_name('WIP', 'WIP_INVALID_LOCATOR');
504
505 elsif (wip_flow_validation.demand_class(p_rowid => p_rowid) = 0 ) then
506 fnd_message.set_name('WIP', 'WIP_ML_DEMAND_CLASS');
507
508 elsif (wip_flow_validation.schedule_group_id(p_rowid => p_rowid) = 0 ) then
509 fnd_message.set_name('WIP', 'WIP_ML_SCHEDULE_GROUP');
510
511 elsif (wip_flow_validation.build_sequence(p_rowid => p_rowid) = 0 ) then
512 fnd_message.set_name('WIP', 'WIP_BUILD_SEQUENCE');
513
514 elsif (wip_flow_validation.line_id(p_rowid => p_rowid) = 0 ) then
515 fnd_message.set_name('WIP', 'WIP_ML_LINE_ID');
516
517 elsif (wip_flow_validation.project_id(p_rowid => p_rowid) = 0 ) then
518 fnd_message.set_name('WIP', 'WIP_INVALID_PROJECT');
519
520 elsif (wip_flow_validation.task_id(p_rowid => p_rowid) = 0 ) then
521 fnd_message.set_name('WIP', 'WIP_INVALID_TASK');
522
523 elsif (wip_flow_validation.schedule_number(p_rowid => p_rowid) = 0 ) then
524 fnd_message.set_name('WIP', 'WIP_INVALID_SCHEDULE_NUMBER');
525
526 elsif (wip_flow_validation.unit_number(p_rowid => p_rowid) = 0 ) then
527 fnd_message.set_name('WIP', 'UEFF-UNIT NUMBER INVALID');
528 else
529 return fnd_api.g_ret_sts_success;
530 end if;
531
532 wip_mti_pub.setMtiError(p_txnInterfaceID => l_interfaceID,
533 p_errCode => null,
534 p_msgData => fnd_message.get);
535 return fnd_api.g_ret_sts_error;
536 exception
537 when others then
538 fnd_message.set_name('WIP', 'WIP_ERROR_FLOW_VALIDATION');
539 fnd_message.set_token('ENTITY1', l_scheduleNumber);
540 wip_mti_pub.setMtiError(p_txnInterfaceID => l_interfaceID,
541 p_errCode => null,
542 p_msgData => fnd_message.get);
543 return fnd_api.g_ret_sts_error;
544 end validateInterfaceCompletion;
545
546
547 /**
548 * This procedure validates the substitution type id and the substitution_item_id
549 * and inventory_item_id. It also checks the operation seq num?
550 * If there is any valiation error, then it will set the process_flag to 3 for
551 * the parent record as well as all the child records.
552 */
553 function checkSubstitution(p_parentID in number) return varchar2 is
554 cursor subs_c is
555 select inventory_item_id,
556 organization_id,
557 substitution_item_id,
558 substitution_type_id,
559 operation_seq_num
560 from mtl_transactions_interface
561 where parent_id = p_parentID
562 and process_flag = 1;
563
564 l_result number := 0;
565 l_seeEngItem number;
566 l_errMsg varchar2(240);
567 begin
568 -- validate substitution_type_id
569 -- 1: Change
570 -- 2: Delete
571 -- 3: Add
572 -- 4: Lot/Serial
573 begin
574 select count(*)
575 into l_result
576 from mtl_transactions_interface
577 where parent_id = p_parentID
578 and process_flag = 1
579 and nvl(substitution_type_id, -1) not in (1, 2, 3, 4);
580
581 if ( l_result > 0 ) then
582 fnd_message.set_name('WIP', 'WIP_ERROR_SUBST_TYPE');
583 wip_mti_pub.setMtiError(p_parentID,
584 'substitution_type_id',
585 substrb(fnd_message.get, 1, 240));
586 return fnd_api.g_ret_sts_error;
587 end if;
588
589 select count(*)
590 into l_result
591 from mtl_transactions_interface
592 where parent_id = p_parentID
593 and process_flag = 1
594 and nvl(flow_schedule, 'Y') <> 'Y';
595
596 if ( l_result > 0 ) then
597 fnd_message.set_name('WIP', 'WIP_FLOW_FLAG_ERROR');
598 wip_mti_pub.setMtiError(p_parentID,
599 'flow_schedule',
600 substrb(fnd_message.get, 1, 240));
601 return fnd_api.g_ret_sts_error;
602 end if;
603 exception
604 when others then
605 null; -- no substitution record
606 end;
607
608 begin
609 l_seeEngItem := to_number(fnd_profile.value('WIP_SEE_ENG_ITEMS'));
610 exception
611 when others then
612 l_seeEngItem := 2; -- default to not an engineering item
613 end;
614
615 for sub_rec in subs_c loop
616 if ( sub_rec.substitution_type_id <> 3 ) then
617 l_result := 0;
618 select 1
619 into l_result
620 from mtl_system_items msi
621 where msi.organization_id = sub_rec.organization_id
622 and msi.inventory_item_id = sub_rec.inventory_item_id
623 and msi.mtl_transactions_enabled_flag = 'Y'
624 and msi.inventory_item_flag = 'Y'
625 and msi.bom_enabled_flag = 'Y'
626 and msi.eng_item_flag = decode(l_seeEngItem,
627 1,
628 msi.eng_item_flag,
629 'N')
630 and msi.bom_item_type = 4; -- standard type
631 if ( l_result = 0 ) then
632 fnd_message.set_name('WIP', 'WIP_ERROR_SUBST_ASSEMBLY');
633 l_errMsg := 'Original item id ' || to_char(sub_rec.inventory_item_id) ||
634 ' at op seq ' || to_char(sub_rec.operation_seq_num) || '.';
635 fnd_message.set_token('ENTITY1', l_errMsg);
636 fnd_message.set_token('ENTITY2', 'Original Component');
637 wip_mti_pub.setMtiError(p_parentID,
638 'inventory_item_id',
639 substrb(fnd_message.get, 1, 240));
640 return fnd_api.g_ret_sts_error;
641 end if;
642 end if;
643
644 if ( sub_rec.substitution_type_id in (1, 3) ) then
645 l_result := 0;
646 select 1
647 into l_result
648 from mtl_system_items msi
649 where msi.organization_id = sub_rec.organization_id
650 and msi.inventory_item_id = sub_rec.substitution_item_id
651 and msi.mtl_transactions_enabled_flag = 'Y'
652 and msi.inventory_item_flag = 'Y'
653 and msi.bom_enabled_flag = 'Y'
654 and msi.eng_item_flag = decode(l_seeEngItem,
655 1,
656 msi.eng_item_flag,
657 'N')
658 and msi.bom_item_type = 4; -- standard type
659 if ( l_result = 0 ) then
660 fnd_message.set_name('WIP', 'WIP_ERROR_SUBST_ASSEMBLY');
661 l_errMsg := 'Original item id ' || to_char(sub_rec.inventory_item_id) ||
662 ' at op seq ' || to_char(sub_rec.operation_seq_num) || '.';
663 fnd_message.set_token('ENTITY1', l_errMsg);
664 fnd_message.set_token('ENTITY2', 'Substitution Component');
665 wip_mti_pub.setMtiError(p_parentID,
666 'substitution_item_id',
667 substrb(fnd_message.get, 1, 240));
668 return fnd_api.g_ret_sts_error;
669 end if;
670 end if;
671 end loop;
672
673 return fnd_api.g_ret_sts_success;
674 end checkSubstitution;
675
676
677 /**
678 * This procedure explodes the BOM and insert the material requirement into
679 * mti table under the given header id and parent id.
680 * If the supply subinv and locator in the BOM is not provided, then it will try
681 * to default those the rule: BOM level --> item level --> wip parameter
682 */
683 procedure explodeRequirementsToMTI(p_txnHeaderID in number,
684 p_parentID in number,
685 p_txnTypeID in number,
686 p_assyID in number,
687 p_orgID in number,
688 p_qty in number,
689 p_altBomDesig in varchar2,
690 p_altOption in number,
691 /* Fix for bug#3423629 */ p_bomRevDate in date default NULL,
692 p_txnDate in date,
693 p_projectID in number,
694 p_taskID in number,
695 p_toOpSeqNum in number,
696 p_altRoutDesig in varchar2,
697 p_txnMode in number,
698 p_lockFlag in number := null,
699 p_txnSourceID in number := null,
700 p_acctPeriodID in number := null,
701 p_cplTxnID in number := null,
702 p_txnBatchID in number := null,
703 p_txnBatchSeq in number := null,
704 /* Fix for bug#5262858 */ p_defaultPushSubinv in varchar2 default null,
705 x_returnStatus out NOCOPY varchar2,
706 /* Fix for bug 5630078 */ x_nontxn_excluded out NOCOPY varchar2) is
707 l_compTbl system.wip_component_tbl_t;
708 l_count number;
709 l_childTxnTypeID number;
710 l_childTxnActionID number;
711 l_insertPhantom number := WIP_CONSTANTS.NO;
712
713 l_insert varchar2(1) ; /*BUG 6134576*/
714 l_service_item_flag varchar2(1) ; /*BUG 6134576*/
715
716 l_params wip_logger.param_tbl_t;
717 l_returnStatus varchar2(1);
718 l_logLevel NUMBER := to_number(fnd_log.g_current_runtime_level);
719 l_errMsg VARCHAR2(2000);
720
721 cursor wfs_info_cursor(wipEntityId number) is
722 select wip_entity_id,
723 planned_quantity,
724 nvl(quantity_completed,0) as quantity_completed,
725 nvl(quantity_scrapped,0) as quantity_scrapped,
726 (planned_quantity - nvl(quantity_completed,0) - nvl(quantity_scrapped,0)) as open_quantity
727 from wip_flow_schedules wfs
728 where wfs.wip_entity_id = wipEntityId
729 ;
730
731 cursor wip_entity_id_cursor(txn_header_id number) is
732 select transaction_source_id
733 from mtl_transactions_interface
734 where transaction_header_id = txn_header_id
735 and rownum < 2
736 ;
737
738 l_wfs_info wfs_info_cursor%ROWTYPE := null;
739 l_wip_entity_id number := null; -- use it to retrieve wip_flow_schedules info
740 begin
741
742 if (l_logLevel <= wip_constants.trace_logging) then
743 l_params(1).paramName := 'p_txnHeaderID';
744 l_params(1).paramValue := p_txnHeaderID;
745 l_params(2).paramName := 'p_parentID';
746 l_params(2).paramValue := p_parentID;
747 l_params(3).paramName := 'p_txnTypeID';
748 l_params(3).paramValue := p_txnTypeID;
749 l_params(4).paramName := 'p_assyID';
750 l_params(4).paramValue := p_assyID;
751 l_params(5).paramName := 'p_orgID';
752 l_params(5).paramValue := p_orgID;
753 l_params(6).paramName := 'p_qty';
754 l_params(6).paramValue := p_qty;
755 l_params(7).paramName := 'p_altBomDesig';
756 l_params(7).paramValue := p_altBomDesig;
757 l_params(8).paramName := 'p_altOption';
758 l_params(8).paramValue := p_altOption;
759 l_params(9).paramName := 'p_txnDate';
760 l_params(9).paramValue := p_txnDate;
761 l_params(10).paramName := 'p_projectID';
762 l_params(10).paramValue := p_projectID;
763 l_params(11).paramName := 'p_taskID';
764 l_params(11).paramValue := p_taskID;
765 l_params(12).paramName := 'p_toOpSeqNum';
766 l_params(12).paramValue := p_toOpSeqNum;
767 l_params(13).paramName := 'p_altRoutDesig';
768 l_params(13).paramValue := p_altRoutDesig;
769 l_params(14).paramName := 'p_txnMode';
770 l_params(14).paramValue := p_txnMode;
771 l_params(15).paramName := 'p_lockFlag';
772 l_params(15).paramValue := p_lockFlag;
773 l_params(16).paramName := 'p_txnSourceID';
774 l_params(16).paramValue := p_txnSourceID;
775 l_params(17).paramName := 'p_acctPeriodID';
776 l_params(17).paramValue := p_acctPeriodID;
777 l_params(18).paramName := 'p_cplTxnID';
778 l_params(18).paramValue := p_cplTxnID;
779 l_params(19).paramName := 'p_txnBatchID';
780 l_params(19).paramValue := p_txnBatchID;
781 l_params(20).paramName := 'p_txnBatchSeq';
782 l_params(20).paramValue := p_txnBatchSeq;
783 l_params(20).paramName := 'p_defaultPushSubinv';
784 l_params(20).paramValue := p_defaultPushSubinv;
785 wip_logger.entryPoint(p_procName => 'wip_flowutil_priv.explodeRequirementsToMTI',
786 p_params => l_params,
787 x_returnStatus => x_returnStatus);
788 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
789 raise fnd_api.g_exc_unexpected_error;
790 end if;
791 end if;
792
793 explodeRequirementsAndDefault(p_assyID => p_assyID,
794 p_orgID => p_orgID,
795 p_qty => p_qty,
796 p_altBomDesig => p_altBomDesig,
797 p_altOption => p_altOption,
798 p_bomRevDate => p_bomRevDate, /* Fix for bug#3423629 */
799 p_txnDate => p_txnDate,
800 p_implFlag => 1,
801 p_projectID => p_projectID,
802 p_taskID => p_taskID,
803 p_toOpSeqNum => p_toOpSeqNum,
804 p_altRoutDesig => p_altRoutDesig,
805 p_txnFlag => true, -- p_txnFlag, for bug 4538135 /* ER 4369064 */
806 p_defaultPushSubinv => p_defaultPushSubinv, --bug#5262858
807 x_compTbl => l_compTbl,
808 x_returnStatus => x_returnStatus);
809 if ( x_returnStatus <> fnd_api.g_ret_sts_success ) then
810 raise fnd_api.g_exc_unexpected_error;
811 end if;
812
813 l_insertPhantom := wip_globals.use_phantom_routings(p_orgID);
814
815 -- bug 5605598
816 -- p_txnSourceID could be null if it's processing records manually inserted into interface table
817 l_wip_entity_id := p_txnSourceID;
818 if (l_wip_entity_id is null) then
819 for c_wip_entity_id in wip_entity_id_cursor(p_txnHeaderID) loop
820 l_wip_entity_id := c_wip_entity_id.transaction_source_id;
821 end loop;
822 end if;
823 -- fetch flow schedule's information into wfs_info
824 for c_wfs_info in wfs_info_cursor(l_wip_entity_id) loop
825 l_wfs_info := c_wfs_info;
826 end loop;
827
828 l_count := l_compTbl.first;
829 while ( l_count is not null ) loop
830 /* Fix for #6134576 If the item is a non-transactable, service-item, no backflushing
831 will take place for them. No records are inserted in MTI for service items */
832
833 l_insert := 'Y' ;
834 l_service_item_flag := 'N' ;
835
836 select service_item_flag
837 into l_service_item_flag
838 from mtl_system_items
839 where inventory_item_id = l_compTbl(l_count).inventory_item_id
840 and organization_id = p_orgid ;
841
842 -- bug 5630078
843 -- we dont insert any component that is not transaction_enabled
844 if ((nvl(l_compTbl(l_count).wip_supply_type, -1) <> 6) and
845 (l_compTbl(l_count).mtl_transactions_enabled_flag <> 'Y') and
846 (l_service_item_flag = 'Y') ) then
847 x_nontxn_excluded := 'Y';
848 l_insert := 'N' ; /* 6134576 */
849 /* 6134576. Removed following goto and control it by
850 l_insert in following if statement
851 */
852 /* goto MtiInsertLoop; */
853
854 end if;
855
856 -- bug 5605598: filter out lot-based components appropriately
857 if (nvl(l_compTbl(l_count).basis_type,WIP_CONSTANTS.ITEM_BASED_MTL) = WIP_CONSTANTS.LOT_BASED_MTL) then
858 if (
859 not(
860 (l_wfs_info.quantity_completed = 0 and l_wfs_info.quantity_scrapped <= 0 and p_qty > 0) or
861 (l_wfs_info.quantity_completed + l_wfs_info.quantity_scrapped > 0 and
862 l_wfs_info.quantity_completed + l_wfs_info.quantity_scrapped + p_qty <= 0)
863 )
864 ) then
865 -- skip the component it it's not the 1st complete/scrap or the last return/return-from-scrap
866 goto MtiInsertLoop;
867 end if;
868 end if;
869
870 -- we don't insert phantom comp(for phantom routing resource charging) into mti if
871 -- the bom parameter is set to NO.
872 if (( l_insertPhantom = WIP_CONSTANTS.YES or
873 nvl(l_compTbl(l_count).wip_supply_type, 1) <> 6) and l_insert = 'Y' ) then /*Bug 6134576*/
874 -- derive the txn type and action id
875 l_childTxnActionID := l_compTbl(l_count).transaction_action_id;
876 l_childTxnTypeID := getTypeFromAction(l_childTxnActionID);
877
878 -- if it is phantom, we insert it with negative op seq num
879 insert into mtl_transactions_interface(
880 transaction_header_id,
881 transaction_interface_id,
882 transaction_mode,
883 parent_id,
884 source_code,
885 source_line_id,
886 source_header_id,
887 inventory_item_id,
888 revision,
889 organization_id,
890 transaction_source_id,
891 operation_seq_num,
892 last_update_date,
893 last_updated_by,
894 creation_date,
895 created_by,
896 last_update_login,
897 request_id,
898 program_application_id,
899 program_id,
900 program_update_date,
901 process_flag,
902 lock_flag,
903 validation_required,
904 transaction_date,
905 transaction_quantity,
906 transaction_uom,
907 primary_quantity,
908 transaction_source_type_id,
909 flow_schedule,
910 transaction_action_id,
911 transaction_type_id,
912 wip_supply_type,
913 wip_entity_type,
914 subinventory_code,
915 locator_id,
916 acct_period_id,
917 completion_transaction_id,
918 transaction_batch_id,
919 transaction_batch_seq,
920 project_id,
921 task_id,
922 source_project_id,
923 source_task_id)
924 values
925 (p_txnHeaderID,
926 mtl_material_transactions_s.nextval,
927 p_txnMode,
928 p_parentID,
929 'Backflush',
930 1,
931 1,
932 l_compTbl(l_count).inventory_item_id,
933 l_compTbl(l_count).revision,
934 p_orgID,
935 p_txnSourceID,
936 decode(l_compTbl(l_count).wip_supply_type,
937 6, -1*l_compTbl(l_count).operation_seq_num,
938 l_compTbl(l_count).operation_seq_num),
939 sysdate,
940 fnd_global.user_id,
941 sysdate,
942 fnd_global.user_id,
943 fnd_global.login_id,
944 fnd_global.conc_request_id,
945 fnd_global.prog_appl_id,
946 fnd_global.conc_program_id,
947 sysdate,
948 1, -- process flag
949 p_lockFlag,
950 1, -- validation required
951 p_txnDate,
952 ROUND(l_compTbl(l_count).primary_quantity * -1, WIP_CONSTANTS.INV_MAX_PRECISION),
953 l_compTbl(l_count).primary_uom_code,
954 ROUND(l_compTbl(l_count).primary_quantity * -1, WIP_CONSTANTS.INV_MAX_PRECISION),
955 5,
956 'Y',
957 l_childTxnActionID,
958 l_childTxnTypeID,
959 l_compTbl(l_count).wip_supply_type,
960 wip_constants.flow,
961 l_compTbl(l_count).supply_subinventory,
962 l_compTbl(l_count).supply_locator_id,
963 p_acctPeriodID,
964 p_cplTxnID,
965 p_txnBatchID,
966 p_txnBatchSeq,
967 l_compTbl(l_count).project_id,
968 l_compTbl(l_count).task_id,
969 p_projectID,
970 p_taskID);
971
972 if (l_logLevel <= wip_constants.full_logging) then
973 wip_logger.log(p_msg => 'Insert item ' || l_compTbl(l_count).inventory_item_id ||
974 ' under op ' || l_compTbl(l_count).operation_seq_num,
975 x_returnStatus => l_returnStatus);
976 end if;
977 end if;
978
979 <<MtiInsertLoop>>
980 l_count := l_compTbl.next(l_count);
981 end loop;
982
983 if (l_logLevel <= wip_constants.trace_logging) then
984 wip_logger.exitPoint(p_procName => 'wip_flowutil_priv.explodeRequirementsToMTI',
985 p_procReturnStatus => x_returnStatus,
986 p_msg => 'Explode BOM to MTI successfully for interface ' ||
987 to_char(p_parentID) || ' successfully!',
988 x_returnStatus => l_returnStatus); --discard logging return status
989 end if;
990 exception
991 when fnd_api.g_exc_unexpected_error then
992 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
993
994 wip_utilities.get_message_stack(p_msg => l_errMsg,
995 p_delete_stack => fnd_api.g_false);
996
997 if (l_logLevel <= wip_constants.trace_logging) then
998 wip_logger.exitPoint(p_procName => 'wip_flowutil_priv.explodeRequirementsToMTI',
999 p_procReturnStatus => x_returnStatus,
1000 p_msg => 'explosion error: ' || l_errMsg || ' for exploding interface '
1001 || to_char(p_parentID),
1002 x_returnStatus => l_returnStatus); --discard logging return status
1003 end if;
1004
1005 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
1006 fnd_message.set_token('MESSAGE', l_errMsg);
1007 wip_mti_pub.setMtiError(p_parentID,
1008 null,
1009 substrb(fnd_message.get, 1, 240));
1010
1011 when others then
1012 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
1013 if (l_logLevel <= wip_constants.trace_logging) then
1014
1015 fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_flowutil_priv',
1016 p_procedure_name => 'explodeRequirementsToMTI',
1017 p_error_text => SQLERRM);
1018
1019 wip_logger.exitPoint(p_procName => 'wip_flowutil_priv.explodeRequirementsToMTI',
1020 p_procReturnStatus => x_returnStatus,
1021 p_msg => 'unexpected error: ' || l_errMsg || ' for exploding interface '
1022 || to_char(p_parentID),
1023 x_returnStatus => l_returnStatus); --discard logging return status
1024 end if;
1025 fnd_message.set_name('WIP', 'WIP_ERROR_FLOW_BKFLUSH');
1026 fnd_message.set_token('ENTITY1', p_parentID);
1027 wip_mti_pub.setMtiError(p_parentID,
1028 null,
1029 substrb(fnd_message.get, 1, 240));
1030 end explodeRequirementsToMTI;
1031
1032
1033 /**
1034 * This function merge the substitution records to backflush records. It also sets
1035 * transaction action id, etc. to the right value.
1036 */
1037 procedure mergeComponents(p_parentID in number,
1038 x_returnStatus out NOCOPY varchar2) is
1039 cursor subs_c is
1040 select transaction_interface_id,
1041 substitution_type_id,
1042 operation_seq_num,
1043 organization_id,
1044 inventory_item_id,
1045 substitution_item_id,
1046 transaction_uom,
1047 subinventory_code,
1048 locator_id
1049 from mtl_transactions_interface
1050 where parent_id = p_parentID
1051 and process_flag = 1
1052 and substitution_type_id is not null
1053 order by substitution_type_id;
1054
1055 cursor bf_c(p_opSeq number,
1056 p_orgID number,
1057 p_itemID number) is
1058 select transaction_interface_id
1059 from mtl_transactions_interface
1060 where parent_id = p_parentID
1061 and process_flag = 1
1062 and substitution_type_id is null
1063 and operation_seq_num = p_opSeq
1064 and organization_id = p_orgID
1065 and inventory_item_id = p_itemID;
1066
1067 l_bfInterfaceID number;
1068 l_primaryUOM varchar2(3);
1069
1070 l_txnActionID number;
1071 l_srcProjID number := null;
1072 l_srcTaskID number := null;
1073 l_txnDate date;
1074 l_wipEntityID number := null;
1075
1076 merge_exception exception;
1077 begin
1078 x_returnStatus := fnd_api.g_ret_sts_success;
1079
1080 for subs_rec in subs_c loop
1081 if ( subs_rec.substitution_type_id = 1 ) then
1082 ------------- Replacement ----------------------
1083 -- 1. Op seq and item exists, replace the item
1084 -- 2. Op seq doesn't exist or item doesn't exist, error out
1085 ------------------------------------------------
1086 open bf_c(subs_rec.operation_seq_num,
1087 subs_rec.organization_id,
1088 subs_rec.inventory_item_id);
1089 fetch bf_c into l_bfInterfaceID;
1090
1091 if ( bf_c%FOUND) then
1092 -- if found, then delete the bf record and set substitute record right
1093 delete from mtl_transactions_interface
1094 where transaction_interface_id = l_bfInterfaceID;
1095
1096 -- we don't need to worry transaction qty, uom, type, etc. since those
1097 -- are not null columns and don't have to be the same as the backflush record
1098 -- we also don't need to worry about lot/serial since those info should go
1099 -- with the substitution record
1100 update mtl_transactions_interface
1101 set inventory_item_id = subs_rec.substitution_item_id,
1102 substitution_item_id = null,
1103 substitution_type_id = null
1104 where transaction_interface_id = subs_rec.transaction_interface_id;
1105
1106 close bf_c;
1107 else
1108 close bf_c;
1109 fnd_message.set_name('WIP', 'WIP_ERROR_MERGE_REPLACE');
1110 fnd_message.set_token('ENTITY1', to_char(subs_rec.operation_seq_num));
1111 raise merge_exception;
1112 end if;
1113
1114 elsif ( subs_rec.substitution_type_id = 2 ) then
1115 --------------- Delete --------------------------
1116 -- 1. Op seq and item exist, delete the item
1117 -- 2. Op seq doesn't exist or item doesn't exist, error out
1118 -------------------------------------------------
1119 open bf_c(subs_rec.operation_seq_num,
1120 subs_rec.organization_id,
1121 subs_rec.inventory_item_id);
1122 fetch bf_c into l_bfInterfaceID;
1123
1124 if ( bf_c%FOUND) then
1125 -- if found, then delete the bf record
1126 delete from mtl_transactions_interface
1127 where transaction_interface_id = l_bfInterfaceID;
1128
1129 -- delete the substitution record as well
1130 delete from mtl_transactions_interface
1131 where transaction_interface_id = subs_rec.transaction_interface_id;
1132
1133 close bf_c;
1134 else
1135 close bf_c;
1136 fnd_message.set_name('WIP', 'WIP_ERROR_MERGE_DELETE');
1137 fnd_message.set_token('ENTITY1', to_char(subs_rec.operation_seq_num));
1138 raise merge_exception;
1139 end if;
1140
1141 elsif ( subs_rec.substitution_type_id = 3 ) then
1142 ----------------- Add --------------------------------
1143 -- Op seq exist, then add it
1144 -- If not exist, we will just let go trough, as decided on the meeting.
1145 -- We don't merge the additions into one transaction. They have to be in
1146 -- the same UOM, transaction action, etc. It's decided to have it as 2
1147 -- separate txns(decided by jgu and dsoosai).
1148 update mtl_transactions_interface
1149 set substitution_type_id = null,
1150 inventory_item_id = subs_rec.substitution_item_id,
1151 substitution_item_id = null
1152 where transaction_interface_id = subs_rec.transaction_interface_id;
1153
1154 elsif ( subs_rec.substitution_type_id = 4 ) then
1155 --------------- Lot/Serial -----------------------------
1156 -- 1. Op seq and item exist, replace the lot/serial association
1157 -- 2. Op seq doesn't exist or item doesn't exist, error out
1158 -- We will error it out if the substitution lot serial info is not in the
1159 -- primary UOM.
1160 ---------------------------------------------------------
1161 if ( wip_common.is_primary_uom(
1162 p_item_id => subs_rec.inventory_item_id,
1163 p_org_id => subs_rec.organization_id,
1164 p_txn_uom => subs_rec.transaction_uom,
1165 p_pri_uom => l_primaryUOM ) = 1 ) then
1166
1167 open bf_c(subs_rec.operation_seq_num,
1168 subs_rec.organization_id,
1169 subs_rec.inventory_item_id);
1170 fetch bf_c into l_bfInterfaceID;
1171
1172 if ( bf_c%FOUND) then
1173 -- if found, then delete the substitution record
1174 delete from mtl_transactions_interface
1175 where transaction_interface_id = subs_rec.transaction_interface_id;
1176
1177 -- build the link and update the subinventory and locator id
1178 update mtl_transactions_interface
1179 set transaction_interface_id = subs_rec.transaction_interface_id,
1180 subinventory_code = subs_rec.subinventory_code,
1181 locator_id = subs_rec.locator_id
1182 where transaction_interface_id = l_bfInterfaceID;
1183
1184 close bf_c;
1185 else
1186 close bf_c;
1187 fnd_message.set_name('WIP', 'WIP_ERROR_MERGE_LOT_SERIAL');
1188 fnd_message.set_token('ENTITY1', to_char(subs_rec.operation_seq_num));
1189 raise merge_exception;
1190 end if; -- end of bf_c%FOUND
1191
1192 else
1193 fnd_message.set_name('WIP', 'WIP_ERROR_MERGE_LOT_UOM');
1194 fnd_message.set_token('ENTITY1', to_char(subs_rec.operation_seq_num));
1195 fnd_message.set_token('ENTITY2', subs_rec.transaction_uom);
1196 fnd_message.set_token('ENTITYY3', l_primaryUOM);
1197 raise merge_exception;
1198 end if;
1199
1200 end if;
1201 end loop;
1202
1203 -- update the txn date, etc.
1204 select transaction_source_id,
1205 transaction_date
1206 into l_wipEntityID,
1207 l_txnDate
1208 from mtl_transactions_interface
1209 where transaction_interface_id = p_parentID;
1210
1211 if ( l_wipEntityID is not null ) then
1212 select project_id, task_id
1213 into l_srcProjID, l_srcTaskID
1214 from wip_flow_schedules
1215 where wip_entity_id = l_wipEntityID;
1216 end if;
1217
1218
1219 update mtl_transactions_interface
1220 set transaction_source_type_id = nvl(transaction_source_type_id, 5),
1221 flow_schedule = nvl(flow_schedule, 'Y'),
1222 source_project_id = l_srcProjID,
1223 source_task_id = l_srcTaskID,
1224 transaction_source_id = l_wipEntityID,
1225 wip_entity_type = decode(l_wipEntityID, null, null, 4),
1226 transaction_date = to_date(to_char(l_txnDate, WIP_CONSTANTS.DT_NOSEC_FMT), WIP_CONSTANTS.DT_NOSEC_FMT)
1227 where parent_id = p_parentID
1228 and process_flag = 1
1229 and substitution_type_id is null;
1230
1231 exception
1232 when merge_exception then
1233 x_returnStatus := fnd_api.g_ret_sts_error;
1234 wip_mti_pub.setMtiError(p_parentID,
1235 null,
1236 substrb(fnd_message.get, 1, 240));
1237 when others then
1238 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
1239 wip_mti_pub.setMtiError(p_parentID,
1240 null,
1241 substrb('Error in merging: ' || SQLERRM, 1, 240));
1242 end mergeComponents;
1243
1244
1245 /**
1246 * Generate the issue locator for all the issues associated with a completion.
1247 * This is only applicable to a project related completions.
1248 * ????
1249 * Need to talk to PJM team to remove wip_entity_id from
1250 * PJM_Project_Locator.Get_Flow_ProjectSupply or provide an equivalent.
1251 */
1252 procedure generateIssueLocator(p_parentID in number,
1253 x_returnStatus out NOCOPY varchar2) is
1254 l_orgID number;
1255 l_wipEntityID number := null;
1256 l_srcProjID number := null;
1257 l_srcTaskID number := null;
1258 l_success number;
1259 begin
1260 x_returnStatus := fnd_api.g_ret_sts_success;
1261 null;
1262 end generateIssueLocator;
1263
1264 /**
1265 * This procedure creates an entry in wip_flow_schedules and wip_entities for
1266 * unscheduled work orderless completion. Those entries are needed for the
1267 * following resource and material transactions.
1268 */
1269 procedure createFlowSchedule(p_txnInterfaceID in number := null,
1270 p_txnTmpID in number := null,
1271 x_returnStatus out nocopy varchar2,
1272 x_wipEntityID out nocopy number) is
1273
1274 type flow_rec_t is record(transaction_action_id NUMBER, -- CFM Scrap
1275 last_update_date DATE,
1276 last_updated_by NUMBER,
1277 creation_date DATE,
1278 created_by NUMBER,
1279 last_update_login NUMBER,
1280 request_id NUMBER,
1281 program_application_id NUMBER,
1282 program_id NUMBER,
1283 program_update_date DATE,
1284 organization_id NUMBER,
1285 inventory_item_id NUMBER,
1286 accounting_class VARCHAR2(10),
1287 transaction_date DATE,
1288 transaction_quantity NUMBER, -- we have to get the primary qty
1289 transaction_uom VARCHAR2(3),
1290 primary_quantity NUMBER,
1291 transaction_source_id NUMBER,
1292 transaction_source_name VARCHAR2(240),
1293 revision VARCHAR2(3),
1294 bom_revision VARCHAR2(3),
1295 routing_revision VARCHAR2(3),
1296 bom_revision_date DATE,
1297 routing_revision_date DATE,
1298 alternate_bom_designator VARCHAR2(10),
1299 alternate_routing_designator VARCHAR2(10),
1300 subinventory_code VARCHAR2(10),
1301 locator_id NUMBER,
1302 demand_class VARCHAR2(30),
1303 schedule_group NUMBER,
1304 build_sequence NUMBER,
1305 repetitive_line_id NUMBER,
1306 source_project_id NUMBER,
1307 project_id NUMBER,
1308 source_task_id NUMBER,
1309 task_id NUMBER,
1310 schedule_number VARCHAR2(30),
1311 scheduled_flag NUMBER,
1312 wip_entity_type NUMBER,
1313 end_item_unit_number VARCHAR2(60),
1314 transaction_header_id NUMBER,
1315 completion_transaction_id NUMBER,
1316 row_id rowid);
1317
1318 cursor c_MTIflowCompletion return flow_rec_t is
1319 select transaction_action_id, -- CFM Scrap
1320 last_update_date,
1321 last_updated_by,
1322 creation_date,
1323 created_by,
1324 last_update_login,
1325 request_id,
1326 program_application_id,
1327 program_id,
1328 program_update_date,
1329 organization_id,
1330 inventory_item_id,
1331 accounting_class,
1332 transaction_date,
1333 transaction_quantity, -- we have to get the primary qty
1334 transaction_uom,
1335 primary_quantity,
1336 transaction_source_id,
1337 transaction_source_name,
1338 revision,
1339 bom_revision,
1340 routing_revision,
1341 bom_revision_date,
1342 routing_revision_date,
1343 alternate_bom_designator,
1344 alternate_routing_designator,
1345 subinventory_code,
1346 locator_id,
1347 demand_class,
1348 schedule_group,
1349 build_sequence,
1350 repetitive_line_id,
1351 source_project_id,
1352 project_id,
1353 source_task_id,
1354 task_id,
1355 schedule_number,
1356 scheduled_flag,
1357 wip_entity_type,
1358 end_item_unit_number,
1359 transaction_header_id,
1360 completion_transaction_id,
1361 rowid
1362 from mtl_transactions_interface
1363 where transaction_interface_id = p_txnInterfaceID
1364 and transaction_source_type_id = 5
1365 and transaction_source_id is null
1366 and flow_schedule = 'Y'
1367 and transaction_action_id in (31, 32, 30) -- CFM Scrap
1368 and scheduled_flag = 2
1369 and process_flag = wip_constants.mti_inventory;
1370
1371 cursor c_MMTTflowCompletion return flow_rec_t is
1372 select transaction_action_id, -- CFM Scrap
1373 last_update_date,
1374 last_updated_by,
1375 creation_date,
1376 created_by,
1377 last_update_login,
1378 request_id,
1379 program_application_id,
1380 program_id,
1381 program_update_date,
1382 organization_id,
1383 inventory_item_id,
1384 class_code,
1385 transaction_date,
1386 transaction_quantity, -- we have to get the primary qty
1387 transaction_uom,
1388 primary_quantity,
1389 transaction_source_id,
1390 transaction_source_name,
1391 revision,
1392 bom_revision,
1393 routing_revision,
1394 bom_revision_date,
1395 routing_revision_date,
1396 alternate_bom_designator,
1397 alternate_routing_designator,
1398 subinventory_code,
1399 locator_id,
1400 demand_class,
1401 schedule_group,
1402 build_sequence,
1403 repetitive_line_id,
1404 source_project_id,
1405 project_id,
1406 source_task_id,
1407 task_id,
1408 schedule_number,
1409 scheduled_flag,
1410 wip_entity_type,
1411 end_item_unit_number,
1412 transaction_header_id,
1413 completion_transaction_id,
1414 rowid
1415 from mtl_material_transactions_temp
1416 where transaction_temp_id = p_txnTmpID
1417 and transaction_source_type_id = 5
1418 and transaction_source_id is null
1419 and flow_schedule = 'Y'
1420 and transaction_action_id in (31, 32, 30) -- CFM Scrap
1421 and scheduled_flag = 2
1422 and process_flag = 'Y';
1423
1424 l_primaryUOM varchar2(3);
1425 l_wipEntityID number;
1426
1427 l_materialAccount number;
1428 l_materialOverheadAccount number;
1429 l_resourceAccount number;
1430 l_outsideProcessingAccount number;
1431 l_materialVarianceAccount number;
1432 l_resourceVarianceAccount number;
1433 l_outsideProcVarAccount number;
1434 l_stdCostAdjustmentAccount number;
1435 l_overheadAccount number;
1436 l_overheadVarianceAccount number ;
1437
1438 l_params wip_logger.param_tbl_t;
1439 l_returnStatus varchar2(1);
1440 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
1441
1442 l_prjID NUMBER;
1443 l_tskID NUMBER;
1444
1445 l_flowRec flow_rec_t;
1446
1447 cursor c_MTIparams is
1448 select transaction_source_type_id,
1449 transaction_source_id,
1450 flow_schedule,
1451 transaction_action_id,
1452 scheduled_flag,
1453 process_flag
1454 from mtl_transactions_interface
1455 where transaction_interface_id = p_txnInterfaceID;
1456
1457 cursor c_MMTTparams is
1458 select transaction_source_type_id,
1459 transaction_source_id,
1460 flow_schedule,
1461 transaction_action_id,
1462 scheduled_flag,
1463 process_flag
1464 from mtl_transactions_interface
1465 where transaction_interface_id = p_txnInterfaceID;
1466
1467 begin
1468
1469 x_returnStatus := fnd_api.g_ret_sts_success;
1470
1471 if (l_logLevel <= wip_constants.trace_logging) then
1472 l_params(1).paramName := 'p_txnInterfaceID';
1473 l_params(1).paramValue := p_txnInterfaceID;
1474 wip_logger.entryPoint(p_procName => 'wip_flowUtil_priv.createFlowSchedule',
1475 p_params => l_params,
1476 x_returnStatus => x_returnStatus);
1477 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
1478 raise fnd_api.g_exc_unexpected_error;
1479 end if;
1480 end if;
1481
1482 if(l_logLevel <= wip_constants.full_logging) then
1483 declare
1484 l_count NUMBER;
1485 begin
1486 if(p_txnInterfaceID is not null) then
1487 select count(*)
1488 into l_count
1489 from mtl_transactions_interface
1490 where transaction_interface_id = p_txnInterfaceID;
1491 wip_logger.log('MTI rowcount is ' || l_count, l_returnStatus);
1492
1493 for l_paramRec in c_MTIparams loop
1494 wip_logger.log('transaction_source_id:' || l_paramRec.transaction_source_id, l_returnStatus);
1495 wip_logger.log('flow_schedule:' || l_paramRec.flow_schedule, l_returnStatus);
1496 wip_logger.log('transaction_action_id:' || l_paramRec.transaction_action_id, l_returnStatus);
1497 wip_logger.log('scheduled_flag:' || l_paramRec.scheduled_flag, l_returnStatus);
1498 wip_logger.log('process_flag:' || l_paramRec.process_flag, l_returnStatus);
1499 end loop;
1500 else
1501 select count(*)
1502 into l_count
1503 from mtl_material_transactions_temp
1504 where transaction_temp_id = p_txnTmpID;
1505 wip_logger.log('MMTT rowcount is ' || l_count, l_returnStatus);
1506
1507 for l_paramRec in c_MMTTparams loop
1508 wip_logger.log('transaction_source_id:' || l_paramRec.transaction_source_id, l_returnStatus);
1509 wip_logger.log('flow_schedule:' || l_paramRec.flow_schedule, l_returnStatus);
1510 wip_logger.log('transaction_action_id:' || l_paramRec.transaction_action_id, l_returnStatus);
1511 wip_logger.log('scheduled_flag:' || l_paramRec.scheduled_flag, l_returnStatus);
1512 wip_logger.log('process_flag:' || l_paramRec.process_flag, l_returnStatus);
1513 end loop;
1514 end if;
1515 end;
1516 end if;
1517
1518 if(p_txnInterfaceID is not null) then
1519 open c_MTIflowCompletion;
1520 fetch c_MTIflowCompletion into l_flowRec;
1521 close c_MTIflowCompletion;
1522 else
1523 open c_MMTTflowCompletion;
1524 fetch c_MMTTflowCompletion into l_flowRec;
1525 close c_MMTTflowCompletion;
1526 end if;
1527
1528 if(l_flowRec.organization_id is null) then --org id column is not null in both MTI and MMTT
1529 if(l_logLevel <= wip_constants.trace_logging) then
1530 wip_logger.exitPoint(p_procName => 'wip_flowUtil_priv.createFlowSchedule',
1531 p_procReturnStatus => x_returnStatus,
1532 p_msg => 'Flow schedule creation not necessary.',
1533 x_returnStatus => l_returnStatus); --discard logging return status
1534 end if;
1535 x_returnStatus := fnd_api.g_ret_sts_success;
1536 return;
1537 end if;
1538
1539 l_flowRec.wip_entity_type := 4 ; -- Set it to Flow Schedule
1540
1541 -- get the primary txn quantity
1542 select primary_uom_code
1543 into l_primaryUOM
1544 from mtl_system_items
1545 where inventory_item_id = l_flowRec.inventory_item_id
1546 and organization_id = l_flowRec.organization_id;
1547
1548 if( l_primaryUOM <> l_flowRec.transaction_uom ) then
1549 l_flowRec.primary_quantity :=
1550 inv_convert.inv_um_convert(
1551 item_id => l_flowRec.inventory_item_id,
1552 precision => NULL,
1553 from_quantity => l_flowRec.transaction_quantity,
1554 from_unit => l_flowRec.transaction_uom,
1555 to_unit => l_primaryUOM,
1556 from_name => NULL,
1557 to_name => NULL) ;
1558 else
1559 l_flowRec.primary_quantity := l_flowRec.transaction_quantity;
1560 end if;
1561
1562 -- now getting the wip_entity_id and the account info
1563 select wip_entities_s.nextval into l_wipEntityID from dual;
1564
1565 select material_account,
1566 material_overhead_account,
1567 resource_account,
1568 outside_processing_account,
1569 material_variance_account,
1570 resource_variance_account,
1571 outside_proc_variance_account,
1572 std_cost_adjustment_account,
1573 overhead_account,
1574 overhead_variance_account
1575 into l_materialAccount,
1576 l_materialOverheadAccount,
1577 l_resourceAccount,
1578 l_outsideProcessingAccount,
1579 l_materialVarianceAccount,
1580 l_resourceVarianceAccount,
1581 l_outsideProcVarAccount,
1582 l_stdCostAdjustmentAccount,
1583 l_overheadAccount,
1584 l_overheadVarianceAccount
1585 from wip_accounting_classes
1586 where class_code = l_flowRec.accounting_class
1587 and organization_id = l_flowRec.organization_id;
1588
1589 -- we do NOT need to insert an entry in wip_entities since there is a database
1590 -- trigger on wfs to do that
1591 insert into wip_flow_schedules(
1592 wip_entity_id,
1593 organization_id,
1594 last_update_date,
1595 last_updated_by,
1596 creation_date,
1597 created_by,
1598 last_update_login,
1599 request_id,
1600 program_application_id,
1601 program_id,
1602 program_update_date,
1603 primary_item_id,
1604 class_code,
1605 scheduled_start_date,
1606 date_closed,
1607 planned_quantity,
1608 quantity_completed,
1609 quantity_scrapped,
1610 mps_scheduled_completion_date,
1611 mps_net_quantity,
1612 bom_revision,
1613 routing_revision,
1614 bom_revision_date,
1615 routing_revision_date,
1616 alternate_bom_designator,
1617 alternate_routing_designator,
1618 completion_subinventory,
1619 completion_locator_id,
1620 material_account,
1621 material_overhead_account,
1622 resource_account,
1623 outside_processing_account,
1624 material_variance_account,
1625 resource_variance_account,
1626 outside_proc_variance_account,
1627 std_cost_adjustment_account,
1628 overhead_account,
1629 overhead_variance_account,
1630 demand_class,
1631 scheduled_completion_date,
1632 schedule_group_id,
1633 build_sequence,
1634 line_id,
1635 project_id,
1636 task_id,
1637 status,
1638 schedule_number,
1639 scheduled_flag,
1640 end_item_unit_number,
1641 attribute_category,
1642 attribute1,
1643 attribute2,
1644 attribute3,
1645 attribute4,
1646 attribute5,
1647 attribute6,
1648 attribute7,
1649 attribute8,
1650 attribute9,
1651 attribute10,
1652 attribute11,
1653 attribute12,
1654 attribute13,
1655 attribute14,
1656 attribute15)
1657 values(
1658 l_wipEntityID,
1659 l_flowRec.organization_id,
1660 l_flowRec.last_update_date,
1661 l_flowRec.last_updated_by,
1662 l_flowRec.creation_date,
1663 l_flowRec.created_by,
1664 l_flowRec.last_update_login,
1665 l_flowRec.request_id,
1666 l_flowRec.program_application_id,
1667 l_flowRec.program_id,
1668 l_flowRec.program_update_date,
1669 l_flowRec.inventory_item_id,
1670 l_flowRec.accounting_class,
1671 l_flowRec.transaction_date,
1672 NULL,
1673 0,
1674 0,
1675 0,
1676 NULL,
1677 NULL,
1678 l_flowRec.bom_revision,
1679 l_flowRec.routing_revision,
1680 l_flowRec.bom_revision_date,
1681 l_flowRec.routing_revision_date,
1682 l_flowRec.alternate_bom_designator,
1683 l_flowRec.alternate_routing_designator,
1684 l_flowRec.subinventory_code,
1685 l_flowRec.locator_id,
1686 l_materialAccount,
1687 l_materialOverheadAccount,
1688 l_resourceAccount,
1689 l_outsideProcessingAccount,
1690 l_materialVarianceAccount,
1691 l_resourceVarianceAccount,
1692 l_outsideProcVarAccount,
1693 l_stdCostAdjustmentAccount,
1694 l_overheadAccount,
1695 l_overheadVarianceAccount,
1696 l_flowRec.demand_class,
1697 l_flowRec.transaction_date,
1698 l_flowRec.schedule_group,
1699 l_flowRec.build_sequence,
1700 l_flowRec.repetitive_line_id,
1701 --technically, the user should populate the source prj/tsk columns, but also
1702 --accept prj/tsk columns
1703 nvl(l_flowRec.source_project_id, l_flowRec.project_id),
1704 decode(l_flowRec.source_project_id, null, l_flowRec.task_id, l_flowRec.source_task_id),
1705 1, -- 1. Open, 2. Close
1706 l_flowRec.schedule_number,
1707 2, -- Unscheduled
1708 l_flowRec.end_item_unit_number, -- end item unit number
1709 NULL,
1710 NULL, NULL, NULL, NULL, NULL,
1711 NULL, NULL, NULL, NULL, NULL,
1712 NULL, NULL, NULL, NULL, NULL
1713 ) returning project_id, task_id into l_prjID, l_tskID;
1714
1715 x_wipEntityID := l_wipEntityID;
1716
1717 if (l_logLevel <= wip_constants.full_logging) then
1718 wip_logger.log(p_msg => 'wip entity id: ' || x_wipEntityID,
1719 x_returnStatus => l_returnStatus);
1720 wip_logger.log(p_msg => 'src prjID: ' || l_flowRec.source_project_id,
1721 x_returnStatus => l_returnStatus);
1722 wip_logger.log(p_msg => 'projID: ' || l_flowRec.project_id,
1723 x_returnStatus => l_returnStatus);
1724 wip_logger.log(p_msg => 'prjID: ' || l_prjID,
1725 x_returnStatus => l_returnStatus);
1726 wip_logger.log(p_msg => 'tskID:' || l_tskID,
1727 x_returnStatus => l_returnStatus);
1728 wip_logger.log(p_msg => 'Flow schedule ' || l_wipEntityID || ' was created!',
1729 x_returnStatus => l_returnStatus);
1730 end if;
1731
1732 -- set the transaction_source_id for the assembly record (if from MTI)
1733 --and its components
1734 update mtl_transactions_interface
1735 set last_update_date = sysdate,
1736 last_updated_by = fnd_global.user_id,
1737 last_update_login = fnd_global.login_id,
1738 program_application_id = fnd_global.prog_appl_id,
1739 program_id = fnd_global.conc_program_id,
1740 program_update_date = sysdate,
1741 request_id = fnd_global.conc_request_id,
1742 transaction_source_id = l_wipEntityID,
1743 wip_entity_type = l_flowRec.wip_entity_type
1744 where transaction_header_id = l_flowRec.transaction_header_id
1745 and completion_transaction_id = l_flowRec.completion_transaction_id;
1746
1747 if (l_logLevel <= wip_constants.full_logging) then
1748 wip_logger.log(p_msg => SQL%ROWCOUNT || 'MTI rows updated!',
1749 x_returnStatus => l_returnStatus);
1750 end if;
1751
1752 --if txn originated from MMTT, update the assy record
1753 if(p_txnTmpID is not null) then
1754 update mtl_material_transactions_temp
1755 set last_update_date = sysdate,
1756 last_updated_by = fnd_global.user_id,
1757 last_update_login = fnd_global.login_id,
1758 program_application_id = fnd_global.prog_appl_id,
1759 program_id = fnd_global.conc_program_id,
1760 program_update_date = sysdate,
1761 request_id = fnd_global.conc_request_id,
1762 transaction_source_id = l_wipEntityID,
1763 wip_entity_type = l_flowRec.wip_entity_type
1764 where transaction_temp_id = p_txnTmpID;
1765 end if;
1766
1767 if (l_logLevel <= wip_constants.full_logging) then
1768 wip_logger.log(p_msg => SQL%ROWCOUNT || 'MMTT rows updated!',
1769 x_returnStatus => l_returnStatus);
1770 end if;
1771
1772 if (l_logLevel <= wip_constants.trace_logging) then
1773 wip_logger.exitPoint(p_procName => 'wip_flowUtil_priv.createFlowSchedule',
1774 p_procReturnStatus => x_returnStatus,
1775 p_msg => 'Flow schedules created successfully!',
1776 x_returnStatus => l_returnStatus); --discard logging return status
1777 end if;
1778 exception
1779 when others then
1780 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
1781 if (l_logLevel <= wip_constants.trace_logging) then
1782 wip_logger.exitPoint(p_procName => 'wip_flowUtil_priv.createFlowSchedule',
1783 p_procReturnStatus => x_returnStatus,
1784 p_msg => 'unexpected error: ' || SQLERRM,
1785 x_returnStatus => l_returnStatus); --discard logging return status
1786 end if;
1787
1788 fnd_message.set_name('WIP', 'WIP_ERROR_FLOW_CREATION');
1789 fnd_message.set_token('ENTITY1',to_char(p_txnInterfaceID));
1790 fnd_msg_pub.add;
1791 end createFlowSchedule;
1792
1793
1794 /**
1795 * This procedure performs the update to wip flow schedule.
1796 */
1797 procedure updateFlowSchedule(p_txnTempID in number,
1798 x_returnStatus out nocopy varchar2) is
1799 l_wipEntityID number;
1800 l_transactionDate date;
1801 l_cplQty number;
1802 l_scrapQty number;
1803 l_flowSchedule varchar(1) := 'N';
1804 l_statusChange number := 0;
1805 l_completedQty number;
1806 l_plannedQty number;
1807 l_newCompletedQty number;
1808
1809 l_params wip_logger.param_tbl_t;
1810 l_returnStatus varchar2(1);
1811 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
1812 begin
1813
1814 if (l_logLevel <= wip_constants.trace_logging) then
1815 l_params(1).paramName := 'p_txnTempID';
1816 l_params(1).paramValue := p_txnTempID;
1817 wip_logger.entryPoint(p_procName => 'wip_flowUtil_priv.updateFlowSchedule',
1818 p_params => l_params,
1819 x_returnStatus => x_returnStatus);
1820 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
1821 raise fnd_api.g_exc_unexpected_error;
1822 end if;
1823 end if;
1824
1825 /*
1826 select distinct
1827 transaction_source_id,
1828 decode(transaction_action_id, 30, 0, primary_quantity),
1829 decode(transaction_action_id, 30, primary_quantity, 0),-- CFM Scrap
1830 transaction_date,
1831 flow_schedule
1832 into l_wipEntityID,
1833 l_cplQty,
1834 l_scrapQty,
1835 l_transactionDate,
1836 l_flowSchedule
1837 from mtl_material_transactions
1838 where transaction_set_id = p_txnHeaderID
1839 and transaction_action_id in (30, 31, 32);
1840 */
1841
1842 select transaction_source_id,
1843 decode(transaction_action_id, 30, 0, primary_quantity),
1844 decode(transaction_action_id, 30, primary_quantity, 0),-- CFM Scrap
1845 transaction_date,
1846 flow_schedule
1847 into l_wipEntityID,
1848 l_cplQty,
1849 l_scrapQty,
1850 l_transactionDate,
1851 l_flowSchedule
1852 from mtl_material_transactions_temp
1853 where transaction_temp_id = p_txnTempID;
1854
1855 select planned_quantity,
1856 quantity_completed
1857 into l_plannedQty,
1858 l_completedQty
1859 from wip_flow_schedules
1860 where wip_entity_id = l_wipEntityID;
1861
1862 -- status change
1863 -- 0: no change, 1: reopen the schedule, 2: close the schedule
1864 l_statusChange := 0;
1865 l_newCompletedQty := l_completedQty + l_cplQty;
1866
1867 if ( l_newCompletedQty >= l_plannedQty ) then
1868 if ( l_completedQty <= l_plannedQty ) then
1869 l_statusChange := 2;
1870 end if;
1871 else
1872 if ( l_completedQty >= l_plannedQty ) then
1873 l_statusChange := 1;
1874 end if;
1875 end if;
1876
1877 update wip_flow_schedules
1878 set quantity_completed = nvl(quantity_completed, 0) + l_cplQty,
1879 quantity_scrapped = nvl(quantity_scrapped, 0) + l_scrapQty,
1880 transacted_flag = 'Y',
1881 date_closed = decode(upper(nvl(l_flowSchedule, 'N')),
1882 'Y',
1883 decode(l_statusChange, 0, date_closed,
1884 1, null,
1885 2, l_transactionDate),
1886 date_closed),
1887 status = decode(upper(nvl(l_flowSchedule, 'N')),
1888 'Y',
1889 decode(l_statusChange, 0, status,
1890 1, 1,
1891 2, 2),
1892 status)
1893 where wip_entity_id = l_wipEntityID;
1894
1895 if (l_logLevel <= wip_constants.trace_logging) then
1896 wip_logger.exitPoint(p_procName => 'wip_flowUtil_priv.updateFlowSchedule',
1897 p_procReturnStatus => x_returnStatus,
1898 p_msg => 'Flow schedules updated successfully!',
1899 x_returnStatus => l_returnStatus); --discard logging return status
1900 end if;
1901 exception
1902 when others then
1903 x_returnStatus := fnd_api.g_ret_sts_error;
1904 if (l_logLevel <= wip_constants.trace_logging) then
1905 wip_logger.exitPoint(p_procName => 'wip_flowUtil_priv.updateFlowSchedule',
1906 p_procReturnStatus => x_returnStatus,
1907 p_msg => 'unexpected error: ' || SQLERRM,
1908 x_returnStatus => l_returnStatus); --discard logging return status
1909 end if;
1910
1911 fnd_message.set_name('WIP', 'WIP_UPDATE_WFS_ERROR');
1912 fnd_msg_pub.add;
1913 end updateFlowSchedule;
1914
1915 /**
1916 * This procedure sets the error status to the mmtt. It sets the error
1917 * for the given temp id as well as the child records.
1918 */
1919 procedure setMmttError(p_txnTempID in number,
1920 p_msgData in varchar2) is
1921 begin
1922 update mtl_material_transactions_temp
1923 set last_update_date = sysdate,
1924 last_updated_by = fnd_global.user_id,
1925 last_update_login = fnd_global.login_id,
1926 program_application_id = fnd_global.prog_appl_id,
1927 program_id = fnd_global.conc_program_id,
1928 program_update_date = sysdate,
1929 request_id = fnd_global.conc_request_id,
1930 process_flag = 'E',
1931 lock_flag = 2,
1932 error_code = substrb(p_msgData, 1, 240),
1933 error_explanation = substrb(p_msgData, 1, 240)
1934 where transaction_temp_id = p_txnTempID
1935 or completion_transaction_id =
1936 (select completion_transaction_id
1937 from mtl_material_transactions_temp
1938 where transaction_temp_id = p_txnTempID);
1939 end setMmttError;
1940
1941
1942 /**
1943 * This procedure explodes the BOM for the given assemble and do the default of
1944 * subinventory and locator. It will find the components up to the toOpSeqNum.
1945 * If the supply subinv and locator in the BOM is not provided, then it will try
1946 * to default those the rule: BOM level --> item level --> wip parameter
1947 *
1948 * ER 4369064: This API is called from both Flow and WIP. If called from Flow, we
1949 * need to 1) Validate transaction flag for components
1950 * 2) Include / exclude component yield based on WIP Parameter
1951 * Calling program should pass 'TRUE' through the parameter p_tcnFlag if the above
1952 * two tasks are applicable, and 'FALSE' if not.
1953 */
1954 procedure explodeRequirementsAndDefault(p_assyID in number,
1955 p_orgID in number,
1956 p_qty in number,
1957 p_altBomDesig in varchar2,
1958 p_altOption in number,
1959 /* Fix for bug#3423629 */ p_bomRevDate in date default NULL,
1960 p_txnDate in date,
1961 /* Fix for bug 5383135 */ p_implFlag in number,
1962 p_projectID in number,
1963 p_taskID in number,
1964 p_toOpSeqNum in number,
1965 p_altRoutDesig in varchar2,
1966 /* Fix for bug#4538135 */ p_txnFlag in boolean default true,
1967 /* Fix for bug#5262858 */ p_defaultPushSubinv in varchar2 default null,
1968 /* added for bug 5332615 */ p_unitNumber in varchar2 DEFAULT '',
1969 x_compTbl out nocopy system.wip_component_tbl_t,
1970 x_returnStatus out nocopy varchar2) is
1971 l_numOfComp number;
1972 l_count number := 1;
1973 l_returnStatus varchar2(1);
1974
1975 l_msiSubinv varchar2(10);
1976 l_msiLocatorID number;
1977 l_wpSubinv varchar2(10);
1978 l_wpLocatorID number;
1979
1980 l_cfmRouting number;
1981 l_commonRoutSeqID number;
1982 l_checkPass boolean;
1983 l_lineOpTbl bom_rtg_network_api.op_tbl_type;
1984 l_constructed boolean := false;
1985 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
1986 l_success boolean;
1987 l_locatorID number := null;
1988
1989 l_includeYield NUMBER; /*Component Yield Enhancement(Bug 4369064)*/
1990 l_service_item_flag varchar2(1); /*Bug 6134576*/
1991 begin
1992 x_returnStatus := fnd_api.g_ret_sts_success;
1993
1994 wip_bflProc_priv.explodeRequirements(p_itemID => p_assyID,
1995 p_orgID => p_orgID,
1996 p_qty => p_qty,
1997 p_altBomDesig => p_altBomDesig,
1998 p_altOption => p_altOption,
1999 p_bomRevDate => p_bomRevDate, /* Fix for 3423629*/
2000 p_txnDate => p_txnDate,
2001 p_implFlag => p_implFlag,
2002 p_projectID => p_projectID,
2003 p_taskID => p_taskID,
2004 p_unitNumber => p_unitNumber, /* added for bug 5332615 */
2005 p_initMsgList => fnd_api.g_false,
2006 p_endDebug => fnd_api.g_true,
2007 x_compTbl => x_compTbl,
2008 x_returnStatus => x_returnStatus);
2009 if ( x_returnStatus <> fnd_api.g_ret_sts_success ) then
2010 return;
2011 end if;
2012
2013 if (l_logLevel <= wip_constants.full_logging) then
2014 wip_logger.log(p_msg => x_compTbl.count || ' components exploded!',
2015 x_returnStatus => l_returnStatus);
2016 end if;
2017
2018 -- get the routing type
2019 begin
2020 select common_routing_sequence_id,
2021 nvl(cfm_routing_flag, 2)
2022 into l_commonRoutSeqID,
2023 l_cfmRouting
2024 from bom_operational_routings
2025 where organization_id = p_orgID
2026 and assembly_item_id = p_assyID
2027 and nvl(alternate_routing_designator, 'NONE') =
2028 nvl(p_altRoutDesig, 'NONE');
2029 exception
2030 when others then
2031 l_cfmRouting := -2;
2032 end;
2033
2034 -- ER 4369064: Get the 'Include yield' setting from WIP Parameters
2035 if (p_txnFlag) then
2036 select nvl(include_component_yield,1)
2037 into l_includeYield
2038 from wip_parameters
2039 where organization_id = p_orgID;
2040 end if;
2041
2042 l_numOfComp := x_compTbl.count;
2043 while ( l_count <= l_numOfComp ) loop
2044
2045 -- ER 4369064: For work orderless txn, we have to include yield factor
2046 -- if Include Yield in WIP Parameters is checked.
2047 -- Rounding this to 5 decimals since this will go into MTI
2048 /* Bug fix 4728358: Number of rounding decimal places should be obtained from WIP_CONSTANTS.
2049 In particular we want 6 places instead of 5. */
2050 if (p_txnFlag AND l_includeYield = 1) then
2051 x_compTbl(l_count).primary_quantity := round(x_compTbl(l_count).primary_quantity
2052 / x_compTbl(l_count).component_yield_factor, WIP_CONSTANTS.INV_MAX_PRECISION);
2053 /* End of bug fix 4728358 */
2054 end if;
2055
2056 if (round(abs(x_compTbl(l_count).primary_quantity),
2057 WIP_CONSTANTS.INV_MAX_PRECISION) = 0) then
2058 if(p_txnFlag) then
2059 if (l_logLevel <= wip_constants.full_logging) then
2060 wip_logger.log(p_msg => 'Qty too small, component ' ||
2061 x_compTbl(l_count).inventory_item_id || ' stripped!',
2062 x_returnStatus => l_returnStatus);
2063 end if;
2064 x_compTbl.delete(l_count);
2065 end if;
2066 l_checkPass := false;
2067 elsif ( l_cfmRouting = -2 ) then
2068 -- the assy doesn't have a routing, we backflush everything
2069 l_checkPass := true;
2070 else
2071 -- according to the meeting on 02/15/2002, as decided by Richard, Jung and
2072 -- Barry, even though the operation is disabled, we still should backflush
2073 -- the material needed at that operation. So here I am not checking the
2074 -- operation effectivity/disable date.
2075 if ( p_toOpSeqNum is null ) then
2076 -- we backflush all the material if terminal op seq is not provided
2077 l_checkPass := true;
2078 elsif ( l_cfmRouting <> 1 ) then
2079 if ( x_compTbl(l_count).operation_seq_num > p_toOpSeqNum ) then
2080 -- delete the comp that has op seq greater than to op seq num
2081 if (l_logLevel <= wip_constants.full_logging) then
2082 wip_logger.log(p_msg => 'Op ' || x_compTbl(l_count).operation_seq_num ||
2083 ' after terminal op, item ' ||
2084 x_compTbl(l_count).inventory_item_id || ' stripped!',
2085 x_returnStatus => l_returnStatus);
2086 end if;
2087 x_compTbl.delete(l_count);
2088 l_checkPass := false;
2089 else
2090 l_checkPass := true;
2091 end if;
2092 else
2093 -- it is cfm routing, what we get in op seq num is the event number,
2094 -- we need to check whether the line op this event belongs to is before
2095 -- or the same as the one provided or not.
2096 if ( not l_constructed ) then
2097 constructWipLineOps(p_routingSeqID => l_commonRoutSeqID,
2098 p_assyItemID => null,
2099 p_orgID => null,
2100 p_altRoutDesig => null,
2101 p_terminalOpSeqNum => p_toOpSeqNum,
2102 x_lineOpTbl => l_lineOpTbl);
2103 l_constructed := true;
2104 end if;
2105 if ( eventInPriorSameLineOp(l_commonRoutSeqID,
2106 x_compTbl(l_count).operation_seq_num,
2107 p_toOpSeqNum,
2108 l_lineOpTbl) ) then
2109 l_checkPass := true;
2110 else
2111 if (l_logLevel <= wip_constants.full_logging) then
2112 wip_logger.log(p_msg => 'Event ' || x_compTbl(l_count).operation_seq_num ||
2113 ' belongs to line op that after terminal op, item ' ||
2114 x_compTbl(l_count).inventory_item_id || ' stripped!',
2115 x_returnStatus => l_returnStatus);
2116 end if;
2117 x_compTbl.delete(l_count);
2118 l_checkPass := false;
2119 end if;
2120 end if;
2121 end if; -- end of l_cfmRouting = -2
2122
2123 -- for work orderless txn, we don't backflush 'Bulk' components. We only
2124 -- backflush operation pull, assembly pull and push components.
2125 -- for phantom, we insert them into mmtt with negative op seq num
2126 -- ER 4369064: This code is called for discrete jobs too. Bulk and Supplier
2127 -- type components should be exploded for discrete jobs.
2128 if ( l_checkPass and p_txnFlag and
2129 nvl(x_compTbl(l_count).wip_supply_type, 1) not in (1, 2, 3, 6) ) then
2130 if (l_logLevel <= wip_constants.full_logging) then
2131 wip_logger.log(p_msg => 'Nonrelated supply type item ' ||
2132 x_compTbl(l_count).inventory_item_id ||
2133 ' at op ' || x_compTbl(l_count).operation_seq_num ||
2134 ' stripped!',
2135 x_returnStatus => l_returnStatus);
2136 end if;
2137 x_compTbl.delete(l_count);
2138 l_checkPass := false;
2139 end if;
2140
2141 -- here, we should NOT set the supply subinv/locator for phantom,
2142 -- however, since inv's validation logic validates the whole phantom record, if will fail
2143 -- if we don't default the subinv/locator.
2144 -- for next release, we should revert this logic back and have inv skip the record for phantom.
2145 -- if ( l_checkPass and nvl(x_compTbl(l_count).wip_supply_type, -1) <> 6 ) then
2146 if ( l_checkPass ) then
2147 /* Fix for Bug#6134576. Let non transactable service item go through */
2148
2149 l_service_item_flag := 'Y' ;
2150
2151 select msi.service_item_flag
2152 into l_service_item_flag
2153 from mtl_system_items msi
2154 where msi.inventory_item_id = x_compTbl(l_count).inventory_item_id
2155 and msi.organization_id = p_orgID ;
2156
2157 if l_service_item_flag = 'N' then
2158 if ( nvl(x_compTbl(l_count).wip_supply_type, -1) <> 6 and
2159 x_compTbl(l_count).mtl_transactions_enabled_flag <> 'Y' and
2160 p_txnFlag) then --Bug4538135.Don't check this while creating job
2161 x_returnStatus := fnd_api.g_ret_sts_error;
2162 fnd_message.set_name('WIP', 'WIP_COMP_NOT_TRANSACTABLE');
2163 fnd_message.set_token('ENTITY1', x_compTbl(l_count).item_name);
2164 fnd_msg_pub.add;
2165 if (l_logLevel <= wip_constants.full_logging) then
2166 wip_logger.log(p_msg => 'Item ' || x_compTbl(l_count).inventory_item_id ||
2167 ' not transactable, failed explosion!',
2168 x_returnStatus => l_returnStatus);
2169 end if;
2170 x_compTbl.delete;
2171 return;
2172 end if;
2173 end if;
2174 l_locatorID := null; /* reset locatorID */
2175 if ( x_compTbl(l_count).supply_subinventory is null) then
2176 select msi.wip_supply_subinventory,
2177 msi.wip_supply_locator_id,
2178 wp.default_pull_supply_subinv,
2179 wp.default_pull_supply_locator_id
2180 into l_msiSubinv,
2181 l_msiLocatorID,
2182 l_wpSubinv,
2183 l_wpLocatorID
2184 from mtl_system_items msi,
2185 wip_parameters wp
2186 where msi.organization_id = wp.organization_id
2187 and msi.organization_id = p_orgID
2188 and msi.inventory_item_id = x_compTbl(l_count).inventory_item_id;
2189 /* Bugfix 4556685: Locator ID and supply subinventory at parameter and item levels
2190 should be checked together. */
2191 if ( l_msiSubinv is not null ) then
2192 x_compTbl(l_count).supply_subinventory := l_msiSubinv;
2193 l_locatorID := l_msiLocatorID;
2194 /* for bug 5057025. Do not default supply info for push components */
2195 /* for bug 5262858, we should default supply info for wol/flow txn */
2196 else
2197 if(nvl(p_defaultPushSubinv, 'N') = 'Y') then --This is wol/flow txn
2198 x_compTbl(l_count).supply_subinventory := l_wpSubinv;
2199 l_locatorID := l_wpLocatorID;
2200 else -- This is discrete txn
2201 if(x_compTbl(l_count).wip_supply_type in(wip_constants.op_pull, wip_constants.assy_pull)) then
2202 x_compTbl(l_count).supply_subinventory := l_wpSubinv;
2203 l_locatorID := l_wpLocatorID;
2204 end if;
2205 end if;
2206 end if;
2207 else
2208 if x_compTbl(l_count).supply_locator_id is not null then
2209 l_locatorID := x_compTbl(l_count).supply_locator_id;
2210 else
2211 l_locatorID := null;
2212 end if;
2213 end if;
2214
2215 if(l_locatorID is not null) then
2216 l_success := pjm_project_locator.get_component_projectSupply(
2217 p_organization_id => p_orgID,
2218 p_project_id => x_compTbl(l_count).project_id,
2219 p_task_id => x_compTbl(l_count).task_id,
2220 p_wip_entity_id => null,--unused
2221 p_supply_sub => x_compTbl(l_count).supply_subinventory,
2222 p_supply_loc_id => l_locatorID,
2223 p_item_id => x_compTbl(l_count).inventory_item_id,
2224 p_org_loc_control => null); --unused
2225 x_compTbl(l_count).supply_locator_id := l_locatorID;
2226 end if;
2227 /* Fix for bug 5437157. Populate locator_name field as this will be displayed in
2228 backflush region of self service flow txn pages */
2229 if(x_compTbl(l_count).supply_locator_id is not null and
2230 x_compTbl(l_count).locator_name is null) then
2231 select decode (mp.project_reference_enabled,
2232 null,milk.concatenated_segments,
2233 2,milk.concatenated_segments,
2234 1, inv_project.get_pjm_locsegs(milk.concatenated_segments))
2235 into x_compTbl(l_count).locator_name
2236 from mtl_parameters mp, mtl_item_locations_kfv milk
2237 where mp.organization_id = p_orgID
2238 and mp.organization_id = milk.organization_id
2239 and milk.inventory_location_id = x_compTbl(l_count).supply_locator_id;
2240 end if;
2241 /* end of fix for bug 5437157 */
2242 end if; -- end of l_checkPass
2243
2244 -- we need to increment the counter anyway even though the current element
2245 -- may be deleted since PL/SQL keeps placeholders for deleted elements.
2246 l_count := l_count + 1;
2247 end loop;
2248 end explodeRequirementsAndDefault;
2249
2250 /**
2251 * This procedure explodes the BOM and insert the material requirement into
2252 * mmtt table under the given header id and completion txn id.
2253 * If the supply subinv and locator in the BOM is not provided, then it will try
2254 * to default those the rule: BOM level --> item level --> wip parameter
2255 */
2256 procedure explodeRequirementsToMMTT(p_txnTempID in number,
2257 p_assyID in number,
2258 p_orgID in number,
2259 p_qty in number,
2260 p_altBomDesig in varchar2,
2261 p_altOption in number,
2262 p_txnDate in date,
2263 p_projectID in number,
2264 p_taskID in number,
2265 p_toOpSeqNum in number,
2266 p_altRoutDesig in varchar2,
2267 x_returnStatus out nocopy varchar2) is
2268 l_compTbl system.wip_component_tbl_t;
2269 l_count number;
2270
2271 l_childTxnTypeID number;
2272 l_childTxnActionID number;
2273 l_insertPhantom number;
2274 l_acctPeriodID number;
2275 l_openPastPeriod boolean := false;
2276
2277 l_params wip_logger.param_tbl_t;
2278 l_returnStatus varchar2(1);
2279 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
2280 begin
2281
2282 if (l_logLevel <= wip_constants.trace_logging) then
2283 l_params(1).paramName := 'p_txnTempID';
2284 l_params(1).paramValue := p_txnTempID;
2285 l_params(2).paramName := 'p_assyID';
2286 l_params(2).paramValue := p_assyID;
2287 l_params(3).paramName := 'p_orgID';
2288 l_params(3).paramValue := p_orgID;
2289 l_params(4).paramName := 'p_qty';
2290 l_params(4).paramValue := p_qty;
2291 l_params(5).paramName := 'p_altBomDesig';
2292 l_params(5).paramValue := p_altBomDesig;
2293 l_params(6).paramName := 'p_altOption';
2294 l_params(6).paramValue := p_altOption;
2295 l_params(7).paramName := 'p_txnDate';
2296 l_params(7).paramValue := p_txnDate;
2297 l_params(8).paramName := 'p_projectID';
2298 l_params(8).paramValue := p_projectID;
2299 l_params(9).paramName := 'p_taskID';
2300 l_params(9).paramValue := p_taskID;
2301 l_params(10).paramName := 'p_toOpSeqNum';
2302 l_params(10).paramValue := p_toOpSeqNum;
2303 l_params(11).paramName := 'p_altRoutDesig';
2304 l_params(11).paramValue := p_altRoutDesig;
2305 wip_logger.entryPoint(p_procName => 'wip_flowUtil_priv.explodeRequirementsToMMTT',
2306 p_params => l_params,
2307 x_returnStatus => x_returnStatus);
2308 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
2309 raise fnd_api.g_exc_unexpected_error;
2310 end if;
2311 end if;
2312
2313 invttmtx.tdatechk(org_id => p_orgID,
2314 transaction_date => p_txnDate,
2315 period_id => l_acctPeriodID,
2316 open_past_period => l_openPastPeriod);
2317 if ( l_acctPeriodID is null ) then
2318 fnd_message.set_name('INV', 'INV_NO_OPEN_PERIOD');
2319 fnd_msg_pub.add;
2320 raise fnd_api.g_exc_unexpected_error;
2321 end if;
2322
2323 explodeRequirementsAndDefault(p_assyID => p_assyID,
2324 p_orgID => p_orgID,
2325 p_qty => p_qty,
2326 p_altBomDesig => p_altBomDesig,
2327 p_altOption => p_altOption,
2328 p_txnDate => p_txnDate,
2329 p_implFlag => 1,
2330 p_projectID => p_projectID,
2331 p_taskID => p_taskID,
2332 p_toOpSeqNum => p_toOpSeqNum,
2333 p_altRoutDesig => p_altRoutDesig,
2334 /* fix for bug4538135 */ p_txnFlag => true, /* E 4369064 */
2335 x_compTbl => l_compTbl,
2336 x_returnStatus => x_returnStatus);
2337 if ( x_returnStatus <> fnd_api.g_ret_sts_success ) then
2338 raise fnd_api.g_exc_unexpected_error;
2339 end if;
2340
2341 l_insertPhantom := wip_globals.use_phantom_routings(p_orgID);
2342
2343 l_count := l_compTbl.first;
2344 while ( l_count is not null ) loop
2345 -- we don't insert phantom comp(for phantom routing resource charging) into mmtt if
2346 -- the bom parameter is set to NO.
2347 if ( l_insertPhantom = WIP_CONSTANTS.YES or
2348 nvl(l_compTbl(l_count).wip_supply_type, 1) <> 6 ) then
2349 -- derive the txn action and type id
2350 l_childTxnActionID := l_compTbl(l_count).transaction_action_id;
2351 l_childTxnTypeID := getTypeFromAction(l_childTxnActionID);
2352
2353 insert into mtl_material_transactions_temp(
2354 last_update_date,
2355 last_updated_by,
2356 creation_date,
2357 created_by,
2358 last_update_login,
2359 request_id,
2360 program_application_id,
2361 program_id,
2362 program_update_date,
2363 transaction_header_id,
2364 transaction_temp_id,
2365 transaction_mode,
2366 transaction_source_id,
2367 transaction_source_type_id,
2368 transaction_type_id,
2369 transaction_action_id,
2370 transaction_date,
2371 transaction_quantity,
2372 transaction_uom,
2373 primary_quantity,
2374 parent_transaction_temp_id,
2375 wip_supply_type,
2376 wip_entity_type,
2377 inventory_item_id,
2378 revision,
2379 operation_seq_num,
2380 organization_id,
2381 source_code,
2382 process_flag,
2383 posting_flag,
2384 lock_flag,
2385 subinventory_code,
2386 locator_id,
2387 acct_period_id,
2388 completion_transaction_id,
2389 flow_schedule
2390 )
2391 select
2392 sysdate,
2393 fnd_global.user_id,
2394 sysdate,
2395 fnd_global.user_id,
2396 fnd_global.login_id,
2397 fnd_global.conc_request_id,
2398 fnd_global.prog_appl_id,
2399 fnd_global.conc_program_id,
2400 sysdate,
2401 mmtt.transaction_header_id,
2402 mtl_material_transactions_s.nextval,
2403 mmtt.transaction_mode,
2404 mmtt.transaction_source_id,
2405 5,
2406 l_childTxnTypeID,
2407 l_childTxnActionID,
2408 p_txnDate,
2409 l_compTbl(l_count).primary_quantity * -1,
2410 l_compTbl(l_count).primary_uom_code,
2411 l_compTbl(l_count).primary_quantity * -1,
2412 p_txnTempID, -- parent transaction temp id
2413 l_compTbl(l_count).wip_supply_type,
2414 mmtt.wip_entity_type,
2415 l_compTbl(l_count).inventory_item_id,
2416 l_compTbl(l_count).revision,
2417 decode(l_compTbl(l_count).wip_supply_type,
2418 6, -1*l_compTbl(l_count).operation_seq_num,
2419 l_compTbl(l_count).operation_seq_num),
2420 p_orgID,
2421 'WIP Flow Transcaction',
2422 'N', -- default to No. call processLotSerialTemp() to update process flag
2423 -- and determine if unfulfilled l/s requirements exist
2424 'Y',
2425 2, -- lock flag
2426 l_compTbl(l_count).supply_subinventory,
2427 l_compTbl(l_count).supply_locator_id,
2428 l_acctPeriodID,
2429 mmtt.completion_transaction_id,
2430 'Y'
2431 from mtl_material_transactions_temp mmtt
2432 where mmtt.transaction_temp_id = p_txnTempID;
2433
2434 if (l_logLevel <= wip_constants.full_logging) then
2435 wip_logger.log(p_msg => 'Insert item ' || l_compTbl(l_count).inventory_item_id ||
2436 ' under op ' || l_compTbl(l_count).operation_seq_num,
2437 x_returnStatus => l_returnStatus);
2438 end if;
2439 end if;
2440
2441 l_count := l_compTbl.next(l_count);
2442 end loop;
2443
2444 l_compTbl.delete;
2445 if (l_logLevel <= wip_constants.trace_logging) then
2446 wip_logger.exitPoint(p_procName => 'wip_flowUtil_priv.explodeRequirementsToMMTT',
2447 p_procReturnStatus => x_returnStatus,
2448 p_msg => 'Exploded BOM to MMTT successfully!',
2449 x_returnStatus => l_returnStatus); --discard logging return status
2450 end if;
2451 exception
2452 when others then
2453 x_returnStatus := fnd_api.g_ret_sts_error;
2454 if (l_logLevel <= wip_constants.trace_logging) then
2455 wip_logger.exitPoint(p_procName => 'wip_flowUtil_priv.explodeRequirementsToMMTT',
2456 p_procReturnStatus => x_returnStatus,
2457 p_msg => 'unexpected error: ' || SQLERRM,
2458 x_returnStatus => l_returnStatus); --discard logging return status
2459 end if;
2460
2461 end explodeRequirementsToMMTT;
2462
2463
2464 /**
2465 * This procedure constructs the wip line ops table of records by calling
2466 * the appropriate BOM API.
2467 *
2468 * You must either privide the routing sequence id or
2469 * (assy id, orgid, alternate routing designator)
2470 * Line Op doesn't have any effective date, it is always in effect. So we
2471 * don't need to check it against the effectivity_date and disable_date. The event
2472 * does though.
2473 *
2474 * p_terminalOpSeqNum is greater than 0, it calls the BOM API to get all the
2475 * line ops up to the terminal line op in the primary path of the routing network.
2476 * p_terminalOpSeqNum is -1, then all the line ops in the primary patch of the
2477 * routing network are cached.
2478 * p_terminalOpSeqNum is -2, then all the line ops (except rework loops) in the
2479 * routing network are cached.
2480 */
2481 procedure constructWipLineOps(p_routingSeqID in number,
2482 p_assyItemID in number,
2483 p_orgID in number,
2484 p_altRoutDesig in varchar2,
2485 p_terminalOpSeqNum in number,
2486 x_lineOpTbl out nocopy bom_rtg_network_api.op_tbl_type) is
2487 l_opSeqID number;
2488 l_num number;
2489 begin
2490 x_lineOpTbl.delete;
2491
2492 if ( p_terminalOpSeqNum > 0 ) then
2493 bom_rtg_network_api.get_primary_prior_line_ops(
2494 p_rtg_sequence_id => p_routingSeqID,
2495 p_assy_item_id => p_assyItemID,
2496 p_org_id => p_orgID,
2497 p_alt_rtg_desig => p_altRoutDesig,
2498 p_curr_line_op => p_terminalOpSeqNum,
2499 x_op_tbl => x_lineOpTbl);
2500 -- we get all that are prior to this one. we need to get the terminalOpSeqNum as well
2501 begin
2502 if ( p_routingSeqID is null ) then
2503 -- line op doesn't have effectivity date
2504 select distinct bos.operation_sequence_id
2505 into l_opSeqID
2506 from bom_operation_sequences bos,
2507 bom_operational_routings bor
2508 where bor.common_routing_sequence_id = bos.routing_sequence_id
2509 and bor.assembly_item_id = p_assyItemID
2510 and bor.organization_id = p_orgID
2511 and nvl(alternate_routing_designator, 'NONE') =
2512 nvl(p_altRoutDesig, 'NONE')
2513 and bos.operation_seq_num = p_terminalOpSeqNum
2514 and bos.operation_type = 3;
2515 else
2516 select distinct bos.operation_sequence_id
2517 into l_opSeqID
2518 from bom_operation_sequences bos,
2519 bom_operational_routings bor
2520 where bor.common_routing_sequence_id = bos.routing_sequence_id
2521 and bor.common_routing_sequence_id = p_routingSeqID
2522 and bos.operation_seq_num = p_terminalOpSeqNum
2523 and bos.operation_type = 3;
2524 end if;
2525
2526 l_num := x_lineOpTbl.count;
2527 x_lineOpTbl(l_num+1).operation_seq_num := p_terminalOpSeqNum;
2528 x_lineOpTbl(l_num+1).operation_sequence_id := l_opSeqID;
2529 exception
2530 when others then
2531 null; --if the terminal op seq or the routing doesn't exist, just ignore it
2532 end;
2533 elsif ( p_terminalOpSeqNum = -1 ) then
2534 bom_rtg_network_api.get_all_primary_line_ops(
2535 p_rtg_sequence_id => p_routingSeqID,
2536 p_assy_item_id => p_assyItemID,
2537 p_org_id => p_orgID,
2538 p_alt_rtg_desig => p_altRoutDesig,
2539 x_op_tbl => x_lineOpTbl);
2540 elsif ( p_terminalOpSeqNum = -2 ) then
2541 bom_rtg_network_api.get_all_line_ops(
2542 p_rtg_sequence_id => p_routingSeqID,
2543 p_assy_item_id => p_assyItemID,
2544 p_org_id => p_orgID,
2545 p_alt_rtg_desig => p_altRoutDesig,
2546 x_op_tbl => x_lineOpTbl);
2547 end if;
2548 end constructWipLineOps;
2549
2550
2551 /**
2552 * This function decides whether the given event belongs to a line op that is
2553 * prior to or the same as the given line op or not.
2554 * If you call constructWipLineOps before calling this function, then the cache
2555 * built before will be used to check the existence. Otherwise, it will construct
2556 * the cache and then do the compare.
2557 * It returns true if p_eventNum belongs to a line op that is prior or same as
2558 * p_lineOpNum. It returns false otherwise. It also returns false if any of the
2559 * given parameter doesn't exist.
2560 */
2561 function eventInPriorSameLineOp(p_routingSeqID in number,
2562 p_eventNum in number,
2563 p_lineOpNum in number,
2564 p_lineOpTbl in bom_rtg_network_api.op_tbl_type)
2565 return boolean is
2566 l_evtLineOp number;
2567 l_count number;
2568 begin
2569 select distinct bos2.operation_seq_num
2570 into l_evtLineOp
2571 from bom_operation_sequences bos1,
2572 bom_operation_sequences bos2
2573 where bos1.routing_sequence_id = bos2.routing_sequence_id
2574 and bos1.routing_sequence_id = p_routingSeqID
2575 and bos1.operation_seq_num = p_eventNum
2576 and bos1.operation_type = 1 -- event
2577 and bos1.line_op_seq_id = bos2.operation_sequence_id
2578 and bos2.operation_type = 3; -- line op
2579
2580 l_count := p_lineOpTbl.first;
2581 while ( l_count is not null ) loop
2582 if ( p_lineOpTbl(l_count).operation_seq_num = l_evtLineOp ) then
2583 return true;
2584 end if;
2585 l_count := p_lineOpTbl.next(l_count);
2586 end loop;
2587 return false;
2588 exception
2589 when no_data_found then
2590 -- the select statment doesn't select anything, this event is not assigned to
2591 -- any line op.
2592 return true;
2593 when others then
2594 return false;
2595 end eventInPriorSameLineOp;
2596
2597
2598 /**
2599 * This function is used to derive the transaction action id from the
2600 * transaction type id
2601 */
2602 function getTypeFromAction(p_txnActionID in number) return number is
2603 begin
2604 if ( p_txnActionID = WIP_CONSTANTS.ISSCOMP_ACTION ) then
2605 return WIP_CONSTANTS.ISSCOMP_TYPE;
2606 end if;
2607
2608 if ( p_txnActionID = WIP_CONSTANTS.ISSNEGC_ACTION ) then
2609 return WIP_CONSTANTS.ISSNEGC_TYPE;
2610 end if;
2611
2612 if ( p_txnActionID = WIP_CONSTANTS.RETCOMP_ACTION ) then
2613 return WIP_CONSTANTS.RETCOMP_TYPE;
2614 end if;
2615
2616 if ( p_txnActionID = WIP_CONSTANTS.RETNEGC_ACTION ) then
2617 return WIP_CONSTANTS.RETNEGC_TYPE;
2618 end if;
2619
2620 return null;
2621 end getTypeFromAction;
2622
2623 /**
2624 * This function is used to derive the transaction_type_id and transaction_action_id
2625 * of the child given the parent txn type id and required per assembly.
2626 */
2627 procedure getChildTxn(p_parentTxnTypeID in number,
2628 p_signOfPer in number,
2629 x_childTxnTypeID out nocopy number,
2630 x_childTxnActionID out nocopy number) is
2631 begin
2632 if ( p_parentTxnTypeID in (WIP_CONSTANTS.CPLASSY_TYPE,
2633 WIP_CONSTANTS.SCRASSY_TYPE) ) then
2634 if ( p_signOfPer > 0 ) then
2635 x_childTxnTypeID := WIP_CONSTANTS.ISSCOMP_TYPE;
2636 x_childTxnActionID := WIP_CONSTANTS.ISSCOMP_ACTION;
2637 else
2638 x_childTxnTypeID := WIP_CONSTANTS.ISSNEGC_TYPE;
2639 x_childTxnActionID := WIP_CONSTANTS.ISSNEGC_ACTION;
2640 end if;
2641 else -- return or return from scrap
2642 if ( p_signOfPer > 0 ) then
2643 x_childTxnTypeID := WIP_CONSTANTS.RETCOMP_TYPE;
2644 x_childTxnActionID := WIP_CONSTANTS.RETCOMP_ACTION;
2645 else
2646 x_childTxnTypeID := WIP_CONSTANTS.RETNEGC_TYPE;
2647 x_childTxnActionID := WIP_CONSTANTS.RETNEGC_ACTION;
2648 end if;
2649 end if;
2650 end getChildTxn;
2651
2652
2653 /**
2654 * Generate the issue locators for all the issues associated with a completion
2655 * This would be called only for a project related completions.
2656 */
2657 procedure generateCompLocator(p_parentID in number,
2658 x_returnStatus out nocopy varchar2) is
2659 cursor comp_c(cpl_id number) is
2660 select inventory_item_id,
2661 subinventory_code,
2662 locator_id,
2663 rowid
2664 from mtl_material_transactions_temp
2665 where completion_transaction_id = cpl_id
2666 and transaction_source_type_id = 5
2667 and flow_schedule = 'Y'
2668 and process_flag = 'Y'
2669 and locator_id is not null
2670 and transaction_action_id in (WIP_CONSTANTS.ISSCOMP_ACTION,
2671 WIP_CONSTANTS.RETCOMP_ACTION,
2672 WIP_CONSTANTS.ISSNEGC_ACTION,
2673 WIP_CONSTANTS.RETNEGC_ACTION)
2674 order by operation_seq_num;
2675
2676 l_params wip_logger.param_tbl_t;
2677 l_returnStatus varchar2(1);
2678 l_success boolean;
2679
2680 l_orgID number;
2681 l_cplID number;
2682 l_srcProjectID number;
2683 l_srcTaskID number;
2684 l_wipEntityID number;
2685 l_projRefEnabled number;
2686 l_orgLocControl number := 0;
2687 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
2688 begin
2689 if (l_logLevel <= wip_constants.trace_logging) then
2690 l_params(1).paramName := 'p_parentID';
2691 l_params(1).paramValue := p_parentID;
2692 wip_logger.entryPoint(p_procName => 'wip_flowUtil_priv.generateCompLocator',
2693 p_params => l_params,
2694 x_returnStatus => x_returnStatus);
2695 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
2696 raise fnd_api.g_exc_unexpected_error;
2697 end if;
2698 end if;
2699
2700 select organization_id,
2701 completion_transaction_id,
2702 transaction_source_id,
2703 source_project_id,
2704 source_task_id
2705 into l_orgID,
2706 l_cplID,
2707 l_wipEntityID,
2708 l_srcProjectID,
2709 l_srcTaskID
2710 from mtl_material_transactions_temp
2711 where transaction_temp_id = p_parentID;
2712
2713 select nvl(project_reference_enabled, 2),
2714 stock_locator_control_code
2715 into l_projRefEnabled,
2716 l_orgLocControl
2717 from mtl_parameters
2718 where organization_id = l_orgID;
2719
2720 if ( (l_srcProjectID is null) or (l_projRefEnabled <> 1) ) then
2721 if (l_logLevel <= wip_constants.trace_logging) then
2722 wip_logger.exitPoint(p_procName => 'wip_flowUtil_priv.generateCompLocator',
2723 p_procReturnStatus => x_returnStatus,
2724 p_msg => 'Source project id is null or the org parameter ' ||
2725 'does not has project reference enabled',
2726 x_returnStatus => l_returnStatus); --discard logging return status
2727 end if;
2728 return;
2729 end if;
2730
2731 for comp_rec in comp_c(l_cplID) loop
2732 l_success := pjm_project_locator.get_component_projectsupply(
2733 p_organization_id => l_orgID,
2734 p_project_id => l_srcProjectID,
2735 p_task_id => l_srcTaskID,
2736 p_wip_entity_id => l_wipEntityID,
2737 p_supply_sub => comp_rec.subinventory_code,
2738 p_supply_loc_id => comp_rec.locator_id,
2739 p_item_id => comp_rec.inventory_item_id,
2740 p_org_loc_control => l_orgLocControl);
2741 if ( l_success = false ) then
2742 x_returnStatus := fnd_api.g_ret_sts_error;
2743 if (l_logLevel <= wip_constants.trace_logging) then
2744 wip_logger.exitPoint(p_procName => 'wip_flowUtil_priv.generateCompLocator',
2745 p_procReturnStatus => x_returnStatus,
2746 p_msg => 'Error in calling '||
2747 'pjm_project_locator.get_component_projectsupply!',
2748 x_returnStatus => l_returnStatus); --discard logging return status
2749 end if;
2750 return;
2751 end if;
2752
2753 if ( comp_rec.locator_id <> 0 ) then
2754 update mtl_material_transactions_temp
2755 set (locator_id, project_id, task_id) =
2756 (select inventory_location_id,
2757 project_id,
2758 task_id
2759 from mtl_item_locations
2760 where inventory_location_id = comp_rec.locator_id
2761 and organization_id = l_orgID)
2762 where rowid = comp_rec.rowid;
2763 end if;
2764 end loop;
2765
2766 if (l_logLevel <= wip_constants.trace_logging) then
2767 wip_logger.exitPoint(p_procName => 'wip_flowUtil_priv.generateCompLocator',
2768 p_procReturnStatus => x_returnStatus,
2769 p_msg => 'Finished!',
2770 x_returnStatus => l_returnStatus); --discard logging return status
2771 end if;
2772 end generateCompLocator;
2773
2774 end wip_flowUtil_priv;