PACKAGE BODY: APPS.OPI_EDW_OPI_LOT_M_SZ

Source

1 PACKAGE BODY OPI_EDW_OPI_LOT_M_SZ AS
2 /* $Header: OPIOLTZB.pls 120.1 2005/06/07 02:29:29 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 count(*) cnt
9 	FROM
10 	MTL_LOT_NUMBERS
11 	where  last_update_date between p_from_date and p_to_date ;
12 BEGIN
13 
14   OPEN c_cnt_rows;
15        FETCH c_cnt_rows INTO p_num_rows;
16   CLOSE c_cnt_rows;
17 
18 END;  -- procedure cnt_rows.
19 
20 
21 PROCEDURE est_row_len(p_from_date DATE,
22                    p_to_date DATE,
23                    p_est_row_len OUT NOCOPY NUMBER) IS
24 	x_EDW_LOT_PK  NUMBER ;
25 	x_Organization_id NUMBER ;
26 	x_Inventory_Item_id NUMBER ;
27 	x_Name NUMBER ;
28 	x_LOT_NAME NUMBER ;
29 	x_EXPIRATION_DATE NUMBER ;
30 	x_LOT_DESCRIPTION NUMBER ;
31 	x_LAST_UPDATE_DATE NUMBER ;
32 	x_CREATION_DATE NUMBER ;
33 	x_INSTANCE NUMBER;
34 
35 	x_total NUMBER := 0 ;
36 
37 CURSOR c_1  IS
38 
39 	SELECT
40 	-- EDW_LOT_PK need to add instance_code
41 	avg(nvl(vsize(INVENTORY_ITEM_ID||ORGANIZATION_ID||LOT_NUMBER), 0)),
42 	-- EDW_LOT_DP
43 	-- Organization_id
44 	avg(nvl(vsize(ORGANIZATION_ID), 0)),
45 	-- Inventory_Item_id
46 	avg(nvl(vsize(Inventory_Item_id), 0)),
47 	-- Name
48 	avg(nvl(vsize(lot_number||Inventory_Item_ID||Organization_ID), 0)) ,
49 	-- LOT_NAME
50 	avg(nvl(vsize(lot_number), 0)),
51 	-- ITEM_REVISION
52 	-- NETTABLE_FLAG
53 	-- EXPIRATION_DATE
54 	avg(nvl(vsize(expiration_date), 0)),
55 	-- LOT_DESCRIPTION
56 	avg(nvl(vsize(lot_number), 0)) ,
57 	-- LAST_UPDATE_DATE
58 	avg(nvl(vsize(LAST_UPDATE_DATE), 0)),
59 	-- CREATION_DATE
60 	avg(nvl(vsize(CREATION_DATE), 0))
61 	FROM
62 	MTL_LOT_NUMBERS ;
63 	--WHERE
64 	--last_update_date between p_from_date and p_to_date ;
65 
66   CURSOR c_2 IS
67 	SELECT
68 		avg(nvl(vsize(instance_code), 0))
69 	FROM	EDW_LOCAL_INSTANCE ;
70         -- WHERE last_update_date between
71        --  p_from_date  and  p_to_date;
72 BEGIN
73 
74   /* dbms_output.put_line ('******************'||x_total||'******') ; */
75 
76   OPEN c_1;
77        FETCH c_1 INTO
78 	x_EDW_LOT_PK  ,
79 	x_Organization_id,
80 	x_Inventory_Item_id,
81 	x_Name ,
82 	x_LOT_NAME ,
83 	x_EXPIRATION_DATE ,
84 	x_LOT_DESCRIPTION,
85 	x_LAST_UPDATE_DATE ,
86 	x_CREATION_DATE  ;
87   CLOSE c_1;
88 
89   /* dbms_output.put_line ('******************'||x_total||'******') ; */
90     x_total := 3 +
91 	    x_total +
92 	ceil(x_EDW_LOT_PK + 1) +
93 	ceil(x_Organization_id+ 1) +
94 	ceil(x_Inventory_Item_id+ 1) +
95 	ceil(x_Name + 1) +
96 	ceil(x_LOT_NAME + 1) +
97 	ceil(x_EXPIRATION_DATE+ 1) +
98 	ceil(x_LOT_DESCRIPTION+ 1) +
99 	ceil(x_LAST_UPDATE_DATE + 1) +
100 	ceil(x_CREATION_DATE+ 1)   ;
101 
102   /* dbms_output.put_line ('******************'||x_total||'******') ; */
103   OPEN c_2;
104        FETCH c_2 INTO
105 	x_INSTANCE ;
106   CLOSE c_2;
107 
108   /* dbms_output.put_line ('******************'||x_total||'******') ; */
109   x_total := x_total +
110 	ceil(x_INSTANCE + 1) ;
111 
112 	p_est_row_len := x_total ;
113 
114 
115   /* dbms_output.put_line ('******************'||x_total||'******') ; */
116 
117 
118 END ;
119 
120 END OPI_EDW_OPI_LOT_M_SZ ;  -- procedure est_row_len.