-- function will get the price of given ID in ch20_psm create or replace FUNCTION CH20_GET_PRICE ( xpsm IN NUMBER ) RETURN NUMBER IS y_price number; BEGIN SELECT price INTO y_price FROM ch20_psm WHERE ID = xpsm; return y_price; EXCEPTION WHEN OTHERS THEN return(-505); END CH20_GET_PRICE; / create or replace PROCEDURE CH20_CALCULATE_BOM (xpsm IN NUMBER, ystatus OUT INTEGER, yprice OUT NUMBER) -- creation date: 20.1.2023 -- author: Robert Leskovar -- input parameter: xpsm, id of product or semi-product -- output parameters 1: status of calculation: 0=not calculated, 1=succesfully calculated -- output parameters 2: price calculated -- description: calculation of the total costs of given product and all underlaying levels -- exceptions: system and user defined -- date of testing: 20.1.2023 -- author of test: swqlab -- status: APPROVED is v_rezult NUMBER(38, 2); v_psm NUMBER; v_price NUMBER(38, 2); e_negative_price EXCEPTION; CURSOR c_ds IS WITH STRUK(hier, subprt_id, aggreg_id, quantity) AS (SELECT 1 AS hier, subprt_id, aggreg_id, quantity FROM CH20_BOM JOIN CH20_PSM ON subprt_id = CH20_PSM.ID WHERE aggreg_id = xpsm UNION ALL SELECT hier+1, C1.subprt_id, C1.aggreg_id, C1.quantity FROM CH20_BOM C1 JOIN CH20_PSM P ON C1.subprt_id = P.ID JOIN STRUK C2 ON C1.aggreg_id = C2.subprt_id) SELECT hier, subprt_id, aggreg_id, quantity FROM STRUK ORDER BY hier DESC, aggreg_id, subprt_id; BEGIN ystatus := 0; v_rezult := 0.0; v_psm := 0; SAVEPOINT old_state; FOR r_ds IN c_ds LOOP v_price:= CH20_GET_PRICE(r_ds.subprt_id); IF ( v_price <= 0 ) THEN RAISE e_negative_price; END IF; IF ( r_ds.aggreg_id = v_psm ) THEN v_rezult := v_rezult + v_price * r_ds.quantity; UPDATE ch20_psm SET price = v_rezult WHERE ID = r_ds.aggreg_id; ELSE v_psm := r_ds.aggreg_id; v_rezult := v_price * r_ds.quantity; UPDATE ch20_psm SET price = v_rezult WHERE ID = r_ds.aggreg_id; END IF; END LOOP; COMMIT; ystatus := 1; yprice := v_rezult; EXCEPTION WHEN e_negative_price THEN ROLLBACK TO old_state; ystatus := 0; yprice := -505; WHEN OTHERS THEN ROLLBACK TO old_state; ystatus := 0; yprice := -100; end CH20_CALCULATE_BOM; / -- trigger prevents inserting into ch20_bom -- if pair (aggreg_id,subprt_id) exists or (aggreg_id = subprt_id) create or replace TRIGGER CH20_TRG_BOM_RULES before insert or update on ch20_bom for each row declare xsubprt numeric :=:new.subprt_id; xaggreg numeric :=:new.aggreg_id; xcount numeric; begin select count(subprt_id) into xcount from ch20_bom where subprt_id = xaggreg and aggreg_id = xsubprt; if xsubprt = xaggreg or xcount > 0 then :new.subprt_id :=null; end if; end; /