I have an issue on enabling the maintenance while importing purchase order through the business adapter

[csganesh]

We already have an custom adapter to pull in the purchase order from the PO DB, we recently have the maintenance start date and end date added the PO DB which we want to import through the  adapter to FNMS. On our existing adapter we have an staging DB to process the PO records, i have added the an SQL case statement to add a maintenance column and give an value of 1 when i found dates on Maintenance  start date on the PO DB.

I can see the maintenance column populated with right values (1 on where it finds the start date) and when i map the column to the maintenance object on the adapter and run it its not enabling the Maintenance on those PO's, even i cast the value to an bit on SQL i see it reflect on the staging DB but when i run it through adapter its not enabling the maintenance record.

 Does any one have the same issues or suggestions to solve it?

Thanks,

Ganesh

[mfranz]

Hi Ganesh,

I don't have a business adapter at hand for testing, but have you tried passing a string value 'true' for existing maintenance?

Best regards,

Markward

[anne_watson · Accepted solution]

We have adapters built of this nature.  You will need to add a value for Include Support, Maintenance or Service Agreement.  You can create another field in your SQL query to case a value of Y (any value will do really) for when you have effective date populated, as such  

     Case When (t1.[MaintenanceDateStart] is not null and t1.[MaintenanceDateStart] <> '') Then '1'
     Else NULL
     END "Maintenance Flag"

Do not put a value when there is no maintenance.  Any value will trigger that field to be enabled. 

That should resolve the issue for you.  Best of luck.

[csganesh]

Hi @anne_watson ,

Thanks for your suggestion, i initially tried with enabling the maintenance and assigning the start dates and end dates as well on the same SQL statement as below.

case when (po.[Maintenance_start_date]) is not null then 1 end as Maintenance,

Maintenance_start_date as Maint_startdate,

Maintenance_end_date as Maint_enddate,

The above sql statements are part of the sql select statements from an staging DB, enabling the maintenance field works when i dont give the last two lines to assign the start and end dates. 

Do i need to create an another adapter to assign the start and end for the PO's Maintenance has been enabled or  is there a way to assign it in the same adapter.

Thanks,

Ganesh

[kclausen]

You should be able to do both in the same business adapter, except that I would change the SELECT on the maintenance flag:

case when (po.[Maintenance_start_date]) is not null then 1 else 0 end as Maintenance,

And when mapping this to the "Includes Maintenance" object, just assign it as a normal field value - not as a SQL field.

Kirk

[anne_watson]

Hi Kirk,

I have to disagree with you on the 0 value.  If you have any value at all, doesn't matter what it is, it takes it's presence as a yes - which is why we chose Null.

[kclausen]

@anne_watson  - See attached example where I am using a value of 1 or 0 to set the "Purchase includes Maintenance and Support" check box in the FNMS User Interface.  This is with FNMS 2019 R2 On-Premises, using the Business Adapter Studio.

Kirk

[anne_watson]

You are on a different version.  We just upgraded to 2018 R2 this year, so i will give that a go, but leaving the value blank works just as well.

Thanks for the info.

[anne_watson]

you will not need to build a second adapter.  Locate the field in the Purchase Details Line called Include Support, Maintenance or Agreement and map it to your new CASEd field.  See attachment.