APPS.MSC_IMPORT_UTIL SQL Statements

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

Line: 125

select lov_type,sql_statement from
msc_criteria where
folder_object in ('MSC_IMM_UPDATE_ATTRIBUTES',
		  'MSC_REGION_UPDATE_ATTRIBUTES',
		  'MSC_CUST_UPDATE_ATTRIBUTES',
		  'MSC_IMM_DESTINATION_ATTRIBUTES'
		  )
and field_name = l_field_name;

Line: 135

select count(*) from
msc_criteria where
folder_object in ('MSC_IMM_UPDATE_ATTRIBUTES',
		  'MSC_REGION_UPDATE_ATTRIBUTES',
		  'MSC_CUST_UPDATE_ATTRIBUTES',
		  'MSC_IMM_DESTINATION_ATTRIBUTES')
and field_name = l_field_name;

Line: 376

procedure update_record_to_db(stg_rec_rowid VARCHAR2) is

   cursor c_get_simset_id(l_simset_name varchar2) is
   select simulation_set_id
   from msc_item_simulation_sets
   where simulation_set_name = l_simset_name;

Line: 384

   select distinct inventory_item_id
   from msc_system_items
   where item_name = l_item_name and
         organization_id = l_org_id and
         sr_instance_id = l_inst_id and
         plan_id = -1;

Line: 392

   select sr_tp_id
   from msc_trading_partners
   where organization_code = l_org_code;

Line: 397

   select instance_id
   from msc_apps_instances
   where instance_code=l_inst_code;

Line: 402

   select region_id
   from msc_regions
   where zone=l_zone and
   sr_instance_id=l_sr_instance_id;

Line: 408

   select partner_id
   from msc_trading_partners
   where partner_type=2 and
   partner_name = l_cust_name and
   sr_instance_id = l_sr_instance_id;

Line: 415

   select partner_site_id --sr_tp_site_id
   from msc_trading_partner_sites
   where partner_id=l_cust_id and
   location= l_tp_site_code and
   tp_site_code='SHIP_TO' and
   sr_instance_id = l_sr_instance_id;

Line: 424

   select 1
   from msc_criteria
   where folder_object = p_folder_object
   and field_name = p_attr_name;

Line: 431

   select rowid
   from msc_item_attributes
   where simulation_set_id=l_simset_id and
   	 inventory_item_id=l_item_id and
   	 organization_id=l_org_id and
   	 sr_instance_id= l_inst_id and
   	 customer_id=l_cust_id and
   	 customer_site_id=l_site_id;

Line: 443

   select rowid
   from msc_item_attributes
   where simulation_set_id=l_simset_id and
   	 inventory_item_id=l_item_id and
   	 organization_id=l_org_id and
   	 sr_instance_id= l_inst_id and
   	 region_id=l_zone_id;

Line: 452

   select rowid
   from msc_item_attributes
   where simulation_set_id=l_simset_id and
   	 inventory_item_id=l_item_id and
   	 organization_id=l_org_id and
   	 sr_instance_id= l_inst_id and
   	 ( region_id=l_zone_id
         or (region_id is null and l_zone_id is null)) and
   	 ( customer_id=l_cust_id
         or (customer_id is null and l_cust_id is null)) and
   	 ( customer_site_id=l_site_id
         or (customer_site_id is null and l_site_id is null));

Line: 476

   l_update_stmt varchar2(32000);

Line: 477

   l_insert_stmt varchar2(32000);

Line: 478

   l_insert_cols varchar2(32000);

Line: 479

   l_insert_vals varchar2(32000);

Line: 497

	msc_util.msc_debug('In update proc.. '||g_attr_val(1)||'-'||g_attr_val(2));

Line: 557

				Open c_chk_attr('MSC_REGION_UPDATE_ATTRIBUTES',g_attr_name(i));

Line: 560

					msc_util.msc_debug('Attribute '||g_attr_name(i)||' is not updateable at zone level.');

