[Home] [Help]
PACKAGE BODY: APPS.EAM_FORECAST_PVT
Source
1 PACKAGE BODY "EAM_FORECAST_PVT" AS
2 /* $Header: EAMVFORB.pls 120.24.12020000.8 2012/12/07 10:14:38 srkotika ship $ */
3 G_PKG_NAME CONSTANT VARCHAR2(30) := 'EAM_FOREACST_PVT';
4 G_LOG_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
5 G_FILE_NAME CONSTANT VARCHAR2(30) := 'EAMVFORB.pls';
6 -- TODO: Propagate OA required parameters back to the top level
7 -- extract_forecast ....eg api_version, status...is this required?
8
9 --------------------------------------------------------------------------
10 -- PROCEDURE --
11 -- Historical_WO_Costs --
12 -- --
13 -- DESCRIPTION --
14 -- This API is part of the backend support for Maintenance and --
15 -- Budgeting project. --
16 -- --
17 -- This API determines the historical cost and account information of a --
18 -- work order and passes the record back to the calling program. --
19 -- --
20 -- It is called from the Maintenance and Budgeting engine, to detemine --
21 -- costs of Historical Type Forecats. The calling program will insert --
22 -- values passed by this API into EAM_FORECASTS_CEBBA table. --
23 -- --
24 -- PURPOSE: --
25 -- Oracle Applications Rel 12 --
26 -- --
27 -- HISTORY: --
28 -- 04/20/05 Anju Gupta Created --
29 ----------------------------------------------------------------------------
30
31 PROCEDURE Get_HistoricalCosts (
32 p_api_version IN NUMBER,
33 p_commit IN VARCHAR2 := FND_API.G_FALSE,
34 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
35 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
36 p_debug IN VARCHAR2 ,
37
38 p_forecast_id IN NUMBER,
39 p_organization_id IN NUMBER,
40 p_wip_entity_id IN NUMBER,
41 p_account_from IN VARCHAR2,
42 p_account_to IN VARCHAR2,
43 --p_acct_period_from IN NUMBER,
44 --p_acct_period_to IN NUMBER,
45
46 p_user_id IN NUMBER,
47 p_request_id IN NUMBER,
48 p_prog_id IN NUMBER,
49 p_prog_app_id IN NUMBER,
50 p_login_id IN NUMBER,
51
52
53 x_hist_cost_tbl OUT NOCOPY eam_wo_relations_tbl_type,
54 x_return_status OUT NOCOPY VARCHAR2,
55 x_msg_count OUT NOCOPY NUMBER,
56 x_msg_data OUT NOCOPY VARCHAR2) IS
57
58 l_api_name CONSTANT VARCHAR2(30) := 'Get_HistoricalCosts';
59 l_api_version CONSTANT NUMBER := 1.0;
60
61 l_msg_count NUMBER := 0;
62 l_msg_data VARCHAR2(8000) := '';
63
64
65 l_stmt_num NUMBER := 0;
66 l_error_num NUMBER := 0;
67 l_err_code VARCHAR2(240) := '';
68 l_err_msg VARCHAR2(240) := '';
69
70 l_forecast_rec eam_forecast_rec_type;
71 l_def_eam_cost_element_id NUMBER := 0;
72 l_cnt NUMBER := 0;
73
74 CURSOR c_efcebba (p_def_eam_cost_element_id number) IS
75
76 SELECT
77 wepb.period_set_name,
78 wepb.period_name,
79 wepb.acct_period_id,
80 wepb.operations_dept_id,
81 cdv.operation_seq_num,
82 wepb.maint_cost_category,
83 wepb.owning_dept_id,
84 cdv.reference_account,
85 decode(cdv.cost_element,
86 'Material', 1,
87 'Resource', 3,
88 'Material Overhead', 2,
89 'Outside Processing', 5,
90 'Overhead', 4) mfg_cost_element,
91 decode(nvl(cdv.resource_seq_num, -1),
92 -1, 3,
93 decode((SELECT resource_type
94 FROM bom_resources br, wip_operation_resources wor
95 WHERE br.resource_id = wor.resource_id
96 AND wor.wip_ENTITY_ID = cdv.wip_entity_id
97 AND wor.operation_seq_num = cdv.operation_seq_num
98 AND wor.resource_seq_num = cdv.resource_seq_num),
99 1, 1, 2, 2, p_def_eam_cost_element_id)
100 ) as txn_type,
101 oap.period_start_date,
102 oap.period_num,
103 oap.period_year,
104 (cdv.base_transaction_value)as value
105 FROM cst_distribution_lite_v cdv, wip_eam_period_balances wepb, org_acct_periods oap,
106 mfg_lookups mf
107 WHERE cdv.operation_seq_num is not null
108 and mf.lookup_type = 'CST_ACCOUNTING_LINE_TYPE'
109 and mf.lookup_code in (7,8)
110 and mf.meaning = cdv.line_type_name
111 AND wepb.wip_entity_id = cdv.wip_entity_id
112 AND wepb.operation_seq_num = cdv.operation_seq_num
113 AND wepb.acct_period_id = cdv.acct_period_id
114 AND oap.acct_period_id = cdv.acct_period_id
115 AND oap.organization_id = cdv.organization_id
116 AND cdv.wip_entity_id = p_wip_entity_id
117 --AND cdv.organization_id = p_organization_id Bug#5632148
118 AND (p_account_from is null or
119 cdv.reference_account in (
120 SELECT code_combination_id from gl_code_combinations glcc
121 where fnd_flex_ext.get_segs('SQLGL', 'GL#',
122 glcc.chart_of_accounts_id, glcc.code_combination_id) >= p_account_from
123 AND fnd_flex_ext.get_segs('SQLGL', 'GL#', glcc.chart_of_accounts_id,
124 glcc.code_combination_id) <= p_account_to))
125 UNION ALL
126 SELECT
127 oap.period_set_name,
128 oap.period_name,
129 oap.acct_period_id,
130 null,
131 cdv.operation_seq_num,
132 ep.def_maint_cost_category,
133 wdj.owning_department,
134 cdv.reference_account,
135 decode(cdv.cost_element,
136 'Material', 1,
137 'Resource', 3,
138 'Material Overhead', 2,
139 'Outside Processing', 5,
140 'Overhead', 4) mfg_cost_element,
141 null txn_type,
142 oap.period_start_date,
143 oap.period_num,
144 oap.period_year,
145 (cdv.base_transaction_value) value
146 FROM cst_distribution_lite_v cdv, org_acct_periods oap, wip_discrete_jobs wdj,
147 wip_eam_parameters ep, mfg_lookups mf
148 WHERE ep.organization_id = cdv.organization_id
149 and cdv.wip_entity_id = wdj.wip_entity_id
150 and cdv.organization_id = wdj.organization_id
151 and cdv.operation_seq_num is null
152 and mf.lookup_type = 'CST_ACCOUNTING_LINE_TYPE'
153 and mf.lookup_code in (7,8)
154 and mf.meaning = cdv.line_type_name
155 AND oap.acct_period_id = cdv.acct_period_id
156 AND oap.organization_id = cdv.organization_id
157 AND cdv.wip_entity_id = p_wip_entity_id
158 --AND cdv.organization_id = p_organization_id Bug#5632148
159 AND (p_account_from is null or
160 cdv.reference_account in (
161 SELECT code_combination_id from gl_code_combinations glcc
162 where fnd_flex_ext.get_segs('SQLGL', 'GL#',
163 glcc.chart_of_accounts_id, glcc.code_combination_id) >= p_account_from
164 AND fnd_flex_ext.get_segs('SQLGL', 'GL#', glcc.chart_of_accounts_id,
165 glcc.code_combination_id) <= p_account_to));
166
167 begin
168
169 -------------------------------------------------------------------------
170 -- Establish savepoint
171 -------------------------------------------------------------------------
172
173 SAVEPOINT HistoricalCosts_PVT;
174
175 -------------------------------------------------------------------------
176 -- standard call to check for call compatibility
177 -------------------------------------------------------------------------
178 IF NOT fnd_api.compatible_api_call (
179 l_api_version,
180 p_api_version,
181 l_api_name,
182 G_PKG_NAME ) then
183
184 RAISE fnd_api.g_exc_unexpected_error;
185
186 END IF;
187
188 ---------------------------------------------------------------------------
189 -- Initialize message list if p_init_msg_list is set to TRUE
190 ---------------------------------------------------------------------------
191
192 IF FND_API.to_Boolean(p_init_msg_list) THEN
193 FND_MSG_PUB.initialize;
194 END IF;
195
196
197 -------------------------------------------------------------------------
198 -- initialize api return status to success
199 -------------------------------------------------------------------------
200
201 l_stmt_num := 10;
202 x_return_status := fnd_api.g_ret_sts_success;
203
204
205 -------------------------------------------------------------------------
206 /*-- Determine the dates for the periods of interest
207 -------------------------------------------------------------------------
208
209 select oap.period_start_date
210 into l_period_from
211 from org_acct_periods oap
212 where organization_id = p_org_id
213 and acct_period_id = p_acct_period_from;
214
215 select nvl(oap.period_close_date, oap.SCHEDULE_CLOSE_DATE)
216 into l_period_to
217 from org_acct_periods oap
218 where organization_id = p_org_id
219 and acct_period_id = p_acct_period_to; */
220
221 -------------------------------------------------------------------------
222 -- Get the Default EAM cost element id for this organization
223 -------------------------------------------------------------------------
224 l_stmt_num := 20;
225
226 SELECT def_eam_cost_element_id
227 INTO l_def_eam_cost_element_id
228 FROM wip_eam_parameters
229 WHERE organization_id = p_organization_id;
230
231 -------------------------------------------------------------------------
232 -- Initialize common variables
233 -------------------------------------------------------------------------
234
235 l_stmt_num := 30;
236
237 l_forecast_rec.WIP_ENTITY_ID := p_wip_entity_id;
238 l_forecast_rec.ORGANIZATION_ID := p_organization_id;
239 l_forecast_rec.forecast_id := p_forecast_id;
240
241 l_forecast_rec.LAST_UPDATE_DATE := sysdate;
242 l_forecast_rec.LAST_UPDATED_BY := p_user_id;
243 l_forecast_rec.CREATION_DATE := sysdate;
244 l_forecast_rec.CREATED_BY := p_user_id;
245 l_forecast_rec.LAST_UPDATE_LOGIN := p_user_id;
246 l_forecast_rec.REQUEST_ID := p_request_id;
247 l_forecast_rec.PROGRAM_APPLICATION_ID := p_prog_app_id;
248 l_forecast_rec.PROGRAM_ID := p_prog_id;
249 l_forecast_rec.PROGRAM_UPDATE_DATE := sysdate;
250
251 l_cnt := 1;
252
253 -------------------------------------------------------------------------
254 -- Get the Historical Cost Information for the Work Order
255 -------------------------------------------------------------------------
256
257 l_stmt_num := 40;
258
259
260 FOR c_efcebba_rec IN c_efcebba (l_def_eam_cost_element_id)
261 LOOP
262
263
264 l_forecast_rec.PERIOD_SET_NAME := c_efcebba_rec.period_set_name;
265 l_forecast_rec.PERIOD_NAME := c_efcebba_rec.period_name;
266
267 l_forecast_rec.ACCT_PERIOD_ID := c_efcebba_rec.acct_period_id;
268 l_forecast_rec.OPERATIONS_DEPT_ID := c_efcebba_rec.operations_dept_id;
269 l_forecast_rec.OPERATION_SEQ_NUM := c_efcebba_rec.operation_seq_num;
270 l_forecast_rec.MAINT_COST_CATEGORY := c_efcebba_rec.maint_cost_category;
271 l_forecast_rec.OWNING_DEPT_ID := c_efcebba_rec.owning_dept_id;
272 l_forecast_rec.ACCT_VALUE := c_efcebba_rec.value;
273 l_forecast_rec.TXN_TYPE := c_efcebba_rec.txn_type;
274
275
276 l_forecast_rec.PERIOD_START_DATE := c_efcebba_rec.period_start_date;
277 l_forecast_rec.CCID := c_efcebba_rec.reference_account;
278 l_forecast_rec.MFG_COST_ELEMENT_ID := c_efcebba_rec.mfg_cost_element;
279 l_forecast_rec.PERIOD_YEAR := c_efcebba_rec.period_year;
280 l_forecast_rec.PERIOD_NUM := c_efcebba_rec.period_num;
281
282 x_hist_cost_tbl(l_cnt) := l_forecast_rec;
283
284 l_cnt := l_cnt + 1;
285
286 END LOOP;
287
288 l_stmt_num := 50;
289
290 ---------------------------------------------------------------------------
291 -- Standard Call to get message count and if count = 1, get message info
292 ---------------------------------------------------------------------------
293
294 FND_MSG_PUB.Count_And_Get (
295 p_count => x_msg_count,
296 p_data => x_msg_data );
297
298 EXCEPTION
299
300 WHEN fnd_api.g_exc_error THEN
301 x_return_status := fnd_api.g_ret_sts_error;
302
303 -- Get message count and data
304 fnd_msg_pub.count_and_get
305 ( p_count => x_msg_count
306 , p_data => x_msg_data
307 );
308 --
309 WHEN fnd_api.g_exc_unexpected_error THEN
310 x_return_status := fnd_api.g_ret_sts_unexp_error ;
311
312 -- Get message count and data
313 fnd_msg_pub.count_and_get
314 ( p_count => x_msg_count
315 , p_data => x_msg_data
316 );
317
318 WHEN OTHERS THEN
319 x_return_status := fnd_api.g_ret_sts_unexp_error ;
320 --
321 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
322 THEN
323 fnd_msg_pub.add_exc_msg
324 ( 'EAM_HistoricalCosts_PVT'
325 , 'Get_HistoricalCosts : l_stmt_num - '||to_char(l_stmt_num)
326 );
327
328 END IF;
329 -- Get message count and data
330 fnd_msg_pub.count_and_get
331 ( p_count => x_msg_count
332 , p_data => x_msg_data
333 );
334
335
336 end Get_HistoricalCosts;
337
338 procedure delete_work_order(p_forecast_id IN number, p_wip_id IN number)IS
339
340 BEGIN
341 delete from eam_forecast_wdj where
342 forecast_id = p_forecast_id
343 AND wip_entity_id = p_wip_id;
344
345 delete from eam_forecast_wo
346 where forecast_id = p_forecast_id
347 AND wip_entity_id = p_wip_id;
348
349 delete from eam_forecast_wor
350 where forecast_id = p_forecast_id
351 AND wip_entity_id = p_wip_id;
352
353 delete from eam_forecast_wro
354 where forecast_id = p_forecast_id
355 AND wip_entity_id = p_wip_id;
356
357 delete from eam_forecast_wedi
358 where forecast_id = p_forecast_id
359 AND wip_entity_id = p_wip_id;
360
361 delete from eam_forecast_cebba
362 where forecast_id = p_forecast_id
363 AND wip_entity_id = p_wip_id;
364
365 end delete_work_order;
366
367 procedure delete_forecast(p_forecast_id IN number)IS
368
369 BEGIN
370
371 delete from eam_forecasts where
372 forecast_id = p_forecast_id;
373
374 delete from eam_forecast_wdj where
375 forecast_id = p_forecast_id;
376
377 delete from eam_forecast_wo
378 where forecast_id = p_forecast_id;
379
380 delete from eam_forecast_wor
381 where forecast_id = p_forecast_id;
382
383 delete from eam_forecast_wro
384 where forecast_id = p_forecast_id;
385
386 delete from eam_forecast_wedi
387 where forecast_id = p_forecast_id;
388
389 delete from eam_forecast_cebba
390 where forecast_id = p_forecast_id;
391
392 end delete_forecast;
393
394 procedure delete_forecast_data(p_forecast_id IN number)IS
395
396 BEGIN
397
398 delete from eam_forecast_wdj where
399 forecast_id = p_forecast_id;
400
401 delete from eam_forecast_wo
402 where forecast_id = p_forecast_id;
403
404 delete from eam_forecast_wor
405 where forecast_id = p_forecast_id;
406
407 delete from eam_forecast_wro
408 where forecast_id = p_forecast_id;
409
410 delete from eam_forecast_wedi
411 where forecast_id = p_forecast_id;
412
413 delete from eam_forecast_cebba
414 where forecast_id = p_forecast_id;
415
416 end delete_forecast_data;
417
418 procedure Generate_Forecast(
419 errbuf out NOCOPY varchar2,
420 retcode out NOCOPY varchar2,
421 p_forecast_id IN number) is
422
423 l_msg_count NUMBER := 0;
424 l_msg_data VARCHAR2(8000) := '';
425 l_return_status VARCHAR2(2000);
426 l_forecast_id NUMBER;
427 BEGIN
428
429 delete_forecast_data(p_forecast_id);
430 commit;
431
432 Extract_Forecast(
433 p_api_version => 1.0,
434 p_commit => FND_API.G_TRUE,
435 p_debug => 'N',
436
437 p_forecast_id => p_forecast_id,
438 x_return_status => l_return_status,
439 x_msg_count => l_msg_count,
440 x_msg_data => l_msg_data);
441
442 update eam_forecasts
443 set completion_date = SYSDATE
444 where forecast_id = p_forecast_id;
445
446
447 END Generate_Forecast;
448
449
450
451 /* This is a private PROCEDURE that extracts a historical forecast */
452
453 PROCEDURE debug(l_msg IN VARCHAR2, l_level IN NUMBER := 1)IS
454 l_n NUMBER;
455 l_log_level CONSTANT NUMBER := fnd_log.g_current_runtime_level;
456 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level ;
457 l_sLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_statement >= l_log_level;
458
459 BEGIN
460 IF(l_level >= 1)
461 THEN
462 IF( l_sLog ) THEN
463 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
464 'eam.plsql.EAM_FORECAST_PVT.extract_forecast',l_msg);
465 -- dbms_OUTPUT.put_line('DEBUG:: ' || l_msg);
466 l_n := 1;
467 END IF;
468 END IF;
469 END debug;
470
471
472 FUNCTION get_asset_cursor(p_forecast_rec eam_forecasts%rowtype)
473 RETURN forecast_asset_cursor_type
474 IS
475
476 BEGIN
477 return get_asset_cursor(p_forecast_rec.organization_id,
478 p_forecast_rec.asset_num_from,
479 p_forecast_rec.asset_num_to,
480 p_forecast_rec.asset_serial_num_from,
481 p_forecast_rec.asset_serial_num_to,
482 p_forecast_rec.asset_group_from,
483 p_forecast_rec.asset_group_to,
484 p_forecast_rec.area_from,
485 p_forecast_rec.area_to);
486 END get_asset_cursor;
487
488
489 FUNCTION get_wip_table(p_forecast_rec eam_forecasts%rowtype)
490 RETURN wo_table_type
491 IS
492 --
493 TYPE wo_cursor IS REF CURSOR;
494 TYPE wo_table IS table of number index by binary_integer;
495 l_wip_cursor wo_cursor;
496 l_wip_table wo_table_type;
497 l_start_date DATE;
498 l_end_date DATE;
499 l_asset_query BOOLEAN;
500 l_work_order_type_from VARCHAR2(30);
501 l_work_order_type_to VARCHAR2(30);
502
503
504 -- worst case, historical must include asset information/query
505 l_sql VARCHAR2(8010);
506
507
508 l_sql_select VARCHAR2(1000);
509 l_sql_from VARCHAR2(4500);
510 l_sql_where VARCHAR2(2500);
511
512
513 BEGIN
514 debug('Getting wo table');
515 --_OUTPUT.enable(1000000);
516 -- Get the start date and end date information for the forecast
517
518 l_asset_query := false;
519
520 SELECT glp.start_date
521 INTO l_start_date
522 FROM gl_periods glp
523 WHERE glp.period_name = p_forecast_rec.period_from
524 AND glp.period_set_name = p_forecast_rec.period_set_name_from;
525
526 debug('Start date is ' || l_start_date);
527
528 SELECT glp.end_date
529 INTO l_end_date
530 FROM gl_periods glp
531 WHERE glp.period_name = p_forecast_rec.period_to
532 AND glp.period_set_name = p_forecast_rec.period_set_name_to;
533
534 debug('End date is ' || l_end_date);
535
536 l_sql_select := 'SELECT wdj.wip_entity_id ';
537 l_sql_from := 'FROM wip_discrete_jobs wdj ';
538 l_sql_where := 'WHERE ';
539
540 debug('Select stmt: ' || l_sql_select);
541 debug('FROM stmt: ' || l_sql_from);
542 debug('WHERE stmt: ' || l_sql_where);
543
544
545
546 IF p_forecast_rec.include_transacted = 'Y'
547 THEN
548 -- A historical forecast, must be driven by assets if transacted
549 --flag has been checked
550 l_sql_where := l_sql_where || ':organization_id IS NOT NULL ' ||
551 'AND :creation_date IS NOT NULL ';
552
553
554 ELSE
555 --Default case, organization_id is first selection criteria
556 l_sql_where := l_sql_where || 'wdj.organization_id = :organization_id ';
557 IF p_forecast_rec.forecast_type = 4
558 THEN
559 l_sql_where := l_sql_where || 'AND wdj.creation_date >= :creation_date ';
560 ELSE
561 l_sql_where := l_sql_where || 'AND :creation_date IS NOT NULL ';
562 END IF;
563 END IF;
564
565 --Add the PM criteria
566
567 -- Use asset criteria only if specified, OR If include transacted
568 IF (p_forecast_rec.asset_num_from IS NOT NULL OR
569 p_forecast_rec.asset_num_to IS NOT NULL OR
570 p_forecast_rec.asset_group_from IS NOT NULL OR
571 p_forecast_rec.asset_group_to IS NOT NULL OR
572 p_forecast_rec.asset_serial_num_from IS NOT NULL OR
573 p_forecast_rec.asset_serial_num_to IS NOT NULL OR
574 p_forecast_rec.include_transacted = 'Y') AND
575 p_forecast_rec.forecast_type <> 4
576 THEN
577 l_sql_from := l_sql_from || ',(' ||
578 get_asset_query(p_forecast_rec) || ') assets ';
579 l_sql_where := l_sql_where ||
580 'AND wdj.maintenance_object_id = assets.asset_id ' ||
581 'AND wdj.maintenance_object_type = assets.asset_type ';
582 l_asset_query := true;
583 -- Must be an eAM wip entry
584 ELSE
585
586 l_sql_where := l_sql_where ||
587 'AND wdj.maintenance_object_id IS NOT NULL ';
588 END IF;
589
590 IF p_forecast_rec.forecast_type IN(1,2,4)
591 THEN
592
593
594 IF p_forecast_rec.forecast_type = 2
595 THEN
596 l_sql_where := l_sql_where ||
597 'AND wdj.PM_SCHEDULE_ID IS NULL ';
598 ELSE
599 l_sql_where := l_sql_where ||
600 'AND wdj.PM_SCHEDULE_ID IS NOT NULL ';
601 END IF;
602
603 END IF;
604
605
606 --Add start date and end date criteria
607
608 l_sql_where := l_sql_where ||
609 'AND wdj.scheduled_completion_date >= :start_date ' ||
610 'AND wdj.scheduled_start_date <= :end_date ';
611
612 -- WO Number Criteria
613
614 IF p_forecast_rec.work_order_from IS NOT NULL AND
615 p_forecast_rec.work_order_to IS NOT NULL
616 THEN
617
618 l_sql_from := l_sql_from ||
619 ',wip_entities we ';
620 l_sql_where := l_sql_where ||
621 'AND wdj.wip_entity_id = we.wip_entity_id ' ||
622 'AND we.wip_entity_name >= :wo_from ' ||
623 'AND we.wip_entity_name <= :wo_to ';
624 ELSE
625
626 l_sql_where := l_sql_where ||
627 'AND (:wo_from IS NULL OR :wo_to IS NULL) ';
628
629
630 END IF;
631
632
633 -- WO Type Criteria
634
635 IF p_forecast_rec.work_order_type_from IS NOT NULL AND
636 p_forecast_rec.work_order_type_to IS NOT NULL
637 THEN
638 select lookup_code
639 INTO l_work_order_type_from
640 from fnd_lookup_values
641 where LANGUAGE = userenv('LANG')
642 AND view_application_id = 700
643 AND security_group_id = 0
644 AND lookup_type = 'WIP_EAM_WORK_ORDER_TYPE'
645 AND enabled_flag = 'Y'
646 and meaning = p_forecast_rec.work_order_type_from;
647
648 select lookup_code
649 INTO l_work_order_type_to
650 from fnd_lookup_values
651 where LANGUAGE = userenv('LANG')
652 AND view_application_id = 700
653 AND security_group_id = 0
654 AND lookup_type = 'WIP_EAM_WORK_ORDER_TYPE'
655 AND enabled_flag = 'Y'
656 and meaning = p_forecast_rec.work_order_type_to;
657
658
659 l_sql_where := l_sql_where ||
660 'AND wdj.work_order_type >= :wo_type_from ' ||
661 'AND wdj.work_order_type <= :wo_type_to ';
662 ELSE
663
664 l_sql_where := l_sql_where ||
665 'AND (:wo_type_from IS NULL OR :wo_type_to IS NULL) ';
666
667 END IF;
668
669
670 -- WAC criteria
671
672 IF p_forecast_rec.wip_acct_class_from IS NOT NULL AND
673 p_forecast_rec.wip_acct_class_to IS NOT NULL
674 THEN
675 l_sql_where := l_sql_where ||
676 'AND wdj.class_code >= :wac_from ' ||
677 'AND wdj.class_code <= :wac_to ';
678 ELSE
679 l_sql_where := l_sql_where ||
680 'AND (:wac_from IS NULL OR :wac_to IS NULL) ';
681 END IF;
682
683
684 -- Activity Criteria
685
686 IF p_forecast_rec.activity_from IS NOT NULL AND
687 p_forecast_rec.activity_to IS NOT NULL
688 THEN
689 l_sql_from := l_sql_from ||
690 ',mtl_system_items_b_kfv msi ';
691 l_sql_where := l_sql_where ||
692 'AND msi.organization_id = wdj.organization_id ' ||
693 'AND msi.inventory_item_id = wdj.primary_item_id ' ||
694 'AND msi.eam_item_type(+) = 2 ' ||
695 'AND msi.concatenated_segments >= :activity_from ' ||
696 'AND msi.concatenated_segments <= :activity_to ';
697 ELSE
698 l_sql_where := l_sql_where ||
699 'AND (:activity_from IS NULL OR :activity_to IS NULL) ';
700 END IF;
701
702
703 -- Department criteria
704
705 IF p_forecast_rec.department_from IS NOT NULL AND
706 p_forecast_rec.department_to IS NOT NULL
707 THEN
708 l_sql_from := l_sql_from ||
709 ',bom_departments bd ';
710 l_sql_where := l_sql_where ||
711 'AND bd.department_id (+) = wdj.owning_department ' ||
712 'AND bd.department_code >= :department_from ' ||
713 'AND bd.department_code <= :department_to ';
714 ELSE
715 l_sql_where := l_sql_where ||
716 'AND (:department_from IS NULL OR :department_to IS NULL) ';
717 END IF;
718
719 -- Project criteria
720
721 IF p_forecast_rec.project_from IS NOT NULL AND
722 p_forecast_rec.project_to IS NOT NULL
723 THEN
724 l_sql_from := l_sql_from ||
725 ',pa_projects pp ';
726 l_sql_where := l_sql_where ||
727 'AND pp.project_id (+) = wdj.project_id ' ||
728 'AND pp.name >= :project_from ' ||
729 'AND pp.name <= :project_to ';
730 ELSE
731 l_sql_where := l_sql_where ||
732 'AND (:project_from IS NULL OR :project_to IS NULL) ';
733 END IF;
734
735
736 -- Concatenate the clauses
737
738 l_sql := l_sql_select || l_sql_from || l_sql_where;
739
740 debug('Inserting sql');
741 /*
742 dbms_output.put_line(SUBSTR(l_sql,1,250));
743 dbms_output.put_line(SUBSTR(l_sql,250,250));
744 dbms_output.put_line(SUBSTR(l_sql,500,250));
745 dbms_output.put_line(SUBSTR(l_sql,750,250));
746 */
747
748 IF l_asset_query = true
749 THEN
750
751 debug('Opening cursor');
752
753 OPEN l_wip_cursor for l_sql USING
754
755 p_forecast_rec.organization_id,
756 p_forecast_rec.asset_num_from,
757 p_forecast_rec.asset_num_to,
758 p_forecast_rec.asset_serial_num_from,
759 p_forecast_rec.asset_serial_num_to,
760 p_forecast_rec.asset_group_from,
761 p_forecast_rec.asset_group_to,
762 p_forecast_rec.area_from,
763 p_forecast_rec.area_to,
764 p_forecast_rec.organization_id,
765 p_forecast_rec.asset_group_from,
766 p_forecast_rec.asset_group_to,
767
768 p_forecast_rec.organization_id,
769 p_forecast_rec.creation_date,
770 l_start_date,
771 l_end_date,
772 p_forecast_rec.work_order_from,
773 p_forecast_rec.work_order_to,
774 l_work_order_type_from,
775 l_work_order_type_to,
776 p_forecast_rec.wip_acct_class_from,
777 p_forecast_rec.wip_acct_class_to,
778 p_forecast_rec.activity_from,
779 p_forecast_rec.activity_to,
780 p_forecast_rec.department_from,
781 p_forecast_rec.department_to,
782 p_forecast_rec.project_from,
783 p_forecast_rec.project_to;
784
785
786
787 FETCH l_wip_cursor BULK COLLECT INTO l_wip_table;
788
789
790 ELSE
791 --Otherwise, the asset criteria has already been used before calling
792 --the pm engine
793
794 debug('Opening non-transact cursor');
795
796 OPEN l_wip_cursor for l_sql USING
797 p_forecast_rec.organization_id,
798 p_forecast_rec.creation_date,
799 l_start_date,
800 l_end_date,
801 p_forecast_rec.work_order_from,
802 p_forecast_rec.work_order_to,
803 l_work_order_type_from,
804 l_work_order_type_to,
805 p_forecast_rec.wip_acct_class_from,
806 p_forecast_rec.wip_acct_class_to,
807 p_forecast_rec.activity_from,
808 p_forecast_rec.activity_to,
809 p_forecast_rec.department_from,
810 p_forecast_rec.department_to,
811 p_forecast_rec.project_from,
812 p_forecast_rec.project_to;
813
814 FETCH l_wip_cursor BULK COLLECT INTO l_wip_table;
815 END IF;
816
817 debug('DONE Getting wo table: ' || l_wip_table.COUNT );
818 return l_wip_table;
819
820 END get_wip_table;
821
822
823
824 FUNCTION get_asset_cursor(p_organization_id IN NUMBER,p_asset_number_from IN VARCHAR2,
825 p_asset_number_to IN VARCHAR2, p_serial_number_from IN VARCHAR2,
826 p_serial_number_to IN VARCHAR2 , p_asset_group_from IN VARCHAR2,
827 p_asset_group_to IN VARCHAR2 , p_area_from IN VARCHAR2, p_area_to IN VARCHAR2)
828 RETURN forecast_asset_cursor_type
829 IS
830 l_asset_cursor forecast_asset_cursor_type;
831 l_asset_query VARCHAR2(4010);
832 BEGIN
833 l_asset_query := get_asset_query(p_organization_id,
834 p_asset_number_from,
835 p_asset_number_to,
836 p_serial_number_from,
837 p_serial_number_to,
838 p_asset_group_from,
839 p_asset_group_to,
840 p_area_from,
841 p_area_to);
842 /*
843 dbms_output.put_line(SUBSTR(l_asset_query,1,250));
844 dbms_output.put_line(SUBSTR(l_asset_query,250,250));
845 dbms_output.put_line(SUBSTR(l_asset_query,500,250));
846 dbms_output.put_line(SUBSTR(l_asset_query,750,250));
847 dbms_output.put_line(SUBSTR(l_asset_query,1000,250));
848 dbms_output.put_line(SUBSTR(l_asset_query,1250,250));
849 dbms_output.put_line(SUBSTR(l_asset_query,1500,250));
850 dbms_output.put_line(SUBSTR(l_asset_query,1750,250));
851 dbms_output.put_line(SUBSTR(l_asset_query,2000,250));
852 */
853 debug('opening asset cursor',2);
854 OPEN l_asset_cursor FOR l_asset_query USING p_organization_id, p_asset_number_from,
855 p_asset_number_to, p_serial_number_from ,
856 p_serial_number_to, p_asset_group_from ,
857 p_asset_group_to , p_area_from , p_area_to,
858 p_organization_id, p_asset_group_from,
859 p_asset_group_to;
860 debug('done opening asset cursor',2);
861 return l_asset_cursor;
862
863 END get_asset_cursor;
864
865 FUNCTION get_asset_query(p_forecast_rec eam_forecasts%rowtype)
866 RETURN VARCHAR2
867 IS
868
869 BEGIN
870 return get_asset_query(p_forecast_rec.organization_id,
871 p_forecast_rec.asset_num_from,
872 p_forecast_rec.asset_num_to,
873 p_forecast_rec.asset_serial_num_from,
874 p_forecast_rec.asset_serial_num_to,
875 p_forecast_rec.asset_group_from,
876 p_forecast_rec.asset_group_to,
877 p_forecast_rec.area_from,
878 p_forecast_rec.area_to);
879 END get_asset_query;
880
881 FUNCTION get_asset_query(p_organization_id IN NUMBER,p_asset_number_from IN VARCHAR2,
882 p_asset_number_to IN VARCHAR2, p_serial_number_from IN VARCHAR2,
883 p_serial_number_to IN VARCHAR2 , p_asset_group_from IN VARCHAR2,
884 p_asset_group_to IN VARCHAR2 , p_area_from IN VARCHAR2, p_area_to IN VARCHAR2)
885 RETURN VARCHAR2
886 IS
887 -- There are two running statements that will be joined in a union
888 -- One is for serialized items with entries in cii, and the other is
889 -- for non-serialized rebuildables, with entries in only msi.
890
891 l_sql_query VARCHAR2(4010);
892
893 l_sql_cii VARCHAR2(2000);
894 l_sql_cii_select VARCHAR2(100);
895 l_sql_cii_from VARCHAR2(200);
896 l_sql_cii_where VARCHAR2(1700);
897
898
899
900 l_sql_msi VARCHAR2(2000);
901 l_sql_msi_select VARCHAR2(100);
902 l_sql_msi_from VARCHAR2(200);
903 l_sql_msi_where VARCHAR2(1700);
904
905 BEGIN
906
907 debug('Inside get_asset_query', 2);
908 -- CII.instance_id as asset_id, serialized as type
909
910 l_sql_cii_select := 'SELECT cii.instance_id as asset_id, 3 as asset_type ';
911
912 --MSI.inventory_item_id as asset_id, nonserialized (2) as type
913
914 l_sql_msi_select := 'SELECT msi.inventory_item_id as asset_id, 2 as asset_type ';
915
916
917 l_sql_cii_from := 'FROM csi_item_instances cii,' ||
918 'eam_org_maint_defaults eomd,' ||
919 'mtl_parameters mp ';
920
921 l_sql_msi_from := 'FROM mtl_parameters mp ';
922
923 -- If asset group criteria is specified, join with msi kfv
924
925 IF p_asset_group_from IS NOT NULL AND p_asset_group_to IS NOT NULL
926 THEN
927 l_sql_cii_from := l_sql_cii_from || ',mtl_system_items_b_kfv msi ';
928 l_sql_msi_from := l_sql_msi_from || ',mtl_system_items_b_kfv msi ';
929 ELSE
930 l_sql_cii_from := l_sql_cii_from || ',mtl_system_items_b msi ';
931 l_sql_msi_from := l_sql_msi_from || ',mtl_system_items_b msi ';
932 END IF;
933
934 debug('Inside get_asset_query 1', 2);
935 -- Join with mtl_locations to get area if the area criteria is specified
936 IF p_area_from IS NOT NULL AND p_area_to IS NOT NULL
937 THEN
938 l_sql_cii_from := l_sql_cii_from || ',mtl_eam_locations mel ';
939
940 -- No asset area for non serialized rebuildables
941 END IF;
942
943 -- Add the mandatory where clauses
944 debug('Inside get_asset_query 2', 2);
945 l_sql_cii_where := 'WHERE ' ||
946 'msi.eam_item_type in (1,3) ' ||
947 'AND msi.inventory_item_id = cii.inventory_item_id ' ||
948 'AND msi.organization_id = cii.last_vld_organization_id ' ||
949 'AND msi.serial_number_control_code <> 1 ' ||
950 'AND nvl(cii.active_start_date, sysdate-1) <= sysdate ' ||
951 'AND nvl(cii.active_end_date, sysdate+1) >= sysdate ' ||
952 'AND cii.instance_id = eomd.object_id (+) ' ||
953 'AND eomd.object_type (+) = 50 ' ||
954 'AND (eomd.organization_id is null or eomd.organization_id ' ||
955 '= mp.maint_organization_id) ' ||
956 --organization criteria
957 'AND msi.organization_id = mp.organization_id ' ||
958 'AND mp.maint_organization_id = :organization_id ';
959
960 l_sql_msi_where := 'WHERE ' ||
961 'msi.eam_item_type = 3 ' ||
962 'AND msi.SERIAL_NUMBER_CONTROL_CODE = 1 ' ||
963 'AND msi.organization_id = mp.organization_id ' ||
964 'AND mp.maint_organization_id = :organization_id ';
965
966 -- Add the where clause for asset numbers
967 debug('Inside get_asset_query 3', 2);
968 IF p_asset_number_from IS NOT NULL AND p_asset_number_to IS NOT NULL
969 THEN
970 l_sql_cii_where := l_sql_cii_where ||
971 'AND cii.instance_number >= :asset_number_from ' ||
972 'AND cii.instance_number <= :asset_number_to ';
973 ELSE
974 l_sql_cii_where := l_sql_cii_where ||
975 'AND (:asset_number_from IS NULL OR :asset_number_to IS NULL) ';
976 END IF;
977
978 -- Add the where clause for asset serial numbers
979
980 IF p_serial_number_from IS NOT NULL AND p_serial_number_to IS NOT NULL
981 THEN
982 l_sql_cii_where := l_sql_cii_where ||
983 'AND cii.serial_number >= :serial_number_from ' ||
984 'AND cii.serial_number <= :serial_number_to ';
985 ELSE
986 l_sql_cii_where := l_sql_cii_where ||
987 'AND (:serial_number_from IS NULL OR :serial_number_to IS NULL) ';
988 END IF;
989 debug('Inside get_asset_query 4', 2);
990 -- Add the where clause for asset groups
991
992 IF p_asset_group_from IS NOT NULL AND p_asset_group_to IS NOT NULL
993 THEN
994 l_sql_cii_where := l_sql_cii_where ||
995 'AND msi.concatenated_segments >= :asset_group_from ' ||
996 'AND msi.concatenated_segments <= :asset_group_to ';
997 l_sql_msi_where := l_sql_msi_where ||
998 'AND msi.concatenated_segments >= :asset_group_from ' ||
999 'AND msi.concatenated_segments <= :asset_group_to ';
1000 ELSE
1001 l_sql_cii_where := l_sql_cii_where ||
1002 'AND (:asset_group_from IS NULL OR :asset_group_to IS NULL) ';
1003 l_sql_msi_where := l_sql_msi_where ||
1004 'AND (:asset_group_from IS NULL OR :asset_group_to IS NULL) ';
1005 END IF;
1006
1007 -- Add the where clause for asset area
1008
1009 IF p_area_from IS NOT NULL AND p_area_to IS NOT NULL
1010 THEN
1011 l_sql_cii_where := l_sql_cii_where ||
1012 'AND mel.location_id = eomd.area_id ' ||
1013 'AND mel.location_codes >= :area_from ' ||
1014 'AND mel.location_codes <= :area_to ';
1015 -- If area is specified, completely ignore non-serialized rebuildables
1016 l_sql_msi := '';
1017 ELSE
1018 l_sql_cii_where := l_sql_cii_where ||
1019 'AND (:area_from IS NULL OR :area_to IS NULL) ';
1020 END IF;
1021
1022 -- Add the sql segments together
1023
1024 debug('Inside get_asset_query A', 2);
1025 l_sql_cii := l_sql_cii_select || l_sql_cii_from || l_sql_cii_where;
1026 l_sql_msi := l_sql_msi_select || l_sql_msi_from || l_sql_msi_where;
1027
1028 debug('Inside get_asset_query B', 2);
1029 -- Include the non-serialized rebuildables?
1030 IF p_area_from IS NOT NULL AND p_area_to IS NOT NULL
1031 THEN
1032 l_sql_query := l_sql_cii;
1033 ELSE
1034 l_sql_query := l_sql_cii || ' UNION ALL ' || l_sql_msi;
1035 END IF;
1036
1037 debug('Done with Inside get_asset_query', 2);
1038 -- Now bind the variables and open the cursor that retrieves all assets
1039
1040 return l_sql_query;
1041
1042 END get_asset_query;
1043
1044
1045 /* Loads the forecast criteria and branches on whether or not the forecast is
1046 historical or pm generated. */
1047
1048 -- TODO: OA needs to update the forecast criteria with the request id
1049
1050 PROCEDURE Extract_Forecast(
1051 p_api_version IN NUMBER,
1052 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1053 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1054 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1055 p_debug IN VARCHAR2 ,
1056 p_forecast_id IN eam_forecasts.forecast_id%TYPE,
1057 x_return_status OUT NOCOPY VARCHAR2 ,
1058 x_msg_count OUT NOCOPY NUMBER ,
1059 x_msg_data OUT NOCOPY VARCHAR2
1060
1061 )
1062 IS
1063
1064 l_api_name CONSTANT VARCHAR2(30) := 'Extract_Forecast';
1065 l_api_version CONSTANT NUMBER := 1.0;
1066 l_return_status VARCHAR2(1);
1067
1068 v_forecast_rec eam_forecasts%ROWTYPE;
1069
1070
1071 begin
1072 -- Standard Start of API savepoint
1073 SAVEPOINT Extract_Forecast_PVT;
1074 -- Standard call to check for call compatibility.
1075 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
1076 p_api_version ,
1077 l_api_name ,
1078 G_PKG_NAME
1079 )
1080 THEN
1081 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1082 END IF;
1083
1084 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1085 FND_MSG_PUB.initialize;
1086 END IF;
1087 -- Initialize API return status to success
1088 x_return_status := FND_API.G_RET_STS_SUCCESS;
1089
1090 SELECT * INTO v_forecast_rec
1091 FROM eam_forecasts
1092 WHERE forecast_id = p_forecast_id;
1093
1094 -- Set profile so org is visible to pjm_projects
1095
1096 EAM_COMMON_UTILITIES_PVT.
1097 set_profile('MFG_ORGANIZATION_ID', v_forecast_rec.organization_id);
1098
1099
1100 -- TODO: externalize lookup constants...best practice??
1101 IF v_forecast_rec.forecast_type <= 3 THEN
1102 debug('Extracting Historical Forecast');
1103
1104 extract_historical_forecast(
1105 p_api_version => p_api_version,
1106 p_commit => p_commit,
1107 p_validation_level => p_validation_level,
1108 p_init_msg_list => p_init_msg_list,
1109
1110 p_debug => p_debug,
1111
1112 p_forecast_rec => v_forecast_rec,
1113
1114
1115 p_user_id => v_forecast_rec.last_updated_by,
1116 p_request_id => v_forecast_rec.request_id,
1117 p_prog_id => 1,
1118 p_prog_app_id => 1,
1119 p_login_id => 1,
1120
1121
1122 x_return_status => x_return_status,
1123 x_msg_count => x_msg_count,
1124 x_msg_data => x_msg_data);
1125
1126 debug('DONE Extracting Historical Forecast');
1127 --extract_historical_forecast(v_forecast_rec);
1128 ELSE
1129 debug('Extracting FUTURE Forecast');
1130 extract_future_forecast(
1131 p_api_version => p_api_version,
1132 p_commit => p_commit,
1133 p_validation_level => p_validation_level,
1134 p_init_msg_list => p_init_msg_list,
1135
1136 p_debug => p_debug,
1137
1138 p_forecast_rec => v_forecast_rec,
1139
1140
1141 p_user_id => v_forecast_rec.last_updated_by,
1142 p_request_id => v_forecast_rec.request_id,
1143 p_prog_id => 1,
1144 p_prog_app_id => 1,
1145 p_login_id => 1,
1146
1147
1148 x_return_status => x_return_status,
1149 x_msg_count => x_msg_count,
1150 x_msg_data => x_msg_data);
1151 debug('DONE Extracting FUTURE Forecast');
1152 END IF;
1153
1154 -- Set OUT values
1155 -- Remove non-positive transaction values as well as work orders
1156 -- that have no costs associated with them
1157
1158 -- delete all costs not within the horizon
1159 delete from eam_forecast_cebba where forecast_id = p_forecast_id AND
1160 period_start_date > (select start_date from gl_periods
1161 where
1162 period_set_name = v_forecast_rec.period_set_name_to
1163 and period_name = v_forecast_rec.period_to);
1164
1165 -- delete all work orders that have zero costs associated
1166 delete from eam_forecast_wdj where forecast_id = p_forecast_id and wip_entity_id
1167 in
1168 (select wip_entity_id from (select wip_entity_id, sum(acct_value) as total from
1169 eam_forecast_cebba where forecast_id = p_forecast_id
1170 group by wip_entity_id) where total = 0);
1171
1172 delete from eam_forecast_wdj where forecast_id = p_forecast_id and
1173 wip_entity_id
1174 not in
1175 (select wip_entity_id from eam_forecast_cebba where forecast_id =
1176 p_forecast_id);
1177
1178
1179 -- Standard check of p_commit.
1180 IF FND_API.To_Boolean( p_commit ) THEN
1181 COMMIT WORK;
1182 END IF;
1183
1184 -- Standard call to get message count and if count is 1, get message info.
1185
1186 FND_MSG_PUB.Count_And_Get
1187 ( p_count => x_msg_count ,
1188 p_data => x_msg_data
1189 );
1190
1191 EXCEPTION
1192 WHEN no_data_found THEN
1193 ROLLBACK TO Extract_Forecast_PVT;
1194 x_return_status := FND_API.G_RET_STS_ERROR ;
1195 FND_MSG_PUB.Count_And_Get
1196 ( p_count => x_msg_count ,
1197 p_data => x_msg_data
1198 );
1199 WHEN OTHERS THEN
1200 ROLLBACK TO Extract_Forecast_PVT;
1201 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1202 IF FND_MSG_PUB.Check_Msg_Level
1203 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1204 THEN
1205 FND_MSG_PUB.Add_Exc_Msg
1206 ( G_FILE_NAME ,
1207 G_PKG_NAME ,
1208 l_api_name
1209 );
1210 END IF;
1211 FND_MSG_PUB.Count_And_Get
1212 ( p_count => x_msg_count ,
1213 p_data => x_msg_data
1214 );
1215
1216 end extract_forecast;
1217
1218
1219 -- Body definition (see forward declaration for more info)
1220 PROCEDURE extract_historical_forecast(
1221 p_api_version IN NUMBER,
1222 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1223 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1224 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1225 p_debug IN VARCHAR2 ,
1226
1227 p_forecast_rec IN eam_forecasts%ROWTYPE,
1228
1229
1230 --p_acct_period_from IN NUMBER,
1231 --p_acct_period_to IN NUMBER,
1232
1233 p_user_id IN NUMBER,
1234 p_request_id IN NUMBER,
1235 p_prog_id IN NUMBER,
1236 p_prog_app_id IN NUMBER,
1237 p_login_id IN NUMBER,
1238
1239 x_return_status OUT NOCOPY VARCHAR2,
1240 x_msg_count OUT NOCOPY NUMBER,
1241 x_msg_data OUT NOCOPY VARCHAR2)
1242 IS
1243 l_wip_table wo_table_type;
1244 begin
1245
1246 --Get the list of work orders to copy
1247
1248 l_wip_table := get_wip_table(p_forecast_rec);
1249
1250 --copy_wdj_to_forecast(l_wip_table, p_forecast_id
1251
1252 debug('Copying WDJ');
1253 Copy_WDJ_To_Forecast(
1254 p_api_version => p_api_version,
1255 p_commit => p_commit,
1256 p_validation_level => p_validation_level,
1257 p_init_msg_list => p_init_msg_list,
1258
1259 p_debug => p_debug,
1260
1261 p_forecast_rec => p_forecast_rec,
1262 p_wip_id_table => l_wip_table,
1263
1264 p_user_id => p_forecast_rec.last_updated_by,
1265 p_request_id => p_forecast_rec.request_id,
1266 p_prog_id => 1,
1267 p_prog_app_id => 1,
1268 p_login_id => 1,
1269
1270
1271 x_return_status => x_return_status,
1272 x_msg_count => x_msg_count,
1273 x_msg_data => x_msg_data);
1274
1275 debug('DONE Copying WDJ');
1276
1277 Copy_WO_To_Forecast(
1278 p_api_version => p_api_version,
1279 p_commit => p_commit,
1280 p_validation_level => p_validation_level,
1281 p_init_msg_list => p_init_msg_list,
1282
1283 p_debug => p_debug,
1284
1285 p_forecast_rec => p_forecast_rec,
1286 p_wip_id_table => l_wip_table,
1287
1288 p_user_id => p_forecast_rec.last_updated_by,
1289 p_request_id => p_forecast_rec.request_id,
1290 p_prog_id => 1,
1291 p_prog_app_id => 1,
1292 p_login_id => 1,
1293
1294
1295 x_return_status => x_return_status,
1296 x_msg_count => x_msg_count,
1297 x_msg_data => x_msg_data);
1298
1299 debug('DONE Copying WO');
1300
1301 Copy_WOR_To_Forecast(
1302 p_api_version => p_api_version,
1303 p_commit => p_commit,
1304 p_validation_level => p_validation_level,
1305 p_init_msg_list => p_init_msg_list,
1306
1307 p_debug => p_debug,
1308
1309 p_forecast_rec => p_forecast_rec,
1310 p_wip_id_table => l_wip_table,
1311
1312 p_user_id => p_forecast_rec.last_updated_by,
1313 p_request_id => p_forecast_rec.request_id,
1314 p_prog_id => 1,
1315 p_prog_app_id => 1,
1316 p_login_id => 1,
1317
1318
1319 x_return_status => x_return_status,
1320 x_msg_count => x_msg_count,
1321 x_msg_data => x_msg_data);
1322
1323 debug('DONE Copying WOR');
1324
1325 Copy_WRO_To_Forecast(
1326 p_api_version => p_api_version,
1327 p_commit => p_commit,
1328 p_validation_level => p_validation_level,
1329 p_init_msg_list => p_init_msg_list,
1330
1331 p_debug => p_debug,
1332
1333 p_forecast_rec => p_forecast_rec,
1334 p_wip_id_table => l_wip_table,
1335
1336 p_user_id => p_forecast_rec.last_updated_by,
1337 p_request_id => p_forecast_rec.request_id,
1338 p_prog_id => 1,
1339 p_prog_app_id => 1,
1340 p_login_id => 1,
1341
1342
1343 x_return_status => x_return_status,
1344 x_msg_count => x_msg_count,
1345 x_msg_data => x_msg_data);
1346
1347 debug('DONE Copying WRO');
1348
1349 Copy_WEDI_To_Forecast(
1350 p_api_version => p_api_version,
1351 p_commit => p_commit,
1352 p_validation_level => p_validation_level,
1353 p_init_msg_list => p_init_msg_list,
1354
1355 p_debug => p_debug,
1356
1357 p_forecast_rec => p_forecast_rec,
1358 p_wip_id_table => l_wip_table,
1359
1360 p_user_id => p_forecast_rec.last_updated_by,
1361 p_request_id => p_forecast_rec.request_id,
1362 p_prog_id => 1,
1363 p_prog_app_id => 1,
1364 p_login_id => 1,
1365
1366
1367 x_return_status => x_return_status,
1368 x_msg_count => x_msg_count,
1369 x_msg_data => x_msg_data);
1370
1371 debug('DONE Copying WEDI');
1372
1373 Copy_CEBBA_To_Forecast(
1374 p_api_version => p_api_version,
1375 p_commit => p_commit,
1376 p_validation_level => p_validation_level,
1377 p_init_msg_list => p_init_msg_list,
1378
1379 p_debug => p_debug,
1380
1381 p_forecast_rec => p_forecast_rec,
1382 p_wip_id_table => l_wip_table,
1383
1384 p_user_id => p_forecast_rec.last_updated_by,
1385 p_request_id => p_forecast_rec.request_id,
1386 p_prog_id => 1,
1387 p_prog_app_id => 1,
1388 p_login_id => 1,
1389
1390
1391 x_return_status => x_return_status,
1392 x_msg_count => x_msg_count,
1393 x_msg_data => x_msg_data);
1394
1395 debug('DONE Copying CEBBA');
1396 end extract_historical_forecast;
1397
1398 -- Body definition (see forward declaration for more info)
1399 PROCEDURE extract_future_forecast (
1400 p_api_version IN NUMBER,
1401 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1402 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1403 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1404 p_debug IN VARCHAR2 ,
1405
1406 p_forecast_rec IN eam_forecasts%ROWTYPE,
1407
1408
1409 --p_acct_period_from IN NUMBER,
1410 --p_acct_period_to IN NUMBER,
1411
1412 p_user_id IN NUMBER,
1413 p_request_id IN NUMBER,
1414 p_prog_id IN NUMBER,
1415 p_prog_app_id IN NUMBER,
1416 p_login_id IN NUMBER,
1417
1418 x_return_status OUT NOCOPY VARCHAR2,
1419 x_msg_count OUT NOCOPY NUMBER,
1420 x_msg_data OUT NOCOPY VARCHAR2)
1421 IS
1422 pragma autonomous_transaction;
1423 l_asset_cursor forecast_asset_cursor_type;
1424 l_asset_id NUMBER;
1425 l_asset_type NUMBER;
1426 l_pm_group_id NUMBER;
1427 l_forecast_id NUMBER;
1428 l_forecast_DATE date;
1429 l_start_date DATE;
1430 l_end_date DATE;
1431 l_forecast_rec EAM_FORECASTS%ROWTYPE;
1432 l_count NUMBER;
1433
1434
1435 BEGIN
1436
1437
1438 SELECT glp.start_date
1439 INTO l_start_date
1440 FROM gl_periods glp
1441 WHERE glp.period_name = p_forecast_rec.period_from
1442 AND glp.period_set_name = p_forecast_rec.period_set_name_from;
1443
1444 SELECT glp.end_date
1445 INTO l_end_date
1446 FROM gl_periods glp
1447 WHERE glp.period_name = p_forecast_rec.period_to
1448 AND glp.period_set_name = p_forecast_rec.period_set_name_to;
1449
1450 select wip_job_schedule_interface_s.nextval into l_pm_group_id from dual;
1451 debug('Before get_asset_cursor', 2);
1452 l_asset_cursor := get_asset_cursor(p_forecast_rec);
1453 debug('Exit get_asset_cursor', 2);
1454
1455 l_forecast_rec := p_forecast_rec;
1456
1457 l_forecast_rec.creation_date := SYSDATE;
1458
1459 debug('BEFORE PM ENGINE');
1460
1461 LOOP
1462
1463 FETCH l_asset_cursor INTO l_asset_id, l_asset_type;
1464 EXIT WHEN l_asset_cursor % NOTFOUND;
1465
1466 debug('BEFORE PM ENGINE: GROUP ID: ' || l_pm_group_id ||
1467 ' ASSET_ID: ' || l_asset_ID || ' ASSET_TYPE: ' ||
1468 l_asset_TYPE);
1469
1470 EAM_PM_ENGINE.do_forecast3(nonSched => 'N',
1471 startDate => l_start_date,
1472 endDate => l_end_date,
1473 orgID => p_forecast_rec.organization_id,
1474 userID => p_forecast_rec.last_update_login,
1475 objectID => l_asset_id,
1476 objectType => l_asset_type,
1477 setname_id => -1,
1478 combine_default => 'Y',
1479 group_id => l_pm_group_id);
1480
1481 debug('AFTER PM ENGINE: GROUP ID: ' || l_pm_group_id ||
1482 ' ASSET_ID: ' || l_asset_ID || ' ASSET_TYPE: ' ||
1483 l_asset_TYPE);
1484 END LOOP;
1485
1486 delete from eam_forecasted_work_orders where
1487 group_id = l_pm_group_id and
1488 scheduled_start_date < l_start_date;
1489
1490 --select count(*) into l_count from wip_discrete_jobs;
1491
1492 --debug('BEFORE CONVERT, WDJ: ' || l_count);
1493
1494 convert_work_orders(p_pm_group_id => l_pm_group_id,
1495 p_return_status => x_return_status,
1496 p_msg => x_msg_data);
1497
1498 --debug('AFTER CONVERT, WDJ: ' || l_count);
1499
1500 extract_autonomous_forecast(
1501 p_api_version => p_api_version,
1502 p_commit => p_commit,
1503 p_validation_level => p_validation_level,
1504 p_init_msg_list => p_init_msg_list,
1505
1506 p_debug => p_debug,
1507
1508 p_forecast_rec => l_forecast_rec,
1509
1510
1511 p_user_id => p_forecast_rec.last_updated_by,
1512 p_request_id => p_forecast_rec.request_id,
1513 p_prog_id => 1,
1514 p_prog_app_id => 1,
1515 p_login_id => 1,
1516
1517
1518 x_return_status => x_return_status,
1519 x_msg_count => x_msg_count,
1520 x_msg_data => x_msg_data);
1521
1522
1523 rollback;
1524
1525 END extract_future_forecast;
1526
1527 PROCEDURE extract_autonomous_forecast(
1528 p_api_version IN NUMBER,
1529 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1530 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1531 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1532 p_debug IN VARCHAR2 ,
1533
1534 p_forecast_rec IN eam_forecasts%ROWTYPE,
1535
1536
1537 --p_acct_period_from IN NUMBER,
1538 --p_acct_period_to IN NUMBER,
1539
1540 p_user_id IN NUMBER,
1541 p_request_id IN NUMBER,
1542 p_prog_id IN NUMBER,
1543 p_prog_app_id IN NUMBER,
1544 p_login_id IN NUMBER,
1545
1546 x_return_status OUT NOCOPY VARCHAR2,
1547 x_msg_count OUT NOCOPY NUMBER,
1548 x_msg_data OUT NOCOPY VARCHAR2)IS
1549
1550 l_wip_table wo_table_type;
1551 begin
1552
1553 --Get the list of work orders to copy
1554
1555 l_wip_table := get_wip_table(p_forecast_rec);
1556
1557 --copy_wdj_to_forecast(l_wip_table, p_forecast_id
1558
1559 debug('Copying WDJ AUTO: ' || l_wip_table.COUNT);
1560
1561 Copy_WDJ_To_Forecast_auto(
1562 p_api_version => p_api_version,
1563 p_commit => p_commit,
1564 p_validation_level => p_validation_level,
1565 p_init_msg_list => p_init_msg_list,
1566
1567 p_debug => p_debug,
1568
1569 p_forecast_rec => p_forecast_rec,
1570 p_wip_id_table => l_wip_table,
1571
1572 p_user_id => p_forecast_rec.last_updated_by,
1573 p_request_id => p_forecast_rec.request_id,
1574 p_prog_id => 1,
1575 p_prog_app_id => 1,
1576 p_login_id => 1,
1577
1578
1579 x_return_status => x_return_status,
1580 x_msg_count => x_msg_count,
1581 x_msg_data => x_msg_data);
1582
1583 debug('DONE Copying WDJ AUTO: ' || l_wip_table.COUNT);
1584 --USAF
1585 Copy_WO_To_Forecast_auto(
1586 p_api_version => p_api_version,
1587 p_commit => p_commit,
1588 p_validation_level => p_validation_level,
1589 p_init_msg_list => p_init_msg_list,
1590
1591 p_debug => p_debug,
1592
1593 p_forecast_rec => p_forecast_rec,
1594 p_wip_id_table => l_wip_table,
1595
1596 p_user_id => p_forecast_rec.last_updated_by,
1597 p_request_id => p_forecast_rec.request_id,
1598 p_prog_id => 1,
1599 p_prog_app_id => 1,
1600 p_login_id => 1,
1601
1602
1603 x_return_status => x_return_status,
1604 x_msg_count => x_msg_count,
1605 x_msg_data => x_msg_data);
1606
1607 debug('DONE Copying WO AUTO: '|| l_wip_table.COUNT);
1608
1609 Copy_WOR_To_Forecast_auto(
1610 p_api_version => p_api_version,
1611 p_commit => p_commit,
1612 p_validation_level => p_validation_level,
1613 p_init_msg_list => p_init_msg_list,
1614
1615 p_debug => p_debug,
1616
1617 p_forecast_rec => p_forecast_rec,
1618 p_wip_id_table => l_wip_table,
1619
1620 p_user_id => p_forecast_rec.last_updated_by,
1621 p_request_id => p_forecast_rec.request_id,
1622 p_prog_id => 1,
1623 p_prog_app_id => 1,
1624 p_login_id => 1,
1625
1626
1627 x_return_status => x_return_status,
1628 x_msg_count => x_msg_count,
1629 x_msg_data => x_msg_data);
1630
1631 debug('DONE Copying WOR');
1632
1633 Copy_WRO_To_Forecast_auto(
1634 p_api_version => p_api_version,
1635 p_commit => p_commit,
1636 p_validation_level => p_validation_level,
1637 p_init_msg_list => p_init_msg_list,
1638
1639 p_debug => p_debug,
1640
1641 p_forecast_rec => p_forecast_rec,
1642 p_wip_id_table => l_wip_table,
1643
1644 p_user_id => p_forecast_rec.last_updated_by,
1645 p_request_id => p_forecast_rec.request_id,
1646 p_prog_id => 1,
1647 p_prog_app_id => 1,
1648 p_login_id => 1,
1649
1650
1651 x_return_status => x_return_status,
1652 x_msg_count => x_msg_count,
1653 x_msg_data => x_msg_data);
1654
1655 debug('DONE Copying WRO');
1656
1657 Copy_WEDI_To_Forecast_auto(
1658 p_api_version => p_api_version,
1659 p_commit => p_commit,
1660 p_validation_level => p_validation_level,
1661 p_init_msg_list => p_init_msg_list,
1662
1663 p_debug => p_debug,
1664
1665 p_forecast_rec => p_forecast_rec,
1666 p_wip_id_table => l_wip_table,
1667
1668 p_user_id => p_forecast_rec.last_updated_by,
1669 p_request_id => p_forecast_rec.request_id,
1670 p_prog_id => 1,
1671 p_prog_app_id => 1,
1672 p_login_id => 1,
1673
1674
1675 x_return_status => x_return_status,
1676 x_msg_count => x_msg_count,
1677 x_msg_data => x_msg_data);
1678
1679 debug('DONE Copying WEDI');
1680
1681
1682
1683
1684 Copy_CEBBA_To_Forecast_auto(
1685 p_api_version => p_api_version,
1686 p_commit => p_commit,
1687 p_validation_level => p_validation_level,
1688 p_init_msg_list => p_init_msg_list,
1689
1690 p_debug => p_debug,
1691
1692 p_forecast_rec => p_forecast_rec,
1693 p_wip_id_table => l_wip_table,
1694
1695 p_user_id => p_forecast_rec.last_updated_by,
1696 p_request_id => p_forecast_rec.request_id,
1697 p_prog_id => 1,
1698 p_prog_app_id => 1,
1699 p_login_id => 1,
1700
1701
1702 x_return_status => x_return_status,
1703 x_msg_count => x_msg_count,
1704 x_msg_data => x_msg_data);
1705
1706 debug('DONE Copying CEBBA');
1707 --USAF
1708
1709 END extract_autonomous_forecast;
1710
1711
1712 PROCEDURE Copy_WDJ_To_Forecast (
1713 p_api_version IN NUMBER,
1714 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1715 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1716 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1717 p_debug IN VARCHAR2 ,
1718
1719 p_forecast_rec IN eam_forecasts%ROWTYPE,
1720 p_wip_id_table IN wo_table_type,
1721
1722 --p_acct_period_from IN NUMBER,
1723 --p_acct_period_to IN NUMBER,
1724
1725 p_user_id IN NUMBER,
1726 p_request_id IN NUMBER,
1727 p_prog_id IN NUMBER,
1728 p_prog_app_id IN NUMBER,
1729 p_login_id IN NUMBER,
1730
1731 x_return_status OUT NOCOPY VARCHAR2,
1732 x_msg_count OUT NOCOPY NUMBER,
1733 x_msg_data OUT NOCOPY VARCHAR2)IS
1734
1735 l_count NUMBER;
1736
1737 BEGIN
1738 FORALL j IN p_wip_id_table.FIRST..p_wip_id_table.LAST
1739
1740 INSERT INTO EAM_FORECAST_WDJ (
1741 WIP_ENTITY_ID, ORGANIZATION_ID, LAST_UPDATE_DATE,
1742 LAST_UPDATED_BY, CREATION_DATE, CREATED_BY,
1743 LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID,
1744 PROGRAM_ID, PROGRAM_UPDATE_DATE, SOURCE_LINE_ID,
1745 SOURCE_CODE, DESCRIPTION, STATUS_TYPE,
1746 PRIMARY_ITEM_ID, FIRM_PLANNED_FLAG, JOB_TYPE,
1747 WIP_SUPPLY_TYPE, CLASS_CODE, MATERIAL_ACCOUNT,
1748 MATERIAL_OVERHEAD_ACCOUNT, RESOURCE_ACCOUNT, OUTSIDE_PROCESSING_ACCOUNT,
1749 MATERIAL_VARIANCE_ACCOUNT, RESOURCE_VARIANCE_ACCOUNT, OUTSIDE_PROC_VARIANCE_ACCOUNT,
1750 STD_COST_ADJUSTMENT_ACCOUNT, OVERHEAD_ACCOUNT, OVERHEAD_VARIANCE_ACCOUNT,
1751 SCHEDULED_START_DATE, DATE_RELEASED, SCHEDULED_COMPLETION_DATE,
1752 DATE_COMPLETED, DATE_CLOSED, START_QUANTITY,
1753 QUANTITY_COMPLETED, QUANTITY_SCRAPPED, NET_QUANTITY,
1754 BOM_REFERENCE_ID, ROUTING_REFERENCE_ID, COMMON_BOM_SEQUENCE_ID,
1755 COMMON_ROUTING_SEQUENCE_ID, BOM_REVISION, ROUTING_REVISION,
1756 BOM_REVISION_DATE, ROUTING_REVISION_DATE, LOT_NUMBER,
1757 ALTERNATE_BOM_DESIGNATOR, ALTERNATE_ROUTING_DESIGNATOR, COMPLETION_SUBINVENTORY,
1758 COMPLETION_LOCATOR_ID, MPS_SCHEDULED_COMPLETION_DATE, MPS_NET_QUANTITY,
1759 DEMAND_CLASS, ATTRIBUTE_CATEGORY, ATTRIBUTE1,
1760 ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4,
1761 ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7,
1762 ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10,
1763 ATTRIBUTE11, ATTRIBUTE12, ATTRIBUTE13,
1764 ATTRIBUTE14, ATTRIBUTE15, SCHEDULE_GROUP_ID,
1765 BUILD_SEQUENCE, LINE_ID, PROJECT_ID,
1766 TASK_ID, KANBAN_CARD_ID, OVERCOMPLETION_TOLERANCE_TYPE,
1767 OVERCOMPLETION_TOLERANCE_VALUE, END_ITEM_UNIT_NUMBER, PO_CREATION_TIME,
1768 PRIORITY, DUE_DATE, EST_SCRAP_ACCOUNT,
1769 EST_SCRAP_VAR_ACCOUNT, EST_SCRAP_PRIOR_QTY, DUE_DATE_PENALTY,
1770 DUE_DATE_TOLERANCE, COPRODUCTS_SUPPLY, PARENT_WIP_ENTITY_ID,
1771 ASSET_NUMBER, ASSET_GROUP_ID, REBUILD_ITEM_ID,
1772 REBUILD_SERIAL_NUMBER, MANUAL_REBUILD_FLAG, SHUTDOWN_TYPE,
1773 ESTIMATION_STATUS, REQUESTED_START_DATE, NOTIFICATION_REQUIRED,
1774 WORK_ORDER_TYPE, OWNING_DEPARTMENT, ACTIVITY_TYPE,
1775 ACTIVITY_CAUSE, TAGOUT_REQUIRED, PLAN_MAINTENANCE,
1776 PM_SCHEDULE_ID, LAST_ESTIMATION_DATE, LAST_ESTIMATION_REQ_ID,
1777 ACTIVITY_SOURCE, SERIALIZATION_START_OP, MAINTENANCE_OBJECT_ID,
1778 MAINTENANCE_OBJECT_TYPE, MAINTENANCE_OBJECT_SOURCE, MATERIAL_ISSUE_BY_MO,
1779 SCHEDULING_REQUEST_ID, ISSUE_ZERO_COST_FLAG, EAM_LINEAR_LOCATION_ID,
1780 ACTUAL_START_DATE,
1781 EXPEDITED, EXPECTED_HOLD_RELEASE_DATE, FORECAST_ID)
1782
1783 SELECT WIP_ENTITY_ID, ORGANIZATION_ID, LAST_UPDATE_DATE,
1784 LAST_UPDATED_BY, CREATION_DATE, CREATED_BY,
1785 LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID,
1786 PROGRAM_ID, PROGRAM_UPDATE_DATE, SOURCE_LINE_ID,
1787 SOURCE_CODE, DESCRIPTION, STATUS_TYPE,
1788 PRIMARY_ITEM_ID, FIRM_PLANNED_FLAG, JOB_TYPE,
1789 WIP_SUPPLY_TYPE, CLASS_CODE, MATERIAL_ACCOUNT,
1790 MATERIAL_OVERHEAD_ACCOUNT, RESOURCE_ACCOUNT, OUTSIDE_PROCESSING_ACCOUNT,
1791 MATERIAL_VARIANCE_ACCOUNT, RESOURCE_VARIANCE_ACCOUNT, OUTSIDE_PROC_VARIANCE_ACCOUNT,
1792 STD_COST_ADJUSTMENT_ACCOUNT, OVERHEAD_ACCOUNT, OVERHEAD_VARIANCE_ACCOUNT,
1793 SCHEDULED_START_DATE, DATE_RELEASED, SCHEDULED_COMPLETION_DATE,
1794 DATE_COMPLETED, DATE_CLOSED, START_QUANTITY,
1795 QUANTITY_COMPLETED, QUANTITY_SCRAPPED, NET_QUANTITY,
1796 BOM_REFERENCE_ID, ROUTING_REFERENCE_ID, COMMON_BOM_SEQUENCE_ID,
1797 COMMON_ROUTING_SEQUENCE_ID, BOM_REVISION, ROUTING_REVISION,
1798 BOM_REVISION_DATE, ROUTING_REVISION_DATE, LOT_NUMBER,
1799 ALTERNATE_BOM_DESIGNATOR, ALTERNATE_ROUTING_DESIGNATOR, COMPLETION_SUBINVENTORY,
1800 COMPLETION_LOCATOR_ID, MPS_SCHEDULED_COMPLETION_DATE, MPS_NET_QUANTITY,
1801 DEMAND_CLASS, ATTRIBUTE_CATEGORY, ATTRIBUTE1,
1802 ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4,
1803 ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7,
1804 ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10,
1805 ATTRIBUTE11, ATTRIBUTE12, ATTRIBUTE13,
1806 ATTRIBUTE14, ATTRIBUTE15, SCHEDULE_GROUP_ID,
1807 BUILD_SEQUENCE, LINE_ID, PROJECT_ID,
1808 TASK_ID, KANBAN_CARD_ID, OVERCOMPLETION_TOLERANCE_TYPE,
1809 OVERCOMPLETION_TOLERANCE_VALUE, END_ITEM_UNIT_NUMBER, PO_CREATION_TIME,
1810 PRIORITY, DUE_DATE, EST_SCRAP_ACCOUNT,
1811 EST_SCRAP_VAR_ACCOUNT, EST_SCRAP_PRIOR_QTY, DUE_DATE_PENALTY,
1812 DUE_DATE_TOLERANCE, COPRODUCTS_SUPPLY, PARENT_WIP_ENTITY_ID,
1813 ASSET_NUMBER, ASSET_GROUP_ID, REBUILD_ITEM_ID,
1814 REBUILD_SERIAL_NUMBER, MANUAL_REBUILD_FLAG, SHUTDOWN_TYPE,
1815 ESTIMATION_STATUS, REQUESTED_START_DATE, NOTIFICATION_REQUIRED,
1816 WORK_ORDER_TYPE, OWNING_DEPARTMENT, ACTIVITY_TYPE,
1817 ACTIVITY_CAUSE, TAGOUT_REQUIRED, PLAN_MAINTENANCE,
1818 PM_SCHEDULE_ID, LAST_ESTIMATION_DATE, LAST_ESTIMATION_REQ_ID,
1819 ACTIVITY_SOURCE, SERIALIZATION_START_OP, MAINTENANCE_OBJECT_ID,
1820 MAINTENANCE_OBJECT_TYPE, MAINTENANCE_OBJECT_SOURCE, MATERIAL_ISSUE_BY_MO,
1821 SCHEDULING_REQUEST_ID, ISSUE_ZERO_COST_FLAG, EAM_LINEAR_LOCATION_ID,
1822 ACTUAL_START_DATE,
1823 EXPEDITED, EXPECTED_HOLD_RELEASE_DATE,
1824 p_forecast_rec.forecast_id AS FORECAST_ID
1825
1826 FROM WIP_DISCRETE_JOBS
1827 WHERE wip_entity_id = p_wip_id_table(j);
1828
1829 debug('COMPLETE WDJ COPY: ');
1830
1831 END Copy_WDJ_To_Forecast;
1832
1833 PROCEDURE Copy_WDJ_To_Forecast_auto (
1834 p_api_version IN NUMBER,
1835 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1836 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1837 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1838 p_debug IN VARCHAR2 ,
1839
1840 p_forecast_rec IN eam_forecasts%ROWTYPE,
1841 p_wip_id_table IN wo_table_type,
1842
1843 --p_acct_period_from IN NUMBER,
1844 --p_acct_period_to IN NUMBER,
1845
1846 p_user_id IN NUMBER,
1847 p_request_id IN NUMBER,
1848 p_prog_id IN NUMBER,
1849 p_prog_app_id IN NUMBER,
1850 p_login_id IN NUMBER,
1851
1852 x_return_status OUT NOCOPY VARCHAR2,
1853 x_msg_count OUT NOCOPY NUMBER,
1854 x_msg_data OUT NOCOPY VARCHAR2)IS
1855
1856
1857 l_wdj_table wdj_table_type;
1858 l_cebba_table cebba_table_type;
1859
1860
1861 BEGIN
1862 Copy_WDJ_To_Forecast(
1863 p_api_version => p_api_version,
1864 p_commit => p_commit,
1865 p_validation_level => p_validation_level,
1866 p_init_msg_list => p_init_msg_list,
1867
1868 p_debug => p_debug,
1869
1870 p_forecast_rec => p_forecast_rec,
1871 p_wip_id_table => p_wip_id_table,
1872
1873 p_user_id => p_forecast_rec.last_updated_by,
1874 p_request_id => p_forecast_rec.request_id,
1875 p_prog_id => 1,
1876 p_prog_app_id => 1,
1877 p_login_id => 1,
1878
1879
1880 x_return_status => x_return_status,
1881 x_msg_count => x_msg_count,
1882 x_msg_data => x_msg_data);
1883
1884 debug('DONE Copying WDJ');
1885
1886 select * bulk collect INTO l_wdj_table
1887 from eam_forecast_wdj
1888 where forecast_id = p_forecast_rec.forecast_id;
1889
1890 debug(' Size of work order table ' || l_wdj_table.COUNT);
1891 --ROLLBACK; --USAF
1892 insert_into_wdj_auto(l_wdj_table);
1893 --insert_into_cebba_auto(l_cebba_table); --USAF
1894
1895 END Copy_WDJ_To_Forecast_auto;
1896
1897 PROCEDURE insert_into_wdj_auto(p_wdj_table wdj_table_type)
1898 IS
1899 pragma autonomous_transaction;
1900 BEGIN
1901 debug(' Size of work order table BEFORE ' || p_wdj_table.COUNT);
1902 FORALL i IN p_wdj_table.First..p_wdj_table.last
1903 insert into eam_forecast_wdj values p_wdj_table(i);
1904 commit;
1905 debug(' Size of work order table AFTER ' || p_wdj_table.COUNT);
1906 END insert_into_wdj_auto;
1907
1908 /*
1909 PROCEDURE insert_into_wdj_auto(p_wdj_table wdj_table_type)
1910 IS
1911 pragma autonomous_transaction;
1912 BEGIN
1913 debug(' Size of work order table BEFORE ' || p_wdj_table.COUNT);
1914 FOR i IN 1..p_wdj_table.last
1915 LOOP
1916 debug('inserting : ' || p_wdj_table(i).wip_entity_id);
1917 INSERT INTO EAM_FORECAST_WDJ (
1918 WIP_ENTITY_ID, ORGANIZATION_ID, LAST_UPDATE_DATE,
1919 LAST_UPDATED_BY, CREATION_DATE, CREATED_BY,
1920 LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID,
1921 PROGRAM_ID, PROGRAM_UPDATE_DATE, SOURCE_LINE_ID,
1922 SOURCE_CODE, DESCRIPTION, STATUS_TYPE,
1923 PRIMARY_ITEM_ID, FIRM_PLANNED_FLAG, JOB_TYPE,
1924 WIP_SUPPLY_TYPE, CLASS_CODE, MATERIAL_ACCOUNT,
1925 MATERIAL_OVERHEAD_ACCOUNT, RESOURCE_ACCOUNT, OUTSIDE_PROCESSING_ACCOUNT,
1926 MATERIAL_VARIANCE_ACCOUNT, RESOURCE_VARIANCE_ACCOUNT, OUTSIDE_PROC_VARIANCE_ACCOUNT,
1927 STD_COST_ADJUSTMENT_ACCOUNT, OVERHEAD_ACCOUNT, OVERHEAD_VARIANCE_ACCOUNT,
1928 SCHEDULED_START_DATE, DATE_RELEASED, SCHEDULED_COMPLETION_DATE,
1929 DATE_COMPLETED, DATE_CLOSED, START_QUANTITY,
1930 QUANTITY_COMPLETED, QUANTITY_SCRAPPED, NET_QUANTITY,
1931 BOM_REFERENCE_ID, ROUTING_REFERENCE_ID, COMMON_BOM_SEQUENCE_ID,
1932 COMMON_ROUTING_SEQUENCE_ID, BOM_REVISION, ROUTING_REVISION,
1933 BOM_REVISION_DATE, ROUTING_REVISION_DATE, LOT_NUMBER,
1934 ALTERNATE_BOM_DESIGNATOR, ALTERNATE_ROUTING_DESIGNATOR, COMPLETION_SUBINVENTORY,
1935 COMPLETION_LOCATOR_ID, MPS_SCHEDULED_COMPLETION_DATE, MPS_NET_QUANTITY,
1936 DEMAND_CLASS, ATTRIBUTE_CATEGORY, ATTRIBUTE1,
1937 ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4,
1938 ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7,
1939 ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10,
1940 ATTRIBUTE11, ATTRIBUTE12, ATTRIBUTE13,
1941 ATTRIBUTE14, ATTRIBUTE15, SCHEDULE_GROUP_ID,
1942 BUILD_SEQUENCE, LINE_ID, PROJECT_ID,
1943 TASK_ID, KANBAN_CARD_ID, OVERCOMPLETION_TOLERANCE_TYPE,
1944 OVERCOMPLETION_TOLERANCE_VALUE, END_ITEM_UNIT_NUMBER, PO_CREATION_TIME,
1945 PRIORITY, DUE_DATE, EST_SCRAP_ACCOUNT,
1946 EST_SCRAP_VAR_ACCOUNT, EST_SCRAP_PRIOR_QTY, DUE_DATE_PENALTY,
1947 DUE_DATE_TOLERANCE, COPRODUCTS_SUPPLY, PARENT_WIP_ENTITY_ID,
1948 ASSET_NUMBER, ASSET_GROUP_ID, REBUILD_ITEM_ID,
1949 REBUILD_SERIAL_NUMBER, MANUAL_REBUILD_FLAG, SHUTDOWN_TYPE,
1950 ESTIMATION_STATUS, REQUESTED_START_DATE, NOTIFICATION_REQUIRED,
1951 WORK_ORDER_TYPE, OWNING_DEPARTMENT, ACTIVITY_TYPE,
1952 ACTIVITY_CAUSE, TAGOUT_REQUIRED, PLAN_MAINTENANCE,
1953 PM_SCHEDULE_ID, LAST_ESTIMATION_DATE, LAST_ESTIMATION_REQ_ID,
1954 ACTIVITY_SOURCE, SERIALIZATION_START_OP, MAINTENANCE_OBJECT_ID,
1955 MAINTENANCE_OBJECT_TYPE, MAINTENANCE_OBJECT_SOURCE, MATERIAL_ISSUE_BY_MO,
1956 SCHEDULING_REQUEST_ID, ISSUE_ZERO_COST_FLAG, EAM_LINEAR_LOCATION_ID,
1957 ACTUAL_START_DATE,
1958 EXPEDITED, EXPECTED_HOLD_RELEASE_DATE, FORECAST_ID)
1959
1960 VALUES(
1961 p_wdj_table(i).WIP_ENTITY_ID, p_wdj_table(i).ORGANIZATION_ID, p_wdj_table(i).LAST_UPDATE_DATE,
1962 p_wdj_table(i).LAST_UPDATED_BY, p_wdj_table(i).CREATION_DATE, p_wdj_table(i).CREATED_BY,
1963 p_wdj_table(i).LAST_UPDATE_LOGIN, p_wdj_table(i).REQUEST_ID, p_wdj_table(i).PROGRAM_APPLICATION_ID,
1964 p_wdj_table(i).PROGRAM_ID, p_wdj_table(i).PROGRAM_UPDATE_DATE, p_wdj_table(i).SOURCE_LINE_ID,
1965 p_wdj_table(i).SOURCE_CODE, p_wdj_table(i).DESCRIPTION, p_wdj_table(i).STATUS_TYPE,
1966 p_wdj_table(i).PRIMARY_ITEM_ID, p_wdj_table(i).FIRM_PLANNED_FLAG, p_wdj_table(i).JOB_TYPE,
1967 p_wdj_table(i).WIP_SUPPLY_TYPE, p_wdj_table(i).CLASS_CODE, p_wdj_table(i).MATERIAL_ACCOUNT,
1968 p_wdj_table(i).MATERIAL_OVERHEAD_ACCOUNT, p_wdj_table(i).RESOURCE_ACCOUNT, p_wdj_table(i).OUTSIDE_PROCESSING_ACCOUNT,
1969 p_wdj_table(i).MATERIAL_VARIANCE_ACCOUNT, p_wdj_table(i).RESOURCE_VARIANCE_ACCOUNT, p_wdj_table(i).OUTSIDE_PROC_VARIANCE_ACCOUNT,
1970 p_wdj_table(i).STD_COST_ADJUSTMENT_ACCOUNT, p_wdj_table(i).OVERHEAD_ACCOUNT, p_wdj_table(i).OVERHEAD_VARIANCE_ACCOUNT,
1971 p_wdj_table(i).SCHEDULED_START_DATE, p_wdj_table(i).DATE_RELEASED, p_wdj_table(i).SCHEDULED_COMPLETION_DATE,
1972 p_wdj_table(i).DATE_COMPLETED, p_wdj_table(i).DATE_CLOSED, p_wdj_table(i).START_QUANTITY,
1973 p_wdj_table(i).QUANTITY_COMPLETED, p_wdj_table(i).QUANTITY_SCRAPPED, p_wdj_table(i).NET_QUANTITY,
1974 p_wdj_table(i).BOM_REFERENCE_ID, p_wdj_table(i).ROUTING_REFERENCE_ID, p_wdj_table(i).COMMON_BOM_SEQUENCE_ID,
1975 p_wdj_table(i).COMMON_ROUTING_SEQUENCE_ID, p_wdj_table(i).BOM_REVISION, p_wdj_table(i).ROUTING_REVISION,
1976 p_wdj_table(i).BOM_REVISION_DATE, p_wdj_table(i).ROUTING_REVISION_DATE, p_wdj_table(i).LOT_NUMBER,
1977 p_wdj_table(i).ALTERNATE_BOM_DESIGNATOR, p_wdj_table(i).ALTERNATE_ROUTING_DESIGNATOR, p_wdj_table(i).COMPLETION_SUBINVENTORY,
1978 p_wdj_table(i).COMPLETION_LOCATOR_ID, p_wdj_table(i).MPS_SCHEDULED_COMPLETION_DATE, p_wdj_table(i).MPS_NET_QUANTITY,
1979 p_wdj_table(i).DEMAND_CLASS, p_wdj_table(i).ATTRIBUTE_CATEGORY, p_wdj_table(i).ATTRIBUTE1,
1980 p_wdj_table(i).ATTRIBUTE2, p_wdj_table(i).ATTRIBUTE3, p_wdj_table(i).ATTRIBUTE4,
1981 p_wdj_table(i).ATTRIBUTE5, p_wdj_table(i).ATTRIBUTE6, p_wdj_table(i).ATTRIBUTE7,
1982 p_wdj_table(i).ATTRIBUTE8, p_wdj_table(i).ATTRIBUTE9, p_wdj_table(i).ATTRIBUTE10,
1983 p_wdj_table(i).ATTRIBUTE11, p_wdj_table(i).ATTRIBUTE12, p_wdj_table(i).ATTRIBUTE13,
1984 p_wdj_table(i).ATTRIBUTE14, p_wdj_table(i).ATTRIBUTE15, p_wdj_table(i).SCHEDULE_GROUP_ID,
1985 p_wdj_table(i).BUILD_SEQUENCE, p_wdj_table(i).LINE_ID, p_wdj_table(i).PROJECT_ID,
1986 p_wdj_table(i).TASK_ID, p_wdj_table(i).KANBAN_CARD_ID, p_wdj_table(i).OVERCOMPLETION_TOLERANCE_TYPE,
1987 p_wdj_table(i).OVERCOMPLETION_TOLERANCE_VALUE, p_wdj_table(i).END_ITEM_UNIT_NUMBER, p_wdj_table(i).PO_CREATION_TIME,
1988 p_wdj_table(i).PRIORITY, p_wdj_table(i).DUE_DATE, p_wdj_table(i).EST_SCRAP_ACCOUNT,
1989 p_wdj_table(i).EST_SCRAP_VAR_ACCOUNT, p_wdj_table(i).EST_SCRAP_PRIOR_QTY, p_wdj_table(i).DUE_DATE_PENALTY,
1990 p_wdj_table(i).DUE_DATE_TOLERANCE, p_wdj_table(i).COPRODUCTS_SUPPLY, p_wdj_table(i).PARENT_WIP_ENTITY_ID,
1991 p_wdj_table(i).ASSET_NUMBER, p_wdj_table(i).ASSET_GROUP_ID, p_wdj_table(i).REBUILD_ITEM_ID,
1992 p_wdj_table(i).REBUILD_SERIAL_NUMBER, p_wdj_table(i).MANUAL_REBUILD_FLAG, p_wdj_table(i).SHUTDOWN_TYPE,
1993 p_wdj_table(i).ESTIMATION_STATUS, p_wdj_table(i).REQUESTED_START_DATE, p_wdj_table(i).NOTIFICATION_REQUIRED,
1994 p_wdj_table(i).WORK_ORDER_TYPE, p_wdj_table(i).OWNING_DEPARTMENT, p_wdj_table(i).ACTIVITY_TYPE,
1995 p_wdj_table(i).ACTIVITY_CAUSE, p_wdj_table(i).TAGOUT_REQUIRED, p_wdj_table(i).PLAN_MAINTENANCE,
1996 p_wdj_table(i).PM_SCHEDULE_ID, p_wdj_table(i).LAST_ESTIMATION_DATE, p_wdj_table(i).LAST_ESTIMATION_REQ_ID,
1997 p_wdj_table(i).ACTIVITY_SOURCE, p_wdj_table(i).SERIALIZATION_START_OP, p_wdj_table(i).MAINTENANCE_OBJECT_ID,
1998 p_wdj_table(i).MAINTENANCE_OBJECT_TYPE, p_wdj_table(i).MAINTENANCE_OBJECT_SOURCE, p_wdj_table(i).MATERIAL_ISSUE_BY_MO,
1999 p_wdj_table(i).SCHEDULING_REQUEST_ID, p_wdj_table(i).ISSUE_ZERO_COST_FLAG, p_wdj_table(i).EAM_LINEAR_LOCATION_ID,
2000 p_wdj_table(i).ACTUAL_START_DATE,
2001 p_wdj_table(i).EXPEDITED, p_wdj_table(i).EXPECTED_HOLD_RELEASE_DATE, p_wdj_table(i).FORECAST_ID);
2002 END LOOP;
2003 commit;
2004 EXCEPTION
2005
2006 WHEN OTHERS THEN
2007 RAISE;
2008 debug(' Size of work order table AFTER ' || p_wdj_table.COUNT);
2009 END insert_into_wdj_auto;
2010 */
2011 PROCEDURE Copy_CEBBA_To_Forecast_auto (
2012 p_api_version IN NUMBER,
2013 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2014 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2015 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2016 p_debug IN VARCHAR2 ,
2017
2018 p_forecast_rec IN eam_forecasts%ROWTYPE,
2019 p_wip_id_table IN wo_table_type,
2020
2021 --p_acct_period_from IN NUMBER,
2022 --p_acct_period_to IN NUMBER,
2023
2024 p_user_id IN NUMBER,
2025 p_request_id IN NUMBER,
2026 p_prog_id IN NUMBER,
2027 p_prog_app_id IN NUMBER,
2028 p_login_id IN NUMBER,
2029
2030 x_return_status OUT NOCOPY VARCHAR2,
2031 x_msg_count OUT NOCOPY NUMBER,
2032 x_msg_data OUT NOCOPY VARCHAR2)IS
2033
2034
2035 l_cebba_table cebba_table_type;
2036
2037 BEGIN
2038 Copy_CEBBA_To_Forecast(
2039 p_api_version => p_api_version,
2040 p_commit => p_commit,
2041 p_validation_level => p_validation_level,
2042 p_init_msg_list => p_init_msg_list,
2043
2044 p_debug => p_debug,
2045
2046 p_forecast_rec => p_forecast_rec,
2047 p_wip_id_table => p_wip_id_table,
2048
2049 p_user_id => p_forecast_rec.last_updated_by,
2050 p_request_id => p_forecast_rec.request_id,
2051 p_prog_id => 1,
2052 p_prog_app_id => 1,
2053 p_login_id => 1,
2054
2055
2056 x_return_status => x_return_status,
2057 x_msg_count => x_msg_count,
2058 x_msg_data => x_msg_data);
2059
2060 debug('DONE Copying CEBBA');
2061 select * bulk collect INTO l_cebba_table
2062 from eam_forecast_cebba
2063 where forecast_id = p_forecast_rec.forecast_id;
2064 ROLLBACK;
2065
2066 insert_into_cebba_auto(l_cebba_table);
2067
2068 END Copy_CEBBA_To_Forecast_auto;
2069
2070
2071 PROCEDURE insert_into_cebba_auto(p_cebba_table cebba_table_type)
2072 IS
2073 pragma autonomous_transaction;
2074 BEGIN
2075 FORALL i IN p_cebba_table.First..p_cebba_table.last
2076 insert into eam_forecast_cebba values p_cebba_table(i);
2077 commit;
2078 END insert_into_cebba_auto;
2079
2080
2081 PROCEDURE Copy_WOR_To_Forecast (
2082 p_api_version IN NUMBER,
2083 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2084 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2085 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2086 p_debug IN VARCHAR2 ,
2087
2088 p_forecast_rec IN eam_forecasts%ROWTYPE,
2089 p_wip_id_table IN wo_table_type,
2090
2091 --p_acct_period_from IN NUMBER,
2092 --p_acct_period_to IN NUMBER,
2093
2094 p_user_id IN NUMBER,
2095 p_request_id IN NUMBER,
2096 p_prog_id IN NUMBER,
2097 p_prog_app_id IN NUMBER,
2098 p_login_id IN NUMBER,
2099
2100 x_return_status OUT NOCOPY VARCHAR2,
2101 x_msg_count OUT NOCOPY NUMBER,
2102 x_msg_data OUT NOCOPY VARCHAR2)IS
2103
2104 BEGIN
2105
2106 FORALL j IN p_wip_id_table.FIRST..p_wip_id_table.LAST
2107
2108 INSERT INTO EAM_FORECAST_WOR (
2109 WIP_ENTITY_ID, OPERATION_SEQ_NUM, RESOURCE_SEQ_NUM,
2110 ORGANIZATION_ID, REPETITIVE_SCHEDULE_ID, LAST_UPDATE_DATE,
2111 LAST_UPDATED_BY, CREATION_DATE, CREATED_BY,
2112 LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID,
2113 PROGRAM_ID, PROGRAM_UPDATE_DATE, RESOURCE_ID,
2114 UOM_CODE, BASIS_TYPE, USAGE_RATE_OR_AMOUNT,
2115 ACTIVITY_ID, SCHEDULED_FLAG, ASSIGNED_UNITS,
2116 AUTOCHARGE_TYPE, STANDARD_RATE_FLAG, APPLIED_RESOURCE_UNITS,
2117 APPLIED_RESOURCE_VALUE, START_DATE, COMPLETION_DATE,
2118 ATTRIBUTE_CATEGORY, ATTRIBUTE1, ATTRIBUTE2,
2119 ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5,
2120 ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8,
2121 ATTRIBUTE9, ATTRIBUTE10, ATTRIBUTE11,
2122 ATTRIBUTE12, ATTRIBUTE13, ATTRIBUTE14,
2123 ATTRIBUTE15, RELIEVED_RES_COMPLETION_UNITS, RELIEVED_RES_SCRAP_UNITS,
2124 RELIEVED_RES_COMPLETION_VALUE, RELIEVED_RES_SCRAP_VALUE, RELIEVED_VARIANCE_VALUE,
2125 TEMP_RELIEVED_VALUE, RELIEVED_RES_FINAL_COMP_UNITS, DEPARTMENT_ID,
2126 PHANTOM_FLAG, PHANTOM_OP_SEQ_NUM, PHANTOM_ITEM_ID,
2127 SCHEDULE_SEQ_NUM, SUBSTITUTE_GROUP_NUM, REPLACEMENT_GROUP_NUM,
2128 PRINCIPLE_FLAG, SETUP_ID, PARENT_RESOURCE_SEQ,
2129 BATCH_ID, RELIEVED_RES_UNITS, RELIEVED_RES_VALUE,
2130 MAXIMUM_ASSIGNED_UNITS, FIRM_FLAG, GROUP_SEQUENCE_ID,
2131 GROUP_SEQUENCE_NUMBER, ACTUAL_START_DATE, ACTUAL_COMPLETION_DATE,
2132 PROJECTED_COMPLETION_DATE, FORECAST_ID)
2133
2134
2135
2136 SELECT WIP_ENTITY_ID, OPERATION_SEQ_NUM, RESOURCE_SEQ_NUM,
2137 ORGANIZATION_ID, REPETITIVE_SCHEDULE_ID, LAST_UPDATE_DATE,
2138 LAST_UPDATED_BY, CREATION_DATE, CREATED_BY,
2139 LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID,
2140 PROGRAM_ID, PROGRAM_UPDATE_DATE, RESOURCE_ID,
2141 UOM_CODE, BASIS_TYPE, USAGE_RATE_OR_AMOUNT,
2142 ACTIVITY_ID, SCHEDULED_FLAG, ASSIGNED_UNITS,
2143 AUTOCHARGE_TYPE, STANDARD_RATE_FLAG, APPLIED_RESOURCE_UNITS,
2144 APPLIED_RESOURCE_VALUE, START_DATE, COMPLETION_DATE,
2145 ATTRIBUTE_CATEGORY, ATTRIBUTE1, ATTRIBUTE2,
2146 ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5,
2147 ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8,
2148 ATTRIBUTE9, ATTRIBUTE10, ATTRIBUTE11,
2149 ATTRIBUTE12, ATTRIBUTE13, ATTRIBUTE14,
2150 ATTRIBUTE15, RELIEVED_RES_COMPLETION_UNITS, RELIEVED_RES_SCRAP_UNITS,
2151 RELIEVED_RES_COMPLETION_VALUE, RELIEVED_RES_SCRAP_VALUE, RELIEVED_VARIANCE_VALUE,
2152 TEMP_RELIEVED_VALUE, RELIEVED_RES_FINAL_COMP_UNITS, DEPARTMENT_ID,
2153 PHANTOM_FLAG, PHANTOM_OP_SEQ_NUM, PHANTOM_ITEM_ID,
2154 SCHEDULE_SEQ_NUM, SUBSTITUTE_GROUP_NUM, REPLACEMENT_GROUP_NUM,
2155 PRINCIPLE_FLAG, SETUP_ID, PARENT_RESOURCE_SEQ,
2156 BATCH_ID, RELIEVED_RES_UNITS, RELIEVED_RES_VALUE,
2157 MAXIMUM_ASSIGNED_UNITS, FIRM_FLAG, GROUP_SEQUENCE_ID,
2158 GROUP_SEQUENCE_NUMBER, ACTUAL_START_DATE, ACTUAL_COMPLETION_DATE,
2159 PROJECTED_COMPLETION_DATE,p_forecast_rec.forecast_id AS FORECAST_ID
2160
2161 FROM WIP_OPERATION_RESOURCES
2162 WHERE wip_entity_id = p_wip_id_table(j);
2163
2164
2165 debug('COMPLETE WOR COPY');
2166
2167 END Copy_WOR_To_Forecast;
2168
2169
2170 --USAF
2171
2172 PROCEDURE Copy_WOR_To_Forecast_auto (
2173 p_api_version IN NUMBER,
2174 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2175 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2176 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2177 p_debug IN VARCHAR2 ,
2178
2179 p_forecast_rec IN eam_forecasts%ROWTYPE,
2180 p_wip_id_table IN wo_table_type,
2181
2182 --p_acct_period_from IN NUMBER,
2183 --p_acct_period_to IN NUMBER,
2184
2185 p_user_id IN NUMBER,
2186 p_request_id IN NUMBER,
2187 p_prog_id IN NUMBER,
2188 p_prog_app_id IN NUMBER,
2189 p_login_id IN NUMBER,
2190
2191 x_return_status OUT NOCOPY VARCHAR2,
2192 x_msg_count OUT NOCOPY NUMBER,
2193 x_msg_data OUT NOCOPY VARCHAR2)IS
2194
2195
2196
2197 l_wor_table wor_table_type;
2198 l_cebba_table cebba_table_type;
2199
2200
2201 BEGIN
2202 Copy_WOR_To_Forecast(
2203 p_api_version => p_api_version,
2204 p_commit => p_commit,
2205 p_validation_level => p_validation_level,
2206 p_init_msg_list => p_init_msg_list,
2207
2208 p_debug => p_debug,
2209
2210 p_forecast_rec => p_forecast_rec,
2211 p_wip_id_table => p_wip_id_table,
2212
2213 p_user_id => p_forecast_rec.last_updated_by,
2214 p_request_id => p_forecast_rec.request_id,
2215 p_prog_id => 1,
2216 p_prog_app_id => 1,
2217 p_login_id => 1,
2218
2219
2220 x_return_status => x_return_status,
2221 x_msg_count => x_msg_count,
2222 x_msg_data => x_msg_data);
2223
2224 debug('DONE Copying WO Op resources');
2225
2226 select * bulk collect INTO l_wor_table
2227 from eam_forecast_wor
2228 where forecast_id = p_forecast_rec.forecast_id;
2229
2230
2231
2232 debug(' Size of work order operations resource table ' || l_wor_table.COUNT);
2233 --ROLLBACK;
2234 insert_into_wor_auto(l_wor_table);
2235
2236
2237 END Copy_WOR_To_Forecast_auto;
2238
2239 PROCEDURE insert_into_wor_auto(p_wor_table wor_table_type)
2240 IS
2241 pragma autonomous_transaction;
2242 BEGIN
2243 debug(' Size of work order operations table BEFORE ' || p_wor_table.COUNT);
2244 FORALL i IN p_wor_table.First..p_wor_table.last
2245 insert into eam_forecast_wor values p_wor_table(i);
2246 commit;
2247 debug(' Size of work order operations table AFTER ' || p_wor_table.COUNT);
2248 END insert_into_wor_auto;
2249
2250
2251
2252 --USAF
2253
2254
2255 PROCEDURE Copy_WRO_To_Forecast (
2256 p_api_version IN NUMBER,
2257 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2258 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2259 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2260 p_debug IN VARCHAR2 ,
2261
2262 p_forecast_rec IN eam_forecasts%ROWTYPE,
2263 p_wip_id_table IN wo_table_type,
2264
2265 --p_acct_period_from IN NUMBER,
2266 --p_acct_period_to IN NUMBER,
2267
2268 p_user_id IN NUMBER,
2269 p_request_id IN NUMBER,
2270 p_prog_id IN NUMBER,
2271 p_prog_app_id IN NUMBER,
2272 p_login_id IN NUMBER,
2273
2274 x_return_status OUT NOCOPY VARCHAR2,
2275 x_msg_count OUT NOCOPY NUMBER,
2276 x_msg_data OUT NOCOPY VARCHAR2)IS
2277
2278 BEGIN
2279
2280 FORALL j IN p_wip_id_table.FIRST..p_wip_id_table.LAST
2281
2282
2283 INSERT INTO EAM_FORECAST_WRO (
2284 INVENTORY_ITEM_ID, ORGANIZATION_ID, WIP_ENTITY_ID,
2285 OPERATION_SEQ_NUM, REPETITIVE_SCHEDULE_ID, LAST_UPDATE_DATE,
2286 LAST_UPDATED_BY, CREATION_DATE, CREATED_BY,
2287 LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID,
2288 PROGRAM_ID, PROGRAM_UPDATE_DATE, COMPONENT_SEQUENCE_ID,
2289 DEPARTMENT_ID, WIP_SUPPLY_TYPE, DATE_REQUIRED,
2290 REQUIRED_QUANTITY, QUANTITY_ISSUED, QUANTITY_PER_ASSEMBLY,
2291 COMMENTS, SUPPLY_SUBINVENTORY, SUPPLY_LOCATOR_ID,
2292 MRP_NET_FLAG, MPS_REQUIRED_QUANTITY, MPS_DATE_REQUIRED,
2293 SEGMENT1, SEGMENT2, SEGMENT3,
2294 SEGMENT4, SEGMENT5, SEGMENT6,
2295 SEGMENT7, SEGMENT8, SEGMENT9,
2296 SEGMENT10, SEGMENT11, SEGMENT12,
2297 SEGMENT13, SEGMENT14, SEGMENT15,
2298 SEGMENT16, SEGMENT17, SEGMENT18,
2299 SEGMENT19, SEGMENT20, ATTRIBUTE_CATEGORY,
2300 ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3,
2301 ATTRIBUTE4, ATTRIBUTE5, ATTRIBUTE6,
2302 ATTRIBUTE7, ATTRIBUTE8, ATTRIBUTE9,
2303 ATTRIBUTE10, ATTRIBUTE11, ATTRIBUTE12,
2304 ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15,
2305 RELIEVED_MATL_COMPLETION_QTY, RELIEVED_MATL_SCRAP_QUANTITY, RELIEVED_MATL_FINAL_COMP_QTY,
2306 QUANTITY_ALLOCATED, QUANTITY_BACKORDERED, QUANTITY_RELIEVED,
2307 COSTED_QUANTITY_ISSUED, COSTED_QUANTITY_RELIEVED, AUTO_REQUEST_MATERIAL,
2308 RELEASED_QUANTITY, SUGGESTED_VENDOR_NAME, VENDOR_ID,
2309 UNIT_PRICE, BASIS_TYPE,
2310 COMPONENT_YIELD_FACTOR, PRIMARY_COMPONENT_ID,FORECAST_ID)
2311
2312
2313
2314 SELECT INVENTORY_ITEM_ID, ORGANIZATION_ID, WIP_ENTITY_ID,
2315 OPERATION_SEQ_NUM, REPETITIVE_SCHEDULE_ID, LAST_UPDATE_DATE,
2316 LAST_UPDATED_BY, CREATION_DATE, CREATED_BY,
2317 LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID,
2318 PROGRAM_ID, PROGRAM_UPDATE_DATE, COMPONENT_SEQUENCE_ID,
2319 DEPARTMENT_ID, WIP_SUPPLY_TYPE, DATE_REQUIRED,
2320 REQUIRED_QUANTITY, QUANTITY_ISSUED, QUANTITY_PER_ASSEMBLY,
2321 COMMENTS, SUPPLY_SUBINVENTORY, SUPPLY_LOCATOR_ID,
2322 MRP_NET_FLAG, MPS_REQUIRED_QUANTITY, MPS_DATE_REQUIRED,
2323 SEGMENT1, SEGMENT2, SEGMENT3,
2324 SEGMENT4, SEGMENT5, SEGMENT6,
2325 SEGMENT7, SEGMENT8, SEGMENT9,
2326 SEGMENT10, SEGMENT11, SEGMENT12,
2327 SEGMENT13, SEGMENT14, SEGMENT15,
2328 SEGMENT16, SEGMENT17, SEGMENT18,
2329 SEGMENT19, SEGMENT20, ATTRIBUTE_CATEGORY,
2330 ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3,
2331 ATTRIBUTE4, ATTRIBUTE5, ATTRIBUTE6,
2332 ATTRIBUTE7, ATTRIBUTE8, ATTRIBUTE9,
2333 ATTRIBUTE10, ATTRIBUTE11, ATTRIBUTE12,
2334 ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15,
2335 RELIEVED_MATL_COMPLETION_QTY, RELIEVED_MATL_SCRAP_QUANTITY, RELIEVED_MATL_FINAL_COMP_QTY,
2336 QUANTITY_ALLOCATED, QUANTITY_BACKORDERED, QUANTITY_RELIEVED,
2337 COSTED_QUANTITY_ISSUED, COSTED_QUANTITY_RELIEVED, AUTO_REQUEST_MATERIAL,
2338 RELEASED_QUANTITY, SUGGESTED_VENDOR_NAME, VENDOR_ID,
2339 UNIT_PRICE, BASIS_TYPE, COMPONENT_YIELD_FACTOR,
2340 PRIMARY_COMPONENT_ID,
2341 p_forecast_rec.forecast_id AS FORECAST_ID
2342
2343 FROM WIP_REQUIREMENT_OPERATIONS
2344 WHERE wip_entity_id = p_wip_id_table(j);
2345 debug('COMPLETE WRO COPY');
2346
2347 END Copy_WRO_To_Forecast;
2348
2349 --USAF
2350
2351 PROCEDURE Copy_wro_To_Forecast_auto (
2352 p_api_version IN NUMBER,
2353 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2354 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2355 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2356 p_debug IN VARCHAR2 ,
2357
2358 p_forecast_rec IN eam_forecasts%ROWTYPE,
2359 p_wip_id_table IN wo_table_type,
2360
2361 --p_acct_period_from IN NUMBER,
2362 --p_acct_period_to IN NUMBER,
2363
2364 p_user_id IN NUMBER,
2365 p_request_id IN NUMBER,
2366 p_prog_id IN NUMBER,
2367 p_prog_app_id IN NUMBER,
2368 p_login_id IN NUMBER,
2369
2370 x_return_status OUT NOCOPY VARCHAR2,
2371 x_msg_count OUT NOCOPY NUMBER,
2372 x_msg_data OUT NOCOPY VARCHAR2)IS
2373
2374
2375
2376 l_wro_table wro_table_type;
2377 l_cebba_table cebba_table_type;
2378
2379
2380 BEGIN
2381 Copy_wro_To_Forecast(
2382 p_api_version => p_api_version,
2383 p_commit => p_commit,
2384 p_validation_level => p_validation_level,
2385 p_init_msg_list => p_init_msg_list,
2386
2387 p_debug => p_debug,
2388
2389 p_forecast_rec => p_forecast_rec,
2390 p_wip_id_table => p_wip_id_table,
2391
2392 p_user_id => p_forecast_rec.last_updated_by,
2393 p_request_id => p_forecast_rec.request_id,
2394 p_prog_id => 1,
2395 p_prog_app_id => 1,
2396 p_login_id => 1,
2397
2398
2399 x_return_status => x_return_status,
2400 x_msg_count => x_msg_count,
2401 x_msg_data => x_msg_data);
2402
2403 debug('DONE Copying WO Op resources');
2404
2405 select * bulk collect INTO l_wro_table
2406 from eam_forecast_wro
2407 where forecast_id = p_forecast_rec.forecast_id;
2408
2409
2410
2411 debug(' Size of work order materials table ' || l_wro_table.COUNT);
2412
2413 insert_into_wro_auto(l_wro_table);
2414
2415
2416 END Copy_wro_To_Forecast_auto;
2417
2418 PROCEDURE insert_into_wro_auto(p_wro_table wro_table_type)
2419 IS
2420 pragma autonomous_transaction;
2421 BEGIN
2422 debug(' Size of work order materials table BEFORE ' || p_wro_table.COUNT);
2423 FORALL i IN p_wro_table.First..p_wro_table.last
2424 insert into eam_forecast_wro values p_wro_table(i);
2425 commit;
2426 debug(' Size of work order materials table AFTER ' || p_wro_table.COUNT);
2427 END insert_into_wro_auto;
2428
2429 --USAF
2430
2431
2432
2433 PROCEDURE Copy_WO_To_Forecast (
2434 p_api_version IN NUMBER,
2435 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2436 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2437 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2438 p_debug IN VARCHAR2 ,
2439
2440 p_forecast_rec IN eam_forecasts%ROWTYPE,
2441 p_wip_id_table IN wo_table_type,
2442
2443 --p_acct_period_from IN NUMBER,
2444 --p_acct_period_to IN NUMBER,
2445
2446 p_user_id IN NUMBER,
2447 p_request_id IN NUMBER,
2448 p_prog_id IN NUMBER,
2449 p_prog_app_id IN NUMBER,
2450 p_login_id IN NUMBER,
2451
2452 x_return_status OUT NOCOPY VARCHAR2,
2453 x_msg_count OUT NOCOPY NUMBER,
2454 x_msg_data OUT NOCOPY VARCHAR2)IS
2455
2456 BEGIN
2457 debug('COPYING WO');
2458 FORALL j IN p_wip_id_table.FIRST..p_wip_id_table.LAST
2459
2460 INSERT INTO EAM_FORECAST_WO (
2461 WIP_ENTITY_ID, OPERATION_SEQ_NUM, ORGANIZATION_ID,
2462 REPETITIVE_SCHEDULE_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY,
2463 CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN,
2464 REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID,
2465 PROGRAM_UPDATE_DATE, OPERATION_SEQUENCE_ID, STANDARD_OPERATION_ID,
2466 DEPARTMENT_ID, DESCRIPTION, SCHEDULED_QUANTITY,
2467 QUANTITY_IN_QUEUE, QUANTITY_RUNNING, QUANTITY_WAITING_TO_MOVE,
2468 QUANTITY_REJECTED, QUANTITY_SCRAPPED, QUANTITY_COMPLETED,
2469 FIRST_UNIT_START_DATE, FIRST_UNIT_COMPLETION_DATE, LAST_UNIT_START_DATE,
2470 LAST_UNIT_COMPLETION_DATE, PREVIOUS_OPERATION_SEQ_NUM, NEXT_OPERATION_SEQ_NUM,
2471 COUNT_POINT_TYPE, BACKFLUSH_FLAG, MINIMUM_TRANSFER_QUANTITY,
2472 DATE_LAST_MOVED, ATTRIBUTE_CATEGORY, ATTRIBUTE1,
2473 ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4,
2474 ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7,
2475 ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10,
2476 ATTRIBUTE11, ATTRIBUTE12, ATTRIBUTE13,
2477 ATTRIBUTE14, ATTRIBUTE15, WF_ITEMTYPE,
2478 WF_ITEMKEY, OPERATION_YIELD, OPERATION_YIELD_ENABLED,
2479 PRE_SPLIT_QUANTITY, OPERATION_COMPLETED, SHUTDOWN_TYPE,
2480 X_POS, Y_POS, PREVIOUS_OPERATION_SEQ_ID,
2481 SKIP_FLAG, LONG_DESCRIPTION, DISABLE_DATE,
2482 CUMULATIVE_SCRAP_QUANTITY, RECOMMENDED, PROGRESS_PERCENTAGE,
2483 WSM_OP_SEQ_NUM, ACTUAL_START_DATE, ACTUAL_COMPLETION_DATE,
2484 WSM_BONUS_QUANTITY, EMPLOYEE_ID,
2485 PROJECT_COMPLETION_DATE, WSM_UPDATE_QUANTITY_TXN_ID, WSM_UPDATE_QUANTITY_COMPLETED,
2486 LOWEST_ACCEPTABLE_YIELD, FORECAST_ID)
2487
2488
2489 SELECT WIP_ENTITY_ID, OPERATION_SEQ_NUM, ORGANIZATION_ID,
2490 REPETITIVE_SCHEDULE_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY,
2491 CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN,
2492 REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID,
2493 PROGRAM_UPDATE_DATE, OPERATION_SEQUENCE_ID, STANDARD_OPERATION_ID,
2494 DEPARTMENT_ID, DESCRIPTION, SCHEDULED_QUANTITY,
2495 QUANTITY_IN_QUEUE, QUANTITY_RUNNING, QUANTITY_WAITING_TO_MOVE,
2496 QUANTITY_REJECTED, QUANTITY_SCRAPPED, QUANTITY_COMPLETED,
2497 FIRST_UNIT_START_DATE, FIRST_UNIT_COMPLETION_DATE, LAST_UNIT_START_DATE,
2498 LAST_UNIT_COMPLETION_DATE, PREVIOUS_OPERATION_SEQ_NUM, NEXT_OPERATION_SEQ_NUM,
2499 COUNT_POINT_TYPE, BACKFLUSH_FLAG, MINIMUM_TRANSFER_QUANTITY,
2500 DATE_LAST_MOVED, ATTRIBUTE_CATEGORY, ATTRIBUTE1,
2501 ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4,
2502 ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7,
2503 ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10,
2504 ATTRIBUTE11, ATTRIBUTE12, ATTRIBUTE13,
2505 ATTRIBUTE14, ATTRIBUTE15, WF_ITEMTYPE,
2506 WF_ITEMKEY, OPERATION_YIELD, OPERATION_YIELD_ENABLED,
2507 PRE_SPLIT_QUANTITY, OPERATION_COMPLETED, SHUTDOWN_TYPE,
2508 X_POS, Y_POS, PREVIOUS_OPERATION_SEQ_ID,
2509 SKIP_FLAG, LONG_DESCRIPTION, DISABLE_DATE,
2510 CUMULATIVE_SCRAP_QUANTITY, RECOMMENDED, PROGRESS_PERCENTAGE,
2511 WSM_OP_SEQ_NUM, ACTUAL_START_DATE, ACTUAL_COMPLETION_DATE,
2512 WSM_BONUS_QUANTITY, EMPLOYEE_ID, PROJECTED_COMPLETION_DATE,
2513 WSM_UPDATE_QUANTITY_TXN_ID, WSM_COSTED_QUANTITY_COMPLETED, LOWEST_ACCEPTABLE_YIELD, p_forecast_rec.forecast_id AS FORECAST_ID
2514
2515 FROM WIP_OPERATIONS
2516 WHERE wip_entity_id = p_wip_id_table(j);
2517 debug('COMPLETE');
2518 EXCEPTION
2519
2520 WHEN no_data_found THEN
2521 --ROLLBACK TO Extract_Forecast_PVT;
2522 x_return_status := FND_API.G_RET_STS_ERROR ;
2523 FND_MSG_PUB.Count_And_Get
2524 ( p_count => x_msg_count ,
2525 p_data => x_msg_data
2526 );
2527 RAISE FND_API.G_EXC_ERROR;
2528 WHEN OTHERS THEN
2529 debug('EXCEPTION');
2530 --ROLLBACK TO Extract_Forecast_PVT;
2531 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2532 IF FND_MSG_PUB.Check_Msg_Level
2533 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2534 THEN
2535 FND_MSG_PUB.Add_Exc_Msg
2536 ( G_FILE_NAME ,
2537 G_PKG_NAME ,
2538 'Copy_WO_To_Forecast'
2539 );
2540 END IF;
2541 FND_MSG_PUB.Count_And_Get
2542 ( p_count => x_msg_count ,
2543 p_data => x_msg_data
2544 );
2545 RAISE FND_API.G_EXC_ERROR;
2546
2547
2548 END Copy_WO_To_Forecast;
2549
2550
2551 --USAF
2552 PROCEDURE Copy_WO_To_Forecast_auto (
2553 p_api_version IN NUMBER,
2554 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2555 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2556 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2557 p_debug IN VARCHAR2 ,
2558
2559 p_forecast_rec IN eam_forecasts%ROWTYPE,
2560 p_wip_id_table IN wo_table_type,
2561
2562 --p_acct_period_from IN NUMBER,
2563 --p_acct_period_to IN NUMBER,
2564
2565 p_user_id IN NUMBER,
2566 p_request_id IN NUMBER,
2567 p_prog_id IN NUMBER,
2568 p_prog_app_id IN NUMBER,
2569 p_login_id IN NUMBER,
2570
2571 x_return_status OUT NOCOPY VARCHAR2,
2572 x_msg_count OUT NOCOPY NUMBER,
2573 x_msg_data OUT NOCOPY VARCHAR2)IS
2574
2575
2576
2577 l_wop_table wop_table_type;
2578 l_cebba_table cebba_table_type;
2579
2580
2581 BEGIN
2582 Copy_WO_To_Forecast(
2583 p_api_version => p_api_version,
2584 p_commit => p_commit,
2585 p_validation_level => p_validation_level,
2586 p_init_msg_list => p_init_msg_list,
2587
2588 p_debug => p_debug,
2589
2590 p_forecast_rec => p_forecast_rec,
2591 p_wip_id_table => p_wip_id_table,
2592
2593 p_user_id => p_forecast_rec.last_updated_by,
2594 p_request_id => p_forecast_rec.request_id,
2595 p_prog_id => 1,
2596 p_prog_app_id => 1,
2597 p_login_id => 1,
2598
2599
2600 x_return_status => x_return_status,
2601 x_msg_count => x_msg_count,
2602 x_msg_data => x_msg_data);
2603
2604 debug('DONE Copying WO Op');
2605
2606 select * bulk collect INTO l_wop_table
2607 from eam_forecast_wo
2608 where forecast_id = p_forecast_rec.forecast_id;
2609
2610
2611
2612 debug(' Size of work order operations table ' || l_wop_table.COUNT);
2613
2614 insert_into_wop_auto(l_wop_table);
2615
2616
2617 END Copy_WO_To_Forecast_auto;
2618
2619 PROCEDURE insert_into_wop_auto(p_wop_table wop_table_type)
2620 IS
2621 pragma autonomous_transaction;
2622 BEGIN
2623 debug(' Size of work order operations table BEFORE ' || p_wop_table.COUNT);
2624 FORALL i IN p_wop_table.First..p_wop_table.last
2625 insert into eam_forecast_wo values p_wop_table(i);
2626 commit;
2627 debug(' Size of work order operations table AFTER ' || p_wop_table.COUNT);
2628 END insert_into_wop_auto;
2629
2630 --USAF
2631
2632
2633 PROCEDURE Copy_CEBBA_To_Forecast (
2634 p_api_version IN NUMBER,
2635 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2636 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2637 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2638 p_debug IN VARCHAR2 ,
2639
2640 p_forecast_rec IN eam_forecasts%ROWTYPE,
2641 p_wip_id_table IN wo_table_type,
2642
2643 --p_acct_period_from IN NUMBER,
2644 --p_acct_period_to IN NUMBER,
2645
2646 p_user_id IN NUMBER,
2647 p_request_id IN NUMBER,
2648 p_prog_id IN NUMBER,
2649 p_prog_app_id IN NUMBER,
2650 p_login_id IN NUMBER,
2651
2652 x_return_status OUT NOCOPY VARCHAR2,
2653 x_msg_count OUT NOCOPY NUMBER,
2654 x_msg_data OUT NOCOPY VARCHAR2)IS
2655
2656
2657
2658
2659 l_current_wo NUMBER;
2660 l_hist_cost_tbl eam_wo_relations_tbl_type;
2661
2662
2663
2664 BEGIN
2665
2666 -- Historical Costs
2667
2668 IF p_forecast_rec.forecast_type <> 4
2669
2670 THEN
2671 debug('CEBBA');
2672 for i in 1..p_wip_id_table.last loop
2673
2674 l_current_wo := p_wip_id_table(i);
2675 debug('Current wo is: ' || l_current_wo);
2676
2677 Get_HistoricalCosts(
2678 p_api_version => p_api_version,
2679 -- p_commit => p_commit,
2680 -- p_validation_level => p_validation_level,
2681 -- p_init_msg_list => p_init_msg_list,
2682 p_debug => p_debug,
2683
2684
2685 p_forecast_id => p_forecast_rec.forecast_id,
2686 p_organization_id => p_forecast_rec.organization_id,
2687 p_wip_entity_id => l_current_wo,
2688 p_account_from => p_forecast_rec.account_from,
2689 p_account_to => p_forecast_rec.account_to,
2690
2691 p_user_id => p_user_id,
2692 p_request_id => p_request_id,
2693 p_prog_id => p_prog_id,
2694 p_prog_app_id => p_prog_app_id,
2695 p_login_id => p_login_id,
2696
2697 x_hist_cost_tbl => l_hist_cost_tbl,
2698 x_return_status => x_return_status,
2699 x_msg_count => x_msg_count,
2700 x_msg_data => x_msg_data);
2701
2702 debug('Current wo is: ' || l_current_wo || ' GOT COST: ' || x_return_status);
2703 IF l_hist_cost_tbl IS NOT NULL AND l_hist_cost_tbl.count > 0
2704 THEN
2705 debug('Count is: ' || l_hist_cost_tbl.count, 2);
2706 for k in 1.. l_hist_cost_tbl.last loop
2707 debug('Current wo is: ' || l_current_wo || ' INSERTING COST ', 2);
2708
2709 debug(l_hist_cost_tbl(k).PERIOD_SET_NAME);
2710 debug(l_hist_cost_tbl(k).PERIOD_NAME);
2711 debug(l_hist_cost_tbl(k).ACCT_PERIOD_ID);
2712 debug(l_hist_cost_tbl(k).WIP_ENTITY_ID);
2713 debug(l_hist_cost_tbl(k).ORGANIZATION_ID);
2714 debug(l_hist_cost_tbl(k).OPERATIONS_DEPT_ID);
2715 debug(l_hist_cost_tbl(k).OPERATION_SEQ_NUM);
2716 debug(l_hist_cost_tbl(k).MAINT_COST_CATEGORY);
2717 debug(l_hist_cost_tbl(k).OWNING_DEPT_ID);
2718 debug(l_hist_cost_tbl(k).ACCT_VALUE);
2719 debug(l_hist_cost_tbl(k).PERIOD_START_DATE);
2720 debug(l_hist_cost_tbl(k).LAST_UPDATE_DATE);
2721 debug(l_hist_cost_tbl(k).LAST_UPDATED_BY);
2722 debug(l_hist_cost_tbl(k).CREATION_DATE);
2723 debug(l_hist_cost_tbl(k).CREATED_BY);
2724 debug(l_hist_cost_tbl(k).LAST_UPDATE_LOGIN);
2725 debug(l_hist_cost_tbl(k).REQUEST_ID);
2726 debug(l_hist_cost_tbl(k).PROGRAM_APPLICATION_ID);
2727 debug(l_hist_cost_tbl(k).PROGRAM_ID);
2728 debug(l_hist_cost_tbl(k).PROGRAM_UPDATE_DATE);
2729 debug(l_hist_cost_tbl(k).FORECAST_ID);
2730 debug(l_hist_cost_tbl(k).CCID);
2731 debug(l_hist_cost_tbl(k).MFG_COST_ELEMENT_ID);
2732 debug(l_hist_cost_tbl(k).PERIOD_YEAR);
2733 debug(l_hist_cost_tbl(k).PERIOD_NUM);
2734
2735
2736 INSERT INTO EAM_FORECAST_CEBBA (
2737 PERIOD_SET_NAME, PERIOD_NAME, ACCT_PERIOD_ID,
2738 WIP_ENTITY_ID, ORGANIZATION_ID, OPERATIONS_DEPT_ID,
2739 OPERATION_SEQ_NUM, MAINT_COST_CATEGORY, OWNING_DEPT_ID,
2740 ACCT_VALUE, PERIOD_START_DATE, LAST_UPDATE_DATE,
2741 LAST_UPDATED_BY, CREATION_DATE, CREATED_BY,
2742 LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID,
2743 PROGRAM_ID, PROGRAM_UPDATE_DATE, FORECAST_ID,
2744 CCID, MFG_COST_ELEMENT_ID, PERIOD_YEAR,
2745 PERIOD_NUM)
2746 VALUES(l_hist_cost_tbl(k).PERIOD_SET_NAME,
2747 l_hist_cost_tbl(k).PERIOD_NAME,
2748 l_hist_cost_tbl(k).ACCT_PERIOD_ID,
2749 l_hist_cost_tbl(k).WIP_ENTITY_ID,
2750 l_hist_cost_tbl(k).ORGANIZATION_ID,
2751 l_hist_cost_tbl(k).OPERATIONS_DEPT_ID,
2752 l_hist_cost_tbl(k).OPERATION_SEQ_NUM,
2753 l_hist_cost_tbl(k).MAINT_COST_CATEGORY,
2754 l_hist_cost_tbl(k).OWNING_DEPT_ID,
2755 l_hist_cost_tbl(k).ACCT_VALUE,
2756 l_hist_cost_tbl(k).PERIOD_START_DATE,
2757 l_hist_cost_tbl(k).LAST_UPDATE_DATE,
2758 l_hist_cost_tbl(k).LAST_UPDATED_BY,
2759 l_hist_cost_tbl(k).CREATION_DATE,
2760 l_hist_cost_tbl(k).CREATED_BY,
2761 l_hist_cost_tbl(k).LAST_UPDATE_LOGIN,
2762 l_hist_cost_tbl(k).REQUEST_ID,
2763 l_hist_cost_tbl(k).PROGRAM_APPLICATION_ID,
2764 l_hist_cost_tbl(k).PROGRAM_ID,
2765 l_hist_cost_tbl(k).PROGRAM_UPDATE_DATE,
2766 l_hist_cost_tbl(k).FORECAST_ID,
2767 l_hist_cost_tbl(k).CCID,
2768 l_hist_cost_tbl(k).MFG_COST_ELEMENT_ID,
2769 l_hist_cost_tbl(k).PERIOD_YEAR,
2770 l_hist_cost_tbl(k).PERIOD_NUM);
2771 debug('Current wo is: ' || l_current_wo || ' DOnE INSERTING COST ', 2);
2772 end loop;
2773 END IF;
2774
2775 end loop;
2776
2777 ELSE
2778 FORALL j IN p_wip_id_table.FIRST..p_wip_id_table.LAST
2779
2780 INSERT INTO EAM_FORECAST_CEBBA (
2781 PERIOD_SET_NAME, PERIOD_NAME, ACCT_PERIOD_ID,
2782 WIP_ENTITY_ID, ORGANIZATION_ID, OPERATIONS_DEPT_ID,
2783 OPERATION_SEQ_NUM, MAINT_COST_CATEGORY, OWNING_DEPT_ID,
2784 ACCT_VALUE, PERIOD_START_DATE, LAST_UPDATE_DATE,
2785 LAST_UPDATED_BY, CREATION_DATE, CREATED_BY,CCID,
2786 TXN_TYPE, MFG_COST_ELEMENT_ID, FORECAST_ID,
2787 PERIOD_YEAR,PERIOD_NUM)
2788
2789 SELECT PERIOD_SET_NAME, PERIOD_NAME, ACCT_PERIOD_ID,
2790 WIP_ENTITY_ID, ORGANIZATION_ID, OPERATIONS_DEPT_ID,
2791 OPERATIONS_SEQ_NUM, MAINT_COST_CATEGORY, OWNING_DEPT_ID,
2792 ACCT_VALUE, PERIOD_START_DATE, LAST_UPDATE_DATE ,
2793 LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, ACCOUNT_ID,
2794 TXN_TYPE, MFG_COST_ELEMENT_ID, p_forecast_rec.forecast_id as forecast_id,
2795 1 as period_year, 1 as period_type
2796
2797
2798 FROM CST_EAM_BALANCE_BY_ACCOUNTS
2799 WHERE wip_entity_id = p_wip_id_table(j);
2800
2801 END IF;
2802
2803 debug('COMPLETE COST COPY');
2804
2805 END Copy_CEBBA_To_Forecast;
2806
2807 PROCEDURE Copy_WEDI_To_Forecast (
2808 p_api_version IN NUMBER,
2809 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2810 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2811 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2812 p_debug IN VARCHAR2 ,
2813
2814 p_forecast_rec IN eam_forecasts%ROWTYPE,
2815 p_wip_id_table IN wo_table_type,
2816
2817 --p_acct_period_from IN NUMBER,
2818 --p_acct_period_to IN NUMBER,
2819
2820 p_user_id IN NUMBER,
2821 p_request_id IN NUMBER,
2822 p_prog_id IN NUMBER,
2823 p_prog_app_id IN NUMBER,
2824 p_login_id IN NUMBER,
2825
2826 x_return_status OUT NOCOPY VARCHAR2,
2827 x_msg_count OUT NOCOPY NUMBER,
2828 x_msg_data OUT NOCOPY VARCHAR2)IS
2829
2830 BEGIN
2831
2832 debug('WeDI');
2833 FORALL j IN p_wip_id_table.FIRST..p_wip_id_table.LAST
2834
2835 INSERT INTO EAM_FORECAST_WEDI (
2836 DESCRIPTION, PURCHASING_CATEGORY_ID, DIRECT_ITEM_SEQUENCE_ID,
2837 OPERATION_SEQ_NUM, DEPARTMENT_ID, WIP_ENTITY_ID,
2838 ORGANIZATION_ID, SUGGESTED_VENDOR_NAME, SUGGESTED_VENDOR_ID,
2839 SUGGESTED_VENDOR_SITE, SUGGESTED_VENDOR_SITE_ID, SUGGESTED_VENDOR_CONTACT,
2840 SUGGESTED_VENDOR_CONTACT_ID, SUGGESTED_VENDOR_PHONE, SUGGESTED_VENDOR_ITEM_NUM,
2841 UNIT_PRICE, AUTO_REQUEST_MATERIAL, REQUIRED_QUANTITY,
2842 UOM, NEED_BY_DATE, ATTRIBUTE_CATEGORY,
2843 ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3,
2844 ATTRIBUTE4, ATTRIBUTE5, ATTRIBUTE6,
2845 ATTRIBUTE7, ATTRIBUTE8, ATTRIBUTE9,
2846 ATTRIBUTE10, ATTRIBUTE11, ATTRIBUTE12,
2847 ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15,
2848 CREATED_BY, CREATION_DATE, LAST_UPDATE_DATE,
2849 LAST_UPDATE_LOGIN, LAST_UPDATED_BY, PROGRAM_APPLICATION_ID,
2850 PROGRAM_ID, PROGRAM_UPDATE_DATE, REQUEST_ID,
2851 FORECAST_ID)
2852
2853 SELECT DESCRIPTION, PURCHASING_CATEGORY_ID, DIRECT_ITEM_SEQUENCE_ID,
2854 OPERATION_SEQ_NUM, DEPARTMENT_ID, WIP_ENTITY_ID,
2855 ORGANIZATION_ID, SUGGESTED_VENDOR_NAME, SUGGESTED_VENDOR_ID,
2856 SUGGESTED_VENDOR_SITE, SUGGESTED_VENDOR_SITE_ID, SUGGESTED_VENDOR_CONTACT,
2857 SUGGESTED_VENDOR_CONTACT_ID, SUGGESTED_VENDOR_PHONE, SUGGESTED_VENDOR_ITEM_NUM,
2858 UNIT_PRICE, AUTO_REQUEST_MATERIAL, REQUIRED_QUANTITY,
2859 UOM, NEED_BY_DATE, ATTRIBUTE_CATEGORY,
2860 ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3,
2861 ATTRIBUTE4, ATTRIBUTE5, ATTRIBUTE6,
2862 ATTRIBUTE7, ATTRIBUTE8, ATTRIBUTE9,
2863 ATTRIBUTE10, ATTRIBUTE11, ATTRIBUTE12,
2864 ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15,
2865 CREATED_BY, CREATION_DATE, LAST_UPDATE_DATE,
2866 LAST_UPDATE_LOGIN, LAST_UPDATED_BY, PROGRAM_APPLICATION_ID,
2867 PROGRAM_ID, PROGRAM_UPDATE_DATE, REQUEST_ID,
2868 p_forecast_rec.forecast_id AS FORECAST_ID
2869
2870 FROM WIP_EAM_DIRECT_ITEMS
2871 WHERE wip_entity_id = p_wip_id_table(j);
2872
2873 debug('COMPLETE WEDI COPY');
2874
2875 END Copy_WEDI_To_Forecast;
2876
2877 --USAF
2878
2879 PROCEDURE Copy_wedi_To_Forecast_auto (
2880 p_api_version IN NUMBER,
2881 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2882 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2883 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2884 p_debug IN VARCHAR2 ,
2885
2886 p_forecast_rec IN eam_forecasts%ROWTYPE,
2887 p_wip_id_table IN wo_table_type,
2888
2889 --p_acct_period_from IN NUMBER,
2890 --p_acct_period_to IN NUMBER,
2891
2892 p_user_id IN NUMBER,
2893 p_request_id IN NUMBER,
2894 p_prog_id IN NUMBER,
2895 p_prog_app_id IN NUMBER,
2896 p_login_id IN NUMBER,
2897
2898 x_return_status OUT NOCOPY VARCHAR2,
2899 x_msg_count OUT NOCOPY NUMBER,
2900 x_msg_data OUT NOCOPY VARCHAR2)IS
2901
2902
2903
2904 l_wedi_table wedi_table_type;
2905 l_cebba_table cebba_table_type;
2906
2907
2908 BEGIN
2909 Copy_wedi_To_Forecast(
2910 p_api_version => p_api_version,
2911 p_commit => p_commit,
2912 p_validation_level => p_validation_level,
2913 p_init_msg_list => p_init_msg_list,
2914
2915 p_debug => p_debug,
2916
2917 p_forecast_rec => p_forecast_rec,
2918 p_wip_id_table => p_wip_id_table,
2919
2920 p_user_id => p_forecast_rec.last_updated_by,
2921 p_request_id => p_forecast_rec.request_id,
2922 p_prog_id => 1,
2923 p_prog_app_id => 1,
2924 p_login_id => 1,
2925
2926
2927 x_return_status => x_return_status,
2928 x_msg_count => x_msg_count,
2929 x_msg_data => x_msg_data);
2930
2931 debug('DONE Copying WO Op resources');
2932
2933 select * bulk collect INTO l_wedi_table
2934 from eam_forecast_wedi
2935 where forecast_id = p_forecast_rec.forecast_id;
2936
2937
2938
2939 debug(' Size of work order materials table ' || l_wedi_table.COUNT);
2940
2941 insert_into_wedi_auto(l_wedi_table);
2942
2943
2944 END Copy_wedi_To_Forecast_auto;
2945
2946 PROCEDURE insert_into_wedi_auto(p_wedi_table wedi_table_type)
2947 IS
2948 pragma autonomous_transaction;
2949 BEGIN
2950 debug(' Size of work order materials table BEFORE ' || p_wedi_table.COUNT);
2951 FORALL i IN p_wedi_table.First..p_wedi_table.last
2952 insert into eam_forecast_wedi values p_wedi_table(i);
2953 commit;
2954 debug(' Size of work order materials table AFTER ' || p_wedi_table.COUNT);
2955 END insert_into_wedi_auto;
2956
2957 --USAF
2958
2959
2960 PROCEDURE Populate_Test_Data(p_forecast_id IN NUMBER)
2961 IS
2962 l_msg_count NUMBER := 0;
2963 l_msg_data VARCHAR2(8000) := '';
2964 l_return_status VARCHAR2(2000);
2965
2966 BEGIN
2967
2968 Generate_Forecast(l_msg_data, l_msg_count,p_forecast_id);
2969
2970
2971
2972 END Populate_Test_Data;
2973
2974
2975
2976
2977 FUNCTION getForecastXml(p_forecast_id NUMBER)return CLOB IS
2978
2979
2980 l_xml XMLType;
2981 l_forecast_id NUMBER;
2982 l_sql VARCHAR2(32767);
2983 l_organization_id NUMBER;
2984 c_no_flag CONSTANT VARCHAR2(1) := 'N';
2985 c_pm_no_msg CONSTANT VARCHAR2(6) := 'Non-PM';
2986 c_pm_yes_msg CONSTANT VARCHAR2(2) := 'PM';
2987
2988
2989
2990 BEGIN
2991
2992
2993 l_forecast_id := p_forecast_id;
2994
2995 -- get the organization id of the forecast
2996 select organization_id into l_organization_id
2997 from eam_forecasts
2998 where forecast_id = l_forecast_id;
2999
3000 EAM_COMMON_UTILITIES_PVT.
3001 set_profile('MFG_ORGANIZATION_ID', l_organization_id);
3002
3003 l_sql := '
3004
3005 SELECT /*+ ordered use_nl(efc job msi entity msn loc msi2 bd hou.hao hou.haotl
3006 glcc) */
3007 XMLELEMENT("Forecast", XMLATTRIBUTES(ef.forecast_id AS "id"),
3008 XMLAGG(XMLELEMENT("WorkOrder", XMLATTRIBUTES(efc.wip_entity_id),
3009 XMLForest(
3010 (SELECT (XMLAgg(
3011 XMLELEMENT( "COST", null,
3012 xmlforest(
3013 periods.period_name as period,
3014 NVL(costs.cost, 0) AS VALUE
3015 )
3016 )
3017 ORDER BY periods.start_date
3018 )
3019 )
3020 FROM
3021
3022 (SELECT glp.period_name, glp.start_date, ef2.forecast_id
3023 FROM gl_periods glp, gl_periods glp2, gl_periods glp3, eam_forecasts ef2
3024 WHERE
3025 glp.start_date >= glp2.start_date
3026 AND glp.end_date <= glp3.end_date
3027 AND glp.period_type = glp2.period_type
3028 AND glp.period_set_name = glp2.period_set_name
3029 AND glp2.period_set_name = ef2.period_set_name_from
3030 AND glp2.period_name = ef2.period_from
3031 AND glp3.period_set_name = ef2.period_set_name_to
3032 AND glp3.period_name = ef2.period_to
3033 )periods,
3034
3035 (SELECT cebba.wip_entity_id , ef3.forecast_id, cebba.ccid, cebba.period_name AS period_name,SUM(cebba.acct_value) AS cost
3036 FROM eam_forecast_cebba cebba, eam_forecasts ef3
3037 WHERE cebba.forecast_id = ef3.forecast_id
3038 GROUP BY cebba.wip_entity_id, cebba.ccid, cebba.period_name, ef3.forecast_id
3039 ) costs
3040
3041 WHERE
3042 periods.forecast_id = ef.forecast_id
3043 AND periods.forecast_id = costs.forecast_id (+)
3044 AND periods.period_name = costs.period_name(+)
3045 AND efc.wip_entity_id = costs.wip_entity_id (+)
3046 AND efc.ccid = costs.ccid (+)
3047
3048
3049
3050 )AS ACCOUNT_COSTS,
3051
3052 entity.wip_entity_name AS NAME,
3053 glcc.concatenated_segments AS ACCOUNT,
3054 DECODE(NVL(job.plan_maintenance, :1), :2, :3, :4) AS SOURCE,
3055 hou.name AS ORGANIZATION,
3056 cii.instance_number AS ASSET,
3057 msi.concatenated_segments AS ASSETGROUP,
3058 msi2.concatenated_segments AS Activity,
3059 loc.location_codes AS AREA,
3060 mlWOtype.meaning AS WOTYPE,
3061 bd.department_code AS DEPARTMENT,
3062 job.class_code AS CLASS,
3063 pjm_project.all_proj_idtonum(job.project_id) AS PROJECT
3064 )
3065 )))
3066 FROM
3067 (
3068 SELECT /*+ no_merge */
3069 DISTINCT cebba.wip_entity_id,
3070 cebba.ccid,
3071 cebba.forecast_id AS id
3072 FROM eam_forecast_cebba cebba
3073 WHERE cebba.forecast_id = :6) efc,
3074 eam_forecast_wdj job, mtl_system_items_kfv msi,
3075 eam_forecasts ef, wip_entities entity,
3076 mtl_serial_numbers msn,
3077 csi_item_instances cii,
3078 mtl_eam_locations loc, mtl_system_items_kfv msi2,
3079 bom_departments bd, hr_organization_units hou,
3080 gl_code_combinations_kfv glcc,mfg_lookups mlWOtype
3081
3082
3083 WHERE
3084 efc.id = ef.forecast_id AND
3085 job.wip_entity_id = efc.wip_entity_id AND
3086 job.forecast_id = efc.id AND
3087 entity.wip_entity_id (+) = job.wip_entity_id AND
3088 glcc.code_combination_id = efc.ccid AND
3089 entity.entity_type (+) = DECODE(job.status_type,12,7,6) AND
3090 (msi.inventory_item_id = job.asset_group_id OR
3091 msi.inventory_item_id = job.rebuild_item_id) AND
3092 msi.organization_id = job.organization_id AND
3093 msn.inventory_item_id (+) = nvl(job.asset_group_id,job.rebuild_item_id) AND -- added nvl for bug 15893217 for rebuild
3094 msn.current_organization_id(+) = job.organization_id AND
3095 msn.serial_number(+) = nvl(job.asset_number,job.rebuild_serial_number) AND -- added nvl for bug 15893217
3096 cii.inventory_item_id (+) = nvl(job.asset_group_id,job.rebuild_item_id) AND -- added nvl for bug 15893217
3097 cii.last_vld_organization_id(+) = job.organization_id AND
3098 cii.serial_number(+) = nvl(job.asset_number,job.rebuild_serial_number) AND -- added nvl for bug 15893217
3099 loc.location_id (+) = msn.eam_location_id AND
3100 msi2.inventory_item_id (+) = job.primary_item_id AND
3101 msi2.organization_id (+) = job.organization_id AND
3102 bd.department_id (+) = job.owning_department AND
3103 hou.organization_id = job.organization_id AND
3104 mlWOtype.lookup_type(+) = ''WIP_EAM_WORK_ORDER_TYPE'' AND
3105 mlWOtype.enabled_flag(+) = ''Y'' AND
3106 mlWOtype.lookup_code (+) = job.work_order_type AND
3107 ef.forecast_id = :5
3108 GROUP BY ef.forecast_ID';
3109
3110
3111
3112
3113
3114 execute immediate l_sql into l_xml using c_no_flag, c_no_flag,
3115 c_pm_no_msg, c_pm_yes_msg, l_forecast_id, l_forecast_id;
3116
3117 return l_xml.getClobVal();
3118
3119
3120
3121 END getForecastXml;
3122
3123 procedure convert_work_orders(p_pm_group_id number,
3124 p_return_status OUT NOCOPY VARCHAR2,
3125 p_msg OUT NOCOPY VARCHAR2) IS
3126 l_group_id NUMBER;
3127 l_forecast_id NUMBER;
3128 l_old_flag VARCHAR2(1);
3129 l_req_id NUMBER;
3130
3131 -- parameters needed for the WO wrapper API call
3132 l_eam_wo_tbl eam_process_wo_pub.eam_wo_tbl_type;
3133 l_eam_wo_relations_tbl eam_process_wo_pub.eam_wo_relations_tbl_type;
3134 l_eam_op_tbl eam_process_wo_pub.eam_op_tbl_type;
3135 l_eam_op_network_tbl eam_process_wo_pub.eam_op_network_tbl_type;
3136 l_eam_res_tbl eam_process_wo_pub.eam_res_tbl_type;
3137 l_eam_res_inst_tbl eam_process_wo_pub.eam_res_inst_tbl_type;
3138 l_eam_sub_res_tbl eam_process_wo_pub.eam_sub_res_tbl_type;
3139 l_eam_res_usage_tbl eam_process_wo_pub.eam_res_usage_tbl_type;
3140 l_eam_mat_req_tbl eam_process_wo_pub.eam_mat_req_tbl_type;
3141 l_eam_direct_item_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
3142 l_eam_wo_comp_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_tbl_type;
3143 l_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
3144 l_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
3145 l_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
3146 l_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
3147 l_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
3148 l_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
3149 l_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
3150
3151 l_out_eam_wo_tbl eam_process_wo_pub.eam_wo_tbl_type;
3152 l_out_eam_wo_relations_tbl eam_process_wo_pub.eam_wo_relations_tbl_type;
3153 l_out_eam_op_tbl eam_process_wo_pub.eam_op_tbl_type;
3154 l_out_eam_op_network_tbl eam_process_wo_pub.eam_op_network_tbl_type;
3155 l_out_eam_res_tbl eam_process_wo_pub.eam_res_tbl_type;
3156 l_out_eam_res_inst_tbl eam_process_wo_pub.eam_res_inst_tbl_type;
3157 l_out_eam_sub_res_tbl eam_process_wo_pub.eam_sub_res_tbl_type;
3158 l_out_eam_res_usage_tbl eam_process_wo_pub.eam_res_usage_tbl_type;
3159 l_out_eam_mat_req_tbl eam_process_wo_pub.eam_mat_req_tbl_type;
3160 l_out_eam_direct_item_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
3161 l_out_eam_wo_comp_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_tbl_type;
3162 l_out_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
3163 l_out_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
3164 l_out_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
3165 l_out_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
3166 l_out_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
3167 l_out_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
3168 l_out_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
3169
3170 l_return_status VARCHAR2(1);
3171 l_msl_count NUMBER;
3172 l_message_text VARCHAR2(256);
3173 l_msl_text VARCHAR2(256);
3174 l_entity_index NUMBER;
3175 l_entity_id VARCHAR2(100);
3176 l_message_type VARCHAR2(100);
3177
3178 l_api_name CONSTANT VARCHAR2(30) := 'convert_work_orders';
3179
3180 l_module varchar2(200) ;
3181 l_log_level CONSTANT NUMBER := fnd_log.g_current_runtime_level;
3182 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level ;
3183 l_sLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_statement >= l_log_level;
3184
3185 -- This cursor returns all necessary fields to call the WO API.modified for ib
3186 -- Query changed for performance reasons.
3187 CURSOR c1 IS
3188 SELECT meaa.asset_activity_id, fw.pm_schedule_id, fw.action_type,
3189 fw.wip_entity_id, fw.wo_status, ewsv.system_status, fw.cycle_id, fw.seq_id,
3190 meaa.maintenance_object_type, meaa.maintenance_object_id,
3191 msi.inventory_item_id, msi.eam_item_type, fw.scheduled_start_date,
3192 fw.scheduled_completion_date, fw.organization_id organization_id,
3193 fw.pm_base_meter_reading
3194 from eam_forecasted_work_orders fw, mtl_eam_asset_activities meaa,
3195 eam_wo_statuses_v ewsv, csi_item_instances cii, mtl_system_items_b msi
3196 where group_id = l_group_id and
3197 fw.activity_association_id = meaa.activity_association_id and
3198 ewsv.status_id=fw.wo_status and meaa.maintenance_object_type = 3 and
3199 meaa.maintenance_object_id = cii.instance_id and cii.inventory_item_id =
3200 msi.inventory_item_id and cii.last_vld_organization_id = msi.organization_id
3201 union all
3202 SELECT meaa.asset_activity_id, fw.pm_schedule_id, fw.action_type,
3203 fw.wip_entity_id, fw.wo_status, ewsv.system_status, fw.cycle_id, fw.seq_id,
3204 meaa.maintenance_object_type, meaa.maintenance_object_id,
3205 meaa.maintenance_object_id, 3, fw.scheduled_start_date,
3206 fw.scheduled_completion_date, fw.organization_id organization_id,
3207 fw.pm_base_meter_reading
3208 from eam_forecasted_work_orders fw, mtl_eam_asset_activities meaa,
3209 eam_wo_statuses_v ewsv
3210 where group_id = l_group_id and fw.activity_association_id =
3211 meaa.activity_association_id and ewsv.status_id=fw.wo_status and
3212 meaa.maintenance_object_type = 2 ;
3213
3214 /*
3215 CURSOR c1 IS
3216 SELECT meaa.asset_activity_id, pm_schedule_id, action_type, fw.wip_entity_id, fw.wo_status,ewsv.system_status,fw.cycle_id,fw.seq_id,maintenance_object_type, maintenance_object_id,
3217 meaa.inventory_item_id, default_eam_class wip_acct_class,meaa.eam_item_type,
3218 scheduled_start_date, scheduled_completion_date, meaa.organization_id
3219 from eam_forecasted_work_orders fw, mtl_eam_asset_activities_v meaa,
3220 wip_eam_parameters wep, mtl_system_items msi,eam_wo_statuses_v ewsv
3221 where group_id = l_group_id
3222 and wep.organization_id = meaa.organization_id
3223 and fw.activity_association_id = meaa.activity_association_id
3224 and meaa.inventory_item_id = msi.inventory_item_id
3225 and meaa.organization_id = msi.organization_id
3226 and ewsv.status_id=fw.wo_status;
3227 */
3228
3229 sugg_rec c1%ROWTYPE;
3230
3231
3232 i number;
3233
3234 -- counter for relationship table
3235 j number;
3236 l_output_dir VARCHAR2(512);
3237 BEGIN
3238
3239 if (l_ulog) then
3240 l_module := 'eam.plsql.'||g_pkg_name|| '.' || l_api_name;
3241 end if;
3242 l_group_id := p_pm_group_id;
3243 l_eam_wo_tbl.delete;
3244 l_eam_wo_relations_tbl.delete;
3245
3246 i := 1;
3247
3248 debug('BEFORE LOOP EXPANDING WORK ORDERS');
3249 FOR sugg_rec in c1
3250
3251 LOOP
3252
3253 if(sugg_rec.action_type IN (2,6,7,1,4)) then
3254 debug('EXPANDING: ' || sugg_rec.action_type);
3255 l_eam_wo_tbl(i).plan_maintenance := 'Y';
3256
3257 l_eam_wo_tbl(i).transaction_type := eam_process_wo_pub.G_OPR_CREATE;
3258 l_eam_wo_tbl(i).source_code := 'BUD'; -- budget forecast ER 14323372
3259
3260 l_eam_wo_tbl(i).maintenance_object_source := 1; -- EAM
3261 l_eam_wo_tbl(i).maintenance_object_type := sugg_rec.maintenance_object_type;
3262 l_eam_wo_tbl(i).maintenance_object_id := sugg_rec.maintenance_object_id;
3263 l_eam_wo_tbl(i).class_code := null; -- WO API will default WAC
3264 -- l_eam_wo_tbl(i).status_type := 1; -- unreleased
3265
3266 --modified for bug 6715761
3267 l_eam_wo_tbl(i).status_type := 17; --sugg_rec.system_status;
3268 l_eam_wo_tbl(i).user_defined_status_id := 17;--sugg_rec.wo_status;
3269 l_eam_wo_tbl(i).cycle_id := sugg_rec.cycle_id;
3270 l_eam_wo_tbl(i).seq_id := sugg_rec.seq_id;
3271
3272 l_eam_wo_tbl(i).pm_schedule_id := sugg_rec.pm_schedule_id;
3273 l_eam_wo_tbl(i).asset_activity_id := sugg_rec.asset_activity_id;
3274
3275
3276 if(sugg_rec.scheduled_start_date is not null) then
3277 -- forward scheduling
3278 l_eam_wo_tbl(i).scheduled_start_date := sugg_rec.scheduled_start_date;
3279 -- dummy value here, it will be over-written by the scheduler
3280 l_eam_wo_tbl(i).scheduled_completion_date := sugg_rec.scheduled_start_date;
3281 l_eam_wo_tbl(i).requested_start_date := sugg_rec.scheduled_start_date;
3282 else
3283 -- forward scheduling
3284 l_eam_wo_tbl(i).scheduled_start_date := sugg_rec.scheduled_completion_date;
3285 -- dummy value here, it will be over-written by the scheduler
3286 l_eam_wo_tbl(i).scheduled_completion_date := sugg_rec.scheduled_completion_date;
3287 l_eam_wo_tbl(i).due_date := sugg_rec.scheduled_completion_date;
3288 end if;
3289
3290 l_eam_wo_tbl(i).organization_id := sugg_rec.organization_id;
3291
3292 if(sugg_rec.eam_item_type = 1) then
3293 -- asset
3294 l_eam_wo_tbl(i).asset_group_id := sugg_rec.inventory_item_id;
3295 else
3296 -- rebuildable
3297 l_eam_wo_tbl(i).rebuild_item_id := sugg_rec.inventory_item_id;
3298 end if;
3299
3300 -- common fields for all operations
3301 l_eam_wo_tbl(i).batch_id := p_pm_group_id;
3302 l_eam_wo_tbl(i).header_id := i;
3303
3304 i := i + 1;
3305 j := j + 1;
3306 end if;
3307
3308
3309 end loop;
3310
3311 /*
3312 delete from eam_forecasted_work_orders
3313 where group_id = l_group_id;
3314 */
3315
3316 EAM_WORKORDER_UTIL_PKG.log_path(l_output_dir);
3317 debug('CALLING PROCCESS: ' || l_eam_wo_tbl.COUNT);
3318
3319 eam_process_wo_pub.PROCESS_MASTER_CHILD_WO
3320 ( p_bo_identifier => 'EAM'
3321 , p_init_msg_list => TRUE
3322 , p_api_version_number => 1.0
3323 , p_eam_wo_relations_tbl => l_eam_wo_relations_tbl
3324 , p_eam_wo_tbl => l_eam_wo_tbl
3325
3326 -- dummy parameters as these are not used in PM
3327 , p_eam_op_tbl => l_eam_op_tbl
3328 , p_eam_op_network_tbl => l_eam_op_network_tbl
3329 , p_eam_res_tbl => l_eam_res_tbl
3330 , p_eam_res_inst_tbl => l_eam_res_inst_tbl
3331 , p_eam_sub_res_tbl => l_eam_sub_res_tbl
3332 , p_eam_mat_req_tbl => l_eam_mat_req_tbl
3333 , p_eam_direct_items_tbl => l_eam_direct_item_tbl
3334 , p_eam_res_usage_tbl => l_eam_res_usage_tbl
3335 , p_eam_wo_comp_tbl => l_eam_wo_comp_tbl
3336 , p_eam_wo_quality_tbl => l_eam_wo_quality_tbl
3337 , p_eam_meter_reading_tbl => l_eam_meter_reading_tbl
3338 , p_eam_counter_prop_tbl => l_eam_counter_prop_tbl
3339 , p_eam_wo_comp_rebuild_tbl => l_eam_wo_comp_rebuild_tbl
3340 , p_eam_wo_comp_mr_read_tbl => l_eam_wo_comp_mr_read_tbl
3341 , p_eam_op_comp_tbl => l_eam_op_comp_tbl
3342 , p_eam_request_tbl => l_eam_request_tbl
3343 , x_eam_direct_items_tbl => l_out_eam_direct_item_tbl
3344 , x_eam_res_usage_tbl => l_eam_res_usage_tbl
3345 , x_eam_wo_tbl => l_out_eam_wo_tbl
3346 , x_eam_wo_relations_tbl => l_out_eam_wo_relations_tbl
3347 , x_eam_op_tbl => l_out_eam_op_tbl
3348 , x_eam_op_network_tbl => l_out_eam_op_network_tbl
3349 , x_eam_res_tbl => l_out_eam_res_tbl
3350 , x_eam_res_inst_tbl => l_out_eam_res_inst_tbl
3351 , x_eam_sub_res_tbl => l_out_eam_sub_res_tbl
3352 , x_eam_mat_req_tbl => l_out_eam_mat_req_tbl
3353 , x_eam_wo_comp_tbl => l_out_eam_wo_comp_tbl
3354 , x_eam_wo_quality_tbl => l_out_eam_wo_quality_tbl
3355 , x_eam_meter_reading_tbl => l_out_eam_meter_reading_tbl
3356 , x_eam_counter_prop_tbl => l_out_eam_counter_prop_tbl
3357 , x_eam_wo_comp_rebuild_tbl => l_out_eam_wo_comp_rebuild_tbl
3358 , x_eam_wo_comp_mr_read_tbl => l_out_eam_wo_comp_mr_read_tbl
3359 , x_eam_op_comp_tbl => l_out_eam_op_comp_tbl
3360 , x_eam_request_tbl => l_out_eam_request_tbl
3361
3362 , p_commit => 'N'
3363 -- , x_error_msl_tbl OUT NOCOPY EAM_ERROR_MESSAGE_PVT.error_tbl_type
3364 , x_return_status => p_return_status
3365 , x_msg_count => l_msl_count
3366 , p_debug => NVL(fnd_profile.value('EAM_DEBUG'), 'N')
3367 , p_debug_filename => 'convertwo.log'
3368 , p_output_dir => l_output_dir
3369 );
3370
3371
3372 EAM_ERROR_MESSAGE_PVT.Get_Message(l_message_text, l_entity_index, l_entity_id, l_message_type);
3373 debug('Return status:' || p_return_status);
3374 debug('Error message:' || SUBSTRB(l_message_text,1,200));
3375 IF( l_slog ) THEN
3376 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,'Return status:' || p_return_status);
3377 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,'Error message:' || SUBSTRB(l_message_text,1,200));
3378 END IF;
3379 END convert_work_orders;
3380
3381
3382 END;