SAP B1 For HANA 序列号/批次交易流水

实现脚本：

DROP VIEW "IPS_CC_InvtryTransFlow";

CREATE VIEW "IPS_CC_InvtryTransFlow"

AS

SELECT

T0."LogEntry",

T0."ItemCode",

T0."ItemName",

T0."DocNum",

T0."DocEntry",

T0."DocLine",

T0."DocType",

ABS(T1."Quantity") AS "IOQuantity",

CASE WHEN T1."Quantity">0 THEN 'I' ELSE 'O' END AS "IOType",

CASE WHEN T1."Quantity">0 THEN '入' ELSE '出' END AS "IODirt",

CASE WHEN T1."Quantity">0

   THEN IO."LocCode" 

   ELSE NULL

   END AS "FromWhsCode", 

CASE WHEN T1."Quantity">0

   THEN IOW."WhsName" 

   ELSE NULL

   END AS "FromWhsName", 

T0."LocCode" AS "WhsCode",

T2."WhsName",

CASE WHEN T1."Quantity">0

   THEN NULL

   ELSE IO."LocCode" 

   END AS "ToWhsCode", 

CASE WHEN T1."Quantity">0

   THEN NULL

   ELSE IOW."WhsName" 

   END AS "ToWhsName",  

CASE WHEN IT."ManSerNum"='Y'

   THEN SR."MnfSerial"

   ELSE NULL END AS "MnfSerial", 

CASE WHEN IT."ManSerNum"='Y' THEN SR."DistNumber"

   WHEN IT."ManBtchNum"='Y' THEN BT."DistNumber"

   ELSE NULL END AS "DistNumber",

CASE WHEN IT."ManSerNum"='Y'

   THEN SR."LotNumber"

   ELSE NULL END "LotNumber",

T1."Quantity",

IT."ManSerNum",

IT."ManBtchNum",

CAST(T0."CreateDate" AS NVARCHAR(10))||' '||TO_TIME(LPAD(T0."CreateTime",6,'0'),'HHMISS') AS "CreateTime"

FROM "OITL" T0

INNER JOIN "OITM" IT ON IT."ItemCode"=T0."ItemCode"

INNER JOIN "ITL1" T1 ON T1."LogEntry"=T0."LogEntry"

INNER JOIN "OWHS" T2 ON T2."WhsCode"=T0."LocCode"

LEFT JOIN "OSRN" SR ON SR."ItemCode"=T0."ItemCode" AND SR."SysNumber"=T1."SysNumber"

LEFT JOIN "OBTN" BT ON BT."ItemCode"=T0."ItemCode" AND BT."SysNumber"=T1."SysNumber"

LEFT JOIN "OITL" IO ON IO."ItemCode"=T0."ItemCode" AND IO."DocEntry"=T0."DocEntry"

               AND IO."DocLine"=T0."DocLine" AND IO."DocType"=T0."DocType" 

               AND IO."LogEntry"<>T0."LogEntry"

LEFT JOIN "OWHS" IOW ON IOW."WhsCode"=IO."LocCode"

ORDER BY T0."LogEntry";