midrange.com code scratchpad
Name:
Michael Schutte
Scriptlanguage:
SQL
Tabwidth:
4
Date:
05/24/2011 02:53:08 pm
IP:
Logged
Description:
Stored Procedure manipulating table in qtemp and returning as result.
Code:
  1. CREATE PROCEDURE LIBRARY/GET_COMPANY_PROMOS_BREAKDOWN ( 
  2.     IN @COMPANY CHAR(3) , 
  3.     IN @FISYEAR DECIMAL(4, 0) , 
  4.     IN @FISPERIOD DECIMAL(2, 0) , 
  5.     IN @MEASURE CHAR(10) ) 
  6.     DYNAMIC RESULT SETS 1 
  7.     LANGUAGE SQL 
  8.     SPECIFIC LIBRARY/PGM
  9.     NOT DETERMINISTIC 
  10.     MODIFIES SQL DATA 
  11.     CALLED ON NULL INPUT 
  12.     SET OPTION  ALWBLK = *ALLREAD , 
  13.     OUTPUT=*PRINT,
  14.     ALWCPYDTA = *OPTIMIZE , 
  15.     COMMIT = *NONE , 
  16.     DECRESULT = (31, 31, 00) , 
  17.     DFTRDBCOL = *NONE , 
  18.     DLYPRP = *NO , 
  19.     DYNDFTCOL = *NO , 
  20.     DYNUSRPRF = *USER , 
  21.     RDBCNNMTH = *RUW , 
  22.     SRTSEQ = *HEX   
  23.     BEGIN 
  24.   
  25.         DECLARE NO_TABLE CONDITION FOR SQLSTATE '42704' ; 
  26.         DECLARE CURSOR_NOT_OPEN CONDITION FOR SQLSTATE '24501' ; 
  27.         DECLARE @BEGINYEAR DECIMAL ( 4 , 0 ) ; 
  28.         DECLARE @BEGINPERIOD DECIMAL ( 2 , 0 ) ; 
  29.         DECLARE @TMPYEAR DECIMAL ( 4 , 0 ) ; 
  30.         DECLARE @TMPPERIOD DECIMAL ( 2 , 0 ) ; 
  31.         DECLARE TABLEEXISTS VARCHAR ( 80 ) ;     
  32.         DECLARE CURSOROPEN CHAR ( 1 ) ;     
  33.         DECLARE C1 CURSOR WITH RETURN FOR 
  34.             SELECT    COALESCE ( SUMTLDD , DESCRIPTION ) AS DESCRIPTION , SUM ( AMOUNT ) AMOUNT 
  35.             FROM    QTEMP / PROMOS 
  36.             LEFT JOIN    TLFIL / TLPCODSUM 
  37.             ON    SUMCONO = @COMPANY 
  38.             AND    SUMSCD = 'PROMO' 
  39.             AND    DESCRIPTION = SUMTLDS
  40.             GROUP BY    COALESCE ( SUMTLDD , DESCRIPTION ) 
  41.             ORDER BY    1 ; 
  42.   
  43.         A : BEGIN 
  44.             DECLARE EXIT HANDLER FOR NO_TABLE 
  45.             BEGIN 
  46.                 SET TABLEEXISTS = '0' ; 
  47.             END ; 
  48.             DELETE FROM QTEMP / PROMOS ; 
  49.              
  50.             SET TABLEEXISTS = '1' ; 
  51.         END ; 
  52.         IF TABLEEXISTS = '0' THEN 
  53.             CREATE TABLE QTEMP / PROMOS ( 
  54.                 LOCATION CHAR( 6 ) NOT NULL WITH DEFAULT, 
  55.                 DESCRIPTION CHAR ( 30 ) NOT NULL WITH DEFAULT , 
  56.                 AMOUNT DEC ( 11 , 2 ) NOT NULL WITH DEFAULT 
  57.             ) ; 
  58.         END IF ; 
  59.   
  60.         SET @BEGINPERIOD = CASE    WHEN @MEASURE = 'ROLLYTD' THEN @FISPERIOD - 11 
  61.                 WHEN @MEASURE = 'ROLL90' THEN @FISPERIOD - 2 
  62.                 WHEN @MEASURE = 'YTD' THEN 1 
  63.                 WHEN @MEASURE = 'QTD' AND @FISPERIOD IN ( 1 , 2 , 3 ) THEN 1 
  64.                 WHEN @MEASURE = 'QTD' AND @FISPERIOD IN ( 4 , 5 , 6 ) THEN 4 
  65.                 WHEN @MEASURE = 'QTD' AND @FISPERIOD IN ( 7 , 8 , 9 ) THEN 7 
  66.                 WHEN @MEASURE = 'QTD' AND @FISPERIOD IN ( 10 , 11 , 12 ) THEN 10 
  67.                 ELSE @FISPERIOD 
  68.             END ;     
  69.   
  70.         SET @BEGINYEAR = @FISYEAR ; 
  71.         IF @BEGINPERIOD <= 0 THEN 
  72.             SET @BEGINPERIOD = @BEGINPERIOD + 12 ; 
  73.             SET @BEGINYEAR = @BEGINYEAR - 1 ; 
  74.         END IF ; 
  75.         SET @TMPYEAR = @BEGINYEAR ; 
  76.         SET @TMPPERIOD = @BEGINPERIOD ; 
  77.         WHILE @TMPYEAR * 100 + @TMPPERIOD <= @FISYEAR * 100 + @FISPERIOD DO 
  78.   
  79.             IF @TMPYEAR >= 2012 THEN 
  80.          
  81.                 INSERT INTO QTEMP / PROMOS 
  82.                 WITH cte AS ( 
  83.                     SELECT CTRC02, TXDESC, TXFAMT
  84.                     FROM ChartOfAccountsFile
  85.                     JOIN  AccountPostingFile
  86.                     ON     CTPAGE = TXPAGE                         
  87.                     WHERE     TXCO = @COMPANY                       
  88.                     AND     TXYEAR = @TMPYEAR
  89.                     AND     TXMNTH = @TMPPERIOD                          
  90.                     AND TXPOST = 'P'                           
  91.                     AND CTSEGN = 5                             
  92.                     AND CTCO = @COMPANY                         
  93.                     AND CTRC01 = @COMPANY                       
  94.                     AND CTRC04 IN ( SELECT CODCOD              
  95.                              FROM CodesFile
  96.                             WHERE CODCPY = @COMPANY AND                 
  97.                             CODTYP = 'DCA' AND                      
  98.                             CODUC1 = 'PROMO' AND                     
  99.                             CODSTATUS = '1' )   ) 
  100.                 SELECT CTRC02, TXDESC, TXFAMT FROM cte;
  101.      
  102.             ELSE 
  103.                 INSERT INTO QTEMP / PROMOS 
  104.                  WITH cte AS ( 
  105.                     SELECT CTRC02, TXDESC, TXFAMT 
  106.                     FROM ChartOfAccounts                  
  107.                     JOIN     AccountPostingFile
  108.                     ON     CTPAGE = TXPAGE                         
  109.                     WHERE     TXCO = @COMPANY                       
  110.                     AND     TXYEAR = @TMPYEAR
  111.                     AND     TXMNTH = @TMPPERIOD                          
  112.                     AND     TXPOST = 'P'                           
  113.                     AND     CTSEGN = 5                             
  114.                     AND     CTCO = @COMPANY                         
  115.                     AND     CTRC01 = @COMPANY                       
  116.                     AND     CTRC04 IN ( SELECT CODCOD              
  117.                                  FROM CodesFile                   
  118.                             WHERE CODCPY = @COMPANY AND                 
  119.                             CODTYP = 'DCA' AND                      
  120.                             CODUC1 = 'HIST' AND                     
  121.                             CODSTATUS = '1' )   ) 
  122.                 SELECT CTRC02, TXDESC, TXFAMT FROM cte;
  123.  
  124.                 
  125.                 DELETE FROM QTEMP / PROMOS 
  126.                 WHERE EXISTS (    SELECT    1 
  127.                         FROM    DescriptionSummaryFile
  128.                         WHERE    SUMCONO = @COMPANY 
  129.                         AND    SUMSCD <> 'PROMO' 
  130.                         AND    DESCRIPTION = SUMTLDS
  131.                     ) ; 
  132.                  
  133.                 IF @COMPANY = '001' THEN 
  134.                     DELETE FROM QTEMP / PROMOS 
  135.                     WHERE EXISTS (    SELECT    1 
  136.                             FROM    CodesFile
  137.                             WHERE    CODCPY = @COMPANY 
  138.                             AND    CODTYP = 'PCR' 
  139.                             AND    CODSTATUS = '1' 
  140.                             AND    CODCOD LIKE 'EXC%' 
  141.                             AND    TRIM ( DESCRIPTION ) LIKE '%' CONCAT TRIM ( CODDSC ) 
  142.                         ) ; 
  143.                 END IF ; 
  144.             END IF ; 
  145.             SET @TMPPERIOD = @TMPPERIOD + 1 ; 
  146.             IF @TMPPERIOD > 12 THEN 
  147.                 SET @TMPPERIOD = @TMPPERIOD - 12 ; 
  148.                 SET @TMPYEAR = @TMPYEAR + 1 ; 
  149.             END IF ; 
  150.              
  151.              
  152.         END WHILE ; 
  153. -- I need to make sure the cursor is closed, because this procedure will be called from RPG. and the RPG program will query the qtemp table.  The cursor remains open when a second call is made from the same job. 
  154.         B : BEGIN 
  155.             DECLARE EXIT HANDLER FOR CURSOR_NOT_OPEN 
  156.             BEGIN 
  157.                 SET CURSOROPEN = '0' ; 
  158.             END ; 
  159.             CLOSE C1 ; 
  160.              
  161.             SET CURSOROPEN = '1' ; 
  162.         END ; 
  163.   
  164.         OPEN C1 ; 
  165.     END  ;
© 2004-2019 by midrange.com generated in 0.053s valid xhtml & css