APPS.OPI_EDW_JOB_DETAIL_FOPM_SZ SQL Statements

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

Line: 9

	select count(*)
	FROM
          (
            SELECT
                BH.BATCH_ID,
                BH.BATCH_NO,
                BH.batch_status,
                BH.WIP_WHSE_CODE,
	        BH.PLAN_START_DATE,
	        BH.ACTUAL_START_DATE,
	        BH.EXPCT_CMPLT_DATE,
	        BH.ACTUAL_CMPLT_DATE,
	        BH.PLANT_CODE,
                BH.FORMULA_ID,
                RT.ROUTING_NO,
                to_char(RT.ROUTING_VERS) ROUTING_VERS,
		BD.ITEM_ID,
                BD.PLAN_QTY,
                BD.ACTUAL_QTY,
                BD.ITEM_UM,
                BD.COST_ALLOC,
                BD.LINE_NO,
                BH.CREATION_DATE,
                GREATEST(BH.LAST_UPDATE_DATE, BD.LAST_UPDATE_DATE) LAST_UPDATE_DATE
		FROM  PM_BTCH_HDR  BH,
                  PM_MATL_DTL  BD,
                  FM_ROUT_HDR  RT
		WHERE BH.BATCH_ID   = BD.BATCH_ID
                  AND BH.ROUTING_ID=RT.ROUTING_ID(+)
      		AND BH.BATCH_STATUS in (-1,0,1,2,3,4)
      		AND BD.LINE_TYPE=1
                ) B,
          SY_ORGN_MST  OM,
          IC_ITEM_MST  IM,
          IC_PLNT_INV  PI,
          GL_SETS_OF_BOOKS SOB,
          GL_PLCY_MST  PM,
          MTL_SYSTEM_ITEMS ITEM_FK_V,
          IC_WHSE_MST IW,
          EDW_LOCAL_INSTANCE inst,
          OPI_PMI_UOMS_MST UOM,
          GEM_LOOKUPS LKUP
     WHERE
          B.PLANT_CODE = OM.ORGN_CODE
      AND B.PLANT_CODE = PI.ORGN_CODE(+)
      AND B.item_id    = PI.item_id(+)
      AND OM.co_CODE  = PM.co_code
      AND PM.set_of_books_name =SOB.name
      AND B.ITEM_ID    = IM.ITEM_ID
      AND ITEM_FK_V.SEGMENT1= IM.ITEM_NO
      AND ITEM_FK_V.ORGANIZATION_ID = IW.MTL_ORGANIZATION_ID
      AND IW.WHSE_CODE = B.WIP_WHSE_CODE
      AND UOM.UM_CODE = IM.ITEM_UM
      AND LKUP.LOOKUP_TYPE='BATCH_STATUS'
      AND LKUP.LOOKUP_CODE=B.BATCH_STATUS
      AND B.LAST_UPDATE_DATE between p_from_date and p_to_date;

Line: 105

	SELECT
        avg(nvl(vsize(BH.Plant_code||'-'||BH.Batch_id||
        '-'||BD.item_id||'-'||'OPM'),0))    JOB_DETAIL_PK,
        avg(nvl(vsize(BH.ACTUAL_CMPLT_DATE-BH.ACTUAL_START_DATE),0)) ACT_JOB_TIME,
        avg(nvl(vsize(BD.ACTUAL_QTY),0)),
        avg(nvl(vsize(BH.BATCH_NO),0)),
	avg(nvl(vsize(BH.BATCH_STATUS),0)),
	avg(nvl(vsize('PROCESSMFG'),0)),
	avg(nvl(vsize(BH.EXPCT_CMPLT_DATE-BH.PLAN_START_DATE),0)),
	avg(nvl(vsize(BD.PLAN_QTY),0)),
	avg(nvl(vsize(RT.ROUTING_NO),0)),
        avg(nvl(vsize(RT.ROUTING_VERS),0))
        FROM
                  PM_BTCH_HDR  BH,
                  PM_MATL_DTL  BD,
                  FM_ROUT_HDR  RT
               WHERE BH.BATCH_ID   = BD.BATCH_ID
                  AND BH.ROUTING_ID=RT.ROUTING_ID(+)
      		AND BH.BATCH_STATUS in (-1,0,1,2,3,4)
      		AND BD.LINE_TYPE=1;

Line: 129

	SELECT
	avg(nvl(vsize(EDW_ITEMS_PKG.ITEM_ORG_FK(ITEM_FK_V.INVENTORY_ITEM_ID,
        IW.MTL_ORGANIZATION_ID,NULL,TO_NUMBER(NULL),NULL)), 0))
	FROM	MTL_SYSTEM_ITEMS ITEM_FK_V,
                IC_WHSE_MST IW,
                IC_ITEM_MST IM
        WHERE   ITEM_FK_V.SEGMENT1= IM.ITEM_NO
                AND ITEM_FK_V.ORGANIZATION_ID = IW.MTL_ORGANIZATION_ID;

Line: 139

	SELECT
		avg(nvl(vsize(instance_code), 0))
	FROM	EDW_LOCAL_INSTANCE ;

Line: 145

	SELECT  avg(nvl(vsize(BASE_CURRENCY_CODE), 0))
        FROM    gl_plcy_mst;

Line: 150

	SELECT  avg(nvl(vsize(UOM_CODE), 0))
	FROM OPI_PMI_UOMS_MST;

Line: 153

	SELECT  avg(nvl(vsize(ORGN_CODE), 0))
	FROM SY_ORGN_MST;

Line: 157

	SELECT          avg(nvl(vsize(substr(edw_time_pkg.cal_day_fk
           (BH.ACTUAL_CMPLT_DATE,SOB.SET_OF_BOOKS_ID),1,120)),0))
	FROM
          PM_BTCH_HDR  BH,
          SY_ORGN_MST  OM,
          GL_PLCY_MST  PM,
          GL_SETS_OF_BOOKS SOB
          WHERE
          BH.PLANT_CODE = OM.ORGN_CODE
          AND OM.CO_CODE      = PM.co_code
          AND PM.SET_OF_BOOKS_NAME=SOB.name;