1 PACKAGE pos_product_service_utl_pkg AS
2 /* $Header: POSPSUTS.pls 120.5.12010000.2 2008/10/15 13:49:28 dashah ship $*/
3 --
4 -- before calling other procedures in this package,
5 -- call this procedure to initialize
6 --
7 -- x_status: Y or N for success or failure
8 -- x_error_message: an error message if there is an error
9 --
10 PROCEDURE initialize
11 (x_status OUT NOCOPY VARCHAR2,
12 x_error_message OUT NOCOPY VARCHAR2
13 );
14
15 PROCEDURE validate_segment_prof_nocache (
16 p_product_segment_definition IN VARCHAR2
17 , x_status OUT NOCOPY VARCHAR2
18 , x_error_message OUT NOCOPY VARCHAR2
19 );
20
21 PROCEDURE save_segment_profile (
22 p_product_segment_definition IN VARCHAR2
23 , x_status OUT NOCOPY VARCHAR2
24 , x_error_message OUT NOCOPY VARCHAR2
25 );
26
27 PROCEDURE get_product_meta_data
28 (x_product_segment_definition OUT NOCOPY VARCHAR2,
29 x_product_segment_count OUT NOCOPY NUMBER,
30 x_default_po_category_set_id OUT NOCOPY NUMBER,
31 x_delimiter OUT NOCOPY VARCHAR2
32 );
33
34 PROCEDURE get_product_segment_info
35 (p_index IN NUMBER,
36 x_column_name OUT NOCOPY VARCHAR2,
37 x_value_set_id OUT NOCOPY NUMBER,
38 x_validation_type OUT NOCOPY VARCHAR2,
39 x_table_name OUT NOCOPY VARCHAR2,
40 x_meaning_column OUT NOCOPY VARCHAR2,
41 x_id_column OUT NOCOPY VARCHAR2,
42 x_value_column OUT NOCOPY VARCHAR2,
43 x_where_clause OUT NOCOPY VARCHAR2,
44 x_parent_segment_index OUT NOCOPY INTEGER
45 );
46 --
47
48 -- get the description of product and service for a row
49 -- in pos_sup_products_services table.
50 --
51 PROCEDURE get_product_description
52 (p_classification_id IN NUMBER, x_description OUT NOCOPY VARCHAR2 );
53
54 -- get the description of product and service represented
55 -- in the "category" format: 'value.value.valule..'
56 --
57 PROCEDURE get_product_description
58 (p_category IN VARCHAR2, x_description OUT NOCOPY VARCHAR2 );
59
60 -- get the description of product and service for a row
61 -- in pos_sup_products_services table, and whether there
62 -- is a subcategories for the product and service
63 PROCEDURE get_desc_check_subcategory
64 (p_classification_id IN NUMBER,
65 x_description OUT NOCOPY VARCHAR2,
66 x_has_subcategory OUT NOCOPY VARCHAR2 -- return Y or N
67 );
68
69 FUNCTION get_vendor_by_category_query RETURN VARCHAR2;
70
71 -- return a string that includes meta data and the product segments info
72 FUNCTION debug_to_string RETURN VARCHAR2;
73
74 -- this function is included in the spec to allow testing
75 -- please do not call this function except for testing purpose
76 FUNCTION get_product_description
77 (p_product_segment_index IN NUMBER,
78 p_segment_value IN VARCHAR2,
79 p_parent_segment_value IN VARCHAR2 DEFAULT NULL
80 ) RETURN VARCHAR2;
81
82 -- get the description of product and service for a row
83 -- in pos_product_service_requests table, and whether there
84 -- is a subcategories for the product and service
85 PROCEDURE get_req_desc_has_sub
86 (p_ps_request_id IN NUMBER,
87 x_description OUT NOCOPY VARCHAR2,
88 x_has_subcategory OUT nocopy VARCHAR2 -- return Y or N
89 );
90
91
92
93 PROCEDURE add_new_ps_req
94 ( p_vendor_id IN NUMBER,
95 p_segment1 IN VARCHAR2,
96 p_segment2 IN VARCHAR2,
97 p_segment3 IN VARCHAR2,
98 p_segment4 IN VARCHAR2,
99 p_segment5 IN VARCHAR2,
100 p_segment6 IN VARCHAR2,
101 p_segment7 IN VARCHAR2,
102 p_segment8 IN VARCHAR2,
103 p_segment9 IN VARCHAR2,
104 p_segment10 IN VARCHAR2,
105 p_segment11 IN VARCHAR2,
106 p_segment12 IN VARCHAR2,
107 p_segment13 IN VARCHAR2,
108 p_segment14 IN VARCHAR2,
109 p_segment15 IN VARCHAR2,
110 p_segment16 IN VARCHAR2,
111 p_segment17 IN VARCHAR2,
112 p_segment18 IN VARCHAR2,
113 p_segment19 IN VARCHAR2,
114 p_segment20 IN VARCHAR2,
115 p_segment_definition IN VARCHAR2,
116 x_return_status OUT nocopy VARCHAR2,
117 x_msg_count OUT nocopy NUMBER,
118 x_msg_data OUT nocopy VARCHAR2
119 ) ;
120 PROCEDURE update_main_ps_req
121 ( p_req_id_tbl IN po_tbl_number,
122 p_status IN VARCHAR2,
123 x_return_status OUT nocopy VARCHAR2,
124 x_msg_count OUT nocopy NUMBER,
125 x_msg_data OUT nocopy VARCHAR2
126 );
127
128 PROCEDURE remove_mult_ps_reqs
129 ( p_req_id_tbl IN po_tbl_number,
130 x_return_status OUT nocopy VARCHAR2,
131 x_msg_count OUT nocopy NUMBER,
132 x_msg_data OUT nocopy VARCHAR2
133 );
134
135 PROCEDURE approve_mult_temp_ps_reqs
136 ( p_req_id_tbl IN po_tbl_number,
137 x_return_status OUT nocopy VARCHAR2,
138 x_msg_count OUT nocopy NUMBER,
139 x_msg_data OUT nocopy VARCHAR2
140 );
141 /* Added following functions for P and S ER 7482793 */
142
143 PROCEDURE insert_into_glb_temp
144 (
145 p_validation_type IN VARCHAR2,
146 p_curr_seg_val_id IN NUMBER,
147 p_parent_seg_val_id IN NUMBER,
148 p_table_name IN VARCHAR2,
149 p_where_clause IN VARCHAR2,
150 p_meaning IN VARCHAR2,
151 p_id_column IN VARCHAR2,
152 p_value_column IN VARCHAR2,
153 x_return_status OUT nocopy VARCHAR2,
154 x_msg_count OUT nocopy NUMBER,
155 x_msg_data OUT nocopy VARCHAR2
156 );
157
158 FUNCTION get_segment_value_description(x_segment_value_id NUMBER)
159 RETURN VARCHAR2;
160
161 FUNCTION get_segment_value_code(x_segment_value_id NUMBER)
162 RETURN VARCHAR2;
163
164 FUNCTION get_classid(x_segment_code in varchar2,x_vendor_id in NUMBER)
165 RETURN NUMBER;
166
167 FUNCTION get_requestid(x_segment_code in VARCHAR2,x_mapp_id in NUMBER)
168 RETURN NUMBER;
169
170
171 FUNCTION get_concat_code(x_classification_id in varchar2)
172 RETURN VARCHAR2;
173
174 END pos_product_service_utl_pkg;
[Home] [Help]