deliverynote

CREATE OR ALTER PROCEDURE GET_DELIVERY_NOTE_ITEMS_DATA (
    P_DELIVERY_NOTE_ID INTEGER)
RETURNS (
    ADR_1_COMMENT VARCHAR(255),
    ADR_1_ID INTEGER,
    ADR_1_NAME VARCHAR(255),
    ADR_2_COMMENT VARCHAR(255),
    ADR_2_ID INTEGER,
    ADR_2_NAME VARCHAR(255),
    ADR_3_COMMENT VARCHAR(255),
    ADR_3_ID INTEGER,
    ADR_3_NAME VARCHAR(255),
    ADR_4_COMMENT VARCHAR(255),
    ADR_4_ID INTEGER,
    ADR_4_NAME VARCHAR(255),
    BILLED_QUANTITY DOUBLE PRECISION,
    CATEGORY VARCHAR(3),
    CONSIGNMENT DOUBLE PRECISION,
    CUSTOMER_LICENSE_ID INTEGER,
    DELIVERY_NOTE_ID INTEGER,
    DELIVERY_NOTE_ITEM_ID INTEGER,
    EKAER_REQUIRED SMALLINT,
    EXPIRATION_DATE DATE,
    FEMZAR_NUMBER VARCHAR(30),
    FORMULA VARCHAR(50),
    GOODS_RETURN DOUBLE PRECISION,
    GROSS_AMOUNT DOUBLE PRECISION,
    GROSS_PRICE DOUBLE PRECISION,
    HAS_FACTORY_PRICE SMALLINT,
    IS_PACKAGE SMALLINT,
    LICENSE_NUMBER VARCHAR(50),
    LICENSE_TYPE_ID INTEGER,
    NET_AMOUNT DOUBLE PRECISION,
    NET_PRICE DOUBLE PRECISION,
    OTHER_RECEIPT DOUBLE PRECISION,
    PACKAGE_TYPE SMALLINT,
    PRODUCT_ID INTEGER,
    PRODUCT_NAME VARCHAR(60),
    PRODUCT_NUMBER VARCHAR(40),
    PURCHASE_PRICE DOUBLE PRECISION,
    QUANTITY DOUBLE PRECISION,
    QUANTITY_UNIT_NAME VARCHAR(6),
    QUOTA DOUBLE PRECISION,
    REVERSE_TAXATION SMALLINT,
    SELLING_PRICE DOUBLE PRECISION,
    VAT_AMOUNT DOUBLE PRECISION,
    VAT_PERCENT DOUBLE PRECISION,
    VTSZ_NAME VARCHAR(20))
