ravencs

deliverynote

Jul 5th, 2018
64
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE OR ALTER PROCEDURE GET_DELIVERY_NOTE_ITEMS_DATA (
  2.     P_DELIVERY_NOTE_ID INTEGER)
  3. RETURNS (
  4.     ADR_1_COMMENT VARCHAR(255),
  5.     ADR_1_ID INTEGER,
  6.     ADR_1_NAME VARCHAR(255),
  7.     ADR_2_COMMENT VARCHAR(255),
  8.     ADR_2_ID INTEGER,
  9.     ADR_2_NAME VARCHAR(255),
  10.     ADR_3_COMMENT VARCHAR(255),
  11.     ADR_3_ID INTEGER,
  12.     ADR_3_NAME VARCHAR(255),
  13.     ADR_4_COMMENT VARCHAR(255),
  14.     ADR_4_ID INTEGER,
  15.     ADR_4_NAME VARCHAR(255),
  16.     BILLED_QUANTITY DOUBLE PRECISION,
  17.     CATEGORY VARCHAR(3),
  18.     CONSIGNMENT DOUBLE PRECISION,
  19.     CUSTOMER_LICENSE_ID INTEGER,
  20.     DELIVERY_NOTE_ID INTEGER,
  21.     DELIVERY_NOTE_ITEM_ID INTEGER,
  22.     EKAER_REQUIRED SMALLINT,
  23.     EXPIRATION_DATE DATE,
  24.     FEMZAR_NUMBER VARCHAR(30),
  25.     FORMULA VARCHAR(50),
  26.     GOODS_RETURN DOUBLE PRECISION,
  27.     GROSS_AMOUNT DOUBLE PRECISION,
  28.     GROSS_PRICE DOUBLE PRECISION,
  29.     HAS_FACTORY_PRICE SMALLINT,
  30.     IS_PACKAGE SMALLINT,
  31.     LICENSE_NUMBER VARCHAR(50),
  32.     LICENSE_TYPE_ID INTEGER,
  33.     NET_AMOUNT DOUBLE PRECISION,
  34.     NET_PRICE DOUBLE PRECISION,
  35.     OTHER_RECEIPT DOUBLE PRECISION,
  36.     PACKAGE_TYPE SMALLINT,  
  37.     PRODUCT_ID INTEGER,
  38.     PRODUCT_NAME VARCHAR(60),
  39.     PRODUCT_NUMBER VARCHAR(40),
  40.     PURCHASE_PRICE DOUBLE PRECISION,
  41.     QUANTITY DOUBLE PRECISION,
  42.     QUANTITY_UNIT_NAME VARCHAR(6),
  43.     QUOTA DOUBLE PRECISION,
  44.     REVERSE_TAXATION SMALLINT,
  45.     SELLING_PRICE DOUBLE PRECISION,
  46.     VAT_AMOUNT DOUBLE PRECISION,
  47.     VAT_PERCENT DOUBLE PRECISION,
  48.     VTSZ_NAME VARCHAR(20))
  49. AS
  50. DECLARE VARIABLE V_REVERSE_TAXATION_INVOICE SMALLINT;
  51. DECLARE VARIABLE V_VEVOKEDV_COUNT SMALLINT;
  52. DECLARE VARIABLE V_VEVOKEDVFEJ_COUNT SMALLINT;
  53. BEGIN
  54.     V_REVERSE_TAXATION_INVOICE = 0;
  55.     FOR SELECT SZLT.ID_SZLEVEL, SZLT.ID_SZLEVELTETEL, SZLT.ID_TERMEK, SZLT.MENNYISEG, SZLT.BESZAR, SZLT.ELADAR,
  56.                SZLT.SZAMLAZOTT, SZLT.VISSZAARU, SZLT.BIZOMANYOS, SZLT.EGYEB_BIZONYLAT, SZLT.KVOTA, SZLT.RECEPT,
  57.                SZLT.ID_VEVO_ENGEDELY, SZLT.SARZS, SZLT.LEJARAT,
  58.                T.NEV, T.EGYSEG, T.AFA, T.FEMZARSZAM, T.KATEGORIA, T.FORDITOTT, T.ID_ENGEDELY_TIPUS,            
  59.                T.ID_ADR, ADR1.NEV, ADR1.COMMENT,  T.ID_ADR2, ADR2.NEV, ADR2.COMMENT,
  60.                T.ID_ADR3, ADR3.NEV, ADR3.COMMENT, T.ID_ADR4, ADR4.NEV, ADR4.COMMENT,
  61.                T.EKAER_KOTELES, T.IS_PACKAGE, T.PACKAGE_TYPE,
  62.                V.NEV, E.NEV,
  63.                COALESCE((SELECT COUNT(*) FROM VEVOKEDV WHERE ID_VEVO = SZL.ID_VEVO AND ID_TERMEK = SZLT.ID_TERMEK), 0),
  64.                COALESCE((SELECT COUNT(*) FROM VEVOKEDVFEJ WHERE ID_VEVO = SZL.ID_VEVO AND ID_FORGALMAZO = T.ID_FORGALMAZO), 0)
  65.         FROM SZLEVELTETEL SZLT
  66.         JOIN SZLEVEL SZL ON SZL.ID_SZLEVEL = SZLT.ID_SZLEVEL
  67.         JOIN TERMEK T ON T.ID_TERMEK = SZLT.ID_TERMEK
  68.         LEFT JOIN VTSZ V ON V.ID_VTSZ = T.ID_VTSZ
  69.         LEFT JOIN ENGEDELY_TIPUS E ON E.ID_ENGEDELY_TIPUS = T.ID_ENGEDELY_TIPUS
  70.         LEFT JOIN ADR ADR1 ON ADR1.ID_ADR = T.ID_ADR
  71.         LEFT JOIN ADR ADR2 ON ADR2.ID_ADR = T.ID_ADR2
  72.         LEFT JOIN ADR ADR3 ON ADR3.ID_ADR = T.ID_ADR3
  73.         LEFT JOIN ADR ADR4 ON ADR4.ID_ADR = T.ID_ADR4
  74.         --LEFT JOIN VEVOKEDV VK ON VK.ID_VEVO = SZL.ID_VEVO AND VK.ID_TERMEK = SZLT.ID_TERMEK
  75.         --LEFT JOIN VEVOKEDVFEJ VKF ON VKF.ID_VEVO = SZL.ID_VEVO AND VKF.ID_FORGALMAZO = T.ID_FORGALMAZO
  76.         WHERE SZLT.ID_SZLEVEL = :P_DELIVERY_NOTE_ID
  77.         INTO :DELIVERY_NOTE_ID, :DELIVERY_NOTE_ITEM_ID, :PRODUCT_ID, :QUANTITY, :PURCHASE_PRICE, :SELLING_PRICE,
  78.              :BILLED_QUANTITY, :GOODS_RETURN, :CONSIGNMENT, :OTHER_RECEIPT, :QUOTA, :FORMULA,
  79.              :CUSTOMER_LICENSE_ID, :PRODUCT_NUMBER, :EXPIRATION_DATE,
  80.              :PRODUCT_NAME, :QUANTITY_UNIT_NAME, :VAT_PERCENT, :FEMZAR_NUMBER, :CATEGORY, :REVERSE_TAXATION, :LICENSE_TYPE_ID,
  81.              :ADR_1_ID, :ADR_1_NAME, :ADR_1_COMMENT, :ADR_2_ID, :ADR_2_NAME, :ADR_2_COMMENT,
  82.              :ADR_3_ID, :ADR_3_NAME, :ADR_3_COMMENT, :ADR_4_ID, :ADR_4_NAME, :ADR_4_COMMENT,
  83.              :EKAER_REQUIRED, :IS_PACKAGE, :PACKAGE_TYPE,
  84.              :VTSZ_NAME, :LICENSE_NUMBER, :V_VEVOKEDV_COUNT, :V_VEVOKEDVFEJ_COUNT
  85.     DO
  86.     BEGIN
  87.         NET_PRICE = SELLING_PRICE;
  88.         GROSS_PRICE = IIF(V_REVERSE_TAXATION_INVOICE = 1, NET_PRICE, (NET_PRICE * (1 + (VAT_PERCENT / 100.0))));
  89.         NET_AMOUNT = CAST(NET_PRICE * QUANTITY AS NUMERIC(15,2));
  90.         GROSS_AMOUNT = CAST(GROSS_PRICE * QUANTITY AS NUMERIC(15,2));
  91.         VAT_AMOUNT = CAST(GROSS_AMOUNT - NET_AMOUNT AS NUMERIC(15,2));
  92.         NET_PRICE = CAST(NET_PRICE AS NUMERIC(15,2));
  93.         GROSS_PRICE = CAST(GROSS_PRICE AS NUMERIC(15,2));
  94.         HAS_FACTORY_PRICE = IIF(V_VEVOKEDV_COUNT = 0, IIF(V_VEVOKEDVFEJ_COUNT = 0, 0, 1), 1);
  95.         SUSPEND;
  96.     END
  97. END
RAW Paste Data Copied