FMS Query in SAP Business one for AR Invoice,Service and Inventory

Following are the FMS Query Example in SAP Business one 



===============================================================
Invoice Text in AR Invoice , AR Creditmemo , Delivery , Return , Sales Order Fms Query
======================================================================================

AR Invoice
SELECT $[INV1.text]
AR Credit Memo
SELECT $[RIN1.text] 
Delivery
SELECT $[DLN1.text]
Return
SELECT $[RDN1.text] 
Sales Order
SELECT $[RDR1.text] 
---------------------------------------------------------
=====================================================================================
WareHouse Stock Fms Query
=====================================================================================
        SELECT T0.[OnHand]
        FROM OITW T0
              WHERE
T0.[ItemCode] = $[$38.1.0] 
                  and  
T0.[WhsCode] = $[$38.24.0]
----------------------------------------------------------------------------
====================================================================================
Price Difference Between Last Purchase Price & Unit Price Fms Query
====================================================================================
SELECT $[POR1.U_Tax_name.numbeR] - $[$38.14.numbeR]
----------------------------------------------------------------------------------
===================================================================================
Last Purchase Price in Purchase Order Fms Query
===================================================================================
SELECT Top 1 price
FROM POR1 T0
JOIN OPOR T1 ON T1.DocENtry=T0.DocEntry
WHERE T0.ItemCode =$[$38.1.0]
-----------------------------------------------------------------------------------
===================================================================================
Amount in Words Fms Query
===================================================================================
SELECT dbo.inwords($[$29.0.Number],$[$63.0.0])
ORDER BY T1.DocEntry Desc
----------------------------------------------------------------------------------
===================================================================================

Fms query in Service Call
===================================================================================

Previous Service Call Id :-
Select MAX(Callid) from OSCL T0 where T0.Customer = (Select $[OSCL.customer]) and T0.ItemCode=ItemCode

Previous Service Call Date :-
select mAX(Createdate) from OSCL T0 where T0.internalSN = (Select $[OSCL.internalSN])

Previous Service Call Technician :-
select Top 1 (a.Technician_Name) from OHEM7 a where a.Serial_No = (Select $[OSCL.internalSN])
order by 
a.callid desc

Previous Service Call Subject :-
Select Top 1 (b.Subject)  from ohem7 a  inner join OSCL b on a.callid = b.callID
where b.internalSN = (Select $[OSCL.internalSN])
order by 
b.callID desc

Previous Service Call Status :-
Select MAX(status) from OSCL T0 where T0.internalSN = (Select $[OSCL.internalSN])

Item Group :-
        if ($[OSCL.Itemgroup]= '201')(SELECT 'Networking')
else if ($[OSCL.Itemgroup]= '102')(SELECT 'Software')

Call Priority :-
        If ($[OSCL.Itemgroup]= '201')(SELECT 'High')
else if ($[OSCL.Itemgroup]= '103')(SELECT 'low')

else if ($[OSCL.Itemgroup]= '112')(SELECT 'Medium')