Suppliers Total to pay

[Former Member]

Hello Experts!

I have this query that i would like to add it 2 things:

1. Calculate Total after VAT - If it VAT must so it will be = AA.[Price]AA.[OpenQty]1.16

Else it will be = AA.[Price]*AA.[OpenQty].

2. Calculate Total only with US Dollar: (AA.[Price]*AA.[OpenQty] in US dollar)

IF Currency is $ = AA.[Price]*AA.[OpenQty]

IF Currency is NIS = (AA.[Price]*AA.[OpenQty]) / AA.[SysRate]

IF Currency is Euro = Euro Rate \ AA.[SysRate]

IF Currency is Pound Strlnig = 1.6 * AA.[Price] * AA.[OpenQty]

SELECT AA.[DocType] AS N'u05E1u05D5u05D2 u05DEu05E1u05DEu05DA', 
	AA.[CardCode], 
	AA.[CardName], 
	AA.[LineStatus], 
	AA.[DocNum], 
	AA.[Project], 
	AA.[OcrCode],
	AA.[GTotal],  
	AA.[TaxStatus], 
	AA.[TotalSumSy], 
	AA.[Price], 
	AA.[Quantity], 
	AA.[OpenQty],
	AA.[Price]*AA.[OpenQty]  'u05E1u05DBu05D5u05DD u05E4u05EAu05D5u05D7 u05DCu05EAu05E9u05DCu05D5u05DD',
	AA.[Currency], 
              AA.[SysRate] ,
	AA.[ItemCode], 
	AA.[Dscription], 
	AA.[DocDueDate], 
	AA.[DocDate] 
FROM (
	SELECT	N'u05D4u05D6u05DEu05E0u05EA u05E8u05DBu05E9' AS DocType, 
T0.[CardCode], T0.[CardName], T1.[LineStatus], T0.[DocNum], T1.[Project], T1.[OcrCode],T1.[GTotal],  T1.[TaxStatus], T1.[TotalSumSy], T1.[Price], T1.[Quantity], T1.[OpenQty],T1.[Price]*T1.[OpenQty] as 'u05E1u05DBu05D5u05DD u05E4u05EAu05D5u05D7 u05DCu05EAu05E9u05DCu05D5u05DD',T0.[SysRate] ,T1.[Currency], T1.[ItemCode], T1.[Dscription], T0.[DocDueDate], T0.[DocDate] 
	FROM OPOR T0  
		LEFT JOIN POR1 T1 ON T0.DocEntry = T1.DocEntry 
			WHERE (T0.[DocStatus] = 'O' AND T1.[LineStatus] = 'O')
	UNION ALL
	SELECT	N'u05DEu05E9u05DCu05D5u05D7 u05E8u05DBu05E9'AS DocType, 
T0.[CardCode], T0.[CardName], T1.[LineStatus], T0.[DocNum], T1.[Project], T1.[OcrCode],T1.[GTotal],  T1.[TaxStatus], T1.[TotalSumSy], T1.[Price], T1.[Quantity], T1.[OpenQty],T1.[Price]*T1.[OpenQty] as 'u05E1u05DBu05D5u05DD u05E4u05EAu05D5u05D7 u05DCu05EAu05E9u05DCu05D5u05DD',T0.[SysRate] ,T1.[Currency], T1.[ItemCode], T1.[Dscription], T0.[DocDueDate], T0.[DocDate] 
	FROM OPDN T0  
		LEFT JOIN PDN1 T1 ON T0.DocEntry = T1.DocEntry 
			WHERE (T0.[DocStatus] = 'O' AND T1.[LineStatus] = 'O')
	UNION ALL
	SELECT	N'u05EAu05E2u05D5u05D3u05EA u05E8u05DBu05E9'AS DocType, 
T0.[CardCode], T0.[CardName], T1.[LineStatus], T0.[DocNum], T1.[Project], T1.[OcrCode],T1.[GTotal],  T1.[TaxStatus], T1.[TotalSumSy], T1.[Price], T1.[Quantity], T1.[OpenQty],T1.[Price]*T1.[OpenQty] as 'u05E1u05DBu05D5u05DD u05E4u05EAu05D5u05D7 u05DCu05EAu05E9u05DCu05D5u05DD',T0.[SysRate] ,T1.[Currency], T1.[ItemCode], T1.[Dscription], T0.[DocDueDate], T0.[DocDate] 
	FROM OPCH T0  
		LEFT JOIN PCH1 T1 ON T0.DocEntry = T1.DocEntry 
			WHERE (T0.[DocStatus] = 'O' AND T1.[LineStatus] = 'O')
	UNION ALL
	SELECT	N'u05D6u05D9u05DBu05D5u05D9 u05E8u05DBu05E9'AS DocType, 
	T0.[CardCode], T0.[CardName], T1.[LineStatus], T0.[DocNum], T1.[Project], T1.[OcrCode],-1*T1.[GTotal],  T1.[TaxStatus], -1*T1.[TotalSumSy], T1.[Price], -1*T1.[Quantity], T1.[OpenQty],T1.[Price]*(-1*T1.[Quantity]) as 'u05E1u05DBu05D5u05DD u05E4u05EAu05D5u05D7 u05DCu05EAu05E9u05DCu05D5u05DD',T0.[SysRate] ,T1.[Currency], T1.[ItemCode], T1.[Dscription], T0.[DocDueDate], T0.[DocDate] 
	FROM ORPC T0  
		LEFT JOIN RPC1 T1 ON T0.DocEntry = T1.DocEntry 
			WHERE (T0.[DocStatus] = 'O' AND T1.[LineStatus] = 'O') ) AA

