1 PACKAGE QP_PRICE_BOOK_UTIL AS
2 /* $Header: QPXUPBKS.pls 120.19.12010000.2 2008/10/15 14:07:03 dnema ship $ */
3
4 TYPE NUMBER_TYPE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
5 TYPE VARCHAR30_TYPE IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
6 TYPE VARCHAR_TYPE IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
7 TYPE VARCHAR2000_TYPE IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
8 TYPE FLAG_TYPE IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
9
10 TYPE price_book_message_rec IS RECORD (message_type VARCHAR2(1) := 'E',
11 message_code VARCHAR2(30),
12 message_text VARCHAR2(2000),
13 pb_input_header_id NUMBER := NULL,
14 price_book_header_id NUMBER := NULL,
15 price_book_line_id NUMBER := NULL);
16
17 TYPE price_book_messages_tbl IS TABLE OF price_book_message_rec
18 INDEX BY BINARY_INTEGER;
19
20 TYPE pb_input_lines_tbl IS TABLE OF qp_pb_input_lines%ROWTYPE
21 INDEX BY BINARY_INTEGER;
22
23 PROCEDURE Insert_Price_Book_Messages(
24 p_price_book_messages_tbl IN price_book_messages_tbl);
25
26 PROCEDURE Convert_PB_Input_Value_to_Id (
27 p_pb_input_header_rec IN OUT NOCOPY QP_PRICE_BOOK_PUB.pb_input_header_rec);
28
29 PROCEDURE Default_PB_Input_Criteria (
30 p_pb_input_header_rec IN OUT NOCOPY QP_PRICE_BOOK_PUB.pb_input_header_rec);
31
32 PROCEDURE DEFAULT_CUST_ACCOUNT_ID
33 (
34 p_customer_attr_value IN VARCHAR2,
35 x_cust_account_id OUT NOCOPY VARCHAR2
36 );
37
38 PROCEDURE Validate_PB_Inp_Criteria_Wrap(
39 p_pb_input_header_id IN NUMBER,
40 x_return_status OUT NOCOPY VARCHAR2,
41 x_return_text IN OUT NOCOPY VARCHAR2);
42
43 PROCEDURE Insert_Price_Book_Header(
44 p_pb_input_header_rec IN qp_pb_input_headers_vl%ROWTYPE,
45 x_price_book_header_id OUT NOCOPY NUMBER);
46
47 FUNCTION value_to_meaning( p_code IN VARCHAR2,p_type IN VARCHAR2) RETURN VARCHAR2;
48
49 FUNCTION get_attribute_name(p_context_code in varchar2
50 ,p_attribute_code in varchar2
51 ,p_attribute_type in varchar2) return varchar2;
52
53 FUNCTION get_product_value(p_attribute_code in varchar2
54 ,p_attribute_value_code in varchar2
55 ,p_org_id in varchar2) return varchar2;
56
57 FUNCTION get_customer_value(p_attribute_code in varchar2,
58 p_attribute_value_code in varchar2) return varchar2;
59
60 FUNCTION get_customer_name(p_customer_id in varchar2) return varchar2;
61
62 FUNCTION get_operating_unit(p_orgid in number) return varchar2;
63
64 FUNCTION get_context_name (p_context in varchar2,p_attribute_type in varchar2) return varchar2;
65
66 FUNCTION get_item_description(p_item_number in number,p_pb_header_id in number) return varchar2;
67
68 FUNCTION get_item_category (p_item_category in number ) return varchar2;
69
70 FUNCTION get_item_cat_description (p_item_category in number) return varchar2;
71
72 FUNCTION get_item_number(p_item_number in number, p_pb_header_id in number) return varchar2;
73
74 FUNCTION get_customer_number (p_item_number in number,p_pb_header_id in number) return varchar2;
75
76 FUNCTION get_customer_item_desc (p_item_number in number,p_pb_header_id in number) return varchar2;
77
78 FUNCTION get_attribute_value_common(p_attribute_type in varchar2
79 ,p_context in varchar2
80 ,p_attribute in varchar2
81 ,p_attribute_value in varchar2
82 ,p_comparison_operator in varchar2 default '=') return varchar2;
83
84 FUNCTION get_list_name (p_list_header_id in number)return varchar2;
85
86 FUNCTION get_currency_name (p_currency_code in varchar2) return varchar2;
87
88 PROCEDURE Delete_PriceBook_info(p_price_book_header_id in number) ;
89
90 PROCEDURE Delete_Input_Criteria(p_pb_input_header_id in number);
91
92 -- Added by SNIMMAGA (bug 7034445)
93 FUNCTION Get_Processing_BatchSize RETURN NATURAL;
94
95 PROCEDURE INSERT_PB_TL_RECORDS
96 (
97 p_pb_input_header_id IN VARCHAR2,
98 p_price_book_name IN VARCHAR2,
99 p_pl_agr_bsa_name IN VARCHAR2
100 );
101
102 PROCEDURE CATGI_HEADER_CONVERSIONS
103 (
104 p_org_id IN NUMBER,
105 p_pricing_effective_date IN DATE,
106 p_limit_products_by_code IN VARCHAR2,
107 p_price_based_on_code IN VARCHAR2,
108 p_customer_id IN VARCHAR2,
109 p_item_number IN VARCHAR2,
110 p_item_number_cust IN VARCHAR2,
111 p_item_id IN VARCHAR2,
112 p_item_category_name IN VARCHAR2,
113 p_item_category_id IN VARCHAR2,
114 p_price_list_name IN VARCHAR2,
115 p_price_list_id IN VARCHAR2,
116 p_agreement_name IN VARCHAR2,
117 p_agreement_id IN VARCHAR2,
118 p_bsa_name IN VARCHAR2,
119 p_bsa_id IN VARCHAR2,
120 x_prod_attr_value OUT NOCOPY VARCHAR2,
121 x_pl_agr_bsa_id OUT NOCOPY VARCHAR2,
122 x_pl_agr_bsa_name OUT NOCOPY VARCHAR2,
123 x_return_status OUT NOCOPY VARCHAR2,
124 x_return_text OUT NOCOPY VARCHAR2
125 );
126
127 PROCEDURE GET_CONTEXT_CODE
128 (
129 p_context_name IN VARCHAR2,
130 p_attribute_type IN VARCHAR2,
131 x_context_code OUT NOCOPY VARCHAR2,
132 x_return_status OUT NOCOPY VARCHAR2,
133 x_return_text OUT NOCOPY VARCHAR2
134 );
135
136 PROCEDURE GET_ATTRIBUTE_CODE
137 (
138 p_context_code IN VARCHAR2,
139 p_attribute_name IN VARCHAR2,
140 p_attribute_type IN VARCHAR2,
141 x_attribute_code OUT NOCOPY VARCHAR2,
142 x_return_status OUT NOCOPY VARCHAR2,
143 x_return_text OUT NOCOPY VARCHAR2
144 );
145
146 PROCEDURE GET_ATTRIBUTE_VALUE_CODE
147 (
148 p_context_code IN VARCHAR2,
149 p_attribute_code IN VARCHAR2,
150 p_attribute_value_name IN VARCHAR2,
151 p_attribute_type IN VARCHAR2,
152 x_attribute_value_code OUT NOCOPY VARCHAR2,
153 x_return_status OUT NOCOPY VARCHAR2,
154 x_return_text OUT NOCOPY VARCHAR2
155 );
156
157 PROCEDURE PUBLISH_AND_DELIVER_CP
158 (
159 err_buff OUT NOCOPY VARCHAR2,
160 retcode OUT NOCOPY NUMBER,
161 p_pb_input_header_id NUMBER,
162 p_price_book_id NUMBER,
163 p_servlet_url IN VARCHAR2 DEFAULT NULL
164 );
165
166 PROCEDURE PUBLISH_AND_DELIVER
167 (
168 p_pb_input_header_id IN NUMBER,
169 p_price_book_header_id IN NUMBER,
170 p_servlet_url IN VARCHAR2 DEFAULT NULL,
171 x_return_status OUT NOCOPY VARCHAR2,
172 x_return_status_text OUT NOCOPY VARCHAR2
173 );
174
175 PROCEDURE SEND_SYNC_CATALOG
176 (
177 p_price_book_header_id IN NUMBER,
178 x_return_status OUT NOCOPY VARCHAR2,
179 x_return_status_text OUT NOCOPY VARCHAR2
180 );
181
182 PROCEDURE GENERATE_PUBLISH_PRICE_BOOK_WF
183 (
184 itemtype in VARCHAR2,
185 itemkey in VARCHAR2,
186 actid in NUMBER,
187 funcmode in VARCHAR2,
188 resultout in OUT NOCOPY VARCHAR2
189 );
190
191 PROCEDURE CATSO_SELECTOR
192 ( p_itemtype in varchar2,
193 p_itemkey in varchar2,
194 p_actid in number,
195 p_funcmode in varchar2,
196 p_x_result in out NOCOPY /* file.sql.39 change */ varchar2
197 );
198
199 PROCEDURE SET_XML_CONTEXT
200 (
201 p_user_name IN VARCHAR2,
202 x_return_status OUT NOCOPY VARCHAR2,
203 x_return_text IN OUT NOCOPY VARCHAR2
204 );
205
206 PROCEDURE CATGI_UPDATE_PUBLISH_OPTIONS
207 (
208 p_price_book_name IN VARCHAR2,
209 p_customer_attr_value IN NUMBER,
210 p_effective_date IN DATE,
211 p_price_book_type_code IN VARCHAR2,
212 p_dlv_xml_site_id IN NUMBER,
213 p_generation_time_code IN VARCHAR2,
214 p_gen_schedule_date IN DATE,
215 x_pb_input_header_id OUT NOCOPY NUMBER,
216 x_return_status OUT NOCOPY VARCHAR2,
217 x_return_text IN OUT NOCOPY VARCHAR2
218 );
219
220 PROCEDURE CATGI_POST_INSERT_PROCESSING
221 (
222 p_pb_input_header_id IN NUMBER,
223 x_return_status OUT NOCOPY VARCHAR2,
224 x_return_text IN OUT NOCOPY VARCHAR2
225 );
226
227 PROCEDURE CATGI_UPDATE_CUST_ACCOUNT_ID
228 (
229 p_pb_input_header_id IN NUMBER,
230 p_cust_account_id IN NUMBER,
231 x_return_status OUT NOCOPY VARCHAR2,
232 x_return_text IN OUT NOCOPY VARCHAR2
233 );
234
235 FUNCTION GET_PTE_CODE(p_request_type_code VARCHAR2) RETURN VARCHAR2;
236
237 ---------------------------------------------------------
238
239 END QP_PRICE_BOOK_UTIL;