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