[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.26.12020000.3 2013/02/10 19:27:48 vegajula ship $ */
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 /* PDOI for Complex PO Project -- Start */
33 PROCEDURE populate_payitems
34 (p_interface_line_id IN NUMBER,
35 p_style_id IN NUMBER
36 );
37
38 PROCEDURE populate_advance_payitem
39 (p_interface_line_id IN NUMBER
40 );
41
42 PROCEDURE populate_advance_payitem_dist
43 (p_interface_line_location_id IN NUMBER
44 );
45
46 PROCEDURE populate_progress_payitem
47 (p_interface_line_id IN NUMBER
48 );
49
50 PROCEDURE populate_progress_payitem_dist
51 (p_interface_line_location_id IN NUMBER
52 );
53 /* PDOI for Complex PO Project -- End */
54
55 PROCEDURE assign_po_header_id;
56
57 PROCEDURE check_release_dates
58 ( p_interface_header_id IN NUMBER,
59 p_po_header_id IN NUMBER,
60 p_ga_flag IN VARCHAR2,
61 p_new_doc_start_date IN DATE,
62 x_valid IN OUT NOCOPY VARCHAR2
63 );
64
65 --CLM PDOI Integration Starts
66 PROCEDURE derive_line_num;
67
68 PROCEDURE default_group_line_id
69 ( p_intf_header_id_tbl IN PO_TBL_NUMBER,
70 p_intf_line_id_tbl IN PO_TBL_NUMBER ,
71 p_line_num_disp_tbl IN PO_TBL_VARCHAR100,
72 p_is_line_num_disp_valid IN PO_TBL_VARCHAR1,
73 p_group_line_id_tbl OUT NOCOPY PO_TBL_NUMBER
74 );
75
76 PROCEDURE validate_line_num_display
77 ( p_intf_header_id_tbl IN PO_TBL_NUMBER,
78 p_intf_line_id_tbl IN PO_TBL_NUMBER,
79 p_line_num_disp_tbl IN PO_TBL_VARCHAR100,
80 p_clm_info_flag_tbl IN PO_TBL_VARCHAR1,
81 p_is_line_num_disp_valid OUT NOCOPY PO_TBL_VARCHAR1,
82 p_po_line_id_tbl IN PO_TBL_NUMBER
83 );
84 --CLM PDOI Integration Ends
85
86 -------------------------------------------------------
87 -------------- PUBLIC PROCEDURES ----------------------
88 -------------------------------------------------------
89
90 -----------------------------------------------------------------------
91 --Start of Comments
92 --Name: process
93 --Function:
94 -- Main procedure of PRE-PROCESSING in PDOI
95 --Parameters:
96 --IN:
97 --IN OUT:
98 --OUT:
99 --End of Comments
100 ------------------------------------------------------------------------
101 PROCEDURE process IS
102
103 d_api_name CONSTANT VARCHAR2(30) := 'process';
104 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
105 d_position NUMBER;
106
107 BEGIN
108
109 d_position := 0;
110
111 IF (PO_LOG.d_proc) THEN
112 PO_LOG.proc_begin (d_module);
113 END IF;
114
115 PO_TIMING_UTL.start_time (PO_PDOI_CONSTANTS.g_T_PREPROCESSING);
116
117 IF (PO_PDOI_PARAMS.g_request.document_type <>
118 PO_PDOI_CONSTANTS.g_DOC_TYPE_STANDARD
119 AND
120 PO_PDOI_PARAMS.g_request.process_code =
121 PO_PDOI_CONSTANTS.g_PROCESS_CODE_NOTIFIED) THEN
122
123 update_dependent_line_acc_flag; -- bug5149827 - Renamed the procedure
124 END IF;
125
126 d_position := 10;
127 assign_processing_id;
128
129 d_position := 15;
130 --CLM PDOI Integration
131 derive_line_num;
132
133 d_position := 20;
134 validate_interface_values;
135
136 d_position := 30;
137
138 derive_vendor_id; -- have to prepopulate vendor info because catalog
139 -- existence check needs this
140
141 -- For update and replace action, make sure that the document exists
142 -- For ORIGINAL action, make sure that there should not be another document
143 -- in the system with the same document identifiers (e.g. segment1,
144 -- vendor_doc_num, etc.)
145
146 d_position := 40;
147 verify_action_replace;
148
149 d_position := 50;
150 verify_action_update;
151
152 d_position := 60;
153 verify_action_original;
154
155 d_position := 70;
156 populate_line_loc_interface;
157
158 d_position := 80;
159 -- For documents that will get created, assign po_header_id
160 assign_po_header_id;
161
162 d_position := 90;
163 PO_INTERFACE_ERRORS_UTL.flush_errors_tbl;
164
165 d_position := 100;
166 PO_PDOI_UTL.commit_work;
167
168 PO_TIMING_UTL.stop_time (PO_PDOI_CONSTANTS.g_T_PREPROCESSING);
169
170 IF (PO_LOG.d_proc) THEN
171 PO_LOG.proc_end (d_module);
172 END IF;
173
174 EXCEPTION
175 WHEN OTHERS THEN
176 PO_MESSAGE_S.add_exc_msg
177 ( p_pkg_name => d_pkg_name,
178 p_procedure_name => d_api_name || '.' || d_position
179 );
180 RAISE;
181 END process;
182
183
184 -------------------------------------------------------
185 -------------- PRIVATE PROCEDURES ---------------------
186 -------------------------------------------------------
187
188 -- bug5149827
189 -- Renamed the procedure
190
191 -----------------------------------------------------------------------
192 --Start of Comments
193 --Name: update_dependent_line_acc_flag
194 --Function:
195 -- 1. Update price break acceptance flag according to the acceptance status
196 -- of the parent line
197 -- 2. Update lines that have parent interface lne id according to the
198 -- acceptance status of the parent line
199 --Parameters:
200 --IN:
201 --IN OUT:
202 --OUT:
203 --End of Comments
204 ------------------------------------------------------------------------
205 PROCEDURE update_dependent_line_acc_flag IS
206
207 d_api_name CONSTANT VARCHAR2(30) := 'update_dependent_line_acc_flag';
208 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
209 d_position NUMBER;
210
211 l_intf_line_id_tbl PO_TBL_NUMBER;
212 l_price_chg_accept_flag_tbl PO_TBL_VARCHAR1;
213 l_price_break_flag_tbl PO_TBL_VARCHAR1;
214
215 l_current_flag VARCHAR2(1);
216
217 l_update_flag_value_idx_tbl PO_PDOI_UTL.pls_integer_tbl_type :=
218 PO_PDOI_UTL.pls_integer_tbl_type();
219
220 BEGIN
221 d_position := 0;
222
223 IF (PO_LOG.d_proc) THEN
224 PO_LOG.proc_begin (d_module);
225 END IF;
226
227 IF (PO_PDOI_PARAMS.g_request.interface_header_id IS NOT NULL) THEN
228
229 -- (1) Update price break acceptance flag according to the acceptance
230 -- status of the parent line
231
232 SELECT interface_line_id,
233 price_chg_accept_flag,
234 price_break_flag
235 BULK COLLECT
236 INTO l_intf_line_id_tbl,
237 l_price_chg_accept_flag_tbl,
238 l_price_break_flag_tbl
239 FROM po_lines_interface
240 WHERE interface_header_id = PO_PDOI_PARAMS.g_request.interface_header_id
241 AND NVL(process_code, PO_PDOI_CONSTANTS.g_process_code_PENDING) =
242 PO_PDOI_CONSTANTS.g_process_code_NOTIFIED
243 ORDER BY po_line_id, interface_line_id;
244
245 IF (PO_LOG.d_stmt) THEN
246 PO_LOG.stmt(d_module, d_position, 'number of lines with notified status ',
247 l_intf_line_id_tbl.COUNT);
248 END IF;
249
250 d_position := 10;
251
252 FOR i IN 1..l_intf_line_id_tbl.COUNT LOOP
253 IF (PO_LOG.d_stmt) THEN
254 PO_LOG.stmt(d_module, d_position, 'i = ' || i || ', intf_line_id = ' ||
255 l_intf_line_id_tbl(i) || ' change accept flag = ' ||
256 l_price_chg_accept_flag_tbl(i));
257 END IF;
258
259 IF (NVL(l_price_break_flag_tbl(i), 'N') = 'N') THEN
260 -- regular po line
261 l_current_flag := l_price_chg_accept_flag_tbl(i);
262 ELSE
263 -- price break. Need to update
264 l_price_chg_accept_flag_tbl(i) := l_current_flag;
265 l_update_flag_value_idx_tbl.extend;
266 l_update_flag_value_idx_tbl(l_update_flag_value_idx_tbl.COUNT) := i;
267 END IF;
268 END LOOP;
269
270 d_position := 20;
271
272 -- update price change accept flag for price break lines
273 IF (l_update_flag_value_idx_tbl.COUNT > 0) THEN
274 FORALL i IN VALUES OF l_update_flag_value_idx_tbl
275 UPDATE po_lines_interface
276 SET price_chg_accept_flag = l_price_chg_accept_flag_tbl(i)
277 WHERE interface_line_id = l_intf_line_id_tbl(i);
278 END IF;
279
280 d_position := 30;
281
282 -- (2) Update lines that have parent interface lne id according to the
283 -- acceptance status of the parent line
284
285 -- bug5149827
286 -- Set the acceptance status of the child record to be the same as
287 -- the parent
288 UPDATE po_lines_interface lines
289 SET lines.price_chg_accept_flag =
290 ( SELECT parent_lines.price_chg_accept_flag
291 FROM po_lines_interface parent_lines
292 WHERE lines.parent_interface_line_id =
293 parent_lines.interface_line_id )
294 WHERE lines.interface_header_id = PO_PDOI_PARAMS.g_request.interface_header_id
295 AND NVL(lines.process_code, PO_PDOI_CONSTANTS.g_process_code_PENDING) =
296 PO_PDOI_CONSTANTS.g_process_code_NOTIFIED
297 AND lines.parent_interface_line_id IS NOT NULL;
298
299 IF (PO_LOG.d_stmt) THEN
300 PO_LOG.stmt(d_module, d_position, '# of lines updated based on parent_intf_line_id',
301 SQL%ROWCOUNT);
302 END IF;
303
304 d_position := 40;
305
306 -- Reject all records that have not been accepted
307 UPDATE po_lines_interface
308 SET process_code = PO_PDOI_CONSTANTS.g_PROCESS_CODE_REJECTED
309 WHERE interface_header_id = PO_PDOI_PARAMS.g_request.interface_header_id
310 AND process_code = PO_PDOI_CONSTANTS.g_PROCESS_CODE_NOTIFIED
311 AND price_chg_accept_flag = 'N';
312
313 d_position := 30;
314 END IF;
315
316 IF (PO_LOG.d_proc) THEN
317 PO_LOG.proc_end (d_module);
318 END IF;
319
320 EXCEPTION
321 WHEN OTHERS THEN
322 PO_MESSAGE_S.add_exc_msg
323 ( p_pkg_name => d_pkg_name,
324 p_procedure_name => d_api_name || '.' || d_position
325 );
326 RAISE;
327 END update_dependent_line_acc_flag;
328
329 -- determine what records PDOI needs to process in this run and
330 -- assign all those records with a processing_id
331
332 -----------------------------------------------------------------------
333 --Start of Comments
334 --Name: assign_processing_id
335 --Function:
336 -- Assign an internally tracking processing id to identify all the records that
337 -- will be processed in this current PDOI run
338 --Parameters:
339 --IN:
340 --IN OUT:
341 --OUT:
342 --End of Comments
343 ------------------------------------------------------------------------
344 PROCEDURE assign_processing_id IS
345
346 d_api_name CONSTANT VARCHAR2(30) := 'assign_processing_id';
347 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
348 d_position NUMBER;
349
350
351 CURSOR c_interface_headers IS
352 SELECT PHI.interface_header_id,
353 PHI.process_code,
354 PHI.request_id
355 FROM po_headers_interface PHI
356 WHERE PHI.org_id = PO_PDOI_PARAMS.g_request.org_id
357 AND NVL(PHI.process_code, PO_PDOI_CONSTANTS.g_PROCESS_CODE_PENDING) <>
358 PO_PDOI_CONSTANTS.g_PROCESS_CODE_REJECTED
359 AND (PHI.batch_id = PO_PDOI_PARAMS.g_request.batch_id
360 OR
361 PO_PDOI_PARAMS.g_request.batch_id IS NULL)
362 AND (PHI.process_code = PO_PDOI_PARAMS.g_request.process_code
363 OR
364 ( NVL(PO_PDOI_PARAMS.g_request.process_code,
365 PO_PDOI_CONSTANTS.g_process_code_PENDING) <>
366 PO_PDOI_CONSTANTS.g_process_code_NOTIFIED
367 AND
368 PHI.process_code = PO_PDOI_CONSTANTS.g_process_code_IN_PROCESS)
369 OR
370 PHI.process_code IS NULL)
371 AND (PHI.interface_header_id = PO_PDOI_PARAMS.g_request.interface_header_id
372 OR
373 PO_PDOI_PARAMS.g_request.interface_header_id IS NULL)
374 AND (PHI.document_type_code = PO_PDOI_PARAMS.g_request.document_type
375 OR
376 PHI.document_type_code IS NULL)
377 AND (PHI.processing_id IS NULL
378 OR
379 PHI.processing_id <> PO_PDOI_PARAMS.g_processing_id)
380 -- bug5471513
381 -- Catalog uploaded records should only be processed by catalog upload
382 -- request
383 -- bug5463188
384 -- Buyer acceptance process shouldn't worry about the calling module
385 AND ( PO_PDOI_PARAMS.g_request.process_code =
386 PO_PDOI_CONSTANTS.g_process_code_NOTIFIED
387 OR
388 DECODE (PHI.interface_source_code,
389 PO_PDOI_CONSTANTS.g_CALL_MOD_CATALOG_UPLOAD,
390 1, 2) =
391 DECODE (PO_PDOI_PARAMS.g_request.calling_module,
392 PO_PDOI_CONSTANTS.g_CALL_MOD_CATALOG_UPLOAD,
393 1, 2));
394
395
396 l_intf_header_id_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
397 l_process_code_tbl PO_TBL_VARCHAR30 := PO_TBL_VARCHAR30();
398 l_request_id_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
399
400 l_intf_line_id_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
401 BEGIN
402 d_position := 0;
403
404 IF (PO_LOG.d_proc) THEN
405 PO_LOG.proc_begin (d_module);
406 END IF;
407
408 -- <MOAC R12 START>
409 -- ECO 4420269
410 -- If batch id is specified, update the records that match the batch id but
411 -- do not have org_id specified.
412 IF (PO_PDOI_PARAMS.g_request.batch_id IS NOT NULL) THEN
413
414 UPDATE po_headers_interface PHI
415 SET PHI.org_id = PO_PDOI_PARAMS.g_request.org_id
416 WHERE PHI.batch_id = PO_PDOI_PARAMS.g_request.batch_id
417 AND PHI.org_id IS NULL;
418
419 IF (PO_LOG.d_stmt) THEN
420 PO_LOG.stmt(d_module, d_position, 'updatec org id for ' ||
421 SQL%ROWCOUNT || ' records.');
422 END IF;
423 END IF;
424
425 d_position := 10;
426 OPEN c_interface_headers;
427
428 LOOP
429 d_position := 20;
430 FETCH c_interface_headers
431 BULK COLLECT
432 INTO l_intf_header_id_tbl,
433 l_process_code_tbl,
434 l_request_id_tbl
435 LIMIT PO_PDOI_CONSTANTS.g_DEF_BATCH_SIZE;
436
437 EXIT WHEN l_intf_header_id_tbl.COUNT = 0;
438
439 -- Filter the list further more to only return records that are
440 -- truly process-able
441 get_processable_records
442 ( x_intf_header_id_tbl => l_intf_header_id_tbl,
443 p_process_code_tbl => l_process_code_tbl,
444 p_request_id_tbl => l_request_id_tbl
445 );
446
447 d_position := 30;
448 -- Header level assignment
449 FORALL i IN 1..l_intf_header_id_tbl.COUNT
450 UPDATE po_headers_interface
451 SET processing_id = PO_PDOI_PARAMS.g_processing_id,
452 process_code = PO_PDOI_CONSTANTS.g_process_code_IN_PROCESS,
453 processing_round_num = NULL, -- reset processing number
454 request_id = FND_GLOBAL.conc_request_id,
455 approval_status = NVL(approval_status,
456 PO_PDOI_PARAMS.g_request.approved_status)
457 WHERE interface_header_id = l_intf_header_id_tbl(i);
458
459 IF (PO_LOG.d_stmt) THEN
460 PO_LOG.stmt(d_module, d_position, 'after header assignment. Updated ' ||
461 SQL%ROWCOUNT || ' records');
462 END IF;
463
464 d_position := 40;
465 -- Line level assignment
466 FORALL i IN 1..l_intf_header_id_tbl.COUNT
467 UPDATE po_lines_interface
468 SET processing_id = PO_PDOI_PARAMS.g_processing_id,
469 action = DECODE (action,
470 PO_PDOI_CONSTANTS.g_action_ADD, action,
471 NULL), -- null out process code unless it is force add
472 process_code = DECODE (PO_PDOI_PARAMS.g_request.process_code,
473 PO_PDOI_CONSTANTS.g_PROCESS_CODE_NOTIFIED,
474 PO_PDOI_CONSTANTS.g_PROCESS_CODE_PENDING,
475 process_code) -- bug5149827
476 WHERE interface_header_id = l_intf_header_id_tbl(i)
477 AND (PO_PDOI_PARAMS.g_request.process_code = process_code
478 OR
479 ( NVL(PO_PDOI_PARAMS.g_request.process_code,
480 PO_PDOI_CONSTANTS.g_PROCESS_CODE_PENDING) <>
481 PO_PDOI_CONSTANTS.g_PROCESS_CODE_NOTIFIED AND
482 NVL(process_code, PO_PDOI_CONSTANTS.g_PROCESS_CODE_PENDING)
483 IN (PO_PDOI_CONSTANTS.g_PROCESS_CODE_PENDING,
484 PO_PDOI_CONSTANTS.g_PROCESS_CODE_VAL_AND_REJECT)))
485 AND (NVL(process_code, PO_PDOI_CONSTANTS.g_PROCESS_CODE_PENDING)
486 IN (PO_PDOI_CONSTANTS.g_PROCESS_CODE_PENDING,
487 PO_PDOI_CONSTANTS.g_PROCESS_CODE_VAL_AND_REJECT)
488 OR
489 (process_code = PO_PDOI_CONSTANTS.g_PROCESS_CODE_NOTIFIED AND
490 NVL(price_chg_accept_flag, 'N') = 'Y'))
491 RETURNING interface_line_id
492 BULK COLLECT INTO l_intf_line_id_tbl;
493
494
495 IF (PO_LOG.d_stmt) THEN
496 PO_LOG.stmt(d_module, d_position, 'after line assignment. Updated ' ||
497 SQL%ROWCOUNT || ' records');
498 END IF;
499
500 d_position := 50;
501 FORALL i IN 1..l_intf_line_id_tbl.COUNT
502 UPDATE po_line_locations_interface
503 SET processing_id = PO_PDOI_PARAMS.g_processing_id
504 WHERE interface_line_id = l_intf_line_id_tbl(i);
505
506 IF (PO_LOG.d_stmt) THEN
507 PO_LOG.stmt(d_module, d_position, 'after line location assignment. ' ||
508 ' Updated ' || SQL%ROWCOUNT || ' records');
509 END IF;
510
511 d_position := 60;
512 FORALL i IN 1..l_intf_line_id_tbl.COUNT
513 UPDATE po_price_diff_interface
514 SET processing_id = PO_PDOI_PARAMS.g_processing_id
515 WHERE interface_line_id = l_intf_line_id_tbl(i);
516
517 IF (PO_LOG.d_stmt) THEN
518 PO_LOG.stmt(d_module, d_position, 'after price diff assignment. ' ||
519 'Updated ' || SQL%ROWCOUNT || ' records');
520 END IF;
521
522 d_position := 70;
523 IF (PO_PDOI_PARAMS.g_request.document_type =
524 PO_PDOI_CONSTANTS.g_DOC_TYPE_STANDARD) THEN
525 FORALL i IN 1..l_intf_line_id_tbl.COUNT
526 UPDATE po_distributions_interface
527 SET processing_id = PO_PDOI_PARAMS.g_processing_id
528 WHERE interface_line_id = l_intf_line_id_tbl(i);
529
530 IF (PO_LOG.d_stmt) THEN
531 PO_LOG.stmt(d_module, d_position, 'after distirbution assignment. ' ||
532 'Updated ' || SQL%ROWCOUNT || ' records');
533 END IF;
534 END IF;
535
536 d_position := 80;
537 IF (PO_PDOI_PARAMS.g_request.document_type <>
538 PO_PDOI_CONSTANTS.g_DOC_TYPE_STANDARD) THEN
539
540 d_position := 90;
541 FORALL i IN 1..l_intf_line_id_tbl.COUNT
542 UPDATE po_attr_values_interface
543 SET processing_id = PO_PDOI_PARAMS.g_processing_id
544 WHERE interface_line_id = l_intf_line_id_tbl(i);
545
546 IF (PO_LOG.d_stmt) THEN
547 PO_LOG.stmt(d_module, d_position, 'after attr value assignment. ' ||
548 'Updated ' || SQL%ROWCOUNT || ' records');
549 END IF;
550
551 d_position := 100;
552 FORALL i IN 1..l_intf_line_id_tbl.COUNT
553 UPDATE po_attr_values_tlp_interface
554 SET processing_id = PO_PDOI_PARAMS.g_processing_id
555 WHERE interface_line_id = l_intf_line_id_tbl(i);
556
557 IF (PO_LOG.d_stmt) THEN
558 PO_LOG.stmt(d_module, d_position, 'after attr values tlp assignment.' ||
559 ' Updated ' || SQL%ROWCOUNT || ' records');
560 END IF;
561 END IF;
562
563 d_position := 110;
564
565 END LOOP;
566
567 CLOSE c_interface_headers;
568
569 IF (PO_LOG.d_proc) THEN
570 PO_LOG.proc_end (d_module);
571 END IF;
572
573 EXCEPTION
574 WHEN OTHERS THEN
575 IF (c_interface_headers%ISOPEN) THEN
576 CLOSE c_interface_headers;
577 END IF;
578
579 PO_MESSAGE_S.add_exc_msg
580 ( p_pkg_name => d_pkg_name,
581 p_procedure_name => d_api_name || '.' || d_position
582 );
583 RAISE;
584 END assign_processing_id;
585
586 -----------------------------------------------------------------------
587 --Start of Comments
588 --Name: get_processable_records
589 --Function:
590 -- Verify that the records are processable by current PDOI run. Records
591 -- that meet the filtering criteria may be unable to be processed if
592 -- there is another PDOI process working on the same interface record.
593 --Parameters:
594 --IN:
595 --p_process_code_tbl
596 -- table of process codes
597 --p_request_id_tbl
598 -- table of request ids that have processed / are processing the records
599 --IN OUT:
600 --x_intf_header_id_tbl
601 -- interface records to be evaluated
602 --OUT:
603 --End of Comments
604 ------------------------------------------------------------------------
605 PROCEDURE get_processable_records
606 ( x_intf_header_id_tbl IN OUT NOCOPY PO_TBL_NUMBER,
607 p_process_code_tbl IN PO_TBL_VARCHAR30,
608 p_request_id_tbl IN PO_TBL_NUMBER
609 ) IS
610
611 d_api_name CONSTANT VARCHAR2(30) := 'get_processable_records';
612 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
613 d_position NUMBER;
614
615 l_tmp_intf_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
616
617 l_old_request_complete VARCHAR2(1);
618
619 BEGIN
620 d_position := 0;
621
622 IF (PO_LOG.d_proc) THEN
623 PO_LOG.proc_begin (d_module, '# of records to eval', x_intf_header_id_tbl.COUNT);
624 END IF;
625
626 FOR i IN 1..x_intf_header_id_tbl.COUNT LOOP
627 d_position := 10;
628
629 IF (p_process_code_tbl(i) = PO_PDOI_CONSTANTS.g_process_code_IN_PROCESS) THEN
630
631 l_old_request_complete := PO_PDOI_UTL.is_old_request_complete
632 ( p_old_request_id => p_request_id_tbl(i)
633 );
634
635 d_position := 20;
636
637 IF (l_old_request_complete = FND_API.G_TRUE) THEN
638 l_tmp_intf_tbl.EXTEND;
639 l_tmp_intf_tbl(l_tmp_intf_tbl.COUNT) := x_intf_header_id_tbl(i);
640 END IF;
641
642 ELSE
643 d_position := 30;
644
645 l_tmp_intf_tbl.EXTEND;
646 l_tmp_intf_tbl(l_tmp_intf_tbl.COUNT) := x_intf_header_id_tbl(i);
647 END IF;
648 END LOOP;
649
650 -- return the list with records that are still being processed filtered.
651 x_intf_header_id_tbl := l_tmp_intf_tbl;
652
653 IF (PO_LOG.d_proc) THEN
654 PO_LOG.proc_end (d_module, '# of processable rec', x_intf_header_id_tbl.COUNT);
655 END IF;
656
657 EXCEPTION
658 WHEN OTHERS THEN
659 PO_MESSAGE_S.add_exc_msg
660 ( p_pkg_name => d_pkg_name,
661 p_procedure_name => d_api_name || '.' || d_position
662 );
663 RAISE;
664 END get_processable_records;
665
666 -- Check some of the general columns in the interface tables
667 -- and make sure that they follow the rules of PDOI
668 -----------------------------------------------------------------------
669 --Start of Comments
670 --Name: validate_interface_values
671 --Function:
672 -- Validate interface values that are required for PDOI to process the records
673 -- properly (e.g. ACTION column)
674 --Parameters:
675 --IN:
676 --IN OUT:
677 --OUT:
678 --End of Comments
679 ------------------------------------------------------------------------
680 PROCEDURE validate_interface_values IS
681
682 d_api_name CONSTANT VARCHAR2(30) := 'validate_interface_values';
683 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
684 d_position NUMBER;
685
686 l_reject_tbl PO_TBL_NUMBER;
687 l_action_tbl PO_TBL_VARCHAR25;
688
689 l_message_name FND_NEW_MESSAGES.message_name%TYPE;
690 BEGIN
691
692 d_position := 0;
693
694 IF (PO_LOG.d_proc) THEN
695 PO_LOG.proc_begin (d_module);
696 END IF;
697
698 -- Check action code
699 SELECT interface_header_id, action
700 BULK COLLECT
701 INTO l_reject_tbl, l_action_tbl
702 FROM po_headers_interface
703 WHERE processing_id = PO_PDOI_PARAMS.g_processing_id
704 AND (action IS NULL
705 OR
706 (PO_PDOI_PARAMS.g_request.document_type IN
707 (PO_PDOI_CONSTANTS.g_DOC_TYPE_BLANKET,
708 PO_PDOI_CONSTANTS.g_DOC_TYPE_QUOTATION) AND
709 action NOT IN (PO_PDOI_CONSTANTS.g_ACTION_ORIGINAL,
710 PO_PDOI_CONSTANTS.g_ACTION_ADD,
711 PO_PDOI_CONSTANTS.g_ACTION_REPLACE,
712 PO_PDOI_CONSTANTS.g_ACTION_UPDATE))
713 OR
714 (PO_PDOI_PARAMS.g_request.document_type = PO_PDOI_CONSTANTS.g_DOC_TYPE_STANDARD AND
715 action NOT IN (PO_PDOI_CONSTANTS.g_ACTION_ORIGINAL,
716 PO_PDOI_CONSTANTS.g_ACTION_ADD,
717 PO_PDOI_CONSTANTS.g_ACTION_UPDATE)));
718
719 d_position := 10;
720
721 IF (PO_LOG.d_stmt) THEN
722 PO_LOG.stmt(d_module, d_position, '# of records to reject:',
723 l_reject_tbl.COUNT);
724 END IF;
725
726 FOR i IN 1..l_reject_tbl.COUNT LOOP
727 IF (PO_PDOI_PARAMS.g_request.document_type = PO_PDOI_CONSTANTS.g_DOC_TYPE_STANDARD
728 AND
729 l_action_tbl(i) = PO_PDOI_CONSTANTS.g_ACTION_REPLACE) THEN
730
731 l_message_name := 'PO_PDOI_STD_ACTION';
732 ELSE
733 l_message_name := 'PO_PDOI_INVALID_ACTION';
734 END IF;
735
736 d_position := 20;
737 PO_PDOI_ERR_UTL.add_fatal_error
738 ( p_interface_header_id => l_reject_tbl(i),
739 p_error_message_name => l_message_name,
740 p_table_name => 'PO_HEADERS_INTERFACE',
741 p_column_name => 'ACTION',
742 p_column_value => l_action_tbl(i),
743 p_token1_name => 'VALUE',
744 p_token1_value => l_action_tbl(i));
745 END LOOP;
746
747 d_position := 30;
748 PO_PDOI_UTL.reject_headers_intf
749 ( p_id_param_type => PO_PDOI_CONSTANTS.g_INTERFACE_HEADER_ID,
750 p_id_tbl => l_reject_tbl,
751 p_cascade => FND_API.G_TRUE);
752
753 IF (PO_LOG.d_proc) THEN
754 PO_LOG.proc_end (d_module);
755 END IF;
756
757 EXCEPTION
758 WHEN OTHERS THEN
759 PO_MESSAGE_S.add_exc_msg
760 ( p_pkg_name => d_pkg_name,
761 p_procedure_name => d_api_name || '.' || d_position
762 );
763 RAISE;
764 END validate_interface_values;
765
766
767 -----------------------------------------------------------------------
768 --Start of Comments
769 --Name: derive_vendor_id
770 --Function:
771 -- Derive vendor id based on vendor name and vendor num, if necessary.
772 --Parameters:
773 --IN:
774 --IN OUT:
775 --OUT:
776 --End of Comments
777 ------------------------------------------------------------------------
778 PROCEDURE derive_vendor_id IS
779
780 d_api_name CONSTANT VARCHAR2(30) := 'derive_vendor_id';
781 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
782 d_position NUMBER;
783
784 l_key NUMBER;
785
786 l_intf_header_id_tbl PO_TBL_NUMBER;
787 l_vendor_name_tbl PO_TBL_VARCHAR2000;
788 l_vendor_num_tbl PO_TBL_VARCHAR30;
789 l_vendor_id_tbl PO_TBL_NUMBER;
790 --CLM PDOI Project
791 l_clm_source_document_disp_tbl PO_TBL_VARCHAR240;
792 l_clm_source_document_id_tbl PO_TBL_NUMBER;
793 l_doc_type_tbl PO_TBL_VARCHAR30;
794
795 l_reject_list PO_TBL_NUMBER := PO_TBL_NUMBER();
796
797 l_column_name VARCHAR2(30);
798 l_token_value VARCHAR2(200);
799
800 l_ordered_num_list DBMS_SQL.NUMBER_TABLE;
801
802 BEGIN
803 d_position := 0;
804
805 IF (PO_LOG.d_proc) THEN
806 PO_LOG.proc_begin (d_module);
807 END IF;
808
809 l_key := PO_CORE_S.get_session_gt_nextval;
810
811 SELECT interface_header_id,
812 vendor_name,
813 vendor_num,
814 vendor_id,
815 clm_source_document_disp,
816 clm_source_document_id,
817 document_type_code
818 BULK COLLECT
819 INTO l_intf_header_id_tbl,
820 l_vendor_name_tbl,
821 l_vendor_num_tbl,
822 l_vendor_id_tbl,
823 l_clm_source_document_disp_tbl,
824 l_clm_source_document_id_tbl,
825 l_doc_type_tbl
826 FROM po_headers_interface PHI
827 WHERE vendor_id IS NULL
828 AND po_header_id IS NULL -- if po_header_id is already provided,
829 -- skip vendor_id derivation as it is
830 -- not needed
831 AND processing_id = PO_PDOI_PARAMS.g_processing_id;
832
833 d_position := 10;
834
835 PO_PDOI_UTL.generate_ordered_num_list
836 ( p_size => l_intf_header_id_tbl.COUNT,
837 x_num_list => l_ordered_num_list
838 );
839
840 PO_PDOI_HEADER_PROCESS_PVT.derive_vendor_id
841 ( p_key => l_key,
842 p_index_tbl => l_ordered_num_list,
843 p_vendor_name_tbl => l_vendor_name_tbl,
844 p_vendor_num_tbl => l_vendor_num_tbl,
845 x_vendor_id_tbl => l_vendor_id_tbl
846 );
847
848 --CLM PDOI Changes Start
849 d_position := 12;
850 IF Nvl(PO_PDOI_PARAMS.g_request.clm_flag,'N') = 'Y'
851 THEN
852 PO_PDOI_HEADER_PROCESS_PVT.derive_clm_source_document_id
853 (
854 p_key => l_key,
855 p_index_tbl => l_ordered_num_list,
856 p_clm_source_document_disp_tbl => l_clm_source_document_disp_tbl,
857 x_clm_source_document_id_tbl => l_clm_source_document_id_tbl
858 );
859 d_position := 14;
860 PO_PDOI_HEADER_PROCESS_PVT.derive_vendor_id_from_idv
861 (
862 p_key => l_key,
863 p_index_tbl => l_ordered_num_list,
864 p_clm_source_document_id_tbl => l_clm_source_document_id_tbl,
865 p_doc_type_tbl => l_doc_type_tbl,
866 x_vendor_id_tbl => l_vendor_id_tbl
867 );
868 END IF;
869 --CLM PDOI Changes End
870
871 d_position := 20;
872 -- Update vendor_id to headers interface
873 FORALL i IN 1..l_intf_header_id_tbl.COUNT
874 UPDATE po_headers_interface
875 SET vendor_id = l_vendor_id_tbl(i)
876 WHERE interface_header_id = l_intf_header_id_tbl(i)
877 AND l_vendor_id_tbl(i) IS NOT NULL;
878
879 d_position := 30;
880 FOR i IN 1..l_intf_header_id_tbl.COUNT LOOP
881 IF (l_vendor_id_tbl(i) IS NULL) THEN
882
883 IF (l_vendor_num_tbl(i) IS NULL) THEN
884 l_column_name := 'VENDOR_NAME';
885 l_token_value := l_vendor_name_tbl(i);
886 ELSE
887 l_column_name := 'VENDOR_NUM';
888 l_token_value := l_vendor_num_tbl(i);
889 END IF;
890
891 PO_PDOI_ERR_UTL.add_fatal_error
892 ( p_interface_header_id => l_intf_header_id_tbl(i),
893 p_error_message_name => 'PO_PDOI_DERV_ERROR',
894 p_table_name => 'PO_HEADERS_INTERFACE',
895 p_column_name => 'VENDOR_ID',
896 p_column_value => l_vendor_id_tbl(i),
897 p_token1_name => 'COLUMN_NAME',
898 p_token1_value => l_column_name,
899 p_token2_name => 'VALUE',
900 p_token2_value => l_token_value
901 );
902
903 l_reject_list.extend;
904 l_reject_list(l_reject_list.COUNT) := l_intf_header_id_tbl(i);
905 END IF;
906 END LOOP;
907
908 d_position := 40;
909 -- For records that cannot derive vendor id, reject header and its children
910 PO_PDOI_UTL.reject_headers_intf
911 ( p_id_param_type => PO_PDOI_CONSTANTS.g_INTERFACE_HEADER_ID,
912 p_id_tbl => l_reject_list,
913 p_cascade => FND_API.G_TRUE
914 );
915
916 IF (PO_LOG.d_proc) THEN
917 PO_LOG.proc_end (d_module);
918 END IF;
919
920 EXCEPTION
921 WHEN OTHERS THEN
922 PO_MESSAGE_S.add_exc_msg
923 ( p_pkg_name => d_pkg_name,
924 p_procedure_name => d_api_name || '.' || d_position
925 );
926 RAISE;
927 END derive_vendor_id;
928
929
930 -----------------------------------------------------------------------
931 --Start of Comments
932 --Name: verify_action_replace
933 --Function:
934 -- For records with action = 'REPLACE', verify that the action can be
935 -- performed
936 --Parameters:
937 --IN:
938 --IN OUT:
939 --OUT:
940 --End of Comments
941 ------------------------------------------------------------------------
942 PROCEDURE verify_action_replace
943 IS
944
945 d_api_name CONSTANT VARCHAR2(30) := 'verify_action_replace';
946 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
947 d_position NUMBER;
948
949 l_interface_header_id_tbl PO_TBL_NUMBER;
950 l_vendor_id_tbl PO_TBL_NUMBER;
951 l_start_date_tbl PO_TBL_DATE;
952 l_end_date_tbl PO_TBL_DATE;
953 l_vendor_doc_num_tbl PO_TBL_VARCHAR25;
954
955 l_orig_po_header_id_tbl PO_TBL_NUMBER;
956 l_orig_closed_code_tbl PO_TBL_VARCHAR25;
957 l_orig_cancel_flag_tbl PO_TBL_VARCHAR1;
958 l_orig_ga_tbl PO_TBL_VARCHAR1;
959
960 l_doc_active BOOLEAN;
961
962 l_index_for_replacement NUMBER;
963
964 l_error_message_name FND_NEW_MESSAGES.message_name%TYPE;
965
966 l_valid VARCHAR2(1);
967
968 l_final_intf_header_id_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
969 l_final_orig_header_id_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
970 l_reject_list PO_TBL_NUMBER := PO_TBL_NUMBER();
971 BEGIN
972 d_position := 0;
973
974 IF (PO_LOG.d_proc) THEN
975 PO_LOG.proc_begin (d_module);
976 END IF;
977
978 -- For update and replace action, make sure that the document exists
979
980 SELECT interface_header_id,
981 vendor_id,
982 effective_date,
983 expiration_date,
984 vendor_doc_num
985 BULK COLLECT INTO l_interface_header_id_tbl, l_vendor_id_tbl,
986 l_start_date_tbl, l_end_date_tbl, l_vendor_doc_num_tbl
987 FROM po_headers_interface
988 WHERE processing_id = PO_PDOI_PARAMS.g_processing_id
989 AND action = PO_PDOI_CONSTANTS.g_ACTION_REPLACE;
990
991 IF (l_interface_header_id_tbl IS NULL OR l_interface_header_id_tbl.COUNT = 0) THEN
992 d_position := 10;
993 RETURN;
994 END IF;
995
996 d_position := 20;
997 FOR i IN 1..l_interface_header_id_tbl.COUNT LOOP
998 l_valid := FND_API.G_TRUE;
999
1000 IF (PO_LOG.d_stmt) THEN
1001 PO_LOG.stmt(d_module, d_position, 'checking for ' ||
1002 l_interface_header_id_tbl(i));
1003 END IF;
1004
1005 -- start date has to be provided for replace
1006 IF l_start_date_tbl(i) IS NULL THEN
1007 d_position := 30;
1008 PO_PDOI_ERR_UTL.add_fatal_error
1009 ( p_interface_header_id => l_interface_header_id_tbl(i),
1010 p_error_message_name => 'PO_PDOI_COLUMN_NOT_NULL',
1011 p_table_name => 'PO_HEADERS_INTERFACE',
1012 p_column_name => 'START_DATE',
1013 p_column_value => l_start_date_tbl(i),
1014 p_token1_name => 'COLUMN_NAME',
1015 p_token1_value => 'START_DATE'
1016 );
1017
1018 l_valid := FND_API.G_FALSE;
1019 END IF;
1020
1021 d_position := 40;
1022 -- start date has to be greater than end date
1023 IF (TRUNC(l_start_date_tbl(i)) > TRUNC(NVL(l_end_date_tbl(i), l_start_date_tbl(i)))) THEN
1024 PO_PDOI_ERR_UTL.add_fatal_error
1025 ( p_interface_header_id => l_interface_header_id_tbl(i),
1026 p_error_message_name => 'PO_PDOI_INVALID_START_DATE',
1027 p_table_name => 'PO_HEADERS_INTERFACE',
1028 p_column_name => 'START_DATE',
1029 p_column_value => l_start_date_tbl(i),
1030 p_token1_name => 'VALUE',
1031 p_token1_value => l_start_date_tbl(i)
1032 );
1033
1034 l_valid := FND_API.G_FALSE;
1035 END IF;
1036
1037 d_position := 50;
1038
1039 IF (l_valid = FND_API.G_TRUE) THEN
1040
1041 IF (PO_PDOI_PARAMS.g_request.document_type =
1042 PO_PDOI_CONSTANTS.g_DOC_TYPE_QUOTATION) THEN
1043
1044 d_position := 60;
1045
1046 -- Quotation: Match vendor doc num with quote_vendor_quote_number
1047 SELECT po_header_id,
1048 NVL(closed_code, 'OPEN'),
1049 NVL(cancel_flag, 'N'),
1050 NULL
1051 BULK COLLECT
1052 INTO l_orig_po_header_id_tbl,
1053 l_orig_closed_code_tbl,
1054 l_orig_cancel_flag_tbl,
1055 l_orig_ga_tbl
1056 FROM po_headers POH
1057 WHERE vendor_id = l_vendor_id_tbl(i)
1058 AND quote_vendor_quote_number = l_vendor_doc_num_tbl(i)
1059 AND TRUNC(l_start_date_tbl(i)) >= TRUNC(NVL(start_date, SYSDATE))
1060 AND TRUNC(NVL(l_end_date_tbl(i), SYSDATE)) <= TRUNC(NVL(end_date, SYSDATE));
1061
1062 ELSIF (PO_PDOI_PARAMS.g_request.document_type =
1063 PO_PDOI_CONSTANTS.g_DOC_TYPE_BLANKET) THEN
1064
1065 d_position := 70;
1066 -- Blanket: Match vendor doc num with vendor_order_num
1067 SELECT po_header_id,
1068 NVL(closed_code, 'OPEN'),
1069 NVL(cancel_flag, 'N'),
1070 NVL(global_agreement_flag, 'N')
1071 BULK COLLECT
1072 INTO l_orig_po_header_id_tbl,
1073 l_orig_closed_code_tbl,
1074 l_orig_cancel_flag_tbl,
1075 l_orig_ga_tbl
1076 FROM po_headers POH
1077 WHERE vendor_id = l_vendor_id_tbl(i)
1078 AND vendor_order_num = l_vendor_doc_num_tbl(i)
1079 AND TRUNC(l_start_date_tbl(i)) >= TRUNC(NVL(start_date, SYSDATE));
1080 -- for issue 14458735
1081 --AND TRUNC(NVL(l_end_date_tbl(i), SYSDATE)) <= TRUNC(NVL(end_date, SYSDATE));
1082 END IF;
1083
1084 l_doc_active := FALSE;
1085 l_index_for_replacement := NULL;
1086 l_error_message_name := NULL;
1087
1088 IF (l_orig_po_header_id_tbl.COUNT = 0) THEN
1089 d_position := 80;
1090 l_error_message_name := 'PO_PDOI_INVALID_ORIG_CATALOG';
1091 ELSE
1092 d_position := 90;
1093 -- If there are existing documents with the same vendor doc number info,
1094 -- then we take the active one if there is only one.
1095
1096 FOR j IN 1..l_orig_po_header_id_tbl.COUNT LOOP
1097 IF (l_orig_closed_code_tbl(j) <> 'FINALLY CLOSED' AND
1098 l_orig_cancel_flag_tbl(j) <> 'Y')
1099 THEN
1100 IF (l_doc_active) THEN
1101 -- there is already an active doc. It's an error.
1102 l_error_message_name := 'PO_PDOI_INVAL_MULT_ORIG_CATG';
1103 ELSE
1104 l_doc_active := TRUE;
1105 l_index_for_replacement := j;
1106 END IF;
1107 ELSE
1108 -- inactive
1109 IF (NOT l_doc_active AND l_index_for_replacement IS NULL) THEN
1110 l_index_for_replacement := j;
1111 ELSIF (NOT l_doc_active) THEN
1112 -- matching multiple inactive documents is error as well
1113 l_error_message_name := 'PO_PDOI_INVALID_ORIG_CATALOG';
1114 END IF;
1115 END IF;
1116 END LOOP; -- FOR i in i..l_orig_po_header_id_tbl.COUNT
1117 END IF;
1118
1119 d_position := 100;
1120
1121 IF (l_error_message_name IS NOT NULL) THEN
1122 PO_PDOI_ERR_UTL.add_fatal_error
1123 ( p_interface_header_id => l_interface_header_id_tbl(i),
1124 p_error_message_name => l_error_message_name,
1125 p_table_name => 'PO_HEADERS_INTERFACE',
1126 p_column_name => 'VENDOR_DOC_NUM',
1127 p_column_value => l_vendor_doc_num_tbl(i),
1128 p_token1_name => 'DOC_NUMBER',
1129 p_token1_value => l_vendor_doc_num_tbl(i)
1130 );
1131 l_index_for_replacement := NULL; -- no id to replace
1132 l_valid := FND_API.G_FALSE;
1133 END IF;
1134 END IF;
1135
1136 IF (l_index_for_replacement IS NOT NULL) THEN
1137 d_position := 110;
1138
1139 IF (PO_LOG.d_stmt) THEN
1140 PO_LOG.stmt(d_module, d_position, 'Found doc for replacement: ' ||
1141 l_orig_po_header_id_tbl(l_index_for_replacement));
1142 END IF;
1143
1144 -- For blanket, make sure that all releases should not have release
1145 -- date greater than the start date of the newly replaced blanket, whcih
1146 -- is equivalent to the end date of the old blanket
1147
1148 IF (PO_PDOI_PARAMS.g_request.document_type =
1149 PO_PDOI_CONSTANTS.g_DOC_TYPE_BLANKET) THEN
1150
1151 d_position := 120;
1152 check_release_dates
1153 ( p_interface_header_id => l_interface_header_id_tbl(i),
1154 p_po_header_id => l_orig_po_header_id_tbl(l_index_for_replacement),
1155 p_ga_flag => l_orig_ga_tbl(l_index_for_replacement),
1156 p_new_doc_start_date => l_start_date_tbl(i),
1157 x_valid => l_valid
1158 );
1159 END IF;
1160 END IF;
1161
1162 IF (l_valid = FND_API.G_TRUE) THEN
1163 d_position := 130;
1164
1165 IF (PO_LOG.d_stmt) THEN
1166 PO_LOG.stmt(d_module, d_position, 'Release date check passed');
1167 END IF;
1168
1169 l_final_intf_header_id_tbl.extend;
1170 l_final_intf_header_id_tbl(l_final_intf_header_id_tbl.COUNT) :=
1171 l_interface_header_id_tbl(i);
1172
1173 l_final_orig_header_id_tbl.extend;
1174 l_final_orig_header_id_tbl(l_final_orig_header_id_tbl.COUNT) :=
1175 l_orig_po_header_id_tbl(l_index_for_replacement);
1176 ELSE
1177 d_position := 140;
1178 l_reject_list.extend;
1179 l_reject_list(l_reject_list.COUNT) := l_interface_header_id_tbl(i);
1180 END IF;
1181 END LOOP;
1182
1183 d_position := 150;
1184
1185 -- Set original header id
1186 FORALL i IN 1..l_final_orig_header_id_tbl.COUNT
1187 UPDATE po_headers_interface
1188 SET original_po_header_id = l_final_orig_header_id_tbl(i)
1189 WHERE interface_header_id = l_final_intf_header_id_tbl(i);
1190
1191 d_position := 160;
1192 -- propagate errors to lower level
1193 PO_PDOI_UTL.reject_headers_intf
1194 ( p_id_param_type => PO_PDOI_CONSTANTS.g_INTERFACE_HEADER_ID,
1195 p_id_tbl => l_reject_list,
1196 p_cascade => FND_API.G_TRUE
1197 );
1198
1199 IF (PO_LOG.d_proc) THEN
1200 PO_LOG.proc_end (d_module);
1201 END IF;
1202
1203 EXCEPTION
1204 WHEN OTHERS THEN
1205 PO_MESSAGE_S.add_exc_msg
1206 ( p_pkg_name => d_pkg_name,
1207 p_procedure_name => d_api_name || '.' || d_position
1208 );
1209 RAISE;
1210 END verify_action_replace;
1211
1212
1213 -----------------------------------------------------------------------
1214 --Start of Comments
1215 --Name: check_release_dates
1216 --Function:
1217 -- Given the blanket, check whether there is existing release for the blanket
1218 -- that has release date earlier than the new start date of the blanket
1219 --Parameters:
1220 --IN:
1221 --p_interface_header_id
1222 -- interface header id
1223 --p_po_header_id
1224 -- document to check
1225 --p_ga_flag
1226 -- whether the document is a global agreement or not
1227 --p_new_doc_start_date
1228 -- proposed start date of the document
1229 --IN OUT:
1230 --x_valid
1231 -- FND_API.G_TRUE if the this validation passes
1232 -- FND_API.G_FALSE otherwise
1233 --OUT:
1234 --End of Comments
1235 ------------------------------------------------------------------------
1236 PROCEDURE check_release_dates
1237 ( p_interface_header_id IN NUMBER,
1238 p_po_header_id IN NUMBER,
1239 p_ga_flag IN VARCHAR2,
1240 p_new_doc_start_date IN DATE,
1241 x_valid IN OUT NOCOPY VARCHAR2
1242 ) IS
1243
1244 d_api_name CONSTANT VARCHAR2(30) := 'check_release_dates';
1245 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
1246 d_position NUMBER;
1247
1248 l_rel_exists VARCHAR2(1);
1249 l_exp_date DATE := p_new_doc_start_date - 1;
1250
1251 BEGIN
1252 d_position := 0;
1253
1254 IF (PO_LOG.d_proc) THEN
1255 PO_LOG.proc_begin (d_module);
1256 END IF;
1257
1258 -- if we are expiring a blanket, make sure that none of the releases falls
1259 -- outside of the effective dates of the blanket being expired
1260 IF (p_ga_flag = 'Y') THEN
1261 SELECT MAX('Y')
1262 INTO l_rel_exists
1263 FROM DUAL
1264 WHERE EXISTS (SELECT 'Exists std PO ref the orig GA'
1265 FROM po_lines_all POL,
1266 po_headers_all POH
1267 WHERE POL.from_header_id = p_po_header_id
1268 AND POL.po_header_id = POH.po_header_id
1269 AND POH.creation_date >= l_exp_date);
1270
1271 d_position := 10;
1272
1273 IF (l_rel_exists = 'Y') THEN
1274 PO_PDOI_ERR_UTL.add_fatal_error
1275 ( p_interface_header_id => p_interface_header_id,
1276 p_error_message_name => 'PO_PDOI_GA_ST_DATE_GT_PO_DATE',
1277 p_table_name => 'PO_HEADERS_INTERFACE',
1278 p_column_name => 'EFFECTIVE_DATE',
1279 p_column_value => p_new_doc_start_date
1280 );
1281
1282 x_valid := FND_API.G_FALSE;
1283 END IF;
1284
1285 ELSE
1286 d_position := 20;
1287
1288 SELECT MAX('Y')
1289 INTO l_rel_exists
1290 FROM DUAL
1291 WHERE EXISTS (SELECT 'release exist after expiration date'
1292 FROM po_releases POR
1293 WHERE POR.po_header_id = p_po_header_id
1294 AND POR.release_date >= l_exp_date);
1295
1296 IF (l_rel_exists = 'Y') THEN
1297 PO_PDOI_ERR_UTL.add_fatal_error
1298 ( p_interface_header_id => p_interface_header_id,
1299 p_error_message_name => 'PO_PDOI_ST_DATE_GT_REL_DATE',
1300 p_table_name => 'PO_HEADERS_INTERFACE',
1301 p_column_name => 'EFFECTIVE_DATE',
1302 p_column_value => p_new_doc_start_date
1303 );
1304
1305 x_valid := FND_API.G_FALSE;
1306 END IF;
1307 END IF;
1308
1309 IF (PO_LOG.d_proc) THEN
1310 PO_LOG.proc_end (d_module);
1311 END IF;
1312
1313 EXCEPTION
1314 WHEN OTHERS THEN
1315 PO_MESSAGE_S.add_exc_msg
1316 ( p_pkg_name => d_pkg_name,
1317 p_procedure_name => d_api_name || '.' || d_position
1318 );
1319 RAISE;
1320 END check_release_dates;
1321
1322
1323 -----------------------------------------------------------------------
1324 --Start of Comments
1325 --Name: verify_action_update
1326 --Function:
1327 -- For records with action = 'UPDATE', verify that the action can be
1328 -- performed
1329 --Parameters:
1330 --IN:
1331 --IN OUT:
1332 --OUT:
1333 --End of Comments
1334 ------------------------------------------------------------------------
1335 PROCEDURE verify_action_update IS
1336
1337 d_api_name CONSTANT VARCHAR2(30) := 'verify_action_update';
1338 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
1339 d_position NUMBER;
1340
1341 l_existing_header VARCHAR2(1);
1342
1343 l_valid VARCHAR2(1);
1344
1345 l_doc_type PO_DOCUMENT_TYPES.document_type_code%TYPE;
1346 l_doc_subtype PO_DOCUMENT_TYPES.document_subtype%TYPE;
1347
1348
1349 l_interface_header_id_tbl PO_TBL_NUMBER;
1350 l_vendor_id_tbl PO_TBL_NUMBER;
1351 l_start_date_tbl PO_TBL_DATE;
1352 l_end_date_tbl PO_TBL_DATE;
1353 l_po_header_id_tbl PO_TBL_NUMBER;
1354 l_vendor_doc_num_tbl PO_TBL_VARCHAR25;
1355 l_document_num_tbl PO_TBL_VARCHAR25;
1356
1357 l_message_name FND_NEW_MESSAGES.message_name%TYPE;
1358 l_col_name PO_INTERFACE_ERRORS.column_name%TYPE;
1359 l_col_value PO_INTERFACE_ERRORS.column_value%TYPE;
1360 l_token_name VARCHAR2(100);
1361 l_token_value VARCHAR2(100);
1362 l_doc_num_for_msg_dsp PO_HEADERS_ALL.segment1%TYPE;
1363
1364 l_skip_cat_upload_chk VARCHAR2(1);
1365
1366 l_status_rec PO_STATUS_REC_TYPE;
1367 l_return_status VARCHAR2(1);
1368
1369 l_orig_po_header_id_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
1370 l_orig_consumption_flag_tbl PO_TBL_VARCHAR1 := PO_TBL_VARCHAR1();
1371
1372 l_consigned_consumption_flag PO_HEADERS_ALL.consigned_consumption_flag%TYPE;
1373
1374 l_final_intf_header_id_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
1375 l_final_po_header_id_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
1376
1377 l_reject_list PO_TBL_NUMBER := PO_TBL_NUMBER();
1378 BEGIN
1379 d_position := 0;
1380
1381 IF (PO_LOG.d_proc) THEN
1382 PO_LOG.proc_begin (d_module);
1383 END IF;
1384
1385 SELECT interface_header_id,
1386 vendor_id,
1387 effective_date,
1388 expiration_date,
1389 po_header_id,
1390 vendor_doc_num,
1391 document_num
1392 BULK COLLECT
1393 INTO l_interface_header_id_tbl,
1394 l_vendor_id_tbl,
1395 l_start_date_tbl,
1396 l_end_date_tbl,
1397 l_po_header_id_tbl,
1398 l_vendor_doc_num_tbl,
1399 l_document_num_tbl
1400 FROM po_headers_interface
1401 WHERE processing_id = PO_PDOI_PARAMS.g_processing_id
1402 AND action = PO_PDOI_CONSTANTS.g_ACTION_UPDATE;
1403
1404 IF (l_interface_header_id_tbl IS NULL OR l_interface_header_id_tbl.COUNT = 0) THEN
1405 d_position := 10;
1406 RETURN;
1407 END IF;
1408
1409 IF (PO_PDOI_PARAMS.g_request.calling_module =
1410 PO_PDOI_CONSTANTS.g_call_mod_CATALOG_UPLOAD) THEN
1411 l_skip_cat_upload_chk := FND_API.G_TRUE;
1412 ELSE
1413 l_skip_cat_upload_chk := FND_API.G_FALSE;
1414 END IF;
1415
1416 FOR i IN 1..l_interface_header_id_tbl.COUNT LOOP
1417 l_valid := FND_API.g_TRUE;
1418
1419 IF (PO_LOG.d_stmt) THEN
1420 PO_LOG.stmt(d_module, d_position, 'checking for ' ||
1421 l_interface_header_id_tbl(i));
1422 END IF;
1423
1424 IF (l_po_header_id_tbl(i) IS NOT NULL) THEN
1425 d_position := 20;
1426
1427 IF (PO_LOG.d_stmt) THEN
1428 PO_LOG.stmt(d_module, d_position, 'po_header_id ' ||
1429 l_po_header_id_tbl(i) || 'is provided');
1430 END IF;
1431
1432 -- Make sure that the po_header_id is still valid
1433
1434 SELECT DECODE(MAX(POH.po_header_id), NULL, 'N', 'Y'),
1435 NVL(MAX(POH.consigned_consumption_flag), 'N')
1436 INTO l_existing_header,
1437 l_consigned_consumption_flag
1438 FROM po_headers POH
1439 WHERE POH.po_header_id = l_po_header_id_tbl(i)
1440 AND POH.type_lookup_code = PO_PDOI_PARAMS.g_request.document_type;
1441
1442 IF (l_existing_header = 'N' OR l_consigned_consumption_flag = 'Y') THEN
1443
1444 IF (PO_LOG.d_stmt) THEN
1445 PO_LOG.stmt(d_module, d_position, 'po header id does not exist or' ||
1446 ' document type does not match');
1447 END IF;
1448
1449 l_valid := FND_API.g_FALSE;
1450 END IF;
1451
1452 d_position := 30;
1453
1454 IF (l_valid = FND_API.g_TRUE) THEN
1455 IF (PO_PDOI_PARAMS.g_request.document_type IN
1456 (PO_PDOI_CONSTANTS.g_DOC_TYPE_BLANKET,
1457 PO_PDOI_CONSTANTS.g_DOC_TYPE_STANDARD)) THEN
1458
1459 d_position := 40;
1460
1461 PO_PDOI_UTL.get_processing_doctype_info
1462 ( x_doc_type => l_doc_type,
1463 x_doc_subtype => l_doc_subtype
1464 );
1465
1466 PO_DOCUMENT_CHECKS_GRP.po_status_check
1467 ( p_api_version => 1.0,
1468 p_header_id => l_po_header_id_tbl(i),
1469 p_document_type => l_doc_type,
1470 p_document_subtype => l_doc_subtype,
1471 p_mode => 'CHECK_UPDATEABLE',
1472 p_calling_module => PO_DRAFTS_PVT.g_call_mod_PDOI,
1473 p_role => PO_PDOI_PARAMS.g_request.role,
1474 p_skip_cat_upload_chk => l_skip_cat_upload_chk,
1475 x_po_status_rec => l_status_rec,
1476 x_return_status => l_return_status
1477 );
1478
1479 IF (l_return_status <> FND_API.g_RET_STS_SUCCESS) THEN
1480 d_position := 50;
1481 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1482 ELSE
1483 d_position := 60;
1484 IF (l_status_rec.updatable_flag(1) = 'N') THEN
1485 l_valid := FND_API.g_FALSE;
1486 END IF;
1487 END IF;
1488 END IF;
1489 END IF;
1490
1491 IF (l_valid <> FND_API.g_TRUE) THEN
1492 d_position := 70;
1493
1494 IF (PO_PDOI_PARAMS.g_request.document_type =
1495 PO_PDOI_CONSTANTS.g_DOC_TYPE_STANDARD) THEN
1496 l_message_name := 'PO_PDOI_INVALID_ORIG_STD_PO';
1497 ELSE
1498 l_message_name := 'PO_PDOI_INVALID_ORIG_CATALOG';
1499 END IF;
1500
1501 -- since the message takes in document number rather than
1502 -- po_header_id, we attempt to derive document number
1503 -- from po_header_id
1504 SELECT NVL(MIN(segment1), 'UNKNOWN')
1505 INTO l_doc_num_for_msg_dsp
1506 FROM po_headers_all
1507 WHERE po_header_id = l_po_header_id_tbl(i);
1508
1509 PO_PDOI_ERR_UTL.add_fatal_error
1510 ( p_interface_header_id => l_interface_header_id_tbl(i),
1511 p_error_message_name => l_message_name,
1512 p_table_name => 'PO_HEADERS_INTERFACE',
1513 p_column_name => 'PO_HEADER_ID',
1514 p_column_value => l_po_header_id_tbl(i),
1515 p_token1_name => 'DOC_NUMBER',
1516 p_token1_value => l_doc_num_for_msg_dsp
1517 );
1518 l_valid := FND_API.g_FALSE;
1519
1520 l_reject_list.extend;
1521 l_reject_list(l_reject_list.COUNT) := l_interface_header_id_tbl(i);
1522 END IF;
1523
1524 ELSE -- po_header_id is not provided
1525 d_position := 80;
1526
1527 IF (PO_LOG.d_stmt) THEN
1528 PO_LOG.stmt(d_module, d_position, 'po_header_id is not provided. ' ||
1529 'vendor doc num = ' || l_vendor_doc_num_tbl(i) ||
1530 ', document_num = ' || l_document_num_tbl(i));
1531 END IF;
1532
1533 IF (l_vendor_doc_num_tbl(i) IS NOT NULL) THEN
1534 -- Definitely need to match vendor doc num. Matching document num
1535 -- will be performed, if provided
1536 IF (PO_PDOI_PARAMS.g_request.document_type IN
1537 (PO_PDOI_CONSTANTS.g_DOC_TYPE_STANDARD,
1538 PO_PDOI_CONSTANTS.g_DOC_TYPE_BLANKET)) THEN
1539
1540 d_position := 90;
1541 SELECT POH.po_header_id,
1542 NVL(POH.consigned_consumption_flag, 'N')
1543 BULK COLLECT
1544 INTO l_orig_po_header_id_tbl,
1545 l_orig_consumption_flag_tbl
1546 FROM po_headers POH
1547 WHERE POH.vendor_id = l_vendor_id_tbl(i)
1548 AND POH.vendor_order_num = l_vendor_doc_num_tbl(i)
1549 AND POH.segment1 = NVL(l_document_num_tbl(i), POH.segment1)
1550 AND POH.type_lookup_code = PO_PDOI_PARAMS.g_request.document_type
1551 AND (POH.type_lookup_code = 'STANDARD'
1552 OR
1553 (POH.type_lookup_code = 'BLANKET'
1554 AND TRUNC(NVL(l_start_date_tbl(i), SYSDATE)) >=
1555 TRUNC(NVL(POH.start_date, SYSDATE))
1556 AND TRUNC(nvl(l_end_date_tbl(i), SYSDATE)) <=
1557 TRUNC(nvl(POH.end_date, SYSDATE))))
1558 AND NVL(POH.closed_code, 'OPEN') <> 'FINALLY CLOSED'
1559 AND NVL(POH.cancel_flag, 'N') <> 'Y';
1560
1561 ELSIF (PO_PDOI_PARAMS.g_request.document_type =
1562 PO_PDOI_CONSTANTS.g_DOC_TYPE_QUOTATION) THEN
1563 d_position := 100;
1564 SELECT POH.po_header_id,
1565 NVL(POH.consigned_consumption_flag, 'N')
1566 BULK COLLECT
1567 INTO l_orig_po_header_id_tbl,
1568 l_orig_consumption_flag_tbl
1569 FROM po_headers POH
1570 WHERE POH.vendor_id = l_vendor_id_tbl(i)
1571 AND POH.quote_vendor_quote_number = l_vendor_doc_num_tbl(i)
1572 AND POH.segment1 = NVL(l_document_num_tbl(i), POH.segment1)
1573 AND POH.type_lookup_code = PO_PDOI_PARAMS.g_request.document_type
1574 AND POH.type_lookup_code = 'QUOTATION'
1575 AND TRUNC(NVL(l_start_date_tbl(i), SYSDATE)) >=
1576 TRUNC(NVL(POH.start_date, SYSDATE))
1577 AND TRUNC(nvl(l_end_date_tbl(i), SYSDATE)) <=
1578 TRUNC(nvl(POH.end_date, SYSDATE))
1579 AND NVL(POH.closed_code, 'OPEN') <> 'FINALLY CLOSED'
1580 AND NVL(POH.cancel_flag, 'N') <> 'Y';
1581
1582 END IF;
1583
1584 ELSIF (l_document_num_tbl(i) IS NOT NULL) THEN
1585 d_position := 110;
1586 -- Definitely need to match document num. Matching vendor doc num
1587 -- will be performed, if provided
1588
1589 IF (PO_PDOI_PARAMS.g_request.document_type IN
1590 (PO_PDOI_CONSTANTS.g_DOC_TYPE_STANDARD,
1591 PO_PDOI_CONSTANTS.g_DOC_TYPE_BLANKET)) THEN
1592
1593 SELECT POH.po_header_id,
1594 NVL(POH.consigned_consumption_flag, 'N')
1595 BULK COLLECT
1596 INTO l_orig_po_header_id_tbl,
1597 l_orig_consumption_flag_tbl
1598 FROM po_headers POH
1599 WHERE POH.vendor_id = l_vendor_id_tbl(i)
1600 AND NVL(POH.vendor_order_num, FND_API.G_MISS_CHAR) =
1601 NVL(l_vendor_doc_num_tbl(i),
1602 NVL(POH.vendor_order_num, FND_API.G_MISS_CHAR))
1603 AND POH.segment1 = l_document_num_tbl(i)
1604 AND POH.type_lookup_code = PO_PDOI_PARAMS.g_request.document_type
1605 AND (POH.type_lookup_code = 'STANDARD'
1606 OR
1607 (POH.type_lookup_code = 'BLANKET'
1608 AND TRUNC(NVL(l_start_date_tbl(i), SYSDATE)) >=
1609 TRUNC(NVL(POH.start_date, SYSDATE))
1610 AND TRUNC(nvl(l_end_date_tbl(i), SYSDATE)) <=
1611 TRUNC(nvl(POH.end_date, SYSDATE))))
1612 AND NVL(POH.closed_code, 'OPEN') <> 'FINALLY CLOSED'
1613 AND NVL(POH.cancel_flag, 'N') <> 'Y';
1614
1615 ELSIF (PO_PDOI_PARAMS.g_request.document_type =
1616 PO_PDOI_CONSTANTS.g_DOC_TYPE_QUOTATION) THEN
1617
1618 SELECT POH.po_header_id,
1619 NVL(POH.consigned_consumption_flag, 'N')
1620 BULK COLLECT
1621 INTO l_orig_po_header_id_tbl,
1622 l_orig_consumption_flag_tbl
1623 FROM po_headers POH
1624 WHERE POH.vendor_id = l_vendor_id_tbl(i)
1625 AND NVL(POH.quote_vendor_quote_number, FND_API.G_MISS_CHAR) =
1626 NVL(l_vendor_doc_num_tbl(i),
1627 NVL(POH.quote_vendor_quote_number, FND_API.G_MISS_CHAR))
1628 AND POH.segment1 = l_document_num_tbl(i)
1629 AND POH.type_lookup_code = PO_PDOI_PARAMS.g_request.document_type
1630 AND POH.type_lookup_code = 'QUOTATION'
1631 AND TRUNC(NVL(l_start_date_tbl(i), SYSDATE)) >=
1632 TRUNC(NVL(POH.start_date, SYSDATE))
1633 AND TRUNC(nvl(l_end_date_tbl(i), SYSDATE)) <=
1634 TRUNC(nvl(POH.end_date, SYSDATE))
1635 AND NVL(POH.closed_code, 'OPEN') <> 'FINALLY CLOSED'
1636 AND NVL(POH.cancel_flag, 'N') <> 'Y';
1637
1638 END IF;
1639
1640 END IF;
1641
1642 d_position := 120;
1643
1644 -- derive the following fields for error reporting
1645 l_token_name := 'DOC_NUMBER';
1646 IF (l_document_num_tbl(i) IS NOT NULL AND l_vendor_doc_num_tbl(i) IS NULL) THEN
1647 l_col_name := 'DOCUMENT_NUM';
1648 l_col_value := l_document_num_tbl(i);
1649 l_token_value := l_document_num_tbl(i);
1650 ELSE
1651 l_col_name := 'VENDOR_DOC_NUM';
1652 l_col_value := l_vendor_doc_num_tbl(i);
1653 l_token_value := l_vendor_doc_num_tbl(i);
1654 END IF;
1655
1656 IF (l_orig_po_header_id_tbl.COUNT <> 1) THEN
1657 IF (l_orig_po_header_id_tbl.COUNT = 0) THEN
1658 IF (PO_PDOI_PARAMS.g_request.document_type =
1659 PO_PDOI_CONSTANTS.g_DOC_TYPE_STANDARD) THEN
1660 l_message_name := 'PO_PDOI_INVALID_ORIG_STD_PO';
1661 ELSE
1662 l_message_name := 'PO_PDOI_INVALID_ORIG_CATALOG';
1663 END IF;
1664 ELSE
1665 IF (PO_PDOI_PARAMS.g_request.document_type =
1666 PO_PDOI_CONSTANTS.g_DOC_TYPE_STANDARD) THEN
1667 l_message_name := 'PO_PDOI_MULTIPLE_STD_PO';
1668 ELSE
1669 l_message_name := 'PO_PDOI_INVALID_MULT_ORIG_CATG';
1670 END IF;
1671 END IF;
1672
1673 d_position := 130;
1674
1675 PO_PDOI_ERR_UTL.add_fatal_error
1676 ( p_interface_header_id => l_interface_header_id_tbl(i),
1677 p_error_message_name => l_message_name,
1678 p_table_name => 'PO_HEADERS_INTERFACE',
1679 p_column_name => l_col_name,
1680 p_column_value => l_col_value,
1681 p_token1_name => l_token_name,
1682 p_token1_value => l_token_value
1683 );
1684 l_valid := FND_API.g_FALSE;
1685 END IF;
1686
1687 IF (l_valid = FND_API.g_TRUE) THEN
1688 IF (PO_PDOI_PARAMS.g_request.document_type IN
1689 (PO_PDOI_CONSTANTS.g_DOC_TYPE_BLANKET,
1690 PO_PDOI_CONSTANTS.g_DOC_TYPE_STANDARD)) THEN
1691
1692 d_position := 140;
1693
1694 PO_PDOI_UTL.get_processing_doctype_info
1695 ( x_doc_type => l_doc_type,
1696 x_doc_subtype => l_doc_subtype
1697 );
1698
1699 PO_DOCUMENT_CHECKS_GRP.po_status_check
1700 ( p_api_version => 1.0,
1701 p_header_id => l_orig_po_header_id_tbl(1),
1702 p_document_type => l_doc_type,
1703 p_document_subtype => l_doc_subtype,
1704 p_mode => 'CHECK_UPDATEABLE',
1705 p_calling_module => PO_DRAFTS_PVT.g_call_mod_PDOI,
1706 p_role => PO_PDOI_PARAMS.g_request.role,
1707 p_skip_cat_upload_chk => l_skip_cat_upload_chk,
1708 x_po_status_rec => l_status_rec,
1709 x_return_status => l_return_status
1710 );
1711
1712 IF (l_return_status <> FND_API.g_RET_STS_SUCCESS) THEN
1713 d_position := 150;
1714 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1715 ELSE
1716 IF (l_orig_consumption_flag_tbl(1) = 'Y' OR
1717 l_status_rec.updatable_flag(1) = 'N') THEN
1718
1719 IF (PO_PDOI_PARAMS.g_request.document_type =
1720 PO_PDOI_CONSTANTS.g_DOC_TYPE_STANDARD) THEN
1721 l_message_name := 'PO_PDOI_STD_PO_INVALID_STATUS';
1722 ELSE
1723 l_message_name := 'PO_PDOI_INVALID_ORIG_CATALOG';
1724 END IF;
1725
1726 d_position := 160;
1727 PO_PDOI_ERR_UTL.add_fatal_error
1728 ( p_interface_header_id => l_interface_header_id_tbl(i),
1729 p_error_message_name => l_message_name,
1730 p_table_name => 'PO_HEADERS_INTERFACE',
1731 p_column_name => l_col_name,
1732 p_column_value => l_col_value,
1733 p_token1_name => l_token_name,
1734 p_token1_value => l_token_value
1735 );
1736
1737 l_valid := FND_API.g_FALSE;
1738 END IF;
1739 END IF;
1740 END IF;
1741 END IF;
1742
1743 d_position := 170;
1744 IF (l_valid = FND_API.g_TRUE) THEN
1745 l_final_intf_header_id_tbl.extend;
1746 l_final_intf_header_id_tbl(l_final_intf_header_id_tbl.COUNT) :=
1747 l_interface_header_id_tbl(i);
1748
1749 l_final_po_header_id_tbl.extend;
1750 l_final_po_header_id_tbl(l_final_po_header_id_tbl.COUNT) :=
1751 l_orig_po_header_id_tbl(1);
1752 ELSE
1753 l_reject_list.extend;
1754 l_reject_list(l_reject_list.COUNT) := l_interface_header_id_tbl(i);
1755 END IF;
1756 END IF; -- p_po_header_id_tbl(i) IS NOT NULL
1757
1758 END LOOP;
1759
1760 d_position := 180;
1761 -- Set po header id (document to update)
1762 FORALL i IN 1..l_final_intf_header_id_tbl.COUNT
1763 UPDATE po_headers_interface
1764 SET po_header_id = l_final_po_header_id_tbl(i)
1765 WHERE interface_header_id = l_final_intf_header_id_tbl(i);
1766
1767 d_position := 190;
1768 -- propagate rejection status to lower level
1769 PO_PDOI_UTL.reject_headers_intf
1770 ( p_id_param_type => PO_PDOI_CONSTANTS.g_INTERFACE_HEADER_ID,
1771 p_id_tbl => l_reject_list,
1772 p_cascade => FND_API.G_TRUE
1773 );
1774
1775 IF (PO_LOG.d_proc) THEN
1776 PO_LOG.proc_end (d_module);
1777 END IF;
1778
1779 EXCEPTION
1780 WHEN OTHERS THEN
1781 PO_MESSAGE_S.add_exc_msg
1782 ( p_pkg_name => d_pkg_name,
1783 p_procedure_name => d_api_name || '.' || d_position
1784 );
1785 RAISE;
1786 END verify_action_update;
1787
1788
1789
1790 -----------------------------------------------------------------------
1791 --Start of Comments
1792 --Name: verify_action_original
1793 --Function:
1794 -- For records with action = 'ORIGINAL' or 'ADD', verify that the action can be
1795 -- performed
1796 --Parameters:
1797 --IN:
1798 --IN OUT:
1799 --OUT:
1800 --End of Comments
1801 ------------------------------------------------------------------------
1802 PROCEDURE verify_action_original IS
1803
1804 d_api_name CONSTANT VARCHAR2(30) := 'verify_action_original';
1805 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
1806 d_position NUMBER;
1807
1808
1809 l_po_header_id_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
1810 l_reject_list PO_TBL_NUMBER := PO_TBL_NUMBER();
1811 l_vendor_doc_num_tbl PO_TBL_VARCHAR25 := PO_TBL_VARCHAR25();
1812 BEGIN
1813 d_position := 0;
1814
1815 IF (PO_LOG.d_proc) THEN
1816 PO_LOG.proc_begin (d_module);
1817 END IF;
1818
1819 IF (PO_PDOI_PARAMS.g_request.document_type =
1820 PO_PDOI_CONSTANTS.g_DOC_TYPE_QUOTATION) THEN
1821
1822 SELECT POH.po_header_id,
1823 PHI.interface_header_id,
1824 PHI.vendor_doc_num
1825 BULK COLLECT
1826 INTO l_po_header_id_tbl,
1827 l_reject_list,
1828 l_vendor_doc_num_tbl
1829 FROM po_headers POH,
1830 po_headers_interface PHI
1831 WHERE POH.vendor_id = PHI.vendor_id
1832 AND POH.quote_vendor_quote_number = PHI.vendor_doc_Num
1833 AND TRUNC (NVL(PHI.effective_date, SYSDATE)) >=
1834 TRUNC (NVL(POH.start_date, SYSDATE))
1835 AND TRUNC (NVL(PHI.expiration_date, SYSDATE)) <=
1836 TRUNC (NVL(POH.end_date, SYSDATE))
1837 AND NVL(POH.closed_code, 'OPEN') <> 'FINALLY CLOSED'
1838 AND NVL(POH.cancel_flag, 'N') <> 'Y'
1839 AND PHI.processing_id = PO_PDOI_PARAMS.g_processing_id
1840 AND PHI.action IN (PO_PDOI_CONSTANTS.g_ACTION_ORIGINAL,
1841 PO_PDOI_CONSTANTS.g_ACTION_ADD);
1842
1843 ELSIF (PO_PDOI_PARAMS.g_request.document_type =
1844 PO_PDOI_CONSTANTS.g_DOC_TYPE_BLANKET) THEN
1845
1846 SELECT POH.po_header_id,
1847 PHI.interface_header_id,
1848 PHI.vendor_doc_num
1849 BULK COLLECT
1850 INTO l_po_header_id_tbl,
1851 l_reject_list,
1852 l_vendor_doc_num_tbl
1853 FROM po_headers POH,
1854 po_headers_interface PHI
1855 WHERE POH.vendor_id = PHI.vendor_id
1856 AND POH.vendor_order_num = PHI.vendor_doc_Num
1857 AND TRUNC (NVL(PHI.effective_date, SYSDATE)) >=
1858 TRUNC (NVL(POH.start_date, SYSDATE))
1859 AND TRUNC (NVL(PHI.expiration_date, SYSDATE)) <=
1860 TRUNC (NVL(POH.end_date, SYSDATE))
1861 AND NVL(POH.closed_code, 'OPEN') <> 'FINALLY CLOSED'
1862 AND NVL(POH.cancel_flag, 'N') <> 'Y'
1863 AND PHI.processing_id = PO_PDOI_PARAMS.g_processing_id
1864 AND PHI.action IN (PO_PDOI_CONSTANTS.g_ACTION_ORIGINAL,
1865 PO_PDOI_CONSTANTS.g_ACTION_ADD);
1866
1867 END IF;
1868
1869 d_position := 10;
1870
1871 FOR i IN 1..l_reject_list.COUNT LOOP
1872 PO_PDOI_ERR_UTL.add_fatal_error
1873 ( p_interface_header_id => l_reject_list(i),
1874 p_error_message_name => 'PO_PDOI_CATG_ALREADY_EXISTS',
1875 p_table_name => 'PO_HEADERS_INTERFACE',
1876 p_column_name => 'VENDOR_DOC_NUM',
1877 p_column_value => l_vendor_doc_num_tbl(i),
1878 p_token1_name => 'DOC_NUMBER',
1879 p_token1_value => l_vendor_doc_num_tbl(i)
1880 );
1881 END LOOP;
1882
1883 d_position := 20;
1884
1885 -- propagate rejection status to lower level for each document getting
1886 -- rejected
1887 PO_PDOI_UTL.reject_headers_intf
1888 ( p_id_param_type => PO_PDOI_CONSTANTS.g_INTERFACE_HEADER_ID,
1889 p_id_tbl => l_reject_list,
1890 p_cascade => FND_API.G_TRUE
1891 );
1892
1893 IF (PO_LOG.d_proc) THEN
1894 PO_LOG.proc_end (d_module);
1895 END IF;
1896 EXCEPTION
1897 WHEN OTHERS THEN
1898 PO_MESSAGE_S.add_exc_msg
1899 ( p_pkg_name => d_pkg_name,
1900 p_procedure_name => d_api_name || '.' || d_position
1901 );
1902 RAISE;
1903 END verify_action_original;
1904
1905
1906
1907 -----------------------------------------------------------------------
1908 --Start of Comments
1909 --Name: assign_po_header_id
1910 --Function:
1911 -- For interface records that yield new documents to be created in the
1912 -- system, assign po_header_id from sequence
1913 --Parameters:
1914 --IN:
1915 --IN OUT:
1916 --OUT:
1917 --End of Comments
1918 ------------------------------------------------------------------------
1919 PROCEDURE assign_po_header_id IS
1920
1921 d_api_name CONSTANT VARCHAR2(30) := 'assign_po_header_id';
1922 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
1923 d_position NUMBER;
1924
1925 BEGIN
1926
1927 d_position := 0;
1928
1929 IF (PO_LOG.d_proc) THEN
1930 PO_LOG.proc_begin (d_module);
1931 END IF;
1932
1933 -- For ORIGINAL, ADD or REPLACE action, new document will be created
1934 -- Need to assign a new po_header_id
1935 UPDATE po_headers_interface
1936 SET po_header_id = PO_HEADERS_S.nextval
1937 WHERE processing_id = PO_PDOI_PARAMS.g_processing_id
1938 AND po_header_id IS NULL
1939 AND action IN (PO_PDOI_CONSTANTS.g_ACTION_ORIGINAL,
1940 PO_PDOI_CONSTANTS.g_ACTION_ADD,
1941 PO_PDOI_CONSTANTS.g_ACTION_REPLACE);
1942
1943 IF (PO_LOG.d_proc) THEN
1944 PO_LOG.proc_end (d_module);
1945 END IF;
1946
1947 EXCEPTION
1948 WHEN OTHERS THEN
1949 PO_MESSAGE_S.add_exc_msg
1950 ( p_pkg_name => d_pkg_name,
1951 p_procedure_name => d_api_name || '.' || d_position
1952 );
1953 RAISE;
1954 END assign_po_header_id;
1955
1956
1957 -----------------------------------------------------------------------
1958 --Start of Comments
1959 --Name: populate_line_loc_interface
1960 --Function:
1961 -- For line interface records that require line location to be populated
1962 -- (indicated by line_loc_populated_flag <> 'Y'), populate a record into
1963 -- line locations interface, using the attribute values from lines
1964 -- interface
1965 --Parameters:
1966 --IN:
1967 --IN OUT:
1968 --OUT:
1969 --End of Comments
1970 ------------------------------------------------------------------------
1971 PROCEDURE populate_line_loc_interface IS
1972
1973 d_api_name CONSTANT VARCHAR2(30) := 'populate_line_loc_interface';
1974 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
1975 d_position NUMBER;
1976
1977 --SQL What: Get all the lines interface records requiring line location
1978 -- interface records defaulting
1979 --SQL Why: User may Line Location information only to lines interface. In this
1980 -- case we need to populate the information to line location information
1981 -- for processing later on
1982 CURSOR c_line_intf IS
1983 SELECT rowid
1984 FROM po_lines_interface
1985 WHERE processing_id = PO_PDOI_PARAMS.g_processing_id
1986 AND NVL(line_loc_populated_flag, 'N') = 'N';
1987
1988 --SQL What: Get line location records that are just being populated
1989 --SQL Why: Need to populate interface_line_location_id to po distributions and
1990 -- po price differentials that were originally only attached to the
1991 -- lines interface records.
1992 CURSOR c_line_loc_intf IS
1993 SELECT PLLI.interface_line_id,
1994 PLLI.interface_line_location_id
1995 FROM po_line_locations_interface PLLI,
1996 po_lines_interface PLI
1997 WHERE PLLI.processing_id = PO_PDOI_PARAMS.g_processing_id
1998 AND PLLI.interface_line_id = PLI.interface_line_id
1999 AND NVL(PLI.line_loc_populated_flag, 'N') = 'N';
2000
2001 l_rowid_tbl DBMS_SQL.urowid_table;
2002 l_intf_line_tbl PO_TBL_NUMBER;
2003 l_intf_line_loc_tbl PO_TBL_NUMBER;
2004 -- << PDOI for Complex PO Project: Start >>
2005 l_is_complex_work_style BOOLEAN;
2006 l_is_financing_style BOOLEAN;
2007 l_style_id NUMBER := 1;
2008 l_interface_line_id NUMBER;
2009 l_interface_line_location_id NUMBER;
2010 -- << PDOI for Complex PO Project: End >>
2011 BEGIN
2012 d_position := 0;
2013
2014 IF (PO_LOG.d_proc) THEN
2015 PO_LOG.proc_begin (d_module);
2016 END IF;
2017
2018 OPEN c_line_intf;
2019
2020 LOOP
2021 FETCH c_line_intf
2022 BULK COLLECT
2023 INTO l_rowid_tbl
2024 LIMIT PO_PDOI_CONSTANTS.g_DEF_BATCH_SIZE;
2025
2026 d_position := 10;
2027
2028 EXIT WHEN l_rowid_tbl.COUNT = 0;
2029
2030 IF (PO_LOG.d_stmt) THEN
2031 PO_LOG.stmt(d_module, d_position, 'number of lines fetched: ' ||
2032 l_rowid_tbl.COUNT);
2033 END IF;
2034
2035 FOR i IN 1..l_rowid_tbl.COUNT LOOP -- PDOI for Complex PO Project
2036 d_position := 20;
2037 INSERT INTO po_line_locations_interface
2038 (
2039 interface_line_location_id,
2040 interface_header_id,
2041 interface_line_id,
2042 processing_id,
2043 process_code,
2044 line_location_id,
2045 shipment_type,
2046 shipment_num,
2047 ship_to_organization_id,
2048 ship_to_organization_code,
2049 ship_to_location_id,
2050 ship_to_location,
2051 terms_id,
2052 payment_terms,
2053 qty_rcv_exception_code,
2054 freight_carrier,
2055 fob,
2056 freight_terms,
2057 enforce_ship_to_location_code,
2058 allow_substitute_receipts_flag,
2059 days_early_receipt_allowed,
2060 days_late_receipt_allowed,
2061 receipt_days_exception_code,
2062 invoice_close_tolerance,
2063 receive_close_tolerance,
2064 receiving_routing_id,
2065 receiving_routing,
2066 accrue_on_receipt_flag,
2067 firm_flag,
2068 need_by_date,
2069 promised_date,
2070 from_line_location_id,
2071 inspection_required_flag,
2072 receipt_required_flag,
2073 source_shipment_id,
2074 note_to_receiver,
2075 transaction_flow_header_id,
2076 quantity,
2077 price_discount,
2078 start_date,
2079 end_date,
2080 price_override,
2081 lead_time,
2082 lead_time_unit,
2083 amount,
2084 secondary_quantity,
2085 secondary_unit_of_measure,
2086 attribute_category,
2087 attribute1,
2088 attribute2,
2089 attribute3,
2090 attribute4,
2091 attribute5,
2092 attribute6,
2093 attribute7,
2094 attribute8,
2095 attribute9,
2096 attribute10,
2097 attribute11,
2098 attribute12,
2099 attribute13,
2100 attribute14,
2101 attribute15,
2102 creation_date,
2103 created_by,
2104 last_update_date,
2105 last_updated_by,
2106 last_update_login,
2107 request_id,
2108 program_application_id,
2109 program_id,
2110 program_update_date,
2111 unit_of_measure,
2112 preferred_grade,
2113 taxable_flag,
2114 tax_code_id,
2115 tax_name,
2116 qty_rcv_tolerance
2117 )
2118 SELECT po_line_locations_interface_s.nextval,
2119 PLI.interface_header_id,
2120 PLI.interface_line_id,
2121 PLI.processing_id,
2122 PLI.process_code,
2123 PLI.line_location_id,
2124 PLI.shipment_type,
2125 PLI.shipment_num,
2126 PLI.ship_to_organization_id,
2127 PLI.ship_to_organization_code,
2128 PLI.ship_to_location_id,
2129 PLI.ship_to_location,
2130 PLI.terms_id,
2131 PLI.payment_terms,
2132 PLI.qty_rcv_exception_code,
2133 PLI.freight_carrier,
2134 PLI.fob,
2135 PLI.freight_terms,
2136 PLI.enforce_ship_to_location_code,
2137 PLI.allow_substitute_receipts_flag,
2138 PLI.days_early_receipt_allowed,
2139 PLI.days_late_receipt_allowed,
2140 PLI.receipt_days_exception_code,
2141 PLI.invoice_close_tolerance,
2142 PLI.receive_close_tolerance,
2143 PLI.receiving_routing_id,
2144 PLI.receiving_routing,
2145 PLI.accrue_on_receipt_flag,
2146 PLI.firm_flag,
2147 PLI.need_by_date,
2148 PLI.promised_date,
2149 PLI.from_line_location_id,
2150 PLI.inspection_required_flag,
2151 PLI.receipt_required_flag,
2152 PLI.source_shipment_id,
2153 PLI.note_to_receiver,
2154 PLI.transaction_flow_header_id,
2155 PLI.quantity,
2156 PLI.price_discount,
2157 PLI.effective_date,
2158 PLI.expiration_date,
2159 PLI.unit_price,
2160 PLI.lead_time,
2161 PLI.lead_time_unit,
2162 PLI.amount,
2163 PLI.secondary_quantity,
2164 PLI.secondary_unit_of_measure,
2165 PLI.shipment_attribute_category,
2166 PLI.shipment_attribute1,
2167 PLI.shipment_attribute2,
2168 PLI.shipment_attribute3,
2169 PLI.shipment_attribute4,
2170 PLI.shipment_attribute5,
2171 PLI.shipment_attribute6,
2172 PLI.shipment_attribute7,
2173 PLI.shipment_attribute8,
2174 PLI.shipment_attribute9,
2175 PLI.shipment_attribute10,
2176 PLI.shipment_attribute11,
2177 PLI.shipment_attribute12,
2178 PLI.shipment_attribute13,
2179 PLI.shipment_attribute14,
2180 PLI.shipment_attribute15,
2181 PLI.creation_date,
2182 PLI.created_by,
2183 PLI.last_update_date,
2184 PLI.last_updated_by,
2185 PLI.last_update_login,
2186 PLI.request_id,
2187 PLI.program_application_id,
2188 PLI.program_id,
2189 PLI.program_update_date,
2190 PLI.unit_of_measure,
2191 PLI.preferred_grade,
2192 PLI.taxable_flag,
2193 PLI.tax_code_id,
2194 PLI.tax_name,
2195 PLI.qty_rcv_tolerance
2196 FROM po_lines_interface PLI
2197 WHERE PLI.rowid = l_rowid_tbl(i);
2198
2199 /* PDOI for Complex PO Project: Logic for creating Pay items: Start */
2200 BEGIN
2201 SELECT Nvl(DECODE(PHI.po_header_id,NULL,PHI.style_id,
2202 (SELECT PH.style_id FROM po_headers_all PH
2203 WHERE PH.po_header_id = PHI.po_header_id)),1),
2204 -- In 'UPDATE' mode, the style_id corresponding to the base PO
2205 -- needs to be considered.
2206 PLI.interface_line_id,
2207 po_line_locations_interface_s.currval
2208 INTO l_style_id, l_interface_line_id, l_interface_line_location_id
2209 FROM po_headers_interface PHI,
2210 po_lines_interface PLI
2211 WHERE PLI.rowid = l_rowid_tbl(i)
2212 AND PLI.interface_header_id = PHI.interface_header_id;
2213 EXCEPTION
2214 WHEN OTHERS THEN
2215 l_style_id := 1;
2216 END;
2217
2218 l_is_complex_work_style := FALSE;
2219 l_is_financing_style := FALSE;
2220
2221 l_is_complex_work_style := PO_COMPLEX_WORK_PVT.is_complex_work_style(p_style_id => l_style_id);
2222 l_is_financing_style := PO_COMPLEX_WORK_PVT.is_financing_payment_style(p_style_id => l_style_id);
2223
2224 IF l_is_complex_work_style THEN
2225
2226 d_position := 30;
2227
2228 UPDATE po_line_locations_interface
2229 SET payment_type = DECODE(quantity,NULL,'LUMPSUM','MILESTONE'),
2230 price_override = DECODE(quantity,NULL,NULL,0)
2231 WHERE interface_line_location_id = l_interface_line_location_id;
2232
2233 IF l_is_financing_style THEN
2234
2235 UPDATE po_line_locations_interface
2236 SET shipment_type = 'PREPAYMENT',
2237 shipment_num = DECODE(shipment_num,NULL,1,shipment_num)
2238 WHERE interface_line_location_id = l_interface_line_location_id;
2239
2240 UPDATE po_distributions_interface
2241 SET prevent_encumbrance_flag = 'Y'
2242 WHERE interface_line_id = (SELECT PLI.interface_line_id
2243 FROM po_lines_interface PLI
2244 WHERE PLI.rowid = l_rowid_tbl(i));
2245 END IF;
2246
2247 IF (PO_LOG.d_stmt) THEN
2248 PO_LOG.stmt(d_module, d_position, 'creating Pay items for Interface Line Id: ' ||
2249 l_rowid_tbl(i));
2250 END IF;
2251
2252 populate_payitems(p_interface_line_id => l_interface_line_id,
2253 p_style_id => l_style_id);
2254 END IF;
2255 /* PDOI for Complex PO Project: Logic for creating Pay items: End */
2256
2257 END LOOP; -- PDOI for Complex PO Project
2258
2259 END LOOP;
2260
2261 d_position := 20;
2262
2263 CLOSE c_line_intf;
2264
2265 OPEN c_line_loc_intf;
2266
2267 LOOP
2268 d_position := 30;
2269
2270 FETCH c_line_loc_intf
2271 BULK COLLECT
2272 INTO l_intf_line_tbl,
2273 l_intf_line_loc_tbl
2274 LIMIT PO_PDOI_CONSTANTS.g_DEF_BATCH_SIZE;
2275
2276 EXIT WHEN l_intf_line_tbl.COUNT = 0;
2277
2278 d_position := 40;
2279
2280 FORALL i IN 1..l_intf_line_tbl.COUNT
2281 UPDATE po_distributions_interface
2282 SET interface_line_location_id = l_intf_line_loc_tbl(i)
2283 WHERE interface_line_id = l_intf_line_tbl(i)
2284 AND interface_line_location_id IS NULL; -- PDOI for Complex PO Project
2285
2286 FORALL i IN 1..l_intf_line_tbl.COUNT
2287 UPDATE po_price_diff_interface
2288 SET interface_line_location_id = l_intf_line_loc_tbl(i)
2289 WHERE interface_line_id = l_intf_line_tbl(i);
2290
2291 /* PDOI for Complex PO Project -- START */
2292 FOR i IN 1..l_intf_line_tbl.COUNT LOOP
2293 DECLARE
2294 BEGIN
2295 UPDATE po_distributions_interface pd1
2296 SET (charge_account_id,
2297 charge_account_segment1,
2298 charge_account_segment2,
2299 charge_account_segment3,
2300 charge_account_segment4,
2301 charge_account_segment5,
2302 charge_account_segment6,
2303 charge_account_segment7,
2304 charge_account_segment8,
2305 charge_account_segment9,
2306 charge_account_segment10,
2307 charge_account_segment11,
2308 charge_account_segment12,
2309 charge_account_segment13,
2310 charge_account_segment14,
2311 charge_account_segment15,
2312 charge_account_segment16,
2313 charge_account_segment17,
2314 charge_account_segment18,
2315 charge_account_segment19,
2316 charge_account_segment20,
2317 charge_account_segment21,
2318 charge_account_segment22,
2319 charge_account_segment23,
2320 charge_account_segment24,
2321 charge_account_segment25,
2322 charge_account_segment26,
2323 charge_account_segment27,
2324 charge_account_segment28,
2325 charge_account_segment29,
2326 charge_account_segment30) =
2327 (SELECT charge_account_id,
2328 charge_account_segment1,
2329 charge_account_segment2,
2330 charge_account_segment3,
2331 charge_account_segment4,
2332 charge_account_segment5,
2333 charge_account_segment6,
2334 charge_account_segment7,
2335 charge_account_segment8,
2336 charge_account_segment9,
2337 charge_account_segment10,
2338 charge_account_segment11,
2339 charge_account_segment12,
2340 charge_account_segment13,
2341 charge_account_segment14,
2342 charge_account_segment15,
2343 charge_account_segment16,
2344 charge_account_segment17,
2345 charge_account_segment18,
2346 charge_account_segment19,
2347 charge_account_segment20,
2348 charge_account_segment21,
2349 charge_account_segment22,
2350 charge_account_segment23,
2351 charge_account_segment24,
2352 charge_account_segment25,
2353 charge_account_segment26,
2354 charge_account_segment27,
2355 charge_account_segment28,
2356 charge_account_segment29,
2357 charge_account_segment30
2358 FROM po_distributions_interface pd2
2359 WHERE pd2.interface_line_id = l_intf_line_tbl(i)
2360 AND (charge_account_id IS NOT NULL OR charge_account_segment1 IS NOT NULL)
2361 AND ROWNUM = 1)
2362 WHERE pd1.interface_line_id = l_intf_line_tbl(i)
2363 AND pd1.charge_account_id IS NULL
2364 AND pd1.charge_account_segment1 IS NULL;
2365 EXCEPTION
2366 WHEN OTHERS THEN
2367 NULL;
2368 END;
2369 END LOOP;
2370 /* PDOI for Complex PO Project -- END */
2371 END LOOP;
2372
2373 CLOSE c_line_loc_intf;
2374
2375 d_position := 50;
2376
2377 -- Since the default line location has been populated,
2378 -- populate 'S' to line_loc_populated_flag
2379 UPDATE po_lines_interface
2380 SET line_loc_populated_flag = 'S'
2381 WHERE processing_id = PO_PDOI_PARAMS.g_processing_id
2382 AND NVL(line_loc_populated_flag, 'N') = 'N';
2383
2384 IF (PO_LOG.d_proc) THEN
2385 PO_LOG.proc_end (d_module);
2386 END IF;
2387
2388 EXCEPTION
2389 WHEN OTHERS THEN
2390 IF (c_line_loc_intf%ISOPEN) THEN
2391 CLOSE c_line_loc_intf;
2392 END IF;
2393
2394 PO_MESSAGE_S.add_exc_msg
2395 ( p_pkg_name => d_pkg_name,
2396 p_procedure_name => d_api_name || '.' || d_position
2397 );
2398 RAISE;
2399 END populate_line_loc_interface;
2400
2401 ---------------------------------------------------------------------------
2402 -- PDOI for Complex PO Project
2403 --Start of Comments
2404 --Name: populate_payitems
2405 --Pre-requisites:
2406 -- This Procedure needs to be called for only those interface line records,
2407 -- which belong to complex style. When line_loc_populated_flag = 'Y', this
2408 -- procedure will not be called.
2409 --Function:
2410 -- This Procedure will create Advance shipment, and Contract finance
2411 -- shipment, along with default Pay items.
2412 --Parameters:
2413 --IN:
2414 --IN OUT:
2415 --OUT:
2416 --End of Comments
2417 ---------------------------------------------------------------------------
2418 PROCEDURE populate_payitems
2419 ( p_interface_line_id IN NUMBER,
2420 p_style_id IN NUMBER) IS
2421
2422 d_api_name CONSTANT VARCHAR2(30) := 'populate_payitems';
2423 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
2424 d_position NUMBER;
2425
2426 l_complex_work_flag VARCHAR2(1);
2427 l_financing_payments_flag VARCHAR2(1);
2428 l_retainage_allowed_flag VARCHAR2(1);
2429 l_advance_allowed_flag VARCHAR2(1);
2430 l_milestone_allowed_flag VARCHAR2(1);
2431 l_lumpsum_allowed_flag VARCHAR2(1);
2432 l_rate_allowed_flag VARCHAR2(1);
2433
2434 BEGIN
2435 d_position := 0;
2436
2437 IF (PO_LOG.d_proc) THEN
2438 PO_LOG.proc_begin (d_module);
2439 END IF;
2440
2441 PO_COMPLEX_WORK_PVT.get_payment_style_settings(
2442 p_style_id => p_style_id
2443 , x_complex_work_flag => l_complex_work_flag
2444 , x_financing_payments_flag => l_financing_payments_flag
2445 , x_retainage_allowed_flag => l_retainage_allowed_flag
2446 , x_advance_allowed_flag => l_advance_allowed_flag
2447 , x_milestone_allowed_flag => l_milestone_allowed_flag
2448 , x_lumpsum_allowed_flag => l_lumpsum_allowed_flag
2449 , x_rate_allowed_flag => l_rate_allowed_flag
2450 );
2451
2452 IF l_advance_allowed_flag = 'Y' THEN
2453 populate_advance_payitem(p_interface_line_id => p_interface_line_id);
2454 END IF;
2455
2456 IF l_financing_payments_flag = 'Y' THEN
2457 populate_progress_payitem(p_interface_line_id => p_interface_line_id);
2458 END IF;
2459
2460 IF (PO_LOG.d_proc) THEN
2461 PO_LOG.proc_end (d_module);
2462 END IF;
2463
2464 EXCEPTION
2465 WHEN OTHERS THEN
2466 PO_MESSAGE_S.add_exc_msg
2467 ( p_pkg_name => d_pkg_name,
2468 p_procedure_name => d_api_name || '.' || d_position
2469 );
2470 RAISE;
2471 END populate_payitems;
2472
2473 ---------------------------------------------------------------------------
2474 -- PDOI for Complex PO Project
2475 --Start of Comments
2476 --Name: populate_advance_payitem
2477 --Pre-requisites:
2478 -- This Procedure needs to be called for only those interface line records,
2479 -- which belong to complex style and have the option Advance enabled. When
2480 -- line_loc_populated_flag = 'Y', this procedure will not be called.
2481 --Function:
2482 -- This Procedure will create Advance shipment for the given interface
2483 -- line record.
2484 --Parameters:
2485 --IN:
2486 --IN OUT:
2487 --OUT:
2488 --End of Comments
2489 ---------------------------------------------------------------------------
2490 PROCEDURE populate_advance_payitem
2491 ( p_interface_line_id IN NUMBER) IS
2492
2493 d_api_name CONSTANT VARCHAR2(30) := 'populate_advance_payitem';
2494 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
2495 d_position NUMBER;
2496
2497 l_interface_line_location_id NUMBER;
2498
2499 BEGIN
2500 d_position := 0;
2501
2502 IF (PO_LOG.d_proc) THEN
2503 PO_LOG.proc_begin (d_module);
2504 END IF;
2505
2506 INSERT INTO po_line_locations_interface
2507 (
2508 interface_line_location_id,
2509 interface_header_id,
2510 interface_line_id,
2511 processing_id,
2512 process_code,
2513 line_location_id,
2514 shipment_type,
2515 shipment_num,
2516 ship_to_organization_id,
2517 ship_to_organization_code,
2518 ship_to_location_id,
2519 ship_to_location,
2520 terms_id,
2521 payment_terms,
2522 qty_rcv_exception_code,
2523 freight_carrier,
2524 fob,
2525 freight_terms,
2526 enforce_ship_to_location_code,
2527 allow_substitute_receipts_flag,
2528 days_early_receipt_allowed,
2529 days_late_receipt_allowed,
2530 receipt_days_exception_code,
2531 invoice_close_tolerance,
2532 receive_close_tolerance,
2533 receiving_routing_id,
2534 receiving_routing,
2535 accrue_on_receipt_flag,
2536 firm_flag,
2537 need_by_date,
2538 promised_date,
2539 from_line_location_id,
2540 inspection_required_flag,
2541 receipt_required_flag,
2542 source_shipment_id,
2543 note_to_receiver,
2544 transaction_flow_header_id,
2545 quantity,
2546 price_discount,
2547 start_date,
2548 end_date,
2549 price_override,
2550 lead_time,
2551 lead_time_unit,
2552 amount,
2553 secondary_quantity,
2554 secondary_unit_of_measure,
2555 attribute_category,
2556 attribute1,
2557 attribute2,
2558 attribute3,
2559 attribute4,
2560 attribute5,
2561 attribute6,
2562 attribute7,
2563 attribute8,
2564 attribute9,
2565 attribute10,
2566 attribute11,
2567 attribute12,
2568 attribute13,
2569 attribute14,
2570 attribute15,
2571 creation_date,
2572 created_by,
2573 last_update_date,
2574 last_updated_by,
2575 last_update_login,
2576 request_id,
2577 program_application_id,
2578 program_id,
2579 program_update_date,
2580 unit_of_measure,
2581 payment_type,
2582 value_basis,
2583 matching_basis,
2584 preferred_grade,
2585 taxable_flag,
2586 tax_code_id,
2587 tax_name,
2588 qty_rcv_tolerance
2589 )
2590 SELECT po_line_locations_interface_s.nextval,
2591 PLI.interface_header_id,
2592 PLI.interface_line_id,
2593 PLI.processing_id,
2594 PLI.process_code,
2595 PLI.line_location_id,
2596 'PREPAYMENT', -- shipment_type
2597 0, -- shipment_num
2598 PLI.ship_to_organization_id,
2599 PLI.ship_to_organization_code,
2600 PLI.ship_to_location_id,
2601 PLI.ship_to_location,
2602 PLI.terms_id,
2603 PLI.payment_terms,
2604 PLI.qty_rcv_exception_code,
2605 PLI.freight_carrier,
2606 PLI.fob,
2607 PLI.freight_terms,
2608 PLI.enforce_ship_to_location_code,
2609 PLI.allow_substitute_receipts_flag,
2610 PLI.days_early_receipt_allowed,
2611 PLI.days_late_receipt_allowed,
2612 PLI.receipt_days_exception_code,
2613 PLI.invoice_close_tolerance,
2614 PLI.receive_close_tolerance,
2615 PLI.receiving_routing_id,
2616 PLI.receiving_routing,
2617 PLI.accrue_on_receipt_flag,
2618 PLI.firm_flag,
2619 NULL, -- need_by_date
2620 NULL, -- promised_date
2621 PLI.from_line_location_id,
2622 PLI.inspection_required_flag,
2623 'N', -- receipt_required_flag
2624 PLI.source_shipment_id,
2625 PLI.note_to_receiver,
2626 PLI.transaction_flow_header_id,
2627 NULL, -- quantity
2628 NULL, -- price_discount
2629 PLI.effective_date,
2630 PLI.expiration_date,
2631 NULL, -- unit_price
2632 PLI.lead_time,
2633 PLI.lead_time_unit,
2634 PLI.advance_amount, -- amount
2635 NULL, -- secondary_quantity
2636 NULL, -- secondary_unit_of_measure
2637 PLI.shipment_attribute_category,
2638 PLI.shipment_attribute1,
2639 PLI.shipment_attribute2,
2640 PLI.shipment_attribute3,
2641 PLI.shipment_attribute4,
2642 PLI.shipment_attribute5,
2643 PLI.shipment_attribute6,
2644 PLI.shipment_attribute7,
2645 PLI.shipment_attribute8,
2646 PLI.shipment_attribute9,
2647 PLI.shipment_attribute10,
2648 PLI.shipment_attribute11,
2649 PLI.shipment_attribute12,
2650 PLI.shipment_attribute13,
2651 PLI.shipment_attribute14,
2652 PLI.shipment_attribute15,
2653 PLI.creation_date,
2654 PLI.created_by,
2655 PLI.last_update_date,
2656 PLI.last_updated_by,
2657 PLI.last_update_login,
2658 PLI.request_id,
2659 PLI.program_application_id,
2660 PLI.program_id,
2661 PLI.program_update_date,
2662 NULL, -- unit_of_measure
2663 'ADVANCE', -- payment_type
2664 'FIXED PRICE', -- value_basis
2665 'AMOUNT', -- matching_basis
2666 PLI.preferred_grade,
2667 PLI.taxable_flag,
2668 PLI.tax_code_id,
2669 PLI.tax_name,
2670 PLI.qty_rcv_tolerance
2671 FROM po_lines_interface PLI
2672 WHERE PLI.interface_line_id = p_interface_line_id
2673 AND Nvl(PLI.advance_amount,0) > 0;
2674
2675 IF (SQL%ROWCOUNT > 0) THEN
2676 SELECT po_line_locations_interface_s.CURRVAL
2677 INTO l_interface_line_location_id
2678 FROM DUAL;
2679
2680 populate_advance_payitem_dist
2681 (p_interface_line_location_id => l_interface_line_location_id);
2682 END IF;
2683
2684 IF (PO_LOG.d_proc) THEN
2685 PO_LOG.proc_end (d_module);
2686 END IF;
2687
2688 EXCEPTION
2689 WHEN OTHERS THEN
2690 PO_MESSAGE_S.add_exc_msg
2691 ( p_pkg_name => d_pkg_name,
2692 p_procedure_name => d_api_name || '.' || d_position
2693 );
2694 RAISE;
2695 END populate_advance_payitem;
2696
2697 ---------------------------------------------------------------------------
2698 -- PDOI for Complex PO Project
2699 --Start of Comments
2700 --Name: populate_advance_payitem_dist
2701 --Pre-requisites:
2702 -- This Procedure needs to be called for only those interface line
2703 -- location records, which belong to complex style and have the option
2704 -- Advance enabled. When line_loc_populated_flag = 'Y', this procedure
2705 -- will not be called.
2706 --Function:
2707 -- This Procedure will create distribution for the given Advance shipment
2708 -- (Interface line location record).
2709 --Parameters:
2710 --IN:
2711 --IN OUT:
2712 --OUT:
2713 --End of Comments
2714 ---------------------------------------------------------------------------
2715 PROCEDURE populate_advance_payitem_dist
2716 ( p_interface_line_location_id IN NUMBER) IS
2717
2718 d_api_name CONSTANT VARCHAR2(30) := 'populate_advance_payitem_dist';
2719 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
2720 d_position NUMBER;
2721
2722 BEGIN
2723 d_position := 0;
2724
2725 IF (PO_LOG.d_proc) THEN
2726 PO_LOG.proc_begin (d_module);
2727 END IF;
2728
2729 INSERT INTO po_distributions_interface
2730 (
2731 interface_distribution_id,
2732 interface_header_id,
2733 interface_line_id,
2734 interface_line_location_id,
2735 processing_id,
2736 process_code,
2737 distribution_num,
2738 rate_date,
2739 amount_ordered,
2740 destination_type_code,
2741 accrue_on_receipt_flag,
2742 prevent_encumbrance_flag,
2743 creation_date,
2744 created_by,
2745 last_update_date,
2746 last_updated_by,
2747 last_update_login,
2748 request_id,
2749 program_application_id,
2750 program_id,
2751 program_update_date
2752 )
2753 SELECT po_distributions_interface_s.nextval,
2754 PLL.interface_header_id,
2755 PLL.interface_line_id,
2756 PLL.interface_line_location_id,
2757 PLL.processing_id,
2758 PLL.process_code,
2759 1, -- distribution_num
2760 sysdate, -- rate_date
2761 PLL.amount, -- amount_ordered
2762 'EXPENSE', -- destination_type_code
2763 PLL.accrue_on_receipt_flag,
2764 'Y', -- prevent_encumbrance_flag
2765 PLL.creation_date,
2766 PLL.created_by,
2767 PLL.last_update_date,
2768 PLL.last_updated_by,
2769 PLL.last_update_login,
2770 PLL.request_id,
2771 PLL.program_application_id,
2772 PLL.program_id,
2773 PLL.program_update_date
2774 FROM po_line_locations_interface PLL
2775 WHERE PLL.interface_line_location_id = p_interface_line_location_id;
2776
2777 IF (PO_LOG.d_proc) THEN
2778 PO_LOG.proc_end (d_module);
2779 END IF;
2780
2781 EXCEPTION
2782 WHEN OTHERS THEN
2783 PO_MESSAGE_S.add_exc_msg
2784 ( p_pkg_name => d_pkg_name,
2785 p_procedure_name => d_api_name || '.' || d_position
2786 );
2787 RAISE;
2788 END populate_advance_payitem_dist;
2789
2790 ---------------------------------------------------------------------------
2791 -- PDOI for Complex PO Project
2792 --Start of Comments
2793 --Name: populate_progress_payitem
2794 --Pre-requisites:
2795 -- This Procedure needs to be called for only those interface line records,
2796 -- which belong to complex style and have the option "Treat Progress
2797 -- Payments as Contract Financing" is checked. When the
2798 -- line_loc_populated_flag = 'Y', this procedure will not be called.
2799 --Function:
2800 -- This Procedure will create the Pay item that correspond to the contract
2801 -- financing, for the given interface line record.
2802 --Parameters:
2803 --IN:
2804 --IN OUT:
2805 --OUT:
2806 --End of Comments
2807 ---------------------------------------------------------------------------
2808 PROCEDURE populate_progress_payitem
2809 ( p_interface_line_id IN NUMBER) IS
2810
2811 d_api_name CONSTANT VARCHAR2(30) := 'populate_progress_payitem';
2812 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
2813 d_position NUMBER;
2814
2815 l_interface_line_location_id NUMBER;
2816
2817 BEGIN
2818 d_position := 0;
2819
2820 IF (PO_LOG.d_proc) THEN
2821 PO_LOG.proc_begin (d_module);
2822 END IF;
2823
2824 INSERT INTO po_line_locations_interface
2825 (
2826 interface_line_location_id,
2827 interface_header_id,
2828 interface_line_id,
2829 processing_id,
2830 process_code,
2831 line_location_id,
2832 shipment_type,
2833 shipment_num,
2834 ship_to_organization_id,
2835 ship_to_organization_code,
2836 ship_to_location_id,
2837 ship_to_location,
2838 terms_id,
2839 payment_terms,
2840 qty_rcv_exception_code,
2841 freight_carrier,
2842 fob,
2843 freight_terms,
2844 enforce_ship_to_location_code,
2845 allow_substitute_receipts_flag,
2846 days_early_receipt_allowed,
2847 days_late_receipt_allowed,
2848 receipt_days_exception_code,
2849 invoice_close_tolerance,
2850 receive_close_tolerance,
2851 receiving_routing_id,
2852 receiving_routing,
2853 accrue_on_receipt_flag,
2854 firm_flag,
2855 need_by_date,
2856 promised_date,
2857 from_line_location_id,
2858 inspection_required_flag,
2859 receipt_required_flag,
2860 source_shipment_id,
2861 note_to_receiver,
2862 transaction_flow_header_id,
2863 quantity,
2864 price_discount,
2865 start_date,
2866 end_date,
2867 price_override,
2868 lead_time,
2869 lead_time_unit,
2870 amount,
2871 secondary_quantity,
2872 secondary_unit_of_measure,
2873 attribute_category,
2874 attribute1,
2875 attribute2,
2876 attribute3,
2877 attribute4,
2878 attribute5,
2879 attribute6,
2880 attribute7,
2881 attribute8,
2882 attribute9,
2883 attribute10,
2884 attribute11,
2885 attribute12,
2886 attribute13,
2887 attribute14,
2888 attribute15,
2889 creation_date,
2890 created_by,
2891 last_update_date,
2892 last_updated_by,
2893 last_update_login,
2894 request_id,
2895 program_application_id,
2896 program_id,
2897 program_update_date,
2898 unit_of_measure,
2899 payment_type,
2900 preferred_grade,
2901 taxable_flag,
2902 tax_code_id,
2903 tax_name,
2904 qty_rcv_tolerance
2905 )
2906 SELECT po_line_locations_interface_s.nextval,
2907 PLI.interface_header_id,
2908 PLI.interface_line_id,
2909 PLI.processing_id,
2910 PLI.process_code,
2911 PLI.line_location_id,
2912 'STANDARD', -- shipment_type
2913 1, -- shipment_num
2914 PLI.ship_to_organization_id,
2915 PLI.ship_to_organization_code,
2916 PLI.ship_to_location_id,
2917 PLI.ship_to_location,
2918 PLI.terms_id,
2919 PLI.payment_terms,
2920 PLI.qty_rcv_exception_code,
2921 PLI.freight_carrier,
2922 PLI.fob,
2923 PLI.freight_terms,
2924 PLI.enforce_ship_to_location_code,
2925 PLI.allow_substitute_receipts_flag,
2926 PLI.days_early_receipt_allowed,
2927 PLI.days_late_receipt_allowed,
2928 PLI.receipt_days_exception_code,
2929 PLI.invoice_close_tolerance,
2930 PLI.receive_close_tolerance,
2931 PLI.receiving_routing_id,
2932 PLI.receiving_routing,
2933 PLI.accrue_on_receipt_flag,
2934 PLI.firm_flag,
2935 PLI.need_by_date,
2936 PLI.promised_date,
2937 PLI.from_line_location_id,
2938 PLI.inspection_required_flag,
2939 PLI.receipt_required_flag,
2940 PLI.source_shipment_id,
2941 PLI.note_to_receiver,
2942 PLI.transaction_flow_header_id,
2943 PLI.quantity,
2944 PLI.price_discount,
2945 PLI.effective_date,
2946 PLI.expiration_date,
2947 PLI.unit_price,
2948 PLI.lead_time,
2949 PLI.lead_time_unit,
2950 PLI.amount,
2951 PLI.secondary_quantity,
2952 PLI.secondary_unit_of_measure,
2953 PLI.shipment_attribute_category,
2954 PLI.shipment_attribute1,
2955 PLI.shipment_attribute2,
2956 PLI.shipment_attribute3,
2957 PLI.shipment_attribute4,
2958 PLI.shipment_attribute5,
2959 PLI.shipment_attribute6,
2960 PLI.shipment_attribute7,
2961 PLI.shipment_attribute8,
2962 PLI.shipment_attribute9,
2963 PLI.shipment_attribute10,
2964 PLI.shipment_attribute11,
2965 PLI.shipment_attribute12,
2966 PLI.shipment_attribute13,
2967 PLI.shipment_attribute14,
2968 PLI.shipment_attribute15,
2969 PLI.creation_date,
2970 PLI.created_by,
2971 PLI.last_update_date,
2972 PLI.last_updated_by,
2973 PLI.last_update_login,
2974 PLI.request_id,
2975 PLI.program_application_id,
2976 PLI.program_id,
2977 PLI.program_update_date,
2978 PLI.unit_of_measure,
2979 'DELIVERY', -- payment_type
2980 PLI.preferred_grade,
2981 PLI.taxable_flag,
2982 PLI.tax_code_id,
2983 PLI.tax_name,
2984 PLI.qty_rcv_tolerance
2985 FROM po_lines_interface PLI
2986 WHERE PLI.interface_line_id = p_interface_line_id;
2987
2988 IF (SQL%ROWCOUNT > 0) THEN
2989 SELECT po_line_locations_interface_s.CURRVAL
2990 INTO l_interface_line_location_id
2991 FROM DUAL;
2992
2993 populate_progress_payitem_dist
2994 (p_interface_line_location_id => l_interface_line_location_id);
2995 END IF;
2996
2997 IF (PO_LOG.d_proc) THEN
2998 PO_LOG.proc_end (d_module);
2999 END IF;
3000
3001 EXCEPTION
3002 WHEN OTHERS THEN
3003 PO_MESSAGE_S.add_exc_msg
3004 ( p_pkg_name => d_pkg_name,
3005 p_procedure_name => d_api_name || '.' || d_position
3006 );
3007 RAISE;
3008 END populate_progress_payitem;
3009
3010 ---------------------------------------------------------------------------
3011 -- PDOI for Complex PO Project
3012 --Start of Comments
3013 --Name: populate_progress_payitem
3014 --Pre-requisites:
3015 -- This Procedure needs to be called for only those interface line records,
3016 -- which belong to complex style and have the option "Treat Progress
3017 -- Payments as Contract Financing" is checked. When the
3018 -- line_loc_populated_flag = 'Y', this procedure will not be called.
3019 --Function:
3020 -- This Procedure will create the Pay item that correspond to the contract
3021 -- financing, for the given interface line record.
3022 --Parameters:
3023 --IN:
3024 --IN OUT:
3025 --OUT:
3026 --End of Comments
3027 ---------------------------------------------------------------------------
3028 PROCEDURE populate_progress_payitem_dist
3029 ( p_interface_line_location_id IN NUMBER) IS
3030
3031 d_api_name CONSTANT VARCHAR2(30) := 'populate_progress_payitem_dist';
3032 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
3033 d_position NUMBER;
3034
3035 BEGIN
3036 d_position := 0;
3037
3038 IF (PO_LOG.d_proc) THEN
3039 PO_LOG.proc_begin (d_module);
3040 END IF;
3041
3042 INSERT INTO po_distributions_interface
3043 (
3044 interface_distribution_id,
3045 interface_header_id,
3046 interface_line_id,
3047 interface_line_location_id,
3048 processing_id,
3049 process_code,
3050 distribution_num,
3051 quantity_ordered,
3052 rate_date,
3053 amount_ordered,
3054 destination_type_code,
3055 accrue_on_receipt_flag,
3056 prevent_encumbrance_flag,
3057 creation_date,
3058 created_by,
3059 last_update_date,
3060 last_updated_by,
3061 last_update_login,
3062 request_id,
3063 program_application_id,
3064 program_id,
3065 program_update_date
3066 )
3067 SELECT po_distributions_interface_s.nextval,
3068 PLL.interface_header_id,
3069 PLL.interface_line_id,
3070 PLL.interface_line_location_id,
3071 PLL.processing_id,
3072 PLL.process_code,
3073 1, -- distribution_num
3074 PLL.quantity,
3075 sysdate, -- rate_date
3076 PLL.amount, -- amount_ordered
3077 'EXPENSE', -- destination_type_code
3078 PLL.accrue_on_receipt_flag,
3079 'N', -- prevent_encumbrance_flag
3080 PLL.creation_date,
3081 PLL.created_by,
3082 PLL.last_update_date,
3083 PLL.last_updated_by,
3084 PLL.last_update_login,
3085 PLL.request_id,
3086 PLL.program_application_id,
3087 PLL.program_id,
3088 PLL.program_update_date
3089 FROM po_line_locations_interface PLL
3090 WHERE PLL.interface_line_location_id = p_interface_line_location_id;
3091
3092 IF (PO_LOG.d_proc) THEN
3093 PO_LOG.proc_end (d_module);
3094 END IF;
3095
3096 EXCEPTION
3097 WHEN OTHERS THEN
3098 PO_MESSAGE_S.add_exc_msg
3099 ( p_pkg_name => d_pkg_name,
3100 p_procedure_name => d_api_name || '.' || d_position
3101 );
3102 RAISE;
3103 END populate_progress_payitem_dist;
3104
3105
3106
3107 -----------------------------------------------------------------------
3108 --Start of Comments
3109 --Name: derive_line_num
3110 --Function:
3111 -- Derive line num based on line num display, if necessary.
3112 --Parameters:
3113 --IN:
3114 --IN OUT:
3115 --OUT:
3116 --End of Comments
3117 ------------------------------------------------------------------------
3118 PROCEDURE derive_line_num IS
3119
3120 d_api_name CONSTANT VARCHAR2(30) := 'derive_line_num';
3121 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
3122 d_position NUMBER;
3123
3124 l_intf_header_id_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
3125 l_intf_line_id_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
3126 l_line_num_disp_tbl PO_TBL_VARCHAR100 := PO_TBL_VARCHAR100();
3127 l_line_num NUMBER := 1;
3128 l_clm_info_flag_tbl PO_TBL_VARCHAR1 := PO_TBL_VARCHAR1();
3129 l_line_num_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
3130 l_group_line_id_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
3131 l_is_line_num_disp_valid PO_TBL_VARCHAR1 := PO_TBL_VARCHAR1();
3132 l_header_id NUMBER;
3133 l_po_header_id_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
3134 l_max_line_num_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
3135 -- table used to save the index of the each row
3136 l_num_list DBMS_SQL.NUMBER_TABLE;
3137 -- key of temp table used to identify the derived result
3138 l_key po_session_gt.key%TYPE;
3139 l_index_tbl PO_TBL_NUMBER;
3140 l_index NUMBER;
3141 l_reject_list PO_TBL_NUMBER := PO_TBL_NUMBER();
3142 max_line_num NUMBER;
3143
3144
3145 BEGIN
3146
3147
3148 d_position := 0;
3149
3150 IF (PO_LOG.d_proc) THEN
3151 PO_LOG.proc_begin (d_module);
3152 END IF;
3153
3154 SELECT PLI.interface_header_id,
3155 PLI.interface_line_id,
3156 PLI.line_num_display,
3157 PLI.clm_info_flag,
3158 phi.po_header_id
3159 BULK COLLECT
3160 INTO l_intf_header_id_tbl,
3161 l_intf_line_id_tbl,
3162 l_line_num_disp_tbl,
3163 l_clm_info_flag_tbl,
3164 l_po_header_id_tbl
3165 FROM po_lines_interface PLI,po_headers_interface phi
3166 WHERE PLI.processing_id = PO_PDOI_PARAMS.g_processing_id
3167 AND PLI.interface_header_id = phi.interface_header_id
3168 ORDER BY PLI.interface_header_id, PLI.line_num_display;
3169
3170 l_line_num_tbl.EXTEND(l_intf_line_id_tbl.Count);
3171
3172 l_group_line_id_tbl.EXTEND(l_intf_line_id_tbl.Count);
3173
3174 l_is_line_num_disp_valid.EXTEND(l_intf_line_id_tbl.Count);
3175
3176 validate_line_num_display(l_intf_header_id_tbl,
3177 l_intf_line_id_tbl,
3178 l_line_num_disp_tbl,
3179 l_clm_info_flag_tbl,
3180 l_is_line_num_disp_valid,
3181 l_po_header_id_tbl
3182 );
3183
3184 l_header_id := -9999;
3185
3186 --derive line_num & store in l_line_num_tbl
3187 l_index := 1;
3188 FOR i IN 1..l_intf_line_id_tbl.Count
3189 LOOP
3190 IF l_header_id = l_intf_header_id_tbl(i)
3191 THEN
3192 l_line_num_tbl(i) := l_line_num;
3193 ELSE
3194 IF l_po_header_id_tbl(i) IS NULL
3195 THEN
3196 l_line_num := 1;
3197 ELSE
3198 SELECT Max(line_num) INTO max_line_num
3199 FROM po_lines_merge_v
3200 WHERE po_header_id = l_po_header_id_tbl(i)
3201 AND draft_id = -1;
3202
3203 l_line_num := max_line_num + 1;
3204 END IF;
3205 l_line_num_tbl(i) := l_line_num;
3206 l_header_id := l_intf_header_id_tbl(i);
3207 END IF;
3208 l_line_num := l_line_num + 1;
3209 END LOOP;
3210
3211 --default group_line_id
3212 default_group_line_id(l_intf_header_id_tbl,
3213 l_intf_line_id_tbl,
3214 l_line_num_disp_tbl,
3215 l_is_line_num_disp_valid,
3216 l_group_line_id_tbl
3217 );
3218
3219 --update line_num & group_line_id to lines interface
3220 FORALL i IN 1..l_intf_line_id_tbl.COUNT
3221 UPDATE po_lines_interface
3222 SET line_num = l_line_num_tbl(i),
3223 group_line_id = l_group_line_id_tbl(i)
3224 WHERE interface_header_id = l_intf_header_id_tbl(i)
3225 AND interface_line_id = l_intf_line_id_tbl(i)
3226 AND l_line_num_tbl(i) IS NOT NULL;
3227
3228 FOR i IN 1..l_intf_line_id_tbl.Count
3229 LOOP
3230 IF Nvl(l_is_line_num_disp_valid(i),'Y') = 'N'
3231 THEN
3232 PO_PDOI_ERR_UTL.add_fatal_error
3233 ( p_interface_header_id => l_intf_header_id_tbl(i),
3234 p_error_message_name => 'PO_PDOI_DERV_ERROR',
3235 p_table_name => 'PO_LINES_INTERFACE',
3236 p_column_name => 'LINE_NUM_DISPLAY',
3237 p_column_value => l_line_num_disp_tbl(i)
3238 );
3239 l_reject_list.extend;
3240 l_reject_list(l_reject_list.COUNT) := l_intf_header_id_tbl(i);
3241 END IF;
3242 END LOOP;
3243
3244 PO_PDOI_UTL.reject_headers_intf
3245 ( p_id_param_type => PO_PDOI_CONSTANTS.g_INTERFACE_HEADER_ID,
3246 p_id_tbl => l_reject_list,
3247 p_cascade => FND_API.G_TRUE
3248 );
3249
3250 EXCEPTION
3251 WHEN OTHERS THEN
3252 PO_MESSAGE_S.add_exc_msg
3253 ( p_pkg_name => d_pkg_name,
3254 p_procedure_name => d_api_name || '.' || d_position
3255 );
3256 RAISE;
3257
3258
3259 END derive_line_num;
3260
3261
3262
3263
3264
3265 PROCEDURE default_group_line_id
3266 ( p_intf_header_id_tbl IN PO_TBL_NUMBER,
3267 p_intf_line_id_tbl IN PO_TBL_NUMBER ,
3268 p_line_num_disp_tbl IN PO_TBL_VARCHAR100,
3269 p_is_line_num_disp_valid IN PO_TBL_VARCHAR1,
3270 p_group_line_id_tbl OUT NOCOPY PO_TBL_NUMBER
3271 )
3272 IS
3273
3274 d_api_name CONSTANT VARCHAR2(30) := 'default_group_line_id';
3275 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
3276 d_position NUMBER;
3277 l_group_line_id_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
3278 -- table used to save the index of the each row
3279 l_num_list DBMS_SQL.NUMBER_TABLE;
3280 -- key of temp table used to identify the derived result
3281 l_key po_session_gt.key%TYPE;
3282 l_index_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
3283 l_index NUMBER;
3284 BEGIN
3285
3286 d_position := 0;
3287
3288 IF (PO_LOG.d_proc) THEN
3289 PO_LOG.proc_begin (d_module);
3290 END IF;
3291 p_group_line_id_tbl := PO_TBL_NUMBER();
3292
3293 -- initialize table containing the row number(index)
3294 PO_PDOI_UTL.generate_ordered_num_list
3295 (
3296 p_size => p_intf_line_id_tbl.Count,
3297 x_num_list => l_num_list
3298 );
3299 -- assign a new key used in temporary table
3300 l_key := PO_CORE_S.get_session_gt_nextval;
3301
3302 d_position := 10;
3303
3304 FORALL i IN 1..l_num_list.COUNT
3305 INSERT INTO po_session_gt(KEY, num1, num2, num3, num4, char1, char2)
3306 SELECT l_key,
3307 l_num_list(i), --num1
3308 p_intf_header_id_tbl(i),--num2
3309 p_intf_line_id_tbl(i), --num3
3310 null, --num4
3311 p_line_num_disp_tbl(i), --char1
3312 p_is_line_num_disp_valid(i)--char2
3313 FROM dual;
3314
3315 FORALL i IN 1..l_num_list.COUNT
3316 UPDATE po_session_gt psg1
3317 SET psg1.num4 = ( SELECT psg2.num3 FROM po_session_gt psg2
3318 WHERE Nvl(psg2.char2,'Y') = 'Y'
3319 AND Length(psg2.char1) = 4
3320 AND psg2.char1 = SubStr(psg1.char1,1,4)
3321 AND psg2.num2 = psg1.num2
3322 )
3323 WHERE Nvl(psg1.char2,'Y') = 'Y'
3324 AND Length(psg1.char1) = 6
3325 AND psg1.num3 = p_intf_line_id_tbl(i);
3326
3327 DELETE FROM po_session_gt
3328 WHERE key = l_key
3329 RETURNING num1, num4 BULK COLLECT INTO l_index_tbl , l_group_line_id_tbl;
3330
3331 d_position := 30;
3332
3333 IF (PO_LOG.d_stmt) THEN
3334 PO_LOG.stmt(d_module, d_position, 'l_index_tbl', l_index_tbl);
3335 PO_LOG.stmt(d_module, d_position, 'l_group_line_id_tbl', l_group_line_id_tbl);
3336 END IF;
3337
3338 p_group_line_id_tbl.extend(l_index_tbl.Count);
3339
3340 FOR i IN 1..l_index_tbl.COUNT
3341 LOOP
3342 l_index := l_index_tbl(i);
3343 p_group_line_id_tbl(l_index) := l_group_line_id_tbl(i);
3344 END LOOP;
3345
3346 IF (PO_LOG.d_proc) THEN
3347 PO_LOG.proc_end (d_module);
3348 END IF;
3349
3350 EXCEPTION
3351 WHEN OTHERS THEN
3352 PO_MESSAGE_S.add_exc_msg
3353 ( p_pkg_name => d_pkg_name,
3354 p_procedure_name => d_api_name || '.' || d_position
3355 );
3356
3357 RAISE;
3358
3359 END default_group_line_id;
3360
3361
3362
3363 PROCEDURE validate_line_num_display
3364 ( p_intf_header_id_tbl IN PO_TBL_NUMBER,
3365 p_intf_line_id_tbl IN PO_TBL_NUMBER,
3366 p_line_num_disp_tbl IN PO_TBL_VARCHAR100,
3367 p_clm_info_flag_tbl IN PO_TBL_VARCHAR1,
3368 p_is_line_num_disp_valid OUT NOCOPY PO_TBL_VARCHAR1,
3369 p_po_line_id_tbl IN PO_TBL_NUMBER
3370 )
3371 IS
3372
3373 d_api_name CONSTANT VARCHAR2(30) := 'validate_line_num_display';
3374 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
3375 d_position NUMBER;
3376 l_index_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
3377 l_is_line_num_disp_valid PO_TBL_VARCHAR1 := PO_TBL_VARCHAR1();
3378 -- table used to save the index of the each row
3379 l_num_list DBMS_SQL.NUMBER_TABLE;
3380 -- key of temp table used to identify the derived result
3381 l_key po_session_gt.key%TYPE;
3382 l_index NUMBER;
3383 BEGIN
3384
3385 p_is_line_num_disp_valid := PO_TBL_VARCHAR1();
3386 d_position := 0;
3387
3388 IF (PO_LOG.d_proc) THEN
3389 PO_LOG.proc_begin (d_module);
3390 END IF;
3391 -- initialize table containing the row number(index)
3392 PO_PDOI_UTL.generate_ordered_num_list
3393 (
3394 p_size => p_intf_line_id_tbl.Count,
3395 x_num_list => l_num_list
3396 );
3397 -- assign a new key used in temporary table
3398 l_key := PO_CORE_S.get_session_gt_nextval;
3399
3400 d_position := 10;
3401
3402 FORALL i IN 1..l_num_list.COUNT
3403 INSERT INTO po_session_gt(KEY, num1, num2, num3, char1, char2, char3, num4)
3404 SELECT l_key,
3405 l_num_list(i), --num1
3406 p_intf_header_id_tbl(i),--num2
3407 p_intf_line_id_tbl(i), --num3
3408 p_line_num_disp_tbl(i), --char1
3409 'Y', --char2
3410 p_clm_info_flag_tbl(i), --char3
3411 p_po_line_id_tbl(i)
3412 FROM dual;
3413
3414 --check for duplicacy of line_num_display within the same header_id
3415 FORALL i IN 1..l_num_list.COUNT
3416 UPDATE po_session_gt psg1
3417 SET char2 = 'N'
3418 WHERE 1 <= ( SELECT Count(num3)
3419 FROM po_session_gt psg2
3420 WHERE psg2.char1 = psg1.char1
3421 AND psg2.num2 = psg1.num2
3422 AND psg2.num3 <> psg1.num3
3423 )
3424 OR psg1.char1 IN ( SELECT line_num_display FROM po_lines_merge_v
3425 WHERE po_header_id = p_po_line_id_tbl(i)
3426 )
3427 AND psg1.num3 = p_intf_line_id_tbl(i);
3428
3429 d_position := 20;
3430
3431 UPDATE po_session_gt
3432 SET char2 = 'N'
3433 WHERE Length(char1) NOT IN (4,6)
3434 OR
3435 NOT REGEXP_LIKE (SubStr(char1,1,4),'^[[:digit:]]+$') --not a clin or slin
3436 OR
3437 (Length(char1) = 6 --slin
3438 AND Nvl(char3,'N') = 'Y' --info
3439 AND NOT REGEXP_LIKE (SubStr(char1,5,2),'^[[:digit:]]+$') --last two characters not digits
3440 )
3441 OR
3442 (Length(char1) = 6 --slin
3443 AND Nvl(char3,'N') = 'N' --priced
3444 AND NOT REGEXP_LIKE (SubStr(char1,5,2),'^[[:alpha:]]+$') --last two charcters not alphabets
3445 );
3446
3447 DELETE FROM po_session_gt
3448 WHERE key = l_key
3449 RETURNING num1, char2 BULK COLLECT INTO l_index_tbl , l_is_line_num_disp_valid;
3450
3451 d_position := 30;
3452
3453 IF (PO_LOG.d_stmt) THEN
3454 PO_LOG.stmt(d_module, d_position, 'l_index_tbl', l_index_tbl);
3455 PO_LOG.stmt(d_module, d_position, 'l_is_line_num_disp_valid', l_is_line_num_disp_valid);
3456 END IF;
3457
3458 p_is_line_num_disp_valid.extend(l_is_line_num_disp_valid.Count);
3459 FOR i IN 1..l_index_tbl.COUNT
3460 LOOP
3461 l_index := l_index_tbl(i);
3462 p_is_line_num_disp_valid(l_index) := l_is_line_num_disp_valid(i);
3463 END LOOP;
3464
3465 EXCEPTION
3466 WHEN OTHERS THEN
3467 PO_MESSAGE_S.add_exc_msg
3468 (
3469 p_pkg_name => d_pkg_name,
3470 p_procedure_name => d_api_name || '.' || d_position
3471 );
3472 RAISE;
3473 END validate_line_num_display;
3474
3475
3476 END PO_PDOI_PREPROC_PVT;