[Home] [Help]
PACKAGE BODY: APPS.OPI_EDW_JOB_DETAIL_FOPM_SZ
Source
1 PACKAGE BODY OPI_EDW_JOB_DETAIL_FOPM_SZ AS
2 /* $Header: OPIPJDZB.pls 120.1 2005/06/07 03:23:07 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 CURSOR c_cnt_rows IS
9 select count(*)
10 FROM
11 (
12 SELECT
13 BH.BATCH_ID,
14 BH.BATCH_NO,
15 BH.batch_status,
16 BH.WIP_WHSE_CODE,
17 BH.PLAN_START_DATE,
18 BH.ACTUAL_START_DATE,
19 BH.EXPCT_CMPLT_DATE,
20 BH.ACTUAL_CMPLT_DATE,
21 BH.PLANT_CODE,
22 BH.FORMULA_ID,
23 RT.ROUTING_NO,
24 to_char(RT.ROUTING_VERS) ROUTING_VERS,
25 BD.ITEM_ID,
26 BD.PLAN_QTY,
27 BD.ACTUAL_QTY,
28 BD.ITEM_UM,
29 BD.COST_ALLOC,
30 BD.LINE_NO,
31 BH.CREATION_DATE,
32 GREATEST(BH.LAST_UPDATE_DATE, BD.LAST_UPDATE_DATE) LAST_UPDATE_DATE
33 FROM PM_BTCH_HDR BH,
34 PM_MATL_DTL BD,
35 FM_ROUT_HDR RT
36 WHERE BH.BATCH_ID = BD.BATCH_ID
37 AND BH.ROUTING_ID=RT.ROUTING_ID(+)
38 AND BH.BATCH_STATUS in (-1,0,1,2,3,4)
39 AND BD.LINE_TYPE=1
40 ) B,
41 SY_ORGN_MST OM,
42 IC_ITEM_MST IM,
43 IC_PLNT_INV PI,
44 GL_SETS_OF_BOOKS SOB,
45 GL_PLCY_MST PM,
46 MTL_SYSTEM_ITEMS ITEM_FK_V,
47 IC_WHSE_MST IW,
48 EDW_LOCAL_INSTANCE inst,
49 OPI_PMI_UOMS_MST UOM,
50 GEM_LOOKUPS LKUP
51 WHERE
52 B.PLANT_CODE = OM.ORGN_CODE
53 AND B.PLANT_CODE = PI.ORGN_CODE(+)
54 AND B.item_id = PI.item_id(+)
55 AND OM.co_CODE = PM.co_code
56 AND PM.set_of_books_name =SOB.name
57 AND B.ITEM_ID = IM.ITEM_ID
58 AND ITEM_FK_V.SEGMENT1= IM.ITEM_NO
59 AND ITEM_FK_V.ORGANIZATION_ID = IW.MTL_ORGANIZATION_ID
60 AND IW.WHSE_CODE = B.WIP_WHSE_CODE
61 AND UOM.UM_CODE = IM.ITEM_UM
62 AND LKUP.LOOKUP_TYPE='BATCH_STATUS'
63 AND LKUP.LOOKUP_CODE=B.BATCH_STATUS
64 AND B.LAST_UPDATE_DATE between p_from_date and p_to_date;
65 BEGIN
66
67 OPEN c_cnt_rows;
68 FETCH c_cnt_rows INTO p_num_rows;
69 CLOSE c_cnt_rows;
70
71 END CNT_ROWS;
72
73
74 PROCEDURE EST_ROW_LEN(p_from_date DATE,
75 p_to_date DATE,
76 p_avg_row_len OUT NOCOPY NUMBER) IS
77 x_total number := 0;
78 x_constant number := 6;
79 X_DATE number :=7;
80
81 X_JOB_DETAIL_PK NUMBER;
82 X_LOCATOR_FK NUMBER;
83 X_ITEM_FK NUMBER;
84 X_PRD_LINE_FK NUMBER;
85 X_TRX_DATE_FK NUMBER;
86 X_SOB_CURRENCY_FK NUMBER;
87 X_UOM_FK NUMBER;
88 X_INSTANCE_FK NUMBER;
89 X_STS_LOOKUP_FK NUMBER;
90 X_ACT_JOB_TIME NUMBER;
91 X_ACT_OUT_QTY NUMBER;
92 X_JOB_NO NUMBER;
93 X_JOB_STATUS NUMBER;
94 X_MFG_MODE NUMBER;
95 X_PLN_JOB_TIME NUMBER;
96 X_PLN_OUT_QTY NUMBER;
97 X_ROUTING NUMBER;
98 X_ROUTING_REVISION NUMBER;
99 X_STD_QTY NUMBER;
100 X_STD_TIME NUMBER;
101 X_STND_HRS_EARNED NUMBER;
102
103
104 CURSOR JOB_DTL IS
105 SELECT
106 avg(nvl(vsize(BH.Plant_code||'-'||BH.Batch_id||
107 '-'||BD.item_id||'-'||'OPM'),0)) JOB_DETAIL_PK,
108 avg(nvl(vsize(BH.ACTUAL_CMPLT_DATE-BH.ACTUAL_START_DATE),0)) ACT_JOB_TIME,
109 avg(nvl(vsize(BD.ACTUAL_QTY),0)),
110 avg(nvl(vsize(BH.BATCH_NO),0)),
111 avg(nvl(vsize(BH.BATCH_STATUS),0)),
112 avg(nvl(vsize('PROCESSMFG'),0)),
113 avg(nvl(vsize(BH.EXPCT_CMPLT_DATE-BH.PLAN_START_DATE),0)),
114 avg(nvl(vsize(BD.PLAN_QTY),0)),
115 avg(nvl(vsize(RT.ROUTING_NO),0)),
116 avg(nvl(vsize(RT.ROUTING_VERS),0))
117 FROM
118 PM_BTCH_HDR BH,
119 PM_MATL_DTL BD,
120 FM_ROUT_HDR RT
121 WHERE BH.BATCH_ID = BD.BATCH_ID
122 AND BH.ROUTING_ID=RT.ROUTING_ID(+)
123 AND BH.BATCH_STATUS in (-1,0,1,2,3,4)
124 AND BD.LINE_TYPE=1;
125
126
127 CURSOR ITEM_PK IS
128 /* ITEM_FK */
129 SELECT
130 avg(nvl(vsize(EDW_ITEMS_PKG.ITEM_ORG_FK(ITEM_FK_V.INVENTORY_ITEM_ID,
131 IW.MTL_ORGANIZATION_ID,NULL,TO_NUMBER(NULL),NULL)), 0))
132 FROM MTL_SYSTEM_ITEMS ITEM_FK_V,
133 IC_WHSE_MST IW,
134 IC_ITEM_MST IM
135 WHERE ITEM_FK_V.SEGMENT1= IM.ITEM_NO
136 AND ITEM_FK_V.ORGANIZATION_ID = IW.MTL_ORGANIZATION_ID;
137
138 CURSOR INST_PK IS
139 SELECT
140 avg(nvl(vsize(instance_code), 0))
141 FROM EDW_LOCAL_INSTANCE ;
142
143
144 CURSOR CURR_PK is
145 SELECT avg(nvl(vsize(BASE_CURRENCY_CODE), 0))
146 FROM gl_plcy_mst;
147
148
149 CURSOR UOM_PK is
150 SELECT avg(nvl(vsize(UOM_CODE), 0))
151 FROM OPI_PMI_UOMS_MST;
152 CURSOR LOC_PK is
153 SELECT avg(nvl(vsize(ORGN_CODE), 0))
154 FROM SY_ORGN_MST;
155
156 CURSOR TRX_DATE_PK is
157 SELECT avg(nvl(vsize(substr(edw_time_pkg.cal_day_fk
158 (BH.ACTUAL_CMPLT_DATE,SOB.SET_OF_BOOKS_ID),1,120)),0))
159 FROM
160 PM_BTCH_HDR BH,
161 SY_ORGN_MST OM,
162 GL_PLCY_MST PM,
163 GL_SETS_OF_BOOKS SOB
164 WHERE
165 BH.PLANT_CODE = OM.ORGN_CODE
166 AND OM.CO_CODE = PM.co_code
167 AND PM.SET_OF_BOOKS_NAME=SOB.name;
168
169
170
171 BEGIN
172
173 x_total:=5*ceil(x_date+1);
174
175 OPEN JOB_DTL;
176 FETCH JOB_DTL INTO
177 X_JOB_DETAIL_PK,
178 X_ACT_JOB_TIME,
179 X_ACT_OUT_QTY,
180 X_JOB_NO,
181 X_JOB_STATUS,
182 X_MFG_MODE,
183 X_PLN_JOB_TIME,
184 X_PLN_OUT_QTY,
185 X_ROUTING,
186 X_ROUTING_REVISION;
187 CLOSE JOB_DTL;
188
189 x_total := x_total +
190 ceil(X_JOB_DETAIL_PK + 1) +
191 ceil(X_ACT_JOB_TIME + 1) +
192 ceil(X_ACT_OUT_QTY + 1) +
193 ceil(X_JOB_NO + 1) +
194 ceil(X_JOB_STATUS + 1) +
195 ceil(X_MFG_MODE + 1) +
196 ceil(X_PLN_JOB_TIME + 1) +
197 ceil(X_PLN_OUT_QTY + 1) +
198 ceil(X_ROUTING + 1) +
199 ceil(X_ROUTING_REVISION + 1) +
200 2*ceil(X_PLN_OUT_QTY + 1) +
201 8*ceil(x_ACT_OUT_QTY+2);
202
203
204 OPEN ITEM_PK;
205 FETCH ITEM_PK INTO x_ITEM_FK ;
206 CLOSE ITEM_PK;
207 x_total := x_total + ceil(x_ITEM_FK + 1) ;
208
209 OPEN TRX_DATE_PK;
210 FETCH TRX_DATE_PK INTO x_TRX_DATE_FK;
211 CLOSE TRX_DATE_PK;
212 x_total := x_total + ceil(x_TRX_DATE_FK + 1);
213
214 OPEN INST_PK;
215 FETCH INST_PK INTO x_INSTANCE_FK;
216 CLOSE INST_PK;
217 x_total := x_total + ceil(x_INSTANCE_FK + 1);
218
219 OPEN CURR_PK ;
220 FETCH CURR_PK INTO x_SOB_CURRENCY_FK;
221 CLOSE CURR_PK ;
222 x_total := x_total + ceil(x_SOB_CURRENCY_FK + 1);
223
224 OPEN UOM_PK ;
225 FETCH UOM_PK INTO x_UOM_FK;
226 CLOSE UOM_PK ;
227 x_total := x_total + ceil(x_UOM_FK + 1);
228
229
230 OPEN LOC_PK ;
231 FETCH LOC_PK INTO x_LOCATOR_FK;
232 CLOSE LOC_PK ;
233 x_total := x_total + ceil(x_LOCATOR_FK + 1);
234
235 -- Miscellaneous
236 x_total := x_total + 5 * ceil(x_INSTANCE_FK + 1);
237
238 p_avg_row_len := x_total;
239
240 END; -- procedure est_row_len.
241
242 END OPI_EDW_JOB_DETAIL_FOPM_SZ;