Line: 573

				Open c_chk_attr('MSC_CUST_UPDATE_ATTRIBUTES',g_attr_name(i));

Line: 576

					msc_util.msc_debug('Attribute '||g_attr_name(i)||' is not updateable at Customer level.');

Line: 597

		l_update_stmt := 'update msc_item_attributes set ';

Line: 598

		msc_util.msc_debug('l_update_stmt:'||l_update_stmt);

Line: 614

						l_sql := 'select hidden,displayed from ('||g_sql_stmt(i)||') where displayed='||''''||g_attr_val(i)||'''';

Line: 632

				l_sql := 'select '||g_attr_name(i)||' from msc_item_attributes where rowid='||''''||l_dest_rowid||'''';

Line: 640

					l_update_stmt := l_update_stmt||g_attr_name(i)||'='||l_hidden;

Line: 643

					l_update_stmt := l_update_stmt||','||g_attr_name(i)||'='||l_hidden;

Line: 650

		--	msc_util.msc_debug('l_update_stmt:'||l_update_stmt);

Line: 656

		l_update_stmt := l_update_stmt||',updated_columns_count=updated_columns_count+'||l_upd_attr_count;

Line: 657

		l_update_stmt := l_update_stmt||' where rowid='||''''||l_dest_rowid||'''';

Line: 658

		msc_util.msc_debug('update stmt:'||l_update_stmt);

Line: 659

		msc_Get_name.execute_dsql(l_update_stmt);

Line: 661

	else	-- dest row id is null so insert row into msc_item_attributes
		l_zone_val := nvl(to_char(l_zone_id),'null');

Line: 665

		l_insert_stmt := 'insert into msc_item_attributes ';

Line: 666

		l_insert_cols := 'plan_id,simulation_set_id,inventory_item_id,organization_id,sr_instance_id,created_by,creation_date,last_update_date,last_updated_by,last_update_login,
        region,region_id,region_instance_id,customer_id,customer_site_id,customer_instance_id';

