Header Ads

Working with Get_Dquery_Definition

7:32 PM , , , ,

SAP CRM  Account search

When searching for accounts we f.i. can't search for their Sales Area Data. But let's say we only want to display accounts that have a certain Sales Office. This guide explains how to add a field from one component (Sales) into a search of another (Account Search). Of course the data has to be related -> check the GENIL Model Browser.

Step by Step

1. Get technical data of the Account Search

Log in to the Web UI and navigate to the Account Management. Under Search, click on Accounts. Now click in any input field and press F2 to get the technical data. So we write down:
Component: BP_HEAD_SEARCH
View: MainSearch
Context Node:  SEARCH
Role Key (Found): ZSALES

2. Get technical data of the Sales Office

Log in to the Web UI and navigate to the Account Management. Under Search, click on Accounts. Search for any account and look at it. Find the Sales Area Data and click on Edit respectively New. Locate Sales Office under Organization and click into the picklist. While still clicking, press F2 to get the technical data. So we write down:
Component: BP_SALES
View: CorpAccountOrgEF
Context Node: BUILRESORG
Attribute: STRUCT.SALES_OFFICE

3. Get the dictionary structure of the Account Search

We can find structures through the Genil Model Browser - Transaction GENIL_MODEL_BROWSER. Choose Component Set ALL and press F8. Open the node Search Objects and locate BuilHeaderSearch. Open Attribute Structure and write down...
Account Head Search Structure:    CRMT_BUPA_IL_HEADER_SEARCH

4. Get the dictionary data element of the Sales Office

Again open the Genil Model Browser with Component Set ALL. We know that account and sales data are related. So we search through the Relations.
Open Root Objects and locate BuilHeader. Open node Relations and search for the sales relation. You'll find one called BuilSalesArrangementRel.
Open it and again open the Relations node. There you'll find an object called BuilResponsibleOrganisationRel. Open this relation and its object. Now open Attribute Structure and the structure below it: there is our Sales Office. Open it to get the dictionary data element and write it down...
Sales Office Data Element: CRMT_SALES_OFFICE

5. Extend the Account Search structure

Open structure CRMT_BUPA_IL_HEADER_SEARCH and click on the "Append Structure" button. Create a new structure in your customer Namespace i.e. ZCRMT_BUPA_IL_HEADER_SEARCH and add the Sales Office as ZZSALES_OFFICE with the data element we found in Step 4.

6. Add the Sales Office to the Account Search page

Now that we have the field Sales Office in the account search structure can we add it to the View MainSearch as search parameter.
Open the Component Workbench - transaction BSP_WD_CMPWB - and display BP_HEAD_SEARCH. Double click on View MainSearch and go the Configuration tab. Select the configuration of found Role Key in step 1 (or copy configuration if there is none existing yet). Locate in Available Search Criteria the newly added Sales Office, move it to the right side and display it.
Can we search yet?   No!
We added the field to the search structure but that's it. The search logic is missing. At the moment the search would try to find the Sales Office directly in the account header data - and that obviously won't work.

7. Enhance the Business Partner Search with BAdI implementation

Because we added a new field which doesn't belong to the account header data, must we program our own search logic. This is done using BAdI Implementation. In transaction se18 we got to find the needed BAdI and its Enhancement Spot:
Enhancement Spot: CRM_BUPA_IL_SEARCH
BAdI: BADI_CRM_BUPA_IL_SEARCH_EXT
Open the above mentioned Enhancement Spot and go to tab Enh. Spot Element Definitions. Open the search extension BAdI, right click on Implementation and choose Create Implementation. You will get a window showing all existing implementations. Click on New (empty paper icon). Follow the instructions to create the implementation.
Open the newly created Enhancement Implementation. In tab Enh. Implementation Elements expand your BAdI and double click on Implementing Class. If a class has not yet been created, create one.
Now we reached the programming part. Open your Implementing Class and the method IF_EX_CRM_BUPA_IL_SEARCH_EXT~SEARCH_CRITERIA_INITIAL. This method should check if there are relevant search criteria for this implementation. In our case we check if the Sales Office has been filled:
data: ls_bupa_header_search type crmt_bupa_il_header_search.

move-corresponding is_parameters to ls_bupa_header_search. " set search parameters in accout search structure

if ls_bupa_header_search-zzsales_office is not initial. " check if the Sales Office has been filled...
  cv_is_not_initial = abap_true. "...and set the changing parameter to true
endif.

Now open the actual search method IF_EX_CRM_BUPA_IL_SEARCH_EXT~SEARCH_PARTNERS. This method either gets the business partner that were found so far or nothing (parameter CT_PARTNER_KEYS). In the second case, this method has to search for all partners and return the ones that have the searched Sales Office. The code I'm going to post is just an example. There are maybe better ways to do it.
 Data definitions:
