CREATE PROCEDURE PROC1 (IN StDate date, IN EnDate date, IN TempCom1 VARCHAR( 50 ), IN TempCom2 VARCHAR( 50 ), IN WhichQuery VARCHAR( 6 ))

 DYNAMIC RESULT SETS  1  
 LANGUAGE SQL 
 SPECIFIC PROC1
 NOT DETERMINISTIC 
 MODIFIES SQL DATA 
 CALLED ON NULL INPUT 
--
-- SQL Stored Procedure 
--
P1: BEGIN 
DECLARE stmt16 VARCHAR( 6144 );
DECLARE v_sqlstmt16 VARCHAR( 6144 );

DECLARE GTK16 CURSOR WITH RETURN FOR stmt16;


CASE WHICHQUERY

WHEN 'GTK16' THEN SET v_sqlstmt16='
WITH DefSums AS 
(SELECT TD.KntID AS dfsmsKntID 
 ,SUM(TD.InvCost*TK.CurrRate_c) AS dfsmsTDInv  
,SUM(case when top.C_oplat is not null then TOP.C_Oplat*TK.CurrRate_r else 0 end) AS dfsmsTOpC_Opl 
FROM (BASE1.Table2 TD
 LEFT OUTER JOIN BASE1.Table1 TK ON TK.KntID=TD.KntID)
 LEFT OUTER JOIN BASE1.Table3 TOP ON TD.KntID=TOP.KntID 
WHERE (''' CONCAT CAST(EnDate AS VARCHAR( 10 )) CONCAT ''')>TD.BoardDate GROUP BY TD.KntID
having SUM(TD.InvCost*TK.CurrRate_c)<>SUM(case when top.c_oplat is not null then TOP.C_Oplat*TK.CurrRate_r else 0 end)
 AND SUM(TD.InvCost*TK.CurrRate_c)>SUM(case when top.c_oplat is not null then TOP.C_Oplat*TK.CurrRate_r else 0 end)) 
,dfKntSum AS 
(SELECT TA.KntID AS TAgrmKntID
 ,MAX(TA.Agrmno) AS TAgrmNo
 ,TA.AgrmSum AS TAgrmSum
 ,TA.CurrRate AS TAgrmCurrRate 
FROM BASE1.Table4 TA 
GROUP BY TA.KntID
 ,TA.AgrmSum
 ,TA.CurrRate)

SELECT TK.Name_ExpFir AS "Наим. эксп-ра" 
 ,TK.Name_ImpFir AS "Наим. получ" 
 ,TK.NomKont CONCAT ' CONCAT ''' '' CONCAT CAST(TK.D_Kont AS CHAR(10)) AS "Номер и дата закл. контр." 
 ,CASE WHEN TK.KntID=TAgrmKntID AND TAgrmSum<>0 THEN TAgrmSum ELSE TK.KntSum END AS "Сумма в вал. конт." 
 ,CASE WHEN TK.KntID=TAgrmKntID AND TAgrmSum<>0 THEN TAgrmSum*TAgrmCurrRate/TD.G13 ELSE TK.KntSum*TK.CurrRate_c/TD.G13 END AS "Сумма в дол. США" 
 ,SUM(CASE WHEN TOpl.KntID=TK.KntID AND YEAR(TOpl.D_Oplat)=YEAR(''' CONCAT CAST(EnDate AS VARCHAR( 10 )) CONCAT ''') THEN TOpl.C_Oplat*TK.CurrRate_r ELSE 0 END) AS "Пост. вал. выр. с нач. года" 
 ,SUM(CASE WHEN TOpl.KntID=TK.KntID AND TOpl.D_Oplat BETWEEN ('''CONCAT CAST(StDate AS VARCHAR( 10 )) CONCAT''') AND (''' CONCAT CAST(EnDate AS VARCHAR( 10 )) CONCAT ''') THEN TOpl.C_Oplat*TK.CurrRate_r ELSE 0 END) AS "Пост. вал. выр. за период" 
 ,SUM(CASE WHEN TCSl.KntID=TK.KntID AND YEAR(TCSl.PmDate)=YEAR(''' CONCAT CAST(EnDate AS VARCHAR( 10 )) CONCAT ''') THEN TCSl.PmSum*TCSl.CurrRate/TCSl.CurrCount ELSE 0 END) AS "Обяз. прод с нач. года" 
 ,SUM(CASE WHEN TCSl.KntID=TK.KntID AND TCSl.PmDate BETWEEN ('''CONCAT CAST(StDate AS VARCHAR( 10 )) CONCAT''') AND (''' CONCAT CAST(EnDate AS VARCHAR( 10 )) CONCAT ''') THEN TCSl.PmSum*TCSl.CurrRate/TCSl.CurrCount ELSE 0 END) AS "Обяз. прод с нач. мес." 
 ,dfsmsTDInv-dfsmsTOpC_Opl AS "Деб. задолж." 
