[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_sql_stmt := 'SELECT ' ||
'B.TRANSACTION_TYPE_ID ORDER_TYPE_ID, ' ||
'UPPER(B.ORDER_CATEGORY_CODE) ORDER_CATEGORY_CODE, ' ||
'UPPER(T.NAME) NAME ' ||
'FROM ' ||
'OE_TRANSACTION_TYPES_TL' || g_dblink || ' T, ' ||
'OE_TRANSACTION_TYPES_ALL' || g_dblink || ' B '||
'WHERE ' ||
'B.TRANSACTION_TYPE_ID = T.TRANSACTION_TYPE_ID AND ' ||
'B.Transaction_type_code = ''ORDER'' AND ' ||
'nvl(B.SALES_DOCUMENT_TYPE_CODE,''O'') <> ''B'' AND ' ||
'T.LANGUAGE = userenv(''LANG'') ';
SELECT
identifier, STG_SERIES_COL_NAME, MSD_SR_ITEM_PK_COL, MSD_SOURCE_DATE_COL, GMP_SR_ITEM_PK_COL, GMP_SOURCE_DATE_COL, CUSTOM_VIEW_NAME, GMP_CUSTOM_VIEW_NAME
FROM
msd_dem_series
WHERE
series_id = p_series_id
AND series_type = 1;
* This procedure inserts dummy rows into the sales staging tables for new items
*/
PROCEDURE INSERT_DUMMY_ROWS (
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2,
p_dest_table IN VARCHAR2,
p_sr_instance_id IN NUMBER)
IS
/*** CURSORS ***/
CURSOR c_check_new_items
IS
SELECT 1
FROM dual
WHERE EXISTS (SELECT 1
FROM msd_dem_new_items
WHERE sr_instance_id = p_sr_instance_id
AND process_flag = 2);
msd_dem_common_utilities.log_debug ('Entering msd_dem_collect_history_data.insert_dummy_rows - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
errbuf := 'Unable to get the query for inserting dummy rows for new items into sales staging table';
msd_dem_common_utilities.log_message ('Warning(1): msd_dem_collect_history_data.insert_dummy_rows');
UPDATE msd_dem_new_items
SET process_flag = 1
WHERE sr_instance_id = p_sr_instance_id
AND process_flag = 2;
msd_dem_common_utilities.log_debug ('Exiting msd_dem_collect_history_data.insert_dummy_rows - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
msd_dem_common_utilities.log_message ('Exception: msd_dem_collect_history_data.insert_dummy_rows - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
END INSERT_DUMMY_ROWS;
SELECT owner
FROM dba_objects
WHERE owner = owner
AND object_type = 'TABLE'
AND object_name = 'MDP_MATRIX'
ORDER BY created desc;
/* Atleast one parameter must be selected */
IF ( p_bh_bi_bd = G_NO
AND p_bh_bi_rd = G_NO
AND p_bh_ri_bd = G_NO
AND p_bh_ri_rd = G_NO
AND p_sh_si_sd = G_NO
AND p_sh_si_rd = G_NO
AND p_sh_ri_sd = G_NO
AND p_sh_ri_rd = G_NO)
THEN
retcode := -1;
errbuf := 'No series selected for collection';
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.';
msd_dem_common_utilities.log_debug ('Begin delete from sales staging table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
x_sql := 'DELETE FROM '|| x_dest_table || ' where sales_date between ''' || x_from_date || ''' AND ''' || x_to_date || '''';
msd_dem_common_utilities.log_debug ('End delete from sales staging table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
msd_dem_common_utilities.log_message ('Begin Delete data from ERR table - ' || x_dest_table ||'_err');
msd_dem_common_utilities.log_debug ('End delete from ERR table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
/* Collect each series selected by the user */
/* Booking History - Booked Items - Booked Date */
msd_dem_common_utilities.log_debug ('Begin collect Booking History - Booked Items - Booked Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
/* Bug# 5869314 - Insert dummy rows in the staging table for new items */
msd_dem_common_utilities.log_debug ('Begin Insert dummy rows for new items into the staging table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
insert_dummy_rows (
x_errbuf1,
x_retcode1,
x_dest_table,
p_sr_instance_id);
msd_dem_common_utilities.log_message ('Error while inserting dummy rows into the sales staging table for new items. ');
msd_dem_common_utilities.log_debug ('End Insert dummy rows for new items into the staging table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
l_sql := 'select id, table_name, from_date, until_date from '|| g_schema || '.transfer_query where query_name = ''Purge History Data''';
/* Calling API to modify the data profile to purge selected series */
msd_dem_common_utilities.log_debug ('Calling API_MODIFY_INTEG_SERIES_ATTR - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
l_sql := 'select forecast_type_id from '|| g_schema || '.computed_fields where computed_name = ''ebs_bh_book_qty_bd''';
l_sql := 'select forecast_type_id from '|| g_schema || '.computed_fields where computed_name = ''ebs_bh_book_qty_rd''';
l_sql := 'select forecast_type_id from '|| g_schema || '.computed_fields where computed_name = ''ebs_bh_req_qty_bd''';
l_sql := 'select forecast_type_id from '|| g_schema || '.computed_fields where computed_name = ''ebs_bh_req_qty_rd''';
l_sql := 'select forecast_type_id from '|| g_schema || '.computed_fields where computed_name = ''ebs_sh_ship_qty_sd''';
l_sql := 'select forecast_type_id from '|| g_schema || '.computed_fields where computed_name = ''ebs_sh_ship_qty_rd''';
l_sql := 'select forecast_type_id from '|| g_schema || '.computed_fields where computed_name = ''sales''';
l_sql := 'select forecast_type_id from '|| g_schema || '.computed_fields where computed_name = ''ebs_sh_req_qty_rd''';
l_sql := 'select datet from '|| g_schema ||'.inputs where datet >= '''||x_from_date||''' and rownum = 1 order by datet asc';
l_sql := 'select datet from '|| g_schema ||'.inputs where datet <= '''||x_to_date||''' and rownum = 1 order by datet desc';
msd_dem_common_utilities.log_message ('For the selected series, the old data will be purged from ''' || x_from_date ||''' to '''||x_to_date ||'''');
l_sql := 'insert into '|| g_schema ||'.'||l_table_name||'(sdate, level1)'||
'select '''||x_from_date||''', teo.organization from '||g_schema||'.t_ep_organization teo '||
'where teo.organization in
(SELECT mtp.organization_code
FROM msc_instance_orgs mio,
msc_trading_partners mtp
WHERE mio.sr_instance_id = '||p_sr_instance_id||
' AND nvl(mio.org_group, ''-888'') = decode('''||p_collection_group||''', ''-999'', nvl(mio.org_group, ''-888''), '''||p_collection_group||''')'||
' AND nvl(mio.dp_enabled_flag, mio.enabled_flag) = 1 '||
' AND mtp.sr_tp_id = mio.organization_id '||
' AND mtp.partner_type = 3) ';
l_sql := 'select user_id from ' ||msd_dem_common_utilities.get_lookup_value('MSD_DEM_TABLES', 'DCM_PRODUCTS') || ' where product_name = ''' || msd_dem_common_utilities.get_lookup_value('MSD_DEM_COMPONENTS', 'DEMAND_MANAGEMENT') || '''';
l_sql := 'select user_name, password from '||dem_schema||'.user_id where user_id = '||l_user_id;
l_sql := 'select user_id from ' ||msd_dem_common_utilities.get_lookup_value('MSD_DEM_TABLES', 'DCM_PRODUCTS') || ' where product_name = ''' || msd_dem_common_utilities.get_lookup_value('MSD_DEM_COMPONENTS', 'SOP') || '''';
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=EBS%20Full%20Download&sync=no'') FROM dual';