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