1 package body IBE_QUOTE_SAVESHARE_V2_PVT as
2 /*$Header: IBEVSS2B.pls 120.2.12020000.2 2012/09/10 06:46:57 nsatyava 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 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2116 IBE_UTIL.DEBUG('STOP_SHARING: :: l_sharing_party_id: '|| l_sharing_party_id );
2117 END IF;
2118
2119 --dbms_output.put_line('Input quote access is null ');
2120 FOR rec_get_recipients in c_get_recipients(p_quote_header_id) LOOP
2121 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2122 IBE_UTIL.DEBUG('STOP_SHARING:: '||tbl_counter||'. quote_sharee_id:'||rec_get_recipients.quote_sharee_id||', party_id:' || rec_get_recipients.party_id );
2123 END IF;
2124
2125 l_quote_access_tbl(tbl_counter).quote_sharee_id := rec_get_recipients.quote_sharee_id;
2126 l_quote_access_tbl(tbl_counter).party_id := rec_get_recipients.party_id;
2127 --dbms_output.put_line('found party_id: '||rec_get_recipient_info.party_id);
2128 l_quote_access_tbl(tbl_counter).cust_account_id := rec_get_recipients.cust_account_id;
2129 l_quote_access_tbl(tbl_counter).contact_point_id := rec_get_recipients.contact_point_id;
2130 l_quote_access_tbl(tbl_counter).quote_header_id := p_quote_header_id;
2131 l_quote_access_tbl(tbl_counter).quote_sharee_number := rec_get_recipients.quote_sharee_number;
2132 l_quote_access_tbl(tbl_counter).email_contact_address := rec_get_recipients.email_address;
2133 l_quote_access_tbl(tbl_counter).shared_by_party_id := rec_get_recipients.shared_by_party_id;
2134 -- 14109131 - STOP SHARED CART NOTIFICATION SENT WHEN RECIPIENT PLACES ORDER
2135 IF (l_sharing_party_id = rec_get_recipients.party_id) THEN
2136 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2137 IBE_UTIL.DEBUG('STOP_SHARING :: sharee party id is party id so making notify flag false ');
2138
2139 END IF;
2140 l_quote_access_tbl(tbl_counter).notify_flag := FND_API.G_FALSE;
2141 End IF;
2142 -- 14109131 - STOP SHARED CART NOTIFICATION SENT WHEN RECIPIENT PLACES ORDER
2143 tbl_counter := tbl_counter+1;
2144 EXIT when c_get_recipients%notfound;
2145 END LOOP;
2146 ELSE
2147 --dbms_output.put_line('Input quote access table is not null');
2148
2149 FOR tbl_counter in 1..p_quote_access_tbl.count LOOP
2150 --dbms_output.put_line('p_quote_access_tbl(tbl_counter).quote_sharee_id: '||p_quote_access_tbl(tbl_counter).quote_sharee_id);
2151 FOR rec_get_recipient_info in c_get_recipient_info(l_quote_access_tbl(tbl_counter).quote_sharee_id) LOOP
2152 IF l_sharing_party_id <> rec_get_recipient_info.party_id THEN
2153 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2154 IBE_UTIL.DEBUG('STOP_SHARING: '||tbl_counter||'. quote_sharee_id:'||p_quote_access_tbl(tbl_counter).quote_sharee_id||', party_id:' || rec_get_recipient_info.party_id ||', notify_flag:' || p_quote_access_tbl(tbl_counter).notify_flag );
2155 END IF;
2156
2157
2158 l_quote_access_tbl(tbl_counter).quote_sharee_id := p_quote_access_tbl(tbl_counter).quote_sharee_id;
2159 l_quote_access_tbl(tbl_counter).notify_flag := p_quote_access_tbl(tbl_counter).notify_flag;
2160 l_quote_access_tbl(tbl_counter).party_id := rec_get_recipient_info.party_id;
2161 --dbms_output.put_line('found party_id: '||rec_get_recipient_info.party_id);
2162 l_quote_access_tbl(tbl_counter).cust_account_id := rec_get_recipient_info.cust_account_id;
2163 l_quote_access_tbl(tbl_counter).contact_point_id := rec_get_recipient_info.contact_point_id;
2164 l_quote_access_tbl(tbl_counter).quote_header_id := p_quote_header_id;
2165 l_quote_access_tbl(tbl_counter).quote_sharee_number := rec_get_recipient_info.quote_sharee_number;
2166 l_quote_access_tbl(tbl_counter).email_contact_address := rec_get_recipient_info.email_address;
2167 l_quote_access_tbl(tbl_counter).shared_by_party_id := rec_get_recipient_info.shared_by_party_id;
2168 END IF;
2169 EXIT when c_get_recipient_info%notfound;
2170 END LOOP;
2171 END LOOP;
2172 END IF;
2173 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2174 IBE_UTIL.DEBUG('STOP_SHARING:calling delete_recipient on the following recipients:');
2175 END IF;
2176 --dbms_output.put_line('STOP_SHARING:calling delete_recipient on the following recipients:');
2177 FOR i in 1.. l_quote_access_tbl.count LOOP
2178 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2179 IBE_UTIL.DEBUG('STOP_SHARING:calling delete_recipient');
2180 IBE_UTIL.DEBUG('STOP_SHARING:l_quote_access_tbl:'||i||':quote_sharee_id: '||l_quote_access_tbl(i).quote_sharee_id);
2181 IBE_UTIL.DEBUG('STOP_SHARING:l_quote_access_tbl:'||i||':contact_point_id: '||l_quote_access_tbl(i).contact_point_id);
2182 IBE_UTIL.DEBUG('STOP_SHARING:l_quote_access_tbl:'||i||':party_id: '||l_quote_access_tbl(i).party_id);
2183 IBE_UTIL.DEBUG('STOP_SHARING:l_quote_access_tbl:'||i||':cust_account_id: '||l_quote_access_tbl(i).cust_account_id);
2184 END IF;
2185 --dbms_output.put_line('STOP_SHARING:l_quote_access_tbl:'||i||':quote_sharee_id: '||l_quote_access_tbl(i).quote_sharee_id);
2186 --dbms_output.put_line('Calling delete recipient for the recipient record ');
2187 IBE_QUOTE_SAVESHARE_V2_PVT.DELETE_RECIPIENT(
2188 P_Quote_access_rec => l_quote_access_tbl(i) ,
2189 p_minisite_id => p_minisite_id ,
2190 p_delete_code => p_delete_context ,
2191 p_notes => p_notes ,
2192 x_return_status => x_return_status ,
2193 x_msg_count => x_msg_count ,
2194 x_msg_data => x_msg_data );
2195
2196 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2197 RAISE FND_API.G_EXC_ERROR;
2198 END IF;
2199 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2200 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2201 END IF;
2202
2203 END LOOP;
2204 --This is to delete any new recipients who got added while we were trying to delete existing recipients.
2205 --This code is to handle issues arising out of concurrency among multiple admins of a shared cart
2206 tbl_counter := 1;
2207
2208 FOR rec_get_recipients in c_get_recipients(p_quote_header_id) LOOP
2209 l_quote_access_tbl_tmp(tbl_counter).quote_sharee_id := rec_get_recipients.quote_sharee_id;
2210 l_quote_access_tbl_tmp(tbl_counter).party_id := rec_get_recipients.party_id;
2211 --dbms_output.put_line('found party_id: '||rec_get_recipient_info.party_id);
2212 l_quote_access_tbl_tmp(tbl_counter).cust_account_id := rec_get_recipients.cust_account_id;
2213 l_quote_access_tbl_tmp(tbl_counter).contact_point_id := rec_get_recipients.contact_point_id;
2214 l_quote_access_tbl_tmp(tbl_counter).quote_header_id := p_quote_header_id;
2215 l_quote_access_tbl_tmp(tbl_counter).quote_sharee_number := rec_get_recipients.quote_sharee_number;
2216 l_quote_access_tbl_tmp(tbl_counter).email_contact_address := rec_get_recipients.email_address;
2217 l_quote_access_tbl_tmp(tbl_counter).shared_by_party_id := rec_get_recipients.shared_by_party_id;
2218 l_quote_access_tbl_tmp(tbl_counter).notify_flag := FND_API.G_FALSE;
2219 tbl_counter := tbl_counter+1;
2220 EXIT when c_get_recipients%notfound;
2221 END LOOP;
2222
2223 FOR i in 1.. l_quote_access_tbl_tmp.count LOOP
2224 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2225 IBE_UTIL.DEBUG('STOP_SHARING: Concurrency part begins');
2226 IBE_UTIL.DEBUG('STOP_SHARING:l_quote_access_tbl:'||i||':quote_sharee_id: '||l_quote_access_tbl_tmp(i).quote_sharee_id);
2227 IBE_UTIL.DEBUG('STOP_SHARING:l_quote_access_tbl:'||i||':contact_point_id: '||l_quote_access_tbl_tmp(i).contact_point_id);
2228 IBE_UTIL.DEBUG('STOP_SHARING:l_quote_access_tbl:'||i||':party_id: '||l_quote_access_tbl_tmp(i).party_id);
2229 IBE_UTIL.DEBUG('STOP_SHARING:l_quote_access_tbl:'||i||':cust_account_id: '||l_quote_access_tbl_tmp(i).cust_account_id);
2230 END IF;
2231 --dbms_output.put_line('STOP_SHARING:l_quote_access_tbl:'||i||':quote_sharee_id: '||l_quote_access_tbl_tmp(i).quote_sharee_id);
2232 --dbms_output.put_line('Calling delete recipient for the recipient record ');
2233 IBE_QUOTE_SAVESHARE_V2_PVT.DELETE_RECIPIENT(
2234 P_Quote_access_rec => l_quote_access_tbl_tmp(i) ,
2235 p_minisite_id => p_minisite_id ,
2236 p_delete_code => p_delete_context ,
2237 p_notes => FND_API.G_MISS_CHAR ,
2238 x_return_status => x_return_status ,
2239 x_msg_count => x_msg_count ,
2240 x_msg_data => x_msg_data );
2241
2242 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2243 RAISE FND_API.G_EXC_ERROR;
2244 END IF;
2245 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2246 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2247 END IF;
2248 END LOOP;
2249 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2250 IBE_UTIL.DEBUG('STOP_SHARING: Concurrency part ends');
2251 END IF;
2252
2253 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2254 IBE_UTIL.DEBUG('IBE_QUOTE_SAVESHARE_V2_PVT.STOP_SHARING: END');
2255 END IF;
2256
2257 EXCEPTION
2258
2259 WHEN FND_API.G_EXC_ERROR THEN
2260 ROLLBACK TO STOPSHARING_V2;
2261 x_return_status := FND_API.G_RET_STS_ERROR;
2262 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
2263 p_count => x_msg_count ,
2264 p_data => x_msg_data);
2265 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2266 IBE_Util.Debug('Expected error in IBE_QUOTE_SAVESHARE_V2_PVT.STOPSHARING_V2');
2267 END IF;
2268
2269 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2270 ROLLBACK TO STOPSHARING_V2;
2271 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2272 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
2273 p_count => x_msg_count ,
2274 p_data => x_msg_data);
2275 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2276 IBE_Util.Debug('Unexpected error in IBE_QUOTE_SAVESHARE_V2_PVT.STOPSHARING_V2');
2277 END IF;
2278
2279 WHEN OTHERS THEN
2280 ROLLBACK TO STOPSHARING_V2;
2281 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2282 IF FND_Msg_Pub.Check_Msg_Level(FND_Msg_Pub.G_MSG_LVL_UNEXP_ERROR) THEN
2283 FND_Msg_Pub.Add_Exc_Msg(G_PKG_NAME,
2284 l_api_name);
2285 END IF;
2286 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
2287 p_count => x_msg_count ,
2288 p_data => x_msg_data);
2289 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2290 IBE_Util.Debug('Unknown error in IBE_QUOTE_SAVESHARE_V2_PVT.STOPSHARING_V2');
2291 END IF;
2292
2293 END;
2294
2295 /*To handle the specific removing of a recipients from one cartId
2296 *Usages:
2297 -"End Working" button
2298 */
2299 Procedure end_working (
2300 p_quote_access_tbl IN IBE_QUOTE_SAVESHARE_pvt.QUOTE_ACCESS_Tbl_Type
2301 := IBE_QUOTE_SAVESHARE_pvt.G_miss_quote_access_Tbl ,
2302 P_Quote_header_id IN Number ,
2303 P_Party_id IN Number := FND_API.G_MISS_NUM ,
2304 P_Cust_account_id IN Number := FND_API.G_MISS_NUM ,
2305 p_retrieval_number IN Number := FND_API.G_MISS_NUM ,
2306 P_URL IN Varchar2 := FND_API.G_MISS_CHAR ,
2307 P_minisite_id IN Number := FND_API.G_MISS_NUM ,
2308 p_notes IN Varchar2 := FND_API.G_MISS_CHAR ,
2309 p_api_version IN NUMBER := 1 ,
2310 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
2311 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
2312 x_return_status OUT NOCOPY VARCHAR2 ,
2313 x_msg_count OUT NOCOPY NUMBER ,
2314 x_msg_data OUT NOCOPY VARCHAR2 ) is
2315
2316 l_api_name CONSTANT VARCHAR2(30) := 'ENDWORKING_V2';
2317 l_api_version CONSTANT NUMBER := 1.0;
2318 l_recip_id NUMBER;
2319 l_initiator_id NUMBER;
2320 l_quote_access_tbl IBE_QUOTE_SAVESHARE_pvt.QUOTE_ACCESS_Tbl_Type
2321 := IBE_QUOTE_SAVESHARE_pvt.G_miss_quote_access_Tbl;
2322 END_WORKING NUMBER := 4;
2323
2324 CURSOR c_get_recip_id(c_retrieval_num NUMBER,
2325 c_party_id NUMBER,
2326 c_cust_account_id NUMBER,
2327 c_quote_header_id NUMBER) is
2328 SELECT quote_sharee_id, quote_header_id
2329 FROM IBE_SH_QUOTE_ACCESS
2330 WHERE quote_sharee_number = c_retrieval_num
2331 OR (party_id = c_party_id and cust_account_id = c_cust_account_id)
2332 AND quote_header_id = c_quote_header_id
2333 AND nvl(end_date_active, sysdate+1) > sysdate;
2334
2335 rec_get_recip_id c_get_recip_id%rowtype;
2336 l_quote_access_rec IBE_QUOTE_SAVESHARE_pvt.QUOTE_ACCESS_REC_TYPE
2337 := IBE_QUOTE_SAVESHARE_pvt.G_MISS_QUOTE_ACCESS_REC;
2338 l_quote_access_rec_recip IBE_QUOTE_SAVESHARE_pvt.QUOTE_ACCESS_REC_TYPE
2339 := IBE_QUOTE_SAVESHARE_pvt.G_MISS_QUOTE_ACCESS_REC;
2340
2341 cursor c_get_recipient_party(c_recipient_id NUMBER) is
2342 SELECT party_id,
2343 quote_sharee_number,
2344 quote_header_id,
2345 update_privilege_type_code,
2346 contact_point_id
2347 FROM ibe_sh_quote_access
2348 where quote_sharee_id = c_recipient_id;
2349
2350 rec_get_recipient_party c_get_recipient_party%rowtype;
2351
2352 BEGIN
2353 SAVEPOINT ENDWORKING_V2;
2354 -- Standard call to check for call compatibility.
2355 IF NOT FND_API.Compatible_API_Call ( l_api_version,
2356 P_Api_Version,
2357 l_api_name,
2358 G_PKG_NAME )
2359 THEN
2360 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2361 END IF;
2362 -- Initialize message list IF p_init_msg_list is set to TRUE.
2363 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2364 FND_MSG_PUB.initialize;
2365 END IF;
2366
2367 -- Initialize API return status to success
2368 x_return_status := FND_API.G_RET_STS_SUCCESS;
2369 -----------------------------------------------------------------------------------------
2370 --API Body start
2371 -----------------------------------------------------------------------------------------
2372 -- l_quote_access_rec := p_quote_access_rec;
2373 FOR rec_userenv_partyid in c_userenv_partyid LOOP
2374 l_initiator_id := rec_userenv_partyid.customer_id;
2375 EXIT when c_userenv_partyid%notfound;
2376 END LOOP;
2377
2378 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2379 IBE_UTIL.DEBUG('IBE_QUOTE_SAVESHARE_V2_PVT.END_WORKING: START');
2380 END IF;
2381 --DBMS_OUTPUT.PUT_LINE('IBE_QUOTE_SAVESHARE_V2_PVT.END_WORKING: START');
2382 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2383 IBE_UTIL.DEBUG('Opening cursor to retrieve the recipient_id');
2384 END IF;
2385 --DBMS_OUTPUT.PUT_LINE('Opening cursor to retrieve the recipient_id');
2386 --DBMS_OUTPUT.PUT_LINE('p_quote_access_rec.quote_sharee_number: '||p_quote_access_rec.quote_sharee_number);
2387 --DBMS_OUTPUT.PUT_LINE('p_quote_access_rec.party_id: '||p_quote_access_rec.party_id);
2388 --DBMS_OUTPUT.PUT_LINE('p_quote_access_rec.cust_account_id: '||p_quote_access_rec.cust_account_id);
2389 --DBMS_OUTPUT.PUT_LINE('p_quote_access_rec.quote_header_id: '||p_quote_access_rec.quote_header_id);
2390
2391 l_quote_access_tbl:= p_quote_access_tbl;
2392 FOR rec_get_recip_id in c_get_recip_id(p_retrieval_number,
2393 p_party_id,
2394 p_cust_account_id,
2395 p_quote_header_id) LOOP
2396 l_quote_access_rec.quote_sharee_id := rec_get_recip_id.quote_sharee_id;
2397 l_quote_access_rec.quote_sharee_number := p_retrieval_number;
2398 l_quote_access_rec.party_id := p_party_id;
2399 l_quote_access_rec.cust_account_id := p_cust_account_id;
2400 l_quote_access_rec.quote_header_id := rec_get_recip_id.quote_header_id;
2401 EXIT WHEN c_get_recip_id%NOTFOUND;
2402 END LOOP;
2403 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2404 IBE_UTIL.DEBUG('Recipient id obtained is: '||l_quote_access_rec.quote_sharee_id);
2405 END IF;
2406
2407 --DBMS_OUTPUT.PUT_LINE('Recipient id obtained is: '||l_quote_access_rec.quote_sharee_id);
2408 IBE_QUOTE_SAVESHARE_V2_PVT.DELETE_RECIPIENT(
2409 P_Quote_access_rec => l_quote_access_rec ,
2410 p_minisite_id => p_minisite_id ,
2411 p_delete_code => 'END_WORKING' ,
2412 p_url => p_url ,
2413 p_notes => p_notes ,
2414 x_return_status => x_return_status ,
2415 x_msg_count => x_msg_count ,
2416 x_msg_data => x_msg_data );
2417
2418 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2419 RAISE FND_API.G_EXC_ERROR;
2420 END IF;
2421 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2422 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2423 END IF;
2424
2425 FOR counter in 1..p_quote_access_tbl.count LOOP
2426 IF ((l_quote_access_tbl(counter).notify_flag = FND_API.G_TRUE) AND
2427 (l_quote_access_rec.quote_sharee_id <> l_quote_access_tbl(counter).quote_sharee_id)) THEN
2428
2429 FOR rec_get_recipient_party in c_get_recipient_party(p_quote_access_tbl(counter).quote_sharee_id) LOOP
2430 l_quote_access_rec_recip.party_id := rec_get_recipient_party.party_id;
2431 l_quote_access_rec_recip.quote_sharee_number := rec_get_recipient_party.quote_sharee_number;
2432 l_quote_access_rec_recip.quote_header_id := rec_get_recipient_party.quote_header_id;
2433 l_quote_access_rec_recip.update_privilege_type_code := rec_get_recipient_party.update_privilege_type_code;
2434 -- As part of bug fix 3349991 getting contact_point_id to the notification api.
2435 l_quote_access_rec_recip.contact_point_id := rec_get_recipient_party.contact_point_id;
2436 EXIT when c_get_recipient_party%NOTFOUND;
2437 END LOOP;
2438
2439 -- Assign the quote_sharee_id to l_quote_access_rec (value needed in the notification api,3349991)
2440 l_quote_access_rec_recip.quote_sharee_id := p_quote_access_tbl(counter).quote_sharee_id;
2441
2442 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2443 IBE_UTIL.DEBUG('IBE_QUOTE_SAVESHARE_V2_PVT.END_WORKING: Ready to call view shared cart');
2444 IBE_UTIL.DEBUG('IBE_QUOTE_SAVESHARE_V2_PVT.END_WORKING:initiator_party_id: '||l_initiator_id);
2445 IBE_UTIL.DEBUG('IBE_QUOTE_SAVESHARE_V2_PVT.END_WORKING:recipient party_id: '||l_quote_access_rec_recip.party_id);
2446 IBE_UTIL.DEBUG('IBE_QUOTE_SAVESHARE_V2_PVT.END_WORKING:recipient number: '||l_quote_access_rec_recip.quote_sharee_number);
2447 IBE_UTIL.DEBUG('IBE_QUOTE_SAVESHARE_V2_PVT.END_WORKING:recipient id: '||l_quote_access_rec_recip.quote_sharee_id);
2448 IBE_UTIL.DEBUG('IBE_QUOTE_SAVESHARE_V2_PVT.END_WORKING:contact_point_id: '||l_quote_access_rec_recip.contact_point_id);
2449 END IF;
2450
2451 IBE_WORKFLOW_PVT.Notify_view_shared_cart(
2452 p_api_version => p_api_version
2453 ,p_init_msg_list => p_init_msg_list
2454 ,p_quote_access_rec => l_quote_access_rec_recip
2455 ,p_minisite_id => p_minisite_id
2456 ,p_url => p_url
2457 ,p_notes => p_notes
2458 ,p_sent_by_party_id => l_initiator_id
2459 ,x_return_status => x_return_status
2460 ,x_msg_count => x_msg_count
2461 ,x_msg_data => x_msg_data );
2462 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2463 RAISE FND_API.G_EXC_ERROR;
2464 END IF;
2465 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2466 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2467 END IF;
2468 END IF;
2469 END LOOP;
2470
2471 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2472 IBE_UTIL.DEBUG('IBE_QUOTE_SAVESHARE_V2_PVT.END_WORKING: END');
2473 END IF;
2474 --DBMS_OUTPUT.PUT_LINE('IBE_QUOTE_SAVESHARE_V2_PVT.END_WORKING: END ');
2475 EXCEPTION
2476 WHEN FND_API.G_EXC_ERROR THEN
2477 ROLLBACK TO ENDWORKING_V2;
2478 x_return_status := FND_API.G_RET_STS_ERROR;
2479 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
2480 p_count => x_msg_count ,
2481 p_data => x_msg_data);
2482 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2483 IBE_Util.Debug('Expected error in IBE_QUOTE_SAVESHARE_V2_PVT.ENDWORKING');
2484 END IF;
2485
2486 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2487 ROLLBACK TO ENDWORKING_V2;
2488 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2489 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
2490 p_count => x_msg_count ,
2491 p_data => x_msg_data);
2492 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2493 IBE_Util.Debug('Unexpected error in IBE_QUOTE_SAVESHARE_V2_PVT.ENDWORKING)');
2494 END IF;
2495
2496 WHEN OTHERS THEN
2497 ROLLBACK TO ENDWORKING_V2;
2498 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2499 IF FND_Msg_Pub.Check_Msg_Level(FND_Msg_Pub.G_MSG_LVL_UNEXP_ERROR) THEN
2500 FND_Msg_Pub.Add_Exc_Msg(G_PKG_NAME,
2501 l_api_name);
2502 END IF;
2503 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
2504 p_count => x_msg_count ,
2505 p_data => x_msg_data);
2506 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2507 IBE_Util.Debug('Unknown error in IBE_QUOTE_SAVESHARE_V2_PVT.ENDWORKING');
2508 END IF;
2509
2510
2511 END;
2512 /*updates all recipients' access levels to readonly
2513 *Usages:
2514 -Status changes from "cart" to"quote" - request sales assistance, contract cart
2515 */
2516 Procedure share_readonly (
2517 p_quote_header_id IN Number ,
2518 P_minisite_id IN Number := FND_API.G_MISS_NUM,
2519 p_url IN Varchar2 := FND_API.G_MISS_CHAR,
2520 p_api_version IN NUMBER := 1 ,
2521 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
2522 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
2523 x_return_status OUT NOCOPY VARCHAR2 ,
2524 x_msg_count OUT NOCOPY NUMBER ,
2525 x_msg_data OUT NOCOPY VARCHAR2 ) is
2526
2527 l_api_name CONSTANT VARCHAR2(30) := 'SHAREREADONLY_V2';
2528 l_api_version CONSTANT NUMBER := 1.0;
2529 tbl_counter NUMBER := 1;
2530 l_initiator_id NUMBER;
2531
2532 l_quote_access_tbl IBE_QUOTE_SAVESHARE_pvt.QUOTE_ACCESS_Tbl_Type
2533 := IBE_QUOTE_SAVESHARE_pvt.g_miss_quote_access_tbl;
2534
2535 cursor c_get_recipients(c_qte_hdr_id NUMBER) is
2536 select quote_sharee_id,
2537 party_id,
2538 cust_account_id,
2539 quote_header_id
2540 from IBE_SH_QUOTE_ACCESS
2541 where quote_header_id = c_qte_hdr_id
2542 and nvl(end_date_active,sysdate+1) > sysdate;
2543
2544 rec_get_recipients c_get_recipients%rowtype;
2545
2546 BEGIN
2547
2548 SAVEPOINT SHAREREADONLY_V2;
2549 -- Standard call to check for call compatibility.
2550 IF NOT FND_API.Compatible_API_Call ( l_api_version,
2551 P_Api_Version,
2552 l_api_name,
2553 G_PKG_NAME )
2554 THEN
2555 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2556 END IF;
2557 -- Initialize message list IF p_init_msg_list is set to TRUE.
2558 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2559 FND_MSG_PUB.initialize;
2560 END IF;
2561
2562 -- Initialize API return status to success
2563 x_return_status := FND_API.G_RET_STS_SUCCESS;
2564 -------------------------------------------------------------------------------------------------------------
2565 --API Body start
2566 -------------------------------------------------------------------------------------------------------------
2567
2568 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2569 IBE_UTIL.DEBUG('IBE_QUOTE_SAVESHARE_V2_PVT.SHARE_READONLY: START');
2570 IBE_UTIL.DEBUG('IBE_QUOTE_SAVESHARE_V2_PVT.SHARE_READONLY:quote_header_id: '||p_quote_header_id);
2571 IBE_UTIL.DEBUG('IBE_QUOTE_SAVESHARE_V2_PVT.SHARE_READONLY: Querying initiator party id');
2572 END IF;
2573
2574 FOR rec_userenv_partyid in c_userenv_partyid LOOP
2575 l_initiator_id := rec_userenv_partyid.customer_id;
2576 EXIT when c_userenv_partyid%notfound;
2577 END LOOP;
2578
2579 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2580 IBE_UTIL.DEBUG('Building quote access table');
2581 END IF;
2582
2583 FOR rec_get_recipients in c_get_recipients(p_quote_header_id) LOOP
2584
2585 --Omitting the initiator's details as initiator is not required to get the notification
2586 IF (l_initiator_id <> nvl(rec_get_recipients.party_id,-1) ) THEN
2587 l_quote_access_tbl(tbl_counter).quote_sharee_id := rec_get_recipients.quote_sharee_id;
2588
2589 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2590 IBE_UTIL.DEBUG('Added sharee quote_sharee_id: '||rec_get_recipients.quote_sharee_id);
2591 END IF;
2592
2593 l_quote_access_tbl(tbl_counter).update_privilege_type_code := 'R';
2594 l_quote_access_tbl(tbl_counter).party_id := rec_get_recipients.party_id;
2595 l_quote_access_tbl(tbl_counter).cust_account_id := rec_get_recipients.cust_account_id;
2596 l_quote_access_tbl(tbl_counter).quote_header_id := rec_get_recipients.quote_header_id;
2597 l_quote_access_tbl(tbl_counter).operation_code := 'UPDATE';
2598 tbl_counter := tbl_counter+1;
2599 END IF;
2600 EXIT when c_get_recipients%notfound;
2601 END LOOP;
2602 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2603 IBE_UTIL.DEBUG('Done building quote access table');
2604 END IF;
2605
2606 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2607 IBE_UTIL.DEBUG('Calling save_recipients to update recipient access level');
2608 END IF;
2609 IBE_QUOTE_SAVESHARE_V2_PVT.save_recipients(
2610 P_Quote_access_tbl => l_quote_access_tbl ,
2611 P_Quote_header_id => P_Quote_header_id ,
2612 P_minisite_id => p_minisite_id ,
2613 p_url => p_url ,
2614 p_api_version => p_api_version ,
2615 p_init_msg_list => fnd_api.g_false ,
2616 p_commit => fnd_api.g_false ,
2617 x_return_status => x_return_status ,
2618 x_msg_count => x_msg_count ,
2619 x_msg_data => x_msg_data );
2620
2621 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2622 IBE_UTIL.DEBUG('Done calling save_recipients to update recipient access level');
2623 IBE_UTIL.DEBUG('IBE_QUOTE_SAVESHARE_V2_PVT.SHARE_READONLY: END');
2624 END IF;
2625
2626 EXCEPTION
2627
2628 WHEN FND_API.G_EXC_ERROR THEN
2629 ROLLBACK TO STOPSHARING_V2;
2630 x_return_status := FND_API.G_RET_STS_ERROR;
2631 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
2632 p_count => x_msg_count ,
2633 p_data => x_msg_data);
2634 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2635 IBE_Util.Debug('Expected error in IBE_QUOTE_SAVESHARE_V2_PVT.SHAREREADONLY_V2');
2636 END IF;
2637
2638 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2639 ROLLBACK TO STOPSHARING_V2;
2640 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2641 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
2642 p_count => x_msg_count ,
2643 p_data => x_msg_data);
2644 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2645 IBE_Util.Debug('Unexpected error in IBE_QUOTE_SAVESHARE_V2_PVT.SHAREREADONLY_V2');
2646 END IF;
2647
2648 WHEN OTHERS THEN
2649 ROLLBACK TO STOPSHARING_V2;
2650 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2651 IF FND_Msg_Pub.Check_Msg_Level(FND_Msg_Pub.G_MSG_LVL_UNEXP_ERROR) THEN
2652 FND_Msg_Pub.Add_Exc_Msg(G_PKG_NAME,
2653 l_api_name);
2654 END IF;
2655 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
2656 p_count => x_msg_count ,
2657 p_data => x_msg_data);
2658 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2659 IBE_Util.Debug('Unknown error in IBE_QUOTE_SAVESHARE_V2_PVT.SHAREREADONLY_V2');
2660 END IF;
2661 END;
2662
2663 /*To delete a recipient (a sort of wrapper around the "raw" delete table hander)
2664 *Usages:
2665 -To remove a recipient from the list of persons to whom a cart is shared.
2666 -All deletes for a recipient should be done using this api.
2667 -As of IBE.P - p_quote_access_rec must have the shared_by_party_id set
2668 */
2669 Procedure DELETE_RECIPIENT (
2670 P_Quote_access_rec IN IBE_QUOTE_SAVESHARE_pvt.QUOTE_ACCESS_REC_TYPE
2671 := IBE_QUOTE_SAVESHARE_pvt.G_MISS_QUOTE_ACCESS_REC ,
2672 P_minisite_id IN NUMBER ,
2673 p_delete_code IN VARCHAR2 := 'IBE_SC_CART_STOPSHARING' ,
2674 p_url IN VARCHAR2 := FND_API.G_MISS_CHAR ,
2675 p_notes IN VARCHAR2 := FND_API.G_MISS_CHAR ,
2676 p_api_version IN NUMBER := 1 ,
2677 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
2678 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
2679 x_return_status OUT NOCOPY VARCHAR2 ,
2680 x_msg_count OUT NOCOPY NUMBER ,
2681 x_msg_data OUT NOCOPY VARCHAR2 ) is
2682
2683 l_api_name CONSTANT VARCHAR2(30) := 'DELETERECIPIENT_V2';
2684 l_api_version CONSTANT NUMBER := 1.0;
2685 l_url VARCHAR2(2000);
2686 l_owner_partyid NUMBER;
2687 l_owner_accountid NUMBER;
2688 l_contact_point_rec HZ_CONTACT_POINT_V2PUB.CONTACT_POINT_REC_TYPE;
2689 l_party_id NUMBER;
2690 l_contact_point_id NUMBER;
2691 l_object_version_number NUMBER;
2692
2693
2694 /* Fixed the query for the bug 4860734 */
2695 cursor c_find_active_cart is
2696 select a.quote_header_id, a.party_id ,a.cust_account_id
2697 from IBE_ACTIVE_QUOTES a, ibe_sh_quote_access b
2698 where a.party_id = b.party_id
2699 and a.cust_account_id = b.cust_account_id
2700 and b.quote_sharee_id = P_Quote_access_rec.quote_sharee_id
2701 and nvl(b.end_date_active, sysdate+1) > sysdate
2702 and a.quote_header_id = p_quote_access_rec.quote_header_id
2703 and record_type= 'CART';
2704
2705 cursor c_get_owner_ids(c_quote_id number) is
2706 select party_id, cust_account_id
2707 from ASO_QUOTE_HEADERS_ALL
2708 where quote_header_id = c_quote_id;
2709
2710 cursor c_get_contact_point_ovn(c_quote_sharee_id Number) IS
2711 select QUOTE_ACCESS.party_id, CNTCT_POINTS.contact_point_id, CNTCT_POINTS.object_version_number
2712 from hz_contact_points CNTCT_POINTS, ibe_sh_quote_access QUOTE_ACCESS
2713 where CNTCT_POINTS.contact_point_id = QUOTE_ACCESS.contact_point_id
2714 and quote_sharee_id = c_quote_sharee_id;
2715
2716 rec_find_active_cart c_find_active_cart%rowtype;
2717 rec_get_owner_ids c_get_owner_ids%rowtype;
2718
2719 l_delete_context VARCHAR2(2000);
2720 l_shared_by_party_id NUMBER;
2721
2722 BEGIN
2723
2724 SAVEPOINT DELETERECIPIENT_V2;
2725 -- Standard call to check for call compatibility.
2726 IF NOT FND_API.Compatible_API_Call ( l_api_version,
2727 P_Api_Version,
2728 l_api_name,
2729 G_PKG_NAME )
2730 THEN
2731 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2732 END IF;
2733 -- Initialize message list IF p_init_msg_list is set to TRUE.
2734 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2735 FND_MSG_PUB.initialize;
2736 END IF;
2737
2738 -- Initialize API return status to success
2739 x_return_status := FND_API.G_RET_STS_SUCCESS;
2740 -------------------------------------------------------------------------------------------------------------
2741 --API Body start
2742 -------------------------------------------------------------------------------------------------------------
2743
2744 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2745 IBE_UTIL.DEBUG('IBE_QUOTE_SAVESHARE_V2_PVT.DELETE_RECIPIENT: START');
2746 END IF;
2747 FOR rec_find_active_cart in c_find_active_cart loop
2748 IF(rec_find_active_cart.quote_header_id is not null) THEN
2749 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2750 IBE_UTIL.DEBUG('IBE_QUOTE_SAVESHARE_V2_PVT.DELETE_RECIPIENT:user has an active cart');
2751 IBE_UTIL.DEBUG('Calling update row handler to deactivate the cart');
2752 END IF;
2753 --DBMS_OUTPUT.PUT_LINE('IBE_QUOTE_SAVESHARE_V2_PVT.DELETE_RECIPIENT:user has an active cart');
2754
2755 IBE_ACTIVE_QUOTES_ALL_PKG.UPDATE_ROW(
2756 X_OBJECT_VERSION_NUMBER => 1 ,
2757 X_QUOTE_HEADER_ID => null ,
2758 X_PARTY_ID => rec_find_active_cart.party_id ,
2759 X_CUST_ACCOUNT_ID => rec_find_active_cart.cust_account_id,
2760 X_RECORD_TYPE => 'CART' ,
2761 X_CURRENCY_CODE => null ,
2762 x_last_update_date => sysdate ,
2763 x_last_updated_by => fnd_global.user_id ,
2764 x_last_update_login => 1);
2765 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2766 IBE_UTIL.DEBUG('Done calling update row handler to deactivate the cart');
2767 END IF;
2768 END IF;
2769 EXIT WHEN c_find_active_cart%NOTFOUND;
2770 END LOOP;
2771 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2772 IBE_UTIL.DEBUG('Calling Update row handler on sh_quote_access table to end-date recipient row');
2773 END IF;
2774 --DBMS_OUTPUT.PUT_LINE('Calling Update row handler on sh_quote_access table to end-date recipient row');
2775
2776 open c_get_contact_point_ovn(P_Quote_access_rec.quote_sharee_id);
2777 fetch c_get_contact_point_ovn into l_party_id, l_contact_point_id, l_object_version_number;
2778
2779 if(l_party_id is NULL) THEN
2780 l_contact_point_rec.contact_point_id := l_contact_point_id;
2781 l_contact_point_rec.status := 'I';
2782 l_contact_point_rec.primary_flag := 'N';
2783 HZ_CONTACT_POINT_V2PUB.update_contact_point(
2784 p_init_msg_list => FND_API.G_FALSE,
2785 p_contact_point_rec => l_contact_point_rec,
2786 p_object_version_number => l_object_version_number,
2787 x_return_status => x_return_status,
2788 x_msg_count => x_msg_count,
2789 x_msg_data => x_msg_data);
2790
2791 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2792 RAISE FND_API.G_EXC_ERROR;
2793 END IF;
2794 END IF;
2795
2796 IBE_SH_QUOTE_ACCESS_PKG.update_Row(
2797 p_quote_sharee_id => P_Quote_access_rec.quote_sharee_id
2798 ,p_end_date_active => sysdate);
2799 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2800 IBE_UTIL.DEBUG('Done calling Update row handler on sh_quote_access table to end-date recipient row');
2801 END IF;
2802 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2803 IBE_UTIL.DEBUG('IBE_QUOTE_SAVESHARE_V2_PVT.DELETE_RECIPIENT: END');
2804 IBE_UTIL.DEBUG('delete_code is: '||p_delete_code);
2805 END IF;
2806
2807 IF (p_quote_access_rec.notify_flag = FND_API.G_TRUE) THEN
2808 --Notify_finish_sharing API with the appropriate delete_code
2809 IF (p_delete_code = 'END_WORKING') THEN
2810 /*obtain the owner party_id and acctid of the cart/quote being dealt with*/
2811 FOR rec_get_owner_ids in c_get_owner_ids(p_quote_access_rec.quote_header_id) LOOP
2812 l_owner_partyid := rec_get_owner_ids.party_id;
2813 l_owner_accountid := rec_get_owner_ids.cust_account_id;
2814 EXIT when c_get_owner_ids%notfound;
2815 END LOOP;
2816
2817 -- notification that goes to the owner (so and so has finished working this cart)
2818 -- for generic email to owner, we need to add some special parameters since we will not have a retrieval number
2819 l_url := p_url || '&opid=' || l_owner_partyid || '&oaid=' || l_owner_accountid;
2820 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2821 IBE_UTIL.DEBUG('url for generic email to OWNER: '||l_url);
2822 END IF;
2823
2824 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2825 IBE_UTIL.DEBUG('Calling notify_end_working API');
2826 END IF;
2827 IBE_WORKFLOW_PVT.Notify_End_Working(
2828 p_api_version => p_api_version ,
2829 p_init_msg_list => p_init_msg_list ,
2830 p_quote_header_id => p_quote_access_rec.quote_header_id ,
2831 p_party_id => p_quote_access_rec.party_id ,
2832 p_Cust_Account_Id => p_quote_access_rec.cust_account_id ,
2833 p_retrieval_number => p_quote_access_rec.quote_sharee_number,
2834 p_minisite_id => p_minisite_id ,
2835 p_url => l_url ,
2836 p_notes => p_notes ,
2837 x_return_status => x_return_status ,
2838 x_msg_count => x_msg_count ,
2839 x_msg_data => x_msg_data );
2840
2841 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2842 RAISE FND_API.G_EXC_ERROR;
2843 END IF;
2844 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2845 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2846 END IF;
2847 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2848 IBE_UTIL.DEBUG('Done calling notify_end_working API');
2849 END IF;
2850 ELSE
2851 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2852 IBE_UTIL.DEBUG('Calling notify_finish_sharing API');
2853 END IF;
2854 --DBMS_OUTPUT.PUT_LINE('Calling notify_stop_sharing API ');
2855 -- notification that goes to the member (you no longer have access to this cart)
2856 IBE_WORKFLOW_PVT.Notify_Finish_Sharing(
2857 p_api_version => p_api_version ,
2858 p_init_msg_list => p_init_msg_list ,
2859 p_quote_access_rec => p_quote_access_rec , --of the recepient
2860 p_minisite_id => p_minisite_id ,
2861 p_url => p_url ,
2862 p_context_code => p_delete_code ,
2863 p_shared_by_partyid=> p_quote_access_rec.shared_by_party_id,
2864 p_notes => p_notes ,
2865 x_return_status => x_return_status ,
2866 x_msg_count => x_msg_count ,
2867 x_msg_data => x_msg_data );
2868
2869 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2870 RAISE FND_API.G_EXC_ERROR;
2871 END IF;
2872 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2873 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2874 END IF;
2875 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2876 IBE_UTIL.DEBUG('Done calling notify_finish_sharing API');
2877 END IF;
2878 END IF; --end working or finish sharing
2879 END IF; --IF notify_flag is true
2880
2881 EXCEPTION
2882 WHEN FND_API.G_EXC_ERROR THEN
2883 ROLLBACK TO DELETERECIPIENT_V2;
2884 x_return_status := FND_API.G_RET_STS_ERROR;
2885 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
2886 p_count => x_msg_count ,
2887 p_data => x_msg_data);
2888 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2889 IBE_Util.Debug('Expected error in IBE_QUOTE_SAVESHARE_V2_PVT.DELETE_RECIPIENT()');
2890 END IF;
2891
2892 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2893 ROLLBACK TO DELETERECIPIENT_V2;
2894 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2895 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
2896 p_count => x_msg_count ,
2897 p_data => x_msg_data);
2898 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2899 IBE_Util.Debug('Unexpected error in IBE_QUOTE_SAVESHARE_V2_PVT.DELETE_RECIPIENT()');
2900 END IF;
2901
2902 WHEN OTHERS THEN
2903 ROLLBACK TO DELETERECIPIENT_V2;
2904 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2905 IF FND_Msg_Pub.Check_Msg_Level(FND_Msg_Pub.G_MSG_LVL_UNEXP_ERROR) THEN
2906 FND_Msg_Pub.Add_Exc_Msg(G_PKG_NAME,
2907 l_api_name);
2908 END IF;
2909 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
2910 p_count => x_msg_count ,
2911 p_data => x_msg_data);
2912 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2913 IBE_Util.Debug('Unknown error in IBE_QUOTE_SAVESHARE_V2_PVT.DELETE_RECIPIENT()');
2914 END IF;
2915
2916 END;
2917
2918 PROCEDURE Validate_share_Update(
2919 p_api_version_number IN NUMBER := 1.0
2920 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
2921 ,p_quote_header_rec IN ASO_QUOTE_PUB.Qte_Header_Rec_Type
2922 ,p_quote_access_tbl IN IBE_QUOTE_SAVESHARE_pvt.QUOTE_ACCESS_Tbl_Type
2923 := IBE_QUOTE_SAVESHARE_pvt.G_miss_quote_access_Tbl
2924 -- partyid and accountid cannot be gmiss coming in
2925 ,p_party_id IN NUMBER
2926 ,p_cust_account_id IN NUMBER
2927 ,p_retrieval_number IN NUMBER := FND_API.G_MISS_NUM
2928 ,p_operation_code IN VARCHAR2
2929 ,x_return_status OUT NOCOPY VARCHAR2
2930 ,x_msg_count OUT NOCOPY NUMBER
2931 ,x_msg_data OUT NOCOPY VARCHAR2
2932 )
2933
2934 is
2935
2936 l_api_name CONSTANT VARCHAR2(30) := 'Validate_share_Update';
2937 l_api_version CONSTANT NUMBER := 1.0;
2938 l_check_updates VARCHAR2(1) := FND_API.G_FALSE; -- checks timestamps and if updates are really necessary
2939 l_check_onlynotify VARCHAR2(1) := FND_API.G_FALSE;
2940 l_is_owner VARCHAR2(1) := FND_API.G_TRUE;
2941 l_owner_party_id NUMBER;
2942 l_userenv_party_id NUMBER;
2943 l_access_level VARCHAR2(30) := null;
2944 l_db_last_update_date DATE;
2945 l_db_end_date_active DATE;
2946 l_db_access_level VARCHAR2(30) := null;
2947 l_db_quote_access_tbl IBE_QUOTE_SAVESHARE_pvt.QUOTE_ACCESS_Tbl_Type;
2948 l_env_user_id NUMBER;
2949
2950 cursor c_get_owner_partyid(c_quote_id number) is
2951 select party_id
2952 from ASO_QUOTE_HEADERS_ALL
2953 where quote_header_id = c_quote_id;
2954
2955 rec_get_owner_partyid c_get_owner_partyid%rowtype;
2956
2957 cursor c_get_role_by_retr_num(c_retrieval_number number, c_party_id number, c_acct_id number) is
2958 select update_privilege_type_code
2959 from ibe_sh_quote_access
2960 where quote_sharee_number = c_retrieval_number
2961 and (party_id is null or party_id = c_party_id)
2962 and (cust_account_id is null or cust_account_id = c_acct_id);
2963
2964 rec_get_role_by_retr_num c_get_role_by_retr_num%rowtype;
2965
2966 cursor c_get_role_by_user(c_party_id number, c_account_id number, c_qte_hdr_id number) is
2967 select update_privilege_type_code
2968 from ibe_sh_quote_access
2969 where quote_header_id = c_qte_hdr_id
2970 and party_id = c_party_id
2971 and cust_account_id = c_account_id
2972 AND nvl(end_date_active, sysdate+1) > sysdate;
2973
2974
2975 rec_get_role_by_user c_get_role_by_user%rowtype;
2976
2977 cursor c_get_recipient_info(c_recipient_id number) is
2978 select last_update_date, last_updated_by, end_date_active, update_privilege_type_code
2979 from ibe_sh_quote_access
2980 where quote_sharee_id = c_recipient_id;
2981
2982 rec_get_recipient_info c_get_recipient_info%rowtype;
2983
2984 BEGIN
2985
2986 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2987 IBE_UTIL.DEBUG('Begin validate_share_update : ' || to_char(sysdate, 'mm/dd/yyyy:hh24:MI:SS'));
2988 END IF;
2989
2990 -- Standard call to check for call compatibility.
2991 IF NOT FND_API.Compatible_API_Call (l_api_version,
2992 p_api_version_number,
2993 l_api_name,
2994 G_PKG_NAME )
2995 THEN
2996 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2997 END IF;
2998
2999 -- Initialize message list if p_init_msg_list is set to TRUE.
3000
3001 IF FND_API.To_Boolean( p_init_msg_list ) THEN
3002 FND_Msg_Pub.initialize;
3003 END IF;
3004
3005 -- Initialize API return status to success
3006 x_return_status := FND_API.G_RET_STS_SUCCESS;
3007
3008 -- API body
3009 -- start by returning the input
3010
3011 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3012 IBE_UTIL.DEBUG('Incoming quote_header_id is :'||p_quote_header_rec.quote_header_id);
3013 IBE_UTIL.DEBUG('Incoming operation_code is :'||p_operation_code);
3014 IBE_UTIL.DEBUG('Incoming party_id is :'||p_party_id);
3015 IBE_UTIL.DEBUG('Incoming account_id is :'||p_cust_account_id);
3016 IBE_UTIL.DEBUG('Incoming retrieval_number is :'||p_retrieval_number);
3017 END IF;
3018
3019 /* FOR rec_userenv_partyid in c_userenv_partyid LOOP
3020 l_userenv_party_id := rec_userenv_partyid.customer_id;
3021 EXIT when c_userenv_partyid%notfound;
3022 END LOOP;
3023 */
3024 FOR rec_get_owner_partyid in c_get_owner_partyid(p_quote_header_rec.quote_header_id) LOOP
3025 l_owner_party_id := rec_get_owner_partyid.party_id;
3026 EXIT when c_get_owner_partyid%notfound;
3027 END LOOP;
3028 ------------ USER VALIDATION - must be either owner or valid recipient ------------------------------
3029 if (l_owner_party_id <> p_party_id) then
3030 l_is_owner := FND_API.G_FALSE;
3031 else
3032 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3033 IBE_Util.Debug('Initiator is OWNER');
3034 END IF;
3035 end if;
3036
3037 -- if ((l_is_owner = FND_API.G_FALSE) and (p_operation_code <> OP_END_WORKING)) then
3038 if (l_is_owner = FND_API.G_FALSE) then
3039 -- if user is not the owner then we need to make sure he is a valid member
3040 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3041 IBE_Util.Debug('Initiator may be RECIPIENT');
3042 END IF;
3043 if ((p_retrieval_number is not null) and (p_retrieval_number <> FND_API.G_MISS_NUM)) then
3044 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
3045 l_access_level := rec_get_role_by_retr_num.update_privilege_type_code;
3046 EXIT when c_get_role_by_retr_num%notfound;
3047 END LOOP;
3048 else
3049 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
3050 l_access_level := rec_get_role_by_user.update_privilege_type_code;
3051 EXIT when c_get_role_by_user%notfound;
3052 END LOOP;
3053 end if;
3054 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3055 IBE_Util.Debug('RECIPIENT access level: ' || l_access_level);
3056 END IF;
3057 if (l_access_level is null) then
3058 IF FND_Msg_Pub.Check_Msg_Level (FND_Msg_Pub.G_MSG_LVL_ERROR) THEN
3059 FND_Message.Set_Name('IBE', 'IBE_SC_ERR_USERACCESS');
3060 FND_Msg_Pub.Add;
3061 END IF;
3062 -- need to raise an error that the user no longer has access to this cart
3063 RAISE FND_API.G_EXC_ERROR;
3064 end if;
3065 end if;
3066
3067 ------------ DETERMINE WHICH VALIDATIONS TO DO BASED ON OP CODE ------------------------------
3068 -- owner only operations
3069 if (p_operation_code in (OP_APPEND, OP_NAME_CART, OP_SAVE_CART_AND_RECIPIENTS)) then
3070 if (l_is_owner = FND_API.G_FALSE) then
3071 IF FND_Msg_Pub.Check_Msg_Level (FND_Msg_Pub.G_MSG_LVL_ERROR) THEN
3072 FND_Message.Set_Name('IBE', 'IBE_SC_ERR_PRIVILEGE');
3073 FND_Msg_Pub.Add;
3074 END IF;
3075 -- raise an error that the user does not have this privilege
3076 RAISE FND_API.G_EXC_ERROR;
3077 end if;
3078 if (p_operation_code = OP_SAVE_CART_AND_RECIPIENTS) then
3079 l_check_updates := FND_API.G_TRUE;
3080 end if;
3081 -- owner or admin operations
3082 elsif (p_operation_code in (OP_STOP_SHARING, OP_DELETE_CART)) then
3083 if ((l_is_owner = FND_API.G_FALSE) and (l_access_level <> 'A')) then
3084 IF FND_Msg_Pub.Check_Msg_Level (FND_Msg_Pub.G_MSG_LVL_ERROR) THEN
3085 FND_Message.Set_Name('IBE', 'IBE_SC_ERR_PRIVILEGE');
3086 FND_Msg_Pub.Add;
3087 END IF;
3088 -- raise an error that the user does not have this privilege
3089 RAISE FND_API.G_EXC_ERROR;
3090 end if;
3091 l_check_onlynotify := FND_API.G_TRUE;
3092 l_check_updates := FND_API.G_TRUE;
3093 -- any role, but different checks depending on role
3094 elsif (p_operation_code = OP_SAVE_RECIPIENTS) then
3095 if ((l_is_owner = FND_API.G_FALSE) and (l_access_level <> 'A')) then
3096 -- if not the owner or not admin, then make sure user is only notifying
3097 l_check_onlynotify := FND_API.G_TRUE;
3098 end if;
3099 l_check_updates := FND_API.G_TRUE;
3100 elsif ((p_operation_code = OP_ACTIVATE_QUOTE) or (p_operation_code = OP_DEACTIVATE)) then
3101 l_check_updates := FND_API.G_FALSE; -- no validations to do here that have not already been done
3102 end if; -- end if else over operation_code
3103
3104 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3105 IBE_UTIL.DEBUG('l_check_onlynotify is :'||l_check_onlynotify);
3106 IBE_UTIL.DEBUG('l_check_updates is :'||l_check_updates);
3107 END IF;
3108
3109 ------------ VALIDATIONS ON THE INPUT QUOTE ACCESS TABLE ------------------------------
3110 if ((l_check_updates = FND_API.G_TRUE) or (l_check_onlynotify = FND_API.G_TRUE)) then
3111 l_env_user_id := FND_GLOBAL.USER_ID;
3112 FOR i in 1..p_quote_access_tbl.count LOOP
3113 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3114 IBE_UTIL.DEBUG('current quote_sharee_id : '||p_quote_access_tbl(i).quote_sharee_id);
3115 end if;
3116 -- populate a local table with all the db data - but index it according to the input table
3117 -- in this loop, we will check every opcode if necessary
3118 -- down below, we will end up only checking the ones that have a shareeid
3119 l_db_quote_access_tbl(i).operation_code := p_quote_access_tbl(i).operation_code;
3120 if ((p_quote_access_tbl(i).quote_sharee_id is not null)
3121 and (p_quote_access_tbl(i).quote_sharee_id <> fnd_api.g_miss_num)) then
3122 --cursor query to get last_update_date, contact_point_id, end_date_active
3123 FOR rec_get_recipient_info in c_get_recipient_info(p_quote_access_tbl(i).quote_sharee_id) LOOP
3124 l_db_quote_access_tbl(i).last_update_date := rec_get_recipient_info.last_update_date;
3125 l_db_quote_access_tbl(i).last_updated_by := rec_get_recipient_info.last_updated_by;
3126 l_db_quote_access_tbl(i).end_date_active := rec_get_recipient_info.end_date_active;
3127 l_db_quote_access_tbl(i).update_privilege_type_code := rec_get_recipient_info.update_privilege_type_code;
3128 EXIT when c_get_recipient_info%notfound;
3129 END LOOP;
3130 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3131 IBE_UTIL.DEBUG('input op_code : '||p_quote_access_tbl(i).operation_code);
3132 IBE_UTIL.DEBUG('input last_update_date : '||to_char(p_quote_access_tbl(i).last_update_date,'mm/dd/yyyy:hh24:MI:SS'));
3133 IBE_UTIL.DEBUG('db last_update_date : '||to_char(l_db_quote_access_tbl(i).last_update_date,'mm/dd/yyyy:hh24:MI:SS'));
3134 IBE_UTIL.DEBUG('input access_level : '||p_quote_access_tbl(i).update_privilege_type_code);
3135 IBE_UTIL.DEBUG('db access_level : '||l_db_quote_access_tbl(i).update_privilege_type_code);
3136 end if;
3137 -- do the opcode permissions check as we loop
3138 if (l_check_onlynotify = FND_API.G_TRUE) then
3139 -- make sure we are not creating, deleting, or doing any real updates
3140 if ((p_quote_access_tbl(i).operation_code = 'DELETE')
3141 or(p_quote_access_tbl(i).operation_code = 'CREATE')
3142 or ((p_quote_access_tbl(i).operation_code = 'UPDATE')
3143 and (p_quote_access_tbl(i).update_privilege_type_code is not null)
3144 and (p_quote_access_tbl(i).update_privilege_type_code <> FND_API.G_MISS_CHAR)
3145 and (p_quote_access_tbl(i).update_privilege_type_code <> l_db_quote_access_tbl(i).update_privilege_type_code))) then
3146 IF FND_Msg_Pub.Check_Msg_Level (FND_Msg_Pub.G_MSG_LVL_ERROR) THEN
3147 FND_Message.Set_Name('IBE', 'IBE_SC_ERR_PRIVILEGE');
3148 FND_Msg_Pub.Add;
3149 END IF;
3150 RAISE FND_API.G_EXC_ERROR;
3151 end if;
3152 end if; -- end check onlynotify
3153 end if; -- end if we have a quote_sharee_id
3154 end loop; -- end loop over input access tbl
3155 -- second loop to check last update dates
3156 if (l_check_updates = FND_API.G_TRUE) then
3157 FOR i in 1..p_quote_access_tbl.count LOOP
3158 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3159 IBE_UTIL.DEBUG('current quote_sharee_id : '||p_quote_access_tbl(i).quote_sharee_id);
3160 end if;
3161 if ((p_quote_access_tbl(i).quote_sharee_id is not null)
3162 and (p_quote_access_tbl(i).quote_sharee_id <> fnd_api.g_miss_num)) then
3163
3164 if ((p_quote_access_tbl(i).last_update_date <> FND_API.G_MISS_DATE) and
3165 (p_quote_access_tbl(i).last_update_date <> l_db_quote_access_tbl(i).last_update_date)) then
3166 -- i.e. don't throw exception if we want to end a row and it's already enddated.
3167 if ((p_quote_access_tbl(i).operation_code = 'DELETE') and (nvl(l_db_quote_access_tbl(i).end_date_active,sysdate-1) < sysdate)) then
3168 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3169 IBE_UTIL.DEBUG('deleteing an end dated row, allowing it to pass through');
3170 end if;
3171 elsif (l_env_user_id = l_db_quote_access_tbl(i).last_updated_by) then
3172 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3173 IBE_UTIL.DEBUG('timestamps do not match, but user was the last to update the row so allowing it to go through');
3174 end if;
3175 else
3176 IF FND_Msg_Pub.Check_Msg_Level (FND_Msg_Pub.G_MSG_LVL_ERROR) THEN
3177 FND_Message.Set_Name('IBE', 'IBE_SC_ERR_MEMBERS_OUT_OF_SYNC');
3178 FND_Msg_Pub.Add;
3179 END IF;
3180 -- need a new error msg that the row has been updated by another user and to try again
3181 RAISE FND_API.G_EXC_ERROR;
3182 end if;
3183 end if; -- end if last_update_dates dont match
3184 end if; -- end if shareeid is not null
3185 end loop; -- end loop over input quote access tbl
3186 end if; -- end if doing check updates
3187 end if; -- end check of updates
3188
3189 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3190 IBE_UTIL.DEBUG('End validate_share_update' || to_char(sysdate, 'mm/dd/yyyy:hh24:MI:SS'));
3191 END IF;
3192
3193 EXCEPTION
3194 WHEN FND_API.G_EXC_ERROR THEN
3195 x_return_status := FND_API.G_RET_STS_ERROR;
3196 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
3197 p_count => x_msg_count ,
3198 p_data => x_msg_data);
3199 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3200 IBE_Util.Debug('End IBE_Quote_Misc_pvt.validate_share_update: expected error');
3201 END IF;
3202 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3203 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3204 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
3205 p_count => x_msg_count ,
3206 p_data => x_msg_data);
3207 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3208 IBE_Util.Debug('End IBE_Quote_Misc_pvt.validate_share_update: unexpected error');
3209 END IF;
3210 WHEN OTHERS THEN
3211 IF FND_Msg_Pub.Check_Msg_Level(FND_Msg_Pub.G_MSG_LVL_UNEXP_ERROR) THEN
3212 FND_Msg_Pub.Add_Exc_Msg(G_PKG_NAME,
3213 l_api_name);
3214 END IF;
3215 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
3216 p_count => x_msg_count ,
3217 p_data => x_msg_data);
3218 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3219 IBE_Util.Debug('End IBE_Quote_Misc_pvt.validate_share_update: other exception');
3220 END IF;
3221 END validate_share_update;
3222
3223 END IBE_QUOTE_SAVESHARE_V2_PVT;
[Home] [Help]