APPS.WIP_WS_PTPKPI_PK SQL Statements

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

Line: 159

        Description     : This procedure will delete the existing rows for
                          the plan table and populate new data. This API
                          will be called from concurrent program API

****************************************************************************/

Procedure populate_plan_data(p_org_id        in number,
            p_org_ptpkpi_rec IN org_ptpkpi_rec_type,
            x_return_status out nocopy varchar2,
            x_msg_count     out nocopy number,
            x_msg_data      out nocopy varchar2) is

   l_sql VARCHAR2(2048) := null;

Line: 187

   delete wip_ws_ptpkpi_plan
   where  organization_id = p_org_id;

Line: 190

   wip_ws_util.trace_log(' Deleted old rows in the table. Rows deleted = '||sql%rowcount);

Line: 197

   'insert into wip_ws_ptpkpi_plan(
                organization_id,
                department_id,
                wip_entity_id,
                operation_seq_num,
                shift_id,
                planned_qty,
                primary_uom_code,
                op_lead_time,
                concurrent_request_id,
                last_update_date,
                last_update_by,
                creation_date,
                created_by,
		shift_start_time
              )
   select
	  wo.organization_id,
	  wo.department_id,
	  wo.wip_entity_id,
	  wo.operation_seq_num,
	  WIP_WS_PTPKPI_UTIL.get_shift_id_for_date(
	    wo.organization_id, wo.department_id, null, wo.last_unit_completion_date
	  ) ,
	  wo.scheduled_quantity-wo.cumulative_scrap_quantity ,
	  msi.primary_uom_code,
	  WIP_WS_PTPKPI_UTIL.get_operation_lead_time(
	    wo.organization_id, wo.wip_entity_id, wo.operation_seq_num
	  ) ,
	  fnd_global.CONC_REQUEST_ID,
          sysdate,
          :guserid,
          sysdate,
          :guserid,
	  WIP_WS_PTPKPI_UTIL.get_datetime_for_shift(:p_org_id,WIP_WS_PTPKPI_UTIL.get_shift_id_for_date(
	    wo.organization_id, wo.department_id, null, wo.last_unit_completion_date
	  ),1)
    from
	  wip_operations wo,
	  wip_discrete_jobs wdj,
	  mtl_system_items msi
    where wo.organization_id = :p_org_id
	  and wo.repetitive_schedule_id is null
	  and wo.last_unit_completion_date >= trunc(sysdate) - 30
	  and wo.wip_entity_id = wdj.wip_entity_id
	  and wo.organization_id = wdj.organization_id
	  and wdj.primary_item_id = msi.inventory_item_id
	  and wdj.organization_id = msi.organization_id';

Line: 254

   wip_ws_util.trace_log(' Number of rows inserted = '||sql%rowcount);

