[Home] [Help]
PACKAGE BODY: APPS.OPI_EDW_INV_DAILY_STAT_FOPM_SZ
Source
1 PACKAGE BODY OPI_EDW_INV_DAILY_STAT_FOPM_SZ AS
2 /* $Header: OPIPINZB.pls 120.1 2005/06/07 03:14:06 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 BEGIN
9
10 SELECT COUNT(*) INTO p_num_rows
11 FROM (
12 (SELECT
13 org.CO_CODE,org.ORGN_CODE,cmp.WHSE_CODE,cmp.ITEM_ID,cmp.LOT_ID,
14 nvl(cmp.LOCATION,'NONE'),trunc(cmp.TRANS_DATE),cmp.TRANS_UM
15 from ic_tran_cmp cmp,ic_loct_inv loct, sy_orgn_mst org,ic_whse_mst whse
16 where trunc(cmp.last_update_date) between p_from_date and p_to_date
17 AND loct.item_id = cmp.item_id AND loct.lot_id = cmp.lot_id
18 AND loct.whse_code = cmp.whse_code AND loct.location = cmp.location
19 AND cmp.whse_code is not null
20 AND whse.whse_code = loct.whse_code
21 AND whse.orgn_code = org.orgn_code
22 and cmp.whse_code = whse.whse_code
23 AND cmp.location is not null
24 GROUP BY org.CO_CODE,org.ORGN_CODE,cmp.WHSE_CODE,cmp.ITEM_ID,cmp.LOT_ID,
25 nvl(cmp.LOCATION,'NONE'),trunc(cmp.TRANS_DATE),cmp.TRANS_UM)
26 UNION ALL
27 ( SELECT org.CO_CODE,org.ORGN_CODE,pnd.WHSE_CODE,pnd.ITEM_ID,pnd.LOT_ID,
28 nvl(pnd.LOCATION,'NONE'),trunc(pnd.TRANS_DATE),pnd.TRANS_UM
29 from ic_tran_pnd pnd ,ic_loct_inv loct , sy_orgn_mst org,ic_whse_mst whse
30 where trunc(pnd.last_update_date) between p_from_date and p_to_date
31 AND pnd.COMPLETED_IND = 1
32 AND loct.item_id = pnd.item_id
33 AND loct.lot_id = pnd.lot_id
34 AND loct.whse_code = pnd.whse_code
35 AND loct.location = pnd.location
36 AND whse.whse_code = loct.whse_code
37 AND whse.orgn_code = org.orgn_code
38 and pnd.whse_code = whse.whse_code
39 AND pnd.delete_mark = 0
40 GROUP BY org.CO_CODE,org.ORGN_CODE,pnd.WHSE_CODE,pnd.ITEM_ID,pnd.LOT_ID,
41 nvl(pnd.LOCATION,'NONE'),trunc(pnd.TRANS_DATE),pnd.TRANS_UM));
42
43
44 --dbms_output.put_line ('Number of rows : '||p_num_rows);
45 EXCEPTION
46 WHEN OTHERS THEN
47 p_num_rows := 0;
48 END; -- procedure cnt_rows.
49
50
51
52 PROCEDURE est_row_len(p_from_date DATE,
53 p_to_date DATE,
54 p_avg_row_len OUT NOCOPY NUMBER) IS
55 x_date number := 7;
56 x_total number := 0;
57 x_constant number := 6;
58
59 x_INSTANCE_FK NUMBER;
60
61 --------
62
63 CURSOR C_Trans_dtl IS
64 SELECT avg(nvl(vsize(tr_dtl.co_code),0)) co_code,
65 avg(nvl(vsize(tr_dtl.orgn_code),0)) orgn_code,
66 avg(nvl(vsize(tr_dtl.whse_code),0)) whse_code,
67 avg(nvl(vsize(tr_dtl.mtl_orgn_id),0)) mtl_org_id,
68 avg(nvl(vsize(tr_dtl.item_id),0)) item_id,
69 avg(nvl(vsize(tr_dtl.Lot_id),0)) lot_id,
70 avg(nvl(vsize(tr_dtl.Loct),0)) loct,
71 avg(nvl(vsize(tr_dtl.trx_date),0)) trx_date,
72 avg(nvl(vsize(tr_dtl.UOM),0)) uom,
73 avg(nvl(vsize(tr_dtl.item_status),0)) item_status,
74 avg(nvl(vsize(tr_dtl.item_type),0)) item_type,
75 avg(nvl(vsize(tr_dtl.commodity),0)) commodity,
76 avg(nvl(vsize(tr_dtl.qty),0)) trans_qty
77 FROM
78 ((SELECT
79 org.CO_CODE co_code,org.ORGN_CODE orgn_code,cmp.WHSE_CODE whse_code,
80 whse.mtl_organization_id mtl_orgn_id,cmp.ITEM_ID item_id,cmp.LOT_ID lot_id,
81 nvl(cmp.LOCATION,'NONE') LOCT,trunc(cmp.TRANS_DATE) trx_date,cmp.TRANS_UM UOM,
82 iim.inactive_ind item_status,iim.inv_type item_type,iim.commodity_code commodity,
83 SUM(cmp.TRANS_QTY) QTY
84 from ic_tran_cmp cmp,
85 ic_loct_inv loct,
86 sy_orgn_mst org,
87 ic_whse_mst whse,
88 ic_item_mst iim
89 where trunc(cmp.last_update_date) between p_from_date and p_to_date
90 AND loct.item_id = cmp.item_id AND loct.lot_id = cmp.lot_id
91 AND loct.whse_code = cmp.whse_code AND loct.location = cmp.location
92 AND cmp.whse_code is not null
93 AND whse.whse_code = loct.whse_code
94 AND whse.orgn_code = org.orgn_code
95 and cmp.whse_code = whse.whse_code
96 AND cmp.location is not null
97 AND cmp.item_id = iim.item_id
98 GROUP BY org.CO_CODE,org.ORGN_CODE,cmp.WHSE_CODE,whse.mtl_organization_id,cmp.ITEM_ID,cmp.LOT_ID,
99 nvl(cmp.LOCATION,'NONE'),trunc(cmp.TRANS_DATE),cmp.TRANS_UM,
100 iim.inactive_ind,iim.inv_type,iim.commodity_code)
101 UNION ALL
102 ( SELECT org.CO_CODE co_code,org.ORGN_CODE org_code ,pnd.WHSE_CODE whse_code,
103 whse.mtl_organization_id mtl_orgn_id,pnd.ITEM_ID item_id,pnd.LOT_ID lot_id,
104 nvl(pnd.LOCATION,'NONE'),trunc(pnd.TRANS_DATE) trx_date,pnd.TRANS_UM UOM,
105 iim.inactive_ind item_status,iim.inv_type item_type,iim.commodity_code commodity,SUM(TRANS_QTY) QTY
106 from ic_tran_pnd pnd ,
107 ic_loct_inv loct ,
108 sy_orgn_mst org,
109 ic_whse_mst whse ,
110 ic_item_mst iim
111 where trunc(pnd.last_update_date) between p_from_date and p_to_date
112 AND pnd.COMPLETED_IND = 1
113 AND pnd.item_id = iim.item_id
114 AND loct.item_id = pnd.item_id
115 AND loct.lot_id = pnd.lot_id
116 AND loct.whse_code = pnd.whse_code
117 AND loct.location = pnd.location
118 AND whse.whse_code = loct.whse_code
119 AND whse.orgn_code = org.orgn_code
120 and pnd.whse_code = whse.whse_code
121 AND pnd.delete_mark = 0
122 GROUP BY org.CO_CODE,org.ORGN_CODE,pnd.WHSE_CODE,whse.mtl_organization_id,pnd.ITEM_ID,pnd.LOT_ID,
123 nvl(pnd.LOCATION,'NONE'),trunc(pnd.TRANS_DATE),pnd.TRANS_UM,
124 iim.inactive_ind,iim.inv_type,iim.commodity_code)) tr_dtl;
125
126 CURSOR c_instance IS
127 SELECT
128 avg(nvl(vsize(instance_code), 0))
129 FROM EDW_LOCAL_INSTANCE ;
130
131
132 CURSOR c_sob_dtl IS
133 SELECT AVG(Nvl(Vsize(EDW_TIME_PKG.CAL_DAY_FK(Sysdate, SOB_ID) ),0)) date_fk,
134 AVG(Nvl(Vsize(EDW_TIME_PKG.CAL_DAY_TO_CAL_PERIOD_FK(Sysdate, SOB_ID) ),0)) perd_fk,
135 AVG(NVL(VSIZE(SOB_ID),0)) SOB_ID,
136 AVG(NVL(VSIZE(ORG_ID),0)) ORG_ID,
137 AVG(NVL(VSIZE(BASE_CURRENCY_CODE),0)) CURRENCY
138 FROM gl_plcy_mst;
139
140 Trans_dtl_rec C_Trans_dtl%ROWTYPE;
141 sob_dtl_rec c_sob_dtl%ROWTYPE;
142 BEGIN
143 OPEN c_instance;
144 FETCH c_instance INTO x_instance_fk;
145 CLOSE c_instance;
146
147 OPEN C_Trans_dtl;
148 FETCH C_Trans_dtl INTO Trans_dtl_rec;
149 CLOSE C_Trans_dtl;
150
151 OPEN c_sob_dtl;
152 FETCH c_sob_dtl INTO sob_dtl_rec;
153 CLOSE c_sob_dtl;
154
155
156 x_total := 3 +
157 x_total +
158 Ceil( Trans_dtl_rec.CO_CODE + 1) +
159 Ceil( Trans_dtl_rec.ORGN_CODE + 1) +
160 Ceil( Trans_dtl_rec.WHSE_CODE + 1) * 2 +
161 Ceil( Trans_dtl_rec.loct + 1) * 2 +
162 Ceil( Trans_dtl_rec.ITEM_ID + 1) * 3 +
163 Ceil( Trans_dtl_rec.uom + 1) +
164 Ceil( Trans_dtl_rec.mtl_org_id + 1) +
165 Ceil( Trans_dtl_rec.lot_id + 1) +
166 Ceil( Trans_dtl_rec.item_status + 1) +
167 Ceil( Trans_dtl_rec.item_type + 1) +
168 Ceil( Trans_dtl_rec.commodity + 1) +
169 Ceil( x_date + 1) * 3 +
170 Ceil( Trans_dtl_rec.trans_qty + 1) * 12 +
171 Ceil( Trans_dtl_rec.trans_qty + 2) * 24+
172 Ceil( x_instance_fk + 1) * 6+
173 Ceil( sob_dtl_rec.currency + 1) +
174 Ceil( sob_dtl_rec.date_fk + 1) +
175 Ceil( sob_dtl_rec.perd_fk + 1) +
176 -- All hardcoded tokens
177 Ceil( 44 + 1) ;
178
179
180 p_avg_row_len := x_total;
181
182 END; -- procedure est_row_len.
183
184 END; -- package body OPI_EDW_INV_DAILY_STAT_FOPM_SZ