SAP B1 Guide: 2015

Saturday 17 October 2015

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

Monday 28 September 2015

Sales Tax Report

DECLARE
@StartDate DATETIME,
@EndDate DATETIME,
@Dummy INTEGER
SELECT TOP 1 @Dummy = DocNum
FROM OINV T0

SELECT DISTINCT
T0.DocEntry as 'Docentry',
Case
when T0.DocType = 'I' then 'INVOICE'
when T0.DocType = 'S' then 'INVOICE'
End 'Doctype',

T0.DocNum as 'Docnum',
T0.TaxDate as [Document date],
T0.DocDate as [Posting Date],
T0.CardCode as [Customer Code],
T0.CardName AS [Customer Name] ,
CASE
when t1.TaxCode = 'ICST2B' then 'Form C'
End 'Trans.Category',
Case
When t0.series = '420' then 'Karnataka-BLR'
When t0.series = '423' then 'Tamilnadu-CHN'
When t0.series = '424' then 'Maharasthra-PUN'
When t0.series = '425' then 'Maharasthra-MUM'
When t0.series = '574' then 'Karnataka-BLR'
When t0.series = '575' then 'Tamilnadu-CHN'
When t0.series = '576' then 'Maharasthra-PUN'
When t0.series = '577' then 'Maharasthra-MUM' end 'StateName',

T0.NumAtCard as 'Refno.',
T2.TaxId11 as 'TinNo.' ,
T2.Taxid1 as 'CstNo.',
T2.Taxid3 as 'SerTaxNo.',

(SELECT DISTINCT ISNULL (SUM(INV4.BaseSum),0)
FROM INV4 inner join osta t4 on INV4.stacode = t4.code and INV4.statype = t4.type
WHERE
INV4.StaType in ('5') and t4.U_sertype = 'ITSS' and INV4.StcCode in ( 'S12V5+SM' , 'S12V5M' , 'C5+ST12M')
AND INV4.DocEntry = T0.DocEntry) AS 'ITSS@Ser12Basamt',
(SELECT DISTINCT ISNULL (SUM(INV4.TaxSum),0)
FROM INV4 inner join osta t4 on INV4.stacode = t4.code and INV4.statype = t4.type
WHERE
INV4.StaType in ('6','-10', '5') and t4.U_sertype = 'ITSS' and INV4.StcCode in ( 'S12V5+SM' , 'S12V5M' , 'C5+ST12M')
AND INV4.DocEntry = T0.DocEntry) AS 'ITSS@Ser12Taxamt',
(SELECT DISTINCT ISNULL (SUM(INV4.TaxSum),0)
FROM INV4 inner join osta t4 on INV4.stacode = t4.code and INV4.statype = t4.type
WHERE INV4.StaType = '1' and t4.U_sertype = 'ITSS' and INV4.StcCode in ( 'S12V5+SM' , 'S12V5M' , 'S14V5M' )
AND INV4.DocEntry = T0.DocEntry) AS 'ITSS@Vat5TaxAmt',

(SELECT DISTINCT ISNULL (SUM(INV4.TaxSum),0)
FROM INV4 inner join osta t4 on INV4.stacode = t4.code and INV4.statype = t4.type
WHERE
INV4.StaType in ('6','-10', '5') and INV4.StcCode = 'S14BASM'
AND INV4.DocEntry = T0.DocEntry) AS 'BAS@Ser14Taxamt',

(SELECT DISTINCT ISNULL (SUM(INV4.BaseSum),0)
FROM INV4 inner join osta t4 on INV4.stacode = t4.code and INV4.statype = t4.type
WHERE
INV4.StaType in ('5') and INV4.StcCode = 'S12BASM'
AND INV4.DocEntry = T0.DocEntry) AS 'BAS@Ser12Basamt',
(SELECT DISTINCT ISNULL (SUM(INV4.TaxSum),0)
FROM INV4 inner join osta t4 on INV4.stacode = t4.code and INV4.statype = t4.type
WHERE
INV4.StaType in ('6','-10', '5') and INV4.StcCode = 'S12BASM'
AND INV4.DocEntry = T0.DocEntry) AS 'BAS@Ser12Taxamt',

(SELECT DISTINCT ISNULL (SUM(INV4.BaseSum),0)
FROM INV4 inner join osta t4 on INV4.stacode = t4.code and INV4.statype = t4.type
WHERE
INV4.StaType in ('5', '4' ) and INV4.StcCode IN ('STEXEMPM' , 'ST14EXMM' )
AND INV4.DocEntry = T0.DocEntry) AS 'SEZ',

(SELECT DISTINCT ISNULL (SUM(INV4.BaseSum),0)
FROM INV4 inner join osta t4 on INV4.stacode = t4.code and INV4.statype = t4.type
WHERE
INV4.StaType in ('4')
and INV4.StcCode = 'CST2MUM'
AND INV4.DocEntry = T0.DocEntry) AS 'CST2Basamt',
(SELECT DISTINCT ISNULL (SUM(INV4.TaxSum),0)
FROM INV4 inner join osta t4 on INV4.stacode = t4.code and INV4.statype = t4.type
WHERE
INV4.StaType in ('4')
and INV4.StcCode = 'CST2MUM'
AND INV4.DocEntry = T0.DocEntry) AS 'CST2Taxamt',

(SELECT DISTINCT ISNULL (SUM(INV4.BaseSum),0)
FROM INV4 inner join osta t4 on INV4.stacode = t4.code and INV4.statype = t4.type
WHERE
INV4.StaType in ('1') and INV4.StcCode IN ('VAT5MUM' )
AND INV4.DocEntry = T0.DocEntry) AS 'Vat5BasAmt',

(SELECT DISTINCT ISNULL (SUM(INV4.TaxSum),0)
FROM INV4 inner join osta t4 on INV4.stacode = t4.code and INV4.statype = t4.type
WHERE
INV4.StaType in ('1') and INV4.StcCode IN ('VAT5MUM', 'V75S100M' , 'V75SM' )
AND INV4.DocEntry = T0.DocEntry) AS 'Vat5TaxAmt',

(SELECT DISTINCT ISNULL (SUM(INV4.TaxSum),0)
FROM INV4 inner join osta t4 on INV4.stacode = t4.code and INV4.statype = t4.type
WHERE
INV4.StaType in ('4')
and INV4.StcCode in ( 'CST5MUM' , 'C5+ST12M' , 'C5+ST14M' )
AND INV4.DocEntry = T0.DocEntry) AS 'CST5Taxamt',

