1 Reply Latest reply: May 21, 2012 12:47 AM by 577838 RSS

    PLZ Help me . . . Need some help in OWB 10.2 to upload Flat Files

    577838
      Hello to every one,

      I am new on OWB and working to implement CCnB warehouse through OWB. I have generate and deploy 3 Dimenssions and also their mapping and work flows in OWB and they all are working well (eg: Account Alert, Person Identifier, Meter).

      Now i am facing the problem in Meter Location Dimenssion b/c in Intial Upload Files or Flat Files of ML(Meter Location) 1 meter has several locations but 1 will be active at a time so the EFF_END_DT will be 4000 of year only with active while I my problem is that when I upload my Initial Files then it pass EFF_END_DT with year 4000 with every location of a meter.


      My update procedure in Transformation of OWB for meter location is as below(I have tested it with both MTR_ID and SRC_MTR_LOC_ID) :

      CREATE OR REPLACE PROCEDURE DWADM."SPL_ML_UPD_PRC"("P_BULK_SIZE" IN NUMBER) IS

      ws_stmt VARCHAR2(80);

      CURSOR c_STG_ML_EXT IS
      SELECT
      TO_NUMBER(STG.DATA_SOURCE_IND )
      ,STG.CHANGE_TYPE_CD
      -- ,RTRIM(STG.SRC_MTR_LOC_ID) SRC_MTR_LOC_ID
      ,RTRIM(STG.MTR_ID) MTR_ID
      ,TO_DATE(RTRIM(SUBSTR(STG.UPDATE_DTTM,1,14)),'YYYYMMDDHH24MISS')
      ,JCTL.JOB_NBR
      FROM STG_ML_EXT STG
      ,STG_ML_CTL_EXT CTL
      ,B1_ETL_JOB_CTRL JCTL
      WHERE JCTL.BATCH_CD = CTL.BATCH_CD
      AND JCTL.BATCH_NBR = TO_NUMBER(CTL.BATCH_NBR)
      AND JCTL.BATCH_THREAD_NBR = TO_NUMBER(CTL.BATCH_THREAD_NBR)
      AND JCTL.JOB_STATUS_FLG = 'IP'
      ;


      TYPE T_DATA_SOURCE_IND IS TABLE OF NUMBER(6) INDEX BY BINARY_INTEGER;
      TYPE T_CHANGE_TYPE_CD IS TABLE OF CHAR(1) INDEX BY BINARY_INTEGER;
      --TYPE T_SRC_MTR_LOC_ID IS TABLE OF VARCHAR2(12) INDEX BY BINARY_INTEGER;
      TYPE T_MTR_ID IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;
      TYPE T_UPDATE_DTTM IS TABLE OF DATE INDEX BY BINARY_INTEGER;
      TYPE T_JOB_NBR IS TABLE OF NUMBER(12) INDEX BY BINARY_INTEGER;


      --A_SRC_MTR_LOC_ID T_SRC_MTR_LOC_ID;
      A_MTR_ID T_MTR_ID;
      A_DATA_SOURCE_IND T_DATA_SOURCE_IND;
      A_CHANGE_TYPE_CD T_CHANGE_TYPE_CD;
      A_UPDATE_DTTM T_UPDATE_DTTM;
      A_JOB_NBR T_JOB_NBR;

      BEGIN

      OPEN C_STG_ML_EXT;

      -- A_SRC_MTR_LOC_ID.DELETE;
      A_MTR_ID.DELETE;
      A_DATA_SOURCE_IND.DELETE;
      A_CHANGE_TYPE_CD.DELETE;
      A_UPDATE_DTTM.DELETE;
      A_JOB_NBR.DELETE;

      LOOP

      FETCH C_STG_ML_EXT BULK COLLECT INTO
      A_DATA_SOURCE_IND,A_CHANGE_TYPE_CD,A_MTR_ID, A_UPDATE_DTTM,A_JOB_NBR LIMIT p_bulk_size;

      IF C_STG_ML_EXT%NOTFOUND AND A_DATA_SOURCE_IND.COUNT = 0 THEN
      EXIT;
      END IF;

      FORALL i IN 1..A_DATA_SOURCE_IND.COUNT
      UPDATE
      CD_ML
      SET EFF_END_DTTM = A_UPDATE_DTTM(i)
      ,JOB_NBR = A_JOB_NBR(i)
      WHERE MTR_ID = A_MTR_ID(i)
      AND DATA_SOURCE_IND = A_DATA_SOURCE_IND(i)
      AND EFF_END_DTTM = TO_DATE('01014000','MMDDYYYY')
      ;

      END LOOP;

      END;
      /


      and I am using Flat Files in .DAT format.

      Edited by: Grid Component on May 20, 2012 10:48 PM

      Edited by: Grid Component on May 20, 2012 11:20 PM