-- B1 DEPENDS: AFTER:SP:BEM_CLEAR_TRANS_REP AFTER:PT:PROCESS_END

CREATE PROCEDURE BEM_PREPARE_TRANS_REP (
	-- input parameters
	REP_ID NVARCHAR (36),			-- replication id from bem
	PERIOD_ENTRY INT				-- AbsEntry in b1 table OBEM
)
LANGUAGE SQLSCRIPT 
SQL SECURITY INVOKER

AS
BEGIN

DECLARE COUNTER INT;
DECLARE BEM_SCOPE_KEY NVARCHAR (36);	-- scope key
DECLARE START_DATE DATE;				-- start date of the period
DECLARE END_DATE DATE;					-- start date of the period
DECLARE BEM_DIMENSION INT;

-- 1. Clear old transaction data in BEM2
CALL BEM_CLEAR_TRANS_REP (:REP_ID);


-- 2. Update LastRepId in OBEM
SELECT COUNT(*) INTO COUNTER FROM OBEM
	WHERE "AbsEntry" = :PERIOD_ENTRY;
		
-- SELECT :COUNTER AS "No. of Periods Found" FROM DUMMY;

IF :COUNTER = 1 THEN
	SELECT "ScopeKey", "StartDate", "EndDate" INTO BEM_SCOPE_KEY, START_DATE, END_DATE
		FROM OBEM
		WHERE "AbsEntry" = :PERIOD_ENTRY;
	UPDATE OBEM SET "LastRepId" = :REP_ID
		WHERE "AbsEntry" = :PERIOD_ENTRY;
	
-- SELECT :BEM_SCOPE_KEY AS "BEM scope key", :START_DATE AS "Start Date", :END_DATE AS "End Date" FROM DUMMY;

-- Set Dimension of BEM
SELECT IFNULL("Dimension", 0) INTO BEM_DIMENSION FROM BEM1;
 
-- 3. Map ItemCode to Standard UOM
CREATE COLUMN TABLE ITEM_UOM AS (
SELECT
	II."ItemCode",
	II."IUoMEntry",
	CASE WHEN II."WeightKGM" > 0 THEN II."WeightKGM"
		WHEN UUU."UomEntry" IS NOT NULL THEN UUU."UomFactor"
		ELSE 1 END AS "UomFactor",
	CASE WHEN II."WeightKGM" > 0 THEN 'KGM'
		WHEN UUU."UomEntry" IS NOT NULL THEN UUU."UomStandard"
		ELSE 'EA' END AS "UomStandard"
FROM
(
	SELECT 
		I."ItemCode",
		I."IUoMEntry",
		(IFNULL(I."IWeight1" * W1."WightInMG", 0) + IFNULL(I."IWeight2" * W2."WightInMG", 0)) / 1000000 AS "WeightKGM" -- / 1000000 to get KG
	FROM OITM I
		LEFT OUTER JOIN OWGT W1
		ON I."IWght1Unit" = W1."UnitCode"
		LEFT OUTER JOIN OWGT W2
		ON I."IWght2Unit" = W2."UnitCode"
) II
LEFT OUTER JOIN
(
	SELECT
		"UomEntry",
		CASE WHEN "WeightKGM" > 0 THEN "WeightKGM"
			WHEN "VolumeInM3" > 0 THEN "VolumeInM3"	
			WHEN "LengthMTR" > 0 AND "WidthMTR" > 0 AND "HeightMTR" > 0 THEN "LengthMTR" * "WidthMTR" * "HeightMTR"	-- 1,1,1
			WHEN "LengthMTR" > 0 AND "WidthMTR" > 0 AND "HeightMTR" = 0 THEN "LengthMTR" * "WidthMTR"					-- 1,1,0
			WHEN "LengthMTR" > 0 AND "WidthMTR" = 0 AND "HeightMTR" > 0 THEN "LengthMTR" * "HeightMTR"					-- 1,0,1
			WHEN "LengthMTR" > 0 AND "WidthMTR" = 0 AND "HeightMTR" = 0 THEN "LengthMTR"								-- 1,0,0
			WHEN "LengthMTR" = 0 AND "WidthMTR" > 0 AND "HeightMTR" > 0 THEN "WidthMTR" * "HeightMTR"					-- 0,1,1
			WHEN "LengthMTR" = 0 AND "WidthMTR" > 0 AND "HeightMTR" = 0 THEN "WidthMTR"								-- 0,1,0
			WHEN "LengthMTR" = 0 AND "WidthMTR" = 0 AND "HeightMTR" > 0 THEN "HeightMTR"								-- 0,0,1
			ELSE 1 END AS "UomFactor",
		CASE WHEN "WeightKGM" > 0 THEN 'KGM'
			WHEN "VolumeInM3" > 0 THEN 'MTQ'	
			WHEN "LengthMTR" > 0 AND "WidthMTR" > 0 AND "HeightMTR" > 0 THEN 'MTQ'	-- 1,1,1
			WHEN "LengthMTR" > 0 AND "WidthMTR" > 0 AND "HeightMTR" = 0 THEN 'MTK'	-- 1,1,0
			WHEN "LengthMTR" > 0 AND "WidthMTR" = 0 AND "HeightMTR" > 0 THEN 'MTK'	-- 1,0,1
			WHEN "LengthMTR" > 0 AND "WidthMTR" = 0 AND "HeightMTR" = 0 THEN 'MTR'	-- 1,0,0
			WHEN "LengthMTR" = 0 AND "WidthMTR" > 0 AND "HeightMTR" > 0 THEN 'MTK'	-- 0,1,1
			WHEN "LengthMTR" = 0 AND "WidthMTR" > 0 AND "HeightMTR" = 0 THEN 'MTR'	-- 0,1,0
			WHEN "LengthMTR" = 0 AND "WidthMTR" = 0 AND "HeightMTR" > 0 THEN 'MTR'	-- 0,0,1
			ELSE 'EA' END AS "UomStandard"
	FROM	
	(
		SELECT
			U."UomEntry",
			(IFNULL(U."Weight1" * W1."WightInMG", 0) + IFNULL(U."Weight2" * W2."WightInMG", 0)) / 1000000 AS "WeightKGM", -- / 1000000 to get KG
			IFNULL(U."Volume" * V."SizeInMM" * V."SizeInMM" * V."SizeInMM", 0) / 1000000000 AS "VolumeInM3", --  / 1000000000 to get M3
			(IFNULL(U."Length1" * LL1."SizeInMM", 0) + IFNULL(U."length2" * LL2."SizeInMM", 0)) / 1000 AS "LengthMTR", -- / 1000 to get MTR
			(IFNULL(U."Width1" * LW1."SizeInMM", 0) + IFNULL(U."Width2" * LW2."SizeInMM", 0)) / 1000 AS "WidthMTR", -- / 1000 to get MTR
			(IFNULL(U."Height1" * LH1."SizeInMM", 0) + IFNULL(U."Height2" * LH2."SizeInMM", 0)) / 1000 AS "HeightMTR" -- / 1000 to get MTR
		FROM OUOM U
		LEFT OUTER JOIN OWGT W1
		ON U."WghtUnit" = W1."UnitCode"
		LEFT OUTER JOIN OWGT W2
		ON U."Wght2Unit" = W2."UnitCode"
		LEFT OUTER JOIN OLGT V
		ON U."VolUnit" = V."UnitCode"
		LEFT OUTER JOIN OLGT LL1
		ON U."Len1Unit" = LL1."UnitCode"
		LEFT OUTER JOIN OLGT LL2
		ON U."Len2Unit" = LL2."UnitCode"
		LEFT OUTER JOIN OLGT LW1
		ON U."Wdth1Unit" = LW1."UnitCode"
		LEFT OUTER JOIN OLGT LW2
		ON U."Wdth2Unit" = LW2."UnitCode"
		LEFT OUTER JOIN OLGT LH1
		ON U."Hght1Unit" = LH1."UnitCode"
		LEFT OUTER JOIN OLGT LH2
		ON U."Hght2Unit" = LH2."UnitCode"
	) UU
) UUU
ON II."IUoMEntry" = UUU."UomEntry"
);


-- 4. Insert Data into BEM2
-- 4-01. 604, Opening Stock
INSERT INTO BEM2 SELECT
	"RowNumber", "BEMDocType", "BEMDocName", "ScopeKey", "RepId",
	"StartDate", "EndDate", "CreateDate", "B1ObjCode",
	"ItemCode", "WhseCode", "UomCode",
	"InQty", "OutQty", "Quantity",
	"VendorCode", "WOREntry", "RoleCode",
	"RscCode", "RscQty", "RscUomCode",
	"Project", "ToWhseCode", "CostCenter"