Your Help please!

Thank You!

Meital

[former_member186095 · Accepted solution]

Hi,

you have pasted a very detail query but I see that you use union all syntax. It seems that you try to display all query results from different tables i.e. OPOR, OPDN, OPCH and OPRC. So, I suggest just put one query only. I mean just query for one table.

Rgds,

JimM

[former_member325312]

Hi

follow the below Query i think this will solve ur prob

SELECT T0.[DocNum]'PO NO', T0.[DocDate], T0.[CardName], T1.[ItemCode], T1.[Quantity], T0.[DocTotal],

T2.[DocNum]'GRPO NO', T2.[DocDate], T2.[CardName], T3.[ItemCode], T3.[Quantity], T2.[DocTotal],

T4.[DocNum]'A/P Invoice No', T4.[DocDate], T4.[CardName], T5.[ItemCode], T5.[Quantity], T4.[DocTotal],

T6.[DocNum]'A/P Debit Note', T6.[DocDate], T6.[CardName], T7.[ItemCode], T7.[Quantity], T6.[DocTotal]

FROM OPOR T0 INNER JOIN POR1 T1 ON T0.DocEntry = T1.DocEntry,

OPDN T2 INNER JOIN PDN1 T3 ON T2.DocEntry = T3.DocEntry,

OPCH T4 INNER JOIN PCH1 T5 ON T4.DocEntry = T5.DocEntry,

ORPC T6 INNER JOIN RPC1 T7 ON T6.DocEntry = T7.DocEntry

WHERE T7.[BaseRef] = T4.[DocNum] AND T5.[BaseRef] = T2.[DocNum] AND

T3.[BaseRef] = T0.[DocNum] AND T7.[ItemCode] = T5.[ItemCode] AND

T5.[ItemCode] = T3.[ItemCode] AND T3.[ItemCode] = T1.[ItemCode]

GROUP BY T0.[DocNum], T0.[DocDate], T0.[CardName], T1.[ItemCode], T1.[Quantity], T0.[DocTotal],

T2.[DocNum], T2.[DocDate], T2.[CardName], T3.[ItemCode], T3.[Quantity], T2.[DocTotal],

T4.[DocNum], T4.[DocDate], T4.[CardName], T5.[ItemCode], T5.[Quantity], T4.[DocTotal],

T6.[DocNum], T6.[DocDate], T6.[CardName], T7.[ItemCode], T7.[Quantity], T6.[DocTotal]

Regards

Jenny

[Former Member]

I have this query that i would like to add it 2 things:

1. Calculate Total after VAT - If it VAT must so it will be = AA.[Price]AA.[OpenQty]1.16

Else it will be = AA.[Price]*AA.[OpenQty].

2. Calculate Total only with US Dollar: (AA.[Price]AA.[OpenQty] in US dollar) IF Currency is $ = AA.[Price]AA.[OpenQty]

IF Currency is NIS = (AA.[Price]*AA.[OpenQty]) / AA.[SysRate]

IF Currency is Euro = Euro Rate \ AA.[SysRate]

IF Currency is Pound Strlnig = 1.6 * AA.[Price] * AA.[OpenQty]

MEITAL