[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT COUNT(*)
into l_records_found
FROM DUAL
WHERE EXISTS
(SELECT 1
FROM MTL_MATERIAL_TRANSACTIONS
WHERE ORGANIZATION_ID = arg_org_id
AND INVENTORY_ITEM_ID = arg_item_id
AND TRANSACTION_SOURCE_TYPE_ID + 0 = 5
AND TRANSACTION_SOURCE_ID = arg_wip_entity_id);
SELECT COUNT(*)
into l_records_found
FROM DUAL
WHERE EXISTS
(SELECT 1
FROM MTL_TRANSACTION_ACCOUNTS MTA , MTL_MATERIAL_TRANSACTIONS MMT
WHERE MMT.ORGANIZATION_ID = arg_org_id
AND MMT.TRANSACTION_SOURCE_ID = arg_wip_entity_id
AND MMT.TRANSACTION_ID = MTA.TRANSACTION_ID
AND MMT.TRANSACTION_SOURCE_TYPE_ID +0 = 5 );
SELECT COUNT(*)
into l_records_found
FROM DUAL
WHERE EXISTS
(SELECT 1
FROM MTL_TRANSACTION_LOT_NUMBERS
WHERE TRANSACTION_SOURCE_TYPE_ID = 5
AND TRANSACTION_SOURCE_ID = arg_wip_entity_id );
SELECT COUNT(*)
into l_records_found
FROM DUAL
WHERE EXISTS
(SELECT 1
FROM MTL_UNIT_TRANSACTIONS
WHERE TRANSACTION_SOURCE_TYPE_ID = 5
AND TRANSACTION_SOURCE_ID = arg_wip_entity_id );
SELECT COUNT(*)
into l_records_found
FROM DUAL
WHERE EXISTS
(SELECT 1
FROM MTL_DEMAND MD, WIP_ENTITIES WE
WHERE WE.WIP_ENTITY_ID = arg_wip_entity_id
AND MD.SUPPLY_SOURCE_TYPE = 5
AND MD.SUPPLY_SOURCE_HEADER_ID = WE.WIP_ENTITY_ID
AND MD.INVENTORY_ITEM_ID = WE.PRIMARY_ITEM_ID
AND MD.ORGANIZATION_ID = arg_org_id);
SELECT COUNT(*)
into l_records_found
FROM DUAL
WHERE EXISTS
(SELECT 1
FROM MTL_USER_SUPPLY
WHERE SOURCE_TYPE_ID = 4
AND SOURCE_ID = arg_wip_entity_id
AND ORGANIZATION_ID = arg_org_id);
SELECT COUNT(*)
into l_records_found
FROM DUAL
WHERE EXISTS
(SELECT 1
FROM MTL_USER_DEMAND
WHERE SOURCE_TYPE_ID = 4
AND SOURCE_ID = arg_wip_entity_id
AND ORGANIZATION_ID = arg_org_id);
SELECT COUNT(*)
into l_records_found
FROM DUAL
WHERE EXISTS
(SELECT 1
FROM MTL_SERIAL_NUMBERS
WHERE INVENTORY_ITEM_ID = arg_item_id
AND ORIGINAL_WIP_ENTITY_ID = arg_wip_entity_id);
PROCEDURE DELETE_EXE_TABLES(arg_wip_entity_id in number,
arg_org_id in number) IS
BEGIN
delete from flm_exe_serial_numbers
where wip_entity_id = arg_wip_entity_id;
delete from flm_exe_lot_numbers
where wip_entity_id = arg_wip_entity_id;
delete from flm_exe_req_operations
where wip_entity_id = arg_wip_entity_id;
END DELETE_EXE_TABLES;
Procedure DELETE_TABLES(
arg_wip_entity_id in number,
arg_org_id in number,
arg_auto_replenish in varchar2, /* Added for Enhancement #2829204 */
arg_return_value out NOCOPY number,
errbuf out NOCOPY varchar2
)
IS
l_stmt_num NUMBER := G_ZERO;
SELECT kanban_activity_id
FROM mtl_kanban_card_activity
WHERE source_wip_entity_id = arg_wip_entity_id;
DELETE FROM MTL_MATERIAL_TRANSACTIONS_TEMP
WHERE TRANSACTION_SOURCE_TYPE_ID +0 = 5
AND TRANSACTION_SOURCE_ID = arg_wip_entity_id
AND ORGANIZATION_ID = arg_org_id;
DELETE FROM MTL_TRANSACTIONS_INTERFACE
WHERE TRANSACTION_SOURCE_ID = arg_wip_entity_id
AND ORGANIZATION_ID = arg_org_id;
DELETE FROM MRP_RELIEF_INTERFACE
WHERE DISPOSITION_TYPE = 1
AND DISPOSITION_ID = arg_wip_entity_id;
DELETE FROM WIP_REQ_OPERATION_COST_DETAILS
WHERE WIP_ENTITY_ID = arg_wip_entity_id ;
DELETE FROM WIP_OPERATION_OVERHEADS
WHERE WIP_ENTITY_ID = arg_wip_entity_id ;
DELETE FROM WIP_TRANSACTIONS
WHERE WIP_ENTITY_ID = arg_wip_entity_id;
DELETE FROM WIP_TRANSACTION_ACCOUNTS
WHERE WIP_ENTITY_ID = arg_wip_entity_id;
DELETE FROM WIP_PERIOD_BALANCES
WHERE WIP_ENTITY_ID = arg_wip_entity_id
AND ORGANIZATION_ID = arg_org_id;
* To Delete data from flm_exe_operations table also.
*/
DELETE FROM FLM_EXE_OPERATIONS
WHERE WIP_ENTITY_ID = arg_wip_entity_id;
delete_exe_tables (arg_wip_entity_id, arg_org_id);
DELETE FROM WIP_FLOW_SCHEDULES
WHERE WIP_ENTITY_ID = arg_wip_entity_id;
UPDATE mtl_kanban_card_activity
SET source_wip_entity_id = NULL
WHERE kanban_activity_id = l_card_activity_csr.kanban_activity_id;
DELETE FROM WIP_ENTITIES
WHERE WIP_ENTITY_ID = arg_wip_entity_id;
MRP_UTIL.MRP_LOG('Error at '||l_stmt_num|| ' in Delete_Tables');
END DELETE_TABLES;
SELECT wfs.wip_entity_id ,
wfs.schedule_number,
wfs.status,
wfs.primary_item_id ,
wfs.line_id line_id,
wfs.scheduled_completion_date ,
wfs.date_closed,
wfs.organization_id,
wfs.auto_replenish /* Added for Enhancement #2829204 */
FROM wip_flow_schedules wfs
WHERE wfs.organization_id = arg_org_id
AND wfs.scheduled_completion_date <= p_cutoff_date
AND (arg_line is null or wfs.line_id = to_number(arg_line) )
AND wfs.primary_item_id = nvl(arg_assembly,wfs.primary_item_id);
l_records_deleted NUMBER := G_ZERO;
select line_code into l_line_code
from wip_lines
where line_id = arg_line and organization_id = arg_org_id;
select max(period_close_date) --fix bug#3170105
into l_account_close_date
from org_acct_periods
where organization_id = arg_org_id
and schedule_close_date
<= l_cutoff_date
and open_flag = 'N'
and period_close_date IS NOT NULL;
DELETE FROM WIP_TRANSACTIONS
WHERE WIP_ENTITY_ID = l_wip_entity_id;
DELETE FROM WIP_TRANSACTION_ACCOUNTS
WHERE WIP_ENTITY_ID = l_wip_entity_id;
l_records_deleted := l_records_deleted + 1;
if (l_records_deleted >= G_BATCH ) then
COMMIT;
l_records_deleted := G_ZERO;
* To Delete data from flm_exe_operations table if purge_option = 3
*/
elsif (arg_purge_option = 3) and (l_flag) then
DELETE FROM FLM_EXE_OPERATIONS
WHERE WIP_ENTITY_ID = l_wip_entity_id;
delete_exe_tables (l_wip_entity_id, l_organization_id);
l_records_deleted := l_records_deleted + 1;
if (l_records_deleted >= G_BATCH ) then
COMMIT;
l_records_deleted := G_ZERO;
Delete_Tables(l_wip_entity_id,
l_organization_id,
l_auto_replenish, /* Added for Enhancement #2829204 */
l_return_value,
errbuf
);
l_records_deleted := l_records_deleted + 1;
if (l_records_deleted >= G_BATCH ) then
COMMIT;
l_records_deleted := G_ZERO;
if (l_records_deleted > 0) then
COMMIT;