[Home] [Help]
PACKAGE BODY: APPS.OPI_EDW_OPM_PRD_PKG
Source
1 PACKAGE BODY OPI_EDW_OPM_PRD_PKG AS
2 /* $Header: OPIEPRDB.pls 115.3 2002/05/07 13:29:02 pkm ship $ */
3
4 FUNCTION FIND_PROD_GRADE(TRANS_ID_VI IN INTEGER,ITEM_ID_VI IN INTEGER,LOT_ID_VI IN INTEGER)
5 RETURN VARCHAR2
6 IS
7 TRANS_ID_V ic_TRAN_CMP.TRANS_id%TYPE := NULL;
8 QC_GRADE_V ic_TRAN_CMP.QC_GRADE%TYPE := NULL;
9
10 CURSOR GRADE_CHANGE_TRAN_CUR(
11 ITEM_ID_V ic_item_mst.item_id%TYPE,
12 LOT_ID_V ic_lots_mst.lot_id%TYPE )
13 IS
14 SELECT nvl(max(trans_id),0) from ic_tran_cmp_vw1
15 where item_id = item_id_v
16 and lot_id = lot_id_v
17 and doc_type = 'GRDI'
18 and reason_code = fnd_profile.value('PMI$REASON_CODE');
19 begin
20 if (item_id_vi = PKG_VAR_ITEM_ID_V AND
21 LOT_ID_VI = PKG_VAR_LOT_ID_V) then
22 return PKG_VAR_QC_GRADE_V;
23 end if;
24
25 IF NOT GRADE_CHANGE_TRAN_CUR%ISOPEN THEN
26 OPEN GRADE_CHANGE_TRAN_CUR(ITEM_ID_VI,LOT_ID_VI);
27 END IF;
28
29 FETCH GRADE_CHANGE_TRAN_CUR INTO trans_id_v;
30 IF trans_id_v = 0 THEN
31 SELECT QC_GRADE into qc_grade_v
32 from ic_tran_cmp_vw1 where trans_id = trans_id_vi;
33 else
34 SELECT QC_GRADE into qc_grade_v
35 from ic_tran_cmp_vw1 where trans_id = trans_id_v;
36 end if;
37
38 PKG_VAR_ITEM_ID_V := item_id_vi;
39 PKG_VAR_LOT_ID_V := lot_id_vi;
40 PKG_VAR_qc_grade_v := qc_grade_v;
41 return qc_grade_v;
42
43 end FIND_PROD_GRADE;
44
45 FUNCTION NO_OF_SAMPLES_TAKEN(BATCH_ID_VI IN INTEGER) RETURN INTEGER
46 is
47 NO_OF_SAMPLES_V INTEGER := 0;
48
49 Begin
50 if BATCH_ID_vi = PKG_VAR_BATCH_ID_V1 then
51 return PKG_VAR_NO_OF_SAMPLES_V;
52 end if;
53
54 select count(*) into NO_OF_SAMPLES_v from qc_smpl_mst
55 where batch_id = BATCH_ID_VI;
56
57 PKG_VAR_BATCH_ID_V1 := BATCH_ID_vi;
58 PKG_VAR_NO_OF_SAMPLES_V := NO_OF_SAMPLES_v;
59
60 return NO_OF_SAMPLES_V;
61 end NO_OF_SAMPLES_TAKEN;
62
63 FUNCTION NO_OF_SAMPLES_COMPLETE(BATCH_ID_VI IN INTEGER) RETURN INTEGER
64 is
65 Complet_SAMPLES_V INTEGER := 0;
66
67 Begin
68 if BATCH_ID_vi = PKG_VAR_BATCH_ID_V4 then
69 return PKG_VAR_NO_OF_SMPL_CMPLT_V;
70 end if;
71
72 select count(*) into COMPLET_SAMPLES_v from qc_smpl_mst
73 where batch_id = BATCH_ID_VI
74 and sample_status in ('ACCEPT','REJECT');
75
76 PKG_VAR_BATCH_ID_V4 := BATCH_ID_vi;
77 PKG_VAR_NO_OF_SMPL_CMPLT_V := COMPLET_SAMPLES_v;
78
79 return COMPLET_SAMPLES_V;
80 end NO_OF_SAMPLES_COMPLETE;
81
82 FUNCTION NO_OF_SAMPLES_PASSED(BATCH_ID_VI IN INTEGER) RETURN INTEGER
83 is
84 PASSED_SAMPLES_V INTEGER := 0;
85
86 Begin
87 if BATCH_ID_vi = PKG_VAR_BATCH_ID_V2 then
88 return PKG_VAR_PASSED_SAMPLES_V;
89 end if;
90
91 select count(*) into PASSED_SAMPLES_V from qc_smpl_mst
92 where batch_id = BATCH_ID_VI
93 and sample_status = 'ACCEPT';
94
95 PKG_VAR_BATCH_ID_V2 := BATCH_ID_vi;
96 PKG_VAR_PASSED_SAMPLES_V := PASSED_SAMPLES_v;
97
98 return PASSED_SAMPLES_V;
99 end NO_OF_SAMPLES_PASSED;
100
101 FUNCTION NO_OF_TIMES_ADJUSTED(BATCH_ID_VI IN INTEGER) RETURN INTEGER
102 is
103 ADJUST_BATCH_V INTEGER := 0;
104
105 Begin
106 if BATCH_ID_vi = PKG_VAR_BATCH_ID_V3 then
107 return PKG_VAR_ADJUST_BATCH_V;
108 end if;
109
110 select count(distinct to_char(item_id)||to_char(TRANS_DATE,'dd/mm/yy hh24:mi:ss'))
111 into ADJUST_BATCH_V
112 from ic_tran_cmp_vw1
113 where doc_id = BATCH_ID_VI
114 and doc_type = 'PROD'
115 and reason_code = fnd_profile.value('PMI$REASON_CODE_BTCH_ADJ');
116
117 PKG_VAR_BATCH_ID_V3 := BATCH_ID_vi;
118 PKG_VAR_ADJUST_BATCH_V := ADJUST_BATCH_v;
119
120 return ADJUST_BATCH_v;
121 end NO_OF_TIMES_ADJUSTED;
122
123
124 FUNCTION INGREDIENT_VALUE(BATCH_ID_VI IN INTEGER , ITEM_ID_VI INTEGER, LINE_NO_VI INTEGER,SOURCE IN VARCHAR2) RETURN NUMBER
125 is
126 INGREDIENT_VALUE_V NUMBER := 0;
127 L_ITEM_VALUE NUMBER:=0;
128 L_TOTAL_BATCH_OUT_VALUE NUMBER:=0;
129 Begin
130 IF SOURCE = 'ACTUAL' THEN
131 /* Calculate Totla Ingredient Usage Value */
132 SELECT sum(gmicuom.i2uom_cv(BtchDtl.ITEM_ID,0,BtchDtl.ITEM_UM,
133 BtchDtl.ACTUAL_QTY,ItemMst.ITEM_UM)*OPI_OPM_COMMON_PKG.OPMCO_GET_COST(
134 BtchDtl.ITEM_ID,BtchHdr.WIP_WHSE_CODE,NULL,
135 BtchHdr.ACTUAL_CMPLT_DATE))
136 into INGREDIENT_VALUE_V
137 FROM
138 PM_BTCH_HDR BtchHdr,
139 PM_MATL_DTL BtchDtl,
140 IC_ITEM_MST ItemMst
141 WHERE BtchHdr.BATCH_ID = BATCH_ID_VI
142 AND BtchHdr.BATCH_ID = BtchDtl.BATCH_ID
143 AND BtchDtl.ITEM_ID = ItemMst.ITEM_ID
144 AND BtchDtl.LINE_TYPE=-1;
145
146 /* Calculate Item Output Value */
147 SELECT gmicuom.i2uom_cv(BtchDtl.ITEM_ID,0,BtchDtl.ITEM_UM,
148 BtchDtl.ACTUAL_QTY,ItemMst.ITEM_UM)*OPI_OPM_COMMON_PKG.OPMCO_GET_COST(
149 BtchDtl.ITEM_ID,BtchHdr.WIP_WHSE_CODE,NULL,BtchHdr.ACTUAL_CMPLT_DATE)
150 into L_ITEM_VALUE
151 FROM
152 PM_BTCH_HDR BtchHdr,
153 PM_MATL_DTL BtchDtl,
154 IC_ITEM_MST ItemMst
155 WHERE BtchHdr.BATCH_ID = BATCH_ID_VI
156 AND BtchHdr.BATCH_ID = BtchDtl.BATCH_ID
157 AND BtchDtl.ITEM_ID = Itemmst.ITEM_ID
158 AND BtchDtl.LINE_TYPE=1
159 AND BtchDtl.ITEM_ID=ITEM_ID_VI
160 AND BtchDtl.LINE_NO=LINE_NO_VI;
161
162 /*Calculate Total Batch Out Put Value */
163 SELECT sum(gmicuom.i2uom_cv(BtchDtl.ITEM_ID,0,BtchDtl.ITEM_UM,
164 BtchDtl.ACTUAL_QTY,ItemMst.ITEM_UM)*OPI_OPM_COMMON_PKG.OPMCO_GET_COST(
165 BtchDtl.ITEM_ID,BtchHdr.WIP_WHSE_CODE,NULL,
166 DECODE(BtchHdr.BATCH_STATUS,3,BtchHdr.ACTUAL_CMPLT_DATE,
167 4,BtchHdr.ACTUAL_CMPLT_DATE,
168 BtchHdr.EXPCT_CMPLT_DATE)))
169 into L_TOTAL_BATCH_OUT_VALUE
170 FROM
171 PM_BTCH_HDR BtchHdr,
172 PM_MATL_DTL BtchDtl,
173 IC_ITEM_MST ItemMst
174 WHERE BtchHdr.BATCH_ID = BATCH_ID_VI
175 AND BtchHdr.BATCH_ID = BtchDtl.BATCH_ID
176 AND BtchDtl.ITEM_ID = ItemMst.ITEM_ID
177 AND BtchDtl.LINE_TYPE=1;
178 IF L_TOTAL_BATCH_OUT_VALUE = 0 THEN
179 INGREDIENT_VALUE_V:=0;
180 ELSE
181 INGREDIENT_VALUE_V:=INGREDIENT_VALUE_V*(l_ITEM_VALUE/L_TOTAL_BATCH_OUT_VALUE);
182 END IF;
183 ELSIF SOURCE='PLAN' THEN
184 SELECT sum(gmicuom.i2uom_cv(BtchDtl.ITEM_ID,0,BtchDtl.ITEM_UM,
185 BtchDtl.PLAN_QTY,ItemMst.ITEM_UM)*OPI_OPM_COMMON_PKG.OPMCO_GET_COST(
186 BtchDtl.ITEM_ID,BtchHdr.WIP_WHSE_CODE,NULL,
187 DECODE(BtchHdr.BATCH_STATUS,3,BtchHdr.ACTUAL_CMPLT_DATE,
188 4,BtchHdr.ACTUAL_CMPLT_DATE,
189 BtchHdr.EXPCT_CMPLT_DATE)))
190 into INGREDIENT_VALUE_V
191 FROM
192 PM_BTCH_HDR BtchHdr,
193 PM_MATL_DTL BtchDtl,
194 IC_ITEM_MST ItemMst
195 WHERE BtchHdr.BATCH_ID = BATCH_ID_VI
196 AND BtchHdr.BATCH_ID = BtchDtl.BATCH_ID
197 AND BtchDtl.ITEM_ID = ItemMst.ITEM_ID
198 AND BtchDtl.LINE_TYPE=-1;
199 END IF;
200 return INGREDIENT_VALUE_V;
201 end INGREDIENT_VALUE;
202
203 FUNCTION BYPRODUCT_VALUE(BATCH_ID_VI IN INTEGER, SOURCE IN VARCHAR2) RETURN NUMBER
204 is
205 BYPRODUCT_VALUE_V NUMBER := 0;
206 Begin
207 IF SOURCE = 'ACTUAL' THEN
208 SELECT sum(gmicuom.i2uom_cv(BtchDtl.ITEM_ID,0,BtchDtl.ITEM_UM,
209 BtchDtl.ACTUAL_QTY,ItemMst.ITEM_UM)*OPI_OPM_COMMON_PKG.OPMCO_GET_COST(
210 BtchDtl.ITEM_ID,BtchHdr.WIP_WHSE_CODE,NULL,BtchHdr.ACTUAL_CMPLT_DATE))
211 into BYPRODUCT_VALUE_V
212 FROM
213 PM_BTCH_HDR BtchHdr,
214 PM_MATL_DTL BtchDtl,
215 IC_ITEM_MST ItemMst
216 WHERE BtchHdr.BATCH_ID = BATCH_ID_VI
217 AND BtchHdr.BATCH_ID = BtchDtl.BATCH_ID
218 AND BtchDtl.ITEM_ID = ItemMst.ITEM_ID
219 AND BtchDtl.LINE_TYPE=2;
220 ELSIF SOURCE = 'PLAN' THEN
221 SELECT sum(gmicuom.i2uom_cv(BtchDtl.ITEM_ID,0,BtchDtl.ITEM_UM,
222 BtchDtl.PLAN_QTY,ItemMst.ITEM_UM)*OPI_OPM_COMMON_PKG.OPMCO_GET_COST(
223 BtchDtl.ITEM_ID,BtchHdr.WIP_WHSE_CODE,NULL,
224 DECODE(BtchHdr.BATCH_STATUS,3,BtchHdr.ACTUAL_CMPLT_DATE,
225 4,BtchHdr.ACTUAL_CMPLT_DATE,
226 BtchHdr.EXPCT_CMPLT_DATE)))
227 into BYPRODUCT_VALUE_V
228 FROM
229 PM_BTCH_HDR BtchHdr,
230 PM_MATL_DTL BtchDtl,
231 IC_ITEM_MST ItemMst
232 WHERE BtchHdr.BATCH_ID = BATCH_ID_VI
233 AND BtchHdr.BATCH_ID = BtchDtl.BATCH_ID
234 AND BtchDtl.ITEM_ID = ItemMst.ITEM_ID
235 AND BtchDtl.LINE_TYPE=2;
236 END IF;
237
238 return BYPRODUCT_VALUE_V;
239 end BYPRODUCT_VALUE;
240
241
242 FUNCTION SCHD_WORK_DAYS(BATCH_ID_VI IN INTEGER , START_DATE IN DATE, CMPLT_DATE IN DATE) RETURN NUMBER
243 is
244 START_DATE_V PM_BTCH_HDR.ACTUAL_START_DATE%type;
245 CMPLT_DATE_V PM_BTCH_HDR.ACTUAL_CMPLT_DATE%type;
246 NO_DAYS_V INTEGER := 0;
247 Count1_V INTEGER := 0;
248 Count2_V INTEGER := 0;
249 batch_time_v INTEGER := 0;
250 PLANT_CODE_V1 PM_BTCH_HDR.PLANT_CODE%type;
251 SCHEDULE_VI ps_schd_hdr.schedule%type;
252 SHIFT_START_V INTEGER := 0;
253 SHIFT_END_V INTEGER := 0;
254 SHIFT_DURATION_V INTEGER;
255 start_sec INTEGER;
256 end_sec INTEGER;
257 prev_end_sec INTEGER := 0;
258 batch_hrs_v NUMBER;
259 batch_days_v NUMBER;
260 Batch_day_V1 DATE;
261
262 CURSOR shop_cal_CUR(
263 SCHEDULE_V PS_SCHD_HDR.SCHEDULE%TYPE,
264 PLANT_CODE_V PM_BTCH_HDR.PLANT_CODE%type,
265 Batch_day_V DATE )
266 IS
267 SELECT shift_start,shift_duration
268 FROM ps_schd_dtl sd, ps_schd_hdr sh,
269 mr_shcl_dtl sc, mr_shdy_dtl sl
270 WHERE sd.schedule_id = sh.schedule_id
271 AND sd.Calendar_id = sc.Calendar_id
272 AND sc.shopday_no = sl.shopday_no
273 AND sh.schedule = SCHEDULE_V
274 AND sd.orgn_code = PLANT_CODE_V
275 AND trunc(sc.calendar_date) = trunc(batch_day_V)
276 order by shift_start;
277
278 Begin
279 SELECT PLANT_CODE
280 into PLANT_CODE_V1
281 from pm_btch_hdr
282 where batch_id = batch_id_vi;
283 /* Assign the Parameters */
284 START_DATE_V:=START_DATE;
285 CMPLT_DATE_V:=CMPLT_DATE;
286 SCHEDULE_VI := fnd_profile.value('GEMMS_DEFAULT_SCHEDULE');
287
288 select count(*) into count2_v
289 from ps_schd_dtl sd, ps_schd_hdr sh
290 where sd.schedule_id = sh.schedule_id
291 and sh.schedule = SCHEDULE_VI
292 AND sd.orgn_code = PLANT_CODE_V1;
293
294 if count2_v = 0 then /* No Calendar Found */
295 Batch_days_v := (CMPLT_DATE_V - START_DATE_V);
296 return Batch_days_v;
297 end if;
298
299 no_days_v := trunc(CMPLT_DATE_V) - trunc(START_DATE_V);
300 start_sec := 3600*to_char(START_DATE_V,'hh24') +
301 60*to_char(START_DATE_V,'mi') +
302 to_char(START_DATE_V,'ss');
303 End_sec := 3600*to_char(CMPLT_DATE_V,'hh24') +
304 60*to_char(CMPLT_DATE_V,'mi') +
305 to_char(CMPLT_DATE_V,'ss');
306
307 LOOP
308 batch_day_v1 := START_DATE_V + count1_v;
309
310 IF shop_cal_CUR%ISOPEN THEN
311 CLOSE shop_cal_CUR;
312 end if;
313
314 OPEN shop_cal_CUR(SCHEDULE_VI,PLANT_CODE_V1,batch_day_v1);
315
316 LOOP
317 FETCH shop_cal_CUR INTO SHIFT_START_V,SHIFT_DURATION_V;
318
319 if shop_cal_CUR%NOTFOUND then
320 if (prev_end_sec <= start_sec and start_sec < 86400) then
321 if (prev_end_sec <= end_sec and end_sec < 86400) then
322 if no_days_v = 0 then
323 Batch_time_v := (end_sec - start_sec);
324 EXIT;
325 else
326 if count1_v = 0 then
327 Batch_time_v := batch_time_v +(86400 - start_sec);
328 else
329 if count1_v = no_days_v then
330 Batch_time_v := batch_time_v +(end_sec - prev_end_sec);
331 end if;
332 end if;
333 end if;
334 else
335 if count1_v = 0 then
336 Batch_time_v := batch_time_v +(86400 - start_sec);
337 end if;
338 end if;
339 else
340 if (prev_end_sec <= end_sec and end_sec < 86400) then
341 if no_days_v = count1_v then
342 Batch_time_v := batch_time_v +(end_sec - prev_end_sec);
343 end if;
344 end if;
345 end if;
346 EXIT;
347 end if;
348
349 if (prev_end_sec <= start_sec and start_sec < SHIFT_START_V) then
350 if (prev_end_sec <= end_sec and end_sec < SHIFT_START_V) then
351 if no_days_v = 0 then
352 Batch_time_v := (end_sec - start_sec);
353
354 EXIT;
355 else
359 if count1_v = no_days_v then
356 if count1_v = 0 then
357 Batch_time_v := batch_time_v +(SHIFT_START_V - start_sec);
358 else
360 Batch_time_v := batch_time_v +(end_sec - prev_end_sec);
361 end if;
362 end if;
363 end if;
364 else
365 if count1_v = 0 then
366 Batch_time_v := batch_time_v +(SHIFT_START_V - start_sec);
367 end if;
368 end if;
369 else
370 if (prev_end_sec <= end_sec and end_sec < SHIFT_START_V) then
371 if no_days_v = count1_v then
372 Batch_time_v := batch_time_v +(end_sec - prev_end_sec);
373 end if;
374 end if;
375 end if;
376
377 shift_end_v := SHIFT_START_V + SHIFT_DURATION_V;
378
379 if (count1_v = 0 and start_sec > SHIFT_START_V) then
380 if start_sec < shift_end_v then
381 Batch_time_v := batch_time_v + (SHIFT_START_V - start_sec);
382 else
383 Batch_time_v := batch_time_v - SHIFT_DURATION_V;
384 end if;
385 end if;
386
387 if no_days_v = count1_v then /* Last Day */
388 if End_sec >= shift_end_v then
389 Batch_time_v := batch_time_v + SHIFT_DURATION_V;
390 else if end_sec > SHIFT_START_V then
391 Batch_time_v := batch_time_v + SHIFT_DURATION_V -
392 (shift_end_v - End_sec);
393 end if;
394 end if;
395 else /* Days in the Middle */
396 Batch_time_v := batch_time_v + SHIFT_DURATION_V;
397 end if;
398
399 prev_end_sec := shift_end_v;
400 END LOOP;
401
402 prev_end_sec := 0;
403 count1_v := count1_v + 1;
404 EXIT WHEN count1_v > no_days_v;
405 END LOOP;
406 Batch_days_v := Batch_time_v / 3600;
407 return Batch_days_v;
408 end SCHD_WORK_DAYS;
409 END OPI_EDW_OPM_PRD_PKG;