1 PACKAGE BODY rcv_core_s AS
2 /* $Header: RCVCOCOB.pls 120.4 2012/01/05 04:43:51 honwei ship $*/
3
4 -- Read the profile option that enables/disables the debug log
5 g_asn_debug VARCHAR2(1) := asn_debug.is_debug_on; -- Bug 9152790
6 g_log_head CONSTANT VARCHAR2(30) := 'po.plsql.RCV_CORE_S.'; -- <BUG 3365446>
7 e_validation_error EXCEPTION;
8
9 /*===========================================================================
10
11 PROCEDURE NAME: val_destination_info
12
13 ===========================================================================*/
14 PROCEDURE val_destination_info(
15 x_receipt_source_code IN VARCHAR2,
16 x_trx_date IN DATE,
17 x_line_loc_id IN NUMBER,
18 x_dist_id IN NUMBER,
19 x_ship_line_id IN NUMBER,
20 x_org_id IN NUMBER,
21 x_item_id IN NUMBER,
22 x_ship_to_loc_id OUT NOCOPY NUMBER,
23 x_ship_to_loc_code OUT NOCOPY VARCHAR2,
24 x_deliver_to_loc_id OUT NOCOPY NUMBER,
25 x_deliver_to_loc_code OUT NOCOPY VARCHAR2,
26 x_dest_subinv OUT NOCOPY VARCHAR2,
27 x_locator_id OUT NOCOPY NUMBER,
28 x_locator OUT NOCOPY VARCHAR2,
29 x_dest_org_id OUT NOCOPY NUMBER,
30 x_dest_org_code OUT NOCOPY VARCHAR2,
31 x_dest_type_code OUT NOCOPY VARCHAR2,
32 x_deliver_to_person_id OUT NOCOPY NUMBER,
33 x_deliver_to_person OUT NOCOPY VARCHAR2
34 ) IS
35 /*
36 ** Procedure validates whether ship-to location, deliver-to location,
37 ** destination subinventory, destination organization and deliver-to person
38 ** specified as defaults on the purchase order are active for the transaction
39 ** date. If they are not active, they are nulled out.
40 ** If the destination of an item is inventory, then look up default locator
41 ** for that subinventory for that line item.
42 */
43 x_progress VARCHAR2(3) := NULL;
44 x_temp VARCHAR2(50) := NULL;
45 ship_to_loc_id NUMBER;
46 deliver_to_loc_id NUMBER;
47 dest_subinv VARCHAR2(10);
48 dest_org_id NUMBER;
49 dest_type_code NUMBER;
50 deliver_to_person_id NUMBER;
51 x_ship_head_id NUMBER;
52 x_active_date DATE;
53 BEGIN
54 IF (x_receipt_source_code = 'VENDOR') THEN
55 x_progress := '010';
56
57 SELECT deliver_to_location_id,
58 deliver_to_person_id,
59 destination_subinventory,
60 destination_organization_id,
61 destination_type_code
62 INTO deliver_to_loc_id,
63 deliver_to_person_id,
64 dest_subinv,
65 dest_org_id,
66 dest_type_code
67 FROM po_distributions
68 WHERE po_distribution_id = x_dist_id;
69
70 x_progress := '020';
71
72 SELECT ship_to_location_id
73 INTO ship_to_loc_id
74 FROM po_line_locations
75 WHERE line_location_id = x_line_loc_id;
76 ELSE
77 x_progress := '030';
78
79 SELECT deliver_to_location_id,
80 deliver_to_person_id,
81 to_subinventory,
82 to_organization_id,
83 destination_type_code,
84 shipment_header_id
85 INTO deliver_to_loc_id,
86 deliver_to_person_id,
87 dest_subinv,
88 dest_org_id,
89 dest_type_code,
90 x_ship_head_id
91 FROM rcv_shipment_lines
92 WHERE shipment_line_id = x_ship_line_id;
93
94 x_progress := '040';
95
96 SELECT ship_to_location_id
97 INTO ship_to_loc_id
98 FROM rcv_shipment_headers
99 WHERE shipment_header_id = x_ship_head_id;
100 END IF;
101
102 -- Validate ship_to_location_id
103
104 x_progress := '040';
105
106 SELECT location_code
107 INTO x_ship_to_loc_code
108 FROM hr_locations
109 WHERE NVL(inventory_organization_id, x_org_id) = x_org_id
110 AND ( inactive_date IS NULL
111 OR inactive_date > x_trx_date)
112 AND location_id = ship_to_loc_id;
113
114 IF (SQL%NOTFOUND) THEN
115 x_ship_to_loc_id := NULL;
116 x_ship_to_loc_code := NULL;
117 ELSE
118 x_ship_to_loc_id := ship_to_loc_id;
119 END IF;
120
121 -- Validate deliver_to_location
122
123 x_progress := '050';
124
125 SELECT location_code
126 INTO x_deliver_to_loc_code
127 FROM hr_locations
128 WHERE NVL(inventory_organization_id, x_org_id) = x_org_id
129 AND ( inactive_date IS NULL
130 OR inactive_date > x_trx_date)
131 AND location_id = deliver_to_loc_id;
132
133 IF (SQL%NOTFOUND) THEN
134 x_deliver_to_loc_id := NULL;
135 x_deliver_to_loc_code := NULL;
136 ELSE
137 x_deliver_to_loc_id := deliver_to_loc_id;
138 END IF;
139
140 -- Validate destination_subinventory
141
142 x_progress := '060';
143
144 SELECT 'Check to see if subinventory is valid'
145 INTO x_temp
146 FROM mtl_secondary_inventories
147 WHERE ( disable_date IS NULL
148 OR disable_date > x_trx_date)
149 AND organization_id = x_org_id
150 AND secondary_inventory_name = dest_subinv
151 AND ( (x_item_id IS NULL)
152 OR ( x_item_id IS NOT NULL
153 AND EXISTS(SELECT 'valid subinventory'
154 FROM mtl_system_items msi
155 WHERE msi.organization_id = x_org_id
156 AND msi.inventory_item_id = x_item_id
157 AND ( msi.restrict_subinventories_code = 2
158 OR ( msi.restrict_subinventories_code = 1
159 AND EXISTS(SELECT 'valid subinventory'
160 FROM mtl_item_sub_inventories mis
161 WHERE mis.organization_id = x_org_id
162 AND mis.inventory_item_id = x_item_id
163 AND mis.secondary_inventory = secondary_inventory_name))))
164 )
165 );
166
167 IF (SQL%NOTFOUND) THEN
168 x_dest_subinv := NULL;
169 ELSE
170 x_dest_subinv := dest_subinv;
171 END IF;
172
173 -- Validate destination_organization
174
175 x_progress := '070';
176
177 --perf bugfix 5217401
178 SELECT mp.organization_code
179 INTO x_dest_org_code
180 FROM HR_ORGANIZATION_UNITS HOU,
181 MTL_PARAMETERS MP
182 WHERE HOU.ORGANIZATION_ID = MP.ORGANIZATION_ID
183 AND HOU.organization_id = dest_org_id
184 AND ( HOU.DATE_TO is NULL OR HOU.DATE_To > x_trx_date);
185
186 IF (SQL%NOTFOUND) THEN
187 x_dest_org_id := NULL;
188 x_dest_org_code := NULL;
189 ELSE
190 x_dest_org_id := dest_org_id;
191 END IF;
192
193 -- Validate deliver_to_person
194
195 x_progress := '080';
196
197 SELECT full_name
198 INTO x_deliver_to_person
199 FROM hr_employees_current_v
200 WHERE ( inactive_date IS NULL
201 OR inactive_date > x_trx_date)
202 AND employee_id = deliver_to_person_id;
203
204 IF (SQL%NOTFOUND) THEN
205 x_deliver_to_person_id := NULL;
206 x_deliver_to_person := NULL;
207 ELSE
208 x_deliver_to_person_id := deliver_to_person_id;
209 END IF;
210
211 -- Validate locator for INVENTORY destination
212
213 x_progress := '090';
214
215 IF (dest_type_code = 'INVENTORY') THEN
216 --call Thomas's get_default_locator, val_locator routines
217 NULL;
218 END IF;
219 EXCEPTION
220 WHEN OTHERS THEN
221 po_message_s.sql_error('val_destination_info',
222 x_progress,
223 SQLCODE
224 );
225 RAISE;
226 END val_destination_info;
227
228 /*===========================================================================
229
230 PROCEDURE NAME: get_receiving_controls
231
232 ===========================================================================*/
233 PROCEDURE get_receiving_controls(
234 x_line_loc_id IN NUMBER,
235 x_item_id IN NUMBER,
236 x_vendor_id IN NUMBER,
237 x_org_id IN NUMBER,
238 x_enforce_ship_to_loc IN OUT NOCOPY VARCHAR2,
239 x_allow_substitutes IN OUT NOCOPY VARCHAR2,
240 x_routing_id IN OUT NOCOPY NUMBER,
241 x_qty_rcv_tolerance IN OUT NOCOPY NUMBER,
242 x_qty_rcv_exception IN OUT NOCOPY VARCHAR2,
243 x_days_early_receipt IN OUT NOCOPY NUMBER,
244 x_days_late_receipt IN OUT NOCOPY NUMBER,
245 x_rcv_date_exception IN OUT NOCOPY VARCHAR2,
246 p_payment_type IN VARCHAR2 DEFAULT NULL
247 ) IS
248 x_progress VARCHAR2(3) := NULL;
249 l_routing_name rcv_routing_headers.routing_name%TYPE; -- <BUG 3365446>
250 BEGIN
251 -- <BUG 3365446 START>
252 --
253 rcv_core_s.get_receiving_controls(p_order_type_lookup_code => NULL,
254 p_purchase_basis => NULL,
255 p_line_location_id => x_line_loc_id,
256 p_item_id => x_item_id,
257 p_org_id => x_org_id,
258 p_vendor_id => x_vendor_id,
259 x_enforce_ship_to_loc_code => x_enforce_ship_to_loc,
260 x_allow_substitute_receipts => x_allow_substitutes,
261 x_routing_id => x_routing_id,
262 x_routing_name => l_routing_name,
263 x_qty_rcv_tolerance => x_qty_rcv_tolerance,
264 x_qty_rcv_exception_code => x_qty_rcv_exception,
265 x_days_early_receipt_allowed => x_days_early_receipt,
266 x_days_late_receipt_allowed => x_days_late_receipt,
267 x_receipt_days_exception_code => x_rcv_date_exception,
268 p_payment_type => p_payment_type
269 );
270 -- <BUG 3365446 END>
271
272 EXCEPTION
273 WHEN OTHERS THEN
274 po_message_s.sql_error('get_receiving_controls',
275 x_progress,
276 SQLCODE
277 );
278 RAISE;
279 END get_receiving_controls;
280
281 ------------------------------------------------------------------<BUG 3365446>
282 -------------------------------------------------------------------------------
283 --Start of Comments
284 --Name: get_receiving_controls
285 --Pre-reqs:
286 -- None.
287 --Modifies:
288 -- None.
289 --Locks:
290 -- None.
291 --Function:
292 --
293 -- (Overloaded procedure)
294 -- Retrieves default Receiving Controls according to the following hierarchy:
295 --
296 -- 1) PO_LINE_LOCATIONS_ALL (...from the PO Shipment)
297 -- 2) MTL_SYSTEM_ITEMS (...from the Item Master definition)
298 -- 3) PO_VENDORS (...from the Supplier defaults)
299 -- 4) RCV_PARAMETERS (...from the Receiving Controls Setup)
300 --
301 -- For each Receiving Control not gotten at the first level, we will try to
302 -- retrieve a value for it from the next level, and so on.
303 --
304 -- For Services lines (i.e. 'FIXED PRICE/SERVICES','*/TEMP LABOR'), we will
305 -- directly assign values for most of the parameters regardless of what are
306 -- specified in the various setups:
307 --
308 -- FIXED PRICE/SERVICES * / TEMP LABOR
309 -- -------------------- --------------------
310 -- x_enforce_ship_to_loc_code 'NONE' 'NONE'
311 -- x_allow_substitute_receipts NULL NULL
312 -- x_routing_id 3 3
313 -- x_routing_name 'Direct Delivery' 'Direct Delivery'
314 -- x_qty_rcv_tolerance <default hierarchy> <default hierarchy>
315 -- x_qty_rcv_exception_code <default hierarchy> <default hierarchy>
316 -- x_days_early_receipt_allowed <default hierarchy> NULL
317 -- x_days_late_receipt_allowed <default hierarchy> NULL
318 -- x_receipt_days_exception_code <default hierarchy> NULL
319 --
320 --Parameters:
321 --IN:
322 --p_order_type_lookup_code
323 -- Value basis of Shipment on which Receiving Controls will be defaulted.
324 -- If NULL, Shipment will be treated as non-Fixed Price line.
325 --p_purchase_basis
326 -- Purchase basis of Shipment on which Receiving Controls will be defaulted.
327 -- If NULL, Shipment will be treated as non-Temp Labor line.
328 --p_line_location_id
329 -- Shipment ID to get values from PO.
330 --p_item_id
331 -- Item ID (used with Org ID) to get default from Item Master definition.
332 --p_org_id
333 -- Org ID (used with Item ID) to get default from Item Master definition.
334 --p_vendor_id
335 -- Supplier ID to get default from Supplier Setup.
336 --p_drop_ship_flag := 'N'
337 -- Drop Ship flag. If the Shipment is Drop Ship, Routing name is set to
338 -- 'Direct Delivery' and x_routing_id = 3.
339 --OUT:
340 --x_routing_id
341 --x_routing_name
342 --x_qty_rcv_tolerance
343 --x_qty_rcv_exception_code
344 --x_days_early_receipt_allowed
345 --x_days_late_receipt_allowed
346 --x_receipt_days_exception_code
347 --Testing:
348 -- None.
349 --End of Comments
350 -------------------------------------------------------------------------------
351 -------------------------------------------------------------------------------
352 PROCEDURE get_receiving_controls(
353 p_order_type_lookup_code IN VARCHAR2,
354 p_purchase_basis IN VARCHAR2,
355 p_line_location_id IN NUMBER,
356 p_item_id IN NUMBER,
357 p_org_id IN NUMBER,
358 p_vendor_id IN NUMBER,
359 p_drop_ship_flag IN VARCHAR2 := 'N',
360 x_enforce_ship_to_loc_code OUT NOCOPY VARCHAR2,
361 x_allow_substitute_receipts OUT NOCOPY VARCHAR2,
362 x_routing_id OUT NOCOPY NUMBER,
363 x_routing_name OUT NOCOPY VARCHAR2,
364 x_qty_rcv_tolerance OUT NOCOPY NUMBER,
365 x_qty_rcv_exception_code OUT NOCOPY VARCHAR2,
366 x_days_early_receipt_allowed OUT NOCOPY NUMBER,
367 x_days_late_receipt_allowed OUT NOCOPY NUMBER,
368 x_receipt_days_exception_code OUT NOCOPY VARCHAR2,
369 p_payment_type IN VARCHAR2 DEFAULT NULL
370 ) IS
371 l_api_name VARCHAR2(30) := 'get_receiving_controls';
372 l_log_head VARCHAR2(100) := g_log_head || l_api_name;
373 l_progress VARCHAR2(3);
374 BEGIN
375 l_progress := '000';
376 po_debug.debug_begin(l_log_head);
377
378 -- PO Shipment ============================================================
379
380 IF (p_line_location_id IS NOT NULL) THEN
381 l_progress := '010';
382 po_debug.debug_stmt(l_log_head,
383 l_progress,
384 'Retrieving Receiving Controls from PO Shipment...'
385 );
386 l_progress := '020';
387 po_debug.debug_var(l_log_head,
388 l_progress,
389 'p_line_location_id',
390 p_line_location_id
391 );
392
393 BEGIN
394 SELECT enforce_ship_to_location_code,
395 allow_substitute_receipts_flag,
396 receiving_routing_id,
397 qty_rcv_tolerance,
398 qty_rcv_exception_code,
399 days_early_receipt_allowed,
400 days_late_receipt_allowed,
401 receipt_days_exception_code
402 INTO x_enforce_ship_to_loc_code,
403 x_allow_substitute_receipts,
404 x_routing_id,
405 x_qty_rcv_tolerance,
406 x_qty_rcv_exception_code,
407 x_days_early_receipt_allowed,
408 x_days_late_receipt_allowed,
409 x_receipt_days_exception_code
410 FROM po_line_locations_all
411 WHERE line_location_id = p_line_location_id;
412 EXCEPTION
413 WHEN NO_DATA_FOUND THEN
414 NULL;
415 END;
416 END IF;
417
418 -- Item Master ============================================================
419
420 IF (p_item_id IS NOT NULL) THEN
421 l_progress := '030';
422 po_debug.debug_stmt(l_log_head,
423 l_progress,
424 'Retrieving Receiving Controls from Item Master...'
425 );
426 l_progress := '040';
427 po_debug.debug_var(l_log_head,
428 l_progress,
429 'p_item_id',
430 TO_NUMBER(p_item_id)
431 );
432 l_progress := '050';
433 po_debug.debug_var(l_log_head,
434 l_progress,
435 'p_org_id',
436 p_org_id
437 );
438
439 BEGIN
440 SELECT NVL(x_enforce_ship_to_loc_code, enforce_ship_to_location_code),
441 NVL(x_allow_substitute_receipts, allow_substitute_receipts_flag),
442 NVL(x_routing_id, receiving_routing_id),
443 NVL(x_qty_rcv_tolerance, qty_rcv_tolerance),
444 NVL(x_qty_rcv_exception_code, qty_rcv_exception_code),
445 NVL(x_days_early_receipt_allowed, days_early_receipt_allowed),
446 NVL(x_days_late_receipt_allowed, days_late_receipt_allowed),
447 NVL(x_receipt_days_exception_code, receipt_days_exception_code)
448 INTO x_enforce_ship_to_loc_code,
449 x_allow_substitute_receipts,
450 x_routing_id,
451 x_qty_rcv_tolerance,
452 x_qty_rcv_exception_code,
453 x_days_early_receipt_allowed,
454 x_days_late_receipt_allowed,
455 x_receipt_days_exception_code
456 FROM mtl_system_items
457 WHERE inventory_item_id = p_item_id
458 AND NVL(organization_id, -99) = NVL(p_org_id, -99);
459 EXCEPTION
460 WHEN NO_DATA_FOUND THEN
461 NULL;
462 END;
463 END IF;
464
465 -- Supplier ===============================================================
466
467 IF (p_vendor_id IS NOT NULL) THEN
468 l_progress := '060';
469 po_debug.debug_stmt(l_log_head,
470 l_progress,
471 'Retrieving Receiving Controls from Supplier Setup...'
472 );
473 l_progress := '070';
474 po_debug.debug_var(l_log_head,
475 l_progress,
476 'p_vendor_id',
477 p_vendor_id
478 );
479
480 BEGIN
481 SELECT NVL(x_enforce_ship_to_loc_code, enforce_ship_to_location_code),
482 NVL(x_allow_substitute_receipts, allow_substitute_receipts_flag),
483 NVL(x_routing_id, receiving_routing_id),
484 NVL(x_qty_rcv_tolerance, qty_rcv_tolerance),
485 NVL(x_qty_rcv_exception_code, qty_rcv_exception_code),
486 NVL(x_days_early_receipt_allowed, days_early_receipt_allowed),
487 NVL(x_days_late_receipt_allowed, days_late_receipt_allowed),
488 NVL(x_receipt_days_exception_code, receipt_days_exception_code)
489 INTO x_enforce_ship_to_loc_code,
490 x_allow_substitute_receipts,
491 x_routing_id,
492 x_qty_rcv_tolerance,
493 x_qty_rcv_exception_code,
494 x_days_early_receipt_allowed,
495 x_days_late_receipt_allowed,
496 x_receipt_days_exception_code
497 FROM po_vendors
498 WHERE vendor_id = p_vendor_id;
499 EXCEPTION
500 WHEN NO_DATA_FOUND THEN
501 NULL;
502 END;
503 END IF;
504
505 -- Receiving Controls Setup ===============================================
506
507 l_progress := '080';
508 po_debug.debug_stmt(l_log_head,
509 l_progress,
510 'Retrieving Receiving Controls from Default Setup...'
511 );
512
513 BEGIN
514 SELECT NVL(x_enforce_ship_to_loc_code, enforce_ship_to_location_code),
515 NVL(x_allow_substitute_receipts, allow_substitute_receipts_flag),
516 NVL(x_routing_id, receiving_routing_id),
517 NVL(x_qty_rcv_tolerance, qty_rcv_tolerance),
518 NVL(x_qty_rcv_exception_code, qty_rcv_exception_code),
519 NVL(x_days_early_receipt_allowed, days_early_receipt_allowed),
520 NVL(x_days_late_receipt_allowed, days_late_receipt_allowed),
521 NVL(x_receipt_days_exception_code, receipt_days_exception_code)
522 INTO x_enforce_ship_to_loc_code,
523 x_allow_substitute_receipts,
524 x_routing_id,
525 x_qty_rcv_tolerance,
526 x_qty_rcv_exception_code,
527 x_days_early_receipt_allowed,
528 x_days_late_receipt_allowed,
529 x_receipt_days_exception_code
530 FROM rcv_parameters
531 WHERE organization_id = p_org_id;
532 EXCEPTION
533 WHEN NO_DATA_FOUND THEN
534 NULL;
535 END;
536
537 -- Default Values =========================================================
538
539 l_progress := '090';
540 po_debug.debug_stmt(l_log_head,
541 l_progress,
542 'Assigning default values to any unassigned values...'
543 );
544 l_progress := '100';
545 po_debug.debug_var(l_log_head,
546 l_progress,
547 'p_order_type_lookup_code',
548 p_order_type_lookup_code
549 );
550 l_progress := '110';
551 po_debug.debug_var(l_log_head,
552 l_progress,
553 'p_purchase_basis',
554 p_purchase_basis
555 );
556
557 -- Fixed Price/Services Line Types
558 --
559 IF ( (p_order_type_lookup_code = 'FIXED PRICE')
560 AND (p_purchase_basis = 'SERVICES')) THEN
561 SELECT 'NONE',
562 NULL,
563 3 -- 'Direct Delivery'
564 ,
565 NVL(x_qty_rcv_tolerance, 0),
566 NVL(x_qty_rcv_exception_code, 'NONE'),
567 NVL(x_days_early_receipt_allowed, 0),
568 NVL(x_days_late_receipt_allowed, 0),
569 NVL(x_receipt_days_exception_code, 'NONE')
570 INTO x_enforce_ship_to_loc_code,
571 x_allow_substitute_receipts,
572 x_routing_id,
573 x_qty_rcv_tolerance,
574 x_qty_rcv_exception_code,
575 x_days_early_receipt_allowed,
576 x_days_late_receipt_allowed,
577 x_receipt_days_exception_code
578 FROM DUAL;
579 -- Temp Labor Line Types
580 --
581 ELSIF(p_purchase_basis = 'TEMP LABOR') THEN
582 SELECT 'NONE',
583 NULL,
584 3 -- 'Direct Delivery'
585 ,
586 NVL(x_qty_rcv_tolerance, 0),
587 NVL(x_qty_rcv_exception_code, 'NONE'),
588 NULL,
589 NULL,
590 NULL
591 INTO x_enforce_ship_to_loc_code,
592 x_allow_substitute_receipts,
593 x_routing_id,
594 x_qty_rcv_tolerance,
595 x_qty_rcv_exception_code,
596 x_days_early_receipt_allowed,
597 x_days_late_receipt_allowed,
598 x_receipt_days_exception_code
599 FROM DUAL;
600 -- All other Line Types
601 --
602 ELSE
603 SELECT NVL(x_enforce_ship_to_loc_code, 'NONE'),
604 NVL(x_allow_substitute_receipts, 'N'),
605 x_routing_id,
606 NVL(x_qty_rcv_tolerance, 0),
607 NVL(x_qty_rcv_exception_code, 'NONE'),
608 NVL(x_days_early_receipt_allowed, 0),
609 NVL(x_days_late_receipt_allowed, 0),
610 NVL(x_receipt_days_exception_code, 'NONE')
611 INTO x_enforce_ship_to_loc_code,
612 x_allow_substitute_receipts,
613 x_routing_id,
614 x_qty_rcv_tolerance,
615 x_qty_rcv_exception_code,
616 x_days_early_receipt_allowed,
617 x_days_late_receipt_allowed,
618 x_receipt_days_exception_code
619 FROM DUAL;
620 END IF;
621
622 -- Drop Shipments
623 --
624 IF (p_drop_ship_flag = 'Y') THEN
625 x_routing_id := 3;
626 END IF;
627
628 /* R12 Complex Work. Bug 4484236.
629 * For Complex work POs receipt_routing is always
630 * direct delivery. In addition,overreceipt tolerance is 0
631 * for the payment_type MILESTONE.
632 */
633
634 If (p_payment_type is not null) then
635 x_routing_id := 3;
636
637 If (p_payment_type = 'MILESTONE') then
638 x_qty_rcv_tolerance := 0;
639 end if;
640 END IF;
641
642
643 -- Derive Routing Name ====================================================
644
645 IF (x_routing_id IS NOT NULL) THEN
646 l_progress := '120';
647 po_debug.debug_stmt(l_log_head,
648 l_progress,
649 'Looking up routing name...'
650 );
651 l_progress := '130';
652 po_debug.debug_var(l_log_head,
653 l_progress,
654 'x_routing_id',
655 x_routing_id
656 );
657
658 BEGIN
659 SELECT routing_name
660 INTO x_routing_name
661 FROM rcv_routing_headers
662 WHERE routing_header_id = x_routing_id;
663 EXCEPTION
664 WHEN NO_DATA_FOUND THEN
665 x_routing_name := NULL;
666 END;
667 END IF;
668
669 --=========================================================================
670
671 l_progress := '140';
672 po_debug.debug_end(l_log_head);
673 EXCEPTION
674 WHEN OTHERS THEN
675 po_debug.debug_exc(l_log_head, l_progress);
676 RAISE;
677 END get_receiving_controls;
678
679 /*===========================================================================
680
681 FUNCTION NAME: val_unique_receipt_num
682
683 ===========================================================================*/
684 FUNCTION val_unique_receipt_num(
685 x_receipt_num IN VARCHAR2
686 )
687 RETURN BOOLEAN IS
688 /*
689 ** Function checks if the receipt number passed in already exists in
690 ** rcv_shipment_headers. If is does, then it returns a value of FALSE. If
691 ** it doesn't, then it checks if it exists in po_history_receipts. If it
692 ** does then it returns a value of FALSE. If the receipt number doesn't
693 ** exist in either table, it returns a value of TRUE.
694 */
695 x_progress VARCHAR2(3) := NULL;
696 dup_count NUMBER := 0;
697 BEGIN
698 x_progress := '010';
699
700 SELECT COUNT(1)
701 INTO dup_count
702 FROM rcv_shipment_headers
703 WHERE receipt_num = x_receipt_num;
704
705 x_progress := '020';
706
707 IF dup_count <> 0 THEN
708 RETURN(FALSE);
709 ELSE
710 SELECT COUNT(1)
711 INTO dup_count
712 FROM po_history_receipts
713 WHERE receipt_num = x_receipt_num;
714
715 IF dup_count <> 0 THEN
716 RETURN(FALSE);
717 END IF;
718 END IF;
719
720 RETURN(TRUE);
721 EXCEPTION
722 WHEN OTHERS THEN
723 po_message_s.sql_error('val_unique_receipt_num',
724 x_progress,
725 SQLCODE
726 );
727 RAISE;
728 END val_unique_receipt_num;
729
730 /*===========================================================================
731
732 FUNCTION NAME: val_unique_shipment_num
733
734 ===========================================================================*/
735 FUNCTION val_unique_shipment_num(
736 x_shipment_num IN VARCHAR2,
737 x_vendor_id IN NUMBER
738 )
739 RETURN BOOLEAN IS
740 /*
741 ** Function checks if the shipment number passed in already exists in
742 ** rcv_shipment_headers for the current vendor. It returns a value of TRUE
743 ** if the shipment number passed in is unique, FALSE if it is a duplicate
744 */
745 x_progress VARCHAR2(3) := NULL;
746 dup_count NUMBER := 0;
747 BEGIN
748 x_progress := '010';
749
750 SELECT COUNT(1)
751 INTO dup_count
752 FROM rcv_shipment_headers
753 WHERE shipment_num = x_shipment_num
754 AND receipt_source_code = 'VENDOR'
755 AND vendor_id = x_vendor_id;
756
757 IF dup_count <> 0 THEN
758 RETURN(FALSE);
759 ELSE
760 RETURN(TRUE);
761 END IF;
762 EXCEPTION
763 WHEN OTHERS THEN
764 po_message_s.sql_error('val_unique_shipment_num',
765 x_progress,
766 SQLCODE
767 );
768 RAISE;
769 END val_unique_shipment_num;
770
771 /*===========================================================================
772
773 PROCEDURE NAME: get_ussgl_info
774
775 ===========================================================================*/
776 PROCEDURE get_ussgl_info(
777 x_line_location_id IN NUMBER,
778 x_ussgl_trx_code OUT NOCOPY VARCHAR2,
779 x_govt_context OUT NOCOPY VARCHAR2
780 ) IS
781 /*
782 ** Procedure gets ussgl_transaction_code and government_context from
783 ** po_line_locations.
784 */
785 x_progress VARCHAR2(3) := NULL;
786 BEGIN
787 x_progress := '010';
788
789 SELECT ussgl_transaction_code,
790 government_context
791 INTO x_ussgl_trx_code,
792 x_govt_context
793 FROM po_line_locations
794 WHERE line_location_id = x_line_location_id;
795 EXCEPTION
796 WHEN OTHERS THEN
797 po_message_s.sql_error('get_ussgl_info',
798 x_progress,
799 SQLCODE
800 );
801 RAISE;
802 END get_ussgl_info;
803
804 /*===========================================================================
805
806 PROCEDURE NAME: val_po_shipment
807
808 ===========================================================================*/
809 PROCEDURE val_po_shipment(
810 x_trx_type IN VARCHAR2,
811 x_parent_id IN NUMBER,
812 x_receipt_source_code IN VARCHAR2,
813 x_parent_trx_type IN VARCHAR2,
814 x_grand_parent_id IN NUMBER,
815 x_correction_type IN VARCHAR2,
816 x_available_quantity IN OUT NOCOPY NUMBER,
817 x_tolerable_qty IN OUT NOCOPY NUMBER,
818 x_uom IN OUT NOCOPY VARCHAR2
819 ) IS
820 x_progress VARCHAR2(3) := NULL;
821 /*Bug 1548597 */
822 x_secondary_available_qty NUMBER := 0;
823 BEGIN
824 x_progress := '010';
825 /*Bug 1548597 */
826 rcv_quantities_s.get_available_quantity(x_trx_type,
827 x_parent_id,
828 x_receipt_source_code,
829 x_parent_trx_type,
830 x_grand_parent_id,
831 x_correction_type,
832 x_available_quantity,
833 x_tolerable_qty,
834 x_uom,
835 x_secondary_available_qty
836 );
837 EXCEPTION
838 WHEN OTHERS THEN
839 po_message_s.sql_error('val_po_shipment',
840 x_progress,
841 SQLCODE
842 );
843 RAISE;
844 END val_po_shipment;
845
846 /*===========================================================================
847
848 PROCEDURE NAME: val_exp_cas_func
849
850 ===========================================================================*/
851 PROCEDURE val_exp_cas_func IS
852 x_progress VARCHAR2(3) := NULL;
853 BEGIN
854 x_progress := '010';
855 EXCEPTION
856 WHEN OTHERS THEN
857 po_message_s.sql_error('val_exp_cas_func',
858 x_progress,
859 SQLCODE
860 );
861 RAISE;
862 END val_exp_cas_func;
863
864 /* ==========================================================================
865
866 PROCEDURE NAME: PO_DIST_INFO
867
868 ===========================================================================*/
869 PROCEDURE po_dist_info(
870 x_po_dist_id IN NUMBER,
871 x_wip_entity_id OUT NOCOPY NUMBER,
872 x_wip_repetitive_schedule_id OUT NOCOPY NUMBER,
873 x_wip_operation_seq_num OUT NOCOPY NUMBER,
874 x_wip_resource_seq_num OUT NOCOPY NUMBER,
875 x_wip_line_id OUT NOCOPY NUMBER,
876 x_bom_resource_id OUT NOCOPY NUMBER
877 ) IS
878 x_progress VARCHAR2(3) := NULL;
879 BEGIN
880 x_progress := 10;
881
882 SELECT wip_entity_id,
883 wip_operation_seq_num,
884 wip_resource_seq_num,
885 wip_repetitive_schedule_id,
886 wip_line_id,
887 bom_resource_id
888 INTO x_wip_entity_id,
889 x_wip_operation_seq_num,
890 x_wip_resource_seq_num,
891 x_wip_repetitive_schedule_id,
892 x_wip_line_id,
893 x_bom_resource_id
894 FROM po_distributions pod
895 WHERE pod.po_distribution_id = x_po_dist_id;
896 EXCEPTION
897 WHEN OTHERS THEN
898 po_message_s.sql_error('PO_DIST_INFO',
899 x_progress,
900 SQLCODE
901 );
902 RAISE;
903 END po_dist_info;
904
905 /* ==========================================================================
906
907 PROCEDURE NAME: get_outside_processing_info
908
909 ===========================================================================*/
910 PROCEDURE get_outside_processing_info(
911 x_po_distribution_id IN NUMBER,
912 x_organization_id IN NUMBER,
913 x_job_schedule OUT NOCOPY VARCHAR2,
914 x_operation_seq_num OUT NOCOPY VARCHAR2,
915 x_department OUT NOCOPY VARCHAR2,
916 x_wip_line OUT NOCOPY VARCHAR2,
917 x_bom_resource_id OUT NOCOPY NUMBER,
918 x_po_operation_seq_num OUT NOCOPY NUMBER,
919 x_po_resource_seq_num OUT NOCOPY NUMBER
920 ) IS
921 x_wip_entity_id NUMBER(10);
922 x_wip_repetitive_schedule_id NUMBER(10);
923 x_wip_operation_seq_num NUMBER(10);
924 x_wip_resource_seq_num NUMBER(10);
925 x_wip_line_id NUMBER(10);
926 x_progress VARCHAR2(3);
927 BEGIN
928 /*
929 ** The po_operation and resource sequence numbers are off the po
930 ** distribution and is used for inserting the transaction rather
931 ** than the operation_seq_num which is derived from the wip tables
932 ** and shows the next operation rather than the current one. This
933 ** value is used for display purposes
934 */
935 x_progress := 10;
936 po_dist_info(x_po_distribution_id,
937 x_wip_entity_id,
938 x_wip_repetitive_schedule_id,
939 x_wip_operation_seq_num,
940 x_wip_resource_seq_num,
941 x_wip_line_id,
942 x_bom_resource_id
943 );
944 x_po_operation_seq_num := x_wip_operation_seq_num;
945 x_po_resource_seq_num := x_wip_resource_seq_num;
946 x_progress := 20;
947 rcv_core_s.out_op_info(x_wip_entity_id,
948 x_organization_id,
949 x_wip_repetitive_schedule_id,
950 x_wip_operation_seq_num,
951 x_wip_resource_seq_num,
952 x_job_schedule,
953 x_operation_seq_num,
954 x_department
955 );
956 x_progress := 30;
957
958 IF NVL(x_wip_line_id, 0) <> 0 THEN
959 rcv_core_s.wip_line_info(x_wip_line_id,
960 x_organization_id,
961 x_wip_line
962 );
963 END IF;
964 EXCEPTION
965 WHEN OTHERS THEN
966 po_message_s.sql_error('OUT_INFO',
967 x_progress,
968 SQLCODE
969 );
970 RAISE;
971 END get_outside_processing_info;
972
973 /* ==========================================================================
974
975 PROCEDURE NAME: OUT_OP_INFO
976
977 ===========================================================================*/
978 PROCEDURE out_op_info(
979 x_wip_entity_id IN NUMBER,
980 x_organization_id IN NUMBER,
981 x_wip_repetitive_schedule_id IN NUMBER,
982 x_wip_operation_seq_num IN NUMBER,
983 x_wip_resource_seq_num IN NUMBER,
984 x_job_schedule_dsp OUT NOCOPY VARCHAR2,
985 x_op_seq_num_dsp OUT NOCOPY VARCHAR2,
986 x_department_code OUT NOCOPY VARCHAR2
987 ) IS
988 x_progress VARCHAR2(3) := NULL;
989 BEGIN
990 x_progress := 10;
991
992 --Bug# 2000013 togeorge 09/18/2001
993 --Eam: Split the following sql to 3 different sqls because eAM w/o would
994 -- not have resource information and this sql will fail.
995 /*
996 select we.wip_entity_name,
997 wn.operation_seq_num,
998 bd.department_code
999 into x_job_schedule_dsp,
1000 x_op_seq_num_dsp,
1001 x_department_code
1002 from wip_entities we,
1003 bom_departments bd,
1004 wip_operation_resources wr,
1005 wip_operations wn,
1006 wip_operations wo
1007 where wo.wip_entity_id = x_wip_entity_id
1008 and wo.organization_id = x_organization_id
1009 and nvl(wo.repetitive_schedule_id, -1) =
1010 nvl(x_wip_repetitive_schedule_id, -1)
1011 and wo.operation_seq_num = x_wip_operation_seq_num
1012 and wr.wip_entity_id = x_wip_entity_id
1013 and wr.organization_id = x_organization_id
1014 and nvl(wr.repetitive_schedule_id, -1) =
1015 nvl(x_wip_repetitive_schedule_id, -1)
1016 and wr.operation_seq_num = x_wip_operation_seq_num
1017 and wr.resource_seq_num = x_wip_resource_seq_num
1018 and wn.wip_entity_id = x_wip_entity_id
1019 and wn.organization_id = x_organization_id
1020 and nvl(wn.repetitive_schedule_id, -1) =
1021 nvl(x_wip_repetitive_schedule_id, -1)
1022 and wn.operation_seq_num = decode(wr.autocharge_type,
1023 4, nvl(wo.next_operation_seq_num,
1024 wo.operation_seq_num),wo.operation_seq_num)
1025 and bd.department_id = wn.department_id
1026 and we.wip_entity_id = x_wip_entity_id
1027 and we.organization_id = x_organization_id ;
1028 */
1029 IF x_wip_entity_id IS NOT NULL THEN
1030 BEGIN
1031 SELECT we.wip_entity_name job
1032 INTO x_job_schedule_dsp
1033 FROM wip_entities we
1034 WHERE we.wip_entity_id = x_wip_entity_id
1035 AND we.organization_id = x_organization_id;
1036 EXCEPTION
1037 WHEN OTHERS THEN
1038 x_job_schedule_dsp := NULL;
1039 END;
1040 END IF;
1041
1042 IF x_wip_entity_id IS NOT NULL
1043 AND x_wip_operation_seq_num IS NOT NULL THEN
1044 BEGIN
1045 SELECT wn.operation_seq_num SEQUENCE,
1046 bd.department_code department
1047 INTO x_op_seq_num_dsp,
1048 x_department_code
1049 FROM bom_departments bd,
1050 wip_operation_resources wr,
1051 wip_operations wn,
1052 wip_operations wo
1053 WHERE wo.wip_entity_id = x_wip_entity_id
1054 AND wo.organization_id = x_organization_id
1055 AND NVL(wo.repetitive_schedule_id, -1) = NVL(x_wip_repetitive_schedule_id, -1)
1056 AND wo.operation_seq_num = x_wip_operation_seq_num
1057 AND wr.wip_entity_id = x_wip_entity_id
1058 AND wr.organization_id = x_organization_id
1059 AND NVL(wr.repetitive_schedule_id, -1) = NVL(x_wip_repetitive_schedule_id, -1)
1060 AND wr.operation_seq_num = x_wip_operation_seq_num
1061 AND wr.resource_seq_num = x_wip_resource_seq_num
1062 AND wn.wip_entity_id = x_wip_entity_id
1063 AND wn.organization_id = x_organization_id
1064 AND NVL(wn.repetitive_schedule_id, -1) = NVL(x_wip_repetitive_schedule_id, -1)
1065 AND wn.operation_seq_num = DECODE(wr.autocharge_type,
1066 4, NVL(wo.next_operation_seq_num, wo.operation_seq_num),
1067 wo.operation_seq_num
1068 )
1069 AND bd.department_id = wn.department_id;
1070 EXCEPTION
1071 WHEN NO_DATA_FOUND THEN
1072 --for EAM workorders the above sql would raise no_data_found.
1073 --find department code and sequence with out touching resource table.
1074 BEGIN
1075 SELECT bd.department_code department
1076 INTO x_department_code
1077 FROM bom_departments bd,
1078 wip_operations wn
1079 WHERE wn.wip_entity_id = x_wip_entity_id
1080 AND wn.organization_id = x_organization_id
1081 AND NVL(wn.repetitive_schedule_id, -1) = NVL(x_wip_repetitive_schedule_id, -1)
1082 AND bd.department_id = wn.department_id;
1083 EXCEPTION
1084 WHEN OTHERS THEN
1085 x_department_code := NULL;
1086 END;
1087
1088 BEGIN
1089 SELECT wo.operation_seq_num SEQUENCE
1090 INTO x_op_seq_num_dsp
1091 FROM wip_operations wo
1092 WHERE wo.wip_entity_id = x_wip_entity_id
1093 AND wo.organization_id = x_organization_id
1094 AND NVL(wo.repetitive_schedule_id, -1) = NVL(x_wip_repetitive_schedule_id, -1)
1095 AND wo.operation_seq_num = x_wip_operation_seq_num;
1096 EXCEPTION
1097 WHEN OTHERS THEN
1098 x_op_seq_num_dsp := NULL;
1099 END;
1100 WHEN OTHERS THEN
1101 x_op_seq_num_dsp := NULL;
1102 x_department_code := NULL;
1103 END;
1104 END IF;
1105 EXCEPTION
1106 WHEN OTHERS THEN
1107 po_message_s.sql_error('OUT_OP_INFO',
1108 x_progress,
1109 SQLCODE
1110 );
1111 RAISE;
1112 END out_op_info;
1113
1114 /* ==========================================================================
1115
1116 PROCEDURE NAME: WIP_LINE_INFO
1117
1118 ===========================================================================*/
1119 PROCEDURE wip_line_info(
1120 x_wip_line_id IN NUMBER,
1121 x_org_id IN NUMBER,
1122 x_wip_line_dsp OUT NOCOPY VARCHAR2
1123 ) IS
1124 x_progress VARCHAR2(3) := NULL;
1125 BEGIN
1126 x_progress := 10;
1127
1128 SELECT wl.line_code
1129 INTO x_wip_line_dsp
1130 FROM wip_lines wl
1131 WHERE wl.organization_id = x_org_id
1132 AND wl.line_id = x_wip_line_id;
1133 EXCEPTION
1134 WHEN OTHERS THEN
1135 po_message_s.sql_error('WIP_LINE_INFO',
1136 x_progress,
1137 SQLCODE
1138 );
1139 RAISE;
1140 END wip_line_info;
1141
1142 /* ==========================================================================
1143
1144 FUNCTION NAME: note_info
1145
1146 ===========================================================================*/
1147 FUNCTION note_info(
1148 x_note_attribute VARCHAR2,
1149 x_note_table_name VARCHAR2,
1150 x_note_column_name VARCHAR2,
1151 x_foreign_id NUMBER
1152 )
1153 RETURN NUMBER IS
1154 x_progress VARCHAR2(3) := NULL;
1155 x_note_count NUMBER;
1156 BEGIN
1157 x_progress := 10;
1158
1159 /* bug 13552245 comment below sql, as table po_notes has been dropped.
1160 SELECT COUNT(pon.po_note_id)
1161 INTO x_note_count
1162 FROM po_note_references ponr,
1163 po_notes pon,
1164 po_usage_attributes poua
1165 WHERE ponr.po_note_id = pon.po_note_id
1166 AND pon.usage_id = poua.usage_id
1167 AND poua.note_attribute = x_note_attribute
1168 AND ponr.table_name = x_note_table_name
1169 AND ponr.column_name = x_note_column_name
1170 AND ponr.foreign_id = x_foreign_id;
1171 */
1172
1173 RETURN(0);/* bug 13552245, Return 0 from now on*/
1174 EXCEPTION
1175 WHEN NO_DATA_FOUND THEN
1176 NULL;
1177 RETURN(0);
1178 WHEN OTHERS THEN
1179 RETURN(0);/*bug 13552245, Return 0 from now on*/
1180 END note_info;
1181
1182 /* ==========================================================================
1183
1184 FUNCTION NAME: get_note_count
1185
1186 ===========================================================================*/
1187 FUNCTION get_note_count(
1188 x_header_id IN NUMBER,
1189 x_line_id IN NUMBER,
1190 x_location_id IN NUMBER,
1191 x_po_line_id IN NUMBER,
1192 x_po_release_id IN NUMBER,
1193 x_po_header_id IN NUMBER,
1194 x_item_id IN NUMBER
1195 )
1196 RETURN NUMBER IS
1197 x_progress VARCHAR2(3) := NULL;
1198 x_ret_note_cnt NUMBER;
1199 BEGIN
1200 x_progress := 10;
1201 x_ret_note_cnt := note_info('RVCRC',
1202 'RCV_SHIPMENT_HEADERS',
1203 'SHIPMENT_HEADER_ID',
1204 x_header_id
1205 );
1206
1207 IF x_ret_note_cnt > 0 THEN
1208 RETURN(x_ret_note_cnt);
1209 END IF;
1210
1211 x_progress := 20;
1212 x_ret_note_cnt := note_info('RVCRC',
1213 'RCV_SHIPMENT_LINES',
1214 'SHIPMENT_LINE_ID',
1215 x_line_id
1216 );
1217
1218 IF x_ret_note_cnt > 0 THEN
1219 RETURN(x_ret_note_cnt);
1220 END IF;
1221
1222 x_progress := 30;
1223 x_ret_note_cnt := note_info('RVCRC',
1224 'PO_LINE_LOCATIONS',
1225 'LINE_LOCATION_ID',
1226 x_location_id
1227 );
1228
1229 IF x_ret_note_cnt > 0 THEN
1230 RETURN(x_ret_note_cnt);
1231 END IF;
1232
1233 x_progress := 40;
1234 x_ret_note_cnt := note_info('RVCRC',
1235 'PO_LINES',
1236 'PO_LINE_ID',
1237 x_po_line_id
1238 );
1239
1240 IF x_ret_note_cnt > 0 THEN
1241 RETURN(x_ret_note_cnt);
1242 END IF;
1243
1244 x_progress := 50;
1245 x_ret_note_cnt := note_info('RVCRC',
1246 'PO_RELEASES',
1247 'PO_RELEASE_ID',
1248 x_po_release_id
1249 );
1250
1251 IF x_ret_note_cnt > 0 THEN
1252 RETURN(x_ret_note_cnt);
1253 END IF;
1254
1255 x_progress := 60;
1256 x_ret_note_cnt := note_info('RVCRC',
1257 'PO_HEADERS',
1258 'PO_HEADER_ID',
1259 x_po_header_id
1260 );
1261
1262 IF x_ret_note_cnt > 0 THEN
1263 RETURN(x_ret_note_cnt);
1264 END IF;
1265
1266 x_progress := 70;
1267 x_ret_note_cnt := note_info('ITMIT',
1268 'MTL_SYSTEM_ITEMS',
1269 'INVENTORY_ITEM_ID',
1270 x_item_id
1271 );
1272 RETURN(x_ret_note_cnt);
1273 EXCEPTION
1274 WHEN NO_DATA_FOUND THEN
1275 NULL;
1276 RETURN(0);
1277 WHEN OTHERS THEN
1278 po_message_s.sql_error('NOTE',
1279 x_progress,
1280 SQLCODE
1281 );
1282 RAISE;
1283 END get_note_count;
1284
1285 /* ==========================================================================
1286
1287 PROCEDURE NAME: DERIVE_SHIPMENT_INFO
1288
1289 ===========================================================================*/
1290 PROCEDURE derive_shipment_info(
1291 x_header_record IN OUT NOCOPY rcv_shipment_header_sv.headerrectype
1292 ) IS
1293 BEGIN
1294 IF x_header_record.header_record.receipt_header_id IS NOT NULL THEN
1295 IF (g_asn_debug = 'Y') THEN
1296 asn_debug.put_line('Need to put a cursor to retrieve other values');
1297 asn_debug.put_line('Shipment header Id has been provided');
1298 END IF;
1299
1300 RETURN;
1301 END IF;
1302
1303 -- Check that the shipment_num is not null
1304
1305 IF ( x_header_record.header_record.shipment_num IS NULL
1306 OR x_header_record.header_record.shipment_num = '0'
1307 OR REPLACE(x_header_record.header_record.shipment_num,
1308 ' ',
1309 ''
1310 ) IS NULL) THEN
1311 IF (g_asn_debug = 'Y') THEN
1312 asn_debug.put_line('Cannot derive the shipment_header_id at this point');
1313 END IF;
1314
1315 RETURN;
1316 END IF;
1317
1318 -- Derive the shipment_header_id only for transaction_type = CANCEL
1319
1320 /*
1321 * BUGNO: 1708017
1322 * The where clause used to have organization_id =
1323 * X_header_record.header_record.ship_to_organization_id
1324 * This used to be populated with ship_to_organization_id.
1325 * Now this is populated as null since it is supposed to
1326 * be from organization_id. So changed it to ship_to_org_id.
1327 */
1328 IF x_header_record.header_record.transaction_type = 'CANCEL'
1329 AND x_header_record.header_record.receipt_header_id IS NULL THEN
1330 BEGIN
1331 SELECT MAX(shipment_header_id) -- if we ever have 2 shipments with the same combo
1332 INTO x_header_record.header_record.receipt_header_id
1333 FROM rcv_shipment_headers
1334 WHERE NVL(vendor_site_id, -9999) = NVL(x_header_record.header_record.vendor_site_id, -9999)
1335 AND vendor_id = x_header_record.header_record.vendor_id
1336 AND ship_to_org_id = x_header_record.header_record.ship_to_organization_id
1337 AND shipment_num = x_header_record.header_record.shipment_num
1338 AND shipped_date >= ADD_MONTHS(x_header_record.header_record.shipped_date, -12);
1339 EXCEPTION
1340 WHEN OTHERS THEN
1341 IF (g_asn_debug = 'Y') THEN
1342 asn_debug.put_line(SQLERRM);
1343 END IF;
1344 END;
1345
1346 RETURN;
1347 END IF;
1348 EXCEPTION
1349 WHEN OTHERS THEN
1350 rcv_error_pkg.set_sql_error_message('derive_shipment_info', '000');
1351 x_header_record.error_record.error_status := rcv_error_pkg.g_ret_sts_unexp_error;
1352 x_header_record.error_record.error_message := rcv_error_pkg.get_last_message;
1353 END derive_shipment_info;
1354
1355 /* ==========================================================================
1356
1357 PROCEDURE NAME: DEFAULT_SHIPMENT_INFO
1358
1359 ===========================================================================*/
1360 PROCEDURE default_shipment_info(
1361 x_header_record IN OUT NOCOPY rcv_shipment_header_sv.headerrectype
1362 ) IS
1363 x_count NUMBER;
1364 BEGIN
1365 -- no need to derive shipment_header_id if it is already provided
1366
1367 IF x_header_record.header_record.receipt_header_id IS NOT NULL THEN
1368 IF (g_asn_debug = 'Y') THEN
1369 asn_debug.put_line('Shipment header Id has been provided');
1370 END IF;
1371
1372 RETURN;
1373 END IF;
1374
1375 -- Check for shipment number which is null, blank , zero
1376
1377 IF x_header_record.header_record.asn_type IN('ASN', 'ASBN')
1378 AND ( x_header_record.header_record.shipment_num IS NULL
1379 OR x_header_record.header_record.shipment_num = '0'
1380 OR REPLACE(x_header_record.header_record.shipment_num,
1381 ' ',
1382 ''
1383 ) IS NULL) THEN
1384 IF (g_asn_debug = 'Y') THEN
1385 asn_debug.put_line('Shipment num is still null');
1386 END IF;
1387
1388 RETURN;
1389 END IF;
1390
1391 -- Derive the shipment_header_id based on the shipment_num for transaction_type = CANCEL
1392
1393 /*
1394 * BUGNO: 1708017
1395 * The where clause used to have organization_id =
1396 * X_header_record.header_record.ship_to_organization_id
1397 * This used to be populated with ship_to_organization_id.
1398 * Now this is populated as null since it is supposed to
1399 * be from organization_id. So changed it to ship_to_org_id.
1400 */
1401 IF x_header_record.header_record.transaction_type = 'CANCEL'
1402 AND x_header_record.header_record.receipt_header_id IS NULL THEN
1403 BEGIN
1404 SELECT MAX(shipment_header_id) -- if we ever have 2 shipments with the same combo
1405 INTO x_header_record.header_record.receipt_header_id
1406 FROM rcv_shipment_headers
1407 WHERE NVL(vendor_site_id, -9999) = NVL(x_header_record.header_record.vendor_site_id, -9999)
1408 AND vendor_id = x_header_record.header_record.vendor_id
1409 AND ship_to_org_id = x_header_record.header_record.ship_to_organization_id
1410 AND shipment_num = x_header_record.header_record.shipment_num
1411 AND shipped_date >= ADD_MONTHS(x_header_record.header_record.shipped_date, -12);
1412 EXCEPTION
1413 WHEN OTHERS THEN
1414 IF (g_asn_debug = 'Y') THEN
1415 asn_debug.put_line(SQLERRM);
1416 END IF;
1417 END;
1418
1419 RETURN;
1420 END IF;
1421 EXCEPTION
1422 WHEN OTHERS THEN
1423 rcv_error_pkg.set_sql_error_message('default_shipment_info', '000');
1424 x_header_record.error_record.error_status := rcv_error_pkg.g_ret_sts_unexp_error;
1425 x_header_record.error_record.error_message := rcv_error_pkg.get_last_message;
1426 END default_shipment_info;
1427
1428 /* ==========================================================================
1429
1430 PROCEDURE NAME: VALIDATE_SHIPMENT_NUMBER
1431
1432 ===========================================================================*/
1433 PROCEDURE validate_shipment_number(
1434 x_header_record IN OUT NOCOPY rcv_shipment_header_sv.headerrectype
1435 ) IS
1436 x_count NUMBER;
1437 x_shipment_header_id NUMBER; -- added for cancel process
1438 x_sysdate DATE := SYSDATE;
1439 BEGIN
1440 -- Check for shipment number which is null, blank , zero
1441 IF (g_asn_debug = 'Y') THEN
1442 asn_debug.put_line('Check for shipment number which is null, blank , zero ');
1443 END IF;
1444
1445 /*dbms_output.put_line(nvl(X_header_record.header_record.shipment_num,'@@@'));*/
1446 IF x_header_record.header_record.asn_type IN('ASN', 'ASBN')
1447 AND ( x_header_record.header_record.shipment_num IS NULL
1448 OR x_header_record.header_record.shipment_num = '0'
1449 OR REPLACE(x_header_record.header_record.shipment_num,
1450 ' ',
1451 ''
1452 ) IS NULL) THEN
1453 /*dbms_output.put_line(X_header_record.header_record.asn_type);
1454 dbms_output.put_line(X_header_record.header_record.shipment_num);*/
1455 rcv_error_pkg.set_error_message('RCV_NO_SHIPMENT_NUM');
1456 RAISE e_validation_error;
1457 END IF;
1458
1459 -- Check for Receipts before ASN
1460
1461 IF (g_asn_debug = 'Y') THEN
1462 asn_debug.put_line('Check for Receipts before ASN ');
1463 END IF;
1464
1465 /*
1466 * BUGNO: 1708017
1467 * The where clause used to have organization_id =
1468 * X_header_record.header_record.ship_to_organization_id
1469 * This used to be populated with ship_to_organization_id.
1470 * Now this is populated as null since it is supposed to
1471 * be from organization_id. So changed it to ship_to_org_id.
1472 */
1473 /* Bug 2485699- commented the condn trunc(Shipped_date) = trunc(header.record.shipped_date).
1474 Added the shipped date is null since we are not populating the same in rcv_shipment_headers
1475 while receiving thru forms.*/
1476 IF x_header_record.header_record.asn_type IN('ASN', 'ASBN')
1477 AND x_header_record.header_record.transaction_type <> 'CANCEL' THEN -- added this for CANCEL
1478 SELECT COUNT(*)
1479 INTO x_count
1480 FROM rcv_shipment_headers
1481 WHERE NVL(vendor_site_id, -9999) = NVL(x_header_record.header_record.vendor_site_id, -9999)
1482 AND vendor_id = x_header_record.header_record.vendor_id
1483 AND --trunc(shipped_date) = trunc(X_header_record.header_record.shipped_date) and
1484 ( shipped_date IS NULL
1485 OR shipped_date >= ADD_MONTHS(x_sysdate, -12))
1486 AND shipment_num = x_header_record.header_record.shipment_num
1487 AND ship_to_org_id = x_header_record.header_record.ship_to_organization_id
1488 AND receipt_num IS NOT NULL;
1489
1490 IF x_count > 0 THEN
1491 rcv_error_pkg.set_error_message('RCV_RCV_BEFORE_ASN');
1492 rcv_error_pkg.set_token('SHIPMENT', x_header_record.header_record.shipment_num);
1493 rcv_error_pkg.set_token('ITEM', ' ');
1494 RAISE e_validation_error;
1495 END IF;
1496 END IF;
1497
1498 -- Change transaction_type to NEW if transaction_type is REPLACE and
1499 -- we cannot locate the shipment notice for the vendor site with the
1500 -- same shipped date
1501 /*
1502 * BUGNO: 1708017
1503 * The where clause used to have organization_id =
1504 * X_header_record.header_record.ship_to_organization_id
1505 * This used to be populated with ship_to_organization_id.
1506 * Now this is populated as null since it is supposed to
1507 * be from organization_id. So changed it to ship_to_org_id.
1508 */
1509 IF x_header_record.header_record.transaction_type = 'REPLACE' THEN
1510 SELECT COUNT(*)
1511 INTO x_count
1512 FROM rcv_shipment_headers
1513 WHERE NVL(vendor_site_id, -9999) = NVL(x_header_record.header_record.vendor_site_id, -9999)
1514 AND vendor_id = x_header_record.header_record.vendor_id
1515 AND TRUNC(shipped_date) = TRUNC(x_header_record.header_record.shipped_date)
1516 AND shipped_date >= ADD_MONTHS(x_sysdate, -12)
1517 AND ship_to_org_id = x_header_record.header_record.ship_to_organization_id
1518 AND shipment_num = x_header_record.header_record.shipment_num;
1519
1520 IF x_count = 0 THEN
1521 x_header_record.header_record.transaction_type := 'NEW';
1522 END IF;
1523 END IF;
1524
1525 -- Check for any shipment_num which exist for the same vendor site and within a year
1526 -- of the previous shipment with the same num. This is only done for transaction_type = NEW
1527
1528 /*
1529 * BUGNO: 1708017
1530 * The where clause used to have organization_id =
1531 * X_header_record.header_record.ship_to_organization_id
1532 * This used to be populated with ship_to_organization_id.
1533 * Now this is populated as null since it is supposed to
1534 * be from organization_id. So changed it to ship_to_org_id.
1535 */
1536
1537 /* Fix for bug 2682881.
1538 * No validation on shipment_num was happening if a new ASN
1539 * is created with the same supplier,supplier site, shipment
1540 * num, but with different shipped_date. Shipment_num should
1541 * be unique from the supplier,supplier site for a period of
1542 * one year. Hence commented the condition "trunc(shipped_date)
1543 * = trunc(X_header_record.header_record.shipped_date) and"
1544 * from the following sql which is not required.
1545 */
1546 IF x_header_record.header_record.transaction_type = 'NEW'
1547 AND x_header_record.header_record.asn_type IN('ASN', 'ASBN') THEN
1548 SELECT COUNT(*)
1549 INTO x_count
1550 FROM rcv_shipment_headers
1551 WHERE NVL(vendor_site_id, -9999) = NVL(x_header_record.header_record.vendor_site_id, -9999)
1552 AND vendor_id = x_header_record.header_record.vendor_id
1553 AND ship_to_org_id = x_header_record.header_record.ship_to_organization_id
1554 AND shipment_num = x_header_record.header_record.shipment_num
1555 AND --trunc(shipped_date) = trunc(X_header_record.header_record.shipped_date) and
1556 shipped_date >= ADD_MONTHS(x_sysdate, -12);
1557
1558 IF x_count > 0 THEN
1559 /* Bug# 1413880
1560 As per the manual Shipment number should be unique for one year period for
1561 given supplier.Changing Warning to Error. */
1562 rcv_error_pkg.set_error_message('PO_PDOI_SHIPMENT_NUM_UNIQUE');
1563 rcv_error_pkg.set_token('VALUE', x_header_record.header_record.shipment_num);
1564 RAISE e_validation_error;
1565 END IF;
1566 END IF;
1567
1568 /*bug 2123721. bgopired
1569 We were not checking the uniqueness of shipment number incase of
1570 Standard Receipts. Used the same logic of Enter Receipt form to check
1571 the uniqueness */
1572 IF x_header_record.header_record.transaction_type = 'NEW'
1573 AND x_header_record.header_record.asn_type IN('STD') THEN
1574 IF NOT val_unique_shipment_num(x_header_record.header_record.shipment_num, x_header_record.header_record.vendor_id) THEN
1575 rcv_error_pkg.set_error_message('PO_PDOI_SHIPMENT_NUM_UNIQUE');
1576 rcv_error_pkg.set_token('VALUE', x_header_record.header_record.shipment_num);
1577 RAISE e_validation_error;
1578 END IF;
1579 END IF;
1580
1581 -- Check for matching ASN if ADD, CANCEL
1582 IF (g_asn_debug = 'Y') THEN
1583 asn_debug.put_line('Check for matching ASN if ADD, CANCEL');
1584 END IF;
1585
1586 /*
1587 * BUGNO: 1708017
1588 * The where clause used to have organization_id =
1589 * X_header_record.header_record.ship_to_organization_id
1590 * This used to be populated with ship_to_organization_id.
1591 * Now this is populated as null since it is supposed to
1592 * be from organization_id. So changed it to ship_to_org_id.
1593 */
1594 IF x_header_record.header_record.transaction_type IN('ADD', 'CANCEL')
1595 AND x_header_record.header_record.asn_type IN('ASN', 'ASBN') THEN
1596 SELECT COUNT(*)
1597 INTO x_count
1598 FROM rcv_shipment_headers
1599 WHERE NVL(vendor_site_id, -9999) = NVL(x_header_record.header_record.vendor_site_id, -9999)
1600 AND vendor_id = x_header_record.header_record.vendor_id
1601 AND ship_to_org_id = x_header_record.header_record.ship_to_organization_id
1602 AND shipment_num = x_header_record.header_record.shipment_num
1603 AND TRUNC(shipped_date) = TRUNC(x_header_record.header_record.shipped_date)
1604 AND shipped_date >= ADD_MONTHS(x_sysdate, -12);
1605
1606 IF x_count = 0 THEN
1607 rcv_error_pkg.set_error_message('RCV_NO_MATCHING_ASN');
1608 rcv_error_pkg.set_token('SHIPMENT', x_header_record.header_record.shipment_num);
1609 RAISE e_validation_error;
1610 END IF;
1611 END IF;
1612
1613 -- Check that there are no receipts against the ASN for ADD, CANCEL
1614 IF (g_asn_debug = 'Y') THEN
1615 asn_debug.put_line('Check that there are no receipts against the ASN for ADD, CANCEL');
1616 END IF;
1617
1618 IF x_header_record.header_record.transaction_type IN('ADD', 'CANCEL')
1619 AND x_header_record.header_record.asn_type IN('ASN', 'ASBN') THEN
1620 IF x_header_record.header_record.receipt_header_id IS NOT NULL THEN
1621 SELECT SUM(quantity_received)
1622 INTO x_count
1623 FROM rcv_shipment_lines
1624 WHERE rcv_shipment_lines.shipment_header_id = x_header_record.header_record.receipt_header_id;
1625 ELSE
1626 /*
1627 * BUGNO: 1708017
1628 * The where clause used to have organization_id =
1629 * X_header_record.header_record.ship_to_organization_id
1630 * This used to be populated with ship_to_organization_id.
1631 * Now this is populated as null since it is supposed to
1632 * be from organization_id. So changed it to ship_to_org_id.
1633 */
1634 SELECT SUM(quantity_received)
1635 INTO x_count
1636 FROM rcv_shipment_lines
1637 WHERE EXISTS(SELECT 'x'
1638 FROM rcv_shipment_headers
1639 WHERE rcv_shipment_headers.shipment_header_id = rcv_shipment_lines.shipment_header_id
1640 AND NVL(vendor_site_id, -9999) = NVL(x_header_record.header_record.vendor_site_id, -9999)
1641 AND vendor_id = x_header_record.header_record.vendor_id
1642 AND ship_to_org_id = x_header_record.header_record.ship_to_organization_id
1643 AND shipment_num = x_header_record.header_record.shipment_num
1644 AND TRUNC(shipped_date) = TRUNC(x_header_record.header_record.shipped_date)
1645 AND shipped_date >= ADD_MONTHS(x_sysdate, -12));
1646 END IF;
1647
1648 IF NVL(x_count, 0) > 0 THEN -- Some quantity has been received
1649 IF (g_asn_debug = 'Y') THEN
1650 asn_debug.put_line('There are receipts against the ASN ' || x_header_record.header_record.shipment_num);
1651 END IF;
1652
1653 rcv_error_pkg.set_error_message('RCV_ASN_QTY_RECEIVED');
1654 rcv_error_pkg.set_token('SHIPMENT', x_header_record.header_record.shipment_num);
1655 RAISE e_validation_error;
1656 END IF;
1657 END IF;
1658
1659 -- If we have reached this place that means the shipment exists
1660 -- Make sure we have a shipment header id
1661
1662 IF (g_asn_debug = 'Y') THEN
1663 asn_debug.put_line('Make sure we have a shipment_header_id');
1664 END IF;
1665
1666 /*
1667 * BUGNO: 1708017
1668 * The where clause used to have organization_id =
1669 * X_header_record.header_record.ship_to_organization_id
1670 * This used to be populated with ship_to_organization_id.
1671 * Now this is populated as null since it is supposed to
1672 * be from organization_id. So changed it to ship_to_org_id.
1673 */
1674 IF x_header_record.header_record.transaction_type IN('CANCEL')
1675 AND x_header_record.header_record.receipt_header_id IS NULL THEN
1676 SELECT MAX(shipment_header_id)
1677 INTO x_header_record.header_record.receipt_header_id
1678 FROM rcv_shipment_headers
1679 WHERE NVL(vendor_site_id, -9999) = NVL(x_header_record.header_record.vendor_site_id, -9999)
1680 AND vendor_id = x_header_record.header_record.vendor_id
1681 AND ship_to_org_id = x_header_record.header_record.ship_to_organization_id
1682 AND shipment_num = x_header_record.header_record.shipment_num
1683 AND TRUNC(shipped_date) = TRUNC(x_header_record.header_record.shipped_date)
1684 AND shipped_date >= ADD_MONTHS(x_sysdate, -12);
1685 END IF;
1686
1687 -- Verify that the shipment_header_id matches the derived/defaulted shipment_header_id
1688
1689 IF (g_asn_debug = 'Y') THEN
1690 asn_debug.put_line('Verify that the shipment_header_id matches the derived/defaulted shipment_header_id');
1691 END IF;
1692
1693 /*
1694 * BUGNO: 1708017
1695 * The where clause used to have organization_id =
1696 * X_header_record.header_record.ship_to_organization_id
1697 * This used to be populated with ship_to_organization_id.
1698 * Now this is populated as null since it is supposed to
1699 * be from organization_id. So changed it to ship_to_org_id.
1700 */
1701 IF x_header_record.header_record.transaction_type IN('CANCEL')
1702 AND x_header_record.header_record.receipt_header_id IS NOT NULL THEN
1703 SELECT MAX(shipment_header_id)
1704 INTO x_shipment_header_id
1705 FROM rcv_shipment_headers
1706 WHERE NVL(vendor_site_id, -9999) = NVL(x_header_record.header_record.vendor_site_id, -9999)
1707 AND vendor_id = x_header_record.header_record.vendor_id
1708 AND ship_to_org_id = x_header_record.header_record.ship_to_organization_id
1709 AND shipment_num = x_header_record.header_record.shipment_num
1710 AND TRUNC(shipped_date) = TRUNC(x_header_record.header_record.shipped_date)
1711 AND shipped_date >= ADD_MONTHS(x_sysdate, -12);
1712
1713 IF x_shipment_header_id <> x_header_record.header_record.receipt_header_id THEN
1714 IF (g_asn_debug = 'Y') THEN
1715 asn_debug.put_line('The shipment_header_id do not match ');
1716 END IF;
1717
1718 rcv_error_pkg.set_error_message('RCV_ASN_MISMATCH_SHIP_ID');
1719 rcv_error_pkg.set_token('SHIPMENT', x_header_record.header_record.shipment_num);
1720 RAISE e_validation_error;
1721 END IF;
1722 END IF;
1723 EXCEPTION
1724 WHEN e_validation_error THEN
1725 x_header_record.error_record.error_status := rcv_error_pkg.g_ret_sts_error;
1726 x_header_record.error_record.error_message := rcv_error_pkg.get_last_message;
1727 WHEN OTHERS THEN
1728 rcv_error_pkg.set_sql_error_message('validate_shipment_number', '000');
1729 x_header_record.error_record.error_status := rcv_error_pkg.g_ret_sts_unexp_error;
1730 x_header_record.error_record.error_message := rcv_error_pkg.get_last_message;
1731 END validate_shipment_number;
1732 END rcv_core_s;
[Home] [Help]