1 PACKAGE ICX_CAT_SQE_PVT AS
2 /* $Header: ICXVSQES.pls 120.2 2006/05/23 18:29:10 rwidjaja noship $*/
3
4 -- procedure to create sqes for a given content zone
5 -- this constructs the intermedia expression for the content zone, puts
6 -- it in an sqe and returns the sqe name
7 -- if the expression is too long, it is an error and x_return_status
8 -- will be set to 'E'
9 PROCEDURE create_sqes_for_zone
10 (
11 p_content_zone_id IN NUMBER,
12 p_supplier_attr_action_flag IN VARCHAR2,
13 p_supplier_ids IN ICX_TBL_NUMBER,
14 p_supplier_site_ids IN ICX_TBL_NUMBER,
15 p_items_without_supplier_flag IN VARCHAR2,
16 p_category_attr_action_flag IN VARCHAR2,
17 p_category_ids IN ICX_TBL_NUMBER,
18 p_items_without_shop_catg_flag IN VARCHAR2,
19 x_return_status OUT NOCOPY VARCHAR2,
20 x_sqe_sequence IN OUT NOCOPY NUMBER
21 );
22
23 -- procedure to combine three expressions with the '&' operator
24 -- depending on which are not null
25 PROCEDURE combine_exprs
26 (
27 p_expr1 IN VARCHAR2,
28 p_expr2 IN VARCHAR2,
29 p_expr3 IN VARCHAR2,
30 x_result_expr OUT NOCOPY VARCHAR2
31 );
32
33 -- procedure to construct the intermedia expressions for a given zone
34 -- takes in all the required parameters and returns the intermedia expressions
35 -- it returns one intermedia expression for internal only items
36 -- one for purchasable only items and one for both
37 PROCEDURE construct_exprs_for_zone
38 (
39 p_supplier_attr_action_flag IN VARCHAR2,
40 p_supplier_ids IN ICX_TBL_NUMBER,
41 p_supplier_site_ids IN ICX_TBL_NUMBER,
42 p_items_without_supplier_flag IN VARCHAR2,
43 p_category_attr_action_flag IN VARCHAR2,
44 p_category_ids IN ICX_TBL_NUMBER,
45 p_items_without_shop_catg_flag IN VARCHAR2,
46 x_int_intermedia_expression OUT NOCOPY VARCHAR2,
47 x_purch_intermedia_expression OUT NOCOPY VARCHAR2,
48 x_both_intermedia_expression OUT NOCOPY VARCHAR2
49 );
50
51 -- procedure to constuct the supplier and site expression for a given zone
52 -- takes in the required parameters and returns the supplier and site expression
53 PROCEDURE construct_supp_and_site_expr
54 (
55 p_supplier_attr_action_flag IN VARCHAR2,
56 p_supplier_ids IN ICX_TBL_NUMBER,
57 p_supplier_site_ids IN ICX_TBL_NUMBER,
58 p_items_without_supplier_flag IN VARCHAR2,
59 x_supplier_and_site_expr OUT NOCOPY VARCHAR2
60 );
61
62 -- procedure to constuct the category expression for a given zone
63 -- takes in the required parameters and returns the category expression
64 PROCEDURE construct_category_expr
65 (
66 p_category_attr_action_flag IN VARCHAR2,
67 p_category_ids IN ICX_TBL_NUMBER,
68 p_items_without_shop_catg_flag IN VARCHAR2,
69 x_category_expr OUT NOCOPY VARCHAR2
70 );
71
72 -- procedure to purge the deleted sqes
73 -- this purges all sqes that have been deleted more than a day ago
74 PROCEDURE purge_deleted_sqes;
75
76 -- procedure to sync sqes for all content zones
77 -- called for hierarchy changes from a concurrent program
78 -- or from the schema loader
79 -- this will recreate sqes for all the content zones
80 -- if some content zones have errors since the expression is too long
81 -- the job will be errored out with a message specifying which zones failed
82 -- the successful zones will however be updated
83 PROCEDURE sync_sqes_hier_change_internal
84 (
85 x_return_status OUT NOCOPY VARCHAR2,
86 x_errored_zone_name_list OUT NOCOPY VARCHAR2
87 );
88
89 -- procedure to sync sqes for all content zones
90 -- called for hierarchy changes from a concurrent program
91 -- this will call the main api which does the actual sync
92 -- this api in addition updates the failed line messages
93 -- and the job status
94 PROCEDURE sync_sqes_for_hierarchy_change
95 (
96 x_errbuf OUT NOCOPY VARCHAR2,
97 x_retcode OUT NOCOPY NUMBER
98 );
99
100 -- procedure to sync sqes for all content zones
101 -- called for hierarchy changes from the schema loader
102 -- this will call the main api which does the actual sync
103 -- this api in addition updates the failed line messages
104 -- and the failed lines table
105 PROCEDURE sync_sqes_for_hierarchy_change
106 (
107 p_request_id IN NUMBER,
108 p_line_number IN NUMBER,
109 p_action IN VARCHAR2,
110 x_return_status OUT NOCOPY VARCHAR2
111 );
112
113 -- procedure to sync the sqes for all the zones
114 -- this will only be called during upgrade
115 -- this is also useful for testing purposes and also useful if we
116 -- want to re-sync all zones on any instance
117 PROCEDURE sync_sqes_for_all_zones;
118
119 END ICX_CAT_SQE_PVT;
[Home] [Help]