APPS.MSD_DEM_PROCESS_SALES_DATA SQL Statements

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

Line: 31

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

Line: 41

select instance_type
from msc_apps_instances
where instance_id = p_instance_id;

Line: 85

 ' 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: 128

 ' 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: 165

 ' UPDATE  '||l_schema_name||'.'||' t_src_sales_tmpl  t1 '
 ||' SET t1.ebs_site_sr_pk = ( SELECT t2.sr_tp_site_id '
 ||'                           FROM msc_tp_site_id_lid t2,msc_trading_partner_sites t3,msc_trading_partners t4 '
 ||'                           WHERE t4.partner_type       = 2 '
 ||'                           AND   t4.partner_name       = substr(t1.dm_site_code,1,instr(t1.dm_site_code,'':'') - 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) ' ;

Line: 195

 ' 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: 225

 ' 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: 237

 ' 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: 275

 ' 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: 288

 ' 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: 335

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

Line: 342

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