eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.MSC_CL_ROUTING_ODS_LOAD SQL Statements

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

Line: 21


  SELECT
     std_op_res.SR_INSTANCE_ID,
     std_op_res.STANDARD_OPERATION_ID,
     std_op_res.RESOURCE_ID,
     std_op_res.OPERATION_CODE,
     std_op_res.ORGANIZATION_ID,
     std_op_res.DEPARTMENT_ID,
     std_op_res.RESOURCE_SEQ_NUM,
     std_op_res.RESOURCE_USAGE,
     std_op_res.BASIS_TYPE,
     std_op_res.RESOURCE_UNITS,
     std_op_res.SUBSTITUTE_GROUP_NUM,
     std_op_res.UOM_CODE,
     std_op_res.SCHEDULE_FLAG
 FROM MSC_ST_STD_OP_RESOURCES std_op_res
 WHERE std_op_res.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;

Line: 47


      MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_STD_OP_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1);

Line: 49


      MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_STD_OP_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);  /* ds change change */

Line: 57


       INSERT INTO MSC_STD_OP_RESOURCES
        ( PLAN_ID,
        SR_INSTANCE_ID,
        STANDARD_OPERATION_ID  ,
        RESOURCE_ID,
        OPERATION_CODE,
        ORGANIZATION_ID,
        DEPARTMENT_ID,
        RESOURCE_SEQ_NUM,
        RESOURCE_USAGE,
        BASIS_TYPE,
        RESOURCE_UNITS,
	SUBSTITUTE_GROUP_NUM,
	UOM_CODE,
	SCHEDULE_FLAG,
	REFRESH_NUMBER,
        LAST_UPDATE_DATE,
        LAST_UPDATED_BY,
        CREATION_DATE,
        CREATED_BY)
      VALUES
        ( -1,
        c_rec.SR_INSTANCE_ID,
        c_rec.STANDARD_OPERATION_ID  ,
        c_rec.RESOURCE_ID,
        c_rec.OPERATION_CODE,
        c_rec.ORGANIZATION_ID,
        c_rec.DEPARTMENT_ID,
        c_rec.RESOURCE_SEQ_NUM,
        c_rec.RESOURCE_USAGE,
        c_rec.BASIS_TYPE,
        c_rec.RESOURCE_UNITS,
        c_rec.SUBSTITUTE_GROUP_NUM,
        c_rec.UOM_CODE,
        c_rec.SCHEDULE_FLAG,
        MSC_CL_COLLECTION.v_last_collection_id,
        MSC_CL_COLLECTION.v_current_date,
        MSC_CL_COLLECTION.v_current_user,
        MSC_CL_COLLECTION.v_current_date,
        MSC_CL_COLLECTION.v_current_user );

Line: 168


Select
mon.FROM_OP_SEQ_ID,
mon.TO_OP_SEQ_ID,
mon.TRANSITION_TYPE,
mon.PLANNING_PCT,
mon.CUMMULATIVE_PCT,
mon.EFECTIVITY_DATE,
mon.DISABLE_DATE,
mon.PLAN_ID,
mon.CREATED_BY,
mon.CREATION_DATE,
mon.DELETED_FLAG,
mon.LAST_UPDATED_BY,
mon.LAST_UPDATE_DATE,
mon.LAST_UPDATE_LOGIN,
mon.ATTRIBUTE_CATEGORY,
mon.ATTRIBUTE1,
mon.ATTRIBUTE2,
mon.ATTRIBUTE3,
mon.ATTRIBUTE4,
mon.ATTRIBUTE5,
mon.ATTRIBUTE6,
mon.ATTRIBUTE7,
mon.ATTRIBUTE8,
mon.ATTRIBUTE9,
mon.ATTRIBUTE10,
mon.ATTRIBUTE11,
mon.ATTRIBUTE12,
mon.ATTRIBUTE13,
mon.ATTRIBUTE14,
mon.ATTRIBUTE15,
mon.routing_sequence_id,
mon.FROM_OP_SEQ_NUM,
mon.TO_OP_SEQ_NUM,
mon.TO_ROUTING_SEQUENCE_ID,  /*ds change change start */
t1.INVENTORY_ITEM_ID   FROM_ITEM_ID,
mon.ORGANIZATION_ID,
mon.MINIMUM_TRANSFER_QTY,
mon.MINIMUM_TIME_OFFSET,
mon.MAXIMUM_TIME_OFFSET,
mon.DEPENDENCY_TYPE,
mon.APPLY_TO_CHARGES,
mon.TRANSFER_PCT,
mon.TRANSFER_QTY,
mon.TRANSFER_UOM,		/*ds change change end */
mon.REFRESH_ID,
mon.SR_INSTANCE_ID
from MSC_ST_OPERATION_NETWORKS mon,
MSC_ITEM_ID_LID t1	  /* ds change change */
WHERE mon.SR_INSTANCE_ID = MSC_CL_COLLECTION.v_instance_id
AND   mon.DELETED_FLAG	= MSC_UTIL.SYS_NO
AND   mon.FROM_ITEM_ID =  t1.SR_INVENTORY_ITEM_ID(+)	/* ds change change */
AND   mon.sr_instance_id =  t1.sr_instance_id(+) ;     /* ds change change */

Line: 224


select
mon.FROM_OP_SEQ_ID,
mon.TO_OP_SEQ_ID,
mon.DELETED_FLAG,
mon.REFRESH_ID,
mon.SR_INSTANCE_ID
FROM MSC_ST_OPERATION_NETWORKS mon
WHERE mon.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
and mon.DELETED_FLAG= MSC_UTIL.SYS_YES;

Line: 243


    MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_OPERATION_NETWORKS', MSC_CL_COLLECTION.v_instance_id, -1);

Line: 246


    MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_OPERATION_NETWORKS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);

Line: 257


DELETE MSC_OPERATION_NETWORKS
 WHERE PLAN_ID= -1
   AND FROM_OP_SEQ_ID = c_rec.from_op_seq_id
   AND TO_OP_SEQ_ID =   c_rec.to_op_seq_id
   AND SR_INSTANCE_ID=  c_rec.SR_INSTANCE_ID;

Line: 276


UPDATE MSC_OPERATION_NETWORKS
SET
FROM_OP_SEQ_ID = c_rec.from_op_seq_id,
TO_OP_SEQ_ID  = c_rec.to_op_seq_id,
routing_sequence_id = c_rec.routing_sequence_id,
TRANSITION_TYPE = c_rec.transition_type,
PLANNING_PCT   = c_rec.planning_pct,
CUMMULATIVE_PCT = c_rec. cummulative_pct,
EFFECTIVITY_DATE  = c_Rec.efectivity_date,
DISABLE_DATE     = c_Rec.disable_date,
PLAN_ID          = -1,
LAST_UPDATED_BY = MSC_CL_COLLECTION.v_current_user,
LAST_UPDATE_DATE = MSC_CL_COLLECTION.v_current_date,
ATTRIBUTE_CATEGORY = c_rec.attribute_category,
ATTRIBUTE1  = c_rec.attribute1,
ATTRIBUTE2  = c_rec.attribute2,
ATTRIBUTE3  = c_Rec.attribute3,
ATTRIBUTE4  = c_rec.attribute4,
ATTRIBUTE5  = c_rec.attribute5,
ATTRIBUTE6  = c_rec.attribute6,
ATTRIBUTE7  = c_rec.attribute7,
ATTRIBUTE8  = c_rec.attribute8,
ATTRIBUTE9  = c_rec.attribute9,
ATTRIBUTE10  = c_rec.attribute10,
ATTRIBUTE11  = c_rec.attribute11,
ATTRIBUTE12   = c_rec.attribute12,
ATTRIBUTE13   = c_rec.attribute13,
ATTRIBUTE14   = c_Rec.attribute14,
ATTRIBUTE15   = c_rec.attribute15,
FROM_OP_SEQ_NUM= c_rec.FROM_OP_SEQ_NUM,
TO_OP_SEQ_NUM = c_rec.TO_OP_SEQ_NUM,
DEPENDENCY_TYPE = c_rec.DEPENDENCY_TYPE,   /* ds change other new attr not added as they are for opm */
REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id
WHERE PLAN_ID= -1
AND FROM_OP_SEQ_ID= c_rec.FROM_OP_SEQ_ID
AND TO_OP_SEQ_ID=   c_rec.TO_OP_SEQ_ID
AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;

