Skip to Content

VAT REPORT BASED ON CURRENT LOGGED IN USER

Hi Guys,

I am Working for a client and it they are having 9 branches and for each branch there will be divided by different Series and different Tax Codes and they want a TAX Report based on the User Logged in.Each User Will be Assigned a branch and in the tax code an UDF(Location) has been created and for each tax code location has been entered there.

So when User 1 of Branch 1 is running the report then the report should show only the record of that particular Branch 1 and

When User 2 of Branch 2 is running the Report then it should show only branch 2 records .

I Tired using an FMS to record the Current User Logged in but i am unable to include this in the below query.

SELECT T0.U_NAME FROM OUSR T0 WHERE INTERNAL_K = $[USER]

In the Below query , the Report needs to be filtered automatically based on the Current user Logged in(User who is running the report).

There will be different Branches and Each Branch has One O Two Licenses and whenever the User1 Of Branch 1 runs the report then the report should display only the record related to that particular branch within the given date range.

So ,to differentiate the Tax codes for each Branches we have created an UDF(Location) in tax codes and enter the branch name in the UDF and also each branch will have 3 different document series and each series.

Instead of Giving the Location as a Parameter to choose we need to filter the report by the User logged in .

SELECT distinct  M.DocNum AS 'AR Inv. #', M.DocDate as 'Date',
 l.targettype AS 'TARGET TYPE',
t4.series,T4.SeriesName,
case when T4.SeriesName='LOC/HOF' then 'HEAD OFFICE' 
       when T4.SeriesName='LOC/CHN' then 'CHENNAI' 
        when t4.seriesName ='INS/CHN' THEN 'CHENNAI'
        WHEN T4.SERIESNAME ='STF/CHN' THEN 'CHENNAI'
        WHEN T4.SERIESNAME ='INS/HOF' THEN 'HEAD OFFICE'
       end Location,L.TAXCODE,T6.RATE,
case when t.statype ='1' then  'VAT'
       WHEN T.STATYPE ='4' THEN 'CST'
       END TAXTYPE,T.STATYPE,
M.CARDCODE,M.CardName as 'Customer Name',M.NumAtCard as 'Bill No. & Dt.',M.DiscSum,M.RoundDif,
(Select Sum(LineTotal) FROM INV1 L Where L.DocEntry=M.DocEntry) as 'Base Amt.(Rs.)',M.VATSUM,
(Select Sum(LineTotal) FROM INV1 L Where L.DocEntry=M.DocEntry and statype =1  ) as 'VAT  SALES',
(SELECT SUM(TaxSum) FROM INV4 t where t.docentry=m.docentry and statype=1  ) as '  VAT COLLECTED ',

(Select Sum(LineTotal) FROM INV1 L Where L.DocEntry=M.DocEntry and statype =4  ) as 'CST  SALES',
(SELECT SUM(TaxSum) FROM INV4 where statype=4 and  DocEntry=M.DocEntry   ) as ' CST COLLECTED ',
(Select Sum(LineTotal) FROM INV3 Q Where Q.DocEntry=M.DocEntry) as 'FREIGHT',

M.DocTotal as 'Total (Rs.)'
FROM OINV M LEFT OUTER JOIN INV1 L on L.DocEntry=M.DocEntry
LEFT OUTER JOIN INV4 T on T.DocEntry=L.DocEntry 
LEFT OUTER JOIN INV5 J ON M.DocEntry = J.AbsEntry
LEFT OUTER JOIN INV3 Q ON M.DocEntry = Q.DocEntry
INNER JOIN NNM1 T4 ON M.SERIES=T4.SERIES 
LEFT JOIN OSTC T6 ON T6.CODE= L.TAXCODE
LEFT JOIN OUBR T7 ON  T7.NAME = T6.U_LOC
LEFT JOIN OUSR T8 ON T8.BRANCH = T7.CODE
WHERE (M.DocDate >= '[%0]' AND M.DocDate <= '[%1]') 
AND  TARGETTYPE !='14' and taxrate !='0'  A
ND T6.RATE != '0' 
and t6.u_loc ='[%2]'


GROUP BY
M.DocNum,M.DocDate,M.CARDCODE,M.CardName,M.NumAtCard,M.DocEntry,
M.DiscSum,M.WTSum,
M.DocTotal,M.Series,T4.SeriesName,
T4.REMARK,M.RoundDif,M.VATSUM,T.STATYPE,T.TAXRATE,Q.LINETOTAL,t4.series,
l.targettype,t.docentry,L.TAXCODE,T6.RATE

Pls Gimme Some Ideas guys for designing this report .

Thanks And Regards,

P.V

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • author's profile photo Former Member
    Former Member
    Posted on Dec 05, 2011 at 04:15 PM

    HI,

    Try add:

    AND $[USER\] in (SELECT USERID FROM OUSR WHERE BRANCH = T7.CODE)

    to the end of WHERE clause.

    Thanks,

    Gordon

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.