1 PACKAGE IBE_Quote_Misc_pvt AUTHID CURRENT_USER AS
2 /* $Header: IBEVQMIS.pls 120.6 2005/10/20 00:38:22 khiremat 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
185 procedure get_load_errors(
186 X_reason_code OUT NOCOPY JTF_VARCHAR2_TABLE_100,
187 p_api_version IN NUMBER := 1.0 ,
188 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
189 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
190 x_return_status OUT NOCOPY VARCHAR2 ,
191 x_msg_count OUT NOCOPY NUMBER ,
192 x_msg_data OUT NOCOPY VARCHAR2 ,
193 P_quote_header_id IN number := FND_API.G_MISS_NUM,
194 P_Load_type IN number := FND_API.G_MISS_NUM,
195 P_quote_number IN number := FND_API.G_MISS_NUM,
196 P_quote_version IN number := FND_API.G_MISS_NUM,
197 P_party_id IN number := FND_API.G_MISS_NUM,
198 P_cust_account_id IN number := FND_API.G_MISS_NUM,
199 P_retrieval_number IN number := FND_API.G_MISS_NUM,
200 P_share_type IN number := -1,
201 p_access_level IN number := 0
202 );
203
204
205 PROCEDURE Update_Config_Item_Lines(
206 x_return_status OUT NOCOPY VARCHAR2,
207 x_msg_count OUT NOCOPY NUMBER ,
208 x_msg_data OUT NOCOPY VARCHAR2,
209 px_qte_line_dtl_tbl IN OUT NOCOPY ASO_QUOTE_PUB.Qte_Line_Dtl_tbl_Type
210 );
211
212 procedure Validate_Items(
213 x_item_exists OUT NOCOPY jtf_number_Table,
214 p_cust_account_id IN NUMBER,
215 p_minisite_id IN NUMBER,
216 p_merchant_item_ids IN JTF_NUMBER_TABLE,
217 p_org_id IN NUMBER
218 );
219
220 PROCEDURE Get_Included_Warranties(
221 p_api_version_number IN NUMBER := 1,
222 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
223 p_commit IN VARCHAR2 := FND_API.G_FALSE,
224 x_return_status OUT NOCOPY VARCHAR2,
225 x_msg_count OUT NOCOPY NUMBER,
226 x_msg_data OUT NOCOPY VARCHAR2,
227 p_organization_id IN NUMBER := NULL,
228 p_product_item_id IN NUMBER,
229 x_service_item_ids OUT NOCOPY JTF_NUMBER_TABLE
230 );
231
232 PROCEDURE Get_Available_Services(
233 p_api_version_number IN NUMBER := 1,
234 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
235 p_commit IN VARCHAR2 := FND_API.G_FALSE,
236 x_return_status OUT NOCOPY VARCHAR2,
237 x_msg_count OUT NOCOPY NUMBER,
238 x_msg_data OUT NOCOPY VARCHAR2,
239 p_product_item_id IN NUMBER,
240 p_customer_id IN NUMBER,
241 p_product_revision IN VARCHAR2,
242 p_request_date IN DATE,
243 x_service_item_ids OUT NOCOPY JTF_NUMBER_TABLE
244 );
245
246 Procedure Duplicate_Line(
247 p_api_version_number IN NUMBER
248 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
249 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
250 ,X_Return_Status OUT NOCOPY VARCHAR2
251 ,X_Msg_Count OUT NOCOPY NUMBER
252 ,X_Msg_Data OUT NOCOPY VARCHAR2
253 ,p_quote_header_id IN NUMBER
254 ,p_qte_line_id IN NUMBER
255 ,x_qte_line_tbl IN OUT NOCOPY ASO_Quote_Pub.qte_line_tbl_type
256 ,x_qte_line_dtl_tbl IN OUT NOCOPY ASO_Quote_Pub.Qte_Line_Dtl_tbl_Type
257 ,x_line_attr_ext_tbl IN OUT NOCOPY ASO_Quote_Pub.Line_Attribs_Ext_tbl_Type
258 ,x_line_rltship_tbl IN OUT NOCOPY ASO_Quote_Pub.Line_Rltship_tbl_Type
259 ,x_ln_price_attributes_tbl IN OUT NOCOPY ASO_Quote_Pub.Price_Attributes_Tbl_Type
260 ,x_ln_price_adj_tbl IN OUT NOCOPY ASO_Quote_Pub.Price_Adj_Tbl_Type
261 );
262
263 FUNCTION getHdrPrcAdjNonPRGTbl (
264 P_Qte_Header_Id IN NUMBER := FND_API.G_MISS_NUM,
265 P_Qte_Line_Id IN NUMBER := FND_API.G_MISS_NUM
266 ) RETURN ASO_QUOTE_PUB.Price_Adj_Tbl_Type;
267
268 Procedure Split_Line(
269 p_api_version_number IN NUMBER
270 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
271 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
272 ,X_Return_Status OUT NOCOPY VARCHAR2
273 ,X_Msg_Count OUT NOCOPY NUMBER
274 ,X_Msg_Data OUT NOCOPY VARCHAR2
275 ,p_quote_header_id IN NUMBER
276 ,p_qte_line_id IN NUMBER
277 ,p_quantities IN jtf_number_table
278 ,p_last_update_date IN OUT NOCOPY DATE
279 ,p_party_id IN NUMBER := FND_API.G_MISS_NUM
280 ,p_cust_account_id IN NUMBER := FND_API.G_MISS_NUM
281 ,p_quote_retrieval_number IN NUMBER := FND_API.G_MISS_NUM
282 ,p_minisite_id IN NUMBER := FND_API.G_MISS_NUM
283 ,p_validate_user IN VARCHAR2 := FND_API.G_FALSE
284 );
285
286 PROCEDURE validate_quote(
287 p_quote_header_id IN NUMBER
288 ,p_save_type IN NUMBER := FND_API.G_MISS_NUM
289 ,x_return_status OUT NOCOPY VARCHAR2
290 ,x_msg_count OUT NOCOPY NUMBER
291 ,x_msg_data OUT NOCOPY VARCHAR2);
292
293 PROCEDURE Validate_User_Update(
294 p_api_version_number IN NUMBER := 1.0
295 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
296 ,p_quote_header_id IN NUMBER
297 ,p_party_id IN NUMBER := FND_API.G_MISS_NUM
298 ,p_cust_account_id IN NUMBER := FND_API.G_MISS_NUM
299 ,p_quote_retrieval_number IN NUMBER := FND_API.G_MISS_NUM
300 ,p_validate_user IN VARCHAR2 := FND_API.G_FALSE
301 ,p_privilege_type_code IN VARCHAR2 := 'F'
302 ,p_save_type IN NUMBER := FND_API.G_MISS_NUM
303 ,p_last_update_date IN DATE := FND_API.G_MISS_DATE
304 ,x_return_status OUT NOCOPY VARCHAR2
305 ,x_msg_count OUT NOCOPY NUMBER
306 ,x_msg_data OUT NOCOPY VARCHAR2);
307
308 PROCEDURE upgrade_recipient_row(
309 p_party_id IN NUMBER,
310 p_cust_account_id IN NUMBER,
311 p_retrieval_number IN NUMBER,
312 p_quote_header_id IN NUMBER,
313 x_valid_flag OUT NOCOPY VARCHAR2);
314
315
316 PROCEDURE Log_Environment_Info (
317 p_quote_header_id in number := null
318 );
319
320 FUNCTION Get_party_name (
321 p_party_id NUMBER,
322 p_party_type VARCHAR2
323 )
324 RETURN VARCHAR2;
325
326 PROCEDURE Add_Attachment(
327 p_api_version_number IN NUMBER
328 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
329 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
330 ,p_category_id IN VARCHAR2
331 ,p_document_description IN VARCHAR2
332 ,p_datatype_id IN VARCHAR2
333 ,p_text IN LONG
334 ,p_file_name IN VARCHAR2
335 ,p_url IN VARCHAR2
336 ,p_function_name IN VARCHAR2 := null
337 ,p_quote_header_id IN NUMBER
338 ,p_media_id IN NUMBER
339 ,p_party_id IN NUMBER := FND_API.G_MISS_NUM
340 ,p_cust_account_id IN NUMBER := FND_API.G_MISS_NUM
341 ,p_retrieval_number IN NUMBER := FND_API.G_MISS_NUM
342 ,p_validate_user IN VARCHAR2 := FND_API.G_FALSE
343 ,p_last_update_date IN DATE := FND_API.G_MISS_DATE
344 ,p_save_type IN NUMBER := FND_API.G_MISS_NUM
345 ,x_last_update_date OUT NOCOPY DATE
346 ,x_return_status OUT NOCOPY VARCHAR2
347 ,x_msg_count OUT NOCOPY NUMBER
348 ,x_msg_data OUT NOCOPY VARCHAR2
349 );
350
351 PROCEDURE Delete_Attachment(
352 p_api_version_number IN NUMBER := 1.0
353 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
354 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
355 ,p_quote_header_id IN NUMBER
356 ,p_quote_attachment_ids IN JTF_VARCHAR2_TABLE_100
357 ,p_last_update_date IN DATE := FND_API.G_MISS_DATE
358 ,p_party_id IN NUMBER := FND_API.G_MISS_NUM
359 ,p_cust_account_id IN NUMBER := FND_API.G_MISS_NUM
360 ,p_retrieval_number IN NUMBER := FND_API.G_MISS_NUM
361 ,x_last_update_date OUT NOCOPY DATE
362 ,x_return_status OUT NOCOPY VARCHAR2
363 ,x_msg_count OUT NOCOPY NUMBER
364 ,x_msg_data OUT NOCOPY VARCHAR2
365 );
366
367 Function get_aso_quote_status (p_quote_header_id NUMBER )RETURN varchar2 ;
368
369 PROCEDURE get_primary_file_id(p_quote_id IN NUMBER,
370 x_file_id OUT NOCOPY NUMBER);
371
372 END IBE_Quote_Misc_pvt;