[Home] [Help]
PACKAGE BODY: APPS.EDW_MTL_INVENTORY_LOC_M_SZ
Source
1 PACKAGE BODY EDW_MTL_INVENTORY_LOC_M_SZ AS
2 /* $Header: OPIINLZB.pls 120.1 2005/06/10 13:35:19 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
10 (
11 -- Locator Level
12 select count(*) cnt
13 FROM mtl_item_locations_kfv locf,
14 mtl_parameters mp,
15 hr_organization_units hou
16 where
17 locf.last_update_date between p_from_date and p_to_date AND
18 locf.organization_id = mp.organization_id AND
19 hou.organization_id=mp.organization_id AND
20 (locf.physical_location_id = locf.inventory_location_id OR
21 locf.physical_location_id IS NULL)
22 union all
23 -- Sub Inventory Level
24 select count(*) cnt
25 FROM mtl_secondary_inventories msi,
26 hr_all_organization_units bg,
27 hr_all_organization_units org,
28 mtl_parameters mp
29 WHERE msi.organization_id = mp.organization_id + 0
30 AND bg.organization_id = org.business_group_id
31 AND org.organization_id = mp.organization_id
32 and msi.last_update_date between p_from_date and p_to_date
33 union all
34 -- Inventory Organization Level
35 select count(*) cnt
36 FROM
37 hr_all_organization_units bg,
38 hr_all_organization_units org,
39 HR_ORGANIZATION_INFORMATION HOI,
40 mtl_parameters mp
41 WHERE bg.organization_id = org.business_group_id
42 AND org.organization_id = mp.organization_id
43 and org.ORGANIZATION_ID = HOI.ORGANIZATION_ID
44 AND ( HOI.ORG_INFORMATION_CONTEXT || '') ='Accounting Information'
45 and mp.last_update_date between p_from_date and p_to_date
46 union all
47 -- Operating Unit Level
48 select count(*) cnt
49 FROM EDW_ORGA_OPER_UNIT_LCV
50 where last_update_date between p_from_date and p_to_date
51 -- Inventory Organization Parent Goup level
52 union all
53 SELECT COUNT(*) cnt
54 FROM SY_ORGN_MST
55 WHERE ORGN_CODE = CO_CODE
56 -- Inventory Organization Parent Goup level
57 union all
58 SELECT COUNT(*) cnt
59 FROM SY_ORGN_MST
60 -- Locator level non location controlled
61 UNION ALL
62 SELECT COUNT(*) cnt
63 FROM IC_LOCT_INV ILI,
64 IC_ITEM_MST IIM,
65 IC_WHSE_MST IWM,
66 MTL_PARAMETERS MP
67 WHERE
68 ILI.ITEM_ID = IIM.ITEM_ID
69 AND ILI.WHSE_CODE = IWM.WHSE_CODE
70 AND MP.ORGANIZATION_ID = IWM.MTL_ORGANIZATION_ID
71 AND IWM.LOCT_CTL * IIM.LOCT_CTL = 0
72 -- Locator level Non Validated
73 UNION ALL
74 SELECT COUNT(*) cnt
75 FROM IC_LOCT_INV ILI,
76 IC_ITEM_MST IIM,
77 IC_WHSE_MST IWM,
78 MTL_PARAMETERS MP
79 WHERE
80 ILI.ITEM_ID = IIM.ITEM_ID
81 AND ILI.WHSE_CODE = IWM.WHSE_CODE
82 AND MP.ORGANIZATION_ID = IWM.MTL_ORGANIZATION_ID
83 AND IWM.LOCT_CTL * IIM.LOCT_CTL >1 );
84
85 BEGIN
86
87 OPEN c_cnt_rows;
88 FETCH c_cnt_rows INTO p_num_rows;
89 CLOSE c_cnt_rows;
90
91 END; -- procedure cnt_rows.
92
93
94 PROCEDURE est_row_len(p_from_date DATE,
95 p_to_date DATE,
96 p_est_row_len OUT NOCOPY NUMBER) IS
97
98 x_LOCATOR_PK NUMBER ;
99 x_STOCK_ROOM_FK NUMBER ;
100 x_Locator_Locator_Name NUMBER ;
101 x_Locator_Description NUMBER ;
102 x_Enabled_Flag NUMBER ;
103 x_Locator_DP NUMBER ;
104 x_Locator_Name NUMBER ;
105 x_Locator_Creation_Date NUMBER ;
106 x_Locator_Last_Update_Date NUMBER ;
107 x_Inventory_Location_ID NUMBER;
108 x_Organization_ID NUMBER;
109 x_Org_Code NUMBER;
110 x_Org_Name NUMBER;
111 x_Instance NUMBER;
112 x_Stock_Room_Name_PK NUMBER;
113 x_Stock_Room_Description NUMBER;
114 x_Stock_Room_DP NUMBER;
115 x_Stock_Room_Creation_Date NUMBER;
116 x_Stock_Room_Last_Update_Date NUMBER;
117 x_Plant_Last_Update_Date NUMBER;
118 x_Plant_Creation_date NUMBER;
119 x_PLANT_DP NUMBER;
120 x_PLANT_NAME_ORG_Name NUMBER;
121 x_OU_FK_OPM_ORG_FK NUMBER;
122 x_total NUMBER := 0 ;
123 x_ORGN_CODE NUMBER;
124 x_ORGN_NAME NUMBER;
125 CURSOR c_1 IS -- Locator Level
126 SELECT
127 -- LOCATOR_PK needs to add instance_code and org_code
128 avg(nvl(vsize(locf.inventory_location_id), 0)),
129 -- INSTANCE_CODE, needs to instance_Code
130 -- STOCK_ROOM_FK needs to add org_code and instance code
131 avg(nvl(vsize(locf.SUBINVENTORY_CODE), 0)),
132 -- Locator_Name needs to add org_code
133 avg(nvl(vsize(locf.CONCATENATED_SEGMENTS), 0)),
134 -- Description
135 avg(nvl(vsize(locf.description), 0)),
136 -- Enabled_Flag
137 avg(nvl(vsize(locf.ENABLED_FLAG), 0)),
138 -- Locator_DP
139 -- length('LOCATOR'),
140 7, -- for some strange reason, the package doesn't compile the length function here
141 -- Name needs to add name
142 avg(nvl(vsize(locf.CONCATENATED_SEGMENTS), 0)),
143 -- Creation_Date
144 avg(nvl(vsize(locf.creation_date), 0)),
145 -- Last_Update_Date
146 avg(nvl(vsize(locf.last_update_date), 0)),
147 -- Inventory_Location_ID
148 avg(nvl(vsize(locf.inventory_location_id), 0)),
149 -- Organization_ID
150 avg(nvl(vsize(locf.organization_id), 0))
151 From
152 mtl_item_locations_kfv locf;
153
154
155 CURSOR c_2 IS -- Organization_Code used in all levels: Locator, Stock_Room, Plant
156 -- and Plant's Creation and Last_Update_Date
157 Select
158 -- Org_Code
159 10*avg(nvl(vsize(mp.organization_code), 0)),
160 -- Plant_Last_Update_Date
161 avg(nvl(vsize(mp.last_update_date), 0)),
162 -- Plant_Creation_date
163 avg(nvl(vsize(mp.creation_date), 0))
164 From
165 mtl_parameters mp;
166
167
168 CURSOR c_3 IS -- Part of Locator Name
169 Select
170 -- Org_Name
171 avg(nvl(vsize(hou.name), 0))
172 From
173 hr_organization_units hou;
174
175
176 CURSOR c_4 IS -- Instance_Code used in all Levels: Locator, Stock_Room, Plant
177 SELECT
178 -- Instance
179 15*avg(nvl(vsize(instance_code), 0))
180 FROM EDW_LOCAL_INSTANCE ;
181
182 CURSOR c_5 IS -- Stock_Room (Sub Inv) Level
183 Select
184 -- STOCK_ROOM_PK, needs to add org_code and instance_Code
185 3*avg(nvl(vsize(msi.secondary_inventory_name), 0)),
186 -- PLANT_FK, needs to add org_code and instance_Code
187 -- INSTANCE_CODE, needs to instance_Code
188 -- STOCK_ROOM, needs to add org_code
189 -- avg(nvl(vsize(msi.secondary_inventory_name), 0)),
190 -- DESCRIPTION
191 avg(nvl(vsize(msi.description), 0)),
192 -- STOCK_ROOM_DP
193 length('SUB_INVENTORY'),
194 -- NAME
195 -- avg(nvl(vsize(msi.secondary_inventory_name), 0)),
196 -- CREATION_DATE
197 avg(nvl(vsize(msi.creation_date), 0)),
198 -- LAST_UPDATE_DATE
199 avg(nvl(vsize(msi.last_update_date), 0))
200 From
201 mtl_secondary_inventories msi;
202
203 CURSOR c_6 IS -- Plant Level (Inv Org)
204 Select
205 -- PLANT_PK, it's org_code and instance_code
206 -- INSTANCE_CODE, needs to instance_Code
207 -- PLANT_DP
208 length('PLANT'),
209 -- NAME and Org_Name
210 3*avg(nvl(vsize(bg.name), 0))
211 From
212 hr_all_organization_units bg;
213
214 CURSOR c_7 IS -- Plant_Level (Inv Org)
215 Select
216 -- OPERATING_UNIT_FK needs to add instance_code
217 -- OPM_ORGANIZATION_FK needs to add instance_code
218 2*avg(nvl(vsize(HOI.ORG_INFORMATION3), 0))
219 From
220 HR_ORGANIZATION_INFORMATION HOI;
221
222 CURSOR c_8 IS
223 SELECT avg(nvl(vsize(ORGN_CODE),0)) ORGN_CODE,
224 avg(nvl(vsize(ORGN_NAME),0)) ORGN_NAME
225 FROM SY_ORGN_MST;
226
227 BEGIN
228
229 --dbms_output.put_line ('******************'||x_total||'******') ;
230 OPEN c_1;
231 FETCH c_1 INTO
232 x_LOCATOR_PK,
233 x_STOCK_ROOM_FK,
234 x_Locator_Locator_Name,
235 x_Locator_Description,
236 x_Enabled_Flag,
237 x_Locator_DP,
238 x_Locator_Name,
239 x_Locator_Creation_Date,
240 x_Locator_Last_Update_Date,
241 x_Inventory_Location_ID,
242 x_Organization_ID;
243 CLOSE c_1;
244
245 x_total := 3 +
246 x_total +
247 ceil(x_LOCATOR_PK + 1) +
248 ceil(x_STOCK_ROOM_FK + 1) +
249 ceil(x_Locator_Locator_Name + 1) +
250 ceil(x_Locator_Description + 1) +
251 ceil(x_Enabled_Flag + 1) +
252 ceil(x_Locator_DP + 1) +
253 ceil(x_Locator_Name + 1) +
254 ceil(x_Locator_Creation_Date + 1) +
255 ceil(x_Locator_Last_Update_Date + 1) +
256 ceil(x_Inventory_Location_ID + 1) +
257 ceil(x_Organization_ID + 1) ;
258
259 --dbms_output.put_line ('******************'||x_total||'******') ;
260 OPEN c_2;
261 FETCH c_2 INTO
262 x_Org_Code,
263 x_Plant_Last_Update_Date,
264 x_Plant_Creation_date;
265 CLOSE c_2;
266
267 x_total := x_total + ceil(x_Org_Code + 1) + ceil(x_Plant_Last_Update_Date + 1) + ceil(x_Plant_Creation_date + 1);
268
269
270 --dbms_output.put_line ('******************'||x_total||'******') ;
271 OPEN c_3;
272 FETCH c_3 INTO
273 x_Org_Name;
274 CLOSE c_3;
275
276 x_total := x_total + ceil(x_Org_Name + 1) ;
277
278
279 --dbms_output.put_line ('******************'||x_total||'******') ;
280 OPEN c_4;
281 FETCH c_4 INTO
282 x_Instance;
283 CLOSE c_4;
284
285 x_total := x_total + ceil(x_Instance + 1) ;
286
287 --dbms_output.put_line ('******************'||x_total||'******') ;
288 OPEN c_5;
289 FETCH c_5 INTO
290 x_Stock_Room_Name_PK,
291 x_Stock_Room_Description,
292 x_Stock_Room_DP,
293 x_Stock_Room_Creation_Date,
294 x_Stock_Room_Last_Update_Date;
295 CLOSE c_5;
296
297 x_total := x_total +
298 ceil(x_Stock_Room_Name_PK + 1) +
299 ceil(x_Stock_Room_Description + 1) +
300 ceil(x_Stock_Room_DP + 1) +
301 ceil(x_Stock_Room_Creation_Date + 1) +
302 ceil(x_Stock_Room_Last_Update_Date + 1);
303
304 --dbms_output.put_line ('******************'||x_total||'******') ;
305 OPEN c_6;
306 FETCH c_6 INTO
307 x_PLANT_DP,
308 x_PLANT_NAME_ORG_Name;
309 Close c_6;
310
311 x_total := x_total + ceil(x_PLANT_DP + 1) + ceil(x_PLANT_NAME_ORG_Name + 1);
312
313 --dbms_output.put_line ('******************'||x_total||'******') ;
314 OPEN c_7;
315 FETCH c_7 INTO
316 x_OU_FK_OPM_ORG_FK;
317 CLOSE c_7;
318
319 x_total := x_total + ceil(x_OU_FK_OPM_ORG_FK + 1);
320
321 OPEN c_8;
322 FETCH c_8 INTO X_ORGN_CODE,X_ORGN_NAME;
323 CLOSE c_8;
324 -- Inventory Parent Group level
325
326 x_total := x_total + 4 * ceil(x_ORGN_CODE+1) + 3 * ceil(x_ORGN_NAME+1) + 35;
327
328 -- Inventory Group level
329 x_total := x_total + 4 * ceil(x_ORGN_CODE+1) + 3 * ceil(x_ORGN_NAME+1) + 30;
330
331 p_est_row_len := x_total ;
332
333 --dbms_output.put_line ('******************'||x_total||'******') ;
334
335
336 END ;
337
338 END EDW_MTL_INVENTORY_LOC_M_SZ; -- procedure est_row_len.