Line: 368

        select
	  wo.organization_id  ,
	  wo.department_id  ,
	  wo.wip_entity_id ,
	  wo.operation_seq_num,
	  wmt1.PRIMARY_QUANTITY,
	  WIP_WS_PTPKPI_UTIL.get_shift_id_for_date(
	          wo.organization_id,
		  wo.department_id,
		  null,
		  wmt1.transaction_date
	           )  shift_id,
	  fm_operation_seq_num  fm_op,
	  FM_INTRAOPERATION_STEP_TYPE fm_step,
	  to_operation_seq_num to_op,
	  TO_INTRAOPERATION_STEP_TYPE  to_step,
	  wmt1.primary_uom primary_uom_code,
	  WIP_WS_PTPKPI_UTIL.get_operation_lead_time(
	           wmt1.organization_id,
		   wmt1.wip_entity_id,
		   wo.operation_seq_num) op_lead_time,
          wo.operation_seq_num op
	from
	  wip_move_transactions wmt1,
	  wip_operations wo,
	  wip_discrete_jobs wdj
	where
	      wo.organization_id = p_org_id
	  and wo.organization_id = wmt1.organization_id
	  and wo.wip_entity_id   = wdj.wip_entity_id
	  and wo.wip_entity_id = wmt1.wip_entity_id
	  and wo.repetitive_schedule_id is null
	  and wmt1.transaction_date >= recentShiftDate
	  and (
	    (
	      wo.operation_seq_num >=
	        wmt1.fm_operation_seq_num +
	        DECODE(SIGN(wmt1.FM_INTRAOPERATION_STEP_TYPE - 2),1,1,0)
	      and wo.operation_seq_num <
	        wmt1.to_operation_seq_num +
	        DECODE(SIGN(wmt1.TO_INTRAOPERATION_STEP_TYPE - 2),1,1,0)
	      and (
	        wmt1.to_operation_seq_num > wmt1.fm_operation_seq_num OR
	        (wmt1.to_operation_seq_num = wmt1.fm_operation_seq_num AND
	         wmt1.FM_INTRAOPERATION_STEP_TYPE <= 2 AND
	         wmt1.TO_INTRAOPERATION_STEP_TYPE > 2)
	      )
	      AND (
	        wo.count_point_type < 3 OR
	        wo.operation_seq_num = wmt1.fm_operation_seq_num OR
	        (wo.operation_seq_num = wmt1.to_operation_seq_num AND
	         wmt1.TO_INTRAOPERATION_STEP_TYPE > 2)
	      )
	    )
	    OR
	    (
	      wo.operation_seq_num <
	        wmt1.fm_operation_seq_num +
	        DECODE(SIGN(wmt1.FM_INTRAOPERATION_STEP_TYPE-2),1,1,0) AND
	      wo.operation_seq_num >=
	        wmt1.to_operation_seq_num +
	        DECODE(SIGN(wmt1.TO_INTRAOPERATION_STEP_TYPE-2),1,1,0) AND
	      (wmt1.fm_operation_seq_num > wmt1.to_operation_seq_num OR
	       (wmt1.fm_operation_seq_num = wmt1.to_operation_seq_num AND
	        wmt1.TO_INTRAOPERATION_STEP_TYPE <= 2 AND
	        wmt1.FM_INTRAOPERATION_STEP_TYPE > 2)
	      ) AND
	      (wo.count_point_type < 3 OR
	       (wo.operation_seq_num = wmt1.to_operation_seq_num AND wo.count_point_type < 3) OR
	       (wo.operation_seq_num = wmt1.fm_operation_seq_num AND wmt1.FM_INTRAOPERATION_STEP_TYPE > 2)
	      )
	    )
	    OR
	    (
	      -- pick up all the returns from scrap/reject for the source operation
	      wmt1.FM_INTRAOPERATION_STEP_TYPE in (4,5) AND
	      wo.operation_seq_num = wmt1.fm_operation_seq_num
	    )
	  );

Line: 497

	delete
	from wip_ws_ptpkpi_actual
	where shift_start_time < trunc(sysdate) - 30
	and organization_id = p_org_id;

Line: 502

	wip_ws_util.trace_log('Deleted old records. Number of records deleted = '||sql%rowcount);

Line: 509

	  select shift_id, shift_start_time
	  into   RecentShiftid, RecentShiftStartTime
	  from  wip_ws_ptpkpi_actual
	  where shift_start_time in
		(select max(shift_start_Time)
		 from   wip_ws_ptpkpi_actual)
	  and rownum = 1;

Line: 531

	   delete from wip_ws_ptpkpi_actual
	   where shift_start_time >= RecentShiftStartTime;

Line: 536

	delete from wip_ws_ptpkpi_actual
	where shift_start_time >= sysdate-7
  and organization_id = p_org_id;

