1 package body IBE_QUOTE_SAVESHARE_V2_PVT as
2 /*$Header: IBEVSS2B.pls 120.2 2005/12/09 01:57:19 makulkar ship $ */
3 l_true VARCHAR2(1) := FND_API.G_TRUE;
4
5 cursor c_check_active_cart(c_party_id number,
6 c_cust_account_id number) is
7 select quote_header_id
8 from IBE_ACTIVE_QUOTES
9 where party_id = c_party_id
10 and cust_account_id = c_cust_account_id
11 and record_type = 'CART';
12
13
14 cursor c_get_cart_name(c_q_hdr_id number,
15 c_party_id number,
16 c_cust_account_id number) is
17 select quote_name
18 from aso_quote_headers_all
19 where party_id = c_party_id
20 and cust_account_id = c_cust_account_id
21 and quote_header_id = c_q_hdr_id;
22 rec_get_cart_name c_get_cart_name%rowtype;
23 rec_check_active_cart c_check_active_cart%rowtype;
24 cursor c_userenv_partyid is
25 select customer_id
26 from FND_USER
27 where user_id = FND_GLOBAL.USER_ID;
28 rec_userenv_partyid c_userenv_partyid%rowtype;
29
30 /*This procedure is used to save the missing party_id and cust_account_id
31 of the recipient before cart activation*/
32 PROCEDURE save_party_id(
33 p_party_id NUMBER,
34 p_cust_account_id NUMBER,
35 p_retrieval_number NUMBER) is
36
37 cursor c_find_party(c_retrieval_num NUMBER) is
38 select quote_sharee_id,quote_header_id,party_id, cust_account_id
39 from ibe_sh_quote_access
40 where quote_sharee_number = c_retrieval_num ;
41
42 cursor c_get_sold_to(c_quote_header_id NUMBER) is
43 select cust_account_id, party_type
44 from aso_quote_headers_all a, hz_parties p
45 where a.party_id = p.party_id
46 and quote_header_id = c_quote_header_id;
47
48 rec_get_sold_to c_get_sold_to%rowtype;
49 rec_find_party c_find_party%rowtype;
50 l_recip_id NUMBER := NULL;
51 l_cust_account_id NUMBER := NULL;
52 l_party_id NUMBER := NULL;
53 l_sold_to_cust NUMBER := NULL;
54 l_quote_header_id NUMBER;
55 l_party_type VARCHAR2(2000);
56
57 BEGIN
58
59 FOR rec_find_party in c_find_party(p_retrieval_number) LOOP
60 l_recip_id := rec_find_party.quote_sharee_id;
61 l_party_id := rec_find_party.party_id;
62 l_cust_account_id := rec_find_party.cust_account_id;
63 l_quote_header_id := rec_find_party.quote_header_id;
64 exit when c_find_party%notfound;
65 END LOOP;
66 IF(l_party_id is null and l_cust_account_id is null) THEN
67 FOR rec_get_sold_to in c_get_sold_to(l_quote_header_id) LOOP
68 l_sold_to_cust := rec_get_sold_to.cust_account_id;
69 l_party_type := rec_get_sold_to.party_type;
70 exit when c_get_sold_to%NOTFOUND;
71 END LOOP;
72 IF(((l_party_type = 'PARTY_RELATIONSHIP')
73 and (p_cust_account_id = l_sold_to_cust))or (l_party_type = 'PERSON')) THEN
74
75 IBE_SH_QUOTE_ACCESS_PKG.update_Row(
76 p_QUOTE_SHAREE_ID => l_recip_id
77 ,p_party_id => p_party_id
78 ,p_cust_account_id => p_cust_account_id);
79 ELSE
80 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
81 IBE_UTIL.DEBUG('Not saving party and cust_account_id because account_ids do not match');
82 END IF;
83 IF FND_Msg_Pub.Check_Msg_Level (FND_Msg_Pub.G_MSG_LVL_ERROR) THEN
84 FND_Message.Set_Name('IBE', 'IBE_SC_ERR_USERACCESS');
85 FND_Msg_Pub.Add;
86 END IF;
87 RAISE FND_API.G_EXC_ERROR;
88 END IF;
89 END IF;
90 END;
91
92 /*The "Main" api; a sort of counterpart to IBE_Quote_Save_pvt.save where this api will be the single point
93 of entry for all of the new save, share, append, and active cart operations
94 Usages:
95 One for each operation_code:
96 "APPEND", "ACTIVATE", "DEACTIVATE", "NAME_CART", "SAVE_RECIPIENTS",
97 "STOP_SHARING", "SAVE_CART_AND_RECIPIENTS", "END_WORKING"*/
98 Procedure save_share_v2 (
99 P_saveshare_control_rec IN SAVESHARE_CONTROL_REC_TYPE
100 := G_MISS_saveshare_control_rec ,
101 P_party_id IN Number ,
102 P_cust_account_id IN Number ,
103 P_retrieval_number IN Number ,
104 P_Quote_header_rec IN ASO_QUOTE_PUB.Qte_Header_Rec_Type ,
105 P_quote_access_tbl IN IBE_QUOTE_SAVESHARE_pvt.QUOTE_ACCESS_Tbl_Type
106 := IBE_QUOTE_SAVESHARE_pvt.G_miss_quote_access_Tbl,
107 P_source_quote_header_id IN NUMBER := FND_API.G_MISS_NUM ,
108 P_source_last_update_date IN Date := FND_API.G_MISS_DATE ,
109 p_minisite_id IN NUMBER ,
110 p_URL IN VARCHAR2 ,
111 p_notes IN VARCHAR2 := FND_API.G_MISS_CHAR ,
112 p_api_version IN NUMBER := 1 ,
113 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
114 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
115 x_return_status OUT NOCOPY VARCHAR2 ,
116 x_msg_count OUT NOCOPY NUMBER ,
117 x_msg_data OUT NOCOPY VARCHAR2 ) is
118
119 L_control_rec aso_quote_pub.control_rec_type;
120 l_quote_header_id NUMBER;
121 l_api_name CONSTANT VARCHAR2(30) := 'SAVESHARE_V2';
122 l_api_version CONSTANT NUMBER := 1.0;
123 l_last_update_date DATE ;
124
125 BEGIN
126 SAVEPOINT SAVESHARE_V2;
127 -- Standard call to check for call compatibility.
128 IF NOT FND_API.Compatible_API_Call ( l_api_version,
129 P_Api_Version,
130 l_api_name,
131 G_PKG_NAME )
132 THEN
133 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
134 END IF;
135 -- Initialize message list IF p_init_msg_list is set to TRUE.
136 IF FND_API.to_Boolean( p_init_msg_list ) THEN
137 FND_MSG_PUB.initialize;
138 END IF;
139
140 -- Initialize API return status to success
141 x_return_status := FND_API.G_RET_STS_SUCCESS;
142 -------------------------------------------------------------------------------------------------------------
143 --API Body start
144 -------------------------------------------------------------------------------------------------------------
145 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
146 IBE_UTIL.DEBUG('IBE_QUOTE_SAVESHARE_V2_PVT.save_share_v2: START');
147 IBE_UTIL.DEBUG('saveshare_control_rec.operation_code is '||p_saveshare_control_rec.operation_code);
148 END IF;
149
150 Validate_share_Update(
151 p_api_version_number => 1.0
152 ,p_init_msg_list => FND_API.G_FALSE
153 ,p_quote_header_rec => P_Quote_header_rec
154 ,p_quote_access_tbl => p_quote_access_tbl
155 ,p_party_id => P_party_id
156 ,p_cust_account_id => P_cust_account_id
157 ,p_retrieval_number => p_retrieval_number
158 ,p_operation_code => p_saveshare_control_rec.operation_code
159 ,x_return_status => x_return_status
160 ,x_msg_count => x_msg_count
161 ,x_msg_data => x_msg_data) ;
162
163 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
164 RAISE FND_API.G_EXC_ERROR;
165 END IF;
166 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
167 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
168 END IF;
169
170 --DBMS_OUTPUT.PUT_LINE('saveshare_control_rec.operation_code is '||p_saveshare_control_rec.operation_code);
171 IF ((p_saveshare_control_rec.operation_code = OP_NAME_CART)
172 OR (p_saveshare_control_rec.operation_code = OP_SAVE_CART_AND_RECIPIENTS)) THEN
173 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
174 IBE_UTIL.DEBUG('calling IBE_Quote_Save_pvt.SAVE to save the quote name');
175 END IF;
176 --DBMS_OUTPUT.PUT_LINE_LINE('calling IBE_Quote_Save_pvt.SAVE to save the quote name');
177 --DBMS_OUTPUT.PUT_LINE_LINE('incoming quote_name :'||P_Quote_header_rec.quote_name);
178 --DBMS_OUTPUT.PUT_LINE_LINE('incoming quote_headeR_id :'||P_Quote_header_rec.quote_header_id);
179 IBE_Quote_Save_pvt.save(
180 p_api_version_number => p_api_version ,
181 p_init_msg_list => fnd_api.g_false ,
182 p_commit => fnd_api.g_false ,
183
184 p_qte_header_rec => P_Quote_header_rec ,
185 p_control_rec => P_saveshare_control_rec.control_rec,
186 x_quote_header_id => l_quote_header_id ,
187 x_last_update_date => l_last_update_date ,
188
189 x_return_status => x_return_status ,
190 x_msg_count => x_msg_count ,
191 x_msg_data => x_msg_data);
192
193 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
194 RAISE FND_API.G_EXC_ERROR;
195 END IF;
196
197 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
198 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
199 END IF;
200 END IF;
201
202 IF ((p_saveshare_control_rec.operation_code = OP_SAVE_RECIPIENTS)
203 OR (p_saveshare_control_rec.operation_code = OP_SAVE_CART_AND_RECIPIENTS)) THEN
204 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
205 IBE_UTIL.DEBUG('Calling save_recipients to save recipient information');
206 END IF;
207 --DBMS_OUTPUT.PUT_LINE('Calling save_recipients to save recipient information ');
208 --dbms_output.put_line('Comments passed : '||p_notes);
209 IF(nvl(p_quote_access_tbl.count,0) > 0) THEN
210 IBE_QUOTE_SAVESHARE_V2_PVT.save_recipients(
211 p_quote_access_tbl => p_quote_access_tbl ,
212 p_quote_header_id => p_quote_header_rec.quote_header_id,
213 p_party_id => p_party_id ,
214 p_cust_account_id => p_cust_account_id ,
215 p_url => p_url ,
216 p_minisite_id => p_minisite_id ,
217 p_notes => p_notes ,
218 p_api_version => p_api_version ,
219 p_init_msg_list => fnd_api.g_false ,
220 p_commit => fnd_api.g_false ,
221 x_return_status => x_return_status ,
222 x_msg_count => x_msg_count ,
223 x_msg_data => x_msg_data);
224
225 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
226 RAISE FND_API.G_EXC_ERROR;
227 END IF;
228
229 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
230 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
231 END IF;
232 END IF; -- IF(nvl(p_quote_access_tbl.count,0)
233 END IF;
234
235
236 IF(p_saveshare_control_rec.operation_code = OP_APPEND) THEN
237 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
238 IBE_UTIL.DEBUG('cALLING APPEND_QUOTE');
239 END IF;
240 IBE_QUOTE_SAVESHARE_V2_PVT.APPEND_QUOTE(
241 P_source_quote_header_id => P_source_quote_header_id,
242 P_source_last_update_date => P_source_last_update_date,
243 P_target_header_rec => P_Quote_header_rec,
244 P_control_rec => P_saveshare_control_rec.control_rec,
245 P_delete_source_cart => P_saveshare_control_rec.delete_source_cart,
246 P_combinesameitem => P_saveshare_control_rec.combinesameitem,
247 P_minisite_id => p_minisite_id,
248 p_api_version => 1,
249 p_init_msg_list => FND_API.G_FALSE,
250 p_commit => FND_API.G_FALSE,
251 x_return_status => x_return_status,
252 x_msg_count => x_msg_count,
253 x_msg_data => x_msg_data);
254 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
255 RAISE FND_API.G_EXC_ERROR;
256 END IF;
257
258 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
259 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
260 END IF;
261
262
263 ELSIF(p_saveshare_control_rec.operation_code = OP_ACTIVATE_QUOTE) THEN
264 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
265 IBE_UTIL.DEBUG('OPERATION_CODE IS: '||p_saveshare_control_rec.operation_code||'CALLING ACTIVATE_QUOTE');
266 END IF;
267 --DBMS_OUTPUT.PUT_LINE_LINE('OPERATION_CODE IS: '||p_saveshare_control_rec.operation_code||'CALLING ACTIVATE_QUOTE');
268 IBE_QUOTE_SAVESHARE_V2_PVT.ACTIVATE_QUOTE(
269 P_Quote_header_rec => P_Quote_header_rec ,
270 P_Party_id => P_party_id ,
271 P_Cust_account_id => P_cust_account_id ,
272 P_control_rec => P_saveshare_control_rec.control_rec,
273 p_retrieval_number => p_retrieval_number ,
274 P_api_version => P_api_version ,
275 P_init_msg_list => FND_API.G_FALSE ,
276 P_commit => FND_API.G_FALSE ,
277 x_return_status => x_return_status ,
278 x_msg_count => x_msg_count ,
279 x_msg_data => x_msg_data);
280
281 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
282 RAISE FND_API.G_EXC_ERROR;
283 END IF;
284
285 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
286 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
287 END IF;
288 --DBMS_OUTPUT.PUT_LINE_LINE('Finished calling ACTIVATE_QUOTE ');
289 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
290 IBE_UTIL.DEBUG('Finished calling ACTIVATE_QUOTE');
291 END IF;
292
293 ELSIF(p_saveshare_control_rec.operation_code = OP_END_WORKING) THEN
294 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
295 IBE_UTIL.DEBUG(' Calling IBE_QUOTE_SAVE_SHARE_V2_PVT.END_WORKING');
296 END IF;
297 IBE_QUOTE_SAVESHARE_V2_PVT.END_WORKING(
298 P_Quote_access_tbl => p_quote_access_tbl ,
299 p_quote_header_id => p_quote_header_rec.quote_header_id,
300 p_party_id => p_party_id ,
301 p_cust_account_id => p_cust_account_id ,
302 p_retrieval_number => p_retrieval_number ,
303 P_URL => p_url ,
304 P_minisite_id => p_minisite_id ,
305 p_notes => p_notes ,
306 p_api_version => p_api_version ,
307 p_init_msg_list => fnd_api.g_false ,
308 p_commit => fnd_api.g_false ,
309 x_return_status => x_return_status ,
310 x_msg_count => x_msg_count ,
311 x_msg_data => x_msg_data );
312 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
313 RAISE FND_API.G_EXC_ERROR;
314 END IF;
315
316 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
317 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
318 END IF;
319
320 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
321 IBE_UTIL.DEBUG('Done calling IBE_QUOTE_SAVE_SHARE_V2_PVT.END_WORKING');
322 END IF;
323
324 ELSIF(p_saveshare_control_rec.operation_code = OP_STOP_SHARING) THEN
325 IBE_QUOTE_SAVESHARE_V2_PVT.STOP_SHARING (
326 p_quote_header_id => P_Quote_header_rec.quote_header_id ,
327 P_minisite_id => p_minisite_id ,
328 p_notes => p_notes ,
329 p_quote_access_tbl => p_quote_access_tbl ,
330 p_api_version => p_api_version ,
331 p_init_msg_list => fnd_api.g_false ,
332 p_commit => fnd_api.g_false ,
333 x_return_status => x_return_status ,
334 x_msg_count => x_msg_count ,
335 x_msg_data => x_msg_data );
336
337 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
338 RAISE FND_API.G_EXC_ERROR;
339 END IF;
340
341 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
342 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
343 END IF;
344 END IF;--Check for p_saveshare_control_rec.operation_code
345 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
346 IBE_UTIL.DEBUG('P_saveshare_control_rec.deactivate_cart: '||P_saveshare_control_rec.deactivate_cart);
347 END IF;
348 IF ((P_saveshare_control_rec.deactivate_cart = FND_API.G_TRUE)
349 OR (p_saveshare_control_rec.operation_code = OP_DEACTIVATE)) THEN
350
351 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
352 IBE_UTIL.DEBUG('Calling deactivate API');
353 END IF;
354 --DBMS_OUTPUT.PUT_LINE_LINE('Calling deactivate API');
355 IBE_QUOTE_SAVESHARE_V2_PVT.DEACTIVATE_QUOTE (
356 P_Quote_header_id => P_Quote_header_rec.quote_header_id ,
357 P_Party_id => p_party_id ,
358 P_Cust_account_id => p_cust_account_id ,
359 P_minisite_id => p_minisite_id ,
360 p_api_version => p_api_version ,
361 p_init_msg_list => fnd_api.g_false ,
362 p_commit => fnd_api.g_false ,
363 x_return_status => x_return_status ,
364 x_msg_count => x_msg_count ,
365 x_msg_data => x_msg_data );
366 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
367 RAISE FND_API.G_EXC_ERROR;
368 END IF;
369
370 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
371 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
372 END IF;
373 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
374 IBE_UTIL.DEBUG('Deactivate owner cart after sharing:Done');
375 END IF;
376 END IF; --op_code = name_cart with deactivate
377
378 IF (p_saveshare_control_rec.operation_code = OP_DELETE_CART) THEN
379 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
380 IBE_UTIL.DEBUG('Save_share_v2:Op_code in p_saveshare_control_rec.operation_code: '||p_saveshare_control_rec.operation_code);
381 IBE_UTIL.DEBUG('Save_share_v2:Ready to call IBE_QUOTE_SAVE_PVT.Delete on: '||P_Quote_header_rec.quote_header_id);
382 IBE_UTIL.DEBUG('save_share_v2:Expunge flag passed to delete_cart is: '||p_saveshare_control_rec.delete_source_cart);
383 IBE_UTIL.DEBUG('save_share_v2:P_Quote_header_rec.last_update_date: '||P_Quote_header_rec.last_update_date);
384 END IF;
385 --dbms_output.put_line('Save_share_v2:Op_code in p_saveshare_control_rec.operation_code: '||p_saveshare_control_rec.operation_code);
386 --dbms_output.put_line('Save_share_v2:Ready to call IBE_QUOTE_SAVE_PVT.Delete on: '||P_Quote_header_rec.quote_header_id);
387
388 IBE_QUOTE_SAVE_PVT.Delete(
389 p_api_version_number => p_api_version
390 ,p_init_msg_list => fnd_api.g_false
391 ,p_commit => fnd_api.g_false
392 ,x_return_status => x_return_status
393 ,x_msg_count => x_msg_count
394 ,x_msg_data => x_msg_data
395 ,p_quote_header_id => P_Quote_header_rec.quote_header_id
396 ,p_expunge_flag => FND_API.G_FALSE
397 ,p_minisite_id => p_minisite_id
398 ,p_last_update_date => P_Quote_header_rec.last_update_date
399 ,p_Quote_access_tbl => p_quote_access_tbl
400 ,p_notes => p_notes
401 ,p_initiator_party_id => p_party_id
402 ,p_initiator_account_id => p_cust_account_id );
403 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
404 RAISE FND_API.G_EXC_ERROR;
405 END IF;
406
407 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
408 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
409 END IF;
410 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
411 IBE_UTIL.DEBUG('Delete owner cart :Done');
412 END IF;
413 END IF; --op_code = OP_DELETE_CART
414
415 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
416 IBE_UTIL.DEBUG('IBE_QUOTE_SAVESHARE_V2_PVT.save_share_v2: END');
417 END IF;
418 EXCEPTION
419 WHEN FND_API.G_EXC_ERROR THEN
420 ROLLBACK TO SAVESHARE_V2;
421 x_return_status := FND_API.G_RET_STS_ERROR;
422 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
423 p_count => x_msg_count ,
424 p_data => x_msg_data);
425 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
426 IBE_Util.Debug('Expected Error in IBE_QUOTE_SAVESHARE_V2_PVT.save_share_v2()');
427 END IF;
428
429 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
430 ROLLBACK TO SAVESHARE_V2;
431 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
432 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
433 p_count => x_msg_count ,
434 p_data => x_msg_data);
435 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
436 IBE_Util.Debug('Unexpected Error in IBE_QUOTE_SAVESHARE_V2_PVT.save_share_v2()');
437 END IF;
438
439 WHEN OTHERS THEN
440 ROLLBACK TO SAVESHARE_V2;
441 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
442 IF FND_Msg_Pub.Check_Msg_Level(FND_Msg_Pub.G_MSG_LVL_UNEXP_ERROR) THEN
443 FND_Msg_Pub.Add_Exc_Msg(G_PKG_NAME,
444 l_api_name);
445 END IF;
446 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
447 p_count => x_msg_count ,
448 p_data => x_msg_data);
449 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
450 IBE_Util.Debug('Unknown error IBE_QUOTE_SAVESHARE_V2_PVT.save_share_v2()');
451 END IF;
452
453
454 END;
455
456 /*To handle creating, updating, and removing of recipients.
457 *Usages:
458 -Add new set of recipients in first visit to Share Cart Details page
459 -Handling all possible updates to recipient info from Share Cart Details page in subsequent
460 updates from "Add/Modify Recipients" - this includes adding recipients, changing recipient
461 info (access level), and removing recipients.
462 -"End Working" button from recipient pages
463 -"Remove" button on the "List of Saved Carts" page for recipients.
464 -Possibly other api's to do single removes, adds, or updates. */
465
466 Procedure save_recipients (
467 P_Quote_access_tbl IN IBE_QUOTE_SAVESHARE_pvt.QUOTE_ACCESS_TBL_TYPE
468 := IBE_QUOTE_SAVESHARE_pvt.G_miss_quote_access_Tbl ,
469 P_Quote_header_id IN Number ,
470 P_Party_id IN Number := FND_API.G_MISS_NUM ,
471 P_Cust_account_id IN Number := FND_API.G_MISS_NUM ,
472 P_URL IN Varchar2 := FND_API.G_MISS_CHAR ,
473 P_minisite_id IN Number := FND_API.G_MISS_NUM ,
474 p_send_notif IN Varchar2 := FND_API.G_TRUE ,
475 p_notes IN Varchar2 := FND_API.G_MISS_CHAR ,
476 p_api_version IN Number := 1 ,
477 p_init_msg_list IN varchar2 := FND_API.G_TRUE ,
478 p_commit IN Varchar2 := FND_API.G_FALSE ,
479 x_return_status OUT NOCOPY Varchar2 ,
480 x_msg_count OUT NOCOPY Number ,
481 x_msg_data OUT NOCOPY Varchar2 ) is
482
483 cursor c_check_recip_row(c_quote_hdr_id number,
484 c_party_id number,
485 c_cust_account_id number) is
486 select quote_header_id, quote_sharee_id, quote_sharee_number
487 from ibe_sh_quote_access
488 where party_id = c_party_id
489 and cust_account_id = c_cust_account_id
490 and quote_header_id = c_quote_hdr_id
491 and nvl(end_date_active, sysdate+1) > sysdate;
492
493 cursor c_recip_details(c_recip_id NUMBER,
494 c_quote_header_id NUMBER,
495 c_party_id NUMBER) is
496 select nvl(update_privilege_type_code, fnd_api.g_miss_char) access_level,
497 party_id,
498 cust_account_id,
499 contact_point_id,
500 quote_header_id,
501 quote_sharee_number,
502 quote_sharee_id,
503 fnd.customer_id shared_by_party_id
504 from ibe_sh_quote_access ibe, fnd_user fnd
505 where ibe.created_by = fnd.user_id
506 and (quote_sharee_id = c_recip_id
507 or (quote_header_id = c_quote_header_id
508 and party_id = c_party_id));
509
510 cursor c_get_owner_ids(c_quote_id number) is
511 select party_id, cust_account_id
512 from ASO_QUOTE_HEADERS_ALL
513 where quote_header_id = c_quote_id;
514
515 cursor c_get_created_recip(c_quote_header_id number,
516 c_party_id number,
517 c_created_by number) is
518 select quote_sharee_id
519 from ibe_sh_quote_access
520 where party_id = c_party_id
521 and quote_header_id = c_quote_header_id
522 and created_by = c_created_by;
523
524 rec_check_recip_row c_check_recip_row%rowtype;
525 rec_recip_details c_recip_details%rowtype;
526 rec_get_owner_ids c_get_owner_ids%rowtype;
527 rec_get_created_recip c_get_created_recip%rowtype;
528
529 l_url VARCHAR2(2000);
530 l_quote_present NUMBER := NULL;
531 l_retrieval_number NUMBER;
532 l_quote_recip_id NUMBER;
533 t_counter NUMBER := 1;
534
535 l_old_access_level VARCHAR2(1);
536 l_contact_point_id NUMBER;
537 l_api_name CONSTANT VARCHAR2(30) := 'SAVERECIPIENTS_V2';
538 l_api_version CONSTANT NUMBER := 1.0;
539 l_call_save_contact_point VARCHAR2(1) := FND_API.G_FALSE;
540 l_quote_access_rec IBE_QUOTE_SAVESHARE_pvt.QUOTE_ACCESS_rec_TYPE;
541 l_quote_access_rec_owr IBE_QUOTE_SAVESHARE_pvt.QUOTE_ACCESS_rec_TYPE;
542 l_call_insert_handler VARCHAR2(1) := FND_API.G_FALSE;
543 l_recip_party_id NUMBER;
544 l_recip_cust_account_id NUMBER;
545 l_sharing_partyid NUMBER;
546 l_owner_partyid NUMBER;
547 l_owner_accountid NUMBER;
548 l_created_by NUMBER;
549
550 BEGIN
551
552 SAVEPOINT SAVERECIPIENTS_V2;
553 -- Standard call to check for call compatibility.
554 IF NOT FND_API.Compatible_API_Call ( l_api_version,
555 P_Api_Version,
556 l_api_name,
557 G_PKG_NAME )
558 THEN
559 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
560 END IF;
561 -- Initialize message list IF p_init_msg_list is set to TRUE.
562 IF FND_API.to_Boolean( p_init_msg_list ) THEN
563 FND_MSG_PUB.initialize;
564 END IF;
565
566 -- Initialize API return status to success
567 x_return_status := FND_API.G_RET_STS_SUCCESS;
568 -------------------------------------------------------------------------------------------------------------
569 --API Body start
570 -------------------------------------------------------------------------------------------------------------
571
572
573 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
574 IBE_UTIL.DEBUG('IBE_QUOTE_SAVESHARE_V2_PVT.save_recipients: START');
575 END IF;
576 /*Obtain the party id of the user performing the action on the UI from the env*/
577 FOR rec_userenv_partyid in c_userenv_partyid LOOP
578 l_sharing_partyid := rec_userenv_partyid.customer_id;
579 EXIT when c_userenv_partyid%notfound;
580 END LOOP;
581
582 /*obtain the owner party_id of the cart/quote being dealt with*/
583 FOR rec_get_owner_ids in c_get_owner_ids(p_quote_header_id) LOOP
584 l_owner_partyid := rec_get_owner_ids.party_id;
585 l_owner_accountid := rec_get_owner_ids.cust_account_id;
586 EXIT when c_get_owner_ids%notfound;
587 END LOOP;
588 --Loop around the input quote_access table
589 FOR counter IN 1..P_quote_access_tbl.COUNT LOOP
590 l_call_insert_handler := FND_API.G_FALSE;
591 l_call_save_contact_point := FND_API.G_FALSE;
592 l_quote_access_rec := P_Quote_access_tbl(counter);
593
594 /*Obtaining the recipient details here when there is a recipient_id or a combnation of party_id
595 and quote_hdr_id available in the input quote access record.
596 This query will tell us if there is already a recipiet record avialable to re-use.*/
597 --Old_access_level will be relevant only when the Op-code in P_Quote_access_tbl(counter) is UPDATE
598 --Old_access_level will be passed to notify_access_change API
599 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
600 IBE_UTIL.DEBUG('Incoming Quote_sharee_id = '||P_Quote_access_tbl(counter).quote_sharee_id);
601 IBE_UTIL.DEBUG('Incoming Quote_header_id = '||P_Quote_access_tbl(counter).quote_header_id);
602 IBE_UTIL.DEBUG('Incoming party_id = '||P_Quote_access_tbl(counter).party_id);
603 END IF;
604
605 IF(((P_Quote_access_tbl(counter).quote_sharee_id is not null) and
606 (P_Quote_access_tbl(counter).quote_sharee_id <> FND_API.G_MISS_NUM))or
607 ((p_quote_access_tbl(counter).party_id is not null) and
608 (p_quote_access_tbl(counter).party_id <> FND_API.G_MISS_NUM))) THEN
609 FOR rec_recip_details in c_recip_details(P_Quote_access_tbl(counter).quote_sharee_id,
610 P_Quote_access_tbl(counter).quote_header_id,
611 P_Quote_access_tbl(counter).party_id) LOOP
612
613 l_old_access_level := rec_recip_details.access_level;
614 l_quote_access_rec.contact_point_id := rec_recip_details.contact_point_id;
615 l_quote_access_rec.quote_header_id := rec_recip_details.quote_header_id;
616 l_quote_access_rec.quote_sharee_number := rec_recip_details.quote_sharee_number;
617
618 --Use the incoming quote sharee id
619 IF ((P_Quote_access_tbl(counter).quote_sharee_id is null) OR
620 (P_Quote_access_tbl(counter).quote_sharee_id = FND_API.G_MISS_NUM)) THEN
621 l_quote_access_rec.quote_sharee_id := rec_recip_details.quote_sharee_id;
622 ELSE
623 l_quote_access_rec.quote_sharee_id := P_Quote_access_tbl(counter).quote_sharee_id;
624 END IF;
625
626 l_quote_access_rec.shared_by_party_id := rec_recip_details.shared_by_party_id;
627 /*If no party_id and cust_account_id are passed in then query them from the database
628 using the quote sharee id*/
629 IF (((p_quote_access_tbl(counter).party_id is null ) OR
630 (p_quote_access_tbl(counter).party_id = FND_API.G_MISS_NUM))AND
631 ((p_quote_access_tbl(counter).cust_account_id is null ) OR
632 (p_quote_access_tbl(counter).cust_account_id = FND_API.G_MISS_NUM))) THEN
633
634 l_quote_access_rec.party_id := rec_recip_details.party_id;
635 l_quote_access_rec.cust_account_id := rec_recip_details.cust_account_id;
636
637 ELSE
638
639 l_quote_access_rec.party_id := p_quote_access_tbl(counter).party_id;
640 l_quote_access_rec.cust_account_id := p_quote_access_tbl(counter).cust_account_id;
641
642 END IF;
643 EXIT WHEN c_recip_details%notfound;
644 END LOOP;
645 ELSE
646 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
647 IBE_UTIL.DEBUG('Cannot query recipient details ,recipient_id is null or g_miss');
648 IBE_UTIL.DEBUG('Saving recipients for first time?!');
649 END IF;
650 END IF; --if p_quote_access_tbl(counter).quote_sharee_id is not null and g_miss
651
652 /*Processing the recipients in the input access table according to the op-codes
653 corresponding to each recipient record*/
654 --Start with 'CREATE' op_code
655 IF( p_quote_access_tbl(counter).operation_code = 'CREATE') THEN
656
657 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
658 IBE_UTIL.DEBUG('Operation code in quote_access_tbl is '||p_quote_access_tbl(counter).operation_code);
659 END IF;
660 /*Open this cursor to check if there are any inactive(end-dated) rows existing in sh_quote_access
661 for this party, cust and quote combination */
662 /*If there is an end-dated record present for the given combination of party, cust_account,
663 quote_hdr in quote_access then use this record, else insert a new record*/
664 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
665 IBE_UTIL.DEBUG('Save_recipients: Opening c_get_created_recip');
666 IBE_UTIL.DEBUG('Save_recipients: FND_GLOBAL.USER_ID: '||FND_GLOBAL.USER_ID);
667 END IF;
668 l_quote_access_rec.quote_sharee_id := FND_API.G_MISS_NUM;
669 FOR rec_get_created_recip in c_get_created_recip(p_quote_access_tbl(counter).quote_header_id,
670 p_quote_access_tbl(counter).party_id,
671 FND_GLOBAL.USER_ID) LOOP
672 l_quote_access_rec.quote_sharee_id := rec_get_created_recip.quote_sharee_id;
673 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
674 IBE_UTIL.DEBUG('Save_recipients: rec_get_created_recip found quote_sharee_id: '||l_quote_access_rec.quote_sharee_id);
675 END IF;
676
677 EXIT when c_get_created_recip%NOTFOUND;
678 END LOOP;
679 IF (l_quote_access_rec.quote_sharee_id is not null AND
680 l_quote_access_rec.quote_sharee_id <> FND_API.G_MISS_NUM) THEN
681 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
682 IBE_UTIL.DEBUG('Record found for the recipient in quote_access table, need to update it');
683 END IF;
684
685 IBE_SH_QUOTE_ACCESS_PKG.update_Row(
686 p_quote_sharee_id => l_quote_access_rec.quote_sharee_id
687 ,p_quote_header_id => p_quote_header_id
688 ,p_party_id => p_quote_access_tbl(counter).party_id
689 ,p_cust_account_id => p_quote_access_tbl(counter).cust_account_id
690 ,p_update_privilege_type_code => p_quote_access_tbl(counter).update_privilege_type_code
691 ,p_contact_point_id => p_quote_access_tbl(counter).contact_point_id
692 ,p_start_date_active => sysdate
693 ,p_end_date_active => null);
694 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
695 IBE_UTIL.DEBUG('Finsihed calling update handler of quote_access table');
696 END IF;
697 --Calling save_contact_point for the updated recipient record just in case the
698 --contact point has changed
699 IF(p_quote_access_tbl(counter).contact_point_id is null and
700 p_quote_access_tbl(counter).EMAIL_CONTACT_ADDRESS is not null) THEN
701 l_call_save_contact_point := FND_API.G_TRUE;
702 END IF;
703
704 ELSE --Need to create a new record
705 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
706 IBE_UTIL.DEBUG('Record not found for this recipient in quote_access table');
707 END IF;
708 --Obtain the recipient number here
709 select IBE_SH_QUOTE_ACCESS_s1.nextval into l_quote_recip_id
710 from dual;
711 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
712 IBE_UTIL.DEBUG('sharee id from the sequence is '||l_quote_recip_id );
713 END IF;
714
715 IBE_QUOTE_SAVESHARE_pvt.GenerateShareeNumber(
716 p_quote_header_id => p_quote_header_id,
717 p_recip_id => l_quote_recip_id,
718 x_sharee_number => l_quote_access_rec.quote_sharee_number);
719
720 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
721 IBE_UTIL.DEBUG('sharee number is '||l_quote_access_rec.quote_sharee_number );
722 END IF;
723
724 --Deal with saving the contact point here
725 /*A contact point is created when there is an input e-mail address and a null
726 input contact point id*/
727 IF (p_quote_access_tbl(counter).EMAIL_CONTACT_ADDRESS is not null
728 and p_quote_access_tbl(counter).EMAIL_CONTACT_ADDRESS <> fnd_api.g_miss_char
729 and (p_quote_access_tbl(counter).contact_point_id is null or
730 p_quote_access_tbl(counter).contact_point_id = FND_API.G_MISS_NUM)) THEN
731 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
732 IBE_UTIL.DEBUG('saving contact point for the current recipient record');
733 END IF;
734 l_call_save_contact_point := FND_API.G_TRUE;
735
736 --This is the case when the incoming B2B record has a valid contact_point_id
737 ELSIF((p_quote_access_tbl(counter).contact_point_id is not null )
738 and (p_quote_access_tbl(counter).contact_point_id <> FND_API.G_MISS_NUM)) THEN
739 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
740 IBE_UTIL.DEBUG('Contact point Id found in the input quote access record');
741 END IF;
742 l_contact_point_id := p_quote_access_tbl(counter).contact_point_id;
743
744 END IF;--save_contact_point
745 l_call_insert_handler := FND_API.G_TRUE;
746
747 END IF;-- existing recip or new recip
748
749 --Constructing the URL here
750 l_url := p_url||l_quote_access_rec.quote_sharee_number;
751 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
752 IBE_UTIL.DEBUG('URL of shared cart is: '||l_url);
753 END IF;
754
755 --Saving the contact_point here
756 IF l_call_save_contact_point = FND_API.G_TRUE THEN
757 save_contact_point
758 ( p_api_version_number => P_Api_Version
759 ,p_init_msg_list => FND_API.G_FALSE
760 ,p_commit => FND_API.G_FALSE
761 ,P_EMAIL => p_quote_access_tbl(counter).EMAIL_CONTACT_ADDRESS
762 ,p_owner_table_id => l_quote_recip_id
763 ,p_mode => 'EMAIL'
764 ,x_contact_point_id => l_contact_point_id
765 ,x_return_status => x_return_status
766 ,x_msg_count => x_msg_count
767 ,x_msg_data => x_msg_data );
768 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
769 RAISE FND_API.G_EXC_ERROR;
770 END IF;
771 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
772 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
773 END IF;
774
775 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
776 IBE_UTIL.DEBUG('Done saving contact point: '||l_contact_point_id);
777 END IF;
778 l_quote_access_rec.contact_point_id := l_contact_point_id;
779
780 END IF;-- l_call_save_contact_point
781
782 IF (l_call_insert_handler = FND_API.G_TRUE) THEN
783 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
784 IBE_UTIL.DEBUG('calling ins handler ');
785 IBE_UTIL.DEBUG('p_quote_access_tbl(counter).recipient_name: '||p_quote_access_tbl(counter).recipient_name);
786 END IF;
787 IBE_SH_QUOTE_ACCESS_PKG.Insert_Row(
788 p_quote_sharee_id => l_quote_recip_id,
789 p_quote_header_id => p_quote_header_id,
790 p_quote_sharee_number => l_quote_access_rec.quote_sharee_number,
791 p_update_privilege_type_code => p_quote_access_tbl(counter).update_privilege_type_code,
792 p_party_id => p_quote_access_tbl(counter).party_id,
793 p_cust_account_id => p_quote_access_tbl(counter).cust_account_id,
794 p_recipient_name => p_quote_access_tbl(counter).recipient_name,
795 p_contact_point_id => l_contact_point_id);
796 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
797 IBE_UTIL.DEBUG('finished inserting ');
798 END IF;
799 END IF;
800 l_quote_access_rec.quote_sharee_id := l_quote_recip_id;
801 --calling the new shared cart notification API here
802
803 IF ((p_send_notif = FND_API.G_TRUE) AND (p_quote_access_tbl(counter).notify_flag = FND_API.G_TRUE)) THEN
804 IBE_WORKFLOW_PVT.Notify_Shared_Cart(
805 p_api_version => p_api_version ,
806 p_init_msg_list => p_init_msg_list ,
807 p_quote_access_rec => l_quote_access_rec ,
808 p_minisite_id => p_minisite_id ,
809 p_url => l_url ,
810 p_shared_by_party_id => l_sharing_partyid ,
811 p_notes => p_notes ,
812 x_return_status => x_return_status ,
813 x_msg_count => x_msg_count ,
814 x_msg_data => x_msg_data );
815
816 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
817 RAISE FND_API.G_EXC_ERROR;
818 END IF;
819
820 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
821 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
822 END IF;
823 END IF;
824
825 --DBMS_OUTPUT.PUT_LINE('Done calling the new shared cart notification API ');
826
827 ELSIF(p_quote_access_tbl(counter).operation_code = 'UPDATE') THEN
828
829 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
830 IBE_UTIL.DEBUG('Operation code in quote_access_table is UPDATE');
831 END IF;
832 IF((l_quote_access_rec.update_privilege_type_code is not null) and
833 (l_quote_access_rec.update_privilege_type_code <> FND_API.G_MISS_CHAR) and
834 (l_old_access_level <> l_quote_access_rec.update_privilege_type_code)) then
835 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
836 IBE_UTIL.DEBUG('Calling IBE_SH_QUOTE_ACCESS_PKG.Update_Row to update the recip record');
837 IBE_UTIL.DEBUG('p_quote_access_tbl(counter).quote_sharee_id: '||p_quote_access_tbl(counter).quote_sharee_id);
838 END IF;
839
840 IBE_SH_QUOTE_ACCESS_PKG.Update_Row(
841 p_QUOTE_HEADER_ID => p_quote_header_id,
842 p_quote_sharee_id => p_quote_access_tbl(counter).quote_sharee_id,
843 p_UPDATE_PRIVILEGE_TYPE_CODE => p_quote_access_tbl(counter).update_privilege_type_code);
844
845 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
846 IBE_UTIL.DEBUG('Done IBE_SH_QUOTE_ACCESS_PKG.Update_Row ');
847 END IF;
848
849 /*If access level is being downgraded to 'read-only' */
850 IF(p_quote_access_tbl(counter).UPDATE_PRIVILEGE_TYPE_CODE = 'R') THEN
851 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
852 IBE_UTIL.DEBUG('Update privilege type is downgraded to read-only');
853 END IF;
854 FOR rec_check_active_cart in c_check_active_cart(l_quote_access_rec.party_id ,
855 l_quote_access_rec.cust_account_id) loop
856 l_quote_present := rec_check_active_cart.quote_header_id;
857 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
858 IBE_UTIL.DEBUG('Recipient has '||l_quote_present||'as active cart');
859 END IF;
860 IF((l_quote_present is not null) AND (p_quote_header_id = l_quote_present)) THEN
861 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
862 IBE_UTIL.DEBUG('Recipient has '||l_quote_present||' shared cart as the active-cart , delete this while downgrading level');
863 END IF;
864 --if this recipient has this shared cart as the active cart , then need to delete this
865 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
866 IBE_UTIL.DEBUG('Calling delete handler of active_quotes_all table');
867 END IF;
868
869 IBE_ACTIVE_QUOTES_ALL_PKG.UPDATE_ROW(
870 x_object_version_number => 1,
871 x_quote_header_id => null,
872 x_party_id => l_quote_access_rec.party_id ,
873 x_cust_account_id => l_quote_access_rec.cust_account_id,
874 X_RECORD_TYPE => 'CART',
875 X_CURRENCY_CODE => null,
876 x_last_update_date => sysdate,
877 x_last_updated_by => fnd_global.user_id,
878 x_last_update_login => 1);
879 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
880 IBE_UTIL.DEBUG('Done calling Update handler of active_quotes_all table to erase the quote header id' );
881 END IF;
882 END IF;
883 EXIT WHEN c_check_active_cart%NOTFOUND;
884 END LOOP; -- for c_check_active_cart
885 END IF; -- when privelege is read_only
886
887 -- new access level is available in the quote access record
888 IF(p_quote_access_tbl(counter).notify_flag = FND_API.G_TRUE) THEN
889 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
890 IBE_UTIL.DEBUG('Save_recipients: Ready to call Notify_access_change' );
891 END IF;
892 --dbms_output.put_line('Save_recipients: Ready to call Notify_access_change ');
893 IBE_WORKFLOW_PVT.Notify_access_change(
894 p_api_version => p_api_version ,
895 p_init_msg_list => p_init_msg_list ,
896 p_quote_access_rec => l_quote_access_rec ,
897 p_minisite_id => p_minisite_id ,
898 p_url => p_url ,
899 p_old_accesslevel => l_old_access_level ,
900 p_notes => p_notes ,
901 p_shared_by_party_id => l_sharing_partyid ,
902 x_return_status => x_return_status ,
903 x_msg_count => x_msg_count ,
904 x_msg_data => x_msg_data );
905
906 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
907 RAISE FND_API.G_EXC_ERROR;
908 END IF;
909
910 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
911 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
912 END IF;
913 END IF; -- end if notify flag = true for notify access change email
914 ELSE
915 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
916 IBE_UTIL.DEBUG('Opcode was update but access level has not changed. Consider generic email');
917 IBE_UTIL.DEBUG('view_shared_cart: l_sharing_partyid : ' || l_sharing_partyid);
918 IBE_UTIL.DEBUG('view_shared_cart: l_owner_partyid : ' || l_owner_partyid);
919 IBE_UTIL.DEBUG('view_shared_cart: p_notes : ' || p_notes);
920 IBE_UTIL.DEBUG('view_shared_cart: l_quote_access_rec.qute_sharee_num: '||l_quote_access_rec.quote_sharee_number);
921 IBE_UTIL.DEBUG('view_shared_cart: l_quote_access_rec.party_id: '||l_quote_access_rec.party_id);
922 END IF;
923 IF ((p_quote_access_tbl(counter).notify_flag = FND_API.G_TRUE) AND
924 (nvl(l_quote_access_rec.party_id,-1) <> l_sharing_partyid)) THEN
925 l_quote_access_rec.UPDATE_PRIVILEGE_TYPE_CODE := l_old_access_level;
926 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
927 IBE_UTIL.DEBUG('l_quote_access_rec.UPDATE_PRIVILEGE_TYPE_CODE '||l_quote_access_rec.UPDATE_PRIVILEGE_TYPE_CODE);
928 END IF;
929 IBE_WORKFLOW_PVT.Notify_view_shared_cart(
930 p_api_version => p_api_version ,
931 p_init_msg_list => p_init_msg_list ,
932 p_quote_access_rec => l_quote_access_rec ,
933 p_minisite_id => p_minisite_id ,
934 p_url => p_url ,
935 p_notes => p_notes ,
936 p_sent_by_party_id => l_sharing_partyid ,
937 x_return_status => x_return_status ,
938 x_msg_count => x_msg_count ,
939 x_msg_data => x_msg_data );
940 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
941 RAISE FND_API.G_EXC_ERROR;
942 END IF;
943 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
944 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
945 END IF;
946 END IF; -- end if notify_flag = true for generic email
947 END IF; -- end if old access level is not diff from new access level
948 ELSIF(p_quote_access_tbl(counter).operation_code = 'DELETE') then
949 --ELSE --orig functionality was to default to delete, so we've got to keep it that way
950 --IF(p_quote_access_tbl(counter).operation_code = 'DELETE') THEN --OPERATION_CODE IS DELETE
951 --DBMS_OUTPUT.PUT_LINE('Operation code in save recip is delete: Caling delete_recipient');
952 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
953 IBE_UTIL.DEBUG('Operation code in save recip is delete');
954 IBE_UTIL.DEBUG('Calling delete_recipient');
955 IBE_UTIL.DEBUG('l_quote_access_rec.quote_sharee_id: '||l_quote_access_rec.quote_sharee_id);
956 END IF;
957
958 IBE_QUOTE_SAVESHARE_V2_PVT.DELETE_RECIPIENT(
959 P_Quote_access_rec => l_quote_access_rec ,
960 p_minisite_id => p_minisite_id ,
961 p_url => p_url ,
962 p_notes => p_notes ,
963 x_return_status => x_return_status ,
964 x_msg_count => x_msg_count ,
965 x_msg_data => x_msg_data );
966
967 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
968 RAISE FND_API.G_EXC_ERROR;
969 END IF;
970 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
971 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
972 END IF;
973 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
974 IBE_UTIL.DEBUG('Done calling delete_recipient');
975 END IF;
976 END IF ;--FOR OPERATION CODE
977 END LOOP; -- end loop over input table of recipients
978
979 --To send a generic notification to the owner of the cart
980 l_quote_access_rec_owr.party_id := l_owner_partyid;
981 l_quote_access_rec_owr.quote_header_id := P_Quote_header_id;
982
983 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
984 IBE_UTIL.DEBUG('l_sharing_partyid: '||l_sharing_partyid);
985 IBE_UTIL.DEBUG('l_owner_partyid: '||l_owner_partyid);
986 IBE_UTIL.DEBUG('l_owner_partyid: '||l_owner_accountid);
987 END IF;
988
989 IF (l_sharing_partyid <> l_owner_partyid) THEN
990 -- for generic email to owner, we need to add some special parameters since we will not have a retrieval number
991 l_url := p_url || '&opid=' || l_owner_partyid || '&oaid=' || l_owner_accountid;
992 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
993 IBE_UTIL.DEBUG('url for generic email to OWNER: '||l_url);
994 END IF;
995 IBE_WORKFLOW_PVT.Notify_view_shared_cart(
996 p_api_version => p_api_version
997 ,p_init_msg_list => p_init_msg_list
998 ,p_quote_access_rec => l_quote_access_rec_owr
999 ,p_minisite_id => p_minisite_id
1000 ,p_url => l_url
1001 ,p_notes => p_notes
1002 ,p_sent_by_party_id => l_sharing_partyid
1003 ,p_owner_party_id => l_owner_partyid
1004 ,x_return_status => x_return_status
1005 ,x_msg_count => x_msg_count
1006 ,x_msg_data => x_msg_data );
1007 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1008 RAISE FND_API.G_EXC_ERROR;
1009 END IF;
1010 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1011 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1012 END IF;
1013 END IF;
1014
1015 --DBMS_OUTPUT.PUT_LINE('IBE_QUOTE_SAVESHARE_V2_PVT.save_recipients: END ');
1016
1017 -- Standard check of p_commit.
1018 IF FND_API.To_Boolean( p_commit ) THEN
1019 COMMIT WORK;
1020 END IF;
1021
1022 -- Standard call to get message count and if count is 1, get message info.
1023 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
1024 p_count => x_msg_count ,
1025 p_data => x_msg_data);
1026 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1027 IBE_UTIL.DEBUG('IBE_QUOTE_SAVESHARE_V2_PVT.save_recipients: END');
1028 END IF;
1029
1030 EXCEPTION
1031 WHEN FND_API.G_EXC_ERROR THEN
1032 ROLLBACK TO SAVERECIPIENTS_V2;
1033 x_return_status := FND_API.G_RET_STS_ERROR;
1034 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
1035 p_count => x_msg_count ,
1036 p_data => x_msg_data);
1037 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1038 IBE_Util.Debug('Expected error in IBE_QUOTE_SAVESHARE_V2_PVT.SAVE_RECIPIENTS()');
1039 END IF;
1040
1041 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1042 ROLLBACK TO SAVERECIPIENTS_V2;
1043 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1044 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
1045 p_count => x_msg_count ,
1046 p_data => x_msg_data);
1047 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1048 IBE_Util.Debug('Unxpected error in IBE_QUOTE_SAVESHARE_V2_PVT.SAVE_RECIPIENTS()');
1049 END IF;
1050
1051 WHEN OTHERS THEN
1052 ROLLBACK TO SAVERECIPIENTS_V2;
1053 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1054 IF FND_Msg_Pub.Check_Msg_Level(FND_Msg_Pub.G_MSG_LVL_UNEXP_ERROR) THEN
1055 FND_Msg_Pub.Add_Exc_Msg(G_PKG_NAME,
1056 l_api_name);
1057 END IF;
1058 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
1059 p_count => x_msg_count ,
1060 p_data => x_msg_data);
1061 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1062 IBE_Util.Debug('Unknown exception IBE_QUOTE_SAVESHARE_V2_PVT.SAVE_RECIPIENTS()');
1063 END IF;
1064
1065 END;
1066
1067 /*To handle new Active Cart Definition
1068 *Usages:
1069 -"Edit" button called from List of Saved Carts, List of Shared Out Carts, List of Recipient Carts,
1070 or Cart Details */
1071
1072 Procedure activate_quote (
1073 P_Quote_header_rec IN ASO_QUOTE_PUB.Qte_Header_Rec_Type ,
1074 P_Party_id IN NUMBER := FND_API.G_MISS_NUM ,
1075 P_Cust_account_id IN NUMBER := FND_API.G_MISS_NUM ,
1076 P_control_rec IN ASO_QUOTE_PUB.control_rec_type
1077 := ASO_QUOTE_PUB.G_MISS_Control_Rec,
1078 p_retrieval_number IN NUMBER := FND_API.G_MISS_NUM ,
1079 p_api_version IN NUMBER := 1 ,
1080 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
1081 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
1082 x_return_status OUT NOCOPY VARCHAR2 ,
1083 x_msg_count OUT NOCOPY NUMBER ,
1084 x_msg_data OUT NOCOPY VARCHAR2 ) is
1085
1086 l_current_cartname VARCHAR2(2000);
1087 l_new_cartname VARCHAR2(2000);
1088 l_quote_header_rec ASO_QUOTE_PUB.Qte_header_rec_type;
1089 l_quote_header_id NUMBER;
1090 l_last_update_date date;
1091 l_control_rec ASO_Quote_Pub.Control_Rec_Type
1092 := ASO_Quote_Pub.G_Miss_Control_Rec;
1093 l_ac_present NUMBER := null; --active cart present in active carts table or not
1094 l_ac_party_id NUMBER := null;
1095 l_qhac_present NUMBER := null; --active cart present in quote headers or not
1096 l_qhac_quote_name VARCHAR2(2000) := null;
1097 l_cart_type VARCHAR2(2000) ;
1098 l_api_name CONSTANT VARCHAR2(30) := 'ACTIVATEQUOTE_V2';
1099 l_api_version CONSTANT NUMBER := 1.0;
1100 l_sharee_party_id NUMBER := FND_API.G_MISS_NUM;
1101 l_sharee_acct_id NUMBER := FND_API.G_MISS_NUM;
1102 l_sharee_number NUMBER := FND_API.G_MISS_NUM;
1103
1104
1105 cursor c_check_ac_aqa(c_party_id number,
1106 c_cust_account_id number) is
1107 select aq.quote_header_id ,aq.party_id
1108 from ibe_active_quotes aq
1109 where party_id = c_party_id
1110 and cust_account_id = c_cust_account_id
1111 and record_type = 'CART';
1112
1113 cursor c_check_guest_oneclk(c_qte_hdr_id NUMBER) is
1114 select quote_source_code
1115 from aso_quote_headers
1116 where quote_header_id = c_qte_hdr_id;
1117
1118 cursor c_check_ac_qh(c_qh_id NUMBER) is
1119 select quote_header_id, quote_name
1120 from aso_quote_headers
1121 where quote_header_id = c_qh_id;
1122
1123 rec_check_ac_qh c_check_ac_qh%rowtype;
1124 rec_check_ac_aqa c_check_ac_aqa%rowtype;
1125 rec_check_guest_oneclk c_check_guest_oneclk%rowtype;
1126
1127
1128 BEGIN
1129
1130 SAVEPOINT ACTIVATEQUOTE_V2;
1131 -- Standard call to check for call compatibility.
1132 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1133 IBE_UTIL.DEBUG('Activate_quote:Before caling the compatible_api_call API');
1134 END IF;
1135 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1136 P_Api_Version,
1137 l_api_name,
1138 G_PKG_NAME )
1139 THEN
1140 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1141 END IF;
1142 -- Initialize message list IF p_init_msg_list is set to TRUE.
1143 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1144 FND_MSG_PUB.initialize;
1145 END IF;
1146
1147 -- Initialize API return status to success
1148 x_return_status := FND_API.G_RET_STS_SUCCESS;
1149
1150
1151 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1152 IBE_UTIL.DEBUG('IBE_QUOTE_SAVESHARE_V2_PVT.activate_quote: START');
1153 IBE_UTIL.DEBUG('p_party_id : '||p_party_id);
1154 IBE_UTIL.DEBUG('p_cust_account_id: '||p_cust_account_id);
1155 IBE_UTIL.DEBUG('p_retrieval_number: '||p_retrieval_number);
1156 IBE_UTIL.DEBUG('p_quote_header_id: '||p_quote_header_rec.quote_header_id);
1157 END IF;
1158 --DBMS_OUTPUT.PUT_LINE('IBE_QUOTE_SAVESHARE_V2_PVT.activate_quote: START');
1159 FOR rec_check_guest_oneclk in c_check_guest_oneclk(p_quote_header_rec.quote_header_id) LOOP
1160 l_cart_type := rec_check_guest_oneclk.quote_source_code;
1161 EXIT WHEN c_check_guest_oneclk%notfound;
1162 END LOOP;
1163 --DBMS_OUTPUT.PUT_LINE('l_cart_type: '||nvl(l_cart_type,'Null'));
1164 --Cannot activate a guest cart, Installbase cart, Punchout cart
1165 IF ((l_cart_type <> 'IStore Walkin')and
1166 (l_cart_type <> 'IStore Oneclick')and
1167 (l_cart_type <> 'IStore InstallBase')and
1168 (l_cart_type <> 'IStore ProcPunchout'))THEN
1169 IBE_Quote_Misc_pvt.validate_user_update
1170 (p_quote_header_id => p_quote_header_rec.quote_header_id,
1171 p_party_id => p_party_id,
1172 p_cust_account_id => p_cust_account_id,
1173 p_quote_retrieval_number => p_retrieval_number,
1174 p_validate_user => FND_API.G_TRUE,
1175 p_last_update_date => p_quote_header_rec.last_update_date,
1176 x_return_status => x_return_status,
1177 x_msg_count => x_msg_count,
1178 x_msg_data => x_msg_data);
1179
1180 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1181 RAISE FND_API.G_EXC_ERROR;
1182 END IF;
1183
1184 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1185 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1186 END IF;
1187 --DBMS_OUTPUT.PUT_LINE('Opening the cursor to check active cart present in active carts table');
1188 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1189 IBE_UTIL.DEBUG('Opening the cursor to check active cart present in active carts table');
1190 END IF;
1191 /*c_check_ac_aqa will query to check if p_party_id already has an active cart in active_quotes table
1192 If yes then update the quote_header_id of this record to p_quote_header_rec.quote_header_id(new active_cart hdr_id)
1193 else insert a record into the active_quotes table*/
1194
1195 for rec_check_ac_aqa in c_check_ac_aqa( p_party_id,
1196 p_cust_account_id) loop
1197 l_ac_present := rec_check_ac_aqa.quote_header_id;
1198 l_ac_party_id := rec_check_ac_aqa.party_id;
1199 exit when c_check_ac_aqa%notfound;
1200 end loop;
1201
1202 IF (l_ac_present is not null) THEN
1203 --There already an active cart for p_party_id in active_quotes_table
1204 --DBMS_OUTPUT.PUT_LINE('active cart present in active carts table for: '||l_quote_header_rec.quote_header_id);
1205 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1206 IBE_UTIL.DEBUG('active cart present in active carts table l_ac_present: '||l_ac_present);
1207 END IF;
1208
1209 IF(FND_API.G_TRUE=IBE_QUOTE_MISC_PVT.is_quote_usable(l_ac_present,p_party_id,p_cust_account_id)) THEN
1210 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1211 IBE_UTIL.DEBUG('current active cart is still validate.');
1212 IBE_UTIL.DEBUG('checking if it is a unnamed cart.');
1213 END IF;
1214 for rec_check_ac_qh in c_check_ac_qh(l_ac_present) loop
1215 --Openin this cursor to check if the active cart name is IBEUNNAMED
1216 --DBMS_OUTPUT.PUT_LINE('Opened cursor c_check_ac_qh ');
1217 l_qhac_present := rec_check_ac_qh.quote_header_id;
1218 l_qhac_quote_name := rec_check_ac_qh.quote_name;
1219 exit when c_check_ac_qh%notfound;
1220
1221 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1222 IBE_UTIL.DEBUG('l_qhac_present: '||l_qhac_present);
1223 IBE_UTIL.DEBUG('l_qhac_quote_name: '||l_qhac_quote_name);
1224 END IF;
1225
1226 end loop;
1227 --DBMS_OUTPUT.PUT_LINE('after end loop of c_check_ac_qh ');
1228
1229 IF (l_qhac_quote_name = 'IBE_PRMT_SC_UNNAMED') THEN
1230
1231 --change the IBEUNNAMED cart to IBEDEFAULTNAMED and save it by calling IBE_Quote_Save_pvt.SAVE
1232 l_quote_header_rec.quote_header_id := l_qhac_present;
1233 l_quote_header_rec.quote_name := 'IBE_PRMT_SC_DEFAULTNAMED';
1234 --reasons for calling save here
1235 --to save the IBEUNNAMED cart to IBEDEFAULTNAMED
1236 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1237 IBE_UTIL.DEBUG('Unnamed cart found for: '||l_quote_header_rec.quote_header_id);
1238 IBE_UTIL.DEBUG('renaming it to IBEDEFAULTNAMED...');
1239 END IF;
1240 IBE_Quote_Save_pvt.save(
1241 p_api_version_number => p_api_version ,
1242 p_init_msg_list => fnd_api.g_false ,
1243 p_commit => fnd_api.g_false ,
1244
1245 p_qte_header_rec => l_Quote_header_rec ,
1246 --p_control_rec => l_control_rec ,
1247 x_quote_header_id => l_quote_header_id ,
1248 x_last_update_date => l_last_update_date ,
1249
1250 x_return_status => x_return_status ,
1251 x_msg_count => x_msg_count ,
1252 x_msg_data => x_msg_data);
1253 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1254 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1255 IBE_UTIL.DEBUG('failed to rename the cart.');
1256 END IF;
1257 RAISE FND_API.G_EXC_ERROR;
1258 END IF;
1259
1260 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1261 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1262 IBE_UTIL.DEBUG('failed to rename the cart, unexpected error.');
1263 END IF;
1264 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1265 END IF;
1266
1267 END IF;--l_qhac_quote_name = 'IBEUNNAMED'
1268 ELSE
1269 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1270 IBE_UTIL.DEBUG('invalid or expired active cart ignored.');
1271 END IF;
1272 END IF; -- end of is_quote_usable
1273 END IF; --l_ac_present
1274
1275 IF (l_ac_party_id is not null) THEN
1276 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1277 IBE_UTIL.DEBUG('IBE_QUOTE_SAVESHARE_V2_PVT.activate_quote:User already has a row in active_quotes table');
1278 IBE_UTIL.DEBUG('IBE_QUOTE_SAVESHARE_V2_PVT.activate_quote:Calling update row hdlr');
1279 END IF;
1280 --DBMS_OUTPUT.PUT_LINE('Calling update row handler for active carts');
1281
1282 IBE_ACTIVE_QUOTES_ALL_PKG.update_row(
1283 X_OBJECT_VERSION_NUMBER => 1,
1284 x_last_update_date => sysdate,
1285 x_last_updated_by => fnd_global.user_id,
1286 x_last_update_login => 1,
1287 X_RECORD_TYPE => 'CART',
1288 X_CURRENCY_CODE => null,
1289 x_party_id => p_party_id,
1290 x_cust_account_id => p_cust_account_id,
1291 x_quote_header_id => p_quote_header_rec.quote_header_id);
1292
1293 ELSE
1294
1295 --no previous active carts present for p_party_id hence inserting a new record
1296 --DBMS_OUTPUT.PUT_LINE('no active carts present for p_party_id hence inserting a new record');
1297 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1298 IBE_UTIL.DEBUG('no active carts present for p_party_id hence inserting a new record');
1299 IBE_UTIL.DEBUG('Quote header id of new active active cart is: '||p_quote_header_rec.quote_header_id);
1300 END IF;
1301 IBE_ACTIVE_QUOTES_ALL_PKG.Insert_row(
1302 X_OBJECT_VERSION_NUMBER => 1,
1303 X_QUOTE_HEADER_ID => p_quote_header_rec.quote_header_id,
1304 X_PARTY_ID => p_party_id,
1305 X_CUST_ACCOUNT_ID => p_cust_account_id,
1306 X_LAST_UPDATE_DATE => sysdate,
1307 X_CREATION_DATE => sysdate,
1308 X_RECORD_TYPE => 'CART',
1309 X_CURRENCY_CODE => null,
1310 X_CREATED_BY => fnd_global.USER_ID,
1311 X_LAST_UPDATED_BY => fnd_global.USER_ID,
1312 X_LAST_UPDATE_LOGIN => fnd_global.conc_login_id,
1313 X_ORG_ID => MO_GLOBAL.get_current_org_id());
1314 END IF;
1315
1316 if ((P_control_rec.pricing_request_type <> FND_API.G_MISS_CHAR) or
1317 (P_control_rec.header_pricing_event <> FND_API.G_MISS_CHAR) or
1318 (P_control_rec.line_pricing_event <> FND_API.G_MISS_CHAR) or
1319 (P_control_rec.CALCULATE_TAX_FLAG <> FND_API.G_MISS_CHAR) or
1320 (P_control_rec.CALCULATE_FREIGHT_CHARGE_FLAG <> FND_API.G_MISS_CHAR))
1321 then
1322 --1. re-price cart based on the state of control_rec
1323 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1324 IBE_UTIL.DEBUG('Calling SAVE');
1325 IBE_UTIL.DEBUG('reprice cart based on the state of control_rec');
1326 END IF;
1327 if (p_retrieval_number <> FND_API.G_MISS_NUM) then
1328 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1329 IBE_UTIL.DEBUG('repricing as recipient; retrieval num: ' || p_retrieval_number);
1330 END IF;
1331
1332 l_sharee_number := p_retrieval_number;
1333 l_sharee_party_id := p_party_id;
1334 l_sharee_acct_id := p_cust_account_id;
1335 end if;
1336 IBE_Quote_Save_pvt.save(
1337 p_api_version_number => p_api_version ,
1338 p_init_msg_list => fnd_api.g_false ,
1339 p_commit => fnd_api.g_false ,
1340
1341 p_qte_header_rec => p_Quote_header_rec ,
1342 p_control_rec => P_control_rec ,
1343
1344 p_sharee_number => l_sharee_number ,
1345 p_sharee_party_id => l_sharee_party_id ,
1346 p_sharee_cust_account_id => l_sharee_acct_id ,
1347
1348 x_quote_header_id => l_quote_header_id ,
1349 x_last_update_date => l_last_update_date ,
1350
1351 x_return_status => x_return_status ,
1352 x_msg_count => x_msg_count ,
1353 x_msg_data => x_msg_data);
1354 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1355 RAISE FND_API.G_EXC_ERROR;
1356 END IF;
1357
1358 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1359 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1360 END IF;
1361 end if;
1362 ELSE
1363 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1364 IBE_UTIL.DEBUG('Cannot activate a '||nvl(l_cart_type,'Invalid ')||' Cart');
1365 END IF;
1366 --DBMS_OUTPUT.PUT_LINE('Cannot activate a '||nvl(l_cart_type,'Invalid ')||' Cart');
1367
1368 END IF; --qute source code check
1369 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1370 IBE_UTIL.DEBUG('IBE_QUOTE_SAVESHARE_V2_PVT.activate_quote: END');
1371 END IF;
1372
1373 EXCEPTION
1374 WHEN FND_API.G_EXC_ERROR THEN
1375 ROLLBACK TO ACTIVATEQUOTE_V2;
1376 x_return_status := FND_API.G_RET_STS_ERROR;
1377 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
1378 p_count => x_msg_count ,
1379 p_data => x_msg_data);
1380 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1381 IBE_Util.Debug('Expected error in IBE_QUOTE_SAVESHARE_V2_PVT.activate_quote()');
1382 END IF;
1383
1384 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1385 ROLLBACK TO ACTIVATEQUOTE_V2;
1386 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1387 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
1388 p_count => x_msg_count ,
1389 p_data => x_msg_data);
1390 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1391 IBE_Util.Debug('Unexpected error in IBE_QUOTE_SAVESHARE_V2_PVT.activate_quote()');
1392 END IF;
1393
1394 WHEN OTHERS THEN
1395 ROLLBACK TO ACTIVATEQUOTE_V2;
1396 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1397 IF FND_Msg_Pub.Check_Msg_Level(FND_Msg_Pub.G_MSG_LVL_UNEXP_ERROR) THEN
1398 FND_Msg_Pub.Add_Exc_Msg(G_PKG_NAME,
1399 l_api_name);
1400 END IF;
1401 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
1402 p_count => x_msg_count ,
1403 p_data => x_msg_data);
1404 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1405 IBE_Util.Debug('Unknown error IBE_QUOTE_SAVESHARE_V2_PVT.activate_quote()');
1406 END IF;
1407
1408 END;
1409
1410 PROCEDURE save_contact_point(
1411 p_api_version_number IN NUMBER
1412 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1413 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
1414 ,P_URL IN VARCHAR2 := FND_API.G_MISS_char
1415 ,P_EMAIL IN VARCHAR2 := FND_API.G_MISS_char
1416 ,p_owner_table_id IN NUMBER
1417 ,p_mode IN VARCHAR2
1418 ,x_contact_point_id OUT NOCOPY NUMBER
1419 ,X_Return_Status OUT NOCOPY VARCHAR2
1420 ,X_Msg_Count OUT NOCOPY NUMBER
1421 ,X_Msg_Data OUT NOCOPY VARCHAR2
1422 )
1423 IS
1424 l_api_name CONSTANT VARCHAR2(30) := 'SaveContactPoint';
1425 l_api_version CONSTANT NUMBER := 1.0;
1426 l_application_id CONSTANT NUMBER := 671;
1427 l_object_version_number NUMBER := 1;
1428 l_created_by_module VARCHAR2(30) := 'SHARED CARTS';
1429
1430 l_test number;
1431
1432 l_contact_points_rec hz_CONTACT_POINT_V2PUB.CONTACT_POINT_REC_TYPE;
1433
1434 l_email_rec hz_CONTACT_POINT_V2PUB.EMAIL_REC_TYPE
1435 := hz_CONTACT_POINT_V2PUB.g_miss_EMAIL_REC;
1436 l_web_rec hz_CONTACT_POINT_V2PUB.web_REC_TYPE
1437 := hz_CONTACT_POINT_V2PUB.g_miss_web_REC;
1438
1439 Cursor c_getContactInfo(c_owner_table_id Number
1440 ,c_contact_point_type varchar2
1441 ,c_owner_table_name varchar2) IS
1442 Select contact_point_id, object_version_number
1443 From hz_contact_points
1444 Where OWNER_TABLE_NAME = c_owner_table_name
1445 AND CONTACT_POINT_TYPE = c_CONTACT_POINT_TYPE
1446 AND OWNER_TABLE_ID = c_owner_table_id;
1447
1448
1449
1450 BEGIN
1451 -- Standard Start of API savepoint
1452 SAVEPOINT SAVECONTACTPOINT_PVT;
1453 -- Standard call to check for call compatibility.
1454 IF NOT FND_API.Compatible_API_Call (l_api_version,
1455 P_Api_Version_Number,
1456 l_api_name,
1457 G_PKG_NAME )
1458 THEN
1459 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1460 END IF;
1461 -- Initialize message list IF p_init_msg_list is set to TRUE.
1462 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1463 FND_MSG_PUB.initialize;
1464 END IF;
1465
1466 -- Initialize API return status to success
1467 x_return_status := FND_API.G_RET_STS_SUCCESS;
1468
1469 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1470 IBE_UTIL.debug('Inside Save_contact_point API');
1471 END IF;
1472 --DBMS_OUTPUT.PUT_LINE('Inside Save_contact_point API');
1473 l_contact_points_rec.status := 'A';
1474 l_contact_points_rec.owner_table_id := p_owner_table_id;
1475 l_contact_points_rec.created_by_module := l_created_by_module;
1476 l_contact_points_rec.application_id := l_application_id;
1477
1478 IF (p_mode = 'EMAIL') THEN
1479 --DBMS_OUTPUT.PUT_LINE('Mode of contact point is EMAIL');
1480 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1481 IBE_UTIL.debug('Mode of contact point is EMAIL');
1482 END IF;
1483 l_contact_points_rec.contact_point_type := 'EMAIL';
1484 l_contact_points_rec.owner_table_name := 'IBE_SH_QUOTE_ACCESS';
1485 l_email_rec.email_address := P_EMAIL;
1486 elsif (p_mode = 'WEB') THEN
1487 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1488 IBE_UTIL.debug('Mode of contact point is WEB');
1489 END IF;
1490
1491 l_contact_points_rec.contact_point_type := 'WEB';
1492 l_contact_points_rec.owner_table_name := 'IBE_SH_QUOTE_ACCESS';
1493 l_web_rec.url := P_URL;
1494 l_web_rec.web_type := 'com';
1495 END IF;
1496
1497
1498 SELECT COUNT(contact_point_id)
1499 INTO l_test
1500 FROM hz_contact_points
1501 WHERE owner_table_name = l_contact_points_rec.owner_table_name
1502 AND owner_table_id = l_contact_points_rec.owner_table_id;
1503
1504 IF l_test = 0 THEN
1505 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1506 IBE_UTIL.DEBUG('call hz_conteact_point_pub.create_contact_points at'
1507 || to_char(sysdate, 'mm/dd/yyyy:hh24:MI:SS'));
1508 END IF;
1509
1510 --DBMS_OUTPUT.PUT_LINE('Create contact point ');
1511 hz_contact_point_v2pub.create_contact_point
1512 ( p_init_msg_list => FND_API.G_FALSE
1513 ,p_contact_point_rec => l_contact_points_rec
1514 ,p_web_rec => l_web_rec
1515 ,p_email_rec => l_email_rec
1516 ,x_contact_point_id => x_contact_point_id
1517 ,x_return_status => x_return_status
1518 ,x_msg_count => x_msg_count
1519 ,x_msg_data => x_msg_data
1520 );
1521 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1522 RAISE FND_API.G_EXC_ERROR;
1523 END IF;
1524 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1525 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1526 END IF;
1527 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1528 IBE_UTIL.DEBUG('done hz_conteact_point_pub.create_contact_points at'
1529 || to_char(sysdate, 'mm/dd/yyyy:hh24:MI:SS'));
1530 END IF;
1531
1532 else
1533 --DBMS_OUTPUT.PUT_LINE('Updating contact point ');
1534 open c_getContactInfo(l_contact_points_rec.owner_table_id
1535 ,l_contact_points_rec.contact_point_type
1536 ,l_contact_points_rec.owner_table_name);
1537 fetch c_getContactInfo into l_contact_points_rec.contact_point_id
1538 ,l_object_version_number;
1539 close c_getContactInfo;
1540 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1541 IBE_UTIL.DEBUG('call hz_conteact_point_pub.update_contact_points at'
1542 || to_char(sysdate, 'mm/dd/yyyy:hh24:MI:SS'));
1543 END IF;
1544
1545 hz_contact_point_v2pub.update_contact_point(
1546 p_init_msg_list => FND_API.G_FALSE
1547 ,p_contact_point_rec => l_contact_points_rec
1548 ,p_web_rec => l_web_rec
1549 ,p_email_rec => l_email_rec
1550 ,p_object_version_number => l_object_version_number
1551 ,x_return_status => x_return_status
1552 ,x_msg_count => x_msg_count
1553 ,x_msg_data => x_msg_data);
1554
1555 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1556 RAISE FND_API.G_EXC_ERROR;
1557 END IF;
1558
1559 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1560 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1561 END IF;
1562 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1563 IBE_UTIL.DEBUG('done hz_conteact_point_pub.update_contact_points at'
1564 || to_char(sysdate, 'mm/dd/yyyy:hh24:MI:SS'));
1565 END IF;
1566 END IF;
1567
1568 -- Standard check of p_commit.
1569 IF FND_API.To_Boolean( p_commit ) THEN
1570 COMMIT WORK;
1571 END IF;
1572
1573 -- Standard call to get message count and IF count is 1, get message info.
1574 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1575 p_count => x_msg_count,
1576 p_data => x_msg_data);
1577 EXCEPTION
1578 WHEN FND_API.G_EXC_ERROR THEN
1579 ROLLBACK TO SAVECONTACTPOINT_PVT;
1580 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1581 IBE_UTIL.DEBUG('Expected exception in Ibe_quote_saveshare_v2_pvt.SaveContactPoint');
1582 end if;
1583 x_return_status := FND_API.G_RET_STS_ERROR;
1584 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1585 p_count => x_msg_count,
1586 p_data => x_msg_data);
1587 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1588 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1589 IBE_UTIL.DEBUG('Unexpected exception in Ibe_quote_saveshare_v2_pvt.SaveContactPoint');
1590 end if;
1591
1592 ROLLBACK TO SAVECONTACTPOINT_PVT;
1593 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1594 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1595 p_count => x_msg_count,
1596 p_data => x_msg_data);
1597 WHEN OTHERS THEN
1598 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1599 IBE_UTIL.DEBUG('Unknown exception in Ibe_quote_saveshare_v2_pvt.SaveContactPoint');
1600 end if;
1601
1602 ROLLBACK TO SAVECONTACTPOINT_PVT;
1603 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1604
1605 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1606 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,
1607 l_api_name);
1608 END IF;
1609
1610 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1611 p_count => x_msg_count,
1612 p_data => x_msg_data);
1613 END SAVE_CONTACT_POINT;
1614
1615 /*To mark an active cart as no longer active for the submitted user (either owner or recipient as identified
1616 by party_id and account_id) and/or all of that cart's recipients.
1617 *Usages:
1618 -User clicks "save" on a saved active cart
1619 -Other api's where the quote is no longer active (submit quote, request sales assistance, etc)*/
1620
1621 Procedure deactivate_quote (
1622 P_Quote_header_id IN Number ,
1623 P_Party_id IN Number := FND_API.G_MISS_NUM ,
1624 P_Cust_account_id IN Number := FND_API.G_MISS_NUM ,
1625 P_minisite_id IN Number := FND_API.G_MISS_NUM ,
1626 p_api_version IN NUMBER := 1 ,
1627 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
1628 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
1629 x_return_status OUT NOCOPY VARCHAR2 ,
1630 x_msg_count OUT NOCOPY NUMBER ,
1631 x_msg_data OUT NOCOPY VARCHAR2 ) is
1632
1633 l_api_name CONSTANT VARCHAR2(30) := 'DEACTIVATEQUOTE_V2';
1634 l_api_version CONSTANT NUMBER := 1.0;
1635
1636 cursor c_select_recip(c_qte_hdr_id number) is
1637 select party_id, cust_account_id
1638 from ibe_sh_quote_access
1639 where quote_header_id = c_qte_hdr_id
1640 and nvl(end_date_active, sysdate+1) > sysdate ;
1641
1642 rec_select_recip c_select_recip%rowtype;
1643 l_party_cust_tbl IBE_QUOTE_SAVESHARE_pvt.QUOTE_ACCESS_Tbl_Type
1644 := IBE_QUOTE_SAVESHARE_pvt.g_miss_QUOTE_ACCESS_Tbl;
1645 counter NUMBER := 1;
1646
1647 BEGIN
1648
1649 SAVEPOINT DEACTIVATEQUOTE_V2;
1650 -- Standard call to check for call compatibility.
1651 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1652 P_Api_Version,
1653 l_api_name,
1654 G_PKG_NAME )
1655 THEN
1656 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1657 END IF;
1658 -- Initialize message list IF p_init_msg_list is set to TRUE.
1659 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1660 FND_MSG_PUB.initialize;
1661 END IF;
1662
1663 -- Initialize API return status to success
1664 x_return_status := FND_API.G_RET_STS_SUCCESS;
1665 -------------------------------------------------------------------------------------------------------------
1666 --API Body start
1667 -------------------------------------------------------------------------------------------------------------
1668 --dbms_output.put_line(' Deactivate API: Trying to deactivate: '||rec_check_active_cart.quote_header_id);
1669 FOR rec_check_active_cart in c_check_active_cart(p_party_id,
1670 p_cust_account_id) LOOP
1671 IF(rec_check_active_cart.quote_header_id is not null) THEN
1672 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1673 IBE_UTIL.DEBUG('Deactivate API: Trying to deactivate: '||rec_check_active_cart.quote_header_id);
1674 --dbms_output.put_line(' Deactivate API: Trying to deactivate: '||rec_check_active_cart.quote_header_id);
1675 END IF;
1676
1677 --DBMS_OUTPUT.PUT_LINE('rec_check_active_cart.quote_header_id '||rec_check_active_cart.quote_header_id );
1678 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1679 IBE_UTIL.DEBUG('Calling update handler of active_quotes_all table');
1680 END IF;
1681 --DBMS_OUTPUT.PUT_LINE('Calling update handler of active_quotes_all table');
1682
1683 --DBMS_OUTPUT.PUT_LINE('P_quote_header_id '||P_quote_header_id);
1684 --DBMS_OUTPUT.PUT_LINE('p_quote_access_tbl(counter).party_id '||p_party_id);
1685 --DBMS_OUTPUT.PUT_LINE('p_quote_access_tbl(counter).cust_account_id '||p_cust_account_id);
1686
1687 IBE_ACTIVE_QUOTES_ALL_PKG.UPDATE_ROW(
1688 X_OBJECT_VERSION_NUMBER => 1,
1689 X_QUOTE_HEADER_ID => null,
1690 X_PARTY_ID => p_party_id ,
1691 X_CUST_ACCOUNT_ID => p_cust_account_id,
1692 X_RECORD_TYPE => 'CART',
1693 X_CURRENCY_CODE => null,
1694 x_last_update_date => sysdate,
1695 x_last_updated_by => fnd_global.user_id,
1696 x_last_update_login => 1);
1697 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1698 IBE_UTIL.DEBUG('Done calling Update handler of active_quotes_all table to erase the quote header id' );
1699 END IF;
1700 --DBMS_OUTPUT.PUT_LINE('Done calling delete handler of active_quotes_all table');
1701 END IF;
1702 EXIT when c_check_active_cart%notfound;
1703 END LOOP; --for c_check_active_cart
1704
1705 -- Standard check of p_commit.
1706 IF FND_API.To_Boolean( p_commit ) THEN
1707 COMMIT WORK;
1708 END IF;
1709
1710 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1711 IBE_UTIL.DEBUG('IBE_QUOTE_SAVESHARE_V2_PVT.deactivate_quote: END');
1712 END IF;
1713
1714 EXCEPTION
1715 WHEN FND_API.G_EXC_ERROR THEN
1716 ROLLBACK TO DEACTIVATEQUOTE_V2;
1717 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1718 IBE_UTIL.DEBUG('Expected exception in Ibe_quote_saveshare_v2_pvt.Deactivate_quote');
1719 end if;
1720
1721 x_return_status := FND_API.G_RET_STS_ERROR;
1722 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
1723 p_count => x_msg_count ,
1724 p_data => x_msg_data);
1725 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1726 IBE_Util.Debug('Unexpected error in IBE_QUOTE_SAVESHARE_V2_PVT.deactivate_quote');
1727 END IF;
1728
1729 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1730 ROLLBACK TO DEACTIVATEQUOTE_V2;
1731 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1732 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
1733 p_count => x_msg_count ,
1734 p_data => x_msg_data);
1735 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1736 IBE_Util.Debug('Unknown error in IBE_QUOTE_SAVESHARE_V2_PVT.deactivate_quote');
1737 END IF;
1738
1739 WHEN OTHERS THEN
1740 ROLLBACK TO DEACTIVATEQUOTE_V2;
1741 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1742 IF FND_Msg_Pub.Check_Msg_Level(FND_Msg_Pub.G_MSG_LVL_UNEXP_ERROR) THEN
1743 FND_Msg_Pub.Add_Exc_Msg(G_PKG_NAME,
1744 l_api_name);
1745 END IF;
1746 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
1747 p_count => x_msg_count ,
1748 p_data => x_msg_data);
1749 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1750 IBE_Util.Debug('Abnormal end IBE_QUOTE_SAVESHARE_V2_PVT.deactivate_quote');
1751 END IF;
1752
1753 END;
1754
1755 /*to handle new Active Cart Definition (may be able to use original version w/ updates to handle new
1756 active cart definition)
1757 *Usages:
1758 -Saving active cart and selecting a previously saved cart to append to.
1759 */
1760 Procedure APPEND_QUOTE(
1761 P_source_quote_header_id IN Number ,
1762 P_source_last_update_date IN Date ,
1763 P_target_header_rec IN ASO_QUOTE_PUB.Qte_Header_Rec_Type ,
1764 P_control_rec IN ASO_QUOTE_PUB.control_rec_type
1765 := ASO_QUOTE_PUB.G_MISS_Control_Rec ,
1766 P_delete_source_cart IN Varchar2 := FND_API.G_TRUE ,
1767 P_combinesameitem IN Varchar2 := FND_API.G_TRUE ,
1768 P_minisite_id IN Number := FND_API.G_MISS_NUM ,
1769 p_api_version IN NUMBER := 1 ,
1770 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
1771 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
1772 x_return_status OUT NOCOPY VARCHAR2 ,
1773 x_msg_count OUT NOCOPY NUMBER ,
1774 x_msg_data OUT NOCOPY VARCHAR2 ) is
1775
1776 l_api_name CONSTANT VARCHAR2(30) := 'APPENDQUOTE_V2';
1777 l_api_version CONSTANT NUMBER := 1.0;
1778
1779
1780 l_qte_line_tbl ASO_QUOTE_PUB.qte_line_tbl_type;
1781 l_qte_line_dtl_tbl ASO_QUOTE_PUB.qte_line_dtl_tbl_type;
1782 l_line_attr_ext_tbl ASO_Quote_Pub.Line_Attribs_Ext_tbl_Type;
1783 l_line_rltship_tbl ASO_QUOTE_PUB.line_rltship_tbl_type;
1784 l_ln_price_attributes_tbl ASO_Quote_Pub.Price_Attributes_Tbl_Type;
1785 l_hd_shipment_tbl ASO_Quote_Pub.Shipment_rec_Type
1786 := ASO_Quote_Pub.G_MISS_SHIPMENT_rec;
1787 l_last_update_date DATE;
1788 l_qte_header_id NUMBER;
1789
1790 -- added 12/22/03: PRG, no line merge
1791 l_Price_Adjustment_Tbl ASO_Quote_Pub.Price_Adj_Tbl_Type;
1792 l_Price_Adj_Rltship_Tbl ASO_Quote_Pub.Price_Adj_Rltship_Tbl_Type;
1793
1794 BEGIN
1795
1796 SAVEPOINT APPENDQUOTE_V2;
1797 -- Standard call to check for call compatibility.
1798 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1799 P_Api_Version,
1800 l_api_name,
1801 G_PKG_NAME )
1802 THEN
1803 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1804 END IF;
1805 -- Initialize message list IF p_init_msg_list is set to TRUE.
1806 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1807 FND_MSG_PUB.initialize;
1808 END IF;
1809
1810 -- Initialize API return status to success
1811 x_return_status := FND_API.G_RET_STS_SUCCESS;
1812 -------------------------------------------------------------------------------------------------------------
1813 --API Body start
1814 -------------------------------------------------------------------------------------------------------------
1815 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1816 IBE_UTIL.DEBUG('IBE_QUOTE_SAVESHARE_V2_PVT.APPEND_QUOTE: START');
1817 END IF;
1818
1819 IBE_Quote_Misc_pvt.validate_user_update
1820 (p_quote_header_id => P_source_quote_header_id,
1821 p_validate_user => FND_API.G_TRUE,
1822 x_return_status => x_return_status,
1823 x_msg_count => x_msg_count,
1824 x_msg_data => x_msg_data);
1825
1826 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1827 RAISE FND_API.G_EXC_ERROR;
1828 END IF;
1829
1830 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1831 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1832 END IF;
1833
1834 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1835 IBE_UTIL.DEBUG('IBE_QUOTE_SAVESHARE_V2_PVT.APPEND_QUOTE:Calling IBE_QUOTE_SAVESHARE_pvt.Copy_lines');
1836 END IF;
1837 --DBMS_OUTPUT.PUT_LINE('IBE_QUOTE_SAVESHARE_V2_PVT.APPEND_QUOTE:Calling IBE_QUOTE_SAVESHARE_pvt.Copy_lines ');
1838
1839 IBE_QUOTE_SAVESHARE_pvt.COPY_LINES(
1840 p_api_version_number => p_api_version ,
1841 p_init_msg_list => FND_API.G_FALSE ,
1842 p_commit => FND_API.G_FALSE ,
1843
1844 X_Return_Status => X_Return_Status ,
1845 X_Msg_Count => X_Msg_Count ,
1846 X_Msg_Data => X_Msg_Data ,
1847
1848 p_from_quote_header_id => P_source_quote_header_id ,
1849 p_to_quote_header_id => p_target_header_rec.quote_header_id,
1850 x_qte_line_tbl => l_qte_line_tbl ,
1851 x_qte_line_dtl_tbl => l_qte_line_dtl_tbl ,
1852 x_line_attr_ext_tbl => l_line_attr_ext_tbl ,
1853 x_line_rltship_tbl => l_line_rltship_tbl ,
1854 x_ln_price_attributes_tbl => l_ln_price_attributes_tbl ,
1855 x_Price_Adjustment_tbl => l_Price_Adjustment_tbl ,
1856 x_Price_Adj_Rltship_tbl => l_Price_Adj_Rltship_tbl );
1857
1858 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1859 RAISE FND_API.G_EXC_ERROR;
1860 END IF;
1861 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1862 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1863 END IF;
1864 --DBMS_OUTPUT.PUT_LINE('copy_lines done ');
1865 --DBMS_OUTPUT.PUT_LINE('immediately after copy_lines p_target_header_rec.quote_header_id '||p_target_header_rec.quote_header_id);
1866
1867 --l_hd_shipment_tbl(1) := p_hd_shipment_rec; $$ Verify this part. Ned to pass
1868 --p_to_hd_shipment_rec?? $$
1869 -- l_hd_shipment_tbl(1).quote_header_id := P_target_header_rec.quote_header_id;
1870
1871 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1872 IBE_UTIL.DEBUG('IBE_QUOTE_SAVESHARE_V2_PVT.APPEND_QUOTE:IBE_QUOTE_SAVESHARE_pvt.Copy_lines End');
1873 IBE_UTIL.DEBUG('IBE_QUOTE_SAVESHARE_V2_PVT.APPEND_QUOTE: Calling IBE_Quote_Save_pvt.Save to save target cart');
1874 END IF;
1875 --DBMS_OUTPUT.PUT_LINE('IBE_QUOTE_SAVESHARE_V2_PVT.APPEND_QUOTE: Calling IBE_Quote_Save_pvt.Save to save target cart ');
1876 --DBMS_OUTPUT.PUT_LINE('before save p_target_header_rec.quote_header_id '||p_target_header_rec.quote_header_id);
1877 IBE_Quote_Save_pvt.SAVE
1878 ( p_api_version_number => p_api_version
1879 ,p_init_msg_list => FND_API.G_FALSE
1880 ,p_commit => FND_API.G_FALSE
1881 ,p_qte_header_rec => p_target_header_rec
1882 ,p_Qte_Line_Tbl => l_qte_line_tbl
1883 ,p_Qte_Line_Dtl_Tbl => l_qte_line_dtl_tbl
1884 ,p_Line_Attr_Ext_Tbl => l_line_attr_ext_tbl
1885 ,p_Line_rltship_tbl => l_line_rltship_tbl
1886 ,p_control_rec => P_control_rec
1887 ,p_Price_Adjustment_tbl => l_Price_Adjustment_tbl
1888 ,p_Price_Adj_Rltship_tbl => l_Price_Adj_Rltship_tbl
1889 -- ,p_hd_shipment_tbl => l_hd_shipment_tbl
1890 ,x_quote_header_id => l_qte_header_id
1891 ,x_last_update_date => l_last_update_date
1892 ,x_return_status => x_return_status
1893 ,x_msg_count => x_msg_count
1894 ,x_msg_data => x_msg_data) ;
1895 --DBMS_OUTPUT.PUT_LINE('IBE_QUOTE_SAVESHARE_V2_PVT.APPEND_QUOTE: Done save ');
1896 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1897 RAISE FND_API.G_EXC_ERROR;
1898 END IF;
1899 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1900 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1901 END IF;
1902 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1903 IBE_UTIL.DEBUG('IBE_QUOTE_SAVESHARE_V2_PVT.APPEND_QUOTE: P_delete_source_cart: '||P_delete_source_cart);
1904 END IF;
1905 IF (P_delete_source_cart = FND_API.G_TRUE ) THEN
1906 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1907 IBE_UTIL.DEBUG('IBE_QUOTE_SAVESHARE_V2_PVT.APPEND_QUOTE: P_delete_source_cart is true');
1908 END IF;
1909 IBE_Quote_Save_pvt.Delete(
1910 p_api_version_number => p_api_version
1911 ,p_init_msg_list => FND_API.G_FALSE
1912 ,p_commit => FND_API.G_FALSE
1913 ,x_return_status => x_return_status
1914 ,x_msg_count => x_msg_count
1915 ,x_msg_data => x_msg_data
1916 ,p_quote_header_id => P_source_quote_header_id
1917 ,p_expunge_flag => FND_API.G_FALSE
1918 ,p_minisite_id => p_minisite_id
1919 ,p_last_update_date => P_source_last_update_date);
1920
1921 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1922 RAISE FND_API.G_EXC_ERROR;
1923 END IF;
1924 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1925 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1926 END IF;
1927 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1928 IBE_UTIL.DEBUG('IBE_QUOTE_SAVESHARE_V2_PVT.APPEND_QUOTE:Delete on source cart done');
1929 END IF;
1930 END IF;
1931
1932 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1933 IBE_UTIL.DEBUG('IBE_QUOTE_SAVESHARE_V2_PVT.APPEND_QUOTE: END');
1934 END IF;
1935
1936 EXCEPTION
1937 WHEN FND_API.G_EXC_ERROR THEN
1938 ROLLBACK TO APPENDQUOTE_V2;
1939 x_return_status := FND_API.G_RET_STS_ERROR;
1940 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
1941 p_count => x_msg_count ,
1942 p_data => x_msg_data);
1943 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1944 IBE_Util.Debug('Expected error in IBE_QUOTE_SAVESHARE_V2_PVT.append_quote()');
1945 END IF;
1946
1947 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1948 ROLLBACK TO APPENDQUOTE_V2;
1949 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1950 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
1951 p_count => x_msg_count ,
1952 p_data => x_msg_data);
1953 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1954 IBE_Util.Debug('Unexpected error in IBE_QUOTE_SAVESHARE_V2_PVT.append_quote()');
1955 END IF;
1956
1957 WHEN OTHERS THEN
1958 ROLLBACK TO APPENDQUOTE_V2;
1959 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1960 IF FND_Msg_Pub.Check_Msg_Level(FND_Msg_Pub.G_MSG_LVL_UNEXP_ERROR) THEN
1961 FND_Msg_Pub.Add_Exc_Msg(G_PKG_NAME,
1962 l_api_name);
1963 END IF;
1964 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
1965 p_count => x_msg_count ,
1966 p_data => x_msg_data);
1967 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1968 IBE_Util.Debug('Abnormal end IBE_QUOTE_SAVESHARE_V2_PVT.append_quote()');
1969 END IF;
1970
1971 END;
1972
1973 /*to handle removing of all recipients of one cartId
1974 *Usages:
1975 -"Stop Sharing" button from List of Shared Out Carts, List of Shared Out Quotes
1976 -Other apis where cart becomes "unusable" - submit quote and delete on the shared cart.
1977 */
1978
1979 Procedure stop_sharing (
1980 p_quote_header_id IN Number ,
1981 p_delete_context IN VARCHAR2 := 'IBE_SC_CART_STOPSHARING' ,
1982 P_minisite_id IN Number := FND_API.G_MISS_NUM ,
1983 p_notes IN Varchar2 := FND_API.G_MISS_CHAR ,
1984 p_quote_access_tbl IN IBE_QUOTE_SAVESHARE_pvt.QUOTE_ACCESS_Tbl_Type
1985 := IBE_QUOTE_SAVESHARE_pvt.G_miss_quote_access_Tbl,
1986 p_api_version IN NUMBER := 1 ,
1987 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
1988 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
1989 x_return_status OUT NOCOPY VARCHAR2 ,
1990 x_msg_count OUT NOCOPY NUMBER ,
1991 x_msg_data OUT NOCOPY VARCHAR2 ) is
1992
1993 l_api_name CONSTANT VARCHAR2(30) := 'STOPSHARING_V2';
1994 l_api_version CONSTANT NUMBER := 1.0;
1995 tbl_counter NUMBER := 1;
1996
1997 l_quote_access_tbl IBE_QUOTE_SAVESHARE_pvt.QUOTE_ACCESS_Tbl_Type
1998 := IBE_QUOTE_SAVESHARE_pvt.g_miss_quote_access_tbl;
1999 l_quote_access_tbl_tmp IBE_QUOTE_SAVESHARE_pvt.QUOTE_ACCESS_Tbl_Type
2000 := IBE_QUOTE_SAVESHARE_pvt.g_miss_quote_access_tbl;
2001 l_owner_party_id NUMBER;
2002 l_owner_cust_account_id NUMBER;
2003 l_sharing_party_id NUMBER;
2004
2005 cursor c_get_recipients(c_qte_hdr_id NUMBER) is
2006 select quote_sharee_id,
2007 party_id,
2008 cust_account_id,
2009 SH.contact_point_id,
2010 quote_sharee_number,
2011 HZ.EMAIL_ADDRESS,
2012 FND.customer_id shared_by_party_id
2013 from IBE_SH_QUOTE_ACCESS SH,
2014 HZ_CONTACT_POINTS HZ,
2015 FND_USER FND
2016 where SH.contact_point_id = HZ.Contact_point_id
2017 and quote_header_id = c_qte_hdr_id
2018 and nvl(end_date_active, sysdate+1) > sysdate
2019 and sh.created_by = fnd.user_id;
2020
2021 cursor c_get_recipient_info(c_recipient_id NUMBER) is
2022 select quote_sharee_id,
2023 party_id,
2024 cust_account_id,
2025 SH.contact_point_id,
2026 quote_sharee_number,
2027 HZ.EMAIL_ADDRESS,
2028 FND.customer_id shared_by_party_id
2029 from IBE_SH_QUOTE_ACCESS SH,
2030 HZ_CONTACT_POINTS HZ,
2031 FND_USER FND
2032 where SH.contact_point_id = HZ.Contact_point_id
2033 and quote_sharee_id = c_recipient_id
2034 and nvl(end_date_active, sysdate+1) > sysdate
2035 and sh.created_by = fnd.user_id;
2036
2037 CURSOR c_get_owner(c_quote_header_id NUMBER) is
2038 select party_id, cust_account_id
2039 from aso_quote_headers
2040 where quote_header_id = c_quote_header_id;
2041
2042
2043 rec_get_recipients c_get_recipients%rowtype;
2044 rec_get_owner c_get_owner%rowtype;
2045 rec_get_recipient_info c_get_recipient_info%rowtype;
2046
2047 BEGIN
2048 SAVEPOINT STOPSHARING_V2;
2049 -- Standard call to check for call compatibility.
2050 IF NOT FND_API.Compatible_API_Call ( l_api_version,
2051 P_Api_Version,
2052 l_api_name,
2053 G_PKG_NAME )
2054 THEN
2055 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2056 END IF;
2057 -- Initialize message list IF p_init_msg_list is set to TRUE.
2058 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2059 FND_MSG_PUB.initialize;
2060 END IF;
2061
2062 -- Initialize API return status to success
2063 x_return_status := FND_API.G_RET_STS_SUCCESS;
2064 -------------------------------------------------------------------------------------------------------------
2065 --API Body start
2066 -------------------------------------------------------------------------------------------------------------
2067 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2068 IBE_UTIL.DEBUG('IBE_QUOTE_SAVESHARE_V2_PVT.STOP_SHARING: START');
2069 END IF;
2070 -- Stop sharing should deactivate owner's active cart if any.
2071 --dbms_output.put_line('p_quote_header_id: '||p_quote_header_id);
2072 FOR rec_get_owner in c_get_owner(p_quote_header_id) LOOP
2073 l_owner_party_id := rec_get_owner.party_id;
2074 l_owner_cust_account_id := rec_get_owner.cust_account_id;
2075 --dbms_output.put_line('loop one in cursor ');
2076 EXIT when c_get_owner%NOTFOUND;
2077 END LOOP;
2078 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2079 IBE_UTIL.DEBUG('STOP_SHARING:Calling deactivate API');
2080 END IF;
2081 --DBMS_OUTPUT.PUT_LINE('Calling deactivate API to deactivate owner''s cart');
2082 --dbms_output.put_line('PARTY_ID: '||l_owner_party_id);
2083 --dbms_output.put_line('CUST_ACCOUNT_ID: '||l_owner_cust_account_id );
2084
2085 IBE_QUOTE_SAVESHARE_V2_PVT.DEACTIVATE_QUOTE (
2086 P_Quote_header_id => p_quote_header_id ,
2087 P_Party_id => l_owner_party_id ,
2088 P_Cust_account_id => l_owner_cust_account_id ,
2089 P_minisite_id => p_minisite_id ,
2090 p_api_version => p_api_version ,
2091 p_init_msg_list => fnd_api.g_false ,
2092 p_commit => fnd_api.g_false ,
2093 x_return_status => x_return_status ,
2094 x_msg_count => x_msg_count ,
2095 x_msg_data => x_msg_data );
2096 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2097 RAISE FND_API.G_EXC_ERROR;
2098 END IF;
2099
2100 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2101 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2102 END IF;
2103 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2104 IBE_UTIL.DEBUG('STOP_SHARING:Deactivation of owner cart :Done');
2105 END IF;
2106 --dbms_output.put_line('STOP_SHARING:Deactivation of owner cart :Done');
2107 --If there is no input quote access tbl then send notification to all recipients.
2108 --dbms_output.put_line('p_quote_access_tbl(1).quote_sharee_id: '||nvl(p_quote_access_tbl(1).quote_sharee_id,0));
2109 IF ((p_quote_access_tbl is not null) or (p_quote_access_tbl.count = 0) ) THEN
2110 FOR rec_userenv_partyid in c_userenv_partyid LOOP
2111 l_sharing_party_id := rec_userenv_partyid.customer_id;
2112 exit when c_userenv_partyid%NOTFOUND;
2113 END LOOP;
2114
2115 --dbms_output.put_line('Input quote access is null ');
2116 FOR rec_get_recipients in c_get_recipients(p_quote_header_id) LOOP
2117 l_quote_access_tbl(tbl_counter).quote_sharee_id := rec_get_recipients.quote_sharee_id;
2118 l_quote_access_tbl(tbl_counter).party_id := rec_get_recipients.party_id;
2119 --dbms_output.put_line('found party_id: '||rec_get_recipient_info.party_id);
2120 l_quote_access_tbl(tbl_counter).cust_account_id := rec_get_recipients.cust_account_id;
2121 l_quote_access_tbl(tbl_counter).contact_point_id := rec_get_recipients.contact_point_id;
2122 l_quote_access_tbl(tbl_counter).quote_header_id := p_quote_header_id;
2123 l_quote_access_tbl(tbl_counter).quote_sharee_number := rec_get_recipients.quote_sharee_number;
2124 l_quote_access_tbl(tbl_counter).email_contact_address := rec_get_recipients.email_address;
2125 l_quote_access_tbl(tbl_counter).shared_by_party_id := rec_get_recipients.shared_by_party_id;
2126 tbl_counter := tbl_counter+1;
2127 EXIT when c_get_recipients%notfound;
2128 END LOOP;
2129 ELSE
2130 --dbms_output.put_line('Input quote access table is not null');
2131
2132 FOR tbl_counter in 1..p_quote_access_tbl.count LOOP
2133 --dbms_output.put_line('p_quote_access_tbl(tbl_counter).quote_sharee_id: '||p_quote_access_tbl(tbl_counter).quote_sharee_id);
2134 FOR rec_get_recipient_info in c_get_recipient_info(l_quote_access_tbl(tbl_counter).quote_sharee_id) LOOP
2135 IF l_sharing_party_id <> rec_get_recipient_info.party_id THEN
2136
2137 l_quote_access_tbl(tbl_counter).quote_sharee_id := p_quote_access_tbl(tbl_counter).quote_sharee_id;
2138 l_quote_access_tbl(tbl_counter).notify_flag := p_quote_access_tbl(tbl_counter).notify_flag;
2139 l_quote_access_tbl(tbl_counter).party_id := rec_get_recipient_info.party_id;
2140 --dbms_output.put_line('found party_id: '||rec_get_recipient_info.party_id);
2141 l_quote_access_tbl(tbl_counter).cust_account_id := rec_get_recipient_info.cust_account_id;
2142 l_quote_access_tbl(tbl_counter).contact_point_id := rec_get_recipient_info.contact_point_id;
2143 l_quote_access_tbl(tbl_counter).quote_header_id := p_quote_header_id;
2144 l_quote_access_tbl(tbl_counter).quote_sharee_number := rec_get_recipient_info.quote_sharee_number;
2145 l_quote_access_tbl(tbl_counter).email_contact_address := rec_get_recipient_info.email_address;
2146 l_quote_access_tbl(tbl_counter).shared_by_party_id := rec_get_recipient_info.shared_by_party_id;
2147 END IF;
2148 EXIT when c_get_recipient_info%notfound;
2149 END LOOP;
2150 END LOOP;
2151 END IF;
2152 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2153 IBE_UTIL.DEBUG('STOP_SHARING:calling delete_recipient on the following recipients:');
2154 END IF;
2155 --dbms_output.put_line('STOP_SHARING:calling delete_recipient on the following recipients:');
2156 FOR i in 1.. l_quote_access_tbl.count LOOP
2157 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2158 IBE_UTIL.DEBUG('STOP_SHARING:calling delete_recipient');
2159 IBE_UTIL.DEBUG('STOP_SHARING:l_quote_access_tbl:'||i||':quote_sharee_id: '||l_quote_access_tbl(i).quote_sharee_id);
2160 IBE_UTIL.DEBUG('STOP_SHARING:l_quote_access_tbl:'||i||':contact_point_id: '||l_quote_access_tbl(i).contact_point_id);
2161 IBE_UTIL.DEBUG('STOP_SHARING:l_quote_access_tbl:'||i||':party_id: '||l_quote_access_tbl(i).party_id);
2162 IBE_UTIL.DEBUG('STOP_SHARING:l_quote_access_tbl:'||i||':cust_account_id: '||l_quote_access_tbl(i).cust_account_id);
2163 END IF;
2164 --dbms_output.put_line('STOP_SHARING:l_quote_access_tbl:'||i||':quote_sharee_id: '||l_quote_access_tbl(i).quote_sharee_id);
2165 --dbms_output.put_line('Calling delete recipient for the recipient record ');
2166 IBE_QUOTE_SAVESHARE_V2_PVT.DELETE_RECIPIENT(
2167 P_Quote_access_rec => l_quote_access_tbl(i) ,
2168 p_minisite_id => p_minisite_id ,
2169 p_delete_code => p_delete_context ,
2170 p_notes => p_notes ,
2171 x_return_status => x_return_status ,
2172 x_msg_count => x_msg_count ,
2173 x_msg_data => x_msg_data );
2174
2175 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2176 RAISE FND_API.G_EXC_ERROR;
2177 END IF;
2178 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2179 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2180 END IF;
2181
2182 END LOOP;
2183 --This is to delete any new recipients who got added while we were trying to delete existing recipients.
2184 --This code is to handle issues arising out of concurrency among multiple admins of a shared cart
2185 tbl_counter := 1;
2186
2187 FOR rec_get_recipients in c_get_recipients(p_quote_header_id) LOOP
2188 l_quote_access_tbl_tmp(tbl_counter).quote_sharee_id := rec_get_recipients.quote_sharee_id;
2189 l_quote_access_tbl_tmp(tbl_counter).party_id := rec_get_recipients.party_id;
2190 --dbms_output.put_line('found party_id: '||rec_get_recipient_info.party_id);
2191 l_quote_access_tbl_tmp(tbl_counter).cust_account_id := rec_get_recipients.cust_account_id;
2192 l_quote_access_tbl_tmp(tbl_counter).contact_point_id := rec_get_recipients.contact_point_id;
2193 l_quote_access_tbl_tmp(tbl_counter).quote_header_id := p_quote_header_id;
2194 l_quote_access_tbl_tmp(tbl_counter).quote_sharee_number := rec_get_recipients.quote_sharee_number;
2195 l_quote_access_tbl_tmp(tbl_counter).email_contact_address := rec_get_recipients.email_address;
2196 l_quote_access_tbl_tmp(tbl_counter).shared_by_party_id := rec_get_recipients.shared_by_party_id;
2197 l_quote_access_tbl_tmp(tbl_counter).notify_flag := FND_API.G_FALSE;
2198 tbl_counter := tbl_counter+1;
2199 EXIT when c_get_recipients%notfound;
2200 END LOOP;
2201
2202 FOR i in 1.. l_quote_access_tbl_tmp.count LOOP
2203 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2204 IBE_UTIL.DEBUG('STOP_SHARING: Concurrency part begins');
2205 IBE_UTIL.DEBUG('STOP_SHARING:l_quote_access_tbl:'||i||':quote_sharee_id: '||l_quote_access_tbl_tmp(i).quote_sharee_id);
2206 IBE_UTIL.DEBUG('STOP_SHARING:l_quote_access_tbl:'||i||':contact_point_id: '||l_quote_access_tbl_tmp(i).contact_point_id);
2207 IBE_UTIL.DEBUG('STOP_SHARING:l_quote_access_tbl:'||i||':party_id: '||l_quote_access_tbl_tmp(i).party_id);
2208 IBE_UTIL.DEBUG('STOP_SHARING:l_quote_access_tbl:'||i||':cust_account_id: '||l_quote_access_tbl_tmp(i).cust_account_id);
2209 END IF;
2210 --dbms_output.put_line('STOP_SHARING:l_quote_access_tbl:'||i||':quote_sharee_id: '||l_quote_access_tbl_tmp(i).quote_sharee_id);
2211 --dbms_output.put_line('Calling delete recipient for the recipient record ');
2212 IBE_QUOTE_SAVESHARE_V2_PVT.DELETE_RECIPIENT(
2213 P_Quote_access_rec => l_quote_access_tbl_tmp(i) ,
2214 p_minisite_id => p_minisite_id ,
2215 p_delete_code => p_delete_context ,
2216 p_notes => FND_API.G_MISS_CHAR ,
2217 x_return_status => x_return_status ,
2218 x_msg_count => x_msg_count ,
2219 x_msg_data => x_msg_data );
2220
2221 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2222 RAISE FND_API.G_EXC_ERROR;
2223 END IF;
2224 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2225 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2226 END IF;
2227 END LOOP;
2228 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2229 IBE_UTIL.DEBUG('STOP_SHARING: Concurrency part ends');
2230 END IF;
2231
2232 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2233 IBE_UTIL.DEBUG('IBE_QUOTE_SAVESHARE_V2_PVT.STOP_SHARING: END');
2234 END IF;
2235
2236 EXCEPTION
2237
2238 WHEN FND_API.G_EXC_ERROR THEN
2239 ROLLBACK TO STOPSHARING_V2;
2240 x_return_status := FND_API.G_RET_STS_ERROR;
2241 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
2242 p_count => x_msg_count ,
2243 p_data => x_msg_data);
2244 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2245 IBE_Util.Debug('Expected error in IBE_QUOTE_SAVESHARE_V2_PVT.STOPSHARING_V2');
2246 END IF;
2247
2248 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2249 ROLLBACK TO STOPSHARING_V2;
2250 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2251 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
2252 p_count => x_msg_count ,
2253 p_data => x_msg_data);
2254 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2255 IBE_Util.Debug('Unexpected error in IBE_QUOTE_SAVESHARE_V2_PVT.STOPSHARING_V2');
2256 END IF;
2257
2258 WHEN OTHERS THEN
2259 ROLLBACK TO STOPSHARING_V2;
2260 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2261 IF FND_Msg_Pub.Check_Msg_Level(FND_Msg_Pub.G_MSG_LVL_UNEXP_ERROR) THEN
2262 FND_Msg_Pub.Add_Exc_Msg(G_PKG_NAME,
2263 l_api_name);
2264 END IF;
2265 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
2266 p_count => x_msg_count ,
2267 p_data => x_msg_data);
2268 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2269 IBE_Util.Debug('Unknown error in IBE_QUOTE_SAVESHARE_V2_PVT.STOPSHARING_V2');
2270 END IF;
2271
2272 END;
2273
2274 /*To handle the specific removing of a recipients from one cartId
2275 *Usages:
2276 -"End Working" button
2277 */
2278 Procedure end_working (
2279 p_quote_access_tbl IN IBE_QUOTE_SAVESHARE_pvt.QUOTE_ACCESS_Tbl_Type
2280 := IBE_QUOTE_SAVESHARE_pvt.G_miss_quote_access_Tbl ,
2281 P_Quote_header_id IN Number ,
2282 P_Party_id IN Number := FND_API.G_MISS_NUM ,
2283 P_Cust_account_id IN Number := FND_API.G_MISS_NUM ,
2284 p_retrieval_number IN Number := FND_API.G_MISS_NUM ,
2285 P_URL IN Varchar2 := FND_API.G_MISS_CHAR ,
2286 P_minisite_id IN Number := FND_API.G_MISS_NUM ,
2287 p_notes IN Varchar2 := FND_API.G_MISS_CHAR ,
2288 p_api_version IN NUMBER := 1 ,
2289 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
2290 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
2291 x_return_status OUT NOCOPY VARCHAR2 ,
2292 x_msg_count OUT NOCOPY NUMBER ,
2293 x_msg_data OUT NOCOPY VARCHAR2 ) is
2294
2295 l_api_name CONSTANT VARCHAR2(30) := 'ENDWORKING_V2';
2296 l_api_version CONSTANT NUMBER := 1.0;
2297 l_recip_id NUMBER;
2298 l_initiator_id NUMBER;
2299 l_quote_access_tbl IBE_QUOTE_SAVESHARE_pvt.QUOTE_ACCESS_Tbl_Type
2300 := IBE_QUOTE_SAVESHARE_pvt.G_miss_quote_access_Tbl;
2301 END_WORKING NUMBER := 4;
2302
2303 CURSOR c_get_recip_id(c_retrieval_num NUMBER,
2304 c_party_id NUMBER,
2305 c_cust_account_id NUMBER,
2306 c_quote_header_id NUMBER) is
2307 SELECT quote_sharee_id, quote_header_id
2308 FROM IBE_SH_QUOTE_ACCESS
2309 WHERE quote_sharee_number = c_retrieval_num
2310 OR (party_id = c_party_id and cust_account_id = c_cust_account_id)
2311 AND quote_header_id = c_quote_header_id
2312 AND nvl(end_date_active, sysdate+1) > sysdate;
2313
2314 rec_get_recip_id c_get_recip_id%rowtype;
2315 l_quote_access_rec IBE_QUOTE_SAVESHARE_pvt.QUOTE_ACCESS_REC_TYPE
2316 := IBE_QUOTE_SAVESHARE_pvt.G_MISS_QUOTE_ACCESS_REC;
2317 l_quote_access_rec_recip IBE_QUOTE_SAVESHARE_pvt.QUOTE_ACCESS_REC_TYPE
2318 := IBE_QUOTE_SAVESHARE_pvt.G_MISS_QUOTE_ACCESS_REC;
2319
2320 cursor c_get_recipient_party(c_recipient_id NUMBER) is
2321 SELECT party_id,
2322 quote_sharee_number,
2323 quote_header_id,
2324 update_privilege_type_code,
2325 contact_point_id
2326 FROM ibe_sh_quote_access
2327 where quote_sharee_id = c_recipient_id;
2328
2329 rec_get_recipient_party c_get_recipient_party%rowtype;
2330
2331 BEGIN
2332 SAVEPOINT ENDWORKING_V2;
2333 -- Standard call to check for call compatibility.
2334 IF NOT FND_API.Compatible_API_Call ( l_api_version,
2335 P_Api_Version,
2336 l_api_name,
2337 G_PKG_NAME )
2338 THEN
2339 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2340 END IF;
2341 -- Initialize message list IF p_init_msg_list is set to TRUE.
2342 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2343 FND_MSG_PUB.initialize;
2344 END IF;
2345
2346 -- Initialize API return status to success
2347 x_return_status := FND_API.G_RET_STS_SUCCESS;
2348 -----------------------------------------------------------------------------------------
2349 --API Body start
2350 -----------------------------------------------------------------------------------------
2351 -- l_quote_access_rec := p_quote_access_rec;
2352 FOR rec_userenv_partyid in c_userenv_partyid LOOP
2353 l_initiator_id := rec_userenv_partyid.customer_id;
2354 EXIT when c_userenv_partyid%notfound;
2355 END LOOP;
2356
2357 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2358 IBE_UTIL.DEBUG('IBE_QUOTE_SAVESHARE_V2_PVT.END_WORKING: START');
2359 END IF;
2360 --DBMS_OUTPUT.PUT_LINE('IBE_QUOTE_SAVESHARE_V2_PVT.END_WORKING: START');
2361 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2362 IBE_UTIL.DEBUG('Opening cursor to retrieve the recipient_id');
2363 END IF;
2364 --DBMS_OUTPUT.PUT_LINE('Opening cursor to retrieve the recipient_id');
2365 --DBMS_OUTPUT.PUT_LINE('p_quote_access_rec.quote_sharee_number: '||p_quote_access_rec.quote_sharee_number);
2366 --DBMS_OUTPUT.PUT_LINE('p_quote_access_rec.party_id: '||p_quote_access_rec.party_id);
2367 --DBMS_OUTPUT.PUT_LINE('p_quote_access_rec.cust_account_id: '||p_quote_access_rec.cust_account_id);
2368 --DBMS_OUTPUT.PUT_LINE('p_quote_access_rec.quote_header_id: '||p_quote_access_rec.quote_header_id);
2369
2370 l_quote_access_tbl:= p_quote_access_tbl;
2371 FOR rec_get_recip_id in c_get_recip_id(p_retrieval_number,
2372 p_party_id,
2373 p_cust_account_id,
2374 p_quote_header_id) LOOP
2375 l_quote_access_rec.quote_sharee_id := rec_get_recip_id.quote_sharee_id;
2376 l_quote_access_rec.quote_sharee_number := p_retrieval_number;
2377 l_quote_access_rec.party_id := p_party_id;
2378 l_quote_access_rec.cust_account_id := p_cust_account_id;
2379 l_quote_access_rec.quote_header_id := rec_get_recip_id.quote_header_id;
2380 EXIT WHEN c_get_recip_id%NOTFOUND;
2381 END LOOP;
2382 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2383 IBE_UTIL.DEBUG('Recipient id obtained is: '||l_quote_access_rec.quote_sharee_id);
2384 END IF;
2385
2386 --DBMS_OUTPUT.PUT_LINE('Recipient id obtained is: '||l_quote_access_rec.quote_sharee_id);
2387 IBE_QUOTE_SAVESHARE_V2_PVT.DELETE_RECIPIENT(
2388 P_Quote_access_rec => l_quote_access_rec ,
2389 p_minisite_id => p_minisite_id ,
2390 p_delete_code => 'END_WORKING' ,
2391 p_url => p_url ,
2392 p_notes => p_notes ,
2393 x_return_status => x_return_status ,
2394 x_msg_count => x_msg_count ,
2395 x_msg_data => x_msg_data );
2396
2397 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2398 RAISE FND_API.G_EXC_ERROR;
2399 END IF;
2400 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2401 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2402 END IF;
2403
2404 FOR counter in 1..p_quote_access_tbl.count LOOP
2405 IF ((l_quote_access_tbl(counter).notify_flag = FND_API.G_TRUE) AND
2406 (l_quote_access_rec.quote_sharee_id <> l_quote_access_tbl(counter).quote_sharee_id)) THEN
2407
2408 FOR rec_get_recipient_party in c_get_recipient_party(p_quote_access_tbl(counter).quote_sharee_id) LOOP
2409 l_quote_access_rec_recip.party_id := rec_get_recipient_party.party_id;
2410 l_quote_access_rec_recip.quote_sharee_number := rec_get_recipient_party.quote_sharee_number;
2411 l_quote_access_rec_recip.quote_header_id := rec_get_recipient_party.quote_header_id;
2412 l_quote_access_rec_recip.update_privilege_type_code := rec_get_recipient_party.update_privilege_type_code;
2413 -- As part of bug fix 3349991 getting contact_point_id to the notification api.
2414 l_quote_access_rec_recip.contact_point_id := rec_get_recipient_party.contact_point_id;
2415 EXIT when c_get_recipient_party%NOTFOUND;
2416 END LOOP;
2417
2418 -- Assign the quote_sharee_id to l_quote_access_rec (value needed in the notification api,3349991)
2419 l_quote_access_rec_recip.quote_sharee_id := p_quote_access_tbl(counter).quote_sharee_id;
2420
2421 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2422 IBE_UTIL.DEBUG('IBE_QUOTE_SAVESHARE_V2_PVT.END_WORKING: Ready to call view shared cart');
2423 IBE_UTIL.DEBUG('IBE_QUOTE_SAVESHARE_V2_PVT.END_WORKING:initiator_party_id: '||l_initiator_id);
2424 IBE_UTIL.DEBUG('IBE_QUOTE_SAVESHARE_V2_PVT.END_WORKING:recipient party_id: '||l_quote_access_rec_recip.party_id);
2425 IBE_UTIL.DEBUG('IBE_QUOTE_SAVESHARE_V2_PVT.END_WORKING:recipient number: '||l_quote_access_rec_recip.quote_sharee_number);
2426 IBE_UTIL.DEBUG('IBE_QUOTE_SAVESHARE_V2_PVT.END_WORKING:recipient id: '||l_quote_access_rec_recip.quote_sharee_id);
2427 IBE_UTIL.DEBUG('IBE_QUOTE_SAVESHARE_V2_PVT.END_WORKING:contact_point_id: '||l_quote_access_rec_recip.contact_point_id);
2428 END IF;
2429
2430 IBE_WORKFLOW_PVT.Notify_view_shared_cart(
2431 p_api_version => p_api_version
2432 ,p_init_msg_list => p_init_msg_list
2433 ,p_quote_access_rec => l_quote_access_rec_recip
2434 ,p_minisite_id => p_minisite_id
2435 ,p_url => p_url
2436 ,p_notes => p_notes
2437 ,p_sent_by_party_id => l_initiator_id
2438 ,x_return_status => x_return_status
2439 ,x_msg_count => x_msg_count
2440 ,x_msg_data => x_msg_data );
2441 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2442 RAISE FND_API.G_EXC_ERROR;
2443 END IF;
2444 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2445 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2446 END IF;
2447 END IF;
2448 END LOOP;
2449
2450 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2451 IBE_UTIL.DEBUG('IBE_QUOTE_SAVESHARE_V2_PVT.END_WORKING: END');
2452 END IF;
2453 --DBMS_OUTPUT.PUT_LINE('IBE_QUOTE_SAVESHARE_V2_PVT.END_WORKING: END ');
2454 EXCEPTION
2455 WHEN FND_API.G_EXC_ERROR THEN
2456 ROLLBACK TO ENDWORKING_V2;
2457 x_return_status := FND_API.G_RET_STS_ERROR;
2458 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
2459 p_count => x_msg_count ,
2460 p_data => x_msg_data);
2461 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2462 IBE_Util.Debug('Expected error in IBE_QUOTE_SAVESHARE_V2_PVT.ENDWORKING');
2463 END IF;
2464
2465 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2466 ROLLBACK TO ENDWORKING_V2;
2467 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2468 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
2469 p_count => x_msg_count ,
2470 p_data => x_msg_data);
2471 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2472 IBE_Util.Debug('Unexpected error in IBE_QUOTE_SAVESHARE_V2_PVT.ENDWORKING)');
2473 END IF;
2474
2475 WHEN OTHERS THEN
2476 ROLLBACK TO ENDWORKING_V2;
2477 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2478 IF FND_Msg_Pub.Check_Msg_Level(FND_Msg_Pub.G_MSG_LVL_UNEXP_ERROR) THEN
2479 FND_Msg_Pub.Add_Exc_Msg(G_PKG_NAME,
2480 l_api_name);
2481 END IF;
2482 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
2483 p_count => x_msg_count ,
2484 p_data => x_msg_data);
2485 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2486 IBE_Util.Debug('Unknown error in IBE_QUOTE_SAVESHARE_V2_PVT.ENDWORKING');
2487 END IF;
2488
2489
2490 END;
2491 /*updates all recipients' access levels to readonly
2492 *Usages:
2493 -Status changes from "cart" to"quote" - request sales assistance, contract cart
2494 */
2495 Procedure share_readonly (
2496 p_quote_header_id IN Number ,
2497 P_minisite_id IN Number := FND_API.G_MISS_NUM,
2498 p_url IN Varchar2 := FND_API.G_MISS_CHAR,
2499 p_api_version IN NUMBER := 1 ,
2500 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
2501 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
2502 x_return_status OUT NOCOPY VARCHAR2 ,
2503 x_msg_count OUT NOCOPY NUMBER ,
2504 x_msg_data OUT NOCOPY VARCHAR2 ) is
2505
2506 l_api_name CONSTANT VARCHAR2(30) := 'SHAREREADONLY_V2';
2507 l_api_version CONSTANT NUMBER := 1.0;
2508 tbl_counter NUMBER := 1;
2509 l_initiator_id NUMBER;
2510
2511 l_quote_access_tbl IBE_QUOTE_SAVESHARE_pvt.QUOTE_ACCESS_Tbl_Type
2512 := IBE_QUOTE_SAVESHARE_pvt.g_miss_quote_access_tbl;
2513
2514 cursor c_get_recipients(c_qte_hdr_id NUMBER) is
2515 select quote_sharee_id,
2516 party_id,
2517 cust_account_id,
2518 quote_header_id
2519 from IBE_SH_QUOTE_ACCESS
2520 where quote_header_id = c_qte_hdr_id
2521 and nvl(end_date_active,sysdate+1) > sysdate;
2522
2523 rec_get_recipients c_get_recipients%rowtype;
2524
2525 BEGIN
2526
2527 SAVEPOINT SHAREREADONLY_V2;
2528 -- Standard call to check for call compatibility.
2529 IF NOT FND_API.Compatible_API_Call ( l_api_version,
2530 P_Api_Version,
2531 l_api_name,
2532 G_PKG_NAME )
2533 THEN
2534 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2535 END IF;
2536 -- Initialize message list IF p_init_msg_list is set to TRUE.
2537 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2538 FND_MSG_PUB.initialize;
2539 END IF;
2540
2541 -- Initialize API return status to success
2542 x_return_status := FND_API.G_RET_STS_SUCCESS;
2543 -------------------------------------------------------------------------------------------------------------
2544 --API Body start
2545 -------------------------------------------------------------------------------------------------------------
2546
2547 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2548 IBE_UTIL.DEBUG('IBE_QUOTE_SAVESHARE_V2_PVT.SHARE_READONLY: START');
2549 IBE_UTIL.DEBUG('IBE_QUOTE_SAVESHARE_V2_PVT.SHARE_READONLY:quote_header_id: '||p_quote_header_id);
2550 IBE_UTIL.DEBUG('IBE_QUOTE_SAVESHARE_V2_PVT.SHARE_READONLY: Querying initiator party id');
2551 END IF;
2552
2553 FOR rec_userenv_partyid in c_userenv_partyid LOOP
2554 l_initiator_id := rec_userenv_partyid.customer_id;
2555 EXIT when c_userenv_partyid%notfound;
2556 END LOOP;
2557
2558 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2559 IBE_UTIL.DEBUG('Building quote access table');
2560 END IF;
2561
2562 FOR rec_get_recipients in c_get_recipients(p_quote_header_id) LOOP
2563
2564 --Omitting the initiator's details as initiator is not required to get the notification
2565 IF (l_initiator_id <> nvl(rec_get_recipients.party_id,-1) ) THEN
2566 l_quote_access_tbl(tbl_counter).quote_sharee_id := rec_get_recipients.quote_sharee_id;
2567
2568 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2569 IBE_UTIL.DEBUG('Added sharee quote_sharee_id: '||rec_get_recipients.quote_sharee_id);
2570 END IF;
2571
2572 l_quote_access_tbl(tbl_counter).update_privilege_type_code := 'R';
2573 l_quote_access_tbl(tbl_counter).party_id := rec_get_recipients.party_id;
2574 l_quote_access_tbl(tbl_counter).cust_account_id := rec_get_recipients.cust_account_id;
2575 l_quote_access_tbl(tbl_counter).quote_header_id := rec_get_recipients.quote_header_id;
2576 l_quote_access_tbl(tbl_counter).operation_code := 'UPDATE';
2577 tbl_counter := tbl_counter+1;
2578 END IF;
2579 EXIT when c_get_recipients%notfound;
2580 END LOOP;
2581 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2582 IBE_UTIL.DEBUG('Done building quote access table');
2583 END IF;
2584
2585 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2586 IBE_UTIL.DEBUG('Calling save_recipients to update recipient access level');
2587 END IF;
2588 IBE_QUOTE_SAVESHARE_V2_PVT.save_recipients(
2589 P_Quote_access_tbl => l_quote_access_tbl ,
2590 P_Quote_header_id => P_Quote_header_id ,
2591 P_minisite_id => p_minisite_id ,
2592 p_url => p_url ,
2593 p_api_version => p_api_version ,
2594 p_init_msg_list => fnd_api.g_false ,
2595 p_commit => fnd_api.g_false ,
2596 x_return_status => x_return_status ,
2597 x_msg_count => x_msg_count ,
2598 x_msg_data => x_msg_data );
2599
2600 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2601 IBE_UTIL.DEBUG('Done calling save_recipients to update recipient access level');
2602 IBE_UTIL.DEBUG('IBE_QUOTE_SAVESHARE_V2_PVT.SHARE_READONLY: END');
2603 END IF;
2604
2605 EXCEPTION
2606
2607 WHEN FND_API.G_EXC_ERROR THEN
2608 ROLLBACK TO STOPSHARING_V2;
2609 x_return_status := FND_API.G_RET_STS_ERROR;
2610 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
2611 p_count => x_msg_count ,
2612 p_data => x_msg_data);
2613 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2614 IBE_Util.Debug('Expected error in IBE_QUOTE_SAVESHARE_V2_PVT.SHAREREADONLY_V2');
2615 END IF;
2616
2617 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2618 ROLLBACK TO STOPSHARING_V2;
2619 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2620 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
2621 p_count => x_msg_count ,
2622 p_data => x_msg_data);
2623 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2624 IBE_Util.Debug('Unexpected error in IBE_QUOTE_SAVESHARE_V2_PVT.SHAREREADONLY_V2');
2625 END IF;
2626
2627 WHEN OTHERS THEN
2628 ROLLBACK TO STOPSHARING_V2;
2629 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2630 IF FND_Msg_Pub.Check_Msg_Level(FND_Msg_Pub.G_MSG_LVL_UNEXP_ERROR) THEN
2631 FND_Msg_Pub.Add_Exc_Msg(G_PKG_NAME,
2632 l_api_name);
2633 END IF;
2634 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
2635 p_count => x_msg_count ,
2636 p_data => x_msg_data);
2637 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2638 IBE_Util.Debug('Unknown error in IBE_QUOTE_SAVESHARE_V2_PVT.SHAREREADONLY_V2');
2639 END IF;
2640 END;
2641
2642 /*To delete a recipient (a sort of wrapper around the "raw" delete table hander)
2643 *Usages:
2644 -To remove a recipient from the list of persons to whom a cart is shared.
2645 -All deletes for a recipient should be done using this api.
2646 -As of IBE.P - p_quote_access_rec must have the shared_by_party_id set
2647 */
2648 Procedure DELETE_RECIPIENT (
2649 P_Quote_access_rec IN IBE_QUOTE_SAVESHARE_pvt.QUOTE_ACCESS_REC_TYPE
2650 := IBE_QUOTE_SAVESHARE_pvt.G_MISS_QUOTE_ACCESS_REC ,
2651 P_minisite_id IN NUMBER ,
2652 p_delete_code IN VARCHAR2 := 'IBE_SC_CART_STOPSHARING' ,
2653 p_url IN VARCHAR2 := FND_API.G_MISS_CHAR ,
2654 p_notes IN VARCHAR2 := FND_API.G_MISS_CHAR ,
2655 p_api_version IN NUMBER := 1 ,
2656 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
2657 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
2658 x_return_status OUT NOCOPY VARCHAR2 ,
2659 x_msg_count OUT NOCOPY NUMBER ,
2660 x_msg_data OUT NOCOPY VARCHAR2 ) is
2661
2662 l_api_name CONSTANT VARCHAR2(30) := 'DELETERECIPIENT_V2';
2663 l_api_version CONSTANT NUMBER := 1.0;
2664 l_url VARCHAR2(2000);
2665 l_owner_partyid NUMBER;
2666 l_owner_accountid NUMBER;
2667 l_contact_point_rec HZ_CONTACT_POINT_V2PUB.CONTACT_POINT_REC_TYPE;
2668 l_party_id NUMBER;
2669 l_contact_point_id NUMBER;
2670 l_object_version_number NUMBER;
2671
2672
2673 /* Fixed the query for the bug 4860734 */
2674 cursor c_find_active_cart is
2675 select a.quote_header_id, a.party_id ,a.cust_account_id
2676 from IBE_ACTIVE_QUOTES a, ibe_sh_quote_access b
2677 where a.party_id = b.party_id
2678 and a.cust_account_id = b.cust_account_id
2679 and b.quote_sharee_id = P_Quote_access_rec.quote_sharee_id
2680 and nvl(b.end_date_active, sysdate+1) > sysdate
2681 and a.quote_header_id = p_quote_access_rec.quote_header_id
2682 and record_type= 'CART';
2683
2684 cursor c_get_owner_ids(c_quote_id number) is
2685 select party_id, cust_account_id
2686 from ASO_QUOTE_HEADERS_ALL
2687 where quote_header_id = c_quote_id;
2688
2689 cursor c_get_contact_point_ovn(c_quote_sharee_id Number) IS
2690 select QUOTE_ACCESS.party_id, CNTCT_POINTS.contact_point_id, CNTCT_POINTS.object_version_number
2691 from hz_contact_points CNTCT_POINTS, ibe_sh_quote_access QUOTE_ACCESS
2692 where CNTCT_POINTS.contact_point_id = QUOTE_ACCESS.contact_point_id
2693 and quote_sharee_id = c_quote_sharee_id;
2694
2695 rec_find_active_cart c_find_active_cart%rowtype;
2696 rec_get_owner_ids c_get_owner_ids%rowtype;
2697
2698 l_delete_context VARCHAR2(2000);
2699 l_shared_by_party_id NUMBER;
2700
2701 BEGIN
2702
2703 SAVEPOINT DELETERECIPIENT_V2;
2704 -- Standard call to check for call compatibility.
2705 IF NOT FND_API.Compatible_API_Call ( l_api_version,
2706 P_Api_Version,
2707 l_api_name,
2708 G_PKG_NAME )
2709 THEN
2710 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2711 END IF;
2712 -- Initialize message list IF p_init_msg_list is set to TRUE.
2713 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2714 FND_MSG_PUB.initialize;
2715 END IF;
2716
2717 -- Initialize API return status to success
2718 x_return_status := FND_API.G_RET_STS_SUCCESS;
2719 -------------------------------------------------------------------------------------------------------------
2720 --API Body start
2721 -------------------------------------------------------------------------------------------------------------
2722
2723 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2724 IBE_UTIL.DEBUG('IBE_QUOTE_SAVESHARE_V2_PVT.DELETE_RECIPIENT: START');
2725 END IF;
2726 FOR rec_find_active_cart in c_find_active_cart loop
2727 IF(rec_find_active_cart.quote_header_id is not null) THEN
2728 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2729 IBE_UTIL.DEBUG('IBE_QUOTE_SAVESHARE_V2_PVT.DELETE_RECIPIENT:user has an active cart');
2730 IBE_UTIL.DEBUG('Calling update row handler to deactivate the cart');
2731 END IF;
2732 --DBMS_OUTPUT.PUT_LINE('IBE_QUOTE_SAVESHARE_V2_PVT.DELETE_RECIPIENT:user has an active cart');
2733
2734 IBE_ACTIVE_QUOTES_ALL_PKG.UPDATE_ROW(
2735 X_OBJECT_VERSION_NUMBER => 1 ,
2736 X_QUOTE_HEADER_ID => null ,
2737 X_PARTY_ID => rec_find_active_cart.party_id ,
2738 X_CUST_ACCOUNT_ID => rec_find_active_cart.cust_account_id,
2739 X_RECORD_TYPE => 'CART' ,
2740 X_CURRENCY_CODE => null ,
2741 x_last_update_date => sysdate ,
2742 x_last_updated_by => fnd_global.user_id ,
2743 x_last_update_login => 1);
2744 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2745 IBE_UTIL.DEBUG('Done calling update row handler to deactivate the cart');
2746 END IF;
2747 END IF;
2748 EXIT WHEN c_find_active_cart%NOTFOUND;
2749 END LOOP;
2750 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2751 IBE_UTIL.DEBUG('Calling Update row handler on sh_quote_access table to end-date recipient row');
2752 END IF;
2753 --DBMS_OUTPUT.PUT_LINE('Calling Update row handler on sh_quote_access table to end-date recipient row');
2754
2755 open c_get_contact_point_ovn(P_Quote_access_rec.quote_sharee_id);
2756 fetch c_get_contact_point_ovn into l_party_id, l_contact_point_id, l_object_version_number;
2757
2758 if(l_party_id is NULL) THEN
2759 l_contact_point_rec.contact_point_id := l_contact_point_id;
2760 l_contact_point_rec.status := 'I';
2761 l_contact_point_rec.primary_flag := 'N';
2762 HZ_CONTACT_POINT_V2PUB.update_contact_point(
2763 p_init_msg_list => FND_API.G_FALSE,
2764 p_contact_point_rec => l_contact_point_rec,
2765 p_object_version_number => l_object_version_number,
2766 x_return_status => x_return_status,
2767 x_msg_count => x_msg_count,
2768 x_msg_data => x_msg_data);
2769
2770 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2771 RAISE FND_API.G_EXC_ERROR;
2772 END IF;
2773 END IF;
2774
2775 IBE_SH_QUOTE_ACCESS_PKG.update_Row(
2776 p_quote_sharee_id => P_Quote_access_rec.quote_sharee_id
2777 ,p_end_date_active => sysdate);
2778 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2779 IBE_UTIL.DEBUG('Done calling Update row handler on sh_quote_access table to end-date recipient row');
2780 END IF;
2781 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2782 IBE_UTIL.DEBUG('IBE_QUOTE_SAVESHARE_V2_PVT.DELETE_RECIPIENT: END');
2783 IBE_UTIL.DEBUG('delete_code is: '||p_delete_code);
2784 END IF;
2785
2786 IF (p_quote_access_rec.notify_flag = FND_API.G_TRUE) THEN
2787 --Notify_finish_sharing API with the appropriate delete_code
2788 IF (p_delete_code = 'END_WORKING') THEN
2789 /*obtain the owner party_id and acctid of the cart/quote being dealt with*/
2790 FOR rec_get_owner_ids in c_get_owner_ids(p_quote_access_rec.quote_header_id) LOOP
2791 l_owner_partyid := rec_get_owner_ids.party_id;
2792 l_owner_accountid := rec_get_owner_ids.cust_account_id;
2793 EXIT when c_get_owner_ids%notfound;
2794 END LOOP;
2795
2796 -- notification that goes to the owner (so and so has finished working this cart)
2797 -- for generic email to owner, we need to add some special parameters since we will not have a retrieval number
2798 l_url := p_url || '&opid=' || l_owner_partyid || '&oaid=' || l_owner_accountid;
2799 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2800 IBE_UTIL.DEBUG('url for generic email to OWNER: '||l_url);
2801 END IF;
2802
2803 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2804 IBE_UTIL.DEBUG('Calling notify_end_working API');
2805 END IF;
2806 IBE_WORKFLOW_PVT.Notify_End_Working(
2807 p_api_version => p_api_version ,
2808 p_init_msg_list => p_init_msg_list ,
2809 p_quote_header_id => p_quote_access_rec.quote_header_id ,
2810 p_party_id => p_quote_access_rec.party_id ,
2811 p_Cust_Account_Id => p_quote_access_rec.cust_account_id ,
2812 p_retrieval_number => p_quote_access_rec.quote_sharee_number,
2813 p_minisite_id => p_minisite_id ,
2814 p_url => l_url ,
2815 p_notes => p_notes ,
2816 x_return_status => x_return_status ,
2817 x_msg_count => x_msg_count ,
2818 x_msg_data => x_msg_data );
2819
2820 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2821 RAISE FND_API.G_EXC_ERROR;
2822 END IF;
2823 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2824 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2825 END IF;
2826 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2827 IBE_UTIL.DEBUG('Done calling notify_end_working API');
2828 END IF;
2829 ELSE
2830 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2831 IBE_UTIL.DEBUG('Calling notify_finish_sharing API');
2832 END IF;
2833 --DBMS_OUTPUT.PUT_LINE('Calling notify_stop_sharing API ');
2834 -- notification that goes to the member (you no longer have access to this cart)
2835 IBE_WORKFLOW_PVT.Notify_Finish_Sharing(
2836 p_api_version => p_api_version ,
2837 p_init_msg_list => p_init_msg_list ,
2838 p_quote_access_rec => p_quote_access_rec , --of the recepient
2839 p_minisite_id => p_minisite_id ,
2840 p_url => p_url ,
2841 p_context_code => p_delete_code ,
2842 p_shared_by_partyid=> p_quote_access_rec.shared_by_party_id,
2843 p_notes => p_notes ,
2844 x_return_status => x_return_status ,
2845 x_msg_count => x_msg_count ,
2846 x_msg_data => x_msg_data );
2847
2848 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2849 RAISE FND_API.G_EXC_ERROR;
2850 END IF;
2851 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2852 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2853 END IF;
2854 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2855 IBE_UTIL.DEBUG('Done calling notify_finish_sharing API');
2856 END IF;
2857 END IF; --end working or finish sharing
2858 END IF; --IF notify_flag is true
2859
2860 EXCEPTION
2861 WHEN FND_API.G_EXC_ERROR THEN
2862 ROLLBACK TO DELETERECIPIENT_V2;
2863 x_return_status := FND_API.G_RET_STS_ERROR;
2864 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
2865 p_count => x_msg_count ,
2866 p_data => x_msg_data);
2867 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2868 IBE_Util.Debug('Expected error in IBE_QUOTE_SAVESHARE_V2_PVT.DELETE_RECIPIENT()');
2869 END IF;
2870
2871 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2872 ROLLBACK TO DELETERECIPIENT_V2;
2873 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2874 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
2875 p_count => x_msg_count ,
2876 p_data => x_msg_data);
2877 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2878 IBE_Util.Debug('Unexpected error in IBE_QUOTE_SAVESHARE_V2_PVT.DELETE_RECIPIENT()');
2879 END IF;
2880
2881 WHEN OTHERS THEN
2882 ROLLBACK TO DELETERECIPIENT_V2;
2883 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2884 IF FND_Msg_Pub.Check_Msg_Level(FND_Msg_Pub.G_MSG_LVL_UNEXP_ERROR) THEN
2885 FND_Msg_Pub.Add_Exc_Msg(G_PKG_NAME,
2886 l_api_name);
2887 END IF;
2888 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
2889 p_count => x_msg_count ,
2890 p_data => x_msg_data);
2891 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2892 IBE_Util.Debug('Unknown error in IBE_QUOTE_SAVESHARE_V2_PVT.DELETE_RECIPIENT()');
2893 END IF;
2894
2895 END;
2896
2897 PROCEDURE Validate_share_Update(
2898 p_api_version_number IN NUMBER := 1.0
2899 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
2900 ,p_quote_header_rec IN ASO_QUOTE_PUB.Qte_Header_Rec_Type
2901 ,p_quote_access_tbl IN IBE_QUOTE_SAVESHARE_pvt.QUOTE_ACCESS_Tbl_Type
2902 := IBE_QUOTE_SAVESHARE_pvt.G_miss_quote_access_Tbl
2903 -- partyid and accountid cannot be gmiss coming in
2904 ,p_party_id IN NUMBER
2905 ,p_cust_account_id IN NUMBER
2906 ,p_retrieval_number IN NUMBER := FND_API.G_MISS_NUM
2907 ,p_operation_code IN VARCHAR2
2908 ,x_return_status OUT NOCOPY VARCHAR2
2909 ,x_msg_count OUT NOCOPY NUMBER
2910 ,x_msg_data OUT NOCOPY VARCHAR2
2911 )
2912
2913 is
2914
2915 l_api_name CONSTANT VARCHAR2(30) := 'Validate_share_Update';
2916 l_api_version CONSTANT NUMBER := 1.0;
2917 l_check_updates VARCHAR2(1) := FND_API.G_FALSE; -- checks timestamps and if updates are really necessary
2918 l_check_onlynotify VARCHAR2(1) := FND_API.G_FALSE;
2919 l_is_owner VARCHAR2(1) := FND_API.G_TRUE;
2920 l_owner_party_id NUMBER;
2921 l_userenv_party_id NUMBER;
2922 l_access_level VARCHAR2(30) := null;
2923 l_db_last_update_date DATE;
2924 l_db_end_date_active DATE;
2925 l_db_access_level VARCHAR2(30) := null;
2926 l_db_quote_access_tbl IBE_QUOTE_SAVESHARE_pvt.QUOTE_ACCESS_Tbl_Type;
2927 l_env_user_id NUMBER;
2928
2929 cursor c_get_owner_partyid(c_quote_id number) is
2930 select party_id
2931 from ASO_QUOTE_HEADERS_ALL
2932 where quote_header_id = c_quote_id;
2933
2934 rec_get_owner_partyid c_get_owner_partyid%rowtype;
2935
2936 cursor c_get_role_by_retr_num(c_retrieval_number number, c_party_id number, c_acct_id number) is
2937 select update_privilege_type_code
2938 from ibe_sh_quote_access
2939 where quote_sharee_number = c_retrieval_number
2940 and (party_id is null or party_id = c_party_id)
2941 and (cust_account_id is null or cust_account_id = c_acct_id);
2942
2943 rec_get_role_by_retr_num c_get_role_by_retr_num%rowtype;
2944
2945 cursor c_get_role_by_user(c_party_id number, c_account_id number, c_qte_hdr_id number) is
2946 select update_privilege_type_code
2947 from ibe_sh_quote_access
2948 where quote_header_id = c_qte_hdr_id
2949 and party_id = c_party_id
2950 and cust_account_id = c_account_id
2951 AND nvl(end_date_active, sysdate+1) > sysdate;
2952
2953
2954 rec_get_role_by_user c_get_role_by_user%rowtype;
2955
2956 cursor c_get_recipient_info(c_recipient_id number) is
2957 select last_update_date, last_updated_by, end_date_active, update_privilege_type_code
2958 from ibe_sh_quote_access
2959 where quote_sharee_id = c_recipient_id;
2960
2961 rec_get_recipient_info c_get_recipient_info%rowtype;
2962
2963 BEGIN
2964
2965 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2966 IBE_UTIL.DEBUG('Begin validate_share_update : ' || to_char(sysdate, 'mm/dd/yyyy:hh24:MI:SS'));
2967 END IF;
2968
2969 -- Standard call to check for call compatibility.
2970 IF NOT FND_API.Compatible_API_Call (l_api_version,
2971 p_api_version_number,
2972 l_api_name,
2973 G_PKG_NAME )
2974 THEN
2975 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2976 END IF;
2977
2978 -- Initialize message list if p_init_msg_list is set to TRUE.
2979
2980 IF FND_API.To_Boolean( p_init_msg_list ) THEN
2981 FND_Msg_Pub.initialize;
2982 END IF;
2983
2984 -- Initialize API return status to success
2985 x_return_status := FND_API.G_RET_STS_SUCCESS;
2986
2987 -- API body
2988 -- start by returning the input
2989
2990 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2991 IBE_UTIL.DEBUG('Incoming quote_header_id is :'||p_quote_header_rec.quote_header_id);
2992 IBE_UTIL.DEBUG('Incoming operation_code is :'||p_operation_code);
2993 IBE_UTIL.DEBUG('Incoming party_id is :'||p_party_id);
2994 IBE_UTIL.DEBUG('Incoming account_id is :'||p_cust_account_id);
2995 IBE_UTIL.DEBUG('Incoming retrieval_number is :'||p_retrieval_number);
2996 END IF;
2997
2998 /* FOR rec_userenv_partyid in c_userenv_partyid LOOP
2999 l_userenv_party_id := rec_userenv_partyid.customer_id;
3000 EXIT when c_userenv_partyid%notfound;
3001 END LOOP;
3002 */
3003 FOR rec_get_owner_partyid in c_get_owner_partyid(p_quote_header_rec.quote_header_id) LOOP
3004 l_owner_party_id := rec_get_owner_partyid.party_id;
3005 EXIT when c_get_owner_partyid%notfound;
3006 END LOOP;
3007 ------------ USER VALIDATION - must be either owner or valid recipient ------------------------------
3008 if (l_owner_party_id <> p_party_id) then
3009 l_is_owner := FND_API.G_FALSE;
3010 else
3011 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3012 IBE_Util.Debug('Initiator is OWNER');
3013 END IF;
3014 end if;
3015
3016 -- if ((l_is_owner = FND_API.G_FALSE) and (p_operation_code <> OP_END_WORKING)) then
3017 if (l_is_owner = FND_API.G_FALSE) then
3018 -- if user is not the owner then we need to make sure he is a valid member
3019 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3020 IBE_Util.Debug('Initiator may be RECIPIENT');
3021 END IF;
3022 if ((p_retrieval_number is not null) and (p_retrieval_number <> FND_API.G_MISS_NUM)) then
3023 FOR rec_get_role_by_retr_num in c_get_role_by_retr_num(p_retrieval_number, p_party_id, p_cust_account_id) LOOP
3024 l_access_level := rec_get_role_by_retr_num.update_privilege_type_code;
3025 EXIT when c_get_role_by_retr_num%notfound;
3026 END LOOP;
3027 else
3028 FOR rec_get_role_by_user in c_get_role_by_user(p_party_id, p_cust_account_id,p_quote_header_rec.quote_header_id) LOOP
3029 l_access_level := rec_get_role_by_user.update_privilege_type_code;
3030 EXIT when c_get_role_by_user%notfound;
3031 END LOOP;
3032 end if;
3033 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3034 IBE_Util.Debug('RECIPIENT access level: ' || l_access_level);
3035 END IF;
3036 if (l_access_level is null) then
3037 IF FND_Msg_Pub.Check_Msg_Level (FND_Msg_Pub.G_MSG_LVL_ERROR) THEN
3038 FND_Message.Set_Name('IBE', 'IBE_SC_ERR_USERACCESS');
3039 FND_Msg_Pub.Add;
3040 END IF;
3041 -- need to raise an error that the user no longer has access to this cart
3042 RAISE FND_API.G_EXC_ERROR;
3043 end if;
3044 end if;
3045
3046 ------------ DETERMINE WHICH VALIDATIONS TO DO BASED ON OP CODE ------------------------------
3047 -- owner only operations
3048 if (p_operation_code in (OP_APPEND, OP_NAME_CART, OP_SAVE_CART_AND_RECIPIENTS)) then
3049 if (l_is_owner = FND_API.G_FALSE) then
3050 IF FND_Msg_Pub.Check_Msg_Level (FND_Msg_Pub.G_MSG_LVL_ERROR) THEN
3051 FND_Message.Set_Name('IBE', 'IBE_SC_ERR_PRIVILEGE');
3052 FND_Msg_Pub.Add;
3053 END IF;
3054 -- raise an error that the user does not have this privilege
3055 RAISE FND_API.G_EXC_ERROR;
3056 end if;
3057 if (p_operation_code = OP_SAVE_CART_AND_RECIPIENTS) then
3058 l_check_updates := FND_API.G_TRUE;
3059 end if;
3060 -- owner or admin operations
3061 elsif (p_operation_code in (OP_STOP_SHARING, OP_DELETE_CART)) then
3062 if ((l_is_owner = FND_API.G_FALSE) and (l_access_level <> 'A')) then
3063 IF FND_Msg_Pub.Check_Msg_Level (FND_Msg_Pub.G_MSG_LVL_ERROR) THEN
3064 FND_Message.Set_Name('IBE', 'IBE_SC_ERR_PRIVILEGE');
3065 FND_Msg_Pub.Add;
3066 END IF;
3067 -- raise an error that the user does not have this privilege
3068 RAISE FND_API.G_EXC_ERROR;
3069 end if;
3070 l_check_onlynotify := FND_API.G_TRUE;
3071 l_check_updates := FND_API.G_TRUE;
3072 -- any role, but different checks depending on role
3073 elsif (p_operation_code = OP_SAVE_RECIPIENTS) then
3074 if ((l_is_owner = FND_API.G_FALSE) and (l_access_level <> 'A')) then
3075 -- if not the owner or not admin, then make sure user is only notifying
3076 l_check_onlynotify := FND_API.G_TRUE;
3077 end if;
3078 l_check_updates := FND_API.G_TRUE;
3079 elsif ((p_operation_code = OP_ACTIVATE_QUOTE) or (p_operation_code = OP_DEACTIVATE)) then
3080 l_check_updates := FND_API.G_FALSE; -- no validations to do here that have not already been done
3081 end if; -- end if else over operation_code
3082
3083 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3084 IBE_UTIL.DEBUG('l_check_onlynotify is :'||l_check_onlynotify);
3085 IBE_UTIL.DEBUG('l_check_updates is :'||l_check_updates);
3086 END IF;
3087
3088 ------------ VALIDATIONS ON THE INPUT QUOTE ACCESS TABLE ------------------------------
3089 if ((l_check_updates = FND_API.G_TRUE) or (l_check_onlynotify = FND_API.G_TRUE)) then
3090 l_env_user_id := FND_GLOBAL.USER_ID;
3091 FOR i in 1..p_quote_access_tbl.count LOOP
3092 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3093 IBE_UTIL.DEBUG('current quote_sharee_id : '||p_quote_access_tbl(i).quote_sharee_id);
3094 end if;
3095 -- populate a local table with all the db data - but index it according to the input table
3096 -- in this loop, we will check every opcode if necessary
3097 -- down below, we will end up only checking the ones that have a shareeid
3098 l_db_quote_access_tbl(i).operation_code := p_quote_access_tbl(i).operation_code;
3099 if ((p_quote_access_tbl(i).quote_sharee_id is not null)
3100 and (p_quote_access_tbl(i).quote_sharee_id <> fnd_api.g_miss_num)) then
3101 --cursor query to get last_update_date, contact_point_id, end_date_active
3102 FOR rec_get_recipient_info in c_get_recipient_info(p_quote_access_tbl(i).quote_sharee_id) LOOP
3103 l_db_quote_access_tbl(i).last_update_date := rec_get_recipient_info.last_update_date;
3104 l_db_quote_access_tbl(i).last_updated_by := rec_get_recipient_info.last_updated_by;
3105 l_db_quote_access_tbl(i).end_date_active := rec_get_recipient_info.end_date_active;
3106 l_db_quote_access_tbl(i).update_privilege_type_code := rec_get_recipient_info.update_privilege_type_code;
3107 EXIT when c_get_recipient_info%notfound;
3108 END LOOP;
3109 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3110 IBE_UTIL.DEBUG('input op_code : '||p_quote_access_tbl(i).operation_code);
3111 IBE_UTIL.DEBUG('input last_update_date : '||to_char(p_quote_access_tbl(i).last_update_date,'mm/dd/yyyy:hh24:MI:SS'));
3112 IBE_UTIL.DEBUG('db last_update_date : '||to_char(l_db_quote_access_tbl(i).last_update_date,'mm/dd/yyyy:hh24:MI:SS'));
3113 IBE_UTIL.DEBUG('input access_level : '||p_quote_access_tbl(i).update_privilege_type_code);
3114 IBE_UTIL.DEBUG('db access_level : '||l_db_quote_access_tbl(i).update_privilege_type_code);
3115 end if;
3116 -- do the opcode permissions check as we loop
3117 if (l_check_onlynotify = FND_API.G_TRUE) then
3118 -- make sure we are not creating, deleting, or doing any real updates
3119 if ((p_quote_access_tbl(i).operation_code = 'DELETE')
3120 or(p_quote_access_tbl(i).operation_code = 'CREATE')
3121 or ((p_quote_access_tbl(i).operation_code = 'UPDATE')
3122 and (p_quote_access_tbl(i).update_privilege_type_code is not null)
3123 and (p_quote_access_tbl(i).update_privilege_type_code <> FND_API.G_MISS_CHAR)
3124 and (p_quote_access_tbl(i).update_privilege_type_code <> l_db_quote_access_tbl(i).update_privilege_type_code))) then
3125 IF FND_Msg_Pub.Check_Msg_Level (FND_Msg_Pub.G_MSG_LVL_ERROR) THEN
3126 FND_Message.Set_Name('IBE', 'IBE_SC_ERR_PRIVILEGE');
3127 FND_Msg_Pub.Add;
3128 END IF;
3129 RAISE FND_API.G_EXC_ERROR;
3130 end if;
3131 end if; -- end check onlynotify
3132 end if; -- end if we have a quote_sharee_id
3133 end loop; -- end loop over input access tbl
3134 -- second loop to check last update dates
3135 if (l_check_updates = FND_API.G_TRUE) then
3136 FOR i in 1..p_quote_access_tbl.count LOOP
3137 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3138 IBE_UTIL.DEBUG('current quote_sharee_id : '||p_quote_access_tbl(i).quote_sharee_id);
3139 end if;
3140 if ((p_quote_access_tbl(i).quote_sharee_id is not null)
3141 and (p_quote_access_tbl(i).quote_sharee_id <> fnd_api.g_miss_num)) then
3142
3143 if ((p_quote_access_tbl(i).last_update_date <> FND_API.G_MISS_DATE) and
3144 (p_quote_access_tbl(i).last_update_date <> l_db_quote_access_tbl(i).last_update_date)) then
3145 -- i.e. don't throw exception if we want to end a row and it's already enddated.
3146 if ((p_quote_access_tbl(i).operation_code = 'DELETE') and (nvl(l_db_quote_access_tbl(i).end_date_active,sysdate-1) < sysdate)) then
3147 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3148 IBE_UTIL.DEBUG('deleteing an end dated row, allowing it to pass through');
3149 end if;
3150 elsif (l_env_user_id = l_db_quote_access_tbl(i).last_updated_by) then
3151 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3152 IBE_UTIL.DEBUG('timestamps do not match, but user was the last to update the row so allowing it to go through');
3153 end if;
3154 else
3155 IF FND_Msg_Pub.Check_Msg_Level (FND_Msg_Pub.G_MSG_LVL_ERROR) THEN
3156 FND_Message.Set_Name('IBE', 'IBE_SC_ERR_MEMBERS_OUT_OF_SYNC');
3157 FND_Msg_Pub.Add;
3158 END IF;
3159 -- need a new error msg that the row has been updated by another user and to try again
3160 RAISE FND_API.G_EXC_ERROR;
3161 end if;
3162 end if; -- end if last_update_dates dont match
3163 end if; -- end if shareeid is not null
3164 end loop; -- end loop over input quote access tbl
3165 end if; -- end if doing check updates
3166 end if; -- end check of updates
3167
3168 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3169 IBE_UTIL.DEBUG('End validate_share_update' || to_char(sysdate, 'mm/dd/yyyy:hh24:MI:SS'));
3170 END IF;
3171
3172 EXCEPTION
3173 WHEN FND_API.G_EXC_ERROR THEN
3174 x_return_status := FND_API.G_RET_STS_ERROR;
3175 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
3176 p_count => x_msg_count ,
3177 p_data => x_msg_data);
3178 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3179 IBE_Util.Debug('End IBE_Quote_Misc_pvt.validate_share_update: expected error');
3180 END IF;
3181 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3182 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3183 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
3184 p_count => x_msg_count ,
3185 p_data => x_msg_data);
3186 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3187 IBE_Util.Debug('End IBE_Quote_Misc_pvt.validate_share_update: unexpected error');
3188 END IF;
3189 WHEN OTHERS THEN
3190 IF FND_Msg_Pub.Check_Msg_Level(FND_Msg_Pub.G_MSG_LVL_UNEXP_ERROR) THEN
3191 FND_Msg_Pub.Add_Exc_Msg(G_PKG_NAME,
3192 l_api_name);
3193 END IF;
3194 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
3195 p_count => x_msg_count ,
3196 p_data => x_msg_data);
3197 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3198 IBE_Util.Debug('End IBE_Quote_Misc_pvt.validate_share_update: other exception');
3199 END IF;
3200 END validate_share_update;
3201
3202 END IBE_QUOTE_SAVESHARE_V2_PVT;
[Home] [Help]