[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Procedure insert_seed_data is
Cursor tcur is
select count(*)
from msc_activity_parameters
where param_version >= p_version;
select lookup_code, meaning
from mfg_lookups
where lookup_type='MSC_PROCESS_ACTIVITY_TYPES'
LOOKUP_CODE MEANING
----------- -----------------------------------
1 Run Supply Chain Plan
10 Run Demantra Collections and Download
11 Run ASCP Collections
12 Generate Forecast
13 Review Forecast
14 Review Supply Chain Plan
15 Review Financial Plan
16 Review Marketing Plan
17 Review Demand Plan
18 Review Sales Plan
19 Upload Forecast
20 Review Supply Network Plan
21 Approve Consensus Demand
22 Executive Review
4 Run Inventory Plan
6 Run Supply Network Plan
msc_activity_parameters
ACTIVITY_TYPE NOT NULL NUMBER
NAME NOT NULL VARCHAR2(30)
DATA_TYPE VARCHAR2(30)
DEFAULT_VALUE VARCHAR2(30)
SEQUENCE NUMBER
SQL VARCHAR2(2000)
CREATED_BY NUMBER
CREATION_DATE DATE
LAST_UPDATE_DATE DATE
LAST_UPDATED_BY NUMBER
LAST_UPDATE_LOGIN NUMBER
LOOKUP_TYPE VARCHAR2(30)
REQUIRED VARCHAR2(10)
DISPLAYED VARCHAR2(10)
DISPLAY_NAME, PARAM_VERSION VARCHAR2(64)
ENABLED NUMBER -- 1, YES, 2/NULL - NO
COMPONENT_STYLE NUMBER -- 1- Message text input
-- 2- LOV
-- 3- Date
PARAM_VERSION NUMBER
*********************************************************/
open tcur;
delete from msc_activity_parameters;
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(1,'PLAN_ID','NUMBER',NULL,1,
'',
1,SYSDATE,'Y','N','Plan Name',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(1,'LAUNCH_SNAPSHOT','NUMBER','FULL',2,
'select decode(lookup_code, 1, ''FULL'',2,''NO'',3,''DP_ONLY'') hidden, meaning display '||
'from mfg_lookups '||
'where lookup_type = ''MSC_LAUNCH_SNAPSHOT''',
1,SYSDATE,'Y','Y','Launch Snapshot',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(1,'LAUNCH_PLANNER','NUMBER','Y',3,
'select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display '||
'from mfg_lookups '||
'where lookup_type = ''SYS_YES_NO''',
1,SYSDATE,'Y','Y','Launch Planner',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(1,'ANCHOR_DATE','DATE',SYSDATE,4,
'',
1,SYSDATE,'Y','Y','Anchor Date',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(1,'ARCHIVE_FLAG','VARCHAR2','N',5,
'select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1,sysdate,'Y','Y','Archive Plan Summary',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(1,'ENABLE_24X7ATP','NUMBER',NULL,6,
'select decode(lookup_code, 1, ''YES_PURGE'',2,''NO'',3,''YES_NO_PURGE'') hidden, meaning display '||
'from mfg_lookups '||
'where lookup_type = ''MSC_24X7_PURGE''',
1,SYSDATE,'Y','Y','Enable 24x7ATP',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(1,'RELEASE_RESCHEDULES','NUMBER','N',7,
'select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display '||
'from mfg_lookups '||
'where lookup_type = ''SYS_YES_NO''',
1,SYSDATE,'Y','Y','Release Reschedules',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(1,'SNAP_STATIC_ENTITIES','NUMBER','Y',8,
'select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display '||
'from mfg_lookups '||
'where lookup_type = ''SYS_YES_NO''',
1,SYSDATE,'Y','Y','Snapshot Static Entities',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(6,'PLAN_ID','VARCHAR2',NULL,1,
' SELECT plans.compile_designator hidden,'||
' plans.compile_designator display'||
' FROM msc_plans plans, msc_designators desig'||
' WHERE plans.organization_id = NVL(fnd_profile.value(''SCENARIO_PLANNING_ORG''),plans.organization_id)'||
' AND plans.sr_instance_id = NVL(fnd_profile.value(''SCENARIO_PLANNING_INST''),plans.sr_instance_id)'||
' AND plans.curr_plan_type = 6 '||
' AND plans.organization_id = desig.organization_id'||
' AND plans.sr_instance_id = desig.sr_instance_id'||
' AND plans.compile_designator = desig.designator'||
' AND NVL(desig.disable_date, TRUNC(SYSDATE)+1) > TRUNC(SYSDATE)'||
' AND plans.organization_selection <> 1'||
' AND NVL(plans.copy_plan_id,-1) = -1'||
' AND NVL(desig.copy_designator_id, -1) = -1',
1,SYSDATE,'Y','N','Plan Name',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(6,'SOLVE_IN_SERVER','VARCHAR2','Y',2,
'select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display '||
'from mfg_lookups '||
'where lookup_type = ''SYS_YES_NO''',
1,SYSDATE,'Y','Y','Solve in Server',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(6,'appProfile','VARCHAR2',NULL,3,
'select decode(lookup_code, 1, ''SOP'',2,''SCRM'',3,''SNO'') hidden, meaning display from mfg_lookups where lookup_type=''MSC_SCN_SOP_PROFILE''',
1,SYSDATE,'Y','Y','AppProfile',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(4,'PLAN_ID','NUMBER',NULL,1,
'',
1,SYSDATE,'Y','N','Plan Name',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(4,'ANCHOR_DATE','DATE',SYSDATE,2,
'',
1,SYSDATE,'Y','Y','Anchor Date',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(4,'ARCHIVE_FLAG','VARCHAR2','N',3,
'select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1,sysdate,'Y','Y','Archive Plan Summary',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(11,'INSTANCE_ID','NUMBER',NULL,1,
'select INSTANCE_ID hidden, INSTANCE_CODE Display '||
'FROM MSC_APPS_INSTANCES '||
'where instance_type IN (1,2,4) and enable_flag = 1',
1,SYSDATE,'Y','Y','Instance',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(11,'COLLECTION_GROUP','VARCHAR2',NULL,2,
'select CODE hidden, ORG_GROUP Display FROM MSC_ORG_GROUPS_V ' ,
1,SYSDATE,'Y','Y','Collections Group',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(11,'TOTAL_WORKER_NUM','NUMBER',3,3,NULL,1,SYSDATE,'Y','Y','Number of workers',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(11,'TIME_OUT','NUMBER',180,4,NULL,1,SYSDATE,'Y','Y','Timeout (Minutes)',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(11,'ODS_PURGE_OPTION','VARCHAR2','Y',5,
'select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1,SYSDATE,'Y','Y','Purge Previously Collected Data',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(11,'COLLECTION_METHOD','VARCHAR2','COMPLETE_REFRESH',6,
'select decode(lookup_code, 1, ''COMPLETE_REFRESH'',2,''NET_CHANGE_REFRESH'',3,''TARGETED_REFRESH'') hidden, meaning display ' ||
'from mfg_lookups WHERE lookup_type = ''PARTIAL_YES_NO''',
1,SYSDATE,'Y','Y','Collection Method',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(11,'ANALYZE_TABLES_ENABLED','VARCHAR2','N',7,
'select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1,SYSDATE,'Y','Y','Analyze Staging Tables',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(11,'APPROVED_SUPPLIER_LIST','VARCHAR2','YES_REPLACE',8,
' select decode(lookup_code, 1, ''YES_REPLACE'',2,''NO'',3,''YES_BUT_RETAIN_CP'') hidden, meaning display '||
'from mfg_lookups where lookup_type=''MSC_X_ASL_SYS_YES_NO''',
1,SYSDATE,'Y','Y','Collect Approved Supplier Lists (Supplier Capacities)',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(11,'ATP_RULES_ENABLED','VARCHAR2','Y',9,
'select decode(lookup_code, 1, ''Y'',2,''N'')hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1,SYSDATE,'Y','Y','Collect ATP Rules',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(11,'BOM_ENABLED','VARCHAR2','Y',10,
'select decode(lookup_code, 1, ''Y'',2,''N'')hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1,SYSDATE,'Y','Y','Collect Bill of Materials/Routings/Resources',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(11,'BOR_ENABLED','VARCHAR2','Y',11,
'select decode(lookup_code, 1, ''Y'',2,''N'')hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1,SYSDATE,'Y','Y','Collect Bills of Resources',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(11,'CALENDAR_ENABLED','VARCHAR2','Y',12,
'select decode(lookup_code, 1, ''Y'',2,''N'')hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1,SYSDATE,'Y','Y','Collect Calendars',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(11,'DEMAND_CLASS_ENABLED','VARCHAR2','Y',13,
'select decode(lookup_code, 1, ''Y'',2,''N'')hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1,SYSDATE,'Y','Y','Collect Demand Classes',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(11,'ITEM_SUBST_ENABLED','VARCHAR2','Y',14,
'select decode(lookup_code, 1, ''Y'',2,''N'')hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1,SYSDATE,'Y','Y','Collect End Item Substitutions',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(11,'FORECAST_ENABLED','VARCHAR2','Y',15,
'select decode(lookup_code, 1, ''Y'',2,''N'')hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1,SYSDATE,'Y','Y','Collect Forecasts',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(11,'ITEM_ENABLED','VARCHAR2','Y',16,
'select decode(lookup_code, 1, ''Y'',2,''N'')hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1,SYSDATE,'Y','Y','Collect Items',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(11,'KPI_BIS_ENABLED','VARCHAR2','Y',17,
'select decode(lookup_code, 1, ''Y'',2,''N'')hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1,SYSDATE,'Y','Y','Collect Key Performance Indicator Targets',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(11,'MDS_ENABLED','VARCHAR2','Y',18,
'select decode(lookup_code, 1, ''Y'',2,''N'')hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1,SYSDATE,'Y','Y','Collect Master Demand Schedules',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(11,'MPS_ENABLED','VARCHAR2','Y',19,
'select decode(lookup_code, 1, ''Y'',2,''N'')hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1,SYSDATE,'Y','Y','Collect Master Production Schedules',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(11,'ON_HAND_ENABLED','VARCHAR2','Y',20,
'select decode(lookup_code, 1, ''Y'',2,''N'')hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1,SYSDATE,'Y','Y','Collect On Hand',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(11,'PARAMETER_ENABLED','VARCHAR2','Y',21,
'select decode(lookup_code, 1, ''Y'',2,''N'')hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1,SYSDATE,'Y','Y','Collect Planning Parameters',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(11,'PLANNER_ENABLED','VARCHAR2','Y',22,
'select decode(lookup_code, 1, ''Y'',2,''N'')hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1,SYSDATE,'Y','Y','Planner',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(11,'PO_RECEIPTS_ENABLED','VARCHAR2','Y',23,
'select decode(lookup_code, 1, ''Y'',2,''N'')hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1,SYSDATE,'Y','Y','Collect PO Receipts',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(11,'PROJECT_ENABLED','VARCHAR2','Y',24,
'select decode(lookup_code, 1, ''Y'',2,''N'')hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1,SYSDATE,'Y','Y','Collect Projects / Tasks',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(11,'PUR_REQ_PO_ENABLED','VARCHAR2','Y',25,
'select decode(lookup_code, 1, ''Y'',2,''N'')hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1,SYSDATE,'Y','Y','Collect Purchase Orders / Purchase Requisitions',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(11,'RESERVES_HARD_ENABLED','VARCHAR2','Y',26,
'select decode(lookup_code, 1, ''Y'',2,''N'')hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1,SYSDATE,'Y','Y','Reservations',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(11,'RESOURCE_AVAILABILITY','VARCHAR2','COLLECT_DATA',27,
'select decode(lookup_code, 1, ''COLLECT_DATA'', 2, ''DO_NOT_COLLECT_DATA'', 3, ''REGENERATE_DATA'') hidden, meaning display from MFG_LOOKUPS where lookup_type=''MSC_NRA_ENABLED''',
1,SYSDATE,'Y','Y','Collect Resources Availability',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(11,'SAFE_STOCK_ENABLED','VARCHAR2','Y',28,
' select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1,SYSDATE,'Y','Y','Collect Safety Stock',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(11,'SALES_ORDER_RTYPE','VARCHAR2','N',29,
'select decode(lookup_code, 1, ''Y'', 2, ''N'') hidden, meaning display from mfg_lookups WHERE lookup_type = ''SYS_YES_NO''',
1,SYSDATE,'Y','Y','Collect Sales Orders',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(11,'SOURCING_HISTORY_ENABLED','VARCHAR2','N',30,
' select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1,SYSDATE,'Y','Y','Collect Sourcing History',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(11,'SOURCING_ENABLED','VARCHAR2','Y',31,
' select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1,SYSDATE,'Y','Y','Collect Sourcing Rules',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(11,'SUB_INV_ENABLED','VARCHAR2','Y',32,
' select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1,SYSDATE,'Y','Y','Collect Subinventories',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(11,'SUPPLIER_RESPONSE_ENABLED','VARCHAR2','Y',33,
' select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1,SYSDATE,'Y','Y','Collect Supplier Responses',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(11,'TP_CUSTOMER_ENABLED','VARCHAR2','Y',34,
' select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1,SYSDATE,'Y','Y','Collect Suppliers/Customers/Orgs',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(11,'TRIP_ENABLED','VARCHAR2','Y',35,
' select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1,SYSDATE,'Y','Y','Collect Transportation Details',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(11,'UNIT_NO_ENABLED','VARCHAR2','Y',36,
' select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1,SYSDATE,'Y','Y','Collect Unit Numbers',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(11,'UOM_ENABLED','VARCHAR2','Y',37,
' select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1,SYSDATE,'Y','Y','Collect Units Of Measure',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(11,'USER_COMPANY_ENABLED','VARCHAR2','NO',38,
'select decode(lookup_code, 1, ''NO'', 2, ''ENABLE_UCA'', 3, ''CREATE_USERS_ENABLE_UCA'') hidden, meaning display ' ||
'from fnd_lookups where lookup_type = ''MSC_X_USER_COMPANY''',
1,SYSDATE,'Y','Y','Collect User company Association',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(11,'USER_SUPPLY_DEMAND','VARCHAR2','Y',39,
' select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1,SYSDATE,'Y','Y','Collect User Supplies and Demands',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(11,'WIP_ENABLED','VARCHAR2','Y',40,
' select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1,SYSDATE,'Y','Y','Collect Work in Process',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(11,'SALES_CHANNEL_ENABLED','VARCHAR2','N',41,
' select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1,SYSDATE,'Y','Y','Collect Sales Channel',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(11,'FISCAL_CALENDAR_ENABLED','VARCHAR2','N',42,
' select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1,SYSDATE,'Y','Y','Collect Fiscal Calendar',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(11,'ITERNAL_REPAIR_ENABLED','VARCHAR2','N',43,
' select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1,SYSDATE,'Y','Y','Collect Internal Repair Orders',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(11,'ETERNAL_REPAIR_ENABLED','VARCHAR2','N',44,
' select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1,SYSDATE,'Y','Y','Collect External Repair Orders',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(11,'PAYBACK_DEMAND_SUPPLY_ENABLED','VARCHAR2','N',45,
' select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1,SYSDATE,'Y','Y','Enable Pay Back Demand Supply',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(11,'CURRENCY_CONVERSION_ENABLED','VARCHAR2','N',46,
' select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1,SYSDATE,'Y','Y','Enable Currency Conversion',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(11,'DELIVERY_DETAILS_ENABLED','VARCHAR2','N',47,
' select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1,SYSDATE,'Y','Y','Enable Delivery Details',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(11,'ODSTOTALWORKERNUM','NUMBER','3',48,NULL,
1,SYSDATE,'Y','Y','Number of workers',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(11,'RECALC_RES_AVAILABILITY','VARCHAR2','Y',49,
' select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1,SYSDATE,'Y','Y','Recalculate Sourcing History',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(11,'PURGE_SOURCING_HISTORY','VARCHAR2','N',50,
' select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1,SYSDATE,'Y','Y','Purge Sourcing History',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(10,'INSTANCE_ID','NUMBER',NULL,1,'select INSTANCE_ID hidden, INSTANCE_CODE Display '||
'FROM MSC_APPS_INSTANCES '||
'where instance_type IN (1,2,4) and enable_flag = 1',
1,SYSDATE,'Y','Y','Instance',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(10,'COLLECTION_GROUP','VARCHAR','-999',2,
'select CODE hidden, ORG_GROUP Display FROM MSD_DEM_ORG_GROUPS_V ',
1,SYSDATE,'Y','Y','Shipment and Booking History -Collection Group',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(10,'COLLECTION_METHOD','NUMBER',NULL,3,NULL,
1,SYSDATE,'Y','Y','Shipment and Booking History -Collection Method',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(10,'DATE_RANGE_TYPE','NUMBER',NULL,4,NULL,
1,SYSDATE,'Y','Y','Data Range Type',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(10,'HISTORY_COLLECTION_WINDOW','NUMBER',NULL,5,NULL,
1,SYSDATE,'N','Y','Shipment and Booking History - History Collection Window',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(10,'DATE_FROM','DATE',NULL,6,NULL,
1,SYSDATE,'N','Y','Shipment and Booking History - Date From',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(10,'DATE_TO','DATE',NULL,7,NULL,
1,SYSDATE,'N','Y','Shipment and Booking History - Date To',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(10,'BH_BOOKED_ITEMS_BOOKED_DATE','VARCHAR','N',8,
'select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1,SYSDATE,'Y','Y','Booking History - Booked Items - Booked Date',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(10,'BH_BOOKED_ITEMS_REQUESTED_DATE','VARCHAR','N',9,
'select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1,SYSDATE,'Y','Y','Booking History - Booked Items - Requested Date',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(10,'BH_REQUESTED_ITEMS_BOOKED_DATE','VARCHAR','N',10,
'select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1,SYSDATE,'Y','Y','Booking History - Requested Items - Booked Date',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(10,'BH_REQUESTED_ITEMS_REQUESTED_DATE','VARCHAR','N',11,
'select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1,SYSDATE,'Y','Y','Booking History - Requested Items - Requested Date',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(10,'SH_SHIPPED_ITEMS_SHIPPED_DATE','VARCHAR','N',12,
'select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1,SYSDATE,'Y','Y','ShipmentHistory - Shipped Items - Shipped Date',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(10,'SH_SHIPPED_ITEMS_REQUESTED_DATE','VARCHAR','N',13,
'select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1,SYSDATE,'Y','Y','ShipmentHistory - Shipped Items - Requested Date',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(10,'SH_REQUESTED_ITEMS_SHIPPED_DATE','VARCHAR','N',14,
'select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1,SYSDATE,'Y','Y','ShipmentHistory - Requested Items - Shipped Date',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(10,'SH_REQUESTED_ITEMS_REQUESTED_DATE','VARCHAR','N',15,
'select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1,SYSDATE,'Y','Y','ShipmentHistory - Requested Items - Requested Date',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(10,'COLLECT_ISO','VARCHAR','N',16,
'select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1,SYSDATE,'Y','Y','Collect Internal Sales Orders',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(10,'COLLECT_ALL_ORDER_TYPES','VARCHAR','Y',17,
'select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1,SYSDATE,'Y','Y','Collect All Order Types',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(10,'INCLUDE_ORDER_TYPES','VARCHAR','N',18,NULL,1,SYSDATE,'N','Y','Include Order types',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(10,'EXCLUDE_ORDER_TYPES','VARCHAR','N',19,NULL,1,SYSDATE,'N','Y','Exclude Order types',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(10,'LAUNCH_DOWNLOAD','VARCHAR','N',20,
'select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1,SYSDATE,'Y','Y','Launch Download',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(10,'DATE_FROM3','DATE',NULL,21,NULL,
1,SYSDATE,'N','Y','Currency Conversions - Date From',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(10,'DATE_TO3','DATE',NULL,22,NULL,
1,SYSDATE,'N','Y','Currency Conversions - Date To',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(10,'COLLECT_ALL_CURRENCIES','VARCHAR','N',23,
'select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1,SYSDATE,'Y','Y','Collect All Currency Conversions',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(10,'INCLUDE_CURRENCY_LIST','VARCHAR',NULL,24,NULL,
1,SYSDATE,'N','Y','Include Currency List',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(10,'EXCLUDE_CURRENCY_LIST','VARCHAR',NULL,25,NULL,
1,SYSDATE,'N','Y','Exclude Currency List',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(10,'INCLUDE_ALL','VARCHAR','N',26,
'select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1,SYSDATE,'Y','Y','Collect All Unit of Measures',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(10,'INCLUDE_UOM_LIST','VARCHAR',NULL,27,NULL,
1,SYSDATE,'N','Y','Include Unit of Measures',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(10,'EXCLUDE_UOM_LIST','VARCHAR',NULL,28,NULL,
1,SYSDATE,'N','Y','Exclude Unit of Measures',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(10,'START_DATE','DATE',NULL,29,NULL,
1,SYSDATE,'Y','Y','Pricing Data - Date From',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(10,'END_DATE','DATE',NULL,30,NULL,
1,SYSDATE,'Y','Y','Pricing Data - Date To',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(10,'INCLUDE_ALL1','VARCHAR','N',31,
'select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1,SYSDATE,'Y','Y','Collect all Price Lists',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(10,'INCLUDE_PRICE_LIST','VARCHAR',NULL,32,NULL,
1,SYSDATE,'N','Y','Include Price Lists',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(10,'EXCLUDE_PRICE_LIST','VARCHAR',NULL,33,NULL,
1,SYSDATE,'N','Y','Exclude Price Lists',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(10,'COLLECTION_GROUP1','VARCHAR','-999',34,
'select CODE hidden, ORG_GROUP Display FROM MSD_DEM_ORG_GROUPS_V ',
1,SYSDATE,'Y','Y','Supply Chain Intelligence Data - Collection Group',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(10,'COLLECTION_METHOD1','NUMBER',NULL,35,NULL,
1,SYSDATE,'Y','Y','Supply Chain Intelligence Data - Collection Method',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(10,'DATE_RANGE_TYPE1','NUMBER',NULL,36,NULL,
1,SYSDATE,'Y','Y','Data Range Type',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(10,'HISTORY_COLLECTION_WINDOW1','NUMBER',NULL,37,NULL,
1,SYSDATE,'N','Y','Supply Chain Intelligence Data - History Collection Window',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(10,'DATE_FROM1','DATE',NULL,38,NULL,
1,SYSDATE,'N','Y','Supply Chain Intelligence Data - Date From',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(10,'DATE_TO1','DATE',NULL,39,NULL,
1,SYSDATE,'N','Y','Shipment and Booking History - Date To',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(13,'NEW_PLAN_NAME','VARCHAR2',NULL,1,NULL,
1,SYSDATE,'Y','Y','New Plan Name',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(13,'DATA_PROFILE_NAME','VARCHAR2',NULL,2,
'SELECT SUBSTR(tq.query_name, 1, 50) hidden, SUBSTR(tq.query_name, 1, 50) display '||
'FROM msd_dem_transfer_list tl, msd_dem_transfer_query tq ' ||
'WHERE tl.id = tq.transfer_id AND tq.integration_type <> 1 AND tq.export_type = 1 '||
'AND tq.presentation_type = 1 AND msd_dem_upload_forecast.is_valid_scenario(tq.id) = 1 ',
1,SYSDATE,'Y','Y','Data Profile name of the Forecast',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(13,'ARCHIVE_FLAG','NUMBER',2,3,
'select lookup_code hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1,sysdate,'Y','Y','Archive Forecast',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(19,'NEW_PLAN_NAME','VARCHAR2',NULL,1,NULL,
1,SYSDATE,'Y','Y','New Plan Name',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(19,'DATA_PROFILE_NAME','VARCHAR2',NULL,2,
'SELECT SUBSTR(tq.query_name, 1, 50) hidden, SUBSTR(tq.query_name, 1, 50) display '||
'FROM msd_dem_transfer_list tl, msd_dem_transfer_query tq ' ||
'WHERE tl.id = tq.transfer_id AND tq.integration_type <> 1 AND tq.export_type = 1 '||
'AND tq.presentation_type = 1 AND msd_dem_upload_forecast.is_valid_scenario(tq.id) = 1 ',
1,SYSDATE,'Y','Y','Data Profile name of the Forecast',p_version);
INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
NAME,
DATA_TYPE,
DEFAULT_VALUE,
SEQUENCE,
SQL,
CREATED_BY,
CREATION_DATE,
REQUIRED,
DISPLAYED,
DISPLAY_NAME,
PARAM_VERSION)
VALUES(19,'ARCHIVE_FLAG','NUMBER',2,3,
'select lookup_code hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1,sysdate,'Y','Y','Archive Forecast',p_version);
end insert_seed_data;
select compile_designator as plan_name
from msc_plans
where plan_type = nvl(p_plan_type,plan_type) and plan_id = p_plan_id
union
select distinct scenario_name as plan_name
from msd_dp_ascp_scenarios_v
where scenario_id=p_plan_id and p_plan_type=10;
select plan_run_name
from msc_plan_runs
where plan_id=p_plan_id and
plan_run_id = p_plan_run_id;
select end_date
from msc_plan_runs
where plan_run_id = p_plan_run_id;
select user_name
from fnd_user
where user_id = p_owner_id;
select end_date
from msc_plan_runs
where plan_run_id = p_plan_run_id;
select scenario_name
from msc_scenarios
where scenario_id = p_scenario_id;
select meaning from mfg_lookups
where lookup_type = 'MSC_PROCESS_ACTIVITY_TYPES'
and lookup_code = p_activity_type;
insert into msc_scenario_plans (scenario_id,
plan_type,
plan_id,
created_by,
creation_Date ,
last_update_date,
last_updated_by,
last_update_login,
status,
run_date,
plan_horizon,
plan_run_id)
(select p_dest_scnId,
plan_type,
plan_id,
l_user_id,
sysdate,
sysdate,
l_user_id,
l_login_id,
status,
run_date,
plan_horizon,
plan_run_id from msc_scenario_plans where scenario_id = p_src_scnId);
select msc_scn_utils.get_owner_name(user_id)
from msc_scenario_users
where scenario_id = p_scn_id;
select count(*) from msc_scenario_plans
where scenario_id <> p_scn_id and plan_id = p_plan_id and plan_run_id = p_plan_run_id;
generate plan_run_id and update plan_run_id field for it.
call populate_Details api
end for;
insert new record into msc_scenarios for current version of the scenario
update scenario_name,version for this scenario_id by appending sysdate to it
*/
procedure archive_scn_conc( errbuf out nocopy varchar2, retcode out nocopy varchar2 , p_scn_id in number) is
cursor c_scn_plans is
select plan_type,plan_id,run_date
from msc_scenario_plans
where scenario_id = p_scn_id and archive_flag = 'Y';
select count(*) into l_scn_count from msc_scenarios where scenario_name like l_scn_name||' ('||sysdate||')%';
update msc_scenarios set scn_version = sysdate,scenario_name = scenario_name||' ('||sysdate||')('||l_scn_count||')' where scenario_id = p_scn_id;
select msc_scn_scenarios_s.nextval into l_new_scn_id from dual;
insert into msc_scenarios(scenario_id,
scenario_name,
created_by,
creation_date,
last_update_date,
last_updated_by,
last_update_login,
parent_scn_id,
description,
owner,
scn_access,
scn_comment,
valid_from,
valid_to,
scn_version)
(select l_new_scn_id,
l_scn_name,
l_user_id,
sysdate,
sysdate,
l_user_id,
l_login_id,
parent_scn_id,
description,
owner,
scn_access,
scn_comment,
valid_from,
valid_to,
null
from msc_scenarios where scenario_id = p_scn_id);
insert into msc_scenario_plans (scenario_id,
plan_type,
plan_id,
created_by,
creation_Date ,
last_update_date,
last_updated_by,
last_update_login,
status,
run_date,
plan_horizon,
plan_run_id)
(select l_new_scn_id,
plan_type,
plan_id,
l_user_id,
sysdate,
sysdate,
l_user_id,
l_login_id,
status,
run_date,
plan_horizon,
plan_run_id from msc_scenario_plans where scenario_id = p_scn_id);
insert into msc_scenario_users (scenario_id,
user_id,
created_by,
creation_Date,
last_update_date,
last_updated_by,
last_update_login)
(select l_new_scn_id,
user_id,
l_user_id,
sysdate,
sysdate,
l_user_id,
l_login_id from msc_scenario_users where scenario_id = p_scn_id);
update msc_scenario_plans set plan_run_id = l_plan_run_id where
scenario_id = p_scn_id and plan_id = l_plan_id and plan_type = l_plan_type;
select plan_type,plan_id,plan_run_id
from msc_scenario_plans
where scenario_id = p_scn_id and purge_flag = 'Y' and plan_run_id is not null;
select scn_version
from msc_scenarios
where scenario_id = p_scn_id;
delete msc_scenarios where scenario_id = p_scn_id;
delete msc_scenario_plans where scenario_id = p_scn_id;
delete msc_Scenario_users where scenario_id = p_scn_id;
delete msc_Scenario_set_details where scenario_id = p_scn_id;
delete from msc_scenario_plans where plan_id = p_plan_id and plan_type = p_plan_type;
select name,sql
from msc_activity_parameters
where activity_type = p_activity_type and sql is not null;
l_insert varchar2(2000);
l_select varchar2(2000);
insert_seed_data;
select count(*) into l_count from msc_form_query where query_id = p_activity_type;
l_substr := substr(l_sql,instr(upper(l_sql),'SELECT ')+7);
l_insert := 'insert into msc_form_query (query_id,
char1,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
char2,
char3)';
l_select := ' select '||p_activity_type||','||''''||l_param_name||''''||','||'sysdate,1,1,sysdate,1,'||l_substr;
l_sql_stmnt := l_insert||l_select;
Select
msc_Get_name.lookup_meaning(
'MSC_SCN_PLAN_STATUS',
decode(upper(fcr.status_code),'C',4,'E',3)
) plan_status_display
from msc_plans mp,
msc_plan_runs mpr,
fnd_concurrent_requests fcr
where mp.plan_type is not null and
mp.plan_id = mpr.plan_id and
mpr.end_date is not null and
mp.plan_completion_date is not null and
mp.request_id = fcr.request_id
and mp.plan_id = c_plan_id;
select scenario_set_name
from msc_scenario_sets
where scenario_set_id = p_scenario_set_id;
select_query varchar2(32000) := null;
select_query := ' SELECT source_table, msc_get_name.lookup_meaning(''MSC_SCN_ACTIVITY_STATES'', status) activity_status, ' ;
select_query:= select_query || ' decode(SIGN(finish_by -TRUNC(sysdate) + 0), -1, ''PAST_DUE'', 0, ''CURRENT'', 1, ''FUTURE'') ppf, ';
select_query:= select_query || ' status, COUNT(activity_id) summ ';
from_query := ' from(select Activity_Name, Activity_Description, Activity_Status, Owner_Name, Finish_By, Activity_Type, Activity_Comment, Scenario_Set_Name, Scenario_Set_Description, Scenario_Name, Scenario_Description, ';
from_query := from_query || ' Scenario_set_Id, source_table, Owner, Status, created_by, last_update_date, Last_updated_by, last_update_login, Priority, Alternate_Owner, Scenario_owner from ( (select ';
from_query := from_query || ' msa.Owner, msa.Status Status, msa.created_by, msa.last_update_date, msa.Last_updated_by, msa.last_update_login, msa.Priority, msa.Alternate_Owner, ms.owner Scenario_Owner FROM MSC_SCENARIO_ACTIVITIES MSA, ';
from_query := from_query || ' (select concat(concat(mpp.process_name,'' - ''),msc_get_name.lookup_meaning(''MSC_PROCESS_ACTIVITY_TYPES'',MPPA.Activity_Type)) Activity_Name, to_char(NULL) Activity_Description, ';
from_query := from_query || ' MPPA.Status Status, MPPA.created_by, MPPA.last_update_date, MPPA.Last_updated_by, MPPA.last_update_login, to_number(NULL) Priority, MPPA.Alternate_Owner Alternate_Owner, to_number(NULL) Scenario_Owner ';
sql_stmt := select_query || ' ' || from_query || ' ' || l_where_clause || ' ' || group_by;
select plan_completion_date
from msc_plans
where plan_type = p_plan_type and
plan_id = p_plan_id;
select last_update_date
from msd_dp_scenario_revisions mdr,
msd_dp_ascp_scenarios_v mdas
where mdr.scenario_id=mdas.scenario_id and
mdr.revision = mdas.last_revision;
select
nvl(
decode(s5,0,5,1,5,
decode(s4,0,4,1,4,
decode(s6,0,6,1,6,
decode(s2,0,2,1,2,
decode(s1,0,1,1,1,3)
)
)
)
),3)
proc_status from
(select
sign(sum(decode(status,1,1,0))-1) s1,
sign(sum(decode(status,2,1,0))-1) s2,
sign(sum(decode(status,3,1,0))-1) s3,
sign(sum(decode(status,4,1,0))-1) s4,
sign(sum(decode(status,5,1,0))-1) s5,
sign(sum(decode(status,6,1,0))-1) s6
from msc_planning_proc_activities
where
process_id = p_process_id
and run_sequence = p_curr_run_seq
and skip=2) a;
SELECT sequence,sql,default_value from msc_activity_parameters
where activity_type = p_activity_type and sql is not null and
default_value is not null;
l_sql_stmt := 'select hidden,display from (' || l_sql || ') where hidden = ''' || l_default || '''';