[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
insert into Wip_temp_reports(
key1, /* Group ID */
program_source,
last_updated_by,
organization_id,
wip_entity_id,
inventory_item_id,
key2, /* line ID */
key3, /* Repetitive schedule ID */
description, /* Table Name */
key6, /* Info Type */
attribute1 /* Info */,
date1, /* Start Date */
date2, /* Completion Date */
date3, /* Close Date */
attribute2, /*Job Name*/
attribute3 /*Line Code*/
) values (
p_rec.group_id,
'WICTPG', /* program_source */
-1, /* Last Updated By */
p_rec.org_id,
p_rec.wip_entity_id,
p_rec.primary_item_id,
p_rec.line_id,
p_rec.schedule_id,
p_rec.table_name,
p_rec.info_type,
p_rec.info,
p_rec.start_date,
p_rec.complete_date,
p_rec.close_date,
p_rec.entity_name,
p_rec.line_code
);
procedure delete_purge_temp_table(
p_group_id in number ) is
begin
delete from Wip_Purge_Temp where group_id = p_group_id ;
end delete_purge_temp_table ;
procedure delete_from_table(
p_option in number,
p_purge_rec in purge_report_type,
p_delete_statement in varchar2) is
x_cursor_id number;
dbms_sql.parse(x_cursor_id, p_delete_statement, dbms_sql.v7);
end delete_from_table;
** delete_from_table except the dynamic sql execution.
*/
procedure before_append_report(
p_option in number,
p_purge_rec in purge_report_type,
num_rows in number) is
x_purge_rec purge_report_type;
procedure delete_job_details(
p_option in number,
p_group_id in number,
p_purge_request in get_purge_requests%rowtype) is
x_num_rows number := 0;
select count(*) into x_num_rows from WIP_OPERATIONS where
WIP_ENTITY_ID= x_purge_rec.wip_entity_id;
DELETE FROM WIP_OPERATIONS
WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id;
select count(*) into x_num_rows from WIP_REQUIREMENT_OPERATIONS where
WIP_ENTITY_ID= x_purge_rec.wip_entity_id;
DELETE FROM WIP_REQUIREMENT_OPERATIONS
WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id;
select count(*) into x_num_rows from WIP_OPERATION_RESOURCES where
WIP_ENTITY_ID= x_purge_rec.wip_entity_id;
DELETE FROM WIP_OPERATION_RESOURCES
WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id;
select count(*) into x_num_rows from WIP_OP_RESOURCE_INSTANCES where
WIP_ENTITY_ID= x_purge_rec.wip_entity_id
AND ORGANIZATION_ID = x_purge_rec.org_id;
DELETE FROM WIP_OP_RESOURCE_INSTANCES
WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id
AND ORGANIZATION_ID = x_purge_rec.org_id;
select count(*) into x_num_rows from WIP_SHOP_FLOOR_STATUSES where
WIP_ENTITY_ID= x_purge_rec.wip_entity_id;
DELETE FROM WIP_SHOP_FLOOR_STATUSES
WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id;
select count(*) into x_num_rows from WIP_OPERATION_OVERHEADS
where WIP_ENTITY_ID=to_char(x_purge_rec.wip_entity_id);
DELETE FROM WIP_OPERATION_OVERHEADS
WHERE WIP_ENTITY_ID = to_char(x_purge_rec.wip_entity_id);
select count(*) into x_num_rows from WIP_REQ_OPERATION_COST_DETAILS
where WIP_ENTITY_ID=to_char(x_purge_rec.wip_entity_id);
DELETE FROM WIP_REQ_OPERATION_COST_DETAILS
WHERE WIP_ENTITY_ID = to_char(x_purge_rec.wip_entity_id);
select count(*)
into x_num_rows
from FND_ATTACHED_DOCUMENTS
WHERE PK1_VALUE = to_char(x_purge_rec.wip_entity_id)
AND (( PK2_VALUE = to_char(x_purge_rec.org_id)
AND ENTITY_NAME = 'WIP_DISCRETE_JOBS'
)
OR ( PK3_VALUE = to_char(x_purge_rec.org_id)
AND ENTITY_NAME = 'WIP_DISCRETE_OPERATIONS'
)
);
/* Bug 6056455: (FP of 5224338) Added following if condition before calling FND API to delete attachments*/
if ( l_op_count > 0) then
FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments(
X_entity_name => 'WIP_DISCRETE_OPERATIONS',
X_pk1_value => to_char(x_purge_rec.wip_entity_id),
X_pk3_value => to_char(x_purge_rec.org_id),
X_delete_document_flag => 'Y' );
FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments(
X_entity_name => 'WIP_DISCRETE_JOBS',
X_pk1_value => to_char(x_purge_rec.wip_entity_id),
X_pk2_value => to_char(x_purge_rec.org_id),
X_delete_document_flag => 'Y' );
end delete_job_details;
procedure delete_sched_details(
p_option in number,
p_group_id in number,
p_purge_request in get_purge_requests%rowtype) is
x_purge_rec purge_report_type;
select count(*) into x_num_rows from WIP_OPERATIONS
WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id
AND REPETITIVE_SCHEDULE_ID = x_purge_rec.schedule_id
AND ORGANIZATION_ID = x_purge_rec.org_id;
DELETE FROM WIP_OPERATIONS
WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id
AND REPETITIVE_SCHEDULE_ID = x_purge_rec.schedule_id
AND ORGANIZATION_ID = x_purge_rec.org_id;
select count(*) into x_num_rows from WIP_REQUIREMENT_OPERATIONS
WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id
AND REPETITIVE_SCHEDULE_ID = x_purge_rec.schedule_id;
DELETE FROM WIP_REQUIREMENT_OPERATIONS
WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id
AND REPETITIVE_SCHEDULE_ID = x_purge_rec.schedule_id;
select count(*) into x_num_rows from WIP_OPERATION_RESOURCES
WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id
AND REPETITIVE_SCHEDULE_ID = x_purge_rec.schedule_id;
DELETE FROM WIP_OPERATION_RESOURCES
WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id
AND REPETITIVE_SCHEDULE_ID = x_purge_rec.schedule_id;
select count(*) into x_num_rows from WIP_OP_RESOURCE_INSTANCES
WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id
AND ORGANIZATION_ID = x_purge_rec.org_id;
DELETE FROM WIP_OP_RESOURCE_INSTANCES
WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id
AND ORGANIZATION_ID = x_purge_rec.org_id;
select count(*)
into x_num_rows
from FND_ATTACHED_DOCUMENTS
WHERE PK1_VALUE = to_char(x_purge_rec.wip_entity_id)
AND PK3_VALUE = to_char(x_purge_rec.org_id)
AND ( ( PK2_VALUE = to_char(x_purge_rec.schedule_id)
AND ENTITY_NAME = 'WIP_REPETITIVE_SCHEDULES'
)
OR ( PK4_VALUE = to_char(x_purge_rec.schedule_id)
AND ENTITY_NAME = 'WIP_REPETITIVE_OPERATIONS'
)
) ;
FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments(
X_entity_name => 'WIP_REPETITIVE_OPERATIONS',
X_pk1_value => to_char(x_purge_rec.wip_entity_id),
X_pk3_value => to_char(x_purge_rec.org_id),
X_pk4_value => to_char(x_purge_rec.schedule_id),
X_delete_document_flag => 'Y' );
FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments(
X_entity_name => 'WIP_REPETITIVE_SCHEDULES',
X_pk1_value => to_char(x_purge_rec.wip_entity_id),
X_pk2_value => to_char(x_purge_rec.schedule_id),
X_pk3_value => to_char(x_purge_rec.org_id),
X_delete_document_flag => 'Y' );
end delete_sched_details;
procedure delete_job_move_trx(
p_option in number,
p_group_id in number,
p_purge_request in get_purge_requests%rowtype) is
x_purge_rec purge_report_type;
select count(*) into x_num_rows
from wip_serial_move_transactions wsmt
where wsmt.transaction_id in (select wmt.transaction_id
from wip_move_transactions wmt
where wmt.wip_entity_id = x_purge_rec.wip_entity_id);
delete from wip_serial_move_transactions
where transaction_id in (select wmt.transaction_id
from wip_move_transactions wmt
where wmt.wip_entity_id = x_purge_rec.wip_entity_id);
select count(*) into x_num_rows from WIP_MOVE_TRANSACTIONS
WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id;
DELETE FROM WIP_MOVE_TRANSACTIONS
WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id;
end delete_job_move_trx;
procedure delete_sched_move_trx(
p_option in number,
p_group_id in number,
p_purge_request in get_purge_requests%rowtype,
p_cutoff_date in date,
p_sched_move_txn_flag in out nocopy boolean) is
x_purge_rec purge_report_type;
SELECT COUNT(*) into l_num_rows
FROM DUAL WHERE EXISTS (
SELECT out_wmta.transaction_id
FROM wip_move_txn_allocations out_wmta
WHERE out_wmta.organization_id = p_purge_request.organization_id
AND out_wmta.repetitive_schedule_id = p_purge_request.repetitive_schedule_id
AND transaction_id IN (
SELECT transaction_id
FROM wip_repetitive_schedules wrs,
wip_move_txn_allocations wmta
WHERE wmta.transaction_id = out_wmta.transaction_id
AND wmta.repetitive_schedule_id <> out_wmta.repetitive_schedule_id
AND nvl(wrs.date_closed, p_cutoff_date+1) > p_cutoff_date
AND wrs.repetitive_schedule_id = wmta.repetitive_schedule_id
AND wmta.organization_id = p_purge_request.organization_id
AND wrs.organization_id = p_purge_request.organization_id));
DELETE FROM WIP_MOVE_TRANSACTIONS
WHERE TRANSACTION_ID IN (
SELECT out_wmta.transaction_id
FROM wip_move_txn_allocations out_wmta
WHERE out_wmta.organization_id = p_purge_request.organization_id
AND out_wmta.repetitive_schedule_id = p_purge_request.repetitive_schedule_id
AND out_wmta.transaction_id IN (
SELECT transaction_id
FROM wip_repetitive_schedules wrs,
wip_move_txn_allocations wmta
WHERE wmta.transaction_id = out_wmta.transaction_id
AND wmta.repetitive_schedule_id <> out_wmta.repetitive_schedule_id
AND nvl(wrs.date_closed, p_cutoff_date+1) <= p_cutoff_date
AND wrs.repetitive_schedule_id = wmta.repetitive_schedule_id
AND wmta.organization_id = p_purge_request.organization_id
AND wrs.organization_id = p_purge_request.organization_id));
DELETE FROM WIP_MOVE_TXN_ALLOCATIONS
WHERE TRANSACTION_ID IN (
SELECT out_wmta.transaction_id
FROM wip_move_txn_allocations out_wmta
WHERE out_wmta.organization_id = p_purge_request.organization_id
AND out_wmta.repetitive_schedule_id = p_purge_request.repetitive_schedule_id
AND out_wmta.transaction_id IN (
SELECT transaction_id
FROM wip_repetitive_schedules wrs,
wip_move_txn_allocations wmta
WHERE wmta.transaction_id = out_wmta.transaction_id
AND wmta.repetitive_schedule_id <> out_wmta.repetitive_schedule_id
AND nvl(wrs.date_closed, p_cutoff_date+1) <= p_cutoff_date
AND wrs.repetitive_schedule_id = wmta.repetitive_schedule_id
AND wmta.organization_id = p_purge_request.organization_id
AND wrs.organization_id = p_purge_request.organization_id));
end delete_sched_move_trx;
procedure delete_job_cost_trx(
p_option in number,
p_group_id in number,
p_purge_request in get_purge_requests%rowtype,
p_cut_off_date in date) is /*bug 4082908*/
x_purge_rec purge_report_type;
SELECT count(*) into x_num_rows_non_lot
FROM WIP_TRANSACTION_ACCOUNTS WTA
WHERE WTA.TRANSACTION_ID IN
( SELECT WT.TRANSACTION_ID
FROM WIP_TRANSACTIONS WT
WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id
AND WT.TRANSACTION_TYPE NOT IN (11,12));
SELECT count(*) into x_num_rows_lot
FROM WIP_TRANSACTION_ACCOUNTS WTA
WHERE WTA.TRANSACTION_ID IN
( SELECT WT.TRANSACTION_ID
FROM WIP_TRANSACTIONS WT
WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id
AND WT.TRANSACTION_TYPE IN (11,12)
AND NOT EXISTS (SELECT 1 FROM
WIP_TRANSACTION_ACCOUNTS WTA1, WIP_DISCRETE_JOBS WDJ
WHERE WTA1.WIP_ENTITY_ID=WDJ.WIP_ENTITY_ID
AND WTA1.TRANSACTION_ID=WT.TRANSACTION_ID
AND NVL(WDJ.DATE_CLOSED,SYSDATE) >= p_cut_off_date)
UNION
SELECT WT.TRANSACTION_ID
FROM WIP_TRANSACTION_ACCOUNTS WT
WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id
AND NOT EXISTS
( SELECT 1 FROM
WIP_TRANSACTION_ACCOUNTS WTA1, WIP_DISCRETE_JOBS WDJ
WHERE WTA1.WIP_ENTITY_ID=WDJ.WIP_ENTITY_ID
AND WTA1.TRANSACTION_ID=WT.TRANSACTION_ID
AND NVL(WDJ.DATE_CLOSED,SYSDATE) > p_cut_off_date)
);
DELETE FROM WIP_TRANSACTION_ACCOUNTS WTA
WHERE WTA.TRANSACTION_ID IN
( SELECT WT.TRANSACTION_ID
FROM WIP_TRANSACTIONS WT
WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id
AND WT.TRANSACTION_TYPE NOT IN (11,12));
DELETE FROM WIP_TRANSACTION_ACCOUNTS WTA
WHERE WTA.TRANSACTION_ID IN
( SELECT WT.TRANSACTION_ID
FROM WIP_TRANSACTIONS WT
WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id
AND WT.TRANSACTION_TYPE IN (11,12)
AND NOT EXISTS (SELECT 1 FROM
WIP_TRANSACTION_ACCOUNTS WTA1, WIP_DISCRETE_JOBS WDJ
WHERE WTA1.WIP_ENTITY_ID=WDJ.WIP_ENTITY_ID
AND WTA1.TRANSACTION_ID=WT.TRANSACTION_ID
AND NVL(WDJ.DATE_CLOSED,SYSDATE) >= p_cut_off_date)
UNION
SELECT WT.TRANSACTION_ID
FROM WIP_TRANSACTION_ACCOUNTS WT
WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id
AND NOT EXISTS
( SELECT 1 FROM
WIP_TRANSACTION_ACCOUNTS WTA1, WIP_DISCRETE_JOBS WDJ
WHERE WTA1.WIP_ENTITY_ID=WDJ.WIP_ENTITY_ID
AND WTA1.TRANSACTION_ID=WT.TRANSACTION_ID
AND NVL(WDJ.DATE_CLOSED,SYSDATE) > p_cut_off_date)
);
select count(*) into x_num_rows from WIP_TRANSACTIONS
WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id;
DELETE FROM WIP_TRANSACTIONS
WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id;
end delete_job_cost_trx;
procedure delete_sched_cost_trx(
p_option in number,
p_group_id in number,
p_purge_request in get_purge_requests%rowtype,
p_cutoff_date in date,
p_sched_txn_flag in out nocopy boolean ) is
x_purge_rec purge_report_type;
SELECT COUNT(*) into l_num_rows
FROM DUAL WHERE EXISTS (
SELECT out_wta.transaction_id
FROM wip_txn_allocations out_wta
WHERE out_wta.organization_id = p_purge_request.organization_id
AND out_wta.repetitive_schedule_id = p_purge_request.repetitive_schedule_id
AND out_wta.transaction_id IN (
SELECT transaction_id
FROM wip_repetitive_schedules wrs,
wip_txn_allocations wta
WHERE wta.transaction_id = out_wta.transaction_id
AND wta.repetitive_schedule_id <> out_wta.repetitive_schedule_id
AND nvl(wrs.date_closed, p_cutoff_date+1) > p_cutoff_date
AND wrs.repetitive_schedule_id = wta.repetitive_schedule_id
AND wta.organization_id = p_purge_request.organization_id
AND wrs.organization_id = p_purge_request.organization_id));
DELETE FROM WIP_TRANSACTIONS
WHERE TRANSACTION_ID IN (
SELECT out_wta.transaction_id
FROM wip_txn_allocations out_wta
WHERE out_wta.organization_id = p_purge_request.organization_id
AND out_wta.repetitive_schedule_id = p_purge_request.repetitive_schedule_id
AND NOT EXISTS (
SELECT transaction_id
FROM wip_repetitive_schedules wrs,
wip_txn_allocations wta
WHERE wta.transaction_id = out_wta.transaction_id
AND wta.repetitive_schedule_id <> out_wta.repetitive_schedule_id
AND nvl(wrs.date_closed, p_cutoff_date+1) <= p_cutoff_date
AND wrs.repetitive_schedule_id = wta.repetitive_schedule_id
AND wta.organization_id = p_purge_request.organization_id
AND wrs.organization_id = p_purge_request.organization_id));
DELETE FROM WIP_TRANSACTION_ACCOUNTS
WHERE TRANSACTION_ID IN (
SELECT out_wta.transaction_id
FROM wip_txn_allocations out_wta
WHERE out_wta.organization_id = p_purge_request.organization_id
AND out_wta.repetitive_schedule_id = p_purge_request.repetitive_schedule_id
AND NOT EXISTS (
SELECT transaction_id
FROM wip_repetitive_schedules wrs,
wip_txn_allocations wta
WHERE wta.transaction_id = out_wta.transaction_id
AND wta.repetitive_schedule_id <> out_wta.repetitive_schedule_id
AND nvl(wrs.date_closed, p_cutoff_date+1) <= p_cutoff_date
AND wrs.repetitive_schedule_id = wta.repetitive_schedule_id
AND wta.organization_id = p_purge_request.organization_id
AND wrs.organization_id = p_purge_request.organization_id));
DELETE FROM WIP_TXN_ALLOCATIONS
WHERE TRANSACTION_ID IN (
SELECT out_wta.transaction_id
FROM wip_txn_allocations out_wta
WHERE out_wta.organization_id = p_purge_request.organization_id
AND out_wta.repetitive_schedule_id = p_purge_request.repetitive_schedule_id
AND NOT EXISTS (
SELECT transaction_id
FROM wip_repetitive_schedules wrs,
wip_txn_allocations wta
WHERE wta.transaction_id = out_wta.transaction_id
AND wta.repetitive_schedule_id <> out_wta.repetitive_schedule_id
AND nvl(wrs.date_closed, p_cutoff_date+1) <= p_cutoff_date
AND wrs.repetitive_schedule_id = wta.repetitive_schedule_id
AND wta.organization_id = p_purge_request.organization_id
AND wrs.organization_id = p_purge_request.organization_id));
end delete_sched_cost_trx;
procedure delete_job_header(
p_option in number,
p_group_id in number,
p_purge_request in get_purge_requests%rowtype) is
x_purge_rec purge_report_type;
select count(*)
into x_records_returned
from dual
where (0,0,0,0,0,0,0,0,0,0) <>
(select sum(NVL(TL_RESOURCE_IN, 0) - NVL(TL_RESOURCE_OUT, 0) - NVL(TL_RESOURCE_VAR,0)),
sum(NVL(TL_OVERHEAD_IN, 0) - NVL(TL_OVERHEAD_OUT, 0) - NVL(TL_OVERHEAD_VAR,0)),
sum(NVL(TL_OUTSIDE_PROCESSING_IN,0) - NVL(TL_OUTSIDE_PROCESSING_OUT, 0) - NVL(TL_OUTSIDE_PROCESSING_VAR,0)),
sum(0 - NVL(TL_MATERIAL_OUT, 0) - NVL(TL_MATERIAL_VAR,0)),
sum(0 - NVL(TL_MATERIAL_OVERHEAD_OUT, 0) - NVL(TL_MATERIAL_OVERHEAD_VAR,0)),
sum(NVL(PL_MATERIAL_IN, 0) - NVL(PL_MATERIAL_OUT, 0) - NVL(PL_MATERIAL_VAR,0)),
sum(NVL(PL_MATERIAL_OVERHEAD_IN, 0) - NVL(PL_MATERIAL_OVERHEAD_OUT, 0) - NVL(PL_MATERIAL_OVERHEAD_VAR,0)),
sum(NVL(PL_RESOURCE_IN, 0) - NVL(PL_RESOURCE_OUT, 0) - NVL(PL_RESOURCE_VAR,0)),
sum(NVL(PL_OVERHEAD_IN, 0) - NVL(PL_OVERHEAD_OUT, 0) - NVL(PL_OVERHEAD_VAR,0)),
sum(NVL(PL_OUTSIDE_PROCESSING_IN, 0) - NVL(PL_OUTSIDE_PROCESSING_OUT, 0) - NVL(PL_OUTSIDE_PROCESSING_VAR,0))
from WIP_PERIOD_BALANCES
where WIP_ENTITY_ID = x_purge_rec.wip_entity_id);
SELECT COUNT(*)
into x_records_returned
FROM DUAL
WHERE EXISTS
(SELECT 1
FROM CST_STD_COST_ADJ_VALUES
WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id);
SELECT COUNT(*)
into x_records_returned
FROM DUAL
WHERE EXISTS
(SELECT 1
FROM PO_DISTRIBUTIONS_ALL
WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id
/* Fixed bug 3115844 */
AND po_line_id IS NOT NULL
AND line_location_id IS NOT NULL);
SELECT COUNT(*)
into x_records_returned
FROM DUAL
WHERE EXISTS
(SELECT 1
FROM PO_DISTRIBUTIONS_ARCHIVE_ALL
WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id);
SELECT COUNT(*)
into x_records_returned
FROM DUAL
WHERE EXISTS
(SELECT 1
FROM PO_REQUISITION_LINES_ALL
WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id );
SELECT COUNT(*)
into x_records_returned
FROM DUAL
WHERE EXISTS
(SELECT 1
FROM RCV_TRANSACTIONS
WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id );
SELECT COUNT(*)
into x_records_returned
FROM DUAL
WHERE EXISTS
(SELECT 1
FROM MTL_DEMAND
WHERE SUPPLY_SOURCE_TYPE = 5
AND INVENTORY_ITEM_ID IS NULL
AND SUPPLY_SOURCE_HEADER_ID = x_purge_rec.wip_entity_id
AND ORGANIZATION_ID = x_purge_rec.org_id);
SELECT COUNT(*)
into x_records_returned
FROM DUAL
WHERE EXISTS
(SELECT 1
FROM MTL_DEMAND
WHERE SUPPLY_SOURCE_TYPE = 5
AND INVENTORY_ITEM_ID = x_purge_rec.primary_item_id
AND SUPPLY_SOURCE_HEADER_ID = x_purge_rec.wip_entity_id
AND ORGANIZATION_ID = x_purge_rec.org_id);
SELECT COUNT(*)
into x_records_returned
FROM DUAL
WHERE EXISTS
(SELECT 1
FROM MTL_USER_SUPPLY
WHERE SOURCE_TYPE_ID = 4
AND SOURCE_ID = x_purge_rec.wip_entity_id
AND INVENTORY_ITEM_ID IS NULL );
SELECT COUNT(*)
into x_records_returned
FROM DUAL
WHERE EXISTS
(SELECT 1
FROM MTL_USER_SUPPLY
WHERE SOURCE_TYPE_ID = 4
AND SOURCE_ID = x_purge_rec.wip_entity_id
AND INVENTORY_ITEM_ID = x_purge_rec.primary_item_id );
SELECT COUNT(*)
into x_records_returned
FROM DUAL
WHERE EXISTS
(SELECT 1
FROM MTL_USER_DEMAND
WHERE SOURCE_TYPE_ID = 4
AND SOURCE_ID = x_purge_rec.wip_entity_id
AND INVENTORY_ITEM_ID IS NULL );
SELECT COUNT(*)
into x_records_returned
FROM DUAL
WHERE EXISTS
(SELECT 1
FROM MTL_USER_DEMAND
WHERE SOURCE_TYPE_ID = 4
AND SOURCE_ID = x_purge_rec.wip_entity_id
AND INVENTORY_ITEM_ID = x_purge_rec.primary_item_id );
SELECT COUNT(*)
into x_records_returned
FROM DUAL
WHERE EXISTS
(SELECT 1
FROM MTL_SERIAL_NUMBERS
WHERE ORIGINAL_WIP_ENTITY_ID = x_purge_rec.wip_entity_id
AND INVENTORY_ITEM_ID IS NULL );
SELECT COUNT(*)
into x_records_returned
FROM DUAL
WHERE EXISTS
(SELECT 1
FROM MTL_SERIAL_NUMBERS
WHERE ORIGINAL_WIP_ENTITY_ID = x_purge_rec.wip_entity_id
AND INVENTORY_ITEM_ID = x_purge_rec.primary_item_id );
/* Bug 5935560. Need to update original_wip_entity_id in MSN to -999
and purge if there is a associated serial number. */
/* x_records_found := x_records_found OR (x_records_returned > 0); */
SELECT COUNT(*)
into x_records_returned
FROM DUAL
WHERE EXISTS
(SELECT 1
FROM MTL_MATERIAL_TRANSACTIONS
WHERE TRANSACTION_SOURCE_TYPE_ID + 0 = 5
AND TRANSACTION_SOURCE_ID = x_purge_rec.wip_entity_id
AND ORGANIZATION_ID = x_purge_rec.org_id );
SELECT COUNT(*)
into x_records_returned
FROM DUAL
WHERE EXISTS
(SELECT 1
FROM MTL_TRANSACTION_ACCOUNTS MTA , MTL_MATERIAL_TRANSACTIONS MMT
WHERE MMT.TRANSACTION_SOURCE_TYPE_ID + 0 = 5
AND MMT.TRANSACTION_SOURCE_ID = x_purge_rec.wip_entity_id
AND MMT.TRANSACTION_ID = MTA.TRANSACTION_ID
AND MMT.ORGANIZATION_ID = x_purge_rec.org_id );
SELECT COUNT(*)
into x_records_returned
FROM DUAL
WHERE EXISTS
(SELECT 1
FROM MTL_TRANSACTION_LOT_NUMBERS
WHERE TRANSACTION_SOURCE_TYPE_ID = 5
AND TRANSACTION_SOURCE_ID = x_purge_rec.wip_entity_id );
SELECT COUNT(*)
into x_records_returned
FROM DUAL
WHERE EXISTS
(SELECT 1
FROM MTL_UNIT_TRANSACTIONS
WHERE TRANSACTION_SOURCE_TYPE_ID = 5
AND TRANSACTION_SOURCE_ID = x_purge_rec.wip_entity_id );
select count(*) into x_num_rows
from MTL_SERIAL_NUMBERS
WHERE ORIGINAL_WIP_ENTITY_ID = x_purge_rec.wip_entity_id ;
update MTL_SERIAL_NUMBERS
set original_wip_entity_id = -999
WHERE ORIGINAL_WIP_ENTITY_ID = x_purge_rec.wip_entity_id;
SELECT COUNT(*) INTO x_num_rows
FROM CST_PERIOD_VALUE_TEMP
WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id
AND ORGANIZATION_ID = x_purge_rec.org_id;
DELETE FROM CST_PERIOD_VALUE_TEMP
WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id
AND ORGANIZATION_ID = x_purge_rec.org_id;
SELECT COUNT(*) INTO x_num_rows
FROM CST_STD_COST_ADJ_DEBUG
WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id
AND ORGANIZATION_ID = x_purge_rec.org_id;
DELETE FROM CST_STD_COST_ADJ_DEBUG
WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id
AND ORGANIZATION_ID = x_purge_rec.org_id;
SELECT COUNT(*) INTO x_num_rows
FROM PO_REQUISITIONS_INTERFACE_ALL
WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id;
DELETE FROM PO_REQUISITIONS_INTERFACE_ALL
WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id;
SELECT COUNT(*) INTO x_num_rows
FROM RCV_TRANSACTIONS_INTERFACE
WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id;
DELETE FROM RCV_TRANSACTIONS_INTERFACE
WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id;
SELECT COUNT(*) INTO x_num_rows
FROM MRP_RELIEF_INTERFACE
WHERE DISPOSITION_TYPE = 1
AND DISPOSITION_ID = x_purge_rec.wip_entity_id;
DELETE FROM MRP_RELIEF_INTERFACE
WHERE DISPOSITION_TYPE = 1
AND DISPOSITION_ID = x_purge_rec.wip_entity_id;
SELECT COUNT(*) INTO x_num_rows
FROM MTL_DEMAND_INTERFACE
WHERE ORGANIZATION_ID = x_purge_rec.org_id
AND SUPPLY_SOURCE_TYPE = 5
AND SUPPLY_HEADER_ID = x_purge_rec.wip_entity_id;
DELETE FROM MTL_DEMAND_INTERFACE
WHERE ORGANIZATION_ID = x_purge_rec.org_id
AND SUPPLY_SOURCE_TYPE = 5
AND SUPPLY_HEADER_ID = x_purge_rec.wip_entity_id;
SELECT COUNT(*) INTO x_num_rows
FROM MTL_TRANSACTIONS_INTERFACE
WHERE TRANSACTION_SOURCE_TYPE_ID = 5
AND TRANSACTION_SOURCE_ID = x_purge_rec.wip_entity_id;
DELETE FROM MTL_TRANSACTIONS_INTERFACE
WHERE TRANSACTION_SOURCE_TYPE_ID = 5
AND TRANSACTION_SOURCE_ID = x_purge_rec.wip_entity_id;
SELECT COUNT(*)
INTO x_num_rows
FROM MTL_MATERIAL_TRANSACTIONS_TEMP
WHERE TRANSACTION_SOURCE_TYPE_ID = 5
AND INVENTORY_ITEM_ID IS NULL
AND TRANSACTION_SOURCE_ID = x_purge_rec.wip_entity_id;
DELETE FROM MTL_MATERIAL_TRANSACTIONS_TEMP
WHERE TRANSACTION_SOURCE_TYPE_ID = 5
AND INVENTORY_ITEM_ID IS NULL
AND TRANSACTION_SOURCE_ID = x_purge_rec.wip_entity_id;
SELECT COUNT(*)
INTO x_num_rows
FROM MTL_MATERIAL_TRANSACTIONS_TEMP
WHERE TRANSACTION_SOURCE_TYPE_ID = 5
AND INVENTORY_ITEM_ID = x_purge_rec.primary_item_id
AND TRANSACTION_SOURCE_ID = x_purge_rec.wip_entity_id;
DELETE FROM MTL_MATERIAL_TRANSACTIONS_TEMP
WHERE TRANSACTION_SOURCE_TYPE_ID = 5
AND INVENTORY_ITEM_ID = x_purge_rec.primary_item_id
AND TRANSACTION_SOURCE_ID = x_purge_rec.wip_entity_id;
SELECT COUNT(*) INTO X_NUM_ROWS
FROM WIP_RESOURCE_ACTUAL_TIMES
WHERE ORGANIZATION_ID = X_PURGE_REC.ORG_ID AND
WIP_ENTITY_ID = X_PURGE_REC.WIP_ENTITY_ID;
DELETE FROM WIP_RESOURCE_ACTUAL_TIMES
WHERE ORGANIZATION_ID = X_PURGE_REC.ORG_ID AND
WIP_ENTITY_ID = X_PURGE_REC.WIP_ENTITY_ID;
SELECT COUNT(*) INTO X_NUM_ROWS
FROM WIP_LPN_COMPLETIONS_LOTS WLCL
WHERE WLCL.HEADER_ID IN
(
SELECT WLC.HEADER_ID
FROM WIP_LPN_COMPLETIONS WLC
WHERE TRANSACTION_SOURCE_ID = X_PURGE_REC.WIP_ENTITY_ID
);
DELETE FROM WIP_LPN_COMPLETIONS_LOTS WLCL
WHERE WLCL.HEADER_ID IN
(
SELECT WLC.HEADER_ID
FROM WIP_LPN_COMPLETIONS WLC
WHERE WLC.TRANSACTION_SOURCE_ID = X_PURGE_REC.WIP_ENTITY_ID
);
SELECT COUNT(*) INTO X_NUM_ROWS
FROM WIP_LPN_COMPLETIONS_SERIALS WLCS
WHERE WLCS.HEADER_ID IN
(
SELECT WLC.HEADER_ID
FROM WIP_LPN_COMPLETIONS WLC
WHERE TRANSACTION_SOURCE_ID = X_PURGE_REC.WIP_ENTITY_ID
);
DELETE FROM WIP_LPN_COMPLETIONS_SERIALS WLCS
WHERE WLCS.HEADER_ID IN
(
SELECT WLC.HEADER_ID
FROM WIP_LPN_COMPLETIONS WLC
WHERE WLC.TRANSACTION_SOURCE_ID = X_PURGE_REC.WIP_ENTITY_ID
);
SELECT COUNT(*) INTO X_NUM_ROWS
FROM WIP_LPN_COMPLETIONS
WHERE TRANSACTION_SOURCE_ID = X_PURGE_REC.WIP_ENTITY_ID;
DELETE FROM WIP_LPN_COMPLETIONS
WHERE TRANSACTION_SOURCE_ID = X_PURGE_REC.WIP_ENTITY_ID;
SELECT COUNT(*) INTO x_num_rows
FROM MTL_SUPPLY_DEMAND_TEMP
WHERE DISPOSITION_TYPE = 5
AND DISPOSITION_ID = x_purge_rec.wip_entity_id;
DELETE FROM MTL_SUPPLY_DEMAND_TEMP
WHERE DISPOSITION_TYPE = 5
AND DISPOSITION_ID = x_purge_rec.wip_entity_id;
SELECT COUNT(*) INTO x_num_rows
FROM WIP_SO_ALLOCATIONS
WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id;
DELETE FROM WIP_SO_ALLOCATIONS
WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id;
SELECT COUNT(*)
INTO x_num_rows
FROM WIP_SCHEDULING_EXCEPTIONS
WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id;
DELETE FROM WIP_SCHEDULING_EXCEPTIONS
WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id;
SELECT COUNT(*)
INTO x_num_rows
FROM WIP_EXCEPTIONS
WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id
AND ORGANIZATION_ID = x_purge_rec.org_id;
DELETE FROM WIP_EXCEPTIONS
WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id
AND ORGANIZATION_ID = x_purge_rec.org_id;
SELECT COUNT(*) INTO x_num_rows
FROM WIP_PERIOD_BALANCES
WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id;
DELETE FROM WIP_PERIOD_BALANCES
WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id;
SELECT COUNT(*) INTO x_num_rows
FROM WIP_DISCRETE_JOBS
WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id;
DELETE FROM WIP_DISCRETE_JOBS
WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id;
SELECT COUNT(*) INTO x_num_rows
FROM WIP_ENTITIES
WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id;
DELETE FROM WIP_ENTITIES
WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id;
end delete_job_header;
procedure delete_sched_header (
p_option in number,
p_group_id in number,
p_purge_request in get_purge_requests%rowtype,
p_sched_move_txn_flag in out nocopy boolean,
p_sched_txn_flag in out nocopy boolean ) is
x_purge_rec purge_report_type;
select count(*) into l_num_rows
from sys.dual
where (0,0,0,0,0,0,0,0,0,0) <>
(select sum(NVL(TL_RESOURCE_IN, 0) - NVL(TL_RESOURCE_OUT, 0)
- NVL(TL_RESOURCE_VAR,0)),
sum(NVL(TL_OVERHEAD_IN, 0) - NVL(TL_OVERHEAD_OUT, 0)
- NVL(TL_OVERHEAD_VAR,0)),
sum(NVL(TL_OUTSIDE_PROCESSING_IN,0) - NVL(TL_OUTSIDE_PROCESSING_OUT, 0)
- NVL(TL_OUTSIDE_PROCESSING_VAR,0)),
sum(0 - NVL(TL_MATERIAL_OUT, 0) - NVL(TL_MATERIAL_VAR,0)),
sum(0 - NVL(TL_MATERIAL_OVERHEAD_OUT, 0)
- NVL(TL_MATERIAL_OVERHEAD_VAR,0)),
sum(NVL(PL_MATERIAL_IN, 0) - NVL(PL_MATERIAL_OUT, 0)
- NVL(PL_MATERIAL_VAR,0)),
sum(NVL(PL_MATERIAL_OVERHEAD_IN, 0) - NVL(PL_MATERIAL_OVERHEAD_OUT, 0)
- NVL(PL_MATERIAL_OVERHEAD_VAR,0)),
sum(NVL(PL_RESOURCE_IN, 0) - NVL(PL_RESOURCE_OUT, 0)
- NVL(PL_RESOURCE_VAR,0)),
sum(NVL(PL_OVERHEAD_IN, 0) - NVL(PL_OVERHEAD_OUT, 0)
- NVL(PL_OVERHEAD_VAR,0)),
sum(NVL(PL_OUTSIDE_PROCESSING_IN, 0) - NVL(PL_OUTSIDE_PROCESSING_OUT, 0)
- NVL(PL_OUTSIDE_PROCESSING_VAR,0))
from WIP_PERIOD_BALANCES
where WIP_ENTITY_ID = x_purge_rec.wip_entity_id
and REPETITIVE_SCHEDULE_ID = x_purge_rec.schedule_id);
SELECT COUNT(*) into l_num_rows
FROM DUAL
WHERE EXISTS (SELECT 1
FROM PO_DISTRIBUTIONS_ALL
WHERE WIP_REPETITIVE_SCHEDULE_ID = x_purge_rec.schedule_id
AND WIP_ENTITY_ID = x_purge_rec.wip_entity_id
/* Fixed bug 3115844 */
AND po_line_id IS NOT NULL
AND line_location_id IS NOT NULL);
SELECT COUNT(*) into l_num_rows
FROM DUAL
WHERE EXISTS (SELECT 1
FROM PO_DISTRIBUTIONS_ARCHIVE_ALL
WHERE WIP_REPETITIVE_SCHEDULE_ID = x_purge_rec.schedule_id
and WIP_ENTITY_ID = x_purge_rec.wip_entity_id);
SELECT COUNT(*) into l_num_rows
FROM DUAL
WHERE EXISTS (SELECT 1
FROM PO_REQUISITION_LINES_ALL
WHERE WIP_REPETITIVE_SCHEDULE_ID = x_purge_rec.schedule_id
AND WIP_ENTITY_ID = x_purge_rec.wip_entity_id);
SELECT COUNT(*) into l_num_rows
FROM DUAL
WHERE EXISTS (SELECT 1
FROM RCV_TRANSACTIONS
WHERE WIP_REPETITIVE_SCHEDULE_ID = x_purge_rec.schedule_id
AND WIP_ENTITY_ID = x_purge_rec.wip_entity_id);
SELECT COUNT(*) into l_num_rows
FROM DUAL
WHERE EXISTS (SELECT 1
FROM MTL_TRANSACTION_ACCOUNTS MTA,
MTL_MATERIAL_TRANSACTIONS MMT
WHERE MMT.TRANSACTION_SOURCE_TYPE_ID + 0 = 5
AND MMT.TRANSACTION_SOURCE_ID = x_purge_rec.wip_entity_id
AND MMT.REPETITIVE_LINE_ID = x_purge_rec.line_id
AND MMT.TRANSACTION_ID = MTA.TRANSACTION_ID
AND MMT.ORGANIZATION_ID = x_purge_rec.org_id
AND MTA.REPETITIVE_SCHEDULE_ID = x_purge_rec.schedule_id);
SELECT COUNT(*) into l_num_rows
FROM DUAL
WHERE EXISTS (SELECT 1
FROM MTL_MATERIAL_TXN_ALLOCATIONS
WHERE REPETITIVE_SCHEDULE_ID = x_purge_rec.schedule_id
AND ORGANIZATION_ID = x_purge_rec.org_id);
DELETE FROM PO_DISTRIBUTIONS_INTERFACE
WHERE WIP_REPETITIVE_SCHEDULE_ID = x_purge_rec.schedule_id;
DELETE FROM PO_REQUISITIONS_INTERFACE_ALL
WHERE WIP_REPETITIVE_SCHEDULE_ID = x_purge_rec.schedule_id
AND WIP_ENTITY_ID = x_purge_rec.wip_entity_id;
SELECT COUNT(*) INTO x_num_rows
FROM RCV_TRANSACTIONS_INTERFACE
WHERE WIP_REPETITIVE_SCHEDULE_ID = x_purge_rec.schedule_id
AND WIP_ENTITY_ID = x_purge_rec.wip_entity_id;
DELETE FROM RCV_TRANSACTIONS_INTERFACE
WHERE WIP_REPETITIVE_SCHEDULE_ID = x_purge_rec.schedule_id
AND WIP_ENTITY_ID = x_purge_rec.wip_entity_id;
delete_from_table(
p_option => p_option,
p_purge_rec => x_purge_rec,
p_delete_statement =>
'DELETE FROM ' || x_purge_rec.table_name ||
' WHERE REPETITIVE_SCHEDULE_ID = ' || to_char(x_purge_rec.schedule_id));
delete_from_table(
p_option => p_option,
p_purge_rec => x_purge_rec,
p_delete_statement =>
'DELETE FROM ' || x_purge_rec.table_name ||
' WHERE REPETITIVE_SCHEDULE_ID = ' || to_char(x_purge_rec.schedule_id));
SELECT COUNT(*) INTO x_num_rows
FROM WIP_PERIOD_BALANCES
WHERE REPETITIVE_SCHEDULE_ID = x_purge_rec.schedule_id
AND WIP_ENTITY_ID = x_purge_rec.wip_entity_id;
DELETE FROM WIP_PERIOD_BALANCES
WHERE REPETITIVE_SCHEDULE_ID = x_purge_rec.schedule_id
AND WIP_ENTITY_ID = x_purge_rec.wip_entity_id;
SELECT COUNT(*) INTO x_num_rows
FROM WIP_REPETITIVE_SCHEDULES
WHERE REPETITIVE_SCHEDULE_ID = x_purge_rec.schedule_id;
DELETE FROM WIP_REPETITIVE_SCHEDULES
WHERE REPETITIVE_SCHEDULE_ID = x_purge_rec.schedule_id;
end delete_sched_header ;
select Wip_purge_temp_s.nextval into x_group_id from dual;
x_sql_stm1 := ' insert into wip_purge_temp '||
' (group_id, ' ||
' wip_entity_id, ' ||
' repetitive_schedule_id, ' ||
' primary_item_id, ' ||
' line_id, ' ||
' start_date, ' ||
' complete_date, ' ||
' close_date, ' ||
' status_type, ' ||
' organization_id) ' ||
' select ' ||
/* Fix for bug#4902938 - Convert Literal to Bind variable*/
' :l_group_id , ' ||
' wdj.wip_entity_id, ' ||
' NULL, ' ||
' wdj.primary_item_id, ' ||
' NULL, ' ||
' wdj.scheduled_start_date, ' ||
' wdj.scheduled_completion_date, ' ||
' wdj.date_closed, ' ||
' wdj.status_type, ' ||
' wdj.organization_id ' ||
' from wip_discrete_jobs wdj, ' ||
' wip_entities we ' ||
/* Fix for bug#4902938 - Convert Literal to Bind variable*/
' where we.organization_id = :l_organization_id' ||
' and we.organization_id = wdj.organization_id ' ||
' and wdj.status_type = 12 ' || -- WIP_CONSTANTS.CLOSED
/* Fix for bug#4902938 - Convert Literal to Bind variable*/
/* Fix for bug#5137027 - remove '' for date bind variables */
-- bug 5129924
-- Commented out this condition as its handled
-- separately below
-- ntungare Wed May 31 00:25:28 PDT 2006
--
-- ' and wdj.date_closed <= :l_cutoff_date '||
' and we.wip_entity_id = wdj.wip_entity_id ' ;
select p_cutoff_date - nvl(p_days_before_cutoff, 0)
into x_from_date
from dual ;
' (select msi.inventory_item_id ' ||
' from mtl_system_items msi ' ||
' where msi.inventory_item_id = wdj.primary_item_id ' ||
' and msi.organization_id = wdj.organization_id ' ||
' and msi.base_item_id is null ' ||
' and msi.bom_item_type = 4 /*standard*/)) ';
x_sql_stm2 := ' insert into wip_purge_temp '||
' (group_id, ' ||
' wip_entity_id, ' ||
' repetitive_schedule_id, ' ||
' primary_item_id, ' ||
' line_id, ' ||
' start_date, ' ||
' complete_date, ' ||
' close_date, ' ||
' status_type, ' ||
' organization_id) ' ||
' select distinct ' ||
/* Fix for bug#4902938 - Convert Literal to Bind variable*/
' :l_group_id , ' ||
' wrs.wip_entity_id, ' ||
' wrs.repetitive_schedule_id, ' ||
' wri.primary_item_id, ' ||
' wri.line_id, ' ||
' wrs.first_unit_start_date, ' ||
' wrs.last_unit_completion_date, ' ||
' wrs.date_closed, ' ||
' wrs.status_type, ' ||
' wrs.organization_id ' ||
' from wip_repetitive_schedules wrs , ' ||
' wip_repetitive_items wri ' ||
/* Fix for bug#4902938 - Convert Literal to Bind variable*/
' where wri.organization_id = :l_organization_id' ||
' and wrs.organization_id = wri.organization_id ' ||
' and wrs.wip_entity_id = wri.wip_entity_id ' ||
' and wrs.line_id = wri.line_id ' ||
/* Fix for bug#4902938 - Convert Literal to Bind variable*/
/* Fix for bug#5137027 - remove '' for date bind variables */
-- bug 5129924
-- Commented this where clause as its handled below
-- ntungare
-- ' and wrs.date_closed <= :l_cutoff_date '||
' and wrs.status_type in ( 7,5 ) ' ; -- WIP_CONSTANTS.COMP_NOCHRG, CANCELLED
select p_cutoff_date - nvl(p_days_before_cutoff, 0)
into x_from_date
from dual ;
select count(group_id)
into x_count
from wip_purge_temp
where group_id = x_group_id;
WSM_JobPurge_GRP.delete_osfm_tables(
p_option => p_option,
p_group_id => x_group_id,
p_purge_request => x_purge_request,
--Bug#4918553 - Passing detail_flag to OSFM
p_detail_flag => nvl(p_detail_flag, false),
p_return_status => x_ret_success
);
delete_job_details(
p_option => nvl(p_option,REPORT_ONLY),
p_group_id => x_group_id,
p_purge_request => x_purge_request);
delete_sched_details(
p_option => nvl(p_option,REPORT_ONLY),
p_group_id => x_group_id,
p_purge_request => x_purge_request);
delete_job_move_trx(
p_option => nvl(p_option,REPORT_ONLY),
p_group_id => x_group_id,
p_purge_request => x_purge_request);
delete_sched_move_trx(
p_option => nvl(p_option,REPORT_ONLY),
p_group_id => x_group_id,
p_purge_request => x_purge_request,
p_cutoff_date => p_cutoff_date,
p_sched_move_txn_flag => x_sched_move_txn_flag );
delete_job_cost_trx(
p_option => nvl(p_option,REPORT_ONLY),
p_group_id => x_group_id,
p_purge_request => x_purge_request,
p_cut_off_date => p_cutoff_date);
delete_sched_cost_trx(
p_option => nvl(p_option,REPORT_ONLY),
p_group_id => x_group_id,
p_purge_request => x_purge_request,
p_cutoff_date => p_cutoff_date,
p_sched_txn_flag => x_sched_txn_flag );
delete_job_header(
p_option => nvl(p_option,REPORT_ONLY),
p_group_id => x_group_id,
p_purge_request => x_purge_request);
delete_sched_header(
p_option => nvl(p_option,REPORT_ONLY),
p_group_id => x_group_id,
p_purge_request => x_purge_request,
p_sched_move_txn_flag => x_sched_move_txn_flag,
p_sched_txn_flag => x_sched_txn_flag );
delete_purge_temp_table(x_group_id);