1 PACKAGE BODY PO_SHIPMENTS_SV8 as
2 /* $Header: POXPOS8B.pls 120.6 2011/08/10 20:06:13 ajunnikr ship $*/
3
4 /*===========================================================================
5
6 FUNCTION NAME: val_start_dates()
7
8 ===========================================================================*/
9
10 FUNCTION val_start_dates(X_start_date IN DATE,
11 X_po_header_id IN NUMBER) RETURN BOOLEAN IS
12
13 /* This procedure is used by RFQs and Quotations to verify the
14 ** START_DATE which has been entered on the header level is not
15 ** later than the earliest START_DATE of it's shipments.
16 */
17
18 x_progress VARCHAR2(3) := '';
19 x_start_date_count NUMBER;
20
21 BEGIN
22 x_progress := '010';
23 /*
24 ** Verify that the start date entered on the header is earlier than
25 ** the start dates of it's shipments.
26 */
27
28 SELECT count(*)
29 INTO x_start_date_count
30 FROM po_line_locations
31 WHERE start_date < X_start_date
32 AND po_header_id = X_po_header_id;
33
34 x_progress := '020';
35
36 if x_start_date_count > 0 then
37 RETURN(FALSE);
38 -- dbms_output.put_line('FALSE: start date invalid');
39 else
40 RETURN(TRUE);
41 -- dbms_output.put_line('TRUE: start date valid');
42 end if;
43
44 EXCEPTION
45 when no_data_found then
46 return(TRUE);
47 WHEN OTHERS THEN
48 po_message_s.sql_error('val_start_dates', x_progress, sqlcode);
49 raise;
50 END val_start_dates;
51 /*===========================================================================
52
53 FUNCTION NAME: val_end_dates()
54
55 ===========================================================================*/
56
57 FUNCTION val_end_dates(X_end_date IN DATE,
58 X_po_header_id IN NUMBER) RETURN BOOLEAN IS
59
60 -- This procedure is used by RFQs and Quotations to verify the
61 -- END_DATE which has been entered on the header level is not
62 -- earlier than than the latest END_DATE of it's shipments.
63
64
65 x_progress VARCHAR2(3) := '';
66 x_end_date_count NUMBER;
67
68 BEGIN
69 x_progress := '010';
70
71 --
72 -- Verify that the end date entered on the header is later than
73 -- the end dates of it's shipments.
74
75
76 SELECT count(*)
77 INTO x_end_date_count
78 FROM po_line_locations
79 WHERE end_date > X_end_date
80 AND po_header_id = X_po_header_id;
81
82 x_progress := '020';
83
84 if x_end_date_count > 0 then
85 RETURN(FALSE);
86 -- dbms_output.put_line('FALSE: start date invalid');
87 else
88 RETURN(TRUE);
89 -- dbms_output.put_line('TRUE: start date valid');
90 end if;
91
92 EXCEPTION
93 when no_data_found then
94 return(TRUE);
95 WHEN OTHERS THEN
96 po_message_s.sql_error('val_end_dates', x_progress, sqlcode);
97 raise;
98
99 END val_end_dates;
100
101 /*==============================================================================
102 ** PROCEDURE : autocreate_ship()
103 Modified 26-FEB-01 MCHANDAK(OPM-GML)
104 Bug# 1548597.. Added 3 process related fields.
105 X_secondary_unit_of_measure,X_secondary_quantity and
106 X_preferred_grade.
107
108 **=============================================================================*/
109
110 procedure autocreate_ship ( X_line_location_id IN OUT NOCOPY NUMBER,
111 X_last_update_date DATE,
112 X_last_updated_by NUMBER,
113 X_creation_date DATE,
114 X_created_by NUMBER,
115 X_last_update_login NUMBER,
116 X_po_header_id NUMBER,
117 X_po_line_id NUMBER,
118 X_type_lookup_code VARCHAR2,
119 X_quantity NUMBER,
120 X_ship_to_location_id NUMBER,
121 X_ship_org_id NUMBER,
122 X_need_by_date DATE,
123 X_promised_date DATE,
124 X_unit_price NUMBER,
125 X_tax_code_id NUMBER,
126 X_taxable_flag VARCHAR2,
127 X_enforce_ship_to_location VARCHAR2,
128 X_receiving_routing_id NUMBER,
129 X_inspection_required_flag VARCHAR2,
130 X_receipt_required_flag VARCHAR2,
131 X_qty_rcv_tolerance NUMBER,
132 X_qty_rcv_exception_code VARCHAR2,
133 X_days_early_receipt_allowed NUMBER,
134 X_days_late_receipt_allowed NUMBER,
135 X_allow_substitute_receipts VARCHAR2,
136 X_receipt_days_exception_code VARCHAR2,
137 X_invoice_close_tolerance NUMBER,
138 X_receive_close_tolerance NUMBER,
139 X_item_status VARCHAR2,
140 X_outside_operation_flag VARCHAR2,
141 X_destination_type_code VARCHAR2,
142 X_expense_accrual_code VARCHAR2,
143 X_item_id NUMBER,
144 X_ussgl_transaction_code VARCHAR2,
145 X_accrue_on_receipt_flag IN OUT NOCOPY VARCHAR2,
146 X_autocreated_ship IN OUT NOCOPY BOOLEAN,
147 X_unit_meas_lookup_code IN VARCHAR2, -- Added Bug 731564
148 p_value_basis IN VARCHAR2, -- <Complex Work R12>
149 p_matching_basis IN VARCHAR2, -- <Complex Work R12>
150 -- start of bug# 1548597
151 X_secondary_unit_of_measure IN VARCHAR2,
152 X_secondary_quantity IN NUMBER,
153 X_preferred_grade IN VARCHAR2,
154 p_consigned_from_supplier_flag IN VARCHAR2 --bug 3523348
155 -- end of bug# 1548597
156 ,p_org_id IN NUMBER -- <R12.MOAC>
157 ,p_outsourced_assembly IN NUMBER -- <R12 SHIKYU>
158 )
159 is
160
161 X_Progress varchar2(3) := '';
162 x_vendor_site_id number; -- Bug 880864
163 x_vendor_id number;
164 X_invoice_match_option VARCHAR2(25);
165 x_country_of_origin_code VARCHAR2(2); -- bug 2350043 by jbalakri
166
167 /* CONSIGNED FPI START */
168
169 x_consigned_flag
170 po_line_locations.consigned_flag%TYPE := NULL;
171
172 -- OPEN is the default if the shipment line is not consigned
173 x_closed_code
174 po_line_locations.closed_code%TYPE := 'OPEN';
175
176 x_closed_reason
177 po_line_locations.closed_reason%TYPE := NULL;
178
179 l_invoice_close_tolerance number := X_invoice_close_tolerance;
180
181 l_inspection_required_flag
182 po_line_locations.inspection_required_flag%TYPE := X_inspection_required_flag;
183
184 l_receipt_required_flag
185 po_line_locations.receipt_required_flag%TYPE := X_receipt_required_flag;
186
187 /* CONSIGNED FPI END */
188
189 l_from_line_id number; -- GA FPI
190 l_from_header_id number; -- GA FPI
191 l_amount number;
192
193 /*Bug 8559443 - Variable initialization start*/
194 l_transaction_flow_header_id PO_LINE_LOCATIONS_ALL.transaction_flow_header_id%TYPE;
195 l_is_valid BOOLEAN;
196 l_in_current_sob BOOLEAN;
197 l_check_txn_flow BOOLEAN;
198 l_return_status VARCHAR2(1);
199 p_item_category_id NUMBER;
200 /*Bug 8559443 - Variable initialization end*/
201
202 begin
203
204 X_Progress := '010';
205
206 if X_item_id is NULL then
207 if X_destination_type_code = 'INVENTORY' then
208 /* invalid Defaults */
209 null;
210 else
211 /*copy expense to world and more */
212 null;
213 end if;
214 end if;
215
216
217 /* Algorithm for figuring out the value of accrue_on_receipt */
218
219 if X_outside_operation_flag = 'Y' then
220
221 X_accrue_on_receipt_flag := 'Y';
222
223 elsif X_item_status = 'E' then
224
225 if ( X_destination_type_code = '' OR
226 X_destination_type_code = 'INVENTORY') then
227
228 X_accrue_on_receipt_flag := 'Y';
229
230 elsif (X_destination_type_code = 'EXPENSE') then
231
232 if X_expense_accrual_code = 'RECEIPT' then
233
234 X_accrue_on_receipt_flag := X_receipt_required_flag;
235
236 elsif X_expense_accrual_code = 'PERIOD END' then
237
238 X_accrue_on_receipt_flag := 'N';
239
240 end if;
241
242 end if;
243 end if;
244
245 /** Bug 880864 , bgu, Apr. 28, 1999
246 * Need to autocreate match_option into the shipment
247 */
248 X_Progress := '015';
249
250 -- Get vendor site id from po header
251 select vendor_site_id, vendor_id
252 into x_vendor_site_id, x_vendor_id
253 from po_headers
254 where po_header_id = x_po_header_id;
255
256 -- The following code is copied from
257 -- PO_SHIPMENTS_C22.invoice_match_option, POXPOPOS.pld
258 if (X_vendor_site_id is not null) then
259 /* Retrieve Invoice Match Option from Vendor site*/
260 SELECT match_option
261 INTO x_invoice_match_option
262 FROM po_vendor_sites
263 WHERE vendor_site_id = X_vendor_site_id;
264 end if;
265
266 if(x_invoice_match_option is NULL) then
267 /* Retrieve Invoice Match option from Vendor */
268 if (X_vendor_id is not null) then
269 SELECT match_option
270 INTO x_invoice_match_option
271 FROM po_vendors
272 WHERE vendor_id = X_vendor_id;
273 end if;
274 end if;
275
276 if(x_invoice_match_option is NULL) then
277 /* Retrieve Invoice Match Option from Financial System
278 * Parameters */
279
280 /* Bug 9484848 start
281 As per new data model chnges in R12, invoice match option field
282 at shipment level should be defaulted from ap_product_setup table.
283 */
284 /* SELECT fsp.match_option
285 INTO x_invoice_match_option
286 FROM financials_system_parameters fsp;
287 end if;
288 */
289 SELECT aps.match_option
290 INTO x_invoice_match_option
291 FROM ap_product_setup aps;
292 end if;
293
294 /* Bug 9484848 end*/
295
296
297 X_Progress := '020';
298
299 SELECT po_line_locations_s.nextval
300 INTO X_line_location_id
301 FROM sys.dual;
302 -- Added for bug 2350043 by jbalakri
303 X_Progress:= '025';
304
305 po_coo_s.get_default_country_of_origin(x_item_id,
306 x_ship_org_id,
307 x_vendor_id,
308 x_vendor_site_id,
309 x_country_of_origin_code);
310 -- end of 2350043
311
312 X_Progress := '030';
313
314 /*Bug 8559443*/
315
316 SELECT category_id
317 INTO p_item_category_id
318 FROM po_lines
319 WHERE po_line_id = X_po_line_id;
320
321 /*Bug 8559443*/
322
323 --bug 3523348: move logic to POXPIPOL.pld so we can remove source doc
324 -- Now we pass p_consigned_from_supplier_flag parameter
325 /* CONSIGNED FPI START */
326 -- Set the new shipment as consigned if the corresponding
327 -- ASL entry is consigned and item is not expense
328
329 /* Bug 8559443: Add the procedure validate_ship_to_org to get the transaction_flow_header_id */
330
331 IF(p_consigned_from_supplier_flag = 'Y')
332 THEN
333 -- set shipment line as consigned
334 x_consigned_flag := 'Y';
335 X_accrue_on_receipt_flag := 'N';
336 l_invoice_close_tolerance := 100;
337 x_closed_code := 'CLOSED FOR INVOICE';
338 FND_MESSAGE.SET_NAME('PO', 'PO_SUP_CONS_CLOSED_REASON');
339 x_closed_reason := FND_MESSAGE.GET;
340 l_inspection_required_flag := 'N';
341 l_receipt_required_flag := 'N';
342 X_invoice_match_option := 'P';
343 ELSE
344 /* Bug 8559443 - start */
345 PO_SHARED_PROC_PVT.validate_ship_to_org
346 (p_init_msg_list => 'T',
347 x_return_status => l_return_status,
348 p_ship_to_org_id => X_ship_org_id,
349 p_item_category_id => p_item_category_id,
350 p_item_id => X_item_id,
351 x_is_valid => l_is_valid,
352 x_in_current_sob => l_in_current_sob,
353 x_check_txn_flow => l_check_txn_flow,
354 x_transaction_flow_header_id => l_transaction_flow_header_id);
355
356
357
358 IF (l_return_status <> 'S') THEN
359 -- Null out the txn flow header on error
360 l_transaction_flow_header_id := NULL;
361 ELSIF (NOT l_is_valid) THEN
362 -- Null out the txn flow header on validation failure
363 l_transaction_flow_header_id := NULL;
364
365 END IF;
366 /* Bug 8559443 - end */
367 END IF;
368 /* CONSIGNED FPI END */
369
370 /* GA FPI start : we need to insert the source doc info from the lines to shipments */
371 /* SERVICES FPJ : We also need to insert the line amount to the shipments */
372 begin
373 select from_line_id,from_header_id,amount
374 into l_from_line_id, l_from_header_id,l_amount
375 from po_lines
376 where po_line_id = X_po_line_id ;
377 exception
378 when others then
379 l_from_line_id := null;
380 l_from_header_id := null;
381 end;
382 /* GA FPI end */
383
384 INSERT into po_line_locations
385 (line_location_id ,
386 last_update_date ,
387 last_updated_by ,
388 creation_date ,
389 created_by ,
390 last_update_login ,
391 po_header_id ,
392 po_line_id ,
393 shipment_num ,
394 shipment_type ,
395 quantity ,
396 quantity_received ,
397 quantity_accepted ,
398 quantity_rejected ,
399 quantity_billed ,
400 quantity_cancelled ,
401 ship_to_location_id ,
402 ship_to_organization_id ,
403 need_by_date ,
404 promised_date ,
405 last_accept_date ,
406 cancel_flag ,
407 closed_code ,
408 approved_flag ,
409 price_override ,
410 encumbered_flag ,
411 tax_code_id ,
412 taxable_flag ,
413 enforce_ship_to_location_code,
414 receiving_routing_id ,
415 inspection_required_flag,
416 receipt_required_flag ,
417 qty_rcv_tolerance ,
418 qty_rcv_exception_code ,
419 days_early_receipt_allowed,
420 days_late_receipt_allowed,
421 allow_substitute_receipts_flag,
422 receipt_days_exception_code,
423 invoice_close_tolerance ,
424 receive_close_tolerance,
425 accrue_on_receipt_flag,
426 tax_user_override_flag,
427 calculate_tax_flag,
428 unit_meas_lookup_code, -- Added Bug 731564
429 match_option, -- Bug 880864
430 -- start of bug# 1548597
431 secondary_unit_of_measure,
432 secondary_quantity,
433 preferred_grade,
434 secondary_quantity_received,
435 secondary_quantity_accepted,
436 secondary_quantity_rejected,
437 secondary_quantity_cancelled,
438 country_of_origin_code, -- bug 2350043
439 -- end of bug# 1548597
440 consigned_flag, /* CONSIGNED FPI */
441 closed_reason , /* CONSIGNED FPI */
442 from_header_id, -- GA FPI
443 from_line_id , -- GA FPI
444 amount
445 --<DBI Req Fulfillment 11.5.11 Start >
446 ,shipment_closed_date
447 ,closed_for_receiving_date
448 ,closed_for_invoice_date
449 --<DBI Req Fulfillment 11.5.11 End >
450 ,Org_Id -- <R12.MOAC>
451 , value_basis -- <Complex Work R12>
452 , matching_basis -- <Complex Work R12>
453 ,outsourced_assembly --<R12 SHIKYU>
454 ,transaction_flow_header_id -- Bug8559443
455 )
456 VALUES
457 (X_line_location_id ,
458 X_last_update_date ,
459 X_last_updated_by ,
460 X_creation_date ,
461 X_created_by ,
462 X_last_update_login ,
463 X_po_header_id ,
464 X_po_line_id ,
465 '1' ,
466 X_type_lookup_code ,
467 X_quantity ,
468 '0' ,
469 '0' ,
470 '0' ,
471 '0' ,
472 '0' ,
473 X_ship_to_location_id ,
474 X_ship_org_id ,
475 X_need_by_date ,
476 X_promised_date ,
477 '' ,
478 'N' ,
479 x_closed_code, /* CONSIGNED FPI */ --'OPEN' ,
480 'N' ,
481 X_unit_price,
482 'N' ,
483 X_tax_code_id ,
484 X_taxable_flag ,
485 X_enforce_ship_to_location ,
486 X_receiving_routing_id ,
487 l_inspection_required_flag , /* CONSIGNED FPI */
488 l_receipt_required_flag , /* CONSIGNED FPI */
489 X_qty_rcv_tolerance ,
490 X_qty_rcv_exception_code ,
491 X_days_early_receipt_allowed ,
492 X_days_late_receipt_allowed ,
493 X_allow_substitute_receipts ,
494 X_receipt_days_exception_code ,
495 l_invoice_close_tolerance , /* CONSIGNED FPI */
496 X_receive_close_tolerance ,
497 nvl(X_accrue_on_receipt_flag,'N'),
498 'N',
499 'Y',
500 X_unit_meas_lookup_code, -- Added Bug 731564
501 X_invoice_match_option, -- Bug 880864
502 -- start of bug# 1548597
503 X_secondary_unit_of_measure,
504 X_secondary_quantity,
505 X_preferred_grade,
506 decode(X_secondary_unit_of_measure,NULL,NULL,0),
507 decode(X_secondary_unit_of_measure,NULL,NULL,0),
508 decode(X_secondary_unit_of_measure,NULL,NULL,0),
509 decode(X_secondary_unit_of_measure,NULL,NULL,0),
510 x_country_of_origin_code, -- bug 2350043
511 -- end of bug# 1548597
512 x_consigned_flag, /* CONSIGNED FPI */
513 x_closed_reason, /* CONSIGNED FPI */
514 l_from_header_id, -- GA FPI
515 l_from_line_id, -- GA FPI
516 l_amount -- SERVICES FPJ
517 --<DBI Req Fulfillment 11.5.11 Start >
518 ,decode(x_closed_code,'CLOSED',
519 sysdate, null) -- Shipment_closed_date
520 ,decode(x_closed_code,'CLOSED',sysdate,
521 'CLOSED FOR RECEIVING',sysdate,null) -- Closed_for_receiving_date
522 ,decode(x_closed_code,'CLOSED',sysdate,
523 'CLOSED FOR INVOICE',sysdate,null) -- closed_for_invoice_date
524 --<DBI Req Fulfillment 11.5.11 End >
525 ,p_org_id -- <R12.MOAC>
526 , p_value_basis -- <Complex Work R12>
527 , p_matching_basis -- <Complex Work R12>
528 , p_outsourced_assembly -- <R12 SHIKYU>
529 ,l_transaction_flow_header_id -- Bug8559443
530 );
531
532 X_autocreated_ship := TRUE;
533
534
535 exception
536
537 when others then
538 po_message_s.sql_error('autocreate_ship', X_progress, sqlcode);
539 raise;
540
541 end autocreate_ship;
542
543 PROCEDURE get_matching_controls(X_vendor_id IN number,
544 X_line_type_id IN number,
545 X_item_id IN number,
546 X_receipt_required_flag IN OUT NOCOPY VARCHAR2,
547 X_inspection_required_flag IN OUT NOCOPY VARCHAR2) IS
548
549 x_progress VARCHAR2(3) := '';
550 X_receipt_required_flag_tmp VARCHAR2(1) := '';
551 X_inspection_required_flag_tmp VARCHAR2(1) := '';
552
553
554 BEGIN
555
556 -- Get receipt required flag and inspection required flags
557 -- in the following order:
558 -- item
559 -- line type (only for receipt required)
560 -- vendor
561 -- system options
562
563 x_progress := '010';
564
565 -- bug 413511
566 -- Set default values from PO system options
567
568 -- bug 10286305 : null handling for X_receipt_required_flag
569 -- and X_inspection_required_flag was missing
570
571 BEGIN
572
573 SELECT receiving_flag,
574 inspection_required_flag
575 INTO X_receipt_required_flag,
576 X_inspection_required_flag
577 FROM po_system_parameters;
578
579 EXCEPTION
580 WHEN OTHERS THEN
581 X_receipt_required_flag := NULL;
582 X_inspection_required_flag := NULL;
583
584 END;
585
586 x_progress := '020';
587
588 IF (X_vendor_id is NOT null) THEN
589
590 SELECT receipt_required_flag,
591 inspection_required_flag
592 INTO X_receipt_required_flag_tmp,
593 X_inspection_required_flag_tmp
594 FROM po_vendors
595 WHERE vendor_id = X_vendor_id;
596
597 -- bug 413511
598 -- If the flags are not null,take them; Otherwise, bypass them
599
600 IF X_receipt_required_flag_tmp is not NULL AND
601 X_inspection_required_flag_tmp is not NULL THEN
602 X_receipt_required_flag := X_receipt_required_flag_tmp;
603 X_inspection_required_flag := X_inspection_required_flag_tmp;
604 END IF;
605
606 END IF;
607
608 x_progress := '030';
609
610 IF (X_line_type_id is NOT null) THEN
611
612 SELECT receiving_flag
613 INTO X_receipt_required_flag_tmp
614 FROM po_line_types
615 WHERE line_type_id = X_line_type_id;
616
617 -- bug 413511
618 -- If the flag is not null, take it; Otherwise bypass it
619
620 IF X_receipt_required_flag_tmp is not NULL THEN
621 X_receipt_required_flag := X_receipt_required_flag_tmp;
622
623 /* Bug 2174318 If the RR_flag is N then the IR_flag should
624 ** be made null to override the default from vendors
625 */
626 IF X_receipt_required_flag_tmp = 'N' THEN
627 X_inspection_required_flag := null;
628 END IF;
629
630 END IF;
631
632 END IF;
633
634 x_progress := '040';
635
636 IF (X_item_id is NOT null) THEN
637
638 SELECT receipt_required_flag,
639 inspection_required_flag
640 INTO X_receipt_required_flag_tmp,
641 X_inspection_required_flag_tmp
642 FROM mtl_system_items,
643 financials_system_parameters
644 WHERE inventory_item_id = X_item_id
645 AND organization_id = inventory_organization_id;
646
647 -- bug 413511
648 -- If the flags are not null,take them; Otherwise, bypass them
649
650 -- Bug 475621
651 -- INV Item forms allow user to set the RR_flag and IR_flag separately
652 -- need to handle one null value, change AND to OR
653
654 -- Bug 12791538 start. Making the conditions similar to autocreate code since there is mismatch between
655 -- the Defaulted Invoice Matching in Enter PO form and through Autocreate.
656
657
658 IF X_receipt_required_flag_tmp is not NULL THEN
659
660 X_receipt_required_flag := X_receipt_required_flag_tmp;
661
662 end if;
663
664
665 IF X_inspection_required_flag_tmp is not NULL THEN
666
667 X_inspection_required_flag := X_inspection_required_flag_tmp;
668
669 end if;
670
671 --Bug 12791538 end.
672
673 END IF;
674
675 EXCEPTION
676 when others then
677 po_message_s.sql_error('get_matching_controls', x_progress, sqlcode);
678 raise;
679
680 END get_matching_controls;
681
682
683
684 /* <TIMEPHASED FPI START> */
685
686 /*
687 This procedure is used to perform the various validations on the price break
688 effective Start Date, End Date and the line level Expiration Date
689 */
690 PROCEDURE validate_effective_dates(p_start_date IN date,
691 p_end_date IN date,
692 p_from_date IN date,
693 p_to_date IN date,
694 p_expiration_date IN date,
695 x_errormsg OUT NOCOPY varchar2)
696 IS
697 l_progress VARCHAR2(3) := '';
698
699 BEGIN
700 l_progress := '000';
701 /* Validation for expiration date */
702 if (p_expiration_date is not null) then
703 if (p_start_date > p_expiration_date OR p_end_date < p_expiration_date) then
704
705 -- bug2735633
706 -- Error message to be returned here should be POX_EXPIRATION_DATES
707 -- instead of POX_EXPIRATION_DATES1
708
709 -- x_errormsg := 'POX_EXPIRATION_DATES1';
710 x_errormsg := 'POX_EXPIRATION_DATES';
711 return;
712 end if;
713 end if;
714
715 l_progress := '001';
716 /*
717 Pricebreak effective From Date cannot be earlier than Blanket Agreement header
718 start date
719 */
720 if (p_from_date is not null AND p_from_date < p_start_date) then
721 x_errormsg := 'POX_EFFECTIVE_DATES1';
722 return;
723 end if;
724
725
726 l_progress := '002';
727 /*
728 Pricebreak effective From Date cannot be later than Blanket Agreement header
729 end date
730 */
731 if (p_from_date is not null AND p_from_date > p_end_date) then
732 x_errormsg := 'POX_EFFECTIVE_DATES4';
733 return;
734 end if;
735
736 l_progress := '003';
737 /* Pricebreak effective From Date cannot be later than Pricebreak effective To Date */
738 /* Bug 2691705
739 * Changed the message name to POX_EFFECTIVE_DATES3.
740 */
741 if (p_to_date is not null AND p_from_date is not null AND p_from_date > p_to_date ) then
742 x_errormsg := 'POX_EFFECTIVE_DATES3';
743 return;
744 end if;
745
746 /* Bug 2691705.
747 * Price break from date cannot be greater than the blanket line
748 * expiration date.
749 */
750 l_progress := '035';
751
752 if (p_from_date is not null and p_from_date > p_expiration_date ) then
753 x_errormsg := 'POX_EFFECTIVE_DATES6';
754 return;
755 end if;
756 l_progress := '004';
757 /*
758 Pricebreak effective To Date cannot be later than Expiration date, if Expiration
759 Date exists
760 */
761 /* Bug 2691705
762 * Changed the message name to POX_EFFECTIVE_DATES2.
763 */
764 if (p_expiration_date is not null AND p_to_date > p_expiration_date) then
765 x_errormsg := 'POX_EFFECTIVE_DATES2';
766 return;
767 end if;
768
769 l_progress := '005';
770 /*
771 If expiration date does not exist and Pricebreak To Date is greater than Header End
772 date, raise an error message
773 */
774 if (p_expiration_date is null AND p_end_date is not null AND p_to_date > p_end_date) then
775 x_errormsg := 'POX_EFFECTIVE_DATES';
776 return;
777 end if;
778
779 l_progress := '006';
780 /* Pricebreak To Date cannot be earlier than Header Start date */
781 if (p_start_date is not null AND p_to_date < p_start_date) then
782 x_errormsg := 'POX_EFFECTIVE_DATES5';
783 return;
784 end if;
785
786 l_progress := '007';
787 /* Pricebreak effective To Date cannot be earlier than Pricebreak effective From Date */
788 /* Bug 2691705
789 * Changed the message name to POX_EFFECTIVE_DATES3.
790 */
791 if (p_to_date is not null AND p_from_date is not null AND p_to_date < p_from_date) then
792 x_errormsg := 'POX_EFFECTIVE_DATES3';
793 return;
794 end if;
795
796 EXCEPTION
797 when others then
798 po_message_s.sql_error('validate_effective_dates', l_progress, sqlcode);
799 raise;
800
801 END validate_effective_dates;
802
803
804
805 PROCEDURE validate_pricebreak_attributes(p_from_date IN date,
806 p_to_date IN date,
807 p_quantity IN varchar2,
808 p_ship_to_org IN varchar2,
809 p_ship_to_location IN varchar2,
810 x_errormsg_name OUT NOCOPY varchar2)
811 IS
812 l_progress VARCHAR2(3) := '';
813
814 BEGIN
815 l_progress := '001';
816 if (p_from_date is null AND p_to_date is null
817 AND (p_quantity is null OR (to_number(p_quantity) <= 0))
818 AND p_ship_to_org is null AND p_ship_to_location is null) then
819 x_errormsg_name := 'POX_PRICEBREAK_ITEM_FAILED';
820 return;
821 end if;
822
823 EXCEPTION
824 when others then
825 po_message_s.sql_error('validate_pricebreak_attributes', l_progress, sqlcode);
826 raise;
827
828 END validate_pricebreak_attributes;
829
830 /* <TIMEPHASED FPI END> */
831
832
833
834 END PO_SHIPMENTS_SV8;
[Home] [Help]