[Home] [Help]
PACKAGE BODY: APPS.OPI_EDW_RES_UTIL_F_SZ
Source
1 PACKAGE BODY opi_edw_res_util_f_sz AS
2 /* $Header: OPIORUZB.pls 120.1 2005/06/16 03:52:08 appldev $*/
3
4 PROCEDURE cnt_rows(p_from_date DATE,
5 p_to_date DATE,
6 p_num_rows OUT NOCOPY NUMBER) IS
7
8 CURSOR c_cnt_rows IS
9 select sum(cnt)
10 from (
11 -- Third UNION: no wip actual, only availability, 24 hr available
12 SELECT count(*) cnt
13 FROM
14 bom_resources br,
15 bom_departments bd,
16 bom_department_resources bdr,
17 bom_calendar_dates bcd,
18 mtl_parameters mp,
19 mtl_units_of_measure m1,
20 mtl_units_of_measure m2
21 WHERE bdr.available_24_hours_flag = 1 -- 24 hr available
22 AND bdr.share_from_dept_id IS NULL
23 AND br.resource_id = bdr.resource_id
24 AND m1.uom_code = fnd_profile.value('BOM:HOUR_UOM_CODE')
25 AND m2.uom_code = br.unit_of_measure
26 AND m2.uom_class = m1.uom_class
27 AND bd.department_id = bdr.department_id
28 AND bd.organization_id = mp.organization_id
29 AND bcd.calendar_code = mp.calendar_code
30 AND bcd.exception_set_id = mp.calendar_exception_set_id
31 AND bcd.seq_num IS NOT NULL
32 AND ( bd.disable_date IS NULL OR bcd.calendar_date < bd.disable_date)
33 AND ( br.disable_date IS NULL OR bcd.calendar_date < br.disable_date)
34 AND bcd.calendar_date BETWEEN p_from_date AND p_to_date
35 UNION ALL
36 -- Fourth UNION: no wip actual, only availability, shift based
37 SELECT count(*) cnt
38 FROM
39 bom_resources br,
40 bom_departments bd,
41 bom_department_resources bdr,
42 bom_resource_shifts brs,
43 bom_shift_dates bsd,
44 bom_shift_times bst,
45 mtl_parameters mp,
46 mtl_units_of_measure m1,
47 mtl_units_of_measure m2
48 WHERE bdr.available_24_hours_flag = 2 -- shift based
49 AND bdr.share_from_dept_id IS NULL
50 AND br.resource_id = bdr.resource_id
51 AND m1.uom_code = fnd_profile.value('BOM:HOUR_UOM_CODE')
52 AND m2.uom_code = br.unit_of_measure
53 AND m2.uom_class = m1.uom_class
54 AND bd.department_id = bdr.department_id
55 AND bd.organization_id = mp.organization_id
56 AND brs.department_id = bd.department_id
57 AND brs.resource_id = br.resource_id
58 AND bsd.calendar_code = mp.calendar_code
59 AND bsd.exception_set_id = mp.calendar_exception_set_id
60 AND bsd.shift_num = brs.shift_num
61 AND bsd.seq_num IS NOT NULL
62 AND ( bd.disable_date IS NULL OR bsd.shift_date < bd.disable_date)
63 AND ( br.disable_date IS NULL OR bsd.shift_date < br.disable_date)
64 AND bst.calendar_code = mp.calendar_code
65 AND bst.shift_num = brs.shift_num
66 AND bsd.shift_date BETWEEN p_from_date AND p_to_date
67 GROUP BY
68 mp.organization_id,
69 mp.organization_code,
70 bd.department_id, -- owning dept
71 bd.department_code,
72 br.resource_id,
73 br.unit_of_measure,
74 bsd.shift_date
75 ) ;
76 BEGIN
77
78 OPEN c_cnt_rows;
79 FETCH c_cnt_rows INTO p_num_rows;
80 CLOSE c_cnt_rows;
81
82 END; -- procedure cnt_rows.
83
84
85 PROCEDURE est_row_len(p_from_date DATE,
86 p_to_date DATE,
87 p_avg_row_len OUT NOCOPY NUMBER) IS
88 x_date number := 7;
89 x_total number := 0;
90 x_constant number := 6;
91
92 CURSOR c_res IS
93 SELECT avg(nvl(vsize(resource_id), 0)) res_id,
94 avg(nvl(vsize(unit_of_measure), 0)) uom,
95 avg(nvl(vsize(resource_code), 0)) res_code
96 FROM bom_resources;
97
98 CURSOR c_instance IS
99 SELECT
100 avg(nvl(vsize(instance_code), 0))
101 FROM EDW_LOCAL_INSTANCE ;
102
103 CURSOR c_dept IS
104 SELECT avg(nvl(Vsize(department_code), 0))
105 FROM bom_departments;
106
107 CURSOR c_org IS
108 SELECT avg(nvl(Vsize(organization_id), 0)) org_id,
109 avg(nvl(Vsize(organization_code), 0)) org_code
110 FROM mtl_parameters;
111
112 CURSOR c_act_usage IS
113 SELECT AVG(Nvl(Vsize(primary_quantity), 0))
114 FROM wip_transactions
115 WHERE last_update_date between p_from_date and p_to_date;
116
117 CURSOR c_avail IS
118 SELECT AVG(Nvl(Vsize(24*capacity_units), 0))
119 FROM bom_department_resources
120 WHERE last_update_date between p_from_date and p_to_date;
121
122 CURSOR c_trx_date_fk IS
123 SELECT AVG(Nvl(Vsize(EDW_TIME_PKG.CAL_DAY_FK(Sysdate, set_of_books_id) ),0))
124 FROM gl_sets_of_books;
125
126 x_res_util_pk NUMBER;
127
128 x_locator_fk NUMBER;
129 x_rsrc_fk NUMBER;
130 x_trx_date_fk NUMBER;
131 x_uom_fk NUMBER;
132 x_instance_fk NUMBER;
133 x_act_res_usage NUMBER;
134 x_avail_res NUMBER;
135 x_department NUMBER;
136 x_trx_date NUMBER;
137
138 l_res c_res%ROWTYPE;
139 l_org c_org%ROWTYPE;
140
141
142 BEGIN
143 OPEN c_instance;
144 FETCH c_instance INTO x_instance_fk;
145 CLOSE c_instance;
146
147 OPEN c_res ;
148 FETCH c_res INTO l_res;
149 CLOSE c_res;
150
151 OPEN c_org;
152 FETCH c_org INTO l_org;
153 CLOSE c_org;
154
155 OPEN c_trx_date_fk;
156 FETCH c_trx_date_fk INTO x_trx_date_fk;
157 CLOSE c_trx_date_fk;
158
159 OPEN c_dept;
160 FETCH c_dept INTO x_department;
161 CLOSE c_dept;
162
163 OPEN c_avail;
164 FETCH c_avail INTO x_avail_res;
165 CLOSE c_avail;
166
167 OPEN c_act_usage;
168 FETCH c_act_usage INTO x_act_res_usage;
169 CLOSE c_act_usage;
170
171 x_total := 3 + x_total
172 --bcd.calendar_date ||'-'|| mp.organization_id ||'-'|| br.resource_id
173 -- ||'-'|| inst.instance_code ||'-'|| 'OPI' res_util_pk,
174 + Ceil( x_date + l_org.org_id + l_res.res_id + x_instance_fk + 4 +4 + 1)
175 --mp.organization_code ||'-'||inst.instance_code ||'-PLNT' locator_fk
176 + Ceil( l_org.org_code + x_instance_fk + 5 +1 + 1)
177 --res.resource_code||'-'||dept.department_code||'-'||
178 -- mp.organization_code||'-'||inst.instance_code RSRC_FK,
179 + Ceil(l_res.res_code +x_department +l_org.org_code +x_instance_fk +3+1)
180 -- EDW_TIME_PKG.CAL_DAY_FK(bcd.calendar_date,To_number(hoi.org_information1))
181 -- trx_date_fk,
182 + Ceil( x_trx_date_fk +1)
183 -- br.unit_of_measure uom_fk,
184 + Ceil(l_res.uom + 1 )
185 -- inst.instance_code instance_fk,
186 + Ceil(x_instance_fk +1)
187 ;
188
189 -- dbms_output.put_line('1 x_total is ' || x_total );
190
191 x_total := x_total
192 -- 5 user_fk with 'NA_EDW'
193 + 5* x_constant
194 -- act_res_usage,
195 + Ceil( Nvl(x_act_res_usage,3) + 1 )
196 -- 24* bdr.capacity_units avail_res,
197 + Ceil( Nvl(x_avail_res, 3) + 1)
198 -- bd.department_code department,
199 + Ceil(x_department + 1)
200 -- bcd.calendar_date trx_date,
201 + x_date
202 -- bcd.calendar_date last_update_date,
203 + x_date;
204
205 -- dbms_output.put_line('a2 x_total is ' || x_total );
206 p_avg_row_len := x_total;
207
208
209 END; -- procedure est_row_len.
210
211 END; -- package body OPI_EDW_RES_UTIL_F_SZ