1 PACKAGE BODY PO_PDOI_HEADER_GROUPING_PVT AS
2 /* $Header: PO_PDOI_HEADER_GROUPING_PVT.plb 120.12 2012/01/13 12:12:07 sbontala ship $ */
3
4 d_pkg_name CONSTANT VARCHAR2(50) :=
5 PO_LOG.get_package_base('PO_PDOI_HEADER_GROUPING_PVT');
6
7 -------------------------------------------------------
8 ----------- PRIVATE PROCEDURES PROTOTYPE --------------
9 -------------------------------------------------------
10
11 PROCEDURE assign_round_num
12 ( x_all_headers_processed OUT NOCOPY VARCHAR2
13 );
14
15 PROCEDURE assign_draft_id;
16
17 PROCEDURE expire_lines_by_catalog_name;
18
19 PROCEDURE init_doc_info_tbl
20 ( p_intf_header_id_tbl PO_TBL_NUMBER
21 );
22
23 PROCEDURE check_new_draft_needed
24 ( p_draft_id IN NUMBER,
25 p_status IN VARCHAR2,
26 x_new_draft_needed OUT NOCOPY VARCHAR2
27 );
28
29 -------------------------------------------------------
30 -------------- PUBLIC PROCEDURES ----------------------
31 -------------------------------------------------------
32
33 -----------------------------------------------------------------------
34 --Start of Comments
35 --Name: process
36 --Function:
37 -- Main process for header grouping
38 --Parameters:
39 --IN:
40 --IN OUT:
41 --OUT:
42 --x_all_headers_processed
43 -- FND_API.G_TRUE if all the headers are processed
44 -- FND_API.G_FALSE otherwise
45 --End of Comments
46 ------------------------------------------------------------------------
47 PROCEDURE process
48 ( x_all_headers_processed OUT NOCOPY VARCHAR2
49 ) IS
50
51 d_api_name CONSTANT VARCHAR2(30) := 'process';
52 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
53 d_position NUMBER;
54
55 BEGIN
56 d_position := 0;
57
58 IF (PO_LOG.d_proc) THEN
59 PO_LOG.proc_begin (d_module);
60 END IF;
61
62 PO_TIMING_UTL.start_time(PO_PDOI_CONSTANTS.g_T_HEADER_GROUPING);
63
64 d_position := 10;
65
66 -- set round number for interface records that will be processed in
67 -- current round
68 assign_round_num
69 ( x_all_headers_processed => x_all_headers_processed );
70
71 d_position := 20;
72
73 -- create draft for records that will be processed in current round
74 IF (x_all_headers_processed = FND_API.G_FALSE) THEN
75 assign_draft_id;
76
77 d_position := 30;
78
79 -- If lines for a particular catalog name will get expired, move those
80 -- records to draft table and set expiration date for those lines
81 IF (PO_PDOI_PARAMS.g_request.calling_module =
82 PO_PDOI_CONSTANTS.g_CALL_MOD_CATALOG_UPLOAD
83 AND
84 PO_PDOI_PARAMS.g_request.catalog_to_expire IS NOT NULL) THEN
85
86 expire_lines_by_catalog_name;
87 END IF;
88 END IF;
89
90 d_position := 40;
91
92 PO_INTERFACE_ERRORS_UTL.flush_errors_tbl;
93
94 d_position := 50;
95
96 PO_PDOI_UTL.commit_work;
97
98 PO_TIMING_UTL.stop_time(PO_PDOI_CONSTANTS.g_T_HEADER_GROUPING);
99
100 IF (PO_LOG.d_proc) THEN
101 PO_LOG.proc_end (d_module);
102 END IF;
103
104 EXCEPTION
105 WHEN OTHERS THEN
106 PO_MESSAGE_S.add_exc_msg
107 ( p_pkg_name => d_pkg_name,
108 p_procedure_name => d_api_name || '.' || d_position
109 );
110 RAISE;
111 END process;
112
113
114 -------------------------------------------------------
115 -------------- PRIVATE PROCEDURES ---------------------
116 -------------------------------------------------------
117
118 -----------------------------------------------------------------------
119 --Start of Comments
120 --Name: assign_round_num
121 --Function:
122 -- For documents that can be processed in the same round, stamp those
123 -- record with processing_round_num = current_round_num so that they
124 -- will be processed later on
125 --Parameters:
126 --IN:
127 --IN OUT:
128 --OUT:
129 --x_all_headers_processed
130 -- FND_API.G_TRUE if all the headers are processed
131 -- FND_API.G_FALSE otherwise
132 --End of Comments
133 ------------------------------------------------------------------------
134 PROCEDURE assign_round_num
135 ( x_all_headers_processed OUT NOCOPY VARCHAR2
136 ) IS
137
138 d_api_name CONSTANT VARCHAR2(30) := 'assign_round_num';
139 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
140 d_position NUMBER;
141
142 l_process_update_replace BOOLEAN := FALSE;
143
144 l_intf_header_id_tbl PO_TBL_NUMBER;
145 l_po_header_id_tbl PO_TBL_NUMBER;
146
147 l_process_list DBMS_SQL.NUMBER_TABLE;
148
149 BEGIN
150 d_position := 0;
151
152 IF (PO_LOG.d_proc) THEN
153 PO_LOG.proc_begin (d_module);
154 END IF;
155
156 x_all_headers_processed := FND_API.G_FALSE;
157
158 -- Increment round number
159 PO_PDOI_PARAMS.g_current_round_num := PO_PDOI_PARAMS.g_current_round_num + 1;
160
161 IF (PO_LOG.d_stmt) THEN
162 PO_LOG.stmt(d_module, d_position, 'Current Round Number: ' ||
163 PO_PDOI_PARAMS.g_current_round_num);
164 END IF;
165
166 IF (PO_PDOI_PARAMS.g_original_doc_processed = FND_API.G_FALSE) THEN
167 d_position := 10;
168
169 -- First, we need to process NEW documents
170
171 SELECT interface_header_id
172 BULK COLLECT
173 INTO l_intf_header_id_tbl
174 FROM (SELECT interface_header_id
175 FROM po_headers_interface
176 WHERE processing_round_num IS NULL
177 AND processing_id = PO_PDOI_PARAMS.g_processing_id
178 AND action IN (PO_PDOI_CONSTANTS.g_action_ORIGINAL,
179 PO_PDOI_CONSTANTS.g_action_ADD)
180 ORDER BY interface_header_id)
181 WHERE rownum <= PO_PDOI_PARAMS.g_request.batch_size;
182
183 IF (SQL%ROWCOUNT = 0) THEN
184 d_position := 20;
185
186 -- mark g_original_doc_processed to TRUE so that we won't process
187 -- NEW documents the next time we come here
188 PO_PDOI_PARAMS.g_original_doc_processed := FND_API.G_TRUE;
189
190 IF (PO_LOG.d_stmt) THEN
191 PO_LOG.stmt(d_module, d_position, 'Finished progressing doc with ' ||
192 'action = original ');
193 END IF;
194
195 -- IF there is no new doucment to create, we can go ahead and look for
196 -- documents to update/replace
197 l_process_update_replace := TRUE;
198 ELSE
199
200 d_position := 30;
201
202 FORALL i IN 1..l_intf_header_id_tbl.COUNT
203 UPDATE po_headers_interface
204 SET processing_round_num = PO_PDOI_PARAMS.g_current_round_num
205 WHERE interface_header_id = l_intf_header_id_tbl(i);
206
207 IF (PO_LOG.d_stmt) THEN
208 PO_LOG.stmt(d_module, d_position, 'updated ' || SQL%ROWCOUNT ||
209 'rows with current round number ');
210 END IF;
211 END IF;
212
213 ELSE
214 d_position := 40;
215
216 l_process_update_replace := TRUE;
217 END IF;
218
219
220 IF (l_process_update_replace) THEN
221 d_position := 50;
222
223 -- if there are multiple records in headers interface, they can be processed
224 -- together only if they are not acting on the same document.
225
226 SELECT interface_header_id,
227 po_header_id
228 BULK COLLECT
229 INTO l_intf_header_id_tbl,
230 l_po_header_id_tbl
231 FROM po_headers_interface
232 WHERE processing_round_num IS NULL
233 AND processing_id = PO_PDOI_PARAMS.g_processing_id
234 AND po_header_id IS NOT NULL
235 AND action IN (PO_PDOI_CONSTANTS.g_action_UPDATE,
236 PO_PDOI_CONSTANTS.g_action_REPLACE)
237 ORDER BY interface_header_id;
238
239 d_position := 60;
240
241 FOR i IN 1..l_intf_header_id_tbl.COUNT LOOP
242 -- Use an associative array to figure out whether there is already
243 -- another header interface record in the same round that tries to
244 -- update the same document. If so, defer the current update to the
245 -- next round.
246
247 IF (l_po_header_id_tbl IS NOT NULL AND
248 NOT l_process_list.EXISTS(l_po_header_id_tbl(i))) THEN
249 l_process_list(l_po_header_id_tbl(i)) := l_intf_header_id_tbl(i);
250 END IF;
251
252 IF (l_process_list.COUNT >= PO_PDOI_PARAMS.g_request.batch_size) THEN
253 IF (PO_LOG.d_stmt) THEN
254 PO_LOG.stmt(d_module, d_position, '# of documents to process has ' ||
255 'reached the limit. Wait for the next round');
256 END IF;
257
258 -- exit the loop if the number of document to process exceeds
259 -- the specified limit
260 EXIT;
261 END IF;
262 END LOOP;
263
264 d_position := 70;
265
266 IF (l_process_list.COUNT > 0) THEN
267 -- assign round number for those in process list
268 FORALL i IN INDICES OF l_process_list
269 UPDATE po_headers_interface
270 SET processing_round_num = PO_PDOI_PARAMS.g_current_round_num
271 WHERE interface_header_id = l_process_list(i);
272
273 IF (PO_LOG.d_stmt) THEN
274 PO_LOG.stmt(d_module, d_position, 'updated ' || SQL%ROWCOUNT ||
275 'rows with current round number ');
276 END IF;
277
278 ELSE
279
280 IF (PO_LOG.d_stmt) THEN
281 PO_LOG.stmt(d_module, d_position, '** No more doc to process ***');
282 END IF;
283
284 x_all_headers_processed := FND_API.G_TRUE;
285 END IF;
286
287 END IF;
288
289 IF (PO_LOG.d_proc) THEN
290 PO_LOG.proc_end (d_module);
291 END IF;
292
293 EXCEPTION
294 WHEN OTHERS THEN
295 PO_MESSAGE_S.add_exc_msg
296 ( p_pkg_name => d_pkg_name,
297 p_procedure_name => d_api_name || '.' || d_position
298 );
299 RAISE;
300 END assign_round_num;
301
302
303
304 -----------------------------------------------------------------------
305 --Start of Comments
306 --Name: assign_draft_id
307 --Function:
308 -- Stamp header interface records with draft ids. If the document the
309 -- interface record tries to process already has a draft version, reuse
310 -- the draft; otherwise, create a new draft
311 --Parameters:
312 --IN:
313 --IN OUT:
314 --OUT:
315 --End of Comments
316 ------------------------------------------------------------------------
317 PROCEDURE assign_draft_id IS
318
319 d_api_name CONSTANT VARCHAR2(30) := 'assign_draft_id';
320 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
321 d_position NUMBER;
322
323 l_draft_id_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
324 l_reject_list PO_TBL_NUMBER := PO_TBL_NUMBER();
325
326 l_new_dft_idx_tbl PO_PDOI_UTL.pls_integer_tbl_type :=
327 PO_PDOI_UTL.pls_integer_tbl_type();
328 l_existing_dft_idx_tbl PO_PDOI_UTL.pls_integer_tbl_type :=
329 PO_PDOI_UTL.pls_integer_tbl_type();
330
331 l_intf_header_id_tbl PO_TBL_NUMBER;
332 l_po_header_id_tbl PO_TBL_NUMBER;
333 l_action_tbl PO_TBL_VARCHAR30;
334 l_revision_num_tbl PO_TBL_NUMBER;
335
336 l_tmp_draft_id PO_DRAFTS.draft_id%TYPE;
337 l_tmp_draft_status PO_DRAFTS.status%TYPE;
338 l_tmp_draft_owner_role PO_DRAFTS.owner_role%TYPE;
339 l_new_draft_needed VARCHAR2(1);
340 l_return_status VARCHAR2(1);
341
342 l_status PO_DRAFTS.status%TYPE := PO_DRAFTS_PVT.g_status_PDOI_PROCESSING;
343
344 l_locking_allowed VARCHAR2(1);
345 l_message VARCHAR2(30);
346
347 l_intf_hdr_id NUMBER; -- bug5129752
348 l_temp NUMBER;
349 BEGIN
350 d_position := 0;
351
352 IF (PO_LOG.d_proc) THEN
353 PO_LOG.proc_begin (d_module);
354 END IF;
355
356 --SQL What: get all records that will be processed in current round. It also
357 -- locks the po_Headers of documents that are being updated
358 --SQL Why: For each of this record, we need to find out whether we can
359 -- reuse an existing draft or have to create a new draft
360 SELECT PHI.interface_header_id,
361 PHI.po_header_id,
362 PHI.action,
363 NVL(PH.revision_num, 0)
364 BULK COLLECT
365 INTO l_intf_header_id_tbl,
366 l_po_header_id_tbl,
367 l_action_tbl,
368 l_revision_num_tbl
369 FROM po_headers_interface PHI,
370 po_headers_all PH
371 WHERE PHI.processing_id = PO_PDOI_PARAMS.g_processing_id
372 AND PHI.processing_round_num = PO_PDOI_PARAMS.g_current_round_num
373 AND PHI.po_header_id = PH.po_header_id(+);
374 --For Bug: 11794764.Refer bug for reference.
375 --FOR UPDATE OF PH.po_header_id;
376
377 FOR i IN 1 .. l_po_header_id_tbl.count LOOP
378 ---BEGIN BUG: 13588901 : Adding the no data found exception handler code
379 BEGIN
380 SELECT PO_HEADER_ID
381 INTO l_temp
382 FROM po_headers_all
383 WHERE po_header_id = l_po_header_id_tbl(i)
384 FOR UPDATE OF po_header_id;
385
386
387 EXCEPTION
388 WHEN NO_DATA_FOUND THEN
389 NULL;
390 END;
391 ---END BUG : 13588901
392 END LOOP;
393
394 d_position := 10;
395
396 -- initialize structure that stores additional information for each
397 -- interface header record
398
399 init_doc_info_tbl
400 ( p_intf_header_id_tbl => l_intf_header_id_tbl
401 );
402
403 -- allocate space to store draft ids
404 l_draft_id_tbl.extend(l_intf_header_id_tbl.COUNT);
405
406 FOR i IN 1..l_intf_header_id_tbl.COUNT LOOP
407 IF (l_action_tbl(i) = PO_PDOI_CONSTANTS.g_action_UPDATE) THEN
408 d_position := 20;
409
410 -- If we are updating a document, a draft for the document may or may
411 -- not exist. if the draft exists, reuse it; if not, create a new
412 -- draft
413
414 PO_DRAFTS_PVT.find_draft
415 ( p_po_header_id => l_po_header_id_tbl(i),
416 x_draft_id => l_tmp_draft_id,
417 x_draft_status => l_tmp_draft_status,
418 x_draft_owner_role => l_tmp_draft_owner_role
419 );
420
421 d_position := 30;
422
423 IF (l_tmp_draft_id IS NULL) THEN
424 IF (PO_LOG.d_stmt) THEN
425 PO_LOG.stmt(d_module, d_position, 'draft id not found');
426 END IF;
427
428 d_position := 40;
429
430 -- need to create a new draft
431 l_new_dft_idx_tbl.extend;
432 l_new_dft_idx_tbl(l_new_dft_idx_tbl.COUNT) := i;
433 l_draft_id_tbl(i) := PO_DRAFTS_PVT.draft_id_nextval;
434
435 ELSE
436 IF (l_tmp_draft_status IN (PO_DRAFTS_PVT.g_status_PDOI_PROCESSING,
437 PO_DRAFTS_PVT.g_status_PDOI_ERROR)) THEN
438
439 d_position := 50;
440
441 IF (PO_LOG.d_stmt) THEN
442 PO_LOG.stmt(d_module, d_position, 'existing draft is processed ' ||
443 'by PDOI. check whether the previous PDOI is still ' ||
444 'working on it');
445 END IF;
446
447 -- check whether the existing draft is still valid, or it is just
448 -- data corruption caused by incomplete execution of the previous
449 -- PDOI run.
450 check_new_draft_needed
451 ( p_draft_id => l_tmp_draft_id,
452 p_status => l_tmp_draft_status,
453 x_new_draft_needed => l_new_draft_needed
454 );
455
456 d_position := 60;
457
458
459 IF (l_new_draft_needed = FND_API.G_TRUE) THEN
460 d_position := 70;
461
462 IF (PO_LOG.d_stmt) THEN
463 PO_LOG.stmt(d_module, d_position, 'old draft can be removed. ' ||
464 'Creating a new one');
465 END IF;
466
467 PO_DRAFTS_PVT.remove_draft_changes
468 ( p_draft_id => l_tmp_draft_id,
469 p_exclude_ctrl_tbl => FND_API.G_FALSE,
470 x_return_status => l_return_status
471 );
472
473 -- new draft to be created
474 l_new_dft_idx_tbl.EXTEND;
475 l_new_dft_idx_tbl(l_new_dft_idx_tbl.COUNT) := i;
476 l_draft_id_tbl(i) := PO_DRAFTS_PVT.draft_id_nextval;
477 ELSE
478 d_position := 80;
479
480 IF (PO_LOG.d_stmt) THEN
481 PO_LOG.stmt(d_module, d_position, 'old draft is still being ' ||
482 'worked on. Fail the current intf record');
483 END IF;
484
485 PO_PDOI_ERR_UTL.add_fatal_error
486 ( p_interface_header_id => l_intf_header_id_tbl(i),
487 p_error_message_name => 'PO_LOCKED_OR_INVALID_STS',
488 p_table_name => 'PO_HEADERS_INTERFACE',
489 p_column_name => 'DRAFT_ID',
490 p_column_value => l_tmp_draft_id
491 );
492
493 -- the existing draft is still running. Need to reject the interface
494 -- record for the current run as it cannot be processed
495 l_reject_list.EXTEND;
496 l_reject_list(l_reject_list.COUNT) := l_intf_header_id_tbl(i);
497 END IF;
498
499 ELSE
500
501 d_position := 90;
502
503 IF (PO_LOG.d_stmt) THEN
504 PO_LOG.stmt(d_module, d_position, 'draft with id ' ||
505 l_tmp_draft_id || ' can be reused.');
506 END IF;
507
508 -- other draft status -- reuse the draft
509 l_draft_id_tbl(i) := l_tmp_draft_id;
510 l_existing_dft_idx_tbl.extend;
511 l_existing_dft_idx_tbl(l_existing_dft_idx_tbl.COUNT) := i;
512 END IF;
513 END IF;
514
515 ELSE
516 d_position := 100;
517
518 -- actions that cause new documents to be created
519 l_new_dft_idx_tbl.extend;
520 l_new_dft_idx_tbl(l_new_dft_idx_tbl.COUNT) := i;
521 l_draft_id_tbl(i) := PO_DRAFTS_PVT.draft_id_nextval;
522 END IF;
523 END LOOP;
524
525 d_position := 110;
526
527 PO_PDOI_UTL.reject_headers_intf
528 ( p_id_param_type => PO_PDOI_CONSTANTS.g_INTERFACE_HEADER_ID,
529 p_id_tbl => l_reject_list,
530 p_cascade => FND_API.G_TRUE
531 );
532
533 d_position := 120;
534
535 -- stamp draft_id value to interface table
536 FORALL i IN 1..l_intf_header_id_tbl.COUNT
537 UPDATE po_headers_interface
538 SET draft_id = l_draft_id_tbl(i)
539 WHERE interface_header_id = l_intf_header_id_tbl(i)
540 AND processing_id = PO_PDOI_PARAMS.g_processing_id;
541
542 d_position := 130;
543
544 -- Since DB 10g (bug4340345) has probems using VALUES OF against EMPTY
545 -- COLLECTION, Check the collection and call FORALL only if it's not empty
546
547 IF (l_new_dft_idx_tbl.COUNT > 0) THEN
548 d_position := 140;
549 -- Create new drafts
550 FORALL i IN VALUES OF l_new_dft_idx_tbl
551 INSERT INTO po_drafts
552 ( draft_id,
553 document_id,
554 revision_num,
555 owner_user_id,
556 owner_role,
557 status,
558 last_update_date,
559 last_updated_by,
560 last_update_login,
561 creation_date,
562 created_by,
563 request_id
564 )
565 VALUES
566 ( l_draft_id_tbl(i),
567 l_po_header_id_tbl(i),
568 l_revision_num_tbl(i),
569 FND_GLOBAL.user_id,
570 PO_PDOI_PARAMS.g_request.role,
571 l_status,
572 SYSDATE,
573 FND_GLOBAL.user_id,
574 FND_GLOBAL.login_id,
575 SYSDATE,
576 FND_GLOBAL.user_id,
577 FND_GLOBAL.conc_request_id
578 );
579
580 END IF;
581
582 -- bug5129752
583 -- Update new draft flag
584 FOR i IN 1..l_new_dft_idx_tbl.COUNT LOOP
585 -- get the interface header id that creates the new draft, and set
586 -- the new draft flag
587 l_intf_hdr_id := l_intf_header_id_tbl ( l_new_dft_idx_tbl(i) );
588 PO_PDOI_PARAMS.g_docs_info(l_intf_hdr_id).new_draft := FND_API.G_TRUE;
589
590 END LOOP;
591
592 d_position := 150;
593
594 IF (PO_LOG.d_stmt) THEN
595 PO_LOG.stmt(d_module, d_position,
596 'Number of existing drafts: ' || l_existing_dft_idx_tbl.COUNT);
597 END IF;
598
599 -- Since DB 10g has probems using VALUES OF against EMPTYCOLLECTION,
600 -- Check the collection and call FORALL only if it's not empty
601
602 IF (l_existing_dft_idx_tbl.COUNT > 0) THEN
603 d_position := 160;
604 -- If we are updating an existing draft, we need to temporarily set the
605 -- status to PDOI_PROCESSING
606 FORALL i IN VALUES OF l_existing_dft_idx_tbl
607 UPDATE po_drafts
608 SET status = l_status,
609 request_id = FND_GLOBAL.conc_request_id,
610 last_update_date = SYSDATE,
611 last_updated_by = FND_GLOBAL.user_id
612 WHERE draft_id = l_draft_id_tbl(i);
613 END IF;
614
615 IF (PO_LOG.d_proc) THEN
616 PO_LOG.proc_end (d_module);
617 END IF;
618
619 EXCEPTION
620 WHEN OTHERS THEN
621 PO_MESSAGE_S.add_exc_msg
622 ( p_pkg_name => d_pkg_name,
623 p_procedure_name => d_api_name || '.' || d_position
624 );
625 RAISE;
626 END assign_draft_id;
627
628 -----------------------------------------------------------------------
629 --Start of Comments
630 --Name: expire_lines_by_catalog_name
631 --Function:
632 -- Expire all the lines that match the catalog name specified in the parameter.
633 -- This involves bringing the lines to draft table.
634 --Parameters:
635 --IN:
636 --IN OUT:
637 --OUT:
638 --End of Comments
639 ------------------------------------------------------------------------
640 PROCEDURE expire_lines_by_catalog_name IS
641
642 d_api_name CONSTANT VARCHAR2(30) := 'expire_lines_by_catalog_name';
643 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
644 d_position NUMBER;
645
646 l_draft_id_tbl PO_TBL_NUMBER;
647 l_line_id_tbl PO_TBL_NUMBER;
648 l_delete_flag_tbl PO_TBL_VARCHAR1 := PO_TBL_VARCHAR1();
649
650 l_record_exist_tbl PO_TBL_VARCHAR1;
651 BEGIN
652 d_position := 0;
653
654 IF (PO_LOG.d_proc) THEN
655 PO_LOG.proc_begin (d_module);
656 END IF;
657
658 --SQL What: Get all the existing lines that match the category name given
659 -- Make sure that the lines are not cancelled, closed or expired
660 --SQL Why: All those lines need to be expired.
661 SELECT PHI.draft_id,
662 POL.po_line_id
663 BULK COLLECT
664 INTO l_draft_id_tbl,
665 l_line_id_tbl
666 FROM po_headers_interface PHI,
667 po_lines_all POL
668 WHERE PHI.processing_id = PO_PDOI_PARAMS.g_processing_id
669 AND PHI.processing_round_num = PO_PDOI_PARAMS.g_current_round_num
670 AND PHI.po_header_id = POL.po_header_id
671 AND POL.catalog_name = PO_PDOI_PARAMS.g_request.catalog_to_expire
672 AND NVL(POL.cancel_flag, 'N') = 'N'
673 AND NVL(POL.closed_code, 'OPEN') <> 'FINALLY CLOSED'
674 AND NVL(POL.expiration_date, SYSDATE+1) > SYSDATE;
675
676 IF (PO_LOG.d_stmt) THEN
677 PO_LOG.stmt(d_module, d_position, 'Number of lines to expire',
678 l_line_id_tbl.COUNT);
679 END IF;
680
681 IF (l_line_id_tbl.COUNT > 0) THEN
682 d_position := 10;
683
684 l_delete_flag_tbl.EXTEND(l_line_id_tbl.COUNT);
685
686 -- Bring all lines with matching catalog name to draft table
687 PO_LINES_DRAFT_PKG.sync_draft_from_txn
688 ( p_po_line_id_tbl => l_line_id_tbl,
689 p_draft_id_tbl => l_draft_id_tbl,
690 p_delete_flag_tbl => l_delete_flag_tbl,
691 x_record_already_exist_tbl => l_record_exist_tbl
692 );
693
694 d_position := 20;
695
696 -- set expiration date of the lines
697 FORALL i IN 1..l_line_id_tbl.COUNT
698 UPDATE po_lines_draft_all
699 SET expiration_date = TRUNC(SYSDATE-1)
700 WHERE po_line_id = l_line_id_tbl(i)
701 AND draft_id = l_draft_id_tbl(i);
702
703 END IF;
704
705 IF (PO_LOG.d_proc) THEN
706 PO_LOG.proc_end (d_module);
707 END IF;
708
709 EXCEPTION
710 WHEN OTHERS THEN
711 PO_MESSAGE_S.add_exc_msg
712 ( p_pkg_name => d_pkg_name,
713 p_procedure_name => d_api_name || '.' || d_position
714 );
715 RAISE;
716 END expire_lines_by_catalog_name;
717
718 -----------------------------------------------------------------------
719 --Start of Comments
720 --Name: init_doc_info_tbl
721 --Function:
722 -- initialize a structure that holds extra header interface record information
723 -- such has number of processed lines, whether there are lines to notify, etc.
724 --Parameters:
725 --IN:
726 --p_intf_header_id_tbl
727 -- list of interface header ids
728 --IN OUT:
729 --OUT:
730 --End of Comments
731 ------------------------------------------------------------------------
732 PROCEDURE init_doc_info_tbl
733 ( p_intf_header_id_tbl PO_TBL_NUMBER
734 ) IS
735
736 d_api_name CONSTANT VARCHAR2(30) := 'init_doc_info_tbl';
737 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
738 d_position NUMBER;
739
740 l_id PO_HEADERS_INTERFACE.interface_header_id%TYPE;
741
742
743 -- bug5215871 START
744 l_reject_count NUMBER;
745 -- bug5215871 END
746
747 BEGIN
748 d_position := 0;
749
750 IF (PO_LOG.d_proc) THEN
751 PO_LOG.proc_begin (d_module);
752 END IF;
753
754 PO_PDOI_PARAMS.g_docs_info.DELETE;
755
756 d_position := 10;
757
758 FOR i IN 1..p_intf_header_id_tbl.COUNT LOOP
759 l_id := p_intf_header_id_tbl(i);
760
761 -- bug5215871
762 -- Count the number of the lines that are already rejected before
763 -- PDOI processes them. We need to include them processed and errored
764 -- out.
765 SELECT count(*)
766 INTO l_reject_count
767 FROM po_lines_interface PLI
768 WHERE PLI.interface_header_id = p_intf_header_id_tbl(i)
769 AND PLI.process_code = PO_PDOI_CONSTANTS.g_PROCESS_CODE_REJECTED;
770
771 PO_PDOI_PARAMS.g_docs_info(l_id).number_of_processed_lines :=
772 l_reject_count;
773 PO_PDOI_PARAMS.g_docs_info(l_id).number_of_errored_lines :=
774 l_reject_count;
775 PO_PDOI_PARAMS.g_docs_info(l_id).number_of_valid_lines := 0;
776 PO_PDOI_PARAMS.g_docs_info(l_id).err_tolerance_exceeded := FND_API.G_FALSE;
777 PO_PDOI_PARAMS.g_docs_info(l_id).has_errors := FND_API.G_FALSE;
778 PO_PDOI_PARAMS.g_docs_info(l_id).has_lines_to_notify := FND_API.G_FALSE;
779 PO_PDOI_PARAMS.g_docs_info(l_id).new_draft := FND_API.G_FALSE; -- bug5129752
780 END LOOP;
781
782 IF (PO_LOG.d_proc) THEN
783 PO_LOG.proc_end (d_module);
784 END IF;
785
786 EXCEPTION
787 WHEN OTHERS THEN
788 PO_MESSAGE_S.add_exc_msg
789 ( p_pkg_name => d_pkg_name,
790 p_procedure_name => d_api_name || '.' || d_position
791 );
792 RAISE;
793 END init_doc_info_tbl;
794
795
796 -----------------------------------------------------------------------
797 --Start of Comments
798 --Name: check_new_draft_needed
799 --Function:
800 -- Check whether the previous draft is still being processed by PDOI.
801 -- If not, then return a flag telling the caller to create a new draft,
802 -- as the existing one is no longer being processed.
803 --Parameters:
804 --IN:
805 --p_draft_id
806 -- draft unique identifier
807 --p_status
808 -- status of the draft. (status will be PDOI related. Possible values:
809 -- PDOI_PROCESSING, PDOI_ERROR
810 --IN OUT:
811 --OUT:
812 --x_new_draft_needed
813 -- FND_API.G_TRUE if a new draft needs to be created
814 -- FND_API.G_FALSE if the old draft is still effective
815 --End of Comments
816 ------------------------------------------------------------------------
817 PROCEDURE check_new_draft_needed
818 ( p_draft_id IN NUMBER,
819 p_status IN VARCHAR2,
820 x_new_draft_needed OUT NOCOPY VARCHAR2
821 ) IS
822
823 d_api_name CONSTANT VARCHAR2(30) := 'check_new_draft_needed';
824 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
825 d_position NUMBER;
826
827 l_dft_request_id PO_DRAFTS.request_id%TYPE;
828 l_cur_request_id NUMBER;
829
830
831 BEGIN
832 d_position := 0;
833
834 IF (PO_LOG.d_proc) THEN
835 PO_LOG.proc_begin (d_module, 'p_draft_id', p_draft_id);
836 PO_LOG.proc_begin (d_module, 'p_status', p_status);
837 END IF;
838
839 x_new_draft_needed := FND_API.G_FALSE;
840
841 IF (p_status = PO_DRAFTS_PVT.g_status_PDOI_ERROR) THEN
842 d_position := 10;
843
844 x_new_draft_needed := FND_API.G_TRUE;
845
846 ELSIF (p_status = PO_DRAFTS_PVT.g_status_PDOI_PROCESSING) THEN
847 d_position := 20;
848
849 l_cur_request_id := FND_GLOBAL.conc_request_id;
850
851 PO_DRAFTS_PVT.get_request_id
852 ( p_draft_id => p_draft_id,
853 x_request_id => l_dft_request_id
854 );
855
856 x_new_draft_needed := PO_PDOI_UTL.is_old_request_complete
857 ( p_old_request_id => l_dft_request_id
858 );
859
860 END IF; -- if status = p_status = PO_DRAFTS_PVT.g_status_PDOI_ERROR
861
862 IF (PO_LOG.d_proc) THEN
863 PO_LOG.proc_end (d_module, 'x_new_draft_needed', x_new_draft_needed);
864 END IF;
865
866 EXCEPTION
867 WHEN OTHERS THEN
868 PO_MESSAGE_S.add_exc_msg
869 ( p_pkg_name => d_pkg_name,
870 p_procedure_name => d_api_name || '.' || d_position
871 );
872 RAISE;
873 END check_new_draft_needed;
874
875
876
877
878 END PO_PDOI_HEADER_GROUPING_PVT;
[Home] [Help]