[Home] [Help]
PACKAGE BODY: APPS.PJM_MASS_TRANSFER_PUB
Source
1 PACKAGE BODY PJM_MASS_TRANSFER_PUB AS
2 /* $Header: PJMMXFRB.pls 115.15 2004/01/07 22:36:04 alaw noship $ */
3
4 --
5 -- Global Declarations
6 --
7 G_PKG_NAME VARCHAR2(30) := 'PJM_MASS_TRANSFER_PUB';
8 G_MODULE VARCHAR2(80) := 'pjm.plsql.pjm_mass_transfer_pub';
9
10 --
11 -- Private Procedures
12 --
13 FUNCTION Get_Acct_Period
14 ( P_Organization_ID IN NUMBER
15 , P_Txn_Date IN DATE
16 ) RETURN NUMBER IS
17
18 acct_period_id NUMBER;
19 open_past_period BOOLEAN := fnd_profile.value('TRANSACTION_DATE') IN (3,4);
20
21 BEGIN
22
23 INVTTMTX.TDATECHK
24 ( P_Organization_ID
25 , trunc(P_Txn_Date)
26 , acct_period_id
27 , open_past_period
28 );
29
30 RETURN( acct_period_id );
31
32 EXCEPTION
33 WHEN OTHERS THEN
34 RETURN( -1 );
35
36 END Get_Acct_Period;
37
38
39 FUNCTION Get_Txn_Header
40 RETURN NUMBER IS
41
42 txn_header_id NUMBER;
43
44 BEGIN
45
46 SELECT mtl_material_transactions_s.nextval
47 INTO txn_header_id
48 FROM dual;
49
50 RETURN( txn_header_id );
51
52 END Get_Txn_Header;
53
54
55 PROCEDURE Create_Transfer_Transaction
56 ( P_Txn_Header_ID IN NUMBER
57 , P_Process_Mode IN NUMBER
58 , P_Organization_ID IN NUMBER
59 , P_Item_ID IN NUMBER
60 , P_Revision IN VARCHAR2
61 , P_Lot_Number IN VARCHAR2
62 , P_Txn_Quantity IN NUMBER
63 , P_Subinventory_Code IN VARCHAR2
64 , P_From_Locator_ID IN NUMBER
65 , P_To_Locator_ID IN NUMBER
66 , P_Txn_Date IN DATE
67 , P_Acct_Period_ID IN NUMBER
68 , P_Txn_Reason_ID IN NUMBER
69 , P_Txn_Reference IN VARCHAR2
70 , P_DFF IN DFF_Rec_Type
71 , X_Return_Status OUT NOCOPY VARCHAR2
72 , X_Msg_Count OUT NOCOPY NUMBER
73 , X_Msg_Data OUT NOCOPY VARCHAR2
74 ) IS
75
76 user_id NUMBER := FND_GLOBAL.user_id;
77 login_id NUMBER := FND_GLOBAL.login_id;
78 txn_xface_id NUMBER;
79 ser_txn_id NUMBER;
80 primary_uom VARCHAR2(3);
81 lot_control VARCHAR2(1);
82 serial_control VARCHAR2(1);
83 progress NUMBER;
84
85 BEGIN
86 --
87 -- Standard Start of API savepoint
88 --
89 SAVEPOINT create_txfr_txn;
90
91 PJM_DEBUG.Debug( 'CREATE_TRANSFER_TRANSACTION'
92 , G_MODULE , FND_LOG.LEVEL_PROCEDURE );
93
94 X_Return_Status := FND_API.G_RET_STS_SUCCESS;
95
96 --
97 -- Fetching value for TRANSACTION_TEMP_ID
98 --
99 txn_xface_id := Get_Txn_Header;
100
101 --
102 -- Fetching Primary UOM, lot control and serial control code
103 -- from item
104 --
105 progress := 10;
106
107 SELECT primary_uom_code
108 , decode(lot_control_code , 1 , 'N' , 'Y')
109 , decode(serial_number_control_code , 1 , 'N' , 'Y')
110 INTO primary_uom
111 , lot_control
112 , serial_control
113 FROM mtl_system_items
114 WHERE organization_id = P_Organization_ID
115 AND inventory_item_id = P_Item_ID;
116
117 PJM_DEBUG.Debug(
118 'txn_xface_id => ' || txn_xface_id ||
119 ', lot_control => ' || lot_control ||
120 ', serial_control => ' || serial_control);
121
122 --
123 -- Creating main transaction entry
124 --
125 progress := 20;
126
127 PJM_DEBUG.Debug('Creating Transaction Header');
128
129 INSERT INTO mtl_transactions_interface
130 ( transaction_header_id
131 , transaction_interface_id
132 , source_code
133 , source_header_id
134 , source_line_id
135 , transaction_mode
136 , lock_flag
137 , process_flag
138 , validation_required
139 , last_update_date
140 , last_updated_by
141 , creation_date
142 , created_by
143 , last_update_login
144 , inventory_item_id
145 , revision
146 , organization_id
147 , subinventory_code
148 , locator_id
149 , transaction_quantity
150 , transaction_uom
151 , primary_quantity
152 , transaction_type_id
153 , transaction_action_id
154 , transaction_source_type_id
155 , transaction_date
156 , acct_period_id
157 , reason_id
158 , transfer_organization
159 , transfer_subinventory
160 , transfer_locator
161 , transaction_reference
162 , attribute_category
163 , attribute1
164 , attribute2
165 , attribute3
166 , attribute4
167 , attribute5
168 , attribute6
169 , attribute7
170 , attribute8
171 , attribute9
172 , attribute10
173 , attribute11
174 , attribute12
175 , attribute13
176 , attribute14
177 , attribute15
178 ) VALUES
179 ( P_Txn_Header_ID
180 , txn_xface_id
181 , 'PJM MASS TRANSFER' /* Source Code */
182 , 0 /* Source Header ID */
183 , 0 /* Source Line ID */
184 , P_Process_Mode
185 , NULL /* Lock Flag */
186 , 1 /* Process Flag */
187 , 1 /* Validation Required */
188 , sysdate
189 , user_id
190 , sysdate
191 , user_id
192 , login_id
193 , P_Item_ID
194 , P_Revision
195 , P_Organization_ID
196 , P_Subinventory_Code
197 , P_From_Locator_ID
198 , P_Txn_Quantity
199 , primary_uom
200 , P_Txn_Quantity
201 , 67 /* Transaction Type ID - Project Transfer */
202 , 2 /* Transaction Action ID - Subinventory Transfer */
203 , 13 /* Trasaction Source Type ID - Inventory */
204 , P_Txn_Date
205 , P_Acct_Period_ID
206 , P_Txn_Reason_ID
207 , P_Organization_ID
208 , P_Subinventory_Code
209 , P_To_Locator_ID
210 , P_Txn_Reference
211 , P_DFF.Category
212 , P_DFF.Attr1
213 , P_DFF.Attr2
214 , P_DFF.Attr3
215 , P_DFF.Attr4
216 , P_DFF.Attr5
217 , P_DFF.Attr6
218 , P_DFF.Attr7
219 , P_DFF.Attr8
220 , P_DFF.Attr9
221 , P_DFF.Attr10
222 , P_DFF.Attr11
223 , P_DFF.Attr12
224 , P_DFF.Attr13
225 , P_DFF.Attr14
226 , P_DFF.Attr15
227 );
228
229 --
230 -- Populate Lot Table if item is lot controlled
231 --
232 IF ( lot_control = 'Y' AND P_Lot_Number IS NOT NULL ) THEN
233
234 progress := 30;
235
236 IF ( serial_control = 'Y' ) THEN
237 --
238 -- Fetching value for SERIAL_TRANSACTION_TEMP_ID
239 --
240 ser_txn_id := Get_Txn_Header;
241 ELSE
242 ser_txn_id := NULL;
243 END IF;
244
245 progress := 35;
246
247 PJM_DEBUG.Debug('Creating Transaction Lot Information');
248
249 INSERT INTO mtl_transaction_lots_interface
250 ( transaction_interface_id
251 , serial_transaction_temp_id
252 , source_code
253 , source_line_id
254 , last_update_date
255 , last_updated_by
256 , creation_date
257 , created_by
258 , last_update_login
259 , transaction_quantity
260 , primary_quantity
261 , lot_number
262 )
263 SELECT mti.transaction_interface_id
264 , ser_txn_id
265 , mti.source_code
266 , mti.source_line_id
267 , mti.last_update_date
268 , mti.last_updated_by
269 , mti.creation_date
270 , mti.created_by
271 , mti.last_update_login
272 , mti.transaction_quantity
273 , mti.primary_quantity
274 , P_Lot_Number
275 FROM mtl_transactions_interface mti
276 WHERE mti.transaction_interface_id = txn_xface_id;
277
278 END IF;
279
280 --
281 -- Populate Serial Table if item is serial controlled
282 --
283 IF ( serial_control = 'Y' ) THEN
284
285 progress := 40;
286
287 --
288 -- MSNI.TRANSACTION_INTERFACE_ID points to
289 -- > MTLI.SERIAL_TRANSACTION_TEMP_ID is lot controlled;
290 -- > MTI.TRANSACTION_INTERFACE_ID otherwise
291 IF ( ser_txn_id IS NULL ) THEN
292 ser_txn_id := txn_xface_id;
293 END IF;
294
295 PJM_DEBUG.Debug('Creating Transaction Serial Information');
296
297 INSERT INTO mtl_serial_numbers_interface
298 ( transaction_interface_id
299 , source_code
300 , source_line_id
301 , last_update_date
302 , last_updated_by
303 , creation_date
304 , created_by
305 , last_update_login
306 , fm_serial_number
307 , to_serial_number
308 )
309 SELECT ser_txn_id
310 , mti.source_code
311 , mti.source_line_id
312 , mti.last_update_date
313 , mti.last_updated_by
314 , mti.creation_date
315 , mti.created_by
316 , mti.last_update_login
317 , msn.serial_number
318 , msn.serial_number
319 FROM mtl_transactions_interface mti
320 , mtl_serial_numbers msn
321 WHERE mti.transaction_interface_id = txn_xface_id
322 AND msn.inventory_item_id = mti.inventory_item_id
323 AND msn.current_organization_id = mti.organization_id
324 AND msn.current_subinventory_code = mti.subinventory_code
325 AND msn.current_locator_id = mti.locator_id
326 AND nvl(msn.lot_number , '<No Lot Number>') =
327 nvl(P_Lot_Number , '<No Lot Number>')
328 AND msn.current_status = 3;
329
330 /*
331 IF ( sql%rowcount <> ABS(P_Txn_Quantity) ) THEN
332 --
333 -- Serial count does not match transaction quantity, error out
334 --
335 FND_MESSAGE.set_name('INV' , 'INV_SERQTY_NOTMATCH');
336 FND_MSG_PUB.add;
337 RAISE FND_API.G_EXC_ERROR;
338 END IF;
339 */
340 END IF;
341
342 PJM_DEBUG.Debug( 'CREATE_TRANSFER_TRANSACTION completed'
343 , G_MODULE , FND_LOG.LEVEL_PROCEDURE );
344
345 EXCEPTION
346 WHEN FND_API.G_EXC_ERROR THEN
347 ROLLBACK TO create_txfr_txn;
348 X_Return_Status := FND_API.G_RET_STS_ERROR;
349 FND_MSG_PUB.Count_And_Get( p_count => X_Msg_Count
350 , p_data => X_Msg_Data );
351
352 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
353 ROLLBACK TO create_txfr_txn;
354 X_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
355 FND_MSG_PUB.Count_And_Get( p_count => X_Msg_Count
356 , p_data => X_Msg_Data );
357
358 WHEN OTHERS THEN
359 ROLLBACK TO create_txfr_txn;
360 X_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
361 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
362 FND_MSG_PUB.add_exc_msg
363 ( p_pkg_name => G_PKG_NAME
364 , p_procedure_name => 'CREATE_TRANSFER_TRANSACTION:' || progress);
365 END IF;
366 FND_MSG_PUB.Count_And_Get( p_count => X_Msg_Count
367 , p_data => X_Msg_Data );
368
369 END Create_Transfer_Transaction;
370
371
372 --
373 -- Public Procedure
374 --
375 PROCEDURE Transfer
376 ( P_api_version IN NUMBER
377 , P_init_msg_list IN VARCHAR2
378 , P_commit IN VARCHAR2
379 , X_Return_Status OUT NOCOPY VARCHAR2
380 , X_Msg_Count OUT NOCOPY NUMBER
381 , X_Msg_Data OUT NOCOPY VARCHAR2
382 , P_Process_Mode IN NUMBER
383 , P_Transfer_Mode IN NUMBER
384 , P_Txn_Header_ID IN NUMBER
385 , P_Organization_ID IN NUMBER
386 , P_Item_ID IN NUMBER
387 , P_Category_Set_ID IN NUMBER
388 , P_Category_ID IN NUMBER
389 , P_From_Project_ID IN NUMBER
390 , P_From_Task_ID IN NUMBER
391 , P_To_Project_ID IN NUMBER
392 , P_To_Task_ID IN NUMBER
393 , P_Txn_Date IN DATE
394 , P_Acct_Period_ID IN NUMBER
395 , P_Txn_Reason_ID IN NUMBER
396 , P_Txn_Reference IN VARCHAR2
397 , P_DFF IN DFF_Rec_Type
398 , X_Txn_Header_ID OUT NOCOPY NUMBER
399 , X_Txn_Count OUT NOCOPY NUMBER
400 ) IS
401
402 l_api_name CONSTANT VARCHAR2(30) := 'TRANSFER';
403 l_api_version CONSTANT NUMBER := 1.0;
404
405 TYPE item_rc IS REF CURSOR;
406
407 c item_rc;
408 stmt VARCHAR2(2000);
409 item_id NUMBER;
410 acct_period NUMBER;
411 txn_header_id NUMBER;
412 txn_count NUMBER := 0;
413
414 BEGIN
415 --
416 -- Standard Start of API savepoint
417 --
418 SAVEPOINT pjm_mass_transfer;
419
420 PJM_DEBUG.Debug(
421 l_api_name ||
422 '( process_mode => ' || P_Process_Mode ||
423 ', transfer_mode => ' || P_Transfer_Mode ||
424 ', txn_header_id => ' || P_Txn_Header_ID ||
425 ', organization_id => ' || P_Organization_ID ||
426 ', item_id => ' || P_Item_ID ||
427 ', category_set_id => ' || P_Category_Set_ID ||
428 ', category_id => ' || P_Category_ID ||
429 ', from_project_id => ' || P_From_Project_ID ||
430 ', from_task_id => ' || P_From_Task_ID ||
431 ', to_project_id => ' || P_To_Project_ID ||
432 ', to_task_id => ' || P_To_Task_ID ||
433 ', txn_date => ' || FND_DATE.Date_To_DisplayDT(P_Txn_Date) ||
434 ', txn_reason_id => ' || P_Txn_Reason_ID ||
435 ', acct_period_id => ' || P_Acct_Period_ID ||
436 ', txn_reference => ' || P_Txn_Reference || ' )'
437 , G_MODULE , FND_LOG.LEVEL_PROCEDURE);
438
439 --
440 -- Check API incompatibility
441 --
442 IF NOT FND_API.Compatible_API_Call( l_api_version
443 , P_api_version
444 , l_api_name
445 , G_PKG_NAME )
446 THEN
447 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
448 END IF;
449
450 --
451 -- Initialize the message table if requested.
452 --
453 IF FND_API.TO_BOOLEAN( P_init_msg_list ) THEN
454 FND_MSG_PUB.initialize;
455 END IF;
456
457 --
458 -- Set API return status to success
459 --
460 X_Return_Status := FND_API.G_RET_STS_SUCCESS;
461
462 --
463 -- Get Current Open Accounting Period based on Transaction Date
464 -- if not already done
465 --
466 IF ( P_Acct_Period_ID IS NULL ) THEN
467 acct_period := Get_Acct_Period(P_Organization_ID , P_Txn_Date);
468 PJM_DEBUG.Debug('acct_period => ' || acct_period);
469 ELSE
470 acct_period := P_Acct_Period_ID;
471 END IF;
472
473 --
474 -- If open accounting period not found, error out
475 --
476 IF ( acct_period = 0 ) THEN
477 FND_MESSAGE.set_name('INV' , 'INV_NO_OPEN_PERIOD');
478 FND_MSG_PUB.add;
479 RAISE FND_API.G_EXC_ERROR;
480 ELSIF ( acct_period = -1 ) THEN
481 FND_MESSAGE.set_name('INV' , 'INV_RETRIEVE_PERIOD');
482 FND_MSG_PUB.add;
483 RAISE FND_API.G_EXC_ERROR;
484 END IF;
485
486 --
487 -- Get Transaction Header ID if not already done
488 --
489 IF ( P_Txn_Header_ID IS NULL ) THEN
490 txn_header_id := Get_Txn_Header;
491 PJM_DEBUG.Debug('txn_header_id => ' || txn_header_id);
492 ELSE
493 txn_header_id := P_Txn_Header_ID;
494 END IF;
495
496 X_Txn_Count := 0;
497
498 PJM_DEBUG.Debug('Loop for item...');
499
500 IF ( P_Transfer_Mode = G_TXFR_MODE_ALL_ITEMS ) THEN
501
502 OPEN c FOR
503 SELECT DISTINCT moq.inventory_item_id
504 FROM mtl_onhand_quantities_detail moq
505 , mtl_item_locations mil
506 WHERE moq.organization_id = P_Organization_ID
507 AND mil.organization_id = moq.organization_id
508 AND mil.inventory_location_id = moq.locator_id
509 AND mil.project_id = P_From_Project_ID
510 AND nvl(mil.task_id , 0) = nvl(P_From_Task_ID , 0);
511
512 ELSIF ( P_Transfer_Mode = G_TXFR_MODE_CATEGORY ) THEN
513
514 OPEN c FOR
515 SELECT DISTINCT moq.inventory_item_id
516 FROM mtl_onhand_quantities_detail moq
517 , mtl_item_locations mil
518 , mtl_item_categories mic
519 WHERE mic.organization_id = P_Organization_ID
520 AND mic.category_set_id = P_Category_Set_ID
521 AND mic.category_id = P_Category_ID
522 AND moq.organization_id = mic.organization_id
523 AND moq.inventory_item_id = mic.inventory_item_id
524 AND mil.organization_id = moq.organization_id
525 AND mil.inventory_location_id = moq.locator_id
526 AND mil.project_id = P_From_Project_ID
527 AND nvl(mil.task_id , 0) = nvl(P_From_Task_ID , 0);
528
529 ELSE
530
531 OPEN c FOR
532 SELECT P_Item_ID FROM DUAL
533 WHERE P_Item_ID is not null;
534
535 END IF;
536
537 LOOP
538 FETCH c INTO item_id;
539 EXIT WHEN c%notfound;
540
541 PJM_DEBUG.Debug('item_id => ' || item_id);
542
543 Item_Transfer
544 ( P_api_version => P_api_version
545 , P_init_msg_list => FND_API.G_FALSE
546 , P_commit => FND_API.G_FALSE
547 , X_Return_Status => X_Return_Status
548 , X_Msg_Count => X_Msg_Count
549 , X_Msg_Data => X_Msg_Data
550 , P_Process_Mode => P_Process_Mode
551 , P_Txn_Header_ID => txn_header_id
552 , P_Organization_ID => P_Organization_ID
553 , P_Item_ID => item_id
554 , P_From_Project_ID => P_From_Project_ID
555 , P_From_Task_ID => P_From_Task_ID
556 , P_To_Project_ID => P_To_Project_ID
557 , P_To_Task_ID => P_To_Task_ID
558 , P_Txn_Date => P_Txn_Date
559 , P_Acct_Period_ID => acct_period
560 , P_Txn_Reason_ID => P_Txn_Reason_ID
561 , P_Txn_Reference => P_Txn_Reference
562 , P_DFF => P_DFF
563 , X_Txn_Header_ID => X_Txn_Header_ID
564 , X_Txn_Count => txn_count
565 );
566
567 IF ( X_Return_Status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
568 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
569 ELSIF ( X_Return_Status = FND_API.G_RET_STS_ERROR ) THEN
570 RAISE FND_API.G_EXC_ERROR;
571 END IF;
572
573 X_Txn_Count := X_Txn_Count + txn_count;
574
575 END LOOP;
576
577 --
578 -- Stanard commit check
579 --
580 IF FND_API.To_Boolean( p_commit ) THEN
581 COMMIT WORK;
582 END IF;
583
584 --
585 -- Standard call to get message count and if count is 1, get message
586 -- info
587 --
588 FND_MSG_PUB.Count_And_Get( p_count => X_Msg_Count
589 , p_data => X_Msg_Data );
590
591 PJM_DEBUG.Debug( l_api_name || ' completed'
592 , G_MODULE , FND_LOG.LEVEL_PROCEDURE );
593
594 EXCEPTION
595 WHEN FND_API.G_EXC_ERROR THEN
596 ROLLBACK TO pjm_mass_transfer;
597 X_Return_Status := FND_API.G_RET_STS_ERROR;
598 FND_MSG_PUB.Count_And_Get( p_count => X_Msg_Count
599 , p_data => X_Msg_Data );
600
601 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
602 ROLLBACK TO pjm_mass_transfer;
603 X_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
604 FND_MSG_PUB.Count_And_Get( p_count => X_Msg_Count
605 , p_data => X_Msg_Data );
606
607 WHEN OTHERS THEN
608 ROLLBACK TO pjm_mass_transfer;
609 X_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
610 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
611 FND_MSG_PUB.add_exc_msg
612 ( p_pkg_name => G_PKG_NAME
613 , p_procedure_name => 'TRANSFER');
614 END IF;
615 FND_MSG_PUB.Count_And_Get( p_count => X_Msg_Count
616 , p_data => X_Msg_Data );
617
618 END Transfer;
619
620
621 PROCEDURE Item_Transfer
622 ( P_api_version IN NUMBER
623 , P_init_msg_list IN VARCHAR2
624 , P_commit IN VARCHAR2
625 , X_Return_Status OUT NOCOPY VARCHAR2
626 , X_Msg_Count OUT NOCOPY NUMBER
627 , X_Msg_Data OUT NOCOPY VARCHAR2
628 , P_Process_Mode IN NUMBER
629 , P_Txn_Header_ID IN NUMBER
630 , P_Organization_ID IN NUMBER
631 , P_Item_ID IN NUMBER
632 , P_From_Project_ID IN NUMBER
633 , P_From_Task_ID IN NUMBER
634 , P_To_Project_ID IN NUMBER
635 , P_To_Task_ID IN NUMBER
636 , P_Txn_Date IN DATE
637 , P_Acct_Period_ID IN NUMBER
638 , P_Txn_Reason_ID IN NUMBER
639 , P_Txn_Reference IN VARCHAR2
640 , P_DFF IN DFF_Rec_Type
641 , X_Txn_Header_ID OUT NOCOPY NUMBER
642 , X_Txn_Count OUT NOCOPY NUMBER
643 ) IS
644
645 l_api_name CONSTANT VARCHAR2(30) := 'ITEM_TRANSFER';
646 l_api_version CONSTANT NUMBER := 1.0;
647
648 CURSOR onhand IS
649 SELECT moq.subinventory_code
650 , moq.locator_id
651 , moq.lot_number
652 , moq.revision
653 , (-1) * sum(moq.transaction_quantity) txn_quantity
654 FROM mtl_onhand_quantities_detail moq
655 , mtl_item_locations mil
656 WHERE moq.organization_id = P_Organization_ID
657 AND moq.inventory_item_id = P_Item_ID
658 AND mil.organization_id = moq.organization_id
659 AND mil.inventory_location_id = moq.locator_id
660 AND mil.project_id = P_From_Project_ID
661 AND nvl(mil.task_id , 0) = nvl(P_From_Task_ID , 0)
662 AND NOT EXISTS (
663 SELECT 'Expired lot'
664 FROM mtl_lot_numbers
665 WHERE organization_id = moq.organization_id
666 AND inventory_item_id = moq.inventory_item_id
667 AND lot_number = moq.lot_number
668 AND expiration_date < sysdate )
669 GROUP BY moq.subinventory_code , moq.locator_id , moq.lot_number , moq.revision
670 HAVING sum(moq.transaction_quantity) > 0;
671
672 acct_period NUMBER;
673 txfr_locator_id NUMBER;
674 txn_header_id NUMBER;
675 txn_count NUMBER;
676
677 BEGIN
678 --
679 -- Standard Start of API savepoint
680 --
681 SAVEPOINT pjm_mass_transfer_item;
682
683 PJM_DEBUG.Debug(
684 l_api_name ||
685 '( process_mode => ' || P_Process_Mode ||
686 ', txn_header_id => ' || P_Txn_Header_ID ||
687 ', organization_id => ' || P_Organization_ID ||
688 ', item_id => ' || P_Item_ID ||
689 ', from_project_id => ' || P_From_Project_ID ||
690 ', from_task_id => ' || P_From_Task_ID ||
691 ', to_project_id => ' || P_To_Project_ID ||
692 ', to_task_id => ' || P_To_Task_ID ||
693 ', txn_date => ' || FND_DATE.Date_To_DisplayDT(P_Txn_Date) ||
694 ', acct_period_id => ' || P_Acct_Period_ID ||
695 ', txn_reason_id => ' || P_Txn_Reason_ID ||
696 ', txn_reference => ' || P_Txn_Reference || ' )'
697 , G_MODULE , FND_LOG.LEVEL_PROCEDURE);
698
699 --
700 -- Check API incompatibility
701 --
702 IF NOT FND_API.Compatible_API_Call( l_api_version
703 , P_api_version
704 , l_api_name
705 , G_PKG_NAME )
706 THEN
707 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
708 END IF;
709
710 --
711 -- Initialize the message table if requested.
712 --
713 IF FND_API.TO_BOOLEAN( P_init_msg_list ) THEN
714 FND_MSG_PUB.initialize;
715 END IF;
716
717 --
718 -- Set API return status to success
719 --
720 X_Return_Status := FND_API.G_RET_STS_SUCCESS;
721
722 --
723 -- Get Current Open Accounting Period based on Transaction Date
724 -- if not already done
725 --
726 IF ( P_Acct_Period_ID IS NULL ) THEN
727 acct_period := Get_Acct_Period(P_Organization_ID , P_Txn_Date);
728 PJM_DEBUG.Debug('acct_period => ' || acct_period);
729 ELSE
730 acct_period := P_Acct_Period_ID;
731 END IF;
732
733 --
734 -- If open accounting period not found, error out
735 --
736 IF ( acct_period = 0 ) THEN
737 FND_MESSAGE.set_name('INV' , 'INV_NO_OPEN_PERIOD');
738 FND_MSG_PUB.add;
739 RAISE FND_API.G_EXC_ERROR;
740 ELSIF ( acct_period = -1 ) THEN
741 FND_MESSAGE.set_name('INV' , 'INV_RETRIEVE_PERIOD');
742 FND_MSG_PUB.add;
743 RAISE FND_API.G_EXC_ERROR;
744 END IF;
745
746 --
747 -- Get Transaction Header ID if not already done
748 --
749 IF ( P_Txn_Header_ID IS NULL ) THEN
750 txn_header_id := Get_Txn_Header;
751 PJM_DEBUG.Debug('txn_header_id => ' || txn_header_id);
752 ELSE
753 txn_header_id := P_Txn_Header_ID;
754 END IF;
755
756 txn_count := 0;
757
758 --
759 -- Loop through all onhand quantity record for the item
760 --
761 PJM_DEBUG.Debug('Loop for onhand...');
762
763 FOR ohrec IN onhand LOOP
764 --
765 -- Derive the destination locator based on current locator
766 -- and transfer to project/task
767 --
768 PJM_PROJECT_LOCATOR.Get_DefaultProjectLocator
769 ( P_Organization_ID => P_Organization_ID
770 , P_Locator_ID => ohrec.locator_id
771 , P_Project_ID => P_To_Project_ID
772 , P_Task_ID => P_To_Task_ID
773 , P_Project_Locator_ID => txfr_locator_id
774 );
775
776 PJM_DEBUG.Debug(
777 'subinventory => ' || ohrec.subinventory_code ||
778 ', locator_id => ' || ohrec.locator_id ||
779 ', txfr_locator_id => ' || txfr_locator_id);
780
781 --
782 -- Call private function to create transaction
783 --
784 Create_Transfer_Transaction
785 ( P_Txn_Header_ID => txn_header_id
786 , P_Process_Mode => P_Process_Mode
787 , P_Organization_ID => P_Organization_ID
788 , P_Item_ID => P_Item_ID
789 , P_Revision => ohrec.revision
790 , P_Lot_Number => ohrec.lot_number
791 , P_Txn_Quantity => ohrec.txn_quantity
792 , P_Subinventory_Code => ohrec.subinventory_code
793 , P_From_Locator_ID => ohrec.locator_id
794 , P_To_Locator_ID => txfr_locator_id
795 , P_Txn_Date => P_Txn_Date
796 , P_Acct_Period_ID => acct_period
797 , P_Txn_Reason_ID => P_Txn_Reason_ID
798 , P_Txn_Reference => P_Txn_Reference
799 , P_DFF => P_DFF
800 , X_Return_Status => X_Return_Status
801 , X_Msg_Count => X_Msg_Count
802 , X_Msg_Data => X_Msg_Data
803 );
804
805 IF ( X_Return_Status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
806 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
807 ELSIF ( X_Return_Status = FND_API.G_RET_STS_ERROR ) THEN
808 RAISE FND_API.G_EXC_ERROR;
809 END IF;
810
811 txn_count := txn_count + 1;
812
813 END LOOP;
814
815 X_Txn_Count := txn_count;
816
817 X_Txn_Header_ID := txn_header_id;
818
819 --
820 -- Stanard commit check
821 --
822 IF FND_API.To_Boolean( p_commit ) THEN
823 COMMIT WORK;
824 END IF;
825
826 --
827 -- Standard call to get message count and if count is 1, get message
828 -- info
829 --
830 FND_MSG_PUB.Count_And_Get( p_count => X_Msg_Count
831 , p_data => X_Msg_Data );
832
833 PJM_DEBUG.Debug( l_api_name || ' completed'
834 , G_MODULE , FND_LOG.LEVEL_PROCEDURE );
835
836 EXCEPTION
837 WHEN FND_API.G_EXC_ERROR THEN
838 ROLLBACK TO pjm_mass_transfer_item;
839 X_Return_Status := FND_API.G_RET_STS_ERROR;
840 FND_MSG_PUB.Count_And_Get( p_count => X_Msg_Count
841 , p_data => X_Msg_Data );
842
843 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
844 ROLLBACK TO pjm_mass_transfer_item;
845 X_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
846 FND_MSG_PUB.Count_And_Get( p_count => X_Msg_Count
847 , p_data => X_Msg_Data );
848
849 WHEN OTHERS THEN
850 ROLLBACK TO pjm_mass_transfer_item;
851 X_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
852 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
853 FND_MSG_PUB.add_exc_msg
854 ( p_pkg_name => G_PKG_NAME
855 , p_procedure_name => 'ITEM_TRANSFER');
856 END IF;
857 FND_MSG_PUB.Count_And_Get( p_count => X_Msg_Count
858 , p_data => X_Msg_Data );
859 END Item_Transfer;
860
861
862 PROCEDURE Mass_Transfer
863 ( P_api_version IN NUMBER
864 , P_init_msg_list IN VARCHAR2
865 , P_commit IN VARCHAR2
866 , X_Return_Status OUT NOCOPY VARCHAR2
867 , X_Msg_Count OUT NOCOPY NUMBER
868 , X_Msg_Data OUT NOCOPY VARCHAR2
869 , P_Transfer_ID IN NUMBER
870 , X_Txn_Header_ID OUT NOCOPY NUMBER
871 , X_Txn_Count OUT NOCOPY NUMBER
872 , X_Request_ID OUT NOCOPY NUMBER
873 ) IS
874
875 l_api_name CONSTANT VARCHAR2(30) := 'MASS_TRANSFER';
876 l_api_version CONSTANT NUMBER := 1.0;
877
878 CURSOR h IS
879 SELECT p.organization_id
880 , p.acct_period_id
881 , p.from_project_id
882 , p.to_project_id
883 , p.transfer_date
884 , p.transfer_mode
885 , p.inventory_item_id
886 , p.category_set_id
887 , p.category_id
888 , p.transfer_reason_id
889 , p.transfer_reference
890 , p.process_mode
891 , p.attribute_category
892 , p.attribute1
893 , p.attribute2
894 , p.attribute3
895 , p.attribute4
896 , p.attribute5
897 , p.attribute6
898 , p.attribute7
899 , p.attribute8
900 , p.attribute9
901 , p.attribute10
902 , p.attribute11
903 , p.attribute12
904 , p.attribute13
905 , p.attribute14
906 , p.attribute15
907 FROM pjm_mass_transfers p
908 WHERE p.mass_transfer_id = P_Transfer_ID;
909
910 CURSOR l IS
911 SELECT from_task_id
912 , to_task_id
913 FROM pjm_mass_transfer_tasks
914 WHERE mass_transfer_id = P_Transfer_ID;
915
916 hrec h%rowtype;
917 lrec l%rowtype;
918
919 Txn_Count NUMBER;
920 Txn_Header_IN NUMBER;
921 Txn_Header_OUT NUMBER;
922 DFF PJM_MASS_TRANSFER_PUB.DFF_Rec_Type;
923
924 BEGIN
925 --
926 -- Standard Start of API savepoint
927 --
928 SAVEPOINT pjm_mass_transfer_item;
929
930 PJM_DEBUG.Debug( 'Transfer_ID => ' || P_Transfer_ID
931 , G_MODULE , FND_LOG.LEVEL_EVENT );
932
933 --
934 -- Check API incompatibility
935 --
936 IF NOT FND_API.Compatible_API_Call( l_api_version
937 , P_api_version
938 , l_api_name
939 , G_PKG_NAME )
940 THEN
941 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
942 END IF;
943
944 --
945 -- Initialize the message table if requested.
946 --
947 IF FND_API.TO_BOOLEAN( P_init_msg_list ) THEN
948 FND_MSG_PUB.initialize;
949 END IF;
950
951 --
952 -- Set API return status to success
953 --
954 X_Return_Status := FND_API.G_RET_STS_SUCCESS;
955
956 X_Txn_Count := 0;
957
958 --
959 -- Fetch transfer information
960 --
961 OPEN h;
962 FETCH h INTO hrec;
963 CLOSE h;
964
965 FOR lrec IN l LOOP
966
967 DFF.Category := hrec.attribute_category;
968 DFF.Attr1 := hrec.attribute1;
969 DFF.Attr2 := hrec.attribute2;
970 DFF.Attr3 := hrec.attribute3;
971 DFF.Attr4 := hrec.attribute4;
972 DFF.Attr5 := hrec.attribute5;
973 DFF.Attr6 := hrec.attribute6;
974 DFF.Attr7 := hrec.attribute7;
975 DFF.Attr8 := hrec.attribute8;
976 DFF.Attr9 := hrec.attribute9;
977 DFF.Attr10 := hrec.attribute10;
978 DFF.Attr11 := hrec.attribute11;
979 DFF.Attr12 := hrec.attribute12;
980 DFF.Attr13 := hrec.attribute13;
981 DFF.Attr14 := hrec.attribute14;
982 DFF.Attr15 := hrec.attribute15;
983
984 IF ( hrec.transfer_mode = G_TXFR_MODE_ONE_ITEM ) THEN
985 --
986 -- If Transfer Mode is Signle Item, call the single item
987 -- transfer procedure directly
988 --
989 PJM_MASS_TRANSFER_PUB.Item_Transfer
990 ( P_api_version => 1.0
991 , P_init_msg_list => FND_API.G_FALSE
992 , P_commit => FND_API.G_FALSE
993 , X_Return_Status => X_Return_Status
994 , X_Msg_Count => X_Msg_Count
995 , X_Msg_Data => X_Msg_Data
996 , P_Process_Mode => hrec.process_mode
997 , P_Txn_Header_ID => Txn_Header_IN
998 , P_Organization_ID => hrec.organization_id
999 , P_Item_ID => hrec.inventory_item_id
1000 , P_From_Project_ID => hrec.from_project_id
1001 , P_From_Task_ID => lrec.from_task_id
1002 , P_To_Project_ID => hrec.to_project_id
1003 , P_To_Task_ID => lrec.to_task_id
1004 , P_Txn_Date => hrec.transfer_date
1005 , P_Acct_Period_ID => hrec.acct_period_id
1006 , P_Txn_Reason_ID => hrec.transfer_reason_id
1007 , P_Txn_Reference => hrec.transfer_reference
1008 , P_DFF => DFF
1009 , X_Txn_Header_ID => Txn_Header_OUT
1010 , X_Txn_Count => Txn_Count
1011 );
1012
1013 ELSE
1014
1015 PJM_MASS_TRANSFER_PUB.Transfer
1016 ( P_api_version => 1.0
1017 , P_init_msg_list => FND_API.G_FALSE
1018 , P_commit => FND_API.G_FALSE
1019 , X_Return_Status => X_Return_Status
1020 , X_Msg_Count => X_Msg_Count
1021 , X_Msg_Data => X_Msg_Data
1022 , P_Process_Mode => hrec.process_mode
1023 , P_Transfer_Mode => hrec.transfer_mode
1024 , P_Txn_Header_ID => Txn_Header_IN
1025 , P_Organization_ID => hrec.organization_id
1026 , P_Item_ID => hrec.inventory_item_id
1027 , P_Category_Set_ID => hrec.category_set_id
1028 , P_Category_ID => hrec.category_id
1029 , P_From_Project_ID => hrec.from_project_id
1030 , P_From_Task_ID => lrec.from_task_id
1031 , P_To_Project_ID => hrec.to_project_id
1032 , P_To_Task_ID => lrec.to_task_id
1033 , P_Txn_Date => hrec.transfer_date
1034 , P_Acct_Period_ID => hrec.acct_period_id
1035 , P_Txn_Reason_ID => hrec.transfer_reason_id
1036 , P_Txn_Reference => hrec.transfer_reference
1037 , P_DFF => DFF
1038 , X_Txn_Header_ID => Txn_Header_OUT
1039 , X_Txn_Count => Txn_Count
1040 );
1041
1042 END IF;
1043
1044 X_Txn_Count := X_Txn_Count + Txn_Count;
1045
1046 IF ( Txn_Header_IN IS NULL ) THEN
1047 Txn_Header_IN := Txn_Header_OUT;
1048 X_Txn_Header_ID := Txn_Header_OUT;
1049 END IF;
1050
1051 END LOOP;
1052
1053 --
1054 -- Submit Request if process mode is Concurrent
1055 --
1056
1057 IF ( X_Txn_Count > 0 AND hrec.process_mode = G_PROC_MODE_IMMEDIATE ) THEN
1058
1059 FND_MESSAGE.Set_Name('PJM' , 'MXFR-CONC PROGRAM DESC');
1060
1061 X_Request_ID := FND_REQUEST.Submit_Request
1062 ( application => 'INV'
1063 , program => 'INCTCW'
1064 , description => FND_MESSAGE.Get
1065 , argument1 => X_Txn_Header_ID
1066 , argument2 => '3' /* Process from MTI with full validation */
1067 --
1068 -- The following 3 arguments should not be needed; however,
1069 -- the Inventory Transaction Worker fails without them
1070 --
1071 , argument3 => ''
1072 , argument4 => ''
1073 , argument5 => ''
1074 );
1075
1076 ELSE
1077
1078 X_Request_ID := NULL;
1079
1080 END IF;
1081
1082 --
1083 -- Stanard commit check
1084 --
1085 IF FND_API.To_Boolean( p_commit ) THEN
1086 COMMIT WORK;
1087 END IF;
1088
1089 --
1090 -- Standard call to get message count and if count is 1, get message
1091 -- info
1092 --
1093 FND_MSG_PUB.Count_And_Get( p_count => X_Msg_Count
1094 , p_data => X_Msg_Data );
1095
1096 PJM_DEBUG.Debug( l_api_name || ' completed'
1097 , G_MODULE , FND_LOG.LEVEL_EVENT );
1098
1099 EXCEPTION
1100 WHEN FND_API.G_EXC_ERROR THEN
1101 ROLLBACK TO pjm_mass_transfer;
1102 X_Return_Status := FND_API.G_RET_STS_ERROR;
1103 FND_MSG_PUB.Count_And_Get( p_count => X_Msg_Count
1104 , p_data => X_Msg_Data );
1105
1106 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1107 ROLLBACK TO pjm_mass_transfer;
1108 X_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
1109 FND_MSG_PUB.Count_And_Get( p_count => X_Msg_Count
1110 , p_data => X_Msg_Data );
1111
1112 WHEN OTHERS THEN
1113 ROLLBACK TO pjm_mass_transfer;
1114 X_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
1115 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1116 FND_MSG_PUB.add_exc_msg
1117 ( p_pkg_name => G_PKG_NAME
1118 , p_procedure_name => 'MASS_TRANSFER');
1119 END IF;
1120 FND_MSG_PUB.Count_And_Get( p_count => X_Msg_Count
1121 , p_data => X_Msg_Data );
1122 END Mass_Transfer;
1123
1124 END PJM_MASS_TRANSFER_PUB;