SAP Business One Transaction Notification in SAP HANA Database Various cases Query Examples

Following are the query example for SAP Business One Transaction Notification in SAP  HANA Database, hope it will help you.

------BLOCK RETURN WITH  GREATER THAN ZERO COST -----------

----------------------------------------------------------------

/*IF (:transaction_type ='A' OR :transaction_type ='U') AND :object_type = '16'  THEN  

SELECT (SELECT 1 FROM ORDN INNER JOIN RDN1 ON ORDN."DocEntry" = RDN1."DocEntry" 

INNER JOIN OITM ON RDN1."ItemCode" = OITM."ItemCode" 

WHERE  RDN1."StockPrice" <> '0' 

AND ORDN."DocEntry" = :list_of_cols_val_tab_del) 

INTO temp_var_0 FROM DUMMY;

IF :temp_var_0 > 0 THEN 

SELECT 123, 'BK - Cannot return  Goods with a cost greater than zero. The item cost must be zero'  FROM DUMMY;

Return;

END IF;

END IF;*/

-------------------------------------------------------------------------------------------------------------------

------BLOCK  Date of Loading should not be greater than current date and should not be less then posting date -----------

----------------------------------------------------------------

IF (:transaction_type ='A' OR :transaction_type ='U') AND :object_type = '17'  THEN  

SELECT (SELECT 1 AS "CNT" FROM ORDR INNER JOIN RDR1 ON RDR1."DocEntry" = ORDR."DocEntry" 

WHERE ORDR."DocEntry" = :list_of_cols_val_tab_del AND (IFNULL(RDR1."U_DOL",NOW()) > NOW() )) 

OR RDR1."U_DOL" < ORDR."DocDate")) 

INTO temp_var_0 FROM DUMMY;

IF :temp_var_0 > 0 THEN 

SELECT 3, 'BK - You cannot enter the Date of Loading Greater than Today Date and Less then Document Date.'  FROM DUMMY;

Return;

END IF;

END IF;

-------------------------------------------------------------------------------------------------------------------

------BLOCK  GOODS RECIEPT PRICE AND PURCHASE ORDER PRICE SHOULD NOT BE DIFFERENT--------

----------------------------------------------------------------

IF (:transaction_type ='A' OR :transaction_type ='U') AND :object_type = '20'  THEN

SELECT (SELECT 1 FROM PDN1 INNER JOIN POR1 ON PDN1."BaseEntry" = POR1."DocEntry" AND PDN1."BaseLine" = POR1."LineNum" AND PDN1."Price" > POR1."Price" 

INNER JOIN OPDN ON PDN1."DocEntry" = OPDN."DocEntry" 

 WHERE PDN1."DocEntry" = :list_of_cols_val_tab_del) 

INTO temp_var_0 FROM DUMMY;

IF :temp_var_0 > 0 THEN 

SELECT 5, 'GRN price is greater than PO price'  FROM DUMMY;

Return;

END IF;

END IF;

-------------------------------------------------------------------------------------------------------------------