Trigger1
trans_value_issue_106 AFTER INSERT ON stock_transaction_detail_106 FOR EACH ROW EXECUTE PROCEDURE trans_value_issue_update_106()

Function1
_100410|trans_value_issue_update_106|trigger||volatile|masivakumar|plpgsql|
DECLARE
        parent_stock_rec record;
        parent_transaction_rec record;
        new_quantity numeric;
        parent_transaction_prop numeric;
        current_transaction_prop numeric;
BEGIN
        IF (TG_OP='INSERT') THEN
                IF (NEW.transaction_type='i' AND NEW.stock_reference_type_id IN (5,10,3,8)) THEN
                        --get total incoming quantity of the parent stock of this transaction for calculating proportionate values for the current transaction
                        SELECT incoming_quantity[1] as quantity INTO parent_stock_rec
                        FROM stk_details_meta_106 WHERE stock_id=NEW.reference_detail;

                        --parent stock may have more than 1 incoming transactions. For each of them as source_id insert proportionate value for the current transaction
                        FOR parent_transaction_rec IN SELECT transaction_id FROM stock_transaction_detail_106
                        WHERE transaction_type='i' AND stock_id=NEW.reference_detail
                        ORDER BY transaction_id LOOP
                                --for the current transaction getting total hide quantity
                                new_quantity:=0;
                                IF (NEW.hide_side='S') THEN
                                        new_quantity:=NEW.quantity/2;
                                ELSIF (NEW.hide_side='H') THEN
                                        new_quantity:=NEW.quantity;
                                END IF;
                                --value for new transaction should be proportion of new transaction qty to the parent stock quantity
                                --current_transaction_prop:=(new_quantity/parent_stock_rec.quantity)::numeric;
                                INSERT INTO transaction_value (transaction_id,cost_type_id,section_id,value,source_id) SELECT NEW.transaction_id,cost_type_id,section_id,((sum(value)/parent_stock_rec.quantity)*new_quantity),parent_transaction_rec.transaction_id FROM transaction_value WHERE transaction_id =parent_transaction_rec.transaction_id GROUP BY cost_type_id,section_id;
                        END LOOP;
                END IF;
        END IF;
RETURN null;
END;

Trigger 2
transaction_value_update_trigg AFTER INSERT OR UPDATE ON transaction_value FOR EACH ROW EXECUTE PROCEDURE transaction_value_update_func()

Function 2
_100410|transaction_value_update_func|trigger||volatile|masivakumar|plpgsql|
DECLARE
        product_category_rec record;
        outgoing_transactions_rec record;
        stock_qty_rec record;
        child_proportionate_value numeric;
        transaction_id_rec record;
        chem_stock_qty_rec record;
        chem_child_proportionate_value numeric;
        section_id_rec record;
        leather_transaction_ids_rec record;
        std_qty_change_chk_rec record;
        table_exist record;
        cost_value_rec record;
        incoming_transactions_rec record;

