[Home] [Help]
PACKAGE BODY: APPS.ASO_ORDER_INT
Source
1 PACKAGE BODY ASO_order_INT as
2 /* $Header: asoiordb.pls 120.10 2006/01/26 15:43:20 hagrawal ship $ */
3 -- Start of Comments
4 -- Package name : ASO_ORDER_INT
5 -- Purpose :
6 -- History :
7 -- 10/07/2002 hyang - 2611381, performance fix for 1158
8 -- 10/18/2002 hyang - 2633507, performance fix
9 -- 12/04/2002 hyang - 2692785, performance nocopy fix
10 -- 04/07/2003 hyang - 2860045, performance fix.
11 -- NOTE :
12 -- End of Comments
13
14 G_PKG_NAME CONSTANT VARCHAR2(30):= 'ASO_ORDER_INT';
15 G_FILE_NAME CONSTANT VARCHAR2(12) := 'asoiordb.pls';
16
17
18 PROCEDURE Initialize_OM_rec_types
19 (
20 px_header_rec IN OUT NOCOPY OE_Order_PUB.Header_Rec_Type,
21 px_line_tbl IN OUT NOCOPY OE_Order_PUB.Line_Tbl_Type,
22 p_line_tbl_count IN NUMBER
23 )
24 IS
25 BEGIN
26
27 px_header_rec := OE_Order_PUB.G_MISS_HEADER_REC;
28 FOR i in 1..p_line_tbl_count LOOP
29 px_line_tbl(i) := OE_Order_PUB.G_MISS_LINE_REC;
30 END LOOP;
31
32 /* the following record types are exact replicas of OM record types
33 and are initialized to G_MISS values in aso_order_int.
34
35 px_Header_Scredit_tbl := OE_Order_PUB.G_MISS_HEADER_SCREDIT_TBL;
36 px_Line_Scredit_tbl := OE_Order_PUB.G_MISS_LINE_SCREDIT_TBL;
37 px_Lot_Serial_tbl := OE_Order_PUB.G_MISS_LOT_SERIAL_TBL;
38 */
39
40 END Initialize_OM_rec_types;
41
42
43
44
45 FUNCTION Salesrep_Id (
46 employee_person_id NUMBER
47 ) RETURN NUMBER
48 IS
49 l_salesrep_id NUMBER;
50
51 Cursor C_salesrep IS
52 SELECT salesrep_id
53 FROM ra_salesreps
54 WHERE person_id = employee_person_id
55 AND (sysdate BETWEEN NVL(start_date_active, sysdate) AND
56 NVL(end_date_active, sysdate));
57 BEGIN
58 OPEN C_salesrep;
59 FETCH C_salesrep into l_salesrep_id;
60
61 IF (C_salesrep%NOTFOUND) THEN
62 null;
63 END IF;
64 CLOSE C_salesrep;
65
66 RETURN l_salesrep_id;
67 END Salesrep_Id;
68
69
70 FUNCTION Service_Index (
71 quote_line_id NUMBER := FND_API.G_MISS_NUM,
72 quote_line_index NUMBER := FND_API.G_MISS_NUM,
73 P_Line_Rltship_Tbl ASO_QUOTE_PUB.Line_Rltship_Tbl_Type ,
74 p_shipment_tbl ASO_QUOTE_PUB.Shipment_Tbl_Type
75 ) RETURN NUMBER
76 IS
77 l_parent_id NUMBER := FND_API.G_MISS_NUM;
78 l_parent_index NUMBER := FND_API.G_MISS_NUM;
79 l_shipment_id NUMBER := FND_API.G_MISS_NUM;
80 l_shipment_index NUMBER := FND_API.G_MISS_NUM;
81 BEGIN
82
83 -- ids is a fall back. quotes should be using index
84 -- figure OUT NOCOPY /* file.sql.39 change */ the parent to which this service has to be linked.
85 -- this gives the line id/index
86
87 FOR i in 1..p_line_rltship_tbl.count LOOP
88 IF (p_line_rltship_tbl(i).related_quote_line_id = quote_line_id
89 OR p_line_rltship_tbl(i).related_qte_line_index = quote_line_index)
90 AND p_line_rltship_tbl(i).relationship_type_code = 'SERVICE'
91 THEN
92
93 l_parent_id := p_line_rltship_tbl(i).quote_line_id;
94 l_parent_index := p_line_rltship_tbl(i).qte_line_index;
95 exit;
96 END IF;
97 END LOOP;
98
99 -- figure OUT NOCOPY /* file.sql.39 change */ the corresponding shipment line id/index
100 -- If the service is a delayed service then both parent_id and parent_index
101 -- will be g_miss values.
102
103 IF l_parent_id <> FND_API.G_MISS_NUM
104 OR l_parent_index <> FND_API.G_MISS_NUM THEN
105
106 FOR i in 1..p_shipment_tbl.count LOOP
107 IF p_shipment_tbl(i).quote_line_id = l_parent_id
108 OR p_shipment_tbl(i).qte_line_index = l_parent_index THEN
109
110 l_shipment_id := p_shipment_tbl(i).shipment_id;
111 l_shipment_index := i;
112 exit;
113 END IF;
114 END LOOP;
115 END IF;
116
117 -- if every shipment line becomes one and only one quote line then i is the
118 -- index into the order line. however, this may not be the case in future.
119 -- then we will have to check the source code id to figure it out.
120
121
122 IF l_shipment_index <> FND_API.G_MISS_NUM THEN
123 return l_shipment_index;
124 END IF;
125
126 END;
127
128 -- usage: this function is needed because OM is writing messages into its on
129 -- stack and is not using the fnd stack. here the exception handlers will not
130 -- take care of it.
131
132 PROCEDURE Retrieve_OE_Messages IS
133 l_msg_count NUMBER;
134 l_msg_data VARCHAR2(2000);
135 x_msg_data VARCHAR2(2000);
136
137 l_len_sqlerrm NUMBER;
138 i NUMBER := 1;
139
140 l_error_index_flag VARCHAR2(1) := 'N';
141 l_msg_index NUMBER := 0;
142 l_msg_context VARCHAR2(2000);
143 l_msg_entity_code VARCHAR2(30);
144 l_msg_entity_ref VARCHAR2(50);
145 l_msg_entity_id NUMBER;
146 l_msg_header_id NUMBER;
147 l_msg_line_id NUMBER;
148 l_msg_order_source_id NUMBER;
149 l_msg_orig_sys_document_ref VARCHAR2(50);
150 l_msg_change_sequence VARCHAR2(50);
151 l_msg_orig_sys_line_ref VARCHAR2(50);
152 l_msg_orig_sys_shipment_ref VARCHAR2(50);
153 l_msg_source_document_type_id NUMBER;
154 l_msg_source_document_id NUMBER;
155 l_msg_source_document_line_id NUMBER;
156 l_msg_attribute_code VARCHAR2(50);
157 l_msg_constraint_id NUMBER;
158 l_msg_process_activity NUMBER;
159 l_msg_notification_flag VARCHAR2(1);
160 l_msg_type VARCHAR2(30);
161
162 BEGIN
163
164 OE_MSG_PUB.Count_And_Get
165 ( p_count => l_msg_count,
166 p_data => l_msg_data
167 );
168
169 IF l_msg_count > 0 THEN
170
171 IF aso_debug_pub.g_debug_flag = 'Y' THEN
172 aso_debug_pub.add('before updating the processing messages table',1,'N');
173 END IF;
174
175 FOR k IN 1 .. l_msg_count LOOP
176
177 i:=1;
178
179 IF aso_debug_pub.g_debug_flag = 'Y' THEN
180 aso_debug_pub.add('before calling oe_msg_pub.get',1,'N');
181 END IF;
182 oe_msg_pub.get (
183 p_msg_index => k
184 ,p_encoded => FND_API.G_FALSE
185 ,p_data => l_msg_data
186 ,p_msg_index_out => l_msg_index);
187
188 IF (upper(l_msg_data) <> 'ORDER HAS BEEN BOOKED.') THEN -- bug# 1935468
189
190 BEGIN
191 IF aso_debug_pub.g_debug_flag = 'Y' THEN
192 aso_debug_pub.add('before calling get_msg_context l_msg_index:'||l_msg_index,1,'N');
193 END IF;
194 oe_msg_pub.get_msg_context (
195 p_msg_index => l_msg_index
196 ,x_entity_code => l_msg_entity_code
197 ,x_entity_ref => l_msg_entity_ref
198 ,x_entity_id => l_msg_entity_id
199 ,x_header_id => l_msg_header_id
200 ,x_line_id => l_msg_line_id
201 ,x_order_source_id => l_msg_order_source_id
202 ,x_orig_sys_document_ref => l_msg_orig_sys_document_ref
203 ,x_orig_sys_line_ref => l_msg_orig_sys_line_ref
204 ,x_orig_sys_shipment_ref => l_msg_orig_sys_shipment_ref
205 ,x_change_sequence => l_msg_change_sequence
206 ,x_source_document_type_id => l_msg_source_document_type_id
207 ,x_source_document_id => l_msg_source_document_id
208 ,x_source_document_line_id => l_msg_source_document_line_id
209 ,x_attribute_code => l_msg_attribute_code
210 ,x_constraint_id => l_msg_constraint_id
211 ,x_process_activity => l_msg_process_activity
212 ,x_notification_flag => l_msg_notification_flag
213 ,x_type => l_msg_type
214 );
215
216 EXCEPTION
217 WHEN others THEN
218 IF aso_debug_pub.g_debug_flag = 'Y' THEN
219 aso_debug_pub.add('Unexpected error in get msg : '||sqlerrm,1,'N');
220 aso_debug_pub.add('Ignoring above message',1,'N');
221 END IF;
222 l_error_index_flag := 'Y';
223 END;
224
225 IF l_error_index_flag = 'Y' THEN
226 EXIT;
227 END IF;
228 IF aso_debug_pub.g_debug_flag = 'Y' THEN
229 aso_debug_pub.add('after calling get_msg_context',1,'N');
230 END IF;
231
232 IF oe_msg_pub.g_msg_tbl(l_msg_index).message_text IS NULL THEN
233 IF aso_debug_pub.g_debug_flag = 'Y' THEN
234 aso_debug_pub.add('In index.message_text is null',1,'N');
235 END IF;
236 x_msg_data := oe_msg_pub.get(l_msg_index, 'F');
237 END IF;
238
239 IF aso_debug_pub.g_debug_flag = 'Y' THEN
240 aso_debug_pub.add('l_msg_orig_sys_line_ref'||l_msg_orig_sys_line_ref,1,'N');
241 END IF;
242 IF l_msg_orig_sys_line_ref IS NOT NULL AND l_msg_orig_sys_line_ref <> FND_API.G_MISS_CHAR THEN
243 l_msg_context := 'Error in Line: '||rtrim(l_msg_orig_sys_line_ref)||' :';
244 END IF;
245
246 x_msg_data := l_msg_context||l_msg_data;
247
248 IF aso_debug_pub.g_debug_flag = 'Y' THEN
249 aso_debug_pub.add(x_msg_data,1,'N');
250 END IF;
251
252 l_len_sqlerrm := Length(x_msg_data) ;
253 WHILE l_len_sqlerrm >= i LOOP
254 FND_MESSAGE.Set_Name('ASO', 'ASO_OM_ERROR');
255 FND_MESSAGE.Set_token('MSG_TXT' , substr(x_msg_data,i,240));
256 i := i + 240;
257 FND_MSG_PUB.ADD;
258 END LOOP;
259
260 END IF; -- bug# 1935468
261
262 END LOOP;
263
264 END IF;
265
266 END Retrieve_OE_Messages;
267
268
269 PROCEDURE Map_quote_to_fulfillment(
270 P_Qte_Line_Tbl IN ASO_QUOTE_PUB.Qte_Line_Tbl_Type
271 := ASO_QUOTE_PUB.G_MISS_qte_line_TBL,
272 P_Line_ATTRIBS_EXT_Tbl IN ASO_QUOTE_PUB.Line_Attribs_Ext_Tbl_Type
273 := ASO_QUOTE_PUB.G_MISS_Line_Attribs_Ext_TBL,
274 -- P_fulfillment_tbl IN ASO_ORDER_INT.FULFILLMENT_TBL_TYPE,
275 X_ffm_content_tbl OUT NOCOPY /* file.sql.39 change */ ASO_FFM_INT.ffm_content_tbl_type,
276 X_ffm_bind_tbl OUT NOCOPY /* file.sql.39 change */ ASO_FFM_INT.ffm_bind_tbl_type
277 )
278 IS
279
280
281 /*
282 * 2611381: changed the cursor variable name so that it is not the
283 * same as column name.
284 */
285 -- hyang, bug 2860045, performance fix.
286 CURSOR C_fulfillment(lc_inventory_item_id NUMBER) IS
287 SELECT CAN_FULFILL_ELECTRONIC_FLAG , jtf_amv_item_id
288 FROM AMS_DELIVERABLES_ALL_B
289 WHERE inventory_item_id = lc_inventory_item_id;
290
291 fulfil_index NUMBER := 0;
292 i NUMBER;
293 j NUMBER;
294 l_electronic_flag VARCHAR2(1);
295 l_content_id NUMBER;
296
297 BEGIN
298
299
300 FOR i in 1..p_qte_line_tbl.count LOOP
301 Open C_fulfillment(p_qte_line_tbl(i).inventory_item_id);
302 FETCH C_fulfillment into l_electronic_flag, l_content_id;
303 Close C_fulfillment;
304
305 IF l_electronic_flag = FND_API.G_TRUE THEN
306 fulfil_index := fulfil_index + 1;
307 x_ffm_content_tbl(fulfil_index).content_id
308 := l_content_id;
309 x_ffm_content_tbl(fulfil_index).quantity
310 := p_qte_line_tbl(i).quantity;
311 x_ffm_content_tbl(fulfil_index).content_name
312 := p_qte_line_tbl(i).ffm_content_name;
313 x_ffm_content_tbl(fulfil_index).document_type
314 := p_qte_line_tbl(i).ffm_document_type;
315 x_ffm_content_tbl(fulfil_index).media_type
316 := p_qte_line_tbl(i).ffm_media_type;
317
318 IF x_ffm_content_tbl(fulfil_index).media_type = 'PRINTER' THEN
319 x_ffm_content_tbl(fulfil_index).printer:= p_qte_line_tbl(i).ffm_media_id;
320 ELSIF x_ffm_content_tbl(fulfil_index).media_type = 'EMAIL' THEN
321 x_ffm_content_tbl(fulfil_index).email := p_qte_line_tbl(i).ffm_media_id;
322 ELSIF x_ffm_content_tbl(fulfil_index).media_type = 'FAX' THEN
323 x_ffm_content_tbl(fulfil_index).fax := p_qte_line_tbl(i).ffm_media_id;
324 ELSIF x_ffm_content_tbl(fulfil_index).media_type = 'FILE' THEN
325 x_ffm_content_tbl(fulfil_index).file_path
326 := p_qte_line_tbl(i).ffm_media_id;
327 END IF;
328
329 FOR k in 1..p_line_attribs_ext_tbl.count LOOP
330 IF p_line_attribs_ext_tbl(k).qte_line_index = i THEN
331
332 x_ffm_bind_tbl(k).content_index := fulfil_index;
333 x_ffm_bind_tbl(k).bind_var := p_line_attribs_ext_tbl(k).name;
334 x_ffm_bind_tbl(k).bind_val := p_line_attribs_ext_tbl(k).value;
335 x_ffm_bind_tbl(k).bind_var_type
336 := p_line_attribs_ext_tbl(k).value_type;
337
338 END IF; -- quote_line_index
339
340 END LOOP; -- line attribs
341 END IF; -- electronic flag
342
343 END LOOP;
344
345 END Map_quote_to_fulfillment;
346
347
348 /*
349 ** convert from ASO_ORDER_INT.Sales_Credit and ASO_ORDER_INT.Lot_Serial
350 ** to ASO_QUOTE_PUB.Sales_Credit and ASO_QUOTE_PUB.Lot_Serial
351 ** respectively.
352 */
353 Procedure Map_Scredit_to_Qcredit(
354 P_header_sales_credit_TBL IN Sales_credit_tbl_type
355 := G_MISS_sales_credit_TBL,
356 P_Line_sales_credit_TBL IN Sales_credit_tbl_type
357 := G_MISS_sales_credit_TBL,
358 P_Lot_Serial_Tbl IN Lot_Serial_Tbl_Type
359 := G_MISS_Lot_Serial_Tbl,
360 x_header_sales_credit_TBL OUT NOCOPY /* file.sql.39 change */ ASO_QUOTE_PUB.Sales_credit_tbl_type,
361 x_Line_sales_credit_TBL OUT NOCOPY /* file.sql.39 change */ ASO_QUOTE_PUB.Sales_credit_tbl_type,
362 x_Lot_Serial_Tbl OUT NOCOPY /* file.sql.39 change */ ASO_QUOTE_PUB.Lot_Serial_Tbl_Type,
363 X_Return_Status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
364 X_Msg_Count OUT NOCOPY /* file.sql.39 change */ NUMBER,
365 X_Msg_Data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
366 )
367 IS
368 CURSOR resource_id(p_salesrepid NUMBER) IS
369 SELECT RESOURCE_ID
370 from jtf_rs_srp_vl
371 where SALESREP_ID = p_salesrepid;
372
373 BEGIN
374
375 FOR i in 1..P_header_sales_credit_TBL.Count LOOP
376 x_header_sales_credit_TBL(i).QTE_LINE_INDEX := P_header_sales_credit_TBL(i).line_index;
377 -- x_header_sales_credit_TBL(i).OPERATION_CODE := P_header_sales_credit_TBL(i).operation;
378 x_header_sales_credit_TBL(i).SALES_CREDIT_ID := P_header_sales_credit_TBL(i).sales_credit_id;
379 -- x_header_sales_credit_TBL(i).CREATION_DATE := P_header_sales_credit_TBL(i).;
380 -- x_header_sales_credit_TBL(i).CREATED_BY := P_header_sales_credit_TBL(i).;
381 -- x_header_sales_credit_TBL(i).LAST_UPDATED_BY := P_header_sales_credit_TBL(i).;
382 -- x_header_sales_credit_TBL(i).LAST_UPDATE_DATE := P_header_sales_credit_TBL(i).;
383 -- x_header_sales_credit_TBL(i).LAST_UPDATE_LOGIN := P_header_sales_credit_TBL(i).;
384 -- x_header_sales_credit_TBL(i).REQUEST_ID := P_header_sales_credit_TBL(i).;
385 -- x_header_sales_credit_TBL(i).PROGRAM_APPLICATION_ID := P_header_sales_credit_TBL(i).;
386 -- x_header_sales_credit_TBL(i).PROGRAM_ID := P_header_ sales_credit_TBL(i).;
387 --x_header_sales_credit_TBL(i).PROGRAM_UPDATE_DATE := P_header_sales_credit_TBL(i).;
388 --x_header_sales_credit_TBL(i).SECURITY_GROUP_ID := P_header_sales_credit_TBL(i).;
389 x_header_sales_credit_TBL(i).QUOTE_HEADER_ID := P_header_sales_credit_TBL(i).QUOTE_HEADER_ID;
390 x_header_sales_credit_TBL(i).QUOTE_LINE_ID := P_header_sales_credit_TBL(i).QUOTE_LINE_ID;
391 x_header_sales_credit_TBL(i).PERCENT := P_header_sales_credit_TBL(i).PERCENT;
392
393 OPEN resource_id(P_header_sales_credit_TBL(i).SALESREP_ID);
394 FETCH resource_id INTO x_header_sales_credit_TBL(i).RESOURCE_ID;
395 IF (resource_id%NOTFOUND) THEN
396 CLOSE resource_id;
397 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
398 FND_MESSAGE.Set_Name('ASO', 'API_INVALID_ID');
399 FND_MESSAGE.Set_Token('COLUMN', 'RESOURCE ID', FALSE);
400 FND_MSG_PUB.ADD;
401 END IF;
402 ELSE
403 CLOSE resource_id;
404 END IF;
405
406 /*
407 SELECT RESOURCE_ID into x_header_sales_credit_TBL(i).RESOURCE_ID
408 from jtf_rs_srp_vl
409 where SALESREP_ID = P_header_sales_credit_TBL(i).SALESREP_ID;
410 */
411
412 -- x_header_sales_credit_TBL(i).FIRST_NAME := P_header_sales_credit_TBL(i).;
413 -- x_header_sales_credit_TBL(i).LAST_NAME := P_header_sales_credit_TBL(i).;
414 -- x_header_sales_credit_TBL(i).SALES_CREDIT_TYPE := P_header_sales_credit_TBL(i).;
415 -- x_header_sales_credit_TBL(i).RESOURCE_GROUP_ID := P_header_sales_credit_TBL(i).;
416 -- x_header_sales_credit_TBL(i).EMPLOYEE_PERSON_ID := P_header_sales_credit_TBL(i).;
417 -- x_header_sales_credit_TBL(i).SALES_CREDIT_TYPE_ID := P_header_sales_credit_TBL(i).;
418 x_header_sales_credit_TBL(i).ATTRIBUTE_CATEGORY_CODE := P_header_sales_credit_TBL(i).CONTEXT;
419 x_header_sales_credit_TBL(i).ATTRIBUTE1 := P_header_sales_credit_TBL(i).ATTRIBUTE1;
420 x_header_sales_credit_TBL(i).ATTRIBUTE2 := P_header_sales_credit_TBL(i).ATTRIBUTE2;
421 x_header_sales_credit_TBL(i).ATTRIBUTE3 := P_header_sales_credit_TBL(i).ATTRIBUTE3;
422 x_header_sales_credit_TBL(i).ATTRIBUTE4 := P_header_sales_credit_TBL(i).ATTRIBUTE4;
423 x_header_sales_credit_TBL(i).ATTRIBUTE5 := P_header_sales_credit_TBL(i).ATTRIBUTE5;
424 x_header_sales_credit_TBL(i).ATTRIBUTE6 := P_header_sales_credit_TBL(i).ATTRIBUTE6;
425 x_header_sales_credit_TBL(i).ATTRIBUTE7 := P_header_sales_credit_TBL(i).ATTRIBUTE7;
426 x_header_sales_credit_TBL(i).ATTRIBUTE8 := P_header_sales_credit_TBL(i).ATTRIBUTE8;
427 x_header_sales_credit_TBL(i).ATTRIBUTE9 := P_header_sales_credit_TBL(i).ATTRIBUTE9;
428 x_header_sales_credit_TBL(i).ATTRIBUTE10 := P_header_sales_credit_TBL(i).ATTRIBUTE10;
429 x_header_sales_credit_TBL(i).ATTRIBUTE11 := P_header_sales_credit_TBL(i).ATTRIBUTE11;
430 x_header_sales_credit_TBL(i).ATTRIBUTE12 := P_header_sales_credit_TBL(i).ATTRIBUTE12;
431 x_header_sales_credit_TBL(i).ATTRIBUTE13 := P_header_sales_credit_TBL(i).ATTRIBUTE13;
432 x_header_sales_credit_TBL(i).ATTRIBUTE14 := P_header_sales_credit_TBL(i).ATTRIBUTE14;
433 x_header_sales_credit_TBL(i).ATTRIBUTE15 := P_header_sales_credit_TBL(i).ATTRIBUTE15;
434 END LOOP;
435
436
437
438 FOR i in 1..P_line_sales_credit_TBL.Count LOOP
439
440 x_line_sales_credit_TBL(i).QTE_LINE_INDEX := P_line_sales_credit_TBL(i).line_index;
441 --x_line_sales_credit_TBL(i).OPERATION_CODE := P_line_sales_credit_TBL(i).operation;
442 x_line_sales_credit_TBL(i).SALES_CREDIT_ID := P_line_sales_credit_TBL(i).sales_credit_id;
443 -- x_line_sales_credit_TBL(i).CREATION_DATE := P_line_sales_credit_TBL(i).;
444 -- x_line_sales_credit_TBL(i).CREATED_BY := P_line_sales_credit_TBL(i).;
445 -- x_line_sales_credit_TBL(i).LAST_UPDATED_BY := P_line_sales_credit_TBL(i).;
446 -- x_line_sales_credit_TBL(i).LAST_UPDATE_DATE := P_line_sales_credit_TBL(i).;
447 -- x_line_sales_credit_TBL(i).LAST_UPDATE_LOGIN := P_line_sales_credit_TBL(i).;
448 -- x_line_sales_credit_TBL(i).REQUEST_ID := P_line_sales_credit_TBL(i).;
449 -- x_line_sales_credit_TBL(i).PROGRAM_APPLICATION_ID := P_line_sales_credit_TBL(i).;
450 -- x_line_sales_credit_TBL(i).PROGRAM_ID := P_line_sales_credit_TBL(i).;
451 --x_line_sales_credit_TBL(i).PROGRAM_UPDATE_DATE := P_line_sales_credit_TBL(i).;
452 --x_line_sales_credit_TBL(i).SECURITY_GROUP_ID := P_line_sale_credit_TBL(i).;
453 x_line_sales_credit_TBL(i).QUOTE_HEADER_ID := P_line_sales_credit_TBL(i).QUOTE_HEADER_ID;
454 x_line_sales_credit_TBL(i).QUOTE_LINE_ID := P_line_sales_credit_TBL(i).QUOTE_LINE_ID;
455 x_line_sales_credit_TBL(i).PERCENT := P_line_sales_credit_TBL(i).PERCENT;
456
457 OPEN resource_id(P_line_sales_credit_TBL(i).SALESREP_ID);
458 FETCH resource_id into x_line_sales_credit_TBL(i).RESOURCE_ID;
459 IF (resource_id%NOTFOUND) THEN
460 CLOSE resource_id;
461 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
462 FND_MESSAGE.Set_Name('ASO', 'API_INVALID_ID');
463 FND_MESSAGE.Set_Token('COLUMN', 'RESOURCE ID', FALSE);
464 FND_MSG_PUB.ADD;
465 END IF;
466 ELSE
467 CLOSE resource_id;
468 END IF;
469
470
471 /*
472 SELECT RESOURCE_ID into x_line_sales_credit_TBL(i).RESOURCE_ID
473 from jtf_rs_srp_vl
474 where SALESREP_ID = P_line_sales_credit_TBL(i).SALESREP_ID;
475 */
476
477 -- x_line_sales_credit_TBL(i).FIRST_NAME := P_line_sales_credit_TBL(i).;
478 -- x_line_sales_credit_TBL(i).LAST_NAME := P_line_sales_credit_TBL(i).;
479 -- x_line_sales_credit_TBL(i).SALES_CREDIT_TYPE := P_line_sales_credit_TBL(i).;
480 -- x_line_sales_credit_TBL(i).RESOURCE_GROUP_ID := P_line_sales_credit_TBL(i).;
481 -- x_line_sales_credit_TBL(i).EMPLOYEE_PERSON_ID := P_line_sales_credit_TBL(i).;
482 -- x_line_sales_credit_TBL(i).SALES_CREDIT_TYPE_ID := P_line_sales_credit_TBL(i).;
483 x_line_sales_credit_TBL(i).ATTRIBUTE_CATEGORY_CODE := P_line_sales_credit_TBL(i).CONTEXT;
484 x_line_sales_credit_TBL(i).ATTRIBUTE1 := P_line_sales_credit_TBL(i).ATTRIBUTE1;
485 x_line_sales_credit_TBL(i).ATTRIBUTE2 := P_line_sales_credit_TBL(i).ATTRIBUTE2;
486 x_line_sales_credit_TBL(i).ATTRIBUTE3 := P_line_sales_credit_TBL(i).ATTRIBUTE3;
487 x_line_sales_credit_TBL(i).ATTRIBUTE4 := P_line_sales_credit_TBL(i).ATTRIBUTE4;
488 x_line_sales_credit_TBL(i).ATTRIBUTE5 := P_line_sales_credit_TBL(i).ATTRIBUTE5;
489 x_line_sales_credit_TBL(i).ATTRIBUTE6 := P_line_sales_credit_TBL(i).ATTRIBUTE6;
490 x_line_sales_credit_TBL(i).ATTRIBUTE7 := P_line_sales_credit_TBL(i).ATTRIBUTE7;
491 x_line_sales_credit_TBL(i).ATTRIBUTE8 := P_line_sales_credit_TBL(i).ATTRIBUTE8;
492 x_line_sales_credit_TBL(i).ATTRIBUTE9 := P_line_sales_credit_TBL(i).ATTRIBUTE9;
493 x_line_sales_credit_TBL(i).ATTRIBUTE10 := P_line_sales_credit_TBL(i).ATTRIBUTE10;
494 x_line_sales_credit_TBL(i).ATTRIBUTE11 := P_line_sales_credit_TBL(i).ATTRIBUTE11;
495 x_line_sales_credit_TBL(i).ATTRIBUTE12 := P_line_sales_credit_TBL(i).ATTRIBUTE12;
496 x_line_sales_credit_TBL(i).ATTRIBUTE13 := P_line_sales_credit_TBL(i).ATTRIBUTE13;
497 x_line_sales_credit_TBL(i).ATTRIBUTE14 := P_line_sales_credit_TBL(i).ATTRIBUTE14;
498 x_line_sales_credit_TBL(i).ATTRIBUTE15 := P_line_sales_credit_TBL(i).ATTRIBUTE15;
499
500 END LOOP;
501
502
503 FOR i in 1..p_Lot_Serial_Tbl.COUNT LOOP
504 x_Lot_Serial_Tbl(i).attribute1 := p_Lot_Serial_Tbl(i).attribute1 ;
505 x_Lot_Serial_Tbl(i).attribute10 := p_Lot_Serial_Tbl(i).attribute10 ;
506 x_Lot_Serial_Tbl(i).attribute11 := p_Lot_Serial_Tbl(i).attribute11 ;
507 x_Lot_Serial_Tbl(i).attribute12 := p_Lot_Serial_Tbl(i).attribute12 ;
508 x_Lot_Serial_Tbl(i).attribute13 := p_Lot_Serial_Tbl(i).attribute13 ;
509 x_Lot_Serial_Tbl(i).attribute14 := p_Lot_Serial_Tbl(i).attribute14 ;
510 x_Lot_Serial_Tbl(i).attribute15 := p_Lot_Serial_Tbl(i).attribute15 ;
511 x_Lot_Serial_Tbl(i).attribute2 := p_Lot_Serial_Tbl(i).attribute2 ;
512 x_Lot_Serial_Tbl(i).attribute3 := p_Lot_Serial_Tbl(i).attribute3 ;
513 x_Lot_Serial_Tbl(i).attribute4 := p_Lot_Serial_Tbl(i).attribute4 ;
514 x_Lot_Serial_Tbl(i).attribute5 := p_Lot_Serial_Tbl(i).attribute5 ;
515 x_Lot_Serial_Tbl(i).attribute6 := p_Lot_Serial_Tbl(i).attribute6 ;
516 x_Lot_Serial_Tbl(i).attribute7 := p_Lot_Serial_Tbl(i).attribute7 ;
517 x_Lot_Serial_Tbl(i).attribute8 := p_Lot_Serial_Tbl(i).attribute8 ;
518 x_Lot_Serial_Tbl(i).attribute9 := p_Lot_Serial_Tbl(i).attribute9 ;
519 x_Lot_Serial_Tbl(i).context := p_Lot_Serial_Tbl(i).context ;
520 x_Lot_Serial_Tbl(i).created_by := p_Lot_Serial_Tbl(i).created_by ;
521 x_Lot_Serial_Tbl(i).creation_date := p_Lot_Serial_Tbl(i).creation_date ;
522 x_Lot_Serial_Tbl(i).from_serial_number := p_Lot_Serial_Tbl(i).from_serial_number ;
523 x_Lot_Serial_Tbl(i).last_updated_by := p_Lot_Serial_Tbl(i).last_updated_by ;
524 x_Lot_Serial_Tbl(i).last_update_date := p_Lot_Serial_Tbl(i).last_update_date ;
525 x_Lot_Serial_Tbl(i).last_update_login := p_Lot_Serial_Tbl(i).last_update_login ;
526 x_Lot_Serial_Tbl(i).line_id := p_Lot_Serial_Tbl(i).line_id ;
527 x_Lot_Serial_Tbl(i).lot_number := p_Lot_Serial_Tbl(i).lot_number;
528 x_Lot_Serial_Tbl(i).lot_serial_id := p_Lot_Serial_Tbl(i).lot_serial_id ;
529 x_Lot_Serial_Tbl(i).quantity := p_Lot_Serial_Tbl(i).quantity ;
530 x_Lot_Serial_Tbl(i).to_serial_number := p_Lot_Serial_Tbl(i).to_serial_number ;
531 x_Lot_Serial_Tbl(i).return_status := p_Lot_Serial_Tbl(i).return_status ;
532 x_Lot_Serial_Tbl(i).db_flag := p_Lot_Serial_Tbl(i).db_flag ;
533 x_Lot_Serial_Tbl(i).operation := p_Lot_Serial_Tbl(i).operation ;
534 x_Lot_Serial_Tbl(i).line_index := p_Lot_Serial_Tbl(i).line_index ;
535 x_Lot_Serial_Tbl(i).orig_sys_lotserial_ref := p_Lot_Serial_Tbl(i).orig_sys_lotserial_ref ;
536 x_Lot_Serial_Tbl(i).change_request_code := p_Lot_Serial_Tbl(i).change_request_code ;
537 x_Lot_Serial_Tbl(i).status_flag := p_Lot_Serial_Tbl(i).status_flag ;
538 x_Lot_Serial_Tbl(i).line_set_id := p_Lot_Serial_Tbl(i).line_set_id;
539
540 END LOOP;
541
542
543 END Map_Scredit_to_Qcredit;
544
545
546
547 PROCEDURE Create_order(
548 P_Api_Version_Number IN NUMBER,
549 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
550 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
551 P_Qte_Rec IN ASO_QUOTE_PUB.Qte_Header_Rec_Type
552 := ASO_QUOTE_PUB.G_MISS_QTE_HEADER_REC,
553 P_Header_Payment_Tbl IN ASO_QUOTE_PUB.Payment_Tbl_Type
554 := ASO_QUOTE_PUB.G_MISS_Payment_TBL,
555 P_Header_Price_Adj_Tbl IN ASO_QUOTE_PUB.Price_Adj_Tbl_Type
556 := ASO_QUOTE_PUB.G_MISS_Price_Adj_TBL,
557 P_Header_Price_Attributes_Tbl IN ASO_QUOTE_PUB.Price_Attributes_Tbl_Type
558 := ASO_QUOTE_PUB.G_MISS_Price_attributes_TBL,
559 P_Header_Price_Adj_rltship_Tbl IN ASO_QUOTE_PUB.Price_Adj_Rltship_Tbl_Type
560 := ASO_QUOTE_PUB.G_MISS_price_adj_rltship_TBL,
561 P_Header_Price_Adj_Attr_Tbl IN ASO_QUOTE_PUB.Price_Adj_Attr_Tbl_Type
562 := ASO_QUOTE_PUB.G_Miss_PRICE_ADJ_ATTR_Tbl,
563 P_Header_Shipment_Tbl IN ASO_QUOTE_PUB.Shipment_Tbl_Type
564 := ASO_QUOTE_PUB.G_MISS_shipment_TBL,
565 P_Header_TAX_DETAIL_Tbl IN ASO_QUOTE_PUB.TAX_DETAIL_Tbl_Type
566 := ASO_QUOTE_PUB.G_MISS_TAX_DETAIL_TBL,
567 P_Header_FREIGHT_CHARGE_Tbl IN ASO_QUOTE_PUB.FREIGHT_CHARGE_Tbl_Type
568 := ASO_QUOTE_PUB.G_MISS_FREIGHT_CHARGE_Tbl,
569 P_header_sales_credit_TBL IN Sales_credit_tbl_type
570 := G_MISS_sales_credit_TBL,
571 P_Qte_Line_Tbl IN ASO_QUOTE_PUB.Qte_Line_Tbl_Type
572 := ASO_QUOTE_PUB.G_MISS_qte_line_TBL,
573 P_Qte_Line_Dtl_tbl IN ASO_QUOTE_PUB.Qte_Line_Dtl_tbl_Type
574 := ASO_QUOTE_PUB.G_MISS_qte_line_dtl_TBL,
575 P_Line_Payment_Tbl IN ASO_QUOTE_PUB.Payment_Tbl_Type
576 := ASO_QUOTE_PUB.G_MISS_Payment_TBL,
577 P_Line_Price_Adj_Tbl IN ASO_QUOTE_PUB.Price_Adj_Tbl_Type
578 := ASO_QUOTE_PUB.G_MISS_Price_Adj_TBL,
579 P_Line_Price_Attributes_Tbl IN ASO_QUOTE_PUB.Price_Attributes_Tbl_Type
580 := ASO_QUOTE_PUB.G_MISS_Price_attributes_TBL,
581 P_Line_Price_Adj_rltship_Tbl IN ASO_QUOTE_PUB.Price_Adj_Rltship_Tbl_Type
582 := ASO_QUOTE_PUB.G_MISS_price_adj_rltship_TBL,
583 P_Line_Price_Adj_Attr_Tbl IN ASO_QUOTE_PUB.Price_Adj_Attr_Tbl_Type
584 := ASO_QUOTE_PUB.G_Miss_PRICE_ADJ_ATTR_Tbl,
585 P_Line_Shipment_Tbl IN ASO_QUOTE_PUB.Shipment_Tbl_Type
586 := ASO_QUOTE_PUB.G_MISS_shipment_TBL,
587 P_Line_TAX_DETAIL_Tbl IN ASO_QUOTE_PUB.TAX_DETAIL_Tbl_Type
588 := ASO_QUOTE_PUB.G_MISS_TAX_DETAIL_TBL,
589 P_Line_FREIGHT_CHARGE_Tbl IN ASO_QUOTE_PUB.FREIGHT_CHARGE_Tbl_Type
590 := ASO_QUOTE_PUB.G_MISS_FREIGHT_CHARGE_Tbl,
591 P_Line_ATTRIBS_EXT_Tbl IN ASO_QUOTE_PUB.Line_Attribs_Ext_Tbl_Type
592 := ASO_QUOTE_PUB.G_MISS_Line_Attribs_Ext_TBL,
593 P_Line_Rltship_Tbl IN ASO_QUOTE_PUB.Line_Rltship_Tbl_Type
594 := ASO_QUOTE_PUB.G_MISS_line_rltship_TBL,
595 P_Line_sales_credit_TBL IN Sales_credit_tbl_type
596 := G_MISS_sales_credit_TBL,
597 P_Lot_Serial_Tbl IN Lot_Serial_Tbl_Type
598 := G_MISS_Lot_Serial_Tbl,
599 P_Control_Rec IN Control_Rec_Type := G_MISS_Control_Rec,
600 X_Order_Header_Rec OUT NOCOPY /* file.sql.39 change */ Order_Header_Rec_Type,
601 X_Order_Line_Tbl OUT NOCOPY /* file.sql.39 change */ Order_Line_Tbl_type,
602 X_Return_Status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
603 X_Msg_Count OUT NOCOPY /* file.sql.39 change */ NUMBER,
604 X_Msg_Data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
605 )
606 IS
607
608 l_api_version_number CONSTANT NUMBER := 1.0;
609 l_api_name CONSTANT VARCHAR2(30):= 'Create_Order';
610 l_header_sales_credit_TBL ASO_QUOTE_PUB.Sales_credit_tbl_type := ASO_QUOTE_PUB.G_MISS_sales_credit_TBL;
611 l_Line_sales_credit_TBL ASO_QUOTE_PUB.Sales_credit_tbl_type := ASO_QUOTE_PUB.G_MISS_sales_credit_TBL;
612 l_Lot_Serial_Tbl ASO_QUOTE_PUB.Lot_Serial_Tbl_Type := ASO_QUOTE_PUB.G_MISS_Lot_Serial_Tbl;
613
614 BEGIN
615
616
617 -- Standard Start of API savepoint
618 SAVEPOINT CREATE_ORDER_PVT;
619
620 aso_debug_pub.g_debug_flag := nvl(fnd_profile.value('ASO_ENABLE_DEBUG'),'N');
621
622 -- Standard call to check for call compatibility.
623 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
624 p_api_version_number,
625 l_api_name,
626 G_PKG_NAME)
627 THEN
628 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
629 END IF;
630
631
632 -- Initialize message list if p_init_msg_list is set to TRUE.
633 IF FND_API.to_Boolean( p_init_msg_list )
634 THEN
635 FND_MSG_PUB.initialize;
636 END IF;
637
638 -- Debug Message
639 ASO_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: ' || l_api_name || 'start');
640
641 -- Initialize API return status to SUCCESS
642 x_return_status := FND_API.G_RET_STS_SUCCESS;
643
644 --
645 -- API body
646 --
647
648 Map_Scredit_to_Qcredit(
649 P_header_sales_credit_TBL => p_header_sales_credit_TBL,
650 P_Line_sales_credit_TBL => P_Line_sales_credit_TBL,
651 P_Lot_Serial_Tbl => P_Lot_Serial_Tbl,
652 x_header_sales_credit_TBL => l_header_sales_credit_TBL,
653 x_Line_sales_credit_TBL => l_Line_sales_credit_TBL,
654 x_Lot_Serial_Tbl => l_Lot_Serial_Tbl,
655 X_Return_Status => X_Return_Status,
656 X_Msg_Count => X_Msg_Count,
657 X_Msg_Data => X_Msg_Data
658 );
659
660
661 -- call the overloaded procedure create_order
662
663 Create_Order(
664 P_Api_Version => l_api_version_number,
665 P_Init_Msg_List =>P_Init_Msg_List,
666 P_Commit =>p_commit,
667 P_Qte_Rec => P_Qte_Rec,
668 P_Header_Payment_Tbl => P_Header_Payment_Tbl,
669 P_Header_Price_Adj_Tbl => P_Header_Price_Adj_Tbl,
670 P_Header_Price_Attributes_Tbl => P_Header_Price_Attributes_Tbl,
671 P_Header_Price_Adj_rltship_Tbl =>P_Header_Price_Adj_rltship_Tbl,
672 P_Header_Price_Adj_Attr_Tbl => P_Header_Price_Adj_Attr_Tbl,
673 P_Header_Shipment_Tbl => P_Header_Shipment_Tbl,
674 P_Header_TAX_DETAIL_Tbl =>P_Header_TAX_DETAIL_Tbl,
675 P_Header_FREIGHT_CHARGE_Tbl => P_Header_FREIGHT_CHARGE_Tbl,
676 P_Header_ATTRIBS_EXT_Tbl => ASO_QUOTE_PUB.G_MISS_Line_Attribs_Ext_TBL,
677 P_Header_Quote_Party_Tbl => ASO_QUOTE_PUB.G_MISS_QUOTE_PARTY_TBL,
678 P_header_sales_credit_TBL => l_header_sales_credit_TBL,
679 P_Qte_Line_Tbl => P_Qte_Line_Tbl,
680 P_Qte_Line_Dtl_Tbl => P_Qte_Line_Dtl_Tbl,
681 P_Line_Payment_Tbl => P_Line_Payment_Tbl,
682 P_Line_Price_Adj_Tbl => P_Line_Price_Adj_Tbl,
683 P_Line_Price_Attributes_Tbl => P_Line_Price_Attributes_Tbl,
684 P_Line_Price_Adj_rltship_Tbl => P_Line_Price_Adj_rltship_Tbl,
685 P_Line_Price_Adj_Attr_Tbl => P_Line_Price_Adj_Attr_Tbl,
686 P_Line_Shipment_Tbl => P_Line_Shipment_Tbl,
687 P_Line_TAX_DETAIL_Tbl => P_Line_TAX_DETAIL_Tbl,
688 P_Line_FREIGHT_CHARGE_Tbl => P_Line_FREIGHT_CHARGE_Tbl,
689 P_Line_ATTRIBS_EXT_Tbl =>P_Line_ATTRIBS_EXT_Tbl,
690 P_Line_Rltship_Tbl => P_Line_Rltship_Tbl,
691 P_Line_sales_credit_TBL => l_line_sales_credit_TBL,
692 P_Line_Quote_Party_Tbl => ASO_QUOTE_PUB.G_MISS_QUOTE_PARTY_TBL,
693 P_Lot_Serial_Tbl => l_Lot_Serial_Tbl,
694 P_Control_Rec => P_Control_Rec,
695 X_Order_Header_Rec => X_Order_Header_Rec,
696 X_Order_Line_Tbl => X_Order_Line_Tbl,
697 X_Return_Status => X_Return_Status,
698 X_Msg_Count => X_Msg_Count,
699 X_Msg_Data => X_Msg_Data
700 );
701 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
702 -- retrieve_oe_messages;
703 RAISE FND_API.G_EXC_ERROR;
704 END IF;
705
706
707 -- End of API body
708 --
709
710 -- Standard check for p_commit
711 IF FND_API.to_Boolean( p_commit )
712 THEN
713 COMMIT WORK;
714 END IF;
715
716
717
718 -- Standard call to get message count and if count is 1, get message info.
719 FND_MSG_PUB.Count_And_Get
720 ( p_count => x_msg_count,
721 p_data => x_msg_data
722 );
723
724 EXCEPTION
725 WHEN FND_API.G_EXC_ERROR THEN
726 null;
727 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
728 P_API_NAME => L_API_NAME
729 ,P_PKG_NAME => G_PKG_NAME
730 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
731 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
732 ,X_MSG_COUNT => X_MSG_COUNT
733 ,X_MSG_DATA => X_MSG_DATA
734 ,X_RETURN_STATUS => X_RETURN_STATUS);
735
736 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
737 null;
738 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
739 P_API_NAME => L_API_NAME
740 ,P_PKG_NAME => G_PKG_NAME
741 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
742 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
743 ,X_MSG_COUNT => X_MSG_COUNT
744 ,X_MSG_DATA => X_MSG_DATA
745 ,X_RETURN_STATUS => X_RETURN_STATUS);
746 WHEN OTHERS THEN
747 null;
748 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
749 P_API_NAME => L_API_NAME
750 ,P_PKG_NAME => G_PKG_NAME
751 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
752 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
753 ,X_MSG_COUNT => X_MSG_COUNT
754 ,X_MSG_DATA => X_MSG_DATA
755 ,X_RETURN_STATUS => X_RETURN_STATUS);
756
757 END Create_Order;
758
759
760 PROCEDURE Update_order(
761 P_Api_Version_Number IN NUMBER,
762 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
763 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
764 P_Qte_Rec IN ASO_QUOTE_PUB.Qte_Header_Rec_Type
765 := ASO_QUOTE_PUB.G_MISS_QTE_HEADER_REC,
766 P_Header_Payment_Tbl IN ASO_QUOTE_PUB.Payment_Tbl_Type
767 := ASO_QUOTE_PUB.G_MISS_Payment_TBL,
768 P_Header_Price_Adj_Tbl IN ASO_QUOTE_PUB.Price_Adj_Tbl_Type
769 := ASO_QUOTE_PUB.G_MISS_Price_Adj_TBL,
770 P_Header_Price_Attributes_Tbl IN ASO_QUOTE_PUB.Price_Attributes_Tbl_Type
771 := ASO_QUOTE_PUB.G_MISS_Price_attributes_TBL,
772 P_Header_Price_Adj_rltship_Tbl IN ASO_QUOTE_PUB.Price_Adj_Rltship_Tbl_Type
773 := ASO_QUOTE_PUB.G_MISS_price_adj_rltship_TBL,
774 P_Header_Price_Adj_Attr_Tbl IN ASO_QUOTE_PUB.Price_Adj_Attr_Tbl_Type
775 := ASO_QUOTE_PUB.G_Miss_PRICE_ADJ_ATTR_Tbl,
776 P_Header_Shipment_Tbl IN ASO_QUOTE_PUB.Shipment_Tbl_Type
777 := ASO_QUOTE_PUB.G_MISS_shipment_TBL,
778 P_Header_TAX_DETAIL_Tbl IN ASO_QUOTE_PUB.TAX_DETAIL_Tbl_Type
779 := ASO_QUOTE_PUB.G_MISS_TAX_DETAIL_TBL,
780 P_Header_FREIGHT_CHARGE_Tbl IN ASO_QUOTE_PUB.FREIGHT_CHARGE_Tbl_Type
781 := ASO_QUOTE_PUB.G_MISS_FREIGHT_CHARGE_Tbl,
782 P_header_sales_credit_TBL IN Sales_credit_tbl_type
783 := G_MISS_sales_credit_TBL,
784 P_Qte_Line_Tbl IN ASO_QUOTE_PUB.Qte_Line_Tbl_Type
785 := ASO_QUOTE_PUB.G_MISS_qte_line_TBL,
786 P_Qte_Line_Dtl_tbl IN ASO_QUOTE_PUB.Qte_Line_Dtl_Tbl_Type
787 := ASO_QUOTE_PUB.G_MISS_qte_line_dtl_TBL,
788 P_Line_Payment_Tbl IN ASO_QUOTE_PUB.Payment_Tbl_Type
789 := ASO_QUOTE_PUB.G_MISS_Payment_TBL,
790 P_Line_Price_Adj_Tbl IN ASO_QUOTE_PUB.Price_Adj_Tbl_Type
791 := ASO_QUOTE_PUB.G_MISS_Price_Adj_TBL,
792 P_Line_Price_Attributes_Tbl IN ASO_QUOTE_PUB.Price_Attributes_Tbl_Type
793 := ASO_QUOTE_PUB.G_MISS_Price_attributes_TBL,
794 P_Line_Price_Adj_rltship_Tbl IN ASO_QUOTE_PUB.Price_Adj_Rltship_Tbl_Type
795 := ASO_QUOTE_PUB.G_MISS_price_adj_rltship_TBL,
796 P_Line_Price_Adj_Attr_Tbl IN ASO_QUOTE_PUB.Price_Adj_Attr_Tbl_Type
797 := ASO_QUOTE_PUB.G_Miss_PRICE_ADJ_ATTR_Tbl,
798 P_Line_Shipment_Tbl IN ASO_QUOTE_PUB.Shipment_Tbl_Type
799 := ASO_QUOTE_PUB.G_MISS_shipment_TBL,
800 P_Line_TAX_DETAIL_Tbl IN ASO_QUOTE_PUB.TAX_DETAIL_Tbl_Type
801 := ASO_QUOTE_PUB.G_MISS_TAX_DETAIL_TBL,
802 P_Line_FREIGHT_CHARGE_Tbl IN ASO_QUOTE_PUB.FREIGHT_CHARGE_Tbl_Type
803 := ASO_QUOTE_PUB.G_MISS_FREIGHT_CHARGE_Tbl,
804 P_Line_Rltship_Tbl IN ASO_QUOTE_PUB.Line_Rltship_Tbl_Type
805 := ASO_QUOTE_PUB.G_MISS_line_rltship_TBL,
806 P_Line_sales_credit_TBL IN Sales_credit_tbl_type
807 := G_MISS_sales_credit_TBL,
808 P_Lot_Serial_Tbl IN Lot_Serial_Tbl_Type
809 := G_MISS_Lot_Serial_Tbl,
810 P_Control_Rec IN Control_Rec_Type := G_MISS_Control_Rec,
811 X_Order_Header_Rec OUT NOCOPY /* file.sql.39 change */ Order_Header_Rec_Type,
812 X_Order_Line_Tbl OUT NOCOPY /* file.sql.39 change */ Order_Line_Tbl_type,
813 X_Return_Status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
814 X_Msg_Count OUT NOCOPY /* file.sql.39 change */ NUMBER,
815 X_Msg_Data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
816 )
817 IS
818
819 l_api_version_number CONSTANT NUMBER := 1.0;
820 l_api_name CONSTANT VARCHAR2(30):= 'Update_Order';
821 l_header_sales_credit_TBL ASO_QUOTE_PUB.Sales_credit_tbl_type := ASO_QUOTE_PUB.G_MISS_sales_credit_TBL;
822 l_Line_sales_credit_TBL ASO_QUOTE_PUB.Sales_credit_tbl_type := ASO_QUOTE_PUB.G_MISS_sales_credit_TBL;
823 l_Lot_Serial_Tbl ASO_QUOTE_PUB.Lot_Serial_Tbl_Type := ASO_QUOTE_PUB.G_MISS_Lot_Serial_Tbl;
824
825 BEGIN
826 -- Standard Start of API savepoint
827 SAVEPOINT UPDATE_order_PVT;
828
829 aso_debug_pub.g_debug_flag := nvl(fnd_profile.value('ASO_ENABLE_DEBUG'),'N');
830
831 -- Standard call to check for call compatibility.
832 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
833 p_api_version_number,
834 l_api_name,
835 G_PKG_NAME)
836 THEN
837 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
838 END IF;
839
840
841 -- Initialize message list if p_init_msg_list is set to TRUE.
842 IF FND_API.to_Boolean( p_init_msg_list )
843 THEN
844 FND_MSG_PUB.initialize;
845 END IF;
846
847 -- Debug Message
848 ASO_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: ' || l_api_name || 'start');
849
850 -- Initialize API return status to SUCCESS
851 x_return_status := FND_API.G_RET_STS_SUCCESS;
852
853 --
854 -- API body
855 --
856
857
858
859 Map_Scredit_to_Qcredit(
860 P_header_sales_credit_TBL => p_header_sales_credit_TBL,
861 P_Line_sales_credit_TBL => P_Line_sales_credit_TBL,
862 P_Lot_Serial_Tbl => P_Lot_Serial_Tbl,
863 x_header_sales_credit_TBL => l_header_sales_credit_TBL,
864 x_Line_sales_credit_TBL => l_Line_sales_credit_TBL,
865 x_Lot_Serial_Tbl => l_Lot_Serial_Tbl,
866 X_Return_Status => X_Return_Status,
867 X_Msg_Count => X_Msg_Count,
868 X_Msg_Data => X_Msg_Data
869 );
870
871
872
873 -- call overloaded procedure UpdatE_order.
874 Update_Order(
875 P_Api_Version => l_api_version_number,
876 P_Init_Msg_List =>P_Init_Msg_List,
877 P_Commit =>p_commit,
878 P_Qte_Rec => P_Qte_Rec,
879 P_Header_Payment_Tbl => P_Header_Payment_Tbl,
880 P_Header_Price_Adj_Tbl => P_Header_Price_Adj_Tbl,
881 P_Header_Price_Attributes_Tbl => P_Header_Price_Attributes_Tbl,
882 P_Header_Price_Adj_rltship_Tbl =>P_Header_Price_Adj_rltship_Tbl,
883 P_Header_Price_Adj_Attr_Tbl => P_Header_Price_Adj_Attr_Tbl,
884 P_Header_Shipment_Tbl => P_Header_Shipment_Tbl,
885 P_Header_TAX_DETAIL_Tbl =>P_Header_TAX_DETAIL_Tbl,
886 P_Header_FREIGHT_CHARGE_Tbl => P_Header_FREIGHT_CHARGE_Tbl,
887 P_Header_ATTRIBS_EXT_Tbl => ASO_QUOTE_PUB.G_MISS_Line_Attribs_Ext_TBL,
888 P_Header_Quote_Party_Tbl => ASO_QUOTE_PUB.G_MISS_QUOTE_PARTY_TBL,
889 P_header_sales_credit_TBL => l_header_sales_credit_TBL,
890 P_Qte_Line_Tbl => P_Qte_Line_Tbl,
891 P_Qte_Line_Dtl_Tbl => P_Qte_Line_Dtl_Tbl,
892 P_Line_Payment_Tbl => P_Line_Payment_Tbl,
893 P_Line_Price_Adj_Tbl => P_Line_Price_Adj_Tbl,
894 P_Line_Price_Attributes_Tbl => P_Line_Price_Attributes_Tbl,
895 P_Line_Price_Adj_rltship_Tbl => P_Line_Price_Adj_rltship_Tbl,
896 P_Line_Price_Adj_Attr_Tbl => P_Line_Price_Adj_Attr_Tbl,
897 P_Line_Shipment_Tbl => P_Line_Shipment_Tbl,
898 P_Line_TAX_DETAIL_Tbl => P_Line_TAX_DETAIL_Tbl,
899 P_Line_FREIGHT_CHARGE_Tbl => P_Line_FREIGHT_CHARGE_Tbl,
900 P_Line_ATTRIBS_EXT_Tbl =>ASO_QUOTE_PUB.G_MISS_Line_Attribs_Ext_TBL,
901 P_Line_Rltship_Tbl => P_Line_Rltship_Tbl,
902 P_Line_sales_credit_TBL => l_Line_sales_credit_TBL ,
903 P_Line_Quote_Party_Tbl => ASO_QUOTE_PUB.G_MISS_QUOTE_PARTY_TBL,
904 P_Lot_Serial_Tbl => l_Lot_Serial_Tbl,
905 P_Control_Rec => P_Control_Rec,
906 X_Order_Header_Rec => X_Order_Header_Rec,
907 X_Order_Line_Tbl => X_Order_Line_Tbl,
908 X_Return_Status => X_Return_Status,
909 X_Msg_Count => X_Msg_Count,
910 X_Msg_Data => X_Msg_Data
911 );
912
913 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
914 -- retrieve_oe_messages;
915 RAISE FND_API.G_EXC_ERROR;
916 END IF;
917
918 --
919 -- End of API body
920 --
921
922 -- Standard check for p_commit
923 IF FND_API.to_Boolean( p_commit )
924 THEN
925 COMMIT WORK;
926 END IF;
927
928
929
930 -- Standard call to get message count and if count is 1, get message info.
931 FND_MSG_PUB.Count_And_Get
932 ( p_count => x_msg_count,
933 p_data => x_msg_data
934 );
935
936 EXCEPTION
937 WHEN FND_API.G_EXC_ERROR THEN
938 null;
939 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
940 P_API_NAME => L_API_NAME
941 ,P_PKG_NAME => G_PKG_NAME
942 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
943 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
944 ,X_MSG_COUNT => X_MSG_COUNT
945 ,X_MSG_DATA => X_MSG_DATA
946 ,X_RETURN_STATUS => X_RETURN_STATUS);
947
948 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
949 null;
950 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
951 P_API_NAME => L_API_NAME
952 ,P_PKG_NAME => G_PKG_NAME
953 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
954 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
955 ,X_MSG_COUNT => X_MSG_COUNT
956 ,X_MSG_DATA => X_MSG_DATA
957 ,X_RETURN_STATUS => X_RETURN_STATUS);
958 WHEN OTHERS THEN
959 null;
960 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
961 P_API_NAME => L_API_NAME
962 ,P_PKG_NAME => G_PKG_NAME
963 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
964 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
965 ,X_MSG_COUNT => X_MSG_COUNT
966 ,X_MSG_DATA => X_MSG_DATA
967 ,X_RETURN_STATUS => X_RETURN_STATUS);
968
969 END Update_Order;
970
971
972 PROCEDURE Delete_order(
973 P_Api_Version_Number IN NUMBER,
974 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
975 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
976 P_Order_Header_id IN NUMBER,
977 X_Return_Status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
978 X_Msg_Count OUT NOCOPY /* file.sql.39 change */ NUMBER,
979 X_Msg_Data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
980 )
981 IS
982
983 l_api_version_number CONSTANT NUMBER := 1.0;
984 l_api_name CONSTANT VARCHAR2(30):= 'DELETE_ORDER';
985 BEGIN
986
987
988 -- Standard Start of API savepoint
989 SAVEPOINT Delete_Order_PVT;
990
991 -- Standard call to check for call compatibility.
992 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
993 p_api_version_number,
994 l_api_name,
995 G_PKG_NAME)
996 THEN
997 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
998 END IF;
999
1000
1001 -- Initialize message list if p_init_msg_list is set to TRUE.
1002 IF FND_API.to_Boolean( p_init_msg_list )
1003 THEN
1004 FND_MSG_PUB.initialize;
1005 END IF;
1006
1007 -- Debug Message
1008 ASO_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: ' || l_api_name || 'start');
1009
1010 -- Initialize API return status to SUCCESS
1011 x_return_status := FND_API.G_RET_STS_SUCCESS;
1012
1013 --
1014 -- API body
1015 --
1016
1017 -- ******************************************************************
1018 -- Validate Environment
1019 -- ******************************************************************
1020 IF FND_GLOBAL.User_Id IS NULL
1021 THEN
1022 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1023 THEN
1024 FND_MESSAGE.Set_Name(' + appShortName +', 'UT_CANNOT_GET_PROFILE_VALUE');
1025 FND_MESSAGE.Set_Token('PROFILE', 'USER_ID', FALSE);
1026 FND_MSG_PUB.ADD;
1027 END IF;
1028 RAISE FND_API.G_EXC_ERROR;
1029 END IF;
1030
1031
1032 OE_ORDER_PUB.Delete_Order
1033 (
1034 p_header_id => p_order_header_id
1035 , x_return_status => x_return_status
1036 , x_msg_count => x_msg_count
1037 , x_msg_data => x_msg_data
1038 );
1039
1040
1041 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1042 retrieve_oe_messages;
1043 RAISE FND_API.G_EXC_ERROR;
1044 END IF;
1045
1046
1047 --
1048 -- End of API body
1049 --
1050
1051 -- Standard check for p_commit
1052 IF FND_API.to_Boolean( p_commit )
1053 THEN
1054 COMMIT WORK;
1055 END IF;
1056
1057
1058
1059 -- Standard call to get message count and if count is 1, get message info.
1060 FND_MSG_PUB.Count_And_Get
1061 ( p_count => x_msg_count,
1062 p_data => x_msg_data
1063 );
1064
1065 EXCEPTION
1066 WHEN FND_API.G_EXC_ERROR THEN
1067 null;
1068 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1069 P_API_NAME => L_API_NAME
1070 ,P_PKG_NAME => G_PKG_NAME
1071 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1072 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
1073 ,X_MSG_COUNT => X_MSG_COUNT
1074 ,X_MSG_DATA => X_MSG_DATA
1075 ,X_RETURN_STATUS => X_RETURN_STATUS);
1076
1077 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1078 null;
1079 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1080 P_API_NAME => L_API_NAME
1081 ,P_PKG_NAME => G_PKG_NAME
1082 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1083 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
1084 ,X_MSG_COUNT => X_MSG_COUNT
1085 ,X_MSG_DATA => X_MSG_DATA
1086 ,X_RETURN_STATUS => X_RETURN_STATUS);
1087 WHEN OTHERS THEN
1088 null;
1089 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1090 P_API_NAME => L_API_NAME
1091 ,P_PKG_NAME => G_PKG_NAME
1092 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1093 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
1094 ,X_MSG_COUNT => X_MSG_COUNT
1095 ,X_MSG_DATA => X_MSG_DATA
1096 ,X_RETURN_STATUS => X_RETURN_STATUS);
1097 END Delete_order;
1098
1099
1100
1101 PROCEDURE BOOK_ORDER(
1102 P_Api_Version_Number IN NUMBER ,
1103 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1104 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
1105 p_order_header_id IN NUMBER ,
1106 X_Return_Status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
1107 X_Msg_Count OUT NOCOPY /* file.sql.39 change */ NUMBER,
1108 X_Msg_Data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1109 )
1110 IS
1111 l_api_version_number NUMBER := 1.0;
1112 l_api_name VARCHAR2(50) := 'BOOK_ORDER';
1113
1114
1115
1116
1117 l_request_tbl OE_Order_PUB.Request_Tbl_Type :=
1118 OE_Order_PUB.G_MISS_REQUEST_TBL;
1119 l_line_shipment_tbl ASO_QUOTE_PUB.Shipment_Tbl_Type;
1120
1121 l_control_rec OE_GLOBALS.Control_Rec_Type;
1122 l_return_status VARCHAR2(1);
1123 l_header_rec OE_Order_PUB.Header_Rec_Type;
1124 l_Header_Adj_tbl OE_Order_PUB.Header_Adj_Tbl_Type;
1125 l_Header_price_Att_tbl OE_Order_PUB.Header_Price_Att_Tbl_Type ;
1126 l_Header_Adj_Att_tbl OE_Order_PUB.Header_Adj_Att_Tbl_Type ;
1127 l_Header_Adj_Assoc_tbl OE_Order_PUB.Header_Adj_Assoc_Tbl_Type ;
1128 l_Header_Scredit_tbl OE_Order_PUB.Header_Scredit_Tbl_Type;
1129 l_line_tbl OE_Order_PUB.Line_Tbl_Type;
1130 l_Line_Adj_tbl OE_Order_PUB.Line_Adj_Tbl_Type;
1131 l_Line_price_Att_tbl OE_Order_PUB.Line_Price_Att_Tbl_Type ;
1132 l_Line_Adj_Att_tbl OE_Order_PUB.Line_Adj_Att_Tbl_Type ;
1133 l_Line_Adj_Assoc_tbl OE_Order_PUB.Line_Adj_Assoc_Tbl_Type ;
1134 l_Line_Scredit_tbl OE_Order_PUB.Line_Scredit_Tbl_Type;
1135 l_Lot_Serial_tbl OE_Order_PUB.Lot_Serial_Tbl_Type;
1136 l_old_header_rec OE_Order_PUB.Header_Rec_Type;
1137 l_old_Header_Adj_tbl OE_Order_PUB.Header_Adj_Tbl_Type;
1138 l_old_Header_price_Att_tbl OE_Order_PUB.Header_Price_Att_Tbl_Type ;
1139 l_old_Header_Adj_Att_tbl OE_Order_PUB.Header_Adj_Att_Tbl_Type ;
1140 l_old_Header_Adj_Assoc_tbl OE_Order_PUB.Header_Adj_Assoc_Tbl_Type ;
1141 l_old_Header_Scredit_tbl OE_Order_PUB.Header_Scredit_Tbl_Type;
1142 l_old_line_tbl OE_Order_PUB.Line_Tbl_Type;
1143 l_old_Line_Adj_tbl OE_Order_PUB.Line_Adj_Tbl_Type;
1144 l_old_Line_price_Att_tbl OE_Order_PUB.Line_Price_Att_Tbl_Type ;
1145 l_old_Line_Adj_Att_tbl OE_Order_PUB.Line_Adj_Att_Tbl_Type ;
1146 l_old_Line_Adj_Assoc_tbl OE_Order_PUB.Line_Adj_Assoc_Tbl_Type ;
1147 l_old_Line_Scredit_tbl OE_Order_PUB.Line_Scredit_Tbl_Type;
1148 l_old_Lot_Serial_tbl OE_Order_PUB.Lot_Serial_Tbl_Type;
1149 l_action_request_tbl OE_Order_PUB.Request_Tbl_Type;
1150
1151
1152 l_return_values varchar2(50);
1153 l_header_val_rec OE_Order_PUB.Header_Val_Rec_Type;
1154 l_old_header_val_rec OE_Order_PUB.Header_Val_Rec_Type;
1155 l_header_adj_val_tbl OE_Order_PUB.Header_Adj_Val_Tbl_Type;
1156 l_old_header_adj_val_tbl OE_Order_PUB.Header_Adj_Val_Tbl_Type;
1157 l_header_scredit_val_tbl OE_Order_PUB.Header_Scredit_Val_Tbl_Type;
1158 l_old_header_scredit_val_tbl OE_Order_PUB.Header_Scredit_Val_Tbl_Type;
1159 l_line_val_tbl OE_Order_PUB.Line_Val_Tbl_Type;
1160 l_old_line_val_tbl OE_Order_PUB.Line_Val_Tbl_Type;
1161 l_line_adj_val_tbl OE_Order_PUB.Line_Adj_Val_Tbl_Type;
1162 l_old_line_adj_val_tbl OE_Order_PUB.Line_Adj_Val_Tbl_Type;
1163 l_line_scredit_val_tbl OE_Order_PUB.Line_Scredit_Val_Tbl_Type;
1164 l_old_line_scredit_val_tbl OE_Order_PUB.Line_Scredit_Val_Tbl_Type;
1165 l_lot_serial_val_tbl OE_Order_PUB.Lot_Serial_Val_Tbl_Type;
1166 l_old_lot_serial_val_tbl OE_Order_PUB.Lot_Serial_Val_Tbl_Type;
1167 l_msg_count number;
1168 l_msg_data varchar2(200);
1169
1170 -- hyang: 2692785
1171 lx_header_rec OE_Order_PUB.Header_Rec_Type;
1172
1173 BEGIN
1174 -- Standard Start of API savepoint
1175 SAVEPOINT Book_Order_PUB;
1176
1177 -- Standard call to check for call compatibility.
1178 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1179 p_api_version_number,
1180 l_api_name,
1181 G_PKG_NAME)
1182 THEN
1183 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1184 END IF;
1185
1186
1187 -- Initialize message list if p_init_msg_list is set to TRUE.
1188 IF FND_API.to_Boolean( p_init_msg_list )
1189 THEN
1190 FND_MSG_PUB.initialize;
1191 END IF;
1192
1193 -- Debug Message
1194 ASO_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: ' || l_api_name || 'start');
1195
1196 -- Initialize API return status to SUCCESS
1197 x_return_status := FND_API.G_RET_STS_SUCCESS;
1198
1199 --
1200 -- API body
1201 --
1202
1203 -- ******************************************************************
1204 -- Validate Environment
1205 -- ******************************************************************
1206 IF FND_GLOBAL.User_Id IS NULL
1207 THEN
1208 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1209 THEN
1210 FND_MESSAGE.Set_Name(' + appShortName +', 'UT_CANNOT_GET_PROFILE_VALUE');
1211 FND_MESSAGE.Set_Token('PROFILE', 'USER_ID', FALSE);
1212 FND_MSG_PUB.ADD;
1213 END IF;
1214 RAISE FND_API.G_EXC_ERROR;
1215 END IF;
1216 /*
1217 OE_ORDER_BOOK_UTIL.BOOK_ORDER
1218 (p_api_version_number => 1.0
1219 ,p_header_id => p_order_header_id
1220 ,x_return_status => x_return_status
1221 ,x_msg_count => x_msg_count
1222 ,x_msg_data => x_msg_data
1223 ) ;
1224
1225 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1226 retrieve_oe_messages;
1227 RAISE FND_API.G_EXC_ERROR;
1228 END IF;
1229
1230 */
1231
1232 Initialize_OM_rec_types(
1233 px_header_rec => l_header_rec,
1234 px_line_tbl => l_line_tbl,
1235 p_line_tbl_count => 0
1236 );
1237
1238
1239 l_request_tbl(1).entity_code := OE_GLOBALS.G_ENTITY_HEADER;
1240 l_request_tbl(1).request_type := OE_GLOBALS.G_BOOK_ORDER;
1241 l_header_rec.header_id := p_order_header_id;
1242 -- l_header_rec.operation := OE_Globals.G_OPR_UPDATE;
1243
1244 -- bug# 1927450
1245 OE_STANDARD_WF.SAVE_MESSAGES_OFF;
1246
1247 OE_Order_GRP.Process_Order
1248 ( p_api_version_number => 1.0
1249 , p_init_msg_list => FND_API.G_TRUE
1250 , p_return_values => l_return_values
1251 , p_commit => FND_API.G_FALSE
1252 , x_return_status => x_return_status
1253 , x_msg_count => x_msg_count
1254 , x_msg_data => x_msg_data
1255 , p_header_rec => l_header_rec
1256 , p_Action_Request_tbl => l_request_tbl
1257 , x_header_rec => lx_header_rec
1258 , x_header_val_rec => l_header_val_rec
1259 , x_Header_Adj_tbl => l_header_adj_tbl
1260 , x_Header_Adj_val_tbl => l_header_adj_val_tbl
1261 , x_Header_price_Att_tbl => l_header_price_att_tbl
1262 , x_Header_Adj_Att_tbl => l_header_adj_att_tbl
1263 , x_Header_Adj_Assoc_tbl => l_header_adj_assoc_tbl
1264 , x_Header_Scredit_tbl => l_header_scredit_tbl
1265 , x_Header_Scredit_val_tbl => l_header_scredit_val_tbl
1266 , x_line_tbl => l_line_tbl
1267 , x_line_val_tbl => l_line_val_tbl
1268 , x_Line_Adj_tbl => l_line_adj_tbl
1269 , x_Line_Adj_val_tbl => l_line_adj_val_tbl
1270 , x_Line_price_Att_tbl => l_line_price_att_tbl
1271 , x_Line_Adj_Att_tbl => l_line_adj_att_tbl
1272 , x_Line_Adj_Assoc_tbl => l_line_adj_assoc_tbl
1273 , x_Line_Scredit_tbl => l_line_scredit_tbl
1274 , x_Line_Scredit_val_tbl => l_line_scredit_val_tbl
1275 , x_Lot_Serial_tbl => l_lot_serial_tbl
1276 , x_Lot_Serial_val_tbl => l_lot_serial_val_tbl
1277 , x_action_request_tbl => l_action_request_tbl
1278 );
1279
1280
1281 -- hyang: 2692785
1282 l_header_rec := lx_header_rec;
1283
1284 -- Check return status from the above procedure call
1285 IF x_return_status = FND_API.G_RET_STS_ERROR then
1286 raise FND_API.G_EXC_ERROR;
1287 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
1288 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1289 END IF;
1290
1291 --
1292 -- End of API body.
1293 --
1294
1295 -- Standard check for p_commit
1296 IF FND_API.to_Boolean( p_commit )
1297 THEN
1298 COMMIT WORK;
1299 END IF;
1300
1301
1302 -- Debug Message
1303 ASO_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Public API: ' || l_api_name || 'end');
1304
1305
1306 -- Standard call to get message count and if count is 1, get message info.
1307 FND_MSG_PUB.Count_And_Get
1308 ( p_count => x_msg_count,
1309 p_data => x_msg_data
1310 );
1311
1312 EXCEPTION
1313 WHEN FND_API.G_EXC_ERROR THEN
1314 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1315 P_API_NAME => L_API_NAME
1316 ,P_PKG_NAME => G_PKG_NAME
1317 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1318 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PUB
1319 ,X_MSG_COUNT => X_MSG_COUNT
1320 ,X_MSG_DATA => X_MSG_DATA
1321 ,X_RETURN_STATUS => X_RETURN_STATUS);
1322
1323 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1324 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1325 P_API_NAME => L_API_NAME
1326 ,P_PKG_NAME => G_PKG_NAME
1327 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1328 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PUB
1329 ,X_MSG_COUNT => X_MSG_COUNT
1330 ,X_MSG_DATA => X_MSG_DATA
1331 ,X_RETURN_STATUS => X_RETURN_STATUS);
1332
1333 WHEN OTHERS THEN
1334 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1335 P_API_NAME => L_API_NAME
1336 ,P_PKG_NAME => G_PKG_NAME
1337 ,P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS
1338 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PUB
1339 ,X_MSG_COUNT => X_MSG_COUNT
1340 ,X_MSG_DATA => X_MSG_DATA
1341 ,X_RETURN_STATUS => X_RETURN_STATUS);
1342
1343 END Book_Order;
1344
1345
1346
1347
1348 PROCEDURE CANCEL_ORDER(
1349 P_Api_Version_Number IN NUMBER ,
1350 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1351 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
1352 p_order_header_id IN NUMBER ,
1353 X_Return_Status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
1354 X_Msg_Count OUT NOCOPY /* file.sql.39 change */ NUMBER,
1355 X_Msg_Data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1356 )
1357 IS
1358 l_api_version_number NUMBER := 1.0;
1359 l_api_name VARCHAR2(50) := 'CANCEL_ORDER';
1360
1361
1362
1363
1364 l_request_tbl OE_Order_PUB.Request_Tbl_Type :=
1365 OE_Order_PUB.G_MISS_REQUEST_TBL;
1366 l_line_shipment_tbl ASO_QUOTE_PUB.Shipment_Tbl_Type;
1367
1368 l_control_rec OE_GLOBALS.Control_Rec_Type;
1369 l_return_status VARCHAR2(1);
1370 l_header_rec OE_Order_PUB.Header_Rec_Type;
1371 l_Header_Adj_tbl OE_Order_PUB.Header_Adj_Tbl_Type;
1372 l_Header_price_Att_tbl OE_Order_PUB.Header_Price_Att_Tbl_Type ;
1373 l_Header_Adj_Att_tbl OE_Order_PUB.Header_Adj_Att_Tbl_Type ;
1374 l_Header_Adj_Assoc_tbl OE_Order_PUB.Header_Adj_Assoc_Tbl_Type ;
1375 l_Header_Scredit_tbl OE_Order_PUB.Header_Scredit_Tbl_Type;
1376 l_line_tbl OE_Order_PUB.Line_Tbl_Type;
1377 l_Line_Adj_tbl OE_Order_PUB.Line_Adj_Tbl_Type;
1378 l_Line_price_Att_tbl OE_Order_PUB.Line_Price_Att_Tbl_Type ;
1379 l_Line_Adj_Att_tbl OE_Order_PUB.Line_Adj_Att_Tbl_Type ;
1380 l_Line_Adj_Assoc_tbl OE_Order_PUB.Line_Adj_Assoc_Tbl_Type ;
1381 l_Line_Scredit_tbl OE_Order_PUB.Line_Scredit_Tbl_Type;
1382 l_Lot_Serial_tbl OE_Order_PUB.Lot_Serial_Tbl_Type;
1383 l_old_header_rec OE_Order_PUB.Header_Rec_Type;
1384 l_old_Header_Adj_tbl OE_Order_PUB.Header_Adj_Tbl_Type;
1385 l_old_Header_price_Att_tbl OE_Order_PUB.Header_Price_Att_Tbl_Type ;
1386 l_old_Header_Adj_Att_tbl OE_Order_PUB.Header_Adj_Att_Tbl_Type ;
1387 l_old_Header_Adj_Assoc_tbl OE_Order_PUB.Header_Adj_Assoc_Tbl_Type ;
1388 l_old_Header_Scredit_tbl OE_Order_PUB.Header_Scredit_Tbl_Type;
1389 l_old_line_tbl OE_Order_PUB.Line_Tbl_Type;
1390 l_old_Line_Adj_tbl OE_Order_PUB.Line_Adj_Tbl_Type;
1391 l_old_Line_price_Att_tbl OE_Order_PUB.Line_Price_Att_Tbl_Type ;
1392 l_old_Line_Adj_Att_tbl OE_Order_PUB.Line_Adj_Att_Tbl_Type ;
1393 l_old_Line_Adj_Assoc_tbl OE_Order_PUB.Line_Adj_Assoc_Tbl_Type ;
1394 l_old_Line_Scredit_tbl OE_Order_PUB.Line_Scredit_Tbl_Type;
1395 l_old_Lot_Serial_tbl OE_Order_PUB.Lot_Serial_Tbl_Type;
1396 l_action_request_tbl OE_Order_PUB.Request_Tbl_Type;
1397
1398
1399 l_return_values varchar2(50);
1400 l_header_val_rec OE_Order_PUB.Header_Val_Rec_Type;
1401 l_old_header_val_rec OE_Order_PUB.Header_Val_Rec_Type;
1402 l_header_adj_val_tbl OE_Order_PUB.Header_Adj_Val_Tbl_Type;
1403 l_old_header_adj_val_tbl OE_Order_PUB.Header_Adj_Val_Tbl_Type;
1404 l_header_scredit_val_tbl OE_Order_PUB.Header_Scredit_Val_Tbl_Type;
1405 l_old_header_scredit_val_tbl OE_Order_PUB.Header_Scredit_Val_Tbl_Type;
1406 l_line_val_tbl OE_Order_PUB.Line_Val_Tbl_Type;
1407 l_old_line_val_tbl OE_Order_PUB.Line_Val_Tbl_Type;
1408 l_line_adj_val_tbl OE_Order_PUB.Line_Adj_Val_Tbl_Type;
1409 l_old_line_adj_val_tbl OE_Order_PUB.Line_Adj_Val_Tbl_Type;
1410 l_line_scredit_val_tbl OE_Order_PUB.Line_Scredit_Val_Tbl_Type;
1411 l_old_line_scredit_val_tbl OE_Order_PUB.Line_Scredit_Val_Tbl_Type;
1412 l_lot_serial_val_tbl OE_Order_PUB.Lot_Serial_Val_Tbl_Type;
1413 l_old_lot_serial_val_tbl OE_Order_PUB.Lot_Serial_Val_Tbl_Type;
1414 l_msg_count number;
1415 l_msg_data varchar2(200);
1416
1417 -- hyang: 2692785
1418 lx_header_rec OE_Order_PUB.Header_Rec_Type;
1419
1420 CURSOR C_cancel_reason IS
1421 SELECT lookup_code
1422 from oe_lookups
1423 WHERE lookup_type = 'CANCEL_CODE'
1424 AND lookup_code = 'Not provided';
1425 BEGIN
1426 -- Standard Start of API savepoint
1427 SAVEPOINT CANCEL_Order_PUB;
1428
1429 -- Standard call to check for call compatibility.
1430 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1431 p_api_version_number,
1432 l_api_name,
1433 G_PKG_NAME)
1434 THEN
1435 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1436 END IF;
1437
1438
1439 -- Initialize message list if p_init_msg_list is set to TRUE.
1440 IF FND_API.to_Boolean( p_init_msg_list )
1441 THEN
1442 FND_MSG_PUB.initialize;
1443 END IF;
1444
1445 -- Debug Message
1446 ASO_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: ' || l_api_name || 'start');
1447
1448 -- Initialize API return status to SUCCESS
1449 x_return_status := FND_API.G_RET_STS_SUCCESS;
1450
1451 --
1452 -- API body
1453 --
1454
1455 -- ******************************************************************
1456 -- Validate Environment
1457 -- ******************************************************************
1458 IF FND_GLOBAL.User_Id IS NULL
1459 THEN
1460 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1461 THEN
1462 FND_MESSAGE.Set_Name(' + appShortName +', 'UT_CANNOT_GET_PROFILE_VALUE');
1463 FND_MESSAGE.Set_Token('PROFILE', 'USER_ID', FALSE);
1464 FND_MSG_PUB.ADD;
1465 END IF;
1466 RAISE FND_API.G_EXC_ERROR;
1467 END IF;
1468
1469
1470 -- Initialize OM record types
1471 Initialize_OM_rec_types(
1472 px_header_rec => l_header_rec,
1473 px_line_tbl => l_line_tbl,
1474 p_line_tbl_count => 0
1475 );
1476
1477
1478 l_header_rec.header_id := p_order_header_id;
1479 l_header_rec.cancelled_flag := 'Y';
1480
1481 OPEN C_cancel_reason;
1482 FETCH C_cancel_reason into l_header_rec.change_reason;
1483 CLOSE C_cancel_reason;
1484
1485 l_header_rec.operation := OE_Globals.G_OPR_UPDATE;
1486
1487 -- bug# 1927450
1488 OE_STANDARD_WF.SAVE_MESSAGES_OFF;
1489
1490 OE_Order_GRP.Process_Order
1491 ( p_api_version_number => 1.0
1492 , p_init_msg_list => FND_API.G_TRUE
1493 , p_return_values => l_return_values
1494 , p_commit => FND_API.G_FALSE
1495 , x_return_status => x_return_status
1496 , x_msg_count => x_msg_count
1497 , x_msg_data => x_msg_data
1498 , p_header_rec => l_header_rec
1499 , x_header_rec => lx_header_rec
1500 , x_header_val_rec => l_header_val_rec
1501 , x_Header_Adj_tbl => l_header_adj_tbl
1502 , x_Header_Adj_val_tbl => l_header_adj_val_tbl
1503 , x_Header_price_Att_tbl => l_header_price_att_tbl
1504 , x_Header_Adj_Att_tbl => l_header_adj_att_tbl
1505 , x_Header_Adj_Assoc_tbl => l_header_adj_assoc_tbl
1506 , x_Header_Scredit_tbl => l_header_scredit_tbl
1507 , x_Header_Scredit_val_tbl => l_header_scredit_val_tbl
1508 , x_line_tbl => l_line_tbl
1509 , x_line_val_tbl => l_line_val_tbl
1510 , x_Line_Adj_tbl => l_line_adj_tbl
1511 , x_Line_Adj_val_tbl => l_line_adj_val_tbl
1512 , x_Line_price_Att_tbl => l_line_price_att_tbl
1513 , x_Line_Adj_Att_tbl => l_line_adj_att_tbl
1514 , x_Line_Adj_Assoc_tbl => l_line_adj_assoc_tbl
1515 , x_Line_Scredit_tbl => l_line_scredit_tbl
1516 , x_Line_Scredit_val_tbl => l_line_scredit_val_tbl
1517 , x_Lot_Serial_tbl => l_lot_serial_tbl
1518 , x_Lot_Serial_val_tbl => l_lot_serial_val_tbl
1519 , x_action_request_tbl => l_action_request_tbl
1520 );
1521
1522
1523 -- hyang: 2692785
1524 l_header_rec := lx_header_rec;
1525
1526 -- Check return status from the above procedure call
1527 IF x_return_status = FND_API.G_RET_STS_ERROR then
1528 raise FND_API.G_EXC_ERROR;
1529 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
1530 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1531 END IF;
1532
1533 --
1534 -- End of API body.
1535 --
1536
1537 -- Standard check for p_commit
1538 IF FND_API.to_Boolean( p_commit )
1539 THEN
1540 COMMIT WORK;
1541 END IF;
1542
1543
1544 -- Debug Message
1545 ASO_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Public API: ' || l_api_name || 'end');
1546
1547
1548 -- Standard call to get message count and if count is 1, get message info.
1549 FND_MSG_PUB.Count_And_Get
1550 ( p_count => x_msg_count,
1551 p_data => x_msg_data
1552 );
1553
1554 EXCEPTION
1555 WHEN FND_API.G_EXC_ERROR THEN
1556 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1557 P_API_NAME => L_API_NAME
1558 ,P_PKG_NAME => G_PKG_NAME
1559 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1560 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PUB
1561 ,X_MSG_COUNT => X_MSG_COUNT
1562 ,X_MSG_DATA => X_MSG_DATA
1563 ,X_RETURN_STATUS => X_RETURN_STATUS);
1564
1565 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1566 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1567 P_API_NAME => L_API_NAME
1568 ,P_PKG_NAME => G_PKG_NAME
1569 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1570 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PUB
1571 ,X_MSG_COUNT => X_MSG_COUNT
1572 ,X_MSG_DATA => X_MSG_DATA
1573 ,X_RETURN_STATUS => X_RETURN_STATUS);
1574
1575 WHEN OTHERS THEN
1576 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1577 P_API_NAME => L_API_NAME
1578 ,P_PKG_NAME => G_PKG_NAME
1579 ,P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS
1580 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PUB
1581 ,X_MSG_COUNT => X_MSG_COUNT
1582 ,X_MSG_DATA => X_MSG_DATA
1583 ,X_RETURN_STATUS => X_RETURN_STATUS);
1584
1585 END Cancel_Order;
1586
1587
1588 --- create order overloaded
1589 -- the following procedure is an overloaded procedure which takes the same
1590 -- parameters as the create order but all record types are defined in
1591 -- ASO_QUOTE_PUB.
1592
1593 PROCEDURE Create_order(
1594 P_Api_Version IN NUMBER,
1595 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1596 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
1597 P_Qte_Rec IN ASO_QUOTE_PUB.Qte_Header_Rec_Type
1598 := ASO_QUOTE_PUB.G_MISS_QTE_HEADER_REC,
1599 P_Header_Payment_Tbl IN ASO_QUOTE_PUB.Payment_Tbl_Type
1600 := ASO_QUOTE_PUB.G_MISS_Payment_TBL,
1601 P_Header_Price_Adj_Tbl IN ASO_QUOTE_PUB.Price_Adj_Tbl_Type
1602 := ASO_QUOTE_PUB.G_MISS_Price_Adj_TBL,
1603 P_Header_Price_Attributes_Tbl IN ASO_QUOTE_PUB.Price_Attributes_Tbl_Type
1604 := ASO_QUOTE_PUB.G_MISS_Price_attributes_TBL,
1605 P_Header_Price_Adj_rltship_Tbl IN ASO_QUOTE_PUB.Price_Adj_Rltship_Tbl_Type
1606 := ASO_QUOTE_PUB.G_MISS_price_adj_rltship_TBL,
1607 P_Header_Price_Adj_Attr_Tbl IN ASO_QUOTE_PUB.Price_Adj_Attr_Tbl_Type
1608 := ASO_QUOTE_PUB.G_Miss_PRICE_ADJ_ATTR_Tbl,
1609 P_Header_Shipment_Tbl IN ASO_QUOTE_PUB.Shipment_Tbl_Type
1610 := ASO_QUOTE_PUB.G_MISS_shipment_TBL,
1611 P_Header_TAX_DETAIL_Tbl IN ASO_QUOTE_PUB.TAX_DETAIL_Tbl_Type
1612 := ASO_QUOTE_PUB.G_MISS_TAX_DETAIL_TBL,
1613 P_Header_FREIGHT_CHARGE_Tbl IN ASO_QUOTE_PUB.FREIGHT_CHARGE_Tbl_Type
1614 := ASO_QUOTE_PUB.G_MISS_FREIGHT_CHARGE_Tbl,
1615 P_Header_ATTRIBS_EXT_Tbl IN ASO_QUOTE_PUB.Line_Attribs_Ext_Tbl_Type
1616 := ASO_QUOTE_PUB.G_MISS_Line_Attribs_Ext_TBL,
1617 P_Header_Quote_Party_Tbl IN ASO_QUOTE_PUB.Quote_Party_Tbl_Type
1618 := ASO_QUOTE_PUB.G_MISS_Quote_Party_Tbl,
1619 P_header_sales_credit_TBL IN ASO_QUOTE_PUB.Sales_credit_tbl_type
1620 := ASO_QUOTE_PUB.G_MISS_sales_credit_TBL,
1621 P_Qte_Line_Tbl IN ASO_QUOTE_PUB.Qte_Line_Tbl_Type
1622 := ASO_QUOTE_PUB.G_MISS_qte_line_TBL,
1623 P_Qte_Line_Dtl_Tbl IN ASO_QUOTE_PUB.Qte_Line_Dtl_Tbl_Type
1624 := ASO_QUOTE_PUB.G_MISS_qte_line_dtl_TBL,
1625 P_Line_Payment_Tbl IN ASO_QUOTE_PUB.Payment_Tbl_Type
1626 := ASO_QUOTE_PUB.G_MISS_Payment_TBL,
1627 P_Line_Price_Adj_Tbl IN ASO_QUOTE_PUB.Price_Adj_Tbl_Type
1628 := ASO_QUOTE_PUB.G_MISS_Price_Adj_TBL,
1629 P_Line_Price_Attributes_Tbl IN ASO_QUOTE_PUB.Price_Attributes_Tbl_Type
1630 := ASO_QUOTE_PUB.G_MISS_Price_attributes_TBL,
1631 P_Line_Price_Adj_rltship_Tbl IN ASO_QUOTE_PUB.Price_Adj_Rltship_Tbl_Type
1632 := ASO_QUOTE_PUB.G_MISS_price_adj_rltship_TBL,
1633 P_Line_Price_Adj_Attr_Tbl IN ASO_QUOTE_PUB.Price_Adj_Attr_Tbl_Type
1634 := ASO_QUOTE_PUB.G_Miss_PRICE_ADJ_ATTR_Tbl,
1635 P_Line_Shipment_Tbl IN ASO_QUOTE_PUB.Shipment_Tbl_Type
1636 := ASO_QUOTE_PUB.G_MISS_shipment_TBL,
1637 P_Line_TAX_DETAIL_Tbl IN ASO_QUOTE_PUB.TAX_DETAIL_Tbl_Type
1638 := ASO_QUOTE_PUB.G_MISS_TAX_DETAIL_TBL,
1639 P_Line_FREIGHT_CHARGE_Tbl IN ASO_QUOTE_PUB.FREIGHT_CHARGE_Tbl_Type
1640 := ASO_QUOTE_PUB.G_MISS_FREIGHT_CHARGE_Tbl,
1641 P_Line_ATTRIBS_EXT_Tbl IN ASO_QUOTE_PUB.Line_Attribs_Ext_Tbl_Type
1642 := ASO_QUOTE_PUB.G_MISS_Line_Attribs_Ext_TBL,
1643 P_Line_Rltship_Tbl IN ASO_QUOTE_PUB.Line_Rltship_Tbl_Type
1644 := ASO_QUOTE_PUB.G_MISS_line_rltship_TBL,
1645 P_Line_sales_credit_TBL IN ASO_QUOTE_PUB.Sales_credit_tbl_type
1646 := ASO_QUOTE_PUB.G_MISS_sales_credit_TBL,
1647 P_Line_Quote_Party_Tbl IN ASO_QUOTE_PUB.Quote_Party_Tbl_Type
1648 := ASO_QUOTE_PUB.G_MISS_Quote_Party_Tbl,
1649 P_Lot_Serial_Tbl IN ASO_QUOTE_PUB.Lot_Serial_Tbl_Type
1650 := ASO_QUOTE_PUB.G_MISS_Lot_Serial_Tbl,
1651 P_Control_Rec IN Control_Rec_Type := G_MISS_Control_Rec,
1652 X_Order_Header_Rec OUT NOCOPY /* file.sql.39 change */ Order_Header_Rec_Type,
1653 X_Order_Line_Tbl OUT NOCOPY /* file.sql.39 change */ Order_Line_Tbl_type,
1654 X_Return_Status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
1655 X_Msg_Count OUT NOCOPY /* file.sql.39 change */ NUMBER,
1656 X_Msg_Data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1657 )
1658 IS
1659 l_api_version_number CONSTANT NUMBER := 1.0;
1660 l_api_name CONSTANT VARCHAR2(30):= 'Create_Order';
1661 l_control_rec OE_GLOBALS.Control_Rec_Type;
1662 l_return_status VARCHAR2(1);
1663
1664 l_line_shipment_tbl ASO_QUOTE_PUB.Shipment_Tbl_Type;
1665
1666 -- header record types
1667 l_header_rec OE_Order_PUB.Header_Rec_Type;
1668 l_Header_Adj_tbl OE_Order_PUB.Header_Adj_Tbl_Type;
1669 l_Header_price_Att_tbl OE_Order_PUB.Header_Price_Att_Tbl_Type ;
1670 l_Header_Adj_Att_tbl OE_Order_PUB.Header_Adj_Att_Tbl_Type ;
1671 l_Header_Adj_Assoc_tbl OE_Order_PUB.Header_Adj_Assoc_Tbl_Type ;
1672 l_Header_Scredit_tbl OE_Order_PUB.Header_Scredit_Tbl_Type;
1673 l_Header_Payment_tbl OE_Order_PUB.Header_Payment_Tbl_Type;
1674 l_Header_Payment_val_tbl OE_Order_PUB.Header_Payment_Val_Tbl_Type;
1675 -- line record types
1676 l_line_tbl OE_Order_PUB.Line_Tbl_Type;
1677 l_Line_Adj_tbl OE_Order_PUB.Line_Adj_Tbl_Type;
1678 l_Line_price_Att_tbl OE_Order_PUB.Line_Price_Att_Tbl_Type ;
1679 l_Line_Adj_Att_tbl OE_Order_PUB.Line_Adj_Att_Tbl_Type ;
1680 l_Line_Adj_Assoc_tbl OE_Order_PUB.Line_Adj_Assoc_Tbl_Type ;
1681 l_Line_Scredit_tbl OE_Order_PUB.Line_Scredit_Tbl_Type;
1682 l_Line_Payment_tbl OE_Order_PUB.Line_Payment_Tbl_Type;
1683 l_Line_Payment_val_tbl OE_Order_PUB.Line_Payment_Val_Tbl_Type;
1684
1685 l_Lot_Serial_tbl OE_Order_PUB.Lot_Serial_Tbl_Type;
1686 l_old_header_rec OE_Order_PUB.Header_Rec_Type;
1687 l_old_Header_Adj_tbl OE_Order_PUB.Header_Adj_Tbl_Type;
1688 l_old_Header_price_Att_tbl OE_Order_PUB.Header_Price_Att_Tbl_Type ;
1689 l_old_Header_Adj_Att_tbl OE_Order_PUB.Header_Adj_Att_Tbl_Type ;
1690 l_old_Header_Adj_Assoc_tbl OE_Order_PUB.Header_Adj_Assoc_Tbl_Type ;
1691 l_old_Header_Scredit_tbl OE_Order_PUB.Header_Scredit_Tbl_Type;
1692 l_old_line_tbl OE_Order_PUB.Line_Tbl_Type;
1693 l_old_Line_Adj_tbl OE_Order_PUB.Line_Adj_Tbl_Type;
1694 l_old_Line_price_Att_tbl OE_Order_PUB.Line_Price_Att_Tbl_Type ;
1695 l_old_Line_Adj_Att_tbl OE_Order_PUB.Line_Adj_Att_Tbl_Type ;
1696 l_old_Line_Adj_Assoc_tbl OE_Order_PUB.Line_Adj_Assoc_Tbl_Type ;
1697 l_old_Line_Scredit_tbl OE_Order_PUB.Line_Scredit_Tbl_Type;
1698 l_old_Lot_Serial_tbl OE_Order_PUB.Lot_Serial_Tbl_Type;
1699 l_action_request_tbl OE_Order_PUB.Request_Tbl_Type;
1700
1701 l_return_values varchar2(50);
1702 l_header_val_rec OE_Order_PUB.Header_Val_Rec_Type;
1703 l_old_header_val_rec OE_Order_PUB.Header_Val_Rec_Type;
1704 l_header_adj_val_tbl OE_Order_PUB.Header_Adj_Val_Tbl_Type;
1705 l_old_header_adj_val_tbl OE_Order_PUB.Header_Adj_Val_Tbl_Type;
1706 l_header_scredit_val_tbl OE_Order_PUB.Header_Scredit_Val_Tbl_Type;
1707 l_old_header_scredit_val_tbl OE_Order_PUB.Header_Scredit_Val_Tbl_Type;
1708 l_line_val_tbl OE_Order_PUB.Line_Val_Tbl_Type;
1709 l_old_line_val_tbl OE_Order_PUB.Line_Val_Tbl_Type;
1710 l_line_adj_val_tbl OE_Order_PUB.Line_Adj_Val_Tbl_Type;
1711 l_old_line_adj_val_tbl OE_Order_PUB.Line_Adj_Val_Tbl_Type;
1712 l_line_scredit_val_tbl OE_Order_PUB.Line_Scredit_Val_Tbl_Type;
1713 l_old_line_scredit_val_tbl OE_Order_PUB.Line_Scredit_Val_Tbl_Type;
1714 l_lot_serial_val_tbl OE_Order_PUB.Lot_Serial_Val_Tbl_Type;
1715 l_old_lot_serial_val_tbl OE_Order_PUB.Lot_Serial_Val_Tbl_Type;
1716 l_request_tbl OE_Order_PUB.Request_Tbl_Type :=
1717 OE_Order_PUB.G_MISS_REQUEST_TBL;
1718
1719 -- hyang: bug 2692785
1720 lx_header_rec OE_Order_PUB.Header_Rec_Type;
1721 lx_Header_Adj_tbl OE_Order_PUB.Header_Adj_Tbl_Type;
1722 lx_Header_price_Att_tbl OE_Order_PUB.Header_Price_Att_Tbl_Type ;
1723 lx_Header_Adj_Att_tbl OE_Order_PUB.Header_Adj_Att_Tbl_Type ;
1724 lx_Header_Adj_Assoc_tbl OE_Order_PUB.Header_Adj_Assoc_Tbl_Type ;
1725 lx_Header_Scredit_tbl OE_Order_PUB.Header_Scredit_Tbl_Type;
1726 lx_Header_Payment_tbl OE_Order_PUB.Header_Payment_Tbl_Type;
1727 lx_line_tbl OE_Order_PUB.Line_Tbl_Type;
1728 lx_Line_Adj_tbl OE_Order_PUB.Line_Adj_Tbl_Type;
1729 lx_Line_price_Att_tbl OE_Order_PUB.Line_Price_Att_Tbl_Type ;
1730 lx_Line_Adj_Att_tbl OE_Order_PUB.Line_Adj_Att_Tbl_Type ;
1731 lx_Line_Adj_Assoc_tbl OE_Order_PUB.Line_Adj_Assoc_Tbl_Type ;
1732 lx_Line_Scredit_tbl OE_Order_PUB.Line_Scredit_Tbl_Type;
1733 lx_Lot_Serial_tbl OE_Order_PUB.Lot_Serial_Tbl_Type;
1734 lx_Line_Payment_tbl OE_Order_PUB.Line_Payment_Tbl_Type;
1735
1736 -- needed for fulfillment
1737 fulfil_index NUMBER := 1;
1738 l_electronic_flag varchar2(1);
1739
1740 fulfillment FULFILLMENT_TBL_TYPE;
1741 p_ffm_request_rec ASO_FFM_INT.FFM_REQUEST_REC_TYPE;
1742 l_ffm_content_tbl ASO_FFM_INT.FFM_CONTENT_TBL_TYPE;
1743 l_ffm_bind_tbl ASO_FFM_INT.FFM_Bind_Tbl_Type;
1744 X_Request_ID NUMBER;
1745
1746 l_split_pay_prof VARCHAR2(2) := FND_PROFILE.Value('ASO_ENABLE_SPLIT_PAYMENT');
1747 l_CC_Auth_Prof VARCHAR2(2) := FND_PROFILE.Value('ASO_CC_AUTHORIZATION_ENABLED');
1748 l_Enable_Risk_Mgmt_Prof VARCHAR2(2);
1749 l_CC_Auth_Failure_Prof VARCHAR2(20);
1750 l_Risk_Mgmt_Failure_Prof VARCHAR2(20);
1751
1752 i NUMBER := 1;
1753
1754
1755 BEGIN
1756
1757
1758 -- Standard Start of API savepoint
1759 SAVEPOINT CREATE_ORDER_PVT;
1760
1761 aso_debug_pub.g_debug_flag := nvl(fnd_profile.value('ASO_ENABLE_DEBUG'),'N');
1762
1763 -- Standard call to check for call compatibility.
1764 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1765 p_api_version,
1766 l_api_name,
1767 G_PKG_NAME)
1768 THEN
1769 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1770 END IF;
1771
1772
1773 -- Initialize message list if p_init_msg_list is set to TRUE.
1774 IF FND_API.to_Boolean( p_init_msg_list )
1775 THEN
1776 FND_MSG_PUB.initialize;
1777 END IF;
1778
1779 -- Debug Message
1780 ASO_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: ' || l_api_name || 'start');
1781
1782 -- Initialize API return status to SUCCESS
1783 x_return_status := FND_API.G_RET_STS_SUCCESS;
1784
1785 --
1786 -- API body
1787 --
1788
1789 -- ******************************************************************
1790 -- Validate Environment
1791 -- ******************************************************************
1792 IF FND_GLOBAL.User_Id IS NULL
1793 THEN
1794 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1795 THEN
1796 FND_MESSAGE.Set_Name(' + appShortName +', 'UT_CANNOT_GET_PROFILE_VALUE');
1797 FND_MESSAGE.Set_Token('PROFILE', 'USER_ID', FALSE);
1798 FND_MSG_PUB.ADD;
1799 END IF;
1800 RAISE FND_API.G_EXC_ERROR;
1801 END IF;
1802 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1803 aso_debug_pub.add('Create_Order: Begin ', 1, 'N');
1804 END IF;
1805 -- change the org to whatever is stored in the org id column
1806 IF p_qte_rec.org_id is not NULL
1807 AND p_qte_rec.org_id <> FND_API.G_MISS_NUM THEN
1808
1809 /* fnd_client_info.set_org_context(p_qte_rec.org_id); */ --Commented Code Yogeshwar (MOAC)
1810 MO_GLOBAL.set_policy_context('S', p_qte_rec.org_id) ; --New Code Yogeshwar (MOAC)
1811
1812 END IF;
1813
1814 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1815 aso_debug_pub.add('l_split_pay_prof: '||l_split_pay_prof,1,'N');
1816 END IF;
1817
1818 IF (l_split_pay_prof = 'N') THEN
1819 IF p_header_payment_tbl.count > 1 THEN
1820 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1821 FND_MESSAGE.Set_Name('ASO', 'ASO_API_SPLIT_PAYMENT');
1822 FND_MSG_PUB.ADD;
1823 END IF;
1824 RAISE FND_API.G_EXC_ERROR;
1825 ELSIF p_header_payment_tbl.count = 1 THEN
1826 IF p_header_payment_tbl(1).payment_option = 'SPLIT' THEN
1827 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1828 FND_MESSAGE.Set_Name('ASO', 'ASO_API_SPLIT_PAYMENT');
1829 FND_MSG_PUB.ADD;
1830 END IF;
1831 RAISE FND_API.G_EXC_ERROR;
1832 END IF;
1833
1834 END IF;-- p_hd_payment
1835
1836 END IF;-- FND_PROFILE.Value
1837 -- Validate_Payment;(-- there should be only one rec if payment_option<>'SPLIT')
1838 IF (l_split_pay_prof = 'Y') THEN
1839 IF p_header_payment_tbl.count > 1 THEN
1840 FOR i IN 1..p_header_payment_tbl.count LOOP
1841 IF p_header_payment_tbl(i).payment_option <> 'SPLIT' THEN
1842 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1843 FND_MESSAGE.Set_Name('ASO', 'ASO_API_TOO_MANY_PAYMENTS');
1844 FND_MSG_PUB.ADD;
1845 END IF;
1846 RAISE FND_API.G_EXC_ERROR;
1847 END IF;
1848 END LOOP;
1849 END IF;
1850 END IF;
1851
1852 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1853 aso_debug_pub.add('Create_Order: Before MAP_QUOTE_ORDER_INT.Map_Quote_to_Order : ', 1, 'N');
1854 END IF;
1855 ASO_MAP_QUOTE_ORDER_INT.Map_Quote_to_order(
1856 p_operation => 'CREATE' ,
1857 P_Qte_Rec => P_Qte_Rec ,
1858 P_Header_Payment_Tbl => P_Header_Payment_Tbl ,
1859 P_Header_Price_Adj_Tbl => P_Header_Price_Adj_Tbl ,
1860 P_Header_Price_Attributes_Tbl => P_Header_Price_Attributes_Tbl ,
1861 P_Header_Price_Adj_rltship_Tbl=> P_Header_Price_Adj_rltship_Tbl ,
1862 P_Header_Price_Adj_Attr_Tbl => P_Header_Price_Adj_Attr_Tbl ,
1863 P_Header_Shipment_Tbl => P_Header_Shipment_Tbl ,
1864 P_Header_TAX_DETAIL_Tbl => P_Header_TAX_DETAIL_Tbl ,
1865 P_Header_FREIGHT_CHARGE_Tbl => P_Header_FREIGHT_CHARGE_Tbl ,
1866 P_header_sales_credit_TBL => P_header_sales_credit_TBL ,
1867 P_Qte_Line_Tbl => P_Qte_Line_Tbl ,
1868 P_Qte_Line_Dtl_Tbl => P_Qte_Line_Dtl_Tbl ,
1869 P_Line_Payment_Tbl => P_Line_Payment_Tbl ,
1870 P_Line_Price_Adj_Tbl => P_Line_Price_Adj_Tbl ,
1871 P_Line_Price_Attributes_Tbl => P_Line_Price_Attributes_Tbl ,
1872 P_Line_Price_Adj_rltship_Tbl => P_Line_Price_Adj_rltship_Tbl ,
1873 P_Line_Price_Adj_Attr_Tbl => P_Line_Price_Adj_Attr_Tbl ,
1874 P_Line_Shipment_Tbl => P_Line_Shipment_Tbl ,
1875 P_Line_TAX_DETAIL_Tbl => P_Line_TAX_DETAIL_Tbl ,
1876 P_Line_FREIGHT_CHARGE_Tbl => P_Line_FREIGHT_CHARGE_Tbl ,
1877 P_Line_Rltship_Tbl => P_Line_Rltship_Tbl ,
1878 P_Line_sales_credit_TBL => P_Line_sales_credit_TBL ,
1879 P_Lot_serial_TBL => P_Lot_serial_TBL ,
1880 P_Calculate_Price_Flag => P_Control_Rec.Calculate_Price,
1881 x_header_rec => l_header_rec ,
1882 x_header_val_rec => l_header_val_rec ,
1883 x_header_Adj_tbl => l_header_Adj_tbl ,
1884 x_header_Adj_val_tbl => l_header_Adj_val_tbl ,
1885 x_header_price_Att_tbl => l_header_price_Att_tbl ,
1886 x_header_Adj_Att_tbl => l_header_Adj_Att_tbl ,
1887 x_header_Adj_Assoc_tbl => l_header_Adj_Assoc_tbl ,
1888 x_header_Scredit_tbl => l_header_Scredit_tbl ,
1889 x_Header_Scredit_val_tbl => l_Header_Scredit_val_tbl ,
1890 x_Header_Payment_tbl => l_Header_Payment_tbl ,
1891 x_line_tbl => l_line_tbl ,
1892 x_line_val_tbl => l_line_val_tbl ,
1893 x_line_Adj_tbl => l_line_Adj_tbl ,
1894 x_line_Adj_val_tbl => l_line_Adj_val_tbl ,
1895 x_line_price_Att_tbl => l_line_price_Att_tbl ,
1896 x_line_Adj_Att_tbl => l_line_Adj_Att_tbl ,
1897 x_line_Adj_Assoc_tbl => l_line_Adj_Assoc_tbl ,
1898 x_line_Scredit_tbl => l_line_Scredit_tbl ,
1899 x_line_Scredit_val_tbl => l_line_Scredit_val_tbl ,
1900 x_lot_Serial_tbl => l_lot_Serial_tbl ,
1901 X_Lot_Serial_val_tbl => l_Lot_Serial_val_tbl ,
1902 x_Line_Payment_tbl => l_Line_Payment_tbl
1903 );
1904
1905 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1906 aso_debug_pub.add('Create_Order: After Map_Quote_to_Order: ', 1, 'N');
1907 END IF;
1908
1909 -- set the control record flags
1910 -- book order
1911
1912 IF p_control_rec.book_flag = FND_API.G_TRUE THEN
1913 l_request_tbl(i).entity_code := OE_GLOBALS.G_ENTITY_HEADER;
1914 l_request_tbl(i).request_type := OE_GLOBALS.G_BOOK_ORDER;
1915 i := i + 1;
1916 END IF; -- booking
1917
1918 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1919 aso_debug_pub.add('Create_Order: l_CC_Auth_Prof: '||l_CC_Auth_Prof, 1, 'N');
1920 END IF;
1921
1922 IF l_CC_Auth_Prof = 'Y' AND P_Control_Rec.CC_By_Fax <> FND_API.G_TRUE THEN
1923
1924 FOR x IN 1..l_Header_Payment_tbl.count LOOP
1925 IF l_Header_Payment_tbl(x).Payment_Type_Code = 'CREDIT_CARD' AND l_Header_Payment_tbl(x).trxn_extension_id IS NOT NULL THEN
1926 --l_cc_auth_failure_prof := NVL(FND_PROFILE.Value('ASO_CC_AUTH_FAILURE'), 'REJECT');
1927 l_enable_risk_mgmt_prof := NVL(FND_PROFILE.Value('ASO_RISK_MANAGE_CC_AUTH'), 'Y');
1928
1929 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1930 aso_debug_pub.add('Create_Order: l_enable_risk_mgmt_prof: '||l_enable_risk_mgmt_prof, 1, 'N');
1931 END IF;
1932
1933 l_request_tbl(i).request_type := OE_GLOBALS.G_VERIFY_PAYMENT;
1934 l_request_tbl(i).entity_code := OE_GLOBALS.G_ENTITY_HEADER;
1935 --l_request_tbl(i).param2 := l_cc_auth_failure_prof;
1936
1937 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1938 aso_debug_pub.add('Create_Order: l_request_tbl(i).param2: '|| l_request_tbl(i).param2, 1, 'N');
1939 END IF;
1940
1941 IF l_enable_risk_mgmt_prof = 'Y' THEN
1942 --l_risk_mgmt_failure_prof := NVL(FND_PROFILE.Value('ASO_RISK_MANAGE_FAILURE'), 'REJECT');
1943
1944 l_request_tbl(i).param1 := 'Y';
1945 --l_request_tbl(i).param3 := l_risk_mgmt_failure_prof;
1946 ELSE
1947 l_request_tbl(i).param1 := 'N';
1948 END IF;
1949 i := i + 1;
1950 END IF; -- l_Header_Payment_tbl
1951 END LOOP; -- l_Header_Payment_tbl
1952
1953 FOR x IN 1..l_Line_Payment_tbl.count LOOP
1954 IF l_Line_Payment_tbl(x).Payment_Type_Code = 'CREDIT_CARD' AND l_Line_Payment_tbl(x).trxn_extension_id IS NOT NULL THEN
1955 --l_cc_auth_failure_prof := NVL(FND_PROFILE.Value('ASO_CC_AUTH_FAILURE'), 'REJECT');
1956 l_enable_risk_mgmt_prof := NVL(FND_PROFILE.Value('ASO_RISK_MANAGE_CC_AUTH'), 'Y');
1957
1958 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1959 aso_debug_pub.add('Create_Order: l_enable_risk_mgmt_prof: '||l_enable_risk_mgmt_prof, 1, 'N');
1960 END IF;
1961
1962 l_request_tbl(i).request_type := OE_GLOBALS.G_VERIFY_PAYMENT;
1963 l_request_tbl(i).entity_code := OE_GLOBALS.G_ENTITY_LINE;
1964 l_request_tbl(i).entity_index := l_Line_Payment_tbl(x).Line_Index;
1965 --l_request_tbl(i).param2 := l_cc_auth_failure_prof;
1966
1967 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1968 aso_debug_pub.add('Create_Order: l_request_tbl(i).param2: '||l_request_tbl(i).param2, 1, 'N');
1969 END IF;
1970
1971 IF l_enable_risk_mgmt_prof = 'Y' THEN
1972 --l_risk_mgmt_failure_prof := NVL(FND_PROFILE.Value('ASO_RISK_MANAGE_FAILURE'), 'REJECT');
1973
1974 l_request_tbl(i).param1 := 'Y';
1975 --l_request_tbl(i).param3 := l_risk_mgmt_failure_prof;
1976 ELSE
1977 l_request_tbl(i).param1 := 'N';
1978 END IF;
1979 i := i + 1;
1980 END IF; -- l_Line_Payment_tbl
1981 END LOOP; -- l_Line_Payment_tbl
1982
1983 END IF; -- l_CC_Auth_Prof
1984
1985 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1986 aso_debug_pub.add('Create_Order: Before Proc_Order: ', 1, 'N');
1987 aso_utility_pvt.print_login_info();
1988 END IF;
1989
1990 -- added new debug messages
1991 IF l_line_tbl.count > 0 then
1992 for i in 1..l_line_tbl.count loop
1993 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1994 aso_debug_pub.add('Create_Order: l_line_tbl('|| i ||').operation: ' || l_line_tbl(i).operation, 1, 'N');
1995 aso_debug_pub.add('Create_Order: l_line_tbl('|| i ||').service_duration: ' || l_line_tbl(i).service_duration, 1, 'N');
1996 aso_debug_pub.add('Create_Order: l_line_tbl('|| i ||').service_period: ' || l_line_tbl(i).service_period, 1, 'N');
1997 aso_debug_pub.add('Create_Order: l_line_tbl('|| i ||').service_start_date: ' || l_line_tbl(i).service_start_date, 1, 'N');
1998 aso_debug_pub.add('Create_Order: l_line_tbl('|| i ||').service_end_date: ' || l_line_tbl(i).service_end_date, 1, 'N');
1999 aso_debug_pub.add('Create_Order: l_line_tbl('|| i ||').inventory_item_id: ' || l_line_tbl(i).inventory_item_id, 1, 'N');
2000 aso_debug_pub.add('Create_Order: l_line_tbl('|| i ||').ordered_quantity: ' || l_line_tbl(i).ordered_quantity, 1, 'N');
2001 aso_debug_pub.add('Create_Order: l_line_tbl('|| i ||').order_quantity_uom: ' || l_line_tbl(i).order_quantity_uom, 1, 'N');
2002 END IF;
2003 end loop;
2004 else
2005 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2006 aso_debug_pub.add('Create_Order: l_line_tbl count is 0 ', 1, 'N');
2007 END IF;
2008 end if;
2009
2010 -- bug# 1927450
2011 OE_STANDARD_WF.SAVE_MESSAGES_OFF;
2012
2013 OE_Order_GRP.Process_Order
2014 ( p_api_version_number => 1.0
2015 , p_init_msg_list => FND_API.G_TRUE
2016 , p_return_values => l_return_values
2017 , p_commit => FND_API.G_FALSE
2018 , x_return_status => x_return_status
2019 , x_msg_count => x_msg_count
2020 , x_msg_data => x_msg_data
2021 , p_header_rec => l_header_rec
2022 , p_Header_Adj_tbl => l_header_adj_tbl
2023 , p_Header_price_Att_tbl => l_header_price_att_tbl
2024 , p_Header_Adj_Att_tbl => l_header_adj_att_tbl
2025 , p_Header_Adj_Assoc_tbl => l_header_adj_assoc_tbl
2026 , p_Header_Scredit_tbl => l_header_scredit_tbl
2027 , p_Header_Payment_tbl => l_header_payment_tbl
2028 , p_line_tbl => l_line_tbl
2029 , p_Line_Adj_tbl => l_line_adj_tbl
2030 , p_Line_price_Att_tbl => l_line_price_att_tbl
2031 , p_Line_Adj_Att_tbl => l_Line_Adj_Att_tbl
2032 , p_Line_Adj_Assoc_tbl => l_line_adj_assoc_tbl
2033 , p_Line_Scredit_tbl => l_line_scredit_tbl
2034 , p_Lot_Serial_tbl => l_lot_serial_tbl
2035 , p_Line_Payment_tbl => l_line_payment_tbl
2036 , p_Action_Request_tbl => l_request_tbl
2037 , x_header_rec => lx_header_rec
2038 , x_header_val_rec => l_header_val_rec
2039 , x_Header_Adj_tbl => lx_header_adj_tbl
2040 , x_Header_Adj_val_tbl => l_header_adj_val_tbl
2041 , x_Header_price_Att_tbl => lx_header_price_att_tbl
2042 , x_Header_Adj_Att_tbl => lx_header_adj_att_tbl
2043 , x_Header_Adj_Assoc_tbl => lx_header_adj_assoc_tbl
2044 , x_Header_Scredit_tbl => lx_header_scredit_tbl
2045 , x_Header_Scredit_val_tbl => l_header_scredit_val_tbl
2046 , x_Header_Payment_tbl => lx_header_payment_tbl
2047 , x_Header_Payment_val_tbl => l_header_payment_val_tbl
2048 , x_line_tbl => lx_line_tbl
2049 , x_line_val_tbl => l_line_val_tbl
2050 , x_Line_Adj_tbl => lx_line_adj_tbl
2051 , x_Line_Adj_val_tbl => l_line_adj_val_tbl
2052 , x_Line_price_Att_tbl => lx_line_price_att_tbl
2053 , x_Line_Adj_Att_tbl => lx_line_adj_att_tbl
2054 , x_Line_Adj_Assoc_tbl => lx_line_adj_assoc_tbl
2055 , x_Line_Scredit_tbl => lx_line_scredit_tbl
2056 , x_Line_Scredit_val_tbl => l_line_scredit_val_tbl
2057 , x_Lot_Serial_tbl => lx_lot_serial_tbl
2058 , x_Lot_Serial_val_tbl => l_lot_serial_val_tbl
2059 , x_Line_Payment_tbl => lx_line_payment_tbl
2060 , x_Line_Payment_val_tbl => l_line_payment_val_tbl
2061 , x_action_request_tbl => l_action_request_tbl
2062 );
2063
2064 -- hyang: bug 2692785
2065 l_header_rec := lx_header_rec;
2066 l_Header_Adj_tbl := lx_Header_Adj_tbl;
2067 l_Header_price_Att_tbl := lx_Header_price_Att_tbl;
2068 l_Header_Adj_Att_tbl := lx_Header_Adj_Att_tbl;
2069 l_Header_Adj_Assoc_tbl := lx_Header_Adj_Assoc_tbl;
2070 l_Header_Scredit_tbl := lx_Header_Scredit_tbl;
2071 l_line_tbl := lx_line_tbl;
2072 l_Line_Adj_tbl := lx_Line_Adj_tbl;
2073 l_Line_price_Att_tbl := lx_Line_price_Att_tbl;
2074 l_Line_Adj_Att_tbl := lx_Line_Adj_Att_tbl;
2075 l_Line_Adj_Assoc_tbl := lx_Line_Adj_Assoc_tbl;
2076 l_Line_Scredit_tbl := lx_Line_Scredit_tbl;
2077 l_Lot_Serial_tbl := lx_Lot_Serial_tbl;
2078
2079
2080 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2081 aso_debug_pub.add('Create_Order: After Proc_Order: x_return_status: '||x_return_status, 1, 'N');
2082 aso_utility_pvt.print_login_info();
2083 END IF;
2084
2085 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2086 -- retrieve_oe_messages;
2087 IF x_msg_count > 0 THEN
2088 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2089 FND_MESSAGE.Set_Name('ASO', 'ASO_OM_ERROR_MSG');
2090 FND_MSG_PUB.ADD;
2091 END IF;
2092 END IF;
2093
2094 retrieve_oe_messages;
2095
2096 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2097 raise FND_API.G_EXC_ERROR;
2098 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2099 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2100 END IF;
2101
2102 END IF;
2103
2104
2105 IF (x_return_status = FND_API.G_RET_STS_SUCCESS AND
2106 p_control_rec.book_flag = FND_API.G_TRUE AND
2107 l_action_request_tbl(1).return_status <> FND_API.G_RET_STS_SUCCESS)
2108 THEN
2109 if x_msg_count > 0 then
2110 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2111 FND_MESSAGE.Set_Name('ASO', 'ASO_OM_ERROR_MSG');
2112 FND_MSG_PUB.ADD;
2113 END IF;
2114 end if;
2115 retrieve_oe_messages;
2116 x_return_status := FND_API.G_RET_STS_ERROR;
2117 RAISE FND_API.G_EXC_ERROR;
2118 END IF;
2119
2120 retrieve_oe_messages;
2121
2122 -- return the header values
2123 x_order_header_rec.order_number := l_header_rec.order_number;
2124 x_order_header_rec.order_header_id := l_header_rec.header_id;
2125 x_order_header_rec.status := l_header_rec.return_status;
2126 x_order_header_rec.quote_header_id := l_header_rec.source_document_id;
2127
2128 -- return line values
2129 FOR i in 1..l_line_tbl.count LOOP
2130 x_order_line_tbl(i).order_line_id := l_line_tbl(i).line_id;
2131 x_order_line_tbl(i).order_header_id := l_line_tbl(i).header_id;
2132 x_order_line_tbl(i).quote_shipment_line_id := l_line_tbl(i).source_document_line_id;
2133 x_order_line_tbl(i).status := l_line_tbl(i).return_status;
2134 END LOOP;
2135
2136 -- map fulfillment
2137
2138 -- check if the order can be satisfied by fulfillment alone. If yes, then call
2139 -- to OM can be avoided.
2140 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2141 aso_debug_pub.add('Create_Order: Before fulfilment: ', 1, 'N');
2142 END IF;
2143 IF p_control_rec.INTERFACE_FFM_FLAG = FND_API.G_TRUE
2144 AND x_return_status = FND_API.G_RET_STS_SUCCESS THEN
2145 p_ffm_request_rec.party_id := p_qte_rec.party_id;
2146 p_ffm_request_rec.user_id := FND_GLOBAL.USER_ID;
2147 p_ffm_request_rec.server_id := p_control_rec.server_id;
2148
2149
2150 Map_quote_to_fulfillment(
2151 p_qte_line_tbl => p_qte_line_tbl,
2152 p_line_attribs_ext_tbl => p_line_attribs_ext_tbl,
2153 -- p_fulfillment_tbl => fulfillment,
2154 x_ffm_content_tbl => l_ffm_content_tbl,
2155 x_ffm_bind_tbl => l_ffm_bind_tbl
2156 );
2157
2158 IF l_ffm_content_tbl.count > 0 THEN
2159
2160 ASO_FFM_INT.Submit_FFM_Request(
2161 P_Api_Version_Number => 1.0 ,
2162 p_Init_Msg_List => p_init_msg_list ,
2163 p_Commit => FND_API.G_FALSE ,
2164 p_validation_Level => FND_API.G_VALID_LEVEL_FULL,
2165 p_ffm_request_rec => p_ffm_request_rec,
2166 p_ffm_content_tbl => l_ffm_content_tbl,
2167 p_bind_tbl => l_ffm_bind_tbl ,
2168 X_Request_ID => x_request_id ,
2169 X_Return_Status => x_return_status,
2170 X_Msg_Count => x_msg_count ,
2171 X_Msg_Data => x_msg_data );
2172
2173 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2174 aso_debug_pub.add('Create_Order: After submit_ffm_request x_return_status: '||x_return_status, 1, 'N');
2175 END IF;
2176 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2177 --retrieve_oe_messages;
2178 RAISE FND_API.G_EXC_ERROR;
2179 END IF;
2180 END IF;
2181
2182 END IF;
2183
2184
2185 --
2186 -- End of API body
2187 --
2188 -- Standard check for p_commit
2189 IF FND_API.to_Boolean( p_commit )
2190 THEN
2191 COMMIT WORK;
2192 END IF;
2193
2194
2195
2196 -- Standard call to get message count and if count is 1, get message info.
2197 FND_MSG_PUB.Count_And_Get
2198 ( p_count => x_msg_count,
2199 p_data => x_msg_data
2200 );
2201
2202 EXCEPTION
2203 WHEN FND_API.G_EXC_ERROR THEN
2204 null;
2205 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
2206 P_API_NAME => L_API_NAME
2207 ,P_PKG_NAME => G_PKG_NAME
2208 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
2209 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
2210 ,X_MSG_COUNT => X_MSG_COUNT
2211 ,X_MSG_DATA => X_MSG_DATA
2212 ,X_RETURN_STATUS => X_RETURN_STATUS);
2213
2214 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2215 null;
2216 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
2217 P_API_NAME => L_API_NAME
2218 ,P_PKG_NAME => G_PKG_NAME
2219 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
2220 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
2221 ,X_MSG_COUNT => X_MSG_COUNT
2222 ,X_MSG_DATA => X_MSG_DATA
2223 ,X_RETURN_STATUS => X_RETURN_STATUS);
2224 WHEN OTHERS THEN
2225 null;
2226 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
2227 P_API_NAME => L_API_NAME
2228 ,P_PKG_NAME => G_PKG_NAME
2229 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
2230 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
2231 ,X_MSG_COUNT => X_MSG_COUNT
2232 ,X_MSG_DATA => X_MSG_DATA
2233 ,X_RETURN_STATUS => X_RETURN_STATUS);
2234
2235 END Create_Order;
2236
2237
2238
2239 PROCEDURE Update_order(
2240 P_Api_Version IN NUMBER,
2241 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
2242 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
2243 P_Qte_Rec IN ASO_QUOTE_PUB.Qte_Header_Rec_Type
2244 := ASO_QUOTE_PUB.G_MISS_QTE_HEADER_REC,
2245 P_Header_Payment_Tbl IN ASO_QUOTE_PUB.Payment_Tbl_Type
2246 := ASO_QUOTE_PUB.G_MISS_Payment_TBL,
2247 P_Header_Price_Adj_Tbl IN ASO_QUOTE_PUB.Price_Adj_Tbl_Type
2248 := ASO_QUOTE_PUB.G_MISS_Price_Adj_TBL,
2249 P_Header_Price_Attributes_Tbl IN ASO_QUOTE_PUB.Price_Attributes_Tbl_Type
2250 := ASO_QUOTE_PUB.G_MISS_Price_attributes_TBL,
2251 P_Header_Price_Adj_rltship_Tbl IN ASO_QUOTE_PUB.Price_Adj_Rltship_Tbl_Type
2252 := ASO_QUOTE_PUB.G_MISS_price_adj_rltship_TBL,
2253 P_Header_Price_Adj_Attr_Tbl IN ASO_QUOTE_PUB.Price_Adj_Attr_Tbl_Type
2254 := ASO_QUOTE_PUB.G_Miss_PRICE_ADJ_ATTR_Tbl,
2255 P_Header_Shipment_Tbl IN ASO_QUOTE_PUB.Shipment_Tbl_Type
2256 := ASO_QUOTE_PUB.G_MISS_shipment_TBL,
2257 P_Header_TAX_DETAIL_Tbl IN ASO_QUOTE_PUB.TAX_DETAIL_Tbl_Type
2258 := ASO_QUOTE_PUB.G_MISS_TAX_DETAIL_TBL,
2259 P_Header_FREIGHT_CHARGE_Tbl IN ASO_QUOTE_PUB.FREIGHT_CHARGE_Tbl_Type
2260 := ASO_QUOTE_PUB.G_MISS_FREIGHT_CHARGE_Tbl,
2261 P_Header_ATTRIBS_EXT_Tbl IN ASO_QUOTE_PUB.Line_Attribs_Ext_Tbl_Type
2262 := ASO_QUOTE_PUB.G_MISS_Line_Attribs_Ext_TBL,
2263 P_Header_Quote_Party_Tbl IN ASO_QUOTE_PUB.Quote_Party_Tbl_Type
2264 := ASO_QUOTE_PUB.G_MISS_Quote_Party_Tbl,
2265 P_header_sales_credit_TBL IN ASO_QUOTE_PUB.Sales_credit_tbl_type
2266 := ASO_QUOTE_PUB.G_MISS_sales_credit_TBL,
2267 P_Qte_Line_Tbl IN ASO_QUOTE_PUB.Qte_Line_Tbl_Type
2268 := ASO_QUOTE_PUB.G_MISS_qte_line_TBL,
2269 P_Qte_Line_Dtl_tbl IN ASO_QUOTE_PUB.Qte_Line_Dtl_tbl_Type
2270 := ASO_QUOTE_PUB.G_MISS_qte_line_dtl_TBL,
2271 P_Line_Payment_Tbl IN ASO_QUOTE_PUB.Payment_Tbl_Type
2272 := ASO_QUOTE_PUB.G_MISS_Payment_TBL,
2273 P_Line_Price_Adj_Tbl IN ASO_QUOTE_PUB.Price_Adj_Tbl_Type
2274 := ASO_QUOTE_PUB.G_MISS_Price_Adj_TBL,
2275 P_Line_Price_Attributes_Tbl IN ASO_QUOTE_PUB.Price_Attributes_Tbl_Type
2276 := ASO_QUOTE_PUB.G_MISS_Price_attributes_TBL,
2277 P_Line_Price_Adj_rltship_Tbl IN ASO_QUOTE_PUB.Price_Adj_Rltship_Tbl_Type
2278 := ASO_QUOTE_PUB.G_MISS_price_adj_rltship_TBL,
2279 P_Line_Price_Adj_Attr_Tbl IN ASO_QUOTE_PUB.Price_Adj_Attr_Tbl_Type
2280 := ASO_QUOTE_PUB.G_Miss_PRICE_ADJ_ATTR_Tbl,
2281 P_Line_Shipment_Tbl IN ASO_QUOTE_PUB.Shipment_Tbl_Type
2282 := ASO_QUOTE_PUB.G_MISS_shipment_TBL,
2283 P_Line_TAX_DETAIL_Tbl IN ASO_QUOTE_PUB.TAX_DETAIL_Tbl_Type
2284 := ASO_QUOTE_PUB.G_MISS_TAX_DETAIL_TBL,
2285 P_Line_FREIGHT_CHARGE_Tbl IN ASO_QUOTE_PUB.FREIGHT_CHARGE_Tbl_Type := ASO_QUOTE_PUB.G_MISS_FREIGHT_CHARGE_Tbl,
2286 P_Line_ATTRIBS_EXT_Tbl IN ASO_QUOTE_PUB.Line_Attribs_Ext_Tbl_Type
2287 := ASO_QUOTE_PUB.G_MISS_Line_Attribs_Ext_TBL,
2288 P_Line_Rltship_Tbl IN ASO_QUOTE_PUB.Line_Rltship_Tbl_Type
2289 := ASO_QUOTE_PUB.G_MISS_line_rltship_TBL,
2290 P_Line_sales_credit_TBL IN ASO_QUOTE_PUB.Sales_credit_tbl_type
2291 := ASO_QUOTE_PUB.G_MISS_sales_credit_TBL,
2292 P_Line_Quote_Party_Tbl IN ASO_QUOTE_PUB.Quote_Party_Tbl_Type
2293 := ASO_QUOTE_PUB.G_MISS_Quote_Party_Tbl,
2294 P_Lot_Serial_Tbl IN ASO_QUOTE_PUB.Lot_Serial_Tbl_Type
2295 := ASO_QUOTE_PUB.G_MISS_Lot_Serial_Tbl,
2296 P_Control_Rec IN Control_Rec_Type := G_MISS_Control_Rec,
2297 X_Order_Header_Rec OUT NOCOPY /* file.sql.39 change */ Order_Header_Rec_Type,
2298 X_Order_Line_Tbl OUT NOCOPY /* file.sql.39 change */ Order_Line_Tbl_type,
2299 X_Return_Status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
2300 X_Msg_Count OUT NOCOPY /* file.sql.39 change */ NUMBER,
2301 X_Msg_Data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
2302 )
2303 IS
2304 -- this should be changed post 11.5.1
2305 CURSOR C_cancel_reason IS
2306 SELECT lookup_code
2307 from oe_lookups
2308 WHERE lookup_type = 'CANCEL_CODE'
2309 AND lookup_code = 'Not provided';
2310
2311
2312 l_line_shipment_tbl ASO_QUOTE_PUB.Shipment_Tbl_Type;
2313 l_api_version_number CONSTANT NUMBER := 1.0;
2314 l_api_name CONSTANT VARCHAR2(30):= 'Update_Order';
2315 l_control_rec OE_GLOBALS.Control_Rec_Type;
2316 l_return_status VARCHAR2(1);
2317 l_header_rec OE_Order_PUB.Header_Rec_Type;
2318 l_Header_Adj_tbl OE_Order_PUB.Header_Adj_Tbl_Type;
2319 l_Header_price_Att_tbl OE_Order_PUB.Header_Price_Att_Tbl_Type ;
2320 l_Header_Adj_Att_tbl OE_Order_PUB.Header_Adj_Att_Tbl_Type ;
2321 l_Header_Adj_Assoc_tbl OE_Order_PUB.Header_Adj_Assoc_Tbl_Type ;
2322 l_Header_Scredit_tbl OE_Order_PUB.Header_Scredit_Tbl_Type;
2323 l_Header_Payment_tbl OE_Order_PUB.Header_Payment_Tbl_Type;
2324 l_Header_Payment_val_tbl OE_Order_PUB.Header_Payment_Val_Tbl_Type;
2325 l_line_tbl OE_Order_PUB.Line_Tbl_Type;
2326 l_Line_Adj_tbl OE_Order_PUB.Line_Adj_Tbl_Type;
2327 l_Line_price_Att_tbl OE_Order_PUB.Line_Price_Att_Tbl_Type ;
2328 l_Line_Adj_Att_tbl OE_Order_PUB.Line_Adj_Att_Tbl_Type ;
2329 l_Line_Adj_Assoc_tbl OE_Order_PUB.Line_Adj_Assoc_Tbl_Type ;
2330 l_Line_Scredit_tbl OE_Order_PUB.Line_Scredit_Tbl_Type;
2331 l_Line_Payment_tbl OE_Order_PUB.Line_Payment_Tbl_Type;
2332 l_Line_Payment_val_tbl OE_Order_PUB.Line_Payment_Val_Tbl_Type;
2333 l_Lot_Serial_tbl OE_Order_PUB.Lot_Serial_Tbl_Type;
2334 l_old_header_rec OE_Order_PUB.Header_Rec_Type;
2335 l_old_Header_Adj_tbl OE_Order_PUB.Header_Adj_Tbl_Type;
2336 l_old_Header_price_Att_tbl OE_Order_PUB.Header_Price_Att_Tbl_Type ;
2337 l_old_Header_Adj_Att_tbl OE_Order_PUB.Header_Adj_Att_Tbl_Type ;
2338 l_old_Header_Adj_Assoc_tbl OE_Order_PUB.Header_Adj_Assoc_Tbl_Type ;
2339 l_old_Header_Scredit_tbl OE_Order_PUB.Header_Scredit_Tbl_Type;
2340 l_old_line_tbl OE_Order_PUB.Line_Tbl_Type;
2341 l_old_Line_Adj_tbl OE_Order_PUB.Line_Adj_Tbl_Type;
2342 l_old_Line_price_Att_tbl OE_Order_PUB.Line_Price_Att_Tbl_Type ;
2343 l_old_Line_Adj_Att_tbl OE_Order_PUB.Line_Adj_Att_Tbl_Type ;
2344 l_old_Line_Adj_Assoc_tbl OE_Order_PUB.Line_Adj_Assoc_Tbl_Type ;
2345 l_old_Line_Scredit_tbl OE_Order_PUB.Line_Scredit_Tbl_Type;
2346 l_old_Lot_Serial_tbl OE_Order_PUB.Lot_Serial_Tbl_Type;
2347 l_request_tbl OE_Order_PUB.Request_Tbl_Type :=
2348 OE_Order_PUB.G_MISS_REQUEST_TBL;
2349
2350 l_return_values varchar2(50);
2351 l_header_val_rec OE_Order_PUB.Header_Val_Rec_Type;
2352 l_old_header_val_rec OE_Order_PUB.Header_Val_Rec_Type;
2353 l_header_adj_val_tbl OE_Order_PUB.Header_Adj_Val_Tbl_Type;
2354 l_old_header_adj_val_tbl OE_Order_PUB.Header_Adj_Val_Tbl_Type;
2355 l_header_scredit_val_tbl OE_Order_PUB.Header_Scredit_Val_Tbl_Type;
2356 l_old_header_scredit_val_tbl OE_Order_PUB.Header_Scredit_Val_Tbl_Type;
2357 l_line_val_tbl OE_Order_PUB.Line_Val_Tbl_Type;
2358 l_old_line_val_tbl OE_Order_PUB.Line_Val_Tbl_Type;
2359 l_line_adj_val_tbl OE_Order_PUB.Line_Adj_Val_Tbl_Type;
2360 l_old_line_adj_val_tbl OE_Order_PUB.Line_Adj_Val_Tbl_Type;
2361 l_line_scredit_val_tbl OE_Order_PUB.Line_Scredit_Val_Tbl_Type;
2362 l_old_line_scredit_val_tbl OE_Order_PUB.Line_Scredit_Val_Tbl_Type;
2363 l_lot_serial_val_tbl OE_Order_PUB.Lot_Serial_Val_Tbl_Type;
2364 l_old_lot_serial_val_tbl OE_Order_PUB.Lot_Serial_Val_Tbl_Type;
2365
2366 -- hyang: bug 2692785
2367 lx_header_rec OE_Order_PUB.Header_Rec_Type;
2368 lx_Header_Adj_tbl OE_Order_PUB.Header_Adj_Tbl_Type;
2369 lx_Header_price_Att_tbl OE_Order_PUB.Header_Price_Att_Tbl_Type ;
2370 lx_Header_Adj_Att_tbl OE_Order_PUB.Header_Adj_Att_Tbl_Type ;
2371 lx_Header_Adj_Assoc_tbl OE_Order_PUB.Header_Adj_Assoc_Tbl_Type ;
2372 lx_Header_Scredit_tbl OE_Order_PUB.Header_Scredit_Tbl_Type;
2373 lx_Header_Payment_tbl OE_Order_PUB.Header_Payment_Tbl_Type;
2374 lx_line_tbl OE_Order_PUB.Line_Tbl_Type;
2375 lx_Line_Adj_tbl OE_Order_PUB.Line_Adj_Tbl_Type;
2376 lx_Line_price_Att_tbl OE_Order_PUB.Line_Price_Att_Tbl_Type ;
2377 lx_Line_Adj_Att_tbl OE_Order_PUB.Line_Adj_Att_Tbl_Type ;
2378 lx_Line_Adj_Assoc_tbl OE_Order_PUB.Line_Adj_Assoc_Tbl_Type ;
2379 lx_Line_Scredit_tbl OE_Order_PUB.Line_Scredit_Tbl_Type;
2380 lx_Lot_Serial_tbl OE_Order_PUB.Lot_Serial_Tbl_Type;
2381 lx_Line_Payment_tbl OE_Order_PUB.Line_Payment_Tbl_Type;
2382 lx_request_tbl OE_Order_PUB.Request_Tbl_Type;
2383
2384 l_split_pay_prof VARCHAR2(2) := FND_PROFILE.Value('ASO_ENABLE_SPLIT_PAYMENT');
2385 l_CC_Auth_Prof VARCHAR2(2) := FND_PROFILE.Value('ASO_CC_AUTHORIZATION_ENABLED');
2386 l_Enable_Risk_Mgmt_Prof VARCHAR2(2);
2387 l_CC_Auth_Failure_Prof VARCHAR2(20);
2388 l_Risk_Mgmt_Failure_Prof VARCHAR2(20);
2389
2390 i NUMBER := 1;
2391 BEGIN
2392 -- Standard Start of API savepoint
2393 SAVEPOINT UPDATE_order_PVT;
2394
2395 aso_debug_pub.g_debug_flag := nvl(fnd_profile.value('ASO_ENABLE_DEBUG'),'N');
2396
2397 -- Standard call to check for call compatibility.
2398 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
2399 p_api_version ,
2400 l_api_name,
2401 G_PKG_NAME)
2402 THEN
2403 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2404 END IF;
2405
2406
2407 -- Initialize message list if p_init_msg_list is set to TRUE.
2408 IF FND_API.to_Boolean( p_init_msg_list )
2409 THEN
2410 FND_MSG_PUB.initialize;
2411 END IF;
2412
2413 -- Debug Message
2414 ASO_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: ' || l_api_name || 'start');
2415
2416 -- Initialize API return status to SUCCESS
2417 x_return_status := FND_API.G_RET_STS_SUCCESS;
2418
2419 --
2420 -- API body
2421 --
2422
2423 -- ******************************************************************
2424 -- Validate Environment
2425 -- ******************************************************************
2426 IF FND_GLOBAL.User_Id IS NULL
2427 THEN
2428 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2429 THEN
2430 FND_MESSAGE.Set_Name(' + appShortName +', 'UT_CANNOT_GET_PROFILE_VALUE');
2431 FND_MESSAGE.Set_Token('PROFILE', 'USER_ID', FALSE);
2432 FND_MSG_PUB.ADD;
2433 END IF;
2434 RAISE FND_API.G_EXC_ERROR;
2435 END IF;
2436
2437
2438 -- change the org to whatever is stored in the org id column
2439 IF p_qte_rec.org_id is not NULL
2440 AND p_qte_rec.org_id <> FND_API.G_MISS_NUM THEN
2441
2442 /* fnd_client_info.set_org_context(p_qte_rec.org_id); */ --Commented Code Yogeshwar (MOAC)
2443 MO_GLOBAL.set_policy_context ('S', p_qte_rec.org_id) ; --New Code Yogeshwar (MOAC)
2444
2445 END IF;
2446
2447 IF p_qte_rec.order_id is null OR
2448 p_qte_rec.order_id = FND_API.G_MISS_NUM THEN
2449
2450 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2451 THEN
2452 FND_MESSAGE.Set_Name('ASO', 'ASO_API_MISSING_INFO');
2453 FND_MESSAGE.Set_Token('COLUMN', 'ORDER_ID', FALSE);
2454 FND_MSG_PUB.ADD;
2455 END IF;
2456 raise FND_API.G_EXC_ERROR;
2457 END IF;
2458
2459 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2460 aso_debug_pub.add('l_split_pay_prof: '||l_split_pay_prof,1,'N');
2461 END IF;
2462
2463 IF (l_split_pay_prof = 'N') THEN
2464 IF p_header_payment_tbl.count > 1 THEN
2465 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2466 FND_MESSAGE.Set_Name('ASO', 'ASO_API_SPLIT_PAYMENT');
2467 FND_MSG_PUB.ADD;
2468 END IF;
2469 RAISE FND_API.G_EXC_ERROR;
2470 ELSIF p_header_payment_tbl.count = 1 THEN
2471 IF p_header_payment_tbl(1).payment_option = 'SPLIT' THEN
2472 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2473 FND_MESSAGE.Set_Name('ASO', 'ASO_API_SPLIT_PAYMENT');
2474 FND_MSG_PUB.ADD;
2475 END IF;
2476 RAISE FND_API.G_EXC_ERROR;
2477 END IF;
2478
2479 END IF;-- p_hd_payment
2480
2481 END IF;-- FND_PROFILE.Value
2482 IF (l_split_pay_prof = 'Y') THEN
2483 IF p_header_payment_tbl.count > 1 THEN
2484 FOR i IN 1..p_header_payment_tbl.count LOOP
2485 IF p_header_payment_tbl(i).payment_option <> 'SPLIT' THEN
2486 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2487 FND_MESSAGE.Set_Name('ASO', 'ASO_API_TOO_MANY_PAYMENTS');
2488 FND_MSG_PUB.ADD;
2489 END IF;
2490 RAISE FND_API.G_EXC_ERROR;
2491 END IF;
2492 END LOOP;
2493 END IF;
2494 END IF;
2495
2496 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2497 aso_debug_pub.add('before ASO_MAP_QUOTE_ORDER_INT.Map_Quote_Order_Int: ',1,'N');
2498 END IF;
2499
2500 ASO_MAP_QUOTE_ORDER_INT.Map_Quote_to_order(
2501 p_operation => 'UPDATE' ,
2502 P_Qte_Rec => P_Qte_Rec ,
2503 P_Header_Payment_Tbl => P_Header_Payment_Tbl ,
2504 P_Header_Price_Adj_Tbl => P_Header_Price_Adj_Tbl ,
2505 P_Header_Price_Attributes_Tbl => P_Header_Price_Attributes_Tbl ,
2506 P_Header_Price_Adj_rltship_Tbl=> P_Header_Price_Adj_rltship_Tbl ,
2507 P_Header_Price_Adj_Attr_Tbl => P_Header_Price_Adj_Attr_Tbl ,
2508 P_Header_Shipment_Tbl => P_Header_Shipment_Tbl ,
2509 P_Header_TAX_DETAIL_Tbl => P_Header_TAX_DETAIL_Tbl ,
2510 P_Header_FREIGHT_CHARGE_Tbl => P_Header_FREIGHT_CHARGE_Tbl ,
2511 P_header_sales_credit_TBL => P_header_sales_credit_TBL ,
2512 P_Qte_Line_Tbl => P_Qte_Line_Tbl ,
2513 P_Qte_Line_Dtl_Tbl => P_Qte_Line_Dtl_Tbl ,
2514 P_Line_Payment_Tbl => P_Line_Payment_Tbl ,
2515 P_Line_Price_Adj_Tbl => P_Line_Price_Adj_Tbl ,
2516 P_Line_Price_Attributes_Tbl => P_Line_Price_Attributes_Tbl ,
2517 P_Line_Price_Adj_rltship_Tbl => P_Line_Price_Adj_rltship_Tbl ,
2518 P_Line_Price_Adj_Attr_Tbl => P_Line_Price_Adj_Attr_Tbl ,
2519 P_Line_Shipment_Tbl => P_Line_Shipment_Tbl ,
2520 P_Line_TAX_DETAIL_Tbl => P_Line_TAX_DETAIL_Tbl ,
2521 P_Line_FREIGHT_CHARGE_Tbl => P_Line_FREIGHT_CHARGE_Tbl ,
2522 P_Line_Rltship_Tbl => P_Line_Rltship_Tbl ,
2523 P_Line_sales_credit_TBL => P_Line_sales_credit_TBL ,
2524 P_Lot_serial_TBL => P_Lot_serial_TBL ,
2525 x_header_rec => l_header_rec ,
2526 x_header_val_rec => l_header_val_rec ,
2527 x_header_Adj_tbl => l_header_Adj_tbl ,
2528 x_header_Adj_val_tbl => l_header_Adj_val_tbl ,
2529 x_header_price_Att_tbl => l_header_price_Att_tbl ,
2530 x_header_Adj_Att_tbl => l_header_Adj_Att_tbl ,
2531 x_header_Adj_Assoc_tbl => l_header_Adj_Assoc_tbl ,
2532 x_header_Scredit_tbl => l_header_Scredit_tbl ,
2533 x_Header_Scredit_val_tbl => l_Header_Scredit_val_tbl ,
2534 x_Header_Payment_tbl => l_Header_Payment_tbl ,
2535 x_line_tbl => l_line_tbl ,
2536 x_line_val_tbl => l_line_val_tbl ,
2537 x_line_Adj_tbl => l_line_Adj_tbl ,
2538 x_line_Adj_val_tbl => l_line_Adj_val_tbl ,
2539 x_line_price_Att_tbl => l_line_price_Att_tbl ,
2540 x_line_Adj_Att_tbl => l_line_Adj_Att_tbl ,
2541 x_line_Adj_Assoc_tbl => l_line_Adj_Assoc_tbl ,
2542 x_line_Scredit_tbl => l_line_Scredit_tbl ,
2543 x_line_Scredit_val_tbl => l_line_Scredit_val_tbl ,
2544 x_lot_Serial_tbl => l_lot_Serial_tbl ,
2545 X_Lot_Serial_val_tbl => l_Lot_Serial_val_tbl ,
2546 x_Line_Payment_tbl => l_Line_Payment_tbl
2547 );
2548
2549 l_header_rec.operation := OE_Globals.G_OPR_UPDATE;
2550
2551 -- set the control record flags
2552 -- book order
2553
2554 IF p_control_rec.book_flag = FND_API.G_TRUE THEN
2555 l_request_tbl(i).entity_code := OE_GLOBALS.G_ENTITY_HEADER;
2556 l_request_tbl(i).request_type := OE_GLOBALS.G_BOOK_ORDER;
2557 i := i + 1;
2558 END IF; -- booking
2559
2560 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2561 aso_debug_pub.add('Create_Order: l_CC_Auth_Prof: '||l_CC_Auth_Prof, 1, 'N');
2562 END IF;
2563
2564 IF l_CC_Auth_Prof = 'Y' AND P_Control_Rec.CC_By_Fax <> FND_API.G_TRUE THEN
2565
2566 FOR x IN 1..l_Header_Payment_tbl.count LOOP
2567 IF l_Header_Payment_tbl(x).Payment_Type_Code = 'CREDIT_CARD' AND l_Header_Payment_tbl(x).trxn_extension_id IS NOT NULL THEN
2568 --l_cc_auth_failure_prof := NVL(FND_PROFILE.Value('ASO_CC_AUTH_FAILURE'), 'REJECT');
2569 l_enable_risk_mgmt_prof := NVL(FND_PROFILE.Value('ASO_RISK_MANAGE_CC_AUTH'), 'Y');
2570
2571 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2572 aso_debug_pub.add('Create_Order: l_enable_risk_mgmt_prof: '||l_enable_risk_mgmt_prof, 1, 'N');
2573 END IF;
2574
2575 l_request_tbl(i).request_type := OE_GLOBALS.G_VERIFY_PAYMENT;
2576 l_request_tbl(i).entity_code := OE_GLOBALS.G_ENTITY_HEADER;
2577 --l_request_tbl(i).param2 := l_cc_auth_failure_prof;
2578
2579 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2580 aso_debug_pub.add('Create_Order: l_request_tbl(i).param2: '|| l_request_tbl(i).param2, 1, 'N');
2581 END IF;
2582
2583 IF l_enable_risk_mgmt_prof = 'Y' THEN
2584 --l_risk_mgmt_failure_prof := NVL(FND_PROFILE.Value('ASO_RISK_MANAGE_FAILURE'), 'REJECT');
2585 l_request_tbl(i).param1 := 'Y';
2586 --l_request_tbl(i).param3 := l_risk_mgmt_failure_prof;
2587 ELSE
2588 l_request_tbl(i).param1 := 'N';
2589 END IF;
2590 i := i + 1;
2591 END IF; -- l_Header_Payment_tbl
2592 END LOOP; -- l_Header_Payment_tbl
2593
2594 FOR x IN 1..l_Line_Payment_tbl.count LOOP
2595 IF l_Line_Payment_tbl(x).Payment_Type_Code = 'CREDIT_CARD' AND l_Line_Payment_tbl(x).trxn_extension_id IS NOT NULL THEN
2596 --l_cc_auth_failure_prof := NVL(FND_PROFILE.Value('ASO_CC_AUTH_FAILURE'), 'REJECT');
2597 l_enable_risk_mgmt_prof := NVL(FND_PROFILE.Value('ASO_RISK_MANAGE_CC_AUTH'), 'Y');
2598
2599 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2600 aso_debug_pub.add('Create_Order: l_enable_risk_mgmt_prof: '||l_enable_risk_mgmt_prof, 1, 'N');
2601 END IF;
2602
2603 l_request_tbl(i).request_type := OE_GLOBALS.G_VERIFY_PAYMENT;
2604 l_request_tbl(i).entity_code := OE_GLOBALS.G_ENTITY_LINE;
2605 l_request_tbl(i).entity_index := l_Line_Payment_tbl(x).Line_Index;
2606 --l_request_tbl(i).param2 := l_cc_auth_failure_prof;
2607
2608 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2609 aso_debug_pub.add('Create_Order: l_request_tbl(i).param2: '|| l_request_tbl(i).param2, 1, 'N');
2610 END IF;
2611
2612 IF l_enable_risk_mgmt_prof = 'Y' THEN
2613 --l_risk_mgmt_failure_prof := NVL(FND_PROFILE.Value('ASO_RISK_MANAGE_FAILURE'), 'REJECT');
2614 l_request_tbl(i).param1 := 'Y';
2615 --l_request_tbl(i).param3 := l_risk_mgmt_failure_prof;
2616 ELSE
2617 l_request_tbl(i).param1 := 'N';
2618 END IF;
2619 i := i + 1;
2620 END IF; -- l_Line_Payment_tbl
2621 END LOOP; -- l_Line_Payment_tbl
2622
2623 END IF; -- l_CC_Auth_Prof
2624
2625 -- cancel code - this should be changed
2626 FOR i in 1..l_line_tbl.count LOOP
2627 IF l_line_tbl(i).ordered_quantity is not NULL
2628 OR l_line_tbl(i).ordered_quantity <> FND_API.G_MISS_NUM THEN
2629
2630 OPEN C_cancel_reason;
2631 FETCH C_cancel_reason into l_line_tbl(i).change_reason;
2632 CLOSE C_cancel_reason;
2633
2634 END IF;
2635 END LOOP;
2636
2637 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2638 aso_debug_pub.add('Update_Order: Before Process_order',1,'Y');
2639 aso_utility_pvt.print_login_info();
2640 END IF;
2641
2642 -- bug# 1927450
2643 OE_STANDARD_WF.SAVE_MESSAGES_OFF;
2644
2645 OE_Order_GRP.Process_Order
2646 ( p_api_version_number => l_api_version_number
2647 , p_init_msg_list => FND_API.G_TRUE
2648 , p_return_values => l_return_values
2649 , p_commit => p_commit
2650 , x_return_status => x_return_status
2651 , x_msg_count => x_msg_count
2652 , x_msg_data => x_msg_data
2653 , p_header_rec => l_header_rec
2654 --, p_header_val_rec => l_header_val_rec
2655 , p_Header_Adj_tbl => l_header_adj_tbl
2656 --, p_Header_Adj_val_tbl => l_header_adj_val_tbl
2657 , p_Header_price_Att_tbl => l_header_price_att_tbl
2658 , p_Header_Adj_Att_tbl => l_header_adj_att_tbl
2659 , p_Header_Adj_Assoc_tbl => l_header_adj_assoc_tbl
2660 , p_Header_Scredit_tbl => l_header_scredit_tbl
2661 --, p_Header_Scredit_val_tbl => l_header_scredit_val_tbl
2662 , p_Header_Payment_tbl => l_header_payment_tbl
2663 , p_line_tbl => l_line_tbl
2664 --, p_line_val_tbl => l_line_val_tbl
2665 , p_Line_Adj_tbl => l_line_adj_tbl
2666 --, p_Line_Adj_val_tbl => l_line_adj_val_tbl
2667 , p_Line_price_Att_tbl => l_line_price_att_tbl
2668 , p_Line_Adj_Att_tbl => l_Line_Adj_Att_tbl
2669 , p_Line_Adj_Assoc_tbl => l_line_adj_assoc_tbl
2670 , p_Line_Scredit_tbl => l_line_scredit_tbl
2671 --, p_Line_Scredit_val_tbl => l_line_scredit_val_tbl
2672 , p_Lot_Serial_tbl => l_lot_serial_tbl
2673 --, p_Lot_Serial_val_tbl => l_lot_serial_val_tbl
2674 , p_Line_Payment_tbl => l_line_payment_tbl
2675 , P_Action_Request_tbl => l_request_tbl
2676 , x_header_rec => lx_header_rec
2677 , x_header_val_rec => l_header_val_rec
2678 , x_Header_Adj_tbl => lx_header_adj_tbl
2679 , x_Header_Adj_val_tbl => l_header_adj_val_tbl
2680 , x_Header_price_Att_tbl => lx_header_price_att_tbl
2681 , x_Header_Adj_Att_tbl => lx_header_adj_att_tbl
2682 , x_Header_Adj_Assoc_tbl => lx_header_adj_assoc_tbl
2683 , x_Header_Scredit_tbl => lx_header_scredit_tbl
2684 , x_Header_Scredit_val_tbl => l_header_scredit_val_tbl
2685 , x_Header_Payment_tbl => lx_header_payment_tbl
2686 , x_Header_Payment_val_tbl => l_header_payment_val_tbl
2687 , x_line_tbl => lx_line_tbl
2688 , x_line_val_tbl => l_line_val_tbl
2689 , x_Line_Adj_tbl => lx_line_adj_tbl
2690 , x_Line_Adj_val_tbl => l_line_adj_val_tbl
2691 , x_Line_price_Att_tbl => lx_line_price_att_tbl
2692 , x_Line_Adj_Att_tbl => lx_line_adj_att_tbl
2693 , x_Line_Adj_Assoc_tbl => lx_line_adj_assoc_tbl
2694 , x_Line_Scredit_tbl => lx_line_scredit_tbl
2695 , x_Line_Scredit_val_tbl => l_line_scredit_val_tbl
2696 , x_Lot_Serial_tbl => lx_lot_serial_tbl
2697 , x_Lot_Serial_val_tbl => l_lot_serial_val_tbl
2698 , x_Line_Payment_tbl => lx_line_payment_tbl
2699 , x_Line_Payment_val_tbl => l_line_payment_val_tbl
2700 , x_action_request_tbl => lx_request_tbl
2701 );
2702
2703 -- hyang: bug 2692785
2704 l_header_rec := lx_header_rec;
2705 l_Header_Adj_tbl := lx_Header_Adj_tbl;
2706 l_Header_price_Att_tbl := lx_Header_price_Att_tbl;
2707 l_Header_Adj_Att_tbl := lx_Header_Adj_Att_tbl;
2708 l_Header_Adj_Assoc_tbl := lx_Header_Adj_Assoc_tbl;
2709 l_Header_Scredit_tbl := lx_Header_Scredit_tbl;
2710 l_line_tbl := lx_line_tbl;
2711 l_Line_Adj_tbl := lx_Line_Adj_tbl;
2712 l_Line_price_Att_tbl := lx_Line_price_Att_tbl;
2713 l_Line_Adj_Att_tbl := lx_Line_Adj_Att_tbl;
2714 l_Line_Adj_Assoc_tbl := lx_Line_Adj_Assoc_tbl;
2715 l_Line_Scredit_tbl := lx_Line_Scredit_tbl;
2716 l_Lot_Serial_tbl := lx_Lot_Serial_tbl;
2717 l_request_tbl := lx_request_tbl;
2718
2719 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2720 aso_debug_pub.add('Update_Order: After Process_Order: x_return_status'||x_return_status,1,'Y');
2721 aso_utility_pvt.print_login_info();
2722 END IF;
2723
2724 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2725 IF x_msg_count > 0 THEN
2726 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2727 FND_MESSAGE.Set_Name('ASO', 'ASO_OM_ERROR_MSG');
2728 FND_MSG_PUB.ADD;
2729 END IF;
2730 END IF;
2731
2732 retrieve_oe_messages;
2733
2734 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2735 raise FND_API.G_EXC_ERROR;
2736 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2737 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2738 END IF;
2739
2740 END IF;
2741
2742 IF (x_return_status = FND_API.G_RET_STS_SUCCESS AND
2743 p_control_rec.book_flag = FND_API.G_TRUE AND
2744 l_request_tbl(1).return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2745 if x_msg_count > 0 then
2746 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2747 FND_MESSAGE.Set_Name('ASO', 'ASO_OM_ERROR_MSG');
2748 FND_MSG_PUB.ADD;
2749 END IF;
2750 end if;
2751 retrieve_oe_messages;
2752 x_return_status := FND_API.G_RET_STS_ERROR;
2753 RAISE FND_API.G_EXC_ERROR;
2754 END IF;
2755
2756 retrieve_oe_messages;
2757
2758 x_order_header_rec.order_number := l_header_rec.order_number;
2759 x_order_header_rec.order_header_id := l_header_rec.header_id;
2760 x_order_header_rec.status := l_header_rec.return_status;
2761 -- x_order_header_rec.quote_header_id := to_number(l_header_rec.orig_sys_document_ref);
2762 x_order_header_rec.quote_header_id := l_header_rec.source_document_id;
2763
2764 FOR i in 1..l_line_tbl.count LOOP
2765 x_order_line_tbl(i).order_line_id := l_line_tbl(i).line_id;
2766 x_order_line_tbl(i).order_header_id := l_line_tbl(i).header_id;
2767 -- x_order_line_tbl(i).quote_shipment_line_id := to_number(l_line_tbl(i).orig_sys_document_ref);
2768 x_order_line_tbl(i).quote_shipment_line_id := l_line_tbl(i).source_document_line_id;
2769 x_order_line_tbl(i).status := l_line_tbl(i).return_status;
2770 END LOOP;
2771
2772 --
2773 -- End of API body
2774 --
2775
2776 -- Standard check for p_commit
2777 IF FND_API.to_Boolean( p_commit )
2778 THEN
2779 COMMIT WORK;
2780 END IF;
2781
2782
2783
2784 -- Standard call to get message count and if count is 1, get message info.
2785 FND_MSG_PUB.Count_And_Get
2786 ( p_count => x_msg_count,
2787 p_data => x_msg_data
2788 );
2789
2790 EXCEPTION
2791 WHEN FND_API.G_EXC_ERROR THEN
2792 null;
2793 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
2794 P_API_NAME => L_API_NAME
2795 ,P_PKG_NAME => G_PKG_NAME
2796 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
2797 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
2798 ,X_MSG_COUNT => X_MSG_COUNT
2799 ,X_MSG_DATA => X_MSG_DATA
2800 ,X_RETURN_STATUS => X_RETURN_STATUS);
2801
2802 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2803 null;
2804 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
2805 P_API_NAME => L_API_NAME
2806 ,P_PKG_NAME => G_PKG_NAME
2807 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
2808 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
2809 ,X_MSG_COUNT => X_MSG_COUNT
2810 ,X_MSG_DATA => X_MSG_DATA
2811 ,X_RETURN_STATUS => X_RETURN_STATUS);
2812 WHEN OTHERS THEN
2813 null;
2814 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
2815 P_API_NAME => L_API_NAME
2816 ,P_PKG_NAME => G_PKG_NAME
2817 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
2818 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
2819 ,X_MSG_COUNT => X_MSG_COUNT
2820 ,X_MSG_DATA => X_MSG_DATA
2821 ,X_RETURN_STATUS => X_RETURN_STATUS);
2822
2823 END Update_Order;
2824
2825
2826
2827
2828 FUNCTION Get_Header_Status (
2829 p_Header_Id NUMBER) RETURN VARCHAR2
2830 IS
2831 l_result_date DATE;
2832 l_latest_date DATE := NULL;
2833 l_result VARCHAR2(1);
2834 l_return_status VARCHAR2(20) := NULL;
2835 BEGIN
2836
2837
2838 OE_HEADER_STATUS_PUB.Get_Booked_Status
2839 (
2840 p_header_id => p_Header_Id
2841 , x_result => l_result
2842 , x_result_date => l_result_date
2843 );
2844
2845 IF ( l_result = 'Y' ) THEN
2846
2847 l_return_status := 'BOOKED';
2848 l_latest_date := l_result_date;
2849
2850 END IF;
2851
2852
2853 OE_HEADER_STATUS_PUB.Get_Closed_Status
2854 (
2855 p_header_id => p_Header_Id
2856 , x_result => l_result
2857 , x_result_date => l_result_date
2858 );
2859
2860 IF ( l_result = 'Y' ) THEN
2861
2862 IF ( l_latest_date IS NULL OR l_result_date > l_latest_date ) THEN
2863
2864 l_latest_date := l_result_date;
2865 l_return_status := 'CLOSED';
2866
2867 END IF;
2868
2869 END IF;
2870
2871 OE_HEADER_STATUS_PUB.Get_Cancelled_Status
2872 (
2873 p_header_id => p_Header_Id
2874 , x_result => l_result
2875 , x_result_date => l_result_date
2876 );
2877
2878 IF ( l_result = 'Y' ) THEN
2879
2880 IF ( l_latest_date IS NULL OR l_result_date > l_latest_date ) THEN
2881
2882 l_latest_date := l_result_date;
2883 l_return_status := 'CANCELLED';
2884
2885 END IF;
2886
2887 END IF;
2888
2889 IF ( l_return_status IS NULL ) THEN
2890
2891 l_return_status := 'ENTERED';
2892
2893 END IF;
2894
2895 return (l_return_status);
2896
2897 END Get_Header_Status;
2898
2899
2900 FUNCTION Get_Line_Status (
2901 p_Line_Id NUMBER) RETURN VARCHAR2
2902 IS
2903 l_result_date DATE;
2904 l_latest_date DATE := NULL;
2905 l_result VARCHAR2(1);
2906 l_return_status VARCHAR2(20) := NULL;
2907 BEGIN
2908
2909 OE_LINE_STATUS_PUB.Get_Closed_Status
2910 (
2911 p_line_id => p_Line_Id
2912 , x_result => l_result
2913 , x_result_date => l_result_date
2914 );
2915
2916 IF ( l_result = 'Y' ) THEN
2917
2918 l_latest_date := l_result_date;
2919 l_return_status := 'CLOSED';
2920
2921 END IF;
2922
2923 OE_LINE_STATUS_PUB.Get_Cancelled_Status
2924 (
2925 p_line_id => p_Line_Id
2926 , x_result => l_result
2927 , x_result_date => l_result_date
2928 );
2929
2930 IF ( l_result = 'Y' ) THEN
2931
2932 IF ( l_latest_date IS NULL OR l_result_date > l_latest_date ) THEN
2933
2934 l_latest_date := l_result_date;
2935 l_return_status := 'CANCELLED';
2936
2937 END IF;
2938
2939 END IF;
2940
2941 OE_LINE_STATUS_PUB.Get_Purchase_Release_status
2942 (
2943 p_line_id => p_Line_Id
2944 , x_result => l_result
2945 , x_result_date => l_result_date
2946 );
2947
2948 IF ( l_result = 'Y' ) THEN
2949
2950 IF ( l_latest_date IS NULL OR l_result_date > l_latest_date ) THEN
2951
2952 l_latest_date := l_result_date;
2953 l_return_status := 'RELEASED';
2954
2955 END IF;
2956
2957 END IF;
2958
2959 OE_LINE_STATUS_PUB.Get_ship_Status
2960 (
2961 p_line_id => p_Line_Id
2962 , x_result => l_result
2963 , x_result_date => l_result_date
2964 );
2965
2966
2967 IF ( l_result = 'Y' ) THEN
2968
2969 IF ( l_latest_date IS NULL OR l_result_date > l_latest_date ) THEN
2970
2971 l_latest_date := l_result_date;
2972 l_return_status := 'SHIPPED';
2973
2974 END IF;
2975
2976 END IF;
2977
2978 OE_LINE_STATUS_PUB.Get_Received_Status
2979 (
2980 p_line_id => p_Line_Id
2981 , x_result => l_result
2982 , x_result_date => l_result_date
2983 );
2984
2985 IF ( l_result = 'Y' ) THEN
2986
2987 IF ( l_latest_date IS NULL OR l_result_date > l_latest_date ) THEN
2988
2989 l_latest_date := l_result_date;
2990 l_return_status := 'RECEIVED';
2991
2992 END IF;
2993
2994 END IF;
2995
2996 OE_LINE_STATUS_PUB.Get_Invoiced_Status
2997 (
2998 p_line_id => p_Line_Id
2999 , x_result => l_result
3000 , x_result_date => l_result_date
3001 );
3002
3003 IF ( l_result = 'Y' ) THEN
3004
3005 IF ( l_latest_date IS NULL OR l_result_date > l_latest_date ) THEN
3006
3007 l_latest_date := l_result_date;
3008 l_return_status := 'INVOICED';
3009
3010 END IF;
3011
3012 END IF;
3013
3014 IF ( l_return_status IS NULL ) THEN
3015
3016 l_return_status := 'ENTERED';
3017
3018 END IF;
3019
3020 return(l_return_status);
3021
3022 END Get_Line_Status;
3023
3024
3025 FUNCTION Total_List_Price (
3026 p_Header_Id in NUMBER,
3027 p_Line_Id in NUMBER,
3028 p_line_number in NUMBER,
3029 p_shipment_number in number := null)
3030 RETURN NUMBER
3031 IS
3032 l_total NUMBER;
3033 BEGIN
3034
3035 l_total := OE_OE_TOTALS_SUMMARY.LINE_TOTAL(
3036 p_Header_Id , p_Line_Id ,
3037 p_line_number, p_shipment_number);
3038
3039 RETURN l_total;
3040 EXCEPTION
3041 WHEN OTHERS THEN
3042 return null;
3043 END Total_List_Price;
3044
3045 FUNCTION Total_Order_Price (
3046 p_Header_Id in NUMBER) RETURN NUMBER
3047 IS
3048 l_total NUMBER;
3049 l_subtotal number;
3050 l_discount number;
3051 l_charges number;
3052 l_tax number;
3053 BEGIN
3054
3055 OE_OE_TOTALS_SUMMARY.ORDER_TOTALS( p_Header_Id,
3056 l_subtotal,
3057 l_discount,
3058 l_charges,
3059 l_tax
3060 );
3061
3062 l_total := l_subtotal + l_charges + l_tax;
3063
3064 RETURN (l_total);
3065
3066 EXCEPTION
3067 WHEN OTHERS THEN
3068 return null;
3069 END Total_Order_Price;
3070
3071 FUNCTION GET_ORDER_TOTAL(
3072 P_HEADER_ID IN NUMBER,
3073 P_LINE_ID IN NUMBER,
3074 P_TOTAL_TYPE IN VARCHAR2 := 'ALL')
3075 RETURN NUMBER
3076 IS
3077 l_total number;
3078 begin
3079
3080 aso_debug_pub.g_debug_flag := nvl(fnd_profile.value('ASO_ENABLE_DEBUG'),'N');
3081
3082 l_total := OE_Totals_GRP.GET_Order_Total (
3083 P_HEADER_ID ,
3084 P_LINE_ID,
3085 P_TOTAL_TYPE);
3086 return (l_total);
3087 EXCEPTION
3088 WHEN OTHERS THEN
3089 return null;
3090 END GET_ORDER_TOTAL;
3091
3092 PROCEDURE get_acct_site_uses
3093 (
3094 p_party_site_id IN NUMBER,
3095 p_acct_site_type IN VARCHAR2,
3096 p_cust_account_id IN NUMBER,
3097 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
3098 x_site_use_id OUT NOCOPY /* file.sql.39 change */ number
3099 )
3100 IS
3101 CURSOR relationship_cur IS
3102 SELECT a.party_type
3103 from
3104 HZ_PARTIES a, HZ_PARTY_SITES b
3105 where
3106 a.status = 'A'
3107 and b.party_site_id = p_party_site_id
3108 and b.party_id = a.party_id;
3109 CURSOR site_use_cur IS
3110 select a.site_use_id
3111 from
3112 hz_cust_site_uses_all a, hz_cust_acct_sites_all b
3113 where
3114 a.status = 'A'
3115 and b.cust_account_id = p_cust_account_id
3116 and b.party_site_id = p_party_site_id
3117 and a.cust_acct_site_id = b.cust_acct_site_id
3118 and a.site_use_code = p_acct_site_type;
3119 l_party_type VARCHAR2(30);
3120 l_site_use_id number;
3121 begin
3122
3123 aso_debug_pub.g_debug_flag := nvl(fnd_profile.value('ASO_ENABLE_DEBUG'),'N');
3124
3125 x_return_status := FND_API.G_RET_STS_SUCCESS;
3126
3127 OPEN relationship_cur;
3128 FETCH relationship_cur INTO l_party_type;
3129 IF (relationship_cur%NOTFOUND) THEN
3130 l_party_type := NULL;
3131 x_return_status := FND_API.G_RET_STS_ERROR;
3132 END IF;
3133 CLOSE relationship_cur;
3134
3135 IF l_party_type = 'PARTY_RELATIONSHIP' THEN
3136 OPEN site_use_cur;
3137 FETCH site_use_cur INTO l_site_use_id;
3138 IF (site_use_cur%NOTFOUND) THEN
3139 l_site_use_id := NULL;
3140 x_return_status := FND_API.G_RET_STS_ERROR;
3141 END IF;
3142 CLOSE site_use_cur;
3143 END IF;
3144
3145 x_site_use_id := l_site_use_id;
3146 END get_acct_site_uses;
3147
3148 PROCEDURE get_cust_acct_roles
3149 (
3150 p_party_id IN NUMBER,
3151 p_party_site_id IN NUMBER,
3152 p_acct_site_type IN VARCHAR2,
3153 p_cust_account_id IN NUMBER,
3154 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
3155 x_cust_account_role_id OUT NOCOPY /* file.sql.39 change */ number
3156 )
3157 IS
3158 CURSOR relationship_cur IS
3159 SELECT party_type
3160 from
3161 HZ_PARTIES
3162 where party_id = p_party_id and status ='A';
3163 CURSOR org_contact IS
3164 select a.org_contact_id
3165 from
3166 hz_org_contacts a, hz_relationships b
3167 where
3168 b.party_id = p_party_id
3169 --and a.status = 'A' -- status column is obseleted
3170 and b.relationship_id = a.party_relationship_id
3171 and (sysdate between nvl(b.start_date, sysdate) and nvl(b.end_date, sysdate));
3172 CURSOR cust_role IS
3173 select a.cust_account_role_id
3174 from
3175 hz_cust_account_roles a, hz_role_responsibility b, hz_cust_acct_sites_all c
3176 where
3177 a.role_type = 'CONTACT'
3178 and a.party_id = p_party_id
3179 and a.cust_account_id = p_cust_account_id
3180 and a.cust_acct_site_id = c.cust_acct_site_id
3181 and a.cust_account_id = c.cust_account_id
3182 and c.party_site_id = p_party_site_id
3183 and a.cust_account_role_id = b.cust_account_role_id
3184 and responsibility_type = p_acct_site_type;
3185 l_org_contact_id number;
3186 l_party_type VARCHAR2(30);
3187 l_cust_account_role_id number;
3188 begin
3189
3190 aso_debug_pub.g_debug_flag := nvl(fnd_profile.value('ASO_ENABLE_DEBUG'),'N');
3191
3192 x_return_status := FND_API.G_RET_STS_SUCCESS;
3193
3194 OPEN relationship_cur;
3195 FETCH relationship_cur INTO l_party_type;
3196 IF (relationship_cur%NOTFOUND) THEN
3197 l_party_type := NULL;
3198 x_return_status := FND_API.G_RET_STS_ERROR;
3199 END IF;
3200 CLOSE relationship_cur;
3201
3202 IF l_party_type = 'PARTY_RELATIONSHIP' THEN
3203 OPEN org_contact;
3204 FETCH org_contact INTO l_org_contact_id;
3205 IF (org_contact%NOTFOUND) THEN
3206 l_org_contact_id := NULL;
3207 x_return_status := FND_API.G_RET_STS_ERROR;
3208 END IF;
3209 CLOSE org_contact;
3210
3211 OPEN cust_role;
3212 FETCH cust_role INTO l_cust_account_role_id;
3213 IF (cust_role%NOTFOUND) THEN
3214 l_cust_account_role_id := NULL;
3215 x_return_status := FND_API.G_RET_STS_ERROR;
3216 END IF;
3217 CLOSE cust_role;
3218
3219 END IF;
3220
3221 x_cust_account_role_id := l_cust_account_role_id;
3222 END get_cust_acct_roles;
3223
3224 PROCEDURE Get_Cust_Accnt_Id(
3225 P_Qte_Rec IN ASO_QUOTE_PUB.Qte_Header_Rec_Type
3226 := ASO_QUOTE_PUB.G_MISS_QTE_HEADER_REC,
3227 p_Party_Id IN NUMBER,
3228 p_Cust_Acct_Id OUT NOCOPY /* file.sql.39 change */ NUMBER,
3229 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
3230 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
3231 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
3232 IS
3233
3234 CURSOR C_get_cust_id_from_party_id(l_Party_Id NUMBER) IS
3235 SELECT cust_account_id
3236 FROM hz_cust_accounts
3237 WHERE party_id = l_Party_Id
3238 and status = 'A';
3239
3240 count NUMBER := 0;
3241 x_cust_id NUMBER := NULL;
3242 lx_cust_id NUMBER := NULL;
3243
3244 l_msg_count number;
3245 l_msg_data varchar2(200);
3246 lx_cust_account_id NUMBER;
3247 l_return_status VARCHAR2(1);
3248
3249
3250
3251 BEGIN
3252
3253 OPEN C_get_cust_id_from_party_id(p_Party_Id);
3254
3255 LOOP
3256 FETCH C_get_cust_id_from_party_id INTO lx_cust_id;
3257 IF C_get_cust_id_from_party_id%ROWCOUNT > 1 THEN
3258 x_return_status := FND_API.G_RET_STS_ERROR;
3259 EXIT;
3260 END IF;
3261 EXIT WHEN C_get_cust_id_from_party_id%NOTFOUND;
3262 END LOOP;
3263
3264 CLOSE C_get_cust_id_from_party_id;
3265
3266 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3267 FND_MESSAGE.Set_Name('ASO', 'ASO_MULTIPLE_CUST_ACCOUNT');
3268 FND_MESSAGE.Set_Token('ID', to_char( p_qte_rec.party_id), FALSE);
3269 FND_MSG_PUB.ADD;
3270 raise FND_API.G_EXC_ERROR;
3271 END IF;
3272
3273 IF lx_cust_id IS NULL OR lx_cust_id = FND_API.G_MISS_NUM THEN
3274 IF p_qte_rec.party_id is not NULL
3275 AND p_qte_rec.party_id <> FND_API.G_MISS_NUM THEN
3276 ASO_PARTY_INT.Create_Customer_Account(
3277 p_api_version => 1.0
3278 ,P_Qte_REC => p_qte_rec
3279 ,x_return_status => l_return_status
3280 ,x_msg_count => l_msg_count
3281 ,x_msg_data => l_msg_data
3282 ,x_acct_id => lx_cust_account_id
3283 );
3284
3285 IF l_Return_Status <> FND_API.G_RET_STS_SUCCESS THEN
3286 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
3287 THEN
3288 FND_MESSAGE.Set_Name('ASO', 'ASO_CUST_ACCOUNT');
3289 FND_MESSAGE.Set_Token('ID', to_char( p_qte_rec.party_id), FALSE);
3290 FND_MSG_PUB.ADD;
3291 END IF;
3292 raise FND_API.G_EXC_ERROR;
3293 END IF;
3294
3295 p_Cust_Acct_Id := lx_cust_account_id;
3296 END IF;
3297
3298 ELSE
3299 p_Cust_Acct_Id := lx_cust_id;
3300
3301 END IF;
3302
3303 END Get_Cust_Accnt_Id;
3304
3305 End ASO_ORDER_INT;