APPS.MSC_CL_RPO_ODS_LOAD SQL Statements

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

Line: 29

   lv_insert_stmt  VARCHAR2(10240);

Line: 112

  /* 2201791 - select substr(order_number,1,62) since order_number is
   defined as varchar(62) in msc_demands table */

lv_del_stmt :=
              'Select  mshr.repair_line_id,
          	           T1.Inventory_item_id ,
          	           mshr.Organization_id ,
          	           mshr.Origination_type ,
                       mshr.SR_INSTANCE_ID
	            From     MSC_ST_DEMANDS mshr ,
                       msc_item_id_lid  t1
	              Where  mshr.sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
	                     ||' And  mshr.ro_status_code=''C''
	                     And  mshr.origination_type= 77
                       AND t1.SR_INVENTORY_ITEM_ID(+)= mshr.inventory_item_id
                       AND t1.sr_instance_id(+)= mshr.sr_instance_id ';

Line: 135

lv_del_stmt2 := 'SELECT
                   mshr.REPAIR_LINE_ID,
                   mshr.SR_INSTANCE_ID,
                   t1.INVENTORY_ITEM_ID,
                   mshr.ORGANIZATION_ID,
                   mshr.OPERATION_SEQ_NUM,
                   mshr.ORIGINATION_TYPE,
                   mshr.WIP_ENTITY_ID,
                   mshr.wip_entity_name
             FROM MSC_ST_DEMANDS mshr,
                  msc_item_id_lid  t1
            WHERE mshr.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id
            ||' AND mshr.DELETED_FLAG= '||MSC_UTIL.SYS_YES
            ||' AND mshr.ORIGINATION_TYPE=77
                AND t1.SR_INVENTORY_ITEM_ID(+)= mshr.inventory_item_id
                AND t1.sr_instance_id(+)= mshr.sr_instance_id ';

Line: 160

'SELECT'
||'   -1, MSC_DEMANDS_S.nextval, '
||'   NVL(ms.TRANSACTION_ID,-1) DISPOSITION_ID,'
||'   t1.INVENTORY_ITEM_ID,'
||'   msd.ORGANIZATION_ID,'
||'   t2.INVENTORY_ITEM_ID USING_ASSEMBLY_ITEM_ID,'
||'   nvl(msd.USING_ASSEMBLY_DEMAND_DATE,ms.new_schedule_date),'
||'   msd.USING_REQUIREMENT_QUANTITY,'
||'   msd.QUANTITY_PER_ASSEMBLY,'
||'   msd.QUANTITY_ISSUED,'
||'   msd.ASSEMBLY_DEMAND_COMP_DATE,'
||'   msd.DEMAND_TYPE,'
||'   msd.ORIGINATION_TYPE,'
||'   msd.SOURCE_ORGANIZATION_ID,'
||'   msd.RESERVATION_ID,'
||'   msd.OPERATION_SEQ_NUM,'
||'   msd.DEMAND_CLASS,'
||'   msd.REPETITIVE_SCHEDULE_ID,'
||'   msd.SR_INSTANCE_ID,'
||'   msd.PROJECT_ID,'
||'   msd.TASK_ID,'
||'   msd.PLANNING_GROUP,'
||'   msd.END_ITEM_UNIT_NUMBER, '
||'   REPLACE(REPLACE(substr(msd.ORDER_NUMBER,1,62),:v_chr10),:v_chr13) ORDER_NUMBER,'
||'   REPAIR_LINE_ID ,'
||'   msd.WIP_ENTITY_ID,'
||'   msd.WIP_ENTITY_NAME,'
||'   msd.WIP_STATUS_CODE,'
||'   msd.WIP_SUPPLY_TYPE,'
||'   t3.inventory_item_id  ASSET_ITEM_ID,'   /* ds change change*/
||'   msd.ASSET_SERIAL_NUMBER,'  /* ds change change*/
||'   msd.COMPONENT_SCALING_TYPE,' /* Discrete Mfg Enahancements Bug 4492736 */
||'   msd.COMPONENT_YIELD_FACTOR,' /* Discrete Mfg Enahancements Bug 4479743 */
||    lv_data_sql_stmt
||'   :v_last_collection_id,'
||'   :v_current_date,'
||'   :v_current_user,'
||'   :v_current_date,'
||'   :v_current_user '
||' FROM MSC_ITEM_ID_LID t1,'
||'      MSC_ITEM_ID_LID t2,'
||'      MSC_ITEM_ID_LID t3,'
      || lv_supplies_tbl||' ms,'
||'      MSC_ST_DEMANDS msd'
||' WHERE msd.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id
||'  AND msd.ORIGINATION_TYPE = 77'   /* 50 eam demand: ds change change SRP Change 5909379*/
||'  AND msd.DELETED_FLAG= '||MSC_UTIL.SYS_NO
||'  AND t1.SR_INVENTORY_ITEM_ID= msd.inventory_item_id'
||'  AND t1.sr_instance_id= msd.SR_INSTANCE_ID'
||'  AND t2.SR_INVENTORY_ITEM_ID= msd.using_assembly_item_id'
||'  AND t2.sr_instance_id= msd.SR_INSTANCE_ID'
||'  AND t3.SR_INVENTORY_ITEM_ID (+)= msd.ASSET_ITEM_ID'
||'  AND t3.sr_instance_id (+) = msd.SR_INSTANCE_ID'
||'  AND ms.sr_instance_id= msd.SR_INSTANCE_ID'
||'  AND ms.ORGANIZATION_ID= msd.ORGANIZATION_ID'
||'  AND ms.DISPOSITION_ID= msd.repair_line_id '
||'  AND ms.plan_id=-1'
||'  AND ms.ORDER_TYPE= 75'; /* ds change change*/

