1 PACKAGE BODY RCV_TRANSACTIONS_SV AS
2 /* $Header: RCVTXTXB.pls 120.1 2006/08/29 05:17:25 anviswan noship $*/
3
4 /*===========================================================================
5
6 FUNCTION NAME: val_receiving_controls
7
8
9 1. Receiving controls are only checked for vendor receipts. Intransit
10 shipments cannot be rejected and we have not way to define org->org
11 receiving controls.
12 2. Controls are only checked if the exception level is 'REJECT'
13 3. Quantity tolerances are not checked. It is not possible to
14 over-receive an express receipt.
15 4. Standard receipts will be created for the ship-to location
16 specified on the PO so the 'enforce ship-to location' control
17 is not tested.
18 5. Routing controls are checked for both Vendor sourced and
19 intransit receipts.
20
21 ===========================================================================*/
22
23 FUNCTION val_receiving_controls (
24 X_transaction_type IN VARCHAR2,
25 X_auto_transact_code IN VARCHAR2,
26 X_expected_receipt_date IN DATE,
27 X_transaction_date IN DATE,
28 X_routing_header_id IN NUMBER,
29 X_po_line_location_id IN NUMBER,
30 X_item_id IN NUMBER,
31 X_vendor_id IN NUMBER,
32 X_to_organization_id IN NUMBER)
33 RETURN NUMBER IS
34
35 transaction_ok NUMBER := 1;
36 enforce_ship_to_loc VARCHAR2(20);
37 allow_substitutes VARCHAR2(20);
38 routing_id NUMBER;
39 qty_rcv_tolerance NUMBER;
40 qty_rcv_exception VARCHAR2(20);
41 days_early_receipt NUMBER;
42 days_late_receipt NUMBER;
43 rcv_date_exception VARCHAR2(20);
44 allow_routing_override VARCHAR2(20);
45 expected_date DATE;
46 high_range_date DATE;
47 low_range_date DATE;
48 X_progress VARCHAR2(4) := '000';
49
50 --Bug : 3724862
51 l_drop_ship_flag po_line_locations.drop_ship_flag%type;
52
53
54 BEGIN
55
56 /*
57 ** Get the receiving controls for this transaction.
58 */
59
60 /*
61 ** DEBUG: Will this function work properly on getting the routing control
62 ** for internally sourced shipments
63 */
64 X_progress := '200';
65 rcv_core_s.get_receiving_controls (X_po_line_location_id,
66 X_item_id,
67 X_vendor_id,
68 X_to_organization_id,
69 enforce_ship_to_loc,
70 allow_substitutes,
71 routing_id,
72 qty_rcv_tolerance,
73 qty_rcv_exception,
74 days_early_receipt,
75 days_late_receipt,
76 rcv_date_exception);
77
78 /* -- dbms_output.put_line ('Val Receiving Controls : enforce_ship_to_loc : ' ||
79 -- enforce_ship_to_loc);
80 -- dbms_output.put_line ('Val Receiving Controls : allow_substitutes : ' ||
81 -- allow_substitutes);
82 -- dbms_output.put_line ('Val Receiving Controls : routing_id : ' ||
83 -- to_char(routing_id));
84 -- dbms_output.put_line ('Val Receiving Controls : qty_rcv_tolerance : ' ||
85 -- to_char(qty_rcv_tolerance));
86 -- dbms_output.put_line ('Val Receiving Controls : rcv_date_exception : ' ||
87 -- rcv_date_exception);
88 -- dbms_output.put_line ('Val Receiving Controls : qty_rcv_exception : ' ||
89 -- qty_rcv_exception);*/
90 /* -- dbms_output.put_line ('Val Receiving Controls : days_early_receipt : ' ||
91 -- substr(to_char(days_early_receipt),1,3));
92 -- dbms_output.put_line ('Val Receiving Controls : days_late_receipt : ' ||
93 -- substr(to_char(days_late_receipt),1,3));
94 -- dbms_output.put_line ('Val Receiving Controls : rcv_date_exception : ' ||
95 -- rcv_date_exception);*/
96 /*
97 ** if the days exception is set to reject then verify that the receipt
98 ** falls within the date tolerances
99 */
100 IF (rcv_date_exception='REJECT') THEN
101
102 /*
103 ** Check to see that you have a promised date on the po. If not
104 ** then see if you have an expected date. If not then the trx
105 ** passed date validation
106 ** I have placed either the promised date if it is set or the
107 ** need by date into the expected_receipt date column in the interface
108 */
109 IF (X_expected_receipt_date IS NOT NULL) THEN
110
111 expected_date := X_expected_receipt_date;
112
113 ELSE
114 transaction_ok := 0;
115
116 END IF;
117
118 /*
119 ** If you have a date to compare against then set up the range
120 ** based on the days early and late parameters
121 */
122 IF ( transaction_ok > 0 ) THEN
123
124 low_range_date := expected_date - days_early_receipt;
125 high_range_date := expected_date + days_late_receipt;
126
127 -- dbms_output.put_line ('val_receiving_controls : expected_date : ' ||
128 -- to_char(expected_date));
129 -- dbms_output.put_line ('val_receiving_controls : low_range_date : ' ||
130 -- to_char(low_range_date));
131 -- dbms_output.put_line ('val_receiving_controls : high_range_date : ' ||
132 -- to_char(high_range_date));
133
134 /*
135 ** If the transaction date is between the range then it's okay
136 ** to process.
137 */
138 IF (X_transaction_date >= low_range_date AND
139 X_transaction_date <= high_range_date) THEN
140
141 transaction_ok := 0;
142
143 ELSE
144 /* Transaction_Ok = 1 indicates that
145 ** receipt date tolerance is exceeded. */
146 transaction_ok := 1;
147 END IF;
148
149 END IF; -- (transaction_ok > 0)
150
151 ELSE --(rcv_date_exception <> REJECT)
152 transaction_ok := 0;
153 END IF;
154
155 /* Bug 3724862 : If the Express receipt is against a DropShip PO line then
156 ** the routing can only be 'Direct Delivery'. We will error out if
157 ** the routing is not 'Direct Delivery' in the rti.
158 */
159
160 IF ( X_po_line_location_id is not null and
161 transaction_ok = 0 ) THEN
162
163 select nvl(drop_ship_flag,'N')
164 into l_drop_ship_flag
165 from po_line_locations_all
166 where line_location_id = X_po_line_location_id;
167
168 IF ( l_drop_ship_flag = 'Y' and
169 X_transaction_type = 'RECEIVE' and
170 X_auto_transact_code <> 'DELIVER' ) THEN
171
172 transaction_ok := 3;
173
174 END IF;
175 END IF;
176
177 /*
178 ** Check the routing controls to see if the transaction type matches the
179 ** routing specfied on the po or by the hierarchy for item, vendor for
180 ** internally sourced shipments
181 */
182
183 /*
184 ** This component of the check is a little different thab others since
185 ** we have a carry over of the transaction_ok flag. If the flag is
186 ** already set to false then you don't want to perform any other checks
187 */
188 IF (transaction_ok = 0 ) THEN
189 /*
190 ** Go get the routing override value to see if you need to check the
191 ** routing control. If routing override is set to 'Y' then you don't
192 ** need to perform this check since any routing is allowed
193 */
194 X_progress := '300';
195
196 -- dbms_output.put_line('Getting the Routing Info ');
197
198 allow_routing_override := rcv_setup_s.get_override_routing;
199
200 -- dbms_output.put_line ('val_receiving_controls : allow_routing_override : ' ||
201 -- allow_routing_override);
202 -- dbms_output.put_line ('val_receiving_controls : transaction_type : '||
203 -- X_transaction_type);
204 -- dbms_output.put_line ('val_receiving_controls : routing_id : ' ||
205 -- to_char(routing_id));
206
207 /*
208 ** Check the routing controls. If routing_override is set to Y then you
209 ** don't care about the routing controls. Otherwise check to make sure
210 ** you're express option is in line with the routing id
211 */
212 IF (allow_routing_override = 'N' AND transaction_ok = 0 ) THEN
213
214 /*
215 ** You can only do express direct if routing is set to direct
216 */
217 IF (X_transaction_type = 'RECEIVE' AND
218 X_auto_transact_code = 'DELIVER' AND
219 (routing_id IN (3,0))) THEN
220
221 /*
222 ** Direct delivery is allowed
223 */
224 transaction_ok := 0;
225
226 /*
227 ** You can only do express receipt if routing is set to
228 ** standard receipt or inspection required
229 */
230 ELSIF (X_transaction_type = 'RECEIVE' AND
231 X_auto_transact_code = 'RECEIVE' AND
232 (X_routing_header_id IN (1, 2, 0))) THEN
233 /*
234 ** standard receipt is allowed
235 */
236 transaction_ok := 0;
237
238 ELSE
239 /*
240 ** Routing Control is On and the Routing Definitions
241 ** cannot be overridden.Set the return value to
242 ** flag Routing Information as the cause of Failure.
243 */
244 transaction_ok := 2;
245
246 END IF;
247
248 ELSE
249 transaction_ok := 0;
250
251 END IF;
252
253 END IF;
254
255
256 RETURN(transaction_ok);
257
258
259 EXCEPTION
260 WHEN OTHERS THEN
261 po_message_s.sql_error('val_receiving_controls', x_progress, sqlcode);
262 RAISE;
263
264 END val_receiving_controls;
265
266 /*===========================================================================
267
268 FUNCTION NAME: val_wip_info
269
270 ===========================================================================*/
271 FUNCTION val_wip_info (
272 X_to_organization_id IN NUMBER,
273 X_wip_entity_id IN NUMBER,
274 X_wip_operation_seq_num IN NUMBER,
275 X_wip_resource_seq_num IN NUMBER,
276 X_wip_line_id IN NUMBER,
277 X_wip_repetitive_schedule_id IN NUMBER,
278 p_po_line_id IN NUMBER) -- bug 2619164
279 RETURN NUMBER IS
280
281 valid_open_job NUMBER := 0;
282 X_progress VARCHAR2(4) := '000';
283 l_osp_flag po_line_types_b.outside_operation_flag%TYPE; -- bug 2619164
284
285
286 BEGIN
287
288 /*
289 ** The required info for shop floor is the
290 ** job, the op seq num, the reource seq num, the
291 ** repetive schedule and the wip line
292 */
293
294 /*
295 ** First make sure all the required id elemnts are present. If not
296 ** then these rows cannot be transacted.
297 */
298 IF X_to_organization_id IS NULL THEN
299
300 return 10;
301
302 END IF;
303
304 IF X_wip_entity_id IS NULL THEN
305
306 return 20;
307
308 END IF;
309
310 IF (l_osp_flag = 'Y') AND (X_wip_operation_seq_num IS NULL) THEN
311
312 return 30;
313
314 END IF;
315
316 -- bug 2619164 start
317 -- for direct item, wip_resource_seq_num will always be null
318 -- for outside service, wip_resource_seq_num can not be null
319
320 SELECT plt.outside_operation_flag
321 INTO l_osp_flag
322 FROM po_line_types plt,
323 po_lines pl
324 WHERE plt.line_type_id = pl.line_type_id
325 AND pl.po_line_id = p_po_line_id;
326
327 IF (l_osp_flag = 'Y') AND (X_wip_resource_seq_num IS NULL) THEN
328
329 RETURN 40;
330
331 END IF;
332 -- bug 2619164 end
333
334 /*
335 ** If this is an repetitive job then make sure that the job is
336 ** open. If it is not then this cannot be transacted.
337 */
338 IF (X_wip_repetitive_schedule_id IS NOT NULL AND
339 X_wip_line_id IS NOT NULL) THEN
340
341 /* DEBUG:
342 ** We need a function or a view from mike to determine if a job
343 ** is still open or not
344 */
345 BEGIN
346
347 X_progress := '1250';
348 SELECT 1
349 INTO valid_open_job
350 FROM WIP_REPETITIVE_SCHEDULES WRS
351 WHERE WRS.ORGANIZATION_ID = X_to_organization_id
352 AND WRS.REPETITIVE_SCHEDULE_ID =
353 X_wip_repetitive_schedule_id
354 AND WRS.STATUS_TYPE IN (3,4);
355
356 /* If the status of the job is closed then return */
357 IF (valid_open_job = 0) THEN
358 RETURN 50;
359 END IF;
360
361 /*
362 ** If the status of the job is no longed open then you won't
363 ** return a row and therefore the row cannot be transacted
364 */
365 EXCEPTION
366 WHEN NO_DATA_FOUND THEN
367 RETURN 50;
368 END;
369
370 /*
371 ** If this is an discrete job then make sure that the job is
372 ** open. If it is not then this cannot be transacted.
373 */
374 ELSIF (X_wip_repetitive_schedule_id IS NULL AND
375 X_wip_line_id IS NULL) THEN
376
377 /* DEBUG:
378 ** We need a function or a view from mike to determine if a job
379 ** is still open or not
380 */
381 BEGIN
382
383 X_progress := '1260';
384 SELECT 1
385 INTO valid_open_job
386 FROM WIP_DISCRETE_JOBS WDJ
387 WHERE WDJ.ORGANIZATION_ID = X_to_organization_id
388 AND WDJ.WIP_ENTITY_ID = X_wip_entity_id
389 AND WDJ.STATUS_TYPE IN (3,4);
390
391 /* If the status of the job is closed then return */
392 IF (valid_open_job = 0) THEN
393 RETURN 60;
394 END IF;
395
396 /*
397 ** If the status of the job is no longed open then you won't
398 ** return a row and therefore the row cannot be transacted
399 */
400 EXCEPTION
401 WHEN NO_DATA_FOUND THEN
402 RETURN 60;
403
404 END;
405
406 ELSE
407 /*
408 ** one or the other, but not both, of the repetitive
409 ** schedule and wip line have been specified
410 */
411 RETURN 70;
412
413 END IF;
414
415 RETURN 0;
416
417 EXCEPTION
418 WHEN OTHERS THEN
419 po_message_s.sql_error('val_wip_info', X_progress, sqlcode);
420 RAISE;
421
422 END val_wip_info;
423
424 /*===========================================================================
425
426 PROCEDURE NAME: val_if_inventory_destination
427
428 ===========================================================================*/
429
430 /*
431 ** Check to see if any of the distributions are of type inventory
432 */
433 FUNCTION val_if_inventory_destination (X_line_location_id IN NUMBER,
434 X_shipment_line_id IN NUMBER)
435 RETURN BOOLEAN IS
436
437 X_number_of_inv_dest NUMBER := 0;
438 X_progress VARCHAR2(4) := '000';
439
440 BEGIN
441
442 X_progress := '600';
443 /*
444 ** Check to see which id is set to know which table to check for
445 ** inventory destination_type_code
446 */
447 IF (X_line_location_id IS NOT NULL) THEN
448
449 X_progress := '610';
450
451 SELECT count(1)
452 INTO X_number_of_inv_dest
453 FROM po_distributions pd
454 WHERE pd.line_location_id = X_line_location_id
455 AND pd.destination_type_code = 'INVENTORY';
456
457 ELSE
458
459 X_progress := '620';
460
461 SELECT count(1)
462 INTO X_number_of_inv_dest
463 FROM rcv_shipment_lines rsl
464 WHERE rsl.shipment_line_id = X_shipment_line_id
465 AND rsl.destination_type_code = 'INVENTORY';
466 END IF;
467
468 IF (X_number_of_inv_dest > 0) THEN
469 RETURN TRUE;
470 ELSE
471 RETURN FALSE;
472 END IF;
473
474 EXCEPTION
475 WHEN OTHERS THEN
476 po_message_s.sql_error('val_if_inventory_destination', x_progress, sqlcode);
477 RAISE;
478
479 END val_if_inventory_destination;
480
481 /*===========================================================================
482
483 FUNCTION NAME: val_deliver_destination
484
485 Ensure that all mandatory columns for each destination type are
486 populated. If a mandatory column is not specified, the transaction
487 cannot be processed via express.
488 The exceptions are subinventory and locator. If a sub is provided
489 it will be used otherwise the default receiving subinventory will
490 be used (if available). Locator control is evaluated if a sub is
491 provided or defaulted and if locator control is required, the default
492 locator for the sub will be used.
493
494 =============================================================================*/
495
496 FUNCTION val_deliver_destination (
497 X_to_organization_id IN NUMBER,
498 X_item_id IN NUMBER,
499 X_destination_type_code IN VARCHAR2,
500 X_deliver_to_location_id IN NUMBER,
501 X_subinventory IN VARCHAR2)
502 RETURN NUMBER IS
503
504 under_lot_control BOOLEAN := FALSE;
505 under_serial_control BOOLEAN := FALSE;
506 valid_subinventory NUMBER := 0;
507 location_is_valid NUMBER := 1;
508 X_progress VARCHAR2(4) := '000';
509
510 BEGIN
511
512 /*
513 ** Make sure the destination organization is not null since that
514 ** is a requirement irregardless of the desintation type
515 */
516 IF (X_to_organization_id IS NULL) THEN
517 RETURN (10);
518 END IF;
519
520 IF (X_destination_type_code IN ('EXPENSE', 'SHOP FLOOR')) THEN
521
522 -- dbms_output.put_line ('val_deliver_destination : validating location');
523
524 /*
525 ** The only required info for expense is the
526 ** deliver to location
527 */
528 IF (X_deliver_to_location_id IS NULL) THEN
529 RETURN (20);
530
531 ELSE
532 /*
533 ** Make sure that the location is still valid
534 */
535 location_is_valid := po_locations_s.val_location (
536 X_deliver_to_location_id,
537 X_destination_type_code,
538 X_to_organization_id);
539
540 /*
541 ** if the location is not valid then this transaction cannot be
542 ** processed
543 */
544 IF (location_is_valid <> 0) THEN
545 RETURN (30);
546
547 -- dbms_output.put_line ('val_deliver_destination : invalid location');
548
549 END IF;
550
551 END IF;
552
553 ELSIF (X_destination_type_code = 'INVENTORY') THEN
554
555 -- dbms_output.put_line ('val_deliver_destination : validating sub');
556
557 /*
558 ** Make sure that you have a subinventory defined
559 */
560 IF (X_subinventory IS NULL) THEN
561
562 -- dbms_output.put_line ('val_deliver_destination : invalid sub');
563 RETURN (40);
564
565 END IF;
566
567
568 /*
569 ** Validate that the sub on the po was valid
570 ** Validate sub should only return true or false
571 ** or have predefined values as defines so we don't have to
572 ** know the codes
573 */
574
575 X_progress := '1210';
576 valid_subinventory := po_subinventories_s.val_subinventory (
577 X_subinventory,
578 X_to_organization_id,
579 TRUNC(sysdate),
580 X_item_id,
581 X_destination_type_code);
582
583 IF (valid_subinventory <> 0) THEN
584
585 -- dbms_output.put_line ('val_deliver_destination : invalid sub');
586 RETURN (50);
587
588 END IF;
589
590
591 ELSE
592 /*
593 ** The destination type code is goofed and we cannot therefore
594 ** transact the row.
595 */
596 RETURN (60);
597
598 END IF;
599
600 RETURN (0);
601
602 EXCEPTION
603 WHEN OTHERS THEN
604 -- dbms_output.put_line('Progess is :' || X_Progress);
605 po_message_s.sql_error('val_deliver_destination', X_progress, sqlcode);
606 RAISE;
607
608 END val_deliver_destination;
609
610 /*===========================================================================
611
612 PROCEDURE NAME: val_destination_info
613
614 Ensure that all destination information is still valid at the time of
615 receipt. A po can be created with a ship to location or a deliver to
616 location that could become invalid by the time the receipt is entered.
617 To ensure that the lov's for one of these fields does not come up
618 because the item is not in the valid list, we will not populate the
619 column if it is disabled
620
621 =============================================================================*/
622
623 PROCEDURE val_destination_info (
624 X_to_organization_id IN NUMBER,
625 X_item_id IN NUMBER,
626 X_ship_to_location_id IN NUMBER,
627 X_deliver_to_location_id IN NUMBER,
628 X_deliver_to_person_id IN NUMBER,
629 X_subinventory IN VARCHAR2,
630 X_valid_ship_to_location OUT NOCOPY BOOLEAN,
631 X_valid_deliver_to_location OUT NOCOPY BOOLEAN,
632 X_valid_deliver_to_person OUT NOCOPY BOOLEAN,
633 X_valid_subinventory OUT NOCOPY BOOLEAN) IS
634
635 X_return_val NUMBER := 0;
636 location_is_valid NUMBER := 1;
637 X_progress VARCHAR2(4) := '000';
638 x_del_per_val VARCHAR2(240); -- for bug 2392074
639
640 BEGIN
641
642 X_progress := '000';
643
644 /*
645 ** Make sure that the ship to location is still valid
646 */
647 IF (X_ship_to_location_id IS NOT NULL) THEN
648
649 X_valid_ship_to_location := po_locations_s.val_receipt_site_in_org (
650 X_ship_to_location_id,
651 X_to_organization_id);
652
653 END IF;
654
655 X_progress := '010';
656
657 /*
658 ** Make sure that the deliver to location is still valid
659 */
660 IF (X_deliver_to_location_id IS NOT NULL) THEN
661
662 X_return_val := po_locations_s.val_location (
663 X_deliver_to_location_id,
664 'RECEIVING',
665 X_to_organization_id);
666
667 /*
668 ** The return for this is a number: 0 is valid, anything else is invalid
669 */
670 IF (X_return_val = 0) THEN
671
672 X_valid_deliver_to_location := TRUE;
673
674 ELSE
675
676 X_valid_deliver_to_location := FALSE;
677
678 END IF; --(X_return_val = 0)
679
680 END IF; -- (X_deliver_to_location_id IS NOT NULL)
681
682 X_progress := '020';
683
684 /*
685 ** Make sure that the deliver to person is still valid
686 */
687 IF (X_deliver_to_person_id IS NOT NULL) THEN
688
689 /*
690 ** Debug: Need to add a function to PO_EMPLOYEES_SV to
691 ** ensure this person is still active when the receipt
692 ** is done. We'll set it to true for now
693 */
694
695 /* Added the validation on deliver to person
696 for Bug 2392074.
697 */
698 SELECT nvl(max(hre.full_name),'notfound')
699 INTO x_del_per_val
700 FROM hr_employees_current_v hre
701 WHERE (hre.inactive_date IS NULL
702 OR hre.inactive_date > sysdate)
703 AND hre.employee_id = x_deliver_to_person_id;
704
705 if (x_del_per_val='notfound') then
706 X_valid_deliver_to_person := FALSE;
707 else
708 X_valid_deliver_to_person :=TRUE;
709 end if;
710
711
712 END IF;
713
714 X_progress := '030';
715
716 /*
717 ** Make sure that the subinventory is still valid
718 */
719 -- Bug 5495768 : Validation os subinventory should be done only if
720 -- item_id is not null.In case of one time items, the item_id is
721 -- nul.So subinventory validation is not needed.
722
723 IF (X_subinventory IS NOT NULL AND x_item_id IS NOT NULL) THEN
724
725 X_return_val := po_subinventories_s.val_subinventory (
726 x_subinventory, x_to_organization_id, sysdate,
727 x_item_id, 'INVENTORY');
728
729 /*
730 ** The return for this is a number: 0 is valid, anything else is invalid
731 */
732 IF (X_return_val = 0) THEN
733
734 X_valid_subinventory := TRUE;
735
736 ELSE
737
738 X_valid_subinventory := FALSE;
739
740 END IF; --(X_return_val = 0)
741
742 END IF; -- (X_subinventory IS NOT NULL)
743
744 RETURN;
745
746 EXCEPTION
747 WHEN OTHERS THEN
748 po_message_s.sql_error('val_destination_info', x_progress, sqlcode);
749 RAISE;
750
751 END val_destination_info;
752
753 /*===========================================================================
754
755 FUNCTION NAME: val_pending_receipt_trx
756
757 ===========================================================================*/
758 /*
759 ** If there are any receipt supply rows that have not been delivered
760 ** and this line location has multiple distributions then it cannot be
761 ** transacted since you don't know how the user will distribute that
762 ** quantity
763 */
764
765 FUNCTION val_pending_receipt_trx (
766 X_po_line_location_id IN NUMBER,
767 X_group_id IN NUMBER)
768 RETURN BOOLEAN IS
769
770 X_num_of_distributions NUMBER := 0;
771 X_num_of_receipts NUMBER := 0;
772 X_progress VARCHAR2(4) := '000';
773
774 BEGIN
775
776 /*
777 ** This is only an issue for vendor sourced express transactions since
778 ** they are the only ones that can have multiple distributions
779 */
780
781 /*
782 ** Get the number of distributions for this line_location.
783 */
784 X_progress := '500';
785
786 SELECT count (1)
787 INTO X_num_of_distributions
788 FROM po_distributions pod
789 WHERE pod.line_location_id = X_po_line_location_id;
790
791 -- dbms_output.put_line ('val_pending_transactions : X_num_of_distribtions : ' ||
792 -- TO_CHAR(X_num_of_distributions));
793
794 IF (X_num_of_distributions > 1) THEN
795
796 SELECT count (1)
797 INTO X_num_of_receipts
798 FROM rcv_supply rs
799 WHERE rs.po_line_location_id = X_po_line_location_id;
800
801 -- dbms_output.put_line ('val_pending_transactions : X_num_of_receipts : ' ||
802 -- TO_CHAR(X_num_of_receipts));
803
804 /*
805 ** If there is any receiving supply for this line location and there
806 ** are multiple distributions then fail the transaction
807 */
808 IF (X_num_of_receipts > 0) THEN
809
810 RETURN FALSE;
811
812 END IF ;
813
814 END IF;
815
816 RETURN TRUE;
817
818 EXCEPTION
819 /*
820 ** If no rows were found then the transaction is ok
821 */
822 WHEN NO_DATA_FOUND THEN
823 RETURN TRUE;
824 WHEN OTHERS THEN
825 po_message_s.sql_error('val_pending_receipt_trx', x_progress, sqlcode);
826 RAISE;
827
828 END val_pending_receipt_trx;
829
830 /*===========================================================================
831
832 PROCEDURE NAME: get_wip_info
833
834 ===========================================================================*/
835 /*
836 ** Go get the outside processing information for a given receipt line
837 */
838
839 PROCEDURE get_wip_info
840 (X_wip_entity_id IN NUMBER,
841 X_wip_repetitive_schedule_id IN NUMBER,
842 X_wip_line_id IN NUMBER,
843 X_wip_operation_seq_num IN NUMBER,
844 X_wip_resource_seq_num IN NUMBER,
845 X_to_organization_id IN NUMBER,
846 X_job IN OUT NOCOPY VARCHAR2,
847 X_line_num IN OUT NOCOPY VARCHAR2,
848 X_sequence IN OUT NOCOPY NUMBER,
849 X_department IN OUT NOCOPY VARCHAR2) IS
850
851 X_progress VARCHAR2(4) := '000';
852
853 BEGIN
854 --Bug# 2000013 togeorge 09/18/2001
855 --Eam: Split the following sql to 3 different sqls because eAM w/o would
856 -- not have resource information and this sql will fail.
857 /*
858 SELECT we.wip_entity_name job,
859 wn.operation_seq_num sequence,
860 bd.department_code department
861 INTO X_job,
862 X_sequence,
863 X_department
864 FROM wip_entities we,
865 bom_departments bd,
866 wip_operation_resources wr,
867 wip_operations wn,
868 wip_operations wo
869 WHERE wo.wip_entity_id = X_wip_entity_id
870 AND wo.organization_id = X_to_organization_id
871 AND nvl(wo.repetitive_schedule_id, -1) =
872 nvl(X_wip_repetitive_schedule_id, -1)
873 AND wo.operation_seq_num = X_wip_operation_seq_num
874 AND wr.wip_entity_id = X_wip_entity_id
875 AND wr.organization_id = X_to_organization_id
876 AND nvl(wr.repetitive_schedule_id, -1) =
877 nvl(X_wip_repetitive_schedule_id, -1)
878 AND wr.operation_seq_num = X_wip_operation_seq_num
879 AND wr.resource_seq_num = X_wip_resource_seq_num
880 AND wn.wip_entity_id = X_wip_entity_id
881 AND wn.organization_id = X_to_organization_id
882 AND nvl(wn.repetitive_schedule_id, -1) =
883 nvl(X_wip_repetitive_schedule_id, -1)
884 AND wn.operation_seq_num =
885 decode(wr.autocharge_type, 4,
886 nvl(wo.next_operation_seq_num, wo.operation_seq_num),
887 wo.operation_seq_num)
888 AND bd.department_id = wn.department_id
889 AND we.wip_entity_id = X_wip_entity_id
890 AND we.organization_id = X_to_organization_id;
891 */
892
893 if X_wip_entity_id is not null then
894 begin
895 SELECT we.wip_entity_name job
896 INTO X_job
897 FROM wip_entities we
898 WHERE we.wip_entity_id = X_wip_entity_id
899 AND we.organization_id = X_to_organization_id;
900 exception
901 when others then
902 X_job := null;
903 end;
904 end if;
905
906 if X_wip_entity_id is not null and X_wip_operation_seq_num is not null then
907 begin
908 SELECT wn.operation_seq_num sequence,
909 bd.department_code department
910 INTO X_sequence, X_department
911 FROM bom_departments bd,
912 wip_operation_resources wr,
913 wip_operations wn,
914 wip_operations wo
915 WHERE wo.wip_entity_id = X_wip_entity_id
916 AND wo.organization_id = X_to_organization_id
917 AND nvl(wo.repetitive_schedule_id, -1) =
918 nvl(X_wip_repetitive_schedule_id, -1)
919 AND wo.operation_seq_num = X_wip_operation_seq_num
920 AND wr.wip_entity_id = X_wip_entity_id
921 AND wr.organization_id = X_to_organization_id
922 AND nvl(wr.repetitive_schedule_id, -1) =
923 nvl(X_wip_repetitive_schedule_id, -1)
924 AND wr.operation_seq_num = X_wip_operation_seq_num
925 AND wr.resource_seq_num = X_wip_resource_seq_num
926 AND wn.wip_entity_id = X_wip_entity_id
927 AND wn.organization_id = X_to_organization_id
928 AND nvl(wn.repetitive_schedule_id, -1) =
929 nvl(X_wip_repetitive_schedule_id, -1)
930 AND wn.operation_seq_num = wo.operation_seq_num
931 -- Bug#2738959 : Removed the decode statement for autocharge_type 4
932 -- Replaced the following statement with the one above.
933 -- Comparing with operation_seq_num instead of next_operation_seq_num
934 -- AND wn.operation_seq_num =
935 -- decode(wr.autocharge_type, 4,
936 -- nvl(wo.next_operation_seq_num, wo.operation_seq_num),
937 -- wo.operation_seq_num)
938 AND bd.department_id = wn.department_id;
939 exception
940 when no_data_found then
941 --for EAM workorders the above sql would raise no_data_found.
942 --find department code and sequence with out touching resource table.
943 begin
944 select bd.department_code department
945 into X_department
946 from bom_departments bd,wip_operations wn
947 where wn.wip_entity_id = X_wip_entity_id
948 and wn.organization_id = X_to_organization_id
949 and nvl(wn.repetitive_schedule_id, -1) =
950 nvl(X_wip_repetitive_schedule_id, -1)
951 and bd.department_id = wn.department_id;
952 exception
953 when others then
954 X_department :=null;
955 end;
956
957 begin
958 SELECT wo.operation_seq_num sequence
959 INTO X_sequence
960 FROM wip_operations wo
961 WHERE wo.wip_entity_id = X_wip_entity_id
962 AND wo.organization_id = X_to_organization_id
963 AND nvl(wo.repetitive_schedule_id, -1) =
964 nvl(X_wip_repetitive_schedule_id, -1)
965 AND wo.operation_seq_num = X_wip_operation_seq_num;
966 exception
967 when others then
968 X_sequence := null;
969 end;
970 when others then
971 X_sequence :=null;
972 X_department :=null;
973 end;
974 end if;
975 --
976 IF (X_wip_line_id IS NOT NULL) THEN
977
978 select wl.line_code
979 into X_line_num
980 from wip_lines wl
981 where wl.organization_id = X_to_organization_id
982 and wl.line_id = X_wip_line_id;
983
984 END IF;
985
986 EXCEPTION
987 WHEN OTHERS THEN
988 po_message_s.sql_error('get_wip_info', x_progress, sqlcode);
989 RAISE;
990
991 END get_wip_info;
992
993 /*===========================================================================
994 *
995 * PROCEDURE NAME: get_rma_dest_info
996 *
997 *===========================================================================*/
998 /*
999 * ** Get the destination info for the RMA line.
1000 * */
1001
1002 PROCEDURE get_rma_dest_info
1003 (x_oe_order_header_id IN NUMBER,
1004 x_oe_order_line_id IN NUMBER,
1005 x_item_id IN NUMBER,
1006 x_deliver_to_sub IN OUT NOCOPY VARCHAR2,
1007 x_deliver_to_location_id IN OUT NOCOPY NUMBER,
1008 x_deliver_to_location IN OUT NOCOPY VARCHAR2,
1009 x_destination_type_dsp IN OUT NOCOPY VARCHAR2,
1010 x_destination_type_code IN OUT NOCOPY VARCHAR2,
1011 x_to_organization_id IN OUT NOCOPY NUMBER,
1012 x_rate IN OUT NOCOPY NUMBER,
1013 x_rate_date IN OUT NOCOPY DATE) IS
1014
1015 X_progress VARCHAR2(4) := '000';
1016
1017 X_valid_ship_to_location BOOLEAN;
1018 X_valid_deliver_to_location BOOLEAN;
1019 X_valid_deliver_to_person BOOLEAN;
1020 X_valid_subinventory BOOLEAN;
1021
1022 BEGIN
1023
1024 X_progress := '010';
1025
1026 select displayed_field, lookup_code
1027 into x_destination_type_dsp, x_destination_type_code
1028 from po_lookup_codes
1029 where lookup_code = 'RECEIVING'
1030 and lookup_type = 'RCV DESTINATION TYPE';
1031
1032 X_progress := '020';
1033
1034 /* get subinventory, rate, rate_date, item_id, to_organization_id */
1035 select oel.subinventory,
1036 NVL(oel.ship_from_org_id, oeh.ship_from_org_id),
1037 oeh.conversion_rate,
1038 oeh.conversion_rate_date
1039 into x_deliver_to_sub,
1040 x_to_organization_id,
1041 x_rate,
1042 x_rate_date
1043 from oe_order_lines_all oel,
1044 oe_order_headers_all oeh
1045 where oeh.header_id = x_oe_order_header_id
1046 and oel.line_id = x_oe_order_line_id;
1047
1048 X_progress := '030';
1049
1050 /* get location_id and location_code */
1051 select haou.location_id,
1052 hla.location_code
1053 into x_deliver_to_location_id,
1054 x_deliver_to_location
1055 from hr_all_organization_units haou,
1056 hr_locations_all hla
1057 where haou.organization_id = x_to_organization_id
1058 and haou.location_id = hla.location_id;
1059
1060
1061 /*
1062 ** Make sure the dest information is still valid
1063 */
1064 rcv_transactions_sv.val_destination_info (
1065 X_to_organization_id,
1066 X_item_id,
1067 NULL,
1068 X_deliver_to_location_id,
1069 NULL,
1070 X_deliver_to_sub,
1071 X_valid_ship_to_location,
1072 X_valid_deliver_to_location,
1073 X_valid_deliver_to_person,
1074 X_valid_subinventory);
1075
1076 IF (NOT X_valid_deliver_to_location) THEN
1077 X_deliver_to_location_id := NULL;
1078 X_deliver_to_location := NULL;
1079 END IF;
1080
1081 IF (NOT X_valid_subinventory) THEN
1082 X_deliver_to_sub := NULL;
1083 END IF;
1084
1085 EXCEPTION
1086 WHEN OTHERS THEN
1087 po_message_s.sql_error('get_rma_dest_info', x_progress, sqlcode);
1088 RAISE;
1089
1090 END get_rma_dest_info;
1091
1092 END RCV_TRANSACTIONS_SV;
[Home] [Help]