Problem in query for SAP B1 HANA

[former_member244844]

The Procedure is:

CREATE PROCEDURE "Database".Acc_SP_Mov_Con_Aux

(

IN Fec1 timestamp,

IN Fec2 timestamp,

IN Account NVARCHAR(40),

IN Project NVARCHAR(40),

IN OcrCode NVARCHAR(16)

)

LANGUAGE SQLSCRIPT

--WITH ENCRYPTION

AS

BEGIN

/*Factura --> Entrega*/

SELECT

'1. Movimiento de Inventario'"Tipo Mov",

T0."TransId""N° Asiento",

T0."Account""Cuenta",

T0."Debit""Debe",

T0."Credit""Haber",

T0."Project""Proyecto",

T0."ProfitCode""Línea Negocio",

Case T0."TransType"

When'13'Then (SELECTCase T10."DocSubType"

When '--' Then (Case When T10."isIns" = 'Y' Then 'Factura Reserva Clientes' Else 'Factura de Clientes' End)

When'DN'Then'Nota de Débito Clientes'

When'IE'Then'Factura Exenta Clientes'

When'IB'Then'Boleta'

When'EB'Then'Boleta Exenta'

When'IX'Then'Factura Exportación'

When'IR'Then'A/R Invoice & Receipt'

When'RI'Then'Factura Reserva'

Else'Otro'

EndFROM OINV T10 WHERE T10."TransId" = T0."TransId")

When'15'Then'Entrega a Clientes'

When'16'Then'Devolución a Clientes'

When'203'Then'Factura de Anticipo Clientes'

When'14'Then'Nota de Crédito Clientes'

When'20'Then'Entrada Mcias. OP'

When'21'Then'Devolución a Proveedores'

When'204'Then'Solicitud de Anticipo de Proveedores'

When'18'Then'Factura de Proveedores'

When'19'Then'Nota de Créditod Proveedores'

When'30'Then'Asiento Manual'

When'46'Then'Pagos Efectuados'

When'24'Then'Pagos Recibidos'

When'321'Then'Reconciliación interna'

When'67'Then'Transferencia'

When'59'Then'Entrada de Inventario'

When'60'Then'Salida de Inventario'

When'10000071'Then'Contabilización de Stock'

Else T0."TransType"End"Tipo de Documento",

'' "--> Factura y otros <> Entregas/Entrada -->",

CaseWhen T0."TransType" = 15 OR T0."TransType" = 16 OR T0."TransType" = 20 OR T0."TransType" = 21

Then 0

Else T0."BaseRef"End"N° Interno Doc",

CaseWhen T0."TransType" = 15 OR T0."TransType" = 16 OR T0."TransType" = 20 OR T0."TransType" = 21

Then 0

Else T3."FolioNum"End"Folio",

CaseWhen T0."TransType" = 15 OR T0."TransType" = 16 OR T0."TransType" = 20 OR T0."TransType" = 21

Then''

Else T0."RefDate"End"Fecha",

T4."ItemCode""Código Artículo",

T4."Dscription""Nombre Artículo",

T4."InQty" - T4."OutQty""Cantidad",

T4."InvntryUom""Unidad de Medida",

T4."AvgPrice""PMP Unitario",

T4."ItmsGrpNam""Grupo",

T1."U_NAME""Usuario",

T4."Price""Precio Venta",

T4."CalcPrice""Costo",

T4."Price" - T4."CalcPrice""Margen",

CaseWhen T4."Price" <> 0 Then (T4."Price" - T4."CalcPrice") / T4."Price"Else 0 End"%",

T4."CardCode""Cod Cliente",

T4."CardName""Nombre Cliente"

,'' "--> Entrega -->",

Case T0."TransType"

When'13'ThenIfNull(T7."DocNum", 0)

When'14'Then 0

When'18'Then 0

When'19'Then 0

When'15'Then T0."BaseRef"

When'16'Then T0."BaseRef"

When'20'Then T0."BaseRef"

When'21'Then T0."BaseRef"

Else 0 End"N° Doc",

Case T0."TransType"

When'13'ThenIfNull(T7."FolioNum", 0)

When'14'Then 0

When'18'Then 0

When'19'Then 0

When'15'Then T3."FolioNum"

When'16'Then T3."FolioNum"

When'20'Then T3."FolioNum"

When'21'Then T3."FolioNum"

Else 0 End"N° Folio",

Case T0."TransType"

When'13'ThenIfNull(T7."DocDate", '')

When'14'Then 0

When'18'Then 0

When'19'Then 0

When'15'Then T0."RefDate"

When'16'Then T0."RefDate"

When'20'Then T0."RefDate"

When'21'Then T0."RefDate"

Else''End"Fecha Contabilización",

Case T0."TransType"

When'13'ThenIfNull(T7."DocDueDate", '')

When'14'Then 0

When'18'Then 0

When'19'Then 0

When'15'Then T0."DueDate"

When'16'Then T0."DueDate"

When'20'Then T0."DueDate"

When'21'Then T0."DueDate"

Else''End"Fecha Entrega",

Case T0."TransType"

When'13'Then (CaseWhen T7."DocNum"IsNotNullThenCaseWhenIfNull(T7."DocStatus", '') = 'O'Then'Abierto'Else'Cerrado'EndEnd)

