1 PACKAGE BODY OE_DEMAND_STREAM_PROCESSOR AS
2 /* $Header: OEXDSPXB.pls 115.0 99/07/16 08:12:20 porting ship $ */
3
4 --
5 -- NAME: Order_Info.
6 --
7 -- DESCRIPTION:
8 --
9 -- This routine takes an order number and order type name
10 -- and returns the header_id, order_type_id, customer_id and open_flag
11 -- for that order.
12 --
13 -- Also, the entry status is returned which has the following values:
14 --
15 -- 'BOOKED', 'ENTERED', 'PARTIAL' or the value of the s1 column if users
16 -- define something other than the above.
17 --
18 -- RETURN VALUES:
19 --
20 -- x_result: 'Y' if success, 'N' if failure.
21
22 -- x_sqlcode: The SQLCODE for the statement that failed. Typically, this
23 -- will be NO_DATA_FOUND if the order_number, order_type
24 -- combination does not exist in the database.
25 --
26 -- NOTE: x_sqlcode is not applicable when x_result is 'Y'
27 --
28
29 PROCEDURE order_info
30 (
31 x_order_number IN NUMBER,
32 x_order_type_name IN VARCHAR2,
33 x_header_id OUT NUMBER,
34 x_order_type_id OUT NUMBER,
35 x_customer_id OUT NUMBER,
36 x_open_flag OUT VARCHAR2,
37 x_entry_status OUT VARCHAR2,
38 x_po_number OUT VARCHAR2,
39 x_result OUT VARCHAR2,
40 x_sqlcode OUT NUMBER
41 ) IS
42 BEGIN
43
44 -- Initialize results
45
46 x_header_id := NULL;
47 x_order_type_id := NULL;
48 x_customer_id := NULL;
49 x_open_flag := NULL;
50 x_entry_status := NULL;
51 x_po_number := NULL;
52
53 SELECT soh.header_id,
54 soh.order_type_id,
55 soh.customer_id,
56 soh.open_flag,
57 decode (soh.s1,
58 15, 'ENTERED',
59 5, 'PARTIAL',
60 1, 'BOOKED',
61 to_char(soh.s1)),
62 purchase_order_num
63 INTO
64 x_header_id,
65 x_order_type_id,
66 x_customer_id,
67 x_open_flag,
68 x_entry_status,
69 x_po_number
70 FROM
71 so_headers soh,
72 so_order_types sot
73 WHERE
74 sot.order_type_id = soh.order_type_id
75 AND sot.name = x_order_type_name
76 AND soh.order_number = x_order_number;
77
78 -- Return success
79
80 x_result := 'Y';
81 x_sqlcode := 0;
82 RETURN;
83
84 EXCEPTION
85 WHEN OTHERS THEN
86
87 -- Return failure
88
89 x_result := 'N';
90 x_sqlcode := SQLCODE;
91 x_header_id := NULL;
92 x_order_type_id := NULL;
93 x_customer_id := NULL;
94 x_open_flag := NULL;
95 x_entry_status := NULL;
96 x_po_number := NULL;
97
98 RETURN;
99
100 END order_info;
101
102 --
103 -- NAME: Order_Info.
104 --
105 -- DESCRIPTION:
106 --
107 -- This routine takes a header_id and returns the order number, order type
108 -- and open_flag for that order.
109 --
110 -- Also, the entry status is returned which has the following values:
111 --
112 -- 'BOOKED', 'ENTERED', 'PARTIAL' or the value of the s1 column if users
113 -- define something other than the above.
114 --
115 -- RETURN VALUES:
116 --
117 -- x_result: 'Y' if success, 'N' if failure.
118
119 -- x_sqlcode: The SQLCODE for the statement that failed. Typically, this
120 -- will be NO_DATA_FOUND if the order_number, order_type
121 -- combination does not exist in the database.
122 --
123 -- NOTE: x_sqlcode is not applicable when x_result is 'Y'
124 --
125
126 PROCEDURE order_info
127 (
128 x_header_id IN NUMBER,
129 x_order_number OUT NUMBER,
130 x_order_type_name OUT VARCHAR2,
131 x_open_flag OUT VARCHAR2,
132 x_entry_status OUT VARCHAR2,
133 x_po_number OUT VARCHAR2,
134 x_result OUT VARCHAR2,
135 x_sqlcode OUT NUMBER
136 ) is
137 begin
138
139 -- Initialize results
140
141 x_order_number := NULL;
142 x_order_type_name := NULL;
143 x_open_flag := NULL;
144 x_entry_status := NULL;
145 x_po_number := NULL;
146
147
148 SELECT soh.order_number,
149 sot.name,
150 soh.open_flag,
151 decode (soh.s1,
152 15, 'ENTERED',
153 5, 'PARTIAL',
154 1, 'BOOKED',
155 to_char(soh.s1)),
156 soh.purchase_order_num
157 INTO
158 x_order_number,
159 x_order_type_name,
160 x_open_flag,
161 x_entry_status,
162 x_po_number
163 FROM
164 so_headers soh,
165 so_order_types sot
166 WHERE
167 sot.order_type_id = soh.order_type_id
168 AND soh.header_id = x_header_id;
169
170 -- Return success
171
172 x_result := 'Y';
173 x_sqlcode := 0;
174 RETURN;
175
176 EXCEPTION
177 WHEN OTHERS THEN
178
179 -- Return failure
180
181 x_result := 'N';
182 x_sqlcode := SQLCODE;
183 x_order_number := NULL;
184 x_order_type_name := NULL;
185 x_open_flag := NULL;
186 x_entry_status := NULL;
187 x_po_number := NULL;
188
189 RETURN;
190
191 end order_info;
192
193
194 PROCEDURE delete_interface_records
195 (
196 x_order_source_id IN NUMBER,
197 x_original_system_reference IN VARCHAR2,
198 x_request_id IN NUMBER Default NULL
199 ) is
200 begin
201
202 delete
203 from so_lines_interface
204 where order_source_id = x_order_source_id
205 and original_system_reference = x_original_system_reference
206 and nvl(request_id, -99999) = nvl(x_request_id, nvl(request_id, -99999));
207
208 delete
212 and nvl(request_id, -99999) = nvl(x_request_id, nvl(request_id, -99999));
209 from so_line_details_interface
210 where order_source_id = x_order_source_id
211 and original_system_reference = x_original_system_reference
213
214 delete
215 from so_price_adjustments_interface
216 where order_source_id = x_order_source_id
217 and original_system_reference = x_original_system_reference
218 and nvl(request_id, -99999) = nvl(x_request_id, nvl(request_id, -99999));
219
220 delete
221 from so_sales_credits_interface
222 where order_source_id = x_order_source_id
223 and original_system_reference = x_original_system_reference
224 and nvl(request_id, -99999) = nvl(x_request_id, nvl(request_id, -99999));
225
226 delete
227 from so_service_details_interface
228 where order_source_id = x_order_source_id
229 and original_system_reference = x_original_system_reference
230 and nvl(request_id, -99999) = nvl(x_request_id, nvl(request_id, -99999));
231
232 delete
233 from so_headers_interface
234 where order_source_id = x_order_source_id
235 and original_system_reference = x_original_system_reference
236 and nvl(request_id, -99999) = nvl(x_request_id, nvl(request_id, -99999));
237
238 exception
239 when others then
240 Raise;
241
242 end delete_interface_records;
243
244
245
246
247
248 --
249 -- NAME: Lock_Demand_Stream
250 --
251 -- DESCRIPTION: This routine will obtain all the locks necessary for a particular
252 -- Releaese Accounting Demand Stream. The following data gets locked for the
253 -- demand_stream_id passed in as the argument.
254 --
255 -- * Open ATO Lines that have not yet been manufacturing released.
256 -- * Other open Lines that have not yet been fully pick released.
257 -- * Unreleased Line Details for these lines.
258 -- * Backordered picking lines for this demand_stream_id that have not been
259 -- fully backorder released.
260 -- * Unreleased backordered picking line details for these backordered
261 -- picking lines.
262 --
263 -- RETURN VALUES:
264 --
265 -- Returns the SQLCODE of the locking statement. This should be checked to
266 -- see what was the reason for lock failure, or whether the lock request succeeded.
267 --
268
269 FUNCTION lock_demand_stream
270 (
271 x_demand_stream_id IN NUMBER
272 ) RETURN NUMBER IS
273
274 CURSOR lock_lin_and_det(p_demand_stream_id NUMBER) IS
275 SELECT
276 lin.line_id,
277 det.line_detail_id
278 FROM
279 so_line_details det,
280 so_lines lin
281 WHERE
282 Nvl(det.released_flag, 'N') = 'N'
283 AND lin.line_id = det.line_id (+)
284 AND Decode(lin.s27, -- Manufacturing Release
285 NULL, 'LOCK', -- Lock if not reached
286 18, 'LOCK', -- Lock if eligible
287 8, 'LOCK', -- Lock if not applicable
288 'DONT_LOCK') = 'LOCK' -- Don't lock otherwise
289 AND lin.open_flag || '' = 'Y'
290 AND lin.demand_stream_id = p_demand_stream_id
291 FOR UPDATE OF lin.line_id, det.line_detail_id NOWAIT;
292
293 CURSOR lock_backordered_lin_and_det(p_order_line_id NUMBER) IS
294 SELECT
295 pln.picking_line_id,
299 so_picking_line_details pld
296 pld.picking_line_detail_id
297 FROM
298 so_picking_lines pln,
300 WHERE
301 pln.picking_header_id = 0
302 AND nvl(pld.released_flag, 'N') = 'N'
303 AND pln.picking_line_id = pld.picking_line_id
304 AND pln.order_line_id = p_order_line_id
305 FOR UPDATE OF pln.picking_line_id, pld.picking_line_detail_id NOWAIT;
306
307
308 temp_line_id NUMBER := NULL;
309 temp_line_detail_id NUMBER := NULL;
310
311 prev_line_id NUMBER := NULL;
312
313 return_code NUMBER := 0;
314
315 BEGIN
316
317 --
318 -- Establish a savepoint, so that if there is any locking error, we can
319 -- rollback to this savepoint, hence releasing all locks obtained by
320 -- this routine.
321 --
322
323 SAVEPOINT lock_dem_str;
324
325 --
326 -- Lock the lines and details for this demand_stream_id. The open statement
327 -- will do this for you.
328 --
329
330 OPEN lock_lin_and_det(x_demand_stream_id);
331
332 --
333 -- Loop through the above cursor and for each new line_id, lock all of its
334 -- backordered, unreleased picking lines and details.
335 --
336
337 FETCH lock_lin_and_det INTO temp_line_id, temp_line_detail_id;
338
339 WHILE lock_lin_and_det%FOUND LOOP
340
341 --
342 -- We do not need to fetch anything from this cursor. The open
343 -- statement will obtain the lock for us.
344 --
345
346 OPEN lock_backordered_lin_and_det(temp_line_id);
347 CLOSE lock_backordered_lin_and_det;
348
349 prev_line_id := temp_line_id;
350
351 WHILE prev_line_id = temp_line_id AND lock_lin_and_det%FOUND LOOP
352 FETCH lock_lin_and_det INTO temp_line_id, temp_line_detail_id;
353 END LOOP;
354
355 END LOOP;
356
357
358 CLOSE lock_lin_and_det;
359
360 -- At this point, we have successfully locked all four tables, return 0 for success.
361
362 RETURN 0;
363
364 EXCEPTION
365
366 -- If any of the cursors find nothing, it is still a success.
367
368 WHEN NO_DATA_FOUND THEN
369
370 IF lock_lin_and_det%isopen THEN
371 CLOSE lock_lin_and_det;
372 END IF;
373
374 IF lock_backordered_lin_and_det%isopen THEN
375 CLOSE lock_backordered_lin_and_det;
376 END IF;
377
378
379 RETURN 0;
380
381 --
385
382 -- If there are any other exceptions, including locks not obtained,
383 -- return the SQLCODE after rolling back to the savepoint.
384 --
386 WHEN OTHERS THEN
387
388 return_code := SQLCODE;
389
390 IF lock_lin_and_det%ISOPEN THEN
391 CLOSE lock_lin_and_det;
392 END IF;
393
394 IF lock_backordered_lin_and_det%ISOPEN THEN
395 CLOSE lock_backordered_lin_and_det;
396 END IF;
397
398 ROLLBACK TO lock_dem_str;
399
400 RETURN return_code;
401
402 END lock_demand_stream;
403
404
405 --
406 -- NAME: next_line_number
407 --
408 -- DESCRIPTION: This routine takes a header_id for an order and returns the
409 -- next line number for the lines of that order. This will be
410 -- used by release accounting to get new line number when they
411 -- are inserting lines in interface tables.
412 --
413 -- NOTE: This routine assumes that the header_id exists in the
414 -- database.
415 --
416 -- RETURN VALUES:
417 -- x_result: 'Y' if success, 'N' if failure.
418 -- x_sqlcode: The SQLCODE for the statement that failed.
419 --
420 -- NOTE: x_sqlcode is not applicable when x_result is 'Y'
421 --
422
423 PROCEDURE next_line_number
424 (
425 x_header_id IN NUMBER,
426 x_line_number OUT NUMBER,
427 x_result OUT VARCHAR2,
428 x_sqlcode OUT NUMBER
429 ) IS
430 BEGIN
431
432 x_line_number := NULL;
433 x_sqlcode := 0;
434 x_result := 'Y';
435
436 SELECT
437 Nvl(MAX(line_number), 0) + 1
438 INTO
439 x_line_number
440 FROM
441 so_lines
442 WHERE
443 header_id = x_header_id
444 AND shipment_schedule_line_id IS NULL
445 AND parent_line_id IS NULL
446 AND service_parent_line_id IS NULL;
447
448 RETURN;
449
450
451 EXCEPTION
452 WHEN OTHERS THEN
453
454 x_sqlcode := SQLCODE;
455 x_result := 'N';
456 x_line_number := NULL;
457
458 RETURN;
459
460
461 END next_line_number;
462
463
464
465 --
466 -- NAME: set_original_system_reference
467 --
468 -- DESCRIPTION: This routine takes a header_id and updates the
469 -- original_system_source_code and original_system_reference information
470 -- as specified by the x_order_source_id and x_original_system_reference.
471 --
472 -- If no original_system_reference is passed, the system generates an
473 -- automatic original_system_reference by concatening Order Number, Order
474 -- Type.
478 -- original_system_reference already exist in the database.
475 --
476 -- Raises an exception OE_DEMAND_STREAM_PROCESSOR.Original_Sys_Ref_Exists
477 -- if the eventual combination of original_system_source_code and
479 -- The calling program must handle this exception.
480 --
481 -- RETURN_VALUES:
482 -- x_result: 'Y' if success, 'N' if failure.
483 -- x_sqlcode: The SQLCODE for the statement that failed.
484 --
485
486 FUNCTION set_original_system_reference
487 (
488 x_header_id IN NUMBER,
489 x_order_source_id IN NUMBER,
490 x_original_system_reference IN VARCHAR2 DEFAULT NULL,
491 x_result OUT VARCHAR2,
492 x_sqlcode OUT NUMBER
493 ) RETURN VARCHAR2 IS
494
495 l_original_system_reference VARCHAR2(50) := NULL;
496 l_original_system_source_code VARCHAR2(30) := NULL;
497 l_order_number NUMBER := NULL;
498 l_order_type VARCHAR2(30) := NULL;
499 BEGIN
500
501 -- Put a savepoint so we can release the lock in case of errors;
502
503 SAVEPOINT set_original_system_reference;
504
505 -- lock the order we want to operate on.
506
507 SELECT original_system_source_code, original_system_reference
508 INTO l_original_system_source_code, l_original_system_reference
509 FROM
510 so_headers
511 WHERE
512 header_id = x_header_id
513 FOR UPDATE OF
514 original_system_source_code;
515
516 --
517 -- If either original_system_source_code or original_system_reference
518 -- already exist, then exit with success.
519 -- Exception to the above rule, copied orders. If you copy an
520 -- order and then want to use it for automotive, we will override the
521 -- source order informaiton with the automotive information.
522 -- ORIGINAL_SYSTEM_SOURCE_CODE FOR COPIED ORDERS IS '2'
523
524 IF ((l_original_system_source_code <> '2') and
525 (l_original_system_source_code IS NOT NULL or
526 l_original_system_reference IS NOT NULL)) THEN
527
528 x_result := 'Y';
529 x_sqlcode := 0;
530 RETURN l_original_system_reference;
531
532 END IF;
533
534 -- Both original_system_source_code and original_system_reference is NULL.
535 -- Now we get the order type information.
536
537 SELECT h.order_number,
538 t.name
539 INTO
540 l_order_number,
541 l_order_type
542 FROM
543 so_headers h,
544 so_order_types t
545 WHERE
546 h.order_type_id = t.order_type_id
547 AND h.header_id = x_header_id;
548
549
550 l_original_system_source_code := to_char(x_order_source_id);
551 l_original_system_reference := x_original_system_reference;
552
553 -- If the order has no reference or it is a copied order
554 IF (l_original_system_reference IS NULL
555 OR
556 l_original_system_source_code = '2')
557 THEN
558
559 l_original_system_reference := Substr(To_char(l_order_number) ||
560 ', ' ||
561 l_order_type, 1, 50);
565
562
563 l_original_system_source_code := NULL;
564 fnd_profile.get('RLA_ORDERIMPORT_SOURCE', l_original_system_source_code);
566 END IF;
567
568
569
570 --
571 -- We NULL out the source header_id just to be safe. When automotive
572 -- interfaces lines into a copied order, they no longer want to track
573 -- the original order that the header was copied from
574 --
575
576 UPDATE so_headers
577 SET original_system_source_code = l_original_system_source_code,
578 original_system_reference = l_original_system_reference,
579 source_header_id = NULL
580 WHERE
581 header_id = x_header_id
582 AND not exists
583 (SELECT 'x'
584 FROM so_headers
585 WHERE original_system_reference = l_original_system_reference
586 AND original_system_source_code = l_original_system_source_code);
587
588 IF SQL%NOTFOUND THEN
589 RAISE OE_DEMAND_STREAM_PROCESSOR.Original_Sys_Ref_Exists;
590 END IF;
591
592 x_result := 'Y';
593 x_sqlcode := 0;
594
595 RETURN l_original_system_reference;
596
597 EXCEPTION
598
599 WHEN OE_DEMAND_STREAM_PROCESSOR.Original_Sys_Ref_Exists THEN
600
601 x_sqlcode := SQLCODE;
602 x_result := 'N';
603
604 ROLLBACK TO SAVEPOINT set_original_system_reference;
605
606 RAISE OE_DEMAND_STREAM_PROCESSOR.Original_Sys_Ref_Exists;
607
608 WHEN OTHERS THEN
609
610 x_sqlcode := SQLCODE;
611 x_result := 'N';
612
613 ROLLBACK TO SAVEPOINT set_original_system_reference;
614
615 RETURN NULL;
616
617 END set_original_system_reference;
618
619
620 --
621 -- NAME: line_scheduling_exists
622 --
623 -- DESCRIPTION: This routine takes a line_id and determines whether
624 -- any scheduling exists for this line or any of its components.
625 --
626 -- Release Accounting uses this information to determine whether or
627 -- not they can change order quantities on order lines through order
628 -- import.
629 --
630 -- RETURNS:
631 --
632 -- 'Y' if scheduling exists i.e. any line detail of this line or a
633 -- component is demanded, reserved or supply reserved.
634 -- Changes to quantities are not allowed
635 --
636 -- 'N' if scheduling does not exist for this line or any of its
637 -- components. Changes to quantities will be accepted.
638 --
639
640 FUNCTION line_scheduling_exists
641 (
642 x_line_id IN NUMBER
643 ) RETURN VARCHAR2 is
644
645 l_scheduling_exists VARCHAR2(1) := 'N';
646
647 begin
648
649 SELECT 'Y'
650 INTO l_scheduling_exists
651 FROM so_line_details
652 WHERE line_id in
653 (
654 SELECT line_id
655 FROM so_lines
656 WHERE (line_id = x_line_id
657 OR parent_line_id = x_line_id)
658 )
662 return l_scheduling_exists;
659 AND schedule_status_code is NOT NULL
660 AND rownum = 1;
661
663
664 exception
665
666 WHEN NO_DATA_FOUND THEN
667 return 'N';
668
669 end line_scheduling_exists;
670
671
672 END OE_DEMAND_STREAM_PROCESSOR;
[Home] [Help]