[Home] [Help]
PACKAGE BODY: APPS.IBE_ORD_ONECLICK_PVT
Source
1 PACKAGE BODY ibe_ord_oneclick_pvt AS
2 /* $Header: IBEVO1CB.pls 120.10 2006/06/20 19:59:39 apilpre ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'ibe_ord_oneclick_pvt';
5 l_true VARCHAR2(1) := FND_API.G_TRUE;
6
7 /* Local function to Get Credit Card Type Given The Number
8 hekkiral - 21-DEC-2000 */
9
10 /*function Get_Credit_Card_Type(
11 p_Credit_Card_Number NUMBER
12 ) RETURN VARCHAR2;*/
13 /*-----------------------------------------------------------------------------
14
15 Get_Settings
16 - retrives the foreign keys from IBE_ORD_ONECLICK
17 - retrives user's main email address associated with partyid in HZ_PARTIES
18 - validates the address usage via calls to IBE_ADDRESS_V2PVT.valid_usages
19
20 Key Input: party id and account id
21
22 NOTE: this api can be TRANSACTIONAL in that if an address is not valid,
23 it will update the oneclick settings row to be off and null for that address usage.
24
25 ------------------------------------------------------------------------------
26 */
27 procedure Get_Settings(
28 p_api_version IN NUMBER,
29 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
30 p_commit IN VARCHAR2 := FND_API.g_false,
31 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
32 x_return_status OUT NOCOPY VARCHAR2,
33 x_msg_count OUT NOCOPY NUMBER,
34 x_msg_data OUT NOCOPY VARCHAR2,
35
36 p_party_id IN NUMBER := NULL,
37 p_acct_id IN NUMBER := NULL,
38
39 x_OBJECT_VERSION_NUMBER OUT NOCOPY NUMBER,
40 x_ONECLICK_ID OUT NOCOPY NUMBER,
41 x_ENABLED_FLAG OUT NOCOPY VARCHAR2,
42 x_FREIGHT_CODE OUT NOCOPY VARCHAR2,
43 x_PAYMENT_ID OUT NOCOPY NUMBER,
44 x_BILL_PTYSITE_ID OUT NOCOPY NUMBER,
45 x_SHIP_PTYSITE_ID OUT NOCOPY NUMBER,
46 x_LAST_UPDATE_DATE OUT NOCOPY DATE,
47 x_EMAIL_ADDRESS OUT NOCOPY VARCHAR2
48 ) is
49 l_api_name CONSTANT VARCHAR2(30) := 'Get_Settings';
50 l_api_version CONSTANT NUMBER := 1.0;
51
52 CURSOR c_settings(c_party_id NUMBER, c_acct_id NUMBER) IS
53 SELECT object_version_number,
54 ord_oneclick_id,
55 enabled_flag,
56 freight_code,
57 payment_id,
58 bill_to_pty_site_id,
59 ship_to_pty_site_id,
60 last_update_date
61 FROM IBE_ORD_ONECLICK
62 WHERE
63 party_id = c_party_id and
64 cust_account_id = c_acct_id;
65
66 Cursor c_email_addr (owner_id NUMBER)
67 IS select email_address
68 from HZ_CONTACT_POINTS
69 where
70 contact_point_type = 'EMAIL' and
71 owner_table_name = 'HZ_PARTIES' and
72 status = 'A' and
73 owner_table_id = owner_id and primary_flag = 'Y';
74
75 l_usage_codes JTF_VARCHAR2_TABLE_100 ;
76 l_return_codes JTF_VARCHAR2_TABLE_100 ;
77 l_org_id NUMBER;
78 l_save_flag VARCHAR2(1) := 'N';
79
80 l_cvv2_setup VARCHAR2(1);
81 l_statement_address_setup VARCHAR2(1);
82
83 begin
84 --ibe_util.enable_debug;
85 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
86 ibe_util.debug('Starting ibe_ord_oneclick_pvt.Get_Settings ' || to_char(sysdate,'DD-MON-YYYY:HH24:MI:SS'));
87 END IF;
88 SAVEPOINT Get_Settings_Pvt;
89 -- Standard call to check for call compatibility.
90 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
91 p_api_version ,
92 l_api_name ,
93 G_PKG_NAME )
94 THEN
95 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
96 END IF;
97
98 -- Initialize message list if p_init_msg_list is set to TRUE.
99 IF FND_API.to_Boolean( p_init_msg_list ) THEN
100 FND_MSG_PUB.initialize;
101 END IF;
102
103 -- Initialize API rturn status to success
104 x_return_status := FND_API.g_ret_sts_success;
105
106 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
107 ibe_util.debug('------- INPUT: -----------------');
108 ibe_util.debug('p_party_id :'||p_party_id);
109 ibe_util.debug('p_acct_id :'||p_acct_id);
110 END IF;
111
112 IBE_PAYMENT_INT_PVT.check_Payment_channel_setups(
113 p_api_version => p_api_version
114 ,p_init_msg_list => p_init_msg_list
115 ,p_commit => p_commit
116 ,x_cvv2_setup => l_cvv2_setup
117 ,x_statement_address_setup => l_statement_address_setup
118 ,x_return_status => x_return_status
119 ,x_msg_count => x_msg_count
120 ,x_msg_data => x_msg_data);
121
122 if x_return_status <> FND_API.g_ret_sts_success then
123 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
124 ibe_util.debug('ibe_ord_oneclick_pvt.Get_Settings - IBY_PAYMENT_INT_PVT.check_Payment_channel_setups ' || x_return_status);
125 END IF;
126 if x_return_status = FND_API.G_RET_STS_ERROR then
127 RAISE FND_API.G_EXC_ERROR;
128 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
129 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
130 end if;
131 end if;
132
133 /*Changes for credit card consolidation. If Cvv2 is mandatory then xpress checkout should be disabled.
134 User can't enter cvv2 number during express checkout if cvv2 is mandatory.*/
135 IF (l_cvv2_setup = FND_API.G_TRUE) THEN
136 x_ENABLED_FLAG := 'N';
137 ELSE
138
139 OPEN c_settings(p_party_id, p_acct_id);
140 FETCH c_settings
141 INTO
142 x_OBJECT_VERSION_NUMBER,
143 x_ONECLICK_ID,
144 x_ENABLED_FLAG,
145 x_FREIGHT_CODE,
146 x_PAYMENT_ID,
147 x_BILL_PTYSITE_ID,
148 x_SHIP_PTYSITE_ID,
149 x_LAST_UPDATE_DATE;
150 if c_settings%NOTFOUND then
151 x_ONECLICK_ID := FND_API.g_miss_num;
152 end if;
153 CLOSE c_settings;
154
155 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
156 ibe_util.debug('------- Retrieved From TABLES: -----------------');
157 ibe_util.debug('x_OBJECT_VERSION_NUMBER :'||x_OBJECT_VERSION_NUMBER);
158 ibe_util.debug('x_ONECLICK_ID :'||x_ONECLICK_ID);
159 ibe_util.debug('x_ENABLED_FLAG :'||x_ENABLED_FLAG);
160 ibe_util.debug('x_FREIGHT_CODE :'||x_FREIGHT_CODE);
161 ibe_util.debug('x_PAYMENT_ID :'||x_PAYMENT_ID);
162 ibe_util.debug('x_BILL_PTYSITE_ID :'||x_BILL_PTYSITE_ID);
163 ibe_util.debug('x_SHIP_PTYSITE_ID :'||x_SHIP_PTYSITE_ID);
164 ibe_util.debug('x_LAST_UPDATE_DATE :'||to_char(x_LAST_UPDATE_DATE,'DD-MON-YYYY:HH24:MI:SS'));
165 END IF;
166
167 open c_email_addr(p_party_id);
168 fetch c_email_addr into x_EMAIL_ADDRESS;
169 close c_email_addr;
170
171 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
172 ibe_util.debug('x_EMAIL_ADDRESS :'||x_EMAIL_ADDRESS);
173 END IF;
174 l_org_id := MO_GLOBAL.get_current_org_id();
175 -- in case of a single org env the feature of addr usages is unsupported
176 if (l_org_id is not null and l_org_id <> 0) then
177 l_usage_codes := JTF_VARCHAR2_TABLE_100(1);
178 l_return_codes := JTF_VARCHAR2_TABLE_100(1);
179
180 if (x_BILL_PTYSITE_ID is not null) then
181 -- call address validation apis; if either is not valid turn settings off
182 l_usage_codes(1) := 'BILL_TO_COUNTRY';
183 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
184 ibe_util.debug('Calling IBE_ADDRESS_V2PVT.valid_usages ' || to_char(sysdate,'DD-MON-YYYY:HH24:MI:SS'));
185 ibe_util.debug(' party_site_id: ' || x_BILL_PTYSITE_ID);
186 ibe_util.debug(' operating_unit_id: ' || l_org_id);
187 ibe_util.debug(' usage_code: ' || l_usage_codes(1));
188 END IF;
189 IBE_ADDRESS_V2PVT.valid_usages (
190 p_api_version => l_api_version,
191 p_init_msg_list => FND_API.G_FALSE,
192 p_party_site_id => x_BILL_PTYSITE_ID,
193 p_operating_unit_id => l_org_id,
194 p_usage_codes => l_usage_codes,
195 x_return_codes => l_return_codes,
196 x_return_status => x_return_status,
197 x_msg_count => x_msg_count,
198 x_msg_data => x_msg_data );
199 --ibe_util.enable_debug;
200 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
201 ibe_util.debug('Back from IBE_ADDRESS_V2PVT.valid_usages ' || to_char(sysdate,'DD-MON-YYYY:HH24:MI:SS'));
202 ibe_util.debug(' return_code: ' || l_return_codes(1));
203 END IF;
204
205 if x_return_status <> FND_API.g_ret_sts_success then
206 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
207 ibe_util.debug('ibe_ord_oneclick_pvt.Get_Settings - non success status from IBE_ADDRESS_V2PVT.valid_usages: ' || x_return_status);
208 END IF;
209 FND_MESSAGE.SET_NAME('IBE','IBE_EXPR_PLSQL_API_ERROR');
210 FND_MESSAGE.SET_TOKEN ( '0' , 'Get_Settings - IBE_ADDRESS_V2PVT.valid_usages' );
211 FND_MESSAGE.SET_TOKEN ( '1' , x_return_status );
212 FND_MSG_PUB.Add;
213 if x_return_status = FND_API.G_RET_STS_ERROR then
214 RAISE FND_API.G_EXC_ERROR;
215 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
216 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
217 end if;
218 end if;
219
220 if (l_return_codes(1) <> FND_API.G_RET_STS_SUCCESS) then
221 x_BILL_PTYSITE_ID := null;
222 l_save_flag := 'Y';
223 END IF;
224 end if;
225
226 if (x_SHIP_PTYSITE_ID is not null) then
227 -- call address validation apis; if either is not valid turn settings off
228 l_usage_codes(1) := 'SHIP_TO_COUNTRY';
229 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
230 ibe_util.debug('Calling IBE_ADDRESS_V2PVT.valid_usages ' || to_char(sysdate,'DD-MON-YYYY:HH24:MI:SS'));
231 ibe_util.debug(' party_site_id: ' || x_SHIP_PTYSITE_ID);
232 ibe_util.debug(' operating_unit_id: ' || l_org_id);
233 ibe_util.debug(' usage_code: ' || l_usage_codes(1));
234 END IF;
235 IBE_ADDRESS_V2PVT.valid_usages (
236 p_api_version => l_api_version,
237 p_init_msg_list => FND_API.G_FALSE,
238 p_party_site_id => x_SHIP_PTYSITE_ID,
239 p_operating_unit_id => l_org_id,
240 p_usage_codes => l_usage_codes,
241 x_return_codes => l_return_codes,
242 x_return_status => x_return_status,
243 x_msg_count => x_msg_count,
244 x_msg_data => x_msg_data );
245 --ibe_util.enable_debug;
246 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
247 ibe_util.debug('Back from IBE_ADDRESS_V2PVT.valid_usages ' || to_char(sysdate,'DD-MON-YYYY:HH24:MI:SS'));
248 ibe_util.debug(' return_code: ' || l_return_codes(1));
249 END IF;
250
251 if x_return_status <> FND_API.g_ret_sts_success then
252 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
253 ibe_util.debug('ibe_ord_oneclick_pvt.Get_Settings - non success status from IBE_ADDRESS_V2PVT.valid_usages: ' || x_return_status);
254 END IF;
255 FND_MESSAGE.SET_NAME('IBE','IBE_EXPR_PLSQL_API_ERROR');
256 FND_MESSAGE.SET_TOKEN ( '0' , 'Get_Settings - IBE_ADDRESS_V2PVT.valid_usages' );
257 FND_MESSAGE.SET_TOKEN ( '1' , x_return_status );
258 FND_MSG_PUB.Add;
259 if x_return_status = FND_API.G_RET_STS_ERROR then
260 RAISE FND_API.G_EXC_ERROR;
261 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
262 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
263 end if;
264 end if;
265
266 if (l_return_codes(1) <> FND_API.G_RET_STS_SUCCESS) then
267 x_SHIP_PTYSITE_ID := null;
268 l_save_flag := 'Y';
269 END IF;
270 end if;
271
272 if (l_save_flag = 'Y') then
273 x_ENABLED_FLAG := 'N';
274 -- we can assume we have a row of data
275 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
276 ibe_util.debug('One or more addresses were not valid for this orgid for that usage. Updating the settings row...');
277 ibe_util.debug('x_ENABLED_FLAG :'||x_ENABLED_FLAG);
278 ibe_util.debug('x_BILL_PTYSITE_ID :'||x_BILL_PTYSITE_ID);
279 ibe_util.debug('x_SHIP_PTYSITE_ID :'||x_SHIP_PTYSITE_ID);
280 END IF;
281
282 update IBE_ORD_ONECLICK
283 set enabled_flag = x_ENABLED_FLAG,
284 bill_to_pty_site_id = x_BILL_PTYSITE_ID,
285 ship_to_pty_site_id = x_SHIP_PTYSITE_ID,
286 object_version_number = x_OBJECT_VERSION_NUMBER + 1,
287 last_updated_by = FND_GLOBAL.user_id,
288 last_update_date = SYSDATE
289 where
290 ord_oneclick_id = x_ONECLICK_ID;
291
292 x_OBJECT_VERSION_NUMBER := x_OBJECT_VERSION_NUMBER + 1;
293 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
294 ibe_util.debug('x_OBJECT_VERSION_NUMBER :'||x_OBJECT_VERSION_NUMBER);
295 END IF;
296 end if;
297 end if; -- end of if l_org_id_s is not null
298
299 end if ; --if l_cvv2_setup
300
301 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
302 ibe_util.debug('Done with ibe_ord_oneclick_pvt.Get_Settings ' || to_char(sysdate,'DD-MON-YYYY:HH24:MI:SS'));
303 END IF;
304
305 IF FND_API.To_Boolean( p_commit ) THEN
306 COMMIT WORK;
307 END IF;
308 FND_MSG_PUB.Count_And_Get
309 ( p_encoded => FND_API.G_FALSE,
310 p_count => x_msg_count,
311 p_data => x_msg_data
312 );
313 --ibe_util.disable_debug;
314
315 EXCEPTION
316 WHEN FND_API.G_EXC_ERROR THEN
317 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
318 ibe_util.debug('ibe_ord_oneclick_pvt.Get_Settings: EXPECTED ERROR EXCEPTION ' || to_char(sysdate,'DD-MON-YYYY:HH24:MI:SS'));
319 END IF;
320 ROLLBACK TO Get_Settings_Pvt;
321 x_return_status := FND_API.G_RET_STS_ERROR ;
322 FND_MSG_PUB.Count_And_Get
323 ( p_encoded => FND_API.G_FALSE,
324 p_count => x_msg_count,
325 p_data => x_msg_data
326 );
327 --ibe_util.disable_debug;
328 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
329 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
330 ibe_util.debug('ibe_ord_oneclick_pvt.Get_Settings: UNEXPECTED ERROR EXCEPTION ' || to_char(sysdate,'DD-MON-YYYY:HH24:MI:SS'));
331 END IF;
332 ROLLBACK TO Get_Settings_Pvt;
333 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
334 FND_MSG_PUB.Count_And_Get
335 ( p_encoded => FND_API.G_FALSE,
336 p_count => x_msg_count,
337 p_data => x_msg_data
338 );
339 --ibe_util.disable_debug;
340 WHEN OTHERS THEN
341 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
342 ibe_util.debug('ibe_ord_oneclick_pvt.Get_Settings: OTHER EXCEPTION ' || to_char(sysdate,'DD-MON-YYYY:HH24:MI:SS'));
343 END IF;
344 ROLLBACK TO Get_Settings_Pvt;
345 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
346 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
347 THEN
348 FND_MSG_PUB.Add_Exc_Msg
349 ( G_PKG_NAME,
350 l_api_name
351 );
352 END IF;
353 FND_MSG_PUB.Count_And_Get
354 ( p_encoded => FND_API.G_FALSE,
355 p_count => x_msg_count,
356 p_data => x_msg_data
357 );
358 --ibe_util.disable_debug;
359 end Get_Settings;
360
361 /*-----------------------------------------------------------------------------
362
363 Save_Settings
364 - inserts/updates foreign keys into IBE_ORD_ONECLICK
365 - no longer saves user's email address associated with IBE_ORD_ONECLICK
366 - assumes address validation was done at the java layer before calling this api
367
368 ------------------------------------------------------------------------------
369 */
370 procedure Save_Settings(
371 p_api_version IN NUMBER,
372 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
373 p_commit IN VARCHAR2 := FND_API.g_false,
374 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
375 x_return_status OUT NOCOPY VARCHAR2,
376 x_msg_count OUT NOCOPY NUMBER,
377 x_msg_data OUT NOCOPY VARCHAR2,
378
379 p_party_id IN NUMBER := NULL,
380 p_acct_id IN NUMBER := NULL,
381
382 p_OBJECT_VERSION_NUMBER IN NUMBER := FND_API.G_MISS_NUM,
383 p_ENABLED_FLAG IN VARCHAR2 := 'N',
384 p_FREIGHT_CODE IN VARCHAR2 := FND_API.G_MISS_CHAR,
385 p_PAYMENT_ID IN NUMBER := FND_API.G_MISS_NUM,
386 p_BILL_PTYSITE_ID IN NUMBER := FND_API.G_MISS_NUM,
387 p_SHIP_PTYSITE_ID IN NUMBER := FND_API.G_MISS_NUM
388 ) is
389 l_api_name CONSTANT VARCHAR2(30) := 'Save_Settings';
390 l_api_version CONSTANT NUMBER := 1.0;
391
392 l_oneclick_id NUMBER := FND_API.g_miss_num;
393
394 CURSOR c_oneclick_row(c_party_id NUMBER, c_acct_id NUMBER) IS
395 SELECT ord_oneclick_id
396 from IBE_ORD_ONECLICK
397 where party_id = c_party_id and cust_account_id = c_acct_id;
398
399 begin
400 -- ibe_util.enable_debug;
401 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
402 ibe_util.debug('Starting ibe_ord_oneclick_pvt.Save_Settings ' || to_char(sysdate,'DD-MON-YYYY:HH24:MI:SS'));
403 END IF;
404 SAVEPOINT Save_Settings_Pvt;
405 -- Standard call to check for call compatibility.
406 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
407 p_api_version ,
408 l_api_name ,
409 G_PKG_NAME )
410 THEN
411 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
412 END IF;
413 -- Initialize message list if p_init_msg_list is set to TRUE.
414 IF FND_API.to_Boolean( p_init_msg_list ) THEN
415 FND_MSG_PUB.initialize;
416 END IF;
417
418 -- Initialize API rturn status to success
419 x_return_status := FND_API.g_ret_sts_success;
420 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
421 ibe_util.debug('-------------Input Parameters ----------');
422 ibe_util.debug('party_id :'||p_party_id);
423 ibe_util.debug('account_id :'||p_acct_id);
424 ibe_util.debug('p_OBJECT_VERSION_NUMBER :'||p_OBJECT_VERSION_NUMBER);
425 ibe_util.debug('p_ENABLED_FLAG :'||p_ENABLED_FLAG);
426 ibe_util.debug('p_FREIGHT_CODE :'||p_FREIGHT_CODE);
427 ibe_util.debug('p_PAYMENT_ID :'||p_PAYMENT_ID);
428 ibe_util.debug('p_BILL_PTYSITE_ID :'||p_BILL_PTYSITE_ID);
429 ibe_util.debug('p_SHIP_PTYSITE_ID :'||p_SHIP_PTYSITE_ID);
430 ibe_util.debug('----------Input Parameters /end --------');
431 END IF;
432
433 open c_oneclick_row(p_party_id, p_acct_id);
434 fetch c_oneclick_row into l_oneclick_id;
435
436 if c_oneclick_row%NOTFOUND then
437 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
438 ibe_util.debug('ibe_ord_oneclick_pvt.Save_Settings - inserting new row');
439 END IF;
440 insert into IBE_ORD_ONECLICK_ALL (
441 object_version_number,
442 ord_oneclick_id,
443 party_id,
444 cust_account_id,
445 enabled_flag,
446 freight_code,
447 payment_id,
448 bill_to_pty_site_id,
449 ship_to_pty_site_id,
450 org_id,
451 created_by,
452 creation_date,
453 last_updated_by,
454 last_update_date)
455 values (1,
456 IBE_ORD_ONECLICK_S1.NEXTVAL,
457 p_party_id,
458 p_acct_id,
459 p_ENABLED_FLAG,
460 p_FREIGHT_CODE,
461 p_PAYMENT_ID,
462 p_BILL_PTYSITE_ID,
463 p_SHIP_PTYSITE_ID,
464 MO_Global.get_current_org_id(),
465 FND_GLOBAL.user_id,
466 SYSDATE,
467 FND_GLOBAL.user_id,
468 SYSDATE)
469 returning ord_oneclick_id into l_oneclick_id;
470 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
471 ibe_util.debug('ibe_ord_oneclick_pvt.Save_Settings - new ord_oneclick_id: ' || to_char(l_oneclick_id));
472 END IF;
473 else
474 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
475 ibe_util.debug('ibe_ord_oneclick_pvt.Save_Settings - updating row');
476 END IF;
477 update IBE_ORD_ONECLICK
478 set
479 object_version_number = p_OBJECT_VERSION_NUMBER + 1,
480 enabled_flag = p_ENABLED_FLAG,
481 freight_code = p_FREIGHT_CODE,
482 payment_id = p_PAYMENT_ID,
483 bill_to_pty_site_id = p_BILL_PTYSITE_ID,
484 ship_to_pty_site_id = p_SHIP_PTYSITE_ID,
485 last_updated_by = FND_GLOBAL.user_id,
486 last_update_date = SYSDATE
487 where
488 ord_oneclick_id = l_oneclick_id and
489 party_id = p_party_id and
490 cust_account_id = p_acct_id and
491 object_version_number = p_OBJECT_VERSION_NUMBER;
492
493 IF (SQL%NOTFOUND) THEN
494 FND_MESSAGE.Set_Name('IBE', 'IBE_SAVE_ERROR');
495 FND_MSG_PUB.Add;
496 RAISE FND_API.G_EXC_ERROR;
497 END IF;
498 end if;
499 close c_oneclick_row;
500
501 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
502 ibe_util.debug('Done with ibe_ord_oneclick_pvt.Save_Settings ' || to_char(sysdate,'DD-MON-YYYY:HH24:MI:SS'));
503 END IF;
504 IF FND_API.To_Boolean( p_commit ) THEN
505 COMMIT WORK;
506 END IF;
507
508 FND_MSG_PUB.Count_And_Get
509 ( p_encoded => FND_API.G_FALSE,
510 p_count => x_msg_count,
511 p_data => x_msg_data
512 );
513 --ibe_util.disable_debug;
514 EXCEPTION
515 WHEN FND_API.G_EXC_ERROR THEN
516 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
517 ibe_util.debug('ibe_ord_oneclick_pvt.Save_Settings: EXPECTED ERROR EXCEPTION ' || to_char(sysdate,'DD-MON-YYYY:HH24:MI:SS'));
518 END IF;
519 ROLLBACK TO Save_Settings_Pvt;
520 x_return_status := FND_API.G_RET_STS_ERROR ;
521 FND_MSG_PUB.Count_And_Get
522 ( p_encoded => FND_API.G_FALSE,
523 p_count => x_msg_count,
524 p_data => x_msg_data
525 );
526 --ibe_util.disable_debug;
527 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
528 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
529 ibe_util.debug('ibe_ord_oneclick_pvt.Save_Settings: UNEXPECTED ERROR EXCEPTION ' || to_char(sysdate,'DD-MON-YYYY:HH24:MI:SS'));
530 END IF;
531 ROLLBACK TO Save_Settings_Pvt;
532 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
533 FND_MSG_PUB.Count_And_Get
534 ( p_encoded => FND_API.G_FALSE,
535 p_count => x_msg_count,
536 p_data => x_msg_data
537 );
538 --ibe_util.disable_debug;
539 WHEN OTHERS THEN
540 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
541 ibe_util.debug('ibe_ord_oneclick_pvt.Save_Settings: OTHER EXCEPTION ' || to_char(sysdate,'DD-MON-YYYY:HH24:MI:SS'));
542 END IF;
543 ROLLBACK TO Save_Settings_Pvt;
544 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
545 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
546 THEN
547 FND_MSG_PUB.Add_Exc_Msg
548 ( G_PKG_NAME,
549 l_api_name
550 );
551 END IF;
552 FND_MSG_PUB.Count_And_Get
553 ( p_encoded => FND_API.G_FALSE,
554 p_count => x_msg_count,
555 p_data => x_msg_data
556 );
557 --ibe_util.disable_debug;
558 end Save_Settings;
559
560 /*-----------------------------------------------------------------------------
561
562 Express_Buy_Order
563 - Handles Ordering of Individual Items, Shopping Lists,
564 Lines in one Shopping List, and a Shopping Cart
565 - p_flag drives the behavior with possible values: 'ITEMS', 'CART', 'LISTS', 'LIST_LINES'
566 - depending on the p_flag, appropriate input parameters are expected to be set
567 - return the cartid that was ultimately created/updated
568
569 ------------------------------------------------------------------------------
570 */
571
572 procedure Express_Buy_Order(
573 p_api_version IN NUMBER,
574 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
575 p_commit IN VARCHAR2 := FND_API.g_false,
576 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
577 x_return_status OUT NOCOPY VARCHAR2,
578 x_msg_count OUT NOCOPY NUMBER,
579 x_msg_data OUT NOCOPY VARCHAR2,
580
581 -- identification
582 p_party_id IN NUMBER,
583 p_acct_id IN NUMBER,
584 p_retrieval_num IN NUMBER := FND_API.g_miss_num, -- optional, only if recipient is expressing a cart
585
586 -- common pricing parameters
587 p_currency_code IN VARCHAR2 := FND_API.g_miss_char,
588 p_price_list_id IN NUMBER := FND_API.g_miss_num,
589 p_price_req_type IN VARCHAR2 := FND_API.g_miss_char,
590 p_incart_event IN VARCHAR2 := FND_API.g_miss_char,
591 p_incart_line_event IN VARCHAR2 := FND_API.g_miss_char,
592
593 -- flag to drive behavior
594 -- (values: 'ITEMS', 'CART', 'LISTS', 'LIST_LINES')
595 p_flag IN VARCHAR2 := FND_API.g_miss_char,
596
597 -- for express checkout of a shopping cart
598 p_cart_id IN NUMBER := FND_API.g_miss_num,
599 p_minisite_id IN NUMBER := FND_API.g_miss_num, -- for stop sharing notification
600
601 -- for express checkout of a list of shopping lists
602 p_list_ids IN JTF_NUMBER_TABLE,
603 p_list_ovns IN JTF_NUMBER_TABLE,
604
605 -- for express checkout of a list of shopping list lines
606 p_list_line_ids IN JTF_NUMBER_TABLE,
607 p_list_line_ovns IN JTF_NUMBER_TABLE,
608
609 -- for express checkout of a list of items (usually from catalog)
610 p_item_ids IN JTF_NUMBER_TABLE,
611 p_qtys IN JTF_NUMBER_TABLE,
612 p_org_ids IN JTF_NUMBER_TABLE,
613 p_uom_codes IN JTF_VARCHAR2_TABLE_100,
614
615 -- return the quote header id
616 x_new_cart_id OUT NOCOPY NUMBER,
617
618 -- TimeStamp check
619 p_last_update_date IN DATE := FND_API.G_MISS_DATE,
620 x_last_update_date OUT NOCOPY DATE
621
622 ) is
623 l_api_name CONSTANT VARCHAR2(30) := 'Express_Buy_Order';
624 l_api_version CONSTANT NUMBER := 1.0;
625
626 -- local variables for finding an active express cart
627 l_curr_cart_id NUMBER := FND_API.g_miss_num;
628 l_curr_cart_date DATE := FND_API.G_MISS_DATE;
629 l_curr_cart_currcode VARCHAR2(15) := FND_API.G_MISS_CHAR;
630
631 l_retrieval_num NUMBER := FND_API.g_miss_num;
632 l_sharee_party_id NUMBER := FND_API.g_miss_num;
633 l_sharee_acct_id NUMBER := FND_API.g_miss_num;
634
635 l_object_version_number NUMBER := FND_API.G_MISS_NUM;
636 l_ord_oneclick_id NUMBER := FND_API.G_MISS_NUM;
637 l_enabled_flag VARCHAR2(1) := 'N';
638 l_freight_code VARCHAR2(25) := FND_API.G_MISS_CHAR;
639 l_payment_id NUMBER := FND_API.G_MISS_NUM;
640 l_bill_to_pty_site_id NUMBER := FND_API.G_MISS_NUM;
641 l_ship_to_pty_site_id NUMBER := FND_API.G_MISS_NUM;
642 l_settings_date DATE := FND_API.G_MISS_DATE;
643
644 -- local variables and cursors for updating a quote
645 l_save_trigger VARCHAR2(1) := 'Y';
646 l_credit_card_num NUMBER;
647 l_credit_card_name VARCHAR2(80);
648 l_credit_card_exp DATE;
649 l_credit_card_holder_name VARCHAR2(50);
650 l_control_rec ASO_QUOTE_PUB.Control_Rec_Type;
651 l_qte_header_rec ASO_QUOTE_PUB.Qte_Header_Rec_Type;
652 l_qte_line_tbl ASO_QUOTE_PUB.Qte_Line_Tbl_Type;
653 l_hd_payment_tbl ASO_QUOTE_PUB.Payment_Tbl_Type;
654 l_hd_shipment_tbl ASO_QUOTE_PUB.Shipment_Tbl_Type;
655 l_hd_tax_detail_tbl ASO_QUOTE_PUB.Tax_Detail_Tbl_Type;
656 l_payment_rec ASO_QUOTE_PUB.Payment_Rec_Type;
657 l_shipment_rec ASO_QUOTE_PUB.Shipment_Rec_Type;
658 l_tax_detail_rec ASO_QUOTE_PUB.Tax_Detail_Rec_Type;
659 l_attach_contract VARCHAR2(1);
660 l_contract_template_id NUMBER;
661
662 lx_quote_header_id NUMBER;
663 lx_last_update_date DATE;
664
665 l_party_type VARCHAR2(30);
666 l_optional_party_id NUMBER := FND_API.g_miss_num;
667 l_count_tax NUMBER := 0;
668 l_payment_rec_id NUMBER := FND_API.G_MISS_NUM;
669 l_count NUMBER := 0;
670
671 CURSOR c_quote(c_party_id NUMBER, c_acct_id NUMBER) IS
672 SELECT quote_header_id, creation_date, currency_code
673 FROM aso_quote_headers
674 WHERE
675 quote_source_code = 'IStore Oneclick' and
676 party_id = c_party_id and
677 cust_account_id = c_acct_id and
678 quote_name is null and
679 order_id is null and
680 nvl(trunc(quote_expiration_date), trunc(sysdate)+1) >= trunc(sysdate);
681
682 CURSOR c_quote_date(c_qte_header_id NUMBER) IS
683 SELECT last_update_date
684 FROM ASO_QUOTE_HEADERS
685 WHERE quote_header_id = c_qte_header_id;
686
687 BEGIN
688 --ibe_util.enable_debug;
689 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
690 ibe_util.debug('Starting ibe_ord_oneclick_pvt.Express_Buy_Order ' || to_char(sysdate,'DD-MON-YYYY:HH24:MI:SS'));
691 ibe_util.debug('PROCESSING FLAG : ' || p_flag);
692 END IF;
693 SAVEPOINT Express_Buy_Order_Pvt;
694 -- Standard call to check for call compatibility.
695 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
696 p_api_version ,
697 l_api_name ,
698 G_PKG_NAME )
699 THEN
700 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
701 END IF;
702 -- Initialize message list if p_init_msg_list is set to TRUE.
703 IF FND_API.to_Boolean( p_init_msg_list ) THEN
704 FND_MSG_PUB.initialize;
705 END IF;
706
707 -- Initialize API rturn status to success
708 x_return_status := FND_API.g_ret_sts_success;
709
710
711 l_qte_header_rec.quote_header_id := p_cart_id;
712 l_qte_header_rec.party_id := p_party_id;
713 l_qte_header_rec.Cust_account_id := p_acct_id;
714 l_qte_header_rec.currency_code := p_currency_code;
715 l_qte_header_rec.price_list_id := p_price_list_id;
716
717
718 IF(p_flag = 'CART') THEN
719 l_save_trigger := 'Y';
720
721 if ((p_retrieval_num is not null) and (p_retrieval_num <> FND_API.g_miss_num)) then
722
723 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
724 ibe_util.debug('Express_Buy_Order: Validate_user_update START');
725 ibe_util.debug('Express_Buy_Order: p_party_id: '||p_party_id);
726 ibe_util.debug('Express_Buy_Order: p_acct_id: '||p_acct_id);
727 ibe_util.debug('Express_Buy_Order: p_retrieval_number: '||p_retrieval_num);
728 END IF;
729
730 IBE_Quote_Misc_pvt.Validate_User_Update
731 (p_init_msg_list => p_Init_Msg_List
732 ,p_quote_header_id => p_cart_id
733 ,p_party_id => p_party_id
734 ,p_cust_account_id => p_acct_id
735 ,p_quote_retrieval_number => p_retrieval_num
736 ,p_validate_user => FND_API.G_TRUE
737 ,p_privilege_type_code => 'A'
738 ,p_last_update_date => p_last_update_date
739 ,x_return_status => x_return_status
740 ,x_msg_count => x_msg_count
741 ,x_msg_data => x_msg_data );
742
743 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
744 RAISE FND_API.G_EXC_ERROR;
745 END IF;
746 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
747 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
748 END IF;
749
750 end if;
751
752 END IF; -- IF(p_flag = 'CART') THEN
753
754 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
755 ibe_util.debug('Starting ibe_ord_oneclick_pvt.Get_Express_items_settings ' );
756 ibe_util.debug('Input Cartid: '||p_cart_id);
757 ibe_util.debug('Input Partyid: '||p_party_id);
758 ibe_util.debug('Input Accountid: '||p_acct_id);
759 ibe_util.debug('Input p_flag: '||p_flag);
760 ibe_util.debug('Input p_last_update_date: '||p_last_update_date);
761 END IF;
762
763 IBE_ORD_ONECLICK_PVT.Get_Express_items_settings(
764 x_qte_header_rec => l_qte_header_rec
765 ,p_flag => p_flag
766 ,x_payment_tbl => l_hd_payment_tbl
767 ,x_hd_shipment_tbl => l_hd_shipment_tbl
768 ,x_hd_tax_dtl_tbl => l_hd_tax_detail_tbl);
769
770 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
771 ibe_util.debug('Output Cartid: '||l_qte_header_rec.quote_header_id);
772 END IF;
773 l_curr_cart_id := l_qte_header_rec.quote_header_id;
774
775 if (p_flag = 'ITEMS') then
776 l_save_trigger := 'Y';
777 --set items info
778
779 if (p_item_ids.count > 0) then
780 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
781 ibe_util.debug('Express_Buy_Order - ***adding lines for products***');
782 ibe_util.debug('Express_Buy_Order - build list of quote lines for oneclick cart');
783 END IF;
784 l_count := p_item_ids.count;
785 FOR i IN 1..l_count LOOP
786 l_qte_line_tbl(i).operation_code := 'CREATE';
787 l_qte_line_tbl(i).QUOTE_HEADER_id := l_curr_cart_id;
788 l_qte_line_tbl(i).INVENTORY_ITEM_ID := p_item_ids(i);
789 l_qte_line_tbl(i).QUANTITY := p_qtys(i);
790 l_qte_line_tbl(i).organization_id := p_org_ids(i);
791 l_qte_line_tbl(i).UOM_CODE := p_uom_codes(i);
792 --l_qte_line_tbl(i).price_list_id := p_flags.PRICE_LIST_ID;
793 --l_qte_line_tbl(i).currency_code := p_flags.CURRENCY_CODE;
794 l_qte_line_tbl(i).line_category_code := 'ORDER';
795
796 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
797 ibe_util.debug('l_qte_line_tbl(i).operation_code ' || l_qte_line_tbl(i).operation_code);
798 ibe_util.debug('l_qte_line_tbl(i).QUOTE_HEADER_id ' || to_char(l_qte_line_tbl(i).QUOTE_HEADER_id));
799 ibe_util.debug('l_qte_line_tbl(i).INVENTORY_ITEM_ID ' || to_char(l_qte_line_tbl(i).INVENTORY_ITEM_ID));
800 ibe_util.debug('l_qte_line_tbl(i).QUANTITY ' || l_qte_line_tbl(i).QUANTITY);
801 ibe_util.debug('l_qte_line_tbl(i).organization_id ' || l_qte_line_tbl(i).operation_code);
802 ibe_util.debug('l_qte_line_tbl(i).UOM_CODE ' || l_qte_line_tbl(i).UOM_CODE);
803 END IF;
804 --ibe_util.debug('l_qte_line_tbl(i).price_list_id ' || to_char(l_qte_line_tbl(i).price_list_id));
805 --ibe_util.debug('l_qte_line_tbl(i).currency_code ' || l_qte_line_tbl(i).currency_code);
806 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
807 ibe_util.debug('l_qte_line_tbl(i).line_category_code ' || l_qte_line_tbl(i).line_category_code);
808 END IF;
809 End Loop;
810 else
811 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
812 ibe_util.debug('Express_Buy_Order - oneclick update: no products to add!');
813 END IF;
814 end if;
815 end if; --end if adding itmes
816
817 IF (l_curr_cart_id <> FND_API.g_miss_num) then
818 l_qte_header_rec.quote_header_id := l_curr_cart_id;
819 /*OPEN c_quote_date(l_qte_header_rec.quote_header_id);
820 FETCH c_quote_date INTO l_qte_header_rec.last_update_date;
821 CLOSE c_quote_date;*/
822 -- We need to pass the lastupdate from UI to check for concurrency issues.
823 l_qte_header_rec.last_update_date := p_last_update_date;
824 l_control_rec.last_update_date := l_qte_header_rec.last_update_date;
825 IF(p_flag = 'CART') THEN
826 --Contract needs to be attached to the xpressed checkout cart.
827 --There is no consolidation for carts, hence a contract has to be
828 --attached every time a cart is xpressed checked out
829 l_attach_contract :=FND_API.G_TRUE;
830 END IF;
831
832 ELSE
833 -- clear these just in case the previous code used these recs
834 l_qte_header_rec.quote_header_id := FND_API.G_MISS_NUM;
835 l_qte_header_rec.quote_name := '';
836 l_qte_header_rec.last_update_date := FND_API.G_MISS_DATE;
837 l_control_rec.last_update_date := FND_API.G_MISS_DATE;
838 l_attach_contract := FND_API.G_TRUE;
839 END IF;
840
841 -- regardless we will need to reprice - whether a new express quote or adding items or adding from lists
842 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
843 ibe_util.debug('Express_Buy_Order - ***setting pricing flags***');
844 END IF;
845 l_control_rec.pricing_request_type := p_price_req_type;
846 l_control_rec.header_pricing_event := p_incart_event;
847 l_control_rec.line_pricing_event := p_incart_line_event;
848 l_control_rec.CALCULATE_TAX_FLAG := 'Y';
849 l_control_rec.CALCULATE_FREIGHT_CHARGE_FLAG := 'Y';
850 l_qte_header_rec.currency_code := p_currency_code;
851 l_qte_header_rec.price_list_id := p_price_list_id;
852 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
853 ibe_util.debug('l_control_rec.pricing_request_type ' || l_control_rec.pricing_request_type);
854 ibe_util.debug('l_control_rec.header_pricing_event ' || l_control_rec.header_pricing_event);
855 ibe_util.debug('l_control_rec.line_pricing_event ' || l_control_rec.line_pricing_event);
856 ibe_util.debug('l_control_rec.CALCULATE_TAX_FLAG ' || l_control_rec.CALCULATE_TAX_FLAG);
857 ibe_util.debug('l_control_rec.CALCULATE_FREIGHT_CHARGE_FLAG ' || l_control_rec.CALCULATE_FREIGHT_CHARGE_FLAG);
858 ibe_util.debug('l_qte_header_rec.currency_code' || l_qte_header_rec.currency_code);
859 ibe_util.debug('l_qte_header_rec.price_list_id ' || l_qte_header_rec.price_list_id);
860 END IF;
861
862 IF (l_save_trigger = 'Y') then
863 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
864 ibe_util.debug('****Calling IBE_Quote_Save_pvt.SAVE**** ' || to_char(sysdate,'DD-MON-YYYY:HH24:MI:SS'));
865 ibe_util.debug(' l_qte_header_rec.quote_header_id : ' || l_qte_header_rec.quote_header_id);
866 ibe_util.debug(' l_qte_header_rec.quote_name : ' || l_qte_header_rec.quote_name);
867 ibe_util.debug(' l_qte_header_rec.last_update_date : ' || to_char(l_qte_header_rec.last_update_date,'DD-MON-YYYY:HH24:MI:SS'));
868 END IF;
869
870 IF (p_retrieval_num <> FND_API.g_miss_num) then
871 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
872 ibe_util.debug('passing sharee party and acct id');
873 END IF;
874 l_qte_header_rec.party_id := FND_API.G_MISS_NUM;
875 l_qte_header_rec.cust_account_id := FND_API.G_MISS_NUM;
876 l_sharee_party_id := p_party_id;
877 l_sharee_acct_id := p_acct_id;
878 END IF;
879
880 IBE_Quote_Save_pvt.SAVE(
881 p_api_version_number => p_api_version,
882 p_init_msg_list => FND_API.G_FALSE,
883 p_commit => FND_API.g_false,
884 p_auto_update_active_quote => FND_API.g_false,
885 -- p_combineSameItem => 'Y', -- let api default from profile
886 p_sharee_Number => p_retrieval_num,
887 p_sharee_party_id => l_sharee_party_id,
888 p_sharee_cust_account_id => l_sharee_acct_id,
889 p_minisite_id => p_minisite_id,
890 p_control_rec => l_control_rec,
891 p_qte_header_rec => l_qte_header_rec,
892 P_Qte_Line_Tbl => l_qte_line_tbl,
893 P_hd_Payment_Tbl => l_hd_Payment_Tbl,
894 P_hd_Shipment_Tbl => l_hd_Shipment_Tbl,
895 P_hd_Tax_Detail_Tbl => l_hd_Tax_Detail_Tbl,
896
897 x_quote_header_id => lx_quote_header_id,
898 x_last_update_date => lx_last_update_date,
899 X_Return_Status => x_Return_Status,
900 X_Msg_Count => x_Msg_Count,
901 X_Msg_Data => x_Msg_Data);
902
903 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
904 ibe_util.debug('Back from IBE_Quote_Save_pvt.SAVE ' || to_char(sysdate,'DD-MON-YYYY:HH24:MI:SS') || ' quote_header_id: ' || to_char(lx_quote_header_id));
905 END IF;
906 IF x_return_status <> FND_API.g_ret_sts_success THEN
907 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
908 ibe_util.debug('ibe_ord_oneclick_pvt.Express_Buy_Order - non success status from IBE_Quote_Save_pvt.SAVE: ' || x_return_status); FND_MESSAGE.SET_NAME('IBE','IBE_EXPR_PLSQL_API_ERROR');
909 END IF;
910 FND_MESSAGE.SET_TOKEN ( '0' , 'Express_Buy_Order - IBE_Quote_Save_pvt.SAVE' );
911 FND_MESSAGE.SET_TOKEN ( '1' , x_return_status );
912 FND_MSG_PUB.Add;
913 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
914 RAISE FND_API.G_EXC_ERROR;
915 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
916 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
917 END IF;
918 ELSE
919 IF(l_attach_contract = FND_API.G_TRUE) THEN
920 IF (OKC_TERMS_UTIL_GRP.Get_Terms_Template('QUOTE', p_cart_id) IS NULL) THEN -- Checking whether a contract has already been attached (Bug 5260372)
921 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
922 ibe_util.debug('ibe_ord_oneclick_pvt.Express_Buy_Order - : No contract currently attached !');
923 END IF;
924 IF (FND_Profile.Value('OKC_ENABLE_SALES_CONTRACTS') = 'Y' ) THEN --Only if contracts is enabled
925 --instantiate a contract and associate to the quote
926 /*mannamra: changes for MOAC: Bug 4682364 */
927 --l_contract_template_id := FND_PROFILE.VALUE('ASO_DEFAULT_CONTRACT_TEMPLATE'); old style
928 l_contract_template_id := to_number(ASO_UTILITY_PVT.GET_OU_ATTRIBUTE_VALUE(ASO_UTILITY_PVT.G_DEFAULT_CONTRACT_TEMPLATE)); --New style
929 /*mannamra: end of changes for MOAC*/
930
931 IF (l_contract_template_id is not null) THEN
932 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
933 IBE_UTIL.debug('l_contract_template_id = '||l_contract_template_id);
934 IBE_UTIL.debug('Before calling OKC_TERMS_COPY_GRP.copy_terms_api, quoteheaderId = '||lx_quote_header_id);
935 END IF;
936 OKC_TERMS_COPY_GRP.copy_terms(
937 p_api_version =>1.0
938 ,p_template_id => l_contract_template_id
939 ,p_target_doc_type => 'QUOTE'
940 ,p_target_doc_id => lx_quote_header_id
941 ,p_article_effective_date => null
942 ,p_validation_string => null
943 ,x_return_status => x_return_status
944 ,x_msg_count => x_msg_count
945 ,x_msg_data => x_msg_data);
946
947 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
948 IBE_UTIL.debug('After copy_terms api, return status = '||x_return_status);
949 END IF;
950 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
951 RAISE FND_API.G_EXC_ERROR;
952 END IF;
953 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
954 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
955 END IF;
956 END IF; --If contract template id is not null
957 END IF; --IF (FND_Profile.Value('OKC_ENABLE_SALES_CONTRACTS')
958 ELSE
959 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
960 ibe_util.debug('ibe_ord_oneclick_pvt.Express_Buy_Order - : A contract is already instanciated for this quote');
961 END IF;
962 END IF; -- Whether there is a contract currently instanciated for this quote
963 END IF; --If l_attach_contract is true
964 END IF; -- If l_return_status is success
965 IF (p_flag = 'CART') THEN
966 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
967 ibe_util.debug('Express_buy_order: Calling stop_sharing for quote_header_id :'||l_qte_header_rec.quote_header_id);
968 END IF;
969 IBE_QUOTE_SAVESHARE_V2_PVT.stop_sharing (
970 p_quote_header_id => p_cart_id ,
971 p_delete_context => 'IBE_SC_CART_ORDERED',
972 P_minisite_id => p_minisite_id ,
973 p_api_version => p_api_version ,
974 p_init_msg_list => fnd_api.g_false ,
975 p_commit => fnd_api.g_false ,
976 x_return_status => x_return_status ,
977 x_msg_count => x_msg_count ,
978 x_msg_data => x_msg_data );
979
980
981 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
982 RAISE FND_API.G_EXC_ERROR;
983 END IF;
984
985 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
986 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
987 END IF;
988 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
989 ibe_util.debug('Express_buy_order: Done Calling stop_sharing');
990 END IF;
991 END IF;
992
993 l_curr_cart_id := lx_quote_header_id;
994 l_qte_header_rec.quote_header_id := l_curr_cart_id;
995 l_qte_header_rec.last_update_date := lx_last_update_date;
996 l_control_rec.last_update_date := lx_last_update_date;
997 end if; -- end if needed to call quote.save
998
999 -- get ready for the next update of the quote
1000
1001 /* if (p_flag = 'LISTS') then
1002 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1003 ibe_util.debug('Calling IBE_Shop_List_PVT.Save_Quote_From_Lists ' || to_char(sysdate,'DD-MON-YYYY:HH24:MI:SS'));
1004 ibe_util.debug('p_mode ' || 'MERGE');
1005 END IF;
1006 l_count := p_list_ids.count;
1007 FOR i IN 1..l_count LOOP
1008 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1009 ibe_util.debug('p_shpListIds' || i || ' ' || p_list_ids(i));
1010 END IF;
1011 END LOOP;
1012
1013 IBE_Shop_List_PVT.Save_Quote_From_Lists(
1014 P_Api_Version => 1.0
1015 ,P_Init_Msg_List => FND_API.G_FALSE
1016 ,P_Commit => FND_API.G_FALSE
1017 ,p_mode => 'MERGE'
1018 --,p_combine_same_item => null -- let default from profile
1019 ,p_sl_header_ids => p_list_ids
1020 ,p_sl_header_ovns => p_list_ovns
1021 ,p_control_rec => l_control_rec
1022 ,p_q_header_rec => l_qte_header_rec
1023 ,p_url => null
1024 ,p_comments => null
1025 ,X_Return_Status => x_return_status
1026 ,X_Msg_Count => x_msg_count
1027 ,X_Msg_Data => x_msg_data
1028 ,x_q_header_id => lx_quote_header_id );
1029
1030 --ibe_util.enable_debug;
1031 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1032 ibe_util.debug('Back from IBE_Shop_List_PVT.Save_Quote_From_Lists ' || to_char(sysdate,'DD-MON-YYYY:HH24:MI:SS'));
1033 ibe_util.debug('X_cartId ' || lx_quote_header_id);
1034 END IF;
1035
1036 IF x_return_status <> FND_API.g_ret_sts_success THEN
1037 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1038 ibe_util.debug('ibe_ord_oneclick_pvt.Express_Buy_Order - non success status from IBE_Shop_List_PVT.Save_Quote_From_Lists: ' || x_return_status); FND_MESSAGE.SET_NAME('IBE','IBE_EXPR_PLSQL_API_ERROR');
1039 END IF;
1040 FND_MESSAGE.SET_TOKEN ( '0' , 'Express_Buy_Order - IBE_Shop_List_PVT.Save_Quote_From_Lists' );
1041 FND_MESSAGE.SET_TOKEN ( '1' , x_return_status );
1042 FND_MSG_PUB.Add;
1043 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1044 RAISE FND_API.G_EXC_ERROR;
1045 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1046 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1047 END IF;
1048 END IF;
1049
1050 END IF;
1051 */
1052 if (p_flag = 'LIST_LINES') then
1053 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1054 ibe_util.debug('Calling IBE_Shop_List_PVT.Save_Quote_From_List_Items ' || to_char(sysdate,'DD-MON-YYYY:HH24:MI:SS'));
1055 ibe_util.debug('p_mode ' || 'MERGE');
1056 END IF;
1057 l_count := p_list_line_ids.count;
1058 FOR i IN 1..l_count LOOP
1059 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1060 ibe_util.debug('p_shpListLineIds' || i || ' ' || p_list_line_ids(i));
1061 END IF;
1062 end loop;
1063 IBE_Shop_List_PVT.Save_Quote_From_List_Items(
1064 P_Api_Version => 1.0
1065 ,P_Init_Msg_List => FND_API.G_FALSE
1066 ,P_Commit => FND_API.G_FALSE
1067 ,p_mode => 'MERGE'
1068 -- ,p_combine_same_item => null -- let default from profile
1069 ,p_sl_line_ids => p_list_line_ids
1070 ,p_sl_line_ovns => p_list_line_ovns
1071 ,p_control_rec => l_control_rec
1072 ,p_q_header_rec => l_qte_header_rec
1073 ,p_url => null
1074 ,p_comments => null
1075 ,X_Return_Status => x_return_status
1076 ,X_Msg_Count => x_msg_count
1077 ,X_Msg_Data => x_msg_data
1078 ,x_q_header_id => lx_quote_header_id );
1079
1080 --ibe_util.enable_debug;
1081 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1082 ibe_util.debug('Back from IBE_Shop_List_PVT.Save_Quote_From_List_Items ' || to_char(sysdate,'DD-MON-YYYY:HH24:MI:SS'));
1083 ibe_util.debug('X_cartId ' || lx_quote_header_id);
1084 END IF;
1085
1086 if x_return_status <> FND_API.g_ret_sts_success then
1087 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1088 ibe_util.debug('ibe_ord_oneclick_pvt.Express_Buy_Order - non success status from IBE_Shop_List_PVT.Save_Quote_From_List_Items: ' || x_return_status); FND_MESSAGE.SET_NAME('IBE','IBE_EXPR_PLSQL_API_ERROR');
1089 END IF;
1090 FND_MESSAGE.SET_NAME('IBE','IBE_EXPR_PLSQL_API_ERROR');
1091 FND_MESSAGE.SET_TOKEN ( '0' , 'Express_Buy_Order - IBE_Shop_List_PVT.Save_Quote_From_List_Items' );
1092 FND_MESSAGE.SET_TOKEN ( '1' , x_return_status );
1093 FND_MSG_PUB.Add;
1094 if x_return_status = FND_API.G_RET_STS_ERROR then
1095 RAISE FND_API.G_EXC_ERROR;
1096 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
1097 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1098 end if;
1099 end if;
1100 end if;
1101
1102 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1103 ibe_util.debug('Express_Buy_Order - BOTTOM LINE: express cart used: ' || l_curr_cart_id);
1104 END IF;
1105 -- let the caller know which quote we finally updated
1106 x_new_cart_id := l_curr_cart_id;
1107
1108 IF FND_API.To_Boolean( p_commit ) THEN
1109 COMMIT WORK;
1110 END IF;
1111 FND_MSG_PUB.Count_And_Get
1112 ( p_encoded => FND_API.G_FALSE,
1113 p_count => x_msg_count ,
1114 p_data => x_msg_data );
1115
1116 --ibe_util.disable_debug;
1117 EXCEPTION
1118 WHEN FND_API.G_EXC_ERROR THEN
1119 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1120 ibe_util.debug('ibe_ord_oneclick_pvt.Express_Buy_Order: EXPECTED ERROR EXCEPTION ' || to_char(sysdate,'DD-MON-YYYY:HH24:MI:SS'));
1121 END IF;
1122 ROLLBACK TO Express_Buy_Order_Pvt;
1123 x_return_status := FND_API.G_RET_STS_ERROR;
1124 x_last_update_date := IBE_Quote_Misc_pvt.getQuoteLastUpdateDate(p_cart_id);
1125 FND_MSG_PUB.Count_And_Get
1126 ( p_encoded => FND_API.G_FALSE,
1127 p_count => x_msg_count,
1128 p_data => x_msg_data
1129 );
1130 --ibe_util.disable_debug;
1131 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1132 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1133 ibe_util.debug('ibe_ord_oneclick_pvt.Express_Buy_Order: UNEXPECTED ERROR EXCEPTION ' || to_char(sysdate,'DD-MON-YYYY:HH24:MI:SS'));
1134 END IF;
1135 ROLLBACK TO Express_Buy_Order_Pvt;
1136 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1137 FND_MSG_PUB.Count_And_Get
1138 ( p_encoded => FND_API.G_FALSE,
1139 p_count => x_msg_count,
1140 p_data => x_msg_data
1141 );
1142 --ibe_util.disable_debug;
1143 WHEN OTHERS THEN
1144 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1145 ibe_util.debug('ibe_ord_oneclick_pvt.Express_Buy_Order: OTHER EXCEPTION ' || to_char(sysdate,'DD-MON-YYYY:HH24:MI:SS'));
1146 END IF;
1147 ROLLBACK TO Express_Buy_Order_Pvt;
1148 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1149 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1150 THEN
1151 FND_MSG_PUB.Add_Exc_Msg
1152 ( G_PKG_NAME,
1153 l_api_name
1154 );
1155 END IF;
1156 FND_MSG_PUB.Count_And_Get
1157 ( p_encoded => FND_API.G_FALSE,
1158 p_count => x_msg_count,
1159 p_data => x_msg_data
1160 );
1161 --ibe_util.disable_debug;
1162 end Express_Buy_Order;
1163
1164
1165 Procedure get_express_items_settings(
1166 x_qte_header_rec IN OUT NOCOPY aso_quote_pub.Qte_Header_Rec_Type
1167 ,p_flag IN VARCHAR2
1168 ,x_payment_tbl IN OUT NOCOPY ASO_QUOTE_PUB.Payment_Tbl_Type
1169 ,x_hd_shipment_tbl IN OUT NOCOPY ASO_Quote_Pub.Shipment_Tbl_Type
1170 ,x_hd_tax_dtl_tbl IN OUT NOCOPY ASO_QUOTE_PUB.Tax_Detail_Tbl_Type) is
1171
1172 CURSOR c_quote(c_party_id NUMBER, c_acct_id NUMBER) IS
1173 SELECT quote_header_id, creation_date, currency_code
1174 FROM aso_quote_headers
1175 WHERE quote_source_code = 'IStore Oneclick'
1176 and party_id = c_party_id
1177 and cust_account_id = c_acct_id
1178 and quote_name is null
1179 and order_id is null
1180 and nvl(trunc(quote_expiration_date), trunc(sysdate)+1) >= trunc(sysdate);
1181
1182 CURSOR c_settings(c_party_id NUMBER, c_acct_id NUMBER) IS
1183 SELECT object_version_number,
1184 ord_oneclick_id,
1185 enabled_flag,
1186 freight_code,
1187 payment_id,
1188 bill_to_pty_site_id,
1189 ship_to_pty_site_id,
1190 last_update_date
1191
1192 FROM IBE_ORD_ONECLICK
1193 WHERE party_id = c_party_id
1194 and cust_account_id = c_acct_id;
1195
1196 CURSOR c_quote_date(c_qte_header_id NUMBER) IS
1197 SELECT last_update_date
1198 FROM ASO_QUOTE_HEADERS
1199 WHERE quote_header_id = c_qte_header_id;
1200
1201 CURSOR c_party_type(c_acct_id NUMBER) IS
1202 SELECT p.party_type
1203 FROM HZ_PARTIES p, HZ_CUST_ACCOUNTS a
1204 where p.party_id = a.party_id
1205 and a.cust_account_id = c_acct_id;
1206
1207 CURSOR c_bank_acct(c_ba_id NUMBER) IS
1208 SELECT bank_account_num, inactive_date, bank_account_name
1209 FROM AP_BANK_ACCOUNTS
1210 WHERE bank_account_id = c_ba_id;
1211
1212 CURSOR c_payment(c_qtehdr_id NUMBER) IS
1213 SELECT payment_id
1214 FROM ASO_PAYMENTS
1215 WHERE quote_header_id = c_qtehdr_id
1216 and quote_line_id is null;
1217
1218 CURSOR c_shipment(c_qtehdr_id NUMBER) IS
1219 SELECT shipment_id
1220 FROM ASO_SHIPMENTS
1221 WHERE quote_header_id = c_qtehdr_id
1222 and quote_line_id is null;
1223
1224 CURSOR c_party_name(c_party_id NUMBER) IS
1225 SELECT substr(party_name,1,50) from HZ_PARTIES
1226 WHERE party_type = 'PERSON' and party_id = c_party_id
1227 UNION
1228 SELECT substr(party_name,1,50) from HZ_PARTIES
1229 WHERE party_id = (SELECT subject_id from HZ_RELATIONSHIPS
1230 WHERE party_id = c_party_id
1231 and subject_type = 'PERSON'
1232 and object_type = 'ORGANIZATION');
1233
1234 l_true varchar2(10) := 'TRUE';
1235 l_hd_shipment_tbl ASO_Quote_Pub.Shipment_Tbl_Type
1236 := ASO_Quote_Pub.G_MISS_SHIPMENT_Tbl;
1237 l_hd_payment_tbl ASO_QUOTE_PUB.Payment_Tbl_Type;
1238 l_hd_tax_detail_tbl ASO_QUOTE_PUB.Tax_Detail_Tbl_Type;
1239
1240 l_qte_header_rec ASO_QUOTE_PUB.Qte_Header_Rec_Type;
1241 l_push_qte_header_rec ASO_QUOTE_PUB.Qte_Header_Rec_Type;
1242 l_tax_detail_rec ASO_QUOTE_PUB.Tax_Detail_Rec_Type;
1243 l_shipment_rec ASO_QUOTE_PUB.Shipment_Rec_Type;
1244 l_payment_rec ASO_QUOTE_PUB.Payment_Rec_Type;
1245 l_control_rec ASO_QUOTE_PUB.Control_Rec_Type;
1246
1247 l_curr_cart_id NUMBER := FND_API.g_miss_num;
1248 l_curr_cart_date DATE;
1249 l_curr_cart_currcode VARCHAR2(10);
1250 l_object_version_number NUMBER;
1251 l_ord_oneclick_id NUMBER;
1252 l_enabled_flag VARCHAR2(3);
1253 l_freight_code VARCHAR2(25);
1254 l_payment_id NUMBER;
1255 l_bill_to_pty_site_id NUMBER;
1256 l_ship_to_pty_site_id NUMBER;
1257 l_settings_date DATE;
1258 l_save_trigger VARCHAR2(3);
1259 l_party_type VARCHAR2(300);
1260 l_optional_party_id NUMBER;
1261 l_count_tax NUMBER := 0;
1262 l_payment_rec_id NUMBER := FND_API.G_MISS_NUM;
1263 l_count NUMBER := 0;
1264 l_retrieval_num NUMBER := FND_API.g_miss_num;
1265 l_credit_card_num NUMBER;
1266 l_credit_card_name VARCHAR2(80);
1267 l_credit_card_exp DATE;
1268 l_credit_card_holder_name VARCHAR2(50);
1269
1270
1271 lx_quote_header_id NUMBER;
1272 lx_last_update_date DATE;
1273 lX_return_status VARCHAR2(1);
1274 lx_msg_count NUMBER ;
1275 lx_msg_data VARCHAR2(2000);
1276 BEGIN
1277
1278 --Begin API body
1279
1280 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1281 ibe_util.debug('Starting ibe_ord_oneclick_pvt.Get_express_items_settings ');
1282 ibe_util.debug('PROCESSING FLAG : ' || p_flag);
1283 END IF;
1284
1285 l_qte_header_rec := x_qte_header_rec;
1286 if ((x_hd_tax_dtl_tbl is not null) and (x_hd_tax_dtl_tbl.count > 0)) then
1287 l_tax_detail_rec := x_hd_tax_dtl_tbl(1);
1288 end if;
1289 if ((x_payment_tbl is not null) and (x_payment_tbl.count > 0)) then
1290 l_payment_rec := x_payment_tbl(1);
1291 end if;
1292 if ((x_hd_shipment_tbl is not null) and (x_hd_shipment_tbl.count > 0)) then
1293 l_shipment_rec := x_hd_shipment_tbl(1);
1294 end if;
1295
1296 OPEN c_settings(x_qte_header_rec.party_id,
1297 x_qte_header_rec.cust_account_id);
1298 FETCH c_settings INTO
1299 l_object_version_number,
1300 l_ord_oneclick_id,
1301 l_enabled_flag,
1302 l_freight_code,
1303 l_payment_id, --mannamra: In light on credit card consolidation, this will be assignment id
1304 l_bill_to_pty_site_id,
1305 l_ship_to_pty_site_id,
1306 l_settings_date;
1307 CLOSE c_settings;
1308
1309 IF (p_flag <> 'CART') THEN
1310 -- see if there is a current cart
1311 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1312 ibe_util.debug('get_express_items_settings see if there is a current cart');
1313 ibe_util.debug('Input Cartid: '||l_qte_header_rec.quote_header_id);
1314 ibe_util.debug('Input Partyid: '||l_qte_header_rec.party_id);
1315 ibe_util.debug('Input Accountid: '||l_qte_header_rec.cust_account_id);
1316 END IF;
1317 OPEN c_quote(x_qte_header_rec.party_id,
1318 x_qte_header_rec.cust_account_id);
1319 FETCH c_quote INTO l_curr_cart_id, l_curr_cart_date, l_curr_cart_currcode;
1320 if c_quote%NOTFOUND then
1321 l_curr_cart_id := FND_API.g_miss_num;
1322 else
1323 null;
1324 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1325 ibe_util.debug('get_express_items_settings got l_curr_cart_id ' || to_char(l_curr_cart_id));
1326 ibe_util.debug('get_express_items_settings got l_curr_cart_date ' || to_char(l_curr_cart_date,'DD-MON-YYYY:HH:MI:SS'));
1327 ibe_util.debug('get_express_items_settings got l_curr_cart_currcode ' || l_curr_cart_currcode);
1328 END IF;
1329 end if;
1330 CLOSE c_quote;
1331 end if; -- p_flag <> cart
1332
1333 l_control_rec.AUTO_VERSION_FLAG := 'N';
1334
1335 -- see if we can consolidate
1336 if (l_curr_cart_id <> FND_API.g_miss_num) then
1337
1338 -- if no then move out the old cart id and set curr_cart_id to null to trigger creating a new one
1339 if ((l_curr_cart_date < l_settings_date) or (l_curr_cart_currcode <> x_qte_header_rec.currency_code)) then
1340 -- this means that the settings (which now include currency) have changed since we first
1341 -- created this express quote so we cannot safely use this one
1342 -- update the quote_name and "push it out"
1343 l_push_qte_header_rec.quote_header_id := l_curr_cart_id;
1344 l_push_qte_header_rec.quote_name := l_curr_cart_id;
1345 OPEN c_quote_date(l_curr_cart_id);
1346 FETCH c_quote_date INTO l_push_qte_header_rec.last_update_date;
1347 CLOSE c_quote_date;
1348
1349 l_control_rec.last_update_date := l_push_qte_header_rec.last_update_date;
1350 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1351 ibe_util.debug('****Calling IBE_Quote_Save_pvt.SAVE for pushing out **** ');
1352 ibe_util.debug(' l_qte_header_rec.quote_header_id : ' || l_qte_header_rec.quote_header_id);
1353 ibe_util.debug(' l_qte_header_rec.quote_name : ' || l_qte_header_rec.quote_name);
1354 ibe_util.debug(' l_qte_header_rec.last_update_date : ' || to_char(l_qte_header_rec.last_update_date,'DD-MON-YYYY:HH24:MI:SS'));
1355 END IF;
1356 IBE_Quote_Save_pvt.SAVE(
1357 p_api_version_number => 1.0,
1358 p_init_msg_list => FND_API.G_FALSE,
1359 p_commit => FND_API.g_false,
1360 p_auto_update_active_quote => FND_API.g_false,
1361 --p_combineSameItem => 'Y', -- let api default from profile
1362 p_control_rec => l_control_rec,
1363 p_qte_header_rec => l_push_qte_header_rec,
1364 p_save_type => UPDATE_EXPRESSORDER,
1365 x_quote_header_id => lx_quote_header_id,
1366 x_last_update_date => lx_last_update_date,
1367 X_Return_Status => lx_Return_Status,
1368 X_Msg_Count => lx_Msg_Count,
1369 X_Msg_Data => lx_Msg_Data);
1370 --ibe_util.enable_debug;
1371 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1372 ibe_util.debug('Back from IBE_Quote_Save_pvt.SAVE for pushing out' );
1373 END IF;
1374 if lx_return_status <> FND_API.g_ret_sts_success then
1375 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1376 ibe_util.debug('ibe_ord_oneclick_pvt.Express_Buy_Order - non success status from IBE_Quote_Save_pvt.SAVE: ' || lx_return_status);
1377 END IF;
1378 FND_MESSAGE.SET_NAME('IBE','IBE_EXPR_PLSQL_API_ERROR');
1379 FND_MESSAGE.SET_TOKEN ( '0' , 'Get_Express_Items_Settings - IBE_Quote_Save_pvt.SAVE for nonconsolidation' );
1380 FND_MESSAGE.SET_TOKEN ( '1' , lx_return_status );
1381 FND_MSG_PUB.Add;
1382 if lx_return_status = FND_API.G_RET_STS_ERROR then
1383 RAISE FND_API.G_EXC_ERROR;
1384 elsif lx_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
1385 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1386 end if;
1387 end if;
1388 -- set these to g_miss so rest of api can know we have to start with a new quote
1389 l_qte_header_rec.quote_header_id := FND_API.g_miss_num;
1390 l_curr_cart_id := FND_API.g_miss_num;
1391 l_curr_cart_date := FND_API.g_miss_date;
1392 else
1393 --settings have'nt changed so we should consolidate incoming items to the open express-checkout cart
1394 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1395 ibe_util.debug('ibe_ord_oneclick_pvt.Get_express_items_settings-Consolidating to: '||l_curr_cart_id);
1396 END IF;
1397 l_qte_header_rec.quote_header_id := l_curr_cart_id;
1398 end if; -- end pushing out of old cart
1399 end if; -- end checking if we can consolidate
1400
1401 /*At this point, l_curr_cart_id is either set to be the one we should use
1402 or g_miss_num to trigger creation of a new quote*/
1403
1404 -- see if we need to apply the Express Checkout Settings (billing, shipping, payment info)
1405 if ((p_flag = 'CART') or ((p_flag <> 'CART') and (l_curr_cart_id = FND_API.g_miss_num))) then
1406
1407 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1408 ibe_util.debug('Express_Buy_Order got l_settings_date ' || to_char(l_settings_date,'DD-MON-YYYY:HH:MI:SS'));
1409 END IF;
1410
1411 if (p_flag = 'CART') then
1412 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1413 ibe_util.debug('Express ordering a CART id : ' || x_qte_header_rec.quote_header_id);
1414 ibe_util.debug('Making sure user has Full privilege on this cart');
1415 END IF;
1416 l_curr_cart_id := x_qte_header_rec.quote_header_id;
1417 -- l_qte_header_rec.quote_name := l_curr_cart_id;
1418 -- no need to rename the cart anymore since all cart carts will have a name now
1419 end if; --if p_flag = 'CART'
1420
1421 OPEN c_party_type(x_qte_header_rec.cust_account_id);
1422 FETCH c_party_type INTO l_party_type;
1423 CLOSE c_party_type;
1424
1425 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1426 ibe_util.debug('Party Type: ' || l_party_type);
1427 END IF;
1428
1429 if (l_party_type <> 'PERSON') then
1430 l_optional_party_id := x_qte_header_rec.party_id;
1431 end if;
1432
1433 select count(*) into l_count_tax
1434 from ASO_TAX_DETAILS
1435 where quote_header_id = l_curr_cart_id
1436 and quote_line_id is null;
1437
1438 IF (l_count_tax = 0) then
1439
1440 l_tax_detail_rec.operation_code := 'CREATE';
1441 l_tax_detail_rec.quote_header_id := l_curr_cart_id; --will be g_miss for expr chkout of items
1442 l_tax_detail_rec.tax_exempt_flag := 'S';
1443 x_hd_tax_dtl_tbl(1) := l_tax_detail_rec;
1444
1445 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1446 ibe_util.debug('Get_express_items_settings - *** creating header level tax record***');
1447 ibe_util.debug('l_tax_detail_rec.operation_code ' || l_tax_detail_rec.operation_code);
1448 ibe_util.debug('l_tax_detail_rec.quote_header_id ' || l_tax_detail_rec.quote_header_id);
1449 ibe_util.debug('l_tax_detail_rec.tax_exempt_flag ' || l_tax_detail_rec.tax_exempt_flag);
1450 END IF;
1451 END IF; --l_count_tax=0
1452
1453 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1454 ibe_util.debug('Get_express_items_settings - ***setting quote header info***');
1455 END IF;
1456
1457 l_qte_header_rec.invoice_to_party_site_id := l_bill_to_pty_site_id;
1458 l_qte_header_rec.invoice_to_party_id := l_optional_party_id;
1459 l_qte_header_rec.quote_source_code := 'IStore Oneclick';
1460 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1461 ibe_util.debug('Get_express_items_settings:l_qte_header_rec.party_id ' || l_qte_header_rec.party_id);
1462 ibe_util.debug('Get_express_items_settings:l_qte_header_rec.cust_account_id ' || l_qte_header_rec.cust_account_id);
1463 ibe_util.debug('Get_express_items_settings:l_qte_header_rec.invoice_to_party_site_id' || l_qte_header_rec.invoice_to_party_site_id);
1464 ibe_util.debug('Get_express_items_settings:l_qte_header_rec.invoice_to_party_id ' || l_qte_header_rec.invoice_to_party_id);
1465 ibe_util.debug('Get_express_items_settings:l_qte_header_rec.quote_source_code ' || l_qte_header_rec.quote_source_code);
1466 END IF;
1467
1468 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1469 ibe_util.debug('Express_Buy_Order - ***setting to create or update payment rec***');
1470 END IF;
1471 /*mannamra: In light of credit card consolidation, we will not be storing credit card number in one_click_all table
1472 we will also just be passing assignment id to ASO whi will in turn create a transaction and
1473 store the transaction extension id for this quote in aso_payments */
1474
1475 /*OPEN c_bank_acct(l_payment_id);
1476 FETCH c_bank_acct INTO l_credit_card_num, l_credit_card_exp, l_credit_card_holder_name;
1477 if c_bank_acct%NOTFOUND then
1478 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1479 ibe_util.debug('No credit card record found.');
1480 END IF;
1481 end if;
1482 CLOSE c_bank_acct;*/
1483
1484 --l_credit_card_name := IBE_ORD_ONECLICK_PVT.Get_credit_card_Type(l_credit_card_num);
1485 /* If Credit Card name is ERROR raise appropriate exception */
1486 /*If l_credit_card_name = 'ERROR' Then
1487 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1488 ibe_util.debug('Invalid Credit Card');
1489 END IF;
1490 End If;*/
1491
1492 /* per Bug #3020526, using info from AP_BANK_ACCOUNTS table instead
1493 OPEN c_party_name(x_qte_header_rec.party_id);
1494 FETCH c_party_name INTO l_credit_card_holder_name;
1495 if c_party_name%NOTFOUND then
1496 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1497 ibe_util.debug('No name to use for credit card holder name.');
1498 END IF;
1499 end if;
1500 CLOSE c_party_name;
1501 */
1502
1503 OPEN c_payment(l_curr_cart_id);
1504 FETCH c_payment INTO l_payment_rec.payment_id;
1505 if c_payment%NOTFOUND then
1506 l_payment_rec.operation_code := 'CREATE';
1507 else
1508 l_payment_rec.operation_code := 'UPDATE';
1509 end if;
1510 CLOSE c_payment;
1511
1512 /*l_payment_rec.payment_term_id := fnd_profile.value('IBE_DEFAULT_PAYMENT_TERM_ID');
1513 l_payment_rec.payment_type_code := 'CREDIT_CARD';
1514 l_payment_rec.payment_ref_number := l_credit_card_num;
1515 l_payment_rec.credit_card_expiration_date := l_credit_card_exp;
1516 l_payment_rec.credit_card_code := l_credit_card_name;
1517 l_payment_rec.credit_card_holder_name := l_credit_card_holder_name;*/
1518 l_payment_rec.payment_type_code := 'CREDIT_CARD';
1519 l_payment_rec.instr_assignment_id := l_payment_id;
1520 x_payment_tbl(1) := l_payment_rec;
1521 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1522 ibe_util.debug('l_payment_rec.operation_code ' || l_payment_rec.operation_code);
1523 ibe_util.debug('l_payment_rec.payment_term_id ' || l_payment_rec.payment_term_id);
1524 ibe_util.debug('l_payment_rec.payment_type_code '|| l_payment_rec.payment_type_code);
1525 ibe_util.debug('l_payment_rec.payment_ref_number <not logged>');
1526 ibe_util.debug('l_payment_rec.instr_asssignment_id ' || l_payment_rec.instr_assignment_id);
1527 END IF;
1528
1529 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1530 ibe_util.debug('Get_express_items_settings - ***setting to create or update shipment rec***');
1531 END IF;
1532 l_shipment_rec.operation_code := 'UPDATE';
1533 OPEN c_shipment(l_curr_cart_id);
1534 FETCH c_shipment INTO l_shipment_rec.shipment_id;
1535 if c_shipment%NOTFOUND then
1536 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1537 ibe_util.debug('no shipment rec at header, will CREATE one');
1538 END IF;
1539 l_shipment_rec.operation_code := 'CREATE';
1540 end if;
1541 CLOSE c_shipment;
1542
1543 l_shipment_rec.ship_method_code := l_freight_code;
1544 l_shipment_rec.ship_to_party_site_id := l_ship_to_pty_site_id;
1545 l_shipment_rec.ship_to_party_id := l_optional_party_id;
1546 x_hd_shipment_tbl(1) := l_shipment_rec;
1547 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1548 ibe_util.debug('l_shipment_rec.shipment_id ' || l_shipment_rec.shipment_id);
1549 ibe_util.debug('l_shipment_rec.operation_code ' || l_shipment_rec.operation_code);
1550 ibe_util.debug('l_shipment_rec.ship_method_code ' || l_shipment_rec.ship_method_code);
1551 ibe_util.debug('l_shipment_rec.ship_to_party_site_id ' || l_shipment_rec.ship_to_party_site_id);
1552 ibe_util.debug('l_shipment_rec.ship_to_party_id ' || l_shipment_rec.ship_to_party_id);
1553 END IF;
1554
1555 END IF; --IF ((p_flag = 'CART') or ((p_flag <> 'CART') and (l_curr_cart_id = FND_API.g_miss_num)))
1556
1557 x_qte_header_rec := l_qte_header_rec;
1558
1559 END;
1560
1561
1562 /* Local Function to get credit card type given the number
1563
1564 -- Input Parameter(s)
1565 - p_credit_card_number NUMBER
1566 -- Returns
1567 Credit_card_type VARCHAR2
1568 -- hekkiral - 21-DEC-2000
1569 */
1570 function Get_Credit_Card_Type(
1571 p_Credit_Card_Number NUMBER
1572 ) RETURN VARCHAR2
1573 AS
1574 l_credit_Card_number Varchar2(30);
1575 l_credit_card_length Number;
1576 Begin
1577
1578 l_credit_card_number := to_char(p_credit_card_number);
1579 l_credit_card_length := length(l_credit_card_number);
1580
1581 If (l_credit_Card_length = 16 and substr(l_credit_card_number,1,2) in ('51','52','53','54','55') ) Then
1582 Return ('MC');
1583 Elsif ((l_credit_Card_length = 13 or l_credit_card_length = 16) and substr(l_credit_card_number,1,1) = '4') Then
1584 Return ('VISA');
1585 Elsif (l_credit_Card_length = 15 and substr(l_credit_card_number,1,2) in ('34','37')) Then
1586 Return ('AMEX');
1587 Elsif (l_credit_card_length = 14 and substr(l_credit_card_number,1,3) in ('300','301','302','303','305','36','38')) Then
1588 Return('DINERS');
1589 Elsif (l_credit_card_length = 16 and substr(l_credit_card_number,1,4) = '6011') Then
1590 Return ('DISCOVER');
1591 Elsif ((l_credit_card_length = 15 and substr(l_credit_card_number,1,4) in ('2014','2149')) or
1592 ((l_credit_card_length = 15 or l_credit_card_length = 16) and (substr(l_credit_card_number,1,1) = '3' or substr(l_credit_card_number,1,4) in ('2131','1800')))) Then
1593 Return ('OTHERS');
1594 Else
1595 Return('ERROR');
1596 End If;
1597
1598 End Get_Credit_Card_Type;
1599
1600 /*-----------------------------------------------------------------------------
1601
1602 Update_Settings
1603 - Updates the Express checkout settings
1604 - Called when a credit card is deleted, to check if the deleted
1605 credit card is the one selected for Express Checkout. If so,
1606 disables the Express Checkout
1607
1608 ------------------------------------------------------------------------------
1609 */
1610 Procedure Update_Settings(
1611 p_api_version IN NUMBER,
1612 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1613 p_commit IN VARCHAR2 := FND_API.g_false,
1614 x_return_status OUT NOCOPY VARCHAR2,
1615 x_msg_count OUT NOCOPY NUMBER,
1616 x_msg_data OUT NOCOPY VARCHAR2,
1617 p_party_id IN NUMBER := NULL,
1618 p_acct_id IN NUMBER := NULL,
1619 p_assignment_id IN NUMBER := NULL)
1620 IS
1621
1622 l_api_name CONSTANT VARCHAR2(30) := 'Update_Settings';
1623 l_api_version CONSTANT NUMBER := 1.0;
1624 l_object_version_number NUMBER := FND_API.G_MISS_NUM;
1625 l_oneclick_id NUMBER := FND_API.G_MISS_NUM;
1626 l_payment_id NUMBER := FND_API.G_MISS_NUM;
1627 l_bill_ptysite_id NUMBER := FND_API.G_MISS_NUM;
1628 l_ship_ptysite_id NUMBER := FND_API.G_MISS_NUM;
1629 l_enabled_flag VARCHAR2(1) := 'N';
1630 l_freight_code VARCHAR2(30) := FND_API.G_MISS_CHAR;
1631
1632 Cursor c_get_oneclick_settings(c_party_id IN NUMBER,
1633 c_acct_id IN NUMBER)
1634 IS
1635
1636 select ord_oneclick_id, object_version_number, enabled_flag, freight_code,
1637 payment_id, bill_to_pty_site_id, ship_to_pty_site_id
1638 from ibe_ord_oneclick
1639 where party_id = c_party_id and cust_account_id = c_acct_id;
1640
1641 Begin
1642
1643 SAVEPOINT Update_Settings;
1644
1645 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1646 ibe_util.debug('------- Input to ibe_ord_oneclick_pvt.Update_Settings: -----------------');
1647 ibe_util.debug('p_party_id :'||p_party_id);
1648 ibe_util.debug('p_acct_id :'||p_acct_id);
1649 ibe_util.debug('p_assignment_id :'||p_assignment_id);
1650 END IF;
1651
1652 OPEN c_get_oneclick_settings(p_party_id, p_acct_id);
1653 FETCH c_get_oneclick_settings
1654 INTO
1655 l_oneclick_id,
1656 l_object_version_number,
1657 l_enabled_flag,
1658 l_freight_code,
1659 l_payment_id,
1660 l_bill_ptysite_id,
1661 l_ship_ptysite_id;
1662
1663 IF c_get_oneclick_settings%NOTFOUND THEN
1664 l_oneclick_id := FND_API.g_miss_num;
1665 END IF;
1666 CLOSE c_get_oneclick_settings;
1667
1668 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1669 ibe_util.debug('------- Retrieved From TABLES: Update_Settings-----------------');
1670 ibe_util.debug('l_object_version_number :'||l_object_version_number);
1671 ibe_util.debug('l_oneclick_id :'||l_oneclick_id);
1672 ibe_util.debug('l_enabled_flag :'||l_enabled_flag);
1673 ibe_util.debug('l_freight_code :'||l_freight_code);
1674 ibe_util.debug('l_payment_id :'||l_payment_id);
1675 ibe_util.debug('l_bill_ptysite_id :'||l_bill_ptysite_id);
1676 ibe_util.debug('l_ship_ptysite_id :'||l_ship_ptysite_id);
1677 ibe_util.debug('Checking p_assignment_id not null :'||p_assignment_id);
1678 END IF;
1679
1680 IF p_assignment_id is not NULL and l_oneclick_id <> FND_API.g_miss_num and l_enabled_flag = 'Y' and l_payment_id = p_assignment_id THEN
1681 l_enabled_flag := 'N';
1682 l_payment_id := '';
1683
1684 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1685 ibe_util.debug('Update_Settings : Deleted CC is the one selected for Express checkout');
1686 ibe_util.debug('Update_Settings : Disabling the Express checkout - Calling ibe_ord_oneclick_pvt.save_settings');
1687 END IF;
1688
1689 ibe_ord_oneclick_pvt.save_settings(
1690 p_api_version => l_api_version,
1691 p_init_msg_list => FND_API.G_FALSE,
1692 p_commit => p_commit,
1693 x_return_status => x_return_status,
1694 x_msg_count => x_msg_count,
1695 x_msg_data => x_msg_data,
1696 p_party_id => p_party_id,
1697 p_acct_id => p_acct_id,
1698 p_object_version_number => l_object_version_number,
1699 p_enabled_flag => l_enabled_flag,
1700 p_freight_code => l_freight_code,
1701 p_payment_id => l_payment_id,
1702 p_bill_ptysite_id => l_bill_ptysite_id,
1703 p_ship_ptysite_id => l_ship_ptysite_id
1704 );
1705
1706 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1707 ibe_util.debug('Update_Settings : After Calling ibe_ord_oneclick_pvt.save_settings');
1708 ibe_util.debug('Update_Settings : x_return_status - '||x_return_status);
1709 END IF;
1710
1711 IF x_return_status <> FND_API.g_ret_sts_success THEN
1712 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1713 ibe_util.debug('ibe_ord_oneclick_pvt.Update_Settings - call to ibe_ord_oneclick_pvt.save_settings failed' || x_return_status);
1714 END IF;
1715 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1716 RAISE FND_API.G_EXC_ERROR;
1717 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1718 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1719 END IF;
1720 END IF;
1721
1722 IF FND_API.To_Boolean( p_commit ) THEN
1723 COMMIT WORK;
1724 END IF;
1725
1726 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1727 ibe_util.debug('Update_Settings : Success - Express Checkout disabled');
1728 ibe_util.debug('Update_Settings : End');
1729 END IF;
1730 ELSE
1731 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1732 ibe_util.debug('Update_Settings : Express Checkout disabled - No need for update');
1733 ibe_util.debug('Update_Settings : End');
1734 END IF;
1735
1736 END IF;
1737
1738 FND_MSG_PUB.Count_And_Get
1739 ( p_encoded => FND_API.G_FALSE,
1740 p_count => x_msg_count,
1741 p_data => x_msg_data
1742 );
1743
1744 EXCEPTION
1745 WHEN FND_API.G_EXC_ERROR THEN
1746 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1747 ibe_util.debug('ibe_ord_oneclick_pvt.Update_Settings: EXPECTED ERROR EXCEPTION ' || to_char(sysdate,'DD-MON-YYYY:HH24:MI:SS'));
1748 END IF;
1749 ROLLBACK TO Update_Settings;
1750 x_return_status := FND_API.G_RET_STS_ERROR ;
1751 FND_MSG_PUB.Count_And_Get
1752 ( p_encoded => FND_API.G_FALSE,
1753 p_count => x_msg_count,
1754 p_data => x_msg_data
1755 );
1756
1757 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1758 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1759 ibe_util.debug('ibe_ord_oneclick_pvt.Update_Settings: UNEXPECTED ERROR EXCEPTION ' || to_char(sysdate,'DD-MON-YYYY:HH24:MI:SS'));
1760 END IF;
1761 ROLLBACK TO Update_Settings;
1762 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1763 FND_MSG_PUB.Count_And_Get
1764 ( p_encoded => FND_API.G_FALSE,
1765 p_count => x_msg_count,
1766 p_data => x_msg_data
1767 );
1768
1769 WHEN OTHERS THEN
1770 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1771 ibe_util.debug('ibe_ord_oneclick_pvt.Update_Settings: OTHER EXCEPTION ' || to_char(sysdate,'DD-MON-YYYY:HH24:MI:SS'));
1772 END IF;
1773 ROLLBACK TO Update_Settings;
1774 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1775 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1776 FND_MSG_PUB.Add_Exc_Msg
1777 ( G_PKG_NAME,
1778 l_api_name
1779 );
1780 END IF;
1781 FND_MSG_PUB.Count_And_Get
1782 ( p_encoded => FND_API.G_FALSE,
1783 p_count => x_msg_count,
1784 p_data => x_msg_data
1785 );
1786
1787 End Update_Settings;
1788 end ibe_ord_oneclick_pvt;