[Home] [Help]
1 PACKAGE BODY WSM_Serial_support_GRP AS
2 /* $Header: WSMGSERB.pls 120.2.12000000.2 2007/02/23 12:32:48 mprathap 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 ,
326 );
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
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,
447 WIP_ENTITY_TYPE,
444 OPERATION_SEQ_NUM,
445 DEPARTMENT_ID,
446 EMPLOYEE_CODE,
448 COMPLETION_TRANSACTION_ID,
449 ATTRIBUTE_CATEGORY,
450 ATTRIBUTE1,
451 ATTRIBUTE2,
452 ATTRIBUTE3,
453 ATTRIBUTE4,
454 ATTRIBUTE5,
455 ATTRIBUTE6,
456 ATTRIBUTE7,
457 ATTRIBUTE8,
458 ATTRIBUTE9,
459 ATTRIBUTE10,
460 ATTRIBUTE11,
461 ATTRIBUTE12,
462 ATTRIBUTE13,
463 ATTRIBUTE14,
464 ATTRIBUTE15,
465 MOVEMENT_ID,
466 SOURCE_PROJECT_ID,
467 SOURCE_TASK_ID,
468 PROJECT_ID,
469 TASK_ID,
470 MOVE_TRANSACTION_ID --Bug 5207917
471 )
472 SELECT
473 p_mtl_txn_hdr_id,
474 l_temp_id,
475 MMT.SOURCE_CODE,
476 MMT.SOURCE_LINE_ID,
477 'Y', -- PROCESS_FLAG,
478 'Y',-- POSTING_FLAG,
479 --'N',-- WIP_COMMIT_FLAG,
480 sysdate,
481 l_user_id,
482 sysdate,
483 l_user_id,
484 l_login_id,
485 l_prog_id,
486 sysdate,
487 l_prog_appl_id,
488 l_req_id,
489 MMT.ORGANIZATION_ID,
490 MMT.SUBINVENTORY_CODE,
491 MMT.LOCATOR_ID,
492 MMT.INVENTORY_ITEM_ID,
493 MMT.REVISION,
494 decode(MMT.TRANSACTION_ACTION_ID,wip_constants.issnegc_action,
495 wip_constants.retnegc_type,
496 wip_constants.retcomp_type
497
498 ),
499 decode(MMT.TRANSACTION_ACTION_ID,wip_constants.issnegc_action,
500 wip_constants.retnegc_action,
501 wip_constants.retcomp_action
502
503 ),
504 MMT.TRANSACTION_SOURCE_TYPE_ID,
505 MMT.TRANSACTION_SOURCE_ID,
506 MMT.TRANSACTION_SOURCE_NAME,
507 MMT.TRANSACTION_REFERENCE,
508 MMT.REASON_ID,
509 p_txn_date, -- transaction date
510 l_acct_period_id, -- accout period id...
511 -1 * MMT.TRANSACTION_QUANTITY,
512 MMT.TRANSACTION_UOM,
513 -1 * MMT.PRIMARY_QUANTITY,
514 MMT.OPERATION_SEQ_NUM,
515 MMT.DEPARTMENT_ID,
516 MMT.EMPLOYEE_CODE,
517 5, --MMT.WIP_ENTITY_TYPE,
518 p_compl_txn_id,
519 MMT.ATTRIBUTE_CATEGORY,
520 MMT.ATTRIBUTE1,
521 MMT.ATTRIBUTE2,
522 MMT.ATTRIBUTE3,
523 MMT.ATTRIBUTE4,
524 MMT.ATTRIBUTE5,
525 MMT.ATTRIBUTE6,
526 MMT.ATTRIBUTE7,
527 MMT.ATTRIBUTE8,
528 MMT.ATTRIBUTE9,
529 MMT.ATTRIBUTE10,
530 MMT.ATTRIBUTE11,
531 MMT.ATTRIBUTE12,
532 MMT.ATTRIBUTE13,
533 MMT.ATTRIBUTE14,
534 MMT.ATTRIBUTE15,
535 MMT.MOVEMENT_ID,
536 MMT.SOURCE_PROJECT_ID,
537 MMT.SOURCE_TASK_ID,
538 MMT.PROJECT_ID,
539 MMT.TASK_ID,
540 p_move_txn_id --Bug 5207917
541 FROM MTL_MATERIAL_TRANSACTIONS MMT
542 WHERE TRANSACTION_ID = l_mmt_rec.transaction_id;
543
544 IF (G_LOG_LEVEL_STATEMENT >= l_log_level) THEN
545 l_msg_tokens.delete;
546 WSM_log_PVT.logMessage (p_module_name => l_module ,
547 p_msg_text => 'Inserted : ' || SQL%ROWCOUNT || ' rows into MMTT',
548 p_stmt_num => l_stmt_num ,
549 p_msg_tokens => l_msg_tokens ,
550 p_fnd_log_level => G_LOG_LEVEL_STATEMENT ,
551 p_run_log_level => l_log_level
552 );
553 END IF;
554
555 l_stmt_num := 80;
556 l_lot_controlled := 0;
557 -- Now obtain the the Lot
558 for l_lot_txn_rec in c_lot_txn(l_mmt_rec.transaction_id) loop
559
560 l_stmt_num := 90;
561
562 l_lot_controlled := 1;
563 if l_lot_txn_rec.serial_transaction_id is not null then
564 l_stmt_num := 100;
565 select mtl_material_transactions_s.nextval
569 l_stmt_num := 110;
566 into l_serial_txn_id
567 from dual;
568 else
570 l_serial_txn_id := null;
571 end if;
572
573 l_stmt_num := 120;
574 insert into mtl_transaction_lots_temp
575 (
576 TRANSACTION_TEMP_ID ,
577 LAST_UPDATE_DATE ,
578 LAST_UPDATED_BY ,
579 CREATION_DATE ,
580 CREATED_BY ,
581 LAST_UPDATE_LOGIN ,
582 REQUEST_ID ,
583 PROGRAM_APPLICATION_ID ,
584 PROGRAM_ID ,
585 PROGRAM_UPDATE_DATE ,
586 TRANSACTION_QUANTITY ,
587 PRIMARY_QUANTITY ,
588 LOT_NUMBER ,
589 SERIAL_TRANSACTION_TEMP_ID ,
590 DESCRIPTION ,
591 VENDOR_NAME ,
592 SUPPLIER_LOT_NUMBER ,
593 ORIGINATION_DATE ,
594 DATE_CODE ,
595 GRADE_CODE ,
596 CHANGE_DATE ,
597 MATURITY_DATE ,
598 STATUS_ID ,
599 RETEST_DATE ,
600 AGE ,
601 ITEM_SIZE ,
602 COLOR ,
603 VOLUME ,
604 VOLUME_UOM ,
605 PLACE_OF_ORIGIN ,
606 BEST_BY_DATE ,
607 LENGTH ,
608 LENGTH_UOM ,
609 RECYCLED_CONTENT ,
610 THICKNESS ,
611 THICKNESS_UOM ,
612 WIDTH ,
613 WIDTH_UOM ,
614 CURL_WRINKLE_FOLD ,
615 LOT_ATTRIBUTE_CATEGORY ,
616 C_ATTRIBUTE1 ,
617 C_ATTRIBUTE2 ,
618 C_ATTRIBUTE3 ,
619 C_ATTRIBUTE4 ,
620 C_ATTRIBUTE5 ,
621 C_ATTRIBUTE6 ,
622 C_ATTRIBUTE7 ,
623 C_ATTRIBUTE8 ,
624 C_ATTRIBUTE9 ,
625 C_ATTRIBUTE10 ,
626 C_ATTRIBUTE11 ,
627 C_ATTRIBUTE12 ,
628 C_ATTRIBUTE13 ,
629 C_ATTRIBUTE14 ,
630 C_ATTRIBUTE15 ,
631 C_ATTRIBUTE16 ,
632 C_ATTRIBUTE17 ,
633 C_ATTRIBUTE18 ,
634 C_ATTRIBUTE19 ,
635 C_ATTRIBUTE20 ,
636 D_ATTRIBUTE1 ,
637 D_ATTRIBUTE2 ,
638 D_ATTRIBUTE3 ,
639 D_ATTRIBUTE4 ,
640 D_ATTRIBUTE5 ,
641 D_ATTRIBUTE6 ,
642 D_ATTRIBUTE7 ,
643 D_ATTRIBUTE8 ,
644 D_ATTRIBUTE9 ,
645 D_ATTRIBUTE10 ,
646 N_ATTRIBUTE1 ,
647 N_ATTRIBUTE2 ,
648 N_ATTRIBUTE3 ,
649 N_ATTRIBUTE4 ,
650 N_ATTRIBUTE5 ,
651 N_ATTRIBUTE6 ,
652 N_ATTRIBUTE7 ,
653 N_ATTRIBUTE8 ,
654 N_ATTRIBUTE9 ,
655 N_ATTRIBUTE10 ,
656 VENDOR_ID ,
657 TERRITORY_CODE ,
658 PRODUCT_CODE ,
659 PRODUCT_TRANSACTION_ID ,
660 ATTRIBUTE_CATEGORY ,
661 ATTRIBUTE1 ,
662 ATTRIBUTE2 ,
663 ATTRIBUTE3 ,
664 ATTRIBUTE4 ,
665 ATTRIBUTE5 ,
669 ATTRIBUTE9 ,
666 ATTRIBUTE6 ,
667 ATTRIBUTE7 ,
668 ATTRIBUTE8 ,
670 ATTRIBUTE10 ,
671 ATTRIBUTE11 ,
672 ATTRIBUTE12 ,
673 ATTRIBUTE13 ,
674 ATTRIBUTE14 ,
675 ATTRIBUTE15 --,
676 )
677 SELECT
678 l_temp_id ,
679 sysdate ,
680 l_user_id ,
681 sysdate ,
682 l_user_id ,
683 l_login_id ,
684 l_req_id ,
685 l_prog_appl_id ,
686 l_prog_id ,
687 sysdate ,
688 -1 * TRANSACTION_QUANTITY ,
689 -1 * PRIMARY_QUANTITY ,
690 LOT_NUMBER ,
691 l_serial_txn_id ,
692 DESCRIPTION ,
693 VENDOR_NAME ,
694 SUPPLIER_LOT_NUMBER ,
695 ORIGINATION_DATE ,
696 DATE_CODE ,
697 GRADE_CODE ,
698 CHANGE_DATE ,
699 MATURITY_DATE ,
700 STATUS_ID ,
701 RETEST_DATE ,
702 AGE ,
703 ITEM_SIZE ,
704 COLOR ,
705 VOLUME ,
706 VOLUME_UOM ,
707 PLACE_OF_ORIGIN ,
708 BEST_BY_DATE ,
709 LENGTH ,
710 LENGTH_UOM ,
711 RECYCLED_CONTENT ,
712 THICKNESS ,
713 THICKNESS_UOM ,
714 WIDTH ,
715 WIDTH_UOM ,
716 CURL_WRINKLE_FOLD ,
717 LOT_ATTRIBUTE_CATEGORY ,
718 C_ATTRIBUTE1 ,
719 C_ATTRIBUTE2 ,
720 C_ATTRIBUTE3 ,
721 C_ATTRIBUTE4 ,
722 C_ATTRIBUTE5 ,
723 C_ATTRIBUTE6 ,
724 C_ATTRIBUTE7 ,
725 C_ATTRIBUTE8 ,
726 C_ATTRIBUTE9 ,
727 C_ATTRIBUTE10 ,
728 C_ATTRIBUTE11 ,
729 C_ATTRIBUTE12 ,
730 C_ATTRIBUTE13 ,
731 C_ATTRIBUTE14 ,
732 C_ATTRIBUTE15 ,
733 C_ATTRIBUTE16 ,
734 C_ATTRIBUTE17 ,
735 C_ATTRIBUTE18 ,
736 C_ATTRIBUTE19 ,
737 C_ATTRIBUTE20 ,
738 D_ATTRIBUTE1 ,
739 D_ATTRIBUTE2 ,
740 D_ATTRIBUTE3 ,
741 D_ATTRIBUTE4 ,
742 D_ATTRIBUTE5 ,
743 D_ATTRIBUTE6 ,
744 D_ATTRIBUTE7 ,
745 D_ATTRIBUTE8 ,
746 D_ATTRIBUTE9 ,
747 D_ATTRIBUTE10 ,
748 N_ATTRIBUTE1 ,
749 N_ATTRIBUTE2 ,
750 N_ATTRIBUTE3 ,
751 N_ATTRIBUTE4 ,
752 N_ATTRIBUTE5 ,
753 N_ATTRIBUTE6 ,
754 N_ATTRIBUTE7 ,
755 N_ATTRIBUTE8 ,
756 N_ATTRIBUTE9 ,
757 N_ATTRIBUTE10 ,
758 VENDOR_ID ,
759 TERRITORY_CODE ,
760 PRODUCT_CODE ,
761 PRODUCT_TRANSACTION_ID ,
762 ATTRIBUTE_CATEGORY ,
766 ATTRIBUTE4 ,
763 ATTRIBUTE1 ,
764 ATTRIBUTE2 ,
765 ATTRIBUTE3 ,
767 ATTRIBUTE5 ,
768 ATTRIBUTE6 ,
769 ATTRIBUTE7 ,
770 ATTRIBUTE8 ,
771 ATTRIBUTE9 ,
772 ATTRIBUTE10 ,
773 ATTRIBUTE11 ,
774 ATTRIBUTE12 ,
775 ATTRIBUTE13 ,
776 ATTRIBUTE14 ,
777 ATTRIBUTE15 --,
778 --PARENT_LOT_NUMBER
779 from MTL_TRANSACTION_LOT_NUMBERS
780 --Bug 5614015:Rowid is used to uniquely identify the lot selected
781 --where transaction_id = l_mmt_rec.transaction_id
782 --and lot_number = l_lot_txn_rec.lot_number;
783 where rowid = l_lot_txn_rec.rowid;
784
785 IF (G_LOG_LEVEL_STATEMENT >= l_log_level) THEN
786 l_msg_tokens.delete;
787 WSM_log_PVT.logMessage (p_module_name => l_module ,
788 p_msg_text => 'Inserted : ' || SQL%ROWCOUNT
789 || ' rows into MTLT for lot '
790 || l_lot_txn_rec.lot_number,
791 p_stmt_num => l_stmt_num ,
792 p_msg_tokens => l_msg_tokens ,
793 p_fnd_log_level => G_LOG_LEVEL_STATEMENT ,
794 p_run_log_level => l_log_level
795 );
796 END IF;
797
798 l_stmt_num := 130;
799 if nvl(l_serial_txn_id,0) <> 0 then
800
801 l_stmt_num := 140;
802 insert into mtl_serial_numbers_temp
803 (
804 TRANSACTION_TEMP_ID ,
805 LAST_UPDATE_DATE ,
806 LAST_UPDATED_BY ,
807 CREATION_DATE ,
808 CREATED_BY ,
809 LAST_UPDATE_LOGIN ,
810 REQUEST_ID ,
811 PROGRAM_APPLICATION_ID ,
812 PROGRAM_ID ,
813 PROGRAM_UPDATE_DATE ,
814 --VENDOR_SERIAL_NUMBER ,
815 --VENDOR_LOT_NUMBER ,
816 FM_SERIAL_NUMBER ,
817 TO_SERIAL_NUMBER ,
818 --SERIAL_PREFIX ,
819 --ERROR_CODE ,
820 --GROUP_HEADER_ID ,
821 PARENT_SERIAL_NUMBER ,
822 SERIAL_ATTRIBUTE_CATEGORY ,
823 ORIGINATION_DATE ,
824 C_ATTRIBUTE1 ,
825 C_ATTRIBUTE2 ,
826 C_ATTRIBUTE3 ,
827 C_ATTRIBUTE4 ,
828 C_ATTRIBUTE5 ,
829 C_ATTRIBUTE6 ,
830 C_ATTRIBUTE7 ,
831 C_ATTRIBUTE8 ,
832 C_ATTRIBUTE9 ,
833 C_ATTRIBUTE10 ,
834 C_ATTRIBUTE11 ,
835 C_ATTRIBUTE12 ,
836 C_ATTRIBUTE13 ,
837 C_ATTRIBUTE14 ,
838 C_ATTRIBUTE15 ,
839 C_ATTRIBUTE16 ,
840 C_ATTRIBUTE17 ,
841 C_ATTRIBUTE18 ,
842 C_ATTRIBUTE19 ,
843 C_ATTRIBUTE20 ,
844 D_ATTRIBUTE1 ,
845 D_ATTRIBUTE2 ,
846 D_ATTRIBUTE3 ,
850 D_ATTRIBUTE7 ,
847 D_ATTRIBUTE4 ,
848 D_ATTRIBUTE5 ,
849 D_ATTRIBUTE6 ,
851 D_ATTRIBUTE8 ,
852 D_ATTRIBUTE9 ,
853 D_ATTRIBUTE10 ,
854 N_ATTRIBUTE1 ,
855 N_ATTRIBUTE2 ,
856 N_ATTRIBUTE3 ,
857 N_ATTRIBUTE4 ,
858 N_ATTRIBUTE5 ,
859 N_ATTRIBUTE6 ,
860 N_ATTRIBUTE7 ,
861 N_ATTRIBUTE8 ,
862 N_ATTRIBUTE9 ,
863 N_ATTRIBUTE10 ,
864 STATUS_ID ,
865 TERRITORY_CODE ,
866 TIME_SINCE_NEW ,
867 CYCLES_SINCE_NEW ,
868 TIME_SINCE_OVERHAUL ,
869 CYCLES_SINCE_OVERHAUL ,
870 TIME_SINCE_REPAIR ,
871 CYCLES_SINCE_REPAIR ,
872 TIME_SINCE_VISIT ,
873 CYCLES_SINCE_VISIT ,
874 TIME_SINCE_MARK ,
875 CYCLES_SINCE_MARK ,
876 NUMBER_OF_REPAIRS ,
877 PRODUCT_CODE ,
878 PRODUCT_TRANSACTION_ID ,
879 ATTRIBUTE_CATEGORY ,
880 ATTRIBUTE1 ,
881 ATTRIBUTE2 ,
882 ATTRIBUTE3 ,
883 ATTRIBUTE4 ,
884 ATTRIBUTE5 ,
885 ATTRIBUTE6 ,
886 ATTRIBUTE7 ,
887 ATTRIBUTE8 ,
888 ATTRIBUTE9 ,
889 ATTRIBUTE10 ,
890 ATTRIBUTE11 ,
891 ATTRIBUTE12 ,
892 ATTRIBUTE13 ,
893 ATTRIBUTE14 ,
894 ATTRIBUTE15 ,
895 DFF_UPDATED_FLAG
896 --PARENT_LOT_NUMBER
897 )
898 select
899 l_serial_txn_id ,
900 sysdate ,
901 l_user_id ,
902 sysdate ,
903 l_user_id ,
904 l_login_id ,
905 l_req_id ,
906 l_prog_appl_id ,
907 l_prog_id ,
908 sysdate ,
909 --VENDOR_SERIAL_NUMBER ,
910 --VENDOR_LOT_NUMBER ,
911 MUT.SERIAL_NUMBER ,
912 MUT.SERIAL_NUMBER ,
913 --SERIAL_PREFIX ,
914 --ERROR_CODE ,
915 --GROUP_HEADER_ID ,
916 MSN.PARENT_SERIAL_NUMBER ,
917 --END_ITEM_UNIT_NUMBER ,
918 MUT.SERIAL_ATTRIBUTE_CATEGORY ,
919 MUT.ORIGINATION_DATE ,
920 MUT.C_ATTRIBUTE1 ,
921 MUT.C_ATTRIBUTE2 ,
925 MUT.C_ATTRIBUTE6 ,
922 MUT.C_ATTRIBUTE3 ,
923 MUT.C_ATTRIBUTE4 ,
924 MUT.C_ATTRIBUTE5 ,
926 MUT.C_ATTRIBUTE7 ,
927 MUT.C_ATTRIBUTE8 ,
928 MUT.C_ATTRIBUTE9 ,
929 MUT.C_ATTRIBUTE10 ,
930 MUT.C_ATTRIBUTE11 ,
931 MUT.C_ATTRIBUTE12 ,
932 MUT.C_ATTRIBUTE13 ,
933 MUT.C_ATTRIBUTE14 ,
934 MUT.C_ATTRIBUTE15 ,
935 MUT.C_ATTRIBUTE16 ,
936 MUT.C_ATTRIBUTE17 ,
937 MUT.C_ATTRIBUTE18 ,
938 MUT.C_ATTRIBUTE19 ,
939 MUT.C_ATTRIBUTE20 ,
940 MUT.D_ATTRIBUTE1 ,
941 MUT.D_ATTRIBUTE2 ,
942 MUT.D_ATTRIBUTE3 ,
943 MUT.D_ATTRIBUTE4 ,
944 MUT.D_ATTRIBUTE5 ,
945 MUT.D_ATTRIBUTE6 ,
946 MUT.D_ATTRIBUTE7 ,
947 MUT.D_ATTRIBUTE8 ,
948 MUT.D_ATTRIBUTE9 ,
949 MUT.D_ATTRIBUTE10 ,
950 MUT.N_ATTRIBUTE1 ,
951 MUT.N_ATTRIBUTE2 ,
952 MUT.N_ATTRIBUTE3 ,
953 MUT.N_ATTRIBUTE4 ,
954 MUT.N_ATTRIBUTE5 ,
955 MUT.N_ATTRIBUTE6 ,
956 MUT.N_ATTRIBUTE7 ,
957 MUT.N_ATTRIBUTE8 ,
958 MUT.N_ATTRIBUTE9 ,
959 MUT.N_ATTRIBUTE10 ,
960 MUT.STATUS_ID ,
961 MUT.TERRITORY_CODE ,
962 MUT.TIME_SINCE_NEW ,
963 MUT.CYCLES_SINCE_NEW ,
964 MUT.TIME_SINCE_OVERHAUL ,
965 MUT.CYCLES_SINCE_OVERHAUL ,
966 MUT.TIME_SINCE_REPAIR ,
967 MUT.CYCLES_SINCE_REPAIR ,
968 MUT.TIME_SINCE_VISIT ,
969 MUT.CYCLES_SINCE_VISIT ,
970 MUT.TIME_SINCE_MARK ,
971 MUT.CYCLES_SINCE_MARK ,
972 MUT.NUMBER_OF_REPAIRS ,
973 MUT.PRODUCT_CODE ,
974 MUT.PRODUCT_TRANSACTION_ID ,
975 MUT.ATTRIBUTE_CATEGORY ,
976 MUT.ATTRIBUTE1 ,
977 MUT.ATTRIBUTE2 ,
978 MUT.ATTRIBUTE3 ,
979 MUT.ATTRIBUTE4 ,
980 MUT.ATTRIBUTE5 ,
981 MUT.ATTRIBUTE6 ,
982 MUT.ATTRIBUTE7 ,
983 MUT.ATTRIBUTE8 ,
984 MUT.ATTRIBUTE9 ,
985 MUT.ATTRIBUTE10 ,
989 MUT.ATTRIBUTE14 ,
986 MUT.ATTRIBUTE11 ,
987 MUT.ATTRIBUTE12 ,
988 MUT.ATTRIBUTE13 ,
990 MUT.ATTRIBUTE15 ,
991 decode(nvl(MUT.ATTRIBUTE_CATEGORY,-1),-1,'N','Y')
992 from mtl_unit_transactions MUT,
993 mtl_serial_numbers MSN
994 where MUT.transaction_id = l_lot_txn_rec.serial_transaction_id
995 and MUT.serial_number = MSN.serial_number
996 and MSN.current_organization_id = MUT.ORGANIZATION_ID
997 and msn.last_transaction_id = l_mmt_rec.transaction_id; --l_lot_txn_rec.serial_transaction_id;
998
999 IF (G_LOG_LEVEL_STATEMENT >= l_log_level) THEN
1000 l_msg_tokens.delete;
1001 WSM_log_PVT.logMessage (p_module_name => l_module ,
1002 p_msg_text => 'Inserted : ' || SQL%ROWCOUNT
1003 || ' rows into MSTT(Serial)',
1004 p_stmt_num => l_stmt_num ,
1005 p_msg_tokens => l_msg_tokens ,
1006 p_fnd_log_level => G_LOG_LEVEL_STATEMENT ,
1007 p_run_log_level => l_log_level
1008 );
1009 END IF;
1010 end if;
1011
1012 end loop;
1013
1014 l_stmt_num := 150;
1015 if l_lot_controlled = 0 then
1016
1017 l_stmt_num := 160;
1018 -- insert the records for serial controlled assemblies...
1019 insert into mtl_serial_numbers_temp
1020 (
1021 TRANSACTION_TEMP_ID ,
1022 LAST_UPDATE_DATE ,
1023 LAST_UPDATED_BY ,
1024 CREATION_DATE ,
1025 CREATED_BY ,
1026 LAST_UPDATE_LOGIN ,
1027 REQUEST_ID ,
1028 PROGRAM_APPLICATION_ID ,
1029 PROGRAM_ID ,
1030 PROGRAM_UPDATE_DATE ,
1031 --VENDOR_SERIAL_NUMBER ,
1032 --VENDOR_LOT_NUMBER ,
1033 FM_SERIAL_NUMBER ,
1034 TO_SERIAL_NUMBER ,
1035 --SERIAL_PREFIX ,
1036 --ERROR_CODE ,
1037 --GROUP_HEADER_ID ,
1038 PARENT_SERIAL_NUMBER ,
1039 --END_ITEM_UNIT_NUMBER ,
1040 SERIAL_ATTRIBUTE_CATEGORY ,
1041 ORIGINATION_DATE ,
1042 C_ATTRIBUTE1 ,
1043 C_ATTRIBUTE2 ,
1044 C_ATTRIBUTE3 ,
1045 C_ATTRIBUTE4 ,
1046 C_ATTRIBUTE5 ,
1047 C_ATTRIBUTE6 ,
1048 C_ATTRIBUTE7 ,
1049 C_ATTRIBUTE8 ,
1050 C_ATTRIBUTE9 ,
1051 C_ATTRIBUTE10 ,
1052 C_ATTRIBUTE11 ,
1053 C_ATTRIBUTE12 ,
1054 C_ATTRIBUTE13 ,
1055 C_ATTRIBUTE14 ,
1056 C_ATTRIBUTE15 ,
1057 C_ATTRIBUTE16 ,
1061 C_ATTRIBUTE20 ,
1058 C_ATTRIBUTE17 ,
1059 C_ATTRIBUTE18 ,
1060 C_ATTRIBUTE19 ,
1062 D_ATTRIBUTE1 ,
1063 D_ATTRIBUTE2 ,
1064 D_ATTRIBUTE3 ,
1065 D_ATTRIBUTE4 ,
1066 D_ATTRIBUTE5 ,
1067 D_ATTRIBUTE6 ,
1068 D_ATTRIBUTE7 ,
1069 D_ATTRIBUTE8 ,
1070 D_ATTRIBUTE9 ,
1071 D_ATTRIBUTE10 ,
1072 N_ATTRIBUTE1 ,
1073 N_ATTRIBUTE2 ,
1074 N_ATTRIBUTE3 ,
1075 N_ATTRIBUTE4 ,
1076 N_ATTRIBUTE5 ,
1077 N_ATTRIBUTE6 ,
1078 N_ATTRIBUTE7 ,
1079 N_ATTRIBUTE8 ,
1080 N_ATTRIBUTE9 ,
1081 N_ATTRIBUTE10 ,
1082 STATUS_ID ,
1083 TERRITORY_CODE ,
1084 TIME_SINCE_NEW ,
1085 CYCLES_SINCE_NEW ,
1086 TIME_SINCE_OVERHAUL ,
1087 CYCLES_SINCE_OVERHAUL ,
1088 TIME_SINCE_REPAIR ,
1089 CYCLES_SINCE_REPAIR ,
1090 TIME_SINCE_VISIT ,
1091 CYCLES_SINCE_VISIT ,
1092 TIME_SINCE_MARK ,
1093 CYCLES_SINCE_MARK ,
1094 NUMBER_OF_REPAIRS ,
1095 PRODUCT_CODE ,
1096 PRODUCT_TRANSACTION_ID ,
1097 ATTRIBUTE_CATEGORY ,
1098 ATTRIBUTE1 ,
1099 ATTRIBUTE2 ,
1100 ATTRIBUTE3 ,
1101 ATTRIBUTE4 ,
1102 ATTRIBUTE5 ,
1103 ATTRIBUTE6 ,
1104 ATTRIBUTE7 ,
1105 ATTRIBUTE8 ,
1106 ATTRIBUTE9 ,
1107 ATTRIBUTE10 ,
1108 ATTRIBUTE11 ,
1109 ATTRIBUTE12 ,
1110 ATTRIBUTE13 ,
1111 ATTRIBUTE14 ,
1112 ATTRIBUTE15 ,
1113 DFF_UPDATED_FLAG
1114 --PARENT_LOT_NUMBER
1115 )
1116 select
1117 l_temp_id ,
1118 sysdate ,
1119 l_user_id ,
1120 sysdate ,
1121 l_user_id ,
1122 l_login_id ,
1123 l_req_id ,
1124 l_prog_appl_id ,
1125 l_prog_id ,
1126 sysdate ,
1130 MUT.SERIAL_NUMBER ,
1127 --VENDOR_SERIAL_NUMBER ,
1128 --VENDOR_LOT_NUMBER ,
1129 MUT.SERIAL_NUMBER ,
1131 --SERIAL_PREFIX ,
1132 --ERROR_CODE ,
1133 --GROUP_HEADER_ID ,
1134 MSN.PARENT_SERIAL_NUMBER ,
1135 --END_ITEM_UNIT_NUMBER ,
1136 MUT.SERIAL_ATTRIBUTE_CATEGORY ,
1137 MUT.ORIGINATION_DATE ,
1138 MUT.C_ATTRIBUTE1 ,
1139 MUT.C_ATTRIBUTE2 ,
1140 MUT.C_ATTRIBUTE3 ,
1141 MUT.C_ATTRIBUTE4 ,
1142 MUT.C_ATTRIBUTE5 ,
1143 MUT.C_ATTRIBUTE6 ,
1144 MUT.C_ATTRIBUTE7 ,
1145 MUT.C_ATTRIBUTE8 ,
1146 MUT.C_ATTRIBUTE9 ,
1147 MUT.C_ATTRIBUTE10 ,
1148 MUT.C_ATTRIBUTE11 ,
1149 MUT.C_ATTRIBUTE12 ,
1150 MUT.C_ATTRIBUTE13 ,
1151 MUT.C_ATTRIBUTE14 ,
1152 MUT.C_ATTRIBUTE15 ,
1153 MUT.C_ATTRIBUTE16 ,
1154 MUT.C_ATTRIBUTE17 ,
1155 MUT.C_ATTRIBUTE18 ,
1156 MUT.C_ATTRIBUTE19 ,
1157 MUT.C_ATTRIBUTE20 ,
1158 MUT.D_ATTRIBUTE1 ,
1159 MUT.D_ATTRIBUTE2 ,
1160 MUT.D_ATTRIBUTE3 ,
1161 MUT.D_ATTRIBUTE4 ,
1162 MUT.D_ATTRIBUTE5 ,
1163 MUT.D_ATTRIBUTE6 ,
1164 MUT.D_ATTRIBUTE7 ,
1165 MUT.D_ATTRIBUTE8 ,
1166 MUT.D_ATTRIBUTE9 ,
1167 MUT.D_ATTRIBUTE10 ,
1168 MUT.N_ATTRIBUTE1 ,
1169 MUT.N_ATTRIBUTE2 ,
1170 MUT.N_ATTRIBUTE3 ,
1171 MUT.N_ATTRIBUTE4 ,
1172 MUT.N_ATTRIBUTE5 ,
1173 MUT.N_ATTRIBUTE6 ,
1174 MUT.N_ATTRIBUTE7 ,
1175 MUT.N_ATTRIBUTE8 ,
1176 MUT.N_ATTRIBUTE9 ,
1177 MUT.N_ATTRIBUTE10 ,
1178 MUT.STATUS_ID ,
1179 MUT.TERRITORY_CODE ,
1180 MUT.TIME_SINCE_NEW ,
1181 MUT.CYCLES_SINCE_NEW ,
1182 MUT.TIME_SINCE_OVERHAUL ,
1183 MUT.CYCLES_SINCE_OVERHAUL ,
1184 MUT.TIME_SINCE_REPAIR ,
1185 MUT.CYCLES_SINCE_REPAIR ,
1186 MUT.TIME_SINCE_VISIT ,
1187 MUT.CYCLES_SINCE_VISIT ,
1188 MUT.TIME_SINCE_MARK ,
1189 MUT.CYCLES_SINCE_MARK ,
1190 MUT.NUMBER_OF_REPAIRS ,
1191 MUT.PRODUCT_CODE ,
1195 MUT.ATTRIBUTE2 ,
1192 MUT.PRODUCT_TRANSACTION_ID ,
1193 MUT.ATTRIBUTE_CATEGORY ,
1194 MUT.ATTRIBUTE1 ,
1196 MUT.ATTRIBUTE3 ,
1197 MUT.ATTRIBUTE4 ,
1198 MUT.ATTRIBUTE5 ,
1199 MUT.ATTRIBUTE6 ,
1200 MUT.ATTRIBUTE7 ,
1201 MUT.ATTRIBUTE8 ,
1202 MUT.ATTRIBUTE9 ,
1203 MUT.ATTRIBUTE10 ,
1204 MUT.ATTRIBUTE11 ,
1205 MUT.ATTRIBUTE12 ,
1206 MUT.ATTRIBUTE13 ,
1207 MUT.ATTRIBUTE14 ,
1208 MUT.ATTRIBUTE15 ,
1209 decode(nvl(MUT.ATTRIBUTE_CATEGORY,-1),-1,'N','Y')
1210 -- PARENT_LOT_NUMBER
1211 from mtl_unit_transactions MUT,
1212 mtl_serial_numbers MSN
1213 where MUT.transaction_id = l_mmt_rec.transaction_id
1214 and MUT.serial_number = MSN.serial_number
1215 and MSN.current_organization_id = MUT.ORGANIZATION_ID
1216 and msn.last_transaction_id = l_mmt_rec.transaction_id;
1217
1218 IF (G_LOG_LEVEL_STATEMENT >= l_log_level) THEN
1219 l_msg_tokens.delete;
1220 WSM_log_PVT.logMessage (p_module_name => l_module ,
1221 p_msg_text => 'Inserted : ' || SQL%ROWCOUNT
1222 || ' rows into MSTT(Serial)',
1223 p_stmt_num => l_stmt_num ,
1224 p_msg_tokens => l_msg_tokens ,
1225 p_fnd_log_level => G_LOG_LEVEL_STATEMENT ,
1226 p_run_log_level => l_log_level
1227 );
1228 END IF;
1229
1230 end if;
1231
1232 end loop;
1233
1234 exception
1235
1236 WHEN FND_API.G_EXC_ERROR THEN
1237
1238 x_return_status := G_RET_ERROR;
1239 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,
1240 p_count => x_error_count ,
1241 p_data => x_error_msg
1242 );
1243
1244 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1245
1246 x_return_status := G_RET_UNEXPECTED;
1247
1248 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,
1249 p_count => x_error_count ,
1250 p_data => x_error_msg
1251 );
1252 WHEN OTHERS THEN
1253
1254 x_return_status := G_RET_UNEXPECTED;
1255
1256 IF (G_LOG_LEVEL_UNEXPECTED >= l_log_level) OR
1257 (FND_MSG_PUB.check_msg_level(G_MSG_LVL_UNEXP_ERROR))
1258 THEN
1259 WSM_log_PVT.handle_others( p_module_name => l_module ,
1260 p_stmt_num => l_stmt_num ,
1261 p_fnd_log_level => G_LOG_LEVEL_UNEXPECTED ,
1262 p_run_log_level => l_log_level
1263 );
1264 END IF;
1265
1266 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,
1267 p_count => x_error_count ,
1268 p_data => x_error_msg
1269 );
1270 END populate_components;
1271
1272 END WSM_Serial_support_GRP;
|
|
|
|