(SELECT DISTINCT ISNULL (SUM(INV4.TaxSum),0)
FROM INV4 inner join osta t4 on INV4.stacode = t4.code and INV4.statype = t4.type
WHERE
INV4.StaType in ('4')
and INV4.StcCode = 'CST12.5M'
AND INV4.DocEntry = T0.DocEntry) AS 'CST12.5Taxamt',

(SELECT DISTINCT ISNULL (SUM(INV4.BaseSum),0)
FROM INV4 inner join osta t4 on INV4.stacode = t4.code and INV4.statype = t4.type
WHERE INV4.StcCode = 'VAT12.5M' and INV4.RelateType in (1,3)
AND INV4.DocEntry = T0.DocEntry) AS 'Vat12.5BasAmt',

(SELECT DISTINCT ISNULL (SUM(INV4.TaxSum),0)
FROM INV4 inner join osta t4 on INV4.stacode = t4.code and INV4.statype = t4.type
WHERE INV4.StaType = '1' and INV4.StcCode = 'VAT12.5M'
AND INV4.DocEntry = T0.DocEntry) AS 'Vat12.5TaxAmt',

(SELECT DISTINCT ISNULL (SUM(TotalSumSy),0)
FROM INV1
WHERE INV1.TAXCODE in ('NOTAXMUM') and INV1.DocEntry = t0.DocEntry ) as 'No-TaxBasAmt',
(case when t0.taxonexp = 0 then t0.TotalExpns end) 'FreightAmt',
t0.RoundDifSy as 'Roundoff',
(t0.DocTotal)+(t0.WTSum) as 'Doctotal',
-t0.WTSum as WTax ,
t0.DocTotal as 'AfterWHDoctotal',
Case when t0.series = 425 and t3.U_state not in ('MUM') then 'Wrong Taxcode Selected'
when t0.series = 577 and t3.U_state not in ('MUM') then 'Wrong Taxcode Selected'
when t0.series not in ('425' , '577' ) then 'Wrong Docseries Selected' End 'Remarks',
t3.U_State as 'State'
FROM
OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN INV12 T2 ON T2.DocEntry = T0.DocEntry
Left Outer Join [@MIPLVATIP]as T3 On t1.TaxCode = T3.U_TaxCode
where
T0.DocDate >= [%3]
AND T0.DocDate <= [%4] AND
t3.U_State = 'MUM'

union all

SELECT DISTINCT
T0.DocEntry as 'Docentry',
Case
when T0.DocType = 'I' then 'CREDITMEMO'
when T0.DocType = 'S' then ' CREDITMEMO '
End 'Doctype',

T0.DocNum as 'Docnum',
T0.TaxDate as [Document date],
T0.DocDate as [Posting Date],
T0.CardCode as [Customer Code],
T0.CardName AS [Customer Name] ,
CASE
when t1.TaxCode = 'ICST2B' then 'Form C'
End 'Trans.Category',
Case
When t0.series = '426' then 'Karnataka-BLR'
When t0.series = '427' then 'Tamilnadu-CHN'
When t0.series = '428' then 'Maharasthra-PUN'
When t0.series = '429' then 'Maharasthra-MUM'
When t0.series = '559' then 'Karnataka-BLR'
When t0.series = '560' then 'Tamilnadu-CHN'
When t0.series = '561' then 'Maharasthra-PUN'
When t0.series = '562' then 'Maharasthra-MUM'
end 'StateName',
T0.NumAtCard as 'Refno.',
T2.TaxId11 as 'TinNo.' ,
T2.Taxid1 as 'CstNo.',
T2.Taxid3 as 'SerTaxNo.',

(SELECT DISTINCT -ISNULL (SUM(RIN4.BaseSum),0)
FROM RIN4 inner join osta t4 on RIN4.stacode = t4.code and RIN4.statype = t4.type
WHERE
RIN4.StaType in ('5') and t4.U_sertype = 'ITSS' and RIN4.StcCode in ( 'S12V5+SM' , 'S12V5M' , 'C5+ST12M' )
AND RIN4.DocEntry = T0.DocEntry) AS 'ITSS@Ser12Basamt',
(SELECT DISTINCT -ISNULL (SUM(RIN4.TaxSum),0)
FROM RIN4 inner join osta t4 on RIN4.stacode = t4.code and RIN4.statype = t4.type
WHERE
RIN4.StaType in ('6','-10', '5') and t4.U_sertype = 'ITSS' and RIN4.StcCode in ( 'S12V5+SM' , 'S12V5M' , 'C5+ST12M' )
AND RIN4.DocEntry = T0.DocEntry) AS 'ITSS@Ser12Taxamt',
(SELECT DISTINCT -ISNULL (SUM(RIN4.TaxSum),0)
FROM RIN4 inner join osta t4 on RIN4.stacode = t4.code and RIN4.statype = t4.type
WHERE RIN4.StaType = '1' and t4.U_sertype = 'ITSS' and RIN4.StcCode in ( 'S12V5+SM' , 'S12V5M' , 'S14V5M' )
AND RIN4.DocEntry = T0.DocEntry) AS 'ITSS@Vat5TaxAmt',

(SELECT DISTINCT -ISNULL (SUM(RIN4.BaseSum),0)
FROM RIN4 inner join osta t4 on RIN4.stacode = t4.code and RIN4.statype = t4.type
WHERE
RIN4.StaType in ('5') and RIN4.StcCode = 'S14BASM'
AND RIN4.DocEntry = T0.DocEntry) AS 'BAS@Ser14Basamt',
(SELECT DISTINCT -ISNULL (SUM(RIN4.TaxSum),0)
FROM RIN4 inner join osta t4 on RIN4.stacode = t4.code and RIN4.statype = t4.type
WHERE
RIN4.StaType in ('6','-10', '5') and RIN4.StcCode = 'S14BASM'
AND RIN4.DocEntry = T0.DocEntry) AS 'BAS@Ser14Taxamt',

(SELECT DISTINCT -ISNULL (SUM(RIN4.BaseSum),0)
FROM RIN4 inner join osta t4 on RIN4.stacode = t4.code and RIN4.statype = t4.type
WHERE
RIN4.StaType in ('5') and RIN4.StcCode = 'S12BASM'
AND RIN4.DocEntry = T0.DocEntry) AS 'BAS@Ser12Basamt',
(SELECT DISTINCT -ISNULL (SUM(RIN4.TaxSum),0)
FROM RIN4 inner join osta t4 on RIN4.stacode = t4.code and RIN4.statype = t4.type
WHERE
RIN4.StaType in ('6','-10', '5') and RIN4.StcCode = 'S12BASM'
AND RIN4.DocEntry = T0.DocEntry) AS 'BAS@Ser12Taxamt',

