The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT ROUND(DECODE(BR.FUNCTIONAL_CURRENCY_FLAG, 1, 1,
NVL(CRC.RESOURCE_RATE,0)) * 1 -- 1 TO REPRESENT UNIT RESOURCE QUANTITY
* DECODE(NULL, 1, NULL, 2, 1, 1) ,p_ext_precision)
INTO X_UNIT_RESOURCE_VALUE
FROM CST_RESOURCE_COSTS CRC, BOM_RESOURCES BR
WHERE CRC.RESOURCE_ID = p_resource_id
AND BR.RESOURCE_ID = CRC.RESOURCE_ID
AND BR.ORGANIZATION_ID = p_org_id
AND CRC.COST_TYPE_ID = p_cost_type_id;
SELECT ROUND(SUM( 1 * -- 1 TO REPRESENT UNIT MAT QUANTITY
DECODE(MSI.EAM_ITEM_TYPE, 3,DECODE(NULL,'Y',0, NVL(CCICV.ITEM_COST,0)), NVL(CCICV.ITEM_COST,0))), p_ext_precision) MAT_VALUE
INTO x_unit_mat_value
FROM CST_CG_ITEM_COSTS_VIEW CCICV,
MTL_SYSTEM_ITEMS_B MSI
WHERE CCICV.INVENTORY_ITEM_ID = p_inv_id
AND CCICV.ORGANIZATION_ID = p_org_id
AND CCICV.COST_GROUP_ID = DECODE(p_cost_method,1,1, p_cost_group_id)
AND MSI.ORGANIZATION_ID = p_org_id
AND MSI.INVENTORY_ITEM_ID = p_inv_id
AND MSI.STOCK_ENABLED_FLAG = 'Y';
SELECT ROUND(MSIK.LIST_PRICE_PER_UNIT,p_ext_precision)
INTO x_unit_mat_value
FROM MTL_SYSTEM_ITEMS_VL MSIK
WHERE MSIK.ORGANIZATION_ID = p_org_id
AND MSIK.INVENTORY_ITEM_ID = p_inv_id
AND MSIK.STOCK_ENABLED_FLAG = 'N';
debug('Calling API - INSERT_ALL_WO_LINES');
INSERT_ALL_WO_LINES(
p_api_version => 1.0
, p_init_msg_list => FND_API.G_FALSE
, p_commit => p_commit
, p_estimate_id => l_estimate_id
, p_eam_ce_wo_lines_tbl => l_out_eam_ce_wo_lines_tbl
, x_return_status => l_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
debug('Error in API - INSERT_ALL_WO_LINES');
debug('Completed API - INSERT_ALL_WO_LINES');
PROCEDURE DELETE_WO_LINE(
p_api_version IN NUMBER := 1.0
, p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
, p_commit IN VARCHAR2
, p_work_order_line_id IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
)
IS
BEGIN
SAVEPOINT DELETE_WO_LINE;
debug('Inside API - DELETE_WO_LINE');
EAM_CE_WORK_ORDER_LINES_PKG.DELETE_ROW(
p_work_order_line_id => p_work_order_line_id
);
debug('End API - DELETE_WO_LINE');
ROLLBACK TO DELETE_WO_LINE;
debug('FND_API.G_EXC_ERROR Error in API - DELETE_WO_LINE');
ROLLBACK TO DELETE_WO_LINE;
debug('FND_API.G_EXC_UNEXPECTED_ERROR Error in API - DELETE_WO_LINE');
ROLLBACK TO DELETE_WO_LINE;
debug('OTHERS Error in API - DELETE_WO_LINE');
FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, 'DELETE_WO_LINE');
END DELETE_WO_LINE;
PROCEDURE INSERT_PARENT_WO_LINE(
p_api_version IN NUMBER := 1.0
, p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
, p_commit IN VARCHAR2
, p_estimate_id IN NUMBER
, p_parent_wo_line_rec IN EAM_EST_DATASTRUCTURES_PUB.EAM_CE_PARENT_WO_REC
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_estimate_rec EAM_EST_DATASTRUCTURES_PUB.EAM_CONSTRUCTION_ESTIMATE_REC;
l_last_updated_date DATE := SYSDATE;
l_last_updated_by NUMBER := FND_GLOBAL.USER_ID;
l_last_updated_login NUMBER;
SELECT ESTIMATE_ID ,
ORGANIZATION_ID ,
CREATE_PARENT_WO_FLAG,
PARENT_WO_ID
FROM EAM_CONSTRUCTION_ESTIMATES
WHERE ESTIMATE_ID = p_estimate_id
AND ORGANIZATION_ID = p_parent_wo_line_rec.ORGANIZATION_ID;
SELECT
SYSTEM_STATUS
FROM EAM_WO_STATUSES_V
WHERE STATUS_ID = p_user_defined_status_id
AND ENABLED_FLAG = 'Y';
SAVEPOINT INSERT_PARENT_WO_LINE;
debug('Inside API - INSERT_PARENT_WO_LINE');
EAM_CE_WORK_ORDER_LINES_PKG.UPDATE_ROW(
p_estimate_work_order_line_id => l_wo_line_id_seq
,p_estimate_work_order_id => FND_API.G_MISS_NUM
,p_src_cu_id => FND_API.G_MISS_NUM
,p_src_activity_id => FND_API.G_MISS_NUM
,p_src_activity_qty => FND_API.G_MISS_NUM
,p_src_op_seq_num => FND_API.G_MISS_NUM
,p_src_acct_class_code => FND_API.G_MISS_CHAR
,p_src_diff_id => FND_API.G_MISS_NUM
,p_diff_qty => FND_API.G_MISS_NUM
,p_estimate_id => p_estimate_id
,p_organization_id => p_parent_wo_line_rec.ORGANIZATION_ID
,p_work_order_seq_num => FND_API.G_MISS_NUM
,p_work_order_number => p_parent_wo_line_rec.PARENT_WORK_ORDER_NUMBER
,p_work_order_description => p_parent_wo_line_rec.WORK_ORDER_DESCRIPTION
,p_ref_wip_entity_id => FND_API.G_MISS_NUM
,p_primary_item_id => FND_API.G_MISS_NUM
,p_status_type => l_system_status_rec.SYSTEM_STATUS -- Bug 12541554
--,p_status_type => FND_API.G_MISS_NUM
,p_acct_class_code => p_parent_wo_line_rec.ACCT_CLASS_CODE
,p_scheduled_start_date => sysdate
,p_scheduled_completion_date => FND_API.G_MISS_DATE
,p_project_id => p_parent_wo_line_rec.PROJECT_ID
,p_task_id => p_parent_wo_line_rec.TASK_ID
,p_maintenance_object_id => p_parent_wo_line_rec.MAINTENANCE_OBJECT_ID
,p_maintenance_object_type => p_parent_wo_line_rec.MAINTENANCE_OBJECT_TYPE
,p_maintenance_object_source => p_parent_wo_line_rec.MAINTENANCE_OBJECT_SOURCE
,p_owning_department_id => p_parent_wo_line_rec.OWNING_DEPARTMENT_ID
,p_user_defined_status_id => p_parent_wo_line_rec.STATUS_TYPE
,p_op_seq_num => FND_API.G_MISS_NUM
,p_op_description => FND_API.G_MISS_CHAR
,p_standard_operation_id => FND_API.G_MISS_NUM
,p_op_department_id => FND_API.G_MISS_NUM
,p_op_long_description => FND_API.G_MISS_CHAR
,p_res_seq_num => FND_API.G_MISS_NUM
,p_res_id => FND_API.G_MISS_NUM
,p_res_uom => FND_API.G_MISS_CHAR
,p_res_basis_type => FND_API.G_MISS_NUM
,p_res_usage_rate_or_amount => FND_API.G_MISS_NUM
,p_res_required_units => FND_API.G_MISS_NUM
,p_res_assigned_units => FND_API.G_MISS_NUM
,p_item_type => FND_API.G_MISS_NUM
,p_required_quantity => FND_API.G_MISS_NUM
,p_unit_price => FND_API.G_MISS_NUM
,p_uom => FND_API.G_MISS_CHAR
,p_basis_type => FND_API.G_MISS_NUM
,p_suggested_vendor_name => FND_API.G_MISS_CHAR
,p_suggested_vendor_id => FND_API.G_MISS_NUM
,p_suggested_vendor_site => FND_API.G_MISS_CHAR
,p_suggested_vendor_site_id => FND_API.G_MISS_NUM
,p_mat_inventory_item_id => FND_API.G_MISS_NUM
,p_mat_component_seq_num => FND_API.G_MISS_NUM
,p_mat_supply_subinventory => FND_API.G_MISS_CHAR
,p_mat_supply_locator_id => FND_API.G_MISS_NUM
,p_di_amount => FND_API.G_MISS_NUM
,p_di_order_type_lookup_code => FND_API.G_MISS_CHAR
,p_di_description => FND_API.G_MISS_CHAR
,p_di_purchase_category_id => FND_API.G_MISS_NUM
,p_di_auto_request_material => FND_API.G_MISS_CHAR
,p_di_need_by_date => FND_API.G_MISS_DATE
,p_work_order_line_cost => FND_API.G_MISS_NUM
,p_creation_date => sysdate
,p_created_by => FND_GLOBAL.LOGIN_ID
,p_last_update_date => sysdate
,p_last_updated_by => FND_GLOBAL.LOGIN_ID
,p_last_update_login => FND_GLOBAL.LOGIN_ID
,p_work_order_type => FND_API.G_MISS_NUM
,p_activity_type => FND_API.G_MISS_NUM
,p_activity_source => FND_API.G_MISS_NUM
,p_activity_cause => FND_API.G_MISS_NUM
,p_available_qty => FND_API.G_MISS_NUM
,p_item_comments => FND_API.G_MISS_CHAR
,p_cu_qty => FND_API.G_MISS_NUM
,p_res_sch_flag => FND_API.G_MISS_NUM
);
SELECT EAM_CE_WORK_ORDER_LINES_S.NEXTVAL INTO l_wo_line_id_seq FROM DUAL;
EAM_CE_WORK_ORDER_LINES_PKG.INSERT_ROW(
p_estimate_work_order_line_id => l_wo_line_id_seq
,p_estimate_work_order_id => FND_API.G_MISS_NUM
,p_src_cu_id => FND_API.G_MISS_NUM
,p_src_activity_id => FND_API.G_MISS_NUM
,p_src_activity_qty => FND_API.G_MISS_NUM
,p_src_op_seq_num => FND_API.G_MISS_NUM
,p_src_acct_class_code => FND_API.G_MISS_CHAR
,p_src_diff_id => FND_API.G_MISS_NUM
,p_diff_qty => FND_API.G_MISS_NUM
,p_estimate_id => p_estimate_id
,p_organization_id => p_parent_wo_line_rec.ORGANIZATION_ID
,p_work_order_seq_num => FND_API.G_MISS_NUM
,p_work_order_number => p_parent_wo_line_rec.PARENT_WORK_ORDER_NUMBER
,p_work_order_description => p_parent_wo_line_rec.WORK_ORDER_DESCRIPTION
,p_ref_wip_entity_id => FND_API.G_MISS_NUM
,p_primary_item_id => FND_API.G_MISS_NUM
,p_status_type => l_system_status_rec.SYSTEM_STATUS -- Bug 12541554
--,p_status_type => FND_API.G_MISS_NUM
,p_acct_class_code => p_parent_wo_line_rec.ACCT_CLASS_CODE
,p_scheduled_start_date => sysdate
,p_scheduled_completion_date => FND_API.G_MISS_DATE
,p_project_id => p_parent_wo_line_rec.PROJECT_ID
,p_task_id => p_parent_wo_line_rec.TASK_ID
,p_maintenance_object_id => p_parent_wo_line_rec.MAINTENANCE_OBJECT_ID
,p_maintenance_object_type => p_parent_wo_line_rec.MAINTENANCE_OBJECT_TYPE
,p_maintenance_object_source => p_parent_wo_line_rec.MAINTENANCE_OBJECT_SOURCE
,p_owning_department_id => p_parent_wo_line_rec.OWNING_DEPARTMENT_ID
,p_user_defined_status_id => p_parent_wo_line_rec.STATUS_TYPE
,p_op_seq_num => FND_API.G_MISS_NUM
,p_op_description => FND_API.G_MISS_CHAR
,p_standard_operation_id => FND_API.G_MISS_NUM
,p_op_department_id => FND_API.G_MISS_NUM
,p_op_long_description => FND_API.G_MISS_CHAR
,p_res_seq_num => FND_API.G_MISS_NUM
,p_res_id => FND_API.G_MISS_NUM
,p_res_uom => FND_API.G_MISS_CHAR
,p_res_basis_type => FND_API.G_MISS_NUM
,p_res_usage_rate_or_amount => FND_API.G_MISS_NUM
,p_res_required_units => FND_API.G_MISS_NUM
,p_res_assigned_units => FND_API.G_MISS_NUM
,p_item_type => FND_API.G_MISS_NUM
,p_required_quantity => FND_API.G_MISS_NUM
,p_unit_price => FND_API.G_MISS_NUM
,p_uom => FND_API.G_MISS_CHAR
,p_basis_type => FND_API.G_MISS_NUM
,p_suggested_vendor_name => FND_API.G_MISS_CHAR
,p_suggested_vendor_id => FND_API.G_MISS_NUM
,p_suggested_vendor_site => FND_API.G_MISS_CHAR
,p_suggested_vendor_site_id => FND_API.G_MISS_NUM
,p_mat_inventory_item_id => FND_API.G_MISS_NUM
,p_mat_component_seq_num => FND_API.G_MISS_NUM
,p_mat_supply_subinventory => FND_API.G_MISS_CHAR
,p_mat_supply_locator_id => FND_API.G_MISS_NUM
,p_di_amount => FND_API.G_MISS_NUM
,p_di_order_type_lookup_code => FND_API.G_MISS_CHAR
,p_di_description => FND_API.G_MISS_CHAR
,p_di_purchase_category_id => FND_API.G_MISS_NUM
,p_di_auto_request_material => FND_API.G_MISS_CHAR
,p_di_need_by_date => FND_API.G_MISS_DATE
,p_work_order_line_cost => FND_API.G_MISS_NUM
,p_creation_date => sysdate
,p_created_by => FND_GLOBAL.LOGIN_ID
,p_last_update_date => sysdate
,p_last_updated_by => FND_GLOBAL.LOGIN_ID
,p_last_update_login => FND_GLOBAL.LOGIN_ID
,p_work_order_type => FND_API.G_MISS_NUM
,p_activity_type => FND_API.G_MISS_NUM
,p_activity_source => FND_API.G_MISS_NUM
,p_activity_cause => FND_API.G_MISS_NUM
,p_available_qty => FND_API.G_MISS_NUM
,p_item_comments => FND_API.G_MISS_CHAR
,p_cu_qty => FND_API.G_MISS_NUM
,p_res_sch_flag => FND_API.G_MISS_NUM
);
EAM_CONSTRUCTION_ESTIMATES_PKG.UPDATE_ROW(
p_ESTIMATE_ID => p_estimate_id,
p_ORGANIZATION_ID => l_estimate_rec.ORGANIZATION_ID,
p_ESTIMATE_NUMBER => l_estimate_rec.ESTIMATE_NUMBER,
p_ESTIMATE_DESCRIPTION => l_estimate_rec.ESTIMATE_DESCRIPTION,
p_GROUPING_OPTION => l_estimate_rec.GROUPING_OPTION,
p_PARENT_WO_ID => l_wo_line_id_seq,
p_CREATE_PARENT_WO_FLAG => p_parent_wo_line_rec.CREATE_PARENT_FLAG,
p_CREATION_DATE => l_creation_date,
p_CREATED_BY => l_created_by,
p_LAST_UPDATE_DATE => l_last_updated_date,
p_LAST_UPDATED_BY => l_last_updated_by,
p_LAST_UPDATE_LOGIN => l_last_updated_login,
p_ATTRIBUTE_CATEGORY => l_estimate_rec.attribute_category,
p_ATTRIBUTE1 => l_estimate_rec.attribute1,
p_ATTRIBUTE2 => l_estimate_rec.attribute2,
p_ATTRIBUTE3 => l_estimate_rec.attribute3,
p_ATTRIBUTE4 => l_estimate_rec.attribute4,
p_ATTRIBUTE5 => l_estimate_rec.attribute5,
p_ATTRIBUTE6 => l_estimate_rec.attribute6,
p_ATTRIBUTE7 => l_estimate_rec.attribute7,
p_ATTRIBUTE8 => l_estimate_rec.attribute8,
p_ATTRIBUTE9 => l_estimate_rec.attribute9,
p_ATTRIBUTE10 => l_estimate_rec.attribute10,
p_ATTRIBUTE11 => l_estimate_rec.attribute11,
p_ATTRIBUTE12 => l_estimate_rec.attribute12,
p_ATTRIBUTE13 => l_estimate_rec.attribute13,
p_ATTRIBUTE14 => l_estimate_rec.attribute14,
p_ATTRIBUTE15 => l_estimate_rec.attribute15
);
EAM_CONSTRUCTION_ESTIMATES_PKG.UPDATE_ROW(
p_ESTIMATE_ID => p_estimate_id,
p_ORGANIZATION_ID => l_estimate_rec.ORGANIZATION_ID,
p_ESTIMATE_NUMBER => l_estimate_rec.ESTIMATE_NUMBER,
p_ESTIMATE_DESCRIPTION => l_estimate_rec.ESTIMATE_DESCRIPTION,
p_GROUPING_OPTION => l_estimate_rec.GROUPING_OPTION,
p_PARENT_WO_ID => l_parent_wo_num,
p_CREATE_PARENT_WO_FLAG => p_parent_wo_line_rec.CREATE_PARENT_FLAG,
p_CREATION_DATE => l_creation_date,
p_CREATED_BY => l_created_by,
p_LAST_UPDATE_DATE => l_last_updated_date,
p_LAST_UPDATED_BY => l_last_updated_by,
p_LAST_UPDATE_LOGIN => l_last_updated_login,
p_ATTRIBUTE_CATEGORY => l_estimate_rec.attribute_category,
p_ATTRIBUTE1 => l_estimate_rec.attribute1,
p_ATTRIBUTE2 => l_estimate_rec.attribute2,
p_ATTRIBUTE3 => l_estimate_rec.attribute3,
p_ATTRIBUTE4 => l_estimate_rec.attribute4,
p_ATTRIBUTE5 => l_estimate_rec.attribute5,
p_ATTRIBUTE6 => l_estimate_rec.attribute6,
p_ATTRIBUTE7 => l_estimate_rec.attribute7,
p_ATTRIBUTE8 => l_estimate_rec.attribute8,
p_ATTRIBUTE9 => l_estimate_rec.attribute9,
p_ATTRIBUTE10 => l_estimate_rec.attribute10,
p_ATTRIBUTE11 => l_estimate_rec.attribute11,
p_ATTRIBUTE12 => l_estimate_rec.attribute12,
p_ATTRIBUTE13 => l_estimate_rec.attribute13,
p_ATTRIBUTE14 => l_estimate_rec.attribute14,
p_ATTRIBUTE15 => l_estimate_rec.attribute15
);
EAM_CE_WORK_ORDER_LINES_PKG.DELETE_ROW (
p_work_order_line_id => l_estimate_parent_rec.PARENT_WO_ID
);
EAM_CONSTRUCTION_ESTIMATES_PKG.UPDATE_ROW(
p_ESTIMATE_ID => p_estimate_id,
p_ORGANIZATION_ID => l_estimate_rec.ORGANIZATION_ID,
p_ESTIMATE_NUMBER => l_estimate_rec.ESTIMATE_NUMBER,
p_ESTIMATE_DESCRIPTION => l_estimate_rec.ESTIMATE_DESCRIPTION,
p_GROUPING_OPTION => l_estimate_rec.GROUPING_OPTION,
p_PARENT_WO_ID => NULL,
p_CREATE_PARENT_WO_FLAG => p_parent_wo_line_rec.CREATE_PARENT_FLAG,
p_CREATION_DATE => l_creation_date,
p_CREATED_BY => l_created_by,
p_LAST_UPDATE_DATE => l_last_updated_date,
p_LAST_UPDATED_BY => l_last_updated_by,
p_LAST_UPDATE_LOGIN => l_last_updated_login,
p_ATTRIBUTE_CATEGORY => l_estimate_rec.attribute_category,
p_ATTRIBUTE1 => l_estimate_rec.attribute1,
p_ATTRIBUTE2 => l_estimate_rec.attribute2,
p_ATTRIBUTE3 => l_estimate_rec.attribute3,
p_ATTRIBUTE4 => l_estimate_rec.attribute4,
p_ATTRIBUTE5 => l_estimate_rec.attribute5,
p_ATTRIBUTE6 => l_estimate_rec.attribute6,
p_ATTRIBUTE7 => l_estimate_rec.attribute7,
p_ATTRIBUTE8 => l_estimate_rec.attribute8,
p_ATTRIBUTE9 => l_estimate_rec.attribute9,
p_ATTRIBUTE10 => l_estimate_rec.attribute10,
p_ATTRIBUTE11 => l_estimate_rec.attribute11,
p_ATTRIBUTE12 => l_estimate_rec.attribute12,
p_ATTRIBUTE13 => l_estimate_rec.attribute13,
p_ATTRIBUTE14 => l_estimate_rec.attribute14,
p_ATTRIBUTE15 => l_estimate_rec.attribute15
);
EAM_CE_WORK_ORDER_LINES_PKG.DELETE_ROW (
p_work_order_line_id => l_estimate_parent_rec.PARENT_WO_ID
);
debug('End API - INSERT_PARENT_WO_LINE');
ROLLBACK TO INSERT_PARENT_WO_LINE;
debug('FND_API.G_EXC_ERROR Error in API - INSERT_PARENT_WO_LINE');
ROLLBACK TO INSERT_PARENT_WO_LINE;
debug('FND_API.G_EXC_UNEXPECTED_ERROR Error in API - INSERT_PARENT_WO_LINE');
ROLLBACK TO INSERT_PARENT_WO_LINE;
debug('FND_API.G_EXC_UNEXPECTED_ERROR Error in API - INSERT_PARENT_WO_LINE');
FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, 'INSERT_PARENT_WO_LINE');
END INSERT_PARENT_WO_LINE;
SELECT
p_op_seq OPERATION_SEQ_NUM,
p_op_seq_desc OPERATION_DESC,
BSOR.RESOURCE_SEQ_NUM,
BSO.ORGANIZATION_ID,
BSOR.LAST_UPDATE_DATE,
BSOR.LAST_UPDATED_BY,
BSOR.CREATION_DATE,
BSOR.CREATED_BY,
BSOR.LAST_UPDATE_LOGIN,
BSOR.REQUEST_ID,
BSOR.PROGRAM_APPLICATION_ID,
BSOR.PROGRAM_ID,
BSOR.PROGRAM_UPDATE_DATE,
BSOR.RESOURCE_ID,
BR.UNIT_OF_MEASURE,
BSOR.BASIS_TYPE,
BSOR.USAGE_RATE_OR_AMOUNT,
BSOR.ACTIVITY_ID,
BSOR.SCHEDULE_FLAG,
BSOR.ASSIGNED_UNITS,
DECODE(BSOR.AUTOCHARGE_TYPE,1,2,4,3,2,2,3,3,2) AUTOCHARGE_TYPE,
BSOR.STANDARD_RATE_FLAG,
0 APPLIED_RESOURCE_UNITS,
0 APPLIED_RESOURCE_VALUE,
SYSDATE START_DATE,
BSO.DEPARTMENT_ID,
DECODE(BSOR.SCHEDULE_FLAG,2,NULL,BSOR.RESOURCE_SEQ_NUM) ,
BSOR.SUBSTITUTE_GROUP_NUM
FROM BOM_STANDARD_OPERATIONS BSO,
BOM_STD_OP_RESOURCES BSOR,
BOM_RESOURCES BR
WHERE BSO.STANDARD_OPERATION_ID = BSOR.STANDARD_OPERATION_ID
AND BR.RESOURCE_ID = BSOR.RESOURCE_ID
AND BSO.STANDARD_OPERATION_ID = p_std_op_id
AND BSO.ORGANIZATION_ID = p_org_id;
SELECT NVL(MP.DEFAULT_COST_GROUP_ID,-1) ,
DECODE (MP.PRIMARY_COST_METHOD, 1, MP.PRIMARY_COST_METHOD, NVL(MP.AVG_RATES_COST_TYPE_ID,-1)),
MP.PRIMARY_COST_METHOD
INTO l_cost_group_id, l_cost_type_id,
l_primary_cost_method
FROM MTL_PARAMETERS MP
WHERE ORGANIZATION_ID = p_org_id;
SELECT EXTENDED_PRECISION
INTO l_ext_precision
FROM FND_CURRENCIES FC,
GL_SETS_OF_BOOKS SOB ,
HR_ORGANIZATION_INFORMATION HROI
WHERE HROI.ORGANIZATION_ID = p_org_id
AND HROI.ORG_INFORMATION1 = TO_CHAR(SOB.SET_OF_BOOKS_ID)
AND HROI.ORG_INFORMATION_CONTEXT = 'Accounting Information'
AND SOB.CURRENCY_CODE = FC.CURRENCY_CODE
AND FC.ENABLED_FLAG = 'Y';
EAM_CE_WORK_ORDER_LINES_PKG.INSERT_ROW(
p_estimate_work_order_line_id => NULL
,p_estimate_work_order_id => FND_API.G_MISS_NUM
,p_src_cu_id => NULL
,p_src_activity_id => NULL
,p_src_activity_qty => NULL
,p_src_op_seq_num => NULL
,p_src_acct_class_code => NULL
,p_src_diff_id => NULL
,p_diff_qty => NULL
,p_estimate_id => p_estimate_id
,p_organization_id => p_org_id
,p_work_order_seq_num => NULL
,p_work_order_number => NULL
,p_work_order_description => NULL
,p_ref_wip_entity_id => NULL
,p_primary_item_id => NULL
,p_status_type => NULL
,p_acct_class_code => NULL
,p_scheduled_start_date => std_op_rec.START_DATE
,p_scheduled_completion_date => NULL
,p_project_id => NULL
,p_task_id => NULL
,p_maintenance_object_id => NULL
,p_maintenance_object_type => NULL
,p_maintenance_object_source => NULL
,p_owning_department_id => std_op_rec.DEPARTMENT_ID
,p_user_defined_status_id => NULL
,p_op_seq_num => std_op_rec.OPERATION_SEQ_NUM
,p_op_description => std_op_rec.OPERATION_DESC
,p_standard_operation_id => p_std_op_id
,p_op_department_id => std_op_rec.DEPARTMENT_ID
,p_op_long_description => NULL
,p_res_seq_num => std_op_rec.RESOURCE_SEQ_NUM
,p_res_id => std_op_rec.RESOURCE_ID
,p_res_uom => std_op_rec.UNIT_OF_MEASURE
,p_res_basis_type => std_op_rec.BASIS_TYPE
,p_res_usage_rate_or_amount => std_op_rec.USAGE_RATE_OR_AMOUNT
,p_res_required_units => std_op_rec.USAGE_RATE_OR_AMOUNT
,p_res_assigned_units => std_op_rec.ASSIGNED_UNITS
,p_item_type => NULL
,p_required_quantity => std_op_rec.USAGE_RATE_OR_AMOUNT
,p_unit_price => l_unit_cost
,p_uom => NULL
,p_basis_type => NULL
,p_suggested_vendor_name => NULL
,p_suggested_vendor_id => NULL
,p_suggested_vendor_site => NULL
,p_suggested_vendor_site_id => NULL
,p_mat_inventory_item_id => NULL
,p_mat_component_seq_num => NULL
,p_mat_supply_subinventory => NULL
,p_mat_supply_locator_id => NULL
,p_di_amount => NULL
,p_di_order_type_lookup_code => NULL
,p_di_description => NULL
,p_di_purchase_category_id => NULL
,p_di_auto_request_material => NULL
,p_di_need_by_date => NULL
,p_work_order_line_cost => l_unit_cost
,p_creation_date => sysdate
,p_created_by => FND_GLOBAL.LOGIN_ID
,p_last_update_date => sysdate
,p_last_updated_by => FND_GLOBAL.LOGIN_ID
,p_last_update_login => FND_GLOBAL.LOGIN_ID
,p_work_order_type => NULL
,p_activity_type => NULL
,p_activity_source => NULL
,p_activity_cause => NULL
,p_available_qty => NULL
,p_item_comments => NULL
,p_cu_qty => NULL
,p_res_sch_flag => std_op_rec.SCHEDULE_FLAG
);
PROCEDURE INSERT_ALL_WO_LINES(
p_api_version IN NUMBER := 1.0
, p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
, p_commit IN VARCHAR2
, p_estimate_id IN NUMBER
, p_eam_ce_wo_lines_tbl IN EAM_EST_DATASTRUCTURES_PUB.EAM_CE_WORK_ORDER_LINES_TBL
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_ce_tab_index NUMBER := 0;
SELECT
SYSTEM_STATUS
FROM EAM_WO_STATUSES_V
WHERE STATUS_ID = p_user_defined_status_id
AND ENABLED_FLAG = 'Y';
SAVEPOINT INSERT_ALL_WO_LINES;
debug('Inside API - INSERT_ALL_WO_LINES');
SELECT NVL(MP.DEFAULT_COST_GROUP_ID,-1) ,
DECODE (MP.PRIMARY_COST_METHOD, 1, MP.PRIMARY_COST_METHOD, NVL(MP.AVG_RATES_COST_TYPE_ID,-1)),
MP.PRIMARY_COST_METHOD
INTO l_cost_group_id, l_cost_type_id,
l_primary_cost_method
FROM MTL_PARAMETERS MP
WHERE ORGANIZATION_ID = l_org_id;
SELECT EXTENDED_PRECISION
INTO l_ext_precision
FROM FND_CURRENCIES FC,
GL_SETS_OF_BOOKS SOB ,
HR_ORGANIZATION_INFORMATION HROI
WHERE HROI.ORGANIZATION_ID = l_org_id
AND HROI.ORG_INFORMATION1 = TO_CHAR(SOB.SET_OF_BOOKS_ID)
AND HROI.ORG_INFORMATION_CONTEXT = 'Accounting Information'
AND SOB.CURRENCY_CODE = FC.CURRENCY_CODE
AND FC.ENABLED_FLAG = 'Y';
EAM_CE_WORK_ORDER_LINES_PKG.DELETE_ALL_WITH_ESTIMATE_ID(
p_estimate_id => p_estimate_id
);
debug(' Beginning insert/update in to EAM_CE_WORK_ORDER_LINES');
EAM_CE_WORK_ORDER_LINES_PKG.UPDATE_ROW(
p_estimate_work_order_line_id => p_eam_ce_wo_lines_tbl(l_ce_tab_index).ESTIMATE_WORK_ORDER_LINE_ID
,p_estimate_work_order_id => FND_API.G_MISS_NUM
,p_src_cu_id => p_eam_ce_wo_lines_tbl(l_ce_tab_index).SRC_CU_ID
,p_src_activity_id => p_eam_ce_wo_lines_tbl(l_ce_tab_index).SRC_ACTIVITY_ID
,p_src_activity_qty => p_eam_ce_wo_lines_tbl(l_ce_tab_index).SRC_ACTIVITY_QTY
,p_src_op_seq_num => p_eam_ce_wo_lines_tbl(l_ce_tab_index).SRC_OP_SEQ_NUM
,p_src_acct_class_code => p_eam_ce_wo_lines_tbl(l_ce_tab_index).SRC_ACCT_CLASS_CODE
,p_src_diff_id => p_eam_ce_wo_lines_tbl(l_ce_tab_index).DIFFICULTY_ID
,p_diff_qty => p_eam_ce_wo_lines_tbl(l_ce_tab_index).DIFFICULTY_QTY
,p_estimate_id => p_eam_ce_wo_lines_tbl(l_ce_tab_index).ESTIMATE_ID
,p_organization_id => p_eam_ce_wo_lines_tbl(l_ce_tab_index).ORGANIZATION_ID
,p_work_order_seq_num => p_eam_ce_wo_lines_tbl(l_ce_tab_index).WORK_ORDER_SEQ_NUM
,p_work_order_number => p_eam_ce_wo_lines_tbl(l_ce_tab_index).WORK_ORDER_NUMBER
,p_work_order_description => p_eam_ce_wo_lines_tbl(l_ce_tab_index).WORK_ORDER_DESCRIPTION
,p_ref_wip_entity_id => p_eam_ce_wo_lines_tbl(l_ce_tab_index).REF_WIP_ENTITY_ID
,p_primary_item_id => p_eam_ce_wo_lines_tbl(l_ce_tab_index).PRIMARY_ITEM_ID
,p_status_type => l_system_status_rec.SYSTEM_STATUS
,p_acct_class_code => p_eam_ce_wo_lines_tbl(l_ce_tab_index).ACCT_CLASS_CODE
,p_scheduled_start_date => p_eam_ce_wo_lines_tbl(l_ce_tab_index).SCHEDULED_START_DATE
,p_scheduled_completion_date => p_eam_ce_wo_lines_tbl(l_ce_tab_index).SCHEDULED_COMPLETION_DATE
,p_project_id => p_eam_ce_wo_lines_tbl(l_ce_tab_index).PROJECT_ID
,p_task_id => p_eam_ce_wo_lines_tbl(l_ce_tab_index).TASK_ID
,p_maintenance_object_id => p_eam_ce_wo_lines_tbl(l_ce_tab_index).MAINTENANCE_OBJECT_ID
,p_maintenance_object_type => p_eam_ce_wo_lines_tbl(l_ce_tab_index).MAINTENANCE_OBJECT_TYPE
,p_maintenance_object_source => p_eam_ce_wo_lines_tbl(l_ce_tab_index).MAINTENANCE_OBJECT_SOURCE
,p_owning_department_id => p_eam_ce_wo_lines_tbl(l_ce_tab_index).OWNING_DEPARTMENT_ID
,p_user_defined_status_id => p_eam_ce_wo_lines_tbl(l_ce_tab_index).USER_DEFINED_STATUS_ID
,p_op_seq_num => p_eam_ce_wo_lines_tbl(l_ce_tab_index).OP_SEQ_NUM
,p_op_description => p_eam_ce_wo_lines_tbl(l_ce_tab_index).OP_DESCRIPTION
,p_standard_operation_id => p_eam_ce_wo_lines_tbl(l_ce_tab_index).STANDARD_OPERATION_ID
,p_op_department_id => p_eam_ce_wo_lines_tbl(l_ce_tab_index).OP_DEPARTMENT_ID
,p_op_long_description => p_eam_ce_wo_lines_tbl(l_ce_tab_index).OP_LONG_DESCRIPTION
,p_res_seq_num => p_eam_ce_wo_lines_tbl(l_ce_tab_index).RES_SEQ_NUM
,p_res_id => p_eam_ce_wo_lines_tbl(l_ce_tab_index).RES_ID
,p_res_uom => p_eam_ce_wo_lines_tbl(l_ce_tab_index).RES_UOM
,p_res_basis_type => p_eam_ce_wo_lines_tbl(l_ce_tab_index).RES_BASIS_TYPE
,p_res_usage_rate_or_amount => p_eam_ce_wo_lines_tbl(l_ce_tab_index).RES_USAGE_RATE_OR_AMOUNT
,p_res_required_units => p_eam_ce_wo_lines_tbl(l_ce_tab_index).RES_REQUIRED_UNITS
,p_res_assigned_units => p_eam_ce_wo_lines_tbl(l_ce_tab_index).RES_ASSIGNED_UNITS
,p_item_type => p_eam_ce_wo_lines_tbl(l_ce_tab_index).ITEM_TYPE
,p_required_quantity => p_eam_ce_wo_lines_tbl(l_ce_tab_index).REQUIRED_QUANTITY
,p_unit_price => p_eam_ce_wo_lines_tbl(l_ce_tab_index).UNIT_PRICE
,p_uom => p_eam_ce_wo_lines_tbl(l_ce_tab_index).UOM
,p_basis_type => p_eam_ce_wo_lines_tbl(l_ce_tab_index).BASIS_TYPE
,p_suggested_vendor_name => p_eam_ce_wo_lines_tbl(l_ce_tab_index).SUGGESTED_VENDOR_NAME
,p_suggested_vendor_id => p_eam_ce_wo_lines_tbl(l_ce_tab_index).SUGGESTED_VENDOR_ID
,p_suggested_vendor_site => p_eam_ce_wo_lines_tbl(l_ce_tab_index).SUGGESTED_VENDOR_SITE
,p_suggested_vendor_site_id => p_eam_ce_wo_lines_tbl(l_ce_tab_index).SUGGESTED_VENDOR_SITE_ID
,p_mat_inventory_item_id => p_eam_ce_wo_lines_tbl(l_ce_tab_index).MAT_INVENTORY_ITEM_ID
,p_mat_component_seq_num => p_eam_ce_wo_lines_tbl(l_ce_tab_index).MAT_COMPONENT_SEQ_NUM
,p_mat_supply_subinventory => p_eam_ce_wo_lines_tbl(l_ce_tab_index).MAT_SUPPLY_SUBINVENTORY
,p_mat_supply_locator_id => p_eam_ce_wo_lines_tbl(l_ce_tab_index).MAT_SUPPLY_LOCATOR_ID
,p_di_amount => p_eam_ce_wo_lines_tbl(l_ce_tab_index).DI_AMOUNT
,p_di_order_type_lookup_code => p_eam_ce_wo_lines_tbl(l_ce_tab_index).DI_ORDER_TYPE_LOOKUP_CODE
,p_di_description => p_eam_ce_wo_lines_tbl(l_ce_tab_index).DI_DESCRIPTION
,p_di_purchase_category_id => p_eam_ce_wo_lines_tbl(l_ce_tab_index).DI_PURCHASE_CATEGORY_ID
,p_di_auto_request_material => p_eam_ce_wo_lines_tbl(l_ce_tab_index).DI_AUTO_REQUEST_MATERIAL
,p_di_need_by_date => p_eam_ce_wo_lines_tbl(l_ce_tab_index).DI_NEED_BY_DATE
,p_work_order_line_cost => l_unit_cost
,p_creation_date => sysdate
,p_created_by => FND_GLOBAL.LOGIN_ID
,p_last_update_date => sysdate
,p_last_updated_by => FND_GLOBAL.LOGIN_ID
,p_last_update_login => FND_GLOBAL.LOGIN_ID
,p_work_order_type => p_eam_ce_wo_lines_tbl(l_ce_tab_index).WORK_ORDER_TYPE
,p_activity_type => p_eam_ce_wo_lines_tbl(l_ce_tab_index).ACTIVITY_TYPE
,p_activity_source => p_eam_ce_wo_lines_tbl(l_ce_tab_index).ACTIVITY_SOURCE
,p_activity_cause => p_eam_ce_wo_lines_tbl(l_ce_tab_index).ACTIVITY_CAUSE
,p_available_qty => p_eam_ce_wo_lines_tbl(l_ce_tab_index).AVAILABLE_QUANTITY
,p_item_comments => p_eam_ce_wo_lines_tbl(l_ce_tab_index).ITEM_COMMENTS
,p_cu_qty => p_eam_ce_wo_lines_tbl(l_ce_tab_index).CU_QTY
,p_res_sch_flag => p_eam_ce_wo_lines_tbl(l_ce_tab_index).RES_SCHEDULED_FLAG
);
EAM_CE_WORK_ORDER_LINES_PKG.INSERT_ROW(
p_estimate_work_order_line_id => p_eam_ce_wo_lines_tbl(l_ce_tab_index).ESTIMATE_WORK_ORDER_LINE_ID
,p_estimate_work_order_id => FND_API.G_MISS_NUM
,p_src_cu_id => p_eam_ce_wo_lines_tbl(l_ce_tab_index).SRC_CU_ID
,p_src_activity_id => p_eam_ce_wo_lines_tbl(l_ce_tab_index).SRC_ACTIVITY_ID
,p_src_activity_qty => p_eam_ce_wo_lines_tbl(l_ce_tab_index).SRC_ACTIVITY_QTY
,p_src_op_seq_num => p_eam_ce_wo_lines_tbl(l_ce_tab_index).SRC_OP_SEQ_NUM
,p_src_acct_class_code => p_eam_ce_wo_lines_tbl(l_ce_tab_index).SRC_ACCT_CLASS_CODE
,p_src_diff_id => p_eam_ce_wo_lines_tbl(l_ce_tab_index).DIFFICULTY_ID
,p_diff_qty => p_eam_ce_wo_lines_tbl(l_ce_tab_index).DIFFICULTY_QTY
,p_estimate_id => p_eam_ce_wo_lines_tbl(l_ce_tab_index).ESTIMATE_ID
,p_organization_id => p_eam_ce_wo_lines_tbl(l_ce_tab_index).ORGANIZATION_ID
,p_work_order_seq_num => p_eam_ce_wo_lines_tbl(l_ce_tab_index).WORK_ORDER_SEQ_NUM
,p_work_order_number => p_eam_ce_wo_lines_tbl(l_ce_tab_index).WORK_ORDER_NUMBER
,p_work_order_description => p_eam_ce_wo_lines_tbl(l_ce_tab_index).WORK_ORDER_DESCRIPTION
,p_ref_wip_entity_id => p_eam_ce_wo_lines_tbl(l_ce_tab_index).REF_WIP_ENTITY_ID
,p_primary_item_id => p_eam_ce_wo_lines_tbl(l_ce_tab_index).PRIMARY_ITEM_ID
,p_status_type => l_system_status_rec.SYSTEM_STATUS
,p_acct_class_code => p_eam_ce_wo_lines_tbl(l_ce_tab_index).ACCT_CLASS_CODE
,p_scheduled_start_date => p_eam_ce_wo_lines_tbl(l_ce_tab_index).SCHEDULED_START_DATE
,p_scheduled_completion_date => p_eam_ce_wo_lines_tbl(l_ce_tab_index).SCHEDULED_COMPLETION_DATE
,p_project_id => p_eam_ce_wo_lines_tbl(l_ce_tab_index).PROJECT_ID
,p_task_id => p_eam_ce_wo_lines_tbl(l_ce_tab_index).TASK_ID
,p_maintenance_object_id => p_eam_ce_wo_lines_tbl(l_ce_tab_index).MAINTENANCE_OBJECT_ID
,p_maintenance_object_type => p_eam_ce_wo_lines_tbl(l_ce_tab_index).MAINTENANCE_OBJECT_TYPE
,p_maintenance_object_source => p_eam_ce_wo_lines_tbl(l_ce_tab_index).MAINTENANCE_OBJECT_SOURCE
,p_owning_department_id => p_eam_ce_wo_lines_tbl(l_ce_tab_index).OWNING_DEPARTMENT_ID
,p_user_defined_status_id => p_eam_ce_wo_lines_tbl(l_ce_tab_index).USER_DEFINED_STATUS_ID
,p_op_seq_num => p_eam_ce_wo_lines_tbl(l_ce_tab_index).OP_SEQ_NUM
,p_op_description => p_eam_ce_wo_lines_tbl(l_ce_tab_index).OP_DESCRIPTION
,p_standard_operation_id => p_eam_ce_wo_lines_tbl(l_ce_tab_index).STANDARD_OPERATION_ID
,p_op_department_id => p_eam_ce_wo_lines_tbl(l_ce_tab_index).OP_DEPARTMENT_ID
,p_op_long_description => p_eam_ce_wo_lines_tbl(l_ce_tab_index).OP_LONG_DESCRIPTION
,p_res_seq_num => p_eam_ce_wo_lines_tbl(l_ce_tab_index).RES_SEQ_NUM
,p_res_id => p_eam_ce_wo_lines_tbl(l_ce_tab_index).RES_ID
,p_res_uom => p_eam_ce_wo_lines_tbl(l_ce_tab_index).RES_UOM
,p_res_basis_type => p_eam_ce_wo_lines_tbl(l_ce_tab_index).RES_BASIS_TYPE
,p_res_usage_rate_or_amount => p_eam_ce_wo_lines_tbl(l_ce_tab_index).RES_USAGE_RATE_OR_AMOUNT
,p_res_required_units => p_eam_ce_wo_lines_tbl(l_ce_tab_index).RES_REQUIRED_UNITS
,p_res_assigned_units => p_eam_ce_wo_lines_tbl(l_ce_tab_index).RES_ASSIGNED_UNITS
,p_item_type => p_eam_ce_wo_lines_tbl(l_ce_tab_index).ITEM_TYPE
,p_required_quantity => p_eam_ce_wo_lines_tbl(l_ce_tab_index).REQUIRED_QUANTITY
,p_unit_price => p_eam_ce_wo_lines_tbl(l_ce_tab_index).UNIT_PRICE
,p_uom => p_eam_ce_wo_lines_tbl(l_ce_tab_index).UOM
,p_basis_type => p_eam_ce_wo_lines_tbl(l_ce_tab_index).BASIS_TYPE
,p_suggested_vendor_name => p_eam_ce_wo_lines_tbl(l_ce_tab_index).SUGGESTED_VENDOR_NAME
,p_suggested_vendor_id => p_eam_ce_wo_lines_tbl(l_ce_tab_index).SUGGESTED_VENDOR_ID
,p_suggested_vendor_site => p_eam_ce_wo_lines_tbl(l_ce_tab_index).SUGGESTED_VENDOR_SITE
,p_suggested_vendor_site_id => p_eam_ce_wo_lines_tbl(l_ce_tab_index).SUGGESTED_VENDOR_SITE_ID
,p_mat_inventory_item_id => p_eam_ce_wo_lines_tbl(l_ce_tab_index).MAT_INVENTORY_ITEM_ID
,p_mat_component_seq_num => p_eam_ce_wo_lines_tbl(l_ce_tab_index).MAT_COMPONENT_SEQ_NUM
,p_mat_supply_subinventory => p_eam_ce_wo_lines_tbl(l_ce_tab_index).MAT_SUPPLY_SUBINVENTORY
,p_mat_supply_locator_id => p_eam_ce_wo_lines_tbl(l_ce_tab_index).MAT_SUPPLY_LOCATOR_ID
,p_di_amount => p_eam_ce_wo_lines_tbl(l_ce_tab_index).DI_AMOUNT
,p_di_order_type_lookup_code => p_eam_ce_wo_lines_tbl(l_ce_tab_index).DI_ORDER_TYPE_LOOKUP_CODE
,p_di_description => p_eam_ce_wo_lines_tbl(l_ce_tab_index).DI_DESCRIPTION
,p_di_purchase_category_id => p_eam_ce_wo_lines_tbl(l_ce_tab_index).DI_PURCHASE_CATEGORY_ID
,p_di_auto_request_material => p_eam_ce_wo_lines_tbl(l_ce_tab_index).DI_AUTO_REQUEST_MATERIAL
,p_di_need_by_date => p_eam_ce_wo_lines_tbl(l_ce_tab_index).DI_NEED_BY_DATE
,p_work_order_line_cost => l_unit_cost
,p_creation_date => sysdate
,p_created_by => FND_GLOBAL.LOGIN_ID
,p_last_update_date => sysdate
,p_last_updated_by => FND_GLOBAL.LOGIN_ID
,p_last_update_login => FND_GLOBAL.LOGIN_ID
,p_work_order_type => p_eam_ce_wo_lines_tbl(l_ce_tab_index).WORK_ORDER_TYPE
,p_activity_type => p_eam_ce_wo_lines_tbl(l_ce_tab_index).ACTIVITY_TYPE
,p_activity_source => p_eam_ce_wo_lines_tbl(l_ce_tab_index).ACTIVITY_SOURCE
,p_activity_cause => p_eam_ce_wo_lines_tbl(l_ce_tab_index).ACTIVITY_CAUSE
,p_available_qty => p_eam_ce_wo_lines_tbl(l_ce_tab_index).AVAILABLE_QUANTITY
,p_item_comments => p_eam_ce_wo_lines_tbl(l_ce_tab_index).ITEM_COMMENTS
,p_cu_qty => p_eam_ce_wo_lines_tbl(l_ce_tab_index).CU_QTY
,p_res_sch_flag => p_eam_ce_wo_lines_tbl(l_ce_tab_index).RES_SCHEDULED_FLAG
);
debug(' End of insert/update in to EAM_CE_WORK_ORDER_LINES. Total Inserted - ' || p_eam_ce_wo_lines_tbl.count);
debug('End of API - INSERT_ALL_WO_LINES');
ROLLBACK TO INSERT_ALL_WO_LINES;
debug('FND_API.G_EXC_ERROR Error in API INSERT_ALL_WO_LINES');
ROLLBACK TO INSERT_ALL_WO_LINES;
debug('FND_API.G_EXC_UNEXPECTED_ERROR Error in API INSERT_ALL_WO_LINES');
ROLLBACK TO INSERT_ALL_WO_LINES;
debug('OTHERS Error in API INSERT_ALL_WO_LINES');
FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, 'INSERT_ALL_WO_LINES');
END INSERT_ALL_WO_LINES;
SELECT ESTIMATE_ASSOCIATION_ID,
ORGANIZATION_ID,
ESTIMATE_ID,
CU_ID,
ACCT_CLASS_CODE,
ACTIVITY_ID,
ACTIVITY_QTY,
RESOURCE_MULTIPLIER,
DIFFICULTY_ID,
CU_QTY
FROM EAM_ESTIMATE_ASSOCIATIONS
WHERE ESTIMATE_ID = p_estimate_id;
DELETE FROM EAM_ESTIMATE_ASSOCIATIONS WHERE ESTIMATE_ID = p_estimate_id;
SELECT NVL(MP.DEFAULT_COST_GROUP_ID,-1) ,
DECODE (MP.PRIMARY_COST_METHOD, 1, MP.PRIMARY_COST_METHOD, NVL(MP.AVG_RATES_COST_TYPE_ID,-1)),
MP.PRIMARY_COST_METHOD
INTO l_cost_group_id, l_cost_type_id,
l_primary_cost_method
FROM MTL_PARAMETERS MP
WHERE ORGANIZATION_ID = p_ce_associatin_rec.ORGANIZATION_ID;
SELECT EXTENDED_PRECISION
INTO l_ext_precision
FROM FND_CURRENCIES FC,
GL_SETS_OF_BOOKS SOB ,
HR_ORGANIZATION_INFORMATION HROI
WHERE HROI.ORGANIZATION_ID = p_ce_associatin_rec.ORGANIZATION_ID
AND HROI.ORG_INFORMATION1 = TO_CHAR(SOB.SET_OF_BOOKS_ID)
AND HROI.ORG_INFORMATION_CONTEXT = 'Accounting Information'
AND SOB.CURRENCY_CODE = FC.CURRENCY_CODE
AND FC.ENABLED_FLAG = 'Y';
SELECT CU_NAME,
DESCRIPTION,
ORGANIZATION_ID,
CU_EFFECTIVE_FROM,
CU_EFFECTIVE_TO
INTO px_cu_tbl(l_index).CU_NAME,
px_cu_tbl(l_index).DESCRIPTION,
px_cu_tbl(l_index).ORGANIZATION_ID,
px_cu_tbl(l_index).CU_EFFECTIVE_FROM,
px_cu_tbl(l_index).CU_EFFECTIVE_TO
FROM EAM_CONSTRUCTION_UNITS
WHERE CU_ID = px_cu_tbl(l_index).CU_ID
AND ORGANIZATION_ID = p_org_id
AND CU_EFFECTIVE_FROM <= SYSDATE
AND (CU_EFFECTIVE_TO IS NULL OR CU_EFFECTIVE_TO > SYSDATE);
SELECT ECU.CU_ID,
ECU.ORGANIZATION_ID,
ECUD.ACCT_CLASS_CODE,
ECUD.ACTIVITY_ID,
ECUD.CU_ACTIVITY_QTY
FROM EAM_CONSTRUCTION_UNITS ECU,
EAM_CONSTRUCTION_UNIT_DETAILS ECUD
WHERE ECU.CU_ID = p_cu_id
AND ECU.CU_ID = ECUD.CU_ID
AND ECU.CU_EFFECTIVE_FROM < SYSDATE + 1
AND (ECU.CU_EFFECTIVE_TO IS NULL OR ECU.CU_EFFECTIVE_TO > SYSDATE)
AND (ECUD.CU_ACTIVITY_EFFECTIVE_TO IS NULL OR ECUD.CU_ACTIVITY_EFFECTIVE_TO > SYSDATE);
SELECT CU_NAME,
DESCRIPTION
INTO l_activities_rec.CU_NAME,
l_activities_rec.CU_DESCRIPTION
FROM EAM_CONSTRUCTION_UNITS
WHERE CU_ID = l_activities_rec.CU_ID;
SELECT CONCATENATED_SEGMENTS,
DESCRIPTION
INTO l_activities_rec.ACTIVITY_NAME,
l_activities_rec.ACTIVITY_DESCRIPTION
FROM MTL_SYSTEM_ITEMS_KFV
WHERE INVENTORY_ITEM_ID = l_activities_rec.ACTIVITY_ID
AND ORGANIZATION_ID = l_activities_rec.ORGANIZATION_ID;
l_last_updated_date DATE := SYSDATE;
l_last_updated_by NUMBER := FND_GLOBAL.USER_ID;
l_last_updated_login NUMBER;
SELECT 'Y'
INTO l_estimate_exist
FROM EAM_CONSTRUCTION_ESTIMATES
WHERE ESTIMATE_NUMBER = px_estimate_rec.ESTIMATE_NUMBER
AND ROWNUM = 1;
EAM_CONSTRUCTION_ESTIMATES_PKG.INSERT_ROW(
px_ESTIMATE_ID => px_estimate_rec.ESTIMATE_ID,
p_ORGANIZATION_ID => px_estimate_rec.ORGANIZATION_ID,
p_ESTIMATE_NUMBER => px_estimate_rec.ESTIMATE_NUMBER,
p_ESTIMATE_DESCRIPTION => px_estimate_rec.ESTIMATE_DESCRIPTION,
p_GROUPING_OPTION => px_estimate_rec.GROUPING_OPTION,
p_PARENT_WO_ID => px_estimate_rec.PARENT_WO_ID,
p_CREATE_PARENT_WO_FLAG => px_estimate_rec.CREATE_PARENT_WO_FLAG,
p_CREATION_DATE => l_creation_date,
p_CREATED_BY => l_created_by,
p_LAST_UPDATE_DATE => l_last_updated_date,
p_LAST_UPDATED_BY => l_last_updated_by,
p_LAST_UPDATE_LOGIN => l_last_updated_login,
p_ATTRIBUTE_CATEGORY => px_estimate_rec.attribute_category,
p_ATTRIBUTE1 => px_estimate_rec.attribute1,
p_ATTRIBUTE2 => px_estimate_rec.attribute2,
p_ATTRIBUTE3 => px_estimate_rec.attribute3,
p_ATTRIBUTE4 => px_estimate_rec.attribute4,
p_ATTRIBUTE5 => px_estimate_rec.attribute5,
p_ATTRIBUTE6 => px_estimate_rec.attribute6,
p_ATTRIBUTE7 => px_estimate_rec.attribute7,
p_ATTRIBUTE8 => px_estimate_rec.attribute8,
p_ATTRIBUTE9 => px_estimate_rec.attribute9,
p_ATTRIBUTE10 => px_estimate_rec.attribute10,
p_ATTRIBUTE11 => px_estimate_rec.attribute11,
p_ATTRIBUTE12 => px_estimate_rec.attribute12,
p_ATTRIBUTE13 => px_estimate_rec.attribute13,
p_ATTRIBUTE14 => px_estimate_rec.attribute14,
p_ATTRIBUTE15 => px_estimate_rec.attribute15
);
PROCEDURE UPDATE_ESTIMATE(
p_api_version IN NUMBER,
p_commit IN VARCHAR2,
p_init_msg_list IN VARCHAR2,
p_validation_level IN NUMBER,
p_estimate_rec IN EAM_EST_DATASTRUCTURES_PUB.EAM_CONSTRUCTION_ESTIMATE_REC,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_ESTIMATE';
l_last_updated_date DATE := SYSDATE;
l_last_updated_by NUMBER := FND_GLOBAL.USER_ID;
l_last_updated_login NUMBER;
SAVEPOINT UPDATE_ESTIMATE;
DEBUG('UPDATE_ESTIMATE ' ||
p_api_version ||'-'||
p_commit ||'-'||
p_init_msg_list ||'-'||
p_validation_level);
SELECT 'Y'
INTO l_estimate_exist
FROM EAM_CONSTRUCTION_ESTIMATES
WHERE ESTIMATE_NUMBER = p_estimate_rec.ESTIMATE_NUMBER
AND ESTIMATE_ID <> p_estimate_rec.ESTIMATE_ID
AND ROWNUM = 1;
EAM_CONSTRUCTION_ESTIMATES_PKG.UPDATE_ROW(
p_ESTIMATE_ID => p_estimate_rec.ESTIMATE_ID,
p_ORGANIZATION_ID => p_estimate_rec.ORGANIZATION_ID,
p_ESTIMATE_NUMBER => p_estimate_rec.ESTIMATE_NUMBER,
p_ESTIMATE_DESCRIPTION => p_estimate_rec.ESTIMATE_DESCRIPTION,
p_GROUPING_OPTION => p_estimate_rec.GROUPING_OPTION,
p_PARENT_WO_ID => l_parent_wo_id,
p_CREATE_PARENT_WO_FLAG => l_parent_creation_flag,
p_CREATION_DATE => l_creation_date,
p_CREATED_BY => l_created_by,
p_LAST_UPDATE_DATE => l_last_updated_date,
p_LAST_UPDATED_BY => l_last_updated_by,
p_LAST_UPDATE_LOGIN => l_last_updated_login,
p_ATTRIBUTE_CATEGORY => p_estimate_rec.attribute_category,
p_ATTRIBUTE1 => p_estimate_rec.attribute1,
p_ATTRIBUTE2 => p_estimate_rec.attribute2,
p_ATTRIBUTE3 => p_estimate_rec.attribute3,
p_ATTRIBUTE4 => p_estimate_rec.attribute4,
p_ATTRIBUTE5 => p_estimate_rec.attribute5,
p_ATTRIBUTE6 => p_estimate_rec.attribute6,
p_ATTRIBUTE7 => p_estimate_rec.attribute7,
p_ATTRIBUTE8 => p_estimate_rec.attribute8,
p_ATTRIBUTE9 => p_estimate_rec.attribute9,
p_ATTRIBUTE10 => p_estimate_rec.attribute10,
p_ATTRIBUTE11 => p_estimate_rec.attribute11,
p_ATTRIBUTE12 => p_estimate_rec.attribute12,
p_ATTRIBUTE13 => p_estimate_rec.attribute13,
p_ATTRIBUTE14 => p_estimate_rec.attribute14,
p_ATTRIBUTE15 => p_estimate_rec.attribute15
);
DEBUG('Update estimate with ID : ' || p_estimate_rec.ESTIMATE_ID);
ROLLBACK TO UPDATE_ESTIMATE;
ROLLBACK TO UPDATE_ESTIMATE;
ROLLBACK TO UPDATE_ESTIMATE;
END UPDATE_ESTIMATE;
l_last_updated_date DATE := SYSDATE;
l_last_updated_by NUMBER := FND_GLOBAL.USER_ID;
l_last_updated_login NUMBER;
SELECT ESTIMATE_ID,
ORGANIZATION_ID
INTO l_ce_id,
l_organization_id
FROM EAM_CONSTRUCTION_ESTIMATES
WHERE ESTIMATE_ID = p_ce_id;
EAM_ESTIMATE_ASSOCIATIONS_PKG.INSERT_ROW(
px_ESTIMATE_ASSOCIATION_ID => l_est_association_id,
p_ORGANIZATION_ID => l_organization_id,
p_ESTIMATE_ID => l_ce_id,
p_CU_ID => l_cu_id,
p_CU_QTY => l_cu_qty,
p_ACCT_CLASS_CODE => l_acct_class_code,
p_ACTIVITY_ID => l_activity_id,
p_ACTIVITY_QTY => l_activity_qty,
p_DIFFICULTY_ID => l_difficulty_id,
p_RESOURCE_MULTIPLIER => l_resource_multiplier,
p_CREATION_DATE => l_creation_date,
p_CREATED_BY => l_created_by,
p_LAST_UPDATE_DATE => l_last_updated_date,
p_LAST_UPDATED_BY => l_last_updated_by,
p_LAST_UPDATE_LOGIN => l_last_updated_login
);
SELECT *
FROM EAM_CE_WORK_ORDER_LINES
WHERE ESTIMATE_ID = p_ce_id
AND estimate_work_order_line_id NOT IN (SELECT parent_wo_id
FROM EAM_CONSTRUCTION_ESTIMATES
WHERE estimate_id = p_ce_id
AND nvl(create_parent_wo_flag, 'N') = 'Y');
SELECT ECWOL.*,
NVL(ECWOL.OP_SEQ_NUM, ECWOL.SRC_OP_SEQ_NUM) OP_SEQ_NUM_DERIVED
FROM EAM_CE_WORK_ORDER_LINES ECWOL
WHERE ECWOL.ESTIMATE_ID = p_ce_id
AND ECWOL.estimate_work_order_line_id NOT IN (SELECT parent_wo_id
FROM EAM_CONSTRUCTION_ESTIMATES
WHERE estimate_id = p_ce_id
AND nvl(create_parent_wo_flag, 'N') = 'Y')
ORDER BY ECWOL.SRC_CU_ID,
ECWOL.SRC_ACTIVITY_ID,
OP_SEQ_NUM_DERIVED,
ECWOL.RES_SEQ_NUM,
ECWOL.MAT_COMPONENT_SEQ_NUM,
ECWOL.DI_DESCRIPTION;
SELECT ECWOL.*,
NVL(ECWOL.OP_SEQ_NUM, ECWOL.SRC_OP_SEQ_NUM) OP_SEQ_NUM_DERIVED
FROM EAM_CE_WORK_ORDER_LINES ECWOL
WHERE ECWOL.ESTIMATE_ID = p_ce_id
AND ECWOL.estimate_work_order_line_id NOT IN (SELECT parent_wo_id
FROM EAM_CONSTRUCTION_ESTIMATES
WHERE estimate_id = p_ce_id
AND nvl(create_parent_wo_flag, 'N') = 'Y')
ORDER BY ECWOL.SRC_ACCT_CLASS_CODE,
ECWOL.SRC_CU_ID,
ECWOL.SRC_ACTIVITY_ID,
OP_SEQ_NUM_DERIVED,
ECWOL.RES_SEQ_NUM,
ECWOL.MAT_COMPONENT_SEQ_NUM,
ECWOL.DI_DESCRIPTION;
PROCEDURE UPDATE_CE_WO_LNS_BY_GROUP_OPT(
p_api_version IN NUMBER,
p_commit IN VARCHAR2,
p_init_msg_list IN VARCHAR2,
p_validation_level IN NUMBER,
p_ce_wo_defaults IN EAM_EST_DATASTRUCTURES_PUB.EAM_CE_WO_DEFAULTS_REC,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
--Note: When order by a column, all rows with that column having the value of NULL
--would come last in the sorted list
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_CE_WO_LNS_BY_GROUP_OPT';
SAVEPOINT UPDATE_CE_WO_LNS_BY_GROUP_OPT;
DEBUG('UPDATE_CE_WO_LNS_BY_GROUP_OPT ' ||
p_api_version ||'-'||
p_commit ||'-'||
p_init_msg_list ||'-'||
p_validation_level);
SELECT MEANING
INTO l_group_option
FROM MFG_LOOKUPS
WHERE LOOKUP_TYPE = 'CONSTRUCTION_GROUP_OPTIONS'
AND ENABLED_FLAG = 'Y'
AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE-1)
AND NVL(END_DATE_ACTIVE, SYSDATE+1)
AND LOOKUP_CODE = p_ce_wo_defaults.GROUPING_OPTION;
EAM_CE_WORK_ORDER_LINES_PKG.UPDATE_ROW(
p_estimate_work_order_line_id => l_ce_wo_ln_tbl(i).ESTIMATE_WORK_ORDER_LINE_ID,
p_estimate_work_order_id => FND_API.G_MISS_NUM,
p_src_cu_id => l_ce_wo_ln_tbl(i).SRC_CU_ID,
p_src_activity_id => l_ce_wo_ln_tbl(i).SRC_ACTIVITY_ID,
p_src_activity_qty => l_ce_wo_ln_tbl(i).SRC_ACTIVITY_QTY,
p_src_op_seq_num => l_ce_wo_ln_tbl(i).SRC_OP_SEQ_NUM,
p_src_acct_class_code => l_ce_wo_ln_tbl(i).SRC_ACCT_CLASS_CODE,
p_src_diff_id => l_ce_wo_ln_tbl(i).DIFFICULTY_ID,
p_diff_qty => l_ce_wo_ln_tbl(i).DIFFICULTY_QTY,
p_estimate_id => l_ce_wo_ln_tbl(i).ESTIMATE_ID,
p_organization_id => l_ce_wo_ln_tbl(i).ORGANIZATION_ID,
p_work_order_seq_num => l_ce_wo_ln_tbl(i).WORK_ORDER_SEQ_NUM,
p_work_order_number => l_ce_wo_ln_tbl(i).WORK_ORDER_NUMBER,
p_work_order_description => l_ce_wo_ln_tbl(i).WORK_ORDER_DESCRIPTION,
p_ref_wip_entity_id => l_ce_wo_ln_tbl(i).REF_WIP_ENTITY_ID,
p_primary_item_id => l_ce_wo_ln_tbl(i).PRIMARY_ITEM_ID,
p_status_type => l_ce_wo_ln_tbl(i).STATUS_TYPE,
p_acct_class_code => l_ce_wo_ln_tbl(i).ACCT_CLASS_CODE,
p_scheduled_start_date => l_ce_wo_ln_tbl(i).SCHEDULED_START_DATE,
p_scheduled_completion_date => l_ce_wo_ln_tbl(i).SCHEDULED_COMPLETION_DATE,
p_project_id => l_ce_wo_ln_tbl(i).PROJECT_ID,
p_task_id => l_ce_wo_ln_tbl(i).TASK_ID,
p_maintenance_object_id => l_ce_wo_ln_tbl(i).MAINTENANCE_OBJECT_ID,
p_maintenance_object_type => l_ce_wo_ln_tbl(i).MAINTENANCE_OBJECT_TYPE,
p_maintenance_object_source => l_ce_wo_ln_tbl(i).MAINTENANCE_OBJECT_SOURCE,
p_owning_department_id => l_ce_wo_ln_tbl(i).OWNING_DEPARTMENT_ID,
p_user_defined_status_id => l_ce_wo_ln_tbl(i).USER_DEFINED_STATUS_ID,
p_op_seq_num => l_ce_wo_ln_tbl(i).OP_SEQ_NUM,
p_op_description => l_ce_wo_ln_tbl(i).OP_DESCRIPTION,
p_standard_operation_id => l_ce_wo_ln_tbl(i).STANDARD_OPERATION_ID,
p_op_department_id => l_ce_wo_ln_tbl(i).OP_DEPARTMENT_ID,
p_op_long_description => l_ce_wo_ln_tbl(i).OP_LONG_DESCRIPTION,
p_res_seq_num => l_ce_wo_ln_tbl(i).RES_SEQ_NUM,
p_res_id => l_ce_wo_ln_tbl(i).RES_ID,
p_res_uom => l_ce_wo_ln_tbl(i).RES_UOM,
p_res_basis_type => l_ce_wo_ln_tbl(i).RES_BASIS_TYPE,
p_res_usage_rate_or_amount => l_ce_wo_ln_tbl(i).RES_USAGE_RATE_OR_AMOUNT,
p_res_required_units => l_ce_wo_ln_tbl(i).RES_REQUIRED_UNITS,
p_res_assigned_units => l_ce_wo_ln_tbl(i).RES_ASSIGNED_UNITS,
p_item_type => l_ce_wo_ln_tbl(i).ITEM_TYPE,
p_required_quantity => l_ce_wo_ln_tbl(i).REQUIRED_QUANTITY,
p_unit_price => l_ce_wo_ln_tbl(i).UNIT_PRICE,
p_uom => l_ce_wo_ln_tbl(i).UOM,
p_basis_type => l_ce_wo_ln_tbl(i).BASIS_TYPE,
p_suggested_vendor_name => l_ce_wo_ln_tbl(i).SUGGESTED_VENDOR_NAME,
p_suggested_vendor_id => l_ce_wo_ln_tbl(i).SUGGESTED_VENDOR_ID,
p_suggested_vendor_site => l_ce_wo_ln_tbl(i).SUGGESTED_VENDOR_SITE,
p_suggested_vendor_site_id => l_ce_wo_ln_tbl(i).SUGGESTED_VENDOR_SITE_ID,
p_mat_inventory_item_id => l_ce_wo_ln_tbl(i).MAT_INVENTORY_ITEM_ID,
p_mat_component_seq_num => l_ce_wo_ln_tbl(i).MAT_COMPONENT_SEQ_NUM,
p_mat_supply_subinventory => l_ce_wo_ln_tbl(i).MAT_SUPPLY_SUBINVENTORY,
p_mat_supply_locator_id => l_ce_wo_ln_tbl(i).MAT_SUPPLY_LOCATOR_ID,
p_di_amount => l_ce_wo_ln_tbl(i).DI_AMOUNT,
p_di_order_type_lookup_code => l_ce_wo_ln_tbl(i).DI_ORDER_TYPE_LOOKUP_CODE,
p_di_description => l_ce_wo_ln_tbl(i).DI_DESCRIPTION,
p_di_purchase_category_id => l_ce_wo_ln_tbl(i).DI_PURCHASE_CATEGORY_ID,
p_di_auto_request_material => l_ce_wo_ln_tbl(i).DI_AUTO_REQUEST_MATERIAL,
p_di_need_by_date => l_ce_wo_ln_tbl(i).DI_NEED_BY_DATE,
p_work_order_line_cost => l_ce_wo_ln_tbl(i).WORK_ORDER_LINE_COST,
p_creation_date => FND_API.G_MISS_DATE,
p_created_by => FND_API.G_MISS_NUM,
p_last_update_date => SYSDATE,
p_last_updated_by => FND_GLOBAL.USER_ID,
p_last_update_login => FND_GLOBAL.USER_ID
,p_work_order_type => l_ce_wo_ln_tbl(i).WORK_ORDER_TYPE
,p_activity_type => l_ce_wo_ln_tbl(i).ACTIVITY_TYPE
,p_activity_source => l_ce_wo_ln_tbl(i).ACTIVITY_SOURCE
,p_activity_cause => l_ce_wo_ln_tbl(i).ACTIVITY_CAUSE
,p_available_qty => l_ce_wo_ln_tbl(i).AVAILABLE_QUANTITY
,p_item_comments => l_ce_wo_ln_tbl(i).ITEM_COMMENTS
,p_cu_qty => l_ce_wo_ln_tbl(i).CU_QTY
,p_res_sch_flag => l_ce_wo_ln_tbl(i).RES_SCHEDULED_FLAG);
ROLLBACK TO UPDATE_CE_WO_LNS_BY_GROUP_OPT;
ROLLBACK TO UPDATE_CE_WO_LNS_BY_GROUP_OPT;
ROLLBACK TO UPDATE_CE_WO_LNS_BY_GROUP_OPT;
END UPDATE_CE_WO_LNS_BY_GROUP_OPT;
SELECT *
FROM EAM_CE_WORK_ORDER_LINES
WHERE organization_id = p_organization_id
AND estimate_id = p_estimate_id
AND estimate_work_order_line_id NOT IN (SELECT parent_wo_id
FROM EAM_CONSTRUCTION_ESTIMATES
WHERE ORGANIZATION_ID = P_ORGANIzATION_ID
AND estimate_id = p_estimate_id
AND nvl(create_parent_wo_flag, 'N') = 'Y'
)
ORDER BY
work_order_seq_num,
op_seq_num,
res_seq_num;
SELECT nvl(create_parent_wo_flag, 'N'), parent_wo_id
INTO l_create_parent, l_parent_job_id
FROM EAM_CONSTRUCTION_ESTIMATES
WHERE organization_id = p_organization_id
AND estimate_id = p_estimate_id;
SELECT 1
INTO l_wo_seq_exists
FROM EAM_CE_WORK_ORDER_LINES
WHERE organization_id = p_organization_id
AND estimate_id = p_estimate_id
AND work_order_seq_num IS NULL
AND rownum = 1;
SELECT 1
INTO l_wo_seq_exists
FROM EAM_CE_WORK_ORDER_LINES
WHERE organization_id = p_organization_id
AND estimate_id = p_estimate_id
AND work_order_seq_num IS NULL
AND estimate_work_order_line_id <> l_parent_job_id
AND rownum = 1;
SELECT 1
INTO l_wo_exists
FROM EAM_CE_WORK_ORDER_LINES
WHERE organization_id = p_organization_id
AND estimate_id = p_estimate_id
AND estimate_work_order_id IS NOT NULL
AND rownum = 1;
SELECT 1
INTO l_noasset
FROM EAM_CE_WORK_ORDER_LINES
WHERE organization_id = p_organization_id
AND estimate_id = p_estimate_id
AND maintenance_object_id IS NULL
AND rownum = 1;
SELECT *
INTO l_ce_parent_rec
FROM EAM_CE_WORK_ORDER_LINES
WHERE estimate_work_order_line_id = l_parent_job_id;
UPDATE EAM_CE_WORK_ORDER_LINES
SET estimate_work_order_id = l_eam_wo_tbl1(1).wip_entity_id
WHERE estimate_work_order_line_id = l_parent_job_id;
UPDATE eam_work_order_details
SET estimate_id = p_estimate_id
WHERE wip_entity_id = l_eam_wo_tbl1(1).wip_entity_id
AND organization_id = l_eam_wo_tbl1(1).organization_id;
UPDATE EAM_CE_WORK_ORDER_LINES
SET estimate_work_order_id = l_eam_wo_tbl1(i).wip_entity_id
WHERE work_order_seq_num = l_eam_wo_tbl1(i).batch_id
and estimate_id = p_estimate_id;
UPDATE eam_work_order_details
SET estimate_id = p_estimate_id
WHERE wip_entity_id = l_eam_wo_tbl1(i).wip_entity_id
AND organization_id = l_eam_wo_tbl1(i).organization_id;
SELECT cii.inventory_item_id, msi.eam_item_type
INTO l_item_id, l_eam_item
FROM csi_item_instances cii, mtl_system_items_b msi
WHERE cii.inventory_item_id = msi.inventory_item_id
AND cii.last_vld_organization_id = msi.organization_id
AND cii.instance_id = p_ce_line_rec.maintenance_object_id;
SELECT efsa.failure_code_required
INTO l_failure_required
FROM eam_failure_set_associations efsa , eam_failure_sets efs
WHERE efsa.inventory_item_id = l_item_id
AND efsa.set_id = efs.set_id (+)
AND (efsa.EFFECTIVE_END_DATE is null or efs.EFFECTIVE_END_DATE >= sysdate )
AND ( efs.EFFECTIVE_END_DATE is null or efs.EFFECTIVE_END_DATE >=sysdate ) ;
select DECODE(BOR.AUTOCHARGE_TYPE,1,2,4,3,3,3,2) autocharge
into l_autocharge
from BOM_OPERATION_RESOURCES BOR, BOM_OPERATIONAL_ROUTINGS BORT, BOM_OPERATION_SEQUENCES BOS
where BOR.RESOURCE_ID = p_ce_line_rec.res_id
and BORT.assembly_item_id = p_ce_line_rec.SRC_ACTIVITY_ID
AND BORT.organization_id = p_ce_line_rec.organization_id
AND BOS.ROUTING_SEQUENCE_ID = BORT.COMMON_ROUTING_SEQUENCE_ID
AND BOS.OPERATION_SEQUENCE_ID = BOR.OPERATION_SEQUENCE_ID
AND BOS.EFFECTIVITY_DATE <= sysdate
AND NVL(BOS.DISABLE_DATE, sysdate + 2) >= sysdate
AND (BOR.ACD_TYPE IS NULL OR BOR.ACD_TYPE <> 3);
l_last_updated_date DATE := SYSDATE;
l_last_updated_by NUMBER := FND_GLOBAL.USER_ID;
l_last_updated_login NUMBER;
SELECT CEWOL.*
FROM EAM_CE_WORK_ORDER_LINES CEWOL
WHERE ESTIMATE_ID = p_src_estimate_id
AND ORGANIZATION_ID = p_org_id
ORDER BY ESTIMATE_WORK_ORDER_LINE_ID;
SELECT ECE.*
FROM EAM_CONSTRUCTION_ESTIMATES ECE
WHERE ESTIMATE_ID = p_src_estimate_id
AND ORGANIZATION_ID = p_org_id;
EAM_CONSTRUCTION_ESTIMATES_PKG.INSERT_ROW(
px_ESTIMATE_ID => l_estimate_rec.ESTIMATE_ID,
p_ORGANIZATION_ID => l_estimate_rec.ORGANIZATION_ID,
p_ESTIMATE_NUMBER => l_estimate_rec.ESTIMATE_NUMBER,
p_ESTIMATE_DESCRIPTION => l_estimate_rec.ESTIMATE_DESCRIPTION,
p_GROUPING_OPTION => l_estimate_rec.GROUPING_OPTION,
p_PARENT_WO_ID => l_estimate_rec.PARENT_WO_ID,
p_CREATE_PARENT_WO_FLAG => l_estimate_rec.CREATE_PARENT_WO_FLAG,
p_CREATION_DATE => l_creation_date,
p_CREATED_BY => l_created_by,
p_LAST_UPDATE_DATE => l_last_updated_date,
p_LAST_UPDATED_BY => l_last_updated_by,
p_LAST_UPDATE_LOGIN => l_last_updated_login,
p_ATTRIBUTE_CATEGORY => l_estimate_rec.attribute_category,
p_ATTRIBUTE1 => l_estimate_rec.attribute1,
p_ATTRIBUTE2 => l_estimate_rec.attribute2,
p_ATTRIBUTE3 => l_estimate_rec.attribute3,
p_ATTRIBUTE4 => l_estimate_rec.attribute4,
p_ATTRIBUTE5 => l_estimate_rec.attribute5,
p_ATTRIBUTE6 => l_estimate_rec.attribute6,
p_ATTRIBUTE7 => l_estimate_rec.attribute7,
p_ATTRIBUTE8 => l_estimate_rec.attribute8,
p_ATTRIBUTE9 => l_estimate_rec.attribute9,
p_ATTRIBUTE10 => l_estimate_rec.attribute10,
p_ATTRIBUTE11 => l_estimate_rec.attribute11,
p_ATTRIBUTE12 => l_estimate_rec.attribute12,
p_ATTRIBUTE13 => l_estimate_rec.attribute13,
p_ATTRIBUTE14 => l_estimate_rec.attribute14,
p_ATTRIBUTE15 => l_estimate_rec.attribute15
);
SELECT EAM_CE_WORK_ORDER_LINES_S.NEXTVAL INTO l_wo_line_id_seq FROM DUAL;
EAM_CE_WORK_ORDER_LINES_PKG.INSERT_ROW(
p_estimate_work_order_line_id => l_wo_line_id_seq
,p_estimate_work_order_id => NULL
,p_src_cu_id => l_est_workorder_line_rec.SRC_CU_ID
,p_src_activity_id => l_est_workorder_line_rec.SRC_ACTIVITY_ID
,p_src_activity_qty => l_est_workorder_line_rec.SRC_ACTIVITY_QTY
,p_src_op_seq_num => l_est_workorder_line_rec.SRC_OP_SEQ_NUM
,p_src_acct_class_code => l_est_workorder_line_rec.SRC_ACCT_CLASS_CODE
,p_src_diff_id => l_est_workorder_line_rec.SRC_DIFFICULTY_ID
,p_diff_qty => l_est_workorder_line_rec.DIFFICULTY_QTY
,p_estimate_id => p_cpy_estimate_id
,p_organization_id => l_est_workorder_line_rec.ORGANIZATION_ID
,p_work_order_seq_num => NULL
,p_work_order_number => NULL
,p_work_order_description => NULL
,p_ref_wip_entity_id => NULL
,p_primary_item_id => l_est_workorder_line_rec.PRIMARY_ITEM_ID
,p_status_type => l_est_workorder_line_rec.STATUS_TYPE
,p_acct_class_code => l_est_workorder_line_rec.ACCT_CLASS_CODE
,p_scheduled_start_date => sysdate
,p_scheduled_completion_date => NULL
,p_project_id => l_est_workorder_line_rec.PROJECT_ID
,p_task_id => l_est_workorder_line_rec.TASK_ID
,p_maintenance_object_id => NULL
,p_maintenance_object_type => NULL
,p_maintenance_object_source => NULL
,p_owning_department_id => l_est_workorder_line_rec.OWNING_DEPARTMENT_ID
,p_user_defined_status_id => NULL
,p_op_seq_num => l_est_workorder_line_rec.OP_SEQ_NUM
,p_op_description => l_est_workorder_line_rec.OP_DESCRIPTION
,p_standard_operation_id => l_est_workorder_line_rec.STANDARD_OPERATION_ID
,p_op_department_id => l_est_workorder_line_rec.OP_DEPARTMENT_ID
,p_op_long_description => l_est_workorder_line_rec.OP_LONG_DESCRIPTION
,p_res_seq_num => l_est_workorder_line_rec.RES_SEQ_NUM
,p_res_id => l_est_workorder_line_rec.RES_ID
,p_res_uom => l_est_workorder_line_rec.RES_UOM
,p_res_basis_type => l_est_workorder_line_rec.RES_BASIS_TYPE
,p_res_usage_rate_or_amount => l_est_workorder_line_rec.RES_USAGE_RATE_OR_AMOUNT
,p_res_required_units => l_est_workorder_line_rec.RES_REQUIRED_UNITS
,p_res_assigned_units => l_est_workorder_line_rec.RES_ASSIGNED_UNITS
,p_item_type => l_est_workorder_line_rec.ITEM_TYPE
,p_required_quantity => l_est_workorder_line_rec.REQUIRED_QUANTITY
,p_unit_price => l_est_workorder_line_rec.UNIT_PRICE
,p_uom => l_est_workorder_line_rec.UOM
,p_basis_type => l_est_workorder_line_rec.BASIS_TYPE
,p_suggested_vendor_name => l_est_workorder_line_rec.SUGGESTED_VENDOR_NAME
,p_suggested_vendor_id => l_est_workorder_line_rec.SUGGESTED_VENDOR_ID
,p_suggested_vendor_site => l_est_workorder_line_rec.SUGGESTED_VENDOR_SITE
,p_suggested_vendor_site_id => l_est_workorder_line_rec.SUGGESTED_VENDOR_SITE_ID
,p_mat_inventory_item_id => l_est_workorder_line_rec.MAT_INVENTORY_ITEM_ID
,p_mat_component_seq_num => l_est_workorder_line_rec.MAT_COMPONENT_SEQ_NUM
,p_mat_supply_subinventory => l_est_workorder_line_rec.MAT_SUPPLY_SUBINVENTORY
,p_mat_supply_locator_id => l_est_workorder_line_rec.MAT_SUPPLY_LOCATOR_ID
,p_di_amount => l_est_workorder_line_rec.DI_AMOUNT
,p_di_order_type_lookup_code => l_est_workorder_line_rec.DI_ORDER_TYPE_LOOKUP_CODE
,p_di_description => l_est_workorder_line_rec.DI_DESCRIPTION
,p_di_purchase_category_id => l_est_workorder_line_rec.DI_PURCHASE_CATEGORY_ID
,p_di_auto_request_material => l_est_workorder_line_rec.DI_AUTO_REQUEST_MATERIAL
,p_di_need_by_date => l_est_workorder_line_rec.DI_NEED_BY_DATE
,p_work_order_line_cost => l_est_workorder_line_rec.WO_LINE_PER_UNIT_COST
,p_creation_date => sysdate
,p_created_by => FND_GLOBAL.LOGIN_ID
,p_last_update_date => sysdate
,p_last_updated_by => FND_GLOBAL.LOGIN_ID
,p_last_update_login => FND_GLOBAL.LOGIN_ID
,p_work_order_type => l_est_workorder_line_rec.WORK_ORDER_TYPE
,p_activity_type => l_est_workorder_line_rec.ACTIVITY_TYPE
,p_activity_source => l_est_workorder_line_rec.ACTIVITY_SOURCE
,p_activity_cause => l_est_workorder_line_rec.ACTIVITY_CAUSE
,p_available_qty => l_est_workorder_line_rec.AVAILABLE_QUANTITY
,p_item_comments => l_est_workorder_line_rec.ITEM_COMMENTS
,p_cu_qty => l_est_workorder_line_rec.CU_QTY
,p_res_sch_flag => l_est_workorder_line_rec.RES_SCHEDULED_FLAG
);
EAM_CONSTRUCTION_ESTIMATES_PKG.UPDATE_ROW(
p_ESTIMATE_ID => l_estimate_rec.ESTIMATE_ID,
p_ORGANIZATION_ID => l_estimate_rec.ORGANIZATION_ID,
p_ESTIMATE_NUMBER => l_estimate_rec.ESTIMATE_NUMBER,
p_ESTIMATE_DESCRIPTION => l_estimate_rec.ESTIMATE_DESCRIPTION,
p_GROUPING_OPTION => l_estimate_rec.GROUPING_OPTION,
p_PARENT_WO_ID => l_wo_line_id_seq,
p_CREATE_PARENT_WO_FLAG => l_estimate_rec.CREATE_PARENT_WO_FLAG,
p_CREATION_DATE => l_creation_date,
p_CREATED_BY => l_created_by,
p_LAST_UPDATE_DATE => l_last_updated_date,
p_LAST_UPDATED_BY => l_last_updated_by,
p_LAST_UPDATE_LOGIN => l_last_updated_login,
p_ATTRIBUTE_CATEGORY => l_estimate_rec.attribute_category,
p_ATTRIBUTE1 => l_estimate_rec.attribute1,
p_ATTRIBUTE2 => l_estimate_rec.attribute2,
p_ATTRIBUTE3 => l_estimate_rec.attribute3,
p_ATTRIBUTE4 => l_estimate_rec.attribute4,
p_ATTRIBUTE5 => l_estimate_rec.attribute5,
p_ATTRIBUTE6 => l_estimate_rec.attribute6,
p_ATTRIBUTE7 => l_estimate_rec.attribute7,
p_ATTRIBUTE8 => l_estimate_rec.attribute8,
p_ATTRIBUTE9 => l_estimate_rec.attribute9,
p_ATTRIBUTE10 => l_estimate_rec.attribute10,
p_ATTRIBUTE11 => l_estimate_rec.attribute11,
p_ATTRIBUTE12 => l_estimate_rec.attribute12,
p_ATTRIBUTE13 => l_estimate_rec.attribute13,
p_ATTRIBUTE14 => l_estimate_rec.attribute14,
p_ATTRIBUTE15 => l_estimate_rec.attribute15
);
EAM_CE_WORK_ORDER_LINES_PKG.INSERT_ROW(
p_estimate_work_order_line_id => NULL
,p_estimate_work_order_id => NULL
,p_src_cu_id => l_est_workorder_line_rec.SRC_CU_ID
,p_src_activity_id => l_est_workorder_line_rec.SRC_ACTIVITY_ID
,p_src_activity_qty => l_est_workorder_line_rec.SRC_ACTIVITY_QTY
,p_src_op_seq_num => l_est_workorder_line_rec.SRC_OP_SEQ_NUM
,p_src_acct_class_code => l_est_workorder_line_rec.SRC_ACCT_CLASS_CODE
,p_src_diff_id => l_est_workorder_line_rec.SRC_DIFFICULTY_ID
,p_diff_qty => l_est_workorder_line_rec.DIFFICULTY_QTY
,p_estimate_id => p_cpy_estimate_id
,p_organization_id => l_est_workorder_line_rec.ORGANIZATION_ID
,p_work_order_seq_num => NULL
,p_work_order_number => NULL
,p_work_order_description => NULL
,p_ref_wip_entity_id => NULL
,p_primary_item_id => l_est_workorder_line_rec.PRIMARY_ITEM_ID
,p_status_type => l_est_workorder_line_rec.STATUS_TYPE
,p_acct_class_code => l_est_workorder_line_rec.ACCT_CLASS_CODE
,p_scheduled_start_date => sysdate
,p_scheduled_completion_date => NULL
,p_project_id => l_est_workorder_line_rec.PROJECT_ID
,p_task_id => l_est_workorder_line_rec.TASK_ID
,p_maintenance_object_id => NULL
,p_maintenance_object_type => NULL
,p_maintenance_object_source => NULL
,p_owning_department_id => l_est_workorder_line_rec.OWNING_DEPARTMENT_ID
,p_user_defined_status_id => l_est_workorder_line_rec.USER_DEFINED_STATUS_ID
,p_op_seq_num => l_est_workorder_line_rec.OP_SEQ_NUM
,p_op_description => l_est_workorder_line_rec.OP_DESCRIPTION
,p_standard_operation_id => l_est_workorder_line_rec.STANDARD_OPERATION_ID
,p_op_department_id => l_est_workorder_line_rec.OP_DEPARTMENT_ID
,p_op_long_description => l_est_workorder_line_rec.OP_LONG_DESCRIPTION
,p_res_seq_num => l_est_workorder_line_rec.RES_SEQ_NUM
,p_res_id => l_est_workorder_line_rec.RES_ID
,p_res_uom => l_est_workorder_line_rec.RES_UOM
,p_res_basis_type => l_est_workorder_line_rec.RES_BASIS_TYPE
,p_res_usage_rate_or_amount => l_est_workorder_line_rec.RES_USAGE_RATE_OR_AMOUNT
,p_res_required_units => l_est_workorder_line_rec.RES_REQUIRED_UNITS
,p_res_assigned_units => l_est_workorder_line_rec.RES_ASSIGNED_UNITS
,p_item_type => l_est_workorder_line_rec.ITEM_TYPE
,p_required_quantity => l_est_workorder_line_rec.REQUIRED_QUANTITY
,p_unit_price => l_est_workorder_line_rec.UNIT_PRICE
,p_uom => l_est_workorder_line_rec.UOM
,p_basis_type => l_est_workorder_line_rec.BASIS_TYPE
,p_suggested_vendor_name => l_est_workorder_line_rec.SUGGESTED_VENDOR_NAME
,p_suggested_vendor_id => l_est_workorder_line_rec.SUGGESTED_VENDOR_ID
,p_suggested_vendor_site => l_est_workorder_line_rec.SUGGESTED_VENDOR_SITE
,p_suggested_vendor_site_id => l_est_workorder_line_rec.SUGGESTED_VENDOR_SITE_ID
,p_mat_inventory_item_id => l_est_workorder_line_rec.MAT_INVENTORY_ITEM_ID
,p_mat_component_seq_num => l_est_workorder_line_rec.MAT_COMPONENT_SEQ_NUM
,p_mat_supply_subinventory => l_est_workorder_line_rec.MAT_SUPPLY_SUBINVENTORY
,p_mat_supply_locator_id => l_est_workorder_line_rec.MAT_SUPPLY_LOCATOR_ID
,p_di_amount => l_est_workorder_line_rec.DI_AMOUNT
,p_di_order_type_lookup_code => l_est_workorder_line_rec.DI_ORDER_TYPE_LOOKUP_CODE
,p_di_description => l_est_workorder_line_rec.DI_DESCRIPTION
,p_di_purchase_category_id => l_est_workorder_line_rec.DI_PURCHASE_CATEGORY_ID
,p_di_auto_request_material => l_est_workorder_line_rec.DI_AUTO_REQUEST_MATERIAL
,p_di_need_by_date => l_est_workorder_line_rec.DI_NEED_BY_DATE
,p_work_order_line_cost => l_est_workorder_line_rec.WO_LINE_PER_UNIT_COST
,p_creation_date => sysdate
,p_created_by => FND_GLOBAL.LOGIN_ID
,p_last_update_date => sysdate
,p_last_updated_by => FND_GLOBAL.LOGIN_ID
,p_last_update_login => FND_GLOBAL.LOGIN_ID
,p_work_order_type => l_est_workorder_line_rec.WORK_ORDER_TYPE
,p_activity_type => l_est_workorder_line_rec.ACTIVITY_TYPE
,p_activity_source => l_est_workorder_line_rec.ACTIVITY_SOURCE
,p_activity_cause => l_est_workorder_line_rec.ACTIVITY_CAUSE
,p_available_qty => l_est_workorder_line_rec.AVAILABLE_QUANTITY
,p_item_comments => l_est_workorder_line_rec.ITEM_COMMENTS
,p_cu_qty => l_est_workorder_line_rec.CU_QTY
,p_res_sch_flag => l_est_workorder_line_rec.RES_SCHEDULED_FLAG
);