[Home] [Help]
PACKAGE BODY: APPS.PO_PDOI_PREPROC_PVT
Source
1 PACKAGE BODY PO_PDOI_PREPROC_PVT AS
2 /* $Header: PO_PDOI_PREPROC_PVT.plb 120.24 2006/09/12 00:37:11 bao noship $ */
3
4 d_pkg_name CONSTANT varchar2(50) :=
5 PO_LOG.get_package_base('PO_PDOI_PREPROC_PVT');
6
7 -------------------------------------------------------
8 ----------- PRIVATE PROCEDURES PROTOTYPE --------------
9 -------------------------------------------------------
10 PROCEDURE update_dependent_line_acc_flag; -- bug5149827
11
12 PROCEDURE assign_processing_id;
13
14 PROCEDURE get_processable_records
15 ( x_intf_header_id_tbl IN OUT NOCOPY PO_TBL_NUMBER,
16 p_process_code_tbl IN PO_TBL_VARCHAR30,
17 p_request_id_tbl IN PO_TBL_NUMBER
18 );
19
20 PROCEDURE validate_interface_values;
21
22 PROCEDURE derive_vendor_id;
23
24 PROCEDURE verify_action_replace;
25
26 PROCEDURE verify_action_update;
27
28 PROCEDURE verify_action_original;
29
30 PROCEDURE populate_line_loc_interface;
31
32 PROCEDURE assign_po_header_id;
33
34 PROCEDURE check_release_dates
35 ( p_interface_header_id IN NUMBER,
36 p_po_header_id IN NUMBER,
37 p_ga_flag IN VARCHAR2,
38 p_new_doc_start_date IN DATE,
39 x_valid IN OUT NOCOPY VARCHAR2
40 );
41
42 -------------------------------------------------------
43 -------------- PUBLIC PROCEDURES ----------------------
44 -------------------------------------------------------
45
46 -----------------------------------------------------------------------
47 --Start of Comments
48 --Name: process
49 --Function:
50 -- Main procedure of PRE-PROCESSING in PDOI
51 --Parameters:
52 --IN:
53 --IN OUT:
54 --OUT:
55 --End of Comments
56 ------------------------------------------------------------------------
57 PROCEDURE process IS
58
59 d_api_name CONSTANT VARCHAR2(30) := 'process';
60 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
61 d_position NUMBER;
62
63 BEGIN
64
65 d_position := 0;
66
67 IF (PO_LOG.d_proc) THEN
68 PO_LOG.proc_begin (d_module);
69 END IF;
70
71 PO_TIMING_UTL.start_time (PO_PDOI_CONSTANTS.g_T_PREPROCESSING);
72
73 IF (PO_PDOI_PARAMS.g_request.document_type <>
74 PO_PDOI_CONSTANTS.g_DOC_TYPE_STANDARD
75 AND
76 PO_PDOI_PARAMS.g_request.process_code =
77 PO_PDOI_CONSTANTS.g_PROCESS_CODE_NOTIFIED) THEN
78
79 update_dependent_line_acc_flag; -- bug5149827 - Renamed the procedure
80 END IF;
81
82 d_position := 10;
83 assign_processing_id;
84
85 d_position := 20;
86 validate_interface_values;
87
88 d_position := 30;
89
90 derive_vendor_id; -- have to prepopulate vendor info because catalog
91 -- existence check needs this
92
93 -- For update and replace action, make sure that the document exists
94 -- For ORIGINAL action, make sure that there should not be another document
95 -- in the system with the same document identifiers (e.g. segment1,
96 -- vendor_doc_num, etc.)
97
98 d_position := 40;
99 verify_action_replace;
100
101 d_position := 50;
102 verify_action_update;
103
104 d_position := 60;
105 verify_action_original;
106
107 d_position := 70;
108 populate_line_loc_interface;
109
110 d_position := 80;
111 -- For documents that will get created, assign po_header_id
112 assign_po_header_id;
113
114 d_position := 90;
115 PO_INTERFACE_ERRORS_UTL.flush_errors_tbl;
116
117 d_position := 100;
118 PO_PDOI_UTL.commit_work;
119
120 PO_TIMING_UTL.stop_time (PO_PDOI_CONSTANTS.g_T_PREPROCESSING);
121
122 IF (PO_LOG.d_proc) THEN
123 PO_LOG.proc_end (d_module);
124 END IF;
125
126 EXCEPTION
127 WHEN OTHERS THEN
128 PO_MESSAGE_S.add_exc_msg
129 ( p_pkg_name => d_pkg_name,
130 p_procedure_name => d_api_name || '.' || d_position
131 );
132 RAISE;
133 END process;
134
135
136 -------------------------------------------------------
137 -------------- PRIVATE PROCEDURES ---------------------
138 -------------------------------------------------------
139
140 -- bug5149827
141 -- Renamed the procedure
142
143 -----------------------------------------------------------------------
144 --Start of Comments
145 --Name: update_dependent_line_acc_flag
146 --Function:
147 -- 1. Update price break acceptance flag according to the acceptance status
148 -- of the parent line
149 -- 2. Update lines that have parent interface lne id according to the
150 -- acceptance status of the parent line
151 --Parameters:
152 --IN:
153 --IN OUT:
154 --OUT:
155 --End of Comments
156 ------------------------------------------------------------------------
157 PROCEDURE update_dependent_line_acc_flag IS
158
159 d_api_name CONSTANT VARCHAR2(30) := 'update_dependent_line_acc_flag';
160 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
161 d_position NUMBER;
162
163 l_intf_line_id_tbl PO_TBL_NUMBER;
164 l_price_chg_accept_flag_tbl PO_TBL_VARCHAR1;
165 l_price_break_flag_tbl PO_TBL_VARCHAR1;
166
167 l_current_flag VARCHAR2(1);
168
169 l_update_flag_value_idx_tbl PO_PDOI_UTL.pls_integer_tbl_type :=
170 PO_PDOI_UTL.pls_integer_tbl_type();
171
172 BEGIN
173 d_position := 0;
174
175 IF (PO_LOG.d_proc) THEN
176 PO_LOG.proc_begin (d_module);
177 END IF;
178
179 IF (PO_PDOI_PARAMS.g_request.interface_header_id IS NOT NULL) THEN
180
181 -- (1) Update price break acceptance flag according to the acceptance
182 -- status of the parent line
183
184 SELECT interface_line_id,
185 price_chg_accept_flag,
186 price_break_flag
187 BULK COLLECT
188 INTO l_intf_line_id_tbl,
189 l_price_chg_accept_flag_tbl,
190 l_price_break_flag_tbl
191 FROM po_lines_interface
192 WHERE interface_header_id = PO_PDOI_PARAMS.g_request.interface_header_id
193 AND NVL(process_code, PO_PDOI_CONSTANTS.g_process_code_PENDING) =
194 PO_PDOI_CONSTANTS.g_process_code_NOTIFIED
195 ORDER BY po_line_id, interface_line_id;
196
197 IF (PO_LOG.d_stmt) THEN
198 PO_LOG.stmt(d_module, d_position, 'number of lines with notified status ',
199 l_intf_line_id_tbl.COUNT);
200 END IF;
201
202 d_position := 10;
203
204 FOR i IN 1..l_intf_line_id_tbl.COUNT LOOP
205 IF (PO_LOG.d_stmt) THEN
206 PO_LOG.stmt(d_module, d_position, 'i = ' || i || ', intf_line_id = ' ||
207 l_intf_line_id_tbl(i) || ' change accept flag = ' ||
208 l_price_chg_accept_flag_tbl(i));
209 END IF;
210
211 IF (NVL(l_price_break_flag_tbl(i), 'N') = 'N') THEN
212 -- regular po line
213 l_current_flag := l_price_chg_accept_flag_tbl(i);
214 ELSE
215 -- price break. Need to update
216 l_price_chg_accept_flag_tbl(i) := l_current_flag;
217 l_update_flag_value_idx_tbl.extend;
218 l_update_flag_value_idx_tbl(l_update_flag_value_idx_tbl.COUNT) := i;
219 END IF;
220 END LOOP;
221
222 d_position := 20;
223
224 -- update price change accept flag for price break lines
225 IF (l_update_flag_value_idx_tbl.COUNT > 0) THEN
226 FORALL i IN VALUES OF l_update_flag_value_idx_tbl
227 UPDATE po_lines_interface
228 SET price_chg_accept_flag = l_price_chg_accept_flag_tbl(i)
229 WHERE interface_line_id = l_intf_line_id_tbl(i);
230 END IF;
231
232 d_position := 30;
233
234 -- (2) Update lines that have parent interface lne id according to the
235 -- acceptance status of the parent line
236
237 -- bug5149827
238 -- Set the acceptance status of the child record to be the same as
239 -- the parent
240 UPDATE po_lines_interface lines
241 SET lines.price_chg_accept_flag =
242 ( SELECT parent_lines.price_chg_accept_flag
243 FROM po_lines_interface parent_lines
244 WHERE lines.parent_interface_line_id =
245 parent_lines.interface_line_id )
246 WHERE lines.interface_header_id = PO_PDOI_PARAMS.g_request.interface_header_id
247 AND NVL(lines.process_code, PO_PDOI_CONSTANTS.g_process_code_PENDING) =
248 PO_PDOI_CONSTANTS.g_process_code_NOTIFIED
249 AND lines.parent_interface_line_id IS NOT NULL;
250
251 IF (PO_LOG.d_stmt) THEN
252 PO_LOG.stmt(d_module, d_position, '# of lines updated based on parent_intf_line_id',
253 SQL%ROWCOUNT);
254 END IF;
255
256 d_position := 40;
257
258 -- Reject all records that have not been accepted
259 UPDATE po_lines_interface
260 SET process_code = PO_PDOI_CONSTANTS.g_PROCESS_CODE_REJECTED
261 WHERE interface_header_id = PO_PDOI_PARAMS.g_request.interface_header_id
262 AND process_code = PO_PDOI_CONSTANTS.g_PROCESS_CODE_NOTIFIED
263 AND price_chg_accept_flag = 'N';
264
265 d_position := 30;
266 END IF;
267
268 IF (PO_LOG.d_proc) THEN
269 PO_LOG.proc_end (d_module);
270 END IF;
271
272 EXCEPTION
273 WHEN OTHERS THEN
274 PO_MESSAGE_S.add_exc_msg
275 ( p_pkg_name => d_pkg_name,
276 p_procedure_name => d_api_name || '.' || d_position
277 );
278 RAISE;
279 END update_dependent_line_acc_flag;
280
281 -- determine what records PDOI needs to process in this run and
282 -- assign all those records with a processing_id
283
284 -----------------------------------------------------------------------
285 --Start of Comments
286 --Name: assign_processing_id
287 --Function:
288 -- Assign an internally tracking processing id to identify all the records that
289 -- will be processed in this current PDOI run
290 --Parameters:
291 --IN:
292 --IN OUT:
293 --OUT:
294 --End of Comments
295 ------------------------------------------------------------------------
296 PROCEDURE assign_processing_id IS
297
298 d_api_name CONSTANT VARCHAR2(30) := 'assign_processing_id';
299 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
300 d_position NUMBER;
301
302
303 CURSOR c_interface_headers IS
304 SELECT PHI.interface_header_id,
305 PHI.process_code,
306 PHI.request_id
307 FROM po_headers_interface PHI
308 WHERE PHI.org_id = PO_PDOI_PARAMS.g_request.org_id
309 AND NVL(PHI.process_code, PO_PDOI_CONSTANTS.g_PROCESS_CODE_PENDING) <>
310 PO_PDOI_CONSTANTS.g_PROCESS_CODE_REJECTED
311 AND (PHI.batch_id = PO_PDOI_PARAMS.g_request.batch_id
312 OR
313 PO_PDOI_PARAMS.g_request.batch_id IS NULL)
314 AND (PHI.process_code = PO_PDOI_PARAMS.g_request.process_code
315 OR
316 ( NVL(PO_PDOI_PARAMS.g_request.process_code,
317 PO_PDOI_CONSTANTS.g_process_code_PENDING) <>
318 PO_PDOI_CONSTANTS.g_process_code_NOTIFIED
319 AND
320 PHI.process_code = PO_PDOI_CONSTANTS.g_process_code_IN_PROCESS)
321 OR
322 PHI.process_code IS NULL)
323 AND (PHI.interface_header_id = PO_PDOI_PARAMS.g_request.interface_header_id
324 OR
325 PO_PDOI_PARAMS.g_request.interface_header_id IS NULL)
326 AND (PHI.document_type_code = PO_PDOI_PARAMS.g_request.document_type
327 OR
328 PHI.document_type_code IS NULL)
329 AND (PHI.processing_id IS NULL
330 OR
331 PHI.processing_id <> PO_PDOI_PARAMS.g_processing_id)
332 -- bug5471513
333 -- Catalog uploaded records should only be processed by catalog upload
334 -- request
335 -- bug5463188
336 -- Buyer acceptance process shouldn't worry about the calling module
337 AND ( PO_PDOI_PARAMS.g_request.process_code =
338 PO_PDOI_CONSTANTS.g_process_code_NOTIFIED
339 OR
340 DECODE (PHI.interface_source_code,
341 PO_PDOI_CONSTANTS.g_CALL_MOD_CATALOG_UPLOAD,
342 1, 2) =
343 DECODE (PO_PDOI_PARAMS.g_request.calling_module,
344 PO_PDOI_CONSTANTS.g_CALL_MOD_CATALOG_UPLOAD,
345 1, 2));
346
347
348 l_intf_header_id_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
349 l_process_code_tbl PO_TBL_VARCHAR30 := PO_TBL_VARCHAR30();
350 l_request_id_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
351
352 l_intf_line_id_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
353 BEGIN
354 d_position := 0;
355
356 IF (PO_LOG.d_proc) THEN
357 PO_LOG.proc_begin (d_module);
358 END IF;
359
360 -- <MOAC R12 START>
361 -- ECO 4420269
362 -- If batch id is specified, update the records that match the batch id but
363 -- do not have org_id specified.
364 IF (PO_PDOI_PARAMS.g_request.batch_id IS NOT NULL) THEN
365
366 UPDATE po_headers_interface PHI
367 SET PHI.org_id = PO_PDOI_PARAMS.g_request.org_id
368 WHERE PHI.batch_id = PO_PDOI_PARAMS.g_request.batch_id
369 AND PHI.org_id IS NULL;
370
371 IF (PO_LOG.d_stmt) THEN
372 PO_LOG.stmt(d_module, d_position, 'updatec org id for ' ||
373 SQL%ROWCOUNT || ' records.');
374 END IF;
375 END IF;
376
377 d_position := 10;
378 OPEN c_interface_headers;
379
380 LOOP
381 d_position := 20;
382 FETCH c_interface_headers
383 BULK COLLECT
384 INTO l_intf_header_id_tbl,
385 l_process_code_tbl,
386 l_request_id_tbl
387 LIMIT PO_PDOI_CONSTANTS.g_DEF_BATCH_SIZE;
388
389 EXIT WHEN l_intf_header_id_tbl.COUNT = 0;
390
391 -- Filter the list further more to only return records that are
392 -- truly process-able
393 get_processable_records
394 ( x_intf_header_id_tbl => l_intf_header_id_tbl,
395 p_process_code_tbl => l_process_code_tbl,
396 p_request_id_tbl => l_request_id_tbl
397 );
398
399 d_position := 30;
400 -- Header level assignment
401 FORALL i IN 1..l_intf_header_id_tbl.COUNT
402 UPDATE po_headers_interface
403 SET processing_id = PO_PDOI_PARAMS.g_processing_id,
404 process_code = PO_PDOI_CONSTANTS.g_process_code_IN_PROCESS,
405 processing_round_num = NULL, -- reset processing number
406 request_id = FND_GLOBAL.conc_request_id,
407 approval_status = NVL(approval_status,
408 PO_PDOI_PARAMS.g_request.approved_status)
409 WHERE interface_header_id = l_intf_header_id_tbl(i);
410
411 IF (PO_LOG.d_stmt) THEN
412 PO_LOG.stmt(d_module, d_position, 'after header assignment. Updated ' ||
413 SQL%ROWCOUNT || ' records');
414 END IF;
415
416 d_position := 40;
417 -- Line level assignment
418 FORALL i IN 1..l_intf_header_id_tbl.COUNT
419 UPDATE po_lines_interface
420 SET processing_id = PO_PDOI_PARAMS.g_processing_id,
421 action = DECODE (action,
422 PO_PDOI_CONSTANTS.g_action_ADD, action,
423 NULL), -- null out process code unless it is force add
424 process_code = DECODE (PO_PDOI_PARAMS.g_request.process_code,
425 PO_PDOI_CONSTANTS.g_PROCESS_CODE_NOTIFIED,
426 PO_PDOI_CONSTANTS.g_PROCESS_CODE_PENDING,
427 process_code) -- bug5149827
428 WHERE interface_header_id = l_intf_header_id_tbl(i)
429 AND (PO_PDOI_PARAMS.g_request.process_code = process_code
430 OR
431 ( NVL(PO_PDOI_PARAMS.g_request.process_code,
432 PO_PDOI_CONSTANTS.g_PROCESS_CODE_PENDING) <>
433 PO_PDOI_CONSTANTS.g_PROCESS_CODE_NOTIFIED AND
434 NVL(process_code, PO_PDOI_CONSTANTS.g_PROCESS_CODE_PENDING)
435 IN (PO_PDOI_CONSTANTS.g_PROCESS_CODE_PENDING,
436 PO_PDOI_CONSTANTS.g_PROCESS_CODE_VAL_AND_REJECT)))
437 AND (NVL(process_code, PO_PDOI_CONSTANTS.g_PROCESS_CODE_PENDING)
438 IN (PO_PDOI_CONSTANTS.g_PROCESS_CODE_PENDING,
439 PO_PDOI_CONSTANTS.g_PROCESS_CODE_VAL_AND_REJECT)
440 OR
441 (process_code = PO_PDOI_CONSTANTS.g_PROCESS_CODE_NOTIFIED AND
442 NVL(price_chg_accept_flag, 'N') = 'Y'))
443 RETURNING interface_line_id
444 BULK COLLECT INTO l_intf_line_id_tbl;
445
446
447 IF (PO_LOG.d_stmt) THEN
448 PO_LOG.stmt(d_module, d_position, 'after line assignment. Updated ' ||
449 SQL%ROWCOUNT || ' records');
450 END IF;
451
452 d_position := 50;
453 FORALL i IN 1..l_intf_line_id_tbl.COUNT
454 UPDATE po_line_locations_interface
455 SET processing_id = PO_PDOI_PARAMS.g_processing_id
456 WHERE interface_line_id = l_intf_line_id_tbl(i);
457
458 IF (PO_LOG.d_stmt) THEN
459 PO_LOG.stmt(d_module, d_position, 'after line location assignment. ' ||
460 ' Updated ' || SQL%ROWCOUNT || ' records');
461 END IF;
462
463 d_position := 60;
464 FORALL i IN 1..l_intf_line_id_tbl.COUNT
465 UPDATE po_price_diff_interface
466 SET processing_id = PO_PDOI_PARAMS.g_processing_id
467 WHERE interface_line_id = l_intf_line_id_tbl(i);
468
469 IF (PO_LOG.d_stmt) THEN
470 PO_LOG.stmt(d_module, d_position, 'after price diff assignment. ' ||
471 'Updated ' || SQL%ROWCOUNT || ' records');
472 END IF;
473
474 d_position := 70;
475 IF (PO_PDOI_PARAMS.g_request.document_type =
476 PO_PDOI_CONSTANTS.g_DOC_TYPE_STANDARD) THEN
477 FORALL i IN 1..l_intf_line_id_tbl.COUNT
478 UPDATE po_distributions_interface
479 SET processing_id = PO_PDOI_PARAMS.g_processing_id
480 WHERE interface_line_id = l_intf_line_id_tbl(i);
481
482 IF (PO_LOG.d_stmt) THEN
483 PO_LOG.stmt(d_module, d_position, 'after distirbution assignment. ' ||
484 'Updated ' || SQL%ROWCOUNT || ' records');
485 END IF;
486 END IF;
487
488 d_position := 80;
489 IF (PO_PDOI_PARAMS.g_request.document_type <>
490 PO_PDOI_CONSTANTS.g_DOC_TYPE_STANDARD) THEN
491
492 d_position := 90;
493 FORALL i IN 1..l_intf_line_id_tbl.COUNT
494 UPDATE po_attr_values_interface
495 SET processing_id = PO_PDOI_PARAMS.g_processing_id
496 WHERE interface_line_id = l_intf_line_id_tbl(i);
497
498 IF (PO_LOG.d_stmt) THEN
499 PO_LOG.stmt(d_module, d_position, 'after attr value assignment. ' ||
500 'Updated ' || SQL%ROWCOUNT || ' records');
501 END IF;
502
503 d_position := 100;
504 FORALL i IN 1..l_intf_line_id_tbl.COUNT
505 UPDATE po_attr_values_tlp_interface
506 SET processing_id = PO_PDOI_PARAMS.g_processing_id
507 WHERE interface_line_id = l_intf_line_id_tbl(i);
508
509 IF (PO_LOG.d_stmt) THEN
510 PO_LOG.stmt(d_module, d_position, 'after attr values tlp assignment.' ||
511 ' Updated ' || SQL%ROWCOUNT || ' records');
512 END IF;
513 END IF;
514
515 d_position := 110;
516
517 END LOOP;
518
519 CLOSE c_interface_headers;
520
521 IF (PO_LOG.d_proc) THEN
522 PO_LOG.proc_end (d_module);
523 END IF;
524
525 EXCEPTION
526 WHEN OTHERS THEN
527 IF (c_interface_headers%ISOPEN) THEN
528 CLOSE c_interface_headers;
529 END IF;
530
531 PO_MESSAGE_S.add_exc_msg
532 ( p_pkg_name => d_pkg_name,
533 p_procedure_name => d_api_name || '.' || d_position
534 );
535 RAISE;
536 END assign_processing_id;
537
538 -----------------------------------------------------------------------
539 --Start of Comments
540 --Name: get_processable_records
541 --Function:
542 -- Verify that the records are processable by current PDOI run. Records
543 -- that meet the filtering criteria may be unable to be processed if
544 -- there is another PDOI process working on the same interface record.
545 --Parameters:
546 --IN:
547 --p_process_code_tbl
548 -- table of process codes
549 --p_request_id_tbl
550 -- table of request ids that have processed / are processing the records
551 --IN OUT:
552 --x_intf_header_id_tbl
553 -- interface records to be evaluated
554 --OUT:
555 --End of Comments
556 ------------------------------------------------------------------------
557 PROCEDURE get_processable_records
558 ( x_intf_header_id_tbl IN OUT NOCOPY PO_TBL_NUMBER,
559 p_process_code_tbl IN PO_TBL_VARCHAR30,
560 p_request_id_tbl IN PO_TBL_NUMBER
561 ) IS
562
563 d_api_name CONSTANT VARCHAR2(30) := 'get_processable_records';
564 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
565 d_position NUMBER;
566
567 l_tmp_intf_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
568
569 l_old_request_complete VARCHAR2(1);
570
571 BEGIN
572 d_position := 0;
573
574 IF (PO_LOG.d_proc) THEN
575 PO_LOG.proc_begin (d_module, '# of records to eval', x_intf_header_id_tbl.COUNT);
576 END IF;
577
578 FOR i IN 1..x_intf_header_id_tbl.COUNT LOOP
579 d_position := 10;
580
581 IF (p_process_code_tbl(i) = PO_PDOI_CONSTANTS.g_process_code_IN_PROCESS) THEN
582
583 l_old_request_complete := PO_PDOI_UTL.is_old_request_complete
584 ( p_old_request_id => p_request_id_tbl(i)
585 );
586
587 d_position := 20;
588
589 IF (l_old_request_complete = FND_API.G_TRUE) THEN
590 l_tmp_intf_tbl.EXTEND;
591 l_tmp_intf_tbl(l_tmp_intf_tbl.COUNT) := x_intf_header_id_tbl(i);
592 END IF;
593
594 ELSE
595 d_position := 30;
596
597 l_tmp_intf_tbl.EXTEND;
598 l_tmp_intf_tbl(l_tmp_intf_tbl.COUNT) := x_intf_header_id_tbl(i);
599 END IF;
600 END LOOP;
601
602 -- return the list with records that are still being processed filtered.
603 x_intf_header_id_tbl := l_tmp_intf_tbl;
604
605 IF (PO_LOG.d_proc) THEN
606 PO_LOG.proc_end (d_module, '# of processable rec', x_intf_header_id_tbl.COUNT);
607 END IF;
608
609 EXCEPTION
610 WHEN OTHERS THEN
611 PO_MESSAGE_S.add_exc_msg
612 ( p_pkg_name => d_pkg_name,
613 p_procedure_name => d_api_name || '.' || d_position
614 );
615 RAISE;
616 END get_processable_records;
617
618 -- Check some of the general columns in the interface tables
619 -- and make sure that they follow the rules of PDOI
620 -----------------------------------------------------------------------
621 --Start of Comments
622 --Name: validate_interface_values
623 --Function:
624 -- Validate interface values that are required for PDOI to process the records
625 -- properly (e.g. ACTION column)
626 --Parameters:
627 --IN:
628 --IN OUT:
629 --OUT:
630 --End of Comments
631 ------------------------------------------------------------------------
632 PROCEDURE validate_interface_values IS
633
634 d_api_name CONSTANT VARCHAR2(30) := 'validate_interface_values';
635 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
636 d_position NUMBER;
637
638 l_reject_tbl PO_TBL_NUMBER;
639 l_action_tbl PO_TBL_VARCHAR25;
640
641 l_message_name FND_NEW_MESSAGES.message_name%TYPE;
642 BEGIN
643
644 d_position := 0;
645
646 IF (PO_LOG.d_proc) THEN
647 PO_LOG.proc_begin (d_module);
648 END IF;
649
650 -- Check action code
651 SELECT interface_header_id, action
652 BULK COLLECT
653 INTO l_reject_tbl, l_action_tbl
654 FROM po_headers_interface
655 WHERE processing_id = PO_PDOI_PARAMS.g_processing_id
656 AND (action IS NULL
657 OR
658 (PO_PDOI_PARAMS.g_request.document_type IN
659 (PO_PDOI_CONSTANTS.g_DOC_TYPE_BLANKET,
660 PO_PDOI_CONSTANTS.g_DOC_TYPE_QUOTATION) AND
661 action NOT IN (PO_PDOI_CONSTANTS.g_ACTION_ORIGINAL,
662 PO_PDOI_CONSTANTS.g_ACTION_ADD,
663 PO_PDOI_CONSTANTS.g_ACTION_REPLACE,
664 PO_PDOI_CONSTANTS.g_ACTION_UPDATE))
665 OR
666 (PO_PDOI_PARAMS.g_request.document_type = PO_PDOI_CONSTANTS.g_DOC_TYPE_STANDARD AND
667 action NOT IN (PO_PDOI_CONSTANTS.g_ACTION_ORIGINAL,
668 PO_PDOI_CONSTANTS.g_ACTION_ADD,
669 PO_PDOI_CONSTANTS.g_ACTION_UPDATE)));
670
671 d_position := 10;
672
673 IF (PO_LOG.d_stmt) THEN
674 PO_LOG.stmt(d_module, d_position, '# of records to reject:',
675 l_reject_tbl.COUNT);
676 END IF;
677
678 FOR i IN 1..l_reject_tbl.COUNT LOOP
679 IF (PO_PDOI_PARAMS.g_request.document_type = PO_PDOI_CONSTANTS.g_DOC_TYPE_STANDARD
680 AND
681 l_action_tbl(i) = PO_PDOI_CONSTANTS.g_ACTION_REPLACE) THEN
682
683 l_message_name := 'PO_PDOI_STD_ACTION';
684 ELSE
685 l_message_name := 'PO_PDOI_INVALID_ACTION';
686 END IF;
687
688 d_position := 20;
689 PO_PDOI_ERR_UTL.add_fatal_error
690 ( p_interface_header_id => l_reject_tbl(i),
691 p_error_message_name => l_message_name,
692 p_table_name => 'PO_HEADERS_INTERFACE',
693 p_column_name => 'ACTION',
694 p_column_value => l_action_tbl(i),
695 p_token1_name => 'VALUE',
696 p_token1_value => l_action_tbl(i));
697 END LOOP;
698
699 d_position := 30;
700 PO_PDOI_UTL.reject_headers_intf
701 ( p_id_param_type => PO_PDOI_CONSTANTS.g_INTERFACE_HEADER_ID,
702 p_id_tbl => l_reject_tbl,
703 p_cascade => FND_API.G_TRUE);
704
705 IF (PO_LOG.d_proc) THEN
706 PO_LOG.proc_end (d_module);
707 END IF;
708
709 EXCEPTION
710 WHEN OTHERS THEN
711 PO_MESSAGE_S.add_exc_msg
712 ( p_pkg_name => d_pkg_name,
713 p_procedure_name => d_api_name || '.' || d_position
714 );
715 RAISE;
716 END validate_interface_values;
717
718
719 -----------------------------------------------------------------------
720 --Start of Comments
721 --Name: derive_vendor_id
722 --Function:
723 -- Derive vendor id based on vendor name and vendor num, if necessary.
724 --Parameters:
725 --IN:
726 --IN OUT:
727 --OUT:
728 --End of Comments
729 ------------------------------------------------------------------------
730 PROCEDURE derive_vendor_id IS
731
732 d_api_name CONSTANT VARCHAR2(30) := 'derive_vendor_id';
733 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
734 d_position NUMBER;
735
736 l_key NUMBER;
737
738 l_intf_header_id_tbl PO_TBL_NUMBER;
739 l_vendor_name_tbl PO_TBL_VARCHAR2000;
740 l_vendor_num_tbl PO_TBL_VARCHAR30;
741 l_vendor_id_tbl PO_TBL_NUMBER;
742
743 l_reject_list PO_TBL_NUMBER := PO_TBL_NUMBER();
744
745 l_column_name VARCHAR2(30);
746 l_token_value VARCHAR2(200);
747
748 l_ordered_num_list DBMS_SQL.NUMBER_TABLE;
749
750 BEGIN
751 d_position := 0;
752
753 IF (PO_LOG.d_proc) THEN
754 PO_LOG.proc_begin (d_module);
755 END IF;
756
757 l_key := PO_CORE_S.get_session_gt_nextval;
758
759 SELECT interface_header_id,
760 vendor_name,
761 vendor_num,
762 vendor_id
763 BULK COLLECT
764 INTO l_intf_header_id_tbl,
765 l_vendor_name_tbl,
766 l_vendor_num_tbl,
767 l_vendor_id_tbl
768 FROM po_headers_interface PHI
769 WHERE vendor_id IS NULL
770 AND po_header_id IS NULL -- if po_header_id is already provided,
771 -- skip vendor_id derivation as it is
772 -- not needed
773 AND processing_id = PO_PDOI_PARAMS.g_processing_id;
774
775 d_position := 10;
776
777 PO_PDOI_UTL.generate_ordered_num_list
778 ( p_size => l_intf_header_id_tbl.COUNT,
779 x_num_list => l_ordered_num_list
780 );
781
782 PO_PDOI_HEADER_PROCESS_PVT.derive_vendor_id
783 ( p_key => l_key,
784 p_index_tbl => l_ordered_num_list,
785 p_vendor_name_tbl => l_vendor_name_tbl,
786 p_vendor_num_tbl => l_vendor_num_tbl,
787 x_vendor_id_tbl => l_vendor_id_tbl
788 );
789
790 d_position := 20;
791 -- Update vendor_id to headers interface
792 FORALL i IN 1..l_intf_header_id_tbl.COUNT
793 UPDATE po_headers_interface
794 SET vendor_id = l_vendor_id_tbl(i)
795 WHERE interface_header_id = l_intf_header_id_tbl(i)
796 AND l_vendor_id_tbl(i) IS NOT NULL;
797
798 d_position := 30;
799 FOR i IN 1..l_intf_header_id_tbl.COUNT LOOP
800 IF (l_vendor_id_tbl(i) IS NULL) THEN
801
802 IF (l_vendor_num_tbl(i) IS NULL) THEN
803 l_column_name := 'VENDOR_NAME';
804 l_token_value := l_vendor_name_tbl(i);
805 ELSE
806 l_column_name := 'VENDOR_NUM';
807 l_token_value := l_vendor_num_tbl(i);
808 END IF;
809
810 PO_PDOI_ERR_UTL.add_fatal_error
811 ( p_interface_header_id => l_intf_header_id_tbl(i),
812 p_error_message_name => 'PO_PDOI_DERV_ERROR',
813 p_table_name => 'PO_HEADERS_INTERFACE',
814 p_column_name => 'VENDOR_ID',
815 p_column_value => l_vendor_id_tbl(i),
816 p_token1_name => 'COLUMN_NAME',
817 p_token1_value => l_column_name,
818 p_token2_name => 'VALUE',
819 p_token2_value => l_token_value
820 );
821
822 l_reject_list.extend;
823 l_reject_list(l_reject_list.COUNT) := l_intf_header_id_tbl(i);
824 END IF;
825 END LOOP;
826
827 d_position := 40;
828 -- For records that cannot derive vendor id, reject header and its children
829 PO_PDOI_UTL.reject_headers_intf
830 ( p_id_param_type => PO_PDOI_CONSTANTS.g_INTERFACE_HEADER_ID,
831 p_id_tbl => l_reject_list,
832 p_cascade => FND_API.G_TRUE
833 );
834
835 IF (PO_LOG.d_proc) THEN
836 PO_LOG.proc_end (d_module);
837 END IF;
838
839 EXCEPTION
840 WHEN OTHERS THEN
841 PO_MESSAGE_S.add_exc_msg
842 ( p_pkg_name => d_pkg_name,
843 p_procedure_name => d_api_name || '.' || d_position
844 );
845 RAISE;
846 END derive_vendor_id;
847
848
849 -----------------------------------------------------------------------
850 --Start of Comments
851 --Name: verify_action_replace
852 --Function:
853 -- For records with action = 'REPLACE', verify that the action can be
854 -- performed
855 --Parameters:
856 --IN:
857 --IN OUT:
858 --OUT:
859 --End of Comments
860 ------------------------------------------------------------------------
861 PROCEDURE verify_action_replace
862 IS
863
864 d_api_name CONSTANT VARCHAR2(30) := 'verify_action_replace';
865 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
866 d_position NUMBER;
867
868 l_interface_header_id_tbl PO_TBL_NUMBER;
869 l_vendor_id_tbl PO_TBL_NUMBER;
870 l_start_date_tbl PO_TBL_DATE;
871 l_end_date_tbl PO_TBL_DATE;
872 l_vendor_doc_num_tbl PO_TBL_VARCHAR25;
873
874 l_orig_po_header_id_tbl PO_TBL_NUMBER;
875 l_orig_closed_code_tbl PO_TBL_VARCHAR25;
876 l_orig_cancel_flag_tbl PO_TBL_VARCHAR1;
877 l_orig_ga_tbl PO_TBL_VARCHAR1;
878
879 l_doc_active BOOLEAN;
880
881 l_index_for_replacement NUMBER;
882
883 l_error_message_name FND_NEW_MESSAGES.message_name%TYPE;
884
885 l_valid VARCHAR2(1);
886
887 l_final_intf_header_id_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
888 l_final_orig_header_id_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
889 l_reject_list PO_TBL_NUMBER := PO_TBL_NUMBER();
890 BEGIN
891 d_position := 0;
892
893 IF (PO_LOG.d_proc) THEN
894 PO_LOG.proc_begin (d_module);
895 END IF;
896
897 -- For update and replace action, make sure that the document exists
898
899 SELECT interface_header_id,
900 vendor_id,
901 effective_date,
902 expiration_date,
903 vendor_doc_num
904 BULK COLLECT INTO l_interface_header_id_tbl, l_vendor_id_tbl,
905 l_start_date_tbl, l_end_date_tbl, l_vendor_doc_num_tbl
906 FROM po_headers_interface
907 WHERE processing_id = PO_PDOI_PARAMS.g_processing_id
908 AND action = PO_PDOI_CONSTANTS.g_ACTION_REPLACE;
909
910 IF (l_interface_header_id_tbl IS NULL OR l_interface_header_id_tbl.COUNT = 0) THEN
911 d_position := 10;
912 RETURN;
913 END IF;
914
915 d_position := 20;
916 FOR i IN 1..l_interface_header_id_tbl.COUNT LOOP
917 l_valid := FND_API.G_TRUE;
918
919 IF (PO_LOG.d_stmt) THEN
920 PO_LOG.stmt(d_module, d_position, 'checking for ' ||
921 l_interface_header_id_tbl(i));
922 END IF;
923
924 -- start date has to be provided for replace
925 IF l_start_date_tbl(i) IS NULL THEN
926 d_position := 30;
927 PO_PDOI_ERR_UTL.add_fatal_error
928 ( p_interface_header_id => l_interface_header_id_tbl(i),
929 p_error_message_name => 'PO_PDOI_COLUMN_NOT_NULL',
930 p_table_name => 'PO_HEADERS_INTERFACE',
931 p_column_name => 'START_DATE',
932 p_column_value => l_start_date_tbl(i),
933 p_token1_name => 'COLUMN_NAME',
934 p_token1_value => 'START_DATE'
935 );
936
937 l_valid := FND_API.G_FALSE;
938 END IF;
939
940 d_position := 40;
941 -- start date has to be greater than end date
942 IF (TRUNC(l_start_date_tbl(i)) > TRUNC(NVL(l_end_date_tbl(i), l_start_date_tbl(i)))) THEN
943 PO_PDOI_ERR_UTL.add_fatal_error
944 ( p_interface_header_id => l_interface_header_id_tbl(i),
945 p_error_message_name => 'PO_PDOI_INVALID_START_DATE',
946 p_table_name => 'PO_HEADERS_INTERFACE',
947 p_column_name => 'START_DATE',
948 p_column_value => l_start_date_tbl(i),
949 p_token1_name => 'VALUE',
950 p_token1_value => l_start_date_tbl(i)
951 );
952
953 l_valid := FND_API.G_FALSE;
954 END IF;
955
956 d_position := 50;
957
958 IF (l_valid = FND_API.G_TRUE) THEN
959
960 IF (PO_PDOI_PARAMS.g_request.document_type =
961 PO_PDOI_CONSTANTS.g_DOC_TYPE_QUOTATION) THEN
962
963 d_position := 60;
964
965 -- Quotation: Match vendor doc num with quote_vendor_quote_number
966 SELECT po_header_id,
967 NVL(closed_code, 'OPEN'),
968 NVL(cancel_flag, 'N'),
969 NULL
970 BULK COLLECT
971 INTO l_orig_po_header_id_tbl,
972 l_orig_closed_code_tbl,
973 l_orig_cancel_flag_tbl,
974 l_orig_ga_tbl
975 FROM po_headers POH
976 WHERE vendor_id = l_vendor_id_tbl(i)
977 AND quote_vendor_quote_number = l_vendor_doc_num_tbl(i)
978 AND TRUNC(l_start_date_tbl(i)) >= TRUNC(NVL(start_date, SYSDATE))
979 AND TRUNC(NVL(l_end_date_tbl(i), SYSDATE)) <= TRUNC(NVL(end_date, SYSDATE));
980
981 ELSIF (PO_PDOI_PARAMS.g_request.document_type =
982 PO_PDOI_CONSTANTS.g_DOC_TYPE_BLANKET) THEN
983
984 d_position := 70;
985 -- Blanket: Match vendor doc num with vendor_order_num
986 SELECT po_header_id,
987 NVL(closed_code, 'OPEN'),
988 NVL(cancel_flag, 'N'),
989 NVL(global_agreement_flag, 'N')
990 BULK COLLECT
991 INTO l_orig_po_header_id_tbl,
992 l_orig_closed_code_tbl,
993 l_orig_cancel_flag_tbl,
994 l_orig_ga_tbl
995 FROM po_headers POH
996 WHERE vendor_id = l_vendor_id_tbl(i)
997 AND vendor_order_num = l_vendor_doc_num_tbl(i)
998 AND TRUNC(l_start_date_tbl(i)) >= TRUNC(NVL(start_date, SYSDATE))
999 AND TRUNC(NVL(l_end_date_tbl(i), SYSDATE)) <= TRUNC(NVL(end_date, SYSDATE));
1000 END IF;
1001
1002 l_doc_active := FALSE;
1003 l_index_for_replacement := NULL;
1004 l_error_message_name := NULL;
1005
1006 IF (l_orig_po_header_id_tbl.COUNT = 0) THEN
1007 d_position := 80;
1008 l_error_message_name := 'PO_PDOI_INVALID_ORIG_CATALOG';
1009 ELSE
1010 d_position := 90;
1011 -- If there are existing documents with the same vendor doc number info,
1012 -- then we take the active one if there is only one.
1013
1014 FOR j IN 1..l_orig_po_header_id_tbl.COUNT LOOP
1015 IF (l_orig_closed_code_tbl(j) <> 'FINALLY CLOSED' AND
1016 l_orig_cancel_flag_tbl(j) <> 'Y')
1017 THEN
1018 IF (l_doc_active) THEN
1019 -- there is already an active doc. It's an error.
1020 l_error_message_name := 'PO_PDOI_INVAL_MULT_ORIG_CATG';
1021 ELSE
1022 l_doc_active := TRUE;
1023 l_index_for_replacement := j;
1024 END IF;
1025 ELSE
1026 -- inactive
1027 IF (NOT l_doc_active AND l_index_for_replacement IS NULL) THEN
1028 l_index_for_replacement := j;
1029 ELSIF (NOT l_doc_active) THEN
1030 -- matching multiple inactive documents is error as well
1031 l_error_message_name := 'PO_PDOI_INVALID_ORIG_CATALOG';
1032 END IF;
1033 END IF;
1034 END LOOP; -- FOR i in i..l_orig_po_header_id_tbl.COUNT
1035 END IF;
1036
1037 d_position := 100;
1038
1039 IF (l_error_message_name IS NOT NULL) THEN
1040 PO_PDOI_ERR_UTL.add_fatal_error
1041 ( p_interface_header_id => l_interface_header_id_tbl(i),
1042 p_error_message_name => l_error_message_name,
1043 p_table_name => 'PO_HEADERS_INTERFACE',
1044 p_column_name => 'VENDOR_DOC_NUM',
1045 p_column_value => l_vendor_doc_num_tbl(i),
1046 p_token1_name => 'DOC_NUMBER',
1047 p_token1_value => l_vendor_doc_num_tbl(i)
1048 );
1049 l_index_for_replacement := NULL; -- no id to replace
1050 l_valid := FND_API.G_FALSE;
1051 END IF;
1052 END IF;
1053
1054 IF (l_index_for_replacement IS NOT NULL) THEN
1055 d_position := 110;
1056
1057 IF (PO_LOG.d_stmt) THEN
1058 PO_LOG.stmt(d_module, d_position, 'Found doc for replacement: ' ||
1059 l_orig_po_header_id_tbl(l_index_for_replacement));
1060 END IF;
1061
1062 -- For blanket, make sure that all releases should not have release
1063 -- date greater than the start date of the newly replaced blanket, whcih
1064 -- is equivalent to the end date of the old blanket
1065
1066 IF (PO_PDOI_PARAMS.g_request.document_type =
1067 PO_PDOI_CONSTANTS.g_DOC_TYPE_BLANKET) THEN
1068
1069 d_position := 120;
1070 check_release_dates
1071 ( p_interface_header_id => l_interface_header_id_tbl(i),
1072 p_po_header_id => l_orig_po_header_id_tbl(l_index_for_replacement),
1073 p_ga_flag => l_orig_ga_tbl(l_index_for_replacement),
1074 p_new_doc_start_date => l_start_date_tbl(i),
1075 x_valid => l_valid
1076 );
1077 END IF;
1078 END IF;
1079
1080 IF (l_valid = FND_API.G_TRUE) THEN
1081 d_position := 130;
1082
1083 IF (PO_LOG.d_stmt) THEN
1084 PO_LOG.stmt(d_module, d_position, 'Release date check passed');
1085 END IF;
1086
1087 l_final_intf_header_id_tbl.extend;
1088 l_final_intf_header_id_tbl(l_final_intf_header_id_tbl.COUNT) :=
1089 l_interface_header_id_tbl(i);
1090
1091 l_final_orig_header_id_tbl.extend;
1092 l_final_orig_header_id_tbl(l_final_orig_header_id_tbl.COUNT) :=
1093 l_orig_po_header_id_tbl(l_index_for_replacement);
1094 ELSE
1095 d_position := 140;
1096 l_reject_list.extend;
1097 l_reject_list(l_reject_list.COUNT) := l_interface_header_id_tbl(i);
1098 END IF;
1099 END LOOP;
1100
1101 d_position := 150;
1102
1103 -- Set original header id
1104 FORALL i IN 1..l_final_orig_header_id_tbl.COUNT
1105 UPDATE po_headers_interface
1106 SET original_po_header_id = l_final_orig_header_id_tbl(i)
1107 WHERE interface_header_id = l_final_intf_header_id_tbl(i);
1108
1109 d_position := 160;
1110 -- propagate errors to lower level
1111 PO_PDOI_UTL.reject_headers_intf
1112 ( p_id_param_type => PO_PDOI_CONSTANTS.g_INTERFACE_HEADER_ID,
1113 p_id_tbl => l_reject_list,
1114 p_cascade => FND_API.G_TRUE
1115 );
1116
1117 IF (PO_LOG.d_proc) THEN
1118 PO_LOG.proc_end (d_module);
1119 END IF;
1120
1121 EXCEPTION
1122 WHEN OTHERS THEN
1123 PO_MESSAGE_S.add_exc_msg
1124 ( p_pkg_name => d_pkg_name,
1125 p_procedure_name => d_api_name || '.' || d_position
1126 );
1127 RAISE;
1128 END verify_action_replace;
1129
1130
1131 -----------------------------------------------------------------------
1132 --Start of Comments
1133 --Name: check_release_dates
1134 --Function:
1135 -- Given the blanket, check whether there is existing release for the blanket
1136 -- that has release date earlier than the new start date of the blanket
1137 --Parameters:
1138 --IN:
1139 --p_interface_header_id
1140 -- interface header id
1141 --p_po_header_id
1142 -- document to check
1143 --p_ga_flag
1144 -- whether the document is a global agreement or not
1145 --p_new_doc_start_date
1146 -- proposed start date of the document
1147 --IN OUT:
1148 --x_valid
1149 -- FND_API.G_TRUE if the this validation passes
1150 -- FND_API.G_FALSE otherwise
1151 --OUT:
1152 --End of Comments
1153 ------------------------------------------------------------------------
1154 PROCEDURE check_release_dates
1155 ( p_interface_header_id IN NUMBER,
1156 p_po_header_id IN NUMBER,
1157 p_ga_flag IN VARCHAR2,
1158 p_new_doc_start_date IN DATE,
1159 x_valid IN OUT NOCOPY VARCHAR2
1160 ) IS
1161
1162 d_api_name CONSTANT VARCHAR2(30) := 'check_release_dates';
1163 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
1164 d_position NUMBER;
1165
1166 l_rel_exists VARCHAR2(1);
1167 l_exp_date DATE := p_new_doc_start_date - 1;
1168
1169 BEGIN
1170 d_position := 0;
1171
1172 IF (PO_LOG.d_proc) THEN
1173 PO_LOG.proc_begin (d_module);
1174 END IF;
1175
1176 -- if we are expiring a blanket, make sure that none of the releases falls
1177 -- outside of the effective dates of the blanket being expired
1178 IF (p_ga_flag = 'Y') THEN
1179 SELECT MAX('Y')
1180 INTO l_rel_exists
1181 FROM DUAL
1182 WHERE EXISTS (SELECT 'Exists std PO ref the orig GA'
1183 FROM po_lines_all POL,
1184 po_headers_all POH
1185 WHERE POL.from_header_id = p_po_header_id
1186 AND POL.po_header_id = POH.po_header_id
1187 AND POH.creation_date >= l_exp_date);
1188
1189 d_position := 10;
1190
1191 IF (l_rel_exists = 'Y') THEN
1192 PO_PDOI_ERR_UTL.add_fatal_error
1193 ( p_interface_header_id => p_interface_header_id,
1194 p_error_message_name => 'PO_PDOI_GA_ST_DATE_GT_PO_DATE',
1195 p_table_name => 'PO_HEADERS_INTERFACE',
1196 p_column_name => 'EFFECTIVE_DATE',
1197 p_column_value => p_new_doc_start_date
1198 );
1199
1200 x_valid := FND_API.G_FALSE;
1201 END IF;
1202
1203 ELSE
1204 d_position := 20;
1205
1206 SELECT MAX('Y')
1207 INTO l_rel_exists
1208 FROM DUAL
1209 WHERE EXISTS (SELECT 'release exist after expiration date'
1210 FROM po_releases POR
1211 WHERE POR.po_header_id = p_po_header_id
1212 AND POR.release_date >= l_exp_date);
1213
1214 IF (l_rel_exists = 'Y') THEN
1215 PO_PDOI_ERR_UTL.add_fatal_error
1216 ( p_interface_header_id => p_interface_header_id,
1217 p_error_message_name => 'PO_PDOI_ST_DATE_GT_REL_DATE',
1218 p_table_name => 'PO_HEADERS_INTERFACE',
1219 p_column_name => 'EFFECTIVE_DATE',
1220 p_column_value => p_new_doc_start_date
1221 );
1222
1223 x_valid := FND_API.G_FALSE;
1224 END IF;
1225 END IF;
1226
1227 IF (PO_LOG.d_proc) THEN
1228 PO_LOG.proc_end (d_module);
1229 END IF;
1230
1231 EXCEPTION
1232 WHEN OTHERS THEN
1233 PO_MESSAGE_S.add_exc_msg
1234 ( p_pkg_name => d_pkg_name,
1235 p_procedure_name => d_api_name || '.' || d_position
1236 );
1237 RAISE;
1238 END check_release_dates;
1239
1240
1241 -----------------------------------------------------------------------
1242 --Start of Comments
1243 --Name: verify_action_update
1244 --Function:
1245 -- For records with action = 'UPDATE', verify that the action can be
1246 -- performed
1247 --Parameters:
1248 --IN:
1249 --IN OUT:
1250 --OUT:
1251 --End of Comments
1252 ------------------------------------------------------------------------
1253 PROCEDURE verify_action_update IS
1254
1255 d_api_name CONSTANT VARCHAR2(30) := 'verify_action_update';
1256 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
1257 d_position NUMBER;
1258
1259 l_existing_header VARCHAR2(1);
1260
1261 l_valid VARCHAR2(1);
1262
1263 l_doc_type PO_DOCUMENT_TYPES.document_type_code%TYPE;
1264 l_doc_subtype PO_DOCUMENT_TYPES.document_subtype%TYPE;
1265
1266
1267 l_interface_header_id_tbl PO_TBL_NUMBER;
1268 l_vendor_id_tbl PO_TBL_NUMBER;
1269 l_start_date_tbl PO_TBL_DATE;
1270 l_end_date_tbl PO_TBL_DATE;
1271 l_po_header_id_tbl PO_TBL_NUMBER;
1272 l_vendor_doc_num_tbl PO_TBL_VARCHAR25;
1273 l_document_num_tbl PO_TBL_VARCHAR25;
1274
1275 l_message_name FND_NEW_MESSAGES.message_name%TYPE;
1276 l_col_name PO_INTERFACE_ERRORS.column_name%TYPE;
1277 l_col_value PO_INTERFACE_ERRORS.column_value%TYPE;
1278 l_token_name VARCHAR2(100);
1279 l_token_value VARCHAR2(100);
1280 l_doc_num_for_msg_dsp PO_HEADERS_ALL.segment1%TYPE;
1281
1282 l_skip_cat_upload_chk VARCHAR2(1);
1283
1284 l_status_rec PO_STATUS_REC_TYPE;
1285 l_return_status VARCHAR2(1);
1286
1287 l_orig_po_header_id_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
1288 l_orig_consumption_flag_tbl PO_TBL_VARCHAR1 := PO_TBL_VARCHAR1();
1289
1290 l_consigned_consumption_flag PO_HEADERS_ALL.consigned_consumption_flag%TYPE;
1291
1292 l_final_intf_header_id_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
1293 l_final_po_header_id_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
1294
1295 l_reject_list PO_TBL_NUMBER := PO_TBL_NUMBER();
1296 BEGIN
1297 d_position := 0;
1298
1299 IF (PO_LOG.d_proc) THEN
1300 PO_LOG.proc_begin (d_module);
1301 END IF;
1302
1303 SELECT interface_header_id,
1304 vendor_id,
1305 effective_date,
1306 expiration_date,
1307 po_header_id,
1308 vendor_doc_num,
1309 document_num
1310 BULK COLLECT
1311 INTO l_interface_header_id_tbl,
1312 l_vendor_id_tbl,
1313 l_start_date_tbl,
1314 l_end_date_tbl,
1315 l_po_header_id_tbl,
1316 l_vendor_doc_num_tbl,
1317 l_document_num_tbl
1318 FROM po_headers_interface
1319 WHERE processing_id = PO_PDOI_PARAMS.g_processing_id
1320 AND action = PO_PDOI_CONSTANTS.g_ACTION_UPDATE;
1321
1322 IF (l_interface_header_id_tbl IS NULL OR l_interface_header_id_tbl.COUNT = 0) THEN
1323 d_position := 10;
1324 RETURN;
1325 END IF;
1326
1327 IF (PO_PDOI_PARAMS.g_request.calling_module =
1328 PO_PDOI_CONSTANTS.g_call_mod_CATALOG_UPLOAD) THEN
1329 l_skip_cat_upload_chk := FND_API.G_TRUE;
1330 ELSE
1331 l_skip_cat_upload_chk := FND_API.G_FALSE;
1332 END IF;
1333
1334 FOR i IN 1..l_interface_header_id_tbl.COUNT LOOP
1335 l_valid := FND_API.g_TRUE;
1336
1337 IF (PO_LOG.d_stmt) THEN
1338 PO_LOG.stmt(d_module, d_position, 'checking for ' ||
1339 l_interface_header_id_tbl(i));
1340 END IF;
1341
1342 IF (l_po_header_id_tbl(i) IS NOT NULL) THEN
1343 d_position := 20;
1344
1345 IF (PO_LOG.d_stmt) THEN
1346 PO_LOG.stmt(d_module, d_position, 'po_header_id ' ||
1347 l_po_header_id_tbl(i) || 'is provided');
1348 END IF;
1349
1350 -- Make sure that the po_header_id is still valid
1351
1352 SELECT DECODE(MAX(POH.po_header_id), NULL, 'N', 'Y'),
1353 NVL(MAX(POH.consigned_consumption_flag), 'N')
1354 INTO l_existing_header,
1355 l_consigned_consumption_flag
1356 FROM po_headers POH
1357 WHERE POH.po_header_id = l_po_header_id_tbl(i)
1358 AND POH.type_lookup_code = PO_PDOI_PARAMS.g_request.document_type;
1359
1360 IF (l_existing_header = 'N' OR l_consigned_consumption_flag = 'Y') THEN
1361
1362 IF (PO_LOG.d_stmt) THEN
1363 PO_LOG.stmt(d_module, d_position, 'po header id does not exist or' ||
1364 ' document type does not match');
1365 END IF;
1366
1367 l_valid := FND_API.g_FALSE;
1368 END IF;
1369
1370 d_position := 30;
1371
1372 IF (l_valid = FND_API.g_TRUE) THEN
1373 IF (PO_PDOI_PARAMS.g_request.document_type IN
1374 (PO_PDOI_CONSTANTS.g_DOC_TYPE_BLANKET,
1375 PO_PDOI_CONSTANTS.g_DOC_TYPE_STANDARD)) THEN
1376
1377 d_position := 40;
1378
1379 PO_PDOI_UTL.get_processing_doctype_info
1380 ( x_doc_type => l_doc_type,
1381 x_doc_subtype => l_doc_subtype
1382 );
1383
1384 PO_DOCUMENT_CHECKS_GRP.po_status_check
1385 ( p_api_version => 1.0,
1386 p_header_id => l_po_header_id_tbl(i),
1387 p_document_type => l_doc_type,
1388 p_document_subtype => l_doc_subtype,
1389 p_mode => 'CHECK_UPDATEABLE',
1390 p_calling_module => PO_DRAFTS_PVT.g_call_mod_PDOI,
1391 p_role => PO_PDOI_PARAMS.g_request.role,
1392 p_skip_cat_upload_chk => l_skip_cat_upload_chk,
1393 x_po_status_rec => l_status_rec,
1394 x_return_status => l_return_status
1395 );
1396
1397 IF (l_return_status <> FND_API.g_RET_STS_SUCCESS) THEN
1398 d_position := 50;
1399 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1400 ELSE
1401 d_position := 60;
1402 IF (l_status_rec.updatable_flag(1) = 'N') THEN
1403 l_valid := FND_API.g_FALSE;
1404 END IF;
1405 END IF;
1406 END IF;
1407 END IF;
1408
1409 IF (l_valid <> FND_API.g_TRUE) THEN
1410 d_position := 70;
1411
1412 IF (PO_PDOI_PARAMS.g_request.document_type =
1413 PO_PDOI_CONSTANTS.g_DOC_TYPE_STANDARD) THEN
1414 l_message_name := 'PO_PDOI_INVALID_ORIG_STD_PO';
1415 ELSE
1416 l_message_name := 'PO_PDOI_INVALID_ORIG_CATALOG';
1417 END IF;
1418
1419 -- since the message takes in document number rather than
1420 -- po_header_id, we attempt to derive document number
1421 -- from po_header_id
1422 SELECT NVL(MIN(segment1), 'UNKNOWN')
1423 INTO l_doc_num_for_msg_dsp
1424 FROM po_headers_all
1425 WHERE po_header_id = l_po_header_id_tbl(i);
1426
1427 PO_PDOI_ERR_UTL.add_fatal_error
1428 ( p_interface_header_id => l_interface_header_id_tbl(i),
1429 p_error_message_name => l_message_name,
1430 p_table_name => 'PO_HEADERS_INTERFACE',
1431 p_column_name => 'PO_HEADER_ID',
1432 p_column_value => l_po_header_id_tbl(i),
1433 p_token1_name => 'DOC_NUMBER',
1434 p_token1_value => l_doc_num_for_msg_dsp
1435 );
1436 l_valid := FND_API.g_FALSE;
1437
1438 l_reject_list.extend;
1439 l_reject_list(l_reject_list.COUNT) := l_interface_header_id_tbl(i);
1440 END IF;
1441
1442 ELSE -- po_header_id is not provided
1443 d_position := 80;
1444
1445 IF (PO_LOG.d_stmt) THEN
1446 PO_LOG.stmt(d_module, d_position, 'po_header_id is not provided. ' ||
1447 'vendor doc num = ' || l_vendor_doc_num_tbl(i) ||
1448 ', document_num = ' || l_document_num_tbl(i));
1449 END IF;
1450
1451 IF (l_vendor_doc_num_tbl(i) IS NOT NULL) THEN
1452 -- Definitely need to match vendor doc num. Matching document num
1453 -- will be performed, if provided
1454 IF (PO_PDOI_PARAMS.g_request.document_type IN
1455 (PO_PDOI_CONSTANTS.g_DOC_TYPE_STANDARD,
1456 PO_PDOI_CONSTANTS.g_DOC_TYPE_BLANKET)) THEN
1457
1458 d_position := 90;
1459 SELECT POH.po_header_id,
1460 NVL(POH.consigned_consumption_flag, 'N')
1461 BULK COLLECT
1462 INTO l_orig_po_header_id_tbl,
1463 l_orig_consumption_flag_tbl
1464 FROM po_headers POH
1465 WHERE POH.vendor_id = l_vendor_id_tbl(i)
1466 AND POH.vendor_order_num = l_vendor_doc_num_tbl(i)
1467 AND POH.segment1 = NVL(l_document_num_tbl(i), POH.segment1)
1468 AND POH.type_lookup_code = PO_PDOI_PARAMS.g_request.document_type
1469 AND (POH.type_lookup_code = 'STANDARD'
1470 OR
1471 (POH.type_lookup_code = 'BLANKET'
1472 AND TRUNC(NVL(l_start_date_tbl(i), SYSDATE)) >=
1473 TRUNC(NVL(POH.start_date, SYSDATE))
1474 AND TRUNC(nvl(l_end_date_tbl(i), SYSDATE)) <=
1475 TRUNC(nvl(POH.end_date, SYSDATE))))
1476 AND NVL(POH.closed_code, 'OPEN') <> 'FINALLY CLOSED'
1477 AND NVL(POH.cancel_flag, 'N') <> 'Y';
1478
1479 ELSIF (PO_PDOI_PARAMS.g_request.document_type =
1480 PO_PDOI_CONSTANTS.g_DOC_TYPE_QUOTATION) THEN
1481 d_position := 100;
1482 SELECT POH.po_header_id,
1483 NVL(POH.consigned_consumption_flag, 'N')
1484 BULK COLLECT
1485 INTO l_orig_po_header_id_tbl,
1486 l_orig_consumption_flag_tbl
1487 FROM po_headers POH
1488 WHERE POH.vendor_id = l_vendor_id_tbl(i)
1489 AND POH.quote_vendor_quote_number = l_vendor_doc_num_tbl(i)
1490 AND POH.segment1 = NVL(l_document_num_tbl(i), POH.segment1)
1491 AND POH.type_lookup_code = PO_PDOI_PARAMS.g_request.document_type
1492 AND POH.type_lookup_code = 'QUOTATION'
1493 AND TRUNC(NVL(l_start_date_tbl(i), SYSDATE)) >=
1494 TRUNC(NVL(POH.start_date, SYSDATE))
1495 AND TRUNC(nvl(l_end_date_tbl(i), SYSDATE)) <=
1496 TRUNC(nvl(POH.end_date, SYSDATE))
1497 AND NVL(POH.closed_code, 'OPEN') <> 'FINALLY CLOSED'
1498 AND NVL(POH.cancel_flag, 'N') <> 'Y';
1499
1500 END IF;
1501
1502 ELSIF (l_document_num_tbl(i) IS NOT NULL) THEN
1503 d_position := 110;
1504 -- Definitely need to match document num. Matching vendor doc num
1505 -- will be performed, if provided
1506
1507 IF (PO_PDOI_PARAMS.g_request.document_type IN
1508 (PO_PDOI_CONSTANTS.g_DOC_TYPE_STANDARD,
1509 PO_PDOI_CONSTANTS.g_DOC_TYPE_BLANKET)) THEN
1510
1511 SELECT POH.po_header_id,
1512 NVL(POH.consigned_consumption_flag, 'N')
1513 BULK COLLECT
1514 INTO l_orig_po_header_id_tbl,
1515 l_orig_consumption_flag_tbl
1516 FROM po_headers POH
1517 WHERE POH.vendor_id = l_vendor_id_tbl(i)
1518 AND NVL(POH.vendor_order_num, FND_API.G_MISS_CHAR) =
1519 NVL(l_vendor_doc_num_tbl(i),
1520 NVL(POH.vendor_order_num, FND_API.G_MISS_CHAR))
1521 AND POH.segment1 = l_document_num_tbl(i)
1522 AND POH.type_lookup_code = PO_PDOI_PARAMS.g_request.document_type
1523 AND (POH.type_lookup_code = 'STANDARD'
1524 OR
1525 (POH.type_lookup_code = 'BLANKET'
1526 AND TRUNC(NVL(l_start_date_tbl(i), SYSDATE)) >=
1527 TRUNC(NVL(POH.start_date, SYSDATE))
1528 AND TRUNC(nvl(l_end_date_tbl(i), SYSDATE)) <=
1529 TRUNC(nvl(POH.end_date, SYSDATE))))
1530 AND NVL(POH.closed_code, 'OPEN') <> 'FINALLY CLOSED'
1531 AND NVL(POH.cancel_flag, 'N') <> 'Y';
1532
1533 ELSIF (PO_PDOI_PARAMS.g_request.document_type =
1534 PO_PDOI_CONSTANTS.g_DOC_TYPE_QUOTATION) THEN
1535
1536 SELECT POH.po_header_id,
1537 NVL(POH.consigned_consumption_flag, 'N')
1538 BULK COLLECT
1539 INTO l_orig_po_header_id_tbl,
1540 l_orig_consumption_flag_tbl
1541 FROM po_headers POH
1542 WHERE POH.vendor_id = l_vendor_id_tbl(i)
1543 AND NVL(POH.quote_vendor_quote_number, FND_API.G_MISS_CHAR) =
1544 NVL(l_vendor_doc_num_tbl(i),
1545 NVL(POH.quote_vendor_quote_number, FND_API.G_MISS_CHAR))
1546 AND POH.segment1 = l_document_num_tbl(i)
1547 AND POH.type_lookup_code = PO_PDOI_PARAMS.g_request.document_type
1548 AND POH.type_lookup_code = 'QUOTATION'
1549 AND TRUNC(NVL(l_start_date_tbl(i), SYSDATE)) >=
1550 TRUNC(NVL(POH.start_date, SYSDATE))
1551 AND TRUNC(nvl(l_end_date_tbl(i), SYSDATE)) <=
1552 TRUNC(nvl(POH.end_date, SYSDATE))
1553 AND NVL(POH.closed_code, 'OPEN') <> 'FINALLY CLOSED'
1554 AND NVL(POH.cancel_flag, 'N') <> 'Y';
1555
1556 END IF;
1557
1558 END IF;
1559
1560 d_position := 120;
1561
1562 -- derive the following fields for error reporting
1563 l_token_name := 'DOC_NUMBER';
1564 IF (l_document_num_tbl(i) IS NOT NULL AND l_vendor_doc_num_tbl(i) IS NULL) THEN
1565 l_col_name := 'DOCUMENT_NUM';
1566 l_col_value := l_document_num_tbl(i);
1567 l_token_value := l_document_num_tbl(i);
1568 ELSE
1569 l_col_name := 'VENDOR_DOC_NUM';
1570 l_col_value := l_vendor_doc_num_tbl(i);
1571 l_token_value := l_vendor_doc_num_tbl(i);
1572 END IF;
1573
1574 IF (l_orig_po_header_id_tbl.COUNT <> 1) THEN
1575 IF (l_orig_po_header_id_tbl.COUNT = 0) THEN
1576 IF (PO_PDOI_PARAMS.g_request.document_type =
1577 PO_PDOI_CONSTANTS.g_DOC_TYPE_STANDARD) THEN
1578 l_message_name := 'PO_PDOI_INVALID_ORIG_STD_PO';
1579 ELSE
1580 l_message_name := 'PO_PDOI_INVALID_ORIG_CATALOG';
1581 END IF;
1582 ELSE
1583 IF (PO_PDOI_PARAMS.g_request.document_type =
1584 PO_PDOI_CONSTANTS.g_DOC_TYPE_STANDARD) THEN
1585 l_message_name := 'PO_PDOI_MULTIPLE_STD_PO';
1586 ELSE
1587 l_message_name := 'PO_PDOI_INVALID_MULT_ORIG_CATG';
1588 END IF;
1589 END IF;
1590
1591 d_position := 130;
1592
1593 PO_PDOI_ERR_UTL.add_fatal_error
1594 ( p_interface_header_id => l_interface_header_id_tbl(i),
1595 p_error_message_name => l_message_name,
1596 p_table_name => 'PO_HEADERS_INTERFACE',
1597 p_column_name => l_col_name,
1598 p_column_value => l_col_value,
1599 p_token1_name => l_token_name,
1600 p_token1_value => l_token_value
1601 );
1602 l_valid := FND_API.g_FALSE;
1603 END IF;
1604
1605 IF (l_valid = FND_API.g_TRUE) THEN
1606 IF (PO_PDOI_PARAMS.g_request.document_type IN
1607 (PO_PDOI_CONSTANTS.g_DOC_TYPE_BLANKET,
1608 PO_PDOI_CONSTANTS.g_DOC_TYPE_STANDARD)) THEN
1609
1610 d_position := 140;
1611
1612 PO_PDOI_UTL.get_processing_doctype_info
1613 ( x_doc_type => l_doc_type,
1614 x_doc_subtype => l_doc_subtype
1615 );
1616
1617 PO_DOCUMENT_CHECKS_GRP.po_status_check
1618 ( p_api_version => 1.0,
1619 p_header_id => l_orig_po_header_id_tbl(1),
1620 p_document_type => l_doc_type,
1621 p_document_subtype => l_doc_subtype,
1622 p_mode => 'CHECK_UPDATEABLE',
1623 p_calling_module => PO_DRAFTS_PVT.g_call_mod_PDOI,
1624 p_role => PO_PDOI_PARAMS.g_request.role,
1625 p_skip_cat_upload_chk => l_skip_cat_upload_chk,
1626 x_po_status_rec => l_status_rec,
1627 x_return_status => l_return_status
1628 );
1629
1630 IF (l_return_status <> FND_API.g_RET_STS_SUCCESS) THEN
1631 d_position := 150;
1632 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1633 ELSE
1634 IF (l_orig_consumption_flag_tbl(1) = 'Y' OR
1635 l_status_rec.updatable_flag(1) = 'N') THEN
1636
1637 IF (PO_PDOI_PARAMS.g_request.document_type =
1638 PO_PDOI_CONSTANTS.g_DOC_TYPE_STANDARD) THEN
1639 l_message_name := 'PO_PDOI_STD_PO_INVALID_STATUS';
1640 ELSE
1641 l_message_name := 'PO_PDOI_INVALID_ORIG_CATALOG';
1642 END IF;
1643
1644 d_position := 160;
1645 PO_PDOI_ERR_UTL.add_fatal_error
1646 ( p_interface_header_id => l_interface_header_id_tbl(i),
1647 p_error_message_name => l_message_name,
1648 p_table_name => 'PO_HEADERS_INTERFACE',
1649 p_column_name => l_col_name,
1650 p_column_value => l_col_value,
1651 p_token1_name => l_token_name,
1652 p_token1_value => l_token_value
1653 );
1654
1655 l_valid := FND_API.g_FALSE;
1656 END IF;
1657 END IF;
1658 END IF;
1659 END IF;
1660
1661 d_position := 170;
1662 IF (l_valid = FND_API.g_TRUE) THEN
1663 l_final_intf_header_id_tbl.extend;
1664 l_final_intf_header_id_tbl(l_final_intf_header_id_tbl.COUNT) :=
1665 l_interface_header_id_tbl(i);
1666
1667 l_final_po_header_id_tbl.extend;
1668 l_final_po_header_id_tbl(l_final_po_header_id_tbl.COUNT) :=
1669 l_orig_po_header_id_tbl(1);
1670 ELSE
1671 l_reject_list.extend;
1672 l_reject_list(l_reject_list.COUNT) := l_interface_header_id_tbl(i);
1673 END IF;
1674 END IF; -- p_po_header_id_tbl(i) IS NOT NULL
1675
1676 END LOOP;
1677
1678 d_position := 180;
1679 -- Set po header id (document to update)
1680 FORALL i IN 1..l_final_intf_header_id_tbl.COUNT
1681 UPDATE po_headers_interface
1682 SET po_header_id = l_final_po_header_id_tbl(i)
1683 WHERE interface_header_id = l_final_intf_header_id_tbl(i);
1684
1685 d_position := 190;
1686 -- propagate rejection status to lower level
1687 PO_PDOI_UTL.reject_headers_intf
1688 ( p_id_param_type => PO_PDOI_CONSTANTS.g_INTERFACE_HEADER_ID,
1689 p_id_tbl => l_reject_list,
1690 p_cascade => FND_API.G_TRUE
1691 );
1692
1693 IF (PO_LOG.d_proc) THEN
1694 PO_LOG.proc_end (d_module);
1695 END IF;
1696
1697 EXCEPTION
1698 WHEN OTHERS THEN
1699 PO_MESSAGE_S.add_exc_msg
1700 ( p_pkg_name => d_pkg_name,
1701 p_procedure_name => d_api_name || '.' || d_position
1702 );
1703 RAISE;
1704 END verify_action_update;
1705
1706
1707
1708 -----------------------------------------------------------------------
1709 --Start of Comments
1710 --Name: verify_action_original
1711 --Function:
1712 -- For records with action = 'ORIGINAL' or 'ADD', verify that the action can be
1713 -- performed
1714 --Parameters:
1715 --IN:
1716 --IN OUT:
1717 --OUT:
1718 --End of Comments
1719 ------------------------------------------------------------------------
1720 PROCEDURE verify_action_original IS
1721
1722 d_api_name CONSTANT VARCHAR2(30) := 'verify_action_original';
1723 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
1724 d_position NUMBER;
1725
1726
1727 l_po_header_id_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
1728 l_reject_list PO_TBL_NUMBER := PO_TBL_NUMBER();
1729 l_vendor_doc_num_tbl PO_TBL_VARCHAR25 := PO_TBL_VARCHAR25();
1730 BEGIN
1731 d_position := 0;
1732
1733 IF (PO_LOG.d_proc) THEN
1734 PO_LOG.proc_begin (d_module);
1735 END IF;
1736
1737 IF (PO_PDOI_PARAMS.g_request.document_type =
1738 PO_PDOI_CONSTANTS.g_DOC_TYPE_QUOTATION) THEN
1739
1740 SELECT POH.po_header_id,
1741 PHI.interface_header_id,
1742 PHI.vendor_doc_num
1743 BULK COLLECT
1744 INTO l_po_header_id_tbl,
1745 l_reject_list,
1746 l_vendor_doc_num_tbl
1747 FROM po_headers POH,
1748 po_headers_interface PHI
1749 WHERE POH.vendor_id = PHI.vendor_id
1750 AND POH.quote_vendor_quote_number = PHI.vendor_doc_Num
1751 AND TRUNC (NVL(PHI.effective_date, SYSDATE)) >=
1752 TRUNC (NVL(POH.start_date, SYSDATE))
1753 AND TRUNC (NVL(PHI.expiration_date, SYSDATE)) <=
1754 TRUNC (NVL(POH.end_date, SYSDATE))
1755 AND NVL(POH.closed_code, 'OPEN') <> 'FINALLY CLOSED'
1756 AND NVL(POH.cancel_flag, 'N') <> 'Y'
1757 AND PHI.processing_id = PO_PDOI_PARAMS.g_processing_id
1758 AND PHI.action IN (PO_PDOI_CONSTANTS.g_ACTION_ORIGINAL,
1759 PO_PDOI_CONSTANTS.g_ACTION_ADD);
1760
1761 ELSIF (PO_PDOI_PARAMS.g_request.document_type =
1762 PO_PDOI_CONSTANTS.g_DOC_TYPE_BLANKET) THEN
1763
1764 SELECT POH.po_header_id,
1765 PHI.interface_header_id,
1766 PHI.vendor_doc_num
1767 BULK COLLECT
1768 INTO l_po_header_id_tbl,
1769 l_reject_list,
1770 l_vendor_doc_num_tbl
1771 FROM po_headers POH,
1772 po_headers_interface PHI
1773 WHERE POH.vendor_id = PHI.vendor_id
1774 AND POH.vendor_order_num = PHI.vendor_doc_Num
1775 AND TRUNC (NVL(PHI.effective_date, SYSDATE)) >=
1776 TRUNC (NVL(POH.start_date, SYSDATE))
1777 AND TRUNC (NVL(PHI.expiration_date, SYSDATE)) <=
1778 TRUNC (NVL(POH.end_date, SYSDATE))
1779 AND NVL(POH.closed_code, 'OPEN') <> 'FINALLY CLOSED'
1780 AND NVL(POH.cancel_flag, 'N') <> 'Y'
1781 AND PHI.processing_id = PO_PDOI_PARAMS.g_processing_id
1782 AND PHI.action IN (PO_PDOI_CONSTANTS.g_ACTION_ORIGINAL,
1783 PO_PDOI_CONSTANTS.g_ACTION_ADD);
1784
1785 END IF;
1786
1787 d_position := 10;
1788
1789 FOR i IN 1..l_reject_list.COUNT LOOP
1790 PO_PDOI_ERR_UTL.add_fatal_error
1791 ( p_interface_header_id => l_reject_list(i),
1792 p_error_message_name => 'PO_PDOI_CATG_ALREADY_EXISTS',
1793 p_table_name => 'PO_HEADERS_INTERFACE',
1794 p_column_name => 'VENDOR_DOC_NUM',
1795 p_column_value => l_vendor_doc_num_tbl(i),
1796 p_token1_name => 'DOC_NUMBER',
1797 p_token1_value => l_vendor_doc_num_tbl(i)
1798 );
1799 END LOOP;
1800
1801 d_position := 20;
1802
1803 -- propagate rejection status to lower level for each document getting
1804 -- rejected
1805 PO_PDOI_UTL.reject_headers_intf
1806 ( p_id_param_type => PO_PDOI_CONSTANTS.g_INTERFACE_HEADER_ID,
1807 p_id_tbl => l_reject_list,
1808 p_cascade => FND_API.G_TRUE
1809 );
1810
1811 IF (PO_LOG.d_proc) THEN
1812 PO_LOG.proc_end (d_module);
1813 END IF;
1814 EXCEPTION
1815 WHEN OTHERS THEN
1816 PO_MESSAGE_S.add_exc_msg
1817 ( p_pkg_name => d_pkg_name,
1818 p_procedure_name => d_api_name || '.' || d_position
1819 );
1820 RAISE;
1821 END verify_action_original;
1822
1823
1824
1825 -----------------------------------------------------------------------
1826 --Start of Comments
1827 --Name: assign_po_header_id
1828 --Function:
1829 -- For interface records that yield new documents to be created in the
1830 -- system, assign po_header_id from sequence
1831 --Parameters:
1832 --IN:
1833 --IN OUT:
1834 --OUT:
1835 --End of Comments
1836 ------------------------------------------------------------------------
1837 PROCEDURE assign_po_header_id IS
1838
1839 d_api_name CONSTANT VARCHAR2(30) := 'assign_po_header_id';
1840 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
1841 d_position NUMBER;
1842
1843 BEGIN
1844
1845 d_position := 0;
1846
1847 IF (PO_LOG.d_proc) THEN
1848 PO_LOG.proc_begin (d_module);
1849 END IF;
1850
1851 -- For ORIGINAL, ADD or REPLACE action, new document will be created
1852 -- Need to assign a new po_header_id
1853 UPDATE po_headers_interface
1854 SET po_header_id = PO_HEADERS_S.nextval
1855 WHERE processing_id = PO_PDOI_PARAMS.g_processing_id
1856 AND po_header_id IS NULL
1857 AND action IN (PO_PDOI_CONSTANTS.g_ACTION_ORIGINAL,
1858 PO_PDOI_CONSTANTS.g_ACTION_ADD,
1859 PO_PDOI_CONSTANTS.g_ACTION_REPLACE);
1860
1861 IF (PO_LOG.d_proc) THEN
1862 PO_LOG.proc_end (d_module);
1863 END IF;
1864
1865 EXCEPTION
1866 WHEN OTHERS THEN
1867 PO_MESSAGE_S.add_exc_msg
1868 ( p_pkg_name => d_pkg_name,
1869 p_procedure_name => d_api_name || '.' || d_position
1870 );
1871 RAISE;
1872 END assign_po_header_id;
1873
1874
1875 -----------------------------------------------------------------------
1876 --Start of Comments
1877 --Name: populate_line_loc_interface
1878 --Function:
1879 -- For line interface records that require line location to be populated
1880 -- (indicated by line_loc_populated_flag <> 'Y'), populate a record into
1881 -- line locations interface, using the attribute values from lines
1882 -- interface
1883 --Parameters:
1884 --IN:
1885 --IN OUT:
1886 --OUT:
1887 --End of Comments
1888 ------------------------------------------------------------------------
1889 PROCEDURE populate_line_loc_interface IS
1890
1891 d_api_name CONSTANT VARCHAR2(30) := 'populate_line_loc_interface';
1892 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
1893 d_position NUMBER;
1894
1895 --SQL What: Get all the lines interface records requiring line location
1896 -- interface records defaulting
1897 --SQL Why: User may Line Location information only to lines interface. In this
1898 -- case we need to populate the information to line location information
1899 -- for processing later on
1900 CURSOR c_line_intf IS
1901 SELECT rowid
1902 FROM po_lines_interface
1903 WHERE processing_id = PO_PDOI_PARAMS.g_processing_id
1904 AND NVL(line_loc_populated_flag, 'N') = 'N';
1905
1906 --SQL What: Get line location records that are just being populated
1907 --SQL Why: Need to populate interface_line_location_id to po distributions and
1908 -- po price differentials that were originally only attached to the
1909 -- lines interface records.
1910 CURSOR c_line_loc_intf IS
1911 SELECT PLLI.interface_line_id,
1912 PLLI.interface_line_location_id
1913 FROM po_line_locations_interface PLLI,
1914 po_lines_interface PLI
1915 WHERE PLLI.processing_id = PO_PDOI_PARAMS.g_processing_id
1916 AND PLLI.interface_line_id = PLI.interface_line_id
1917 AND NVL(PLI.line_loc_populated_flag, 'N') = 'N';
1918
1919 l_rowid_tbl DBMS_SQL.urowid_table;
1920 l_intf_line_tbl PO_TBL_NUMBER;
1921 l_intf_line_loc_tbl PO_TBL_NUMBER;
1922 BEGIN
1923 d_position := 0;
1924
1925 IF (PO_LOG.d_proc) THEN
1926 PO_LOG.proc_begin (d_module);
1927 END IF;
1928
1929 OPEN c_line_intf;
1930
1931 LOOP
1932 FETCH c_line_intf
1933 BULK COLLECT
1934 INTO l_rowid_tbl
1935 LIMIT PO_PDOI_CONSTANTS.g_DEF_BATCH_SIZE;
1936
1937 d_position := 10;
1938
1939 EXIT WHEN l_rowid_tbl.COUNT = 0;
1940
1941 IF (PO_LOG.d_stmt) THEN
1942 PO_LOG.stmt(d_module, d_position, 'number of lines fetched: ' ||
1943 l_rowid_tbl.COUNT);
1944 END IF;
1945
1946 FORALL i IN 1..l_rowid_tbl.COUNT
1947 INSERT INTO po_line_locations_interface
1948 (
1949 interface_line_location_id,
1950 interface_header_id,
1951 interface_line_id,
1952 processing_id,
1953 process_code,
1954 line_location_id,
1955 shipment_type,
1956 shipment_num,
1957 ship_to_organization_id,
1958 ship_to_organization_code,
1959 ship_to_location_id,
1960 ship_to_location,
1961 terms_id,
1962 payment_terms,
1963 qty_rcv_exception_code,
1964 freight_carrier,
1965 fob,
1966 freight_terms,
1967 enforce_ship_to_location_code,
1968 allow_substitute_receipts_flag,
1969 days_early_receipt_allowed,
1970 days_late_receipt_allowed,
1971 receipt_days_exception_code,
1972 invoice_close_tolerance,
1973 receive_close_tolerance,
1974 receiving_routing_id,
1975 receiving_routing,
1976 accrue_on_receipt_flag,
1977 firm_flag,
1978 need_by_date,
1979 promised_date,
1980 from_line_location_id,
1981 inspection_required_flag,
1982 receipt_required_flag,
1983 source_shipment_id,
1984 note_to_receiver,
1985 transaction_flow_header_id,
1986 quantity,
1987 price_discount,
1988 start_date,
1989 end_date,
1990 price_override,
1991 lead_time,
1992 lead_time_unit,
1993 amount,
1994 secondary_quantity,
1995 secondary_unit_of_measure,
1996 attribute_category,
1997 attribute1,
1998 attribute2,
1999 attribute3,
2000 attribute4,
2001 attribute5,
2002 attribute6,
2003 attribute7,
2004 attribute8,
2005 attribute9,
2006 attribute10,
2007 attribute11,
2008 attribute12,
2009 attribute13,
2010 attribute14,
2011 attribute15,
2012 creation_date,
2013 created_by,
2014 last_update_date,
2015 last_updated_by,
2016 last_update_login,
2017 request_id,
2018 program_application_id,
2019 program_id,
2020 program_update_date,
2021 unit_of_measure,
2022 preferred_grade,
2023 taxable_flag,
2024 tax_code_id,
2025 tax_name,
2026 qty_rcv_tolerance
2027 )
2028 SELECT po_line_locations_interface_s.nextval,
2029 PLI.interface_header_id,
2030 PLI.interface_line_id,
2031 PLI.processing_id,
2032 PLI.process_code,
2033 PLI.line_location_id,
2034 PLI.shipment_type,
2035 PLI.shipment_num,
2036 PLI.ship_to_organization_id,
2037 PLI.ship_to_organization_code,
2038 PLI.ship_to_location_id,
2039 PLI.ship_to_location,
2040 PLI.terms_id,
2041 PLI.payment_terms,
2042 PLI.qty_rcv_exception_code,
2043 PLI.freight_carrier,
2044 PLI.fob,
2045 PLI.freight_terms,
2046 PLI.enforce_ship_to_location_code,
2047 PLI.allow_substitute_receipts_flag,
2048 PLI.days_early_receipt_allowed,
2049 PLI.days_late_receipt_allowed,
2050 PLI.receipt_days_exception_code,
2051 PLI.invoice_close_tolerance,
2052 PLI.receive_close_tolerance,
2053 PLI.receiving_routing_id,
2054 PLI.receiving_routing,
2055 PLI.accrue_on_receipt_flag,
2056 PLI.firm_flag,
2057 PLI.need_by_date,
2058 PLI.promised_date,
2059 PLI.from_line_location_id,
2060 PLI.inspection_required_flag,
2061 PLI.receipt_required_flag,
2062 PLI.source_shipment_id,
2063 PLI.note_to_receiver,
2064 PLI.transaction_flow_header_id,
2065 PLI.quantity,
2066 PLI.price_discount,
2067 PLI.effective_date,
2068 PLI.expiration_date,
2069 PLI.unit_price,
2070 PLI.lead_time,
2071 PLI.lead_time_unit,
2072 PLI.amount,
2073 PLI.secondary_quantity,
2074 PLI.secondary_unit_of_measure,
2075 PLI.shipment_attribute_category,
2076 PLI.shipment_attribute1,
2077 PLI.shipment_attribute2,
2078 PLI.shipment_attribute3,
2079 PLI.shipment_attribute4,
2080 PLI.shipment_attribute5,
2081 PLI.shipment_attribute6,
2082 PLI.shipment_attribute7,
2083 PLI.shipment_attribute8,
2084 PLI.shipment_attribute9,
2085 PLI.shipment_attribute10,
2086 PLI.shipment_attribute11,
2087 PLI.shipment_attribute12,
2088 PLI.shipment_attribute13,
2089 PLI.shipment_attribute14,
2090 PLI.shipment_attribute15,
2091 PLI.creation_date,
2092 PLI.created_by,
2093 PLI.last_update_date,
2094 PLI.last_updated_by,
2095 PLI.last_update_login,
2096 PLI.request_id,
2097 PLI.program_application_id,
2098 PLI.program_id,
2099 PLI.program_update_date,
2100 PLI.unit_of_measure,
2101 PLI.preferred_grade,
2102 PLI.taxable_flag,
2103 PLI.tax_code_id,
2104 PLI.tax_name,
2105 PLI.qty_rcv_tolerance
2106 FROM po_lines_interface PLI
2107 WHERE PLI.rowid = l_rowid_tbl(i);
2108
2109 END LOOP;
2110
2111 d_position := 20;
2112
2113 CLOSE c_line_intf;
2114
2115 OPEN c_line_loc_intf;
2116
2117 LOOP
2118 d_position := 30;
2119
2120 FETCH c_line_loc_intf
2121 BULK COLLECT
2122 INTO l_intf_line_tbl,
2123 l_intf_line_loc_tbl
2124 LIMIT PO_PDOI_CONSTANTS.g_DEF_BATCH_SIZE;
2125
2126 EXIT WHEN l_intf_line_tbl.COUNT = 0;
2127
2128 d_position := 40;
2129
2130 FORALL i IN 1..l_intf_line_tbl.COUNT
2131 UPDATE po_distributions_interface
2132 SET interface_line_location_id = l_intf_line_loc_tbl(i)
2133 WHERE interface_line_id = l_intf_line_tbl(i);
2134
2135 FORALL i IN 1..l_intf_line_tbl.COUNT
2136 UPDATE po_price_diff_interface
2137 SET interface_line_location_id = l_intf_line_loc_tbl(i)
2138 WHERE interface_line_id = l_intf_line_tbl(i);
2139 END LOOP;
2140
2141 CLOSE c_line_loc_intf;
2142
2143 d_position := 50;
2144
2145 -- Since the default line location has been populated,
2146 -- populate 'S' to line_loc_populated_flag
2147 UPDATE po_lines_interface
2148 SET line_loc_populated_flag = 'S'
2149 WHERE processing_id = PO_PDOI_PARAMS.g_processing_id
2150 AND NVL(line_loc_populated_flag, 'N') = 'N';
2151
2152 IF (PO_LOG.d_proc) THEN
2153 PO_LOG.proc_end (d_module);
2154 END IF;
2155
2156 EXCEPTION
2157 WHEN OTHERS THEN
2158 IF (c_line_loc_intf%ISOPEN) THEN
2159 CLOSE c_line_loc_intf;
2160 END IF;
2161
2162 PO_MESSAGE_S.add_exc_msg
2163 ( p_pkg_name => d_pkg_name,
2164 p_procedure_name => d_api_name || '.' || d_position
2165 );
2166 RAISE;
2167 END populate_line_loc_interface;
2168
2169 END PO_PDOI_PREPROC_PVT;