[Home] [Help]
PACKAGE BODY: APPS.ICX_MARGIN_WEB_ANA_PKG
Source
1 PACKAGE BODY ICX_MARGIN_WEB_ANA_PKG as
2 /* $Header: ICXCSMRB.pls 115.0 99/08/09 17:23:22 porting ship $ */
3 --
4 --
5 procedure BUILD_ICX_CST_MARGIN_TABLE AS
6 records_processed number;
7 sum_margin cst_margin_temp.margin%TYPE;
8 CURSOR wkly_amt_cursor IS
9 SELECT
10 cmt.build_id,
11 cmt.customer_id,
12 cmt.primary_salesrep_id,
13 cmt.territory_id,
14 cmt.inventory_item_id,
15 cmt.org_id,
16 cmt.sold_to_customer_name,
17 sum(invoiced_amount) sum_inv_amt,
18 sum(cogs_amount) sum_cogs_amt
19 FROM
20 CST_MARGIN_TEMP cmt,
21 CST_MARGIN_BUILD cmb
22 WHERE
23 cmt.build_id = cmb.build_id AND
24 cmt.gl_date >= cmb.to_date - 7 AND
25 cmt.build_id = (select max(build_id) from cst_margin_temp)
26 GROUP BY
27 cmt.build_id,
28 cmt.customer_id,
29 cmt.primary_salesrep_id,
30 cmt.territory_id,
31 cmt.inventory_item_id,
32 cmt.org_id,
33 cmt.sold_to_customer_name
34 ;
35 CURSOR mnthly_amt_cursor IS
36 SELECT
37 cmt.build_id,
38 cmt.customer_id,
39 cmt.primary_salesrep_id,
40 cmt.territory_id,
41 cmt.inventory_item_id,
42 cmt.org_id,
43 cmt.sold_to_customer_name,
44 sum(invoiced_amount) sum_inv_amt,
45 sum(cogs_amount) sum_cogs_amt
46 FROM
47 CST_MARGIN_TEMP cmt,
48 CST_MARGIN_BUILD cmb
49 WHERE
50 cmt.build_id = cmb.build_id AND
51 cmt.gl_date >= cmb.to_date - 30 AND
52 cmt.build_id = (select max(build_id) from cst_margin_temp)
53 GROUP BY
54 cmt.build_id,
55 cmt.customer_id,
56 cmt.primary_salesrep_id,
57 cmt.territory_id,
58 cmt.inventory_item_id,
59 cmt.org_id,
60 cmt.sold_to_customer_name
61 ;
62 CURSOR qrtrly_amt_cursor IS
63 SELECT
64 cmt.build_id,
65 cmt.customer_id,
66 cmt.primary_salesrep_id,
67 cmt.territory_id,
68 cmt.inventory_item_id,
69 cmt.org_id,
70 cmt.sold_to_customer_name,
71 sum(invoiced_amount) sum_inv_amt,
72 sum(cogs_amount) sum_cogs_amt
73 FROM
74 CST_MARGIN_TEMP cmt,
75 CST_MARGIN_BUILD cmb
76 WHERE
77 cmt.build_id = cmb.build_id AND
78 cmt.gl_date >= cmb.to_date - 90 AND
79 cmt.build_id = (select max(build_id) from cst_margin_temp)
80 GROUP BY
81 cmt.build_id,
82 cmt.customer_id,
83 cmt.primary_salesrep_id,
84 cmt.territory_id,
85 cmt.inventory_item_id,
86 cmt.org_id,
87 cmt.sold_to_customer_name
88 ;
89 --
90 BEGIN
91
92 --
93 /* delete date from tables */
94 DELETE FROM ICX_MARGIN_ANALYSIS;
95 DELETE FROM ICX_MARGIN_ANALYSIS_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_TEMP.build_id%TYPE;
102 tmp_customer_id CST_MARGIN_TEMP.customer_id%TYPE;
103 tmp_pri_salesrep_id CST_MARGIN_TEMP.primary_salesrep_id%TYPE;
104 tmp_territory_id CST_MARGIN_TEMP.territory_id%TYPE;
105 tmp_inv_item_id CST_MARGIN_TEMP.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 icx_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 icx_margin_analysis_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 icx_margin_analysis_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_TEMP.build_id%TYPE;
208 tmp_customer_id CST_MARGIN_TEMP.customer_id%TYPE;
209 tmp_pri_salesrep_id CST_MARGIN_TEMP.primary_salesrep_id%TYPE;
210 tmp_territory_id CST_MARGIN_TEMP.territory_id%TYPE;
211 tmp_inv_item_id CST_MARGIN_TEMP.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 icx_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;
267 INSERT into icx_margin_analysis_err
264
265 EXCEPTION
266 WHEN DUP_VAL_ON_INDEX THEN
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 icx_margin_analysis_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_TEMP.build_id%TYPE;
314 tmp_customer_id CST_MARGIN_TEMP.customer_id%TYPE;
315 tmp_pri_salesrep_id CST_MARGIN_TEMP.primary_salesrep_id%TYPE;
316 tmp_territory_id CST_MARGIN_TEMP.territory_id%TYPE;
317 tmp_inv_item_id CST_MARGIN_TEMP.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 icx_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 icx_margin_analysis_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 icx_margin_analysis_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_ICX_CST_MARGIN_TABLE;
416
417
418 -- ICX_GET_TOTAL_SALES ==> returns the sum of the invoiced_amounts from the
419 -- CST_MARGIN_WEB_ANALYSIS table based on the period passed in
420 --
421 function icx_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 icx_margin_analysis
427 where period=in_period;
428 return (tot_inv_amount);
429 end icx_get_total_sales;
430 --
431
432 -- ICX_GET_TOTAL_MARGIN ==>returns the sum of the margins from the
433 -- CST_MARGIN_WEB_ANALYSIS table based on the period passed in
434 --
435 function icx_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 icx_margin_analysis
441 where period=in_period;
442 return tot_margin_amount;
443 end icx_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 ICX_MARGIN_WEB_ANA_PKG;