Line: 220

'INSERT /*+ APPEND */  INTO '||lv_tbl
||'(  PLAN_ID,'
||'   DEMAND_ID,'
||'   DISPOSITION_ID,'
||'   INVENTORY_ITEM_ID,'
||'   ORGANIZATION_ID,'
||'   USING_ASSEMBLY_ITEM_ID,'
||'   USING_ASSEMBLY_DEMAND_DATE,'
||'   USING_REQUIREMENT_QUANTITY,'
||'   QUANTITY_PER_ASSEMBLY,'
||'   ISSUED_QUANTITY,'
||'   ASSEMBLY_DEMAND_COMP_DATE,'
||'   DEMAND_TYPE,'
||'   ORIGINATION_TYPE,'
||'   SOURCE_ORGANIZATION_ID,'
||'   RESERVATION_ID,'
||'   OP_SEQ_NUM,'
||'   DEMAND_CLASS,'
||'   REPETITIVE_SCHEDULE_ID,'
||'   SR_INSTANCE_ID,'
||'   PROJECT_ID,'
||'   TASK_ID,'
||'   PLANNING_GROUP,'
||'   UNIT_NUMBER,'
||'   ORDER_NUMBER,'
||'   REPAIR_LINE_ID,'
||'   WIP_ENTITY_ID,'
||'   WIP_ENTITY_NAME,'
||'   WIP_STATUS_CODE,'
||'   WIP_SUPPLY_TYPE,'
||'   ASSET_ITEM_ID,'
||'   ASSET_SERIAL_NUMBER,'
||'   COMPONENT_SCALING_TYPE,'
||'   COMPONENT_YIELD_FACTOR,'
||    lv_sel_sql_stmt
||'   REFRESH_NUMBER,'
||'   LAST_UPDATE_DATE,'
||'   LAST_UPDATED_BY,'
||'   CREATION_DATE,'
||'   CREATED_BY)'
|| lv_cursor_stmt;

Line: 284

       	Delete from msc_demands
      	 WHERE PLAN_ID=  -1
         	AND SR_INSTANCE_ID=    lv_SR_INSTANCE_ID
         	AND ORIGINATION_TYPE=  lv_ORIGINATION_TYPE
         	AND REPAIR_LINE_ID=    lv_REPAIR_LINE_ID
          AND ORGANIZATION_ID =  lv_ORGANIZATION_ID
         	AND INVENTORY_ITEM_ID=  NVL(lv_INVENTORY_ITEM_ID,INVENTORY_ITEM_ID);

Line: 318

          Delete from msc_demands
      	  WHERE PLAN_ID=  -1
         	AND SR_INSTANCE_ID=    lv_SR_INSTANCE_ID
         	AND ORIGINATION_TYPE=  lv_ORIGINATION_TYPE
         	AND REPAIR_LINE_ID=    lv_REPAIR_LINE_ID
          AND OP_SEQ_NUM=        NVL(lv_OPERATION_SEQ_NUM,OP_SEQ_NUM)
          AND ORGANIZATION_ID =  lv_ORGANIZATION_ID
         	AND INVENTORY_ITEM_ID= NVL(lv_INVENTORY_ITEM_ID,INVENTORY_ITEM_ID)
          AND  WIP_ENTITY_NAME   = lv_WIP_ENTITY_NAME  ;

Line: 328

      	 Delete from msc_demands
      	 WHERE PLAN_ID=  -1
         	AND SR_INSTANCE_ID=    lv_SR_INSTANCE_ID
         	AND ORIGINATION_TYPE=  lv_ORIGINATION_TYPE
         	AND REPAIR_LINE_ID=    lv_REPAIR_LINE_ID
          AND OP_SEQ_NUM=        NVL(lv_OPERATION_SEQ_NUM,OP_SEQ_NUM)
          AND ORGANIZATION_ID =  lv_ORGANIZATION_ID
         	AND INVENTORY_ITEM_ID= NVL(lv_INVENTORY_ITEM_ID,INVENTORY_ITEM_ID)
          AND  WIP_ENTITY_ID   = lv_WIP_ENTITY_ID  ;

