APPS.OPI_EDW_OPM_JOB_DETAIL_F_C SQL Statements

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

Line: 19

   SELECT instance_code INTO   l_source
   FROM   edw_local_instance;

Line: 22

   SELECT instance_code INTO   l_target
   FROM   edw_local_instance@edw_apps_to_wh;

Line: 42

SELECT JOB_DETAIL_PK,TRX_DATE_FK,SOB_CURRENCY_FK,INSTANCE_FK
FROM
   OPI_EDW_JOB_DETAIL_FSTG
WHERE
    COLLECTION_STATUS in ('RATE NOT AVAILABLE','INVALID CURRENCY')
AND JOB_DETAIL_PK like '%OPM'
AND SUBSTRB(JOB_DETAIL_PK,instrB(JOB_DETAIL_PK,'-',1,1)+1,
                      instrB(JOB_DETAIL_PK,'-',1,2)-1-instrB(JOB_DETAIL_PK,'-',1,1))
    in (select BATCH_ID from PM_MATL_DTL WHERE
               LINE_ID in (select PRIMARY_KEY from
                              OPI_EDW_OPM_JOB_DETAIL_INC
                              WHERE SEQ_ID is NULL));

Line: 86

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

Line: 91

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

Line: 97

      /* insert into Incremental table all line_id where Currency is missing */
	INSERT /*+ parallel(OPI_EDW_OPM_JOB_DETAIL_INC) */
  	into OPI_EDW_OPM_JOB_DETAIL_INC(PRIMARY_KEY,view_id,seq_id)
      SELECT
        LINE_ID,
        1,
        NULL
      FROM
         PM_MATL_DTL
      WHERE
          LINE_TYPE=1 AND
          BATCH_ID
          in ( SELECT SUBSTRB(JOB_DETAIL_PK,instrB(JOB_DETAIL_PK,'-',1,1)+1,
                      instrB(JOB_DETAIL_PK,'-',1,2)-1-instrB(JOB_DETAIL_PK,'-',1,1))
               FROM  OPI_EDW_JOB_DETAIL_FSTG
               WHERE COLLECTION_STATUS in ('RATE NOT AVAILABLE','INVALID CURRENCY')
                 AND JOB_DETAIL_PK like '%OPM');

