need a help in UD Query

22 Views

Hi experts

i had made around 7 UDT in A/R invoice

1. Base Rate

2. No Days in Month

3. No Days Worked

4. SOTR (Special Over Time Rate)

5. SOTT (Special Over Time Hours)

6. NOTR (Normal Over Time Rate)

7. NOTT (Normal Over Time Hours)

now i need to calculate these tables date and need to appear in Unit Price Feild

The calcuation is as follows

Base Rate / No Days in Month * No Days Worked + SOTR (Special Over Time Rate) * SOTT (Special Over Time Hours) + NOTR (Normal Over Time Rate) * NOTT (Normal Over Time Hours)

the total amount should appear in A/R INVOICE " Unit Price" Table.

for this i had made a query also, but it is not working when am assigning to the "Unit Price Table"

But the when i run the query as normal wise, it returning the total

and this is in row wise.

The below attach is how i assign the Query

*******************************************************************************************************************************************************************************

SELECT INV1.U_BasicRate, INV1.U_NOTHour, INV1.U_NOTRate, INV1.U_SOTHours,

INV1.U_SOTRate, INV1.U_TotalDaysInMonth, INV1.U_TotalWorkDays,

(INV1.U_BasicRate / INV1.U_TotalDaysInMonth * INV1.U_TotalWorkDays + INV1.U_NOTHour * INV1.U_NOTRate)

+ INV1.U_SOTHours * INV1.U_SOTRate AS 'Total Invoice Rate'

FROM INV1 INNER JOIN

OINV ON INV1.DocEntry = OINV.DocEntry

WHERE 'Total Invoice Rate' = $ [$38.14.0]

***************************************************************************************************************************************************************************

pls help me sort it out

Regards