1 PACKAGE ICX_CAT_BUILD_CTX_SQL_PVT AUTHID CURRENT_USER AS
2 /* $Header: ICXVBCSS.pls 120.1 2006/05/03 12:19 sbgeorge noship $*/
3
4 -----------------------------------------------------------
5 -- Global variables --
6 -----------------------------------------------------------
7
8 TYPE g_metadata_rec_type IS RECORD
9 (
10 attribute_id NUMBER,
11 key icx_cat_attributes_tl.key%TYPE,
12 type NUMBER,
13 section_tag NUMBER,
14 attribute_length NUMBER,
15 stored_in_table icx_cat_attributes_tl.stored_in_table%TYPE,
16 stored_in_column icx_cat_attributes_tl.stored_in_column%TYPE
17 );
18
19 TYPE g_metadata_tbl_type IS TABLE OF g_metadata_rec_type
20 INDEX BY BINARY_INTEGER;
21
22 TYPE g_ctx_sql_rec_type IS RECORD
23 (
24 ctx_sql_string VARCHAR2(32000),
25 bind_sequence NUMBER
26 );
27
28 TYPE g_ctx_sql_tbl_type IS TABLE OF g_ctx_sql_rec_type
29 INDEX BY BINARY_INTEGER;
30
31 ------------ Hard coded sequences for searchable attributes ----------
32 g_seqMandatoryBaseRow NUMBER := 1;
33 g_seqForSupplierRow NUMBER := 2;
34 g_seqForInternalItemNumRow NUMBER := 3;
35 g_seqForSourceRow NUMBER := 4;
36 g_seqForItemRevisionRow NUMBER := 5;
37 g_seqForShoppingCategoryRow NUMBER := 6;
38 g_seqStartReqularBaseRow NUMBER := 100;
39 g_seqEndReqularBaseRow NUMBER := 5000;
40 g_seqStartRegularCatgRow NUMBER := 5000;
41 g_seqEndRegularCatgRow NUMBER := 9999;
42 g_seqForPurchasingOrgIdRow NUMBER := 15001;
43
44 PROCEDURE checkIfAttributeIsSrchble(p_attribute_key IN VARCHAR2,
45 p_searchable OUT NOCOPY NUMBER,
46 p_section_tag OUT NOCOPY NUMBER);
47
48 PROCEDURE buildMetadataInfo(p_category_id IN NUMBER,
49 p_special_metadata_tbl IN OUT NOCOPY g_metadata_tbl_type,
50 p_regular_nontl_metadata_tbl IN OUT NOCOPY g_metadata_tbl_type,
51 p_regular_tl_metadata_tbl IN OUT NOCOPY g_metadata_tbl_type);
52
53 PROCEDURE getAttributeDetails(p_special_metadata_tbl IN g_metadata_tbl_type,
54 p_attribute_key IN VARCHAR2,
55 p_attribute_searchable IN OUT NOCOPY VARCHAR2,
56 p_metadata_rec IN OUT NOCOPY g_metadata_rec_type);
57
58 PROCEDURE buildCtxSql(p_category_id IN NUMBER,
59 p_doc_source IN VARCHAR2,
60 p_where_clause IN VARCHAR2 DEFAULT 'ROWID',
61 p_special_metadata_tbl IN g_metadata_tbl_type,
62 p_regular_nontl_metadata_tbl IN g_metadata_tbl_type,
63 p_regular_tl_metadata_tbl IN g_metadata_tbl_type,
64 p_all_ctx_sql_tbl IN OUT NOCOPY g_ctx_sql_tbl_type,
65 p_special_ctx_sql_tbl IN OUT NOCOPY g_ctx_sql_tbl_type,
66 p_regular_ctx_sql_tbl IN OUT NOCOPY g_ctx_sql_tbl_type);
67
68 END ICX_CAT_BUILD_CTX_SQL_PVT;