[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
print_query('SELECT SID, EVENT,seconds_waited FROM
(SELECT SID, EVENT, TIME_WAITED/100 seconds_waited
FROM v$session_event
WHERE SID=' || G_CURRENT_SESSION_ID || '
ORDER BY sid, TIME_WAITED DESC )
WHERE ROWNUM < 10');
print_query( 'SELECT A.SID, A.STATISTIC#, B.NAME, A.VALUE
FROM V$SESSTAT A, V$STATNAME B --V$MYSTAT
WHERE A.SID=' || G_CURRENT_SESSION_ID || '
AND A.STATISTIC# = B.STATISTIC#
AND B.NAME IN (''recursive calls'', ''recursive cpu usage'',
''session logical reads'',''CPU used when call started'',
''CPU used by this session'', ''DB time'',
''session uga memory'',''IPC CPU used by this session'',
''db block gets'', ''consistent gets'',''physical reads'')');
Print_Msg('Parameters selected for planning data pull:');
SELECT
decode(delete_ods_data ,1,'YES','NO') "Purge collected data",
org_group "Org Group",
threshold "Time out",
decode(supplier_capacity ,1,'YES, But retain CP data'
,2,'YES, Replace all values', 'NO') "Approved supplier lists",
decode(atp_rules ,1,'YES','NO') "Atp Rules",
decode(bom ,1,'YES','NO') "BOM/Routings/Resources",
decode(bor ,1,'YES','NO') "Bill of Resources",
decode(calendar_check ,1,'YES','NO') "Calendars",
decode(demand_class ,1,'YES','NO') "Demand Class",
decode(ITEM_SUBSTITUTES ,1,'YES','NO') "End Item Substitutions",
decode(forecast ,1,'YES','NO') "Forecast",
decode(item ,1,'YES','NO') "Item",
decode(kpi_targets_bis ,1,'YES','NO') "Kpi Targets Bis",
decode(mds ,1,'YES','NO') "MDS",
decode(mps ,1,'YES','NO') "MPS",
decode(oh ,1,'YES','NO') "OnHand",
decode(parameter ,1,'YES','NO') "Planning Parameters",
decode(planners ,1,'YES','NO') "Planners",
decode(projects ,1,'YES','NO') "Projects / Tasks",
decode(po ,1,'YES','NO') "PO",
decode(reservations ,1,'YES','NO') "Reservations",
decode(nra ,1,'Collect Existing Data'
,2,'Do not Collect Data'
,3,'Regenerate and Collect Data') "Resource Availability",
decode(safety_stock ,1,'YES','NO') "Safety Stock",
decode(sales_order ,1,'YES','NO') "Sales Order",
decode(sourcing_history ,1,'YES','NO') "Sourcing History",
decode(sourcing ,1,'YES','NO') "Sourcing Rules",
decode(sub_inventories ,1,'YES','NO') "Sub Inventories",
decode(customer ,1,'YES','NO') "Customer",
decode(supplier ,1,'YES','NO') "Supplier",
decode(unit_numbers ,1,'YES','NO') "Unit Numbers",
decode(uom ,1,'YES','NO') "Uom",
decode(user_supply_demand ,1,'YES','NO') "User Supply Demand",
decode(wip ,1,'YES','NO') "Wip",
decode(user_comp_association ,1,'Crete Users and Enable'
,2,'Enable'
,'NO') "User Comp Association",
decode(supplier_response ,1,'YES','NO') "Supplier Response",
decode(trip ,1,'YES','NO') "Transportation details",
decode(po_receipts ,1,'YES','NO') "po receipts",
decode(sales_channel ,1,'YES','NO') "sales channel",
decode(fiscal_calendar ,1,'YES','NO') "fiscal calendar",
decode(INTERNAL_REPAIR ,1,'YES','NO') "Internal Repair Orders",
decode(EXTERNAL_REPAIR ,1,'YES','NO') "External Repair Orders",
decode(payback_demand_supply ,1,'YES','NO') "Payback demand/supply",
decode(currency_conversion ,1,'YES','NO') "Currency conversion",
decode(delivery_Details ,1,'YES','NO') "Delivery Details" ,
decode(ibuc_history ,1,'YES','NO') "Install Base under Contracts" ,
decode(notes_attach ,1,'YES','NO') "Notes (Attachments)" ,
decode(eAM_info ,1,'YES','NO') "eAM Info" , /* USAF*/
decode(eAM_forecasts ,1,'YES','NO') "eAM Forecasts",
decode(cmro_forecasts ,1,'YES','NO') "CMRO Forecasts",
decode(cmro ,1,'YES','NO') "CMRO Data" ,
eam_fc_st_date " eAm Forecasts Start date",
eam_fc_end_date " eAm Forecasts end date" ,
cmro_fc_st_date " CMRO Forecasts Start date",
cmro_fc_end_date " CMRO Forecasts end date",
decode(osp_supply ,1,'YES','NO') "CMRO OSP Supply"
FROM msc_coll_parameters
WHERE instance_id = ]' || pINSTANCE_ID , 2);
Print_Msg('Parameters selected for planning data pull:');
print_query( 'SELECT request_id ,
oracle_Process_id Trace_id ,
req.enable_trace Trace_Flag,
dest.value||''/''||lower (dbnm.value) ||''_ora_''||oracle_process_id||''.trc'' Trace_File_Name
FROM fnd_concurrent_requests req,
v$session ses ,
v$process PROC ,
v$parameter dest ,
v$parameter dbnm ,
fnd_concurrent_programs_vl prog ,
fnd_executables execname
WHERE req.oracle_process_id = proc.spid(+)
AND proc.addr = ses.paddr(+)
AND dest.name = ''user_dump_dest''
AND dbnm.name = ''db_name''
AND req.concurrent_program_id = prog.concurrent_program_id
AND req.program_application_id = prog.application_id
AND prog.application_id = execname.application_id
AND prog.executable_id = execname.executable_id
AND request_id IN ( select request_id from fnd_concurrent_requests req where request_id = '|| pReqID || ' or req.parent_request_id = '|| pReqID ||' )' );
SELECT column_name
FROM all_ind_columns
WHERE index_owner=p_owner
AND table_name = p_table_name
AND index_name = p_index_name
ORDER BY column_position;
SELECT partitioned
INTO l_partitioned
FROM all_indexes
WHERE owner=v_msc_schema
AND table_name = p_table_name
AND index_name = p_index_name;
SELECT application_short_name
into lappshortname
FROM fnd_application
WHERE application_id = p_appl_id;
SELECT alt.tablespace_name,
alt.initial_extent,
alt.next_extent ,
dt.extent_management
INTO v_table_space,
lv_initial_extent,
lv_next_extent ,
lv_extent_management
FROM ALL_TABLES alt,
DBA_TABLESPACES dt
WHERE alt.table_name = upper(p_table_name)
AND alt.owner = upper(p_schema)
AND alt.tablespace_name = dt.tablespace_name ;
SELECT TABLESPACE_NAME
INTO v_index_space
FROM ALL_INDEXES
WHERE table_name = upper(p_table_name)
and owner = upper(p_schema)
and rownum = 1;
SELECT alt.initial_extent,
alt.next_extent ,
dt.extent_management
INTO lv_initial_extent,
lv_next_extent ,
lv_extent_management
FROM ALL_TABLES alt,
DBA_TABLESPACES dt
WHERE alt.table_name = upper(p_table_name)
AND alt.owner = upper(p_schema)
AND alt.tablespace_name = dt.tablespace_name ;
SELECT application_short_name
INTO lv_appl_short_nm
FROM fnd_application
WHERE application_id=724;
SELECT application_short_name
INTO lv_appl_short_nm
FROM fnd_application
WHERE application_id=724;
SELECT application_short_name
INTO lv_appl_short_nm
FROM fnd_application
WHERE application_id=724;
SELECT application_short_name
INTO lv_appl_short_nm
FROM fnd_application
WHERE application_id=724;
SELECT a.oracle_username
INTO lv_schema
FROM FND_ORACLE_USERID a,
FND_PRODUCT_INSTALLATIONS b
WHERE a.oracle_id = b.oracle_id
AND b.application_id = p_apps_id;
select vmi_flag from msc_item_suppliers mis
Where mis.supplier_id = var_supplier_id
and nvl(mis.supplier_site_id,-1) = nvl(var_supplier_site_id,-1)
and mis.using_organization_id = var_org_id
--AND mis.plan_id = var_plan_id
AND mis.plan_id = -1
AND mis.sr_instance_id = var_sr_instance_id
AND mis.sr_instance_id2 = var_sr_instance_id
AND mis.organization_id = var_org_id
AND mis.inventory_item_id = var_inventory_item_id
AND ROWNUM = 1;
select vmi_flag
from msc_item_suppliers mis
Where mis.supplier_id = var_supplier_id
and mis.supplier_site_id is null
and var_supplier_site_id is not null
and mis.using_organization_id = var_org_id
--AND mis.plan_id = var_plan_id
AND mis.plan_id = -1
AND mis.sr_instance_id = var_sr_instance_id
AND mis.sr_instance_id2 = var_sr_instance_id
AND mis.organization_id = var_org_id
AND mis.inventory_item_id = var_inventory_item_id
AND ROWNUM = 1;
select vmi_flag
from msc_item_suppliers mis
Where mis.supplier_id = var_supplier_id
and nvl(mis.supplier_site_id,-1) = nvl(var_supplier_site_id,-1)
and mis.using_organization_id = -1
--AND mis.plan_id = var_plan_id
AND mis.plan_id = -1
AND mis.sr_instance_id = var_sr_instance_id
AND mis.sr_instance_id2 = var_sr_instance_id
AND mis.organization_id = var_org_id
AND mis.inventory_item_id = var_inventory_item_id
AND ROWNUM = 1;
select vmi_flag
from msc_item_suppliers mis
Where mis.supplier_id = var_supplier_id
and mis.supplier_site_id is null
and var_supplier_site_id is not null
and mis.using_organization_id = -1
--AND mis.plan_id = var_plan_id
AND mis.plan_id = -1
AND mis.sr_instance_id = var_sr_instance_id
AND mis.sr_instance_id2 = var_sr_instance_id
AND mis.organization_id = var_org_id
AND mis.inventory_item_id = var_inventory_item_id
AND ROWNUM = 1;
l_sql := 'select 1 from dual@'||p_dblink;
l_sql_stmt := 'insert into '||P_Table_Name||' values( :v_line_no '||
', :v_line_msg '||
', :v_package_name '||
', :v_program_unit )';
select 1 from all_objects
where object_name = p_obj
and owner = p_owner
and object_type = 'TABLE';
MSC_UTIL.g_dbmessage.delete;
SELECT DECODE(M2A_dblink,null,' ','@'||M2A_dblink)
FROM msc_apps_instances
WHERE instance_id = pv_instance_id;
SELECT ITEM_TYPE_ID, ITEM_TYPE_VALUE
INTO G_PARTCONDN_ITEMTYPEID, G_PARTCONDN_GOOD
FROM MSC_ITEM_TYPE_DEFINITIONS
WHERE ITEM_TYPE_NAME = 'PART_CONDITION'
AND ITEM_TYPE_VALUE_MEANING = 'USABLE';
SELECT ITEM_TYPE_VALUE
INTO G_PARTCONDN_BAD
FROM MSC_ITEM_TYPE_DEFINITIONS
WHERE ITEM_TYPE_NAME = 'PART_CONDITION'
AND ITEM_TYPE_VALUE_MEANING = 'DEFECTIVE';
SELECT APPS_VER
INTO v_apps_ver
FROM MSC_APPS_INSTANCES
WHERE INSTANCE_ID= pINSTANCE_ID;
SELECT NVL(TO_NUMBER(FND_PROFILE.VALUE('MSC_COLLECTION_WINDOW_FOR_TP_CHANGES')),0)
INTO v_msc_tp_coll_window
FROM DUAL;
' select 1
from all_mviews
where mview_name =:p1
and owner = :p2 '
into lv_exists using p_MV_name, p_schema_name;
SELECT CATEGORY_SET_ID INTO G_CAT_SET_ID
FROM MSC_CATEGORY_SETS
WHERE CATEGORY_SET_NAME = l_cat_set;
lv_sql_str := ' select 1
from all_objects
where object_name = :p_package_name
and owner = :p2
and object_type = ''PACKAGE''
and status =''VALID''
';
for i in (SELECT syn.owner SYNONYM_owner, syn.synonym_name
FROM --fnd_lookup_values a,
all_synonyms syn
WHERE syn.table_owner = mview_owner and
syn.table_name = mview_name
)
loop
BEGIN
IF I.SYNONYM_owner ='PUBLIC' THEN
lv_sql:='DROP PUBLIC SYNONYM '||i.synonym_name;
for i in (SELECT trg.owner, trg.trigger_name
FROM all_TRIGGERS TRG
WHERE trg.table_owner = mview_owner and
trg.table_name = mview_name
)
loop
BEGIN
lv_sql:= 'DROP TRIGGER '||I.owner||'.'||i.trigger_name;
for i in (SELECT mview_name,msc_util.GET_SCHEMA_NAME(erp_product_code) mview_owner
FROM msc_coll_snapshots_v
WHERE mview_name <> 'ALL SNAPSHOTS'
UNION /*Old Mviews that need to be dropped*/
Select 'BOM_CTO_ORDER_DMD_SN',msc_util.G_BOM_SCHEMA
FROM Dual)
loop
BEGIN
lv_sql:='DROP MATERIALIZED VIEW '||I.mview_owner||'.'||i.mview_name;
select tablespace into lv_Nologging_tblsp from FND_TABLESPACES where tablespace_type = 'NOLOGGING';
FOR j IN(select a.mview_name,c.table_name,c.TABLESPACE_NAME
from MSC_COLL_SNAPSHOTS_V a, ALL_MVIEWS b,ALL_TABLES c
where a.mview_name = b.mview_name
AND b.OWNER = G_APPS_SCHEMA
AND b.CONTAINER_NAME = c.table_name
AND c.owner = G_APPS_SCHEMA
AND c.TABLESPACE_NAME <> lv_Nologging_tblsp )
loop
lv_sql:='DROP MATERIALIZED VIEW '||G_APPS_SCHEMA||'.'||j.mview_name;
SELECT table_name,owner
FROM sys.dba_tables dt
WHERE table_name IN (SELECT mview_name
FROM msc_coll_snapshots_v)
AND NOT EXISTS (SELECT 1
FROM sys.dba_objects do
WHERE do.object_type='MATERIALIZED VIEW'
AND do.owner=dt.owner
AND do.object_name = dt.table_NAME);
select oracle_username
into G_APPS_SCHEMA
from fnd_oracle_userid
where read_only_flag = 'U';
select sid
into G_CURRENT_SESSION_ID
from v$session
where audsid = SYS_CONTEXT('USERENV','SESSIONID');