FROM (
	SELECT ROW_NUMBER() OVER 
		(ORDER BY "ItemCode", "WhseCode", "UomCode") AS "RowNumber",
		'604' AS "BEMDocType",
		'Opening Stock' AS "BEMDocName",
		:BEM_SCOPE_KEY AS "ScopeKey",
		:REP_ID AS "RepId",
		:START_DATE AS "StartDate",
		:END_DATE AS "EndDate",
		CURRENT_DATE AS "CreateDate",
		"B1ObjCode",
		"ItemCode",
		"WhseCode",
		"UomCode",
		"InQty",
		"OutQty",
		"Quantity",
		'' AS "VendorCode",
		'' AS "WOREntry",
		'' AS "RoleCode",
		'' AS "RscCode",
		0 AS "RscQty",
		'' AS "RscUomCode",
		"Project",
		'' AS "ToWhseCode",
		'' AS "CostCenter"
	FROM (
		SELECT
			"ObjType" AS "B1ObjCode",
			"ItemCode",
			"WhsCode" AS "WhseCode",
			"UomStandard" AS "UomCode",
			"Project",
			SUM("InQty") AS "InQty",
			SUM("OutQty") AS "OutQty",
			SUM("InvQty") AS "InvQty",
			SUM("InvQty" * "UomFactor") AS "Quantity"
		FROM (	
			SELECT 
				T0."ObjType",
				T1."ItemCode",
				T1."WhsCode",
				IFNULL(T1."Project", '') AS "Project",
				T1."Quantity" AS "InQty",
				0 AS "OutQty",
				T1."Quantity" - 0 AS "InvQty",
				I."UomFactor",
				I."UomStandard"
			FROM OIQI T0
				INNER JOIN IQI1 T1 ON T0."DocEntry" = T1."DocEntry"
				INNER JOIN ITEM_UOM I ON T1."ItemCode" = I."ItemCode"
			WHERE T0."DocDate" >= :START_DATE AND T0."DocDate" <= :END_DATE
		) ORIGIN
		GROUP BY
			"ObjType",
			"ItemCode",
			"WhsCode",
			"UomStandard",
			"Project"
	) GRP		
) IQI;
-- 4-01. 604, Opening Stock, end

-- 4-02. 301, Goods Receipt From Supplier
INSERT INTO BEM2 SELECT
	"RowNumber", "BEMDocType", "BEMDocName", "ScopeKey", "RepId",
	"StartDate", "EndDate", "CreateDate", "B1ObjCode",
	"ItemCode", "WhseCode", "UomCode",
	"InQty", "OutQty", "Quantity",
	"VendorCode", "WOREntry", "RoleCode",
	"RscCode", "RscQty", "RscUomCode",
	"Project", "ToWhseCode", "CostCenter"
FROM (
	SELECT ROW_NUMBER() OVER 
		(ORDER BY "ItemCode", "WhseCode", "UomCode", "VendorCode") AS "RowNumber",
		'301' AS "BEMDocType",
		'Goods Receipt From Supplier' AS "BEMDocName",
		:BEM_SCOPE_KEY AS "ScopeKey",
		:REP_ID AS "RepId",
		:START_DATE AS "StartDate",
		:END_DATE AS "EndDate",
		CURRENT_DATE AS "CreateDate",
		"B1ObjCode",
		"ItemCode",
		"WhseCode",
		"UomCode",
		"InQty",
		"OutQty",
		"Quantity",
		"VendorCode",
		'' AS "WOREntry",
		'' AS "RoleCode",
		'' AS "RscCode",
		0 AS "RscQty",
		'' AS "RscUomCode",
		"Project",
		'' AS "ToWhseCode",
		'' AS "CostCenter"
	FROM (
		SELECT
			"ObjType" AS "B1ObjCode",
			"ItemCode",
			"WhsCode" AS "WhseCode",
			"UomStandard" AS "UomCode",
			"CardCode" AS "VendorCode",
			"Project",
			SUM("InQty") AS "InQty",
			SUM("OutQty") AS "OutQty",
			SUM("InvQty") AS "InvQty",
			SUM("InvQty" * "UomFactor") AS "Quantity"
		FROM (
			-- GRPO
			SELECT 
				T0."ObjType",
				T0."CardCode",
				T1."ItemCode",
				T1."WhsCode",
				IFNULL(T1."Project", '') AS "Project",
				T1."InvQty" AS "InQty",
				0 AS "OutQty",
				T1."InvQty" - 0 AS "InvQty",
				I."UomFactor",
				I."UomStandard"
			FROM OPDN T0
				INNER JOIN PDN1 T1 ON T0."DocEntry" = T1."DocEntry"
				INNER JOIN ITEM_UOM I ON T1."ItemCode" = I."ItemCode"
			WHERE T0."DocType" = 'I'
				AND T1."ActDelDate" >= :START_DATE AND T1."ActDelDate" <= :END_DATE
			
			UNION ALL
			-- AP Invoice not base on GRPO
			SELECT 
				T0."ObjType",
				T0."CardCode",
				T1."ItemCode",
				T1."WhsCode",
				IFNULL(T1."Project", '') AS "Project",
				T1."InvQty" AS "InQty",
				0 AS "OutQty",
				T1."InvQty" - 0 AS "InvQty",
				I."UomFactor",
				I."UomStandard"
			FROM OPCH T0
				INNER JOIN PCH1 T1 ON T0."DocEntry" = T1."DocEntry"
				INNER JOIN ITEM_UOM I ON T1."ItemCode" = I."ItemCode"
			WHERE T0."DocType" = 'I' AND T0."isIns" = 'N'
				AND T1."BaseType" <> 20
				AND T1."ActDelDate" >= :START_DATE AND T1."ActDelDate" <= :END_DATE
		) ORIGIN
		GROUP BY
			"ObjType",
			"ItemCode",
			"WhsCode",
			"UomStandard",
			"CardCode",			
			"Project"
	) GRP		
) PDNPCH;
-- 4-02. 301, Goods Receipt From Supplier, end

-- 4-03. 343, Goods Return To Supplier
INSERT INTO BEM2 SELECT
	"RowNumber", "BEMDocType", "BEMDocName", "ScopeKey", "RepId",
	"StartDate", "EndDate", "CreateDate", "B1ObjCode",
	"ItemCode", "WhseCode", "UomCode",
	"InQty", "OutQty", "Quantity",
	"VendorCode", "WOREntry", "RoleCode",
	"RscCode", "RscQty", "RscUomCode",
	"Project", "ToWhseCode", "CostCenter"
FROM (
	SELECT ROW_NUMBER() OVER 
		(ORDER BY "ItemCode", "WhseCode", "UomCode", "VendorCode") AS "RowNumber",
		'343' AS "BEMDocType",
		'Goods Return To Supplier' AS "BEMDocName",
		:BEM_SCOPE_KEY AS "ScopeKey",
		:REP_ID AS "RepId",
		:START_DATE AS "StartDate",
		:END_DATE AS "EndDate",
		CURRENT_DATE AS "CreateDate",
		"B1ObjCode",
		"ItemCode",
		"WhseCode",
		"UomCode",
		"InQty",
		"OutQty",
		"Quantity",
		"VendorCode",
		'' AS "WOREntry",
		'' AS "RoleCode",
		'' AS "RscCode",
		0 AS "RscQty",
		'' AS "RscUomCode",
		"Project",
		'' AS "ToWhseCode",
		'' AS "CostCenter"
	FROM (
		SELECT
			"ObjType" AS "B1ObjCode",
			"ItemCode",
			"WhsCode" AS "WhseCode",
			"UomStandard" AS "UomCode",
			"CardCode" AS "VendorCode",
			"Project",
			SUM("InQty") AS "InQty",
			SUM("OutQty") AS "OutQty",
			SUM("InvQty") AS "InvQty",
			SUM("InvQty" * "UomFactor") AS "Quantity"
		FROM (
			-- Goods Return
			SELECT 
				T0."ObjType",
				T0."CardCode",
				T1."ItemCode",
				T1."WhsCode",
				IFNULL(T1."Project", '') AS "Project",
				0 AS "InQty",
				T1."InvQty" AS "OutQty",
				T1."InvQty" - 0 AS "InvQty",
				I."UomFactor",
				I."UomStandard"
			FROM ORPD T0
				INNER JOIN RPD1 T1 ON T0."DocEntry" = T1."DocEntry"
				INNER JOIN ITEM_UOM I ON T1."ItemCode" = I."ItemCode"
			WHERE T0."DocType" = 'I'
				AND T1."ActDelDate" >= :START_DATE AND T1."ActDelDate" <= :END_DATE
				
			UNION ALL
			-- AP Credit Memo with inventory movement
			SELECT 
				T0."ObjType",
				T0."CardCode",
				T1."ItemCode",
				T1."WhsCode",
				IFNULL(T1."Project", '') AS "Project",
				L."InQty" AS "InQty",
				L."OutQty" AS "OutQty",
				L."OutQty" - L."InQty" AS "InvQty",
				I."UomFactor",
				I."UomStandard"
			FROM ORPC T0
				INNER JOIN RPC1 T1 ON T0."DocEntry" = T1."DocEntry"
				INNER JOIN ITEM_UOM I ON T1."ItemCode" = I."ItemCode"
				INNER JOIN OIVL L ON T0."ObjType" = L."TransType" AND T0."DocEntry" = L."CreatedBy" 
					AND T1."LineNum" = L."DocLineNum"
			WHERE T1."ActDelDate" >= :START_DATE AND T1."ActDelDate" <= :END_DATE
		) ORIGIN
		GROUP BY
			"ObjType",
			"ItemCode",
			"WhsCode",
			"UomStandard",
			"CardCode",
			"Project"
	) GRP		
) PDNPCH;
-- 4-03. 343, Goods Return To Supplier, end

