[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_type_selection_method IN NUMBER,
p_include_order_types IN VARCHAR2,
p_exclude_order_types IN VARCHAR2)
RETURN NUMBER
IS
l_order_type_table ORDER_TYPE_TABLE_TYPE;
IF (p_type_selection_method = 1) THEN /*Comma(,) separated values*/
/* Get all the valid order types from the source*/
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'') ';
ELSIF (p_type_selection_method = 2) THEN /* Entity Name Sql stmt*/
p_order_type_flag := l_order_type_flag;
msd_dem_common_utilities.log_debug('SQL statement used for selecting Order Types is :- ' || p_order_type_ids );
ELSIF (p_type_selection_method = 3) THEN /*Valueset*/
p_order_type_flag := l_order_type_flag;
l_order_type_ids := 'SELECT ' ||
'B.TRANSACTION_TYPE_ID ORDER_TYPE_ID ' ||
'FROM ' ||
'OE_TRANSACTION_TYPES_TL T, ' ||
'OE_TRANSACTION_TYPES_ALL B, '||
'FND_FLEX_VALUES FV, ' ||
'FND_FLEX_VALUES_TL FVTL, ' ||
'FND_FLEX_VALUE_SETS FVS '||
'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'') AND ' ||
'fvtl.LANGUAGE = userenv(''LANG'') AND ' ||
'FV.FLEX_VALUE_SET_ID = FVS.FLEX_VALUE_SET_ID AND ' ||
'FVS.FLEX_VALUE_SET_NAME = ''' || l_order_types ||''' '||
'AND UPPER(T.NAME) = UPPER(FVTL.FLEX_VALUE_MEANING) AND ' ||
'fvtl.flex_value_id = fv.flex_value_id and ' ||
'fv.enabled_flag = ''Y'' ';
msd_dem_common_utilities.log_debug('ValueSet used for selecting Order Types is :- ' || l_order_types );
/* THIS PROCEDURE DELETES THE INTERNAL SALES ODERS IN THE SAME LINE OF BUSINESS */
PROCEDURE DELETE_INTERNAL_SALES_ORDERS(
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2,
p_instance_id IN NUMBER )
IS
delete_sql varchar2(1000);
SELECT owner
FROM dba_objects
WHERE owner = owner
AND object_type = 'TABLE'
AND object_name = 'MDP_MATRIX'
ORDER BY created desc;
msd_dem_common_utilities.log_debug ('Entering: msd_dem_collect_history_data.delete_internal_sales_orders - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
delete_sql := 'DELETE FROM ' || x_dest_table || ' sales '
|| ' WHERE EXISTS '
|| ' (SELECT 1 '
|| ' FROM msc_location_associations mla, '
|| ' msc_tp_site_id_lid mtsil, '
|| ' msc_trading_partners orgs, '
|| x_dem_schema || '.t_ep_organization orgs1, '
|| x_dem_schema || '.t_ep_organization orgs2 '
|| ' WHERE sales.ebs_site_sr_pk = mtsil.sr_tp_site_id '
|| ' AND sales.dm_org_code = orgs1.organization '
|| ' AND mla.partner_site_id = mtsil.tp_site_id '
|| ' AND mla.sr_instance_id = :instance_id '
|| ' AND mla.sr_instance_id = mtsil.sr_instance_id '
|| ' AND mla.sr_instance_id = orgs.sr_instance_id '
|| ' AND mla.organization_id = orgs.sr_tp_id '
|| ' AND orgs.partner_type = 3 '
|| ' AND orgs.organization_code = orgs2.organization '
|| ' AND orgs1.t_ep_lob_id = orgs2.t_ep_lob_id '
|| ' AND orgs1.t_ep_lob_id > 0)';
msd_dem_common_utilities.log_debug (delete_sql);
execute immediate delete_sql using p_instance_id;
msd_dem_common_utilities.log_debug ('Exiting: msd_dem_collect_history_data.delete_internal_sales_orders - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
msd_dem_collect_history_data.delete_internal_sales_orders - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
END DELETE_INTERNAL_SALES_ORDERS;
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,
SOURCE_VIEW_HINT,SOURCE_VIEW_HINT2, EXTRA_WHERE
FROM
msd_dem_series
WHERE
series_id = p_series_id
AND series_type = 1;
execute immediate 'select fnd_profile.value' || XDBLINK ||'(''MSC_SUBINVENTORY_PART_CONDITION'') from dual'
into x_profile_val;
execute immediate 'select fnd_profile.value' || XDBLINK ||'(''MSC_SUBINVENTORY_PART_CONDITION'') from dual'
into x_profile_val;
* 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;
p_type_selection_method IN NUMBER DEFAULT G_COMMA,
p_include_order_types IN VARCHAR2 DEFAULT NULL,
p_exclude_order_types IN VARCHAR2 DEFAULT NULL,
p_auto_run_download IN NUMBER,
p_for_spf IN NUMBER DEFAULT G_NO )
IS
/*** LOCAL VARIABLES ****/
x_errbuf VARCHAR2(200) := NULL;
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.';
p_type_selection_method,
p_include_order_types,
p_exclude_order_types );
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 || '''';
x_sql := 'DELETE FROM ' || x_dest_table || ' t1 '
|| ' WHERE ebs_parent_item_sr_pk is not null '
|| ' AND actual_qty = 0 '
|| ' AND ebs_base_model_sr_pk is not null ';
x_sql := 'UPDATE ' || x_dest_table || ' t1 '
|| ' SET ebs_base_model_sr_pk = null '
|| ' WHERE ebs_base_model_sr_pk is not null ';
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'));
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'));
/* Delete Internal Sales Orders in the same Line of Business */
if p_collect_iso = 1 then
msd_dem_common_utilities.log_debug ('Begin Delete Internal Sales Orders in the same Line of Business - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
delete_internal_sales_orders(x_errbuf1,x_retcode1,p_sr_instance_id);
msd_dem_common_utilities.log_debug ('End Delete Internal Sales Orders in the same Line of Business - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
l_sql := 'select table_name, from_date, until_date from '|| g_schema || '.transfer_query where id = ' || l_profile_id;
/* 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 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_instance_id = mio.sr_instance_id ' ||
' AND mtp.sr_tp_id = mio.organization_id '||
' AND mtp.partner_type = 3) ';
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';