Line: 318


insert into MSC_OPERATION_NETWORKS
( FROM_OP_SEQ_ID,
 TO_OP_SEQ_ID,
 ROUTING_SEQUENCE_ID,
 TRANSITION_TYPE,
 PLANNING_PCT,
 CUMMULATIVE_PCT,
 EFFECTIVITY_DATE,
 DISABLE_DATE,
 PLAN_ID,
 TO_ROUTING_SEQUENCE_ID,  /*ds change change start */
 FROM_ITEM_ID,
 ORGANIZATION_ID,
 MINIMUM_TRANSFER_QTY,
 MINIMUM_TIME_OFFSET,
 MAXIMUM_TIME_OFFSET,
 DEPENDENCY_TYPE,
 APPLY_TO_CHARGES,
 TRANSFER_PCT,
 TRANSFER_QTY,
 TRANSFER_UOM,		/*ds change change end */
 CREATED_BY,
 CREATION_DATE,
 LAST_UPDATED_BY,
 LAST_UPDATE_DATE,
 ATTRIBUTE_CATEGORY,
 ATTRIBUTE1,
 ATTRIBUTE2,
 ATTRIBUTE3,
 ATTRIBUTE4,
 ATTRIBUTE5,
 ATTRIBUTE6,
 ATTRIBUTE7,
 ATTRIBUTE8,
 ATTRIBUTE9,
 ATTRIBUTE10,
 ATTRIBUTE11,
 ATTRIBUTE12,
 ATTRIBUTE13,
 ATTRIBUTE14,
 ATTRIBUTE15,
 FROM_OP_SEQ_NUM,
 TO_OP_SEQ_NUM,
 REFRESH_NUMBER,
 SR_INSTANCE_ID)
 values( c_rec.FROM_OP_SEQ_ID,
 c_rec.TO_OP_SEQ_ID,
 c_rec.ROUTING_SEQUENCE_ID,
 c_rec.TRANSITION_TYPE,
 c_rec.PLANNING_PCT,
 c_rec.CUMMULATIVE_PCT,
 c_rec.EFECTIVITY_DATE,
 c_rec.DISABLE_DATE,
 -1,
 c_rec.TO_ROUTING_SEQUENCE_ID,  /*ds change change start */
 c_rec.FROM_ITEM_ID   ,
 c_rec.ORGANIZATION_ID,
 c_rec.MINIMUM_TRANSFER_QTY,
 c_rec.MINIMUM_TIME_OFFSET,
 c_rec.MAXIMUM_TIME_OFFSET,
 c_rec.DEPENDENCY_TYPE,
 c_rec.APPLY_TO_CHARGES,
 c_rec.TRANSFER_PCT,
 c_rec.TRANSFER_QTY,
 c_rec.TRANSFER_UOM,		/*ds change change end */
 MSC_CL_COLLECTION.v_current_user,
 MSC_CL_COLLECTION.v_current_date,
 MSC_CL_COLLECTION.v_current_user,
 MSC_CL_COLLECTION.v_current_date,
 c_rec.ATTRIBUTE_CATEGORY,
 c_rec.ATTRIBUTE1,
 c_rec.ATTRIBUTE2,
 c_Rec.ATTRIBUTE3,
 c_rec.ATTRIBUTE4,
 c_rec.ATTRIBUTE5,
 c_rec.ATTRIBUTE6,
 c_rec.ATTRIBUTE7,
 c_rec.ATTRIBUTE8,
 c_rec.ATTRIBUTE9,
 c_rec.ATTRIBUTE10,
 c_rec.ATTRIBUTE11,
 c_rec.ATTRIBUTE12,
 c_rec.ATTRIBUTE13,
 c_rec.ATTRIBUTE14,
 c_rec.ATTRIBUTE15,
 c_rec.FROM_OP_SEQ_NUM,
 c_rec.TO_OP_SEQ_NUM,
 MSC_CL_COLLECTION.v_last_collection_id,
 MSC_CL_COLLECTION.v_instance_id );

Line: 475


SELECT
  msr.ROUTING_SEQUENCE_ID,
  msr.ROUTING_TYPE,
  REPLACE(REPLACE(substrb(msr.ROUTING_COMMENT,1,240),v_chr10,' '),v_chr13,' ') ROUTING_COMMENT,
  msr.ALTERNATE_ROUTING_DESIGNATOR,
  msr.PROJECT_ID,
  msr.TASK_ID,
  msr.LINE_ID,
  msr.UOM_CODE,
  nvl(msr.CFM_ROUTING_FLAG,2) CFM_ROUTING_FLAG,
  msr.CTP_FLAG,
  t1.INVENTORY_ITEM_ID ASSEMBLY_ITEM_ID,    -- msr.ASSEMBLY_ITEM_ID,
  msr.ORGANIZATION_ID,
  msr.ROUTING_QUANTITY,
  msr.DELETED_FLAG,
  msr.SR_INSTANCE_ID,
  msr.FIRST_OP_SEQ_NUM,
  msr.LAST_OP_SEQ_NUM,
  msr.common_routing_sequence_id,
  msr.auto_step_qty_flag
FROM MSC_ITEM_ID_LID t1,
     MSC_ST_ROUTINGS msr
WHERE t1.SR_INVENTORY_ITEM_ID= msr.assembly_item_id
  AND t1.sr_instance_id= msr.sr_instance_id
  AND msr.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
  AND msr.DELETED_FLAG= MSC_UTIL.SYS_NO;

Line: 503


SELECT
  msr.ROUTING_SEQUENCE_ID,
  msr.SR_INSTANCE_ID
 FROM MSC_ST_ROUTINGS msr
WHERE msr.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
  AND msr.DELETED_FLAG= MSC_UTIL.SYS_YES;

Line: 525


    MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ROUTINGS', MSC_CL_COLLECTION.v_instance_id, -1);

Line: 528


    MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ROUTINGS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);

