on 12-05-2014 11:53 AM
Hi All,
I have one query connected with stored procedure. To execute this query need to create one UDF in title level in marketing documents as Work Type.
Now i want to one UDF U_tin from CRD1.
Query in SAP B1:
/* SELECT FROM dbo.OPCH T4 */ DECLARE @FDate AS datetime /* WHERE */ SET @FDate= /* T4.DocDate */ '[%4]'
/* SELECT FROM dbo.OPCH T5 */ DECLARE @TDate AS datetime /* WHERE */ SET @TDate= /* T5.DocDate */ '[%5]'
/* SELECT FROM dbo.OPCH T6 */ DECLARE @wtype AS VARCHAR(20) /* WHERE */ SET @wtype= /* T6.U_WType */ '[%6]'
EXEC PURCHASE_TAXDETAILS_V2_WorkType @FDate,@TDate,@wtype
STORED PROCEDURE:
CREATE PROCEDURE [dbo].[PURCHASE_TAXDETAILS_V2_WorkType](@fromDate datetime,@toDate datetime,@wtype VARCHAR(20))
AS
BEGIN
Declare @fdate nvarchar(12)
Declare @tdate nvarchar(12)
--Declare @wtype VARCHAR(20)
Set @fdate=Convert(NVARCHAR,@fromDate,112)
Set @tdate=Convert(NVARCHAR,@toDate,112)
--SET @wtype='INTER'
--/* SELECT FROM [dbo].[OPCH] T0*/
--Declare @fdate datetime
--Declare @tdate datetime
--/* WHERE */
--Set @fdate = /* T0.DocDate */ CONVERT(DATETIME, '20120101', 112)
--Set @tdate = /* T0.DocDate */ CONVERT(DATETIME, '20130304', 112)
-- EXEC [PURCHASE_TAXDETAILS] '20130401','20130805'
DECLARE @cols NVARCHAR(2000)
SELECT @cols = COALESCE(@cols + ',[' + StaCode + ']', '[' + StaCode + ']')
FROM (
Select Distinct StaCode from OPCH T0 INNER JOIN PCH4 T1 ON T0.DocEntry=T1.DocEntry where T0.DocDate>= @fdate and T0.DocDate<=@tdate And T1.TaxSum > 0 and T0.U_WType =@wtype
UNION
Select Distinct StaCode from ORPC T0 INNER JOIN RPC4 T1 ON T0.DocEntry=T1.DocEntry where T0.DocDate>= @fdate and T0.DocDate<=@tdate And T1.TaxSum > 0 and T0.U_WType =@wtype
) A
DECLARE @cols1 NVARCHAR(2000)
SELECT @cols1 = COALESCE(@cols1 + ',[' + StaCode + '] ''BaseAmount(' + StaCode + ')''', '[' + StaCode + '] ''BaseAmount(' + StaCode + ')''')
FROM (
Select Distinct StaCode from OPCH T0 INNER JOIN PCH4 T1 ON T0.DocEntry=T1.DocEntry where T0.DocDate >= @fdate and T0.DocDate <=@tdate And T1.TaxSum > 0 and T0.U_WType =@wtype
UNION
Select Distinct StaCode from ORPC T0 INNER JOIN RPC4 T1 ON T0.DocEntry=T1.DocEntry where T0.DocDate >= @fdate and T0.DocDate <=@tdate And T1.TaxSum > 0 and T0.U_WType =@wtype
) A
DECLARE @cols2 NVARCHAR(2000)
SELECT @cols2 = COALESCE(@cols2 + ',[BaseAmount(' + StaCode + ')]', '[BaseAmount(' + StaCode + ')]')
FROM (
Select Distinct StaCode from OPCH T0 INNER JOIN PCH4 T1 ON T0.DocEntry=T1.DocEntry where T0.DocDate >= @fdate and T0.DocDate <=@tdate And T1.TaxSum > 0 and T0.U_WType =@wtype
UNION
Select Distinct StaCode from ORPC T0 INNER JOIN RPC4 T1 ON T0.DocEntry=T1.DocEntry where T0.DocDate >= @fdate and T0.DocDate <=@tdate And T1.TaxSum > 0 and T0.U_WType =@wtype
) A
DECLARE @Query1 NVARCHAR(4000)
DECLARE @Query2 NVARCHAR(4000)
DECLARE @Query3 NVARCHAR(4000)
DECLARE @Query4 NVARCHAR(4000)
DECLARE @Query5 NVARCHAR(4000)
DECLARE @Query6 NVARCHAR(4000)
Declare @Query7 NVARCHAR(4000)
DECLARE @Query8 NVARCHAR(4000)
DECLARE @Query9 NVARCHAR(4000)
Set @Query1='Select ''Invoice'' ''Type'',PVT0.DocEntry,PVT0.NumAtCard ''Vendor Ref No'', convert(varchar(10),PVT0.TaxDate,103) TaxDate
,DocNum[Invoice No.],qty [Invoice Qty.], convert(varchar(10),DocDate,103) [Invoice Date],
PONo[Purchase Order No.],PODate[Purchase Order Date],u_esugamno[ESugam No.],
WorkType,case when Docstatus=''O'' Then ''OPEN'' else ''CLOSE'' end[DocStatus]
,CardCode,CardName,CSTNo,TANNo,TINNo,ShipToAddr[Ship To Address],PayToAddr[Pay To Address]
,[Total Before Discount]--,[Discount]
,(CASE WHEN FREIGHT>=0 THEN FREIGHT end) [FREIGHT],(CASE WHEN FREIGHT<0 THEN FREIGHT end) [DISCOUNT ALLOWED],RoundDif [Rounding]
,'+@cols2+','+@cols+'
,AcctName[GL Account Name],Amount1[GL Account Value], DocTotal from
(Select A.DocEntry,A.NumAtCard, convert(varchar(10),A.TaxDate,103) TaxDate,A.DocNum, convert(varchar(10),A.DocDate,103) DocDate,
(Case when (Select Top 1 X.BaseType From PCH1 X Where X.DocEntry=A.DocEntry)=''20''
then (select top 1 OPOR.DocNum from OPOR inner join POR1 on opor.DocEntry=por1.DocEntry inner join PDN1 on pdn1.BaseEntry=opor.docentry and PDN1.DocEntry =(Select Top 1 X.BaseEntry From PCH1 X Where X.DocEntry=A.DocEntry) GROup by OPOR.DocNum) when (Select Top 1 X.BaseType From PCH1 X Where X.DocEntry=A.DocEntry)=''22''
then (select top 1 OPOR.DocNum from OPOR inner join POR1 on opor.DocEntry=por1.DocEntry inner join PCH1 on pch1.BaseEntry=OPOR.DocEntry and PCH1.DocEntry=a.DocEntry) else '''' end) PONo,
(Case when (Select Top 1 X.BaseType From PCH1 X Where X.DocEntry=A.DocEntry)=''20''
then (select top 1 convert(varchar(10),OPOR.Docdate,103) from OPOR inner join POR1 on opor.DocEntry=por1.DocEntry inner join PDN1 on pdn1.BaseEntry=opor.docentry and PDN1.DocEntry =(Select Top 1 X.BaseEntry From PCH1 X Where X.DocEntry=A.DocEntry) GROup by OPOR.Docdate) when (Select Top 1 X.BaseType From PCH1 X Where X.DocEntry=A.DocEntry)=''22''
then (select top 1 convert(varchar(10),OPOR.Docdate,103) from OPOR inner join POR1 on opor.DocEntry=por1.DocEntry inner join PCH1 on pch1.BaseEntry=OPOR.DocEntry and PCH1.DocEntry=a.DocEntry) else '''' end) PODate,
A.u_esugamno,
A.U_wtype[WorkType],A.Docstatus,A.CardCode,A.CardName,C.TaxId1 CSTNo,C.TaxId6 TANNo,C.TaxID11 TINNo,A.DocTotal,B.StaCode,B.TaxSum,A.Address2 ShipToAddr,A.Address PayToAddr
,TotalExpns [Freight],A.DiscSum [Discount],(A.DOCTOTAL-A.TOTALEXPNS-A.VATSUM+A.DISCSUM) [Total Before Discount],A.RoundDif
from OPCH A
INNER JOIN
(Select T1.DocEntry,T1.StaCode,SUM(T1.TaxSum) TaxSum from OPCH T0
INNER JOIN PCH4 T1 ON T0.DocEntry=T1.DocEntry
Where T0.DocType = ''I'' ANd T0.DocDate>='''+ @fdate + ''' and T0.DocDate<='''+ @tdate + ''' and T0.U_WType ='''+@wtype+'''
Group By T1.DocEntry,T1.StaCode) B ON A.DocEntry=B.DocEntry
Left Outer Join CRD7 C ON C.CardCode=A.CardCode and ISNULL(C.Address,'''')=''''
Where A.DocDate >='''+ @fdate + ''' and A.DocDate<='''+ @tdate + ''' and A.U_WType ='''+@wtype+'''
) P
PIVOT
(SUM(TaxSum) FOR StaCode IN ('+@cols+'))
AS PVT0'
Set @Query2='
INNER JOIN
(Select DocEntry,'+@cols1+',AcctName,Amount1,qty from
(Select DISTINCT A.DocEntry,A.NumAtCard, convert(varchar(10),A.TaxDate,103) TaxDate,A.DocNum, convert(varchar(10),A.DocDate,103) DocDate
,A.U_wtype[WorkType],A.Docstatus,A.CardCode,A.CardName,B.StaCode,B.BaseSum,bb.AcctName,bb.amount1,qty from OPCH A
inner join pch1 bbb on bbb.docentry=a.docentry
inner join
(select pch1.docentry, sum(pch1.Quantity)Qty from pch1
group by pch1.docentry)D on D.docentry=A.docentry
INNER JOIN
(Select T1.DocEntry,T1.StaCode,SUM(T1.BaseSum) BaseSum from OPCH T0
INNER JOIN PCH4 T1 ON T0.DocEntry=T1.DocEntry
Where T0.DocType = ''I'' ANd T0.DocDate>='''+ @fdate + ''' and T0.DocDate<='''+ @tdate + ''' and T0.U_WType ='''+@wtype+''' '
Set @Query3='
Group By T1.DocEntry,T1.StaCode) B ON A.DocEntry=B.DocEntry
left join (select aa.GRPOEntry ,aa.AcctName,sum(aa.Amount)Amount1 from (
select distinct ''GRPO''[Type],''''[InvNo],h.DocEntry[GRPOEntry] ,e.TransId,g.Segment_0 ,g.AcctName,f.debit[Amount] from OJDT e
join JDT1 f on e.TransId=f.TransId join OACT g on f.Account=g.AcctCode join OPDN h on h.TransId =e.TransId join PDN1 i on i.DocEntry=h.DocEntry
where g.segment_0 in (''4110101'',''4110102'',''4110103'',''4110104'',''4110105'',''4110106'',''4110107'',''4110108'',''4110109'',''4110110'',''4110112'')
and h.ObjType in (''20'') and i.TargetType=''18''
union all
select distinct ''Invoice''[Type],h.docentry[InvNo],i.baseentry[GRPOEntry],e.TransId ,g.Segment_0 ,g.AcctName,f.debit[Amount] from OJDT e
join JDT1 f on e.TransId=f.TransId join OACT g on f.Account=g.AcctCode join OPCH h on h.TransId =e.TransId join PCH1 i on i.DocEntry=h.DocEntry
where g.segment_0 in (''4110101'',''4110102'',''4110103'',''4110104'',''4110105'',''4110106'',''4110107'',''4110108'',''4110109'',''4110110'',''4110112'')
and h.ObjType in (''18'') and i.BaseType =''20'' and h.DocDate>='''+ @fdate + ''' and h.DocDate<='''+ @tdate + '''
)AA
group by aa.AcctName ,aa.GRPOEntry
) BB on bb.GRPOEntry = bbb.BaseEntry
Where A.DocDate >='''+ @fdate + ''' and A.DocDate<='''+ @tdate + ''' and A.U_WType ='''+@wtype+'''
) P
PIVOT
(SUM(BaseSum) FOR StaCode IN ('+@cols+'))
AS PVT) PVT1 ON PVT0.DocEntry=PVT1.DocEntry
UNION '
-----Corrected on 28th Mar'13
Set @Query7='Select ''Invoice'' ''Type'',PVT0.DocEntry,PVT0.NumAtCard ''Customer Ref No'', convert(varchar(10),PVT0.TaxDate,103) TaxDate
,DocNum,0 [Invoice Qty.], convert(varchar(10),DocDate,103) [Invoice Date],PONo,PODate,u_esugamno[ESugam No.],
WorkType,case when Docstatus=''O'' Then ''OPEN'' else ''CLOSE'' end[DocStatus],CardCode,CardName,
CSTNo,TANNo,TINNo,ShipToAddr,PayToAddr
,[Total Before Discount]--,[Discount]
,(CASE WHEN FREIGHT>=0 THEN FREIGHT end) [FREIGHT],(CASE WHEN FREIGHT<0 THEN FREIGHT end) [DISCOUNT ALLOWED],RoundDif [Rounding]
,'+@cols2+','+@cols+',''''[GL Account Name],0[GL Account Value],DocTotal from
(Select A.DocEntry,A.NumAtCard, convert(varchar(10),A.TaxDate,103) TaxDate,A.DocNum, convert(varchar(10),A.DocDate,103) DocDate,
(Case when (Select Top 1 X.BaseType From PCH1 X Where X.DocEntry=A.DocEntry)=''20''
then (select top 1 OPOR.DocNum from OPOR inner join POR1 on opor.DocEntry=por1.DocEntry inner join PDN1 on pdn1.BaseEntry=opor.docentry and PDN1.DocEntry =(Select Top 1 X.BaseEntry From PCH1 X Where X.DocEntry=A.DocEntry) GROup by OPOR.DocNum) when (Select Top 1 X.BaseType From PCH1 X Where X.DocEntry=A.DocEntry)=''22''
then (select top 1 OPOR.DocNum from OPOR inner join POR1 on opor.DocEntry=por1.DocEntry inner join PCH1 on pch1.BaseEntry=OPOR.DocEntry and PCH1.DocEntry=a.DocEntry) else '''' end) PONo,
(Case when (Select Top 1 X.BaseType From PCH1 X Where X.DocEntry=A.DocEntry)=''20''
then (select top 1 convert(varchar(10),OPOR.Docdate,103) from OPOR inner join POR1 on opor.DocEntry=por1.DocEntry inner join PDN1 on pdn1.BaseEntry=opor.docentry and PDN1.DocEntry =(Select Top 1 X.BaseEntry From PCH1 X Where X.DocEntry=A.DocEntry) GROup by OPOR.Docdate) when (Select Top 1 X.BaseType From PCH1 X Where X.DocEntry=A.DocEntry)=''22''
then (select top 1 convert(varchar(10),OPOR.Docdate,103) from OPOR inner join POR1 on opor.DocEntry=por1.DocEntry inner join PCH1 on pch1.BaseEntry=OPOR.DocEntry and PCH1.DocEntry=a.DocEntry) else '''' end) PODate,
A.u_esugamno,
A.U_wtype[WorkType],A.Docstatus,A.CardCode,A.CardName,C.TaxId1 CSTNo,C.TaxId6 TANNo,C.TaxId11 TINNo, A.DocTotal,B.StaCode,B.TaxSum,A.Address2 ShipToAddr,A.Address PayToAddr
,TotalExpns [Freight],A.DiscSum [Discount],(A.DOCTOTAL-A.TOTALEXPNS-A.VATSUM+A.DISCSUM) [Total Before Discount],A.RoundDif
from OPCH A
INNER JOIN
(Select T1.DocEntry,T1.StaCode,SUM(T1.TaxSum) TaxSum from OPCH T0
INNER JOIN PCH4 T1 ON T0.DocEntry=T1.DocEntry
Where T0.DocType = ''S'' And T0.U_WType=''JW'' ANd T0.DocDate>='''+ @fdate + ''' and T0.DocDate<='''+ @tdate + ''' and T0.U_WType ='''+@wtype+'''
Group By T1.DocEntry,T1.StaCode) B ON A.DocEntry=B.DocEntry
Left Outer Join CRD7 C ON C.CardCode=A.CardCode and ISNULL(C.Address,'''')=''''
Where A.DocDate >='''+ @fdate + ''' and A.DocDate<='''+ @tdate + ''' and A.U_WType ='''+@wtype+'''
) P
PIVOT
(SUM(TaxSum) FOR StaCode IN ('+@cols+'))
AS PVT0'
Set @Query8='
INNER JOIN
(Select DocEntry,'+@cols1+' from
(Select A.DocEntry,A.NumAtCard, convert(varchar(10),A.TaxDate,103) TaxDate,A.DocNum, convert(varchar(10),A.DocDate,103) DocDate,A.U_wtype[WorkType]
,A.Docstatus,A.CardCode,A.CardName,B.StaCode,B.BaseSum from OPCH A
INNER JOIN
(Select T1.DocEntry,T1.StaCode,SUM(T1.BaseSum) BaseSum from OPCH T0
INNER JOIN PCH4 T1 ON T0.DocEntry=T1.DocEntry
Where T0.DocType = ''S'' And T0.U_WType=''JW'' ANd T0.DocDate>='''+ @fdate + ''' and T0.DocDate<='''+ @tdate + ''' and T0.U_WType ='''+@wtype+''' '
Set @Query9='
Group By T1.DocEntry,T1.StaCode) B ON A.DocEntry=B.DocEntry
Where A.DocDate >='''+ @fdate + ''' and A.DocDate<='''+ @tdate + ''' and A.U_WType ='''+@wtype+'''
) P
PIVOT
(SUM(BaseSum) FOR StaCode IN ('+@cols+'))
AS PVT) PVT1 ON PVT0.DocEntry=PVT1.DocEntry
UNION '
-------------
Set @Query4='Select ''CreditNote'' ''Type'',PVT0.DocEntry,PVT0.NumAtCard ''Customer Ref No'', convert(varchar(10),PVT0.TaxDate,103) TaxDate
,DocNum,isnull(qty,0)[Invoice Qty.], convert(varchar(10),DocDate,103) [Invoice Date],
PONo,PoDate,u_esugamno[ESugam No.],
WorkType,case when Docstatus=''O'' Then ''OPEN'' else ''CLOSE'' end[DocStatus],CardCode,CardName,CSTNo,TANNo,
TINNo,ShipTo,PayToAddr
,[Total Before Discount]--,[Discount]
,(CASE WHEN FREIGHT>=0 THEN FREIGHT end) [FREIGHT],(CASE WHEN FREIGHT<0 THEN FREIGHT end) [DISCOUNT ALLOWED],RoundDif [Rounding]
,'+@cols2+','+@cols+',''''[GL Account Name],0[GL Account Value],DocTotal from
(Select A.DocEntry,A.NumatCard, convert(varchar(10),A.TaxDate,103) TaxDate,A.DocNum, convert(varchar(10),A.DocDate,103) DocDate
,(Case when (Select Top 1 X.BaseType From PCH1 X Where X.DocEntry=A.DocEntry)=''20''
then (select top 1 OPOR.DocNum from OPOR inner join POR1 on opor.DocEntry=por1.DocEntry inner join PDN1 on pdn1.BaseEntry=opor.docentry and PDN1.DocEntry =(Select Top 1 X.BaseEntry From PCH1 X Where X.DocEntry=A.DocEntry) GROup by OPOR.DocNum) when (Select Top 1 X.BaseType From PCH1 X Where X.DocEntry=A.DocEntry)=''22''
then (select top 1 OPOR.DocNum from OPOR inner join POR1 on opor.DocEntry=por1.DocEntry inner join PCH1 on pch1.BaseEntry=OPOR.DocEntry and PCH1.DocEntry=a.DocEntry) else '''' end) PONo,
(Case when (Select Top 1 X.BaseType From PCH1 X Where X.DocEntry=A.DocEntry)=''20''
then (select top 1 convert(varchar(10),OPOR.Docdate,103) from OPOR inner join POR1 on opor.DocEntry=por1.DocEntry inner join PDN1 on pdn1.BaseEntry=opor.docentry and PDN1.DocEntry =(Select Top 1 X.BaseEntry From PCH1 X Where X.DocEntry=A.DocEntry) GROup by OPOR.Docdate) when (Select Top 1 X.BaseType From PCH1 X Where X.DocEntry=A.DocEntry)=''22''
then (select top 1 convert(varchar(10),OPOR.Docdate,103) from OPOR inner join POR1 on opor.DocEntry=por1.DocEntry inner join PCH1 on pch1.BaseEntry=OPOR.DocEntry and PCH1.DocEntry=a.DocEntry) else '''' end) PODate,
A.u_esugamno,
A.U_wtype[WorkType],A.Docstatus,A.CardCode,A.CardName,C.TaxId1 CSTNo,C.TaxId6 TANNo,C.TaxID11 TINNo ,A.Address2 ShipTo,A.Address PayToAddr,A.DocTotal DocTotal,B.StaCode,B.TaxSum
,TotalExpns [Freight],A.DiscSum [Discount],(A.DOCTOTAL-A.TOTALEXPNS-A.VATSUM+A.DISCSUM) [Total Before Discount],A.RoundDif,qty
from ORPC A
INNER JOIN
(select rpc1.docentry, sum(rpc1.Quantity)Qty from rpc1
group by rpc1.docentry)D on D.docentry=A.docentry
INNER JOIN
(Select T1.DocEntry,T1.StaCode,-SUM(T1.TaxSum) TaxSum from ORPC T0
INNER JOIN RPC4 T1 ON T0.DocEntry=T1.DocEntry
Where T0.DocType = ''I'' ANd T0.DocDate>='''+ @fdate + ''' and T0.DocDate<='''+ @tdate + ''' and T0.U_WType ='''+@wtype+'''
Group By T1.DocEntry,T1.StaCode) B ON A.DocEntry=B.DocEntry
INNER JOIN CRD7 C ON C.CardCode=A.CardCode and ISNULL(C.Address,'''')=''''
Where A.DocDate >='''+ @fdate + ''' and A.DocDate<='''+ @tdate + ''' and A.U_WType ='''+@wtype+'''
) P
PIVOT
(SUM(TaxSum) FOR StaCode IN ('+@cols+'))
AS PVT0'
Set @Query5='
INNER JOIN
(Select DocEntry,'+@cols1+' from
(Select A.DocEntry,A.NumAtCard, convert(varchar(10),A.TaxDate,103) TaxDate,A.DocNum, convert(varchar(10),A.DocDate,103) DocDate,A.U_wtype[WorkType]
,A.Docstatus,A.CardCode,A.CardName,B.StaCode,B.BaseSum from ORPC A
INNER JOIN
(Select T1.DocEntry,T1.StaCode,-SUM(T1.BaseSum) BaseSum from ORPC T0
INNER JOIN RPC4 T1 ON T0.DocEntry=T1.DocEntry
Where T0.DocType = ''I'' ANd T0.DocDate>='''+ @fdate + ''' and T0.DocDate<='''+ @tdate + ''' and T0.U_WType ='''+@wtype+''' '
Set @Query6='
Group By T1.DocEntry,T1.StaCode) B ON A.DocEntry=B.DocEntry
Where A.DocDate >='''+ @fdate + ''' and A.DocDate<='''+ @tdate + ''' and A.U_WType ='''+@wtype+'''
) P
PIVOT
(SUM(BaseSum) FOR StaCode IN ('+@cols+'))
AS PVT) PVT1 ON PVT0.DocEntry=PVT1.DocEntry
Order By DocNum,DocDate,Type,CardCode'
--
--PRINT(@Query1+@Query2+@Query3)
--PRINT(@Query7+@Query8+@Query9)
--PRINT(+@Query4+@Query5+@Query6)
--print(@Query1)
--print(@Query2)
--print(@Query3)
--print(@Query7)
--print(@Query8)
--print(@Query9)
--print(@Query4)
--print(@Query5)
--print(@Query6)
EXEC(@Query1+@Query2+@Query3+@Query7+@Query8+@Query9+@Query4+@Query5+@Query6)
END
--Exec [PURCHASE_TAXDETAILS_V2.2] '20140101','20140708'
Can anyone help me out this?
Thanks & Regards,
Saikrishna.
Hi All,
Any update???
Regards,
Saikrishna.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
95 | |
11 | |
11 | |
6 | |
6 | |
4 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.