PACKAGE BODY: APPS.EDW_OPI_ACTV_MOPM_SZ

Source

1 PACKAGE BODY EDW_OPI_ACTV_MOPM_SZ AS
2 /* $Header: OPIPACZB.pls 120.1 2005/06/16 03:53:03 appldev  $*/
3 
4 -- procedure to count Lot Dimension rows.
5 
6   PROCEDURE  cnt_rows    (p_from_date IN  DATE,
7                           p_to_date   IN  DATE,
8                           p_num_rows  OUT NOCOPY NUMBER) IS
9   BEGIN
10     SELECT count(*) into p_num_rows
11     FROM FM_ACTV_MST FA
12     WHERE FA.last_update_date between p_from_date and p_to_date;
13   --  dbms_output.put_line ('Number of rows : '||p_num_rows);
14   EXCEPTION
15     WHEN OTHERS THEN
16       p_num_rows := 0;
17   END cnt_rows;
18 
19 -- procedure to get average row length Lot Dimension rows.
20 
21   PROCEDURE  est_row_len (p_from_date    IN  DATE,
22                           p_to_date      IN  DATE,
23                           p_avg_row_len  OUT NOCOPY NUMBER) IS
24     x_date                 number := 7;
25     x_total                number := 0;
26     x_constant             number := 6;
27     x_INSTANCE             NUMBER := 0;
28     x_ACT_DP               NUMBER := 0;
29     x_ACT_PK               NUMBER := 0;
30     x_COST_ANALYSIS_CODE   NUMBER := 0;
31     x_ACTIVITY             NUMBER := 0;
32     x_ACTIVITY_DESC        NUMBER := 0;
33     x_last_update_date     NUMBER := x_date;
34     x_creation_date        NUMBER := x_date;
35     cursor CUR_INSTANCE_SIZE is
36       select avg(nvl(vsize(instance_code),0))
37       from edw_local_instance;
38 
39     CURSOR CUR_OPM_ACTIVITY_SIZES IS
40       SELECT avg(nvl(vsize(ACTIVITY),0)),
41              avg(nvl(vsize(ACTIVITY_DESC),0)),
42              avg(nvl(vsize(COST_ANALYSIS_CODE),0)),
43              avg(nvl(vsize(ACTIVITY||'-'||'OPM'||'-'),0)) ACT_PK,
44              avg(vsize('ACTV'))
45       FROM FM_ACTV_MST
46       WHERE LAST_UPDATE_DATE BETWEEN P_FROM_DATE AND P_TO_DATE;
47   BEGIN
48      OPEN CUR_INSTANCE_SIZE;
49      FETCH CUR_INSTANCE_SIZE INTO x_INSTANCE;
50      CLOSE CUR_INSTANCE_SIZE;
51      OPEN CUR_OPM_ACTIVITY_SIZES;
52      FETCH CUR_OPM_ACTIVITY_SIZES INTO x_ACTIVITY,x_ACTIVITY_DESC,x_COST_ANALYSIS_CODE,x_ACT_PK,x_ACT_DP;
53      CLOSE CUR_OPM_ACTIVITY_SIZES;
54      x_total := NVL(ceil(x_INSTANCE + 1), 0) +
55                 3 * NVL(ceil(x_ACTIVITY + 1), 0) +
56                 NVL(ceil(x_ACTIVITY_DESC + 1), 0) +
57                 NVL(ceil(x_COST_ANALYSIS_CODE+ 1), 0) +
58                 NVL(ceil(x_ACT_PK + 1), 0) +
59                 NVL(ceil(x_creation_date + 1), 0) +
60                 NVL(ceil(x_creation_date + 1), 0) +
61                 NVL(ceil(x_ACT_DP + 1), 0);
62     p_avg_row_len := x_total;
63 --    dbms_output.put_line ('Average Row Length IS : '||p_avg_row_len);
64   EXCEPTION
65     WHEN OTHERS THEN
66       p_avg_row_len := 0;
67   END est_row_len;
68 END EDW_OPI_ACTV_MOPM_SZ;