Line: 533


  'INSERT INTO MSC_ROUTINGS'
  ||' (PLAN_ID,'
  ||'  ROUTING_SEQUENCE_ID,'
  ||'  ROUTING_TYPE,'
  ||'  ROUTING_COMMENT,'
  ||'  ALTERNATE_ROUTING_DESIGNATOR,'
  ||'  PROJECT_ID,'
  ||'  TASK_ID,'
  ||'  LINE_ID,'
  ||'  UOM_CODE,'
  ||'  CFM_ROUTING_FLAG,'
  ||'  CTP_FLAG,'
  ||'  ASSEMBLY_ITEM_ID,'
  ||'  ORGANIZATION_ID,'
  ||'  ROUTING_QUANTITY,'
  ||'  SR_INSTANCE_ID,'
  ||'  REFRESH_NUMBER,'
  ||'  LAST_UPDATE_DATE,'
  ||'  LAST_UPDATED_BY,'
  ||'  CREATION_DATE,'
  ||'  CREATED_BY,'
  ||'  FIRST_OP_SEQ_NUM,'
  ||'  LAST_OP_SEQ_NUM,'
  ||'  COMMON_ROUTING_SEQUENCE_ID,'
  ||'  AUTO_STEP_QTY_FLAG)'
  ||' SELECT '
  ||'  -1,'
  ||'  msr.ROUTING_SEQUENCE_ID,'
  ||'  msr.ROUTING_TYPE,'
  ||'  REPLACE(REPLACE(substrb(msr.ROUTING_COMMENT,1,240),:v_chr10,'' ''),:v_chr13,'' '') ROUTING_COMMENT,'
  ||'  msr.ALTERNATE_ROUTING_DESIGNATOR,'
  ||'  msr.PROJECT_ID,'
  ||'  msr.TASK_ID,'
  ||'  msr.LINE_ID,'
  ||'  msr.UOM_CODE,'
  ||'  nvl(msr.CFM_ROUTING_FLAG,2) CFM_ROUTING_FLAG,'
  ||'  msr.CTP_FLAG,'
  ||'  t1.INVENTORY_ITEM_ID ASSEMBLY_ITEM_ID,'
  ||'  msr.ORGANIZATION_ID,'
  ||'  msr.ROUTING_QUANTITY,'
  ||'  msr.SR_INSTANCE_ID,'
  ||'  :v_last_collection_id,'
  ||'  :v_current_date,'
  ||'  :v_current_user,'
  ||'  :v_current_date,'
  ||'  :v_current_user, '
  ||'  msr.FIRST_OP_SEQ_NUM,'
  ||'  msr.LAST_OP_SEQ_NUM,'
  ||'  msr.COMMON_ROUTING_SEQUENCE_ID,'
  ||'  msr.AUTO_STEP_QTY_FLAG '
  ||' FROM MSC_ITEM_ID_LID t1,'
  ||'     MSC_ST_ROUTINGS msr'
  ||' WHERE t1.SR_INVENTORY_ITEM_ID= msr.assembly_item_id'
  ||'   AND t1.sr_instance_id= msr.sr_instance_id'
  ||'   AND msr.SR_INSTANCE_ID=  '||MSC_CL_COLLECTION.v_instance_id
  ||'   AND msr.DELETED_FLAG= '||MSC_UTIL.SYS_NO;

Line: 621


        MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'bulk insert failed - routings');

Line: 636


DELETE MSC_ROUTINGS
 WHERE PLAN_ID= -1
   AND ROUTING_SEQUENCE_ID= c_rec.ROUTING_SEQUENCE_ID
   AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;

Line: 654


UPDATE MSC_ROUTINGS
SET
 ROUTING_TYPE= c_rec.ROUTING_TYPE,
 ROUTING_COMMENT= c_rec.ROUTING_COMMENT,
 ALTERNATE_ROUTING_DESIGNATOR= c_rec.ALTERNATE_ROUTING_DESIGNATOR,
 PROJECT_ID= c_rec.PROJECT_ID,
 TASK_ID= c_rec.TASK_ID,
 LINE_ID= c_rec.LINE_ID,
 UOM_CODE= c_rec.UOM_CODE,
 CFM_ROUTING_FLAG= c_rec.CFM_ROUTING_FLAG,
 FIRST_OP_SEQ_NUM = c_rec.FIRST_OP_SEQ_NUM,
 LAST_OP_SEQ_NUM = c_rec.LAST_OP_SEQ_NUM,
 common_routing_sequence_id=c_rec.common_routing_sequence_id,
 CTP_FLAG= c_rec.CTP_FLAG,
 ASSEMBLY_ITEM_ID= c_rec.ASSEMBLY_ITEM_ID,
 ORGANIZATION_ID= c_rec.ORGANIZATION_ID,
 ROUTING_QUANTITY= c_rec.ROUTING_QUANTITY,
 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
WHERE PLAN_ID= -1
  AND ROUTING_SEQUENCE_ID= c_rec.ROUTING_SEQUENCE_ID
  AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;

Line: 682


INSERT INTO MSC_ROUTINGS
( PLAN_ID,
  ROUTING_SEQUENCE_ID,
  ROUTING_TYPE,
  ROUTING_COMMENT,
  ALTERNATE_ROUTING_DESIGNATOR,
  PROJECT_ID,
  TASK_ID,
  LINE_ID,
  UOM_CODE,
  CFM_ROUTING_FLAG,
  CTP_FLAG,
  ASSEMBLY_ITEM_ID,
  ORGANIZATION_ID,
  ROUTING_QUANTITY,
  SR_INSTANCE_ID,
  REFRESH_NUMBER,
  LAST_UPDATE_DATE,
  LAST_UPDATED_BY,
  CREATION_DATE,
  CREATED_BY,
  FIRST_OP_SEQ_NUM,
  LAST_OP_SEQ_NUM,
  common_routing_sequence_id,
  auto_step_qty_flag)
VALUES
( -1,
  c_rec.ROUTING_SEQUENCE_ID,
  c_rec.ROUTING_TYPE,
  c_rec.ROUTING_COMMENT,
  c_rec.ALTERNATE_ROUTING_DESIGNATOR,
  c_rec.PROJECT_ID,
  c_rec.TASK_ID,
  c_rec.LINE_ID,
  c_rec.UOM_CODE,
  c_rec.CFM_ROUTING_FLAG,
  c_rec.CTP_FLAG,
  c_rec.ASSEMBLY_ITEM_ID,
  c_rec.ORGANIZATION_ID,
  c_rec.ROUTING_QUANTITY,
  c_rec.SR_INSTANCE_ID,
  MSC_CL_COLLECTION.v_last_collection_id,
  MSC_CL_COLLECTION.v_current_date,
  MSC_CL_COLLECTION.v_current_user,
  MSC_CL_COLLECTION.v_current_date,
  MSC_CL_COLLECTION.v_current_user,
  c_rec.first_op_seq_num,
  c_rec.last_op_seq_num,
  c_rec.common_routing_sequence_id,
  c_rec.auto_step_qty_flag);

Line: 792


SELECT
  ORGANIZATION_ID,
  OPERATION_SEQUENCE_ID,
  COMPONENT_SEQUENCE_ID,
  BILL_SEQUENCE_ID,
  ROUTING_SEQUENCE_ID,
  SR_INSTANCE_ID
FROM MSC_ST_OPERATION_COMPONENTS msoc
WHERE msoc.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
  AND msoc.DELETED_FLAG= MSC_UTIL.SYS_NO;

Line: 804


SELECT
  OPERATION_SEQUENCE_ID,
  COMPONENT_SEQUENCE_ID,
  BILL_SEQUENCE_ID,
  ROUTING_SEQUENCE_ID,
  SR_INSTANCE_ID,
  ORGANIZATION_ID
