1 PACKAGE IBE_Quote_Misc_pvt AUTHID CURRENT_USER AS
2 /* $Header: IBEVQMIS.pls 120.6.12020000.2 2012/08/14 06:46:19 amaheshw ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IBE_Quote_Misc_pvt';
5
6 SAVE_NORMAL CONSTANT NUMBER := 0;
7 SAVE_ADDTOCART CONSTANT NUMBER := 1;
8 SAVE_EXPRESSORDER CONSTANT NUMBER := 2;
9 SAVE_PAYMENT_ONLY CONSTANT NUMBER := 3;
10 END_WORKING CONSTANT NUMBER := 4;
11 SALES_ASSISTANCE CONSTANT NUMBER := 5;
12 PLACE_ORDER CONSTANT NUMBER := 6;
13 UPDATE_EXPRESSORDER CONSTANT NUMBER := 7;
14 OP_DELETE_CART CONSTANT NUMBER := 8;
15 OP_DUPLICATE_CART CONSTANT NUMBER := 9;
16
17 FUNCTION get_multi_svc_profile return VARCHAR2;
18
19 FUNCTION is_quote_usable(
20 p_quote_header_id IN NUMBER,
21 p_party_id IN NUMBER,
22 p_cust_account_id IN NUMBER) return varchar2;
23 -- Start of comments
24 -- API name : Get_Active_Quote
25 -- Type : Private.
26 -- Function :
27 -- Pre-reqs : None.
28 -- Parameters :
29 -- Version : Current version x1.0
30 -- Notes : Note text
31 --
32 -- End of comments
33 FUNCTION Get_Active_Quote_ID
34 (
35 p_party_id IN NUMBER,
36 p_cust_account_id IN NUMBER
37 -- p_only_max IN BOOLEAN := TRUE
38 ) RETURN NUMBER;
39
40
41 PROCEDURE Get_Number_Of_Lines
42 (
43 p_party_id IN NUMBER,
44 p_cust_account_id IN NUMBER,
45 x_number_of_lines OUT NOCOPY NUMBER
46 );
47
48
49 --wli
50 FUNCTION get_Quote_Status(
51 p_quote_header_id IN NUMBER
52 ) RETURN VARCHAR2;
53
54 FUNCTION getLineIndexFromLineId(
55 p_quote_line_id IN NUMBER
56 ,p_qte_line_tbl IN ASO_QUOTE_PUB.QTE_LINE_TBL_TYPE
57 ) RETURN NUMBER;
58
59
60 FUNCTION getQuoteLastUpdateDate(
61 p_quote_header_id IN NUMBER
62 ) RETURN DATE;
63
64
65 FUNCTION getLinePrcAttrTbl(
66 p_quote_line_id IN NUMBER
67 ) RETURN ASO_QUOTE_PUB.PRICE_ATTRIBUTES_TBL_TYPE;
68
69 FUNCTION getLineAttrExtTbl(
70 p_quote_line_id IN NUMBER
71 ) RETURN ASO_QUOTE_PUB.Line_Attribs_Ext_Tbl_Type;
72
73
74 FUNCTION getLineDetailTbl(
75 p_quote_line_id IN NUMBER
76 ) RETURN ASO_QUOTE_PUB.Qte_Line_Dtl_Tbl_Type;
77
78
79 FUNCTION getLineRelationshipTbl(
80 p_quote_line_id IN NUMBER
81 ) RETURN ASO_QUOTE_PUB.Line_Rltship_Tbl_Type;
82
83 FUNCTION getLinePrcAdjTbl(
84 p_quote_line_id IN NUMBER
85 ) RETURN ASO_Quote_Pub.Price_Adj_Tbl_Type;
86
87 FUNCTION getHdrPrcAdjTbl(
88 p_quote_hdr_id IN NUMBER
89 ) RETURN ASO_Quote_Pub.Price_Adj_Tbl_Type;
90
91 --Added for PRG bug fix,4094994
92 FUNCTION getAllLinesPrcAdjTbl(
93 p_quote_hdr_id IN NUMBER
94 ) RETURN ASO_Quote_Pub.Price_Adj_Tbl_Type;
95
96
97 FUNCTION getLinePrcAdjRelTbl(
98 p_price_adjustment_id IN NUMBER
99 ) RETURN ASO_Quote_Pub.Price_Adj_Rltship_Tbl_Type;
100
101 FUNCTION getPrcAdjIndexFromPrcAdjId(
102 p_price_adjustment_id IN NUMBER
103 ,p_Price_Adjustment_tbl IN aso_quote_pub.Price_Adj_Tbl_Type
104 ) RETURN NUMBER;
105
106 FUNCTION getLineTbl(
107 p_quote_header_Id IN NUMBER
108 ) RETURN ASO_QUOTE_PUB.QTE_LINE_TBL_TYPE;
109
110
111 FUNCTION getLineRec(
112 p_qte_line_id IN NUMBER
113 ) RETURN ASO_QUOTE_PUB.QTE_LINE_REC_TYPE;
114
115
116 FUNCTION getHeaderRec(
117 p_quote_header_Id IN NUMBER
118 ) RETURN ASO_QUOTE_PUB.QTE_HEADER_REC_TYPE;
119
120
121 FUNCTION getHeaderPaymentTbl(
122 p_quote_header_Id IN NUMBER
123 ) RETURN ASO_QUOTE_PUB.PAYMENT_TBL_TYPE;
124
125
126 FUNCTION getShareePrivilege(
127 p_quote_header_Id IN NUMBER
128 ,p_sharee_number IN NUMBER
129 ) RETURN VARCHAR2;
130
131 FUNCTION getUserType(
132 p_partyId IN Varchar2
133 ) RETURN VARCHAR2;
134
135 PROCEDURE ValidateQuoteLastUpdateDate(
136 p_api_version_number IN NUMBER
137 ,p_quote_header_id IN NUMBER
138 ,p_last_update_date IN DATE
139 ,X_Return_Status OUT NOCOPY VARCHAR2
140 ,X_Msg_Count OUT NOCOPY NUMBER
141 ,X_Msg_Data OUT NOCOPY VARCHAR2
142 );
143
144
145 PROCEDURE getQuoteOwner(
146 p_api_version_number IN NUMBER
147 -- ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
148 -- ,p_commit IN VARCHAR2 := FND_API.G_FALSE
149 ,p_quote_header_Id IN NUMBER
150
151 ,x_party_id OUT NOCOPY NUMBER
152 ,x_cust_account_id OUT NOCOPY NUMBER
153 ,X_Return_Status OUT NOCOPY VARCHAR2
154 ,X_Msg_Count OUT NOCOPY NUMBER
155 ,X_Msg_Data OUT NOCOPY VARCHAR2
156 );
157
158 PROCEDURE Get_Shared_Quote(
159 p_api_version_number IN NUMBER ,
160 p_quote_password IN VARCHAR2 := FND_API.G_MISS_CHAR,
161 p_quote_number IN NUMBER ,
162 p_quote_version IN NUMBER := FND_API.G_MISS_NUM ,
163 x_quote_header_id OUT NOCOPY NUMBER ,
164 x_last_update_date OUT NOCOPY DATE ,
165 x_return_status OUT NOCOPY VARCHAR2 ,
166 x_msg_count OUT NOCOPY NUMBER ,
167 x_msg_data OUT NOCOPY VARCHAR2
168 );
169
170 --- direct entry
171 PROCEDURE Load_Item_IDs(
172 p_api_version IN NUMBER := 1 ,
173 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
174 p_cust_id IN NUMBER ,
175 p_cust_item_number_tbl IN jtf_varchar2_table_100 ,
176 p_organization_id IN NUMBER ,
177 p_minisite_id IN NUMBER ,
178 x_inventory_item_id_tbl OUT NOCOPY jtf_number_table ,
179 x_return_status OUT NOCOPY VARCHAR2 ,
180 x_msg_count OUT NOCOPY NUMBER ,
181 x_msg_data OUT NOCOPY VARCHAR2
182 );
183
184 ---converting ShoppingList, saved cart, Quote to Active shopping cart
185 PROCEDURE Check_Item_IDs(
186 p_api_version IN NUMBER := 1 ,
187 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
188 p_cust_id IN NUMBER ,
189 p_organization_id IN NUMBER ,
190 p_minisite_id IN NUMBER ,
191 x_inventory_item_id_tbl IN OUT NOCOPY jtf_number_table ,
192 x_return_status OUT NOCOPY VARCHAR2 ,
193 x_msg_count OUT NOCOPY NUMBER ,
194 x_msg_data OUT NOCOPY VARCHAR2
195 );
196
197
198 procedure get_load_errors(
199 X_reason_code OUT NOCOPY JTF_VARCHAR2_TABLE_100,
200 p_api_version IN NUMBER := 1.0 ,
201 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
202 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
203 x_return_status OUT NOCOPY VARCHAR2 ,
204 x_msg_count OUT NOCOPY NUMBER ,
205 x_msg_data OUT NOCOPY VARCHAR2 ,
206 P_quote_header_id IN number := FND_API.G_MISS_NUM,
207 P_Load_type IN number := FND_API.G_MISS_NUM,
208 P_quote_number IN number := FND_API.G_MISS_NUM,
209 P_quote_version IN number := FND_API.G_MISS_NUM,
210 P_party_id IN number := FND_API.G_MISS_NUM,
211 P_cust_account_id IN number := FND_API.G_MISS_NUM,
212 P_retrieval_number IN number := FND_API.G_MISS_NUM,
213 P_share_type IN number := -1,
214 p_access_level IN number := 0
215 );
216
217
218 PROCEDURE Update_Config_Item_Lines(
219 x_return_status OUT NOCOPY VARCHAR2,
220 x_msg_count OUT NOCOPY NUMBER ,
221 x_msg_data OUT NOCOPY VARCHAR2,
222 px_qte_line_dtl_tbl IN OUT NOCOPY ASO_QUOTE_PUB.Qte_Line_Dtl_tbl_Type
223 );
224
225 procedure Validate_Items(
226 x_item_exists OUT NOCOPY jtf_number_Table,
227 p_cust_account_id IN NUMBER,
228 p_minisite_id IN NUMBER,
229 p_merchant_item_ids IN JTF_NUMBER_TABLE,
230 p_org_id IN NUMBER
231 );
232
233 PROCEDURE Get_Included_Warranties(
234 p_api_version_number IN NUMBER := 1,
235 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
236 p_commit IN VARCHAR2 := FND_API.G_FALSE,
237 x_return_status OUT NOCOPY VARCHAR2,
238 x_msg_count OUT NOCOPY NUMBER,
239 x_msg_data OUT NOCOPY VARCHAR2,
240 p_organization_id IN NUMBER := NULL,
241 p_product_item_id IN NUMBER,
242 x_service_item_ids OUT NOCOPY JTF_NUMBER_TABLE
243 );
244
245 PROCEDURE Get_Available_Services(
246 p_api_version_number IN NUMBER := 1,
247 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
248 p_commit IN VARCHAR2 := FND_API.G_FALSE,
249 x_return_status OUT NOCOPY VARCHAR2,
250 x_msg_count OUT NOCOPY NUMBER,
251 x_msg_data OUT NOCOPY VARCHAR2,
252 p_product_item_id IN NUMBER,
253 p_customer_id IN NUMBER,
254 p_product_revision IN VARCHAR2,
255 p_request_date IN DATE,
256 x_service_item_ids OUT NOCOPY JTF_NUMBER_TABLE
257 );
258
259 Procedure Duplicate_Line(
260 p_api_version_number IN NUMBER
261 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
262 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
263 ,X_Return_Status OUT NOCOPY VARCHAR2
264 ,X_Msg_Count OUT NOCOPY NUMBER
265 ,X_Msg_Data OUT NOCOPY VARCHAR2
266 ,p_quote_header_id IN NUMBER
267 ,p_qte_line_id IN NUMBER
268 ,x_qte_line_tbl IN OUT NOCOPY ASO_Quote_Pub.qte_line_tbl_type
269 ,x_qte_line_dtl_tbl IN OUT NOCOPY ASO_Quote_Pub.Qte_Line_Dtl_tbl_Type
270 ,x_line_attr_ext_tbl IN OUT NOCOPY ASO_Quote_Pub.Line_Attribs_Ext_tbl_Type
271 ,x_line_rltship_tbl IN OUT NOCOPY ASO_Quote_Pub.Line_Rltship_tbl_Type
272 ,x_ln_price_attributes_tbl IN OUT NOCOPY ASO_Quote_Pub.Price_Attributes_Tbl_Type
273 ,x_ln_price_adj_tbl IN OUT NOCOPY ASO_Quote_Pub.Price_Adj_Tbl_Type
274 );
275
276 FUNCTION getHdrPrcAdjNonPRGTbl (
277 P_Qte_Header_Id IN NUMBER := FND_API.G_MISS_NUM,
278 P_Qte_Line_Id IN NUMBER := FND_API.G_MISS_NUM
279 ) RETURN ASO_QUOTE_PUB.Price_Adj_Tbl_Type;
280
281 Procedure Split_Line(
282 p_api_version_number IN NUMBER
283 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
284 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
285 ,X_Return_Status OUT NOCOPY VARCHAR2
286 ,X_Msg_Count OUT NOCOPY NUMBER
287 ,X_Msg_Data OUT NOCOPY VARCHAR2
288 ,p_quote_header_id IN NUMBER
289 ,p_qte_line_id IN NUMBER
290 ,p_quantities IN jtf_number_table
291 ,p_last_update_date IN OUT NOCOPY DATE
292 ,p_party_id IN NUMBER := FND_API.G_MISS_NUM
293 ,p_cust_account_id IN NUMBER := FND_API.G_MISS_NUM
294 ,p_quote_retrieval_number IN NUMBER := FND_API.G_MISS_NUM
295 ,p_minisite_id IN NUMBER := FND_API.G_MISS_NUM
296 ,p_validate_user IN VARCHAR2 := FND_API.G_FALSE
297 );
298
299 PROCEDURE validate_quote(
300 p_quote_header_id IN NUMBER
301 ,p_save_type IN NUMBER := FND_API.G_MISS_NUM
302 ,x_return_status OUT NOCOPY VARCHAR2
303 ,x_msg_count OUT NOCOPY NUMBER
304 ,x_msg_data OUT NOCOPY VARCHAR2);
305
306 PROCEDURE Validate_User_Update(
307 p_api_version_number IN NUMBER := 1.0
308 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
309 ,p_quote_header_id IN NUMBER
310 ,p_party_id IN NUMBER := FND_API.G_MISS_NUM
311 ,p_cust_account_id IN NUMBER := FND_API.G_MISS_NUM
312 ,p_quote_retrieval_number IN NUMBER := FND_API.G_MISS_NUM
313 ,p_validate_user IN VARCHAR2 := FND_API.G_FALSE
314 ,p_privilege_type_code IN VARCHAR2 := 'F'
315 ,p_save_type IN NUMBER := FND_API.G_MISS_NUM
316 ,p_last_update_date IN DATE := FND_API.G_MISS_DATE
317 ,x_return_status OUT NOCOPY VARCHAR2
318 ,x_msg_count OUT NOCOPY NUMBER
319 ,x_msg_data OUT NOCOPY VARCHAR2);
320
321 PROCEDURE upgrade_recipient_row(
322 p_party_id IN NUMBER,
323 p_cust_account_id IN NUMBER,
324 p_retrieval_number IN NUMBER,
325 p_quote_header_id IN NUMBER,
326 x_valid_flag OUT NOCOPY VARCHAR2);
327
328
329 PROCEDURE Log_Environment_Info (
330 p_quote_header_id in number := null
331 );
332
333 FUNCTION Get_party_name (
334 p_party_id NUMBER,
335 p_party_type VARCHAR2
336 )
337 RETURN VARCHAR2;
338
339 PROCEDURE Add_Attachment(
340 p_api_version_number IN NUMBER
341 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
342 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
343 ,p_category_id IN VARCHAR2
344 ,p_document_description IN VARCHAR2
345 ,p_datatype_id IN VARCHAR2
346 ,p_text IN LONG
347 ,p_file_name IN VARCHAR2
348 ,p_url IN VARCHAR2
349 ,p_function_name IN VARCHAR2 := null
350 ,p_quote_header_id IN NUMBER
351 ,p_media_id IN NUMBER
352 ,p_party_id IN NUMBER := FND_API.G_MISS_NUM
353 ,p_cust_account_id IN NUMBER := FND_API.G_MISS_NUM
354 ,p_retrieval_number IN NUMBER := FND_API.G_MISS_NUM
355 ,p_validate_user IN VARCHAR2 := FND_API.G_FALSE
356 ,p_last_update_date IN DATE := FND_API.G_MISS_DATE
357 ,p_save_type IN NUMBER := FND_API.G_MISS_NUM
358 ,x_last_update_date OUT NOCOPY DATE
359 ,x_return_status OUT NOCOPY VARCHAR2
360 ,x_msg_count OUT NOCOPY NUMBER
361 ,x_msg_data OUT NOCOPY VARCHAR2
362 );
363
364 PROCEDURE Delete_Attachment(
365 p_api_version_number IN NUMBER := 1.0
366 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
367 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
368 ,p_quote_header_id IN NUMBER
369 ,p_quote_attachment_ids IN JTF_VARCHAR2_TABLE_100
370 ,p_last_update_date IN DATE := FND_API.G_MISS_DATE
371 ,p_party_id IN NUMBER := FND_API.G_MISS_NUM
372 ,p_cust_account_id IN NUMBER := FND_API.G_MISS_NUM
373 ,p_retrieval_number IN NUMBER := FND_API.G_MISS_NUM
374 ,x_last_update_date OUT NOCOPY DATE
375 ,x_return_status OUT NOCOPY VARCHAR2
376 ,x_msg_count OUT NOCOPY NUMBER
377 ,x_msg_data OUT NOCOPY VARCHAR2
378 );
379
380 Function get_aso_quote_status (p_quote_header_id NUMBER )RETURN varchar2 ;
381
382 PROCEDURE get_primary_file_id(p_quote_id IN NUMBER,
383 x_file_id OUT NOCOPY NUMBER);
384
385 END IBE_Quote_Misc_pvt;