Line: 345

       /* Opening The cursor ... Perofrom Update ... If not found the n inser ... row operation  */


      OPEN c2 FOR lv_cursor_stmt USING MSC_CL_COLLECTION.v_chr10,
                                 MSC_CL_COLLECTION.v_chr13,
                                 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: 405

           Update MSC_DEMANDS
            Set
              USING_ASSEMBLY_ITEM_ID     = lv_USING_ASSEMBLY_ITEM_ID      ,
              USING_ASSEMBLY_DEMAND_DATE = lv_USING_ASSEMBLY_DEMAND_DATE  ,
              USING_REQUIREMENT_QUANTITY = lv_USING_REQUIREMENT_QUANTITY  ,
              QUANTITY_PER_ASSEMBLY = lv_QUANTITY_PER_ASSEMBLY       ,
              ISSUED_QUANTITY       = lv_QUANTITY_ISSUED             ,
              ASSEMBLY_DEMAND_COMP_DATE = lv_ASSEMBLY_DEMAND_COMP_DATE   ,
              DEMAND_TYPE              = lv_DEMAND_TYPE             ,
              SOURCE_ORGANIZATION_ID   = lv_SOURCE_ORGANIZATION_ID  ,
              RESERVATION_ID           = lv_RESERVATION_ID          ,
              DEMAND_CLASS             = lv_DEMAND_CLASS            ,
              REPETITIVE_SCHEDULE_ID   = lv_REPETITIVE_SCHEDULE_ID  ,
              PROJECT_ID   = lv_PROJECT_ID              ,
              TASK_ID      = lv_TASK_ID                 ,
              PLANNING_GROUP    = lv_PLANNING_GROUP          ,
              ORDER_NUMBER      = lv_ORDER_NUMBER            ,
              WIP_STATUS_CODE   = lv_WIP_STATUS_CODE         ,
              WIP_SUPPLY_TYPE   = lv_WIP_SUPPLY_TYPE         ,
              ASSET_ITEM_ID          = lv_ASSET_ITEM_ID	          ,
              ASSET_SERIAL_NUMBER    = lv_ASSET_SERIAL_NUMBER     ,
              COMPONENT_SCALING_TYPE = lv_COMPONENT_SCALING_TYPE  ,
              COMPONENT_YIELD_FACTOR = lv_COMPONENT_YIELD_FACTOR
            WHERE PLAN_ID=  -1
               AND SR_INSTANCE_ID=    lv_SR_INSTANCE_ID
               AND ORIGINATION_TYPE=  lv_ORIGINATION_TYPE
               AND DISPOSITION_ID=    lv_DISPOSITION_ID
               AND OP_SEQ_NUM=        lv_OPERATION_SEQ_NUM
               AND ORGANIZATION_ID =  lv_ORGANIZATION_ID
               AND INVENTORY_ITEM_ID= lv_INVENTORY_ITEM_ID
            	 AND  WIP_ENTITY_NAME   = lv_WIP_ENTITY_NAME  ;

Line: 437

              Update MSC_DEMANDS
            Set
              USING_ASSEMBLY_ITEM_ID     = lv_USING_ASSEMBLY_ITEM_ID      ,
              USING_ASSEMBLY_DEMAND_DATE = lv_USING_ASSEMBLY_DEMAND_DATE  ,
              USING_REQUIREMENT_QUANTITY = lv_USING_REQUIREMENT_QUANTITY  ,
              QUANTITY_PER_ASSEMBLY = lv_QUANTITY_PER_ASSEMBLY       ,
              ISSUED_QUANTITY       = lv_QUANTITY_ISSUED             ,
              ASSEMBLY_DEMAND_COMP_DATE = lv_ASSEMBLY_DEMAND_COMP_DATE   ,
              DEMAND_TYPE              = lv_DEMAND_TYPE             ,
              SOURCE_ORGANIZATION_ID   = lv_SOURCE_ORGANIZATION_ID  ,
              RESERVATION_ID           = lv_RESERVATION_ID          ,
              DEMAND_CLASS             = lv_DEMAND_CLASS            ,
              REPETITIVE_SCHEDULE_ID   = lv_REPETITIVE_SCHEDULE_ID  ,
              PROJECT_ID   = lv_PROJECT_ID              ,
              TASK_ID      = lv_TASK_ID                 ,
              PLANNING_GROUP    = lv_PLANNING_GROUP          ,
              ORDER_NUMBER      = lv_ORDER_NUMBER            ,
              WIP_STATUS_CODE   = lv_WIP_STATUS_CODE         ,
              WIP_SUPPLY_TYPE   = lv_WIP_SUPPLY_TYPE         ,
              ASSET_ITEM_ID          = lv_ASSET_ITEM_ID	          ,
              ASSET_SERIAL_NUMBER    = lv_ASSET_SERIAL_NUMBER     ,
              COMPONENT_SCALING_TYPE = lv_COMPONENT_SCALING_TYPE  ,
              COMPONENT_YIELD_FACTOR = lv_COMPONENT_YIELD_FACTOR
            WHERE PLAN_ID=  -1
               AND SR_INSTANCE_ID=    lv_SR_INSTANCE_ID
               AND ORIGINATION_TYPE=  lv_ORIGINATION_TYPE
               AND DISPOSITION_ID=    lv_DISPOSITION_ID
               AND OP_SEQ_NUM=        lv_OPERATION_SEQ_NUM
               AND ORGANIZATION_ID =  lv_ORGANIZATION_ID
               AND INVENTORY_ITEM_ID= lv_INVENTORY_ITEM_ID
            	 AND  WIP_ENTITY_ID   = lv_WIP_ENTITY_ID  ;

