1 PACKAGE BODY IBE_Order_Save_pvt AS
2 /* $Header: IBEVORDB.pls 120.5 2006/06/30 11:58:44 akhgupta 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 END IF;
2849
2850
2851 /********** User Authentication *************/
2852
2853 IF (l_order_header_rec.header_id is not null AND
2854 l_order_header_rec.header_id <> FND_API.G_MISS_NUM)
2855 THEN
2856 ValidateOrderAccess(p_order_header_id => l_order_header_rec.header_id
2857 ,x_return_status => x_return_status
2858 );
2859 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) then
2860 raise FND_API.G_EXC_ERROR;
2861 END IF;
2862 END IF;
2863
2864
2865 /********** Set Line Record Info *************/
2866 -- Default Line Record
2867
2868 DefaultLineRecord(p_order_line_tbl => p_order_line_tbl
2869 ,p_order_header_rec => l_order_header_rec
2870 ,p_save_type => p_save_type
2871 ,x_order_line_tbl => l_order_line_tbl
2872 );
2873 IF ((l_order_header_rec.operation = OE_Globals.G_OPR_CREATE) OR
2874 (p_billto_partysite_id is not null AND p_billto_partysite_id <> FND_API.G_MISS_NUM) OR
2875 (p_shipto_partysite_id is not null AND p_shipto_partysite_id <> FND_API.G_MISS_NUM))
2876 THEN
2877 DefaultHdrLineAddress(p_order_line_tbl => l_order_line_tbl
2878 ,p_order_header_rec => l_order_header_rec
2879 ,p_party_id => p_party_id
2880 ,p_billto_partysite_id => p_billto_partysite_id
2881 ,p_shipto_partysite_id => p_shipto_partysite_id
2882 ,x_order_header_rec => lx_order_header_rec
2883 ,x_order_line_tbl => lx_line_tbl
2884 ,x_return_status => x_return_status
2885 ,x_msg_count => x_msg_count
2886 ,x_msg_data => x_msg_data
2887 );
2888
2889 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) then
2890 raise FND_API.G_EXC_ERROR;
2891 END IF;
2892
2893 IF (IBE_UTIL.G_DEBUGON = l_true) then
2894 Ibe_Util.DEBUG('ship_to_org_id returned: ' || lx_order_header_rec.ship_to_org_id);
2895 Ibe_Util.DEBUG('bill_to_org_id returned: ' || lx_order_header_rec.invoice_to_org_id);
2896 end if;
2897
2898 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)
2899 THEN
2900 FND_Message.Set_Name('IBE', 'IBE_ERR_OT_SHIPTO_BILLTO_MISS');
2901 FND_Msg_Pub.Add;
2902 RAISE FND_API.G_EXC_ERROR;
2903 END IF;
2904
2905 l_order_header_rec := lx_order_header_rec;
2906 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2907 IBE_Util.Debug('Save :order_hdr_rec.ship_to_org_id: ' || l_order_header_rec.SHIP_TO_ORG_ID);
2908 IBE_Util.Debug('Save :order_hdr_rec.Invoice_to_org_id: ' || l_order_header_rec.INVOICE_TO_ORG_ID);
2909 IBE_Util.Debug('Save :order_hdr_rec.header_id: ' || l_order_header_rec.HEADER_ID);
2910
2911 END IF;
2912 FOR i in 1..lx_line_tbl.COUNT
2913 LOOP
2914 l_order_line_tbl(i) := lx_line_tbl(i);
2915 END LOOP;
2916
2917
2918 END IF; -- if operation = create
2919
2920
2921 -- This flow is for the B2C user,
2922 -- whenever he updates the Return, 'invoicetoorgid' at header level
2923 -- will be populated into lines, because when creating the Returns
2924 -- address would have been populated from referenced order.
2925 -- So that has to be changed to get it from Order.
2926
2927
2928 open cr_persontype(p_party_id);
2929 fetch cr_persontype into l_user_type;
2930 close cr_persontype;
2931
2932
2933 IF ((l_user_type = 'PERSON') AND
2934 (l_order_header_rec.operation = OE_Globals.G_OPR_UPDATE) AND
2935 (p_submit_control_rec.cancel_flag <> 'Y'))
2936 THEN
2937 IF (IBE_UTIL.G_DEBUGON = l_true) then
2938 Ibe_Util.DEBUG('calling SetLine ids API');
2939 END IF;
2940 SetLineShipInvoiceIds(
2941 p_order_header_rec => l_order_header_rec
2942 ,p_order_line_tbl => l_order_line_tbl
2943 ,x_order_line_tbl => lx_line_tbl);
2944
2945 for j in 1..lx_line_tbl.count
2946 loop
2947 l_order_line_tbl(j) := lx_line_tbl(j);
2948 IF (IBE_UTIL.G_DEBUGON = l_true) then
2949 Ibe_Util.DEBUG('Line Level Values after setting: '||l_order_line_tbl(j).line_id||' : '||
2950 l_order_line_tbl(j).ship_to_org_id||' : '||l_order_line_tbl(j).invoice_to_org_id);
2951 END IF;
2952 end loop;
2953
2954 END IF; -- if operation = update
2955
2956 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2957 FOR i in 1..l_order_line_tbl.COUNT
2958 LOOP
2959 IBE_Util.Debug('Save:line_tbl.operation: ' || l_order_line_tbl(i).OPERATION);
2960 IBE_Util.Debug('Save:line_tbl.line_id: ' || l_order_line_tbl(i).LINE_ID);
2961 IBE_Util.Debug('Save:line_tbl.line_type_id: ' || l_order_line_tbl(i).LINE_TYPE_ID);
2962 IBE_Util.Debug('Save:line_tbl.qty: ' || l_order_line_tbl(i).ORDERED_QUANTITY);
2963 IBE_Util.Debug('Save:line_tbl.inv to org id: ' || l_order_line_tbl(i).INVOICE_TO_ORG_ID);
2964 IBE_Util.Debug('Save:line_tbl.ship to org id: ' || l_order_line_tbl(i).SHIP_TO_ORG_ID);
2965 IBE_Util.Debug('Save:line_tbl.return_context: ' || l_order_line_tbl(i).RETURN_CONTEXT);
2966 IBE_Util.Debug('Save:line_tbl.line categ: ' || l_order_line_tbl(i).LINE_CATEGORY_CODE);
2967 IBE_Util.Debug('Save:line_tbl.ret atr1: ' || l_order_line_tbl(i).return_attribute1);
2968 IBE_Util.Debug('Save:line_tbl.ret atr2: ' || l_order_line_tbl(i).return_attribute2);
2969 IBE_Util.Debug('Save:line_tbl.rcode: ' || l_order_line_tbl(i).return_reason_code);
2970 END LOOP;
2971 END IF;
2972
2973
2974 /************ Hard Delete logic for Pending Return ****/
2975
2976 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2977 IBE_Util.Debug('ibe_order_save_pvt:cancel_flag ' ||p_submit_control_rec.cancel_flag);
2978 END IF;
2979
2980 if (p_submit_control_rec.cancel_flag = 'Y') then
2981 CancelOrder(l_order_header_rec,lx_order_header_rec);
2982 l_order_header_rec := lx_order_header_rec;
2983 IF (IBE_UTIL.G_DEBUGON = l_true) then
2984 ibe_util.debug('after cancelorder header_id:' ||l_order_header_rec.header_id);
2985 ibe_util.debug('after cancelorder operation:' ||l_order_header_rec.operation);
2986 end if;
2987
2988 IF(l_order_header_rec.operation = OE_Globals.G_OPR_DELETE) THEN
2989 l_cancel_flow := FND_API.G_TRUE;
2990 END IF;
2991
2992 end if;
2993
2994 /************ Model Items Logic ******************************/
2995
2996 -- Call SaveMDLRelatedOperations() if not submit or cancel
2997 -- This to propagate parent level changes to all children if a
2998 -- MDL item is updated/ removed.
2999 -- This is used currently by the Return-Flow-Orders alone.
3000
3001 IF (l_order_header_rec.operation = OE_GLOBALS.G_OPR_UPDATE AND
3002 (p_submit_control_rec.submit_flag is null
3003 OR p_submit_control_rec.submit_flag = FND_API.G_MISS_CHAR))
3004 THEN
3005 IF l_order_header_rec.order_category_code = 'RETURN' then
3006 SaveMDLRelatedOperations(p_context_type => 'SAVE',
3007 p_order_line_tbl => l_order_line_tbl,
3008 p_order_header_id => l_order_header_rec.header_id,
3009 p_save_type => p_save_type,
3010 x_order_line_tbl => lx_line_tbl
3011 );
3012
3013 for i in 1..lx_line_tbl.count
3014 loop
3015 l_order_line_tbl(i) := lx_line_tbl(i);
3016 end loop;
3017
3018 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3019 IBE_Util.Debug('LineTbl cnt returned from SAveMDL API:'||l_order_line_tbl.count);
3020 END IF;
3021 END IF; -- order_category_code = 'RETURN' check
3022 END IF; -- main if for savemdlrel... call
3023
3024
3025 /***************** Delete The Order When Last Item is Deleted bug#3272947 **************/
3026
3027 IF (l_order_header_rec.order_category_code = 'RETURN'
3028 AND p_save_type = SAVE_REMOVEITEMS) THEN
3029
3030 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3031 IBE_Util.debug('Inside Last Item Removal-Hard Delete If Loop');
3032 end if;
3033
3034 for lineIdx in 1..l_order_line_tbl.count
3035 loop
3036 if(l_order_line_tbl(lineIdx).operation=OE_GLOBALS.G_OPR_DELETE) THEN
3037 p_dl_line_ids := p_dl_line_ids || ','||l_order_line_tbl(lineIdx).LINE_ID;
3038 end if;
3039 end loop;
3040
3041 IBE_LEAD_IMPORT_PVT.parseInput (p_dl_line_ids, 'CHAR', l_parseKey, l_parseNum, l_dl_line_id_qry);
3042
3043 l_dl_tmp_qry := 'SELECT count(*) from oe_order_lines_all '||
3044 'WHERE header_id= :dl_header_id '||
3045 'AND line_id NOT IN('||l_dl_line_id_qry||')';
3046
3047 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3048 IBE_Util.debug('qry for finding if last item is removed: '|| l_dl_tmp_qry);
3049 end if;
3050
3051 open l_dl_tmp for l_dl_tmp_qry using p_order_header_rec.header_id,l_parseKey;
3052 fetch l_dl_tmp into No_Of_ExistingLines;
3053 close l_dl_tmp;
3054
3055 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3056 IBE_Util.debug('No. Of lines Existing in the ReturnOrder: '|| l_dl_tmp_qry);
3057 end if;
3058
3059 IF (No_Of_ExistingLines = 0) THEN -- No More Lines in The Return Order so can be hard deleted.
3060 CancelOrder(l_order_header_rec,lx_order_header_rec);
3061 l_order_header_rec := lx_order_header_rec;
3062 IF (IBE_UTIL.G_DEBUGON = l_true) then
3063 ibe_util.debug('after Remove-Cancelorder header_id:' ||l_order_header_rec.header_id);
3064 ibe_util.debug('after Remove-cancelorder operation:' ||l_order_header_rec.operation);
3065 end if;
3066
3067 IF(l_order_header_rec.operation = OE_Globals.G_OPR_DELETE) THEN
3068 l_cancel_flow := FND_API.G_TRUE;
3069 l_order_line_tbl := OE_ORDER_PUB.G_MISS_LINE_TBL;
3070 END IF;
3071 END IF; --No Of Existing Lines
3072
3073 ENd If; --Main If
3074
3075
3076
3077 /****************** ORDER Booking FLOW ******************/
3078
3079 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3080 IBE_Util.Debug('Save:submit-control_rec.submit_flag ' || p_submit_control_rec.submit_flag);
3081 END IF;
3082
3083 IF p_submit_control_rec.submit_flag = 'Y' THEN
3084
3085 -- If it is 'post 11590' OE flow and if it is a Return:
3086 -- New, Submit_Order() API should be called for booking it.
3087 -- SO validate the qty, before calling submit_order().
3088
3089 IF (IBE_UTIL.G_DEBUGON = l_true) then
3090 IBE_UTIL.DEBUG('order_category_code: ' ||l_order_header_rec.order_category_code );
3091 IBE_UTIL.DEBUG('OE-Get_Code_Release_Level ' || OE_CODE_CONTROL.Get_Code_Release_Level);
3092 end if;
3093
3094 -- As per Bug# 3522453, a new profile is introduced to drive pre-booking approval
3095 -- flows. So check for flow_status_code if it is "WORKING", then go with
3096 -- validations flow; else if it is ENTERED, go with Process_Order() API call.
3097 -- So get the flow_status_code of the ReturnOrder to be submitted.
3098
3099
3100 Get_Order_Status(p_header_id => l_order_header_rec.header_id
3101 ,x_order_status => l_flow_status_code
3102 ,x_last_update_date => l_last_update_date);
3103
3104 IF (IBE_UTIL.G_DEBUGON = l_true) then
3105 IBE_UTIL.DEBUG('SAVE - Pending Return flow_status_code: ' ||l_flow_status_code);
3106 end if;
3107
3108 IF (l_order_header_rec.order_category_code = 'RETURN' AND
3109 l_flow_status_code = 'WORKING') THEN
3110
3111 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3112 IBE_Util.Debug('Before Complete Validate API');
3113 END IF;
3114
3115 Complete_RetOrder_Validate(
3116 P_order_header_id => l_order_header_rec.header_id
3117 ,p_user_type => l_user_type
3118 ,X_failed_line_ids => X_failed_line_ids
3119 ,x_return_status => x_return_status
3120 ,x_msg_count => x_msg_count
3121 ,x_msg_data => x_msg_data
3122 );
3123
3124 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3125 IBE_Util.Debug('After Complete Validate API: '|| x_return_status);
3126 END IF;
3127
3128 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3129 raise FND_API.G_EXC_ERROR;
3130 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3131 raise FND_API.G_EXC_UNEXPECTED_ERROR;
3132 END IF;
3133
3134 -- For post 11590 flow, submit_order() flow would be called directly for booking.
3135 -- But for a b2c user, the header level values(invoice_to_org_id) would be
3136 -- populated for the return lines during submit flow.
3137 -- So Process_Order() should be called before submit_order().
3138 -- For B2B flow, submit_order() could be called directly.
3139 -- Checking whether it is a B2C flow.
3140
3141 IF ((l_user_type = 'PERSON') AND
3142 (l_order_header_rec.operation = OE_Globals.G_OPR_UPDATE) AND
3143 (p_submit_control_rec.cancel_flag <> 'Y')) THEN
3144
3145 l_ProcessOrder_Flow := FND_API.G_TRUE;
3146 ELSE
3147 l_ProcessOrder_Flow := FND_API.G_FALSE;
3148 End If; --user_type ='Person'
3149
3150 Else
3151 l_request_tbl(1).entity_code := OE_GLOBALS.G_ENTITY_HEADER;
3152 l_request_tbl(1).request_type := OE_GLOBALS.G_BOOK_ORDER;
3153 End If;
3154
3155 END IF; -- booking submitflag='Y'
3156
3157 -- bug# 3069333
3158 OE_STANDARD_WF.SAVE_MESSAGES_OFF;
3159
3160 IF (IBE_UTIL.G_DEBUGON = l_true) then
3161 IBE_UTIL.DEBUG('l_ProcessOrder_Flow: ' || l_ProcessOrder_Flow );
3162 end if;
3163
3164 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3165 IBE_Util.Debug('Save Before Process Order:order_hdr_rec.ship_to_org_id: ' || l_order_header_rec.SHIP_TO_ORG_ID);
3166 IBE_Util.Debug('Save Bef Process Ord:order_hdr_rec.Invoice_to_org_id: ' || l_order_header_rec.INVOICE_TO_ORG_ID);
3167 IBE_Util.Debug('Save Bef Process Ord:order_hdr_rec.header_id: ' || l_order_header_rec.HEADER_ID);
3168 END IF;
3169
3170 IF(FND_API.to_Boolean(l_ProcessOrder_Flow)) THEN
3171
3172 /*************--call process order;--*****************/
3173
3174 IF (IBE_UTIL.G_DEBUGON = l_true) then
3175 IBE_UTIL.DEBUG('Save - Before Calling Process_Order()');
3176 end if;
3177
3178 OE_Order_GRP.Process_Order
3179 ( p_api_version_number => 1.0
3180 , p_init_msg_list => FND_API.G_TRUE
3181 , p_return_values => l_return_values
3182 , p_commit => FND_API.G_FALSE
3183 , x_return_status => x_return_status
3184 , x_msg_count => x_msg_count
3185 , x_msg_data => x_msg_data
3186 --, p_api_service_level => p_api_service_level
3187 , p_header_rec => l_order_header_rec
3188 , p_Header_Adj_tbl => l_header_adj_tbl
3189 , p_Header_price_Att_tbl => l_header_price_att_tbl
3190 , p_Header_Adj_Att_tbl => l_header_adj_att_tbl
3191 , p_Header_Adj_Assoc_tbl => l_header_adj_assoc_tbl
3192 , p_Header_Scredit_tbl => l_header_scredit_tbl
3193 , p_line_tbl => l_order_line_tbl
3194 , p_Line_Adj_tbl => l_line_adj_tbl
3195 , p_Line_price_Att_tbl => l_line_price_att_tbl
3196 , p_Line_Adj_Att_tbl => l_Line_Adj_Att_tbl
3197 , p_Line_Adj_Assoc_tbl => l_line_adj_assoc_tbl
3198 , p_Line_Scredit_tbl => l_line_scredit_tbl
3199 , p_Lot_Serial_tbl => l_lot_serial_tbl
3200 , p_Action_Request_tbl => l_request_tbl
3201 , x_header_rec => lx_order_header_rec
3202 , x_header_val_rec => l_header_val_rec
3203 , x_Header_Adj_tbl => lx_header_adj_tbl
3204 , x_Header_Adj_val_tbl => l_header_adj_val_tbl
3205 , x_Header_price_Att_tbl => lx_header_price_att_tbl
3206 , x_Header_Adj_Att_tbl => lx_header_adj_att_tbl
3207 , x_Header_Adj_Assoc_tbl => lx_header_adj_assoc_tbl
3208 , x_Header_Scredit_tbl => lx_header_scredit_tbl
3209 , x_Header_Scredit_val_tbl => l_header_scredit_val_tbl
3210 , x_line_tbl => lx_line_tbl
3211 , x_line_val_tbl => l_line_val_tbl
3212 , x_Line_Adj_tbl => lx_line_adj_tbl
3213 , x_Line_Adj_val_tbl => l_line_adj_val_tbl
3214 , x_Line_price_Att_tbl => lx_line_price_att_tbl
3215 , x_Line_Adj_Att_tbl => lx_line_adj_att_tbl
3216 , x_Line_Adj_Assoc_tbl => lx_line_adj_assoc_tbl
3217 , x_Line_Scredit_tbl => lx_line_scredit_tbl
3218 , x_Line_Scredit_val_tbl => l_line_scredit_val_tbl
3219 , x_Lot_Serial_tbl => lx_lot_serial_tbl
3220 , x_Lot_Serial_val_tbl => l_lot_serial_val_tbl
3221 , x_action_request_tbl => l_action_request_tbl
3222 );
3223
3224 IF (IBE_UTIL.G_DEBUGON = l_true) then
3225 IBE_UTIL.DEBUG('Save After Calling Process_Order() : ' || x_return_status);
3226 IBE_UTIL.DEBUG('header id from OE Save: ' || lx_order_header_rec.header_id);
3227 end if;
3228
3229 for j in 1 .. x_msg_count
3230 loop
3231 x_msg_data:= OE_MSG_PUB.get(fnd_msg_pub.g_next,FND_API.G_FALSE);
3232 IF (IBE_UTIL.G_DEBUGON = l_true) then
3233 IBE_UTIL.DEBUG('Message from OE Save: ' || x_msg_data );
3234 end if;
3235 end loop;
3236
3237 -- This check is for catching Booking Related Exceptions Only.
3238 -- In post 11590 flow, for B2c user, the process_order() would be called
3239 -- before submit_order(). So in the following if condition, the
3240 -- first 2 condns would be satisfied. But for the third condn.,
3241 -- l_action_request_tbl will be empty as we are not sending l_request_tbl()
3242 -- params above. So its safer to check for Exists and chk the return_status.
3243
3244 IF (x_return_status = FND_API.G_RET_STS_SUCCESS
3245 AND p_submit_control_rec.submit_flag = 'Y'
3246 AND (l_action_request_tbl.EXISTS(1) AND
3247 l_action_request_tbl(1).return_status <> FND_API.G_RET_STS_SUCCESS))
3248 THEN
3249 IF (IBE_UTIL.G_DEBUGON = l_true) then
3250 IBE_UTIL.DEBUG('Error in ProcessOrder Booking Flow' );
3251 end if;
3252 retrieve_oe_messages;
3253 x_return_status := FND_API.G_RET_STS_ERROR;
3254 RAISE FND_API.G_EXC_ERROR;
3255 END IF;
3256
3257 -- This check is for catching all generic Exceptions.
3258 IF x_return_status <> FND_API.G_RET_STS_SUCCESS then
3259 IF (IBE_UTIL.G_DEBUGON = l_true) then
3260 IBE_UTIL.DEBUG('Error in Procees Order Flow: '||x_return_status );
3261 end if;
3262 retrieve_oe_messages;
3263 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3264 raise FND_API.G_EXC_ERROR;
3265 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3266 raise FND_API.G_EXC_UNEXPECTED_ERROR;
3267 END IF;
3268 END IF;
3269
3270 x_order_header_id := lx_order_header_rec.header_id;
3271 x_last_update_date := lx_order_header_rec.last_update_date;
3272 x_order_number := lx_order_header_rec.order_number;
3273 x_flow_status_code := lx_order_header_rec.flow_status_code;
3274
3275 END If; --processOrderflow
3276
3277 IF((p_submit_control_rec.submit_flag = 'Y')
3278 AND(l_order_header_rec.order_category_code = 'RETURN' AND
3279 l_flow_status_code = 'WORKING'))THEN
3280
3281 IF (IBE_UTIL.G_DEBUGON = l_true) then
3282 IBE_UTIL.DEBUG('Before Calling OE_RMA_GRP.Submit_Order');
3283 end if;
3284
3285 OE_RMA_GRP.Submit_Order( p_api_version => 1.0
3286 , p_header_id => l_order_header_rec.header_id
3287 , x_return_status => x_return_status
3288 , x_msg_count => x_msg_count
3289 , x_msg_data => x_msg_data);
3290
3291 IF (IBE_UTIL.G_DEBUGON = l_true) then
3292 IBE_UTIL.DEBUG('After Calling Submit_Order - return_stats: '||x_return_status);
3293 IBE_UTIL.DEBUG('x_msg_count: '||x_msg_count);
3294 end if;
3295
3296 --for logging appropriate error message
3297 if(x_msg_count is not null AND x_msg_count > 0) then
3298 for j in 1 .. x_msg_count
3299 loop
3300 x_msg_data:= OE_MSG_PUB.get(fnd_msg_pub.g_next,FND_API.G_FALSE);
3301 IF (IBE_UTIL.G_DEBUGON = l_true) then
3302 IBE_UTIL.DEBUG('Message from OE Save: ' || x_msg_data );
3303 end if;
3304 end loop;
3305 end if;
3306
3307 IF x_return_status <> FND_API.G_RET_STS_SUCCESS then
3308 retrieve_oe_messages;
3309 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3310 raise FND_API.G_EXC_ERROR;
3311 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3312 raise FND_API.G_EXC_UNEXPECTED_ERROR;
3313 END IF;
3314 END IF;
3315 x_order_header_id := l_order_header_rec.header_id;
3316
3317 -- We need to send back order_number and last_update_date back to UI.
3318 -- As OE is not sending these, we are populating.
3319 Declare
3320 cursor c_submit_hdrattr(sh_hdr_id number) is
3321 select order_number,last_update_date,flow_status_code
3322 from oe_order_headers_all where header_id=sh_hdr_id;
3323 Begin
3324 open c_submit_hdrattr(x_order_header_id);
3325 fetch c_submit_hdrattr into x_order_number,x_last_update_date,x_flow_status_code;
3326 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3327 IBE_UTIL.DEBUG('order number after submit: ' || x_order_number );
3328 END IF;
3329 close c_submit_hdrattr;
3330 End;
3331
3332 END If;
3333
3334 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3335 IBE_UTIL.DEBUG('header id returned from OE: ' || x_order_header_id );
3336 IBE_UTIL.DEBUG('x_flow_status_code: ' || x_flow_status_code );
3337 IBE_UTIL.DEBUG('calling ibe_active_quotes_all package handler: ' || l_cancel_flow);
3338 END IF;
3339
3340 IF (FND_API.to_Boolean(l_cancel_flow)
3341 OR ((p_save_type = SAVE_NORMAL AND p_submit_control_rec.submit_flag = 'Y')
3342 -- AND (l_action_request_tbl(1).return_status = FND_API.G_RET_STS_SUCCESS)
3343 ))
3344 THEN
3345 DeactivateOrder(p_party_id,l_order_header_rec.sold_to_org_id,l_order_header_rec.transactional_curr_code);
3346 ELSE
3347 ActivateOrder(x_order_header_id,p_party_id,l_order_header_rec.sold_to_org_id,
3348 l_order_header_rec.transactional_curr_code);
3349 END IF;
3350
3351 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3352 IBE_UTIL.DEBUG('back from ibe_active_quotes_all package handler');
3353 END IF;
3354
3355 -- Calling Notification to the user After successfully booking the order.
3356 -- This would send a notification to the user about the conformation of the Return.
3357
3358 IF (p_save_type = SAVE_NORMAL AND p_submit_control_rec.submit_flag = 'Y') THEN
3359
3360 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3361 IBE_UTIL.debug('IBE_ORDER_SAVE_PVT: Before calling Notification API');
3362 END IF;
3363
3364 IBE_WORKFLOW_PVT.NotifyReturnOrderStatus(
3365 p_api_version => 1,
3366 p_party_id => p_party_id,
3367 p_order_header_id => x_order_header_id,
3368 p_errmsg_count => 0,
3369 p_errmsg_data => NULL,
3370 x_return_status => x_return_status,
3371 x_msg_count => x_msg_count,
3372 x_msg_data => x_msg_data
3373 );
3374 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3375 IBE_UTIL.debug('IBE_ORDER_SAVE_PVT: Notification API is called');
3376 END IF;
3377
3378 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3379 RAISE FND_API.G_EXC_ERROR;
3380 END IF;
3381
3382 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3383 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3384 END IF;
3385
3386 END IF; -- IF (p_save_type....)
3387
3388
3389 --
3390 -- End of API body
3391 --
3392
3393 -- Standard check for p_commit
3394 IF FND_API.to_Boolean( p_commit )
3395 THEN
3396 COMMIT WORK;
3397 END IF;
3398
3399 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3400 IBE_Util.Debug('End OE_ORDER_SAVE_PVT:Save)');
3401 END IF;
3402
3403 -- Standard call to get message count and if count is 1, get message info.
3404
3405 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
3406 p_data => x_msg_data);
3407
3408 EXCEPTION
3409 WHEN FND_API.G_EXC_ERROR THEN
3410 ROLLBACK TO SAVE_ORDER_SAVE_PVT;
3411 x_return_status := FND_API.G_RET_STS_ERROR;
3412 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
3413 p_count => x_msg_count ,
3414 p_data => x_msg_data);
3415 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3416 IBE_Util.Debug('Error IBE_ORDER_SAVE_PVT:Save()'|| sqlerrm);
3417 END IF;
3418
3419 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3420 ROLLBACK TO SAVE_ORDER_SAVE_PVT;
3421 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3422 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
3423 p_count => x_msg_count ,
3424 p_data => x_msg_data);
3425 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3426 IBE_Util.Debug('UNEXPECTEDError IBE_ORDER_SAVE_PVT:Save()' || sqlerrm);
3427 END IF;
3428 WHEN OTHERS THEN
3429 ROLLBACK TO SAVE_ORDER_SAVE_PVT;
3430 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3431
3432 IF FND_Msg_Pub.Check_Msg_Level( FND_Msg_Pub.G_MSG_LVL_UNEXP_ERROR ) THEN
3433 FND_Msg_Pub.Add_Exc_Msg(G_PKG_NAME,
3434 L_API_NAME);
3435 END IF;
3436
3437 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
3438 p_count => x_msg_count ,
3439 p_data => x_msg_data);
3440 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3441 IBE_Util.Debug('Other IBE_ORDER_SAVE_PVT:Save()' || sqlerrm);
3442 END IF;
3443
3444
3445 END Save;
3446
3447 PROCEDURE CheckConstraint(
3448 p_api_version_number IN NUMBER
3449 ,p_init_msg_list IN VARCHAR2
3450 ,p_commit IN VARCHAR2
3451 ,p_order_header_rec IN OE_Order_PUB.Header_Rec_Type
3452 ,p_order_line_tbl IN OE_Order_PUB.Line_Tbl_Type
3453 ,p_submit_control_rec IN IBE_Order_W1_PVT.Control_Rec_Type
3454 ,p_combine_same_lines IN VARCHAR2
3455 ,p_party_id IN NUMBER
3456 ,x_return_status OUT NOCOPY VARCHAR2
3457 ,x_msg_count OUT NOCOPY NUMBER
3458 ,x_msg_data OUT NOCOPY VARCHAR2
3459 ,x_error_lineids OUT NOCOPY JTF_VARCHAR2_TABLE_300
3460 ,x_last_update_date OUT NOCOPY DATE
3461 )
3462 IS
3463
3464 /*********** API Flow ***********/
3465
3466 -- Refer Bug# 2988993 For Details
3467 -- Process_Order() would be called twice
3468 -- First Call, would create a Return Header without lines.
3469 -- Now the second call would use this SAme Header_id created in the first call.
3470 -- Next, set the Control Record and a new Header Record(l_retplcy_orderhdr_rec)
3471 -- with HEaderId created above and opcode 'UPDATE".
3472 -- Call Process_Order() API again, with l_retplcy_orderhdr_rec, ControlRec and
3473 -- Line_Table.
3474 -- This second call would set Api_Service_Level param as 'Check_Security_Only'.
3475 -- Second call check the Return Policy and send the failing LineIds back.
3476 -- Later when OM release bug# 2988993, then these 2 Porcess_Order() API call
3477 -- could be removed and HeaderRecord, LineTable and ControlRecord could be sent
3478 -- together in a Single Call.
3479
3480
3481 l_order_header_rec OE_Order_PUB.Header_Rec_Type := OE_Order_PUB.G_MISS_HEADER_REC;
3482 l_retplcy_orderhdr_rec OE_Order_PUB.Header_Rec_Type := OE_Order_PUB.G_MISS_HEADER_REC;
3483 l_request_tbl OE_Order_PUB.Request_Tbl_Type := OE_Order_PUB.G_MISS_REQUEST_TBL;
3484 l_order_line_tbl OE_Order_PUB.Line_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_TBL;
3485 l_control_rec OE_GLOBALS.Control_Rec_Type;
3486 l_header_val_rec OE_Order_PUB.Header_Val_Rec_Type;
3487 l_header_scredit_val_tbl OE_Order_PUB.Header_Scredit_Val_Tbl_Type;
3488 l_header_adj_val_tbl OE_Order_PUB.Header_Adj_Val_Tbl_Type;
3489 l_line_val_tbl OE_Order_PUB.Line_Val_Tbl_Type;
3490 l_line_adj_val_tbl OE_Order_PUB.Line_Adj_Val_Tbl_Type;
3491 l_line_scredit_val_tbl OE_Order_PUB.Line_Scredit_Val_Tbl_Type;
3492 l_lot_serial_val_tbl OE_Order_PUB.Lot_Serial_Val_Tbl_Type;
3493 l_Action_Request_tbl OE_Order_PUB.request_tbl_type :=OE_Order_PUB.g_miss_request_tbl;
3494
3495 lx_Header_Adj_tbl OE_Order_PUB.Header_Adj_Tbl_Type;
3496 lx_Header_price_Att_tbl OE_Order_PUB.Header_Price_Att_Tbl_Type ;
3497 lx_Header_Adj_Att_tbl OE_Order_PUB.Header_Adj_Att_Tbl_Type ;
3498 lx_Header_Adj_Assoc_tbl OE_Order_PUB.Header_Adj_Assoc_Tbl_Type ;
3499 lx_Header_Scredit_tbl OE_Order_PUB.Header_Scredit_Tbl_Type;
3500 lx_Line_Adj_tbl OE_Order_PUB.Line_Adj_Tbl_Type;
3501 lx_Line_price_Att_tbl OE_Order_PUB.Line_Price_Att_Tbl_Type ;
3502 lx_Line_Adj_Att_tbl OE_Order_PUB.Line_Adj_Att_Tbl_Type ;
3503 lx_Line_Adj_Assoc_tbl OE_Order_PUB.Line_Adj_Assoc_Tbl_Type ;
3504 lx_Line_Scredit_tbl OE_Order_PUB.Line_Scredit_Tbl_Type;
3505 lx_Lot_Serial_tbl OE_Order_PUB.Lot_Serial_Tbl_Type;
3506 lx_order_header_rec OE_Order_PUB.Header_Rec_Type;
3507 lx_line_tbl OE_Order_PUB.Line_Tbl_Type;
3508
3509 l_tmp_line_id varchar2(100) := '';
3510 l_line_id number;
3511 l_line_return_status varchar2(10);
3512 l_line_return_attr2 number;
3513 l_return_values varchar2(4000);
3514 l_api_name VARCHAR2(100) := 'Check Constraints';
3515 l_api_version NUMBER := 1.0;
3516
3517 cursor c_get_lineInfo(l_lineId Number) is
3518 select * from oe_order_lines_all where line_id = l_lineId;
3519
3520 l_line_rec OE_Order_PUB.Line_Rec_Type;
3521 l_line_type_id NUMBER;
3522 l_siteuse_id NUMBER;
3523
3524 TYPE Fail_TmpLineRec IS TABLE OF VARCHAR2(240)
3525 INDEX BY BINARY_INTEGER;
3526
3527 Fail_TmpLineRec_Tbl Fail_TmpLineRec;
3528
3529 l_count NUMBER := 0;
3530 l_salesrep_id VARCHAR2(360);
3531 l_order_type_id NUMBER;
3532 l_commit VARCHAR2(10):=FND_API.G_FALSE;
3533
3534 l_salesrep_number VARCHAR2(360); --MOAC Changes by ASO::Obsoletion of ASO_DEFAULT_PERSON_ID
3535 l_user_orgid NUMBER;
3536
3537
3538 BEGIN
3539
3540 x_error_lineids := JTF_VARCHAR2_TABLE_300();
3541
3542 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3543 IBE_Util.Debug('Begin IBE_ORDER_SAVE_PVT:CheckConstraints()');
3544 END IF;
3545
3546 -- Standard Start of API savepoint
3547 SAVEPOINT ORDER_CHKCONSTRAINT;
3548
3549 -- Standard call to check for call compatibility.
3550 IF NOT FND_API.Compatible_API_Call (l_api_version,
3551 P_Api_Version_Number,
3552 l_api_name,
3553 G_PKG_NAME )
3554 THEN
3555 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3556 END IF;
3557
3558 -- Initialize message list if p_init_msg_list is set to TRUE.
3559 IF FND_API.To_Boolean( p_init_msg_list ) THEN
3560 FND_Msg_Pub.initialize;
3561 END IF;
3562
3563 -- Initialize API return status to success
3564 x_return_status := FND_API.G_RET_STS_SUCCESS;
3565
3566
3567 -- Start OF API body --
3568 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3569 IBE_Util.Debug('In chkconstraint()');
3570 END IF;
3571
3572 -- SET HEADER RECORD
3573 l_order_header_rec := p_order_header_rec;
3574 l_order_header_rec.operation := OE_GLOBALS.G_OPR_CREATE;
3575 -- l_order_header_rec.flow_status_code := 'ENTERED';
3576 --l_order_header_rec.ORDER_CATEGORY_CODE := 'RETURN';
3577 l_order_type_id := FND_PROFILE.VALUE('IBE_RETURN_TRANSACTION_TYPE');
3578
3579 IF (l_order_type_id is null) THEN
3580 FND_Message.Set_Name('IBE', 'IBE_ERR_OT_ORDER_TYPE_ID_MISS');
3581 FND_Msg_Pub.Add;
3582 RAISE FND_API.G_EXC_ERROR;
3583 ELSE
3584 l_order_header_rec.order_type_id := l_order_type_id;
3585 END IF;
3586
3587 -- Get the User's Session ORG_ID
3588 l_user_orgid := mo_global.GET_CURRENT_ORG_ID();
3589 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3590 Ibe_util.Debug('Current Org id : ' ||l_user_orgid);
3591 END IF;
3592
3593 -- Get the Sales Rep Number from the ASO Utility package
3594 l_salesrep_number := ASO_UTILITY_PVT.GET_OU_ATTRIBUTE_VALUE(aso_utility_pvt.GET_DEFAULT_SALESREP,l_user_orgid);
3595 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3596 Ibe_util.Debug('Sales Rep Number for Current Org : ' ||l_salesrep_number);
3597 END IF;
3598
3599 -- Bug 5359687, Proper error message when default salesrep is not set
3600 IF (l_salesrep_number is null) THEN
3601 FND_Message.Set_Name('IBE', 'IBE_ERR_OT_SALESREPID_MISS');
3602 FND_Msg_Pub.Add;
3603 RAISE FND_API.G_EXC_ERROR;
3604 ELSE
3605 -- Get the sales rep id from the sales rep number using the JTF table
3606 select SALESREP_ID into l_salesrep_id from JTF_RS_SALESREPS where SALESREP_NUMBER = l_salesrep_number and ORG_ID = l_user_orgid;
3607 END IF;
3608
3609 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3610 Ibe_util.Debug('Sales Rep Id : ' ||l_salesrep_id);
3611 END IF;
3612
3613 --l_salesrep_id := FND_PROFILE.VALUE('ASO_DEFAULT_PERSON_ID');
3614
3615 IF (l_salesrep_id is null) THEN
3616 FND_Message.Set_Name('IBE', 'IBE_ERR_OT_SALESREPID_MISS');
3617 FND_Msg_Pub.Add;
3618 RAISE FND_API.G_EXC_ERROR;
3619 ELSE
3620 l_order_header_rec.salesrep_id := l_salesrep_id;
3621 END IF;
3622
3623 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3624 IBE_Util.Debug('chkconstraint-l_order_header_rec.header_id:' || l_order_header_rec.header_id);
3625 IBE_Util.Debug('chkconstraint-l_order_header_rec.sold_to_org_id:' || l_order_header_rec.sold_to_org_id);
3626 IBE_Util.Debug('chkconstraint-l_order_header_rec.operation:' || l_order_header_rec.operation);
3627 IBE_Util.Debug('chkconstraint-l_order_header_rec.order_type_id:' || l_order_header_rec.order_type_id);
3628 END IF;
3629
3630
3631 -- SET LINE RECORD
3632 FOR i in 1..p_order_line_tbl.COUNT
3633 LOOP
3634 l_order_line_tbl(i) := p_order_line_tbl(i);
3635
3636 DefaultLineTypes(l_order_header_rec.order_type_id,l_line_type_id);
3637 l_order_line_tbl(i).line_type_id := l_line_type_id;
3638
3639 if(l_order_header_rec.order_category_code = 'RETURN') then
3640 l_order_line_tbl(i).line_category_code := 'RETURN';
3641 l_order_line_tbl(i).return_context := 'ORDER';
3642 end if;
3643
3644 l_order_line_tbl(i).operation := OE_GLOBALS.G_OPR_CREATE;
3645
3646
3647 -- ShipTo / InvoiceTo OrgIds.
3648 -- line level will always be defaulted from original order lines.
3649
3650 for l_line_rec in c_get_lineInfo(l_order_line_tbl(i).return_attribute2)
3651 loop
3652 l_order_line_tbl(i).ship_to_org_id := l_line_rec.SHIP_TO_ORG_ID;
3653 l_order_line_tbl(i).invoice_to_org_id := l_line_rec.INVOICE_TO_ORG_ID;
3654 end loop;
3655
3656 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3657 IBE_Util.Debug('chkconstraint-l_order_line_tbl.lineId: ' || l_order_line_tbl(i).line_id);
3658 IBE_Util.Debug('chkconstraint-l_order_line_tbl.linetypeid: ' || l_order_line_tbl(i).line_type_id);
3659 IBE_Util.Debug('chkconstraint-l_order_line_tbl.shiptoOrgid: ' || l_order_line_tbl(i).ship_to_org_id);
3660 IBE_Util.Debug('chkconstraint-l_order_line_tbl.invoicetoOrgid: ' || l_order_line_tbl(i).invoice_to_org_id);
3661 END IF;
3662
3663 END LOOP;
3664
3665 -- Header Level shipTo /InvoiceTo org ids
3666
3667 DefaultFromLineSiteId(l_order_line_tbl,p_party_id,'SHIP_TO',l_siteuse_id);
3668 l_order_header_rec.ship_to_org_id := l_siteuse_id;
3669 DefaultFromLineSiteId(l_order_line_tbl,p_party_id,'INVOICE_TO',l_siteuse_id);
3670 l_order_header_rec.invoice_to_org_id := l_siteuse_id;
3671
3672 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3673 IBE_Util.Debug('Chkconsrnt-l_order_header_rec.shiporgid: ' || l_order_header_rec.ship_to_org_id);
3674 IBE_Util.Debug('Chkconsrnt-l_order_header_rec.invoiceorgid: ' || l_order_header_rec.invoice_to_org_id);
3675 END IF;
3676
3677 -- FIRST CALL To Process_Order to create only the Header Record. Bug#2988993
3678 -- This would avoid the NoDataFound thrown from OM as reported in the bug.
3679
3680 OE_Order_GRP.Process_Order
3681 ( p_api_version_number => 1.0
3682 , p_init_msg_list => FND_API.G_TRUE
3683 , p_return_values => l_return_values
3684 , p_commit => FND_API.G_FALSE
3685 , x_return_status => x_return_status
3686 , x_msg_count => x_msg_count
3687 , x_msg_data => x_msg_data
3688 , p_control_rec => l_control_rec
3689 , p_header_rec => l_order_header_rec
3690 , p_Action_Request_tbl => l_request_tbl
3691 , x_header_rec => lx_order_header_rec
3692 , x_header_val_rec => l_header_val_rec
3693 , x_Header_Adj_tbl => lx_header_adj_tbl
3694 , x_Header_Adj_val_tbl => l_header_adj_val_tbl
3695 , x_Header_price_Att_tbl => lx_header_price_att_tbl
3696 , x_Header_Adj_Att_tbl => lx_header_adj_att_tbl
3697 , x_Header_Adj_Assoc_tbl => lx_header_adj_assoc_tbl
3698 , x_Header_Scredit_tbl => lx_header_scredit_tbl
3699 , x_Header_Scredit_val_tbl => l_header_scredit_val_tbl
3700 , x_line_tbl => lx_line_tbl
3701 , x_line_val_tbl => l_line_val_tbl
3702 , x_Line_Adj_tbl => lx_line_adj_tbl
3703 , x_Line_Adj_val_tbl => l_line_adj_val_tbl
3704 , x_Line_price_Att_tbl => lx_line_price_att_tbl
3705 , x_Line_Adj_Att_tbl => lx_line_adj_att_tbl
3706 , x_Line_Adj_Assoc_tbl => lx_line_adj_assoc_tbl
3707 , x_Line_Scredit_tbl => lx_line_scredit_tbl
3708 , x_Line_Scredit_val_tbl => l_line_scredit_val_tbl
3709 , x_Lot_Serial_tbl => lx_lot_serial_tbl
3710 , x_Lot_Serial_val_tbl => l_lot_serial_val_tbl
3711 , x_action_request_tbl => l_action_request_tbl
3712 );
3713
3714 IF (IBE_UTIL.G_DEBUGON = l_true) then
3715 IBE_UTIL.DEBUG('Return Status of First-Call to Process_Order() : ' || x_return_status);
3716 IBE_UTIL.DEBUG('header id from OE: ' || lx_order_header_rec.header_id);
3717 end if;
3718
3719 IF x_return_status <> FND_API.G_RET_STS_SUCCESS then
3720 retrieve_oe_messages;
3721 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3722 raise FND_API.G_EXC_ERROR;
3723 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3724 raise FND_API.G_EXC_UNEXPECTED_ERROR;
3725 END IF;
3726 END IF;
3727
3728 --SET HEADER RECORD
3729 l_retplcy_orderhdr_rec.header_id := lx_order_header_rec.header_id;
3730 l_retplcy_orderhdr_rec.operation := OE_Globals.G_OPR_UPDATE;
3731
3732 -- SET CONTROL RECORD
3733 l_control_rec.controlled_operation := TRUE;
3734 l_control_rec.process_partial := TRUE;
3735
3736 -- PLEASE NOTE:
3737 -- process_partial should be TRUE.
3738 -- If FALSE, when in case if first line fails for Return Policy then all the
3739 -- following lines are also sent back as failing Lines from OM
3740
3741 FOR i in 1..l_order_line_tbl.COUNT
3742 LOOP
3743 l_order_line_tbl(i).header_id := lx_order_header_rec.header_id;
3744 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3745 IBE_Util.Debug('l_order_line_tbl.lineId: ' || l_order_line_tbl(i).line_id);
3746 IBE_Util.Debug('l_order_line_tbl.HEaderId: ' || l_order_line_tbl(i).header_id);
3747 IBE_Util.Debug('l_order_line_tbl.retattr1: ' || l_order_line_tbl(i).return_attribute1);
3748 IBE_Util.Debug('l_order_line_tbl.retattr2: ' || l_order_line_tbl(i).return_attribute2);
3749 IBE_Util.Debug('l_order_line_tbl.retcontxt: ' || l_order_line_tbl(i).return_context);
3750 IBE_Util.Debug('l_order_line_tbl.linetypeid: ' || l_order_line_tbl(i).line_type_id);
3751 IBE_Util.Debug('l_order_line_tbl.operation: ' || l_order_line_tbl(i).operation);
3752 IBE_Util.Debug('l_order_line_tbl.shiptoOrgid: ' || l_order_line_tbl(i).ship_to_org_id);
3753 IBE_Util.Debug('l_order_line_tbl.invoicetoOrgid: ' || l_order_line_tbl(i).invoice_to_org_id);
3754 END IF;
3755 END LOOP;
3756
3757
3758 -- Now call Process_Order API for Policy check.
3759
3760 OE_Order_GRP.Process_Order
3761 ( p_api_version_number => 1.0
3762 , p_init_msg_list => FND_API.G_TRUE
3763 , p_return_values => l_return_values
3764 , p_commit => FND_API.G_FALSE
3765 , x_return_status => x_return_status
3766 , x_msg_count => x_msg_count
3767 , x_msg_data => x_msg_data
3768 , p_api_service_level => OE_GLOBALS.G_CHECK_SECURITY_ONLY
3769 , p_control_rec => l_control_rec
3770 , p_header_rec => l_retplcy_orderhdr_rec
3771 , p_line_tbl => l_order_line_tbl
3772 , p_Action_Request_tbl => l_request_tbl
3773 , x_header_rec => lx_order_header_rec
3774 , x_header_val_rec => l_header_val_rec
3775 , x_Header_Adj_tbl => lx_header_adj_tbl
3776 , x_Header_Adj_val_tbl => l_header_adj_val_tbl
3777 , x_Header_price_Att_tbl => lx_header_price_att_tbl
3778 , x_Header_Adj_Att_tbl => lx_header_adj_att_tbl
3779 , x_Header_Adj_Assoc_tbl => lx_header_adj_assoc_tbl
3780 , x_Header_Scredit_tbl => lx_header_scredit_tbl
3781 , x_Header_Scredit_val_tbl => l_header_scredit_val_tbl
3782 , x_line_tbl => lx_line_tbl
3783 , x_line_val_tbl => l_line_val_tbl
3784 , x_Line_Adj_tbl => lx_line_adj_tbl
3785 , x_Line_Adj_val_tbl => l_line_adj_val_tbl
3786 , x_Line_price_Att_tbl => lx_line_price_att_tbl
3787 , x_Line_Adj_Att_tbl => lx_line_adj_att_tbl
3788 , x_Line_Adj_Assoc_tbl => lx_line_adj_assoc_tbl
3789 , x_Line_Scredit_tbl => lx_line_scredit_tbl
3790 , x_Line_Scredit_val_tbl => l_line_scredit_val_tbl
3791 , x_Lot_Serial_tbl => lx_lot_serial_tbl
3792 , x_Lot_Serial_val_tbl => l_lot_serial_val_tbl
3793 , x_action_request_tbl => l_action_request_tbl
3794 );
3795
3796 x_error_lineids := JTF_VARCHAR2_TABLE_300();
3797
3798 for j in 1..lx_line_tbl.count
3799 loop
3800 l_line_id := lx_line_tbl(j).line_id;
3801 l_line_return_status := lx_line_tbl(j).return_status;
3802 l_line_return_attr2 := lx_line_tbl(j).return_attribute2;
3803
3804 If(l_line_return_status <> 'S')then
3805 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3806 IBE_UTIL.DEBUG('line id returned from OE' || l_line_id );
3807 IBE_UTIL.DEBUG('return status returned from OE' || l_line_return_status);
3808 IBE_UTIL.DEBUG('return attribute2 returned from OE' || l_line_return_attr2);
3809 END IF;
3810
3811 l_count := l_count + 1;
3812 Fail_TmpLineRec_Tbl(l_count) := lx_line_tbl(j).return_attribute2;
3813 end if;
3814 end loop;
3815
3816 l_count := Fail_TmpLineRec_Tbl.count;
3817 if (l_count > 0) then
3818 x_error_lineids.extend(l_count);
3819 for k in 1..l_count
3820 loop
3821 null;
3822 x_error_lineids(k) := Fail_TmpLineRec_Tbl(k);
3823 end loop;
3824 end if;
3825
3826 IF (IBE_UTIL.G_DEBUGON = l_true) then
3827 IBE_Util.debug('ChkConstraint Error Messages count: ' || x_msg_count ||' : '||x_return_status);
3828 end if;
3829
3830 for j in 1 .. x_msg_count
3831 loop
3832 x_msg_data:= OE_MSG_PUB.get(fnd_msg_pub.g_next,FND_API.G_FALSE);
3833 IF (IBE_UTIL.G_DEBUGON = l_true) then
3834 IBE_Util.debug('Chk Constraint Exception Message' || x_msg_data);
3835 end if;
3836 end loop;
3837
3838 IF x_return_status <> FND_API.G_RET_STS_SUCCESS then
3839 retrieve_oe_messages;
3840 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3841 raise FND_API.G_EXC_ERROR;
3842 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3843 raise FND_API.G_EXC_UNEXPECTED_ERROR;
3844 END IF;
3845 END IF;
3846
3847 --
3848 -- End of API body
3849 --
3850 -- Bug#2988993
3851 -- As the First Process_Order() API call would create an Order Header Record,
3852 -- This Rollback is a must.
3853
3854 ROLLBACK TO ORDER_CHKCONSTRAINT;
3855 l_commit := p_commit;
3856
3857 -- Standard check for p_commit
3858 IF FND_API.to_Boolean(l_commit)
3859 THEN
3860 COMMIT WORK;
3861 END IF;
3862
3863 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3864 IBE_Util.Debug('End OE_ORDER_SAVE_PVT:CHECKCONSTRAINT()');
3865 END IF;
3866
3867 -- Standard call to get message count and if count is 1, get message info.
3868 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
3869 p_data => x_msg_data);
3870
3871
3872 EXCEPTION
3873 WHEN FND_API.G_EXC_ERROR THEN
3874 ROLLBACK TO ORDER_CHKCONSTRAINT;
3875 x_return_status := FND_API.G_RET_STS_ERROR;
3876 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
3877 p_count => x_msg_count ,
3878 p_data => x_msg_data);
3879 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3880 IBE_Util.Debug('Error IBE_ORDER_SAVE_PVT:CHECKCONSTRAINT()'|| sqlerrm);
3881 END IF;
3882
3883 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3884 ROLLBACK TO ORDER_CHKCONSTRAINT;
3885 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3886 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
3887 p_count => x_msg_count ,
3888 p_data => x_msg_data);
3889 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3890 IBE_Util.Debug('UNEXPECTEDError IBE_ORDER_SAVE_PVT:CHECKCONSTRAINT()' || sqlerrm);
3891 END IF;
3892
3893 WHEN OTHERS THEN
3894 ROLLBACK TO ORDER_CHKCONSTRAINT;
3895 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3896 IF FND_Msg_Pub.Check_Msg_Level( FND_Msg_Pub.G_MSG_LVL_UNEXP_ERROR ) THEN
3897 FND_Msg_Pub.Add_Exc_Msg(G_PKG_NAME,
3898 L_API_NAME);
3899 END IF;
3900
3901 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
3902 p_count => x_msg_count ,
3903 p_data => x_msg_data);
3904 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3905 IBE_Util.Debug('Other IBE_ORDER_SAVE_PVT:CHECKCONSTRAINT()' || sqlerrm);
3906 END IF;
3907
3908 END CheckConstraint;
3909
3910
3911 PROCEDURE UpdateLineShippingBilling(
3912 p_api_version_number IN NUMBER
3913 ,p_init_msg_list IN VARCHAR2
3914 ,p_commit IN VARCHAR2
3915 ,x_return_status OUT NOCOPY VARCHAR2
3916 ,x_msg_count OUT NOCOPY NUMBER
3917 ,x_msg_data OUT NOCOPY VARCHAR2
3918 ,p_order_header_id IN NUMBER
3919 ,p_order_line_id IN NUMBER
3920 ,p_billto_party_id IN NUMBER
3921 ,p_billto_cust_acct_id IN NUMBER
3922 ,p_billto_party_site_id IN NUMBER
3923 ,p_shipto_party_id IN NUMBER
3924 ,p_shipto_cust_acct_id IN NUMBER
3925 ,p_shipto_party_site_id IN NUMBER
3926 ,p_last_update_date IN DATE
3927 )
3928 IS
3929
3930 --l_acct_siteuse_id NUMBER := null;
3931 l_billto_acct_siteuse_id NUMBER := null;
3932 l_shipto_acct_siteuse_id NUMBER := null;
3933 l_return_values varchar2(2000);
3934 l_api_version NUMBER := 1.0;
3935 l_api_name VARCHAR2(30) := 'ORDER_UPDATELINEBILL';
3936
3937 l_order_header_rec OE_Order_PUB.Header_Rec_Type := OE_Order_PUB.G_MISS_HEADER_REC;
3938 l_order_line_tbl OE_Order_PUB.Line_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_TBL;
3939 l_request_tbl OE_Order_PUB.Request_Tbl_Type := OE_Order_PUB.G_MISS_REQUEST_TBL;
3940 l_control_rec OE_GLOBALS.Control_Rec_Type;
3941 l_header_val_rec OE_Order_PUB.Header_Val_Rec_Type;
3942 l_header_scredit_val_tbl OE_Order_PUB.Header_Scredit_Val_Tbl_Type;
3943 l_header_adj_val_tbl OE_Order_PUB.Header_Adj_Val_Tbl_Type;
3944 l_line_val_tbl OE_Order_PUB.Line_Val_Tbl_Type;
3945 l_line_adj_val_tbl OE_Order_PUB.Line_Adj_Val_Tbl_Type;
3946 l_line_scredit_val_tbl OE_Order_PUB.Line_Scredit_Val_Tbl_Type;
3947 l_lot_serial_val_tbl OE_Order_PUB.Lot_Serial_Val_Tbl_Type;
3948 l_Action_Request_tbl OE_Order_PUB.request_tbl_type :=OE_Order_PUB.g_miss_request_tbl;
3949
3950 lx_Header_Adj_tbl OE_Order_PUB.Header_Adj_Tbl_Type;
3951 lx_Header_price_Att_tbl OE_Order_PUB.Header_Price_Att_Tbl_Type ;
3952 lx_Header_Adj_Att_tbl OE_Order_PUB.Header_Adj_Att_Tbl_Type ;
3953 lx_Header_Adj_Assoc_tbl OE_Order_PUB.Header_Adj_Assoc_Tbl_Type ;
3954 lx_Header_Scredit_tbl OE_Order_PUB.Header_Scredit_Tbl_Type;
3955 lx_Line_Adj_tbl OE_Order_PUB.Line_Adj_Tbl_Type;
3956 lx_Line_price_Att_tbl OE_Order_PUB.Line_Price_Att_Tbl_Type ;
3957 lx_Line_Adj_Att_tbl OE_Order_PUB.Line_Adj_Att_Tbl_Type ;
3958 lx_Line_Adj_Assoc_tbl OE_Order_PUB.Line_Adj_Assoc_Tbl_Type ;
3959 lx_Line_Scredit_tbl OE_Order_PUB.Line_Scredit_Tbl_Type;
3960 lx_Lot_Serial_tbl OE_Order_PUB.Lot_Serial_Tbl_Type;
3961 lx_order_header_rec OE_Order_PUB.Header_Rec_Type;
3962 lx_line_tbl OE_Order_PUB.Line_Tbl_Type;
3963 --l_siteuse_type VARCHAR2(20);
3964 l_siteuse_billto VARCHAR2(20);
3965 l_siteuse_shipto VARCHAR2(20);
3966 l_cust_acct_role_id NUMBER;
3967 l_flow_status_code VARCHAR2(30);
3968 l_last_update_date DATE;
3969
3970 BEGIN
3971
3972 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3973 IBE_Util.Debug('Begin IBE_ORDER_SAVE_PVT:UpdateLinebilling()');
3974 END IF;
3975
3976 -- Standard Start of API savepoint
3977 SAVEPOINT ORDER_UPDTLINEBILL;
3978
3979 -- Standard call to check for call compatibility.
3980 IF NOT FND_API.Compatible_API_Call (l_api_version,
3981 P_Api_Version_Number,
3982 l_api_name,
3983 G_PKG_NAME )
3984 THEN
3985 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3986 END IF;
3987
3988 -- Initialize message list if p_init_msg_list is set to TRUE.
3989 IF FND_API.To_Boolean( p_init_msg_list ) THEN
3990 FND_Msg_Pub.initialize;
3991 END IF;
3992
3993 -- Initialize API return status to success
3994 x_return_status := FND_API.G_RET_STS_SUCCESS;
3995
3996 -- Start OF API body --
3997
3998 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3999 IBE_Util.Debug('Calling Validate Order Access');
4000 END IF;
4001
4002 /********** User Authentication *************/
4003
4004 IF (p_order_header_id is not null AND
4005 p_order_header_id <> FND_API.G_MISS_NUM)
4006 THEN
4007 ValidateOrderAccess(p_order_header_id => p_order_header_id
4008 ,x_return_status => x_return_status
4009 );
4010 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) then
4011 raise FND_API.G_EXC_ERROR;
4012 END IF;
4013 END IF;
4014
4015 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4016 Ibe_util.Debug('checking the Return status');
4017 END IF;
4018
4019
4020 Get_Order_Status(p_header_id => p_order_header_id
4021 ,x_order_status => l_flow_status_code
4022 ,x_last_update_date => l_last_update_date);
4023
4024 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4025 Ibe_util.Debug('last_update_date sent from ui: '||p_last_update_date);
4026 Ibe_util.Debug('last_update_date from db: '||l_last_update_date);
4027 Ibe_util.Debug('l_flow_status_code: '||l_flow_status_code);
4028 END IF;
4029
4030 IF ((l_flow_status_code = 'BOOKED' OR l_flow_status_code='CLOSED' OR
4031 l_flow_status_code = 'CANCELLED')
4032 OR l_last_update_date > p_last_update_date)
4033 THEN
4034 FND_Message.Set_Name('IBE', 'IBE_ERR_OT_REFRESH_RETURN');
4035 FND_Msg_Pub.Add;
4036 RAISE FND_API.G_EXC_ERROR;
4037 END IF;
4038
4039
4040 IF (p_billto_cust_acct_id is not null AND p_billto_cust_acct_id <> FND_API.G_MISS_NUM
4041 AND p_billto_party_site_id is not null AND p_billto_party_site_id <> FND_API.G_MISS_NUM)
4042 THEN
4043 --l_siteuse_type := 'BILL_TO';
4044 l_siteuse_billto := 'BILL_TO';
4045 END IF;
4046
4047 IF (p_shipto_cust_acct_id is not null AND p_shipto_cust_acct_id <> FND_API.G_MISS_NUM
4048 AND p_shipto_party_site_id is not null AND p_shipto_party_site_id <> FND_API.G_MISS_NUM)
4049 THEN
4050 --l_siteuse_type := 'SHIP_TO';
4051 l_siteuse_shipto := 'SHIP_TO';
4052 END IF;
4053
4054 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4055 IBE_Util.Debug('UpdateLinebilling-l_siteuse_billto: '||l_siteuse_billto||' : l_siteuse_shipto: '||l_siteuse_shipto);
4056 END IF;
4057
4058 -- Fetching Invoice To Org Id.
4059
4060 if(l_siteuse_billto = 'BILL_TO') then
4061
4062 IBE_CUSTOMER_ACCT_PVT.Get_Cust_Account_Site_Use(
4063 p_cust_acct_id => p_billto_cust_acct_id
4064 ,p_party_id => p_billto_party_id
4065 ,p_siteuse_type => l_siteuse_billto
4066 ,p_partysite_id => p_billto_party_site_id
4067 ,x_siteuse_id => l_billto_acct_siteuse_id
4068 ,x_return_status => x_return_status
4069 ,x_msg_count => x_msg_count
4070 ,x_msg_data => x_msg_data
4071 );
4072
4073 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
4074 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4075 IBE_Util.Debug('UpdateLinebilling- Get_Cust_Account_Site_Use() fails raise exception');
4076 END IF;
4077
4078 FND_Message.Set_Name('IBE', 'IBE_ERR_OT_INVALID_BILLTO_ADDR');
4079 FND_Msg_Pub.Add;
4080 RAISE FND_API.G_EXC_ERROR;
4081 END IF;
4082
4083 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4084 IBE_Util.Debug('UpdateLinebilling-l_acct_siteuse_id: ' || l_billto_acct_siteuse_id);
4085 END IF;
4086 end if;
4087
4088 if(l_siteuse_shipto = 'SHIP_TO') then
4089
4090 IBE_CUSTOMER_ACCT_PVT.Get_Cust_Account_Site_Use(
4091 p_cust_acct_id => p_shipto_cust_acct_id
4092 ,p_party_id => p_shipto_party_id
4093 ,p_siteuse_type => l_siteuse_shipto
4094 ,p_partysite_id => p_shipto_party_site_id
4095 ,x_siteuse_id => l_shipto_acct_siteuse_id
4096 ,x_return_status => x_return_status
4097 ,x_msg_count => x_msg_count
4098 ,x_msg_data => x_msg_data
4099 );
4100
4101 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
4102 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4103 IBE_Util.Debug('UpdateLinebilling- Get_Cust_Account_Site_Use() fails raise exception');
4104 END IF;
4105
4106 FND_Message.Set_Name('IBE', 'IBE_ERR_OT_INVALID_SHIPTO_ADDR');
4107 FND_Msg_Pub.Add;
4108 RAISE FND_API.G_EXC_ERROR;
4109 END IF;
4110 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4111 IBE_Util.Debug('UpdateLinebilling-l_acct_siteuse_id: ' || l_shipto_acct_siteuse_id);
4112 END IF;
4113
4114 end if;
4115
4116 l_order_header_rec.header_id := p_order_header_id;
4117 l_order_header_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
4118 l_order_line_tbl(1) := OE_Order_PUB.G_MISS_LINE_REC;
4119 l_order_line_tbl(1).header_id := p_order_header_id;
4120 l_order_line_tbl(1).operation := OE_GLOBALS.G_OPR_UPDATE;
4121 l_order_line_tbl(1).line_id := p_order_line_id;
4122
4123 if(l_siteuse_billto = 'BILL_TO') then
4124 l_order_line_tbl(1).invoice_to_org_id := l_billto_acct_siteuse_id;
4125 end if;
4126 if(l_siteuse_shipto = 'SHIP_TO') then
4127 l_order_line_tbl(1).ship_to_org_id := l_shipto_acct_siteuse_id;
4128 end if;
4129
4130 -- Fetching InvoiceToContactId
4131 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4132 IBE_Util.Debug('billto_ party_id :: billto_custacct_id: '||p_billto_party_id||' :: '||p_billto_cust_acct_id);
4133 END IF;
4134
4135 If(p_billto_party_id is not null AND
4136 p_billto_party_id <> FND_API.G_MISS_NUM AND
4137 l_siteuse_billto = 'BILL_TO') then
4138
4139 IBE_CUSTOMER_ACCT_PVT.Get_Cust_Acct_Role(
4140 p_party_id => p_billto_party_id
4141 ,p_acctsite_type => 'BILL_TO'
4142 ,p_sold_to_orgid => p_billto_cust_acct_id
4143 ,p_custacct_siteuse_id => l_order_line_tbl(1).invoice_to_org_id
4144 ,x_cust_acct_role_id => l_cust_acct_role_id
4145 ,x_return_status => x_return_status
4146 ,x_msg_count => x_msg_count
4147 ,x_msg_data => x_msg_data
4148 );
4149
4150 l_order_line_tbl(1).invoice_to_contact_id := l_cust_acct_role_id;
4151 end if;
4152
4153 if(p_shipto_party_id is not null AND
4154 p_shipto_party_id <> FND_API.G_MISS_NUM AND
4155 l_siteuse_shipto = 'SHIP_TO') then
4156
4157 IBE_CUSTOMER_ACCT_PVT.Get_Cust_Acct_Role(
4158 p_party_id => p_shipto_party_id
4159 ,p_acctsite_type => 'SHIP_TO'
4160 ,p_sold_to_orgid => p_shipto_cust_acct_id
4161 ,p_custacct_siteuse_id => l_order_line_tbl(1).ship_to_org_id
4162 ,x_cust_acct_role_id => l_cust_acct_role_id
4163 ,x_return_status => x_return_status
4164 ,x_msg_count => x_msg_count
4165 ,x_msg_data => x_msg_data
4166 );
4167 l_order_line_tbl(1).ship_to_contact_id := l_cust_acct_role_id;
4168 end if;
4169
4170
4171 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4172 IBE_Util.Debug('Line Id: '||l_order_line_tbl(1).line_id);
4173 IBE_Util.Debug('Line operation: ' ||l_order_line_tbl(1).operation);
4174 IBE_Util.Debug('Line invoiceToOrgId: ' ||l_order_line_tbl(1).invoice_to_org_id);
4175 IBE_Util.Debug('Line InvoiceToContactId: '||l_order_line_tbl(1).invoice_to_contact_id);
4176 ENd If;
4177
4178 SaveMDLRelatedOperations(p_context_type => 'UPDATELINES',
4179 p_order_line_tbl => l_order_line_tbl,
4180 p_order_header_id => p_order_header_id,
4181 x_order_line_tbl => lx_line_tbl
4182 );
4183
4184 for i in 1..lx_line_tbl.count
4185 loop
4186 l_order_line_tbl(i) := lx_line_tbl(i);
4187 end loop;
4188
4189 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4190 IBE_Util.Debug('UpdateLine Linetbl cnt b4 processOrder '||l_order_line_tbl.count);
4191 IBE_Util.Debug('check this: '||l_order_line_tbl(1).LINE_ID);
4192 IBE_Util.Debug('check this: '||l_order_line_tbl(1).INVOICE_TO_ORG_ID);
4193 IBE_Util.Debug('check this: '||l_order_line_tbl(1).INVOICE_TO_CONTACT_ID);
4194 IBE_Util.Debug('check this: '||l_order_line_tbl(1).SHIP_TO_ORG_ID);
4195 IBE_Util.Debug('check this: '||l_order_line_tbl(1).SHIP_TO_CONTACT_ID);
4196 IBE_Util.Debug('check this: '||l_order_line_tbl(1).OPERATION);
4197 END IF;
4198
4199 -- Calling Process Order
4200 OE_Order_GRP.Process_Order
4201 ( p_api_version_number => 1.0
4202 , p_init_msg_list => FND_API.G_TRUE
4203 , p_return_values => l_return_values
4204 , p_commit => FND_API.G_FALSE
4205 , x_return_status => x_return_status
4206 , x_msg_count => x_msg_count
4207 , x_msg_data => x_msg_data
4208 , p_control_rec => l_control_rec
4209 , p_header_rec => l_order_header_rec
4210 , p_line_tbl => l_order_line_tbl
4211 , p_Action_Request_tbl => l_request_tbl
4212 , x_header_rec => lx_order_header_rec
4213 , x_header_val_rec => l_header_val_rec
4214 , x_Header_Adj_tbl => lx_header_adj_tbl
4215 , x_Header_Adj_val_tbl => l_header_adj_val_tbl
4216 , x_Header_price_Att_tbl => lx_header_price_att_tbl
4217 , x_Header_Adj_Att_tbl => lx_header_adj_att_tbl
4218 , x_Header_Adj_Assoc_tbl => lx_header_adj_assoc_tbl
4219 , x_Header_Scredit_tbl => lx_header_scredit_tbl
4220 , x_Header_Scredit_val_tbl => l_header_scredit_val_tbl
4221 , x_line_tbl => lx_line_tbl
4222 , x_line_val_tbl => l_line_val_tbl
4223 , x_Line_Adj_tbl => lx_line_adj_tbl
4224 , x_Line_Adj_val_tbl => l_line_adj_val_tbl
4225 , x_Line_price_Att_tbl => lx_line_price_att_tbl
4226 , x_Line_Adj_Att_tbl => lx_line_adj_att_tbl
4227 , x_Line_Adj_Assoc_tbl => lx_line_adj_assoc_tbl
4228 , x_Line_Scredit_tbl => lx_line_scredit_tbl
4229 , x_Line_Scredit_val_tbl => l_line_scredit_val_tbl
4230 , x_Lot_Serial_tbl => lx_lot_serial_tbl
4231 , x_Lot_Serial_val_tbl => l_lot_serial_val_tbl
4232 , x_action_request_tbl => l_action_request_tbl
4233 );
4234
4235 IF (IBE_UTIL.G_DEBUGON = l_true) then
4236 IBE_UTIL.DEBUG('Return status from OE updatelinebill: ' || x_return_status);
4237 IBE_UTIL.DEBUG('header id from OE updatelinebill: ' || lx_order_header_rec.header_id);
4238 end if;
4239
4240 for j in 1 .. x_msg_count
4241 loop
4242 x_msg_data:= OE_MSG_PUB.get(fnd_msg_pub.g_next,FND_API.G_FALSE);
4243 IF (IBE_UTIL.G_DEBUGON = l_true) then
4244 IBE_UTIL.DEBUG('Message from OE update line bill: ' || x_msg_data );
4245 end if;
4246 end loop;
4247
4248 IF x_return_status <> FND_API.G_RET_STS_SUCCESS then
4249 retrieve_oe_messages;
4250 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
4251 raise FND_API.G_EXC_ERROR;
4252 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4253 raise FND_API.G_EXC_UNEXPECTED_ERROR;
4254 END IF;
4255 END IF;
4256
4257 --
4258 -- End of API body
4259 --
4260
4261 -- Standard check for p_commit
4262 IF FND_API.to_Boolean( p_commit )
4263 THEN
4264 COMMIT WORK;
4265 END IF;
4266
4267 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4268 IBE_Util.Debug('End IBE_ORDER_SAVE_PVT:UpdateLinebilling()');
4269 END IF;
4270
4271 -- Standard call to get message count and if count is 1, get message info.
4272 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
4273 p_data => x_msg_data);
4274
4275 EXCEPTION
4276 WHEN FND_API.G_EXC_ERROR THEN
4277 ROLLBACK TO ORDER_UPDTLINEBILL;
4278 x_return_status := FND_API.G_RET_STS_ERROR;
4279 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
4280 p_count => x_msg_count ,
4281 p_data => x_msg_data);
4282 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4283 IBE_Util.Debug('Error IBE_ORDER_SAVE_PVT:UpdateLineShippingBilling()'|| sqlerrm);
4284 END IF;
4285
4286 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4287 ROLLBACK TO ORDER_UPDTLINEBILL;
4288 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4289 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
4290 p_count => x_msg_count ,
4291 p_data => x_msg_data);
4292 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4293 IBE_Util.Debug('UNEXPECTEDErr IBE_ORDER_SAVE_PVT:UpdateLineShippingBilling()' || sqlerrm);
4294 END IF;
4295
4296 WHEN OTHERS THEN
4297 ROLLBACK TO ORDER_UPDTLINEBILL;
4298 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4299 IF FND_Msg_Pub.Check_Msg_Level( FND_Msg_Pub.G_MSG_LVL_UNEXP_ERROR ) THEN
4300 FND_Msg_Pub.Add_Exc_Msg(G_PKG_NAME,
4301 L_API_NAME);
4302 END IF;
4303
4304 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
4305 p_count => x_msg_count ,
4306 p_data => x_msg_data);
4307 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
4308 IBE_Util.Debug('OtherExc IBE_ORDER_SAVE_PVT:UpdateLineShippingBilling()' || sqlerrm);
4309 END IF;
4310
4311 END UpdateLineShippingBilling;
4312
4313 END IBE_Order_Save_pvt;
[Home] [Help]