CRM object status

[former_member772337]

Hello, I would like to ask for help with finding the proper status of an CRM item. I am not an ABAP developer, but BI person, so I need to achieve this via SQL query.

The situation is:

- I have a GUID of a CRM_ORDERADM_I object

- I have a list of statuses from CRM_JEST table ( WHERE inact <> 'X' )

- There are multiple statuses in the JEST table, so I check for the priority of the particular status in CRMC_ISU_RELSTAT table for the corresponding item type and the item type = ' '

- I found multiple statuses with the highest priority. So I checked CRM_JCDS for latest change. Unfortunately even there are multiple rows, because the statuses were changed at the same time.

SQL:

SELECT * FROM (SELECT st.stat, i.guid, rsi.prio AS prio_itm, rsn.prio AS prio_null, nvl(nvl(rsi.prio, rsn.prio), '100') AS prio_final, MIN(nvl(nvl(rsi.prio, rsn.prio), '100')) over(PARTITION BY i.guid) AS min_prio, to_date(jc.udate || ' ' || jc.utime, 'yyyymmdd hh24:mi:ss') status_change_time, ROW_NUMBER() OVER(PARTITION BY i.guid ORDER BY to_date(jc.udate || ' ' || jc.utime, 'yyyymmdd hh24:mi:ss') DESC) rn FROM crmd_orderadm_i i JOIN crm_jest je ON je.objnr = i.guid LEFT OUTER JOIN crmc_isu_relstat rsi ON rsi.itm_type = i.itm_type AND rsi.status = je.stat LEFT OUTER JOIN crmc_isu_relstat rsn ON rsn.itm_type = ' ' AND rsn.status = je.stat JOIN (SELECT p.guid, j.stat, CASE WHEN MIN(nvl(nvl(rsi.prio, rsn.prio), '100')) over(PARTITION BY p.guid) = nvl(nvl(rsi.prio, rsn.prio), '100') THEN j.stat END status FROM crmd_orderadm_i p JOIN crm_jest j ON j.objnr = p.guid LEFT OUTER JOIN crmc_isu_relstat rsi ON rsi.itm_type = p.itm_type AND rsi.status = j.stat LEFT OUTER JOIN crmc_isu_relstat rsn ON rsn.itm_type = ' ' AND rsn.status = j.stat WHERE j.stat LIKE 'I%' AND j.stat <> 'I1030' AND j.inact = ' ') st ON st.guid = i.guid AND st.status IS NOT NULL JOIN crm_jcds jc ON jc.objnr = i.guid AND jc.stat = st.status WHERE 1 = 1 AND i.guid = '001A4A1A08A81ED6B3A3E47290B1D72E' AND je.stat LIKE 'I%' AND je.stat <> 'I1030' AND je.inact = ' ') WHERE rn = 1

The issue is that there are two different statues with the same ( highest ) priority, so I don't know which one to choose. The row_number in the query above helps, but it may not be accurate.

Therefore my question is how to exactly determine the correct status, which is actually shown in CRM GUI.

Thanks a lot

George

[former_member751591]

Thank you for visiting SAP Community to get answers to your questions. Since you're asking a question here for the first time, I recommend that you familiarize yourself with Community Q&A , as it provides tips for preparing questions that draw responses from our members.

For example, you can:

- outline what steps you took to find answers (and why they weren't helpful)

- share screenshots of what you've seen/done

- make sure you've applied the appropriate tags

- use a more descriptive subject line.

The more details you provide, the more likely it is that members will be able to respond. Feel free to also take our Q&A tutorial Q&A tutorial

Should you wish, you can revise your question by selecting Actions, then Edit.

By adding a picture to your Profile you encourage readers to respond.