Help required in retrieving category1 & category2 values for CRM

[stephenl1]

We are wishing to get the values for Category 1 and Category 2 in relation to crmd_orderadm_h. Based on various other blogs, we have identified that this is achieved in part by navigating to table crmd_srv_subject. In this table we have fields asp_id and cat_id, but both these are blank, meaning we cannot simply use these to navigate through the categorization levels (which we already have a solution for). Instead we now need to look at fields katalogart, codegruppe, and code from crmd_srv_subject. Field crmd_srv_subject code contains the category code for category 2, and we need to navigate up the shema to get category 1.

An excellent solution was shown here Link, however this does not work for us due to our schema having repeating codes at each level 2.

In CRM Web UI we have a Schema defined as follows

Schema: XYZ
>> Level 1: A1
>>>> Level 2: INSP
>>>> Level 2: COMP

>> Level 1: A2
>>>> Level 2: INSP
>>>> Level 2: COMP

>> Level 1: A3
>>>> Level 2: INSP
>>>> Level 2: COMP

Within crmd_srv_subject we have the code INSP associated with our crmd_orderadm_h record, and we want to find the level 1 category associated with this. Using the solution at the above link, we end up finding A1, A2 and A3, as all are associated with the level 2 category code "INSP". This is why the above linked to solution fails to work for us.

This said the CRM WEB UI screen successfully retreives the correct Level 1 category for the level 2 category INSP, so we know the relationship must be represented somewhere.

So far our solution SQL is as follows. Any suggestion on how to overcome the problem and find the correct level 1 category in this situation would be most welcome.

select admh.process_type
, prct.p_description
, admh.object_id
, subj.asp_id
, subj.cat_id
, subj.katalogart
, subj.codegruppe
, subj.code
, subj.timestamp
, qpct.kurztext
, ctok.obj_extkey
, ctok.obj_guid
, ctln.cat_guid
, ctca.cat_id
, ctas.asp_guid
, ctas.asp_id
, ctas.val_from
, ctas.val_to
, ctas.asp_state

from crmd_orderadm_h as admh 

inner join crmc_proc_type_t as prct
	on prct.client = admh.client
	and prct.process_type = admh.process_type
	and prct.langu = 'E'

inner join crmd_link as lnk 
    on lnk.client = admh.client   
    and lnk.guid_hi = admh.guid 
    and lnk.objtype_set = '29'
    and lnk.objtype_hi = '05'      

inner join crmd_srv_osset as oset 
    on oset.client = lnk.client
    and oset.guid_set = lnk.guid_set

inner join crmd_srv_subject as subj 
    on subj.client = oset.client
    and subj.guid_ref = oset.guid

left outer join qpct as qpct
	on qpct.mandt = subj.client
	and qpct.katalogart = subj.katalogart
	and qpct.codegruppe = subj.codegruppe
	and qpct.code = subj.code
	and qpct.sprache = 'E'

left outer join crmc_erms_cat_ok as ctok -- External Object Keys
	on ctok.client = subj.client
	and replace(left(ctok.obj_extkey,10),' ','~') 
		= left(concat(concat(subj.katalogart, subj.codegruppe),'~~~~~~~~~~~'),10)
	and substr(ctok.obj_extkey,11,99) = subj.code

/*
	Up to this point we have one row of output for crmd_orderadm_h record
	Unfortunately the following link to crmc_erms_cat_ln returns
	multiple records.  This is because we have repeatedly used the same
	code value in each level 2 position of our schema.
*/

left outer join crmc_erms_cat_ln as ctln -- Category link to External Objects
	on ctln.client = ctok.client
	and ctln.lnk_type = 'IS_CODE'
	and ctln.obj_guid = ctok.obj_guid

left outer join crmc_erms_cat_ca as ctca -- Categories
	on ctca.client = ctln.client
	and ctca.cat_guid = ctln.cat_guid

left outer join crmc_erms_cat_as as ctas -- Aspects for categorization
	on ctas.client = ctca.client
	and ctas.asp_guid = ctca.asp_guid

where admh.object_ID = '3701437276'