1 PACKAGE WIP_PROD_INDICATORS AUTHID CURRENT_USER AS
2 /* $Header: wippinds.pls 115.14 2003/12/09 01:37:54 digupta ship $ */
3
4 /* Public Procedures */
5
6 PROCEDURE Load_Summary_Info(
7 errbuf OUT NOCOPY VARCHAR2,
8 retcode OUT NOCOPY VARCHAR2,
9 p_date_from IN VARCHAR2,
10 p_date_to IN VARCHAR2);
11
12 PROCEDURE Populate_Summary_Table (
13 p_group_id IN NUMBER,
14 p_organization_id IN NUMBER,
15 p_date_from IN DATE,
16 p_date_to IN DATE,
17 p_department_id IN NUMBER,
18 p_resource_id IN NUMBER,
19 p_userid IN NUMBER,
20 p_applicationid IN NUMBER,
21 p_errnum OUT NOCOPY NUMBER,
22 p_errmesg OUT NOCOPY VARCHAR2 );
23
24 PROCEDURE Populate_Efficiency (
25 p_group_id IN NUMBER,
26 p_organization_id IN NUMBER,
27 p_date_from IN DATE,
28 p_date_to IN DATE,
29 p_department_id IN NUMBER,
30 p_resource_id IN NUMBER,
31 p_userid IN NUMBER,
32 p_applicationid IN NUMBER,
33 p_errnum OUT NOCOPY NUMBER,
34 p_errmesg OUT NOCOPY VARCHAR2 );
35
36
37 PROCEDURE Populate_Utilization (
38 p_group_id IN NUMBER,
39 p_organization_id IN NUMBER,
40 p_date_from IN DATE,
41 p_date_to IN DATE,
42 p_department_id IN NUMBER,
43 p_resource_id IN NUMBER,
44 p_userid IN NUMBER,
45 p_applicationid IN NUMBER,
46 p_errnum OUT NOCOPY NUMBER,
47 p_errmesg OUT NOCOPY VARCHAR2,
48 p_sfcb IN NUMBER DEFAULT NULL );
49
50
51 PROCEDURE Populate_Yield (
52 p_group_id IN NUMBER,
53 p_organization_id IN NUMBER,
54 p_date_from IN DATE,
55 p_date_to IN DATE,
56 p_department_id IN NUMBER,
57 p_resource_id IN NUMBER,
58 p_userid IN NUMBER,
59 p_applicationid IN NUMBER,
60 p_errnum OUT NOCOPY NUMBER,
61 p_errmesg OUT NOCOPY VARCHAR2 );
62
63
64 PROCEDURE Calc_Eff_Applied_Units (
65 p_errmesg OUT NOCOPY VARCHAR2,
66 p_errnum OUT NOCOPY NUMBER,
67 p_group_id IN NUMBER);
68
69
70 PROCEDURE Calculate_Std_Quantity (
71 p_group_id IN NUMBER,
72 p_organization_id IN NUMBER,
73 p_date_from IN DATE,
74 p_date_to IN DATE,
75 p_department_id IN NUMBER,
76 p_indicator IN NUMBER );
77
78
79 PROCEDURE Calculate_Std_Units (
80 p_group_id IN NUMBER,
81 p_resource_id IN NUMBER,
82 p_errnum OUT NOCOPY NUMBER,
83 p_errmesg OUT NOCOPY VARCHAR2,
84 p_indicator IN NUMBER );
85
86
87 PROCEDURE Calculate_Total_Quantity (
88 p_group_id IN NUMBER,
89 p_organization_id IN NUMBER,
90 p_date_from IN DATE,
91 p_date_to IN DATE,
92 p_department_id IN NUMBER);
93
94
95 PROCEDURE Calculate_Scrap_Quantity (
96 p_group_id IN NUMBER,
97 p_organization_id IN NUMBER,
98 p_date_from IN DATE,
99 p_date_to IN DATE,
100 p_errnum OUT NOCOPY NUMBER,
101 p_errmesg OUT NOCOPY VARCHAR2 );
102
103
104 PROCEDURE Delete_Temp_Info (
105 p_group_id IN NUMBER);
106
107 PROCEDURE Populate_Assy_Yield (
108 p_organization_id IN NUMBER,
109 p_date_from IN DATE,
110 p_date_to IN DATE,
111 p_userid IN NUMBER,
112 p_applicationid IN NUMBER,
113 p_errnum OUT NOCOPY NUMBER,
114 p_errmesg OUT NOCOPY VARCHAR2 );
115
116 PROCEDURE Populate_Productivity (
117 p_group_id IN NUMBER,
118 p_organization_id IN NUMBER,
119 p_date_from IN DATE,
120 p_date_to IN DATE,
121 p_department_id IN NUMBER,
122 p_resource_id IN NUMBER,
123 p_userid IN NUMBER,
124 p_applicationid IN NUMBER,
125 p_errnum OUT NOCOPY NUMBER,
126 p_errmesg OUT NOCOPY VARCHAR2);
127
128 PROCEDURE Populate_Resource_Load (
129 p_group_id IN NUMBER,
130 p_organization_id IN NUMBER,
131 p_date_from IN DATE,
132 p_date_to IN DATE,
133 p_department_id IN NUMBER,
134 p_resource_id IN NUMBER,
135 p_userid IN NUMBER,
136 p_applicationid IN NUMBER,
137 p_errnum OUT NOCOPY NUMBER,
138 p_errmesg OUT NOCOPY VARCHAR2);
139
140 PROCEDURE Calculate_Resource_Avail(
141 p_organization_id IN NUMBER,
142 p_date_from IN DATE,
143 p_date_to IN DATE,
144 p_department_id IN NUMBER,
145 p_resource_id IN NUMBER,
146 p_errnum OUT NOCOPY NUMBER,
147 p_errmesg OUT NOCOPY VARCHAR2);
148
149 FUNCTION get_Workday_Ratio (
150 p_resource_id IN NUMBER,
151 p_organization_id IN NUMBER,
152 p_start_date IN DATE,
153 p_completion_date IN DATE,
154 p_transaction_date IN DATE)
155 RETURN NUMBER ;
156
157
158 PROCEDURE Populate_Denormalize_Data (
159 p_errnum IN OUT NOCOPY NUMBER,
160 p_errmesg IN OUT NOCOPY VARCHAR2);
161
162
163 PROCEDURE denormalize_item_dimension (
164 p_table_name IN VARCHAR2,
165 p_errnum IN OUT NOCOPY NUMBER,
166 p_errmesg IN OUT NOCOPY VARCHAR2);
167
168
169 PROCEDURE denormalize_time_dimension (
170 p_table_name IN VARCHAR2,
171 p_errnum IN OUT NOCOPY NUMBER,
172 p_errmesg IN OUT NOCOPY VARCHAR2);
173
174
175 PROCEDURE denormalize_org_dimension (
176 p_table_name IN VARCHAR2,
177 p_errnum IN OUT NOCOPY NUMBER,
178 p_errmesg IN OUT NOCOPY VARCHAR2);
179
180 PROCEDURE denormalize_geo_dimension (
181 p_table_name IN VARCHAR2,
182 p_errnum IN OUT NOCOPY NUMBER,
183 p_errmesg IN OUT NOCOPY VARCHAR2);
184
185
186 PRAGMA RESTRICT_REFERENCES (get_Workday_Ratio, WNDS, WNPS);
187
188
189 /* Some constants -- For a bug fix*/
190 -- Since might be redefining tables, catch that exception
191 object_already_defined EXCEPTION;
192 -- The associated error number is -995
193 PRAGMA EXCEPTION_INIT (object_already_defined, -955);
194
195
196 -- First query rewrite.
197 -- All we do here is decompose the wip_indicators_temp table
198 -- into 3 separate tables based on whether the records have
199 -- indicator_type = WIP_EFFICIENCY, WIP_UTILIZATION, WIP_YIELD
200 PROCEDURE simple_decomp (
201 p_group_id IN NUMBER);
202
203
204 END WIP_PROD_INDICATORS;
[Home] [Help]