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]

Previous Month Total Sales Qty in PO By WareHouse wise Fms Query

;WITH CTE_INVQty AS
(
Select
Case when t1.Targettype =  '13' then (T1.Quantity*0)
     when t1.TrgetEntry IS NULL and t1.BaseEntry is null then (T1.Quantity)
     when t1.Targettype = '16' and t1.BaseEntry is null then (T1.Quantity)
End  'Qty' ,
 t0.DocDate , t0.docnum , t0.docentry ,
case when t0.doctype = 'I' then 'Delivery'
     when t0.doctype = 's' then 'Delivery' End 'Doctype'
From ODLN T0
  Inner Join DLN1 T1 on T0.DocEntry = T1.DocEntry
where
T1.ItemCode = $[$38.1.0] and T1.[WhsCode] =$[$38.24.0]
and
month(T0.[DocDate]) = month(getdate())-1
and
year(T0.[DocDate]) = year(getdate())
Union All-- Sales Return
Select -(T1.Quantity)[Qty] , t0.DocDate , t0.docnum , t0.docentry ,
case when t0.doctype = 'I' then 'Return'
     when t0.doctype = 's' then 'Return' End 'Doctype'
From ORDN T0
  Inner Join RDN1 T1 on T0.DocEntry = T1.DocEntry
where
T1.ItemCode = $[$38.1.0] and T1.[WhsCode] =$[$38.24.0]
and
month(T0.[DocDate]) = month(getdate())-1
and
year(T0.[DocDate]) = year(getdate())
union all
Select
Case when basetype = '17' then (T1.Quantity)
     when t1.TrgetEntry IS NULL and t1.BaseEntry is null then (T1.Quantity)
     when basetype = '15' then (T1.Quantity)
End 'Qty' ,
 t0.DocDate , t0.docnum , t0.docentry ,
case when t0.doctype = 'I' then 'AR Invoice'
     when t0.doctype = 's' then 'AR Invoice' End 'Doctype'
From OINV T0
  Inner Join INV1 T1 on T0.DocEntry = T1.DocEntry
where
T1.ItemCode = $[$38.1.0] and T1.[WhsCode] =$[$38.24.0]
and
month(T0.[DocDate]) = month(getdate())-1
and
year(T0.[DocDate]) = year(getdate())
Union All-- AR Credit Memo

Select
(-T1.Quantity)[Qty] ,
 t0.DocDate ,
 t0.docnum ,
 t0.docentry ,
case when t0.doctype = 'I' then 'AR Cr.Memo'
     when t0.doctype = 's' then 'AR Cr.Memo' End 'Doctype'
From ORIN T0 Inner Join RIN1 T1 on T0.DocEntry = T1.DocEntry
where T1.ItemCode = $[$38.1.0] and T1.[WhsCode] =$[$38.24.0]
             and
month(T0.[DocDate]) = month(getdate())-1
             and
year(T0.[DocDate]) = year(getdate())
)
Select Sum(A.QTy) as [Total Qty] ,'' as [Docdate], '' as [DocNum] , '' as [Docentry] from CTE_INVQty A

Previous Month Total Sales Qty in PO Fms Query

;WITH CTE_INVQty AS
(
Select
Case when t1.Targettype =  '13' then (T1.Quantity*0)
     when t1.TrgetEntry IS NULL and t1.BaseEntry is null then (T1.Quantity)
     when t1.Targettype = '16' and t1.BaseEntry is null then (T1.Quantity)
End  'Qty' ,
 t0.DocDate , t0.docnum , t0.docentry ,
case when t0.doctype = 'I' then 'Delivery'
     when t0.doctype = 's' then 'Delivery' End 'Doctype'
From ODLN T0
  Inner Join DLN1 T1 on T0.DocEntry = T1.DocEntry
where
T1.ItemCode = $[$38.1.0]
and
month(T0.[DocDate]) = month(getdate())-1
and
year(T0.[DocDate]) = year(getdate())
Union All-- Sales Return
Select -(T1.Quantity)[Qty] , t0.DocDate , t0.docnum , t0.docentry ,
case when t0.doctype = 'I' then 'Return'
     when t0.doctype = 's' then 'Return' End 'Doctype'
From ORDN T0
  Inner Join RDN1 T1 on T0.DocEntry = T1.DocEntry
where
T1.ItemCode = $[$38.1.0]
and
month(T0.[DocDate]) = month(getdate())-1
and
year(T0.[DocDate]) = year(getdate())
union all
Select
Case when basetype = '17' then (T1.Quantity)
     when t1.TrgetEntry IS NULL and t1.BaseEntry is null then (T1.Quantity)
     when basetype = '15' then (T1.Quantity)
End 'Qty' ,
 t0.DocDate , t0.docnum , t0.docentry ,
case when t0.doctype = 'I' then 'AR Invoice'
     when t0.doctype = 's' then 'AR Invoice' End 'Doctype'
From OINV T0
  Inner Join INV1 T1 on T0.DocEntry = T1.DocEntry
where
T1.ItemCode = $[$38.1.0]
and
month(T0.[DocDate]) = month(getdate())-1
and
year(T0.[DocDate]) = year(getdate())
Union All-- AR Credit Memo

Select
(-T1.Quantity)[Qty] ,
 t0.DocDate ,
 t0.docnum ,
 t0.docentry ,
case when t0.doctype = 'I' then 'AR Cr.Memo'
     when t0.doctype = 's' then 'AR Cr.Memo' End 'Doctype'
From ORIN T0 Inner Join RIN1 T1 on T0.DocEntry = T1.DocEntry
where T1.ItemCode = $[$38.1.0]
             and
month(T0.[DocDate]) = month(getdate())-1
             and
year(T0.[DocDate]) = year(getdate())
)
Select Sum(A.QTy) as [Total Qty] ,'' as [Docdate], '' as [DocNum] , '' as [Docentry] from CTE_INVQty A

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]
ORDER BY T1.DocEntry Desc

Amount in Words Fms Query

SELECT dbo.inwords($[$29.0.Number],$[$63.0.0])

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')