APPS.OPI_EDW_JOB_RSRC_FOPM_SZ SQL Statements

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

Line: 9

	select count(*)
	FROM
          PM_BTCH_HDR  BH,
          PM_MATL_DTL  BD,
          IC_ITEM_MST  IM,
          (SELECT
            POD.BATCH_ID,
            PBH.PLANT_CODE,
            POD.ACTIVITY,
            POD.RESOURCES,
            POD.OPRN_LINE_ID,
            POD.BATCHSTEP_NO,
            POD.BATCHSTEPLINE_ID,
            POD.USAGE_UM,
            POD.ACTUAL_CMPLT_DATE,
            POD.ACTUAL_RSRC_COUNT,
            POD.ACTUAL_RSRC_QTY,
            POD.ACTUAL_RSRC_USAGE,
            POD.ACTUAL_START_DATE,
            POD.PLAN_CMPLT_DATE,
            POD.PLAN_RSRC_COUNT,
            POD.PLAN_RSRC_QTY,
            POD.PLAN_RSRC_USAGE,
            POD.PLAN_START_DATE,
            POD.LAST_UPDATE_DATE
            FROM
             PM_OPRN_DTL POD,
             PM_BTCH_HDR PBH
            WHERE POD.BATCH_ID=PBH.BATCH_ID
          )  BR,
          CR_RSRC_DTL  CR,
          SY_ORGN_MST  OM,
          GL_PLCY_MST  PM,
          FM_OPRN_MST  OPRM,
          FM_OPRN_DTL  OPRD,
          MTL_SYSTEM_ITEMS ITEM_FK_V,
          IC_WHSE_MST IW,
          GL_SETS_OF_BOOKS SOB,
          EDW_LOCAL_INSTANCE inst,
          OPI_PMI_UOMS_MST UOM
     WHERE
          BH.BATCH_ID   = BR.BATCH_ID
      AND BH.BATCH_ID   = BD.BATCH_ID
      AND BH.PLANT_CODE = OM.ORGN_CODE
      AND BR.PLANT_CODE = CR.ORGN_CODE(+)
      AND BR.RESOURCES  = CR.RESOURCES(+)
      AND BR.OPRN_LINE_ID = OPRD.OPRN_LINE_ID
      AND OPRD.OPRN_ID    = OPRM.OPRN_ID
      AND OM.CO_CODE      = PM.co_code
      AND PM.SET_OF_BOOKS_NAME=SOB.name
      AND BD.ITEM_ID      = IM.ITEM_ID
      AND BH.BATCH_STATUS in (3,4)
      AND BD.LINE_TYPE=1 and BD.LINE_NO=1
      AND ITEM_FK_V.SEGMENT1= IM.ITEM_NO
      AND ITEM_FK_V.ORGANIZATION_ID = IW.MTL_ORGANIZATION_ID
      AND IW.WHSE_CODE = BH.WIP_WHSE_CODE
      AND UOM.UM_CODE = BR.USAGE_UM
      AND BR.LAST_UPDATE_DATE between p_from_date and p_to_date;

Line: 108

	SELECT 	        avg(nvl(vsize(POD.BATCH_ID||'-'||POD.Batchstep_no||'-'||
            POD.Resources||'-'||POD.Activity||'-'||POD.BATCHSTEPLINE_ID||'-OPM'),0))             JOB_RSRC_PK,
            avg(nvl(vsize(POD.ACTUAL_CMPLT_DATE),0))  ACT_CMPL_DATE,
            avg(nvl(vsize(POD.ACTUAL_RSRC_COUNT),0))  ACT_RSRC_COUNT,
            avg(nvl(vsize(POD.ACTUAL_RSRC_QTY),0))    ACT_RSRC_QTY,
            avg(nvl(vsize(POD.ACTUAL_RSRC_USAGE),0))  ACT_RSRC_USAGE,
            avg(nvl(vsize(POD.ACTUAL_START_DATE),0))  ACT_START_DATE,
            avg(nvl(vsize(POD.PLAN_CMPLT_DATE),0))    PLN_CMPLT_DATE,
            avg(nvl(vsize(POD.PLAN_RSRC_COUNT),0))    PLN_RSRC_COUNT,
            avg(nvl(vsize(POD.PLAN_RSRC_QTY),0))      PLN_RSRC_QTY,
            avg(nvl(vsize(POD.PLAN_RSRC_USAGE),0))    PLN_RSRC_USAGE,
            avg(nvl(vsize(POD.PLAN_START_DATE),0))    PLN_STRT_DATE,
            avg(nvl(vsize(POD.BATCHSTEP_NO),0))       OPERATION_SEQ_NO,
            avg(nvl(vsize(PBH.BATCH_NO),0))          JOB_NO,
            avg(nvl(vsize(((POD.PLAN_RSRC_USAGE
                               /POD.PLAN_RSRC_QTY)
                               *POD.ACTUAL_RSRC_QTY)),0)) STND_RSRC_USAGE
            FROM
             PM_OPRN_DTL POD,
             PM_BTCH_HDR PBH
            WHERE POD.BATCH_ID=PBH.BATCH_ID AND
            POD.last_update_date between
            p_from_date  and  p_to_date;

Line: 133

	SELECT
		/* OPRN_FK */
		avg(nvl(vsize(oprn_id),0))
	FROM	FM_OPRN_MST;

Line: 141

	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: 151

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

Line: 157

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

Line: 161

	SELECT  avg(nvl(vsize(ACTIVITY), 0))
        FROM    FM_ACTV_MST;

Line: 165

	SELECT  avg(nvl(vsize(RESOURCES||'-OPM'), 0))
	FROM CR_RSRC_MST;

Line: 169

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

Line: 172

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

Line: 176

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