Auto Cost Center FMS Query

[former_member236650]

Hello Experts!

I'm having trouble with creating a formatted search query in my purchase request.

The scenario is that whenever the requester changes the department (OPRQ.Department), the field in the "Division/Department" (PRQ1.OCRCODE2) would automatically be filled up with either (1) ENNGDEPT, (2) GEN DEPT ot (3) SALES DEPT.

Help guys. I'm currently on our implementation phase in one of our clients. Thank you!

[former_member185682]

Hi Nikka,

A query for your FMS:

SELECT CASE WHEN (SELECT Name FROM OUDP WHERE Code = $[OPRQ.Department]) IN ('SA', 'SALES', 'SSAD') THEN 'SALESDEPT'
			WHEN (SELECT Name FROM OUDP WHERE Code = $[OPRQ.Department]) IN ('ACCT', 'MNGT', 'BUSDEV') THEN 'GEN DEPT'
			WHEN (SELECT Name FROM OUDP WHERE Code = $[OPRQ.Department]) IN ('E', 'ENG') THEN 'ENGGDEPT' END

The config for your FMS

Kind Regards,

Diego Lother

[former_member185682]

Hi Nikka,

In the configuration of your FMS, set the option "Auto Refresh", on the combobox on side of option "Auto Refresh", choose the option 1"When Exiting Altered Column" and below this information choose the column that represent (OPRQ.Department).

See the image:

Hope it helps.

Kind Regards,

Diego Lother

[former_member236650]

Hi Diego,

I'm having problems with my formatted search query.

[former_member185682]

Hi Nikka,

Could you explain more your problem?

Kind Regards,

Diego Lother

[Former Member]

Hi Nikka,

Is there any relation defined somewhere between Department and OCRCode2?

Thanks,

Rahul

[former_member236650]

Hi Diego and Rahul,

Whenever we create a PR, the requester would have to choose from the drop down list which department she comes from.

I would like to create a FMS that would automatically choose a "Division/Department" column in PRQ. When we choose departments SA, SALES or SSAD it should automatically put SALESDEPT, for ACCT, MNGT and BUSDEV = GEN DEPT , E and ENG = ENGGDEPT. I need help with the creation of the the query.

Thank you very much.

[Former Member]

Hi Nikka,

When you move cursor over Department, you can see the internal numbers, like when you select 'SA' and move cursor to this field you can see internal number at the bottom when you enable System Information. You have to provide those numbers with its value in order to build the FMS else if you have your own FMS please post here to check.

Thanks,

Rahul

[former_member236650]

Hi Rahul,

Thanks for the quick response. Below is the data generated from table OUDP.

I haven't started creating the FMS query yet because I'm not quite sure how to begin building it.

Thank.

[Former Member]

Hi Nikka,

Try to use FMS provided by diego below, Just replace values with Internal codes and then let us know the result.

Thanks,

Rahul

[Former Member]

Else try below,

Declare @Dept as nvarchar (10) 
Set @Dept=$[OPRQ.department] 
If @Dept in ('-2','3','4') 
Begin 
Select 'SALESDEPT' 
End 
If @Dept in ('1','6','5') 
Begin 
Select 'GEN DEPT' 
End 
If @Dept in ('7','2') 
Begin 
Select 'ENGGDEPT' 
End

Hope this will work...

Thanks,

Rahul