APPS.OPI_EDW_OPM_JOB_RSRC_F_C SQL Statements

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

Line: 18

   SELECT instance_code INTO   l_source
   FROM   edw_local_instance;

Line: 21

   SELECT instance_code INTO   l_target
   FROM   edw_local_instance@edw_apps_to_wh;

Line: 41

SELECT JOB_RSRC_PK,TRX_DATE_FK,SOB_CURRENCY_FK
FROM
   OPI_EDW_JOB_RSRC_FSTG
WHERE
    COLLECTION_STATUS in ('RATE NOT AVAILABLE','INVALID CURRENCY')
AND JOB_RSRC_PK like '%OPM'
AND SUBSTRB(JOB_RSRC_PK,instrB(JOB_RSRC_PK,'-',-1,3)+1,
                      instrB(JOB_RSRC_PK,'-',-1,2)-1-instrB(JOB_RSRC_PK,'-',-1,3))
    in (select PRIMARY_KEY from
                              OPI_EDW_OPM_JOB_RSRC_INC
                              WHERE SEQ_ID is NULL);

Line: 86

 /* Delete the incremental table before inserting new data */
  DELETE OPI_EDW_OPM_JOB_RSRC_INC;

Line: 91

  SELECT count(*) into l_count from OPI_EDW_JOB_RSRC_FSTG where
       COLLECTION_STATUS in ('RATE NOT AVAILABLE','INVALID CURRENCY')
       AND JOB_RSRC_PK like '%OPM';

Line: 96

      /* insert into Incremental table all line_id where Currency is missing */
	INSERT /*+ parallel(OPI_EDW_OPM_JOB_RSRC_INC) */
  	into OPI_EDW_OPM_JOB_RSRC_INC(PRIMARY_KEY,view_id,seq_id)
      SELECT
        BATCHSTEPLINE_ID,
        1,
        NULL
      FROM
         PM_OPRN_DTL
      WHERE
         BATCHSTEPLINE_ID
          in ( SELECT SUBSTRB(JOB_RSRC_PK,instrB(JOB_RSRC_PK,'-',-1,3)+1,
                      instrB(JOB_RSRC_PK,'-',-1,2)-1-instrB(JOB_RSRC_PK,'-',-1,3))
                 from OPI_EDW_JOB_RSRC_FSTG
                 WHERE  COLLECTION_STATUS in ('RATE NOT AVAILABLE','INVALID CURRENCY')
                 AND JOB_RSRC_PK like '%OPM');

Line: 122

    /*Delete all missing rows from FSTG table if source and target are on same instance*/
     IF (LOCAL_SAME_AS_REMOTE) THEN
         DELETE OPI_EDW_JOB_RSRC_FSTG
               WHERE COLLECTION_STATUS in ('RATE NOT AVAILABLE','INVALID CURRENCY')
                 AND JOB_RSRC_PK like '%OPM';

Line: 127

        edw_log.Put_line(to_char(sql%rowcount) ||' missing Currency Rate Conversion rows deleted from Staging table');

Line: 155

     INSERT INTO OPI_EDW_JOB_RSRC_FSTG(
      ACTIVITY_FK,
ACT_CMPL_DATE ,
ACT_RSRC_COUNT,
ACT_RSRC_QTY  ,
ACT_RSRC_USAGE  ,
ACT_RSRC_USAGE_VAL_B ,
ACT_RSRC_USAGE_VAL_G  ,
ACT_RSRC_VAL_B  ,
ACT_RSRC_VAL_G ,
ACT_STRT_DATE ,
AVL_RSRC  ,
COLLECTION_STATUS,
CREATION_DATE ,
DEPARTMENT,
EXTD_RSRC_COST ,
INSTANCE_FK,
ITEM_FK ,
JOB_NO,
JOB_RSRC_PK ,
LAST_UPDATE_DATE,
LOCATOR_FK ,
OPERATION_CODE ,
OPERATION_SEQ_NO ,
OPRN_FK ,
PLN_CMPL_DATE,
PLN_RSRC_COUNT  ,
PLN_RSRC_QTY ,
PLN_RSRC_USAGE  ,
PLN_RSRC_USAGE_VAL_B,
PLN_RSRC_USAGE_VAL_G,
PLN_RSRC_VAL_B  ,
PLN_RSRC_VAL_G ,
PLN_STRT_DATE ,
QTY_UOM_FK ,
RSRC_FK ,
SOB_CURRENCY_FK ,
STND_RSRC_USAGE,
TRX_DATE_FK ,
USAGE_UOM_FK ,
USER_ATTRIBUTE1 ,
USER_ATTRIBUTE10 ,
USER_ATTRIBUTE11 ,
USER_ATTRIBUTE12 ,
USER_ATTRIBUTE13,
USER_ATTRIBUTE14 ,
USER_ATTRIBUTE15 ,
USER_ATTRIBUTE2 ,
USER_ATTRIBUTE3 ,
USER_ATTRIBUTE4 ,
USER_ATTRIBUTE5 ,
USER_ATTRIBUTE6 ,
USER_ATTRIBUTE7 ,
USER_ATTRIBUTE8 ,
USER_ATTRIBUTE9 ,
USER_FK1 ,
USER_FK2 ,
USER_FK3 ,
USER_FK4 ,
USER_FK5 ,
USER_MEASURE1  ,
USER_MEASURE2 ,
USER_MEASURE3  ,
USER_MEASURE4 ,
USER_MEASURE5
)
  SELECT /*+ ALL_ROWS */
