1 PACKAGE BODY inv_salesorder AS
2 /* $Header: INVSOOMB.pls 120.3 2006/04/12 18:01:58 dherring ship $ */
3
4 TYPE order_hdr_rec_type IS RECORD
5 ( salesorder_id NUMBER,
6 oe_header_id NUMBER,
7 order_number NUMBER,
8 order_type VARCHAR2(2000),
9 order_source VARCHAR2(2000));
10
11 TYPE order_hdr_tbl_type IS TABLE OF order_hdr_rec_type
12 INDEX BY BINARY_INTEGER;
13
14 -- Global constant for package name
15 g_package_name CONSTANT VARCHAR2(50) := 'INV_SALESORDER';
16 g_om_installed VARCHAR2(3) := NULL;
17 g_order_source VARCHAR2(2000) := NULL;
18
19 -- Cache used to improve performance of get_salesorder_for_oeheader
20 g_order_headers order_hdr_tbl_type;
21
22
23 Procedure create_salesorder (
24 p_api_version_number IN NUMBER,
25 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
26 p_segment1 IN NUMBER,
27 p_segment2 IN VARCHAR2,
28 p_segment3 IN VARCHAR2,
29 p_validate_full IN NUMBER DEFAULT 1,
30 p_validation_date IN DATE,
31 x_salesorder_id OUT NOCOPY NUMBER,
32 x_message_data OUT NOCOPY VARCHAR2,
33 x_message_count OUT NOCOPY NUMBER,
34 x_return_status OUT NOCOPY VARCHAR2) IS
35
36 -- Constants
37 c_api_version_number CONSTANT NUMBER := 1.0 ;
38 c_api_name CONSTANT VARCHAR2(50):= 'CREATE_SALESORDER';
39
40 -- Variables
41 l_segment1 VARCHAR2(150);
42 l_segment2 VARCHAR2(150);
43 l_segment3 VARCHAR2(150);
44 l_segment_arr FND_FLEX_EXT.SEGMENTARRAY ;
45 l_validation_date DATE;
46 l_so_id NUMBER;
47 BEGIN
48
49 -- check for api call compatibility
50 if not fnd_api.compatible_api_call(
51 c_api_version_number,
52 p_api_version_number,
53 c_api_name,
54 g_package_name) then
55 raise fnd_api.g_exc_unexpected_error ;
56 end if;
57
58 -- initialize message list
59 if fnd_api.to_boolean(p_init_msg_list) then
60 fnd_msg_pub.initialize;
61 end if;
62
63 -- initialize return status to success
64 x_return_status := fnd_api.g_ret_sts_success;
65
66 -- start input parameter validation now
67 -- validate if order number is passed in (segment 1)
68 if ( p_segment1 is NULL ) then
69 fnd_message.set_name('INV', 'BAD_INPUT_ARGUMENTS');
70 fnd_msg_pub.add;
71 raise fnd_api.g_exc_error ;
72 else
73 -- convert this to varchar since the flexfield table stores as char(240)
74 l_segment1 := to_char(p_segment1);
75 end if;
76
77 -- validate if order type is passed in (segment 2)
78 if ( p_segment2 is NULL ) then
79 fnd_message.set_name('INV', 'BAD_INPUT_ARGUMENTS');
80 fnd_msg_pub.add;
81 raise fnd_api.g_exc_error ;
82 elsif ( p_segment2 is NOT NULL) then
83 l_segment2 := p_segment2;
84 end if;
85
86 -- validate if order source is passed in
87 if ( p_segment3 is NULL ) then
88 fnd_message.set_name('INV', 'BAD_INPUT_ARGUMENTS');
89 fnd_msg_pub.add;
90 raise fnd_api.g_exc_error ;
91 else
92 l_segment3 := p_segment3 ;
93 end if;
94
95 -- if validate_full is not required, then we can do a quick select and insert
96 -- to create a new sales order if needed or return an existing one
97 if ( p_validate_full = 1 ) then
98 -- validate if validation_date is passed in, if not use sysdate
99 if ( p_validation_date IS NULL ) then
100 l_validation_date := sysdate ;
101 else
102 l_validation_date := p_validation_date;
103 end if;
104
105
106 -- now we have values for all 4 segments required for sales order flex field
107 -- so we can call the flexfield api to create a sales order
108
109 l_segment_arr(1) := l_segment1 ;
110 l_segment_arr(2) := l_segment2 ;
111 l_segment_arr(3) := l_segment3 ;
112
113
114 if NOT ( fnd_flex_ext.get_combination_id(
115 application_short_name => 'INV'
116 ,key_flex_code => 'MKTS'
117 ,structure_number => 101
118 ,validation_date => l_validation_date
119 ,n_segments => 3
120 ,segments => l_segment_arr
121 ,combination_id => x_salesorder_id) ) then
122 fnd_msg_pub.add ;
123 raise fnd_api.g_exc_error ;
124 end if;
125 else
126 -- check if sales order already exists
127 BEGIN
128 SELECT sales_order_id
129 INTO l_so_id
130 FROM MTL_SALES_ORDERS
131 WHERE segment1 = l_segment1
132 AND segment2 = l_segment2
133 AND segment3 = l_segment3 ;
134
135 x_salesorder_id := l_so_id ;
136 return ;
137 EXCEPTION
138 WHEN NO_DATA_FOUND THEN
139 -- sales order does not exist, create a new one
140
141 SELECT mtl_sales_orders_s.nextval into l_so_id from dual ;
142
143 INSERT into MTL_SALES_ORDERS (sales_order_id, segment1, segment2, segment3, last_updated_by,
144 last_update_date, last_update_login, creation_date, created_by,
145 summary_flag, enabled_flag) values
146 (l_so_id, l_segment1, l_segment2, l_segment3, fnd_global.user_id, sysdate,
147 fnd_global.login_id, sysdate, fnd_global.user_id, 'N', 'Y') ;
148
149 x_salesorder_id := l_so_id ;
150
151 END;
152
153 end if;
154
155 EXCEPTION
156 when fnd_api.g_exc_error then
157 x_return_status := fnd_api.g_ret_sts_error ;
158
159 fnd_msg_pub.count_and_get(
160 p_count => x_message_count
161 , p_data => x_message_data
162 , p_encoded => 'F');
163
164 when fnd_api.g_exc_unexpected_error then
165 x_return_status := fnd_api.g_ret_sts_unexp_error;
166
167 fnd_msg_pub.count_and_get(
168 p_count => x_message_count
169 , p_data => x_message_data
170 , p_encoded => 'F');
171
172 when others then
173 x_return_status := fnd_api.g_ret_sts_unexp_error;
174
175 if (fnd_msg_pub.check_msg_level
176 (fnd_msg_pub.g_msg_lvl_unexp_error))then
177 fnd_msg_pub.add_exc_msg(g_package_name,c_api_name);
178 end if;
179
180 fnd_msg_pub.count_and_get(
181 p_count => x_message_count
182 , p_data => x_message_data
183 , p_encoded => 'F');
184
185
186 end create_salesorder;
187
188
189 /*-------------------------------------------------------------------------------------+
190 | get_oe_header_for_salesorder returns the order management order header id given a |
191 | sales order id. If the sales order id does not have a matching order managemnet |
192 | order header id, then a value (-1) is returned. This is possible since not all rows |
193 | in mtl_sales_orders may be created by Order Management. |
194 +-------------------------------------------------------------------------------------*/
195
196 Procedure get_oeheader_for_salesorder(
197 p_salesorder_id IN NUMBER,
198 x_oe_header_id OUT NOCOPY NUMBER,
199 x_return_status OUT NOCOPY VARCHAR2) IS
200
201 -- Constants
202 c_api_version_number CONSTANT NUMBER := 1.0 ;
203 c_api_name CONSTANT VARCHAR2(50) := 'GET_OEHEADER_FOR_SALESORDER';
204
205 -- Local Variables
206 l_order_source VARCHAR2(1000);
207 l_order_header_id VARCHAR2(50);
208 l_order_number VARCHAR2(50);
209 l_order_type VARCHAR2(50);
210
211 BEGIN
212
213 -- initialize return status to success
214 x_return_status := fnd_api.g_ret_sts_success;
215
216 -- initialize x_oe_order_id to -1, assume default that SO row not created by OOM
217 x_oe_header_id := -1 ;
218
219 -- now check if the SO was created by Oracle Order Management (OOM). If not return (-1)
220 if ( g_om_installed IS NULL ) then
221 g_om_installed := oe_install.get_active_product ;
222 end if;
223 if (g_om_installed <> 'ONT') then -- OOM is not active
224 return ;
225 end if;
226
227 -- now select segment 2 for the given sales order id
228 SELECT segment1,segment2,segment3
229 INTO l_order_number, l_order_type, l_order_source
230 FROM mtl_sales_orders
231 WHERE sales_order_id = p_salesorder_id ;
232
233
234 x_oe_header_id := get_header_id(to_number(l_order_number),
235 l_order_type,
236 l_order_source);
237
238 EXCEPTION
239 when fnd_api.g_exc_error then
240 x_return_status := fnd_api.g_ret_sts_error ;
241
242 when fnd_api.g_exc_unexpected_error then
243 x_return_status := fnd_api.g_ret_sts_unexp_error;
244
245 when no_data_found then
246 x_return_status := fnd_api.g_ret_sts_success ;
247
248 when others then
249 x_return_status := fnd_api.g_ret_sts_unexp_error;
250
251 if (fnd_msg_pub.check_msg_level
252 (fnd_msg_pub.g_msg_lvl_unexp_error))then
253 fnd_msg_pub.add_exc_msg(g_package_name,c_api_name);
254 end if;
255
256 end get_oeheader_for_salesorder;
257
258
259 /*---------------------------------------------------------------------------------------+
260 | Function get_salesorder_for_oeheader returns the corresponding sales order id for a |
261 | given order management header id. If a sales order does not exist, then a null is |
262 | returned by this function. |
263 +---------------------------------------------------------------------------------------*/
264
265 Function get_salesorder_for_oeheader(
266 p_oe_header_id IN NUMBER) return number is
267
268 --local variables
269 l_salesorder_id NUMBER;
270 l_order_source VARCHAR2(2000);
271 l_order_type VARCHAR2(2000);
272 l_order_number NUMBER ;
273
274 BEGIN
275
276 IF g_order_headers.exists(p_oe_header_id) THEN
277
278 l_salesorder_id := g_order_headers(p_oe_header_id).salesorder_id;
279 ELSE
280 oe_header_util.get_order_info(p_oe_header_id,
281 l_order_number,
282 l_order_type,
283 l_order_source);
284
285 SELECT sales_order_id
286 INTO l_salesorder_id
287 FROM mtl_sales_orders
288 WHERE segment1 = to_char(l_order_number)
289 AND segment2 = l_order_type
290 AND segment3 = l_order_source ;
291
292 g_order_headers(p_oe_header_id).salesorder_id := l_salesorder_id;
293 g_order_headers(p_oe_header_id).order_number := l_order_number;
294 g_order_headers(p_oe_header_id).order_type := l_order_type;
295 g_order_headers(p_oe_header_id).order_source := l_order_source;
296 g_order_headers(p_oe_header_id).oe_header_id := p_oe_header_id;
297
298 END IF;
299
300 return l_salesorder_id ;
301
302 Exception
303
304 WHEN OTHERS then
305 return to_number(null);
306
307 end get_salesorder_for_oeheader ;
308
309 /*----------------------------------------------------------------------------------------+
310 | This function, synch_salesorders_with_om, is used to update an existing sales order |
311 | with new segment values for either order_number and/or order type and/or order source |
312 | given an original order number and/or order type and/or order source. This API is |
313 | is provided because in Order Management the order number and order type can be updated |
314 | even after a sales order has been created. The input parameter "multiple_rows" |
315 | determines whether it is teh intention of the caller to update multiple rows. |
316 +----------------------------------------------------------------------------------------*/
317
318 function synch_salesorders_with_om(
319 p_original_order_number IN VARCHAR2,
320 p_original_order_type IN VARCHAR2,
321 p_original_source_code IN VARCHAR2,
322 p_new_order_number IN VARCHAR2,
323 p_new_order_type IN VARCHAR2,
324 p_new_order_source IN VARCHAR2,
325 p_multiple_rows IN VARCHAR2 default 'N') return number IS
326
327 BEGIN
328
329 -- Bug 2648869: Performance fix. The update statement was changed based on
330 -- whether thep_multiple_rows is Y or N. If it is N, then all the
331 -- parameters are passed, so the SQL need not have the NVL.
332 -- This is will help is utilizing the index
333 -- and the fetch will be faster.
334
335 if (p_multiple_rows <> 'Y' ) THEN
336
337 if ( (p_original_order_number IS NULL) OR
338 (p_original_order_type IS NULL) OR
339 (p_original_source_code IS NULL) ) then
340 return 0 ;
341 ELSE
342 UPDATE mtl_sales_orders
343 SET segment1 = NVL(p_new_order_number,segment1),
344 segment2 = NVL(p_new_order_type, segment2),
345 segment3 = NVL(p_new_order_source, segment3)
346 WHERE segment1 = p_original_order_number
347 AND segment2 = p_original_order_type
348 AND segment3 = p_original_source_code;
349 end if;
350
351 ELSE
352 --bug4237769
353 UPDATE mtl_sales_orders
354 SET segment1 = NVL(p_new_order_number,segment1),
355 segment2 = NVL(p_new_order_type, segment2),
356 segment3 = NVL(p_new_order_source, segment3)
357 WHERE (p_original_order_number IS NULL OR
358 segment1 = p_original_order_number)
359 AND (p_original_order_type IS NULL OR
360 segment2 = p_original_order_type)
361 AND (p_original_source_code IS NULL OR
362 segment3 = p_original_source_code);
363
364
365 end if;
366
367 return 1;
368
369 EXCEPTION
370
371 WHEN NO_DATA_FOUND then
372 return 0 ;
373
374 end synch_salesorders_with_om ;
375 FUNCTION Get_Header_Id (p_order_number IN NUMBER,
376 p_order_type IN VARCHAR2,
377 p_order_source IN VARCHAR2)
378 RETURN NUMBER
379 IS
380 l_order_type_id NUMBER;
381 l_order_type VARCHAR2(240);
382 l_header_id NUMBER;
383 BEGIN
384
385 Select header_id
386 into l_header_id
387 from oe_order_headers_all
388 where order_number = p_order_number AND
389 order_type_id IN (select tl.transaction_type_id
390 from oe_transaction_types_tl tl,
391 oe_transaction_types_all ta
392 where ta.transaction_type_id =
393 tl.transaction_type_id and
394 tl.name = p_order_type and
395 ta.transaction_type_code = 'ORDER'
396 and LANGUAGE = (
397 select language_code
398 from fnd_languages
399 where installed_flag = 'B'));
400
401 RETURN l_header_id;
402
403 EXCEPTION
404 WHEN NO_DATA_FOUND THEN
405 RETURN -1;
406 WHEN OTHERS THEN
407 RETURN -1;
408
409 END Get_Header_Id;
410
411 Procedure create_mtl_sales_orders_bulk (
412 p_api_version_number IN NUMBER,
413 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
414 p_header_rec IN OE_BULK_ORDER_PVT.HEADER_REC_TYPE,
415 x_message_data OUT NOCOPY VARCHAR2,
416 x_message_count OUT NOCOPY NUMBER,
417 x_return_status OUT NOCOPY VARCHAR2) IS
418
419 -- Constants
420 c_api_version_number CONSTANT NUMBER := 1.0 ;
421 c_api_name CONSTANT VARCHAR2(50):= 'CREATE_MTL_SALES_ORDERS_BULK';
422
423 l_source_code VARCHAR2(40) := FND_PROFILE.VALUE('ONT_SOURCE_CODE');
424
425 BEGIN
426 -- check for api call compatibility
427 if not fnd_api.compatible_api_call(
428 c_api_version_number,
429 p_api_version_number,
430 c_api_name,
431 g_package_name) then
432 raise fnd_api.g_exc_unexpected_error ;
433 end if;
434
435 -- initialize message list
436 if fnd_api.to_boolean(p_init_msg_list) then
437 fnd_msg_pub.initialize;
438 end if;
439
440 -- initialize return status to success
441 x_return_status := fnd_api.g_ret_sts_success;
442
443
444 FORALL i IN p_header_rec.order_number.FIRST..p_header_rec.order_number.LAST
445 INSERT INTO MTL_SALES_ORDERS
446 (sales_order_id,
447 segment1,
448 segment2,
449 segment3,
450 last_updated_by,
451 last_update_date,
452 last_update_login,
453 creation_date,
454 created_by,
455 summary_flag,
456 enabled_flag)
457 Values(
458 mtl_sales_orders_s.nextval,
459 p_header_rec.order_number(i),
460 p_header_rec.order_type_name(i),
461 l_source_code,
462 fnd_global.user_id,
463 sysdate,
464 fnd_global.login_id,
465 sysdate,
466 fnd_global.user_id,
467 'N',
468 'Y');
469
470 EXCEPTION
471 when fnd_api.g_exc_unexpected_error then
472 x_return_status := fnd_api.g_ret_sts_unexp_error;
473
474 fnd_msg_pub.count_and_get(
475 p_count => x_message_count
476 , p_data => x_message_data
477 , p_encoded => 'F');
478
479 OE_MSG_PUB.Add_Exc_Msg(g_package_name, c_api_name, x_message_data);
480
481 when others then
482 x_return_status := fnd_api.g_ret_sts_unexp_error;
483
484 if (fnd_msg_pub.check_msg_level
485 (fnd_msg_pub.g_msg_lvl_unexp_error))then
486 fnd_msg_pub.add_exc_msg(g_package_name,c_api_name);
487 end if;
488
489 fnd_msg_pub.count_and_get(
490 p_count => x_message_count
491 , p_data => x_message_data
492 , p_encoded => 'F');
493 OE_MSG_PUB.Add_Exc_Msg(g_package_name, c_api_name, x_message_data);
494
495 END create_mtl_sales_orders_bulk;
496
497
498 PROCEDURE delete_mtl_sales_orders_bulk(
499 p_api_version_number IN NUMBER,
500 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
501 p_error_rec IN OE_BULK_ORDER_PVT.INVALID_HDR_REC_TYPE,
502 x_message_data OUT NOCOPY VARCHAR2,
503 x_message_count OUT NOCOPY NUMBER,
504 x_return_status OUT NOCOPY VARCHAR2) IS
505
506 -- Constants
507 c_api_version_number CONSTANT NUMBER := 1.0 ;
508 c_api_name CONSTANT VARCHAR2(50):= 'DELETE_MTL_SALES_ORDERS_BULK';
509 BEGIN
510 -- check for api call compatibility
511 if not fnd_api.compatible_api_call(
512 c_api_version_number,
513 p_api_version_number,
514 c_api_name,
515 g_package_name) then
516 raise fnd_api.g_exc_unexpected_error ;
517 end if;
518
519 -- initialize message list
520 if fnd_api.to_boolean(p_init_msg_list) then
521 fnd_msg_pub.initialize;
522 end if;
523
524 -- initialize return status to success
525 x_return_status := fnd_api.g_ret_sts_success;
526
527 --Delete MTL Sales Order Records
528 /*Bug3575085:Changed the sequence of the sub-query from b.name,a.order_number
529 to a.order_number,b.name*/
530 --Bug4237769 Correcting the type mismatch.
531 FORALL i IN 1..P_ERROR_REC.header_id.COUNT
532 DELETE from mtl_sales_orders
533 WHERE (segment1, segment2) IN
534 (select to_char(a.order_number),b.name
535 FROM oe_order_headers a,
536 oe_order_types_v b
537 WHERE a.header_id = p_error_rec.header_id(i)
538 AND a.order_type_id = b.order_type_id);
539
540
541 EXCEPTION
542 when others then
543 fnd_msg_pub.count_and_get(
544 p_count => x_message_count
545 , p_data => x_message_data
546 , p_encoded => 'F');
547
548 OE_MSG_PUB.Add_Exc_Msg(g_package_name, c_api_name, x_message_data);
549 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
550
551 END delete_mtl_sales_orders_bulk;
552
553 end inv_salesorder ;
[Home] [Help]