2 Replies Latest reply: Feb 5, 2014 5:57 PM by Rob Wunderlich RSS

    Incremental data help

      We have a daily  scheduled data extractor to extract data from oracle database with incremental script ( below script),  when we  cross verified  the data after some point of time let’s say 1 month we found that some data is missing it’s not up to date. please check the below script and advise...

       

      // Loading AR Customer Sites
      SET
      vQvdFile='$(vFolderSourceData)CustomerMaster.qvd';

          LET vQvdExists = if(FileSize('$(vQvdFile)') > 0, -1, 0);
      Set vToday = date(Today(),'DD-MMM-YYYY hh:mi:ss');
      Trace >>> Running Customr Sites Scripts;
      Trace >>> Running Script at $(vToday);
      IF
      $(vQvdExists) THEN
      maxdateTab:
      LOAD
      max(Last_Update_Date) as maxdate
      FROM
      $(vQvdFile) (qvd);

      LET vIncrementalExpression = 'WHERE LAST_UPDATE_DATE >= ' & chr(39) & date(peek('maxdate'),'DD-MMM-YYYY') &chr(39) ;
      DROP
      table maxdateTab;
      ELSE

      LET
      vIncrementalExpression = '';
      END
      IF
      TRACE
      >>> SQL Query Where Clause  : $(vIncrementalExpression);
      [AR_CustomerSites] :

          Load Distinct

          COMPANY as Company,
      CUSTOMER_ID
      as Customer_Id,
      CUSTOMER_NUMBER
      as Customer_Number,
      CUSTOMER_NAME
      as Customer_Name,
      PARTY_ID
      as Party_Id,
      PARTY_SITE_ID
      as Party_Site_Id,
      CREATION_DATE
      as Creation_Date,
      CREATED_BY
      as Created_By,
      CUSTOMER_CATEGORY_CODE
      as Customer_Category_Code,
      TRADE_LICENCE_NO
      as Trade_License_No,
      TRADE_LIC_EXP_DATE
      as Trade_License_Expiry_Date,
      SALESREP_ID
      as Salesrep_Id,
      ADDRESS1
      as Address1,

      ADDRESS2 as Address2,
      ADDRESS3
      as Address3,
      ADDRESS4
      as Address4,
      CITY
      as City,

      COUNTRY_CODE as Country_Code,
      PO_BOX_NUMBER
      as PO_Box,
      SITE_NAME
      as Site_Name,
      SITE_CODE
      as Site_Code,

      SITE_USE_CODE as Site_Use_Code,
      SITE_USE_ID
      as Site_Use_Id,
      LOCATION_ID
      as Location_Id,
      LOCATION_CODE
      as Location_Code,
      ORG_ID
      as Org_Id,
      ORIG_SYSTEM_REFERENCE
      as Org_System_Reference,
      CUST_SITE_STATUS
      as Cust_Site_Status
      CREDIT_LIMIT
      as Credit_Limit,
      TERM_ID
      as Payment_Term_Id,
      PARTY_SITE_NAME
      as Pasty_Site_Name,
      CREDIT_HOLD
      as Credit_Hold,
      CREDIT_CHECK
      as Credit_Check,
      COLLECTOR
      as Collector,
      GL_ID_REC
      as Code_Combination_Id,
      REC_ACCOUNT
      as GL_Account,
      PHONE
      as Phone,
      FAX
      as Fax,
      CONTACT_PERSON
      as Contact_Person,
      CONTACT_PERSON_PHONE
      as Contact_Person_Phone,
      LAST_UPDATE_DATE
      as Last_Update_Date,
      CREDIT_HOLD_STATUS
      as Credit_Hold_Status
      SQL
      select
      COMPANY,

      CUSTOMER_ID,

      CUSTOMER_NUMBER,
      CUSTOMER_NAME,
      PARTY_ID,
      PARTY_SITE_ID,
      CREATION_DATE,
      CREATED_BY,
      CUSTOMER_CATEGORY_CODE,
      TRADE_LICENCE_NO,
      TRADE_LIC_EXP_DATE,
      SALESREP_ID,
      ADDRESS1,
      ADDRESS2,
      ADDRESS3,
      ADDRESS4,
      CITY,
      COUNTRY_CODE,

      PO_BOX_NUMBER,
      SITE_NAME,
      SITE_CODE,
      SITE_USE_CODE,
      SITE_USE_ID,
      LOCATION_ID,
      LOCATION_CODE,
      ORG_ID,
      ORIG_SYSTEM_REFERENCE,
      CUST_SITE_STATUS,
      CREDIT_LIMIT,
      TERM_ID,
      PARTY_SITE_NAME,
      CREDIT_HOLD,

      CREDIT_CHECK,
      COLLECTOR,
      GL_ID_REC,
      REC_ACCOUNT,

      PHONE,
      FAX,
      CONTACT_PERSON,
      CONTACT_PERSON_PHONE,
      LAST_UPDATE_DATE,
      CREDIT_HOLD_STATUS
      from APPS.XXBI_CUST_SITE_DETAILS 
      $(vIncrementalExpression) ;
      LET
      vNumRows = NoOfRows('AR_CustomerSites');
      TRACE
      >>> Table --> AR_CustomerSites New records loaded with : $(vNumRows)
      IF $(vQvdExists) THEN
      CONCATENATE
      (AR_CustomerSites)
      LOAD
      * FROM $(vQvdFile) (qvd)
      WHERE
      NOT exists(Party_Site_Id);
      END
      IF

      STORE
      [AR_CustomerSites] into $(vQvdFile) (qvd);
      LET
      vNumRows = NoOfRows('AR_CustomerSites');
      TRACE
      >>> Table --> AR_CustomerSites Table loaded with : $(vNumRows)
      Drop Table [AR_CustomerSites];



      rergards

      Madhu

        • Re: Incremental data help
          Tyler Waterfall

          Madhu G,

          From your script I would assume that the data gets updated daily and is unique by day, meaning that once the database is updated for a given day, the data for that day is never updated. Is that the case?

           

          I ask because we saw issues with this type of date-driven incremental loading because the database was updating older records (than the ones that were updated since our last reload). Records were being modified, reinstated, or removed.

          • Re: Incremental data help
            Rob Wunderlich

            It looks ok because you are testing LAST_UPDATE_DATE which I assume gets updated when an older record gets updated.

             

            Is PARTY_SITE_ID a true primary key? That is, is it unique for every record?

             

            -Rob