-- 4-04. 302, Customer Return
INSERT INTO BEM2 SELECT
	"RowNumber", "BEMDocType", "BEMDocName", "ScopeKey", "RepId",
	"StartDate", "EndDate", "CreateDate", "B1ObjCode",
	"ItemCode", "WhseCode", "UomCode",
	"InQty", "OutQty", "Quantity",
	"VendorCode", "WOREntry", "RoleCode",
	"RscCode", "RscQty", "RscUomCode",
	"Project", "ToWhseCode", "CostCenter"
FROM (
	SELECT ROW_NUMBER() OVER 
		(ORDER BY "ItemCode", "WhseCode", "UomCode") AS "RowNumber",
		'302' AS "BEMDocType",
		'Customer Return' AS "BEMDocName",
		:BEM_SCOPE_KEY AS "ScopeKey",
		:REP_ID AS "RepId",
		:START_DATE AS "StartDate",
		:END_DATE AS "EndDate",
		CURRENT_DATE AS "CreateDate",
		"B1ObjCode",
		"ItemCode",
		"WhseCode",
		"UomCode",
		"InQty",
		"OutQty",
		"Quantity",
		'' AS "VendorCode",
		'' AS "WOREntry",
		'' AS "RoleCode",
		'' AS "RscCode",
		0 AS "RscQty",
		'' AS "RscUomCode",
		"Project",
		'' AS "ToWhseCode",
		'' AS "CostCenter"
	FROM (
		SELECT
			"ObjType" AS "B1ObjCode",
			"ItemCode",
			"WhsCode" AS "WhseCode",
			"UomStandard" AS "UomCode",
			"Project",
			SUM("InQty") AS "InQty",
			SUM("OutQty") AS "OutQty",
			SUM("InvQty") AS "InvQty",
			SUM("InvQty" * "UomFactor") AS "Quantity"
		FROM (
			-- Return
			SELECT 
				T0."ObjType",
				T1."ItemCode",
				T1."WhsCode",
				IFNULL(T1."Project", '') AS "Project",
				T1."InvQty" AS "InQty",
				0 AS "OutQty",
				T1."InvQty" - 0 AS "InvQty",
				I."UomFactor",
				I."UomStandard"
			FROM ORDN T0
				INNER JOIN RDN1 T1 ON T0."DocEntry" = T1."DocEntry"
				INNER JOIN ITEM_UOM I ON T1."ItemCode" = I."ItemCode"
			WHERE T0."DocType" = 'I'
				AND T1."ActDelDate" >= :START_DATE AND T1."ActDelDate" <= :END_DATE

			UNION ALL
			-- AR Credit Memo with inventory movement
			SELECT 
				T0."ObjType",
				T1."ItemCode",
				T1."WhsCode",
				IFNULL(T1."Project", '') AS "Project",
				L."InQty" AS "InQty",
				L."OutQty" AS "OutQty",
				L."InQty" - L."OutQty" AS "InvQty",
				I."UomFactor",
				I."UomStandard"
			FROM ORIN T0
				INNER JOIN RIN1 T1 ON T0."DocEntry" = T1."DocEntry"
				INNER JOIN ITEM_UOM I ON T1."ItemCode" = I."ItemCode"
				INNER JOIN OIVL L ON T0."ObjType" = L."TransType" AND T0."DocEntry" = L."CreatedBy" 
					AND T1."LineNum" = L."DocLineNum"
			WHERE T1."ActDelDate" >= :START_DATE AND T1."ActDelDate" <= :END_DATE
		) ORIGIN
		GROUP BY
			"ObjType",
			"ItemCode",
			"WhsCode",
			"UomStandard",
			"Project"
	) GRP		
) RDNRIN;
-- 4-04. 302, Customer Return, end

-- 4-05. 341, Goods Issue For Customer
INSERT INTO BEM2 SELECT
	"RowNumber", "BEMDocType", "BEMDocName", "ScopeKey", "RepId",
	"StartDate", "EndDate", "CreateDate", "B1ObjCode",
	"ItemCode", "WhseCode", "UomCode",
	"InQty", "OutQty", "Quantity",
	"VendorCode", "WOREntry", "RoleCode",
	"RscCode", "RscQty", "RscUomCode",
	"Project", "ToWhseCode", "CostCenter"
FROM (
	SELECT ROW_NUMBER() OVER 
		(ORDER BY "ItemCode", "WhseCode", "UomCode") AS "RowNumber",
		'341' AS "BEMDocType",
		'Goods Issue For Customer' AS "BEMDocName",
		:BEM_SCOPE_KEY AS "ScopeKey",
		:REP_ID AS "RepId",
		:START_DATE AS "StartDate",
		:END_DATE AS "EndDate",
		CURRENT_DATE AS "CreateDate",
		"B1ObjCode",
		"ItemCode",
		"WhseCode",
		"UomCode",
		"InQty",
		"OutQty",
		"Quantity",
		'' AS "VendorCode",
		'' AS "WOREntry",
		'' AS "RoleCode",
		'' AS "RscCode",
		0 AS "RscQty",
		'' AS "RscUomCode",
		"Project",
		'' AS "ToWhseCode",
		'' AS "CostCenter"
	FROM (
		SELECT
			"ObjType" AS "B1ObjCode",
			"ItemCode",
			"WhsCode" AS "WhseCode",
			"UomStandard" AS "UomCode",
			"Project",
			SUM("InQty") AS "InQty",
			SUM("OutQty") AS "OutQty",
			SUM("InvQty") AS "InvQty",
			SUM("InvQty" * "UomFactor") AS "Quantity"
		FROM (
			-- Delivery
			SELECT 
				T0."ObjType",
				T1."ItemCode",
				T1."WhsCode",
				IFNULL(T1."Project", '') AS "Project",
				0 AS "InQty",
				T1."InvQty" AS "OutQty",
				T1."InvQty" - 0 AS "InvQty",
				I."UomFactor",
				I."UomStandard"
			FROM ODLN T0
				INNER JOIN DLN1 T1 ON T0."DocEntry" = T1."DocEntry"
				INNER JOIN ITEM_UOM I ON T1."ItemCode" = I."ItemCode"
			WHERE T0."DocType" = 'I'
				AND T1."ActDelDate" >= :START_DATE AND T1."ActDelDate" <= :END_DATE
			
			UNION ALL
			-- AR Invoice not based on Delivery
			SELECT 
				T0."ObjType",
				T1."ItemCode",
				T1."WhsCode",
				IFNULL(T1."Project", '') AS "Project",
				0 AS "InQty",
				T1."InvQty" AS "OutQty",
				T1."InvQty" - 0 AS "InvQty",
				I."UomFactor",
				I."UomStandard"
			FROM OINV T0
				INNER JOIN INV1 T1 ON T0."DocEntry" = T1."DocEntry"
				INNER JOIN ITEM_UOM I ON T1."ItemCode" = I."ItemCode"
			WHERE T0."DocType" = 'I' AND T0."isIns" = 'N'
				AND T1."BaseType" <> 15
				AND T1."ActDelDate" >= :START_DATE AND T1."ActDelDate" <= :END_DATE
		) ORIGIN
		GROUP BY
			"ObjType",
			"ItemCode",
			"WhsCode",
			"UomStandard",
			"Project"
	) GRP		
) DLNINV;
-- 4-05. 341, Goods Issue For Customer, end

-- 4-06. 344, Goods Issue For Production
INSERT INTO BEM2 SELECT
	"RowNumber", "BEMDocType", "BEMDocName", "ScopeKey", "RepId",
	"StartDate", "EndDate", "CreateDate", "B1ObjCode",
	"ItemCode", "WhseCode", "UomCode",
	"InQty", "OutQty", "Quantity",
	"VendorCode", "WOREntry", "RoleCode",
	"RscCode", "RscQty", "RscUomCode",
	"Project", "ToWhseCode", "CostCenter"
