[Home] [Help]
PACKAGE BODY: APPS.PO_HEADERS_SV9
Source
1 PACKAGE BODY PO_HEADERS_SV9 AS
2 /* $Header: POXPIRDB.pls 115.22 2004/05/25 21:14:59 dreddy ship $ */
3
4 -- Read the profile option that enables/disables the debug log
5 g_po_pdoi_write_to_file VARCHAR2(1) := NVL(FND_PROFILE.VALUE('PO_PDOI_WRITE_TO_FILE'),'N');
6
7 /*================================================================
8
9 PROCEDURE NAME: replace_po_original_catalog()
10
11 ==================================================================*/
12 PROCEDURE replace_po_original_catalog(X_interface_header_id IN NUMBER,
13 X_interface_line_id IN NUMBER,
14 X_vendor_id IN NUMBER,
15 X_document_type_code IN VARCHAR2,
16 X_vendor_doc_num IN VARCHAR2,
17 X_start_date IN DATE,
18 X_end_date IN DATE,
19 X_header_processable_flag IN OUT NOCOPY VARCHAR2,
20 p_ga_flag IN VARCHAR2)
21
22
23 IS
24 X_progress VARCHAR2(3) := NULL;
25 x_temp binary_integer;
26 x_temp2 binary_integer := -1;
27 l_po_header_id NUMBER; --BUG#3165053
28 l_rel_exists VARCHAR2(1) := NULL; --BUG#3165053
29 l_po_exists_num NUMBER; --<Bug# 3504001>
30
31 BEGIN
32 X_progress := '010';
33 /* make sure that start_date is specified */
34 IF (X_start_date is null) THEN
35 po_interface_errors_sv1.handle_interface_errors(
36 'PO_DOCS_OPEN_INTERFACE',
37 'FATAL',
38 null,
39 X_interface_header_id,
40 X_interface_line_id,
41 'PO_PDOI_COLUMN_NOT_NULL',
42 'PO_HEADERS_INTERFACE',
43 'START_DATE',
44 'COLUMN_NAME',
45 null,null,null,null,null,
46 'START_DATE',
47 null,null,null,null,null,
48 X_header_processable_flag);
49 END IF;
50 /* make sure that the start_date < end_date */
51 -- Bug 2449186. Truncate dates when comparing them.
52 IF (TRUNC(X_start_date) > TRUNC(nvl(X_end_date, X_start_date))) THEN
53 po_interface_errors_sv1.handle_interface_errors(
54 'PO_DOCS_OPEN_INTERFACE',
55 'FATAL',
56 null,
57 X_interface_header_id,
58 X_interface_line_id,
59 'PO_PDOI_INVALID_START_DATE',
60 'PO_HEADERS_INTERFACE',
61 'START_DATE',
62 'VALUE',
63 null,null,null,null,null,
64 X_start_date,
65 null,null,null,null,null,
66 X_header_processable_flag);
67 END IF;
68
69 IF (X_header_processable_flag = 'Y') THEN
70 X_progress := '020' ;
71 /* make sure that such a po_header_id exists in po_headers table */
72
73 /*Bug 1239775
74 Performance issue
75 Before the fix we had one sql statment to handle both
76 quotation and the blanket ,but that was a performance issue
77 and so modified it to handle it seperately for quoation and
78 blanket and also created indices on quote_vendor_quote_number
79 and vendor_order_num
80 */
81 -- Bug 2449186. Truncate dates when comparing them.
82 if (x_document_type_code = 'QUOTATION') then
83 SELECT count(*)
84 INTO x_temp
85 FROM po_headers
86 WHERE vendor_id = X_vendor_id
87 AND quote_vendor_quote_number = X_vendor_doc_num
88 AND TRUNC(nvl(X_start_date, sysdate)) >= TRUNC(nvl(start_date, sysdate))
89 AND TRUNC(nvl(X_end_date, sysdate)) <= TRUNC(nvl(end_date, sysdate))
90 AND nvl(closed_code, 'OPEN') not in ('FINALLY CLOSED', 'CANCELLED');
91 elsif(x_document_type_code= 'BLANKET') then
92 SELECT count(*)
93 INTO x_temp
94 FROM po_headers
95 WHERE vendor_id = X_vendor_id
96 AND vendor_order_num = X_vendor_doc_num
97 AND TRUNC(nvl(X_start_date, sysdate)) >= TRUNC(nvl(start_date, sysdate))
98 AND TRUNC(nvl(X_end_date, sysdate)) <= TRUNC(nvl(end_date, sysdate))
99 AND nvl(closed_code, 'OPEN') not in ('FINALLY CLOSED', 'CANCELLED');
100 end if;
101
102 If X_temp = 0 then
103
104 x_temp2 := x_temp;
105
106 /*Bug 1239775
107 Performance issue
108 Before the fix we had one sql statment to handle both
109 quotation and the blanket ,but that was a performance issue
110 and so modified it to handle it seperately for quoation and
111 blanket and also created indices on quote_vendor_quote_number
112 and vendor_order_num
113 */
114 -- Bug 2449186. Truncate dates when comparing them.
115 if (x_document_type_code = 'QUOTATION') then
116 SELECT count(*)
117 INTO x_temp
118 FROM po_headers
119 WHERE vendor_id = X_vendor_id
120 AND quote_vendor_quote_number = X_vendor_doc_num
121 AND TRUNC(nvl(X_start_date, sysdate)) >= TRUNC(nvl(start_date, sysdate))
122 AND TRUNC(nvl(X_end_date, sysdate)) <= TRUNC(nvl(end_date, sysdate));
123 elsif(x_document_type_code= 'BLANKET') then
124 SELECT count(*)
125 INTO x_temp
126 FROM po_headers
127 WHERE vendor_id = X_vendor_id
128 AND vendor_order_num = X_vendor_doc_num
129 AND TRUNC(nvl(X_start_date, sysdate)) >= TRUNC(nvl(start_date, sysdate))
130 AND TRUNC(nvl(X_end_date, sysdate)) <= TRUNC(nvl(end_date, sysdate));
131 end if;
132
133 -- it should be OK to replace a finally closed or cancelled doc.
134
135 if X_temp <> 1 then
136 x_temp := x_temp2;
137 end if;
138
139 end if;
140
141 IF x_temp = 0 THEN
142 /* can not find the original catalog */
143 /* call the error handling routine with the error code =
144 'PDOI_INVALID_ORIGINAL_CATALOG' */
145 X_progress := '030';
146 po_interface_errors_sv1.handle_interface_errors(
147 'PO_DOCS_OPEN_INTERFACE',
148 'FATAL',
149 null,
150 X_interface_header_id,
151 X_interface_line_id,
152 'PO_PDOI_INVALID_ORIG_CATALOG',
153 'PO_HEADERS_INTERFACE',
154 'VENDOR_DOC_NUM',
155 'DOC_NUMBER',
156 null,null,null,null,null,
157 X_vendor_doc_num ,
158 null,null,null,null,null,
159 X_header_processable_flag);
160 ELSIF x_temp > 1 THEN
161 /* if returns more than 1 row, it is an error */
162 X_progress := '040';
163 po_interface_errors_sv1.handle_interface_errors(
164 'PO_DOCS_OPEN_INTERFACE',
165 'FATAL',
166 null,
167 X_interface_header_id,
168 X_interface_line_id,
169 'PO_PDOI_INVAL_MULT_ORIG_CATG',
170 'PO_HEADERS_INTERFACE',
171 'VENDOR_DOC_NUM',
172 'DOC_NUMBER',
173 null,null,null,null,null,
174 X_vendor_doc_num,
175 null,null,null,null,null,
176 X_header_processable_flag);
177 ELSIF x_temp = 1 THEN
178 X_progress := '050';
179 /* update the original catelog by changing the effective and expiration
180 date */
181
182 if x_temp2 = 0 then
183
184 /* Bug#3165053 : When replacing a blanket through PDOI, check that
185 no release with the release date greater than the start date
186 of the new replaced blanket exists */
187
188 SELECT po_header_id
189 INTO l_po_header_id
190 FROM po_headers
191 WHERE vendor_id = X_vendor_id
192 AND DECODE(X_document_type_code, 'QUOTATION', quote_vendor_quote_number,
193 'BLANKET' , vendor_order_num) = X_vendor_doc_num
194 AND TRUNC(nvl(X_start_date, sysdate)) >=
195 TRUNC(nvl(start_date, sysdate))
196 AND TRUNC(nvl(X_end_date, sysdate)) <=
197 TRUNC(nvl(end_date, sysdate));
198
199 IF nvl(p_ga_flag, 'N') = 'N' AND x_document_type_code = 'BLANKET' THEN --<Bug 3504001>
200
201 BEGIN -- Bug#3274836
202 SELECT 'Y' INTO l_rel_exists
203 FROM DUAL
204 WHERE EXISTS(
205 SELECT 'release exist after the expiration date'
206 FROM po_releases
207 WHERE release_date > X_start_date
208 AND po_header_id = l_po_header_id);
209 EXCEPTION
210 WHEN NO_DATA_FOUND THEN
211 l_rel_exists := 'N';
212 END;
213
214 IF l_rel_exists = 'Y' THEN
215
216 po_interface_errors_sv1.handle_interface_errors(
217 x_interface_type => 'PO_DOCS_OPEN_INTERFACE',
218 x_error_type => 'FATAL',
219 x_batch_id => null,
220 x_interface_header_id => X_interface_header_id,
221 x_interface_line_id => null,
222 x_error_message_name => 'PO_PDOI_ST_DATE_GT_REL_DATE',
223 x_table_name => 'PO_HEADERS_INTERFACE',
224 x_column_name => 'EFFECTIVE_DATE',
225 x_tokenname1 => null,
226 x_tokenname2 => null,
227 x_tokenname3 => null,
228 x_tokenname4 => null,
229 x_tokenname5 => null,
230 x_tokenname6 => null,
231 x_tokenvalue1 => null,
232 x_tokenvalue2 => null,
233 x_tokenvalue3 => null,
234 x_tokenvalue4 => null,
235 x_tokenvalue5 => null,
236 x_tokenvalue6 => null,
237 x_header_processable_flag=>x_header_processable_flag);
238 END IF;
239 --<Bug 3504001 START>
240 ELSIF nvl(p_ga_flag, 'N') = 'Y' and x_document_type_code = 'BLANKET' THEN
241 SELECT count(1)
242 INTO l_po_exists_num
243 FROM po_lines_all pl,
244 po_headers_all ph
245 WHERE pl.from_header_id = l_po_header_id
246 AND ph.po_header_id = pl.po_header_id
247 AND ph.creation_date >= X_start_date;
248
249 IF l_po_exists_num > 0 THEN
250 po_interface_errors_sv1.handle_interface_errors(
251 x_interface_type => 'PO_DOCS_OPEN_INTERFACE',
252 x_error_type => 'FATAL',
253 x_batch_id => null,
254 x_interface_header_id => X_interface_header_id,
255 x_interface_line_id => null,
256 x_error_message_name => 'PO_PDOI_GA_ST_DATE_GT_PO_DATE',
257 x_table_name => 'PO_HEADERS_INTERFACE',
258 x_column_name => 'EFFECTIVE_DATE',
259 x_tokenname1 => null,
260 x_tokenname2 => null,
261 x_tokenname3 => null,
262 x_tokenname4 => null,
263 x_tokenname5 => null,
264 x_tokenname6 => null,
265 x_tokenvalue1 => null,
266 x_tokenvalue2 => null,
267 x_tokenvalue3 => null,
268 x_tokenvalue4 => null,
269 x_tokenvalue5 => null,
270 x_tokenvalue6 => null,
271 x_header_processable_flag=>x_header_processable_flag);
272 END IF;
273
274 --<Bug 3504001 END>
275 END IF;
276
277 -- Bug 2449186. Truncate dates when comparing them.
278 UPDATE po_headers
279 SET start_date = nvl(start_date, X_start_date - 1),
280 end_date = X_start_date - 1,
281 last_updated_by = fnd_global.user_id,
282 last_update_date = sysdate
283 WHERE po_header_id = l_po_header_id;
284
285 else
286
287 /* Bug#3165053 : When replacing a blanket through PDOI, check that
288 no release with the release date greater than the start date
289 of the new replaced blanket exists */
290
291 SELECT po_header_id
292 INTO l_po_header_id
293 FROM po_headers
294 WHERE vendor_id = X_vendor_id
295 AND DECODE(X_document_type_code, 'QUOTATION', quote_vendor_quote_number,
296 'BLANKET' , vendor_order_num) = X_vendor_doc_num
297 AND TRUNC(nvl(X_start_date, sysdate)) >=
298 TRUNC(nvl(start_date, sysdate))
299 AND TRUNC(nvl(X_end_date, sysdate)) <=
300 TRUNC(nvl(end_date, sysdate))
301 AND nvl(closed_code, 'OPEN') not in ('FINALLY CLOSED', 'CANCELLED');
302
303 IF nvl(p_ga_flag, 'N') = 'N' AND x_document_type_code = 'BLANKET' THEN --<Bug 3504001>
304 BEGIN -- Bug#3274836
305 SELECT 'Y' INTO l_rel_exists
306 FROM DUAL
307 WHERE EXISTS(
308 SELECT 'release exist after the expiration date'
309 FROM po_releases
310 WHERE release_date > X_start_date
311 AND po_header_id = l_po_header_id);
312 EXCEPTION
313 WHEN NO_DATA_FOUND THEN
314 l_rel_exists := 'N';
315 END;
316
317 IF l_rel_exists = 'Y' THEN
318
319 po_interface_errors_sv1.handle_interface_errors(
320 x_interface_type => 'PO_DOCS_OPEN_INTERFACE',
321 x_error_type => 'FATAL',
322 x_batch_id => null,
323 x_interface_header_id => X_interface_header_id,
324 x_interface_line_id => null,
325 x_error_message_name => 'PO_PDOI_ST_DATE_GT_REL_DATE',
326 x_table_name => 'PO_HEADERS_INTERFACE',
327 x_column_name => 'EFFECTIVE_DATE',
328 x_tokenname1 => null,
329 x_tokenname2 => null,
330 x_tokenname3 => null,
331 x_tokenname4 => null,
332 x_tokenname5 => null,
333 x_tokenname6 => null,
334 x_tokenvalue1 => null,
335 x_tokenvalue2 => null,
336 x_tokenvalue3 => null,
337 x_tokenvalue4 => null,
338 x_tokenvalue5 => null,
339 x_tokenvalue6 => null,
340 x_header_processable_flag=>x_header_processable_flag);
341 END IF;
342 --<Bug 3504001 START>
343 ELSIF nvl(p_ga_flag, 'N') = 'Y' and x_document_type_code = 'BLANKET' THEN
344 SELECT count(1)
345 INTO l_po_exists_num
346 FROM po_lines_all pl,
347 po_headers_all ph
348 WHERE pl.from_header_id = l_po_header_id AND
349 ph.po_header_id = pl.po_header_id AND
350 ph.creation_date >= X_start_date;
351
352 IF l_po_exists_num > 0 THEN
353 po_interface_errors_sv1.handle_interface_errors(
354 x_interface_type => 'PO_DOCS_OPEN_INTERFACE',
355 x_error_type => 'FATAL',
356 x_batch_id => null,
357 x_interface_header_id => X_interface_header_id,
358 x_interface_line_id => null,
359 x_error_message_name => 'PO_PDOI_GA_ST_DATE_GT_PO_DATE',
360 x_table_name => 'PO_HEADERS_INTERFACE',
361 x_column_name => 'EFFECTIVE_DATE',
362 x_tokenname1 => null,
363 x_tokenname2 => null,
364 x_tokenname3 => null,
365 x_tokenname4 => null,
366 x_tokenname5 => null,
367 x_tokenname6 => null,
368 x_tokenvalue1 => null,
369 x_tokenvalue2 => null,
370 x_tokenvalue3 => null,
371 x_tokenvalue4 => null,
372 x_tokenvalue5 => null,
373 x_tokenvalue6 => null,
374 x_header_processable_flag=>x_header_processable_flag);
375 END IF;
376
377 --<Bug 3504001 END>
378 END IF;
379
380 -- Bug 2449186. Truncate dates when comparing them.
381 UPDATE po_headers
382 SET start_date = nvl(start_date, X_start_date - 1),
383 end_date = X_start_date - 1,
384 last_updated_by = fnd_global.user_id,
385 last_update_date = sysdate
386 WHERE po_header_id = l_po_header_id;
387 end if;
388
389 END IF;
390 END IF;
391
392 EXCEPTION
393 WHEN others THEN
394 po_message_s.sql_error('replace_po_original_catalog',
395 X_progress, sqlcode);
396 raise;
397 END replace_po_original_catalog;
398
399
400
401 /*================================================================
402
403 PROCEDURE NAME: check_po_original_catalog()
404
405 ==================================================================*/
406 PROCEDURE check_po_original_catalog(X_interface_header_id IN NUMBER,
407 X_interface_line_id IN NUMBER,
408 X_vendor_id IN NUMBER,
409 X_document_type_code IN VARCHAR2,
410 X_vendor_doc_num IN VARCHAR2,
411 X_start_date IN DATE,
412 X_end_date IN DATE,
413 X_document_num IN Varchar2, -- CTO changes FPH
414 X_po_header_id IN OUT NOCOPY VARCHAR2,
415 X_header_processable_flag IN OUT NOCOPY VARCHAR2)
416
417
418 IS
419 X_progress VARCHAR2(3) := NULL;
420 x_temp binary_integer :=0;
421 x_temp2 binary_integer;
422 /* Cto Changes FPH start */
423 x_colname varchar2(20);
424 x_tokenname varchar2(20);
425 x_tokenvalue po_headers_interface.vendor_doc_num%type; /* Bug3082104 */
426 /* Cto Changes FPH end */
427
428 -- <PDOI FPJ START>
429 -- added the following variables for PDOI enhancement.
430
431 x_po_status_rec PO_STATUS_REC_TYPE;
432 x_return_status varchar2(30);
433 x_consigned_consumption_flag po_headers.consigned_consumption_flag%type ;
434
435 -- <PDOI FPJ END>
436
437
438
439
440
441 BEGIN
442 -- For original and update checks - start and end dates are not required.
443
444 IF (X_header_processable_flag = 'Y') THEN
445 X_progress := '020' ;
446
447 /*Bug 1239775
448 Performance issue
449 Before the fix we had one sql statment to handle both
450 quotation and the blanket ,but that was a performance issue
451 and so modified it to handle it seperately for quoation and
452 blanket and also created indices on quote_vendor_quote_number
453 and vendor_order_num
454 */
455 /* CTO changes FPH. If the X_vendor_doc_num use it to get the
456 * count. Only if it is null then check whether document number
457 * is provided. If so use it. If both are null, then x_temp would
458 * be 0 and hence we return a message PO_PDOI_INVALID_ORIG_CATALOG.
459 * This is done since performance will be bad if we try to do in
460 * the same sql.
461 */
462
463 /* Bug# 3552765 - Added 'if' clause to take care of case where both
464 * vendor_doc_num and document_num are provided
465 */
466
467 if (X_vendor_doc_num is not null) and (X_document_num is not null) then
468
469 if x_document_type_code = 'STANDARD' then
470
471 select count(1)
472 into x_temp
473 from po_headers
474 where vendor_id = x_vendor_id
475 and vendor_order_num = x_vendor_doc_num
476 and segment1 = x_document_num;
477
478 else
479
480 select count(1)
481 into x_temp
482 from po_headers
483 where vendor_id = x_vendor_id
484 and segment1 = x_document_num
485 and decode(x_document_type_code, 'QUOTATION', quote_vendor_quote_number,
486 'BLANKET', vendor_order_num, NULL) = x_vendor_doc_num
487 AND TRUNC(nvl(X_start_date, sysdate)) >= TRUNC(nvl(start_date, sysdate))
488 AND TRUNC(nvl(X_end_date, sysdate)) <= TRUNC(nvl(end_date, sysdate))
489 AND nvl(closed_code, 'OPEN') not in ('FINALLY CLOSED', 'CANCELLED');
490
491 end if;
492
493 elsif (X_vendor_doc_num is not null) then
494
495 -- Bug 2449186. Truncate dates when comparing them.
496 if (x_document_type_code = 'QUOTATION') then
497 SELECT count(*)
498 INTO x_temp
499 FROM po_headers
500 WHERE vendor_id = X_vendor_id
501 AND quote_vendor_quote_number = X_vendor_doc_num
502 AND TRUNC(nvl(X_start_date, sysdate)) >= TRUNC(nvl(start_date, sysdate))
503 AND TRUNC(nvl(X_end_date, sysdate)) <= TRUNC(nvl(end_date, sysdate))
504 AND nvl(closed_code, 'OPEN') not in ('FINALLY CLOSED', 'CANCELLED');
505 elsif(x_document_type_code= 'BLANKET') then
506 SELECT count(*)
507 INTO x_temp
508 FROM po_headers
509 WHERE vendor_id = X_vendor_id
510 AND vendor_order_num = X_vendor_doc_num
511 AND TRUNC(nvl(X_start_date, sysdate)) >= TRUNC(nvl(start_date, sysdate))
512 AND TRUNC(nvl(X_end_date, sysdate)) <= TRUNC(nvl(end_date, sysdate))
513 AND nvl(closed_code, 'OPEN') not in ('FINALLY CLOSED', 'CANCELLED');
514 -- <PDOI FPJ START>
515 ELSIF(x_document_type_code = 'STANDARD') THEN
516 SELECT count(*)
517 INTO x_temp
518 FROM po_headers
519 WHERE vendor_id = x_vendor_id
520 and vendor_order_num = x_vendor_doc_num ;
521 -- <PDOI FPJ END>
522 END IF;
523
524 elsif(x_document_num is not null) then
525 -- <PDOI FPJ>
526 -- Added the below 'if/else' statement for standard PO.
527
528 if (x_document_type_code <> 'STANDARD') then
529 /* Since we are using segment1 it is same for both
530 * Blanket and Quotation.
531 */
532 -- Bug 2449186. Truncate dates when comparing them.
533 SELECT count(*)
534 INTO x_temp
535 FROM po_headers
536 WHERE vendor_id = X_vendor_id
537 AND segment1 = x_document_num
538 AND type_lookup_code= x_document_type_code
539 AND TRUNC(nvl(X_start_date, sysdate)) >= TRUNC(nvl(start_date, sysdate))
540 AND TRUNC(nvl(X_end_date, sysdate)) <= TRUNC(nvl(end_date, sysdate))
541 AND nvl(closed_code, 'OPEN') not in ('FINALLY CLOSED', 'CANCELLED');
542 -- <PDOI FPJ START>
543 else
544 SELECT count(*)
545 INTO x_temp
546 FROM po_headers
547 WHERE vendor_id = x_vendor_id
548 and segment1 = x_document_num ;
549 end if;
550 -- <PDOI FPJ END>
551 end if;
552
553 /* Cto Changes FPH start */
554 if (x_vendor_doc_num is not null) then
555 x_colname := 'VENDOR_DOC_NUM';
556 x_tokenname := 'DOC_NUMBER';
557 x_tokenvalue := X_vendor_doc_num;
558 elsif (x_document_num is not null) then
559 x_colname := 'DOCUMENT_NUM';
560 x_tokenname := 'DOC_NUMBER';
561 x_tokenvalue := x_document_num;
562 end if;
563 IF (g_po_pdoi_write_to_file = 'Y') THEN
564 PO_DEBUG.put_line ('x_colname:' || (x_colname));
565 PO_DEBUG.put_line ('x_tokenname:' || (x_tokenname));
566 PO_DEBUG.put_line ('x_tokenvalue:' ||(x_tokenvalue));
567 END IF;
568 /* Cto Changes FPH end */
569 IF x_temp = 0 THEN
570
571 /* can not find the original catalog */
572 /* call the error handling routine with the error code =
573 'PDOI_INVALID_ORIGINAL_CATALOG' */
574
575 IF (g_po_pdoi_write_to_file = 'Y') THEN
576 PO_DEBUG.put_line ('X_vendor_id:' || to_char(X_vendor_id));
577 PO_DEBUG.put_line ('X_vendor_doc_num:' || X_vendor_doc_num);
578 PO_DEBUG.put_line ('X_vendor_doc_num:' || X_vendor_doc_num);
579 PO_DEBUG.put_line ('x_document_num:' || X_vendor_doc_num);
580 END IF;
581 if ( X_vendor_doc_num is null and x_document_num is null) then
582 x_colname := 'VENDOR_DOC_NUM';
583 x_tokenname := 'DOC_NUMBER';
584 x_tokenvalue := X_vendor_doc_num;
585 end if;
586
587 X_progress := '030';
588 -- <PDOI FPJ>
589 -- Added the following 'if/else' statement for standard purchase orders.
590
591 IF (x_document_type_code <> 'STANDARD') THEN
592 po_interface_errors_sv1.handle_interface_errors(
593 'PO_DOCS_OPEN_INTERFACE',
594 'FATAL',
595 null,
596 X_interface_header_id,
597 X_interface_line_id,
598 'PO_PDOI_INVALID_ORIG_CATALOG',
599 'PO_HEADERS_INTERFACE',
600 x_colname,
601 x_tokenname,
602 null,null,null,null,null,
603 x_tokenvalue ,
604 null,null,null,null,null,
605 X_header_processable_flag);
606 else
607 -- <PDOI FPJ START>
608 -- Added the below procedure to populate the error message incase
609 -- of standard PO.
610 po_interface_errors_sv1.handle_interface_errors(
611 x_interface_type => 'PO_DOCS_OPEN_INTERFACE',
612 x_error_type => 'FATAL',
613 x_batch_id => null,
614 x_interface_header_id => X_interface_header_id,
615 x_interface_line_id => null,
616 x_error_message_name => 'PO_PDOI_INVALID_ORIG_STD_PO',
617 x_table_name => 'PO_HEADERS_INTERFACE',
618 x_column_name => x_colname,
619 x_tokenname1 => x_tokenname,
620 x_tokenname2 => null,
621 x_tokenname3 => null,
622 x_tokenname4 => null,
623 x_tokenname5 => null,
624 x_tokenname6 => null,
625 x_tokenvalue1 => X_tokenvalue,
626 x_tokenvalue2 => null,
627 x_tokenvalue3 => null,
628 x_tokenvalue4 => null,
629 x_tokenvalue5 => null,
630 x_tokenvalue6 => null,
631 x_header_processable_flag=>x_header_processable_flag);
632 -- <PDOI FPJ END>
633 end if;
634 ELSIF x_temp > 1 THEN
635 /* if returns more than 1 row, it is an error */
636 X_progress := '040';
637 IF (g_po_pdoi_write_to_file = 'Y') THEN
638 PO_DEBUG.put_line ('X_vendor_doc_num:' || X_vendor_doc_num);
639 PO_DEBUG.put_line ('x_document_num:' || X_vendor_doc_num);
640 END IF;
641 -- <PDOI FPJ>
642 -- Added the following 'if/else' for standard purchase orders.
643
644 IF (x_document_type_code <> 'STANDARD') THEN
645 po_interface_errors_sv1.handle_interface_errors(
646 'PO_DOCS_OPEN_INTERFACE',
647 'FATAL',
648 null,
649 X_interface_header_id,
650 X_interface_line_id,
651 'PO_PDOI_INVAL_MULT_ORIG_CATG',
652 'PO_HEADERS_INTERFACE',
653 x_colname,
654 x_tokenname,
655 null,null,null,null,null,
656 X_vendor_doc_num,
657 null,null,null,null,null,
658 X_header_processable_flag);
659 ELSE
660 -- <PDOI FPJ START>
661 -- Added the below procedure to populate error message is case of standard
662 -- purchase orders.
663 po_interface_errors_sv1.handle_interface_errors(
664 x_interface_type => 'PO_DOCS_OPEN_INTERFACE',
665 x_error_type => 'FATAL',
666 x_batch_id => null,
667 x_interface_header_id => X_interface_header_id,
668 x_interface_line_id => null,
669 x_error_message_name => 'PO_PDOI_MULTIPLE_STD_PO',
670 x_table_name => 'PO_HEADERS_INTERFACE',
671 x_column_name => x_colname,
672 x_tokenname1 => x_tokenname,
673 x_tokenname2 => null,
674 x_tokenname3 => null,
675 x_tokenname4 => null,
676 x_tokenname5 => null,
677 x_tokenname6 => null,
678 x_tokenvalue1 => X_vendor_doc_num,
679 x_tokenvalue2 => null,
680 x_tokenvalue3 => null,
681 x_tokenvalue4 => null,
682 x_tokenvalue5 => null,
683 x_tokenvalue6 => null,
684 x_header_processable_flag=>x_header_processable_flag);
685 -- <PDOI FPJ END>
686 end if;
687 ELSIF x_temp = 1 THEN
688 X_progress := '050';
689
690 --
691 -- Valid original catalog exists and can be updated.
692 --
693 IF (g_po_pdoi_write_to_file = 'Y') THEN
694 PO_DEBUG.put_line ('Valid catalog/blanket exists and can be updated');
695 END IF;
696
697 -- Bug 2449186. Truncate dates when comparing them. Also refactored update.
698 -- <PDOI FPJ>
699 -- Added the following 'if/else' for standard purchase orders.
700
701 IF (x_document_type_code <> 'STANDARD') THEN
702 SELECT po_header_id
703 INTO X_po_header_id
704 FROM po_headers
705 WHERE vendor_id = X_vendor_id
706 AND decode(x_vendor_doc_num,null,segment1,(DECODE(X_document_type_code,
707 'QUOTATION', quote_vendor_quote_number,
708 'BLANKET' , vendor_order_num)))
709 = decode(X_vendor_doc_num,null,x_document_num,X_vendor_doc_num) --cto changes FPH
710 AND TRUNC(nvl(X_start_date, sysdate)) >= TRUNC(nvl(start_date, sysdate))
711 AND TRUNC(nvl(X_end_date, sysdate)) <= TRUNC(nvl(end_date, sysdate))
712 AND nvl(closed_code, 'OPEN') not in ('FINALLY CLOSED', 'CANCELLED');
713
714 ELSE
715 -- <PDOI FPJ START>
716 -- Added the following api to check whether the document is in valid
717 -- status and updateable.
718 IF (g_po_pdoi_write_to_file = 'Y') THEN
719 PO_DEBUG.put_line ('Calling status check api for standard POs');
720 END IF;
721
722 PO_DOCUMENT_CHECKS_GRP.po_status_check(
723 p_api_version => 1.0,
724 p_header_id => null,
725 p_release_id => null,
726 p_document_type => 'PO',
727 p_document_subtype => 'STANDARD',
728 p_document_num => x_document_num,
729 p_vendor_order_num => x_vendor_doc_num,
730 p_line_id => null,
731 p_line_location_id => null,
732 p_distribution_id => null,
733 p_mode => 'CHECK_UPDATEABLE',
734 x_po_status_rec => x_po_status_rec,
735 x_return_status => x_return_status);
736
737 IF (x_vendor_doc_num is not null) THEN
738
739 SELECT po_header_id,
740 consigned_consumption_flag
741 INTO x_po_header_id,
742 x_consigned_consumption_flag
743 FROM po_headers
744 WHERE vendor_order_num = x_vendor_doc_num;
745
746 ELSE
747
748 SELECT po_header_id,
749 consigned_consumption_flag
750 INTO x_po_header_id,
751 x_consigned_consumption_flag
752 FROM po_headers
753 WHERE segment1 = x_document_num ;
754
755 END IF; /* x_vendor_doc_num is not null */
756
757 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
758
759 if x_po_status_rec.updatable_flag(1) = 'N' or
760 x_consigned_consumption_flag='Y' then
761
762 if (g_po_pdoi_write_to_file = 'Y') THEN
763 PO_DEBUG.put_line ('Standard PO is not updatable');
764 PO_DEBUG.put_line('x_po_status_rec.updatable_flag is ' || x_po_status_rec.updatable_flag(1));
765 PO_DEBUG.put_line ('x_consigned_consumption_flag is
766 '|| x_consigned_consumption_flag);
767 end if;
768
769 po_interface_errors_sv1.handle_interface_errors(
770 x_interface_type => 'PO_DOCS_OPEN_INTERFACE',
771 x_error_type => 'FATAL',
772 x_batch_id => null,
773 x_interface_header_id => X_interface_header_id,
774 x_interface_line_id => null,
775 x_error_message_name => 'PO_PDOI_STD_PO_INVALID_STATUS',
776 x_table_name => 'PO_HEADERS_INTERFACE',
777 x_column_name => x_colname,
778 x_tokenname1 => x_tokenname,
779 x_tokenname2 => null,
780 x_tokenname3 => null,
781 x_tokenname4 => null,
782 x_tokenname5 => null,
783 x_tokenname6 => null,
784 x_tokenvalue1 => X_vendor_doc_num,
785 x_tokenvalue2 => null,
786 x_tokenvalue3 => null,
787 x_tokenvalue4 => null,
788 x_tokenvalue5 => null,
789 x_tokenvalue6 => null,
790 x_header_processable_flag=>x_header_processable_flag);
791
792 END IF; /* x_status_rec.updatable-flag(1)='N' */
793
794 END IF; /* x_return_status = FND_API.G_RET_STS_SUCCESS */
795
796 -- <PDOI FPJ END>
797 end if;
798
799 -- update po_header_id in interface table
800
801 update po_headers_interface
802 set po_header_id = x_po_header_id
803 where interface_header_id = X_interface_header_id; /* nwang, need this */
804
805
806 END IF;
807 END IF;
808
809 EXCEPTION
810 WHEN others THEN
811 po_message_s.sql_error('check_po_original_catalog',
812 X_progress, sqlcode);
813 raise;
814 END check_po_original_catalog;
815
816
817
818 /*================================================================
819
820 PROCEDURE NAME: check_if_catalog_exists()
821
822 ==================================================================*/
823 PROCEDURE check_if_catalog_exists ( X_interface_header_id IN NUMBER,
824 X_interface_line_id IN NUMBER,
825 X_vendor_id IN NUMBER,
826 X_document_type_code IN VARCHAR2,
827 X_vendor_doc_num IN VARCHAR2,
828 X_start_date IN DATE,
829 X_end_date IN DATE,
830 X_po_header_id IN OUT NOCOPY VARCHAR2,
831 X_header_processable_flag IN OUT NOCOPY VARCHAR2)
832
833
834 IS
835 X_progress VARCHAR2(3) := NULL;
836 x_temp binary_integer;
837
838 BEGIN
839 X_progress := '010';
840
841 IF (X_header_processable_flag = 'Y') THEN
842 /*Bug 1239775
843 Performance issue
844 Before the fix we had one sql statment to handle both
845 quotation and the blanket ,but that was a performance issue
846 and so modified it to handle it seperately for quoation and
847 blanket and also created indices on quote_vendor_quote_number
848 and vendor_order_num
849 */
850 -- Bug 2449186. Truncate dates when comparing them.
851 if (x_document_type_code = 'QUOTATION') then
852 SELECT count(*)
853 INTO x_temp
854 FROM po_headers
855 WHERE vendor_id = X_vendor_id
856 AND quote_vendor_quote_number = X_vendor_doc_num
857 AND TRUNC(nvl(X_start_date, sysdate)) >= TRUNC(nvl(start_date, sysdate))
858 AND TRUNC(nvl(X_end_date, sysdate)) <= TRUNC(nvl(end_date, sysdate))
859 AND nvl(closed_code, 'OPEN') not in ('FINALLY CLOSED', 'CANCELLED');
860 elsif(x_document_type_code= 'BLANKET') then
861 SELECT count(*)
862 INTO x_temp
863 FROM po_headers
864 WHERE vendor_id = X_vendor_id
865 AND vendor_order_num = X_vendor_doc_num
866 AND TRUNC(nvl(X_start_date, sysdate)) >= TRUNC(nvl(start_date, sysdate))
867 AND TRUNC(nvl(X_end_date, sysdate)) <= TRUNC(nvl(end_date, sysdate))
868 AND nvl(closed_code, 'OPEN') not in ('FINALLY CLOSED', 'CANCELLED');
869 --frkhan was not catching this case before so request was failing
870 elsif (x_document_type_code= 'STANDARD') then
871 x_temp := 0;
872 end if;
873
874 IF (g_po_pdoi_write_to_file = 'Y') THEN
875 po_debug.put_line ('Value of count(*) is :' || to_char(x_temp));
876 END IF;
877
878 IF x_temp = 0 THEN
879
880 -- No active catalog exists in the system with the same vendor doc number.
881 -- OK to create a new catalog.
882 NULL;
883
884 ELSE
885 /* if returns 1 or more than one row, it is an error */
886 X_progress := '020';
887 po_interface_errors_sv1.handle_interface_errors(
888 'PO_DOCS_OPEN_INTERFACE',
889 'FATAL',
890 null,
891 X_interface_header_id,
892 X_interface_line_id,
893 'PO_PDOI_CATG_ALREADY_EXISTS',
894 'PO_HEADERS_INTERFACE',
895 'VENDOR_DOC_NUM',
896 'DOC_NUMBER',
897 null,null,null,null,null,
898 X_vendor_doc_num,
899 null,null,null,null,null,
900 X_header_processable_flag);
901 END IF;
902 END IF;
903
904 EXCEPTION
905 WHEN others THEN
906 po_message_s.sql_error('check_if_catalog_exists',
907 X_progress, sqlcode);
908 raise;
909 END check_if_catalog_exists;
910
911 END PO_HEADERS_SV9;