Solved

table with list of installed item by customer site and status equipement

  • 21 October 2023
  • 1 reply
  • 58 views
athobie
Rank
Userlevel 4
Badge +11

HI,

 

I would like to know the table with the data  :

1/ Installed item and customer site (as we could see in Installed item transaction or Customer center > Item Inst)

I thought it was the table Item_Inst but it seems that we don’t have the customer site in this table.

 

2/ Installed item and customer site AND status of the Inst item and the PM_person_ID

Maybe a sql is required for this second use case ? 

 

Can you help me ? 

Thanks and Regards

anthony

 


 

 

icon

Best answer by Phil Seifert 21 October 2023, 21:48

View original

1 reply

Phil Seifert
Rank
Userlevel 7
Badge +21

Hi  Anthony,

That is correct because the company_id for the installed_item is stored in the ITEM table, not the ITEM_INST table which has additional information about the installed item.

You could write an SQL like the following (expand this to your needs)

select it.company_id,
       it.serial_no,
       it.item_status,
       ii.pm_sa_person_id

from item it with(nolock)
inner join item_inst ii with(nolock) on ii.item_id = it.item_id

where it.item_status = '1'
/* 1 = Installed, 3 = Inventory */
and it.serial_no = <serial_no>

In the event the same serial number is used across different products, add a filter for item.bpart_id in the WHERE clause.

 

Hopefully this helps.

Reply


Terms & ConditionsCookie settings