Conncetion Order Object / Activity - Product

[former_member558862]

We have some oder objects (activities) in the table CRMD_ORDERADM_H. The activities are always connected to some product. Now I was trying to find out how to determine the product GUID to one specific activity. I already checked if I could just use table CRMD_BRELVONAE which contains the relation between two objects. At some point, this table contains the relation between the activity and the product. However, at an early state there is not yet any entry. That's why I guess there must be some other way. I tried to read the docflow somehow, but I could not succeed:

1. Take GUID of activity from CRMD_ORDERADM_H
2. Set GUID for field OBJKEY in table SRRELROLES (returns some RELID)
3. Search CRMD_BINREL with the RELID -> no result

Are there any other tables which I could check to find the product GUID?

[michael_piesche · Accepted solution]

Just a few pointers regarding CRMD_BRELVONAE and CRMD_BINREL:

• This is not where you would typically find the relationship between One-Order objects and products
• CRMD_BRELVONAE is for different types of relationships, e.g. for document flow links: E-Mail -> Meeting/Appointment -> Lead -> Opportunity -> Contract -> Sales -> Shipping -> Invoice, etc.
  CRMD_ORDERADM_H-GUID would be equal to CRMD_BRELVONAE-OBJGUID_A_SEL
• CRMD_BINREL contains timestamps for relationships, e.g. for those of CRMD_BRELVONAE
  The RELATIONID of CRMD_BRELVONAE would be equal to the one of CRMD_BINREL

The main table for One-Order Objects is CRMD_ORDERADM_H (header) and the main table for items of One-Order Objects is CRMD_ORDERADM_I. The main link table for One-Order Header and Items is the table CRMD_LINK.

When you call Report CRM_ORDER_READ for your One-Order ID or GUID, you should get all the related tables and within those you will find the GUIDs to further objects (e.g. Products, Partners, Document Flow, Documents). The export tables of this report 'simplify' the connection between the different tables of One-Order objects, e.g. CRMD_LINK is not provided. Here are some general guidelines how to read and match the CRM_ORDER_READ export tables:

• If an object is linked through CRMD_LINK with CRMD_ORDERADM_H or CRMD_ORDERADM_I, there will be a REF_GUID in the export tables, linking to the GUID of either the Header or an Item
• If an object is linked directly with the header or an item, there is no REF_GUID attribute and the GUID of the export table will be the same as the header or an item
• If the export table has the suffix _H, it is linked to header data, if it has the suffix _I, it is linked to item data.
• If the export table doesnt have the either suffix (neither _H nor _I), it is linked to header and item data and there should be an attribute KIND or REF_KIND with the value A (header) or B (item)
• To find the corresponding database table for the export tables, in most cases you can exchange the prefix ET_ with CRMD_, but in other instances, it is not that easy, e.g.:
  ET_STATUS = CRM_JEST

Let me know if this helps you, or whether you need more information, because there are a lot more tables....

Here are a couple examples:

" Read Header Data of One-Orders
    SELECT h~guid          h~object_id    h~process_type   h~posting_date
           sh~ship_cond    
           pr~pmnttrms
           sa~req_dlv_date sa~cust_group2 
           pa~partner_no
           c~customfield
      FROM crmd_orderadm_h AS h           " orderadm_h

    INNER JOIN crmd_link AS lpa           " link to partner
       ON lpa~client      = h~client
      AND lpa~guid_hi     = h~guid
      AND lpa~objtype_hi  = '05' " header
      AND lpa~objtype_set = '07' " partner
    INNER JOIN crmd_partner AS pa         " partner
       ON pa~client       = lpa~client
      AND pa~guid         = lpa~guid_set
      AND pa~partner_fct  = '00000001'    "  - AG

    INNER JOIN crmd_link AS lo            " link to orgman
       ON lo~client      = h~client
      AND lo~guid_hi     = h~guid
      AND lo~objtype_hi  = '05' " header
      AND lo~objtype_set = '21' " orgman
    INNER JOIN crmd_orgman AS o           " orgman
       ON o~client       = lo~client
      AND o~guid         = lo~guid_set
      AND o~sales_org    = gv_crm_vkorg
      AND o~dis_channel  = pa_vtweg

    INNER JOIN crmd_customer_h AS c       " customer_h
       ON c~client        = h~client
      AND c~guid          = h~guid

    INNER JOIN crmd_link AS lsh           " link to shipping
       ON lsh~client      = h~client
      AND lsh~guid_hi     = h~guid
      AND lsh~objtype_hi  = '05' " header
      AND lsh~objtype_set = '12' " shipping
    INNER JOIN crmd_shipping AS sh        " shipping
       ON sh~client       = lsh~client
      AND sh~guid         = lsh~guid_set

    INNER JOIN crmd_link AS lpr           " link to pricing
       ON lpr~client      = h~client
      AND lpr~guid_hi     = h~guid
      AND lpr~objtype_hi  = '05' " header
      AND lpr~objtype_set = '09' " pricing
    INNER JOIN crmd_pricing AS pr         " pricing
       ON pr~client       = lpr~client
      AND pr~guid         = lpr~guid_set

    INNER JOIN crmd_link AS lsa           " link to sales
       ON lsa~client      = h~client
      AND lsa~guid_hi     = h~guid
      AND lsa~objtype_hi  = '05' " header
      AND lsa~objtype_set = '11' " sales
    INNER JOIN crmd_sales AS sa           " sales
       ON sa~client       = lsa~client
      AND sa~guid         = lsa~guid_set

" Read item data of One-Orders
SELECT i~guid i~header i~itm_type i~description
       c~customfield       
       z~zz0010 " product settype example
       p~product_id 
       s~quantity
      FROM crmd_orderadm_i AS i

      LEFT JOIN crmd_customer_i AS c
        ON c~guid = i~guid

      LEFT JOIN zprodsettype AS z
         ON z~product_guid = i~product

      INNER JOIN comm_product AS p
         ON p~product_guid = i~product

      INNER JOIN crmd_schedlin AS s
        ON s~item_guid = i~guid
        AND s~event_type = 'ORDER'.