DBA Data[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;