[Home] [Help]
PACKAGE BODY: APPS.ONT_PRT_MARGIN_ANA_PKG
Source
1 PACKAGE BODY ONT_PRT_MARGIN_ANA_PKG as
2 /* $Header: ONTCSMAB.pls 120.0 2005/06/01 01:59:00 appldev noship $ */
3 procedure BUILD_ONT_PRT_MARGIN_TABLE AS
4 records_processed number;
5 sum_margin cst_margin_summary.margin%TYPE;
6 CURSOR wkly_amt_cursor IS
7 SELECT
8 cms.build_id,
9 cms.customer_id,
10 cms.primary_salesrep_id,
11 cms.territory_id,
12 cms.inventory_item_id,
13 cms.org_id,
14 cms.sold_to_customer_name,
15 sum(invoiced_amount) sum_inv_amt,
16 sum(cogs_amount) sum_cogs_amt
17 FROM
18 CST_MARGIN_SUMMARY cms,
19 CST_MARGIN_BUILD cmb
20 WHERE
21 cms.build_id = cmb.build_id AND
22 cms.gl_date >= cmb.to_date - 7 AND
23 cms.build_id = (select max(build_id) from cst_margin_summary)
24 GROUP BY
25 cms.build_id,
26 cms.customer_id,
27 cms.primary_salesrep_id,
28 cms.territory_id,
29 cms.inventory_item_id,
30 cms.org_id,
31 cms.sold_to_customer_name
32 ;
33
34 CURSOR mnthly_amt_cursor IS
35 SELECT
36 cms.build_id,
37 cms.customer_id,
38 cms.primary_salesrep_id,
39 cms.territory_id,
40 cms.inventory_item_id,
41 cms.org_id,
42 cms.sold_to_customer_name,
43 sum(invoiced_amount) sum_inv_amt,
44 sum(cogs_amount) sum_cogs_amt
45 FROM
46 CST_MARGIN_SUMMARY cms,
47 CST_MARGIN_BUILD cmb
48 WHERE
49 cms.build_id = cmb.build_id AND
50 cms.gl_date >= cmb.to_date - 30 AND
51 cms.build_id = (select max(build_id) from cst_margin_summary)
52 GROUP BY
53 cms.build_id,
54 cms.customer_id,
55 cms.primary_salesrep_id,
56 cms.territory_id,
57 cms.inventory_item_id,
58 cms.org_id,
59 cms.sold_to_customer_name
60 ;
61
62 CURSOR qrtrly_amt_cursor IS
63 SELECT
64 cms.build_id,
65 cms.customer_id,
66 cms.primary_salesrep_id,
67 cms.territory_id,
68 cms.inventory_item_id,
69 cms.org_id,
70 cms.sold_to_customer_name,
71 sum(invoiced_amount) sum_inv_amt,
72 sum(cogs_amount) sum_cogs_amt
73 FROM
74 CST_MARGIN_SUMMARY cms,
75 CST_MARGIN_BUILD cmb
76 WHERE
77 cms.build_id = cmb.build_id AND
78 cms.gl_date >= cmb.to_date - 90 AND
79 cms.build_id = (select max(build_id) from cst_margin_summary)
80 GROUP BY
81 cms.build_id,
82 cms.customer_id,
83 cms.primary_salesrep_id,
84 cms.territory_id,
85 cms.inventory_item_id,
86 cms.org_id,
87 cms.sold_to_customer_name
88 ;
89 --
90 BEGIN
91
92 --
93 /* delete old data from tables */
94 DELETE FROM ONT_PRT_MARGIN_ANALYSIS;
95 DELETE FROM ONT_PRT_MARGIN_ANA_ERR;
96
97 records_processed := 0;
98 FOR wkly_amt_rec IN wkly_amt_cursor LOOP
99 --
100 declare
101 tmp_build_id CST_MARGIN_SUMMARY.build_id%TYPE;
102 tmp_customer_id CST_MARGIN_SUMMARY.customer_id%TYPE;
103 tmp_pri_salesrep_id CST_MARGIN_SUMMARY.primary_salesrep_id%TYPE;
104 tmp_territory_id CST_MARGIN_SUMMARY.territory_id%TYPE;
105 tmp_inv_item_id CST_MARGIN_SUMMARY.inventory_item_id%TYPE;
106 --
107 begin
108 tmp_build_id := wkly_amt_rec.build_id;
109 tmp_customer_id := wkly_amt_rec.customer_id;
110 tmp_pri_salesrep_id := wkly_amt_rec.primary_salesrep_id;
111 tmp_territory_id := wkly_amt_rec.territory_id;
112 tmp_inv_item_id := wkly_amt_rec.inventory_item_id;
113
114 if wkly_amt_rec.sum_inv_amt is null then
115 wkly_amt_rec.sum_inv_amt := 0;
116 end if;
117
118 if wkly_amt_rec.sum_cogs_amt is null then
119 wkly_amt_rec.sum_cogs_amt := 0;
120 end if;
121
122 sum_margin := wkly_amt_rec.sum_inv_amt - wkly_amt_rec.sum_cogs_amt;
123
124 INSERT INTO ONT_PRT_MARGIN_ANALYSIS
125 (
126 build_id,
127 customer_id,
128 primary_salesrep_id,
129 territory_id,
130 inventory_item_id,
131 org_id,
132 sold_to_customer_name,
133 invoiced_amount,
134 cogs_amount,
135 margin,
136 period
137 )
138 VALUES
139 (
140 wkly_amt_rec.build_id,
141 wkly_amt_rec.customer_id,
142 wkly_amt_rec.primary_salesrep_id,
143 wkly_amt_rec.territory_id,
144 wkly_amt_rec.inventory_item_id,
145 wkly_amt_rec.org_id,
146 wkly_amt_rec.sold_to_customer_name,
147 wkly_amt_rec.sum_inv_amt,
148 wkly_amt_rec.sum_cogs_amt,
149 sum_margin,
150 '7D'
151 );
152
153 records_processed := records_processed + 1;
154 if (records_processed = 100) then
155 COMMIT;
156 records_processed := 0;
157 end if;
158
159 EXCEPTION
160 WHEN DUP_VAL_ON_INDEX THEN
161 INSERT into ONT_PRT_MARGIN_ANA_ERR
162 (
163 build_id,
164 customer_id,
165 primary_salesrep_id,
166 territory_id,
167 inventory_item_id,
168 period
169 )
170 VALUES
171 (
172 tmp_build_id,
173 tmp_customer_id,
174 tmp_pri_salesrep_id,
175 tmp_territory_id,
176 tmp_inv_item_id,
177 '7D'
178 );
179 WHEN OTHERS THEN
180 INSERT into ONT_PRT_MARGIN_ANA_ERR
181 (
182 build_id,
183 customer_id,
184 primary_salesrep_id,
185 territory_id,
186 inventory_item_id,
187 period
188 )
189 VALUES
190 (
191 tmp_build_id,
192 tmp_customer_id,
193 tmp_pri_salesrep_id,
194 tmp_territory_id,
195 tmp_inv_item_id,
196 '7D'
197 );
198 end;
199
200 end loop;
201
202
203 records_processed := 0;
204 FOR mnthly_amt_rec IN mnthly_amt_cursor LOOP
205 --
206 declare
207 tmp_build_id CST_MARGIN_SUMMARY.build_id%TYPE;
208 tmp_customer_id CST_MARGIN_SUMMARY.customer_id%TYPE;
209 tmp_pri_salesrep_id CST_MARGIN_SUMMARY.primary_salesrep_id%TYPE;
210 tmp_territory_id CST_MARGIN_SUMMARY.territory_id%TYPE;
211 tmp_inv_item_id CST_MARGIN_SUMMARY.inventory_item_id%TYPE;
212 --
213 begin
214 tmp_build_id := mnthly_amt_rec.build_id;
215 tmp_customer_id := mnthly_amt_rec.customer_id;
216 tmp_pri_salesrep_id := mnthly_amt_rec.primary_salesrep_id;
217 tmp_territory_id := mnthly_amt_rec.territory_id;
218 tmp_inv_item_id := mnthly_amt_rec.inventory_item_id;
219
220 if mnthly_amt_rec.sum_inv_amt is null then
221 mnthly_amt_rec.sum_inv_amt := 0;
222 end if;
223
224 if mnthly_amt_rec.sum_cogs_amt is null then
225 mnthly_amt_rec.sum_cogs_amt := 0;
226 end if;
227
228 sum_margin := mnthly_amt_rec.sum_inv_amt - mnthly_amt_rec.sum_cogs_amt;
229
230 INSERT INTO ONT_PRT_MARGIN_ANALYSIS
231 (
232 build_id,
233 customer_id,
234 primary_salesrep_id,
235 territory_id,
236 inventory_item_id,
237 org_id,
238 sold_to_customer_name,
239 invoiced_amount,
240 cogs_amount,
241 margin,
242 period
243 )
244 VALUES
245 (
246 mnthly_amt_rec.build_id,
247 mnthly_amt_rec.customer_id,
248 mnthly_amt_rec.primary_salesrep_id,
249 mnthly_amt_rec.territory_id,
250 mnthly_amt_rec.inventory_item_id,
251 mnthly_amt_rec.org_id,
252 mnthly_amt_rec.sold_to_customer_name,
253 mnthly_amt_rec.sum_inv_amt,
254 mnthly_amt_rec.sum_cogs_amt,
255 sum_margin,
256 '30D'
257 );
258
259 records_processed := records_processed + 1;
260 if (records_processed = 100) then
261 COMMIT;
262 records_processed := 0;
263 end if;
264
265 EXCEPTION
266 WHEN DUP_VAL_ON_INDEX THEN
267 INSERT into ONT_PRT_MARGIN_ANA_ERR
268 (
269 build_id,
270 customer_id,
271 primary_salesrep_id,
272 territory_id,
273 inventory_item_id,
274 period
275 )
276 VALUES
277 (
278 tmp_build_id,
279 tmp_customer_id,
280 tmp_pri_salesrep_id,
281 tmp_territory_id,
282 tmp_inv_item_id,
283 '30D'
284 );
285 WHEN OTHERS THEN
286 INSERT into ONT_PRT_MARGIN_ANA_ERR
287 (
288 build_id,
289 customer_id,
290 primary_salesrep_id,
291 territory_id,
292 inventory_item_id,
293 period
294 )
295 VALUES
296 (
297 tmp_build_id,
298 tmp_customer_id,
299 tmp_pri_salesrep_id,
300 tmp_territory_id,
301 tmp_inv_item_id,
302 '30D'
303 );
304 end;
305
306 end loop;
307
308
309 records_processed := 0;
310 FOR qrtrly_amt_rec IN qrtrly_amt_cursor LOOP
311 --
312 declare
313 tmp_build_id CST_MARGIN_SUMMARY.build_id%TYPE;
314 tmp_customer_id CST_MARGIN_SUMMARY.customer_id%TYPE;
315 tmp_pri_salesrep_id CST_MARGIN_SUMMARY.primary_salesrep_id%TYPE;
316 tmp_territory_id CST_MARGIN_SUMMARY.territory_id%TYPE;
317 tmp_inv_item_id CST_MARGIN_SUMMARY.inventory_item_id%TYPE;
318 --
319 begin
320 tmp_build_id := qrtrly_amt_rec.build_id;
321 tmp_customer_id := qrtrly_amt_rec.customer_id;
322 tmp_pri_salesrep_id := qrtrly_amt_rec.primary_salesrep_id;
323 tmp_territory_id := qrtrly_amt_rec.territory_id;
324 tmp_inv_item_id := qrtrly_amt_rec.inventory_item_id;
325
326 if qrtrly_amt_rec.sum_inv_amt is null then
327 qrtrly_amt_rec.sum_inv_amt := 0;
328 end if;
329
330 if qrtrly_amt_rec.sum_cogs_amt is null then
331 qrtrly_amt_rec.sum_cogs_amt := 0;
332 end if;
333
334 sum_margin := qrtrly_amt_rec.sum_inv_amt - qrtrly_amt_rec.sum_cogs_amt;
335
336 INSERT INTO ONT_PRT_MARGIN_ANALYSIS
337 (
338 build_id,
339 customer_id,
340 primary_salesrep_id,
341 territory_id,
342 inventory_item_id,
343 org_id,
344 sold_to_customer_name,
345 invoiced_amount,
346 cogs_amount,
347 margin,
348 period
349 )
350 VALUES
351 (
352 qrtrly_amt_rec.build_id,
353 qrtrly_amt_rec.customer_id,
354 qrtrly_amt_rec.primary_salesrep_id,
355 qrtrly_amt_rec.territory_id,
356 qrtrly_amt_rec.inventory_item_id,
357 qrtrly_amt_rec.org_id,
358 qrtrly_amt_rec.sold_to_customer_name,
359 qrtrly_amt_rec.sum_inv_amt,
360 qrtrly_amt_rec.sum_cogs_amt,
361 sum_margin,
362 '90D'
363 );
364
365 records_processed := records_processed + 1;
366 if (records_processed = 100) then
367 COMMIT;
368 records_processed := 0;
369 end if;
370
371 EXCEPTION
372 WHEN DUP_VAL_ON_INDEX THEN
373 INSERT into ONT_PRT_MARGIN_ANA_ERR
374 (
375 build_id,
376 customer_id,
377 primary_salesrep_id,
378 territory_id,
379 inventory_item_id,
380 period
381 )
382 VALUES
383 (
384 tmp_build_id,
385 tmp_customer_id,
386 tmp_pri_salesrep_id,
387 tmp_territory_id,
388 tmp_inv_item_id,
389 '90D'
390 );
391 WHEN OTHERS THEN
392 INSERT into ONT_PRT_MARGIN_ANA_ERR
393 (
394 build_id,
395 customer_id,
396 primary_salesrep_id,
397 territory_id,
398 inventory_item_id,
399 period
400 )
401 VALUES
402 (
403 tmp_build_id,
404 tmp_customer_id,
405 tmp_pri_salesrep_id,
406 tmp_territory_id,
407 tmp_inv_item_id,
408 '90D'
409 );
410 end;
411 end loop;
412
413 COMMIT;
414
415 end BUILD_ONT_PRT_MARGIN_TABLE;
416
417
418 -- GET_TOTAL_SALES ==> returns the sum of the invoiced_amounts from the
419 -- CST_MARGIN_SUMMARY table based on the period passed in
420 --
421 function get_total_sales (in_period in varchar2) return number as
422 tot_inv_amount number;
423 begin
424 select sum(invoiced_amount)
425 into tot_inv_amount
426 from ONT_PRT_MARGIN_ANALYSIS
427 where period=in_period;
428 return (tot_inv_amount);
429 end get_total_sales;
430 --
431
432 -- GET_TOTAL_MARGIN ==>returns the sum of the margins from the
433 -- CST_MARGIN_SUMMARY table based on the period passed in
434 --
435 function get_total_margin (in_period in varchar2) return number as
436 tot_margin_amount number;
437 begin
438 select sum(margin)
439 into tot_margin_amount
440 from ONT_PRT_MARGIN_ANALYSIS
441 where period=in_period;
442 return tot_margin_amount;
443 end get_total_margin;
444 --
445 function GET_ITEM_NUMBER (in_ITEM_ID in number)
446 return VARCHAR2 as
447 temp_item_num MTL_SYSTEM_ITEMS_KFV.CONCATENATED_SEGMENTS%TYPE;
448 begin
449 select distinct concatenated_segments
450 into temp_item_num
451 from mtl_system_items_kfv
452 where inventory_item_id = in_item_id;
453 return (temp_item_num);
454 exception
455 when too_many_rows then
456 return (' ');
457 when others then
458 return (' ');
459 end GET_ITEM_NUMBER;
460 --
461 END ONT_PRT_MARGIN_ANA_PKG;