,SUM(CASE WHEN UCASE(TK.VidExpOp)=''К'' AND dfsmsTDInv<dfsmsTOpC_Opl AND '''CONCAT CAST(EnDate AS VARCHAR( 10 )) CONCAT''' - TD.BoardDate - 180>0 THEN '''CONCAT CAST(EnDate AS VARCHAR( 10 )) CONCAT''' - TD.BoardDate - 180 WHEN UCASE(TK.VidExpOp)=''Д'' AND dfsmsTDInv<dfsmsTOpC_Opl AND '''CONCAT CAST(EnDate AS VARCHAR( 10 )) CONCAT''' - TD.BoardDate - 60>0 THEN '''CONCAT CAST(EnDate AS VARCHAR( 10 )) CONCAT''' - TD.BoardDate - 60 WHEN UCASE(TK.VidExpOp)=''Ц'' AND dfsmsTDInv<dfsmsTOpC_Opl AND '''CONCAT CAST(EnDate AS VARCHAR( 10 )) CONCAT''' - TD.BoardDate - 80>0 THEN '''CONCAT CAST(EnDate AS VARCHAR( 10 )) CONCAT''' - TD.BoardDate - 80 ELSE 0 END) AS "Просроченная деб. зад-ть"
,''_'' AS "Снятие с учета" FROM ((((DefSums INNER JOIN BASE1.Table1 TK ON TK.KntID=dfsmsKntID)
 LEFT OUTER JOIN BASE1.Table2 TD ON TK.KntID=TD.KntID)
 LEFT OUTER JOIN BASE1.Table5 TCSl ON TCSl.KntID=TK.KntID)
 LEFT OUTER JOIN BASE1.Table3 TOpl ON TOpl.KntID=TK.KntID)LEFT OUTER JOIN dfKntSum ON TK.KntID=TAgrmKntID 
WHERE TK.KntType=''ЭК''';
IF TempCom2 IS NULL
THEN SET v_sqlstmt16=v_sqlstmt16 CONCAT ' GROUP BY TK.Name_ExpFir 
 ,TK.Name_ImpFir 
 ,TK.NomKont CONCAT ' CONCAT ''' '' CONCAT CAST(TK.D_Kont AS CHAR(10)) 
 ,CASE WHEN TK.KntID=TAgrmKntID AND TAgrmSum<>0 THEN TAgrmSum ELSE TK.KntSum END
 ,CASE WHEN TK.KntID=TAgrmKntID AND TAgrmSum<>0 THEN TAgrmSum*TAgrmCurrRate/TD.G13 ELSE TK.KntSum*TK.CurrRate_c/TD.G13 END
 ,dfsmsTDInv-dfsmsTOpC_Opl 
,''_''';
ELSE SET v_sqlstmt16=v_sqlstmt16 CONCAT ' AND TK.NomKont=''' CONCAT TempCom2 CONCAT ''' GROUP BY TK.Name_ExpFir 
 ,TK.Name_ImpFir 
 ,TK.NomKont CONCAT ' CONCAT ''' '' CONCAT CAST(TK.D_Kont AS CHAR(10)) 
 ,CASE WHEN TK.KntID=TAgrmKntID AND TAgrmSum<>0 THEN TAgrmSum ELSE TK.KntSum END
 ,CASE WHEN TK.KntID=TAgrmKntID AND TAgrmSum<>0 THEN TAgrmSum*TAgrmCurrRate/TD.G13 ELSE TK.KntSum*TK.CurrRate_c/TD.G13 END
 ,dfsmsTDInv-dfsmsTOpC_Opl 