When'18'Then (CaseWhen T7."DocNum"IsNotNullThenCaseWhenIfNull(T7."DocStatus", '') = 'O'Then'Abierto'Else'Cerrado'EndEnd)

When'15'Then (SELECTCase T10."DocStatus"When'O'Then'Abierto'Else'Cerrado'EndFROM ODLN T10 WHERE T10."DocEntry" = T0."CreatedBy")

When'16'Then (SELECTCase T10."DocStatus"When'O'Then'Abierto'Else'Cerrado'EndFROM ORDN T10 WHERE T10."DocEntry" = T0."CreatedBy")

When'20'Then (SELECTCase T10."DocStatus"When'O'Then'Abierto'Else'Cerrado'EndFROM OPDN T10 WHERE T10."DocEntry" = T0."CreatedBy")

When'21'Then (SELECTCase T10."DocStatus"When'O'Then'Abierto'Else'Cerrado'EndFROM ORPD T10 WHERE T10."DocEntry" = T0."CreatedBy")

Else''End"Status"

,'' "--> OV/OC -->",

CaseWhen T6."DocNum"IsNotNullOR T8."DocNum"IsNotNullThen'Orden de Venta'

Else''End"Tipo OC OV",

IfNull(T6."DocNum", IfNull(T8."DocNum", 0)) "N° documento",

IfNull(T6."DocTotal", IfNull(T8."DocTotal", 0)) "Valor OV $",

''"Línea Negocio NV", ''"Proyecto NV",

CaseWhen T6."DocNum"IsNotNullThenCaseWhenIfNull(T6."DocStatus", '') = 'O'Then'Abierto'Else'Cerrado'End

ElseCaseWhen T8."DocNum"IsNotNullThenCaseWhenIfNull(T8."DocStatus", '') = 'O'Then'Abierto'Else'Cerrado'End

Else''EndEnd"Status OV/OC"

,'' "--> OF -->",

''"N° Doc OF",

''"Cod Artículo",

''"Nombre Artículo OF",

''"Cantidad Requerida",

''"Cantidad Consumida"

FROM"JDT1" T0

INNERJOIN"OJDT" T3 ON T0."TransId" = T3."TransId"

LEFTOUTERJOIN"OUSR" T1 ON T0."UserSign" = T1."USERID"

LEFTOUTERJOIN (SELECT T0."TransId"AS"TransId", T0."TransRowId"AS"TransRowId", MAX(T0."ReconNum") AS"MaxReconNum"FROM"ITR1" T0 GROUPBY T0."TransId", T0."TransRowId") T2

ON T0."TransId" = T2."TransId"AND T0."Line_ID" = T2."TransRowId"

LEFTOUTERJOIN

(SELECT T0."ItemCode", T0."Dscription", T0."InQty", T0."OutQty", T0."Price", T0."CalcPrice", T0."CardCode", T0."CardName", T0."CreatedBy", T0."TransType", T1."InvntryUom",

T2."ItmsGrpNam", T1."AvgPrice", T0."DocLineNum"

FROM OINM T0 INNERJOIN OITM T1 ON T0."ItemCode" = T1."ItemCode"INNERJOIN OITB T2 ON T1."ItmsGrpCod" = T2."ItmsGrpCod") T4

ON T0."CreatedBy" = T4."CreatedBy"AND T0."TransType" = T4."TransType"

INNERJOIN"Acc_OINV_01" T5 ON T5."TransId" = T0."TransId"AND T4."DocLineNum" = T5."LineNum"

LEFTJOIN"Acc_ODLN_01" T7 ON T7."DocEntry" = T5."BaseEntry"AND T7."ObjType" = T5."BaseType"AND T7."ItemCode" = T5."ItemCode"AND T5."BaseLine" = T7."LineNum"--Entrega

LEFTJOIN"Acc_ORDR_01" T6 ON T6."DocEntry" = T5."BaseEntry"AND T6."ObjType" = T5."BaseType"AND T6."ItemCode" = T5."ItemCode"AND T5."BaseLine" = T6."LineNum"--Entrega

LEFTJOIN"Acc_ORDR_01" T8 ON T8."DocEntry" = T7."BaseEntry"AND T8."ObjType" = T7."BaseType"AND T8."ItemCode" = T7."ItemCode"AND T7."BaseLine" = T8."LineNum"--Entrega

WHERE (T0."Project" = :Project OR :Project = '')

AND (T0."Account" = :Account OR :Account = '')

AND (T0."ProfitCode" = :OcrCode OR :OcrCode = '')

AND T0."RefDate"BETWEEN :Fec1 AND :Fec2

AND T0."TransType"in (13)

ORDERBY 1,3;

END;

When I run the query from SAP using parameters, the query does not display information

/*SELECT Top 1 T0."RefDate" FROM "OJDT" T0 WHERE T0."RefDate" = [%0];*/

/*SELECT Top 1 T0."RefDate" FROM "OJDT" T1 WHERE T0."RefDate" = [%1];*/

CALL "Database".Acc_SP_Mov_Con_Aux ([%0], [%1], '', '', '');

If execute without parameters, the query show results

CALL "Database".Acc_SP_Mov_Con_Aux ('20010101', '20180110' , '', '', '');