[Home] [Help]
PACKAGE BODY: APPS.PJM_BORROW_PAYBACK
Source
1 PACKAGE BODY PJM_BORROW_PAYBACK AS
2 /* $Header: PJMBWPYB.pls 115.25 2004/01/07 22:35:52 alaw ship $ */
3 -- ---------------------------------------------------------------------
4 -- Global Variables
5 -- ---------------------------------------------------------------------
6 G_Bucket_Size NUMBER := 30;
7
8 -- ---------------------------------------------------------------------
9 -- Private Functions / Procedures
10 -- ---------------------------------------------------------------------
11
12
13 -- ---------------------------------------------------------------------
14 -- Public Functions / Procedures
15 -- ---------------------------------------------------------------------
16
17 --
18 -- Name : Set_Bucket_Size
19 -- Pre-reqs : None
20 -- Function : This procedure sets the global variable
21 -- G_Bucket_Size
22 --
23 --
24 -- Parameters :
25 -- IN : X_Bucket_Size NUMBER
26 --
27 -- Returns : None
28 --
29 PROCEDURE Set_Bucket_Size
30 ( X_Bucket_Size IN NUMBER
31 ) IS
32 BEGIN
33
34 G_Bucket_Size := X_Bucket_Size;
35
36 END Set_Bucket_Size;
37
38
39 --
40 -- Name : Bucket_Size
41 -- Pre-reqs : None
42 -- Function : This procedure gets the value in global variable
43 -- G_Bucket_Size
44 --
45 --
46 -- Parameters :
47 -- IN : None
48 --
49 -- Returns : VARCHAR2
50 --
51 FUNCTION Bucket_Size
52 RETURN NUMBER IS
53 BEGIN
54
55 return ( G_Bucket_Size );
56
57 END Bucket_Size;
58
59
60 --
61 -- Name : Trx_Callback
62 -- Pre-reqs : Non
63 -- Function : This function performs the following tasks:
64 -- 1) for a borrow transaction, it inserts a record
65 -- into PJM_BORROW_TRANSACTIONS
66 --
67 -- 2) for a payback transaction, it allocates the
68 -- payback quantity to borrow transactions and
69 -- insert the results in PJM_BORROW_PAYBACKS
70 --
71 --
72 -- Parameters :
73 -- IN : X_transaction_id NUMBER
74 -- X_transaction_temp_id NUMBER
75 --
76 -- OUT : X_error_code VARCHAR2
77 --
78 -- Returns : Boolean
79 --
80 FUNCTION Trx_Callback
81 ( X_transaction_id IN NUMBER
82 , X_transaction_temp_id IN NUMBER
83 , X_error_code OUT NOCOPY VARCHAR2
84 ) RETURN BOOLEAN IS
85
86 L_trx_type_id NUMBER;
87 L_trx_quantity NUMBER;
88 L_proj_id NUMBER;
89 L_task_id NUMBER;
90 L_to_proj_id NUMBER;
91 L_to_task_id NUMBER;
92 L_item_id NUMBER;
93 L_revision VARCHAR2(3);
94 L_organization_id NUMBER;
95 L_proj_ctrl_level NUMBER;
96
97 L_borrow_trx_id NUMBER;
98 L_outstanding_qty NUMBER;
99 L_payback_qty NUMBER;
100 L_payback_date DATE;
101
102 CURSOR c
103 ( C_borrow_proj_id NUMBER
104 , C_borrow_task_id NUMBER
105 , C_lending_proj_id NUMBER
106 , C_lending_task_id NUMBER
107 , C_item_id NUMBER
108 , C_revision VARCHAR2
109 , C_organization_id NUMBER
110 , C_proj_ctrl_level NUMBER ) IS
111 SELECT borrow_transaction_id
112 , outstanding_quantity
113 FROM pjm_borrow_transactions
114 WHERE decode(C_proj_ctrl_level, 1, borrow_project_id, borrow_task_id) =
115 decode(C_proj_ctrl_level, 1, C_borrow_proj_id, C_borrow_task_id)
116 AND decode(C_proj_ctrl_level, 1, lending_project_id, lending_task_id) =
117 decode(C_proj_ctrl_level, 1, C_lending_proj_id, C_lending_task_id)
118 AND inventory_item_id = C_item_id
119 AND organization_id = C_organization_id
120 AND outstanding_quantity > 0
121 ORDER BY loan_date ASC, borrow_transaction_id ASC
122 FOR UPDATE OF outstanding_quantity;
123
124 BEGIN
125
126 --
127 -- Fetch transaction details from MTL_MATERIAL_TRANSACTIONS
128 --
129 SELECT transaction_type_id
130 , primary_quantity * (-1)
131 , project_id
132 , task_id
133 , to_project_id
134 , to_task_id
135 , inventory_item_id
136 , revision
137 , organization_id
138 INTO L_trx_type_id
139 , L_trx_quantity
140 , L_proj_id
141 , L_task_id
142 , L_to_proj_id
143 , L_to_task_id
144 , L_item_id
145 , L_revision
146 , L_organization_id
147 FROM mtl_material_transactions
148 WHERE transaction_id = X_transaction_id;
149
150 --
151 -- Make sure this is a borrow or payback transaction
152 --
153 if ( L_trx_type_id not in (66, 68) ) then
154 return ( TRUE );
155 end if;
156
157 --
158 -- Make sure this is a "transfer from" transaction
159 --
160 if ( L_trx_quantity < 0 ) then
161 return ( TRUE );
162 end if;
163
164 --
165 -- Get project_control_level parameter for the organization.
166 --
167 select project_control_level
168 into L_proj_ctrl_level
169 from mtl_parameters
170 where organization_id = L_organization_id;
171
172 if ( L_trx_type_id = 66 ) then
173
174 SELECT scheduled_payback_date
175 INTO L_payback_date
176 FROM mtl_material_transactions_temp
177 WHERE transaction_temp_id = X_transaction_temp_id;
178
179 INSERT INTO pjm_borrow_transactions
180 ( borrow_transaction_id
181 , creation_date
182 , created_by
183 , last_update_date
184 , last_updated_by
185 , last_update_login
186 , request_id
187 , program_application_id
188 , program_id
189 , program_update_date
190 , borrow_project_id
191 , borrow_task_id
192 , lending_project_id
193 , lending_task_id
194 , organization_id
195 , inventory_item_id
196 , revision
197 , loan_quantity
198 , outstanding_quantity
199 , loan_date
200 , scheduled_payback_date )
201 SELECT transaction_id
202 , sysdate
203 , created_by
204 , sysdate
205 , last_updated_by
206 , last_update_login
207 , request_id
208 , program_application_id
209 , program_id
210 , sysdate
211 , to_project_id
212 , decode(L_proj_ctrl_level, 2, to_task_id, NULL)
213 , project_id
214 , decode(L_proj_ctrl_level, 2, task_id, NULL)
215 , organization_id
216 , inventory_item_id
217 , revision
218 , (-1) * primary_quantity
219 , (-1) * primary_quantity
220 , transaction_date
221 , L_payback_date
222 FROM mtl_material_transactions
223 WHERE transaction_id = X_transaction_id;
224
225 else
226
227 SELECT sum(outstanding_quantity)
228 INTO L_outstanding_qty
229 FROM pjm_borrow_transactions
230 WHERE decode(L_proj_ctrl_level, 1, borrow_project_id, borrow_task_id) =
231 decode(L_proj_ctrl_level, 1, L_proj_id, L_task_id)
232 AND decode(L_proj_ctrl_level, 1, lending_project_id, lending_task_id) =
233 decode(L_proj_ctrl_level, 1, L_to_proj_id, L_to_task_id)
234 AND inventory_item_id = L_item_id
235 AND organization_id = L_organization_id
236 AND outstanding_quantity > 0;
237
238 if ( L_outstanding_qty < L_trx_quantity ) then
239 X_error_code := 'BWPY-Payback Quantity Exceeded';
240 fnd_message.set_name('PJM', X_Error_Code);
241 return ( FALSE );
242 end if;
243
244 OPEN c ( L_proj_id
245 , L_task_id
246 , L_to_proj_id
247 , L_to_task_id
248 , L_item_id
249 , L_revision
250 , L_organization_id
251 , L_proj_ctrl_level);
252
253 WHILE ( L_trx_quantity > 0 ) LOOP
254
255 FETCH c INTO L_borrow_trx_id, L_outstanding_qty;
256
257 if ( L_outstanding_qty > L_trx_quantity ) then
258 L_payback_qty := L_trx_quantity;
259 else
260 L_payback_qty := L_outstanding_qty;
261 end if;
262
263 INSERT INTO pjm_borrow_paybacks
264 ( payback_transaction_id
265 , borrow_transaction_id
266 , creation_date
267 , created_by
268 , last_update_date
269 , last_updated_by
270 , last_update_login
271 , request_id
272 , program_application_id
273 , program_id
274 , program_update_date
275 , payback_quantity
276 , borrow_project_id
277 , borrow_task_id
278 , lending_project_id
279 , lending_task_id )
280 SELECT transaction_id
281 , L_borrow_trx_id
282 , sysdate
283 , created_by
284 , sysdate
285 , last_updated_by
286 , last_update_login
287 , request_id
288 , program_application_id
289 , program_id
290 , sysdate
291 , L_payback_qty
292 , project_id
293 , decode(L_proj_ctrl_level, 2, task_id, NULL)
294 , to_project_id
295 , decode(L_proj_ctrl_level, 2, to_task_id, NULL)
296 FROM mtl_material_transactions
297 WHERE transaction_id = X_transaction_id;
298
299 UPDATE pjm_borrow_transactions
300 SET outstanding_quantity = outstanding_quantity - L_payback_qty
301 , last_update_date = sysdate
302 WHERE borrow_transaction_id = L_borrow_trx_id;
303
304 L_trx_quantity := L_trx_quantity - L_payback_qty;
305
306 END LOOP;
307
308 if L_trx_quantity > 0 then
309 X_error_code := 'BWPY-Payback Quantity Exceeded';
310 fnd_message.set_name('PJM', X_Error_Code);
311 return ( FALSE );
312 end if;
313
314 end if;
315
316 return ( TRUE );
317
318 END Trx_Callback;
319
320 ------------------------------------------------------------------------
321 -- PURPOSE :
322 -- This function will be called from Inventory Transaction form.
323 -- When user issue a project borrow/payback transaction, the form
324 -- will call this function to validate the transaction.
325 --
326 -- PARAMETERS IN:
327 -- X_Transaction_Type_Id
328 -- X_Transaction_Action_Id
329 -- X_Organization_ID
330 -- X_From_Subinventory
331 -- X_From_Locator_Id
332 -- X_To_Subinventory
333 -- X_To_Locator_Id
334 -- X_Inventory_Item_Id
335 -- X_Revision
336 -- X_Primary_Quantity
337 -- X_Transaction_Date
338 -- X_Payback_Date
339 --
340 -- PARAMETERS OUT:
341 -- X_Error_Code
342 --
343 -- RETURN :
344 -- NUMBER
345 -- 0 -- SUCCESS
346 -- 1 -- FAILURE
347 -- 2 -- WARNING
348 --
349 ----------------------------------------------------------------------
350
351 FUNCTION VALIDATE_TRX
352 ( X_Transaction_Type_Id IN NUMBER
353 , X_Transaction_Action_Id IN NUMBER
354 , X_Organization_Id IN NUMBER
355 , X_From_SubInventory IN VARCHAR2
356 , X_From_Locator_Id IN NUMBER
357 , X_To_Subinventory IN VARCHAR2
358 , X_To_Locator_Id IN NUMBER
359 , X_Inventory_Item_Id IN NUMBER
360 , X_Revision IN VARCHAR2
361 , X_Primary_Quantity IN NUMBER
362 , X_Transaction_Date IN DATE
363 , X_Payback_Date IN DATE
364 , X_Error_Code OUT NOCOPY VARCHAR2
365 ) RETURN NUMBER IS
366
367 l_success number := 0;
368 l_failure number := 1;
369 l_warning number := 2;
370
371 l_from_project_id NUMBER;
372 l_from_task_id NUMBER;
373 l_to_project_id NUMBER;
374 l_to_task_id NUMBER;
375 l_outstanding_qty NUMBER := 0;
376 l_onhand_qty NUMBER := 0;
377 l_project_control_lev NUMBER;
378 l_from_physical_loc NUMBER;
379 l_to_physical_loc NUMBER;
380 l_asset_item_flag VARCHAR2(1);
381 l_asset_inventory VARCHAR2(1);
382
383 CURSOR c(C_Project_Control_Lev NUMBER,
384 C_From_Project_Id NUMBER,
385 C_From_Task_Id NUMBER,
386 C_To_Project_Id NUMBER,
387 C_To_Task_Id NUMBER,
388 C_Organization_Id NUMBER,
389 C_Inventory_Item_Id NUMBER,
390 C_Revision VARCHAR2) IS
391 select outstanding_quantity
392 from pjm_borrow_transactions
393 where decode(c_project_control_lev, 1, lending_project_id,
394 lending_task_id) =
395 decode(c_project_control_lev, 1, c_to_project_id, c_to_task_id)
396 and decode(c_project_control_lev, 1, borrow_project_id,
397 borrow_task_id) =
398 decode(c_project_control_lev, 1, c_from_project_id, c_from_task_id)
399 and organization_id = C_Organization_Id
400 and inventory_item_id = C_Inventory_Item_Id
401 order by loan_date DESC;
402
403 BEGIN
404
405 ------------------------------------------------------------
406 -- if this is not an Inventory Transfer transaction, we don't
407 -- need to do anything.
408 ------------------------------------------------------------
409 if (X_Transaction_Action_Id <> 2) then
410 return(l_success);
411 end if;
412
413
414 -----------------------------------------------------------
415 -- Verify if the item is not a Asset Item.
416 -----------------------------------------------------------
417 if ( X_Transaction_Type_Id in (66, 68) ) then
418
419 select nvl(inventory_asset_flag, 'N')
420 into l_asset_item_flag
421 from mtl_system_items
422 where organization_id = X_Organization_ID
423 and inventory_item_id = X_Inventory_Item_Id;
424
425 if (l_asset_item_flag = 'N') then
426 X_Error_Code := 'BWPY-Non Asset Item Transfer';
427 fnd_message.set_name('PJM', X_Error_Code);
428 return(l_failure);
429 end if;
430
431
432 ------------------------------------------------------------
433 -- Verify if the from/to subinventory is not Asset Inventory
434 ------------------------------------------------------------
435 begin
436 select 'N'
437 into l_asset_inventory
438 from mtl_secondary_inventories
439 where organization_id = X_Organization_Id
440 and (secondary_inventory_name = X_From_Subinventory
441 or secondary_inventory_name = X_To_Subinventory)
442 and asset_inventory = 2;
443
444 if SQL%FOUND then
445 X_Error_Code := 'BWPY-Non Asset Inventory Trans';
446 fnd_message.set_name('PJM', X_Error_Code);
447 return(l_failure);
448 end if;
449
450 exception
451 when NO_DATA_FOUND then
455 fnd_message.set_name('PJM', X_Error_Code);
452 null;
453 when TOO_MANY_ROWS then
454 X_Error_Code := 'BWPY-Non Asset Inventory Trans';
456 return(l_failure);
457 end;
458
459 end if;
460
461 -----------------------------------------------------------
462 -- Derive project_id, task_id from locator_id
463 -----------------------------------------------------------
464 if ( X_From_Locator_Id is not null ) then
465
466 select project_id, task_id,
467 physical_location_id
468 into l_from_project_id, l_from_task_id,
469 l_from_physical_loc
470 from mtl_item_locations
471 where organization_id = X_Organization_Id
472 and inventory_location_id = X_From_Locator_Id ;
473
474 else
475
476 l_from_project_id := NULL;
477 l_from_task_id := NULL;
478 l_from_physical_loc := NULL;
479
480 end if;
481
482 if ( X_To_Locator_Id is not null ) then
483
484 select project_id, task_id,
485 physical_location_id
486 into l_to_project_id, l_to_task_id,
487 l_to_physical_loc
488 from mtl_item_locations
489 where organization_id = X_Organization_Id
490 and inventory_location_id = X_To_Locator_Id;
491
492
493 else
494
495 l_to_project_id := NULL;
496 l_to_task_id := NULL;
497 l_to_physical_loc := NULL;
498
499 end if;
500
501 ------------------------------------------------------------
502 -- Get project_control_level parameter for the organization.
503 ------------------------------------------------------------
504 BEGIN
505 select project_control_level
506 into l_project_control_lev
507 from pjm_org_parameters
508 where organization_id = X_Organization_Id;
509 EXCEPTION
510 WHEN no_data_found THEN
511 l_project_control_lev := 0;
512 END;
513
514 -------------------------------------------------------------
515 -- If transaction_type is not Project Borrow, Project Payback
516 -- or Project Transfer, the from_project_id should always
517 -- equal to to_project_id.
518 -------------------------------------------------------------
519 if (X_Transaction_Type_Id not in (66, 68, 67)) then
520 if( ( nvl(l_from_project_id,0) <> nvl(l_to_project_id,0) ) OR
521 ( nvl(l_from_task_id,0) <> nvl(l_to_task_id,0) ) ) then
522 X_Error_Code := 'BWPY-Project Transfer';
523 fnd_message.set_name('PJM', X_Error_Code);
524 return(l_failure);
525 else
526 return(l_success);
527 end if;
528
529 else
530 -----------------------------------------------------------
531 -- Verify that both sides are not common
532 -----------------------------------------------------------
533 if ( l_from_project_id is null
534 and l_to_project_id is null ) then
535 X_Error_Code := 'BWPY-Common Transfer';
536 fnd_message.set_name('PJM', X_Error_Code);
537 return(l_failure);
538 end if;
539
540 -----------------------------------------------------------
541 -- Verify that both sides are not the same
542 -----------------------------------------------------------
543 -----------------------------------------------------------
544 -- Bug 1284273:
545 -- The following check is only applicable for Borrow and
546 -- Payback transactions
547 -----------------------------------------------------------
548 if (X_Transaction_Type_Id in (66, 68)) then
549 if (l_project_control_lev = 1) then
550 if (nvl(l_from_project_id,0) = nvl(l_to_project_id,0)) then
551 X_Error_Code := 'BWPY-Same Project';
552 fnd_message.set_name('PJM', X_Error_Code);
553 return(l_failure);
554 end if;
555 elsif (l_project_control_lev = 2) then
556 if (nvl(l_from_task_id,0) = nvl(l_to_task_id,0)) then
557 X_Error_Code := 'BWPY-Same Task';
558 fnd_message.set_name('PJM', X_Error_Code);
559 return(l_failure);
560 end if;
561 end if;
562 end if;
563
564 end if;
565
566
567 -----------------------------------------------------------
568 -- If Transaction Type is 'Project Borrow'
569 -----------------------------------------------------------
570 if ( X_Transaction_Type_Id = 66 ) then
571
572 --------------------------------------------------------------
573 -- Verify that the from locator and to locator is not common
574 -- locator
575 --------------------------------------------------------------
576
577 if (l_from_project_id is null or l_to_project_id is null) then
578 X_Error_Code := 'BWPY-Borrow Against Common';
579 fnd_message.set_name('PJM', X_Error_Code);
580 return (l_failure);
581 end if;
582
583 BEGIN
584
585 OPEN c(l_project_control_lev,
586 l_from_project_id,
587 l_from_task_id,
588 l_to_project_id,
589 l_to_task_id,
590 X_organization_id,
594 FETCH c INTO l_outstanding_qty;
591 X_Inventory_Item_Id,
592 X_Revision);
593
595 CLOSE C;
596
597 --------------------------------------------------------------------
598 -- Verify that the lending project does not have an outstanding loan
599 -- from the borrow project for the same item.
600 --------------------------------------------------------------------
601
602 if (l_outstanding_qty > 0) then
603 X_Error_Code := 'BWPY-Loan Loop Detected';
604 fnd_message.set_name('PJM', X_Error_Code);
605 return( l_failure);
606 end if;
607
608 EXCEPTION
609 when NO_DATA_FOUND then
610 null;
611 END;
612
613 BEGIN
614 select sum(moq.transaction_quantity)
615 into l_onhand_qty
616 from mtl_onhand_quantities_detail moq,
617 mtl_item_locations mil
618 where mil.project_id = l_from_project_id
619 and mil.task_id = l_from_task_id
620 and mil.subinventory_code = X_From_Subinventory
621 and mil.organization_id = X_Organization_Id
622 and mil.inventory_location_id = moq.locator_id
623 and mil.organization_id = moq.organization_id
624 and mil.subinventory_code = moq.subinventory_code
625 and moq.inventory_item_id = X_Inventory_Item_Id;
626
627 ------------------------------------------------------------------
628 -- Verify the lending project has sufficient available on-hand
629 ------------------------------------------------------------------
630
631 if (l_onhand_qty < X_Primary_Quantity) then
632 X_Error_Code := 'BWPY-Insufficient Onhand';
633 fnd_message.set_name('PJM', X_Error_Code);
634 return (l_failure);
635 end if;
636
637 EXCEPTION
638 when NO_DATA_FOUND then
639 X_Error_Code := 'BWPY-Insufficient Onhand';
640 fnd_message.set_name('PJM', X_Error_Code);
641 return (l_failure);
642 END;
643
644 --------------------------------------------------------------
645 -- Verify that scheduled_payback_date is valid
646 --------------------------------------------------------------
647
648 if ( X_payback_date is null ) then
649 X_Error_Code := 'BWPY-Payback Date Required';
650 fnd_message.set_name('PJM', X_Error_Code);
651 return (l_failure);
652 end if;
653
654 if ( X_payback_date < sysdate OR
655 X_payback_date < X_transaction_date ) then
656 X_Error_Code := 'BWPY-Payback Date Invalid';
657 fnd_message.set_name('PJM', X_Error_Code);
658 return (l_failure);
659 end if;
660
661 --------------------------------------------------
662 -- If Transaction Type is 'Project Payback'
663 --------------------------------------------------
664 elsif ( X_Transaction_Type_Id = 68) then
665
666 --------------------------------------------------------------------
667 -- Verify the desination locator is not a common locator
668 --------------------------------------------------------------------
669
670 if (l_from_project_id is null or l_to_project_id is null) then
671 X_Error_Code := 'BWPY-Payback to Common';
672 fnd_message.set_name('PJM', X_Error_Code);
673 return (l_failure);
674 end if;
675
676 BEGIN
677 select nvl(sum(outstanding_quantity),0)
678 into l_outstanding_qty
679 from pjm_borrow_transactions
680 where decode(l_project_control_lev, 1, lending_project_id,
681 lending_task_id) =
682 decode(l_project_control_lev, 1, l_to_project_id, l_to_task_id)
683 and decode(l_project_control_lev, 1, borrow_project_id,
684 borrow_task_id) =
685 decode(l_project_control_lev, 1, l_from_project_id, l_from_task_id)
686 and organization_id = X_Organization_Id
687 and Inventory_item_id = X_Inventory_Item_Id;
688
689 --------------------------------------------------------------------
690 -- Verify there is an outstanding loan balance for the item
691 -- against the lendinf project
692 --------------------------------------------------------------------
693
694 if (l_outstanding_qty = 0) then
695 X_Error_Code := 'BWPY-No Loan Balance';
696 fnd_message.set_name('PJM', X_Error_Code);
697 return(l_failure);
698 end if;
699
700 ---------------------------------------------------------------------
701 -- Verify the payback quantity is not greater than the total
702 -- outstanding balance
703 ---------------------------------------------------------------------
704
705 if (l_outstanding_qty < X_Primary_Quantity) then
706 X_Error_Code := 'BWPY-Payback Quantity Exceeded';
707 fnd_message.set_name('PJM', X_Error_Code);
708 return(l_failure);
709 end if;
710
711 EXCEPTION
712 when NO_DATA_FOUND then
716 END;
713 X_Error_Code := 'BWPY-Payback Quantity Exceeded';
714 fnd_message.set_name('PJM', X_Error_Code);
715 return(l_failure);
717
718 end if;
719
720 ------------------------------------------------------------------
721 -- Verify the physical movement required
722 ------------------------------------------------------------------
723
724 if ((X_From_Subinventory <> X_To_Subinventory) OR
725 (l_From_physical_loc <> l_To_physical_loc)) then
726
727 X_Error_Code := 'BWPY-Physical Transfer';
728 fnd_message.set_name('PJM', X_Error_Code);
729 return(l_warning);
730 end if;
731
732 return(l_success);
733
734 END VALIDATE_TRX;
735
736 END PJM_BORROW_PAYBACK;