DBA Data[Home] [Help]

APPS.OPI_EDW_OPMCOGS_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 COGS_PK,COGS_DATE,BASE_CURRENCY_FK
FROM
   OPI_EDW_COGS_FSTG
WHERE
    COLLECTION_STATUS in ('RATE NOT AVAILABLE','INVALID CURRENCY')
AND COGS_PK like '%OPM%';
Line: 79

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

  SELECT count(*) into l_count from OPI_EDW_COGS_FSTG
     WHERE COLLECTION_STATUS in ('RATE NOT AVAILABLE','INVALID CURRENCY')
               AND COGS_PK like '%OPM%';
Line: 89

        /* insert into Incremental table all line_id where Currency is missing */
	INSERT /*+ parallel(OPI_EDW_OPMCOGS_INC) */
  	into OPI_EDW_OPMCOGS_INC (LINE_ID,view_id,seq_id)
      SELECT
        SUBSTRB(ORDER_LINE_ID,1,instrB(ORDER_LINE_ID,'-',1,1)-1),
        1,
        NULL
      FROM
         OPI_EDW_OPMCOGS_FCV
      WHERE
          VIEW_ID=1 AND
          COGS_PK in ( SELECT COGS_PK
               FROM  OPI_EDW_COGS_FSTG
               WHERE COLLECTION_STATUS in ('RATE NOT AVAILABLE','INVALID CURRENCY')
               AND COGS_PK like '%OPM%');
Line: 107

    INSERT /*+ parallel(OPI_EDW_OPMCOGS_INC) */
        	into OPI_EDW_OPMCOGS_INC (LINE_ID,view_id,seq_id)
      SELECT
        SUBSTRB(ORDER_LINE_ID,1,instrB(ORDER_LINE_ID,'-',1,1)-1),
        2,
        NULL
      FROM
         OPI_EDW_OPMCOGS_FCV
      WHERE
          VIEW_ID=2 AND
          COGS_PK in ( SELECT COGS_PK
               FROM  OPI_EDW_COGS_FSTG
               WHERE COLLECTION_STATUS in ('RATE NOT AVAILABLE','INVALID CURRENCY')
               AND COGS_PK like '%OPM%');
Line: 130

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

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

