[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT mai.enable_flag,
mai.st_status
INTO lv_instance_enabled, lv_staging_table_status
FROM MSC_APPS_INSTANCES mai
WHERE mai.INSTANCE_ID= v_instance_id
AND mai.instance_type IN (G_INS_OTHER,G_INS_EXCH) ;
SELECT tab.partitioned
FROM dba_tables tab,
FND_ORACLE_USERID a,
FND_PRODUCT_INSTALLATIONS b
WHERE a.oracle_id = b.oracle_id
AND b.application_id= 724
AND tab.owner= a.oracle_username
AND tab.table_name= p_table_name;
This function deletes record from the MSC tables.
=========================================================================*/
PROCEDURE DELETE_MSC_TABLE( p_table_name IN VARCHAR2,
p_instance_id IN NUMBER,
p_plan_id IN NUMBER:= NULL,
p_sub_str IN VARCHAR2:= NULL) IS
lv_cnt NUMBER;
FND_MESSAGE.SET_TOKEN('PROCEDURE', 'DELETE_MSC_TABLE:'||p_table_name);
SELECT application_short_name
INTO lv_appl_short_nm
FROM fnd_application
WHERE application_id=724;
lv_sql_stmt:= 'DELETE '||p_table_name
|| lv_where_clause;
MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_cnt || ' Rows Deleted ');
lv_sql_stmt:= 'DELETE '||p_table_name
|| lv_where_clause;
MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_cnt || ' Rows Deleted ');
lv_errtext := substr('DELETE_MSC_TABLE'||'('
||v_sql_stmt||')'|| SQLERRM, 1, 240);
END DELETE_MSC_TABLE;
This function deletes record from MSC_LOCAL_ID_XXX table
=========================================================================*/
PROCEDURE DELETE_LID_TABLE(p_entity_name IN VARCHAR2,
p_lid_table IN VARCHAR2,
p_instance_id IN NUMBER,
p_where_str IN VARCHAR2) IS
lv_cnt NUMBER;
FND_MESSAGE.SET_TOKEN('PROCEDURE', 'DELETE_LID_TABLE:'||p_lid_table);
lv_sql_stmt := ' SELECT COUNT(*) '
||' FROM '||p_lid_table||' lid'
||' WHERE lid.instance_id = :p_instance_id'
||' AND lid.entity_name = :p_entity_name'
|| lv_where_str ;
lv_sql_stmt := 'DELETE FROM '||p_lid_table||' lid'
||' WHERE lid.instance_id = :p_instance_id'
||' AND lid.entity_name = :p_entity_name'
|| lv_where_str ;
lv_sql_stmt := 'DELETE FROM '||p_lid_table||' lid'
||' WHERE lid.instance_id = :p_instance_id'
||' AND lid.entity_name = :p_entity_name'
|| lv_where_str
||' AND ROWNUM < :lv_pbs';
MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'No of Rows deleted : '||lv_total);
lv_errtext := substr('DELETE_LID_TABLE'||'('
||v_sql_stmt||')'|| SQLERRM, 1, 240);
END DELETE_LID_TABLE ;
This is the main program that deletes the record from ths MSC_LOCAL_ID_XXX
table . This accepts parameter, date upto which the record has to be deleted
and different Business Object for which this program should delete LID .
If it is complete refresh then it deletes record from the ODS and LID both.
+==========================================================================*/
PROCEDURE PURGE_LID_TABLES(ERRBUF OUT NOCOPY VARCHAR2,
RETCODE OUT NOCOPY NUMBER,
p_instance_id IN NUMBER,
p_complete_refresh IN NUMBER DEFAULT SYS_NO,
p_date IN VARCHAR2,
p_supply_flag IN NUMBER DEFAULT SYS_NO,
p_demand_flag IN NUMBER DEFAULT SYS_NO)
IS
lv_errtext VARCHAR2(5000);
SELECT rowid
FROM msc_local_id_demand lid
WHERE lid.entity_name ='DISPOSITION_ID_FCT'
AND lid.instance_id = p_instance_id
MINUS
SELECT distinct lid.rowid
FROM msc_local_id_demand lid, msc_demands ms
WHERE lid.instance_id = p_instance_id
AND lid.entity_name = 'DISPOSITION_ID_FCT'
AND ms.sales_order_line_id = lid.local_id
AND ms.plan_id = -1
AND ms.origination_type = 29
AND ms.sr_instance_id = lid.instance_id;
SELECT rowid
FROM msc_local_id_demand lid
WHERE lid.entity_name ='DISPOSITION_ID_MDS'
AND lid.instance_id = p_instance_id
MINUS
SELECT distinct lid.rowid
FROM msc_local_id_demand lid, msc_demands ms
WHERE lid.instance_id = p_instance_id
AND lid.entity_name = 'DISPOSITION_ID_MDS'
AND ms.disposition_id = lid.local_id
AND ms.origination_type = 8
AND ms.plan_id = -1
AND ms.sr_instance_id = lid.instance_id;
SELECT rowid
FROM msc_local_id_demand lid
WHERE lid.entity_name ='SALES_ORDER_ID'
AND lid.instance_id = p_instance_id
MINUS
SELECT distinct lid.rowid
FROM msc_local_id_demand lid, msc_sales_orders ms
WHERE lid.instance_id = p_instance_id
AND lid.entity_name = 'SALES_ORDER_ID'
AND ms.demand_source_header_id = lid.local_id
AND ms.sr_instance_id = lid.instance_id;
SELECT rowid
FROM msc_local_id_demand lid
WHERE lid.entity_name ='DEMAND_ID'
AND lid.instance_id = p_instance_id
MINUS
SELECT distinct lid.rowid
FROM msc_local_id_demand lid, msc_sales_orders ms
WHERE lid.instance_id = p_instance_id
AND lid.entity_name = 'DEMAND_ID'
AND ms.demand_id = lid.local_id
AND ms.sr_instance_id = lid.instance_id;
SELECT rowid
FROM msc_local_id_supply lid
WHERE lid.entity_name ='WIP_ENTITY_ID'
AND lid.instance_id = p_instance_id
MINUS
SELECT distinct lid.rowid
FROM msc_local_id_supply lid, msc_supplies ms
WHERE lid.instance_id = p_instance_id
AND lid.entity_name ='WIP_ENTITY_ID'
AND ms.disposition_id = lid.local_id
AND ms.order_type IN (3,7,27)
AND ms.plan_id = -1
AND ms.sr_instance_id = lid.instance_id;
SELECT rowid
FROM msc_local_id_supply lid
WHERE lid.entity_name ='SR_MTL_SUPPLY_ID'
AND lid.instance_id = p_instance_id
MINUS
SELECT distinct lid.rowid
FROM msc_local_id_supply lid, msc_supplies ms
WHERE lid.instance_id = p_instance_id
AND lid.entity_name = 'SR_MTL_SUPPLY_ID'
AND ms.sr_mtl_supply_id = lid.local_id
AND ms.order_type IN (1,2,8,11,12)
AND ms.plan_id = -1
AND ms.sr_instance_id = lid.instance_id;
SELECT rowid
FROM msc_local_id_supply lid
WHERE lid.entity_name ='PO_LINE_ID'
AND lid.instance_id = p_instance_id
MINUS
SELECT distinct lid.rowid
FROM msc_local_id_supply lid, msc_supplies ms
WHERE lid.instance_id = p_instance_id
AND lid.entity_name = 'PO_LINE_ID'
AND ms.po_line_id = lid.local_id
AND ms.order_type IN (1,2,8,11,12)
AND ms.plan_id = -1
AND ms.sr_instance_id = lid.instance_id;
SELECT rowid
FROM msc_local_id_supply lid
WHERE lid.entity_name ='DISPOSITION_ID'
AND lid.instance_id = p_instance_id
MINUS
SELECT distinct lid.rowid
FROM msc_local_id_supply lid, msc_supplies ms
WHERE lid.instance_id = p_instance_id
AND lid.entity_name = 'DISPOSITION_ID'
AND ms.disposition_id = lid.local_id
AND ms.order_type IN (1,2,8,11,12)
AND ms.plan_id = -1
AND ms.sr_instance_id = lid.instance_id;
SELECT rowid
FROM msc_local_id_supply lid
WHERE lid.entity_name ='DISPOSITION_ID_MPS'
AND lid.instance_id = p_instance_id
MINUS
SELECT distinct lid.rowid
FROM msc_local_id_supply lid, msc_supplies ms
WHERE lid.instance_id = p_instance_id
AND lid.entity_name = 'DISPOSITION_ID_MPS'
AND ms.disposition_id = lid.local_id
AND ms.order_type = 5
AND ms.plan_id = -1
AND ms.sr_instance_id = lid.instance_id;
SELECT rowid
FROM msc_local_id_supply lid
WHERE lid.entity_name ='SCHEDULE_GROUP_ID'
AND lid.instance_id = p_instance_id
MINUS
SELECT distinct lid. rowid
FROM msc_local_id_supply lid, msc_supplies ms
WHERE lid.instance_id = p_instance_id
AND lid.entity_name = 'SCHEDULE_GROUP_ID'
AND ms.schedule_group_id = lid.local_id
AND ms.plan_id = -1
AND ms.sr_instance_id = lid.instance_id;
SELECT transaction_id
FROM msc_supplies ms
WHERE ms.sr_instance_id=p_instance_id
AND ms.plan_id = -1 and trunc(NEW_SCHEDULE_DATE) <=
trunc(to_date(p_date,'YYYY/MM/DD HH24:MI:SS'));
DELETE MSC_ITEM_ID_LID WHERE SR_INSTANCE_ID= v_instance_id;
DELETE MSC_ITEM_ID_LID WHERE SR_INSTANCE_ID= -1;
DELETE_MSC_TABLE('MSC_SYSTEM_ITEMS', v_instance_id, -1);
DELETE_LID_TABLE( p_entity_name => 'SR_INVENTORY_ITEM_ID',
p_lid_table => 'MSC_LOCAL_ID_ITEM',
p_instance_id => v_instance_id ,
p_where_str => NULL );
DELETE_MSC_TABLE( 'MSC_ABC_CLASSES', v_instance_id, NULL);
DELETE_LID_TABLE( p_entity_name => 'ABC_CLASS_ID',
p_lid_table => 'MSC_LOCAL_ID_MISC',
p_instance_id => v_instance_id ,
p_where_str => NULL );
DELETE_MSC_TABLE( 'MSC_ITEM_SUBSTITUTES', v_instance_id, -1);
DELETE_MSC_TABLE( 'MSC_BOMS', v_instance_id, -1);
DELETE_LID_TABLE( p_entity_name => 'BILL_SEQUENCE_ID',
p_lid_table => 'MSC_LOCAL_ID_SETUP',
p_instance_id => v_instance_id ,
p_where_str => NULL );
DELETE_MSC_TABLE( 'MSC_BOM_COMPONENTS', v_instance_id, -1);
DELETE_LID_TABLE( p_entity_name => 'COMPONENT_SEQUENCE_ID',
p_lid_table => 'MSC_LOCAL_ID_SETUP',
p_instance_id => v_instance_id,
p_where_str => NULL );
DELETE_MSC_TABLE( 'MSC_COMPONENT_SUBSTITUTES', v_instance_id, -1);
DELETE_LID_TABLE( p_entity_name => 'CO_PRODUCT_GROUP_ID',
p_lid_table => 'MSC_LOCAL_ID_SETUP',
p_instance_id => v_instance_id,
p_where_str => NULL );
DELETE_MSC_TABLE( 'MSC_ROUTINGS', v_instance_id, -1);
DELETE_LID_TABLE( p_entity_name => 'ROUTING_SEQUENCE_ID',
p_lid_table => 'MSC_LOCAL_ID_SETUP',
p_instance_id => v_instance_id,
p_where_str => NULL );
DELETE_MSC_TABLE( 'MSC_ROUTING_OPERATIONS', v_instance_id, -1);
DELETE_LID_TABLE( p_entity_name => 'OPERATION_SEQUENCE_ID',
p_lid_table => 'MSC_LOCAL_ID_SETUP',
p_instance_id => v_instance_id,
p_where_str => NULL );
DELETE_MSC_TABLE( 'MSC_OPERATION_RESOURCES', v_instance_id, -1);
DELETE_LID_TABLE( p_entity_name => 'RESOURCE_SEQ_NUM',
p_lid_table => 'MSC_LOCAL_ID_SETUP',
p_instance_id => v_instance_id,
p_where_str => NULL );
DELETE_MSC_TABLE( 'MSC_OPERATION_COMPONENTS', v_instance_id, -1);
DELETE_MSC_TABLE( 'MSC_OPERATION_RESOURCE_SEQS', v_instance_id, -1);
DELETE_MSC_TABLE( 'MSC_PROCESS_EFFECTIVITY', v_instance_id, -1);
DELETE_MSC_TABLE( 'MSC_OPERATION_NETWORKS', v_instance_id, -1);
DELETE_MSC_TABLE( 'MSC_ITEM_SUPPLIERS', v_instance_id, -1);
DELETE_MSC_TABLE( 'MSC_SUPPLIER_CAPACITIES', v_instance_id, -1);
DELETE_MSC_TABLE( 'MSC_SUPPLIER_FLEX_FENCES', v_instance_id, -1);
DELETE_MSC_TABLE( 'MSC_RESOURCE_GROUPS', v_instance_id, NULL);
DELETE_MSC_TABLE( 'MSC_DEPARTMENT_RESOURCES', v_instance_id, -1);
DELETE_LID_TABLE( p_entity_name => 'DEPARTMENT_ID',
p_lid_table => 'MSC_LOCAL_ID_SETUP',
p_instance_id => v_instance_id,
p_where_str => NULL );
DELETE_LID_TABLE( p_entity_name => 'LINE_ID',
p_lid_table => 'MSC_LOCAL_ID_SETUP',
p_instance_id => v_instance_id,
p_where_str => NULL );
DELETE_LID_TABLE( p_entity_name => 'RESOURCE_ID',
p_lid_table => 'MSC_LOCAL_ID_SETUP',
p_instance_id => v_instance_id,
p_where_str => NULL );
DELETE_MSC_TABLE( 'MSC_RESOURCE_SHIFTS', v_instance_id, NULL);
DELETE_MSC_TABLE( 'MSC_RESOURCE_CHANGES', v_instance_id, NULL);
DELETE_MSC_TABLE( 'MSC_SIMULATION_SETS', v_instance_id, NULL);
DELETE_MSC_TABLE( 'MSC_RESOURCE_REQUIREMENTS', v_instance_id, -1);
DELETE_MSC_TABLE( 'MSC_PROJECTS', v_instance_id, -1);
DELETE_LID_TABLE( p_entity_name => 'PROJECT_ID',
p_lid_table => 'MSC_LOCAL_ID_MISC',
p_instance_id => v_instance_id,
p_where_str => NULL );
DELETE_MSC_TABLE( 'MSC_PROJECT_TASKS', v_instance_id, -1);
DELETE_LID_TABLE( p_entity_name => 'TASK_ID',
p_lid_table => 'MSC_LOCAL_ID_MISC',
p_instance_id => v_instance_id,
p_where_str => NULL );
DELETE_LID_TABLE( p_entity_name => 'COSTING_GROUP_ID',
p_lid_table => 'MSC_LOCAL_ID_MISC',
p_instance_id => v_instance_id,
p_where_str => NULL );
DELETE_MSC_TABLE( 'MSC_DEMAND_CLASSES', v_instance_id, NULL);
/* DELETE MSC_TRADING_PARTNERS
WHERE sr_instance_id= v_instance_id
AND partner_type=3;
DELETE_LID_TABLE( p_entity_name => 'SR_TP_ID',
p_lid_table => 'MSC_LOCAL_ID_SETUP',
p_instance_id => v_instance_id,
p_where_str => NULL );
DELETE_MSC_TABLE( 'MSC_TRADING_PARTNER_SITES', v_instance_id, NULL,
'AND PARTNER_TYPE=3');
DELETE_LID_TABLE( p_entity_name => 'SR_TP_SITE_ID',
p_lid_table => 'MSC_LOCAL_ID_SETUP',
p_instance_id => v_instance_id,
p_where_str => NULL );
DELETE_MSC_TABLE( 'MSC_PARTNER_CONTACTS', v_instance_id, NULL);
DELETE MSC_LOCATION_ASSOCIATIONS
WHERE SR_INSTANCE_ID= v_instance_id;
DELETE_LID_TABLE(p_entity_name => 'LOCATION_ID',
p_lid_table => 'MSC_LOCAL_ID_SETUP',
p_instance_id => v_instance_id,
p_where_str => NULL ); */ --- for legacyno deletion
DELETE_MSC_TABLE( 'MSC_PLANNERS', v_instance_id, NULL);
DELETE_MSC_TABLE( 'MSC_ITEM_CATEGORIES', v_instance_id, NULL);
DELETE_LID_TABLE( p_entity_name => 'SR_CATEGORY_ID',
p_lid_table => 'MSC_LOCAL_ID_MISC',
p_instance_id => v_instance_id,
p_where_str => NULL );
/* DELETE_LID_TABLE( p_entity_name => 'SR_CATEGORY_SET_ID',
p_lid_table => 'MSC_LOCAL_ID_MISC',
p_instance_id => v_instance_id,
p_where_str => NULL ); */ -- as we do not purge ODS
/* DELETE_MSC_TABLE( 'MSC_PERIOD_START_DATES', v_instance_id, NULL);
DELETE_MSC_TABLE( 'MSC_CAL_YEAR_START_DATES', v_instance_id, NULL);
DELETE_MSC_TABLE( 'MSC_CAL_WEEK_START_DATES', v_instance_id, NULL);
DELETE_MSC_TABLE( 'MSC_RESOURCE_SHIFTS', v_instance_id, NULL);
DELETE_MSC_TABLE( 'MSC_CALENDAR_SHIFTS', v_instance_id, NULL);
DELETE_MSC_TABLE( 'MSC_SHIFT_DATES', v_instance_id, NULL);
DELETE_MSC_TABLE( 'MSC_RESOURCE_CHANGES', v_instance_id, NULL);
DELETE_MSC_TABLE( 'MSC_SHIFT_TIMES', v_instance_id, NULL);
DELETE_MSC_TABLE( 'MSC_SHIFT_EXCEPTIONS', v_instance_id, NULL);
DELETE_LID_TABLE( p_entity_name => 'SHIFT_NUM',
p_lid_table => 'MSC_LOCAL_ID_SETUP',
p_instance_id => v_instance_id,
p_where_str => NULL ); */
DELETE_MSC_TABLE( 'MSC_INTERORG_SHIP_METHODS', v_instance_id,-1);
DELETE_LID_TABLE( p_entity_name => 'SOURCING_RULE_ID',
p_lid_table => 'MSC_LOCAL_ID_MISC',
p_instance_id => v_instance_id ,
p_where_str => NULL );
DELETE_LID_TABLE( p_entity_name => 'ASSIGNMENT_SET_ID',
p_lid_table => 'MSC_LOCAL_ID_MISC',
p_instance_id => v_instance_id,
p_where_str => NULL );
DELETE_LID_TABLE( p_entity_name => 'SR_RECEIPT_ID',
p_lid_table => 'MSC_LOCAL_ID_MISC',
p_instance_id => v_instance_id,
p_where_str => NULL );
DELETE_LID_TABLE( p_entity_name => 'SR_SOURCE_ID',
p_lid_table => 'MSC_LOCAL_ID_MISC',
p_instance_id => v_instance_id,
p_where_str => NULL );
DELETE_LID_TABLE( p_entity_name => 'ASSIGNMENT_ID',
p_lid_table => 'MSC_LOCAL_ID_MISC',
p_instance_id => v_instance_id,
p_where_str => NULL ); */
UPDATE MSC_DESIGNATORS
SET DISABLE_DATE= v_current_date,
LAST_UPDATE_DATE= v_current_date,
LAST_UPDATED_BY= v_current_user
WHERE SR_INSTANCE_ID= v_instance_id
AND COLLECTED_FLAG= SYS_YES;
DELETE_MSC_TABLE( 'MSC_SAFETY_STOCKS', v_instance_id, -1);
DELETE_MSC_TABLE( 'MSC_RESERVATIONS', v_instance_id, -1);
DELETE_MSC_TABLE( 'MSC_DEMANDS', v_instance_id, -1 );
DELETE_MSC_TABLE( 'MSC_SALES_ORDERS', v_instance_id, NULL);
DELETE_LID_TABLE( p_entity_name => 'DISPOSITION_ID_FCT',
p_lid_table => 'MSC_LOCAL_ID_DEMAND',
p_instance_id => v_instance_id ,
p_where_str => NULL );
DELETE_LID_TABLE( p_entity_name => 'DISPOSITION_ID_MDS',
p_lid_table => 'MSC_LOCAL_ID_DEMAND',
p_instance_id => v_instance_id,
p_where_str => NULL );
DELETE_LID_TABLE( p_entity_name => 'DISPOSITION_ID_FCT',
p_lid_table => 'MSC_LOCAL_ID_DEMAND',
p_instance_id => v_instance_id,
p_where_str => NULL );
DELETE_LID_TABLE( p_entity_name => 'SALES_ORDER_ID',
p_lid_table => 'MSC_LOCAL_ID_DEMAND',
p_instance_id => v_instance_id,
p_where_str => NULL );
DELETE_LID_TABLE( p_entity_name => 'DEMAND_ID',
p_lid_table => 'MSC_LOCAL_ID_DEMAND',
p_instance_id => v_instance_id,
p_where_str => NULL );
DELETE_MSC_TABLE( 'MSC_SUPPLIES', v_instance_id, -1);
DELETE_LID_TABLE( p_entity_name => 'DISPOSITION_ID',
p_lid_table => 'MSC_LOCAL_ID_SUPPLY',
p_instance_id => v_instance_id,
p_where_str => NULL );
DELETE_LID_TABLE( p_entity_name => 'PO_LINE_ID',
p_lid_table => 'MSC_LOCAL_ID_SUPPLY',
p_instance_id => v_instance_id,
p_where_str => NULL );
DELETE_LID_TABLE( p_entity_name => 'SCHEDULE_GROUP_ID',
p_lid_table => 'MSC_LOCAL_ID_SUPPLY',
p_instance_id => v_instance_id,
p_where_str => NULL );
DELETE_LID_TABLE( p_entity_name => 'DISPOSTION_ID_MPS',
p_lid_table => 'MSC_LOCAL_ID_SUPPLY',
p_instance_id => v_instance_id,
p_where_str => NULL );
DELETE_LID_TABLE( p_entity_name => 'SR_MTL_SUPPLY_ID',
p_lid_table => 'MSC_LOCAL_ID_SUPPLY',
p_instance_id => v_instance_id,
p_where_str => NULL );
DELETE_LID_TABLE( p_entity_name => 'WIP_ENTITY_ID',
p_lid_table => 'MSC_LOCAL_ID_SUPPLY',
p_instance_id => v_instance_id,
p_where_str => NULL );
DELETE_MSC_TABLE('MSC_JOB_OPERATION_NETWORKS', v_instance_id, -1);
DELETE_MSC_TABLE('MSC_JOB_OPERATIONS', v_instance_id, -1);
DELETE_MSC_TABLE('MSC_JOB_REQUIREMENT_OPS', v_instance_id, -1);
DELETE_MSC_TABLE('MSC_JOB_OP_RESOURCES', v_instance_id, -1);
DELETE_MSC_TABLE( p_table_name =>'MSC_DEMANDS',
p_instance_id => v_instance_id,
p_plan_id => -1,
p_sub_str => ' and trunc(USING_ASSEMBLY_DEMAND_DATE) <= '||'trunc(to_date('||''''||p_date||''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''''||'))');
DELETE_MSC_TABLE( p_table_name =>'MSC_SALES_ORDERS',
p_instance_id => v_instance_id,
p_plan_id => NULL,
p_sub_str => ' and trunc(REQUIREMENT_DATE) <= '||'trunc(to_date('||''''||p_date||''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''''||'))');
MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Delete from MSC_LOCAL_ID_DEMAND for entity_name DISPOSITION_ID_FCT' );
DELETE FROM MSC_LOCAL_ID_DEMAND
WHERE ROWID = lb_rowid(j);
MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'No of Rows deleted : '||lv_total);
MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Delete from MSC_LOCAL_ID_DEMAND for entity_name DISPOSITION_ID_MDS');
DELETE FROM MSC_LOCAL_ID_DEMAND
WHERE ROWID = lb_rowid(j);
MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'No of Rows deleted : '||lv_total);
MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Delete from MSC_LOCAL_ID_DEMAND for entity_name SALES_ORDER_ID');
DELETE FROM MSC_LOCAL_ID_DEMAND
WHERE ROWID = lb_rowid(j);
MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'No of Rows deleted : '||lv_total);
MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Delete from MSC_LOCAL_ID_DEMAND for entity_name DEMAND_ID');
DELETE FROM MSC_LOCAL_ID_DEMAND
WHERE ROWID = lb_rowid(j);
MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'No of Rows deleted : '||lv_total);
msc_st_util.log_message('Delete from MSC_RESOURCE_REQUIREMENTS,MSC_JOB_OPERATIONS,MSC_JOB_OP_RESOURCES,MSC_JOB_REQUIREMENT_OPS for delted supply Id');
DELETE FROM MSC_RESOURCE_REQUIREMENTS
WHERE plan_id = -1 and
sr_instance_id = v_instance_id and
SUPPLY_ID = lv_supplyId ;
DELETE FROM MSC_JOB_OPERATIONS
WHERE plan_id = -1 and
sr_instance_id = v_instance_id and
TRANSACTION_ID = lv_supplyId;
DELETE FROM MSC_JOB_OPERATION_NETWORKS
WHERE plan_id = -1 and
sr_instance_id = v_instance_id and
TRANSACTION_ID = lv_supplyId;
DELETE FROM MSC_JOB_OP_RESOURCES
WHERE plan_id = -1 and
sr_instance_id = v_instance_id and
TRANSACTION_ID = lv_supplyId;
DELETE FROM MSC_JOB_REQUIREMENT_OPS
WHERE plan_id = -1 and
sr_instance_id = v_instance_id and
TRANSACTION_ID = lv_supplyId;
DELETE_MSC_TABLE( p_table_name =>'MSC_SUPPLIES',
p_instance_id => v_instance_id,
p_plan_id => -1,
p_sub_str => ' and trunc(NEW_SCHEDULE_DATE) <= '||'trunc(to_date('||''''||p_date||''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''''||'))');
MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Delete from MSC_LOCAL_ID_SUPPLY for entity_name WIP_ENTITY_ID');
DELETE FROM MSC_LOCAL_ID_SUPPLY
WHERE ROWID = lb_rowid(j);
MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'No of Rows deleted : '||lv_total);
MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Delete from MSC_LOCAL_ID_SUPPLY for entity_name SR_MTL_SUPPLY_ID');
DELETE FROM MSC_LOCAL_ID_SUPPLY
WHERE ROWID = lb_rowid(j);
MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'No of Rows deleted : '||lv_total);
MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Delete from MSC_LOCAL_ID_SUPPLY for entity_name PO_LINE_ID');
DELETE FROM MSC_LOCAL_ID_SUPPLY
WHERE ROWID = lb_rowid(j);
MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'No of Rows deleted : '||lv_total);
MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Delete from MSC_LOCAL_ID_SUPPLY for entity_name DISPOSITION_ID');
DELETE FROM MSC_LOCAL_ID_SUPPLY
WHERE ROWID = lb_rowid(j);
MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'No of Rows deleted : '||lv_total);
MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Delete from MSC_LOCAL_ID_SUPPLY for entity_name DISPOSITION_ID_MPS');
DELETE FROM MSC_LOCAL_ID_SUPPLY
WHERE ROWID = lb_rowid(j);
MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'No of Rows deleted : '||lv_total);
MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Delete from MSC_LOCAL_ID_SUPPLY for entity_name SCHEDULE_GROUP_ID');
DELETE FROM MSC_LOCAL_ID_SUPPLY
WHERE ROWID = lb_rowid(j);
MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,' No of Rows deleted : '||lv_total);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOM_COMPONENTS', p_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOMS', p_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_COMPONENT_SUBSTITUTES', p_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ROUTINGS', p_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ROUTING_OPERATIONS', p_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_OPERATION_RESOURCES', p_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_OPERATION_RESOURCE_SEQS', p_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE('MSC_OPERATION_NETWORKS',p_instance_id,NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_PROCESS_EFFECTIVITY', p_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_OPERATION_COMPONENTS', p_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BILL_OF_RESOURCES', p_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOR_REQUIREMENTS', p_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_CALENDAR_DATES', p_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_PERIOD_START_DATES', p_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_CAL_YEAR_START_DATES', p_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_CAL_WEEK_START_DATES', p_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESOURCE_SHIFTS', p_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_CALENDAR_SHIFTS', p_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SHIFT_DATES', p_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESOURCE_CHANGES', p_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SHIFT_TIMES', p_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SHIFT_EXCEPTIONS', p_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_NET_RESOURCE_AVAIL', p_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_NET_RES_INST_AVAIL', p_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ITEM_CATEGORIES', p_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_CATEGORY_SETS', p_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_DEMANDS', p_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SALES_ORDERS', p_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESERVATIONS', p_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SYSTEM_ITEMS', p_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_DEPARTMENT_RESOURCES', p_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SIMULATION_SETS', p_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESOURCE_GROUPS', p_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SAFETY_STOCKS', p_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_DESIGNATORS', p_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ASSIGNMENT_SETS', p_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SOURCING_RULES', p_instance_id, NULL);
DELETE FROM MSC_SR_ASSIGNMENTS
WHERE SR_ASSIGNMENT_INSTANCE_ID= p_instance_id;
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SR_RECEIPT_ORG', p_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SR_SOURCE_ORG', p_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_INTERORG_SHIP_METHODS', p_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_CARRIER_SERVICES', p_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUB_INVENTORIES', p_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ITEM_SUPPLIERS', p_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_CAPACITIES', p_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_FLEX_FENCES', p_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIES', p_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESOURCE_REQUIREMENTS', p_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_TRADING_PARTNERS', p_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_TRADING_PARTNER_SITES', p_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_LOCATION_ASSOCIATIONS', p_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_UNIT_NUMBERS', p_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_PROJECTS', p_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_PROJECT_TASKS', p_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_PARAMETERS', p_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_UNITS_OF_MEASURE', p_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_UOM_CLASS_CONVERSIONS', p_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_UOM_CONVERSIONS', p_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BIS_PERFORMANCE_MEASURES', p_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BIS_TARGET_LEVELS', p_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BIS_TARGETS', p_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BIS_BUSINESS_PLANS', p_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BIS_PERIODS', p_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ATP_RULES', p_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_PLANNERS', p_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_DEMAND_CLASSES', p_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_PARTNER_CONTACTS', p_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE('MSC_ITEM_SOURCING',p_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE('MSC_CALENDARS',p_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE('MSC_WORKDAY_PATTERNS',p_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE('MSC_CALENDAR_EXCEPTIONS',p_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE('MSC_GROUPS',p_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE('MSC_GROUP_COMPANIES',p_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE('MSC_TRIPS',p_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE('MSC_TRIP_STOPS',p_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_DEPT_RES_INSTANCES',p_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_NET_RES_INST_AVAIL',p_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_JOB_OP_RES_INSTANCES',p_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESOURCE_INSTANCE_REQS',p_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESOURCE_SETUPS',p_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SETUP_TRANSITIONS',p_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_STD_OP_RESOURCES',p_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RES_INSTANCE_CHANGES',p_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESOURCE_CHARGES',p_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE ('MSC_SR_LOOKUPS', p_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_CALENDAR_MONTHS',p_instance_id, NULL);
SELECT DECODE( M2A_DBLINK, NULL, ' ', '@'||M2A_DBLINK)
INTO lv_dblink
FROM MSC_APPS_INSTANCES
WHERE INSTANCE_ID= pInstId;
SELECT application_short_name
INTO lv_schema_short_nm
FROM fnd_application
WHERE application_id=724;
lv_qry_str := 'SELECT table_name,partition_name '
||' FROM fnd_lookup_values a,DBA_TAB_PARTITIONS b'
||' WHERE a.attribute2 = b.table_name' -- (Not in MSC_%)see that meaning is there in upper case
||' AND b.table_owner = :B1'
||' AND a.lookup_type IN (''MSC_ODS_TABLE'',''MSC_OTHER_TABLE'')'-- see that staging table can be included here
||' AND a.ATTRIBUTE11 = ''Y''' -- Column SR_INSTANCE_ID Present
||' AND a.enabled_flag = ''Y'''
||' AND a.view_application_id = 700'
||' AND a.language = userenv(''lang'')'
||' AND a.attribute5 != ''U''' -- Table is Partitioned
||' AND NVL(a.attribute13,''-1'')!=''G'''
||' AND b.partition_name like substr( a.attribute2,5)||''%'''
--AND INSTR(partition_name,'__') > 0
||' AND SUBSTR(b.partition_name,INSTR(partition_name,''__'')+2) IN '||lv_inst_str;
SELECT REPLACE(lv_inst_str,'''','') INTO lv_inst_str FROM DUAL;
MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1,'******* Delete Rows for Instance *******');
FOR tab IN ( SELECT attribute2 tname,ATTRIBUTE12 Plan_Id
FROM fnd_lookup_values a, dba_tables b
WHERE a.attribute2 = b.table_name
AND b.owner = lv_schema
AND lookup_type IN ('MSC_ODS_TABLE','MSC_OTHER_TABLE')
AND enabled_flag = 'Y'
AND view_application_id = 700
AND language = userenv('lang')
AND attribute5='U' -- Unpartitioned table
AND a.ATTRIBUTE11 = 'Y' -- Column SR_INSTANCE_ID Present
AND NVL(attribute13,'-1')<>'G') -- to check
LOOP
IF tab.Plan_Id = 'Y' THEN
lv_qry_str := 'DELETE FROM '|| tab.tname||' WHERE plan_id= -1 AND sr_instance_id IN '||lv_inst_str||' AND ROWNUM <= '||row_limit;
lv_qry_str := 'DELETE FROM '||tab.tname||' WHERE sr_instance_id IN '||lv_inst_str||' AND ROWNUM <= '||row_limit;
EXIT WHEN (SQL%ROWCOUNT < row_limit) OR (lv_err_flag = TRUE); -- Exit when Not enough rows to be deleted;
SELECT application_short_name
INTO lv_schema_short_nm
FROM fnd_application
WHERE application_id=704;
lv_qry_str := 'DELETE FROM '||lv_source_schema ||'.MRP_AP_APPS_INSTANCES_ALL'||GET_M2A_DBLINK(pInstList(indx) )
||' WHERE instance_id IN '||lv_inst_str;
BEGIN -- delete from MSC_INST_PARTITIONS --
lv_qry_str := 'DELETE FROM MSC_INST_PARTITIONS WHERE instance_id IN '||lv_inst_str;
BEGIN -- delete from MSC_APPS_INSTANCES --
lv_qry_str := 'DELETE FROM MSC_APPS_INSTANCES WHERE instance_id IN '||lv_inst_str;
SELECT application_short_name
INTO lv_schema_short_nm
FROM fnd_application
WHERE application_id=724;
lv_qry_str := 'SELECT table_name,partition_name '
||' FROM fnd_lookup_values a,DBA_TAB_PARTITIONS b'
||' WHERE a.attribute2 = b.table_name' --
||' AND b.table_owner = :B1'
||' AND a.lookup_type IN (''MSC_ODS_TABLE'',''MSC_PDSONLY_TABLE'',''MSC_OTHER_TABLE'')'-- see that staging table can be included here
||' AND a.ATTRIBUTE12 = ''Y''' -- Column PLAN_ID Present
||' AND a.enabled_flag = ''Y'''
||' AND a.view_application_id = 700'
||' AND a.language = userenv(''lang'')'
||' AND a.attribute5 != ''U''' -- Table is Partitioned
||' AND b.partition_name like substr( a.attribute2,5)||''%'''
||' AND NVL(a.attribute13,''-1'')!=''G'''
||' AND LTRIM(''MSC_''||partition_name, table_name||''_'') IN '||lv_plan_str;
SELECT REPLACE(lv_plan_str,'''','') INTO lv_plan_str FROM DUAL;
MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1,'******* Delete Rows for plan***********');
FOR tab IN (SELECT a.attribute2 tname
FROM fnd_lookup_values a,dba_tables b
WHERE a.attribute2 = b.table_name
AND b.owner = lv_schema
AND lookup_type IN ('MSC_ODS_TABLE','MSC_PDSONLY_TABLE','MSC_OTHER_TABLE')
AND enabled_flag = 'Y'
AND view_application_id = 700
AND language = userenv('lang')
AND attribute5 = DECODE(fnd_profile.value('MSC_SHARE_PARTITIONS'),'Y',attribute5,'U') -- Unpartitioned table
AND a.ATTRIBUTE12 = 'Y' -- Column PLAN_ID Present
AND NVL(attribute13,'-1')<>'G')
LOOP
lv_qry_str := 'DELETE FROM '||tab.tname||' WHERE plan_id IN '||lv_plan_str||' AND ROWNUM <= '||row_limit;
EXIT WHEN (SQL%ROWCOUNT < row_limit) OR (lv_err_flag = TRUE); -- Exit when Not enough rows to be deleted;
lv_sql_stmt:= 'DELETE '||pTable_name ||
' WHERE ROWNUM < :lv_pbs ' ;
MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,lv_cnt || ' records deleted from ' || pTable_name);
lv_sql1 := 'Select distinct entity_name ENT,table_name,local_id_table,local_id_entity,'
||'where_clause, nvl(instance_id,2) instance_flag, '
||' nvl(plan_id,2) plan_flag, nvl(global,2) global_flag '
||' from msc_entity_table_map_v where nvl(delete_flag,2) = 1 and '
||' UPPER(entity_name)= :entityname'
;
select instance_type into lv_inst_type from msc_apps_instances where instance_id = pINSTANCE_ID;
MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV,'delete_msc_table 1' );
DELETE_MSC_TABLE(p_table_name,lv_inst_id,lv_pln_id,lv_where_clause);