Форум программистов [Powered by Invision Power Board]

Код

CREATE OR ALTER TRIGGER ADD_PURCHASE_FROM_TEMP FOR PURCHASE_ORDER_LINE_TEMP
ACTIVE BEFORE INSERT POSITION 0
AS
DECLARE VARIABLE WK_FOUND INTEGER;
DECLARE VARIABLE WK_H_PERSON_ID VARCHAR(10); 
DECLARE VARIABLE WK_P_PERSON_TYPE CHAR(2);
DECLARE VARIABLE WK_P_FIRST_NAME VARCHAR(50); 
DECLARE VARIABLE WK_P_LAST_NAME VARCHAR(50); 
DECLARE VARIABLE WK_P_ADDRESS_LINE1 VARCHAR(100); 
DECLARE VARIABLE WK_P_ADDRESS_LINE2 VARCHAR(50); 
DECLARE VARIABLE WK_P_CITY VARCHAR(25); 
DECLARE VARIABLE WK_P_STATE VARCHAR(15); 
DECLARE VARIABLE WK_P_POST_CODE VARCHAR(10); 
DECLARE VARIABLE WK_P_COUNTRY VARCHAR(25);

DECLARE VARIABLE WK_H_PURCHASE_ORDER VARCHAR(10);
DECLARE VARIABLE WK_H_REQUISITION_NO VARCHAR(10) ; 
DECLARE VARIABLE WK_H_PO_DATE        TIMESTAMP ; 
DECLARE VARIABLE WK_H_PO_REVISION_NO CHAR(2) ; 
DECLARE VARIABLE WK_H_PO_STATUS      CHAR(2) ; 
DECLARE VARIABLE WK_H_COMMENTS       VARCHAR(255) ; 
DECLARE VARIABLE WK_H_PO_PRINTED     TIMESTAMP ; 
DECLARE VARIABLE WK_H_USER_ID        VARCHAR(10) ; 
DECLARE VARIABLE WK_H_COMPANY_ID  VARCHAR(20);
DECLARE VARIABLE WK_H_DIVISION_ID  VARCHAR(20);
DECLARE VARIABLE WK_H_ORDER_TYPE  CHAR(2);