edw_log.Put_line('Inserting Rows into Staging Table');
Line: 166

   Insert Into OPI_EDW_COGS_FSTG
      (COGS_PK
      ,COGS_DATE
      ,COGS_DATE_FK
      ,ORDER_LINE_ID
      ,SHIP_INV_LOCATOR_FK
      ,INSTANCE_FK
      ,TOP_MODEL_ITEM_FK
      ,ITEM_ORG_FK
      ,OPERATING_UNIT_FK
      ,INV_ORG_FK
      ,CUSTOMER_FK
      ,SALES_CHANNEL_FK
      ,PRIM_SALES_REP_FK
      ,PRIM_SALESRESOURCE_FK
      ,BILL_TO_LOC_FK
      ,SHIP_TO_LOC_FK
      ,PROJECT_FK
      ,TASK_FK
      ,ORDER_DATE
      ,BASE_UOM_FK
      ,TRX_CURRENCY_FK
      ,BASE_CURRENCY_FK
      ,ORDER_CATEGORY_FK
      ,ORDER_TYPE_FK
      ,BILL_TO_SITE_FK
      ,SHIP_TO_SITE_FK
      ,MONTH_BOOKED_FK
      ,DATE_BOOKED_FK
      ,DATE_PROMISED_FK
      ,DATE_REQUESTED_FK
      ,DATE_SCHEDULED_FK
      ,DATE_SHIPPED_FK
      ,LOCATOR_FK
      ,ORDER_SOURCE_FK
      ,SET_OF_BOOKS_FK
      ,CAMPAIGN_INIT_FK
      ,CAMPAIGN_ACTL_FK
      ,CAMPAIGN_STATUS_ACTL_FK
      ,CAMPAIGN_STATUS_INIT_FK
      ,MEDCHN_INIT_FK
      ,MEDCHN_ACTL_FK
      ,OFFER_HDR_FK
      ,OFFER_LINE_FK
      ,MARKET_SEGMENT_FK
      ,TARGET_SEGMENT_INIT_FK
      ,TARGET_SEGMENT_ACTL_FK
      ,PROM_EARLY_COUNT
      ,PROM_LATE_COUNT
      ,REQ_EARLY_COUNT
      ,REQ_LATE_COUNT
      ,PROM_EARLY_VAL_G
      ,PROM_LATE_VAL_G
      ,REQ_EARLY_VAL_G
      ,REQ_LATE_VAL_G
      ,REQUEST_LEAD_TIME
      ,PROMISE_LEAD_TIME
      ,ORDER_LEAD_TIME
      ,SHIPPED_QTY_B
      ,RMA_QTY_B
      ,ICAP_QTY_B
      ,COGS_T
      ,COGS_B
      ,COGS_G
      ,RMA_VAL_T
      ,RMA_VAL_G
      ,LAST_UPDATE_DATE
      ,COST_ELEMENT
      ,ACCOUNT
      ,ORDER_NUMBER
      ,WAYBILL_NUMBER
      ,LOT
      ,REVISION
      ,SERIAL_NUMBER
      ,USER_ATTRIBUTE1
      ,USER_ATTRIBUTE2
      ,USER_ATTRIBUTE3
      ,USER_ATTRIBUTE4
      ,USER_ATTRIBUTE5
      ,USER_ATTRIBUTE6
      ,USER_ATTRIBUTE7
      ,USER_ATTRIBUTE8
      ,USER_ATTRIBUTE9
      ,USER_ATTRIBUTE10
      ,USER_ATTRIBUTE11
      ,USER_ATTRIBUTE12
      ,USER_ATTRIBUTE13
      ,USER_ATTRIBUTE14
      ,USER_ATTRIBUTE15
      ,USER_MEASURE1
      ,USER_MEASURE2
      ,USER_MEASURE3
      ,USER_MEASURE4
      ,USER_MEASURE5
      ,USER_FK1
      ,USER_FK2
      ,USER_FK3
      ,USER_FK4
      ,USER_FK5
      ,OPERATION_CODE
      ,COLLECTION_STATUS
      ,CREATION_DATE)
   SELECT /*+ ALL_ROWS */
      COGS_PK
      ,COGS_DATE
      ,COGS_DATE_FK
      ,ORDER_LINE_ID
      ,SHIP_INV_LOCATOR_FK
      ,INSTANCE_FK
      ,TOP_MODEL_ITEM_FK
      ,ITEM_ORG_FK
      ,OPERATING_UNIT_FK
      ,INV_ORG_FK
      ,CUSTOMER_FK
      ,SALES_CHANNEL_FK
      ,PRIM_SALES_REP_FK
      ,PRIM_SALESRESOURCE_FK
      ,BILL_TO_LOC_FK
      ,SHIP_TO_LOC_FK
      ,PROJECT_FK
      ,TASK_FK
      ,ORDER_DATE
      ,BASE_UOM_FK
      ,TRX_CURRENCY_FK
      ,BASE_CURRENCY_FK
      ,ORDER_CATEGORY_FK
      ,ORDER_TYPE_FK
      ,BILL_TO_SITE_FK
      ,SHIP_TO_SITE_FK
      ,MONTH_BOOKED_FK
      ,DATE_BOOKED_FK
      ,DATE_PROMISED_FK
      ,DATE_REQUESTED_FK
      ,DATE_SCHEDULED_FK
      ,DATE_SHIPPED_FK
      ,LOCATOR_FK
      ,ORDER_SOURCE_FK
      ,SET_OF_BOOKS_FK
      ,CAMPAIGN_INIT_FK
      ,CAMPAIGN_ACTL_FK
      ,CAMPAIGN_STATUS_ACTL_FK
      ,CAMPAIGN_STATUS_INIT_FK
      ,MEDCHN_INIT_FK
      ,MEDCHN_ACTL_FK
      ,OFFER_HDR_FK
      ,OFFER_LINE_FK
      ,MARKET_SEGMENT_FK
      ,TARGET_SEGMENT_INIT_FK
      ,TARGET_SEGMENT_ACTL_FK
      ,PROM_EARLY_COUNT
      ,PROM_LATE_COUNT
      ,REQ_EARLY_COUNT
      ,REQ_LATE_COUNT
      ,PROM_EARLY_VAL_G
      ,PROM_LATE_VAL_G
      ,REQ_EARLY_VAL_G
      ,REQ_LATE_VAL_G
      ,REQUEST_LEAD_TIME
      ,PROMISE_LEAD_TIME
      ,ORDER_LEAD_TIME
      ,SHIPPED_QTY_B
      ,RMA_QTY_B
      ,ICAP_QTY_B
      ,COGS_T
      ,COGS_B
      ,COGS_G
      ,RMA_VAL_T
      ,RMA_VAL_G
      ,LAST_UPDATE_DATE
      ,COST_ELEMENT
      ,ACCOUNT
      ,ORDER_NUMBER
      ,WAYBILL_NUMBER
      ,LOT
      ,REVISION
      ,SERIAL_NUMBER
      ,USER_ATTRIBUTE1
      ,USER_ATTRIBUTE2
      ,USER_ATTRIBUTE3
      ,USER_ATTRIBUTE4
      ,USER_ATTRIBUTE5
      ,USER_ATTRIBUTE6
      ,USER_ATTRIBUTE7
      ,USER_ATTRIBUTE8
      ,USER_ATTRIBUTE9
      ,USER_ATTRIBUTE10
      ,USER_ATTRIBUTE11
      ,USER_ATTRIBUTE12
      ,USER_ATTRIBUTE13
      ,USER_ATTRIBUTE14
      ,USER_ATTRIBUTE15
      ,USER_MEASURE1
      ,USER_MEASURE2
      ,USER_MEASURE3
      ,USER_MEASURE4
      ,USER_MEASURE5
      ,USER_FK1
      ,USER_FK2
      ,USER_FK3
      ,USER_FK4
      ,USER_FK5
      ,NULL -- OPERATION_CODE
      ,DECODE(COGS_G,-1,'RATE NOT AVAILABLE',-2,'INVALID CURRENCY','LOCAL READY')
      ,LAST_UPDATE_DATE
