[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select t.conversion_rate std_to_rate,
t.uom_class std_to_class,
f.conversion_rate std_from_rate,
f.uom_class std_from_class
from msc_uom_conversions t,
msc_uom_conversions f
where t.inventory_item_id in (item_id, 0) and
t.uom_code = to_unit and
nvl(t.disable_date, trunc(sysdate) + 1) > trunc(sysdate) and
f.inventory_item_id in (item_id, 0) and
f.uom_code = from_unit and
nvl(f.disable_date, trunc(sysdate) + 1) > trunc(sysdate)
order by t.inventory_item_id desc, f.inventory_item_id desc;
select decode(from_uom_class, p_from_class, 1, 2) from_flag,
decode(to_uom_class, p_to_class, 1, 2) to_flag,
conversion_rate rate
from msc_uom_class_conversions
where inventory_item_id = item_id and
nvl(disable_date, trunc(sysdate) + 1) > trunc(sysdate) and
( (from_uom_class = p_from_class and to_uom_class = p_to_class) or
(from_uom_class = p_to_class and to_uom_class = p_from_class) );
SELECT decode ( m2a_dblink, null, '', '@' || m2a_dblink)
INTO p_dblink
FROM msc_apps_instances
WHERE instance_id = p_sr_instance_id;
SELECT
instance_code,
apps_ver,
gmt_difference/24.0,
instance_type
INTO
p_instance_code,
p_apps_ver,
p_dgmt,
p_instance_type
FROM msc_apps_instances
WHERE instance_id= p_sr_instance_id;
x_sql := 'select series_id from ' ||p_schema ||'.transfer_query_series where id = ' || p_profile_id;
* Update the synonyms MSD_DEM_TRANSFER_LIST and MSD_DEM_TRANSFER_QUERY
* to point to the Demantra's tables TRANSFER_LIST and TRANSFER_QUERY
* if Demantra is installed.
* Sets the profile MSD_DEM_SCHEMA to the Demantra Schema Name
* The checks if the table MDP_MATRIX exists in the Demantra Schema
*/
PROCEDURE UPDATE_SYNONYMS (
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2)
IS
CURSOR c_get_dm_schema
IS
SELECT owner
FROM dba_objects
WHERE owner = owner
AND object_type = 'TABLE'
AND object_name = 'MDP_MATRIX'
ORDER BY created desc;
/* Update synonym MSD_DEM_TRANSFER_LIST to point to Demantra table TRANSFER_LIST */
x_create_synonym_sql := 'CREATE OR REPLACE SYNONYM MSD_DEM_TRANSFER_LIST FOR ' ||
x_dem_schema || '.TRANSFER_LIST';
/* Update synonym MSD_DEM_TRANSFER_QUERY to point to Demantra table TRANSFER_QUERY */
x_create_synonym_sql := 'CREATE OR REPLACE SYNONYM MSD_DEM_TRANSFER_QUERY FOR ' ||
x_dem_schema || '.TRANSFER_QUERY';
/* Update synonym MSD_DEM_TRANSFER_QUERY_LEVELS to point to Demantra table TRANSFER_QUERY_LEVELS */
x_create_synonym_sql := 'CREATE OR REPLACE SYNONYM MSD_DEM_TRANSFER_QUERY_LEVELS FOR ' ||
x_dem_schema || '.TRANSFER_QUERY_LEVELS';
/* Update synonym MSD_DEM_GROUP_TABLES to point to Demantra table GROUP_TABLES */
x_create_synonym_sql := 'CREATE OR REPLACE SYNONYM MSD_DEM_GROUP_TABLES FOR ' ||
x_dem_schema || '.GROUP_TABLES';
/* Deletes the msd_dem_entities_inuse table if the new demantra schema is intstalled
* this will ensure that there will be no mapping between the seeded units in APPS and
* the (display uints,exchange rate,indexes) in Demantra */
PROCEDURE cleanup_entities_inuse(errbuf out nocopy varchar2, retcode out nocopy varchar2)
as
/*Deletes the msd_dem_entities_inuse table */
BEGIN
delete msd_dem_entities_inuse;
x_sql := 'SELECT mp.organization_code org_code ' ||
' FROM msc_instance_orgs mio, mtl_parameters' || x_dblink || ' mp ' ||
' WHERE mio.organization_id = mp.organization_id ' ||
' AND mio.sr_instance_id = :b_sr_instance_id ' ||
' AND mio.org_group = :b_org_group ' ||
' AND nvl(mio.dp_enabled_flag, mio.enabled_flag) = 1 ';
select MEANING
from fnd_lookup_values_vl
where lookup_type = 'MSD_DEM_TABLES'
AND LOOKUP_CODE = 'DM_WIZ_DM_DEF';
L_STMT := 'SELECT TIME_BUCKET FROM '||
L_DM||
' WHERE IS_ACTIVE=1 ';
EXECUTE IMMEDIATE 'SELECT parameter_value FROM msd_dem_setup_parameters' || x_dblink ||
' WHERE parameter_name = ''' || p_parameter_name || ''''
INTO x_parameter_value;
SELECT category_set_id
INTO x_parameter_value
FROM msc_category_set_id_lid
WHERE sr_instance_id = p_sr_instance_id
AND sr_category_set_id = x_sr_category_set_id;
select meaning
from fnd_lookup_values
where lookup_type = p_lookup_type
and lookup_code = p_lookup_code
and language = 'US';
select fnd_profile.value('MSD_DEM_SCHEMA')
from dual;
SELECT table_name
FROM all_tables
WHERE owner = upper(p_schema_name)
AND table_name = 'MDP_MATRIX';
EXECUTE IMMEDIATE 'SELECT display_units FROM ' ||
get_lookup_value ('MSD_DEM_TABLES', 'DISPLAY_UNITS') ||
' WHERE display_units_id = ' || p_unit_id
INTO x_uom_code;
SELECT sr_instance_id
INTO x_sr_instance_id
FROM msc_regions
WHERE zone = p_zone
AND rownum < 2;
SELECT msi.product_family_id
INTO x_product_family_id
FROM msc_system_items msi
WHERE
msi.plan_id = -1
AND msi.sr_instance_id = p_sr_instance_id
AND msi.organization_id = p_master_org_id
AND msi.sr_inventory_item_id = p_sr_inventory_item_id;
SELECT nvl(msi.ato_forecast_control, 3)
INTO x_is_fcstable
FROM msc_system_items msi
WHERE msi.plan_id = -1
AND msi.sr_instance_id = p_sr_instance_id
AND msi.organization_id = p_master_org_id
AND msi.inventory_item_id = x_product_family_id;
SELECT nvl(msi.ato_forecast_control, 3)
INTO x_is_fcstable
FROM msc_system_items msi
WHERE msi.plan_id = -1
AND msi.sr_instance_id = p_sr_instance_id
AND msi.organization_id = x_return_value
AND msi.inventory_item_id = p_inventory_item_id;
select DELIVERY_CALENDAR_CODE
from msc_item_suppliers
where plan_id = p_plan_id
and sr_instance_id = p_sr_instance_id
and organization_id = p_organization_id
and inventory_item_id = p_inventory_item_id
and supplier_id = p_supplier_id
and supplier_site_id = p_supplier_site_id
and using_organization_id = p_using_organization_id;
select calendar_code
from msc_trading_partners
where partner_type = 3
and sr_tp_id = p_organization_id
and sr_instance_id = p_sr_instance_id;
select min(period_start_date) -1 period_end_date
from msc_safety_stocks
where plan_id = p_plan_id
and sr_instance_id = p_sr_instance_id
and organization_id = p_organization_id
and inventory_item_id = p_inventory_item_id
and period_start_date > p_period_start_date;
select CURR_CUTOFF_DATE
from msc_plans
where plan_id = p_plan_id;
SELECT end_date
FROM msd_dem_dates
WHERE p_date BETWEEN start_date AND end_date;
EXECUTE IMMEDIATE 'SELECT datet FROM ( '
|| ' SELECT datet FROM '
|| msd_dem_common_utilities.get_lookup_value ('MSD_DEM_TABLES', 'INPUTS')
|| ' WHERE datet > sysdate '
|| ' ORDER BY datet ) '
|| ' WHERE rownum < 2 '
INTO x_dummy_date;
SELECT substrb(mtp.partner_name, 1, 50)
|| ':' || mtil.sr_cust_account_number
|| ':' || mtps.location
|| ':' || mtps.operating_unit_name
INTO x_site
FROM msc_trading_partner_sites mtps,
msc_trading_partners mtp,
msc_tp_id_lid mtil
WHERE
mtps.partner_site_id = p_customer_site_id
AND mtp.partner_id = mtps.partner_id
AND mtil.tp_id = mtp.partner_id
AND mtil.sr_instance_id = p_sr_instance_id;
SELECT substrb(mtp.partner_name, 1, 50)
|| ':' || mtil.sr_cust_account_number
|| ':' || mtps.location
|| ':' || mtps.operating_unit_name
INTO x_site
FROM msc_trading_partners mtp,
msc_tp_id_lid mtil,
msc_trading_partner_sites mtps,
msc_tp_site_id_lid mtsil
WHERE
mtp.partner_id = p_customer_id
AND mtil.tp_id = mtp.partner_id
AND mtil.sr_instance_id = 21
AND mtps.partner_id = mtp.partner_id
AND mtps.tp_site_code = 'SHIP_TO'
AND mtsil.tp_site_id = mtps.partner_site_id
AND mtsil.sr_instance_id = p_sr_instance_id
AND rownum <2;
SELECT max(mcd.calendar_date)
INTO x_max_date
FROM msd_dem_dates mdd,
msc_calendar_dates mcd
WHERE
p_calendar_date BETWEEN mdd.start_date AND mdd.end_date
AND mcd.sr_instance_id = p_sr_instance_id
AND mcd.calendar_code = p_calendar_code
AND mcd.exception_set_id = -1
AND mcd.calendar_date BETWEEN mdd.start_date AND mdd.end_date
AND mcd.seq_num IS NOT NULL;
SELECT
sr_instance_id,
organization_id,
curr_start_date,
planned_bucket,
planned_bucket_type
INTO
x_sr_instance_id,
x_organization_id,
x_curr_start_date,
x_planned_bucket,
x_planned_bucket_type
FROM
msc_plans
WHERE
plan_id = p_plan_id;
SELECT
calendar_code
INTO
x_calendar_code
FROM
msc_trading_partners
WHERE
partner_type = 3
AND sr_tp_id = x_organization_id
AND sr_instance_id = x_sr_instance_id;
SELECT
max(next_date) - 1
INTO x_plan_cutoff_date
FROM
( SELECT
next_date
FROM
msc_cal_week_start_dates
WHERE
calendar_code = x_calendar_code
AND sr_instance_id = x_sr_instance_id
AND week_start_date > x_curr_start_date
ORDER BY next_date)
WHERE
rownum < x_planned_bucket + 1;
SELECT
max(next_date) - 1
INTO x_plan_cutoff_date
FROM
( SELECT
next_date
FROM
msc_period_start_dates
WHERE
calendar_code = x_calendar_code
AND sr_instance_id = x_sr_instance_id
AND period_start_date > x_curr_start_date
ORDER BY next_date)
WHERE
rownum < x_planned_bucket + 1;
select DELIVERY_CALENDAR_CODE
from msc_item_suppliers
where plan_id = p_plan_id
and sr_instance_id = p_sr_instance_id
and organization_id = p_organization_id
and inventory_item_id = p_inventory_item_id
and supplier_id = p_supplier_id
and supplier_site_id = p_supplier_site_id
and using_organization_id = p_using_organization_id;