AS
DECLARE VARIABLE V_REVERSE_TAXATION_INVOICE SMALLINT;
DECLARE VARIABLE V_VEVOKEDV_COUNT SMALLINT;
DECLARE VARIABLE V_VEVOKEDVFEJ_COUNT SMALLINT;
BEGIN
    V_REVERSE_TAXATION_INVOICE = 0;
    FOR SELECT SZLT.ID_SZLEVEL, SZLT.ID_SZLEVELTETEL, SZLT.ID_TERMEK, SZLT.MENNYISEG, SZLT.BESZAR, SZLT.ELADAR,
               SZLT.SZAMLAZOTT, SZLT.VISSZAARU, SZLT.BIZOMANYOS, SZLT.EGYEB_BIZONYLAT, SZLT.KVOTA, SZLT.RECEPT,
               SZLT.ID_VEVO_ENGEDELY, SZLT.SARZS, SZLT.LEJARAT,
               T.NEV, T.EGYSEG, T.AFA, T.FEMZARSZAM, T.KATEGORIA, T.FORDITOTT, T.ID_ENGEDELY_TIPUS,
               T.ID_ADR, ADR1.NEV, ADR1.COMMENT,  T.ID_ADR2, ADR2.NEV, ADR2.COMMENT,
               T.ID_ADR3, ADR3.NEV, ADR3.COMMENT, T.ID_ADR4, ADR4.NEV, ADR4.COMMENT,
               T.EKAER_KOTELES, T.IS_PACKAGE, T.PACKAGE_TYPE,
               V.NEV, E.NEV,
               COALESCE((SELECT COUNT(*) FROM VEVOKEDV WHERE ID_VEVO = SZL.ID_VEVO AND ID_TERMEK = SZLT.ID_TERMEK), 0),
               COALESCE((SELECT COUNT(*) FROM VEVOKEDVFEJ WHERE ID_VEVO = SZL.ID_VEVO AND ID_FORGALMAZO = T.ID_FORGALMAZO), 0)
        FROM SZLEVELTETEL SZLT
        JOIN SZLEVEL SZL ON SZL.ID_SZLEVEL = SZLT.ID_SZLEVEL
        JOIN TERMEK T ON T.ID_TERMEK = SZLT.ID_TERMEK
        LEFT JOIN VTSZ V ON V.ID_VTSZ = T.ID_VTSZ
        LEFT JOIN ENGEDELY_TIPUS E ON E.ID_ENGEDELY_TIPUS = T.ID_ENGEDELY_TIPUS
        LEFT JOIN ADR ADR1 ON ADR1.ID_ADR = T.ID_ADR
        LEFT JOIN ADR ADR2 ON ADR2.ID_ADR = T.ID_ADR2
        LEFT JOIN ADR ADR3 ON ADR3.ID_ADR = T.ID_ADR3
        LEFT JOIN ADR ADR4 ON ADR4.ID_ADR = T.ID_ADR4
        --LEFT JOIN VEVOKEDV VK ON VK.ID_VEVO = SZL.ID_VEVO AND VK.ID_TERMEK = SZLT.ID_TERMEK
        --LEFT JOIN VEVOKEDVFEJ VKF ON VKF.ID_VEVO = SZL.ID_VEVO AND VKF.ID_FORGALMAZO = T.ID_FORGALMAZO
        WHERE SZLT.ID_SZLEVEL = :P_DELIVERY_NOTE_ID
        INTO :DELIVERY_NOTE_ID, :DELIVERY_NOTE_ITEM_ID, :PRODUCT_ID, :QUANTITY, :PURCHASE_PRICE, :SELLING_PRICE,
             :BILLED_QUANTITY, :GOODS_RETURN, :CONSIGNMENT, :OTHER_RECEIPT, :QUOTA, :FORMULA,
             :CUSTOMER_LICENSE_ID, :PRODUCT_NUMBER, :EXPIRATION_DATE,
             :PRODUCT_NAME, :QUANTITY_UNIT_NAME, :VAT_PERCENT, :FEMZAR_NUMBER, :CATEGORY, :REVERSE_TAXATION, :LICENSE_TYPE_ID,
             :ADR_1_ID, :ADR_1_NAME, :ADR_1_COMMENT, :ADR_2_ID, :ADR_2_NAME, :ADR_2_COMMENT,
             :ADR_3_ID, :ADR_3_NAME, :ADR_3_COMMENT, :ADR_4_ID, :ADR_4_NAME, :ADR_4_COMMENT,
             :EKAER_REQUIRED, :IS_PACKAGE, :PACKAGE_TYPE,
             :VTSZ_NAME, :LICENSE_NUMBER, :V_VEVOKEDV_COUNT, :V_VEVOKEDVFEJ_COUNT
    DO
    BEGIN
        NET_PRICE = SELLING_PRICE;
        GROSS_PRICE = IIF(V_REVERSE_TAXATION_INVOICE = 1, NET_PRICE, (NET_PRICE * (1 + (VAT_PERCENT / 100.0))));
        NET_AMOUNT = CAST(NET_PRICE * QUANTITY AS NUMERIC(15,2));
        GROSS_AMOUNT = CAST(GROSS_PRICE * QUANTITY AS NUMERIC(15,2));
        VAT_AMOUNT = CAST(GROSS_AMOUNT - NET_AMOUNT AS NUMERIC(15,2));
        NET_PRICE = CAST(NET_PRICE AS NUMERIC(15,2));
        GROSS_PRICE = CAST(GROSS_PRICE AS NUMERIC(15,2));
        HAS_FACTORY_PRICE = IIF(V_VEVOKEDV_COUNT = 0, IIF(V_VEVOKEDVFEJ_COUNT = 0, 0, 1), 1);
        SUSPEND;
    END
END