FROM (
	SELECT ROW_NUMBER() OVER 
		(ORDER BY "ItemCode", "WhseCode", "UomCode") AS "RowNumber",
		'344' AS "BEMDocType",
		'Goods Issue For Production' AS "BEMDocName",
		:BEM_SCOPE_KEY AS "ScopeKey",
		:REP_ID AS "RepId",
		:START_DATE AS "StartDate",
		:END_DATE AS "EndDate",
		CURRENT_DATE AS "CreateDate",
		"B1ObjCode",
		"ItemCode",
		"WhseCode",
		"UomCode",
		"InQty",
		"OutQty",
		"Quantity",
		'' AS "VendorCode",
		"WOREntry",
		'' AS "RoleCode",
		'' AS "RscCode",
		0 AS "RscQty",
		'' AS "RscUomCode",
		"Project",
		'' AS "ToWhseCode",
		'' AS "CostCenter"
	FROM (
		SELECT
			"ObjType" AS "B1ObjCode",
			"ItemCode",
			"WhsCode" AS "WhseCode",
			"UomStandard" AS "UomCode",
			"BaseEntry" AS "WOREntry",
			"Project",
			SUM("InQty") AS "InQty",
			SUM("OutQty") AS "OutQty",
			SUM("InvQty") AS "InvQty",
			SUM("InvQty" * "UomFactor") AS "Quantity"
		FROM (
			-- Goods Issue For Production (Issue Component for Standard and Special)
			SELECT 
				T0."ObjType",
				T1."ItemCode",
				T1."WhsCode",
				T1."BaseEntry",
				IFNULL(T1."Project", '') AS "Project",
				0 AS "InQty",
				T1."InvQty" AS "OutQty",
				T1."InvQty" - 0 AS "InvQty",
				I."UomFactor",
				I."UomStandard"
			FROM OIGE T0
				INNER JOIN IGE1 T1 ON T0."DocEntry" = T1."DocEntry"
				INNER JOIN ITEM_UOM I ON T1."ItemCode" = I."ItemCode"
				INNER JOIN OWOR P ON T1."BaseType" = P."ObjType"
					AND T1."BaseEntry" = P."DocEntry"
			WHERE T1."BaseType" = 202 -- 202 is Production Order
				AND T1."ItemType" = 4 -- 4 is for Item
				AND (P."Type" = 'S' OR P."Type" = 'P') -- standard or special production order, not disasembly
				AND T1."BaseLine" >= 0 -- production order components
				AND T0."DocDate" >= :START_DATE AND T0."DocDate" <= :END_DATE
			
			UNION ALL
			-- Goods Issue For Production (Issue Parent for Disassembly)
			SELECT 
				T0."ObjType",
				T1."ItemCode",
				T1."WhsCode",
				T1."BaseEntry",
				IFNULL(T1."Project", '') AS "Project",
				0 AS "InQty",
				T1."InvQty" AS "OutQty",
				T1."InvQty" - 0 AS "InvQty",
				I."UomFactor",
				I."UomStandard"
			FROM OIGE T0
				INNER JOIN IGE1 T1 ON T0."DocEntry" = T1."DocEntry"
				INNER JOIN ITEM_UOM I ON T1."ItemCode" = I."ItemCode"
				INNER JOIN OWOR P ON T1."BaseType" = P."ObjType"
					AND T1."BaseEntry" = P."DocEntry"
			WHERE T1."BaseType" = 202 -- 202 is Production Order
				AND T1."ItemType" = 4 -- 4 is for Item
				AND P."Type" = 'D' -- disassembly production order
				AND (T1."BaseLine" < 0 OR T1."BaseLine" IS NULL) -- disassembly parent
				AND T0."DocDate" >= :START_DATE AND T0."DocDate" <= :END_DATE
			
			UNION ALL
			-- Return Components (Regard these receipt as negative value of issue)
			SELECT 
				T0."ObjType",
				T1."ItemCode",
				T1."WhsCode",
				T1."BaseEntry",
				IFNULL(T1."Project", '') AS "Project",
				0 AS "InQty",
				T1."InvQty" * (-1) AS "OutQty",
				0 - T1."InvQty" AS "InvQty",
				I."UomFactor",
				I."UomStandard"
			FROM OIGN T0
				INNER JOIN IGN1 T1 ON T0."DocEntry" = T1."DocEntry"
				INNER JOIN ITEM_UOM I ON T1."ItemCode" = I."ItemCode"
				INNER JOIN OWOR P ON T1."BaseType" = P."ObjType"
					AND T1."BaseEntry" = P."DocEntry"
			WHERE T1."BaseType" = 202 -- 202 is Production Order
				AND T1."ItemType" = 4 -- 4 is for Item
				AND T1."BaseLine" >= 0 -- production order components
				AND (T1."IsByPrdct" = 'N' OR T1."IsByPrdct" IS NULL) -- not by product item
				AND (P."Type" = 'S' OR P."Type" = 'P') -- standard or special production order, not disasembly
				AND T0."DocDate" >= :START_DATE AND T0."DocDate" <= :END_DATE

			UNION ALL
			-- Return parent for Disassembly, regard as negative issue component
			SELECT 
				T0."ObjType",
				T1."ItemCode",
				T1."WhsCode",
				T1."BaseEntry",
				IFNULL(T1."Project", '') AS "Project",
				0 AS "InQty",
				T1."InvQty" * (-1) AS "OutQty",
				0 - T1."InvQty" AS "InvQty",
				I."UomFactor",
				I."UomStandard"
			FROM OIGN T0
				INNER JOIN IGN1 T1 ON T0."DocEntry" = T1."DocEntry"
				INNER JOIN ITEM_UOM I ON T1."ItemCode" = I."ItemCode"
				INNER JOIN OWOR P ON T1."BaseType" = P."ObjType"
					AND T1."BaseEntry" = P."DocEntry"
			WHERE T1."BaseType" = 202 -- 202 is Production Order
				AND T1."ItemType" = 4 -- 4 is for Item
				AND P."Type" = 'D' -- disassembly production order
				AND (T1."BaseLine" < 0 OR T1."BaseLine" IS NULL) -- disassembly parent
				AND T0."DocDate" >= :START_DATE AND T0."DocDate" <= :END_DATE
		) ORIGIN
		GROUP BY
			"ObjType",
			"ItemCode",
			"WhsCode",
			"BaseEntry",
			"UomStandard",
			"Project"
	) GRP		
) IGEWOR;
-- 4-06. 344, Goods Issue For Production, end

-- 4-07. 303, Goods Receipt From Production
INSERT INTO BEM2 SELECT
	"RowNumber", "BEMDocType", "BEMDocName", "ScopeKey", "RepId",
	"StartDate", "EndDate", "CreateDate", "B1ObjCode",
	"ItemCode", "WhseCode", "UomCode",
	"InQty", "OutQty", "Quantity",
	"VendorCode", "WOREntry", "RoleCode",
	"RscCode", "RscQty", "RscUomCode",
	"Project", "ToWhseCode", "CostCenter"
