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