[Home] [Help]
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
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
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
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
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;
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';
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
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: (
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
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
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;
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';
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
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: (
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
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
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;
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:
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
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
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
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:
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
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
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:
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
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:
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
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:
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
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:
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.
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:
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
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
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.
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:
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
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
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
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: --
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
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'
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
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;
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
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
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
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;
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
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
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
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
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
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
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
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
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
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:
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
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';
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:
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
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
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
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';
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:
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