FROM (
	SELECT ROW_NUMBER() OVER 
		(ORDER BY "ItemCode", "WhseCode", "UomCode") AS "RowNumber",
		'303' AS "BEMDocType",
		'Goods Receipt From Production' AS "BEMDocName",
		:BEM_SCOPE_KEY AS "ScopeKey",
		:REP_ID AS "RepId",
		:START_DATE AS "StartDate",
		:END_DATE AS "EndDate",
		CURRENT_DATE AS "CreateDate",
		"B1ObjCode",
		"ItemCode",
		"WhseCode",
		"UomCode",
		"InQty",
		"OutQty",
		"Quantity",
		'' AS "VendorCode",
		"WOREntry",
		"RoleCode",
		'' AS "RscCode",
		0 AS "RscQty",
		'' AS "RscUomCode",
		"Project",
		'' AS "ToWhseCode",
		'' AS "CostCenter"
	FROM (
		SELECT
			"ObjType" AS "B1ObjCode",
			"ItemCode",
			"WhsCode" AS "WhseCode",
			"UomStandard" AS "UomCode",
			"BaseEntry" AS "WOREntry",
			"RoleCode",
			"Project",
			SUM("InQty") AS "InQty",
			SUM("OutQty") AS "OutQty",
			SUM("InvQty") AS "InvQty",
			SUM("InvQty" * "UomFactor") AS "Quantity"
		FROM (
			-- Goods Receipt From Production, Main Product
			SELECT 
				T0."ObjType",
				T1."ItemCode",
				T1."WhsCode",
				T1."BaseEntry",
				CASE WHEN T1."IsByPrdct" = 'Y' THEN 'BP'
					ELSE 'MP' END
					AS "RoleCode",
				IFNULL(T1."Project", '') AS "Project",
				T1."InvQty" AS "InQty",
				0 AS "OutQty",
				T1."InvQty" - 0 AS "InvQty",
				I."UomFactor",
				I."UomStandard"
			FROM OIGN T0
				INNER JOIN IGN1 T1 ON T0."DocEntry" = T1."DocEntry"
				INNER JOIN ITEM_UOM I ON T1."ItemCode" = I."ItemCode"
			WHERE T1."BaseType" = 202 -- 202 is Production Order
				AND T1."ItemType" = 4 -- 4 is for Item
				AND ("BaseLine" < 0 OR "BaseLine" is null) -- Parent Item
				AND T0."DocDate" >= :START_DATE AND T0."DocDate" <= :END_DATE
			
			UNION ALL
			-- Goods Receipt From Production, By Product
			SELECT 
				T0."ObjType",
				T1."ItemCode",
				T1."WhsCode",
				T1."BaseEntry",
				'BP' AS "RoleCode",
				IFNULL(T1."Project", '') AS "Project",
				T1."InvQty" AS "InQty",
				0 AS "OutQty",
				T1."InvQty" - 0 AS "InvQty",
				I."UomFactor",
				I."UomStandard"
			FROM OIGN T0
				INNER JOIN IGN1 T1 ON T0."DocEntry" = T1."DocEntry"
				INNER JOIN ITEM_UOM I ON T1."ItemCode" = I."ItemCode"
			WHERE T1."BaseType" = 202 -- 202 is Production Order
				AND T1."ItemType" = 4 -- 4 is for Item
				AND T1."BaseLine" >= 0 -- production order components
				AND T1."IsByPrdct" = 'Y' -- by product item
				AND T0."DocDate" >= :START_DATE AND T0."DocDate" <= :END_DATE

			UNION ALL
			-- Disassembly Child, Manual Receipt from Production
			SELECT 
				T0."ObjType",
				T1."ItemCode",
				T1."WhsCode",
				T1."BaseEntry",
				'MP' AS "RoleCode",
				IFNULL(T1."Project", '') AS "Project",
				T1."InvQty" AS "InQty",
				0 AS "OutQty",
				T1."InvQty" - 0 AS "InvQty",
				I."UomFactor",
				I."UomStandard"
			FROM OIGN T0
				INNER JOIN IGN1 T1 ON T0."DocEntry" = T1."DocEntry"
				INNER JOIN ITEM_UOM I ON T1."ItemCode" = I."ItemCode"
				INNER JOIN OWOR P ON T1."BaseType" = P."ObjType"
					AND T1."BaseEntry" = P."DocEntry"
			WHERE T1."BaseType" = 202 -- 202 is Production Order
				AND T1."ItemType" = 4 -- 4 is for Item
				AND T1."BaseLine" >= 0 -- production order components
				AND (T1."IsByPrdct" = 'N' OR T1."IsByPrdct" IS NULL) -- not by product item
				AND P."Type" = 'D' -- disassembly production order
				AND T0."DocDate" >= :START_DATE AND T0."DocDate" <= :END_DATE

			UNION ALL
			-- Disassembly Child, Backflush component, found from OIVL
			SELECT 
				T0."ObjType",
				L."ItemCode",
				L."LocCode" AS "WhsCode",
				T1."BaseEntry",
				'MP' AS "RoleCode",
				IFNULL(T1."Project", '') AS "Project",
				L."InQty",
				L."OutQty",
				L."InQty" - L."OutQty" AS "InvQty",
				I."UomFactor",
				I."UomStandard"
			FROM OIVL L
				INNER JOIN OIGE T0 ON T0."ObjType" = L."TransType"
					AND T0."DocEntry" = L."CreatedBy"
				INNER JOIN IGE1 T1 ON T0."DocEntry" = T1."DocEntry"
				INNER JOIN ITEM_UOM I ON L."ItemCode" = I."ItemCode"
				INNER JOIN OWOR P ON T1."BaseType" = P."ObjType"
					AND T1."BaseEntry" = P."DocEntry"
			WHERE L."InQty" > 0 AND L."OutQty" = 0
				AND T1."BaseType" = 202 -- 202 is Production Order
				AND T1."ItemType" = 4 -- 4 is for Item
				AND (T1."BaseLine" < 0 OR T1."BaseLine" is null) -- backflush of Parent Item
				AND P."Type" = 'D' -- disassembly production order
				AND T0."DocDate" >= :START_DATE AND T0."DocDate" <= :END_DATE

			UNION ALL
			-- Disassembly Child, Manual return to Production, regard as negative receipt from production
			SELECT 
				T0."ObjType",
				T1."ItemCode",
				T1."WhsCode",
				T1."BaseEntry",
				'MP' AS "RoleCode",
				IFNULL(T1."Project", '') AS "Project",
				0 AS "InQty",
				T1."InvQty" * (-1) AS "OutQty",
				0 -T1."InvQty" AS "InvQty",
				I."UomFactor",
				I."UomStandard"
			FROM OIGE T0
				INNER JOIN IGE1 T1 ON T0."DocEntry" = T1."DocEntry"
				INNER JOIN ITEM_UOM I ON T1."ItemCode" = I."ItemCode"
				INNER JOIN OWOR P ON T1."BaseType" = P."ObjType"
					AND T1."BaseEntry" = P."DocEntry"
			WHERE T1."BaseType" = 202 -- 202 is Production Order
				AND T1."ItemType" = 4 -- 4 is for Item
				AND T1."BaseLine" >= 0 -- production order components
				AND (T1."IsByPrdct" = 'N' OR T1."IsByPrdct" IS NULL) -- not by product item
				AND P."Type" = 'D' -- disassembly production order
				AND T0."DocDate" >= :START_DATE AND T0."DocDate" <= :END_DATE
		) ORIGIN
		GROUP BY
			"ObjType",
			"ItemCode",
			"WhsCode",
			"UomStandard",
			"BaseEntry",
			"RoleCode",
			"Project"
	) GRP		
) IGNWOR;
-- 4-07. 303, Goods Receipt From Production


-- 4-08, 403, Internal Service Confirmation
INSERT INTO BEM2 SELECT
	"RowNumber", "BEMDocType", "BEMDocName", "ScopeKey", "RepId",
	"StartDate", "EndDate", "CreateDate", "B1ObjCode",
	"ItemCode", "WhseCode", "UomCode",
	"InQty", "OutQty", "Quantity",
	"VendorCode", "WOREntry", "RoleCode",
	"RscCode", "RscQty", "RscUomCode",
	"Project", "ToWhseCode", "CostCenter"
FROM (
	SELECT ROW_NUMBER() OVER 
		(ORDER BY "RscCode", "WhseCode", "WOREntry") AS "RowNumber",
		'403' AS "BEMDocType",
		'Internal Service Confirmation' AS "BEMDocName",
		:BEM_SCOPE_KEY AS "ScopeKey",
		:REP_ID AS "RepId",
		:START_DATE AS "StartDate",
		:END_DATE AS "EndDate",
		CURRENT_DATE AS "CreateDate",
		"B1ObjCode",
		'' AS "ItemCode",
		"WhseCode",
		'' AS "UomCode",
		"InQty",
		"OutQty",
		0 AS "Quantity",
		'' AS "VendorCode",
		"WOREntry",
		'' AS "RoleCode",
		"RscCode",
		"RscQtySec" / 3600 AS "RscQty",
		'HUR' AS "RscUomCode", -- hour
		"Project",
		'' AS "ToWhseCode",
		'' AS "CostCenter"
	FROM (
		SELECT
			"ObjType" AS "B1ObjCode",
			"WhsCode" AS "WhseCode",
			"BaseEntry" AS "WOREntry",
			"ResCode" AS "RscCode",
			"Project",
			SUM("InQty") AS "InQty",
			SUM("OutQty") AS "OutQty",
			SUM("InvQty") AS "InvQty",
			SUM("InvQty" * "TimeResUn") AS "RscQtySec" -- second
		FROM (
			-- Goods Issue For Production, Resource
			SELECT 
				T0."ObjType",
				T1."WhsCode",
				T1."BaseEntry",
				IFNULL(T1."Project", '') AS "Project",
				R."ResCode",
				0 AS "InQty",
				T1."Quantity" AS "OutQty",
				T1."Quantity" - 0 AS "InvQty",
				R."TimeResUn"
			FROM OIGE T0
				INNER JOIN IGE1 T1 ON T0."DocEntry" = T1."DocEntry"
				INNER JOIN ORSC R ON T1."ItemCode" = R."ResCode"
			WHERE T1."BaseType" = 202 -- 202 is Production Order
				AND T1."ItemType" = 290 -- 290 is for Resource
				AND T0."DocDate" >= :START_DATE AND T0."DocDate" <= :END_DATE
			
			UNION ALL
			-- Return Components, Resource
			SELECT 
				T0."ObjType",
				T1."WhsCode",
				T1."BaseEntry",
				IFNULL(T1."Project", '') AS "Project",
				R."ResCode",
				0 AS "InQty",
				T1."Quantity" * (-1) AS "OutQty",
				0 - T1."Quantity" AS "InvQty",
				R."TimeResUn"
			FROM OIGN T0
				INNER JOIN IGN1 T1 ON T0."DocEntry" = T1."DocEntry"
				INNER JOIN ORSC R ON T1."ItemCode" = R."ResCode"
			WHERE T1."BaseType" = 202 -- 202 is Production Order
				AND T1."ItemType" = 290 -- 290 is for Resource
				AND T0."DocDate" >= :START_DATE AND T0."DocDate" <= :END_DATE
				
		) ORIGIN
		GROUP BY
			"ObjType",
			"WhsCode",
			"BaseEntry",
			"ResCode",
			"Project"
	) GRP		
) RSCWOR;
-- 4-08, 403, Internal Service Confirmation, end

-- 4-09, 381, Goods Transfer
INSERT INTO BEM2 SELECT
	"RowNumber", "BEMDocType", "BEMDocName", "ScopeKey", "RepId",
	"StartDate", "EndDate", "CreateDate", "B1ObjCode",
	"ItemCode", "WhseCode", "UomCode",
	"InQty", "OutQty", "Quantity",
	"VendorCode", "WOREntry", "RoleCode",
	"RscCode", "RscQty", "RscUomCode",
	"Project", "ToWhseCode", "CostCenter"