,''_''';
END IF;
SET v_sqlstmt16=v_sqlstmt16 CONCAT ' UNION ALL SELECT ''ИТОГО'' 
 ,''_'' 
 ,''_'' 
 ,SUM(CASE WHEN TK.KntID=TAgrmKntID THEN TAgrmSum ELSE TK.KntSum END) 
 ,SUM(CASE WHEN TK.KntID=TAgrmKntID THEN TAgrmSum*TAgrmCurrRate/TD.G13 ELSE TK.KntSum*TK.CurrRate_c/TD.G13 END) 
 ,SUM(CASE WHEN TOpl.KntID=TK.KntID AND YEAR(TOpl.D_Oplat)=YEAR(''' CONCAT CAST(EnDate AS VARCHAR( 10 )) CONCAT ''') THEN TOpl.C_Oplat*TK.CurrRate_r ELSE 0 END) AS "Пост. вал. выр. с нач. года" 
 ,SUM(CASE WHEN TOpl.KntID=TK.KntID AND TOpl.D_Oplat BETWEEN ('''CONCAT CAST(StDate AS VARCHAR( 10 )) CONCAT''') AND (''' CONCAT CAST(EnDate AS VARCHAR( 10 )) CONCAT ''') THEN TOpl.C_Oplat*TK.CurrRate_r ELSE 0 END) AS "Пост. вал. выр. с нач. мес." 
 ,SUM(CASE WHEN TCSl.KntID=TK.KntID AND YEAR(TCSl.PmDate)=YEAR(''' CONCAT CAST(EnDate AS VARCHAR( 10 )) CONCAT ''') THEN TCSl.PmSum*TCSl.CurrRate/TCSl.CurrCount ELSE 0 END) AS "Обяз. прод с нач. года" 
 ,SUM(CASE WHEN TCSl.KntID=TK.KntID AND TCSl.PmDate BETWEEN ('''CONCAT CAST(StDate AS VARCHAR( 10 )) CONCAT''') AND (''' CONCAT CAST(EnDate AS VARCHAR( 10 )) CONCAT ''') THEN TCSl.PmSum*TCSl.CurrRate/TCSl.CurrCount ELSE 0 END) AS "Обяз. прод с нач. мес." 
 ,SUM(dfsmsTDInv-dfsmsTOpC_Opl) AS "Деб. задолж." 
 ,SUM(CASE WHEN UCASE(TK.VidExpOp)=''К'' AND dfsmsTDInv<dfsmsTOpC_Opl AND '''CONCAT CAST(EnDate AS VARCHAR( 10 )) CONCAT''' - TD.BoardDate - 180>0 THEN '''CONCAT CAST(EnDate AS VARCHAR( 10 )) CONCAT''' - TD.BoardDate - 180 WHEN UCASE(TK.VidExpOp)=''Д'' AND dfsmsTDInv<dfsmsTOpC_Opl AND '''CONCAT CAST(EnDate AS VARCHAR( 10 )) CONCAT''' - TD.BoardDate - 60>0 THEN '''CONCAT CAST(EnDate AS VARCHAR( 10 )) CONCAT''' - TD.BoardDate - 60 WHEN UCASE(TK.VidExpOp)=''Ц'' AND dfsmsTDInv<dfsmsTOpC_Opl AND '''CONCAT CAST(EnDate AS VARCHAR( 10 )) CONCAT''' - TD.BoardDate - 80>0 THEN '''CONCAT CAST(EnDate AS VARCHAR( 10 )) CONCAT''' - TD.BoardDate - 80 ELSE 0 END) AS "Просроченная деб. зад-ть"
 ,''_'' FROM ((((DefSums INNER JOIN BASE1.Table1 TK ON TK.KntID=dfsmsKntID)
 LEFT OUTER JOIN BASE1.Table2 TD ON TK.KntID=TD.KntID)
 LEFT OUTER JOIN BASE1.Table5 TCSl ON TCSl.KntID=TK.KntID)
 LEFT OUTER JOIN BASE1.Table3 TOpl ON TOpl.KntID=TK.KntID)LEFT OUTER JOIN dfKntSum ON TK.KntID=TAgrmKntID
WHERE TK.KntType=''ЭК'' GROUP BY ''ИТОГО'' 
 ,''_'' 
 ,''_'' 
 ,''_'' '; 
PREPARE stmt16 FROM v_sqlstmt16;
OPEN GTK16;

END CASE;
END P1