[Home] [Help]
PACKAGE BODY: APPS.ASO_ORDER_INT
Source
1 PACKAGE BODY ASO_order_INT as
2 /* $Header: asoiordb.pls 120.22.12020000.3 2012/08/08 05:26:09 akushwah 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
1753
1754 i NUMBER := 1;
1755 L_QUOTE_HEADER_ID number;
1756 L_QUOTE_STATUS_ID number;
1757 P varchar2(50);
1758 l_prg_index number:=0;
1759 l_dis_index number:=0;
1760 mod_header_id number;
1761
1762 -- 122 branch change
1763 cc number;
1764 v number;
1765 vs1 number;
1766 modifier_type VARCHAR2(100);
1767 prg_index number :=0;
1768 dis_index number :=0;
1769 chk_index number :=0;
1770 PREV_ADJ_INDEX NUMBER:=0;
1771 assoc_tbl_index number :=1;
1772 mod_head_id number;
1773
1774 /*** Start : Code change done for Bug 14358079 ***/
1775
1776 CURSOR C_LOCK(P_QUOTE_HEADER_ID NUMBER, P_QUOTE_STATUS_ID NUMBER) IS
1777 SELECT QUOTE_HEADER_ID,
1778 QUOTE_STATUS_ID
1779 FROM ASO_QUOTE_HEADERS_ALL
1780 WHERE QUOTE_HEADER_ID = P_QUOTE_HEADER_ID
1781 AND QUOTE_STATUS_ID = P_QUOTE_STATUS_ID
1782 FOR UPDATE OF QUOTE_STATUS_ID NOWAIT;
1783
1784 /*** End : Code change done for Bug 14358079 ***/
1785
1786 BEGIN
1787 -- Standard Start of API savepoint
1788 SAVEPOINT CREATE_ORDER_PVT;
1789
1790 aso_debug_pub.g_debug_flag := nvl(fnd_profile.value('ASO_ENABLE_DEBUG'),'N');
1791
1792 -- Standard call to check for call compatibility.
1793 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1794 p_api_version,
1795 l_api_name,
1796 G_PKG_NAME)
1797 THEN
1798 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1799 END IF;
1800
1801
1802 -- Initialize message list if p_init_msg_list is set to TRUE.
1803 IF FND_API.to_Boolean( p_init_msg_list )
1804 THEN
1805 FND_MSG_PUB.initialize;
1806 END IF;
1807
1808 -- Debug Message
1809 ASO_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: ' || l_api_name || 'start');
1810
1811 -- Initialize API return status to SUCCESS
1812 x_return_status := FND_API.G_RET_STS_SUCCESS;
1813
1814 --
1815 -- API body
1816 --
1817
1818 -- ******************************************************************
1819 -- Validate Environment
1820 -- ******************************************************************
1821 IF FND_GLOBAL.User_Id IS NULL
1822 THEN
1823 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1824 THEN
1825 FND_MESSAGE.Set_Name(' + appShortName +', 'UT_CANNOT_GET_PROFILE_VALUE');
1826 FND_MESSAGE.Set_Token('PROFILE', 'USER_ID', FALSE);
1827 FND_MSG_PUB.ADD;
1828 END IF;
1829 RAISE FND_API.G_EXC_ERROR;
1830 END IF;
1831 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1832 aso_debug_pub.add('Create_Order: Begin ', 1, 'N');
1833 END IF;
1834 -- change the org to whatever is stored in the org id column
1835 IF p_qte_rec.org_id is not NULL
1836 AND p_qte_rec.org_id <> FND_API.G_MISS_NUM THEN
1837
1838 /* fnd_client_info.set_org_context(p_qte_rec.org_id); */ --Commented Code Yogeshwar (MOAC)
1839 MO_GLOBAL.set_policy_context('S', p_qte_rec.org_id) ; --New Code Yogeshwar (MOAC)
1840
1841 END IF;
1842
1843 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1844 aso_debug_pub.add('l_split_pay_prof: '||l_split_pay_prof,1,'N');
1845 END IF;
1846
1847 IF (l_split_pay_prof = 'N') THEN
1848 IF p_header_payment_tbl.count > 1 THEN
1849 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1850 FND_MESSAGE.Set_Name('ASO', 'ASO_API_SPLIT_PAYMENT');
1851 FND_MSG_PUB.ADD;
1852 END IF;
1853 RAISE FND_API.G_EXC_ERROR;
1854 ELSIF p_header_payment_tbl.count = 1 THEN
1855 IF p_header_payment_tbl(1).payment_option = 'SPLIT' THEN
1856 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1857 FND_MESSAGE.Set_Name('ASO', 'ASO_API_SPLIT_PAYMENT');
1858 FND_MSG_PUB.ADD;
1859 END IF;
1860 RAISE FND_API.G_EXC_ERROR;
1861 END IF;
1862
1863 END IF;-- p_hd_payment
1864
1865 END IF;-- FND_PROFILE.Value
1866 -- Validate_Payment;(-- there should be only one rec if payment_option<>'SPLIT')
1867 IF (l_split_pay_prof = 'Y') THEN
1868 IF p_header_payment_tbl.count > 1 THEN
1869 FOR i IN 1..p_header_payment_tbl.count LOOP
1870 IF p_header_payment_tbl(i).payment_option <> 'SPLIT' THEN
1871 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1872 FND_MESSAGE.Set_Name('ASO', 'ASO_API_TOO_MANY_PAYMENTS');
1873 FND_MSG_PUB.ADD;
1874 END IF;
1875 RAISE FND_API.G_EXC_ERROR;
1876 END IF;
1877 END LOOP;
1878 END IF;
1879 END IF;
1880
1881 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1882 aso_debug_pub.add('Create_Order: Before MAP_QUOTE_ORDER_INT.Map_Quote_to_Order : ', 1, 'N');
1883 END IF;
1884 ASO_MAP_QUOTE_ORDER_INT.Map_Quote_to_order(
1885 p_operation => 'CREATE' ,
1886 P_Qte_Rec => P_Qte_Rec ,
1887 P_Header_Payment_Tbl => P_Header_Payment_Tbl ,
1888 P_Header_Price_Adj_Tbl => P_Header_Price_Adj_Tbl ,
1889 P_Header_Price_Attributes_Tbl => P_Header_Price_Attributes_Tbl ,
1890 P_Header_Price_Adj_rltship_Tbl=> P_Header_Price_Adj_rltship_Tbl ,
1891 P_Header_Price_Adj_Attr_Tbl => P_Header_Price_Adj_Attr_Tbl ,
1892 P_Header_Shipment_Tbl => P_Header_Shipment_Tbl ,
1893 P_Header_TAX_DETAIL_Tbl => P_Header_TAX_DETAIL_Tbl ,
1894 P_Header_FREIGHT_CHARGE_Tbl => P_Header_FREIGHT_CHARGE_Tbl ,
1895 P_header_sales_credit_TBL => P_header_sales_credit_TBL ,
1896 P_Qte_Line_Tbl => P_Qte_Line_Tbl ,
1897 P_Qte_Line_Dtl_Tbl => P_Qte_Line_Dtl_Tbl ,
1898 P_Line_Payment_Tbl => P_Line_Payment_Tbl ,
1899 P_Line_Price_Adj_Tbl => P_Line_Price_Adj_Tbl ,
1900 P_Line_Price_Attributes_Tbl => P_Line_Price_Attributes_Tbl ,
1901 P_Line_Price_Adj_rltship_Tbl => P_Line_Price_Adj_rltship_Tbl ,
1902 P_Line_Price_Adj_Attr_Tbl => P_Line_Price_Adj_Attr_Tbl ,
1903 P_Line_Shipment_Tbl => P_Line_Shipment_Tbl ,
1904 P_Line_TAX_DETAIL_Tbl => P_Line_TAX_DETAIL_Tbl ,
1905 P_Line_FREIGHT_CHARGE_Tbl => P_Line_FREIGHT_CHARGE_Tbl ,
1906 P_Line_Rltship_Tbl => P_Line_Rltship_Tbl ,
1907 P_Line_sales_credit_TBL => P_Line_sales_credit_TBL ,
1908 P_Lot_serial_TBL => P_Lot_serial_TBL ,
1909 P_Calculate_Price_Flag => P_Control_Rec.Calculate_Price,
1910 x_header_rec => l_header_rec ,
1911 x_header_val_rec => l_header_val_rec ,
1912 x_header_Adj_tbl => l_header_Adj_tbl ,
1913 x_header_Adj_val_tbl => l_header_Adj_val_tbl ,
1914 x_header_price_Att_tbl => l_header_price_Att_tbl ,
1915 x_header_Adj_Att_tbl => l_header_Adj_Att_tbl ,
1916 x_header_Adj_Assoc_tbl => l_header_Adj_Assoc_tbl ,
1917 x_header_Scredit_tbl => l_header_Scredit_tbl ,
1918 x_Header_Scredit_val_tbl => l_Header_Scredit_val_tbl ,
1919 x_Header_Payment_tbl => l_Header_Payment_tbl ,
1920 x_line_tbl => l_line_tbl ,
1921 x_line_val_tbl => l_line_val_tbl ,
1922 x_line_Adj_tbl => l_line_Adj_tbl ,
1923 x_line_Adj_val_tbl => l_line_Adj_val_tbl ,
1924 x_line_price_Att_tbl => l_line_price_Att_tbl ,
1925 x_line_Adj_Att_tbl => l_line_Adj_Att_tbl ,
1926 x_line_Adj_Assoc_tbl => l_line_Adj_Assoc_tbl ,
1927 x_line_Scredit_tbl => l_line_Scredit_tbl ,
1928 x_line_Scredit_val_tbl => l_line_Scredit_val_tbl ,
1929 x_lot_Serial_tbl => l_lot_Serial_tbl ,
1930 X_Lot_Serial_val_tbl => l_Lot_Serial_val_tbl ,
1931 x_Line_Payment_tbl => l_Line_Payment_tbl
1932 );
1933
1934 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1935 aso_debug_pub.add('Create_Order: After Map_Quote_to_Order: ', 1, 'N');
1936 END IF;
1937
1938 -- set the control record flags
1939 -- book order
1940
1941 IF p_control_rec.book_flag = FND_API.G_TRUE THEN
1942 l_request_tbl(i).entity_code := OE_GLOBALS.G_ENTITY_HEADER;
1943 l_request_tbl(i).request_type := OE_GLOBALS.G_BOOK_ORDER;
1944 i := i + 1;
1945 END IF; -- booking
1946
1947 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1948 aso_debug_pub.add('Create_Order: l_CC_Auth_Prof: '||l_CC_Auth_Prof, 1, 'N');
1949 END IF;
1950
1951 IF l_CC_Auth_Prof = 'Y' AND P_Control_Rec.CC_By_Fax <> FND_API.G_TRUE THEN
1952
1953 FOR x IN 1..l_Header_Payment_tbl.count LOOP
1954 IF l_Header_Payment_tbl(x).Payment_Type_Code = 'CREDIT_CARD' AND l_Header_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_HEADER;
1964 --l_request_tbl(i).param2 := l_cc_auth_failure_prof;
1965
1966 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1967 aso_debug_pub.add('Create_Order: l_request_tbl(i).param2: '|| l_request_tbl(i).param2, 1, 'N');
1968 END IF;
1969
1970 IF l_enable_risk_mgmt_prof = 'Y' THEN
1971 --l_risk_mgmt_failure_prof := NVL(FND_PROFILE.Value('ASO_RISK_MANAGE_FAILURE'), 'REJECT');
1972
1973 l_request_tbl(i).param1 := 'Y';
1974 --l_request_tbl(i).param3 := l_risk_mgmt_failure_prof;
1975 ELSE
1976 l_request_tbl(i).param1 := 'N';
1977 END IF;
1978 i := i + 1;
1979 END IF; -- l_Header_Payment_tbl
1980 END LOOP; -- l_Header_Payment_tbl
1981
1982 FOR x IN 1..l_Line_Payment_tbl.count LOOP
1983 IF l_Line_Payment_tbl(x).Payment_Type_Code = 'CREDIT_CARD' AND l_Line_Payment_tbl(x).trxn_extension_id IS NOT NULL THEN
1984 --l_cc_auth_failure_prof := NVL(FND_PROFILE.Value('ASO_CC_AUTH_FAILURE'), 'REJECT');
1985 l_enable_risk_mgmt_prof := NVL(FND_PROFILE.Value('ASO_RISK_MANAGE_CC_AUTH'), 'Y');
1986
1987 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1988 aso_debug_pub.add('Create_Order: l_enable_risk_mgmt_prof: '||l_enable_risk_mgmt_prof, 1, 'N');
1989 END IF;
1990
1991 l_request_tbl(i).request_type := OE_GLOBALS.G_VERIFY_PAYMENT;
1992 l_request_tbl(i).entity_code := OE_GLOBALS.G_ENTITY_LINE;
1993 l_request_tbl(i).entity_index := l_Line_Payment_tbl(x).Line_Index;
1994 --l_request_tbl(i).param2 := l_cc_auth_failure_prof;
1995
1996 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1997 aso_debug_pub.add('Create_Order: l_request_tbl(i).param2: '||l_request_tbl(i).param2, 1, 'N');
1998 END IF;
1999
2000 IF l_enable_risk_mgmt_prof = 'Y' THEN
2001 --l_risk_mgmt_failure_prof := NVL(FND_PROFILE.Value('ASO_RISK_MANAGE_FAILURE'), 'REJECT');
2002
2003 l_request_tbl(i).param1 := 'Y';
2004 --l_request_tbl(i).param3 := l_risk_mgmt_failure_prof;
2005 ELSE
2006 l_request_tbl(i).param1 := 'N';
2007 END IF;
2008 i := i + 1;
2009 END IF; -- l_Line_Payment_tbl
2010 END LOOP; -- l_Line_Payment_tbl
2011
2012 END IF; -- l_CC_Auth_Prof
2013
2014 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2015 aso_debug_pub.add('Create_Order: Before Proc_Order: ', 1, 'N');
2016 aso_utility_pvt.print_login_info();
2017 END IF;
2018
2019 -- added new debug messages
2020 IF l_line_tbl.count > 0 then
2021 for i in 1..l_line_tbl.count loop
2022 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2023 aso_debug_pub.add('Create_Order: l_line_tbl('|| i ||').operation: ' || l_line_tbl(i).operation, 1, 'N');
2024 aso_debug_pub.add('Create_Order: l_line_tbl('|| i ||').service_duration: ' || l_line_tbl(i).service_duration, 1, 'N');
2025 aso_debug_pub.add('Create_Order: l_line_tbl('|| i ||').service_period: ' || l_line_tbl(i).service_period, 1, 'N');
2026 aso_debug_pub.add('Create_Order: l_line_tbl('|| i ||').service_start_date: ' || l_line_tbl(i).service_start_date, 1, 'N');
2027 aso_debug_pub.add('Create_Order: l_line_tbl('|| i ||').service_end_date: ' || l_line_tbl(i).service_end_date, 1, 'N');
2028 aso_debug_pub.add('Create_Order: l_line_tbl('|| i ||').inventory_item_id: ' || l_line_tbl(i).inventory_item_id, 1, 'N');
2029 aso_debug_pub.add('Create_Order: l_line_tbl('|| i ||').ordered_quantity: ' || l_line_tbl(i).ordered_quantity, 1, 'N');
2030 aso_debug_pub.add('Create_Order: l_line_tbl('|| i ||').order_quantity_uom: ' || l_line_tbl(i).order_quantity_uom, 1, 'N');
2031 END IF;
2032 end loop;
2033 else
2034 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2035 aso_debug_pub.add('Create_Order: l_line_tbl count is 0 ', 1, 'N');
2036 END IF;
2037 end if;
2038
2039 -- bug# 1927450
2040 OE_STANDARD_WF.SAVE_MESSAGES_OFF;
2041
2042 /* P:=FND_GLOBAL.APPLICATION_SHORT_NAME;
2043 aso_debug_pub.add('Create_Order: Before OE_Order_GRP.Process_Order vs value of p' || P ,1,'N');
2044 --Start Fix for Bug bug 12775283
2045 If UPPER(P) IN ('QOT','ASO','ASF','ASL','AST','ASN','ONT') THEN -- 122 branch change
2046 Begin
2047 aso_debug_pub.add('Create_Order: Inside If condition ');
2048
2049 --Added this If Condition for bug12984742
2050 If P_Qte_Rec.QUOTE_HEADER_ID is not null then
2051
2052 SELECT QUOTE_HEADER_ID, QUOTE_STATUS_ID
2053 INTO L_QUOTE_HEADER_ID, L_QUOTE_STATUS_ID
2054 FROM ASO_QUOTE_HEADERS_ALL
2055 WHERE QUOTE_HEADER_ID = P_Qte_Rec.QUOTE_HEADER_ID
2056 and QUOTE_STATUS_ID = P_Qte_Rec.QUOTE_STATUS_ID
2057 FOR UPDATE OF QUOTE_STATUS_ID NOWAIT;
2058
2059 IF SQL%NOTFOUND THEN
2060 aso_debug_pub.add('Create_OrderSQL%NOTFOUND ASO_API_QUOTE_ORDER_PROGRESS ');
2061 FND_MESSAGE.Set_Name('ASO', 'ASO_API_QUOTE_ORDER_PROGRESS');
2062 FND_MSG_PUB.ADD;
2063 RAISE FND_API.G_EXC_ERROR;
2064 End if;
2065 End If;
2066
2067 aso_debug_pub.add('before process_order L_QUOTE_HEADER_ID ' || L_QUOTE_HEADER_ID);
2068 aso_debug_pub.add('before process_order L_QUOTE_STATUS_ID ' || L_QUOTE_STATUS_ID);
2069
2070 EXCEPTION
2071
2072 when NO_DATA_FOUND then
2073 Null;
2074
2075 WHEN OTHERS THEN
2076 aso_debug_pub.add('In others ASO_API_QUOTE_ORDER_PROGRESS ');
2077 FND_MESSAGE.SET_NAME('ASO', 'ASO_API_QUOTE_ORDER_PROGRESS');
2078 FND_MSG_PUB.ADD;
2079 RAISE FND_API.G_EXC_ERROR;
2080 END;
2081 End If; */
2082 --End Fix for Bug bug 12775283
2083
2084 --Bug 12800776 starts
2085 for I in 1 .. L_LINE_ADJ_TBL.COUNT
2086 LOOP
2087 aso_debug_pub.add('i value ' || I || 'l_line_adj_tbl.line_index ' || L_LINE_ADJ_TBL(I).LINE_INDEX || 'l_line_adj_tbl.list_line_type_code ' ||
2088 L_LINE_ADJ_TBL(I).LIST_LINE_TYPE_CODE ||' L_LINE_ADJ_TBL(I).modifier_header_id' || L_LINE_ADJ_TBL(I).list_header_id);
2089 end loop;
2090
2091 for line_index in 1..l_line_adj_tbl.count loop
2092 if l_line_adj_tbl(line_index).list_line_type_code in ('PBH','PRG','OID') then
2093 l_line_adj_assoc_tbl(assoc_tbl_index).adj_index:=line_index;
2094 prev_adj_index:=line_index;
2095 mod_head_id := l_line_adj_tbl(line_index).list_header_id;
2096 aso_debug_pub.add('Entered in PBH l_line_adj_assoc_tbl(assoc_tbl_index).adj_index' || L_LINE_ADJ_ASSOC_TBL(ASSOC_TBL_INDEX).ADJ_INDEX);
2097 elsif l_line_adj_tbl(line_index).list_line_type_code = 'DIS' and l_line_adj_tbl(line_index).list_header_id = mod_head_id then
2098 l_line_adj_assoc_tbl(assoc_tbl_index).adj_index:=prev_adj_index;
2099 l_line_adj_assoc_tbl(assoc_tbl_index).rltd_adj_index:=line_index;
2100 aso_debug_pub.add('Entered in Dis l_line_adj_assoc_tbl(assoc_tbl_index).adj_index, rltd_adj_index ' || l_line_adj_assoc_tbl(assoc_tbl_index).adj_index || l_line_adj_assoc_tbl(assoc_tbl_index).rltd_adj_index);
2101 assoc_tbl_index:=assoc_tbl_index+1;
2102 END IF;
2103 end loop;
2104
2105 FOR i IN 1 .. l_line_adj_assoc_tbl.count
2106 LOOP
2107
2108 aso_debug_pub.add('bug 12800776 after mapping vidya i value ' || i || 'l_line_adj_assoc_tbl(i).Adj_index: ' || l_line_adj_assoc_tbl(i).adj_index
2109 || 'l_line_adj_assoc_tbl(I).Rltd_Adj_index ' || l_line_adj_assoc_tbl(i).rltd_adj_index );
2110 END LOOP;
2111
2112 --Bug 12800776 ends
2113
2114 /*** Start : Code change done for Bug 14358079 ***/
2115
2116 P := FND_GLOBAL.APPLICATION_SHORT_NAME;
2117
2118 If aso_debug_pub.g_debug_flag = 'Y' Then
2119 aso_debug_pub.add('Create_Order: Before OE_Order_GRP.Process_Order , Product : ' || P ,1,'N');
2120 aso_debug_pub.add('Create_Order: Before OE_Order_GRP.Process_Order , P_Qte_Rec.QUOTE_HEADER_ID : '||P_Qte_Rec.QUOTE_HEADER_ID ,1,'N');
2121 aso_debug_pub.add('Create_Order: Before OE_Order_GRP.Process_Order , P_Qte_Rec.QUOTE_STATUS_ID : '||P_Qte_Rec.QUOTE_STATUS_ID ,1,'N');
2122 End If;
2123
2124 If UPPER(P) IN ('QOT','ASO','ASF','ASL','AST','ASN','ONT') Then
2125
2126 BEGIN
2127 Open C_LOCK(P_Qte_Rec.QUOTE_HEADER_ID,P_Qte_Rec.QUOTE_STATUS_ID);
2128 Fetch C_LOCK Into L_QUOTE_HEADER_ID, L_QUOTE_STATUS_ID;
2129
2130 If aso_debug_pub.g_debug_flag = 'Y' Then
2131 aso_debug_pub.add('Create_Order: Before OE_Order_GRP.Process_Order , L_QUOTE_HEADER_ID : '||L_QUOTE_HEADER_ID ,1,'N');
2132 aso_debug_pub.add('Create_Order: Before OE_Order_GRP.Process_Order , L_QUOTE_STATUS_ID : '||L_QUOTE_STATUS_ID ,1,'N');
2133 End If;
2134
2135 If C_LOCK%NOTFOUND Then
2136 If aso_debug_pub.g_debug_flag = 'Y' Then
2137 aso_debug_pub.add('Create_Order: Before OE_Order_GRP.Process_Order , Cursor C_LOCK NOT FOUND ' ,1,'N');
2138 End If;
2139 End if;
2140
2141 Close C_LOCK;
2142
2143 EXCEPTION
2144
2145 WHEN OTHERS THEN
2146
2147 If aso_debug_pub.g_debug_flag = 'Y' Then
2148 aso_debug_pub.add('Create_Order: When Others exception occures , ASO_API_QUOTE_ORDER_PROGRESS');
2149 aso_debug_pub.add('SQLCODE : '|| SQLCODE, 1, 'N');
2150 aso_debug_pub.add('SQLERRM : '|| SQLERRM, 1, 'N');
2151 End If;
2152
2153 If FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) Then
2154 FND_MESSAGE.SET_NAME('ASO', 'ASO_API_QUOTE_ORDER_PROGRESS');
2155 FND_MSG_PUB.ADD;
2156 END If;
2157 RAISE FND_API.G_EXC_ERROR;
2158 END;
2159
2160 End If;
2161
2162 IF aso_debug_pub.g_debug_flag = 'Y' Then
2163 aso_debug_pub.add('Create_Order: Before OE_Order_GRP.Process_Order',1,'N');
2164 END If;
2165
2166 /*** End : Code change done for Bug 14358079 ***/
2167
2168 OE_Order_GRP.Process_Order
2169 ( p_api_version_number => 1.0
2170 , p_init_msg_list => FND_API.G_TRUE
2171 , p_return_values => l_return_values
2172 , p_commit => FND_API.G_FALSE
2173 , x_return_status => x_return_status
2174 , x_msg_count => x_msg_count
2175 , x_msg_data => x_msg_data
2176 , p_header_rec => l_header_rec
2177 , p_Header_Adj_tbl => l_header_adj_tbl
2178 , p_Header_price_Att_tbl => l_header_price_att_tbl
2179 , p_Header_Adj_Att_tbl => l_header_adj_att_tbl
2180 , p_Header_Adj_Assoc_tbl => l_header_adj_assoc_tbl
2181 , p_Header_Scredit_tbl => l_header_scredit_tbl
2182 , p_Header_Payment_tbl => l_header_payment_tbl
2183 , p_line_tbl => l_line_tbl
2184 , p_Line_Adj_tbl => l_line_adj_tbl
2185 , p_Line_price_Att_tbl => l_line_price_att_tbl
2186 , p_Line_Adj_Att_tbl => l_Line_Adj_Att_tbl
2187 , p_Line_Adj_Assoc_tbl => l_line_adj_assoc_tbl
2188 , p_Line_Scredit_tbl => l_line_scredit_tbl
2189 , p_Lot_Serial_tbl => l_lot_serial_tbl
2190 , p_Line_Payment_tbl => l_line_payment_tbl
2191 , p_Action_Request_tbl => l_request_tbl
2192 , x_header_rec => lx_header_rec
2193 , x_header_val_rec => l_header_val_rec
2194 , x_Header_Adj_tbl => lx_header_adj_tbl
2195 , x_Header_Adj_val_tbl => l_header_adj_val_tbl
2196 , x_Header_price_Att_tbl => lx_header_price_att_tbl
2197 , x_Header_Adj_Att_tbl => lx_header_adj_att_tbl
2198 , x_Header_Adj_Assoc_tbl => lx_header_adj_assoc_tbl
2199 , x_Header_Scredit_tbl => lx_header_scredit_tbl
2200 , x_Header_Scredit_val_tbl => l_header_scredit_val_tbl
2201 , x_Header_Payment_tbl => lx_header_payment_tbl
2202 , x_Header_Payment_val_tbl => l_header_payment_val_tbl
2203 , x_line_tbl => lx_line_tbl
2204 , x_line_val_tbl => l_line_val_tbl
2205 , x_Line_Adj_tbl => lx_line_adj_tbl
2206 , x_Line_Adj_val_tbl => l_line_adj_val_tbl
2207 , x_Line_price_Att_tbl => lx_line_price_att_tbl
2208 , x_Line_Adj_Att_tbl => lx_line_adj_att_tbl
2209 , x_Line_Adj_Assoc_tbl => lx_line_adj_assoc_tbl
2210 , x_Line_Scredit_tbl => lx_line_scredit_tbl
2211 , x_Line_Scredit_val_tbl => l_line_scredit_val_tbl
2212 , x_Lot_Serial_tbl => lx_lot_serial_tbl
2213 , x_Lot_Serial_val_tbl => l_lot_serial_val_tbl
2214 , x_Line_Payment_tbl => lx_line_payment_tbl
2215 , x_Line_Payment_val_tbl => l_line_payment_val_tbl
2216 , x_action_request_tbl => l_action_request_tbl
2217 );
2218
2219 -- hyang: bug 2692785
2220 l_header_rec := lx_header_rec;
2221 l_Header_Adj_tbl := lx_Header_Adj_tbl;
2222 l_Header_price_Att_tbl := lx_Header_price_Att_tbl;
2223 l_Header_Adj_Att_tbl := lx_Header_Adj_Att_tbl;
2224 l_Header_Adj_Assoc_tbl := lx_Header_Adj_Assoc_tbl;
2225 l_Header_Scredit_tbl := lx_Header_Scredit_tbl;
2226 l_line_tbl := lx_line_tbl;
2227 l_Line_Adj_tbl := lx_Line_Adj_tbl;
2228 l_Line_price_Att_tbl := lx_Line_price_Att_tbl;
2229 l_Line_Adj_Att_tbl := lx_Line_Adj_Att_tbl;
2230 l_Line_Adj_Assoc_tbl := lx_Line_Adj_Assoc_tbl;
2231 l_Line_Scredit_tbl := lx_Line_Scredit_tbl;
2232 l_Lot_Serial_tbl := lx_Lot_Serial_tbl;
2233
2234
2235 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2236 aso_debug_pub.add('Create_Order: After Proc_Order: x_return_status: '||x_return_status, 1, 'N');
2237 aso_utility_pvt.print_login_info();
2238 END IF;
2239
2240 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2241 -- retrieve_oe_messages;
2242 IF x_msg_count > 0 THEN
2243 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2244 FND_MESSAGE.Set_Name('ASO', 'ASO_OM_ERROR_MSG');
2245 FND_MSG_PUB.ADD;
2246 END IF;
2247 END IF;
2248
2249 retrieve_oe_messages;
2250
2251 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2252 raise FND_API.G_EXC_ERROR;
2253 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2254 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2255 END IF;
2256
2257 END IF;
2258
2259
2260 IF (x_return_status = FND_API.G_RET_STS_SUCCESS AND
2261 p_control_rec.book_flag = FND_API.G_TRUE AND
2262 l_action_request_tbl(1).return_status <> FND_API.G_RET_STS_SUCCESS)
2263 THEN
2264 if x_msg_count > 0 then
2265 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2266 FND_MESSAGE.Set_Name('ASO', 'ASO_OM_ERROR_MSG');
2267 FND_MSG_PUB.ADD;
2268 END IF;
2269 end if;
2270 retrieve_oe_messages;
2271 x_return_status := FND_API.G_RET_STS_ERROR;
2272 RAISE FND_API.G_EXC_ERROR;
2273 END IF;
2274
2275 retrieve_oe_messages;
2276
2277 -- return the header values
2278 x_order_header_rec.order_number := l_header_rec.order_number;
2279 x_order_header_rec.order_header_id := l_header_rec.header_id;
2280 x_order_header_rec.status := l_header_rec.return_status;
2281 x_order_header_rec.quote_header_id := l_header_rec.source_document_id;
2282
2283 -- return line values
2284 FOR i in 1..l_line_tbl.count LOOP
2285 x_order_line_tbl(i).order_line_id := l_line_tbl(i).line_id;
2286 x_order_line_tbl(i).order_header_id := l_line_tbl(i).header_id;
2287 x_order_line_tbl(i).quote_shipment_line_id := l_line_tbl(i).source_document_line_id;
2288 x_order_line_tbl(i).status := l_line_tbl(i).return_status;
2289 END LOOP;
2290
2291 -- map fulfillment
2292
2293 -- check if the order can be satisfied by fulfillment alone. If yes, then call
2294 -- to OM can be avoided.
2295 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2296 aso_debug_pub.add('Create_Order: Before fulfilment: ', 1, 'N');
2297 END IF;
2298 IF p_control_rec.INTERFACE_FFM_FLAG = FND_API.G_TRUE
2299 AND x_return_status = FND_API.G_RET_STS_SUCCESS THEN
2300 p_ffm_request_rec.party_id := p_qte_rec.party_id;
2301 p_ffm_request_rec.user_id := FND_GLOBAL.USER_ID;
2302 p_ffm_request_rec.server_id := p_control_rec.server_id;
2303
2304
2305 Map_quote_to_fulfillment(
2306 p_qte_line_tbl => p_qte_line_tbl,
2307 p_line_attribs_ext_tbl => p_line_attribs_ext_tbl,
2308 -- p_fulfillment_tbl => fulfillment,
2309 x_ffm_content_tbl => l_ffm_content_tbl,
2310 x_ffm_bind_tbl => l_ffm_bind_tbl
2311 );
2312
2313 IF l_ffm_content_tbl.count > 0 THEN
2314
2315 ASO_FFM_INT.Submit_FFM_Request(
2316 P_Api_Version_Number => 1.0 ,
2317 p_Init_Msg_List => p_init_msg_list ,
2318 p_Commit => FND_API.G_FALSE ,
2319 p_validation_Level => FND_API.G_VALID_LEVEL_FULL,
2320 p_ffm_request_rec => p_ffm_request_rec,
2321 p_ffm_content_tbl => l_ffm_content_tbl,
2322 p_bind_tbl => l_ffm_bind_tbl ,
2323 X_Request_ID => x_request_id ,
2324 X_Return_Status => x_return_status,
2325 X_Msg_Count => x_msg_count ,
2326 X_Msg_Data => x_msg_data );
2327
2328 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2329 aso_debug_pub.add('Create_Order: After submit_ffm_request x_return_status: '||x_return_status, 1, 'N');
2330 END IF;
2331 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2332 --retrieve_oe_messages;
2333 RAISE FND_API.G_EXC_ERROR;
2334 END IF;
2335 END IF;
2336
2337 END IF;
2338
2339
2340 --
2341 -- End of API body
2342 --
2343 -- Standard check for p_commit
2344 IF FND_API.to_Boolean( p_commit )
2345 THEN
2346 COMMIT WORK;
2347 END IF;
2348
2349
2350
2351 -- Standard call to get message count and if count is 1, get message info.
2352 FND_MSG_PUB.Count_And_Get
2353 ( p_count => x_msg_count,
2354 p_data => x_msg_data
2355 );
2356
2357 EXCEPTION
2358 WHEN FND_API.G_EXC_ERROR THEN
2359 null;
2360 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
2361 P_API_NAME => L_API_NAME
2362 ,P_PKG_NAME => G_PKG_NAME
2363 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
2364 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
2365 ,X_MSG_COUNT => X_MSG_COUNT
2366 ,X_MSG_DATA => X_MSG_DATA
2367 ,X_RETURN_STATUS => X_RETURN_STATUS);
2368
2369 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2370 null;
2371 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
2372 P_API_NAME => L_API_NAME
2373 ,P_PKG_NAME => G_PKG_NAME
2374 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
2375 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
2376 ,X_MSG_COUNT => X_MSG_COUNT
2377 ,X_MSG_DATA => X_MSG_DATA
2378 ,X_RETURN_STATUS => X_RETURN_STATUS);
2379 WHEN OTHERS THEN
2380 null;
2381 /*** Start : Code change done for Bug 14358079 ***/
2382 IF SQLCODE = -54 THEN
2383 FND_MESSAGE.SET_NAME('ASO', 'ASO_API_QUOTE_ORDER_PROGRESS');
2384 FND_MSG_PUB.ADD;
2385 RAISE FND_API.G_EXC_ERROR;
2386 -- X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
2387 END IF;
2388 /*** End : Code change done for Bug 14358079 ***/
2389
2390 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
2391 P_API_NAME => L_API_NAME
2392 ,P_PKG_NAME => G_PKG_NAME
2393 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
2394 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
2395 ,X_MSG_COUNT => X_MSG_COUNT
2396 ,X_MSG_DATA => X_MSG_DATA
2397 ,X_RETURN_STATUS => X_RETURN_STATUS);
2398
2399 END Create_Order;
2400
2401
2402
2403 PROCEDURE Update_order(
2404 P_Api_Version IN NUMBER,
2405 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
2406 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
2407 P_Qte_Rec IN ASO_QUOTE_PUB.Qte_Header_Rec_Type
2408 := ASO_QUOTE_PUB.G_MISS_QTE_HEADER_REC,
2409 P_Header_Payment_Tbl IN ASO_QUOTE_PUB.Payment_Tbl_Type
2410 := ASO_QUOTE_PUB.G_MISS_Payment_TBL,
2411 P_Header_Price_Adj_Tbl IN ASO_QUOTE_PUB.Price_Adj_Tbl_Type
2412 := ASO_QUOTE_PUB.G_MISS_Price_Adj_TBL,
2413 P_Header_Price_Attributes_Tbl IN ASO_QUOTE_PUB.Price_Attributes_Tbl_Type
2414 := ASO_QUOTE_PUB.G_MISS_Price_attributes_TBL,
2415 P_Header_Price_Adj_rltship_Tbl IN ASO_QUOTE_PUB.Price_Adj_Rltship_Tbl_Type
2416 := ASO_QUOTE_PUB.G_MISS_price_adj_rltship_TBL,
2417 P_Header_Price_Adj_Attr_Tbl IN ASO_QUOTE_PUB.Price_Adj_Attr_Tbl_Type
2418 := ASO_QUOTE_PUB.G_Miss_PRICE_ADJ_ATTR_Tbl,
2419 P_Header_Shipment_Tbl IN ASO_QUOTE_PUB.Shipment_Tbl_Type
2420 := ASO_QUOTE_PUB.G_MISS_shipment_TBL,
2421 P_Header_TAX_DETAIL_Tbl IN ASO_QUOTE_PUB.TAX_DETAIL_Tbl_Type
2422 := ASO_QUOTE_PUB.G_MISS_TAX_DETAIL_TBL,
2423 P_Header_FREIGHT_CHARGE_Tbl IN ASO_QUOTE_PUB.FREIGHT_CHARGE_Tbl_Type
2424 := ASO_QUOTE_PUB.G_MISS_FREIGHT_CHARGE_Tbl,
2425 P_Header_ATTRIBS_EXT_Tbl IN ASO_QUOTE_PUB.Line_Attribs_Ext_Tbl_Type
2426 := ASO_QUOTE_PUB.G_MISS_Line_Attribs_Ext_TBL,
2427 P_Header_Quote_Party_Tbl IN ASO_QUOTE_PUB.Quote_Party_Tbl_Type
2428 := ASO_QUOTE_PUB.G_MISS_Quote_Party_Tbl,
2429 P_header_sales_credit_TBL IN ASO_QUOTE_PUB.Sales_credit_tbl_type
2430 := ASO_QUOTE_PUB.G_MISS_sales_credit_TBL,
2431 P_Qte_Line_Tbl IN ASO_QUOTE_PUB.Qte_Line_Tbl_Type
2432 := ASO_QUOTE_PUB.G_MISS_qte_line_TBL,
2433 P_Qte_Line_Dtl_tbl IN ASO_QUOTE_PUB.Qte_Line_Dtl_tbl_Type
2434 := ASO_QUOTE_PUB.G_MISS_qte_line_dtl_TBL,
2435 P_Line_Payment_Tbl IN ASO_QUOTE_PUB.Payment_Tbl_Type
2436 := ASO_QUOTE_PUB.G_MISS_Payment_TBL,
2437 P_Line_Price_Adj_Tbl IN ASO_QUOTE_PUB.Price_Adj_Tbl_Type
2438 := ASO_QUOTE_PUB.G_MISS_Price_Adj_TBL,
2439 P_Line_Price_Attributes_Tbl IN ASO_QUOTE_PUB.Price_Attributes_Tbl_Type
2440 := ASO_QUOTE_PUB.G_MISS_Price_attributes_TBL,
2441 P_Line_Price_Adj_rltship_Tbl IN ASO_QUOTE_PUB.Price_Adj_Rltship_Tbl_Type
2442 := ASO_QUOTE_PUB.G_MISS_price_adj_rltship_TBL,
2443 P_Line_Price_Adj_Attr_Tbl IN ASO_QUOTE_PUB.Price_Adj_Attr_Tbl_Type
2444 := ASO_QUOTE_PUB.G_Miss_PRICE_ADJ_ATTR_Tbl,
2445 P_Line_Shipment_Tbl IN ASO_QUOTE_PUB.Shipment_Tbl_Type
2446 := ASO_QUOTE_PUB.G_MISS_shipment_TBL,
2447 P_Line_TAX_DETAIL_Tbl IN ASO_QUOTE_PUB.TAX_DETAIL_Tbl_Type
2448 := ASO_QUOTE_PUB.G_MISS_TAX_DETAIL_TBL,
2449 P_Line_FREIGHT_CHARGE_Tbl IN ASO_QUOTE_PUB.FREIGHT_CHARGE_Tbl_Type := ASO_QUOTE_PUB.G_MISS_FREIGHT_CHARGE_Tbl,
2450 P_Line_ATTRIBS_EXT_Tbl IN ASO_QUOTE_PUB.Line_Attribs_Ext_Tbl_Type
2451 := ASO_QUOTE_PUB.G_MISS_Line_Attribs_Ext_TBL,
2452 P_Line_Rltship_Tbl IN ASO_QUOTE_PUB.Line_Rltship_Tbl_Type
2453 := ASO_QUOTE_PUB.G_MISS_line_rltship_TBL,
2454 P_Line_sales_credit_TBL IN ASO_QUOTE_PUB.Sales_credit_tbl_type
2455 := ASO_QUOTE_PUB.G_MISS_sales_credit_TBL,
2456 P_Line_Quote_Party_Tbl IN ASO_QUOTE_PUB.Quote_Party_Tbl_Type
2457 := ASO_QUOTE_PUB.G_MISS_Quote_Party_Tbl,
2458 P_Lot_Serial_Tbl IN ASO_QUOTE_PUB.Lot_Serial_Tbl_Type
2459 := ASO_QUOTE_PUB.G_MISS_Lot_Serial_Tbl,
2460 P_Control_Rec IN Control_Rec_Type := G_MISS_Control_Rec,
2461 X_Order_Header_Rec OUT NOCOPY /* file.sql.39 change */ Order_Header_Rec_Type,
2462 X_Order_Line_Tbl OUT NOCOPY /* file.sql.39 change */ Order_Line_Tbl_type,
2463 X_Return_Status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
2464 X_Msg_Count OUT NOCOPY /* file.sql.39 change */ NUMBER,
2465 X_Msg_Data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
2466 )
2467 IS
2468 -- this should be changed post 11.5.1
2469 CURSOR C_cancel_reason IS
2470 SELECT lookup_code
2471 from oe_lookups
2472 WHERE lookup_type = 'CANCEL_CODE'
2473 AND lookup_code = 'Not provided';
2474
2475
2476 l_line_shipment_tbl ASO_QUOTE_PUB.Shipment_Tbl_Type;
2477 l_api_version_number CONSTANT NUMBER := 1.0;
2478 l_api_name CONSTANT VARCHAR2(30):= 'Update_Order';
2479 l_control_rec OE_GLOBALS.Control_Rec_Type;
2480 l_return_status VARCHAR2(1);
2481 l_header_rec OE_Order_PUB.Header_Rec_Type;
2482 l_Header_Adj_tbl OE_Order_PUB.Header_Adj_Tbl_Type;
2483 l_Header_price_Att_tbl OE_Order_PUB.Header_Price_Att_Tbl_Type ;
2484 l_Header_Adj_Att_tbl OE_Order_PUB.Header_Adj_Att_Tbl_Type ;
2485 l_Header_Adj_Assoc_tbl OE_Order_PUB.Header_Adj_Assoc_Tbl_Type ;
2486 l_Header_Scredit_tbl OE_Order_PUB.Header_Scredit_Tbl_Type;
2487 l_Header_Payment_tbl OE_Order_PUB.Header_Payment_Tbl_Type;
2488 l_Header_Payment_val_tbl OE_Order_PUB.Header_Payment_Val_Tbl_Type;
2489 l_line_tbl OE_Order_PUB.Line_Tbl_Type;
2490 l_Line_Adj_tbl OE_Order_PUB.Line_Adj_Tbl_Type;
2491 l_Line_price_Att_tbl OE_Order_PUB.Line_Price_Att_Tbl_Type ;
2492 l_Line_Adj_Att_tbl OE_Order_PUB.Line_Adj_Att_Tbl_Type ;
2493 l_Line_Adj_Assoc_tbl OE_Order_PUB.Line_Adj_Assoc_Tbl_Type ;
2494 l_Line_Scredit_tbl OE_Order_PUB.Line_Scredit_Tbl_Type;
2495 l_Line_Payment_tbl OE_Order_PUB.Line_Payment_Tbl_Type;
2496 l_Line_Payment_val_tbl OE_Order_PUB.Line_Payment_Val_Tbl_Type;
2497 l_Lot_Serial_tbl OE_Order_PUB.Lot_Serial_Tbl_Type;
2498 l_old_header_rec OE_Order_PUB.Header_Rec_Type;
2499 l_old_Header_Adj_tbl OE_Order_PUB.Header_Adj_Tbl_Type;
2500 l_old_Header_price_Att_tbl OE_Order_PUB.Header_Price_Att_Tbl_Type ;
2501 l_old_Header_Adj_Att_tbl OE_Order_PUB.Header_Adj_Att_Tbl_Type ;
2502 l_old_Header_Adj_Assoc_tbl OE_Order_PUB.Header_Adj_Assoc_Tbl_Type ;
2503 l_old_Header_Scredit_tbl OE_Order_PUB.Header_Scredit_Tbl_Type;
2504 l_old_line_tbl OE_Order_PUB.Line_Tbl_Type;
2505 l_old_Line_Adj_tbl OE_Order_PUB.Line_Adj_Tbl_Type;
2506 l_old_Line_price_Att_tbl OE_Order_PUB.Line_Price_Att_Tbl_Type ;
2507 l_old_Line_Adj_Att_tbl OE_Order_PUB.Line_Adj_Att_Tbl_Type ;
2508 l_old_Line_Adj_Assoc_tbl OE_Order_PUB.Line_Adj_Assoc_Tbl_Type ;
2509 l_old_Line_Scredit_tbl OE_Order_PUB.Line_Scredit_Tbl_Type;
2510 l_old_Lot_Serial_tbl OE_Order_PUB.Lot_Serial_Tbl_Type;
2511 l_request_tbl OE_Order_PUB.Request_Tbl_Type :=
2512 OE_Order_PUB.G_MISS_REQUEST_TBL;
2513
2514 l_return_values varchar2(50);
2515 l_header_val_rec OE_Order_PUB.Header_Val_Rec_Type;
2516 l_old_header_val_rec OE_Order_PUB.Header_Val_Rec_Type;
2517 l_header_adj_val_tbl OE_Order_PUB.Header_Adj_Val_Tbl_Type;
2518 l_old_header_adj_val_tbl OE_Order_PUB.Header_Adj_Val_Tbl_Type;
2519 l_header_scredit_val_tbl OE_Order_PUB.Header_Scredit_Val_Tbl_Type;
2520 l_old_header_scredit_val_tbl OE_Order_PUB.Header_Scredit_Val_Tbl_Type;
2521 l_line_val_tbl OE_Order_PUB.Line_Val_Tbl_Type;
2522 l_old_line_val_tbl OE_Order_PUB.Line_Val_Tbl_Type;
2523 l_line_adj_val_tbl OE_Order_PUB.Line_Adj_Val_Tbl_Type;
2524 l_old_line_adj_val_tbl OE_Order_PUB.Line_Adj_Val_Tbl_Type;
2525 l_line_scredit_val_tbl OE_Order_PUB.Line_Scredit_Val_Tbl_Type;
2526 l_old_line_scredit_val_tbl OE_Order_PUB.Line_Scredit_Val_Tbl_Type;
2527 l_lot_serial_val_tbl OE_Order_PUB.Lot_Serial_Val_Tbl_Type;
2528 l_old_lot_serial_val_tbl OE_Order_PUB.Lot_Serial_Val_Tbl_Type;
2529
2530 -- hyang: bug 2692785
2531 lx_header_rec OE_Order_PUB.Header_Rec_Type;
2532 lx_Header_Adj_tbl OE_Order_PUB.Header_Adj_Tbl_Type;
2533 lx_Header_price_Att_tbl OE_Order_PUB.Header_Price_Att_Tbl_Type ;
2534 lx_Header_Adj_Att_tbl OE_Order_PUB.Header_Adj_Att_Tbl_Type ;
2535 lx_Header_Adj_Assoc_tbl OE_Order_PUB.Header_Adj_Assoc_Tbl_Type ;
2536 lx_Header_Scredit_tbl OE_Order_PUB.Header_Scredit_Tbl_Type;
2537 lx_Header_Payment_tbl OE_Order_PUB.Header_Payment_Tbl_Type;
2538 lx_line_tbl OE_Order_PUB.Line_Tbl_Type;
2539 lx_Line_Adj_tbl OE_Order_PUB.Line_Adj_Tbl_Type;
2540 lx_Line_price_Att_tbl OE_Order_PUB.Line_Price_Att_Tbl_Type ;
2541 lx_Line_Adj_Att_tbl OE_Order_PUB.Line_Adj_Att_Tbl_Type ;
2542 lx_Line_Adj_Assoc_tbl OE_Order_PUB.Line_Adj_Assoc_Tbl_Type ;
2543 lx_Line_Scredit_tbl OE_Order_PUB.Line_Scredit_Tbl_Type;
2544 lx_Lot_Serial_tbl OE_Order_PUB.Lot_Serial_Tbl_Type;
2545 lx_Line_Payment_tbl OE_Order_PUB.Line_Payment_Tbl_Type;
2546 lx_request_tbl OE_Order_PUB.Request_Tbl_Type;
2547
2548 l_split_pay_prof VARCHAR2(2) := FND_PROFILE.Value('ASO_ENABLE_SPLIT_PAYMENT');
2549 l_CC_Auth_Prof VARCHAR2(2) := FND_PROFILE.Value('ASO_CC_AUTHORIZATION_ENABLED');
2550 l_Enable_Risk_Mgmt_Prof VARCHAR2(2);
2551 l_CC_Auth_Failure_Prof VARCHAR2(20);
2552 l_Risk_Mgmt_Failure_Prof VARCHAR2(20);
2553
2554 i NUMBER := 1;
2555 BEGIN
2556 -- Standard Start of API savepoint
2557 SAVEPOINT UPDATE_order_PVT;
2558
2559 aso_debug_pub.g_debug_flag := nvl(fnd_profile.value('ASO_ENABLE_DEBUG'),'N');
2560
2561 -- Standard call to check for call compatibility.
2562 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
2563 p_api_version ,
2564 l_api_name,
2565 G_PKG_NAME)
2566 THEN
2567 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2568 END IF;
2569
2570
2571 -- Initialize message list if p_init_msg_list is set to TRUE.
2572 IF FND_API.to_Boolean( p_init_msg_list )
2573 THEN
2574 FND_MSG_PUB.initialize;
2575 END IF;
2576
2577 -- Debug Message
2578 ASO_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: ' || l_api_name || 'start');
2579
2580 -- Initialize API return status to SUCCESS
2581 x_return_status := FND_API.G_RET_STS_SUCCESS;
2582
2583 --
2584 -- API body
2585 --
2586
2587 -- ******************************************************************
2588 -- Validate Environment
2589 -- ******************************************************************
2590 IF FND_GLOBAL.User_Id IS NULL
2591 THEN
2592 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2593 THEN
2594 FND_MESSAGE.Set_Name(' + appShortName +', 'UT_CANNOT_GET_PROFILE_VALUE');
2595 FND_MESSAGE.Set_Token('PROFILE', 'USER_ID', FALSE);
2596 FND_MSG_PUB.ADD;
2597 END IF;
2598 RAISE FND_API.G_EXC_ERROR;
2599 END IF;
2600
2601
2602 -- change the org to whatever is stored in the org id column
2603 IF p_qte_rec.org_id is not NULL
2604 AND p_qte_rec.org_id <> FND_API.G_MISS_NUM THEN
2605
2606 /* fnd_client_info.set_org_context(p_qte_rec.org_id); */ --Commented Code Yogeshwar (MOAC)
2607 MO_GLOBAL.set_policy_context ('S', p_qte_rec.org_id) ; --New Code Yogeshwar (MOAC)
2608
2609 END IF;
2610
2611 IF p_qte_rec.order_id is null OR
2612 p_qte_rec.order_id = FND_API.G_MISS_NUM THEN
2613
2614 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2615 THEN
2616 FND_MESSAGE.Set_Name('ASO', 'ASO_API_MISSING_INFO');
2617 FND_MESSAGE.Set_Token('COLUMN', 'ORDER_ID', FALSE);
2618 FND_MSG_PUB.ADD;
2619 END IF;
2620 raise FND_API.G_EXC_ERROR;
2621 END IF;
2622
2623 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2624 aso_debug_pub.add('l_split_pay_prof: '||l_split_pay_prof,1,'N');
2625 END IF;
2626
2627 IF (l_split_pay_prof = 'N') THEN
2628 IF p_header_payment_tbl.count > 1 THEN
2629 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2630 FND_MESSAGE.Set_Name('ASO', 'ASO_API_SPLIT_PAYMENT');
2631 FND_MSG_PUB.ADD;
2632 END IF;
2633 RAISE FND_API.G_EXC_ERROR;
2634 ELSIF p_header_payment_tbl.count = 1 THEN
2635 IF p_header_payment_tbl(1).payment_option = 'SPLIT' THEN
2636 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2637 FND_MESSAGE.Set_Name('ASO', 'ASO_API_SPLIT_PAYMENT');
2638 FND_MSG_PUB.ADD;
2639 END IF;
2640 RAISE FND_API.G_EXC_ERROR;
2641 END IF;
2642
2643 END IF;-- p_hd_payment
2644
2645 END IF;-- FND_PROFILE.Value
2646 IF (l_split_pay_prof = 'Y') THEN
2647 IF p_header_payment_tbl.count > 1 THEN
2648 FOR i IN 1..p_header_payment_tbl.count LOOP
2649 IF p_header_payment_tbl(i).payment_option <> 'SPLIT' THEN
2650 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2651 FND_MESSAGE.Set_Name('ASO', 'ASO_API_TOO_MANY_PAYMENTS');
2652 FND_MSG_PUB.ADD;
2653 END IF;
2654 RAISE FND_API.G_EXC_ERROR;
2655 END IF;
2656 END LOOP;
2657 END IF;
2658 END IF;
2659
2660 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2661 aso_debug_pub.add('before ASO_MAP_QUOTE_ORDER_INT.Map_Quote_Order_Int: ',1,'N');
2662 aso_debug_pub.add('before ASO_MAP_QUOTE_ORDER_INT.Map_Quote_Order_Int: P_Calculate_Price_Flag '||P_Control_Rec.Calculate_Price,1,'N');
2663 END IF;
2664
2665 ASO_MAP_QUOTE_ORDER_INT.Map_Quote_to_order(
2666 p_operation => 'UPDATE' ,
2667 P_Qte_Rec => P_Qte_Rec ,
2668 P_Header_Payment_Tbl => P_Header_Payment_Tbl ,
2669 P_Header_Price_Adj_Tbl => P_Header_Price_Adj_Tbl ,
2670 P_Header_Price_Attributes_Tbl => P_Header_Price_Attributes_Tbl ,
2671 P_Header_Price_Adj_rltship_Tbl=> P_Header_Price_Adj_rltship_Tbl ,
2672 P_Header_Price_Adj_Attr_Tbl => P_Header_Price_Adj_Attr_Tbl ,
2673 P_Header_Shipment_Tbl => P_Header_Shipment_Tbl ,
2674 P_Header_TAX_DETAIL_Tbl => P_Header_TAX_DETAIL_Tbl ,
2675 P_Header_FREIGHT_CHARGE_Tbl => P_Header_FREIGHT_CHARGE_Tbl ,
2676 P_header_sales_credit_TBL => P_header_sales_credit_TBL ,
2677 P_Qte_Line_Tbl => P_Qte_Line_Tbl ,
2678 P_Qte_Line_Dtl_Tbl => P_Qte_Line_Dtl_Tbl ,
2679 P_Line_Payment_Tbl => P_Line_Payment_Tbl ,
2680 P_Line_Price_Adj_Tbl => P_Line_Price_Adj_Tbl ,
2681 P_Line_Price_Attributes_Tbl => P_Line_Price_Attributes_Tbl ,
2682 P_Line_Price_Adj_rltship_Tbl => P_Line_Price_Adj_rltship_Tbl ,
2683 P_Line_Price_Adj_Attr_Tbl => P_Line_Price_Adj_Attr_Tbl ,
2684 P_Line_Shipment_Tbl => P_Line_Shipment_Tbl ,
2685 P_Line_TAX_DETAIL_Tbl => P_Line_TAX_DETAIL_Tbl ,
2686 P_Line_FREIGHT_CHARGE_Tbl => P_Line_FREIGHT_CHARGE_Tbl ,
2687 P_Line_Rltship_Tbl => P_Line_Rltship_Tbl ,
2688 P_Line_sales_credit_TBL => P_Line_sales_credit_TBL ,
2689 P_Lot_serial_TBL => P_Lot_serial_TBL ,
2690 P_Calculate_Price_Flag => P_Control_Rec.Calculate_Price, -- bug 13010966
2691 x_header_rec => l_header_rec ,
2692 x_header_val_rec => l_header_val_rec ,
2693 x_header_Adj_tbl => l_header_Adj_tbl ,
2694 x_header_Adj_val_tbl => l_header_Adj_val_tbl ,
2695 x_header_price_Att_tbl => l_header_price_Att_tbl ,
2696 x_header_Adj_Att_tbl => l_header_Adj_Att_tbl ,
2697 x_header_Adj_Assoc_tbl => l_header_Adj_Assoc_tbl ,
2698 x_header_Scredit_tbl => l_header_Scredit_tbl ,
2699 x_Header_Scredit_val_tbl => l_Header_Scredit_val_tbl ,
2700 x_Header_Payment_tbl => l_Header_Payment_tbl ,
2701 x_line_tbl => l_line_tbl ,
2702 x_line_val_tbl => l_line_val_tbl ,
2703 x_line_Adj_tbl => l_line_Adj_tbl ,
2704 x_line_Adj_val_tbl => l_line_Adj_val_tbl ,
2705 x_line_price_Att_tbl => l_line_price_Att_tbl ,
2706 x_line_Adj_Att_tbl => l_line_Adj_Att_tbl ,
2707 x_line_Adj_Assoc_tbl => l_line_Adj_Assoc_tbl ,
2708 x_line_Scredit_tbl => l_line_Scredit_tbl ,
2709 x_line_Scredit_val_tbl => l_line_Scredit_val_tbl ,
2710 x_lot_Serial_tbl => l_lot_Serial_tbl ,
2711 X_Lot_Serial_val_tbl => l_Lot_Serial_val_tbl ,
2712 x_Line_Payment_tbl => l_Line_Payment_tbl
2713 );
2714
2715 l_header_rec.operation := OE_Globals.G_OPR_UPDATE;
2716
2717 -- set the control record flags
2718 -- book order
2719
2720 IF p_control_rec.book_flag = FND_API.G_TRUE THEN
2721 l_request_tbl(i).entity_code := OE_GLOBALS.G_ENTITY_HEADER;
2722 l_request_tbl(i).request_type := OE_GLOBALS.G_BOOK_ORDER;
2723 i := i + 1;
2724 END IF; -- booking
2725
2726 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2727 aso_debug_pub.add('Create_Order: l_CC_Auth_Prof: '||l_CC_Auth_Prof, 1, 'N');
2728 END IF;
2729
2730 IF l_CC_Auth_Prof = 'Y' AND P_Control_Rec.CC_By_Fax <> FND_API.G_TRUE THEN
2731
2732 FOR x IN 1..l_Header_Payment_tbl.count LOOP
2733 IF l_Header_Payment_tbl(x).Payment_Type_Code = 'CREDIT_CARD' AND l_Header_Payment_tbl(x).trxn_extension_id IS NOT NULL THEN
2734 --l_cc_auth_failure_prof := NVL(FND_PROFILE.Value('ASO_CC_AUTH_FAILURE'), 'REJECT');
2735 l_enable_risk_mgmt_prof := NVL(FND_PROFILE.Value('ASO_RISK_MANAGE_CC_AUTH'), 'Y');
2736
2737 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2738 aso_debug_pub.add('Create_Order: l_enable_risk_mgmt_prof: '||l_enable_risk_mgmt_prof, 1, 'N');
2739 END IF;
2740
2741 l_request_tbl(i).request_type := OE_GLOBALS.G_VERIFY_PAYMENT;
2742 l_request_tbl(i).entity_code := OE_GLOBALS.G_ENTITY_HEADER;
2743 --l_request_tbl(i).param2 := l_cc_auth_failure_prof;
2744
2745 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2746 aso_debug_pub.add('Create_Order: l_request_tbl(i).param2: '|| l_request_tbl(i).param2, 1, 'N');
2747 END IF;
2748
2749 IF l_enable_risk_mgmt_prof = 'Y' THEN
2750 --l_risk_mgmt_failure_prof := NVL(FND_PROFILE.Value('ASO_RISK_MANAGE_FAILURE'), 'REJECT');
2751 l_request_tbl(i).param1 := 'Y';
2752 --l_request_tbl(i).param3 := l_risk_mgmt_failure_prof;
2753 ELSE
2754 l_request_tbl(i).param1 := 'N';
2755 END IF;
2756 i := i + 1;
2757 END IF; -- l_Header_Payment_tbl
2758 END LOOP; -- l_Header_Payment_tbl
2759
2760 FOR x IN 1..l_Line_Payment_tbl.count LOOP
2761 IF l_Line_Payment_tbl(x).Payment_Type_Code = 'CREDIT_CARD' AND l_Line_Payment_tbl(x).trxn_extension_id IS NOT NULL THEN
2762 --l_cc_auth_failure_prof := NVL(FND_PROFILE.Value('ASO_CC_AUTH_FAILURE'), 'REJECT');
2763 l_enable_risk_mgmt_prof := NVL(FND_PROFILE.Value('ASO_RISK_MANAGE_CC_AUTH'), 'Y');
2764
2765 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2766 aso_debug_pub.add('Create_Order: l_enable_risk_mgmt_prof: '||l_enable_risk_mgmt_prof, 1, 'N');
2767 END IF;
2768
2769 l_request_tbl(i).request_type := OE_GLOBALS.G_VERIFY_PAYMENT;
2770 l_request_tbl(i).entity_code := OE_GLOBALS.G_ENTITY_LINE;
2771 l_request_tbl(i).entity_index := l_Line_Payment_tbl(x).Line_Index;
2772 --l_request_tbl(i).param2 := l_cc_auth_failure_prof;
2773
2774 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2775 aso_debug_pub.add('Create_Order: l_request_tbl(i).param2: '|| l_request_tbl(i).param2, 1, 'N');
2776 END IF;
2777
2778 IF l_enable_risk_mgmt_prof = 'Y' THEN
2779 --l_risk_mgmt_failure_prof := NVL(FND_PROFILE.Value('ASO_RISK_MANAGE_FAILURE'), 'REJECT');
2780 l_request_tbl(i).param1 := 'Y';
2781 --l_request_tbl(i).param3 := l_risk_mgmt_failure_prof;
2782 ELSE
2783 l_request_tbl(i).param1 := 'N';
2784 END IF;
2785 i := i + 1;
2786 END IF; -- l_Line_Payment_tbl
2787 END LOOP; -- l_Line_Payment_tbl
2788
2789 END IF; -- l_CC_Auth_Prof
2790
2791 -- cancel code - this should be changed
2792 FOR i in 1..l_line_tbl.count LOOP
2793 IF l_line_tbl(i).ordered_quantity is not NULL
2794 OR l_line_tbl(i).ordered_quantity <> FND_API.G_MISS_NUM THEN
2795
2796 OPEN C_cancel_reason;
2797 FETCH C_cancel_reason into l_line_tbl(i).change_reason;
2798 CLOSE C_cancel_reason;
2799
2800 END IF;
2801 END LOOP;
2802
2803 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2804 aso_debug_pub.add('Update_Order: Before Process_order',1,'Y');
2805 aso_utility_pvt.print_login_info();
2806 END IF;
2807
2808 -- bug# 1927450
2809 OE_STANDARD_WF.SAVE_MESSAGES_OFF;
2810
2811 OE_Order_GRP.Process_Order
2812 ( p_api_version_number => l_api_version_number
2813 , p_init_msg_list => FND_API.G_TRUE
2814 , p_return_values => l_return_values
2815 , p_commit => p_commit
2816 , x_return_status => x_return_status
2817 , x_msg_count => x_msg_count
2818 , x_msg_data => x_msg_data
2819 , p_header_rec => l_header_rec
2820 --, p_header_val_rec => l_header_val_rec
2821 , p_Header_Adj_tbl => l_header_adj_tbl
2822 --, p_Header_Adj_val_tbl => l_header_adj_val_tbl
2823 , p_Header_price_Att_tbl => l_header_price_att_tbl
2824 , p_Header_Adj_Att_tbl => l_header_adj_att_tbl
2825 , p_Header_Adj_Assoc_tbl => l_header_adj_assoc_tbl
2826 , p_Header_Scredit_tbl => l_header_scredit_tbl
2827 --, p_Header_Scredit_val_tbl => l_header_scredit_val_tbl
2828 , p_Header_Payment_tbl => l_header_payment_tbl
2829 , p_line_tbl => l_line_tbl
2830 --, p_line_val_tbl => l_line_val_tbl
2831 , p_Line_Adj_tbl => l_line_adj_tbl
2832 --, p_Line_Adj_val_tbl => l_line_adj_val_tbl
2833 , p_Line_price_Att_tbl => l_line_price_att_tbl
2834 , p_Line_Adj_Att_tbl => l_Line_Adj_Att_tbl
2835 , p_Line_Adj_Assoc_tbl => l_line_adj_assoc_tbl
2836 , p_Line_Scredit_tbl => l_line_scredit_tbl
2837 --, p_Line_Scredit_val_tbl => l_line_scredit_val_tbl
2838 , p_Lot_Serial_tbl => l_lot_serial_tbl
2839 --, p_Lot_Serial_val_tbl => l_lot_serial_val_tbl
2840 , p_Line_Payment_tbl => l_line_payment_tbl
2841 , P_Action_Request_tbl => l_request_tbl
2842 , x_header_rec => lx_header_rec
2843 , x_header_val_rec => l_header_val_rec
2844 , x_Header_Adj_tbl => lx_header_adj_tbl
2845 , x_Header_Adj_val_tbl => l_header_adj_val_tbl
2846 , x_Header_price_Att_tbl => lx_header_price_att_tbl
2847 , x_Header_Adj_Att_tbl => lx_header_adj_att_tbl
2848 , x_Header_Adj_Assoc_tbl => lx_header_adj_assoc_tbl
2849 , x_Header_Scredit_tbl => lx_header_scredit_tbl
2850 , x_Header_Scredit_val_tbl => l_header_scredit_val_tbl
2851 , x_Header_Payment_tbl => lx_header_payment_tbl
2852 , x_Header_Payment_val_tbl => l_header_payment_val_tbl
2853 , x_line_tbl => lx_line_tbl
2854 , x_line_val_tbl => l_line_val_tbl
2855 , x_Line_Adj_tbl => lx_line_adj_tbl
2856 , x_Line_Adj_val_tbl => l_line_adj_val_tbl
2857 , x_Line_price_Att_tbl => lx_line_price_att_tbl
2858 , x_Line_Adj_Att_tbl => lx_line_adj_att_tbl
2859 , x_Line_Adj_Assoc_tbl => lx_line_adj_assoc_tbl
2860 , x_Line_Scredit_tbl => lx_line_scredit_tbl
2861 , x_Line_Scredit_val_tbl => l_line_scredit_val_tbl
2862 , x_Lot_Serial_tbl => lx_lot_serial_tbl
2863 , x_Lot_Serial_val_tbl => l_lot_serial_val_tbl
2864 , x_Line_Payment_tbl => lx_line_payment_tbl
2865 , x_Line_Payment_val_tbl => l_line_payment_val_tbl
2866 , x_action_request_tbl => lx_request_tbl
2867 );
2868
2869 -- hyang: bug 2692785
2870 l_header_rec := lx_header_rec;
2871 l_Header_Adj_tbl := lx_Header_Adj_tbl;
2872 l_Header_price_Att_tbl := lx_Header_price_Att_tbl;
2873 l_Header_Adj_Att_tbl := lx_Header_Adj_Att_tbl;
2874 l_Header_Adj_Assoc_tbl := lx_Header_Adj_Assoc_tbl;
2875 l_Header_Scredit_tbl := lx_Header_Scredit_tbl;
2876 l_line_tbl := lx_line_tbl;
2877 l_Line_Adj_tbl := lx_Line_Adj_tbl;
2878 l_Line_price_Att_tbl := lx_Line_price_Att_tbl;
2879 l_Line_Adj_Att_tbl := lx_Line_Adj_Att_tbl;
2880 l_Line_Adj_Assoc_tbl := lx_Line_Adj_Assoc_tbl;
2881 l_Line_Scredit_tbl := lx_Line_Scredit_tbl;
2882 l_Lot_Serial_tbl := lx_Lot_Serial_tbl;
2883 l_request_tbl := lx_request_tbl;
2884
2885 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2886 aso_debug_pub.add('Update_Order: After Process_Order: x_return_status'||x_return_status,1,'Y');
2887 aso_utility_pvt.print_login_info();
2888 END IF;
2889
2890 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2891 IF x_msg_count > 0 THEN
2892 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2893 FND_MESSAGE.Set_Name('ASO', 'ASO_OM_ERROR_MSG');
2894 FND_MSG_PUB.ADD;
2895 END IF;
2896 END IF;
2897
2898 retrieve_oe_messages;
2899
2900 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2901 raise FND_API.G_EXC_ERROR;
2902 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2903 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2904 END IF;
2905
2906 END IF;
2907
2908 IF (x_return_status = FND_API.G_RET_STS_SUCCESS AND
2909 p_control_rec.book_flag = FND_API.G_TRUE AND
2910 l_request_tbl(1).return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2911 if x_msg_count > 0 then
2912 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2913 FND_MESSAGE.Set_Name('ASO', 'ASO_OM_ERROR_MSG');
2914 FND_MSG_PUB.ADD;
2915 END IF;
2916 end if;
2917 retrieve_oe_messages;
2918 x_return_status := FND_API.G_RET_STS_ERROR;
2919 RAISE FND_API.G_EXC_ERROR;
2920 END IF;
2921
2922 retrieve_oe_messages;
2923
2924 x_order_header_rec.order_number := l_header_rec.order_number;
2925 x_order_header_rec.order_header_id := l_header_rec.header_id;
2926 x_order_header_rec.status := l_header_rec.return_status;
2927 -- x_order_header_rec.quote_header_id := to_number(l_header_rec.orig_sys_document_ref);
2928 x_order_header_rec.quote_header_id := l_header_rec.source_document_id;
2929
2930 FOR i in 1..l_line_tbl.count LOOP
2931 x_order_line_tbl(i).order_line_id := l_line_tbl(i).line_id;
2932 x_order_line_tbl(i).order_header_id := l_line_tbl(i).header_id;
2933 -- x_order_line_tbl(i).quote_shipment_line_id := to_number(l_line_tbl(i).orig_sys_document_ref);
2934 x_order_line_tbl(i).quote_shipment_line_id := l_line_tbl(i).source_document_line_id;
2935 x_order_line_tbl(i).status := l_line_tbl(i).return_status;
2936 END LOOP;
2937
2938 --
2939 -- End of API body
2940 --
2941
2942 -- Standard check for p_commit
2943 IF FND_API.to_Boolean( p_commit )
2944 THEN
2945 COMMIT WORK;
2946 END IF;
2947
2948
2949
2950 -- Standard call to get message count and if count is 1, get message info.
2951 FND_MSG_PUB.Count_And_Get
2952 ( p_count => x_msg_count,
2953 p_data => x_msg_data
2954 );
2955
2956 EXCEPTION
2957 WHEN FND_API.G_EXC_ERROR THEN
2958 null;
2959 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
2960 P_API_NAME => L_API_NAME
2961 ,P_PKG_NAME => G_PKG_NAME
2962 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
2963 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
2964 ,X_MSG_COUNT => X_MSG_COUNT
2965 ,X_MSG_DATA => X_MSG_DATA
2966 ,X_RETURN_STATUS => X_RETURN_STATUS);
2967
2968 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2969 null;
2970 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
2971 P_API_NAME => L_API_NAME
2972 ,P_PKG_NAME => G_PKG_NAME
2973 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
2974 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
2975 ,X_MSG_COUNT => X_MSG_COUNT
2976 ,X_MSG_DATA => X_MSG_DATA
2977 ,X_RETURN_STATUS => X_RETURN_STATUS);
2978 WHEN OTHERS THEN
2979 null;
2980 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
2981 P_API_NAME => L_API_NAME
2982 ,P_PKG_NAME => G_PKG_NAME
2983 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
2984 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
2985 ,X_MSG_COUNT => X_MSG_COUNT
2986 ,X_MSG_DATA => X_MSG_DATA
2987 ,X_RETURN_STATUS => X_RETURN_STATUS);
2988
2989 END Update_Order;
2990
2991
2992
2993
2994 FUNCTION Get_Header_Status (
2995 p_Header_Id NUMBER) RETURN VARCHAR2
2996 IS
2997 l_result_date DATE;
2998 l_latest_date DATE := NULL;
2999 l_result VARCHAR2(1);
3000 l_return_status VARCHAR2(20) := NULL;
3001 BEGIN
3002
3003
3004 OE_HEADER_STATUS_PUB.Get_Booked_Status
3005 (
3006 p_header_id => p_Header_Id
3007 , x_result => l_result
3008 , x_result_date => l_result_date
3009 );
3010
3011 IF ( l_result = 'Y' ) THEN
3012
3013 l_return_status := 'BOOKED';
3014 l_latest_date := l_result_date;
3015
3016 END IF;
3017
3018
3019 OE_HEADER_STATUS_PUB.Get_Closed_Status
3020 (
3021 p_header_id => p_Header_Id
3022 , x_result => l_result
3023 , x_result_date => l_result_date
3024 );
3025
3026 IF ( l_result = 'Y' ) THEN
3027
3028 IF ( l_latest_date IS NULL OR l_result_date > l_latest_date ) THEN
3029
3030 l_latest_date := l_result_date;
3031 l_return_status := 'CLOSED';
3032
3033 END IF;
3034
3035 END IF;
3036
3037 OE_HEADER_STATUS_PUB.Get_Cancelled_Status
3038 (
3039 p_header_id => p_Header_Id
3040 , x_result => l_result
3041 , x_result_date => l_result_date
3042 );
3043
3044 IF ( l_result = 'Y' ) THEN
3045
3046 IF ( l_latest_date IS NULL OR l_result_date > l_latest_date ) THEN
3047
3048 l_latest_date := l_result_date;
3049 l_return_status := 'CANCELLED';
3050
3051 END IF;
3052
3053 END IF;
3054
3055 IF ( l_return_status IS NULL ) THEN
3056
3057 l_return_status := 'ENTERED';
3058
3059 END IF;
3060
3061 return (l_return_status);
3062
3063 END Get_Header_Status;
3064
3065
3066 FUNCTION Get_Line_Status (
3067 p_Line_Id NUMBER) RETURN VARCHAR2
3068 IS
3069 l_result_date DATE;
3070 l_latest_date DATE := NULL;
3071 l_result VARCHAR2(1);
3072 l_return_status VARCHAR2(20) := NULL;
3073 BEGIN
3074
3075 OE_LINE_STATUS_PUB.Get_Closed_Status
3076 (
3077 p_line_id => p_Line_Id
3078 , x_result => l_result
3079 , x_result_date => l_result_date
3080 );
3081
3082 IF ( l_result = 'Y' ) THEN
3083
3084 l_latest_date := l_result_date;
3085 l_return_status := 'CLOSED';
3086
3087 END IF;
3088
3089 OE_LINE_STATUS_PUB.Get_Cancelled_Status
3090 (
3091 p_line_id => p_Line_Id
3092 , x_result => l_result
3093 , x_result_date => l_result_date
3094 );
3095
3096 IF ( l_result = 'Y' ) THEN
3097
3098 IF ( l_latest_date IS NULL OR l_result_date > l_latest_date ) THEN
3099
3100 l_latest_date := l_result_date;
3101 l_return_status := 'CANCELLED';
3102
3103 END IF;
3104
3105 END IF;
3106
3107 OE_LINE_STATUS_PUB.Get_Purchase_Release_status
3108 (
3109 p_line_id => p_Line_Id
3110 , x_result => l_result
3111 , x_result_date => l_result_date
3112 );
3113
3114 IF ( l_result = 'Y' ) THEN
3115
3116 IF ( l_latest_date IS NULL OR l_result_date > l_latest_date ) THEN
3117
3118 l_latest_date := l_result_date;
3119 l_return_status := 'RELEASED';
3120
3121 END IF;
3122
3123 END IF;
3124
3125 OE_LINE_STATUS_PUB.Get_ship_Status
3126 (
3127 p_line_id => p_Line_Id
3128 , x_result => l_result
3129 , x_result_date => l_result_date
3130 );
3131
3132
3133 IF ( l_result = 'Y' ) THEN
3134
3135 IF ( l_latest_date IS NULL OR l_result_date > l_latest_date ) THEN
3136
3137 l_latest_date := l_result_date;
3138 l_return_status := 'SHIPPED';
3139
3140 END IF;
3141
3142 END IF;
3143
3144 OE_LINE_STATUS_PUB.Get_Received_Status
3145 (
3146 p_line_id => p_Line_Id
3147 , x_result => l_result
3148 , x_result_date => l_result_date
3149 );
3150
3151 IF ( l_result = 'Y' ) THEN
3152
3153 IF ( l_latest_date IS NULL OR l_result_date > l_latest_date ) THEN
3154
3155 l_latest_date := l_result_date;
3156 l_return_status := 'RECEIVED';
3157
3158 END IF;
3159
3160 END IF;
3161
3162 OE_LINE_STATUS_PUB.Get_Invoiced_Status
3163 (
3164 p_line_id => p_Line_Id
3165 , x_result => l_result
3166 , x_result_date => l_result_date
3167 );
3168
3169 IF ( l_result = 'Y' ) THEN
3170
3171 IF ( l_latest_date IS NULL OR l_result_date > l_latest_date ) THEN
3172
3173 l_latest_date := l_result_date;
3174 l_return_status := 'INVOICED';
3175
3176 END IF;
3177
3178 END IF;
3179
3180 IF ( l_return_status IS NULL ) THEN
3181
3182 l_return_status := 'ENTERED';
3183
3184 END IF;
3185
3186 return(l_return_status);
3187
3188 END Get_Line_Status;
3189
3190
3191 FUNCTION Total_List_Price (
3192 p_Header_Id in NUMBER,
3193 p_Line_Id in NUMBER,
3194 p_line_number in NUMBER,
3195 p_shipment_number in number := null)
3196 RETURN NUMBER
3197 IS
3198 l_total NUMBER;
3199 BEGIN
3200
3201 l_total := OE_OE_TOTALS_SUMMARY.LINE_TOTAL(
3202 p_Header_Id , p_Line_Id ,
3203 p_line_number, p_shipment_number);
3204
3205 RETURN l_total;
3206 EXCEPTION
3207 WHEN OTHERS THEN
3208 return null;
3209 END Total_List_Price;
3210
3211 FUNCTION Total_Order_Price (
3212 p_Header_Id in NUMBER) RETURN NUMBER
3213 IS
3214 l_total NUMBER;
3215 l_subtotal number;
3216 l_discount number;
3217 l_charges number;
3218 l_tax number;
3219 BEGIN
3220
3221 OE_OE_TOTALS_SUMMARY.ORDER_TOTALS( p_Header_Id,
3222 l_subtotal,
3223 l_discount,
3224 l_charges,
3225 l_tax
3226 );
3227
3228 l_total := l_subtotal + l_charges + l_tax;
3229
3230 RETURN (l_total);
3231
3232 EXCEPTION
3233 WHEN OTHERS THEN
3234 return null;
3235 END Total_Order_Price;
3236
3237 FUNCTION GET_ORDER_TOTAL(
3238 P_HEADER_ID IN NUMBER,
3239 P_LINE_ID IN NUMBER,
3240 P_TOTAL_TYPE IN VARCHAR2 := 'ALL')
3241 RETURN NUMBER
3242 IS
3243 l_total number;
3244 begin
3245
3246 aso_debug_pub.g_debug_flag := nvl(fnd_profile.value('ASO_ENABLE_DEBUG'),'N');
3247
3248 l_total := OE_Totals_GRP.GET_Order_Total (
3249 P_HEADER_ID ,
3250 P_LINE_ID,
3251 P_TOTAL_TYPE);
3252 return (l_total);
3253 EXCEPTION
3254 WHEN OTHERS THEN
3255 return null;
3256 END GET_ORDER_TOTAL;
3257
3258 PROCEDURE get_acct_site_uses
3259 (
3260 p_party_site_id IN NUMBER,
3261 p_acct_site_type IN VARCHAR2,
3262 p_cust_account_id IN NUMBER,
3263 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
3264 x_site_use_id OUT NOCOPY /* file.sql.39 change */ number
3265 )
3266 IS
3267 CURSOR relationship_cur IS
3268 SELECT a.party_type
3269 from
3270 HZ_PARTIES a, HZ_PARTY_SITES b
3271 where
3272 a.status = 'A'
3273 and b.party_site_id = p_party_site_id
3274 and b.party_id = a.party_id;
3275 CURSOR site_use_cur IS
3276 select a.site_use_id
3277 from
3278 hz_cust_site_uses_all a, hz_cust_acct_sites_all b
3279 where
3280 a.status = 'A'
3281 and b.cust_account_id = p_cust_account_id
3282 and b.party_site_id = p_party_site_id
3283 and a.cust_acct_site_id = b.cust_acct_site_id
3284 and a.site_use_code = p_acct_site_type;
3285 l_party_type VARCHAR2(30);
3286 l_site_use_id number;
3287 begin
3288
3289 aso_debug_pub.g_debug_flag := nvl(fnd_profile.value('ASO_ENABLE_DEBUG'),'N');
3290
3291 x_return_status := FND_API.G_RET_STS_SUCCESS;
3292
3293 OPEN relationship_cur;
3294 FETCH relationship_cur INTO l_party_type;
3295 IF (relationship_cur%NOTFOUND) THEN
3296 l_party_type := NULL;
3297 x_return_status := FND_API.G_RET_STS_ERROR;
3298 END IF;
3299 CLOSE relationship_cur;
3300
3301 IF l_party_type = 'PARTY_RELATIONSHIP' THEN
3302 OPEN site_use_cur;
3303 FETCH site_use_cur INTO l_site_use_id;
3304 IF (site_use_cur%NOTFOUND) THEN
3305 l_site_use_id := NULL;
3306 x_return_status := FND_API.G_RET_STS_ERROR;
3307 END IF;
3308 CLOSE site_use_cur;
3309 END IF;
3310
3311 x_site_use_id := l_site_use_id;
3312 END get_acct_site_uses;
3313
3314 PROCEDURE get_cust_acct_roles
3315 (
3316 p_party_id IN NUMBER,
3317 p_party_site_id IN NUMBER,
3318 p_acct_site_type IN VARCHAR2,
3319 p_cust_account_id IN NUMBER,
3320 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
3321 x_cust_account_role_id OUT NOCOPY /* file.sql.39 change */ number
3322 )
3323 IS
3324 CURSOR relationship_cur IS
3325 SELECT party_type
3326 from
3327 HZ_PARTIES
3328 where party_id = p_party_id and status ='A';
3329 CURSOR org_contact IS
3330 select a.org_contact_id
3331 from
3332 hz_org_contacts a, hz_relationships b
3333 where
3334 b.party_id = p_party_id
3335 --and a.status = 'A' -- status column is obseleted
3336 and b.relationship_id = a.party_relationship_id
3337 and (sysdate between nvl(b.start_date, sysdate) and nvl(b.end_date, sysdate));
3338 CURSOR cust_role IS
3339 select a.cust_account_role_id
3340 from
3341 hz_cust_account_roles a, hz_role_responsibility b, hz_cust_acct_sites_all c
3342 where
3343 a.role_type = 'CONTACT'
3344 and a.party_id = p_party_id
3345 and a.cust_account_id = p_cust_account_id
3346 and a.cust_acct_site_id = c.cust_acct_site_id
3347 and a.cust_account_id = c.cust_account_id
3348 and c.party_site_id = p_party_site_id
3349 and a.cust_account_role_id = b.cust_account_role_id
3350 and responsibility_type = p_acct_site_type;
3351 l_org_contact_id number;
3352 l_party_type VARCHAR2(30);
3353 l_cust_account_role_id number;
3354 begin
3355
3356 aso_debug_pub.g_debug_flag := nvl(fnd_profile.value('ASO_ENABLE_DEBUG'),'N');
3357
3358 x_return_status := FND_API.G_RET_STS_SUCCESS;
3359
3360 OPEN relationship_cur;
3361 FETCH relationship_cur INTO l_party_type;
3362 IF (relationship_cur%NOTFOUND) THEN
3363 l_party_type := NULL;
3364 x_return_status := FND_API.G_RET_STS_ERROR;
3365 END IF;
3366 CLOSE relationship_cur;
3367
3368 IF l_party_type = 'PARTY_RELATIONSHIP' THEN
3369 OPEN org_contact;
3370 FETCH org_contact INTO l_org_contact_id;
3371 IF (org_contact%NOTFOUND) THEN
3372 l_org_contact_id := NULL;
3373 x_return_status := FND_API.G_RET_STS_ERROR;
3374 END IF;
3375 CLOSE org_contact;
3376
3377 OPEN cust_role;
3378 FETCH cust_role INTO l_cust_account_role_id;
3379 IF (cust_role%NOTFOUND) THEN
3380 l_cust_account_role_id := NULL;
3381 x_return_status := FND_API.G_RET_STS_ERROR;
3382 END IF;
3383 CLOSE cust_role;
3384
3385 END IF;
3386
3387 x_cust_account_role_id := l_cust_account_role_id;
3388 END get_cust_acct_roles;
3389
3390 PROCEDURE Get_Cust_Accnt_Id(
3391 P_Qte_Rec IN ASO_QUOTE_PUB.Qte_Header_Rec_Type
3392 := ASO_QUOTE_PUB.G_MISS_QTE_HEADER_REC,
3393 p_Party_Id IN NUMBER,
3394 p_Cust_Acct_Id OUT NOCOPY /* file.sql.39 change */ NUMBER,
3395 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
3396 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
3397 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
3398 IS
3399
3400 CURSOR C_get_cust_id_from_party_id(l_Party_Id NUMBER) IS
3401 SELECT cust_account_id
3402 FROM hz_cust_accounts
3403 WHERE party_id = l_Party_Id
3404 and status = 'A';
3405
3406 count NUMBER := 0;
3407 x_cust_id NUMBER := NULL;
3408 lx_cust_id NUMBER := NULL;
3409
3410 l_msg_count number;
3411 l_msg_data varchar2(200);
3412 lx_cust_account_id NUMBER;
3413 l_return_status VARCHAR2(1);
3414
3415
3416
3417 BEGIN
3418
3419 OPEN C_get_cust_id_from_party_id(p_Party_Id);
3420
3421 LOOP
3422 FETCH C_get_cust_id_from_party_id INTO lx_cust_id;
3423 IF C_get_cust_id_from_party_id%ROWCOUNT > 1 THEN
3424 x_return_status := FND_API.G_RET_STS_ERROR;
3425 EXIT;
3426 END IF;
3427 EXIT WHEN C_get_cust_id_from_party_id%NOTFOUND;
3428 END LOOP;
3429
3430 CLOSE C_get_cust_id_from_party_id;
3431
3432 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3433 FND_MESSAGE.Set_Name('ASO', 'ASO_MULTIPLE_CUST_ACCOUNT');
3434 FND_MESSAGE.Set_Token('ID', to_char( p_qte_rec.party_id), FALSE);
3435 FND_MSG_PUB.ADD;
3436 raise FND_API.G_EXC_ERROR;
3437 END IF;
3438
3439 IF lx_cust_id IS NULL OR lx_cust_id = FND_API.G_MISS_NUM THEN
3440 IF p_qte_rec.party_id is not NULL
3441 AND p_qte_rec.party_id <> FND_API.G_MISS_NUM THEN
3442 ASO_PARTY_INT.Create_Customer_Account(
3443 p_api_version => 1.0
3444 ,P_Qte_REC => p_qte_rec
3445 ,x_return_status => l_return_status
3446 ,x_msg_count => l_msg_count
3447 ,x_msg_data => l_msg_data
3448 ,x_acct_id => lx_cust_account_id
3449 );
3450
3451 IF l_Return_Status <> FND_API.G_RET_STS_SUCCESS THEN
3452 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
3453 THEN
3454 FND_MESSAGE.Set_Name('ASO', 'ASO_CUST_ACCOUNT');
3455 FND_MESSAGE.Set_Token('ID', to_char( p_qte_rec.party_id), FALSE);
3456 FND_MSG_PUB.ADD;
3457 END IF;
3458 raise FND_API.G_EXC_ERROR;
3459 END IF;
3460
3461 p_Cust_Acct_Id := lx_cust_account_id;
3462 END IF;
3463
3464 ELSE
3465 p_Cust_Acct_Id := lx_cust_id;
3466
3467 END IF;
3468
3469 END Get_Cust_Accnt_Id;
3470
3471 End ASO_ORDER_INT;