FROM (
	SELECT ROW_NUMBER() OVER 
		(ORDER BY "ItemCode", "WhseCode", "UomCode", "ToWhseCode") AS "RowNumber",
		'381' AS "BEMDocType",
		'Goods Transfer' AS "BEMDocName",
		:BEM_SCOPE_KEY AS "ScopeKey",
		:REP_ID AS "RepId",
		:START_DATE AS "StartDate",
		:END_DATE AS "EndDate",
		CURRENT_DATE AS "CreateDate",
		"B1ObjCode",
		"ItemCode",
		"WhseCode",
		"UomCode",
		"InQty",
		"OutQty",
		"Quantity",
		'' AS "VendorCode",
		'' AS "WOREntry",
		'' AS "RoleCode",
		'' AS "RscCode",
		0 AS "RscQty",
		'' AS "RscUomCode",
		"Project",
		"ToWhseCode",
		'' AS "CostCenter"
	FROM (
		SELECT
			"ObjType" AS "B1ObjCode",
			"ItemCode",
			"FromWhsCod" AS "WhseCode",
			"UomStandard" AS "UomCode",
			"WhsCode" AS "ToWhseCode",
			"Project",
			SUM("InQty") AS "InQty",
			SUM("OutQty") AS "OutQty",
			SUM("InvQty") AS "InvQty",
			SUM("InvQty" * "UomFactor") AS "Quantity"
		FROM (
			-- Inventory Transfer
			SELECT 
				T0."ObjType",
				T1."ItemCode",
				T1."WhsCode",
				T1."FromWhsCod",
				IFNULL(T1."Project", '') AS "Project",
				T1."InvQty" AS "InQty",
				T1."InvQty" AS "OutQty",
				T1."InvQty" AS "InvQty",
				I."UomFactor",
				I."UomStandard"
			FROM OWTR T0
				INNER JOIN WTR1 T1 ON T0."DocEntry" = T1."DocEntry"
				INNER JOIN ITEM_UOM I ON T1."ItemCode" = I."ItemCode"
			WHERE T1."WhsCode" <> T1."FromWhsCod"
				AND T1."ActDelDate" >= :START_DATE AND T1."ActDelDate" <= :END_DATE
		) ORIGIN
		GROUP BY
			"ObjType",
			"ItemCode",
			"WhsCode",
			"FromWhsCod",
			"UomStandard",
			"Project"
	) GRP		
) WTR;
-- 4-09, 381, Goods Transfer, end

-- 4-10, 346, Goods Issue Without Reference
INSERT INTO BEM2 SELECT
	"RowNumber", "BEMDocType", "BEMDocName", "ScopeKey", "RepId",
	"StartDate", "EndDate", "CreateDate", "B1ObjCode",
	"ItemCode", "WhseCode", "UomCode",
	"InQty", "OutQty", "Quantity",
	"VendorCode", "WOREntry", "RoleCode",
	"RscCode", "RscQty", "RscUomCode",
	"Project", "ToWhseCode", "CostCenter"
FROM (
	SELECT ROW_NUMBER() OVER 
		(ORDER BY "ItemCode", "WhseCode", "UomCode") AS "RowNumber",
		'346' AS "BEMDocType",
		'Goods Issue Without Reference' AS "BEMDocName",
		:BEM_SCOPE_KEY AS "ScopeKey",
		:REP_ID AS "RepId",
		:START_DATE AS "StartDate",
		:END_DATE AS "EndDate",
		CURRENT_DATE AS "CreateDate",
		"B1ObjCode",
		"ItemCode",
		"WhseCode",
		"UomCode",
		"InQty",
		"OutQty",
		"Quantity",
		'' AS "VendorCode",
		'' AS "WOREntry",
		'' AS "RoleCode",
		'' AS "RscCode",
		0 AS "RscQty",
		'' AS "RscUomCode",
		"Project",
		'' AS "ToWhseCode",
		'' AS "CostCenter"
	FROM (
		SELECT
			"ObjType" AS "B1ObjCode",
			"ItemCode",
			"WhsCode" AS "WhseCode",
			"UomStandard" AS "UomCode",
			"Project",
			SUM("InQty") AS "InQty",
			SUM("OutQty") AS "OutQty",
			SUM("InvQty") AS "InvQty",
			SUM("InvQty" * "UomFactor") AS "Quantity"
		FROM (
			-- Goods Issue no project
			SELECT 
				T0."ObjType",
				T1."ItemCode",
				T1."WhsCode",
				IFNULL(T1."Project", '') AS "Project",
				0 AS "InQty",
				T1."InvQty" AS "OutQty",
				T1."InvQty" - 0 AS "InvQty",
				I."UomFactor",
				I."UomStandard"
			FROM OIGE T0
				INNER JOIN IGE1 T1 ON T0."DocEntry" = T1."DocEntry"
				INNER JOIN ITEM_UOM I ON T1."ItemCode" = I."ItemCode"
			WHERE (T1."BaseType" <> 202 OR T1."BaseType" IS NULL)
				AND (TRIM(T1."Project") = '' OR T1."Project" IS NULL)
				AND (CASE WHEN :BEM_DIMENSION = 1 THEN IFNULL(T1."OcrCode", '')
						  WHEN :BEM_DIMENSION = 2 THEN IFNULL(T1."OcrCode2", '')
						  WHEN :BEM_DIMENSION = 3 THEN IFNULL(T1."OcrCode3", '')
						  WHEN :BEM_DIMENSION = 4 THEN IFNULL(T1."OcrCode4", '')
						  WHEN :BEM_DIMENSION = 5 THEN IFNULL(T1."OcrCode5", '')
						  ELSE ''
					 END) = ''
				AND T1."DocDate" >= :START_DATE AND T1."DocDate" <= :END_DATE
		) ORIGIN
		GROUP BY
			"ObjType",
			"ItemCode",
			"WhsCode",
			"UomStandard",
			"Project"
	) GRP		
) WTR;
-- 4-10, 346, Goods Issue Without Reference, end

-- 4-11, 304, Goods Receipt Without Reference
INSERT INTO BEM2 SELECT
	"RowNumber", "BEMDocType", "BEMDocName", "ScopeKey", "RepId",
	"StartDate", "EndDate", "CreateDate", "B1ObjCode",
	"ItemCode", "WhseCode", "UomCode",
	"InQty", "OutQty", "Quantity",
	"VendorCode", "WOREntry", "RoleCode",
	"RscCode", "RscQty", "RscUomCode",
	"Project", "ToWhseCode", "CostCenter"
FROM (
	SELECT ROW_NUMBER() OVER 
		(ORDER BY "ItemCode", "WhseCode", "UomCode", "Project") AS "RowNumber",
		'304' AS "BEMDocType",
		'Goods Receipt Without Reference' AS "BEMDocName",
		:BEM_SCOPE_KEY AS "ScopeKey",
		:REP_ID AS "RepId",
		:START_DATE AS "StartDate",
		:END_DATE AS "EndDate",
		CURRENT_DATE AS "CreateDate",
		"B1ObjCode",
		"ItemCode",
		"WhseCode",
		"UomCode",
		"InQty",
		"OutQty",
		"Quantity",
		'' AS "VendorCode",
		'' AS "WOREntry",
		'' AS "RoleCode",
		'' AS "RscCode",
		0 AS "RscQty",
		'' AS "RscUomCode",
		"Project",
		'' AS "ToWhseCode",
		'' AS "CostCenter"
	FROM (
		SELECT
			"ObjType" AS "B1ObjCode",
			"ItemCode",
			"WhsCode" AS "WhseCode",
			"UomStandard" AS "UomCode",
			"Project",
			SUM("InQty") AS "InQty",
			SUM("OutQty") AS "OutQty",
			SUM("InvQty") AS "InvQty",
			SUM("InvQty" * "UomFactor") AS "Quantity"
		FROM (
			-- Goods Issue with project
			SELECT 
				T0."ObjType",
				T1."ItemCode",
				T1."WhsCode",
				IFNULL(T1."Project", '') AS "Project",
				T1."InvQty" AS "InQty",
				0 AS "OutQty",
				T1."InvQty" - 0 AS "InvQty",
				I."UomFactor",
				I."UomStandard"
			FROM OIGN T0
				INNER JOIN IGN1 T1 ON T0."DocEntry" = T1."DocEntry"
				INNER JOIN ITEM_UOM I ON T1."ItemCode" = I."ItemCode"
			WHERE (T1."BaseType" <> 202 OR T1."BaseType" IS NULL)
				AND (T1."Project" = '' OR T1."Project" IS NULL)
				AND (CASE WHEN :BEM_DIMENSION = 1 THEN IFNULL(T1."OcrCode", '')
						  WHEN :BEM_DIMENSION = 2 THEN IFNULL(T1."OcrCode2", '')
						  WHEN :BEM_DIMENSION = 3 THEN IFNULL(T1."OcrCode3", '')
						  WHEN :BEM_DIMENSION = 4 THEN IFNULL(T1."OcrCode4", '')
						  WHEN :BEM_DIMENSION = 5 THEN IFNULL(T1."OcrCode5", '')
						  ELSE ''
					 END) = ''
				AND T1."DocDate" >= :START_DATE AND T1."DocDate" <= :END_DATE
		) ORIGIN
		GROUP BY
			"ObjType",
			"ItemCode",
			"WhsCode",
			"UomStandard",
			"Project"
	) GRP		
) WTR;
-- 4-11, 304, Goods Receipt Without Reference, end

