[Home] [Help]
PACKAGE BODY: APPS.PO_HEADERS_SV6
Source
1 PACKAGE BODY PO_HEADERS_SV6 AS
2 /* $Header: POXPIV2B.pls 120.3 2007/10/31 12:50:27 adbharga ship $ */
3 /* Changed the file name to POXPIV2B.pls from POXPIVHB.pls since
4 the file was corrupted . checking in with new name */
5
6 /*================================================================
7
8 FUNCTION NAME: val_doc_num_uniqueness()
9
10 ==================================================================*/
11 FUNCTION val_doc_num_uniqueness(x_segment1 IN VARCHAR2,
12 X_rowid IN VARCHAR2,
13 X_type_lookup_code IN VARCHAR2)
14 RETURN BOOLEAN IS
15
16 x_progress varchar2(3) := null;
17 x_temp binary_integer := 0;
18
19 BEGIN
20 x_progress := '010';
21
22 /* check to see if the segment1 is unique in po_headers table */
23 --<Bug# 3425930 START>
24 if X_type_lookup_code in ('BLANKET', 'STANDARD') then
25 x_progress := '020';
26
27 SELECT count(*)
28 INTO x_temp
29 FROM po_headers
30 WHERE segment1 = x_segment1
31 AND type_lookup_code in ('BLANKET', 'CONTRACT', 'PLANNED', 'STANDARD')
32 AND (rowid <> x_rowid OR x_rowid is null);
33
34 elsif X_type_lookup_code = 'QUOTATION' then
35 x_progress := '030';
36
37 SELECT count(*)
38 INTO x_temp
39 FROM po_headers
40 WHERE segment1 = x_segment1
41 AND type_lookup_code = X_type_lookup_code
42 AND (rowid <> x_rowid OR x_rowid is null);
43 else
44 x_progress := '040';
45 -- Do what ? raise exception ??
46 end if;
47 --<Bug# 3425930 END>
48
49 IF x_temp = 0 THEN
50 RETURN TRUE; /* validation fails */
51 ELSE
52 RETURN FALSE; /* validation succeeds */
53 END IF;
54
55 EXCEPTION
56
57 WHEN others THEN
58 po_message_s.sql_error
59 ('val_doc_num_uniqueness', x_progress, sqlcode);
60 raise;
61 END val_doc_num_uniqueness;
62
63 /*================================================================
64
65 FUNCTION NAME: val_header_id_uniqueness()
66
67 ==================================================================*/
68 FUNCTION val_header_id_uniqueness(x_po_header_id IN NUMBER,
69 x_rowid IN VARCHAR2)
70 RETURN BOOLEAN
71 IS
72
73 x_progress varchar2(3) := null;
74 x_temp binary_integer := 0;
75
76 BEGIN
77 x_progress := '010';
78
79 /* check to see if the po_header_id already exists in po_headers table,
80 if so, FALSE, otherwise, TRUE */
81
82 SELECT COUNT(*)
83 INTO x_temp
84 FROM po_headers
85 WHERE po_header_id = x_po_header_id
86 AND (rowid <> x_rowid OR x_rowid is null);
87
88 IF x_temp = 0 THEN -- no duplicated po_header_id is found
89 RETURN TRUE;
90 ELSE
91 RETURN FALSE; /* validation fails */
92 END IF;
93
94 EXCEPTION
95 WHEN others THEN
96 po_message_s.sql_error('val_header_id_uniqueness', x_progress,sqlcode);
97 raise;
98 END val_header_id_uniqueness;
99
100 /*================================================================
101
102 FUNCTION NAME: val_rate_info()
103
104 ==================================================================*/
105 FUNCTION val_rate_info(X_base_currency_code IN VARCHAR2,
106 X_currency_code IN VARCHAR2,
107 X_rate_type_code IN VARCHAR2,
108 X_rate_date IN DATE,
109 X_rate IN NUMBER,
110 X_error_code IN OUT NOCOPY VARCHAR2)
111 RETURN BOOLEAN
112 IS
113
114 x_progress varchar2(3) := null;
115 X_temp_val boolean;
116 X_temp_rate number;
117 x_set_of_books_id number; -- Bug: 2500843
118 x_res_display_rate number := null; -- Bug: 2500843
119
120 BEGIN
121 x_progress := '010';
122
123 IF (nvl(X_base_currency_code,' ') = nvl(X_currency_code,' ')) THEN
124 /* make sure all the following columns are NULL */
125
126 IF (X_rate_type_code IS NOT NULL ) THEN
127 X_error_code := 'PO_PDOI_RATE_INFO_NULL';
128 RETURN FALSE; /* validation fails */
129 END IF;
130
131 x_progress := '020';
132
133 IF (X_rate_date IS NOT NULL ) THEN
134 X_error_code := 'PO_PDOI_RATE_INFO_NULL';
135 RETURN FALSE;
136 END IF;
137
138 x_progress := '030';
139 IF (X_rate IS NOT NULL ) THEN
140 X_error_code := 'PO_PDOI_RATE_INFO_NULL';
141 RETURN FALSE;
142 END IF;
143
144 ELSIF (nvl(X_base_currency_code,' ') <> nvl(X_currency_code,' ')) THEN
145 x_progress := '040';
146 IF (X_rate_type_code is not null ) THEN
147 X_temp_val := po_daily_conversion_types_sv1.val_rate_type_code(
148 x_rate_type_code);
149 IF (X_temp_val = FALSE) THEN
150 X_error_code := 'PO_PDOI_INVALID_RATE_TYPE';
151 RETURN FALSE;
152 END IF;
153 END IF;
154
155 x_progress := '050';
156 IF (X_rate is null) THEN
157 X_error_code := 'PO_PDOI_COLUMN_NOT_NULL';
158 RETURN FALSE;
159 END IF;
160
161 IF (X_rate < 0) THEN
162 X_error_code := 'PO_PDOI_LT_ZERO';
163 RETURN FALSE;
164 END IF;
165
166 X_progress := '060';
167 /* X_rate need to be not null if rate_type_code = 'User'
168 X_rate need to be the same as the conversion rate
169 if the rate_type_code <> 'User'
170 */
171
172 IF (X_rate_type_code is not null) AND
173 (X_rate_type_code <> 'User') THEN
174 /* get the conversion_rate for gl_daily_conversion_rates
175 table based on the value passed from the parameter */
176
177 X_progress := '070';
178
179
180 BEGIN
181
182 /* Bug: 2500843 Get the SOB id and then call po_currency_sv.get_rate instead of calling GL api
183 directly
184 */
185 /*
186 x_temp_rate := gl_currency_api.get_rate (X_currency_code,
187 X_base_currency_code,
188 X_rate_date,
189 X_rate_type_code);
190 */
191
192
193 SELECT fsp.set_of_books_id
194 INTO X_set_of_books_id
195 FROM financials_system_parameters fsp,
196 gl_sets_of_books sob
197 WHERE fsp.set_of_books_id = sob.set_of_books_id;
198
199 po_currency_sv.get_rate(X_set_of_books_id,
200 X_currency_code,
201 X_rate_type_code,
202 X_rate_date,
203 'N', /* inverse_rate_display_flag */
204 x_temp_rate,
205 x_res_display_rate);
206
207 /* Bug 1769714
208 ** Added round to x_temp_rate.
209 ** The issue is that here we are calling the gl api directly
210 ** whereas in default_po_headers we are calling
211 ** po_currency_sv.get_rate which rounds the resultant rate.
212 ** Later when we compare the two rates from the two functions,
213 ** they might not compare since one is rounded the other isn't.
214 */
215
216
217 EXCEPTION
218 WHEN OTHERS THEN x_temp_rate := 0;
219
220 END;
221
222 /* rate has to be same with conversion_rate from
223 gl_daily_conversion_rates
224 table */
225
226 IF (nvl(X_temp_rate,0) <> nvl(X_rate,0)) THEN
227 X_error_code := 'PO_PDOI_INVALID_RATE';
228 RETURN FALSE;
229 END IF;
230 END IF;
231 END IF;
232
233 RETURN TRUE;
234
235 EXCEPTION
236 WHEN no_data_found THEN
237 X_error_code := 'PO_PDOI_NO_DATA_FOUND';
238 RETURN FALSE;
239 WHEN others THEN
240 po_message_s.sql_error('val_rate_info', x_progress,sqlcode);
241 raise;
242 END val_rate_info;
243
244 /*================================================================
245
246 FUNCTION NAME: val_doc_num()
247
248 ==================================================================*/
249 FUNCTION val_doc_num(X_doc_type IN VARCHAR2,
250 X_doc_num IN VARCHAR2,
251 X_user_defined_num IN VARCHAR2,
252 X_user_defined_po_num_code IN VARCHAR2,
253 X_error_code IN OUT NOCOPY VARCHAR2 )
254 RETURN BOOLEAN
255 IS
256
257 x_progress varchar2(3) := null;
258 X_temp_val boolean;
259
260 BEGIN
261 x_progress := '010';
262
263 /* check to see if doc_num is unique */
264
265 X_temp_val := val_doc_num_uniqueness(X_doc_num,
266 null,
267 X_doc_type);
268 IF (X_temp_val = FALSE) THEN
269 X_error_code := 'PO_PDOI_DOC_NUM_UNIQUE';
270 RETURN FALSE; /* the document_num is not unique */
271 END IF;
272
273 --frkhan add standard
274 IF (X_doc_type in ('QUOTATION', 'BLANKET', 'STANDARD')) THEN
275
276 if (X_doc_type = 'QUOTATION' and X_user_defined_num = 'NUMERIC')
277 OR
278 (X_doc_type in ('BLANKET','STANDARD') and X_user_defined_po_num_code = 'NUMERIC') then
279
280 x_progress := '020';
281 X_temp_val := po_core_sv1.val_numeric_value(X_doc_num);
282 IF (X_temp_val = FALSE) THEN
283 X_error_code := 'PO_PDOI_VALUE_NUMERIC';
284 RETURN FALSE; /* document_num is not numeric */
285 END IF;
286
287 IF (X_doc_num <0 ) THEN
288 X_error_code := 'PO_PDOI_LT_ZERO';
289 RETURN FALSE;
290 ELSE
291 RETURN TRUE;
292 END IF;
293 END IF;
294
295 ELSE
296 X_progress := '030';
297 X_error_code := 'PO_PDOI_INVALID_TYPE_LKUP_CD';
298 RETURN FALSE;
299 END IF;
300
301 RETURN TRUE;
302
303 EXCEPTION
304 WHEN others THEN
305 po_message_s.sql_error('val_doc_num', x_progress,sqlcode);
306 raise;
307 END val_doc_num;
308
309 /*================================================================
310
311 FUNCTION NAME: val_lookup_code()
312
313 ==================================================================*/
314 FUNCTION val_lookup_code (X_lookup_code IN VARCHAR2,
315 X_lookup_type IN VARCHAR2)
316 RETURN BOOLEAN
317 IS
318
319 x_progress varchar2(3) := null;
320 x_temp binary_integer := 0;
321
322 BEGIN
323 x_progress := '010';
324 /* check to see if the given lookup_code and type is valid */
325 SELECT count(*)
326 INTO x_temp
327 FROM po_lookup_codes
328 WHERE lookup_type = X_lookup_type
329 AND sysdate < nvl(inactive_date, sysdate+1)
330 AND lookup_code = X_lookup_code;
331
332 IF x_temp = 0 THEN
333 RETURN FALSE; /* validation fails */
334 ELSE
335 RETURN TRUE; /* validation succeeds */
336 END IF;
337 EXCEPTION
338 WHEN others THEN
339 po_message_s.sql_error('val_lookup_code', x_progress, sqlcode);
340 raise;
341 END val_lookup_code;
342
343
344 /*================================================================
345
346 PROCEDURE NAME: validate_po_headers()
347
348 ==================================================================*/
349 PROCEDURE validate_po_headers(
350 X_PO_HEADER_ID IN NUMBER,
351 X_AGENT_ID IN NUMBER,
352 X_TYPE_LOOKUP_CODE IN VARCHAR2,
353 X_LAST_UPDATE_DATE IN DATE,
354 X_LAST_UPDATED_BY IN NUMBER,
355 X_SEGMENT1 IN VARCHAR2,
356 X_SUMMARY_FLAG IN VARCHAR2,
357 X_ENABLED_FLAG IN VARCHAR2,
358 X_SEGMENT2 IN VARCHAR2,
359 X_SEGMENT3 IN VARCHAR2,
360 X_SEGMENT4 IN VARCHAR2,
361 X_SEGMENT5 IN VARCHAR2,
362 X_START_DATE_ACTIVE IN DATE,
363 X_END_DATE_ACTIVE IN DATE,
364 X_LAST_UPDATE_LOGIN IN NUMBER,
365 X_CREATION_DATE IN DATE,
366 X_CREATED_BY IN NUMBER,
367 X_VENDOR_ID IN NUMBER,
368 X_VENDOR_SITE_ID IN NUMBER,
369 X_VENDOR_CONTACT_ID IN NUMBER,
370 X_SHIP_TO_LOCATION_ID IN NUMBER,
371 X_BILL_TO_LOCATION_ID IN NUMBER,
372 X_TERMS_ID IN NUMBER,
373 X_SHIP_VIA_LOOKUP_CODE IN VARCHAR2,
374 X_FOB_LOOKUP_CODE IN VARCHAR2,
375 X_FREIGHT_TERMS_LOOKUP_CODE IN VARCHAR2,
376 X_STATUS_LOOKUP_CODE IN VARCHAR2,
377 X_CURRENCY_CODE IN VARCHAR2,
378 X_RATE_TYPE IN VARCHAR2,
379 X_RATE_DATE IN DATE,
380 X_RATE IN NUMBER,
381 X_FROM_HEADER_ID IN NUMBER,
382 X_FROM_TYPE_LOOKUP_CODE IN VARCHAR2,
383 X_START_DATE IN DATE,
384 X_END_DATE IN DATE,
385 X_BLANKET_TOTAL_AMOUNT IN NUMBER,
386 X_AUTHORIZATION_STATUS IN VARCHAR2,
387 X_REVISION_NUM IN NUMBER,
388 -- Bug 902976, zxzhang, 10/04/99
389 -- Change REVISED_DATE from VarChar(25) to Date.
390 -- X_REVISED_DATE IN VARCHAR2,
391 X_REVISED_DATE IN DATE,
392 X_APPROVED_FLAG IN VARCHAR2,
393 X_APPROVED_DATE IN DATE,
394 X_AMOUNT_LIMIT IN NUMBER,
395 X_MIN_RELEASE_AMOUNT IN NUMBER,
396 X_NOTE_TO_AUTHORIZER IN VARCHAR2,
397 X_NOTE_TO_VENDOR IN VARCHAR2,
398 X_NOTE_TO_RECEIVER IN VARCHAR2,
399 X_PRINT_COUNT IN NUMBER,
400 X_PRINTED_DATE IN DATE,
401 X_VENDOR_ORDER_NUM IN VARCHAR2,
402 X_CONFIRMING_ORDER_FLAG IN VARCHAR2,
403 X_COMMENTS IN VARCHAR2,
404 X_REPLY_DATE IN DATE,
405 X_REPLY_METHOD_LOOKUP_CODE IN VARCHAR2,
406 X_RFQ_CLOSE_DATE IN DATE,
407 X_QUOTE_TYPE_LOOKUP_CODE IN VARCHAR2,
408 X_QUOTATION_CLASS_CODE IN VARCHAR2,
409 X_QUOTE_WARNING_DELAY IN NUMBER,
410 X_QUOTE_VENDOR_QUOTE_NUM IN VARCHAR2,
411 X_ACCEPTANCE_REQUIRED_FLAG IN VARCHAR2,
412 X_ACCEPTANCE_DUE_DATE IN DATE,
413 X_CLOSED_DATE IN DATE,
414 X_USER_HOLD_FLAG IN VARCHAR2,
415 X_APPROVAL_REQUIRED_FLAG IN VARCHAR2,
416 X_CANCEL_FLAG IN VARCHAR2,
417 X_FIRM_STATUS_LOOKUP_CODE IN VARCHAR2,
418 X_FIRM_DATE IN DATE,
419 X_FROZEN_FLAG IN VARCHAR2,
420 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
421 X_ATTRIBUTE1 IN VARCHAR2,
422 X_ATTRIBUTE2 IN VARCHAR2,
423 X_ATTRIBUTE3 IN VARCHAR2,
424 X_ATTRIBUTE4 IN VARCHAR2,
425 X_ATTRIBUTE5 IN VARCHAR2,
426 X_ATTRIBUTE6 IN VARCHAR2,
427 X_ATTRIBUTE7 IN VARCHAR2,
428 X_ATTRIBUTE8 IN VARCHAR2,
429 X_ATTRIBUTE9 IN VARCHAR2,
430 X_ATTRIBUTE10 IN VARCHAR2,
431 X_ATTRIBUTE11 IN VARCHAR2,
432 X_ATTRIBUTE12 IN VARCHAR2,
433 X_ATTRIBUTE13 IN VARCHAR2,
434 X_ATTRIBUTE14 IN VARCHAR2,
435 X_ATTRIBUTE15 IN VARCHAR2,
436 X_CLOSED_CODE IN VARCHAR2,
437 X_USSGL_TRANSACTION_CODE IN VARCHAR2,
438 X_GOVERNMENT_CONTEXT IN VARCHAR2,
439 X_REQUEST_ID IN NUMBER,
440 X_PROGRAM_APPLICATION_ID IN NUMBER,
441 X_PROGRAM_ID IN NUMBER,
442 X_PROGRAM_UPDATE_DATE IN DATE,
443 X_INTERFACE_SOURCE_CODE IN VARCHAR2,
444 X_INTERFACE_HEADER_ID IN NUMBER,
445 X_REFERENCE_NUM IN VARCHAR2,
446 X_ORG_ID IN NUMBER,
447 X_QUOTE_WARNING_DELAY_UNIT IN VARCHAR2,
448 X_APPROVAL_STATUS IN VARCHAR2,
449 X_release_num IN NUMBER,
450 X_po_release_id IN NUMBER,
451 X_release_date IN DATE,
452 X_manual_quote_num_type IN VARCHAR2,
453 X_manual_po_num_type IN VARCHAR2,
454 X_amount_agreed IN NUMBER,
455 X_base_currency_code IN VARCHAR2,
456 X_chart_of_accounts_id IN NUMBER,
457 X_def_inv_org_id IN NUMBER,
458 X_header_processable_flag IN OUT NOCOPY VARCHAR2,
459 X_action_code IN VARCHAR2,
460 p_shipping_control IN VARCHAR2 -- <INBOUND LOGISTICS FPJ>
461 )
462 IS
463 X_progress VARCHAR2(3) := NULL;
464 X_temp_val BOOLEAN;
465 x_res_carrier VARCHAR2(25) := null;
466 x_res_fob varchar2(25) := null;
467 x_res_freight varchar2(25) := null;
468 x_res_terms number := null;
469 X_interface_line_id number := null;
470 X_error_code varchar2(30) := null;
471 X_temp_count binary_integer;
472 l_res_shipping_control PO_LOOKUP_CODES.lookup_code%TYPE := NULL; -- <INBOUND LOGISTICS FPJ>
473
474 BEGIN
475 X_progress := '010';
476
477 IF (x_type_lookup_code is null) THEN
478 po_interface_errors_sv1.handle_interface_errors(
479 'PO_DOCS_OPEN_INTERFACE',
480 'FATAL',
481 null,
482 X_interface_header_id,
483 X_interface_line_id,
484 'PO_PDOI_COLUMN_NOT_NULL',
485 'PO_HEADERS_INTERAFCE',
486 'TYPE_LOOKUP_CODE',
487 'COLUMN_NAME',
488 null,null,null,null,null,
489 'TYPE_LOOKUP_CODE',
490 null,null,null,null,null,
491 x_header_processable_flag);
492 END IF;
493
494 X_progress := '011';
495 --frkhan add standard
496 IF (x_type_lookup_code not in ('QUOTATION', 'BLANKET', 'STANDARD')) THEN
497 po_interface_errors_sv1.handle_interface_errors(
498 'PO_DOCS_OPEN_INTERFACE',
499 'FATAL',
500 null,
501 X_interface_header_id,
502 X_interface_line_id,
503 'PO_PDOI_INVALID_TYPE_LKUP_CD',
504 'PO_HEADERS_INTERAFCE',
505 'TYPE_LOOKUP_CODE',
506 'VALUE',
507 null,null,null,null,null,
508 x_type_lookup_code,
509 null,null,null,null,null,
510 x_header_processable_flag);
511 END IF;
512
513 X_progress := '020';
514 IF (x_type_lookup_code = 'QUOTATION') THEN
515 BEGIN
516 SELECT count(*)
517 INTO x_temp_count
518 FROM po_document_types
519 WHERE document_type_code = X_type_lookup_code
520 AND document_subtype = x_quote_type_lookup_code;
521
522 IF (x_temp_count = 0) THEN
523 po_interface_errors_sv1.handle_interface_errors(
524 'PO_DOCS_OPEN_INTERFACE',
525 'FATAL',
526 null,
527 X_interface_header_id,
528 X_interface_line_id,
529 'PO_PDOI_INVALID_QUOTE_TYPE_CD',
530 'PO_HEADERS_INTERFACE',
531 'QUOTE_TYPE_LOOKUP_CODE',
532 'VALUE',
533 null,null,null,null,null,
534 X_quote_type_lookup_code,
535 null,null,null,null,null,
536 x_header_processable_flag);
537 END IF;
538 END;
539
540 /* All the following columns are mandatory */
541 X_progress := '030';
542 If (X_quote_warning_delay IS NULL ) THEN
543 po_interface_errors_sv1.handle_interface_errors(
544 'PO_DOCS_OPEN_INTERFACE',
545 'FATAL',
546 null,
547 X_interface_header_id,
548 X_interface_line_id,
549 'PO_PDOI_COLUMN_NOT_NULL',
550 'PO_HEADERS_INTERFACE',
551 'QUOTE_WARNING_DELAY',
552 'COLUMN_NAME',
553 null,null,null,null,null,
554 'QUOTE_WARNING_DELAY',
555 null,null,null,null,null,
556 x_header_processable_flag);
557 END IF;
558
559 X_progress := '040';
560 IF (X_approval_required_flag IS NULL ) THEN
561 po_interface_errors_sv1.handle_interface_errors(
562 'PO_DOCS_OPEN_INTERFACE',
563 'FATAL',
564 null,
565 X_interface_header_id,
566 X_interface_line_id,
567 'PO_PDOI_COLUMN_NOT_NULL',
568 'PO_HEADERS_INTERFACE',
569 'APPROVAL_REQUIRED_FLAG',
570 'COLUMN_NAME',
571 null,null,null,null,null,
572 'APPROVAL_REQUIRED_FLAG',
573 null,null,null,null,null,
574 x_header_processable_flag);
575 END IF;
576
577 X_progress := '050';
578 IF (X_created_by IS NULL ) THEN
579 po_interface_errors_sv1.handle_interface_errors(
580 'PO_DOCS_OPEN_INTERFACE',
581 'FATAL',
582 null,
583 X_interface_header_id,
584 X_interface_line_id,
585 'PO_PDOI_COLUMN_NOT_NULL',
586 'PO_HEADERS_INTERFACE',
587 'CREATED_BY',
588 'COLUMN_NAME',
589 null,null,null,null,null,
590 'CREATED_BY',
591 null,null,null,null,null,
592 x_header_processable_flag);
593 END IF;
594
595 X_progress := '060';
596 IF (X_creation_date IS NULL ) THEN
597 po_interface_errors_sv1.handle_interface_errors(
598 'PO_DOCS_OPEN_INTERFACE',
599 'FATAL',
600 null,
601 X_interface_header_id,
602 X_interface_line_id,
603 'PO_PDOI_COLUMN_NOT_NULL',
604 'PO_HEADERS_INTERFACE',
605 'CREATION_DATE',
606 'COLUMN_NAME',
607 null,null,null,null,null,
608 'CREATION_DATE',
609 null,null,null,null,null,
610 x_header_processable_flag);
611 END IF;
612
613 X_progress := '070';
614 --frkhan add standard
615 ELSIF (X_type_lookup_code in ('BLANKET','STANDARD')) THEN
616 IF (X_quote_type_lookup_code is not null) THEN
617 po_interface_errors_sv1.handle_interface_errors(
618 'PO_DOCS_OPEN_INTERFACE',
619 'FATAL',
620 null,
621 X_interface_header_id,
622 X_interface_line_id,
623 'PO_PDOI_COLUMN_NULL',
624 'PO_HEADERS_INTERFACE',
625 'DOCUMENT_SUBTYPE',
626 'COLUMN_NAME',
627 'VALUE',
628 null,null,null,null,
629 'DOCUMENT_SUBTYPE',
630 X_quote_type_lookup_code,
631 null,null,null,null,
632 x_header_processable_flag);
633
634 END IF;
635
636 X_progress := '075';
637 IF (X_acceptance_required_flag IS NULL ) THEN
638 po_interface_errors_sv1.handle_interface_errors(
639 'PO_DOCS_OPEN_INTERFACE',
640 'FATAL',
641 null,
642 X_interface_header_id,
643 X_interface_line_id,
644 'PO_PDOI_COLUMN_NOT_NULL',
645 'PO_HEADERS_INTERFACE',
646 'ACCEPTANCE_REQUIRED_FLAG',
647 'COLUMN_NAME',
648 null,null,null,null,null,
649 'ACCEPTANCE_REQUIRED_FLAG',
650 null,null,null,null,null,
651 x_header_processable_flag);
652
653 END IF;
654 X_progress := '080';
655
656 --
657 -- Do not need this check for a "UPDATE" action. Blanket can have non-zero revision number.
658 --
659
660 IF (X_action_code <> 'UPDATE' AND X_revision_num <> 0) THEN
661 po_interface_errors_sv1.handle_interface_errors(
662 'PO_DOCS_OPEN_INTERFACE',
663 'FATAL',
664 null,
665 X_interface_header_id,
666 X_interface_line_id,
667 'PO_PDOI_COLUMN_NOT_ZERO',
668 'PO_HEADERS_INTERFACE',
669 'REVISION_NUM',
670 'COLUMN_NAME',
671 null,null,null,null,null,
672 'REVISION_NUM',
673 null,null,null,null,null,
674 x_header_processable_flag);
675
676 END IF;
677 END IF;
678
679 X_progress := '090';
680 --frkhan add standard
681 IF ( X_type_lookup_code IN ('QUOTATION', 'BLANKET', 'STANDARD')) THEN
682 /*** validations common to both blanket/standard and quote ***/
683 IF (X_segment1 IS NULL ) THEN
684 po_interface_errors_sv1.handle_interface_errors(
685 'PO_DOCS_OPEN_INTERFACE',
686 'FATAL',
687 null,
688 X_interface_header_id,
689 X_interface_line_id,
690 'PO_PDOI_COLUMN_NOT_NULL',
691 'PO_HEADERS_INTERFACE',
692 'DOCUMENT_NUM',
693 'COLUMN_NAME',
694 null,null,null,null,null,
695 'DOCUMENT_NUM',
696 null,null,null,null,null,
697 x_header_processable_flag);
698
699 END IF;
700 X_progress := '100';
701 IF (X_po_header_id IS NULL ) THEN
702 po_interface_errors_sv1.handle_interface_errors(
703 'PO_DOCS_OPEN_INTERFACE',
704 'FATAL',
705 null,
706 X_interface_header_id,
707 X_interface_line_id,
708 'PO_PDOI_COLUMN_NOT_NULL',
709 'PO_HEADERS_INTERFACE',
710 'PO_HEADER_ID',
711 'COLUMN_NAME',
712 null,null,null,null,null,
713 'PO_HEADER_ID',
714 null,null,null,null,null,
715 x_header_processable_flag);
716
717 END IF;
718
719 X_progress := '110';
720 IF (X_currency_code IS NULL ) THEN
721 po_interface_errors_sv1.handle_interface_errors(
722 'PO_DOCS_OPEN_INTERFACE',
723 'FATAL',
724 null,
725 X_interface_header_id,
726 X_interface_line_id,
727 'PO_PDOI_COLUMN_NOT_NULL',
728 'PO_HEADERS_INTERFACE',
729 'CURRENCY_CODE',
730 'COLUMN_NAME',
731 null,null,null,null,null,
732 'CURRENCY_CODE',
733 null,null,null,null,null,
734 x_header_processable_flag);
735
736 END IF;
737
738 X_progress := '120';
739 IF (X_agent_id IS NULL ) THEN
740 po_interface_errors_sv1.handle_interface_errors(
741 'PO_DOCS_OPEN_INTERFACE',
742 'FATAL',
743 null,
744 X_interface_header_id,
745 X_interface_line_id,
746 'PO_PDOI_COLUMN_NOT_NULL',
747 'PO_HEADERS_INTERFACE',
748 'AGENT_ID',
749 'COLUMN_NAME',
750 null,null,null,null,null,
751 'AGENT_ID',
752 null,null,null,null,null,
753 x_header_processable_flag);
754
755 END IF;
756
757 X_progress := '130';
758 IF (X_vendor_id IS NULL ) THEN
759 po_interface_errors_sv1.handle_interface_errors(
760 'PO_DOCS_OPEN_INTERFACE',
761 'FATAL',
762 null,
763 X_interface_header_id,
764 X_interface_line_id,
765 'PO_PDOI_COLUMN_NOT_NULL',
766 'PO_HEADERS_INTERFACE',
767 'VENDOR_ID',
768 'COLUMN_NAME',
769 null,null,null,null,null,
770 'VENDOR_ID',
771 null,null,null,null,null,
772 x_header_processable_flag);
773
774 END IF;
775
776 X_progress := '140';
777 /* Bug 1541387: For the corresponding enhancement request, now allowing
778 Quotations to be loaded without the vendor_site_code and hence moving the
779 following piece of code to validations meant for Blankets only.
780
781 IF (X_vendor_site_id IS NULL ) THEN
782 po_interface_errors_sv1.handle_interface_errors(
783 'PO_DOCS_OPEN_INTERFACE',
784 'FATAL',
785 null,
786 X_interface_header_id,
787 X_interface_line_id,
788 'PO_PDOI_COLUMN_NOT_NULL',
789 'PO_HEADERS_INTERFACE',
790 'VENDOR_SITE_ID',
791 'COLUMN_NAME',
792 null,null,null,null,null,
793 'VENDOR_SITE_ID',
794 null,null,null,null,null,
795 x_header_processable_flag);
796
797 END IF;
798 */
799 X_progress := '143';
800 IF (X_ship_to_location_id IS NULL) THEN
801 po_interface_errors_sv1.handle_interface_errors(
802 'PO_DOCS_OPEN_INTERFACE',
803 'FATAL',
804 null,
805 X_interface_header_id,
806 X_interface_line_id,
807 'PO_PDOI_COLUMN_NOT_NULL',
808 'PO_HEADERS_INTERFACE',
809 'SHIP_TO_LOCATION_ID',
810 'COLUMN_NAME',
811 null,null,null,null,null,
812 'SHIP_TO_LOCATION_ID',
813 null,null,null,null,null,
814 x_header_processable_flag);
815
816 END IF;
817
818
819 X_progress := '145';
820 IF (X_bill_to_location_id IS NULL) THEN
821 po_interface_errors_sv1.handle_interface_errors(
822 'PO_DOCS_OPEN_INTERFACE',
823 'FATAL',
824 null,
825 X_interface_header_id,
826 X_interface_line_id,
827 'PO_PDOI_COLUMN_NOT_NULL',
828 'PO_HEADERS_INTERFACE',
829 'BILL_TO_LOCATION_ID',
830 'COLUMN_NAME',
831 null,null,null,null,null,
832 'BILL_TO_LOCATION_ID',
833 null,null,null,null,null,
834 x_header_processable_flag);
835
836 END IF;
837
838
839 X_progress := '150';
840 IF (X_last_updated_by IS NULL ) THEN
841 po_interface_errors_sv1.handle_interface_errors(
842 'PO_DOCS_OPEN_INTERFACE',
843 'FATAL',
844 null,
845 X_interface_header_id,
846 X_interface_line_id,
847 'PO_PDOI_COLUMN_NOT_NULL',
848 'PO_HEADERS_INTERFACE',
849 'LAST_UPDATED_BY',
850 'COLUMN_NAME',
851 null,null,null,null,null,
852 'LAST_UPDATED_BY',
853 null,null,null,null,null,
854 x_header_processable_flag);
855
856 END IF;
857
858 X_progress := '160';
859 IF (X_last_update_date IS NULL ) THEN
860 po_interface_errors_sv1.handle_interface_errors(
861 'PO_DOCS_OPEN_INTERFACE',
862 'FATAL',
863 null,
864 X_interface_header_id,
865 X_interface_line_id,
866 'PO_PDOI_COLUMN_NOT_NULL',
867 'PO_HEADERS_INTERFACE',
868 'LAST_UPDATE_DATE',
869 'COLUMN_NAME',
870 null,null,null,null,null,
871 'LAST_UPDATE_DATE',
872 null,null,null,null,null,
873 x_header_processable_flag);
874
875 END IF;
876
877 X_progress := '170';
878 /*** The following columns have to be NULL for both blankets
879 and quotes ****/
880
881 IF (X_release_num IS NOT NULL ) THEN
882 po_interface_errors_sv1.handle_interface_errors(
883 'PO_DOCS_OPEN_INTERFACE',
884 'FATAL',
885 null,
886 X_interface_header_id,
887 X_interface_line_id,
888 'PO_PDOI_COLUMN_NULL',
889 'PO_HEADERS_INTERFACE',
890 'RELEASE_NUM',
891 'COLUMN_NAME',
892 'VALUE',
893 null,null,null,null,
894 'RELEASE_NUM',
895 X_release_num,
896 null,null,null,null,
897 x_header_processable_flag);
898
899 END IF;
900
901 X_progress := '180';
902 IF (X_po_release_id IS NOT NULL ) THEN
903 po_interface_errors_sv1.handle_interface_errors(
904 'PO_DOCS_OPEN_INTERFACE',
905 'FATAL',
906 null,
907 X_interface_header_id,
908 X_interface_line_id,
909 'PO_PDOI_COLUMN_NULL',
910 'PO_HEADERS_INTERFACE',
911 'PO_RELEASE_ID',
912 'COLUMN_NAME',
913 'VALUE',
914 null,null,null,null,
915 'PO_RELEASE_ID',
916 X_po_release_id,
917 null,null,null,null,
918 x_header_processable_flag);
919
920 END IF;
921
922 X_progress := '190';
923 IF (X_release_date IS NOT NULL ) THEN
924 po_interface_errors_sv1.handle_interface_errors(
925 'PO_DOCS_OPEN_INTERFACE',
926 'FATAL',
927 null,
928 X_interface_header_id,
929 X_interface_line_id,
930 'PO_PDOI_COLUMN_NULL',
931 'PO_HEADERS_INTERFACE',
932 'RELEASE_DATE',
933 'COLUMN_NAME',
934 'VALUE',
935 null,null,null,null,
936 'RELEASE_DATE',
937 X_release_date,
938 null,null,null,null,
939 x_header_processable_flag);
940
941 END IF;
942
943 X_progress := '200';
944 IF (X_revised_date IS NOT NULL ) THEN
945 po_interface_errors_sv1.handle_interface_errors(
946 'PO_DOCS_OPEN_INTERFACE',
947 'FATAL',
948 null,
949 X_interface_header_id,
950 X_interface_line_id,
951 'PO_PDOI_COLUMN_NULL',
952 'PO_HEADERS_INTERFACE',
953 'REVISED_DATE',
954 'COLUMN_NAME',
955 'VALUE',
956 null,null,null,null,
957 'REVISED_DATE',
958 X_revised_date,
959 null,null,null,null,
960 x_header_processable_flag);
961
962 /* gtummala. 6/12/97
963 * There was an "END IF" missing here so I added it.
964 */
965
966 END IF;
967
968 X_progress := '215';
969 IF (X_user_hold_flag IS NOT NULL ) THEN
970 po_interface_errors_sv1.handle_interface_errors(
971 'PO_DOCS_OPEN_INTERFACE',
972 'FATAL',
973 null,
974 X_interface_header_id,
975 X_interface_line_id,
976 'PO_PDOI_COLUMN_NULL',
977 'PO_HEADERS_INTERFACE',
978 'USER_HOLD_FLAG',
979 'COLUMN_NAME',
980 'VALUE',
981 null,null,null,null,
982 'USER_HOLD_FLAG',
983 X_user_hold_flag,
984 null,null,null,null,
985 x_header_processable_flag);
986
987 END IF;
988
989 X_progress := '217';
990 IF (X_printed_date IS NOT NULL ) THEN
991 po_interface_errors_sv1.handle_interface_errors(
992 'PO_DOCS_OPEN_INTERFACE',
993 'FATAL',
994 null,
995 X_interface_header_id,
996 X_interface_line_id,
997 'PO_PDOI_COLUMN_NULL',
998 'PO_HEADERS_INTERFACE',
999 'PRINTED_DATE',
1000 'COLUMN_NAME',
1001 'VALUE',
1002 null,null,null,null,
1003 'PRINTED_DATE',
1004 X_printed_date,
1005 null,null,null,null,
1006 x_header_processable_flag);
1007 END IF;
1008
1009 X_progress := '219';
1010 IF (X_closed_date IS NOT NULL ) THEN
1011 po_interface_errors_sv1.handle_interface_errors(
1012 'PO_DOCS_OPEN_INTERFACE',
1013 'FATAL',
1014 null,
1015 X_interface_header_id,
1016 X_interface_line_id,
1017 'PO_PDOI_COLUMN_NULL',
1018 'PO_HEADERS_INTERFACE',
1019 'CLOSED_DATE',
1020 'COLUMN_NAME',
1021 'VALUE',
1022 null,null,null,null,
1023 'CLOSED_DATE',
1024 X_closed_date,
1025 null,null,null,null,
1026 x_header_processable_flag);
1027 END IF;
1028
1029 END IF; /* X_type_lookup_code in ('QUOTATION', 'BLANKET') */
1030
1031 X_progress := '210';
1032 IF (X_po_header_id IS NOT NULL ) THEN
1033 X_temp_val := po_headers_sv6.val_header_id_uniqueness(X_po_header_id,
1034 null /* rowid */);
1035 IF ( X_temp_val = FALSE ) THEN
1036 po_interface_errors_sv1.handle_interface_errors(
1037 'PO_DOCS_OPEN_INTERFACE',
1038 'FATAL',
1039 null,
1040 X_interface_header_id,
1041 X_interface_line_id,
1042 'PO_PDOI_PO_HDR_ID_UNIQUE',
1043 'PO_HEADERS_INTERFACE',
1044 'PO_HEADER_ID',
1045 'VALUE',
1046 null,null,null,null,null,
1047 X_po_header_id,
1048 null,null,null,null, null,
1049 x_header_processable_flag);
1050
1051 END IF;
1052 END IF;
1053
1054 X_progress := '220';
1055
1056 -- Bug 679535 - skip validation for po number as the number generation is defered till commit time.
1057 -- Validation will only be skipped if no document number is provided in the flat file
1058
1059 IF (X_segment1 IS NOT NULL and X_segment1 <> 'POI_Temp_PO_b679535') THEN
1060
1061 X_temp_val := po_headers_sv6.val_doc_num(
1062 X_type_lookup_code,
1063 X_segment1,
1064 X_manual_quote_num_type,
1065 X_manual_po_num_type,
1066 X_error_code);
1067
1068 IF ( X_temp_val = FALSE ) THEN
1069 IF (X_error_code = 'PO_PDOI_DOC_NUM_UNIQUE') THEN
1070 po_interface_errors_sv1.handle_interface_errors(
1071 'PO_DOCS_OPEN_INTERFACE',
1072 'FATAL',
1073 null,
1074 X_interface_header_id,
1075 X_interface_line_id,
1076 X_error_code,
1077 'PO_HEADERS_INTERFACE',
1078 'DOCUMENT_NUM',
1079 'VALUE',
1080 null,null,null,null,null,
1081 X_segment1,
1082 null,null,null,null,null,
1083 x_header_processable_flag);
1084
1085 ELSIF (X_error_code = 'PO_PDOI_VALUE_NUMERIC') THEN
1086 po_interface_errors_sv1.handle_interface_errors(
1087 'PO_DOCS_OPEN_INTERFACE',
1088 'FATAL',
1089 null,
1090 X_interface_header_id,
1091 X_interface_line_id,
1092 X_error_code,
1093 'PO_HEADERS_INTERFACE',
1094 'DOCUMENT_NUM',
1095 'COLUMN_NAME',
1096 'VALUE',
1097 null,null,null,null,
1098 'DOCUMENT_NUM',
1099 X_segment1,
1100 null,null,null,null,
1101 x_header_processable_flag);
1102
1103 ELSIF (X_error_code = 'PO_PDOI_LT_ZERO') THEN
1104 po_interface_errors_sv1.handle_interface_errors(
1105 'PO_DOCS_OPEN_INTERFACE',
1106 'FATAL',
1107 null,
1108 X_interface_header_id,
1109 X_interface_line_id,
1110 X_error_code,
1111 'PO_HEADERS_INTERFACE',
1112 'DOCUMENT_NUM',
1113 'COLUMN_NAME',
1114 'VALUE',
1115 null,null,null,null,
1116 'DOCUMENT_NUM',
1117 X_segment1,
1118 null,null,null,null,
1119 x_header_processable_flag);
1120
1121 ELSIF (X_error_code = 'PO_PDOI_INVALID_TYPE_LKUP_CD') THEN
1122 po_interface_errors_sv1.handle_interface_errors(
1123 'PO_DOCS_OPEN_INTERFACE',
1124 'FATAL',
1125 null,
1126 X_interface_header_id,
1127 X_interface_line_id,
1128 X_error_code,
1129 'PO_HEADERS_INTERFACE',
1130 'TYPE_LOOKUP_CODE',
1131 'VALUE',
1132 null,null,null,null,null,
1133 X_type_lookup_code,
1134 null,null,null,null,null,
1135 x_header_processable_flag);
1136 END IF;
1137 END IF;
1138 END IF;
1139
1140 X_progress := '230';
1141 IF (X_currency_code IS NOT NULL ) THEN
1142 X_temp_val := po_currency_sv.val_currency(X_currency_code);
1143 IF ( X_temp_val = FALSE ) THEN
1144 po_interface_errors_sv1.handle_interface_errors(
1145 'PO_DOCS_OPEN_INTERFACE',
1146 'FATAL',
1147 null,
1148 X_interface_header_id,
1149 X_interface_line_id,
1150 'PO_PDOI_INVALID_CURRENCY',
1151 'PO_HEADERS_INTERFACE',
1152 'CURRENCY_CODE',
1153 'VALUE',
1154 null,null,null,null,null,
1155 X_currency_code,
1156 null,null,null,null,null,
1157 x_header_processable_flag);
1158 END IF;
1159 END IF;
1160
1161 X_progress := '240';
1162 IF (X_base_currency_code is not null) AND
1163 (x_currency_code is not null)
1164 THEN
1165 X_temp_val := po_headers_sv6.val_rate_info(X_base_currency_code,
1166 X_currency_code,
1167 X_rate_type,
1168 X_rate_date,
1169 X_rate,
1170 X_error_code);
1171 IF ( X_temp_val = FALSE ) THEN
1172 IF (X_error_code = 'PO_PDOI_NO_DATA_FOUND') THEN
1173 po_interface_errors_sv1.handle_interface_errors(
1174 'PO_DOCS_OPEN_INTERFACE',
1175 'FATAL',
1176 null,
1177 X_interface_header_id,
1178 X_interface_line_id,
1179 X_error_code,
1180 'PO_HEADERS_INTERFACE',
1181 'RATE',
1182 'CURRENCY',
1183 'RATE_TYPE',
1184 null,null,null,null,
1185 X_currency_code,
1186 X_rate_type,
1187 null,null,null,null,
1188 x_header_processable_flag);
1189
1190 ELSIF (X_error_code = 'PO_PDOI_RATE_INFO_NULL') THEN
1191 po_interface_errors_sv1.handle_interface_errors(
1192 'PO_DOCS_OPEN_INTERFACE',
1193 'FATAL',
1194 null,
1195 X_interface_header_id,
1196 X_interface_line_id,
1197 X_error_code,
1198 'PO_HEADERS_INTERFACE',
1199 NULL,
1200 null,null,null,null,null,null,
1201 null,
1202 null,null,null,null,null,
1203 x_header_processable_flag);
1204
1205 ELSIF (X_error_code = 'PO_PDOI_INVALID_RATE_TYPE') THEN
1206 po_interface_errors_sv1.handle_interface_errors(
1207 'PO_DOCS_OPEN_INTERFACE',
1208 'FATAL',
1209 null,
1210 X_interface_header_id,
1211 X_interface_line_id,
1212 X_error_code,
1213 'PO_HEADERS_INTERFACE',
1214 'RATE_TYPE' ,
1215 'VALUE',
1216 null,null,null,null,null,
1217 X_rate_type,
1218 null,null,null,null,null,
1219 x_header_processable_flag);
1220
1221 ELSIF (X_error_code = 'PO_PDOI_COLUMN_NOT_NULL') THEN
1222 po_interface_errors_sv1.handle_interface_errors(
1223 'PO_DOCS_OPEN_INTERFACE',
1224 'FATAL',
1225 null,
1226 X_interface_header_id,
1227 X_interface_line_id,
1228 X_error_code,
1229 'PO_HEADERS_INTERFACE',
1230 'RATE',
1231 'COLUMN_NAME',
1232 null,null,null,null,null,
1233 'RATE',
1234 null,null,null,null,null,
1235 x_header_processable_flag);
1236
1237 ELSIF (X_error_code = 'PO_PDOI_INVALID_RATE') THEN
1238 po_interface_errors_sv1.handle_interface_errors(
1239 'PO_DOCS_OPEN_INTERFACE',
1240 'FATAL',
1241 null,
1242 X_interface_header_id,
1243 X_interface_line_id,
1244 X_error_code,
1245 'PO_HEADERS_INTERFACE',
1246 'RATE',
1247 'VALUE',
1248 null,null,null,null,null,
1249 X_rate,
1250 null,null,null,null,null,
1251 x_header_processable_flag);
1252
1253 ELSIF (X_error_code = 'PO_PDOI_LT_ZERO') THEN
1254 po_interface_errors_sv1.handle_interface_errors(
1255 'PO_DOCS_OPEN_INTERFACE',
1256 'FATAL',
1257 null,
1258 X_interface_header_id,
1259 X_interface_line_id,
1260 X_error_code,
1261 'PO_HEADERS_INTERFACE',
1262 'RATE',
1263 'COLUMN_NAME',
1264 'VALUE',
1265 null,null,null,null,
1266 'RATE',
1267 X_rate,
1268 null,null,null,null,
1269 x_header_processable_flag);
1270
1271 END IF;
1272 END IF;
1273 END IF;
1274
1275 X_progress := '250';
1276 IF (X_agent_id IS NOT NULL ) THEN
1277 X_temp_val := po_agents_sv1.val_agent_id(x_agent_id);
1278 IF ( X_temp_val = FALSE ) THEN
1279 po_interface_errors_sv1.handle_interface_errors(
1280 'PO_DOCS_OPEN_INTERFACE',
1281 'FATAL',
1282 null,
1283 X_interface_header_id,
1284 X_interface_line_id,
1285 'PO_PDOI_INVALID_BUYER',
1286 'PO_HEADERS_INTERFACE',
1287 'AGENT_ID',
1288 'VALUE',
1289 null,null,null,null,null,
1290 X_agent_id,
1291 null,null,null,null,null,
1292 x_header_processable_flag);
1293
1294 END IF;
1295 END IF;
1296
1297 X_progress := '260';
1298 IF (X_vendor_id is not null)
1299 THEN
1300 X_temp_val := po_vendors_sv1.val_vendor_info(X_vendor_id,
1301 'PO SITE',
1302 X_vendor_site_id,
1303 X_vendor_contact_id,
1304 X_error_code);
1305 IF ( X_temp_val = FALSE ) THEN
1306 IF (X_error_code = 'PO_PDOI_INVALID_VENDOR') THEN
1307 po_interface_errors_sv1.handle_interface_errors(
1308 'PO_DOCS_OPEN_INTERFACE',
1309 'FATAL',
1310 null,
1311 X_interface_header_id,
1312 X_interface_line_id,
1313 X_error_code,
1314 'PO_HEADERS_INTERFACE',
1315 'VENDOR_ID',
1316 'VALUE',
1317 null,null,null,null,null,
1318 X_vendor_id,
1319 null,null,null,null,null,
1320 x_header_processable_flag);
1321
1322 ELSIF (X_error_code = 'PO_PDOI_INVALID_VENDOR_SITE') THEN
1323 po_interface_errors_sv1.handle_interface_errors(
1324 'PO_DOCS_OPEN_INTERFACE',
1325 'FATAL',
1326 null,
1327 X_interface_header_id,
1328 X_interface_line_id,
1329 X_error_code,
1330 'PO_HEADERS_INTERFACE',
1331 'VENDOR_SITE_ID',
1332 'VALUE',
1333 null,null,null,null,null,
1334 X_vendor_site_id,
1335 null,null,null,null,null,
1336 x_header_processable_flag);
1337
1338 ELSIF (X_error_code = 'PO_PDOI_INVALID_VDR_CNTCT') THEN
1339 po_interface_errors_sv1.handle_interface_errors(
1340 'PO_DOCS_OPEN_INTERFACE',
1341 'FATAL',
1342 null,
1343 X_interface_header_id,
1344 X_interface_line_id,
1345 X_error_code,
1346 'PO_HEADERS_INTERFACE',
1347 'VENDOR_CONTACT_ID',
1348 'VALUE',
1349 null,null,null,null,null,
1350 X_vendor_contact_id,
1351 null,null,null,null,null,
1352 x_header_processable_flag);
1353 END IF;
1354 END IF;
1355 END IF;
1356
1357 X_progress := '270';
1358 IF (X_ship_to_location_id IS NOT NULL ) THEN
1359 X_temp_val := po_line_locations_sv1.val_location_id(
1360 X_ship_to_location_id,
1361 'SHIP_TO');
1362
1363 IF ( X_temp_val = FALSE ) THEN
1364 po_interface_errors_sv1.handle_interface_errors(
1365 'PO_DOCS_OPEN_INTERFACE',
1366 'FATAL',
1367 null,
1368 X_interface_header_id,
1369 X_interface_line_id,
1370 'PO_PDOI_INVALID_SHIP_LOC_ID',
1371 'PO_HEADERS_INTERFACE',
1372 'SHIP_TO_LOCATION_ID',
1373 'VALUE',
1374 null,null,null,null,null,
1375 X_ship_to_location_id,
1376 null,null,null,null,null,
1377 x_header_processable_flag);
1378 END IF;
1379 END IF;
1380
1381 X_progress := '280';
1382 IF (X_bill_to_location_id IS NOT NULL) THEN
1383 X_temp_val := po_line_locations_sv1.val_location_id(
1384 X_bill_to_location_id,
1385 'BILL_TO');
1386
1387 IF ( X_temp_val = FALSE ) THEN
1388 po_interface_errors_sv1.handle_interface_errors(
1389 'PO_DOCS_OPEN_INTERFACE',
1390 'FATAL',
1391 null,
1392 X_interface_header_id,
1393 X_interface_line_id,
1394 'PO_PDOI_INVALID_BILL_LOC_ID',
1395 'PO_HEADERS_INTERFACE',
1396 'BILL_TO_LOCATION_ID',
1397 'VALUE',
1398 null,null,null,null,null,
1399 X_bill_to_location_id,
1400 null,null,null,null,null,
1401 x_header_processable_flag);
1402 END IF;
1403 END IF;
1404
1405 X_progress := '290';
1406 IF (X_terms_id IS NOT NULL ) THEN
1407 po_terms_sv.val_ap_terms(X_terms_id, x_res_terms);
1408 IF ( x_res_terms is null ) THEN
1409 po_interface_errors_sv1.handle_interface_errors(
1410 'PO_DOCS_OPEN_INTERFACE',
1411 'FATAL',
1412 null,
1413 X_interface_header_id,
1414 X_interface_line_id,
1415 'PO_PDOI_INVALID_PAY_TERMS',
1416 'PO_HEADERS_INTERFACE',
1417 'TERMS_ID',
1418 'VALUE',
1419 null,null,null,null,null,
1420 X_terms_id,
1421 null,null,null,null,null,
1422 x_header_processable_flag);
1423 END IF;
1424 END IF;
1425
1426 X_progress := '300';
1427 If (X_ship_via_lookup_code IS NOT NULL ) AND (X_def_inv_org_id is not null)
1428 THEN
1429 po_vendors_sv.val_freight_carrier(X_ship_via_lookup_code,
1430 X_def_inv_org_id,
1431 X_res_carrier);
1432 IF ( X_res_carrier is null ) THEN
1433 po_interface_errors_sv1.handle_interface_errors(
1434 'PO_DOCS_OPEN_INTERFACE',
1435 'FATAL',
1436 null,
1437 X_interface_header_id,
1438 X_interface_line_id,
1439 'PO_PDOI_INVALID_FREIGHT_CARR',
1440 'PO_HEADERS_INTERFACE',
1441 'SHIP_VIA_LOOKUP_CODE',
1442 'VALUE',
1443 null,null,null,null,null,
1444 X_ship_via_lookup_code,
1445 null,null,null,null,null,
1446 x_header_processable_flag);
1447 END IF;
1448 END IF;
1449
1450 X_progress := '310';
1451 IF (X_fob_lookup_code IS NOT NULL ) THEN
1452 po_vendors_sv.val_fob(X_fob_lookup_code, x_res_fob);
1453 IF ( x_res_fob is null ) THEN
1454 po_interface_errors_sv1.handle_interface_errors(
1455 'PO_DOCS_OPEN_INTERFACE',
1456 'FATAL',
1457 null,
1458 X_interface_header_id,
1459 X_interface_line_id,
1460 'PO_PDOI_INVALID_FOB',
1461 'PO_HEADERS_INTERFACE',
1462 'FOB_LOOKUP_CODE',
1463 'VALUE',
1464 null,null,null,null,null,
1465 X_fob_lookup_code,
1466 null,null,null,null,null,
1467 x_header_processable_flag);
1468
1469 END IF;
1470 END IF;
1471
1472 X_progress := '320';
1473 IF (X_freight_terms_lookup_code IS NOT NULL ) THEN
1474 po_vendors_sv.val_freight_terms(X_freight_terms_lookup_code,
1475 x_res_freight);
1476 IF ( x_res_freight is null ) THEN
1477 po_interface_errors_sv1.handle_interface_errors(
1478 'PO_DOCS_OPEN_INTERFACE',
1479 'FATAL',
1480 null,
1481 X_interface_header_id,
1482 X_interface_line_id,
1483 'PO_PDOI_INVALID_FREIGHT_TERMS',
1484 'PO_HEADERS_INTERFACE',
1485 'FREIGHT_TERMS_LOOKUP_CODE',
1486 'VALUE',
1487 null,null,null,null,null,
1488 X_freight_terms_lookup_code,
1489 null,null,null,null,null,
1490 x_header_processable_flag);
1491 END IF;
1492 END IF;
1493
1494 /* <INBOUND LOGISTICS FPJ START> */
1495 X_progress := '325';
1496 IF ( p_shipping_control IS NOT NULL ) THEN
1497 PO_VENDORS_SV.val_shipping_control( p_shipping_control,
1498 l_res_shipping_control);
1499 IF ( l_res_shipping_control IS NULL ) THEN
1500 PO_INTERFACE_ERRORS_SV1.handle_interface_errors (
1501 'PO_DOCS_OPEN_INTERFACE',
1502 'FATAL',
1503 NULL,
1504 X_interface_header_id,
1505 X_interface_line_id,
1506 'PO_PDOI_INVALID_SHIPPING_CTRL',
1507 'PO_HEADERS_INTERFACE',
1508 'SHIPPING_CONTROL',
1509 'VALUE',
1510 NULL,NULL,NULL,NULL,NULL,
1511 p_shipping_control,
1512 NULL,NULL,NULL,NULL,NULL,
1513 x_header_processable_flag);
1514 END IF;
1515 END IF;
1516 /* <INBOUND LOGISTICS FPJ END> */
1517
1518 X_progress := '330';
1519 IF (X_type_lookup_code = 'QUOTATION') THEN
1520 IF (X_approval_required_flag IS NOT NULL ) THEN
1521 X_temp_val := po_core_sv1.val_flag_value(x_approval_required_flag);
1522 IF ( X_temp_val = FALSE ) THEN
1523 po_interface_errors_sv1.handle_interface_errors(
1524 'PO_DOCS_OPEN_INTERFACE',
1525 'FATAL',
1526 null,
1527 X_interface_header_id,
1528 X_interface_line_id,
1529 'PO_PDOI_INVALID_FLAG_VALUE',
1530 'PO_HEADERS_INTERFACE',
1531 'APPROVAL_REQUIRED_FLAG',
1532 'COLUMN_NAME',
1533 'VALUE',
1534 null,null,null,null,
1535 'APPROVAL_REQUIRED_FLAG',
1536 X_approval_required_flag,
1537 null,null,null,null,
1538 x_header_processable_flag );
1539
1540 END IF;
1541 END IF;
1542 X_progress := '340';
1543 IF (X_quote_warning_delay < 0 ) THEN
1544 po_interface_errors_sv1.handle_interface_errors(
1545 'PO_DOCS_OPEN_INTERFACE',
1546 'FATAL',
1547 null,
1548 X_interface_header_id,
1549 X_interface_line_id,
1550 'PO_PDOI_LT_ZERO',
1551 'PO_HEADERS_INTERFACE',
1552 'QUOTE_WARNING_DELAY',
1553 'COLUMN_NAME',
1554 'VALUE',
1555 null,null,null,null,
1556 'QUOTE_WARNING_DELAY',
1557 X_quote_warning_delay,
1558 null,null,null,null,
1559 x_header_processable_flag );
1560
1561 END IF;
1562
1563 X_progress := '350';
1564 IF (X_reply_method_lookup_code IS NOT NULL ) THEN
1565 X_temp_val := po_headers_sv6.val_lookup_code(
1566 X_reply_method_lookup_code,
1567 'REPLY/RECEIVE VIA');
1568 IF ( X_temp_val = FALSE ) THEN
1569 po_interface_errors_sv1.handle_interface_errors(
1570 'PO_DOCS_OPEN_INTERFACE',
1571 'FATAL',
1572 null,
1573 X_interface_header_id,
1574 X_interface_line_id,
1575 'PO_PDOI_INVALID_REPLY_METHOD',
1576 'PO_HEADERS_INTERFACE',
1577 'REPLY_METHOD_LOOKUP_CODE',
1578 'VALUE',
1579 null,null,null,null,null,
1580 X_reply_method_lookup_code,
1581 null,null,null,null,null,
1582 x_header_processable_flag);
1583 END IF;
1584 END IF;
1585 END IF; /* end x_type_lookup_code = 'QUOTATION' */
1586
1587 X_progress := '360';
1588 --frkhan add standard
1589 IF (X_type_lookup_code in ('BLANKET','STANDARD')) THEN
1590 /*** validations which apply to Blanket agreements only ***/
1591
1592 IF (X_confirming_order_flag IS NOT NULL ) THEN
1593 X_temp_val := po_core_sv1.val_flag_value(x_confirming_order_flag);
1594 IF ( X_temp_val = FALSE ) THEN
1595 po_interface_errors_sv1.handle_interface_errors(
1596 'PO_DOCS_OPEN_INTERFACE',
1597 'FATAL',
1598 null,
1599 X_interface_header_id,
1600 X_interface_line_id,
1601 'PO_PDOI_INVALID_FLAG_VALUE',
1602 'PO_HEADERS_INTERFACE',
1603 'CONFIRMING_ORDER_FLAG',
1604 'COLUMN_NAME',
1605 'VALUE',
1606 null,null,null,null,
1607 'CONFIRMING_ORDER_FLAG',
1608 X_confirming_order_flag,
1609 null,null,null,null,
1610 x_header_processable_flag );
1611 END IF;
1612 END IF;
1613
1614 X_progress := '370';
1615 IF (X_acceptance_required_flag IS NOT NULL ) THEN
1616 X_temp_val := po_core_sv1.val_flag_value(
1617 x_acceptance_required_flag);
1618 IF ( X_temp_val = FALSE ) THEN
1619 po_interface_errors_sv1.handle_interface_errors(
1620 'PO_DOCS_OPEN_INTERFACE',
1621 'FATAL',
1622 null,
1623 X_interface_header_id,
1624 X_interface_line_id,
1625 'PO_PDOI_INVALID_FLAG_VALUE',
1626 'PO_HEADERS_INTERFACE',
1627 'ACCEPTANCE_REQUIRED_FLAG',
1628 'COLUMN_NAME',
1629 'VALUE',
1630 null,null,null,null,
1631 'ACCEPTANCE_REQUIRED_FLAG',
1632 X_acceptance_required_flag,
1633 null,null,null,null,
1634 x_header_processable_flag);
1635 END IF;
1636 END IF;
1637 X_progress := '380';
1638
1639 -- bug6601134(obsoleted 4467491)
1640 -- Removing the required property of acceptance due date
1641 -- when acceptance_required_flag is 'Y' to be consistent
1642 -- with forms behavior
1643
1644 /*
1645 IF (X_acceptance_required_flag = 'Y' ) THEN
1646 IF ( X_acceptance_due_date IS NULL ) THEN
1647 po_interface_errors_sv1.handle_interface_errors(
1648 'PO_DOCS_OPEN_INTERFACE',
1649 'FATAL',
1650 null,
1651 X_interface_header_id,
1652 X_interface_line_id,
1653 'PO_PDOI_COLUMN_NOT_NULL',
1654 'PO_HEADERS_INTERFACE',
1655 'ACCEPTANCE_DUE_DATE',
1656 'COLUMN_NAME',
1657 null,null,null,null,null,
1658 'ACCEPTANCE_DUE_DATE',
1659 null,null,null,null,null,
1660 x_header_processable_flag );
1661
1662 END IF;
1663 ELSIF */
1664
1665 IF(X_acceptance_required_flag = 'N') AND
1666 (X_acceptance_due_date IS NOT NULL) THEN
1667 po_interface_errors_sv1.handle_interface_errors(
1668 'PO_DOCS_OPEN_INTERFACE',
1669 'FATAL',
1670 null,
1671 X_interface_header_id,
1672 X_interface_line_id,
1673 'PO_PDOI_COLUMN_NULL',
1674 'PO_HEADERS_INTERFACE',
1675 'ACCEPTANCE_DUE_DATE',
1676 'COLUMN_NAME',
1677 null,null,null,null,null,
1678 'ACCEPTANCE_DUE_DATE',
1679 null,null,null,null,null,
1680 x_header_processable_flag );
1681 END IF;
1682
1683 X_progress := '390';
1684 --frkhan, just for blankets
1685 IF (X_type_lookup_code = 'BLANKET') THEN
1686 IF (X_amount_agreed < 0 ) THEN
1687 po_interface_errors_sv1.handle_interface_errors(
1688 'PO_DOCS_OPEN_INTERFACE',
1689 'FATAL',
1690 null,
1691 X_interface_header_id,
1692 X_interface_line_id,
1693 'PO_PDOI_LT_ZERO',
1694 'PO_HEADERS_INTERFACE',
1695 'AMOUNT_AGREED',
1696 'COLUMN_NAME',
1697 'VALUE',
1698 null,null,null,null,
1699 'AMOUNT_AGREED',
1700 X_amount_agreed,
1701 null,null,null,null,
1702 x_header_processable_flag );
1703
1704 END IF;
1705 END IF;
1706
1707 X_progress := '400';
1708
1709 X_progress := '420';
1710 IF (X_firm_status_lookup_code IS NOT NULL) AND
1711 (X_firm_status_lookup_code NOT IN ('Y', 'N')) THEN
1712
1713 po_interface_errors_sv1.handle_interface_errors(
1714 'PO_DOCS_OPEN_INTERFACE',
1715 'FATAL',
1716 null,
1717 X_interface_header_id,
1718 X_interface_line_id,
1719 'PO_PDOI_INVALID_FLAG_VALUE',
1720 'PO_HEADERS_INTERFACE',
1721 'FIRM_STATUS_LOOKUP_CODE',
1722 'COLUMN_NAME',
1723 'VALUE',
1724 null,null,null,null,
1725 'FIRM_STATUS_LOOKUP_CODE',
1726 X_firm_status_lookup_code,
1727 null,null,null,null,
1728 x_header_processable_flag);
1729 END IF;
1730
1731 X_progress := '420';
1732 IF (X_cancel_flag <> 'N') THEN
1733 po_interface_errors_sv1.handle_interface_errors(
1734 'PO_DOCS_OPEN_INTERFACE',
1735 'FATAL',
1736 null,
1737 X_interface_header_id,
1738 X_interface_line_id,
1739 'PO_PDOI_INVALID_VALUE',
1740 'PO_HEADERS_INTERFACE',
1741 'CANCEL_FLAG',
1742 'COLUMN_NAME',
1743 'VALUE',null,null,null,null,
1744 'CANCEL_FLAG',
1745 X_cancel_flag,null,null,null,null,
1746 x_header_processable_flag);
1747
1748 END IF;
1749
1750 X_progress := '430';
1751 IF (X_closed_code <> 'OPEN') THEN
1752 po_interface_errors_sv1.handle_interface_errors(
1753 'PO_DOCS_OPEN_INTERFACE',
1754 'FATAL',
1755 null,
1756 X_interface_header_id,
1757 X_interface_line_id,
1758 'PO_PDOI_INVALID_VALUE',
1759 'PO_HEADERS_INTERFACE',
1760 'CLOSED_CODE',
1761 'COLUMN_NAME',
1762 'VALUE',null,null,null,null,
1763 'CLOSED_CODE',
1764 X_closed_code,null,null,null,null,
1765 x_header_processable_flag);
1766 END IF;
1767
1768 X_progress := '440';
1769 /* Fix 2560428 draising */
1770 IF X_approval_status <> 'APPROVED' then
1771 IF (X_print_count <> 0) then
1772 po_interface_errors_sv1.handle_interface_errors(
1773 'PO_DOCS_OPEN_INTERFACE',
1774 'FATAL',
1775 null,
1776 X_interface_header_id,
1777 X_interface_line_id,
1778 'PO_PDOI_INVALID_VALUE',
1779 'PO_HEADERS_INTERFACE',
1780 'PRINT_COUNT',
1781 'COLUMN_NAME',
1782 'VALUE',null,null,null,null,
1783 'PRINT_COUNT',
1784 '0',
1785 null,null,null,null,
1786 x_header_processable_flag);
1787 END IF;
1788 END IF;
1789
1790 X_progress := '450';
1791
1792 -- Bug: 2466897 The value of frozen flag would be either Y or N
1793
1794 IF (X_frozen_flag not in ('Y', 'N' )) THEN
1795 po_interface_errors_sv1.handle_interface_errors(
1796 'PO_DOCS_OPEN_INTERFACE',
1797 'FATAL',
1798 null,
1799 X_interface_header_id,
1800 X_interface_line_id,
1801 'PO_PDOI_INVALID_VALUE',
1802 'PO_HEADERS_INTERFACE',
1803 'FROZEN_FLAG',
1804 'COLUMN_NAME',
1805 'VALUE',null,null,null,null,
1806 'FROZEN_FLAG',
1807 X_frozen_flag,null,null,null,null,
1808 x_header_processable_flag);
1809 END IF;
1810 /* Bug No. 1541387: For the corresponding enhancement request, now allowing
1811 Quotations to be loaded without the vendor_site_code and hence moving the
1812 following piece of code to validations meant for Blankets only.*/
1813 IF (X_vendor_site_id IS NULL ) THEN
1814 po_interface_errors_sv1.handle_interface_errors(
1815 'PO_DOCS_OPEN_INTERFACE',
1816 'FATAL',
1817 null,
1818 X_interface_header_id,
1819 X_interface_line_id,
1820 'PO_PDOI_COLUMN_NOT_NULL',
1821 'PO_HEADERS_INTERFACE',
1822 'VENDOR_SITE_ID',
1823 'COLUMN_NAME',
1824 null,null,null,null,null,
1825 'VENDOR_SITE_ID',
1826 null,null,null,null,null,
1827 x_header_processable_flag);
1828
1829 END IF;
1830
1831
1832 END IF; /* type_lookup_code = 'BLANKET' */
1833
1834 X_progress := '460';
1835 IF (X_start_date is not null) AND (X_end_date is not null) THEN
1836 X_temp_val := po_core_sv1.val_start_and_end_date(X_start_date,
1837 X_end_date);
1838 IF (X_temp_val = FALSE) THEN
1839 po_interface_errors_sv1.handle_interface_errors(
1840 'PO_DOCS_OPEN_INTERFACE',
1841 'FATAL',
1842 null,
1843 X_interface_header_id,
1844 X_interface_line_id,
1845 'PO_PDOI_INVALID_START_DATE',
1846 'PO_HEADERS_INTERFACE',
1847 'START_DATE',
1848 'VALUE',
1849 null,null,null,null,null,
1850 X_start_date,
1851 null,null,null,null,null,
1852 x_header_processable_flag );
1853 END IF;
1854 END IF;
1855
1856 --<JFMIP Vendor Registration FPJ Start>
1857 x_progress := '470';
1858 -- (1) QUOTATION is out of scope; STANDARD and BLANKET need to be validated
1859 -- (2) UPDATE only updates lines, does not update vendor/vendor site. Since
1860 -- this piece of code is not called if action is UPDATE, omit the condition
1861 -- (3) INCOMPLETE or INITIATE APPROVAL do no need to pass this validation
1862 IF (x_type_lookup_code <> 'QUOTATION')
1863 -- AND (x_action_code <> 'UPDATE')
1864 AND ((x_approval_status = 'APPROVED')
1865 OR (x_approval_status IS NULL AND x_authorization_status = 'APPROVED'))
1866 AND (x_vendor_id IS NOT NULL)
1867 AND (x_vendor_site_id IS NOT NULL) THEN
1868
1869 -- Call PO_FV_INTEGRATION_PVT.val_vendor_site_ccr_regis to check the
1870 -- Central Contractor Registration (CCR) status of the vendor site
1871 x_temp_val := PO_FV_INTEGRATION_PVT.val_vendor_site_ccr_regis(
1872 p_vendor_id => x_vendor_id,
1873 p_vendor_site_id => x_vendor_site_id);
1874
1875 IF (x_temp_val = FALSE) THEN
1876 PO_INTERFACE_ERRORS_SV1.handle_interface_errors(
1877 x_interface_type => 'PO_DOCS_OPEN_INTERFACE',
1878 x_error_type => 'FATAL',
1879 x_batch_id => null,
1880 x_interface_header_id => x_interface_header_id,
1881 x_interface_line_id => x_interface_line_id,
1882 x_error_message_name => 'PO_PDOI_VENDOR_SITE_CCR_INV',
1883 x_table_name => 'PO_HEADERS_INTERFACE',
1884 x_column_name => 'VENDOR_SITE_ID',
1885 x_tokenname1 => 'VENDOR_ID',
1886 x_tokenname2 => 'VENDOR_SITE_ID',
1887 x_tokenname3 => null,
1888 x_tokenname4 => null,
1889 x_tokenname5 => null,
1890 x_tokenname6 => null,
1891 x_tokenvalue1 => x_vendor_id,
1892 x_tokenvalue2 => x_vendor_site_id,
1893 x_tokenvalue3 => null,
1894 x_tokenvalue4 => null,
1895 x_tokenvalue5 => null,
1896 x_tokenvalue6 => null,
1897 x_header_processable_flag => x_header_processable_flag,
1898 x_interface_dist_id => null);
1899 END IF;
1900 END IF;
1901 --<JFMIP Vendor Registration FPJ End>
1902
1903 X_progress := '480';
1904 /** also need to make sure approval_status specified is valid ***/
1905 /* Now Initiate Approval is also valid for Standard Po's. */
1906 IF (X_approval_status NOT IN ('INCOMPLETE', 'APPROVED', 'INITIATE APPROVAL')) THEN
1907 /*** should be an error here ***/
1908 po_interface_errors_sv1.handle_interface_errors(
1909 'PO_DOCS_OPEN_INTERFACE',
1910 'FATAL',
1911 null,
1912 X_interface_header_id,
1913 X_interface_line_id,
1914 'PO_PDOI_INVALID_STATUS',
1915 'PO_HEADERS_INTERFACE',
1916 'APPROVAL_STATUS',
1917 null,null,null,null,null,null,
1918 null,null,null,null,null,null,
1919 x_header_processable_flag );
1920
1921 END IF;
1922
1923 EXCEPTION
1924
1925 WHEN others THEN
1926 po_message_s.sql_error('validate_po_headers', x_progress, sqlcode);
1927 raise;
1928 END validate_po_headers;
1929
1930
1931 END PO_HEADERS_SV6;