eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.OPI_EDW_JOB_DETAIL_F_SZ

Source


1 PACKAGE BODY OPI_EDW_JOB_DETAIL_F_SZ AS
2 /* $Header: OPIOJDZB.pls 120.2 2006/02/23 22:01:46 sberi noship $*/
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 	Select count(*) cnt
12 	FROM
13 	  WIP_ENTITIES EN, WIP_DISCRETE_JOBS DI
14 	WHERE
15 	  DI.STATUS_TYPE IN (4,5,7,12) AND '_SEC:di.organization_id' IS NOT NULL AND
16 	  DI.WIP_ENTITY_ID = EN.WIP_ENTITY_ID AND DI.ORGANIZATION_ID = EN.ORGANIZATION_ID
17           and en.last_update_date between p_from_date and p_to_date
18         union
19 	Select count(*) cnt
20 	FROM
21 	  WIP_ENTITIES EN, WIP_REPETITIVE_SCHEDULES RE
22 	WHERE
23 	  RE.STATUS_TYPE IN (4,5,7,12) AND '_SEC:re.organization_id' IS NOT NULL AND
24 	  RE.WIP_ENTITY_ID = EN.WIP_ENTITY_ID AND RE.ORGANIZATION_ID = EN.ORGANIZATION_ID
25           and en.last_update_date between p_from_date and p_to_date
26 	union
27 	Select count(*) cnt
28 	FROM
29 	  WIP_ENTITIES EN, WIP_FLOW_SCHEDULES FL
30 	WHERE
31 	 FL.STATUS = 2  AND '_SEC:fl.organization_id' IS NOT NULL AND
32 	 FL.WIP_ENTITY_ID = EN.WIP_ENTITY_ID AND FL.ORGANIZATION_ID = EN.ORGANIZATION_ID
33          and en.last_update_date between p_from_date and p_to_date
34        );
35 
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,
47                    p_to_date DATE,
48                    p_est_row_len OUT NOCOPY NUMBER) IS
49 
50 	 x_JOB_NO NUMBER ;
51 	 x_JOB_ID_EN NUMBER ;
52 	 x_ORG_ID NUMBER ;
53 	 x_ITEM_ORG NUMBER ;
54 	 x_ENTITY_TYPE NUMBER ;
55 	 x_CREATION_DATE NUMBER ;
56 	 x_LAST_UPDATE_DATE NUMBER ;
57          x_ROUTING NUMBER ;
58 
59 	 x_ACT_OUT_QTY_DI NUMBER;
60 	 x_PLN_CMPL_DATE_DI NUMBER;
61 	 x_PLN_OUT_QTY_DI  NUMBER;
62 	 x_ACT_STRT_DATE_DI NUMBER;
63 	 x_PLN_STRT_DATE_DI NUMBER;
64 	 x_ACT_CMPL_DATE_DI NUMBER;
65 	 x_ACT_CNCL_DATE_DI NUMBER;
66 	 x_PRD_LINE_FK_DI NUMBER;
67 	 x_ROUTING_REVISION_DI NUMBER;
68 
69 	 x_JOB_ID_RE NUMBER;
70 	 x_ACT_OUT_QTY_RE NUMBER;
71 	 x_PLN_CMPL_DATE_RE NUMBER;
72 	 x_PLN_OUT_QTY_RE  NUMBER;
73 	 x_ACT_STRT_DATE_RE NUMBER;
74 	 x_PLN_STRT_DATE_RE NUMBER;
75 	 x_ACT_CMPL_DATE_RE NUMBER;
76 	 x_ACT_CNCL_DATE_RE NUMBER;
77 	 x_PRD_LINE_FK_RE NUMBER;
78 	 x_ROUTING_REVISION_RE NUMBER;
79 
80 
81 	 x_ACT_OUT_QTY_FL NUMBER;
82 	 x_PLN_CMPL_DATE_FL NUMBER;
83 	 x_PLN_OUT_QTY_FL  NUMBER;
84 	 x_ACT_STRT_DATE_FL NUMBER;
85 	 x_PLN_STRT_DATE_FL NUMBER;
86 	 x_ACT_CMPL_DATE_FL NUMBER;
87 	 x_PRD_LINE_FK_FL NUMBER;
88 	 x_ROUTING_REVISION_FL NUMBER;
89 
90 	 x_JOB_STATUS_MFG_MODE NUMBER;
91 
92  	 x_ACT_INP_VAL NUMBER;
93 
94 	 x_ACT_PLN_VAL NUMBER;
95 
96 	 x_ACT_PLN_JOB_TIME_DI NUMBER;
97  	 x_ACT_PLN_JOB_TIME_RE NUMBER;
98 	 x_ACT_PLN_JOB_TIME_FL NUMBER;
99 
100 	 x_INST NUMBER;
101          x_MP_ORG NUMBER;
102 
103 	 x_di_rows NUMBER;
104          x_re_rows NUMBER;
105          x_fl_rows NUMBER;
106 
107 	 x_tot_jobs_rows NUMBER;
108 
109 	 x_FULL_LEAD_TIME NUMBER;
110 	 x_trx_date NUMBER;
111 	 x_currency NUMBER;
112 
113  	 x_total NUMBER := 0 ;
114 
115 CURSOR c_1  IS
116 	SELECT
117 	 avg(nvl(vsize(EN.WIP_ENTITY_NAME), 0)),  --    JOB_NO
118 	 avg(nvl(vsize(EN.WIP_ENTITY_ID || '-'), 0)), -- JOB_ID
119 	 3*avg(nvl(vsize(EN.ORGANIZATION_ID), 0)),  -- ORG_ID, Used three times in the stg table
120 	 2*avg(nvl(vsize(EN.PRIMARY_ITEM_ID), 0)),  -- ITEM_ORG, Used twice in the stg table
121 	 avg(nvl(vsize(EN.ENTITY_TYPE), 0)),      -- ENTITY_TYPE
122 	 avg(nvl(vsize(EN.CREATION_DATE), 0)),    -- CREATION_DATE
123 	 avg(nvl(vsize(EN.LAST_UPDATE_DATE), 0)) , -- LAST_UPDATE_DATE
124          avg(nvl(vsize(TO_CHAR(EN.PRIMARY_ITEM_ID)), 0)) -- ROUTING
125 	FROM WIP_ENTITIES EN;
126 
127 CURSOR c_2  IS
128 	SELECT
129 	 avg(nvl(vsize(DI.QUANTITY_COMPLETED), 0)),  -- ACT_OUT_QTY,
130 	 avg(nvl(vsize(DI.SCHEDULED_COMPLETION_DATE), 0)),   -- PLN_CMPL_DATE,
131 	 avg(nvl(vsize(DI.START_QUANTITY), 0)),   -- PLN_OUT_QTY ,
132 	 avg(nvl(vsize(DI.SCHEDULED_START_DATE), 0)),   -- ACT_STRT_DATE,
133 	 avg(nvl(vsize(DI.SCHEDULED_START_DATE), 0)),   -- PLN_STRT_DATE,
134 	 avg(nvl(vsize(DI.date_closed), 0)),   -- ACT_CMPL_DATE,
135 	 avg(nvl(vsize(DECODE(DI.STATUS_TYPE,7,DI.DATE_COMPLETED,NULL)), 0)),   -- ACT_CNCL_DATE,
136 	 avg(nvl(vsize(DI.LINE_ID), 0)),   -- PRD_LINE_FK,
137 	 avg(nvl(vsize(DI.ROUTING_REVISION), 0))   -- ROUTING_REVISION,
138 	FROM WIP_DISCRETE_JOBS DI
139 	WHERE
140 	  DI.STATUS_TYPE IN (4,5,7,12) AND '_SEC:di.organization_id' IS NOT NULL;
141 
142 CURSOR c_3  IS
143 	SELECT
144 	 avg(nvl(vsize(RE.QUANTITY_COMPLETED), 0)),  -- ACT_OUT_QTY,
145 	 avg(nvl(vsize(RE.LAST_UNIT_COMPLETION_DATE), 0)),  -- PLN_CMPL_DATE,
146 	 avg(nvl(vsize('-' || RE.REPETITIVE_SCHEDULE_ID), 0)),   -- JOB_ID,
147 	 avg(nvl(vsize(RE.DAILY_PRODUCTION_RATE * RE.PROCESSING_WORK_DAYS), 0)),  -- PLN_OUT_QTY,
148 	 avg(nvl(vsize(RE.FIRST_UNIT_START_DATE), 0)),  -- ACT_STRT_DATE,
149 	 avg(nvl(vsize(RE.FIRST_UNIT_START_DATE), 0)),    -- PLN_STRT_DATE,
150 	 avg(nvl(vsize(NVL(RE.DATE_CLOSED,RE.last_unit_completion_date)), 0)),  -- ACT_CMPL_DATE,
151 	 avg(nvl(vsize(DECODE(RE.STATUS_TYPE,7,RE.DATE_CLOSED,NULL)), 0)),  -- ACT_CNCL_DATE,
152 	 avg(nvl(vsize(RE.LINE_ID), 0)),  -- PRD_LINE_FK,
153 	 avg(nvl(vsize(RE.ROUTING_REVISION), 0)) -- ROUTING_REVISION
154 	FROM
155 	  WIP_REPETITIVE_SCHEDULES RE
156 	WHERE
157 	  RE.STATUS_TYPE IN (4,5,7,12) AND '_SEC:re.organization_id' IS NOT NULL;
158 
159 CURSOR c_4  IS
160 	SELECT
161 	 avg(nvl(vsize(FL.QUANTITY_COMPLETED), 0)),  -- ACT_OUT_QTY,
162 	 avg(nvl(vsize(FL.SCHEDULED_COMPLETION_DATE), 0)),  -- PLN_CMPL_DATE,
163 	 avg(nvl(vsize(FL.PLANNED_QUANTITY), 0)),  -- PLN_OUT_QTY ,
164 	 avg(nvl(vsize(FL.SCHEDULED_START_DATE), 0)), -- ACT_STRT_DATE,
165 	 avg(nvl(vsize(FL.SCHEDULED_START_DATE), 0)), -- PLN_STRT_DATE,
166 	 avg(nvl(vsize(NVL(FL.DATE_CLOSED,FL.scheduled_completion_date)), 0)), -- ACT_CMPL_DATE,
167 	 avg(nvl(vsize(FL.LINE_ID), 0)), -- PRD_LINE_FK,
168 	 avg(nvl(vsize(FL.ROUTING_REVISION), 0)) -- ROUTING_REVISION,
169 	FROM
170 	  WIP_FLOW_SCHEDULES FL
171 	WHERE
172 	 FL.STATUS = 2  AND '_SEC:fl.organization_id' IS NOT NULL;
173 
174 CURSOR c_5  IS
175 	SELECT
176 	 avg(nvl(vsize(ML1.MEANING), 0)) -- JOB_STATUS_MFG_MODE
177 	FROM MFG_LOOKUPS ML1
178 	WHERE
179 	  ML1.LOOKUP_TYPE = 'WIP_JOB_STATUS' OR ML1.LOOKUP_TYPE = 'WIP_ENTITY' ;
180 
181 CURSOR c_6  IS
182 	 SELECT
183 	  avg(nvl(vsize(NVL(WPB.TL_RESOURCE_IN,0) + NVL(WPB.TL_OVERHEAD_IN,0) + NVL(WPB.TL_OUTSIDE_PROCESSING_IN,0) +
184 	      NVL(WPB.PL_MATERIAL_IN,0) + NVL(WPB.PL_MATERIAL_OVERHEAD_IN,0) + NVL(WPB.PL_RESOURCE_IN,0) + NVL(WPB.PL_OVERHEAD_IN,0) +
185 	      NVL(WPB.PL_OUTSIDE_PROCESSING_IN,0) + NVL(WPB.TL_SCRAP_IN,0)), 0))  -- ACT_INP_VAL
186 	 FROM WIP_PERIOD_BALANCES WPB;
187 
188 CURSOR c_7  IS
189 	SELECT
190 	  -- ACT_MTL_INP_VAL, PLN_MTL_INP_VAl, ACT_BPR_VAL, PLN_BPR_VAl, ACT_OUT_VAL, AVG_ACT_UNIT_CMPL_CST (used twice: STD_VAL_B, PLN_OUT_VAL_B), ACT_SCR_VAL
191 	  avg(nvl(vsize(MMT.PRIMARY_QUANTITY), 0))
192 	FROM
193 	  MTL_MATERIAL_TRANSACTIONS MMT;
194 
195 CURSOR c_8  IS
196 	SELECT
197 	 avg(nvl(vsize((NVL(DI.DATE_COMPLETED,DI.date_closed) - DI.SCHEDULED_START_DATE)), 0))   -- ACT_JOB_TIME AND PLN_JOB_TIME
198 	FROM
199 	  WIP_DISCRETE_JOBS DI;
200 
201 CURSOR c_9  IS
202 	SELECT
203 	 avg(nvl(vsize((NVL(RE.DATE_CLOSED,RE.last_unit_completion_date) - RE.FIRST_UNIT_START_DATE)), 0))   -- ACT_JOB_TIME AND PLN_JOB_TIME
204 	FROM
205 	  WIP_REPETITIVE_SCHEDULES RE;
206 
207 CURSOR c_10  IS
208 	SELECT
209 	  avg(nvl(vsize((NVL(FL.DATE_CLOSED, FL.scheduled_completion_date)  - FL.SCHEDULED_START_DATE)), 0))   -- ACT_JOB_TIME AND PLN_JOB_TIME
210 	 FROM
211 	   WIP_FLOW_SCHEDULES FL;
212 
213 CURSOR c_11 IS
214 	SELECT
215    	   avg(nvl(vsize(instance_code), 0))
216 	FROM	EDW_LOCAL_INSTANCE ;
217 
218 CURSOR c_12 IS
219 	SELECT
220 	   avg(nvl(vsize(mp.organization_code), 0))
221 	FROM mtl_parameters mp ;
222 
223 CURSOR c_13 IS
224         Select count(*) cnt
225 	FROM
226 	  WIP_ENTITIES EN, WIP_DISCRETE_JOBS DI
227 	WHERE
228 	  DI.STATUS_TYPE IN (4,5,7,12) AND '_SEC:di.organization_id' IS NOT NULL AND
229 	  DI.WIP_ENTITY_ID = EN.WIP_ENTITY_ID AND DI.ORGANIZATION_ID = EN.ORGANIZATION_ID	;
230 
231 CURSOR c_14 IS
232 	Select count(*) cnt
233 	FROM
234 	  WIP_ENTITIES EN, WIP_REPETITIVE_SCHEDULES RE
235 	WHERE
236 	  RE.STATUS_TYPE IN (4,5,7,12) AND '_SEC:re.organization_id' IS NOT NULL AND
237 	  RE.WIP_ENTITY_ID = EN.WIP_ENTITY_ID AND RE.ORGANIZATION_ID = EN.ORGANIZATION_ID ;
238 
239 CURSOR c_15 IS
240 	Select count(*) cnt
241 	FROM
242 	  WIP_ENTITIES EN, WIP_FLOW_SCHEDULES FL
243 	WHERE
244 	 FL.STATUS = 2  AND '_SEC:fl.organization_id' IS NOT NULL AND
245 	 FL.WIP_ENTITY_ID = EN.WIP_ENTITY_ID AND FL.ORGANIZATION_ID = EN.ORGANIZATION_ID;
246 
247 CURSOR c_16 IS
248 	SELECT
249 	   avg(nvl(vsize(MSI.FULL_LEAD_TIME), 0))
250         FROM
251 	   MTL_SYSTEM_ITEMS MSI;
252 
253 CURSOR c_17 IS
254         SELECT
255                 avg(nvl(vsize(transaction_date), 0))
256         FROM    WIP_MOVE_TRANSACTIONS;
257 
258 
259 CURSOR c_18 is
260         SELECT  avg(nvl(vsize(gsob.currency_code), 0))
261         FROM    hr_all_organization_units hou,
262                 hr_organization_information hoi,
263                 gl_sets_of_books gsob
264         WHERE   hou.organization_id  = hoi.organization_id
265           AND ( hoi.org_information_context || '') ='Accounting Information'
266           AND hoi.org_information1    = to_char(gsob.set_of_books_id)  ;
267 
268 BEGIN
269 
270   OPEN c_13;
271        FETCH c_13 INTO x_di_rows;
272   CLOSE c_13;
273 
274   OPEN c_14;
275        FETCH c_14 INTO x_re_rows;
276   CLOSE c_14;
277 
278   OPEN c_15;
279        FETCH c_15 INTO x_fl_rows;
280   CLOSE c_15;
281 
282   x_tot_jobs_rows := x_di_rows + x_re_rows + x_fl_rows;
283 
284   -- dbms_output.put_line ('******************'||x_total||'******') ;
285   OPEN c_1;
286        FETCH c_1 INTO
287 	 x_JOB_NO ,
288 	 x_JOB_ID_EN ,
289 	 x_ORG_ID ,
290 	 x_ITEM_ORG ,
291 	 x_ENTITY_TYPE ,
292 	 x_CREATION_DATE ,
293 	 x_LAST_UPDATE_DATE ,
294          x_ROUTING;
295   CLOSE c_1;
296 
297   x_total := 3 +
298 	    x_total +
299 	ceil(x_JOB_NO + 1) +
300 	ceil(x_JOB_ID_EN + 1) +
301 	2*ceil(x_ORG_ID + 1) +  -- ORG_ID is used twice in the job_detail stg record, once in Fact
302 	ceil(x_ITEM_ORG + 1) +
303 	ceil(x_ENTITY_TYPE + 1) +
304 	ceil(x_CREATION_DATE + 1) +
305 	ceil(x_LAST_UPDATE_DATE + 1) +
306 	ceil(x_LAST_UPDATE_DATE + 1) +
307 	ceil(x_ROUTING + 1)   ;
308 
309   -- dbms_output.put_line ('******************'||x_total||'******') ;
310   OPEN c_2;
311        FETCH c_2 INTO
312 	 x_ACT_OUT_QTY_DI,
313 	 x_PLN_CMPL_DATE_DI,
314 	 x_PLN_OUT_QTY_DI ,
315 	 x_ACT_STRT_DATE_DI,
316 	 x_PLN_STRT_DATE_DI,
317 	 x_ACT_CMPL_DATE_DI,
318 	 x_ACT_CNCL_DATE_DI,
319 	 x_PRD_LINE_FK_DI,
320 	 x_ROUTING_REVISION_DI;
321   CLOSE c_2;
322 
323   x_total := x_total +
324 	(ceil(x_ACT_OUT_QTY_DI + 1) +
325 	ceil(x_PLN_CMPL_DATE_DI + 1) +
326 	ceil(x_PLN_OUT_QTY_DI + 1) +
327 	ceil(x_ACT_STRT_DATE_DI + 1) +
328 	ceil(x_PLN_STRT_DATE_DI + 1) +
329 	ceil(x_ACT_CMPL_DATE_DI + 1) +
330 	ceil(x_ACT_CNCL_DATE_DI + 1) +
331 	ceil(x_PRD_LINE_FK_DI + 1) +
332 	ceil(x_ROUTING_REVISION_DI + 1))*(x_di_rows/x_tot_jobs_rows )   ;
333 
334   -- dbms_output.put_line ('******************'||x_total||'******') ;
335   OPEN c_3;
336        FETCH c_3 INTO
337 	 x_JOB_ID_RE,
338 	 x_ACT_OUT_QTY_RE,
339 	 x_PLN_CMPL_DATE_RE,
340 	 x_PLN_OUT_QTY_RE,
341 	 x_ACT_STRT_DATE_RE,
342 	 x_PLN_STRT_DATE_RE,
343 	 x_ACT_CMPL_DATE_RE,
344 	 x_ACT_CNCL_DATE_RE,
345 	 x_PRD_LINE_FK_RE,
346 	 x_ROUTING_REVISION_RE ;
347   CLOSE c_3;
348 
349   x_total := x_total +
350 	(ceil(x_JOB_ID_RE + 1) +
351         ceil(x_ACT_OUT_QTY_RE + 1) +
352 	ceil(x_PLN_CMPL_DATE_RE + 1) +
353 	ceil(x_PLN_OUT_QTY_RE + 1) +
354 	ceil(x_ACT_STRT_DATE_RE + 1) +
355 	ceil(x_PLN_STRT_DATE_RE + 1) +
356 	ceil(x_ACT_CMPL_DATE_RE + 1) +
357 	ceil(x_ACT_CNCL_DATE_RE + 1) +
358 	ceil(x_PRD_LINE_FK_RE + 1) +
359 	ceil(x_ROUTING_REVISION_RE + 1))*(x_re_rows/x_tot_jobs_rows )   ;
360 
361   -- dbms_output.put_line ('******************'||x_total||'******') ;
362   OPEN c_4;
363        FETCH c_4 INTO
364 	 x_ACT_OUT_QTY_FL,
365 	 x_PLN_CMPL_DATE_FL,
366 	 x_PLN_OUT_QTY_FL,
367 	 x_ACT_STRT_DATE_FL,
368 	 x_PLN_STRT_DATE_FL,
369 	 x_ACT_CMPL_DATE_FL,
370 	 x_PRD_LINE_FK_FL,
371 	 x_ROUTING_REVISION_FL;
372   CLOSE c_4;
373 
374   x_total := x_total +
375 	(ceil(x_ACT_OUT_QTY_FL + 1) +
376 	ceil(x_PLN_CMPL_DATE_FL + 1) +
377 	ceil(x_PLN_OUT_QTY_FL + 1) +
378 	ceil(x_ACT_STRT_DATE_FL + 1) +
379 	ceil(x_PLN_STRT_DATE_FL + 1) +
380 	ceil(x_ACT_CMPL_DATE_FL + 1) +
381 	ceil(x_PRD_LINE_FK_FL + 1) +
382 	ceil(x_ROUTING_REVISION_FL + 1))*(x_fl_rows/x_tot_jobs_rows )   ;
383 
384   -- dbms_output.put_line ('******************'||x_total||'******') ;
385   OPEN c_5;
386        FETCH c_5 INTO
387 	 x_JOB_STATUS_MFG_MODE ;
388   CLOSE c_5;
389 
390   x_total := x_total + 2*ceil(x_JOB_STATUS_MFG_MODE  + 1);  -- Used twice in the job_detail record
391 
392   -- dbms_output.put_line ('******************'||x_total||'******') ;
393   OPEN c_6;
394        FETCH c_6 INTO
395 	 x_ACT_INP_VAL ;
396   CLOSE c_6;
397 
398   x_total := x_total + 2*ceil(x_ACT_INP_VAL  + 1);  -- VAL_B and VAL_G
399 
400   -- dbms_output.put_line ('******************'||x_total||'******') ;
401 
402   OPEN c_7;
403        FETCH c_7 INTO
404 	 x_ACT_PLN_VAL ;
405   CLOSE c_7;
406 
407   x_total := x_total + 16*ceil(x_ACT_PLN_VAL   + 1);  -- There are 8 different values that come from this column, that are also converted to warehouse currency
408 
409   -- dbms_output.put_line ('******************'||x_total||'******') ;
410 
411   OPEN c_8;
412        FETCH c_8 INTO
413 	x_ACT_PLN_JOB_TIME_DI;
414   CLOSE c_8;
415 
416   x_total := x_total + ceil(x_ACT_PLN_JOB_TIME_DI  + 1)*(x_di_rows/x_tot_jobs_rows ) ;
417 
418   -- dbms_output.put_line ('******************'||to_char(x_total)||'******') ;
419   -- dbms_output.put_line ('*****just after this *************'||x_total||'******') ;
420   OPEN c_9;
421        FETCH c_9 INTO
422 	x_ACT_PLN_JOB_TIME_RE;
423   CLOSE c_9;
424 
425   -- dbms_output.put_line ('******not here ************'||x_total||'******') ;
426   x_total := x_total + ceil(x_ACT_PLN_JOB_TIME_RE  + 1)*(x_re_rows/x_tot_jobs_rows ) ;
427 
428   -- dbms_output.put_line ('******************'||x_total||'******') ;
429   OPEN c_10;
430        FETCH c_10 INTO
431 	x_ACT_PLN_JOB_TIME_FL;
432   CLOSE c_10;
433 
434   x_total := x_total + ceil(x_ACT_PLN_JOB_TIME_FL  + 1)*(x_fl_rows/x_tot_jobs_rows ) ;
435 
436   -- dbms_output.put_line ('******************'||x_total||'******') ;
437   OPEN c_11;
438        FETCH c_11 INTO
439 	 x_INST;
440   CLOSE c_11;
441 
442   x_total := x_total + 4*ceil(x_INST  + 1);  -- Instance is mentioned 4 times in the Job Detail stg Row but only once in Fact
443 
444   -- dbms_output.put_line ('******************'||x_total||'******') ;
445 --  This is used only in stg table
446   OPEN c_12;
447        FETCH c_12 INTO x_MP_ORG;
448   CLOSE c_12;
449 
450   x_total := x_total + ceil(x_MP_ORG + 1);
451 
455   CLOSE c_16;
452   -- dbms_output.put_line ('******************'||x_total||'******') ;
453   OPEN c_16;
454        FETCH c_16 INTO x_FULL_LEAD_TIME;
456 
457   x_total := x_total + ceil(x_FULL_LEAD_TIME + 1);
458   -- dbms_output.put_line ('******************'||x_total||'******') ;
459 
460   OPEN c_17;
461        FETCH c_17 INTO x_trx_date;
462   CLOSE c_17;
463 
464   x_total := x_total + ceil(x_trx_date + 1);
465   -- dbms_output.put_line ('******************'||x_total||'******') ;
466   OPEN c_18;
467        FETCH c_18 INTO x_currency;
468   CLOSE c_18;
469 
473 
470   x_total := x_total + ceil(x_currency + 1);
471   -- dbms_output.put_line ('******************'||x_total||'******') ;
472 
474   -- * ITEM_FK, * BASE_UOM_FK, * TRX_DATE_FK, * SOB_CURRENCY_FK, * PRD_LINE_FK, * LOCATOR_FK, * INSTANCE_FK: 35 aprox (5 per fk_key)
475 
476   p_est_row_len := x_total + 3 ; -- (UOM_FK)
477 
478 END ;
479 
480 END OPI_EDW_JOB_DETAIL_F_SZ;  -- procedure est_row_len.