SAP Business one Withholding Tax query

[millicentdark]

Hi Experts,

I have a challenge and i will need your help with a withholding tax query.

I want a withholding tax report query that involves, AP Invoice, Outgoing payment and AP Credit memo.

Urgent reply to this request will be appreciated.

Regards

Justice 

[millicentdark · Accepted solution]

USE [liner_12.08.15]

GO

/****** Object:  StoredProcedure [dbo].[WITHOLDING_new1]    Script Date: 08/14/2015 11:07:34 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER proc [dbo].[WITHOLDING_new1]

--declare

@docdate date,@docdate2 date

as

  --SET @DocDate='2015-07-01'

  --SET @DocDate2='2015-07-31'

begin

  IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[wtx29]') AND type in (N'U'))

select

ovpm.DocNum ,OVPM.CardCode,OVPM.CardName,vpm2.SumApplied as TaxbleAmnt ,

vpm2.WtAppld as WTSum,vpm2.WtAppldFC as WTSumFC,vpm2.U_DocNum as NumAtCard

,vpm2.AppliedFC as TxblAmntFC,ovpm.Comments ,ovpm.DocTotal,ovpm.DocDate

,ovpm.DocTotalFC,vpm2.InvoiceId ,vpm2.vatApplied ,vpm2.vatAppldFC ,pch5.TaxbleAmnt as NewTaxableAmt,pch5.WTAmnt as NewWtx,pch5.Rate,vpm2.DocRate

from VPM2 inner join ovpm on ovpm.DocEntry =vpm2.DocNum

inner join PCH5 on pch5.AbsEntry =vpm2.DocEntry

inner join OCRD on ocrd.CardCode =ovpm.CardCode

where

--ovpm.DocNum = '1863'

  OVPM.DocDate >= @docdate AND OVPM.DocDate <=@docdate2

  AND ovpm.Canceled <>'y' and VPM2.WtAppld <> 0.00

union

  SELECT T0.DocNum,T0.CardCode,T0.CardName,0 AS TaxbleAmnt,0 AS WTSum,0 AS WTSumFC,T0.NumAtCard ,0 AS TxblAmntFC

  ,T0.Comments,T0.DocTotal,t0.DocDate ,T0.DocTotalFC,0 AS InvoiceId ,0 as vatApplied, 0 as vatAppldFC ,0 as NewTaxableAmt,0 as NewWtx,0 as Rate,0 as DocRate

  FROM ORPC T0  INNER JOIN RPC1 T1 ON T0.[DocEntry] = T1.[DocEntry]

  INNER JOIN OCRD T2 ON T2.CardCode =T0.CardCode

  WHERE T0.DocType ='S' AND T1.AcctCode ='410095'

  and t0.CANCELED <> 'Y'and T0 .[DocDate] >=@docdate AND T0 .[DocDate]  <= @docdate2

end

[millicentdark]

i did one query as shown below; because my customer wants it from these tables, AP Invoice, Outgoing payment, AP Credit Memo;

But what is happening is the row values seems not to tally for the WTsum, i want to know if any one can tweak my query so that it brings out the desired results...

GO

/****** Object:  StoredProcedure [dbo].[WITHOLDING_new1]    Script Date: 08/13/2015 07:29:36 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER proc [dbo].[WITHOLDING_new1]

--declare

@docdate date,@docdate2 date

as

  --SET @DocDate='2015-07-01'

  --SET @DocDate2='2015-07-31'

begin

  IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[T5]') AND type in (N'U'))

  DROP TABLE [dbo].[T5]

  SELECT T0.CardCode ,T0.CardName ,T2.LicTradNum ,T2.City ,T0.DocNum

  ,T0.DocDate,0 AS TxblAmntFC,0 AS TaxbleAmnt,0 AS WTSumFC  ,0 AS WTSum,T0.NumAtCard ,T0.Comments,T0.DocTotal,T0.DocTotalFC

  INTO T5

  FROM ORPC T0  INNER JOIN RPC1 T1 ON T0.[DocEntry] = T1.[DocEntry]

  INNER JOIN OCRD T2 ON T2.CardCode =T0.CardCode

  WHERE

  --T0.[DocNum] ='2' AND

  T0.DocType ='S' AND T1.AcctCode ='410095'

  and t0.CANCELED <> 'Y'and T0 .[DocDate] >=@DocDate AND T0 .[DocDate]  <= @DocDate2

  UNION

  Select ovpm.CardCode, OVPM.CardName ,ocrd.LicTradNum,ocrd.City,

  ovpm.DocNum , ovpm.DocDate,vpm6.TxblAmntFC,

  vpm6.TaxbleAmnt ,vpm6.WTSumFC, vpm6.WTSum ,

  opch.NumAtCard,OVPM.Comments ,ovpm.DocTotal ,ovpm.DocTotalFC

--INTO TB1

  from  OCRD join OVPM on

  OCRD.CardCode = ovpm.CardCode

  inner join VPM6 on  VPM6.DocNum  = ovpm.DocEntry

  inner join OPCH on opch.CardCode =OCRD.CardCode

  where ovpm.Canceled <>'y'

  and OVPM .[DocDate] >=@DocDate AND OVPM .[DocDate]  <= @DocDate2

  ORDER BY DocNum

  ;with w_holding_tax as

  (

  select ROW_NUMBER ()over(PARTITION by TaxbleAmnt order by CardName) as "test"

  from T5

  )

----select * from w_holding_tax

  delete from w_holding_tax 

  from T5  

  where test > 1

  select * from T5

END

I believe it is the row_number function that is not making things right but i dont seem to get a way to tweak it well.

Results expected;

The highlighted field is supposed to be Two not just one.

SAP B1 Result;

Urgent Help on how to solve this will be appreciated.

Regards

Justice

[millicentdark]

Hi Kennedy,

do you have the query behind that report?

if you do share with me. My customer needs some modifications so if any of you can share your query on my above request with me

Regards

Justice

[KennedyT21]

There are various fields in the INV5 and PCH5 table which display the withholding tax table.

Example query :

SELECT T0.[WTCode], T0.[Rate], T0.[TaxbleAmnt], T0.[WTAmnt], T0.[Type], T0.[TdsAmnt], T0.[CessAmnt], T0.[HscBAmt], T0.[HscAmnt], T0.[TdsAppl], T0.[CessBAmt] FROM INV5 T0

Check the table and select the field relevant for your requirement from the PCH5 and INV5

Regards

Kennedy

[KennedyT21]

What about the Withholding Tax Report in the 'Reports' module?

Reports > Financials > Accounting > Tax > Withholding Tax Report

Regards

Kennedy

[millicentdark]

Hello Experts,

Please any help on the above request?

Regards

Justice