FROM MSC_ST_OPERATION_COMPONENTS msoc
WHERE msoc.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
  AND msoc.DELETED_FLAG= MSC_UTIL.SYS_YES
UNION ALL
SELECT DISTINCT
  TO_NUMBER(NULL),
  TO_NUMBER(NULL),
  moc.BILL_SEQUENCE_ID,
  moc.ROUTING_SEQUENCE_ID,
  moc.SR_INSTANCE_ID,
  moc.ORGANIZATION_ID
FROM MSC_OPERATION_COMPONENTS moc,
     MSC_ST_OPERATION_COMPONENTS msoc
WHERE msoc.Bill_Sequence_ID= moc.Bill_Sequence_ID
  AND msoc.Routing_Sequence_ID <> moc.Routing_Sequence_ID
  AND msoc.Organization_ID= moc.Organization_ID
  AND msoc.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
  AND moc.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
  AND msoc.DELETED_FLAG= MSC_UTIL.SYS_NO
  AND moc.PLAN_ID= -1;

Line: 838


   lv_delete_flag BOOLEAN:= FALSE;

Line: 848


    MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_OPERATION_COMPONENTS', MSC_CL_COLLECTION.v_instance_id, -1);

Line: 851


    MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_OPERATION_COMPONENTS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);

Line: 854


INSERT /*+ append  */
INTO MSC_OPERATION_COMPONENTS
  (PLAN_ID,
  ORGANIZATION_ID,
  OPERATION_SEQUENCE_ID,
  COMPONENT_SEQUENCE_ID,
  SR_INSTANCE_ID,
  BILL_SEQUENCE_ID,
  ROUTING_SEQUENCE_ID,
  REFRESH_NUMBER,
  LAST_UPDATE_DATE,
  LAST_UPDATED_BY,
  CREATION_DATE,
  CREATED_BY)
  SELECT
  -1,
  ORGANIZATION_ID,
  OPERATION_SEQUENCE_ID,
  COMPONENT_SEQUENCE_ID,
  SR_INSTANCE_ID,
  BILL_SEQUENCE_ID,
  ROUTING_SEQUENCE_ID,
  MSC_CL_COLLECTION.v_last_collection_id,
  MSC_CL_COLLECTION.v_current_date,
  MSC_CL_COLLECTION.v_current_user,
  MSC_CL_COLLECTION.v_current_date,
  MSC_CL_COLLECTION.v_current_user
FROM MSC_ST_OPERATION_COMPONENTS msoc
WHERE msoc.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
  AND msoc.DELETED_FLAG= MSC_UTIL.SYS_NO;

Line: 922


lv_sql_stmt1 := ' DELETE MSC_OPERATION_COMPONENTS '
		||'  WHERE PLAN_ID= -1 '
		||'   AND SR_INSTANCE_ID= :SR_INSTANCE_ID ';

Line: 926


  lv_delete_flag := FALSE;

Line: 930


		lv_delete_flag := FALSE;

Line: 935


		lv_delete_flag := FALSE;

Line: 940


		lv_delete_flag := FALSE;

Line: 945


		lv_delete_flag := FALSE;

Line: 950


		lv_delete_flag := FALSE;

Line: 952


if (lv_delete_flag = FALSE) then
EXECUTE IMMEDIATE lv_sql_stmt1 USING c_rec.SR_INSTANCE_ID;

Line: 955