data: ls_bupa_header_search  type  crmt_bupa_il_header_search, " account search structure

      ls_partner_key         type  bupa_partner_guid, " strucutre of the parameter CT_PARTNER_KEYS

      ls_partner_crm         type  crmm_but_lnk0141, " table which contains the SET_GUID of the partner (read by PARTNER_GUID)
      ls_partner_sales       type  crmm_but_set0140, " table which contains the SALES_OFFICE of partner (read by SET_GUID)
      lv_guid                type  crmmspl_bp_l0011-set_guid, " local SET_GUID variable

      rt_bu_type             type range of but000-type, " partner category range table for selectiong table BUT000
      rs_bu_type             like line  of rt_bu_type,  " structure for partner category range table

      lv_index               type  sytabix, " index variable to hold sy-tabix
      lv_i                   type  i.  " misc. numeric variable
 
Getting the account search parameters:
move-corresponding is_parameters to ls_bupa_header_search.
Search for partners if none received:
if ct_partner_keys is initial.
  " set partner category filter
  cv_category_filtered = abap_true.
  rs_bu_type-sign   = 'I'.
  rs_bu_type-option = 'EQ'.
  if is_category-organizations = abap_true.
    rs_bu_type-low = '2'.
    append rs_bu_type to rt_bu_type.
  endif.
  if is_category-persons       = abap_true.
    rs_bu_type-low = '1'.
    append rs_bu_type to rt_bu_type.
  endif.
  if is_category-groups        = abap_true.
    rs_bu_type-low = '3'.
    append rs_bu_type to rt_bu_type.
  endif.
  " get partners
  select partner partner_guid from but000
         into corresponding fields of table ct_partner_keys
         where type in rt_bu_type.
endif.
Filter partner that have the searched Sales Office:
loop at ct_partner_keys into ls_partner_key.
  lv_index = sy-tabix.
  clear: ls_partner_crm, ls_partner_sales, lv_guid.
  " try to get set guid...
  select single * from crmm_but_lnk0141 into ls_partner_crm
         where partner_guid = ls_partner_key-partner_guid.
  if sy-subrc = 0.
    " check if partner has wanted sales office...
    if ls_bupa_header_search-zzsales_office cs '*'.
      replace all occurrences of '*'
              in ls_bupa_header_search-zzsales_office
              with '%'.
      select single * from crmm_but_set0140 into ls_partner_sales
             where set_guid     =    ls_partner_crm-set_guid
               and sales_office like ls_bupa_header_search-zzsales_office.
    else.
      select single * from crmm_but_set0140 into ls_partner_sales
             where set_guid     = ls_partner_crm-set_guid
               and sales_office = ls_bupa_header_search-zzsales_office.
    endif.
    if sy-subrc <> 0.
      "...if not, remove partner
      delete ct_partner_keys index lv_index.
    endif.
  else.
    "...if not existing, remove partner (no sales data exist for partner)
    delete ct_partner_keys index lv_index.
  endif.
Raise exception if no partners were found:
if ct_partner_keys is initial.
  raise no_partners_found.
endif.
NOW we can search for the Sales Office. But... only for its object key. Wouldn't a picklist be much better? Of course it would. Let's do it.

8. Create an Enhancement Set (optional)

If we want to add a picklist for our Sales Office we have to enhance the account main search and add the picklist through programming. To do that we need an Enhancement Set. Such a set is created using transaction sm34 with View Cluster BSPWDVC_CMP_EXT.
Secondly the Enhancement Set needs to be assigned to a client. This is done in transaction sm30 with View BSPWDV_EHSET_ASG.

9. Enhance Account Search View (optional)

 Display BP_HEAD_SEARCH in the Component Workbench with your just created Enhancement Set. Right click on View MainSearch and choose Enhance. Create all objects needed.

10. Add a picklist to the Sales Office search parameter (optional)

