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®ards,
P.Pratap