[Home] [Help]
PACKAGE BODY: APPS.POA_EDW_CSTM_MSR_F_SIZE
Source
1 PACKAGE BODY POA_EDW_CSTM_MSR_F_SIZE AS
2 /*$Header: poaszcmb.pls 120.0 2005/06/01 15:08:22 appldev noship $ */
3
4 PROCEDURE cnt_rows (p_from_date IN DATE,
5 p_to_date IN DATE,
6 p_num_rows OUT NOCOPY NUMBER) IS
7
8 BEGIN
9
10 -- dbms_output.enable(100000);
11
12 select count(*) into p_num_rows
13 from
14 poa_cm_evaluation pme,
15 poa_cm_eval_scores pms
16 WHERE pme.evaluation_id = pms.evaluation_id
17 and greatest(pme.last_update_date, pms.last_update_date)
18 between p_from_date and p_to_date;
19
20 -- dbms_output.put_line('The number of rows for customer measure is: '
21 -- || to_char(p_num_rows));
22
23 EXCEPTION
24 WHEN OTHERS THEN p_num_rows := 0;
25 END;
26
27 -------------------------------------------------------
28 PROCEDURE est_row_len (p_from_date IN DATE,
29 p_to_date IN DATE,
30 p_avg_row_len OUT NOCOPY NUMBER) IS
31
32 x_date number := 7;
33 x_total number := 0;
34
35 x_SUPPLIER_SITE_FK NUMBER := 0 ;
36 x_EVAL_DATE_FK NUMBER := 0 ;
37 x_OPERATING_UNIT_FK NUMBER := 0 ;
38 x_CUSTOM_MEASURE_FK NUMBER := 0 ;
39 x_CRITERIA_CODE_FK NUMBER := 0 ;
40 x_ITEM_FK NUMBER := 0 ;
41 x_INSTANCE_FK NUMBER := 0 ;
42 x_WEIGHTED_SCORE NUMBER := 0 ;
43 x_WEIGHT NUMBER := 0 ;
44 x_SCORE NUMBER := 0 ;
45 x_MIN_SCORE NUMBER := 0 ;
46 x_MAX_SCORE NUMBER := 0 ;
47 x_EVALUATION_ID NUMBER := 0 ;
48 x_USER_NAME NUMBER := 0 ;
49 x_SCORE_COMMENTS NUMBER := 0 ;
50 x_EVAL_COMMENTS NUMBER := 0 ;
51 x_CSTM_MSR_PK NUMBER := 0 ;
52
53 x_item_id NUMBER := 0 ;
54 x_category_id NUMBER := 0 ;
55
56 -------------------------------------------------------------
57
58 CURSOR c_1 IS
59 SELECT avg(nvl(vsize(custom_measure_code), 0)),
60 avg(nvl(vsize(supplier_site_id), 0)),
61 avg(nvl(vsize(oper_unit_id), 0)),
62 avg(nvl(vsize(item_id), 0)),
63 avg(nvl(vsize(category_id), 0)),
64 avg(nvl(vsize(comments), 0)),
65 avg(nvl(vsize(evaluation_id), 0))
66 from poa_cm_evaluation
67 where last_update_date between
68 p_from_date and p_to_date;
69
70 CURSOR c_2 IS
71 SELECT avg(nvl(vsize(evaluation_score_id), 0)),
72 avg(nvl(vsize(criteria_code), 0)),
73 avg(nvl(vsize(score), 0)),
74 avg(nvl(vsize(weight), 0)),
75 avg(nvl(vsize(min_score), 0)),
76 avg(nvl(vsize(max_score), 0)),
77 avg(nvl(vsize(comments), 0))
78 from poa_cm_eval_scores
79 where last_update_date between
80 p_from_date and p_to_date;
81
82 CURSOR c_3 IS
83 SELECT avg(nvl(vsize(user_name), 0))
84 from fnd_user
85 where last_update_date between
86 p_from_date and p_to_date;
87
88 BEGIN
89
90 -- dbms_output.enable(100000);
91
92 -- all date FKs
93
94 x_EVAL_DATE_FK := x_date;
95
96 x_total := 3 + x_total
97 + ceil (x_EVAL_DATE_FK + 1);
98
99 -----------------------------------------------------
100
101
102 OPEN c_1;
103 FETCH c_1 INTO x_custom_measure_fk, x_supplier_site_fk,
104 x_operating_unit_fk, x_item_id, x_category_id,
105 x_eval_comments, x_evaluation_id;
106 CLOSE c_1;
107
108 x_item_fk := x_item_id + x_category_id + 5;
109
110 x_total := x_total
111 + NVL (ceil(x_custom_measure_fk + 1), 0)
112 + NVL (ceil(x_supplier_site_fk + 1), 0)
113 + NVL (ceil(x_operating_unit_fk + 1), 0)
114 + NVL (ceil(x_item_fk + 1), 0)
115 + NVL (ceil(x_eval_comments + 1), 0)
116 + NVL (ceil(x_evaluation_id + 1), 0);
117
118 OPEN c_2;
119 FETCH c_2 INTO x_cstm_msr_pk, x_criteria_code_fk,
120 x_score, x_weight, x_min_score, x_max_score, x_score_comments;
121 CLOSE c_2;
122
123 x_weighted_score := x_weight + x_score;
124
125 x_total := x_total
126 + NVL (ceil(x_cstm_msr_pk + 1), 0)
127 + NVL (ceil(x_criteria_code_fk + 1), 0)
128 + NVL (ceil(x_score + 1), 0)
129 + NVL (ceil(x_weight + 1), 0)
130 + NVL (ceil(x_weighted_score + 1), 0)
131 + NVL (ceil(x_min_score + 1), 0)
132 + NVL (ceil(x_max_score + 1), 0)
133 + NVL (ceil(x_score_comments + 1), 0);
134
135 OPEN c_3;
136 FETCH c_3 INTO x_user_name;
137 CLOSE c_3;
138
139 x_total := x_total + NVL (ceil(x_user_name + 1), 0);
140
141 ------------------------------------------------------------------
142
143 -- dbms_output.put_line(' ');
144 -- dbms_output.put_line('The average row length for customer measures is: '
145 -- || to_char(x_total));
146
147 p_avg_row_len := x_total;
148
149 EXCEPTION
150 WHEN OTHERS THEN p_avg_row_len := 0;
151 END; -- procedure est_row_len.
152
153
154 END;