Skip to Content
0
May 05, 2014 at 05:26 AM

crystal report error

22 Views

Dear all,

Any body help me below listed issues in Crystal query.

1)how to pick the tax rate from inv4 table(i want show taxrate like if i select vat4% it should be show 4 or cst 2% it should show 2).

2) how to convert doc total into Amount in words and excise total in Amount in words(bed+ecc+hsc)

SELECT T0.[DocNum], T0.[DocEntry], T0.[DocDate], T0.[CardCode],t0.[U_DANo],t0.[u_DADate],t0.[u_lrno], t0.[u_lrno],T0.[CardName], T0.[Address],T0.[Address2], T0.[NumAtCard],T0.[ShipToCode], T0.[DocTotal], T0.[U_CST],T0.[U_STIN1], T0.[U_Fright], T0.[U_TPNAME], T0.[U_TNO], T0.[U_DNAME], T0.[U_Warranty],T0.[U_PONum] ,T0.[U_TINNo],T0.[VatSum],T0.[CntctCode],T1.[ItemCode], T1.[Dscription], T1.[Quantity], T1.[Price], T1.[TaxCode], T2.[PymntGroup], T3.[TaxId5], T3.[TaxId11], t0.[U_AmntinWrds],T1.[DiscPrcnt],t1.[linetotal],T0.[U_VAT],T0.[RoundDif],T0.[TotalExpns], T0.[U_Insurence],T0.[U_Vat4],T0.[DocNum], T0.[U_VAT5], T0.[U_TCS1], T0.[U_CST1],

T0.[U_VAT],t0.U_PHONENO, T0.[U_Vat4],t0.U_OTHERSREF, T0.[U_VAT5],T0.[U_InWord],T0.[U_TCS1],t0.[u_bed],t0.[u_hces],t0.[u_ecess],T0.[TotalExpns],t0.[u_aed],

(select T6.[U_ECC] from OCRD t6 where T6.CardCode = T0.CardCode)Eccp,

(select T6.[U_Cst] from OCRD t6 where T6.CardCode = T0.CardCode)cstp,

(select round(sum(t4.[TaxSum]),0) from INV4 t4 where t4.DocEntry=t0.docentry and staType='-90') ED ,(select round(sum(t4.[TaxSum]),0) from INV4 t4 where t4.DocEntry=t0.docentry and staType='-55') HSC,(select round(sum(t4.[TaxSum]),0) from INV4 t4 where t4.DocEntry=t0.docentry and staType='-60') ECES,(select round(sum(t4.[TaxSum]),0) from INV4 t4 where t4.DocEntry=t0.docentry and staType='1') VAT,(select round(sum(t4.[TaxSum]),0) from INV4 t4 where t4.DocEntry=t0.docentry and staType='4') CST,(select t6.[U_Tin] from OCRD t6 where T6.CardCode = T0.CardCode)tinp,(select t5.CntctPrsn from ocrd t5 where t5.CardCode=t0.CardCode ) Contpers FROM [dbo].[OINV] T0 inner JOIN [dbo].[INV1] T1 ON T0.DocEntry = T1.DocEntry LEFT JOIN [dbo].[OCTG] T2 ON T0.GroupNum = T2.GroupNum left JOIN INV12 T3 ON T0.DocEntry = T3.DocEntry where T0.[Docentry]={?DocKey@}

Please help me on this issues.

Thanks&regards,

P.Pratap