1 PACKAGE BODY EAM_ASSET_MOVE_PUB
2 /* $Header: EAMPAMTB.pls 120.15.12010000.11 2009/01/23 10:28:47 vchidura ship $ */
3 AS
4
5 g_pkg_name CONSTANT VARCHAR2(30):= 'EAM_ASSET_MOVE_PUB';
6
7 PROCEDURE prepareMoveAsset
8 (
9 x_return_status OUT NOCOPY VARCHAR2 ,
10 x_return_message OUT NOCOPY VARCHAR2 ,
11 p_parent_instance_id IN NUMBER ,
12 p_dest_org_id IN NUMBER ,
13 p_includeChild IN VARCHAR2 ,
14 p_move_type IN NUMBER ,
15 p_curr_org_id IN NUMBER ,
16 p_curr_subinv_code IN VARCHAR2 ,
17 p_shipment_no IN VARCHAR2 ,
18 p_dest_subinv_code IN VARCHAR2 ,
19 p_context IN VARCHAR2,
20 p_dest_locator_id IN NUMBER :=NULL
21 )
22 IS
23 l_parent_instance_id NUMBER;
24 l_asset_count NUMBER;
25 l_header_id NUMBER;
26 l_curr_org_id NUMBER;
27 l_asset_move_hierarchy_tbl asset_move_hierarchy_tbl_type;
28 l_return_status VARCHAR2(240);
29 l_return_message VARCHAR2(240);
30 l_Parent_inventory_item_id NUMBER ;
31 l_openperiod_flag BOOLEAN;
32 l_openperiod_message VARCHAR2(240);
33 l_prepare_count NUMBER ;
34 l_move_txn_type_id NUMBER;
35 l_intransit_type NUMBER; -- Transaction Type Id for Inventory Transaction
36
37 --logging variables
38 l_api_name constant VARCHAR2(30) := 'prepareMoveAsset';
39 l_module VARCHAR2(200);
40 l_log_level CONSTANT NUMBER := fnd_log.g_current_runtime_level;
41 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level;
42 l_pLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_procedure >= l_log_level;
43 l_exLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_exception >= l_log_level;
44 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
45 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
46 l_eam_asset_move_count NUMBER;
47 --locator variables
48 x_locator_error_flag NUMBER;
49 x_locator_error_messg VARCHAR2(240);
50 --loop variables
51 l_counter_for_validation NUMBER :=1;
52
53 BEGIN
54 SELECT inventory_item_id
55 INTO l_Parent_inventory_item_id
56 FROM CSI_ITEM_INSTANCES
57 WHERE instance_id=p_parent_instance_id ;
58
59 IF (l_ulog) THEN
60 l_module := 'eam.plsql.'|| l_full_name;
61 END IF;
62
63 IF (l_plog) THEN
64 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module, 'Entering ' || l_full_name);
65 END IF;
66
67
68 DELETE FROM EAM_ASSET_MOVE_TEMP;
69
70 if (l_slog) then
71 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
72 'Going to get the asset Hierarchy ');
73 end if;
74
75 getAssetHierarchy( p_parent_instance_id,
76 p_includeChild,
77 l_asset_move_hierarchy_tbl,
78 p_curr_org_id );
79
80 if (l_slog) then
81 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
82 'Completed getting the asset Hierarchy ');
83 end if;
84
85 IF (p_move_type <> 1) THEN
86 IF(NOT(EAM_ASSET_MOVE_UTIL.isItemAssigned(l_Parent_inventory_item_id
87 ,p_dest_org_id))) THEN
88 --validation to check whether item is assigned for the destination_org or not.applicable only in case of inter-org transfers
89 x_return_status:='A';
90 x_return_message:='EAM_ITEM_NOT_ASSIGN';
91 RETURN ;
92 END IF;
93 END IF;
94
95 FOR i IN l_asset_move_hierarchy_tbl.FIRST .. l_asset_move_hierarchy_tbl.LAST
96 LOOP --should be done only for second to last rows.
97 --maint validation should not be done for first asset
98 --hence isItemAssigned validation for the first asset should be done from Controller Class itself
99 --bug 6966482
100 if (l_slog) then FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
101 'Processing asset ' || l_asset_move_hierarchy_tbl(i).instance_id );
102 end if;
103
104 eam_asset_move_util.isValidAssetMove( l_asset_move_hierarchy_tbl(i)
105 ,p_dest_org_id
106 ,l_counter_for_validation
107 ,l_return_status
108 ,l_return_message );
109
110 l_asset_move_hierarchy_tbl(i).prepare_status := l_return_status;
111 l_asset_move_hierarchy_tbl(i).prepare_msg := l_return_message;
112 l_counter_for_validation:=l_counter_for_validation+1;
113 END LOOP;
114
115 -- Generate an Header id from the sequence
116 SELECT APPS.mtl_material_transactions_s.NEXTVAL
117 INTO l_header_id FROM sys.dual;
118
119 if (l_slog) then
120 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
121 'Going to populate the temp ');
122 end if;
123
124 populateTemp(l_header_id
125 ,l_asset_move_hierarchy_tbl
126 ,p_parent_instance_id );
127
128 if (l_slog) then
129 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
130 'Completed populating the temp ');
131 end if;
132
133 IF (p_move_type = 1) THEN -- SubInv Transfer
134 l_move_txn_type_id := 2;
135 ELSE
136 BEGIN
137 if (l_slog) then
138 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
139 'Selecting the intrasit type from mtl_shipping_network ');
140 end if;
141 SELECT intransit_type INTO l_intransit_type
142 FROM MTL_SHIPPING_NETWORK_VIEW WHERE
143 FROM_organization_id = p_curr_org_id
144 AND to_organization_id = p_dest_org_id;
145
146 exception
147 when no_data_found then
148 raise no_data_found;
149 end;
150
151 IF l_intransit_type = 1 -- Direct Inter Org Transfer
152 THEN l_move_txn_type_id := 3;
153 ELSE l_move_txn_type_id := 21; -- In-Transit Inter Org Transfer
154 END IF;
155 END IF;
156 SELECT Count(*) INTO l_eam_asset_move_count
157 FROM EAM_ASSET_MOVE_TEMP;
158 if (l_slog) then
159 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
160 'The l_eam_asset_move_count is : '||l_eam_asset_move_count);
161 end if;
162
163 SELECT Count(*) INTO l_prepare_count
164 FROM EAM_ASSET_MOVE_TEMP
165 WHERE PREPARE_STATUS = 'N';
166 if (l_slog) then
167 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
168 'The l_prepare_count is : '||l_prepare_count);
169 end if;
170 IF(l_prepare_count = 0 OR p_context = 'M') THEN -- If Transaction has to happen (User Clicked Apply after Validation Messages are displayed
171
172 addAssetsToInterface( l_header_id,
173 l_parent_inventory_item_id,
174 p_curr_org_id,
175 p_curr_subinv_code,
176 p_dest_org_id,
177 p_dest_subinv_code,
178 l_move_txn_type_id,
179 p_shipment_no,
180 p_dest_locator_id,
181 x_locator_error_flag,
182 x_locator_error_messg);
183
184 IF x_locator_error_flag =1 THEN
185 IF x_locator_error_messg='EAM_RET_MAT_LOCATOR_NEEDED' THEN
186 x_return_status:='L';
187 ELSIF x_locator_error_messg= 'EAM_RET_MAT_LOCATOR_RESTRICTED' THEN
188 x_return_status:='R';
189 ELSIF x_locator_error_messg='EAM_INT_LOCATOR_NEEDED' THEN
190 x_return_status:='IL';
191 ELSIF x_locator_error_messg='EAM_INT_LOCATOR_RESTRICTED' THEN
192 x_return_status:='IR';
193 ELSIF x_locator_error_messg='EAM_INT_SUBINVENTORY_NEEDED' THEN
194 x_return_status:='IS';
195 END IF;
196 RETURN;
197 END IF;
198 processAssetMoveTxn(l_header_id, x_return_status , x_return_message );
199 --Dbms_Output.put_line('x_return_status is: '||x_return_status);
200 ELSE
201 x_return_status:='P';
202 x_return_message:='EAM_ASSET_MOVE_PREPARE_WARN';
203 --Dbms_Output.put_line('x_return_status is: '||x_return_status);
204 RETURN ;
205 END IF;
206
207 COMMIT;
208
209 END prepareMoveAsset;
210
211 PROCEDURE getAssetHierarchy
212 (
213 p_parent_instance_id IN NUMBER,
214 p_includeChild IN VARCHAR2,
215 x_asset_move_hierarchy_tbl OUT NOCOPY asset_move_hierarchy_tbl_type,
216 p_curr_org_id IN NUMBER
217 )
218 IS
219 l_parent_inf_rec eam_asset_move_pub.asset_move_hierarchy_REC_TYPE;
220 --logging variables
221 l_api_name constant VARCHAR2(30) := 'getAssetHierarchy';
222 l_module VARCHAR2(200);
223 l_log_level CONSTANT NUMBER := fnd_log.g_current_runtime_level;
224 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level;
225 l_pLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_procedure >= l_log_level;
226 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
227 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
228 l_cursor_count NUMBER :=1;
229 l_parent_object_id NUMBER;
230 CURSOR PARENT_ASSET_HIERARCHY_CUR(p_parent_instance_id IN NUMBER,p_current_org_id IN NUMBER)
231 IS
232 SELECT cii.instance_id ,
233 cii.serial_number ,
234 msn.gen_object_id ,
235 cii.inventory_item_id ,
236 msn.CURRENT_ORGANIZATION_ID,
237 cii.INV_SUBINVENTORY_NAME ,
238 cii.maintainable_flag ,
239 msi.eam_item_type ,
240 mp.MAINT_ORGANIZATION_ID
241 FROM mtl_serial_numbers msn ,
242 mtl_object_genealogy mog,
243 mtl_system_items_b msi ,
244 csi_item_instances cii ,
245 mtl_parameters mp
246 WHERE mog.object_id = msn.gen_object_id
247 AND msn.current_organization_id = msi.organization_id
248 --AND msn.current_organization_id = p_current_org_id
249 AND msi.inventory_item_id = msn.inventory_item_id
250 AND msi.eam_item_type IN (1,3)
251 AND msn.inventory_item_id = cii.inventory_item_id
252 AND msn.serial_number = cii.serial_number
253 AND NVL(cii.active_start_date, sysdate-1) <= sysdate
254 AND NVL(cii.active_end_date, sysdate +1) >= sysdate
255 AND msn.current_organization_id = mp.organization_id
256 AND mp.organization_id = cii.last_vld_organization_id
257 AND sysdate >= NVL(mog.start_date_active(+), sysdate)
258 AND sysdate <= NVL(mog.end_date_active(+), sysdate) START
259 WITH mog.parent_object_id = l_parent_object_id CONNECT BY prior mog.object_id = mog.parent_object_id ;
260 -- Parametrized cursor along with the serial number information with parent_instance and org_id as parameters
261
262 BEGIN
263 --dbms_output.put_line('started getting the asset hierarchy..................');
264 IF (l_ulog) THEN
265 l_module := 'eam.plsql.'|| l_full_name;
266 END IF;
267 IF (l_plog) THEN
268 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module, 'Entering ' || l_full_name);
269 END IF;
270
271 /*if (l_slog) then FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
272 'getting the asset hierarchy of' || l_asset_move_hierarchy_tbl(i).instance_id ); --%%%SAVEPOINT LOOP_START;
273 END IF; */
274
275 BEGIN
276 SELECT msn.GEN_OBJECT_ID
277 INTO l_parent_object_id
278 FROM mtl_serial_numbers msn,
279 csi_item_instances cii
280 WHERE cii.instance_id =p_parent_instance_id
281 AND cii.serial_number=msn.serial_number
282 AND cii.inventory_item_id=msn.inventory_item_id; --Added for 6955393
283 EXCEPTION
284 WHEN NO_DATA_FOUND THEN
285 SELECT 0
286 INTO l_parent_object_id
287 FROM dual;
288
289 if (l_slog) then FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
290 'Exception while searching for parent object id' );
291 END IF;
292 -- dbms_output.put_line('Exception while searching for parent object id');
293 END;
294 --dbms_output.put_line('l_parent_object_id is'||l_parent_object_id);
295
296 if (l_slog) then FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
297 'l_parent_object_id is' || l_parent_object_id );
298 END IF; --Commented for 6955393
299
300 SELECT cii.instance_id ,
301 cii.serial_number ,
302 msn.gen_object_id ,
303 cii.inventory_item_id ,
304 nvl(cii.inv_organization_id, cii.last_vld_organization_id),
305 cii.INV_SUBINVENTORY_NAME ,
306 cii.maintainable_flag ,
307 msi.eam_item_type ,
308 mp.MAINT_ORGANIZATION_ID ,
309 NULL ,
310 NULL
311 INTO l_parent_inf_rec
312 FROM CSI_ITEM_INSTANCES cii,
313 MTL_PARAMETERS mp ,
314 MTL_SERIAL_NUMBERS msn,
315 MTL_SYSTEM_ITEMS_B msi
316 WHERE cii.instance_id =p_parent_instance_id
317 AND cii.SERIAL_NUMBER = msn.SERIAL_NUMBER
318 AND mp.organization_id =cii.last_vld_organization_id
319 AND msn.current_organization_id = msi.organization_id
320 AND msi.inventory_item_id = msn.inventory_item_id
321 AND msn.inventory_item_id =cii.inventory_item_id --6955393
322 AND msi.eam_item_type IN (1,3);
323 -- AND cii.INV_MASTER_ORGANIZATION_ID = msn.CURRENT_ORGANIZATION_ID
324 --AND cii.INVENTORY_ITEM_ID = msn.INVENTORY_ITEM_ID
325 x_asset_move_hierarchy_tbl(0).instance_id := l_parent_inf_rec.instance_id;
326 x_asset_move_hierarchy_tbl(0).serial_number := l_parent_inf_rec.serial_number;
327 x_asset_move_hierarchy_tbl(0).gen_object_id := l_parent_inf_rec.gen_object_id;
328 x_asset_move_hierarchy_tbl(0).inventory_item_id := l_parent_inf_rec.inventory_item_id;
329 x_asset_move_hierarchy_tbl(0).current_org_id := l_parent_inf_rec.current_org_id;
330 x_asset_move_hierarchy_tbl(0).current_subinventory_code :=l_parent_inf_rec.current_subinventory_code;
331 x_asset_move_hierarchy_tbl(0).maintainable_flag :=l_parent_inf_rec.maintainable_flag;
332 x_asset_move_hierarchy_tbl(0).eam_item_type := l_parent_inf_rec.eam_item_type;
333 x_asset_move_hierarchy_tbl(0).maint_org_id := l_parent_inf_rec.maint_org_id;
334 --populating the values of the parent instance id into the asset_hierarchy_table
335
336 IF(p_includeChild='Y') THEN
337
338 if (l_slog) then FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
339 'including the child assets' );
340 END IF;
341 --dbms_output.put_line('including the child assets');
342
343 FOR PARENT_ASSET_HIERARCHY_REC IN PARENT_ASSET_HIERARCHY_CUR(p_parent_instance_id,p_curr_org_id)
344 LOOP
345 EXIT WHEN PARENT_ASSET_HIERARCHY_CUR%NOTFOUND;
346 x_asset_move_hierarchy_tbl(l_cursor_count).instance_id := PARENT_ASSET_HIERARCHY_REC.instance_id;
347 x_asset_move_hierarchy_tbl(l_cursor_count).serial_number := PARENT_ASSET_HIERARCHY_REC.serial_number;
348 x_asset_move_hierarchy_tbl(l_cursor_count).gen_object_id := PARENT_ASSET_HIERARCHY_REC.gen_object_id;
349 x_asset_move_hierarchy_tbl(l_cursor_count).inventory_item_id := PARENT_ASSET_HIERARCHY_REC.inventory_item_id;
350 x_asset_move_hierarchy_tbl(l_cursor_count).current_org_id := PARENT_ASSET_HIERARCHY_REC.current_organization_id;
351 x_asset_move_hierarchy_tbl(l_cursor_count).current_subinventory_code :=PARENT_ASSET_HIERARCHY_REC.INV_SUBINVENTORY_NAME;
352 x_asset_move_hierarchy_tbl(l_cursor_count).maintainable_flag :=PARENT_ASSET_HIERARCHY_REC.maintainable_flag;
353 x_asset_move_hierarchy_tbl(l_cursor_count).eam_item_type := PARENT_ASSET_HIERARCHY_REC.eam_item_type;
354 x_asset_move_hierarchy_tbl(l_cursor_count).maint_org_id := PARENT_ASSET_HIERARCHY_REC.MAINT_ORGANIZATION_ID;
355 -- Dbms_Output.put_line('x_asset_move_hierarchy_tbl(l_cursor_count).instance_id is'||x_asset_move_hierarchy_tbl(l_cursor_count).instance_id);
356 -- dbms_output.put_line('x_asset_move_hierarchy_tbl(0).instance_id is '||x_asset_move_hierarchy_tbl(l_cursor_count).instance_id);
357 --dbms_output.put_line('x_asset_move_hierarchy_tbl(0).current_subinventory_code is '||x_asset_move_hierarchy_tbl(l_cursor_count).current_subinventory_code);
358 l_cursor_count:=l_cursor_count+1;
359 END LOOP ;
360 END IF;
361 if (l_slog) then
362 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,'exiting the getassethierarchy' );
363 END IF;
364 --dbms_output.put_line('exiting the getassethierarchy..................');
365 END getAssetHierarchy ;
366 PROCEDURE populateTemp
367 (
368 p_header_id IN NUMBER,
369 p_asset_move_hierarchy_tbl IN asset_move_hierarchy_tbl_type,
370 p_parent_instance_id IN NUMBER
371 )
372 IS
373 l_parent_object_id NUMBER; --required for verification of parent assets of each child asset
374 l_parent_status VARCHAR2(2);
375 l_parent_msg VARCHAR2(240);
376 l_intermediate_subinventory VARCHAR2(20);
377 --logging variables
378 l_api_name constant VARCHAR2(30) := 'populateTemp';
379 l_module VARCHAR2(200);
380 l_log_level CONSTANT NUMBER := fnd_log.g_current_runtime_level;
381 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level;
382 l_pLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_procedure >= l_log_level;
383 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
384 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
385
386 CURSOR child_parent_cur
387 IS
388 SELECT *
389 FROM EAM_ASSET_MOVE_TEMP FOR UPDATE OF PREPARE_STATUS,
390 PREPARE_MSG;
391 BEGIN
392
393 IF (l_ulog) THEN
394 l_module := 'eam.plsql.'|| l_full_name;
395 END IF;
396
397 IF (l_plog) THEN
398 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module, 'Entering ' || l_full_name);
399 END IF;
400
401 IF (l_slog) then
402 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,'Started populating the temp' );
403 END IF;
404
405 --dbms_output.put_line('Populating Temp');
406 FOR i IN p_asset_move_hierarchy_tbl.FIRST .. p_asset_move_hierarchy_tbl.LAST
407 LOOP
408 BEGIN
409 SELECT INTERMEDIATE_SUBINVENTORY
410 INTO l_intermediate_subinventory FROM WIP_EAM_PARAMETERS
411 WHERE ORGANIZATION_ID = p_asset_move_hierarchy_tbl(i).maint_org_id ;
412
413 INSERT
414 INTO EAM_ASSET_MOVE_TEMP
415 (
416 INSTANCE_ID ,
417 SERIAL_NUMBER ,
418 GEN_OBJECT_ID ,
419 INVENTORY_ITEM_ID ,
420 CURRENT_ORG_ID ,
421 CURRENT_SUBINVENTORY_CODE,
422 EAM_ITEM_TYPE ,
423 MAINT_ORG_ID ,
424 PREPARE_STATUS ,
425 PREPARE_MSG ,
426 TRANSACTION_HEADER_ID
427 )
428 VALUES
429 (
430 p_asset_move_hierarchy_tbl(i).instance_id ,
431 p_asset_move_hierarchy_tbl(i).serial_number ,
432 p_asset_move_hierarchy_tbl(i).gen_object_id ,
433 p_asset_move_hierarchy_tbl(i).inventory_item_id ,
434 p_asset_move_hierarchy_tbl(i).current_org_id ,
435 NVL(p_asset_move_hierarchy_tbl(i).current_subinventory_code,l_intermediate_subinventory),
436 /*for 7370638-AMWB-MR --intermediate_subinventory is the place where the
437 Asset is recieved if the asset is not present any of the subinventory and
438 from there asset is transferred to dest_subinv and/or dest-org.*/
439 NVL(p_asset_move_hierarchy_tbl(i).eam_item_type,1) ,
440 p_asset_move_hierarchy_tbl(i).maint_org_id ,
441 p_asset_move_hierarchy_tbl(i).prepare_status ,
442 p_asset_move_hierarchy_tbl(i).prepare_msg ,
443 p_header_id
444 );
445
446 END;
447 END LOOP;
448 FOR child_parent_rec IN child_parent_cur
449 LOOP
450 EXIT
451 WHEN child_parent_cur%NOTFOUND;
452 IF
453 (
454 child_parent_rec.INSTANCE_ID<>p_parent_instance_id
455 )
456 THEN
457
458 IF (l_slog) then
459 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,'selecting the parent objectId for validating child asset'||child_parent_rec.INSTANCE_ID );
460 END IF;
461
462
463 SELECT parent_object_id
464 INTO l_parent_object_id
465 FROM mtl_object_genealogy
466 WHERE object_id =child_parent_rec.gen_object_id
467 AND START_DATE_ACTIVE<=SYSDATE
468 AND Nvl(end_DATE_ACTIVE,SYSDATE+1)>=sysdate
469 AND PARENT_OBJECT_TYPE = 2; --Added for the bug 7721062
470 --can use the below statement as well for filtering out the records
471 --AND parent_object_id IN (SELECT gen_object_id FROM mtl_serial_numbers)
472 IF (l_slog) then
473 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,'parent objectId for validating the child assest is '||l_parent_object_id);
474 END IF;
475
476 BEGIN
477 SELECT PREPARE_STATUS
478 INTO l_parent_status
479 FROM EAM_ASSET_MOVE_TEMP
480 WHERE gen_object_id=l_parent_object_id;
481 EXCEPTION
482 WHEN NO_DATA_FOUND THEN
483 SELECT 'U' INTO l_parent_status FROM dual;
484 IF (l_slog) then
485 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,'No_data_found exception occured when checking for parent status');
486 END IF;
487 END;
488
489 BEGIN
490 SELECT PREPARE_MSG
491 INTO l_parent_msg
492 FROM EAM_ASSET_MOVE_TEMP
493 WHERE gen_object_id=l_parent_object_id;
494 EXCEPTION
495 WHEN NO_DATA_FOUND THEN
496 SELECT 'Unknown' INTO l_parent_msg FROM dual;
497 IF (l_slog) then
498 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,'No_data_found exception occured when checking for parent message');
499 END IF;
500 END;
501
502 IF(l_parent_status ='N') THEN
503 UPDATE EAM_ASSET_MOVE_TEMP
504 SET PREPARE_STATUS = 'N',
505 PREPARE_MSG ='EAM_PAR_ASSET_FAIL'
506 WHERE CURRENT OF child_parent_cur;
507
508 IF (l_slog) then
509 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,'populated its parent status to this asset as it is not moving');
510 END IF;
511
512 END IF;
513 END IF;
514 END LOOP;
515 IF (l_slog) then
516 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,'Exiting the PopulateTemp' );
517 END IF;
518 -- dbms_output.put_line('End of Populating Temp');
519 END populateTemp;
520
521 PROCEDURE addAssetsToInterface
522 (
523 p_header_id IN NUMBER ,
524 p_inventory_item_id IN NUMBER ,
525 p_CURRENT_ORGANIZATION_ID IN NUMBER ,
526 p_current_subinventory_code IN VARCHAR2 ,
527 p_transfer_organization_id IN NUMBER ,
528 p_transfer_subinventory_code IN VARCHAR2 ,
529 p_transaction_type_id IN NUMBER ,
530 p_shipment_number IN VARCHAR2 := NULL,
531 p_transfer_locator_id IN NUMBER :=NULL,
532 x_locator_error_flag OUT NOCOPY NUMBER,
533 x_locator_error_mssg OUT NOCOPY VARCHAR2)
534 IS
535 l_EAM_ASSET_MOVE_REC EAM_ASSET_MOVE_TEMP%ROWTYPE ;
536 l_txn_if_id NUMBER;
537 l_SERIAL_NUMBER VARCHAR2(30);
538 l_Transaction_UOM mtl_system_items_b_kfv.primary_uom_code%type;
539 l_serial_number_control_code mtl_system_items_b_kfv.serial_number_control_code%TYPE;
540 l_Item_Revision CSI_ITEM_INSTANCES.INVENTORY_REVISION%TYPE;
541 l_Transaction_Quantity NUMBER;
542 x_Message VARCHAR2(240);
543 x_Status VARCHAR2(30);
544 l_lot_control_code NUMBER;
545 l_from_ser_number VARCHAR2(30);
546 l_to_ser_number VARCHAR2(30);
547 l_temp_header_id NUMBER;
548 l_current_subinventory_code VARCHAR2(240);
549 l_CURRENT_ORGANIZATION_ID NUMBER;
550 l_inventory_item_id NUMBER;
551 l_current_locator_id NUMBER;
552 l_intransit_type_for_child NUMBER;
553 l_transaction_type_id NUMBER;
554
555 -- logging variables
556 l_api_name constant VARCHAR2(30) := 'addAssetsToInterface';
557 l_module VARCHAR2(200);
558 l_log_level CONSTANT NUMBER := fnd_log.g_current_runtime_level;
559 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level;
560 l_pLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_procedure >= l_log_level;
561 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
562 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
563 l_quantity NUMBER;
564
565 --locator control verification variables
566 x_verif_locator_ctrl NUMBER ; -- Holds the Locator Control information
567 x_verif_locator_error_flag NUMBER; -- returns 0 if no error , >1 if any error .
568 x_verif_locator_error_mssg VARCHAR2(200);
569
570 --variables added for 7370638 (Enhancement on AMWB)
571 l_misc_rec_asset_count NUMBER;
572 l_transaction_batch_id NUMBER;
573 l_intermediate_subinventory varchar2(10);
574 l_intermediate_locator varchar2(20);
575 l_intermediate_locator_id NUMBER;
576 x_verif_loc_ctrl_for_MR NUMBER ;
577 x_verif_loc_err_flag_for_MR NUMBER;
578 x_verif_loc_err_mssg_for_MR VARCHAR2(200);
579 l_asset_count_for_MR NUMBER;
580 l_maint_organization_id NUMBER;
581 --variables added for 7370638 (Enhancement on AMWB)
582 l_transfer_organization_id NUMBER;
583
584 -- Define a cursor with all the entries from Temp Table whose move status is 'Y' and 'MR'
585 CURSOR validAssets_cur
586 IS
587 SELECT *
588 FROM EAM_ASSET_MOVE_TEMP
589 WHERE prepare_status IN ('Y','MR')
590 AND TRANSACTION_HEADER_ID = p_header_id FOR UPDATE OF TRANSACTION_INTERFACE_ID;
591 BEGIN
592 --Dbms_Output.put_line('adding assets to interface tables');
593 -- Dbms_Output.put_line('p_header_id is '||p_header_id);
594 IF (l_ulog) THEN
595 l_module := 'eam.plsql.'|| l_full_name;
596 END IF;
597 IF (l_plog) THEN
598 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,'Entering ' || l_full_name);
599 END IF;
600
601 IF (l_slog) then
602 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,'p_header_id is '||p_header_id );
603 END IF;
604 l_transfer_organization_id := p_transfer_organization_id;
605
606 /* Check for Locator Control which could be defined
607 at 3 level Organization,Subinventory,Item .
608 */--Similar to code from the package--eam_mtl_txn_process i.e., EAMMTTXB.pls
609
610 --Dbms_Output.put_line('before entering Get_LocatorControl_Code');
611 Get_LocatorControl_Code(
612 l_transfer_organization_id,
613 p_transfer_subinventory_code,
614 p_inventory_item_id,
615 32,--p_action in the proc
616 x_verif_locator_ctrl,
617 x_verif_locator_error_flag,
618 x_verif_locator_error_mssg);
619
620 -- Dbms_Output.put_line('x_verif_locator_ctrl is '||x_verif_locator_ctrl);
621 -- Dbms_Output.put_line('x_verif_locator_error_flag is'||x_verif_locator_error_flag );
622 -- Dbms_Output.put_line('x_verif_locator_error_mssg is'||x_verif_locator_error_mssg) ;
623
624 if(x_verif_locator_error_flag <> 0) THEN
625
626 return;
627 end if;
628
629 -- if the locator control is Predefined or Dynamic Entry
630 if(x_verif_locator_ctrl = 2 or x_verif_locator_ctrl = 3) then
631 if(p_transfer_locator_id IS NULL) then
632 x_locator_error_flag := 1;
633 x_locator_error_mssg := 'EAM_RET_MAT_LOCATOR_NEEDED';
634
635 return;
636 end if;
637 elsif(x_verif_locator_ctrl = 1) then -- If the locator control is NOControl
638 if(p_transfer_locator_id IS NOT NULL) then
639 x_locator_error_flag := 1;
640 x_locator_error_mssg := 'EAM_RET_MAT_LOCATOR_RESTRICTED';
641
642 return;
643 end if;
644 end if; -- end of locator_control checkif for Asset Move Transfers
645
646
647
648 BEGIN
649 select MAINT_ORGANIZATION_ID into l_maint_organization_id
650 from MTL_PARAMETERS
651 where ORGANIZATION_ID = P_CURRENT_ORGANIZATION_ID;
652 --Added for the bug 7681240
653 select INTERMEDIATE_SUBINVENTORY into l_intermediate_subinventory
654 from wip_eam_parameters
655 where ORGANIZATION_ID = l_maint_organization_id;
656 EXCEPTION
657 WHEN No_Data_Found THEN
658 l_intermediate_subinventory := NULL;
659 END;
660
661 BEGIN
662 select INTERMEDIATE_LOCATOR into l_intermediate_locator
663 from wip_eam_parameters
664 where ORGANIZATION_ID = l_maint_organization_id;
665 EXCEPTION
666 WHEN No_Data_Found THEN
667 l_intermediate_locator := NULL;
668 END;
669 BEGIN
670 SELECT inventory_location_id
671 INTO l_intermediate_locator_id
672 FROM mtl_item_locations_kfv
673 WHERE concatenated_segments = l_intermediate_locator
674 AND organization_id = l_maint_organization_id;
675 EXCEPTION
676 WHEN No_Data_Found THEN
677 l_intermediate_locator_id := NULL;
678 END;
679
680
681
682 --Locator control check up for intermediate subinventories and intermediate locators For Misc Rec for 7370638
683
684 SELECT COUNT(*) into l_asset_count_for_MR FROM EAM_ASSET_MOVE_TEMP WHERE prepare_status LIKE 'MR';
685 --count required to verify whether any assets that are ready for Misc Receipt
686
687 if(l_asset_count_for_MR > 0) THEN
688 if (l_intermediate_subinventory IS NULL) THEN
689 x_locator_error_flag := 1;
690 x_locator_error_mssg := 'EAM_INT_SUBINVENTORY_NEEDED';
691 return; --if the Intermediate subinventory in eAM parameters form
692 END IF; --is null..Error message is thrown for 7370638
693 /*Locator control verification for the intermediate subinventory,intermediate locator and Inventory Item combination */
694 Get_LocatorControl_Code(
695 l_maint_organization_id, --changed for the bug 7681240
696 l_intermediate_subinventory,
697 p_inventory_item_id,
698 32,--p_action in the proc
699 x_verif_loc_ctrl_for_MR,
700 x_verif_loc_err_flag_for_MR,
701 x_verif_loc_err_mssg_for_MR);
702
703
704 if(x_verif_loc_err_flag_for_MR <> 0) THEN
705 return;
706 end if;
707
708 -- if the locator control is Predefined or Dynamic Entry
709 if(x_verif_loc_ctrl_for_MR = 2 or x_verif_loc_ctrl_for_MR = 3) then
710 if(l_intermediate_locator IS NULL) then
711 x_locator_error_flag := 1;
712 x_locator_error_mssg := 'EAM_INT_LOCATOR_NEEDED';
713
714 return;
715 end if;
716 elsif(x_verif_loc_ctrl_for_MR = 1) then -- If the locator control is NOControl
717 if(l_intermediate_locator IS NOT NULL) then
718 x_locator_error_flag := 1;
719 x_locator_error_mssg := 'EAM_INT_LOCATOR_RESTRICTED';
720
721 return;
722 end if;
723 end if; -- end of locator_control checkif for Miscellanous Receipt
724 end if;
725
726 FOR validAssets_rec IN validAssets_cur
727 LOOP
728 EXIT WHEN validAssets_cur%NOTFOUND;
729 -- get serial number from the cursor
730 l_serial_number :=validAssets_rec.serial_number;
731 l_from_ser_number:=validAssets_rec.serial_number;
732 l_to_ser_number :=validAssets_rec.serial_number;
733 l_CURRENT_ORGANIZATION_ID:=validAssets_rec.CURRENT_ORG_ID;
734 --make nvl(validAssets_rec.CURRENT_SUBINVENTORY_CODE,WIP_EAM_PARAMETERS.default_Subinventory) for the current SI
735
736 l_current_subinventory_code :=nvl(validAssets_rec.CURRENT_SUBINVENTORY_CODE,l_intermediate_subinventory);
737 l_transaction_type_id:=p_transaction_type_id;
738
739 BEGIN
740 select INV_LOCATOR_ID INTO l_current_locator_id
741 from csi_item_instances where INSTANCE_ID=validAssets_rec.instance_id;
742 EXCEPTION
743 WHEN NO_DATA_FOUND THEN
744 l_current_locator_id:=NULL;
745 END;
746
747 IF(validAssets_rec.prepare_status='MR') then --Call For Misc Receipt for 7370638
748 --for 7370638
749 SELECT mtl_material_transactions_s.nextval
750 INTO l_transaction_batch_id
751 FROM dual;
752
753 addAssetsForMiscReceipt(p_header_id,
754 l_transaction_batch_id,
755 l_serial_number,
756 l_CURRENT_ORGANIZATION_ID,
757 validAssets_rec.inventory_item_id,
758 l_current_subinventory_code,
759 l_intermediate_locator_id--need to read the value from EAM-parameters
760 );
761
762 IF (l_current_locator_id IS NULL) THEN
763 l_current_locator_id := l_intermediate_locator_id;
764 END IF;
765 -- Added for bug 7758197
766 END IF; --End of call for misc rec
767
768 BEGIN
769 SELECT primary_uom_code ,
770 serial_number_control_code,
771 LOT_CONTROL_CODE
772 INTO l_Transaction_UOM ,
773 l_serial_number_control_code,
774 l_lot_control_code
775 FROM mtl_system_items_b
776 WHERE inventory_item_id=p_inventory_item_id
777 AND organization_id =p_CURRENT_ORGANIZATION_ID;
778
779 EXCEPTION
780 WHEN NO_DATA_FOUND THEN
781 l_Transaction_UOM:='Ea';
782 --Dbms_Output.put_line('ecxeption occured while selecting');
783 END;
784 -- SELECT INVENTORY_REVISION INTO l_Item_Revision FROM CSI_ITEM_INSTANCES WHERE INVENTORY_ITEM_ID=p_inventory_item_id ;
785 SELECT mtl_material_transactions_s.nextval
786 INTO l_temp_header_id
787 FROM dual;
788 --dbms_output.put_line('l_serial_number_control_code IS'||' '||l_serial_number_control_code);
789 Begin
790 SELECT inventory_item_id INTO l_inventory_item_id
791 FROM csi_item_instances
792 WHERE instance_id = validAssets_rec.instance_id;
793 exception
794 when no_data_found then
795 raise no_data_found;
796 end;
797
798 INV_TRANSACTIONS.G_Header_ID := p_header_id;
799 INV_TRANSACTIONS.G_Interface_ID := l_temp_header_id;
800 UPDATE EAM_ASSET_MOVE_TEMP
801 SET TRANSACTION_INTERFACE_ID = l_temp_header_id
802 WHERE CURRENT OF validAssets_cur;
803 IF(l_serial_number_control_code <> 1) THEN
804 SELECT mtl_material_transactions_s.nextval
805 INTO INV_TRANSACTIONS.G_Serial_ID
806 FROM dual;
807 END IF;
808
809
810 -- insert into MTI, MSNI
811 --dbms_output.put_line('header_id IS' ||INV_TRANSACTIONS.G_Header_ID);
812 IF (l_slog) then
813 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,'Calling the procedure INV_TRANSACTIONS.LINE_INTERFACE_INSERT' );
814 END IF;
815
816 IF(p_transaction_type_id=2) THEN --if subinventory transfer
817 IF(p_CURRENT_ORGANIZATION_ID<>l_CURRENT_ORGANIZATION_ID)THEN
818 --source organisation_id for parent asset and child asset are not equal
819 --that means In the case when the child asset is of some production_org and parent asset
820 --is in the maintenance org of that prod_org
821 --In this case the move type for parent asset is subinventory only but for the child asset becomes
822 --interorg type between the production org and maintenance org.
823 --the parent asset in one maint org and child asset in another maint org in a given hierarchy is
824 --however not supported in Asset Move Work Bench
825 begin
826
827 SELECT
828 intransit_type
829 INTO
830 l_intransit_type_for_child
831 FROM MTL_SHIPPING_NETWORK_VIEW
832 WHERE from_organization_id=l_CURRENT_ORGANIZATION_ID
833 AND TO_ORGANIZATION_ID=p_CURRENT_ORGANIZATION_ID;
834
835 exception
836 when no_data_found then
837 begin
838 raise no_data_found; --needs to handle the exception since no shipping network is enbled
839 --between the production org and maint org
840 IF (l_slog) then
841 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,'Null Exception ocuured as no shipping network is enabled between organisation ids'||l_CURRENT_ORGANIZATION_ID||'AND'||p_CURRENT_ORGANIZATION_ID );
842 END IF;
843 end;
844 end;
845
846 IF(l_intransit_type_for_child=1) THEN -- Direct Inter Org Transfer
847 l_transaction_type_id:=3;
848 ELSE l_transaction_type_id:=21;-- In-Transit Inter Org Transfer
849 END IF;
850
851 END IF;
852 END IF;
853
854 IF(l_transaction_type_id=3) THEN--for direct inter_org transfer the txn quantity is positve
855 l_quantity:=1;
856 ELSE l_quantity:=-1;
857 END IF;
858
859 IF(validAssets_rec.prepare_status='MR') THEN --As the Asset is Recieved into Maintenance Org
860 --Making the Current Organisation as Maint Org
861 SELECT maint_organization_id INTO l_CURRENT_ORGANIZATION_ID
862 FROM MTL_PARAMETERS
863 WHERE organization_id= l_CURRENT_ORGANIZATION_ID;
864
865 If(l_transfer_organization_id = l_CURRENT_ORGANIZATION_ID) then
866 l_transfer_organization_id := NULL;
867 l_transaction_type_id := 2;
868 END IF;
869 --Added for the 7833252
870 END IF;
871
872 INV_TRANSACTIONS.LINE_INTERFACE_INSERT(
873 l_inventory_item_id,
874 NULL, --revision
875 l_CURRENT_ORGANIZATION_ID,
876 NULL, -- l_Transaction_Source_Id,
877 NULL, -- l_Transaction_action_Id,
878 l_current_subinventory_code ,
879 p_transfer_subinventory_code ,
880 l_current_locator_id,--NULL, --l_From_Locator_Id, can be null and cant be null
881 p_transfer_locator_id, --l_To_Locator_Id, from EAMMATTB.pls as in eam_mtl_txn_process.PROCESSMTLTXN()
882 l_transfer_organization_id,
883 l_transaction_type_id,
884 NULL, --l_Transaction_Source_Type_Id
885 l_quantity, --1 (quantity default)
886 l_Transaction_UOM, --from select query
887 SYSDATE,
888 NULL, --l_Reason_Id
889 FND_GLOBAL.USER_ID,
890 x_Message,
891 x_Status
892 );
893
894 --if transaction type is of interorg-intransit then we need to enter the shipment number into MTI table explicitly.
895
896 IF(p_transaction_type_id=21) THEN
897 --code for entering the shipment values into MTI
898 UPDATE MTL_TRANSACTIONS_INTERFACE
899 SET SHIPMENT_NUMBER=p_shipment_number where TRANSACTION_INTERFACE_ID=INV_TRANSACTIONS.G_Interface_ID
900 AND TRANSACTION_HEADER_ID=INV_TRANSACTIONS.G_Header_ID;
901
902 END IF;
903
904 IF(validAssets_rec.prepare_status='MR') THEN --for --for 7370638 Batch seq to be 2 as the transfer is dependent on the Misc. receipt
905 UPDATE MTL_TRANSACTIONS_INTERFACE
906 SET TRANSACTION_BATCH_ID = l_transaction_batch_id,
907 TRANSACTION_BATCH_SEQ = 2
908 WHERE TRANSACTION_INTERFACE_ID=INV_TRANSACTIONS.G_Interface_ID
909 AND TRANSACTION_HEADER_ID=INV_TRANSACTIONS.G_Header_ID;
910 END IF;
911
912 IF(l_serial_number_control_code <> 1
913 AND l_from_ser_number IS NOT NULL
914 AND l_to_ser_number IS NOT NULL AND
915 l_serial_number_control_code IS NOT NULL) THEN
916 --dbms_output.put_line('serial_id IS' ||INV_TRANSACTIONS.G_Serial_ID);
917 INV_TRANSACTIONS.SERIAL_INTERFACE_INSERT(
918 l_from_ser_number ,
919 l_to_ser_number ,
920 FND_GLOBAL.USER_ID ,
921 l_lot_control_code
922 );
923 END IF;
924 l_transfer_organization_id := p_transfer_organization_id;
925 END LOOP;
926
927 IF (l_plog) THEN
928 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module, 'Exiting ' || l_full_name);
929 END IF;
930
931 --Dbms_Output.put_line('end of add assets to interface');
932 END addAssetsToInterface;
933
934 PROCEDURE processAssetMoveTxn
935 (
936 p_txn_header_id IN NUMBER,
937 x_return_status OUT NOCOPY VARCHAR2,
938 x_return_message OUT NOCOPY VARCHAR2
939 )
940 IS
941 l_api_version NUMBER ;
942 l_init_msg_list VARCHAR2(30);
943 l_commit VARCHAR2(30);
944 l_validation_level NUMBER ;
945 x_ret_status VARCHAR2(240);
946 x_mssg_count NUMBER ;
947 x_error_mssg VARCHAR2(240);
948 x_transs_count NUMBER ;
949 l_table NUMBER ;
950 l_txn_count NUMBER;
951 -- x_process_txn_ret_status VARCHAR2(240);
952 --logging variables
953 l_api_name constant VARCHAR2(30) := 'processAssetMoveTxn';
954 l_module VARCHAR2(200);
955 l_log_level CONSTANT NUMBER := fnd_log.g_current_runtime_level;
956 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level;
957 l_pLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_procedure >= l_log_level;
958 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
959 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
960
961 CURSOR Txn_STAT_MMT_CUR(p_txn_header_id IN NUMBER) IS
962 SELECT MTI.TRANSACTION_HEADER_ID ,
963 MTI.TRANSACTION_INTERFACE_ID,
964 MTI.ERROR_CODE ,
965 MTI.ERROR_EXPLANATION
966 FROM MTL_TRANSACTIONS_INTERFACE MTI
967 WHERE MTI.TRANSACTION_HEADER_ID = p_txn_header_id ;
968
969 BEGIN
970 --Dbms_Output.put_line('begining of processAssetMoveTxn');
971 IF (l_ulog) THEN
972 l_module := 'eam.plsql.'|| l_full_name;
973 END IF;
974 IF (l_plog) THEN
975 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module, 'Entering ' || l_full_name);
976 END IF;
977 --Dbms_Output.put_line('Calling the INV transaction process API for the above txn_header_id');
978 -- Call the INV transaction process API for the above txn_header_id
979 l_txn_count := INV_TXN_MANAGER_PUB.process_Transactions
980 (
981 p_api_version => 1.0,
982 p_header_id => p_txn_header_id,
983 p_table => 1, -- meant for process from MTI table.
984 x_return_status => x_return_status,
985 x_msg_count => x_mssg_count,
986 x_msg_data => x_error_mssg,
987 x_trans_count => x_transs_count
988 );
989 -- dbms_output.put_line('x_return_status: '||x_return_status); --status of whole txn
990 -- dbms_output.put_line(x_transs_count); --no of moves gone for
991 -- dbms_output.put_line('l_txn_count: '||l_txn_count); --number of moves successful
992 -- dbms_output.put_line(x_error_mssg); --error message
993
994 IF(x_return_status IS NULL) THEN
995 x_return_status:='N';
996 x_return_message:=x_return_message;
997 END IF;
998 --dbms_output.put_line('x_return_status: '||x_return_status);
999
1000
1001 -- Get the Transaction Status from the respective tables
1002 -- Update the Temporary Table with the transaction status
1003 --*******************important part**********************************
1004 -- dbms_output.put_line('updating EAM_ASSET_MOVE_TEMP after');
1005 FOR Txn_STAT_MTT_REC IN Txn_STAT_MMT_CUR(p_txn_header_id) LOOP
1006 EXIT WHEN Txn_STAT_MMT_CUR%NOTFOUND;
1007 UPDATE EAM_ASSET_MOVE_TEMP
1008 SET TRANSACTION_STATUS ='Failed',--NVL(Txn_STAT_MTT_REC.ERROR_CODE,'YES'),
1009 TRANSACTION_MSG =Txn_STAT_MTT_REC.ERROR_EXPLANATION
1010 WHERE TRANSACTION_INTERFACE_ID = Txn_STAT_MTT_REC.TRANSACTION_INTERFACE_ID;
1011 -- AND instance_id = p_instance_id;
1012 END LOOP;
1013 COMMIT ;
1014
1015 IF (l_plog) THEN
1016 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module, 'Exiting ' || l_full_name);
1017 END IF;
1018
1019 IF (l_plog) THEN
1020 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module, 'Exiting ' || g_pkg_name);
1021 END IF;
1022
1023 END processAssetMoveTxn;
1024 Procedure Get_LocatorControl_Code(
1025 p_org IN NUMBER,
1026 p_subinv IN VARCHAR2,
1027 p_item_id IN NUMBER,
1028 p_action IN NUMBER,
1029 x_locator_ctrl OUT NOCOPY NUMBER,
1030 x_error_flag OUT NOCOPY NUMBER, -- returns 0 if no error ,1 if any error .
1031 x_error_mssg OUT NOCOPY VARCHAR2
1032 ) IS
1033 x_org_ctrl NUMBER;
1034 x_sub_ctrl NUMBER;
1035 x_item_ctrl NUMBER;
1036 x_neg_flag NUMBER;
1037 x_restrict_flag NUMBER;
1038 BEGIN
1039
1040 -- initialize the output .
1041 x_error_flag := 0;
1042 x_error_mssg := '';
1043 --Dbms_Output.put_line('Get_LocatorControl_Code verification');
1044
1045 -- retrive organization level control information
1046 Begin
1047 SELECT
1048 negative_inv_receipt_code,stock_locator_control_code into
1049 x_neg_flag,x_org_ctrl FROM MTL_PARAMETERS
1050 WHERE
1051 organization_id = p_org;
1052 Exception
1053 When no_data_found then
1054 x_error_flag := 1;
1055 x_error_mssg := 'EAM_INVALID_ORGANIZATION';
1056 End;
1057 --Dbms_Output.put_line('Get_LocatorControl_Code- EAM_INVALID_ORGANIZATION');
1058
1059 -- retrive subinventory level control information
1060 Begin
1061 SELECT
1062 locator_type into x_sub_ctrl
1063 FROM MTL_SECONDARY_INVENTORIES
1064 WHERE
1065 organization_id = p_org and
1066 secondary_inventory_name = p_subinv ;
1067 Exception
1068 When no_data_found then
1069 x_error_flag := 1;
1070 x_error_mssg := 'EAM_RET_MAT_INVALID_SUBINV1';
1071 End;
1072 --Dbms_Output.put_line('Get_LocatorControl_Code- EAM_RET_MAT_INVALID_SUBINV1');
1073 -- retrive Item level control information
1074 Begin
1075 SELECT
1076 location_control_code,restrict_locators_code into
1077 x_item_ctrl,x_restrict_flag
1078 FROM MTL_SYSTEM_ITEMS
1079 WHERE
1080 inventory_item_id = p_item_id and
1081 organization_id = p_org;
1082 Exception
1083 When no_data_found then
1084 x_error_flag := 1;
1085 x_error_mssg := 'EAM_NO_ITEM_FOUND';
1086 End;
1087 --Dbms_Output.put_line('Get_LocatorControl_Code - EAM_NO_ITEM_FOUND');
1088
1089 if(x_org_ctrl = 1) then
1090 x_locator_ctrl := 1;
1091 elsif(x_org_ctrl = 2) then
1092 x_locator_ctrl := 2;
1093 elsif(x_org_ctrl = 3) then
1094 x_locator_ctrl := 3;
1095 if(Dynamic_Entry_Not_Allowed(x_restrict_flag,
1096 x_neg_flag,p_action)) then
1097 x_locator_ctrl := 2;
1098 end if;
1099 elsif(x_org_ctrl = 4) then
1100 if(x_sub_ctrl = 1) then
1101 x_locator_ctrl := 1;
1102 elsif(x_sub_ctrl = 2) then
1103 x_locator_ctrl := 2;
1104 elsif(x_sub_ctrl = 3) then
1105 x_locator_ctrl := 3;
1106 if(dynamic_entry_not_allowed(x_restrict_flag,
1107 x_neg_flag,p_action)) then
1108 x_locator_ctrl := 2;
1109 end if;
1110 elsif(x_sub_ctrl = 5) then
1111 if(x_item_ctrl = 1) then
1112 x_locator_ctrl := 1;
1113 elsif(x_item_ctrl = 2) then
1114 x_locator_ctrl := 2;
1115 elsif(x_item_ctrl = 3) then
1116 x_locator_ctrl := 3;
1117 if(dynamic_entry_not_allowed(x_restrict_flag,
1118 x_neg_flag,p_action)) then
1119 x_locator_ctrl := 2;
1120 end if;
1121 elsif(x_item_ctrl IS NULL) then
1122 x_locator_ctrl := x_sub_ctrl;
1123 else
1124 x_error_flag := 1;
1125 x_error_mssg := 'EAM_RET_MAT_INVALID_LOCATOR';
1126 return ;
1127 end if;
1128
1129 else
1130 x_error_flag := 1;
1131 x_error_mssg := 'EAM_RET_MAT_INVALID_SUBINV';
1132 return ;
1133 end if;
1134 else
1135 x_error_flag := 1;
1136 x_error_mssg := 'EAM_RET_MAT_INVALID_ORG';
1137 return ;
1138 end if;
1139
1140
1141 END Get_LocatorControl_Code; -- end of get_locatorcontrol_code procedure
1142
1143 Function Dynamic_Entry_Not_Allowed(
1144 p_restrict_flag IN NUMBER,
1145 p_neg_flag IN NUMBER,
1146 p_action IN NUMBER) return Boolean IS
1147 BEGIN
1148
1149 if(p_restrict_flag = 2 or p_restrict_flag = null) then
1150 if(p_neg_flag = 2) then
1151 if(p_action = 1 or p_action = 2 or p_action = 3 or
1152 p_action = 21 or p_action = 30 or p_action = 32) then
1153 return TRUE;
1154 end if;
1155 else
1156 return FALSE;
1157 end if; -- end of neg_flag check
1158 elsif(p_restrict_flag = 1) then
1159 return TRUE;
1160 end if;
1161 return TRUE;
1162
1163
1164 End Dynamic_Entry_Not_Allowed ;
1165
1166 --Procedure for performing the Miscellanoeus receipt on assets
1167 -- for 7370638
1168
1169 PROCEDURE addAssetsForMiscReceipt(p_header_id IN NUMBER,
1170 p_batch_transaction_id IN NUMBER,
1171 p_serial_number IN VARCHAR2,
1172 p_CURRENT_ORGANIZATION_ID IN NUMBER,
1173 p_inventory_item_id IN NUMBER,
1174 p_current_subinventory_code IN VARCHAR2,
1175 p_intermediate_locator_id IN NUMBER
1176 ) IS
1177
1178
1179
1180 l_org_id NUMBER;
1181 l_qty NUMBER;
1182 l_uom VARCHAR2(3);
1183 l_txn_if_id NUMBER;
1184 l_sysdate DATE;
1185 l_acc_per_id NUMBER;
1186
1187 BEGIN
1188 --Dbms_Output.PUT_LINE( ' entered the addassetsformiscrec');
1189
1190 l_qty := 1;
1191 l_uom := 'Ea';
1192 l_sysdate := SYSDATE-1;
1193
1194 SELECT maint_organization_id INTO l_org_id
1195 FROM MTL_PARAMETERS WHERE
1196 organization_id= p_CURRENT_ORGANIZATION_ID;
1197
1198 SELECT ACCT_PERIOD_ID
1199 INTO l_acc_per_id
1200 FROM ORG_ACCT_PERIODS
1201 WHERE PERIOD_CLOSE_DATE IS NULL
1202 AND ORGANIZATION_ID = l_org_id
1203 AND (SCHEDULE_CLOSE_DATE + 1) > l_sysdate
1204 AND PERIOD_START_DATE <= l_sysdate ;
1205
1206 SELECT APPS.mtl_material_transactions_s.NEXTVAL
1207 INTO l_txn_if_id
1208 FROM sys.dual;
1209
1210 INSERT INTO mtl_transactions_interface
1211 (transaction_header_id,
1212 source_code,
1213 source_line_id,
1214 source_header_id,
1215 process_flag ,
1216 transaction_mode,
1217 lock_flag,
1218 last_update_date,
1219 last_updated_by,
1220 creation_date,
1221 created_by,
1222 organization_id,
1223 inventory_item_id,
1224 --distribution_account_id,
1225 subinventory_code,
1226 locator_id,
1227 transaction_quantity,
1228 transaction_uom,
1229 transaction_date,
1230 transaction_type_id,
1231 transaction_action_id,
1232 transaction_source_type_id,
1233 transaction_interface_id,
1234 transaction_batch_id,
1235 TRANSACTION_BATCH_SEQ
1236 )
1237 VALUES
1238 (p_header_id,
1239 1,
1240 -1,
1241 -1,
1242 1,
1243 3,
1244 2,
1245 l_sysdate,
1246 FND_GLOBAL.USER_ID,
1247 l_sysdate,
1248 FND_GLOBAL.USER_ID,
1249 l_org_id,
1250 p_inventory_item_id,
1251 --20594,
1252 p_current_subinventory_code,
1253 p_intermediate_locator_id,
1254 l_qty,
1255 l_uom,
1256 l_sysdate,
1257 42,
1258 27,
1259 13,
1260 l_txn_if_id,
1261 p_batch_transaction_id,
1262 1
1263 );
1264
1265 INSERT INTO mtl_serial_numbers_interface
1266 (transaction_interface_id,
1267 SOURCE_CODE,
1268 SOURCE_LINE_ID,
1269 last_update_date,
1270 last_updated_by,
1271 creation_date,
1272 created_by,
1273 fm_serial_number,
1274 to_serial_number,
1275 ERROR_CODE,
1276 PROCESS_FLAG)
1277 VALUES
1278 (l_txn_if_id, --l_txn_ser_if_id
1279 'HJO', --optional value
1280 1,
1281 l_sysdate,
1282 FND_GLOBAL.USER_ID,
1283 l_sysdate,
1284 FND_GLOBAL.USER_ID,
1285 p_serial_number,
1286 p_serial_number,
1287 NULL,
1288 1
1289 );
1290
1291 EXCEPTION
1292 WHEN NO_DATA_FOUND THEN
1293 raise no_data_found;
1294
1295 END addAssetsForMiscReceipt;
1296
1297
1298 END eam_asset_move_pub;
[Home] [Help]