Oct 14, 2013 at 11:24 AM

Grouping where conditions on a UDF table for a TN query

50 Views

Hello experts

The thing is a bit hard to explain: I have a TN query which every time that a a draft document is going to be added on system, it checks cost center total amount and cost center budget and if draft document is going to cause an excess on this cost center's budget, draft document addition gets blocked. This is the TN query:

IF@object_type = '112' AND (@transaction_type IN(N'A',N'U'))BEGINIFExists(SELECT T0.DocEntry From DRF1 T0  INNER JOIN ODRF T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OPRC T2 ON T0.OcrCode = T2.PrcCode WHERET0.OcrCode IN ('MKT0201', 'ADM0101', 'GER0102', 'PRE0101', 'GER0202', 'GER0201', 'INS0101', 'MKT0205', 'FUT0604', 'COM0103', 'GER0100', 'MKT0204', 'INS0201', 'MKT0202', 'PRE0202', 'GER0101', 'PRE0201', 'GER0103', 'PRE0104', 'PRE0102', 'PRE0103', 'FUT0601', 'COM0301' , 'COM0301' , 'COM0102' , 'PRE0106' , 'MED0101' , 'FUT0703' , 'FUT0803' , 'FUT0501' , 'MKT0101' , 'FUT0602')AND T0.DocDate >= (CONVERT(DATETIME, '20130701', 112)) AND T0.DocDate <= (CONVERT(DATETIME, '20140630', 112)) AND T1.DocEntry = @list_of_cols_val_tab_delGROUPby T0.DocEntry,T2.U_SFCBudgetCeco, T2.U_SFCBudgetCeco2  , T0.LineTotal , T2.U_SFCBudgetCeco3 , T2.U_SFCBudgetCeco4 , T2.U_SFCBudgetCeco5 , T2.U_SFCBudgetCeco6havingT2.U_SFCBudgetCeco  < T2.U_SFCBudgetCeco2 + T2.U_SFCBudgetCeco3 + T2.U_SFCBudgetCeco4 - T2.U_SFCBudgetCeco5 + T2.U_SFCBudgetCeco6)BEGINSELECT @error = 1    SELECT @error_message = 'Mensaje sistema!: Presupuesto centro de coste excedido'ENDEND

On this point query is working as desired and expected. The problem comes when I try to change the query so the cost centers whose budget have to be limited are checked against a User Defined Table instead of being checked against OcrCode values in brackets. The purpose of doing it this way is to make Cost Center Budget control solution a bit more "user friendly", because this way our Cost Controller will be able to add or remove cost centers into budget control solution just by adding or removing OcrCode values on UDT. This is the modified query:

IF@object_type = '112' AND (@transaction_type IN(N'A',N'U'))BEGINIFExists(SELECT T0.DocEntry From DRF1 T0  INNER JOIN ODRF T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OPRC T2 ON T0.OcrCode = T2.PrcCode INNER JOIN U_SFCPRESUPCECOS T3 ON T2.PrcCode = T3.CodeWHERET0.OcrCode = T3.Code  AND T0.DocDate >= (CONVERT(DATETIME, '20130701', 112)) AND T0.DocDate <= (CONVERT(DATETIME, '20140630', 112)) AND T1.DocEntry = @list_of_cols_val_tab_delGROUPby T0.DocEntry,T2.U_SFCBudgetCeco, T2.U_SFCBudgetCeco2  , T0.LineTotal , T2.U_SFCBudgetCeco3 , T2.U_SFCBudgetCeco4 , T2.U_SFCBudgetCeco5 , T2.U_SFCBudgetCeco6havingT2.U_SFCBudgetCeco  < T2.U_SFCBudgetCeco2 + T2.U_SFCBudgetCeco3 + T2.U_SFCBudgetCeco4 - T2.U_SFCBudgetCeco5 + T2.U_SFCBudgetCeco6)BEGINSELECT @error = 1    SELECT @error_message = 'Mensaje sistema!: Presupuesto centro de coste excedido'ENDEND

There must be some problem with the new query, because despite MS SQL Server Management Studio is not detecting any sintax error when executing the query, system is displaying an internal error when trying to add drafts documents on system, no matter if the document is going to cause budget overrun.

Thanks in advance and regards