1 PACKAGE BODY IBE_Order_Save_pvt AS
2 /* $Header: IBEVORDB.pls 120.9 2010/09/09 02:00:15 scnagara ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IBE_Order_Save_pvt';
5 l_true VARCHAR2(1) := FND_API.G_TRUE;
6
7
8 PROCEDURE Get_Order_Status(p_header_id IN NUMBER
9 ,x_order_status OUT NOCOPY VARCHAR2
10 ,x_last_update_date OUT NOCOPY DATE)
11
12 IS
13 CURSOR c_gethdrstatus(p_header_id NUMBER) IS
14 SELECT flow_status_code,last_update_date FROM oe_order_headers_all WHERE header_id = p_header_id;
15
16
17 l_gethdrstatus c_gethdrstatus%rowtype;
18 lx_order_status VARCHAR2(30) := FND_API.G_MISS_CHAR;
19
20
21 BEGIN
22
23 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
24 Ibe_Util.debug('Begin IBE_ORDER_SAVE_PVT:Get_Order_Status');
25 END IF;
26
27 OPEN c_gethdrstatus(p_header_id);
28 FETCH c_gethdrstatus INTO l_gethdrstatus;
29 IF (c_gethdrstatus%NOTFOUND) THEN
30 x_order_status:= FND_API.G_MISS_CHAR;
31 x_last_update_date := null;
32 ELSE
33 x_order_status := l_gethdrstatus.flow_status_code;
34 x_last_update_date := l_gethdrstatus.last_update_date;
35 END IF;
36 CLOSE c_gethdrstatus;
37
38 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
39 Ibe_Util.debug('End IBE_ORDER_SAVE_PVT:Get_Order_Status');
40 END IF;
41
42 END Get_Order_Status;
43
44
45 PROCEDURE Retrieve_OE_Messages IS
46 l_msg_count NUMBER;
47 l_msg_data VARCHAR2(2000);
48 x_msg_data VARCHAR2(2000);
49
50 l_len_sqlerrm NUMBER;
51 i NUMBER := 1;
52
53 l_error_index_flag VARCHAR2(1) := 'N';
54 l_msg_index NUMBER := 0;
55 l_msg_context VARCHAR2(2000);
56 l_msg_entity_code VARCHAR2(30);
57 l_msg_entity_ref VARCHAR2(50);
58 l_msg_entity_id NUMBER;
59 l_msg_header_id NUMBER;
60 l_msg_line_id NUMBER;
61 l_msg_order_source_id NUMBER;
62 l_msg_orig_sys_document_ref VARCHAR2(50);
63 l_msg_change_sequence VARCHAR2(50);
64 l_msg_orig_sys_line_ref VARCHAR2(50);
65 l_msg_orig_sys_shipment_ref VARCHAR2(50);
66 l_msg_source_document_type_id NUMBER;
67 l_msg_source_document_id NUMBER;
68 l_msg_source_document_line_id NUMBER;
69 l_msg_attribute_code VARCHAR2(50);
70 l_msg_constraint_id NUMBER;
71 l_msg_process_activity NUMBER;
72 l_msg_notification_flag VARCHAR2(1);
73 l_msg_type VARCHAR2(30);
74
75 BEGIN
76
77 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
78 Ibe_Util.debug('Begin IBE_ORDER_SAVE_PVT:Retrieve_OE_Messages');
79 END IF;
80
81 OE_MSG_PUB.Count_And_Get
82 ( p_count => l_msg_count,
83 p_data => l_msg_data
84 );
85
86 IF l_msg_count > 0 THEN
87 FOR k IN 1 .. l_msg_count LOOP
88 i:=1;
89 oe_msg_pub.get (
90 p_msg_index => k
91 ,p_encoded => FND_API.G_FALSE
92 ,p_data => l_msg_data
93 ,p_msg_index_out => l_msg_index);
94
95 IF (upper(l_msg_data) <> 'ORDER HAS BEEN BOOKED.') THEN -- bug# 1935468
96 BEGIN
97 oe_msg_pub.get_msg_context (
98 p_msg_index => l_msg_index
99 ,x_entity_code => l_msg_entity_code
100 ,x_entity_ref => l_msg_entity_ref
101 ,x_entity_id => l_msg_entity_id
102 ,x_header_id => l_msg_header_id
103 ,x_line_id => l_msg_line_id
104 ,x_order_source_id => l_msg_order_source_id
105 ,x_orig_sys_document_ref => l_msg_orig_sys_document_ref
106 ,x_orig_sys_line_ref => l_msg_orig_sys_line_ref
107 ,x_orig_sys_shipment_ref => l_msg_orig_sys_shipment_ref
108 ,x_change_sequence => l_msg_change_sequence
109 ,x_source_document_type_id => l_msg_source_document_type_id
110 ,x_source_document_id => l_msg_source_document_id
111 ,x_source_document_line_id => l_msg_source_document_line_id
112 ,x_attribute_code => l_msg_attribute_code
113 ,x_constraint_id => l_msg_constraint_id
114 ,x_process_activity => l_msg_process_activity
115 ,x_notification_flag => l_msg_notification_flag
116 ,x_type => l_msg_type
117 );
118
119 EXCEPTION
120 WHEN others THEN
121 l_error_index_flag := 'Y';
122 END;
123
124 IF l_error_index_flag = 'Y' THEN
125 EXIT;
126 END IF;
127
128 IF oe_msg_pub.g_msg_tbl(l_msg_index).message_text IS NULL THEN
129 x_msg_data := oe_msg_pub.get(l_msg_index, 'F');
130 END IF;
131
132 IF l_msg_orig_sys_line_ref IS NOT NULL AND l_msg_orig_sys_line_ref <> FND_API.G_MISS_CHAR THEN
133 l_msg_context := 'Error in Line: '||rtrim(l_msg_orig_sys_line_ref)||' :';
134 END IF;
135
136 x_msg_data := l_msg_context||l_msg_data;
137
138 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
139 Ibe_Util.debug('Retrieve_OE_Messages - x_msg_data: '||x_msg_data);
140 END IF;
141
142 l_len_sqlerrm := Length(x_msg_data) ;
143 WHILE l_len_sqlerrm >= i
144 LOOP
145 FND_MESSAGE.Set_Name('IBE', 'IBE_OM_ERROR');
146 FND_MESSAGE.Set_token('MSG_TXT' , substr(x_msg_data,i,240));
147 i := i + 240;
148 FND_MSG_PUB.ADD;
149 END LOOP;
150
151 END IF;
152 END LOOP;
153 END IF;
154
155 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
156 Ibe_Util.debug('End IBE_ORDER_SAVE_PVT:Retrieve_OE_Messages');
157 END IF;
158
159 END Retrieve_OE_Messages;
160
161 PROCEDURE DeactivateOrder(p_party_id IN NUMBER
162 ,p_cust_account_id IN NUMBER
163 ,p_currency_code IN VARCHAR2
164 )
165
166 IS
167
168 BEGIN
169
170 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
171 Ibe_Util.debug('Begin IBE_ORDER_SAVE_PVT:Deactivate Order: ' || p_party_id || ' : '||p_cust_account_id);
172 END IF;
173
174
175 IBE_ACTIVE_QUOTES_ALL_PKG.Update_row(
176 X_OBJECT_VERSION_NUMBER => 1,
177 X_ORDER_HEADER_ID => null ,
178 X_PARTY_ID => p_party_id,
179 X_CUST_ACCOUNT_ID => p_cust_account_id,
180 X_CURRENCY_CODE => p_currency_code,
181 X_LAST_UPDATE_DATE => sysdate,
182 X_LAST_UPDATED_BY => fnd_global.user_id,
183 X_LAST_UPDATE_LOGIN => 1,
184 X_RECORD_TYPE => 'ORDER');
185
186
187 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
188 Ibe_Util.debug('End IBE_ORDER_SAVE_PVT:DeactivateOrder');
189 END IF;
190
191 END DeactivateOrder;
192
193 PROCEDURE ActivateOrder(p_order_header_id IN NUMBER
194 ,p_party_id IN NUMBER
195 ,p_cust_account_id IN NUMBER
196 ,p_currency_code IN VARCHAR2
197 )
198 IS
199
200 l_pend_ret_id NUMBER := null; --active pending return present in active carts table or not
201 l_pr_party_id NUMBER := null;
202
203 cursor c_check_pr_aqa(c_party_id number,
204 c_cust_account_id number,
205 c_currency_code varchar2
206 ) is
207 select aq.order_header_id,aq.party_id
208 from ibe_active_quotes aq
209 where cust_account_id = c_cust_account_id
210 and party_id = c_party_id
211 and currency_code = c_currency_code
212 and record_type = 'ORDER';
213
214 rec_check_pr_aqa c_check_pr_aqa%rowtype;
215
216
217 BEGIN
218
219 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
220 Ibe_Util.debug('Begin IBE_ORDER_SAVE_PVT:ActivateOrder');
221 END IF;
222
223 open c_check_pr_aqa( p_party_id,p_cust_account_id,p_currency_code);
224 fetch c_check_pr_aqa into rec_check_pr_aqa;
225 if (c_check_pr_aqa%notfound) then
226
227 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
228 Ibe_Util.debug('Pending Return Not Exists');
229 END IF;
230 IBE_ACTIVE_QUOTES_ALL_PKG.Insert_row(
231 X_OBJECT_VERSION_NUMBER => 1,
232 X_ORDER_HEADER_ID => p_order_header_id,
233 X_PARTY_ID => p_party_id,
234 X_CUST_ACCOUNT_ID => p_cust_account_id,
235 X_LAST_UPDATE_DATE => sysdate,
236 X_CREATION_DATE => sysdate,
237 X_CREATED_BY => fnd_global.USER_ID,
238 X_LAST_UPDATED_BY => fnd_global.USER_ID,
239 X_LAST_UPDATE_LOGIN => fnd_global.conc_login_id,
240 X_CURRENCY_CODE => p_currency_code,
241 X_RECORD_TYPE => 'ORDER',
242 X_ORG_ID => mo_global.GET_CURRENT_ORG_ID());
243
244
245 else
246 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
247 Ibe_Util.debug('Pending Return Already Exists');
248 END IF;
249
250 IBE_ACTIVE_QUOTES_ALL_PKG.update_row(
251 X_OBJECT_VERSION_NUMBER => 1,
252 X_LAST_UPDATE_DATE => sysdate,
253 X_LAST_UPDATED_BY => fnd_global.user_id,
254 X_LAST_UPDATE_LOGIN => 1,
255 X_PARTY_ID => p_party_id,
256 X_CUST_ACCOUNT_ID => p_cust_account_id,
257 X_ORDER_HEADER_ID => p_order_header_id,
258 X_CURRENCY_CODE => p_currency_code,
259 X_RECORD_TYPE => 'ORDER');
260
261
262 END IF;
263
264 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
265 Ibe_Util.debug('End IBE_ORDER_SAVE_PVT:ActivateOrder');
266 END IF;
267
268 END ActivateOrder;
269
270
271 PROCEDURE DefaultFromLineSiteId(p_line_tbl IN OE_Order_PUB.Line_Tbl_Type
272 ,p_party_id IN NUMBER
273 ,p_siteuse_type IN VARCHAR2
274 ,x_site_use_id OUT NOCOPY NUMBER)
275 IS
276
277 cursor c_get_line_Info(l_lineId Number) is
278 select invoice_to_org_id from oe_order_lines_all where line_id = l_lineId;
279
280
281 p_line_ids varchar2(3000) := null;
282 l_line_id_query varchar2(3000);
283 l_tmp_query varchar2(4000);
284 l_linesite_use_id NUMBER;
285 l_temp_orgid NUMBER;
286 l_get_line_Info c_get_line_Info%rowtype;
287
288 Type partysite_type is REF CURSOR;
289 psitetype_tmp partysite_type;
290
291 l_parseNum NUMBER :=5;
292 l_parseKey varchar2(40) :='ORDER_SAVE_LINE_IDS';
293
294 BEGIN
295
296 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
297 Ibe_Util.debug('Begin IBE_ORDER_SAVE_PVT:DefaultFromLineSiteId');
298 END IF;
299
300 for lineIdx in 1..p_line_tbl.count
301 loop
302 IF (p_siteuse_type = 'BILL_TO') THEN
303 p_line_ids := p_line_ids || ','|| p_line_tbl(lineIdx).invoice_to_org_id;
304 ELSIF (p_siteuse_type = 'SHIP_TO') THEN
305 p_line_ids := p_line_ids || ','|| p_line_tbl(lineIdx).ship_to_org_id;
306 END IF;
307 end loop;
308
309
310 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
311 Ibe_util.debug('p_line_ids set: '||p_line_ids);
312 end if;
313
314 /** This API is called for parsing the where condition PlSQL Bind Variables Std. **/
315 IBE_LEAD_IMPORT_PVT.parseInput (p_line_ids, 'CHAR', l_parseKey,l_parseNum, l_line_id_query);
316
317 l_tmp_query := 'SELECT csu.site_use_id '||
318 'FROM hz_cust_site_uses_all csu '||
319 'WHERE csu.status = ''A'' AND site_use_code = :siteusecode '||
320 'AND csu.site_use_id IN ('||l_line_id_query||')';
321
322
323 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
324 IBE_Util.debug('qry executed: '|| l_tmp_query);
325 end if;
326 open psitetype_tmp for l_tmp_query using p_siteuse_type,l_parseKey;
327 fetch psitetype_tmp into l_linesite_use_id;
328 close psitetype_tmp;
329 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
330 IBE_Util.debug('site use id from line: '|| l_linesite_use_id);
331 end if;
332 x_site_use_id := l_linesite_use_id;
333
334 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
335 Ibe_Util.debug('End IBE_ORDER_SAVE_PVT:DefaultFromLineSiteId');
336 END IF;
337
338 END DefaultFromLineSiteId;
339
340 PROCEDURE MergeLines(
341 p_header_id IN Number,
342 p_order_line_rec IN OE_Order_PUB.Line_Rec_Type,
343 x_line_rec OUT NOCOPY OE_Order_PUB.Line_Rec_Type
344 )
345 IS
346 cursor c_get_lineInfo(l_lineid varchar2,l_ordhdrId varchar2,l_rethdrId number) is
347 select * from oe_order_lines_all
348 where header_id = l_rethdrId
349 and return_attribute2 = l_lineid
350 and return_attribute1 = l_ordhdrId
351 and line_category_code= 'RETURN';
352
353 l_line_rec c_get_lineInfo%rowType;
354
355 BEGIN
356
357 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
358 IBE_Util.Debug('IBE_ORDER_SAVE_PVT:MergeLines - START');
359 END IF;
360 open c_get_lineInfo(p_order_line_rec.return_attribute2,
361 p_order_line_rec.return_attribute1,
362 p_header_id
363 );
364 Fetch c_get_lineInfo into l_line_rec;
365
366 if (c_get_lineInfo%found) then
367 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
368 Ibe_Util.Debug('MergeLines Already a record exists.');
369 END IF;
370 x_line_rec := OE_ORDER_PUB.G_MISS_LINE_REC;
371 x_line_rec.item_type_code := p_order_line_rec.item_type_code;
372 x_line_rec.ordered_quantity := p_order_line_rec.ordered_quantity + l_line_rec.ordered_quantity;
373 x_line_rec.line_id := l_line_rec.line_id;
374 x_line_rec.operation := OE_Globals.G_OPR_UPDATE;
375 else
376 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
377 Ibe_Util.Debug('MergeLines else part: No previous record');
378 end if;
379 x_line_rec := p_order_line_rec;
380 x_line_rec.operation := OE_GLOBALS.G_OPR_CREATE;
381 end if;
382 close c_get_lineInfo;
383
384 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
385 IBE_Util.Debug('End IBE_ORDER_SAVE_PVT:MergeLines');
386 END IF;
387 END MergeLines;
388
389
390 PROCEDURE DefaultLineTypes(p_header_type_id IN NUMBER
391 ,x_line_type_id OUT NOCOPY NUMBER
392 )
393 IS
394
395 cursor c_linetypeinfo(header_typeid number) is
396 select default_inbound_line_type_id from oe_transaction_types_all
397 where transaction_type_id = header_typeid;
398
399 l_linetypeinfo c_linetypeinfo%rowtype;
400
401 BEGIN
402
403 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
404 IBE_Util.Debug('BEgin IBE_ORDER_SAVE_PVT:DefaultLineTypes');
405 END IF;
406 open c_linetypeInfo(p_header_type_id);
407 fetch c_linetypeinfo into l_linetypeinfo;
408 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
409 IBE_util.debug('line type id info' || c_linetypeinfo%rowcount);
410 end if;
411 x_line_type_id := l_linetypeinfo.DEFAULT_INBOUND_LINE_TYPE_ID;
412 close c_linetypeinfo;
413
414 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
415 IBE_Util.Debug('End IBE_ORDER_SAVE_PVT:DefaultLineTypes');
416 END IF;
417
418 END DefaultLineTypes;
419
420
421 PROCEDURE DefaultLineRecord(
422 p_order_line_tbl IN OE_Order_PUB.Line_Tbl_Type
423 ,p_order_header_rec IN OE_Order_PUB.Header_Rec_Type
424 ,p_save_type IN NUMBER := FND_API.G_MISS_NUM
425 ,x_order_line_tbl OUT NOCOPY OE_Order_Pub.Line_Tbl_Type
426 )
427 IS
428
429 CURSOR c_salesrep_info(l_lineid number) IS
430 SELECT salesrep_id from OE_ORDER_LINES_ALL WHERE line_id = l_lineid;
431
432 CURSOR c_defaulthdrline_rec(c_ordhdr_typeid number) IS
433 select order_type_id from oe_order_headers_all where header_id = c_ordhdr_typeid;
434
435 CURSOR OrigOrderQtyCur(c_origqtyLineId number) IS
436 select ordered_quantity
437 from oe_order_lines_all
438 where line_id= c_origqtyLineId;
439
440 l_tmp_origQty NUMBER;
441 l_salesrep_info_rec c_salesrep_info%rowtype;
442 l_order_line_tbl OE_Order_PUB.Line_Tbl_Type := OE_Order_PUB.G_MISS_LINE_TBL;
443 l_linetype_id NUMBER;
444 lx_line_rec OE_Order_PUB.Line_Rec_Type;
445 l_salesrep_id NUMBER;
446 l_orderhdr_typeid NUMBER;
447
448 BEGIN
449
450 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
451 Ibe_util.Debug('Begin IBE_ORDER_SAVE_PVT: DefaultLineRecord');
452 END IF;
453 For j IN 1..p_order_line_tbl.COUNT
454 Loop
455 l_order_line_tbl(j) := p_order_line_tbl(j);
456 End Loop;
457
458 -- line_type_id
459 -- Here header level value is retrieved because
460 -- when a new line is added to the existing return.
461 -- Line_Type_id has to be fetched from the Order_type_id already present for the return.
462 -- Headerlevel: update Line Level:create
463 -- When update operation header may have order type id or may not have.
464 -- so chk it and fetch if not present.
465
466 l_orderhdr_typeid := p_order_header_rec.order_type_id;
467
468 IF (l_orderhdr_typeid is null OR l_orderhdr_typeid=FND_API.G_MISS_NUM)
469 THEN
470 open c_defaulthdrline_rec(p_order_header_rec.header_id);
471 fetch c_defaulthdrline_rec into l_orderhdr_typeid;
472 close c_defaulthdrline_rec;
473 END IF; -- x_orderheader_rec.operation = update
474
475 DefaultLineTypes(p_header_type_id => l_orderhdr_typeid
476 ,x_line_type_id => l_linetype_id);
477
478 IF (IBE_UTIL.G_DEBUGON = l_true) then
479 IBE_util.debug('p_save_type: ' || p_save_type);
480 end if;
481
482 For i in 1..l_order_line_tbl.COUNT
483 LOOP
484
485 IF (p_save_type = SAVE_NORMAL) THEN
486 l_order_line_tbl(i).operation := OE_GLOBALS.G_OPR_UPDATE;
487
488 ELSIF (p_save_type = SAVE_ADDITEMS AND p_order_header_rec.ORDER_CATEGORY_CODE = 'RETURN') THEN
489
490 -- salesrep_id
491 -- IF (l_order_line_tbl(i).return_attribute2 is not null AND
492 -- l_order_line_tbl(i).return_attribute2 <> FND_API.G_MISS_CHAR) THEN
493 for l_salesrep_inforec in c_salesrep_info(l_order_line_tbl(i).return_attribute2)
494 loop
495 l_salesrep_id := l_salesrep_inforec.salesrep_id;
496 end loop;
497 -- end if;
498
499 IF (p_order_header_rec.operation = OE_GLOBALS.G_OPR_UPDATE) THEN
500 MergeLines(p_header_id => p_order_header_rec.HEADER_ID,
501 p_order_line_rec => l_order_line_tbl(i),
502 x_line_rec => lx_line_rec);
503
504 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
505 ibe_util.Debug('Save:line_tbl.ordered_quantity' || lx_line_rec.ORDERED_QUANTITY);
506 ibe_util.Debug('Save:line_tbl.operation ' || lx_line_rec.operation);
507 END IF;
508 l_order_line_tbl(i) := lx_line_rec;
509
510 /* The following IF LOOP would be executed when there is already an active pending Return And
511 the user is trying to add an item to it. So it will be 'UPDATE' at header level and
512 'CREATE' at line level */
513
514 IF (l_order_line_tbl(i).operation = OE_GLOBALS.G_OPR_CREATE) THEN
515 l_order_line_tbl(i).line_category_code := 'RETURN';
516 l_order_line_tbl(i).return_context := 'ORDER';
517 l_order_line_tbl(i).salesrep_id := l_salesrep_id;
518 l_order_line_tbl(i).source_document_type_id := 13; --iStore Account
519
520 -- MultiOrder Flow bug# 3272918
521 -- If any of the Line Record has '0' qty.
522 -- Refer the original OrderedQuantity and create the line w/ the same
523 for k in 1..l_order_line_tbl.count
524 loop
525 if(l_order_line_tbl(i).ORDERED_QUANTITY = 0) Then
526 open OrigOrderQtyCur(l_order_line_tbl(i).RETURN_ATTRIBUTE2);
527 fetch OrigOrderQtyCur into l_tmp_origQty;
528 l_order_line_tbl(i).ORDERED_QUANTITY := l_tmp_origQty;
529 close OrigOrderQtyCur;
530 end if;
531 end loop;
532
533 IF(l_order_line_tbl(i).LINE_TYPE_ID is null OR
534 l_order_line_tbl(i).LINE_TYPE_ID = FND_API.G_MISS_NUM)
535 THEN
536 IF (l_linetype_id is not null AND l_linetype_id <> FND_API.G_MISS_NUM)
537 THEN
538 l_order_line_tbl(i).line_type_id := l_linetype_id;
539 IF (IBE_UTIL.G_DEBUGON = l_true) then
540 ibe_util.debug('line type id..'||l_order_line_tbl(i).line_type_id);
541 end if;
542 ELSE
543 FND_Message.Set_Name('IBE', 'IBE_ERR_OT_LINE_TYPE_ID_MISS');
544 FND_Msg_Pub.Add;
545 RAISE FND_API.G_EXC_ERROR;
546 END IF;
547 END IF;
548 END IF; -- l_order_line_tbl.operation =create
549 ELSE -- headerrec.operation.operation ='update'
550
551 /* This flow will be executed when both HEader and Line level are 'CREATE'.
552 ie first time creation of a Return */
553
554 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
555 ibe_util.Debug('Inside Line Values Defaulting flow');
556 END IF;
557 l_order_line_tbl(i).operation := OE_GLOBALS.G_OPR_CREATE;
558 l_order_line_tbl(i).line_category_code := 'RETURN';
559 l_order_line_tbl(i).return_context := 'ORDER';
560 l_order_line_tbl(i).salesrep_id := l_salesrep_id;
561 l_order_line_tbl(i).source_document_type_id := 13; --iStore Account
562
563 -- MutliOrder Flow bug# 3272918
564 -- If any of the Line Record has '0' qty.
565 -- Refer the original OrderedQuantity and create the line w/ the same
566
567 for k in 1..l_order_line_tbl.count
568 loop
569 if(l_order_line_tbl(i).ORDERED_QUANTITY = 0) Then
570 open OrigOrderQtyCur(l_order_line_tbl(i).RETURN_ATTRIBUTE2);
571 fetch OrigOrderQtyCur into l_tmp_origQty;
572 l_order_line_tbl(i).ORDERED_QUANTITY := l_tmp_origQty;
573 close OrigOrderQtyCur;
574 end if;
575 end loop;
576
577 IF(l_order_line_tbl(i).LINE_TYPE_ID is null OR l_order_line_tbl(i).LINE_TYPE_ID = FND_API.G_MISS_NUM)
578 THEN
579 IF (l_linetype_id is not null AND l_linetype_id <> FND_API.G_MISS_NUM)
580 THEN
581 l_order_line_tbl(i).line_type_id := l_linetype_id;
582 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
583 ibe_util.debug('line type id..'||l_order_line_tbl(i).line_type_id);
584 end if;
585 ELSE
586 FND_Message.Set_Name('IBE', 'IBE_ERR_OT_LINE_TYPE_ID_MISS');
587 FND_Msg_Pub.Add;
588 RAISE FND_API.G_EXC_ERROR;
589 END IF;
590 END IF;
591
592 END IF; -- headerrec.operation.operation ='update'
593
594 ELSIF (p_save_type = SAVE_REMOVEITEMS) THEN
595 l_order_line_tbl(i).operation := OE_GLOBALS.G_OPR_DELETE;
596 END IF;
597
598 End Loop;
599
600 For k in 1..l_order_line_tbl.COUNT
601 Loop
602 x_order_line_tbl(k) := l_order_line_tbl(k);
603 End Loop;
604
605 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
606 Ibe_util.Debug('End IBE_ORDER_SAVE_PVT:DefaultLineRecord');
607 END IF;
608
609 END DefaultLineRecord;
610
611
612 PROCEDURE GetActiveReturnOrder(p_cust_acct_id IN NUMBER
613 ,p_curr_code IN VARCHAR2
614 ,p_party_id IN NUMBER
615 ,x_order_header_id OUT NOCOPY NUMBER)
616 IS
617 CURSOR c_getactive_pendret(custAcctId number,currencyCode varchar2,partyId number) IS
618 SELECT aq.order_header_id FROM ibe_active_quotes aq, oe_order_headers_all oh
619 WHERE aq.party_id = partyId
620 AND aq.cust_account_id = custAcctId
621 AND aq.currency_code = currencyCode
622 AND aq.record_type = 'ORDER'
623 AND aq.order_header_id = oh.header_id
624 AND oh.flow_status_code IN ('ENTERED','WORKING');
625
626 l_active_returnid NUMBER:= FND_API.G_MISS_NUM;
627
628 BEGIN
629
630 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
631 Ibe_util.Debug('Begin IBE_ORDER_SAVE_PVT: GetActiveReturnOrder');
632 END IF;
633
634 OPEN c_getactive_pendret(p_cust_acct_id,
635 p_curr_code,
636 p_party_id);
637
638 FETCH c_getactive_pendret INTO l_active_returnid;
639 IF (c_getactive_pendret%FOUND) THEN
640 x_order_header_id := l_active_returnid;
641 ELSE
642 x_order_header_id := FND_API.G_MISS_NUM;
643 END IF;
644
645 CLOSE c_getactive_pendret;
646
647 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
648 Ibe_util.Debug('End IBE_ORDER_SAVE_PVT: GetActiveReturnOrder');
649 END IF;
650
651 END GetActiveReturnOrder;
652
653
654 PROCEDURE DefaultHeaderRecord(
655 p_order_header_rec IN OE_Order_PUB.Header_Rec_Type := OE_Order_PUB.G_MISS_HEADER_REC
656 ,p_party_id IN NUMBER
657 ,p_save_type IN NUMBER := FND_API.G_MISS_NUM
658 ,x_order_header_rec OUT NOCOPY OE_Order_PUB.Header_Rec_Type
659 )
660 IS
661
662 CURSOR c_defaulthdr_rec(c_ordhdr_id number) IS
663 select order_type_id from oe_order_headers_all where header_id = c_ordhdr_id;
664
665 l_order_header_id NUMBER := FND_API.G_MISS_NUM;
666 l_ordertype_id NUMBER;
667 l_salesrep_id VARCHAR2(360);
668
669 l_salesrep_number VARCHAR2(360); --MOAC Changes by ASO::Obsoletion of ASO_DEFAULT_PERSON_ID
670 l_user_orgid NUMBER;
671
672 l_flow_status_code VARCHAR2(30);
673 l_last_update_date DATE;
674 l_preApprove_prof VARCHAR2(10) := 'N';
675
676 BEGIN
677
678 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
679 Ibe_util.Debug('Begin IBE_ORDER_SAVE_PVT: DefaultHeaderRecord');
680 END IF;
681
682 x_order_header_rec := p_order_header_rec;
683
684 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
685 Ibe_util.Debug('p_save_type: ' || p_save_type ||'::'||x_order_header_rec.header_id);
686 Ibe_util.Debug('category_code ' || x_order_header_rec.ORDER_CATEGORY_CODE);
687 END IF;
688
689
690 IF (p_save_type = SAVE_ADDITEMS) THEN
691 IF (x_order_header_rec.ORDER_CATEGORY_CODE is null
692 OR x_order_header_rec.ORDER_CATEGORY_CODE = FND_API.G_MISS_CHAR)
693 THEN
694 FND_Message.Set_Name('IBE', 'IBE_ERR_OT_ORDER_CATG_MISS');
695 FND_Msg_Pub.Add;
696 RAISE FND_API.G_EXC_ERROR;
697 END IF;
698 END IF;
699
700 /*** Assumptions:
701
702 -- For RemoveLine, SubmitReturn and recalculate HeaderId will be set from JSP
703 -- If headerId not set, then atleaset ORDER_CATEGORY_CODE should be set
704 -- For AddLines flow, No hdrId is set but ordercategcode is definitely set by Order.java
705
706 ***/
707
708 IF (x_order_header_rec.header_id is not null AND
709 x_order_header_rec.header_id <> FND_API.G_MISS_NUM)
710 THEN
711
712 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
713 Ibe_util.Debug('Header id is not null, so cheking its status');
714 END IF;
715
716 Get_Order_Status(p_header_id => x_order_header_rec.header_id
717 ,x_order_status => l_flow_status_code
718 ,x_last_update_date => l_last_update_date);
719
720 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
721 Ibe_util.Debug('last_update_date sent from ui: '||
722 to_char(x_order_header_rec.last_update_date,'dd-mm-yyyy hh:mi:ss'));
723 Ibe_util.Debug('last_update_date from db: '||
724 to_char(l_last_update_date,'dd-mm-yyyy hh:mi:ss'));
725 Ibe_util.Debug('l_flow_status_code: '||l_flow_status_code);
726 END IF;
727
728 IF ((l_flow_status_code = 'BOOKED' OR l_flow_status_code='CLOSED' OR
729 l_flow_status_code = 'CANCELLED')
730 OR l_last_update_date > x_order_header_rec.last_update_date)
731 THEN
732 FND_Message.Set_Name('IBE', 'IBE_ERR_OT_REFRESH_RETURN');
733 FND_Msg_Pub.Add;
734 RAISE FND_API.G_EXC_ERROR;
735 ELSE
736 x_order_header_rec.operation := OE_Globals.G_OPR_UPDATE;
737 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
738 Ibe_util.Debug('Return not booked loop: ' ||x_order_header_rec.operation);
739 END IF;
740 END IF;
741
742 ELSIF (x_order_header_rec.ORDER_CATEGORY_CODE) = 'RETURN' THEN
743
744 GetActiveReturnOrder(p_cust_acct_id => x_order_header_rec.sold_to_org_id
745 ,p_curr_code => x_order_header_rec.transactional_curr_code
746 ,p_party_id => p_party_id
747 ,x_order_header_id => l_order_header_id);
748
749 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
750 Ibe_util.Debug('Active pending Return: '||l_order_header_id);
751 END IF;
752
753 IF(l_order_header_id is null OR l_order_header_id = FND_API.G_MISS_NUM) THEN
754
755 IF (p_save_type = SAVE_ADDITEMS) THEN
756
757 x_order_header_rec.header_id := l_order_header_id;
758 x_order_header_rec.operation := OE_Globals.G_OPR_CREATE;
759
760 /** -- As the flow is for CREATE do set the necessary attributes -- **/
761
762 IF (x_order_header_rec.order_type_id is null OR
763 x_order_header_rec.order_type_id = FND_API.G_MISS_NUM)
764 then
765 x_order_header_rec.order_type_id := FND_PROFILE.VALUE('IBE_RETURN_TRANSACTION_TYPE');
766 IF (x_order_header_rec.order_type_id IS NULL OR x_order_header_rec.order_type_id = FND_API.G_MISS_NUM)
767 THEN
768 FND_Message.Set_Name('IBE', 'IBE_ERR_OT_ORDER_TYPE_ID_MISS');
769 FND_Msg_Pub.Add;
770 RAISE FND_API.G_EXC_ERROR;
771 END IF;
772 end if; -- if order type id
773
774 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
775 Ibe_util.Debug('Order Type Id: ' ||x_order_header_rec.order_type_id);
776 END IF;
777
778 -- Get the User's Session ORG_ID
779 l_user_orgid := mo_global.GET_CURRENT_ORG_ID();
780 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
781 Ibe_util.Debug('Current Org id : ' ||l_user_orgid);
782 END IF;
783
784 -- Get the Sales Rep Number from the ASO Utility package
785 l_salesrep_number := ASO_UTILITY_PVT.GET_OU_ATTRIBUTE_VALUE(aso_utility_pvt.GET_DEFAULT_SALESREP,l_user_orgid);
786 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
787 Ibe_util.Debug('Sales Rep Number for Current Org : ' ||l_salesrep_number);
788 END IF;
789 -- Bug 5255625, Proper error message when default salesrep is not set
790 IF (l_salesrep_number is null) THEN
791 FND_Message.Set_Name('IBE', 'IBE_ERR_OT_SALESREPID_MISS');
792 FND_Msg_Pub.Add;
793 RAISE FND_API.G_EXC_ERROR;
794 ELSE
795 -- Get the sales rep id from the sales rep number using the JTF table
796 select SALESREP_ID into l_salesrep_id from JTF_RS_SALESREPS where SALESREP_NUMBER = l_salesrep_number and ORG_ID = l_user_orgid;
797 END IF;
798
799 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
800 Ibe_util.Debug('Sales Rep Id : ' ||l_salesrep_id);
801 END IF;
802 --l_salesrep_id := FND_PROFILE.VALUE('ASO_DEFAULT_PERSON_ID');
803
804 IF (l_salesrep_id is null) THEN
805 FND_Message.Set_Name('IBE', 'IBE_ERR_OT_SALESREPID_MISS');
806 FND_Msg_Pub.Add;
807 RAISE FND_API.G_EXC_ERROR;
808 ELSE
809 x_order_header_rec.salesrep_id := l_salesrep_id;
810 END IF;
811 x_order_header_rec.source_document_type_id := 13; -- iStore Account
812
813 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
814 Ibe_util.Debug('OM::Get_Code_Release_Level: ' ||OE_CODE_CONTROL.Get_Code_Release_Level);
815 END IF;
816
817 l_preApprove_prof := FND_PROFILE.VALUE('IBE_ENABLE_RETURN_PREBOOK');
818 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
819 Ibe_util.Debug('Enable Pre-Approval Profile value: ' ||l_preApprove_prof);
820 END IF;
821
822 IF (x_order_header_rec.order_category_code = 'RETURN' AND
823 OE_CODE_CONTROL.Get_Code_Release_Level > '110509' AND
824 l_preApprove_prof = 'Y') THEN
825 x_order_header_rec.flow_status_code := 'WORKING';
826 END IF;
827
828 ELSIF(p_save_type = SAVE_NORMAL OR p_save_type = SAVE_REMOVEITEMS) THEN
829 FND_Message.Set_Name('IBE', 'IBE_ERR_OT_REFRESH_RETURN');
830 FND_Msg_Pub.Add;
831 RAISE FND_API.G_EXC_ERROR;
832 END If; --save type= normal
833 ELSE
834
835 -- This flow will be reached when there is aleady an existing active pending return
836 -- and the user tries to create a return without sending active pending return's HeaderId
837 -- in oe_order_header_rec.header_id. So here defaulting should be done from existing active pending
838 -- return's header record.
839
840 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
841 IBE_Util.Debug('Inside Header Record update defaulting flow');
842 END IF;
843 x_order_header_rec.header_id := l_order_header_id;
844 open c_defaulthdr_rec(l_order_header_id);
845 fetch c_defaulthdr_rec into l_ordertype_id;
846 close c_defaulthdr_rec;
847 if (x_order_header_rec.order_type_id is null or
848 x_order_header_rec.order_type_id = FND_API.G_MISS_NUM)
849 then
850 x_order_header_rec.order_type_id := l_ordertype_id;
851 end if;
852 x_order_header_rec.operation := OE_Globals.G_OPR_UPDATE;
853
854 END IF; -- if order_header_id
855
856 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
857 Ibe_util.Debug('Operation: ' ||x_order_header_rec.operation);
858 END IF;
859
860 ELSIF (x_order_header_rec.ORDER_CATEGORY_CODE) = 'ORDER' OR (x_order_header_rec.ORDER_CATEGORY_CODE) = 'MIXED'
861 THEN
862 -- For future enhancements.
863 null;
864 END IF; --IF ORDER_CATEG_CODE
865
866 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
867 Ibe_util.Debug('End IBE_ORDER_SAVE_PVT:DefaultHeaderRecord');
868 END IF;
869
870 END DefaultHeaderRecord;
871
872
873 PROCEDURE CancelOrder(
874 p_order_header_rec IN OE_Order_PUB.Header_Rec_Type
875 ,x_order_header_rec OUT NOCOPY OE_Order_PUB.Header_Rec_Type
876 )
877 IS
878 cursor c_get_hdrInfo(l_hrdId Number) is
879 select flow_status_code, order_category_code from oe_order_headers_all where header_id = l_hrdId;
880
881 l_header_rec c_get_hdrInfo%rowtype;
882 l_flow_status VARCHAR2(30);
883 l_order_category_code VARCHAR2(30);
884
885 BEGIN
886
887 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
888 Ibe_util.Debug('Begin IBE_Order_Save_Pvt.CancelOrder');
889 END IF;
890
891 x_order_header_rec := p_order_header_rec;
892
893 for l_header_rec in c_get_hdrInfo(x_order_header_rec.header_id)
894 loop
895 -- exit when c_get_hdrInfo%notfound;
896 x_order_header_rec.FLOW_STATUS_CODE := l_header_rec.FLOW_STATUS_CODE;
897 x_order_header_rec.ORDER_CATEGORY_CODE := l_header_rec.ORDER_CATEGORY_CODE;
898 end loop;
899
900 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
901 Ibe_util.Debug('CancelOrder - FLOW_STATUS_CODE: '||x_order_header_rec.FLOW_STATUS_CODE);
902 Ibe_util.Debug('CancelOrder - ORDER_CATEGORY_CODE: '||x_order_header_rec.ORDER_CATEGORY_CODE);
903 END IF;
904
905 IF (x_order_header_rec.FLOW_STATUS_CODE in ('ENTERED','WORKING') AND
906 x_order_header_rec.ORDER_CATEGORY_CODE = 'RETURN') THEN
907
908 x_order_header_rec.operation := OE_Globals.G_OPR_DELETE;
909 x_order_header_rec.cancelled_flag := FND_API.G_MISS_CHAR;
910
911 ELSE
912 x_order_header_rec.operation := OE_Globals.G_OPR_UPDATE;
913 x_order_header_rec.cancelled_flag := 'Y';
914 -- x_order_header_rec.change_reasons := p_order_header_rec.change_reasons;
915 -- x_order_header_rec.change_comments := p_order_header_rec.change_comments;
916
917 END IF;
918
919
920 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
921 Ibe_util.Debug('End IBE_ORDER_SAVE_PVT:CancelOrder');
922 END IF;
923
924 END CancelOrder;
925
926 PROCEDURE DefaultHdrLineAddress(
927 p_order_line_tbl IN OE_Order_PUB.Line_Tbl_Type
928 ,p_order_header_rec IN OE_Order_PUB.Header_Rec_Type
929 ,p_party_id IN NUMBER:= FND_API.G_MISS_NUM
930 ,p_shipto_partysite_id IN NUMBER:= FND_API.G_MISS_NUM
931 ,p_billto_partysite_id IN NUMBER:= FND_API.G_MISS_NUM
932 ,x_order_header_rec OUT NOCOPY OE_Order_PUB.Header_Rec_Type
933 ,x_order_line_tbl OUT NOCOPY OE_Order_PUB.Line_Tbl_Type
934 ,x_return_status OUT NOCOPY VARCHAR2
935 ,x_msg_count OUT NOCOPY NUMBER
936 ,x_msg_data OUT NOCOPY VARCHAR2
937 )
938 IS
939
940 cursor c_get_lineInfo(l_lineId Number) is
941 select invoice_to_org_id,ship_to_org_id from oe_order_lines_all where line_id = l_lineId;
942
943 l_line_rec c_get_lineInfo%rowtype;
944
945 cursor c_party_type(cpt_party_id NUMBER) is
946 select party_type from HZ_PARTIES where party_id = cpt_party_id;
947
948 l_party_type VARCHAR2(30);
949 lx_header_siteuse_id NUMBER;
950 l_api_name varchar2(40) := 'DefaultHdrLineAddress';
951
952 l_cust_acct_role_id NUMBER;
953 l_custacct_site_id NUMBER;
954 l_cust_acct_id NUMBER;
955 l_party_id NUMBER;
956
957 BEGIN
958
959 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
960 Ibe_util.Debug('Begin IBE_ORDER_SAVE_PVT:DefaultHdrLineAddress');
961 end if;
962
963 -- Initialize API return status to success
964 x_return_status := FND_API.G_RET_STS_SUCCESS;
965
966 x_order_header_rec := p_order_header_rec;
967
968
969 for lineIdx in 1..p_order_line_tbl.count
970 loop
971 x_order_line_tbl(lineIdx) := p_order_line_tbl(lineIdx);
972 end loop;
973
974 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
975 Ibe_util.Debug('Inside DefaultHdrLineAddress party_id' ||p_party_id);
976 END IF;
977
978 OPEN c_party_type(p_party_id);
979 FETCH c_party_type into l_party_type;
980 CLOSE c_party_type;
981 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
982 Ibe_util.Debug('DefaultHdrLineAddress l_party_type'||l_party_type);
983 END IF;
984
985 -- No need to default any address for line level because OE always
986 -- override the values at line level referencing from original order lines.
987
988 /********* B2B LineLevel **********/
989 /* Invoice to Org Id and ShipTo Org Id will be populated
990 from the referenced Order Lines. */
991
992
993 IF (l_party_type = 'PARTY_RELATIONSHIP') THEN
994 for j in 1..x_order_line_tbl.count
995 loop
996 for l_line_rec in c_get_lineInfo(x_order_line_tbl(j).return_attribute2)
997 loop
998 x_order_line_tbl(j).ship_to_org_id := l_line_rec.SHIP_TO_ORG_ID;
999 x_order_line_tbl(j).invoice_to_org_id := l_line_rec.INVOICE_TO_ORG_ID;
1000 end loop;
1001 end loop;
1002 END IF;
1003
1004 /********* B2B and B2C Header Level **********/
1005
1006 -- Call Get_Cust_Account_Site_Use to retrieve ship_to_org_id /invoice_to_org_id.
1007 -- Now start for 'SHIP_TO_ORG_ID'.
1008
1009
1010 IBE_CUSTOMER_ACCT_PVT.Get_Cust_Account_Site_Use(
1011 p_cust_acct_id => x_order_header_rec.sold_to_org_id
1012 ,p_party_id => p_party_id
1013 ,p_siteuse_type => 'SHIP_TO'
1014 ,p_partysite_id => p_shipto_partysite_id
1015 ,x_siteuse_id => lx_header_siteuse_id
1016 ,x_return_status => x_return_status
1017 ,x_msg_data => x_msg_data
1018 ,x_msg_count => x_msg_count
1019 );
1020
1021 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1022 Ibe_util.Debug('DefaultHdrLineAddress: After call to Get_Cust_Acct_Site_Use-ShipTO '||x_return_status);
1023 END IF;
1024
1025
1026 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1027 -- raise FND_API.G_EXC_ERROR;
1028 FND_Message.Set_Name('IBE', 'IBE_ERR_OT_INVALID_SHIPTO_ADDR');
1029 FND_Msg_Pub.Add;
1030 RAISE FND_API.G_EXC_ERROR;
1031 END IF;
1032
1033 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1034 Ibe_util.Debug('DefaultHdrLineAddress: '||lx_header_siteuse_id);
1035 END IF;
1036
1037 IF (lx_header_siteuse_id is not null AND lx_header_siteuse_id <> FND_API.G_MISS_NUM)
1038 THEN
1039 x_order_header_rec.ship_to_org_id := lx_header_siteuse_id;
1040 ELSIF (l_party_type = 'PARTY_RELATIONSHIP') THEN
1041
1042 -- IF valid SiteUseId is not returned , final attempt, populate from the lines.
1043 -- ONLY FOR B2B flow
1044
1045 DefaultFromLineSiteId(p_line_tbl => x_order_line_tbl
1046 ,p_party_id => p_party_id
1047 ,p_siteuse_type => 'SHIP_TO'
1048 ,x_site_use_id => lx_header_siteuse_id
1049 );
1050
1051 IF (lx_header_siteuse_id is not null AND lx_header_siteuse_id <> FND_API.G_MISS_NUM)
1052 THEN
1053 x_order_header_rec.ship_to_org_id := lx_header_siteuse_id;
1054 ELSE
1055
1056
1057 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1058 Ibe_util.Debug('DefaultHdrLineAddress: Final Attempt- Ship To');
1059 END IF;
1060
1061 -- If final attempt also fails, no other go, Raise An Exception
1062 FND_Message.Set_Name('IBE', 'IBE_ERR_OT_INVALID_SHIPTO_ADDR');
1063 FND_Msg_Pub.Add;
1064 RAISE FND_API.G_EXC_ERROR; -- Error Message should indicate the
1065 -- user to select/ create a valid Shipping address
1066 -- in the profiles tab.
1067 END IF;
1068 ELSE
1069 -- FOR B2C raise an exception.
1070 FND_Message.Set_Name('IBE', 'IBE_ERR_OT_INVALID_SHIPTO_ADDR');
1071 FND_Msg_Pub.Add;
1072 RAISE FND_API.G_EXC_ERROR;
1073 END IF;
1074
1075 ----
1076 -- For 'INVOICE_TO_ORG_ID'
1077 ----
1078
1079 IBE_CUSTOMER_ACCT_PVT.Get_Cust_Account_Site_Use(
1080 p_cust_acct_id => x_order_header_rec.sold_to_org_id
1081 ,p_party_id => p_party_id
1082 ,p_siteuse_type => 'BILL_TO'
1083 ,p_partysite_id => p_billto_partysite_id
1084 ,x_siteuse_id => lx_header_siteuse_id
1085 ,x_return_status => x_return_status
1086 ,x_msg_data => x_msg_data
1087 ,x_msg_count => x_msg_count
1088 );
1089
1090
1091 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1092 Ibe_util.Debug('DefaultHdrLineAddress: After call to Get_Cust_Acct_Site_Use-BillTO '||x_return_status);
1093 END IF;
1094
1095 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1096 FND_Message.Set_Name('IBE', 'IBE_ERR_OT_INVALID_BILLTO_ADDR');
1097 FND_Msg_Pub.Add;
1098 RAISE FND_API.G_EXC_ERROR;
1099 END IF;
1100
1101 IF (lx_header_siteuse_id is not null AND lx_header_siteuse_id <> FND_API.G_MISS_NUM)
1102 THEN
1103 x_order_header_rec.invoice_to_org_id := lx_header_siteuse_id;
1104 ELSIF (l_party_type = 'PARTY_RELATIONSHIP') THEN
1105
1106 -- IF valid SiteUseId is not returned , final attempt, populate from the lines.
1107 -- ONLY FOR B2B USERS
1108
1109 DefaultFromLineSiteId(p_line_tbl => x_order_line_tbl
1110 ,p_party_id => p_party_id
1111 ,p_siteuse_type => 'BILL_TO'
1112 ,x_site_use_id => lx_header_siteuse_id
1113 );
1114
1115 IF (lx_header_siteuse_id is not null AND lx_header_siteuse_id <> FND_API.G_MISS_NUM)
1116 THEN
1117 x_order_header_rec.invoice_to_org_id := lx_header_siteuse_id;
1118 ELSE
1119 -- If final attempt also fails, no other go,Raise An Exception
1120 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1121 Ibe_util.Debug('DefaultHdrLineAddress: Final Attempt- Bill To');
1122 END IF;
1123
1124 FND_Message.Set_Name('IBE', 'IBE_ERR_OT_INVALID_BILLTO_ADDR');
1125 FND_Msg_Pub.Add;
1126 RAISE FND_API.G_EXC_ERROR; -- Error Message should indicate the
1127 -- user to select/ create a valid Shipping address
1128 -- in the profiles tab.
1129 END IF;
1130 ELSE
1131 -- FOR B2C raise an exception.
1132 FND_Message.Set_Name('IBE', 'IBE_ERR_OT_INVALID_BILLTO_ADDR');
1133 FND_Msg_Pub.Add;
1134 RAISE FND_API.G_EXC_ERROR;
1135 END IF;
1136
1137 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1138 Ibe_util.Debug('DefaultHdrLineAddress ship_to_org_id: ' || x_order_header_rec.ship_to_org_id);
1139 Ibe_util.Debug('DefaultHdrLineAddress bill_to_org_id: ' || x_order_header_rec.invoice_to_org_id);
1140 END IF;
1141
1142 /**************** B2C LineLevel *******************/
1143
1144 -- Not Required as OE always override this with orig. order lines.
1145 -- Populate from Header Record.
1146
1147
1148 -- New Changes
1149 -- Invoice To Contact Id:
1150 -- For Lines this would be always defaulted from referenced order lines by OE.(For creation)
1151 -- For Header this would be fetched from hz_cust_account_roles.cust_account_role_id.
1152 -- This flow is only for B2B users.
1153 -- A B2B customer can invoice the Order/Invoice to any of the contacts.
1154 -- But for B2C,it is always to him, the customer. No any contact will be present.
1155 -- This is the value to be populated in oe_order_headers_all.invoice_to_contact_id.
1156 -- How this value is retrieved ?
1157 -- This is hz_cust_account_roles.cust_account_role_id whose
1158 -- cust_acct_site_id = hz_cust_acct_sites_all.cust_acct_site_id and
1159 -- hz_cust_acct_sites_all.cust_acct_site_id = hz_cust_site_uses_all.cust_acct_site_id
1160 -- hz_cust_site_uses_all.site_use_code = 'BILL_TO' and site_use_id = <<InvoiceToOrgId>>.
1161 -- Also, If the address is defaulted from any of the valid lines,
1162 -- no need to populate the 'ContactId'.
1163 -- Why?
1164 -- The user placing the Return doesn't have any valid 'billTo' address for himself.
1165 -- In that case, we are trying to populate from any of the linelevel address.
1166 -- Since <<InvoiceToOrgId>> is used to fetch this 'ContactId', we dont need to
1167 -- populate the contactId related to this 'any' address selected from the order lines.
1168
1169
1170 --for the available site_use_id fetch cust_acct_id and party_id.
1171 Declare
1172 cursor c_cust_acct_id (lin_custacct_siteuse_id number, lin_siteuse_type varchar2)
1173 is
1174 select hca.cust_acct_site_id, hca.cust_account_id,hps.party_id
1175 from hz_cust_acct_sites hca,hz_cust_site_uses hcu,hz_party_sites hps
1176 where
1177 hcu.site_use_id = lin_custacct_siteuse_id
1178 and hcu.site_use_code = lin_siteuse_type
1179 and hcu.cust_acct_site_id = hca.cust_acct_site_id
1180 and hca.party_site_id = hps.party_site_id;
1181 begin
1182 open c_cust_acct_id(x_order_header_rec.invoice_to_org_id,'BILL_TO');
1183 fetch c_cust_acct_id into l_custacct_site_id,l_cust_acct_id,l_party_id;
1184 close c_cust_acct_id;
1185 end;
1186
1187 if(l_cust_acct_id = x_order_header_rec.sold_to_org_id
1188 AND l_party_id = p_party_id) then
1189
1190 IF (l_party_type = 'PARTY_RELATIONSHIP') THEN
1191 IBE_CUSTOMER_ACCT_PVT.Get_Cust_Acct_Role(
1192 p_party_id => p_party_id
1193 ,p_acctsite_type => 'BILL_TO'
1194 ,p_sold_to_orgid => x_order_header_rec.sold_to_org_id
1195 ,p_custacct_siteuse_id => x_order_header_rec.invoice_to_org_id
1196 ,x_cust_acct_role_id => l_cust_acct_role_id
1197 ,x_return_status => x_return_status
1198 ,x_msg_count => x_msg_count
1199 ,x_msg_data => x_msg_data
1200 );
1201
1202 x_order_header_rec.invoice_to_contact_id := l_cust_acct_role_id;
1203 END IF;
1204 end if;
1205
1206 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1207 Ibe_util.Debug('End IBE_ORDER_SAVE_PVT:DefaultHdrLineAddress');
1208 END IF;
1209
1210 FND_MSG_PUB.Count_And_Get
1211 ( p_count => x_msg_count,
1212 p_data => x_msg_data
1213 );
1214
1215 EXCEPTION
1216 WHEN FND_API.G_EXC_ERROR THEN
1217 x_return_status := FND_API.G_RET_STS_ERROR;
1218 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
1219 p_count => x_msg_count ,
1220 p_data => x_msg_data);
1221 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1222 IBE_Util.Debug('Error IBE_ORDER_SAVE_PVT:DefaultHdrLineAddress()'|| sqlerrm);
1223 END IF;
1224
1225 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1226 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1227 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
1228 p_count => x_msg_count ,
1229 p_data => x_msg_data);
1230 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1231 IBE_Util.Debug('UNEXPError IBE_ORDER_SAVE_PVT:DefaultHdrLineAddress()' || sqlerrm);
1232 END IF;
1233
1234 WHEN OTHERS THEN
1235 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1236 IF FND_Msg_Pub.Check_Msg_Level( FND_Msg_Pub.G_MSG_LVL_UNEXP_ERROR ) THEN
1237 FND_Msg_Pub.Add_Exc_Msg(G_PKG_NAME,
1238 L_API_NAME);
1239 END IF;
1240
1241 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
1242 p_count => x_msg_count ,
1243 p_data => x_msg_data);
1244 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1245 IBE_Util.Debug('Other IBE_ORDER_SAVE_PVT:DefaultHdrLineAddress()' || sqlerrm);
1246 END IF;
1247
1248 END DefaultHdrLineAddress;
1249
1250
1251 PROCEDURE SetLineShipInvoiceIds(
1252 p_order_header_rec IN OE_Order_PUB.Header_Rec_Type := OE_Order_PUB.G_MISS_HEADER_REC,
1253 p_order_line_tbl IN OE_Order_PUB.Line_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_TBL,
1254 x_order_line_tbl OUT NOCOPY OE_Order_PUB.Line_Tbl_Type
1255 )
1256 IS
1257
1258 Cursor c_get_hdr_ids(cl_hdr_id number) IS
1259 select ship_to_org_id,invoice_to_org_id from oe_order_headers_all where header_id = cl_hdr_id;
1260
1261 l_get_hdr_ids c_get_hdr_ids%rowtype;
1262 l_order_line_tbl OE_Order_PUB.Line_Tbl_Type;
1263 l_shipto_org_id NUMBER;
1264 l_invoiceto_org_id NUMBER;
1265 l_db_shipto_org_id NUMBER;
1266 l_db_invoiceto_org_id NUMBER;
1267 l_linetbl_count NUMBER := 0;
1268 p_lineids_set VARCHAR2(3000) := null;
1269 l_all_lineids_query VARCHAR2(3000);
1270 l_linetmp_qry VARCHAR2(4000);
1271 l_lineid NUMBER;
1272 l_parseNum NUMBER :=5;
1273 l_parseKey varchar2(40) :='ORDER_SAVE_INVOICE_IDS';
1274
1275 Type lineid_type is REF CURSOR;
1276 lineid_tmp lineid_type;
1277
1278 BEGIN
1279
1280 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1281 IBE_Util.Debug('Begin IBE_ORDER_SAVE_PVT:setlineshipinvoiceids');
1282 IBE_Util.Debug('setlineshipinvoiceids invoicetoorgid: '|| p_order_header_rec.invoice_to_org_id);
1283 END IF;
1284
1285 for i in 1..p_order_line_tbl.count
1286 loop
1287 l_order_line_tbl(i) := p_order_line_tbl(i);
1288 end loop;
1289
1290 -- This API will be called always for any updations in the Return for a B2C user.
1291 -- IF any of the existing lines are updated,
1292 -- those Lines would be present in the LineTbl for UPDATE operations.
1293
1294 -- Other Lines which are not updated, i/e those present in the Return Order but
1295 -- didn't updated from UI:
1296 -- These lines would be fetched from DB and for them also InvoiceToOrgId would be updated.
1297
1298 -- Bug# 3334581:
1299 -- As per the impact analysis done for this bug, ShipToOrgId would not be
1300 -- propagated for the Return Order Lines from the Return HEader.
1301 -- So hopToOrgIds fro Return Order Lines for B2C also
1302 -- would remain same as Original Order Lines.
1303 -- So all ShipToorgId assignments are commented down.
1304
1305 -- J
1306 -- Uncommenting all the above lines since the bug has been fixed by OM as per Bug# 3336052
1307 -- Prereq for this is ONT.J
1308
1309 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1310 IBE_Util.Debug('Begin IBE_ORDER_SAVE_PVT:setlineshipinvoiceids');
1311 IBE_Util.Debug('setlineshipinvoiceids invoicetoorgid: '|| p_order_header_rec.invoice_to_org_id);
1312 END IF;
1313
1314 l_shipto_org_id := p_order_header_rec.ship_to_org_id; --Bug# 3334581
1315 l_invoiceto_org_id := p_order_header_rec.invoice_to_org_id;
1316
1317 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1318 IBE_Util.Debug('Order header_id: '|| p_order_header_rec.header_id);
1319 END IF;
1320
1321 for l_get_hdr_ids in c_get_hdr_ids(p_order_header_rec.header_id)
1322 loop
1323 l_db_shipto_org_id := l_get_hdr_ids.ship_to_org_id; --Bug# 3334581
1324 l_db_invoiceto_org_id := l_get_hdr_ids.invoice_to_org_id;
1325 end loop;
1326
1327 -- Bug# 3334581
1328 IF (l_shipto_org_id is null OR l_shipto_org_id = FND_API.G_MISS_NUM)
1329 THEN
1330 l_shipto_org_id := l_db_shipto_org_id;
1331 END IF;
1332
1333
1334 IF (l_invoiceto_org_id is null OR l_invoiceto_org_id = FND_API.G_MISS_NUM)
1335 THEN
1336 l_invoiceto_org_id := l_db_invoiceto_org_id;
1337 END IF;
1338
1339 for i in 1..l_order_line_tbl.count
1340 loop
1341 l_order_line_tbl(i).ship_to_org_id := l_shipto_org_id;
1342 l_order_line_tbl(i).invoice_to_org_id := l_invoiceto_org_id;
1343 end loop;
1344
1345
1346 -- fetch line ids NOT IN the set from oe_order_lines _all
1347 -- basically to update the other existing lines also.
1348
1349 l_linetbl_count := l_order_line_tbl.count;
1350 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1351 IBE_Util.Debug('No. of lines to be updated: '|| l_linetbl_count);
1352 END IF;
1353
1354 for lineIdx in 1..l_order_line_tbl.count
1355 loop
1356 p_lineids_set := p_lineids_set || ','|| l_order_line_tbl(lineIdx).line_id;
1357 end loop;
1358
1359 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1360 IBE_Util.Debug('Line Ids to be parsed: '|| p_lineids_set);
1361 END IF;
1362
1363 IBE_LEAD_IMPORT_PVT.parseInput (p_lineids_set, 'CHAR', l_parseKey, l_parseNum, l_all_lineids_query);
1364
1365 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1366 IBE_Util.Debug('After calling Lead_Import package');
1367 END IF;
1368
1369 l_linetmp_qry := 'select line_id from oe_order_lines_all '||
1370 'where header_id= :1 and '||
1371 'line_id NOT IN('|| l_all_lineids_query ||')';
1372
1373 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1374 IBE_Util.Debug('Line qry to be executed: '|| l_linetmp_qry);
1375 END IF;
1376
1377 open lineid_tmp for l_linetmp_qry using p_order_header_rec.header_id,l_parseKey;
1378 loop
1379 fetch lineid_tmp into l_lineid;
1380 exit when lineid_tmp%notfound;
1381 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1382 IBE_Util.debug('line id from line: '|| l_lineid);
1383 end if;
1384 l_linetbl_count := l_linetbl_count +1;
1385 l_order_line_tbl(l_linetbl_count) := OE_Order_PUB.G_MISS_LINE_REC;
1386 l_order_line_tbl(l_linetbl_count).line_id := l_lineid;
1387 l_order_line_tbl(l_linetbl_count).operation := OE_Globals.G_OPR_UPDATE;
1388 l_order_line_tbl(l_linetbl_count).invoice_to_org_id := l_invoiceto_org_id;
1389 l_order_line_tbl(l_linetbl_count).ship_to_org_id := l_shipto_org_id;
1390 end loop;
1391 close lineid_tmp;
1392
1393
1394 for j in 1..l_order_line_tbl.count
1395 loop
1396 x_order_line_tbl(j) := l_order_line_tbl(j);
1397 end loop;
1398
1399 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1400 IBE_Util.Debug('End OE_ORDER_SAVE_PVT:setlineshipinvoiceids');
1401 END IF;
1402
1403 END SetLineShipInvoiceIds;
1404
1405
1406
1407 /******* NEW API introduced for MDL Item related operation for RETURNS. ***/
1408 -- Whenever a MDL item is updated, then the chng would be propagated to
1409 -- all the children belong to the parent.
1410 -- If a Model parent is deleted then all related children alos would be deleted.
1411 -- IMPORTANT NOTE:
1412 -- WHEN THIS API IS REMOVED FOR ANY FUTURE ENHANCEMENTS,
1413 -- ENSURE THAT ITEMTYPECODE IS NOT PASSED FROM UI LAYER.
1414 -- NOW MODEL/KIT ITEMS ARE STORED AS STANDARD IN OE.
1415 -- SO IT CANT BE OVERWRITTEN WHICH CREATES UNNECESSARY PBM WHILE BOOKING THE RETURN.
1416 -- UNTIL OE UPTAKES MODEL/CHILDEN RELATION IN RETURN ORDERS DONT OVERRIDE.
1417
1418 Procedure SaveMDLRelatedOperations(p_context_type IN VARCHAR2,
1419 p_order_line_tbl IN OE_Order_PUB.Line_Tbl_Type,
1420 p_order_header_id IN NUMBER,
1421 p_save_type IN VARCHAR2:=FND_API.G_MISS_CHAR,
1422 x_order_line_tbl OUT NOCOPY OE_Order_PUB.Line_Tbl_Type
1423 )
1424 IS
1425
1426 cursor l_childlineid_cur(c_mdlop_lineid Number, c_modop_hdr_id number) is
1427 select line_id,ordered_quantity,line_category_code,header_id
1428 from oe_order_lines_all
1429 where ('ORDER',return_attribute1,return_attribute2) in
1430 (select line_category_code,header_id,line_id
1431 from oe_order_lines_all
1432 where ('RETURN',header_id, top_model_line_id) in
1433 (select line_category_code,return_attribute1,return_attribute2
1434 from oe_order_lines_all
1435 where line_id= c_mdlop_lineid)
1436 and link_to_line_id is not null)
1437 and header_id = c_modop_hdr_id;
1438
1439 -- last condn is added becos same item could have been returned twice in diffrt retns.
1440
1441 cursor l_tmpparent_cur(l_mdlop_line_id number) is
1442 select ordered_quantity from oe_order_lines_all
1443 where line_id = l_mdlop_line_id;
1444
1445 cursor c_linetmp_cur(c_linetmp_id number) is
1446 Select item_type_code from oe_order_lines_all where line_id =
1447 (Select return_attribute2 from oe_order_lines_all where line_id = c_linetmp_id);
1448
1449 l_tmpparent_rec l_tmpparent_cur%rowtype;
1450 l_childlineid_rec l_childlineid_cur%rowtype;
1451 c_linetmp_rec c_linetmp_cur%rowtype;
1452 l_order_line_tbl OE_Order_PUB.Line_Tbl_Type;
1453 l_origparent_qty NUMBER;
1454 l_origchild_qty NUMBER;
1455 l_new_qty NUMBER;
1456 l_linetbl_count NUMBER;
1457 l_line_type VARCHAR2(30) := '';
1458
1459 BEGIN
1460
1461 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1462 IBE_Util.Debug('OE_Order_Save_PVT.SaveMDLRelatedOperations -BEGIN');
1463 IBE_Util.Debug('p_context_type - p_save_type: ' ||p_context_type ||' :: '||p_save_type);
1464 END IF;
1465
1466 for i in 1.. p_order_line_tbl.COUNT
1467 loop
1468 l_order_line_tbl(i) := p_order_line_tbl(i);
1469 end loop;
1470
1471 l_linetbl_count := (l_order_line_tbl.count);
1472 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1473 IBE_Util.Debug('l_linetbl_count: '||l_linetbl_count);
1474 END IF;
1475
1476 if p_context_type = 'SAVE' and p_save_type =SAVE_REMOVEITEMS then
1477 for i in 1..l_order_line_tbl.count
1478 loop
1479
1480 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1481 IBE_Util.Debug('item_type_code: ' || l_order_line_tbl(i).item_type_code);
1482 END IF;
1483
1484 if (l_order_line_tbl(i).ITEM_TYPE_CODE IN ('MODEL','KIT'))
1485 then
1486 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1487 IBE_Util.Debug('Inside MDL check loop');
1488 END IF;
1489 for l_childlineid_rec in l_childlineid_cur(l_order_line_tbl(i).LINE_ID,p_order_header_id)
1490 loop
1491 l_linetbl_count := l_linetbl_count+1;
1492 l_order_line_tbl(l_linetbl_count) := OE_Order_PUB.G_MISS_LINE_REC;
1493 l_order_line_tbl(l_linetbl_count).LINE_ID := l_childlineid_rec.LINE_ID;
1494 l_order_line_tbl(l_linetbl_count).OPERATION := OE_Globals.G_OPR_DELETE;
1495 end loop;
1496 end if;
1497 end loop;
1498
1499 elsif (p_context_type = 'SAVE' AND
1500 (p_save_type = SAVE_ADDITEMS OR p_save_type =SAVE_NORMAL)) then
1501
1502 for i in 1..l_order_line_tbl.count
1503 loop
1504
1505 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1506 IBE_Util.Debug('item_type_code: ' || l_order_line_tbl(i).item_type_code);
1507 END IF;
1508
1509 if (l_order_line_tbl(i).item_type_code IN ('MODEL','KIT'))
1510 then
1511 for l_tmpparent_rec in l_tmpparent_cur(l_order_line_tbl(i).LINE_ID)
1512 loop
1513 l_origparent_qty :=l_tmpparent_rec.ORDERED_QUANTITY;
1514 end loop;
1515 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1516 IBE_Util.Debug('l_origparent_qty: ' || l_origparent_qty);
1517 END IF;
1518
1519 for l_childlineid_rec in l_childlineid_cur(l_order_line_tbl(i).LINE_ID,p_order_header_id)
1520 loop
1521 l_linetbl_count := l_linetbl_count+1;
1522 l_order_line_tbl(l_linetbl_count) := OE_Order_PUB.G_MISS_LINE_REC;
1523 l_order_line_tbl(l_linetbl_count).LINE_ID := l_childlineid_rec.LINE_ID;
1524 l_order_line_tbl(l_linetbl_count).OPERATION := OE_Globals.G_OPR_UPDATE;
1525
1526 if(l_order_line_tbl(i).ORDERED_QUANTITY is not null
1527 AND l_order_line_tbl(i).ORDERED_QUANTITY <> FND_API.G_MISS_NUM)
1528 then
1529 l_new_qty := l_order_line_tbl(i).ORDERED_QUANTITY;
1530 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1531 IBE_Util.Debug('Incoming qty: ' || l_new_qty);
1532 END IF;
1533 l_origchild_qty := l_childlineid_rec.ORDERED_QUANTITY;
1534 IF (l_origparent_qty = 0) THEN
1535 l_order_line_tbl(l_linetbl_count).ORDERED_QUANTITY
1536 := l_new_qty;
1537 END IF;
1538 IF (l_origchild_qty = 0) THEN
1539 l_order_line_tbl(l_linetbl_count).ORDERED_QUANTITY
1540 := l_new_qty;
1541 END IF;
1542 IF (l_origparent_qty <> 0) AND (l_origchild_qty <> 0) THEN
1543 l_order_line_tbl(l_linetbl_count).ORDERED_QUANTITY
1544 := (l_origchild_qty/l_origparent_qty)* l_new_qty;
1545 END IF;
1546 end if;
1547 if(l_order_line_tbl(i).RETURN_REASON_CODE is not null
1548 AND l_order_line_tbl(i).RETURN_REASON_CODE <> FND_API.G_MISS_CHAR)
1549 then
1550 l_order_line_tbl(l_linetbl_count).RETURN_REASON_CODE := l_order_line_tbl(i).RETURN_REASON_CODE;
1551 end if;
1552 end loop;
1553 end if;
1554 end loop;
1555
1556 elsif p_context_type='UPDATELINES' then
1557
1558 for k in 1..l_order_line_tbl.count
1559 loop
1560 -- Check whether this line is a model parent
1561 for c_linetmp_rec in c_linetmp_cur(l_order_line_tbl(k).line_id)
1562 loop
1563 l_line_type := c_linetmp_rec.ITEM_TYPE_CODE;
1564 end loop;
1565
1566 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1567 IBE_Util.Debug('l_line_type: ' || l_line_type);
1568 END IF;
1569
1570 -- if model item then propagate contactIds and OrgIds to all children
1571 if (l_line_type IN ('MODEL','KIT')) then
1572 for l_childlineid_rec in l_childlineid_cur(l_order_line_tbl(k).LINE_ID,p_order_header_id)
1573 loop
1574 l_linetbl_count := l_linetbl_count+1;
1575 l_order_line_tbl(l_linetbl_count) := OE_Order_PUB.G_MISS_LINE_REC;
1576 l_order_line_tbl(l_linetbl_count).LINE_ID := l_childlineid_rec.LINE_ID;
1577 l_order_line_tbl(l_linetbl_count).OPERATION := OE_Globals.G_OPR_UPDATE;
1578
1579 if(l_order_line_tbl(k).ship_to_contact_id is not null AND
1580 l_order_line_tbl(k).ship_to_contact_id <> FND_API.G_MISS_NUM) then
1581 l_order_line_tbl(l_linetbl_count).ship_to_contact_id
1582 := l_order_line_tbl(k).ship_to_contact_id;
1583 end if;
1584 if(l_order_line_tbl(k).ship_to_org_id is not null AND
1585 l_order_line_tbl(k).ship_to_org_id <> FND_API.G_MISS_NUM) then
1586 l_order_line_tbl(l_linetbl_count).ship_to_org_id
1587 := l_order_line_tbl(k).ship_to_org_id;
1588 end if;
1589 if(l_order_line_tbl(k).invoice_to_contact_id is not null AND
1590 l_order_line_tbl(k).invoice_to_contact_id <> FND_API.G_MISS_NUM) then
1591 l_order_line_tbl(l_linetbl_count).invoice_to_contact_id
1592 := l_order_line_tbl(k).invoice_to_contact_id;
1593 end if;
1594 if(l_order_line_tbl(k).invoice_to_org_id is not null AND
1595 l_order_line_tbl(k).invoice_to_org_id <> FND_API.G_MISS_NUM) then
1596 l_order_line_tbl(l_linetbl_count).invoice_to_org_id
1597 := l_order_line_tbl(k).invoice_to_org_id;
1598 end if;
1599 end loop;
1600 end if;
1601
1602 end loop; --main for loop
1603 end if; --main if
1604
1605 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1606 IBE_Util.Debug('l_order_line_tbl.count: '||l_order_line_tbl.count);
1607 END IF;
1608
1609 for k in 1..l_order_line_tbl.count
1610 loop
1611 x_order_line_tbl(k) := l_order_line_tbl(k);
1612 x_order_line_tbl(k).item_type_code := FND_API.G_MISS_CHAR;
1613 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1614 IBE_Util.Debug('x_order_line_tbl.LineId: ' || x_order_line_tbl(k).LINE_ID);
1615 IBE_Util.Debug('x_order_line_tbl.orderd_qty: ' || x_order_line_tbl(k).ORDERED_QUANTITY);
1616 IBE_Util.Debug('x_order_line_tbl.reason_code: ' || x_order_line_tbl(k).RETURN_REASON_CODE);
1617 IBE_Util.Debug('x_order_line_tbl.invoice contact: ' || x_order_line_tbl(k).INVOICE_TO_CONTACT_ID);
1618 IBE_Util.Debug('x_order_line_tbl.invoice org: ' || x_order_line_tbl(k).INVOICE_TO_ORG_ID);
1619 END IF;
1620 end loop;
1621
1622 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1623 IBE_Util.Debug('OE_Order_Save_PVT.SaveMDLRelatedOperations -END');
1624 END IF;
1625
1626 END SaveMDLRelatedOperations;
1627
1628 PROCEDURE ValidateOrderAccess(p_order_header_id IN NUMBER
1629 ,x_return_status OUT NOCOPY VARCHAR2
1630 )
1631
1632 IS
1633
1634 cursor c_createdby_info(l_acc_hdr_id NUMBER) IS
1635 select created_by from oe_order_headers_all where header_id=l_acc_hdr_id;
1636
1637 l_env_user_id NUMBER :=FND_GLOBAL.USER_ID;
1638 l_db_user_id NUMBER;
1639
1640 BEGIN
1641
1642 IF (IBE_UTIL.G_DEBUGON = l_true) then
1643 IBE_UTIL.DEBUG('Order_Save_Pvt: ValidateOrderAccess - BEGIN');
1644 IBE_UTIL.DEBUG('User id obtained from environment is: '||l_env_user_id);
1645 IBE_UTIL.DEBUG('Incoming Header Id: '||p_order_header_id);
1646 END IF;
1647
1648 -- Initialize API return status to success
1649 x_return_status := FND_API.G_RET_STS_SUCCESS;
1650
1651 open c_createdby_info(p_order_header_id);
1652 fetch c_createdby_info into l_db_user_id;
1653 close c_createdby_info;
1654
1655 IF (IBE_UTIL.G_DEBUGON = l_true) then
1656 IBE_UTIL.DEBUG('User id obtained from db is: '|| l_db_user_id);
1657 END IF;
1658
1659 IF (l_db_user_id <> l_env_user_id)
1660 THEN
1661 IF (IBE_UTIL.G_DEBUGON = l_true) then
1662 IBE_UTIL.DEBUG('Inside If');
1663 END IF;
1664 FND_Message.Set_Name('IBE', 'IBE_OT_ERR_USERACCESS');
1665 FND_Msg_Pub.Add;
1666 RAISE FND_API.G_EXC_ERROR;
1667 END IF;
1668
1669 IF (IBE_UTIL.G_DEBUGON = l_true) then
1670 IBE_UTIL.DEBUG('Order_Save_Pvt: ValidateOrderAccess - END');
1671 END IF;
1672
1673 END ValidateOrderAccess;
1674
1675 -- New API added for bug#3240077.
1676 PROCEDURE CheckOverReturnQty(
1677 p_order_header_id IN NUMBER
1678 ,x_qtyfail_LineIds OUT NOCOPY x_qtyfail_LineType
1679 ,x_return_status OUT NOCOPY VARCHAR2
1680 ,x_msg_count OUT NOCOPY NUMBER
1681 ,x_msg_data OUT NOCOPY VARCHAR2
1682 )
1683 IS
1684
1685 CURSOR QtyChkLineCur(qtychk_hdrid NUMBER) IS
1686 SELECT Line_id,inventory_item_id,reference_line_id,ordered_quantity,
1687 line_number,return_attribute1,return_attribute2
1688 FROM oe_order_lines_all
1689 WHERE header_id=qtychk_hdrid
1690 ORDER BY line_number;
1691
1692
1693 QtyChkLineRec QtyChkLineCur%rowtype;
1694 l_order_line_tbl OE_Order_PUB.Line_Tbl_Type;
1695
1696 l_api_name VARCHAR2(50) := 'CheckOverReturnQty';
1697 l_api_version NUMBER := 1.0;
1698 lineTblCnt NUMBER := 0;
1699 x_error_tbl OE_RMA_GRP.OVER_RETURN_ERR_TBL_TYPE;
1700 l_order_id VARCHAR2(240);
1701 l_part_number VARCHAR2(2000);
1702 l_hdr_id NUMBER;
1703 l_order_number NUMBER;
1704 p_tmp_error_lineIds VARCHAR2(3000):=null;
1705 l_tmp_error_lineIds VARCHAR2(3000);
1706 l_qty_tmp_query VARCHAR2(4000);
1707 l_tmp_index NUMBER;
1708 l_index NUMBER;
1709 l_tmp_index1 NUMBER;
1710 l_index1 NUMBER;
1711 l_index2 NUMBER;
1712
1713 TYPE l_tmp_qtychk_line_rec IS RECORD
1714 ( order_number NUMBER
1715 , ordered_item VARCHAR2(2000)
1716 , item_type_code VARCHAR2(30)
1717 , orig_order_line_id NUMBER
1718 , return_line_id NUMBER
1719 , orig_ordered_qty NUMBER
1720 , current_return_qty NUMBER
1721 , already_return_qty NUMBER
1722 , description VARCHAR2(240)
1723 );
1724
1725 TYPE l_tmp_qtychk_line_tbl IS TABLE OF l_tmp_qtychk_line_rec
1726 INDEX BY BINARY_INTEGER;
1727
1728 l_tmp_order_line_tbl l_tmp_qtychk_line_tbl;
1729 l_tmp_lineTblCnt NUMBER := 0;
1730
1731 Type qty_error_cur_type is REF CURSOR;
1732 qty_error_cur qty_error_cur_type;
1733 l_tmp_lineId NUMBER;
1734 l_tmp_orderNo NUMBER;
1735 l_tmp_itemtype VARCHAR2(30);
1736 l_tmp_itemdesc VARCHAR2(240);
1737 l_tmp_pNo VARCHAR2(2000);
1738 tempQty NUMBER;
1739 l_tmp_qty_idx NUMBER;
1740 failcnt NUMBER :=0;
1741 l_parseNum NUMBER :=5;
1742 l_parseKey varchar2(40) :='ORDER_SAVE_LINE_IDS';
1743
1744
1745 BEGIN
1746
1747 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1748 IBE_Util.Debug('Begin IBE_ORDER_SAVE_PVT:CheckOverReturnQty()');
1749 IBE_Util.Debug('Incoming Order Header Id: '|| p_order_header_id);
1750 END IF;
1751
1752 -- Initialize API return status to success
1753 x_return_status := FND_API.G_RET_STS_SUCCESS;
1754
1755 -- Start OF API body --
1756
1757 /*******
1758 -- Info. about the major variables used in this API ---
1759 -- l_order_line_tbl --> Return Line Ids sent to OM for Qty verifications
1760 -- l_tmp_order_line_tbl --> The temporary plsql table declared w/in this API.
1761 -- This would have the failing Return Line Ids: only
1762 -- the Model/Kit Parents' LineIds and Standard LineIds.
1763 -- This is to cover the scenario where individual Child
1764 -- Line Items are already returned, but parents are not.
1765 -- For UI, only the related Parent Line Id need to be sent.
1766 -- x_error_tbl --> It maintains all the qty validation failing Line Ids
1767 -- returned from OM API call.
1768 ***********/
1769
1770 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1771 IBE_Util.Debug('Line Ids Sent To OM for Qty Validations');
1772 END IF;
1773 OPEN QtyChkLineCur(p_order_header_id);
1774 LOOP
1775 FETCH QtyChkLineCur INTO QtyChkLineRec;
1776 EXIT WHEN QtyChkLineCur%NOTFOUND;
1777 l_order_line_tbl(lineTblCnt) := OE_Order_PUB.G_MISS_LINE_REC;
1778 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1779 IBE_Util.Debug('Line Id: '||QtyChkLineRec.LINE_ID);
1780 END IF;
1781 l_order_line_tbl(lineTblCnt).LINE_ID := QtyChkLineRec.LINE_ID;
1782 l_order_line_tbl(lineTblCnt).REFERENCE_LINE_ID := QtyChkLineRec.REFERENCE_LINE_ID;
1783 l_order_line_tbl(lineTblCnt).ORDERED_QUANTITY := QtyChkLineRec.ORDERED_QUANTITY;
1784 l_order_line_tbl(lineTblCnt).LINE_NUMBER := QtyChkLineRec.LINE_NUMBER;
1785 l_order_line_tbl(lineTblCnt).INVENTORY_ITEM_ID := QtyChkLineRec.INVENTORY_ITEM_ID;
1786 l_order_line_tbl(lineTblCnt).RETURN_ATTRIBUTE1 := QtyChkLineRec.RETURN_ATTRIBUTE1;
1787 l_order_line_tbl(lineTblCnt).RETURN_ATTRIBUTE2 := QtyChkLineRec.RETURN_ATTRIBUTE2;
1788 lineTblCnt := lineTblCnt+1;
1789 END LOOP;
1790 CLOSE QtyChkLineCur;
1791
1792 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1793 IBE_Util.Debug('l_order_line_tbl size: '|| l_order_line_tbl.COUNT);
1794 IBE_Util.Debug('Before Calling OE_RMA_GRP.Is_Over_Return()');
1795 END IF;
1796
1797 OE_RMA_GRP.Is_Over_Return(p_api_version => 1.0
1798 , p_line_tbl => l_order_line_tbl
1799 , x_error_tbl => x_error_tbl
1800 , x_return_status => x_return_status
1801 , x_msg_count => x_msg_count
1802 , x_msg_data => x_msg_data
1803 );
1804
1805
1806 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1807 IBE_Util.Debug('After Calling Is_Over_Return - return_status: '|| x_return_status);
1808 END IF;
1809
1810 IF x_return_status <> FND_API.G_RET_STS_SUCCESS then
1811 IF (IBE_UTIL.G_DEBUGON = l_true) then
1812 IBE_UTIL.DEBUG('Error Table Count: ' || x_error_tbl.count);
1813 end if;
1814
1815 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1816 IBE_Util.Debug('Line Quantity Validation Details Returned from OM');
1817 ENd IF;
1818 for j in 1.. x_error_tbl.count
1819 loop
1820 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1821 IBE_Util.Debug('line_id= '||x_error_tbl(j).line_id||' : '||
1822 'prev_qty= '||x_error_tbl(j).previous_quantity||' : '||
1823 'curr_qty= '||x_error_tbl(j).current_quantity||' : '||
1824 'orig_qty= '||x_error_tbl(j).original_quantity||' : '||
1825 'ret_stat= '||x_error_tbl(j).return_status);
1826 END IF;
1827 p_tmp_error_lineIds := p_tmp_error_lineIds || ','||x_error_tbl(j).LINE_ID;
1828 end loop;
1829
1830 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1831 Ibe_util.debug('l_tmp_error_lineIds set: '||p_tmp_error_lineIds);
1832 end if;
1833
1834 IBE_LEAD_IMPORT_PVT.parseInput (p_tmp_error_lineIds, 'CHAR', l_parseKey, l_parseNum, l_tmp_error_lineIds);
1835
1836 -- this query returns the Ordernumber, partnumber necessary for the error message of the lines
1837 -- violating the qty. check
1838 -- Supppose if only one of the child w/in a MODEL is violating, then
1839 -- the related MODEL's LINEID would be used to generate the error message.
1840 -- For MODEL items, the error messg. would be generic.
1841
1842 l_qty_tmp_query := 'SELECT OEH.order_number,msi.concatenated_segments,msi.DESCRIPTION,'||
1843 'OEL.line_id,OEL.item_type_code '||
1844 'FROM oe_order_lines_all OEL,'||
1845 'oe_order_headers_all OEH,'||
1846 'mtl_system_items_vl msi '||
1847 'WHERE OEL.header_id = OEH.header_id '||
1848 'AND OEL.inventory_item_id = msi.inventory_item_Id '||
1849 'AND msi.organization_id = oe_profile.value(''OE_ORGANIZATION_ID'', OEL.org_id) '||
1850 'AND OEL.line_id IN('||
1851 'SELECT nvl(top_model_line_id,line_id) '||
1852 'FROM oe_order_lines_all '||
1853 'WHERE line_id IN('||
1854 'SELECT return_attribute2 '||
1855 'FROM oe_order_lines_all '||
1856 'WHERE line_id in('||l_tmp_error_lineIds||')))';
1857
1858
1859 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1860 IBE_Util.debug('qry executed: '|| l_qty_tmp_query);
1861 end if;
1862
1863 open qty_error_cur for l_qty_tmp_query using l_parseKey;
1864 LOOP
1865 FETCH qty_error_cur INTO l_tmp_orderNo,l_tmp_pNo,l_tmp_itemdesc,l_tmp_lineId,l_tmp_itemtype;
1866 EXIT WHEN qty_error_cur%NOTFOUND;
1867 l_tmp_order_line_tbl(l_tmp_lineTblCnt).ORIG_ORDER_LINE_ID := l_tmp_lineId;
1868 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1869 IBE_Util.debug('l_tmp_lineId: '|| l_tmp_lineId);
1870 end if;
1871 l_tmp_order_line_tbl(l_tmp_lineTblCnt).ORDERED_ITEM := l_tmp_pNo;
1872 l_tmp_order_line_tbl(l_tmp_lineTblCnt).ORDER_NUMBER := l_tmp_orderNo;
1873 l_tmp_order_line_tbl(l_tmp_lineTblCnt).ITEM_TYPE_CODE := l_tmp_itemtype;
1874 l_tmp_order_line_tbl(l_tmp_lineTblCnt).description := l_tmp_itemdesc;
1875 l_tmp_lineTblCnt := l_tmp_lineTblCnt+1;
1876 END LOOP;
1877 close qty_error_cur;
1878
1879
1880 -- The Original OrderNumber, Orig. LineIds returned were fetched.
1881 -- Now need to find the related return lineIds of these order lineIDs.
1882 -- As these were originally sent to OM API, can be checked using the same plsql table.
1883
1884 l_tmp_index := l_tmp_order_line_tbl.FIRST;
1885 while l_tmp_index IS NOt NULL
1886 LOOP
1887 l_index := l_order_line_tbl.FIRST;
1888 while l_index IS NOT NULL
1889 loop
1890 if(l_tmp_order_line_tbl(l_tmp_index).orig_order_line_id = l_order_line_tbl(l_index).return_attribute2) then
1891 -- l_tmp_order_line_tbl(l_tmp_index) := l_tmp_order_line_tbl(l_tmp_index);
1892 l_tmp_order_line_tbl(l_tmp_index).return_line_id := l_order_line_tbl(l_index).line_id;
1893 end if;
1894 l_index := l_order_line_tbl.NEXT(l_index);
1895 end loop; --m loop
1896 l_tmp_index := l_tmp_order_line_tbl.NEXT(l_tmp_index);
1897 end loop;
1898
1899 -- Now collected all details about the failing return line ids.(l_tmp_order_line_tbl)
1900 -- So need to populate the respective error messages into the FND stack
1901 -- l_tmp_order_line_tbl should be checked against "x_error_tbl", OM returned
1902 -- Error Line IDS.
1903 -- If STD item, then populate the error message w/ all quantity details from "x_error_tbl"
1904 -- If Model Item or only any of the child item, is violating,
1905 -- the generic error message should be populated.
1906 -- But only if a childitem is failing for the Qty check, we need to set the ModelParent's
1907 -- PartNO. into Error Message.
1908 -- Note: "x_error_tbl" wont be having the related Model Parents' ID but this is present in
1909 -- "l_tmp_order_line_tbl". - Scenario1
1910 -- There may be a case where only child items are failing.
1911 -- For instance, l_tmp_order_line_tbl --> ModParentId1 and
1912 -- x_error_tbl --> ChldId1,ChldId2.
1913 -- To identify such flows l_errorflow var is introduced.
1914
1915 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1916 IBE_Util.debug('ibe:temp Err and OM:error Table Counts: '|| l_tmp_order_line_tbl.count|| ' , '||x_error_tbl.count);
1917 end if;
1918
1919 ---- New Changes for sorting in sync w/ UI
1920 -- Setting the Quantity details as this is needed for STD item Error Message.
1921
1922 l_tmp_index1 := l_tmp_order_line_tbl.FIRST;
1923 WHILE (l_tmp_index1 IS NOT NULL)
1924 LOOP
1925 l_index1 := x_error_tbl.FIRST;
1926 WHILE (l_index1 IS NOT NULL)
1927 LOOP
1928 IF (l_tmp_order_line_tbl(l_tmp_index1).return_line_id = x_error_tbl(l_index1).line_id) then
1929 l_tmp_order_line_tbl(l_tmp_index1).orig_ordered_qty := x_error_tbl(l_index1).original_quantity;
1930 l_tmp_order_line_tbl(l_tmp_index1).current_return_qty := x_error_tbl(l_index1).current_quantity;
1931 l_tmp_order_line_tbl(l_tmp_index1).already_return_qty := x_error_tbl(l_index1).previous_quantity;
1932 END IF;
1933 l_index1 := x_error_tbl.NEXT(l_index1);
1934 END LOOP;
1935 l_tmp_index1:=l_tmp_order_line_tbl.NEXT(l_tmp_index1);
1936 END LOOP;
1937
1938 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1939 IBE_Util.debug('After retrieving the quantity details for the failing lineids from OM error table');
1940 end if;
1941
1942 /*** Now start to Append the FND stack having the original Plsql table sent to
1943 OM, for qty validations, as
1944 the base table to maintain the UI display order***/
1945
1946 l_index1 := l_order_line_tbl.FIRST;
1947 WHILE (l_index1 IS NOT NULL)
1948 LOOP
1949 l_index2 := l_tmp_order_line_tbl.FIRST;
1950 WHILE (l_index2 IS NOT NULL)
1951 LOOP
1952 IF (l_order_line_tbl(l_index1).line_id = l_tmp_order_line_tbl(l_index2).return_line_id) THEN
1953 IF (l_tmp_order_line_tbl(l_index2).item_type_code='STANDARD') then
1954 tempQty := (l_tmp_order_line_tbl(l_index2).orig_ordered_qty) -
1955 (l_tmp_order_line_tbl(l_index2).already_return_qty);
1956
1957 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1958 IBE_Util.debug('STD-Item: Fails for Qty: Order#='||l_tmp_order_line_tbl(l_index2).order_number
1959 ||' : '||trim(l_tmp_order_line_tbl(l_index2).description)||' : '
1960 ||'allowed Qty = '||tempQty);
1961 end if;
1962 FND_MESSAGE.Set_Name('IBE','IBE_ERR_OT_LINE_OVER_QTY');
1963 FND_MESSAGE.Set_Token('ORD', l_tmp_order_line_tbl(l_index2).order_number);
1964 FND_MESSAGE.Set_Token('DESC', trim(l_tmp_order_line_tbl(l_index2).description));
1965
1966 FND_MESSAGE.Set_Token('QTY', tempQty);
1967 FND_Msg_Pub.Add;
1968
1969 ELSIF(l_tmp_order_line_tbl(l_index2).item_type_code IN ('MODEL','KIT')) then
1970 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1971 IBE_Util.debug('MDL-Item: Fails for Qty: Order#='||l_tmp_order_line_tbl(l_index2).order_number||
1972 ' : '||trim(l_tmp_order_line_tbl(l_index2).description));
1973 end if;
1974 FND_MESSAGE.Set_Name('IBE','IBE_ERR_OT_MDL_LINE_OVER_QTY');
1975 FND_MESSAGE.Set_Token('ORD', l_tmp_order_line_tbl(l_index2).order_number);
1976 FND_MESSAGE.Set_Token('DESC', trim(l_tmp_order_line_tbl(l_index2).description));
1977 FND_Msg_Pub.Add;
1978 END IF;
1979 END IF;
1980 l_index2 := l_tmp_order_line_tbl.NEXT(l_index2);
1981 END LOOP;
1982 l_index1 := l_order_line_tbl.NEXT(l_index1);
1983 END LOOP;
1984
1985 ----------- upto here
1986
1987 /**** This loop is to populate into failing Error Line Ids plsql table, OUT variable ***/
1988
1989 l_tmp_qty_idx:=l_tmp_order_line_tbl.FIRST;
1990
1991 while(l_tmp_qty_idx IS NOT NULL)
1992 loop
1993
1994 failCnt:=failCnt+1;
1995
1996 x_qtyfail_LineIds(failCnt) := 'QTY:'||l_tmp_order_line_tbl(l_tmp_qty_idx).return_line_id;
1997 l_tmp_qty_idx := l_tmp_order_line_tbl.NEXT(l_tmp_qty_idx);
1998 end loop;
1999
2000 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2001 raise FND_API.G_EXC_ERROR;
2002 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2003 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2004 END IF;
2005 END IF;
2006
2007
2008 --
2009 -- End of API body
2010 --
2011
2012 -- Standard call to get message count and if count is 1, get message info.
2013
2014 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2015 p_data => x_msg_data);
2016
2017 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2018 IBE_Util.Debug('End OE_ORDER_SAVE_PVT.CheckReturnQty()');
2019 END IF;
2020
2021
2022 EXCEPTION
2023
2024 WHEN FND_API.G_EXC_ERROR THEN
2025 x_return_status := FND_API.G_RET_STS_ERROR;
2026 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
2027 p_count => x_msg_count ,
2028 p_data => x_msg_data);
2029 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2030 IBE_Util.Debug('Error IBE_ORDER_SAVE_PVT:CheckOverReturnQty()'|| sqlerrm);
2031 END IF;
2032
2033 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2034 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2035 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
2036 p_count => x_msg_count ,
2037 p_data => x_msg_data);
2038 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2039 IBE_Util.Debug('UNEXPECTEDError IBE_ORDER_SAVE_PVT:CheckOverReturnQty()' || sqlerrm);
2040 END IF;
2041 WHEN OTHERS THEN
2042 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2043
2044 IF FND_Msg_Pub.Check_Msg_Level( FND_Msg_Pub.G_MSG_LVL_UNEXP_ERROR ) THEN
2045 FND_Msg_Pub.Add_Exc_Msg(G_PKG_NAME,
2046 L_API_NAME);
2047 END IF;
2048
2049 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
2050 p_count => x_msg_count ,
2051 p_data => x_msg_data);
2052 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2053 IBE_Util.Debug('Other IBE_ORDER_SAVE_PVT:CheckOverReturnQty()' || sqlerrm);
2054 END IF;
2055
2056 END CheckOverReturnQty;
2057
2058 PROCEDURE TCA_AddressValidate(
2059 p_order_header_id IN NUMBER,
2060 p_user_type IN VARCHAR2,
2061 p_site_use_type IN VARCHAR2,
2062 X_failed_line_ids OUT NOCOPY x_qtyfail_LineType,
2063 x_return_status OUT NOCOPY VARCHAR2
2064 )
2065
2066 IS
2067
2068 Type Addr_error_cur_type is REF CURSOR;
2069 Addr_error_cur Addr_error_cur_type;
2070
2071 type tmp_line_id_type IS TABLE OF NUMBER
2072 INDEX BY BINARY_INTEGER;
2073
2074 l_tmpfail_line_id tmp_line_id_type;
2075 tmpCount NUMBER:=0;
2076 FailLines VARCHAR2(10):=FND_API.G_FALSE;
2077 l_index NUMBER;
2078 l_addr_validate_qry VARCHAR2(4000);
2079 failCnt NUMBER:=0;
2080
2081 BEGIN
2082
2083 x_return_status := FND_API.G_RET_STS_SUCCESS;
2084
2085 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2086 IBE_Util.debug('IBE_Order_Save_Pvt.TCA_AddressValidate BEGIN order_hdr_id: '||p_order_header_id);
2087 end if;
2088
2089 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2090 IBE_Util.debug('IBE_Order_Save_Pvt.TCA_AddressValidate BEGIN site_use_type : '||p_site_use_type);
2091 end if;
2092
2093
2094 if(p_user_type = 'PARTY_RELATIONSHIP') then
2095
2096 if (p_site_use_type ='BILL_TO') then
2097 l_addr_validate_qry:='SELECT LINES.LINE_ID '||
2098 'FROM HZ_CUST_SITE_USES SITE, '||
2099 'HZ_CUST_ACCT_SITES ACCT_SITE, '||
2100 'OE_ORDER_LINES_ALL LINES '||
2101 'WHERE LINES.HEADER_ID = :InvChkHdrId '||
2102 'AND SITE.SITE_USE_ID = LINES.invoice_to_org_id '||
2103 'AND SITE.SITE_USE_CODE = ''BILL_TO'' '||
2104 'AND SITE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID '||
2105 'AND (SITE.STATUS = ''I'' OR ACCT_SITE.STATUS = ''I'') '||
2106 'AND '||
2107 'ACCT_SITE.CUST_ACCOUNT_ID in ('||
2108 'SELECT LINES.sold_to_org_id FROM DUAL '||
2109 'UNION '||
2110 'SELECT CUST_ACCOUNT_ID '||
2111 'FROM HZ_CUST_ACCT_RELATE '||
2112 'WHERE '||
2113 'RELATED_CUST_ACCOUNT_ID = LINES.sold_to_org_id '||
2114 'and bill_to_flag = ''Y'' '||
2115 'and status=''A'')';
2116
2117 elsif(p_site_use_type = 'SHIP_TO') then
2118
2119 l_addr_validate_qry :='SELECT LINES.LINE_ID '||
2120 'FROM HZ_CUST_SITE_USES SITE, '||
2121 'HZ_CUST_ACCT_SITES ACCT_SITE, '||
2122 'OE_ORDER_LINES_ALL LINES '||
2123 'WHERE LINES.HEADER_ID = :shpChkHdrId '||
2124 'AND SITE.SITE_USE_ID = LINES.ship_to_org_id '||
2125 'AND SITE.SITE_USE_CODE = ''SHIP_TO'' '||
2126 'AND SITE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID '||
2127 'AND (SITE.STATUS = ''I'' OR ACCT_SITE.STATUS = ''I'') '||
2128 'AND '||
2129 'ACCT_SITE.CUST_ACCOUNT_ID in ('||
2130 'SELECT LINES.sold_to_org_id FROM DUAL '||
2131 'UNION '||
2132 'SELECT CUST_ACCOUNT_ID '||
2133 'FROM HZ_CUST_ACCT_RELATE '||
2134 'WHERE '||
2135 'RELATED_CUST_ACCOUNT_ID = LINES.sold_to_org_id '||
2136 'and ship_to_flag = ''Y'' '||
2137 'and status=''A'')';
2138
2139 end if; -- if p_site_use_type
2140
2141 elsif(p_user_type='PERSON') then
2142
2143 if(p_site_use_type = 'BILL_TO') then
2144 l_addr_validate_qry := 'SELECT HDR.HEADER_ID '||
2145 'FROM HZ_CUST_SITE_USES SITE, '||
2146 'HZ_CUST_ACCT_SITES ACCT_SITE, '||
2147 'OE_ORDER_HEADERS_ALL HDR '||
2148 'WHERE HDR.HEADER_ID = :invChkHdrId '||
2149 'AND SITE.SITE_USE_ID = HDR.invoice_to_org_id '||
2150 'AND SITE.SITE_USE_CODE = ''BILL_TO'' '||
2151 'AND SITE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID '||
2152 'AND (SITE.STATUS = ''I'' OR ACCT_SITE.STATUS = ''I'') '||
2153 'AND '||
2154 'ACCT_SITE.CUST_ACCOUNT_ID in ('||
2155 'SELECT HDR.sold_to_org_id FROM DUAL '||
2156 'UNION '||
2157 'SELECT CUST_ACCOUNT_ID '||
2158 'FROM HZ_CUST_ACCT_RELATE '||
2159 'WHERE '||
2160 'RELATED_CUST_ACCOUNT_ID = HDR.sold_to_org_id '||
2161 'and bill_to_flag = ''Y'' '||
2162 'and status=''A'')';
2163
2164
2165 elsif(p_site_use_type = 'SHIP_TO') then
2166 l_addr_validate_qry := 'SELECT HDR.HEADER_ID '||
2167 'FROM HZ_CUST_SITE_USES SITE, '||
2168 'HZ_CUST_ACCT_SITES ACCT_SITE, '||
2169 'OE_ORDER_HEADERS_ALL HDR '||
2170 'WHERE HDR.HEADER_ID = :shpChkHdrId '||
2171 'AND SITE.SITE_USE_ID = HDR.ship_to_org_id '||
2172 'AND SITE.SITE_USE_CODE = ''SHIP_TO'' '||
2173 'AND SITE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID '||
2174 'AND (SITE.STATUS = ''I'' OR ACCT_SITE.STATUS = ''I'') '||
2175 'AND '||
2176 'ACCT_SITE.CUST_ACCOUNT_ID in ('||
2177 'SELECT HDR.sold_to_org_id FROM DUAL '||
2178 'UNION '||
2179 'SELECT CUST_ACCOUNT_ID '||
2180 'FROM HZ_CUST_ACCT_RELATE '||
2181 'WHERE '||
2182 'RELATED_CUST_ACCOUNT_ID = HDR.sold_to_org_id '||
2183 'and ship_to_flag = ''Y'' '||
2184 'and status=''A'')';
2185 end if; -- if p_site_use_type
2186 end if; -- p_user_type
2187
2188 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2189 IBE_Util.debug('Qry executed for Address validation: '||l_addr_validate_qry);
2190 end if;
2191 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2192 IBE_Util.debug('Qry executed for Address validation: '||l_addr_validate_qry);
2193 end if;
2194
2195 open Addr_error_cur for l_addr_validate_qry using p_order_header_id;
2196 loop
2197 fetch Addr_error_cur into l_tmpfail_line_id(tmpCount);
2198 Exit when Addr_error_cur%notfound;
2199 tmpCount := tmpCount+1;
2200 FailLines := FND_API.G_TRUE;
2201 end loop;
2202
2203 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2204 IBE_Util.debug('Check whether any lines failed: '||FailLines);
2205 end if;
2206
2207 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2208 IBE_Util.debug('Check whether any lines failed: '||FailLines);
2209 end if;
2210
2211 if(FND_API.to_boolean(FailLines)) then
2212 x_return_status := FND_API.G_RET_STS_ERROR;
2213 l_index := l_tmpfail_line_id.FIRST;
2214 while(l_index is not null)
2215 loop
2216 failCnt:=failCnt+1;
2217 if(p_site_use_type='BILL_TO' AND p_user_type = 'PARTY_RELATIONSHIP') then
2218 X_failed_line_ids(failCnt) := 'BILLADDR:'||l_tmpfail_line_id(l_index);
2219 end if;
2220 if(p_site_use_type='SHIP_TO' AND p_user_type = 'PARTY_RELATIONSHIP') then
2221 X_failed_line_ids(failCnt) := 'SHIPADDR:'||l_tmpfail_line_id(l_index);
2222 end if;
2223 if(p_site_use_type='BILL_TO' AND p_user_type = 'PERSON') then
2224 X_failed_line_ids(failCnt) := 'BILLADDR:HDR';
2225 end if;
2226 if(p_site_use_type='SHIP_TO' AND p_user_type = 'PERSON') then
2227 X_failed_line_ids(failCnt) := 'SHIPADDR:HDR';
2228 end if;
2229 l_index:=l_tmpfail_line_id.NEXT(l_index);
2230 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2231 IBE_Util.debug('Line ID Failed for Addr: '|| X_failed_line_ids(failCnt));
2232 end if;
2233 end loop;
2234 end if;
2235
2236 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2237 IBE_Util.debug('IBE_Order_Save_Pvt.TCA_AddressValidate END '||x_return_status);
2238 end if;
2239
2240
2241 END TCA_AddressValidate;
2242
2243 PROCEDURE TCA_ContactValidate(
2244 p_order_header_id IN NUMBER,
2245 p_site_use_type IN VARCHAR2,
2246 X_failed_line_ids OUT NOCOPY x_qtyfail_LineType,
2247 x_return_status OUT NOCOPY VARCHAR2
2248 )
2249
2250 IS
2251
2252 Type Contact_error_cur_type is REF CURSOR;
2253 Contact_error_cur Contact_error_cur_type;
2254
2255 type tmp_line_id_type IS TABLE OF NUMBER
2256 INDEX BY BINARY_INTEGER;
2257
2258 l_tmpfail_line_id tmp_line_id_type;
2259 tmpCount NUMBER:=0;
2260 FailLines VARCHAR2(10):=FND_API.G_FALSE;
2261 l_index NUMBER;
2262 l_contact_validate_qry VARCHAR2(4000);
2263 failCnt NUMBER:=0;
2264
2265 BEGIN
2266
2267 x_return_status := FND_API.G_RET_STS_SUCCESS;
2268
2269 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2270 IBE_Util.debug('IBE_Order_Save_Pvt.TCA_AddressValidate BEGIN');
2271 end if;
2272
2273 if (p_site_use_type ='BILL_TO') then
2274 l_contact_validate_qry:='SELECT LINES.LINE_ID '||
2275 'FROM OE_ORDER_LINES_ALL LINES, '||
2276 'HZ_CUST_ACCOUNT_ROLES ACCT_ROLE, '||
2277 'HZ_CUST_SITE_USES_ALL INV, '||
2278 'HZ_CUST_ACCT_SITES_ALL ADDR '||
2279 'WHERE LINES.HEADER_ID = :InvCntChkHdrId '||
2280 'AND ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = LINES.invoice_to_contact_id '||
2281 'AND ACCT_ROLE.CUST_ACCOUNT_ID = ADDR.CUST_ACCOUNT_ID '||
2282 'AND ACCT_ROLE.ROLE_TYPE = ''CONTACT'' '||
2283 'AND ADDR.CUST_ACCT_SITE_ID = INV.CUST_ACCT_SITE_ID '||
2284 'AND INV.SITE_USE_ID = LINES.invoice_to_org_id '||
2285 'AND INV.STATUS = ''I'' '||
2286 'AND ACCT_ROLE.STATUS = ''I''';
2287
2288 elsif(p_site_use_type = 'SHIP_TO') then
2289
2290 l_contact_validate_qry:='SELECT LINES.LINE_ID '||
2291 'FROM OE_ORDER_LINES_ALL LINES, '||
2292 'HZ_CUST_ACCOUNT_ROLES ACCT_ROLE, '||
2293 'HZ_CUST_SITE_USES_ALL INV, '||
2294 'HZ_CUST_ACCT_SITES_ALL ADDR '||
2295 'WHERE LINES.HEADER_ID = :InvCntChkHdrId '||
2296 'AND ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = LINES.ship_to_contact_id '||
2297 'AND ACCT_ROLE.CUST_ACCOUNT_ID = ADDR.CUST_ACCOUNT_ID '||
2298 'AND ACCT_ROLE.ROLE_TYPE = ''CONTACT'' '||
2299 'AND ADDR.CUST_ACCT_SITE_ID = INV.CUST_ACCT_SITE_ID '||
2300 'AND INV.SITE_USE_ID = LINES.ship_to_org_id '||
2301 'AND INV.STATUS = ''I'' '||
2302 'AND ACCT_ROLE.STATUS = ''I''';
2303
2304 end if; -- if p_site_use_type
2305
2306 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2307 IBE_Util.debug('Contact Query Executed: '||l_contact_validate_qry);
2308 end if;
2309
2310 open Contact_error_cur for l_contact_validate_qry using p_order_header_id;
2311 loop
2312 fetch Contact_error_cur into l_tmpfail_line_id(tmpCount);
2313 Exit when Contact_error_cur%notfound;
2314 tmpCount := tmpCount+1;
2315 FailLines := FND_API.G_TRUE;
2316 end loop;
2317
2318 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2319 IBE_Util.debug('Whether Any Lines failed for Contact check: '|| FailLines);
2320 end if;
2321
2322 if(FND_API.to_boolean(FailLines)) then
2323 x_return_status := FND_API.G_RET_STS_ERROR;
2324 l_index := l_tmpfail_line_id.FIRST;
2325 while(l_index is not null)
2326 loop
2327 failCnt:=failCnt+1;
2328 if(p_site_use_type='BILL_TO') then
2329 X_failed_line_ids(failCnt) := 'BILLCTNT:'||l_tmpfail_line_id(l_index);
2330 end if;
2331 if(p_site_use_type='SHIP_TO') then
2332 X_failed_line_ids(failCnt) := 'SHIPCTNT:'||l_tmpfail_line_id(l_index);
2333 end if;
2334 l_index:=l_tmpfail_line_id.NEXT(l_index);
2335 end loop;
2336 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2337 IBE_Util.debug('Line Id failed for Contact check: '|| X_failed_line_ids(failCnt));
2338 end if;
2339 end if;
2340
2341 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2342 IBE_Util.debug('IBE_Order_Save_Pvt.TCA_AddressValidate END '||x_return_status);
2343 end if;
2344
2345 END TCA_ContactValidate;
2346
2347
2348 -- New API For Address and Quantity validations
2349 -- For POST OM 11590 (OM.J) flows during submit actions.
2350 -- Refer bug# 3272918
2351
2352 PROCEDURE Complete_RetOrder_Validate
2353 ( P_order_header_id IN NUMBER,
2354 p_user_type IN VARCHAR2,
2355 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
2356 X_failed_line_ids OUT NOCOPY JTF_VARCHAR2_TABLE_300,
2357 X_return_status OUT NOCOPY VARCHAR2,
2358 X_msg_count OUT NOCOPY NUMBER,
2359 X_msg_data OUT NOCOPY VARCHAR2
2360 )
2361
2362 IS
2363
2364 l_api_name varchar2(40) := 'Complete_RetOrder_Validate';
2365
2366
2367 tCount NUMBER:=0;
2368 QtyFailLineCnt NUMBER ;
2369 TCAFailLineCnt NUMBER;
2370 l_inv_index NUMBER;
2371 l_shp_index NUMBER;
2372 l_inv_index1 NUMBER;
2373 l_shp_index1 NUMBER;
2374 FailLines VARCHAR2(10):=FND_API.G_FALSE;
2375 x_qtyfail_LineIds x_qtyfail_LineType;
2376 x_TCAfail_LineIds x_qtyfail_LineType;
2377 l_return_status varchar2(100);
2378 ll_return_status varchar2(100);
2379
2380
2381 BEGIN
2382
2383 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2384 IBE_Util.Debug('Begin IBE_ORDER_SAVE_PVT:Complete_RetLine_Validation()');
2385 END IF;
2386
2387 X_failed_line_ids := JTF_VARCHAR2_TABLE_300();
2388
2389 -- Initialize API return status to success
2390 x_return_status := FND_API.G_RET_STS_SUCCESS;
2391
2392 IF FND_API.To_Boolean( p_init_msg_list ) THEN
2393 FND_Msg_Pub.initialize;
2394 END IF;
2395
2396
2397 -- Calling Address validate API w/ BILL_TO
2398 -- If needed, for 'shipto' validation, add one more call w/ SHIP_TO.
2399 -- The user type check would be done in the TCA_Addressvalidate API.
2400
2401
2402
2403
2404
2405 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2406 IBE_Util.Debug('Before calling TCA_AddressValidate for billto: ');
2407 END IF;
2408
2409 TCA_AddressValidate(p_order_header_id => p_order_header_id,
2410 p_user_type => p_user_type,
2411 p_site_use_type => 'BILL_TO',
2412 X_failed_line_ids => x_TCAfail_LineIds,
2413 x_return_status => l_return_status
2414 );
2415
2416 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2417 IBE_Util.Debug('After calling TCA_AddressValidate Billto: '||l_return_status);
2418 END IF;
2419
2420 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2421 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2422 IBE_Util.Debug('Address Failing Line count Billto:'|| x_TCAfail_LineIds.count);
2423 END IF;
2424 tCount := X_failed_line_ids.count;
2425 TCAFailLineCnt := x_TCAfail_LineIds.FIRST;
2426
2427 while (TCAFailLineCnt IS NOT NULL)
2428 Loop
2429 X_failed_line_ids.extend(1);
2430 tCount := tCount+1;
2431 X_failed_line_ids(tCount) := x_TCAfail_LineIds(TCAFailLineCnt);
2432 TCAFailLineCnt := x_TCAfail_LineIds.NEXT(TCAFailLineCnt);
2433 end loop;
2434
2435 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2436 IBE_Util.Debug('Total failLine Ids in Addr Loop Bill to: '|| X_failed_line_ids.count);
2437 for k in 1..X_failed_line_ids.count
2438 loop
2439 IBE_Util.Debug('Total failLine Ids in Addr Loop Billto: '|| X_failed_line_ids(k));
2440 end loop;
2441 END IF;
2442
2443
2444 END IF;
2445
2446
2447 /** p_user_type is checked because if its a B2C flow,
2448 no need to validate the CONTACT details ***/
2449
2450 if(p_user_type ='PARTY_RELATIONSHIP') THEN
2451
2452 TCA_ContactValidate(p_order_header_id => p_order_header_id,
2453 p_site_use_type => 'BILL_TO',
2454 X_failed_line_ids => x_TCAfail_LineIds,
2455 x_return_status => ll_return_status
2456 );
2457
2458 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2459 IBE_Util.Debug('After calling TCA_ContactValidate: '||ll_return_status);
2460 END IF;
2461 IF ll_return_status = FND_API.G_RET_STS_ERROR THEN
2462 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2463 IBE_Util.Debug('Failing Line count:'|| x_TCAfail_LineIds.count);
2464 END IF;
2465 tCount := X_failed_line_ids.count;
2466 TCAFailLineCnt := x_TCAfail_LineIds.FIRST;
2467
2468 while (TCAFailLineCnt IS NOT NULL)
2469 Loop
2470 X_failed_line_ids.extend(1);
2471 tCount := tCount+1;
2472 X_failed_line_ids(tCount) := x_TCAfail_LineIds(TCAFailLineCnt);
2473 TCAFailLineCnt := x_TCAfail_LineIds.NEXT(TCAFailLineCnt);
2474 end loop;
2475
2476 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2477 IBE_Util.Debug('Total failLine Ids in Contact Loop: '|| X_failed_line_ids.count);
2478 for k in 1..X_failed_line_ids.count
2479 loop
2480 IBE_Util.Debug('Total failLine Ids in Contact Loop: '|| X_failed_line_ids(k));
2481 end loop;
2482 END IF;
2483
2484 end if;
2485
2486 /*
2487 Set the Message if either the contact or address is invalid
2488 */
2489 if(l_return_status =FND_API.G_RET_STS_ERROR
2490 OR ll_return_status =FND_API.G_RET_STS_ERROR) then
2491 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2492 IBE_Util.Debug('Invalid Billing Details- Settig the Error to FND stack');
2493 END IF;
2494 FND_MESSAGE.Set_Name('IBE','IBE_ERR_OT_INVALID_BILLDET');
2495 FND_Msg_Pub.Add;
2496 end if;
2497
2498 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2499 IBE_Util.Debug('Before calling TCA_AddressValidate: BILL_TO');
2500 END IF;
2501
2502 -- For Ship To Contact and Address Validation
2503 TCA_AddressValidate(p_order_header_id => p_order_header_id,
2504 p_user_type => p_user_type,
2505 p_site_use_type => 'SHIP_TO',
2506 X_failed_line_ids => x_TCAfail_LineIds,
2507 x_return_status => l_return_status
2508 );
2509
2510 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2511 IBE_Util.Debug('After calling TCA_AddressValidate Ship to: '||l_return_status);
2512 END IF;
2513
2514 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2515 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2516 IBE_Util.Debug('Address Failing Line count Shipto:'|| x_TCAfail_LineIds.count);
2517 END IF;
2518 tCount := X_failed_line_ids.count;
2519 TCAFailLineCnt := x_TCAfail_LineIds.FIRST;
2520
2521 while (TCAFailLineCnt IS NOT NULL)
2522 Loop
2523 X_failed_line_ids.extend(1);
2524 tCount := tCount+1;
2525 X_failed_line_ids(tCount) := x_TCAfail_LineIds(TCAFailLineCnt);
2526 TCAFailLineCnt := x_TCAfail_LineIds.NEXT(TCAFailLineCnt);
2527 end loop;
2528
2529 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2530 IBE_Util.Debug('Total failLine Ids in Addr Loop:Shipto '|| X_failed_line_ids.count);
2531 for k in 1..X_failed_line_ids.count
2532 loop
2533 IBE_Util.Debug('Total failLine Ids in Addr Loop:Shipto '|| X_failed_line_ids(k));
2534 end loop;
2535 END IF;
2536
2537
2538 END IF;
2539
2540 TCA_ContactValidate(p_order_header_id => p_order_header_id,
2541 p_site_use_type => 'SHIP_TO',
2542 X_failed_line_ids => x_TCAfail_LineIds,
2543 x_return_status => ll_return_status
2544 );
2545
2546 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2547 IBE_Util.Debug('After calling TCA_ContactValidate: '||ll_return_status);
2548 END IF;
2549 IF ll_return_status = FND_API.G_RET_STS_ERROR THEN
2550 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2551 IBE_Util.Debug('Failing Line count:'|| x_TCAfail_LineIds.count);
2552 END IF;
2553 tCount := X_failed_line_ids.count;
2554 TCAFailLineCnt := x_TCAfail_LineIds.FIRST;
2555
2556 while (TCAFailLineCnt IS NOT NULL)
2557 Loop
2558 X_failed_line_ids.extend(1);
2559 tCount := tCount+1;
2560 X_failed_line_ids(tCount) := x_TCAfail_LineIds(TCAFailLineCnt);
2561 TCAFailLineCnt := x_TCAfail_LineIds.NEXT(TCAFailLineCnt);
2562 end loop;
2563
2564 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2565 IBE_Util.Debug('Total failLine Ids in Contact Loop: '|| X_failed_line_ids.count);
2566 for k in 1..X_failed_line_ids.count
2567 loop
2568 IBE_Util.Debug('Total failLine Ids in Contact Loop: '|| X_failed_line_ids(k));
2569 end loop;
2570 END IF;
2571
2572 end if;
2573
2574 --End Ship To Contact Validation.
2575
2576 --p_user_type
2577
2578 END IF;
2579
2580
2581 if(l_return_status =FND_API.G_RET_STS_ERROR
2582 OR ll_return_status =FND_API.G_RET_STS_ERROR) then
2583 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2584 IBE_Util.Debug('Invalid Billing Details- Settig the Error to FND stack');
2585 END IF;
2586 FND_MESSAGE.Set_Name('IBE','IBE_ERR_OT_INVALID_SHIPDET');
2587 FND_Msg_Pub.Add;
2588 end if;
2589
2590
2591 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2592 IBE_Util.Debug('Before Calling CheckOverRetQty');
2593 END IF;
2594
2595 -- Now verify the Over Return Quantity
2596
2597
2598 CheckOverReturnQty(p_order_header_id => P_order_header_id
2599 ,x_qtyfail_LineIds => x_qtyfail_LineIds
2600 ,x_return_status => x_return_status
2601 ,x_msg_count => x_msg_count
2602 ,x_msg_data => x_msg_data
2603 );
2604
2605 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2606 IBE_Util.Debug('After Calling CheckOverRetQty');
2607 END IF;
2608
2609 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2610 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2611 IBE_Util.Debug('No. of Lines failing after Qty validations:'|| x_qtyfail_LineIds.count);
2612 END IF;
2613 tCount := X_failed_line_ids.count;
2614 QtyFailLineCnt := x_qtyfail_LineIds.FIRST;
2615
2616 while (QtyFailLineCnt IS NOT NULL)
2617 Loop
2618 X_failed_line_ids.extend(1);
2619 tCount := tCount+1;
2620 X_failed_line_ids(tCount) := x_qtyfail_LineIds(QtyFailLineCnt);
2621 QtyFailLineCnt := x_qtyfail_LineIds.NEXT(QtyFailLineCnt);
2622 end loop;
2623
2624 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2625 IBE_Util.Debug('Total failLine Ids In final loop: '|| X_failed_line_ids.count);
2626 for k in 1..X_failed_line_ids.count
2627 loop
2628 IBE_Util.Debug('Total failLine Ids in final Loop: '|| X_failed_line_ids(k));
2629 end loop;
2630 END IF;
2631 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2632 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2633 END IF;
2634
2635 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2636 IBE_Util.Debug('Total Failing LineCounts:'||tCount);
2637 END IF;
2638 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2639 IBE_Util.Debug(' Total Failing LineCounts:'||tCount);
2640 END IF;
2641
2642 -- If there are validation failing line IDs then set the status as ERROR.
2643
2644 IF (X_failed_line_ids.count > 0) THEN
2645 raise FND_API.G_EXC_ERROR;
2646 ENd IF;
2647
2648 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2649 Ibe_util.Debug('End IBE_ORDER_SAVE_PVT:Complete_RetOrder_Validate');
2650 END IF;
2651
2652 FND_MSG_PUB.Count_And_Get
2653 ( p_count => x_msg_count,
2654 p_data => x_msg_data
2655 );
2656
2657 EXCEPTION
2658 WHEN FND_API.G_EXC_ERROR THEN
2659 x_return_status := FND_API.G_RET_STS_ERROR;
2660 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
2661 p_count => x_msg_count ,
2662 p_data => x_msg_data);
2663 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2664 IBE_Util.Debug('Error IBE_ORDER_SAVE_PVT:Complete_RetOrder_Validate()'|| sqlerrm);
2665 END IF;
2666
2667 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2668 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2669 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
2670 p_count => x_msg_count ,
2671 p_data => x_msg_data);
2672 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2673 IBE_Util.Debug('UNEXPError IBE_ORDER_SAVE_PVT:Complete_RetOrder_Validate()' || sqlerrm);
2674 END IF;
2675
2676 WHEN OTHERS THEN
2677 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2678 IF FND_Msg_Pub.Check_Msg_Level( FND_Msg_Pub.G_MSG_LVL_UNEXP_ERROR ) THEN
2679 FND_Msg_Pub.Add_Exc_Msg(G_PKG_NAME,
2680 L_API_NAME);
2681 END IF;
2682
2683 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
2684 p_count => x_msg_count ,
2685 p_data => x_msg_data);
2686 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2687 IBE_Util.Debug('Other IBE_ORDER_SAVE_PVT:Complete_RetOrder_Validate()' || sqlerrm);
2688 END IF;
2689
2690 END Complete_RetOrder_Validate;
2691
2692
2693 PROCEDURE Save(
2694 p_api_version_number IN NUMBER
2695 ,p_init_msg_list IN VARCHAR2
2696 ,p_commit IN VARCHAR2
2697 ,p_order_header_rec IN OE_Order_PUB.Header_Rec_Type
2698 ,p_order_line_tbl IN OE_Order_PUB.Line_Tbl_Type
2699 ,p_submit_control_rec IN IBE_Order_W1_PVT.Control_Rec_Type
2700 ,p_save_type IN NUMBER
2701 ,p_party_id IN NUMBER
2702 ,p_shipto_partysite_id IN NUMBER
2703 ,p_billto_partysite_id IN NUMBER
2704 ,x_return_status OUT NOCOPY VARCHAR2
2705 ,x_msg_count OUT NOCOPY NUMBER
2706 ,x_msg_data OUT NOCOPY VARCHAR2
2707 ,x_order_header_id OUT NOCOPY NUMBER
2708 ,x_order_number OUT NOCOPY NUMBER
2709 ,x_flow_status_code OUT NOCOPY VARCHAR2
2710 ,x_last_update_date OUT NOCOPY DATE
2711 ,X_failed_line_ids OUT NOCOPY JTF_VARCHAR2_TABLE_300
2712 )
2713 IS
2714
2715 cursor c_holdid is
2716 select hold_id from oe_hold_definitions where name = 'STORE_HOLD';
2717
2718 cursor cr_persontype(cr_prtyid number) is
2719 select party_type from hz_parties where party_id = cr_prtyid;
2720
2721 l_order_header_rec OE_Order_PUB.Header_Rec_Type:= OE_Order_PUB.G_MISS_HEADER_REC;
2722
2723 l_Header_Adj_tbl OE_Order_PUB.Header_Adj_Tbl_Type := OE_Order_PUB.G_MISS_HEADER_ADJ_TBL;
2724 l_Header_Price_Att_tbl OE_Order_PUB.Header_Price_Att_Tbl_Type :=OE_Order_PUB.G_MISS_HEADER_PRICE_ATT_TBL;
2725 l_Header_Adj_Att_tbl OE_Order_PUB.Header_Adj_Att_Tbl_Type :=OE_Order_PUB.G_MISS_HEADER_ADJ_ATT_TBL;
2726 l_Header_Adj_Assoc_tbl OE_Order_PUB.Header_Adj_Assoc_Tbl_Type :=OE_Order_PUB.G_MISS_HEADER_ADJ_ASSOC_TBL;
2727 l_Header_Scredit_tbl OE_Order_PUB.Header_Scredit_Tbl_Type :=OE_Order_PUB.G_MISS_HEADER_SCREDIT_TBL;
2728
2729 l_Line_Adj_tbl OE_Order_PUB.Line_Adj_Tbl_Type :=OE_Order_PUB.G_MISS_LINE_ADJ_TBL;
2730 l_Line_price_Att_tbl OE_Order_PUB.Line_Price_Att_Tbl_Type :=OE_Order_PUB.G_MISS_LINE_PRICE_ATT_TBL;
2731 l_Line_Adj_Att_tbl OE_Order_PUB.Line_Adj_Att_Tbl_Type :=OE_Order_PUB.G_MISS_LINE_ADJ_ATT_TBL;
2732 l_Line_Adj_Assoc_tbl OE_Order_PUB.Line_Adj_Assoc_Tbl_Type :=OE_Order_PUB.G_MISS_LINE_ADJ_ASSOC_TBL;
2733 l_Line_Scredit_tbl OE_Order_PUB.Line_Scredit_Tbl_Type :=OE_Order_PUB.G_MISS_LINE_SCREDIT_TBL;
2734 l_Lot_Serial_tbl OE_Order_PUB.Lot_Serial_Tbl_Type := OE_Order_PUB.G_MISS_LOT_SERIAL_TBL;
2735 l_Action_Request_tbl OE_Order_PUB.request_tbl_type :=OE_Order_PUB.g_miss_request_tbl;
2736 l_header_val_rec OE_Order_PUB.Header_Val_Rec_Type;
2737
2738 l_lot_serial_val_tbl OE_Order_PUB.Lot_Serial_Val_Tbl_Type;
2739 l_header_scredit_val_tbl OE_Order_PUB.Header_Scredit_Val_Tbl_Type;
2740 l_order_line_tbl OE_Order_PUB.Line_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_TBL;
2741 l_line_val_tbl OE_Order_PUB.Line_Val_Tbl_Type;
2742 l_line_adj_val_tbl OE_Order_PUB.Line_Adj_Val_Tbl_Type;
2743 l_header_adj_val_tbl OE_Order_PUB.Header_Adj_Val_Tbl_Type;
2744 l_line_scredit_val_tbl OE_Order_PUB.Line_Scredit_Val_Tbl_Type;
2745 l_request_tbl OE_Order_PUB.Request_Tbl_Type :=OE_Order_PUB.G_MISS_REQUEST_TBL;
2746
2747 lx_Header_Adj_tbl OE_Order_PUB.Header_Adj_Tbl_Type;
2748 lx_Header_price_Att_tbl OE_Order_PUB.Header_Price_Att_Tbl_Type ;
2749 lx_Header_Adj_Att_tbl OE_Order_PUB.Header_Adj_Att_Tbl_Type ;
2750 lx_Header_Adj_Assoc_tbl OE_Order_PUB.Header_Adj_Assoc_Tbl_Type ;
2751 lx_Header_Scredit_tbl OE_Order_PUB.Header_Scredit_Tbl_Type;
2752 lx_Line_Adj_tbl OE_Order_PUB.Line_Adj_Tbl_Type;
2753 lx_Line_price_Att_tbl OE_Order_PUB.Line_Price_Att_Tbl_Type ;
2754 lx_Line_Adj_Att_tbl OE_Order_PUB.Line_Adj_Att_Tbl_Type ;
2755 lx_Line_Adj_Assoc_tbl OE_Order_PUB.Line_Adj_Assoc_Tbl_Type ;
2756 lx_Line_Scredit_tbl OE_Order_PUB.Line_Scredit_Tbl_Type;
2757 lx_Lot_Serial_tbl OE_Order_PUB.Lot_Serial_Tbl_Type;
2758 lx_order_header_rec OE_Order_PUB.Header_Rec_Type:= OE_Order_Pub.G_MISS_HEADER_REC;
2759 lx_line_tbl OE_Order_PUB.Line_Tbl_Type;
2760
2761 x_mergeline_rec OE_Order_PUB.Line_Rec_Type;
2762 lx_line_rec OE_Order_PUB.Line_rec_type;
2763 l_return_values varchar2(2000);
2764 l_api_name VARCHAR2(50) := 'Save_Order';
2765 l_api_version NUMBER := 1.0;
2766
2767 l_cancel_flow VARCHAR2(10) := FND_API.G_FALSE;
2768 l_reqtbl_count NUMBER;
2769 l_apply_hold VARCHAR2(10);
2770 l_hold_id NUMBER;
2771 l_user_type VARCHAR2(30);
2772 linetblcount NUMBER := 0;
2773 l_ProcessOrder_Flow VARCHAR2(10) := FND_API.G_TRUE;
2774 p_api_service_level VARCHAR2(30);
2775
2776 -- Delete Order when last Line deleted loop
2777 p_dl_line_ids VARCHAR2(3000);
2778 l_dl_line_id_qry VARCHAR2(3000);
2779 l_dl_tmp_qry VARCHAR2(4000);
2780 TYPE l_dl_tmp_type is REF CURSOR;
2781 l_dl_tmp l_dl_tmp_type;
2782 No_Of_ExistingLines NUMBER;
2783 l_flow_status_code VARCHAR2(30);
2784 l_last_update_date DATE;
2785 l_parseNum NUMBER :=5;
2786 l_parseKey varchar2(40) :='ORDER_SAVE_LINE_IDS';
2787
2788 /****TEMP***/
2789 --X_failed_line_ids JTF_VARCHAR2_TABLE_300;
2790 /******/
2791 BEGIN
2792
2793 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2794 IBE_Util.Debug('Begin IBE_ORDER_SAVE_PVT:Save(): ' || p_save_type);
2795 END IF;
2796
2797
2798 -- Standard Start of API savepoint
2799 SAVEPOINT SAVE_ORDER_SAVE_PVT;
2800
2801 -- Standard call to check for call compatibility.
2802 IF NOT FND_API.Compatible_API_Call (l_api_version,
2803 P_Api_Version_Number,
2804 l_api_name,
2805 G_PKG_NAME )
2806 THEN
2807 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2808 END IF;
2809
2810 -- Initialize message list if p_init_msg_list is set to TRUE.
2811 IF FND_API.To_Boolean( p_init_msg_list ) THEN
2812 FND_Msg_Pub.initialize;
2813 END IF;
2814
2815 -- Initialize API return status to success
2816 x_return_status := FND_API.G_RET_STS_SUCCESS;
2817
2818 -- Start OF API body --
2819
2820
2821 /********** Set Header Record info *************/
2822
2823 l_order_header_rec := p_order_header_rec;
2824
2825
2826 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2827 IBE_Util.Debug('Begin p_order_header_rec' ||p_order_header_rec.header_id ||'::'|| p_party_id);
2828 END IF;
2829
2830 -- Call Default Header Record
2831 DefaultHeaderRecord(l_order_header_rec
2832 ,p_party_id
2833 ,p_save_type
2834 ,lx_order_header_rec);
2835
2836 l_order_header_rec := lx_order_header_rec;
2837
2838 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2839 IBE_Util.Debug('Save:order_hdr_rec.operation: ' || l_order_header_rec.operation);
2840 IBE_Util.Debug('Save:order_hdr_rec.sold_to_org_id: ' || l_order_header_rec.SOLD_TO_ORG_ID);
2841 IBE_Util.Debug('Save:order_hdr_rec.ship_to_org_id: ' || l_order_header_rec.SHIP_TO_ORG_ID);
2842 IBE_Util.Debug('Save:order_hdr_rec.Invoice_to_org_id: ' || l_order_header_rec.INVOICE_TO_ORG_ID);
2843 IBE_Util.Debug('Save:order_hdr_rec.header_id: ' || l_order_header_rec.HEADER_ID);
2844 IBE_Util.Debug('Save:order_hdr_rec.order type id: ' || l_order_header_rec.ORDER_TYPE_ID);
2845 IBE_Util.Debug('Save:order_hdr_rec.org id: ' || l_order_header_rec.ORG_ID);
2846 IBE_Util.Debug('Save:order_hdr_rec.trans_curr_code: ' || l_order_header_rec.transactional_curr_code);
2847 IBE_Util.Debug('Save:order_hdr_rec.flow_stat_code: ' || l_order_header_rec.flow_status_code);
2848 IBE_Util.Debug('Save:order_hdr_rec.Minisite_Id: ' || l_order_header_rec.Minisite_Id);
2849 IBE_Util.Debug('Save:lx_order_header_rec.Minisite_Id: ' || lx_order_header_rec.Minisite_Id);
2850 END IF;
2851
2852
2853 /********** User Authentication *************/
2854
2855 IF (l_order_header_rec.header_id is not null AND
2856 l_order_header_rec.header_id <> FND_API.G_MISS_NUM)
2857 THEN
2858 ValidateOrderAccess(p_order_header_id => l_order_header_rec.header_id
2859 ,x_return_status => x_return_status
2860 );
2861 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) then
2862 raise FND_API.G_EXC_ERROR;
2863 END IF;
2864 END IF;
2865
2866
2867 /********** Set Line Record Info *************/
2868 -- Default Line Record
2869
2870 DefaultLineRecord(p_order_line_tbl => p_order_line_tbl
2871 ,p_order_header_rec => l_order_header_rec
2872 ,p_save_type => p_save_type
2873 ,x_order_line_tbl => l_order_line_tbl
2874 );
2875 IF ((l_order_header_rec.operation = OE_Globals.G_OPR_CREATE) OR
2876 (p_billto_partysite_id is not null AND p_billto_partysite_id <> FND_API.G_MISS_NUM) OR
2877 (p_shipto_partysite_id is not null AND p_shipto_partysite_id <> FND_API.G_MISS_NUM))
2878 THEN
2879 DefaultHdrLineAddress(p_order_line_tbl => l_order_line_tbl
2880 ,p_order_header_rec => l_order_header_rec
2881 ,p_party_id => p_party_id
2882 ,p_billto_partysite_id => p_billto_partysite_id
2883 ,p_shipto_partysite_id => p_shipto_partysite_id
2884 ,x_order_header_rec => lx_order_header_rec
2885 ,x_order_line_tbl => lx_line_tbl
2886 ,x_return_status => x_return_status
2887 ,x_msg_count => x_msg_count
2888 ,x_msg_data => x_msg_data
2889 );
2890
2891 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) then
2892 raise FND_API.G_EXC_ERROR;
2893 END IF;
2894
2895 IF (IBE_UTIL.G_DEBUGON = l_true) then
2896 Ibe_Util.DEBUG('ship_to_org_id returned: ' || lx_order_header_rec.ship_to_org_id);
2897 Ibe_Util.DEBUG('bill_to_org_id returned: ' || lx_order_header_rec.invoice_to_org_id);
2898 end if;
2899
2900 IF(lx_order_header_rec.invoice_to_org_id is null OR lx_order_header_rec.invoice_to_org_id = FND_API.G_MISS_NUM)
2901 THEN
2902 FND_Message.Set_Name('IBE', 'IBE_ERR_OT_SHIPTO_BILLTO_MISS');
2903 FND_Msg_Pub.Add;
2904 RAISE FND_API.G_EXC_ERROR;
2905 END IF;
2906
2907 l_order_header_rec := lx_order_header_rec;
2908 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2909 IBE_Util.Debug('Save :order_hdr_rec.ship_to_org_id: ' || l_order_header_rec.SHIP_TO_ORG_ID);
2910 IBE_Util.Debug('Save :order_hdr_rec.Invoice_to_org_id: ' || l_order_header_rec.INVOICE_TO_ORG_ID);
2911 IBE_Util.Debug('Save :order_hdr_rec.header_id: ' || l_order_header_rec.HEADER_ID);
2912
2913 END IF;
2914 FOR i in 1..lx_line_tbl.COUNT
2915 LOOP
2916 l_order_line_tbl(i) := lx_line_tbl(i);
2917 END LOOP;
2918
2919
2920 END IF; -- if operation = create
2921
2922
2923 -- This flow is for the B2C user,
2924 -- whenever he updates the Return, 'invoicetoorgid' at header level
2925 -- will be populated into lines, because when creating the Returns
2926 -- address would have been populated from referenced order.
2927 -- So that has to be changed to get it from Order.
2928
2929
2930 open cr_persontype(p_party_id);
2931 fetch cr_persontype into l_user_type;
2932 close cr_persontype;
2933
2934
2935 IF ((l_user_type = 'PERSON') AND
2936 (l_order_header_rec.operation = OE_Globals.G_OPR_UPDATE) AND
2937 (p_submit_control_rec.cancel_flag <> 'Y'))
2938 THEN
2939 IF (IBE_UTIL.G_DEBUGON = l_true) then
2940 Ibe_Util.DEBUG('calling SetLine ids API');
2941 END IF;
2942 SetLineShipInvoiceIds(
2943 p_order_header_rec => l_order_header_rec
2944 ,p_order_line_tbl => l_order_line_tbl
2945 ,x_order_line_tbl => lx_line_tbl);
2946
2947 for j in 1..lx_line_tbl.count
2948 loop
2949 l_order_line_tbl(j) := lx_line_tbl(j);
2950 IF (IBE_UTIL.G_DEBUGON = l_true) then
2951 Ibe_Util.DEBUG('Line Level Values after setting: '||l_order_line_tbl(j).line_id||' : '||
2952 l_order_line_tbl(j).ship_to_org_id||' : '||l_order_line_tbl(j).invoice_to_org_id);
2953 END IF;
2954 end loop;
2955
2956 END IF; -- if operation = update
2957
2958 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2959 FOR i in 1..l_order_line_tbl.COUNT
2960 LOOP
2961 IBE_Util.Debug('Save:line_tbl.operation: ' || l_order_line_tbl(i).OPERATION);
2962 IBE_Util.Debug('Save:line_tbl.line_id: ' || l_order_line_tbl(i).LINE_ID);
2963 IBE_Util.Debug('Save:line_tbl.line_type_id: ' || l_order_line_tbl(i).LINE_TYPE_ID);
2964 IBE_Util.Debug('Save:line_tbl.qty: ' || l_order_line_tbl(i).ORDERED_QUANTITY);
2965 IBE_Util.Debug('Save:line_tbl.inv to org id: ' || l_order_line_tbl(i).INVOICE_TO_ORG_ID);
2966 IBE_Util.Debug('Save:line_tbl.ship to org id: ' || l_order_line_tbl(i).SHIP_TO_ORG_ID);
2967 IBE_Util.Debug('Save:line_tbl.return_context: ' || l_order_line_tbl(i).RETURN_CONTEXT);
2968 IBE_Util.Debug('Save:line_tbl.line categ: ' || l_order_line_tbl(i).LINE_CATEGORY_CODE);
2969 IBE_Util.Debug('Save:line_tbl.ret atr1: ' || l_order_line_tbl(i).return_attribute1);
2970 IBE_Util.Debug('Save:line_tbl.ret atr2: ' || l_order_line_tbl(i).return_attribute2);
2971 IBE_Util.Debug('Save:line_tbl.rcode: ' || l_order_line_tbl(i).return_reason_code);
2972 END LOOP;
2973 END IF;
2974
2975
2976 /************ Hard Delete logic for Pending Return ****/
2977
2978 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2979 IBE_Util.Debug('ibe_order_save_pvt:cancel_flag ' ||p_submit_control_rec.cancel_flag);
2980 END IF;
2981
2982 if (p_submit_control_rec.cancel_flag = 'Y') then
2983 CancelOrder(l_order_header_rec,lx_order_header_rec);
2984 l_order_header_rec := lx_order_header_rec;
2985 IF (IBE_UTIL.G_DEBUGON = l_true) then
2986 ibe_util.debug('after cancelorder header_id:' ||l_order_header_rec.header_id);
2987 ibe_util.debug('after cancelorder operation:' ||l_order_header_rec.operation);
2988 end if;
2989
2990 IF(l_order_header_rec.operation = OE_Globals.G_OPR_DELETE) THEN
2991 l_cancel_flow := FND_API.G_TRUE;
2992 END IF;
2993
2994 end if;
2995
2996 /************ Model Items Logic ******************************/
2997
2998 -- Call SaveMDLRelatedOperations() if not submit or cancel
2999 -- This to propagate parent level changes to all children if a
3000 -- MDL item is updated/ removed.
3001 -- This is used currently by the Return-Flow-Orders alone.
3002
3003 IF (l_order_header_rec.operation = OE_GLOBALS.G_OPR_UPDATE AND
3004 (p_submit_control_rec.submit_flag is null
3005 OR p_submit_control_rec.submit_flag = FND_API.G_MISS_CHAR))
3006 THEN
3007 IF l_order_header_rec.order_category_code = 'RETURN' then
3008 SaveMDLRelatedOperations(p_context_type => 'SAVE',
3009 p_order_line_tbl => l_order_line_tbl,
3010 p_order_header_id => l_order_header_rec.header_id,
3011 p_save_type => p_save_type,
3012 x_order_line_tbl => lx_line_tbl
3013 );
3014
3015 for i in 1..lx_line_tbl.count
3016 loop
3017 l_order_line_tbl(i) := lx_line_tbl(i);
3018 end loop;
3019
3020 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3021 IBE_Util.Debug('LineTbl cnt returned from SAveMDL API:'||l_order_line_tbl.count);
3022 END IF;
3023 END IF; -- order_category_code = 'RETURN' check
3024 END IF; -- main if for savemdlrel... call
3025
3026
3027 /***************** Delete The Order When Last Item is Deleted bug#3272947 **************/
3028
3029 IF (l_order_header_rec.order_category_code = 'RETURN'
3030 AND p_save_type = SAVE_REMOVEITEMS) THEN
3031
3032 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3033 IBE_Util.debug('Inside Last Item Removal-Hard Delete If Loop');
3034 end if;
3035
3036 for lineIdx in 1..l_order_line_tbl.count
3037 loop
3038 if(l_order_line_tbl(lineIdx).operation=OE_GLOBALS.G_OPR_DELETE) THEN
3039 p_dl_line_ids := p_dl_line_ids || ','||l_order_line_tbl(lineIdx).LINE_ID;
3040 end if;
3041 end loop;
3042
3043 IBE_LEAD_IMPORT_PVT.parseInput (p_dl_line_ids, 'CHAR', l_parseKey, l_parseNum, l_dl_line_id_qry);
3044
3045 l_dl_tmp_qry := 'SELECT count(*) from oe_order_lines_all '||
3046 'WHERE header_id= :dl_header_id '||
3047 'AND line_id NOT IN('||l_dl_line_id_qry||')';
3048
3049 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3050 IBE_Util.debug('qry for finding if last item is removed: '|| l_dl_tmp_qry);
3051 end if;
3052
3053 open l_dl_tmp for l_dl_tmp_qry using p_order_header_rec.header_id,l_parseKey;
3054 fetch l_dl_tmp into No_Of_ExistingLines;
3055 close l_dl_tmp;
3056
3057 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3058 IBE_Util.debug('No. Of lines Existing in the ReturnOrder: '|| l_dl_tmp_qry);
3059 end if;
3060
3061 IF (No_Of_ExistingLines = 0) THEN -- No More Lines in The Return Order so can be hard deleted.
3062 CancelOrder(l_order_header_rec,lx_order_header_rec);
3063 l_order_header_rec := lx_order_header_rec;
3064 IF (IBE_UTIL.G_DEBUGON = l_true) then
3065 ibe_util.debug('after Remove-Cancelorder header_id:' ||l_order_header_rec.header_id);
3066 ibe_util.debug('after Remove-cancelorder operation:' ||l_order_header_rec.operation);
3067 end if;
3068
3069 IF(l_order_header_rec.operation = OE_Globals.G_OPR_DELETE) THEN
3070 l_cancel_flow := FND_API.G_TRUE;
3071 l_order_line_tbl := OE_ORDER_PUB.G_MISS_LINE_TBL;
3072 END IF;
3073 END IF; --No Of Existing Lines
3074
3075 ENd If; --Main If
3076
3077
3078
3079 /****************** ORDER Booking FLOW ******************/
3080
3081 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3082 IBE_Util.Debug('Save:submit-control_rec.submit_flag ' || p_submit_control_rec.submit_flag);
3083 END IF;
3084
3085 IF p_submit_control_rec.submit_flag = 'Y' THEN
3086
3087 -- If it is 'post 11590' OE flow and if it is a Return:
3088 -- New, Submit_Order() API should be called for booking it.
3089 -- SO validate the qty, before calling submit_order().
3090
3091 IF (IBE_UTIL.G_DEBUGON = l_true) then
3092 IBE_UTIL.DEBUG('order_category_code: ' ||l_order_header_rec.order_category_code );
3093 IBE_UTIL.DEBUG('OE-Get_Code_Release_Level ' || OE_CODE_CONTROL.Get_Code_Release_Level);
3094 end if;
3095
3096 -- As per Bug# 3522453, a new profile is introduced to drive pre-booking approval
3097 -- flows. So check for flow_status_code if it is "WORKING", then go with
3098 -- validations flow; else if it is ENTERED, go with Process_Order() API call.
3099 -- So get the flow_status_code of the ReturnOrder to be submitted.
3100
3101
3102 Get_Order_Status(p_header_id => l_order_header_rec.header_id
3103 ,x_order_status => l_flow_status_code
3104 ,x_last_update_date => l_last_update_date);
3105
3106 IF (IBE_UTIL.G_DEBUGON = l_true) then
3107 IBE_UTIL.DEBUG('SAVE - Pending Return flow_status_code: ' ||l_flow_status_code);
3108 end if;
3109
3110 IF (l_order_header_rec.order_category_code = 'RETURN' AND
3111 l_flow_status_code = 'WORKING') THEN
3112
3113 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3114 IBE_Util.Debug('Before Complete Validate API');
3115 END IF;
3116
3117 Complete_RetOrder_Validate(
3118 P_order_header_id => l_order_header_rec.header_id
3119 ,p_user_type => l_user_type
3120 ,X_failed_line_ids => X_failed_line_ids
3121 ,x_return_status => x_return_status
3122 ,x_msg_count => x_msg_count
3123 ,x_msg_data => x_msg_data
3124 );
3125
3126 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3127 IBE_Util.Debug('After Complete Validate API: '|| x_return_status);
3128 END IF;
3129
3130 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3131 raise FND_API.G_EXC_ERROR;
3132 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3133 raise FND_API.G_EXC_UNEXPECTED_ERROR;
3134 END IF;
3135
3136 -- For post 11590 flow, submit_order() flow would be called directly for booking.
3137 -- But for a b2c user, the header level values(invoice_to_org_id) would be
3138 -- populated for the return lines during submit flow.
3139 -- So Process_Order() should be called before submit_order().
3140 -- For B2B flow, submit_order() could be called directly.
3141 -- Checking whether it is a B2C flow.
3142
3143 IF ((l_user_type = 'PERSON') AND
3144 (l_order_header_rec.operation = OE_Globals.G_OPR_UPDATE) AND
3145 (p_submit_control_rec.cancel_flag <> 'Y')) THEN
3146
3147 l_ProcessOrder_Flow := FND_API.G_TRUE;
3148 ELSIF ((l_user_type = 'PARTY_RELATIONSHIP') AND -- b2b user
3149 (l_order_header_rec.operation = OE_Globals.G_OPR_UPDATE) AND
3150 (p_submit_control_rec.cancel_flag <> 'Y')) THEN
3151
3152 /* bug 8303137, scnagara, Added this condition for a b2b user so that when quantity
3153 is updated in Review and Submit return page, and Submit Return is clicked, quantity needs
3154 to be saved to db */
3155 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3156 IBE_Util.Debug('B2b user - require update to be performed');
3157 END IF;
3158 l_ProcessOrder_Flow := FND_API.G_TRUE;
3159 ELSE
3160 l_ProcessOrder_Flow := FND_API.G_FALSE;
3161 End If; --user_type ='Person'
3162
3163 Else
3164 l_request_tbl(1).entity_code := OE_GLOBALS.G_ENTITY_HEADER;
3165 l_request_tbl(1).request_type := OE_GLOBALS.G_BOOK_ORDER;
3166 End If;
3167
3168 END IF; -- booking submitflag='Y'
3169
3170 -- bug# 3069333
3171 OE_STANDARD_WF.SAVE_MESSAGES_OFF;
3172
3173 IF (IBE_UTIL.G_DEBUGON = l_true) then
3174 IBE_UTIL.DEBUG('l_ProcessOrder_Flow: ' || l_ProcessOrder_Flow );
3175 end if;
3176
3177 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3178 IBE_Util.Debug('Save Before Process Order:order_hdr_rec.ship_to_org_id: ' || l_order_header_rec.SHIP_TO_ORG_ID);
3179 IBE_Util.Debug('Save Bef Process Ord:order_hdr_rec.Invoice_to_org_id: ' || l_order_header_rec.INVOICE_TO_ORG_ID);
3180 IBE_Util.Debug('Save Bef Process Ord:order_hdr_rec.header_id: ' || l_order_header_rec.HEADER_ID);
3181 END IF;
3182
3183 IF(FND_API.to_Boolean(l_ProcessOrder_Flow)) THEN
3184
3185 /*************--call process order;--*****************/
3186
3187 IF (IBE_UTIL.G_DEBUGON = l_true) then
3188 IBE_UTIL.DEBUG('Save - Before Calling Process_Order()');
3189 end if;
3190
3191 OE_Order_GRP.Process_Order
3192 ( p_api_version_number => 1.0
3193 , p_init_msg_list => FND_API.G_TRUE
3194 , p_return_values => l_return_values
3195 , p_commit => FND_API.G_FALSE
3196 , x_return_status => x_return_status
3197 , x_msg_count => x_msg_count
3198 , x_msg_data => x_msg_data
3199 --, p_api_service_level => p_api_service_level
3200 , p_header_rec => l_order_header_rec
3201 , p_Header_Adj_tbl => l_header_adj_tbl
3202 , p_Header_price_Att_tbl => l_header_price_att_tbl
3203 , p_Header_Adj_Att_tbl => l_header_adj_att_tbl
3204 , p_Header_Adj_Assoc_tbl => l_header_adj_assoc_tbl
3205 , p_Header_Scredit_tbl => l_header_scredit_tbl
3206 , p_line_tbl => l_order_line_tbl
3207 , p_Line_Adj_tbl => l_line_adj_tbl
3208 , p_Line_price_Att_tbl => l_line_price_att_tbl
3209 , p_Line_Adj_Att_tbl => l_Line_Adj_Att_tbl
3210 , p_Line_Adj_Assoc_tbl => l_line_adj_assoc_tbl
3211 , p_Line_Scredit_tbl => l_line_scredit_tbl
3212 , p_Lot_Serial_tbl => l_lot_serial_tbl
3213 , p_Action_Request_tbl => l_request_tbl
3214 , x_header_rec => lx_order_header_rec
3215 , x_header_val_rec => l_header_val_rec
3216 , x_Header_Adj_tbl => lx_header_adj_tbl
3217 , x_Header_Adj_val_tbl => l_header_adj_val_tbl
3218 , x_Header_price_Att_tbl => lx_header_price_att_tbl
3219 , x_Header_Adj_Att_tbl => lx_header_adj_att_tbl
3220 , x_Header_Adj_Assoc_tbl => lx_header_adj_assoc_tbl
3221 , x_Header_Scredit_tbl => lx_header_scredit_tbl
3222 , x_Header_Scredit_val_tbl => l_header_scredit_val_tbl
3223 , x_line_tbl => lx_line_tbl
3224 , x_line_val_tbl => l_line_val_tbl
3225 , x_Line_Adj_tbl => lx_line_adj_tbl
3226 , x_Line_Adj_val_tbl => l_line_adj_val_tbl
3227 , x_Line_price_Att_tbl => lx_line_price_att_tbl
3228 , x_Line_Adj_Att_tbl => lx_line_adj_att_tbl
3229 , x_Line_Adj_Assoc_tbl => lx_line_adj_assoc_tbl
3230 , x_Line_Scredit_tbl => lx_line_scredit_tbl
3231 , x_Line_Scredit_val_tbl => l_line_scredit_val_tbl
3232 , x_Lot_Serial_tbl => lx_lot_serial_tbl
3233 , x_Lot_Serial_val_tbl => l_lot_serial_val_tbl
3234 , x_action_request_tbl => l_action_request_tbl
3235 );
3236
3237 IF (IBE_UTIL.G_DEBUGON = l_true) then
3238 IBE_UTIL.DEBUG('Save After Calling Process_Order() : ' || x_return_status);
3239 IBE_UTIL.DEBUG('header id from OE Save: ' || lx_order_header_rec.header_id);
3240 end if;
3241
3242 for j in 1 .. x_msg_count
3243 loop
3244 x_msg_data:= OE_MSG_PUB.get(fnd_msg_pub.g_next,FND_API.G_FALSE);
3245 IF (IBE_UTIL.G_DEBUGON = l_true) then
3246 IBE_UTIL.DEBUG('Message from OE Save: ' || x_msg_data );
3247 end if;
3248 end loop;
3249
3250 -- This check is for catching Booking Related Exceptions Only.
3251 -- In post 11590 flow, for B2c user, the process_order() would be called
3252 -- before submit_order(). So in the following if condition, the
3253 -- first 2 condns would be satisfied. But for the third condn.,
3254 -- l_action_request_tbl will be empty as we are not sending l_request_tbl()
3255 -- params above. So its safer to check for Exists and chk the return_status.
3256
3257 IF (x_return_status = FND_API.G_RET_STS_SUCCESS
3258 AND p_submit_control_rec.submit_flag = 'Y'
3259 AND (l_action_request_tbl.EXISTS(1) AND
3260 l_action_request_tbl(1).return_status <> FND_API.G_RET_STS_SUCCESS))
3261 THEN
3262 IF (IBE_UTIL.G_DEBUGON = l_true) then
3263 IBE_UTIL.DEBUG('Error in ProcessOrder Booking Flow' );
3264 end if;
3265 retrieve_oe_messages;
3266 x_return_status := FND_API.G_RET_STS_ERROR;
3267 RAISE FND_API.G_EXC_ERROR;
3268 END IF;
3269
3270 -- This check is for catching all generic Exceptions.
3271 IF x_return_status <> FND_API.G_RET_STS_SUCCESS then
3272 IF (IBE_UTIL.G_DEBUGON = l_true) then
3273 IBE_UTIL.DEBUG('Error in Procees Order Flow: '||x_return_status );
3274 end if;
3275 retrieve_oe_messages;
3276 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3277 raise FND_API.G_EXC_ERROR;
3278 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3279 raise FND_API.G_EXC_UNEXPECTED_ERROR;
3280 END IF;
3281 END IF;
3282
3283 x_order_header_id := lx_order_header_rec.header_id;
3284 x_last_update_date := lx_order_header_rec.last_update_date;
3285 x_order_number := lx_order_header_rec.order_number;
3286 x_flow_status_code := lx_order_header_rec.flow_status_code;
3287
3288 END If; --processOrderflow
3289
3290 IF((p_submit_control_rec.submit_flag = 'Y')
3291 AND(l_order_header_rec.order_category_code = 'RETURN' AND
3292 l_flow_status_code = 'WORKING'))THEN
3293
3294 IF (IBE_UTIL.G_DEBUGON = l_true) then
3295 IBE_UTIL.DEBUG('Before Calling OE_RMA_GRP.Submit_Order');
3296 end if;
3297
3298 OE_RMA_GRP.Submit_Order( p_api_version => 1.0
3299 , p_header_id => l_order_header_rec.header_id
3300 , x_return_status => x_return_status
3301 , x_msg_count => x_msg_count
3302 , x_msg_data => x_msg_data);
3303
3304 IF (IBE_UTIL.G_DEBUGON = l_true) then
3305 IBE_UTIL.DEBUG('After Calling Submit_Order - return_stats: '||x_return_status);
3306 IBE_UTIL.DEBUG('x_msg_count: '||x_msg_count);
3307 end if;
3308
3309 --for logging appropriate error message
3310 if(x_msg_count is not null AND x_msg_count > 0) then
3311 for j in 1 .. x_msg_count
3312 loop
3313 x_msg_data:= OE_MSG_PUB.get(fnd_msg_pub.g_next,FND_API.G_FALSE);
3314 IF (IBE_UTIL.G_DEBUGON = l_true) then
3315 IBE_UTIL.DEBUG('Message from OE Save: ' || x_msg_data );
3316 end if;
3317 end loop;
3318 end if;
3319
3320 IF x_return_status <> FND_API.G_RET_STS_SUCCESS then
3321 retrieve_oe_messages;
3322 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3323 raise FND_API.G_EXC_ERROR;
3324 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3325 raise FND_API.G_EXC_UNEXPECTED_ERROR;
3326 END IF;
3327 END IF;
3328 x_order_header_id := l_order_header_rec.header_id;
3329
3330 -- We need to send back order_number and last_update_date back to UI.
3331 -- As OE is not sending these, we are populating.
3332 Declare
3333 cursor c_submit_hdrattr(sh_hdr_id number) is
3334 select order_number,last_update_date,flow_status_code
3335 from oe_order_headers_all where header_id=sh_hdr_id;
3336 Begin
3337 open c_submit_hdrattr(x_order_header_id);
3338 fetch c_submit_hdrattr into x_order_number,x_last_update_date,x_flow_status_code;
3339 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3340 IBE_UTIL.DEBUG('order number after submit: ' || x_order_number );
3341 END IF;
3342 close c_submit_hdrattr;
3343 End;
3344
3345 END If;
3346
3347 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3348 IBE_UTIL.DEBUG('header id returned from OE: ' || x_order_header_id );
3349 IBE_UTIL.DEBUG('x_flow_status_code: ' || x_flow_status_code );
3350 IBE_UTIL.DEBUG('calling ibe_active_quotes_all package handler: ' || l_cancel_flow);
3351 END IF;
3352
3353 IF (FND_API.to_Boolean(l_cancel_flow)
3354 OR ((p_save_type = SAVE_NORMAL AND p_submit_control_rec.submit_flag = 'Y')
3355 -- AND (l_action_request_tbl(1).return_status = FND_API.G_RET_STS_SUCCESS)
3356 ))
3357 THEN
3358 DeactivateOrder(p_party_id,l_order_header_rec.sold_to_org_id,l_order_header_rec.transactional_curr_code);
3359 ELSE
3360 ActivateOrder(x_order_header_id,p_party_id,l_order_header_rec.sold_to_org_id,
3361 l_order_header_rec.transactional_curr_code);
3362 END IF;
3363
3364 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3365 IBE_UTIL.DEBUG('back from ibe_active_quotes_all package handler');
3366 END IF;
3367
3368 -- Calling Notification to the user After successfully booking the order.
3369 -- This would send a notification to the user about the conformation of the Return.
3370
3371 IF (p_save_type = SAVE_NORMAL AND p_submit_control_rec.submit_flag = 'Y') THEN
3372
3373 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3374 IBE_UTIL.debug('IBE_ORDER_SAVE_PVT: Before calling Notification API');
3375 END IF;
3376
3377 IBE_WORKFLOW_PVT.NotifyReturnOrderStatus(
3378 p_api_version => 1,
3379 p_party_id => p_party_id,
3380 p_order_header_id => x_order_header_id,
3381 p_errmsg_count => 0,
3382 p_errmsg_data => NULL,
3383 x_return_status => x_return_status,
3384 x_msg_count => x_msg_count,
3385 x_msg_data => x_msg_data
3386 );
3387 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3388 IBE_UTIL.debug('IBE_ORDER_SAVE_PVT: Notification API is called');
3389 END IF;
3390
3391 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3392 RAISE FND_API.G_EXC_ERROR;
3393 END IF;
3394
3395 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3396 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3397 END IF;
3398
3399 END IF; -- IF (p_save_type....)
3400
3401
3402 --
3403 -- End of API body
3404 --
3405
3406 -- Standard check for p_commit
3407 IF FND_API.to_Boolean( p_commit )
3408 THEN
3409 COMMIT WORK;
3410 END IF;
3411
3412 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3413 IBE_Util.Debug('End OE_ORDER_SAVE_PVT:Save)');
3414 END IF;
3415
3416 -- Standard call to get message count and if count is 1, get message info.
3417
3418 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
3419 p_data => x_msg_data);
3420
3421 EXCEPTION
3422 WHEN FND_API.G_EXC_ERROR THEN
3423 ROLLBACK TO SAVE_ORDER_SAVE_PVT;
3424 x_return_status := FND_API.G_RET_STS_ERROR;
3425 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
3426 p_count => x_msg_count ,
3427 p_data => x_msg_data);
3428 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3429 IBE_Util.Debug('Error IBE_ORDER_SAVE_PVT:Save()'|| sqlerrm);
3430 END IF;
3431
3432 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3433 ROLLBACK TO SAVE_ORDER_SAVE_PVT;
3434 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3435 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
3436 p_count => x_msg_count ,
3437 p_data => x_msg_data);
3438 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3439 IBE_Util.Debug('UNEXPECTEDError IBE_ORDER_SAVE_PVT:Save()' || sqlerrm);
3440 END IF;
3441 WHEN OTHERS THEN
3442 ROLLBACK TO SAVE_ORDER_SAVE_PVT;
3443 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3444
3445 IF FND_Msg_Pub.Check_Msg_Level( FND_Msg_Pub.G_MSG_LVL_UNEXP_ERROR ) THEN
3446 FND_Msg_Pub.Add_Exc_Msg(G_PKG_NAME,
3447 L_API_NAME);
3448 END IF;
3449
3450 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
3451 p_count => x_msg_count ,
3452 p_data => x_msg_data);
3453 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3454 IBE_Util.Debug('Other IBE_ORDER_SAVE_PVT:Save()' || sqlerrm);
3455 END IF;
3456
3457
3458 END Save;
3459
3460 PROCEDURE CheckConstraint(
3461 p_api_version_number IN NUMBER
3462 ,p_init_msg_list IN VARCHAR2
3463 ,p_commit IN VARCHAR2
3464 ,p_order_header_rec IN OE_Order_PUB.Header_Rec_Type
3465 ,p_order_line_tbl IN OE_Order_PUB.Line_Tbl_Type
3466 ,p_submit_control_rec IN IBE_Order_W1_PVT.Control_Rec_Type
3467 ,p_combine_same_lines IN VARCHAR2
3468 ,p_party_id IN NUMBER
3469 ,x_return_status OUT NOCOPY VARCHAR2
3470 ,x_msg_count OUT NOCOPY NUMBER
3471 ,x_msg_data OUT NOCOPY VARCHAR2
3472 ,x_error_lineids OUT NOCOPY JTF_VARCHAR2_TABLE_300
3473 ,x_last_update_date OUT NOCOPY DATE
3474 )
3475 IS
3476
3477 /*********** API Flow ***********/
3478
3479 -- Refer Bug# 2988993 For Details
3480 -- Process_Order() would be called twice
3481 -- First Call, would create a Return Header without lines.
3482 -- Now the second call would use this SAme Header_id created in the first call.
3483 -- Next, set the Control Record and a new Header Record(l_retplcy_orderhdr_rec)
3484 -- with HEaderId created above and opcode 'UPDATE".
3485 -- Call Process_Order() API again, with l_retplcy_orderhdr_rec, ControlRec and
3486 -- Line_Table.
3487 -- This second call would set Api_Service_Level param as 'Check_Security_Only'.
3488 -- Second call check the Return Policy and send the failing LineIds back.
3489 -- Later when OM release bug# 2988993, then these 2 Porcess_Order() API call
3490 -- could be removed and HeaderRecord, LineTable and ControlRecord could be sent
3491 -- together in a Single Call.
3492
3493
3494 l_order_header_rec OE_Order_PUB.Header_Rec_Type := OE_Order_PUB.G_MISS_HEADER_REC;
3495 l_retplcy_orderhdr_rec OE_Order_PUB.Header_Rec_Type := OE_Order_PUB.G_MISS_HEADER_REC;
3496 l_request_tbl OE_Order_PUB.Request_Tbl_Type := OE_Order_PUB.G_MISS_REQUEST_TBL;
3497 l_order_line_tbl OE_Order_PUB.Line_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_TBL;
3498 l_control_rec OE_GLOBALS.Control_Rec_Type;
3499 l_header_val_rec OE_Order_PUB.Header_Val_Rec_Type;
3500 l_header_scredit_val_tbl OE_Order_PUB.Header_Scredit_Val_Tbl_Type;
3501 l_header_adj_val_tbl OE_Order_PUB.Header_Adj_Val_Tbl_Type;
3502 l_line_val_tbl OE_Order_PUB.Line_Val_Tbl_Type;
3503 l_line_adj_val_tbl OE_Order_PUB.Line_Adj_Val_Tbl_Type;
3504 l_line_scredit_val_tbl OE_Order_PUB.Line_Scredit_Val_Tbl_Type;
3505 l_lot_serial_val_tbl OE_Order_PUB.Lot_Serial_Val_Tbl_Type;
3506 l_Action_Request_tbl OE_Order_PUB.request_tbl_type :=OE_Order_PUB.g_miss_request_tbl;
3507
3508 lx_Header_Adj_tbl OE_Order_PUB.Header_Adj_Tbl_Type;
3509 lx_Header_price_Att_tbl OE_Order_PUB.Header_Price_Att_Tbl_Type ;
3510 lx_Header_Adj_Att_tbl OE_Order_PUB.Header_Adj_Att_Tbl_Type ;
3511 lx_Header_Adj_Assoc_tbl OE_Order_PUB.Header_Adj_Assoc_Tbl_Type ;
3512 lx_Header_Scredit_tbl OE_Order_PUB.Header_Scredit_Tbl_Type;
3513 lx_Line_Adj_tbl OE_Order_PUB.Line_Adj_Tbl_Type;
3514 lx_Line_price_Att_tbl OE_Order_PUB.Line_Price_Att_Tbl_Type ;
3515 lx_Line_Adj_Att_tbl OE_Order_PUB.Line_Adj_Att_Tbl_Type ;
3516 lx_Line_Adj_Assoc_tbl OE_Order_PUB.Line_Adj_Assoc_Tbl_Type ;
3517 lx_Line_Scredit_tbl OE_Order_PUB.Line_Scredit_Tbl_Type;
3518 lx_Lot_Serial_tbl OE_Order_PUB.Lot_Serial_Tbl_Type;
3519 lx_order_header_rec OE_Order_PUB.Header_Rec_Type;
3520 lx_line_tbl OE_Order_PUB.Line_Tbl_Type;
3521
3522 l_tmp_line_id varchar2(100) := '';
3523 l_line_id number;
3524 l_line_return_status varchar2(10);
3525 l_line_return_attr2 number;
3526 l_return_values varchar2(4000);
3527 l_api_name VARCHAR2(100) := 'Check Constraints';
3528 l_api_version NUMBER := 1.0;
3529
3530 cursor c_get_lineInfo(l_lineId Number) is
3531 select * from oe_order_lines_all where line_id = l_lineId;
3532
3533 l_line_rec OE_Order_PUB.Line_Rec_Type;
3534 l_line_type_id NUMBER;
3535 l_siteuse_id NUMBER;
3536
3537 TYPE Fail_TmpLineRec IS TABLE OF VARCHAR2(240)
3538 INDEX BY BINARY_INTEGER;
3539
3540 Fail_TmpLineRec_Tbl Fail_TmpLineRec;
3541
3542 l_count NUMBER := 0;
3543 l_salesrep_id VARCHAR2(360);
3544 l_order_type_id NUMBER;
3545 l_commit VARCHAR2(10):=FND_API.G_FALSE;
3546
3547 l_salesrep_number VARCHAR2(360); --MOAC Changes by ASO::Obsoletion of ASO_DEFAULT_PERSON_ID
3548 l_user_orgid NUMBER;
3549
3550
3551 BEGIN
3552
3553 x_error_lineids := JTF_VARCHAR2_TABLE_300();
3554
3555 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3556 IBE_Util.Debug('Begin IBE_ORDER_SAVE_PVT:CheckConstraints()');
3557 END IF;
3558
3559 -- Standard Start of API savepoint
3560 SAVEPOINT ORDER_CHKCONSTRAINT;
3561
3562 -- Standard call to check for call compatibility.
3563 IF NOT FND_API.Compatible_API_Call (l_api_version,
3564 P_Api_Version_Number,
3565 l_api_name,
3566 G_PKG_NAME )
3567 THEN
3568 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3569 END IF;
3570
3571 -- Initialize message list if p_init_msg_list is set to TRUE.
3572 IF FND_API.To_Boolean( p_init_msg_list ) THEN
3573 FND_Msg_Pub.initialize;
3574 END IF;
3575
3576 -- Initialize API return status to success
3577 x_return_status := FND_API.G_RET_STS_SUCCESS;
3578
3579
3580 -- Start OF API body --
3581 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3582 IBE_Util.Debug('In chkconstraint()');
3583 END IF;
3584
3585 -- SET HEADER RECORD
3586 l_order_header_rec := p_order_header_rec;
3587 l_order_header_rec.operation := OE_GLOBALS.G_OPR_CREATE;
3588 -- l_order_header_rec.flow_status_code := 'ENTERED';
3589 --l_order_header_rec.ORDER_CATEGORY_CODE := 'RETURN';
3590 l_order_type_id := FND_PROFILE.VALUE('IBE_RETURN_TRANSACTION_TYPE');
3591
3592 IF (l_order_type_id is null) THEN
3593 FND_Message.Set_Name('IBE', 'IBE_ERR_OT_ORDER_TYPE_ID_MISS');
3594 FND_Msg_Pub.Add;
3595 RAISE FND_API.G_EXC_ERROR;
3596 ELSE
3597 l_order_header_rec.order_type_id := l_order_type_id;
3598 END IF;
3599
3600 -- Get the User's Session ORG_ID
3601 l_user_orgid := mo_global.GET_CURRENT_ORG_ID();
3602 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3603 Ibe_util.Debug('Current Org id : ' ||l_user_orgid);
3604 END IF;
3605
3606 -- Get the Sales Rep Number from the ASO Utility package
3607 l_salesrep_number := ASO_UTILITY_PVT.GET_OU_ATTRIBUTE_VALUE(aso_utility_pvt.GET_DEFAULT_SALESREP,l_user_orgid);
3608 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3609 Ibe_util.Debug('Sales Rep Number for Current Org : ' ||l_salesrep_number);
3610 END IF;
3611
3612 -- Bug 5359687, Proper error message when default salesrep is not set
3613 IF (l_salesrep_number is null) THEN
3614 FND_Message.Set_Name('IBE', 'IBE_ERR_OT_SALESREPID_MISS');
3615 FND_Msg_Pub.Add;
3616 RAISE FND_API.G_EXC_ERROR;
3617 ELSE
3618 -- Get the sales rep id from the sales rep number using the JTF table
3619 select SALESREP_ID into l_salesrep_id from JTF_RS_SALESREPS where SALESREP_NUMBER = l_salesrep_number and ORG_ID = l_user_orgid;
3620 END IF;
3621
3622 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3623 Ibe_util.Debug('Sales Rep Id : ' ||l_salesrep_id);
3624 END IF;
3625
3626 --l_salesrep_id := FND_PROFILE.VALUE('ASO_DEFAULT_PERSON_ID');
3627
3628 IF (l_salesrep_id is null) THEN
3629 FND_Message.Set_Name('IBE', 'IBE_ERR_OT_SALESREPID_MISS');
3630 FND_Msg_Pub.Add;
3631 RAISE FND_API.G_EXC_ERROR;
3632 ELSE
3633 l_order_header_rec.salesrep_id := l_salesrep_id;
3634 END IF;
3635
3636 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3637 IBE_Util.Debug('chkconstraint-l_order_header_rec.header_id:' || l_order_header_rec.header_id);
3638 IBE_Util.Debug('chkconstraint-l_order_header_rec.sold_to_org_id:' || l_order_header_rec.sold_to_org_id);
3639 IBE_Util.Debug('chkconstraint-l_order_header_rec.operation:' || l_order_header_rec.operation);
3640 IBE_Util.Debug('chkconstraint-l_order_header_rec.order_type_id:' || l_order_header_rec.order_type_id);
3641 END IF;
3642
3643
3644 -- SET LINE RECORD
3645 FOR i in 1..p_order_line_tbl.COUNT
3646 LOOP
3647 l_order_line_tbl(i) := p_order_line_tbl(i);
3648
3649 DefaultLineTypes(l_order_header_rec.order_type_id,l_line_type_id);
3650 l_order_line_tbl(i).line_type_id := l_line_type_id;
3651
3652 if(l_order_header_rec.order_category_code = 'RETURN') then
3653 l_order_line_tbl(i).line_category_code := 'RETURN';
3654 l_order_line_tbl(i).return_context := 'ORDER';
3655 end if;
3656
3657 l_order_line_tbl(i).operation := OE_GLOBALS.G_OPR_CREATE;
3658
3659
3660 -- ShipTo / InvoiceTo OrgIds.
3661 -- line level will always be defaulted from original order lines.
3662
3663 for l_line_rec in c_get_lineInfo(l_order_line_tbl(i).return_attribute2)
3664 loop
3665 l_order_line_tbl(i).ship_to_org_id := l_line_rec.SHIP_TO_ORG_ID;
3666 l_order_line_tbl(i).invoice_to_org_id := l_line_rec.INVOICE_TO_ORG_ID;
3667 end loop;
3668
3669 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3670 IBE_Util.Debug('chkconstraint-l_order_line_tbl.lineId: ' || l_order_line_tbl(i).line_id);
3671 IBE_Util.Debug('chkconstraint-l_order_line_tbl.linetypeid: ' || l_order_line_tbl(i).line_type_id);
3672 IBE_Util.Debug('chkconstraint-l_order_line_tbl.shiptoOrgid: ' || l_order_line_tbl(i).ship_to_org_id);
3673 IBE_Util.Debug('chkconstraint-l_order_line_tbl.invoicetoOrgid: ' || l_order_line_tbl(i).invoice_to_org_id);
3674 END IF;
3675
3676 END LOOP;
3677
3678 -- Header Level shipTo /InvoiceTo org ids
3679
3680 DefaultFromLineSiteId(l_order_line_tbl,p_party_id,'SHIP_TO',l_siteuse_id);
3681 l_order_header_rec.ship_to_org_id := l_siteuse_id;
3682 DefaultFromLineSiteId(l_order_line_tbl,p_party_id,'INVOICE_TO',l_siteuse_id);
3683 l_order_header_rec.invoice_to_org_id := l_siteuse_id;
3684
3685 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3686 IBE_Util.Debug('Chkconsrnt-l_order_header_rec.shiporgid: ' || l_order_header_rec.ship_to_org_id);
3687 IBE_Util.Debug('Chkconsrnt-l_order_header_rec.invoiceorgid: ' || l_order_header_rec.invoice_to_org_id);
3688 END IF;
3689
3690 -- FIRST CALL To Process_Order to create only the Header Record. Bug#2988993
3691 -- This would avoid the NoDataFound thrown from OM as reported in the bug.
3692
3693 OE_Order_GRP.Process_Order
3694 ( p_api_version_number => 1.0
3695 , p_init_msg_list => FND_API.G_TRUE
3696 , p_return_values => l_return_values
3697 , p_commit => FND_API.G_FALSE
3698 , x_return_status => x_return_status
3699 , x_msg_count => x_msg_count
3700 , x_msg_data => x_msg_data
3701 , p_control_rec => l_control_rec
3702 , p_header_rec => l_order_header_rec
3703 , p_Action_Request_tbl => l_request_tbl
3704 , x_header_rec => lx_order_header_rec
3705 , x_header_val_rec => l_header_val_rec
3706 , x_Header_Adj_tbl => lx_header_adj_tbl
3707 , x_Header_Adj_val_tbl => l_header_adj_val_tbl
3708 , x_Header_price_Att_tbl => lx_header_price_att_tbl
3709 , x_Header_Adj_Att_tbl => lx_header_adj_att_tbl
3710 , x_Header_Adj_Assoc_tbl => lx_header_adj_assoc_tbl
3711 , x_Header_Scredit_tbl => lx_header_scredit_tbl
3712 , x_Header_Scredit_val_tbl => l_header_scredit_val_tbl
3713 , x_line_tbl => lx_line_tbl
3714 , x_line_val_tbl => l_line_val_tbl
3715 , x_Line_Adj_tbl => lx_line_adj_tbl
3716 , x_Line_Adj_val_tbl => l_line_adj_val_tbl
3717 , x_Line_price_Att_tbl => lx_line_price_att_tbl
3718 , x_Line_Adj_Att_tbl => lx_line_adj_att_tbl
3719 , x_Line_Adj_Assoc_tbl => lx_line_adj_assoc_tbl
3720 , x_Line_Scredit_tbl => lx_line_scredit_tbl
3721 , x_Line_Scredit_val_tbl => l_line_scredit_val_tbl
3722 , x_Lot_Serial_tbl => lx_lot_serial_tbl
3723 , x_Lot_Serial_val_tbl => l_lot_serial_val_tbl
3724 , x_action_request_tbl => l_action_request_tbl
3725 );
3726
3727 IF (IBE_UTIL.G_DEBUGON = l_true) then
3728 IBE_UTIL.DEBUG('Return Status of First-Call to Process_Order() : ' || x_return_status);
3729 IBE_UTIL.DEBUG('header id from OE: ' || lx_order_header_rec.header_id);
3730 end if;
3731
3732 IF x_return_status <> FND_API.G_RET_STS_SUCCESS then
3733 retrieve_oe_messages;
3734 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3735 raise FND_API.G_EXC_ERROR;
3736 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3737 raise FND_API.G_EXC_UNEXPECTED_ERROR;
3738 END IF;
3739 END IF;
3740
3741 --SET HEADER RECORD
3742 l_retplcy_orderhdr_rec.header_id := lx_order_header_rec.header_id;
3743 l_retplcy_orderhdr_rec.operation := OE_Globals.G_OPR_UPDATE;
3744
3745 -- SET CONTROL RECORD
3746 l_control_rec.controlled_operation := TRUE;
3747 l_control_rec.process_partial := TRUE;
3748
3749 -- PLEASE NOTE:
3750 -- process_partial should be TRUE.
3751 -- If FALSE, when in case if first line fails for Return Policy then all the
3752 -- following lines are also sent back as failing Lines from OM
3753
3754 FOR i in 1..l_order_line_tbl.COUNT
3755 LOOP
3756 l_order_line_tbl(i).header_id := lx_order_header_rec.header_id;
3757 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3758 IBE_Util.Debug('l_order_line_tbl.lineId: ' || l_order_line_tbl(i).line_id);
3759 IBE_Util.Debug('l_order_line_tbl.HEaderId: ' || l_order_line_tbl(i).header_id);
3760 IBE_Util.Debug('l_order_line_tbl.retattr1: ' || l_order_line_tbl(i).return_attribute1);
3761 IBE_Util.Debug('l_order_line_tbl.retattr2: ' || l_order_line_tbl(i).return_attribute2);
3762 IBE_Util.Debug('l_order_line_tbl.retcontxt: ' || l_order_line_tbl(i).return_context);
3763 IBE_Util.Debug('l_order_line_tbl.linetypeid: ' || l_order_line_tbl(i).line_type_id);
3764 IBE_Util.Debug('l_order_line_tbl.operation: ' || l_order_line_tbl(i).operation);
3765 IBE_Util.Debug('l_order_line_tbl.shiptoOrgid: ' || l_order_line_tbl(i).ship_to_org_id);
3766 IBE_Util.Debug('l_order_line_tbl.invoicetoOrgid: ' || l_order_line_tbl(i).invoice_to_org_id);
3767 END IF;
3768 END LOOP;
3769
3770
3771 -- Now call Process_Order API for Policy check.
3772
3773 OE_Order_GRP.Process_Order
3774 ( p_api_version_number => 1.0
3775 , p_init_msg_list => FND_API.G_TRUE
3776 , p_return_values => l_return_values
3777 , p_commit => FND_API.G_FALSE
3778 , x_return_status => x_return_status
3779 , x_msg_count => x_msg_count
3780 , x_msg_data => x_msg_data
3781 , p_api_service_level => OE_GLOBALS.G_CHECK_SECURITY_ONLY
3782 , p_control_rec => l_control_rec
3783 , p_header_rec => l_retplcy_orderhdr_rec
3784 , p_line_tbl => l_order_line_tbl
3785 , p_Action_Request_tbl => l_request_tbl
3786 , x_header_rec => lx_order_header_rec
3787 , x_header_val_rec => l_header_val_rec
3788 , x_Header_Adj_tbl => lx_header_adj_tbl
3789 , x_Header_Adj_val_tbl => l_header_adj_val_tbl
3790 , x_Header_price_Att_tbl => lx_header_price_att_tbl
3791 , x_Header_Adj_Att_tbl => lx_header_adj_att_tbl
3792 , x_Header_Adj_Assoc_tbl => lx_header_adj_assoc_tbl
3793 , x_Header_Scredit_tbl => lx_header_scredit_tbl
3794 , x_Header_Scredit_val_tbl => l_header_scredit_val_tbl
3795 , x_line_tbl => lx_line_tbl
3796 , x_line_val_tbl => l_line_val_tbl
3797 , x_Line_Adj_tbl => lx_line_adj_tbl
3798 , x_Line_Adj_val_tbl => l_line_adj_val_tbl
3799 , x_Line_price_Att_tbl => lx_line_price_att_tbl
3800 , x_Line_Adj_Att_tbl => lx_line_adj_att_tbl
3801 , x_Line_Adj_Assoc_tbl => lx_line_adj_assoc_tbl
3802 , x_Line_Scredit_tbl => lx_line_scredit_tbl
3803 , x_Line_Scredit_val_tbl => l_line_scredit_val_tbl
3804 , x_Lot_Serial_tbl => lx_lot_serial_tbl
3805 , x_Lot_Serial_val_tbl => l_lot_serial_val_tbl
3806 , x_action_request_tbl => l_action_request_tbl
3807 );
3808
3809 x_error_lineids := JTF_VARCHAR2_TABLE_300();
3810
3811 for j in 1..lx_line_tbl.count
3812 loop
3813 l_line_id := lx_line_tbl(j).line_id;
3814 l_line_return_status := lx_line_tbl(j).return_status;
3815 l_line_return_attr2 := lx_line_tbl(j).return_attribute2;
3816
3817 If(l_line_return_status <> 'S')then
3818 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3819 IBE_UTIL.DEBUG('line id returned from OE' || l_line_id );
3820 IBE_UTIL.DEBUG('return status returned from OE' || l_line_return_status);
3821 IBE_UTIL.DEBUG('return attribute2 returned from OE' || l_line_return_attr2);
3822 END IF;
3823
3824 l_count := l_count + 1;
3825 Fail_TmpLineRec_Tbl(l_count) := lx_line_tbl(j).return_attribute2;
3826 end if;
3827 end loop;
3828
3829 l_count := Fail_TmpLineRec_Tbl.count;
3830 if (l_count > 0) then
3831 x_error_lineids.extend(l_count);
3832 for k in 1..l_count
3833 loop
3834 null;
3835 x_error_lineids(k) := Fail_TmpLineRec_Tbl(k);
3836 end loop;
3837 end if;
3838
3839 IF (IBE_UTIL.G_DEBUGON = l_true) then
3840 IBE_Util.debug('ChkConstraint Error Messages count: ' || x_msg_count ||' : '||x_return_status);
3841 end if;
3842
3843 for j in 1 .. x_msg_count
3844 loop
3845 x_msg_data:= OE_MSG_PUB.get(fnd_msg_pub.g_next,FND_API.G_FALSE);
3846 IF (IBE_UTIL.G_DEBUGON = l_true) then
3847 IBE_Util.debug('Chk Constraint Exception Message' || x_msg_data);
3848 end if;
3849 end loop;
3850
3851 IF x_return_status <> FND_API.G_RET_STS_SUCCESS then
3852 retrieve_oe_messages;
3853 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3854 raise FND_API.G_EXC_ERROR;
3855 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3856 raise FND_API.G_EXC_UNEXPECTED_ERROR;
3857 END IF;
3858 END IF;
3859
3860 --
3861 -- End of API body
3862 --
3863 -- Bug#2988993
3864 -- As the First Process_Order() API call would create an Order Header Record,
3865 -- This Rollback is a must.
3866
3867 ROLLBACK TO ORDER_CHKCONSTRAINT;
3868 l_commit := p_commit;
3869
3870 -- Standard check for p_commit
3871 IF FND_API.to_Boolean(l_commit)
3872 THEN
3873 COMMIT WORK;
3874 END IF;
3875
3876 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3877 IBE_Util.Debug('End OE_ORDER_SAVE_PVT:CHECKCONSTRAINT()');
3878 END IF;
3879
3880 -- Standard call to get message count and if count is 1, get message info.
3881 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
3882 p_data => x_msg_data);
3883
3884
3885 EXCEPTION
3886 WHEN FND_API.G_EXC_ERROR THEN
3887 ROLLBACK TO ORDER_CHKCONSTRAINT;
3888 x_return_status := FND_API.G_RET_STS_ERROR;
3889 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
3890 p_count => x_msg_count ,
3891 p_data => x_msg_data);
3892 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3893 IBE_Util.Debug('Error IBE_ORDER_SAVE_PVT:CHECKCONSTRAINT()'|| sqlerrm);
3894 END IF;
3895
3896 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3897 ROLLBACK TO ORDER_CHKCONSTRAINT;
3898 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3899 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
3900 p_count => x_msg_count ,
3901 p_data => x_msg_data);
3902 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3903 IBE_Util.Debug('UNEXPECTEDError IBE_ORDER_SAVE_PVT:CHECKCONSTRAINT()' || sqlerrm);
3904 END IF;
3905
3906 WHEN OTHERS THEN
3907 ROLLBACK TO ORDER_CHKCONSTRAINT;
3908 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3909 IF FND_Msg_Pub.Check_Msg_Level( FND_Msg_Pub.G_MSG_LVL_UNEXP_ERROR ) THEN
3910 FND_Msg_Pub.Add_Exc_Msg(G_PKG_NAME,
3911 L_API_NAME);
3912 END IF;
3913
3914 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
3915 p_count => x_msg_count ,
3916 p_data => x_msg_data);
3917 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3918 IBE_Util.Debug('Other IBE_ORDER_SAVE_PVT:CHECKCONSTRAINT()' || sqlerrm);
3919 END IF;
3920
3921 END CheckConstraint;
3922
3923
3924 PROCEDURE UpdateLineShippingBilling(
3925 p_api_version_number IN NUMBER
3926 ,p_init_msg_list IN VARCHAR2
3927 ,p_commit IN VARCHAR2
3928 ,x_return_status OUT NOCOPY VARCHAR2
3929 ,x_msg_count OUT NOCOPY NUMBER
3930 ,x_msg_data OUT NOCOPY VARCHAR2
3931 ,p_order_header_id IN NUMBER
3932 ,p_order_line_id IN NUMBER
3933 ,p_billto_party_id IN NUMBER
3934 ,p_billto_cust_acct_id IN NUMBER
3935 ,p_billto_party_site_id IN NUMBER
3936 ,p_shipto_party_id IN NUMBER
3937 ,p_shipto_cust_acct_id IN NUMBER
3938 ,p_shipto_party_site_id IN NUMBER
3939 ,p_last_update_date IN DATE
3940 )
3941 IS
3942
3943 --l_acct_siteuse_id NUMBER := null;
3944 l_billto_acct_siteuse_id NUMBER := null;
3945 l_shipto_acct_siteuse_id NUMBER := null;
3946 l_return_values varchar2(2000);
3947 l_api_version NUMBER := 1.0;
3948 l_api_name VARCHAR2(30) := 'ORDER_UPDATELINEBILL';
3949
3950 l_order_header_rec OE_Order_PUB.Header_Rec_Type := OE_Order_PUB.G_MISS_HEADER_REC;
3951 l_order_line_tbl OE_Order_PUB.Line_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_TBL;
3952 l_request_tbl OE_Order_PUB.Request_Tbl_Type := OE_Order_PUB.G_MISS_REQUEST_TBL;
3953 l_control_rec OE_GLOBALS.Control_Rec_Type;
3954 l_header_val_rec OE_Order_PUB.Header_Val_Rec_Type;
3955 l_header_scredit_val_tbl OE_Order_PUB.Header_Scredit_Val_Tbl_Type;
3956 l_header_adj_val_tbl OE_Order_PUB.Header_Adj_Val_Tbl_Type;
3957 l_line_val_tbl OE_Order_PUB.Line_Val_Tbl_Type;
3958 l_line_adj_val_tbl OE_Order_PUB.Line_Adj_Val_Tbl_Type;
3959 l_line_scredit_val_tbl OE_Order_PUB.Line_Scredit_Val_Tbl_Type;
3960 l_lot_serial_val_tbl OE_Order_PUB.Lot_Serial_Val_Tbl_Type;
3961 l_Action_Request_tbl OE_Order_PUB.request_tbl_type :=OE_Order_PUB.g_miss_request_tbl;
3962
3963 lx_Header_Adj_tbl OE_Order_PUB.Header_Adj_Tbl_Type;
3964 lx_Header_price_Att_tbl OE_Order_PUB.Header_Price_Att_Tbl_Type ;
3965 lx_Header_Adj_Att_tbl OE_Order_PUB.Header_Adj_Att_Tbl_Type ;
3966 lx_Header_Adj_Assoc_tbl OE_Order_PUB.Header_Adj_Assoc_Tbl_Type ;
3967 lx_Header_Scredit_tbl OE_Order_PUB.Header_Scredit_Tbl_Type;
3968 lx_Line_Adj_tbl OE_Order_PUB.Line_Adj_Tbl_Type;
3969 lx_Line_price_Att_tbl OE_Order_PUB.Line_Price_Att_Tbl_Type ;
3970 lx_Line_Adj_Att_tbl OE_Order_PUB.Line_Adj_Att_Tbl_Type ;
3971 lx_Line_Adj_Assoc_tbl OE_Order_PUB.Line_Adj_Assoc_Tbl_Type ;
3972 lx_Line_Scredit_tbl OE_Order_PUB.Line_Scredit_Tbl_Type;
3973 lx_Lot_Serial_tbl OE_Order_PUB.Lot_Serial_Tbl_Type;
3974 lx_order_header_rec OE_Order_PUB.Header_Rec_Type;
3975 lx_line_tbl OE_Order_PUB.Line_Tbl_Type;
3976 --l_siteuse_type VARCHAR2(20);
3977 l_siteuse_billto VARCHAR2(20);
3978 l_siteuse_shipto VARCHAR2(20);
3979 l_cust_acct_role_id NUMBER;
3980 l_flow_status_code VARCHAR2(30);
3981 l_last_update_date DATE;
3982
3983 BEGIN
3984
3985 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3986 IBE_Util.Debug('Begin IBE_ORDER_SAVE_PVT:UpdateLinebilling()');
3987 END IF;
3988
3989 -- Standard Start of API savepoint
3990 SAVEPOINT ORDER_UPDTLINEBILL;
3991
3992 -- Standard call to check for call compatibility.
3993 IF NOT FND_API.Compatible_API_Call (l_api_version,
3994 P_Api_Version_Number,
3995 l_api_name,
3996 G_PKG_NAME )
3997 THEN
3998 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3999 END IF;
4000
4001 -- Initialize message list if p_init_msg_list is set to TRUE.
4002 IF FND_API.To_Boolean( p_init_msg_list ) THEN
4003 FND_Msg_Pub.initialize;
4004 END IF;
4005
4006 -- Initialize API return status to success
4007 x_return_status := FND_API.G_RET_STS_SUCCESS;
4008
4009 -- Start OF API body --
4010
4011 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4012 IBE_Util.Debug('Calling Validate Order Access');
4013 END IF;
4014
4015 /********** User Authentication *************/
4016
4017 IF (p_order_header_id is not null AND
4018 p_order_header_id <> FND_API.G_MISS_NUM)
4019 THEN
4020 ValidateOrderAccess(p_order_header_id => p_order_header_id
4021 ,x_return_status => x_return_status
4022 );
4023 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) then
4024 raise FND_API.G_EXC_ERROR;
4025 END IF;
4026 END IF;
4027
4028 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4029 Ibe_util.Debug('checking the Return status');
4030 END IF;
4031
4032
4033 Get_Order_Status(p_header_id => p_order_header_id
4034 ,x_order_status => l_flow_status_code
4035 ,x_last_update_date => l_last_update_date);
4036
4037 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4038 Ibe_util.Debug('last_update_date sent from ui: '||p_last_update_date);
4039 Ibe_util.Debug('last_update_date from db: '||l_last_update_date);
4040 Ibe_util.Debug('l_flow_status_code: '||l_flow_status_code);
4041 END IF;
4042
4043 IF ((l_flow_status_code = 'BOOKED' OR l_flow_status_code='CLOSED' OR
4044 l_flow_status_code = 'CANCELLED')
4045 OR l_last_update_date > p_last_update_date)
4046 THEN
4047 FND_Message.Set_Name('IBE', 'IBE_ERR_OT_REFRESH_RETURN');
4048 FND_Msg_Pub.Add;
4049 RAISE FND_API.G_EXC_ERROR;
4050 END IF;
4051
4052
4053 IF (p_billto_cust_acct_id is not null AND p_billto_cust_acct_id <> FND_API.G_MISS_NUM
4054 AND p_billto_party_site_id is not null AND p_billto_party_site_id <> FND_API.G_MISS_NUM)
4055 THEN
4056 --l_siteuse_type := 'BILL_TO';
4057 l_siteuse_billto := 'BILL_TO';
4058 END IF;
4059
4060 IF (p_shipto_cust_acct_id is not null AND p_shipto_cust_acct_id <> FND_API.G_MISS_NUM
4061 AND p_shipto_party_site_id is not null AND p_shipto_party_site_id <> FND_API.G_MISS_NUM)
4062 THEN
4063 --l_siteuse_type := 'SHIP_TO';
4064 l_siteuse_shipto := 'SHIP_TO';
4065 END IF;
4066
4067 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4068 IBE_Util.Debug('UpdateLinebilling-l_siteuse_billto: '||l_siteuse_billto||' : l_siteuse_shipto: '||l_siteuse_shipto);
4069 END IF;
4070
4071 -- Fetching Invoice To Org Id.
4072
4073 if(l_siteuse_billto = 'BILL_TO') then
4074
4075 IBE_CUSTOMER_ACCT_PVT.Get_Cust_Account_Site_Use(
4076 p_cust_acct_id => p_billto_cust_acct_id
4077 ,p_party_id => p_billto_party_id
4078 ,p_siteuse_type => l_siteuse_billto
4079 ,p_partysite_id => p_billto_party_site_id
4080 ,x_siteuse_id => l_billto_acct_siteuse_id
4081 ,x_return_status => x_return_status
4082 ,x_msg_count => x_msg_count
4083 ,x_msg_data => x_msg_data
4084 );
4085
4086 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
4087 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4088 IBE_Util.Debug('UpdateLinebilling- Get_Cust_Account_Site_Use() fails raise exception');
4089 END IF;
4090
4091 FND_Message.Set_Name('IBE', 'IBE_ERR_OT_INVALID_BILLTO_ADDR');
4092 FND_Msg_Pub.Add;
4093 RAISE FND_API.G_EXC_ERROR;
4094 END IF;
4095
4096 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4097 IBE_Util.Debug('UpdateLinebilling-l_acct_siteuse_id: ' || l_billto_acct_siteuse_id);
4098 END IF;
4099 end if;
4100
4101 if(l_siteuse_shipto = 'SHIP_TO') then
4102
4103 IBE_CUSTOMER_ACCT_PVT.Get_Cust_Account_Site_Use(
4104 p_cust_acct_id => p_shipto_cust_acct_id
4105 ,p_party_id => p_shipto_party_id
4106 ,p_siteuse_type => l_siteuse_shipto
4107 ,p_partysite_id => p_shipto_party_site_id
4108 ,x_siteuse_id => l_shipto_acct_siteuse_id
4109 ,x_return_status => x_return_status
4110 ,x_msg_count => x_msg_count
4111 ,x_msg_data => x_msg_data
4112 );
4113
4114 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
4115 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4116 IBE_Util.Debug('UpdateLinebilling- Get_Cust_Account_Site_Use() fails raise exception');
4117 END IF;
4118
4119 FND_Message.Set_Name('IBE', 'IBE_ERR_OT_INVALID_SHIPTO_ADDR');
4120 FND_Msg_Pub.Add;
4121 RAISE FND_API.G_EXC_ERROR;
4122 END IF;
4123 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4124 IBE_Util.Debug('UpdateLinebilling-l_acct_siteuse_id: ' || l_shipto_acct_siteuse_id);
4125 END IF;
4126
4127 end if;
4128
4129 l_order_header_rec.header_id := p_order_header_id;
4130 l_order_header_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
4131 l_order_line_tbl(1) := OE_Order_PUB.G_MISS_LINE_REC;
4132 l_order_line_tbl(1).header_id := p_order_header_id;
4133 l_order_line_tbl(1).operation := OE_GLOBALS.G_OPR_UPDATE;
4134 l_order_line_tbl(1).line_id := p_order_line_id;
4135
4136 if(l_siteuse_billto = 'BILL_TO') then
4137 l_order_line_tbl(1).invoice_to_org_id := l_billto_acct_siteuse_id;
4138 end if;
4139 if(l_siteuse_shipto = 'SHIP_TO') then
4140 l_order_line_tbl(1).ship_to_org_id := l_shipto_acct_siteuse_id;
4141 end if;
4142
4143 -- Fetching InvoiceToContactId
4144 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4145 IBE_Util.Debug('billto_ party_id :: billto_custacct_id: '||p_billto_party_id||' :: '||p_billto_cust_acct_id);
4146 END IF;
4147
4148 If(p_billto_party_id is not null AND
4149 p_billto_party_id <> FND_API.G_MISS_NUM AND
4150 l_siteuse_billto = 'BILL_TO') then
4151
4152 IBE_CUSTOMER_ACCT_PVT.Get_Cust_Acct_Role(
4153 p_party_id => p_billto_party_id
4154 ,p_acctsite_type => 'BILL_TO'
4155 ,p_sold_to_orgid => p_billto_cust_acct_id
4156 ,p_custacct_siteuse_id => l_order_line_tbl(1).invoice_to_org_id
4157 ,x_cust_acct_role_id => l_cust_acct_role_id
4158 ,x_return_status => x_return_status
4159 ,x_msg_count => x_msg_count
4160 ,x_msg_data => x_msg_data
4161 );
4162
4163 l_order_line_tbl(1).invoice_to_contact_id := l_cust_acct_role_id;
4164 end if;
4165
4166 if(p_shipto_party_id is not null AND
4167 p_shipto_party_id <> FND_API.G_MISS_NUM AND
4168 l_siteuse_shipto = 'SHIP_TO') then
4169
4170 IBE_CUSTOMER_ACCT_PVT.Get_Cust_Acct_Role(
4171 p_party_id => p_shipto_party_id
4172 ,p_acctsite_type => 'SHIP_TO'
4173 ,p_sold_to_orgid => p_shipto_cust_acct_id
4174 ,p_custacct_siteuse_id => l_order_line_tbl(1).ship_to_org_id
4175 ,x_cust_acct_role_id => l_cust_acct_role_id
4176 ,x_return_status => x_return_status
4177 ,x_msg_count => x_msg_count
4178 ,x_msg_data => x_msg_data
4179 );
4180 l_order_line_tbl(1).ship_to_contact_id := l_cust_acct_role_id;
4181 end if;
4182
4183
4184 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4185 IBE_Util.Debug('Line Id: '||l_order_line_tbl(1).line_id);
4186 IBE_Util.Debug('Line operation: ' ||l_order_line_tbl(1).operation);
4187 IBE_Util.Debug('Line invoiceToOrgId: ' ||l_order_line_tbl(1).invoice_to_org_id);
4188 IBE_Util.Debug('Line InvoiceToContactId: '||l_order_line_tbl(1).invoice_to_contact_id);
4189 ENd If;
4190
4191 SaveMDLRelatedOperations(p_context_type => 'UPDATELINES',
4192 p_order_line_tbl => l_order_line_tbl,
4193 p_order_header_id => p_order_header_id,
4194 x_order_line_tbl => lx_line_tbl
4195 );
4196
4197 for i in 1..lx_line_tbl.count
4198 loop
4199 l_order_line_tbl(i) := lx_line_tbl(i);
4200 end loop;
4201
4202 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4203 IBE_Util.Debug('UpdateLine Linetbl cnt b4 processOrder '||l_order_line_tbl.count);
4204 IBE_Util.Debug('check this: '||l_order_line_tbl(1).LINE_ID);
4205 IBE_Util.Debug('check this: '||l_order_line_tbl(1).INVOICE_TO_ORG_ID);
4206 IBE_Util.Debug('check this: '||l_order_line_tbl(1).INVOICE_TO_CONTACT_ID);
4207 IBE_Util.Debug('check this: '||l_order_line_tbl(1).SHIP_TO_ORG_ID);
4208 IBE_Util.Debug('check this: '||l_order_line_tbl(1).SHIP_TO_CONTACT_ID);
4209 IBE_Util.Debug('check this: '||l_order_line_tbl(1).OPERATION);
4210 END IF;
4211
4212 -- Calling Process Order
4213 OE_Order_GRP.Process_Order
4214 ( p_api_version_number => 1.0
4215 , p_init_msg_list => FND_API.G_TRUE
4216 , p_return_values => l_return_values
4217 , p_commit => FND_API.G_FALSE
4218 , x_return_status => x_return_status
4219 , x_msg_count => x_msg_count
4220 , x_msg_data => x_msg_data
4221 , p_control_rec => l_control_rec
4222 , p_header_rec => l_order_header_rec
4223 , p_line_tbl => l_order_line_tbl
4224 , p_Action_Request_tbl => l_request_tbl
4225 , x_header_rec => lx_order_header_rec
4226 , x_header_val_rec => l_header_val_rec
4227 , x_Header_Adj_tbl => lx_header_adj_tbl
4228 , x_Header_Adj_val_tbl => l_header_adj_val_tbl
4229 , x_Header_price_Att_tbl => lx_header_price_att_tbl
4230 , x_Header_Adj_Att_tbl => lx_header_adj_att_tbl
4231 , x_Header_Adj_Assoc_tbl => lx_header_adj_assoc_tbl
4232 , x_Header_Scredit_tbl => lx_header_scredit_tbl
4233 , x_Header_Scredit_val_tbl => l_header_scredit_val_tbl
4234 , x_line_tbl => lx_line_tbl
4235 , x_line_val_tbl => l_line_val_tbl
4236 , x_Line_Adj_tbl => lx_line_adj_tbl
4237 , x_Line_Adj_val_tbl => l_line_adj_val_tbl
4238 , x_Line_price_Att_tbl => lx_line_price_att_tbl
4239 , x_Line_Adj_Att_tbl => lx_line_adj_att_tbl
4240 , x_Line_Adj_Assoc_tbl => lx_line_adj_assoc_tbl
4241 , x_Line_Scredit_tbl => lx_line_scredit_tbl
4242 , x_Line_Scredit_val_tbl => l_line_scredit_val_tbl
4243 , x_Lot_Serial_tbl => lx_lot_serial_tbl
4244 , x_Lot_Serial_val_tbl => l_lot_serial_val_tbl
4245 , x_action_request_tbl => l_action_request_tbl
4246 );
4247
4248 IF (IBE_UTIL.G_DEBUGON = l_true) then
4249 IBE_UTIL.DEBUG('Return status from OE updatelinebill: ' || x_return_status);
4250 IBE_UTIL.DEBUG('header id from OE updatelinebill: ' || lx_order_header_rec.header_id);
4251 end if;
4252
4253 for j in 1 .. x_msg_count
4254 loop
4255 x_msg_data:= OE_MSG_PUB.get(fnd_msg_pub.g_next,FND_API.G_FALSE);
4256 IF (IBE_UTIL.G_DEBUGON = l_true) then
4257 IBE_UTIL.DEBUG('Message from OE update line bill: ' || x_msg_data );
4258 end if;
4259 end loop;
4260
4261 IF x_return_status <> FND_API.G_RET_STS_SUCCESS then
4262 retrieve_oe_messages;
4263 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
4264 raise FND_API.G_EXC_ERROR;
4265 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4266 raise FND_API.G_EXC_UNEXPECTED_ERROR;
4267 END IF;
4268 END IF;
4269
4270 --
4271 -- End of API body
4272 --
4273
4274 -- Standard check for p_commit
4275 IF FND_API.to_Boolean( p_commit )
4276 THEN
4277 COMMIT WORK;
4278 END IF;
4279
4280 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4281 IBE_Util.Debug('End IBE_ORDER_SAVE_PVT:UpdateLinebilling()');
4282 END IF;
4283
4284 -- Standard call to get message count and if count is 1, get message info.
4285 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
4286 p_data => x_msg_data);
4287
4288 EXCEPTION
4289 WHEN FND_API.G_EXC_ERROR THEN
4290 ROLLBACK TO ORDER_UPDTLINEBILL;
4291 x_return_status := FND_API.G_RET_STS_ERROR;
4292 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
4293 p_count => x_msg_count ,
4294 p_data => x_msg_data);
4295 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4296 IBE_Util.Debug('Error IBE_ORDER_SAVE_PVT:UpdateLineShippingBilling()'|| sqlerrm);
4297 END IF;
4298
4299 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4300 ROLLBACK TO ORDER_UPDTLINEBILL;
4301 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4302 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
4303 p_count => x_msg_count ,
4304 p_data => x_msg_data);
4305 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4306 IBE_Util.Debug('UNEXPECTEDErr IBE_ORDER_SAVE_PVT:UpdateLineShippingBilling()' || sqlerrm);
4307 END IF;
4308
4309 WHEN OTHERS THEN
4310 ROLLBACK TO ORDER_UPDTLINEBILL;
4311 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4312 IF FND_Msg_Pub.Check_Msg_Level( FND_Msg_Pub.G_MSG_LVL_UNEXP_ERROR ) THEN
4313 FND_Msg_Pub.Add_Exc_Msg(G_PKG_NAME,
4314 L_API_NAME);
4315 END IF;
4316
4317 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
4318 p_count => x_msg_count ,
4319 p_data => x_msg_data);
4320 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4321 IBE_Util.Debug('OtherExc IBE_ORDER_SAVE_PVT:UpdateLineShippingBilling()' || sqlerrm);
4322 END IF;
4323
4324 END UpdateLineShippingBilling;
4325
4326 END IBE_Order_Save_pvt;
[Home] [Help]