Line: 668

		l_insert_vals := '-1,'||l_sim_set_id||','||l_item_id||','||l_org_id||','||l_inst_id||','||1||',sysdate,sysdate,'||1||','||1||','''||g_attr_val(5)||''','||l_zone_val||','||l_inst_id||','||l_cust_val||','||l_site_val||','||l_inst_id;

Line: 686

						l_sql := 'select hidden,displayed from ('||g_sql_stmt(i)||') where displayed='||''''||g_attr_val(i)||'''';

Line: 705

				l_insert_cols := l_insert_cols||','||g_attr_name(i);

Line: 706

				l_insert_vals := l_insert_vals||','||l_hidden;

Line: 709

	--		msc_util.msc_debug('l_insert_stmt:'||l_insert_stmt);

Line: 715

		l_insert_stmt := l_insert_stmt||'('||l_insert_cols||',updated_columns_count) values ('||l_insert_vals||','||l_upd_attr_count||')';

Line: 716

		msc_util.msc_debug('insert stmt:'||l_insert_stmt);

Line: 717

		msc_Get_name.execute_dsql(l_insert_stmt);

Line: 720

	delete from msc_st_item_attributes where rowid=stg_rec_rowid;

Line: 722

end update_record_to_db;

Line: 727

 select simulation_set_name from
 msc_item_simulation_sets ms,
 msc_plans mp
 where mp.plan_id=l_plan_id and ms.simulation_set_id = mp.item_simulation_set_id;

Line: 743

 SELECT msc_item_simulation_sets_s.nextval
 FROM   dual;

Line: 747

 select simulation_set_id from msc_item_simulation_sets where simulation_set_name = p_simset;

Line: 762

 	insert into msc_item_simulation_sets(simulation_set_name,simulation_set_id,last_update_date,last_updated_by,last_update_login,created_by,creation_date) values (p_simset,l_simset_id,sysdate,1,1,1,sysdate);

Line: 767

  	update msc_plans set item_simulation_set_id = l_simset_id where plan_id=p_plan_id;

Line: 776

 select sql_statement,lov_type from
 msc_criteria where
 folder_object in ('MSC_IMM_UPDATE_ATTRIBUTES',
		  'MSC_REGION_UPDATE_ATTRIBUTES',
		  'MSC_CUST_UPDATE_ATTRIBUTES',
		  'MSC_IMM_DESTINATION_ATTRIBUTES'
		  )
and field_name = p_attr_name;

Line: 800

 select item_simulation_set_id
 from msc_plans
 where plan_id = p_plan_id;

Line: 805

 select rowid
 from msc_item_attributes
 where simulation_set_id=p_simset_id and
 	inventory_item_id = p_item_id and
 	organization_id = p_org_id and
 	sr_instance_id = p_inst_id and
 	region_id is null and
 	customer_id is null;

Line: 847

 if l_rowid is null then -- insert record into msc_item_attributes

 	l_sql := 'insert into msc_item_attributes(plan_id,simulation_set_id,'||
 					'inventory_item_id,'||
 					'organization_id,'||
 					'sr_instance_id,'||
 					p_attr_name||','||
 					'updated_columns_count,'||
 					'last_updated_by,'||
 					'last_update_date,'||
 					'last_update_login,'||
 					'creation_date,'||
 					'created_by) values (-1,'||
 					l_simset_id||','||
 					p_item_id||','||
 					p_org_id||','||
 					p_inst_id||','||
 					l_attr_val||','||
 					1||','||
 					1||','||
 					'sysdate'||','||
 					1||','||
 					'sysdate'||','||
 					1||')';

Line: 874

 else -- update existing row

  	l_sql := 'select '||p_attr_name||',updated_columns_count from msc_item_attributes where rowid='||''''||l_rowid||'''';

Line: 887

 	l_sql := 'update msc_item_attributes set '||p_attr_name||'='||l_attr_val||',updated_columns_count='||l_upd_count||
 		 ' where rowid='||''''||l_rowid||'''';

Line: 921

 l_sql := 'update msc_system_items set '||p_attr_name||'='||l_attr_val||
  	  ' where plan_id='||p_plan_id||' and inventory_item_id ='||p_item_id||' and organization_id='||p_org_id||' and sr_instance_id='||p_sr_instance_id;

Line: 926

  l_sql := 'update msc_supplies set applied=2,status=0'||
  	  ' where plan_id='||p_plan_id||' and inventory_item_id ='||p_item_id||' and organization_id='||p_org_id||' and sr_instance_id='||p_sr_instance_id;

Line: 931

   l_sql := 'update msc_demands set applied=2,status=0'||
  	  ' where plan_id='||p_plan_id||' and inventory_item_id ='||p_item_id||' and organization_id='||p_org_id||' and sr_instance_id='||p_sr_instance_id;

Line: 936

     l_sql := 'update msc_supplier_capacities set applied=2,status=0'||
  	  ' where plan_id='||p_plan_id||' and inventory_item_id ='||p_item_id||' and organization_id='||p_org_id||' and sr_instance_id='||p_sr_instance_id;

Line: 980

select count(*) from msc_st_item_attributes where request_id=l_req_id;

Line: 983

select max(request_id) from msc_st_item_attributes;

Line: 986

select
	ROWID,
	SIMULATION_SET_NAME,
	ITEM_NAME,
	ORGANIZATION_CODE,
	SR_INSTANCE_CODE,
	ZONE,
	CUSTOMER_NAME,
	CUSTOMER_SITE_NAME,
	CRITICAL_COMPONENT_FLAG,
	FULL_LEAD_TIME,
	PREPROCESSING_LEAD_TIME,
	POSTPROCESSING_LEAD_TIME,
	FIXED_LEAD_TIME,
	VARIABLE_LEAD_TIME,
	FIXED_ORDER_QUANTITY,
	FIXED_DAYS_SUPPLY,
	SHRINKAGE_RATE,
	FIXED_LOT_MULTIPLIER,
	MINIMUM_ORDER_QUANTITY,
	MAXIMUM_ORDER_QUANTITY,
	SERVICE_LEVEL,
	CARRYING_COST,
	DEMAND_TIME_FENCE_DAYS,
	ATO_FORECAST_CONTROL,
	PLANNING_TIME_FENCE_DAYS,
	STANDARD_COST,
	PIP_FLAG,
	LIST_PRICE,
	SUBSTITUTION_WINDOW,
	SAFETY_STOCK_BUCKET_DAYS,
	UNIT_WEIGHT,
	UNIT_VOLUME,
	SAFETY_STOCK_CODE,
	SAFETY_STOCK_PERCENT,
	ABC_CLASS_NAME,
	MRP_PLANNING_CODE,
	DRP_PLANNED,
	DAYS_MAX_INV_SUPPLY,
	DAYS_MAX_INV_WINDOW,
	DAYS_TGT_INV_SUPPLY,
	DAYS_TGT_INV_WINDOW,
	CONTINOUS_TRANSFER,
	CONVERGENCE,
	CREATE_SUPPLY_FLAG,
	DIVERGENCE,
	PLANNING_EXCEPTION_SET,
	INVENTORY_USE_UP_DATE,
	PLANNING_MAKE_BUY_CODE,
	WEIGHT_UOM,
	VOLUME_UOM,
	ROUNDING_CONTROL_TYPE,
	ATP_FLAG,
	ATP_COMPONENTS_FLAG,
	DEMAND_FULFILLMENT_LT,
	LOTS_EXPIRATION,
	CONSIGNED_FLAG,
	LEADTIME_VARIABILITY,
	PLANNER_CODE,
	EO_FLAG,
	EXCESS_HORIZON,
	OBSOLESCENCE_DATE,
	REPAIR_LEAD_TIME,
	REPAIR_YIELD,
	REPAIR_COST,
	RELIABILITY,
	FAILURE_IMPACT,
	STANDARD_DEVIATION,
	COEFFICIENT_OF_VARIATION,
	BASIS_AVG_DAILY_DEMAND,
	FORECAST_RULE_FOR_DEMANDS,
	FORECAST_RULE_FOR_RETURNS,
	LIFE_TIME_BUY_DATE,
	END_OF_LIFE_DATE,
	AVG_DEMAND_BEYOND_PH,
	AVG_RETURNS_BEYOND_PH,
	RETURN_FORECAST_TIME_FENCE,
	AVERAGE_DAILY_DEMAND,
	DEFECTIVE_ITEM_COST,
	STD_DEVIATION_FOR_DEMAND,
	MEAN_INTER_ARRIVAL,
	STD_DEVIATION_INTER_ARRIVAL,
	INTERARRIVAL_DIST_METHOD,
	INTERMITTENT_DEMAND,
	MAX_USAGE_FACTOR,
	MIN_REM_SHELF_LIFE_DAYS,
	UNSATISFIED_DEMAND_FACTOR,
	DMD_SATISFIED_PERCENT,
	MIN_SUP_DEM_PERCENT,
	ROP_SAFETY_STOCK,
	COMPUTE_SS,
	COMPUTE_EOQ,
	ORDER_COST
from MSC_ST_ITEM_ATTRIBUTES where request_id=l_req_id;

Line: 1085

l_select_clause varchar2(32000);

Line: 1117

	 /*	l_sql_stmt := 'select rowid,';

Line: 1142

			update_record_to_db(l_imm_stg_rec.rowid);

Line: 1143

			msc_util.msc_debug('after update_record_to_db');

Line: 1151

		msc_util.msc_debug(l_stg_rec_count||' records inserted into staging table');

Line: 1157

		msc_util.msc_debug(l_stg_rec_count||' records not processed properly and not inserted/updated to IMM');

Line: 1167

			update_record_to_db(l_imm_stg_rec.rowid);

Line: 1168

			msc_util.msc_debug('after update_record_to_db');