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,