--DELETE FROM "MSTR"."PO_HEADER";
--DELETE FROM "MSTR"."PO_ITEM";
--DELETE FROM "MSTR"."DELIVERY"; 

DROP PROCEDURE BUILD_MSTR_DATA;

CREATE PROCEDURE BUILD_MSTR_DATA (IN NMBR INT) LANGUAGE SQLSCRIPT AS 

CNTR INTEGER;
NUMMAX INTEGER;

lV_MOD INTEGER;
LV_ID INTEGER;
LV_PO_DATE_ID INTEGER;
LV_PO_ORG_ID INTEGER;
LV_VENDOR_ID INTEGER;
LV_MATNR_ID INTEGER;
LV_PO_NUM INTEGER;
LV_PO_ITEM INTEGER;
LV_DEL_NUM INTEGER;
LV_PO_DATE DATE;
LV_REQ_DATE DATE;
LV_DEL_DATE DATE;
LV_ORD_QUANTITY INTEGER;
LV_DEL_QUANTITY INTEGER;
LV_UNIT_PRICE DECIMAL(10,2);
LV_VALUE DECIMAL(10,2);


LV_CNT_PO_DAT_ID INTEGER;
LV_CNT_PO_ORG_ID INTEGER;
LV_CNT_VENDOR_ID INTEGER;
LV_CNT_MATNR_ID INTEGER;


BEGIN
CNTR := 0;
SELECT COUNT(*) INTO CNTR from "MSTR"."PO_HEADER";
NUMMAX := :NMBR + :CNTR;
SELECT COUNT(*) INTO LV_CNT_PO_DAT_ID FROM "MSTR"."TIME_MDA" WHERE ID BETWEEN 0 and 1400;
SELECT COUNT(*) INTO LV_CNT_PO_ORG_ID FROM "MSTR"."PO_ORG_MDA" WHERE ID > 0;
SELECT COUNT(*) INTO LV_CNT_VENDOR_ID FROM "MSTR"."VENDOR_MDA" WHERE ID > 0;
SELECT COUNT(*) INTO LV_CNT_MATNR_ID FROM "MSTR"."MATERIAL_MDA" WHERE ID > 0;

WHILE CNTR < :NUMMAX DO
--Random PO date from time table
LV_ID := ROUND(RAND() * LV_CNT_PO_DAT_ID, 0 );       
SELECT DAY_DATE INTO LV_PO_DATE FROM "MSTR"."TIME_MDA" WHERE ID = LV_ID;
--PO requested delivery date (+ 0-25)
LV_REQ_DATE := ADD_DAYS (TO_DATE (LV_PO_DATE, 'YYYY-MM-DD'), RAND() * 25);
--PO actual delivery date (+ 0-60)
LV_DEL_DATE := ADD_DAYS (TO_DATE (LV_PO_DATE, 'YYYY-MM-DD'), RAND() * 60);
--Random PO organisation ID
LV_ID := ROUND(RAND() * LV_CNT_PO_ORG_ID, 0 );     
SELECT PO_ORG_ID INTO LV_PO_ORG_ID FROM "MSTR"."PO_ORG_MDA" WHERE ID = LV_ID;
--Random Vendor ID
LV_ID := ROUND(RAND() * LV_CNT_VENDOR_ID, 0 );
SELECT VENDOR_ID INTO LV_VENDOR_ID FROM "MSTR"."VENDOR_MDA" WHERE ID = LV_ID;
--Random Material ID + Unit price
LV_ID := ROUND(RAND() * LV_CNT_MATNR_ID, 0 );
SELECT MATERIAL_ID, UNIT_PRICE INTO LV_MATNR_ID, LV_UNIT_PRICE FROM "MSTR"."MATERIAL_MDA" WHERE ID = LV_ID;
--Random Order Qunatity (1-100)
LV_ORD_QUANTITY := ROUND(RAND() * 100, 0 );
IF LV_ORD_QUANTITY = 0
THEN
LV_ORD_QUANTITY := 1;
END IF; 
--Order Value
LV_VALUE :=  LV_UNIT_PRICE * LV_ORD_QUANTITY;  
--Delivered Quantity (0-ordered quantity)
LV_MOD := MOD(CNTR,9);
IF LV_MOD > 0
THEN  
LV_DEL_QUANTITY := LV_ORD_QUANTITY;
ELSE
LV_DEL_QUANTITY := ROUND(RAND() * LV_ORD_QUANTITY, 0 );
END IF;
--PO number
LV_PO_NUM := CNTR + 1000000;
--Delivery number
LV_DEL_NUM := CNTR + 200000;
--PO Item number
LV_PO_ITEM := 10; 

--Super Vendor Exactly OTIF
IF LV_VENDOR_ID = 106
THEN
LV_DEL_QUANTITY := LV_ORD_QUANTITY;
LV_DEL_DATE := LV_REQ_DATE;
END IF;

INSERT INTO "MSTR"."PO_HEADER"
VALUES(LV_PO_NUM,LV_PO_DATE,LV_PO_ORG_ID,LV_VENDOR_ID,1);

INSERT INTO "MSTR"."PO_ITEM"
VALUES(LV_PO_NUM,LV_PO_ITEM,LV_MATNR_ID,LV_ORD_QUANTITY,LV_UNIT_PRICE,LV_VALUE,'EUR','PCS',LV_REQ_DATE,1);

INSERT INTO "MSTR"."DELIVERY"
VALUES(LV_DEL_NUM,LV_PO_NUM,LV_PO_ITEM,LV_MATNR_ID,LV_DEL_QUANTITY,'PCS',LV_DEL_DATE,1);

CNTR := CNTR + 1;
END WHILE;
END;

call BUILD_MSTR_DATA(1000000);