[Home] [Help]
PACKAGE BODY: APPS.EDW_MRP_PLAN_M_SIZE
Source
1 PACKAGE BODY EDW_MRP_PLAN_M_SIZE AS
2 /* $Header: ISCSGD2B.pls 115.2 2002/12/19 00:45:50 scheung ship $ */
3
4 /* ------------------------------------------
5 PROCEDURE NAME : cnt_rows
6 INPUT PARAMETERS : p_from_date, p_to_date
7 OUTPUT PARAMETERS: p_num_rows
8 DESCRIPTION : Count the number of rows
9 ------------------------------------------- */
10
11 PROCEDURE cnt_rows(p_from_date DATE,
12 p_to_date DATE,
13 p_num_rows OUT NOCOPY NUMBER) IS
14
15 BEGIN
16
17 SELECT count(*)
18 INTO p_num_rows
19 FROM mfg_lookups ml1,
20 mtl_parameters mp,
21 mrp_plans mpl
22 WHERE ml1.lookup_type = 'MRP_PLAN_TYPE'
23 AND ml1.lookup_code = mpl.plan_type
24 AND mp.organization_id=mpl.organization_id
25 AND mpl.plan_completion_date IS NOT NULL
26 AND mpl.last_update_date BETWEEN p_from_date AND p_to_date;
27
28 Exception When others then
29 rollback;
30
31 END;
32
33 /* ------------------------------------------
34 PROCEDURE NAME : est_row_len
35 INPUT PARAMETERS : p_from_date, p_to_date
36 OUTPUT PARAMETERS: p_avg_row_len
37 DESCRIPTION : Estimate input_f
38 ------------------------------------------ */
39
40 PROCEDURE est_row_len(p_from_date DATE,
41 p_to_date DATE,
42 p_avg_row_len OUT NOCOPY NUMBER) IS
43
44 x_total number := 0;
45 x_org_code number := 0;
46 x_compile_designator number := 0;
47 x_org_id number := 0;
48 x_PLAN_NAME_PK NUMBER;
49 x_PLAN_TYPE NUMBER;
50 x_PLAN_NAME NUMBER;
51 x_DESCRIPTION NUMBER;
52 x_INCLUDE_PO_FLAG NUMBER;
53 x_INCLUDE_WIP_FLAG NUMBER;
54 x_COMPLETION_DATE NUMBER;
55 x_PLAN_RESERVE_FLAG NUMBER;
56 x_PLAN_SAFETY_STOCK NUMBER;
57 x_PLAN_CUTOFF_DATE NUMBER;
58 x_PLAN_NAME_DP NUMBER;
59 x_NAME NUMBER;
60 x_CREATION_DATE NUMBER;
61 x_LAST_UPDATE_DATE NUMBER;
62
63 CURSOR c_1 IS
64 SELECT nvl(avg(nvl(vsize(meaning),0)),0)
65 FROM mfg_lookups
66 WHERE lookup_type = 'MRP_PLAN_TYPE';
67
68 CURSOR c_2 IS
69 SELECT nvl(avg(nvl(vsize(organization_code),0)),0)
70 FROM mtl_parameters;
71
72 CURSOR c_3 IS
73 SELECT nvl(avg(nvl(vsize(compile_designator),0)),0),
74 nvl(avg(nvl(vsize(organization_id),0)),0),
75 nvl(avg(nvl(vsize(description),0)),0),
76 nvl(avg(nvl(vsize(consider_po),0)),0),
77 nvl(avg(nvl(vsize(consider_wip),0)),0),
78 nvl(avg(nvl(vsize(plan_completion_date),0)),0),
79 nvl(avg(nvl(vsize(consider_reservations),0)),0),
80 nvl(avg(nvl(vsize(plan_safety_stock),0)),0),
81 nvl(avg(nvl(vsize(cutoff_date),0)),0),
82 nvl(avg(nvl(vsize(creation_date),0)),0),
83 nvl(avg(nvl(vsize(last_update_date),0)),0)
84 FROM mrp_plans
85 WHERE last_update_date BETWEEN p_from_date AND p_to_date;
86
87 BEGIN
88
89 OPEN c_1;
90 FETCH c_1 INTO x_PLAN_TYPE;
91 CLOSE c_1;
92
93 x_total := 3 + x_total + ceil(x_PLAN_TYPE + 1);
94
95 OPEN c_2;
96 FETCH c_2 INTO x_org_code;
97 CLOSE c_2;
98
99 OPEN c_3;
100 FETCH c_3 INTO x_compile_designator, x_org_id, x_DESCRIPTION,
101 x_INCLUDE_PO_FLAG, x_INCLUDE_WIP_FLAG, x_COMPLETION_DATE,
102 x_PLAN_RESERVE_FLAG, x_PLAN_SAFETY_STOCK, x_PLAN_CUTOFF_DATE,
103 x_CREATION_DATE, x_LAST_UPDATE_DATE;
104 CLOSE c_3;
105
106 x_PLAN_NAME_PK := x_compile_designator + x_org_id;
107 x_PLAN_NAME := x_compile_designator + x_org_code;
108 x_PLAN_NAME_DP := x_PLAN_NAME;
109 x_NAME := x_PLAN_NAME;
110
111 x_total := x_total + ceil(x_PLAN_NAME_PK + 1) + ceil(x_PLAN_NAME + 1) +
112 ceil(x_DESCRIPTION + 1) + ceil(x_INCLUDE_PO_FLAG + 1) +
113 ceil(x_INCLUDE_WIP_FLAG + 1) + ceil(x_COMPLETION_DATE + 1) +
114 ceil(x_PLAN_RESERVE_FLAG + 1) + ceil(x_PLAN_SAFETY_STOCK + 1) +
115 ceil(x_PLAN_CUTOFF_DATE + 1) + ceil(x_PLAN_NAME_DP + 1) +
116 ceil(x_NAME + 1) + ceil(x_CREATION_DATE + 1) +
117 ceil(x_LAST_UPDATE_DATE + 1);
118
119 p_avg_row_len := x_total;
120
121 Exception When others then
122 rollback;
123
124 END;
125
126 END EDW_MRP_PLAN_M_SIZE;