BEGIN
        IF (TG_OP='UPDATE') THEN
                SELECT product_category_id INTO product_category_rec FROM stock_transaction_detail
                JOIN stock_details USING (stock_id) JOIN stock_store USING (stock_store_id) WHERE transaction_id=NEW.transaction_id;

                IF (product_category_rec.product_category_id=106) THEN
                        -- IF THE trigger is fired by quantity update of std 106 then do not update child values
                        SELECT transaction_id INTO std_qty_change_chk_rec FROM std_qty_temp WHERE transaction_id=NEW.transaction_id;
                                IF (std_qty_change_chk_rec.transaction_id=NEW.transaction_id) THEN
                                       --take incoming quantity of the stock of this transaction
                                        SELECT INTO stock_qty_rec incoming_quantity[1] as stk_qty FROM stk_details_meta_106 WHERE stock_id=(select stock_id from stock_transaction_detail_106 where transaction_id=NEW.transaction_id);

                                        --take all outgoing transactions of the stock and loop
                                        FOR outgoing_transactions_rec IN SELECT stock_id,transaction_id, stock_reference_type_id,transaction_date, reference_detail FROM stock_transaction_detail_106 WHERE stock_id=(select stock_id from stock_transaction_detail_106 where transaction_id=NEW.transaction_id) AND transaction_type='o'
                                        LOOP

                                        --get child stock incoming transaction id corresponding to current o transaction
                                        SELECT INTO transaction_id_rec transaction_id,quantity FROM stock_transaction_detail_106 WHERE stock_id=outgoing_transactions_rec.reference_detail AND reference_detail=outgoing_transactions_rec.stock_id and transaction_date=outgoing_transactions_rec.transaction_date and transaction_type='i';

                                        --even if 2 transactions exist for the same stock to same child stock, the source transaction id will be different so no need to handle them separately
                --                      IF (count(transaction_id_rec)=1)) THEN

                                                FOR incoming_transactions_rec IN SELECT transaction_id,quantity FROM stock_transaction_detail_106 WHERE stock_id=(select stock_id from stock_transaction_detail_106 where transaction_id=NEW.transaction_id) AND transaction_type='i'
                                                LOOP
                                                        IF (NEW.section_id IS NOT NULL) THEN
                                                                SELECT INTO cost_value_rec sum(value) as cost_value FROM transaction_value WHERE transaction_id IN (SELECT transaction_id FROM stock_transaction_detail_106 WHERE stock_id=(select stock_id from stock_transaction_detail_106 where transaction_id=NEW.transaction_id) AND transaction_type='i') and cost_type_id=NEW.cost_type_id and section_id=NEW.section_id;

                                                                --take proportion of (value_diff/incoming_quantity)*trans_quantity=child_proportionate_value
                                                                child_proportionate_value:=((cost_value_rec.cost_value/stock_qty_rec.stk_qty)*transaction_id_rec.quantity)/stock_qty_rec.stk_qty*incoming_transactions_rec.quantity;
                                                                --update transaction_value of child transaction creted from that o to value=value+child_proportionate_value
                                                                UPDATE transaction_value SET value=child_proportionate_value WHERE transaction_id=transaction_id_rec.transaction_id AND cost_type_id=NEW.cost_type_id AND section_id=NEW.section_id AND source_id=incoming_transactions_rec.transaction_id;
                                                        ELSE
                                                                SELECT INTO cost_value_rec sum(value) as cost_value FROM transaction_value WHERE transaction_id IN (SELECT transaction_id FROM stock_transaction_detail_106 WHERE stock_id=(select stock_id from stock_transaction_detail_106 where transaction_id=NEW.transaction_id) AND transaction_type='i') and cost_type_id=NEW.cost_type_id;
                                                                --take proportion of (value_diff/incoming_quantity)*trans_quantity=child_proportionate_value
                                                                child_proportionate_value:=((cost_value_rec.cost_value/stock_qty_rec.stk_qty)*transaction_id_rec.quantity)/stock_qty_rec.stk_qty*incoming_transactions_rec.quantity;

                                                                UPDATE transaction_value SET value=child_proportionate_value WHERE transaction_id=transaction_id_rec.transaction_id AND cost_type_id=NEW.cost_type_id AND source_id=incoming_transactions_rec.transaction_id;
                                                        END IF;
                                                END LOOP;
                                        END LOOP;
                        ELSE

                                --take incoming quantity of the stock of this transaction
                                SELECT INTO stock_qty_rec incoming_quantity[1] as stk_qty FROM stk_details_meta_106 WHERE stock_id=(select stock_id from stock_transaction_detail_106 where transaction_id=NEW.transaction_id);

                                --take all outgoing transactions of the stock and loop
                                FOR outgoing_transactions_rec IN SELECT stock_id,transaction_id, stock_reference_type_id,transaction_date, reference_detail FROM stock_transaction_detail_106 WHERE stock_id=(select stock_id from stock_transaction_detail_106 where transaction_id=NEW.transaction_id) AND transaction_type='o'
                                LOOP

                                --get child stock incoming transaction id corresponding to current o transaction
                                SELECT INTO transaction_id_rec transaction_id,quantity FROM stock_transaction_detail_106 WHERE stock_id=outgoing_transactions_rec.reference_detail AND reference_detail=outgoing_transactions_rec.stock_id and transaction_date=outgoing_transactions_rec.transaction_date;

                                --take proportion of (value_diff/incoming_quantity)*trans_quantity=child_proportionate_value
                                child_proportionate_value:=((NEW.value-OLD.value)/stock_qty_rec.stk_qty)*transaction_id_rec.quantity;


                                --even if 2 transactions exist for the same stock to same child stock, the source transaction id will be different so no need to handle them separately
        --                      IF (count(transaction_id_rec)=1)) THEN
                                        IF (NEW.section_id IS NOT NULL) THEN
                                                --update transaction_value of child transaction creted from that o to value=value+child_proportionate_value
                                                UPDATE transaction_value SET value=value+child_proportionate_value WHERE transaction_id=transaction_id_rec.transaction_id AND cost_type_id=NEW.cost_type_id AND section_id=NEW.section_id AND source_id=NEW.transaction_id;
                                        ELSE
                                                UPDATE transaction_value SET value=value+child_proportionate_value WHERE transaction_id=transaction_id_rec.transaction_id AND cost_type_id=NEW.cost_type_id AND source_id=NEW.transaction_id;
                                        END IF;
                                END LOOP;

                        END IF;
                ELSIF (product_category_rec.product_category_id=117) THEN

                        SELECT transaction_id INTO std_qty_change_chk_rec FROM std_qty_temp WHERE transaction_id=NEW.transaction_id;
                                IF (std_qty_change_chk_rec.transaction_id=NEW.transaction_id) THEN
                                --get stock quantity of chemical value being updated.
                                SELECT INTO chem_stock_qty_rec incoming_quantity[1] as stk_qty FROM stk_details_meta_117 WHERE stock_id =(select stock_id from stock_transaction_detail_117 where transaction_id=NEW.transaction_id);

                                --get all outgoing transactions of the stock being updated
                                FOR outgoing_transactions_rec IN SELECT stock_id,transaction_id, reference_detail, quantity, stock_reference_type_id FROM stock_transaction_detail_117 WHERE stock_id=(select stock_id from stock_transaction_detail_117 where transaction_id=NEW.transaction_id) AND transaction_type='o' LOOP
                                IF (outgoing_transactions_rec.stock_reference_type_id=4) THEN

                                        --for each transaction outgoing assign proportionate value
                                        chem_child_proportionate_value:=((NEW.value)/chem_stock_qty_rec.stk_qty)*outgoing_transactions_rec.quantity;

                                        SELECT INTO stock_qty_rec incoming_quantity[1] as stk_qty FROM stk_details_meta_106 WHERE stock_id =(SELECT stock_id from batch_status_stock WHERE batch_status_id=outgoing_transactions_rec.reference_detail);

                                        SELECT INTO section_id_rec section_id FROM master_production_gate mpg JOIN order_status_batches osb ON mpg.production_gate_id=osb.production_gate_id WHERE osb.status_id=outgoing_transactions_rec.reference_detail;

                                        FOR leather_transaction_ids_rec IN SELECT std.stock_id, std.transaction_id, quantity FROM stock_transaction_detail_106 std JOIN batch_status_stock bss ON std.stock_id=bss.stock_id WHERE bss.batch_status_id=outgoing_transactions_rec.reference_detail AND std.transaction_type='i' LOOP

                                        child_proportionate_value:=chem_child_proportionate_value*leather_transaction_ids_rec.quantity/stock_qty_rec.stk_qty;
                                        UPDATE transaction_value SET value=child_proportionate_value WHERE transaction_id=leather_transaction_ids_rec.transaction_id AND cost_type_id=2 AND section_id=section_id_rec.section_id AND source_id=NEW.transaction_id;
                                        IF (NOT FOUND) THEN
                                                INSERT INTO transaction_value (transaction_id,cost_type_id,section_id,value,source_id) VALUES (leather_transaction_ids_rec.transaction_id,2,section_id_rec.section_id,child_proportionate_value,NEW.transaction_id);
                                        END IF;
                                        END LOOP;
                                ELSIF (outgoing_transactions_rec.stock_reference_type_id=2) THEN
                                        chem_child_proportionate_value:=((NEW.value)/chem_stock_qty_rec.stk_qty)*outgoing_transactions_rec.quantity;

                                        SELECT INTO transaction_id_rec transaction_id FROM stock_transaction_detail_117 WHERE reference_detail=outgoing_transactions_rec.stock_id AND stock_id=outgoing_transactions_rec.reference_detail AND stock_reference_type_id=2;

                                        UPDATE transaction_value SET value=chem_child_proportionate_value WHERE transaction_id=transaction_id_rec.transaction_id AND source_id=NEW.transaction_id;
                                END IF;
                                END LOOP;
                        ELSE

                                                --get stock quantity of chemical value being updated.
                                SELECT INTO chem_stock_qty_rec incoming_quantity[1] as stk_qty FROM stk_details_meta_117 WHERE stock_id =(select stock_id from stock_transaction_detail_117 where transaction_id=NEW.transaction_id);

                                --get all outgoing transactions of the stock being updated
                                FOR outgoing_transactions_rec IN SELECT stock_id,transaction_id, reference_detail, quantity, stock_reference_type_id FROM stock_transaction_detail_117 WHERE stock_id=(select stock_id from stock_transaction_detail_117 where transaction_id=NEW.transaction_id) AND transaction_type='o' LOOP
                                IF (outgoing_transactions_rec.stock_reference_type_id=4) THEN

                                        --for each transaction outgoing assign proportionate value
                                        chem_child_proportionate_value:=((NEW.value-OLD.value)/chem_stock_qty_rec.stk_qty)*outgoing_transactions_rec.quantity;

                                        SELECT INTO stock_qty_rec incoming_quantity[1] as stk_qty FROM stk_details_meta_106 WHERE stock_id =(SELECT stock_id from batch_status_stock WHERE batch_status_id=outgoing_transactions_rec.reference_detail);

                                        SELECT INTO section_id_rec section_id FROM master_production_gate mpg JOIN order_status_batches osb ON mpg.production_gate_id=osb.production_gate_id WHERE osb.status_id=outgoing_transactions_rec.reference_detail;

                                        FOR leather_transaction_ids_rec IN SELECT std.stock_id, std.transaction_id, quantity FROM stock_transaction_detail_106 std JOIN batch_status_stock bss ON std.stock_id=bss.stock_id WHERE bss.batch_status_id=outgoing_transactions_rec.reference_detail AND std.transaction_type='i' LOOP

                                        child_proportionate_value:=chem_child_proportionate_value*leather_transaction_ids_rec.quantity/stock_qty_rec.stk_qty;
                                        UPDATE transaction_value SET value=value+child_proportionate_value WHERE transaction_id=leather_transaction_ids_rec.transaction_id AND cost_type_id=2 AND section_id=section_id_rec.section_id AND source_id=NEW.transaction_id;
                                        IF (NOT FOUND) THEN
                                                INSERT INTO transaction_value (transaction_id,cost_type_id,section_id,value,source_id) VALUES (leather_transaction_ids_rec.transaction_id,2,section_id_rec.section_id,child_proportionate_value,NEW.transaction_id);
                                        END IF;
                                        END LOOP;
                                ELSIF (outgoing_transactions_rec.stock_reference_type_id=2) THEN
                                        chem_child_proportionate_value:=((NEW.value-OLD.value)/chem_stock_qty_rec.stk_qty)*outgoing_transactions_rec.quantity;

                                        SELECT INTO transaction_id_rec transaction_id FROM stock_transaction_detail_117 WHERE reference_detail=outgoing_transactions_rec.stock_id AND stock_id=outgoing_transactions_rec.reference_detail AND stock_reference_type_id=2;

                                        UPDATE transaction_value SET value=value+chem_child_proportionate_value WHERE transaction_id=transaction_id_rec.transaction_id AND source_id=NEW.transaction_id;
                                END IF;
                                END LOOP;
                        END IF;
                END IF;
        ELSIF (TG_OP='INSERT') THEN

                IF (NEW.cost_type_id=3) THEN

                        SELECT INTO stock_qty_rec incoming_quantity[1] as stk_qty FROM stk_details_meta_106 WHERE stock_id=(select stock_id from stock_transaction_detail_106 where transaction_id=NEW.transaction_id);

                        --take all outgoing transactions of the stock and loop
                        FOR outgoing_transactions_rec IN SELECT stock_id,transaction_id, stock_reference_type_id,transaction_date, reference_detail FROM stock_transaction_detail_106 WHERE stock_id=(select stock_id from stock_transaction_detail_106 where transaction_id=NEW.transaction_id) AND transaction_type='o'
                        LOOP

                        --get child stock incoming transaction id
                        SELECT INTO transaction_id_rec transaction_id,quantity FROM stock_transaction_detail_106 WHERE stock_id=outgoing_transactions_rec.reference_detail AND reference_detail=outgoing_transactions_rec.stock_id and transaction_date=outgoing_transactions_rec.transaction_date;

                        --take proportion of (value_diff/incoming_quantity)*trans_quantity=child_proportionate_value
                        child_proportionate_value:=(NEW.value/stock_qty_rec.stk_qty)*transaction_id_rec.quantity;


                        IF (count(transaction_id_rec)=1) THEN
                                --update transaction_value of child transaction creted from that o to value=value+child_proportionate_value
                                UPDATE transaction_value SET value=value+child_proportionate_value WHERE transaction_id=transaction_id_rec.transaction_id AND cost_type_id=NEW.cost_type_id AND section_id=NEW.section_id AND source_id=NEW.transaction_id;
                                IF (NOT FOUND) THEN
                                        INSERT INTO transaction_value (transaction_id,cost_type_id,section_id,value,source_id) VALUES (transaction_id_rec.transaction_id,3,NEW.section_id,child_proportionate_value,NEW.transaction_id);
                                END IF;
                        END IF;
                        END LOOP;

                ELSIF(NEW.cost_type_id=2) THEN

                        SELECT INTO stock_qty_rec incoming_quantity[1] as stk_qty FROM stk_details_meta_106 WHERE stock_id=(select stock_id from stock_transaction_detail_106 where transaction_id=NEW.transaction_id);

                        --take all outgoing transactions of the stock and loop
                        FOR outgoing_transactions_rec IN SELECT stock_id,transaction_id, stock_reference_type_id,transaction_date, reference_detail FROM stock_transaction_detail_106 WHERE stock_id=(select stock_id from stock_transaction_detail_106 where transaction_id=NEW.transaction_id) AND transaction_type='o' and stock_reference_type_id in (2,3,5,8,10,18)
                        LOOP

                        --get child stock incoming transaction id
                        SELECT INTO transaction_id_rec stock_id,transaction_id,quantity FROM stock_transaction_detail_106 WHERE stock_id=outgoing_transactions_rec.reference_detail AND reference_detail=outgoing_transactions_rec.stock_id and transaction_date=outgoing_transactions_rec.transaction_date and transaction_type='i';
                        --take proportion of (value_diff/incoming_quantity)*trans_quantity=child_proportionate_value
                        child_proportionate_value:=(NEW.value/stock_qty_rec.stk_qty)*transaction_id_rec.quantity;


                        IF (count(transaction_id_rec)=1) THEN
                                --update transaction_value of child transaction creted from that o to value=value+child_proportionate_value
                                UPDATE transaction_value SET value=value+child_proportionate_value WHERE transaction_id=transaction_id_rec.transaction_id AND cost_type_id=NEW.cost_type_id AND section_id=NEW.section_id AND source_id=NEW.transaction_id;
                                IF (NOT FOUND) THEN
                                        INSERT INTO transaction_value (transaction_id,cost_type_id,section_id,value,source_id) VALUES (transaction_id_rec.transaction_id,2,NEW.section_id,child_proportionate_value,NEW.transaction_id);
                                END IF;
                        END IF;

                        END LOOP;
                END IF;
        ELSIF (TG_OP='DELETE') THEN

                IF(OLD.cost_type_id=3) THEN

                        SELECT INTO stock_qty_rec incoming_quantity[1] as stk_qty FROM stk_details_meta_106 WHERE stock_id=(select stock_id from stock_transaction_detail_106 where transaction_id=OLD.transaction_id);

                        --take all outgoing transactions of the stock and loop
                        FOR outgoing_transactions_rec IN SELECT stock_id,transaction_id, stock_reference_type_id,transaction_date, reference_detail FROM stock_transaction_detail_106 WHERE stock_id=(select stock_id from stock_transaction_detail_106 where transaction_id=OLD.transaction_id) AND transaction_type='o'
                        LOOP

                        --get child stock incoming transaction id
                        SELECT INTO transaction_id_rec transaction_id,quantity FROM stock_transaction_detail_106 WHERE stock_id=outgoing_transactions_rec.reference_detail AND reference_detail=outgoing_transactions_rec.stock_id and transaction_date=outgoing_transactions_rec.transaction_date;

                        --take proportion of (value_diff/incoming_quantity)*trans_quantity=child_proportionate_value
                        child_proportionate_value:=(OLD.value/stock_qty_rec.stk_qty)*transaction_id_rec.quantity;


                        IF (count(transaction_id_rec)=1) THEN
                                --update transaction_value of child transaction creted from that o to value=value+child_proportionate_value
                                UPDATE transaction_value SET value=value-child_proportionate_value WHERE transaction_id=transaction_id_rec.transaction_id AND cost_type_id=OLD.cost_type_id AND section_id=OLD.section_id AND source_id=OLD.transaction_id;
                        END IF;
                        END LOOP;

                ELSIF(OLD.cost_type_id=2) THEN

                        SELECT INTO stock_qty_rec incoming_quantity[1] as stk_qty FROM stk_details_meta_106 WHERE stock_id=(select stock_id from stock_transaction_detail_106 where transaction_id=OLD.transaction_id);

                        --take all outgoing transactions of the stock and loop
                        FOR outgoing_transactions_rec IN SELECT stock_id,transaction_id, stock_reference_type_id,transaction_date, reference_detail FROM stock_transaction_detail_106 WHERE stock_id=(select stock_id from stock_transaction_detail_106 where transaction_id=OLD.transaction_id) AND transaction_type='o' and stock_reference_type_id in (2,3,5,8,10,18)
                        LOOP

                        --get child stock incoming transaction id
                        SELECT INTO transaction_id_rec stock_id,transaction_id,quantity FROM stock_transaction_detail_106 WHERE stock_id=outgoing_transactions_rec.reference_detail AND reference_detail=outgoing_transactions_rec.stock_id and transaction_date=outgoing_transactions_rec.transaction_date and transaction_type='i';
                        --take proportion of (value_diff/incoming_quantity)*trans_quantity=child_proportionate_value
                        child_proportionate_value:=(OLD.value/stock_qty_rec.stk_qty)*transaction_id_rec.quantity;

                        IF (count(transaction_id_rec)=1) THEN
                                --update transaction_value of child transaction creted from that o to value=value+child_proportionate_value
                                UPDATE transaction_value SET value=value-child_proportionate_value WHERE transaction_id=transaction_id_rec.transaction_id AND cost_type_id=OLD.cost_type_id AND section_id=OLD.section_id AND source_id=OLD.transaction_id;
                        END IF;
                        END LOOP;
                END IF;
        END IF;
RETURN NULL;
END;