[Home] [Help]
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
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
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
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
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;
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';
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
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: (
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
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
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;
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';
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
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: (
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
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
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;
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:
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
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
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
674: -- SQL Why : To check if we need to call iProc API
675: -- SQL Join: key
676: SELECT count(*)
677: INTO l_num_rows_is_gt
678: FROM PO_SESSION_GT
679: WHERE key = l_key;
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:
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
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
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:
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
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
811: AND ( NOT EXISTS
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
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:
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
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
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.
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:
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
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
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.
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:
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
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
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
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: --
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
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'
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
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:
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;
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
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
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
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;
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
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
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
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
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
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
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
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
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
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:
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
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';
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:
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
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
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
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';
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:
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