[Home] [Help]
PACKAGE BODY: APPS.OPI_EDW_UOM_CONV_F_SZ
Source
1 PACKAGE BODY OPI_EDW_UOM_CONV_F_SZ AS
2 /* $Header: OPIOUCZB.pls 120.1 2005/06/16 03:52:28 appldev $*/
3
4 PROCEDURE cnt_rows(p_from_date DATE,
5 p_to_date DATE,
6 p_num_rows OUT NOCOPY NUMBER) IS
7 CURSOR c_cnt_rows IS
8 select sum(cnt)
9 from (select count(*) cnt
10 FROM EDW_LOCAL_INSTANCE ELI, MTL_UOM_CONVERSIONS MUC,
11 MTL_UNITS_OF_MEASURE MUOM, MTL_UNITS_OF_MEASURE BUOM,
12 mtl_system_items_kfv msik
13 WHERE
14 muom.uom_code = muc.uom_code and
15 muom.uom_class = muc.uom_class and
16 muom.uom_class = buom.uom_class and
17 buom.BASE_UOM_FLAG='Y' and
18 msik.inventory_item_id (+) = MUC.INVENTORY_ITEM_ID and
19 muom.last_update_date between p_from_date and p_to_date
20 UNION
21 select count(*) cnt
22 FROM
23 edw_local_instance eli,
24 mtl_uom_class_conversions mucc ,
25 mtl_system_items_kfv msik
26 where
27 mucc.from_uom_code <> mucc.to_uom_code and
28 msik.inventory_item_id = MUCC.INVENTORY_ITEM_ID and
29 mucc.last_update_date between p_from_date and p_to_date
30 group by DECODE(MUCC.INVENTORY_ITEM_ID,0, 'STANDARD',
31 TO_CHAR(MUCC.INVENTORY_ITEM_ID)) ||'-' ||
32 MUCC.FROM_UOM_CODE||'-'||MUCC.TO_UOM_CODE||'-'||
33 ELI.INSTANCE_CODE , MUCC.INVENTORY_ITEM_ID,
34 MUCC.FROM_UOM_CODE, MUCC.TO_UOM_CODE,
35 MUCC.CONVERSION_RATE, ELI.INSTANCE_CODE, MUCC.LAST_UPDATE_DATE, MUCC.CREATION_DATE ) ;
36
37 BEGIN
38
39 OPEN c_cnt_rows;
40 FETCH c_cnt_rows INTO p_num_rows;
41 CLOSE c_cnt_rows;
42
43 END; -- procedure cnt_rows.
44
45
46 PROCEDURE est_row_len(p_from_date DATE, p_to_date DATE, p_avg_row_len OUT NOCOPY NUMBER) IS
47 x_UOM_CONV_PK NUMBER ;
48 x_EDW_UOM_FK NUMBER ;
49 x_EDW_BASE_UOM_FK NUMBER ;
50 x_INVENTORY_ITEM_ID NUMBER ;
51 x_UOM NUMBER ;
52 x_BASE_UOM NUMBER ;
53 x_CONVERSION_RATE NUMBER ;
54 x_EDW_CONVERSION_RATE NUMBER ;
55 x_INSTANCE_FK NUMBER ;
56 x_USER_ATTRIBUTE1 NUMBER ;
57 x_LAST_UPDATE_DATE NUMBER ;
58 x_CREATION_DATE NUMBER ;
59 x_CLASS_CONVERSION_FLAG NUMBER ;
60
61 x_total NUMBER := 0 ;
62
63 CURSOR c_1 IS
64 SELECT
65 -- x_UOM_CONV_PK NUMBER ;
66 avg(nvl(vsize(DECODE(MUC.INVENTORY_ITEM_ID,0,'STANDARD',TO_CHAR(MUC.INVENTORY_ITEM_ID))||MUC.UOM_CODE||eli.instance_code), 0)) ,
67 -- x_EDW_UOM_FK NUMBER ;
68 -- NULL,
69 -- x_EDW_BASE_UOM_FK NUMBER ;
70 -- NULL,
71 -- x_INVENTORY_ITEM_ID NUMBER ;
72 avg(nvl(vsize(MUC.INVENTORY_ITEM_ID), 0)),
73 -- x_UOM NUMBER ;
74 avg(nvl(vsize(MUC.UOM_CODE), 0)),
75 -- x_BASE_UOM NUMBER ;
76 avg(nvl(vsize(BUOM.UOM_CODE), 0)),
77 -- x_CONVERSION_RATE NUMBER ;
78 avg(nvl(vsize(MUC.CONVERSION_RATE), 0)),
79 -- x_EDW_CONVERSION_RATE NUMBER ;
80 -- TO_NUMBER(NULL),
81 -- x_INSTANCE_FK NUMBER ;
82 avg(nvl(vsize(ELI.INSTANCE_CODE), 0)),
83 -- x_USER_ATTRIBUTE1 NUMBER ;
84 avg(nvl(vsize(msik.concatenated_segments), 0)),
85 -- x_LAST_UPDATE_DATE NUMBER ;
86 avg(nvl(vsize(MUC.LAST_UPDATE_DATE), 0)),
87 -- x_CREATION_DATE NUMBER ;
88 avg(nvl(vsize(MUC.CREATION_DATE), 0))
89 -- x_CLASS_CONVERSION_FLAG NUMBER ;
90 FROM
91 EDW_LOCAL_INSTANCE ELI,
92 MTL_UOM_CONVERSIONS MUC,
93 MTL_UNITS_OF_MEASURE MUOM,
94 MTL_UNITS_OF_MEASURE BUOM,
95 mtl_system_items_kfv msik
96 WHERE
97 muom.uom_code = muc.uom_code and
98 muom.uom_class = muc.uom_class and
99 muom.uom_class = buom.uom_class and
100 buom.BASE_UOM_FLAG='Y' and
101 msik.inventory_item_id (+) = MUC.INVENTORY_ITEM_ID and
102 muom.last_update_date between p_from_date and p_to_date ;
103 BEGIN
104 OPEN c_1;
105 FETCH c_1 INTO
106 x_UOM_CONV_PK ,
107 x_INVENTORY_ITEM_ID,
108 x_UOM ,
109 x_BASE_UOM ,
110 x_CONVERSION_RATE ,
111 x_INSTANCE_FK ,
112 x_USER_ATTRIBUTE1,
113 x_LAST_UPDATE_DATE,
114 x_CREATION_DATE ;
115 CLOSE c_1 ;
116 x_total := 3 +
117 x_total +
118 ceil(x_UOM_CONV_PK + 1)+
119 ceil(x_INVENTORY_ITEM_ID+ 1)+
120 ceil(x_UOM+ 1) +
121 ceil(x_BASE_UOM+ 1) +
122 ceil(x_CONVERSION_RATE+ 1) +
123 ceil(x_INSTANCE_FK+ 1) +
124 ceil(x_USER_ATTRIBUTE1+ 1)+
125 ceil(x_LAST_UPDATE_DATE+ 1)+
126 ceil(x_CREATION_DATE + 1) ;
127
128 p_avg_row_len := x_total ;
129
130 END est_row_len ; -- procedure est_row_len.
131
132 END OPI_EDW_UOM_CONV_F_SZ ;