1 PACKAGE IBE_QUOTE_SAVESHARE_pvt as
2 /* $Header: IBEVQSSS.pls 120.2 2005/07/15 10:03:10 appldev ship $ */
3 -- Start of Comments
4 -- Package name : IBE_QUOTE_SAVESHARE_pvt
5 -- Purpose :
6 -- NOTE :
7
8 -- End of Comments
9
10 -- Default number of records fetch per call
11 G_DEFAULT_NUM_REC_FETCH NUMBER := 30;
12
13 TYPE QUOTE_ACCESS_Rec_Type IS RECORD
14 (
15 QUOTE_SHAREE_ID NUMBER := FND_API.G_MISS_NUM,
16 CREATION_DATE DATE := FND_API.G_MISS_DATE,
17 CREATED_BY NUMBER := FND_API.G_MISS_NUM,
18 LAST_UPDATE_DATE DATE := FND_API.G_MISS_DATE,
19 LAST_UPDATED_BY NUMBER := FND_API.G_MISS_NUM,
20 LAST_UPDATE_LOGIN NUMBER := FND_API.G_MISS_NUM,
21 REQUEST_ID NUMBER := FND_API.G_MISS_NUM,
22 PROGRAM_APPLICATION_ID NUMBER := FND_API.G_MISS_NUM,
23 PROGRAM_ID NUMBER := FND_API.G_MISS_NUM,
24 PROGRAM_UPDATE_DATE DATE := FND_API.G_MISS_DATE,
25 OBJECT_VERSION_NUMBER NUMBER := FND_API.G_MISS_NUM,
26 QUOTE_HEADER_ID NUMBER := FND_API.G_MISS_NUM,
27 QUOTE_SHAREE_NUMBER NUMBER := FND_API.G_MISS_NUM,
28 EMAIL_CONTACT_ADDRESS VARCHAR2(2000) := FND_API.G_MISS_CHAR,
29 UPDATE_PRIVILEGE_TYPE_CODE VARCHAR2(100) := FND_API.G_MISS_CHAR,
30 --MANNAMRA: NEW COLUMNS ADDED: 08/26/2002
31 SECURITY_GROUP_ID NUMBER := FND_API.G_MISS_NUM,
32 PARTY_ID NUMBER := FND_API.G_MISS_NUM,
33 CUST_ACCOUNT_ID NUMBER := FND_API.G_MISS_NUM,
34 START_DATE_ACTIVE DATE := FND_API.G_MISS_DATE,
35 END_DATE_ACTIVE DATE := FND_API.G_MISS_DATE,
36 RECIPIENT_NAME VARCHAR2(2000) := FND_API.G_MISS_CHAR,
37 OPERATION_CODE VARCHAR2(100) := FND_API.G_MISS_CHAR,
38 CONTACT_POINT_ID NUMBER := FND_API.G_MISS_NUM,
39 --END: NEW COLUMNS
40 --MANNAMRA: NEW COLUMNS ADDED: 06/30/2003
41 /*Adding shared_by_partyid to this record structure to identify who actually shared the cart.
42 This is necessary to display the "shared by" name in notifications.Because it's possible to have multiple
43 admins on a shared cart, the recipients on a shared cart could have been added by different admins
44 or the owner.The necessity to match the recipient with appropriate "shared_by" requires us to add the
45 shared_by_partyid to this record structure.We determine the shared_by_partyid by looking at the
46 "created by" column in sh_quote_access tbl*/
47
48 NOTIFY_FLAG VARCHAR2(2) := FND_API.G_TRUE,
49 SHARED_BY_PARTY_ID NUMBER := FND_API.G_MISS_NUM
50 --END: NEW COLUMNS
51 );
52
53 G_MISS_QUOTE_ACCESS_REC QUOTE_ACCESS_Rec_Type;
54 TYPE QUOTE_ACCESS_Tbl_Type IS TABLE OF QUOTE_ACCESS_Rec_Type
55 INDEX BY BINARY_INTEGER;
56 G_MISS_QUOTE_ACCESS_TBL QUOTE_ACCESS_Tbl_Type;
57
58 --MANNAMRA: NEW ACTIVE_CARTS_REC_TYPE ADDED: 08/26/2002
59 TYPE ACTIVE_CARTS_Rec_Type is RECORD
60 (
61 ACTIVE_QUOTE_ID NUMBER :=FND_API.G_MISS_NUM,
62 PROGRAM_APPLICATION_ID NUMBER :=FND_API.G_MISS_NUM,
63 PROGRAM_ID NUMBER :=FND_API.G_MISS_NUM,
64 PROGRAM_UPDATE_DATE DATE :=FND_API.G_MISS_DATE,
65 OBJECT_VERSION_NUMBER NUMBER :=FND_API.G_MISS_NUM,
66 CREATED_BY NUMBER :=FND_API.G_MISS_NUM,
67 CREATION_DATE DATE :=FND_API.G_MISS_DATE,
68 LAST_UPDATED_BY NUMBER :=FND_API.G_MISS_NUM,
69 LAST_UPDATE_DATE DATE :=FND_API.G_MISS_DATE,
70 LAST_UPDATE_LOGIN NUMBER :=FND_API.G_MISS_NUM,
71 QUOTE_HEADER_ID NUMBER :=FND_API.G_MISS_NUM,
72 PARTY_ID NUMBER :=FND_API.G_MISS_NUM,
73 CUST_ACCOUNT_ID NUMBER :=FND_API.G_MISS_NUM,
74 ORG_ID NUMBER :=FND_API.G_MISS_NUM
75 );
76 G_MISS_ACTIVE_CART_REC_TYPE ACTIVE_CARTS_Rec_Type;
77 TYPE ACTIVE_CART_Tbl_Type Is table of
78 ACTIVE_CARTS_Rec_Type INDEX BY BINARY_INTEGER;
79 G_MISS_ACTIVE_CART_tbl_TYPE ACTIVE_CART_tbl_TYPE;
80
81 --END:NEW ACTIVE_CARTS_REC_TYPE
82
83 -- API NAME: SAVEASANDSHARE
84 -- IN PARAMETERS (non-standard)
85 -- 1. need p_original_quote_header_id to get items related information
86 -- like: lines, det_lines, rel_lines, line_ext_attribute
87 -- 2. need p_quote_name, p_quote_source_type, p_party_id, p_cust_account_id
88 -- p_quote_password for create a new quote header
89 -- 3. need p_url, p_sharee_email_address, p_sharee_privilege_type
90 -- for create a new sharee record and send email to sharees
91 -- 4. need p_currency_code, p_price_list_id and
92 -- control_rec
93 -- (p_pricing_request_type, p_header_pricing_event, p_line_pricing_event,
94 -- p_cal_tax_flag, p_cal_freight_charge_flag)
95 -- to decide price related issues
96 -- OUT PARAMETERS (non-standard)
97 -- x_new_quote_Header_id
98
99 PROCEDURE SaveAsAndShare(
100 p_api_version_number IN NUMBER
101 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
102 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
103 ,x_return_status OUT NOCOPY VARCHAR2
104 ,x_msg_count OUT NOCOPY NUMBER
105 ,x_msg_data OUT NOCOPY VARCHAR2
106 ,p_from_quote_header_id IN NUMBER
107 ,p_from_last_update_date IN DATE
108 ,p_copy_only_header IN VARCHAR2 := FND_API.G_FALSE
109 ,p_to_Control_Rec IN ASO_Quote_Pub.Control_Rec_Type
110 := ASO_Quote_Pub.G_Miss_Control_Rec
111 ,p_to_Qte_Header_Rec IN ASO_Quote_Pub.Qte_Header_Rec_Type
112 ,p_to_hd_Shipment_rec IN ASO_Quote_Pub.Shipment_rec_Type
113 := ASO_Quote_Pub.G_MISS_SHIPMENT_rec
114 ,p_url IN VARCHAR2 := FND_API.G_MISS_CHAR
115 ,p_sharee_email_address IN jtf_varchar2_table_2000 := NULL
116 ,p_sharee_privilege_type IN jtf_varchar2_table_100 := NULL
117 ,p_comments IN VARCHAR2 := FND_API.G_MISS_CHAR
118 ,p_quote_retrieval_number IN NUMBER := FND_API.G_MISS_NUM
119 ,p_minisite_id IN NUMBER := FND_API.G_MISS_NUM
120 ,p_validate_user IN VARCHAR2 := FND_API.G_FALSE
121 ,x_to_quote_header_id OUT NOCOPY NUMBER
122 ,x_to_last_update_date OUT NOCOPY DATE
123 );
124
125 -- API NAME: APPENDTOORREPLACEANDSHARE
126 -- Append
127 -- IN PARAMETERS (non-standard)
128 -- 1. need p_original_quote_header_id to get items related information
129 -- like: lines, det_lines, rel_lines, line_ext_attribute
130 -- 2. need p_appendto_quote_header_id
131 -- 3. need p_new_quote_password for new password.
132 -- 4. need p_url, p_sharee_email_address, p_sharee_privilege_type
133 -- for create a new sharee record and send email to sharees
134 -- 5. need p_currency_code, p_price_list_id and
135 -- control_rec
136 -- (p_pricing_request_type, p_header_pricing_event,
137 -- p_line_pricing_event, p_cal_tax_flag, p_cal_freight_charge_flag)
138 -- to decide price related issues
139 -- 6. need p_increaseversion to decide if make a copy of appendto quote or not
140 -- 7. may need p_combinesameitem to decide
141 -- if combine save inventory item to save line or not
142 -- OUT PARAMETERs (non-standard)
143 -- x_new_quote_Header_id
144
145
146 -- Replace
147 -- IN PARAMETERS (non-standard)
148 -- 1. need p_original_quote_header_id to get items related information
149 -- like: lines, det_lines, rel_lines, line_ext_attribute
150 -- 2. need p_appendto_quote_header_id
151 -- 3. need p_new_quote_password for new password.
152 -- 4. need p_url, p_sharee_email_address, p_sharee_privilege_type
153 -- for create a new sharee record and send email to sharees
154 -- 5. need p_currency_code, p_price_list_id and
155 -- control_rec
156 -- (p_pricing_request_type, p_header_pricing_event,
157 -- p_line_pricing_event, p_cal_tax_flag, p_cal_freight_charge_flag)
158 -- to decide price related issues
159 -- 6. need p_increaseversion to decide if make a copy of appendto quote or not
160 -- 7. 8/12/02: added more ASO API parameters to be passed to the IBE_Quote_Save_pvt.save
161 -- OUT PARAMETERs (non-standard)
162 -- x_new_quote_Header_id
163
164 PROCEDURE AppendToReplaceShare(
165 p_api_version_number IN NUMBER ,
166 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
167 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
168 x_return_status OUT NOCOPY VARCHAR2 ,
169 x_msg_count OUT NOCOPY NUMBER ,
170 x_msg_data OUT NOCOPY VARCHAR2 ,
171 p_mode IN VARCHAR2 := 'APPENDTO' ,
172 p_combinesameitem IN VARCHAR2 := FND_API.G_MISS_CHAR,
173 p_increaseversion IN VARCHAR2 := FND_API.G_FALSE ,
174 p_original_quote_header_id IN NUMBER ,
175 p_last_update_date IN DATE := FND_API.G_MISS_DATE,
176 p_rep_app_quote_header_id IN NUMBER := FND_API.G_MISS_NUM ,
177 p_new_quote_password IN VARCHAR2 := FND_API.G_MISS_CHAR,
178 p_url IN VARCHAR2 := FND_API.G_MISS_CHAR,
179 p_sharee_email_address IN jtf_varchar2_table_2000 := NULL,
180 p_sharee_privilege_type IN jtf_varchar2_table_100 := NULL,
181 p_currency_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
182 p_price_list_id IN NUMBER := FND_API.G_MISS_NUM ,
183 p_control_rec IN ASO_QUOTE_PUB.Control_Rec_Type := ASO_QUOTE_PUB.G_MISS_Control_Rec,
184 p_comments IN VARCHAR2 := FND_API.G_MISS_CHAR ,
185 p_rep_app_invTo_partySiteId IN NUMBER := FND_API.G_MISS_NUM ,
186 p_Hd_Price_Attributes_Tbl IN ASO_Quote_Pub.Price_Attributes_Tbl_Type := ASO_Quote_Pub.G_Miss_Price_Attributes_Tbl,
187 p_Hd_Payment_Tbl IN ASO_Quote_Pub.Payment_Tbl_Type := ASO_Quote_Pub.G_MISS_PAYMENT_TBL,
188 p_Hd_Shipment_Tbl IN ASO_Quote_Pub.Shipment_Tbl_Type := ASO_Quote_Pub.G_MISS_SHIPMENT_TBL,
189 p_Hd_Freight_Charge_Tbl IN ASO_Quote_Pub.Freight_Charge_Tbl_Type := ASO_Quote_Pub.G_Miss_Freight_Charge_Tbl,
190 p_Hd_Tax_Detail_Tbl IN ASO_Quote_Pub.TAX_DETAIL_TBL_TYPE := ASO_Quote_Pub.G_Miss_Tax_Detail_Tbl,
191 p_Price_Adjustment_Tbl IN ASO_Quote_Pub.Price_Adj_Tbl_Type := ASO_Quote_Pub.G_Miss_Price_Adj_Tbl,
192 p_Price_Adj_Attr_Tbl IN ASO_Quote_Pub.Price_Adj_Attr_Tbl_Type := ASO_Quote_Pub.G_Miss_PRICE_ADJ_ATTR_Tbl,
193 p_Price_Adj_Rltship_Tbl IN ASO_Quote_Pub.Price_Adj_Rltship_Tbl_Type:= ASO_Quote_Pub.G_Miss_Price_Adj_Rltship_Tbl,
194 p_quote_retrieval_number IN NUMBER := FND_API.G_MISS_NUM,
195 p_party_id IN NUMBER := FND_API.G_MISS_NUM,
196 p_cust_account_id IN NUMBER := FND_API.G_MISS_NUM,
197 p_validate_user IN VARCHAR2 := FND_API.G_FALSE,
198 p_minisite_id IN NUMBER := FND_API.G_MISS_NUM,
199 x_quote_header_id OUT NOCOPY NUMBER ,
200 x_last_update_date OUT NOCOPY DATE
201 );
202
203 -- API Name: ShareQuote
204 -- IN PARAMETERS (non-standard)
205 -- 1. need p_quote_header_id to share with
206 -- 2. need p_url, p_sharee_email_address, p_sharee_privilege_type
207 -- for create a new sharee record and send email to sharees
208 -- OUT PARAMETERs (non-standard)
209 -- no
210 PROCEDURE ShareQuote(
211 p_api_version_number IN NUMBER := 1 ,
212 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
213 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
214 x_return_status OUT NOCOPY VARCHAR2 ,
215 x_msg_count OUT NOCOPY NUMBER ,
216 x_msg_data OUT NOCOPY VARCHAR2 ,
217 p_quote_header_id IN NUMBER ,
218 p_url IN VARCHAR2 ,
219 p_sharee_email_address IN JTF_VARCHAR2_TABLE_2000 := NULL,
220 p_sharee_privilege_type IN JTF_VARCHAR2_TABLE_100 := NULL,
221 p_comments IN VARCHAR2 := FND_API.G_MISS_CHAR
222 );
223
224
225 Procedure ActivateQuote(
226 p_api_version_number IN NUMBER := 1 ,
227 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
228 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
229 x_return_status OUT NOCOPY VARCHAR2 ,
230 x_msg_count OUT NOCOPY NUMBER ,
231 x_msg_data OUT NOCOPY VARCHAR2 ,
232 p_quote_header_id IN NUMBER ,
233 p_last_update_date IN DATE := FND_API.G_MISS_DATE,
234 p_increaseversion IN VARCHAR2 := FND_API.G_FALSE ,
235 x_quote_header_id OUT NOCOPY NUMBER ,
236 x_last_update_date OUT NOCOPY DATE
237 );
238
239
240 PROCEDURE RetrieveShareQuote(
241 p_api_version_number IN NUMBER ,
242 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
243 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
244 x_return_status OUT NOCOPY VARCHAR2 ,
245 x_msg_count OUT NOCOPY NUMBER ,
246 x_msg_data OUT NOCOPY VARCHAR2 ,
247 p_quote_password IN VARCHAR2 := FND_API.G_MISS_CHAR,
248 p_quote_number IN NUMBER ,
249 p_quote_version IN NUMBER := FND_API.G_MISS_NUM ,
250 p_sharee_number IN NUMBER ,
251 p_sharee_party_id IN NUMBER := FND_API.G_MISS_NUM ,
252 p_sharee_cust_account_id IN NUMBER := FND_API.G_MISS_NUM ,
253 p_currency_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
254 p_price_list_id IN NUMBER := FND_API.G_MISS_NUM ,
255 p_control_rec IN ASO_QUOTE_PUB.Control_Rec_Type := ASO_QUOTE_PUB.G_MISS_Control_Rec,
256 p_minisite_id IN NUMBER := FND_API.G_MISS_NUM ,
257 x_quote_header_id OUT NOCOPY NUMBER ,
258 x_last_update_date OUT NOCOPY DATE ,
259 x_privilege_type_code OUT NOCOPY VARCHAR2
260 );
261
262
263 PROCEDURE mergeActiveQuote (
264 p_api_version_number IN NUMBER ,
265 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
266 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
267 x_return_status OUT NOCOPY VARCHAR2 ,
268 x_msg_count OUT NOCOPY NUMBER ,
269 x_msg_data OUT NOCOPY VARCHAR2 ,
270 p_quote_header_id IN NUMBER ,
271 p_last_update_date IN VARCHAR2 := FND_API.G_FALSE ,
272 p_mode IN VARCHAR2 := 'MERGE' ,
273 p_combinesameitem IN VARCHAR2 := FND_API.G_MISS_CHAR,
274 p_party_id IN NUMBER ,
275 p_cust_account_id IN NUMBER ,
276 p_quote_source_code IN VARCHAR2 := 'IStore Account' ,
277 p_minisite_id IN NUMBER := FND_API.G_MISS_NUM ,
278 p_currency_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
279 p_price_list_id IN NUMBER := FND_API.G_MISS_NUM ,
280 p_control_rec IN ASO_QUOTE_PUB.Control_Rec_Type := ASO_QUOTE_PUB.G_MISS_Control_Rec,
281 x_quote_header_id OUT NOCOPY NUMBER ,
282 x_last_update_date OUT NOCOPY DATE ,
283 x_retrieval_number OUT NOCOPY NUMBER
284 );
285
286 PROCEDURE SaveSharee (
287 P_Api_Version_Number IN NUMBER
288 ,p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE
289 ,p_Commit IN VARCHAR2 := FND_API.G_FALSE
290 ,p_Quote_Header_id IN NUMBER
291 ,p_emailAddress IN varchar2
292 ,p_privilegeType IN varchar2
293 ,p_recip_party_id IN NUMBER := FND_API.G_MISS_NUM
294 ,p_recip_cust_account_id IN NUMBER := FND_API.G_MISS_NUM
295 ,x_qte_access_rec OUT NOCOPY IBE_QUOTE_saveshare_pvt.QUOTE_ACCESS_Rec_Type
296 ,X_Return_Status OUT NOCOPY VARCHAR2
297 ,X_Msg_Count OUT NOCOPY NUMBER
298 ,X_Msg_Data OUT NOCOPY VARCHAR2
299 );
300
301 PROCEDURE EmailSharee(
302 p_Api_Version_Number IN NUMBER
306 ,p_Quote_Header_id IN NUMBER
303 ,p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE
304 ,p_Commit IN VARCHAR2 := FND_API.G_FALSE
305
307 ,p_emailAddress IN varchar2
308 ,p_privilegeType IN varchar2
309
310 ,p_url IN varchar2
311 ,p_qte_access_rec IN IBE_QUOTE_saveshare_pvt.QUOTE_ACCESS_Rec_Type
312 ,p_comments IN VARCHAR2 := FND_API.G_MISS_CHAR
313 ,X_Return_Status OUT NOCOPY VARCHAR2
314 ,X_Msg_Count OUT NOCOPY NUMBER
315 ,X_Msg_Data OUT NOCOPY VARCHAR2
316 );
317
318 PROCEDURE GenerateShareeNumber
319 (
320 p_quote_header_id IN NUMBER,
321 p_recip_id IN NUMBER,
322 x_sharee_number OUT NOCOPY NUMBER
323 );
324
325 Procedure Copy_Lines(
326 p_api_version_number IN NUMBER
327 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
328 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
329 ,X_Return_Status OUT NOCOPY VARCHAR2
330 ,X_Msg_Count OUT NOCOPY NUMBER
331 ,X_Msg_Data OUT NOCOPY VARCHAR2
332
333 ,p_from_quote_header_id IN NUMBER
334 ,p_to_quote_header_id IN NUMBER
335 ,p_mode IN VARCHAR2 := FND_API.G_MISS_CHAR
336 ,x_qte_line_tbl OUT NOCOPY ASO_Quote_Pub.qte_line_tbl_type
337 ,x_qte_line_dtl_tbl OUT NOCOPY ASO_Quote_Pub.Qte_Line_Dtl_tbl_Type
338 ,x_line_attr_ext_tbl OUT NOCOPY ASO_Quote_Pub.Line_Attribs_Ext_tbl_Type
339 ,x_line_rltship_tbl OUT NOCOPY ASO_Quote_Pub.Line_Rltship_tbl_Type
340 ,x_ln_price_attributes_tbl OUT NOCOPY ASO_Quote_Pub.Price_Attributes_Tbl_Type
341 ,x_Price_Adjustment_Tbl IN OUT NOCOPY ASO_Quote_Pub.Price_Adj_Tbl_Type
342 ,x_Price_Adj_Rltship_Tbl IN OUT NOCOPY ASO_Quote_Pub.Price_Adj_Rltship_Tbl_Type
343 );
344
345 END IBE_QUOTE_SAVESHARE_pvt;
[Home] [Help]