[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 decode ( a2m_dblink, null, '', '@' || a2m_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,
p_demantra_schema IN VARCHAR2 DEFAULT NULL)
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;
SELECT count(1)
FROM dba_tab_columns
WHERE owner = p_owner
AND table_name = p_table_name
AND column_name = p_column_name
AND data_type = p_data_type;
SELECT count(1)
FROM dba_tables
WHERE owner = p_owner
AND table_name = p_table_name;
/* 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';
log_message ('Updated synonym MSD_DEM_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';
log_message ('Updated synonym MSD_DEM_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';
log_message ('Updated synonym MSD_DEM_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';
log_message ('Updated synonym MSD_DEM_GROUP_TABLES');
/* Update synonym T_SRC_SALES_TMPL to point to Demantra table T_SRC_SALES_TMPL */
x_create_synonym_sql := 'CREATE OR REPLACE SYNONYM T_SRC_SALES_TMPL FOR '||
x_dem_schema || '.T_SRC_SALES_TMPL';
log_message ('Updated synonym T_SRC_SALES_TMPL');
/* Update synonym MSD_DEM_RETURN_HISTORY to point to Demantra table MSD_DEM_RETURN_HISTORY */
x_create_synonym_sql := 'CREATE OR REPLACE SYNONYM MSD_DEM_RETURN_HISTORY FOR ' ||
x_dem_schema || '.MSD_DEM_RETURN_HISTORY';
log_message ('Updated synonym MSD_DEM_RETURN_HISTORY');
/* grant select on required tables */
log_message ('Granting SELECT privilege on following tables to ' || x_dem_schema || ' schema.');
x_grant_sql := 'GRANT SELECT ON ' || x_msc_schema || '.MSC_PLANS TO ' || x_dem_schema;
x_grant_sql := 'GRANT SELECT ON ' || x_msc_schema || '.MSC_INT_PUBLISH_DATA TO ' || x_dem_schema;
x_grant_sql := 'GRANT SELECT ON ' || x_msc_schema || '.MSC_INT_SUPPORTED_API TO ' || x_dem_schema;
x_grant_sql := 'GRANT SELECT ON ' || x_msc_schema || '.MSC_BIS_INV_DETAIL TO ' || x_dem_schema;
x_grant_sql := 'GRANT SELECT ON ' || x_msc_schema || '.MSC_SYSTEM_ITEMS TO ' || x_dem_schema;
x_grant_sql := 'GRANT SELECT ON ' || x_msc_schema || '.MSC_PLAN_ORGANIZATIONS TO ' || x_dem_schema;
x_grant_sql := 'GRANT SELECT ON ' || x_msc_schema || '.MSC_TRADING_PARTNERS TO ' || x_dem_schema;
x_grant_sql := 'GRANT SELECT ON ' || x_msc_schema || '.MSC_TP_ID_LID TO ' || x_dem_schema;
x_grant_sql := 'GRANT SELECT ON ' || x_msc_schema || '.MSC_TRADING_PARTNER_SITES TO ' || x_dem_schema;
x_grant_sql := 'GRANT SELECT ON ' || x_msc_schema || '.MSC_TP_SITE_ID_LID TO ' || x_dem_schema;
x_grant_sql := 'GRANT SELECT ON ' || x_msc_schema || '.MSC_REGIONS TO ' || x_dem_schema;
x_grant_sql := 'GRANT SELECT ON ' || x_msd_schema || '.MSD_DEM_TIME TO ' || x_dem_schema;
x_grant_sql := 'GRANT SELECT ON ' || x_msd_schema || '.MSD_DEM_CTO_BOM TO ' || x_dem_schema;
x_get_dem_ver_sql := 'select version from ' || x_dem_schema || '.version_details' ;
x_ext_logout_url_sql := ' Update '|| x_dem_schema || '.sys_params' ||
' Set pval = ''' || x_appl_home_page_url ||
''' Where pname like ''ExternalLogoutUrl'' ' ;
log_message ('Updated ExternalLogoutUrl parameter in sys_params table to :- ' ||x_appl_home_page_url);
x_sql := 'select count(1) from ' || x_dem_schema || '.plan_type_lookup where type_id = 2';
x_sql := 'INSERT INTO ' || x_dem_schema || '.plan_type_lookup (type_id, type_desc) values (2, ''Rapid Plan'')';
EXECUTE IMMEDIATE ' UPDATE ' || x_dem_schema || '.sys_params set pval = ''' || x_msd_schema || '.MSD_DEM_TIME'' ' ||
' where pname = ''Integration1CalendarLoad''';
log_message ('Updated Integration1CalendarLoad parameter in sys_params table to :- ' || x_msd_schema || '.MSD_DEM_TIME');
SELECT count(1)
INTO x_count3
FROM dba_tables
WHERE owner = x_dem_schema
AND table_name = 'EP_T_SRC_SALES_TMPL_LD';
x_sql := 'SELECT COUNT(1) FROM ( SELECT COLUMN_NAME FROM DBA_TAB_COLUMNS WHERE owner = ''' || x_dem_schema || ''' and table_name = ''T_SRC_SALES_TMPL'' '
|| ' MINUS '
|| ' SELECT COLUMN_NAME FROM DBA_TAB_COLUMNS WHERE owner = ''' || x_dem_schema || ''' and table_name = ''EP_T_SRC_SALES_TMPL_LD'' ) ';
|| ' SELECT tsst.*, TRUNC(tsst.sales_date) AGGRE_SD '
|| ' FROM ' || x_dem_schema || '.T_SRC_SALES_TMPL tsst '
|| ' WHERE 1 = 2 ';
SELECT count(1)
INTO x_count4
FROM dba_tables
WHERE owner = x_dem_schema
AND table_name = 'T_SRC_SALES_TMPL_TMP';
update_dem_apcc_synonym(errbuf,retcode);
/* 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;
* UPDATE_DEM_APCC_SYNONYM
* GET_CTO_EFFECTIVE_DATE
* GET_DEM_SYSDATE
* GET_DEM_CTO_BASE_MODEL
* GET_DEM_CTO_OPTION_CLASS
* GET_DEM_CTO_OPTION
* GET_DEM_SPF_BASE_MODEL
* GET_DEM_SPF_OPTION_CLASS
* GET_DEM_SPF_OPTION
* GET_ITEM_LABEL
* GET_ORG_LABEL
* GET_SITE_LABEL
* GET_DC_LABEL
* GET_SC_LABEL
* GET_ITEM_ID
* GET_ORG_ID
* GET_SITE_ID
* GET_DC_ID
* GET_SC_ID
* GET_ASSET_GROUP_LABEL
* GET_CLASS_CODE_LABEL
* GET_WORKORDER_ITEM
* GET_ASSET_GROUP_ID
* GET_CLASS_CODE_ID
* GET_SPF_SR_CAT_SET_ID
*/
/*
* This function returns the comma(,) separated list of demand management enabled orgs
* belonging to the given org group.
*/
FUNCTION GET_ALL_ORGS (
p_org_group IN VARCHAR2,
p_sr_instance_id IN NUMBER)
RETURN VARCHAR2
IS
TYPE REF_CURSOR_TYPE IS REF CURSOR;
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;
EXECUTE IMMEDIATE 'select instance_type from msc_apps_instances where instance_id = :1'
INTO x_instance_type
USING p_sr_instance_id;
x_sql := 'SELECT TO_NUMBER(PARAMETER_VALUE) FROM MSD_DEM_SETUP_PARAMETERS WHERE PARAMETER_NAME = ''MSD_DEM_MASTER_ORG''';
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,
msc_tp_site_id_lid mtsil
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
AND mtsil.tp_site_id = mtps.partner_site_id
AND mtsil.sr_instance_id = p_sr_instance_id
AND mtsil.sr_cust_acct_id = mtil.sr_tp_id
AND rownum < 2;
l_sql := ' select s.site
FROM msc_trading_partners mtp,
msc_tp_id_lid mtil,
msc_trading_partner_sites mtps,
msc_tp_site_id_lid mtsil, '
|| C_MSD_DEM_SCHEMA || '.t_ep_site s
WHERE
mtp.partner_id = ' || p_customer_id ||
' AND mtil.tp_id = mtp.partner_id
AND mtil.sr_instance_id = ' || p_sr_instance_id ||
' 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 mtsil.sr_cust_acct_id = mtil.sr_tp_id -- bug14694419-12.2.1/14694420-12.3 KKHATRI
AND lower(s.site) = lower( substrb(mtp.partner_name, 1, 50)
|| '':'' || mtil.sr_cust_account_number
|| '':'' || mtps.location
|| '':'' || mtps.operating_unit_name )
AND rownum < 2 ';
SELECT /* INDEX(mtpsil MSC_TP_SITE_ID_LID_N1) */
to_char(p_sr_instance_id) || '::' || to_char(mtpsil.sr_tp_site_id)
INTO x_site
FROM msc_tp_site_id_lid mtpsil
WHERE
mtpsil.tp_site_id = p_customer_site_id
AND mtpsil.sr_instance_id = p_sr_instance_id
AND mtpsil.partner_type = 2;
l_sql := ' select /* INDEX(mtps MSC_TRADING_PARTNER_SITES_U3) */
to_char(' || p_sr_instance_id || ') || ''::'' || to_char(mtsil.sr_tp_site_id)
from
msc_trading_partner_sites mtps,
msc_tp_site_id_lid mtsil, '
|| C_MSD_DEM_SCHEMA || '.t_ep_site s
WHERE
mtps.partner_id = ' || p_customer_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 lower(s.site) = to_char(' || p_sr_instance_id || ') || ''::'' || to_char(mtsil.sr_tp_site_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;
SELECT nvl(uom_code,'Ea')
INTO x_master_uom
FROM msc_system_items
WHERE plan_id = -1
AND sr_instance_id = p_sr_instance_id
AND organization_id = x_master_org
AND inventory_item_id = p_inventory_item_id;
x_select_col VARCHAR2(100) := NULL;
SELECT nvl(count(1),0)
INTO x_col_ep_id_present
FROM dba_tab_columns
WHERE owner = upper(C_MSD_DEM_SCHEMA)
AND table_name = upper(p_lookup_table_name)
and column_name = upper(p_lookup_table_name)|| '_EP_ID' ;
SELECT nvl(count(1),0)
INTO x_col_id_present
FROM dba_tab_columns
WHERE owner = upper(C_MSD_DEM_SCHEMA)
AND table_name = upper(p_lookup_table_name)
and column_name = upper(p_lookup_table_name)|| '_ID' ;
x_select_col := SUBSTR(p_lookup_table_name,6) ;
x_select_col := p_lookup_table_name || '_code ' ;
EXECUTE IMMEDIATE 'SELECT ' || x_select_col || ' FROM '
|| C_MSD_DEM_SCHEMA || '.' || p_lookup_table_name
|| ' where ' || p_lookup_table_name || x_col_suffix || ' = ' || to_char(p_lookup_id)
INTO x_ret_value;
EXECUTE IMMEDIATE 'SELECT table_label FROM ' || C_MSD_DEM_SCHEMA || '.group_tables'
|| ' WHERE group_table_id = ' || to_char(p_it_level_code)
INTO x_ret_value;
EXECUTE IMMEDIATE 'SELECT pval FROM ' || C_MSD_DEM_SCHEMA || '.DB_PARAMS'
|| ' WHERE lower(pname) = ''nls_date_format'' '
INTO x_dem_nls_date_format;
EXECUTE IMMEDIATE 'SELECT pval FROM ' || C_MSD_DEM_SCHEMA || '.SYS_PARAMS'
|| ' WHERE lower(pname) = ''min_sales_date'' '
INTO x_dem_min_sales_date;
SELECT datet
INTO C_DEM_MIN_SALES_DATE_D
FROM msd_dem_dates
WHERE x_dem_min_sales_date_d between start_date and end_date;
EXECUTE IMMEDIATE 'SELECT pval FROM ' || C_MSD_DEM_SCHEMA || '.DB_PARAMS'
|| ' WHERE lower(pname) = ''nls_date_format'' '
INTO x_dem_nls_date_format;
EXECUTE IMMEDIATE 'SELECT pval FROM ' || C_MSD_DEM_SCHEMA || '.SYS_PARAMS'
|| ' WHERE lower(pname) = ''max_fore_sales_date'' '
INTO x_dem_max_fore_sales_date;
SELECT datet
INTO C_DEM_MAX_FORE_SALES_DATE_D
FROM msd_dem_dates
WHERE x_dem_max_fore_sales_date_d between start_date and end_date;
SELECT datet
INTO x_date
FROM msd_dem_dates
WHERE p_date between start_date and end_date;
EXECUTE IMMEDIATE 'SELECT count(1) FROM ' || FND_PROFILE.VALUE('MSD_DEM_SCHEMA') ||
'.VERSION_DETAILS ' ||
' WHERE version LIKE ''7.2%'''
INTO x_present;
p_is_select IN NUMBER,
p_column_name IN VARCHAR2)
RETURN VARCHAR2
IS
CURSOR c_get_lookup_value
IS
SELECT meaning,
attribute1,
attribute2,
attribute3,
attribute4
FROM fnd_lookup_values_vl
WHERE lookup_type = p_lookup_type
AND lookup_code = p_lookup_code;
IF (p_is_select = 1)
THEN
x_sql := 'SELECT ' || p_column_name || ' FROM '
|| x_dem_schema || '.' || x_lk_attribute3
|| ' WHERE ' || x_lk_attribute4 || ' = ''' || x_lk_attribute2 || '''';
IF (p_is_select = 1)
THEN
x_sql := 'SELECT ' || p_column_name || ' FROM '
|| x_dem_schema || '.' || x_lk_attribute3
|| ' WHERE lower(application_id) = lower(''' || x_lk_attribute1 || ''')';
* Procedure Name - UPDATE_DEM_APCC_SYNONYM
* This procedure creates the required dummy objets for APCC
* 1) Checks if demantra is installed and the mview created
* 1.1.a) If mview is available, drop it.
* 1.1.b) Create a new mview with the same name - BIEO_OBI_MV
* 1.2) If demantra is not installed, and dummy table available
* 1.2.a) Drop the dummy table
* 1.2.b) Create the dummy table - MSD_DEM_BIEO_OBI_MV_DUMMY
* 2) Create synonym MSD_DEM_BIEO_OBI_MV_SYN accordingly.
*
*/
PROCEDURE UPDATE_DEM_APCC_SYNONYM(
errbuf out NOCOPY varchar2,
retcode out NOCOPY varchar2
)
IS
CURSOR c_check_expview(schema_owner varchar2) IS
SELECT object_name
FROM dba_objects
WHERE owner = upper(schema_owner)
AND object_type = 'MATERIALIZED VIEW'
AND object_name = 'BIEO_OBI_MV'
ORDER BY created DESC;
SELECT object_name,owner
FROM dba_objects
WHERE owner = owner
AND object_type = 'TABLE'
AND object_name = 'MSD_DEM_BIEO_OBI_MV_DUMMY'
ORDER BY created DESC;
select datet SDATE
,1 LEVEL1
,1 LEVEL2
,1 LEVEL3
,1 LEVEL4
,1 LEVEL5
,1 EBS_BH_BOOK_QTY_BD
,1 EBS_SH_SHIP_QTY_SD
,1 ACRY_MAPE_PCT_ERR
,1 PRTY_DEMAND
,1 WEEK4_ABS_PCT_ERR
,1 WEEK8_ABS_PCT_ERR
,1 WEEK13_ABS_PCT_ERR
,1 DKEY_ITEM
,1 DKEY_SITE
,1 ACTUAL_PROD
,1 TOTAL_BACKLOG
,1 FCST_CONSENSUS
,1 BUDGET
,1 SALES_FCST
,1 MKTG_FCST
,1 FCST_BOOKING
,1 FCST_SHIPMENT
,1 PROJ_BACKLOG
,1 RECORD_TYPE
,1 EBS_RETURN_HISTORY
,1 FCST_HYP_ANNUAL_PLAN
,1 FCST_HYP_FINANCIAL
,1 C_PRED
,1 ACTUAL_ON_HAND
,1 EBS_BH_BOOK_QTY_RD
from '||x_dem_schema||'.inputs,
dual';
SELECT SDATE
,LEVEL1
,LEVEL2
,LEVEL3
,LEVEL4
,EBS_BH_BOOK_QTY_BD
,EBS_SH_SHIP_QTY_SD
,ACRY_MAPE_PCT_ERR
,PRTY_DEMAND
,WEEK4_ABS_PCT_ERR
,WEEK8_ABS_PCT_ERR
,WEEK13_ABS_PCT_ERR
,DKEY_ITEM
,DKEY_SITE
,ACTUAL_PROD
,TOTAL_BACKLOG
,FCST_CONSENSUS
,BUDGET
,SALES_FCST
,MKTG_FCST
,FCST_BOOKING
,FCST_SHIPMENT
,PROJ_BACKLOG
,RECORD_TYPE
,NULL EBS_RETURN_HISTORY
,NULL FCST_HYP_ANNUAL_PLAN
,NULL FCST_HYP_FINANCIAL
,NULL C_PRED
,NULL ACTUAL_ON_HAND
,NULL EBS_BH_BOOK_QTY_RD
FROM '||x_dem_schema||'.BIEO_OBI_MV,DUAL';
SELECT SDATE
,LEVEL1
,LEVEL2
,LEVEL3
,LEVEL4
,EBS_BH_BOOK_QTY_BD
,EBS_SH_SHIP_QTY_SD
,ACRY_MAPE_PCT_ERR
,PRTY_DEMAND
,WEEK4_ABS_PCT_ERR
,WEEK8_ABS_PCT_ERR
,WEEK13_ABS_PCT_ERR
,DKEY_ITEM
,DKEY_SITE
,ACTUAL_PROD
,TOTAL_BACKLOG
,FCST_CONSENSUS
,BUDGET
,SALES_FCST
,MKTG_FCST
,FCST_BOOKING
,FCST_SHIPMENT
,PROJ_BACKLOG
,RECORD_TYPE
,EBS_RETURN_HISTORY
,FCST_HYP_ANNUAL_PLAN
,FCST_HYP_FINANCIAL
,C_PRED
,ACTUAL_ON_HAND
,EBS_BH_BOOK_QTY_RD
FROM '||x_dem_schema||'.BIEO_OBI_MV,DUAL';
/* Update synonym MSD_DEM_BIEO_OBI_MV_SYN to point to dummy table MSD_DEM_BIEO_OBI_MV_DUMMY */
x_create_synonym_sql := 'CREATE OR REPLACE SYNONYM MSD_DEM_BIEO_OBI_MV_SYN FOR ' || x_syn_base;
END UPDATE_DEM_APCC_SYNONYM;
EXECUTE IMMEDIATE 'SELECT pval FROM ' || C_MSD_DEM_SCHEMA || '.DB_PARAMS'
|| ' WHERE lower(pname) = ''nls_date_format'' '
INTO x_dem_nls_date_format;
EXECUTE IMMEDIATE 'SELECT pval FROM ' || C_MSD_DEM_SCHEMA || '.SYS_PARAMS'
|| ' WHERE lower(pname) = ''max_sales_date'' '
INTO x_dem_max_sales_date;
EXECUTE IMMEDIATE 'SELECT max(sales_date) FROM ' || C_MSD_DEM_SCHEMA || '.T_SRC_SALES_TMPL'
INTO x_stg_max_sales_date_d;
EXECUTE IMMEDIATE 'SELECT pval FROM ' || C_MSD_DEM_SCHEMA || '.SYS_PARAMS'
|| ' WHERE lower(pname) = ''cto_history_periods'' '
INTO C_DEM_HISTORY_PERIODS;
EXECUTE IMMEDIATE 'SELECT value_float FROM ' || C_MSD_DEM_SCHEMA || '.INIT_PARAMS_0'
|| ' WHERE lower(pname) = ''lead'' '
INTO C_DEM_LEAD;
SELECT datet
INTO x_bom_date
FROM msd_dem_dates
WHERE trunc(x_bom_date) BETWEEN start_date AND end_date;
SELECT mil.sr_category_set_id
INTO var_sr_cat_set_id
FROM msc_category_set_id_lid mil
WHERE mil.category_set_id = var_cat_set_id
AND mil.sr_instance_id = p_sr_instance_id;
select user_id into x_user_id from fnd_user where user_name = p_user_name;
select responsibility_id into x_resp_id from fnd_responsibility_vl where responsibility_name = p_resp_name;
select application_id into x_appl_id from fnd_application_vl where application_name = p_appl_name;
l_sql := 'select user_name, password from '||dem_schema||'.user_id where user_id = '||l_user_id;
l_sql := 'select user_name, password from '||dem_schema||'.user_id where user_id = '||l_user_id;
l_sql := 'SELECT utl_http.request('''||l_url||'/WorkflowServer?action=run_proc&user='||l_user_name||'&password='||l_password||'&schema='|| replace(l_schema_name, ' ', '%20') ||'&sync=no'') FROM dual';
l_sql := ' select wfpl.status, wfs.schema_name from '
|| dem_schema || '.wf_schemas wfs, '
|| dem_schema || '.wf_process_log wfpl '
|| ' where wfpl.process_id = ' || ret_process_id
|| ' and wfpl.schema_id = wfs.schema_id ' ;
x_errbuf := 'The ''Date From'' and ''Date To'' fields are ignored if ''Rolling'' date_range_type is selected.';
x_errbuf := 'The ''History Collection Window'' field is ignored if ''Absolute'' date range type is selected.';
errbuf := 'The ''History Collection Window'' field cannot be NULL, if ''Rolling'' date range type is selected.';
errbuf := 'The ''Date From'' and ''Date To'' fields cannot be NULL, if ''Absolute'' date range type is selected.';
* This procedure will update parameter's value in demantra
* Can be used only for updating paramters in sys_params table
*
* ------------ PARAMETERS LIST ----------------
* p_start_param : parameter name of start_date
* p_start_bucket : offset value for start_date
* p_end_param : parameter name of end_date
* p_end_bucket : offset value for end_date
* p_pivot_param : base date parameter name which is used for
* setting the above start & end params.
* Above offsets are applied against this param.
* p_param_name : parameter name to be updated
* p_param_value : parameter value to be loaded
*
*/
PROCEDURE update_dem_params(
p_start_param IN VARCHAR2 DEFAULT NULL,
p_start_bucket IN VARCHAR2 DEFAULT NULL,
p_end_param IN VARCHAR2 DEFAULT NULL,
p_end_bucket IN VARCHAR2 DEFAULT NULL,
p_pivot_param IN VARCHAR2 DEFAULT NULL,
p_param_name IN VARCHAR2 DEFAULT NULL,
p_param_value IN VARCHAR2 DEFAULT NULL )
IS
/*--- local variables ---*/
x_sql VARCHAR2(500) := NULL;
raise_application_error (-20001, 'Error: msd_dem_common_utilities.update_dem_params - Unable to find schema name.');
raise_application_error (-20002, 'Error: msd_dem_common_utilities.update_dem_params - Missing value for BucketStart adjustment.');
x_sql := 'SELECT 1 FROM ' || x_dem_schema || '.sys_params WHERE LOWER(pname)=''' || LOWER(p_start_param) || '''' ;
raise_application_error (-20003, 'Error: msd_dem_common_utilities.update_dem_params - ' || p_start_param ||' Parameter doesnot exist.');
raise_application_error (-20004, 'Error: msd_dem_common_utilities.update_dem_params - Missing BASE parameter name.');
x_sql := 'SELECT 1 FROM ' || x_dem_schema || '.sys_params WHERE LOWER(pname)=''' || LOWER(p_pivot_param) || '''' ;
raise_application_error (-20005, 'Error: msd_dem_common_utilities.update_dem_params - ' || p_pivot_param ||' Parameter doesnot exist.');
raise_application_error (-20006, 'Error: msd_dem_common_utilities.update_dem_params - Missing start parameter name.');
raise_application_error (-20007, 'Error: msd_dem_common_utilities.update_dem_params - Missing value for BucketEnd adjustment.');
x_sql := 'SELECT 1 FROM ' || x_dem_schema || '.sys_params WHERE LOWER(pname)=''' || LOWER(p_end_param) || '''' ;
RAISE_APPLICATION_ERROR (-20008, 'Error: msd_dem_common_utilities.update_dem_params - ' || p_end_param ||' Parameter doesnot exist.');
raise_application_error (-20009, 'Error: msd_dem_common_utilities.update_dem_params - Missing BASE parameter name.');
x_sql := 'SELECT 1 FROM ' || x_dem_schema || '.sys_params WHERE LOWER(pname)=''' || LOWER(p_pivot_param) || '''' ;
raise_application_error (-200010, 'Error: msd_dem_common_utilities.update_dem_params - ' || p_pivot_param ||' Parameter doesnot exist.');
raise_application_error (-20011, 'Error: msd_dem_common_utilities.update_dem_params - Missing END parameter name.');
raise_application_error (-20012, 'Error: msd_dem_common_utilities.update_dem_params - Missing parameter value.');
x_sql := 'SELECT 1 FROM ' || x_dem_schema || '.sys_params WHERE LOWER(pname)=''' || LOWER(p_param_name) || '''' ;
RAISE_APPLICATION_ERROR (-20013, 'Error: msd_dem_common_utilities.update_dem_params - ' || p_param_name ||' Parameter doesnot exist.');
raise_application_error (-20014, 'Error: msd_dem_common_utilities.update_dem_params - Missing parameter name.');
x_sql := 'SELECT pval FROM ' || x_dem_schema || '.db_params WHERE pname = ''nls_date_format''' ;
x_sql := 'SELECT pval FROM ' || x_dem_schema || '.sys_params WHERE LOWER(pname) = ''' || LOWER(p_pivot_param) || '''' ;
x_sql := 'SELECT pval FROM ' || x_dem_schema || '.sys_params WHERE pname = ''Timeresolution''' ;
x_sql := ' UPDATE ' || x_dem_schema || '.sys_params set pval = ''' || to_char(vd_synchrangestart,'MM/DD/YYYY HH24:MI:SS') || '''' ||
' WHERE LOWER(pname) = '''|| LOWER(p_start_param) || '''' ;
x_sql := ' UPDATE ' || x_dem_schema || '.sys_params set pval = ''' || to_char(vd_synchrangeend,'MM/DD/YYYY HH24:MI:SS') || '''' ||
' WHERE LOWER(pname) = ''' || LOWER(p_end_param) || '''' ;
x_sql := ' UPDATE ' || x_dem_schema || '.sys_params set pval = ''' || p_param_value || '''' ||
' WHERE LOWER(pname) = ''' || LOWER(p_param_name) || '''' ;
RAISE_APPLICATION_ERROR (-20015, 'Error: msd_dem_common_utilities.update_dem_params - '|| substr(sqlerrm,1,150));
END update_dem_params;
* This procedure updates the series load & purge option for the given data profile.
* p_schema : demantra schema name
* p_dataprofile : Data profile lookup value
* p_series : Series lookup value
* p_load : Load option ( 0 - OVERRIDE / 1 - ACCUMULATE / 2 - No Load )
* p_purge: purge option (0 - No Purge / 1 - Purge All dates without new data / 2 - Purge All dates without new data, within DP time range )
* p_notify : notify application server ( 1 - Yes / 2 - No )
*/
PROCEDURE SET_SERIES_OPTIONS(
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2,
p_schema IN VARCHAR2,
p_dataprofile IN VARCHAR2,
p_series IN VARCHAR2,
p_load IN NUMBER,
p_purge IN NUMBER,
p_notify IN NUMBER DEFAULT 2)
IS
x_profile_id number;
x_sql := 'select query_name from '|| p_schema || '.transfer_query where id = ' || x_profile_id;
x_sql := 'select '':'' || mtp_app_org.partner_name '
|| 'from msc_location_associations mla,
msc_trading_partners mtp_app_org '
|| 'where mla.sr_instance_id = ' || p_sr_instance_id
|| 'and mla.partner_id = ' || p_partner_id
|| 'and mla.partner_site_id = ' || p_tp_site_id
|| 'and mtp_app_org.sr_instance_id = mla.sr_instance_id '
|| 'and mtp_app_org.sr_tp_id = mla.organization_id '
|| 'and mtp_app_org.partner_type = 3 '
|| 'and rownum < 2 '
;
l_sql := ' select wfpl.status, wfs.schema_name from '
|| p_dem_schema || '.wf_schemas wfs, '
|| p_dem_schema || '.wf_process_log wfpl '
|| ' where wfpl.process_id = ' || p_ret_process_id
|| ' and wfpl.schema_id = wfs.schema_id ' ;