Line: 123

    /*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_DETAIL_FSTG
               WHERE COLLECTION_STATUS in ('RATE NOT AVAILABLE','INVALID CURRENCY')
                 AND JOB_DETAIL_PK like '%OPM';

Line: 128

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

Line: 156

     INSERT INTO OPI_EDW_JOB_DETAIL_FSTG(
ACT_BPR_VAL_B ,
ACT_BPR_VAL_G ,
ACT_CMPL_DATE ,
ACT_CNCL_DATE ,
ACT_INP_VAL_B  ,
ACT_INP_VAL_G  ,
ACT_JOB_TIME ,
ACT_MTL_INP_VAL_B  ,
ACT_MTL_INP_VAL_G  ,
ACT_OUT_QTY ,
ACT_OUT_VAL_B  ,
ACT_OUT_VAL_G ,
ACT_SCR_VAL_B  ,
ACT_SCR_VAL_G ,
ACT_STRT_DATE ,
BASE_UOM_FK,
COLLECTION_STATUS ,
CREATION_DATE ,
FST_PASS_YLD  ,
INSTANCE_FK ,
ITEM_FK ,
JOB_DETAIL_PK ,
JOB_NO ,
JOB_STATUS ,
LAST_UPDATE_DATE ,
LOCATOR_FK ,
MFG_MODE ,
MOVE_TIME  ,
NO_ADJ  ,
NO_TIME_RESH  ,
OPERATION_CODE ,
PLN_BPR_VAL_B ,
PLN_BPR_VAL_G  ,
PLN_CMPL_DATE ,
PLN_INP_VAL_B  ,
PLN_INP_VAL_G ,
PLN_JOB_TIME  ,
PLN_MTL_INP_VAL_B  ,
PLN_MTL_INP_VAL_G  ,
PLN_OUT_QTY ,
PLN_OUT_VAL_B ,
PLN_OUT_VAL_G ,
PLN_SCR_VAL_B ,
PLN_SCR_VAL_G  ,
PLN_STRT_DATE ,
PRD_LINE_FK ,
QC_FAIL_QTY  ,
QC_TEST ,
QUEUE_TIME  ,
RESH_REASON_CODE ,
RES_LOOKUP_FK ,
REWORK_QTY  ,
ROUTING,
ROUTING_REVISION,
RUN_TIME  ,
SETUP_TIME  ,
SMPL_CNT  ,
SOB_CURRENCY_FK ,
STD_QTY  ,
STD_TIME  ,
STD_VAL_B  ,
STD_VAL_G  ,
STND_HRS_EARNED,
STS_LOOKUP_FK ,
TRX_DATE_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 */
ACT_BPR_VAL_B ,
DECODE(ACT_BPR_VAL_G,-1,NULL,-2,NULL,ACT_BPR_VAL_G) ,
ACT_CMPL_DATE ,
ACT_CNCL_DATE ,
ACT_INP_VAL_B  ,
DECODE(ACT_INP_VAL_G,-1,NULL,-2,NULL,ACT_INP_VAL_G) ,
ACT_JOB_TIME ,
ACT_MTL_INP_VAL_B  ,
DECODE(ACT_MTL_INP_VAL_G,-1,NULL,-2,NULL,ACT_MTL_INP_VAL_G) ,
ACT_OUT_QTY ,
ACT_OUT_VAL_B  ,
DECODE(ACT_OUT_VAL_G,-1,NULL,-2,NULL,ACT_OUT_VAL_G) ,
ACT_SCR_VAL_B  ,
ACT_SCR_VAL_G ,
ACT_STRT_DATE ,
BASE_UOM_FK ,
DECODE(PLN_OUT_VAL_G,-1,'RATE NOT AVAILABLE',-2,'INVALID CURRENCY','LOCAL READY') ,
CREATION_DATE ,
FST_PASS_YLD  ,
INSTANCE_FK ,
ITEM_FK ,
JOB_DETAIL_PK ,
JOB_NO ,
JOB_STATUS ,
LAST_UPDATE_DATE ,
LOCATOR_FK ,
MFG_MODE ,
MOVE_TIME  ,
NO_ADJ  ,
NO_TIME_RESH  ,
NULL OPERATION_CODE ,
PLN_BPR_VAL_B ,
DECODE(PLN_BPR_VAL_G,-1,NULL,-2,NULL,PLN_BPR_VAL_G)  ,
PLN_CMPL_DATE ,
PLN_INP_VAL_B  ,
DECODE(PLN_INP_VAL_G,-1,NULL,-2,NULL,PLN_INP_VAL_G) ,
PLN_JOB_TIME  ,
PLN_MTL_INP_VAL_B  ,
DECODE(PLN_MTL_INP_VAL_G,-1,NULL,-2,NULL,PLN_MTL_INP_VAL_G)  ,
PLN_OUT_QTY ,
PLN_OUT_VAL_B ,
DECODE(PLN_OUT_VAL_G,-1,NULL,-2,NULL,PLN_OUT_VAL_G) ,
PLN_SCR_VAL_B ,
PLN_SCR_VAL_G  ,
PLN_STRT_DATE ,
PRD_LINE_FK ,
QC_FAIL_QTY  ,
QC_TEST ,
QUEUE_TIME  ,
RESH_REASON_CODE ,
RES_LOOKUP_FK ,
REWORK_QTY  ,
ROUTING,
ROUTING_REVISION,
RUN_TIME  ,
SETUP_TIME  ,
SMPL_CNT  ,
SOB_CURRENCY_FK ,
STD_QTY  ,
STD_TIME  ,
STD_VAL_B  ,
DECODE(STD_VAL_G,-1,NULL,-2,NULL,STD_VAL_G)  ,
STND_HRS_EARNED,
STS_LOOKUP_FK ,
TRX_DATE_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_DETAIL_FCV
    WHERE view_id    = p_view_id
    AND   seq_id    = p_seq_id;

Line: 368

   select OPI_EDW_JOB_DETAIL_INC_S.nextval into l_seq_id from dual;

Line: 369

      /* insert into Incremental table all line_id but not part of missing currency convenrsion rows */
	INSERT /*+ parallel(OPI_EDW_OPM_JOB_DETAIL_INC) */
  	into OPI_EDW_OPM_JOB_DETAIL_INC(PRIMARY_KEY,view_id,seq_id)
      SELECT
        LINE_ID,
        1,
        l_seq_id
      FROM  PM_BTCH_HDR  BH,
         PM_MATL_DTL  BD
      WHERE BH.BATCH_ID   = BD.BATCH_ID
	  AND BH.BATCH_STATUS in (-1,0,1,2,3,4)
	  AND BD.LINE_TYPE=1
        AND GREATEST(BH.LAST_UPDATE_DATE, BD.LAST_UPDATE_DATE)
          BETWEEN g_push_from_date and g_push_to_date
        AND LINE_ID not in
           (SELECT PRIMARY_KEY
              from  OPI_EDW_OPM_JOB_DETAIL_INC
              WHERE SEQ_ID is NULL);

Line: 390

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

Line: 479

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

Line: 482

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

Line: 487

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

Line: 494

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

Line: 509

      rollback;   -- Rollback insert into local staging

Line: 510

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