1 PACKAGE "EAM_FORECAST_PVT" AS
2 /* $Header: EAMVFORS.pls 120.7 2006/09/20 10:48:15 csprague noship $ */
3 TYPE forecast_asset_cursor_type IS REF CURSOR;
4 TYPE forecast_wo_cursor_type IS REF CURSOR;
5 TYPE wdj_table_type IS table of EAM_FORECAST_WDJ%ROWTYPE;
6 TYPE cebba_table_type IS table of EAM_FORECAST_CEBBA%ROWTYPE;
7 TYPE wo_table_type IS table of NUMBER INDEX BY BINARY_INTEGER;
8
9 type eam_forecast_rec_type is record
10 (
11 FORECAST_ID eam_forecast_cebba.FORECAST_ID%type,
12 PERIOD_SET_NAME eam_forecast_cebba.period_set_name%type,
13 PERIOD_NAME eam_forecast_cebba.period_name%type,
14 ACCT_PERIOD_ID eam_forecast_cebba.acct_period_id%type,
15 WIP_ENTITY_ID eam_forecast_cebba.wip_entity_id%type,
16 ORGANIZATION_ID eam_forecast_cebba.organization_id%type,
17 OPERATIONS_DEPT_ID eam_forecast_cebba.operations_dept_id%type,
18 OPERATION_SEQ_NUM eam_forecast_cebba.operation_seq_num%type,
19 MAINT_COST_CATEGORY eam_forecast_cebba.maint_cost_category%type,
20 txn_type eam_forecast_cebba.txn_type%type,
21 OWNING_DEPT_ID eam_forecast_cebba.owning_dept_id%type,
22 acct_VALUE eam_forecast_cebba.acct_value%type,
23 PERIOD_START_DATE eam_forecast_cebba.period_start_date%type,
24 LAST_UPDATE_DATE eam_forecast_cebba.last_update_date%type,
25 LAST_UPDATED_BY eam_forecast_cebba.last_updated_by%type,
26 CREATION_DATE eam_forecast_cebba.creation_date%type,
27 CREATED_BY eam_forecast_cebba.created_by%type,
28 LAST_UPDATE_LOGIN eam_forecast_cebba.last_update_login%type,
29 REQUEST_ID eam_forecast_cebba.request_id%type,
30 PROGRAM_APPLICATION_ID eam_forecast_cebba.program_application_id%type,
31 PROGRAM_ID eam_forecast_cebba.program_id%type,
32 PROGRAM_UPDATE_DATE eam_forecast_cebba.program_update_date%type,
33 CCID eam_forecast_cebba.ccid%type,
34 MFG_COST_ELEMENT_ID eam_forecast_cebba.mfg_cost_element_id%type,
35 PERIOD_YEAR eam_forecast_cebba.period_year%type,
36 PERIOD_NUM eam_forecast_cebba.period_num%type
37 );
38
39
40 Type eam_wo_relations_tbl_type is table of eam_forecast_rec_type
41 INDEX BY BINARY_INTEGER;
42
43 PROCEDURE insert_into_cebba_auto(p_cebba_table cebba_table_type);
44
45 PROCEDURE Extract_Forecast(
46 p_api_version IN NUMBER,
47 p_commit IN VARCHAR2 := FND_API.G_FALSE,
48 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
49 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
50 p_debug IN VARCHAR2 ,
51 p_forecast_id IN eam_forecasts.forecast_id%TYPE,
52 x_return_status OUT NOCOPY VARCHAR2 ,
53 x_msg_count OUT NOCOPY NUMBER ,
54 x_msg_data OUT NOCOPY VARCHAR2
55
56 );
57
58 procedure convert_work_orders(p_pm_group_id number,
59 p_return_status OUT NOCOPY VARCHAR2,
60 p_msg OUT NOCOPY VARCHAR2);
61 PROCEDURE insert_into_wdj_auto(p_wdj_table wdj_table_type);
62
63 procedure delete_forecast_data(p_forecast_id IN number);
64
65 PROCEDURE Copy_CEBBA_To_Forecast_auto (
66 p_api_version IN NUMBER,
67 p_commit IN VARCHAR2 := FND_API.G_FALSE,
68 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
69 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
70 p_debug IN VARCHAR2 ,
71
72 p_forecast_rec IN eam_forecasts%ROWTYPE,
73 p_wip_id_table IN wo_table_type,
74
75 --p_acct_period_from IN NUMBER,
76 --p_acct_period_to IN NUMBER,
77
78 p_user_id IN NUMBER,
79 p_request_id IN NUMBER,
80 p_prog_id IN NUMBER,
81 p_prog_app_id IN NUMBER,
82 p_login_id IN NUMBER,
83
84 x_return_status OUT NOCOPY VARCHAR2,
85 x_msg_count OUT NOCOPY NUMBER,
86 x_msg_data OUT NOCOPY VARCHAR2);
87 PROCEDURE extract_historical_forecast(
88 p_api_version IN NUMBER,
89 p_commit IN VARCHAR2 := FND_API.G_FALSE,
90 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
91 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
92 p_debug IN VARCHAR2 ,
93
94 p_forecast_rec IN eam_forecasts%ROWTYPE,
95
96
97 --p_acct_period_from IN NUMBER,
98 --p_acct_period_to IN NUMBER,
99
100 p_user_id IN NUMBER,
101 p_request_id IN NUMBER,
102 p_prog_id IN NUMBER,
103 p_prog_app_id IN NUMBER,
104 p_login_id IN NUMBER,
105
106 x_return_status OUT NOCOPY VARCHAR2,
107 x_msg_count OUT NOCOPY NUMBER,
108 x_msg_data OUT NOCOPY VARCHAR2);
109
110 PROCEDURE extract_autonomous_forecast(
111 p_api_version IN NUMBER,
112 p_commit IN VARCHAR2 := FND_API.G_FALSE,
113 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
114 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
115 p_debug IN VARCHAR2 ,
116
117 p_forecast_rec IN eam_forecasts%ROWTYPE,
118
119
120 --p_acct_period_from IN NUMBER,
121 --p_acct_period_to IN NUMBER,
122
123 p_user_id IN NUMBER,
124 p_request_id IN NUMBER,
125 p_prog_id IN NUMBER,
126 p_prog_app_id IN NUMBER,
127 p_login_id IN NUMBER,
128
129 x_return_status OUT NOCOPY VARCHAR2,
130 x_msg_count OUT NOCOPY NUMBER,
131 x_msg_data OUT NOCOPY VARCHAR2);
132
133 PROCEDURE Get_HistoricalCosts (
134 p_api_version IN NUMBER,
135 p_commit IN VARCHAR2 := FND_API.G_FALSE,
136 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
137 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
138 p_debug IN VARCHAR2 ,
139
140 p_forecast_id IN NUMBER,
141 p_organization_id IN NUMBER,
142 p_wip_entity_id IN NUMBER,
143 p_account_from IN VARCHAR2,
144 p_account_to IN VARCHAR2,
145 --p_acct_period_from IN NUMBER,
146 --p_acct_period_to IN NUMBER,
147
148 p_user_id IN NUMBER,
149 p_request_id IN NUMBER,
150 p_prog_id IN NUMBER,
151 p_prog_app_id IN NUMBER,
152 p_login_id IN NUMBER,
153
154
155 x_hist_cost_tbl OUT NOCOPY eam_wo_relations_tbl_type,
156 x_return_status OUT NOCOPY VARCHAR2,
157 x_msg_count OUT NOCOPY NUMBER,
158 x_msg_data OUT NOCOPY VARCHAR2);
159
160
161 procedure Generate_Forecast(
162 errbuf out NOCOPY varchar2,
163 retcode out NOCOPY varchar2,
164 p_forecast_id IN number);
165 /* This is a private PROCEDURE that extracts a future forecast */
166
167 PROCEDURE extract_future_forecast (
168 p_api_version IN NUMBER,
169 p_commit IN VARCHAR2 := FND_API.G_FALSE,
170 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
171 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
172 p_debug IN VARCHAR2 ,
173
174 p_forecast_rec IN eam_forecasts%ROWTYPE,
175
176
177 --p_acct_period_from IN NUMBER,
178 --p_acct_period_to IN NUMBER,
179
180 p_user_id IN NUMBER,
181 p_request_id IN NUMBER,
182 p_prog_id IN NUMBER,
183 p_prog_app_id IN NUMBER,
184 p_login_id IN NUMBER,
185
186 x_return_status OUT NOCOPY VARCHAR2,
187 x_msg_count OUT NOCOPY NUMBER,
188 x_msg_data OUT NOCOPY VARCHAR2);
189
190
191 PROCEDURE Copy_WDJ_To_Forecast (
192 p_api_version IN NUMBER,
193 p_commit IN VARCHAR2 := FND_API.G_FALSE,
194 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
195 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
196 p_debug IN VARCHAR2 ,
197
198 p_forecast_rec IN eam_forecasts%ROWTYPE,
199 p_wip_id_table IN wo_table_type,
200
201 --p_acct_period_from IN NUMBER,
202 --p_acct_period_to IN NUMBER,
203
204 p_user_id IN NUMBER,
205 p_request_id IN NUMBER,
206 p_prog_id IN NUMBER,
207 p_prog_app_id IN NUMBER,
208 p_login_id IN NUMBER,
209
210 x_return_status OUT NOCOPY VARCHAR2,
211 x_msg_count OUT NOCOPY NUMBER,
212 x_msg_data OUT NOCOPY VARCHAR2);
213
214 PROCEDURE Copy_WDJ_To_Forecast_auto (
215 p_api_version IN NUMBER,
216 p_commit IN VARCHAR2 := FND_API.G_FALSE,
217 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
218 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
219 p_debug IN VARCHAR2 ,
220
221 p_forecast_rec IN eam_forecasts%ROWTYPE,
222 p_wip_id_table IN wo_table_type,
223
224 --p_acct_period_from IN NUMBER,
225 --p_acct_period_to IN NUMBER,
226
227 p_user_id IN NUMBER,
228 p_request_id IN NUMBER,
229 p_prog_id IN NUMBER,
230 p_prog_app_id IN NUMBER,
231 p_login_id IN NUMBER,
232
233 x_return_status OUT NOCOPY VARCHAR2,
234 x_msg_count OUT NOCOPY NUMBER,
235 x_msg_data OUT NOCOPY VARCHAR2);
236
237 procedure delete_work_order(p_forecast_id IN number, p_wip_id IN number);
238
239 procedure delete_forecast(p_forecast_id IN number);
240
241 PROCEDURE Copy_WOR_To_Forecast (
242 p_api_version IN NUMBER,
243 p_commit IN VARCHAR2 := FND_API.G_FALSE,
244 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
245 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
246 p_debug IN VARCHAR2 ,
247
248 p_forecast_rec IN eam_forecasts%ROWTYPE,
249 p_wip_id_table IN wo_table_type,
250
251 --p_acct_period_from IN NUMBER,
252 --p_acct_period_to IN NUMBER,
253
254 p_user_id IN NUMBER,
255 p_request_id IN NUMBER,
256 p_prog_id IN NUMBER,
257 p_prog_app_id IN NUMBER,
258 p_login_id IN NUMBER,
259
260 x_return_status OUT NOCOPY VARCHAR2,
261 x_msg_count OUT NOCOPY NUMBER,
262 x_msg_data OUT NOCOPY VARCHAR2);
263
264 PROCEDURE Copy_WRO_To_Forecast (
265 p_api_version IN NUMBER,
266 p_commit IN VARCHAR2 := FND_API.G_FALSE,
267 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
268 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
269 p_debug IN VARCHAR2 ,
270
271 p_forecast_rec IN eam_forecasts%ROWTYPE,
272 p_wip_id_table IN wo_table_type,
273
274 --p_acct_period_from IN NUMBER,
275 --p_acct_period_to IN NUMBER,
276
277 p_user_id IN NUMBER,
278 p_request_id IN NUMBER,
279 p_prog_id IN NUMBER,
280 p_prog_app_id IN NUMBER,
281 p_login_id IN NUMBER,
282
283 x_return_status OUT NOCOPY VARCHAR2,
284 x_msg_count OUT NOCOPY NUMBER,
285 x_msg_data OUT NOCOPY VARCHAR2);
286
287 PROCEDURE Copy_WO_To_Forecast (
288 p_api_version IN NUMBER,
289 p_commit IN VARCHAR2 := FND_API.G_FALSE,
290 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
291 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
292 p_debug IN VARCHAR2 ,
293
294 p_forecast_rec IN eam_forecasts%ROWTYPE,
295 p_wip_id_table IN wo_table_type,
296
297 --p_acct_period_from IN NUMBER,
298 --p_acct_period_to IN NUMBER,
299
300 p_user_id IN NUMBER,
301 p_request_id IN NUMBER,
302 p_prog_id IN NUMBER,
303 p_prog_app_id IN NUMBER,
304 p_login_id IN NUMBER,
305
306 x_return_status OUT NOCOPY VARCHAR2,
307 x_msg_count OUT NOCOPY NUMBER,
308 x_msg_data OUT NOCOPY VARCHAR2);
309
310
311 PROCEDURE Copy_CEBBA_To_Forecast (
312 p_api_version IN NUMBER,
313 p_commit IN VARCHAR2 := FND_API.G_FALSE,
314 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
315 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
316 p_debug IN VARCHAR2 ,
317
318 p_forecast_rec IN eam_forecasts%ROWTYPE,
319 p_wip_id_table IN wo_table_type,
320
321 --p_acct_period_from IN NUMBER,
322 --p_acct_period_to IN NUMBER,
323
324 p_user_id IN NUMBER,
325 p_request_id IN NUMBER,
326 p_prog_id IN NUMBER,
327 p_prog_app_id IN NUMBER,
328 p_login_id IN NUMBER,
329
330 x_return_status OUT NOCOPY VARCHAR2,
331 x_msg_count OUT NOCOPY NUMBER,
332 x_msg_data OUT NOCOPY VARCHAR2);
333
334 PROCEDURE Copy_WEDI_To_Forecast (
335 p_api_version IN NUMBER,
336 p_commit IN VARCHAR2 := FND_API.G_FALSE,
337 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
338 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
339 p_debug IN VARCHAR2 ,
340
341 p_forecast_rec IN eam_forecasts%ROWTYPE,
342 p_wip_id_table IN wo_table_type,
343
344 --p_acct_period_from IN NUMBER,
345 --p_acct_period_to IN NUMBER,
346
347 p_user_id IN NUMBER,
348 p_request_id IN NUMBER,
349 p_prog_id IN NUMBER,
350 p_prog_app_id IN NUMBER,
351 p_login_id IN NUMBER,
352
353 x_return_status OUT NOCOPY VARCHAR2,
354 x_msg_count OUT NOCOPY NUMBER,
355 x_msg_data OUT NOCOPY VARCHAR2);
356
357 PROCEDURE Populate_Test_Data(p_forecast_id IN NUMBER);
358 FUNCTION get_wip_table(p_forecast_rec eam_forecasts%rowtype)
359 RETURN wo_table_type;
360
361 FUNCTION get_asset_cursor(p_forecast_rec eam_forecasts%rowtype)
362 RETURN forecast_asset_cursor_type;
363
364 FUNCTION get_asset_cursor(p_organization_id IN NUMBER,p_asset_number_from IN VARCHAR2,
365 p_asset_number_to IN VARCHAR2, p_serial_number_from IN VARCHAR2,
366 p_serial_number_to IN VARCHAR2 , p_asset_group_from IN VARCHAR2,
367 p_asset_group_to IN VARCHAR2 , p_area_from IN VARCHAR2, p_area_to IN VARCHAR2)
368 RETURN forecast_asset_cursor_type;
369
370 FUNCTION get_asset_query(p_forecast_rec eam_forecasts%rowtype)
371 RETURN VARCHAR2;
372
373 FUNCTION get_asset_query(p_organization_id IN NUMBER,p_asset_number_from IN VARCHAR2,
374 p_asset_number_to IN VARCHAR2, p_serial_number_from IN VARCHAR2,
375 p_serial_number_to IN VARCHAR2 , p_asset_group_from IN VARCHAR2,
376 p_asset_group_to IN VARCHAR2 , p_area_from IN VARCHAR2, p_area_to IN VARCHAR2)
377 RETURN VARCHAR2;
378
379 PROCEDURE debug(l_msg IN VARCHAR2, l_level IN NUMBER := 1);
380
381 FUNCTION getForecastXml(p_forecast_id NUMBER)return CLOB;
382 END;
[Home] [Help]