1 PACKAGE BODY OKI_DISCO_UTIL_PVT AS
2 /* $Header: OKIRDULB.pls 115.4 2002/12/02 22:22:33 rpotnuru noship $ */
3 ----------------------------------------------------------------------------
4 -- The following function derives the number of periods based
5 -- on organization, start and end date.
6 ----------------------------------------------------------------------------
7
8 FUNCTION get_num_periods(
9 p_sob_id IN NUMBER,
10 p_start_date in date,
11 p_end_date in date
12 ) RETURN NUMBER is
13 l_num_periods NUMBER;
14 begin
15 BEGIN
16 select count(1)
17 into l_num_periods
18 from gl_periods gp, gl_sets_of_books sob
19 where 1 = 1
20 and sob.set_of_books_id = p_sob_id
21 and gp.period_set_name = sob.period_set_name
22 and gp.period_type = sob.accounted_period_type
23 and trunc(gp.end_date) >= p_start_date
24 and trunc(gp.start_date) <= p_end_date
25 and gp.adjustment_period_flag = 'N';
26 IF(l_num_periods = 0)
27 THEN
28 l_num_periods := -1;
29 END IF;
30 EXCEPTION
31 WHEN OTHERS THEN
32 l_num_periods := -1;
33 END;
34 return l_num_periods;
35 END get_num_periods;
36
37 ----------------------------------------------------------------------------
38 -- The following function returns the original license order number
39 -- based on the chr_id
40 ----------------------------------------------------------------------------
41 FUNCTION get_order_number(
42 p_chr_id IN NUMBER)
43 RETURN VARCHAR2 IS
44 l_order_number VARCHAR2(120);
45 BEGIN
46 BEGIN
47 select order_number
48 into l_order_number
49 from okx_order_headers_v oh,
50 okc_k_rel_objs_v ro
51 where ro.chr_id = p_chr_id
52 and ro.cle_id is null
53 and ro.object1_id1 = oh.id1;
54 EXCEPTION
55 WHEN OTHERS THEN
56 l_order_number := NULL;
57 END;
58 return l_order_number;
59 END get_order_number;
60
61 ----------------------------------------------------------------------------
62 -- The following function derives the most suitable period based
63 -- on a start and end date.
64 -- This function should be used in conjunction with get_duration function
65 ----------------------------------------------------------------------------
66 FUNCTION get_period(
67 p_start_date IN DATE ,
68 p_end_date IN DATE )
69 RETURN VARCHAR2 IS
70 l_duration number;
71 l_timeunit varchar2(100);
72 l_return_status varchar2(100);
73 BEGIN
74 OKI_DISCO_UTIL_PUB.g_start_date := p_start_date;
75 OKI_DISCO_UTIL_PUB.g_end_date := p_end_date;
76 okc_time_util_pvt.get_duration(
77 p_start_date,
78 p_end_date,
79 l_duration,
80 l_timeunit,
81 l_return_status);
82 OKI_DISCO_UTIL_PUB.g_duration := l_duration;
83 OKI_DISCO_UTIL_PUB.g_period := l_timeunit;
84 return l_timeunit;
85 END get_period;
86 ----------------------------------------------------------------------------
87 -- The following function derives the most suitable duration based
88 -- on a start and end date.
89 -- This function should be used in conjunction with get_period function
90 ----------------------------------------------------------------------------
91
92 FUNCTION get_duration(
93 p_start_date IN DATE ,
94 p_end_date IN DATE )
95 RETURN NUMBER IS
96 l_duration number;
97 l_timeunit varchar2(100);
98 l_return_status varchar2(100);
99 BEGIN
100 OKI_DISCO_UTIL_PUB.g_start_date := p_start_date;
101 OKI_DISCO_UTIL_PUB.g_end_date := p_end_date;
102 okc_time_util_pvt.get_duration(
103 p_start_date,
104 p_end_date,
105 l_duration,
106 l_timeunit,
107 l_return_status);
108 OKI_DISCO_UTIL_PUB.g_duration := l_duration;
109 OKI_DISCO_UTIL_PUB.g_period := l_timeunit;
110 return l_duration;
111 END get_duration;
112
113 ----------------------------------------------------------------------------
114 -- The following function derives the aanualized amount based
115 -- on amount, start and end date.
116 ----------------------------------------------------------------------------
117 FUNCTION get_annualized_amount(
118 p_amount IN NUMBER,
119 p_start_date IN DATE,
120 p_end_date IN DATE)
121 RETURN NUMBER IS
122 l_annualized_amount NUMBER;
123 BEGIN
124 l_annualized_amount := p_amount / (trunc(p_end_date)+0.99999-trunc(p_start_date)) * 365;
125 return l_annualized_amount;
126 EXCEPTION
127 WHEN OTHERS THEN
128 return 0;
129 END get_annualized_amount;
130
131 ----------------------------------------------------------------------------
132 -- The following function derives current month revenue for the given
133 -- chr_id, sob_id, End date.
134 ----------------------------------------------------------------------------
135 FUNCTION get_cur_month_rev(
136 p_chr_id IN NUMBER,
137 p_sob_id IN NUMBER,
138 p_end_date IN DATE)
139 RETURN NUMBER IS
140 l_cur_month_rev NUMBER;
141 l_period_start_date DATE;
142 l_period_end_date DATE;
143 BEGIN
144
145 /* get period start and end dates */
146 select trunc(gp.start_date), trunc(gp.end_date)
147 into l_period_start_date, l_period_end_date
148 from gl_periods gp, gl_sets_of_books sob
149 where 1 = 1
150 and sob.set_of_books_id = p_sob_id
151 and gp.period_set_name = sob.period_set_name
152 and gp.period_type = sob.accounted_period_type
153 and p_end_date between gp.start_date and gp.end_date
154 and gp.adjustment_period_flag = 'N';
155
156 /* Select current month revenue */
157 select
158 SUM(
159 cpl.price_negotiated
160 * months_between(
161 trunc(least(l_period_end_date,cpl.end_date))+0.99999,
162 trunc(greatest(l_period_start_date,cpl.start_date))
163 )
164 / months_between(trunc(cpl.end_date)+0.99999,trunc(cpl.start_date))
165 )
166 into l_cur_month_rev
167 from oki_cov_prd_lines cpl
168 where 1 = 1
169 and cpl.chr_id = p_chr_id
170 and cpl.start_date <= l_period_end_date+0.99999
171 and cpl.end_date >= l_period_start_date
172 and cpl.ste_code = 'ENTERED';
173 return l_cur_month_rev;
174 EXCEPTION
175 WHEN NO_DATA_FOUND THEN
176 l_cur_month_rev := 0;
177 return l_cur_month_rev;
178 END get_cur_month_rev;
179
180 ----------------------------------------------------------------------------
181 -- The following function derives backdated revenue for the given
182 -- chr_id, sob_id, End date.
183 ----------------------------------------------------------------------------
184 FUNCTION get_backdated_rev(
185 p_chr_id IN NUMBER,
186 p_sob_id IN NUMBER,
187 p_end_date IN DATE)
188 RETURN NUMBER IS
189 l_backdated_rev NUMBER;
190 l_period_start_date DATE;
191 l_period_end_date DATE;
192 BEGIN
193
194 /* get period start and end dates */
195 select trunc(gp.start_date), trunc(gp.end_date)
196 into l_period_start_date, l_period_end_date
197 from gl_periods gp, gl_sets_of_books sob
198 where 1 = 1
199 and sob.set_of_books_id = p_sob_id
200 and gp.period_set_name = sob.period_set_name
201 and gp.period_type = sob.accounted_period_type
202 and p_end_date between gp.start_date and gp.end_date
203 and gp.adjustment_period_flag = 'N';
204
205 /* Select back dated revenue */
206 select
207 SUM(
208 cpl.price_negotiated
209 *months_between(
210 trunc(least(l_period_start_date,cpl.end_date))+0.99999,
211 trunc(cpl.start_date)
212 )
213 / months_between(trunc(cpl.end_date)+0.99999,trunc(cpl.start_date))
214 )
215 into l_backdated_rev
216 from oki_cov_prd_lines cpl
217 where 1 = 1
218 and cpl.chr_id = p_chr_id
219 and cpl.start_date < l_period_start_date
220 and cpl.ste_code = 'ENTERED';
221
222 return l_backdated_rev;
223 EXCEPTION
224 WHEN NO_DATA_FOUND THEN
225 l_backdated_rev := 0;
226 return l_backdated_rev;
227 END get_backdated_rev;
228
229 END OKI_DISCO_UTIL_PVT;
[Home] [Help]