1 PACKAGE BODY HRI_BPL_OE_ORDER AS
2 /* $Header: hriboeod.pkb 120.0 2005/05/29 07:03:43 appldev noship $ */
3 --globals to cache
4 g_line_number oe_order_lines_all.line_number%type;
5 g_order_number oe_order_headers_all.order_number%type;
6 g_line_id oe_order_lines_all.line_id%type;
7 g_header_id oe_order_headers_all.header_id%type;
8
9 PROCEDURE UPDATE_GLOBALS(p_line_id IN NUMBER)
10 IS
11 CURSOR csr_line_order
12 IS
13 select
14 ool.header_id l_header_id,
15 ool.line_number l_line_number,
16 ooh.order_number l_order_number
17 from
18 oe_order_headers_all ooh,
19 oe_order_lines_all ool
20 where
21 p_line_id = ool.line_id
22 AND ool.header_id = ooh.header_id;
23
24 l_rec csr_line_order%ROWTYPE;
25
26 BEGIN
27 open csr_line_order;
28 fetch csr_line_order into l_rec;
29 -- update the cache.
30 g_line_id := p_line_id;
31 g_line_number := l_rec.l_line_number;
32 g_header_id := l_rec.l_header_id;
33 g_order_number := l_rec.l_order_number;
34 close csr_line_order;
35 END UPDATE_GLOBALS;
36
37 --
38 -- Function returns Line Number for corrosponding Line_id
39 -- To improve performance it teturns a cached value if it can
40 --
41 FUNCTION GET_LINE_NUMBER
42 (p_line_id IN NUMBER
43 )
44 RETURN NUMBER
45 IS
46 BEGIN
47 IF (g_line_id = p_line_id) THEN
48 -- cache hit, already have the Line Number cached
49 RETURN g_line_number;
50 ELSE
51 -- cache miss, get the Line Number.
52 UPDATE_GLOBALS
53 (p_line_id
54 );
55 RETURN g_line_number;
56 END IF;
57 END GET_LINE_NUMBER;
58
59 --
60 -- Function returns Order Number for corrosponding Line_id
61 -- To improve performance it teturns a cached value if it can
62 --
63 FUNCTION GET_ORDER_NUMBER
64 (p_line_id IN NUMBER
65 )
66 RETURN NUMBER
67 IS
68 BEGIN
69 IF (g_line_id = p_line_id) THEN
70 -- cache hit, already have the Order Number cached
71 RETURN g_order_number;
72 ELSE
73 -- cache miss, get the Order Number.
74 UPDATE_GLOBALS
75 (p_line_id
76 );
77 RETURN g_order_number;
78 END IF;
79 END GET_ORDER_NUMBER;
80
81 --
82 -- Function returns Header id for corrosponding Line_id
83 -- To improve performance it teturns a cached value if it can
84 --
85 FUNCTION GET_HEADER_ID
86 (p_line_id IN NUMBER
87 )
88 RETURN NUMBER
89 IS
90 BEGIN
91 IF (g_line_id = p_line_id) THEN
92 -- cache hit, already have the header_id cached
93 RETURN g_header_id;
94 ELSE
95 -- cache miss, get the header_id.
96 UPDATE_GLOBALS
97 (p_line_id
98 );
99 RETURN g_header_id;
100 END IF;
101 END GET_HEADER_ID;
102
103 END HRI_BPL_OE_ORDER;