/* bug 988700 fix : change UPDATE to DELETE */
/*
DELETE MSC_OPERATION_COMPONENTS
 WHERE PLAN_ID= -1
   AND BILL_SEQUENCE_ID= NVL( c_rec.BILL_SEQUENCE_ID, BILL_SEQUENCE_ID)
   AND ROUTING_SEQUENCE_ID= NVL( c_rec.ROUTING_SEQUENCE_ID, ROUTING_SEQUENCE_ID)
   AND COMPONENT_SEQUENCE_ID= NVL( c_rec.COMPONENT_SEQUENCE_ID, COMPONENT_SEQUENCE_ID)
   AND OPERATION_SEQUENCE_ID= NVL( c_rec.OPERATION_SEQUENCE_ID, OPERATION_SEQUENCE_ID)
   AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;

Line: 1010


UPDATE MSC_OPERATION_COMPONENTS
   SET PLAN_ID= -1,
       LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
       LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
   WHERE PLAN_ID= -1
   AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
   AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
   AND BILL_SEQUENCE_ID = c_rec.BILL_SEQUENCE_ID
   AND ROUTING_SEQUENCE_ID = c_rec.ROUTING_SEQUENCE_ID
   AND COMPONENT_SEQUENCE_ID= c_rec.COMPONENT_SEQUENCE_ID
   AND OPERATION_SEQUENCE_ID= c_rec.OPERATION_SEQUENCE_ID;

Line: 1026


INSERT INTO MSC_OPERATION_COMPONENTS
( PLAN_ID,
  ORGANIZATION_ID,
  OPERATION_SEQUENCE_ID,
  COMPONENT_SEQUENCE_ID,
  SR_INSTANCE_ID,
  BILL_SEQUENCE_ID,
  ROUTING_SEQUENCE_ID,
  REFRESH_NUMBER,
  LAST_UPDATE_DATE,
  LAST_UPDATED_BY,
  CREATION_DATE,
  CREATED_BY)
VALUES
( -1,
  c_rec.ORGANIZATION_ID,
  c_rec.OPERATION_SEQUENCE_ID,
  c_rec.COMPONENT_SEQUENCE_ID,
  c_rec.SR_INSTANCE_ID,
  c_rec.BILL_SEQUENCE_ID,
  c_rec.ROUTING_SEQUENCE_ID,
  MSC_CL_COLLECTION.v_last_collection_id,
  MSC_CL_COLLECTION.v_current_date,
  MSC_CL_COLLECTION.v_current_user,
  MSC_CL_COLLECTION.v_current_date,
  MSC_CL_COLLECTION.v_current_user);

Line: 1117


SELECT
  msors.ROUTING_SEQUENCE_ID,
  msors.OPERATION_SEQUENCE_ID,
  msors.RESOURCE_SEQ_NUM,
  msors.SCHEDULE_FLAG,
  msors.RESOURCE_OFFSET_PERCENT,
  msors.DEPARTMENT_ID,
  msors.ACTIVITY_GROUP_ID,
  msors.SR_INSTANCE_ID,
  msors.ORGANIZATION_ID
FROM MSC_ST_OPERATION_RESOURCE_SEQS msors
WHERE msors.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
  AND msors.DELETED_FLAG= MSC_UTIL.SYS_NO;

Line: 1132


SELECT
  msors.ROUTING_SEQUENCE_ID,
  msors.OPERATION_SEQUENCE_ID,
  msors.RESOURCE_SEQ_NUM,
  msors.SR_INSTANCE_ID
FROM MSC_ST_OPERATION_RESOURCE_SEQS msors
WHERE msors.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
  AND msors.DELETED_FLAG= MSC_UTIL.SYS_YES;

Line: 1165


       MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_OPERATION_RESOURCE_SEQS', MSC_CL_COLLECTION.v_instance_id, -1);

Line: 1168


       MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_OPERATION_RESOURCE_SEQS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);

Line: 1175


  ' INSERT INTO  '||lv_tbl
  ||' ( PLAN_ID, '
  ||'   ROUTING_SEQUENCE_ID, '
  ||'   OPERATION_SEQUENCE_ID, '
  ||'   RESOURCE_SEQ_NUM, '
  ||'   SCHEDULE_FLAG, '
  ||'   RESOURCE_OFFSET_PERCENT, '
  ||'   DEPARTMENT_ID, '
  ||'   ACTIVITY_GROUP_ID, '
  ||'   SR_INSTANCE_ID, '
  ||'   ORGANIZATION_ID, '
  ||'   REFRESH_NUMBER, '
  ||'   LAST_UPDATE_DATE, '
  ||'   LAST_UPDATED_BY, '
  ||'   CREATION_DATE, '
  ||'   CREATED_BY) '
  ||'SELECT '
  ||'  -1,'
  ||'  msors.ROUTING_SEQUENCE_ID,'
  ||'  msors.OPERATION_SEQUENCE_ID,'
  ||'  msors.RESOURCE_SEQ_NUM,'
  ||'  msors.SCHEDULE_FLAG,'
  ||'  msors.RESOURCE_OFFSET_PERCENT,'
  ||'  msors.DEPARTMENT_ID,'
  ||'  msors.ACTIVITY_GROUP_ID,'
  ||'  msors.SR_INSTANCE_ID,'
  ||'  msors.ORGANIZATION_ID, '
  ||'  :v_last_collection_id,'
  ||'  :v_current_date,'
  ||'  :v_current_user,'
  ||'  :v_current_date,'
  ||'  :v_current_user '
  ||' FROM MSC_ST_OPERATION_RESOURCE_SEQS msors'
  ||' WHERE msors.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id
  ||'   AND msors.DELETED_FLAG= '||MSC_UTIL.SYS_NO;

Line: 1242


        MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'bulk insert failed - operation resource seqs');

Line: 1250


' INSERT INTO  '||lv_tbl
||' ( PLAN_ID, '
||'   ROUTING_SEQUENCE_ID, '
||'   OPERATION_SEQUENCE_ID, '
||'   RESOURCE_SEQ_NUM, '
||'   SCHEDULE_FLAG, '
||'   RESOURCE_OFFSET_PERCENT, '
||'   DEPARTMENT_ID, '
||'   ACTIVITY_GROUP_ID, '
||'   SR_INSTANCE_ID, '
||'   ORGANIZATION_ID, '
||'   REFRESH_NUMBER, '
||'   LAST_UPDATE_DATE, '
||'   LAST_UPDATED_BY, '
||'   CREATION_DATE, '
||'   CREATED_BY) '
||' VALUES '
||' ( -1, '
||'   :ROUTING_SEQUENCE_ID, '
||'   :OPERATION_SEQUENCE_ID, '
||'   :RESOURCE_SEQ_NUM, '
||'   :SCHEDULE_FLAG, '
||'   :RESOURCE_OFFSET_PERCENT, '
||'   :DEPARTMENT_ID, '
||'   :ACTIVITY_GROUP_ID, '
||'   :SR_INSTANCE_ID, '
||'   :ORGANIZATION_ID, '
||'   :v_last_collection_id, '
||'   :v_current_date, '
||'   :v_current_user, '
||'   :v_current_date, '
||'   :v_current_user ) ';

Line: 1290


    DELETE MSC_OPERATION_RESOURCE_SEQS
     WHERE PLAN_ID= -1
       AND ROUTING_SEQUENCE_ID= c_rec.ROUTING_SEQUENCE_ID
       AND OPERATION_SEQUENCE_ID= c_rec.OPERATION_SEQUENCE_ID
       AND RESOURCE_SEQ_NUM= c_rec.RESOURCE_SEQ_NUM
       AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;

Line: 1297


    DELETE MSC_OPERATION_RESOURCE_SEQS
     WHERE PLAN_ID= -1
       AND ROUTING_SEQUENCE_ID= c_rec.ROUTING_SEQUENCE_ID
       AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;

Line: 1316


UPDATE MSC_OPERATION_RESOURCE_SEQS
SET
 SCHEDULE_FLAG= c_rec.SCHEDULE_FLAG,
 RESOURCE_OFFSET_PERCENT= c_rec.RESOURCE_OFFSET_PERCENT,
 DEPARTMENT_ID= c_rec.DEPARTMENT_ID,
 ACTIVITY_GROUP_ID= c_rec.ACTIVITY_GROUP_ID,
 ORGANIZATION_ID=c_rec.ORGANIZATION_ID,
 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
WHERE PLAN_ID= -1
  AND ROUTING_SEQUENCE_ID= c_rec.ROUTING_SEQUENCE_ID
  AND OPERATION_SEQUENCE_ID= c_rec.OPERATION_SEQUENCE_ID
  AND RESOURCE_SEQ_NUM= c_rec.RESOURCE_SEQ_NUM
  AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;

Line: 1421


         'INSERT INTO '||lv_tbl
          ||' SELECT * from MSC_OPERATION_RESOURCE_SEQS'
          ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
          ||' AND plan_id = -1 '
          ||' AND organization_id not '||MSC_UTIL.v_in_org_str;

Line: 1464


SELECT
  msro.ROUTING_SEQUENCE_ID,
  msro.OPERATION_SEQ_NUM,
  msro.OPERATION_SEQUENCE_ID,
  REPLACE(REPLACE(substrb(msro.OPERATION_DESCRIPTION,1,240),v_chr10,'-'),v_chr13,'-') OPERATION_DESCRIPTION,
  msro.EFFECTIVITY_DATE,
  msro.DISABLE_DATE,
  msro.FROM_UNIT_NUMBER,
  msro.TO_UNIT_NUMBER,
  msro.OPTION_DEPENDENT_FLAG,
  msro.OPERATION_TYPE,
  msro.MINIMUM_TRANSFER_QUANTITY,
  msro.YIELD,
  msro.DEPARTMENT_ID,
  msro.DEPARTMENT_CODE,
  msro.OPERATION_LEAD_TIME_PERCENT,
  msro.CUMULATIVE_YIELD,
  msro.REVERSE_CUMULATIVE_YIELD,
  msro.NET_PLANNING_PERCENT,
  msro.SETUP_DURATION,
  msro.TEAR_DOWN_DURATION,
  msro.UOM_CODE,
  msro.STANDARD_OPERATION_CODE,
  msro.STEP_QUANTITY,
  msro.STEP_QUANTITY_UOM,
  msro.DELETED_FLAG,
  msro.SR_INSTANCE_ID,
  msro.ORGANIZATION_ID
 FROM MSC_ST_ROUTING_OPERATIONS msro
WHERE msro.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
  AND msro.DELETED_FLAG= MSC_UTIL.SYS_NO;

Line: 1497


SELECT
  msro.ROUTING_SEQUENCE_ID,
  msro.OPERATION_SEQUENCE_ID,
  msro.SR_INSTANCE_ID
 FROM MSC_ST_ROUTING_OPERATIONS msro
WHERE msro.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
  AND msro.DELETED_FLAG= MSC_UTIL.SYS_YES;

Line: 1529


       MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ROUTING_OPERATIONS', MSC_CL_COLLECTION.v_instance_id, -1);

Line: 1532


       MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ROUTING_OPERATIONS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);

Line: 1538


     ' INSERT INTO '||lv_tbl
     ||' ( PLAN_ID, '
     ||'   ROUTING_SEQUENCE_ID, '
     ||'   OPERATION_SEQ_NUM, '
     ||'   OPERATION_SEQUENCE_ID, '
     ||'   OPERATION_DESCRIPTION, '
     ||'   EFFECTIVITY_DATE, '
     ||'   DISABLE_DATE, '
     ||'   FROM_UNIT_NUMBER, '
     ||'   TO_UNIT_NUMBER, '
     ||'   OPTION_DEPENDENT_FLAG, '
     ||'   OPERATION_TYPE, '
     ||'   MINIMUM_TRANSFER_QUANTITY, '
     ||'   YIELD, '
     ||'   DEPARTMENT_ID, '
     ||'   DEPARTMENT_CODE, '
     ||'   OPERATION_LEAD_TIME_PERCENT, '
     ||'   CUMULATIVE_YIELD, '
     ||'   REVERSE_CUMULATIVE_YIELD, '
     ||'   NET_PLANNING_PERCENT, '
     ||'   SETUP_DURATION, '
     ||'   TEAR_DOWN_DURATION, '
     ||'   UOM_CODE, '
     ||'   STANDARD_OPERATION_CODE, '
     ||'   STEP_QUANTITY, '
     ||'   STEP_QUANTITY_UOM, '
     ||'   SR_INSTANCE_ID, '
     ||'   ORGANIZATION_ID, '
     ||'   REFRESH_NUMBER, '
     ||'   LAST_UPDATE_DATE, '
     ||'   LAST_UPDATED_BY, '
     ||'   CREATION_DATE, '
     ||'   CREATED_BY) '
     ||' SELECT '
     ||'   -1, '
     ||'   msro.ROUTING_SEQUENCE_ID, '
     ||'   msro.OPERATION_SEQ_NUM, '
     ||'   msro.OPERATION_SEQUENCE_ID, '
     ||'   REPLACE(REPLACE(substrb(msro.OPERATION_DESCRIPTION,1,240),:v_chr10,''-''),:v_chr13,''-'') OPERATION_DESCRIPTION, '
     ||'   msro.EFFECTIVITY_DATE, '
     ||'   msro.DISABLE_DATE, '
     ||'   msro.FROM_UNIT_NUMBER, '
     ||'   msro.TO_UNIT_NUMBER, '
     ||'   msro.OPTION_DEPENDENT_FLAG, '
     ||'   msro.OPERATION_TYPE, '
     ||'   msro.MINIMUM_TRANSFER_QUANTITY, '
     ||'   msro.YIELD, '
     ||'   msro.DEPARTMENT_ID, '
     ||'   msro.DEPARTMENT_CODE, '
     ||'   msro.OPERATION_LEAD_TIME_PERCENT, '
     ||'   msro.CUMULATIVE_YIELD, '
     ||'   msro.REVERSE_CUMULATIVE_YIELD, '
     ||'   msro.NET_PLANNING_PERCENT, '
     ||'   msro.SETUP_DURATION, '
     ||'   msro.TEAR_DOWN_DURATION, '
     ||'   msro.UOM_CODE, '
     ||'   msro.STANDARD_OPERATION_CODE, '
     ||'   msro.STEP_QUANTITY, '
     ||'   msro.STEP_QUANTITY_UOM, '
     ||'   msro.SR_INSTANCE_ID, '
     ||'   msro.ORGANIZATION_ID, '
     ||'   :v_last_collection_id, '
     ||'   :v_current_date, '
     ||'   :v_current_user, '
     ||'   :v_current_date, '
     ||'   :v_current_user '
     ||'   FROM MSC_ST_ROUTING_OPERATIONS msro '
     ||'   WHERE msro.SR_INSTANCE_ID= ' || MSC_CL_COLLECTION.v_instance_id
     ||'   AND msro.DELETED_FLAG= ' || MSC_UTIL.SYS_NO;

Line: 1639


          MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'bulk insert failed - routing operations');

Line: 1648


' INSERT INTO '||lv_tbl
||' ( PLAN_ID, '
||'   ROUTING_SEQUENCE_ID, '
||'   OPERATION_SEQ_NUM, '
||'   OPERATION_SEQUENCE_ID, '
||'   OPERATION_DESCRIPTION, '
||'   EFFECTIVITY_DATE, '
||'   DISABLE_DATE, '
||'   FROM_UNIT_NUMBER, '
||'   TO_UNIT_NUMBER, '
||'   OPTION_DEPENDENT_FLAG, '
||'   OPERATION_TYPE, '
||'   MINIMUM_TRANSFER_QUANTITY, '
||'   YIELD, '
||'   DEPARTMENT_ID, '
||'   DEPARTMENT_CODE, '
||'   OPERATION_LEAD_TIME_PERCENT, '
||'   CUMULATIVE_YIELD, '
||'   REVERSE_CUMULATIVE_YIELD, '
||'   NET_PLANNING_PERCENT, '
||'   SETUP_DURATION, '
||'   TEAR_DOWN_DURATION, '
||'   UOM_CODE, '
||'   STANDARD_OPERATION_CODE, '
||'   STEP_QUANTITY, '
||'   STEP_QUANTITY_UOM, '
||'   SR_INSTANCE_ID, '
||'   ORGANIZATION_ID, '
||'   REFRESH_NUMBER, '
||'   LAST_UPDATE_DATE, '
||'   LAST_UPDATED_BY, '
||'   CREATION_DATE, '
||'   CREATED_BY) '
||' VALUES '
||' ( -1, '
||'   :ROUTING_SEQUENCE_ID, '
||'   :OPERATION_SEQ_NUM, '
||'   :OPERATION_SEQUENCE_ID, '
||'   :OPERATION_DESCRIPTION, '
||'   :EFFECTIVITY_DATE, '
||'   :DISABLE_DATE, '
||'   :FROM_UNIT_NUMBER, '
||'   :TO_UNIT_NUMBER, '
||'   :OPTION_DEPENDENT_FLAG, '
||'   :OPERATION_TYPE, '
||'   :MINIMUM_TRANSFER_QUANTITY, '
||'   :YIELD, '
||'   :DEPARTMENT_ID, '
||'   :DEPARTMENT_CODE, '
||'   :OPERATION_LEAD_TIME_PERCENT, '
||'   :CUMULATIVE_YIELD, '
||'   :REVERSE_CUMULATIVE_YIELD, '
||'   :NET_PLANNING_PERCENT, '
||'   :SETUP_DURATION, '
||'   :TEAR_DOWN_DURATION, '
||'   :UOM_CODE, '
||'   :STANDARD_OPERATION_CODE, '
||'   :STEP_QUANTITY, '
||'   :STEP_QUANTITY_UOM, '
||'   :SR_INSTANCE_ID, '
||'   :ORGANIZATION_ID, '
||'   :v_last_collection_id, '
||'   :v_current_date, '
||'   :v_current_user, '
||'   :v_current_date, '
||'   :v_current_user ) ';

Line: 1722


DELETE MSC_ROUTING_OPERATIONS
 WHERE PLAN_ID= -1
   AND ROUTING_SEQUENCE_ID= NVL(c_rec.ROUTING_SEQUENCE_ID,ROUTING_SEQUENCE_ID)
   AND OPERATION_SEQUENCE_ID= NVL(c_rec.OPERATION_SEQUENCE_ID,OPERATION_SEQUENCE_ID)
   AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;

Line: 1738


UPDATE MSC_ROUTING_OPERATIONS
SET
 OPERATION_SEQ_NUM= c_rec.OPERATION_SEQ_NUM,
 OPERATION_DESCRIPTION= c_rec.OPERATION_DESCRIPTION,
 EFFECTIVITY_DATE= c_rec.EFFECTIVITY_DATE,
 DISABLE_DATE= c_rec.DISABLE_DATE,
 FROM_UNIT_NUMBER= c_rec.FROM_UNIT_NUMBER,
 TO_UNIT_NUMBER= c_rec.TO_UNIT_NUMBER,
 OPTION_DEPENDENT_FLAG= c_rec.OPTION_DEPENDENT_FLAG,
 OPERATION_TYPE= c_rec.OPERATION_TYPE,
 MINIMUM_TRANSFER_QUANTITY= c_rec.MINIMUM_TRANSFER_QUANTITY,
 YIELD= c_rec.YIELD,
 DEPARTMENT_ID= c_rec.DEPARTMENT_ID,
 DEPARTMENT_CODE= c_rec.DEPARTMENT_CODE,
 OPERATION_LEAD_TIME_PERCENT= c_rec.OPERATION_LEAD_TIME_PERCENT,
 CUMULATIVE_YIELD= c_rec.CUMULATIVE_YIELD,
 REVERSE_CUMULATIVE_YIELD= c_rec.REVERSE_CUMULATIVE_YIELD,
 NET_PLANNING_PERCENT= c_rec.NET_PLANNING_PERCENT,
 SETUP_DURATION= c_rec.SETUP_DURATION,
 TEAR_DOWN_DURATION= c_rec.TEAR_DOWN_DURATION,
 UOM_CODE= c_rec.UOM_CODE,
 STANDARD_OPERATION_CODE= c_rec.STANDARD_OPERATION_CODE,
 STEP_QUANTITY= c_rec.STEP_QUANTITY,
 STEP_QUANTITY_UOM= c_rec.STEP_QUANTITY_UOM,
 ORGANIZATION_ID = c_rec.ORGANIZATION_ID,
 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
WHERE PLAN_ID= -1
  AND ROUTING_SEQUENCE_ID= c_rec.ROUTING_SEQUENCE_ID
  AND OPERATION_SEQUENCE_ID= c_rec.OPERATION_SEQUENCE_ID
  AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;

Line: 1870


         'INSERT INTO '||lv_tbl
          ||' SELECT * from MSC_ROUTING_OPERATIONS'
          ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
          ||' AND plan_id = -1 '
          ||' AND organization_id not '||MSC_UTIL.v_in_org_str;

Line: 1915


SELECT
  msor.ROUTING_SEQUENCE_ID,
  msor.OPERATION_SEQUENCE_ID,
  msor.RESOURCE_SEQ_NUM,
  msor.RESOURCE_ID,
  msor.ALTERNATE_NUMBER,
  nvl(msor.PRINCIPAL_FLAG,1) PRINCIPAL_FLAG,
  msor.BASIS_TYPE,
  msor.RESOURCE_USAGE,
  msor.MAX_RESOURCE_UNITS,
  msor.RESOURCE_UNITS,
  msor.UOM_CODE,
  msor.RESOURCE_TYPE,
  msor.SR_INSTANCE_ID,
  msor.ORGANIZATION_ID,
  msor.SETUP_ID,		/*ds change change start */
  msor.MINIMUM_CAPACITY,
  msor.MAXIMUM_CAPACITY ,
  msor.orig_resource_seq_num,
  msor.BREAKABLE_ACTIVITY_FLAG  /*ds change change end */
