eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.MSD_DEM_PROCESS_SALES_DATA SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 32


select fnd_profile.value('MSD_DEM_SCHEMA')
from dual;

Line: 44


select instance_type
from msc_apps_instances
where instance_id = p_instance_id;

Line: 110


 ' UPDATE  '||l_schema_name||'.'||' t_src_sales_tmpl  t1 '
 ||' SET t1.ebs_item_sr_pk = ( SELECT t2.sr_inventory_item_id '
 ||'                           FROM msc_system_items t2 '
 ||'                           WHERE t2.plan_id         = -1 '
 ||'                           AND   t2.sr_instance_id  =  :p_instance_id '
 ||'                           AND   t2.item_name       = t1.dm_item_code '
 ||'		               AND   t2.organization_id = ( SELECT t3.sr_tp_id '
 ||'						            FROM msc_trading_partners t3 '
 ||'						            WHERE t3.partner_type      = 3 '
 ||'						            AND   t3.organization_code = t1.dm_org_code '
 ||'						            AND   t3.sr_instance_id    =  :p_instance_id '
 ||'	                                              )'
 ||'			     )'
 ||' WHERE NVL(t1.ebs_item_sr_pk,'||''''||NULL_CHAR||''''||') '
 ||'                       =     '||''''||NULL_CHAR||'''';

Line: 153


 ' UPDATE  '||l_schema_name||'.'||' t_src_sales_tmpl  t1 '
 ||' SET t1.ebs_org_sr_pk = ( SELECT t2.sr_tp_id '
 ||'                          FROM msc_trading_partners t2 '
 ||'                          WHERE t2.sr_instance_id    = :p_instance_id '
 ||'                          AND   t2.organization_code = t1.dm_org_code '
 ||'                          AND partner_type           = 3 '
 ||'                          AND rownum                 = 1 '
 ||'                         )'
 ||' WHERE NVL(t1.ebs_org_sr_pk,'||''''||NULL_CHAR||''''||') '
 ||'                       =    '||''''||NULL_CHAR||'''';

Line: 190


 ' UPDATE  '||l_schema_name||'.'||' t_src_sales_tmpl  t1 '
 ||' SET t1.ebs_site_sr_pk = ( SELECT t2.sr_tp_site_id '
 ||'                           FROM msc_trading_partners t4, msc_tp_id_lid t5, msc_trading_partner_sites t3, msc_tp_site_id_lid t2 '
 ||'                           WHERE t4.partner_type       = 2 '
 ||'                           AND   t4.partner_name       = substr(t1.dm_site_code,1,instr(t1.dm_site_code,'':'') - 1 ) '
 ||'                           AND   t5.partner_type       = 2 '
 ||'                           AND   t5.sr_instance_id     = ' || to_char(p_instance_id)
 ||'                           AND   t5.tp_id              = t4.partner_id '
 ||'                           AND   nvl(t5.sr_cust_account_number, ''###'') = nvl(substr(t1.dm_site_code, instr(t1.dm_site_code, '':'') + 1, '
 ||                                                         ' instr(t1.dm_site_code, '':'', 1, 2) - instr(t1.dm_site_code, '':'', 1, 1) - 1), ''###'') '
 ||'                           AND   t3.partner_id         = t4.partner_id '
 ||'                           AND   t3.location           = substr(t1.dm_site_code, instr(t1.dm_site_code,'':'',1,2)+1, instr(t1.dm_site_code,'':'',1,3)-instr(t1.dm_site_code,'':'',1,2)-1) '
 ||'                           AND   nvl(t3.operating_unit_name, ''###'') = nvl(substr(t1.dm_site_code , instr(t1.dm_site_code, '':'', 1, 3) + 1, '
 ||'                                 decode (instr(t1.dm_site_code , '':'', 1, 4), 0, length(t1.dm_site_code ) + 1,  instr(t1.dm_site_code , '':'', 1, 4)) - instr(t1.dm_site_code , '':'', 1, 3) - 1), ''###'') ' ;

Line: 227


 ' UPDATE  '||l_schema_name||'.'||' t_src_sales_tmpl  t1 '
 ||' SET t1.ebs_site_sr_pk =  msd_dem_sr_util.get_null_pk '
 ||' WHERE dm_site_code = msd_dem_sr_util.get_null_code';

