DBA Data[Home] [Help]

APPS.OPI_EDW_OPMINV_DAILY_STAT_F_C SQL Statements

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

Line: 20

   SELECT instance_code INTO   l_source
   FROM   edw_local_instance;
Line: 23

   SELECT instance_code INTO   l_target
   FROM   edw_local_instance@edw_apps_to_wh;
Line: 43

SELECT INV_DAILY_STATUS_PK,TRX_DATE_FK,BASE_CURRENCY_FK
FROM
   OPI_EDW_INV_DAILY_STAT_FSTG
WHERE
    COLLECTION_STATUS in ('RATE NOT AVAILABLE','INVALID CURRENCY')
AND INV_DAILY_STATUS_PK like '%OPM';
Line: 81

       /* Delete the incremental table before inserting new data */
      DELETE OPI_EDW_OPMINV_DAILY_STAT_INC WHERE SEQ_ID IS NOT NULL;
Line: 86

  SELECT count(*) into l_count from opi_edw_inv_daily_stat_fstg where
       COLLECTION_STATUS in ('RATE NOT AVAILABLE','INVALID CURRENCY')
       AND INV_DAILY_STATUS_PK like '%OPM';
Line: 90

        /* insert into Incremental table all line_id where Currency is missing */
	INSERT /*+ parallel(OPI_EDW_OPMINV_DAILY_STAT_INC) */
  	into OPI_EDW_OPMINV_DAILY_STAT_INC
           ( PRIMARY_KEY,
             PRIMARY_KEY1,
             PRIMARY_KEY2,
             PRIMARY_KEY3,
             PRIMARY_KEY4,
             PRIMARY_KEY5,
             PRIMARY_KEY6,
             VIEW_ID,
             SEQ_ID)
      SELECT
         CO_CODE,
	 ORGN_CODE,
	 WHSE_CODE,
	 LOCATION,
	 ITEM_ID,
	 LOT_ID,
	 TRX_DATE,
        1,
        NULL
      FROM
         OPI_EDW_OPMINV_DAILY_STAT_FCV
      WHERE
          INV_DAILY_STATUS_PK in ( SELECT INV_DAILY_STATUS_PK
               FROM  opi_edw_inv_daily_stat_fstg
               WHERE COLLECTION_STATUS in ('RATE NOT AVAILABLE','INVALID CURRENCY')
                 AND INV_DAILY_STATUS_PK like '%OPM');
Line: 128

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

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

   select OPI_EDW_OPMINV_DAILY_INC_S.nextval into l_seq_id from dual;
Line: 160

      /* insert into Incremental table all line_id but not part of missing currency convenrsion rows */
	INSERT /*+ parallel(OPI_EDW_OPMINV_DAILY_STAT_INC) */
  	into OPI_EDW_OPMINV_DAILY_STAT_INC
           ( PRIMARY_KEY,
             PRIMARY_KEY1,
             PRIMARY_KEY2,
             PRIMARY_KEY3,
             PRIMARY_KEY4,
             PRIMARY_KEY5,
             PRIMARY_KEY6,
             VIEW_ID,
             SEQ_ID)
      SELECT
         CO_CODE,
	 ORGN_CODE,
	 WHSE_CODE,
	 LOCATION,
	 ITEM_ID,
	 LOT_ID,
	 TRX_DATE,
         1,
         L_SEQ_ID
      FROM
         OPI_PMI_INV_DAILY_STAT_SUM OPM
      WHERE OPM.LAST_UPDATE_DATE BETWEEN g_push_from_date and g_push_to_date
        AND  CO_CODE||ORGN_CODE||WHSE_CODE||LOCATION||ITEM_ID||LOT_ID||TRX_DATE
           not in
           (SELECT PRIMARY_KEY||PRIMARY_KEY1||PRIMARY_KEY2||PRIMARY_KEY3||PRIMARY_KEY4
                   ||PRIMARY_KEY5||PRIMARY_KEY6
              from  OPI_EDW_OPMINV_DAILY_STAT_INC
              WHERE SEQ_ID is NULL);
