eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.EDW_ITEMS_M_SIZE

Source


1 PACKAGE BODY EDW_ITEMS_M_SIZE AS
2 /* $Header: ENIITMSB.pls 115.3 2004/01/30 21:51:47 sbag noship $*/
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         select count(*) cnt
12         from
13         mtl_system_items m
14         where  m.last_update_date between
15         p_from_date and p_to_date ) ;
16 
17 
18 BEGIN
19 
20   OPEN c_cnt_rows;
21        FETCH c_cnt_rows INTO p_num_rows;
22   CLOSE c_cnt_rows;
23 
24 EXCEPTION
25   WHEN OTHERS THEN
26 	IF c_cnt_rows%ISOPEN THEN
27 		CLOSE c_cnt_rows;
28 	END IF;
29 
30 END CNT_ROWS;
31 
32 PROCEDURE est_row_len(p_from_date DATE,
33                       p_to_date DATE,
34                       p_avg_row_len OUT NOCOPY NUMBER) IS
35 
36  x_date                 number := 7;
37  x_total                number := 0;
38  x_constant             number := 6;
39 
40   -- EDW_ITEM_ITEMORG_LCV
41 
42   CURSOR c_1 IS
43         SELECT
44         avg(nvl(vsize(mti.inventory_item_id ||'-'||mti.organization_id ||'-'||inst.instance_code), 0)) +
45         avg(nvl(vsize(inst.instance_code), 0)) +
46         avg(nvl(vsize(mti.inventory_item_id ||'-'||edw_items_pkg.get_master_parent(mti.organization_id) ||'-'||inst.instance_code), 0)) +
47         avg(nvl(vsize('NA_EDW'), 0)) +
48         avg(nvl(vsize('NA_EDW'), 0)) +
49         avg(nvl(vsize(substr(mti.concatenated_segments ||'('||mtp.organization_code||')',1,240)), 0)) +
50         avg(nvl(vsize(substr(mti.concatenated_segments ||'('||mtp.organization_code||')',1,80)), 0)) +
51         avg(nvl(vsize(decode(mti.buyer_id, NULL, 'NA_EDW', to_char(mti.buyer_id)||'?')), 0)) +
52         avg(nvl(vsize(decode(mti.planner_code, NULL, 'NA_EDW', mti.planner_code||'?')), 0)) +
53         avg(nvl(vsize(substr(mti.concatenated_segments ||'('||mtp.organization_code||')',1,240)), 0)) +
54         avg(nvl(vsize(mti.description), 0)) +
55         avg(nvl(vsize(mti.inventory_item_id), 0)) +
56         avg(nvl(vsize(mti.organization_id), 0)) +
57         avg(nvl(vsize(mti.planning_make_buy_code), 0)) +
58         avg(nvl(vsize(mti.list_price_per_unit), 0)) +
59         avg(nvl(vsize(mti.market_price), 0)) +
60         avg(nvl(vsize(mti.taxable_flag), 0)) +
61         avg(nvl(vsize(mti.stock_enabled_flag), 0)) +
62         avg(nvl(vsize(mti.internal_order_enabled_flag), 0)) +
63         avg(nvl(vsize(mti.inventory_planning_code), 0)) +
64         avg(nvl(vsize(mti.lot_control_code), 0)) +
65         avg(nvl(vsize(mti.outside_operation_flag), 0)) +
66         avg(nvl(vsize(mti.price_tolerance_percent), 0)) +
67         avg(nvl(vsize(mti.purchasing_enabled_flag), 0)) +
68         avg(nvl(vsize(mti.shelf_life_code), 0)) +
69         avg(nvl(vsize(mti.shelf_life_days), 0)) +
70         avg(nvl(vsize(mti.tax_code), 0)) +
71         avg(nvl(vsize(mti.revision_qty_control_code), 0)) +
72         avg(nvl(vsize(mti.inspection_required_flag), 0)) +
73         avg(nvl(vsize(mti.receipt_required_flag), 0)) +
74         avg(nvl(vsize(mti.location_control_code), 0)) +
75         avg(nvl(vsize(mti.effectivity_control), 0)) +
76         avg(nvl(vsize(mti.serial_number_control_code), 0)) +
77         avg(nvl(vsize(mti.mrp_planning_code), 0)) +
78         avg(nvl(vsize(mti.must_use_approved_vendor_flag), 0)) +
79         avg(nvl(vsize(mti.allow_substitute_receipts_flag), 0)) +
80         avg(nvl(vsize(mti.allow_unordered_receipts_flag), 0)) +
81         avg(nvl(vsize(mti.allow_express_delivery_flag), 0)) +
82         avg(nvl(vsize(mti.hazard_class_id), 0)) +
83         avg(nvl(vsize(mti.un_number_id), 0)) +
84         avg(nvl(vsize(mti.rfq_required_flag), 0)) +
85         avg(nvl(vsize(mti.creation_date), 0)) +
86         avg(nvl(vsize(mti.last_update_date), 0)) +
87         avg(nvl(vsize(NULL), 0)) +
88         avg(nvl(vsize(mti.segment1), 0)) +
89         45
90         from mtl_system_items_b_kfv mti,
91         mtl_parameters mtp,
92         mtl_parameters mtm,
93         edw_local_instance inst
94         WHERE
95         mti.product_family_item_id is NULL
96         and mti.organization_id=mtp.organization_id
97         and mtp.master_organization_id=mtm.organization_id
98         and mti.last_update_date between
99         p_from_date and p_to_date;
100 
101   -- EDW_ITEM_ITEMREV_LCV
102 
103   CURSOR c_2 IS
104         SELECT
105         avg(nvl(vsize(mtr.revision||'-'|| mtr.inventory_item_id ||'-'|| mtr.organization_id ||'-'|| inst.instance_code), 0))  +
106         avg(nvl(vsize(inst.instance_code), 0)) +
107 	avg(nvl(vsize(mtr.inventory_item_id||'-'||mtr.organization_id||'-'||inst.instance_code), 0)) +
108         avg(nvl(vsize(substr(mtr.revision || '(' || mti.concatenated_segments || ',' ||mp.organization_code || ')',1,240)), 0)) +
109         avg(nvl(vsize(substr(mtr.revision || '(' || mti.concatenated_segments || ',' || mp.organization_code || ')',1,80)), 0)) +
110         avg(nvl(vsize(substr(mtr.revision || '(' || mti.concatenated_segments || ',' || mp.organization_code || ')',1,240)), 0)) +
111         avg(nvl(vsize(mtr.effectivity_date), 0)) +
112         avg(nvl(vsize(mtr.creation_date), 0)) +
113         avg(nvl(vsize(mtr.last_update_date), 0)) +
114         avg(nvl(vsize(NULL), 0)) +
115         10
116         FROM
117         mtl_system_items_kfv mti,
118         mtl_item_revisions mtr,
119         mtl_parameters mp,
120         edw_local_instance inst
121         WHERE
122         mti.inventory_item_id = mtr.inventory_item_id
123         and mti.organization_id = mtr.organization_id
124         and mtr.organization_id = mp.organization_id
125         and mtr.last_update_date between
126         p_from_date and p_to_date;
127 
128 
129   -- EDW_ITEM_ITEM_LCV
130 
131   CURSOR c_3 IS
132         SELECT
133         avg(nvl(vsize(mti.inventory_item_id||'-'||mti.organization_id||'-'||inst.instance_code), 0)) +
134         avg(nvl(vsize(inst.instance_code), 0)) +
135         avg(nvl(vsize('NA_EDW'), 0)) +
136         avg(nvl(vsize('NA_EDW'), 0)) +
137         avg(nvl(vsize('NA_EDW'), 0)) +
138         avg(nvl(vsize('NA_EDW'), 0)) +
139         avg(nvl(vsize(substr(mti.CONCATENATED_SEGMENTS||'('||mtp.organization_code||')',1,240)), 0)) +
140         avg(nvl(vsize(substr(mti.CONCATENATED_SEGMENTS||'('||mtp.organization_code||')',1,80)), 0)) +
141         avg(nvl(vsize(substr(mti.CONCATENATED_SEGMENTS||'('||mtp.organization_code||')',1,240)), 0)) +
142         avg(nvl(vsize(mti.description), 0)) +
143         avg(nvl(vsize(mti.ORGANIZATION_ID), 0)) +
144         avg(nvl(vsize(mti.inventory_item_id), 0)) +
145         avg(nvl(vsize(mti.last_update_date), 0)) +
146         avg(nvl(vsize(mti.creation_date), 0)) +
147         avg(nvl(vsize(NULL), 0)) +
148         15
149         from mtl_system_items_kfv mti,
150         mtl_parameters mtp,
151         edw_local_instance inst
152         WHERE mtp.organization_id = mtp.master_organization_id
153         and mti.organization_id = mtp.organization_id
154         and mti.last_update_date between
155         p_from_date and p_to_date;
156 
157 
158   -- EDW_ITEM_ONETIME_ITEMORG_LCV
159 
160   CURSOR c_4 IS
161         SELECT DISTINCT
162         avg(nvl(vsize(l.ITEM_DESCRIPTION || '-' || cat.category_id || '-' || l.org_id || '-' || inst.instance_code || '-ONETIME'), 0)) +
163         avg(nvl(vsize(inst.instance_code), 0)) +
164         avg(nvl(vsize(l.ITEM_DESCRIPTION || '-' || cat.category_id || '-' || l.org_id || '-' || inst.instance_code || '-ONETIME'), 0)) +
165         avg(nvl(vsize('NA_EDW'), 0)) +
166         avg(nvl(vsize(cat.category_id||'-'||inst.instance_code), 0)) +
167         avg(nvl(vsize(substr(l.ITEM_DESCRIPTION || '('|| cat.CONCATENATED_SEGMENTS || ',' || l.org_id|| ')',1,240)), 0)) +
168         avg(nvl(vsize(substr(l.ITEM_DESCRIPTION || '(' || cat.CONCATENATED_SEGMENTS || ',' || l.org_id|| ')',1,80)), 0)) +
169         avg(nvl(vsize('NA_EDW'), 0)) +
170         avg(nvl(vsize('NA_EDW'), 0)) +
171         avg(nvl(vsize('Y'), 0)) +
172         avg(nvl(vsize(NULL), 0)) +
173         avg(nvl(vsize(l.item_description), 0)) +
174         avg(nvl(vsize(l.org_id), 0)) +
175         avg(nvl(vsize(NULL), 0)) +
176         avg(nvl(vsize(null), 0)) +
177         avg(nvl(vsize(null), 0)) +
178         avg(nvl(vsize(null), 0)) +
179         avg(nvl(vsize(null), 0)) +
180         avg(nvl(vsize(null), 0)) +
181         avg(nvl(vsize(null), 0)) +
182         avg(nvl(vsize(null), 0)) +
183         avg(nvl(vsize(null), 0)) +
184         avg(nvl(vsize(NULL), 0)) +
185         avg(nvl(vsize(null), 0)) +
186         avg(nvl(vsize(null), 0)) +
187         avg(nvl(vsize(null), 0)) +
188         avg(nvl(vsize(null), 0)) +
189         avg(nvl(vsize(null), 0)) +
190         avg(nvl(vsize(null), 0)) +
191         avg(nvl(vsize(null), 0)) +
192         avg(nvl(vsize(null), 0)) +
193         avg(nvl(vsize(null), 0)) +
194         avg(nvl(vsize(null), 0)) +
195         avg(nvl(vsize(null), 0)) +
196         avg(nvl(vsize(null), 0)) +
197         avg(nvl(vsize(null), 0)) +
198         avg(nvl(vsize(null), 0)) +
199         avg(nvl(vsize(null), 0)) +
200         avg(nvl(vsize(null), 0)) +
201         avg(nvl(vsize(min(l.creation_date)), 0)) +
202         avg(nvl(vsize(max(l.last_update_date)), 0)) +
203         avg(nvl(vsize(NULL), 0)) +
204         avg(nvl(vsize(NULL), 0)) +
205         avg(nvl(vsize(NULL), 0)) +
206         avg(nvl(vsize(NULL), 0)) +
207         avg(nvl(vsize(NULL), 0)) +
208         avg(nvl(vsize(NULL), 0)) +
209         47
210         From
211         mtl_categories_kfv cat,
212         po_lines_all l,
213         edw_local_instance inst
214         WHERE cat.category_id = l.category_id
215         and l.item_id is null
216         and l.last_update_date between
217         p_from_date and p_to_date
218         Group by
219         l.ITEM_DESCRIPTION,
220         l.org_id,
221         cat.category_id,
222         cat.CONCATENATED_SEGMENTS,
223         inst.instance_code ;
224 
225   -- EDW_ITEM_ONETIME_ITEM_LCV
226 
227   CURSOR c_5 IS
228         SELECT distinct
229         avg(nvl(vsize(l.ITEM_DESCRIPTION || '-' || cat.category_id || '-' || l.org_id|| '-' || inst.instance_code ||'-ONETIME'), 0)) +
230         avg(nvl(vsize(inst.instance_code), 0)) +
231         avg(nvl(vsize(cat.category_id || '-' || inst.instance_code), 0)) +
232         avg(nvl(vsize('NA_EDW'), 0)) +
233         avg(nvl(vsize(substr(l.ITEM_DESCRIPTION || '(' || cat.CONCATENATED_SEGMENTS|| ')',1,240)), 0)) +
234         avg(nvl(vsize(substr(l.ITEM_DESCRIPTION || '(' || cat.CONCATENATED_SEGMENTS|| ')',1,80)), 0)) +
235         avg(nvl(vsize('Y'), 0)) +
236         avg(nvl(vsize(l.item_description), 0)) +
237         avg(nvl(vsize(NVL(l.item_description,'NA_EDW')), 0)) +
238         avg(nvl(vsize(min(l.creation_date)), 0)) +
239         avg(nvl(vsize(max(l.last_update_date)), 0)) +
240         avg(nvl(vsize(NULL), 0)) +
241         avg(nvl(vsize(NULL), 0)) +
242         avg(nvl(vsize(NULL), 0)) +
243         avg(nvl(vsize(NULL), 0)) +
244         avg(nvl(vsize(NULL), 0)) +
245         avg(nvl(vsize(NULL), 0)) +
246         17
247         From edw_local_instance inst,
248         mtl_categories_kfv cat,
249         po_lines_all l
250         WHERE cat.category_id = l.category_id
251         and l.item_id is null
252         and l.last_update_date between
253         p_from_date and p_to_date
254         Group by l.ITEM_DESCRIPTION,
255         l.org_id,
256         cat.category_id,
257         cat.concatenated_segments,
258         inst.instance_code;
259 
260   -- EDW_ITEM_ITEMORGPF_LCV
261 
262   CURSOR c_6 IS
263         SELECT
264         avg(nvl(vsize(mti.inventory_item_id||'-'||mtp.organization_id||'-'||inst.instance_code), 0)) +
265         avg(nvl(vsize(NULL), 0)) +
266         avg(nvl(vsize(mti.inventory_item_id||'-'||edw_items_pkg.get_master_parent(mtp.organization_id) ||'-'||inst.instance_code), 0)) +
267         avg(nvl(vsize(mic.category_id ||'-'||inst.instance_code), 0)) +
268         avg(nvl(vsize('NA_EDW'), 0)) +
269         avg(nvl(vsize(substr(mti.concatenated_segments||'('||mtp.organization_code||')',1,240)), 0)) +
270         avg(nvl(vsize(substr(mti.concatenated_segments||'('||mtp.organization_code||')',1,80)), 0)) +
271         avg(nvl(vsize(decode(mti.buyer_id, NULL,'NA_EDW',to_char(mti.buyer_id)||'?')), 0)) +
272         avg(nvl(vsize(decode(mti.planner_code, NULL, 'NA_EDW', mti.planner_code||'?')), 0)) +
273         avg(nvl(vsize(substr(mti.concatenated_segments||'('||mtp.organization_code||')',1,240)), 0)) +
274         avg(nvl(vsize(mti.description), 0)) +
275         avg(nvl(vsize(mti.inventory_item_id), 0)) +
276         avg(nvl(vsize(mti.organization_id), 0)) +
277         avg(nvl(vsize(mti.planning_make_buy_code), 0)) +
278         avg(nvl(vsize(mti.list_price_per_unit), 0)) +
279         avg(nvl(vsize(mti.market_price), 0)) +
280         avg(nvl(vsize(mti.taxable_flag), 0)) +
281         avg(nvl(vsize(mti.stock_enabled_flag), 0)) +
282         avg(nvl(vsize(mti.internal_order_enabled_flag), 0)) +
283         avg(nvl(vsize(mti.inventory_planning_code), 0)) +
284         avg(nvl(vsize(mti.lot_control_code), 0)) +
285         avg(nvl(vsize(mti.outside_operation_flag), 0)) +
286         avg(nvl(vsize(mti.price_tolerance_percent), 0)) +
287         avg(nvl(vsize(mti.purchasing_enabled_flag), 0)) +
288         avg(nvl(vsize(mti.shelf_life_code), 0)) +
289         avg(nvl(vsize(mti.shelf_life_days), 0)) +
290         avg(nvl(vsize(mti.tax_code), 0)) +
291         avg(nvl(vsize(mti.revision_qty_control_code), 0)) +
292         avg(nvl(vsize(mti.inspection_required_flag), 0)) +
293         avg(nvl(vsize(mti.receipt_required_flag), 0)) +
294         avg(nvl(vsize(mti.location_control_code), 0)) +
295         avg(nvl(vsize(mti.effectivity_control), 0)) +
296         avg(nvl(vsize(mti.serial_number_control_code), 0)) +
297         avg(nvl(vsize(mti.mrp_planning_code), 0)) +
298         avg(nvl(vsize(mti.must_use_approved_vendor_flag), 0)) +
299         avg(nvl(vsize(mti.allow_substitute_receipts_flag), 0)) +
300         avg(nvl(vsize(mti.allow_unordered_receipts_flag), 0)) +
301         avg(nvl(vsize(mti.allow_express_delivery_flag), 0)) +
302         avg(nvl(vsize(mti.hazard_class_id), 0)) +
303         avg(nvl(vsize(mti.un_number_id), 0)) +
304         avg(nvl(vsize(mti.rfq_required_flag), 0)) +
305         avg(nvl(vsize(mti.creation_date), 0)) +
306         avg(nvl(vsize(mti.last_update_date), 0)) +
307         avg(nvl(vsize(NULL), 0)) +
308         avg(nvl(vsize(mti.segment1), 0)) +
309         45
310         FROM
311         mtl_system_items_kfv mti,
312         mtl_item_categories mic,
313         mtl_category_sets sets,
314         mtl_parameters mtp,
315         mtl_parameters mtm,
316         edw_local_instance inst
317         WHERE
318         mti.product_family_item_id is not null
319         and mti.organization_id = mtp.organization_id
320         and mic.inventory_item_id = mti.product_family_item_id
321         and mic.organization_id = mti.organization_id
322         and mic.category_set_id = sets.category_set_id
323         and sets.category_set_name = 'Product Family'
324         and mtp.master_organization_id = mtm.organization_id
325         and mti.last_update_date between
326         p_from_date and p_to_date;
327 
328   -- EDW_ITEM_ITEM_CAT_LCV
329 
330   CURSOR c_7 IS
331         SELECT
332         avg(nvl(vsize(mtc.category_id||'-'||inst.instance_code), 0)) +
333         avg(nvl(vsize(inst.instance_code), 0)) +
334         avg(nvl(vsize('ALL'), 0)) +
335         avg(nvl(vsize(substr(mtc.concatenated_segments,1,240)), 0)) +
336         avg(nvl(vsize(substr(mtc.concatenated_segments,1,80)), 0)) +
337         avg(nvl(vsize(mtc.description), 0)) +
338         avg(nvl(vsize(mts.category_set_name), 0)) +
339         avg(nvl(vsize(mtc.category_id), 0)) +
340         avg(nvl(vsize(mtc.creation_date), 0)) +
341         avg(nvl(vsize(greatest(mtc.last_update_date, mts.last_update_date)), 0)) +
342         avg(nvl(vsize(NULL), 0)) +
343         11
344         from
345         mtl_categories_kfv mtc,
346         mtl_category_sets mts,
347         edw_local_instance inst
348         WHERE mtc.structure_id = mts.structure_id
349         and mts.control_level = 1
350         and mtc.last_update_date between
351         p_from_date and p_to_date;
352 
353 
354   -- EDW_ITEM_ITEM_ORG_CAT_LCV
355 
356   CURSOR c_8 IS
360         avg(nvl(vsize('ALL'), 0)) +
357         SELECT
358         avg(nvl(vsize(mtc.category_id ||'-'||inst.instance_code), 0)) +
359         avg(nvl(vsize(inst.instance_code), 0)) +
361         avg(nvl(vsize(substr(mtc.concatenated_segments,1,240)), 0)) +
362         avg(nvl(vsize(substr(mtc.concatenated_segments,1,80)), 0)) +
363         avg(nvl(vsize(mtc.description), 0)) +
364         avg(nvl(vsize(mts.category_set_name), 0)) +
365         avg(nvl(vsize(mtc.category_id), 0)) +
366         avg(nvl(vsize(mtc.creation_date), 0)) +
367         avg(nvl(vsize(greatest(mtc.last_update_date, mts.last_update_date)), 0)) +
368         avg(nvl(vsize(NULL), 0)) +
369         11
370         from mtl_categories_kfv mtc,
371         mtl_category_sets mts,
372         edw_local_instance inst
373         WHERE mtc.structure_id = mts.structure_id
374         and mtc.last_update_date between
375         p_from_date and p_to_date;
376 
377 
378 
379   -- EDW_ITEM_PRDFAM_LCV
380 
381   CURSOR c_9 IS
382         SELECT
383         avg(nvl(vsize(cats.category_id ||'-'||inst.instance_code), 0)) +
384         avg(nvl(vsize(inst.instance_code), 0)) +
385         avg(nvl(vsize('ALL'), 0)) +
386         avg(nvl(vsize(substr(CATS.CONCATENATED_SEGMENTS,1,240)), 0)) +
387         avg(nvl(vsize(substr(CATS.CONCATENATED_SEGMENTS,1,80)), 0)) +
388         avg(nvl(vsize(substr(cats.concatenated_segments,1,240)), 0)) +
389         avg(nvl(vsize(cats.description), 0)) +
390         avg(nvl(vsize(cats.creation_date), 0)) +
391         avg(nvl(vsize(cats.last_update_date), 0)) +
392         avg(nvl(vsize(NULL), 0)) +
393         avg(nvl(vsize(NULL), 0)) +
394         avg(nvl(vsize(NULL), 0)) +
395         avg(nvl(vsize(NULL), 0)) +
396         avg(nvl(vsize(NULL), 0)) +
397         avg(nvl(vsize(NULL), 0)) +
398         15
399         FROM
400         mtl_categories_kfv cats,
401         mtl_category_sets sets,
402         edw_local_instance inst
403         WHERE
404         cats.structure_id = sets.structure_id
405         and sets.category_set_name = 'Product Family'
406         and cats.last_update_date between
407         p_from_date and p_to_date;
408 
409 
410   -- EDW_ITEM_PROD_CATG_LCV
411 
412   CURSOR c_10 IS
413         SELECT
414         avg(nvl(vsize(CD.INTEREST_CODE_ID || '-' || INST.INSTANCE_CODE || '-PRIM_CODE'), 0)) +
415         avg(nvl(vsize(INTYP.INTEREST_TYPE_ID || '-' || INST.INSTANCE_CODE || '-INTR_TYPE'), 0)) +
416         avg(nvl(vsize(CD.CODE ||'(' || INTYP.INTEREST_TYPE || ')'), 0)) +
417         avg(nvl(vsize(CD.CODE), 0)) +
418         avg(nvl(vsize(CD.DESCRIPTION), 0)) +
419         avg(nvl(vsize(CD.INTEREST_CODE_ID), 0)) +
420         avg(nvl(vsize(CD.MASTER_ENABLED_FLAG), 0)) +
421         avg(nvl(vsize(INST.INSTANCE_CODE), 0)) +
422         avg(nvl(vsize(CD.CREATION_DATE), 0)) +
423         avg(nvl(vsize(GREATEST(CD.LAST_UPDATE_DATE, INTYP.LAST_UPDATE_DATE)), 0)) +
424         avg(nvl(vsize(NULL), 0)) +
425         avg(nvl(vsize(NULL), 0)) +
426         avg(nvl(vsize(NULL), 0)) +
427         avg(nvl(vsize(NULL), 0)) +
428         avg(nvl(vsize(NULL), 0)) +
429         avg(nvl(vsize(TO_DATE(NULL)), 0)) +
430         16
431         FROM
432         AS_INTEREST_CODES_V CD ,
433         AS_INTEREST_TYPES_V INTYP ,
434         EDW_LOCAL_INSTANCE INST
435         WHERE
436         CD.INTEREST_TYPE_ID = INTYP.INTEREST_TYPE_ID
437         AND CD.PARENT_INTEREST_CODE_ID IS NULL
438         AND INTYP.EXPECTED_PURCHASE_FLAG = 'Y'
439         AND ( ( CD.LAST_UPDATE_DATE > TO_DATE('1000/01/01', 'YYYY/MM/DD') )
440         OR (INTYP.LAST_UPDATE_DATE > TO_DATE('1000/01/01', 'YYYY/MM/DD') ) )
441         and cd.last_update_date between
442         p_from_date and p_to_date;
443 
444 
445   -- EDW_ITEM_PROD_GRP_LCV
446 
447   CURSOR c_11 IS
448         SELECT
449         avg(nvl(vsize(SCD.INTEREST_CODE_ID || '-' || INST.INSTANCE_CODE || '-SECN_CODE'), 0)) +
450         avg(nvl(vsize(PCD.INTEREST_CODE_ID || '-' || INST.INSTANCE_CODE || '-PRIM_CODE'), 0)) +
451         avg(nvl(vsize(SCD.CODE ||'(' || PCD.CODE || ')' || '(' || INTYP.INTEREST_TYPE || ')'), 0)) +
452         avg(nvl(vsize(SCD.CODE), 0)) +
453         avg(nvl(vsize(SCD.DESCRIPTION), 0)) +
454         avg(nvl(vsize(SCD.INTEREST_CODE_ID), 0)) +
455         avg(nvl(vsize(SCD.MASTER_ENABLED_FLAG), 0)) +
456         avg(nvl(vsize(INST.INSTANCE_CODE), 0)) +
457         avg(nvl(vsize(SCD.CREATION_DATE), 0)) +
458         avg(nvl(vsize(GREATEST(SCD.LAST_UPDATE_DATE, PCD.LAST_UPDATE_DATE, INTYP.LAST_UPDATE_DATE)), 0)) +
459         avg(nvl(vsize(NULL), 0)) +
460         avg(nvl(vsize(NULL), 0)) +
461         avg(nvl(vsize(NULL), 0)) +
462         avg(nvl(vsize(NULL), 0)) +
463         avg(nvl(vsize(NULL), 0)) +
464         avg(nvl(vsize(TO_DATE(NULL)), 0)) +
465         16
466         FROM
467         AS_INTEREST_CODES_V SCD ,
468         AS_INTEREST_CODES_V PCD ,
469         AS_INTEREST_TYPES_V INTYP ,
470         EDW_LOCAL_INSTANCE INST
471         WHERE
472         SCD.PARENT_INTEREST_CODE_ID = PCD.INTEREST_CODE_ID
473         AND PCD.PARENT_INTEREST_CODE_ID IS NULL
474         AND SCD.INTEREST_TYPE_ID = PCD.INTEREST_TYPE_ID
475         AND PCD.INTEREST_TYPE_ID = INTYP.INTEREST_TYPE_ID
476         AND INTYP.EXPECTED_PURCHASE_FLAG = 'Y'
477         AND ( ( SCD.LAST_UPDATE_DATE > TO_DATE('1000/01/01', 'YYYY/MM/DD') )
478         OR ( PCD.LAST_UPDATE_DATE > TO_DATE('1000/01/01', 'YYYY/MM/DD') )
479         OR (INTYP.LAST_UPDATE_DATE > TO_DATE('1000/01/01', 'YYYY/MM/DD') ) )
483 
480         and scd.last_update_date between
481         p_from_date and p_to_date;
482 
484   -- EDW_ITEM_PROD_LINE_LCV
485 
486   CURSOR c_12 IS
487         SELECT
488         avg(nvl(vsize(INTYP.INTEREST_TYPE_ID || '-' || INST.INSTANCE_CODE || '-INTR_TYPE'), 0)) +
489         avg(nvl(vsize('ALL'), 0)) +
490         avg(nvl(vsize(INTYP.INTEREST_TYPE), 0)) +
491         avg(nvl(vsize(INTYP.INTEREST_TYPE), 0)) +
492         avg(nvl(vsize(INTYP.DESCRIPTION), 0)) +
493         avg(nvl(vsize(INTYP.INTEREST_TYPE_ID), 0)) +
494         avg(nvl(vsize(INTYP.MASTER_ENABLED_FLAG), 0)) +
495         avg(nvl(vsize(INST.INSTANCE_CODE), 0)) +
496         avg(nvl(vsize(INTYP.CREATION_DATE), 0)) +
497         avg(nvl(vsize(INTYP.LAST_UPDATE_DATE), 0)) +
498         avg(nvl(vsize(NULL), 0)) +
499         avg(nvl(vsize(NULL), 0)) +
500         avg(nvl(vsize(NULL), 0)) +
501         avg(nvl(vsize(NULL), 0)) +
502         avg(nvl(vsize(NULL), 0)) +
503         avg(nvl(vsize(TO_DATE(NULL)), 0)) +
504         16
505         FROM
506         AS_INTEREST_TYPES_V INTYP ,
507         EDW_LOCAL_INSTANCE INST
508         WHERE
509         INTYP.EXPECTED_PURCHASE_FLAG = 'Y'
510         and intyp.last_update_date between
511         p_from_date and p_to_date;
512 
513 
514 		l_current NUMBER := 0;
515 		l_total NUMBER := 0;
516 
517   BEGIN
518 
519 	OPEN c_1;
520 	FETCH c_1 INTO l_current;
521 	l_total := l_total + l_current;
522 	CLOSE c_1;
523 
524  	OPEN c_2;
525 	FETCH c_2 INTO l_current;
526 	l_total := l_total + l_current;
527 	CLOSE c_2;
528 
529 	OPEN c_3;
530 	FETCH c_3 INTO l_current;
531 	l_total := l_total + l_current;
532 	CLOSE c_3;
533 
534 	OPEN c_4;
535 	FETCH c_4 INTO l_current;
536 	l_total := l_total + l_current;
537 	CLOSE c_4;
538 
539 	OPEN c_5;
540 	FETCH c_5 INTO l_current;
541 	l_total := l_total + l_current;
542 	CLOSE c_5;
543 
544 	OPEN c_6;
545 	FETCH c_6 INTO l_current;
546 	l_total := l_total + l_current;
547 	CLOSE c_6;
548 
549 	OPEN c_7;
550 	FETCH c_7 INTO l_current;
551 	l_total := l_total + 22*l_current;
552 	CLOSE c_7;
553 
554 	OPEN c_8;
555 	FETCH c_8 INTO l_current;
556 	l_total := l_total + 6*l_current;
557 	CLOSE c_8;
558 
559 	OPEN c_9;
560 	FETCH c_9 INTO l_current;
561 	l_total := l_total + l_current;
562 	CLOSE c_9;
563 
564 	OPEN c_10;
565 	FETCH c_10 INTO l_current;
566 	l_total := l_total + l_current;
567 	CLOSE c_10;
568 
569 	OPEN c_11;
570 	FETCH c_11 INTO l_current;
571 	l_total := l_total + l_current;
572 	CLOSE c_11;
573 
574 	OPEN c_12;
575 	FETCH c_12 INTO l_current;
576 	l_total := l_total + l_current;
577 	CLOSE c_12;
578 
579 	p_avg_row_len := ceil(l_total) + 3*31;
580 
581 EXCEPTION
582   WHEN OTHERS THEN
583 	IF c_1%ISOPEN THEN
584 		CLOSE c_1;
585 	END IF;
586 
587 	IF c_2%ISOPEN THEN
588 	   CLOSE c_2;
589 	END IF;
590 
591 	IF c_3%ISOPEN THEN
592 	   CLOSE c_3;
593 	END IF;
594 
595 	IF c_4%ISOPEN THEN
596 	   CLOSE c_4;
597 	END IF;
598 
599     IF c_5%ISOPEN THEN
600        CLOSE c_5;
601 	END IF;
602 
603  	IF c_6%ISOPEN THEN
604 	   CLOSE c_6;
605 	END IF;
606 
607 	IF c_7%ISOPEN THEN
608 	   CLOSE c_7;
609 	END IF;
610 
611 	IF c_8%ISOPEN THEN
612 	   CLOSE c_8;
613 	END IF;
614 
615 	IF c_9%ISOPEN THEN
616 	   CLOSE c_9;
617 	END IF;
618 
619 	IF c_10%ISOPEN THEN
620 	   CLOSE c_10;
621 	END IF;
622 
623 	IF c_11%ISOPEN THEN
624 	   CLOSE c_11;
625 	END IF;
626 
627 	IF c_12%ISOPEN THEN
628 	   CLOSE c_12;
629 	END IF;
630 
631 
632   END EST_ROW_LEN;
633 
634 END EDW_ITEMS_M_SIZE;