Line: 241


    ' UPDATE  '||l_schema_name||'.'||'t_src_sales_tmpl  t1 '
    ||' SET t1.dm_site_code =  ''' || to_char(p_instance_id) || '::'' || t1.ebs_site_sr_pk '
    ||' WHERE NVL(ebs_site_sr_pk,0) > 0 ';

Line: 274


 ' UPDATE  '||l_schema_name||'.'||' t_src_sales_tmpl  t1 '
 ||' SET t1.ebs_sales_channel_sr_pk = to_char(msd_dem_sr_util.get_null_pk), t1.ebs_sales_channel_code = msd_dem_sr_util.get_null_code'
 ||' WHERE t1.ebs_sales_channel_code is null';

Line: 286


 ' UPDATE  '||l_schema_name||'.'||' t_src_sales_tmpl  t1 '
 ||' SET t1.ebs_sales_channel_sr_pk = ( SELECT t2.sales_channel '
 ||'                                    FROM msc_sales_channel t2 '
 ||'                                    WHERE t2.meaning   = t1.ebs_sales_channel_code '
 ||'                                    AND sr_instance_id = :p_instance_id '
 ||'                                    AND rownum         = 1 '
 ||'                                   )'
 ||' WHERE NVL(t1.ebs_sales_channel_sr_pk,'||''''||NULL_CHAR||''''||') '
 ||'                       =              '||''''||NULL_CHAR||'''';

Line: 324


 ' UPDATE  '||l_schema_name||'.'||' t_src_sales_tmpl  t1 '
 ||' SET t1.ebs_demand_class_sr_pk = to_char(msd_dem_sr_util.get_null_pk), t1.ebs_demand_class_code = msd_dem_sr_util.get_null_code'
 ||' WHERE t1.ebs_demand_class_code is null';

Line: 337


 ' UPDATE  '||l_schema_name||'.'||' t_src_sales_tmpl  t1 '
 ||' SET t1.ebs_demand_class_sr_pk = (  SELECT t2.demand_class '
 ||'                                    FROM msc_demand_classes t2 '
 ||'                                    WHERE t2.meaning   = t1.ebs_demand_class_code '
 ||'                                    AND sr_instance_id = :p_instance_id '
 ||'                                    AND rownum         = 1 '
 ||'                                  )'
 ||' WHERE NVL(t1.ebs_demand_class_sr_pk,'||''''||NULL_CHAR||''''||') '
 ||'                       =             '||''''||NULL_CHAR||'''';

Line: 375


 ' UPDATE  '||l_schema_name||'.'||'t_src_sales_tmpl  t1 '
 ||' SET t1.ebs_base_model_sr_pk = ( SELECT t2.sr_inventory_item_id '
 ||'                           FROM msc_system_items t2 '
 ||'                           WHERE t2.plan_id         = -1 '
 ||'                           AND   t2.sr_instance_id  =  :p_instance_id '
 ||'                           AND   t2.item_name       = t1.ebs_base_model_code '
 ||'		               AND   t2.organization_id = ( SELECT t3.sr_tp_id '
 ||'						            FROM msc_trading_partners t3 '
 ||'						            WHERE t3.partner_type      = 3 '
 ||'						            AND   t3.organization_code = t1.dm_org_code '
 ||'						            AND   t3.sr_instance_id    =  :p_instance_id '
 ||'	                                              )'
 ||'			     )'
 ||' WHERE t1.ebs_base_model_code IS NOT NULL ';

Line: 414


/* 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'));

Line: 417


msd_dem_collect_history_data.insert_dummy_rows (
								errbuf,
								retcode,
								'T_SRC_SALES_TMPL',
								p_instance_id);

Line: 426


   msd_dem_common_utilities.log_message ('Error while inserting dummy rows into the sales staging table for new items. ');

Line: 430


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'));

Line: 450


msd_dem_common_utilities.log_debug ('Begin: Delete from sales staging table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

Line: 457


msd_dem_common_utilities.log_debug ('End: Delete from ERR table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));  -- syenamar