Line: 194

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

     INSERT INTO opi_edw_inv_daily_stat_fstg(AVG_INT_QTY
     ,AVG_INT_VAL_B
     ,AVG_INT_VAL_G
     ,AVG_ONH_QTY
     ,AVG_ONH_VAL_B
     ,AVG_ONH_VAL_G
     ,AVG_WIP_QTY
     ,AVG_WIP_VAL_B
     ,AVG_WIP_VAL_G
     ,BASE_CURRENCY_FK
     ,BASE_UOM_FK
     ,BEG_INT_QTY
     ,BEG_INT_VAL_B
     ,BEG_INT_VAL_G
     ,BEG_ONH_QTY
     ,BEG_ONH_VAL_B
     ,BEG_ONH_VAL_G
     ,BEG_WIP_QTY
     ,BEG_WIP_VAL_B
     ,BEG_WIP_VAL_G
     ,COMMODITY_CODE
     ,COST_GROUP
     ,CREATION_DATE
     ,END_INT_QTY
     ,END_INT_VAL_B
     ,END_INT_VAL_G
     ,END_ONH_QTY
     ,END_ONH_VAL_B
     ,END_ONH_VAL_G
     ,END_WIP_QTY
     ,END_WIP_VAL_B
     ,END_WIP_VAL_G
     ,FROM_ORG_QTY
     ,FROM_ORG_VAL_B
     ,FROM_ORG_VAL_G
     ,INSTANCE_FK
     ,INV_ADJ_QTY
     ,INV_ADJ_VAL_B
     ,INV_ADJ_VAL_G
     ,INV_DAILY_STATUS_PK
     ,INV_ORG_FK
     ,ITEM_ORG_FK
     ,ITEM_STATUS
     ,ITEM_TYPE
     ,LAST_UPDATE_DATE
     ,LOCATOR_FK
     ,LOT_FK
     ,NETTABLE_FLAG
     ,PO_DEL_QTY
     ,PO_DEL_VAL_B
     ,PO_DEL_VAL_G
     ,PRD_DATE_FK
     ,TOTAL_REC_QTY
     ,TOTAL_REC_VAL_B
     ,TOTAL_REC_VAL_G
     ,TOT_CUST_SHIP_QTY
     ,TOT_CUST_SHIP_VAL_B
     ,TOT_CUST_SHIP_VAL_G
     ,TOT_ISSUES_QTY
     ,TOT_ISSUES_VAL_B
     ,TOT_ISSUES_VAL_G
     ,TO_ORG_QTY
     ,TO_ORG_VAL_B
     ,TO_ORG_VAL_G
     ,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
     ,WIP_ASSY_QTY
     ,WIP_ASSY_VAL_B
     ,WIP_ASSY_VAL_G
     ,WIP_COMP_QTY
     ,WIP_COMP_VAL_B
     ,WIP_COMP_VAL_G
     ,WIP_ISSUE_QTY
     ,WIP_ISSUE_VAL_B
     ,WIP_ISSUE_VAL_G
     ,TRX_DATE
     ,PERIOD_FLAG
     ,OPERATION_CODE
     ,COLLECTION_STATUS)
  SELECT /*+ ALL_ROWS */
      AVG_INT_QTY
     ,AVG_INT_VAL_B
     ,AVG_INT_VAL_G
     ,AVG_ONH_QTY
     ,AVG_ONH_VAL_B
     ,AVG_ONH_VAL_G
     ,AVG_WIP_QTY
     ,AVG_WIP_VAL_B
     ,AVG_WIP_VAL_G
     ,BASE_CURRENCY_FK
     ,BASE_UOM_FK
     ,BEG_INT_QTY
     ,BEG_INT_VAL_B
     ,BEG_INT_VAL_G
     ,BEG_ONH_QTY
     ,BEG_ONH_VAL_B
     ,BEG_ONH_VAL_G
     ,BEG_WIP_QTY
     ,BEG_WIP_VAL_B
     ,BEG_WIP_VAL_G
     ,COMMODITY_CODE
     ,COST_GROUP
     ,CREATION_DATE
     ,END_INT_QTY
     ,END_INT_VAL_B
     ,END_INT_VAL_G
     ,END_ONH_QTY
     ,END_ONH_VAL_B
     ,END_ONH_VAL_G
     ,END_WIP_QTY
     ,END_WIP_VAL_B
     ,END_WIP_VAL_G
     ,FROM_ORG_QTY
     ,FROM_ORG_VAL_B
     ,FROM_ORG_VAL_G
     ,INSTANCE_FK
     ,INV_ADJ_QTY
     ,INV_ADJ_VAL_B
     ,INV_ADJ_VAL_G
     ,INV_DAILY_STATUS_PK
     ,INV_ORG_FK
     ,ITEM_ORG_FK
     ,ITEM_STATUS
     ,ITEM_TYPE
     ,LAST_UPDATE_DATE
     ,LOCATOR_FK
     ,LOT_FK
     ,NETTABLE_FLAG
     ,PO_DEL_QTY
     ,PO_DEL_VAL_B
     ,PO_DEL_VAL_G
     ,PRD_DATE_FK
     ,TOTAL_REC_QTY
     ,TOTAL_REC_VAL_B
     ,TOTAL_REC_VAL_G
     ,TOT_CUST_SHIP_QTY
     ,TOT_CUST_SHIP_VAL_B
     ,TOT_CUST_SHIP_VAL_G
     ,TOT_ISSUES_QTY
     ,TOT_ISSUES_VAL_B
     ,TOT_ISSUES_VAL_G
     ,TO_ORG_QTY
     ,TO_ORG_VAL_B
     ,TO_ORG_VAL_G
     ,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
     ,WIP_ASSY_QTY
     ,WIP_ASSY_VAL_B
     ,WIP_ASSY_VAL_G
     ,WIP_COMP_QTY
     ,WIP_COMP_VAL_B
     ,WIP_COMP_VAL_G
     ,WIP_ISSUE_QTY
     ,WIP_ISSUE_VAL_B
     ,WIP_ISSUE_VAL_G
     ,TRX_DATE
     ,PERIOD_FLAG
     ,NULL
     ,DECODE(END_ONH_VAL_G,-1,'RATE NOT AVAILABLE',-2,'INVALID CURRENCY','LOCAL READY')
    FROM opi_edw_opminv_daily_stat_fcv
    WHERE view_id=p_view_id
      AND seq_id = p_seq_id;
Line: 538

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

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

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

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

   /* Update Data Which has been pushed */
    UPDATE OPI_PMI_INV_DAILY_STAT_SUM
    SET DATA_PUSHED_IND = 1
    WHERE LAST_UPDATE_DATE BETWEEN g_push_from_date AND g_push_to_date AND
       CO_CODE||ORGN_CODE||WHSE_CODE||LOCATION||ITEM_ID||LOT_ID||TRX_DATE
           not in
           (SELECT PRIMARY_KEY||PRIMARY_KEY1||PRIMARY_KEY2||PRIMARY_KEY3||PRIMARY_KEY4
                   ||PRIMARY_KEY5||PRIMARY_KEY6
              from  OPI_EDW_OPMINV_DAILY_STAT_INC
              WHERE SEQ_ID is NULL);
Line: 588

      rollback;   -- Rollback insert into local staging
Line: 589

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