1 PACKAGE "EAM_FORECAST_PVT" AUTHID CURRENT_USER AS
2 /* $Header: EAMVFORS.pls 120.7.12020000.2 2012/08/03 08:57:37 vchidura ship $ */
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 wop_table_type IS TABLE OF EAM_FORECAST_WO%ROWTYPE ; --USAF Bug 13693293
7 TYPE wor_table_type IS TABLE OF EAM_FORECAST_WOR%ROWTYPE ; --USAF Bug 13693293
8 TYPE wro_table_type IS TABLE OF EAM_FORECAST_WRO%ROWTYPE ; --USAF Bug 13693293
9 TYPE wedi_table_type IS TABLE OF EAM_FORECAST_WEDI%ROWTYPE ; --USAF Bug 13693293
10 TYPE cebba_table_type IS table of EAM_FORECAST_CEBBA%ROWTYPE;
11 TYPE wo_table_type IS table of NUMBER INDEX BY BINARY_INTEGER;
12
13 type eam_forecast_rec_type is record
14 (
15 FORECAST_ID eam_forecast_cebba.FORECAST_ID%type,
16 PERIOD_SET_NAME eam_forecast_cebba.period_set_name%type,
17 PERIOD_NAME eam_forecast_cebba.period_name%type,
18 ACCT_PERIOD_ID eam_forecast_cebba.acct_period_id%type,
19 WIP_ENTITY_ID eam_forecast_cebba.wip_entity_id%type,
20 ORGANIZATION_ID eam_forecast_cebba.organization_id%type,
21 OPERATIONS_DEPT_ID eam_forecast_cebba.operations_dept_id%type,
22 OPERATION_SEQ_NUM eam_forecast_cebba.operation_seq_num%type,
23 MAINT_COST_CATEGORY eam_forecast_cebba.maint_cost_category%type,
24 txn_type eam_forecast_cebba.txn_type%type,
25 OWNING_DEPT_ID eam_forecast_cebba.owning_dept_id%type,
26 acct_VALUE eam_forecast_cebba.acct_value%type,
27 PERIOD_START_DATE eam_forecast_cebba.period_start_date%type,
28 LAST_UPDATE_DATE eam_forecast_cebba.last_update_date%type,
29 LAST_UPDATED_BY eam_forecast_cebba.last_updated_by%type,
30 CREATION_DATE eam_forecast_cebba.creation_date%type,
31 CREATED_BY eam_forecast_cebba.created_by%type,
32 LAST_UPDATE_LOGIN eam_forecast_cebba.last_update_login%type,
33 REQUEST_ID eam_forecast_cebba.request_id%type,
34 PROGRAM_APPLICATION_ID eam_forecast_cebba.program_application_id%type,
35 PROGRAM_ID eam_forecast_cebba.program_id%type,
36 PROGRAM_UPDATE_DATE eam_forecast_cebba.program_update_date%type,
37 CCID eam_forecast_cebba.ccid%type,
38 MFG_COST_ELEMENT_ID eam_forecast_cebba.mfg_cost_element_id%type,
39 PERIOD_YEAR eam_forecast_cebba.period_year%type,
40 PERIOD_NUM eam_forecast_cebba.period_num%type
41 );
42
43
44 Type eam_wo_relations_tbl_type is table of eam_forecast_rec_type
45 INDEX BY BINARY_INTEGER;
46
47 PROCEDURE insert_into_cebba_auto(p_cebba_table cebba_table_type);
48
49 PROCEDURE Extract_Forecast(
50 p_api_version IN NUMBER,
51 p_commit IN VARCHAR2 := FND_API.G_FALSE,
52 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
53 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
54 p_debug IN VARCHAR2 ,
55 p_forecast_id IN eam_forecasts.forecast_id%TYPE,
56 x_return_status OUT NOCOPY VARCHAR2 ,
57 x_msg_count OUT NOCOPY NUMBER ,
58 x_msg_data OUT NOCOPY VARCHAR2
59
60 );
61
62 procedure convert_work_orders(p_pm_group_id number,
63 p_return_status OUT NOCOPY VARCHAR2,
64 p_msg OUT NOCOPY VARCHAR2);
65 PROCEDURE insert_into_wdj_auto(p_wdj_table wdj_table_type);
66
67 PROCEDURE insert_into_wop_auto(p_wop_table wop_table_type); --USAF
68
69 procedure delete_forecast_data(p_forecast_id IN number);
70
71 PROCEDURE Copy_CEBBA_To_Forecast_auto (
72 p_api_version IN NUMBER,
73 p_commit IN VARCHAR2 := FND_API.G_FALSE,
74 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
75 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
76 p_debug IN VARCHAR2 ,
77
78 p_forecast_rec IN eam_forecasts%ROWTYPE,
79 p_wip_id_table IN wo_table_type,
80
81 --p_acct_period_from IN NUMBER,
82 --p_acct_period_to IN NUMBER,
83
84 p_user_id IN NUMBER,
85 p_request_id IN NUMBER,
86 p_prog_id IN NUMBER,
87 p_prog_app_id IN NUMBER,
88 p_login_id IN NUMBER,
89
90 x_return_status OUT NOCOPY VARCHAR2,
91 x_msg_count OUT NOCOPY NUMBER,
92 x_msg_data OUT NOCOPY VARCHAR2);
93 PROCEDURE extract_historical_forecast(
94 p_api_version IN NUMBER,
95 p_commit IN VARCHAR2 := FND_API.G_FALSE,
96 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
97 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
98 p_debug IN VARCHAR2 ,
99
100 p_forecast_rec IN eam_forecasts%ROWTYPE,
101
102
103 --p_acct_period_from IN NUMBER,
104 --p_acct_period_to IN NUMBER,
105
106 p_user_id IN NUMBER,
107 p_request_id IN NUMBER,
108 p_prog_id IN NUMBER,
109 p_prog_app_id IN NUMBER,
110 p_login_id IN NUMBER,
111
112 x_return_status OUT NOCOPY VARCHAR2,
113 x_msg_count OUT NOCOPY NUMBER,
114 x_msg_data OUT NOCOPY VARCHAR2);
115
116 PROCEDURE extract_autonomous_forecast(
117 p_api_version IN NUMBER,
118 p_commit IN VARCHAR2 := FND_API.G_FALSE,
119 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
120 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
121 p_debug IN VARCHAR2 ,
122
123 p_forecast_rec IN eam_forecasts%ROWTYPE,
124
125
126 --p_acct_period_from IN NUMBER,
127 --p_acct_period_to IN NUMBER,
128
129 p_user_id IN NUMBER,
130 p_request_id IN NUMBER,
131 p_prog_id IN NUMBER,
132 p_prog_app_id IN NUMBER,
133 p_login_id IN NUMBER,
134
135 x_return_status OUT NOCOPY VARCHAR2,
136 x_msg_count OUT NOCOPY NUMBER,
137 x_msg_data OUT NOCOPY VARCHAR2);
138
139 PROCEDURE Get_HistoricalCosts (
140 p_api_version IN NUMBER,
141 p_commit IN VARCHAR2 := FND_API.G_FALSE,
142 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
143 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
144 p_debug IN VARCHAR2 ,
145
146 p_forecast_id IN NUMBER,
147 p_organization_id IN NUMBER,
148 p_wip_entity_id IN NUMBER,
149 p_account_from IN VARCHAR2,
150 p_account_to IN VARCHAR2,
151 --p_acct_period_from IN NUMBER,
152 --p_acct_period_to IN NUMBER,
153
154 p_user_id IN NUMBER,
155 p_request_id IN NUMBER,
156 p_prog_id IN NUMBER,
157 p_prog_app_id IN NUMBER,
158 p_login_id IN NUMBER,
159
160
161 x_hist_cost_tbl OUT NOCOPY eam_wo_relations_tbl_type,
162 x_return_status OUT NOCOPY VARCHAR2,
163 x_msg_count OUT NOCOPY NUMBER,
164 x_msg_data OUT NOCOPY VARCHAR2);
165
166
167 procedure Generate_Forecast(
168 errbuf out NOCOPY varchar2,
169 retcode out NOCOPY varchar2,
170 p_forecast_id IN number);
171 /* This is a private PROCEDURE that extracts a future forecast */
172
173 PROCEDURE extract_future_forecast (
174 p_api_version IN NUMBER,
175 p_commit IN VARCHAR2 := FND_API.G_FALSE,
176 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
177 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
178 p_debug IN VARCHAR2 ,
179
180 p_forecast_rec IN eam_forecasts%ROWTYPE,
181
182
183 --p_acct_period_from IN NUMBER,
184 --p_acct_period_to IN NUMBER,
185
186 p_user_id IN NUMBER,
187 p_request_id IN NUMBER,
188 p_prog_id IN NUMBER,
189 p_prog_app_id IN NUMBER,
190 p_login_id IN NUMBER,
191
192 x_return_status OUT NOCOPY VARCHAR2,
193 x_msg_count OUT NOCOPY NUMBER,
194 x_msg_data OUT NOCOPY VARCHAR2);
195
196
197 PROCEDURE Copy_WDJ_To_Forecast (
198 p_api_version IN NUMBER,
199 p_commit IN VARCHAR2 := FND_API.G_FALSE,
200 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
201 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
202 p_debug IN VARCHAR2 ,
203
204 p_forecast_rec IN eam_forecasts%ROWTYPE,
205 p_wip_id_table IN wo_table_type,
206
207 --p_acct_period_from IN NUMBER,
208 --p_acct_period_to IN NUMBER,
209
210 p_user_id IN NUMBER,
211 p_request_id IN NUMBER,
212 p_prog_id IN NUMBER,
213 p_prog_app_id IN NUMBER,
214 p_login_id IN NUMBER,
215
216 x_return_status OUT NOCOPY VARCHAR2,
217 x_msg_count OUT NOCOPY NUMBER,
218 x_msg_data OUT NOCOPY VARCHAR2);
219
220 PROCEDURE Copy_WDJ_To_Forecast_auto (
221 p_api_version IN NUMBER,
222 p_commit IN VARCHAR2 := FND_API.G_FALSE,
223 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
224 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
225 p_debug IN VARCHAR2 ,
226
227 p_forecast_rec IN eam_forecasts%ROWTYPE,
228 p_wip_id_table IN wo_table_type,
229
230 --p_acct_period_from IN NUMBER,
231 --p_acct_period_to IN NUMBER,
232
233 p_user_id IN NUMBER,
234 p_request_id IN NUMBER,
235 p_prog_id IN NUMBER,
236 p_prog_app_id IN NUMBER,
237 p_login_id IN NUMBER,
238
239 x_return_status OUT NOCOPY VARCHAR2,
240 x_msg_count OUT NOCOPY NUMBER,
241 x_msg_data OUT NOCOPY VARCHAR2);
242
243 procedure delete_work_order(p_forecast_id IN number, p_wip_id IN number);
244
245 procedure delete_forecast(p_forecast_id IN number);
246
247 PROCEDURE Copy_WOR_To_Forecast (
248 p_api_version IN NUMBER,
249 p_commit IN VARCHAR2 := FND_API.G_FALSE,
250 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
251 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
252 p_debug IN VARCHAR2 ,
253
254 p_forecast_rec IN eam_forecasts%ROWTYPE,
255 p_wip_id_table IN wo_table_type,
256
257 --p_acct_period_from IN NUMBER,
258 --p_acct_period_to IN NUMBER,
259
260 p_user_id IN NUMBER,
261 p_request_id IN NUMBER,
262 p_prog_id IN NUMBER,
263 p_prog_app_id IN NUMBER,
264 p_login_id IN NUMBER,
265
266 x_return_status OUT NOCOPY VARCHAR2,
267 x_msg_count OUT NOCOPY NUMBER,
268 x_msg_data OUT NOCOPY VARCHAR2);
269
270 --USAF
271 PROCEDURE Copy_WOR_To_Forecast_auto (
272 p_api_version IN NUMBER,
273 p_commit IN VARCHAR2 := FND_API.G_FALSE,
274 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
275 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
276 p_debug IN VARCHAR2 ,
277
278 p_forecast_rec IN eam_forecasts%ROWTYPE,
279 p_wip_id_table IN wo_table_type,
280
281 --p_acct_period_from IN NUMBER,
282 --p_acct_period_to IN NUMBER,
283
284 p_user_id IN NUMBER,
285 p_request_id IN NUMBER,
286 p_prog_id IN NUMBER,
287 p_prog_app_id IN NUMBER,
288 p_login_id IN NUMBER,
289
290 x_return_status OUT NOCOPY VARCHAR2,
291 x_msg_count OUT NOCOPY NUMBER,
292 x_msg_data OUT NOCOPY VARCHAR2);
293
294 PROCEDURE insert_into_wor_auto(p_wor_table wor_table_type); --USAF
295
296
297 PROCEDURE Copy_WRO_To_Forecast (
298 p_api_version IN NUMBER,
299 p_commit IN VARCHAR2 := FND_API.G_FALSE,
300 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
301 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
302 p_debug IN VARCHAR2 ,
303
304 p_forecast_rec IN eam_forecasts%ROWTYPE,
305 p_wip_id_table IN wo_table_type,
306
307 --p_acct_period_from IN NUMBER,
308 --p_acct_period_to IN NUMBER,
309
313 p_prog_app_id IN NUMBER,
310 p_user_id IN NUMBER,
311 p_request_id IN NUMBER,
312 p_prog_id IN NUMBER,
314 p_login_id IN NUMBER,
315
316 x_return_status OUT NOCOPY VARCHAR2,
317 x_msg_count OUT NOCOPY NUMBER,
318 x_msg_data OUT NOCOPY VARCHAR2);
319
320 --USAF
321
322 PROCEDURE Copy_WRO_To_Forecast_auto (
323 p_api_version IN NUMBER,
324 p_commit IN VARCHAR2 := FND_API.G_FALSE,
325 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
326 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
327 p_debug IN VARCHAR2 ,
328
329 p_forecast_rec IN eam_forecasts%ROWTYPE,
330 p_wip_id_table IN wo_table_type,
331
332 --p_acct_period_from IN NUMBER,
333 --p_acct_period_to IN NUMBER,
334
335 p_user_id IN NUMBER,
336 p_request_id IN NUMBER,
337 p_prog_id IN NUMBER,
338 p_prog_app_id IN NUMBER,
339 p_login_id IN NUMBER,
340
341 x_return_status OUT NOCOPY VARCHAR2,
342 x_msg_count OUT NOCOPY NUMBER,
343 x_msg_data OUT NOCOPY VARCHAR2);
344
345 PROCEDURE insert_into_wro_auto(p_wro_table wro_table_type); --USAF
346
347 PROCEDURE Copy_WO_To_Forecast (
348 p_api_version IN NUMBER,
349 p_commit IN VARCHAR2 := FND_API.G_FALSE,
350 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
351 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
352 p_debug IN VARCHAR2 ,
353
354 p_forecast_rec IN eam_forecasts%ROWTYPE,
355 p_wip_id_table IN wo_table_type,
356
357 --p_acct_period_from IN NUMBER,
358 --p_acct_period_to IN NUMBER,
359
360 p_user_id IN NUMBER,
361 p_request_id IN NUMBER,
362 p_prog_id IN NUMBER,
363 p_prog_app_id IN NUMBER,
364 p_login_id IN NUMBER,
365
366 x_return_status OUT NOCOPY VARCHAR2,
367 x_msg_count OUT NOCOPY NUMBER,
368 x_msg_data OUT NOCOPY VARCHAR2);
369
370 --USAF
371 PROCEDURE Copy_WO_To_Forecast_auto (
372 p_api_version IN NUMBER,
373 p_commit IN VARCHAR2 := FND_API.G_FALSE,
374 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
375 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
376 p_debug IN VARCHAR2 ,
377
378 p_forecast_rec IN eam_forecasts%ROWTYPE,
379 p_wip_id_table IN wo_table_type,
380
381 --p_acct_period_from IN NUMBER,
382 --p_acct_period_to IN NUMBER,
383
384 p_user_id IN NUMBER,
385 p_request_id IN NUMBER,
386 p_prog_id IN NUMBER,
387 p_prog_app_id IN NUMBER,
388 p_login_id IN NUMBER,
389
390 x_return_status OUT NOCOPY VARCHAR2,
391 x_msg_count OUT NOCOPY NUMBER,
392 x_msg_data OUT NOCOPY VARCHAR2);
393
394
395 PROCEDURE Copy_CEBBA_To_Forecast (
396 p_api_version IN NUMBER,
397 p_commit IN VARCHAR2 := FND_API.G_FALSE,
398 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
399 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
400 p_debug IN VARCHAR2 ,
401
402 p_forecast_rec IN eam_forecasts%ROWTYPE,
403 p_wip_id_table IN wo_table_type,
404
405 --p_acct_period_from IN NUMBER,
406 --p_acct_period_to IN NUMBER,
407
408 p_user_id IN NUMBER,
409 p_request_id IN NUMBER,
410 p_prog_id IN NUMBER,
411 p_prog_app_id IN NUMBER,
412 p_login_id IN NUMBER,
413
414 x_return_status OUT NOCOPY VARCHAR2,
415 x_msg_count OUT NOCOPY NUMBER,
416 x_msg_data OUT NOCOPY VARCHAR2);
417
418 PROCEDURE Copy_WEDI_To_Forecast (
419 p_api_version IN NUMBER,
420 p_commit IN VARCHAR2 := FND_API.G_FALSE,
421 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
422 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
423 p_debug IN VARCHAR2 ,
424
425 p_forecast_rec IN eam_forecasts%ROWTYPE,
426 p_wip_id_table IN wo_table_type,
427
428 --p_acct_period_from IN NUMBER,
429 --p_acct_period_to IN NUMBER,
430
431 p_user_id IN NUMBER,
435 p_login_id IN NUMBER,
432 p_request_id IN NUMBER,
433 p_prog_id IN NUMBER,
434 p_prog_app_id IN NUMBER,
436
437 x_return_status OUT NOCOPY VARCHAR2,
438 x_msg_count OUT NOCOPY NUMBER,
439 x_msg_data OUT NOCOPY VARCHAR2);
440
441
442 --USAF
443 PROCEDURE Copy_WEDI_To_Forecast_auto (
444 p_api_version IN NUMBER,
445 p_commit IN VARCHAR2 := FND_API.G_FALSE,
446 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
447 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
448 p_debug IN VARCHAR2 ,
449
450 p_forecast_rec IN eam_forecasts%ROWTYPE,
451 p_wip_id_table IN wo_table_type,
452
453 --p_acct_period_from IN NUMBER,
454 --p_acct_period_to IN NUMBER,
455
456 p_user_id IN NUMBER,
457 p_request_id IN NUMBER,
458 p_prog_id IN NUMBER,
459 p_prog_app_id IN NUMBER,
460 p_login_id IN NUMBER,
461
462 x_return_status OUT NOCOPY VARCHAR2,
463 x_msg_count OUT NOCOPY NUMBER,
464 x_msg_data OUT NOCOPY VARCHAR2);
465
466 PROCEDURE insert_into_WEDI_auto(p_wedi_table wedi_table_type); --USAF
467
468 PROCEDURE Populate_Test_Data(p_forecast_id IN NUMBER);
469 FUNCTION get_wip_table(p_forecast_rec eam_forecasts%rowtype)
470 RETURN wo_table_type;
471
472 FUNCTION get_asset_cursor(p_forecast_rec eam_forecasts%rowtype)
473 RETURN forecast_asset_cursor_type;
474
475 FUNCTION get_asset_cursor(p_organization_id IN NUMBER,p_asset_number_from IN VARCHAR2,
476 p_asset_number_to IN VARCHAR2, p_serial_number_from IN VARCHAR2,
477 p_serial_number_to IN VARCHAR2 , p_asset_group_from IN VARCHAR2,
478 p_asset_group_to IN VARCHAR2 , p_area_from IN VARCHAR2, p_area_to IN VARCHAR2)
479 RETURN forecast_asset_cursor_type;
480
481 FUNCTION get_asset_query(p_forecast_rec eam_forecasts%rowtype)
482 RETURN VARCHAR2;
483
484 FUNCTION get_asset_query(p_organization_id IN NUMBER,p_asset_number_from IN VARCHAR2,
485 p_asset_number_to IN VARCHAR2, p_serial_number_from IN VARCHAR2,
486 p_serial_number_to IN VARCHAR2 , p_asset_group_from IN VARCHAR2,
487 p_asset_group_to IN VARCHAR2 , p_area_from IN VARCHAR2, p_area_to IN VARCHAR2)
488 RETURN VARCHAR2;
489
490 PROCEDURE debug(l_msg IN VARCHAR2, l_level IN NUMBER := 1);
491
492 FUNCTION getForecastXml(p_forecast_id NUMBER)return CLOB;
493 END;
[Home] [Help]