-- 4-12, 945, Goods Issue For Project
INSERT INTO BEM2 SELECT
	"RowNumber", "BEMDocType", "BEMDocName", "ScopeKey", "RepId",
	"StartDate", "EndDate", "CreateDate", "B1ObjCode",
	"ItemCode", "WhseCode", "UomCode",
	"InQty", "OutQty", "Quantity",
	"VendorCode", "WOREntry", "RoleCode",
	"RscCode", "RscQty", "RscUomCode",
	"Project", "ToWhseCode", "CostCenter"
FROM (
	SELECT ROW_NUMBER() OVER 
		(ORDER BY "ItemCode", "WhseCode", "UomCode", "Project") AS "RowNumber",
		'945' AS "BEMDocType",
		'Goods Issue For Project' AS "BEMDocName",
		:BEM_SCOPE_KEY AS "ScopeKey",
		:REP_ID AS "RepId",
		:START_DATE AS "StartDate",
		:END_DATE AS "EndDate",
		CURRENT_DATE AS "CreateDate",
		"B1ObjCode",
		"ItemCode",
		"WhseCode",
		"UomCode",
		"InQty",
		"OutQty",
		"Quantity",
		'' AS "VendorCode",
		'' AS "WOREntry",
		'' AS "RoleCode",
		'' AS "RscCode",
		0 AS "RscQty",
		'' AS "RscUomCode",
		"Project",
		'' AS "ToWhseCode",
		'' AS "CostCenter"
	FROM (
		SELECT
			"ObjType" AS "B1ObjCode",
			"ItemCode",
			"WhsCode" AS "WhseCode",
			"UomStandard" AS "UomCode",
			"Project",
			SUM("InQty") AS "InQty",
			SUM("OutQty") AS "OutQty",
			SUM("InvQty") AS "InvQty",
			SUM("InvQty" * "UomFactor") AS "Quantity"
		FROM (
			-- Goods Issue with project
			SELECT 
				T0."ObjType",
				T1."ItemCode",
				T1."WhsCode",
				IFNULL(T1."Project", '') AS "Project",
				0 AS "InQty",
				T1."InvQty" AS "OutQty",
				T1."InvQty" - 0 AS "InvQty",
				I."UomFactor",
				I."UomStandard"
			FROM OIGE T0
				INNER JOIN IGE1 T1 ON T0."DocEntry" = T1."DocEntry"
				INNER JOIN ITEM_UOM I ON T1."ItemCode" = I."ItemCode"
			WHERE (T1."BaseType" <> 202 OR T1."BaseType" IS NULL)
				AND (T1."Project" IS NOT NULL AND T1."Project" <> '')
				AND (CASE WHEN :BEM_DIMENSION = 1 THEN IFNULL(T1."OcrCode", '')
						  WHEN :BEM_DIMENSION = 2 THEN IFNULL(T1."OcrCode2", '')
						  WHEN :BEM_DIMENSION = 3 THEN IFNULL(T1."OcrCode3", '')
						  WHEN :BEM_DIMENSION = 4 THEN IFNULL(T1."OcrCode4", '')
						  WHEN :BEM_DIMENSION = 5 THEN IFNULL(T1."OcrCode5", '')
						  ELSE ''
					 END) = ''
				AND T1."DocDate" >= :START_DATE AND T1."DocDate" <= :END_DATE
		) ORIGIN
		GROUP BY
			"ObjType",
			"ItemCode",
			"WhsCode",
			"UomStandard",
			"Project"
	) GRP		
) WTR;
-- 4-12, 945, Goods Issue For Project, end

-- 4-13, 906, Goods Return From Project
INSERT INTO BEM2 SELECT
	"RowNumber", "BEMDocType", "BEMDocName", "ScopeKey", "RepId",
	"StartDate", "EndDate", "CreateDate", "B1ObjCode",
	"ItemCode", "WhseCode", "UomCode",
	"InQty", "OutQty", "Quantity",
	"VendorCode", "WOREntry", "RoleCode",
	"RscCode", "RscQty", "RscUomCode",
	"Project", "ToWhseCode", "CostCenter"
FROM (
	SELECT ROW_NUMBER() OVER 
		(ORDER BY "ItemCode", "WhseCode", "UomCode", "Project") AS "RowNumber",
		'906' AS "BEMDocType",
		'Goods Return From Project' AS "BEMDocName",
		:BEM_SCOPE_KEY AS "ScopeKey",
		:REP_ID AS "RepId",
		:START_DATE AS "StartDate",
		:END_DATE AS "EndDate",
		CURRENT_DATE AS "CreateDate",
		"B1ObjCode",
		"ItemCode",
		"WhseCode",
		"UomCode",
		"InQty",
		"OutQty",
		"Quantity",
		'' AS "VendorCode",
		'' AS "WOREntry",
		'' AS "RoleCode",
		'' AS "RscCode",
		0 AS "RscQty",
		'' AS "RscUomCode",
		"Project",
		'' AS "ToWhseCode",
		'' AS "CostCenter"
	FROM (
		SELECT
			"ObjType" AS "B1ObjCode",
			"ItemCode",
			"WhsCode" AS "WhseCode",
			"UomStandard" AS "UomCode",
			"Project",
			SUM("InQty") AS "InQty",
			SUM("OutQty") AS "OutQty",
			SUM("InvQty") AS "InvQty",
			SUM("InvQty" * "UomFactor") AS "Quantity"
		FROM (
			-- Goods Issue with project
			SELECT 
				T0."ObjType",
				T1."ItemCode",
				T1."WhsCode",
				IFNULL(T1."Project", '') AS "Project",
				T1."InvQty" AS "InQty",
				0 AS "OutQty",
				T1."InvQty" - 0 AS "InvQty",
				I."UomFactor",
				I."UomStandard"
			FROM OIGN T0
				INNER JOIN IGN1 T1 ON T0."DocEntry" = T1."DocEntry"
				INNER JOIN ITEM_UOM I ON T1."ItemCode" = I."ItemCode"
			WHERE (T1."BaseType" <> 202 OR T1."BaseType" IS NULL)
				AND (T1."Project" IS NOT NULL AND T1."Project" <> '')
				AND (CASE WHEN :BEM_DIMENSION = 1 THEN IFNULL(T1."OcrCode", '')
						  WHEN :BEM_DIMENSION = 2 THEN IFNULL(T1."OcrCode2", '')
						  WHEN :BEM_DIMENSION = 3 THEN IFNULL(T1."OcrCode3", '')
						  WHEN :BEM_DIMENSION = 4 THEN IFNULL(T1."OcrCode4", '')
						  WHEN :BEM_DIMENSION = 5 THEN IFNULL(T1."OcrCode5", '')
						  ELSE ''
					 END) = ''
				AND T1."DocDate" >= :START_DATE AND T1."DocDate" <= :END_DATE
		) ORIGIN
		GROUP BY
			"ObjType",
			"ItemCode",
			"WhsCode",
			"UomStandard",
			"Project"
	) GRP		
) WTR;
-- 4-13, 906, Goods Return From Project, end

-- 4-14, 345, Goods Issue For Cost Center
INSERT INTO BEM2 SELECT
	"RowNumber", "BEMDocType", "BEMDocName", "ScopeKey", "RepId",
	"StartDate", "EndDate", "CreateDate", "B1ObjCode",
	"ItemCode", "WhseCode", "UomCode",
	"InQty", "OutQty", "Quantity",
	"VendorCode", "WOREntry", "RoleCode",
	"RscCode", "RscQty", "RscUomCode",
	"Project", "ToWhseCode", "CostCenter"
