[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select entity_type into l_entity_type
from wip_entities
where wip_entity_id = p_wip_entity_id;
select parent_wip_entity_id,
asset_group_id,
asset_number,
organization_id,
rebuild_item_id,
manual_rebuild_flag,
status_type,
shutdown_type
into x_parent_wip_entity_id,
x_asset_group_id,
x_asset_number,
x_organization_id,
l_rebuild_item_id,
x_manual_rebuild_flag,
l_status_type,
x_shutdown_type
from wip_discrete_jobs
where wip_entity_id = p_wip_entity_id;
select max(actual_end_date) into l_max_op_end_date
from eam_op_completion_txns
where wip_entity_id = p_wip_entity_id and transaction_type = G_TXN_TYPE_COMPLETE;
select min(actual_start_date) into l_min_op_start_date
from eam_op_completion_txns
where wip_entity_id = p_wip_entity_id and transaction_type = G_TXN_TYPE_COMPLETE;
select nvl(min(period_start_date),sysdate+2)
into l_min_open_period_start_date
from org_acct_periods
where organization_id = p_organization_id
and open_flag = 'Y';
select nvl(min(period_start_date),sysdate+2)
into l_min_open_period_start_date
from org_acct_periods
where organization_id = p_organization_id
and open_flag = 'Y';
select max(transaction_date) into l_max_tran_date
from eam_job_completion_txns where transaction_type = 1
and wip_entity_id = p_wip_entity_id;
select actual_start_date, actual_end_date into
x_actual_start_date, x_actual_end_date
from eam_job_completion_txns
where transaction_date = l_max_tran_date;
select asset_inventory into l_asset_inventory
from mtl_secondary_inventories
where secondary_inventory_name = p_inventory_item_info(1).subinventory
and organization_id = p_organization_id;
select location_control_code into l_location_control_code
from mtl_system_items
where inventory_item_id = p_asset_group_id and
organization_id = p_organization_id;
select stock_locator_control_code into l_stock_locator_control_code
from mtl_parameters
where organization_id = p_organization_id;
select locator_type into l_locator_type
from mtl_secondary_inventories
where secondary_inventory_name = l_subinventory
and organization_id = p_organization_id;
select count(*) into l_count
from mtl_item_locations
where organization_id = p_organization_id and inventory_location_id = l_locator and subinventory_code = l_subinventory;
select maintenance_object_source, rebuild_item_id
into l_maintenance_source_id, l_rebuild_item_id
from wip_discrete_jobs
where wip_entity_id = p_wip_entity_id;
l_db_last_update_date DATE;
l_last_updated_by wip_operations.last_updated_by%type;
l_last_update_login wip_operations.last_update_login%type;
l_last_update_date wip_operations.last_update_date%type;
select
wo.department_id,
wo.operation_completed,
wo.shutdown_type,
wo.description,
wo.quantity_completed,
wo.first_unit_start_date,
wo.last_unit_completion_date,
wo.created_by,
wo.creation_date,
wo.last_updated_by,
wo.last_update_login,
wo.last_update_date
into
l_department_id,
l_operation_completed,
l_shutdown_type,
l_description,
l_quantity_completed,
l_first_unit_start_date,
l_last_unit_completion_date,
l_created_by,
l_creation_date,
l_last_updated_by,
l_last_update_login,
l_last_update_date
from
wip_operations wo
where
wip_entity_id = p_wip_entity_id and
operation_seq_num =p_operation_seq_num;
select nvl(min(period_start_date),sysdate+1)
into l_open_acct_per_date
from org_acct_periods
where organization_id = (select organization_id from wip_entities where wip_entity_id=p_wip_entity_id)
and open_flag = 'Y';
select count(*) into l_count from wip_discrete_jobs where
wip_entity_id = p_wip_entity_id and
organization_id = p_organization_id;
update wip_discrete_jobs set manual_rebuild_flag = p_manual_rebuild_flag
where wip_entity_id = p_wip_entity_id and
organization_id = p_organization_id;
select count(*) into l_count from wip_discrete_jobs where
wip_entity_id = p_wip_entity_id and
organization_id = p_organization_id;
select count(*) into l_count from bom_departments where
department_id = p_owning_department
and organization_id = p_organization_id;
update wip_discrete_jobs set owning_department = p_owning_department
where wip_entity_id = p_wip_entity_id and
organization_id = p_organization_id;
* Procedure : Update_EWOD
* Parameters IN : organization Id
* group_id
* user_defined_status_id
* Parameters OUT NOCOPY:
* errbuf error messages
* retcode return status. 0 for success, 1 for warning and 2 for error.
* Purpose : Procedure will update the database with the user_defined_status_id passed.
* This procedure was added for a WIP bug 6718091
***********************************************************************/
PROCEDURE Update_EWOD
(p_group_id IN NUMBER,
p_organization_id IN NUMBER,
p_new_status IN NUMBER,
ERRBUF OUT NOCOPY VARCHAR2 ,
RETCODE OUT NOCOPY VARCHAR2
)
IS
BEGIN
update eam_work_order_details
SET user_defined_status_id = p_new_status
, program_update_date = SYSDATE
, last_update_date = SYSDATE
, last_updated_by = FND_GLOBAL.user_id
, last_update_login = FND_GLOBAL.login_id
WHERE organization_id = p_organization_id
AND WIP_ENTITY_ID IN (SELECT wdct.WIP_ENTITY_ID
FROM WIP_DJ_CLOSE_TEMP wdct, wip_discrete_jobs wdj
WHERE wdct.ORGANIZATION_ID = p_organization_id
AND wdct.GROUP_ID = p_group_id
and wdj.wip_entity_id = wdct.WIP_ENTITY_ID
and wdj.status_type = WIP_CONSTANTS.PEND_CLOSE);
END Update_EWOD;
* Purpose : Procedure will update workflow status to pending close for all wip_entity_ids provided in the group_id.
* This procedure was added for a WIP bug 6718091
***********************************************************************/
PROCEDURE RAISE_WORKFLOW_STATUS_PEND_CLS
(p_group_id IN NUMBER,
p_new_status IN NUMBER,
ERRBUF OUT NOCOPY VARCHAR2 ,
RETCODE OUT NOCOPY VARCHAR2 )
IS
l_return_status VARCHAR2(1);
SELECT wdj.wip_entity_id, we.wip_entity_name,
wdj.status_type, wdj.organization_id, ewod.user_defined_status_id, ewod.workflow_type
FROM wip_discrete_jobs wdj, wip_dj_close_temp wdct, eam_work_order_details ewod, wip_entities we
WHERE wdct.group_id = p_group_id
and wdct.wip_entity_id = wdj.wip_entity_id
and wdct.organization_id = wdj.organization_id
and wdj.wip_entity_id = ewod.wip_entity_id
and wdj.organization_id = ewod.organization_id
and wdj.wip_entity_id = we.wip_entity_id
and wdj.organization_id = we.organization_id
and we.entity_type = WIP_CONSTANTS.EAM;
p_event => 'UPDATE',
p_wip_entity_id =>l_workorders_rec.wip_entity_id,
p_organization_id =>l_workorders_rec.organization_id,
p_last_update_date => SYSDATE,
p_last_updated_by => FND_GLOBAL.user_id,
p_last_update_login =>FND_GLOBAL.login_id
);