[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
lv_update_cr_stmt varchar2(2000);
|| '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 THEN
-- AND ( MRP_CL_FUNCTION.MAP_REGION_TO_SITE(null) = 1) THEN --9396359
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,notes_attach,cmro,eam_info,cmro_closed_wo '
||' FROM msc_coll_parameters'||v_dblink
||' WHERE instance_id = '||g_INSTANCE_ID;
execute immediate ' select ' || exp || ' from dual ' into val;
lv_sql_stmt:= 'select NVL(FND_PROFILE.VALUE'||v_a2m_dblink
||'(''MSC_ASCP_IGNORE_CMRO_EAM_WO''),1)'
|| ' from dual ';
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,last_ibuc_coll_date '
||' FROM msc_apps_instances'||v_dblink
||' WHERE instance_id = '||pINSTANCE_ID;
lv_sql_stmt:= 'select FND_PROFILE.VALUE'||v_dblink||'(''MSC_COLL_TIME_WINDOW_IBUC_HISTORY'') '
|| ' from dual ';
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';
MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Before update of mrp_ap_apps_instances_all');
UPDATE MRP_AP_APPS_INSTANCES_ALL SET LRD = SYSDATE
WHERE INSTANCE_ID = pINSTANCE_ID
AND INSTANCE_CODE= pINSTANCE_CODE
AND nvl(A2M_DBLINK,NULL_DBLINK) = nvl(pA2M_DBLINK,NULL_DBLINK) ;
lv_sql_stmt2 := ' SELECT ibuc_history '
||' FROM msc_coll_parameters'||v_dblink
||' WHERE instance_id = '||pINSTANCE_ID ;
SELECT MRP_AP_REFRESH_S.NEXTVAL
INTO v_refresh_number
FROM DUAL;
lv_update_cr_stmt:= 'UPDATE MSC_COLL_SNAPSHOTS_V '
|| ' SET complete_refresh_timestamp = to_char(sysdate,''YYYY-MM-DD HH24:MI:SS'') '
|| ' WHERE MVIEW_NAME in ( '
|| ' SELECT mview_name from all_mviews WHERE mview_name in (' || lv_snapshot_str_tmp || ') '
|| ' and last_refresh_type= ''COMPLETE'' AND owner = '
|| '''' || MSC_UTIL.G_APPS_SCHEMA || '''' || ')' ;
EXECUTE IMMEDIATE lv_update_cr_stmt;
MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Done with update complete refresh MViews timestamp');
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 Nvl(fnd_profile.value('MSC_REFRESH_REGION_SITE'),1)
INTO lv_map_region_during_coll
FROM dual;
SELECT DECODE( A2M_DBLINK,
NULL, NULL_DBLINK,
'@'||A2M_DBLINK)
INTO 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) ;
We are selecting notes column as well from msc_coll_parameters ***/
lv_sql_stmt := ' SELECT nvl(sourcing,0) ,notes_attach '
||' FROM msc_coll_parameters'||v_dblink
||' WHERE instance_id = '||pINSTANCE_ID ;
SELECT max(last_update_date)
INTO max_lrd
FROM MRP_REGION_SITES;
=====If collections is run distributed mode, data is inserted in to the destination staging table MSC_ST_LONG_TEXT,
from the table MSC_ST_LONG_TEXT on the source=====
IF ( pCALLING_MODULE = G_COLLECTIONS
AND ( pREFRESH_TYPE='C' OR pREFRESH_TYPE='P')
AND lv_notes_attach = 1
AND CHECK_DB_LINK =1 ) THEN
lv_temp_stmt:=
'BEGIN'
||' :lv_out := MRP_CL_FUNCTION.LONG_TEXT '||';'
lv_sql_stmt := ' SELECT cmro '
||' FROM msc_coll_parameters'||v_dblink
||' WHERE instance_id = :v_INSTANCE_ID';
' 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 MRP_AP_REFRESH_S.NEXTVAL
INTO lv_ref_num
FROM DUAL;
' select 1'
||' from mrp_sn_supply'
||' where transaction_id= 0'
||' and rownum=1'
INTO lv_initialization_flag;
ELSIF (pREFRESH_MODE = G_REFRESH_MODE_FORCE) THEN --Force ,code insert for bug 14006179
MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'single refresh, Force');
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;
select LAST_REFRESH_TYPE into lv_last_refresh_type from dba_Mviews where owner||'.'||mview_name=lv_snap_str;
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 ');
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 , existance_check
into lv_MV_complete_refresh, lv_existance_check
from msc_coll_snapshots_v
where mview_name = p_MV_name
and nvl(to_date(complete_refresh_timestamp,'YYYY-MM-DD HH:MI:SS'),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 item_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 ;
v_sql_stmt := 'select count(*)
from ' || p_MV_name || '
where item_rn <= ' || p_entity_lrn || '
and organization_id ' || p_org_str || '
and rownum <= :num_thr' ;
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 = to_char(' || MSC_UTIL.SYS_YES || ')
and check_for_cont_refresh = ' || MSC_UTIL.SYS_YES || '
and to_date(nvl(complete_refresh_timestamp,to_char(:vDate,''YYYY-MM-DD HH:MI:SS'')),''YYYY-MM-DD HH:MI:SS'') > :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;