Skip to Content

CRM Order Status often gets changed in various stages such as Approved, Completed, and Rejected etc.     

Analytics Report is required to track all the change history for the various above user statuses in a CRM Order Header:

  1. Who approved it  or rejected it or completed it
  2. When it got approved or rejected or completed.

CRM Tables:

  1. CRMD _ORDERADM_H: Header Table for all CRM Transactions.
  2. CRM_JCDS: Change History for System/User Statuses.

We need to check and make sure that we are extracting Delta on daily basis considering CRM Order Header and User Status Change log history Tables sizes.

Let’s say we have to extract only User status as below:

  1. E0005- CRM Order gets approved
  2. I1005 – CRM Order gets completed.
  3. I1032 – CRM Order gets rejected.

We can achieve it by both the options creating Generic extractor on top of view and also using FM.

I will showcase the 2nd Option of doing it via Generic Delta extractor using FM as i have to do some other calculations here as well.

  1. Create an Extract Structure consisting of all the fields required from both the Tables.

I1.jpg

Make a note DLTDATE this is not available in any source Tables only being used to extract delta. This would contain the value when the last successful delta happened to SAP BW once the Extractor is initialized.

2. Create Custom Function Module copying Standard FM RSAX_BIW_GET_DATA_SIMPLE.

    1. Pass all the BW IP selections (If any) to source system to make sure only required valid data gets extracted.  Also DLTDATE (Delta Date) values are being passed from RSA7 (Delta Queue) going forward once the delta gets initialized successfully.

      LOOP AT s_s_ift_select INTO l_s_select WHERE fieldnm = ‘TRAN_ID’.
             
      MOVE-CORRESPONDING l_s_select TO l_r_object_id.
             
      APPEND l_r_object_id.
           
      ENDLOOP.

            LOOP AT s_s_ift_select INTO l_s_select WHERE fieldnm = ‘HEADER_GUID’.
             
      MOVE-CORRESPONDING l_s_select TO l_r_guid.
             
      APPEND l_r_guid.
           
      ENDLOOP.

            LOOP AT s_s_ift_select INTO l_s_select WHERE fieldnm = ‘PROCESS_TYPE’.
             
      MOVE-CORRESPONDING l_s_select TO l_r_process_type.
             
      APPEND l_r_process_type.
           
      ENDLOOP.

            LOOP AT s_s_ift_select INTO l_s_select WHERE fieldnm = ‘DLTDATE’.
             
      MOVE-CORRESPONDING l_s_select TO l_r_dltdate.
             
      APPEND l_r_dltdate.
             
      MOVE-CORRESPONDING l_s_select TO l_r_date.
              
      APPEND l_r_date.       
           
      ENDLOOP.

 

II. Fetch Change Log Data for status change at header level joining both the Table and Comparing Dates CRM_JCDS-UDATE with L_R_Date

(This contains date range from last successful delta updated into SAP BW).

   Please note we are only extracting the delta records satisfying the join Condition (Header GUID of CRM Order/Transaction) and comparing the Dates.


OPEN CURSOR WITH HOLD s_cursor FOR
     
SELECT
        crmd_orderadm_h
~guid
        crmd_orderadm_h
~object_id
        crmd_orderadm_h
~created_at
        crmd_orderadm_h
~created_by
        crm_jcds
~stat
        crm_jcds
~chgnr
        crm_jcds
~usnam
        crm_jcds
~udate
        crm_jcds
~utime
       
FROM crm_jcds
        INNER
JOIN crmd_orderadm_h
       
ON crm_jcds~objnr = crmd_orderadm_h~guid
       
WHERE crm_jcds~udate IN l_r_date
         
AND crm_jcds~stat IN (‘E0005’,‘I1005’,‘I1032’ )
         
AND crm_jcds~chind IN (‘I’,‘U’)
         
AND crm_jcds~inact NE ‘X’
         
AND crmd_orderadm_h~guid IN l_r_guid
         
AND crmd_orderadm_h~object_id IN l_r_object_id.

FETCH NEXT CURSOR s_cursor
                   
APPENDING CORRESPONDING FIELDS
                   
OF TABLE lt_result
                    PACKAGE
SIZE s_s_ifmaxsize.
   
IF sysubrc <> 0.
     
CLOSE CURSOR s_cursor.
     
RAISE no_more_data.
   
ENDIF.

III. Here we are just creating Timestamp concatenating CRM_JCDS-UDATE and CRM_JCDS-UDATE.

    LOOP AT lt_result INTO ls_result.
      e_t_data
header_guid = ls_resultguid.
      e_t_data
tran_id     = ls_resultobject_id.
      e_t_data
created_at  = ls_resultcreated_at.
      e_t_data
created_by  = ls_resultcreated_by.
      e_t_data
STAT  = ls_resultstat.
      e_t_data
stat_changed_by = ls_resultusnam.


     
CONCATENATE ls_resultudate ls_resultutime INTO e_t_datastat_changed_at.

IV. Extractor Output

I2.png

3. Generic Delta setting and how it works:

   

    I3.png

  Make a note of Delta Date ‘DLTDATE’ here for the first time it will not pass any value as no successful delta happen till now. But once the first Delta gets  initialized successfully then it would check Delta Queue status take into consideration the upper and lower limit as well if defined for delta extraction in generic extractor.

Once the Delta was initialized then the DLTDATE (used in Generic Delta settings) contains the following value in RSA7 (Delta Queue). It shows when the data was successfully transferred to BW from CRM.

Note: How does it work taking into consideration the upper and lower limits. All below examples taking into consideration the last successful delta to BW that is 09/18/2015.

Delta Logic/Formula

RSA7 < Delta <= D (Day on which the load is run)

If we are running the load on 09/19/2015 then

09/18/2015 <Delta <= 09/19/2015

Now on 20th

09/19/2015 <Delta <= 09/20/2015

The above can lead to wrong calculations as more records can be created for 20th and 19th and the same would be missed by extractor.

  1. If upper limit of 1 is defined

RSA7 < Delta <= D-1  (Day on which the load is run)

If we are running the load on 09/19/2015 then

09/18/2015 < Delta <= 09/18/2015- 19th we are getting all of 18th (This is correct as no more records of 18th can be created on 19th)

Now on 20th

09/19/2015 < Delta <= 09/19/2015- 20th we are getting all of 19th (same as above).

2. If lower limit of 1 is defined

RSA7-1 < Delta <= D  (Day on which the load is run)

If we run the load on 19th

09/17/2015 <Delta<=09/19/2015 – 19th we r getting all of 18th and 19th  (This is wrong because more records can be created on 18th)

Now if we run the load on 20th

09/18/2015 <Delta<=20    – 20th we are getting all of 19th and 20th (this only includes partial of 20th created till now as more can be created for 20th).

Here records are not missed but are repeating. Its recommended to define upper limit in all such scenario’s.

Its always better to make a note of upper and lower limits which buliding extractor. 🙂

I would like to thank my colleague Prabhav Tandon  for working with me on thiss requirement.

Thanks

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply