[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
lv_sql_stmt := ' Select organization_id, nvl(organization_type,1) '
||' From msc_instance_orgs mio '
||' Where sr_instance_id = '||p_instance_id|| ' and organization_id '||lv_in_org_str
;
lv_sql_stmt := 'select mtil.sr_tp_id '
|| ' from msc_trading_partners mtp, msc_tp_id_lid mtil '
|| ' where mtp.modeled_supplier_id is not null and '
|| ' mtp.sr_tp_id '|| lv_ext_repair_org_str || ' and '
|| ' mtil.tp_id=mtp.modeled_supplier_id and '
|| ' mtp.sr_instance_id = ' || p_instance_id || ' and '
|| ' mtp.sr_instance_id = mtil.sr_instance_id and '
|| ' mtil.partner_type = 1 and '
|| ' mtp.partner_type = 3';
SELECT
FND_GLOBAL.USER_NAME,
FND_GLOBAL.RESP_NAME,
FND_GLOBAL.APPLICATION_NAME
INTO lv_user_name,
lv_resp_name,
lv_application_name
FROM dual;
SELECT APPLICATION_ID
INTO lv_application_id
FROM FND_APPLICATION_VL
WHERE APPLICATION_NAME = lv_application_name;
/* SELECT FND_GLOBAL.USER_NAME,
FND_GLOBAL.RESP_NAME,
FND_GLOBAL.APPLICATION_NAME
INTO lv_user_name,
lv_resp_name,
lv_application_name
FROM dual;
SELECT APPLICATION_ID
INTO lv_application_id
FROM FND_APPLICATION_VL
WHERE APPLICATION_NAME = lv_application_name;
SELECT DECODE ( pRTYPE, MSC_UTIL.G_COMPLETE, 'C',
MSC_UTIL.G_INCREMENTAL, 'I',
MSC_UTIL.G_PARTIAL, 'P',
MSC_UTIL.G_CONT, 'T')
INTO lv_refresh_type
FROM DUAL;
' SELECT owner,master FROM ALL_SNAPSHOTS'|| p_dblink || ' WHERE name = :p_snap_name '
INTO lv_snap_schema,lv_base_table_name
USING p_snap_name;
' SELECT LOG_TABLE FROM ALL_SNAPSHOT_LOGS'|| p_dblink
||' WHERE MASTER = upper(:pTABLE_NAME)
AND LOG_OWNER = upper(:pSCHEMA_NAME)
AND ROWNUM = 1 '
INTO lv_mlog_tab_name
USING lv_base_table_name, lv_snap_schema;
SELECT delete_ods_data,org_group,threshold,supplier_capacity, atp_rules,
bom, bor, calendar_check, demand_class,ITEM_SUBSTITUTES, forecast, item,
kpi_targets_bis, mds, mps, oh, parameter, planners,
projects, po, reservations, nra, safety_stock,
sales_order, sourcing_history, sourcing, sub_inventories,
customer, supplier, unit_numbers, uom, user_supply_demand, wip, user_comp_association,
/* CP-ACK starts */
supplier_response,
/* CP-ACK ends */
trip, ds_mode, po_receipts, sales_channel,fiscal_calendar,INTERNAL_REPAIR,EXTERNAL_REPAIR, -- For Bug 5909379
payback_demand_supply, currency_conversion,delivery_Details,ibuc_history,notes_attach ,
eAM_info, eAM_forecasts, eam_fc_st_date, eam_fc_end_date, cmro, cmro_forecasts, cmro_fc_st_date, cmro_fc_end_date
,cmro_closed_wo,osp_supply
INTO p_prec.purge_ods_flag,p_prec.org_group_flag, p_prec.threshold,p_prec.app_supp_cap_flag,
p_prec.atp_rules_flag, p_prec.bom_flag,
p_prec.bor_flag, p_prec.calendar_flag,
p_prec.demand_class_flag, p_prec.item_subst_flag,p_prec.forecast_flag,
p_prec.item_flag, p_prec.kpi_bis_flag,
p_prec.mds_flag, p_prec.mps_flag,
p_prec.oh_flag, p_prec.parameter_flag,
p_prec.planner_flag, p_prec.project_flag,
p_prec.po_flag, p_prec.reserves_flag,
p_prec.resource_nra_flag, p_prec.saf_stock_flag,
p_prec.sales_order_flag, p_prec.source_hist_flag,
p_prec.sourcing_rule_flag, p_prec.sub_inventory_flag,
p_prec.tp_customer_flag, p_prec.tp_vendor_flag,
p_prec.unit_number_flag, p_prec.uom_flag,
p_prec.user_supply_demand_flag, p_prec.wip_flag, p_prec.user_company_flag,
/* CP-ACK starts */
p_prec.supplier_response_flag,
/* CP-ACK ends */
p_prec.trip_flag, p_prec.ds_mode,p_prec.po_receipts_flag,
p_prec.sales_channel_flag,p_prec.fiscal_calendar_flag,p_prec.internal_repair_flag,p_prec.external_repair_flag, -- for bug 5909379
p_prec.payback_demand_supply_flag, p_prec.currency_conversion_flag ,--bug # 6469722
p_prec.delivery_details_flag,p_prec.ibuc_history_flag,p_prec.notes_attach_flag,
p_prec.eAM_info_flag, p_prec.eAM_forecasts_flag, p_prec.eam_fc_st_date, p_prec.eam_fc_end_date,
p_prec.cmro_flag, p_prec.cmro_forecasts_flag, p_prec.cmro_fc_st_date, p_prec.cmro_fc_end_date
,p_prec.cmro_closed_wo,p_prec.osp_supply
FROM msc_coll_parameters
WHERE instance_id = p_instance_id;
SELECT DECODE( M2A_DBLINK,
NULL, NULL_DBLINK,
'@'||M2A_DBLINK),
LAST_TGT_CONT_COLL_TIME
INTO v_dblink,
lv_last_tgt_cont_coll_time
FROM MSC_APPS_INSTANCES
WHERE INSTANCE_ID= p_instance_id;
select mio.organization_id org_id
from msc_instance_orgs mio,
msc_coll_parameters mcp
where mio.sr_instance_id= p_instance_id
and mcp.instance_id = p_instance_id
and mio.enabled_flag= 1
and (( mcp.org_group = MSC_UTIL.G_ALL_ORGANIZATIONS ) or
(mio.org_group=mcp.org_group));
select organization_id org_id
from msc_instance_orgs
where sr_instance_id= p_instance_id
and enabled_flag= 1;
SELECT DECODE(M2A_DBLINK,NULL,NULL_DBLINK,'@'||M2A_DBLINK)
INTO lv_dblink
FROM MSC_APPS_INSTANCES
WHERE INSTANCE_ID= p_instance_id;
lv_sql_str:=' SELECT mp.organization_id org_id'
||' from mtl_parameters'||lv_dblink||' mp,'
||' msc_instance_orgs mio'
||' where mio.sr_instance_id= :p_instance_id'
||' and mio.enabled_flag= 1'
||' and mio.organization_id=mp.organization_id'
||' and mp.process_enabled_flag='||'''Y''';
SELECT DECODE( M2A_DBLINK,
NULL, NULL_DBLINK,
'@'||M2A_DBLINK),
DECODE( M2A_DBLINK,
NULL, MSC_UTIL.SYS_NO,
MSC_UTIL.SYS_YES),
APPS_VER,
APPS_LRN,
APPS_LRN
INTO v_dblink,
v_distributed_config_flag,
lv_apps_ver,
v_lrnn,
v_so_lrn
FROM MSC_APPS_INSTANCES
WHERE INSTANCE_ID= pINSTANCE_ID;
'SELECT USERENV(''LANG''),'
||' FND_PROFILE.VALUE'||v_dblink||'(''BOM:HOUR_UOM_CODE''),'
||' DECODE( FND_PROFILE.VALUE'||v_dblink||'(''MRP_MPS_CONSUMPTION''),'
||' ''Y'', 1,'
||' ''1'', 1,'
||' 2),'
||' DECODE( FND_PROFILE.VALUE'||v_dblink||'(''MRP_SHIP_ARRIVE_FLAG''),'
||' ''Y'', 1,'
||' ''1'', 1,'
||' 2),'
||' DECODE( FND_PROFILE.VALUE'||v_dblink||'(''CRP_SPREAD_LOAD''),'
||' ''Y'', 1,'
||' ''1'', 1,'
||' 2),'
||' TO_NUMBER( FND_PROFILE.VALUE'||v_dblink||'(''MSO_ITEM_DMD_PENALTY'')),'
||' TO_NUMBER( FND_PROFILE.VALUE'||v_dblink||'(''MSO_ITEM_CAP_PENALTY'')),'
||' TO_NUMBER( FND_PROFILE.VALUE'||v_dblink||'(''MSO_ORG_DMD_PENALTY'')),'
||' TO_NUMBER( FND_PROFILE.VALUE'||v_dblink||'(''MSO_ORG_ITEM_PENALTY'')),'
||' TO_NUMBER( FND_PROFILE.VALUE'||v_dblink||'(''MSO_ORG_RES_PENALTY'')),'
||' TO_NUMBER( FND_PROFILE.VALUE'||v_dblink||'(''MSO_ORG_TRSP_PENALTY'')),'
||' FND_PROFILE.VALUE'||v_dblink||'(''MSC_AGGREG_RES_NAME''),'
||' TO_NUMBER( FND_PROFILE.VALUE'||v_dblink||'(''MSO_RES_PENALTY'')),'
||' TO_NUMBER( FND_PROFILE.VALUE'||v_dblink||'(''MSO_SUP_CAP_PENALTY'')),'
||' TO_NUMBER( FND_PROFILE.VALUE'||v_dblink||'(''MSC_BOM_SUBST_PRIORITY'')),'
||' TO_NUMBER( FND_PROFILE.VALUE'||v_dblink||'(''MSO_TRSP_PENALTY'')),'
||' TO_NUMBER( FND_PROFILE.VALUE'||v_dblink||'(''MSC_ALT_BOM_COST'')),'
||' TO_NUMBER( FND_PROFILE.VALUE'||v_dblink||'(''MSO_FCST_PENALTY'')),'
||' TO_NUMBER( FND_PROFILE.VALUE'||v_dblink||'(''MSO_SO_PENALTY'')),'
-- ||' TO_NUMBER( FND_PROFILE.VALUE'||v_dblink||'(''MSC_RESOURCE_TYPE'')),'
||' TO_NUMBER( FND_PROFILE.VALUE'||v_dblink||'(''MSC_ALT_RES_PRIORITY'')),'
||' TO_NUMBER( FND_PROFILE.VALUE'||v_dblink||'(''MSC_BATCHABLE_FLAG'')),'
||' TO_NUMBER( FND_PROFILE.VALUE'||v_dblink||'(''MSC_BATCHING_WINDOW'')),'
||' TO_NUMBER( FND_PROFILE.VALUE'||v_dblink||'(''MSC_MIN_CAPACITY'')),'
||' TO_NUMBER( FND_PROFILE.VALUE'||v_dblink||'(''MSC_MAX_CAPACITY'')),'
||' TO_NUMBER( FND_PROFILE.VALUE'||v_dblink||'(''MSC_UNIT_OF_MEASURE'')),'
||' TO_NUMBER( FND_PROFILE.VALUE'||v_dblink||'(''MSC_SIMUL_RES_SEQ'')),'
||' NVL(TO_NUMBER( FND_PROFILE.VALUE'||v_dblink||'(''MRP_BIS_AV_DISCOUNT'')),0),'
||' TO_NUMBER( FND_PROFILE.VALUE'||v_dblink||'(''MRP_BIS_PRICE_LIST'')),'
||' TO_NUMBER( FND_PROFILE.VALUE'||v_dblink||'(''MRP_DMD_PRIORITY_FLEX_NUM'')),'
--for bug13562886,use the MSC_UTIL.MSC_NUMVAL() to filter the wrong value set in the profile.
||' MSC_UTIL.MSC_NUMVAL( FND_PROFILE.VALUE'||v_dblink||'(''MSC_FCST_PRIORITY_FLEX_NUM''))'
--||' TO_NUMBER( FND_PROFILE.VALUE'||v_dblink||'(''MSC_FCST_PRIORITY_FLEX_NUM''))'
||' FROM DUAL';
'SELECT USERENV(''LANG''),'
||' FND_PROFILE.VALUE'||v_dblink||'(''BOM:HOUR_UOM_CODE''),'
||' DECODE( FND_PROFILE.VALUE'||v_dblink||'(''MRP_MPS_CONSUMPTION''),'
||' ''Y'', 1,'
||' ''1'', 1,'
||' 2),'
||' DECODE( FND_PROFILE.VALUE'||v_dblink||'(''MRP_SHIP_ARRIVE_FLAG''),'
||' ''Y'', 1,'
||' ''1'', 1,'
||' 2),'
||' DECODE( FND_PROFILE.VALUE'||v_dblink||'(''CRP_SPREAD_LOAD''),'
||' ''Y'', 1,'
||' ''1'', 1,'
||' 2),'
||' TO_NUMBER( FND_PROFILE.VALUE'||v_dblink||'(''MRP_ITEM_DMD_PENALTY'')),'
||' TO_NUMBER( FND_PROFILE.VALUE'||v_dblink||'(''MRP_ITEM_CAP_PENALTY'')),'
||' TO_NUMBER( FND_PROFILE.VALUE'||v_dblink||'(''MRP_ORG_DMD_PENALTY'')),'
||' TO_NUMBER( FND_PROFILE.VALUE'||v_dblink||'(''MRP_ORG_ITEM_PENALTY'')),'
||' TO_NUMBER( FND_PROFILE.VALUE'||v_dblink||'(''MRP_ORG_RES_PENALTY'')),'
||' TO_NUMBER( FND_PROFILE.VALUE'||v_dblink||'(''MRP_ORG_TRSP_PENALTY'')),'
||' TO_NUMBER( FND_PROFILE.VALUE'||v_dblink||'(''MRP_AGGREG_RES_NAME'')),'
||' TO_NUMBER( FND_PROFILE.VALUE'||v_dblink||'(''MRP_RES_PENALTY'')),'
||' TO_NUMBER( FND_PROFILE.VALUE'||v_dblink||'(''MRP_SUP_CAP_PENALTY'')),'
||' TO_NUMBER( FND_PROFILE.VALUE'||v_dblink||'(''MRP_BOM_SUBST_PRIORITY'')),'
||' TO_NUMBER( FND_PROFILE.VALUE'||v_dblink||'(''MRP_TRSP_PENALTY'')),'
||' TO_NUMBER( FND_PROFILE.VALUE'||v_dblink||'(''MRP_ALT_BOM_COST'')),'
||' TO_NUMBER( FND_PROFILE.VALUE'||v_dblink||'(''MRP_FCST_PENALTY'')),'
||' TO_NUMBER( FND_PROFILE.VALUE'||v_dblink||'(''MRP_SO_PENALTY'')),'
||' TO_NUMBER( FND_PROFILE.VALUE'||v_dblink||'(''MRP_RESOURCE_TYPE'')),'
||' TO_NUMBER( FND_PROFILE.VALUE'||v_dblink||'(''MRP_ALT_RES_PRIORITY'')),'
||' TO_NUMBER( FND_PROFILE.VALUE'||v_dblink||'(''MRP_BATCHABLE_FLAG'')),'
||' TO_NUMBER( FND_PROFILE.VALUE'||v_dblink||'(''MRP_BATCHING_WINDOW'')),'
||' TO_NUMBER( FND_PROFILE.VALUE'||v_dblink||'(''MRP_MIN_CAPACITY'')),'
||' TO_NUMBER( FND_PROFILE.VALUE'||v_dblink||'(''MRP_MAX_CAPACITY'')),'
||' TO_NUMBER( FND_PROFILE.VALUE'||v_dblink||'(''MRP_UNIT_OF_MEASURE'')),'
||' TO_NUMBER( FND_PROFILE.VALUE'||v_dblink||'(''MRP_SIMUL_RES_SEQ'')),'
||' NVL(TO_NUMBER( FND_PROFILE.VALUE'||v_dblink||'(''MRP_BIS_AV_DISCOUNT'')),0),'
||' TO_NUMBER( FND_PROFILE.VALUE'||v_dblink||'(''MRP_BIS_PRICE_LIST'')),'
||' TO_NUMBER( FND_PROFILE.VALUE'||v_dblink||'(''MRP_DMD_PRIORITY_FLEX_NUM'')),'
--for bug13562886,use the MSC_UTIL.MSC_NUMVAL() to filter the wrong value set in the profile.
--||' TO_NUMBER( FND_PROFILE.VALUE'||v_dblink||'(''MSC_FCST_PRIORITY_FLEX_NUM''))'
||' MSC_UTIL.MSC_NUMVAL( FND_PROFILE.VALUE'||v_dblink||'(''MSC_FCST_PRIORITY_FLEX_NUM''))'
||' FROM DUAL';
'SELECT OE_INSTALL.Get_Active_Product'||v_dblink||' FROM DUAL';
SELECT (SYSDATE- lv_start_time) INTO lv_process_time FROM dual;
SELECT org_group,delete_ods_data, supplier_capacity, atp_rules,
bom, bor, calendar_check, demand_class,ITEM_SUBSTITUTES, forecast, item,
decode(v_apps_ver, MSC_UTIL.G_APPS122, MSC_UTIL.SYS_NO, kpi_targets_bis), mds, mps, oh, parameter, planners,
projects, po, reservations, nra, safety_stock,
sales_order, sourcing_history, sourcing, sub_inventories,
customer, supplier, unit_numbers, uom, user_supply_demand, wip, user_comp_association,
po_receipts, bom_sn_flag, bor_sn_flag, item_sn_flag, oh_sn_flag,
usup_sn_flag, udmd_sn_flag, so_sn_flag, fcst_sn_flag,
wip_sn_flag, supcap_sn_flag, po_sn_flag, mds_sn_flag,
mps_sn_flag, nosnap_flag
/* CP-ACK starts */
,SUPPLIER_RESPONSE
/* CP-ACK ends */
/* CP-AUTO */
,SUPREP_SN_FLAG,trip,trip_sn_flag,ds_mode,sales_channel,fiscal_calendar,INTERNAL_REPAIR,EXTERNAL_REPAIR -- for bug 5909379
,payback_demand_supply, currency_conversion -- bug #6469722
,delivery_Details,ibuc_history,notes_attach,
eAM_info, eAM_forecasts, eam_fc_st_date, eam_fc_end_date, cmro, cmro_forecasts, cmro_fc_st_date, cmro_fc_end_date
,cmro_closed_wo,osp_supply
INTO prec.org_group_flag, prec.purge_ods_flag, prec.app_supp_cap_flag,
prec.atp_rules_flag, prec.bom_flag,
prec.bor_flag, prec.calendar_flag,
prec.demand_class_flag, prec.item_subst_flag,prec.forecast_flag,
prec.item_flag, prec.kpi_bis_flag,
prec.mds_flag, prec.mps_flag,
prec.oh_flag, prec.parameter_flag,
prec.planner_flag, prec.project_flag,
prec.po_flag, prec.reserves_flag,
prec.resource_nra_flag, prec.saf_stock_flag,
prec.sales_order_flag, prec.source_hist_flag,
prec.sourcing_rule_flag, prec.sub_inventory_flag,
prec.tp_customer_flag, prec.tp_vendor_flag,
prec.unit_number_flag, prec.uom_flag,
prec.user_supply_demand_flag, prec.wip_flag, prec.user_company_flag,
prec.po_receipts_flag,
prec.bom_sn_flag, prec.bor_sn_flag,
prec.item_sn_flag, prec.oh_sn_flag,
prec.usup_sn_flag, prec.udmd_sn_flag,
prec.so_sn_flag, prec.fcst_sn_flag,
prec.wip_sn_flag,
prec.supcap_sn_flag, prec.po_sn_flag,
prec.mds_sn_flag, prec.mps_sn_flag,
prec.nosnap_flag
/* CP-ACK starts */
,prec.supplier_response_flag
/* CP-ACK ends */
/* CP-AUTO */
,prec.suprep_sn_flag,prec.trip_flag,prec.trip_sn_flag ,
prec.ds_mode,
prec.sales_channel_flag,prec.fiscal_calendar_flag,prec.internal_repair_flag,prec.external_repair_flag -- for bug 5909379
,prec.payback_demand_supply_flag, prec.currency_conversion_flag -- bug # 6469722
,prec.delivery_details_flag,prec.ibuc_history_flag,prec.notes_attach_flag,
prec.eAM_info_flag, prec.eAM_forecasts_flag, prec.eam_fc_st_date, prec.eam_fc_end_date,
prec.cmro_flag, prec.cmro_forecasts_flag, prec.cmro_fc_st_date, prec.cmro_fc_end_date, prec.cmro_closed_wo,
prec.osp_supply
FROM msc_coll_parameters
WHERE instance_id = pINSTANCE_ID;
/* select the instance_type and database link */
BEGIN
SELECT DECODE( M2A_DBLINK,
NULL, NULL_DBLINK,
'@'||M2A_DBLINK),
DECODE( A2M_DBLINK,
NULL, NULL_DBLINK,
A2M_DBLINK),
INSTANCE_TYPE,
APPS_VER,
LAST_TGT_CONT_COLL_TIME,
INSTANCE_CODE
INTO v_dblink,
v_dest_a2m, -- bug fix for 2320600
v_instance_type,
v_apps_ver,
lv_last_tgt_cont_coll_time,
v_instance_code
FROM MSC_APPS_INSTANCES
WHERE INSTANCE_ID= pINSTANCE_ID;
lv_sql_stmt:= 'update msc_apps_instances set LBJ_DETAILS = (select decode(to_number(FND_PROFILE.VALUE' || v_dblink || '(''WSM_CREATE_LBJ_COPY_ROUTING'')),1,1,2) from dual' || ' ) '
|| ' where instance_id = ' || pINSTANCE_ID ;
lv_sql_stmt:= 'update msc_apps_instances set LBJ_DETAILS = 2 '
|| ' where instance_id = ' || pINSTANCE_ID ;
UPDATE msc_coll_parameters
SET COLLECTIONS_START_TIME = sysdate
WHERE INSTANCE_ID = pINSTANCE_ID;
'SELECT DECODE( mar.A2M_DBLINK,NULL,'''||NULL_DBLINK||''',mar.A2M_DBLINK),'
||' DECODE( mar.M2A_DBLINK,NULL,'''||NULL_DBLINK||''',''@''||mar.M2A_DBLINK)'
||' FROM '||lv_table_name||v_dblink||' mar'
||' WHERE mar.INSTANCE_ID = '||pINSTANCE_ID
||' AND mar.INSTANCE_CODE = '''||v_instance_code||''''
||' AND nvl(mar.A2M_DBLINK,'''||NULL_DBLINK||''') = '''||v_dest_a2m||'''';
SELECT FND_GLOBAL.USER_ID
INTO v_current_user
FROM MSC_APPS_INSTANCES
WHERE INSTANCE_ID= pINSTANCE_ID;
'SELECT PJM_UNIT_EFF.ENABLED'||v_dblink || ' FROM DUAL';
UPDATE msc_apps_instances
SET lrtype = DECODE ( pRTYPE, MSC_UTIL.G_COMPLETE, 'C',
MSC_UTIL.G_INCREMENTAL, 'I',
MSC_UTIL.G_PARTIAL, 'P',
MSC_UTIL.G_CONT, 'T'),
pjm_enabled = lv_pjm_enabled
WHERE instance_id = pINSTANCE_ID;
SELECT count(*)
INTO lv_param_rec_count
FROM msc_coll_parameters
WHERE instance_id = pINSTANCE_ID;
INSERT INTO msc_coll_parameters
(INSTANCE_ID,org_group,threshold, delete_ods_data, supplier_capacity, atp_rules,
bom, bor, calendar_check, demand_class, forecast, item,
kpi_targets_bis, mds, mps, oh, parameter, planners,
item_substitutes, projects, po, reservations, nra, safety_stock,
sales_order, sourcing_history, sourcing, sub_inventories,
customer, supplier, unit_numbers, uom, user_supply_demand, wip, user_comp_association,trip ,po_receipts,
supplier_response,sales_channel,fiscal_calendar,internal_repair,external_repair,LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, -- for bug 5909379
ds_mode, payback_demand_supply, currency_conversion,delivery_details,ibuc_history,notes_attach, -- bug # 6469722
eAM_info, eAM_forecasts, eam_fc_st_date, eam_fc_end_date, cmro, cmro_forecasts, cmro_fc_st_date, cmro_fc_end_date,cmro_closed_wo,
osp_supply)
VALUES (pINSTANCE_ID,prec.org_group_flag,v_cont_coll_thresh, prec.purge_ods_flag, prec.app_supp_cap_flag,
prec.atp_rules_flag, prec.bom_flag,
prec.bor_flag, prec.calendar_flag,
prec.demand_class_flag, prec.forecast_flag,
prec.item_flag, decode(v_apps_ver, MSC_UTIL.G_APPS122, MSC_UTIL.SYS_NO, prec.kpi_bis_flag),
prec.mds_flag, prec.mps_flag,
prec.oh_flag, prec.parameter_flag,
prec.planner_flag,prec.item_subst_flag, prec.project_flag,
prec.po_flag, prec.reserves_flag,
prec.resource_nra_flag, prec.saf_stock_flag,
prec.sales_order_flag, prec.source_hist_flag,
prec.sourcing_rule_flag, prec.sub_inventory_flag,
prec.tp_customer_flag, prec.tp_vendor_flag,
prec.unit_number_flag, prec.uom_flag,
prec.user_supply_demand_flag, prec.wip_flag, prec.user_company_flag,prec.trip_flag, -- DRP
prec.po_receipts_flag, prec.supplier_response_flag,prec.sales_channel_flag,prec.fiscal_calendar_flag,prec.internal_repair_flag,prec.external_repair_flag, sysdate, v_current_user, sysdate, v_current_user,
prec.ds_mode,
prec.payback_demand_supply_flag, prec.currency_conversion_flag, --bug # 6469722
prec.delivery_details_flag,prec.ibuc_history_flag,prec.notes_attach_flag ,
prec.eAM_info_flag, prec.eAM_forecasts_flag, prec.eam_fc_st_date, prec.eam_fc_end_date,
prec.cmro_flag, prec.cmro_forecasts_flag, prec.cmro_fc_st_date, prec.cmro_fc_end_date,prec.cmro_closed_wo,
prec.osp_supply );
SELECT st_status
INTO lv_coll_stat
FROM msc_apps_instances
WHERE instance_id = pINSTANCE_ID;
DELETE FROM msc_coll_parameters
WHERE instance_id = pINSTANCE_ID;
INSERT INTO msc_coll_parameters
(INSTANCE_ID,org_group,threshold, delete_ods_data, supplier_capacity, atp_rules,
bom, bor, calendar_check, demand_class, forecast, item,
kpi_targets_bis, mds, mps, oh, parameter, planners,
item_substitutes,projects, po, reservations, nra,
safety_stock, sales_order, sourcing_history, sourcing,
sub_inventories, customer, supplier, unit_numbers,
uom, user_supply_demand, wip, user_comp_association,trip,po_receipts,supplier_response,
sales_channel,fiscal_calendar,internal_repair , external_repair , -- for bug 5909379
LAST_UPDATE_DATE,LAST_UPDATED_BY, CREATION_DATE, CREATED_BY,
ds_mode,
payback_demand_supply, currency_conversion,delivery_details,ibuc_history,notes_attach, -- bug # 6469722
eAM_info, eAM_forecasts, eam_fc_st_date, eam_fc_end_date, cmro, cmro_forecasts, cmro_fc_st_date, cmro_fc_end_date,
cmro_closed_wo,osp_supply )
VALUES (pINSTANCE_ID,prec.org_group_flag,v_cont_coll_thresh, prec.purge_ods_flag,
prec.app_supp_cap_flag, prec.atp_rules_flag,
prec.bom_flag, prec.bor_flag, prec.calendar_flag,
prec.demand_class_flag, prec.forecast_flag,
prec.item_flag, decode(v_apps_ver, MSC_UTIL.G_APPS122, MSC_UTIL.SYS_NO,prec.kpi_bis_flag ),
prec.mds_flag, prec.mps_flag,
prec.oh_flag, prec.parameter_flag,
prec.planner_flag,prec.item_subst_flag, prec.project_flag,
prec.po_flag, prec.reserves_flag,
prec.resource_nra_flag, prec.saf_stock_flag,
prec.sales_order_flag, prec.source_hist_flag,
prec.sourcing_rule_flag, prec.sub_inventory_flag,
prec.tp_customer_flag, prec.tp_vendor_flag,
prec.unit_number_flag, prec.uom_flag,
prec.user_supply_demand_flag, prec.wip_flag, prec.user_company_flag,prec.trip_flag, -- DRP
prec.po_receipts_flag, prec.supplier_response_flag,
prec.sales_channel_flag,prec.fiscal_calendar_flag,prec.internal_repair_flag,prec.external_repair_flag, -- for bug 5909379
sysdate, v_current_user, sysdate, v_current_user,
prec.ds_mode,
prec.payback_demand_supply_flag,prec.currency_conversion_flag,--bug # 6469722
prec.delivery_details_flag,prec.ibuc_history_flag,prec.notes_attach_flag ,
prec.eAM_info_flag, prec.eAM_forecasts_flag, prec.eam_fc_st_date, prec.eam_fc_end_date,
prec.cmro_flag, prec.cmro_forecasts_flag, prec.cmro_fc_st_date, prec.cmro_fc_end_date,prec.cmro_closed_wo,
prec.osp_supply);
/* select the instance_type and database link */
/* --agmcont: move code below above, since we need dblink earlier
BEGIN
SELECT DECODE( M2A_DBLINK,
NULL, NULL_DBLINK,
'@'||M2A_DBLINK),
DECODE( A2M_DBLINK,
NULL, NULL_DBLINK,
A2M_DBLINK),
INSTANCE_TYPE
INTO v_dblink,
lv_dest_a2m, -- bug fix for 2320600
v_instance_type
FROM MSC_APPS_INSTANCES
WHERE INSTANCE_ID= pINSTANCE_ID;
'SELECT DECODE( mar.A2M_DBLINK,NULL,'''||NULL_DBLINK||''',mar.A2M_DBLINK),'
||' DECODE( mar.M2A_DBLINK,NULL,'''||NULL_DBLINK||''',''@''||mar.M2A_DBLINK)'
||' FROM MRP_AP_APPS_INSTANCES'||v_dblink||' mar';
UPDATE MSC_APPS_INSTANCES
SET SO_TBL_STATUS = decode(pSalesOrder_RTYPE,MSC_UTIL.SYS_YES
,decode(pRTYPE,MSC_UTIL.G_COMPLETE,decode(lv_inv_ctp_val,5,MSC_UTIL.SYS_YES,SO_TBL_STATUS),MSC_UTIL.G_PARTIAL,decode(lv_inv_ctp_val,5,MSC_UTIL.SYS_YES,SO_TBL_STATUS),SO_TBL_STATUS),
SO_TBL_STATUS)
WHERE INSTANCE_ID= pINSTANCE_ID;
'SELECT mar.LRN, mar.validation_org_id'
||' FROM '||lv_table_name||v_dblink||' mar'
||' WHERE mar.INSTANCE_ID = ' ||pINSTANCE_ID
||' AND mar.INSTANCE_CODE = '''||v_instance_code||''''
||' AND nvl(mar.A2M_DBLINK, '''||NULL_DBLINK||''' ) = '''||v_dest_a2m||'''';
UPDATE MSC_APPS_INSTANCES
SET VALIDATION_ORG_ID = v_validation_org_id
WHERE INSTANCE_ID = pINSTANCE_ID;
'SELECT mar.LRN '
||' FROM '||lv_table_name||v_dblink||' mar'
||' WHERE mar.INSTANCE_ID = ' ||pINSTANCE_ID
||' AND mar.INSTANCE_CODE = '''||v_instance_code||''''
||' AND nvl(mar.A2M_DBLINK, '''||NULL_DBLINK||''' ) = '''||v_dest_a2m||'''';
to be refreshed and this will be inserted into msc_coll_parameters*/
IF (prec.bom_flag = MSC_UTIL.SYS_YES) THEN
prec.bom_sn_flag := lv_bom_sn_flag;
UPDATE msc_coll_parameters
SET bom_sn_flag = prec.bom_sn_flag,
bor_sn_flag = prec.bor_sn_flag,
item_sn_flag = prec.item_sn_flag,
oh_sn_flag = prec.oh_sn_flag,
usup_sn_flag = prec.usup_sn_flag,
udmd_sn_flag = prec.udmd_sn_flag,
so_sn_flag = prec.so_sn_flag,
fcst_sn_flag = prec.fcst_sn_flag,
wip_sn_flag = prec.wip_sn_flag,
supcap_sn_flag = prec.supcap_sn_flag,
po_sn_flag = prec.po_sn_flag,
mds_sn_flag = prec.mds_sn_flag,
mps_sn_flag = prec.mps_sn_flag,
--supplier_response = prec.supplier_response_flag,
suprep_sn_flag = prec.suprep_sn_flag,
nosnap_flag = prec.nosnap_flag,
trip_sn_flag = prec.trip_sn_flag
WHERE INSTANCE_ID=pINSTANCE_ID;
UPDATE msc_apps_instances
SET SO_TBL_STATUS = decode (prec.so_sn_flag,MSC_UTIL.SYS_TGT,MSC_UTIL.SYS_NO,SO_TBL_STATUS)
WHERE INSTANCE_ID=pINSTANCE_ID;
UPDATE MSC_APPS_INSTANCES
SET SO_TBL_STATUS = decode(pSalesOrder_RTYPE,MSC_UTIL.SYS_YES
,decode(pRTYPE,MSC_UTIL.G_COMPLETE,decode(lv_inv_ctp_val,5,MSC_UTIL.SYS_YES,SO_TBL_STATUS),MSC_UTIL.G_PARTIAL,decode(lv_inv_ctp_val,5,MSC_UTIL.SYS_YES,SO_TBL_STATUS),SO_TBL_STATUS),
SO_TBL_STATUS)
WHERE INSTANCE_ID= pINSTANCE_ID;
'SELECT sysdate FROM DUAL'||v_dblink;
UPDATE msc_coll_parameters
SET PULL_WRKR_START_TIME = lv_src_time
WHERE INSTANCE_ID = pINSTANCE_ID;
SELECT (SYSDATE- lv_start_time) INTO lv_process_time FROM dual;
SELECT (SYSDATE- lv_start_time) INTO lv_process_time FROM dual;
UPDATE MSC_APPS_INSTANCES
SET SO_TBL_STATUS = decode(pSalesOrder_RTYPE,MSC_UTIL.SYS_YES
,decode(pRTYPE,MSC_UTIL.G_COMPLETE,decode(lv_inv_ctp_val,5,MSC_UTIL.SYS_YES,SO_TBL_STATUS),MSC_UTIL.G_PARTIAL,decode(lv_inv_ctp_val,5,MSC_UTIL.SYS_YES,SO_TBL_STATUS),SO_TBL_STATUS),
SO_TBL_STATUS)
WHERE INSTANCE_ID= pINSTANCE_ID;
UPDATE MSC_APPS_INSTANCES
SET SO_TBL_STATUS = decode(pSalesOrder_RTYPE,MSC_UTIL.SYS_YES
,decode(pRTYPE,MSC_UTIL.G_COMPLETE,decode(lv_inv_ctp_val,5,MSC_UTIL.SYS_YES,SO_TBL_STATUS),MSC_UTIL.G_PARTIAL,decode(lv_inv_ctp_val,5,MSC_UTIL.SYS_YES,SO_TBL_STATUS),SO_TBL_STATUS),
SO_TBL_STATUS)
WHERE INSTANCE_ID= pINSTANCE_ID;
UPDATE MSC_APPS_INSTANCES
SET SO_TBL_STATUS = decode(pSalesOrder_RTYPE,MSC_UTIL.SYS_YES
,decode(pRTYPE,MSC_UTIL.G_COMPLETE,decode(lv_inv_ctp_val,5,MSC_UTIL.SYS_YES,SO_TBL_STATUS),MSC_UTIL.G_PARTIAL,decode(lv_inv_ctp_val,5,MSC_UTIL.SYS_YES,SO_TBL_STATUS),SO_TBL_STATUS),
SO_TBL_STATUS)
WHERE INSTANCE_ID= pINSTANCE_ID;
PROCEDURE DELETE_PROCESS(
ERRBUF OUT NOCOPY VARCHAR2,
RETCODE OUT NOCOPY NUMBER,
pINSTANCE_ID IN NUMBER)
IS
BEGIN
INITIALIZE_PULL_GLOBALS( pINSTANCE_ID); -- Initializes Level 2 Global Variables used in Data Pull
END DELETE_PROCESS;
select apps_lrn
into lv_apps_lrn
from msc_apps_instances
where instance_id = p_instance_id;
select min(nvl(supplier_capacity_lrn,lv_apps_lrn)),
min(nvl(bom_lrn,lv_apps_lrn)),
min(nvl(bor_lrn,lv_apps_lrn)),
min(nvl(forecast_lrn,lv_apps_lrn)),
min(nvl(item_lrn,lv_apps_lrn)),
min(nvl(mds_lrn,lv_apps_lrn)),
min(nvl(mps_lrn,lv_apps_lrn)),
min(nvl(oh_lrn,lv_apps_lrn)),
min(nvl(reservations_lrn,lv_apps_lrn)),
min(nvl(LAST_SUCC_RES_REF_TIME, SYSDATE-365000)),
min(nvl(po_lrn,lv_apps_lrn)),
min(nvl(so_lrn,lv_apps_lrn)),
min(nvl(user_supply_demand_lrn,lv_apps_lrn)),
min(nvl(wip_lrn,lv_apps_lrn)),
min(nvl(nra_lrn,lv_apps_lrn)),
min(nvl(saf_stock_lrn,lv_apps_lrn)),
min(nvl(unit_no_lrn,lv_apps_lrn)),
min(nvl(uom_lrn,lv_apps_lrn)),
min(nvl(calendar_lrn,lv_apps_lrn)),
min(nvl(trip_lrn,lv_apps_lrn))
into
lv_sup_cap_lrn,
lv_bom_lrn,
lv_bor_lrn,
lv_forecast_lrn,
lv_item_lrn,
lv_mds_lrn,
lv_mps_lrn,
lv_oh_lrn,
lv_rsv_lrn,
g_LAST_SUCC_RES_REF_TIME,
lv_po_lrn,
lv_so_lrn,
lv_usd_lrn,
lv_wip_lrn,
lv_nra_lrn,
lv_saf_stock_lrn,
lv_unit_no_lrn,
lv_uom_lrn,
lv_calendar_lrn,
lv_trip_lrn
from msc_instance_orgs
WHERE ((p_org_group =MSC_UTIL.G_ALL_ORGANIZATIONS) or (org_group=p_org_group))
AND sr_instance_id = p_instance_id;
/* Earlier sales order is alwasy collected in netchange even if sales order is not selected for continuous collection
in srs */
select min(nvl(IRO_LRN,lv_apps_lrn)),
nvl(min(LAST_SUCC_IRO_REF_TIME), SYSDATE-365000)
into
lv_iro_lrn,
g_last_succ_iro_ref_time
from msc_instance_orgs
WHERE organization_type = 3
AND sr_instance_id = p_instance_id;
select min(nvl(ERO_LRN,lv_apps_lrn))
into
lv_ero_lrn
from msc_instance_orgs
WHERE organization_type <> 3
AND sr_instance_id = p_instance_id;
SELECT mai.ENABLE_FLAG,
mai.ST_STATUS,
DECODE(mai.LRTYPE,'C',MSC_UTIL.G_COMPLETE,'P',MSC_UTIL.G_PARTIAL,'I',MSC_UTIL.G_INCREMENTAL,'T',MSC_UTIL.G_CONT)
INTO lv_instance_enabled, lv_staging_table_status,lv_refresh_type
FROM MSC_APPS_INSTANCES mai
WHERE mai.INSTANCE_ID= pINSTANCE_ID
FOR UPDATE;
SELECT so_sn_flag
INTO lv_cont_coll_mode
FROM msc_coll_parameters
WHERE instance_id = pINSTANCE_ID;
UPDATE MSC_APPS_INSTANCES
SET ST_STATUS= MSC_UTIL.G_ST_PULLING,
SO_TBL_STATUS= DECODE( pSO_RTYPE,MSC_UTIL.SYS_YES
,decode(lv_refresh_type,MSC_UTIL.G_INCREMENTAL,MSC_UTIL.SYS_YES,
decode(lv_inv_ctp_val,5,decode(lv_refresh_type,
MSC_UTIL.G_CONT ,decode(lv_cont_coll_mode,
MSC_UTIL.SYS_INCR,MSC_UTIL.SYS_YES,
MSC_UTIL.SYS_NO,MSC_UTIL.SYS_YES,
MSC_UTIL.SYS_NO),
MSC_UTIL.SYS_NO),
MSC_UTIL.SYS_YES)
)
,MSC_UTIL.SYS_YES),
LAST_UPDATE_DATE= SYSDATE,
LAST_UPDATED_BY= FND_GLOBAL.USER_ID,
REQUEST_ID= FND_GLOBAL.CONC_REQUEST_ID
WHERE INSTANCE_ID= pINSTANCE_ID;
UPDATE MSC_APPS_INSTANCES
SET ST_STATUS= MSC_UTIL.G_ST_EMPTY,
LAST_UPDATE_DATE= SYSDATE,
LAST_UPDATED_BY= FND_GLOBAL.USER_ID,
REQUEST_ID= FND_GLOBAL.CONC_REQUEST_ID
WHERE INSTANCE_ID= pINSTANCE_ID;
UPDATE MSC_APPS_INSTANCES
SET ST_STATUS= MSC_UTIL.G_ST_READY,
LAST_UPDATE_DATE= SYSDATE,
LAST_UPDATED_BY= FND_GLOBAL.USER_ID,
REQUEST_ID= FND_GLOBAL.CONC_REQUEST_ID
WHERE INSTANCE_ID= pINSTANCE_ID;
Select MSC_CL_REFRESH_S.NEXTVAL
into pREFRESH_ID
from dual;
UPDATE MSC_APPS_INSTANCES
SET APPS_LRN= pLRN,
LRTYPE= DECODE( pRTYPE, MSC_UTIL.SYS_YES, 'C', 'I'),
SO_LRTYPE= DECODE( pSO_RTYPE, MSC_UTIL.SYS_YES, 'C', 'I'),
LRID= msc_cl_refresh_s.currval,
CLEANSED_FLAG= MSC_UTIL.SYS_NO,
LAST_UPDATE_DATE= SYSDATE,
LAST_UPDATED_BY= FND_GLOBAL.USER_ID,
REQUEST_ID= FND_GLOBAL.CONC_REQUEST_ID,
LR_SOURCING_FLAG = pSOURCING_ENABLED
WHERE INSTANCE_ID= pINSTANCE_ID;
UPDATE MSC_APPS_INSTANCES
SET APPS_LRN= pLRN,
LRTYPE= DECODE( pRTYPE, MSC_UTIL.G_COMPLETE, 'C',
MSC_UTIL.G_INCREMENTAL, 'I',
MSC_UTIL.G_PARTIAL, 'P',
MSC_UTIL.G_CONT, 'T'),
SO_LRTYPE= DECODE( pSO_RTYPE, MSC_UTIL.SYS_YES, DECODE(pRTYPE,MSC_UTIL.G_INCREMENTAL,'I', 'C'), 'I'),
LRID= msc_cl_refresh_s.currval,
CLEANSED_FLAG= MSC_UTIL.SYS_NO,
LAST_UPDATE_DATE= SYSDATE,
LAST_UPDATED_BY= FND_GLOBAL.USER_ID,
REQUEST_ID= FND_GLOBAL.CONC_REQUEST_ID,
LR_SOURCING_FLAG = pSOURCING_ENABLED
WHERE INSTANCE_ID= pINSTANCE_ID;
UPDATE MSC_INSTANCE_ORGS
SET ITEM_LRN = decode(prec.item_flag,1,pLRN,ITEM_LRN),
SAF_STOCK_LRN = decode(prec.saf_stock_flag, 1, pLRN, SAF_STOCK_LRN),
UNIT_NO_LRN = decode(prec.unit_number_flag,1,pLRN,UNIT_NO_LRN),
UOM_LRN = decode(prec.uom_flag,1,pLRN,UOM_LRN),
BOM_LRN = decode(prec.bom_flag,1,pLRN,BOM_LRN),
BOR_LRN = decode(prec.bor_flag ,1,pLRN,BOR_LRN ),
FORECAST_LRN = decode(prec.forecast_flag ,1,pLRN,FORECAST_LRN ),
MDS_LRN = decode(prec.mds_flag ,1,pLRN,MDS_LRN ),
MPS_LRN = decode(prec.mps_flag ,1,pLRN,MPS_LRN ),
OH_LRN = decode(prec.oh_flag ,1,pLRN,OH_LRN ),
PO_LRN = decode(prec.po_flag ,1,pLRN,PO_LRN ),
WIP_LRN = decode(prec.wip_flag ,1,pLRN,WIP_LRN ),
RESERVATIONS_LRN = decode(prec.reserves_flag ,1,pLRN,RESERVATIONS_LRN ),
USER_SUPPLY_DEMAND_LRN = decode(prec.user_supply_demand_flag ,1,pLRN,USER_SUPPLY_DEMAND_LRN ),
SUPPLIER_CAPACITY_LRN = decode(prec.app_supp_cap_flag ,1,pLRN, 3,pLRN,SUPPLIER_CAPACITY_LRN ),
TRIP_LRN = decode(prec.trip_flag ,1,pLRN,TRIP_LRN ),
LAST_SUCC_RES_REF_TIME = decode(prec.reserves_flag ,1,sysdate,g_LAST_SUCC_RES_REF_TIME),
ORG_LRN = pLRN
WHERE SR_INSTANCE_ID= pINSTANCE_ID
AND (pORG_GROUP= MSC_UTIL.G_ALL_ORGANIZATIONS OR ORG_GROUP=pORG_GROUP);
UPDATE MSC_INSTANCE_ORGS
SET ERO_LRN = decode(prec.external_repair_flag ,1,pLRN,ERO_LRN )
WHERE SR_INSTANCE_ID= pINSTANCE_ID
AND organization_type <> 3;
UPDATE MSC_INSTANCE_ORGS
SET
IRO_LRN = decode(prec.internal_repair_flag ,1,pLRN,IRO_LRN ),
LAST_SUCC_IRO_REF_TIME = sysdate
WHERE SR_INSTANCE_ID= pINSTANCE_ID
AND organization_type = 3;
UPDATE MSC_INSTANCE_ORGS
SET CALENDAR_LRN= pLRN
WHERE SR_INSTANCE_ID= pINSTANCE_ID
AND (pORG_GROUP= MSC_UTIL.G_ALL_ORGANIZATIONS OR ORG_GROUP=pORG_GROUP);
/* Sales orders are not collected during Targeted and Continious Refresh if Sales Orders = No, so we will not update SO_LRN in this case */
IF ( (pRTYPE = MSC_UTIL.G_PARTIAL OR pRTYPE = MSC_UTIL.G_CONT) AND prec.sales_order_flag = MSC_UTIL.SYS_NO ) THEN
NULL;
UPDATE MSC_INSTANCE_ORGS
SET SO_LRN= pLRN
WHERE SR_INSTANCE_ID= pINSTANCE_ID
AND (pORG_GROUP= MSC_UTIL.G_ALL_ORGANIZATIONS OR ORG_GROUP=pORG_GROUP);
select decode(NRA_ENABLED,1,1,2,0,3,1,0)
into lv_nra_enabled_r11
from dual;
SELECT DECODE( M2A_DBLINK,
NULL, ' ',
'@'||M2A_DBLINK),
INSTANCE_CODE||':',
APPS_VER,
GMT_DIFFERENCE/24.0,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
INSTANCE_TYPE
INTO v_dblink,
v_icode,
v_apps_ver,
v_dgmt,
lv_task_start_time,
v_current_user,
v_current_date,
v_instance_type
FROM MSC_APPS_INSTANCES
WHERE INSTANCE_ID= pIID;
SELECT decode(NVL(FND_PROFILE.VALUE('MSC_COLLECT_COMPLETED_JOBS'),'Y'),
'Y', 1,
2)
INTO v_collect_completed_jobs
FROM DUAL;
SELECT DECODE(LRTYPE, 'P', MSC_UTIL.SYS_YES, MSC_UTIL.SYS_NO)
INTO v_is_partial_refresh
FROM msc_apps_instances
WHERE instance_id = pIID;
SELECT DECODE(LRTYPE,'C',MSC_UTIL.G_COMPLETE,'P',MSC_UTIL.G_PARTIAL,'I',MSC_UTIL.G_INCREMENTAL,'T',MSC_UTIL.G_CONT)
INTO
lv_refresh_type
FROM msc_apps_instances
WHERE instance_id = pIID;
SELECT DECODE(LRTYPE, 'T', MSC_UTIL.SYS_YES, MSC_UTIL.SYS_NO)
INTO v_is_cont_refresh
FROM msc_apps_instances
WHERE instance_id = pIID;
MSC_CL_ITEM_PULL.INSERT_DUMMY_ITEMS;
/* check sales order is selected */
ELSIF prec.sales_order_flag = MSC_UTIL.SYS_YES AND pTASKNUM= TASK_SALES_ORDER1 THEN
IF ( v_apps_ver>= MSC_UTIL.G_APPS115) and (v_discrete_flag = MSC_UTIL.SYS_YES) THEN -- 11i source instance only
-- only call if doing discrete mfg
FND_MESSAGE.SET_NAME('MSC', 'MSC_DP_TASK_START');
FND_MESSAGE.SET_TOKEN('PROCEDURE', 'MSC_CL_ITEM_PULL.INSERT_DUMMY_CATEGORIES');
MSC_CL_ITEM_PULL.INSERT_DUMMY_CATEGORIES ;
SELECT table_name
FROM all_tables
WHERE owner=p_owner
AND table_name like 'MSC_ST_%'
AND temporary <> 'Y';
SELECT a.oracle_username
FROM FND_ORACLE_USERID a, FND_PRODUCT_INSTALLATIONS b
WHERE a.oracle_id = b.oracle_id
and b.application_id= 724;