Go to the implementation class of the enhanced MainSearch View (probably called ZL_BP_HEAD_MAINSEARCH_IMPL). Redefine the method GET_DQUERY_DEFINITIONS. Make sure that you call the original code from class CL_BP_HEAD_MAINSEARCH_IMPL by using the super->get_dquery_definitions call. In addition to the Sales Office we also provide a dropdown list for the Sales Group. This is how I've done it:
Data defintions:
  DATA: lt_ddlb           TYPE bsp_wd_dropdown_table, " drop down table type of method
        ls_ddlb           TYPE bsp_wd_dropdown_line,  " cl_crm_uiu_bp_cust_get=>get_val_for_sales_office
        lt_ddlb_opt       TYPE crmt_thtmlb_search_ddlb_nvp, " drop down table type of field ddlb_options
        ls_ddlb_opt       TYPE crms_thtmlb_search_ddlb_nvp, " of parameter rt_result

        lt_ddlb_group     TYPE bsp_wd_dropdown_table,
        ls_ddlb_group     TYPE bsp_wd_dropdown_line,
        lt_ddlb_opt_group TYPE crmt_thtmlb_search_ddlb_nvp,
        ls_ddlb_opt_group TYPE crms_thtmlb_search_ddlb_nvp,

        lt_operators      type CRMT_THTMLB_SEARCH_OPERATOR,
        lv_operator       type CRM_THTMLB_SEARCH_OPERATOR,

        ls_sales_area     TYPE crmt_bus_sales_area, " sales area structure
        lv_sales_office   TYPE crmt_sales_office,
        lt_partner_crm    TYPE TABLE OF crmmspl_bp_l0011,
        ls_partner_crm    TYPE          crmmspl_bp_l0011.
  FIELD-SYMBOLS: <rs_result> LIKE LINE OF rt_result.
 Calling super method:
  CALL METHOD super->get_dquery_definitions
    RECEIVING
      rt_result = rt_result.
Adding Sales Office and Sales Group:
  " Initial line of Sales Offices
  APPEND INITIAL LINE TO lt_ddlb.
  MOVE-CORRESPONDING ls_ddlb TO ls_ddlb_opt.
  APPEND ls_ddlb_opt TO lt_ddlb_opt.

  " Initial line of Sales Groups
  APPEND INITIAL LINE TO lt_ddlb_group.
  MOVE-CORRESPONDING ls_ddlb_group TO ls_ddlb_opt_group.
  APPEND ls_ddlb_opt_group TO lt_ddlb_opt_group.
  " Get all Sales Organizaions
  SELECT DISTINCT sales_org channel division
         FROM crmmspl_bp_l0011
         INTO CORRESPONDING FIELDS OF TABLE lt_partner_crm.

  LOOP AT lt_partner_crm INTO ls_partner_crm.
    MOVE-CORRESPONDING ls_partner_crm TO ls_sales_area.
    " Get values for SALES_OFFICE provide sales area
    CALL METHOD cl_crm_uiu_bp_cust_get=>get_val_for_sales_office
      EXPORTING
        is_sales_area  = ls_sales_area
      RECEIVING
        rt_value       = lt_ddlb
      EXCEPTIONS
        error_occurred = 1
        OTHERS         = 2.
    IF sy-subrc = 0.
      LOOP AT lt_ddlb INTO ls_ddlb.
        lv_sales_office = ls_ddlb-key.
        " get sales groups
        CALL METHOD cl_crm_uiu_bp_cust_get=>get_val_for_sales_group
          EXPORTING
            is_sales_area   = ls_sales_area
            iv_sales_office = lv_sales_office
          RECEIVING
            rt_value        = lt_ddlb_group
          EXCEPTIONS
            error_occurred  = 1
            OTHERS          = 2.
        IF sy-subrc = 0.
          LOOP AT lt_ddlb_group INTO ls_ddlb_group.
            MOVE-CORRESPONDING ls_ddlb_group TO ls_ddlb_opt_group.
            APPEND ls_ddlb_opt_group TO lt_ddlb_opt_group.
          ENDLOOP.
        ENDIF.
        MOVE-CORRESPONDING ls_ddlb TO ls_ddlb_opt.
        APPEND ls_ddlb_opt TO lt_ddlb_opt.
      ENDLOOP.
    ENDIF.
  ENDLOOP.

  lv_operator = 'EQ'.
  append lv_operator to lt_operators.

  LOOP AT rt_result ASSIGNING <rs_result>.
    CASE <rs_result>-field.
      WHEN 'ZZSALES_OFFICE'.
        SORT lt_ddlb_opt BY value.
        <rs_result>-ddlb_options = lt_ddlb_opt.
        <rs_result>-operators    = lt_operators.
      WHEN 'ZZSALES_GROUP'.
        SORT lt_ddlb_opt_group BY value.
        <rs_result>-ddlb_options = lt_ddlb_opt_group.
        <rs_result>-operators    = lt_operators.
      WHEN OTHERS.
    ENDCASE.
  ENDLOOP.
Note: the method call of get_val_for_sales_office was found when looking at component BP_SALES and its view (as found in step 2). There the attribute STRUCT.SALES_OFFICE has a GET_V method which stands for get value help.
--------------------------------------------------------------------------------------------------------------------------
contact us for all your SAP Consulting , Implementation and Support requirements www.anniesummerconsulting.com

Post a Comment

Powered by Blogger.