DECLARE VARIABLE WK_L_PURCHASE_ORDER  VARCHAR(10);
DECLARE VARIABLE WK_L_PO_LINE         CHAR(4);
DECLARE VARIABLE WK_L_REQUISITION_NO  VARCHAR(10) ; 
DECLARE VARIABLE WK_L_PROD_ID         VARCHAR(30) ; 
DECLARE VARIABLE WK_L_PO_LINE_QTY     INTEGER ; 
DECLARE VARIABLE WK_L_UNIT_PRICE      NUMERIC(9, 3) ; 
DECLARE VARIABLE WK_L_UOM_ORDER       CHAR(2) ; 
DECLARE VARIABLE WK_L_PO_LINE_DUE_DATE TIMESTAMP ; 
DECLARE VARIABLE WK_L_PO_LINE_STATUS  CHAR(2) ; 
DECLARE VARIABLE WK_L_COMMENTS        VARCHAR(255) ; 
DECLARE VARIABLE WK_L_GST_VALUE       INTEGER ; 
DECLARE VARIABLE WK_L_GST_RATE        INTEGER ; 
DECLARE VARIABLE WK_L_GST_CODE        VARCHAR(3) ; 
DECLARE VARIABLE WK_L_LINE_CNT        INTEGER ; 
DECLARE VARIABLE WK_LEN_LABEL_NO      INTEGER; 
DECLARE VARIABLE WK_PROD_LENGTH       INTEGER;
DECLARE VARIABLE WK_PROD_FIXED        CHAR(1);
DECLARE VARIABLE WK_PURCHASE_LENGTH   INTEGER;
DECLARE VARIABLE WK_PURCHASE_FIXED    CHAR(1);
DECLARE VARIABLE WK_PURCHASE_ORDER    VARCHAR(10); 
BEGIN
   WK_H_PERSON_ID  = NEW.H_PERSON_ID;
   WK_P_PERSON_TYPE = NEW.P_PERSON_TYPE;
   WK_P_FIRST_NAME = NEW.P_FIRST_NAME;
   WK_P_LAST_NAME = NEW.P_LAST_NAME;
   WK_P_ADDRESS_LINE1 = NEW.P_ADDRESS_LINE1;
   WK_P_ADDRESS_LINE2 = NEW.P_ADDRESS_LINE2;
   WK_P_CITY = NEW.P_CITY;
   WK_P_STATE = NEW.P_STATE;
   WK_P_POST_CODE = NEW.P_POST_CODE;
   WK_P_COUNTRY = NEW.P_COUNTRY;

   WK_H_PURCHASE_ORDER = NEW.H_PURCHASE_ORDER ;
   WK_H_REQUISITION_NO = NEW.H_REQUISITION_NO ;
   WK_H_PO_DATE        = NEW.H_PO_DATE        ;
   WK_H_PO_REVISION_NO = NEW.H_PO_REVISION_NO ;
   WK_H_PO_STATUS      = NEW.H_PO_STATUS      ;
   WK_H_COMMENTS       = NEW.H_COMMENTS       ;
   WK_H_PO_PRINTED     = NEW.H_PO_PRINTED     ;
   WK_H_USER_ID        = NEW.H_USER_ID        ;
   WK_H_COMPANY_ID = NEW.H_COMPANY_ID;
   WK_H_DIVISION_ID = NEW.H_DIVISION_ID;
   IF (NEW.H_ORDER_TYPE IS NULL) THEN
   BEGIN
      WK_H_ORDER_TYPE = 'PO';
   END
   ELSE
   BEGIN
      WK_H_ORDER_TYPE = NEW.H_ORDER_TYPE;
   END

   WK_L_PURCHASE_ORDER  = NEW.L_PURCHASE_ORDER  ;
   WK_L_PO_LINE         = NEW.L_PO_LINE         ;
   WK_L_REQUISITION_NO  = NEW.L_REQUISITION_NO  ;
   WK_L_PROD_ID         = NEW.L_PROD_ID         ;
   WK_L_PO_LINE_QTY     = NEW.L_PO_LINE_QTY     ;
   WK_L_UNIT_PRICE      = NEW.L_UNIT_PRICE      ;
   WK_L_UOM_ORDER       = NEW.L_UOM_ORDER       ;
   WK_L_PO_LINE_DUE_DATE = NEW.L_PO_LINE_DUE_DATE ;
   WK_L_PO_LINE_STATUS  = NEW.L_PO_LINE_STATUS  ;
   WK_L_COMMENTS        = NEW.L_COMMENTS        ;
   WK_L_GST_VALUE       = NEW.L_GST_VALUE       ;
   WK_L_GST_RATE        = NEW.L_GST_RATE        ;
   WK_L_GST_CODE        = NEW.L_GST_CODE        ;
   /* Check person */
   WK_FOUND = 0;
   SELECT 1 FROM PERSON 
      WHERE PERSON_ID = :WK_H_PERSON_ID
      INTO :WK_FOUND;
   IF (WK_FOUND = 0) THEN
   BEGIN
      /* no person so insert it */
      INSERT INTO PERSON (PERSON_ID, PERSON_TYPE, FIRST_NAME, LAST_NAME, ADDRESS_LINE1, ADDRESS_LINE2, CITY, STATE, POST_CODE, COUNTRY )
      VALUES (:WK_H_PERSON_ID, :WK_P_PERSON_TYPE, :WK_P_FIRST_NAME, :WK_P_LAST_NAME, :WK_P_ADDRESS_LINE1, :WK_P_ADDRESS_LINE2, :WK_P_CITY, :WK_P_STATE, :WK_P_POST_CODE, :WK_P_COUNTRY);
   END
   ELSE
   BEGIN
      UPDATE PERSON  SET PERSON_TYPE = :WK_P_PERSON_TYPE, 
         FIRST_NAME = :WK_P_FIRST_NAME, 
         LAST_NAME = :WK_P_LAST_NAME, 
         ADDRESS_LINE1 = :WK_P_ADDRESS_LINE1, 
         ADDRESS_LINE2 = :WK_P_ADDRESS_LINE2, 
         CITY = :WK_P_CITY, 
         STATE = :WK_P_STATE, 
         POST_CODE = :WK_P_POST_CODE, 
         COUNTRY = :WK_P_COUNTRY 
       WHERE PERSON_ID = :WK_H_PERSON_ID;
   END
   WK_FOUND = 0;
   /* Get length for purchase */   
   SELECT MAX_LENGTH, FIXED_LENGTH
   FROM PARAM
   WHERE DATA_ID = 'PURCHASE'
   INTO :WK_PURCHASE_LENGTH, :WK_PURCHASE_FIXED;
   IF (WK_PURCHASE_LENGTH IS NULL) THEN
   BEGIN
      WK_PURCHASE_LENGTH = 0;
   END
   IF (WK_PURCHASE_FIXED IS NULL) THEN
   BEGIN
      WK_PURCHASE_FIXED = 'F';
   END
   IF (SUBSTR(WK_H_PURCHASE_ORDER,1,1) <> 'P') THEN
   BEGIN
      IF (WK_PURCHASE_FIXED = 'T') THEN
      BEGIN
         WK_PURCHASE_ORDER = WK_H_PURCHASE_ORDER;
         WK_H_PURCHASE_ORDER = 'P' ; 
         WK_LEN_LABEL_NO = STRLEN(WK_PURCHASE_ORDER) + 1 ;     
         /* Padding leading with 0 */
         WHILE (WK_LEN_LABEL_NO < :WK_PURCHASE_LENGTH) DO
         BEGIN
            WK_H_PURCHASE_ORDER= WK_H_PURCHASE_ORDER || '0';      
            WK_LEN_LABEL_NO = WK_LEN_LABEL_NO + 1;
         END
         WK_H_PURCHASE_ORDER= WK_H_PURCHASE_ORDER || WK_PURCHASE_ORDER;      
      END
   END
   WK_L_PURCHASE_ORDER = WK_H_PURCHASE_ORDER ;
   IF (NEW.L_PURCHASE_ORDER IS NULL) THEN
   BEGIN
      NEW.L_PURCHASE_ORDER = WK_L_PURCHASE_ORDER;
   END

   SELECT 1 FROM PURCHASE_ORDER
      WHERE PURCHASE_ORDER = :WK_H_PURCHASE_ORDER
      INTO :WK_FOUND;
   IF (WK_FOUND = 0) THEN
   BEGIN
      /* no order so insert it */
      INSERT INTO PURCHASE_ORDER (
         PURCHASE_ORDER, 
         REQUISITION_NO,
         PO_DATE,      
         PO_REVISION_NO,
         PO_STATUS,    
         COMMENTS,    
         PO_PRINTED, 
         USER_ID,   
         PERSON_ID,
         COMPANY_ID,
         DIVISION_ID,
         ORDER_TYPE)
      VALUES (
         :WK_H_PURCHASE_ORDER     ,
         :WK_H_REQUISITION_NO    ,
         :WK_H_PO_DATE          ,
         :WK_H_PO_REVISION_NO  ,
         :WK_H_PO_STATUS             ,
         :WK_H_COMMENTS             ,
         :WK_H_PO_PRINTED          ,
         :WK_H_USER_ID            ,
         :WK_H_PERSON_ID ,
         :WK_H_COMPANY_ID ,
         :WK_H_DIVISION_ID,
         :WK_H_ORDER_TYPE);
   END
   ELSE
   BEGIN
      UPDATE PURCHASE_ORDER SET
         REQUISITION_NO = :WK_H_REQUISITION_NO ,
         PO_DATE =     :WK_H_PO_DATE ,
         PO_REVISION_NO = :WK_H_PO_REVISION_NO  ,
         PO_STATUS =   :WK_H_PO_STATUS,
         COMMENTS =   :WK_H_COMMENTS ,
         PO_PRINTED = :WK_H_PO_PRINTED ,
         USER_ID =  :WK_H_USER_ID ,
        PERSON_ID = :WK_H_PERSON_ID ,
        COMPANY_ID = :WK_H_COMPANY_ID ,
        DIVISION_ID = :WK_H_DIVISION_ID, 
        ORDER_TYPE = :WK_H_ORDER_TYPE 
         WHERE PURCHASE_ORDER = :WK_H_PURCHASE_ORDER ;
   END
   /* Get length for product */   
   SELECT MAX_LENGTH, FIXED_LENGTH
   FROM PARAM
   WHERE DATA_ID = 'PROD_INTERNAL'
   INTO :WK_PROD_LENGTH, :WK_PROD_FIXED;
   IF (WK_PROD_LENGTH IS NULL) THEN
   BEGIN
      WK_PROD_LENGTH = 0;
   END
   IF (WK_PROD_FIXED IS NULL) THEN
   BEGIN
      WK_PROD_FIXED = 'F';
   END
   IF (WK_PROD_FIXED = 'T') THEN
   BEGIN
      WK_LEN_LABEL_NO = STRLEN(WK_L_PROD_ID) ;     
      /* Padding leading with 0 */
      WHILE (WK_LEN_LABEL_NO < :WK_PROD_LENGTH) DO
      BEGIN
         WK_L_PROD_ID = '0' || WK_L_PROD_ID ;      
         WK_LEN_LABEL_NO = WK_LEN_LABEL_NO + 1;
      END
   END

   /* do I have a po line */
   IF (WK_L_PO_LINE IS NULL) THEN
   BEGIN
      SELECT FIRST 1 PO_LINE 
      FROM PURCHASE_ORDER_LINE
      WHERE PURCHASE_ORDER = :WK_L_PURCHASE_ORDER AND
      PROD_ID = :WK_L_PROD_ID 
      INTO :WK_L_PO_LINE;
   END
   IF (WK_L_PO_LINE IS NULL) THEN
   BEGIN
      /* ok so no line - must generate one */
      SELECT COUNT(*) 
      FROM PURCHASE_ORDER_LINE
      WHERE PURCHASE_ORDER = :WK_L_PURCHASE_ORDER 
      INTO :WK_L_LINE_CNT;
      IF (WK_L_LINE_CNT IS NULL) THEN
      BEGIN
         WK_L_LINE_CNT = 0;
      END
      WK_L_LINE_CNT = WK_L_LINE_CNT + 1;
      WK_L_PO_LINE = LPAD(WK_L_LINE_CNT,'0',4);
   END
   IF (NEW.L_PO_LINE IS NULL) THEN
   BEGIN
      NEW.L_PO_LINE = WK_L_PO_LINE;
   END
   WK_FOUND = 0;
   SELECT 1 FROM PURCHASE_ORDER_LINE
      WHERE PURCHASE_ORDER = :WK_L_PURCHASE_ORDER AND
            PO_LINE = :WK_L_PO_LINE
      INTO :WK_FOUND;
   IF (WK_FOUND = 0) THEN
   BEGIN
      /* no line so insert it */
      INSERT INTO PURCHASE_ORDER_LINE (
         PURCHASE_ORDER,              
         PO_LINE,                    
         REQUISITION_NO,            
         PROD_ID,                  
         PO_LINE_QTY,            
         UNIT_PRICE,            
         UOM_ORDER,            
         PO_LINE_DUE_DATE,    
         PO_LINE_STATUS,     
         COMMENTS,          
         GST_VALUE,        
         GST_RATE,        
         GST_CODE,
         ORIGINAL_QTY)        
      VALUES (
         :WK_L_PURCHASE_ORDER,              
         :WK_L_PO_LINE,                    
         :WK_L_REQUISITION_NO,            
         :WK_L_PROD_ID       ,           
         :WK_L_PO_LINE_QTY   ,         
         :WK_L_UNIT_PRICE    ,        
         :WK_L_UOM_ORDER     ,       
         :WK_L_PO_LINE_DUE_DATE,    
         :WK_L_PO_LINE_STATUS,     
         :WK_L_COMMENTS      ,    
         :WK_L_GST_VALUE     ,   
         :WK_L_GST_RATE      ,  
         :WK_L_GST_CODE      ,
         :WK_L_PO_LINE_QTY            
 );       
   END
   ELSE
   BEGIN
      UPDATE PURCHASE_ORDER_LINE SET
         REQUISITION_NO = :WK_L_REQUISITION_NO,            
         PROD_ID =        :WK_L_PROD_ID       ,           
         PO_LINE_QTY =    :WK_L_PO_LINE_QTY   ,         
         UNIT_PRICE =     :WK_L_UNIT_PRICE    ,        
         UOM_ORDER =      :WK_L_UOM_ORDER     ,       
         PO_LINE_DUE_DATE = :WK_L_PO_LINE_DUE_DATE,    
         PO_LINE_STATUS =   :WK_L_PO_LINE_STATUS,     
         COMMENTS =         :WK_L_COMMENTS    ,      
         GST_VALUE =      :WK_L_GST_VALUE     ,   
         GST_RATE =       :WK_L_GST_RATE      ,  
         GST_CODE =       :WK_L_GST_CODE         
      WHERE PURCHASE_ORDER = :WK_L_PURCHASE_ORDER AND           
         PO_LINE =        :WK_L_PO_LINE;                    
   END
END