-- B1 DEPENDS: AFTER:PT:PROCESS_END

create procedure TmSp_ZZZ_VIEW_B1_SnBOITL1OnhCnlView
LANGUAGE SQLSCRIPT 
SQL SECURITY INVOKER
AS
cnt int;
sqlStr nvarchar(20000);
BEGIN
	select count (*) into cnt from "PUBLIC"."VIEWS" where "VIEW_NAME" = 'B1_SnBOITL1OnhCnlView' AND "SCHEMA_NAME" = CURRENT_SCHEMA;
	if :cnt > 0 then
		sqlStr := ('drop view "B1_SnBOITL1OnhCnlView"');
		exec (:sqlStr);
	end if;
	
	sqlStr := 'CREATE VIEW "B1_SnBOITL1OnhCnlView" ( "LogEntry",
	 "ItemCode",
	 "SysNumber",
	 "Quantity",
	 "AllocQty",
	 "MdAbsEntry",
	 "ReleaseQty",
	 "PickedQty",
	 "OrderedQty" ) AS SELECT
	 T0."LogEntry",
	 T0."ItemCode",
	 T0."SysNumber",
	 COALESCE(T3."Quantity",
	 0) + T0."Quantity" AS "Quantity",
	 T0."AllocQty",
	 T0."MdAbsEntry",
	 T0."ReleaseQty",
	 T0."PickedQty",
	 T0."OrderedQty" 
FROM "ITL1" T0 
INNER JOIN "OITL" T1 ON T1."LogEntry" = T0."LogEntry" 
AND T1."StockEff" = 1 
AND (T1."DocType" <> T1."BaseType" 
	OR T1."DocType" = 165 
	OR T1."DocType" = 163 ) 
LEFT OUTER JOIN "OITL" T2 ON T2."BaseType" = T1."DocType" 
AND T2."BaseEntry" = T1."DocEntry" 
AND T2."BaseLine" = T1."DocLine" 
AND T2."BSubLineNo" = T1."SubLineNum" 
AND T2."BaseType" = T2."DocType" 
AND T1."DocType" <> 165 
AND T1."DocType" <> 163 
LEFT OUTER JOIN "ITL1" T3 ON T2."LogEntry" = T3."LogEntry" 
AND T0."MdAbsEntry" = T3."MdAbsEntry" 
WHERE T3."Quantity" IS NULL 
OR ((T0."Quantity" > (0 ) 
		AND T0."Quantity" > T3."Quantity" * -1 ) 
	OR (T0."Quantity" < (0 ) 
		AND T0."Quantity" < T3."Quantity" * -1 )) WITH READ ONLY';

  exec (:sqlStr);
	--ins_msg_proc(:sqlStr);
END;