[Home] [Help]
PACKAGE BODY: APPS.EDW_OPI_LOT_MOPM_SZ
Source
1 PACKAGE BODY EDW_OPI_LOT_MOPM_SZ AS
2 /* $Header: OPIPLTZB.pls 120.1 2005/06/07 03:54:45 appldev $*/
3
4 -- procedure to count Lot Dimension rows.
5
6 PROCEDURE cnt_rows (p_from_date IN DATE,
7 p_to_date IN DATE,
8 p_num_rows OUT NOCOPY NUMBER) IS
9 BEGIN
10 SELECT count(*) into p_num_rows
11 FROM IC_LOTS_MST LT
12 WHERE lt.last_update_date between p_from_date and p_to_date;
13 --dbms_output.put_line ('Number of rows : '||p_num_rows);
14 EXCEPTION
15 WHEN OTHERS THEN
16 p_num_rows := 0;
17 END cnt_rows;
18
19 -- procedure to get average row length Lot Dimension rows.
20
21 PROCEDURE est_row_len (p_from_date IN DATE,
22 p_to_date IN DATE,
23 p_avg_row_len OUT NOCOPY NUMBER) IS
24 x_date number := 7;
25 x_total number := 0;
26 x_constant number := 6;
27 x_INSTANCE NUMBER := 0;
28 x_LOT_DP NUMBER := 0;
29 x_EXPIRATION_DATE NUMBER := x_date;
30 x_LOT_PK NUMBER := 0;
31 x_NAME NUMBER := 0;
32 x_LOT_NAME NUMBER := 0;
33 x_LOT NUMBER := 0;
34 x_PARENT_LOT NUMBER := 0;
35 x_DESCRIPTION NUMBER := 0;
36 x_last_update_date NUMBER := x_date;
37 x_creation_date NUMBER := x_date;
38 cursor CUR_INSTANCE_SIZE is
39 select avg(nvl(vsize(instance_code),0))
40 from edw_local_instance;
41
42 CURSOR CUR_OPM_LOT_SIZES IS
43 SELECT avg(nvl(vsize(LT.LOT_NO),0)) LOT_NO,
44 avg(nvl(vsize(LT.SUBLOT_NO),0)) SUBLOT_NO,
45 avg(nvl(vsize(LT.LOT_DESC),0)) LOT_DESC,
46 avg(nvl(vsize(LOT_NO||DECODE(SUBLOT_NO,NULL,NULL,'-'||SUBLOT_NO) ||'('|| IIM.ITEM_NO ||')'),0)) NAME,
47 avg(nvl(vsize(LT.LOT_ID||'-'||LT.ITEM_ID||'-'||'OPM'||'-'),0)) LOT_PK,
48 avg(vsize('LOTD'))
49 FROM IC_LOTS_MST LT,
50 IC_ITEM_MST IIM
51 WHERE LT.ITEM_ID = IIM.ITEM_ID
52 AND LT.LAST_UPDATE_DATE BETWEEN P_FROM_DATE AND P_TO_DATE;
53 BEGIN
54 OPEN CUR_INSTANCE_SIZE;
55 FETCH CUR_INSTANCE_SIZE INTO x_INSTANCE;
56 CLOSE CUR_INSTANCE_SIZE;
57 OPEN CUR_OPM_LOT_SIZES;
58 FETCH CUR_OPM_LOT_SIZES INTO x_PARENT_LOT,x_LOT,x_DESCRIPTION,x_NAME,x_LOT_PK,x_LOT_DP;
59 CLOSE CUR_OPM_LOT_SIZES;
60 x_total := NVL(ceil(x_INSTANCE + 1), 0) +
61 NVL(ceil(x_PARENT_LOT + 1), 0) +
62 NVL(ceil(x_LOT + 1), 0) +
63 2 *NVL(ceil(x_DESCRIPTION+ 1), 0) +
64 NVL(ceil(x_NAME + 1), 0) +
65 NVL(ceil(x_LOT_PK + 1), 0) +
66 NVL(ceil(x_creation_date + 1), 0) +
67 NVL(ceil(x_creation_date + 1), 0) +
68 NVL(ceil(x_EXPIRATION_DATE + 1), 0) +
69 NVL(ceil(x_LOT_DP + 1), 0);
70 p_avg_row_len := x_total;
71 --dbms_output.put_line ('Average Row Length IS : '||p_avg_row_len);
72 EXCEPTION
73 WHEN OTHERS THEN
74 p_avg_row_len := 0;
75 END est_row_len;
76 END EDW_OPI_LOT_MOPM_SZ;
77