cancel
Showing results for 
Search instead for 
Did you mean: 

Needed help in FMS

Former Member
0 Kudos

Dear Experts,

I have made an FMS to get Sales Opportunity no in Sales Quotation row level. Actually there is no direct link between Sales Opportunity & Sales Quotation in B1, after the Quotation get saved in the system which is been created from the Opportunity it stores the information of link doc type & doc no in Opportunity table - OPR1.

But i need a Opportunity no before the Quotation is saved for that i have an FMS which has an error of date/time out of range

for the first query:

FMS-1:

select a.OpprId from OOPR a

left join OPR1 b on a.OpprId=b.OpprId

left join OQUT c on b.OpenDate=c.DocDate

where a.CardCode=$[OQUT.CardCode] and b.OpenDate= $[OQUT.DocDate]

Then i created the second Query:

FMS-2:

select a.OpprId from OOPR a

left join OPR1 b on a.OpprId=b.OpprId

left join OQUT c on b.OpenDate=c.DocDate

where a.CardCode=$[OQUT.CardCode] and b.OpenDate= convert(datetime, $[oqut.docdate], 103)

it has started giving an error of Internal error (-1003)

Kindly suggest what are possible change can be done to over come from this issue.

Thanks in Advance...

Regards

Krishna

Accepted Solutions (1)

Accepted Solutions (1)

former_member218051
Active Contributor
0 Kudos

Hi Krishna,

Try $[oqut.docdate.date] instead.

Thanks

malhaar

Former Member
0 Kudos

Hi! Malhaar

Thanks for help and I had done as per the changes you had suggested but i can't come of this issue getting again an error -[Microsoft][SQL Server Native 10.0][SQL Server] Invalid pseudocolumn "$http".

Actually my date format is in this way dd/mm/yy does it makes any difference from your query kindly suggest.....

Regards

Krishna

Edited by: krishnaoctopus on Dec 26, 2011 11:14 PM

former_member218051
Active Contributor
0 Kudos

Hi Krishna,

you do not have to type http.

The syntax is dollar sign,opening square bracket,table name.column name.datatype,closing square bracket

In SAP B1 2007 it was

The syntax is dollar sign,opening square bracket,dollar sign,table name.column name.datatype,closing square bracket

Thanks

malhaar

Former Member
0 Kudos

Hi ! Malhaar

Thanks a lot,for your support which had really helped me in getting the sales opportunity no in Sales Quotation.

Regards,

Krishnakumar

Answers (1)

Answers (1)

Former Member
0 Kudos

Hi Krishna.........

Try this........

select a.OpprId from OOPR a
left join OPR1 b on a.OpprId=b.OpprId
left join OQUT c on b.OpenDate=c.DocDate
where a.CardCode=$[$4.0.0] and b.OpenDate= $[$10.0.0]

Regards,

Rahul

Former Member
0 Kudos

Hi! Rahul,

As per your advice i have saved your query & executed in SAP now am getting an error of "The conversion of nvarchar data type to a datetime data type resulted in an out-of-range value."

Kindly suggest me any other possiblities....

Regards,

Krishna