ACTIVITY_FK,
ACT_CMPL_DATE ,
ACT_RSRC_COUNT,
ACT_RSRC_QTY  ,
ACT_RSRC_USAGE  ,
ACT_RSRC_USAGE_VAL_B ,
ACT_RSRC_USAGE_VAL_G  ,
ACT_RSRC_VAL_B  ,
ACT_RSRC_VAL_G ,
ACT_STRT_DATE ,
AVL_RSRC  ,
DECODE(PLN_RSRC_USAGE_VAL_G,-1,'RATE NOT AVAILABLE',-2,'INVALID CURRENCY','LOCAL READY'),
NULL ,
DEPARTMENT,
EXTD_RSRC_COST ,
INSTANCE_FK,
ITEM_FK ,
JOB_NO,
JOB_RSRC_PK ,
LAST_UPDATE_DATE,
LOCATOR_FK ,
NULL OPERATION_CODE ,
OPERATION_SEQ_NO ,
OPRN_FK ,
PLN_CMPL_DATE,
PLN_RSRC_COUNT  ,
PLN_RSRC_QTY ,
PLN_RSRC_USAGE  ,
PLN_RSRC_USAGE_VAL_B,
PLN_RSRC_USAGE_VAL_G  ,
PLN_RSRC_VAL_B  ,
PLN_RSRC_VAL_G ,
PLN_STRT_DATE ,
QTY_UOM_FK ,
RSRC_FK ,
SOB_CURRENCY_FK ,
STND_RSRC_USAGE,
TRX_DATE_FK ,
USAGE_UOM_FK ,
USER_ATTRIBUTE1 ,
USER_ATTRIBUTE10 ,
USER_ATTRIBUTE11 ,
USER_ATTRIBUTE12 ,
USER_ATTRIBUTE13,
USER_ATTRIBUTE14 ,
USER_ATTRIBUTE15 ,
USER_ATTRIBUTE2 ,
USER_ATTRIBUTE3 ,
USER_ATTRIBUTE4 ,
USER_ATTRIBUTE5 ,
USER_ATTRIBUTE6 ,
USER_ATTRIBUTE7 ,
USER_ATTRIBUTE8 ,
USER_ATTRIBUTE9 ,
USER_FK1 ,
USER_FK2 ,
USER_FK3 ,
USER_FK4 ,
USER_FK5 ,
USER_MEASURE1 ,
USER_MEASURE2 ,
USER_MEASURE3 ,
USER_MEASURE4 ,
USER_MEASURE5
    FROM OPI_EDW_OPM_JOB_RSRC_FCV
    WHERE view_id   = p_view_id
    AND   seq_id    = p_seq_id;

Line: 314

   select OPI_EDW_JOB_RSRC_INC_S.nextval into l_seq_id from dual;

Line: 315

	INSERT /*+ parallel(OPI_EDW_OPM_JOB_RSRC_INC) */
  	into OPI_EDW_OPM_JOB_RSRC_INC(PRIMARY_KEY,view_id,seq_id)
      SELECT
        BATCHSTEPLINE_ID,
        1,
        l_seq_id
      FROM
             PM_OPRN_DTL POD,
             PM_BTCH_HDR PBH
            WHERE POD.BATCH_ID=PBH.BATCH_ID
            AND GREATEST(POD.LAST_UPDATE_DATE, PBH.LAST_UPDATE_DATE)
          BETWEEN g_push_from_date and g_push_to_date
            AND BATCHSTEPLINE_ID not in (
                SELECT PRIMARY_KEY
                 from  OPI_EDW_OPM_JOB_RSRC_INC
                 WHERE SEQ_ID is NULL);

Line: 333

          /* Update the Missing Currency convenrsion rows with new Sequence */
          Update OPI_EDW_OPM_JOB_RSRC_INC set view_id=1,seq_id=l_seq_id
                 WHERE seq_id is NULL;

Line: 413

      edw_log.put_line('Inserting into local staging table ');

Line: 416

      edw_log.put_line('Inserted '||nvl(l_row_count1,0)||
         ' rows into the local staging table ');

Line: 421

      edw_log.put_line('For all views types, inserted '||nvl(g_row_count,0)||
        ' rows into local staging table ');

Line: 428

      edw_log.put_line('Inserted '||nvl(g_row_count,0)||
         ' rows into the staging table');

Line: 441

      rollback;   -- Rollback insert into local staging

Line: 442

      edw_log.put_line('Inserting into local staging have failed');