[Home] [Help]
PACKAGE BODY: APPS.EAM_FORECAST_PVT
Source
1 PACKAGE BODY "EAM_FORECAST_PVT" AS
2 /* $Header: EAMVFORB.pls 120.19.12000000.2 2007/04/20 04:52:49 bsurnida 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
501
502
503 -- worst case, historical must include asset information/query
504 l_sql VARCHAR2(8010);
505
506
507 l_sql_select VARCHAR2(1000);
508 l_sql_from VARCHAR2(4500);
509 l_sql_where VARCHAR2(2500);
510
511
512 BEGIN
513 debug('Getting wo table');
514 --_OUTPUT.enable(1000000);
515 -- Get the start date and end date information for the forecast
516
517 l_asset_query := false;
518
519 SELECT glp.start_date
520 INTO l_start_date
521 FROM gl_periods glp
522 WHERE glp.period_name = p_forecast_rec.period_from
523 AND glp.period_set_name = p_forecast_rec.period_set_name_from;
524
525 debug('Start date is ' || l_start_date);
526
527 SELECT glp.end_date
528 INTO l_end_date
529 FROM gl_periods glp
530 WHERE glp.period_name = p_forecast_rec.period_to
531 AND glp.period_set_name = p_forecast_rec.period_set_name_to;
532
533 debug('End date is ' || l_end_date);
534
535 l_sql_select := 'SELECT wdj.wip_entity_id ';
536 l_sql_from := 'FROM wip_discrete_jobs wdj ';
537 l_sql_where := 'WHERE ';
538
539 debug('Select stmt: ' || l_sql_select);
540 debug('FROM stmt: ' || l_sql_from);
541 debug('WHERE stmt: ' || l_sql_where);
542
543
544
545 IF p_forecast_rec.include_transacted = 'Y'
546 THEN
547 -- A historical forecast, must be driven by assets if transacted
548 --flag has been checked
549 l_sql_where := l_sql_where || ':organization_id IS NOT NULL ' ||
550 'AND :creation_date IS NOT NULL ';
551
552
553 ELSE
554 --Default case, organization_id is first selection criteria
555 l_sql_where := l_sql_where || 'wdj.organization_id = :organization_id ';
556 IF p_forecast_rec.forecast_type = 4
557 THEN
558 l_sql_where := l_sql_where || 'AND wdj.creation_date >= :creation_date ';
559 ELSE
560 l_sql_where := l_sql_where || 'AND :creation_date IS NOT NULL ';
561 END IF;
562 END IF;
563
564 --Add the PM criteria
565
566 -- Use asset criteria only if specified, OR If include transacted
567 IF (p_forecast_rec.asset_num_from IS NOT NULL OR
568 p_forecast_rec.asset_num_to IS NOT NULL OR
569 p_forecast_rec.asset_group_from IS NOT NULL OR
570 p_forecast_rec.asset_group_to IS NOT NULL OR
571 p_forecast_rec.asset_serial_num_from IS NOT NULL OR
572 p_forecast_rec.asset_serial_num_to IS NOT NULL OR
573 p_forecast_rec.include_transacted = 'Y') AND
574 p_forecast_rec.forecast_type <> 4
575 THEN
576 l_sql_from := l_sql_from || ',(' ||
577 get_asset_query(p_forecast_rec) || ') assets ';
578 l_sql_where := l_sql_where ||
579 'AND wdj.maintenance_object_id = assets.asset_id ' ||
580 'AND wdj.maintenance_object_type = assets.asset_type ';
581 l_asset_query := true;
582 -- Must be an eAM wip entry
583 ELSE
584
585 l_sql_where := l_sql_where ||
586 'AND wdj.maintenance_object_id IS NOT NULL ';
587 END IF;
588
589 IF p_forecast_rec.forecast_type IN(1,2,4)
590 THEN
591
592
593 IF p_forecast_rec.forecast_type = 2
594 THEN
595 l_sql_where := l_sql_where ||
596 'AND wdj.PM_SCHEDULE_ID IS NULL ';
597 ELSE
598 l_sql_where := l_sql_where ||
599 'AND wdj.PM_SCHEDULE_ID IS NOT NULL ';
600 END IF;
601
602 END IF;
603
604
605 --Add start date and end date criteria
606
607 l_sql_where := l_sql_where ||
608 'AND wdj.scheduled_completion_date >= :start_date ' ||
609 'AND wdj.scheduled_start_date <= :end_date ';
610
611 -- WO Number Criteria
612
613 IF p_forecast_rec.work_order_from IS NOT NULL AND
614 p_forecast_rec.work_order_to IS NOT NULL
615 THEN
616
617 l_sql_from := l_sql_from ||
618 ',wip_entities we ';
619 l_sql_where := l_sql_where ||
620 'AND wdj.wip_entity_id = we.wip_entity_id ' ||
621 'AND we.wip_entity_name >= :wo_from ' ||
622 'AND we.wip_entity_name <= :wo_to ';
623 ELSE
624
625 l_sql_where := l_sql_where ||
626 'AND (:wo_from IS NULL OR :wo_to IS NULL) ';
627
628
629 END IF;
630
631
632 -- WO Type Criteria
633
634 IF p_forecast_rec.work_order_type_from IS NOT NULL AND
635 p_forecast_rec.work_order_type_to IS NOT NULL
636 THEN
637
638 l_sql_where := l_sql_where ||
639 'AND wdj.work_order_type >= :wo_type_from ' ||
640 'AND wdj.work_order_type <= :wo_type_to ';
641 ELSE
642
643 l_sql_where := l_sql_where ||
644 'AND (:wo_type_from IS NULL OR :wo_type_to IS NULL) ';
645
646 END IF;
647
648
649 -- WAC criteria
650
651 IF p_forecast_rec.wip_acct_class_from IS NOT NULL AND
652 p_forecast_rec.wip_acct_class_to IS NOT NULL
653 THEN
654 l_sql_where := l_sql_where ||
655 'AND wdj.class_code >= :wac_from ' ||
656 'AND wdj.class_code <= :wac_to ';
657 ELSE
658 l_sql_where := l_sql_where ||
659 'AND (:wac_from IS NULL OR :wac_to IS NULL) ';
660 END IF;
661
662
663 -- Activity Criteria
664
665 IF p_forecast_rec.activity_from IS NOT NULL AND
666 p_forecast_rec.activity_to IS NOT NULL
667 THEN
668 l_sql_from := l_sql_from ||
669 ',mtl_system_items_b_kfv msi ';
670 l_sql_where := l_sql_where ||
671 'AND msi.organization_id = wdj.organization_id ' ||
672 'AND msi.inventory_item_id = wdj.primary_item_id ' ||
673 'AND msi.eam_item_type(+) = 2 ' ||
674 'AND msi.concatenated_segments >= :activity_from ' ||
675 'AND msi.concatenated_segments <= :activity_to ';
676 ELSE
677 l_sql_where := l_sql_where ||
678 'AND (:activity_from IS NULL OR :activity_to IS NULL) ';
679 END IF;
680
681
682 -- Department criteria
683
684 IF p_forecast_rec.department_from IS NOT NULL AND
685 p_forecast_rec.department_to IS NOT NULL
686 THEN
687 l_sql_from := l_sql_from ||
688 ',bom_departments bd ';
689 l_sql_where := l_sql_where ||
690 'AND bd.department_id (+) = wdj.owning_department ' ||
691 'AND bd.department_code >= :department_from ' ||
692 'AND bd.department_code <= :department_to ';
693 ELSE
694 l_sql_where := l_sql_where ||
695 'AND (:department_from IS NULL OR :department_to IS NULL) ';
696 END IF;
697
698 -- Project criteria
699
700 IF p_forecast_rec.project_from IS NOT NULL AND
701 p_forecast_rec.project_to IS NOT NULL
702 THEN
703 l_sql_from := l_sql_from ||
704 ',pa_projects pp ';
705 l_sql_where := l_sql_where ||
706 'AND pp.project_id (+) = wdj.project_id ' ||
707 'AND pp.name >= :project_from ' ||
708 'AND pp.name <= :project_to ';
709 ELSE
710 l_sql_where := l_sql_where ||
711 'AND (:project_from IS NULL OR :project_to IS NULL) ';
712 END IF;
713
714
715 -- Concatenate the clauses
716
717 l_sql := l_sql_select || l_sql_from || l_sql_where;
718
719 debug('Inserting sql');
720 /*
721 dbms_output.put_line(SUBSTR(l_sql,1,250));
722 dbms_output.put_line(SUBSTR(l_sql,250,250));
723 dbms_output.put_line(SUBSTR(l_sql,500,250));
724 dbms_output.put_line(SUBSTR(l_sql,750,250));
725 */
726
727 IF l_asset_query = true
728 THEN
729
730 debug('Opening cursor');
731
732 OPEN l_wip_cursor for l_sql USING
733
734 p_forecast_rec.organization_id,
735 p_forecast_rec.asset_num_from,
736 p_forecast_rec.asset_num_to,
737 p_forecast_rec.asset_serial_num_from,
738 p_forecast_rec.asset_serial_num_to,
739 p_forecast_rec.asset_group_from,
740 p_forecast_rec.asset_group_to,
741 p_forecast_rec.area_from,
742 p_forecast_rec.area_to,
743 p_forecast_rec.organization_id,
744 p_forecast_rec.asset_group_from,
745 p_forecast_rec.asset_group_to,
746
747 p_forecast_rec.organization_id,
748 p_forecast_rec.creation_date,
749 l_start_date,
750 l_end_date,
751 p_forecast_rec.work_order_from,
752 p_forecast_rec.work_order_to,
753 p_forecast_rec.work_order_type_from,
754 p_forecast_rec.work_order_type_to,
755 p_forecast_rec.wip_acct_class_from,
756 p_forecast_rec.wip_acct_class_to,
757 p_forecast_rec.activity_from,
758 p_forecast_rec.activity_to,
759 p_forecast_rec.department_from,
760 p_forecast_rec.department_to,
761 p_forecast_rec.project_from,
762 p_forecast_rec.project_to;
763
764
765
766 FETCH l_wip_cursor BULK COLLECT INTO l_wip_table;
767
768
769 ELSE
770 --Otherwise, the asset criteria has already been used before calling
771 --the pm engine
772
773 debug('Opening non-transact cursor');
774
775 OPEN l_wip_cursor for l_sql USING
776 p_forecast_rec.organization_id,
777 p_forecast_rec.creation_date,
778 l_start_date,
779 l_end_date,
780 p_forecast_rec.work_order_from,
781 p_forecast_rec.work_order_to,
782 p_forecast_rec.work_order_type_from,
783 p_forecast_rec.work_order_type_to,
784 p_forecast_rec.wip_acct_class_from,
785 p_forecast_rec.wip_acct_class_to,
786 p_forecast_rec.activity_from,
787 p_forecast_rec.activity_to,
788 p_forecast_rec.department_from,
789 p_forecast_rec.department_to,
790 p_forecast_rec.project_from,
791 p_forecast_rec.project_to;
792
793 FETCH l_wip_cursor BULK COLLECT INTO l_wip_table;
794 END IF;
795
796 debug('DONE Getting wo table: ' || l_wip_table.COUNT );
797 return l_wip_table;
798
799 END get_wip_table;
800
801
802
803 FUNCTION get_asset_cursor(p_organization_id IN NUMBER,p_asset_number_from IN VARCHAR2,
804 p_asset_number_to IN VARCHAR2, p_serial_number_from IN VARCHAR2,
805 p_serial_number_to IN VARCHAR2 , p_asset_group_from IN VARCHAR2,
806 p_asset_group_to IN VARCHAR2 , p_area_from IN VARCHAR2, p_area_to IN VARCHAR2)
807 RETURN forecast_asset_cursor_type
808 IS
809 l_asset_cursor forecast_asset_cursor_type;
810 l_asset_query VARCHAR2(4010);
811 BEGIN
812 l_asset_query := get_asset_query(p_organization_id,
813 p_asset_number_from,
814 p_asset_number_to,
815 p_serial_number_from,
816 p_serial_number_to,
817 p_asset_group_from,
818 p_asset_group_to,
819 p_area_from,
820 p_area_to);
821 /*
822 dbms_output.put_line(SUBSTR(l_asset_query,1,250));
823 dbms_output.put_line(SUBSTR(l_asset_query,250,250));
824 dbms_output.put_line(SUBSTR(l_asset_query,500,250));
825 dbms_output.put_line(SUBSTR(l_asset_query,750,250));
826 dbms_output.put_line(SUBSTR(l_asset_query,1000,250));
827 dbms_output.put_line(SUBSTR(l_asset_query,1250,250));
828 dbms_output.put_line(SUBSTR(l_asset_query,1500,250));
829 dbms_output.put_line(SUBSTR(l_asset_query,1750,250));
830 dbms_output.put_line(SUBSTR(l_asset_query,2000,250));
831 */
832 debug('opening asset cursor',2);
833 OPEN l_asset_cursor FOR l_asset_query USING p_organization_id, p_asset_number_from,
834 p_asset_number_to, p_serial_number_from ,
835 p_serial_number_to, p_asset_group_from ,
836 p_asset_group_to , p_area_from , p_area_to,
837 p_organization_id, p_asset_group_from,
838 p_asset_group_to;
839 debug('done opening asset cursor',2);
840 return l_asset_cursor;
841
842 END get_asset_cursor;
843
844 FUNCTION get_asset_query(p_forecast_rec eam_forecasts%rowtype)
845 RETURN VARCHAR2
846 IS
847
848 BEGIN
849 return get_asset_query(p_forecast_rec.organization_id,
850 p_forecast_rec.asset_num_from,
851 p_forecast_rec.asset_num_to,
852 p_forecast_rec.asset_serial_num_from,
853 p_forecast_rec.asset_serial_num_to,
854 p_forecast_rec.asset_group_from,
855 p_forecast_rec.asset_group_to,
856 p_forecast_rec.area_from,
857 p_forecast_rec.area_to);
858 END get_asset_query;
859
860 FUNCTION get_asset_query(p_organization_id IN NUMBER,p_asset_number_from IN VARCHAR2,
861 p_asset_number_to IN VARCHAR2, p_serial_number_from IN VARCHAR2,
862 p_serial_number_to IN VARCHAR2 , p_asset_group_from IN VARCHAR2,
863 p_asset_group_to IN VARCHAR2 , p_area_from IN VARCHAR2, p_area_to IN VARCHAR2)
864 RETURN VARCHAR2
865 IS
866 -- There are two running statements that will be joined in a union
867 -- One is for serialized items with entries in cii, and the other is
868 -- for non-serialized rebuildables, with entries in only msi.
869
870 l_sql_query VARCHAR2(4010);
871
872 l_sql_cii VARCHAR2(2000);
873 l_sql_cii_select VARCHAR2(100);
874 l_sql_cii_from VARCHAR2(200);
875 l_sql_cii_where VARCHAR2(1700);
876
877
878
879 l_sql_msi VARCHAR2(2000);
880 l_sql_msi_select VARCHAR2(100);
881 l_sql_msi_from VARCHAR2(200);
882 l_sql_msi_where VARCHAR2(1700);
883
884 BEGIN
885
886 debug('Inside get_asset_query', 2);
887 -- CII.instance_id as asset_id, serialized as type
888
889 l_sql_cii_select := 'SELECT cii.instance_id as asset_id, 3 as asset_type ';
890
891 --MSI.inventory_item_id as asset_id, nonserialized (2) as type
892
893 l_sql_msi_select := 'SELECT msi.inventory_item_id as asset_id, 2 as asset_type ';
894
895
896 l_sql_cii_from := 'FROM csi_item_instances cii,' ||
897 'eam_org_maint_defaults eomd,' ||
898 'mtl_parameters mp ';
899
900 l_sql_msi_from := 'FROM mtl_parameters mp ';
901
902 -- If asset group criteria is specified, join with msi kfv
903
904 IF p_asset_group_from IS NOT NULL AND p_asset_group_to IS NOT NULL
905 THEN
906 l_sql_cii_from := l_sql_cii_from || ',mtl_system_items_b_kfv msi ';
907 l_sql_msi_from := l_sql_msi_from || ',mtl_system_items_b_kfv msi ';
908 ELSE
909 l_sql_cii_from := l_sql_cii_from || ',mtl_system_items_b msi ';
910 l_sql_msi_from := l_sql_msi_from || ',mtl_system_items_b msi ';
911 END IF;
912
913 debug('Inside get_asset_query 1', 2);
914 -- Join with mtl_locations to get area if the area criteria is specified
915 IF p_area_from IS NOT NULL AND p_area_to IS NOT NULL
916 THEN
917 l_sql_cii_from := l_sql_cii_from || ',mtl_eam_locations mel ';
918
919 -- No asset area for non serialized rebuildables
920 END IF;
921
922 -- Add the mandatory where clauses
923 debug('Inside get_asset_query 2', 2);
924 l_sql_cii_where := 'WHERE ' ||
925 'msi.eam_item_type in (1,3) ' ||
926 'AND msi.inventory_item_id = cii.inventory_item_id ' ||
927 'AND msi.organization_id = cii.last_vld_organization_id ' ||
928 'AND msi.serial_number_control_code <> 1 ' ||
929 'AND nvl(cii.active_start_date, sysdate-1) <= sysdate ' ||
930 'AND nvl(cii.active_end_date, sysdate+1) >= sysdate ' ||
931 'AND cii.instance_id = eomd.object_id (+) ' ||
932 'AND eomd.object_type (+) = 50 ' ||
933 'AND (eomd.organization_id is null or eomd.organization_id ' ||
934 '= mp.maint_organization_id) ' ||
935 --organization criteria
936 'AND msi.organization_id = mp.organization_id ' ||
937 'AND mp.maint_organization_id = :organization_id ';
938
939 l_sql_msi_where := 'WHERE ' ||
940 'msi.eam_item_type = 3 ' ||
941 'AND msi.SERIAL_NUMBER_CONTROL_CODE = 1 ' ||
942 'AND msi.organization_id = mp.organization_id ' ||
943 'AND mp.maint_organization_id = :organization_id ';
944
945 -- Add the where clause for asset numbers
946 debug('Inside get_asset_query 3', 2);
947 IF p_asset_number_from IS NOT NULL AND p_asset_number_to IS NOT NULL
948 THEN
949 l_sql_cii_where := l_sql_cii_where ||
950 'AND cii.instance_number >= :asset_number_from ' ||
951 'AND cii.instance_number <= :asset_number_to ';
952 ELSE
953 l_sql_cii_where := l_sql_cii_where ||
954 'AND (:asset_number_from IS NULL OR :asset_number_to IS NULL) ';
955 END IF;
956
957 -- Add the where clause for asset serial numbers
958
959 IF p_serial_number_from IS NOT NULL AND p_serial_number_to IS NOT NULL
960 THEN
961 l_sql_cii_where := l_sql_cii_where ||
962 'AND cii.serial_number >= :serial_number_from ' ||
963 'AND cii.serial_number <= :serial_number_to ';
964 ELSE
965 l_sql_cii_where := l_sql_cii_where ||
966 'AND (:serial_number_from IS NULL OR :serial_number_to IS NULL) ';
967 END IF;
968 debug('Inside get_asset_query 4', 2);
969 -- Add the where clause for asset groups
970
971 IF p_asset_group_from IS NOT NULL AND p_asset_group_to IS NOT NULL
972 THEN
973 l_sql_cii_where := l_sql_cii_where ||
974 'AND msi.concatenated_segments >= :asset_group_from ' ||
975 'AND msi.concatenated_segments <= :asset_group_to ';
976 l_sql_msi_where := l_sql_msi_where ||
977 'AND msi.concatenated_segments >= :asset_group_from ' ||
978 'AND msi.concatenated_segments <= :asset_group_to ';
979 ELSE
980 l_sql_cii_where := l_sql_cii_where ||
981 'AND (:asset_group_from IS NULL OR :asset_group_to IS NULL) ';
982 l_sql_msi_where := l_sql_msi_where ||
983 'AND (:asset_group_from IS NULL OR :asset_group_to IS NULL) ';
984 END IF;
985
986 -- Add the where clause for asset area
987
988 IF p_area_from IS NOT NULL AND p_area_to IS NOT NULL
989 THEN
990 l_sql_cii_where := l_sql_cii_where ||
991 'AND mel.location_id = eomd.area_id ' ||
992 'AND mel.location_codes >= :area_from ' ||
993 'AND mel.location_codes <= :area_to ';
994 -- If area is specified, completely ignore non-serialized rebuildables
995 l_sql_msi := '';
996 ELSE
997 l_sql_cii_where := l_sql_cii_where ||
998 'AND (:area_from IS NULL OR :area_to IS NULL) ';
999 END IF;
1000
1001 -- Add the sql segments together
1002
1003 debug('Inside get_asset_query A', 2);
1004 l_sql_cii := l_sql_cii_select || l_sql_cii_from || l_sql_cii_where;
1005 l_sql_msi := l_sql_msi_select || l_sql_msi_from || l_sql_msi_where;
1006
1007 debug('Inside get_asset_query B', 2);
1008 -- Include the non-serialized rebuildables?
1009 IF p_area_from IS NOT NULL AND p_area_to IS NOT NULL
1010 THEN
1011 l_sql_query := l_sql_cii;
1012 ELSE
1013 l_sql_query := l_sql_cii || ' UNION ALL ' || l_sql_msi;
1014 END IF;
1015
1016 debug('Done with Inside get_asset_query', 2);
1017 -- Now bind the variables and open the cursor that retrieves all assets
1018
1019 return l_sql_query;
1020
1021 END get_asset_query;
1022
1023
1024 /* Loads the forecast criteria and branches on whether or not the forecast is
1025 historical or pm generated. */
1026
1027 -- TODO: OA needs to update the forecast criteria with the request id
1028
1029 PROCEDURE Extract_Forecast(
1030 p_api_version IN NUMBER,
1031 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1032 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1033 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1034 p_debug IN VARCHAR2 ,
1035 p_forecast_id IN eam_forecasts.forecast_id%TYPE,
1036 x_return_status OUT NOCOPY VARCHAR2 ,
1037 x_msg_count OUT NOCOPY NUMBER ,
1038 x_msg_data OUT NOCOPY VARCHAR2
1039
1040 )
1041 IS
1042
1043 l_api_name CONSTANT VARCHAR2(30) := 'Extract_Forecast';
1044 l_api_version CONSTANT NUMBER := 1.0;
1045 l_return_status VARCHAR2(1);
1046
1047 v_forecast_rec eam_forecasts%ROWTYPE;
1048
1049
1050 begin
1051 -- Standard Start of API savepoint
1052 SAVEPOINT Extract_Forecast_PVT;
1053 -- Standard call to check for call compatibility.
1054 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
1055 p_api_version ,
1056 l_api_name ,
1057 G_PKG_NAME
1058 )
1059 THEN
1060 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1061 END IF;
1062
1063 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1064 FND_MSG_PUB.initialize;
1065 END IF;
1066 -- Initialize API return status to success
1067 x_return_status := FND_API.G_RET_STS_SUCCESS;
1068
1069 SELECT * INTO v_forecast_rec
1070 FROM eam_forecasts
1071 WHERE forecast_id = p_forecast_id;
1072
1073 -- Set profile so org is visible to pjm_projects
1074
1075 EAM_COMMON_UTILITIES_PVT.
1076 set_profile('MFG_ORGANIZATION_ID', v_forecast_rec.organization_id);
1077
1078
1079 -- TODO: externalize lookup constants...best practice??
1080 IF v_forecast_rec.forecast_type <= 3 THEN
1081 debug('Extracting Historical Forecast');
1082
1083 extract_historical_forecast(
1084 p_api_version => p_api_version,
1085 p_commit => p_commit,
1086 p_validation_level => p_validation_level,
1087 p_init_msg_list => p_init_msg_list,
1088
1089 p_debug => p_debug,
1090
1091 p_forecast_rec => v_forecast_rec,
1092
1093
1094 p_user_id => v_forecast_rec.last_updated_by,
1095 p_request_id => v_forecast_rec.request_id,
1096 p_prog_id => 1,
1097 p_prog_app_id => 1,
1098 p_login_id => 1,
1099
1100
1101 x_return_status => x_return_status,
1102 x_msg_count => x_msg_count,
1103 x_msg_data => x_msg_data);
1104
1105 debug('DONE Extracting Historical Forecast');
1106 --extract_historical_forecast(v_forecast_rec);
1107 ELSE
1108 debug('Extracting FUTURE Forecast');
1109 extract_future_forecast(
1110 p_api_version => p_api_version,
1111 p_commit => p_commit,
1112 p_validation_level => p_validation_level,
1113 p_init_msg_list => p_init_msg_list,
1114
1115 p_debug => p_debug,
1116
1117 p_forecast_rec => v_forecast_rec,
1118
1119
1120 p_user_id => v_forecast_rec.last_updated_by,
1121 p_request_id => v_forecast_rec.request_id,
1122 p_prog_id => 1,
1123 p_prog_app_id => 1,
1124 p_login_id => 1,
1125
1126
1127 x_return_status => x_return_status,
1128 x_msg_count => x_msg_count,
1129 x_msg_data => x_msg_data);
1130 debug('DONE Extracting FUTURE Forecast');
1131 END IF;
1132
1133 -- Set OUT values
1134 -- Remove non-positive transaction values as well as work orders
1135 -- that have no costs associated with them
1136
1137 -- delete all costs not within the horizon
1138 delete from eam_forecast_cebba where forecast_id = p_forecast_id AND
1139 period_start_date > (select start_date from gl_periods
1140 where
1141 period_set_name = v_forecast_rec.period_set_name_to
1142 and period_name = v_forecast_rec.period_to);
1143
1144 -- delete all work orders that have zero costs associated
1145 delete from eam_forecast_wdj where forecast_id = p_forecast_id and wip_entity_id
1146 in
1147 (select wip_entity_id from (select wip_entity_id, sum(acct_value) as total from
1148 eam_forecast_cebba where forecast_id = p_forecast_id
1149 group by wip_entity_id) where total = 0);
1150
1151 delete from eam_forecast_wdj where forecast_id = p_forecast_id and
1152 wip_entity_id
1153 not in
1154 (select wip_entity_id from eam_forecast_cebba where forecast_id =
1155 p_forecast_id);
1156
1157
1158 -- Standard check of p_commit.
1159 IF FND_API.To_Boolean( p_commit ) THEN
1160 COMMIT WORK;
1161 END IF;
1162
1163 -- Standard call to get message count and if count is 1, get message info.
1164
1165 FND_MSG_PUB.Count_And_Get
1166 ( p_count => x_msg_count ,
1167 p_data => x_msg_data
1168 );
1169
1170 EXCEPTION
1171 WHEN no_data_found THEN
1172 ROLLBACK TO Extract_Forecast_PVT;
1173 x_return_status := FND_API.G_RET_STS_ERROR ;
1174 FND_MSG_PUB.Count_And_Get
1175 ( p_count => x_msg_count ,
1176 p_data => x_msg_data
1177 );
1178 WHEN OTHERS THEN
1179 ROLLBACK TO Extract_Forecast_PVT;
1180 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1181 IF FND_MSG_PUB.Check_Msg_Level
1182 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1183 THEN
1184 FND_MSG_PUB.Add_Exc_Msg
1185 ( G_FILE_NAME ,
1186 G_PKG_NAME ,
1187 l_api_name
1188 );
1189 END IF;
1190 FND_MSG_PUB.Count_And_Get
1191 ( p_count => x_msg_count ,
1192 p_data => x_msg_data
1193 );
1194
1195 end extract_forecast;
1196
1197
1198 -- Body definition (see forward declaration for more info)
1199 PROCEDURE extract_historical_forecast(
1200 p_api_version IN NUMBER,
1201 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1202 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1203 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1204 p_debug IN VARCHAR2 ,
1205
1206 p_forecast_rec IN eam_forecasts%ROWTYPE,
1207
1208
1209 --p_acct_period_from IN NUMBER,
1210 --p_acct_period_to IN NUMBER,
1211
1212 p_user_id IN NUMBER,
1213 p_request_id IN NUMBER,
1214 p_prog_id IN NUMBER,
1215 p_prog_app_id IN NUMBER,
1216 p_login_id IN NUMBER,
1217
1218 x_return_status OUT NOCOPY VARCHAR2,
1219 x_msg_count OUT NOCOPY NUMBER,
1220 x_msg_data OUT NOCOPY VARCHAR2)
1221 IS
1222 l_wip_table wo_table_type;
1223 begin
1224
1225 --Get the list of work orders to copy
1226
1227 l_wip_table := get_wip_table(p_forecast_rec);
1228
1229 --copy_wdj_to_forecast(l_wip_table, p_forecast_id
1230
1231 debug('Copying WDJ');
1232 Copy_WDJ_To_Forecast(
1233 p_api_version => p_api_version,
1234 p_commit => p_commit,
1235 p_validation_level => p_validation_level,
1236 p_init_msg_list => p_init_msg_list,
1237
1238 p_debug => p_debug,
1239
1240 p_forecast_rec => p_forecast_rec,
1241 p_wip_id_table => l_wip_table,
1242
1243 p_user_id => p_forecast_rec.last_updated_by,
1244 p_request_id => p_forecast_rec.request_id,
1245 p_prog_id => 1,
1246 p_prog_app_id => 1,
1247 p_login_id => 1,
1248
1249
1250 x_return_status => x_return_status,
1251 x_msg_count => x_msg_count,
1252 x_msg_data => x_msg_data);
1253
1254 debug('DONE Copying WDJ');
1255
1256 Copy_WO_To_Forecast(
1257 p_api_version => p_api_version,
1258 p_commit => p_commit,
1259 p_validation_level => p_validation_level,
1260 p_init_msg_list => p_init_msg_list,
1261
1262 p_debug => p_debug,
1263
1264 p_forecast_rec => p_forecast_rec,
1265 p_wip_id_table => l_wip_table,
1266
1267 p_user_id => p_forecast_rec.last_updated_by,
1268 p_request_id => p_forecast_rec.request_id,
1269 p_prog_id => 1,
1270 p_prog_app_id => 1,
1271 p_login_id => 1,
1272
1273
1274 x_return_status => x_return_status,
1275 x_msg_count => x_msg_count,
1276 x_msg_data => x_msg_data);
1277
1278 debug('DONE Copying WO');
1279
1280 Copy_WOR_To_Forecast(
1281 p_api_version => p_api_version,
1282 p_commit => p_commit,
1283 p_validation_level => p_validation_level,
1284 p_init_msg_list => p_init_msg_list,
1285
1286 p_debug => p_debug,
1287
1288 p_forecast_rec => p_forecast_rec,
1289 p_wip_id_table => l_wip_table,
1290
1291 p_user_id => p_forecast_rec.last_updated_by,
1292 p_request_id => p_forecast_rec.request_id,
1293 p_prog_id => 1,
1294 p_prog_app_id => 1,
1295 p_login_id => 1,
1296
1297
1298 x_return_status => x_return_status,
1299 x_msg_count => x_msg_count,
1300 x_msg_data => x_msg_data);
1301
1302 debug('DONE Copying WOR');
1303
1304 Copy_WRO_To_Forecast(
1305 p_api_version => p_api_version,
1306 p_commit => p_commit,
1307 p_validation_level => p_validation_level,
1308 p_init_msg_list => p_init_msg_list,
1309
1310 p_debug => p_debug,
1311
1312 p_forecast_rec => p_forecast_rec,
1313 p_wip_id_table => l_wip_table,
1314
1315 p_user_id => p_forecast_rec.last_updated_by,
1316 p_request_id => p_forecast_rec.request_id,
1317 p_prog_id => 1,
1318 p_prog_app_id => 1,
1319 p_login_id => 1,
1320
1321
1322 x_return_status => x_return_status,
1323 x_msg_count => x_msg_count,
1324 x_msg_data => x_msg_data);
1325
1326 debug('DONE Copying WRO');
1327
1328 Copy_WEDI_To_Forecast(
1329 p_api_version => p_api_version,
1330 p_commit => p_commit,
1331 p_validation_level => p_validation_level,
1332 p_init_msg_list => p_init_msg_list,
1333
1334 p_debug => p_debug,
1335
1336 p_forecast_rec => p_forecast_rec,
1337 p_wip_id_table => l_wip_table,
1338
1339 p_user_id => p_forecast_rec.last_updated_by,
1340 p_request_id => p_forecast_rec.request_id,
1341 p_prog_id => 1,
1342 p_prog_app_id => 1,
1343 p_login_id => 1,
1344
1345
1346 x_return_status => x_return_status,
1347 x_msg_count => x_msg_count,
1348 x_msg_data => x_msg_data);
1349
1350 debug('DONE Copying WEDI');
1351
1352 Copy_CEBBA_To_Forecast(
1353 p_api_version => p_api_version,
1354 p_commit => p_commit,
1355 p_validation_level => p_validation_level,
1356 p_init_msg_list => p_init_msg_list,
1357
1358 p_debug => p_debug,
1359
1360 p_forecast_rec => p_forecast_rec,
1361 p_wip_id_table => l_wip_table,
1362
1363 p_user_id => p_forecast_rec.last_updated_by,
1364 p_request_id => p_forecast_rec.request_id,
1365 p_prog_id => 1,
1366 p_prog_app_id => 1,
1367 p_login_id => 1,
1368
1369
1370 x_return_status => x_return_status,
1371 x_msg_count => x_msg_count,
1372 x_msg_data => x_msg_data);
1373
1374 debug('DONE Copying CEBBA');
1375 end extract_historical_forecast;
1376
1377 -- Body definition (see forward declaration for more info)
1378 PROCEDURE extract_future_forecast (
1379 p_api_version IN NUMBER,
1380 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1381 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1382 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1383 p_debug IN VARCHAR2 ,
1384
1385 p_forecast_rec IN eam_forecasts%ROWTYPE,
1386
1387
1388 --p_acct_period_from IN NUMBER,
1389 --p_acct_period_to IN NUMBER,
1390
1391 p_user_id IN NUMBER,
1392 p_request_id IN NUMBER,
1393 p_prog_id IN NUMBER,
1394 p_prog_app_id IN NUMBER,
1395 p_login_id IN NUMBER,
1396
1397 x_return_status OUT NOCOPY VARCHAR2,
1398 x_msg_count OUT NOCOPY NUMBER,
1399 x_msg_data OUT NOCOPY VARCHAR2)
1400 IS
1401 pragma autonomous_transaction;
1402 l_asset_cursor forecast_asset_cursor_type;
1403 l_asset_id NUMBER;
1404 l_asset_type NUMBER;
1405 l_pm_group_id NUMBER;
1406 l_forecast_id NUMBER;
1407 l_forecast_DATE date;
1408 l_start_date DATE;
1409 l_end_date DATE;
1410 l_forecast_rec EAM_FORECASTS%ROWTYPE;
1411 l_count NUMBER;
1412
1413
1414 BEGIN
1415
1416
1417 SELECT glp.start_date
1418 INTO l_start_date
1419 FROM gl_periods glp
1420 WHERE glp.period_name = p_forecast_rec.period_from
1421 AND glp.period_set_name = p_forecast_rec.period_set_name_from;
1422
1423 SELECT glp.end_date
1424 INTO l_end_date
1425 FROM gl_periods glp
1426 WHERE glp.period_name = p_forecast_rec.period_to
1427 AND glp.period_set_name = p_forecast_rec.period_set_name_to;
1428
1429 select wip_job_schedule_interface_s.nextval into l_pm_group_id from dual;
1430 debug('Before get_asset_cursor', 2);
1431 l_asset_cursor := get_asset_cursor(p_forecast_rec);
1432 debug('Exit get_asset_cursor', 2);
1433
1434 l_forecast_rec := p_forecast_rec;
1435
1436 l_forecast_rec.creation_date := SYSDATE;
1437
1438 debug('BEFORE PM ENGINE');
1439
1440 LOOP
1441
1442 FETCH l_asset_cursor INTO l_asset_id, l_asset_type;
1443 EXIT WHEN l_asset_cursor % NOTFOUND;
1444
1445 debug('BEFORE PM ENGINE: GROUP ID: ' || l_pm_group_id ||
1446 ' ASSET_ID: ' || l_asset_ID || ' ASSET_TYPE: ' ||
1447 l_asset_TYPE);
1448
1449 EAM_PM_ENGINE.do_forecast3(nonSched => 'N',
1450 startDate => l_start_date,
1451 endDate => l_end_date,
1452 orgID => p_forecast_rec.organization_id,
1453 userID => p_forecast_rec.last_update_login,
1454 objectID => l_asset_id,
1455 objectType => l_asset_type,
1456 setname_id => -1,
1457 combine_default => 'Y',
1458 group_id => l_pm_group_id);
1459
1460 debug('AFTER PM ENGINE: GROUP ID: ' || l_pm_group_id ||
1461 ' ASSET_ID: ' || l_asset_ID || ' ASSET_TYPE: ' ||
1462 l_asset_TYPE);
1463 END LOOP;
1464
1465 delete from eam_forecasted_work_orders where
1466 group_id = l_pm_group_id and
1467 scheduled_start_date < l_start_date;
1468
1469 --select count(*) into l_count from wip_discrete_jobs;
1470
1471 --debug('BEFORE CONVERT, WDJ: ' || l_count);
1472
1473 convert_work_orders(p_pm_group_id => l_pm_group_id,
1474 p_return_status => x_return_status,
1475 p_msg => x_msg_data);
1476
1477 --debug('AFTER CONVERT, WDJ: ' || l_count);
1478
1479 extract_autonomous_forecast(
1480 p_api_version => p_api_version,
1481 p_commit => p_commit,
1482 p_validation_level => p_validation_level,
1483 p_init_msg_list => p_init_msg_list,
1484
1485 p_debug => p_debug,
1486
1487 p_forecast_rec => l_forecast_rec,
1488
1489
1490 p_user_id => p_forecast_rec.last_updated_by,
1491 p_request_id => p_forecast_rec.request_id,
1492 p_prog_id => 1,
1493 p_prog_app_id => 1,
1494 p_login_id => 1,
1495
1496
1497 x_return_status => x_return_status,
1498 x_msg_count => x_msg_count,
1499 x_msg_data => x_msg_data);
1500
1501
1502 rollback;
1503
1504 END extract_future_forecast;
1505
1506 PROCEDURE extract_autonomous_forecast(
1507 p_api_version IN NUMBER,
1508 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1509 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1510 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1511 p_debug IN VARCHAR2 ,
1512
1513 p_forecast_rec IN eam_forecasts%ROWTYPE,
1514
1515
1516 --p_acct_period_from IN NUMBER,
1517 --p_acct_period_to IN NUMBER,
1518
1519 p_user_id IN NUMBER,
1520 p_request_id IN NUMBER,
1521 p_prog_id IN NUMBER,
1522 p_prog_app_id IN NUMBER,
1523 p_login_id IN NUMBER,
1524
1525 x_return_status OUT NOCOPY VARCHAR2,
1526 x_msg_count OUT NOCOPY NUMBER,
1527 x_msg_data OUT NOCOPY VARCHAR2)IS
1528
1529 l_wip_table wo_table_type;
1530 begin
1531
1532 --Get the list of work orders to copy
1533
1534 l_wip_table := get_wip_table(p_forecast_rec);
1535
1536 --copy_wdj_to_forecast(l_wip_table, p_forecast_id
1537
1538 debug('Copying WDJ AUTO: ' || l_wip_table.COUNT);
1539
1540 Copy_WDJ_To_Forecast_auto(
1541 p_api_version => p_api_version,
1542 p_commit => p_commit,
1543 p_validation_level => p_validation_level,
1544 p_init_msg_list => p_init_msg_list,
1545
1546 p_debug => p_debug,
1547
1548 p_forecast_rec => p_forecast_rec,
1549 p_wip_id_table => l_wip_table,
1550
1551 p_user_id => p_forecast_rec.last_updated_by,
1552 p_request_id => p_forecast_rec.request_id,
1553 p_prog_id => 1,
1554 p_prog_app_id => 1,
1555 p_login_id => 1,
1556
1557
1558 x_return_status => x_return_status,
1559 x_msg_count => x_msg_count,
1560 x_msg_data => x_msg_data);
1561
1562 debug('DONE Copying WDJ AUTO: ' || l_wip_table.COUNT);
1563 /*
1564 Copy_WO_To_Forecast(
1565 p_api_version => p_api_version,
1566 p_commit => p_commit,
1567 p_validation_level => p_validation_level,
1568 p_init_msg_list => p_init_msg_list,
1569
1570 p_debug => p_debug,
1571
1572 p_forecast_rec => p_forecast_rec,
1573 p_wip_id_table => l_wip_table,
1574
1575 p_user_id => p_forecast_rec.last_updated_by,
1576 p_request_id => p_forecast_rec.request_id,
1577 p_prog_id => 1,
1578 p_prog_app_id => 1,
1579 p_login_id => 1,
1580
1581
1582 x_return_status => x_return_status,
1583 x_msg_count => x_msg_count,
1584 x_msg_data => x_msg_data);
1585
1586 debug('DONE Copying WO');
1587
1588 Copy_WOR_To_Forecast(
1589 p_api_version => p_api_version,
1590 p_commit => p_commit,
1591 p_validation_level => p_validation_level,
1592 p_init_msg_list => p_init_msg_list,
1593
1594 p_debug => p_debug,
1595
1596 p_forecast_rec => p_forecast_rec,
1597 p_wip_id_table => l_wip_table,
1598
1599 p_user_id => p_forecast_rec.last_updated_by,
1600 p_request_id => p_forecast_rec.request_id,
1601 p_prog_id => 1,
1602 p_prog_app_id => 1,
1603 p_login_id => 1,
1604
1605
1606 x_return_status => x_return_status,
1607 x_msg_count => x_msg_count,
1608 x_msg_data => x_msg_data);
1609
1610 debug('DONE Copying WOR');
1611
1612 Copy_WRO_To_Forecast(
1613 p_api_version => p_api_version,
1614 p_commit => p_commit,
1615 p_validation_level => p_validation_level,
1616 p_init_msg_list => p_init_msg_list,
1617
1618 p_debug => p_debug,
1619
1620 p_forecast_rec => p_forecast_rec,
1621 p_wip_id_table => l_wip_table,
1622
1623 p_user_id => p_forecast_rec.last_updated_by,
1624 p_request_id => p_forecast_rec.request_id,
1625 p_prog_id => 1,
1626 p_prog_app_id => 1,
1627 p_login_id => 1,
1628
1629
1630 x_return_status => x_return_status,
1631 x_msg_count => x_msg_count,
1632 x_msg_data => x_msg_data);
1633
1634 debug('DONE Copying WRO');
1635
1636 Copy_WEDI_To_Forecast(
1637 p_api_version => p_api_version,
1638 p_commit => p_commit,
1639 p_validation_level => p_validation_level,
1640 p_init_msg_list => p_init_msg_list,
1641
1642 p_debug => p_debug,
1643
1644 p_forecast_rec => p_forecast_rec,
1645 p_wip_id_table => l_wip_table,
1646
1647 p_user_id => p_forecast_rec.last_updated_by,
1648 p_request_id => p_forecast_rec.request_id,
1649 p_prog_id => 1,
1650 p_prog_app_id => 1,
1651 p_login_id => 1,
1652
1653
1654 x_return_status => x_return_status,
1655 x_msg_count => x_msg_count,
1656 x_msg_data => x_msg_data);
1657
1658 debug('DONE Copying WEDI');
1659
1660
1661
1662
1663 Copy_CEBBA_To_Forecast_auto(
1664 p_api_version => p_api_version,
1665 p_commit => p_commit,
1666 p_validation_level => p_validation_level,
1667 p_init_msg_list => p_init_msg_list,
1668
1669 p_debug => p_debug,
1670
1671 p_forecast_rec => p_forecast_rec,
1672 p_wip_id_table => l_wip_table,
1673
1674 p_user_id => p_forecast_rec.last_updated_by,
1675 p_request_id => p_forecast_rec.request_id,
1676 p_prog_id => 1,
1677 p_prog_app_id => 1,
1678 p_login_id => 1,
1679
1680
1681 x_return_status => x_return_status,
1682 x_msg_count => x_msg_count,
1683 x_msg_data => x_msg_data);
1684
1685 debug('DONE Copying CEBBA');
1686 */
1687
1688 END extract_autonomous_forecast;
1689
1690
1691 PROCEDURE Copy_WDJ_To_Forecast (
1692 p_api_version IN NUMBER,
1693 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1694 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1695 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1696 p_debug IN VARCHAR2 ,
1697
1698 p_forecast_rec IN eam_forecasts%ROWTYPE,
1699 p_wip_id_table IN wo_table_type,
1700
1701 --p_acct_period_from IN NUMBER,
1702 --p_acct_period_to IN NUMBER,
1703
1704 p_user_id IN NUMBER,
1705 p_request_id IN NUMBER,
1706 p_prog_id IN NUMBER,
1707 p_prog_app_id IN NUMBER,
1708 p_login_id IN NUMBER,
1709
1710 x_return_status OUT NOCOPY VARCHAR2,
1711 x_msg_count OUT NOCOPY NUMBER,
1712 x_msg_data OUT NOCOPY VARCHAR2)IS
1713
1714 l_count NUMBER;
1715
1716 BEGIN
1717 FORALL j IN p_wip_id_table.FIRST..p_wip_id_table.LAST
1718
1719 INSERT INTO EAM_FORECAST_WDJ (
1720 WIP_ENTITY_ID, ORGANIZATION_ID, LAST_UPDATE_DATE,
1721 LAST_UPDATED_BY, CREATION_DATE, CREATED_BY,
1722 LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID,
1723 PROGRAM_ID, PROGRAM_UPDATE_DATE, SOURCE_LINE_ID,
1724 SOURCE_CODE, DESCRIPTION, STATUS_TYPE,
1725 PRIMARY_ITEM_ID, FIRM_PLANNED_FLAG, JOB_TYPE,
1726 WIP_SUPPLY_TYPE, CLASS_CODE, MATERIAL_ACCOUNT,
1727 MATERIAL_OVERHEAD_ACCOUNT, RESOURCE_ACCOUNT, OUTSIDE_PROCESSING_ACCOUNT,
1728 MATERIAL_VARIANCE_ACCOUNT, RESOURCE_VARIANCE_ACCOUNT, OUTSIDE_PROC_VARIANCE_ACCOUNT,
1729 STD_COST_ADJUSTMENT_ACCOUNT, OVERHEAD_ACCOUNT, OVERHEAD_VARIANCE_ACCOUNT,
1730 SCHEDULED_START_DATE, DATE_RELEASED, SCHEDULED_COMPLETION_DATE,
1731 DATE_COMPLETED, DATE_CLOSED, START_QUANTITY,
1732 QUANTITY_COMPLETED, QUANTITY_SCRAPPED, NET_QUANTITY,
1733 BOM_REFERENCE_ID, ROUTING_REFERENCE_ID, COMMON_BOM_SEQUENCE_ID,
1734 COMMON_ROUTING_SEQUENCE_ID, BOM_REVISION, ROUTING_REVISION,
1735 BOM_REVISION_DATE, ROUTING_REVISION_DATE, LOT_NUMBER,
1736 ALTERNATE_BOM_DESIGNATOR, ALTERNATE_ROUTING_DESIGNATOR, COMPLETION_SUBINVENTORY,
1737 COMPLETION_LOCATOR_ID, MPS_SCHEDULED_COMPLETION_DATE, MPS_NET_QUANTITY,
1738 DEMAND_CLASS, ATTRIBUTE_CATEGORY, ATTRIBUTE1,
1739 ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4,
1740 ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7,
1741 ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10,
1742 ATTRIBUTE11, ATTRIBUTE12, ATTRIBUTE13,
1743 ATTRIBUTE14, ATTRIBUTE15, SCHEDULE_GROUP_ID,
1744 BUILD_SEQUENCE, LINE_ID, PROJECT_ID,
1745 TASK_ID, KANBAN_CARD_ID, OVERCOMPLETION_TOLERANCE_TYPE,
1746 OVERCOMPLETION_TOLERANCE_VALUE, END_ITEM_UNIT_NUMBER, PO_CREATION_TIME,
1747 PRIORITY, DUE_DATE, EST_SCRAP_ACCOUNT,
1748 EST_SCRAP_VAR_ACCOUNT, EST_SCRAP_PRIOR_QTY, DUE_DATE_PENALTY,
1749 DUE_DATE_TOLERANCE, COPRODUCTS_SUPPLY, PARENT_WIP_ENTITY_ID,
1750 ASSET_NUMBER, ASSET_GROUP_ID, REBUILD_ITEM_ID,
1751 REBUILD_SERIAL_NUMBER, MANUAL_REBUILD_FLAG, SHUTDOWN_TYPE,
1752 ESTIMATION_STATUS, REQUESTED_START_DATE, NOTIFICATION_REQUIRED,
1753 WORK_ORDER_TYPE, OWNING_DEPARTMENT, ACTIVITY_TYPE,
1754 ACTIVITY_CAUSE, TAGOUT_REQUIRED, PLAN_MAINTENANCE,
1755 PM_SCHEDULE_ID, LAST_ESTIMATION_DATE, LAST_ESTIMATION_REQ_ID,
1756 ACTIVITY_SOURCE, SERIALIZATION_START_OP, MAINTENANCE_OBJECT_ID,
1757 MAINTENANCE_OBJECT_TYPE, MAINTENANCE_OBJECT_SOURCE, MATERIAL_ISSUE_BY_MO,
1758 SCHEDULING_REQUEST_ID, ISSUE_ZERO_COST_FLAG, EAM_LINEAR_LOCATION_ID,
1759 ACTUAL_START_DATE,
1760 EXPEDITED, EXPECTED_HOLD_RELEASE_DATE, FORECAST_ID)
1761
1762 SELECT WIP_ENTITY_ID, ORGANIZATION_ID, LAST_UPDATE_DATE,
1763 LAST_UPDATED_BY, CREATION_DATE, CREATED_BY,
1764 LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID,
1765 PROGRAM_ID, PROGRAM_UPDATE_DATE, SOURCE_LINE_ID,
1766 SOURCE_CODE, DESCRIPTION, STATUS_TYPE,
1767 PRIMARY_ITEM_ID, FIRM_PLANNED_FLAG, JOB_TYPE,
1768 WIP_SUPPLY_TYPE, CLASS_CODE, MATERIAL_ACCOUNT,
1769 MATERIAL_OVERHEAD_ACCOUNT, RESOURCE_ACCOUNT, OUTSIDE_PROCESSING_ACCOUNT,
1770 MATERIAL_VARIANCE_ACCOUNT, RESOURCE_VARIANCE_ACCOUNT, OUTSIDE_PROC_VARIANCE_ACCOUNT,
1771 STD_COST_ADJUSTMENT_ACCOUNT, OVERHEAD_ACCOUNT, OVERHEAD_VARIANCE_ACCOUNT,
1772 SCHEDULED_START_DATE, DATE_RELEASED, SCHEDULED_COMPLETION_DATE,
1773 DATE_COMPLETED, DATE_CLOSED, START_QUANTITY,
1774 QUANTITY_COMPLETED, QUANTITY_SCRAPPED, NET_QUANTITY,
1775 BOM_REFERENCE_ID, ROUTING_REFERENCE_ID, COMMON_BOM_SEQUENCE_ID,
1776 COMMON_ROUTING_SEQUENCE_ID, BOM_REVISION, ROUTING_REVISION,
1777 BOM_REVISION_DATE, ROUTING_REVISION_DATE, LOT_NUMBER,
1778 ALTERNATE_BOM_DESIGNATOR, ALTERNATE_ROUTING_DESIGNATOR, COMPLETION_SUBINVENTORY,
1779 COMPLETION_LOCATOR_ID, MPS_SCHEDULED_COMPLETION_DATE, MPS_NET_QUANTITY,
1780 DEMAND_CLASS, ATTRIBUTE_CATEGORY, ATTRIBUTE1,
1781 ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4,
1782 ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7,
1783 ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10,
1784 ATTRIBUTE11, ATTRIBUTE12, ATTRIBUTE13,
1785 ATTRIBUTE14, ATTRIBUTE15, SCHEDULE_GROUP_ID,
1786 BUILD_SEQUENCE, LINE_ID, PROJECT_ID,
1787 TASK_ID, KANBAN_CARD_ID, OVERCOMPLETION_TOLERANCE_TYPE,
1788 OVERCOMPLETION_TOLERANCE_VALUE, END_ITEM_UNIT_NUMBER, PO_CREATION_TIME,
1789 PRIORITY, DUE_DATE, EST_SCRAP_ACCOUNT,
1790 EST_SCRAP_VAR_ACCOUNT, EST_SCRAP_PRIOR_QTY, DUE_DATE_PENALTY,
1791 DUE_DATE_TOLERANCE, COPRODUCTS_SUPPLY, PARENT_WIP_ENTITY_ID,
1792 ASSET_NUMBER, ASSET_GROUP_ID, REBUILD_ITEM_ID,
1793 REBUILD_SERIAL_NUMBER, MANUAL_REBUILD_FLAG, SHUTDOWN_TYPE,
1794 ESTIMATION_STATUS, REQUESTED_START_DATE, NOTIFICATION_REQUIRED,
1795 WORK_ORDER_TYPE, OWNING_DEPARTMENT, ACTIVITY_TYPE,
1796 ACTIVITY_CAUSE, TAGOUT_REQUIRED, PLAN_MAINTENANCE,
1797 PM_SCHEDULE_ID, LAST_ESTIMATION_DATE, LAST_ESTIMATION_REQ_ID,
1798 ACTIVITY_SOURCE, SERIALIZATION_START_OP, MAINTENANCE_OBJECT_ID,
1799 MAINTENANCE_OBJECT_TYPE, MAINTENANCE_OBJECT_SOURCE, MATERIAL_ISSUE_BY_MO,
1800 SCHEDULING_REQUEST_ID, ISSUE_ZERO_COST_FLAG, EAM_LINEAR_LOCATION_ID,
1801 ACTUAL_START_DATE,
1802 EXPEDITED, EXPECTED_HOLD_RELEASE_DATE,
1803 p_forecast_rec.forecast_id AS FORECAST_ID
1804
1805 FROM WIP_DISCRETE_JOBS
1806 WHERE wip_entity_id = p_wip_id_table(j);
1807
1808 debug('COMPLETE WDJ COPY: ');
1809
1810 END Copy_WDJ_To_Forecast;
1811
1812 PROCEDURE Copy_WDJ_To_Forecast_auto (
1813 p_api_version IN NUMBER,
1814 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1815 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1816 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1817 p_debug IN VARCHAR2 ,
1818
1819 p_forecast_rec IN eam_forecasts%ROWTYPE,
1820 p_wip_id_table IN wo_table_type,
1821
1822 --p_acct_period_from IN NUMBER,
1823 --p_acct_period_to IN NUMBER,
1824
1825 p_user_id IN NUMBER,
1826 p_request_id IN NUMBER,
1827 p_prog_id IN NUMBER,
1828 p_prog_app_id IN NUMBER,
1829 p_login_id IN NUMBER,
1830
1831 x_return_status OUT NOCOPY VARCHAR2,
1832 x_msg_count OUT NOCOPY NUMBER,
1833 x_msg_data OUT NOCOPY VARCHAR2)IS
1834
1835
1836 l_wdj_table wdj_table_type;
1837 l_cebba_table cebba_table_type;
1838
1839
1840 BEGIN
1841 Copy_WDJ_To_Forecast(
1842 p_api_version => p_api_version,
1843 p_commit => p_commit,
1844 p_validation_level => p_validation_level,
1845 p_init_msg_list => p_init_msg_list,
1846
1847 p_debug => p_debug,
1848
1849 p_forecast_rec => p_forecast_rec,
1850 p_wip_id_table => p_wip_id_table,
1851
1852 p_user_id => p_forecast_rec.last_updated_by,
1853 p_request_id => p_forecast_rec.request_id,
1854 p_prog_id => 1,
1855 p_prog_app_id => 1,
1856 p_login_id => 1,
1857
1858
1859 x_return_status => x_return_status,
1860 x_msg_count => x_msg_count,
1861 x_msg_data => x_msg_data);
1862
1863 debug('DONE Copying WDJ');
1864
1865 select * bulk collect INTO l_wdj_table
1866 from eam_forecast_wdj
1867 where forecast_id = p_forecast_rec.forecast_id;
1868 Copy_CEBBA_To_Forecast(
1869 p_api_version => p_api_version,
1870 p_commit => p_commit,
1871 p_validation_level => p_validation_level,
1872 p_init_msg_list => p_init_msg_list,
1873
1874 p_debug => p_debug,
1875
1876 p_forecast_rec => p_forecast_rec,
1877 p_wip_id_table => p_wip_id_table,
1878
1879 p_user_id => p_forecast_rec.last_updated_by,
1880 p_request_id => p_forecast_rec.request_id,
1881 p_prog_id => 1,
1882 p_prog_app_id => 1,
1883 p_login_id => 1,
1884
1885
1886 x_return_status => x_return_status,
1887 x_msg_count => x_msg_count,
1888 x_msg_data => x_msg_data);
1889
1890 debug('DONE Copying CEBBA');
1891 select * bulk collect INTO l_cebba_table
1892 from eam_forecast_cebba
1893 where forecast_id = p_forecast_rec.forecast_id;
1894
1895
1896
1897
1898 debug(' Size of work order table ' || l_wdj_table.COUNT);
1899 ROLLBACK;
1900 insert_into_wdj_auto(l_wdj_table);
1901 insert_into_cebba_auto(l_cebba_table);
1902
1903 END Copy_WDJ_To_Forecast_auto;
1904
1905 PROCEDURE insert_into_wdj_auto(p_wdj_table wdj_table_type)
1906 IS
1907 pragma autonomous_transaction;
1908 BEGIN
1909 debug(' Size of work order table BEFORE ' || p_wdj_table.COUNT);
1910 FORALL i IN p_wdj_table.First..p_wdj_table.last
1911 insert into eam_forecast_wdj values p_wdj_table(i);
1912 commit;
1913 debug(' Size of work order table AFTER ' || p_wdj_table.COUNT);
1914 END insert_into_wdj_auto;
1915
1916 /*
1917 PROCEDURE insert_into_wdj_auto(p_wdj_table wdj_table_type)
1918 IS
1919 pragma autonomous_transaction;
1920 BEGIN
1921 debug(' Size of work order table BEFORE ' || p_wdj_table.COUNT);
1922 FOR i IN 1..p_wdj_table.last
1923 LOOP
1924 debug('inserting : ' || p_wdj_table(i).wip_entity_id);
1925 INSERT INTO EAM_FORECAST_WDJ (
1926 WIP_ENTITY_ID, ORGANIZATION_ID, LAST_UPDATE_DATE,
1927 LAST_UPDATED_BY, CREATION_DATE, CREATED_BY,
1928 LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID,
1929 PROGRAM_ID, PROGRAM_UPDATE_DATE, SOURCE_LINE_ID,
1930 SOURCE_CODE, DESCRIPTION, STATUS_TYPE,
1931 PRIMARY_ITEM_ID, FIRM_PLANNED_FLAG, JOB_TYPE,
1932 WIP_SUPPLY_TYPE, CLASS_CODE, MATERIAL_ACCOUNT,
1933 MATERIAL_OVERHEAD_ACCOUNT, RESOURCE_ACCOUNT, OUTSIDE_PROCESSING_ACCOUNT,
1934 MATERIAL_VARIANCE_ACCOUNT, RESOURCE_VARIANCE_ACCOUNT, OUTSIDE_PROC_VARIANCE_ACCOUNT,
1935 STD_COST_ADJUSTMENT_ACCOUNT, OVERHEAD_ACCOUNT, OVERHEAD_VARIANCE_ACCOUNT,
1936 SCHEDULED_START_DATE, DATE_RELEASED, SCHEDULED_COMPLETION_DATE,
1937 DATE_COMPLETED, DATE_CLOSED, START_QUANTITY,
1938 QUANTITY_COMPLETED, QUANTITY_SCRAPPED, NET_QUANTITY,
1939 BOM_REFERENCE_ID, ROUTING_REFERENCE_ID, COMMON_BOM_SEQUENCE_ID,
1940 COMMON_ROUTING_SEQUENCE_ID, BOM_REVISION, ROUTING_REVISION,
1941 BOM_REVISION_DATE, ROUTING_REVISION_DATE, LOT_NUMBER,
1942 ALTERNATE_BOM_DESIGNATOR, ALTERNATE_ROUTING_DESIGNATOR, COMPLETION_SUBINVENTORY,
1943 COMPLETION_LOCATOR_ID, MPS_SCHEDULED_COMPLETION_DATE, MPS_NET_QUANTITY,
1944 DEMAND_CLASS, ATTRIBUTE_CATEGORY, ATTRIBUTE1,
1945 ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4,
1946 ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7,
1947 ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10,
1948 ATTRIBUTE11, ATTRIBUTE12, ATTRIBUTE13,
1949 ATTRIBUTE14, ATTRIBUTE15, SCHEDULE_GROUP_ID,
1950 BUILD_SEQUENCE, LINE_ID, PROJECT_ID,
1951 TASK_ID, KANBAN_CARD_ID, OVERCOMPLETION_TOLERANCE_TYPE,
1952 OVERCOMPLETION_TOLERANCE_VALUE, END_ITEM_UNIT_NUMBER, PO_CREATION_TIME,
1953 PRIORITY, DUE_DATE, EST_SCRAP_ACCOUNT,
1954 EST_SCRAP_VAR_ACCOUNT, EST_SCRAP_PRIOR_QTY, DUE_DATE_PENALTY,
1955 DUE_DATE_TOLERANCE, COPRODUCTS_SUPPLY, PARENT_WIP_ENTITY_ID,
1956 ASSET_NUMBER, ASSET_GROUP_ID, REBUILD_ITEM_ID,
1957 REBUILD_SERIAL_NUMBER, MANUAL_REBUILD_FLAG, SHUTDOWN_TYPE,
1958 ESTIMATION_STATUS, REQUESTED_START_DATE, NOTIFICATION_REQUIRED,
1959 WORK_ORDER_TYPE, OWNING_DEPARTMENT, ACTIVITY_TYPE,
1960 ACTIVITY_CAUSE, TAGOUT_REQUIRED, PLAN_MAINTENANCE,
1961 PM_SCHEDULE_ID, LAST_ESTIMATION_DATE, LAST_ESTIMATION_REQ_ID,
1962 ACTIVITY_SOURCE, SERIALIZATION_START_OP, MAINTENANCE_OBJECT_ID,
1963 MAINTENANCE_OBJECT_TYPE, MAINTENANCE_OBJECT_SOURCE, MATERIAL_ISSUE_BY_MO,
1964 SCHEDULING_REQUEST_ID, ISSUE_ZERO_COST_FLAG, EAM_LINEAR_LOCATION_ID,
1965 ACTUAL_START_DATE,
1966 EXPEDITED, EXPECTED_HOLD_RELEASE_DATE, FORECAST_ID)
1967
1968 VALUES(
1969 p_wdj_table(i).WIP_ENTITY_ID, p_wdj_table(i).ORGANIZATION_ID, p_wdj_table(i).LAST_UPDATE_DATE,
1970 p_wdj_table(i).LAST_UPDATED_BY, p_wdj_table(i).CREATION_DATE, p_wdj_table(i).CREATED_BY,
1971 p_wdj_table(i).LAST_UPDATE_LOGIN, p_wdj_table(i).REQUEST_ID, p_wdj_table(i).PROGRAM_APPLICATION_ID,
1972 p_wdj_table(i).PROGRAM_ID, p_wdj_table(i).PROGRAM_UPDATE_DATE, p_wdj_table(i).SOURCE_LINE_ID,
1973 p_wdj_table(i).SOURCE_CODE, p_wdj_table(i).DESCRIPTION, p_wdj_table(i).STATUS_TYPE,
1974 p_wdj_table(i).PRIMARY_ITEM_ID, p_wdj_table(i).FIRM_PLANNED_FLAG, p_wdj_table(i).JOB_TYPE,
1975 p_wdj_table(i).WIP_SUPPLY_TYPE, p_wdj_table(i).CLASS_CODE, p_wdj_table(i).MATERIAL_ACCOUNT,
1976 p_wdj_table(i).MATERIAL_OVERHEAD_ACCOUNT, p_wdj_table(i).RESOURCE_ACCOUNT, p_wdj_table(i).OUTSIDE_PROCESSING_ACCOUNT,
1977 p_wdj_table(i).MATERIAL_VARIANCE_ACCOUNT, p_wdj_table(i).RESOURCE_VARIANCE_ACCOUNT, p_wdj_table(i).OUTSIDE_PROC_VARIANCE_ACCOUNT,
1978 p_wdj_table(i).STD_COST_ADJUSTMENT_ACCOUNT, p_wdj_table(i).OVERHEAD_ACCOUNT, p_wdj_table(i).OVERHEAD_VARIANCE_ACCOUNT,
1979 p_wdj_table(i).SCHEDULED_START_DATE, p_wdj_table(i).DATE_RELEASED, p_wdj_table(i).SCHEDULED_COMPLETION_DATE,
1980 p_wdj_table(i).DATE_COMPLETED, p_wdj_table(i).DATE_CLOSED, p_wdj_table(i).START_QUANTITY,
1981 p_wdj_table(i).QUANTITY_COMPLETED, p_wdj_table(i).QUANTITY_SCRAPPED, p_wdj_table(i).NET_QUANTITY,
1982 p_wdj_table(i).BOM_REFERENCE_ID, p_wdj_table(i).ROUTING_REFERENCE_ID, p_wdj_table(i).COMMON_BOM_SEQUENCE_ID,
1983 p_wdj_table(i).COMMON_ROUTING_SEQUENCE_ID, p_wdj_table(i).BOM_REVISION, p_wdj_table(i).ROUTING_REVISION,
1984 p_wdj_table(i).BOM_REVISION_DATE, p_wdj_table(i).ROUTING_REVISION_DATE, p_wdj_table(i).LOT_NUMBER,
1985 p_wdj_table(i).ALTERNATE_BOM_DESIGNATOR, p_wdj_table(i).ALTERNATE_ROUTING_DESIGNATOR, p_wdj_table(i).COMPLETION_SUBINVENTORY,
1986 p_wdj_table(i).COMPLETION_LOCATOR_ID, p_wdj_table(i).MPS_SCHEDULED_COMPLETION_DATE, p_wdj_table(i).MPS_NET_QUANTITY,
1987 p_wdj_table(i).DEMAND_CLASS, p_wdj_table(i).ATTRIBUTE_CATEGORY, p_wdj_table(i).ATTRIBUTE1,
1988 p_wdj_table(i).ATTRIBUTE2, p_wdj_table(i).ATTRIBUTE3, p_wdj_table(i).ATTRIBUTE4,
1989 p_wdj_table(i).ATTRIBUTE5, p_wdj_table(i).ATTRIBUTE6, p_wdj_table(i).ATTRIBUTE7,
1990 p_wdj_table(i).ATTRIBUTE8, p_wdj_table(i).ATTRIBUTE9, p_wdj_table(i).ATTRIBUTE10,
1991 p_wdj_table(i).ATTRIBUTE11, p_wdj_table(i).ATTRIBUTE12, p_wdj_table(i).ATTRIBUTE13,
1992 p_wdj_table(i).ATTRIBUTE14, p_wdj_table(i).ATTRIBUTE15, p_wdj_table(i).SCHEDULE_GROUP_ID,
1993 p_wdj_table(i).BUILD_SEQUENCE, p_wdj_table(i).LINE_ID, p_wdj_table(i).PROJECT_ID,
1994 p_wdj_table(i).TASK_ID, p_wdj_table(i).KANBAN_CARD_ID, p_wdj_table(i).OVERCOMPLETION_TOLERANCE_TYPE,
1995 p_wdj_table(i).OVERCOMPLETION_TOLERANCE_VALUE, p_wdj_table(i).END_ITEM_UNIT_NUMBER, p_wdj_table(i).PO_CREATION_TIME,
1996 p_wdj_table(i).PRIORITY, p_wdj_table(i).DUE_DATE, p_wdj_table(i).EST_SCRAP_ACCOUNT,
1997 p_wdj_table(i).EST_SCRAP_VAR_ACCOUNT, p_wdj_table(i).EST_SCRAP_PRIOR_QTY, p_wdj_table(i).DUE_DATE_PENALTY,
1998 p_wdj_table(i).DUE_DATE_TOLERANCE, p_wdj_table(i).COPRODUCTS_SUPPLY, p_wdj_table(i).PARENT_WIP_ENTITY_ID,
1999 p_wdj_table(i).ASSET_NUMBER, p_wdj_table(i).ASSET_GROUP_ID, p_wdj_table(i).REBUILD_ITEM_ID,
2000 p_wdj_table(i).REBUILD_SERIAL_NUMBER, p_wdj_table(i).MANUAL_REBUILD_FLAG, p_wdj_table(i).SHUTDOWN_TYPE,
2001 p_wdj_table(i).ESTIMATION_STATUS, p_wdj_table(i).REQUESTED_START_DATE, p_wdj_table(i).NOTIFICATION_REQUIRED,
2002 p_wdj_table(i).WORK_ORDER_TYPE, p_wdj_table(i).OWNING_DEPARTMENT, p_wdj_table(i).ACTIVITY_TYPE,
2003 p_wdj_table(i).ACTIVITY_CAUSE, p_wdj_table(i).TAGOUT_REQUIRED, p_wdj_table(i).PLAN_MAINTENANCE,
2004 p_wdj_table(i).PM_SCHEDULE_ID, p_wdj_table(i).LAST_ESTIMATION_DATE, p_wdj_table(i).LAST_ESTIMATION_REQ_ID,
2005 p_wdj_table(i).ACTIVITY_SOURCE, p_wdj_table(i).SERIALIZATION_START_OP, p_wdj_table(i).MAINTENANCE_OBJECT_ID,
2006 p_wdj_table(i).MAINTENANCE_OBJECT_TYPE, p_wdj_table(i).MAINTENANCE_OBJECT_SOURCE, p_wdj_table(i).MATERIAL_ISSUE_BY_MO,
2007 p_wdj_table(i).SCHEDULING_REQUEST_ID, p_wdj_table(i).ISSUE_ZERO_COST_FLAG, p_wdj_table(i).EAM_LINEAR_LOCATION_ID,
2008 p_wdj_table(i).ACTUAL_START_DATE,
2009 p_wdj_table(i).EXPEDITED, p_wdj_table(i).EXPECTED_HOLD_RELEASE_DATE, p_wdj_table(i).FORECAST_ID);
2010 END LOOP;
2011 commit;
2012 EXCEPTION
2013
2014 WHEN OTHERS THEN
2015 RAISE;
2016 debug(' Size of work order table AFTER ' || p_wdj_table.COUNT);
2017 END insert_into_wdj_auto;
2018 */
2019 PROCEDURE Copy_CEBBA_To_Forecast_auto (
2020 p_api_version IN NUMBER,
2021 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2022 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2023 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2024 p_debug IN VARCHAR2 ,
2025
2026 p_forecast_rec IN eam_forecasts%ROWTYPE,
2027 p_wip_id_table IN wo_table_type,
2028
2029 --p_acct_period_from IN NUMBER,
2030 --p_acct_period_to IN NUMBER,
2031
2032 p_user_id IN NUMBER,
2033 p_request_id IN NUMBER,
2034 p_prog_id IN NUMBER,
2035 p_prog_app_id IN NUMBER,
2036 p_login_id IN NUMBER,
2037
2038 x_return_status OUT NOCOPY VARCHAR2,
2039 x_msg_count OUT NOCOPY NUMBER,
2040 x_msg_data OUT NOCOPY VARCHAR2)IS
2041
2042
2043 l_cebba_table cebba_table_type;
2044
2045 BEGIN
2046 Copy_CEBBA_To_Forecast(
2047 p_api_version => p_api_version,
2048 p_commit => p_commit,
2049 p_validation_level => p_validation_level,
2050 p_init_msg_list => p_init_msg_list,
2051
2052 p_debug => p_debug,
2053
2054 p_forecast_rec => p_forecast_rec,
2055 p_wip_id_table => p_wip_id_table,
2056
2057 p_user_id => p_forecast_rec.last_updated_by,
2058 p_request_id => p_forecast_rec.request_id,
2059 p_prog_id => 1,
2060 p_prog_app_id => 1,
2061 p_login_id => 1,
2062
2063
2064 x_return_status => x_return_status,
2065 x_msg_count => x_msg_count,
2066 x_msg_data => x_msg_data);
2067
2068 debug('DONE Copying CEBBA');
2069 select * bulk collect INTO l_cebba_table
2070 from eam_forecast_cebba
2071 where forecast_id = p_forecast_rec.forecast_id;
2072 ROLLBACK;
2073
2074 insert_into_cebba_auto(l_cebba_table);
2075
2076 END Copy_CEBBA_To_Forecast_auto;
2077
2078
2079 PROCEDURE insert_into_cebba_auto(p_cebba_table cebba_table_type)
2080 IS
2081 pragma autonomous_transaction;
2082 BEGIN
2083 FORALL i IN p_cebba_table.First..p_cebba_table.last
2084 insert into eam_forecast_cebba values p_cebba_table(i);
2085 commit;
2086 END insert_into_cebba_auto;
2087
2088
2089 PROCEDURE Copy_WOR_To_Forecast (
2090 p_api_version IN NUMBER,
2091 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2092 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2093 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2094 p_debug IN VARCHAR2 ,
2095
2096 p_forecast_rec IN eam_forecasts%ROWTYPE,
2097 p_wip_id_table IN wo_table_type,
2098
2099 --p_acct_period_from IN NUMBER,
2100 --p_acct_period_to IN NUMBER,
2101
2102 p_user_id IN NUMBER,
2103 p_request_id IN NUMBER,
2104 p_prog_id IN NUMBER,
2105 p_prog_app_id IN NUMBER,
2106 p_login_id IN NUMBER,
2107
2108 x_return_status OUT NOCOPY VARCHAR2,
2109 x_msg_count OUT NOCOPY NUMBER,
2110 x_msg_data OUT NOCOPY VARCHAR2)IS
2111
2112 BEGIN
2113
2114 FORALL j IN p_wip_id_table.FIRST..p_wip_id_table.LAST
2115
2116 INSERT INTO EAM_FORECAST_WOR (
2117 WIP_ENTITY_ID, OPERATION_SEQ_NUM, RESOURCE_SEQ_NUM,
2118 ORGANIZATION_ID, REPETITIVE_SCHEDULE_ID, LAST_UPDATE_DATE,
2119 LAST_UPDATED_BY, CREATION_DATE, CREATED_BY,
2120 LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID,
2121 PROGRAM_ID, PROGRAM_UPDATE_DATE, RESOURCE_ID,
2122 UOM_CODE, BASIS_TYPE, USAGE_RATE_OR_AMOUNT,
2123 ACTIVITY_ID, SCHEDULED_FLAG, ASSIGNED_UNITS,
2124 AUTOCHARGE_TYPE, STANDARD_RATE_FLAG, APPLIED_RESOURCE_UNITS,
2125 APPLIED_RESOURCE_VALUE, START_DATE, COMPLETION_DATE,
2126 ATTRIBUTE_CATEGORY, ATTRIBUTE1, ATTRIBUTE2,
2127 ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5,
2128 ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8,
2129 ATTRIBUTE9, ATTRIBUTE10, ATTRIBUTE11,
2130 ATTRIBUTE12, ATTRIBUTE13, ATTRIBUTE14,
2131 ATTRIBUTE15, RELIEVED_RES_COMPLETION_UNITS, RELIEVED_RES_SCRAP_UNITS,
2132 RELIEVED_RES_COMPLETION_VALUE, RELIEVED_RES_SCRAP_VALUE, RELIEVED_VARIANCE_VALUE,
2133 TEMP_RELIEVED_VALUE, RELIEVED_RES_FINAL_COMP_UNITS, DEPARTMENT_ID,
2134 PHANTOM_FLAG, PHANTOM_OP_SEQ_NUM, PHANTOM_ITEM_ID,
2135 SCHEDULE_SEQ_NUM, SUBSTITUTE_GROUP_NUM, REPLACEMENT_GROUP_NUM,
2136 PRINCIPLE_FLAG, SETUP_ID, PARENT_RESOURCE_SEQ,
2137 BATCH_ID, RELIEVED_RES_UNITS, RELIEVED_RES_VALUE,
2138 MAXIMUM_ASSIGNED_UNITS, FIRM_FLAG, GROUP_SEQUENCE_ID,
2139 GROUP_SEQUENCE_NUMBER, ACTUAL_START_DATE, ACTUAL_COMPLETION_DATE,
2140 PROJECTED_COMPLETION_DATE, FORECAST_ID)
2141
2142
2143
2144 SELECT WIP_ENTITY_ID, OPERATION_SEQ_NUM, RESOURCE_SEQ_NUM,
2145 ORGANIZATION_ID, REPETITIVE_SCHEDULE_ID, LAST_UPDATE_DATE,
2146 LAST_UPDATED_BY, CREATION_DATE, CREATED_BY,
2147 LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID,
2148 PROGRAM_ID, PROGRAM_UPDATE_DATE, RESOURCE_ID,
2149 UOM_CODE, BASIS_TYPE, USAGE_RATE_OR_AMOUNT,
2150 ACTIVITY_ID, SCHEDULED_FLAG, ASSIGNED_UNITS,
2151 AUTOCHARGE_TYPE, STANDARD_RATE_FLAG, APPLIED_RESOURCE_UNITS,
2152 APPLIED_RESOURCE_VALUE, START_DATE, COMPLETION_DATE,
2153 ATTRIBUTE_CATEGORY, ATTRIBUTE1, ATTRIBUTE2,
2154 ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5,
2155 ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8,
2156 ATTRIBUTE9, ATTRIBUTE10, ATTRIBUTE11,
2157 ATTRIBUTE12, ATTRIBUTE13, ATTRIBUTE14,
2158 ATTRIBUTE15, RELIEVED_RES_COMPLETION_UNITS, RELIEVED_RES_SCRAP_UNITS,
2159 RELIEVED_RES_COMPLETION_VALUE, RELIEVED_RES_SCRAP_VALUE, RELIEVED_VARIANCE_VALUE,
2160 TEMP_RELIEVED_VALUE, RELIEVED_RES_FINAL_COMP_UNITS, DEPARTMENT_ID,
2161 PHANTOM_FLAG, PHANTOM_OP_SEQ_NUM, PHANTOM_ITEM_ID,
2162 SCHEDULE_SEQ_NUM, SUBSTITUTE_GROUP_NUM, REPLACEMENT_GROUP_NUM,
2163 PRINCIPLE_FLAG, SETUP_ID, PARENT_RESOURCE_SEQ,
2164 BATCH_ID, RELIEVED_RES_UNITS, RELIEVED_RES_VALUE,
2165 MAXIMUM_ASSIGNED_UNITS, FIRM_FLAG, GROUP_SEQUENCE_ID,
2166 GROUP_SEQUENCE_NUMBER, ACTUAL_START_DATE, ACTUAL_COMPLETION_DATE,
2167 PROJECTED_COMPLETION_DATE,p_forecast_rec.forecast_id AS FORECAST_ID
2168
2169 FROM WIP_OPERATION_RESOURCES
2170 WHERE wip_entity_id = p_wip_id_table(j);
2171
2172
2173 debug('COMPLETE WOR COPY');
2174
2175 END Copy_WOR_To_Forecast;
2176
2177
2178 PROCEDURE Copy_WRO_To_Forecast (
2179 p_api_version IN NUMBER,
2180 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2181 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2182 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2183 p_debug IN VARCHAR2 ,
2184
2185 p_forecast_rec IN eam_forecasts%ROWTYPE,
2186 p_wip_id_table IN wo_table_type,
2187
2188 --p_acct_period_from IN NUMBER,
2189 --p_acct_period_to IN NUMBER,
2190
2191 p_user_id IN NUMBER,
2192 p_request_id IN NUMBER,
2193 p_prog_id IN NUMBER,
2194 p_prog_app_id IN NUMBER,
2195 p_login_id IN NUMBER,
2196
2197 x_return_status OUT NOCOPY VARCHAR2,
2198 x_msg_count OUT NOCOPY NUMBER,
2199 x_msg_data OUT NOCOPY VARCHAR2)IS
2200
2201 BEGIN
2202
2203 FORALL j IN p_wip_id_table.FIRST..p_wip_id_table.LAST
2204
2205
2206 INSERT INTO EAM_FORECAST_WRO (
2207 INVENTORY_ITEM_ID, ORGANIZATION_ID, WIP_ENTITY_ID,
2208 OPERATION_SEQ_NUM, REPETITIVE_SCHEDULE_ID, LAST_UPDATE_DATE,
2209 LAST_UPDATED_BY, CREATION_DATE, CREATED_BY,
2210 LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID,
2211 PROGRAM_ID, PROGRAM_UPDATE_DATE, COMPONENT_SEQUENCE_ID,
2212 DEPARTMENT_ID, WIP_SUPPLY_TYPE, DATE_REQUIRED,
2213 REQUIRED_QUANTITY, QUANTITY_ISSUED, QUANTITY_PER_ASSEMBLY,
2214 COMMENTS, SUPPLY_SUBINVENTORY, SUPPLY_LOCATOR_ID,
2215 MRP_NET_FLAG, MPS_REQUIRED_QUANTITY, MPS_DATE_REQUIRED,
2216 SEGMENT1, SEGMENT2, SEGMENT3,
2217 SEGMENT4, SEGMENT5, SEGMENT6,
2218 SEGMENT7, SEGMENT8, SEGMENT9,
2219 SEGMENT10, SEGMENT11, SEGMENT12,
2220 SEGMENT13, SEGMENT14, SEGMENT15,
2221 SEGMENT16, SEGMENT17, SEGMENT18,
2222 SEGMENT19, SEGMENT20, ATTRIBUTE_CATEGORY,
2223 ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3,
2224 ATTRIBUTE4, ATTRIBUTE5, ATTRIBUTE6,
2225 ATTRIBUTE7, ATTRIBUTE8, ATTRIBUTE9,
2226 ATTRIBUTE10, ATTRIBUTE11, ATTRIBUTE12,
2227 ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15,
2228 RELIEVED_MATL_COMPLETION_QTY, RELIEVED_MATL_SCRAP_QUANTITY, RELIEVED_MATL_FINAL_COMP_QTY,
2229 QUANTITY_ALLOCATED, QUANTITY_BACKORDERED, QUANTITY_RELIEVED,
2230 COSTED_QUANTITY_ISSUED, COSTED_QUANTITY_RELIEVED, AUTO_REQUEST_MATERIAL,
2231 RELEASED_QUANTITY, SUGGESTED_VENDOR_NAME, VENDOR_ID,
2232 UNIT_PRICE, BASIS_TYPE,
2233 COMPONENT_YIELD_FACTOR, PRIMARY_COMPONENT_ID,FORECAST_ID)
2234
2235
2236
2237 SELECT INVENTORY_ITEM_ID, ORGANIZATION_ID, WIP_ENTITY_ID,
2238 OPERATION_SEQ_NUM, REPETITIVE_SCHEDULE_ID, LAST_UPDATE_DATE,
2239 LAST_UPDATED_BY, CREATION_DATE, CREATED_BY,
2240 LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID,
2241 PROGRAM_ID, PROGRAM_UPDATE_DATE, COMPONENT_SEQUENCE_ID,
2242 DEPARTMENT_ID, WIP_SUPPLY_TYPE, DATE_REQUIRED,
2243 REQUIRED_QUANTITY, QUANTITY_ISSUED, QUANTITY_PER_ASSEMBLY,
2244 COMMENTS, SUPPLY_SUBINVENTORY, SUPPLY_LOCATOR_ID,
2245 MRP_NET_FLAG, MPS_REQUIRED_QUANTITY, MPS_DATE_REQUIRED,
2246 SEGMENT1, SEGMENT2, SEGMENT3,
2247 SEGMENT4, SEGMENT5, SEGMENT6,
2248 SEGMENT7, SEGMENT8, SEGMENT9,
2249 SEGMENT10, SEGMENT11, SEGMENT12,
2250 SEGMENT13, SEGMENT14, SEGMENT15,
2251 SEGMENT16, SEGMENT17, SEGMENT18,
2252 SEGMENT19, SEGMENT20, ATTRIBUTE_CATEGORY,
2253 ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3,
2254 ATTRIBUTE4, ATTRIBUTE5, ATTRIBUTE6,
2255 ATTRIBUTE7, ATTRIBUTE8, ATTRIBUTE9,
2256 ATTRIBUTE10, ATTRIBUTE11, ATTRIBUTE12,
2257 ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15,
2258 RELIEVED_MATL_COMPLETION_QTY, RELIEVED_MATL_SCRAP_QUANTITY, RELIEVED_MATL_FINAL_COMP_QTY,
2259 QUANTITY_ALLOCATED, QUANTITY_BACKORDERED, QUANTITY_RELIEVED,
2260 COSTED_QUANTITY_ISSUED, COSTED_QUANTITY_RELIEVED, AUTO_REQUEST_MATERIAL,
2261 RELEASED_QUANTITY, SUGGESTED_VENDOR_NAME, VENDOR_ID,
2262 UNIT_PRICE, BASIS_TYPE, COMPONENT_YIELD_FACTOR,
2263 PRIMARY_COMPONENT_ID,
2264 p_forecast_rec.forecast_id AS FORECAST_ID
2265
2266 FROM WIP_REQUIREMENT_OPERATIONS
2267 WHERE wip_entity_id = p_wip_id_table(j);
2268 debug('COMPLETE WRO COPY');
2269
2270 END Copy_WRO_To_Forecast;
2271
2272
2273
2274 PROCEDURE Copy_WO_To_Forecast (
2275 p_api_version IN NUMBER,
2276 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2277 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2278 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2279 p_debug IN VARCHAR2 ,
2280
2281 p_forecast_rec IN eam_forecasts%ROWTYPE,
2282 p_wip_id_table IN wo_table_type,
2283
2284 --p_acct_period_from IN NUMBER,
2285 --p_acct_period_to IN NUMBER,
2286
2287 p_user_id IN NUMBER,
2288 p_request_id IN NUMBER,
2289 p_prog_id IN NUMBER,
2290 p_prog_app_id IN NUMBER,
2291 p_login_id IN NUMBER,
2292
2293 x_return_status OUT NOCOPY VARCHAR2,
2294 x_msg_count OUT NOCOPY NUMBER,
2295 x_msg_data OUT NOCOPY VARCHAR2)IS
2296
2297 BEGIN
2298 debug('COPYING WO');
2299 FORALL j IN p_wip_id_table.FIRST..p_wip_id_table.LAST
2300
2301 INSERT INTO EAM_FORECAST_WO (
2302 WIP_ENTITY_ID, OPERATION_SEQ_NUM, ORGANIZATION_ID,
2303 REPETITIVE_SCHEDULE_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY,
2304 CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN,
2305 REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID,
2306 PROGRAM_UPDATE_DATE, OPERATION_SEQUENCE_ID, STANDARD_OPERATION_ID,
2307 DEPARTMENT_ID, DESCRIPTION, SCHEDULED_QUANTITY,
2308 QUANTITY_IN_QUEUE, QUANTITY_RUNNING, QUANTITY_WAITING_TO_MOVE,
2309 QUANTITY_REJECTED, QUANTITY_SCRAPPED, QUANTITY_COMPLETED,
2310 FIRST_UNIT_START_DATE, FIRST_UNIT_COMPLETION_DATE, LAST_UNIT_START_DATE,
2311 LAST_UNIT_COMPLETION_DATE, PREVIOUS_OPERATION_SEQ_NUM, NEXT_OPERATION_SEQ_NUM,
2312 COUNT_POINT_TYPE, BACKFLUSH_FLAG, MINIMUM_TRANSFER_QUANTITY,
2313 DATE_LAST_MOVED, ATTRIBUTE_CATEGORY, ATTRIBUTE1,
2314 ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4,
2315 ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7,
2316 ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10,
2317 ATTRIBUTE11, ATTRIBUTE12, ATTRIBUTE13,
2318 ATTRIBUTE14, ATTRIBUTE15, WF_ITEMTYPE,
2319 WF_ITEMKEY, OPERATION_YIELD, OPERATION_YIELD_ENABLED,
2320 PRE_SPLIT_QUANTITY, OPERATION_COMPLETED, SHUTDOWN_TYPE,
2321 X_POS, Y_POS, PREVIOUS_OPERATION_SEQ_ID,
2322 SKIP_FLAG, LONG_DESCRIPTION, DISABLE_DATE,
2323 CUMULATIVE_SCRAP_QUANTITY, RECOMMENDED, PROGRESS_PERCENTAGE,
2324 WSM_OP_SEQ_NUM, ACTUAL_START_DATE, ACTUAL_COMPLETION_DATE,
2325 WSM_BONUS_QUANTITY, EMPLOYEE_ID,
2326 PROJECT_COMPLETION_DATE, WSM_UPDATE_QUANTITY_TXN_ID, WSM_UPDATE_QUANTITY_COMPLETED,
2327 LOWEST_ACCEPTABLE_YIELD, FORECAST_ID)
2328
2329
2330 SELECT WIP_ENTITY_ID, OPERATION_SEQ_NUM, ORGANIZATION_ID,
2331 REPETITIVE_SCHEDULE_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY,
2332 CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN,
2333 REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID,
2334 PROGRAM_UPDATE_DATE, OPERATION_SEQUENCE_ID, STANDARD_OPERATION_ID,
2335 DEPARTMENT_ID, DESCRIPTION, SCHEDULED_QUANTITY,
2336 QUANTITY_IN_QUEUE, QUANTITY_RUNNING, QUANTITY_WAITING_TO_MOVE,
2337 QUANTITY_REJECTED, QUANTITY_SCRAPPED, QUANTITY_COMPLETED,
2338 FIRST_UNIT_START_DATE, FIRST_UNIT_COMPLETION_DATE, LAST_UNIT_START_DATE,
2339 LAST_UNIT_COMPLETION_DATE, PREVIOUS_OPERATION_SEQ_NUM, NEXT_OPERATION_SEQ_NUM,
2340 COUNT_POINT_TYPE, BACKFLUSH_FLAG, MINIMUM_TRANSFER_QUANTITY,
2341 DATE_LAST_MOVED, ATTRIBUTE_CATEGORY, ATTRIBUTE1,
2342 ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4,
2343 ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7,
2344 ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10,
2345 ATTRIBUTE11, ATTRIBUTE12, ATTRIBUTE13,
2346 ATTRIBUTE14, ATTRIBUTE15, WF_ITEMTYPE,
2347 WF_ITEMKEY, OPERATION_YIELD, OPERATION_YIELD_ENABLED,
2348 PRE_SPLIT_QUANTITY, OPERATION_COMPLETED, SHUTDOWN_TYPE,
2349 X_POS, Y_POS, PREVIOUS_OPERATION_SEQ_ID,
2350 SKIP_FLAG, LONG_DESCRIPTION, DISABLE_DATE,
2351 CUMULATIVE_SCRAP_QUANTITY, RECOMMENDED, PROGRESS_PERCENTAGE,
2352 WSM_OP_SEQ_NUM, ACTUAL_START_DATE, ACTUAL_COMPLETION_DATE,
2353 WSM_BONUS_QUANTITY, EMPLOYEE_ID, PROJECTED_COMPLETION_DATE,
2354 WSM_UPDATE_QUANTITY_TXN_ID, WSM_COSTED_QUANTITY_COMPLETED, LOWEST_ACCEPTABLE_YIELD, p_forecast_rec.forecast_id AS FORECAST_ID
2355
2356 FROM WIP_OPERATIONS
2357 WHERE wip_entity_id = p_wip_id_table(j);
2358 debug('COMPLETE');
2359 EXCEPTION
2360
2361 WHEN no_data_found THEN
2362 --ROLLBACK TO Extract_Forecast_PVT;
2363 x_return_status := FND_API.G_RET_STS_ERROR ;
2364 FND_MSG_PUB.Count_And_Get
2365 ( p_count => x_msg_count ,
2366 p_data => x_msg_data
2367 );
2368 RAISE FND_API.G_EXC_ERROR;
2369 WHEN OTHERS THEN
2370 debug('EXCEPTION');
2371 --ROLLBACK TO Extract_Forecast_PVT;
2372 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2373 IF FND_MSG_PUB.Check_Msg_Level
2374 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2375 THEN
2376 FND_MSG_PUB.Add_Exc_Msg
2377 ( G_FILE_NAME ,
2378 G_PKG_NAME ,
2379 'Copy_WO_To_Forecast'
2380 );
2381 END IF;
2382 FND_MSG_PUB.Count_And_Get
2383 ( p_count => x_msg_count ,
2384 p_data => x_msg_data
2385 );
2386 RAISE FND_API.G_EXC_ERROR;
2387
2388
2389 END Copy_WO_To_Forecast;
2390
2391
2392 PROCEDURE Copy_CEBBA_To_Forecast (
2393 p_api_version IN NUMBER,
2394 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2395 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2396 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2397 p_debug IN VARCHAR2 ,
2398
2399 p_forecast_rec IN eam_forecasts%ROWTYPE,
2400 p_wip_id_table IN wo_table_type,
2401
2402 --p_acct_period_from IN NUMBER,
2403 --p_acct_period_to IN NUMBER,
2404
2405 p_user_id IN NUMBER,
2406 p_request_id IN NUMBER,
2407 p_prog_id IN NUMBER,
2408 p_prog_app_id IN NUMBER,
2409 p_login_id IN NUMBER,
2410
2411 x_return_status OUT NOCOPY VARCHAR2,
2412 x_msg_count OUT NOCOPY NUMBER,
2413 x_msg_data OUT NOCOPY VARCHAR2)IS
2414
2415
2416
2417
2418 l_current_wo NUMBER;
2419 l_hist_cost_tbl eam_wo_relations_tbl_type;
2420
2421
2422
2423 BEGIN
2424
2425 -- Historical Costs
2426
2427 IF p_forecast_rec.forecast_type <> 4
2428
2429 THEN
2430 debug('CEBBA');
2431 for i in 1..p_wip_id_table.last loop
2432
2433 l_current_wo := p_wip_id_table(i);
2434 debug('Current wo is: ' || l_current_wo);
2435
2436 Get_HistoricalCosts(
2437 p_api_version => p_api_version,
2438 -- p_commit => p_commit,
2439 -- p_validation_level => p_validation_level,
2440 -- p_init_msg_list => p_init_msg_list,
2441 p_debug => p_debug,
2442
2443
2444 p_forecast_id => p_forecast_rec.forecast_id,
2445 p_organization_id => p_forecast_rec.organization_id,
2446 p_wip_entity_id => l_current_wo,
2447 p_account_from => p_forecast_rec.account_from,
2448 p_account_to => p_forecast_rec.account_to,
2449
2450 p_user_id => p_user_id,
2451 p_request_id => p_request_id,
2452 p_prog_id => p_prog_id,
2453 p_prog_app_id => p_prog_app_id,
2454 p_login_id => p_login_id,
2455
2456 x_hist_cost_tbl => l_hist_cost_tbl,
2457 x_return_status => x_return_status,
2458 x_msg_count => x_msg_count,
2459 x_msg_data => x_msg_data);
2460
2461 debug('Current wo is: ' || l_current_wo || ' GOT COST: ' || x_return_status);
2462 IF l_hist_cost_tbl IS NOT NULL AND l_hist_cost_tbl.count > 0
2463 THEN
2464 debug('Count is: ' || l_hist_cost_tbl.count, 2);
2465 for k in 1.. l_hist_cost_tbl.last loop
2466 debug('Current wo is: ' || l_current_wo || ' INSERTING COST ', 2);
2467
2468 debug(l_hist_cost_tbl(k).PERIOD_SET_NAME);
2469 debug(l_hist_cost_tbl(k).PERIOD_NAME);
2470 debug(l_hist_cost_tbl(k).ACCT_PERIOD_ID);
2471 debug(l_hist_cost_tbl(k).WIP_ENTITY_ID);
2472 debug(l_hist_cost_tbl(k).ORGANIZATION_ID);
2473 debug(l_hist_cost_tbl(k).OPERATIONS_DEPT_ID);
2474 debug(l_hist_cost_tbl(k).OPERATION_SEQ_NUM);
2475 debug(l_hist_cost_tbl(k).MAINT_COST_CATEGORY);
2476 debug(l_hist_cost_tbl(k).OWNING_DEPT_ID);
2477 debug(l_hist_cost_tbl(k).ACCT_VALUE);
2478 debug(l_hist_cost_tbl(k).PERIOD_START_DATE);
2479 debug(l_hist_cost_tbl(k).LAST_UPDATE_DATE);
2480 debug(l_hist_cost_tbl(k).LAST_UPDATED_BY);
2481 debug(l_hist_cost_tbl(k).CREATION_DATE);
2482 debug(l_hist_cost_tbl(k).CREATED_BY);
2483 debug(l_hist_cost_tbl(k).LAST_UPDATE_LOGIN);
2484 debug(l_hist_cost_tbl(k).REQUEST_ID);
2485 debug(l_hist_cost_tbl(k).PROGRAM_APPLICATION_ID);
2486 debug(l_hist_cost_tbl(k).PROGRAM_ID);
2487 debug(l_hist_cost_tbl(k).PROGRAM_UPDATE_DATE);
2488 debug(l_hist_cost_tbl(k).FORECAST_ID);
2489 debug(l_hist_cost_tbl(k).CCID);
2490 debug(l_hist_cost_tbl(k).MFG_COST_ELEMENT_ID);
2491 debug(l_hist_cost_tbl(k).PERIOD_YEAR);
2492 debug(l_hist_cost_tbl(k).PERIOD_NUM);
2493
2494
2495 INSERT INTO EAM_FORECAST_CEBBA (
2496 PERIOD_SET_NAME, PERIOD_NAME, ACCT_PERIOD_ID,
2497 WIP_ENTITY_ID, ORGANIZATION_ID, OPERATIONS_DEPT_ID,
2498 OPERATION_SEQ_NUM, MAINT_COST_CATEGORY, OWNING_DEPT_ID,
2499 ACCT_VALUE, PERIOD_START_DATE, LAST_UPDATE_DATE,
2500 LAST_UPDATED_BY, CREATION_DATE, CREATED_BY,
2501 LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID,
2502 PROGRAM_ID, PROGRAM_UPDATE_DATE, FORECAST_ID,
2503 CCID, MFG_COST_ELEMENT_ID, PERIOD_YEAR,
2504 PERIOD_NUM)
2505 VALUES(l_hist_cost_tbl(k).PERIOD_SET_NAME,
2506 l_hist_cost_tbl(k).PERIOD_NAME,
2507 l_hist_cost_tbl(k).ACCT_PERIOD_ID,
2508 l_hist_cost_tbl(k).WIP_ENTITY_ID,
2509 l_hist_cost_tbl(k).ORGANIZATION_ID,
2510 l_hist_cost_tbl(k).OPERATIONS_DEPT_ID,
2511 l_hist_cost_tbl(k).OPERATION_SEQ_NUM,
2512 l_hist_cost_tbl(k).MAINT_COST_CATEGORY,
2513 l_hist_cost_tbl(k).OWNING_DEPT_ID,
2514 l_hist_cost_tbl(k).ACCT_VALUE,
2515 l_hist_cost_tbl(k).PERIOD_START_DATE,
2516 l_hist_cost_tbl(k).LAST_UPDATE_DATE,
2517 l_hist_cost_tbl(k).LAST_UPDATED_BY,
2518 l_hist_cost_tbl(k).CREATION_DATE,
2519 l_hist_cost_tbl(k).CREATED_BY,
2520 l_hist_cost_tbl(k).LAST_UPDATE_LOGIN,
2521 l_hist_cost_tbl(k).REQUEST_ID,
2522 l_hist_cost_tbl(k).PROGRAM_APPLICATION_ID,
2523 l_hist_cost_tbl(k).PROGRAM_ID,
2524 l_hist_cost_tbl(k).PROGRAM_UPDATE_DATE,
2525 l_hist_cost_tbl(k).FORECAST_ID,
2526 l_hist_cost_tbl(k).CCID,
2527 l_hist_cost_tbl(k).MFG_COST_ELEMENT_ID,
2528 l_hist_cost_tbl(k).PERIOD_YEAR,
2529 l_hist_cost_tbl(k).PERIOD_NUM);
2530 debug('Current wo is: ' || l_current_wo || ' DOnE INSERTING COST ', 2);
2531 end loop;
2532 END IF;
2533
2534 end loop;
2535
2536 ELSE
2537 FORALL j IN p_wip_id_table.FIRST..p_wip_id_table.LAST
2538
2539 INSERT INTO EAM_FORECAST_CEBBA (
2540 PERIOD_SET_NAME, PERIOD_NAME, ACCT_PERIOD_ID,
2541 WIP_ENTITY_ID, ORGANIZATION_ID, OPERATIONS_DEPT_ID,
2542 OPERATION_SEQ_NUM, MAINT_COST_CATEGORY, OWNING_DEPT_ID,
2543 ACCT_VALUE, PERIOD_START_DATE, LAST_UPDATE_DATE,
2544 LAST_UPDATED_BY, CREATION_DATE, CREATED_BY,CCID,
2545 TXN_TYPE, MFG_COST_ELEMENT_ID, FORECAST_ID,
2546 PERIOD_YEAR,PERIOD_NUM)
2547
2548 SELECT PERIOD_SET_NAME, PERIOD_NAME, ACCT_PERIOD_ID,
2549 WIP_ENTITY_ID, ORGANIZATION_ID, OPERATIONS_DEPT_ID,
2550 OPERATIONS_SEQ_NUM, MAINT_COST_CATEGORY, OWNING_DEPT_ID,
2551 ACCT_VALUE, PERIOD_START_DATE, LAST_UPDATE_DATE ,
2552 LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, ACCOUNT_ID,
2553 TXN_TYPE, MFG_COST_ELEMENT_ID, p_forecast_rec.forecast_id as forecast_id,
2554 1 as period_year, 1 as period_type
2555
2556
2557 FROM CST_EAM_BALANCE_BY_ACCOUNTS
2558 WHERE wip_entity_id = p_wip_id_table(j);
2559
2560 END IF;
2561
2562 debug('COMPLETE COST COPY');
2563
2564 END Copy_CEBBA_To_Forecast;
2565
2566 PROCEDURE Copy_WEDI_To_Forecast (
2567 p_api_version IN NUMBER,
2568 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2569 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2570 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2571 p_debug IN VARCHAR2 ,
2572
2573 p_forecast_rec IN eam_forecasts%ROWTYPE,
2574 p_wip_id_table IN wo_table_type,
2575
2576 --p_acct_period_from IN NUMBER,
2577 --p_acct_period_to IN NUMBER,
2578
2579 p_user_id IN NUMBER,
2580 p_request_id IN NUMBER,
2581 p_prog_id IN NUMBER,
2582 p_prog_app_id IN NUMBER,
2583 p_login_id IN NUMBER,
2584
2585 x_return_status OUT NOCOPY VARCHAR2,
2586 x_msg_count OUT NOCOPY NUMBER,
2587 x_msg_data OUT NOCOPY VARCHAR2)IS
2588
2589 BEGIN
2590
2591 debug('WeDI');
2592 FORALL j IN p_wip_id_table.FIRST..p_wip_id_table.LAST
2593
2594 INSERT INTO EAM_FORECAST_WEDI (
2595 DESCRIPTION, PURCHASING_CATEGORY_ID, DIRECT_ITEM_SEQUENCE_ID,
2596 OPERATION_SEQ_NUM, DEPARTMENT_ID, WIP_ENTITY_ID,
2597 ORGANIZATION_ID, SUGGESTED_VENDOR_NAME, SUGGESTED_VENDOR_ID,
2598 SUGGESTED_VENDOR_SITE, SUGGESTED_VENDOR_SITE_ID, SUGGESTED_VENDOR_CONTACT,
2599 SUGGESTED_VENDOR_CONTACT_ID, SUGGESTED_VENDOR_PHONE, SUGGESTED_VENDOR_ITEM_NUM,
2600 UNIT_PRICE, AUTO_REQUEST_MATERIAL, REQUIRED_QUANTITY,
2601 UOM, NEED_BY_DATE, ATTRIBUTE_CATEGORY,
2602 ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3,
2603 ATTRIBUTE4, ATTRIBUTE5, ATTRIBUTE6,
2604 ATTRIBUTE7, ATTRIBUTE8, ATTRIBUTE9,
2605 ATTRIBUTE10, ATTRIBUTE11, ATTRIBUTE12,
2606 ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15,
2607 CREATED_BY, CREATION_DATE, LAST_UPDATE_DATE,
2608 LAST_UPDATE_LOGIN, LAST_UPDATED_BY, PROGRAM_APPLICATION_ID,
2609 PROGRAM_ID, PROGRAM_UPDATE_DATE, REQUEST_ID,
2610 FORECAST_ID)
2611
2612 SELECT DESCRIPTION, PURCHASING_CATEGORY_ID, DIRECT_ITEM_SEQUENCE_ID,
2613 OPERATION_SEQ_NUM, DEPARTMENT_ID, WIP_ENTITY_ID,
2614 ORGANIZATION_ID, SUGGESTED_VENDOR_NAME, SUGGESTED_VENDOR_ID,
2615 SUGGESTED_VENDOR_SITE, SUGGESTED_VENDOR_SITE_ID, SUGGESTED_VENDOR_CONTACT,
2616 SUGGESTED_VENDOR_CONTACT_ID, SUGGESTED_VENDOR_PHONE, SUGGESTED_VENDOR_ITEM_NUM,
2617 UNIT_PRICE, AUTO_REQUEST_MATERIAL, REQUIRED_QUANTITY,
2618 UOM, NEED_BY_DATE, ATTRIBUTE_CATEGORY,
2619 ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3,
2620 ATTRIBUTE4, ATTRIBUTE5, ATTRIBUTE6,
2621 ATTRIBUTE7, ATTRIBUTE8, ATTRIBUTE9,
2622 ATTRIBUTE10, ATTRIBUTE11, ATTRIBUTE12,
2623 ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15,
2624 CREATED_BY, CREATION_DATE, LAST_UPDATE_DATE,
2625 LAST_UPDATE_LOGIN, LAST_UPDATED_BY, PROGRAM_APPLICATION_ID,
2626 PROGRAM_ID, PROGRAM_UPDATE_DATE, REQUEST_ID,
2627 p_forecast_rec.forecast_id AS FORECAST_ID
2628
2629 FROM WIP_EAM_DIRECT_ITEMS
2630 WHERE wip_entity_id = p_wip_id_table(j);
2631
2632 debug('COMPLETE WEDI COPY');
2633
2634 END Copy_WEDI_To_Forecast;
2635
2636
2637 PROCEDURE Populate_Test_Data(p_forecast_id IN NUMBER)
2638 IS
2639 l_msg_count NUMBER := 0;
2640 l_msg_data VARCHAR2(8000) := '';
2641 l_return_status VARCHAR2(2000);
2642
2643 BEGIN
2644
2645 Generate_Forecast(l_msg_data, l_msg_count,p_forecast_id);
2646
2647
2648
2649 END Populate_Test_Data;
2650
2651
2652
2653
2654 FUNCTION getForecastXml(p_forecast_id NUMBER)return CLOB IS
2655
2656
2657 l_xml XMLType;
2658 l_forecast_id NUMBER;
2659 l_sql VARCHAR2(32767);
2660 l_organization_id NUMBER;
2661 c_no_flag CONSTANT VARCHAR2(1) := 'N';
2662 c_pm_no_msg CONSTANT VARCHAR2(6) := 'Non-PM';
2663 c_pm_yes_msg CONSTANT VARCHAR2(2) := 'PM';
2664
2665
2666
2667 BEGIN
2668
2669
2670 l_forecast_id := p_forecast_id;
2671
2672 -- get the organization id of the forecast
2673 select organization_id into l_organization_id
2674 from eam_forecasts
2675 where forecast_id = l_forecast_id;
2676
2677 EAM_COMMON_UTILITIES_PVT.
2678 set_profile('MFG_ORGANIZATION_ID', l_organization_id);
2679
2680 l_sql := '
2681
2682 SELECT XMLELEMENT("Forecast", XMLATTRIBUTES(ef.forecast_id AS "id"),
2683 XMLAGG(XMLELEMENT("WorkOrder", XMLATTRIBUTES(efc.wip_entity_id),
2684 XMLForest(
2685 (SELECT (XMLAgg(
2686 XMLELEMENT( "COST", null,
2687 xmlforest(
2688 periods.period_name as period,
2689 NVL(costs.cost, 0) AS VALUE
2690 )
2691 )
2692 ORDER BY periods.start_date
2693 )
2694 )
2695 FROM
2696
2697 (SELECT glp.period_name, glp.start_date, ef2.forecast_id
2698 FROM gl_periods glp, gl_periods glp2, gl_periods glp3, eam_forecasts ef2
2699 WHERE
2700 glp.start_date >= glp2.start_date
2701 AND glp.end_date <= glp3.end_date
2702 AND glp.period_type = glp2.period_type
2703 AND glp.period_set_name = glp2.period_set_name
2704 AND glp2.period_set_name = ef2.period_set_name_from
2705 AND glp2.period_name = ef2.period_from
2706 AND glp3.period_set_name = ef2.period_set_name_to
2707 AND glp3.period_name = ef2.period_to
2708 )periods,
2709
2710 (SELECT cebba.wip_entity_id , ef3.forecast_id, cebba.ccid, cebba.period_name AS period_name,SUM(cebba.acct_value) AS cost
2711 FROM eam_forecast_cebba cebba, eam_forecasts ef3
2712 WHERE cebba.forecast_id = ef3.forecast_id
2713 GROUP BY cebba.wip_entity_id, cebba.ccid, cebba.period_name, ef3.forecast_id
2714 ) costs
2715
2716 WHERE
2717 periods.forecast_id = ef.forecast_id
2718 AND periods.forecast_id = costs.forecast_id (+)
2719 AND periods.period_name = costs.period_name(+)
2720 AND efc.wip_entity_id = costs.wip_entity_id (+)
2721 AND efc.ccid = costs.ccid (+)
2722
2723
2724
2725 )AS ACCOUNT_COSTS,
2726
2727 entity.wip_entity_name AS NAME,
2728 glcc.concatenated_segments AS ACCOUNT,
2729 DECODE(NVL(job.plan_maintenance, :1), :2, :3, :4) AS SOURCE,
2730 hou.name AS ORGANIZATION,
2731 job.asset_number AS ASSET,
2732 msi.concatenated_segments AS ASSETGROUP,
2733 msi2.concatenated_segments AS Activity,
2734 loc.location_codes AS AREA,
2735 job.work_order_type AS WOTYPE,
2736 bd.department_code AS DEPARTMENT,
2737 job.class_code AS CLASS,
2738 pjm_project.all_proj_idtonum(job.project_id) AS PROJECT
2739 )
2740 )))
2741 FROM eam_forecasts ef, eam_forecast_wdj job, wip_entities entity,
2742 mtl_system_items_kfv msi, mtl_serial_numbers msn,
2743 mtl_eam_locations loc, mtl_system_items_kfv msi2,
2744 bom_departments bd, hr_organization_units hou,
2745
2746 (SELECT DISTINCT wip_entity_id, ccid, forecast_id AS id
2747 FROM eam_forecast_cebba )efc,
2748
2749 gl_code_combinations_kfv glcc
2750
2751
2752 WHERE
2753 efc.id = ef.forecast_id AND
2754 job.wip_entity_id = efc.wip_entity_id AND
2755 entity.wip_entity_id (+) = job.wip_entity_id AND
2756 glcc.code_combination_id = efc.ccid AND
2757 entity.entity_type (+) = DECODE(job.status_type,12,7,6) AND
2758 (msi.inventory_item_id = job.asset_group_id OR
2759 msi.inventory_item_id = job.rebuild_item_id) AND
2760 msi.organization_id = job.organization_id AND
2761 msn.inventory_item_id (+) = job.asset_group_id AND
2762 msn.current_organization_id(+) = job.organization_id AND
2763 msn.serial_number(+) = job.asset_number AND
2764 loc.location_id (+) = msn.eam_location_id AND
2765 msi2.inventory_item_id (+) = job.primary_item_id AND
2766 msi2.organization_id (+) = job.organization_id AND
2767 bd.department_id (+) = job.owning_department AND
2768 hou.organization_id = job.organization_id AND
2769 ef.forecast_id = :5
2770 GROUP BY ef.forecast_ID';
2771
2772
2773
2774
2775
2776 execute immediate l_sql into l_xml using c_no_flag, c_no_flag,
2777 c_pm_no_msg, c_pm_yes_msg, l_forecast_id;
2778
2779 return l_xml.getClobVal();
2780
2781
2782
2783 END getForecastXml;
2784
2785 procedure convert_work_orders(p_pm_group_id number,
2786 p_return_status OUT NOCOPY VARCHAR2,
2787 p_msg OUT NOCOPY VARCHAR2) IS
2788 l_group_id NUMBER;
2789 l_forecast_id NUMBER;
2790 l_old_flag VARCHAR2(1);
2791 l_req_id NUMBER;
2792
2793 -- parameters needed for the WO wrapper API call
2794 l_eam_wo_tbl eam_process_wo_pub.eam_wo_tbl_type;
2795 l_eam_wo_relations_tbl eam_process_wo_pub.eam_wo_relations_tbl_type;
2796 l_eam_op_tbl eam_process_wo_pub.eam_op_tbl_type;
2797 l_eam_op_network_tbl eam_process_wo_pub.eam_op_network_tbl_type;
2798 l_eam_res_tbl eam_process_wo_pub.eam_res_tbl_type;
2799 l_eam_res_inst_tbl eam_process_wo_pub.eam_res_inst_tbl_type;
2800 l_eam_sub_res_tbl eam_process_wo_pub.eam_sub_res_tbl_type;
2801 l_eam_res_usage_tbl eam_process_wo_pub.eam_res_usage_tbl_type;
2802 l_eam_mat_req_tbl eam_process_wo_pub.eam_mat_req_tbl_type;
2803 l_eam_direct_item_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
2804 l_eam_wo_comp_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_tbl_type;
2805 l_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
2806 l_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
2807 l_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
2808 l_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
2809 l_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
2810 l_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
2811 l_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
2812
2813 l_out_eam_wo_tbl eam_process_wo_pub.eam_wo_tbl_type;
2814 l_out_eam_wo_relations_tbl eam_process_wo_pub.eam_wo_relations_tbl_type;
2815 l_out_eam_op_tbl eam_process_wo_pub.eam_op_tbl_type;
2816 l_out_eam_op_network_tbl eam_process_wo_pub.eam_op_network_tbl_type;
2817 l_out_eam_res_tbl eam_process_wo_pub.eam_res_tbl_type;
2818 l_out_eam_res_inst_tbl eam_process_wo_pub.eam_res_inst_tbl_type;
2819 l_out_eam_sub_res_tbl eam_process_wo_pub.eam_sub_res_tbl_type;
2820 l_out_eam_res_usage_tbl eam_process_wo_pub.eam_res_usage_tbl_type;
2821 l_out_eam_mat_req_tbl eam_process_wo_pub.eam_mat_req_tbl_type;
2822 l_out_eam_direct_item_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
2823 l_out_eam_wo_comp_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_tbl_type;
2824 l_out_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
2825 l_out_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
2826 l_out_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
2827 l_out_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
2828 l_out_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
2829 l_out_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
2830 l_out_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
2831
2832 l_return_status VARCHAR2(1);
2833 l_msl_count NUMBER;
2834 l_message_text VARCHAR2(256);
2835 l_msl_text VARCHAR2(256);
2836 l_entity_index NUMBER;
2837 l_entity_id VARCHAR2(100);
2838 l_message_type VARCHAR2(100);
2839
2840 l_api_name CONSTANT VARCHAR2(30) := 'convert_work_orders';
2841
2842 l_module varchar2(200) ;
2843 l_log_level CONSTANT NUMBER := fnd_log.g_current_runtime_level;
2844 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level ;
2845 l_sLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_statement >= l_log_level;
2846
2847 -- This cursor returns all necessary fields to call the WO API.modified for ib
2848 -- Query changed for performance reasons.
2849 CURSOR c1 IS
2850 SELECT meaa.asset_activity_id, fw.pm_schedule_id, fw.action_type,
2851 fw.wip_entity_id, fw.wo_status, ewsv.system_status, fw.cycle_id, fw.seq_id,
2852 meaa.maintenance_object_type, meaa.maintenance_object_id,
2853 msi.inventory_item_id, msi.eam_item_type, fw.scheduled_start_date,
2854 fw.scheduled_completion_date, fw.organization_id organization_id,
2855 fw.pm_base_meter_reading
2856 from eam_forecasted_work_orders fw, mtl_eam_asset_activities meaa,
2857 eam_wo_statuses_v ewsv, csi_item_instances cii, mtl_system_items_b msi
2858 where group_id = l_group_id and
2859 fw.activity_association_id = meaa.activity_association_id and
2860 ewsv.status_id=fw.wo_status and meaa.maintenance_object_type = 3 and
2861 meaa.maintenance_object_id = cii.instance_id and cii.inventory_item_id =
2862 msi.inventory_item_id and cii.last_vld_organization_id = msi.organization_id
2863 union all
2864 SELECT meaa.asset_activity_id, fw.pm_schedule_id, fw.action_type,
2865 fw.wip_entity_id, fw.wo_status, ewsv.system_status, fw.cycle_id, fw.seq_id,
2866 meaa.maintenance_object_type, meaa.maintenance_object_id,
2867 meaa.maintenance_object_id, 3, fw.scheduled_start_date,
2868 fw.scheduled_completion_date, fw.organization_id organization_id,
2869 fw.pm_base_meter_reading
2870 from eam_forecasted_work_orders fw, mtl_eam_asset_activities meaa,
2871 eam_wo_statuses_v ewsv
2872 where group_id = l_group_id and fw.activity_association_id =
2873 meaa.activity_association_id and ewsv.status_id=fw.wo_status and
2874 meaa.maintenance_object_type = 2 ;
2875
2876 /*
2877 CURSOR c1 IS
2878 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,
2879 meaa.inventory_item_id, default_eam_class wip_acct_class,meaa.eam_item_type,
2880 scheduled_start_date, scheduled_completion_date, meaa.organization_id
2881 from eam_forecasted_work_orders fw, mtl_eam_asset_activities_v meaa,
2882 wip_eam_parameters wep, mtl_system_items msi,eam_wo_statuses_v ewsv
2883 where group_id = l_group_id
2884 and wep.organization_id = meaa.organization_id
2885 and fw.activity_association_id = meaa.activity_association_id
2886 and meaa.inventory_item_id = msi.inventory_item_id
2887 and meaa.organization_id = msi.organization_id
2888 and ewsv.status_id=fw.wo_status;
2889 */
2890
2891 sugg_rec c1%ROWTYPE;
2892
2893
2894 i number;
2895
2896 -- counter for relationship table
2897 j number;
2898 l_output_dir VARCHAR2(512);
2899 BEGIN
2900
2901 if (l_ulog) then
2902 l_module := 'eam.plsql.'||g_pkg_name|| '.' || l_api_name;
2903 end if;
2904 l_group_id := p_pm_group_id;
2905 l_eam_wo_tbl.delete;
2906 l_eam_wo_relations_tbl.delete;
2907
2908 i := 1;
2909
2910 debug('BEFORE LOOP EXPANDING WORK ORDERS');
2911 FOR sugg_rec in c1
2912
2913 LOOP
2914
2915 if(sugg_rec.action_type IN (2,6,7,1,4)) then
2916 debug('EXPANDING: ' || sugg_rec.action_type);
2917 l_eam_wo_tbl(i).plan_maintenance := 'Y';
2918
2919 l_eam_wo_tbl(i).transaction_type := eam_process_wo_pub.G_OPR_CREATE;
2920
2921 l_eam_wo_tbl(i).maintenance_object_source := 1; -- EAM
2922 l_eam_wo_tbl(i).maintenance_object_type := sugg_rec.maintenance_object_type;
2923 l_eam_wo_tbl(i).maintenance_object_id := sugg_rec.maintenance_object_id;
2924 l_eam_wo_tbl(i).class_code := null; -- WO API will default WAC
2925 -- l_eam_wo_tbl(i).status_type := 1; -- unreleased
2926
2927 --added by akalaval for cyclic pm
2928 l_eam_wo_tbl(i).status_type := sugg_rec.system_status;
2929 l_eam_wo_tbl(i).user_defined_status_id := sugg_rec.wo_status;
2930 l_eam_wo_tbl(i).cycle_id := sugg_rec.cycle_id;
2931 l_eam_wo_tbl(i).seq_id := sugg_rec.seq_id;
2932
2933 l_eam_wo_tbl(i).pm_schedule_id := sugg_rec.pm_schedule_id;
2934 l_eam_wo_tbl(i).asset_activity_id := sugg_rec.asset_activity_id;
2935
2936
2937 if(sugg_rec.scheduled_start_date is not null) then
2938 -- forward scheduling
2939 l_eam_wo_tbl(i).scheduled_start_date := sugg_rec.scheduled_start_date;
2940 -- dummy value here, it will be over-written by the scheduler
2941 l_eam_wo_tbl(i).scheduled_completion_date := sugg_rec.scheduled_start_date;
2942 l_eam_wo_tbl(i).requested_start_date := sugg_rec.scheduled_start_date;
2943 else
2944 -- forward scheduling
2945 l_eam_wo_tbl(i).scheduled_start_date := sugg_rec.scheduled_completion_date;
2946 -- dummy value here, it will be over-written by the scheduler
2947 l_eam_wo_tbl(i).scheduled_completion_date := sugg_rec.scheduled_completion_date;
2948 l_eam_wo_tbl(i).due_date := sugg_rec.scheduled_completion_date;
2949 end if;
2950
2951 l_eam_wo_tbl(i).organization_id := sugg_rec.organization_id;
2952
2953 if(sugg_rec.eam_item_type = 1) then
2954 -- asset
2955 l_eam_wo_tbl(i).asset_group_id := sugg_rec.inventory_item_id;
2956 else
2957 -- rebuildable
2958 l_eam_wo_tbl(i).rebuild_item_id := sugg_rec.inventory_item_id;
2959 end if;
2960
2961 -- common fields for all operations
2962 l_eam_wo_tbl(i).batch_id := p_pm_group_id;
2963 l_eam_wo_tbl(i).header_id := i;
2964
2965 i := i + 1;
2966 j := j + 1;
2967 end if;
2968
2969
2970 end loop;
2971
2972 /*
2973 delete from eam_forecasted_work_orders
2974 where group_id = l_group_id;
2975 */
2976
2977 EAM_WORKORDER_UTIL_PKG.log_path(l_output_dir);
2978 debug('CALLING PROCCESS: ' || l_eam_wo_tbl.COUNT);
2979
2980 eam_process_wo_pub.PROCESS_MASTER_CHILD_WO
2981 ( p_bo_identifier => 'EAM'
2982 , p_init_msg_list => TRUE
2983 , p_api_version_number => 1.0
2984 , p_eam_wo_relations_tbl => l_eam_wo_relations_tbl
2985 , p_eam_wo_tbl => l_eam_wo_tbl
2986
2987 -- dummy parameters as these are not used in PM
2988 , p_eam_op_tbl => l_eam_op_tbl
2989 , p_eam_op_network_tbl => l_eam_op_network_tbl
2990 , p_eam_res_tbl => l_eam_res_tbl
2991 , p_eam_res_inst_tbl => l_eam_res_inst_tbl
2992 , p_eam_sub_res_tbl => l_eam_sub_res_tbl
2993 , p_eam_mat_req_tbl => l_eam_mat_req_tbl
2994 , p_eam_direct_items_tbl => l_eam_direct_item_tbl
2995 , p_eam_res_usage_tbl => l_eam_res_usage_tbl
2996 , p_eam_wo_comp_tbl => l_eam_wo_comp_tbl
2997 , p_eam_wo_quality_tbl => l_eam_wo_quality_tbl
2998 , p_eam_meter_reading_tbl => l_eam_meter_reading_tbl
2999 , p_eam_counter_prop_tbl => l_eam_counter_prop_tbl
3000 , p_eam_wo_comp_rebuild_tbl => l_eam_wo_comp_rebuild_tbl
3001 , p_eam_wo_comp_mr_read_tbl => l_eam_wo_comp_mr_read_tbl
3002 , p_eam_op_comp_tbl => l_eam_op_comp_tbl
3003 , p_eam_request_tbl => l_eam_request_tbl
3004 , x_eam_direct_items_tbl => l_out_eam_direct_item_tbl
3005 , x_eam_res_usage_tbl => l_eam_res_usage_tbl
3006 , x_eam_wo_tbl => l_out_eam_wo_tbl
3007 , x_eam_wo_relations_tbl => l_out_eam_wo_relations_tbl
3008 , x_eam_op_tbl => l_out_eam_op_tbl
3009 , x_eam_op_network_tbl => l_out_eam_op_network_tbl
3010 , x_eam_res_tbl => l_out_eam_res_tbl
3011 , x_eam_res_inst_tbl => l_out_eam_res_inst_tbl
3012 , x_eam_sub_res_tbl => l_out_eam_sub_res_tbl
3013 , x_eam_mat_req_tbl => l_out_eam_mat_req_tbl
3014 , x_eam_wo_comp_tbl => l_out_eam_wo_comp_tbl
3015 , x_eam_wo_quality_tbl => l_out_eam_wo_quality_tbl
3016 , x_eam_meter_reading_tbl => l_out_eam_meter_reading_tbl
3017 , x_eam_counter_prop_tbl => l_out_eam_counter_prop_tbl
3018 , x_eam_wo_comp_rebuild_tbl => l_out_eam_wo_comp_rebuild_tbl
3019 , x_eam_wo_comp_mr_read_tbl => l_out_eam_wo_comp_mr_read_tbl
3020 , x_eam_op_comp_tbl => l_out_eam_op_comp_tbl
3021 , x_eam_request_tbl => l_out_eam_request_tbl
3022
3023 , p_commit => 'N'
3024 -- , x_error_msl_tbl OUT NOCOPY EAM_ERROR_MESSAGE_PVT.error_tbl_type
3025 , x_return_status => p_return_status
3026 , x_msg_count => l_msl_count
3027 , p_debug => NVL(fnd_profile.value('EAM_DEBUG'), 'N')
3028 , p_debug_filename => 'convertwo.log'
3029 , p_output_dir => l_output_dir
3030
3031 );
3032
3033
3034 EAM_ERROR_MESSAGE_PVT.Get_Message(l_message_text, l_entity_index, l_entity_id, l_message_type);
3035 debug('Return status:' || p_return_status);
3036 debug('Error message:' || SUBSTRB(l_message_text,1,200));
3037 IF( l_slog ) THEN
3038 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,'Return status:' || p_return_status);
3039 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,'Error message:' || SUBSTRB(l_message_text,1,200));
3040 END IF;
3041 END convert_work_orders;
3042
3043
3044 END;