FROM (
	SELECT ROW_NUMBER() OVER 
		(ORDER BY "ItemCode", "WhseCode", "UomCode", "CostCenter") AS "RowNumber",
		'345' AS "BEMDocType",
		'Goods Issue For Cost Center' AS "BEMDocName",
		:BEM_SCOPE_KEY AS "ScopeKey",
		:REP_ID AS "RepId",
		:START_DATE AS "StartDate",
		:END_DATE AS "EndDate",
		CURRENT_DATE AS "CreateDate",
		"B1ObjCode",
		"ItemCode",
		"WhseCode",
		"UomCode",
		"InQty",
		"OutQty",
		"Quantity",
		'' AS "VendorCode",
		'' AS "WOREntry",
		'' AS "RoleCode",
		'' AS "RscCode",
		0 AS "RscQty",
		'' AS "RscUomCode",
		"Project",
		'' AS "ToWhseCode",
		"CostCenter"
	FROM (
		SELECT
			"ObjType" AS "B1ObjCode",
			"ItemCode",
			"WhsCode" AS "WhseCode",
			"UomStandard" AS "UomCode",
			'' AS "Project",
			SUM("InQty" * "DistRate") AS "InQty",
			SUM("OutQty" * "DistRate") AS "OutQty",
			SUM("InvQty" * "DistRate") AS "InvQty",
			SUM("InvQty" * "UomFactor" * "DistRate") AS "Quantity",
			"CostCenter"
		FROM (
			-- Goods Issue with Cost Center
			SELECT 
				T0."ObjType",
				T1."ItemCode",
				T1."WhsCode",
				0 AS "InQty",
				T1."InvQty" AS "OutQty",
				T1."InvQty" - 0 AS "InvQty",
				I."UomFactor",
				I."UomStandard",
				T2."PrcCode" AS "CostCenter",
				CASE WHEN T2."OcrTotal" = 0 THEN 1
					 ELSE T2."PrcAmount" / T2."OcrTotal"
					 END AS "DistRate"
			FROM OIGE T0
				INNER JOIN IGE1 T1 ON T0."DocEntry" = T1."DocEntry"
				INNER JOIN ITEM_UOM I ON T1."ItemCode" = I."ItemCode"
				INNER JOIN OCR1 T2 ON T2."OcrCode" =  
									  (CASE WHEN :BEM_DIMENSION = 1 THEN T1."OcrCode"
											WHEN :BEM_DIMENSION = 2 THEN T1."OcrCode2"
											WHEN :BEM_DIMENSION = 3 THEN T1."OcrCode3"
											WHEN :BEM_DIMENSION = 4 THEN T1."OcrCode4"
											WHEN :BEM_DIMENSION = 5 THEN T1."OcrCode5"
											ELSE ''
										END)
			WHERE (T1."BaseType" <> 202 OR T1."BaseType" IS NULL)
				AND (CASE WHEN :BEM_DIMENSION = 1 THEN IFNULL(T1."OcrCode", '')
						  WHEN :BEM_DIMENSION = 2 THEN IFNULL(T1."OcrCode2", '')
						  WHEN :BEM_DIMENSION = 3 THEN IFNULL(T1."OcrCode3", '')
						  WHEN :BEM_DIMENSION = 4 THEN IFNULL(T1."OcrCode4", '')
						  WHEN :BEM_DIMENSION = 5 THEN IFNULL(T1."OcrCode5", '')
						  ELSE ''
					 END) <> ''
				AND T1."DocDate" >= :START_DATE AND T1."DocDate" <= :END_DATE
		) ORIGIN
		GROUP BY
			"ObjType",
			"ItemCode",
			"WhsCode",
			"UomStandard",
			"CostCenter"
	) GRP		
) WTR;
-- 4-14, 345, Goods Issue For Cost Center, end

-- 4-15, 306, Goods Return From Cost Center
INSERT INTO BEM2 SELECT
	"RowNumber", "BEMDocType", "BEMDocName", "ScopeKey", "RepId",
	"StartDate", "EndDate", "CreateDate", "B1ObjCode",
	"ItemCode", "WhseCode", "UomCode",
	"InQty", "OutQty", "Quantity",
	"VendorCode", "WOREntry", "RoleCode",
	"RscCode", "RscQty", "RscUomCode",
	"Project", "ToWhseCode", "CostCenter"
FROM (
	SELECT ROW_NUMBER() OVER 
		(ORDER BY "ItemCode", "WhseCode", "UomCode", "CostCenter") AS "RowNumber",
		'306' AS "BEMDocType",
		'Goods Return From Cost Center' AS "BEMDocName",
		:BEM_SCOPE_KEY AS "ScopeKey",
		:REP_ID AS "RepId",
		:START_DATE AS "StartDate",
		:END_DATE AS "EndDate",
		CURRENT_DATE AS "CreateDate",
		"B1ObjCode",
		"ItemCode",
		"WhseCode",
		"UomCode",
		"InQty",
		"OutQty",
		"Quantity",
		'' AS "VendorCode",
		'' AS "WOREntry",
		'' AS "RoleCode",
		'' AS "RscCode",
		0 AS "RscQty",
		'' AS "RscUomCode",
		"Project",
		'' AS "ToWhseCode",
		"CostCenter"
	FROM (
		SELECT
			"ObjType" AS "B1ObjCode",
			"ItemCode",
			"WhsCode" AS "WhseCode",
			"UomStandard" AS "UomCode",
			'' AS "Project",
			SUM("InQty" * "DistRate") AS "InQty",
			SUM("OutQty" * "DistRate") AS "OutQty",
			SUM("InvQty" * "DistRate") AS "InvQty",
			SUM("InvQty" * "UomFactor" * "DistRate") AS "Quantity",
			"CostCenter"
		FROM (
			-- Goods Issue with Cost Center
			SELECT 
				T0."ObjType",
				T1."ItemCode",
				T1."WhsCode",
				T1."InvQty" AS "InQty",
				0 AS "OutQty",
				T1."InvQty" - 0 AS "InvQty",
				I."UomFactor",
				I."UomStandard",
				T2."PrcCode" AS "CostCenter",
				CASE WHEN T2."OcrTotal" = 0 THEN 1
					 ELSE T2."PrcAmount" / T2."OcrTotal"
					 END AS "DistRate"
			FROM OIGN T0
				INNER JOIN IGN1 T1 ON T0."DocEntry" = T1."DocEntry"
				INNER JOIN ITEM_UOM I ON T1."ItemCode" = I."ItemCode"
				INNER JOIN OCR1 T2 ON T2."OcrCode" =  
									  (CASE WHEN :BEM_DIMENSION = 1 THEN T1."OcrCode"
											WHEN :BEM_DIMENSION = 2 THEN T1."OcrCode2"
											WHEN :BEM_DIMENSION = 3 THEN T1."OcrCode3"
											WHEN :BEM_DIMENSION = 4 THEN T1."OcrCode4"
											WHEN :BEM_DIMENSION = 5 THEN T1."OcrCode5"
											ELSE ''
										END)
			WHERE (T1."BaseType" <> 202 OR T1."BaseType" IS NULL)
				AND (CASE WHEN :BEM_DIMENSION = 1 THEN IFNULL(T1."OcrCode", '')
						  WHEN :BEM_DIMENSION = 2 THEN IFNULL(T1."OcrCode2", '')
						  WHEN :BEM_DIMENSION = 3 THEN IFNULL(T1."OcrCode3", '')
						  WHEN :BEM_DIMENSION = 4 THEN IFNULL(T1."OcrCode4", '')
						  WHEN :BEM_DIMENSION = 5 THEN IFNULL(T1."OcrCode5", '')
						  ELSE ''
					 END) <> ''
				AND T1."DocDate" >= :START_DATE AND T1."DocDate" <= :END_DATE
		) ORIGIN
		GROUP BY
			"ObjType",
			"ItemCode",
			"WhsCode",
			"UomStandard",
			"CostCenter"
	) GRP		
) WTR;
-- 4-15, 306, Goods Return From Cost Center, end

-- 4-16, PRODUCTION_DOCUMENT, Production Document
INSERT INTO BEM2 SELECT
	"RowNumber", "BEMDocType", "BEMDocName", "ScopeKey", "RepId",
	"StartDate", "EndDate", "CreateDate", "B1ObjCode",
	"ItemCode", "WhseCode", "UomCode",
	"InQty", "OutQty", "Quantity",
	"VendorCode", "WOREntry", "RoleCode",
	"RscCode", "RscQty", "RscUomCode",
	"Project", "ToWhseCode", "CostCenter"
FROM (
	SELECT ROW_NUMBER() OVER 
		(ORDER BY "WOREntry", "WhseCode", "Project") AS "RowNumber",
		'0' AS "BEMDocType",
		'Production Document' AS "BEMDocName",
		:BEM_SCOPE_KEY AS "ScopeKey",
		:REP_ID AS "RepId",
		:START_DATE AS "StartDate",
		:END_DATE AS "EndDate",
		CURRENT_DATE AS "CreateDate",
		"B1ObjCode",
		'' AS "ItemCode",
		"WhseCode",
		'' AS "UomCode",
		0 AS "InQty",
		0 AS "OutQty",
		0 AS "Quantity",
		'' AS "VendorCode",
		"WOREntry",
		'' AS "RoleCode",
		'' AS "RscCode",
		0 AS "RscQty",
		'' AS "RscUomCode",
		"Project",
		'' AS "ToWhseCode",
		'' AS "CostCenter"
	FROM (
		SELECT
			"ObjType" AS "B1ObjCode",
			"Warehouse" AS "WhseCode",
			"DocEntry" AS "WOREntry",
			IFNULL("Project", '') AS "Project"
		FROM OWOR P
		WHERE CAST("DocEntry" AS NVARCHAR(60)) IN (
			SELECT "WOREntry" FROM BEM2 B
			WHERE B."RepId" = :REP_ID
			)
		GROUP BY
			"ObjType",
			"Warehouse",
			"DocEntry",
			"Project"
	) GRP
) WOR;
-- 4-16, PRODUCTION_DOCUMENT, Production Document, end

DROP TABLE ITEM_UOM;
END IF;
-- END IF for PERIOD_ENTRY exists.

-- 5. Return Data Count
SELECT "BEMDocType", "BEMDocName", COUNT(*) AS "RowCount"
FROM BEM2
WHERE "RepId" = :REP_ID
GROUP BY "BEMDocType", "BEMDocName"
ORDER BY "BEMDocType";


END;