Missing Retail Tender in SAPB1 Warehouse : Generate Mappings

Missing Retail Tender in SAPB1 Warehouse : Generate Mappings

This KB is for Internal Purpose Only for CitiXsys

Environment:

iVend 6.6 with SAPB1 9.2

Problem statement: 

There are some warehouse in SAPB1 where iVend Addon Screen "Retail Tender" doesn't display the available tenders to be mapped.

Symptoms :

There are some warehouse in SAPB1 where iVend Addon Screen "Retail Tender" doesn't display the available tenders to be mapped.

Resolution/Work Around: 

It is not reproducible but below SQL Queries can be used to generate the mapping 
Note :  These SQL Queries will work only if warehouse is not mapped COMPLETELY in SAPB1 Retail Tender Screen

Case1 : SAPB1 HANA 
Step 1 : Execute the query on SAP database
         CREATE SEQUENCE seq START WITH 1;
Step 2 : Replace the T4 with actual whscode in beloew query and execute
DO BEGIN 
 DECLARE RecordCount int;
 DECLARE WhsCode nvarchar(100);
 WhsCode:= 'T4';
 SELECT COUNT(1) into RecordCount From "@CXS_RETEND" Where "U_CXS_WHCD" = :WhsCode;
 IF(:RecordCount <= 0) THEN
  DECLARE NewCode int;
  Select IFNULL(MAX(TO_INT("Code")),0) into NewCode From "@CXS_RETEND";

  insert into "@CXS_RETEND"("Code", "Name", "U_CTX_SORT", "U_CXS_RETD", "U_CXS_MPTN", "U_CXS_MPTC", "U_CXS_WHCD","U_CXS_GLAC","U_CXS_GLFC","U_CXS_CUCD")  
  select seq.NEXTVAL + NewCode, seq.NEXTVAL + NewCode, seq.NEXTVAL + NewCode, "U_CXS_RETD", "U_CXS_MPTN", "U_CXS_MPTC", 
  :WhsCode, "U_CXS_GLAC","U_CXS_GLFC","U_CXS_CUCD" 
  from "@CXS_RETEND" 
  where IFNULL("U_CXS_WHCD",'') = '' and "U_CXS_TETP" <> '3'; 
 
 END IF; 
END;

Case2 : SAPB1 SQL Server
BEGIN 
    DECLARE @RecordCount int;
    DECLARE @WhsCode nvarchar(100);
    SET @WhsCode = 'T4';
    SELECT @RecordCount= COUNT(1) From "@CXS_RETEND" Where "U_CXS_WHCD" = @WhsCode;
    IF(@RecordCount <= 0) 
    BEGIN 
        DECLARE @NewCode int;
        Select @NewCode  = ISNULL(MAX(CAST(Code as int)),0) From "@CXS_RETEND";

        insert into "@CXS_RETEND"("Code", "Name", "U_CTX_SORT", "U_CXS_RETD", "U_CXS_MPTN", "U_CXS_MPTC", "U_CXS_WHCD","U_CXS_GLAC","U_CXS_GLFC","U_CXS_CUCD")  
        select    @NewCode + ROW_NUMBER() Over (Order By "U_CXS_WHCD"), 
                @NewCode + ROW_NUMBER() Over (Order By "U_CXS_WHCD"),
                @NewCode + ROW_NUMBER() Over (Order By "U_CXS_WHCD"),
        "U_CXS_RETD", "U_CXS_MPTN", "U_CXS_MPTC", 
                @WhsCode, "U_CXS_GLAC","U_CXS_GLFC","U_CXS_CUCD" 
        from "@CXS_RETEND" 
        where ISNULL("U_CXS_WHCD",'') = '' and "U_CXS_TETP" <> '3';  
    END; 
END;

Recommendations /Suggestion:

Suggest to apply such tender mapping with Support team