Line: 472

                      lv_insert_stmt:=
                      'INSERT INTO '||lv_tbl
                      ||'(  PLAN_ID,'
                      ||'   DEMAND_ID,'
                      ||'   INVENTORY_ITEM_ID,'
                      ||'   ORGANIZATION_ID,'
                      ||'   USING_ASSEMBLY_ITEM_ID,'
                      ||'   USING_ASSEMBLY_DEMAND_DATE,'
                      ||'   USING_REQUIREMENT_QUANTITY,'
                      ||'   QUANTITY_PER_ASSEMBLY,'
                      ||'   ISSUED_QUANTITY,'
                      ||'   ASSEMBLY_DEMAND_COMP_DATE,'
                      ||'   DEMAND_TYPE,'
                      ||'   ORIGINATION_TYPE,'
                      ||'   SOURCE_ORGANIZATION_ID,'
                      ||'   DISPOSITION_ID,'
                      ||'   RESERVATION_ID,'
                      ||'   OP_SEQ_NUM,'
                      ||'   DEMAND_CLASS,'
                      ||'   SR_INSTANCE_ID,'
                      ||'   PROJECT_ID,'
                      ||'   TASK_ID,'
                      ||'   PLANNING_GROUP,'
                      ||'   UNIT_NUMBER,'
                      ||'   ORDER_NUMBER,'
                      ||'   REPAIR_LINE_ID,'
                      ||'   WIP_ENTITY_ID,'
                      ||'   WIP_ENTITY_NAME,'
                      ||'   WIP_STATUS_CODE,'
                      ||'   WIP_SUPPLY_TYPE,'
                      ||'   REPETITIVE_SCHEDULE_ID,'
                      ||'   ASSET_ITEM_ID,'
                      ||'   ASSET_SERIAL_NUMBER,'
                      ||'   COMPONENT_SCALING_TYPE,'
                      ||'   COMPONENT_YIELD_FACTOR,'
                      ||'   ITEM_TYPE_ID,'
                      ||'   ITEM_TYPE_VALUE,'
                      ||'   REFRESH_NUMBER,'
                      ||'   LAST_UPDATE_DATE,'
                      ||'   LAST_UPDATED_BY,'
                      ||'   CREATION_DATE,'
                      ||'   CREATED_BY)'
                      ||'VALUES'
                      ||'(  -1,'
                      ||'   MSC_DEMANDS_S.nextval,'
                      ||'   :INVENTORY_ITEM_ID,'
                      ||'   :ORGANIZATION_ID,'
                      ||'   :USING_ASSEMBLY_ITEM_ID,'
                      ||'   :USING_ASSEMBLY_DEMAND_DATE,'
                      ||'   :USING_REQUIREMENT_QUANTITY,'
                      ||'   :QUANTITY_PER_ASSEMBLY,'
                      ||'   :ISSUED_QUANTITY,'
                      ||'   :ASSEMBLY_DEMAND_COMP_DATE,'
                      ||'   :DEMAND_TYPE,'
                      ||'   :ORIGINATION_TYPE,'
                      ||'   :SOURCE_ORGANIZATION_ID,'
                      ||'   :DISPOSITION_ID,'
                      ||'   :RESERVATION_ID,'
                      ||'   :OPERATION_SEQ_NUM,'
                      ||'   :DEMAND_CLASS,'
                      ||'   :SR_INSTANCE_ID,'
                      ||'   :PROJECT_ID,'
                      ||'   :TASK_ID,'
                      ||'   :PLANNING_GROUP,'
                      ||'   :END_ITEM_UNIT_NUMBER, '
                      ||'   :ORDER_NUMBER,'
                      ||'   :REPAIR_LINE_ID,'
                      ||'   :WIP_ENTITY_ID,'
                      ||'   :WIP_ENTITY_NAME,'
                      ||'   :WIP_STATUS_CODE,'
                      ||'   :WIP_SUPPLY_TYPE,'
                      ||'   :REPETITIVE_SCHEDULE_ID,'
                      ||'   :ASSET_ITEM_ID,'
                      ||'   :ASSET_SERIAL_NUMBER,'
                      ||'   :COMPONENT_SCALING_TYPE,'
                      ||'   :COMPONENT_YIELD_FACTOR,'
                      ||'   :ITEM_TYPE_ID,'
                      ||'   :ITEM_TYPE_VALUE,'
                      ||'   :v_last_collection_id,'
                      ||'   :v_current_date,'
                      ||'   :v_current_user,'
                      ||'   :v_current_date,'
                      ||'   :v_current_user )';

Line: 556

                      EXECUTE IMMEDIATE lv_insert_stmt
                        USING
                           lv_INVENTORY_ITEM_ID,
                           lv_ORGANIZATION_ID,
                           lv_USING_ASSEMBLY_ITEM_ID,
                           lv_USING_ASSEMBLY_DEMAND_DATE,
                           lv_USING_REQUIREMENT_QUANTITY,
                           lv_QUANTITY_PER_ASSEMBLY,
                           lv_QUANTITY_ISSUED,
                           lv_ASSEMBLY_DEMAND_COMP_DATE,
                           lv_DEMAND_TYPE,
                           lv_ORIGINATION_TYPE,
                           lv_SOURCE_ORGANIZATION_ID,
                           lv_DISPOSITION_ID,
                           lv_RESERVATION_ID,
                           lv_OPERATION_SEQ_NUM,
                           lv_DEMAND_CLASS,
                           lv_SR_INSTANCE_ID,
                           lv_PROJECT_ID,
                           lv_TASK_ID,
                           lv_PLANNING_GROUP,
                           lv_END_ITEM_UNIT_NUMBER,
                           lv_ORDER_NUMBER,
                           lv_REPAIR_LINE_ID,
                           lv_WIP_ENTITY_ID,
                           lv_WIP_ENTITY_NAME,
                           lv_WIP_STATUS_CODE,
                           lv_WIP_SUPPLY_TYPE,
                           lv_REPETITIVE_SCHEDULE_ID,
                           lv_ASSET_ITEM_ID,
                           lv_ASSET_SERIAL_NUMBER,
                           lv_COMPONENT_SCALING_TYPE,
                           lv_COMPONENT_YIELD_FACTOR,
                           lv_ITEM_TYPE_ID,
                           lv_ITEM_TYPE_VALUE,
                           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: 665

   SELECT msd.WIP_ENTITY_ID,
           msd.OPERATION_SEQ_NUM,
           t1.INVENTORY_ITEM_ID,
           msd.ORIGINATION_TYPE,
           msd.SR_INSTANCE_ID,
           msd.ORGANIZATION_ID
      FROM MSC_ITEM_ID_LID t1,
           MSC_ST_DEMANDS msd
     WHERE msd.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
       AND msd.ORIGINATION_TYPE = 77
       AND msd.DELETED_FLAG= MSC_UTIL.SYS_YES
       AND t1.SR_INVENTORY_ITEM_ID(+)= msd.inventory_item_id
       AND t1.sr_instance_id(+)= MSC_CL_COLLECTION.v_instance_id;*/

