[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
|| 'last_update_date ,REPAIR_MODE ) TABLESPACE '
||lv_index_tablespace;
lv_sql_stmt:= 'select NVL(FND_PROFILE.VALUE'||v_a2m_dblink||'(''MSC_SRP_ENABLED''),''N'')'
|| ' from dual ';
/* UPDATE THE PROFILE OPTION MSC_SOURCE_SETUP TO NO */
IF lv_success AND ( MRP_CL_FUNCTION.MAP_REGION_TO_SITE(null) = 1) THEN
begin
MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Updating Profile Option MSC_SOURCE_SETUP to No ');
UPDATE FND_PROFILE_OPTION_VALUES
SET PROFILE_OPTION_VALUE = 'N'
WHERE PROFILE_OPTION_ID = (SELECT PROFILE_OPTION_ID
FROM FND_PROFILE_OPTIONS
WHERE PROFILE_OPTION_NAME = 'MSC_SOURCE_SETUP');
MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Profile Option MSC_SOURCE_SETUP has been updated No ');
SELECT APPLICATION_ID
INTO l_application_id
FROM FND_APPLICATION_VL
WHERE APPLICATION_NAME = lv_application_name;
SELECT USER_ID
INTO l_user_id
FROM FND_USER
WHERE USER_NAME = lv_user_name;
SELECT table_name
FROM ALL_TABLES
WHERE TABLE_NAME like 'MRP_AD%'
AND owner = lv_owner;
SELECT LOG_TABLE
INTO lv_mlog_tab_name
FROM ALL_SNAPSHOT_LOGS
WHERE MASTER = upper(pTABLE_NAME)
AND LOG_OWNER = upper(lv_base_schema)
AND ROWNUM = 1;
lv_sel_snp_stmt := ' select count(*) from '||MSC_UTIL.G_APPS_SCHEMA|| '.' ||pSNAP_NAME
|| ' where rownum < 2 ';
lv_sel_sql_stmt := ' select count(*) from '||lv_base_schema|| '.'||lv_mlog_tab_name
|| ' where rownum <= :p1 ';
' (select wip_entity_id ' ||
' from wip_discrete_jobs ' ||
' where ' ||
' status_type in (1,3,4,6) '||
' and wip_entity_id = WIP_REQUIREMENT_OPERATIONS.wip_entity_id ) ' ;
' (select wip_entity_id ' ||
' from wip_discrete_jobs ' ||
' where ' ||
' status_type in (1,3,4,6) '||
' and wip_entity_id = WIP_OPERATIONS.wip_entity_id ) ' ;
' (select wip_entity_id ' ||
' from wip_discrete_jobs ' ||
' where ' ||
' status_type in (1,3,4,6) '||
' and wip_entity_id = WIP_OPERATION_RESOURCES.wip_entity_id ) ' ;
' SELECT LOG_TABLE FROM ALL_SNAPSHOT_LOGS '
|| ' WHERE MASTER = :lv_master_tbl AND '
|| ' LOG_OWNER = :p_schema AND '
|| ' ROWNUM = 1'
INTO lv_mlog_tab_name
USING upper(lv_master_tbl), upper(lv_base_schema);
' SELECT count(*) FROM ' || lv_master_tbl || lv_where_clause
INTO lv_tab_count;
' SELECT count(1) FROM '||MSC_UTIL.G_APPS_SCHEMA||'.'||p_snapshot
INTO lv_snap_count;
' SELECT count(*) FROM ' || lv_base_schema || '.' || lv_mlog_tab_name || ' WHERE nvl(snaptime$$, sysdate+1) > sysdate '
INTO lv_log_count;
SELECT DECODE( A2M_DBLINK,
NULL, NULL_DBLINK,
'@'||A2M_DBLINK)
INTO v_dblink
FROM MRP_AP_APPS_INSTANCES_ALL
WHERE INSTANCE_ID = g_INSTANCE_ID
AND INSTANCE_CODE= g_INSTANCE_CODE
AND nvl(A2M_DBLINK,NULL_DBLINK) = nvl(g_A2M_DBLINK,NULL_DBLINK) ;
lv_sql_stmt := ' SELECT item,ITEM_SUBSTITUTES,supplier, customer, bom, '
||' reservations, sourcing, wip,internal_repair,external_repair, safety_stock, '
||' po, oh, supplier_capacity, supplier_response, uom, mds, '
||' forecast, mps, sales_order,USER_SUPPLY_DEMAND,trip '
||' FROM msc_coll_parameters'||v_dblink
||' WHERE instance_id = '||g_INSTANCE_ID;
execute immediate ' select ' || exp || ' from dual ' into val;
MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'No Snapshots selected');
lv_cusros_str := ' select mview_name, existance_check
from msc_coll_snapshots_v
where mview_name in ' || lv_snapshot_groups_str || '
or snapshot_group_string in ' || lv_snapshot_groups_str ;
select APPLICATION_COLUMN_NAME
from FND_ID_FLEX_SEGMENTS
where ID_FLEX_CODE = 'MSTK'
and ENABLED_FLAG = 'Y'
and DISPLAY_FLAG = 'Y'
and APPLICATION_ID = 401
and ID_FLEX_NUM = 101
order by SEGMENT_NUM;
SELECT MRP_AP_REFRESH_S.NEXTVAL
INTO v_refresh_number
FROM DUAL;
SELECT max(LRD)
INTO max_lrd
FROM MRP_AP_APPS_INSTANCES_ALL;
SELECT LRN, DECODE( A2M_DBLINK,
NULL, NULL_DBLINK,
'@'||A2M_DBLINK)
INTO v_old_lrn, v_dblink
FROM MRP_AP_APPS_INSTANCES_ALL
WHERE INSTANCE_ID = pINSTANCE_ID
AND INSTANCE_CODE= pINSTANCE_CODE
AND nvl(A2M_DBLINK,NULL_DBLINK) = nvl(pA2M_DBLINK,NULL_DBLINK) ;
lv_sql_stmt := ' SELECT apps_lrn '
||' FROM msc_apps_instances'||v_dblink
||' WHERE instance_id = '||pINSTANCE_ID;
lv_sql_stmt := ' SELECT nvl(min(so_lrn),'||to_char(v_apps_lrn)||')'
||' FROM msc_instance_orgs'||v_dblink
||' WHERE sr_instance_id = '||pINSTANCE_ID;
SELECT DECODE(NVL(fnd_profile.value('MSC_SOURCE_SETUP') ,'Y'), 'Y',1 ,2)
INTO lv_setup_source_objs
FROM DUAL;
' select 1'
||' from mrp_sn_supply'
||' where transaction_id= 0'
||' and rownum=1';
lv_sql_stmt := ' SELECT sales_order '
||' , wip, wip_sn_flag '
||' FROM msc_coll_parameters'||v_dblink
||' WHERE instance_id = '||pINSTANCE_ID ;
SELECT application_short_name
INTO lv_flm_appl_short_name
FROM fnd_application
WHERE application_id=714;
select '||'''||CONCATENATED_SEGMENT_DELIMITER||'''||'
into delimiter
from fnd_id_flex_structures
where ID_FLEX_CODE = 'MSTK'
and APPLICATION_ID = 401
and ID_FLEX_NUM = 101;
lv_sql_stmt := 'UPDATE MRP_AP_APPS_INSTANCES_ALL '
||' SET LRN= MRP_AP_REFRESH_S.CURRVAL, '
-- Resource Start Time. This time will be updated before the snapshot refresh.
-- LRD= SYSDATE,
||' LAST_UPDATE_DATE= SYSDATE,'
||' LAST_UPDATED_BY= FND_GLOBAL.USER_ID,'
||' BOM_HOUR_UOM_CODE =FND_PROFILE.VALUE(''BOM:HOUR_UOM_CODE''),'
||' MRP_MPS_CONSUMPTION =DECODE( FND_PROFILE.VALUE(''MRP_MPS_CONSUMPTION''),'
||' ''Y'', 1,'
||' ''1'', 1,'
||' 2),'
||' MRP_SHIP_ARRIVE_FLAG =DECODE( FND_PROFILE.VALUE(''MRP_SHIP_ARRIVE_FLAG''),'
||' ''Y'', 1,'
||' ''1'', 1,'
||' 2),'
||' CRP_SPREAD_LOAD =DECODE( FND_PROFILE.VALUE(''CRP_SPREAD_LOAD''),'
||' ''Y'', 1,'
||' ''1'', 1,'
||' 2),'
||' MSO_ITEM_DMD_PENALTY =TO_NUMBER( FND_PROFILE.VALUE(''MSO_ITEM_DMD_PENALTY'')),'
||' MSO_ITEM_CAP_PENALTY =TO_NUMBER( FND_PROFILE.VALUE(''MSO_ITEM_CAP_PENALTY'')),'
||' MSO_ORG_DMD_PENALTY =TO_NUMBER( FND_PROFILE.VALUE(''MSO_ORG_DMD_PENALTY'')),'
||' MSO_ORG_ITEM_PENALTY =TO_NUMBER( FND_PROFILE.VALUE(''MSO_ORG_ITEM_PENALTY'')),'
||' MSO_ORG_RES_PENALTY =TO_NUMBER( FND_PROFILE.VALUE(''MSO_ORG_RES_PENALTY'')),'
||' MSO_ORG_TRSP_PENALTY =TO_NUMBER( FND_PROFILE.VALUE(''MSO_ORG_TRSP_PENALTY'')),'
||' MSC_AGGREG_RES_NAME =TO_NUMBER( FND_PROFILE.VALUE(''MSC_AGGREG_RES_NAME'')),'
||' MSO_RES_PENALTY =TO_NUMBER( FND_PROFILE.VALUE(''MSO_RES_PENALTY'')),'
||' MSO_SUP_CAP_PENALTY =TO_NUMBER( FND_PROFILE.VALUE(''MSO_SUP_CAP_PENALTY'')),'
||' MSC_BOM_SUBST_PRIORITY =TO_NUMBER( FND_PROFILE.VALUE(''MSC_BOM_SUBST_PRIORITY'')),'
||' MSO_TRSP_PENALTY =TO_NUMBER( FND_PROFILE.VALUE(''MSO_TRSP_PENALTY'')),'
||' MSC_ALT_BOM_COST =TO_NUMBER( FND_PROFILE.VALUE(''MSC_ALT_BOM_COST'')),'
||' MSO_FCST_PENALTY =TO_NUMBER( FND_PROFILE.VALUE(''MSO_FCST_PENALTY'')),'
||' MSO_SO_PENALTY =TO_NUMBER( FND_PROFILE.VALUE(''MSO_SO_PENALTY'')),'
-- MSC_ALT_OP_RES =TO_NUMBER( FND_PROFILE.VALUE('MSC_RESOURCE_TYPE')),
||' MSC_ALT_RES_PRIORITY =TO_NUMBER( FND_PROFILE.VALUE(''MSC_ALT_RES_PRIORITY'')),'
||' MSC_SIMUL_RES_SEQ =TO_NUMBER( FND_PROFILE.VALUE(''MSC_SIMUL_RES_SEQ'')),'
||' MRP_BIS_AV_DISCOUNT =NVL(TO_NUMBER(FND_PROFILE.VALUE(''MRP_BIS_AV_DISCOUNT'')),0),'
||' MRP_BIS_PRICE_LIST =TO_NUMBER( FND_PROFILE.VALUE(''MRP_BIS_PRICE_LIST'')),'
||' MSC_DMD_PRIORITY_FLEX_NUM=NVL(TO_NUMBER( FND_PROFILE.VALUE(''MSC_DMD_PRIORITY_FLEX_NUM'')),0),'
||' MSC_BATCHABLE_FLAG =TO_NUMBER( FND_PROFILE.VALUE(''MSC_BATCHABLE_FLAG'')),'
||' MSC_BATCHING_WINDOW =TO_NUMBER( FND_PROFILE.VALUE(''MSC_BATCHING_WINDOW'')),'
||' MSC_MIN_CAPACITY =TO_NUMBER( FND_PROFILE.VALUE(''MSC_MIN_CAPACITY'')),'
||' MSC_MAX_CAPACITY =TO_NUMBER( FND_PROFILE.VALUE(''MSC_MAX_CAPACITY'')),'
||' MSC_UNIT_OF_MEASURE =TO_NUMBER( FND_PROFILE.VALUE(''MSC_UNIT_OF_MEASURE'')),'
||' VALIDATION_ORG_ID =NVL(TO_NUMBER( FND_PROFILE.VALUE(''MSC_ORG_FOR_BOM_EXPLOSION'')),to_number(null)),'
||' MSC_SO_OFFSET_DAYS =TO_NUMBER( NVL(FND_PROFILE.VALUE'||v_dblink||'(''MSC_SO_OFFSET_DAYS''),99999)),'
||' ITEM_NAME_FROM_KFV = :lv_item_name_kfv '
||' WHERE INSTANCE_ID = :pINSTANCE_ID'
||' AND INSTANCE_CODE= :pINSTANCE_CODE'
||' AND nvl(A2M_DBLINK,'||''''||NULL_DBLINK ||''''||') = nvl(:pA2M_DBLINK,'||''''||NULL_DBLINK||''''||') ';
' select 1'
||' from mrp_sn_supply'
||' where transaction_id= 0'
||' and rownum=1'
INTO lv_initialization_flag;
SELECT MRP_AP_REFRESH_S.NEXTVAL
INTO v_refresh_number
FROM DUAL;
SELECT master_table, erp_product_code
INTO lv_base_table_name,lv_erp_product_code
FROM MSC_COLL_SNAPSHOTS_V
WHERE mview_name = lv_snapshot_name;
EXECUTE IMMEDIATE ' SELECT DECODE(last_refresh_type,''COMPLETE'',''C'', ''F'') '
|| ' FROM all_mviews WHERE mview_name = :lv_snapshot_name '
|| ' AND owner = :lv_snap_schema '
INTO lv_refresh_param -- overwrite existing value with the actual refresh done...
USING lv_snapshot_name, MSC_UTIL.G_APPS_SCHEMA;
UPDATE MSC_COLL_SNAPSHOTS_V
SET complete_refresh_timestamp = to_char(sysdate,'YYYY-MM-DD HH:MI:SS')
WHERE MVIEW_NAME = lv_snapshot_name ;
MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'successfully updated the complete refresh time in fnd_lookup_values');
MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'No Snapshots selected');
lv_cusror_str := ' select mview_name, existance_check
from msc_coll_snapshots_v
where mview_name in ' || lv_snapshot_groups_str || '
or snapshot_group_string in ' || lv_snapshot_groups_str ;
UPDATE FND_PROFILE_OPTION_VALUES
SET PROFILE_OPTION_VALUE = 'Y'
WHERE PROFILE_OPTION_ID = (SELECT PROFILE_OPTION_ID
FROM FND_PROFILE_OPTIONS
WHERE PROFILE_OPTION_NAME = 'MSC_SOURCE_SETUP');
MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Profile Option MSC_SOURCE_SETUP has been updated Yes ');
select 1
into lv_MV_complete_refresh
from msc_coll_snapshots_v
where mview_name = p_MV_name
and existance_check = MSC_UTIL.SYS_YES
and nvl(complete_refresh_timestamp,p_last_tgt_cont_coll_time) > p_last_tgt_cont_coll_time;
v_sql_stmt := 'select count(*)
from ' || p_ad_table_name || '
where organization_id ' || p_org_str ;
v_sql_stmt := 'select count(*)
from ' || p_MV_name || '
where rn > ' || p_entity_lrn || '
and organization_id ' || p_org_str ;
v_sql_stmt := 'select count(*)
from ' || p_MV_name || '
where rn <= ' || p_entity_lrn || '
and organization_id ' || p_org_str || '
and rownum < :num_thr' ;
select 1
from msc_coll_snapshots_v
where snapshot_group_string = ''' || p_entity_name || '''
and existance_check = ' || MSC_UTIL.SYS_YES || '
and check_for_cont_refresh = ' || MSC_UTIL.SYS_YES || '
and nvl(complete_refresh_timestamp,:vDate) > :vDate
and rownum < 2'
into lv_MV_complete_refresh
using p_last_tgt_cont_coll_time, p_last_tgt_cont_coll_time; -- tobe enhanced
lv_cusros_str := ' select mview_name, ad_table_name, existance_check
from msc_coll_snapshots_v
where check_for_cont_refresh = ' || MSC_UTIL.SYS_YES || '
and snapshot_group_string = ''' || p_entity_name || '''' ;
this need to be updated with previous result. */
IF entity_flag = MSC_UTIL.SYS_NO AND lv_entity_incr = msc_util.sys_yes THEN
entity_flag := MSC_UTIL.SYS_INCR;