[Home] [Help]
PACKAGE BODY: APPS.OE_BIS
Source
1 PACKAGE BODY OE_BIS AS
2 /* $Header: OEXBISRB.pls 115.6 2003/04/02 02:25:49 sphatarp ship $ */
3 function get_daily_value_shipped (p_day IN DATE,
4 p_warehouse_id IN NUMBER,
5 p_currency IN VARCHAR2)
6 return NUMBER IS
7
8 p_daily_value NUMBER;
9
10 BEGIN
11
12 /*
13 select sum(
14 decode(gl_currency_api.rate_exists(line_currency,p_currency,
15 trunc(sysdate),'Corporate'),
16 'Y', gl_currency_API.get_rate(line_currency, p_currency,
17 trunc(sysdate),'Corporate'),
18 1) *
19 shipment_value)
20 into p_daily_value
21 from wsh_bis_fill_rate_v
22 where date_closed = p_day
23 and warehouse_id = p_warehouse_id;
24 */
25 select sum(shipment_value)
26 into p_daily_value
27 from wsh_bis_fill_rate_v
28 where date_closed = p_day
29 and warehouse_id = p_warehouse_id;
30
31 return( p_daily_value);
32
33 EXCEPTION
34 WHEN OTHERS THEN
35 return (0);
36
37 END;
38
39 function get_days_top_returns (p_day IN DATE,
40 p_org_id IN NUMBER)
41 return NUMBER IS
42
43 p_days_bookings NUMBER;
44 p_days_returns NUMBER;
45
46 BEGIN
47
48 begin
49 select count(*)
50 into p_days_bookings
51 from oe_bis_bookings_v
52 where customer_id in
53 (select customer_id from oe_bis_top_customers)
54 and trunc(booking_date) = p_day
55 and nvl(p_org_id,ou_id) = ou_id;
56
57 exception
58 WHEN NO_DATA_FOUND THEN
59 p_days_bookings :=0;
60 end;
61
62 begin
63 select count(*)
64 into p_days_returns
65 from so_lines_all l, so_headers_all h
66 where h.customer_id in
67 (select customer_id from oe_bis_top_customers)
68 and l.creation_date = p_day
69 and nvl(p_org_id,l.org_id) = l.org_id
70 and l.line_type_code = 'RETURN';
71
72 exception
73 WHEN NO_DATA_FOUND THEN
74 p_days_returns :=0;
75 end;
76
77 return( p_days_returns/ p_days_bookings * 100);
78
79 EXCEPTION
80 WHEN OTHERS THEN
81 return (0);
82
83 END;
84
85
86 function get_days_top_deliveries (p_day IN DATE,
87 p_org_id IN NUMBER)
88 return NUMBER IS
89
90 delivery_percent NUMBER;
91
92 BEGIN
93
94 begin
95 select sum(decode(trunc(promise_date),
96 trunc(date_closed),1,0)) /
97 count(*) * 100
98 into delivery_percent
99 from wsh_bis_fill_rate_v
100 where customer_id in
101 (select customer_id
102 from oe_bis_top_customers)
103 and promise_date = p_day
104 and nvl(p_org_id,ou_id) = ou_id;
105
106 exception
107 WHEN NO_DATA_FOUND THEN
108 delivery_percent :=0;
109 end;
110
111 return( delivery_percent);
112
113 EXCEPTION
114 WHEN OTHERS THEN
115 return (0);
116
117 END;
118
119
120
121 Procedure GET_TOP_CUSTOMERS ( P_PERIOD_START DATE,
122 P_PERIOD_END DATE,
123 P_CURRENCY VARCHAR2,
124 P_ORG_ID NUMBER ) IS
125
126 cursor cust is
127 select customer_id,
128 sum(
129 decode(gl_currency_api.rate_exists(line_currency,p_currency,
130 trunc(sysdate),'Corporate'),
131 'Y', gl_currency_API.get_rate(line_currency, p_currency,
132 trunc(sysdate),'Corporate'),
133 1) *
134 (ordered_quantity - cancelled_quantity) *
135 unit_selling_price) sales
136 from oe_bis_bookings_v
137 where booking_date between
138 to_date('01-01-'||to_char(sysdate,'YYYY'),'DD-MM-YYYY')
139 and sysdate
140 and nvl(p_org_id,ou_id) = ou_id
141 group by customer_id
142 order by sales desc;
143
144 counter number :=0;
145
146 begin
147
148 -- lock table oe_bis_top_customers in exclusive mode nowait;
149
150 delete from oe_bis_top_customers;
151 commit;
152
153 FOR custrec in cust LOOP
154
155 insert into oe_bis_top_customers (
156 customer_id,
157 ytd_sales,
158 currency_code,
159 period_start,
160 period_end,
161 organization_id,
162 creation_date)
163 values
164 (custrec.customer_id,
165 custrec.sales,
166 p_currency,
167 p_period_start,
168 p_period_end,
169 p_org_id,
170 sysdate );
171
172
173 counter := counter + 1;
174 IF (counter = 10) then
175 EXIT;
176 END IF;
177
178
179 END LOOP;
180
181 update oe_bis_top_customers c
182 set period_bookings =
183 (select sum(
184 decode(gl_currency_api.rate_exists(line_currency,p_currency,
185 trunc(sysdate),'Corporate'),
186 'Y', gl_currency_API.get_rate(line_currency, p_currency,
187 trunc(sysdate),'Corporate'),
188 1) *
189 line_selling_price)
190 from oe_bis_bookings_v
191 where booking_date between p_period_start and p_period_end
192 and nvl(p_org_id,ou_id) = ou_id
193 and customer_id = c.customer_id);
194
195 update oe_bis_top_customers c set
196 period_billings =
197 (select sum(
198 decode(gl_currency_api.rate_exists(line_currency,p_currency,
199 trunc(sysdate),'Corporate'),
200 'Y', gl_currency_API.get_rate(line_currency, p_currency,
201 trunc(sysdate),'Corporate'),
202 1) *
203 invoiced_selling_price)
204 from oe_bis_billings_v
205 where invoicing_date between p_period_start and p_period_end
206 and nvl(p_org_id,ou_id) = ou_id
207 and customer_id = c.customer_id);
208
209 update oe_bis_top_customers c set
210 current_backlog =
211 (select sum(
212 decode(gl_currency_api.rate_exists(line_currency,p_currency,
213 trunc(sysdate),'Corporate'),
214 'Y', gl_currency_API.get_rate(line_currency, p_currency,
215 trunc(sysdate),'Corporate'),
216 1) *
217 bl_selling_price)
218 from oe_bis_backlog_v
219 where customer_id = c.customer_id
220 and nvl(p_org_id,ou_id) = ou_id);
221
222 --lchen remove alias on_time_perent from following subquery to fix bug 1753574
223
224 update oe_bis_top_customers c set
225 on_time_deliveries =
226 (select sum(decode(trunc(promise_date),
227 trunc(date_closed),1,0)) /
228 count(*) * 100
229 from wsh_bis_fill_rate_v
230 where customer_id = c.customer_id
231 and promise_date between p_period_start and p_period_end);
232
233 update oe_bis_top_customers c set
234 return_lines =
235 (select count(*)
236 from so_lines_all l, so_headers_all h
237 where l.header_id = h.header_id
238 and h.customer_id = c.customer_id
239 and l.line_type_code = 'RETURN'
240 and l.org_id = nvl(p_org_id,l.org_id)
241 and l.creation_date between
242 p_period_start and p_period_end);
243
244 update oe_bis_top_customers c set
245 order_lines =
246 (select count(distinct line_id)
247 from oe_bis_bookings_v
248 where customer_id = c.customer_id
249 and ou_id = nvl(p_org_id,ou_id)
250 and booking_date between p_period_start and p_period_end);
251
252 commit;
253
254 exception
255 when others then
256 -- dbms_output.put_line(SQLERRM);
257 null;
258 end get_top_customers;
259
260
261 Procedure GET_BBB_INFO ( P_PERIOD_START DATE,
262 P_PERIOD_END DATE,
263 P_CURRENCY VARCHAR2,
264 P_ORG_ID NUMBER ) IS
265
266 begin
267
268 delete from oe_bis_bbb_info;
269
270 insert into oe_bis_bbb_info ( period_year,
271 period_num,
272 period_name,
273 period_start,
274 period_end,
275 currency_code,
276 ou_id )
277 select gp.period_year,
278 gp.period_num,
279 gp.period_name,
280 gp.start_date,
281 gp.end_date,
282 p_currency,
283 p_org_id
284 from gl_periods gp
285 where gp.period_type = 'Month'
286 and gp.adjustment_period_flag = 'N'
287 and gp.period_set_name = 'Accounting'
288 and ((p_period_start <= gp.start_date
289 and p_period_end >= gp.end_date)
290 or (p_period_end between gp.start_date and gp.end_date));
291
292 update oe_bis_bbb_info bbb
293 set bookings =
294 (select sum(
295 decode(gl_currency_api.rate_exists(line_currency, bbb.currency_code,
296 trunc(sysdate),'Corporate'),
297 'Y', gl_currency_API.get_rate(line_currency, bbb.currency_code,
298 trunc(sysdate),'Corporate'),
299 1) *
300 line_selling_price)
301 from oe_bis_bookings_v
302 where booking_date between bbb.period_start and bbb.period_end
303 and nvl(p_org_id,ou_id) = ou_id);
304
305 update oe_bis_bbb_info bbb set
306 billings =
307 (select sum(
308 decode(gl_currency_api.rate_exists(line_currency,bbb.currency_code,
309 trunc(sysdate),'Corporate'),
310 'Y', gl_currency_API.get_rate(line_currency, bbb.currency_code,
311 trunc(sysdate),'Corporate'),
312 1) *
313 invoiced_selling_price)
314 from oe_bis_billings_v
315 where invoicing_date between bbb.period_start and bbb.period_end
316 and nvl(p_org_id,ou_id) = ou_id);
317
318 update oe_bis_bbb_info bbb set
319 adjustments =
320 (select sum(
321 decode(gl_currency_api.rate_exists(currency_code,bbb.currency_code,
322 trunc(sysdate),'Corporate'),
323 'Y', gl_currency_API.get_rate(currency_code, bbb.currency_code,
324 trunc(sysdate),'Corporate'),
325 1) *
326 selling_price * cancelled_quantity)
327 from oe_bis_cancelled_bookings_v
328 where cancel_date between bbb.period_start and bbb.period_end
329 and nvl(p_org_id,ou_id) = ou_id);
330
331
332 update oe_bis_bbb_info bbb set
333 closing_backlog =
334 (select sum(
335 decode(gl_currency_api.rate_exists(line_currency, bbb.currency_code,
336 trunc(sysdate),'Corporate'),
337 'Y', gl_currency_API.get_rate(line_currency, bbb.currency_code,
338 trunc(sysdate),'Corporate'),
339 1) *
340 bl_selling_price)
341 from oe_bis_backlog_v
342 where (invoiced_flag = 'N' or invoiced_date > bbb.period_end)
343 and booked_date <= bbb.period_end+1
344 and nvl(p_org_id,ou_id) = ou_id);
345
346
347 commit;
348
349 exception
350 when others then
351 -- dbms_output.put_line(SQLERRM);
352 null;
353 end get_bbb_info;
354
355
356
357 END;