PAN No Mandatory in AR Invoice

  If @object_type = '13' and @transaction_type in ( 'A','U')

BEGINIf exists (Select P.DocEntry From INV12 P INNER JOIN OINV R on P.DocEntry =R.DocEntry

inner join INV1 Q on P.DocEntry = Q.DocEntry

Where P.DocEntry=@list_of_cols_val_tab_del

and isnull(P.Taxid0,'') = '' and R.DocTotal >= '200000'

and Q.LineStatus = 'O'

)

Select @error = 1310001,

@error_message = 'AR Invoice Doctotal Greater than 2 Lakhs. Please set PAN No. for Customer.'

end

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

 

Hour/Minutes Calculation Between Two Specific Date/Time & Converting Integer into varchar for Time Format

Select (DATEDIFF(MINUTE, OCLG1.StartDtTm , OCLG1.EndDtTm)) as 'Minutes' ,

((DATEDIFF(MINUTE, OCLG1.StartDtTm , OCLG1.EndDtTm))/(0.6*100)) as 'Hrs' ,

CASE when Durtype = 'M' then ((DATEDIFF(MINUTE, OCLG1.StartDtTm , OCLG1.EndDtTm))/(0.1*1000))

when Durtype = 'H' then ((DATEDIFF(MINUTE, OCLG1.StartDtTm , OCLG1.EndDtTm))/(0.6*100))

when Durtype = 'D' then ((DATEDIFF(MINUTE, OCLG1.StartDtTm , OCLG1.EndDtTm))/(0.6*100))

End 'TotalHrs.Mins',

OCLG1.DurType , OCLG1.ClgCode, OCLG1.parentId , OCLG1.CardCode , OCLG1.Recontact as 'BeginDate' , OCLG1.BeginTime,

CONVERT(datetime, CONVERT(varchar(10), OCLG1.Recontact, 120) + ' '

+ SUBSTRING(REPLICATE('0', 4 - LEN(OCLG1.BeginTime)) + CONVERT(varchar, OCLG1.BeginTime, 10), 1, 2) + ':'

+ SUBSTRING(REPLICATE('0', 4 - LEN(OCLG1.BeginTime)) + CONVERT(varchar, OCLG1.BeginTime, 10), 3, 4) + ':' + '00.000') as 'StartDtTm',

OCLG1.endDate , OCLG1.ENDTime ,

CONVERT(datetime, CONVERT(varchar(10), OCLG1.endDate, 120) + ' '

+ SUBSTRING(REPLICATE('0', 4 - LEN(OCLG1.ENDTime)) + CONVERT(varchar, OCLG1. ENDTime, 10), 1, 2) + ':'

+ SUBSTRING(REPLICATE('0', 4 - LEN(OCLG1.ENDTime)) + CONVERT(varchar, OCLG1. ENDTime, 10), 3, 4) + ':' + '00.000') as 'EndDtTm'

from OCLG1