(SELECT DISTINCT -ISNULL (SUM(RIN4.BaseSum),0)
FROM RIN4 inner join osta t4 on RIN4.stacode = t4.code and RIN4.statype = t4.type
WHERE
RIN4.StaType in ('5', '4' ) and RIN4.StcCode IN ('STEXEMPM' , 'ST14EXMM' )
AND RIN4.DocEntry = T0.DocEntry) AS 'SEZ',

(SELECT DISTINCT -ISNULL (SUM(RIN4.BaseSum),0)
FROM RIN4 inner join osta t4 on RIN4.stacode = t4.code and RIN4.statype = t4.type
WHERE
RIN4.StaType in ('4')
and RIN4.StcCode = 'CST2MUM'
AND RIN4.DocEntry = T0.DocEntry) AS 'CST2Basamt',
(SELECT DISTINCT -ISNULL (SUM(RIN4.TaxSum),0)
FROM RIN4 inner join osta t4 on RIN4.stacode = t4.code and RIN4.statype = t4.type
WHERE
RIN4.StaType in ('4')
and RIN4.StcCode = 'CST2MUM'
AND RIN4.DocEntry = T0.DocEntry) AS 'CST2Taxamt',

(SELECT DISTINCT -ISNULL (SUM(RIN4.BaseSum),0)
FROM RIN4 inner join osta t4 on RIN4.stacode = t4.code and RIN4.statype = t4.type
WHERE
RIN4.StaType in ('1') and RIN4.StcCode IN ('VAT5MUM' )
AND RIN4.DocEntry = T0.DocEntry) AS 'Vat5BasAmt',

(SELECT DISTINCT -ISNULL (SUM(RIN4.TaxSum),0)
FROM RIN4 inner join osta t4 on RIN4.stacode = t4.code and RIN4.statype = t4.type
WHERE
RIN4.StaType in ('1') and RIN4.StcCode IN ('VAT5MUM', 'V75S100M' , 'V75SM' )
AND RIN4.DocEntry = T0.DocEntry) AS 'Vat5TaxAmt',

(SELECT DISTINCT -ISNULL (SUM(RIN4.TaxSum),0)
FROM RIN4 inner join osta t4 on RIN4.stacode = t4.code and RIN4.statype = t4.type
WHERE
RIN4.StaType in ('4')
and RIN4.StcCode in ( 'CST5MUM' , 'C5+ST12M' , 'C5+ST14M' )
AND RIN4.DocEntry = T0.DocEntry) AS 'CST5Taxamt',

(SELECT DISTINCT -ISNULL (SUM(RIN4.TaxSum),0)
FROM RIN4 inner join osta t4 on RIN4.stacode = t4.code and RIN4.statype = t4.type
WHERE
RIN4.StaType in ('4')
and RIN4.StcCode = 'CST5MUM'
AND RIN4.DocEntry = T0.DocEntry) AS 'CST12.5Taxamt',

(SELECT DISTINCT -ISNULL (SUM(RIN4.BaseSum),0)
FROM RIN4 inner join osta t4 on RIN4.stacode = t4.code and RIN4.statype = t4.type
WHERE RIN4.StcCode = 'VAT12.5M' and RIN4.RelateType in (1,3)
AND RIN4.DocEntry = T0.DocEntry) AS 'Vat12.5BasAmt',

(SELECT DISTINCT -ISNULL (SUM(RIN4.TaxSum),0)
FROM RIN4 inner join osta t4 on RIN4.stacode = t4.code and RIN4.statype = t4.type
WHERE RIN4.StaType = '1' and RIN4.StcCode = 'VAT12.5M'
AND RIN4.DocEntry = T0.DocEntry) AS 'Vat12.5TaxAmt',

(SELECT DISTINCT -ISNULL (SUM(TotalSumSy),0)
FROM RIN1
WHERE RIN1.TAXCODE in ('NOTAXMUM') and RIN1.DocEntry = t0.DocEntry ) as 'No-TaxBasAmt',

-(case when t0.taxonexp = 0 then t0.TotalExpns end) 'FreightAmt',
-t0.RoundDifSy as 'Roundoff',
-((t0.DocTotal)+(t0.WTSum)) as 'Doctotal',
t0.WTSum as WTax ,
-t0.DocTotal as 'AfterWHDoctotal',
Case when t0.series = 429 and t3.U_state not in ('MUM') then 'Wrong Taxcode Selected'
when t0.series = 562 and t3.U_state not in ('MUM') then 'Wrong Taxcode Selected'
when t0.series not in ('429' , '562' ) then 'Wrong Docseries Selected' End 'Remarks',
t3.U_State as 'State'
FROM
ORIN T0 INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN RIN12 T2 ON T2.DocEntry = T0.DocEntry
Left Outer Join [@MIPLVATIP]as T3 On t1.TaxCode = T3.U_TaxCode
where
T0.DocDate >= [%5]
AND T0.DocDate <= [%6] AND
t3.U_State = 'MUM'

Purchase Tax Report

DECLARE
@StartDate DATETIME,
@EndDate DATETIME,
@Dummy INTEGER
SELECT TOP 1 @Dummy = DocNum
FROM OPCH T0
WHERE T0.DocDate >=[%1]
AND T0.DocDate <= [%2]

SELECT DISTINCT
T0.DocEntry as 'Docentry',
Case
when T0.DocType = 'I' then 'INVOICE'
when T0.DocType = 'S' then 'INVOICE'
End 'Doctype',

T0.DocNum as 'Docnum',
T0.TaxDate as [Document date],
T0.DocDate as [Posting Date],
T0.CardCode as 'Vendor Code',
T0.CardName AS 'Vendor Name' ,
CASE
when t1.TaxCode = 'ICST2B' then 'Form C'
End 'Trans.Category',
Case
When t0.series = '467' then 'Karnataka-BLR'
When t0.series = '468' then 'Tamilnadu-CHN'
When t0.series = '469' then 'Maharasthra-PUN'
When t0.series = '470' then 'Maharasthra-MUM'
When t0.series = '625' then 'Karnataka-BLR'
When t0.series = '626' then 'Tamilnadu-CHN'
When t0.series = '627' then 'Maharasthra-PUN'
When t0.series = '628' then 'Maharasthra-MUM'
end 'StateName',

T0.NumAtCard as 'Refno.',
T2.TaxId11 as 'TinNo.' ,
T2.Taxid1 as 'CstNo.',
T2.Taxid3 as 'SerTaxNo.',

(SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)
FROM PCH4 inner join osta t4 on pch4.stacode = t4.code and pch4.statype = t4.type
WHERE
PCH4.StaType in ('5') and PCH4.StcCode in ('S14V5M' , 'C5+ST14M', 'IC5SV14', 'S14V5+SM')
AND PCH4.DocEntry = T0.DocEntry) AS 'ITSS@Ser14Taxamt',

(SELECT DISTINCT ISNULL (SUM(PCH4.BaseSum),0)
FROM PCH4 inner join osta t4 on pch4.stacode = t4.code and pch4.statype = t4.type
WHERE
PCH4.StaType in ('5') and PCH4.StcCode in ('S12V5+SM' , 'IC5SV12' , 'S12V5M')
AND PCH4.DocEntry = T0.DocEntry) AS 'ITSS@Ser12Basamt',
(SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)
FROM PCH4 inner join osta t4 on pch4.stacode = t4.code and pch4.statype = t4.type
WHERE
PCH4.StaType in ('6','-10', '5') and PCH4.StcCode in ('S12V5+SM' , 'IC5SV12' , 'S12V5M')
AND PCH4.DocEntry = T0.DocEntry) AS 'ITSS@Ser12Taxamt',
(SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)
FROM PCH4 inner join osta t4 on pch4.stacode = t4.code and pch4.statype = t4.type
WHERE PCH4.StaType = '1' and PCH4.StcCode in ('S12V5+SM' , 'S12V5M' , 'S14V5+SM' , 'S14V5M' )
AND PCH4.DocEntry = T0.DocEntry) AS 'ITSS@Vat5TaxAmt',

(SELECT DISTINCT ISNULL (SUM(PCH4.BaseSum),0)
FROM PCH4 inner join osta t4 on pch4.stacode = t4.code and pch4.statype = t4.type
WHERE
PCH4.StaType in ('5') and t4.U_sertype = 'BAS' and PCH4.StcCode = 'S14BASM'
AND PCH4.DocEntry = T0.DocEntry) AS 'BAS@Ser14Basamt',
(SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)
FROM PCH4 inner join osta t4 on pch4.stacode = t4.code and pch4.statype = t4.type
WHERE
PCH4.StaType in ('5') and t4.U_sertype = 'BAS' and PCH4.StcCode = 'S14BASM'
AND PCH4.DocEntry = T0.DocEntry) AS 'BAS@Ser14Taxamt',

(SELECT DISTINCT ISNULL (SUM(PCH4.BaseSum),0)
FROM PCH4 inner join osta t4 on pch4.stacode = t4.code and pch4.statype = t4.type
WHERE
PCH4.StaType in ('5') and t4.U_sertype = 'BAS' and PCH4.StcCode = 'S12BASM'
AND PCH4.DocEntry = T0.DocEntry) AS 'BAS@Ser12Basamt',
(SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)
FROM PCH4 inner join osta t4 on pch4.stacode = t4.code and pch4.statype = t4.type
WHERE
PCH4.StaType in ('6','-10', '5') and t4.U_sertype = 'BAS' and PCH4.StcCode = 'S12BASM'
AND PCH4.DocEntry = T0.DocEntry) AS 'BAS@Ser12Taxamt',

(SELECT DISTINCT ISNULL (SUM(PCH4.BaseSum),0)
FROM PCH4 inner join osta t4 on pch4.stacode = t4.code and pch4.statype = t4.type
WHERE
PCH4.StaType in ('5') and PCH4.StcCode IN ('S14MRSM' , 'V75SM')
AND PCH4.DocEntry = T0.DocEntry) AS 'MRS@Ser14BasAmt',

(SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)
FROM PCH4 inner join osta t4 on pch4.stacode = t4.code and pch4.statype = t4.type
WHERE
PCH4.StaType in ('5') and PCH4.StcCode IN ('S14MRSM' , 'V75SM')
AND PCH4.DocEntry = T0.DocEntry) AS 'MRS@Ser14TaxAmt',

(SELECT DISTINCT ISNULL (SUM(PCH4.BaseSum),0)
FROM PCH4 inner join osta t4 on pch4.stacode = t4.code and pch4.statype = t4.type
WHERE
PCH4.StaType in ('5') and PCH4.StcCode IN ('S12MRSM', 'V75S100M')
AND PCH4.DocEntry = T0.DocEntry) AS 'MRS@Ser12BasAmt',

(SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)
FROM PCH4 inner join osta t4 on pch4.stacode = t4.code and pch4.statype = t4.type
WHERE
PCH4.StaType in ('6','-10', '5') and PCH4.StcCode IN ('S12MRSM','V75S100M')
AND PCH4.DocEntry = T0.DocEntry) AS 'MRS@Ser12TaxAmt',

(SELECT DISTINCT ISNULL (SUM(PCH4.BaseSum),0)
FROM PCH4 inner join osta t4 on pch4.stacode = t4.code and pch4.statype = t4.type
WHERE
PCH4.RelateType in (1,3)
and PCH4.StcCode = 'ICST2M'
AND PCH4.DocEntry = T0.DocEntry) AS 'ICST2Basamt',

(SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)
FROM PCH4 inner join osta t4 on pch4.stacode = t4.code and pch4.statype = t4.type
WHERE
PCH4.StaType in ('4')
and PCH4.StcCode = 'ICST2M'
AND PCH4.DocEntry = T0.DocEntry) AS 'ICST2Taxamt',

(SELECT DISTINCT ISNULL (SUM(PCH4.BaseSum),0)
FROM PCH4 inner join osta t4 on pch4.stacode = t4.code and pch4.statype = t4.type
WHERE PCH4.StcCode = 'VAT5MUM' and pch4.RelateType in (1,3)
AND PCH4.DocEntry = T0.DocEntry) AS 'Vat5BasAmt',

(SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)
FROM PCH4 inner join osta t4 on pch4.stacode = t4.code and pch4.statype = t4.type
WHERE pch4.RelateType in (1,3) and PCH4.StcCode in ( 'VAT5MUM' , 'V75S100M' , 'V75SM' )
AND PCH4.DocEntry = T0.DocEntry) AS 'Vat5TaxAmt',

(SELECT DISTINCT ISNULL (SUM(PCH4.BaseSum),0)
FROM PCH4 inner join osta t4 on pch4.stacode = t4.code and pch4.statype = t4.type
WHERE
PCH4.RelateType in (1,3)
and PCH4.StcCode = 'ICST5M'
AND PCH4.DocEntry = T0.DocEntry) AS 'ICST5Basamt',

(SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)
FROM PCH4 inner join osta t4 on pch4.stacode = t4.code and pch4.statype = t4.type
WHERE
PCH4.StaType in ('4')
and PCH4.StcCode = 'ICST5M'
AND PCH4.DocEntry = T0.DocEntry) AS 'ICST5Taxamt',

(SELECT DISTINCT ISNULL (SUM(TotalSumSy),0)
FROM PCH1
WHERE PCH1.TAXCODE in ('ICST5.5M') and PCH1.DocEntry = t0.DocEntry ) as 'ICST5.5Basamt',

(SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)
FROM PCH4 inner join osta t4 on pch4.stacode = t4.code and pch4.statype = t4.type
WHERE
PCH4.StaType in ('4')
and PCH4.StcCode in ('IC5SV12' , 'ICST5.5M' , 'IC5SV14' )
AND PCH4.DocEntry = T0.DocEntry) AS 'ICST5.5Taxamt',

(SELECT DISTINCT ISNULL (SUM(PCH4.BaseSum),0)
FROM PCH4 inner join osta t4 on pch4.stacode = t4.code and pch4.statype = t4.type
WHERE PCH4.StcCode = 'VAT12.5M' and pch4.RelateType in (1,3)
AND PCH4.DocEntry = T0.DocEntry) AS 'Vat12.5BasAmt',

(SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)
FROM PCH4 inner join osta t4 on pch4.stacode = t4.code and pch4.statype = t4.type
WHERE PCH4.StaType = '1' and PCH4.StcCode = 'VAT12.5M'
AND PCH4.DocEntry = T0.DocEntry) AS 'Vat12.5TaxAmt',

(SELECT DISTINCT ISNULL (SUM(PCH4.BaseSum),0)
FROM PCH4 inner join osta t4 on pch4.stacode = t4.code and pch4.statype = t4.type
WHERE
PCH4.RelateType in (1,3)
and PCH4.StcCode = 'ICS14.5M'
AND PCH4.DocEntry = T0.DocEntry) AS 'ICST14.5Basamt',

(SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)
FROM PCH4 inner join osta t4 on pch4.stacode = t4.code and pch4.statype = t4.type
WHERE
PCH4.StaType in ('4')
and PCH4.StcCode = 'ICS14.5M'
AND PCH4.DocEntry = T0.DocEntry) AS 'ICST14.5Taxamt',

(SELECT DISTINCT ISNULL (SUM(TotalSumSy),0)
FROM PCH1
WHERE PCH1.TAXCODE in ('NOTAXMUM') and PCH1.DocEntry = t0.DocEntry ) as 'No-TaxBasAmt',

(case when t0.taxonexp = 0 then t0.TotalExpns end) 'FreightAmt',
t0.RoundDifSy as 'Roundoff',
(t0.DocTotal)+(t0.WTSum) as 'Doctotal',
-t0.WTSum as WTax ,
t0.DocTotal as 'AfterWHDoctotal',
Case when t3.U_state not in ('MUM') then 'Wrong Taxcode Selected'
when t0.series not in ('470', '628') then 'Wrong Docseries Selected' End 'Remarks',
t3.U_State as 'State'
FROM
OPCH T0 INNER JOIN PCH1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN PCH12 T2 ON T2.DocEntry = T0.DocEntry
Left Outer Join [@MIPLVATIP]as T3 On t1.TaxCode = T3.U_TaxCode
where
T0.DocDate >= [%3]
AND T0.DocDate <= [%4] AND
t3.U_state = 'MUM'

union all

SELECT DISTINCT
T0.DocEntry as 'Docentry',
Case
when T0.DocType = 'I' then 'CREDITMEMO'
when T0.DocType = 'S' then ' CREDITMEMO '
End 'Doctype',

T0.DocNum as 'Docnum',
T0.TaxDate as [Document date],
T0.DocDate as [Posting Date],
T0.CardCode as 'Vendor Code',
T0.CardName AS 'Vendor Name' ,
CASE
when t1.TaxCode = 'ICST2B' then 'Form C'
End 'Trans.Category',
Case
When t0.series = '478' then 'Karnataka-BLR'
When t0.series = '479' then 'Tamilnadu-CHN'
When t0.series = '480' then 'Maharasthra-PUN'
When t0.series = '481' then 'Maharasthra-MUM'
When t0.series = '629' then 'Karnataka-BLR'
When t0.series = '630' then 'Tamilnadu-CHN'
When t0.series = '631' then 'Maharasthra-PUN'
When t0.series = '632' then 'Maharasthra-MUM'
end 'StateName',T0.NumAtCard as 'Refno.',
T2.TaxId11 as 'TinNo.' ,
T2.Taxid1 as 'CstNo.',
T2.Taxid3 as 'SerTaxNo.',

(SELECT DISTINCT -ISNULL (SUM(RPC4.TaxSum),0)
FROM RPC4 inner join osta t4 on RPC4.stacode = t4.code and RPC4.statype = t4.type
WHERE
RPC4.StaType in ('5') and RPC4.StcCode in ('S14V5M' , 'C5+ST14M', 'IC5SV14', 'S14V5+SM')
AND RPC4.DocEntry = T0.DocEntry) AS 'ITSS@Ser14Taxamt',

(SELECT DISTINCT -ISNULL (SUM(RPC4.BaseSum),0)
FROM RPC4 inner join osta t4 on RPC4.stacode = t4.code and RPC4.statype = t4.type
WHERE
RPC4.StaType in ('5') and RPC4.StcCode in ('S12V5+SM' , 'IC5SV12' , 'S12V5M')
AND RPC4.DocEntry = T0.DocEntry) AS 'ITSS@Ser12Basamt',
(SELECT DISTINCT -ISNULL (SUM(RPC4.TaxSum),0)
FROM RPC4 inner join osta t4 on RPC4.stacode = t4.code and RPC4.statype = t4.type
WHERE
RPC4.StaType in ('6','-10', '5') and RPC4.StcCode in ('S12V5+SM' , 'IC5SV12' , 'S12V5M')
AND RPC4.DocEntry = T0.DocEntry) AS 'ITSS@Ser12Taxamt',
(SELECT DISTINCT -ISNULL (SUM(RPC4.TaxSum),0)
FROM RPC4 inner join osta t4 on RPC4.stacode = t4.code and RPC4.statype = t4.type
WHERE RPC4.StaType = '1' and RPC4.StcCode in ('S12V5+SM' , 'S12V5M' , 'S14V5+SM' , 'S14V5M' )
AND RPC4.DocEntry = T0.DocEntry) AS 'ITSS@Vat5TaxAmt',

