[Home] [Help]
PACKAGE BODY: APPS.OPI_EDW_INV_DAILY_STAT_F_SZ
Source
1 PACKAGE BODY OPI_EDW_INV_DAILY_STAT_F_SZ AS
2 /* $Header: OPIOINZB.pls 120.1 2005/06/07 02:07:01 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
9 CURSOR mmt_cnt_rows IS
10 SELECT count(*)
11 FROM mtl_material_transactions mmt
12 WHERE transaction_date >= p_from_date
13 AND transaction_date <= p_to_Date
14 GROUP BY trunc(mmt.TRANSACTION_DATE),mmt.ORGANIZATION_ID,mmt.INVENTORY_ITEM_ID,
15 mmt.COST_GROUP_ID,mmt.REVISION,mmt.SUBINVENTORY_CODE,mmt.locator_id;
16
17 CURSOR wt_cnt_rows IS
18 select count(*) from (
19 SELECT wdj.primary_item_id,wdj.bom_revision
20 FROM wip_transactions wt,
21 wip_discrete_jobs wdj
22 WHERE wt.transaction_date between p_from_date
23 and p_to_Date
24 AND wt.wip_entity_id = wdj.wip_entity_id
25 GROUP BY wdj.primary_item_id,
26 wdj.bom_revision
27 UNION ALL
28 SELECT we.primary_item_id,wrs.bom_revision
29 FROM wip_transactions wt,
30 wip_repetitive_schedules wrs,
31 wip_entities we
32 WHERE wt.transaction_date between p_from_date
33 and p_to_Date
34 AND wt.wip_entity_id = wrs.wip_entity_id
35 AND wt.wip_entity_id = we.wip_entity_id
36 GROUP BY we.primary_item_id,
37 wrs.bom_revision
38 UNION ALL
39 SELECT wfs.primary_item_id,wfs.bom_revision
40 FROM wip_transactions wt,
41 wip_flow_schedules wfs
42 WHERE wt.transaction_date between p_from_date
43 and p_to_Date
44 AND wt.wip_entity_id = wfs.wip_entity_id
45 GROUP BY wfs.primary_item_id,
46 wfs.bom_revision );
47
48 p_mmt_num_rows number:=0;
49 p_wt_num_rows number:=0;
50
51 BEGIN
52
53
54 /* Called it in loop rather than defining an INLINE SQL as cursor performance was much better with the number of records in mmt*/
55
56 OPEN mmt_cnt_rows;
57 loop
58 FETCH mmt_cnt_rows INTO p_mmt_num_rows;
59 if(mmt_cnt_rows%NOTFOUND) then
60 CLOSE mmt_cnt_rows;
61 EXIT;
62 end if;
63 p_num_rows := nvl(p_num_rows,0)+1;
64 end loop;
65
66
67 OPEN wt_cnt_rows;
68 FETCH wt_cnt_rows INTO p_wt_num_rows;
69 CLOSE wt_cnt_rows;
70
71 p_num_rows := nvl(p_num_rows,0)+nvl(p_wt_num_rows,0);
72
73 END; -- procedure cnt_rows.
74
75
76
77 PROCEDURE est_row_len(p_from_date DATE,
78 p_to_date DATE,
79 p_avg_row_len OUT NOCOPY NUMBER) IS
80 x_date number := 7;
81 x_total number := 0;
82 x_constant number := 6;
83
84 x_AVG_INT_QTY NUMBER;
85 x_AVG_INT_VAL_B NUMBER;
86 x_AVG_INT_VAL_G NUMBER;
87 x_AVG_ONH_QTY NUMBER;
88 x_AVG_ONH_VAL_B NUMBER;
89 x_AVG_ONH_VAL_G NUMBER;
90 x_AVG_WIP_QTY NUMBER;
91 x_AVG_WIP_VAL_B NUMBER;
92 x_AVG_WIP_VAL_G NUMBER;
93 x_BASE_CURRENCY_FK NUMBER;
94 x_BASE_UOM_FK NUMBER;
95 x_BEG_INT_QTY NUMBER;
96 x_BEG_INT_VAL_B NUMBER;
97 x_BEG_INT_VAL_G NUMBER;
98 x_BEG_ONH_QTY NUMBER;
99 x_BEG_ONH_VAL_B NUMBER;
100 x_BEG_ONH_VAL_G NUMBER;
101 x_BEG_WIP_QTY NUMBER;
102 x_BEG_WIP_VAL_B NUMBER;
103 x_BEG_WIP_VAL_G NUMBER;
104 x_COMMODITY_CODE VARCHAR2(40);
105 x_COST_GROUP NUMBER;
106 x_CREATION_DATE DATE;
107 x_END_INT_QTY NUMBER;
108 x_END_INT_VAL_B NUMBER;
109 x_END_INT_VAL_G NUMBER;
110 x_END_ONH_QTY NUMBER;
111 x_END_ONH_VAL_B NUMBER;
112 x_END_ONH_VAL_G NUMBER;
113 x_END_WIP_QTY NUMBER;
114 x_END_WIP_VAL_B NUMBER;
115 x_END_WIP_VAL_G NUMBER;
116 x_FROM_ORG_QTY NUMBER;
117 x_FROM_ORG_VAL_B NUMBER;
118 x_FROM_ORG_VAL_G NUMBER;
119 x_INSTANCE_FK NUMBER;
120 x_INV_ADJ_QTY NUMBER;
121 x_INV_ADJ_VAL_B NUMBER;
122 x_INV_ADJ_VAL_G NUMBER;
123 x_INV_DAILY_STATUS_PK NUMBER;
124 x_INV_ORG_FK NUMBER;
125 x_ITEM_ORG_FK NUMBER;
126 x_ITEM_STATUS VARCHAR2(40);
127 x_ITEM_TYPE VARCHAR2(40);
128 x_LAST_UPDATE_DATE DATE;
129 x_LOCATOR_FK NUMBER;
130 x_LOT_FK NUMBER;
131 x_NETTABLE_FLAG VARCHAR2(15);
132 x_PO_DEL_QTY NUMBER;
133 x_PO_DEL_VAL_B NUMBER;
134 x_PO_DEL_VAL_G NUMBER;
135 x_PRD_DATE_FK NUMBER;
136 x_TOTAL_REC_QTY NUMBER;
137 x_TOTAL_REC_VAL_B NUMBER;
138 x_TOTAL_REC_VAL_G NUMBER;
139 x_TOT_CUST_SHIP_QTY NUMBER;
140 x_TOT_CUST_SHIP_VAL_B NUMBER;
141 x_TOT_CUST_SHIP_VAL_G NUMBER;
142 x_TOT_ISSUES_QTY NUMBER;
143 x_TOT_ISSUES_VAL_B NUMBER;
144 x_TOT_ISSUES_VAL_G NUMBER;
145 x_TO_ORG_QTY NUMBER;
146 x_TO_ORG_VAL_B NUMBER;
147 x_TO_ORG_VAL_G NUMBER;
148 x_TRX_DATE_FK NUMBER;
149 x_USER_ATTRIBUTE1 VARCHAR2(240);
150 x_USER_ATTRIBUTE10 VARCHAR2(240);
151 x_USER_ATTRIBUTE11 VARCHAR2(240);
152 x_USER_ATTRIBUTE12 VARCHAR2(240);
153 x_USER_ATTRIBUTE13 VARCHAR2(240);
154 x_USER_ATTRIBUTE14 VARCHAR2(240);
155 x_USER_ATTRIBUTE15 VARCHAR2(240);
156 x_USER_ATTRIBUTE2 VARCHAR2(240);
157 x_USER_ATTRIBUTE3 VARCHAR2(240);
158 x_USER_ATTRIBUTE4 VARCHAR2(240);
159 x_USER_ATTRIBUTE5 VARCHAR2(240);
160 x_USER_ATTRIBUTE6 VARCHAR2(240);
161 x_USER_ATTRIBUTE7 VARCHAR2(240);
162 x_USER_ATTRIBUTE8 VARCHAR2(240);
163 x_USER_ATTRIBUTE9 VARCHAR2(240);
164 x_USER_FK1_KEY NUMBER;
165 x_USER_FK2_KEY NUMBER;
166 x_USER_FK3_KEY NUMBER;
167 x_USER_FK4_KEY NUMBER;
168 x_USER_FK5_KEY NUMBER;
169 x_USER_MEASURE1 NUMBER;
170 x_USER_MEASURE2 NUMBER;
171 x_USER_MEASURE3 NUMBER;
172 x_USER_MEASURE4 NUMBER;
173 x_USER_MEASURE5 NUMBER;
174 x_WIP_ASSY_QTY NUMBER;
175 x_WIP_ASSY_VAL_B NUMBER;
176 x_WIP_ASSY_VAL_G NUMBER;
177 x_WIP_COMP_QTY NUMBER;
178 x_WIP_COMP_VAL_B NUMBER;
179 x_WIP_COMP_VAL_G NUMBER;
180 x_WIP_ISSUE_QTY NUMBER;
181 x_WIP_ISSUE_VAL_B NUMBER;
182 x_WIP_ISSUE_VAL_G NUMBER;
183 x_TRX_DATE DATE;
184 x_PERIOD_FLAG NUMBER;
185
186 --------
187
188 cursor c_1 is
189 select
190 avg(nvl(vsize(trunc(mmt.TRANSACTION_DATE)||mmt.ORGANIZATION_ID||mmt.INVENTORY_ITEM_ID||mmt.COST_GROUP_ID||mmt.REVISION||mmt.SUBINVENTORY_CODE||mmt.LOCATOR_ID),0)),
191 avg(nvl(vsize(cost_group_id),0)),
192 avg(nvl(vsize(mmt.INVENTORY_ITEM_ID||mmt.ORGANIZATION_ID),0)),
193 avg(nvl(vsize(mmt.ORGANIZATION_ID),0)),
194 avg(nvl(vsize(mmt.LOCATOR_ID||mmt.SUBINVENTORY_CODE||mmt.ORGANIZATION_ID),0)),
195 avg(nvl(vsize(sum(primary_quantity)),0))
196 from mtl_material_transactions mmt
197 where transaction_date between p_from_date and p_to_date
198 GROUP BY trunc(mmt.TRANSACTION_DATE),mmt.ORGANIZATION_ID,mmt.INVENTORY_ITEM_ID,
199 mmt.COST_GROUP_ID,mmt.REVISION,mmt.SUBINVENTORY_CODE,mmt.locator_id;
200
201
202 cursor c_2 is
203 SELECT
204 avg(nvl(vsize(sum(mta.BASE_TRANSACTION_VALUE)),0))
205 FROM MTL_MATERIAL_TRANSACTIONS mmt,
206 MTL_TRANSACTION_ACCOUNTS mta
207 WHERE mmt.transaction_id = mta.transaction_id
208 AND mta.accounting_line_type = 1
209 AND ((mmt.transaction_action_id in (2,3,12)
210 AND mmt.primary_quantity >0 )
211 OR (mmt.transaction_action_id in (31,32)
212 AND mmt.transaction_source_type_id=5))
213 AND mmt.transaction_date >= p_from_date
214 AND mmt.transaction_date <= p_to_date
215 GROUP BY trunc(mmt.TRANSACTION_DATE),mmt.ORGANIZATION_ID,mmt.INVENTORY_ITEM_ID,mmt.COST_GROUP_ID,
216 mmt.REVISION,mmt.SUBINVENTORY_CODE,mmt.LOCATOR_ID;
217
218
219 CURSOR c_3 IS
220 SELECT
221 avg(nvl(vsize(instance_code), 0))
222 FROM EDW_LOCAL_INSTANCE ;
223
224 CURSOR c_4 is
225 SELECT avg(nvl(vsize(gsob.currency_code), 0))
226 FROM hr_all_organization_units hou,
227 hr_organization_information hoi,
228 gl_sets_of_books gsob
229 WHERE hou.organization_id = hoi.organization_id
230 AND ( hoi.org_information_context || '') ='Accounting Information'
231 AND hoi.org_information1 = to_char(gsob.set_of_books_id) ;
232
233
234 BEGIN
235
236 OPEN c_1;
237 FETCH c_1 INTO
238 x_INV_DAILY_STATUS_PK,
239 x_COST_GROUP,
240 x_ITEM_ORG_FK,
241 x_INV_ORG_FK,
242 x_LOCATOR_FK,
243 x_END_ONH_QTY;
244
245 CLOSE c_1;
246
247 /*------------------------------------------------------------------------------------------------
248 The above value for Ending Onhand quantity should be almost the same
249 for BEG/END/AVG onhand, wip and intransit as well as total receipt and issue qty
250
251 x_AVG_INT_QTY
252 x_AVG_ONH_QTY
253 x_AVG_WIP_QTY
254 x_BEG_INT_QTY
255 x_BEG_ONH_QTY
256 x_BEG_WIP_QTY
257 x_END_INT_QTY
258 x_END_ONH_QTY
259 x_END_WIP_QTY
260 x_TOTAL_REC_QTY
261 x_TOT_ISSUES_QTY
262 QTYsize = 10*x_END_ONH_QTY;
263
264 For other QTY columns we are taking the average of the columns that will be populated.
265
266 x_FROM_ORG_QTY
267 x_INV_ADJ_QTY
268 x_PO_DEL_QTY
269 x_TOT_CUST_SHIP_QTY
270 x_TO_ORG_QTY
271 x_WIP_ASSY_QTY
272 x_WIP_COMP_QTY
273 x_WIP_ISSUE_QTY
274 QTYsize = (10+4)*x_END_ONH_QTY
275 ---------------------------------------------------------------------------------------------------*/
276
277 x_END_ONH_QTY := 14*x_END_ONH_QTY;
278
279
280
281 OPEN c_2;
282 FETCH c_2 INTO
283 x_END_ONH_VAL_B;
284
285 CLOSE c_2;
286
287 /*-------------------------------------------------------------------------------------------------
288 THe above average value for the ending_onhand_value should be the same for
289
290 x_AVG_INT_VAL_B
291 x_AVG_INT_VAL_G
292 x_AVG_ONH_VAL_B
293 x_AVG_ONH_VAL_G
294 x_AVG_WIP_VAL_B
295 x_AVG_WIP_VAL_G
296 x_BEG_INT_VAL_B
297 x_BEG_INT_VAL_G
298 x_BEG_ONH_VAL_B
299 x_BEG_ONH_VAL_G
300 x_BEG_WIP_VAL_B
301 x_BEG_WIP_VAL_G
302 x_END_INT_VAL_B
303 x_END_INT_VAL_G
304 x_END_ONH_VAL_B
305 x_END_ONH_VAL_G
306 x_END_WIP_VAL_B
307 x_END_WIP_VAL_G
308 x_TOTAL_REC_VAL_B
309 x_TOTAL_REC_VAL_G
310 x_TOT_ISSUES_VAL_B
311 x_TOT_ISSUES_VAL_G
312
313 avg size for value columns = 2*11*x_END_ONH_VAL_B; (2 is for VAL_G and VAL_B)
314
315 For other value columns we will take an average
316 x_FROM_ORG_VAL_B
317 x_FROM_ORG_VAL_G
318 x_INV_ADJ_VAL_B
319 x_INV_ADJ_VAL_G
320 x_PO_DEL_VAL_B
321 x_PO_DEL_VAL_G
322 x_TOT_CUST_SHIP_VAL_B
323 x_TOT_CUST_SHIP_VAL_G
324 x_TO_ORG_VAL_B
325 x_TO_ORG_VAL_G
326 x_WIP_ASSY_VAL_B
327 x_WIP_ASSY_VAL_G
328 x_WIP_COMP_VAL_B
329 x_WIP_COMP_VAL_G
330 x_WIP_ISSUE_VAL_B
331 x_WIP_ISSUE_VAL_G
332 avg size for value columns = 2*(11+4)*x_END_ONH_VAL_B
333
334 --------------------------------------------------------------------------------------------------*/
335
336
337 x_END_ONH_VAL_B:= 2*14*x_END_ONH_VAL_B;
338
339
340
341 x_PRD_DATE_FK := x_date;
342 x_TRX_DATE_FK := x_date;
343
344 x_total := 3 +
345 x_total +
346 ceil(x_INV_DAILY_STATUS_PK + 1) +
347 ceil(x_COST_GROUP + 1) +
348 ceil(x_ITEM_ORG_FK + 1) +
349 ceil(x_INV_ORG_FK + 1) +
350 ceil(x_LOCATOR_FK + 1) +
351 ceil(x_END_ONH_QTY + 14) + --- Add 14 becuase there are 30 diff columns.
352 ceil(x_END_ONH_VAL_B + 30) + --- Add 30 because there are 30 different columns.
353 ceil(x_PRD_DATE_FK + 1) +
354 ceil(x_TRX_DATE_FK + 1);
355
356 OPEN c_3;
357 FETCH c_3 INTO x_INSTANCE_FK;
358 CLOSE c_3;
359
360 x_total := x_total + ceil(x_INSTANCE_FK + 1);
361
362 OPEN c_4 ;
363 FETCH c_4 INTO x_BASE_CURRENCY_FK;
364 CLOSE c_4 ;
365
366 x_total := x_total + ceil(x_BASE_CURRENCY_FK + 1);
367
368
369 -- Miscellaneous
370 x_total := x_total + 4 * ceil(x_INSTANCE_FK + 1);
371
372 p_avg_row_len := x_total;
373
374 END; -- procedure est_row_len.
375
376 END; -- package body OPI_EDW_INV_DAILY_STAT_F_SZ