Line: 708

   lv_DELETED_FLAG     NUMBER;

Line: 716

 lv_del_stmt := 'SELECT msd1.WIP_ENTITY_ID,
                 msd1.OPERATION_SEQ_NUM,
                 t1.INVENTORY_ITEM_ID,
                 msd1.ORIGINATION_TYPE,
                 msd1.SR_INSTANCE_ID,
                 msd1.ORGANIZATION_ID
            FROM MSC_ITEM_ID_LID t1,
                 MSC_ST_DEMANDS msd1
           WHERE msd1.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id
             ||' AND msd1.ORIGINATION_TYPE = 77
                 AND msd1.DELETED_FLAG= '|| MSC_UTIL.SYS_YES
             ||' AND t1.SR_INVENTORY_ITEM_ID(+)= msd1.inventory_item_id
                 AND t1.sr_instance_id(+)=  '||MSC_CL_COLLECTION.v_instance_id ;

Line: 751

   UPDATE MSC_DEMANDS
       SET USING_REQUIREMENT_QUANTITY= 0,
           DAILY_DEMAND_RATE= 0,
           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 SR_INSTANCE_ID=    lv_SR_INSTANCE_ID
       AND ORIGINATION_TYPE=  lv_ORIGINATION_TYPE
       AND WIP_ENTITY_ID=     lv_WIP_ENTITY_ID
       AND OP_SEQ_NUM=        NVL(lv_OPERATION_SEQ_NUM,OP_SEQ_NUM)
       AND ORGANIZATION_ID =  lv_ORGANIZATION_ID
       AND INVENTORY_ITEM_ID= NVL(lv_INVENTORY_ITEM_ID,INVENTORY_ITEM_ID);

Line: 795

'SELECT'
||'   -1, MSC_DEMANDS_S.nextval, '
||'   NVL(ms.TRANSACTION_ID,-1) DISPOSITION_ID,'
||'   t1.INVENTORY_ITEM_ID,'
||'   msd.ORGANIZATION_ID,'
||'   t2.INVENTORY_ITEM_ID USING_ASSEMBLY_ITEM_ID,'
||'   msd.USING_ASSEMBLY_DEMAND_DATE,'
||'   msd.USING_REQUIREMENT_QUANTITY,'
||'   msd.QUANTITY_PER_ASSEMBLY,'
||'   msd.QUANTITY_ISSUED,'
||'   msd.ASSEMBLY_DEMAND_COMP_DATE,'
||'   msd.DEMAND_TYPE,'
||'   msd.ORIGINATION_TYPE,'
||'   msd.SOURCE_ORGANIZATION_ID,'
||'   msd.RESERVATION_ID,'
||'   msd.OPERATION_SEQ_NUM,'
||'   msd.DEMAND_CLASS,'
||'   msd.REPETITIVE_SCHEDULE_ID,'
||'   msd.SR_INSTANCE_ID,'
||'   msd.PROJECT_ID,'
||'   msd.TASK_ID,'
||'   msd.PLANNING_GROUP,'
||'   msd.END_ITEM_UNIT_NUMBER, '
||'   REPLACE(REPLACE(substr(msd.ORDER_NUMBER,1,62),:v_chr10),:v_chr13) ORDER_NUMBER,'
||'   msd.WIP_ENTITY_ID,'
||'   msd.WIP_STATUS_CODE,'
||'   msd.WIP_SUPPLY_TYPE,'
||'   t3.inventory_item_id  ASSET_ITEM_ID,'   /* ds change change*/
||'   msd.ASSET_SERIAL_NUMBER,'  /* ds change change*/
||'   msd.COMPONENT_SCALING_TYPE,' /* Discrete Mfg Enahancements Bug 4492736 */
||'   msd.COMPONENT_YIELD_FACTOR,' /* Discrete Mfg Enahancements Bug 4479743 */
||    lv_data_sql_stmt
||'   :v_last_collection_id,'
||'   :v_current_date,'
||'   :v_current_user,'
||'   :v_current_date,'
||'   :v_current_user '
||' FROM MSC_ITEM_ID_LID t1,'
||'      MSC_ITEM_ID_LID t2,'
||'      MSC_ITEM_ID_LID t3,'
      || lv_supplies_tbl||' ms,'