FROM MSC_ST_OPERATION_RESOURCES msor
WHERE msor.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
  AND msor.DELETED_FLAG= MSC_UTIL.SYS_NO;

Line: 1940


SELECT
  msor.ROUTING_SEQUENCE_ID,
  msor.OPERATION_SEQUENCE_ID,
  msor.RESOURCE_SEQ_NUM,
  msor.RESOURCE_ID,
  msor.ALTERNATE_NUMBER,
  msor.SR_INSTANCE_ID
FROM MSC_ST_OPERATION_RESOURCES msor
WHERE msor.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
  AND msor.DELETED_FLAG= MSC_UTIL.SYS_YES;

Line: 1952


SELECT
  msor.ROUTING_SEQUENCE_ID,
  msor.RESOURCE_ID,
  msor.RESOURCE_SEQ_NUM
FROM MSC_ST_OPERATION_RESOURCES msor
WHERE msor.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
  AND msor.DELETED_FLAG= MSC_UTIL.SYS_NO
  AND nvl(msor.RESOURCE_SEQ_NUM,0)=0
  AND msor.ALTERNATE_NUMBER > 0;

Line: 1971


  lv_delete_flag BOOLEAN:= FALSE;

Line: 1988


       MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_OPERATION_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1);

Line: 1991


       MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_OPERATION_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);