from OPI_EDW_OPMCOGS_FCV
WHERE view_id    = p_view_id
 AND  seq_id     = p_seq_id;
Line: 374

edw_log.Put_line('Insert completed in Staging tables');
Line: 398

   select OPI_EDW_OPMCOGS_INC_S.nextval into l_seq_id from dual;
Line: 400

	INSERT /*+ parallel(OPI_EDW_OPMCOGS_INC) */
  	into OPI_EDW_OPMCOGS_INC (LINE_ID,view_id,seq_id)
      SELECT
        SD.LINE_ID,
        1,
        l_seq_id
     FROM
         OP_ORDR_HDR SH,
         OP_ORDR_DTL SD,
         SY_ORGN_MST OM,
         GL_PLCY_MST  PM
     WHERE SH.order_id = sd.order_id
      AND SH.orgn_code = OM.orgn_code
      AND OM.co_CODE  = PM.co_code
      AND SD.LINE_STATUS >= 20
      AND GREATEST(SH.LAST_UPDATE_DATE, SD.LAST_UPDATE_DATE,PM.LAST_UPDATE_DATE)
      between g_push_from_date and g_push_to_date and
      sd.line_id not in
          (select LINE_ID from OPI_EDW_OPMCOGS_INC
                  WHERE VIEW_ID = 1 AND SEQ_ID is NULL);
Line: 422

      UPDATE  OPI_EDW_OPMCOGS_INC set SEQ_Id=l_SEQ_ID
              WHERE VIEW_ID=1 AND SEQ_ID is NULL;
Line: 428

	INSERT /*+ parallel(OPI_EDW_OPMCOGS_INC) */
  	into OPI_EDW_OPMCOGS_INC (LINE_ID,view_id,seq_id)
      SELECT
        IT.LINE_ID,
        2,
        l_seq_id
     FROM
       OE_ORDER_HEADERS_ALL OOH,
       OE_ORDER_LINES_ALL OOL,
       IC_TRAN_VW1        IT
     WHERE OOH.HEADER_ID       = OOL.HEADER_ID  AND
       OOH.ORG_ID              = OOL.ORG_ID     AND
       OOL.LINE_CATEGORY_CODE  = 'ORDER'        AND
       IT.DOC_ID               = OOH.HEADER_ID  AND
       IT.LINE_ID              = OOL.LINE_ID    AND
       IT.DOC_TYPE             = 'OMSO'         AND
       GREATEST(OOL.LAST_UPDATE_DATE, OOH.LAST_UPDATE_DATE)
      between g_push_from_date and g_push_to_date AND
      IT.LINE_ID not in
       (select LINE_ID from OPI_EDW_OPMCOGS_INC
                  WHERE VIEW_ID = 2 AND SEQ_ID is NULL);
Line: 451

      UPDATE  OPI_EDW_OPMCOGS_INC set SEQ_Id=l_SEQ_ID
              WHERE VIEW_ID=2 AND SEQ_ID is NULL;
Line: 554

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

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

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

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

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

      UPDATE OPI_EDW_COGS_FSTG cogs
      SET PRIM_SALESRESOURCE_FK =
             (select sr.salesrep_id||'-'||sr.org_id||'-'||cogs.instance_fk||'-SALESREP-PERS'
              FROM RA_SALESREPS_ALL sr
              WHERE sr.SALESREP_NUMBER = substrb(PRIM_SALESRESOURCE_FK,1,instrb(PRIM_SALESRESOURCE_FK,'-',-1,1)-1)
                AND sr.org_id =  substrb(PRIM_SALESRESOURCE_FK,instrb(PRIM_SALESRESOURCE_FK,'-',-1,1)+1))
      WHERE PRIM_SALESRESOURCE_FK <> 'NA_EDW'
        AND COLLECTION_STATUS = 'LOCAL READY';
Line: 591

      delete OPI_EDW_OPMCOGS_INC;
Line: 598

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

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

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

      delete OPI_EDW_OPMCOGS_INC;
Line: 623

      delete OPI_EDW_OPMCOGS_INC;
Line: 638

      delete OPI_EDW_OPMCOGS_INC;