[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_final_status NUMBER := 0; -- this status will be updated in WDJ
SELECT wip_entity_name
INTO l_work_order_name
FROM wip_entities
WHERE wip_entity_id = p_wip_entity_id;
SELECT nvl(wdj.status_type,1),
nvl(wdj.firm_planned_flag,2),
wdj.organization_id,
nvl(wdj.asset_group_id,0),
nvl(wdj.asset_number,''),
nvl(wdj.rebuild_item_id,0),
nvl(wdj.rebuild_serial_number,''),
wdj.primary_item_id,
wdj.class_code,
we.wip_entity_name,
wdj.date_completed,
wdj.date_closed,
wdj.maintenance_object_source,
wdj.po_creation_time,
wdj.maintenance_object_id
INTO l_current_status,
l_firm_flag,
l_organization_id,
l_asset_group_id,
l_asset_number,
l_rebuild_item_id,
l_rebuild_serial_number,
l_primary_item_id,
l_class_code,
l_wip_entity_name,
l_date_completed,
l_date_closed,
l_maintenance_obj_src,
l_po_creation_time,
l_maint_obj_id
FROM wip_discrete_jobs wdj, wip_entities we
where wdj.wip_entity_id = l_wip_entity_id
and we.wip_entity_id = wdj.wip_entity_id
and we.organization_id = wdj.organization_id;
EAM_ASSET_LOG_PVT.INSERT_ROW
(
p_api_version => 1.0,
p_event_date => sysdate,
p_event_type => 'EAM_SYSTEM_EVENTS',
p_event_id => 7,
p_organization_id => p_organization_id,
p_instance_id => l_maint_obj_id,
p_comments => null,
p_reference => l_work_order_name,
p_ref_id => p_wip_entity_id,
p_operable_flag => null,
p_reason_code => null,
x_return_status => l_asset_ops_return_status,
x_msg_count => l_asset_ops_msg_count,
x_msg_data => l_asset_ops_msg_data
);
-- Delete data from EAM_WORK_ORDER_ROUTE table
DELETE FROM EAM_WORK_ORDER_ROUTE
WHERE wip_entity_id = p_wip_entity_id;
select scheduled_start_date into
l_wo_sched_start_date from wip_discrete_jobs
where wip_entity_id = l_wip_entity_id
and organization_id = l_organization_id;
select nvl(min(period_start_date),l_wo_sched_start_date)
into l_min_open_period_date from org_acct_periods
where organization_id=l_organization_id
and open_flag = 'Y' and period_close_date is null;
UPDATE wip_requirement_operations
SET operation_seq_num = (SELECT MIN(operation_seq_num)
FROM wip_operations
WHERE wip_entity_id = l_wip_entity_id
AND organization_id = l_organization_id)
WHERE wip_entity_id = l_wip_entity_id
AND organization_id = l_organization_id
AND operation_seq_num = 1;
UPDATE wip_eam_direct_items
SET operation_seq_num = (SELECT MIN(operation_seq_num)
FROM wip_operations
WHERE wip_entity_id = l_wip_entity_id
AND organization_id = l_organization_id)
WHERE wip_entity_id = l_wip_entity_id
AND organization_id = l_organization_id
AND operation_seq_num = 1;
/* Insert the route snapshot, only if it does not already exist */
BEGIN
select count(*)
into l_route
from EAM_WORK_ORDER_ROUTE
where wip_entity_id = l_wip_entity_id;
INSERT INTO EAM_WORK_ORDER_ROUTE
(
wip_entity_id ,
route_asset_seq_id ,
instance_id ,
last_update_date ,
last_updated_by ,
creation_date ,
created_by ,
last_update_login
)
SELECT
wdj.wip_entity_id,
EAM_WORK_ORDER_ROUTE_S.nextval,
mena.maintenance_object_id,
sysdate,
fnd_global.login_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id
FROM
WIP_DISCRETE_JOBS wdj,
MTL_EAM_NETWORK_ASSETS mena,
CSI_ITEM_INSTANCES CII,
MTL_PARAMETERS mp
WHERE
mena.network_object_id = wdj.maintenance_object_id
AND wdj.organization_id = p_organization_id
AND wdj.wip_entity_id = p_wip_entity_id
AND mena.maintenance_object_id = cii.instance_id
AND cii.last_vld_organization_id = mp.organization_id
AND mp.maint_organization_id = p_organization_id
AND nvl(mena.start_date_active, sysdate) <= nvl(wdj.date_released, sysdate)
AND nvl(mena.end_date_active, sysdate) >= nvl(wdj.date_released, sysdate);
EAM_ASSET_LOG_PVT.INSERT_ROW
(
p_api_version => 1.0,
p_event_date => sysdate,
p_event_type => 'EAM_SYSTEM_EVENTS',
p_event_id => 6,
p_organization_id => p_organization_id,
p_instance_id => l_maint_obj_id,
p_comments => null,
p_reference => l_work_order_name,
p_ref_id => p_wip_entity_id,
p_operable_flag => null,
p_reason_code => null,
x_return_status => l_asset_ops_return_status,
x_msg_count => l_asset_ops_msg_count,
x_msg_data => l_asset_ops_msg_data
);
SELECT 1 INTO l_di_count
FROM DUAL
WHERE EXISTS
(
SELECT 1
FROM wip_eam_direct_items wedi
WHERE wedi.wip_entity_id = l_wip_entity_id
AND wedi.organization_id = l_organization_id
)
OR EXISTS
(
SELECT 1
FROM wip_requirement_operations wro, mtl_system_items_b msi
WHERE wro.wip_entity_id = l_wip_entity_id
AND wro.organization_id = l_organization_id
AND wro.inventory_item_id = msi.inventory_item_id
AND wro.organization_id = msi.organization_id
AND nvl(msi.stock_enabled_flag, 'N') = 'N'
);
SELECT 1
INTO l_relations_count
FROM eam_wo_relationships
WHERE (parent_object_id=l_wip_entity_id
OR child_object_id=l_wip_entity_id)
AND parent_relationship_type =2 AND rownum<=1;
SELECT 1
INTO l_po_exists
FROM (
SELECT 1
FROM PO_RELEASES_ALL PR,
PO_HEADERS_ALL PH,
PO_DISTRIBUTIONS_ALL PD,
PO_LINE_LOCATIONS_ALL PLL
WHERE pd.po_line_id IS NOT NULL
AND pd.line_location_id IS NOT NULL
AND PD.WIP_ENTITY_ID = l_wip_entity_id
AND PD.DESTINATION_ORGANIZATION_ID = l_organization_id
AND PH.PO_HEADER_ID = PD.PO_HEADER_ID
AND PLL.LINE_LOCATION_ID = PD.LINE_LOCATION_ID
AND PR.PO_RELEASE_ID (+) = PD.PO_RELEASE_ID
AND (pll.cancel_flag IS NULL OR
pll.cancel_flag = 'N')
AND (
(PLL.QUANTITY_RECEIVED < (PLL.QUANTITY-PLL.QUANTITY_CANCELLED))
OR
(PLL.AMOUNT_RECEIVED < (PLL.AMOUNT-PLL.AMOUNT_CANCELLED))
) /*ADDED AMOUNT condition for Bug7497877*/
AND nvl(pll.closed_code,'OPEN') not in ('FINALLY CLOSED', 'CLOSED') --Added CLOSED status:Bug#6142700
UNION ALL
SELECT 1
FROM PO_REQUISITION_LINES_ALL PRL
WHERE PRL.WIP_ENTITY_ID = l_wip_entity_id
AND PRL.DESTINATION_ORGANIZATION_ID = l_organization_id
AND nvl(PRL.cancel_flag, 'N') = 'N'
AND PRL.LINE_LOCATION_ID is NULL
UNION ALL
SELECT 1
FROM PO_REQUISITIONS_INTERFACE_ALL PRI
WHERE PRI.WIP_ENTITY_ID = l_wip_entity_id
AND PRI.DESTINATION_ORGANIZATION_ID = l_organization_id
) ;
IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Selecting group_id'); END IF;
SELECT wip_dj_close_temp_s.nextval
INTO l_gid
FROM dual;
IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Insert into WIP_DJ_CLOSE_TEMP'); END IF;
INSERT INTO WIP_DJ_CLOSE_TEMP
(WIP_ENTITY_ID,
ORGANIZATION_ID,
WIP_ENTITY_NAME,
PRIMARY_ITEM_ID,
STATUS_TYPE,
actual_close_date,
GROUP_ID)
VALUES
(l_wip_entity_id,
l_organization_id,
l_wip_entity_name,
l_primary_item_id,
decode(l_current_status, 16, 1, l_current_status),
NVL(p_actual_close_date,SYSDATE),
l_gid);
p_select_jobs => 2,
p_report_type => NVL(p_report_type,'4'),
x_request_id => l_request_id
);
UPDATE EAM_WORK_ORDER_DETAILS
SET user_defined_status_id = l_closed_status,
last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE wip_entity_id = l_wip_entity_id;
UPDATE WIP_DISCRETE_JOBS
SET STATUS_TYPE = l_final_status
WHERE ORGANIZATION_ID = l_organization_id
AND WIP_ENTITY_ID = l_wip_entity_id;
UPDATE WIP_ENTITIES
SET ENTITY_TYPE = 6
WHERE ORGANIZATION_ID = l_organization_id
AND WIP_ENTITY_ID = l_wip_entity_id;
UPDATE wip_discrete_jobs
SET date_closed = NULL
WHERE organization_id = l_organization_id
AND WIP_ENTITY_ID = l_wip_entity_id;
UPDATE WIP_ENTITIES
SET ENTITY_TYPE = 7
WHERE ORGANIZATION_ID = l_organization_id
AND WIP_ENTITY_ID = l_wip_entity_id;
SELECT WOR.OPERATION_SEQ_NUM,
WOR.RESOURCE_SEQ_NUM
FROM WIP_OPERATION_RESOURCES WOR,
WIP_OPERATIONS WO
WHERE WO.WIP_ENTITY_ID = p_wip_entity_id
AND WO.ORGANIZATION_ID = p_organization_id
AND WOR.WIP_ENTITY_ID = WO.WIP_ENTITY_ID
AND WOR.ORGANIZATION_ID = WO.ORGANIZATION_ID
AND WOR.OPERATION_SEQ_NUM = WO.OPERATION_SEQ_NUM
AND WOR.AUTOCHARGE_TYPE = WIP_CONSTANTS.PO_RECEIPT
AND WO.COUNT_POINT_TYPE <> WIP_CONSTANTS.NO_DIRECT;
select to_number(ho.ORG_INFORMATION3)
into l_ou_id
from hr_organization_information ho
where ho.organization_id = p_organization_id
and ho.ORG_INFORMATION_CONTEXT = 'Accounting Information';