Line: 1997


    ' INSERT INTO '||lv_tbl
    ||' ( PLAN_ID, '
    ||'   ROUTING_SEQUENCE_ID, '
    ||'   OPERATION_SEQUENCE_ID, '
    ||'   RESOURCE_SEQ_NUM, '
    ||'   RESOURCE_ID, '
    ||'   ALTERNATE_NUMBER, '
    ||'   PRINCIPAL_FLAG, '
    ||'   BASIS_TYPE, '
    ||'   RESOURCE_USAGE, '
    ||'   MAX_RESOURCE_UNITS, '
    ||'   RESOURCE_UNITS, '
    ||'   UOM_CODE, '
    ||'   RESOURCE_TYPE, '
    ||'   SR_INSTANCE_ID, '
    ||'   ORGANIZATION_ID, '
    ||'   SETUP_ID, '   	     /*ds change change start */
    ||'   MINIMUM_CAPACITY, '
    ||'   MAXIMUM_CAPACITY, '
    ||'   orig_resource_seq_num, '
    ||'   BREAKABLE_ACTIVITY_FLAG, ' /*ds change change end */
    ||'   REFRESH_NUMBER, '
    ||'   LAST_UPDATE_DATE, '
    ||'   LAST_UPDATED_BY, '
    ||'   CREATION_DATE, '
    ||'   CREATED_BY) '
    ||' SELECT '
    ||'   -1, '
    ||'   msor.ROUTING_SEQUENCE_ID, '
    ||'   msor.OPERATION_SEQUENCE_ID, '
    ||'   msor.RESOURCE_SEQ_NUM, '
    ||'   msor.RESOURCE_ID, '
    ||'   msor.ALTERNATE_NUMBER, '
    ||'   nvl(msor.PRINCIPAL_FLAG,1), '
    ||'   msor.BASIS_TYPE, '
    ||'   msor.RESOURCE_USAGE, '
    ||'   msor.MAX_RESOURCE_UNITS, '
    ||'   msor.RESOURCE_UNITS, '
    ||'   msor.UOM_CODE, '
    ||'   msor.RESOURCE_TYPE, '
    ||'   msor.SR_INSTANCE_ID, '
    ||'   msor.ORGANIZATION_ID,'
    ||'   msor.SETUP_ID, '   		  /*ds change change start */
    ||'   msor.MINIMUM_CAPACITY, '
    ||'   msor.MAXIMUM_CAPACITY, '
    ||'   msor.ORIG_RESOURCE_SEQ_NUM, '
    ||'   msor.BREAKABLE_ACTIVITY_FLAG, ' /*ds change change end */
    ||'   :v_last_collection_id, '
    ||'   :v_current_date, '
    ||'   :v_current_user, '
    ||'   :v_current_date, '
    ||'   :v_current_user  '
    ||'   FROM MSC_ST_OPERATION_RESOURCES msor'
    ||'   WHERE msor.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id
    ||'     AND msor.DELETED_FLAG= '||MSC_UTIL.SYS_NO
    ||'     AND NOT(nvl(msor.RESOURCE_SEQ_NUM,0)=0 AND msor.ALTERNATE_NUMBER > 0)';

