[Home] [Help]
PACKAGE BODY: APPS.PO_TERMS_SV
Source
1 PACKAGE BODY PO_TERMS_SV AS
2 /* $Header: POXPOTEB.pls 115.7 2003/12/03 20:17:17 bao ship $*/
3
4 -- Read the profile option that enables/disables the debug log
5 g_asn_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('PO_RVCTP_ENABLE_TRACE'),'N');
6
7
8 -- bug3225062
9
10 PROCEDURE set_terms_token
11 ( p_prompt IN VARCHAR2,
12 p_po_terms_val IN VARCHAR2,
13 p_ref_terms_val IN VARCHAR2,
14 x_po_terms_list IN OUT NOCOPY VARCHAR2,
15 x_ref_terms_list IN OUT NOCOPY VARCHAR2
16 ) ;
17
18 /*===========================================================================
19
20 FUNCTION NAME: val_fob_code()
21
22 ===========================================================================*/
23 FUNCTION val_fob_code(X_fob_lookup_code IN VARCHAR2) return BOOLEAN IS
24
25 X_progress varchar2(25) := NULL;
26 X_fob_lookup_val varchar2(25) := NULL;
27
28 BEGIN
29
30 X_progress := '010';
31
32 po_vendors_sv.val_fob(X_fob_lookup_code, X_fob_lookup_val);
33
34 if (X_fob_lookup_val is not null) then
35 return (TRUE);
36 else
37 return (FALSE);
38 end if;
39
40 EXCEPTION
41
42 when others then
43 po_message_s.sql_error('val_fob_code', X_progress, sqlcode);
44 raise;
45
46 END val_fob_code;
47
48 /*===========================================================================
49
50 FUNCTION NAME: val_freight_code()
51
52 ===========================================================================*/
53 FUNCTION val_freight_code(X_freight_terms_code IN VARCHAR2) return BOOLEAN IS
54
55 X_progress varchar2(3) := NULL;
56 X_freight_terms_val varchar2(25) := NULL;
57
58 BEGIN
59
60 X_progress := '010';
61
62 po_vendors_sv.val_freight_terms(X_freight_terms_code, X_freight_terms_val);
63
64 if (X_freight_terms_val is not null) then
65 return (TRUE);
66 else
67 return (FALSE);
68 end if;
69
70 EXCEPTION
71
72 when others then
73 po_message_s.sql_error('val_freight_code', X_progress, sqlcode);
74 raise;
75
76 END val_freight_code;
77
78 /*===========================================================================
79
80 FUNCTION NAME: val_ship_via()
81
82 ===========================================================================*/
83 FUNCTION val_ship_via(X_ship_via_code IN VARCHAR2,
84 X_org_id IN NUMBER) return BOOLEAN IS
85
86 X_progress varchar2(3) := NULL;
87 X_ship_via_val varchar2(25) := NULL;
88
89 BEGIN
90
91 X_progress := '010';
92
93 po_vendors_sv.val_freight_carrier(X_ship_via_code, X_org_id, X_ship_via_val);
94
95 if (X_ship_via_val is not null) then
96 return (TRUE);
97 else
98 return (FALSE);
99 end if;
100
101 EXCEPTION
102
103 when others then
104 po_message_s.sql_error('val_ship_via', X_progress, sqlcode);
105 raise;
106
107 END val_ship_via;
108
109 /*===========================================================================
110
111 FUNCTION NAME: val_payment_terms()
112
113 ===========================================================================*/
114
115 FUNCTION val_payment_terms(X_ap_terms_id IN NUMBER) return BOOLEAN IS
116
117 X_progress varchar2(3) := NULL;
118 X_ap_terms_val number := NULL;
119
120 BEGIN
121
122 X_progress := '010';
123
124 po_terms_sv.val_ap_terms(X_ap_terms_id, X_ap_terms_val);
125
126 if (X_ap_terms_val is not null) then
127 return (TRUE);
128 else
129 return (FALSE);
130 end if;
131
132 EXCEPTION
133
134 when others then
135 po_message_s.sql_error('val_payment_terms', X_progress, sqlcode);
136 raise;
137
138 END val_payment_terms;
139
140 /*===========================================================================
141
142 PROCEDURE NAME: val_ap_terms()
143
144 ===========================================================================*/
145
146
147 procedure val_ap_terms (X_temp_terms_id IN number, X_res_terms_id IN OUT NOCOPY number) is
148 X_progress varchar2(3) := '';
149 begin
150 X_progress := '010';
151
152 /* Check if the given Terms Id is active */
153
154 SELECT term_id
155 INTO X_res_terms_id
156 FROM ap_terms
157 WHERE sysdate BETWEEN nvl(start_date_active, sysdate - 1)
158 AND nvl(end_date_active, sysdate + 1)
159 AND term_id = X_temp_terms_id;
160
161 exception
162
163 when no_data_found then
164 X_res_terms_id := '';
165 when too_many_rows then
166 X_res_terms_id := '';
167 when others then
168 po_message_s.sql_error('val_ap_terms',X_progress,sqlcode);
169 raise;
170
171 end val_ap_terms;
172
173 /*===========================================================================
174
175 PROCEDURE NAME: get_terms_name()
176
177 ===========================================================================*/
178
179
180 procedure get_terms_name (X_terms_id IN NUMBER,
181 X_terms_name IN OUT NOCOPY VARCHAR2) IS
182
183 X_progress varchar2(3) := '';
184
185 begin
186 X_progress := '010';
187
188 /* Get the Terms Name for a certain terms_id */
189
190 IF (X_terms_id is not NULL) THEN
191
192 SELECT name
193 INTO X_terms_name
194 FROM ap_terms
195 WHERE term_id = X_terms_id;
196
197 END IF;
198
199 exception
200
201 when others then
202 po_message_s.sql_error('get_terms_name',X_progress,sqlcode);
203 raise;
204
205 end get_terms_name;
206
207 /*===========================================================================
208
209 PROCEDURE NAME: derive_payment_terms_info()
210
211 ===========================================================================*/
212
213 PROCEDURE derive_payment_terms_info(
214 p_pay_record IN OUT NOCOPY RCV_SHIPMENT_HEADER_SV.PayRecType) IS
215
216 cid INTEGER;
217 rows_processed INTEGER;
218 sql_str VARCHAR2(2000);
219
220 Pay_name_null BOOLEAN := TRUE;
221 Pay_id_null BOOLEAN := TRUE;
222
223 BEGIN
224
225 sql_str := 'SELECT name, term_id FROM ap_terms WHERE ';
226
227 IF p_pay_record.payment_term_name IS NULL and
228 p_pay_record.payment_term_id IS NULL THEN
229
230 p_pay_record.error_record.error_status := 'W';
231 RETURN;
232
233 END IF;
234
235 IF p_pay_record.payment_term_name IS NOT NULL and
236 p_pay_record.payment_term_id IS NOT NULL THEN
237
238 p_pay_record.error_record.error_status := 'S';
239 RETURN;
240
241 END IF;
242
243 IF p_pay_record.payment_term_name IS NOT NULL THEN
244
245 sql_str := sql_str || ' name = :v_pay_name and';
246 pay_name_null := FALSE;
247
248 END IF;
249
250 IF p_pay_record.payment_term_id IS NOT NULL THEN
251
252 sql_str := sql_str || ' term_id = :v_pay_id and';
253 pay_id_null := FALSE;
254
255 END IF;
256
257 sql_str := substr(sql_str,1,length(sql_str)-3);
258
259 -- dbms_output.put_line(substr(sql_str,1,255));
260 -- dbms_output.put_line(substr(sql_str,256,255));
261 -- dbms_output.put_line(substr(sql_str,513,255));
262
263 cid := dbms_sql.open_cursor;
264
265 dbms_sql.parse(cid, sql_str , dbms_sql.native);
266
267 dbms_sql.define_column(cid,1,p_pay_record.payment_term_name,55);
268 dbms_sql.define_column(cid,2,p_pay_record.payment_term_id);
269
270 IF NOT pay_name_null THEN
271
272 dbms_sql.bind_variable(cid,'v_pay_name',p_pay_record.payment_term_name);
273
274 END IF;
275
276 IF NOT pay_id_null THEN
277
278 dbms_sql.bind_variable(cid,'v_pay_id',p_pay_record.payment_term_id);
279
280 END IF;
281
282 rows_processed := dbms_sql.execute_and_fetch(cid);
283
284 IF rows_processed = 1 THEN
285
286 IF pay_name_null THEN
287 dbms_sql.column_value(cid,1,p_pay_record.payment_term_name);
288 END IF;
289
290 IF pay_id_null THEN
291 dbms_sql.column_value(cid,2,p_pay_record.payment_term_id);
292 END IF;
293
294 p_pay_record.error_record.error_status := 'S';
295
296 ELSIF rows_processed = 0 THEN
297
298 p_pay_record.error_record.error_status := 'W';
299
300 ELSE
301
302 p_pay_record.error_record.error_status := 'W';
303
304 END IF;
305
306 IF dbms_sql.is_open(cid) THEN
307 dbms_sql.close_cursor(cid);
308 END IF;
309
310 EXCEPTION
311 WHEN others THEN
312
313 IF dbms_sql.is_open(cid) THEN
314 dbms_sql.close_cursor(cid);
315 END IF;
316
317 p_pay_record.error_record.error_status := 'U';
318 p_pay_record.error_record.error_message := sqlerrm;
319 IF (g_asn_debug = 'Y') THEN
320 asn_debug.put_line(p_pay_record.error_record.error_message);
321 END IF;
322
323 END derive_payment_terms_info;
324
325 /*===========================================================================
326
327 PROCEDURE NAME: validate_payment_terms_info()
328
329 ===========================================================================*/
330
331 PROCEDURE validate_payment_terms_info (
332 p_pay_record IN OUT NOCOPY RCV_SHIPMENT_HEADER_SV.PayRecType) IS
333
334 X_cid INTEGER;
335 X_rows_processed INTEGER;
336 X_sql_str VARCHAR2(2000);
337
338 X_pay_name_null BOOLEAN := TRUE;
339 X_pay_id_null BOOLEAN := TRUE;
340
341 X_start_date_active DATE;
342 X_end_date_active DATE;
343 X_enabled_flag VARCHAR2(1);
344 X_sysdate DATE := sysdate;
345
346 BEGIN
347
348 X_sql_str := 'SELECT start_date_active, end_date_active, enabled_flag FROM ap_terms WHERE ';
349
350 IF p_pay_record.payment_term_name IS NULL and
351 p_pay_record.payment_term_id IS NULL THEN
352
353 -- dbms_output.put_line('All Blanks');
354 p_pay_record.error_record.error_status := 'E';
355 p_pay_record.error_record.error_message := 'All Blanks';
356 RETURN;
357
358 END IF;
359
360 IF p_pay_record.payment_term_name IS NOT NULL THEN
361
362 X_sql_str := X_sql_str || ' name = :v_pay_name and';
363 X_pay_name_null := FALSE;
364
365 END IF;
366
367 IF p_pay_record.payment_term_id IS NOT NULL THEN
368
369 X_sql_str := X_sql_str || ' term_id = :v_pay_id and';
370 X_pay_id_null := FALSE;
371
372 END IF;
373
374 X_sql_str := substr(X_sql_str,1,length(X_sql_str)-3);
375
376 -- dbms_output.put_line(substr(X_sql_str,1,255));
377 -- dbms_output.put_line(substr(X_sql_str,256,255));
378 -- dbms_output.put_line(substr(X_sql_str,513,255));
379
380 X_cid := dbms_sql.open_cursor;
381
382 dbms_sql.parse(X_cid, X_sql_str , dbms_sql.native);
383
384 dbms_sql.define_column(X_cid,1,X_start_date_active);
385 dbms_sql.define_column(X_cid,2,X_end_date_active);
386 dbms_sql.define_column(X_cid,3,X_enabled_flag,1);
387
388 IF NOT X_pay_name_null THEN
389
390 dbms_sql.bind_variable(X_cid,'v_pay_name',p_pay_record.payment_term_name);
391
392 END IF;
393
394 IF NOT X_pay_id_null THEN
395
396 dbms_sql.bind_variable(X_cid,'v_pay_id',p_pay_record.payment_term_id);
397
398 END IF;
399
400 X_rows_processed := dbms_sql.execute_and_fetch(X_cid);
401
402 IF X_rows_processed = 1 THEN
403
404 dbms_sql.column_value(X_cid,1,X_start_date_active);
405 dbms_sql.column_value(X_cid,2,X_end_date_active);
406 dbms_sql.column_value(X_cid,3,X_enabled_flag);
407
408 IF NOT (X_sysdate between nvl(X_start_date_active, X_sysdate -1) and
409 nvl(X_end_date_active, X_sysdate + 1) and
410 nvl(X_enabled_flag,'Y') = 'Y') THEN
411
412 IF (g_asn_debug = 'Y') THEN
413 asn_debug.put_line('Disabled');
414 END IF;
415 p_pay_record.error_record.error_status := 'E';
416 p_pay_record.error_record.error_message := 'RCV_PAY_TERMS_DISABLED';
417
418 IF dbms_sql.is_open(X_cid) THEN
419 dbms_sql.close_cursor(X_cid);
420 END IF;
421
422 RETURN;
423
424 END IF;
425
426 p_pay_record.error_record.error_status := 'S';
427 p_pay_record.error_record.error_message := NULL;
428
429 ELSIF X_rows_processed = 0 THEN
430
431 p_pay_record.error_record.error_status := 'E';
432 p_pay_record.error_record.error_message := 'RCV_PAY_TERMS_ID';
433
434 IF dbms_sql.is_open(X_cid) THEN
435 dbms_sql.close_cursor(X_cid);
436 END IF;
437
438 RETURN;
439
440 ELSE
441
442 p_pay_record.error_record.error_status := 'E';
443 p_pay_record.error_record.error_message := 'Too many rows';
444
445 IF dbms_sql.is_open(X_cid) THEN
446 dbms_sql.close_cursor(X_cid);
447 END IF;
448
449 RETURN;
450
451 END IF;
452
453 IF dbms_sql.is_open(X_cid) THEN
454 dbms_sql.close_cursor(X_cid);
455 END IF;
456
457 EXCEPTION
458 WHEN others THEN
459 IF dbms_sql.is_open(X_cid) THEN
460 dbms_sql.close_cursor(X_cid);
461 END IF;
462
463 p_pay_record.error_record.error_status := 'U';
464 p_pay_record.error_record.error_message := sqlerrm;
465 IF (g_asn_debug = 'Y') THEN
466 asn_debug.put_line(p_pay_record.error_record.error_message);
467 END IF;
468
469 END validate_payment_terms_info;
470
471 /*===========================================================================
472
473 PROCEDURE NAME: validate_freight_carrier_info()
474
475 ===========================================================================*/
476
477 PROCEDURE validate_freight_carrier_info (
478 p_carrier_rec IN OUT NOCOPY RCV_SHIPMENT_HEADER_SV.FreightRecType) IS
479
480 cursor C IS SELECT ofg.disable_date disable_date
481 FROM org_freight ofg
482 WHERE
483 ofg.freight_code = p_carrier_rec.freight_carrier_code and
484 ofg.organization_id = p_carrier_rec.organization_id ;
485
486 X_sysdate DATE := sysdate;
487 freight_record C%ROWTYPE;
488
489 BEGIN
490
491 OPEN C;
492 FETCH C INTO freight_record;
493 IF C%NOTFOUND THEN
494
495 -- dbms_output.put_line('Invalid Carrier');
496 p_carrier_rec.error_record.error_status := 'E';
497 p_carrier_rec.error_record.error_message := 'CARRIER_INVALID';
498 CLOSE C;
499 RETURN;
500
501 ELSE
502
503 IF nvl(freight_record.disable_date, X_sysdate + 1) < X_sysdate THEN
504
505 -- dbms_output.put_line('Disabled Carrier');
506 p_carrier_rec.error_record.error_status := 'E';
507 p_carrier_rec.error_record.error_message := 'CARRIER_DISABLED';
508 CLOSE C;
509 RETURN;
510
511 END IF;
512
513 LOOP
514
515 FETCH C INTO freight_record;
516 IF C%NOTFOUND THEN
517
518 p_carrier_rec.error_record.error_status := 'S';
519 p_carrier_rec.error_record.error_message := NULL;
520 exit;
521
522 ELSE
523
524 p_carrier_rec.error_record.error_status := 'E';
525 p_carrier_rec.error_record.error_message := 'Too many rows';
526 EXIT;
527
528 END IF;
529
530 END LOOP;
531
532 END IF;
533
534 EXCEPTION
535
536 WHEN others THEN
537
538 p_carrier_rec.error_record.error_status := 'U';
539 p_carrier_rec.error_record.error_message := sqlerrm;
540 RETURN;
541
542 END validate_freight_carrier_info;
543
544
545 --=============================================================================
546 -- PROCEDURE : get_global_terms_conditions <2699404>
547 -- TYPE : Private
548 --
549 -- PRE-REQS : -
550 -- MODIFIES : -
551 --
552 -- DESCRIPTION : Gets global Terms and Conditions for a particular document.
553 -- If document does not exist, returns NULLs for OUT parameters.
554 --
555 -- PARAMETERS : p_po_header_id - document ID
556 --
557 -- RETURNS : x_terms_id - Payment Terms ID
558 -- x_fob_lookup_code - FOB Lookup Code
559 -- x_freight_terms - Freight Terms Lookup Code
560 -- x_supplier_note - Supplier Note
561 -- x_receiver_note - Receiver Note
562 --
563 -- EXCEPTIONS : -
564 --=============================================================================
565 PROCEDURE get_global_terms_conditions
566 (
567 p_po_header_id IN PO_HEADERS_ALL.po_header_id%TYPE
568 , x_terms_id OUT NOCOPY PO_HEADERS_ALL.terms_id%TYPE
569 , x_fob_lookup_code OUT NOCOPY PO_HEADERS_ALL.fob_lookup_code%TYPE
570 , x_freight_terms OUT NOCOPY PO_HEADERS_ALL.freight_terms_lookup_code%TYPE
571 , x_supplier_note OUT NOCOPY PO_HEADERS_ALL.note_to_vendor%TYPE
572 , x_receiver_note OUT NOCOPY PO_HEADERS_ALL.note_to_receiver%TYPE
573 , x_shipping_control OUT NOCOPY PO_HEADERS_ALL.shipping_control%TYPE -- <INBOUND LOGISTICS FPJ>
574 )
575 IS
576 BEGIN
577
578 SELECT terms_id
579 , fob_lookup_code
580 , freight_terms_lookup_code
581 , note_to_vendor
582 , note_to_receiver
583 , shipping_control -- <INBOUND LOGISTICS FPJ>
584 INTO x_terms_id
585 , x_fob_lookup_code
586 , x_freight_terms
587 , x_supplier_note
588 , x_receiver_note
589 , x_shipping_control -- <INBOUND LOGISTICS FPJ>
590 FROM po_headers_all
591 WHERE po_header_id = p_po_header_id;
592
593 EXCEPTION
594
595 WHEN OTHERS THEN
596 x_terms_id := NULL;
597 x_fob_lookup_code := NULL;
598 x_freight_terms := NULL;
599 x_supplier_note := NULL;
600 x_receiver_note := NULL;
601 x_shipping_control := NULL; -- <INBOUND LOGISTICS FPJ>
602
603 END get_global_terms_conditions;
604
605
606 --=============================================================================
607 -- PROCEDURE : get_local_terms_conditions <2699404>
608 -- TYPE : Private
609 --
610 -- PRE-REQS : -
611 -- MODIFIES : -
612 --
613 -- DESCRIPTION : Gets the local Terms and Conditions for a particular document.
614 -- If document does not exist, returns NULLs for OUT parameters.
615 --
616 -- PARAMETERS : p_po_header_id - document ID
617 --
618 -- RETURNS : x_pay_on_code - Pay On Lookup Code
619 -- x_bill_to_id - Bill-To Location ID
620 -- x_ship_to_id - Ship-To Location ID
621 -- x_ship_via_code - Carrier (Ship Via) Lookup Code
622 ---
623 -- EXCEPTIONS : -
624 --=============================================================================
625 PROCEDURE get_local_terms_conditions
626 (
627 p_po_header_id IN PO_HEADERS_ALL.po_header_id%TYPE
628 , x_pay_on_code OUT NOCOPY PO_HEADERS_ALL.pay_on_code%TYPE
629 , x_bill_to_id OUT NOCOPY PO_HEADERS_ALL.bill_to_location_id%TYPE
630 , x_ship_to_id OUT NOCOPY PO_HEADERS_ALL.ship_to_location_id%TYPE
631 , x_ship_via_code OUT NOCOPY PO_HEADERS_ALL.ship_via_lookup_code%TYPE
632 )
633 IS
634 BEGIN
635
636 SELECT pay_on_code
637 , bill_to_location_id
638 , ship_to_location_id
639 , ship_via_lookup_code
640 INTO x_pay_on_code
641 , x_bill_to_id
642 , x_ship_to_id
643 , x_ship_via_code
644 FROM po_headers_all
645 WHERE po_header_id = p_po_header_id;
646
647 EXCEPTION
648
649 WHEN OTHERS THEN
650 x_pay_on_code := NULL;
651 x_bill_to_id := NULL;
652 x_ship_to_id := NULL;
653 x_ship_via_code := NULL;
654
655 END get_local_terms_conditions;
656
657 -- <GC FPJ START>
658
659 -- bug3225062 START
660
661 -----------------------------------------------------------------------
662 --Start of Comments
663 --Name: compare_terms_conditions
664 --Pre-reqs:
665 --Modifies:
666 --Locks:
667 --Function: Compare the values in p_terms_rec1 and p_terms_rec2. If
668 -- they are different, the result will be reflected in
669 -- x_comparison_result
670 --Parameters:
671 --IN:
672 --p_comparison_scrop
673 -- GLOBAL: only compare global terms
674 -- LOCAL: only compare local terms
675 -- ALL: compare global and local terms
676 --p_terms_rec1
677 -- Record containing first set of terms and conditions
678 --p_terms_rec2
679 -- Record containing second set of terms and conditions
680 --IN OUT:
681 --OUT:
682 --x_same_terms
683 -- FND_API.G_TRUE if p_terms_rec1 and p_terms_rec2 have the same terms
684 -- and conditions within the scope
685 -- FND_API.G_FALSE if any of the terms and conditions between two records
686 -- differs within the scope
687 --x_comparison_result
688 -- For each term in p_terms_rec1 and p_terms_rec2, if the value is the
689 -- same, 'Y' will be put into the corresponding entry in
690 -- x_comparison_result. Otherwise, a 'N' will be put in
691 --Returns:
692 --Notes:
693 --Testing:
694 --End of Comments
695 ------------------------------------------------------------------------
696
697 PROCEDURE compare_terms_conditions
698 ( p_comparison_scope IN VARCHAR2,
699 p_terms_rec1 IN terms_and_cond_rec_type,
700 p_terms_rec2 IN terms_and_cond_rec_type,
701 x_same_terms OUT NOCOPY VARCHAR2,
702 x_comparison_result OUT NOCOPY terms_cond_comp_rec_type
703 ) IS
704
705 l_api_name CONSTANT VARCHAR2(50) := 'compare_terms_conditions';
706
707 BEGIN
708 x_same_terms := FND_API.G_TRUE;
709
710 IF (p_comparison_scope IN (G_COMPARISON_SCOPE_GLOBAL,
711 G_COMPARISON_SCOPE_ALL)) THEN
712
713 SELECT DECODE (p_terms_rec1.terms_id,
714 p_terms_rec2.terms_id, 'Y', 'N'),
715 DECODE (p_terms_rec1.fob_lookup_code,
716 p_terms_rec2.fob_lookup_code, 'Y', 'N'),
717 DECODE (p_terms_rec1.freight_terms_lookup_code,
718 p_terms_rec2.freight_terms_lookup_code, 'Y', 'N'),
719 DECODE (p_terms_rec1.note_to_vendor,
720 p_terms_rec2.note_to_vendor, 'Y', 'N'),
721 DECODE (p_terms_rec1.note_to_receiver,
722 p_terms_rec2.note_to_receiver, 'Y', 'N'),
723 DECODE (p_terms_rec1.shipping_control,
724 p_terms_rec2.shipping_control, 'Y', 'N')
725 INTO x_comparison_result.terms_id_eq,
726 x_comparison_result.fob_lookup_code_eq,
727 x_comparison_result.freight_terms_lookup_code_eq,
728 x_comparison_result.note_to_vendor_eq,
729 x_comparison_result.note_to_receiver_eq,
730 x_comparison_result.shipping_control_eq
731 FROM dual;
732
733 IF (x_comparison_result.terms_id_eq = 'N' OR
734 x_comparison_result.fob_lookup_code_eq = 'N' OR
735 x_comparison_result.freight_terms_lookup_code_eq = 'N' OR
736 x_comparison_result.note_to_vendor_eq = 'N' OR
737 x_comparison_result.note_to_receiver_eq = 'N' OR
738 x_comparison_result.shipping_control_eq = 'N') THEN
739
740 x_same_terms := FND_API.G_FALSE;
741 END IF;
742
743 END IF; -- scope IN ('GLOBAL', 'ALL')
744
745 IF (p_comparison_scope IN (G_COMPARISON_SCOPE_LOCAL,
746 G_COMPARISON_SCOPE_ALL)) THEN
747
748 SELECT DECODE (p_terms_rec1.pay_on_code,
749 p_terms_rec2.pay_on_code, 'Y', 'N'),
750 DECODE (p_terms_rec1.bill_to_location_id,
751 p_terms_rec2.bill_to_location_id, 'Y', 'N'),
752 DECODE (p_terms_rec1.ship_to_location_id,
753 p_terms_rec2.ship_to_location_id, 'Y', 'N'),
754 DECODE (p_terms_rec1.ship_via_lookup_code,
755 p_terms_rec2.ship_via_lookup_code, 'Y', 'N')
756 INTO x_comparison_result.pay_on_code_eq,
757 x_comparison_result.bill_to_location_id_eq,
758 x_comparison_result.ship_to_location_id_eq,
759 x_comparison_result.ship_via_lookup_code_eq
760 FROM dual;
761
762 IF (x_comparison_result.pay_on_code_eq = 'N' OR
763 x_comparison_result.bill_to_location_id_eq = 'N' OR
764 x_comparison_result.ship_to_location_id_eq = 'N' OR
765 x_comparison_result.ship_via_lookup_code_eq = 'N') THEN
766
767 x_same_terms := FND_API.G_FALSE;
768 END IF;
769
770 END IF; -- scope IN ('LOCAL', 'ALL')
771
772 EXCEPTION
773 WHEN OTHERS THEN
774 PO_MESSAGE_S.sql_error(l_api_name, '000', sqlcode);
775 RAISE;
776 END compare_terms_conditions;
777
778 -----------------------------------------------------------------------
779 --Start of Comments
780 --Name: set_terms_comparison_msg
781 --Pre-reqs:
782 --Modifies:
783 --Locks:
784 --Function: Construct the message for displaying the difference between
785 -- p_terms_rec1 and p_terms_rec2
786 --Parameters:
787 --IN:
788 --p_ref_doc_type
789 -- Type of reference documents
790 --p_comparison_scope
791 -- GLOBAL: only compare global terms
792 -- LOCAL: only compare local terms
793 -- ALL: compare global and local terms
794 --p_terms_rec1
795 -- Record containing first set of terms and conditions
796 --p_terms_rec2
797 -- Record containing second set of terms and conditions
798 --p_comparison_result
799 -- Indicate whether the terms are different for each entry in
800 -- p_terms_rec1 and p_terms_rec2
801 --IN OUT:
802 --OUT:
803 --Returns:
804 --Notes:
805 --Testing:
806 --End of Comments
807 ------------------------------------------------------------------------
808 PROCEDURE set_terms_comparison_msg
809 ( p_ref_doc_type IN VARCHAR2,
810 p_comparison_scope IN VARCHAR2,
811 p_terms_rec1 IN terms_and_cond_rec_type,
812 p_terms_rec2 IN terms_and_cond_rec_type,
813 p_comparison_result IN terms_cond_comp_rec_type
814 ) IS
815
816 l_api_name CONSTANT VARCHAR2(50) := 'set_terms_comparison_msg';
817
818 l_doc_type_token FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
819 l_po_terms_val VARCHAR2(2000);
820 l_ref_terms_val VARCHAR2(2000);
821 l_po_terms_list VARCHAR2(2000);
822 l_ref_terms_list VARCHAR2(2000);
823 l_prompt VARCHAR2(200);
824
825 l_num_terms_to_compare CONSTANT NUMBER := 10;
826
827 BEGIN
828 IF (p_ref_doc_type = 'CONTRACT') THEN
829 FND_MESSAGE.set_name('PO', 'PO_CONTRACT_AGREEMENT');
830 l_doc_type_token := FND_MESSAGE.get;
831 ELSIF (p_ref_doc_type IN ('QUOTATION', 'GA')) THEN
832 PO_CORE_S.get_displayed_value
833 ( x_lookup_type => 'PO SOURCE DOCUMENT TYPE',
834 x_lookup_code => p_ref_doc_type,
835 x_disp_value => l_doc_type_token
836 );
837 END IF;
838
839 FND_MESSAGE.set_name('PO', 'PO_PO_AND_REF_TERMS_MISMATCH');
840
841 FND_MESSAGE.set_token('DOC_TYPE', l_doc_type_token);
842
843 IF (p_comparison_scope IN (G_COMPARISON_SCOPE_GLOBAL,
844 G_COMPARISON_SCOPE_ALL)) THEN
845
846 IF (p_comparison_result.terms_id_eq = 'N') THEN
847 l_prompt := FND_MESSAGE.get_string('PO', 'POS_PAYMENT_TERMS');
848
849 l_po_terms_val :=
850 PO_VENDORS_SV.get_terms_dsp(p_terms_rec1.terms_id);
851 l_ref_terms_val :=
852 PO_VENDORS_SV.get_terms_dsp(p_terms_rec2.terms_id);
853
854 set_terms_token (p_prompt => l_prompt,
855 p_po_terms_val => l_po_terms_val,
856 p_ref_terms_val => l_ref_terms_val,
857 x_po_terms_list => l_po_terms_list,
858 x_ref_terms_list => l_ref_terms_list);
859 END IF;
860
861 IF (p_comparison_result.fob_lookup_code_eq = 'N') THEN
862 l_prompt := FND_MESSAGE.get_string('PO', 'POS_FOB');
863
864 PO_CORE_S.get_displayed_value
865 ( x_lookup_type => 'FOB',
866 x_lookup_code => p_terms_rec1.fob_lookup_code,
867 x_disp_value => l_po_terms_val
868 );
869
870 PO_CORE_S.get_displayed_value
871 ( x_lookup_type => 'FOB',
872 x_lookup_code => p_terms_rec2.fob_lookup_code,
873 x_disp_value => l_ref_terms_val
874 );
875
876 set_terms_token (p_prompt => l_prompt,
877 p_po_terms_val => l_po_terms_val,
878 p_ref_terms_val => l_ref_terms_val,
879 x_po_terms_list => l_po_terms_list,
880 x_ref_terms_list => l_ref_terms_list);
881 END IF;
882
883 IF (p_comparison_result.freight_terms_lookup_code_eq = 'N') THEN
884 l_prompt := FND_MESSAGE.get_string('PO', 'POS_FREIGHT_TERMS');
885
886 PO_CORE_S.get_displayed_value
887 ( x_lookup_type => 'FREIGHT TERMS',
888 x_lookup_code => p_terms_rec1.freight_terms_lookup_code,
889 x_disp_value => l_po_terms_val
890 );
891
892 PO_CORE_S.get_displayed_value
893 ( x_lookup_type => 'FREIGHT TERMS',
894 x_lookup_code => p_terms_rec2.freight_terms_lookup_code,
895 x_disp_value => l_ref_terms_val
896 );
897
898 set_terms_token (p_prompt => l_prompt,
899 p_po_terms_val => l_po_terms_val,
900 p_ref_terms_val => l_ref_terms_val,
901 x_po_terms_list => l_po_terms_list,
902 x_ref_terms_list => l_ref_terms_list);
903 END IF;
904
905 IF (p_comparison_result.note_to_vendor_eq = 'N') THEN
906 l_prompt := FND_MESSAGE.get_string('PO', 'POS_NOTE_TO_VENDOR');
907
908 l_po_terms_val := SUBSTRB(p_terms_rec1.note_to_vendor, 1, 200);
909 l_ref_terms_val := SUBSTRB(p_terms_rec2.note_to_vendor, 1, 200);
910
911 set_terms_token (p_prompt => l_prompt,
912 p_po_terms_val => l_po_terms_val,
913 p_ref_terms_val => l_ref_terms_val,
914 x_po_terms_list => l_po_terms_list,
915 x_ref_terms_list => l_ref_terms_list);
916 END IF;
917
918 IF (p_comparison_result.note_to_receiver_eq = 'N') THEN
919 l_prompt := FND_MESSAGE.get_string('PO', 'POS_NOTE_TO_RECEIVER');
920
921 l_po_terms_val := SUBSTRB(p_terms_rec1.note_to_receiver, 1, 200);
922 l_ref_terms_val := SUBSTRB(p_terms_rec2.note_to_receiver, 1, 200);
923
924 set_terms_token (p_prompt => l_prompt,
925 p_po_terms_val => l_po_terms_val,
926 p_ref_terms_val => l_ref_terms_val,
927 x_po_terms_list => l_po_terms_list,
928 x_ref_terms_list => l_ref_terms_list);
929 END IF;
930
931 IF (p_comparison_result.shipping_control_eq = 'N') THEN
932 l_prompt := FND_MESSAGE.get_string('PO', 'POS_SHIPPING_CONTROL');
933
934 PO_CORE_S.get_displayed_value
935 ( x_lookup_type => 'SHIPPING CONTROL',
936 x_lookup_code => p_terms_rec1.shipping_control,
937 x_disp_value => l_po_terms_val
938 );
939
940 PO_CORE_S.get_displayed_value
941 ( x_lookup_type => 'SHIPPING CONTROL',
942 x_lookup_code => p_terms_rec2.shipping_control,
943 x_disp_value => l_ref_terms_val
944 );
945
946 set_terms_token (p_prompt => l_prompt,
947 p_po_terms_val => l_po_terms_val,
948 p_ref_terms_val => l_ref_terms_val,
949 x_po_terms_list => l_po_terms_list,
950 x_ref_terms_list => l_ref_terms_list);
951 END IF;
952 END IF; -- scope IN ('GLOBAL', 'ALL')
953
954 IF (p_comparison_scope IN (G_COMPARISON_SCOPE_LOCAL,
955 G_COMPARISON_SCOPE_ALL)) THEN
956
957 IF (p_comparison_result.pay_on_code_eq = 'N') THEN
958 l_prompt := FND_MESSAGE.get_string('PO', 'POS_PAY_ON');
959
960 PO_CORE_S.get_displayed_value
961 ( x_lookup_type => 'PAY ON CODE',
962 x_lookup_code => p_terms_rec1.pay_on_code,
963 x_disp_value => l_po_terms_val
964 );
965
966 PO_CORE_S.get_displayed_value
967 ( x_lookup_type => 'PAY ON CODE',
968 x_lookup_code => p_terms_rec2.pay_on_code,
969 x_disp_value => l_ref_terms_val
970 );
971
972 set_terms_token (p_prompt => l_prompt,
973 p_po_terms_val => l_po_terms_val,
974 p_ref_terms_val => l_ref_terms_val,
975 x_po_terms_list => l_po_terms_list,
976 x_ref_terms_list => l_ref_terms_list);
977 END IF;
978
979 IF (p_comparison_result.bill_to_location_id_eq = 'N') THEN
980 l_prompt := FND_MESSAGE.get_string('PO', 'POS_BILL_TO');
981
982 l_po_terms_val :=
983 PO_LOCATIONS_S.get_location_code
984 ( p_location_id => p_terms_rec1.bill_to_location_id
985 );
986
987 l_ref_terms_val :=
988 PO_LOCATIONS_S.get_location_code
989 ( p_location_id => p_terms_rec2.bill_to_location_id
990 );
991
992 set_terms_token (p_prompt => l_prompt,
993 p_po_terms_val => l_po_terms_val,
994 p_ref_terms_val => l_ref_terms_val,
995 x_po_terms_list => l_po_terms_list,
996 x_ref_terms_list => l_ref_terms_list);
997 END IF;
998
999 IF (p_comparison_result.ship_to_location_id_eq = 'N') THEN
1000 l_prompt := FND_MESSAGE.get_string('PO', 'POS_SHIP_TO');
1001
1002 l_po_terms_val :=
1003 PO_LOCATIONS_S.get_location_code
1004 ( p_location_id => p_terms_rec1.ship_to_location_id
1005 );
1006
1007 l_ref_terms_val :=
1008 PO_LOCATIONS_S.get_location_code
1009 ( p_location_id => p_terms_rec2.ship_to_location_id
1010 );
1011
1012 set_terms_token (p_prompt => l_prompt,
1013 p_po_terms_val => l_po_terms_val,
1014 p_ref_terms_val => l_ref_terms_val,
1015 x_po_terms_list => l_po_terms_list,
1016 x_ref_terms_list => l_ref_terms_list);
1017 END IF;
1018
1019 IF (p_comparison_result.ship_via_lookup_code_eq = 'N') THEN
1020 l_prompt := FND_MESSAGE.get_string('PO', 'POS_SHIP_VIA');
1021
1022 l_po_terms_val := p_terms_rec1.ship_via_lookup_code;
1023 l_ref_terms_val := p_terms_rec2.ship_via_lookup_code;
1024
1025 set_terms_token (p_prompt => l_prompt,
1026 p_po_terms_val => l_po_terms_val,
1027 p_ref_terms_val => l_ref_terms_val,
1028 x_po_terms_list => l_po_terms_list,
1029 x_ref_terms_list => l_ref_terms_list);
1030 END IF;
1031
1032 END IF; -- scope IN ('LOCAL', 'ALL')
1033
1034 FND_MESSAGE.set_name('PO', 'PO_PO_AND_REF_TERMS_MISMATCH');
1035
1036 FND_MESSAGE.set_token('DOC_TYPE', l_doc_type_token);
1037 FND_MESSAGE.set_token('PO_TERMS', l_po_terms_list);
1038 FND_MESSAGE.set_token('REF_DOC_TERMS', l_ref_terms_list);
1039
1040 EXCEPTION
1041 WHEN OTHERS THEN
1042 PO_MESSAGE_S.sql_error(l_api_name, '000', sqlcode);
1043 RAISE;
1044 END set_terms_comparison_msg;
1045
1046 -----------------------------------------------------------------------
1047 --Start of Comments
1048 --Name: set_terms_token
1049 --Pre-reqs:
1050 --Modifies:
1051 --Locks:
1052 --Function: Construct x_po_terms_list and x_ref_terms_list that
1053 -- list the terms and conditions for the PO and Referencing
1054 -- document
1055 --Parameters:
1056 --IN:
1057 --p_prompt
1058 -- Name of the terms
1059 --p_po_terms_val
1060 -- Value of the terms in PO
1061 --p_ref_terms_val
1062 -- Value of the terms in Referencing Document
1063 --IN OUT:
1064 --x_po_terms_list
1065 -- a String holding the terms information in PO
1066 --x_ref_terms_list
1067 -- a string holding the terms information in Referencing Document
1068 --OUT:
1069 --Returns:
1070 --Notes:
1071 --Testing:
1072 --End of Comments
1073 ------------------------------------------------------------------------
1074 PROCEDURE set_terms_token
1075 ( p_prompt IN VARCHAR2,
1076 p_po_terms_val IN VARCHAR2,
1077 p_ref_terms_val IN VARCHAR2,
1078 x_po_terms_list IN OUT NOCOPY VARCHAR2,
1079 x_ref_terms_list IN OUT NOCOPY VARCHAR2
1080 ) IS
1081
1082 l_api_name CONSTANT VARCHAR2(50) := 'set_terms_token';
1083 l_token_num VARCHAR2(2);
1084 l_colon VARCHAR2(3);
1085 l_line_break VARCHAR2(1);
1086 BEGIN
1087
1088 FND_MESSAGE.set_name ('PO', 'PO_ATTR_PROMPT_AND_VALUE');
1089 FND_MESSAGE.set_token ('ATTRIBUTE', p_prompt);
1090 FND_MESSAGE.set_token ('VALUE', p_po_terms_val);
1091 x_po_terms_list := x_po_terms_list || FND_MESSAGE.get;
1092
1093 FND_MESSAGE.set_name ('PO', 'PO_ATTR_PROMPT_AND_VALUE');
1094 FND_MESSAGE.set_token ('ATTRIBUTE', p_prompt);
1095 FND_MESSAGE.set_token ('VALUE', p_ref_terms_val);
1096 x_ref_terms_list := x_ref_terms_list || FND_MESSAGE.get;
1097
1098 EXCEPTION
1099 WHEN OTHERS THEN
1100 PO_MESSAGE_S.sql_error(l_api_name, '000', sqlcode);
1101 RAISE;
1102 END set_terms_token;
1103
1104 -- bug3225062 END
1105
1106 -- <GC FPJ END>
1107
1108 end PO_TERMS_SV;