||'      MSC_ST_DEMANDS msd'
||' WHERE msd.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id
||'  AND msd.ORIGINATION_TYPE = 77'   /* 50 eam demand: ds change change SRP Change 5909379*/
||'  AND msd.DELETED_FLAG= '||MSC_UTIL.SYS_NO
||'  AND t1.SR_INVENTORY_ITEM_ID= msd.inventory_item_id'
||'  AND t1.sr_instance_id= msd.SR_INSTANCE_ID'
||'  AND t2.SR_INVENTORY_ITEM_ID= msd.using_assembly_item_id'
||'  AND t2.sr_instance_id= msd.SR_INSTANCE_ID'
||'  AND t3.SR_INVENTORY_ITEM_ID (+)= msd.ASSET_ITEM_ID'
||'  AND t3.sr_instance_id (+) = msd.SR_INSTANCE_ID'
||'  AND ms.sr_instance_id= msd.SR_INSTANCE_ID'
||'  AND ms.ORGANIZATION_ID= msd.ORGANIZATION_ID'
||'  AND ms.DISPOSITION_ID= msd.wip_entity_id '
||'  AND ms.plan_id=-1'
||'  AND ms.ORDER_TYPE= 86'; /* ds change change*/

Line: 854

'INSERT /*+ APPEND */  INTO '||lv_tbl
||'(  PLAN_ID,'
||'   DEMAND_ID,'
||'   DISPOSITION_ID,'
||'   INVENTORY_ITEM_ID,'
||'   ORGANIZATION_ID,'
||'   USING_ASSEMBLY_ITEM_ID,'
||'   USING_ASSEMBLY_DEMAND_DATE,'
||'   USING_REQUIREMENT_QUANTITY,'
||'   QUANTITY_PER_ASSEMBLY,'
||'   ISSUED_QUANTITY,'
||'   ASSEMBLY_DEMAND_COMP_DATE,'
||'   DEMAND_TYPE,'
||'   ORIGINATION_TYPE,'
||'   SOURCE_ORGANIZATION_ID,'
||'   RESERVATION_ID,'
||'   OP_SEQ_NUM,'
||'   DEMAND_CLASS,'
||'   REPETITIVE_SCHEDULE_ID,'
||'   SR_INSTANCE_ID,'
||'   PROJECT_ID,'
||'   TASK_ID,'
||'   PLANNING_GROUP,'
||'   UNIT_NUMBER,'
||'   ORDER_NUMBER,'
||'   WIP_ENTITY_ID,'
||'   WIP_STATUS_CODE,'
||'   WIP_SUPPLY_TYPE,'
||'   ASSET_ITEM_ID,'
||'   ASSET_SERIAL_NUMBER,'
||'   COMPONENT_SCALING_TYPE,'
||'   COMPONENT_YIELD_FACTOR,'
||    lv_sel_sql_stmt
||'   REFRESH_NUMBER,'
||'   LAST_UPDATE_DATE,'
||'   LAST_UPDATED_BY,'
||'   CREATION_DATE,'
||'   CREATED_BY)'
|| lv_cursor_stmt;

Line: 930

    'INSERT INTO '||lv_tbl
    ||'(  PLAN_ID,'
    ||'   DEMAND_ID,'
    ||'   INVENTORY_ITEM_ID,'
    ||'   ORGANIZATION_ID,'
    ||'   USING_ASSEMBLY_ITEM_ID,'
    ||'   USING_ASSEMBLY_DEMAND_DATE,'
    ||'   USING_REQUIREMENT_QUANTITY,'
    ||'   QUANTITY_PER_ASSEMBLY,'
    ||'   ISSUED_QUANTITY,'
    ||'   ASSEMBLY_DEMAND_COMP_DATE,'
    ||'   DEMAND_TYPE,'
    ||'   ORIGINATION_TYPE,'
    ||'   SOURCE_ORGANIZATION_ID,'
    ||'   DISPOSITION_ID,'
    ||'   RESERVATION_ID,'
    ||'   OP_SEQ_NUM,'
    ||'   DEMAND_CLASS,'
    ||'   SR_INSTANCE_ID,'
    ||'   PROJECT_ID,'
    ||'   TASK_ID,'
    ||'   PLANNING_GROUP,'
    ||'   UNIT_NUMBER,'
    ||'   ORDER_NUMBER,'
    ||'   WIP_ENTITY_ID,'
    ||'   WIP_STATUS_CODE,'
    ||'   WIP_SUPPLY_TYPE,'
    ||'   REPETITIVE_SCHEDULE_ID,'
    ||'   ASSET_ITEM_ID,'    /* ds change change*/
    ||'   ASSET_SERIAL_NUMBER,'	/* ds change change*/
    ||'   COMPONENT_SCALING_TYPE,'  /* Discrete Mfg Enahancements Bug 4492736 */
    ||'   COMPONENT_YIELD_FACTOR,'  /* Discrete Mfg Enahancements Bug 4492743 */
    ||'   ITEM_TYPE_ID,'
		||'   ITEM_TYPE_VALUE,'
    ||'   REFRESH_NUMBER,'
    ||'   LAST_UPDATE_DATE,'
    ||'   LAST_UPDATED_BY,'
    ||'   CREATION_DATE,'
    ||'   CREATED_BY)'
    ||'VALUES'
    ||'(  -1,'
    ||'   MSC_DEMANDS_S.nextval,'
    ||'   :INVENTORY_ITEM_ID,'
    ||'   :ORGANIZATION_ID,'
    ||'   :USING_ASSEMBLY_ITEM_ID,'
    ||'   :USING_ASSEMBLY_DEMAND_DATE,'
    ||'   :USING_REQUIREMENT_QUANTITY,'
    ||'   :QUANTITY_PER_ASSEMBLY,'
    ||'   :ISSUED_QUANTITY,'
    ||'   :ASSEMBLY_DEMAND_COMP_DATE,'
    ||'   :DEMAND_TYPE,'
    ||'   :ORIGINATION_TYPE,'
    ||'   :SOURCE_ORGANIZATION_ID,'
    ||'   :DISPOSITION_ID,'
    ||'   :RESERVATION_ID,'
    ||'   :OPERATION_SEQ_NUM,'
    ||'   :DEMAND_CLASS,'
    ||'   :SR_INSTANCE_ID,'
    ||'   :PROJECT_ID,'
    ||'   :TASK_ID,'
    ||'   :PLANNING_GROUP,'
    ||'   :END_ITEM_UNIT_NUMBER, '
    ||'   :ORDER_NUMBER,'
    ||'   :WIP_ENTITY_ID,'
    ||'   :WIP_STATUS_CODE,'
    ||'   :WIP_SUPPLY_TYPE,'
    ||'   :REPETITIVE_SCHEDULE_ID,'
    ||'   :ASSET_ITEM_ID,'		/* ds change change*/
    ||'   :ASSET_SERIAL_NUMBER,'    /* ds change change*/
    ||'   :COMPONENT_SCALING_TYPE,' /* Discrete Mfg Enahancements Bug 4492736 */
    ||'   :COMPONENT_YIELD_FACTOR,' /* Discrete Mfg Enahancements Bug 4492743 */
    ||'   :ITEM_TYPE_ID,'
		||'   :ITEM_TYPE_VALUE,'
    ||'   :v_last_collection_id,'
    ||'   :v_current_date,'
    ||'   :v_current_user,'
    ||'   :v_current_date,'
    ||'   :v_current_user )';