(SELECT DISTINCT -ISNULL (SUM(RPC4.BaseSum),0)
FROM RPC4 inner join osta t4 on RPC4.stacode = t4.code and RPC4.statype = t4.type
WHERE
RPC4.StaType in ('5') and t4.U_sertype = 'BAS' and RPC4.StcCode = 'S14BASM'
AND RPC4.DocEntry = T0.DocEntry) AS 'BAS@Ser14Basamt',
(SELECT DISTINCT -ISNULL (SUM(RPC4.TaxSum),0)
FROM RPC4 inner join osta t4 on RPC4.stacode = t4.code and RPC4.statype = t4.type
WHERE
RPC4.StaType in ('5') and t4.U_sertype = 'BAS' and RPC4.StcCode = 'S14BASM'
AND RPC4.DocEntry = T0.DocEntry) AS 'BAS@Ser14Taxamt',

(SELECT DISTINCT -ISNULL (SUM(RPC4.BaseSum),0)
FROM RPC4 inner join osta t4 on RPC4.stacode = t4.code and RPC4.statype = t4.type
WHERE
RPC4.StaType in ('5') and t4.U_sertype = 'BAS' and RPC4.StcCode = 'S12BASM'
AND RPC4.DocEntry = T0.DocEntry) AS 'BAS@Ser12Basamt',
(SELECT DISTINCT -ISNULL (SUM(RPC4.TaxSum),0)
FROM RPC4 inner join osta t4 on RPC4.stacode = t4.code and RPC4.statype = t4.type
WHERE
RPC4.StaType in ('6','-10', '5') and t4.U_sertype = 'BAS' and RPC4.StcCode = 'S12BASM'
AND RPC4.DocEntry = T0.DocEntry) AS 'BAS@Ser12Taxamt',

(SELECT DISTINCT -ISNULL (SUM(RPC4.TaxSum),0)
FROM RPC4 inner join osta t4 on RPC4.stacode = t4.code and RPC4.statype = t4.type
WHERE
RPC4.StaType in ('5') and RPC4.StcCode IN ('S14MRSM' , 'V75SM')
AND RPC4.DocEntry = T0.DocEntry) AS 'MRS@Ser14TaxAmt',

(SELECT DISTINCT -ISNULL (SUM(RPC4.TaxSum),0)
FROM RPC4 inner join osta t4 on RPC4.stacode = t4.code and RPC4.statype = t4.type
WHERE
RPC4.StaType in ('6','-10', '5') and RPC4.StcCode IN ('S12MRSM','V75S100M')
AND RPC4.DocEntry = T0.DocEntry) AS 'MRS@Ser12TaxAmt',

(SELECT DISTINCT -ISNULL (SUM(RPC4.BaseSum),0)
FROM RPC4 inner join osta t4 on RPC4.stacode = t4.code and RPC4.statype = t4.type
WHERE
RPC4.RelateType in (1,3)
and RPC4.StcCode = 'ICST2M'
AND RPC4.DocEntry = T0.DocEntry) AS 'ICST2Basamt',

(SELECT DISTINCT -ISNULL (SUM(RPC4.TaxSum),0)
FROM RPC4 inner join osta t4 on RPC4.stacode = t4.code and RPC4.statype = t4.type
WHERE
RPC4.StaType in ('4')
and RPC4.StcCode = 'ICST2M'
AND RPC4.DocEntry = T0.DocEntry) AS 'ICST2Taxamt',

(SELECT DISTINCT -ISNULL (SUM(RPC4.BaseSum),0)
FROM RPC4 inner join osta t4 on RPC4.stacode = t4.code and RPC4.statype = t4.type
WHERE RPC4.StcCode = 'VAT5MUM' and RPC4.RelateType in (1,3)
AND RPC4.DocEntry = T0.DocEntry) AS 'Vat5BasAmt',

(SELECT DISTINCT -ISNULL (SUM(RPC4.TaxSum),0)
FROM RPC4 inner join osta t4 on RPC4.stacode = t4.code and RPC4.statype = t4.type
WHERE RPC4.RelateType in (1,3) and RPC4.StcCode in ( 'VAT5MUM' , 'V75S100M' , 'V75SM' )
AND RPC4.DocEntry = T0.DocEntry) AS 'Vat5TaxAmt',

(SELECT DISTINCT -ISNULL (SUM(RPC4.BaseSum),0)
FROM RPC4 inner join osta t4 on RPC4.stacode = t4.code and RPC4.statype = t4.type
WHERE
RPC4.RelateType in (1,3)
and RPC4.StcCode = 'ICST5M'
AND RPC4.DocEntry = T0.DocEntry) AS 'ICST5Basamt',

(SELECT DISTINCT -ISNULL (SUM(RPC4.TaxSum),0)
FROM RPC4 inner join osta t4 on RPC4.stacode = t4.code and RPC4.statype = t4.type
WHERE
RPC4.StaType in ('4')
and RPC4.StcCode = 'ICST5M'
AND RPC4.DocEntry = T0.DocEntry) AS 'ICST5Taxamt',

(SELECT DISTINCT -ISNULL (SUM(TotalSumSy),0)
FROM RPC1
WHERE RPC1.TAXCODE in ('ICST5.5M') and RPC1.DocEntry = t0.DocEntry ) as 'ICST5.5Basamt',

(SELECT DISTINCT -ISNULL (SUM(RPC4.TaxSum),0)
FROM RPC4 inner join osta t4 on RPC4.stacode = t4.code and RPC4.statype = t4.type
WHERE
RPC4.StaType in ('4')
and RPC4.StcCode in ('IC5SV12' , 'ICST5.5M' , 'IC5SV14' )
AND RPC4.DocEntry = T0.DocEntry) AS 'ICST5.5Taxamt',

(SELECT DISTINCT -ISNULL (SUM(RPC4.BaseSum),0)
FROM RPC4 inner join osta t4 on RPC4.stacode = t4.code and RPC4.statype = t4.type
WHERE RPC4.StcCode = 'VAT12.5M' and RPC4.RelateType in (1,3)
AND RPC4.DocEntry = T0.DocEntry) AS 'Vat12.5BasAmt',

(SELECT DISTINCT -ISNULL (SUM(RPC4.TaxSum),0)
FROM RPC4 inner join osta t4 on RPC4.stacode = t4.code and RPC4.statype = t4.type
WHERE RPC4.StaType = '1' and RPC4.StcCode = 'VAT12.5M'
AND RPC4.DocEntry = T0.DocEntry) AS 'Vat12.5TaxAmt',

