[Home] [Help]
PACKAGE BODY: APPS.WMA_FLOW
Source
1 PACKAGE BODY wma_flow AS
2 /* $Header: wmapflwb.pls 120.10 2007/10/12 18:31:26 vjambhek ship $ */
3
4 Function putIntoMMTT(flowRec FlowRecord, errMsg OUT NOCOPY VARCHAR2) return boolean;
5
6 /**
7 * This procedure is the entry point for work order-less/flow transaction.
8 * Parameters:
9 * parameters FlowParam contains values from the mobile form.
10 * status Indicates success (0), failure (-1).
11 * errMessage The error or warning message, if any.
12 */
13 PROCEDURE insertParentRecord(param IN FlowParam,
14 status OUT NOCOPY NUMBER,
15 errMessage OUT NOCOPY VARCHAR2) IS
16 flowRec FlowRecord;
17 errMsg VARCHAR2(241);
18 l_returnStatus VARCHAR2(1);
19 l_params wip_logger.param_tbl_t;
20 l_logLevel NUMBER := to_number(fnd_log.g_current_runtime_level);
21 Begin
22 status := 0;
23 if (l_logLevel <= wip_constants.trace_logging) then
24 l_params(1).paramName := 'not printing params';
25 l_params(1).paramValue := ' ';
26 wip_logger.entryPoint(p_procName => 'wma_flow.insertParentRecord',
27 p_params => l_params,
28 x_returnStatus => l_returnStatus);
29 end if;
30
31 if ( derive(param, flowRec, errMsg) = false ) then
32 status := -1;
33 errMessage := errMsg;
34 if (l_logLevel <= wip_constants.trace_logging) then
35 wip_logger.exitPoint(p_procName => 'wma_flow.insertParentRecord',
36 p_procReturnStatus => status,
37 p_msg => errMessage,
38 x_returnStatus => l_returnStatus);
39 end if;
40 return;
41 end if;
42
43 if ( put(flowRec, errMsg) = false ) then
44 status := -1;
45 errMessage := errMsg;
46 if (l_logLevel <= wip_constants.trace_logging) then
47 wip_logger.exitPoint(p_procName => 'wma_flow.insertParentRecord',
48 p_procReturnStatus => status,
49 p_msg => errMessage,
50 x_returnStatus => l_returnStatus);
51 end if;
52 return;
53 end if;
54
55 if (l_logLevel <= wip_constants.trace_logging) then
56 wip_logger.exitPoint(p_procName => 'wma_flow.insertParentRecord',
57 p_procReturnStatus => status,
58 p_msg => 'success',
59 x_returnStatus => l_returnStatus);
60 end if;
61
62 EXCEPTION
63 when others then
64 status := -1;
65 fnd_message.set_name ('WIP', 'GENERIC_ERROR');
66 fnd_message.set_token ('FUNCTION', 'wma_flow.insertParentRecord');
67 fnd_message.set_token ('ERROR', SQLCODE || ' ' || SQLERRM);
68 errMessage := fnd_message.get;
69 if (l_logLevel <= wip_constants.trace_logging) then
70 wip_logger.exitPoint(p_procName => 'wma_flow.insertParentRecord',
71 p_procReturnStatus => status,
72 p_msg => errMessage,
73 x_returnStatus => l_returnStatus);
74 end if;
75 End insertParentRecord;
76
77 /**
78 * This procedure is the entry point for work order-less/flow transaction.
79 * Parameters:
80 * parameters FlowParam contains values from the mobile form.
81 * status Indicates success (0), failure (-1).
82 * errMessage The error or warning message, if any.
83 */
84 PROCEDURE insertParentRecordIntoMMTT(param IN FlowParam,
85 status OUT NOCOPY NUMBER,
86 errMessage OUT NOCOPY VARCHAR2) IS
87 flowRec FlowRecord;
88 errMsg VARCHAR2(241);
89 l_returnStatus VARCHAR2(1);
90 l_params wip_logger.param_tbl_t;
91 l_logLevel NUMBER := to_number(fnd_log.g_current_runtime_level);
92 Begin
93
94 if (l_logLevel <= wip_constants.trace_logging) then
95 l_params(1).paramName := 'not printing params';
96 l_params(1).paramValue := ' ';
97 wip_logger.entryPoint(p_procName => 'wma_flow.insertParentRecordintoMMTT',
98 p_params => l_params,
99 x_returnStatus => l_returnStatus);
100 end if;
101
102 if ( derive(param, flowRec, errMsg) = false ) then
103 status := -1;
104 errMessage := errMsg;
105 if (l_logLevel <= wip_constants.trace_logging) then
106 wip_logger.exitPoint(p_procName => 'wma_flow.insertParentRecordintoMMTT',
107 p_procReturnStatus => status,
108 p_msg => errMessage,
109 x_returnStatus => l_returnStatus);
110 end if;
111 return;
112 end if;
113
114 if ( putIntoMMTT(flowRec, errMsg) = false ) then
115 status := -1;
116 errMessage := errMsg;
117 if (l_logLevel <= wip_constants.trace_logging) then
118 wip_logger.exitPoint(p_procName => 'wma_flow.insertParentRecordintoMMTT',
119 p_procReturnStatus => status,
120 p_msg => errMessage,
121 x_returnStatus => l_returnStatus);
122 end if;
123 return;
124 end if;
125
126 if (l_logLevel <= wip_constants.trace_logging) then
127 wip_logger.exitPoint(p_procName => 'wma_flow.insertParentRecordintoMMTT',
128 p_procReturnStatus => status,
129 p_msg => 'success',
130 x_returnStatus => l_returnStatus);
131 end if;
132
133 EXCEPTION
134 when others then
135 status := -1;
136 fnd_message.set_name ('WIP', 'GENERIC_ERROR');
137 fnd_message.set_token ('FUNCTION', 'wma_flow.insertParentRecordintoMMTT');
138 fnd_message.set_token ('ERROR', SQLCODE || ' ' || SQLERRM);
139 errMessage := fnd_message.get;
140 if (l_logLevel <= wip_constants.trace_logging) then
141 wip_logger.exitPoint(p_procName => 'wma_flow.insertParentRecordintoMMTT',
142 p_procReturnStatus => status,
143 p_msg => errMessage,
144 x_returnStatus => l_returnStatus);
145 end if;
146 End insertParentRecordIntoMMTT;
147
148 /**
149 * This function is used to derive the neccessary information to filled out
150 * the FlowRecord structure to passed into function put.
151 */
152 Function derive(param FlowParam,
153 flowRec OUT NOCOPY FlowRecord,
154 errMsg OUT NOCOPY VARCHAR2) return boolean IS
155 assembly wma_common.Item;
156 periodID number;
157 openPastPeriod boolean := false;
158 scheduleNumber VARCHAR2(30);
159 scrapAcctID NUMBER := null;
160 dummy NUMBER;
161 accountingClass VARCHAR2(30);
162 retval VARCHAR2(1);
163 errCode VARCHAR2(80);
164 errMesg1 VARCHAR2(30);
165 errClass1 VARCHAR2(10);
166 errMesg2 VARCHAR2(30);
167 errClass2 VARCHAR2(10);
168 defaultPrefix VARCHAR2(200);
169 Begin
170
171 assembly := wma_derive.getItem(param.assemblyID,
172 param.environment.orgID,
173 param.locatorID);
174
175 if assembly.revQtyControlCode = WIP_CONSTANTS.REV then
176 BOM_REVISIONS.Get_Revision(
177 type => 'PART',
178 eco_status => 'EXCLUDE_OPEN_HOLD',
179 examine_type => 'ALL',
180 org_id => param.environment.orgID,
181 item_id => param.assemblyID,
182 rev_date => param.transactionDate,
183 itm_rev => flowRec.revision);
184 else
185 flowRec.revision := NULL;
186 end if;
187
188 accountingClass := wip_common.default_acc_class(
189 param.environment.orgID,
190 param.assemblyID,
191 4, -- for flow schedule
192 assembly.projectID,
193 errMesg1,
194 errClass1,
195 errMesg2,
196 errClass2);
197
198 -- If there is no WIP defaulting accounting class, error out
199 if(accountingClass is null) then
200 fnd_message.set_name(
201 application => 'WIP',
202 name => 'WIP_NO_DEFAULT_CLASSES');
203 errMsg := fnd_message.get;
204 return false;
205 end if;
206
207 -- get the accounting period
208 invttmtx.tdatechk(
209 org_id => param.environment.orgID,
210 transaction_date => param.transactionDate,
211 period_id => periodID,
212 open_past_period => openPastPeriod);
213
214 if (periodID = -1 or periodID = 0) then
215 fnd_message.set_name(
216 application => 'INV',
217 name => 'INV_NO_OPEN_PERIOD');
218 errMsg := fnd_message.get;
219 return false;
220 end if;
221
222 -- default schedule number, it is the value read from profile option 'WIP_JOB_PREFIX'
223 -- appended by a sequence number. We only default it for non flow schedule.
224 -- 3 means unscheduled, 1 means scheduled
225 if ( param.scheduleNumber is null ) then
226 defaultPrefix := substr(fnd_profile.value('WIP_JOB_PREFIX'), 1, 200);
227 scheduleNumber := defaultPrefix || wma_derive.getNextVal('WIP_JOB_NUMBER_S');
228 else
229 scheduleNumber := param.scheduleNumber;
230 end if;
231 flowRec.schedule_number := scheduleNumber;
232
233 flowRec.transaction_date := param.transactionDate;
234
235 -- derive routing and bom rev info
236 dummy := Wip_Flow_Derive.bom_revision (
237 p_bom_rev => flowRec.bom_revision,
238 p_rev => flowRec.revision,
239 p_bom_rev_date => flowRec.bom_revision_date,
240 p_item_id => param.assemblyID,
241 p_start_date => flowRec.transaction_date,
242 p_Org_id => param.environment.orgID);
243
244 dummy := Wip_Flow_Derive.routing_revision(
245 p_rout_rev => flowRec.routing_revision,
246 p_rout_rev_date => flowRec.routing_revision_date,
247 p_item_id => param.assemblyID,
248 p_start_date => flowRec.transaction_date,
249 p_Org_id => param.environment.orgID);
250
251
252 flowRec.transaction_interface_id := param.transactionIntID;
253 flowRec.transaction_header_id := param.transactionHeaderID;
254 flowRec.completion_transaction_id := param.completionTxnID;
255 flowRec.transaction_mode := wma_derive.getTxnMode(param.environment.orgID);
256 flowRec.process_flag := wip_constants.mti_inventory;
257
258 flowRec.source_code := WMA_COMMON.SOURCE_CODE;
259
260 flowRec.last_updated_by := param.environment.userID;
261 flowRec.last_update_date := sysdate;
262 flowRec.creation_date := sysdate;
263 flowRec.created_by := param.environment.userID;
264
265 flowRec.inventory_item_id := param.assemblyID;
266 flowRec.organization_id := param.environment.orgID;
267 flowRec.acct_period_id := periodID;
268
269 flowRec.transaction_type_id := param.transactionType;
270 flowRec.transaction_quantity := param.transactionQty;
271 flowRec.primary_quantity := param.transactionQty;
272
273 if ( param.transactionType = WIP_CONSTANTS.CPLASSY_TYPE ) then
274 -- for completion
275 flowRec.transaction_action_id := WIP_CONSTANTS.CPLASSY_ACTION;
276 elsif ( param.transactionType = WIP_CONSTANTS.RETASSY_TYPE ) then
277 -- for return
278 flowRec.transaction_action_id := WIP_CONSTANTS.RETASSY_ACTION;
279 elsif ( param.transactionType in (WIP_CONSTANTS.SCRASSY_TYPE,
280 WIP_CONSTANTS.RETSCRA_TYPE) ) then
281 -- for scrap
282 flowRec.transaction_action_id := WIP_CONSTANTS.SCRASSY_ACTION;
283 if ( wma_util.getScrapAcctID(param.environment.orgID,
284 scrapAcctID,
285 errMsg) = false ) then
286 return false;
287 end if;
288 end if;
289
290 flowRec.distribution_account_id := scrapAcctID;
291 flowRec.transaction_uom := param.transactionUOM;
292 flowRec.subinventory_code := param.subinventoryCode;
293 flowRec.locator_id := param.locatorID;
294 flowRec.reason_id := param.reasonID;
295 flowRec.qa_collection_id := param.qualityID;
296 flowRec.transaction_source_type_id := 5; -- means WIP
297
298 flowRec.source_line_id := -1;
299 flowRec.source_header_id := -1;
300
301
302 flowRec.repetitive_line_id := param.lineID;
303 flowRec.operation_seq_num := param.lineOp;
304 flowRec.scheduled_flag := param.scheduledFlag;
305 flowRec.flow_schedule := 'Y';
306 flowRec.wipEntityType := wip_constants.flow;
307
308 flowRec.demand_source_header_id := param.demandSourceHeaderId;
309 flowRec.demand_source_line := param.demandSourceLine;
310 flowRec.demand_source_delivery := param.demandSourceDelivery;
311
312 flowRec.transaction_source_id := param.wipEntityID;
313 if ( flowRec.transaction_source_id is not null ) then
314 flowRec.wip_entity_type := wip_constants.flow; -- means flow schedule
315 end if;
316
317 flowRec.header_id := param.headerId;/*Fix for bug #6216695, which is an FP of 6082623 :
318 Add header id to populate MTLT and MSNT*/
319
320 flowRec.accounting_class := accountingClass;
321 flowRec.kanban_card_id := param.kanbanID;
322
323 -- if the transaction is a work order-less completion, call the PJM
324 -- API to check that the project references are correct
325 if (param.scheduledFlag <> 1 and param.transactionType = WIP_CONSTANTS.CPLASSY_TYPE) then
326 retval := PJM_PROJECT.VALIDATE_PROJ_REFERENCES
327 (x_inventory_org_id => param.environment.orgID,
328 x_project_id => param.projectID,
329 x_task_id => param.taskID,
330 x_date1 => param.transactionDate,
331 x_date2 => NULL,
332 x_calling_function => 'wmapflwb',
333 x_error_code => errCode
334 );
335 if ( retval = PJM_PROJECT.G_VALIDATE_FAILURE ) then
336 wip_utilities.get_message_stack(p_msg => errMsg);
337 return false;
338 end if;
339 end if;
340
341 flowRec.source_project_id := param.projectID;
342 flowRec.source_task_id := param.taskID;
343 flowRec.lpn_id := param.lpnID;
344
345 if ( param.wipEntityID is not null and param.scheduledFlag = 1 ) then
346 select project_id,
347 task_id
348 into flowRec.source_project_id,
349 flowRec.source_task_id
350 from wip_flow_schedules
351 where wip_entity_id = param.wipEntityID
352 and organization_id = param.environment.orgID;
353
354 --if the destination sub is known, transfer the reservation. The sub is
355 --not known for lpn flow completions, so inventory does a callback when
356 --the material is dropped (wma_inv_wrappers.transferReservation())
357 /* Not required as Sales Order will be entered through UI
358 if(param.subinventoryCode is not null AND
359 param.demandSourceHeaderID IS NULL) then
360 -- if the item is a CTO item then we should populate sales order info to mmtt
361 -- talked to Renga and the following sql should be used to determine whether the
362 -- item is CTO item or not.
363 dummy := 0;
364 select count(*) into dummy
365 from mtl_system_items
366 where inventory_item_id = param.assemblyID
367 and organization_id = param.environment.orgID
368 and build_in_wip_flag = 'Y'
369 and base_item_id is not null
370 and bom_item_type = 4
371 and replenish_to_order_flag = 'Y';
372 if ( dummy = 1 ) then
373 select demand_source_header_id,
374 demand_source_line,
375 demand_source_delivery
376 into flowRec.demand_source_header_id,
377 flowRec.demand_source_line,
378 flowRec.demand_source_delivery
379 from wip_flow_schedules
380 where organization_id = param.environment.orgID
381 and wip_entity_id = param.wipEntityID;
382 end if;
383 end if;
384 */
385 end if;
386
387 return true;
388 End derive;
389
390 /**
391 * This function is used to insert the record encapsulated in flowRec to
392 * table mtl_transactions_interface and some furthur validation and processing.
393 */
394 Function put(flowRec FlowRecord, errMsg OUT NOCOPY VARCHAR2) return boolean IS
395 l_dummy varchar2(1);
396 Begin
397 wip_logger.log('lpnid: ' || flowRec.lpn_id, l_dummy);
398 INSERT INTO mtl_transactions_interface
399 (transaction_interface_id,
400 transaction_header_id,
401 completion_transaction_id,
402 transaction_mode,
403 process_flag,
404 source_code,
405 last_updated_by, last_update_date,
406 creation_date, created_by,
407 inventory_item_id,
408 organization_id,
409 acct_period_id,
410 transaction_date,
411 bom_revision, revision,
412 bom_revision_date,
413 routing_revision, routing_revision_date,
414 transaction_type_id,
415 transaction_action_id,
416 transaction_quantity,
417 primary_quantity,
418 distribution_account_id,
419 transaction_uom,
420 subinventory_code,
421 locator_id, reason_id,
422 qa_collection_id,
423 transaction_source_type_id,
424 schedule_number,
425 repetitive_line_id,
426 operation_seq_num,
427 scheduled_flag,
428 flow_schedule,
429 wip_entity_type,
430 transaction_source_id,
431 accounting_class,
432 source_project_id,
433 source_task_id,
434 project_id,
435 task_id,
436 kanban_card_id,
437 demand_source_header_id,
438 demand_source_line,
439 demand_source_delivery,
440 lpn_id,
441 source_header_id,
442 source_line_id,
443 transaction_batch_id, --bug 4545130
444 transaction_batch_seq --bug 4545130
445 )
446 VALUES (flowRec.transaction_interface_id,
447 flowRec.transaction_header_id,
448 flowRec.completion_transaction_id,
449 flowRec.transaction_mode,
450 flowRec.process_flag,
451 flowRec.source_code,
452 flowRec.last_updated_by, flowRec.last_update_date,
453 flowRec.creation_date, flowRec.created_by,
454 flowRec.inventory_item_id,
455 flowRec.organization_id,
456 flowRec.acct_period_id,
457 flowRec.transaction_date,
458 flowRec.bom_revision, flowRec.revision,
459 flowRec.bom_revision_date,
460 flowRec.routing_revision,
461 flowRec.routing_revision_date,
462 flowRec.transaction_type_id,
463 flowRec.transaction_action_id,
464 flowRec.transaction_quantity,
465 flowRec.primary_quantity,
466 flowRec.distribution_account_id,
467 flowRec.transaction_uom,
468 flowRec.subinventory_code,
469 flowRec.locator_id,
470 flowRec.reason_id,
471 flowRec.qa_collection_id,
472 flowRec.transaction_source_type_id,
473 flowRec.schedule_number,
474 flowRec.repetitive_line_id,
475 flowRec.operation_seq_num,
476 flowRec.scheduled_flag,
477 flowRec.flow_schedule,
478 flowRec.wip_entity_type,
479 flowRec.transaction_source_id,
480 flowRec.accounting_class,
481 flowRec.source_project_id,
482 flowRec.source_task_id,
483 flowRec.source_project_id,
484 flowRec.source_task_id,
485 flowRec.kanban_card_id,
486 flowRec.demand_source_header_id,
487 flowRec.demand_source_line,
488 flowRec.demand_source_delivery,
489 flowRec.lpn_id,
490 flowRec.source_header_id,
491 flowRec.source_line_id,
492 flowRec.transaction_header_id, --bug 4545130
493 wip_constants.ASSY_BATCH_SEQ --bug 4545130
494 );
495 return true;
496
497 EXCEPTION
498 when others then
499 fnd_message.set_name ('WIP', 'GENERIC_ERROR');
500 fnd_message.set_token ('FUNCTION', 'wma_flow.put');
501 fnd_message.set_token ('ERROR', SQLCODE || ' ' || SQLERRM);
502 errMsg := fnd_message.get;
503 return false;
504 End put;
505
506 Function putIntoMMTT(flowRec FlowRecord, errMsg OUT NOCOPY VARCHAR2) return boolean IS
507 l_dummy varchar2(1);
508
509 /* Start - Fix for bug #6216695, which is an FP of 6082623 :
510 Changed code to make it compatible with 8i database.
511 Following cursor has been created for this */
512 l_txnTmpID NUMBER;
513 cursor get_serial_txn is
514 select mtlt.serial_transaction_temp_id,
515 wlcs.fm_serial_number,
516 wlcs.to_serial_number
517 from mtl_transaction_lots_temp mtlt,
518 wip_lpn_completions_serials wlcs
519 where mtlt.lot_number = wlcs.lot_number
520 and wlcs.header_id = flowRec.header_id
521 and mtlt.transaction_temp_id = l_txnTmpID;
522 /* End - Fix for bug #6216695, which is an FP of 6082623 */
523
524 Begin
525 wip_logger.log('lpnid: ' || flowRec.lpn_id, l_dummy);
526 INSERT INTO mtl_material_transactions_temp
527 (transaction_temp_id,
528 transaction_header_id,
529 completion_transaction_id,
530 transaction_mode,
531 process_flag,
532 source_code,
533 last_updated_by, last_update_date,
534 creation_date, created_by,
535 inventory_item_id,
536 organization_id,
537 acct_period_id,
538 transaction_date,
539 bom_revision, revision,
540 bom_revision_date,
541 routing_revision, routing_revision_date,
542 transaction_type_id,
543 transaction_action_id,
544 transaction_quantity,
545 primary_quantity,
546 distribution_account_id,
547 transaction_uom,
548 subinventory_code,
549 locator_id, reason_id,
550 qa_collection_id,
551 transaction_source_type_id,
552 schedule_number,
553 repetitive_line_id,
554 operation_seq_num,
555 scheduled_flag,
556 flow_schedule,
557 wip_entity_type,
558 transaction_source_id,
559 class_code,
560 source_project_id,
561 source_task_id,
562 project_id,
563 task_id,
564 kanban_card_id,
565 demand_source_header_id,
566 demand_source_line,
567 demand_source_delivery,
568 lpn_id--,
569 -- source_header_id,
570 -- source_line_id
571 )
572 VALUES (flowRec.transaction_interface_id,
573 flowRec.transaction_header_id,
574 flowRec.completion_transaction_id,
575 flowRec.transaction_mode,
576 decode(flowRec.process_flag, wip_constants.mti_inventory, 'Y', 'N'),
577 flowRec.source_code,
578 flowRec.last_updated_by, flowRec.last_update_date,
579 flowRec.creation_date, flowRec.created_by,
580 flowRec.inventory_item_id,
581 flowRec.organization_id,
582 flowRec.acct_period_id,
583 flowRec.transaction_date,
584 flowRec.bom_revision, flowRec.revision,
585 flowRec.bom_revision_date,
586 flowRec.routing_revision,
587 flowRec.routing_revision_date,
588 flowRec.transaction_type_id,
589 flowRec.transaction_action_id,
590 flowRec.transaction_quantity,
591 flowRec.primary_quantity,
592 flowRec.distribution_account_id,
593 flowRec.transaction_uom,
594 flowRec.subinventory_code,
595 flowRec.locator_id,
596 flowRec.reason_id,
597 flowRec.qa_collection_id,
598 flowRec.transaction_source_type_id,
599 flowRec.schedule_number,
600 flowRec.repetitive_line_id,
601 flowRec.operation_seq_num,
602 flowRec.scheduled_flag,
603 flowRec.flow_schedule,
604 flowRec.wip_entity_type,
605 flowRec.transaction_source_id,
606 flowRec.accounting_class,
607 flowRec.source_project_id,
608 flowRec.source_task_id,
609 flowRec.source_project_id,
610 flowRec.source_task_id,
611 flowRec.kanban_card_id,
612 flowRec.demand_source_header_id,
613 flowRec.demand_source_line,
614 flowRec.demand_source_delivery,
615 flowRec.lpn_id--,
616 -- flowRec.source_header_id,
617 -- flowRec.source_line_id
618 )
619 returning transaction_temp_id into l_txnTmpID ;
620 /*Fix for bug #6216695, which is an FP of 6082623 :
621 Store txnTempId into local variable*/
622
623
624 /*Start - Fix for bug #6216695, which is an FP of 6082623 :
625 Insert records into MTLT and MSNT also. Need to create records in
626 mtl_transaction_lots_temp and mtl_serial_numbers_temp based on data
627 in wip_lpn_completions_lots and wip_lpn_completions_serials.
628 This is done so that the data is available for label printing. */
629
630 insert into mtl_transaction_lots_temp(
631 transaction_temp_id,
632 serial_transaction_temp_id,
633 creation_date,
634 created_by,
635 last_update_login,
636 request_id,
637 program_update_date,
638 program_application_id,
639 program_id,
640 transaction_quantity,
641 primary_quantity,
642 lot_number,
643 lot_expiration_date,
644 error_code,
645 lot_attribute_category,
646 status_id,
647 c_attribute1,
648 c_attribute2,
649 c_attribute3,
650 c_attribute4,
651 c_attribute5,
652 c_attribute6,
653 c_attribute7,
654 c_attribute8,
655 c_attribute9,
656 c_attribute10,
657 c_attribute11,
658 c_attribute12,
659 c_attribute13,
660 c_attribute14,
661 c_attribute15,
662 c_attribute16,
663 c_attribute17,
664 c_attribute18,
665 c_attribute19,
666 c_attribute20,
667 d_attribute1,
668 d_attribute2,
669 d_attribute3,
670 d_attribute4,
671 d_attribute5,
672 d_attribute6,
673 d_attribute7,
674 d_attribute8,
675 d_attribute9,
676 d_attribute10,
677 n_attribute1,
678 n_attribute2,
679 n_attribute3,
680 n_attribute4,
681 n_attribute5,
682 n_attribute6,
683 n_attribute7,
684 n_attribute8,
685 n_attribute9,
686 n_attribute10,
687 territory_code,
688 vendor_name,
689 supplier_lot_number,
690 vendor_id,
691 description,
692 grade_code,
693 origination_date,
694 date_code,
695 change_date,
696 age,
697 retest_date,
698 maturity_date,
699 item_size,
700 color,
701 volume,
702 volume_uom,
703 place_of_origin,
704 best_by_date,
705 length,
706 length_uom,
707 recycled_content,
708 thickness,
709 thickness_uom,
710 width,
711 width_uom,
712 curl_wrinkle_fold,
713 last_update_date,
714 last_updated_by
715 )
716 select l_txnTmpID,
717 null,
718 wlcl.creation_date,
719 wlcl.created_by,
720 wlcl.last_update_login,
721 wlcl.request_id,
722 wlcl.program_update_date,
723 wlcl.program_application_id,
724 wlcl.program_id,
725 wlcl.transaction_quantity,
726 wlcl.primary_quantity,
727 wlcl.lot_number,
728 wlcl.lot_expiration_date,
729 wlcl.error_code,
730 wlcl.lot_attribute_category,
731 wlcl.status_id,
732 wlcl.c_attribute1,
733 wlcl.c_attribute2,
734 wlcl.c_attribute3,
735 wlcl.c_attribute4,
736 wlcl.c_attribute5,
737 wlcl.c_attribute6,
738 wlcl.c_attribute7,
739 wlcl.c_attribute8,
740 wlcl.c_attribute9,
741 wlcl.c_attribute10,
742 wlcl.c_attribute11,
743 wlcl.c_attribute12,
744 wlcl.c_attribute13,
745 wlcl.c_attribute14,
746 wlcl.c_attribute15,
747 wlcl.c_attribute16,
748 wlcl.c_attribute17,
749 wlcl.c_attribute18,
750 wlcl.c_attribute19,
751 wlcl.c_attribute20,
752 wlcl.d_attribute1,
753 wlcl.d_attribute2,
754 wlcl.d_attribute3,
755 wlcl.d_attribute4,
756 wlcl.d_attribute5,
757 wlcl.d_attribute6,
758 wlcl.d_attribute7,
759 wlcl.d_attribute8,
760 wlcl.d_attribute9,
761 wlcl.d_attribute10,
762 wlcl.n_attribute1,
763 wlcl.n_attribute2,
764 wlcl.n_attribute3,
765 wlcl.n_attribute4,
766 wlcl.n_attribute5,
767 wlcl.n_attribute6,
768 wlcl.n_attribute7,
769 wlcl.n_attribute8,
770 wlcl.n_attribute9,
771 wlcl.n_attribute10,
772 wlcl.territory_code,
773 wlcl.vendor_name,
774 wlcl.supplier_lot_number,
775 wlcl.vendor_id,
776 wlcl.description,
777 wlcl.grade_code,
778 wlcl.origination_date,
779 wlcl.date_code,
780 wlcl.change_date,
781 wlcl.age,
782 wlcl.retest_date,
783 wlcl.maturity_date,
784 wlcl.item_size,
785 wlcl.color,
786 wlcl.volume,
787 wlcl.volume_uom,
788 wlcl.place_of_origin,
789 wlcl.best_by_date,
790 wlcl.length,
791 wlcl.length_uom,
792 wlcl.recycled_content,
793 wlcl.thickness,
794 wlcl.thickness_uom,
795 wlcl.width,
796 wlcl.width_uom,
797 wlcl.curl_wrinkle_fold,
798 wlcl.last_update_date,
799 wlcl.last_updated_by
800 from wip_lpn_completions_lots wlcl
801 where wlcl.header_id = flowRec.header_id;
802
803 update mtl_transaction_lots_temp
804 set serial_transaction_temp_id = mtl_material_transactions_s.nextval
805 where transaction_temp_id=l_txnTmpID
806 and lot_number in
807 (select lot_number
808 from wip_lpn_completions_serials
809 where header_id = flowRec.header_id) ;
810
811
812 insert into mtl_serial_numbers_temp(
813 transaction_temp_id,
814 fm_serial_number,
815 to_serial_number,
816 serial_prefix,
817 parent_serial_number,
818 error_code,
819 c_attribute1,
820 c_attribute2,
821 c_attribute3,
822 c_attribute4,
823 c_attribute5,
824 c_attribute6,
825 c_attribute7,
826 c_attribute8,
827 c_attribute9,
828 c_attribute10,
829 c_attribute11,
830 c_attribute12,
831 c_attribute13,
832 c_attribute14,
833 c_attribute15,
834 c_attribute16,
835 c_attribute17,
836 c_attribute18,
837 c_attribute19,
838 c_attribute20,
839 d_attribute1,
840 d_attribute2,
841 d_attribute3,
842 d_attribute4,
843 d_attribute5,
844 d_attribute6,
845 d_attribute7,
846 d_attribute8,
847 d_attribute9,
848 d_attribute10,
849 n_attribute1,
850 n_attribute2,
851 n_attribute3,
852 n_attribute4,
853 n_attribute5,
854 n_attribute6,
855 n_attribute7,
856 n_attribute8,
857 n_attribute9,
858 n_attribute10,
859 territory_code,
860 time_since_new,
861 cycles_since_new,
862 time_since_overhaul,
863 cycles_since_overhaul,
864 time_since_repair,
865 cycles_since_repair,
866 time_since_visit,
867 cycles_since_visit,
868 time_since_mark,
869 cycles_since_mark,
870 number_of_repairs,
871 last_update_date,
872 last_updated_by,
873 creation_date,
874 created_by,
875 last_update_login,
876 request_id,
877 program_application_id,
878 program_id,
879 program_update_date,
880 serial_attribute_category,
881 status_id,
882 origination_date
883 )
884 select l_txnTmpID,
885 wlcs.fm_serial_number,
886 wlcs.to_serial_number,
887 wlcs.serial_prefix,
888 wlcs.parent_serial_number,
889 wlcs.error_code,
890 wlcs.c_attribute1,
891 wlcs.c_attribute2,
892 wlcs.c_attribute3,
893 wlcs.c_attribute4,
894 wlcs.c_attribute5,
895 wlcs.c_attribute6,
896 wlcs.c_attribute7,
897 wlcs.c_attribute8,
898 wlcs.c_attribute9,
899 wlcs.c_attribute10,
900 wlcs.c_attribute11,
901 wlcs.c_attribute12,
902 wlcs.c_attribute13,
903 wlcs.c_attribute14,
904 wlcs.c_attribute15,
905 wlcs.c_attribute16,
906 wlcs.c_attribute17,
907 wlcs.c_attribute18,
908 wlcs.c_attribute19,
909 wlcs.c_attribute20,
910 wlcs.d_attribute1,
911 wlcs.d_attribute2,
912 wlcs.d_attribute3,
913 wlcs.d_attribute4,
914 wlcs.d_attribute5,
915 wlcs.d_attribute6,
916 wlcs.d_attribute7,
917 wlcs.d_attribute8,
918 wlcs.d_attribute9,
919 wlcs.d_attribute10,
920 wlcs.n_attribute1,
921 wlcs.n_attribute2,
922 wlcs.n_attribute3,
923 wlcs.n_attribute4,
924 wlcs.n_attribute5,
925 wlcs.n_attribute6,
926 wlcs.n_attribute7,
927 wlcs.n_attribute8,
928 wlcs.n_attribute9,
929 wlcs.n_attribute10,
930 wlcs.territory_code,
931 wlcs.time_since_new,
932 wlcs.cycles_since_new,
933 wlcs.time_since_overhaul,
934 wlcs.cycles_since_overhaul,
935 wlcs.time_since_repair,
936 wlcs.cycles_since_repair,
937 wlcs.time_since_visit,
938 wlcs.cycles_since_visit,
939 wlcs.time_since_mark,
940 wlcs.cycles_since_mark,
941 wlcs.number_of_repairs,
942 wlcs.last_update_date,
943 wlcs.last_updated_by,
944 wlcs.creation_date,
945 wlcs.created_by,
946 wlcs.last_update_login,
947 wlcs.request_id,
948 wlcs.program_application_id,
949 wlcs.program_id,
950 wlcs.program_update_date,
951 wlcs.serial_attribute_category,
952 wlcs.status_id,
953 wlcs.origination_date
954 from wip_lpn_completions_serials wlcs
955 where wlcs.header_id = flowRec.header_id;
956
957 for serial_rec in get_serial_txn loop
958
959 update mtl_serial_numbers_temp
960 set transaction_temp_id = serial_rec.serial_transaction_temp_id
961 where fm_serial_number = serial_rec.fm_serial_number
962 and to_serial_number = serial_rec.to_serial_number
963 and transaction_temp_id = l_txnTmpID;
964
965 end loop;
966
967 /*End - Fix for bug #6216695, which is an FP of 6082623 :
968 Insert records into MTLT and MSNT also*/
969
970 return true;
971
972 EXCEPTION
973 when others then
974 fnd_message.set_name ('WIP', 'GENERIC_ERROR');
975 fnd_message.set_token ('FUNCTION', 'wma_flow.putIntoMMTT');
976 fnd_message.set_token ('ERROR', SQLCODE || ' ' || SQLERRM);
977 errMsg := fnd_message.get;
978 return false;
979 End putIntoMMTT;
980
981 procedure explodeBOMAndDerive(p_assyID in number,
982 p_orgID in number,
983 p_qty in number,
984 p_wipEntityID in number,
985 p_txnDate in date,
986 p_projectID in number,
987 p_taskID in number,
988 p_toOpSeqNum in number,
989 x_lotEntryType out nocopy number,
990 x_compInfo out nocopy system.wip_lot_serial_obj_t,
991 x_returnStatus out nocopy varchar2,
992 x_errMessage out nocopy varchar2) is
993 l_compTbl system.wip_component_tbl_t;
994 l_bomRevDate date := null;
995 l_count number;
996 l_returnStatus VARCHAR2(1);
997 l_params wip_logger.param_tbl_t;
998 l_logLevel NUMBER := to_number(fnd_log.g_current_runtime_level);
999
1000 l_alt_bom VARCHAR2(10);
1001 l_alt_rtg VARCHAR2(10);
1002 l_line_id NUMBER;
1003
1004 cursor wfs_info_cursor(wipEntityId number) is
1005 select wip_entity_id,
1006 planned_quantity,
1007 nvl(quantity_completed,0) as quantity_completed,
1008 nvl(quantity_scrapped,0) as quantity_scrapped,
1009 (planned_quantity - nvl(quantity_completed,0) - nvl(quantity_scrapped,0)) as open_quantity
1010 from wip_flow_schedules wfs
1011 where wfs.wip_entity_id = wipEntityId
1012 ;
1013
1014 l_wfs_info wfs_info_cursor%ROWTYPE := null;
1015
1016 begin
1017 x_returnStatus := fnd_api.g_ret_sts_success;
1018
1019 if (l_logLevel <= wip_constants.trace_logging) then
1020 l_params(1).paramName := 'not printing params';
1021 l_params(1).paramValue := ' ';
1022 wip_logger.entryPoint(p_procName => 'wma_flow.explodeBOMAndDerive',
1023 p_params => l_params,
1024 x_returnStatus => l_returnStatus);
1025 end if;
1026
1027 select backflush_lot_entry_type
1028 into x_lotEntryType
1029 from wip_parameters
1030 where organization_id = p_orgID;
1031
1032
1033 /*
1034 if ( p_wipEntityID is not null ) then
1035 select scheduled_completion_date
1036 into l_bomRevDate
1037 from wip_flow_schedules
1038 where wip_entity_id = p_wipEntityID;
1039 end if;
1040 */
1041
1042
1043 if ( p_wipEntityID is not null ) then
1044 select scheduled_completion_date, line_id, alternate_bom_designator,
1045 alternate_routing_designator
1046 into l_bomRevDate, l_line_id, l_alt_bom, l_alt_rtg
1047 from wip_flow_schedules
1048 where wip_entity_id = p_wipEntityID;
1049 end if;
1050
1051
1052 l_compTbl := system.wip_component_tbl_t();
1053 -- explode the bom and do the default for the supply subinv and locator
1054 --commented for flow execution - component and detail merging
1055
1056 wip_flowUtil_priv.explodeRequirementsAndDefault(
1057 p_assyID => p_assyID,
1058 p_orgID => p_orgID,
1059 p_qty => p_qty,
1060 p_altBomDesig => null,
1061 p_altOption => 2,
1062 p_bomRevDate => l_bomRevDate,
1063 p_txnDate => p_txnDate,
1064 p_implFlag => 1,
1065 p_projectID => p_projectID,
1066 p_taskID => p_taskID,
1067 p_toOpSeqNum => p_toOpSeqNum,
1068 p_altRoutDesig => null,
1069 p_txnFlag => true, -- fix for bug4538135 - ER 4369064
1070 p_defaultPushSubinv => 'Y', --fox for bug 5358603
1071 x_compTbl => l_compTbl,
1072 x_returnStatus => x_returnStatus);
1073 if ( x_returnStatus <> fnd_api.g_ret_sts_success ) then
1074 raise fnd_api.g_exc_unexpected_error;
1075 end if;
1076
1077 if (l_logLevel <= wip_constants.full_logging) then
1078 wip_logger.log('Done with explosion of components',l_returnStatus);
1079 end if;
1080
1081 -- fetch flow schedule's information into wfs_info
1082 for c_wfs_info in wfs_info_cursor(p_wipEntityID) loop
1083 l_wfs_info := c_wfs_info;
1084 end loop;
1085
1086 -- filter out unwanted components
1087 l_count := l_compTbl.first;
1088 while (l_count is not null) loop
1089 -- bug 5630078
1090 -- we dont insert any component that is not transaction_enabled
1091 if ((nvl(l_compTbl(l_count).wip_supply_type, -1) <> 6) and
1092 (l_compTbl(l_count).mtl_transactions_enabled_flag <> 'Y')) then
1093 l_compTbl.delete(l_count);
1094 if (l_logLevel <= wip_constants.full_logging) then
1095 wip_logger.log('Removed non txn enabled component: '+l_compTbl(l_count).item_name, l_returnStatus);
1096 end if;
1097 goto continue_loop;
1098 end if;
1099
1100 -- bug 5605598: filter out lot-based components appropriately
1101 if (nvl(l_compTbl(l_count).basis_type,WIP_CONSTANTS.ITEM_BASED_MTL) = WIP_CONSTANTS.LOT_BASED_MTL) then
1102 if (
1103 not(
1104 (l_wfs_info.quantity_completed = 0 and l_wfs_info.quantity_scrapped <= 0 and p_qty > 0) or
1105 (l_wfs_info.quantity_completed + l_wfs_info.quantity_scrapped > 0 and
1106 l_wfs_info.quantity_completed + l_wfs_info.quantity_scrapped + p_qty <= 0)
1107 )
1108 ) then
1109 -- remove the component it it's not the 1st complete/scrap or the last return/return-from-scrap
1110 l_compTbl.delete(l_count);
1111 goto continue_loop;
1112 end if;
1113 end if;
1114
1115 <<continue_loop>>
1116 l_count := l_compTbl.next(l_count);
1117 end loop;
1118
1119 x_compInfo := system.wip_lot_serial_obj_t(null, null, null, l_compTbl, null, null);
1120 x_compInfo.initialize;
1121
1122 wip_autoLotProc_priv.deriveLots(x_compLots => x_compInfo,
1123 p_orgID => p_orgID,
1124 p_wipEntityID => p_wipEntityID,
1125 p_initMsgList => fnd_api.g_false,
1126 p_endDebug => fnd_api.g_true,
1127 p_destroyTrees => fnd_api.g_true,
1128 p_treeMode => inv_quantity_tree_pvt.g_reservation_mode,
1129 p_treeSrcName => null,
1130 x_returnStatus => x_returnStatus);
1131 if ( x_returnStatus = fnd_api.g_ret_sts_unexp_error ) then
1132 raise fnd_api.g_exc_unexpected_error;
1133 end if;
1134
1135 if (l_logLevel <= wip_constants.full_logging) then
1136 wip_logger.log('Done with lot derivation',l_returnStatus);
1137 end if;
1138
1139 --now that we have exploded components and derived lots
1140 --merge these with recorded details
1141 flm_execution_util.get_backflush_comps(
1142 p_wip_ent_id => p_wipEntityID,
1143 p_line_id => l_line_id,
1144 p_assyID => p_assyID,
1145 p_orgID => p_orgID,
1146 p_qty => p_qty,
1147 p_altBomDesig => l_alt_bom,
1148 p_altOption => 2,
1149 p_bomRevDate => l_bomRevDate,
1150 p_txnDate => p_txnDate,
1151 p_projectID => p_projectID,
1152 p_taskID => p_taskID,
1153 p_toOpSeqNum => p_toOpSeqNum,
1154 p_altRoutDesig => l_alt_rtg,
1155 x_compInfo => x_compInfo,
1156 x_returnStatus => x_returnStatus);
1157
1158 if (l_logLevel <= wip_constants.trace_logging) then
1159 wip_logger.exitPoint(p_procName => 'wma_flow.explodeBOMAndDerive',
1160 p_procReturnStatus => x_returnStatus,
1161 p_msg => 'success',
1162 x_returnStatus => l_returnStatus);
1163 end if;
1164
1165 exception
1166 when fnd_api.g_exc_unexpected_error then
1167 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
1168 x_errMessage := fnd_msg_pub.get(p_encoded => 'F');
1169 if (l_logLevel <= wip_constants.trace_logging) then
1170 wip_logger.exitPoint(p_procName => 'wma_flow.explodeBOMAndDerive',
1171 p_procReturnStatus => x_returnStatus,
1172 p_msg => x_errMessage,
1173 x_returnStatus => l_returnStatus);
1174 end if;
1175 rollback;
1176
1177
1178 when others then
1179 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
1180 x_errMessage := SQLERRM;
1181 if (l_logLevel <= wip_constants.trace_logging) then
1182 wip_logger.exitPoint(p_procName => 'wma_flow.explodeBOMAndDerive',
1183 p_procReturnStatus => x_returnStatus,
1184 p_msg => x_errMessage,
1185 x_returnStatus => l_returnStatus);
1186 end if;
1187 rollback;
1188 end explodeBOMAndDerive;
1189
1190 END wma_flow;