[Home] [Help]
96:
97: is
98:
99: /* Start Bug 3336172 */
100: TYPE g_sec_level_tbl_type IS TABLE OF po_document_types_b.security_level_code%TYPE;
101: TYPE g_doc_type_code_tbl_type IS TABLE OF po_document_types_b.document_type_code%TYPE;
102: TYPE g_doc_subtype_tbl_type IS TABLE OF po_document_types_b.document_subtype%TYPE;
103:
104: l_sec_level_tbl g_sec_level_tbl_type;
97: is
98:
99: /* Start Bug 3336172 */
100: TYPE g_sec_level_tbl_type IS TABLE OF po_document_types_b.security_level_code%TYPE;
101: TYPE g_doc_type_code_tbl_type IS TABLE OF po_document_types_b.document_type_code%TYPE;
102: TYPE g_doc_subtype_tbl_type IS TABLE OF po_document_types_b.document_subtype%TYPE;
103:
104: l_sec_level_tbl g_sec_level_tbl_type;
105: l_doc_type_code_tbl g_doc_type_code_tbl_type;
98:
99: /* Start Bug 3336172 */
100: TYPE g_sec_level_tbl_type IS TABLE OF po_document_types_b.security_level_code%TYPE;
101: TYPE g_doc_type_code_tbl_type IS TABLE OF po_document_types_b.document_type_code%TYPE;
102: TYPE g_doc_subtype_tbl_type IS TABLE OF po_document_types_b.document_subtype%TYPE;
103:
104: l_sec_level_tbl g_sec_level_tbl_type;
105: l_doc_type_code_tbl g_doc_type_code_tbl_type;
106: l_doc_subtype_tbl g_doc_subtype_tbl_type;
121: * A. Removing the 6 way cartesian join all on the same table.
122: * Instead, use a PL-SQL table variable to store the data
123: * we need, and then loop through it. This should avoid the
124: * massive join while also avoiding 6 PL/SQL to SQL context switches.
125: * B. Use the PO_DOCUMENT_TYPES_B base view instead of PO_DOCUMENT_TYPES
126: * There is no need for translation in this code, so the base view, which
127: * does not join with a translation table is a better choice.
128: * Note: we use three PL-SQL tables, as in 8I DB, you cannot bulk collect
129: * into a table of records! In 9I, that is allowed, but for now,
131: */
132:
133: SELECT security_level_code, document_type_code, document_subtype
134: BULK COLLECT into l_sec_level_tbl, l_doc_type_code_tbl, l_doc_subtype_tbl
135: FROM po_document_types_b
136: WHERE (document_type_code = 'PO' and document_subtype = 'STANDARD')
137: or (document_type_code = 'PA' and document_subtype = 'BLANKET')
138: or (document_type_code = 'PA' and document_subtype = 'CONTRACT')
139: or (document_type_code = 'PO' and document_subtype = 'PLANNED')