Line: 2108


        MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'bulk insert failed - operation resources');

Line: 2119


' INSERT INTO '||lv_tbl
||' ( PLAN_ID, '
||'   ROUTING_SEQUENCE_ID, '
||'   OPERATION_SEQUENCE_ID, '
||'   RESOURCE_SEQ_NUM, '
||'   RESOURCE_ID, '
||'   ALTERNATE_NUMBER, '
||'   PRINCIPAL_FLAG, '
||'   BASIS_TYPE, '
||'   RESOURCE_USAGE, '
||'   MAX_RESOURCE_UNITS, '
||'   RESOURCE_UNITS, '
||'   UOM_CODE, '
||'   RESOURCE_TYPE, '
||'   SR_INSTANCE_ID, '
||'   ORGANIZATION_ID, '
||'   SETUP_ID, '   		 /*ds change change start */
||'   MINIMUM_CAPACITY, '
||'   MAXIMUM_CAPACITY, '
||'   orig_resource_seq_num, '
||'   BREAKABLE_ACTIVITY_FLAG, ' /*ds change change end */
||'   REFRESH_NUMBER, '
||'   LAST_UPDATE_DATE, '
||'   LAST_UPDATED_BY, '
||'   CREATION_DATE, '
||'   CREATED_BY) '
||' VALUES '
||' ( -1, '
||'   :ROUTING_SEQUENCE_ID, '
||'   :OPERATION_SEQUENCE_ID, '
||'   :RESOURCE_SEQ_NUM, '
||'   :RESOURCE_ID, '
||'   :ALTERNATE_NUMBER, '
||'   :PRINCIPAL_FLAG, '
||'   :BASIS_TYPE, '
||'   :RESOURCE_USAGE, '
||'   :MAX_RESOURCE_UNITS, '
||'   :RESOURCE_UNITS, '
||'   :UOM_CODE, '
||'   :RESOURCE_TYPE, '
||'   :SR_INSTANCE_ID, '
||'   :ORGANIZATION_ID,'
||'   :SETUP_ID, '   		 /*ds change change start */
||'   :MINIMUM_CAPACITY, '
||'   :MAXIMUM_CAPACITY, '
||'   :orig_resource_seq_num, '
||'   :BREAKABLE_ACTIVITY_FLAG, ' /*ds change change end */
||'   :v_last_collection_id, '
||'   :v_current_date, '
||'   :v_current_user, '
||'   :v_current_date, '
||'   :v_current_user ) ';

Line: 2177


		lv_delete_flag := FALSE;

Line: 2178


lv_sql_stmt1 := ' UPDATE MSC_OPERATION_RESOURCES '
		||'   SET RESOURCE_USAGE= 0, '
		||'   REFRESH_NUMBER=   :v_last_collection_id, '
		||'   LAST_UPDATE_DATE= :v_current_date, '
		||'   LAST_UPDATED_BY=  :v_current_user '
		||'  WHERE PLAN_ID= -1 '
		||' AND SR_INSTANCE_ID= :SR_INSTANCE_ID ';

Line: 2188


		lv_delete_flag := TRUE;

Line: 2193


		lv_delete_flag := TRUE;

Line: 2198


		lv_delete_flag := TRUE;

Line: 2203


		lv_delete_flag := TRUE;

Line: 2208


		lv_delete_flag := TRUE;

Line: 2212


If (lv_delete_flag) then
    EXECUTE IMMEDIATE lv_sql_stmt1 USING MSC_CL_COLLECTION.v_last_collection_id, MSC_CL_COLLECTION.v_current_date, MSC_CL_COLLECTION.v_current_user, c_rec.SR_INSTANCE_ID ;

Line: 2228


UPDATE MSC_OPERATION_RESOURCES
   SET RESOURCE_USAGE= 0,
       REFRESH_NUMBER= v_last_collection_id,
       LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
       LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
 WHERE PLAN_ID= -1
   AND ROUTING_SEQUENCE_ID= NVL(c_rec.ROUTING_SEQUENCE_ID,ROUTING_SEQUENCE_ID)
   AND OPERATION_SEQUENCE_ID= NVL(c_rec.OPERATION_SEQUENCE_ID,OPERATION_SEQUENCE_ID)
   AND RESOURCE_SEQ_NUM= NVL(c_rec.RESOURCE_SEQ_NUM,RESOURCE_SEQ_NUM)
   AND RESOURCE_ID= NVL(c_rec.RESOURCE_ID,RESOURCE_ID)
   AND ALTERNATE_NUMBER= NVL( c_rec.ALTERNATE_NUMBER,ALTERNATE_NUMBER)
   AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;

Line: 2254


UPDATE MSC_OPERATION_RESOURCES
SET
 PRINCIPAL_FLAG= c_rec.PRINCIPAL_FLAG,
 BASIS_TYPE= c_rec.BASIS_TYPE,
 RESOURCE_USAGE= c_rec.RESOURCE_USAGE,
 MAX_RESOURCE_UNITS= c_rec.MAX_RESOURCE_UNITS,
 RESOURCE_UNITS= c_rec.RESOURCE_UNITS,
 UOM_CODE= c_rec.UOM_CODE,
 RESOURCE_TYPE= c_rec.RESOURCE_TYPE,
 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
 ALTERNATE_NUMBER= c_rec.ALTERNATE_NUMBER,
 ORGANIZATION_ID= c_rec.ORGANIZATION_ID  ,
 SETUP_ID= c_rec.SETUP_ID  ,               /* ds change change start*/
 MINIMUM_CAPACITY = c_rec.MINIMUM_CAPACITY  ,
 MAXIMUM_CAPACITY = c_rec.MAXIMUM_CAPACITY ,
 orig_resource_seq_num = c_rec.orig_resource_seq_num ,
 BREAKABLE_ACTIVITY_FLAG = c_rec.BREAKABLE_ACTIVITY_FLAG  /* ds change change end */
WHERE PLAN_ID= -1
  AND ROUTING_SEQUENCE_ID= c_rec.ROUTING_SEQUENCE_ID
  AND OPERATION_SEQUENCE_ID= c_rec.OPERATION_SEQUENCE_ID
  AND RESOURCE_SEQ_NUM= c_rec.RESOURCE_SEQ_NUM
  AND RESOURCE_ID= c_rec.RESOURCE_ID
/*  AND ALTERNATE_NUMBER= c_rec.ALTERNATE_NUMBER
    Moving this to update */
  AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;

Line: 2404


         'INSERT INTO '||lv_tbl
          ||' SELECT * from MSC_OPERATION_RESOURCES'
          ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
          ||' AND plan_id = -1 '
          ||' AND organization_id not '||MSC_UTIL.v_in_org_str;