Line: 1011

       if the bulk insert above failed for whatever reason */

    lv_cursor_stmt:=
    'SELECT'
    ||'   NVL(ms.TRANSACTION_ID,-1) DISPOSITION_ID,'
    ||'   t1.INVENTORY_ITEM_ID,'
    ||'   msd.ORGANIZATION_ID,'
    ||'   t2.INVENTORY_ITEM_ID USING_ASSEMBLY_ITEM_ID,'
    ||'   msd.USING_ASSEMBLY_DEMAND_DATE,'
    ||'   msd.USING_REQUIREMENT_QUANTITY,'
    ||'   msd.QUANTITY_PER_ASSEMBLY,'
    ||'   msd.QUANTITY_ISSUED,'
    ||'   msd.ASSEMBLY_DEMAND_COMP_DATE,'
    ||'   msd.DEMAND_TYPE,'
    ||'   msd.ORIGINATION_TYPE,'
    ||'   msd.SOURCE_ORGANIZATION_ID,'
    ||'   msd.RESERVATION_ID,'
    ||'   msd.OPERATION_SEQ_NUM,'
    ||'   msd.DEMAND_CLASS,'
    ||'   msd.REPETITIVE_SCHEDULE_ID,'
    ||'   msd.SR_INSTANCE_ID,'
    ||'   msd.PROJECT_ID,'
    ||'   msd.TASK_ID,'
    ||'   msd.PLANNING_GROUP,'
    ||'   msd.END_ITEM_UNIT_NUMBER, '
    ||'   REPLACE(REPLACE(substr(msd.ORDER_NUMBER,1,62),:v_chr10),:v_chr13) ORDER_NUMBER,'
    ||'   msd.WIP_ENTITY_ID,'
    ||'   msd.WIP_STATUS_CODE,'
    ||'   msd.WIP_SUPPLY_TYPE,'
    ||'   msd.DELETED_FLAG,'
    ||'   t3.inventory_item_id  ASSET_ITEM_ID,'   /* ds change change*/
    ||'   msd.ASSET_SERIAL_NUMBER,'  /* ds change change*/
    ||'   msd.COMPONENT_SCALING_TYPE,' /* Discrete Mfg Enahancements Bug 4492736 */
    ||'   msd.COMPONENT_YIELD_FACTOR' /* Discrete Mfg Enahancements Bug 4492743 */
    ||' FROM MSC_ITEM_ID_LID t1,'
    ||'      MSC_ITEM_ID_LID t2,'
    ||'      MSC_ITEM_ID_LID t3,'
          || lv_supplies_tbl||' ms,'
    ||'      MSC_ST_DEMANDS msd'
    ||' WHERE msd.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id
    ||'  AND msd.ORIGINATION_TYPE = 77'   /* 50 eam demand: ds change change*/
    ||'  AND msd.DELETED_FLAG= '||MSC_UTIL.SYS_NO
    ||'  AND t1.SR_INVENTORY_ITEM_ID= msd.inventory_item_id'
    ||'  AND t1.sr_instance_id= msd.SR_INSTANCE_ID'
    ||'  AND t2.SR_INVENTORY_ITEM_ID= msd.using_assembly_item_id'
    ||'  AND t2.sr_instance_id= msd.SR_INSTANCE_ID'
    ||'  AND t3.SR_INVENTORY_ITEM_ID (+)= msd.ASSET_ITEM_ID'
    ||'  AND t3.sr_instance_id (+) = msd.SR_INSTANCE_ID'
    ||'  AND ms.sr_instance_id= msd.SR_INSTANCE_ID'
    ||'  AND ms.ORGANIZATION_ID= msd.ORGANIZATION_ID'
    ||'  AND ms.DISPOSITION_ID= msd.WIP_ENTITY_ID'
    ||'  AND ms.plan_id=-1'
    ||'  AND ms.ORDER_TYPE= 86' /* ds change change*/
    ||'  order by msd.SOURCE_WIP_ENTITY_ID, msd.SOURCE_INVENTORY_ITEM_ID,msd.SOURCE_ORGANIZATION_ID,msd.ORIGINATION_TYPE';

