1 PACKAGE BODY PO_CATALOG_INDEX_PVT AS
2 /* $Header: PO_CATALOG_INDEX_PVT.plb 120.13.12020000.3 2013/03/29 06:25:08 xueche ship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'PO_CATALOG_INDEX_PVT';
5 g_log_head CONSTANT VARCHAR2(100) := 'po.plsql.' || g_pkg_name || '.';
6
7 -- Read the profile option that enables/disables the debug log
8 g_debug_stmt CONSTANT BOOLEAN := PO_DEBUG.is_debug_stmt_on;
9 g_debug_unexp CONSTANT BOOLEAN := PO_DEBUG.is_debug_unexp_on;
10
11 g_application_err_num CONSTANT NUMBER := -20000;
12
13 -- Forward function declarations: Start
14
15 PROCEDURE populate_sessiongt_for_pa
16 (
17 p_po_header_ids IN PO_TBL_NUMBER
18 );
19
20 PROCEDURE populate_sessiongt_for_quote
21 (
22 p_po_header_id IN NUMBER
23 );
24
25 PROCEDURE populate_sessiongt_for_rt
26 (
27 p_reqexpress_name IN VARCHAR2
28 , p_org_id IN NUMBER
29 );
30
31 PROCEDURE insert_header_changes
32 (
33 p_key IN NUMBER
34 , p_key_remaining_headers IN NUMBER
35 );
36
37 PROCEDURE insert_line_changes
38 (
39 p_key IN NUMBER
40 , p_key_remaining_headers IN NUMBER
41 );
42
43 PROCEDURE insert_attr_changes
44 (
45 p_key IN NUMBER
46 , p_key_remaining_headers IN NUMBER
47 );
48
49 PROCEDURE insert_tlp_changes
50 (
51 p_key IN NUMBER
52 , p_key_remaining_headers IN NUMBER
53 );
54
55 PROCEDURE delete_processed_headers
56 (
57 p_key IN NUMBER
58 , p_key_remaining_headers IN NUMBER
59 );
60
61 PROCEDURE populate_sessiongt_for_orgs
62 (
63 p_po_header_ids IN PO_TBL_NUMBER
64 );
65
66 -- <Bug 7655719>
67 -- Moved code for updating of item description and category to
68 -- PO_LINES_SV11.update_line
69 /*PROCEDURE synch_item_description
70 (
71 p_type IN VARCHAR2
72 , p_po_header_id IN NUMBER DEFAULT NULL
73 , p_po_header_ids IN PO_TBL_NUMBER DEFAULT NULL
74 , p_reqexpress_name IN VARCHAR2 DEFAULT NULL
75 , p_org_id IN NUMBER DEFAULT NULL
76 );
77
78 -- Bug6979842: Added new procedure synch_item_category.
79 PROCEDURE synch_item_category
80 (
81 p_type IN VARCHAR2
82 , p_po_header_id IN NUMBER DEFAULT NULL
83 , p_po_header_ids IN PO_TBL_NUMBER DEFAULT NULL
84 , p_reqexpress_name IN VARCHAR2 DEFAULT NULL
85 , p_org_id IN NUMBER DEFAULT NULL
86 );*/
87 -- Forward function declarations: End
88
89 --------------------------------------------------------------------------------
90 --Start of Comments
91 --Name: rebuild_index
92 --Pre-reqs:
93 -- None
94 --Modifies:
95 -- a) PO_SESSION_GT Table (inserts data to be passed onto iProc API)
96 --Locks:
97 -- None.
98 --Function:
99 -- To populate or rebuild the intermedia index required for iProcurement
100 -- Catalog Search. The search will support following document changes:
101 --
102 -- 1) Global Blankets
103 -- 2) Quotations
104 -- 3) ReqTemplates
105 --
106 -- This API populates the PO_SESSION_GT table with the data required in the
107 -- format specified by iProc. In the end, it calls the iProc API to
108 -- populate/rebuild the index. All exceptions in this API will be silently
109 -- logged in the debug logs. The errors/exceptions in th rebuild_index API
110 -- are not thrown up to the calling program, so as not to interrupt the
111 -- normal flow.
112 --
113 --Parameters:
114 --IN:
115 --p_type:
116 -- Specifies what kind of document is being passed in for rebuilding the
117 -- index. It can take the following values:
118 --
119 -- 1) 'BLANKET'
120 -- 2) 'BLANKET_BULK'
121 -- 3) 'QUOTATION'
122 -- 4) 'REQ_TEMPLATE'
123 --
124 --p_po_header_id
125 --p_reqexpress_name
126 -- All ID parameters of this API has default NULL values. Depending on the
127 -- type specified, only the respective ID has to be specified. For example,
128 -- when a BLANKET type is specified, only the p_po_header_id parameter has
129 -- to have a value specified. Other parameters such as p_reqexpress_name will be
130 -- ignored.
131 --p_org_id
132 -- Org ID to which the ReqTemplate belongs. This is required only if the
133 -- p_type parameter is REQ_TEMPLATE.
134 --p_po_header_ids
135 -- Required when p_type parameter is BLANKET_BULK. This is intended for
136 -- PDOI flow where documents are processed in bulk.
137 --OUT:
138 -- None
139 --End of Comments
140 --------------------------------------------------------------------------------
141 PROCEDURE rebuild_index
142 (
143 p_type IN VARCHAR2
144 , p_po_header_id IN NUMBER DEFAULT NULL
145 , p_po_header_ids IN PO_TBL_NUMBER DEFAULT NULL
146 , p_reqexpress_name IN VARCHAR2 DEFAULT NULL
147 , p_org_id IN NUMBER DEFAULT NULL
148 )
149 IS
150 l_api_name CONSTANT VARCHAR2(30) := 'rebuild_index';
151 l_log_head CONSTANT VARCHAR2(100) := g_log_head || l_api_name;
152 l_progress VARCHAR2(3) := '000';
153
154 l_po_header_ids PO_TBL_NUMBER := PO_TBL_NUMBER(1);
155 BEGIN
156
157 -- Log the input parameters into debug logs
158 IF g_debug_stmt THEN
159 PO_DEBUG.debug_begin(l_log_head);
160 PO_DEBUG.debug_var(l_log_head,l_progress,'p_type',p_type);
161 PO_DEBUG.debug_var(l_log_head,l_progress,'p_po_header_id',p_po_header_id);
162 PO_DEBUG.debug_var(l_log_head,l_progress,'p_reqexpress_name',p_reqexpress_name);
163 PO_DEBUG.debug_var(l_log_head,l_progress,'p_org_id',p_org_id);
164
165 IF ( (p_po_header_ids IS NOT NULL) AND
166 (p_po_header_ids.COUNT > 0) ) THEN
167 PO_DEBUG.debug_var(l_log_head,l_progress,'p_po_header_ids.COUNT',p_po_header_ids.COUNT);
168 PO_DEBUG.debug_var(l_log_head,l_progress,'p_po_header_ids(1)',p_po_header_ids(1));
169 ELSE
170 PO_DEBUG.debug_stmt (l_log_head,l_progress,'p_po_header_ids LIST is NULL');
171 END IF;
172 END IF;
173
174 l_progress := '010';
175 -- Main switchboard. Call the respective procedures, based on p_type.
176 IF (p_type = TYPE_BLANKET) THEN
177
178 -- <Bug 7655719>
179 -- Moved code for updating of item description and category to
180 -- PO_LINES_SV11.update_line
181 /*synch_item_description
182 (
183 p_type => p_type
184 , p_po_header_id => p_po_header_id
185 );
186
187 -- Bug6979842: Synch item category also.
188 synch_item_category
189 (
190 p_type => p_type
191 , p_po_header_id => p_po_header_id
192 );*/
193
194 l_po_header_ids(1) := p_po_header_id;
195 populate_sessiongt_for_pa(p_po_header_ids => l_po_header_ids);
196
197 ELSIF (p_type = TYPE_BLANKET_BULK) THEN
198 /*synch_item_description
199 (
200 p_type => p_type
201 , p_po_header_ids => p_po_header_ids
202 );
203
204 -- Bug6979842: Synch item category also.
205 synch_item_category
206 (
207 p_type => p_type
208 , p_po_header_ids => p_po_header_ids
209 );*/
210
211 populate_sessiongt_for_pa(p_po_header_ids => p_po_header_ids);
212
213 ELSIF (p_type = TYPE_QUOTATION) THEN
214 /*synch_item_description
215 (
216 p_type => p_type
217 , p_po_header_id => p_po_header_id
218 );
219
220 -- Bug6979842: Synch item category also.
221 synch_item_category
222 (
223 p_type => p_type
224 , p_po_header_id => p_po_header_id
225 );*/
226
227 populate_sessiongt_for_quote(p_po_header_id => p_po_header_id);
228
229 ELSIF (p_type = TYPE_REQ_TEMPLATE) THEN
230 /*synch_item_description
231 (
232 p_type => p_type
233 , p_reqexpress_name => p_reqexpress_name
234 , p_org_id => p_org_id
235 );
236
237 -- Bug6979842: Synch item category also.
238 synch_item_category
239 (
240 p_type => p_type
241 , p_reqexpress_name => p_reqexpress_name
242 , p_org_id => p_org_id
243 );*/
244
245 populate_sessiongt_for_rt( p_reqexpress_name => p_reqexpress_name
246 , p_org_id => p_org_id);
247 ELSE
248 -- Invalid type
249 IF g_debug_stmt THEN PO_DEBUG.debug_stmt(l_log_head,l_progress,'Invalid type:'||p_type); END IF;
250 END IF;
251
252 l_progress := '020';
253 IF g_debug_stmt THEN PO_DEBUG.debug_end(l_log_head); END IF;
254
255 EXCEPTION
256 WHEN OTHERS THEN
257 IF g_debug_unexp THEN PO_DEBUG.debug_exc(l_log_head,l_progress); END IF;
258 -- do not raise the exception as rebuild_index errors have to be ignored
259 -- by the calling program.
260 END rebuild_index;
261
262
263 --------------------------------------------------------------------------------
264 -- Internal procedures
265 --------------------------------------------------------------------------------
266
267
268 --------------------------------------------------------------------------------
269 --Start of Comments
270 --Name: populate_sessiongt_for_quote
271 --Pre-reqs:
272 -- None
273 --Modifies:
274 -- a) PO_SESSION_GT Table (inserts data to be passed onto iProc API)
275 --Locks:
276 -- None.
277 --Function:
278 -- To populate or rebuild the intermedia index required for iProcurement
279 -- Catalog Search for Quotations. It populates the GT table with all the
280 -- lines in the given Quotation.
281 --
282 -- This API populates the PO_SESSION_GT table with the data required in the
283 -- format specified by iProc. In the end, it calls the iProc API to
284 -- populate/rebuild the index. All exceptions in this API will be silently
285 -- logged in the debug logs. The errors/exceptions in th rebuild_index API
286 -- are not thrown up to the calling program, so as not to interrupt the
287 -- normal flow.
288 --
289 --Parameters:
290 --IN:
291 --p_po_header_id
292 -- The PO_HEADER_ID for the Quotation header required to be made searchable
293 -- in the catalog.
294 --OUT:
295 -- None
296 --End of Comments
297 --------------------------------------------------------------------------------
298 PROCEDURE populate_sessiongt_for_quote
299 (
300 p_po_header_id IN NUMBER
301 )
302 IS
303 l_api_name CONSTANT VARCHAR2(30) := 'populate_sessiongt_for_quote';
304 l_log_head CONSTANT VARCHAR2(100) := g_log_head || l_api_name;
305 l_progress VARCHAR2(3) := '000';
306
307 l_key PO_SESSION_GT.key%TYPE;
308 l_return_status VARCHAR2(1);
309 l_num_rows_is_gt NUMBER := 0;
310
311 l_segment1 PO_HEADERS_ALL.segment1%TYPE;
312 BEGIN
313 IF g_debug_stmt THEN
314 PO_DEBUG.debug_begin(l_log_head);
315 PO_DEBUG.debug_var(l_log_head,l_progress,'p_po_header_id',p_po_header_id);
316 END IF;
317
318 l_progress := '010';
319 -- pick a new key for temp table
320 SELECT PO_SESSION_GT_S.nextval
321 INTO l_key
322 FROM DUAL;
323
324 l_progress := '020';
325 -- Insert all lines in the given Quotation into the GT table
326 INSERT INTO PO_SESSION_GT
327 (
328 key
329 , index_num1 -- PO_LINE_ID (for Quotation Line): Required by iProc
330 , index_num2 -- PO_HEADER_ID (for Quotation Header): Internal to PO
331 , char5 -- DATA INFO: Internal to PO
332 )
333 SELECT l_key
334 , po_line_id -- PO_LINE_ID (for Quotation Line): Required by iProc
335 , po_header_id -- PO_HEADER_ID (for Quotation Header): Internal to PO
336 , 'QUOTATION' -- DATA INFO: Internal to PO
337 FROM PO_LINES_ALL
338 WHERE po_header_id = p_po_header_id
339 and last_update_date between sysdate-1 and sysdate+1;
340
341 l_num_rows_is_gt := SQL%rowcount;
342
343 l_progress := '030';
344 IF g_debug_stmt THEN PO_DEBUG.debug_stmt(l_log_head,l_progress,'Number or recs inserted into GT table='||SQL%rowcount); END IF;
345
346 IF (l_num_rows_is_gt > 0) THEN
347 IF g_debug_stmt THEN
348 PO_LOG.stmt_session_gt
349 (
350 p_module_base => l_log_head -- IN VARCHAR2
351 , p_position => l_progress -- IN NUMBER
352 , p_key => l_key -- IN NUMBER
353 , p_column_name_tbl => NULL -- IN PO_TBL_VARCHAR30 DEFAULT NULL (For all columns)
354 );
355
356 SELECT segment1
357 INTO l_segment1
358 FROM PO_HEADERS_ALL
359 WHERE po_header_id = p_po_header_id;
360
361 PO_DEBUG.debug_stmt(l_log_head,l_progress,'SEGMENT1 = '||l_segment1||', for PO_HEADER_ID = '||p_po_header_id);
362 END IF;
363
364 IF g_debug_stmt THEN PO_DEBUG.debug_stmt(l_log_head,l_progress,'Calling iProc API ICX_CAT_POPULATE_QUOTE_GRP.populateOnlineQuotes() to rebuild index'); END IF;
365 -- Call iproc api for rebuild index for Quotations
366 -- Pass in the key for PO_SESSION_GT table
367 l_progress := '040';
368
369 ICX_CAT_POPULATE_QUOTE_GRP.populateOnlineQuotes
370 (
371 p_api_version => 1.0, -- NUMBER IN
372 p_commit => FND_API.G_TRUE, -- VARCHAR2 IN
373 p_init_msg_list => FND_API.G_FALSE, -- VARCHAR2 IN
374 p_validation_level => FND_API.G_VALID_LEVEL_FULL, -- VARCHAR2 IN
375 x_return_status => l_return_status, -- VARCHAR2 OUT
376 p_key => l_key -- NUMBER IN
377 );
378
379 l_progress := '050';
380 -- In case of error, just log in debug logs. There is no need to raise
381 -- it up, because rebuild_index errors have to be ignored by the calling
382 -- program.
383 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
384 IF g_debug_stmt THEN PO_DEBUG.debug_stmt(l_log_head,l_progress,'iProc API ICX_CAT_POPULATE_QUOTE_GRP.populateOnlineQuotes() returned error: '||l_return_status); END IF;
385 END IF;
386 ELSE
387 IF g_debug_stmt THEN PO_DEBUG.debug_stmt(l_log_head,l_progress,'Skipped: iProc API ICX_CAT_POPULATE_QUOTE_GRP.populateOnlineQuotes()'); END IF;
388 END IF;
389
390 IF g_debug_stmt THEN PO_DEBUG.debug_end(l_log_head); END IF;
391 EXCEPTION
392 WHEN OTHERS THEN
393 IF g_debug_unexp THEN PO_DEBUG.debug_exc(l_log_head,l_progress); END IF;
394 END populate_sessiongt_for_quote;
395
396 --------------------------------------------------------------------------------
397 --Start of Comments
398 --Name: populate_sessiongt_for_rt
399 --Pre-reqs:
400 -- None
401 --Modifies:
402 -- a) PO_SESSION_GT Table (inserts data to be passed onto iProc API)
403 --Locks:
404 -- None.
405 --Function:
406 -- To populate or rebuild the intermedia index required for iProcurement
407 -- Catalog Search for ReqTempaltes. It populates the GT table with all the
408 -- lines in the given ReqTemplate.
409 --
410 -- This API populates the PO_SESSION_GT table with the data required in the
411 -- format specified by iProc. In the end, it calls the iProc API to
412 -- populate/rebuild the index. All exceptions in this API will be silently
413 -- logged in the debug logs. The errors/exceptions in th rebuild_index API
414 -- are not thrown up to the calling program, so as not to interrupt the
415 -- normal flow.
416 --
417 --Parameters:
418 --IN:
419 --p_reqexpress_name
420 -- The ReqTemplate Name for the ReqTemplate that is required to be made
421 -- searchable in the catalog.
422 --p_org_id
423 -- The Org ID to which the ReqTemplate belongs.
424 --OUT:
425 -- None
426 --End of Comments
427 --------------------------------------------------------------------------------
428 PROCEDURE populate_sessiongt_for_rt
429 (
430 p_reqexpress_name IN VARCHAR2
431 , p_org_id IN NUMBER
432 )
433 IS
434 l_api_name CONSTANT VARCHAR2(30) := 'populate_sessiongt_for_rt';
435 l_log_head CONSTANT VARCHAR2(100) := g_log_head || l_api_name;
436 l_progress VARCHAR2(3) := '000';
437
438 l_key PO_SESSION_GT.key%TYPE;
439 l_return_status VARCHAR2(1);
440 l_num_rows_is_gt NUMBER := 0;
441 BEGIN
442 IF g_debug_stmt THEN PO_DEBUG.debug_begin(l_log_head); END IF;
443
444 l_progress := '010';
445 -- pick a new key for temp table
446 SELECT PO_SESSION_GT_S.nextval
447 INTO l_key
448 FROM DUAL;
449
450 l_progress := '020';
451 -- Insert all lines in the given ReqTemplate into the GT table
452 INSERT INTO PO_SESSION_GT
453 (
454 key
455 , index_char1 -- ReqTemplate Name
456 , index_num1 -- Reqtemplate Line Num
457 , index_num2 -- Org Id
458 , char5 -- DATA INFO: Internal to PO
459 )
460 SELECT l_key
461 , p_reqexpress_name -- ReqTemplate Name
462 , sequence_num -- Reqtemplate Line Num
463 , org_id -- Org Id
464 , 'REQ_TEMPLATE' -- DATA INFO: Internal to PO
465 FROM PO_REQEXPRESS_LINES_ALL
466 WHERE express_name = p_reqexpress_name
467 AND org_id = p_org_id
468 and last_update_date between sysdate-1 and sysdate+1;
469
470 l_num_rows_is_gt := SQL%rowcount;
471
472 l_progress := '030';
473 IF g_debug_stmt THEN PO_DEBUG.debug_stmt(l_log_head,l_progress,'Number or recs inserted into GT table='||SQL%rowcount); END IF;
474
475 IF (l_num_rows_is_gt > 0) THEN
476 IF g_debug_stmt THEN
477 PO_LOG.stmt_session_gt
478 (
479 p_module_base => l_log_head -- IN VARCHAR2
480 , p_position => l_progress -- IN NUMBER
481 , p_key => l_key -- IN NUMBER
482 , p_column_name_tbl => NULL -- IN PO_TBL_VARCHAR30 DEFAULT NULL (For all columns)
483 );
484 END IF;
485
486 IF g_debug_stmt THEN PO_DEBUG.debug_stmt(l_log_head,l_progress,'Calling iProc API ICX_CAT_POPULATE_REQTMPL_GRP.populateOnlineReqTemplates() to rebuild index'); END IF;
487 -- Call iproc api for rebuild index for ReqTemplates
488 -- Pass in the key for PO_SESSION_GT table
489 l_progress := '040';
490
491 ICX_CAT_POPULATE_REQTMPL_GRP.populateOnlineReqTemplates
492 (
493 p_api_version => 1.0, -- NUMBER IN
494 p_commit => FND_API.G_TRUE, -- VARCHAR2 IN
495 p_init_msg_list => FND_API.G_FALSE, -- VARCHAR2 IN
496 p_validation_level => FND_API.G_VALID_LEVEL_FULL, -- VARCHAR2 IN
497 x_return_status => l_return_status, -- VARCHAR2 OUT
498 p_key => l_key -- NUMBER IN
499 );
500
501 l_progress := '050';
502 -- In case of error, just log in debug logs. There is no need to raise
503 -- it up, because rebuild_index errors have to be ignored by the calling
504 -- program.
505 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
506 IF g_debug_stmt THEN PO_DEBUG.debug_stmt(l_log_head,l_progress,'iProc API ICX_CAT_POPULATE_REQTMPL_GRP.populateOnlineReqTemplates() returned error: '||l_return_status); END IF;
507 END IF;
508 ELSE
509 IF g_debug_stmt THEN PO_DEBUG.debug_stmt(l_log_head,l_progress,'Skipped: iProc API ICX_CAT_POPULATE_REQTMPL_GRP.populateOnlineReqTemplates()'); END IF;
510 END IF;
511
512 IF g_debug_stmt THEN PO_DEBUG.debug_end(l_log_head); END IF;
513 EXCEPTION
514 WHEN OTHERS THEN
515 IF g_debug_unexp THEN PO_DEBUG.debug_exc(l_log_head,l_progress); END IF;
516 END populate_sessiongt_for_rt;
517
518 --------------------------------------------------------------------------------
519 --Start of Comments
520 --Name: populate_sessiongt_for_pa
521 --Pre-reqs:
522 -- None
523 --Modifies:
524 -- a) PO_SESSION_GT Table (inserts data to be passed onto iProc API)
525 --Locks:
526 -- None.
527 --Function:
528 -- To populate or rebuild the intermedia index required for iProcurement
529 -- Catalog Search for Blankets / Global Blankets. It populates the GT table
533 -- Header Level: SUPPLIER, SUPPLIER_SITE
530 -- with all those lines in the given document, that have one of the following
531 -- searchable fields modified:
532 --
534 -- Line Level : IP_CATEGORY_ID, PO_CATEGORY_ID, SUPP_REF_NUM,
535 -- SUPPLIER_PART_AUX_ID, ITEM_ID, ITEM_REVISION
536 -- Attr Level : ANY FIELD
537 -- TLP Level : ANY FIELD
538 --
539 -- This API populates the PO_SESSION_GT table with the data required in the
540 -- format specified by iProc. In the end, it calls the iProc API to
541 -- populate/rebuild the index. All exceptions in this API will be silently
542 -- logged in the debug logs. The errors/exceptions in th rebuild_index API
543 -- are not thrown up to the calling program, so as not to interrupt the
544 -- normal flow.
545 --
546 --Parameters:
547 --IN:
548 --p_po_header_ids
549 -- The list of PO_HEADER_ID's for the Global Blankets that are required to be
550 -- made searchable in the Catalog.
551 --OUT:
552 -- None
553 --End of Comments
554 --------------------------------------------------------------------------------
555 PROCEDURE populate_sessiongt_for_pa
556 (
557 p_po_header_ids IN PO_TBL_NUMBER
558 )
559 IS
560 l_api_name CONSTANT VARCHAR2(30) := 'populate_sessiongt_for_pa';
561 l_log_head CONSTANT VARCHAR2(100) := g_log_head || l_api_name;
562 l_progress VARCHAR2(3) := '000';
563
564 l_key PO_SESSION_GT.key%TYPE;
565 l_key_remaining_headers PO_SESSION_GT.key%TYPE;
566 l_key_org_assignments PO_SESSION_GT.key%TYPE;
567 l_return_status VARCHAR2(1);
568 l_num_rows_is_gt NUMBER := 0;
569
570 BEGIN
571 IF g_debug_stmt THEN PO_DEBUG.debug_begin(l_log_head); END IF;
572
573 l_progress := '010';
574 -- pick a new key for temp table, to store records to be passed to iProc
575 SELECT PO_SESSION_GT_S.nextval
576 INTO l_key
577 FROM DUAL;
578
579 -- Pick another key for temp table, used for remaining headers to be processed
580 -- This will be the key to those PO_HEADER_ID's in PO_SESSSION_GT table that
581 -- have not been completely checked to see if they contain any changes to the
582 -- searchable attributes at Heade/Line/Attr/TLP levels.
583 SELECT PO_SESSION_GT_S.nextval
584 INTO l_key_remaining_headers
585 FROM DUAL;
586
587 -- Insert ALL the doc_id's that came in in the input parameter list into the
588 -- 'remaining_headers' list
589 FORALL i in 1..p_po_header_ids.COUNT
590 INSERT INTO PO_SESSION_GT
591 (
592 key -- Key: Internal to PO
593 , index_num1 -- List of Input PO_HEADER_ID's
594 , char5 -- DATA INFO: Internal to PO
595 )
596 VALUES
597 (
598 l_key_remaining_headers -- Key: Internal to PO
599 , p_po_header_ids(i) -- List of Input PO_HEADER_ID's
600 , 'Remaining PO_HEADER_IDs' -- DATA INFO: Internal to PO
601 );
602
603 IF g_debug_stmt THEN PO_DEBUG.debug_stmt(l_log_head,l_progress,'Number or PO_HEADER_IDs inserted into GT table='||SQL%rowcount); END IF;
604
605 -- Insert lines for headers that have been modified
606 -- The fields that need rebuild index are:
607 -- SUPPLIER, SUPPLIER_SITE
608 insert_header_changes
609 (
610 p_key => l_key
611 , p_key_remaining_headers => l_key_remaining_headers
612 );
613
614 -- From now on consider only those headers that do not have any changes on it
615 -- we need to figure out if there are any changes at line level for these headers
616 -- Get the headers that did not have any changes i.e. p_po_header_ids - <docIds inserted in _gt>
617
618 -- From the input list, delete those that were already marked as having
619 -- header level changes
620 delete_processed_headers
621 (
622 p_key => l_key
623 , p_key_remaining_headers => l_key_remaining_headers
624 );
625
626 -- Now we have only those documents that did not have header changes
627 -- For these documents check if there were any line changes
628 -- Field changes that need rebuild index:
629 -- IP_CATEGORY_ID, PO_CATEGORY_ID, AUXID, PART_NUM, ITEM_ID, ITEM_REVISION
630 insert_line_changes
631 (
632 p_key => l_key
633 , p_key_remaining_headers => l_key_remaining_headers
634 );
635
636 -- From the remaining headers list, delete those that have been marked as
637 -- having line level changes
638 delete_processed_headers
639 (
640 p_key => l_key
641 , p_key_remaining_headers => l_key_remaining_headers
642 );
643
644 -- Now we have the documents for which there were no Header or Line
645 -- changes, but there could potentialy be some Attribute, TLP, Org assignment
646 -- changes
647 -- Field changes that need rebuild index:
648 -- ANY field at Attr Level
649 insert_attr_changes
650 (
651 p_key => l_key
652 , p_key_remaining_headers => l_key_remaining_headers
653 );
654
655 -- From the remaining headers list, delete those that have been marked as
656 -- having Attr level changes
657 delete_processed_headers
658 (
659 p_key => l_key
660 , p_key_remaining_headers => l_key_remaining_headers
661 );
662
663 -- Now we have the documents for which there were no Header/Line/Attr
664 -- changes, but there could potentialy be some TLP changes
665 -- Field changes that need rebuild index:
666 -- ANY field at TLP Level
667 insert_tlp_changes
668 (
669 p_key => l_key
670 , p_key_remaining_headers => l_key_remaining_headers
671 );
672
673 -- SQL What: Get the number of rows inserted in GT table
674 -- SQL Why : To check if we need to call iProc API
675 -- SQL Join: key
679 WHERE key = l_key;
676 SELECT count(*)
677 INTO l_num_rows_is_gt
678 FROM PO_SESSION_GT
680
681 IF g_debug_stmt THEN PO_DEBUG.debug_stmt(l_log_head,l_progress,'Number or recs inserted into GT table='||l_num_rows_is_gt); END IF;
682
683 IF (l_num_rows_is_gt > 0) THEN
684 IF g_debug_stmt THEN PO_DEBUG.debug_stmt(l_log_head,l_progress,'Calling iProc API ICX_CAT_POPULATE_BPA_GRP.populateOnlineBlankets() to rebuild index'); END IF;
685 -- Call iproc api for rebuild index for ReqTemplates
686 -- Pass in the key for PO_SESSION_GT table
687 l_progress := '040';
688
689 IF g_debug_stmt THEN
690 PO_LOG.stmt_session_gt
691 (
692 p_module_base => l_log_head -- IN VARCHAR2
693 , p_position => l_progress -- IN NUMBER
694 , p_key => l_key -- IN NUMBER
695 , p_column_name_tbl => NULL -- IN PO_TBL_VARCHAR30 DEFAULT NULL (For all columns)
696 );
697 END IF;
698 /* Bug 14046925 : Changing p_commit parameter to false, to avoid commit during doApprove call */
699 ICX_CAT_POPULATE_BPA_GRP.populateOnlineBlankets
700 (
701 p_api_version => 1.0, -- NUMBER IN
702 p_commit => FND_API.G_FALSE, -- VARCHAR2 IN
703 p_init_msg_list => FND_API.G_FALSE, -- VARCHAR2 IN
704 p_validation_level => FND_API.G_VALID_LEVEL_FULL, -- VARCHAR2 IN
705 x_return_status => l_return_status, -- VARCHAR2 OUT
706 p_key => l_key -- NUMBER IN
707 );
708
709 l_progress := '050';
710 -- In case of error, just log in debug logs. There is no need to raise
711 -- it up, because rebuild_index errors have to be ignored by the calling
712 -- program.
713 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
714 IF g_debug_stmt THEN PO_DEBUG.debug_stmt(l_log_head,l_progress,'iProc API ICX_CAT_POPULATE_BPA_GRP.populateOnlineBlankets() returned error: '||l_return_status); END IF;
715 END IF;
716 ELSE
717 IF g_debug_stmt THEN PO_DEBUG.debug_stmt(l_log_head,l_progress,'Skipped: iProc API ICX_CAT_POPULATE_BPA_GRP.populateOnlineBlankets()'); END IF;
718 END IF;
719
720 -- Finally, see if Org Assignments have changed, and call a separate
721 -- iProc API, to pass in Org Assignment information
722 populate_sessiongt_for_orgs
723 (
724 p_po_header_ids => p_po_header_ids
725 );
726
727 IF g_debug_stmt THEN PO_DEBUG.debug_end(l_log_head); END IF;
728 EXCEPTION
729 WHEN OTHERS THEN
730 IF g_debug_unexp THEN PO_DEBUG.debug_exc(l_log_head,l_progress); END IF;
731 END populate_sessiongt_for_pa;
732
733 --------------------------------------------------------------------------------
734 --Start of Comments
735 --Name: insert_header_changes
736 --Pre-reqs:
737 -- None
738 --Modifies:
739 -- a) PO_SESSION_GT Table (inserts data to be passed onto iProc API)
740 --Locks:
741 -- None.
742 --Function:
743 -- This API tracks the changes to the following searchable fields at the Header
744 -- Level of a GBPA.
745 --
746 -- SUPPLIER, SUPPLIER_SITE
747 --
748 -- It tracks the changes by comparing the data in the Header archive table.
749 -- It populates the PO_SESSION_GT table with the data required in the
750 -- format specified by iProc.
751 --
752 --Parameters:
753 --IN:
754 --p_key
755 -- The key to those records in PO_SESSSION_GT table that will be passed onto
756 -- the iProc API.
757 --p_key_remaining_headers
758 -- The key to those PO_HEADER_ID's in PO_SESSSION_GT table that have not been
759 -- completely checked to see if they contain any changes to the searchable
760 -- attributes at Header/Line/Attr/TLP levels.
761 --OUT:
762 -- None
763 --End of Comments
764 --------------------------------------------------------------------------------
765 PROCEDURE insert_header_changes
766 (
767 p_key IN NUMBER
768 , p_key_remaining_headers IN NUMBER
769 )
770 IS
771 l_api_name CONSTANT VARCHAR2(30) := 'insert_header_changes';
772 l_log_head CONSTANT VARCHAR2(100) := g_log_head || l_api_name;
773 l_progress VARCHAR2(3) := '000';
774
775 BEGIN
776 IF g_debug_stmt THEN PO_DEBUG.debug_begin(l_log_head); END IF;
777
778 l_progress := '010';
779
780 -- Insert lines for headers that have been modified
781 -- The fields that need rebuild index are:
782 -- supplier, supplier_site
783 INSERT INTO PO_SESSION_GT
784 (
785 key
786 , index_num1 -- PO_LINE_ID
787 , index_char1 -- Line Changed Flag
788 , index_char2 -- Attr Changed Flag
789 , char1 -- TLP Changed Flag
790 , char2 -- Language
791 , char3 -- Global Agreement Flag
792 , index_num2 -- PO_HEADER_ID (Internal to PO Dev, not required by iProc)
793 , char5 -- DATA INFO: Internal to PO
794 )
795 SELECT
796 p_key
797 , POL.po_line_id -- PO_LINE_ID
798 , 'Y' -- Line Changed Flag
799 , NULL -- Attr Changed Flag: n/a if line_changed_flag is Y
800 , NULL -- TLP Changed Flag: n/a if line_changed_flag is Y
801 , NULL -- Language: n/a if line_changed_flag is Y
802 , POH.global_agreement_flag -- Global Agreement Flag
803 , POH.po_header_id -- PO_HEADER_ID (Internal to PO Dev, not required by iProc)
804 , 'BLANKET:HEADER' -- DATA INFO: Internal to PO
805 FROM PO_LINES_ALL POL
806 , PO_HEADERS_ALL POH
807 , PO_SESSION_GT GT_REMAINING_HDRS
808 WHERE GT_REMAINING_HDRS.key = p_key_remaining_headers
809 AND POH.po_header_id = GT_REMAINING_HDRS.index_num1 -- index_num1 stores the PO_HEADER_ID
810 AND POL.po_header_id = POH.po_header_id
814 WHERE POHA.po_header_id = POH.po_header_id)
811 AND ( NOT EXISTS
812 (SELECT 'Headers were archived'
813 FROM PO_HEADERS_ARCHIVE_ALL POHA
815 OR
816 EXISTS
817 (SELECT 'Some attribute is modified'
818 FROM PO_HEADERS_ARCHIVE_ALL POHA
819 WHERE POHA.po_header_id = POH.po_header_id
820 AND POHA.latest_external_flag = 'Y'
821 AND (POH.vendor_id <> POHA.vendor_id OR
822 (POH.vendor_id IS NULL AND POHA.vendor_id IS NOT NULL) OR
823 (POH.vendor_id IS NOT NULL AND POHA.vendor_id IS NULL) OR
824 POH.vendor_site_id <> POHA.vendor_site_id OR
825 (POH.vendor_site_id IS NULL AND POHA.vendor_site_id IS NOT NULL) OR
826 (POH.vendor_site_id IS NOT NULL AND POHA.vendor_site_id IS NULL) OR
827 --Bug 16196550: insert records when supplier contact is modified
828 POH.vendor_contact_id <> POHA.vendor_contact_id OR
829 (POH.vendor_contact_id IS NULL AND POHA. vendor_contact_id IS NOT NULL) OR
830 (POH.vendor_contact_id IS NOT NULL AND POHA.vendor_contact_id IS NULL))));
831
832 IF g_debug_stmt THEN PO_DEBUG.debug_stmt(l_log_head,l_progress,'Number or recs inserted into GT table='||SQL%rowcount); END IF;
833
834 IF g_debug_stmt THEN PO_DEBUG.debug_end(l_log_head); END IF;
835 EXCEPTION
836 WHEN OTHERS THEN
837 IF g_debug_unexp THEN PO_DEBUG.debug_exc(l_log_head,l_progress); END IF;
838 END insert_header_changes;
839
840 --------------------------------------------------------------------------------
841 --Start of Comments
842 --Name: insert_line_changes
843 --Pre-reqs:
844 -- None
845 --Modifies:
846 -- a) PO_SESSION_GT Table (inserts data to be passed onto iProc API)
847 --Locks:
848 -- None.
849 --Function:
850 -- This API tracks the changes to the following searchable fields at the Line
851 -- Level of a GBPA.
852 --
853 -- IP_CATEGORY_ID, PO_CATEGORY_ID, SUPP_REF_NUM,
854 -- SUPPLIER_PART_AUX_ID, ITEM_ID, ITEM_REVISION
855 --
856 -- It tracks the changes by comparing the data in the Line archive table.
857 -- It populates the PO_SESSION_GT table with the data required in the
858 -- format specified by iProc.
859 --
860 --Parameters:
861 --IN:
862 --p_key
863 -- The key to those records in PO_SESSSION_GT table that will be passed onto
864 -- the iProc API.
865 --p_key_remaining_headers
866 -- The key to those PO_HEADER_ID's in PO_SESSSION_GT table that have not been
867 -- completely checked to see if they contain any changes to the searchable
868 -- attributes at Header/Line/Attr/TLP levels.
869 --OUT:
870 -- None
871 --End of Comments
872 --------------------------------------------------------------------------------
873 PROCEDURE insert_line_changes
874 (
875 p_key IN NUMBER
876 , p_key_remaining_headers IN NUMBER
877 )
878 IS
879 l_api_name CONSTANT VARCHAR2(30) := 'insert_line_changes';
880 l_log_head CONSTANT VARCHAR2(100) := g_log_head || l_api_name;
881 l_progress VARCHAR2(3) := '000';
882
883 BEGIN
884 IF g_debug_stmt THEN PO_DEBUG.debug_begin(l_log_head); END IF;
885
886 l_progress := '010';
887 -- Now we have only those documents that did not have header changes
888 -- For these documents check if there were any line changes
889 -- Field changes that need rebuild index:
890 -- ip_category_id, po_category_id, auxid, part_num
891 -- Bug#4902870: Check for vendor_product_num changes
892 INSERT INTO PO_SESSION_GT
893 (
894 key
895 , index_num1 -- PO_LINE_ID
896 , index_char1 -- Line Changed Flag
897 , index_char2 -- Attr Changed Flag
898 , char1 -- TLP Changed Flag
899 , char2 -- Language
900 , char3 -- Global Agreement Flag
901 , index_num2 -- PO_HEADER_ID (Internal to PO Dev, not required by iProc)
902 , char5 -- DATA INFO: Internal to PO
903 )
904 SELECT
905 p_key
906 , POL.po_line_id -- PO_LINE_ID
907 , 'Y' -- Line Changed Flag
908 , NULL -- Attr Changed Flag: n/a if line_changed_flag is Y
909 , NULL -- TLP Changed Flag: n/a if line_changed_flag is Y
910 , NULL -- Language: n/a if line_changed_flag is Y
911 , POH.global_agreement_flag -- Global Agreement Flag
912 , POH.po_header_id -- PO_HEADER_ID (Internal to PO Dev, not required by iProc)
913 , 'BLANKET:LINE' -- DATA INFO: Internal to PO
914 FROM PO_LINES_ALL POL
915 , PO_HEADERS_ALL POH
916 , PO_SESSION_GT GT_REMAINING_HDRS
917 WHERE GT_REMAINING_HDRS.key = p_key_remaining_headers
918 AND POH.po_header_id = GT_REMAINING_HDRS.index_num1 -- index_num1 stores the PO_HEADER_ID
919 AND POL.po_header_id = POH.po_header_id
920 /*Bug12530695 : Revert ECO change. Instead handle it by comparing last
921 update date of po lines with extracted ip record for already extracted
922 records */
923 /* Bug 5559492: As part of this ECO, we are not checking if any specific list
924 of columns have changed. We always call the IP's rebuild_index API.*/
925 AND ( NOT EXISTS
926 (SELECT 'Lines were archived'
927 FROM PO_LINES_ARCHIVE_ALL POLA
928 WHERE POLA.po_line_id = POL.po_line_id)
929 OR
930 EXISTS
931 (
932 SELECT 'last update date of po_lines is greater than ip record'
933 FROM ICX_CAT_ITEMS_CTX_HDRS_TLP hdrs
934 WHERE hdrs.PO_LINE_ID = POL.PO_LINE_ID
935 AND POL.last_update_date > hdrs.last_update_date )
936 --Bug 13343886
937 OR NOT EXISTS
938 (
942
939 SELECT '1' FROM ICX_CAT_ITEMS_CTX_HDRS_TLP hdrs WHERE HDRS.PO_LINE_ID = POL.PO_LINE_ID
940 ) --Bug 13343886
941 );
943 IF g_debug_stmt THEN PO_DEBUG.debug_stmt(l_log_head,l_progress,'Number or recs inserted into GT table='||SQL%rowcount); END IF;
944
945 IF g_debug_stmt THEN PO_DEBUG.debug_end(l_log_head); END IF;
946 EXCEPTION
947 WHEN OTHERS THEN
948 IF g_debug_unexp THEN PO_DEBUG.debug_exc(l_log_head,l_progress); END IF;
949 END insert_line_changes;
950
951 --------------------------------------------------------------------------------
952 --Start of Comments
953 --Name: insert_attr_changes
954 --Pre-reqs:
955 -- None
956 --Modifies:
957 -- a) PO_SESSION_GT Table (inserts data to be passed onto iProc API)
958 --Locks:
959 -- None.
960 --Function:
961 -- This API tracks the changes to any fields at the Attr Level of a GBPA.
962 -- It uses the REBUILD_SEARCH_INDEX_FLAG column in the Attr table to check
963 -- if the record was modified.
964 -- It populates the PO_SESSION_GT table with the data required in the
965 -- format specified by iProc.
966 --
967 --Parameters:
968 --IN:
969 --p_key
970 -- The key to those records in PO_SESSSION_GT table that will be passed onto
971 -- the iProc API.
972 --p_key_remaining_headers
973 -- The key to those PO_HEADER_ID's in PO_SESSSION_GT table that have not been
974 -- completely checked to see if they contain any changes to the searchable
975 -- attributes at Header/Line/Attr/TLP levels.
976 --OUT:
977 -- None
978 --End of Comments
979 --------------------------------------------------------------------------------
980 PROCEDURE insert_attr_changes
981 (
982 p_key IN NUMBER
983 , p_key_remaining_headers IN NUMBER
984 )
985 IS
986 l_api_name CONSTANT VARCHAR2(30) := 'insert_attr_changes';
987 l_log_head CONSTANT VARCHAR2(100) := g_log_head || l_api_name;
988 l_progress VARCHAR2(3) := '000';
989
990 BEGIN
991 IF g_debug_stmt THEN PO_DEBUG.debug_begin(l_log_head); END IF;
992
993 l_progress := '010';
994 -- Now we have the documents for which there were no Header or Line
995 -- changes, but there could potentialy be some Attribute changes
996 INSERT INTO PO_SESSION_GT
997 (
998 key
999 , index_num1 -- PO_LINE_ID
1000 , index_char1 -- Line Changed Flag
1001 , index_char2 -- Attr Changed Flag
1002 , char1 -- TLP Changed Flag
1003 , char2 -- Language
1004 , char3 -- Global Agreement Flag
1005 , index_num2 -- PO_HEADER_ID (Internal to PO Dev, not required by iProc)
1006 , char5 -- DATA INFO: Internal to PO
1007 )
1008 SELECT
1009 p_key
1010 , POL.po_line_id -- PO_LINE_ID
1011 , 'N' -- Line Changed Flag
1012 , 'Y' -- Attr Changed Flag
1013 , NULL -- TLP Changed Flag: n/a if line_changed_flag is Y
1014 , NULL -- Language: n/a if line_changed_flag is Y
1015 , POH.global_agreement_flag -- Global Agreement Flag
1016 , POH.po_header_id -- PO_HEADER_ID (Internal to PO Dev, not required by iProc)
1017 , 'BLANKET:ATTR' -- DATA INFO: Internal to PO
1018 FROM PO_LINES_ALL POL
1019 , PO_HEADERS_ALL POH
1020 , PO_ATTRIBUTE_VALUES POATR
1021 , PO_SESSION_GT GT_REMAINING_HDRS
1022 WHERE GT_REMAINING_HDRS.key = p_key_remaining_headers
1023 AND POH.po_header_id = GT_REMAINING_HDRS.index_num1 -- index_num1 stores the PO_HEADER_ID
1024 AND POL.po_header_id = POH.po_header_id
1025 AND POATR.po_line_id = POL.po_line_id
1026 AND POATR.rebuild_search_index_flag = 'Y'
1027 /*Bug12530695 : Revert ECO change. Instead handle it by comparing last
1028 update date of po lines with extracted ip record for already extracted
1029 records */
1030 AND ( NOT EXISTS
1031 (SELECT 'Lines were archived'
1032 FROM PO_LINES_ARCHIVE_ALL POLA
1033 WHERE POLA.po_line_id = POL.po_line_id)
1034 OR
1035 EXISTS
1036 (
1037 SELECT 'last update date of po_attribute greater than ip record'
1038 FROM ICX_CAT_ITEMS_CTX_HDRS_TLP hdrs
1039 WHERE hdrs.PO_LINE_ID = POL.PO_LINE_ID
1040 AND POATR.last_update_date > hdrs.last_update_date )
1041 --Bug 13343886
1042 OR NOT EXISTS
1043 (
1044 SELECT '1' FROM ICX_CAT_ITEMS_CTX_HDRS_TLP hdrs WHERE HDRS.PO_LINE_ID = POL.PO_LINE_ID
1045 ) --Bug 13343886
1046 );
1047
1048
1049 IF g_debug_stmt THEN PO_DEBUG.debug_stmt(l_log_head,l_progress,'Number or recs inserted into GT table='||SQL%rowcount); END IF;
1050
1051 IF g_debug_stmt THEN PO_DEBUG.debug_end(l_log_head); END IF;
1052 EXCEPTION
1053 WHEN OTHERS THEN
1054 IF g_debug_unexp THEN PO_DEBUG.debug_exc(l_log_head,l_progress); END IF;
1055 END insert_attr_changes;
1056
1057 --------------------------------------------------------------------------------
1058 --Start of Comments
1059 --Name: insert_tlp_changes
1060 --Pre-reqs:
1061 -- None
1062 --Modifies:
1063 -- a) PO_SESSION_GT Table (inserts data to be passed onto iProc API)
1064 --Locks:
1065 -- None.
1066 --Function:
1067 -- This API tracks the changes to any fields at the TLP Level of a GBPA.
1068 -- It uses the REBUILD_SEARCH_INDEX_FLAG column in the TLP table to check
1069 -- if the record was modified.
1070 -- It populates the PO_SESSION_GT table with the data required in the
1071 -- format specified by iProc.
1072 --
1073 --Parameters:
1074 --IN:
1075 --p_key
1076 -- The key to those records in PO_SESSSION_GT table that will be passed onto
1077 -- the iProc API.
1078 --p_key_remaining_headers
1082 --OUT:
1079 -- The key to those PO_HEADER_ID's in PO_SESSSION_GT table that have not been
1080 -- completely checked to see if they contain any changes to the searchable
1081 -- attributes at Header/Line/Attr/TLP levels.
1083 -- None
1084 --End of Comments
1085 --------------------------------------------------------------------------------
1086 PROCEDURE insert_tlp_changes
1087 (
1088 p_key IN NUMBER
1089 , p_key_remaining_headers IN NUMBER
1090 )
1091 IS
1092 l_api_name CONSTANT VARCHAR2(30) := 'insert_tlp_changes';
1093 l_log_head CONSTANT VARCHAR2(100) := g_log_head || l_api_name;
1094 l_progress VARCHAR2(3) := '000';
1095
1096 BEGIN
1097 IF g_debug_stmt THEN PO_DEBUG.debug_begin(l_log_head); END IF;
1098
1099 l_progress := '010';
1100 -- Now we have the documents for which there were no Header/Line/Attr
1101 -- changes, but there could potentialy be some TLP changes
1102 INSERT INTO PO_SESSION_GT
1103 (
1104 key
1105 , index_num1 -- PO_LINE_ID
1106 , index_char1 -- Line Changed Flag
1107 , index_char2 -- Attr Changed Flag
1108 , char1 -- TLP Changed Flag
1109 , char2 -- Language
1110 , char3 -- Global Agreement Flag
1111 , index_num2 -- PO_HEADER_ID (Internal to PO Dev, not required by iProc)
1112 , char5 -- DATA INFO: Internal to PO
1113 )
1114 SELECT
1115 p_key
1116 , POL.po_line_id -- PO_LINE_ID
1117 , 'N' -- Line Changed Flag
1118 , 'N' -- Attr Changed Flag
1119 , 'Y' -- TLP Changed Flag
1120 , POTLP.language -- Language
1121 , POH.global_agreement_flag -- Global Agreement Flag
1122 , POH.po_header_id -- PO_HEADER_ID (Internal to PO Dev, not required by iProc)
1123 , 'BLANKET:TLP' -- DATA INFO: Internal to PO
1124 FROM PO_LINES_ALL POL
1125 , PO_HEADERS_ALL POH
1126 , PO_ATTRIBUTE_VALUES_TLP POTLP
1127 , PO_SESSION_GT GT_REMAINING_HDRS
1128 WHERE GT_REMAINING_HDRS.key = p_key_remaining_headers
1129 AND POH.po_header_id = GT_REMAINING_HDRS.index_num1 -- index_num1 stores the PO_HEADER_ID
1130 AND POL.po_header_id = POH.po_header_id
1131 AND POTLP.po_line_id = POL.po_line_id
1132 AND POTLP.rebuild_search_index_flag = 'Y'
1133 /*Bug12530695 : Revert ECO change. Instead handle it by comparing last
1134 update date of po lines with extracted ip record for already extracted
1135 records */
1136 AND ( NOT EXISTS
1137 (SELECT 'Lines were archived'
1138 FROM PO_LINES_ARCHIVE_ALL POLA
1139 WHERE POLA.po_line_id = POL.po_line_id)
1140 OR
1141 EXISTS
1142 (
1143 SELECT 'last update date of po_attribute tlp greater than ip rec'
1144 FROM ICX_CAT_ITEMS_CTX_HDRS_TLP hdrs
1145 WHERE hdrs.PO_LINE_ID = POL.PO_LINE_ID
1146 AND POTLP.last_update_date > hdrs.last_update_date )
1147 --Bug 13343886
1148 OR NOT EXISTS
1149 (
1150 SELECT '1' FROM ICX_CAT_ITEMS_CTX_HDRS_TLP hdrs WHERE HDRS.PO_LINE_ID = POL.PO_LINE_ID
1151 ) --Bug 13343886
1152 );
1153
1154 IF g_debug_stmt THEN PO_DEBUG.debug_stmt(l_log_head,l_progress,'Number or recs inserted into GT table='||SQL%rowcount); END IF;
1155
1156 IF g_debug_stmt THEN PO_DEBUG.debug_end(l_log_head); END IF;
1157 EXCEPTION
1158 WHEN OTHERS THEN
1159 IF g_debug_unexp THEN PO_DEBUG.debug_exc(l_log_head,l_progress); END IF;
1160 END insert_tlp_changes;
1161
1162 --------------------------------------------------------------------------------
1163 --Start of Comments
1164 --Name: delete_processed_headers
1165 --Pre-reqs:
1166 -- None
1167 --Modifies:
1168 -- a) PO_SESSION_GT Table (deletes data)
1169 --Locks:
1170 -- None.
1171 --Function:
1172 -- This procedure deletes the records from the 'remaining headers' queue
1173 -- in the PO_SESSION_GT table by checking if that PO_HEADER_ID has already
1174 -- been inserted in the 'to be processed' queue. The PO_HEADER_ID in the
1175 -- to-be-processed queue must be present in the INDEX_NUM2 column of the
1176 -- PO_SESSION_GT table.
1177 --
1178 --Parameters:
1179 --IN:
1180 --p_key
1181 -- The key to those records in PO_SESSSION_GT table that will be passed onto
1182 -- the iProc API.
1183 --p_key_remaining_headers
1184 -- The key to those PO_HEADER_ID's in PO_SESSSION_GT table that have not been
1185 -- completely checked to see if they contain any changes to the searchable
1186 -- attributes at Header/Line/Attr/TLP levels.
1187 --OUT:
1188 -- None
1189 --End of Comments
1190 --------------------------------------------------------------------------------
1191 PROCEDURE delete_processed_headers
1192 (
1193 p_key IN NUMBER
1194 , p_key_remaining_headers IN NUMBER
1195 )
1196 IS
1197 l_api_name CONSTANT VARCHAR2(30) := 'delete_processed_headers';
1198 l_log_head CONSTANT VARCHAR2(100) := g_log_head || l_api_name;
1199 l_progress VARCHAR2(3) := '000';
1200
1201 BEGIN
1202 IF g_debug_stmt THEN PO_DEBUG.debug_begin(l_log_head); END IF;
1203
1204 l_progress := '010';
1205 -- From the input list, delete those that were already marked as having
1206 -- header level changes. The PO_HEADER_ID in the to-be-processed queue
1207 -- is present in the INDEX_NUM2 column of the PO_SESSION_GT table.
1208 DELETE FROM PO_SESSION_GT GT_REMAINING_HDRS
1209 WHERE GT_REMAINING_HDRS.key = p_key_remaining_headers
1210 AND EXISTS
1211 (SELECT 'Header is already present in the to-be-processed queue in GT table'
1212 FROM PO_SESSION_GT GT1
1213 WHERE GT1.key = p_key
1214 AND GT1.index_num2 = GT_REMAINING_HDRS.index_num1);
1215
1219 EXCEPTION
1216 IF g_debug_stmt THEN PO_DEBUG.debug_stmt(l_log_head,l_progress,'Number or PO_HEADER_IDs deleted='||SQL%rowcount); END IF;
1217
1218 IF g_debug_stmt THEN PO_DEBUG.debug_end(l_log_head); END IF;
1220 WHEN OTHERS THEN
1221 IF g_debug_unexp THEN PO_DEBUG.debug_exc(l_log_head,l_progress); END IF;
1222 END delete_processed_headers;
1223
1224 --------------------------------------------------------------------------------
1225 --Start of Comments
1226 --Name: populate_sessiongt_for_orgs
1227 --Pre-reqs:
1228 -- None
1229 --Modifies:
1230 -- a) PO_SESSION_GT Table (inserts data to be passed onto iProc API)
1231 --Locks:
1232 -- None.
1233 --Function:
1234 -- To populate or rebuild the intermedia index required for iProcurement
1235 -- Catalog Search for Global Blankets, if their Org Assignments have been
1236 -- modified. It populates the GT table with all those GBPA lines in the
1237 -- given document, that have any of the following searchable fields modified:
1238 --
1239 -- Enabled/Disabled Flag
1240 -- Purchasing Org
1241 -- Purchasing Site
1242 --
1243 -- This API populates the PO_SESSION_GT table with the data required in the
1244 -- format specified by iProc. In the end, it calls the iProc API to
1245 -- populate/rebuild the index. All exceptions in this API will be silently
1246 -- logged in the debug logs. The errors/exceptions in the rebuild_index API
1247 -- are not thrown up to the calling program, so as not to interrupt the
1248 -- normal flow.
1249 --
1250 --Parameters:
1251 --IN:
1252 -- The list of PO_HEADER_ID's for the Global Blankets that are required to be
1253 -- made searchable in the Catalog.
1254 --OUT:
1255 -- None
1256 --End of Comments
1257 --------------------------------------------------------------------------------
1258 PROCEDURE populate_sessiongt_for_orgs
1259 (
1260 p_po_header_ids IN PO_TBL_NUMBER
1261 )
1262 IS
1263 l_api_name CONSTANT VARCHAR2(30) := 'populate_sessiongt_for_orgs';
1264 l_log_head CONSTANT VARCHAR2(100) := g_log_head || l_api_name;
1265 l_progress VARCHAR2(3) := '000';
1266
1267 l_key_input_headers PO_SESSION_GT.key%TYPE;
1268 l_key_org_assignments PO_SESSION_GT.key%TYPE;
1269 l_return_status VARCHAR2(1);
1270 l_num_rows_is_gt NUMBER := 0;
1271 BEGIN
1272 IF g_debug_stmt THEN PO_DEBUG.debug_begin(l_log_head); END IF;
1273
1274 l_progress := '010';
1275 -- Pick a key for temp table, used for org_assignemnts
1276 SELECT PO_SESSION_GT_S.nextval
1277 INTO l_key_org_assignments
1278 FROM DUAL;
1279
1280 -- Pick another key for temp table, used for input headers
1281 SELECT PO_SESSION_GT_S.nextval
1282 INTO l_key_input_headers
1283 FROM DUAL;
1284
1285 -- Insert ALL the doc_id's that came in as the input parameter list
1286 FORALL i in 1..p_po_header_ids.COUNT
1287 INSERT INTO PO_SESSION_GT
1288 (
1289 key
1290 , index_num1 -- PO_HEADER_IDs to be processed
1291 , char5 -- DATA INFO: Internal to PO
1292 )
1293 VALUES
1294 (
1295 l_key_input_headers
1296 , p_po_header_ids(i) -- PO_HEADER_IDs to be processed
1297 , 'ORG_ASSIGNMENT:Input PO_HEADER_IDs' -- DATA INFO: Internal to PO
1298 );
1299
1300 IF g_debug_stmt THEN PO_DEBUG.debug_stmt(l_log_head,l_progress,'Number or input PO_HEADER_IDs inserted into GT table='||SQL%rowcount); END IF;
1301
1302 -- Attribute changes that need rebuild index
1303 -- enabled_flag, purchasing org, purchasing site
1304 INSERT INTO PO_SESSION_GT
1305 (
1306 key
1307 , index_num1 -- PO_HEADER_ID
1308 , index_num2 -- ORG_ASSIGNMENT_ID
1309 , index_char1 -- Enabled/Disabled changed flag
1310 , index_char2 -- Other fields Changed Flag (Purc Org, Purch Site)
1311 , char5 -- DATA INFO: Internal to PO
1312 )
1313 SELECT
1314 l_key_org_assignments
1315 , POH.po_header_id -- PO_HEADER_ID
1316 , POGA.org_assignment_id -- ORG_ASSIGNMENT_ID
1317 , 'Y' -- Enabled/Disabled changed flag
1318 , 'Y' -- Other fields Changed Flag (Purc Org, Purch Site)
1319 , 'BLANKET:ORG_ASSIGNMENT' -- DATA INFO: Internal to PO
1320 FROM PO_HEADERS_ALL POH
1321 , PO_GA_ORG_ASSIGNMENTS POGA
1322 , PO_SESSION_GT GT_INPUT_HDRS
1323 WHERE GT_INPUT_HDRS.key = l_key_input_headers
1324 AND POH.po_header_id = GT_INPUT_HDRS.index_num1 -- index_num1 stores the PO_HEADER_ID
1325 AND POH.global_agreement_flag = 'Y'
1326 AND POGA.po_header_id = POH.po_header_id
1327 AND ( NOT EXISTS
1328 (SELECT 'Headers were archived'
1329 FROM PO_GA_ORG_ASSIGNMENTS_ARCHIVE ARCH
1330 WHERE ARCH.org_assignment_id = POGA.org_assignment_id))
1331 UNION ALL
1332 SELECT
1333 l_key_org_assignments
1334 , POH.po_header_id -- PO_HEADER_ID
1335 , POGA.org_assignment_id -- ORG_ASSIGNMENT_ID
1336 , 'Y' -- Enabled/Disabled changed flag
1337 , 'N' -- Other fields Changed Flag (Purc Org, Purch Site)
1338 , 'BLANKET:ORG_ASSIGNMENT' -- DATA INFO: Internal to PO
1339 FROM PO_HEADERS_ALL POH
1340 , PO_GA_ORG_ASSIGNMENTS POGA
1341 , PO_SESSION_GT GT_INPUT_HDRS
1342 WHERE GT_INPUT_HDRS.key = l_key_input_headers
1343 AND POH.po_header_id = GT_INPUT_HDRS.index_num1 -- index_num1 stores the PO_HEADER_ID
1344 AND POH.global_agreement_flag = 'Y'
1345 AND POGA.po_header_id = POH.po_header_id
1346 AND EXISTS
1347 (SELECT 'Only Enabled/disabled flag is modified'
1348 FROM PO_GA_ORG_ASSIGNMENTS_ARCHIVE ARCH
1349 WHERE ARCH.org_assignment_id = POGA.org_assignment_id
1350 AND ARCH.latest_external_flag = 'Y'
1354 UNION ALL
1351 AND ARCH.enabled_flag <> POGA.enabled_flag
1352 AND ARCH.purchasing_org_id = POGA.purchasing_org_id
1353 AND ARCH.vendor_site_id = POGA.vendor_site_id)
1355 SELECT
1356 l_key_org_assignments
1357 , POH.po_header_id -- PO_HEADER_ID
1358 , POGA.org_assignment_id -- ORG_ASSIGNMENT_ID
1359 , 'N' -- Enabled/Disabled changed flag
1360 , 'Y' -- Other fields Changed Flag (Purc Org, Purch Site)
1361 , 'BLANKET:ORG_ASSIGNMENT' -- DATA INFO: Internal to PO
1362 FROM PO_HEADERS_ALL POH
1363 , PO_GA_ORG_ASSIGNMENTS POGA
1364 , PO_SESSION_GT GT_INPUT_HDRS
1365 WHERE GT_INPUT_HDRS.key = l_key_input_headers
1366 AND POH.po_header_id = GT_INPUT_HDRS.index_num1 -- index_num1 stores the PO_HEADER_ID
1367 AND POH.global_agreement_flag = 'Y'
1368 AND POGA.po_header_id = POH.po_header_id
1369 AND EXISTS
1370 (SELECT 'Only Purch Org/Purch Site is modified'
1371 FROM PO_GA_ORG_ASSIGNMENTS_ARCHIVE ARCH
1372 WHERE ARCH.org_assignment_id = POGA.org_assignment_id
1373 AND ARCH.latest_external_flag = 'Y'
1374 AND (ARCH.purchasing_org_id <> POGA.purchasing_org_id OR
1375 ARCH.vendor_site_id <> POGA.vendor_site_id)
1376 AND ARCH.enabled_flag = POGA.enabled_flag)
1377 UNION ALL
1378 SELECT
1379 l_key_org_assignments
1380 , POH.po_header_id -- PO_HEADER_ID
1381 , POGA.org_assignment_id -- ORG_ASSIGNMENT_ID
1382 , 'Y' -- Enabled/Disabled changed flag
1383 , 'Y' -- Other fields Changed Flag (Purc Org, Purch Site)
1384 , 'BLANKET:ORG_ASSIGNMENT' -- DATA INFO: Internal to PO
1385 FROM PO_HEADERS_ALL POH
1386 , PO_GA_ORG_ASSIGNMENTS POGA
1387 , PO_SESSION_GT GT_INPUT_HDRS
1388 WHERE GT_INPUT_HDRS.key = l_key_input_headers
1389 AND POH.po_header_id = GT_INPUT_HDRS.index_num1 -- index_num1 stores the PO_HEADER_ID
1390 AND POH.global_agreement_flag = 'Y'
1391 AND POGA.po_header_id = POH.po_header_id
1392 AND EXISTS
1393 (SELECT 'Both enable_flag AND Purch Org/Purch Site are modified'
1394 FROM PO_GA_ORG_ASSIGNMENTS_ARCHIVE ARCH
1395 WHERE ARCH.org_assignment_id = POGA.org_assignment_id
1396 AND ARCH.latest_external_flag = 'Y'
1397 AND (ARCH.purchasing_org_id <> POGA.purchasing_org_id OR
1398 ARCH.vendor_site_id <> POGA.vendor_site_id)
1399 AND ARCH.enabled_flag <> POGA.enabled_flag);
1400
1401 l_num_rows_is_gt := SQL%rowcount;
1402
1403 IF g_debug_stmt THEN PO_DEBUG.debug_stmt(l_log_head,l_progress,'Number of recs inserted into GT table='||l_num_rows_is_gt); END IF;
1404
1405 IF (l_num_rows_is_gt > 0) THEN
1406 IF g_debug_stmt THEN PO_DEBUG.debug_stmt(l_log_head,l_progress,'Calling iProc API ICX_CAT_POPULATE_BPA_GRP.populateOnlineOrgAssgnmnts() to rebuild index'); END IF;
1407 -- Call iproc api for rebuild index for ReqTemplates
1408 -- Pass in the key for PO_SESSION_GT table
1409 l_progress := '040';
1410
1411 IF g_debug_stmt THEN
1412 PO_LOG.stmt_session_gt
1413 (
1414 p_module_base => l_log_head -- IN VARCHAR2
1415 , p_position => l_progress -- IN NUMBER
1416 , p_key => l_key_org_assignments -- IN NUMBER
1417 , p_column_name_tbl => NULL -- IN PO_TBL_VARCHAR30 DEFAULT NULL (For all columns)
1418 );
1419 END IF;
1420 /* Bug 14046925 : Changing p_commit parameter to false, to avoid commit during doApprove call */
1421 ICX_CAT_POPULATE_BPA_GRP.populateOnlineOrgAssgnmnts
1422 (
1423 p_api_version => 1.0, -- NUMBER IN
1424 p_commit => FND_API.G_FALSE, -- VARCHAR2 IN
1425 p_init_msg_list => FND_API.G_FALSE, -- VARCHAR2 IN
1426 p_validation_level => FND_API.G_VALID_LEVEL_FULL, -- VARCHAR2 IN
1427 x_return_status => l_return_status, -- VARCHAR2 OUT
1428 p_key => l_key_org_assignments -- NUMBER IN
1429 );
1430
1431 l_progress := '050';
1432 -- In case of error, just log in debug logs. There is no need to raise
1433 -- it up, because rebuild_index errors have to be ignored by the calling
1434 -- program.
1435 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1436 IF g_debug_stmt THEN PO_DEBUG.debug_stmt(l_log_head,l_progress,'iProc API ICX_CAT_POPULATE_BPA_GRP.populateOnlineOrgAssgnmnts() returned error: '||l_return_status); END IF;
1437 END IF;
1438 ELSE
1439 IF g_debug_stmt THEN PO_DEBUG.debug_stmt(l_log_head,l_progress,'Skipped: iProc API ICX_CAT_POPULATE_BPA_GRP.populateOnlineOrgAssgnmnts()'); END IF;
1440 END IF;
1441
1442 IF g_debug_stmt THEN PO_DEBUG.debug_end(l_log_head); END IF;
1443 EXCEPTION
1444 WHEN OTHERS THEN
1445 IF g_debug_unexp THEN PO_DEBUG.debug_exc(l_log_head,l_progress); END IF;
1446 END populate_sessiongt_for_orgs;
1447
1448 -- <Bug 7655719>
1449 -- Commented out procedures - synch_item_description and synch_item_category
1450 --------------------------------------------------------------------------------
1451 --Start of Comments
1452 --Name: synch_item_description
1453 --Pre-reqs:
1454 -- None
1455 --Modifies:
1456 -- PO_ATTRIBUTE_VALUES_TLP.item_description
1457 --Locks:
1458 -- None.
1459 --Function:
1460 -- When the item description is updated on a Blanket PO Line, it has to be
1461 -- updated in the TLP level as well, so that the line is searchable with the
1462 -- new description.
1463 -- This procedure is called from the ON-UPDATE trigger of the Enter PO
1464 -- form if the type_lookup_code is BLANKET. It updates the
1465 -- PO_ATTRIBUTE_VALUES_TLP.item_description column with the description
1466 -- at the line level.
1467 -- This also works the same for QUOTATIONS and REQ-TEMPLATE lines.
1471 --p_doc_type
1468 --
1469 --Parameters:
1470 --IN:
1472 -- The document type of the header. This can only be BLANKET or QUOTATION
1473 --p_po_header_id
1474 -- The PO header for which the attribute TLP rows need to be synch'd.
1475 -- This is applicable when p_type is BLANKET or QUOTATION.
1476 --p_po_header_ids
1477 -- The list of PO headers for which the attribute and TLP rows need to be synch'd.
1478 -- This is applicable when p_type is BLANKET_BULK
1479 --p_reqexpress_name
1480 --p_org_id
1481 -- The Req Template name and ORG_ID on the Req Template.
1482 -- These are applicable when p_type is REQ_TEMPLATE
1483 --OUT:
1484 -- None
1485 --End of Comments
1486 --------------------------------------------------------------------------------
1487 /*PROCEDURE synch_item_description
1488 (
1489 p_type IN VARCHAR2
1490 , p_po_header_id IN NUMBER DEFAULT NULL
1491 , p_po_header_ids IN PO_TBL_NUMBER DEFAULT NULL
1492 , p_reqexpress_name IN VARCHAR2 DEFAULT NULL
1493 , p_org_id IN NUMBER DEFAULT NULL
1494 )
1495 IS
1496 l_api_name CONSTANT VARCHAR2(30) := 'synch_item_description';
1497 l_log_head CONSTANT VARCHAR2(100) := g_log_head || l_api_name;
1498 l_progress VARCHAR2(3) := '000';
1499
1500 l_po_line_id_list PO_TBL_NUMBER;
1501 l_item_description_list PO_TBL_VARCHAR240;
1502 l_created_lang_list PO_TBL_VARCHAR5;
1503
1504 l_req_template_name_list PO_TBL_VARCHAR25;
1505 l_req_template_line_num_list PO_TBL_NUMBER;
1506 l_req_template_org_id_list PO_TBL_NUMBER;
1507
1508 l_key PO_SESSION_GT.key%TYPE;
1509 l_base_lang FND_LANGUAGES.language_code%TYPE;
1510 BEGIN
1511 IF g_debug_stmt THEN PO_DEBUG.debug_begin(l_log_head); END IF;
1512
1513 IF (p_type IN (TYPE_BLANKET, TYPE_QUOTATION, TYPE_BLANKET_BULK)) THEN
1514 l_progress := '010';
1515 -- pick a new key for temp table
1516 SELECT PO_SESSION_GT_S.nextval
1517 INTO l_key
1518 FROM DUAL;
1519
1520 IF (p_type IN (TYPE_BLANKET, TYPE_QUOTATION)) THEN
1521 l_progress := '020';
1522
1523 -- Only 1 row
1524 INSERT INTO PO_SESSION_GT(key, index_num1) -- PO_HEADER_ID
1525 VALUES (l_key, p_po_header_id);
1526
1527 ELSE -- BLANKET_BULK
1528 l_progress := '030';
1529
1530 -- Multiple rows
1531 FORALL i in 1..p_po_header_ids.COUNT
1532 INSERT INTO PO_SESSION_GT(key, index_num1) -- PO_HEADER_ID
1533 VALUES (l_key, p_po_header_ids(i));
1534
1535 END IF;
1536
1537 l_progress := '040';
1538 -- Get the list of PO_LINE_ID's whose item description have changed.
1539 SELECT POL.po_line_id,
1540 POL.item_description,
1541 POH.created_language
1542 BULK COLLECT INTO
1543 l_po_line_id_list,
1544 l_item_description_list,
1545 l_created_lang_list
1546 FROM PO_LINES_ALL POL,
1547 PO_HEADERS_ALL POH,
1548 PO_SESSION_GT INPUT_HDRS
1549 WHERE POH.po_header_id = INPUT_HDRS.index_num1
1550 AND INPUT_HDRS.key = l_key -- Bug 6942699 - Added the condition to improve performance
1551 AND POL.po_header_id = POH.po_header_id
1552 AND (NOT EXISTS
1553 (SELECT 'Lines were archived'
1554 FROM PO_LINES_ARCHIVE_ALL POLA
1555 WHERE POLA.po_line_id = POL.po_line_id)
1556 OR EXISTS
1557 (SELECT 'Item description has been modified'
1558 FROM PO_LINES_ARCHIVE_ALL POLA
1559 WHERE POLA.po_line_id = POL.po_line_id
1560 AND POLA.latest_external_flag = 'Y'
1561 AND (POL.item_description <> POLA.item_description OR
1562 (POL.item_description IS NULL AND POLA.item_description IS NOT NULL) OR
1563 (POL.item_description IS NOT NULL AND POLA.item_description IS NULL))));
1564
1565 IF g_debug_stmt THEN PO_DEBUG.debug_stmt(l_log_head,l_progress,'Number or PO_LINE descriptions selected to synch='||SQL%rowcount); END IF;
1566
1567 l_progress := '050';
1568 -- For all the lines whose description have changed, update the TLP records as well.
1569 FORALL i IN 1 .. l_po_line_id_list.COUNT
1570 UPDATE PO_ATTRIBUTE_VALUES_TLP POTLP
1571 SET description = l_item_description_list(i)
1572 WHERE POTLP.po_line_id = l_po_line_id_list(i)
1573 AND language = l_created_lang_list(i);
1574
1575 IF g_debug_stmt THEN PO_DEBUG.debug_stmt(l_log_head,l_progress,'Number or PO_LINE descriptions synchd='||SQL%rowcount); END IF;
1576
1577 ELSIF (p_type = TYPE_REQ_TEMPLATE) THEN
1578
1579 l_progress := '060';
1580 -- Get the list of Req template line's whose item description have changed.
1581 SELECT PORTL.express_name,
1582 PORTL.sequence_num,
1583 PORTL.org_id,
1584 PORTL.item_description
1585 BULK COLLECT INTO
1586 l_req_template_name_list,
1587 l_req_template_line_num_list,
1588 l_req_template_org_id_list,
1589 l_item_description_list
1590 FROM PO_REQEXPRESS_LINES_ALL PORTL
1591 WHERE PORTL.express_name = p_reqexpress_name
1592 AND PORTL.org_id = p_org_id;
1593
1594 IF g_debug_stmt THEN PO_DEBUG.debug_stmt(l_log_head,l_progress,'Number or REQ_TEMPLATE line descriptions selected to synch='||SQL%rowcount); END IF;
1595
1596 l_progress := '070';
1597 -- Get the base language
1598 SELECT language_code
1599 INTO l_base_lang
1600 FROM FND_LANGUAGES
1601 WHERE installed_flag='B';
1602
1603 IF g_debug_stmt THEN PO_DEBUG.debug_stmt(l_log_head,l_progress,'Base language is = '||l_base_lang); END IF;
1604
1605 l_progress := '080';
1606 -- For all the lines whose description have changed, update the TLP records as well.
1610 WHERE POTLP.req_template_name = l_req_template_name_list(i)
1607 FORALL i IN 1 .. l_req_template_line_num_list.COUNT
1608 UPDATE PO_ATTRIBUTE_VALUES_TLP POTLP
1609 SET description = l_item_description_list(i)
1611 AND req_template_line_num = l_req_template_line_num_list(i)
1612 AND org_id = l_req_template_org_id_list(i)
1613 AND language = l_base_lang;
1614
1615 IF g_debug_stmt THEN PO_DEBUG.debug_stmt(l_log_head,l_progress,'Number or REQ_TEMPLATE line descriptions synchd='||SQL%rowcount); END IF;
1616 END IF;
1617
1618 l_progress := '090';
1619
1620 IF g_debug_stmt THEN PO_DEBUG.debug_end(l_log_head); END IF;
1621 EXCEPTION
1622 WHEN OTHERS THEN
1623 IF g_debug_unexp THEN PO_DEBUG.debug_exc(l_log_head,l_progress); END IF;
1624 END synch_item_description;
1625
1626 --------------------------------------------------------------------------------
1627 --Start of Comments
1628 --Bug 6979842: Added new procedure
1629 --Name: synch_item_category
1630 --Pre-reqs:
1631 -- None
1632 --Modifies:
1633 -- PO_ATTRIBUTE_VALUES.ip_category_id
1634 -- PO_ATTRIBUTE_VALUES_TLP.ip_category_id
1635 --Locks:
1636 -- None.
1637 --Function:
1638 -- When the item category is updated on a Blanket PO Line, it has to be updated
1639 -- in the attribute (TLP) level as well, so that the line is searchable with
1640 -- the new category.
1641 -- This procedure is called from the ON-UPDATE trigger of the Enter PO
1642 -- form if the type_lookup_code is BLANKET. It updates the
1643 -- PO_ATTRIBUTE_VALUES(TLP).ip_category_id column with the category
1644 -- at the line level.
1645 -- This also works the same for QUOTATIONS and REQ-TEMPLATE lines.
1646 --
1647 --Parameters:
1648 --IN:
1649 --p_doc_type
1650 -- The document type of the header. This can only be BLANKET or QUOTATION
1651 --p_po_header_id
1652 -- The PO header for which the attribute (TLP) rows need to be synch'd.
1653 -- This is applicable when p_type is BLANKET or QUOTATION.
1654 --p_po_header_ids
1655 -- The list of PO headers for which the attribute and TLP rows need to be synch'd.
1656 -- This is applicable when p_type is BLANKET_BULK
1657 --p_reqexpress_name
1658 --p_org_id
1659 -- The Req Template name and ORG_ID on the Req Template.
1660 -- These are applicable when p_type is REQ_TEMPLATE
1661 --OUT:
1662 -- None
1663 --End of Comments
1664 --------------------------------------------------------------------------------
1665 PROCEDURE synch_item_category
1666 (
1667 p_type IN VARCHAR2
1668 , p_po_header_id IN NUMBER DEFAULT NULL
1669 , p_po_header_ids IN PO_TBL_NUMBER DEFAULT NULL
1670 , p_reqexpress_name IN VARCHAR2 DEFAULT NULL
1671 , p_org_id IN NUMBER DEFAULT NULL
1672 )
1673 IS
1674 l_api_name CONSTANT VARCHAR2(30) := 'synch_item_category';
1675 l_log_head CONSTANT VARCHAR2(100) := g_log_head || l_api_name;
1676 l_progress VARCHAR2(3) := '000';
1677
1678 l_po_line_id_list PO_TBL_NUMBER;
1679 l_category_id_list PO_TBL_VARCHAR240;
1680 l_new_ip_category_id NUMBER;
1681 l_old_ip_category_id_list PO_TBL_NUMBER;
1682
1683 l_req_template_name_list PO_TBL_VARCHAR25;
1684 l_req_template_line_num_list PO_TBL_NUMBER;
1685 l_req_template_org_id_list PO_TBL_NUMBER;
1686
1687 l_key PO_SESSION_GT.key%TYPE;
1688 BEGIN
1689 IF g_debug_stmt THEN PO_DEBUG.debug_begin(l_log_head); END IF;
1690
1691 IF (p_type IN (TYPE_BLANKET, TYPE_QUOTATION, TYPE_BLANKET_BULK)) THEN
1692 l_progress := '010';
1693 -- pick a new key for temp table
1694 SELECT PO_SESSION_GT_S.nextval
1695 INTO l_key
1696 FROM DUAL;
1697
1698 IF (p_type IN (TYPE_BLANKET, TYPE_QUOTATION)) THEN
1699 l_progress := '020';
1700
1701 -- Only 1 row
1702 INSERT INTO PO_SESSION_GT(key, index_num1) -- PO_HEADER_ID
1703 VALUES (l_key, p_po_header_id);
1704
1705 ELSE -- BLANKET_BULK
1706 l_progress := '030';
1707
1708 -- Multiple rows
1709 FORALL i in 1..p_po_header_ids.COUNT
1710 INSERT INTO PO_SESSION_GT(key, index_num1) -- PO_HEADER_ID
1711 VALUES (l_key, p_po_header_ids(i));
1712
1713 END IF;
1714
1715 l_progress := '040';
1716 -- Get the list of item category ids from PO_LINES_ALL
1717 SELECT POL.po_line_id,
1718 POL.category_id,
1719 POATR.ip_category_id
1720 BULK COLLECT INTO
1721 l_po_line_id_list,
1722 l_category_id_list,
1723 l_old_ip_category_id_list
1724 FROM PO_LINES_ALL POL,
1725 PO_HEADERS_ALL POH,
1726 PO_ATTRIBUTE_VALUES POATR,
1727 PO_SESSION_GT INPUT_HDRS
1728 WHERE POH.po_header_id = INPUT_HDRS.index_num1
1729 AND INPUT_HDRS.key = l_key
1730 AND POL.po_header_id = POH.po_header_id
1731 AND POL.po_line_id = POATR.po_line_id
1732 AND POL.category_id IS NOT NULL;
1733
1734 IF g_debug_stmt THEN PO_DEBUG.debug_stmt(l_log_head,l_progress,'Number or PO_LINE category ids selected to synch='||SQL%rowcount); END IF;
1735
1736 l_progress := '050';
1737 -- Get the ip_category_id for all the po lines selected above and update
1738 -- PO_ATTRIBUTE_VALUES and PO_ATTRIBUTE_VALUES_TLP
1739 FOR i IN 1 .. l_po_line_id_list.COUNT LOOP
1740 PO_ATTRIBUTE_VALUES_PVT.get_ip_category_id(l_category_id_list(i), l_new_ip_category_id);
1741
1742 -- Update ip_category_id only if changed.
1743 IF l_new_ip_category_id <> l_old_ip_category_id_list(i) THEN
1744 UPDATE PO_ATTRIBUTE_VALUES
1745 SET ip_category_id = l_new_ip_category_id
1746 WHERE po_line_id = l_po_line_id_list(i);
1747
1748 UPDATE PO_ATTRIBUTE_VALUES_TLP
1749 SET ip_category_id = l_new_ip_category_id
1753
1750 WHERE po_line_id = l_po_line_id_list(i);
1751 END IF;
1752 END LOOP;
1754 IF g_debug_stmt THEN PO_DEBUG.debug_stmt(l_log_head,l_progress,'Number or PO_LINE category ids synchd='||SQL%rowcount); END IF;
1755
1756 ELSIF (p_type = TYPE_REQ_TEMPLATE) THEN
1757
1758 l_progress := '060';
1759 -- Get the list of category ids from PO_REQEXPRESS_LINES_ALL
1760 SELECT PORTL.express_name,
1761 PORTL.sequence_num,
1762 PORTL.org_id,
1763 PORTL.category_id,
1764 POATR.ip_category_id
1765 BULK COLLECT INTO
1766 l_req_template_name_list,
1767 l_req_template_line_num_list,
1768 l_req_template_org_id_list,
1769 l_category_id_list,
1770 l_old_ip_category_id_list
1771 FROM PO_REQEXPRESS_LINES_ALL PORTL,
1772 PO_ATTRIBUTE_VALUES POATR
1773 WHERE PORTL.express_name = p_reqexpress_name
1774 AND PORTL.po_line_id = POATR.po_line_id
1775 AND PORTL.org_id = p_org_id;
1776
1777 IF g_debug_stmt THEN PO_DEBUG.debug_stmt(l_log_head,l_progress,'Number or REQ_TEMPLATE line category ids selected to synch='||SQL%rowcount); END IF;
1778
1779 l_progress := '070';
1780
1781 -- Get the ip_category_id for all the po lines selected above and update
1782 -- PO_ATTRIBUTE_VALUES and PO_ATTRIBUTE_VALUES_TLP
1783 FOR i IN 1 .. l_req_template_line_num_list.COUNT LOOP
1784 PO_ATTRIBUTE_VALUES_PVT.get_ip_category_id(l_category_id_list(i), l_new_ip_category_id);
1785
1786 -- Update ip_category_id only if changed.
1787 IF l_new_ip_category_id <> l_old_ip_category_id_list(i) THEN
1788 UPDATE PO_ATTRIBUTE_VALUES
1789 SET ip_category_id = l_new_ip_category_id
1790 WHERE req_template_name = l_req_template_name_list(i)
1791 AND req_template_line_num = l_req_template_line_num_list(i)
1792 AND org_id = l_req_template_org_id_list(i);
1793
1794 UPDATE PO_ATTRIBUTE_VALUES_TLP
1795 SET ip_category_id = l_new_ip_category_id
1796 WHERE req_template_name = l_req_template_name_list(i)
1797 AND req_template_line_num = l_req_template_line_num_list(i)
1798 AND org_id = l_req_template_org_id_list(i);
1799 END IF;
1800 END LOOP;
1801
1802 IF g_debug_stmt THEN PO_DEBUG.debug_stmt(l_log_head,l_progress,'Number or REQ_TEMPLATE line category ids synchd='||SQL%rowcount); END IF;
1803 END IF;
1804
1805 l_progress := '080';
1806
1807 IF g_debug_stmt THEN PO_DEBUG.debug_end(l_log_head); END IF;
1808 EXCEPTION
1809 WHEN OTHERS THEN
1810 IF g_debug_unexp THEN PO_DEBUG.debug_exc(l_log_head,l_progress); END IF;
1811 END synch_item_category;*/
1812
1813 END PO_CATALOG_INDEX_PVT;
[Home] [Help]