The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
wepb.period_set_name,
wepb.period_name,
wepb.acct_period_id,
wepb.operations_dept_id,
cdv.operation_seq_num,
wepb.maint_cost_category,
wepb.owning_dept_id,
cdv.reference_account,
decode(cdv.cost_element,
'Material', 1,
'Resource', 3,
'Material Overhead', 2,
'Outside Processing', 5,
'Overhead', 4) mfg_cost_element,
decode(nvl(cdv.resource_seq_num, -1),
-1, 3,
decode((SELECT resource_type
FROM bom_resources br, wip_operation_resources wor
WHERE br.resource_id = wor.resource_id
AND wor.wip_ENTITY_ID = cdv.wip_entity_id
AND wor.operation_seq_num = cdv.operation_seq_num
AND wor.resource_seq_num = cdv.resource_seq_num),
1, 1, 2, 2, p_def_eam_cost_element_id)
) as txn_type,
oap.period_start_date,
oap.period_num,
oap.period_year,
(cdv.base_transaction_value)as value
FROM cst_distribution_lite_v cdv, wip_eam_period_balances wepb, org_acct_periods oap,
mfg_lookups mf
WHERE cdv.operation_seq_num is not null
and mf.lookup_type = 'CST_ACCOUNTING_LINE_TYPE'
and mf.lookup_code in (7,8)
and mf.meaning = cdv.line_type_name
AND wepb.wip_entity_id = cdv.wip_entity_id
AND wepb.operation_seq_num = cdv.operation_seq_num
AND wepb.acct_period_id = cdv.acct_period_id
AND oap.acct_period_id = cdv.acct_period_id
AND oap.organization_id = cdv.organization_id
AND cdv.wip_entity_id = p_wip_entity_id
--AND cdv.organization_id = p_organization_id Bug#5632148
AND (p_account_from is null or
cdv.reference_account in (
SELECT code_combination_id from gl_code_combinations glcc
where fnd_flex_ext.get_segs('SQLGL', 'GL#',
glcc.chart_of_accounts_id, glcc.code_combination_id) >= p_account_from
AND fnd_flex_ext.get_segs('SQLGL', 'GL#', glcc.chart_of_accounts_id,
glcc.code_combination_id) <= p_account_to))
UNION ALL
SELECT
oap.period_set_name,
oap.period_name,
oap.acct_period_id,
null,
cdv.operation_seq_num,
ep.def_maint_cost_category,
wdj.owning_department,
cdv.reference_account,
decode(cdv.cost_element,
'Material', 1,
'Resource', 3,
'Material Overhead', 2,
'Outside Processing', 5,
'Overhead', 4) mfg_cost_element,
null txn_type,
oap.period_start_date,
oap.period_num,
oap.period_year,
(cdv.base_transaction_value) value
FROM cst_distribution_lite_v cdv, org_acct_periods oap, wip_discrete_jobs wdj,
wip_eam_parameters ep, mfg_lookups mf
WHERE ep.organization_id = cdv.organization_id
and cdv.wip_entity_id = wdj.wip_entity_id
and cdv.organization_id = wdj.organization_id
and cdv.operation_seq_num is null
and mf.lookup_type = 'CST_ACCOUNTING_LINE_TYPE'
and mf.lookup_code in (7,8)
and mf.meaning = cdv.line_type_name
AND oap.acct_period_id = cdv.acct_period_id
AND oap.organization_id = cdv.organization_id
AND cdv.wip_entity_id = p_wip_entity_id
--AND cdv.organization_id = p_organization_id Bug#5632148
AND (p_account_from is null or
cdv.reference_account in (
SELECT code_combination_id from gl_code_combinations glcc
where fnd_flex_ext.get_segs('SQLGL', 'GL#',
glcc.chart_of_accounts_id, glcc.code_combination_id) >= p_account_from
AND fnd_flex_ext.get_segs('SQLGL', 'GL#', glcc.chart_of_accounts_id,
glcc.code_combination_id) <= p_account_to));
select oap.period_start_date
into l_period_from
from org_acct_periods oap
where organization_id = p_org_id
and acct_period_id = p_acct_period_from;
select nvl(oap.period_close_date, oap.SCHEDULE_CLOSE_DATE)
into l_period_to
from org_acct_periods oap
where organization_id = p_org_id
and acct_period_id = p_acct_period_to; */
SELECT def_eam_cost_element_id
INTO l_def_eam_cost_element_id
FROM wip_eam_parameters
WHERE organization_id = p_organization_id;
l_forecast_rec.LAST_UPDATE_DATE := sysdate;
l_forecast_rec.LAST_UPDATED_BY := p_user_id;
l_forecast_rec.LAST_UPDATE_LOGIN := p_user_id;
l_forecast_rec.PROGRAM_UPDATE_DATE := sysdate;
procedure delete_work_order(p_forecast_id IN number, p_wip_id IN number)IS
BEGIN
delete from eam_forecast_wdj where
forecast_id = p_forecast_id
AND wip_entity_id = p_wip_id;
delete from eam_forecast_wo
where forecast_id = p_forecast_id
AND wip_entity_id = p_wip_id;
delete from eam_forecast_wor
where forecast_id = p_forecast_id
AND wip_entity_id = p_wip_id;
delete from eam_forecast_wro
where forecast_id = p_forecast_id
AND wip_entity_id = p_wip_id;
delete from eam_forecast_wedi
where forecast_id = p_forecast_id
AND wip_entity_id = p_wip_id;
delete from eam_forecast_cebba
where forecast_id = p_forecast_id
AND wip_entity_id = p_wip_id;
end delete_work_order;
procedure delete_forecast(p_forecast_id IN number)IS
BEGIN
delete from eam_forecasts where
forecast_id = p_forecast_id;
delete from eam_forecast_wdj where
forecast_id = p_forecast_id;
delete from eam_forecast_wo
where forecast_id = p_forecast_id;
delete from eam_forecast_wor
where forecast_id = p_forecast_id;
delete from eam_forecast_wro
where forecast_id = p_forecast_id;
delete from eam_forecast_wedi
where forecast_id = p_forecast_id;
delete from eam_forecast_cebba
where forecast_id = p_forecast_id;
end delete_forecast;
procedure delete_forecast_data(p_forecast_id IN number)IS
BEGIN
delete from eam_forecast_wdj where
forecast_id = p_forecast_id;
delete from eam_forecast_wo
where forecast_id = p_forecast_id;
delete from eam_forecast_wor
where forecast_id = p_forecast_id;
delete from eam_forecast_wro
where forecast_id = p_forecast_id;
delete from eam_forecast_wedi
where forecast_id = p_forecast_id;
delete from eam_forecast_cebba
where forecast_id = p_forecast_id;
end delete_forecast_data;
delete_forecast_data(p_forecast_id);
update eam_forecasts
set completion_date = SYSDATE
where forecast_id = p_forecast_id;
l_sql_select VARCHAR2(1000);
SELECT glp.start_date
INTO l_start_date
FROM gl_periods glp
WHERE glp.period_name = p_forecast_rec.period_from
AND glp.period_set_name = p_forecast_rec.period_set_name_from;
SELECT glp.end_date
INTO l_end_date
FROM gl_periods glp
WHERE glp.period_name = p_forecast_rec.period_to
AND glp.period_set_name = p_forecast_rec.period_set_name_to;
l_sql_select := 'SELECT wdj.wip_entity_id ';
debug('Select stmt: ' || l_sql_select);
l_sql := l_sql_select || l_sql_from || l_sql_where;
debug('Inserting sql');
l_sql_cii_select VARCHAR2(100);
l_sql_msi_select VARCHAR2(100);
l_sql_cii_select := 'SELECT cii.instance_id as asset_id, 3 as asset_type ';
l_sql_msi_select := 'SELECT msi.inventory_item_id as asset_id, 2 as asset_type ';
l_sql_cii := l_sql_cii_select || l_sql_cii_from || l_sql_cii_where;
l_sql_msi := l_sql_msi_select || l_sql_msi_from || l_sql_msi_where;
SELECT * INTO v_forecast_rec
FROM eam_forecasts
WHERE forecast_id = p_forecast_id;
p_user_id => v_forecast_rec.last_updated_by,
p_request_id => v_forecast_rec.request_id,
p_prog_id => 1,
p_prog_app_id => 1,
p_login_id => 1,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
p_user_id => v_forecast_rec.last_updated_by,
p_request_id => v_forecast_rec.request_id,
p_prog_id => 1,
p_prog_app_id => 1,
p_login_id => 1,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
-- delete all costs not within the horizon
delete from eam_forecast_cebba where forecast_id = p_forecast_id AND
period_start_date > (select start_date from gl_periods
where
period_set_name = v_forecast_rec.period_set_name_to
and period_name = v_forecast_rec.period_to);
-- delete all work orders that have zero costs associated
delete from eam_forecast_wdj where forecast_id = p_forecast_id and wip_entity_id
in
(select wip_entity_id from (select wip_entity_id, sum(acct_value) as total from
eam_forecast_cebba where forecast_id = p_forecast_id
group by wip_entity_id) where total = 0);
delete from eam_forecast_wdj where forecast_id = p_forecast_id and
wip_entity_id
not in
(select wip_entity_id from eam_forecast_cebba where forecast_id =
p_forecast_id);
p_user_id => p_forecast_rec.last_updated_by,
p_request_id => p_forecast_rec.request_id,
p_prog_id => 1,
p_prog_app_id => 1,
p_login_id => 1,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
p_user_id => p_forecast_rec.last_updated_by,
p_request_id => p_forecast_rec.request_id,
p_prog_id => 1,
p_prog_app_id => 1,
p_login_id => 1,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
p_user_id => p_forecast_rec.last_updated_by,
p_request_id => p_forecast_rec.request_id,
p_prog_id => 1,
p_prog_app_id => 1,
p_login_id => 1,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
p_user_id => p_forecast_rec.last_updated_by,
p_request_id => p_forecast_rec.request_id,
p_prog_id => 1,
p_prog_app_id => 1,
p_login_id => 1,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
p_user_id => p_forecast_rec.last_updated_by,
p_request_id => p_forecast_rec.request_id,
p_prog_id => 1,
p_prog_app_id => 1,
p_login_id => 1,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
p_user_id => p_forecast_rec.last_updated_by,
p_request_id => p_forecast_rec.request_id,
p_prog_id => 1,
p_prog_app_id => 1,
p_login_id => 1,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
SELECT glp.start_date
INTO l_start_date
FROM gl_periods glp
WHERE glp.period_name = p_forecast_rec.period_from
AND glp.period_set_name = p_forecast_rec.period_set_name_from;
SELECT glp.end_date
INTO l_end_date
FROM gl_periods glp
WHERE glp.period_name = p_forecast_rec.period_to
AND glp.period_set_name = p_forecast_rec.period_set_name_to;
select wip_job_schedule_interface_s.nextval into l_pm_group_id from dual;
userID => p_forecast_rec.last_update_login,
objectID => l_asset_id,
objectType => l_asset_type,
setname_id => -1,
combine_default => 'Y',
group_id => l_pm_group_id);
delete from eam_forecasted_work_orders where
group_id = l_pm_group_id and
scheduled_start_date < l_start_date;
p_user_id => p_forecast_rec.last_updated_by,
p_request_id => p_forecast_rec.request_id,
p_prog_id => 1,
p_prog_app_id => 1,
p_login_id => 1,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
p_user_id => p_forecast_rec.last_updated_by,
p_request_id => p_forecast_rec.request_id,
p_prog_id => 1,
p_prog_app_id => 1,
p_login_id => 1,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
p_user_id => p_forecast_rec.last_updated_by,
p_request_id => p_forecast_rec.request_id,
p_prog_id => 1,
p_prog_app_id => 1,
p_login_id => 1,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
p_user_id => p_forecast_rec.last_updated_by,
p_request_id => p_forecast_rec.request_id,
p_prog_id => 1,
p_prog_app_id => 1,
p_login_id => 1,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
p_user_id => p_forecast_rec.last_updated_by,
p_request_id => p_forecast_rec.request_id,
p_prog_id => 1,
p_prog_app_id => 1,
p_login_id => 1,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
p_user_id => p_forecast_rec.last_updated_by,
p_request_id => p_forecast_rec.request_id,
p_prog_id => 1,
p_prog_app_id => 1,
p_login_id => 1,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
p_user_id => p_forecast_rec.last_updated_by,
p_request_id => p_forecast_rec.request_id,
p_prog_id => 1,
p_prog_app_id => 1,
p_login_id => 1,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
INSERT INTO EAM_FORECAST_WDJ (
WIP_ENTITY_ID, ORGANIZATION_ID, LAST_UPDATE_DATE,
LAST_UPDATED_BY, CREATION_DATE, CREATED_BY,
LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID,
PROGRAM_ID, PROGRAM_UPDATE_DATE, SOURCE_LINE_ID,
SOURCE_CODE, DESCRIPTION, STATUS_TYPE,
PRIMARY_ITEM_ID, FIRM_PLANNED_FLAG, JOB_TYPE,
WIP_SUPPLY_TYPE, CLASS_CODE, MATERIAL_ACCOUNT,
MATERIAL_OVERHEAD_ACCOUNT, RESOURCE_ACCOUNT, OUTSIDE_PROCESSING_ACCOUNT,
MATERIAL_VARIANCE_ACCOUNT, RESOURCE_VARIANCE_ACCOUNT, OUTSIDE_PROC_VARIANCE_ACCOUNT,
STD_COST_ADJUSTMENT_ACCOUNT, OVERHEAD_ACCOUNT, OVERHEAD_VARIANCE_ACCOUNT,
SCHEDULED_START_DATE, DATE_RELEASED, SCHEDULED_COMPLETION_DATE,
DATE_COMPLETED, DATE_CLOSED, START_QUANTITY,
QUANTITY_COMPLETED, QUANTITY_SCRAPPED, NET_QUANTITY,
BOM_REFERENCE_ID, ROUTING_REFERENCE_ID, COMMON_BOM_SEQUENCE_ID,
COMMON_ROUTING_SEQUENCE_ID, BOM_REVISION, ROUTING_REVISION,
BOM_REVISION_DATE, ROUTING_REVISION_DATE, LOT_NUMBER,
ALTERNATE_BOM_DESIGNATOR, ALTERNATE_ROUTING_DESIGNATOR, COMPLETION_SUBINVENTORY,
COMPLETION_LOCATOR_ID, MPS_SCHEDULED_COMPLETION_DATE, MPS_NET_QUANTITY,
DEMAND_CLASS, ATTRIBUTE_CATEGORY, ATTRIBUTE1,
ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4,
ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7,
ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10,
ATTRIBUTE11, ATTRIBUTE12, ATTRIBUTE13,
ATTRIBUTE14, ATTRIBUTE15, SCHEDULE_GROUP_ID,
BUILD_SEQUENCE, LINE_ID, PROJECT_ID,
TASK_ID, KANBAN_CARD_ID, OVERCOMPLETION_TOLERANCE_TYPE,
OVERCOMPLETION_TOLERANCE_VALUE, END_ITEM_UNIT_NUMBER, PO_CREATION_TIME,
PRIORITY, DUE_DATE, EST_SCRAP_ACCOUNT,
EST_SCRAP_VAR_ACCOUNT, EST_SCRAP_PRIOR_QTY, DUE_DATE_PENALTY,
DUE_DATE_TOLERANCE, COPRODUCTS_SUPPLY, PARENT_WIP_ENTITY_ID,
ASSET_NUMBER, ASSET_GROUP_ID, REBUILD_ITEM_ID,
REBUILD_SERIAL_NUMBER, MANUAL_REBUILD_FLAG, SHUTDOWN_TYPE,
ESTIMATION_STATUS, REQUESTED_START_DATE, NOTIFICATION_REQUIRED,
WORK_ORDER_TYPE, OWNING_DEPARTMENT, ACTIVITY_TYPE,
ACTIVITY_CAUSE, TAGOUT_REQUIRED, PLAN_MAINTENANCE,
PM_SCHEDULE_ID, LAST_ESTIMATION_DATE, LAST_ESTIMATION_REQ_ID,
ACTIVITY_SOURCE, SERIALIZATION_START_OP, MAINTENANCE_OBJECT_ID,
MAINTENANCE_OBJECT_TYPE, MAINTENANCE_OBJECT_SOURCE, MATERIAL_ISSUE_BY_MO,
SCHEDULING_REQUEST_ID, ISSUE_ZERO_COST_FLAG, EAM_LINEAR_LOCATION_ID,
ACTUAL_START_DATE,
EXPEDITED, EXPECTED_HOLD_RELEASE_DATE, FORECAST_ID)
SELECT WIP_ENTITY_ID, ORGANIZATION_ID, LAST_UPDATE_DATE,
LAST_UPDATED_BY, CREATION_DATE, CREATED_BY,
LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID,
PROGRAM_ID, PROGRAM_UPDATE_DATE, SOURCE_LINE_ID,
SOURCE_CODE, DESCRIPTION, STATUS_TYPE,
PRIMARY_ITEM_ID, FIRM_PLANNED_FLAG, JOB_TYPE,
WIP_SUPPLY_TYPE, CLASS_CODE, MATERIAL_ACCOUNT,
MATERIAL_OVERHEAD_ACCOUNT, RESOURCE_ACCOUNT, OUTSIDE_PROCESSING_ACCOUNT,
MATERIAL_VARIANCE_ACCOUNT, RESOURCE_VARIANCE_ACCOUNT, OUTSIDE_PROC_VARIANCE_ACCOUNT,
STD_COST_ADJUSTMENT_ACCOUNT, OVERHEAD_ACCOUNT, OVERHEAD_VARIANCE_ACCOUNT,
SCHEDULED_START_DATE, DATE_RELEASED, SCHEDULED_COMPLETION_DATE,
DATE_COMPLETED, DATE_CLOSED, START_QUANTITY,
QUANTITY_COMPLETED, QUANTITY_SCRAPPED, NET_QUANTITY,
BOM_REFERENCE_ID, ROUTING_REFERENCE_ID, COMMON_BOM_SEQUENCE_ID,
COMMON_ROUTING_SEQUENCE_ID, BOM_REVISION, ROUTING_REVISION,
BOM_REVISION_DATE, ROUTING_REVISION_DATE, LOT_NUMBER,
ALTERNATE_BOM_DESIGNATOR, ALTERNATE_ROUTING_DESIGNATOR, COMPLETION_SUBINVENTORY,
COMPLETION_LOCATOR_ID, MPS_SCHEDULED_COMPLETION_DATE, MPS_NET_QUANTITY,
DEMAND_CLASS, ATTRIBUTE_CATEGORY, ATTRIBUTE1,
ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4,
ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7,
ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10,
ATTRIBUTE11, ATTRIBUTE12, ATTRIBUTE13,
ATTRIBUTE14, ATTRIBUTE15, SCHEDULE_GROUP_ID,
BUILD_SEQUENCE, LINE_ID, PROJECT_ID,
TASK_ID, KANBAN_CARD_ID, OVERCOMPLETION_TOLERANCE_TYPE,
OVERCOMPLETION_TOLERANCE_VALUE, END_ITEM_UNIT_NUMBER, PO_CREATION_TIME,
PRIORITY, DUE_DATE, EST_SCRAP_ACCOUNT,
EST_SCRAP_VAR_ACCOUNT, EST_SCRAP_PRIOR_QTY, DUE_DATE_PENALTY,
DUE_DATE_TOLERANCE, COPRODUCTS_SUPPLY, PARENT_WIP_ENTITY_ID,
ASSET_NUMBER, ASSET_GROUP_ID, REBUILD_ITEM_ID,
REBUILD_SERIAL_NUMBER, MANUAL_REBUILD_FLAG, SHUTDOWN_TYPE,
ESTIMATION_STATUS, REQUESTED_START_DATE, NOTIFICATION_REQUIRED,
WORK_ORDER_TYPE, OWNING_DEPARTMENT, ACTIVITY_TYPE,
ACTIVITY_CAUSE, TAGOUT_REQUIRED, PLAN_MAINTENANCE,
PM_SCHEDULE_ID, LAST_ESTIMATION_DATE, LAST_ESTIMATION_REQ_ID,
ACTIVITY_SOURCE, SERIALIZATION_START_OP, MAINTENANCE_OBJECT_ID,
MAINTENANCE_OBJECT_TYPE, MAINTENANCE_OBJECT_SOURCE, MATERIAL_ISSUE_BY_MO,
SCHEDULING_REQUEST_ID, ISSUE_ZERO_COST_FLAG, EAM_LINEAR_LOCATION_ID,
ACTUAL_START_DATE,
EXPEDITED, EXPECTED_HOLD_RELEASE_DATE,
p_forecast_rec.forecast_id AS FORECAST_ID
FROM WIP_DISCRETE_JOBS
WHERE wip_entity_id = p_wip_id_table(j);
p_user_id => p_forecast_rec.last_updated_by,
p_request_id => p_forecast_rec.request_id,
p_prog_id => 1,
p_prog_app_id => 1,
p_login_id => 1,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
select * bulk collect INTO l_wdj_table
from eam_forecast_wdj
where forecast_id = p_forecast_rec.forecast_id;
p_user_id => p_forecast_rec.last_updated_by,
p_request_id => p_forecast_rec.request_id,
p_prog_id => 1,
p_prog_app_id => 1,
p_login_id => 1,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
select * bulk collect INTO l_cebba_table
from eam_forecast_cebba
where forecast_id = p_forecast_rec.forecast_id;
insert_into_wdj_auto(l_wdj_table);
insert_into_cebba_auto(l_cebba_table);
PROCEDURE insert_into_wdj_auto(p_wdj_table wdj_table_type)
IS
pragma autonomous_transaction;
insert into eam_forecast_wdj values p_wdj_table(i);
END insert_into_wdj_auto;
PROCEDURE insert_into_wdj_auto(p_wdj_table wdj_table_type)
IS
pragma autonomous_transaction;
debug('inserting : ' || p_wdj_table(i).wip_entity_id);
INSERT INTO EAM_FORECAST_WDJ (
WIP_ENTITY_ID, ORGANIZATION_ID, LAST_UPDATE_DATE,
LAST_UPDATED_BY, CREATION_DATE, CREATED_BY,
LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID,
PROGRAM_ID, PROGRAM_UPDATE_DATE, SOURCE_LINE_ID,
SOURCE_CODE, DESCRIPTION, STATUS_TYPE,
PRIMARY_ITEM_ID, FIRM_PLANNED_FLAG, JOB_TYPE,
WIP_SUPPLY_TYPE, CLASS_CODE, MATERIAL_ACCOUNT,
MATERIAL_OVERHEAD_ACCOUNT, RESOURCE_ACCOUNT, OUTSIDE_PROCESSING_ACCOUNT,
MATERIAL_VARIANCE_ACCOUNT, RESOURCE_VARIANCE_ACCOUNT, OUTSIDE_PROC_VARIANCE_ACCOUNT,
STD_COST_ADJUSTMENT_ACCOUNT, OVERHEAD_ACCOUNT, OVERHEAD_VARIANCE_ACCOUNT,
SCHEDULED_START_DATE, DATE_RELEASED, SCHEDULED_COMPLETION_DATE,
DATE_COMPLETED, DATE_CLOSED, START_QUANTITY,
QUANTITY_COMPLETED, QUANTITY_SCRAPPED, NET_QUANTITY,
BOM_REFERENCE_ID, ROUTING_REFERENCE_ID, COMMON_BOM_SEQUENCE_ID,
COMMON_ROUTING_SEQUENCE_ID, BOM_REVISION, ROUTING_REVISION,
BOM_REVISION_DATE, ROUTING_REVISION_DATE, LOT_NUMBER,
ALTERNATE_BOM_DESIGNATOR, ALTERNATE_ROUTING_DESIGNATOR, COMPLETION_SUBINVENTORY,
COMPLETION_LOCATOR_ID, MPS_SCHEDULED_COMPLETION_DATE, MPS_NET_QUANTITY,
DEMAND_CLASS, ATTRIBUTE_CATEGORY, ATTRIBUTE1,
ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4,
ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7,
ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10,
ATTRIBUTE11, ATTRIBUTE12, ATTRIBUTE13,
ATTRIBUTE14, ATTRIBUTE15, SCHEDULE_GROUP_ID,
BUILD_SEQUENCE, LINE_ID, PROJECT_ID,
TASK_ID, KANBAN_CARD_ID, OVERCOMPLETION_TOLERANCE_TYPE,
OVERCOMPLETION_TOLERANCE_VALUE, END_ITEM_UNIT_NUMBER, PO_CREATION_TIME,
PRIORITY, DUE_DATE, EST_SCRAP_ACCOUNT,
EST_SCRAP_VAR_ACCOUNT, EST_SCRAP_PRIOR_QTY, DUE_DATE_PENALTY,
DUE_DATE_TOLERANCE, COPRODUCTS_SUPPLY, PARENT_WIP_ENTITY_ID,
ASSET_NUMBER, ASSET_GROUP_ID, REBUILD_ITEM_ID,
REBUILD_SERIAL_NUMBER, MANUAL_REBUILD_FLAG, SHUTDOWN_TYPE,
ESTIMATION_STATUS, REQUESTED_START_DATE, NOTIFICATION_REQUIRED,
WORK_ORDER_TYPE, OWNING_DEPARTMENT, ACTIVITY_TYPE,
ACTIVITY_CAUSE, TAGOUT_REQUIRED, PLAN_MAINTENANCE,
PM_SCHEDULE_ID, LAST_ESTIMATION_DATE, LAST_ESTIMATION_REQ_ID,
ACTIVITY_SOURCE, SERIALIZATION_START_OP, MAINTENANCE_OBJECT_ID,
MAINTENANCE_OBJECT_TYPE, MAINTENANCE_OBJECT_SOURCE, MATERIAL_ISSUE_BY_MO,
SCHEDULING_REQUEST_ID, ISSUE_ZERO_COST_FLAG, EAM_LINEAR_LOCATION_ID,
ACTUAL_START_DATE,
EXPEDITED, EXPECTED_HOLD_RELEASE_DATE, FORECAST_ID)
VALUES(
p_wdj_table(i).WIP_ENTITY_ID, p_wdj_table(i).ORGANIZATION_ID, p_wdj_table(i).LAST_UPDATE_DATE,
p_wdj_table(i).LAST_UPDATED_BY, p_wdj_table(i).CREATION_DATE, p_wdj_table(i).CREATED_BY,
p_wdj_table(i).LAST_UPDATE_LOGIN, p_wdj_table(i).REQUEST_ID, p_wdj_table(i).PROGRAM_APPLICATION_ID,
p_wdj_table(i).PROGRAM_ID, p_wdj_table(i).PROGRAM_UPDATE_DATE, p_wdj_table(i).SOURCE_LINE_ID,
p_wdj_table(i).SOURCE_CODE, p_wdj_table(i).DESCRIPTION, p_wdj_table(i).STATUS_TYPE,
p_wdj_table(i).PRIMARY_ITEM_ID, p_wdj_table(i).FIRM_PLANNED_FLAG, p_wdj_table(i).JOB_TYPE,
p_wdj_table(i).WIP_SUPPLY_TYPE, p_wdj_table(i).CLASS_CODE, p_wdj_table(i).MATERIAL_ACCOUNT,
p_wdj_table(i).MATERIAL_OVERHEAD_ACCOUNT, p_wdj_table(i).RESOURCE_ACCOUNT, p_wdj_table(i).OUTSIDE_PROCESSING_ACCOUNT,
p_wdj_table(i).MATERIAL_VARIANCE_ACCOUNT, p_wdj_table(i).RESOURCE_VARIANCE_ACCOUNT, p_wdj_table(i).OUTSIDE_PROC_VARIANCE_ACCOUNT,
p_wdj_table(i).STD_COST_ADJUSTMENT_ACCOUNT, p_wdj_table(i).OVERHEAD_ACCOUNT, p_wdj_table(i).OVERHEAD_VARIANCE_ACCOUNT,
p_wdj_table(i).SCHEDULED_START_DATE, p_wdj_table(i).DATE_RELEASED, p_wdj_table(i).SCHEDULED_COMPLETION_DATE,
p_wdj_table(i).DATE_COMPLETED, p_wdj_table(i).DATE_CLOSED, p_wdj_table(i).START_QUANTITY,
p_wdj_table(i).QUANTITY_COMPLETED, p_wdj_table(i).QUANTITY_SCRAPPED, p_wdj_table(i).NET_QUANTITY,
p_wdj_table(i).BOM_REFERENCE_ID, p_wdj_table(i).ROUTING_REFERENCE_ID, p_wdj_table(i).COMMON_BOM_SEQUENCE_ID,
p_wdj_table(i).COMMON_ROUTING_SEQUENCE_ID, p_wdj_table(i).BOM_REVISION, p_wdj_table(i).ROUTING_REVISION,
p_wdj_table(i).BOM_REVISION_DATE, p_wdj_table(i).ROUTING_REVISION_DATE, p_wdj_table(i).LOT_NUMBER,
p_wdj_table(i).ALTERNATE_BOM_DESIGNATOR, p_wdj_table(i).ALTERNATE_ROUTING_DESIGNATOR, p_wdj_table(i).COMPLETION_SUBINVENTORY,
p_wdj_table(i).COMPLETION_LOCATOR_ID, p_wdj_table(i).MPS_SCHEDULED_COMPLETION_DATE, p_wdj_table(i).MPS_NET_QUANTITY,
p_wdj_table(i).DEMAND_CLASS, p_wdj_table(i).ATTRIBUTE_CATEGORY, p_wdj_table(i).ATTRIBUTE1,
p_wdj_table(i).ATTRIBUTE2, p_wdj_table(i).ATTRIBUTE3, p_wdj_table(i).ATTRIBUTE4,
p_wdj_table(i).ATTRIBUTE5, p_wdj_table(i).ATTRIBUTE6, p_wdj_table(i).ATTRIBUTE7,
p_wdj_table(i).ATTRIBUTE8, p_wdj_table(i).ATTRIBUTE9, p_wdj_table(i).ATTRIBUTE10,
p_wdj_table(i).ATTRIBUTE11, p_wdj_table(i).ATTRIBUTE12, p_wdj_table(i).ATTRIBUTE13,
p_wdj_table(i).ATTRIBUTE14, p_wdj_table(i).ATTRIBUTE15, p_wdj_table(i).SCHEDULE_GROUP_ID,
p_wdj_table(i).BUILD_SEQUENCE, p_wdj_table(i).LINE_ID, p_wdj_table(i).PROJECT_ID,
p_wdj_table(i).TASK_ID, p_wdj_table(i).KANBAN_CARD_ID, p_wdj_table(i).OVERCOMPLETION_TOLERANCE_TYPE,
p_wdj_table(i).OVERCOMPLETION_TOLERANCE_VALUE, p_wdj_table(i).END_ITEM_UNIT_NUMBER, p_wdj_table(i).PO_CREATION_TIME,
p_wdj_table(i).PRIORITY, p_wdj_table(i).DUE_DATE, p_wdj_table(i).EST_SCRAP_ACCOUNT,
p_wdj_table(i).EST_SCRAP_VAR_ACCOUNT, p_wdj_table(i).EST_SCRAP_PRIOR_QTY, p_wdj_table(i).DUE_DATE_PENALTY,
p_wdj_table(i).DUE_DATE_TOLERANCE, p_wdj_table(i).COPRODUCTS_SUPPLY, p_wdj_table(i).PARENT_WIP_ENTITY_ID,
p_wdj_table(i).ASSET_NUMBER, p_wdj_table(i).ASSET_GROUP_ID, p_wdj_table(i).REBUILD_ITEM_ID,
p_wdj_table(i).REBUILD_SERIAL_NUMBER, p_wdj_table(i).MANUAL_REBUILD_FLAG, p_wdj_table(i).SHUTDOWN_TYPE,
p_wdj_table(i).ESTIMATION_STATUS, p_wdj_table(i).REQUESTED_START_DATE, p_wdj_table(i).NOTIFICATION_REQUIRED,
p_wdj_table(i).WORK_ORDER_TYPE, p_wdj_table(i).OWNING_DEPARTMENT, p_wdj_table(i).ACTIVITY_TYPE,
p_wdj_table(i).ACTIVITY_CAUSE, p_wdj_table(i).TAGOUT_REQUIRED, p_wdj_table(i).PLAN_MAINTENANCE,
p_wdj_table(i).PM_SCHEDULE_ID, p_wdj_table(i).LAST_ESTIMATION_DATE, p_wdj_table(i).LAST_ESTIMATION_REQ_ID,
p_wdj_table(i).ACTIVITY_SOURCE, p_wdj_table(i).SERIALIZATION_START_OP, p_wdj_table(i).MAINTENANCE_OBJECT_ID,
p_wdj_table(i).MAINTENANCE_OBJECT_TYPE, p_wdj_table(i).MAINTENANCE_OBJECT_SOURCE, p_wdj_table(i).MATERIAL_ISSUE_BY_MO,
p_wdj_table(i).SCHEDULING_REQUEST_ID, p_wdj_table(i).ISSUE_ZERO_COST_FLAG, p_wdj_table(i).EAM_LINEAR_LOCATION_ID,
p_wdj_table(i).ACTUAL_START_DATE,
p_wdj_table(i).EXPEDITED, p_wdj_table(i).EXPECTED_HOLD_RELEASE_DATE, p_wdj_table(i).FORECAST_ID);
END insert_into_wdj_auto;
p_user_id => p_forecast_rec.last_updated_by,
p_request_id => p_forecast_rec.request_id,
p_prog_id => 1,
p_prog_app_id => 1,
p_login_id => 1,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
select * bulk collect INTO l_cebba_table
from eam_forecast_cebba
where forecast_id = p_forecast_rec.forecast_id;
insert_into_cebba_auto(l_cebba_table);
PROCEDURE insert_into_cebba_auto(p_cebba_table cebba_table_type)
IS
pragma autonomous_transaction;
insert into eam_forecast_cebba values p_cebba_table(i);
END insert_into_cebba_auto;
INSERT INTO EAM_FORECAST_WOR (
WIP_ENTITY_ID, OPERATION_SEQ_NUM, RESOURCE_SEQ_NUM,
ORGANIZATION_ID, REPETITIVE_SCHEDULE_ID, LAST_UPDATE_DATE,
LAST_UPDATED_BY, CREATION_DATE, CREATED_BY,
LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID,
PROGRAM_ID, PROGRAM_UPDATE_DATE, RESOURCE_ID,
UOM_CODE, BASIS_TYPE, USAGE_RATE_OR_AMOUNT,
ACTIVITY_ID, SCHEDULED_FLAG, ASSIGNED_UNITS,
AUTOCHARGE_TYPE, STANDARD_RATE_FLAG, APPLIED_RESOURCE_UNITS,
APPLIED_RESOURCE_VALUE, START_DATE, COMPLETION_DATE,
ATTRIBUTE_CATEGORY, ATTRIBUTE1, ATTRIBUTE2,
ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5,
ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8,
ATTRIBUTE9, ATTRIBUTE10, ATTRIBUTE11,
ATTRIBUTE12, ATTRIBUTE13, ATTRIBUTE14,
ATTRIBUTE15, RELIEVED_RES_COMPLETION_UNITS, RELIEVED_RES_SCRAP_UNITS,
RELIEVED_RES_COMPLETION_VALUE, RELIEVED_RES_SCRAP_VALUE, RELIEVED_VARIANCE_VALUE,
TEMP_RELIEVED_VALUE, RELIEVED_RES_FINAL_COMP_UNITS, DEPARTMENT_ID,
PHANTOM_FLAG, PHANTOM_OP_SEQ_NUM, PHANTOM_ITEM_ID,
SCHEDULE_SEQ_NUM, SUBSTITUTE_GROUP_NUM, REPLACEMENT_GROUP_NUM,
PRINCIPLE_FLAG, SETUP_ID, PARENT_RESOURCE_SEQ,
BATCH_ID, RELIEVED_RES_UNITS, RELIEVED_RES_VALUE,
MAXIMUM_ASSIGNED_UNITS, FIRM_FLAG, GROUP_SEQUENCE_ID,
GROUP_SEQUENCE_NUMBER, ACTUAL_START_DATE, ACTUAL_COMPLETION_DATE,
PROJECTED_COMPLETION_DATE, FORECAST_ID)
SELECT WIP_ENTITY_ID, OPERATION_SEQ_NUM, RESOURCE_SEQ_NUM,
ORGANIZATION_ID, REPETITIVE_SCHEDULE_ID, LAST_UPDATE_DATE,
LAST_UPDATED_BY, CREATION_DATE, CREATED_BY,
LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID,
PROGRAM_ID, PROGRAM_UPDATE_DATE, RESOURCE_ID,
UOM_CODE, BASIS_TYPE, USAGE_RATE_OR_AMOUNT,
ACTIVITY_ID, SCHEDULED_FLAG, ASSIGNED_UNITS,
AUTOCHARGE_TYPE, STANDARD_RATE_FLAG, APPLIED_RESOURCE_UNITS,
APPLIED_RESOURCE_VALUE, START_DATE, COMPLETION_DATE,
ATTRIBUTE_CATEGORY, ATTRIBUTE1, ATTRIBUTE2,
ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5,
ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8,
ATTRIBUTE9, ATTRIBUTE10, ATTRIBUTE11,
ATTRIBUTE12, ATTRIBUTE13, ATTRIBUTE14,
ATTRIBUTE15, RELIEVED_RES_COMPLETION_UNITS, RELIEVED_RES_SCRAP_UNITS,
RELIEVED_RES_COMPLETION_VALUE, RELIEVED_RES_SCRAP_VALUE, RELIEVED_VARIANCE_VALUE,
TEMP_RELIEVED_VALUE, RELIEVED_RES_FINAL_COMP_UNITS, DEPARTMENT_ID,
PHANTOM_FLAG, PHANTOM_OP_SEQ_NUM, PHANTOM_ITEM_ID,
SCHEDULE_SEQ_NUM, SUBSTITUTE_GROUP_NUM, REPLACEMENT_GROUP_NUM,
PRINCIPLE_FLAG, SETUP_ID, PARENT_RESOURCE_SEQ,
BATCH_ID, RELIEVED_RES_UNITS, RELIEVED_RES_VALUE,
MAXIMUM_ASSIGNED_UNITS, FIRM_FLAG, GROUP_SEQUENCE_ID,
GROUP_SEQUENCE_NUMBER, ACTUAL_START_DATE, ACTUAL_COMPLETION_DATE,
PROJECTED_COMPLETION_DATE,p_forecast_rec.forecast_id AS FORECAST_ID
FROM WIP_OPERATION_RESOURCES
WHERE wip_entity_id = p_wip_id_table(j);
INSERT INTO EAM_FORECAST_WRO (
INVENTORY_ITEM_ID, ORGANIZATION_ID, WIP_ENTITY_ID,
OPERATION_SEQ_NUM, REPETITIVE_SCHEDULE_ID, LAST_UPDATE_DATE,
LAST_UPDATED_BY, CREATION_DATE, CREATED_BY,
LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID,
PROGRAM_ID, PROGRAM_UPDATE_DATE, COMPONENT_SEQUENCE_ID,
DEPARTMENT_ID, WIP_SUPPLY_TYPE, DATE_REQUIRED,
REQUIRED_QUANTITY, QUANTITY_ISSUED, QUANTITY_PER_ASSEMBLY,
COMMENTS, SUPPLY_SUBINVENTORY, SUPPLY_LOCATOR_ID,
MRP_NET_FLAG, MPS_REQUIRED_QUANTITY, MPS_DATE_REQUIRED,
SEGMENT1, SEGMENT2, SEGMENT3,
SEGMENT4, SEGMENT5, SEGMENT6,
SEGMENT7, SEGMENT8, SEGMENT9,
SEGMENT10, SEGMENT11, SEGMENT12,
SEGMENT13, SEGMENT14, SEGMENT15,
SEGMENT16, SEGMENT17, SEGMENT18,
SEGMENT19, SEGMENT20, ATTRIBUTE_CATEGORY,
ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3,
ATTRIBUTE4, ATTRIBUTE5, ATTRIBUTE6,
ATTRIBUTE7, ATTRIBUTE8, ATTRIBUTE9,
ATTRIBUTE10, ATTRIBUTE11, ATTRIBUTE12,
ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15,
RELIEVED_MATL_COMPLETION_QTY, RELIEVED_MATL_SCRAP_QUANTITY, RELIEVED_MATL_FINAL_COMP_QTY,
QUANTITY_ALLOCATED, QUANTITY_BACKORDERED, QUANTITY_RELIEVED,
COSTED_QUANTITY_ISSUED, COSTED_QUANTITY_RELIEVED, AUTO_REQUEST_MATERIAL,
RELEASED_QUANTITY, SUGGESTED_VENDOR_NAME, VENDOR_ID,
UNIT_PRICE, BASIS_TYPE,
COMPONENT_YIELD_FACTOR, PRIMARY_COMPONENT_ID,FORECAST_ID)
SELECT INVENTORY_ITEM_ID, ORGANIZATION_ID, WIP_ENTITY_ID,
OPERATION_SEQ_NUM, REPETITIVE_SCHEDULE_ID, LAST_UPDATE_DATE,
LAST_UPDATED_BY, CREATION_DATE, CREATED_BY,
LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID,
PROGRAM_ID, PROGRAM_UPDATE_DATE, COMPONENT_SEQUENCE_ID,
DEPARTMENT_ID, WIP_SUPPLY_TYPE, DATE_REQUIRED,
REQUIRED_QUANTITY, QUANTITY_ISSUED, QUANTITY_PER_ASSEMBLY,
COMMENTS, SUPPLY_SUBINVENTORY, SUPPLY_LOCATOR_ID,
MRP_NET_FLAG, MPS_REQUIRED_QUANTITY, MPS_DATE_REQUIRED,
SEGMENT1, SEGMENT2, SEGMENT3,
SEGMENT4, SEGMENT5, SEGMENT6,
SEGMENT7, SEGMENT8, SEGMENT9,
SEGMENT10, SEGMENT11, SEGMENT12,
SEGMENT13, SEGMENT14, SEGMENT15,
SEGMENT16, SEGMENT17, SEGMENT18,
SEGMENT19, SEGMENT20, ATTRIBUTE_CATEGORY,
ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3,
ATTRIBUTE4, ATTRIBUTE5, ATTRIBUTE6,
ATTRIBUTE7, ATTRIBUTE8, ATTRIBUTE9,
ATTRIBUTE10, ATTRIBUTE11, ATTRIBUTE12,
ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15,
RELIEVED_MATL_COMPLETION_QTY, RELIEVED_MATL_SCRAP_QUANTITY, RELIEVED_MATL_FINAL_COMP_QTY,
QUANTITY_ALLOCATED, QUANTITY_BACKORDERED, QUANTITY_RELIEVED,
COSTED_QUANTITY_ISSUED, COSTED_QUANTITY_RELIEVED, AUTO_REQUEST_MATERIAL,
RELEASED_QUANTITY, SUGGESTED_VENDOR_NAME, VENDOR_ID,
UNIT_PRICE, BASIS_TYPE, COMPONENT_YIELD_FACTOR,
PRIMARY_COMPONENT_ID,
p_forecast_rec.forecast_id AS FORECAST_ID
FROM WIP_REQUIREMENT_OPERATIONS
WHERE wip_entity_id = p_wip_id_table(j);
INSERT INTO EAM_FORECAST_WO (
WIP_ENTITY_ID, OPERATION_SEQ_NUM, ORGANIZATION_ID,
REPETITIVE_SCHEDULE_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY,
CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN,
REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID,
PROGRAM_UPDATE_DATE, OPERATION_SEQUENCE_ID, STANDARD_OPERATION_ID,
DEPARTMENT_ID, DESCRIPTION, SCHEDULED_QUANTITY,
QUANTITY_IN_QUEUE, QUANTITY_RUNNING, QUANTITY_WAITING_TO_MOVE,
QUANTITY_REJECTED, QUANTITY_SCRAPPED, QUANTITY_COMPLETED,
FIRST_UNIT_START_DATE, FIRST_UNIT_COMPLETION_DATE, LAST_UNIT_START_DATE,
LAST_UNIT_COMPLETION_DATE, PREVIOUS_OPERATION_SEQ_NUM, NEXT_OPERATION_SEQ_NUM,
COUNT_POINT_TYPE, BACKFLUSH_FLAG, MINIMUM_TRANSFER_QUANTITY,
DATE_LAST_MOVED, ATTRIBUTE_CATEGORY, ATTRIBUTE1,
ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4,
ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7,
ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10,
ATTRIBUTE11, ATTRIBUTE12, ATTRIBUTE13,
ATTRIBUTE14, ATTRIBUTE15, WF_ITEMTYPE,
WF_ITEMKEY, OPERATION_YIELD, OPERATION_YIELD_ENABLED,
PRE_SPLIT_QUANTITY, OPERATION_COMPLETED, SHUTDOWN_TYPE,
X_POS, Y_POS, PREVIOUS_OPERATION_SEQ_ID,
SKIP_FLAG, LONG_DESCRIPTION, DISABLE_DATE,
CUMULATIVE_SCRAP_QUANTITY, RECOMMENDED, PROGRESS_PERCENTAGE,
WSM_OP_SEQ_NUM, ACTUAL_START_DATE, ACTUAL_COMPLETION_DATE,
WSM_BONUS_QUANTITY, EMPLOYEE_ID,
PROJECT_COMPLETION_DATE, WSM_UPDATE_QUANTITY_TXN_ID, WSM_UPDATE_QUANTITY_COMPLETED,
LOWEST_ACCEPTABLE_YIELD, FORECAST_ID)
SELECT WIP_ENTITY_ID, OPERATION_SEQ_NUM, ORGANIZATION_ID,
REPETITIVE_SCHEDULE_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY,
CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN,
REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID,
PROGRAM_UPDATE_DATE, OPERATION_SEQUENCE_ID, STANDARD_OPERATION_ID,
DEPARTMENT_ID, DESCRIPTION, SCHEDULED_QUANTITY,
QUANTITY_IN_QUEUE, QUANTITY_RUNNING, QUANTITY_WAITING_TO_MOVE,
QUANTITY_REJECTED, QUANTITY_SCRAPPED, QUANTITY_COMPLETED,
FIRST_UNIT_START_DATE, FIRST_UNIT_COMPLETION_DATE, LAST_UNIT_START_DATE,
LAST_UNIT_COMPLETION_DATE, PREVIOUS_OPERATION_SEQ_NUM, NEXT_OPERATION_SEQ_NUM,
COUNT_POINT_TYPE, BACKFLUSH_FLAG, MINIMUM_TRANSFER_QUANTITY,
DATE_LAST_MOVED, ATTRIBUTE_CATEGORY, ATTRIBUTE1,
ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4,
ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7,
ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10,
ATTRIBUTE11, ATTRIBUTE12, ATTRIBUTE13,
ATTRIBUTE14, ATTRIBUTE15, WF_ITEMTYPE,
WF_ITEMKEY, OPERATION_YIELD, OPERATION_YIELD_ENABLED,
PRE_SPLIT_QUANTITY, OPERATION_COMPLETED, SHUTDOWN_TYPE,
X_POS, Y_POS, PREVIOUS_OPERATION_SEQ_ID,
SKIP_FLAG, LONG_DESCRIPTION, DISABLE_DATE,
CUMULATIVE_SCRAP_QUANTITY, RECOMMENDED, PROGRESS_PERCENTAGE,
WSM_OP_SEQ_NUM, ACTUAL_START_DATE, ACTUAL_COMPLETION_DATE,
WSM_BONUS_QUANTITY, EMPLOYEE_ID, PROJECTED_COMPLETION_DATE,
WSM_UPDATE_QUANTITY_TXN_ID, WSM_COSTED_QUANTITY_COMPLETED, LOWEST_ACCEPTABLE_YIELD, p_forecast_rec.forecast_id AS FORECAST_ID
FROM WIP_OPERATIONS
WHERE wip_entity_id = p_wip_id_table(j);
debug('Current wo is: ' || l_current_wo || ' INSERTING COST ', 2);
debug(l_hist_cost_tbl(k).LAST_UPDATE_DATE);
debug(l_hist_cost_tbl(k).LAST_UPDATED_BY);
debug(l_hist_cost_tbl(k).LAST_UPDATE_LOGIN);
debug(l_hist_cost_tbl(k).PROGRAM_UPDATE_DATE);
INSERT INTO EAM_FORECAST_CEBBA (
PERIOD_SET_NAME, PERIOD_NAME, ACCT_PERIOD_ID,
WIP_ENTITY_ID, ORGANIZATION_ID, OPERATIONS_DEPT_ID,
OPERATION_SEQ_NUM, MAINT_COST_CATEGORY, OWNING_DEPT_ID,
ACCT_VALUE, PERIOD_START_DATE, LAST_UPDATE_DATE,
LAST_UPDATED_BY, CREATION_DATE, CREATED_BY,
LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID,
PROGRAM_ID, PROGRAM_UPDATE_DATE, FORECAST_ID,
CCID, MFG_COST_ELEMENT_ID, PERIOD_YEAR,
PERIOD_NUM)
VALUES(l_hist_cost_tbl(k).PERIOD_SET_NAME,
l_hist_cost_tbl(k).PERIOD_NAME,
l_hist_cost_tbl(k).ACCT_PERIOD_ID,
l_hist_cost_tbl(k).WIP_ENTITY_ID,
l_hist_cost_tbl(k).ORGANIZATION_ID,
l_hist_cost_tbl(k).OPERATIONS_DEPT_ID,
l_hist_cost_tbl(k).OPERATION_SEQ_NUM,
l_hist_cost_tbl(k).MAINT_COST_CATEGORY,
l_hist_cost_tbl(k).OWNING_DEPT_ID,
l_hist_cost_tbl(k).ACCT_VALUE,
l_hist_cost_tbl(k).PERIOD_START_DATE,
l_hist_cost_tbl(k).LAST_UPDATE_DATE,
l_hist_cost_tbl(k).LAST_UPDATED_BY,
l_hist_cost_tbl(k).CREATION_DATE,
l_hist_cost_tbl(k).CREATED_BY,
l_hist_cost_tbl(k).LAST_UPDATE_LOGIN,
l_hist_cost_tbl(k).REQUEST_ID,
l_hist_cost_tbl(k).PROGRAM_APPLICATION_ID,
l_hist_cost_tbl(k).PROGRAM_ID,
l_hist_cost_tbl(k).PROGRAM_UPDATE_DATE,
l_hist_cost_tbl(k).FORECAST_ID,
l_hist_cost_tbl(k).CCID,
l_hist_cost_tbl(k).MFG_COST_ELEMENT_ID,
l_hist_cost_tbl(k).PERIOD_YEAR,
l_hist_cost_tbl(k).PERIOD_NUM);
debug('Current wo is: ' || l_current_wo || ' DOnE INSERTING COST ', 2);
INSERT INTO EAM_FORECAST_CEBBA (
PERIOD_SET_NAME, PERIOD_NAME, ACCT_PERIOD_ID,
WIP_ENTITY_ID, ORGANIZATION_ID, OPERATIONS_DEPT_ID,
OPERATION_SEQ_NUM, MAINT_COST_CATEGORY, OWNING_DEPT_ID,
ACCT_VALUE, PERIOD_START_DATE, LAST_UPDATE_DATE,
LAST_UPDATED_BY, CREATION_DATE, CREATED_BY,CCID,
TXN_TYPE, MFG_COST_ELEMENT_ID, FORECAST_ID,
PERIOD_YEAR,PERIOD_NUM)
SELECT PERIOD_SET_NAME, PERIOD_NAME, ACCT_PERIOD_ID,
WIP_ENTITY_ID, ORGANIZATION_ID, OPERATIONS_DEPT_ID,
OPERATIONS_SEQ_NUM, MAINT_COST_CATEGORY, OWNING_DEPT_ID,
ACCT_VALUE, PERIOD_START_DATE, LAST_UPDATE_DATE ,
LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, ACCOUNT_ID,
TXN_TYPE, MFG_COST_ELEMENT_ID, p_forecast_rec.forecast_id as forecast_id,
1 as period_year, 1 as period_type
FROM CST_EAM_BALANCE_BY_ACCOUNTS
WHERE wip_entity_id = p_wip_id_table(j);
INSERT INTO EAM_FORECAST_WEDI (
DESCRIPTION, PURCHASING_CATEGORY_ID, DIRECT_ITEM_SEQUENCE_ID,
OPERATION_SEQ_NUM, DEPARTMENT_ID, WIP_ENTITY_ID,
ORGANIZATION_ID, SUGGESTED_VENDOR_NAME, SUGGESTED_VENDOR_ID,
SUGGESTED_VENDOR_SITE, SUGGESTED_VENDOR_SITE_ID, SUGGESTED_VENDOR_CONTACT,
SUGGESTED_VENDOR_CONTACT_ID, SUGGESTED_VENDOR_PHONE, SUGGESTED_VENDOR_ITEM_NUM,
UNIT_PRICE, AUTO_REQUEST_MATERIAL, REQUIRED_QUANTITY,
UOM, NEED_BY_DATE, ATTRIBUTE_CATEGORY,
ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3,
ATTRIBUTE4, ATTRIBUTE5, ATTRIBUTE6,
ATTRIBUTE7, ATTRIBUTE8, ATTRIBUTE9,
ATTRIBUTE10, ATTRIBUTE11, ATTRIBUTE12,
ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15,
CREATED_BY, CREATION_DATE, LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN, LAST_UPDATED_BY, PROGRAM_APPLICATION_ID,
PROGRAM_ID, PROGRAM_UPDATE_DATE, REQUEST_ID,
FORECAST_ID)
SELECT DESCRIPTION, PURCHASING_CATEGORY_ID, DIRECT_ITEM_SEQUENCE_ID,
OPERATION_SEQ_NUM, DEPARTMENT_ID, WIP_ENTITY_ID,
ORGANIZATION_ID, SUGGESTED_VENDOR_NAME, SUGGESTED_VENDOR_ID,
SUGGESTED_VENDOR_SITE, SUGGESTED_VENDOR_SITE_ID, SUGGESTED_VENDOR_CONTACT,
SUGGESTED_VENDOR_CONTACT_ID, SUGGESTED_VENDOR_PHONE, SUGGESTED_VENDOR_ITEM_NUM,
UNIT_PRICE, AUTO_REQUEST_MATERIAL, REQUIRED_QUANTITY,
UOM, NEED_BY_DATE, ATTRIBUTE_CATEGORY,
ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3,
ATTRIBUTE4, ATTRIBUTE5, ATTRIBUTE6,
ATTRIBUTE7, ATTRIBUTE8, ATTRIBUTE9,
ATTRIBUTE10, ATTRIBUTE11, ATTRIBUTE12,
ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15,
CREATED_BY, CREATION_DATE, LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN, LAST_UPDATED_BY, PROGRAM_APPLICATION_ID,
PROGRAM_ID, PROGRAM_UPDATE_DATE, REQUEST_ID,
p_forecast_rec.forecast_id AS FORECAST_ID
FROM WIP_EAM_DIRECT_ITEMS
WHERE wip_entity_id = p_wip_id_table(j);
select organization_id into l_organization_id
from eam_forecasts
where forecast_id = l_forecast_id;
SELECT XMLELEMENT("Forecast", XMLATTRIBUTES(ef.forecast_id AS "id"),
XMLAGG(XMLELEMENT("WorkOrder", XMLATTRIBUTES(efc.wip_entity_id),
XMLForest(
(SELECT (XMLAgg(
XMLELEMENT( "COST", null,
xmlforest(
periods.period_name as period,
NVL(costs.cost, 0) AS VALUE
)
)
ORDER BY periods.start_date
)
)
FROM
(SELECT glp.period_name, glp.start_date, ef2.forecast_id
FROM gl_periods glp, gl_periods glp2, gl_periods glp3, eam_forecasts ef2
WHERE
glp.start_date >= glp2.start_date
AND glp.end_date <= glp3.end_date
AND glp.period_type = glp2.period_type
AND glp.period_set_name = glp2.period_set_name
AND glp2.period_set_name = ef2.period_set_name_from
AND glp2.period_name = ef2.period_from
AND glp3.period_set_name = ef2.period_set_name_to
AND glp3.period_name = ef2.period_to
)periods,
(SELECT cebba.wip_entity_id , ef3.forecast_id, cebba.ccid, cebba.period_name AS period_name,SUM(cebba.acct_value) AS cost
FROM eam_forecast_cebba cebba, eam_forecasts ef3
WHERE cebba.forecast_id = ef3.forecast_id
GROUP BY cebba.wip_entity_id, cebba.ccid, cebba.period_name, ef3.forecast_id
) costs
WHERE
periods.forecast_id = ef.forecast_id
AND periods.forecast_id = costs.forecast_id (+)
AND periods.period_name = costs.period_name(+)
AND efc.wip_entity_id = costs.wip_entity_id (+)
AND efc.ccid = costs.ccid (+)
)AS ACCOUNT_COSTS,
entity.wip_entity_name AS NAME,
glcc.concatenated_segments AS ACCOUNT,
DECODE(NVL(job.plan_maintenance, :1), :2, :3, :4) AS SOURCE,
hou.name AS ORGANIZATION,
job.asset_number AS ASSET,
msi.concatenated_segments AS ASSETGROUP,
msi2.concatenated_segments AS Activity,
loc.location_codes AS AREA,
job.work_order_type AS WOTYPE,
bd.department_code AS DEPARTMENT,
job.class_code AS CLASS,
pjm_project.all_proj_idtonum(job.project_id) AS PROJECT
)
)))
FROM eam_forecasts ef, eam_forecast_wdj job, wip_entities entity,
mtl_system_items_kfv msi, mtl_serial_numbers msn,
mtl_eam_locations loc, mtl_system_items_kfv msi2,
bom_departments bd, hr_organization_units hou,
(SELECT DISTINCT wip_entity_id, ccid, forecast_id AS id
FROM eam_forecast_cebba )efc,
gl_code_combinations_kfv glcc
WHERE
efc.id = ef.forecast_id AND
job.wip_entity_id = efc.wip_entity_id AND
entity.wip_entity_id (+) = job.wip_entity_id AND
glcc.code_combination_id = efc.ccid AND
entity.entity_type (+) = DECODE(job.status_type,12,7,6) AND
(msi.inventory_item_id = job.asset_group_id OR
msi.inventory_item_id = job.rebuild_item_id) AND
msi.organization_id = job.organization_id AND
msn.inventory_item_id (+) = job.asset_group_id AND
msn.current_organization_id(+) = job.organization_id AND
msn.serial_number(+) = job.asset_number AND
loc.location_id (+) = msn.eam_location_id AND
msi2.inventory_item_id (+) = job.primary_item_id AND
msi2.organization_id (+) = job.organization_id AND
bd.department_id (+) = job.owning_department AND
hou.organization_id = job.organization_id AND
ef.forecast_id = :5
GROUP BY ef.forecast_ID';
SELECT meaa.asset_activity_id, fw.pm_schedule_id, fw.action_type,
fw.wip_entity_id, fw.wo_status, ewsv.system_status, fw.cycle_id, fw.seq_id,
meaa.maintenance_object_type, meaa.maintenance_object_id,
msi.inventory_item_id, msi.eam_item_type, fw.scheduled_start_date,
fw.scheduled_completion_date, fw.organization_id organization_id,
fw.pm_base_meter_reading
from eam_forecasted_work_orders fw, mtl_eam_asset_activities meaa,
eam_wo_statuses_v ewsv, csi_item_instances cii, mtl_system_items_b msi
where group_id = l_group_id and
fw.activity_association_id = meaa.activity_association_id and
ewsv.status_id=fw.wo_status and meaa.maintenance_object_type = 3 and
meaa.maintenance_object_id = cii.instance_id and cii.inventory_item_id =
msi.inventory_item_id and cii.last_vld_organization_id = msi.organization_id
union all
SELECT meaa.asset_activity_id, fw.pm_schedule_id, fw.action_type,
fw.wip_entity_id, fw.wo_status, ewsv.system_status, fw.cycle_id, fw.seq_id,
meaa.maintenance_object_type, meaa.maintenance_object_id,
meaa.maintenance_object_id, 3, fw.scheduled_start_date,
fw.scheduled_completion_date, fw.organization_id organization_id,
fw.pm_base_meter_reading
from eam_forecasted_work_orders fw, mtl_eam_asset_activities meaa,
eam_wo_statuses_v ewsv
where group_id = l_group_id and fw.activity_association_id =
meaa.activity_association_id and ewsv.status_id=fw.wo_status and
meaa.maintenance_object_type = 2 ;
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,
meaa.inventory_item_id, default_eam_class wip_acct_class,meaa.eam_item_type,
scheduled_start_date, scheduled_completion_date, meaa.organization_id
from eam_forecasted_work_orders fw, mtl_eam_asset_activities_v meaa,
wip_eam_parameters wep, mtl_system_items msi,eam_wo_statuses_v ewsv
where group_id = l_group_id
and wep.organization_id = meaa.organization_id
and fw.activity_association_id = meaa.activity_association_id
and meaa.inventory_item_id = msi.inventory_item_id
and meaa.organization_id = msi.organization_id
and ewsv.status_id=fw.wo_status;
l_eam_wo_tbl.delete;
l_eam_wo_relations_tbl.delete;
delete from eam_forecasted_work_orders
where group_id = l_group_id;