Line: 1096

         lv_DELETED_FLAG,
         lv_ASSET_ITEM_ID,		/* ds change change */
         lv_ASSET_SERIAL_NUMBER,	/* ds change change */
         lv_COMPONENT_SCALING_TYPE,  /* Discrete Mfg Enahancements Bug 4492736 */
         lv_COMPONENT_YIELD_FACTOR; /* Discrete Mfg Enahancements Bug 4492743 */

Line: 1109

        UPDATE MSC_DEMANDS
        SET
          OLD_USING_REQUIREMENT_QUANTITY=  USING_REQUIREMENT_QUANTITY,
          OLD_USING_ASSEMBLY_DEMAND_DATE=  USING_ASSEMBLY_DEMAND_DATE,
          OLD_ASSEMBLY_DEMAND_COMP_DATE=  ASSEMBLY_DEMAND_COMP_DATE,
          USING_ASSEMBLY_ITEM_ID=  lv_USING_ASSEMBLY_ITEM_ID,
          USING_ASSEMBLY_DEMAND_DATE=  lv_USING_ASSEMBLY_DEMAND_DATE,
          USING_REQUIREMENT_QUANTITY=  lv_USING_REQUIREMENT_QUANTITY,
          ASSEMBLY_DEMAND_COMP_DATE=  lv_ASSEMBLY_DEMAND_COMP_DATE,
          DEMAND_TYPE=  lv_DEMAND_TYPE,
          SOURCE_ORGANIZATION_ID=  lv_SOURCE_ORGANIZATION_ID,
          RESERVATION_ID=  lv_RESERVATION_ID,
          DEMAND_CLASS=  lv_DEMAND_CLASS,
          PROJECT_ID=  lv_PROJECT_ID,
          TASK_ID=  lv_TASK_ID,
          PLANNING_GROUP=  lv_PLANNING_GROUP,
          UNIT_NUMBER=  lv_END_ITEM_UNIT_NUMBER,
          ORDER_NUMBER=  lv_ORDER_NUMBER,
          WIP_STATUS_CODE= lv_WIP_STATUS_CODE,
          WIP_SUPPLY_TYPE= lv_WIP_SUPPLY_TYPE,
          DISPOSITION_ID= lv_DISPOSITION_ID,
          COMPONENT_SCALING_TYPE = lv_COMPONENT_SCALING_TYPE,  /* Discrete Mfg Enahancements Bug 4492736 */
          COMPONENT_YIELD_FACTOR = lv_COMPONENT_YIELD_FACTOR,  /* Discrete Mfg Enahancements Bug 4492743 */
          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 SR_INSTANCE_ID=  lv_SR_INSTANCE_ID
          AND ORIGINATION_TYPE=  lv_ORIGINATION_TYPE
          AND ORGANIZATION_ID=  lv_ORGANIZATION_ID
          AND WIP_ENTITY_ID=  lv_WIP_ENTITY_ID
          AND OP_SEQ_NUM=  lv_OPERATION_SEQ_NUM
          AND INVENTORY_ITEM_ID=  lv_INVENTORY_ITEM_ID ;

Line: 1145

        IF ( lv_DELETED_FLAG<> MSC_UTIL.SYS_YES ) AND ( lv_ORIGINATION_TYPE= 77)
        AND SQL%NOTFOUND THEN


        EXECUTE IMMEDIATE lv_sql_stmt
        USING
           lv_INVENTORY_ITEM_ID,
           lv_ORGANIZATION_ID,
           lv_USING_ASSEMBLY_ITEM_ID,
           lv_USING_ASSEMBLY_DEMAND_DATE,
           lv_USING_REQUIREMENT_QUANTITY,
           lv_QUANTITY_PER_ASSEMBLY,
           lv_QUANTITY_ISSUED,
           lv_ASSEMBLY_DEMAND_COMP_DATE,
           lv_DEMAND_TYPE,
           lv_ORIGINATION_TYPE,
           lv_SOURCE_ORGANIZATION_ID,
           lv_DISPOSITION_ID,
           lv_RESERVATION_ID,
           lv_OPERATION_SEQ_NUM,
           lv_DEMAND_CLASS,
           lv_SR_INSTANCE_ID,
           lv_PROJECT_ID,
           lv_TASK_ID,
           lv_PLANNING_GROUP,
           lv_END_ITEM_UNIT_NUMBER,
           lv_ORDER_NUMBER,
           lv_WIP_ENTITY_ID,
           lv_WIP_STATUS_CODE,
           lv_WIP_SUPPLY_TYPE,
           lv_REPETITIVE_SCHEDULE_ID,
           lv_ASSET_ITEM_ID,    /* ds change change */
           lv_ASSET_SERIAL_NUMBER,	/* ds changechange */
           lv_COMPONENT_SCALING_TYPE, /* Discrete Mfg Enahancements Bug 4492736 */
           lv_COMPONENT_YIELD_FACTOR, /* Discrete Mfg Enahancements Bug 4492743 */
           lv_item_type_id,
           lv_item_type_value,
           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;