1 PACKAGE BODY inv_salesorder AS
2 /* $Header: INVSOOMB.pls 120.3.12010000.2 2009/05/11 09:39:24 viiyer 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 --Bug# 8262540
277 --For all the references to g_order_headers(p_oe_header_id).*, ORA-01426:
278 --numeric overflow exception was being thrown when the value of p_oe_header_id
279 --was more than 2^31
280 --g_order_headers is a variable of order_hdr_tbl_type which is a table type
281 --INDEX BY BINARY_INTEGER. The range for BINARY_INTEGER is -2147483647 to
282 --+2147483647 and here the value of p_oe_header_id is
283 --beyond the range of BINARY_INTEGER so indexing with this value is not
284 --possible.
285 --Used the mod approach for this fix and replaced all the references
286 --to g_order_headers(p_oe_header_id).* with
287 --g_order_headers(mod(p_oe_header_id,2147483648)).*.
288
289 IF g_order_headers.exists(Mod(p_oe_header_id, 2147483648)) THEN
290
291 l_salesorder_id := g_order_headers(Mod(p_oe_header_id, 2147483648)).salesorder_id;
292 ELSE
293 oe_header_util.get_order_info(p_oe_header_id,
294 l_order_number,
295 l_order_type,
296 l_order_source);
297
298 SELECT sales_order_id
299 INTO l_salesorder_id
300 FROM mtl_sales_orders
301 WHERE segment1 = to_char(l_order_number)
302 AND segment2 = l_order_type
303 AND segment3 = l_order_source ;
304
305 g_order_headers(Mod(p_oe_header_id, 2147483648)).salesorder_id := l_salesorder_id;
306 g_order_headers(Mod(p_oe_header_id, 2147483648)).order_number := l_order_number;
307 g_order_headers(Mod(p_oe_header_id, 2147483648)).order_type := l_order_type;
308 g_order_headers(Mod(p_oe_header_id, 2147483648)).order_source := l_order_source;
309 g_order_headers(Mod(p_oe_header_id, 2147483648)).oe_header_id := p_oe_header_id;
310
311 END IF;
312
313 return l_salesorder_id ;
314
315 Exception
316
317 WHEN OTHERS then
318 return to_number(null);
319
320 end get_salesorder_for_oeheader ;
321
322 /*----------------------------------------------------------------------------------------+
323 | This function, synch_salesorders_with_om, is used to update an existing sales order |
324 | with new segment values for either order_number and/or order type and/or order source |
325 | given an original order number and/or order type and/or order source. This API is |
326 | is provided because in Order Management the order number and order type can be updated |
327 | even after a sales order has been created. The input parameter "multiple_rows" |
328 | determines whether it is teh intention of the caller to update multiple rows. |
329 +----------------------------------------------------------------------------------------*/
330
331 function synch_salesorders_with_om(
332 p_original_order_number IN VARCHAR2,
333 p_original_order_type IN VARCHAR2,
334 p_original_source_code IN VARCHAR2,
335 p_new_order_number IN VARCHAR2,
336 p_new_order_type IN VARCHAR2,
337 p_new_order_source IN VARCHAR2,
338 p_multiple_rows IN VARCHAR2 default 'N') return number IS
339
340 BEGIN
341
342 -- Bug 2648869: Performance fix. The update statement was changed based on
343 -- whether thep_multiple_rows is Y or N. If it is N, then all the
344 -- parameters are passed, so the SQL need not have the NVL.
345 -- This is will help is utilizing the index
346 -- and the fetch will be faster.
347
348 if (p_multiple_rows <> 'Y' ) THEN
349
350 if ( (p_original_order_number IS NULL) OR
351 (p_original_order_type IS NULL) OR
352 (p_original_source_code IS NULL) ) then
353 return 0 ;
354 ELSE
355 UPDATE mtl_sales_orders
356 SET segment1 = NVL(p_new_order_number,segment1),
357 segment2 = NVL(p_new_order_type, segment2),
358 segment3 = NVL(p_new_order_source, segment3)
359 WHERE segment1 = p_original_order_number
360 AND segment2 = p_original_order_type
361 AND segment3 = p_original_source_code;
362 end if;
363
364 ELSE
365 --bug4237769
366 UPDATE mtl_sales_orders
367 SET segment1 = NVL(p_new_order_number,segment1),
368 segment2 = NVL(p_new_order_type, segment2),
369 segment3 = NVL(p_new_order_source, segment3)
370 WHERE (p_original_order_number IS NULL OR
371 segment1 = p_original_order_number)
372 AND (p_original_order_type IS NULL OR
373 segment2 = p_original_order_type)
374 AND (p_original_source_code IS NULL OR
375 segment3 = p_original_source_code);
376
377
378 end if;
379
380 return 1;
381
382 EXCEPTION
383
384 WHEN NO_DATA_FOUND then
385 return 0 ;
386
387 end synch_salesorders_with_om ;
388 FUNCTION Get_Header_Id (p_order_number IN NUMBER,
389 p_order_type IN VARCHAR2,
390 p_order_source IN VARCHAR2)
391 RETURN NUMBER
392 IS
393 l_order_type_id NUMBER;
394 l_order_type VARCHAR2(240);
395 l_header_id NUMBER;
396 BEGIN
397
398 Select header_id
399 into l_header_id
400 from oe_order_headers_all
401 where order_number = p_order_number AND
402 order_type_id IN (select tl.transaction_type_id
403 from oe_transaction_types_tl tl,
404 oe_transaction_types_all ta
405 where ta.transaction_type_id =
406 tl.transaction_type_id and
407 tl.name = p_order_type and
408 ta.transaction_type_code = 'ORDER'
409 and LANGUAGE = (
410 select language_code
411 from fnd_languages
412 where installed_flag = 'B'));
413
414 RETURN l_header_id;
415
416 EXCEPTION
417 WHEN NO_DATA_FOUND THEN
418 RETURN -1;
419 WHEN OTHERS THEN
420 RETURN -1;
421
422 END Get_Header_Id;
423
424 Procedure create_mtl_sales_orders_bulk (
425 p_api_version_number IN NUMBER,
426 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
427 p_header_rec IN OE_BULK_ORDER_PVT.HEADER_REC_TYPE,
428 x_message_data OUT NOCOPY VARCHAR2,
429 x_message_count OUT NOCOPY NUMBER,
430 x_return_status OUT NOCOPY VARCHAR2) IS
431
432 -- Constants
433 c_api_version_number CONSTANT NUMBER := 1.0 ;
434 c_api_name CONSTANT VARCHAR2(50):= 'CREATE_MTL_SALES_ORDERS_BULK';
435
436 l_source_code VARCHAR2(40) := FND_PROFILE.VALUE('ONT_SOURCE_CODE');
437
438 BEGIN
439 -- check for api call compatibility
440 if not fnd_api.compatible_api_call(
441 c_api_version_number,
442 p_api_version_number,
443 c_api_name,
444 g_package_name) then
445 raise fnd_api.g_exc_unexpected_error ;
446 end if;
447
448 -- initialize message list
449 if fnd_api.to_boolean(p_init_msg_list) then
450 fnd_msg_pub.initialize;
451 end if;
452
453 -- initialize return status to success
454 x_return_status := fnd_api.g_ret_sts_success;
455
456
457 FORALL i IN p_header_rec.order_number.FIRST..p_header_rec.order_number.LAST
458 INSERT INTO MTL_SALES_ORDERS
459 (sales_order_id,
460 segment1,
461 segment2,
462 segment3,
463 last_updated_by,
464 last_update_date,
465 last_update_login,
466 creation_date,
467 created_by,
468 summary_flag,
469 enabled_flag)
470 Values(
471 mtl_sales_orders_s.nextval,
472 p_header_rec.order_number(i),
473 p_header_rec.order_type_name(i),
474 l_source_code,
475 fnd_global.user_id,
476 sysdate,
477 fnd_global.login_id,
478 sysdate,
479 fnd_global.user_id,
480 'N',
481 'Y');
482
483 EXCEPTION
484 when fnd_api.g_exc_unexpected_error then
485 x_return_status := fnd_api.g_ret_sts_unexp_error;
486
487 fnd_msg_pub.count_and_get(
488 p_count => x_message_count
489 , p_data => x_message_data
490 , p_encoded => 'F');
491
492 OE_MSG_PUB.Add_Exc_Msg(g_package_name, c_api_name, x_message_data);
493
494 when others then
495 x_return_status := fnd_api.g_ret_sts_unexp_error;
496
497 if (fnd_msg_pub.check_msg_level
498 (fnd_msg_pub.g_msg_lvl_unexp_error))then
499 fnd_msg_pub.add_exc_msg(g_package_name,c_api_name);
500 end if;
501
502 fnd_msg_pub.count_and_get(
503 p_count => x_message_count
504 , p_data => x_message_data
505 , p_encoded => 'F');
506 OE_MSG_PUB.Add_Exc_Msg(g_package_name, c_api_name, x_message_data);
507
508 END create_mtl_sales_orders_bulk;
509
510
511 PROCEDURE delete_mtl_sales_orders_bulk(
512 p_api_version_number IN NUMBER,
513 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
514 p_error_rec IN OE_BULK_ORDER_PVT.INVALID_HDR_REC_TYPE,
515 x_message_data OUT NOCOPY VARCHAR2,
516 x_message_count OUT NOCOPY NUMBER,
517 x_return_status OUT NOCOPY VARCHAR2) IS
518
519 -- Constants
520 c_api_version_number CONSTANT NUMBER := 1.0 ;
521 c_api_name CONSTANT VARCHAR2(50):= 'DELETE_MTL_SALES_ORDERS_BULK';
522 BEGIN
523 -- check for api call compatibility
524 if not fnd_api.compatible_api_call(
525 c_api_version_number,
526 p_api_version_number,
527 c_api_name,
528 g_package_name) then
529 raise fnd_api.g_exc_unexpected_error ;
530 end if;
531
532 -- initialize message list
533 if fnd_api.to_boolean(p_init_msg_list) then
534 fnd_msg_pub.initialize;
535 end if;
536
537 -- initialize return status to success
538 x_return_status := fnd_api.g_ret_sts_success;
539
540 --Delete MTL Sales Order Records
541 /*Bug3575085:Changed the sequence of the sub-query from b.name,a.order_number
542 to a.order_number,b.name*/
543 --Bug4237769 Correcting the type mismatch.
544 FORALL i IN 1..P_ERROR_REC.header_id.COUNT
545 DELETE from mtl_sales_orders
546 WHERE (segment1, segment2) IN
547 (select to_char(a.order_number),b.name
548 FROM oe_order_headers a,
549 oe_order_types_v b
550 WHERE a.header_id = p_error_rec.header_id(i)
551 AND a.order_type_id = b.order_type_id);
552
553
554 EXCEPTION
555 when others then
556 fnd_msg_pub.count_and_get(
557 p_count => x_message_count
558 , p_data => x_message_data
559 , p_encoded => 'F');
560
561 OE_MSG_PUB.Add_Exc_Msg(g_package_name, c_api_name, x_message_data);
562 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
563
564 END delete_mtl_sales_orders_bulk;
565
566 end inv_salesorder ;