Line: 545

        l_sql := 'select
	  wo.organization_id  ,
	  wo.department_id  ,
	  wo.wip_entity_id ,
	  wo.operation_seq_num,
	  wmt1.PRIMARY_QUANTITY,
	  WIP_WS_PTPKPI_UTIL.get_shift_id_for_date(
	          wo.organization_id,
		  wo.department_id,
		  null,
		  wmt1.transaction_date
	           )  shift_id,
	  fm_operation_seq_num  fm_op,
	  FM_INTRAOPERATION_STEP_TYPE fm_step,
	  to_operation_seq_num to_op,
	  TO_INTRAOPERATION_STEP_TYPE  to_step,
	  wmt1.primary_uom primary_uom_code,
	  WIP_WS_PTPKPI_UTIL.get_operation_lead_time(
	           wdj.organization_id,
		   wdj.wip_entity_id,
		   wo.operation_seq_num) op_lead_time,
          wo.operation_seq_num op
	from
	  wip_move_transactions wmt1,
	  wip_operations wo,
	  wip_discrete_jobs wdj
	where
	      wo.organization_id = :p_org_id
	  and wo.organization_id = wmt1.organization_id
	  and wo.wip_entity_id   = wdj.wip_entity_id
	  and wo.wip_entity_id = wmt1.wip_entity_id
	  and wo.repetitive_schedule_id is null
	  and wmt1.transaction_date >= sysdate-7
	  and (
	    (
	      wo.operation_seq_num >=
	        wmt1.fm_operation_seq_num +
	        DECODE(SIGN(wmt1.FM_INTRAOPERATION_STEP_TYPE - 2),1,1,0)
	      and wo.operation_seq_num <
	        wmt1.to_operation_seq_num +
	        DECODE(SIGN(wmt1.TO_INTRAOPERATION_STEP_TYPE - 2),1,1,0)
	      and (
	        wmt1.to_operation_seq_num > wmt1.fm_operation_seq_num OR
	        (wmt1.to_operation_seq_num = wmt1.fm_operation_seq_num AND
	         wmt1.FM_INTRAOPERATION_STEP_TYPE <= 2 AND
	         wmt1.TO_INTRAOPERATION_STEP_TYPE > 2)
	      )
	      AND (
	        wo.count_point_type < 3 OR
	        wo.operation_seq_num = wmt1.fm_operation_seq_num OR
	        (wo.operation_seq_num = wmt1.to_operation_seq_num AND
	         wmt1.TO_INTRAOPERATION_STEP_TYPE > 2)
	      )
	    )
	    OR
	    (
	      wo.operation_seq_num <
	        wmt1.fm_operation_seq_num +
	        DECODE(SIGN(wmt1.FM_INTRAOPERATION_STEP_TYPE-2),1,1,0) AND
	      wo.operation_seq_num >=
	        wmt1.to_operation_seq_num +
	        DECODE(SIGN(wmt1.TO_INTRAOPERATION_STEP_TYPE-2),1,1,0) AND
	      (wmt1.fm_operation_seq_num > wmt1.to_operation_seq_num OR
	       (wmt1.fm_operation_seq_num = wmt1.to_operation_seq_num AND
	        wmt1.TO_INTRAOPERATION_STEP_TYPE <= 2 AND
	        wmt1.FM_INTRAOPERATION_STEP_TYPE > 2)
	      ) AND
	      (wo.count_point_type < 3 OR
	       (wo.operation_seq_num = wmt1.to_operation_seq_num AND wo.count_point_type < 3) OR
	       (wo.operation_seq_num = wmt1.fm_operation_seq_num AND wmt1.FM_INTRAOPERATION_STEP_TYPE > 2)
	      )
	    )
	    OR
	    (
	      -- pick up all the returns from scrap/reject for the source operation
	      wmt1.FM_INTRAOPERATION_STEP_TYPE in (4,5) AND
	      wo.operation_seq_num = wmt1.fm_operation_seq_num
	    )
	  )';

Line: 726

	    insert into wip_ws_ptp_gt
		(organization_id,
		 department_id,
		 wip_entity_id,
		 operation_seq_num,
		 move_qty,
		 scrap_qty,
		 reject_qty,
		 shift_id,
		 primary_uom_code,
		 op_lead_time
		)
            values(
		 l_organization_id,
		 l_department_id,
		 l_wip_entity_id,
		 l_op,
		 move_qty,
		 scrap_qty,
		 reject_qty,
		 l_shift_id,
		 l_primary_uom_code,
		 l_lead_time
		);

Line: 840

	  insert into wip_ws_ptp_gt
		(organization_id,
		 department_id,
		 wip_entity_id,
		 operation_seq_num,
		 move_qty,
		 scrap_qty,
		 reject_qty,
		 shift_id,
		 primary_uom_code,
		 op_lead_time
		)
          values(
		 move_tran_rec.organization_id,
		 move_tran_rec.department_id,
		 move_tran_rec.wip_entity_id,
		 move_tran_rec.op,
		 move_qty,
		 scrap_qty,
		 reject_qty,
		 move_tran_rec.shift_id,
		 move_tran_rec.primary_uom_code,
		 move_tran_rec.op_lead_time
		);

Line: 871

	-- aggregate the temp data and insert to the actual table
	insert into wip_ws_ptpkpi_actual(
		organization_id,
		department_id,
		wip_entity_id,
		shift_id,
		operation_seq_num,
		moved_qty,
		scrapped_qty,
		rejected_qty,
		primary_uom_code,
		op_lead_time,
		shift_start_time,
		concurrent_request_id,
		last_update_date,
		last_update_by,
		creation_date,
		created_by)
	select
	  organization_id,
	  department_id,
	  wip_entity_id,
	  shift_id,
	  operation_seq_num,
	  sum(nvl(move_qty,0)) ,
	  sum(nvl(scrap_qty,0)) ,
	  sum(nvl(reject_qty,0)),
	  t.primary_uom_code,
	  t.op_lead_time,
	  WIP_WS_PTPKPI_UTIL.get_datetime_for_shift(p_org_id,shift_id,1),
	  fnd_global.CONC_REQUEST_ID,
          sysdate,
          g_user_id,
          sysdate,
          g_user_id
	from wip_ws_ptp_gt t
	group by
	  organization_id,department_id,wip_entity_id,operation_seq_num,shift_id,primary_uom_code,
	  op_lead_time;