[Home] [Help]
PACKAGE BODY: APPS.IBE_ORD_ONECLICK_PVT
Source
1 PACKAGE BODY ibe_ord_oneclick_pvt AS
2 /* $Header: IBEVO1CB.pls 120.12.12020000.2 2012/08/14 06:35:35 amaheshw 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
107 ibe_util.debug('------- INPUT: -----------------');
104 x_return_status := FND_API.g_ret_sts_success;
105
106 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
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
229 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
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';
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 ;
349 ( G_PKG_NAME,
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
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
482 payment_id = p_PAYMENT_ID,
479 object_version_number = p_OBJECT_VERSION_NUMBER + 1,
480 enabled_flag = p_ENABLED_FLAG,
481 freight_code = p_FREIGHT_CODE,
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
609 -- for express checkout of a list of items (usually from catalog)
606 p_list_line_ids IN JTF_NUMBER_TABLE,
607 p_list_line_ovns IN JTF_NUMBER_TABLE,
608
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 p_price_mode VARCHAR2 := 'ENTIRE_QUOTE'
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
738 ,p_last_update_date => p_last_update_date
735 ,p_quote_retrieval_number => p_retrieval_num
736 ,p_validate_user => FND_API.G_TRUE
737 ,p_privilege_type_code => 'A'
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
851 l_control_rec.PRICE_MODE:= p_price_mode;
855 end if;
852 if (FND_Profile.Value('IBE_PRICE_CHANGED_LINES') = 'Y' and p_price_mode = 'CHANGE_LINE') then
853 l_qte_header_rec.PRICING_STATUS_INDICATOR := 'I';
854 l_qte_header_rec.TAX_STATUS_INDICATOR := 'I';
856
857 l_qte_header_rec.currency_code := p_currency_code;
858 l_qte_header_rec.price_list_id := p_price_list_id;
859 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
860 ibe_util.debug('l_control_rec.pricing_request_type ' || l_control_rec.pricing_request_type);
861 ibe_util.debug('l_control_rec.header_pricing_event ' || l_control_rec.header_pricing_event);
862 ibe_util.debug('l_control_rec.line_pricing_event ' || l_control_rec.line_pricing_event);
863 ibe_util.debug('l_control_rec.CALCULATE_TAX_FLAG ' || l_control_rec.CALCULATE_TAX_FLAG);
864 ibe_util.debug('l_control_rec.CALCULATE_FREIGHT_CHARGE_FLAG ' || l_control_rec.CALCULATE_FREIGHT_CHARGE_FLAG);
865 ibe_util.debug('l_qte_header_rec.currency_code' || l_qte_header_rec.currency_code);
866 ibe_util.debug('l_qte_header_rec.price_list_id ' || l_qte_header_rec.price_list_id);
867 END IF;
868
869 IF (l_save_trigger = 'Y') then
870 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
871 ibe_util.debug('****Calling IBE_Quote_Save_pvt.SAVE**** ' || to_char(sysdate,'DD-MON-YYYY:HH24:MI:SS'));
872 ibe_util.debug(' l_qte_header_rec.quote_header_id : ' || l_qte_header_rec.quote_header_id);
873 ibe_util.debug(' l_qte_header_rec.quote_name : ' || l_qte_header_rec.quote_name);
874 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'));
875 END IF;
876
877 IF (p_retrieval_num <> FND_API.g_miss_num) then
878 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
879 ibe_util.debug('passing sharee party and acct id');
880 END IF;
881 l_qte_header_rec.party_id := FND_API.G_MISS_NUM;
882 l_qte_header_rec.cust_account_id := FND_API.G_MISS_NUM;
883 l_sharee_party_id := p_party_id;
884 l_sharee_acct_id := p_acct_id;
885 END IF;
886
887 IBE_Quote_Save_pvt.SAVE(
888 p_api_version_number => p_api_version,
889 p_init_msg_list => FND_API.G_FALSE,
890 p_commit => FND_API.g_false,
891 p_auto_update_active_quote => FND_API.g_false,
892 -- p_combineSameItem => 'Y', -- let api default from profile
893 p_sharee_Number => p_retrieval_num,
894 p_sharee_party_id => l_sharee_party_id,
895 p_sharee_cust_account_id => l_sharee_acct_id,
896 p_minisite_id => p_minisite_id,
897 p_control_rec => l_control_rec,
898 p_qte_header_rec => l_qte_header_rec,
899 P_Qte_Line_Tbl => l_qte_line_tbl,
900 P_hd_Payment_Tbl => l_hd_Payment_Tbl,
901 P_hd_Shipment_Tbl => l_hd_Shipment_Tbl,
902 P_hd_Tax_Detail_Tbl => l_hd_Tax_Detail_Tbl,
903
904 x_quote_header_id => lx_quote_header_id,
905 x_last_update_date => lx_last_update_date,
906 X_Return_Status => x_Return_Status,
907 X_Msg_Count => x_Msg_Count,
908 X_Msg_Data => x_Msg_Data);
909
910 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
911 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));
912 END IF;
913 IF x_return_status <> FND_API.g_ret_sts_success THEN
914 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
915 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');
916 END IF;
917 FND_MESSAGE.SET_TOKEN ( '0' , 'Express_Buy_Order - IBE_Quote_Save_pvt.SAVE' );
918 FND_MESSAGE.SET_TOKEN ( '1' , x_return_status );
919 FND_MSG_PUB.Add;
920 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
921 RAISE FND_API.G_EXC_ERROR;
922 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
923 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
924 END IF;
925 ELSE
926 IF(l_attach_contract = FND_API.G_TRUE) THEN
927 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)
928 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
929 ibe_util.debug('ibe_ord_oneclick_pvt.Express_Buy_Order - : No contract currently attached !');
930 END IF;
931 IF (FND_Profile.Value('OKC_ENABLE_SALES_CONTRACTS') = 'Y' ) THEN --Only if contracts is enabled
932 --instantiate a contract and associate to the quote
933 /*mannamra: changes for MOAC: Bug 4682364 */
934 --l_contract_template_id := FND_PROFILE.VALUE('ASO_DEFAULT_CONTRACT_TEMPLATE'); old style
935 l_contract_template_id := to_number(ASO_UTILITY_PVT.GET_OU_ATTRIBUTE_VALUE(ASO_UTILITY_PVT.G_DEFAULT_CONTRACT_TEMPLATE)); --New style
936 /*mannamra: end of changes for MOAC*/
937
938 IF (l_contract_template_id is not null) THEN
939 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
940 IBE_UTIL.debug('l_contract_template_id = '||l_contract_template_id);
941 IBE_UTIL.debug('Before calling OKC_TERMS_COPY_GRP.copy_terms_api, quoteheaderId = '||lx_quote_header_id);
942 END IF;
943 OKC_TERMS_COPY_GRP.copy_terms(
944 p_api_version =>1.0
945 ,p_template_id => l_contract_template_id
946 ,p_target_doc_type => 'QUOTE'
947 ,p_target_doc_id => lx_quote_header_id
948 ,p_article_effective_date => null
952 ,x_msg_data => x_msg_data);
949 ,p_validation_string => null
950 ,x_return_status => x_return_status
951 ,x_msg_count => x_msg_count
953
954 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
955 IBE_UTIL.debug('After copy_terms api, return status = '||x_return_status);
956 END IF;
957 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
958 RAISE FND_API.G_EXC_ERROR;
959 END IF;
960 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
961 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
962 END IF;
963 END IF; --If contract template id is not null
964 END IF; --IF (FND_Profile.Value('OKC_ENABLE_SALES_CONTRACTS')
965 ELSE
966 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
967 ibe_util.debug('ibe_ord_oneclick_pvt.Express_Buy_Order - : A contract is already instanciated for this quote');
968 END IF;
969 END IF; -- Whether there is a contract currently instanciated for this quote
970 END IF; --If l_attach_contract is true
971 END IF; -- If l_return_status is success
972 IF (p_flag = 'CART') THEN
973 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
974 ibe_util.debug('Express_buy_order: Calling stop_sharing for quote_header_id :'||l_qte_header_rec.quote_header_id);
975 END IF;
976 IBE_QUOTE_SAVESHARE_V2_PVT.stop_sharing (
977 p_quote_header_id => p_cart_id ,
978 p_delete_context => 'IBE_SC_CART_ORDERED',
979 P_minisite_id => p_minisite_id ,
980 p_api_version => p_api_version ,
981 p_init_msg_list => fnd_api.g_false ,
982 p_commit => fnd_api.g_false ,
983 x_return_status => x_return_status ,
984 x_msg_count => x_msg_count ,
985 x_msg_data => x_msg_data );
986
987
988 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
989 RAISE FND_API.G_EXC_ERROR;
990 END IF;
991
992 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
993 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
994 END IF;
995 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
996 ibe_util.debug('Express_buy_order: Done Calling stop_sharing');
997 END IF;
998 END IF;
999
1000 l_curr_cart_id := lx_quote_header_id;
1001 l_qte_header_rec.quote_header_id := l_curr_cart_id;
1002 l_qte_header_rec.last_update_date := lx_last_update_date;
1003 l_control_rec.last_update_date := lx_last_update_date;
1004 end if; -- end if needed to call quote.save
1005
1006 -- get ready for the next update of the quote
1007
1008 /* if (p_flag = 'LISTS') then
1009 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1010 ibe_util.debug('Calling IBE_Shop_List_PVT.Save_Quote_From_Lists ' || to_char(sysdate,'DD-MON-YYYY:HH24:MI:SS'));
1011 ibe_util.debug('p_mode ' || 'MERGE');
1012 END IF;
1013 l_count := p_list_ids.count;
1014 FOR i IN 1..l_count LOOP
1015 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1016 ibe_util.debug('p_shpListIds' || i || ' ' || p_list_ids(i));
1017 END IF;
1018 END LOOP;
1019
1020 IBE_Shop_List_PVT.Save_Quote_From_Lists(
1021 P_Api_Version => 1.0
1022 ,P_Init_Msg_List => FND_API.G_FALSE
1023 ,P_Commit => FND_API.G_FALSE
1024 ,p_mode => 'MERGE'
1025 --,p_combine_same_item => null -- let default from profile
1026 ,p_sl_header_ids => p_list_ids
1027 ,p_sl_header_ovns => p_list_ovns
1028 ,p_control_rec => l_control_rec
1029 ,p_q_header_rec => l_qte_header_rec
1030 ,p_url => null
1031 ,p_comments => null
1032 ,X_Return_Status => x_return_status
1033 ,X_Msg_Count => x_msg_count
1034 ,X_Msg_Data => x_msg_data
1035 ,x_q_header_id => lx_quote_header_id );
1036
1037 --ibe_util.enable_debug;
1038 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1039 ibe_util.debug('Back from IBE_Shop_List_PVT.Save_Quote_From_Lists ' || to_char(sysdate,'DD-MON-YYYY:HH24:MI:SS'));
1040 ibe_util.debug('X_cartId ' || lx_quote_header_id);
1041 END IF;
1042
1043 IF x_return_status <> FND_API.g_ret_sts_success THEN
1044 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1045 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');
1046 END IF;
1047 FND_MESSAGE.SET_TOKEN ( '0' , 'Express_Buy_Order - IBE_Shop_List_PVT.Save_Quote_From_Lists' );
1048 FND_MESSAGE.SET_TOKEN ( '1' , x_return_status );
1049 FND_MSG_PUB.Add;
1050 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1051 RAISE FND_API.G_EXC_ERROR;
1052 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1053 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1054 END IF;
1055 END IF;
1056
1057 END IF;
1058 */
1059 if (p_flag = 'LIST_LINES') then
1060 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1061 ibe_util.debug('Calling IBE_Shop_List_PVT.Save_Quote_From_List_Items ' || to_char(sysdate,'DD-MON-YYYY:HH24:MI:SS'));
1062 ibe_util.debug('p_mode ' || 'MERGE');
1063 END IF;
1064 l_count := p_list_line_ids.count;
1065 FOR i IN 1..l_count LOOP
1066 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1067 ibe_util.debug('p_shpListLineIds' || i || ' ' || p_list_line_ids(i));
1068 END IF;
1069 end loop;
1073 ,P_Commit => FND_API.G_FALSE
1070 IBE_Shop_List_PVT.Save_Quote_From_List_Items(
1071 P_Api_Version => 1.0
1072 ,P_Init_Msg_List => FND_API.G_FALSE
1074 ,p_mode => 'MERGE'
1075 -- ,p_combine_same_item => null -- let default from profile
1076 ,p_sl_line_ids => p_list_line_ids
1077 ,p_sl_line_ovns => p_list_line_ovns
1078 ,p_control_rec => l_control_rec
1079 ,p_q_header_rec => l_qte_header_rec
1080 ,p_url => null
1081 ,p_comments => null
1082 ,X_Return_Status => x_return_status
1083 ,X_Msg_Count => x_msg_count
1084 ,X_Msg_Data => x_msg_data
1085 ,x_q_header_id => lx_quote_header_id );
1086
1087 --ibe_util.enable_debug;
1088 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1089 ibe_util.debug('Back from IBE_Shop_List_PVT.Save_Quote_From_List_Items ' || to_char(sysdate,'DD-MON-YYYY:HH24:MI:SS'));
1090 ibe_util.debug('X_cartId ' || lx_quote_header_id);
1091 END IF;
1092
1093 if x_return_status <> FND_API.g_ret_sts_success then
1094 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1095 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');
1096 END IF;
1097 FND_MESSAGE.SET_NAME('IBE','IBE_EXPR_PLSQL_API_ERROR');
1098 FND_MESSAGE.SET_TOKEN ( '0' , 'Express_Buy_Order - IBE_Shop_List_PVT.Save_Quote_From_List_Items' );
1099 FND_MESSAGE.SET_TOKEN ( '1' , x_return_status );
1100 FND_MSG_PUB.Add;
1101 if x_return_status = FND_API.G_RET_STS_ERROR then
1102 RAISE FND_API.G_EXC_ERROR;
1103 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
1104 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1105 end if;
1106 end if;
1107 end if;
1108
1109 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1110 ibe_util.debug('Express_Buy_Order - BOTTOM LINE: express cart used: ' || l_curr_cart_id);
1111 END IF;
1112 -- let the caller know which quote we finally updated
1113 x_new_cart_id := l_curr_cart_id;
1114
1115 IF FND_API.To_Boolean( p_commit ) THEN
1116 COMMIT WORK;
1117 END IF;
1118 FND_MSG_PUB.Count_And_Get
1119 ( p_encoded => FND_API.G_FALSE,
1120 p_count => x_msg_count ,
1121 p_data => x_msg_data );
1122
1123 --ibe_util.disable_debug;
1124 EXCEPTION
1125 WHEN FND_API.G_EXC_ERROR THEN
1126 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1127 ibe_util.debug('ibe_ord_oneclick_pvt.Express_Buy_Order: EXPECTED ERROR EXCEPTION ' || to_char(sysdate,'DD-MON-YYYY:HH24:MI:SS'));
1128 END IF;
1129 ROLLBACK TO Express_Buy_Order_Pvt;
1130 x_return_status := FND_API.G_RET_STS_ERROR;
1131 x_last_update_date := IBE_Quote_Misc_pvt.getQuoteLastUpdateDate(p_cart_id);
1132 FND_MSG_PUB.Count_And_Get
1133 ( p_encoded => FND_API.G_FALSE,
1134 p_count => x_msg_count,
1135 p_data => x_msg_data
1136 );
1137 --ibe_util.disable_debug;
1138 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1139 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1140 ibe_util.debug('ibe_ord_oneclick_pvt.Express_Buy_Order: UNEXPECTED ERROR EXCEPTION ' || to_char(sysdate,'DD-MON-YYYY:HH24:MI:SS'));
1141 END IF;
1142 ROLLBACK TO Express_Buy_Order_Pvt;
1143 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1144 FND_MSG_PUB.Count_And_Get
1145 ( p_encoded => FND_API.G_FALSE,
1146 p_count => x_msg_count,
1147 p_data => x_msg_data
1148 );
1149 --ibe_util.disable_debug;
1150 WHEN OTHERS THEN
1151 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1152 ibe_util.debug('ibe_ord_oneclick_pvt.Express_Buy_Order: OTHER EXCEPTION ' || to_char(sysdate,'DD-MON-YYYY:HH24:MI:SS'));
1153 END IF;
1154 ROLLBACK TO Express_Buy_Order_Pvt;
1155 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1156 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1157 THEN
1158 FND_MSG_PUB.Add_Exc_Msg
1159 ( G_PKG_NAME,
1160 l_api_name
1161 );
1162 END IF;
1163 FND_MSG_PUB.Count_And_Get
1164 ( p_encoded => FND_API.G_FALSE,
1165 p_count => x_msg_count,
1166 p_data => x_msg_data
1167 );
1168 --ibe_util.disable_debug;
1169 end Express_Buy_Order;
1170
1171
1172 Procedure get_express_items_settings(
1173 x_qte_header_rec IN OUT NOCOPY aso_quote_pub.Qte_Header_Rec_Type
1174 ,p_flag IN VARCHAR2
1175 ,x_payment_tbl IN OUT NOCOPY ASO_QUOTE_PUB.Payment_Tbl_Type
1176 ,x_hd_shipment_tbl IN OUT NOCOPY ASO_Quote_Pub.Shipment_Tbl_Type
1177 ,x_hd_tax_dtl_tbl IN OUT NOCOPY ASO_QUOTE_PUB.Tax_Detail_Tbl_Type) is
1178
1179 CURSOR c_quote(c_party_id NUMBER, c_acct_id NUMBER) IS
1180 SELECT quote_header_id, creation_date, currency_code
1181 FROM aso_quote_headers
1182 WHERE quote_source_code = 'IStore Oneclick'
1183 and party_id = c_party_id
1184 and cust_account_id = c_acct_id
1185 and quote_name is null
1186 and order_id is null
1187 and nvl(trunc(quote_expiration_date), trunc(sysdate)+1) >= trunc(sysdate);
1188
1189 CURSOR c_settings(c_party_id NUMBER, c_acct_id NUMBER) IS
1190 SELECT object_version_number,
1191 ord_oneclick_id,
1192 enabled_flag,
1193 freight_code,
1194 payment_id,
1198
1195 bill_to_pty_site_id,
1196 ship_to_pty_site_id,
1197 last_update_date
1199 FROM IBE_ORD_ONECLICK
1200 WHERE party_id = c_party_id
1201 and cust_account_id = c_acct_id;
1202
1203 CURSOR c_quote_date(c_qte_header_id NUMBER) IS
1204 SELECT last_update_date
1205 FROM ASO_QUOTE_HEADERS
1206 WHERE quote_header_id = c_qte_header_id;
1207
1208 CURSOR c_party_type(c_acct_id NUMBER) IS
1209 SELECT p.party_type
1210 FROM HZ_PARTIES p, HZ_CUST_ACCOUNTS a
1211 where p.party_id = a.party_id
1212 and a.cust_account_id = c_acct_id;
1213
1214 CURSOR c_bank_acct(c_ba_id NUMBER) IS
1215 SELECT bank_account_num, inactive_date, bank_account_name
1216 FROM AP_BANK_ACCOUNTS
1217 WHERE bank_account_id = c_ba_id;
1218
1219 CURSOR c_payment(c_qtehdr_id NUMBER) IS
1220 SELECT payment_id
1221 FROM ASO_PAYMENTS
1222 WHERE quote_header_id = c_qtehdr_id
1223 and quote_line_id is null;
1224
1225 CURSOR c_shipment(c_qtehdr_id NUMBER) IS
1226 SELECT shipment_id
1227 FROM ASO_SHIPMENTS
1228 WHERE quote_header_id = c_qtehdr_id
1229 and quote_line_id is null;
1230
1231 CURSOR c_party_name(c_party_id NUMBER) IS
1232 SELECT substr(party_name,1,50) from HZ_PARTIES
1233 WHERE party_type = 'PERSON' and party_id = c_party_id
1234 UNION
1235 SELECT substr(party_name,1,50) from HZ_PARTIES
1236 WHERE party_id = (SELECT subject_id from HZ_RELATIONSHIPS
1237 WHERE party_id = c_party_id
1238 and subject_type = 'PERSON'
1239 and object_type = 'ORGANIZATION');
1240
1241 l_true varchar2(10) := 'TRUE';
1242 l_hd_shipment_tbl ASO_Quote_Pub.Shipment_Tbl_Type
1243 := ASO_Quote_Pub.G_MISS_SHIPMENT_Tbl;
1244 l_hd_payment_tbl ASO_QUOTE_PUB.Payment_Tbl_Type;
1245 l_hd_tax_detail_tbl ASO_QUOTE_PUB.Tax_Detail_Tbl_Type;
1246
1247 l_qte_header_rec ASO_QUOTE_PUB.Qte_Header_Rec_Type;
1248 l_push_qte_header_rec ASO_QUOTE_PUB.Qte_Header_Rec_Type;
1249 l_tax_detail_rec ASO_QUOTE_PUB.Tax_Detail_Rec_Type;
1250 l_shipment_rec ASO_QUOTE_PUB.Shipment_Rec_Type;
1251 l_payment_rec ASO_QUOTE_PUB.Payment_Rec_Type;
1252 l_control_rec ASO_QUOTE_PUB.Control_Rec_Type;
1253
1254 l_curr_cart_id NUMBER := FND_API.g_miss_num;
1255 l_curr_cart_date DATE;
1256 l_curr_cart_currcode VARCHAR2(10);
1257 l_object_version_number NUMBER;
1258 l_ord_oneclick_id NUMBER;
1259 l_enabled_flag VARCHAR2(3);
1260 l_freight_code VARCHAR2(25);
1261 l_payment_id NUMBER;
1262 l_bill_to_pty_site_id NUMBER;
1263 l_ship_to_pty_site_id NUMBER;
1264 l_settings_date DATE;
1265 l_save_trigger VARCHAR2(3);
1266 l_party_type VARCHAR2(300);
1267 l_optional_party_id NUMBER;
1268 l_count_tax NUMBER := 0;
1269 l_payment_rec_id NUMBER := FND_API.G_MISS_NUM;
1270 l_count NUMBER := 0;
1271 l_retrieval_num NUMBER := FND_API.g_miss_num;
1272 l_credit_card_num NUMBER;
1273 l_credit_card_name VARCHAR2(80);
1274 l_credit_card_exp DATE;
1275 l_credit_card_holder_name VARCHAR2(50);
1276
1277
1278 lx_quote_header_id NUMBER;
1279 lx_last_update_date DATE;
1280 lX_return_status VARCHAR2(1);
1281 lx_msg_count NUMBER ;
1282 lx_msg_data VARCHAR2(2000);
1283 BEGIN
1284
1285 --Begin API body
1286
1287 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1288 ibe_util.debug('Starting ibe_ord_oneclick_pvt.Get_express_items_settings ');
1289 ibe_util.debug('PROCESSING FLAG : ' || p_flag);
1290 END IF;
1291
1292 l_qte_header_rec := x_qte_header_rec;
1293 if ((x_hd_tax_dtl_tbl is not null) and (x_hd_tax_dtl_tbl.count > 0)) then
1294 l_tax_detail_rec := x_hd_tax_dtl_tbl(1);
1295 end if;
1296 if ((x_payment_tbl is not null) and (x_payment_tbl.count > 0)) then
1297 l_payment_rec := x_payment_tbl(1);
1298 end if;
1299 if ((x_hd_shipment_tbl is not null) and (x_hd_shipment_tbl.count > 0)) then
1300 l_shipment_rec := x_hd_shipment_tbl(1);
1301 end if;
1302
1303 OPEN c_settings(x_qte_header_rec.party_id,
1304 x_qte_header_rec.cust_account_id);
1305 FETCH c_settings INTO
1306 l_object_version_number,
1307 l_ord_oneclick_id,
1308 l_enabled_flag,
1309 l_freight_code,
1310 l_payment_id, --mannamra: In light on credit card consolidation, this will be assignment id
1311 l_bill_to_pty_site_id,
1312 l_ship_to_pty_site_id,
1313 l_settings_date;
1314 CLOSE c_settings;
1315
1316 IF (p_flag <> 'CART') THEN
1317 -- see if there is a current cart
1318 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1319 ibe_util.debug('get_express_items_settings see if there is a current cart');
1320 ibe_util.debug('Input Cartid: '||l_qte_header_rec.quote_header_id);
1321 ibe_util.debug('Input Partyid: '||l_qte_header_rec.party_id);
1322 ibe_util.debug('Input Accountid: '||l_qte_header_rec.cust_account_id);
1323 END IF;
1324 OPEN c_quote(x_qte_header_rec.party_id,
1325 x_qte_header_rec.cust_account_id);
1326 FETCH c_quote INTO l_curr_cart_id, l_curr_cart_date, l_curr_cart_currcode;
1327 if c_quote%NOTFOUND then
1328 l_curr_cart_id := FND_API.g_miss_num;
1329 else
1330 null;
1331 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1332 ibe_util.debug('get_express_items_settings got l_curr_cart_id ' || to_char(l_curr_cart_id));
1333 ibe_util.debug('get_express_items_settings got l_curr_cart_date ' || to_char(l_curr_cart_date,'DD-MON-YYYY:HH:MI:SS'));
1334 ibe_util.debug('get_express_items_settings got l_curr_cart_currcode ' || l_curr_cart_currcode);
1335 END IF;
1336 end if;
1337 CLOSE c_quote;
1341
1338 end if; -- p_flag <> cart
1339
1340 l_control_rec.AUTO_VERSION_FLAG := 'N';
1342 -- see if we can consolidate
1343 if (l_curr_cart_id <> FND_API.g_miss_num) then
1344
1345 -- if no then move out the old cart id and set curr_cart_id to null to trigger creating a new one
1346 if ((l_curr_cart_date < l_settings_date) or (l_curr_cart_currcode <> x_qte_header_rec.currency_code)) then
1347 -- this means that the settings (which now include currency) have changed since we first
1348 -- created this express quote so we cannot safely use this one
1349 -- update the quote_name and "push it out"
1350 l_push_qte_header_rec.quote_header_id := l_curr_cart_id;
1351 l_push_qte_header_rec.quote_name := l_curr_cart_id;
1352 OPEN c_quote_date(l_curr_cart_id);
1353 FETCH c_quote_date INTO l_push_qte_header_rec.last_update_date;
1354 CLOSE c_quote_date;
1355
1356 l_control_rec.last_update_date := l_push_qte_header_rec.last_update_date;
1357 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1358 ibe_util.debug('****Calling IBE_Quote_Save_pvt.SAVE for pushing out **** ');
1359 ibe_util.debug(' l_qte_header_rec.quote_header_id : ' || l_qte_header_rec.quote_header_id);
1360 ibe_util.debug(' l_qte_header_rec.quote_name : ' || l_qte_header_rec.quote_name);
1361 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'));
1362 END IF;
1363 IBE_Quote_Save_pvt.SAVE(
1364 p_api_version_number => 1.0,
1365 p_init_msg_list => FND_API.G_FALSE,
1366 p_commit => FND_API.g_false,
1367 p_auto_update_active_quote => FND_API.g_false,
1368 --p_combineSameItem => 'Y', -- let api default from profile
1369 p_control_rec => l_control_rec,
1370 p_qte_header_rec => l_push_qte_header_rec,
1371 p_save_type => UPDATE_EXPRESSORDER,
1372 x_quote_header_id => lx_quote_header_id,
1373 x_last_update_date => lx_last_update_date,
1374 X_Return_Status => lx_Return_Status,
1375 X_Msg_Count => lx_Msg_Count,
1376 X_Msg_Data => lx_Msg_Data);
1377 --ibe_util.enable_debug;
1378 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1379 ibe_util.debug('Back from IBE_Quote_Save_pvt.SAVE for pushing out' );
1380 END IF;
1381 if lx_return_status <> FND_API.g_ret_sts_success then
1382 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1383 ibe_util.debug('ibe_ord_oneclick_pvt.Express_Buy_Order - non success status from IBE_Quote_Save_pvt.SAVE: ' || lx_return_status);
1384 END IF;
1385 FND_MESSAGE.SET_NAME('IBE','IBE_EXPR_PLSQL_API_ERROR');
1386 FND_MESSAGE.SET_TOKEN ( '0' , 'Get_Express_Items_Settings - IBE_Quote_Save_pvt.SAVE for nonconsolidation' );
1387 FND_MESSAGE.SET_TOKEN ( '1' , lx_return_status );
1388 FND_MSG_PUB.Add;
1389 if lx_return_status = FND_API.G_RET_STS_ERROR then
1390 RAISE FND_API.G_EXC_ERROR;
1391 elsif lx_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
1392 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1393 end if;
1394 end if;
1395 -- set these to g_miss so rest of api can know we have to start with a new quote
1396 l_qte_header_rec.quote_header_id := FND_API.g_miss_num;
1397 l_curr_cart_id := FND_API.g_miss_num;
1398 l_curr_cart_date := FND_API.g_miss_date;
1399 else
1400 --settings have'nt changed so we should consolidate incoming items to the open express-checkout cart
1401 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1402 ibe_util.debug('ibe_ord_oneclick_pvt.Get_express_items_settings-Consolidating to: '||l_curr_cart_id);
1403 END IF;
1404 l_qte_header_rec.quote_header_id := l_curr_cart_id;
1405 end if; -- end pushing out of old cart
1406 end if; -- end checking if we can consolidate
1407
1408 /*At this point, l_curr_cart_id is either set to be the one we should use
1409 or g_miss_num to trigger creation of a new quote*/
1410
1411 -- see if we need to apply the Express Checkout Settings (billing, shipping, payment info)
1412 if ((p_flag = 'CART') or ((p_flag <> 'CART') and (l_curr_cart_id = FND_API.g_miss_num))) then
1413
1414 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1415 ibe_util.debug('Express_Buy_Order got l_settings_date ' || to_char(l_settings_date,'DD-MON-YYYY:HH:MI:SS'));
1416 END IF;
1417
1418 if (p_flag = 'CART') then
1419 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1420 ibe_util.debug('Express ordering a CART id : ' || x_qte_header_rec.quote_header_id);
1421 ibe_util.debug('Making sure user has Full privilege on this cart');
1422 END IF;
1423 l_curr_cart_id := x_qte_header_rec.quote_header_id;
1424 -- l_qte_header_rec.quote_name := l_curr_cart_id;
1425 -- no need to rename the cart anymore since all cart carts will have a name now
1426 end if; --if p_flag = 'CART'
1427
1428 OPEN c_party_type(x_qte_header_rec.cust_account_id);
1429 FETCH c_party_type INTO l_party_type;
1430 CLOSE c_party_type;
1431
1432 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1433 ibe_util.debug('Party Type: ' || l_party_type);
1434 END IF;
1435
1436 if (l_party_type <> 'PERSON') then
1437 l_optional_party_id := x_qte_header_rec.party_id;
1438 end if;
1439
1440 select count(*) into l_count_tax
1441 from ASO_TAX_DETAILS
1442 where quote_header_id = l_curr_cart_id
1443 and quote_line_id is null;
1444
1445 IF (l_count_tax = 0) then
1446
1447 l_tax_detail_rec.operation_code := 'CREATE';
1451
1448 l_tax_detail_rec.quote_header_id := l_curr_cart_id; --will be g_miss for expr chkout of items
1449 l_tax_detail_rec.tax_exempt_flag := 'S';
1450 x_hd_tax_dtl_tbl(1) := l_tax_detail_rec;
1452 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1453 ibe_util.debug('Get_express_items_settings - *** creating header level tax record***');
1454 ibe_util.debug('l_tax_detail_rec.operation_code ' || l_tax_detail_rec.operation_code);
1455 ibe_util.debug('l_tax_detail_rec.quote_header_id ' || l_tax_detail_rec.quote_header_id);
1456 ibe_util.debug('l_tax_detail_rec.tax_exempt_flag ' || l_tax_detail_rec.tax_exempt_flag);
1457 END IF;
1458 END IF; --l_count_tax=0
1459
1460 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1461 ibe_util.debug('Get_express_items_settings - ***setting quote header info***');
1462 END IF;
1463
1464 l_qte_header_rec.invoice_to_party_site_id := l_bill_to_pty_site_id;
1465 l_qte_header_rec.invoice_to_party_id := l_optional_party_id;
1466 l_qte_header_rec.quote_source_code := 'IStore Oneclick';
1467 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1468 ibe_util.debug('Get_express_items_settings:l_qte_header_rec.party_id ' || l_qte_header_rec.party_id);
1469 ibe_util.debug('Get_express_items_settings:l_qte_header_rec.cust_account_id ' || l_qte_header_rec.cust_account_id);
1470 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);
1471 ibe_util.debug('Get_express_items_settings:l_qte_header_rec.invoice_to_party_id ' || l_qte_header_rec.invoice_to_party_id);
1472 ibe_util.debug('Get_express_items_settings:l_qte_header_rec.quote_source_code ' || l_qte_header_rec.quote_source_code);
1473 END IF;
1474
1475 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1476 ibe_util.debug('Express_Buy_Order - ***setting to create or update payment rec***');
1477 END IF;
1478 /*mannamra: In light of credit card consolidation, we will not be storing credit card number in one_click_all table
1479 we will also just be passing assignment id to ASO whi will in turn create a transaction and
1480 store the transaction extension id for this quote in aso_payments */
1481
1482 /*OPEN c_bank_acct(l_payment_id);
1483 FETCH c_bank_acct INTO l_credit_card_num, l_credit_card_exp, l_credit_card_holder_name;
1484 if c_bank_acct%NOTFOUND then
1485 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1486 ibe_util.debug('No credit card record found.');
1487 END IF;
1488 end if;
1489 CLOSE c_bank_acct;*/
1490
1491 --l_credit_card_name := IBE_ORD_ONECLICK_PVT.Get_credit_card_Type(l_credit_card_num);
1492 /* If Credit Card name is ERROR raise appropriate exception */
1493 /*If l_credit_card_name = 'ERROR' Then
1494 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1495 ibe_util.debug('Invalid Credit Card');
1496 END IF;
1497 End If;*/
1498
1499 /* per Bug #3020526, using info from AP_BANK_ACCOUNTS table instead
1500 OPEN c_party_name(x_qte_header_rec.party_id);
1501 FETCH c_party_name INTO l_credit_card_holder_name;
1502 if c_party_name%NOTFOUND then
1503 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1504 ibe_util.debug('No name to use for credit card holder name.');
1505 END IF;
1506 end if;
1507 CLOSE c_party_name;
1508 */
1509
1510 OPEN c_payment(l_curr_cart_id);
1511 FETCH c_payment INTO l_payment_rec.payment_id;
1512 if c_payment%NOTFOUND then
1513 l_payment_rec.operation_code := 'CREATE';
1514 else
1515 l_payment_rec.operation_code := 'UPDATE';
1516 end if;
1517 CLOSE c_payment;
1518
1519 /*l_payment_rec.payment_term_id := fnd_profile.value('IBE_DEFAULT_PAYMENT_TERM_ID');
1520 l_payment_rec.payment_type_code := 'CREDIT_CARD';
1521 l_payment_rec.payment_ref_number := l_credit_card_num;
1522 l_payment_rec.credit_card_expiration_date := l_credit_card_exp;
1523 l_payment_rec.credit_card_code := l_credit_card_name;
1524 l_payment_rec.credit_card_holder_name := l_credit_card_holder_name;*/
1525 l_payment_rec.payment_type_code := 'CREDIT_CARD';
1526 l_payment_rec.instr_assignment_id := l_payment_id;
1527 x_payment_tbl(1) := l_payment_rec;
1528 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1529 ibe_util.debug('l_payment_rec.operation_code ' || l_payment_rec.operation_code);
1530 ibe_util.debug('l_payment_rec.payment_term_id ' || l_payment_rec.payment_term_id);
1531 ibe_util.debug('l_payment_rec.payment_type_code '|| l_payment_rec.payment_type_code);
1532 ibe_util.debug('l_payment_rec.payment_ref_number <not logged>');
1533 ibe_util.debug('l_payment_rec.instr_asssignment_id ' || l_payment_rec.instr_assignment_id);
1534 END IF;
1535
1536 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1537 ibe_util.debug('Get_express_items_settings - ***setting to create or update shipment rec***');
1538 END IF;
1539 l_shipment_rec.operation_code := 'UPDATE';
1540 OPEN c_shipment(l_curr_cart_id);
1541 FETCH c_shipment INTO l_shipment_rec.shipment_id;
1542 if c_shipment%NOTFOUND then
1543 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1544 ibe_util.debug('no shipment rec at header, will CREATE one');
1545 END IF;
1546 l_shipment_rec.operation_code := 'CREATE';
1547 end if;
1548 CLOSE c_shipment;
1549
1550 l_shipment_rec.ship_method_code := l_freight_code;
1551 l_shipment_rec.ship_to_party_site_id := l_ship_to_pty_site_id;
1552 l_shipment_rec.ship_to_party_id := l_optional_party_id;
1553 x_hd_shipment_tbl(1) := l_shipment_rec;
1557 ibe_util.debug('l_shipment_rec.ship_method_code ' || l_shipment_rec.ship_method_code);
1554 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1555 ibe_util.debug('l_shipment_rec.shipment_id ' || l_shipment_rec.shipment_id);
1556 ibe_util.debug('l_shipment_rec.operation_code ' || l_shipment_rec.operation_code);
1558 ibe_util.debug('l_shipment_rec.ship_to_party_site_id ' || l_shipment_rec.ship_to_party_site_id);
1559 ibe_util.debug('l_shipment_rec.ship_to_party_id ' || l_shipment_rec.ship_to_party_id);
1560 END IF;
1561
1562 END IF; --IF ((p_flag = 'CART') or ((p_flag <> 'CART') and (l_curr_cart_id = FND_API.g_miss_num)))
1563
1564 x_qte_header_rec := l_qte_header_rec;
1565
1566 END;
1567
1568
1569 /* Local Function to get credit card type given the number
1570
1571 -- Input Parameter(s)
1572 - p_credit_card_number NUMBER
1573 -- Returns
1574 Credit_card_type VARCHAR2
1575 -- hekkiral - 21-DEC-2000
1576 */
1577 function Get_Credit_Card_Type(
1578 p_Credit_Card_Number NUMBER
1579 ) RETURN VARCHAR2
1580 AS
1581 l_credit_Card_number Varchar2(30);
1582 l_credit_card_length Number;
1583 Begin
1584
1585 l_credit_card_number := to_char(p_credit_card_number);
1586 l_credit_card_length := length(l_credit_card_number);
1587
1588 If (l_credit_Card_length = 16 and substr(l_credit_card_number,1,2) in ('51','52','53','54','55') ) Then
1589 Return ('MC');
1590 Elsif ((l_credit_Card_length = 13 or l_credit_card_length = 16) and substr(l_credit_card_number,1,1) = '4') Then
1591 Return ('VISA');
1592 Elsif (l_credit_Card_length = 15 and substr(l_credit_card_number,1,2) in ('34','37')) Then
1593 Return ('AMEX');
1594 Elsif (l_credit_card_length = 14 and substr(l_credit_card_number,1,3) in ('300','301','302','303','305','36','38')) Then
1595 Return('DINERS');
1596 Elsif (l_credit_card_length = 16 and substr(l_credit_card_number,1,4) = '6011') Then
1597 Return ('DISCOVER');
1598 Elsif ((l_credit_card_length = 15 and substr(l_credit_card_number,1,4) in ('2014','2149')) or
1599 ((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
1600 Return ('OTHERS');
1601 Else
1602 Return('ERROR');
1603 End If;
1604
1605 End Get_Credit_Card_Type;
1606
1607 /*-----------------------------------------------------------------------------
1608
1609 Update_Settings
1610 - Updates the Express checkout settings
1611 - Called when a credit card is deleted, to check if the deleted
1612 credit card is the one selected for Express Checkout. If so,
1613 disables the Express Checkout
1614
1615 ------------------------------------------------------------------------------
1616 */
1617 Procedure Update_Settings(
1618 p_api_version IN NUMBER,
1619 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1620 p_commit IN VARCHAR2 := FND_API.g_false,
1621 x_return_status OUT NOCOPY VARCHAR2,
1622 x_msg_count OUT NOCOPY NUMBER,
1623 x_msg_data OUT NOCOPY VARCHAR2,
1624 p_party_id IN NUMBER := NULL,
1625 p_acct_id IN NUMBER := NULL,
1626 p_assignment_id IN NUMBER := NULL)
1627 IS
1628
1629 l_api_name CONSTANT VARCHAR2(30) := 'Update_Settings';
1630 l_api_version CONSTANT NUMBER := 1.0;
1631 l_object_version_number NUMBER := FND_API.G_MISS_NUM;
1632 l_oneclick_id NUMBER := FND_API.G_MISS_NUM;
1633 l_payment_id NUMBER := FND_API.G_MISS_NUM;
1634 l_bill_ptysite_id NUMBER := FND_API.G_MISS_NUM;
1635 l_ship_ptysite_id NUMBER := FND_API.G_MISS_NUM;
1636 l_enabled_flag VARCHAR2(1) := 'N';
1637 l_freight_code VARCHAR2(30) := FND_API.G_MISS_CHAR;
1638
1639 Cursor c_get_oneclick_settings(c_party_id IN NUMBER,
1640 c_acct_id IN NUMBER)
1641 IS
1642
1643 select ord_oneclick_id, object_version_number, enabled_flag, freight_code,
1644 payment_id, bill_to_pty_site_id, ship_to_pty_site_id
1645 from ibe_ord_oneclick
1646 where party_id = c_party_id and cust_account_id = c_acct_id;
1647
1648 Begin
1649
1650 SAVEPOINT Update_Settings;
1651
1652 -- Initialize API rturn status to success
1653 x_return_status := FND_API.g_ret_sts_success;
1654
1655 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1656 ibe_util.debug('------- Input to ibe_ord_oneclick_pvt.Update_Settings: -----------------');
1657 ibe_util.debug('p_party_id :'||p_party_id);
1658 ibe_util.debug('p_acct_id :'||p_acct_id);
1659 ibe_util.debug('p_assignment_id :'||p_assignment_id);
1660 END IF;
1661
1662 OPEN c_get_oneclick_settings(p_party_id, p_acct_id);
1663 FETCH c_get_oneclick_settings
1664 INTO
1665 l_oneclick_id,
1666 l_object_version_number,
1667 l_enabled_flag,
1668 l_freight_code,
1669 l_payment_id,
1670 l_bill_ptysite_id,
1671 l_ship_ptysite_id;
1672
1673 IF c_get_oneclick_settings%NOTFOUND THEN
1674 l_oneclick_id := FND_API.g_miss_num;
1675 END IF;
1676 CLOSE c_get_oneclick_settings;
1677
1678 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1679 ibe_util.debug('------- Retrieved From TABLES: Update_Settings-----------------');
1680 ibe_util.debug('l_object_version_number :'||l_object_version_number);
1684 ibe_util.debug('l_payment_id :'||l_payment_id);
1681 ibe_util.debug('l_oneclick_id :'||l_oneclick_id);
1682 ibe_util.debug('l_enabled_flag :'||l_enabled_flag);
1683 ibe_util.debug('l_freight_code :'||l_freight_code);
1685 ibe_util.debug('l_bill_ptysite_id :'||l_bill_ptysite_id);
1686 ibe_util.debug('l_ship_ptysite_id :'||l_ship_ptysite_id);
1687 ibe_util.debug('Checking p_assignment_id not null :'||p_assignment_id);
1688 END IF;
1689
1690 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
1691 l_enabled_flag := 'N';
1692 l_payment_id := '';
1693
1694 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1695 ibe_util.debug('Update_Settings : Deleted CC is the one selected for Express checkout');
1696 ibe_util.debug('Update_Settings : Disabling the Express checkout - Calling ibe_ord_oneclick_pvt.save_settings');
1697 END IF;
1698
1699 ibe_ord_oneclick_pvt.save_settings(
1700 p_api_version => l_api_version,
1701 p_init_msg_list => FND_API.G_FALSE,
1702 p_commit => p_commit,
1703 x_return_status => x_return_status,
1704 x_msg_count => x_msg_count,
1705 x_msg_data => x_msg_data,
1706 p_party_id => p_party_id,
1707 p_acct_id => p_acct_id,
1708 p_object_version_number => l_object_version_number,
1709 p_enabled_flag => l_enabled_flag,
1710 p_freight_code => l_freight_code,
1711 p_payment_id => l_payment_id,
1712 p_bill_ptysite_id => l_bill_ptysite_id,
1713 p_ship_ptysite_id => l_ship_ptysite_id
1714 );
1715
1716 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1717 ibe_util.debug('Update_Settings : After Calling ibe_ord_oneclick_pvt.save_settings');
1718 ibe_util.debug('Update_Settings : x_return_status - '||x_return_status);
1719 END IF;
1720
1721 IF x_return_status <> FND_API.g_ret_sts_success THEN
1722 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1723 ibe_util.debug('ibe_ord_oneclick_pvt.Update_Settings - call to ibe_ord_oneclick_pvt.save_settings failed' || x_return_status);
1724 END IF;
1725 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1726 RAISE FND_API.G_EXC_ERROR;
1727 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1728 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1729 END IF;
1730 END IF;
1731
1732 IF FND_API.To_Boolean( p_commit ) THEN
1733 COMMIT WORK;
1734 END IF;
1735
1736 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1737 ibe_util.debug('Update_Settings : Success - Express Checkout disabled');
1738 ibe_util.debug('Update_Settings : End');
1739 END IF;
1740 ELSE
1741 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1742 ibe_util.debug('Update_Settings : Express Checkout disabled - No need for update');
1743 ibe_util.debug('Update_Settings : End');
1744 END IF;
1745
1746 END IF;
1747
1748 FND_MSG_PUB.Count_And_Get
1749 ( p_encoded => FND_API.G_FALSE,
1750 p_count => x_msg_count,
1751 p_data => x_msg_data
1752 );
1753
1754 EXCEPTION
1755 WHEN FND_API.G_EXC_ERROR THEN
1756 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1757 ibe_util.debug('ibe_ord_oneclick_pvt.Update_Settings: EXPECTED ERROR EXCEPTION ' || to_char(sysdate,'DD-MON-YYYY:HH24:MI:SS'));
1758 END IF;
1759 ROLLBACK TO Update_Settings;
1760 x_return_status := FND_API.G_RET_STS_ERROR ;
1761 FND_MSG_PUB.Count_And_Get
1762 ( p_encoded => FND_API.G_FALSE,
1763 p_count => x_msg_count,
1764 p_data => x_msg_data
1765 );
1766
1767 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1768 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1769 ibe_util.debug('ibe_ord_oneclick_pvt.Update_Settings: UNEXPECTED ERROR EXCEPTION ' || to_char(sysdate,'DD-MON-YYYY:HH24:MI:SS'));
1770 END IF;
1771 ROLLBACK TO Update_Settings;
1772 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1773 FND_MSG_PUB.Count_And_Get
1774 ( p_encoded => FND_API.G_FALSE,
1775 p_count => x_msg_count,
1776 p_data => x_msg_data
1777 );
1778
1779 WHEN OTHERS THEN
1780 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1781 ibe_util.debug('ibe_ord_oneclick_pvt.Update_Settings: OTHER EXCEPTION ' || to_char(sysdate,'DD-MON-YYYY:HH24:MI:SS'));
1782 END IF;
1783 ROLLBACK TO Update_Settings;
1784 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1785 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1786 FND_MSG_PUB.Add_Exc_Msg
1787 ( G_PKG_NAME,
1788 l_api_name
1789 );
1790 END IF;
1791 FND_MSG_PUB.Count_And_Get
1792 ( p_encoded => FND_API.G_FALSE,
1793 p_count => x_msg_count,
1794 p_data => x_msg_data
1795 );
1796
1797 End Update_Settings;
1798 end ibe_ord_oneclick_pvt;