[Home] [Help]
1 PACKAGE BODY WSM_Serial_support_GRP AS
2 /* $Header: WSMGSERB.pls 120.6 2012/01/13 12:42:48 akuppa ship $ */
3
4 g_log_level_unexpected NUMBER := FND_LOG.LEVEL_UNEXPECTED ;
5 g_log_level_error number := FND_LOG.LEVEL_ERROR ;
6 g_log_level_exception number := FND_LOG.LEVEL_EXCEPTION ;
7 g_log_level_event number := FND_LOG.LEVEL_EVENT ;
8 g_log_level_procedure number := FND_LOG.LEVEL_PROCEDURE ;
9 g_log_level_statement number := FND_LOG.LEVEL_STATEMENT ;
10
11 g_msg_lvl_unexp_error NUMBER := FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ;
12 g_msg_lvl_error NUMBER := FND_MSG_PUB.G_MSG_LVL_ERROR ;
13 g_msg_lvl_success NUMBER := FND_MSG_PUB.G_MSG_LVL_SUCCESS ;
14 g_msg_lvl_debug_high NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH ;
15 g_msg_lvl_debug_medium NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM ;
16 g_msg_lvl_debug_low NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW ;
17
18 g_ret_success varchar2(1) := FND_API.G_RET_STS_SUCCESS;
19 g_ret_error varchar2(1) := FND_API.G_RET_STS_ERROR;
20 g_ret_unexpected varchar2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
21
22 -- This procedure will be invoked by the WIP move processor (for interface transactions)
23 -- If the transaction type is an undo/assembly return transaction populate_components will be invoked.
24 -- Or else the WIP API wip_movProc_grp.backflushIntoMTI will be invoked which would derive backflush components..
25
26 Procedure backflush_comp(p_wipEntityID IN NUMBER,
27 p_orgID IN NUMBER,
28 p_primaryQty IN NUMBER,
29 p_txnDate IN DATE,
30 p_txnHdrID IN NUMBER,
31 p_txnType IN NUMBER,
32 p_fmOp IN NUMBER,
33 p_fmStep IN NUMBER,
34 p_toOp IN NUMBER,
35 p_toStep IN NUMBER,
36 p_movTxnID IN NUMBER,
37 p_cplTxnID IN NUMBER:= NULL,
38 p_mtlTxnMode IN NUMBER,
39 p_reasonID IN NUMBER := NULL,
40 p_reference IN VARCHAR2 := NULL,
41 p_init_msg_list IN VARCHAR2,
42 x_lotSerRequired OUT NOCOPY NUMBER,
43 x_returnStatus OUT NOCOPY VARCHAR2,
44 x_error_msg OUT NOCOPY VARCHAR2,
45 x_error_count OUT NOCOPY NUMBER
46 )
47 IS
48
49 l_job_type NUMBER;
50
51 -- Logging variables.....
52 l_msg_tokens WSM_Log_PVT.token_rec_tbl;
53 l_log_level number := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
54
55 l_stmt_num NUMBER;
56 l_module VARCHAR2(100) := 'wsm.plsql.WSM_Serial_support_GRP.backflush_comp';
57 -- Logging variables....
58
59 BEGIN
60 l_stmt_num := 10;
61
62 x_returnStatus := G_RET_SUCCESS;
63 x_error_msg := NULL;
64 x_error_count := 0;
65
66 select entity_type
67 into l_job_type
68 from wip_entities WE
69 where WE.wip_entity_id = p_wipEntityID
70 and WE.organization_id = p_orgID;
71
72 l_stmt_num := 20;
73
74 -- if it is a Lot based job undo/return transaction then, call WSM API
75 if (l_job_type = 5) and
76 (p_txnType = 3 OR ( (p_fmOp > p_toOp)
77 OR
78 ( (p_fmOp = p_toOp) and (p_fmStep > p_toStep) )
79 )
80 )
81 then
82 l_stmt_num := 30;
83
84
85 IF (G_LOG_LEVEL_STATEMENT >= l_log_level) THEN
86 l_msg_tokens.delete;
87 WSM_log_PVT.logMessage (p_module_name => l_module ,
88 p_msg_text => 'Invoking populate_components',
89 p_stmt_num => l_stmt_num ,
90 p_msg_tokens => l_msg_tokens ,
91 p_fnd_log_level => G_LOG_LEVEL_STATEMENT ,
92 p_run_log_level => l_log_level
93 );
94 END IF;
95
96 populate_components(p_wip_entity_id => p_wipEntityID,
97 p_organization_id => p_orgId,
98 p_move_txn_id => p_movTxnID,
99 p_move_txn_type => p_txnType,
100 p_txn_date => p_txnDate,
101 p_mtl_txn_hdr_id => p_txnHdrID,
102 p_compl_txn_id => p_cplTxnID,
103 x_return_status => x_returnStatus ,
104 x_error_count => x_error_msg ,
105 x_error_msg => x_error_count
106 );
107
108 if x_returnStatus <> G_RET_SUCCESS then
109 IF x_returnStatus = G_RET_ERROR THEN
110 raise FND_API.G_EXC_ERROR;
111 ELSIF x_returnStatus = G_RET_UNEXPECTED THEN
112 raise FND_API.G_EXC_UNEXPECTED_ERROR;
113 END IF;
114 end if;
115 l_stmt_num := 40;
116 else
117
118 l_stmt_num := 50;
119
120 IF (G_LOG_LEVEL_STATEMENT >= l_log_level) THEN
121 l_msg_tokens.delete;
122 WSM_log_PVT.logMessage (p_module_name => l_module ,
123 p_msg_text => 'Invoking wip_movProc_grp.backflushIntoMTI',
124 p_stmt_num => l_stmt_num ,
125 p_msg_tokens => l_msg_tokens ,
126 p_fnd_log_level => G_LOG_LEVEL_STATEMENT ,
127 p_run_log_level => l_log_level
128 );
129 END IF;
130
131 -- else call WIP procedure.. (Please refer the WIP MED here )
132 wip_movProc_grp.backflushIntoMTI ( p_wipEntityID => p_wipEntityID ,
133 p_orgID => p_orgID ,
134 p_primaryQty => p_primaryQty ,
135 p_txnDate => p_txnDate ,
136 p_txnHdrID => p_txnHdrID ,
137 p_txnType => p_txnType ,
138 p_fmOp => p_fmOp ,
139 p_fmStep => p_fmStep ,
140 p_toOp => p_toOp ,
141 p_toStep => p_toStep ,
142 p_movTxnID => p_movTxnID ,
143 p_cplTxnID => p_cplTxnID ,
144 p_mtlTxnMode => p_mtlTxnMode ,
145 p_reasonID => p_reasonID ,
146 p_reference => p_reference ,
147 x_lotSerRequired => x_lotSerRequired ,
148 x_returnStatus => x_returnStatus
149 );
150
151 if x_returnStatus <> G_RET_SUCCESS then
152 IF x_returnStatus = G_RET_ERROR THEN
153 raise FND_API.G_EXC_ERROR;
154 ELSIF x_returnStatus = G_RET_UNEXPECTED THEN
155 raise FND_API.G_EXC_UNEXPECTED_ERROR;
156 END IF;
157 end if;
158
159 l_stmt_num := 60;
160 end if;
161 EXCEPTION
162
163 WHEN FND_API.G_EXC_ERROR THEN
164
165 x_returnStatus := G_RET_ERROR;
166 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,
167 p_count => x_error_count ,
168 p_data => x_error_msg
169 );
170
171 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
172
173 x_returnStatus := G_RET_UNEXPECTED;
174
175 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,
176 p_count => x_error_count ,
177 p_data => x_error_msg
178 );
179 WHEN OTHERS THEN
180
181 x_returnStatus := G_RET_UNEXPECTED;
182
183 IF (G_LOG_LEVEL_UNEXPECTED >= l_log_level) OR
184 (FND_MSG_PUB.check_msg_level(G_MSG_LVL_UNEXP_ERROR))
185 THEN
186 WSM_log_PVT.handle_others( p_module_name => l_module ,
187 p_stmt_num => l_stmt_num ,
188 p_fnd_log_level => G_LOG_LEVEL_UNEXPECTED ,
189 p_run_log_level => l_log_level
190 );
191 END IF;
192
193 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,
194 p_count => x_error_count ,
195 p_data => x_error_msg
196 );
197 END backflush_comp;
198
199 -- Populate MMTT,MTLT and MSNT for an assembly return/undo transaction for a lot based job
200 -- based on the previous move transaction records in MMT,MTL,MUT.
201
202 procedure populate_components(p_wip_entity_id IN NUMBER,
203 p_organization_id IN NUMBER,
204 p_move_txn_id IN NUMBER,
205 p_move_txn_type IN NUMBER,
206 p_txn_date IN DATE,
207 p_mtl_txn_hdr_id IN NUMBER,
208 p_compl_txn_id IN NUMBER,
209 x_return_status OUT NOCOPY VARCHAR2,
210 x_error_count OUT NOCOPY NUMBER,
211 x_error_msg OUT NOCOPY VARCHAR2
212 )
213
214
215 IS
216 l_user_id NUMBER := FND_GLOBAL.user_id;
217 l_login_id NUMBER := fnd_global.login_id;
218 l_req_id NUMBER := fnd_global.conc_request_id;
219
220 l_prog_appl_id NUMBER := fnd_global.prog_appl_id;
221 l_prog_id NUMBER := fnd_global.conc_program_id;
222
223 l_lot_controlled NUMBER;
224 l_serial_txn_id NUMBER;
225 l_scrap_txn_id NUMBER;
226 l_move_txn_id NUMBER;
227 l_acct_period_id NUMBER;
228 l_temp_id NUMBER;
229
230 cursor c_mmt_txn is
231 SELECT TRANSACTION_ID
232 FROM MTL_MATERIAL_TRANSACTIONS MMT
233 WHERE MMT.organization_id = p_organization_id
234 AND MMT.move_transaction_id IN (l_move_txn_id,l_scrap_txn_id)
235 AND MMT.transaction_source_id = p_wip_entity_id
236 AND MMT.subinventory_code IS NOT NULL
237 AND MMT.transaction_type_id IN (WIP_CONSTANTS.ISSCOMP_TYPE, -- 35 -- WIP Component Issue
238 WIP_CONSTANTS.ISSNEGC_TYPE -- 38 -- Negative component issue
239 );
240
241 -- Transaction types
242 -- ISSCOMP_TYPE CONSTANT NUMBER := 35; -- Components taken out of INV
243 -- BFLREPL_TYPE CONSTANT NUMBER := 51; -- Backflush replenishment
244 -- COSTUPD_TYPE CONSTANT NUMBER := 25; -- Cost update
245 -- RETCOMP_TYPE CONSTANT NUMBER := 43; -- Components put into INV
246 -- SCRASSY_TYPE CONSTANT NUMBER := 90; -- Assembly scrap
247 -- CPLASSY_TYPE CONSTANT NUMBER := 44; -- Assemblies put into INV
248 -- RETASSY_TYPE CONSTANT NUMBER := 17; -- Assemblies taken out of INV
249 -- ISSNEGC_TYPE CONSTANT NUMBER := 38; -- Negative component issue
250 -- RETNEGC_TYPE CONSTANT NUMBER := 48; -- Negative component return
251 --Bug 5614015: added rowid in the cursor c_lot_txn
252 cursor c_lot_txn(v_txn_id IN NUMBER) IS
253 SELECT TRANSACTION_ID,
254 SERIAL_TRANSACTION_ID,
255 LOT_NUMBER,
256 ROWID
257 from mtl_transaction_lot_numbers MTLN
258 where MTLN.organization_id = p_organization_id
259 and MTLN.transaction_id = v_txn_id;
260
261 l_open_past_period BOOLEAN := false;
262
263 -- Logging variables.....
264 l_msg_tokens WSM_Log_PVT.token_rec_tbl;
265 l_log_level number := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
266
267 l_stmt_num NUMBER;
268 l_module VARCHAR2(100) := 'wsm.plsql.WSM_SERIAL_SUPPORT_GRP.populate_components';
269 l_param_tbl WSM_Log_PVT.param_tbl_type;
270 -- Logging variables...
271
272 BEGIN
273 l_stmt_num := 10;
274
275 x_return_status := G_RET_SUCCESS;
276 x_error_msg := NULL;
277 x_error_count := 0;
278
279 IF (G_LOG_LEVEL_PROCEDURE >= l_log_level) THEN
280 l_param_tbl.delete;
281 l_param_tbl(1).paramName := 'p_move_txn_id';
282 l_param_tbl(1).paramValue := p_move_txn_id;
283
284 l_param_tbl(2).paramName := 'p_mtl_txn_hdr_id';
285 l_param_tbl(2).paramValue := p_mtl_txn_hdr_id;
286
287 l_param_tbl(3).paramName := 'p_organization_id';
288 l_param_tbl(3).paramValue := p_organization_id;
289
290 l_param_tbl(4).paramName := 'p_wip_entity_id';
291 l_param_tbl(4).paramValue := p_wip_entity_id;
292
293 WSM_Log_PVT.logProcParams(p_module_name => 'wsm.plsql.WSM_SERIAL_SUPPORT_GRP.populate_components',
294 p_param_tbl => l_param_tbl,
295 p_fnd_log_level => l_log_level
296 );
297 END IF;
298
299 l_stmt_num := 20;
300
301 invttmtx.tdatechk(org_id => p_organization_id,
302 transaction_date => p_txn_date,
303 period_id => l_acct_period_id,
304 open_past_period => l_open_past_period
305 );
306
307
308 if(l_acct_period_id is null or
309 l_acct_period_id <= 0)
310 then
311 l_stmt_num := 30;
312
313 IF g_log_level_error >= l_log_level OR
314 FND_MSG_PUB.check_msg_level(G_MSG_LVL_ERROR)
315 THEN
316
317 l_msg_tokens.delete;
318 WSM_log_PVT.logMessage(p_module_name => l_module ,
319 p_msg_name => 'INV_NO_OPEN_PERIOD' ,
320 p_msg_appl_name => 'INV' ,
321 p_msg_tokens => l_msg_tokens ,
322 p_stmt_num => l_stmt_num ,
323 p_fnd_msg_level => G_MSG_LVL_ERROR ,
324 p_fnd_log_level => G_LOG_LEVEL_ERROR ,
325 p_run_log_level => l_log_level
326 );
327 END IF;
328 RAISE FND_API.G_EXC_ERROR;
329
330 end if;
331
332 -- get a txn header id..
333 -- No will be passed...
334 /*SELECT mtl_material_transactions_s.nextval
335 into p_mtl_txn_hdr_id
336 from dual;
337 */
338
339 /* Have to be passed....
340 if p_move_txn_type = wip_constants.RET_TXN then
341 SELECT mtl_material_transactions_s.nextval
342 into p_compl_txn_id
343 from dual;
344 end if;
345 */
346
347 -- could be a move and scrap transaction too...
348 -- get the move transaction id alone...
349 l_stmt_num := 40;
350
351 --Bug 5215899: Handle null value for batch_id.
352 select max(wmt.transaction_id)
353 into l_move_txn_id
354 from wip_move_transactions wmt
355 where wmt.organization_id = p_organization_id
356 and wmt.wip_entity_id = p_wip_entity_id
357 and wmt.wsm_undo_txn_id IS NULL
358 and wmt.transaction_id = nvl(wmt.batch_id,wmt.transaction_id)
359 and wmt.transaction_id <> p_move_txn_id;
360
361 begin
362 select wmt.transaction_id
363 into l_scrap_txn_id
364 from wip_move_transactions wmt
365 where wmt.organization_id = p_organization_id
366 and wmt.wip_entity_id = p_wip_entity_id
367 and wmt.wsm_undo_txn_id IS NULL
368 and wmt.transaction_id <> p_move_txn_id
369 and wmt.transaction_id <> wmt.batch_id
370 and wmt.batch_id = l_move_txn_id;
371 exception
372 when no_data_found then
373 null;
374 end;
375
376 l_stmt_num := 50;
377
378 IF (G_LOG_LEVEL_STATEMENT >= l_log_level) THEN
379 l_msg_tokens.delete;
380 WSM_log_PVT.logMessage (p_module_name => l_module ,
381 p_msg_text => 'Old Transaction Values : Move Transaction ID : ' || l_move_txn_id || ' Scrap Transaction ID : ' || l_scrap_txn_id,
382 p_stmt_num => l_stmt_num ,
383 p_msg_tokens => l_msg_tokens ,
384 p_fnd_log_level => G_LOG_LEVEL_STATEMENT ,
385 p_run_log_level => l_log_level
386 );
387 END IF;
388
389 for l_mmt_rec in c_mmt_txn loop
390
391 l_stmt_num := 60;
392 select mtl_material_transactions_s.nextval
393 into l_temp_id
394 from dual;
395
396 IF (G_LOG_LEVEL_STATEMENT >= l_log_level) THEN
397 l_msg_tokens.delete;
398 WSM_log_PVT.logMessage (p_module_name => l_module ,
399 p_msg_text => 'MMTT Transaction ID : ' || l_temp_id,
400 p_stmt_num => l_stmt_num ,
401 p_msg_tokens => l_msg_tokens ,
402 p_fnd_log_level => G_LOG_LEVEL_STATEMENT ,
403 p_run_log_level => l_log_level
404 );
405 END IF;
406
407 l_stmt_num := 70;
408 -- Insert into MMTT
409 INSERT INTO mtl_material_transactions_temp
410 (
411 TRANSACTION_HEADER_ID,
412 TRANSACTION_TEMP_ID,
413 SOURCE_CODE,
414 SOURCE_LINE_ID,
415 PROCESS_FLAG,
416 POSTING_FLAG,
417 --WIP_COMMIT_FLAG,
418 CREATION_DATE,
419 CREATED_BY,
420 LAST_UPDATE_DATE,
421 LAST_UPDATED_BY,
422 LAST_UPDATE_LOGIN,
423 PROGRAM_ID,
424 PROGRAM_UPDATE_DATE,
425 PROGRAM_APPLICATION_ID,
426 REQUEST_ID,
427 ORGANIZATION_ID,
428 SUBINVENTORY_CODE,
429 LOCATOR_ID,
430 INVENTORY_ITEM_ID,
431 REVISION,
432 TRANSACTION_TYPE_ID,
433 TRANSACTION_ACTION_ID,
434 TRANSACTION_SOURCE_TYPE_ID,
435 TRANSACTION_SOURCE_ID,
436 TRANSACTION_SOURCE_NAME,
437 TRANSACTION_REFERENCE,
438 REASON_ID,
439 TRANSACTION_DATE,
440 ACCT_PERIOD_ID,
441 TRANSACTION_QUANTITY,
442 TRANSACTION_UOM,
443 PRIMARY_QUANTITY,
444 OPERATION_SEQ_NUM,
445 DEPARTMENT_ID,
446 EMPLOYEE_CODE,
447 WIP_ENTITY_TYPE,
448 COMPLETION_TRANSACTION_ID,
449 /*
450 Bug 12429485. Don't Copy the DFF Attributes from previous transaction.
451 ATTRIBUTE_CATEGORY,
452 ATTRIBUTE1,
453 ATTRIBUTE2,
454 ATTRIBUTE3,
455 ATTRIBUTE4,
456 ATTRIBUTE5,
457 ATTRIBUTE6,
458 ATTRIBUTE7,
459 ATTRIBUTE8,
460 ATTRIBUTE9,
461 ATTRIBUTE10,
462 ATTRIBUTE11,
463 ATTRIBUTE12,
464 ATTRIBUTE13,
465 ATTRIBUTE14,
466 ATTRIBUTE15,
467 */
468 MOVEMENT_ID,
469 SOURCE_PROJECT_ID,
470 SOURCE_TASK_ID,
471 PROJECT_ID,
472 TASK_ID,
473 MOVE_TRANSACTION_ID --Bug 5207917
474 )
475 SELECT
476 p_mtl_txn_hdr_id,
477 l_temp_id,
478 MMT.SOURCE_CODE,
479 MMT.SOURCE_LINE_ID,
480 'Y', -- PROCESS_FLAG,
481 'Y',-- POSTING_FLAG,
482 --'N',-- WIP_COMMIT_FLAG,
483 sysdate,
484 l_user_id,
485 sysdate,
486 l_user_id,
487 l_login_id,
488 l_prog_id,
489 sysdate,
490 l_prog_appl_id,
491 l_req_id,
492 MMT.ORGANIZATION_ID,
493 MMT.SUBINVENTORY_CODE,
494 MMT.LOCATOR_ID,
495 MMT.INVENTORY_ITEM_ID,
496 MMT.REVISION,
497 decode(MMT.TRANSACTION_ACTION_ID,wip_constants.issnegc_action,
498 wip_constants.retnegc_type,
499 wip_constants.retcomp_type
500
501 ),
502 decode(MMT.TRANSACTION_ACTION_ID,wip_constants.issnegc_action,
503 wip_constants.retnegc_action,
504 wip_constants.retcomp_action
505
506 ),
507 MMT.TRANSACTION_SOURCE_TYPE_ID,
508 MMT.TRANSACTION_SOURCE_ID,
509 MMT.TRANSACTION_SOURCE_NAME,
510 MMT.TRANSACTION_REFERENCE,
511 MMT.REASON_ID,
512 p_txn_date, -- transaction date
513 l_acct_period_id, -- accout period id...
514 -1 * MMT.TRANSACTION_QUANTITY,
515 MMT.TRANSACTION_UOM,
516 -1 * MMT.PRIMARY_QUANTITY,
517 MMT.OPERATION_SEQ_NUM,
518 MMT.DEPARTMENT_ID,
519 MMT.EMPLOYEE_CODE,
520 5, --MMT.WIP_ENTITY_TYPE,
521 p_compl_txn_id,
522 /*
523 Bug 12429485. Don't Copy the DFF Attributes from previous transaction.
524 MMT.ATTRIBUTE_CATEGORY,
525 MMT.ATTRIBUTE1,
526 MMT.ATTRIBUTE2,
527 MMT.ATTRIBUTE3,
528 MMT.ATTRIBUTE4,
529 MMT.ATTRIBUTE5,
530 MMT.ATTRIBUTE6,
531 MMT.ATTRIBUTE7,
532 MMT.ATTRIBUTE8,
533 MMT.ATTRIBUTE9,
534 MMT.ATTRIBUTE10,
535 MMT.ATTRIBUTE11,
536 MMT.ATTRIBUTE12,
537 MMT.ATTRIBUTE13,
538 MMT.ATTRIBUTE14,
539 MMT.ATTRIBUTE15,
540 */
541 MMT.MOVEMENT_ID,
542 MMT.SOURCE_PROJECT_ID,
543 MMT.SOURCE_TASK_ID,
544 MMT.PROJECT_ID,
545 MMT.TASK_ID,
546 p_move_txn_id --Bug 5207917
547 FROM MTL_MATERIAL_TRANSACTIONS MMT
548 WHERE TRANSACTION_ID = l_mmt_rec.transaction_id;
549
550 IF (G_LOG_LEVEL_STATEMENT >= l_log_level) THEN
551 l_msg_tokens.delete;
552 WSM_log_PVT.logMessage (p_module_name => l_module ,
553 p_msg_text => 'Inserted : ' || SQL%ROWCOUNT || ' rows into MMTT',
554 p_stmt_num => l_stmt_num ,
555 p_msg_tokens => l_msg_tokens ,
556 p_fnd_log_level => G_LOG_LEVEL_STATEMENT ,
557 p_run_log_level => l_log_level
558 );
559 END IF;
560
561 l_stmt_num := 80;
562 l_lot_controlled := 0;
563 -- Now obtain the the Lot
564 for l_lot_txn_rec in c_lot_txn(l_mmt_rec.transaction_id) loop
565
566 l_stmt_num := 90;
567
568 l_lot_controlled := 1;
569 if l_lot_txn_rec.serial_transaction_id is not null then
570 l_stmt_num := 100;
571 select mtl_material_transactions_s.nextval
572 into l_serial_txn_id
573 from dual;
574 else
575 l_stmt_num := 110;
576 l_serial_txn_id := null;
577 end if;
578
579 l_stmt_num := 120;
580 insert into mtl_transaction_lots_temp
581 (
582 TRANSACTION_TEMP_ID ,
583 LAST_UPDATE_DATE ,
584 LAST_UPDATED_BY ,
585 CREATION_DATE ,
586 CREATED_BY ,
587 LAST_UPDATE_LOGIN ,
588 REQUEST_ID ,
589 PROGRAM_APPLICATION_ID ,
590 PROGRAM_ID ,
591 PROGRAM_UPDATE_DATE ,
592 TRANSACTION_QUANTITY ,
593 PRIMARY_QUANTITY ,
594 LOT_NUMBER ,
595 SERIAL_TRANSACTION_TEMP_ID ,
596 DESCRIPTION ,
597 VENDOR_NAME ,
598 SUPPLIER_LOT_NUMBER ,
599 ORIGINATION_DATE ,
600 DATE_CODE ,
601 GRADE_CODE ,
602 CHANGE_DATE ,
603 MATURITY_DATE ,
604 STATUS_ID ,
605 RETEST_DATE ,
606 AGE ,
607 ITEM_SIZE ,
608 COLOR ,
609 VOLUME ,
610 VOLUME_UOM ,
611 PLACE_OF_ORIGIN ,
612 BEST_BY_DATE ,
613 LENGTH ,
614 LENGTH_UOM ,
615 RECYCLED_CONTENT ,
616 THICKNESS ,
617 THICKNESS_UOM ,
618 WIDTH ,
619 WIDTH_UOM ,
620 CURL_WRINKLE_FOLD ,
621 LOT_ATTRIBUTE_CATEGORY ,
622 C_ATTRIBUTE1 ,
623 C_ATTRIBUTE2 ,
624 C_ATTRIBUTE3 ,
625 C_ATTRIBUTE4 ,
626 C_ATTRIBUTE5 ,
627 C_ATTRIBUTE6 ,
628 C_ATTRIBUTE7 ,
629 C_ATTRIBUTE8 ,
630 C_ATTRIBUTE9 ,
631 C_ATTRIBUTE10 ,
632 C_ATTRIBUTE11 ,
633 C_ATTRIBUTE12 ,
634 C_ATTRIBUTE13 ,
635 C_ATTRIBUTE14 ,
636 C_ATTRIBUTE15 ,
637 C_ATTRIBUTE16 ,
638 C_ATTRIBUTE17 ,
639 C_ATTRIBUTE18 ,
640 C_ATTRIBUTE19 ,
641 C_ATTRIBUTE20 ,
642 D_ATTRIBUTE1 ,
643 D_ATTRIBUTE2 ,
644 D_ATTRIBUTE3 ,
645 D_ATTRIBUTE4 ,
646 D_ATTRIBUTE5 ,
647 D_ATTRIBUTE6 ,
648 D_ATTRIBUTE7 ,
649 D_ATTRIBUTE8 ,
650 D_ATTRIBUTE9 ,
651 D_ATTRIBUTE10 ,
652 N_ATTRIBUTE1 ,
653 N_ATTRIBUTE2 ,
654 N_ATTRIBUTE3 ,
655 N_ATTRIBUTE4 ,
656 N_ATTRIBUTE5 ,
657 N_ATTRIBUTE6 ,
658 N_ATTRIBUTE7 ,
659 N_ATTRIBUTE8 ,
660 N_ATTRIBUTE9 ,
661 N_ATTRIBUTE10 ,
662 VENDOR_ID ,
663 TERRITORY_CODE ,
664 PRODUCT_CODE ,
665 PRODUCT_TRANSACTION_ID ,
666 ATTRIBUTE_CATEGORY ,
667 ATTRIBUTE1 ,
668 ATTRIBUTE2 ,
669 ATTRIBUTE3 ,
670 ATTRIBUTE4 ,
671 ATTRIBUTE5 ,
672 ATTRIBUTE6 ,
673 ATTRIBUTE7 ,
674 ATTRIBUTE8 ,
675 ATTRIBUTE9 ,
676 ATTRIBUTE10 ,
677 ATTRIBUTE11 ,
678 ATTRIBUTE12 ,
679 ATTRIBUTE13 ,
680 ATTRIBUTE14 ,
681 ATTRIBUTE15 --,
682 )
683 SELECT
684 l_temp_id ,
685 sysdate ,
686 l_user_id ,
687 sysdate ,
688 l_user_id ,
689 l_login_id ,
690 l_req_id ,
691 l_prog_appl_id ,
692 l_prog_id ,
693 sysdate ,
694 -1 * TRANSACTION_QUANTITY ,
695 -1 * PRIMARY_QUANTITY ,
696 LOT_NUMBER ,
697 l_serial_txn_id ,
698 DESCRIPTION ,
699 VENDOR_NAME ,
700 SUPPLIER_LOT_NUMBER ,
701 ORIGINATION_DATE ,
702 DATE_CODE ,
703 GRADE_CODE ,
704 CHANGE_DATE ,
705 MATURITY_DATE ,
706 STATUS_ID ,
707 RETEST_DATE ,
708 AGE ,
709 ITEM_SIZE ,
710 COLOR ,
711 VOLUME ,
712 VOLUME_UOM ,
713 PLACE_OF_ORIGIN ,
714 BEST_BY_DATE ,
715 LENGTH ,
716 LENGTH_UOM ,
717 RECYCLED_CONTENT ,
718 THICKNESS ,
719 THICKNESS_UOM ,
720 WIDTH ,
721 WIDTH_UOM ,
722 CURL_WRINKLE_FOLD ,
723 LOT_ATTRIBUTE_CATEGORY ,
724 C_ATTRIBUTE1 ,
725 C_ATTRIBUTE2 ,
726 C_ATTRIBUTE3 ,
727 C_ATTRIBUTE4 ,
728 C_ATTRIBUTE5 ,
729 C_ATTRIBUTE6 ,
730 C_ATTRIBUTE7 ,
731 C_ATTRIBUTE8 ,
732 C_ATTRIBUTE9 ,
733 C_ATTRIBUTE10 ,
734 C_ATTRIBUTE11 ,
735 C_ATTRIBUTE12 ,
736 C_ATTRIBUTE13 ,
737 C_ATTRIBUTE14 ,
738 C_ATTRIBUTE15 ,
739 C_ATTRIBUTE16 ,
740 C_ATTRIBUTE17 ,
741 C_ATTRIBUTE18 ,
742 C_ATTRIBUTE19 ,
743 C_ATTRIBUTE20 ,
744 D_ATTRIBUTE1 ,
745 D_ATTRIBUTE2 ,
746 D_ATTRIBUTE3 ,
747 D_ATTRIBUTE4 ,
748 D_ATTRIBUTE5 ,
749 D_ATTRIBUTE6 ,
750 D_ATTRIBUTE7 ,
751 D_ATTRIBUTE8 ,
752 D_ATTRIBUTE9 ,
753 D_ATTRIBUTE10 ,
754 N_ATTRIBUTE1 ,
755 N_ATTRIBUTE2 ,
756 N_ATTRIBUTE3 ,
757 N_ATTRIBUTE4 ,
758 N_ATTRIBUTE5 ,
759 N_ATTRIBUTE6 ,
760 N_ATTRIBUTE7 ,
761 N_ATTRIBUTE8 ,
762 N_ATTRIBUTE9 ,
763 N_ATTRIBUTE10 ,
764 VENDOR_ID ,
765 TERRITORY_CODE ,
766 PRODUCT_CODE ,
767 PRODUCT_TRANSACTION_ID ,
768 ATTRIBUTE_CATEGORY ,
769 ATTRIBUTE1 ,
770 ATTRIBUTE2 ,
771 ATTRIBUTE3 ,
772 ATTRIBUTE4 ,
773 ATTRIBUTE5 ,
774 ATTRIBUTE6 ,
775 ATTRIBUTE7 ,
776 ATTRIBUTE8 ,
777 ATTRIBUTE9 ,
778 ATTRIBUTE10 ,
779 ATTRIBUTE11 ,
780 ATTRIBUTE12 ,
781 ATTRIBUTE13 ,
782 ATTRIBUTE14 ,
783 ATTRIBUTE15 --,
784 --PARENT_LOT_NUMBER
785 from MTL_TRANSACTION_LOT_NUMBERS
786 --Bug 5614015:Rowid is used to uniquely identify the lot selected
787 --where transaction_id = l_mmt_rec.transaction_id
788 --and lot_number = l_lot_txn_rec.lot_number;
789 where rowid = l_lot_txn_rec.rowid;
790
791 IF (G_LOG_LEVEL_STATEMENT >= l_log_level) THEN
792 l_msg_tokens.delete;
793 WSM_log_PVT.logMessage (p_module_name => l_module ,
794 p_msg_text => 'Inserted : ' || SQL%ROWCOUNT
795 || ' rows into MTLT for lot '
796 || l_lot_txn_rec.lot_number,
797 p_stmt_num => l_stmt_num ,
798 p_msg_tokens => l_msg_tokens ,
799 p_fnd_log_level => G_LOG_LEVEL_STATEMENT ,
800 p_run_log_level => l_log_level
801 );
802 END IF;
803
804 l_stmt_num := 130;
805 if nvl(l_serial_txn_id,0) <> 0 then
806
807 l_stmt_num := 140;
808 insert into mtl_serial_numbers_temp
809 (
810 TRANSACTION_TEMP_ID ,
811 LAST_UPDATE_DATE ,
812 LAST_UPDATED_BY ,
813 CREATION_DATE ,
814 CREATED_BY ,
815 LAST_UPDATE_LOGIN ,
816 REQUEST_ID ,
817 PROGRAM_APPLICATION_ID ,
818 PROGRAM_ID ,
819 PROGRAM_UPDATE_DATE ,
820 --VENDOR_SERIAL_NUMBER ,
821 --VENDOR_LOT_NUMBER ,
822 FM_SERIAL_NUMBER ,
823 TO_SERIAL_NUMBER ,
824 --SERIAL_PREFIX ,
825 --ERROR_CODE ,
826 --GROUP_HEADER_ID ,
827 PARENT_SERIAL_NUMBER ,
828 SERIAL_ATTRIBUTE_CATEGORY ,
829 ORIGINATION_DATE ,
830 C_ATTRIBUTE1 ,
831 C_ATTRIBUTE2 ,
832 C_ATTRIBUTE3 ,
833 C_ATTRIBUTE4 ,
834 C_ATTRIBUTE5 ,
835 C_ATTRIBUTE6 ,
836 C_ATTRIBUTE7 ,
837 C_ATTRIBUTE8 ,
838 C_ATTRIBUTE9 ,
839 C_ATTRIBUTE10 ,
840 C_ATTRIBUTE11 ,
841 C_ATTRIBUTE12 ,
842 C_ATTRIBUTE13 ,
843 C_ATTRIBUTE14 ,
844 C_ATTRIBUTE15 ,
845 C_ATTRIBUTE16 ,
846 C_ATTRIBUTE17 ,
847 C_ATTRIBUTE18 ,
848 C_ATTRIBUTE19 ,
849 C_ATTRIBUTE20 ,
850 D_ATTRIBUTE1 ,
851 D_ATTRIBUTE2 ,
852 D_ATTRIBUTE3 ,
853 D_ATTRIBUTE4 ,
854 D_ATTRIBUTE5 ,
855 D_ATTRIBUTE6 ,
856 D_ATTRIBUTE7 ,
857 D_ATTRIBUTE8 ,
858 D_ATTRIBUTE9 ,
859 D_ATTRIBUTE10 ,
860 N_ATTRIBUTE1 ,
861 N_ATTRIBUTE2 ,
862 N_ATTRIBUTE3 ,
863 N_ATTRIBUTE4 ,
864 N_ATTRIBUTE5 ,
865 N_ATTRIBUTE6 ,
866 N_ATTRIBUTE7 ,
867 N_ATTRIBUTE8 ,
868 N_ATTRIBUTE9 ,
869 N_ATTRIBUTE10 ,
870 STATUS_ID ,
871 TERRITORY_CODE ,
872 TIME_SINCE_NEW ,
873 CYCLES_SINCE_NEW ,
874 TIME_SINCE_OVERHAUL ,
875 CYCLES_SINCE_OVERHAUL ,
876 TIME_SINCE_REPAIR ,
877 CYCLES_SINCE_REPAIR ,
878 TIME_SINCE_VISIT ,
879 CYCLES_SINCE_VISIT ,
880 TIME_SINCE_MARK ,
881 CYCLES_SINCE_MARK ,
882 NUMBER_OF_REPAIRS ,
883 PRODUCT_CODE ,
884 PRODUCT_TRANSACTION_ID ,
885 ATTRIBUTE_CATEGORY ,
886 ATTRIBUTE1 ,
887 ATTRIBUTE2 ,
888 ATTRIBUTE3 ,
889 ATTRIBUTE4 ,
890 ATTRIBUTE5 ,
891 ATTRIBUTE6 ,
892 ATTRIBUTE7 ,
893 ATTRIBUTE8 ,
894 ATTRIBUTE9 ,
895 ATTRIBUTE10 ,
896 ATTRIBUTE11 ,
897 ATTRIBUTE12 ,
898 ATTRIBUTE13 ,
899 ATTRIBUTE14 ,
900 ATTRIBUTE15 ,
901 DFF_UPDATED_FLAG
902 --PARENT_LOT_NUMBER
903 )
904 select
905 l_serial_txn_id ,
906 sysdate ,
907 l_user_id ,
908 sysdate ,
909 l_user_id ,
910 l_login_id ,
911 l_req_id ,
912 l_prog_appl_id ,
913 l_prog_id ,
914 sysdate ,
915 --VENDOR_SERIAL_NUMBER ,
916 --VENDOR_LOT_NUMBER ,
917 MUT.SERIAL_NUMBER ,
918 MUT.SERIAL_NUMBER ,
919 --SERIAL_PREFIX ,
920 --ERROR_CODE ,
921 --GROUP_HEADER_ID ,
922 MSN.PARENT_SERIAL_NUMBER ,
923 --END_ITEM_UNIT_NUMBER ,
924 MUT.SERIAL_ATTRIBUTE_CATEGORY ,
925 MUT.ORIGINATION_DATE ,
926 MUT.C_ATTRIBUTE1 ,
927 MUT.C_ATTRIBUTE2 ,
928 MUT.C_ATTRIBUTE3 ,
929 MUT.C_ATTRIBUTE4 ,
930 MUT.C_ATTRIBUTE5 ,
931 MUT.C_ATTRIBUTE6 ,
932 MUT.C_ATTRIBUTE7 ,
933 MUT.C_ATTRIBUTE8 ,
934 MUT.C_ATTRIBUTE9 ,
935 MUT.C_ATTRIBUTE10 ,
936 MUT.C_ATTRIBUTE11 ,
937 MUT.C_ATTRIBUTE12 ,
938 MUT.C_ATTRIBUTE13 ,
939 MUT.C_ATTRIBUTE14 ,
940 MUT.C_ATTRIBUTE15 ,
941 MUT.C_ATTRIBUTE16 ,
942 MUT.C_ATTRIBUTE17 ,
943 MUT.C_ATTRIBUTE18 ,
944 MUT.C_ATTRIBUTE19 ,
945 MUT.C_ATTRIBUTE20 ,
946 MUT.D_ATTRIBUTE1 ,
947 MUT.D_ATTRIBUTE2 ,
948 MUT.D_ATTRIBUTE3 ,
949 MUT.D_ATTRIBUTE4 ,
950 MUT.D_ATTRIBUTE5 ,
951 MUT.D_ATTRIBUTE6 ,
952 MUT.D_ATTRIBUTE7 ,
953 MUT.D_ATTRIBUTE8 ,
954 MUT.D_ATTRIBUTE9 ,
955 MUT.D_ATTRIBUTE10 ,
956 MUT.N_ATTRIBUTE1 ,
957 MUT.N_ATTRIBUTE2 ,
958 MUT.N_ATTRIBUTE3 ,
959 MUT.N_ATTRIBUTE4 ,
960 MUT.N_ATTRIBUTE5 ,
961 MUT.N_ATTRIBUTE6 ,
962 MUT.N_ATTRIBUTE7 ,
963 MUT.N_ATTRIBUTE8 ,
964 MUT.N_ATTRIBUTE9 ,
965 MUT.N_ATTRIBUTE10 ,
966 MUT.STATUS_ID ,
967 MUT.TERRITORY_CODE ,
968 MUT.TIME_SINCE_NEW ,
969 MUT.CYCLES_SINCE_NEW ,
970 MUT.TIME_SINCE_OVERHAUL ,
971 MUT.CYCLES_SINCE_OVERHAUL ,
972 MUT.TIME_SINCE_REPAIR ,
973 MUT.CYCLES_SINCE_REPAIR ,
974 MUT.TIME_SINCE_VISIT ,
975 MUT.CYCLES_SINCE_VISIT ,
976 MUT.TIME_SINCE_MARK ,
977 MUT.CYCLES_SINCE_MARK ,
978 MUT.NUMBER_OF_REPAIRS ,
979 MUT.PRODUCT_CODE ,
980 MUT.PRODUCT_TRANSACTION_ID ,
981 MUT.ATTRIBUTE_CATEGORY ,
982 MUT.ATTRIBUTE1 ,
983 MUT.ATTRIBUTE2 ,
984 MUT.ATTRIBUTE3 ,
985 MUT.ATTRIBUTE4 ,
986 MUT.ATTRIBUTE5 ,
987 MUT.ATTRIBUTE6 ,
988 MUT.ATTRIBUTE7 ,
989 MUT.ATTRIBUTE8 ,
990 MUT.ATTRIBUTE9 ,
991 MUT.ATTRIBUTE10 ,
992 MUT.ATTRIBUTE11 ,
993 MUT.ATTRIBUTE12 ,
994 MUT.ATTRIBUTE13 ,
995 MUT.ATTRIBUTE14 ,
996 MUT.ATTRIBUTE15 ,
997 decode(nvl(MUT.ATTRIBUTE_CATEGORY,-1),-1,'N','Y')
998 from mtl_unit_transactions MUT,
999 mtl_serial_numbers MSN
1000 where MUT.transaction_id = l_lot_txn_rec.serial_transaction_id
1001 and MUT.serial_number = MSN.serial_number
1002 and MSN.current_organization_id = MUT.ORGANIZATION_ID
1003 and msn.last_transaction_id = l_mmt_rec.transaction_id; --l_lot_txn_rec.serial_transaction_id;
1004
1005 IF (G_LOG_LEVEL_STATEMENT >= l_log_level) THEN
1006 l_msg_tokens.delete;
1007 WSM_log_PVT.logMessage (p_module_name => l_module ,
1008 p_msg_text => 'Inserted : ' || SQL%ROWCOUNT
1009 || ' rows into MSTT(Serial)',
1010 p_stmt_num => l_stmt_num ,
1011 p_msg_tokens => l_msg_tokens ,
1012 p_fnd_log_level => G_LOG_LEVEL_STATEMENT ,
1013 p_run_log_level => l_log_level
1014 );
1015 END IF;
1016 end if;
1017
1018 end loop;
1019
1020 l_stmt_num := 150;
1021 if l_lot_controlled = 0 then
1022
1023 l_stmt_num := 160;
1024 -- insert the records for serial controlled assemblies...
1025 insert into mtl_serial_numbers_temp
1026 (
1027 TRANSACTION_TEMP_ID ,
1028 LAST_UPDATE_DATE ,
1029 LAST_UPDATED_BY ,
1030 CREATION_DATE ,
1031 CREATED_BY ,
1032 LAST_UPDATE_LOGIN ,
1033 REQUEST_ID ,
1034 PROGRAM_APPLICATION_ID ,
1035 PROGRAM_ID ,
1036 PROGRAM_UPDATE_DATE ,
1037 --VENDOR_SERIAL_NUMBER ,
1038 --VENDOR_LOT_NUMBER ,
1039 FM_SERIAL_NUMBER ,
1040 TO_SERIAL_NUMBER ,
1041 --SERIAL_PREFIX ,
1042 --ERROR_CODE ,
1043 --GROUP_HEADER_ID ,
1044 PARENT_SERIAL_NUMBER ,
1045 --END_ITEM_UNIT_NUMBER ,
1046 SERIAL_ATTRIBUTE_CATEGORY ,
1047 ORIGINATION_DATE ,
1048 C_ATTRIBUTE1 ,
1049 C_ATTRIBUTE2 ,
1050 C_ATTRIBUTE3 ,
1051 C_ATTRIBUTE4 ,
1052 C_ATTRIBUTE5 ,
1053 C_ATTRIBUTE6 ,
1054 C_ATTRIBUTE7 ,
1055 C_ATTRIBUTE8 ,
1056 C_ATTRIBUTE9 ,
1057 C_ATTRIBUTE10 ,
1058 C_ATTRIBUTE11 ,
1059 C_ATTRIBUTE12 ,
1060 C_ATTRIBUTE13 ,
1061 C_ATTRIBUTE14 ,
1062 C_ATTRIBUTE15 ,
1063 C_ATTRIBUTE16 ,
1064 C_ATTRIBUTE17 ,
1065 C_ATTRIBUTE18 ,
1066 C_ATTRIBUTE19 ,
1067 C_ATTRIBUTE20 ,
1068 D_ATTRIBUTE1 ,
1069 D_ATTRIBUTE2 ,
1070 D_ATTRIBUTE3 ,
1071 D_ATTRIBUTE4 ,
1072 D_ATTRIBUTE5 ,
1073 D_ATTRIBUTE6 ,
1074 D_ATTRIBUTE7 ,
1075 D_ATTRIBUTE8 ,
1076 D_ATTRIBUTE9 ,
1077 D_ATTRIBUTE10 ,
1078 N_ATTRIBUTE1 ,
1079 N_ATTRIBUTE2 ,
1080 N_ATTRIBUTE3 ,
1081 N_ATTRIBUTE4 ,
1082 N_ATTRIBUTE5 ,
1083 N_ATTRIBUTE6 ,
1084 N_ATTRIBUTE7 ,
1085 N_ATTRIBUTE8 ,
1086 N_ATTRIBUTE9 ,
1087 N_ATTRIBUTE10 ,
1088 STATUS_ID ,
1089 TERRITORY_CODE ,
1090 TIME_SINCE_NEW ,
1091 CYCLES_SINCE_NEW ,
1092 TIME_SINCE_OVERHAUL ,
1093 CYCLES_SINCE_OVERHAUL ,
1094 TIME_SINCE_REPAIR ,
1095 CYCLES_SINCE_REPAIR ,
1096 TIME_SINCE_VISIT ,
1097 CYCLES_SINCE_VISIT ,
1098 TIME_SINCE_MARK ,
1099 CYCLES_SINCE_MARK ,
1100 NUMBER_OF_REPAIRS ,
1101 PRODUCT_CODE ,
1102 PRODUCT_TRANSACTION_ID ,
1103 ATTRIBUTE_CATEGORY ,
1104 ATTRIBUTE1 ,
1105 ATTRIBUTE2 ,
1106 ATTRIBUTE3 ,
1107 ATTRIBUTE4 ,
1108 ATTRIBUTE5 ,
1109 ATTRIBUTE6 ,
1110 ATTRIBUTE7 ,
1111 ATTRIBUTE8 ,
1112 ATTRIBUTE9 ,
1113 ATTRIBUTE10 ,
1114 ATTRIBUTE11 ,
1115 ATTRIBUTE12 ,
1116 ATTRIBUTE13 ,
1117 ATTRIBUTE14 ,
1118 ATTRIBUTE15 ,
1119 DFF_UPDATED_FLAG
1120 --PARENT_LOT_NUMBER
1121 )
1122 select
1123 l_temp_id ,
1124 sysdate ,
1125 l_user_id ,
1126 sysdate ,
1127 l_user_id ,
1128 l_login_id ,
1129 l_req_id ,
1130 l_prog_appl_id ,
1131 l_prog_id ,
1132 sysdate ,
1133 --VENDOR_SERIAL_NUMBER ,
1134 --VENDOR_LOT_NUMBER ,
1135 MUT.SERIAL_NUMBER ,
1136 MUT.SERIAL_NUMBER ,
1137 --SERIAL_PREFIX ,
1138 --ERROR_CODE ,
1139 --GROUP_HEADER_ID ,
1140 MSN.PARENT_SERIAL_NUMBER ,
1141 --END_ITEM_UNIT_NUMBER ,
1142 MUT.SERIAL_ATTRIBUTE_CATEGORY ,
1143 MUT.ORIGINATION_DATE ,
1144 MUT.C_ATTRIBUTE1 ,
1145 MUT.C_ATTRIBUTE2 ,
1146 MUT.C_ATTRIBUTE3 ,
1147 MUT.C_ATTRIBUTE4 ,
1148 MUT.C_ATTRIBUTE5 ,
1149 MUT.C_ATTRIBUTE6 ,
1150 MUT.C_ATTRIBUTE7 ,
1151 MUT.C_ATTRIBUTE8 ,
1152 MUT.C_ATTRIBUTE9 ,
1153 MUT.C_ATTRIBUTE10 ,
1154 MUT.C_ATTRIBUTE11 ,
1155 MUT.C_ATTRIBUTE12 ,
1156 MUT.C_ATTRIBUTE13 ,
1157 MUT.C_ATTRIBUTE14 ,
1158 MUT.C_ATTRIBUTE15 ,
1159 MUT.C_ATTRIBUTE16 ,
1160 MUT.C_ATTRIBUTE17 ,
1161 MUT.C_ATTRIBUTE18 ,
1162 MUT.C_ATTRIBUTE19 ,
1163 MUT.C_ATTRIBUTE20 ,
1164 MUT.D_ATTRIBUTE1 ,
1165 MUT.D_ATTRIBUTE2 ,
1166 MUT.D_ATTRIBUTE3 ,
1167 MUT.D_ATTRIBUTE4 ,
1168 MUT.D_ATTRIBUTE5 ,
1169 MUT.D_ATTRIBUTE6 ,
1170 MUT.D_ATTRIBUTE7 ,
1171 MUT.D_ATTRIBUTE8 ,
1172 MUT.D_ATTRIBUTE9 ,
1173 MUT.D_ATTRIBUTE10 ,
1174 MUT.N_ATTRIBUTE1 ,
1175 MUT.N_ATTRIBUTE2 ,
1176 MUT.N_ATTRIBUTE3 ,
1177 MUT.N_ATTRIBUTE4 ,
1178 MUT.N_ATTRIBUTE5 ,
1179 MUT.N_ATTRIBUTE6 ,
1180 MUT.N_ATTRIBUTE7 ,
1181 MUT.N_ATTRIBUTE8 ,
1182 MUT.N_ATTRIBUTE9 ,
1183 MUT.N_ATTRIBUTE10 ,
1184 MUT.STATUS_ID ,
1185 MUT.TERRITORY_CODE ,
1186 MUT.TIME_SINCE_NEW ,
1187 MUT.CYCLES_SINCE_NEW ,
1188 MUT.TIME_SINCE_OVERHAUL ,
1189 MUT.CYCLES_SINCE_OVERHAUL ,
1190 MUT.TIME_SINCE_REPAIR ,
1191 MUT.CYCLES_SINCE_REPAIR ,
1192 MUT.TIME_SINCE_VISIT ,
1193 MUT.CYCLES_SINCE_VISIT ,
1194 MUT.TIME_SINCE_MARK ,
1195 MUT.CYCLES_SINCE_MARK ,
1196 MUT.NUMBER_OF_REPAIRS ,
1197 MUT.PRODUCT_CODE ,
1198 MUT.PRODUCT_TRANSACTION_ID ,
1199 MUT.ATTRIBUTE_CATEGORY ,
1200 MUT.ATTRIBUTE1 ,
1201 MUT.ATTRIBUTE2 ,
1202 MUT.ATTRIBUTE3 ,
1203 MUT.ATTRIBUTE4 ,
1204 MUT.ATTRIBUTE5 ,
1205 MUT.ATTRIBUTE6 ,
1206 MUT.ATTRIBUTE7 ,
1207 MUT.ATTRIBUTE8 ,
1208 MUT.ATTRIBUTE9 ,
1209 MUT.ATTRIBUTE10 ,
1210 MUT.ATTRIBUTE11 ,
1211 MUT.ATTRIBUTE12 ,
1212 MUT.ATTRIBUTE13 ,
1213 MUT.ATTRIBUTE14 ,
1214 MUT.ATTRIBUTE15 ,
1215 decode(nvl(MUT.ATTRIBUTE_CATEGORY,-1),-1,'N','Y')
1216 -- PARENT_LOT_NUMBER
1217 from mtl_unit_transactions MUT,
1218 mtl_serial_numbers MSN
1219 where MUT.transaction_id = l_mmt_rec.transaction_id
1220 and MUT.serial_number = MSN.serial_number
1221 and MSN.current_organization_id = MUT.ORGANIZATION_ID
1222 and msn.last_transaction_id = l_mmt_rec.transaction_id;
1223
1224 IF (G_LOG_LEVEL_STATEMENT >= l_log_level) THEN
1225 l_msg_tokens.delete;
1226 WSM_log_PVT.logMessage (p_module_name => l_module ,
1227 p_msg_text => 'Inserted : ' || SQL%ROWCOUNT
1228 || ' rows into MSTT(Serial)',
1229 p_stmt_num => l_stmt_num ,
1230 p_msg_tokens => l_msg_tokens ,
1231 p_fnd_log_level => G_LOG_LEVEL_STATEMENT ,
1232 p_run_log_level => l_log_level
1233 );
1234 END IF;
1235
1236 end if;
1237
1238 end loop;
1239
1240 exception
1241
1242 WHEN FND_API.G_EXC_ERROR THEN
1243
1244 x_return_status := G_RET_ERROR;
1245 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,
1246 p_count => x_error_count ,
1247 p_data => x_error_msg
1248 );
1249
1250 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1251
1252 x_return_status := G_RET_UNEXPECTED;
1253
1254 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,
1255 p_count => x_error_count ,
1256 p_data => x_error_msg
1257 );
1258 WHEN OTHERS THEN
1259
1260 x_return_status := G_RET_UNEXPECTED;
1261
1262 IF (G_LOG_LEVEL_UNEXPECTED >= l_log_level) OR
1263 (FND_MSG_PUB.check_msg_level(G_MSG_LVL_UNEXP_ERROR))
1264 THEN
1265 WSM_log_PVT.handle_others( p_module_name => l_module ,
1266 p_stmt_num => l_stmt_num ,
1267 p_fnd_log_level => G_LOG_LEVEL_UNEXPECTED ,
1268 p_run_log_level => l_log_level
1269 );
1270 END IF;
1271
1272 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,
1273 p_count => x_error_count ,
1274 p_data => x_error_msg
1275 );
1276 END populate_components;
1277
1278 END WSM_Serial_support_GRP;
|
|
|
|