(SELECT DISTINCT -ISNULL (SUM(RPC4.BaseSum),0)
FROM RPC4 inner join osta t4 on RPC4.stacode = t4.code and RPC4.statype = t4.type
WHERE
RPC4.RelateType in (1,3)
and RPC4.StcCode = 'ICS14.5M'
AND RPC4.DocEntry = T0.DocEntry) AS 'ICST14.5Basamt',

(SELECT DISTINCT -ISNULL (SUM(RPC4.TaxSum),0)
FROM RPC4 inner join osta t4 on RPC4.stacode = t4.code and RPC4.statype = t4.type
WHERE
RPC4.StaType in ('4')
and RPC4.StcCode = 'ICS14.5M'
AND RPC4.DocEntry = T0.DocEntry) AS 'ICST14.5Taxamt',

(SELECT DISTINCT -ISNULL (SUM(TotalSumSy),0)
FROM RPC1
WHERE RPC1.TAXCODE in ('NOTAXMUM') and RPC1.DocEntry = t0.DocEntry ) as 'No-TaxBasAmt',

-(case when t0.taxonexp = 0 then t0.TotalExpns end) 'FreightAmt',
-t0.RoundDifSy as 'Roundoff',
-((t0.DocTotal)+(t0.WTSum)) as 'Doctotal',
t0.WTSum as WTax ,
-t0.DocTotal as 'AfterWHDoctotal',
Case when t3.U_state not in ('MUM') then 'Wrong Taxcode Selected'
when t0.series not in ('481', '632') then 'Wrong Docseries Selected' End 'Remarks',
t3.U_State as 'State'
FROM
ORPC T0 INNER JOIN RPC1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN RPC12 T2 ON T2.DocEntry = T0.DocEntry
Left Outer Join [@MIPLVATIP]as T3 On t1.TaxCode = T3.U_TaxCode
where
T0.DocDate >= [%3]
AND T0.DocDate <= [%4] AND
t3.U_state = 'MUM'

Tuesday 25 August 2015

Invoice Prepared before 30 days only by Item description

SELECT
a.CardCode as 'Code',
a.Cardname as 'Customer Name' ,
a.Docentry ,
a.Docnum as 'Inv.No' ,
a.Docdate as 'Inv.dt' ,
b.U_part_no as 'PartNo.',
b.Dscription,
b.U_inv_f_text as 'Inv.Txt',
b.Price as 'Unit SP',
b.Quantity,
b.linetotal as 'Total SP' ,
Case
when a.DocType = 'I' then 'INVOICE'
when a.DocType = 'S' then 'INVOICE'
End 'Doctype'
from OINV a inner join INV1 b on a.docentry = b.docentry
where b.Dscription like '%Rental Charges%'
and (a.docdate = CONVERT(VARCHAR(10),DATEADD(MONTH,-1,GETDATE()+1),120) )

Saturday 8 August 2015

Invoice With Serial Number , Part No , ItemGroup

Select
P2.docentry , P2.docnum as [InvNo.],
P1.DocDate,
P2.cardcode ,P2.cardname as 'CustomerName' ,
P1.U_part_no as 'PartNo',
P1.Itemcode, P1.Dscription,
P1.price as 'UnitPrice' ,
P1.Quantity ,
P1.linetotal as 'TotalSP' ,
case when P2.Doctype = 'I' then 'Invoice' End 'Doctype' ,
(select T6.FirmName from omrc T6 where T20.FirmCode = T6.FirmCode) 'Mfg',
T5.itmsgrpnam as 'ItemGroup',
Case When P1.BaseType=15 then
'S/N : ' + convert(varchar(2000),(Select distinct isnull(SN2.IntrSerial,'')+', '
From SRI1 SN1
Left Join OSRI SN2 ON SN1.ItemCode=SN2.ItemCode And SN1.SysSerial=SN2.SysSerial
Where SN1.BaseType=15 and SN1.BaseEntry=P1.BaseEntry and SN1.BaseLinNum=P1.BaseLine
FOR XML PATH('')))
else
'S/N : ' + convert(varchar(2000),(Select distinct isnull(SN2.IntrSerial,'')+', '
From SRI1 SN1
Left Join OSRI SN2 ON SN1.ItemCode=SN2.ItemCode And SN1.SysSerial=SN2.SysSerial
Where SN1.BaseType=13 and SN1.BaseEntry=P1.DocEntry and SN1.BaseLinNum=P1.LineNum
FOR XML PATH(''))) end AS SerialNo ,
P1.U_INV_F_TEXT as 'Inv.Text'
from OINV P2
INNER JOIN INV1 P1 on P2.DocEntry = P1.DocEntry
INNER JOIN OITM T20 ON T20.ItemCode = P1.ItemCode
LEFT OUTER JOIN OITB T5 ON T5.ItmsGrpCod = T20.ItmsGrpCod
where P2.DocDate >= '[%1]' and P2.DocDate <= '[%2]'
and P2.DocType = 'I' and
T5.ItmsGrpNam in ( 'Server' , 'Iss ACcessory', 'Enterprise Server', 'Blade Server' , 'Blade Accessory', 'NAS' , 'Storage' , 'Storage Accessory')

union all

Select
P2.docentry , P2.Docnum as [InvNo.],
P1.DocDate,
P2.cardcode ,P2.cardname as 'CustomerName' ,
--Cast(P1.DocDate as Varchar(25)) [Docdate],
P1.U_part_no as 'PartNo',
P1.Itemcode, P1.Dscription ,
-P1.price as 'UnitPrice' ,
-P1.Quantity ,
-P1.linetotal as 'TotalSP' ,
case when P2.Doctype = 'I' then 'Creditmemo' End 'Doctype' ,
(select T6.FirmName from omrc T6 where T20.FirmCode = T6.FirmCode) 'Mfg',
t5.itmsgrpnam as 'ItemGroup',
Case When P1.BaseType=15 then
'S/N : ' + convert(varchar(2000),(Select distinct isnull(SN2.IntrSerial,'')+', '
From SRI1 SN1
Left Join OSRI SN2 ON SN1.ItemCode=SN2.ItemCode And SN1.SysSerial=SN2.SysSerial
Where SN1.BaseType=15 and SN1.BaseEntry=P1.BaseEntry and SN1.BaseLinNum=P1.BaseLine
FOR XML PATH('')))
else
'S/N : ' + convert(varchar(2000),(Select distinct isnull(SN2.IntrSerial,'')+', '
From SRI1 SN1
Left Join OSRI SN2 ON SN1.ItemCode=SN2.ItemCode And SN1.SysSerial=SN2.SysSerial
Where SN1.BaseType=14 and SN1.BaseEntry=P1.DocEntry and SN1.BaseLinNum=P1.LineNum
FOR XML PATH(''))) end AS SerialNo ,
P1.U_INV_F_TEXT as 'Inv.Text'
from ORIN P2
INNER JOIN RIN1 P1 on P2.DocEntry = P1.DocEntry
INNER JOIN OITM T20 ON T20.ItemCode = P1.ItemCode
LEFT OUTER JOIN OITB T5 ON T5.ItmsGrpCod = T20.ItmsGrpCod
where P2.DocDate >= '[%1]' and P2.DocDate <= '[%2]'
and P2.DocType = 'I' and
T5.ItmsGrpNam in ( 'Server' , 'Iss ACcessory', 'Enterprise Server', 'Blade Server' , 'Blade Accessory', 'NAS' , 'Storage' , 'Storage Accessory')
order by P2.docnum

Sunday 2 August 2015

One Year Sales Analysis Report

SELECT T0.[CardCode], T0.[CardName], (SUM(T1.Debit) – sum(T1.Credit)) AS ‘January’, (SUM(T2.Debit) – sum(T1.Credit)) AS ‘February’, (SUM(T3.Debit) – sum(T1.Credit)) AS ‘March ‘, (SUM(T4.Debit) – sum(T1.Credit)) AS ‘April’, (SUM(T5.Debit) – sum(T1.Credit)) AS ‘May ‘, (SUM(T6.Debit) – sum(T1.Credit)) AS ‘June’, (SUM(T7.Debit) – sum(T1.Credit)) AS ‘July ‘, (SUM(T8.Debit) – sum(T1.Credit)) AS ‘August’, (SUM(T9.Debit) – sum(T1.Credit)) AS ‘September ‘, (SUM(T10.Debit) – sum(T1.Credit)) AS ‘October ‘, (SUM(T11.Debit) – sum(T1.Credit)) AS ‘November ‘, (SUM(T12.Debit) – sum(T1.Credit)) AS ‘December ‘ FROM dbo.OCRD T0 LEFT JOIN dbo.JDT1 T1 ON T1.ShortName = T0.CardCode AND Month(T1. Duedate) = 1 AND Year(T1.Duedate) = Year(GetDate()) AND T1.TransType in (’13’,’14’) LEFT JOIN dbo.JDT1 T2 ON T2.ShortName = T0.CardCode AND Month(T2. Duedate) = 2 AND Year(T2.Duedate) = Year(GetDate()) AND T2.TransType in (’13’,’14’) LEFT JOIN dbo.JDT1 T3 ON T3.ShortName = T0.CardCode AND Month(T3. Duedate) = 3 AND Year(T3.Duedate) = Year(GetDate()) AND T3.TransType in (’13’,’14’) LEFT JOIN dbo.JDT1 T4 ON T4.ShortName = T0.CardCode AND Month(T4. Duedate) = 4 AND Year(T4.Duedate) = Year(GetDate()) AND T4.TransType in (’13’,’14’) LEFT JOIN dbo.JDT1 T5 ON T5.ShortName = T0.CardCode AND Month(T5. Duedate) = 5 AND Year(T5.Duedate) = Year(GetDate()) AND T5.TransType in (’13’,’14’) LEFT JOIN dbo.JDT1 T6 ON T6.ShortName = T0.CardCode AND Month(T6. Duedate) = 6 AND Year(T6.Duedate) = Year(GetDate()) AND T6.TransType in (’13’,’14’) LEFT JOIN dbo.JDT1 T7 ON T7.ShortName = T0.CardCode AND Month(T7. Duedate) = 7 AND Year(T7.Duedate) = Year(GetDate()) AND T7.TransType in (’13’,’14’) LEFT JOIN dbo.JDT1 T8 ON T8.ShortName = T0.CardCode AND Month(T8. Duedate) = 8 AND Year(T8.Duedate) = Year(GetDate()) AND T8.TransType in (’13’,’14’) LEFT JOIN dbo.JDT1 T9 ON T9.ShortName = T0.CardCode AND Month(T9. Duedate) = 9 AND Year(T9.Duedate) = Year(GetDate()) AND T9.TransType in (’13’,’14’) LEFT JOIN dbo.JDT1 T10 ON T10.ShortName = T0.CardCode AND Month(T10. Duedate) = 10 AND Year(T10.Duedate) = Year(GetDate()) AND T10.TransType in (’13’,’14’) LEFT JOIN dbo.JDT1 T11 ON T11.ShortName = T0.CardCode AND Month(T11. Duedate) = 11 AND Year(T11.Duedate) = Year(GetDate()) AND T11.TransType in (’13’,’14’) LEFT JOIN dbo.JDT1 T12 ON T12.ShortName = T0.CardCode AND Month(T12. Duedate) = 12 AND Year(T12.Duedate) = Year(GetDate()) AND T12.TransType in (’13’,’14’) WHERE T0.CardType = ‘C’ Group By T0.[CardCode], T0.[CardName]

Deliveries done on Previous Day

SELECT
T0.[DocNum] as ‘Delivery Note No.’,
T0.[DocDate],T0.[DocDueDate] as ‘Delivery Date’,
T0.[CardName] as ‘Customer Name’,
T1.[ItemCode],
T1.[Dscription],
T1.[Quantity]
FROM ODLN T0 INNER JOIN DLN1 T1 ON T0.DocEntry = T1.DocEntry
WHERE T0.[DocDate] =DATEADD(DAY, -1, CONVERT(char,GETDATE(),101))
order by T0.[CardName] , T0.[DocDate]

Stock By Warehouse

SELECT
T1.WhsName,
T0.ItemCode,
SUM(T0.InQty – T0.OutQty) as ‘On Hand Changed’
FROM dbo.OINM T0
INNER JOIN dbo.OWHS T1 ON T0.Warehouse = T1.WhsCode
WHERE T0.DocDate >= ‘[%0]’ and
T1.WhsName like ‘[%1]%’ and
T0.ItemCode like ‘[%2]%’
GROUP BY T1.WhsName, T0.ItemCode
Having SUM(T0.InQty – T0.OutQty) != 0

Active item list Query

SELECT T0.ItemCode

FROM dbo.OITM T0

WHERE T0.ITEMCODE IN (SELECT DISTINCT CODE FROM dbo.ITT1) OR

T0.ITEMCODE IN (SELECT DISTINCT CODE FROM dbo.OITT) OR T0.ITEMCODE

IN (SELECT DISTINCT ITEMCODE FROM dbo.OINM) OR T0.ITEMCODE IN (SELECT

DISTINCT ITEMCODE FROM dbo.ADO1)

SAP B1 Guide